SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO


CREATE PROCEDURE [dbo].[p_policy_hd_detail]
	@policy_situation_id INT,
	@product_code ais_code_short,
	@branch_id INT,
	@date_setup DATE,
	@for_view bit

AS
	SET NOCOUNT ON;

	DECLARE @family_code ais_code_short

	IF @policy_situation_id > 0 
	BEGIN 

		SELECT	@product_code = pp.pol_product_code,
				@branch_id = pp.pol_branch_id,
				@date_setup = pps.pols_mvt_date
		from pol_policies_situations pps
			INNER JOIN pol_policies pp ON pps.pols_policy_id = pp.pol_policy_id
		WHERE pps.pols_policy_situation_id = @policy_situation_id

	END 

	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)

	INSERT INTO @variables_table (variable_id)
	SELECT fppv.variable_id
	FROM dbo.f_pr_products_variables(@family_code, @product_code, @date_setup) fppv 
		
	-- VARIABLES
	SELECT 	polv_policy_situation_id = @policy_situation_id,
			polv_variable_id = ppv.prdv_variable_id,
			polv_premium = CASE WHEN pv.polv_policy_situation_id IS NOT NULL THEN pv.polv_premium else ISNULL(ppvb.prdvb_premium, ppv.prdv_premium) end,
			polv_tf = CASE WHEN pv.polv_policy_situation_id IS NOT NULL THEN pv.polv_tf else 0 end,
			polv_taxes = CASE WHEN pv.polv_policy_situation_id IS NOT NULL THEN pv.polv_taxes else 0 end,
			polv_ti = CASE WHEN pv.polv_policy_situation_id IS NOT NULL THEN pv.polv_ti else 0 end,
			polv_fmt_code__premium = CASE WHEN pv.polv_policy_situation_id IS NOT NULL THEN pv.polv_fmt_code__premium ELSE ISNULL(ppvb.prdvb_fmt_code__premium, ppv.prdv_fmt_code__premium) end,
			polv_franchise_mini = CASE WHEN pv.polv_policy_situation_id IS NOT NULL THEN pv.polv_franchise_mini ELSE ISNULL(ppvb.prdvb_franchise_mini, ppv.prdv_franchise_mini) end,
			polv_fmt_code__franchise_mini = CASE WHEN pv.polv_policy_situation_id IS NOT NULL THEN pv.polv_fmt_code__franchise_mini ELSE ISNULL(ppvb.prdvb_fmt_code__franchise_mini, ppv.prdv_fmt_code__franchise_mini) end,
			polv_franchise_maxi = CASE WHEN pv.polv_policy_situation_id IS NOT NULL THEN pv.polv_franchise_maxi ELSE ISNULL(ppvb.prdvb_franchise_maxi, ppv.prdv_franchise_maxi) end,
			polv_fmt_code__franchise_maxi = CASE WHEN pv.polv_policy_situation_id IS NOT NULL THEN pv.polv_fmt_code__franchise_maxi ELSE ISNULL(ppvb.prdvb_fmt_code__franchise_maxi, ppv.prdv_fmt_code__franchise_maxi) end,
			polv_franchise = CASE WHEN pv.polv_policy_situation_id IS NOT NULL THEN pv.polv_franchise ELSE ISNULL(ppvb.prdvb_franchise, ppv.prdv_franchise) end,
			polv_fmt_code__franchise =  CASE WHEN pv.polv_policy_situation_id IS NOT NULL THEN pv.polv_fmt_code__franchise ELSE ISNULL(ppvb.prdvb_fmt_code__franchise, ppv.prdv_fmt_code__franchise) end,
			polv_capital = CASE WHEN pv.polv_policy_situation_id IS NOT NULL THEN pv.polv_capital ELSE ISNULL(ppvb.prdvb_capital, ppv.prdv_capital) end,
			polv_fmt_code__capital = CASE WHEN pv.polv_policy_situation_id IS NOT NULL THEN pv.polv_fmt_code__capital ELSE ISNULL(ppvb.prdvb_fmt_code__capital, ppv.prdv_fmt_code__capital) end,
			npv.nmpv_variable_code, npv.nmpv_l1, npv.nmpv_l2, npv.nmpv_l3, npv.nmpv_type_code,
			ppvb.*
	FROM pr_products_variables ppv
		inner join @variables_table vt on ppv.prdv_variable_id = vt.variable_id
		LEFT JOIN pol_variables pv ON		ppv.prdv_variable_id = pv.polv_variable_id
										AND @policy_situation_id > 0
										AND pv.polv_policy_situation_id = @policy_situation_id
		INNER JOIN nm_products_variables npv ON		ppv.prdv_variable_code = npv.nmpv_variable_code
		LEFT JOIN pr_products_variables_branches ppvb ON		ppv.prdv_variable_id = ppvb.prdvb_variable_id
															AND ppvb.prdvb_branch_id = 	@branch_id
	WHERE		ppv.prdv_product_code = @product_code 
			AND ppv.prdv_ignored = 0
			AND (@for_view = 0 OR pv.polv_policy_situation_id IS NOT NULL)

	-- taxes 
	SELECT 	polvt_variable_id = ppvt.prdvt_variable_id,
			polvt_tax_id = at.acctax_tax_id,
			polvt_tax_value = CASE WHEN pvt.polvt_policy_situation_id IS NOT NULL then pvt.polvt_tax_value else CASE WHEN at.acctax_fixed_value = 1 THEN at.acctax_tax_value else 0 END end,
			polvt_tax_percent = CASE WHEN pvt.polvt_policy_situation_id IS NOT NULL then pvt.polvt_tax_percent ELSE 0 end,
			polvt_tax_net_included = at.acctax_net_included,
			nt.nmtax_tax_code,
			nt.nmtax_l1,
			nt.nmtax_l2,
			nt.nmtax_l3
	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
		LEFT JOIN pol_variables_taxes pvt ON		ppvt.prdvt_variable_id = pvt.polvt_variable_id
												AND at.acctax_tax_id = pvt.polvt_tax_id
												AND @policy_situation_id > 0
												AND pvt.polvt_policy_situation_id = @policy_situation_id	
		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 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
			AND (@for_view = 0 OR pvt.polvt_policy_situation_id IS NOT NULL)


	-- INVOICE VARIABLES
	SELECT 	invpolv_policy_situation_id = @policy_situation_id,
			invpolv_variable_id = ppv.prdv_variable_id,
			invpolv_tf = CASE WHEN aipv.invpolv_policy_situation_id IS NOT NULL THEN aipv.invpolv_tf else 0 end,
			invpolv_taxes = CASE WHEN aipv.invpolv_policy_situation_id IS NOT NULL THEN aipv.invpolv_taxes else 0 end,
			invpolv_ti = CASE WHEN aipv.invpolv_policy_situation_id IS NOT NULL THEN aipv.invpolv_ti else 0 end,
			invpolv_commission_tf = CASE WHEN aipv.invpolv_policy_situation_id IS NOT NULL THEN aipv.invpolv_commission_tf else 0 end,
			invpolv_commission_taxes = CASE WHEN aipv.invpolv_policy_situation_id IS NOT NULL THEN aipv.invpolv_commission_taxes else 0 end,
			invpolv_commission_ti = CASE WHEN aipv.invpolv_policy_situation_id IS NOT NULL THEN aipv.invpolv_commission_ti else 0 end,
			npv.nmpv_variable_code, npv.nmpv_l1, npv.nmpv_l2, npv.nmpv_l3, npv.nmpv_type_code,
			ppvb.*
	FROM pr_products_variables ppv
		inner join @variables_table vt on ppv.prdv_variable_id = vt.variable_id
		LEFT JOIN acc_invoices_policies_variables aipv ON		ppv.prdv_variable_id = aipv.invpolv_variable_id
															AND @policy_situation_id > 0
															AND aipv.invpolv_policy_situation_id= @policy_situation_id
		INNER JOIN nm_products_variables npv ON		ppv.prdv_variable_code = npv.nmpv_variable_code
		LEFT JOIN pr_products_variables_branches ppvb ON		ppv.prdv_variable_id = ppvb.prdvb_variable_id
															AND ppvb.prdvb_branch_id = 	@branch_id
	WHERE		ppv.prdv_product_code = @product_code 
			AND ppv.prdv_ignored = 0
			AND (@for_view = 0 OR aipv.invpolv_policy_situation_id IS NOT NULL)

	--invoice variables premium taxes 
	SELECT 	invpolvt_variable_id = ppvt.prdvt_variable_id,
			invpolvt_tax_id = at.acctax_tax_id,
			invpolvt_tax_value = CASE WHEN aipvt.invpolvt_policy_situation_id IS NOT NULL then aipvt.invpolvt_tax_value else CASE WHEN at.acctax_fixed_value = 1 THEN at.acctax_tax_value else 0 END end,
			at.acctax_net_included,
			nt.nmtax_tax_code,
			nt.nmtax_l1,
			nt.nmtax_l2,
			nt.nmtax_l3
	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 dbo.f_acc_taxes_by_date(@date_setup) f ON at.acctax_tax_id = f.tax_id
		LEFT JOIN acc_invoices_policies_variables_taxes aipvt ON		aipvt.invpolvt_tax_id = f.tax_id
																	AND @policy_situation_id > 0
																	AND aipvt.invpolvt_policy_situation_id= @policy_situation_id
																	AND aipvt.invpolvt_variable_id = vt.variable_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
			AND (@for_view = 0 OR aipvt.invpolvt_policy_situation_id IS NOT NULL)

	--invoice variables commission taxes 
	SELECT 	invpolvct_variable_id = ppvt.prdvct_variable_id,
			invpolvct_tax_id = at.acctax_tax_id,
			invpolvct_tax_value = CASE WHEN aipvct.invpolvct_policy_situation_id IS NOT NULL then aipvct.invpolvct_tax_value else CASE WHEN at.acctax_fixed_value = 1 THEN at.acctax_tax_value else 0 END end,
			at.acctax_net_included,
			nt.nmtax_tax_code,
			nt.nmtax_l1,
			nt.nmtax_l2,
			nt.nmtax_l3
	FROM pr_products_variables_commission_taxes ppvt
		INNER JOIN @variables_table vt ON ppvt.prdvct_variable_id = vt.variable_id
		INNER JOIN acc_taxes at ON at.acctax_tax_code = ppvt.prdvct_tax_code
		INNER JOIN dbo.f_acc_taxes_by_date(@date_setup) f ON at.acctax_tax_id = f.tax_id
		LEFT JOIN acc_invoices_policies_variables_commission_taxes aipvct ON		aipvct.invpolvct_tax_id = f.tax_id
																	AND @policy_situation_id > 0
																	AND aipvct.invpolvct_policy_situation_id= @policy_situation_id
																	AND aipvct.invpolvct_variable_id = vt.variable_id
		INNER JOIN pr_products_variables ppv ON ppvt.prdvct_variable_id = ppv.prdv_variable_id
		INNER JOIN nm_taxes nt ON ppvt.prdvct_tax_code = nt.nmtax_tax_code
	WHERE		ppv.prdv_product_code = @product_code 
			AND ppvt.prdvct_branch_id = @branch_id
			AND ppv.prdv_ignored = 0
			AND (@for_view = 0 OR aipvct.invpolvct_policy_situation_id IS NOT NULL)
		

	

			

	


GO
