CREATE TABLE [dbo].[nm_endorsement_patterns]
(
[nmep_act_code] [dbo].[ais_code_short] NOT NULL,
[nmep_endorsmentpattern_code] [dbo].[ais_code_short] NOT NULL,
[nmep_l1] [nvarchar] (256) COLLATE French_CI_AS NOT NULL,
[nmep_l2] [nvarchar] (256) COLLATE French_CI_AS NULL,
[nmep_l3] [nvarchar] (256) COLLATE French_CI_AS NULL,
[nmep_sys] [bit] NOT NULL CONSTRAINT [DF__nm_endorsement_patterns__nmep_sys] DEFAULT ((0)),
[nmep_all_products_families] [bit] NOT NULL CONSTRAINT [DF__nm_endorsement_patterns__nmep_all_products_families] DEFAULT ((0)),
[nmep_all_product_families_premium_refundable] [bit] NOT NULL CONSTRAINT [DF__nm_endorsement_patterns__nmep_all_product_families_premium_refundable] DEFAULT ((0))
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[nm_endorsement_patterns] ADD CONSTRAINT [CK__nm_endorsement_patterns_nmep_endorsmentpattern_code__string_pk] CHECK ((len(ltrim(rtrim([nmep_endorsmentpattern_code])))>(0) AND charindex('*',[nmep_endorsmentpattern_code])=(0) AND charindex('%',[nmep_endorsmentpattern_code])=(0)))
GO
ALTER TABLE [dbo].[nm_endorsement_patterns] ADD CONSTRAINT [PK_nm_endorsement_patterns] PRIMARY KEY CLUSTERED  ([nmep_act_code], [nmep_endorsmentpattern_code]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[nm_endorsement_patterns] ADD CONSTRAINT [FK_nm_endorsement_patterns_pol_acts] FOREIGN KEY ([nmep_act_code]) REFERENCES [dbo].[pol_acts] ([pact_act_code])
GO
