
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE procedure [dbo].[p_acc_cts_history_find_by_policy_id]
	@policy_id		int	
as

	set nocount on;
	
	-- liste des champs présents dans grid_acc_cts_history
	select	ctsb_type_code,									-- type du bordereau: mise en demeure, suspension, résil etc
			vir_branch_code = a.act_internal_reference,		-- code branche
			ctsb_operation_utc_date,
			ctsb_reference_date,
			pols_number,									-- numéro contrat
			inv_operation_code__last,
			inv_start_date,
			inv_end_date,
			ctsb_bordereau_id,
			ctsa_account_id
	from	vw_acc_invoices_clients_policy icp
			inner join acc_accounts acc on acc.a_account_id = icp.inv_invoice_id
			inner join act_actors a on a.act_actor_id = acc.a_branch_id 
			inner join acc_cts_bordereaux_accounts cbd on cbd.ctsa_account_id = icp.inv_invoice_id
			inner join acc_cts_bordereaux cb on cb.ctsb_bordereau_id = cbd.ctsa_bordereau_id 
	where	icp.pols_policy_id = @policy_id

GO
