
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE [dbo].[p_doc_collections_get_document_details_by_document_id]
	@document_id int
AS

	/*
	DGE 03/003/2016

	Cette procédure renvoie les détails d'un document (par document_id).

	*/

	
	select	d.*,
			c.*, 
			o.obj_compression_type, 
			o.obj_is_compressed,
			a.act_internal_reference, 
			pols_number = isnull(ps1.pols_number, ps2.pols_number),
			cl.cl_internal_reference
	from	doc_collections_docs d
			inner join doc_collections c on c.dc_collection_id = d.dcd_collection_id
			inner join act_actors a on a.act_actor_id = d.dcd_actor_id

			left join vw__obj_objects_select o on o.obj_object_id = d.dcd_object_id

			-- jointure pour recupérer le numéro de contrat, si dcd_policy_situation_id est renseigné
			left join pol_policies_situations ps1 on ps1.pols_policy_situation_id = d.dcd_policy_situation_id
			-- joinutres pour recupérer le numéro de la dernière situation contrat si dcd_policy_situation_id = null et dcd_policy_id != null
			left join pol_policies p on p.pol_policy_id = d.dcd_policy_id
			left join pol_policies_situations ps2 on ps2.pols_policy_situation_id = p.pol_last_policy_situation_id

			left join claim_claims cl on cl.cl_claim_id = d.dcd_claim_id
			
	where	d.dcd_document_id = @document_id

GO
