SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE procedure [dbo].[p_rep_cl_bdx_details]
	@user_code ais_code_medium,
	@bordereau_id int
as 

	set nocount on;


	/*
	DGE 20/04/2016
	Bordereau crédit sinistres

	*/

	select	
			-- informations concernant l'utilisateur
			usr.usr_name, usr.usr_mail,
			-- 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,
			-- compagnie
			cie_internal_reference = cie.act_internal_reference,
			cie_external_reference = cie.act_external_reference,
			cie_display_name = cie.cie_display_name,
			cie_name = cie.adr_complete_name,
			cie_addr1 = cie.adr_address1,
			cie_addr2 = cie.adr_address2,
			cie_addr3 = cie.adr_address3,
			cie_postal_code = cie.adr_postal_code,
			cie_city = cie.adr_city,
			cie_mail = cie.adr_mail,
			cie_home_phone = cie.adr_home_phone,
			-- info bordereau
			b.clb_reference, b.clb_currency_code, b.clb_date_from, b.clb_date_to, 
			-- acteur reversion/récupération
			aa.adr_complete_name, aa.act_internal_reference,
			t.pols_number,
			t.cl_internal_reference, 
			t.nmpv_l1, t.nmpv_l2, t.nmpv_l3, 
			t.rec, t.rev
	from	acc_cl_bordereaux b
			-- utilisateur
			inner join (	
						select *
						from	usr_users
						where	usr_user_code = @user_code ) as usr on 1 = 1

			-- entité juridique
			inner join vw_act_branches branch			on branch.act_actor_id = b.clb_branch_id
			-- compagnies
			inner join acc_cl_bordereaux_accounts ba	on	ba.clba_bordereau_id = b.clb_bordereau_id
			inner join acc_accounts a					on	a.a_account_id = ba.clba_account_id
			inner join vw_act_cies cie					on	cie.act_actor_id = a.a_actor_id
			-- détails
			inner join (
						select	bad.clbad_bordereau_id, bad.clbad_account_id,
								a.a_actor_id, ps.pols_number, c.cl_internal_reference, nv.nmpv_l1, nv.nmpv_l2, nv.nmpv_l3, 
								rec = sum(	case 
											when a.a_type_code = 'CR' then abs(a_amount)
											else 0
											end),
								rev = sum(	case 
											when a.a_type_code = 'CP' then abs(a_amount)
											else 0
											end)
						from	acc_cl_bordereaux_accounts_details bad
								inner join acc_accounts a				on	a.a_account_id = bad.clbad_account_id__payrec
								inner join claim_variables_accounts cva	on	cva.clacc_account_id = a.a_account_id
								inner join claim_claims c				on	c.cl_claim_id = cva.clacc_claim_id
								inner join pol_policies_situations ps	on	ps.pols_policy_situation_id = c.cl_policy_situation_id

								left join pr_products_variables pv		on pv.prdv_variable_id = cva.clacc_variable_id 
								left join nm_claims_variables pv2		on pv2.nmcv_variable_id = cva.clacc_variable_id

								inner join vw_nm_variables nv			on	nv.nmpv_variable_code = isnull(pv.prdv_variable_code, pv2.nmcv_variable_code)
						group by bad.clbad_bordereau_id, bad.clbad_account_id,
								a.a_actor_id, ps.pols_number, c.cl_internal_reference, nv.nmpv_l1, nv.nmpv_l2, nv.nmpv_l3
						) as t	on	t.clbad_bordereau_id = ba.clba_bordereau_id
								and	t.clbad_account_id = ba.clba_account_id
			inner join vw_act_actors_adresses aa		on	aa.act_actor_id = t.a_actor_id
	where	b.clb_bordereau_id = @bordereau_id

GO
