
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE [dbo].[p_product_hd_params]
	@product_code ais_code_short,
	@branch_id INT,
	@date_setup DATE

AS
	SET NOCOUNT ON;

	DECLARE @family_code ais_code_short

	SELECT @family_code = pp.prd_family_code
	from pr_products pp
	WHERE pp.prd_product_code = @product_code

	DECLARE @variables_table TABLE(variable_id INT, pfv_variable_code__parent ais_code_short)

	INSERT INTO @variables_table (	variable_id,		pfv_variable_code__parent)
	SELECT							fppv.variable_id,	pfv.pfv_variable_code__parent	
	FROM dbo.f_pr_products_variables(@family_code, @product_code, @date_setup) fppv
		inner join pr_products_variables ppv on fppv.variable_id = ppv.prdv_variable_id
		INNER JOIN pr_products pp ON ppv.prdv_product_code = pp.prd_product_code
		INNER JOIN pr_families_variables pfv ON		ppv.prdv_family_code = pfv.pfv_family_code 
												AND ppv.prdv_variable_code = pfv.pfv_variable_code
		LEFT JOIN pr_products_variables_branches ppvb ON		fppv.variable_id = ppvb.prdvb_variable_id
															AND ppvb.prdvb_branch_id = 	@branch_id
	where	ppv.prdv_ignored = 0 and -- on elimine le variables ignorées sur tout le produit
			1 =	case --on elimine le variables ignorées sur la branche
					when ppvb.prdvb_variable_id is null then 1
					when isnull(ppvb.prdvb_ignored,1) = 0 then 1
					else 0
				end
		
	DECLARE @taxes_table TABLE(tax_id INT)
	INSERT INTO @taxes_table(	tax_id)
	SELECT						tax_id
	FROM dbo.f_acc_taxes_by_date(@date_setup)


	declare @masterbroker_comm_table TABLE(mbcv_type_code ais_code_short, mbcv_date_setup date, mbcv_value ais_decimal4)

	insert into @masterbroker_comm_table(mbcv_type_code, mbcv_date_setup, mbcv_value)
	select	mbv.mbcv_type_code, mbv.mbcv_date_setup, mbv.mbcv_value
	from	nm_masterbroker_comm_values mbv
			inner join	(	select	a.mbcv_type_code, 
									max_date = max(a.mbcv_date_setup)
							from	nm_masterbroker_comm_values a
							where	a.mbcv_date_setup <= @date_setup
							group by a.mbcv_type_code) as t		on	t.mbcv_type_code = mbv.mbcv_type_code
																and	t.max_date = mbv.mbcv_date_setup


	-- VARIABLES
	SELECT 	prdv_variable_id,
			prdv_variable_code,
			prdv_premium = ISNULL(ppvb.prdvb_premium, ppv.prdv_premium),
			prdv_fmt_code__premium = ISNULL(ppvb.prdvb_fmt_code__premium, ppv.prdv_fmt_code__premium),
			prdv_franchise_mini = ISNULL(ppvb.prdvb_franchise_mini, ppv.prdv_franchise_mini),
			prdv_fmt_code__franchise_mini = ISNULL(ppvb.prdvb_fmt_code__franchise_mini, ppv.prdv_fmt_code__franchise_mini),
			prdv_franchise_maxi = ISNULL(ppvb.prdvb_franchise_maxi, ppv.prdv_franchise_maxi),
			prdv_fmt_code__franchise_maxi = ISNULL(ppvb.prdvb_fmt_code__franchise_maxi, ppv.prdv_fmt_code__franchise_maxi),
			prdv_franchise = ISNULL(ppvb.prdvb_franchise, ppv.prdv_franchise),
			prdv_fmt_code__franchise =  ISNULL(ppvb.prdvb_fmt_code__franchise, ppv.prdv_fmt_code__franchise),
			prdv_capital = ISNULL(ppvb.prdvb_capital, ppv.prdv_capital),
			prdv_fmt_code__capital = ISNULL(ppvb.prdvb_fmt_code__capital, ppv.prdv_fmt_code__capital),
			npv.nmpv_variable_code, npv.nmpv_l1, npv.nmpv_l2, npv.nmpv_l3, npv.nmpv_type_code,
			invpolv_tf = ppvb.prdvb_invoice_premium_tf,
			invpolv_commission_tf = ppvb.prdvb_invoice_commission_tf,
			ppvb.prdvb_premium_is_required,
			ppvb.prdvb_franchise_mini_is_required,
			ppvb.prdvb_franchise_maxi_is_required,
			ppvb.prdvb_franchise_is_required,
			ppvb.prdvb_capital_is_required,
			ppvb.prdvb_invoice_premium_tf_is_required,
			ppvb.prdvb_invoice_commission_tf_is_required,
			ppvb.prdvb_invoice_premium_tf,
			ppvb.prdvb_invoice_commission_tf,
			prdvb_premium = isnull(ppvb.prdvb_premium,ppv.prdv_premium),
			vt.pfv_variable_code__parent,
			npvt.*,
			mbcv_value = isnull(mbv1.mbcv_value, mbv2.mbcv_value)		-- exception par EJ -> param variable
	FROM pr_products_variables ppv
		INNER join @variables_table vt ON ppv.prdv_variable_id = vt.variable_id
		INNER JOIN nm_products_variables npv ON		ppv.prdv_variable_code = npv.nmpv_variable_code
		inner join nm_products_variables_types npvt on npvt.nmpvt_type_code = npv.nmpv_type_code
		LEFT JOIN pr_products_variables_branches ppvb ON		ppv.prdv_variable_id = ppvb.prdvb_variable_id
															AND ppvb.prdvb_branch_id = 	@branch_id
		-- commission courtier grossiste - exceptions par EJ
		left join @masterbroker_comm_table mbv1 on mbv1.mbcv_type_code = ppvb.prdvb_type_code__mbcomm
		-- commission courtier grossiste - paramétrage de la variable
		left join @masterbroker_comm_table mbv2 on mbv1.mbcv_type_code = ppv.prdv_type_code__mbcomm
		
	WHERE		ppv.prdv_product_code = @product_code 
			AND ppv.prdv_ignored = 0
	order by case when vt.pfv_variable_code__parent is null then 0 else 1 end asc

	-- PREMIUM TAXES
	SELECT 	ppvt.prdvt_variable_id,
			ppv.prdv_variable_code,
			at.acctax_tax_id,
			at.acctax_fixed_value,
			--acctax_tax_value = CASE WHEN acctax_fixed_value = 1 THEN acctax_tax_value else null end,
			at.acctax_tax_value,
			at.acctax_net_included,
			at.acctax_payout_currency_code,
			nt.nmtax_tax_code,
			nt.nmtax_l1,
			nt.nmtax_l2,
			nt.nmtax_l3,
			vt.pfv_variable_code__parent
	FROM pr_products_variables_taxes ppvt
		INNER JOIN @variables_table vt ON ppvt.prdvt_variable_id = vt.variable_id
		INNER JOIN acc_taxes at ON at.acctax_tax_code = ppvt.prdvt_tax_code
		INNER JOIN @taxes_table tt ON at.acctax_tax_id = tt.tax_id
		INNER JOIN pr_products_variables ppv ON ppvt.prdvt_variable_id = ppv.prdv_variable_id
		INNER JOIN nm_taxes nt ON ppvt.prdvt_tax_code = nt.nmtax_tax_code
	WHERE		ppv.prdv_product_code = @product_code 
			AND ppvt.prdvt_branch_id = @branch_id
			AND ppv.prdv_ignored = 0

	-- POLICY COMMISSION TAXES
	SELECT 	ppvct.prdvct_variable_id,
			ppv.prdv_variable_code,
			at.acctax_tax_id,
			at.acctax_fixed_value,
			--acctax_tax_value = CASE WHEN acctax_fixed_value = 1 THEN acctax_tax_value else null end,
			at.acctax_tax_value,
			at.acctax_net_included,
			at.acctax_payout_currency_code,
			nt.nmtax_tax_code,
			nt.nmtax_l1,
			nt.nmtax_l2,
			nt.nmtax_l3
	FROM pr_products_variables_commission_taxes ppvct
		INNER JOIN acc_taxes at ON at.acctax_tax_code = ppvct.prdvct_tax_code
		INNER JOIN dbo.f_acc_taxes_by_date(@date_setup) f ON at.acctax_tax_id = f.tax_id
		INNER JOIN pr_products_variables ppv ON ppvct.prdvct_variable_id = ppv.prdv_variable_id
		INNER JOIN nm_taxes nt ON ppvct.prdvct_tax_code = nt.nmtax_tax_code
	WHERE		ppv.prdv_product_code = @product_code 
			AND ppvct.prdvct_branch_id = @branch_id
			AND ppv.prdv_ignored = 0

	SELECT pffv.*, pff.pff_l1, pff.pff_l2, pff.pff_l3
	FROM pr_families_formulas_variables pffv
		INNER JOIN pr_families_formulas pff ON pffv.pffv_family_code = pff.pff_family_code AND pffv.pffv_formula_code = pff.pff_formula_code
	WHERE pffv.pffv_family_code = @family_code


	SELECT *
	FROM pr_families_formulas pff
	WHERE pff.pff_family_code = @family_code
	order by pff_display_order asc 

	/*
	SELECT ab.*
	FROM act_branches ab
	WHERE ab.br_actor_id = @branch_id
	*/

	SELECT ppb.*, ac.cur_use_decimals, ab.br_currency_code
	FROM pr_products_branches ppb
		inner join act_branches ab on ab.br_actor_id = ppb.prdb_branch_id
		inner join acc_currencies ac on ppb.prdb_currency_code = ac.cur_currency_code
	WHERE		ppb.prdb_product_code = @product_code 
			AND ppb.prdb_branch_id = @branch_id

	SELECT *
	FROM pol_variables pv
	WHERE 1 = 2

	SELECT *
	FROM pol_variables_taxes pvt
	WHERE 1 = 2

	SELECT *
	FROM ris_variables rv
	WHERE 1 = 2

	SELECT *
	FROM ris_variables_taxes rvt
	WHERE 1 = 2

	SELECT *
	FROM acc_invoices_policies_variables aipv
	WHERE 1 = 2

	SELECT *
	FROM acc_invoices_policies_variables_taxes aipvt
	WHERE 1 = 2

	SELECT *
	FROM acc_invoices_policies_variables_commission_taxes aipvct
	WHERE 1 = 2

	SELECT *
	FROM acc_invoices_risks_variables aipv
	WHERE 1 = 2

	SELECT *
	FROM acc_invoices_risks_variables_taxes aipvt
	WHERE 1 = 2

	SELECT *
	FROM acc_invoices_risks_variables_commission_taxes aipvct
	WHERE 1 = 2

	SELECT rrs.riss_risk_situation_id, rrs.riss_formula_code
	from ris_risks_situations rrs
	WHERE 1 = 2

	SELECT *
	FROM acc_invoices ai
	WHERE 1 = 2

	SELECT *
	FROM acc_accounts aa
	WHERE 1 = 2

	select *
	from acc_invoices_operations
	where 1 = 2

	SELECT * 
	FROM acc_invoices_risks_situations airs
	WHERE 1 = 2

	SELECT * 
	FROM ris_risks_situations_main_invoices rrsmi
	WHERE 1 = 2

	SELECT *
	FROM pol_policies_situations_main_invoices ppsmi
	WHERE 1 = 2

	exec p_currencies_rates_by_date @exchange_date = @date_setup



			

	













GO
