SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO


CREATE PROCEDURE [dbo].[p_doc_collections_get_docs_by_collection_id]
	@collection_id int
AS

	/*
	DGE 29/02/2016

	Cette procédure renvoie tous les documents d'une collection

	*/

	SELECT	aa.act_internal_reference, adr_complete_name, adr_mail,
			pols_number, c.cl_internal_reference,
			d.*, col.*
	from	doc_collections_docs d
			inner join doc_collections col on col.dc_collection_id = d.dcd_collection_id
			inner join vw_act_actors_adresses aa on aa.act_actor_id = d.dcd_actor_id

			left join pol_policies_situations ps on ps.pols_policy_situation_id = d.dcd_policy_situation_id
			left join claim_claims c on c.cl_claim_id = d.dcd_claim_id
	where	d.dcd_collection_id = @collection_id
GO
