SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE procedure [dbo].[p_claim_actor_by_id]
	@claim_id int,
	@actor_id int, 
	@with_join bit
as

	/*
	DGE 21/06/2017
	Obtient les détails d'un acteur associé au sinistre:
	- en une seule table (jointure), pour l'affichage;
	- en 3 tables, pour gérer la mise à jour
	*/


	if @with_join != 0 
	begin
	
		select	ca.*, adr.*, c.*
		from	claim_actors ca
				inner join act_actors a			on	a.act_actor_id = ca.clact_actor_id
				inner join adr_addresses adr	on	adr.adr_address_id = a.act_address_id
				left join claim_actors_cpl c	on	c.clactcpl_claim_id = ca.clact_claim_id
												and c.clactcpl_actor_id = ca.clact_actor_id
		where	clact_claim_id = @claim_id
		and		clact_actor_id = @actor_id

	end
	else
	begin

		select	ca.*
		from	claim_actors ca
		where	clact_claim_id = @claim_id
		and		clact_actor_id = @actor_id

		select	*
		from	act_actors
		where	act_actor_id = @actor_id

		select	adr.*
		from	act_actors a			
				inner join adr_addresses adr	on	adr.adr_address_id = a.act_address_id
		where	a.act_actor_id = @actor_id

		select	c.*
		from	claim_actors_cpl c
		where	clactcpl_claim_id = @claim_id
		and		clactcpl_actor_id = @actor_id

	end
GO
