SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE procedure [dbo].[p_product_scenario_fields]
	@product_code ais_code_short,
	@branch_id int

as
	set nocount on;

	declare @risk_scen_code ais_code_short
	declare @policy_scen_code ais_code_short
	declare @delegation bit

	select	@risk_scen_code = isnull(pfb.pfb_risk_scen_code, pf.pf_risk_scen_code),
			@policy_scen_code = isnull(pfb.pfb_policy_scen_code, pf.pf_policy_scen_code),
			@delegation = pf_delegation
	from pr_products
		inner join pr_families pf on pf_family_code = prd_family_code
		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 

	select dsf.*, mfc.fld_l1, mfc.fld_l2, mfc.fld_l3
	from dyn_scenarios_fields dsf
		inner join md_fields_cpl mfc on scf_table_name = fld_table_name and scf_field_name = fld_field_name
	where @risk_scen_code is not null and scf_scen_code = @risk_scen_code
		
	select dsf.*, mfc.fld_l1, mfc.fld_l2, mfc.fld_l3
	from dyn_scenarios_fields dsf
		inner join md_fields_cpl mfc on scf_table_name = fld_table_name and scf_field_name = fld_field_name
	where @policy_scen_code is not null and scf_scen_code = @policy_scen_code

	select pf_delegation = @delegation


GO
