SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE procedure [dbo].[p_rep_trn_csd_bdx_details]
	@user_code ais_code_medium,
	@bordereau_id int
as 

	set nocount on;


	select	-- informations concernant l'utilisateur
			usr.usr_name, usr.usr_mail, 
			-- bordereau temporaire ?
			b.trnb_tmp, b.trnb_reference, b.trnb_bank_date, b.trnb_currency_code,
	
			-- entité juridique

			branch_internal_reference = branch.act_internal_reference,
			branch_external_reference = branch.act_external_reference,
			branch_display_name = branch.br_display_name,
			branch_name = branch.adr_complete_name,
			branch_addr1 = branch.adr_address1,
			branch_addr2 = branch.adr_address2,
			branch_addr3 = branch.adr_address3,
			branch_postal_code = branch.adr_postal_code,
			branch_city = branch.adr_city,
			branch_mail = branch.adr_mail,
			branch_home_phone = branch.adr_home_phone,

			-- apporteur
			app_id = i.act_actor_id,
			app_complete_name = i.adr_complete_name,
			app_addr1 = i.adr_address1, 
			app_addr2 = i.adr_address2, 
			app_addr3 = i.adr_address3, 
			app_postal_code = i.adr_postal_code, 
			app_city = i.adr_city,

			-- client
			cli_internal_reference = cli.act_internal_reference,
			cli_name = cli.adr_complete_name,

			-- variables
			nv.nmpv_l2, v.tf, v.taxes, v.ti, vi.value,

			-- contrat
			pps.pols_number, 

			-- invoice
			inv.inv_start_date, inv.inv_end_date



	from	acc_trn_bordereaux_details bd
			inner join acc_trn_bordereaux b on b.trnb_bordereau_id = bd.trnbd_bordereau_id

			-- entité juridique
			inner join acc_accounts acc on acc.a_account_id = bd.trnbd_transaction_id
			inner join vw_act_branches branch on branch.act_actor_id = acc.a_branch_id

			-- client
			inner join vw_act_clients cli on cli.act_actor_id = acc.a_actor_id


			-- apporteur
			inner join acc_commission_sharing_deduced csd on csd.csd_transaction_id = bd.trnbd_transaction_id
			inner join acc_accounts ai on ai.a_account_id = csd.csd_csd_id
			inner join vw_act_introducers i on i.act_actor_id = ai.a_actor_id

			-- détails de la quittance
			inner join acc_invoices inv on inv.inv_invoice_id = csd.csd_invoice_id
			inner join	(
						select		invoice_id = invpolv_invoice_id, variable_id = invpolv_variable_id, 
									tf = sum(invpolv_tf),
									taxes = sum(invpolv_taxes),
									ti = sum(invpolv_ti)
						from		acc_invoices_policies_variables
						group by	invpolv_invoice_id, invpolv_variable_id 

						union

						select		invoice_id = invrisv_invoice_id, variable_id = invrisv_variable_id, 
									tf = sum(invrisv_tf),
									taxes = sum(invrisv_taxes),
									ti = sum(invrisv_ti)
						from		acc_invoices_risks_variables
						group by	invrisv_invoice_id, invrisv_variable_id
						) as v on v.invoice_id = csd.csd_invoice_id

			inner join	(
						select	invoice_id = invpolvi_invoice_id,
								variable_id = invpolvi_variable_id,
								introducer_id = invpolvi_introducer_id,
								value = sum(invpolvi_value)
						from	acc_invoices_policies_variables_introducers
						where	invpolvi_commission_sharing_deduced != 0
						group by invpolvi_invoice_id, invpolvi_variable_id, invpolvi_introducer_id

						union 

						select	invoice_id = invrisvi_invoice_id,
								variable_id = invrisvi_variable_id,
								introducer_id = invrisvi_introducer_id,
								value = sum(invrisvi_value)
						from	acc_invoices_risks_variables_introducers
						where	invrisvi_commission_sharing_deduced != 0
						group by invrisvi_invoice_id, invrisvi_variable_id, invrisvi_introducer_id
						) vi	on	vi.invoice_id = csd.csd_invoice_id
								and	vi.variable_id = v.variable_id
								and	vi.introducer_id = ai.a_actor_id

			inner join pr_products_variables pv on pv.prdv_variable_id = v.variable_id
			inner join nm_products_variables nv on nv.nmpv_variable_code = pv.prdv_variable_code

			-- contrat
			inner join pol_policies_situations pps on pps.pols_policy_situation_id = inv.inv_policy_situation_id

			inner join (	
						select *
						from	usr_users
						where	usr_user_code = @user_code ) as usr on 1 = 1


	where	bd.trnbd_bordereau_id = @bordereau_id



GO
