SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE [dbo].[p_grid_nm_endorsement_patterns]
	@endorsmentpattern_code ais_code_short  = '%',
	@act_code ais_code_short = '%',
	@family_code ais_code_short = '%'
	 
AS
BEGIN
	SET NOCOUNT ON

	set @endorsmentpattern_code = isnull(@endorsmentpattern_code,'%')
	set @act_code = isnull(@act_code,'%')
	set @family_code = isnull(@family_code,'%')

	SELECT nep.*,
		  nmeppf_premium_refundable =	case 
											when nepf.nmeppf_endorsmentpattern_code is not null then isnull(nepf.nmeppf_premium_refundable,0)
											else isnull(nep.nmep_all_product_families_premium_refundable, 0)
										end
	FROM nm_endorsement_patterns nep
	   left join nm_endorsment_patterns_families nepf on	    @family_code != '%' 
												AND nep.nmep_act_code = nepf.nmeppf_act_code
												AND nep.nmep_endorsmentpattern_code = nepf.nmeppf_endorsmentpattern_code
												AND nepf.nmeppf_family_code = @family_code	

	WHERE	 ( @endorsmentpattern_code = '%' OR nep.nmep_endorsmentpattern_code = @endorsmentpattern_code )
		  AND ( @act_code = '%' OR nep.nmep_act_code = @act_code )
		  AND ( @family_code = '%' OR nep.nmep_all_products_families = 1 OR nepf.nmeppf_act_code is not null)
			
END

GO
