CREATE TABLE [dbo].[pr_products_variables_branches]
(
[prdvb_variable_id] [int] NOT NULL,
[prdvb_product_code] [dbo].[ais_code_short] NOT NULL,
[prdvb_branch_id] [int] NOT NULL,
[prdvb_premium] [dbo].[ais_decimal2] NULL,
[prdvb_fmt_code__premium] [dbo].[ais_code_short] NULL,
[prdvb_premium_is_required] [bit] NOT NULL CONSTRAINT [DF__pr_products_variables_branches__prdvb_premium_is_required] DEFAULT ((0)),
[prdvb_franchise_mini] [dbo].[ais_decimal2] NULL,
[prdvb_fmt_code__franchise_mini] [dbo].[ais_code_short] NULL,
[prdvb_franchise_mini_is_required] [bit] NOT NULL CONSTRAINT [DF__pr_products_variables_branches__prdvb_franchise_mini_is_required] DEFAULT ((0)),
[prdvb_franchise_maxi] [dbo].[ais_decimal2] NULL,
[prdvb_fmt_code__franchise_maxi] [dbo].[ais_code_short] NULL,
[prdvb_franchise_maxi_is_required] [bit] NOT NULL CONSTRAINT [DF__pr_products_variables_branches__prdvb_franchise_maxi_is_required] DEFAULT ((0)),
[prdvb_franchise] [dbo].[ais_decimal2] NULL,
[prdvb_fmt_code__franchise] [dbo].[ais_code_short] NULL,
[prdvb_franchise_is_required] [bit] NOT NULL CONSTRAINT [DF__pr_products_variables_branches__prdvb_franchise_is_required] DEFAULT ((0)),
[prdvb_capital] [dbo].[ais_decimal2] NULL,
[prdvb_fmt_code__capital] [dbo].[ais_code_short] NULL,
[prdvb_capital_is_required] [bit] NOT NULL CONSTRAINT [DF__pr_products_variables_branches__prdvb_capital_is_required] DEFAULT ((0)),
[prdvb_invoice_premium_tf] [dbo].[ais_decimal2] NULL,
[prdvb_invoice_premium_tf_is_required] [bit] NOT NULL CONSTRAINT [DF__pr_products_variables_branches__prdvb_invoice_premium_tf_is_required] DEFAULT ((0)),
[prdvb_invoice_commission_tf] [dbo].[ais_decimal2] NULL,
[prdvb_invoice_commission_tf_is_required] [bit] NOT NULL CONSTRAINT [DF__pr_products_variables_branches__prdvb_invoice_commission_tf_is_required] DEFAULT ((0)),
[prdvb_ignored] [bit] NOT NULL CONSTRAINT [DF__pr_products_variables_branches__prdvb_ignored] DEFAULT ((0)),
[prdvb_type_code__mbcomm] [dbo].[ais_code_short] NULL,
[prdvb_coef] [dbo].[ais_decimal4] NULL,
[prdvb_coef_monthly] [dbo].[ais_decimal4] NULL,
[prdvb_coef_quarterly] [dbo].[ais_decimal4] NULL,
[prdvb_coef_semi_annual] [dbo].[ais_decimal4] NULL,
[prdvb_commission_value] [dbo].[ais_decimal4] NULL,
[prdvb_commission_fixed_value] [bit] NOT NULL CONSTRAINT [DF__pr_products_variables_branches__prdvb_commission_fixed_value] DEFAULT ((0))
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[pr_products_variables_branches] ADD CONSTRAINT [CK__pr_products_variables_branches_prdvb_product_code__string_pk] CHECK ((len(ltrim(rtrim([prdvb_product_code])))>(0) AND charindex('*',[prdvb_product_code])=(0) AND charindex('%',[prdvb_product_code])=(0)))
GO
ALTER TABLE [dbo].[pr_products_variables_branches] ADD CONSTRAINT [PK_pr_products_variables_branches] PRIMARY KEY CLUSTERED  ([prdvb_variable_id], [prdvb_branch_id]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[pr_products_variables_branches] ADD CONSTRAINT [FK_pr_products_variables_branches_nm_formats] FOREIGN KEY ([prdvb_fmt_code__premium]) REFERENCES [dbo].[nm_formats] ([fmt_fmt_code])
GO
ALTER TABLE [dbo].[pr_products_variables_branches] ADD CONSTRAINT [FK_pr_products_variables_branches_nm_formats1] FOREIGN KEY ([prdvb_fmt_code__franchise_mini]) REFERENCES [dbo].[nm_formats] ([fmt_fmt_code])
GO
ALTER TABLE [dbo].[pr_products_variables_branches] ADD CONSTRAINT [FK_pr_products_variables_branches_nm_formats2] FOREIGN KEY ([prdvb_fmt_code__franchise_maxi]) REFERENCES [dbo].[nm_formats] ([fmt_fmt_code])
GO
ALTER TABLE [dbo].[pr_products_variables_branches] ADD CONSTRAINT [FK_pr_products_variables_branches_nm_formats3] FOREIGN KEY ([prdvb_fmt_code__franchise]) REFERENCES [dbo].[nm_formats] ([fmt_fmt_code])
GO
ALTER TABLE [dbo].[pr_products_variables_branches] ADD CONSTRAINT [FK_pr_products_variables_branches_nm_formats4] FOREIGN KEY ([prdvb_fmt_code__capital]) REFERENCES [dbo].[nm_formats] ([fmt_fmt_code])
GO
ALTER TABLE [dbo].[pr_products_variables_branches] ADD CONSTRAINT [FK_pr_products_variables_branches_nm_masterbroker_comm_types] FOREIGN KEY ([prdvb_type_code__mbcomm]) REFERENCES [dbo].[nm_masterbroker_comm_types] ([mbct_type_code])
GO
ALTER TABLE [dbo].[pr_products_variables_branches] ADD CONSTRAINT [FK_pr_products_variables_branches_pr_products_variables] FOREIGN KEY ([prdvb_variable_id], [prdvb_product_code]) REFERENCES [dbo].[pr_products_variables] ([prdv_variable_id], [prdv_product_code]) ON DELETE CASCADE
GO
