SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE [dbo].[p_acc_cts_update_type_code__cts_clients]
	@table_id type_table_id READONLY		-- liste des id clients (cli_actor_id)
AS
	
	/*
	DGE
	Cette procedure met à jour le code contentieux (act_type_code__cts) des clients spécifiés dans @table_id
	par rapport aux codes contentieux des contrats du client (pol_policies.pol_type_code__cts)
	*/
	
	update	c
	set		c.cli_type_code__cts = tc.ctstyp_type_code
	from	act_clients c
			inner join	(
						select	distinct t.id, ct.ctstyp_type_code
						from	@table_id t
								left join	(
											select	p.pol_client_id, max_rank = MAX(ct.ctstyp_rank)
											from	pol_policies p
													inner join acc_cts_types ct on ct.ctstyp_type_code = p.pol_type_code__cts and ct.ctstyp_mark_item != 0
											group by p.pol_client_id
											) as tmp on tmp.pol_client_id = t.id
								left join acc_cts_types ct on ct.ctstyp_rank = tmp.max_rank
						) as tc on tc.id = c.cli_actor_id
	
	
GO
