SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE [dbo].[p_acc_cts_update_type_code__cts_policies]
	@table_id type_table_id READONLY		-- liste des id contrats (pol_policy_id)
AS
	
	/*
	DGE
	Cette procedure met à jour le code contentieux (pol_type_code__cts) des contrats spécifiés dans @table_id
	par rapport aux quittances de chaque contrat
	*/
	
	update	p
	set		p.pol_type_code__cts = tp.ctstyp_type_code
	from	pol_policies p
			inner join	(
						select	distinct t.id, ct.ctstyp_type_code
						from	@table_id t
								left join	(
											select	icp.pols_policy_id, max_rank = MAX(ct.ctstyp_rank)
											from	vw_acc_invoices_clients_policy icp
													inner join acc_cts_types ct on ct.ctstyp_type_code = icp.inv_type_code__cts and ct.ctstyp_mark_item != 0
											group by icp.pols_policy_id
											) as tmp on tmp.pols_policy_id = t.id
								left join acc_cts_types ct on ct.ctstyp_rank = tmp.max_rank
						) as tp on tp.id = p.pol_policy_id
			
GO
