SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE [dbo].[p_pr_products_list_by_branch_currency_company]
	@branch_id		int,					-- entité juridique de la session
	@user_code		ais_code_medium,		-- login
	@introducer_id	int,					-- apporteur attaché à l'utilisateur
	@currency_code	ais_code_short,			-- code devise
	@actor_id		int						-- company_id
AS

	SET NOCOUNT ON;

	DECLARE @products_table TABLE (product_code ais_code_short not null primary key) ;
	DECLARE @variables_table TABLE (variable_id int not null primary key) ;

	insert into @products_table (product_code)

	select	distinct product_code
	/*
	DGE 20/04/2017
	0000702: Affichage des produits en fonction des droits d'accès- bordereau compagnie
	On n'applique pas des filtres par utilisateur/apporteur en ce qui concerne l'affichage des produits
	from	dbo.f_pr_products(@branch_id, @user_code, @introducer_id, @currency_code, null)
	*/
	from	dbo.f_pr_products(@branch_id, '%', 0, @currency_code, null)


	

	-- variables contrat
	insert into @variables_table(variable_id)
	select	distinct invpolv_variable_id
	from	acc_invoices_policies_variables iv
			inner join acc_invoices i				on	i.inv_invoice_id = iv.invpolv_invoice_id
													and	i.inv_operation_code__last in ('DB', 'EC', 'RMB', 'S')
			inner join pol_policies_situations ps	on	ps.pols_policy_situation_id = iv.invpolv_policy_situation_id
			inner join pol_policies p				on	p.pol_policy_id = ps.pols_policy_id and p.pol_branch_id = @branch_id
			inner join @products_table tp			on	tp.product_code = p.pol_product_code
			inner join pr_products_variables pv		on	pv.prdv_variable_id = iv.invpolv_variable_id
	where	ISNULL(pv.prdv_company_id, p.pol_company_id) = @actor_id
	OR		EXISTS	(
					SELECT	1
					FROM	acc_invoices_policies_variables_cies ivc
					WHERE	ivc.invpolvc_invoice_id  = iv.invpolv_invoice_id
					AND		ivc.invpolvc_policy_situation_id = iv.invpolv_policy_situation_id
					AND		ivc.invpolvc_variable_id = iv.invpolv_variable_id
					AND		ivc.invpolvc_company_id = @actor_id
					)

	-- variables risque
	insert into @variables_table(variable_id)
	select	distinct invrisv_variable_id
	from	acc_invoices_risks_variables iv
			inner join acc_invoices i				on	i.inv_invoice_id = iv.invrisv_invoice_id
													and	i.inv_operation_code__last in ('DB', 'EC', 'RMB', 'S')
			inner join ris_risks_situations rs		on	rs.riss_risk_situation_id = iv.invrisv_risk_situation_id
			inner join pol_policies p				on	p.pol_policy_id = rs.riss_policy_id and p.pol_branch_id = @branch_id
			inner join @products_table tp			on	tp.product_code = p.pol_product_code
			inner join pr_products_variables pv		on	pv.prdv_variable_id = iv.invrisv_variable_id
	where	(
	
			ISNULL(pv.prdv_company_id, p.pol_company_id) = @actor_id
	OR		EXISTS	(
					SELECT	1
					FROM	acc_invoices_risks_variables_cies ivc
					WHERE	ivc.invrisvc_invoice_id = iv.invrisv_invoice_id
					AND		ivc.invrisvc_risk_situation_id = iv.invrisv_risk_situation_id
					AND		ivc.invrisvc_variable_id = iv.invrisv_variable_id
					AND		ivc.invrisvc_company_id = @actor_id
					)
			)
	-- improbable
	and		not exists	(
						select	1
						from	@variables_table
						where	variable_id = invrisv_variable_id )

	/* 
	Liste des produits utilisés par la compagnie sélectionnée
	*/
	select	distinct p.prd_product_code, p.prd_l1, p.prd_l2, p.prd_l3 
	from	@variables_table tv
			inner join pr_products_variables pv	on pv.prdv_variable_id = tv.variable_id
			inner join pr_products p			on p.prd_product_code = pv.prdv_product_code



	/* 
	Liste des variables (et leur type) utilisées par la compagnie sélectionnée
	*/
	select	distinct nv.nmpv_variable_code, nv.nmpv_l1, nv.nmpv_l2, nv.nmpv_l3, p.prd_product_code,
			nvt.nmpvt_l1, nvt.nmpvt_l2, nvt.nmpvt_l3
	from	@variables_table tv
			inner join pr_products_variables pv			on pv.prdv_variable_id = tv.variable_id
			inner join nm_products_variables nv			on nv.nmpv_variable_code = pv.prdv_variable_code
			inner join nm_products_variables_types nvt	on nvt.nmpvt_type_code = nv.nmpv_type_code
			inner join pr_products p					on p.prd_product_code = pv.prdv_product_code
	where	pv.prdv_ignored = 0
	-- 0000201: Nouveaux types de variables
	-- exclure les types des variables qui ne sont pas "compagnie"
	and		nvt.nmpvt_company != 0













GO
