SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE procedure [dbo].[p_acc_cl_bordereaux_select]
	@branch_id			int,					
	@currency_code		ais_code_short,
	@actor_id			int,								-- un acteur spécifique, sinon -1
	@claim_id			int,								-- un sinistre spécifique, sinon -1
	@pay				bit,								-- inclure les règlements sinistres
	@rec				bit,								-- inclure les recupérations de recours
	@table_companies	type_table_id READONLY,				-- liste des compagnies
	@table_variables	type_table_code_short READONLY,		-- liste des variables
	@date_from			date,
	@date_to			date
	
as


	/*
	DGE 17/04/2016

	Sélectionne les règlements sinitres / encaissements des recours à inclure dans un bordereau crédit sinistre


	*/

	SET XACT_ABORT, NOCOUNT ON;

	select	
			a.a_account_id, pol_company_id = coalesce(cv.clv_company_id, pv.prdv_company_id, p.pol_company_id),

			vir_company_name = ac.adr_complete_name,				-- compagnie
			vir_actor_name = aa.adr_complete_name,					-- acteur
			ps.pols_number,											-- contrat
			c.cl_internal_reference,								-- sinistre
			nv.nmpv_l1, nv.nmpv_l2, nv.nmpv_l3,						-- libellé de la variable
			a.a_type_code,											-- règlement / encaissement de recours
			vir_amount_db =	case	
							when a_type_code = 'CP' then abs(a_amount)
							else 0
							end,
			vir_amount_cr = case 
							when a_type_code = 'CR' then abs(a_amount)
							else 0
							end,
			a.a_operation_utc_date,									-- date de l'opération
			atrn_transaction_id = a_lettering						-- pour afficher l'identifiant de la transaction

	from	claim_variables_accounts cva
			inner join acc_accounts a				on	a.a_account_id = cva.clacc_account_id
			inner join claim_claims c				on	c.cl_claim_id = cva.clacc_claim_id
			-- filtre variables
			inner join vw_claim_variables cv		on	cv.clv_claim_id = cva.clacc_claim_id 
													and	cv.clv_variable_id = cva.clacc_variable_id 
			inner join @table_variables tv			on	tv.code_short = cv.nmpv_variable_code 
			inner join vw_nm_variables nv			on	nv.nmpv_variable_code = cv.nmpv_variable_code 

			left join pr_products_variables pv		on	pv.prdv_variable_id = cva.clacc_variable_id				-- left join, car on peut avoir des variables de type règlement mandataire
			-- filtre compagnies
			inner join pol_policies_situations ps	on	ps.pols_policy_situation_id = c.cl_policy_situation_id
			inner join pol_policies p				on	p.pol_policy_id = ps.pols_policy_id
			inner join @table_companies tc			on	tc.id = coalesce(cv.clv_company_id, pv.prdv_company_id, p.pol_company_id)	-- si variable de la cie principale ou variable de type règlement mandataire
			
			-- acteur
			inner join vw_act_actors_adresses aa	on	aa.act_actor_id = a.a_actor_id		
			-- compagnie
			inner join vw_act_actors_adresses ac	on	ac.act_actor_id = isnull(pv.prdv_company_id, p.pol_company_id)

	where	cva.clacc_operation_code = 'P'							-- uniquement les lignes validées
	and		a.a_branch_id = @branch_id								-- filtre sur EJ
	and		a.a_currency_code = @currency_code						-- filtre sur le code devise
	and		( @claim_id <= 0 or cva.clacc_claim_id = @claim_id )		-- filtre sur @claim_id?
	and		( @actor_id <= 0 or a.a_actor_id = @actor_id )			-- filtre sur @actor_id
	and		1 =		case		
					when a.a_type_code = 'CP' and @pay != 0 then 1
					when a.a_type_code = 'CR' and @rec != 0 then 1
					else 0
					end
	-- exclure les règlements / encaissements de recours déjà extraits sur d'autres bordereaux
	and		not exists	(
						select	1
						from	acc_cl_bordereaux_accounts_details x
						where	x.clbad_account_id__payrec = cva.clacc_account_id )
	-- les CP/CR doivent être validés par une transaction contenue dans un bordereau de transactions validées 
	and		exists		(
						select	*
						from	acc_accounts t
								inner join acc_trn_bordereaux_details bd on bd.trnbd_transaction_id = t.a_account_id
								inner join acc_trn_bordereaux b on b.trnb_bordereau_id = bd.trnbd_bordereau_id
						where	t.a_type_code = 'T'
						and		t.a_actor_id = a.a_actor_id
						and		t.a_lettering = a.a_lettering
						and		b.trnb_tmp = 0
						)
	-- 	0000774: Bordereau crédit sinistre : rajouter un filtre facultatif sur les dates
	and		convert(date, a.a_operation_utc_date) between isnull(@date_from, '01/01/1900') and isnull(@date_to, '01/01/2100');



						






GO
