SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
create procedure [dbo].[p_nm_endorsment_patterns_families_by_act]
	@act_code ais_code_short
as
	set nocount on;

	select	nmeppf_act_code,
			nmeppf_endorsmentpattern_code,
			nmeppf_family_code ,
			nmeppf_premium_refundable
	from nm_endorsment_patterns_families 
	where nmeppf_act_code = @act_code

	union 

	select	nmeppf_act_code = nmep_act_code,
			nmeppf_endorsmentpattern_code = nmep_endorsmentpattern_code,
			nmeppf_family_code = pf_family_code,
			nmeppf_premium_refundable = nmep_all_product_families_premium_refundable
	from nm_endorsement_patterns
		inner join pr_families on 1 = 1 
	where		nmep_act_code = @act_code 
			and nmep_all_products_families = 1 
			and not exists	(	select 1 
								from nm_endorsment_patterns_families 
								where		nmeppf_act_code = nmep_act_code 
										and nmeppf_endorsmentpattern_code = nmep_endorsmentpattern_code 
										and nmeppf_family_code = pf_family_code
							)
GO
