SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE [dbo].[p_pol_get_policies_id_by_invoices_id]
	@table_id type_table_id READONLY,			-- identifiants des quittances
	@cts_only bit								-- traiter uniquement les quittances en contentieux
AS
	SET NOCOUNT ON;
	
	/*
	[DGE]
	IN: table contenant invoice_id

	Renvoyer la liste policy_id pour les valeurs invoice_id specifiées.

	Si @cts_only <> 0 -> on traite uniquement les quittances en état contentieux
	*/

	SELECT	DISTINCT ps.pols_policy_id
	FROM	acc_invoices i 
			INNER JOIN pol_policies_situations ps ON ps.pols_policy_situation_id = i.inv_policy_situation_id
			INNER JOIN @table_id t ON t.id = i.inv_invoice_id
	WHERE	@cts_only = 0
	OR		EXISTS	(
					SELECT	1
					FROM	acc_cts_types cts
					WHERE	cts.ctstyp_type_code = i.inv_type_code__cts
					AND		cts.ctstyp_mark_item <> 0
					)


			




	
GO
