CREATE TABLE [dbo].[pr_products]
(
[prd_product_code] [dbo].[ais_code_short] NOT NULL,
[prd_l1] [nvarchar] (64) COLLATE French_CI_AS NOT NULL,
[prd_l2] [nvarchar] (64) COLLATE French_CI_AS NULL,
[prd_l3] [nvarchar] (64) COLLATE French_CI_AS NULL,
[prd_freeze] [bit] NOT NULL CONSTRAINT [DF__pr_products__prd_freeze] DEFAULT ((0)),
[prd_family_code] [dbo].[ais_code_short] NOT NULL,
[prd_company_id] [int] NOT NULL,
[prd_date_begin] [date] NOT NULL,
[prd_date_end] [date] NULL,
[prd_mvt_code__quotation] [dbo].[ais_code_short] NOT NULL,
[prd_mvt_code__subscription] [dbo].[ais_code_short] NOT NULL,
[prd_mvt_code__amend_quotation] [dbo].[ais_code_short] NOT NULL,
[prd_mvt_code__amend] [dbo].[ais_code_short] NOT NULL,
[prd_sequence_code__quotation] [dbo].[ais_code_short] NOT NULL,
[prd_sequence_code__subscription] [dbo].[ais_code_short] NOT NULL,
[prd_sequence_code__amend_quotation] [dbo].[ais_code_short] NOT NULL,
[prd_quotation_valid_days] [int] NOT NULL CONSTRAINT [DF_pr_products_prd_quotation_valid_days] DEFAULT ((0)),
[prd_invoice_db] [bit] NOT NULL CONSTRAINT [DF__pr_products__prd_invoice_db] DEFAULT ((0)),
[prd_chain_code] [dbo].[ais_code_short] NULL,
[prd_default_formula_code] [dbo].[ais_code_short] NULL,
[prd_date_term] [varchar] (5) COLLATE French_CI_AS NULL,
[prd_date_term01] [varchar] (5) COLLATE French_CI_AS NULL,
[prd_date_term02] [varchar] (5) COLLATE French_CI_AS NULL,
[prd_date_term03] [varchar] (5) COLLATE French_CI_AS NULL,
[prd_date_term04] [varchar] (5) COLLATE French_CI_AS NULL,
[prd_date_term05] [varchar] (5) COLLATE French_CI_AS NULL,
[prd_date_term06] [varchar] (5) COLLATE French_CI_AS NULL,
[prd_date_term07] [varchar] (5) COLLATE French_CI_AS NULL,
[prd_date_term08] [varchar] (5) COLLATE French_CI_AS NULL,
[prd_date_term09] [varchar] (5) COLLATE French_CI_AS NULL,
[prd_date_term10] [varchar] (5) COLLATE French_CI_AS NULL,
[prd_date_term11] [varchar] (5) COLLATE French_CI_AS NULL,
[prd_date_term12] [varchar] (5) COLLATE French_CI_AS NULL,
[prd_days_minus] [smallint] NULL,
[prd_days_plus] [smallint] NULL,
[prd_days_proportion_mini] [smallint] NULL,
[prd_monthly_payment_yesno_code] [varchar] (1) COLLATE French_CI_AS NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[pr_products] ADD CONSTRAINT [CK_pr_products_pf_days_proportion_mini] CHECK ((isnull([prd_days_proportion_mini],(0))>=(0) AND isnull([prd_days_proportion_mini],(0))<=(364)))
GO
ALTER TABLE [dbo].[pr_products] ADD CONSTRAINT [CK__pr_products_prd_product_code__string_pk] CHECK ((len(ltrim(rtrim([prd_product_code])))>(0) AND charindex('*',[prd_product_code])=(0) AND charindex('%',[prd_product_code])=(0)))
GO
ALTER TABLE [dbo].[pr_products] ADD CONSTRAINT [PK_pr_products] PRIMARY KEY CLUSTERED  ([prd_product_code]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[pr_products] ADD CONSTRAINT [FK_pr_products_cts_chains] FOREIGN KEY ([prd_chain_code]) REFERENCES [dbo].[cts_chains] ([ctsch_chain_code])
GO
ALTER TABLE [dbo].[pr_products] ADD CONSTRAINT [FK_pr_products_nm_yesno_values] FOREIGN KEY ([prd_monthly_payment_yesno_code]) REFERENCES [dbo].[nm_yesno_values] ([nmvyn_yesno_code])
GO
ALTER TABLE [dbo].[pr_products] ADD CONSTRAINT [FK_pr_products_pol_mvts] FOREIGN KEY ([prd_mvt_code__quotation]) REFERENCES [dbo].[pol_mvts] ([pmvt_mvt_code])
GO
ALTER TABLE [dbo].[pr_products] ADD CONSTRAINT [FK_pr_products_pol_mvts1] FOREIGN KEY ([prd_mvt_code__subscription]) REFERENCES [dbo].[pol_mvts] ([pmvt_mvt_code])
GO
ALTER TABLE [dbo].[pr_products] ADD CONSTRAINT [FK_pr_products_pol_mvts2] FOREIGN KEY ([prd_mvt_code__amend_quotation]) REFERENCES [dbo].[pol_mvts] ([pmvt_mvt_code])
GO
ALTER TABLE [dbo].[pr_products] ADD CONSTRAINT [FK_pr_products_pol_mvts3] FOREIGN KEY ([prd_mvt_code__amend]) REFERENCES [dbo].[pol_mvts] ([pmvt_mvt_code])
GO
ALTER TABLE [dbo].[pr_products] ADD CONSTRAINT [FK_pr_products_pr_families_cies] FOREIGN KEY ([prd_family_code], [prd_company_id]) REFERENCES [dbo].[pr_families_cies] ([prfc_family_code], [prfc_company_id]) ON UPDATE CASCADE
GO
ALTER TABLE [dbo].[pr_products] ADD CONSTRAINT [FK_pr_products_pr_families_formulas] FOREIGN KEY ([prd_family_code], [prd_default_formula_code]) REFERENCES [dbo].[pr_families_formulas] ([pff_family_code], [pff_formula_code])
GO
ALTER TABLE [dbo].[pr_products] ADD CONSTRAINT [FK_pr_products_sq_sequences] FOREIGN KEY ([prd_sequence_code__quotation]) REFERENCES [dbo].[sq_sequences] ([seq_sequence_code])
GO
ALTER TABLE [dbo].[pr_products] ADD CONSTRAINT [FK_pr_products_sq_sequences1] FOREIGN KEY ([prd_sequence_code__subscription]) REFERENCES [dbo].[sq_sequences] ([seq_sequence_code])
GO
ALTER TABLE [dbo].[pr_products] ADD CONSTRAINT [FK_pr_products_sq_sequences2] FOREIGN KEY ([prd_sequence_code__amend_quotation]) REFERENCES [dbo].[sq_sequences] ([seq_sequence_code])
GO
