SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE procedure [dbo].[p_policy_product_params]
	@product_code ais_code_short,				-- code produit
	@branch_id int,								-- entité juridique de la connexion/contrat
	@date_setup date,							-- date d'effet/mouvement du contrat
	@return_branch bit,
	@return_family bit,
	@return_company bit,
	@return_variables bit,
	@return_taxes bit,
	@return_introducers bit,
	@return_umr bit = 0,
	@return_masterbroker_comm bit = 0
as 
	
	set nocount on;

	declare @family_code ais_code_short
	declare @cie_actor_id int
	declare @pf_delegation bit
	declare @index_code ais_code_medium
	declare @index_value ais_decimal4
	declare @index_date date
	DECLARE @pf_type_pol_code ais_code_short

	-- family code
	select	@family_code = prd_family_code,
			@cie_actor_id = prd_company_id,
			@pf_delegation = pf.pf_delegation,
			@index_code =	case 
							-- pas d'exception 
							when pfb.pfb_family_code is null and pf.pf_index = 1 then pf.pf_index_code 
							-- exception index par EJ
							when pfb.pfb_family_code is not null and pfb.pfb_index = 1 then pfb.pfb_index_code
							else null 
							end,
			@pf_type_pol_code = pf_type_pol_code 
	from pr_products pp
		inner join pr_families pf on pp.prd_family_code = pf.pf_family_code
		-- exception des familles par EJ (pour determiner l'indice)
		left join pr_families_branches pfb	on	pfb.pfb_family_code = pf.pf_family_code
											and	pfb.pfb_branch_id = @branch_id
	where prd_product_code = @product_code 


	-- product
	select pp.*, pf_delegation = @pf_delegation, pf_type_pol_code = @pf_type_pol_code
	from pr_products pp
	where prd_product_code = @product_code
	
	if @return_family = 1 or @return_company = 1
	BEGIN

		if @return_family = 1
		begin

			if @index_code is not NULL
			BEGIN
				select	@index_value = iiv.idxv_value,
						@index_date = iiv.idxv_date
				from idx_indices_values iiv
					inner join (
									select	idxv_index_code,
											max_date = max(idxv_date)
									from idx_indices_values
									where idxv_date <= @date_setup
									-- DGE : trebuie filtru pe codul index-ului
									and	idxv_index_code = @index_code
									group by idxv_index_code 
									) as tmp on		iiv.idxv_index_code = tmp.idxv_index_code
																			and iiv.idxv_date = tmp.max_date

			END

			-- family
			select	pf.*, 
					idxv_value = @index_value,
					idxv_date = @index_date,
					pfb_index_code = @index_code,
					pfb_index = isnull(pfb_index, pf_index)
			from	pr_families pf
					left join pr_families_branches pfb	on	pfb.pfb_family_code = pf.pf_family_code
														and	pfb.pfb_branch_id = @branch_id

			where pf.pf_family_code = @family_code

			-- family formulas
			select pff.*
			from pr_families_formulas pff
			where pff.pff_family_code = @family_code
			order by pff_display_order asc
		end

		if @return_company = 1
		BEGIN
			select * 
			from vw_act_cies
			where cie_actor_id = @cie_actor_id
		END
	end 

	-- BRANCH 
	if @return_branch = 1
	begin
		-- pr_products_branches
		select	ppb.*, 
				ac.cur_currency_code, ac.cur_use_decimals, ac.cur_symbol, ac.cur_symbol_position_left, ac.cur_l1, ac.cur_l2, ac.cur_l3
		from	pr_products_branches ppb
				inner join	acc_currencies ac on ac.cur_currency_code = ppb.prdb_currency_code
		where	ppb.prdb_product_code = @product_code
		and		ppb.prdb_branch_id = @branch_id

		-- branch
		select	wab.*
		from	vw_act_branches wab
		where	wab.act_actor_id = @branch_id

		select * 
		from nm_formats nf
			--left join nm_formats_transformation_types nftt on nf.fmt_transformation_type_code = nftt.nftt_transformation_code 

	end 

	-- VARIABLES
	if @return_variables = 1
	BEGIN

		declare @umr_table TABLE(umrv_umr_code ais_code_long, umrv_date date, umrv_value varchar(30))

		if @return_umr = 1
		BEGIN

			insert into @umr_table (	umrv_umr_code,		umrv_date,		umrv_value)
			select						uv.umrv_umr_code,	uv.umrv_date,	uv.umrv_value
			from umr_values uv
				inner join (	select	uv.umrv_umr_code, 
										max_date = max(uv.umrv_date)
								from umr_values uv
								where uv.umrv_date <= @date_setup
								group by uv.umrv_umr_code) as tmp ON		uv.umrv_umr_code = tmp.umrv_umr_code
																		and uv.umrv_date = tmp.max_date
		END

		-- DGE 05/03/2016
		declare @masterbroker_comm_table TABLE(mbcv_type_code ais_code_short, mbcv_date_setup date, mbcv_value ais_decimal4)

		if @return_masterbroker_comm = 1
		begin

			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
		end



		-- family formulas variables
		if @return_family = 1
		begin	
			select pffv.*, npv.nmpv_type_code, pfv.pfv_optional
			from pr_families_formulas_variables pffv
				inner join nm_products_variables npv on npv.nmpv_variable_code = pffv.pffv_variable_code
				inner join pr_families_variables pfv on		pffv.pffv_family_code = pfv.pfv_family_code
														and pffv.pffv_variable_code = pfv.pfv_variable_code
			where pffv.pffv_family_code = @family_code

			select pfv.*
			from pr_families_variables pfv
			where pfv.pfv_family_code = @family_code

		end 

		-- cautam variabilele in curs la data mvt
		declare @product_variables table(variable_id int primary key, variable_code ais_code_short, is_ignored bit)

		insert into @product_variables(	variable_id,			variable_code,			is_ignored)
		select							ppv.prdv_variable_id,	ppv.prdv_variable_code,	isnull(ppvb.prdvb_ignored, ppv.prdv_ignored)
		from pr_products_variables ppv
			inner join (	select  product_code = prdv_product_code,
									variable_code = prdv_variable_code, 
									max_variable_date_setup = max(prdv_variable_date_setup)
							from pr_products_variables 
							where		prdv_product_code = @product_code
									and prdv_variable_date_setup <= @date_setup -- atentie, aici nu trebuie sa tratez prdv_ignored, poate cauza probleme
							group by prdv_product_code, prdv_variable_code) as temp on		ppv.prdv_product_code = temp.product_code
																						and ppv.prdv_variable_code = temp.variable_code
																						and ppv.prdv_variable_date_setup = temp.max_variable_date_setup
			left join pr_products_variables_branches ppvb on		ppvb.prdvb_variable_id = ppv.prdv_variable_id
																and ppvb.prdvb_product_code = ppv.prdv_product_code
																and ppvb.prdvb_branch_id = @branch_id
		--where isnull(ppvb.prdvb_ignored, ppv.prdv_ignored) = 0 cette condition n'est plus necessaire ici, on teste dans le code, on a besoin de ces variables pour le remboursement	
	
		-- product variables
		select	ppv.*, 
				prdvb_ignored = is_ignored, 
				prdvb_coef = coalesce(ppvb.prdvb_coef, ppv.prdv_coef, 1),
				prdvb_coef_monthly = coalesce(ppvb.prdvb_coef_monthly, ppv.prdv_coef_monthly,1),
				prdvb_coef_quarterly = coalesce(ppvb.prdvb_coef_quarterly, ppv.prdv_coef_quarterly,1),
				prdvb_coef_semi_annual = coalesce(ppvb.prdvb_coef_semi_annual, ppv.prdv_coef_semi_annual, 1),
				prdvb_commission_value = case when prdvb_commission_value is not null then prdvb_commission_value else prdv_commission_value end,
				prdvb_commission_fixed_value = case when prdvb_commission_value is not null then prdvb_commission_fixed_value else prdv_commission_fixed_value end,
				pfv.pfv_optional, 
				npv.*, 
				ut.umrv_date, ut.umrv_value, 
				npvt.*,
				-- masterbroker commission value & date
				mbcv_date_setup = isnull(mbv1.mbcv_date_setup, mbv2.mbcv_date_setup), 
				mbcv_value = isnull(mbv1.mbcv_value, mbv2.mbcv_value)
		from @product_variables pv
			inner join pr_products_variables ppv on pv.variable_id = ppv.prdv_variable_id
			inner join pr_families_variables pfv on ppv.prdv_family_code = pfv.pfv_family_code and ppv.prdv_variable_code = pfv.pfv_variable_code	
			inner join nm_products_variables npv on npv.nmpv_variable_code = ppv.prdv_variable_code
			inner join nm_products_variables_types npvt on npvt.nmpvt_type_code = npv.nmpv_type_code
			left join @umr_table ut on ppv.prdv_umr_code = ut.umrv_umr_code

			-- commission courtier grossiste - exceptions par EJ
			LEFT JOIN pr_products_variables_branches ppvb	ON	ppvb.prdvb_variable_id = ppv.prdv_variable_id
															AND ppvb.prdvb_branch_id = 	@branch_id
			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 mbv2.mbcv_type_code = ppv.prdv_type_code__mbcomm
	END
	
	-- TAXES
	if @return_taxes = 1
	begin
		declare @all_taxes table  ( tax_id int primary key, tax_code ais_code_short )

		insert into @all_taxes( tax_id,				tax_code)
		select					at.acctax_tax_id,	at.acctax_tax_code
		from acc_taxes at
			inner join (
						select	tax_code = acctax_tax_code, 
								date_setup = max(acctax_tax_date_setup) 
						from acc_taxes
						group by acctax_tax_code) as t on		at.acctax_tax_code = t.tax_code
															and at.acctax_tax_date_setup = t.date_setup


		-- taxele setate pe primele variabile produsului (aici le avem si pe cele returnabile din excel, excel_taxes_add)
		select ppvt.*, npv.nmpv_variable_code, at.*, nt.*
		from pr_products_variables_taxes ppvt
			inner join @product_variables pv on pv.variable_id = ppvt.prdvt_variable_id
			inner join @all_taxes allt on ppvt.prdvt_tax_code = allt.tax_code
			inner join acc_taxes at on allt.tax_id = at.acctax_tax_id
			inner join nm_products_variables npv on npv.nmpv_variable_code = pv.variable_code
			inner join nm_taxes nt on nt.nmtax_tax_code = at.acctax_tax_code 
		where ppvt.prdvt_branch_id = @branch_id

		-- taxele setate pe comisioanele variabile produsului (aici le avem si pe cele returnabile din excel, excel_taxes_add)
		
		  select ppvct.*, npv.nmpv_variable_code, at.*, nt.*
		  from pr_products_variables_commission_taxes ppvct
			 inner join @product_variables pv on pv.variable_id = ppvct.prdvct_variable_id
			 inner join @all_taxes allt on ppvct.prdvct_tax_code = allt.tax_code
			 inner join acc_taxes at on allt.tax_id = at.acctax_tax_id
			 inner join nm_products_variables npv on npv.nmpv_variable_code = pv.variable_code
			 inner join nm_taxes nt on nt.nmtax_tax_code = at.acctax_tax_code 
		  where ppvct.prdvct_branch_id = @branch_id
		
	end 

	-- introducers
	if @return_introducers = 1
	begin
		-- introducers
		select	i.*
		from	pr_products_variables_introducers i 
				inner join @product_variables pv on pv.variable_id = i.prdvi_variable_id 
	end

	select nmpop_operation_code, nmpop_act_code
	from nm_pol_operations

	exec p_currencies_rates_by_date @exchange_date = @date_setup






GO
