CREATE TABLE [dbo].[pr_products_variables]
(
[prdv_variable_id] [int] NOT NULL,
[prdv_product_code] [dbo].[ais_code_short] NOT NULL,
[prdv_variable_code] [dbo].[ais_code_short] NOT NULL,
[prdv_variable_date_setup] [date] NOT NULL,
[prdv_family_code] [dbo].[ais_code_short] NOT NULL,
[prdv_ignored] [bit] NOT NULL CONSTRAINT [DF__pr_products_variables__prdv_ignored] DEFAULT ((0)),
[prdv_company_id] [int] NULL,
[prdv_treaty_id] [int] NULL,
[prdv_excel_sheet_name] [nvarchar] (10) COLLATE French_CI_AS NULL,
[prdv_commission_fixed_value] [bit] NOT NULL CONSTRAINT [DF__pr_products_variables__prdv_commission_fixed_value] DEFAULT ((0)),
[prdv_commission_value] [dbo].[ais_decimal4] NULL,
[prdv_splittable] [bit] NOT NULL CONSTRAINT [DF__pr_products_variables__prdv_splittable] DEFAULT ((0)),
[prdv_bm] [bit] NOT NULL CONSTRAINT [DF__pr_products_variables__prdv_bm] DEFAULT ((0)),
[prdv_coef_monthly] [dbo].[ais_decimal4] NOT NULL CONSTRAINT [DF_pr_products_variables_prdv_coef_monthly] DEFAULT ((1)),
[prdv_coef_quarterly] [dbo].[ais_decimal4] NOT NULL CONSTRAINT [DF_pr_products_variables_prdv_coef_quarterly] DEFAULT ((1)),
[prdv_coef_semi_annual] [dbo].[ais_decimal4] NOT NULL CONSTRAINT [DF_pr_products_variables_prdv_coef_semi_annual] DEFAULT ((1)),
[prdv_coef] [dbo].[ais_decimal4] NOT NULL CONSTRAINT [DF_pr_products_variables_prdv_coef] DEFAULT ((1)),
[prdv_prorated_amd] [bit] NOT NULL CONSTRAINT [DF__pr_products_variables__prdv_prorated_amd] DEFAULT ((0)),
[prdv_renewal_subscription_only] [bit] NOT NULL CONSTRAINT [DF__pr_products_variables__prdv_renewal_subscription_only] DEFAULT ((0)),
[prdv_reductible] [bit] NOT NULL CONSTRAINT [DF__pr_products_variables__prdv_reductible] DEFAULT ((0)),
[prdv_retroceded] [bit] NOT NULL CONSTRAINT [DF__pr_products_variables__prdv_retroceded] DEFAULT ((0)),
[prdv_index_franchise] [bit] NOT NULL CONSTRAINT [DF__pr_products_variables__prdv_index_franchise] DEFAULT ((0)),
[prdv_index_capital] [bit] NOT NULL CONSTRAINT [DF__pr_products_variables__prdv_index_capital] DEFAULT ((0)),
[prdv_refoundable] [bit] NOT NULL CONSTRAINT [DF__pr_products_variables__prdv_refoundable] DEFAULT ((0)),
[prdv_refoundable_terminated] [bit] NOT NULL CONSTRAINT [DF__pr_products_variables__prdv_refoundable_terminated] DEFAULT ((0)),
[prdv_premium] [dbo].[ais_decimal2] NULL,
[prdv_fmt_code__premium] [dbo].[ais_code_short] NULL,
[prdv_franchise_mini] [dbo].[ais_decimal2] NULL,
[prdv_fmt_code__franchise_mini] [dbo].[ais_code_short] NULL,
[prdv_franchise_maxi] [dbo].[ais_decimal2] NULL,
[prdv_fmt_code__franchise_maxi] [dbo].[ais_code_short] NULL,
[prdv_franchise] [dbo].[ais_decimal2] NULL,
[prdv_fmt_code__franchise] [dbo].[ais_code_short] NULL,
[prdv_capital] [dbo].[ais_decimal2] NULL,
[prdv_fmt_code__capital] [dbo].[ais_code_short] NULL,
[prdv_met_code] [dbo].[ais_code_short] NULL,
[prdv_umr_code] [dbo].[ais_code_long] NULL,
[prdv_index_premium] [bit] NOT NULL CONSTRAINT [DF__pr_products_variables__prdv_index_premium] DEFAULT ((0)),
[prdv_type_code__mbcomm] [dbo].[ais_code_short] NULL
) ON [PRIMARY]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE TRIGGER [dbo].[t_pr_products_variables__d]
   ON  [dbo].[pr_products_variables]
   AFTER DELETE
AS 
BEGIN

	SET NOCOUNT ON;
	/*
	DGE - cannot delete the first version of a variable
	26/11/2014 - on autorise la suppression des variables avec un code famille != du code famille du produit.
	Cela déclenche une suppression en cascade: 
		pr_products_variables_branches, 
		pr_products_variables_taxes,
		pr_products_variables_commission_taxes, 
		pr_products_variables_introducers
	*/
	
	IF	NOT EXISTS(
		SELECT	1
		FROM	pr_products_variables v
				INNER JOIN deleted d	on	v.prdv_product_code = d.prdv_product_code
										and	v.prdv_variable_code = d.prdv_variable_code 
										and	v.prdv_variable_date_setup < d.prdv_variable_date_setup )
		
		/*	
			exclure les variables qui sont sur une famille différente de celle du produit (cas d'un changement
			de famille
		*/
		AND EXISTS (
		SELECT	1
		FROM	deleted d
				inner join pr_products p	on	p.prd_product_code = d.prdv_product_code
											and	p.prd_family_code = d.prdv_family_code
		)

	BEGIN
	
		RAISERROR('Cannot delete the first version of a variable !', 16, 1)
		ROLLBACK TRANSACTION
		RETURN
		
	END
	

END


GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE TRIGGER [dbo].[t_pr_products_variables__u]
   ON  [dbo].[pr_products_variables]
   AFTER UPDATE
AS 
BEGIN

	SET NOCOUNT ON;
	
	/*
	DGE - check if the variable is already used in a policy or risk situation
	*/
	
	IF EXISTS(
		SELECT	1
		FROM	deleted
		WHERE	0 != [dbo].[f_variable_used_by_policy_or_risk](prdv_product_code, prdv_variable_code, prdv_variable_date_setup))
	BEGIN
	
		RAISERROR('Cannot update a variable used in a policy or risk situation', 16, 1)
		ROLLBACK TRANSACTION
		RETURN
		
	END
	

END

GO
ALTER TABLE [dbo].[pr_products_variables] ADD CONSTRAINT [CK_pr_products_variables_prdv_company_id_prdv_treaty_id] CHECK ((isnull([prdv_company_id],(0))=(0) OR isnull([prdv_treaty_id],(0))=(0)))
GO
ALTER TABLE [dbo].[pr_products_variables] ADD CONSTRAINT [CK__pr_products_variables_prdv_product_code__string_pk] CHECK ((len(ltrim(rtrim([prdv_product_code])))>(0) AND charindex('*',[prdv_product_code])=(0) AND charindex('%',[prdv_product_code])=(0)))
GO
ALTER TABLE [dbo].[pr_products_variables] ADD CONSTRAINT [CK__pr_products_variables_prdv_variable_code__string_pk] CHECK ((len(ltrim(rtrim([prdv_variable_code])))>(0) AND charindex('*',[prdv_variable_code])=(0) AND charindex('%',[prdv_variable_code])=(0)))
GO
ALTER TABLE [dbo].[pr_products_variables] ADD CONSTRAINT [PK_pr_products_variables] PRIMARY KEY CLUSTERED  ([prdv_variable_id]) ON [PRIMARY]
GO
CREATE UNIQUE NONCLUSTERED INDEX [IX_pk_pr_products_variables_product_variable] ON [dbo].[pr_products_variables] ([prdv_product_code], [prdv_variable_code], [prdv_variable_date_setup]) ON [PRIMARY]
GO
CREATE UNIQUE NONCLUSTERED INDEX [IX_pr_products_variables] ON [dbo].[pr_products_variables] ([prdv_variable_id], [prdv_product_code]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[pr_products_variables] ADD CONSTRAINT [FK_pr_products_variables_act_treaties] FOREIGN KEY ([prdv_treaty_id]) REFERENCES [dbo].[act_treaties] ([trt_actor_id])
GO
ALTER TABLE [dbo].[pr_products_variables] ADD CONSTRAINT [FK_pr_products_variables_cts_methods] FOREIGN KEY ([prdv_met_code]) REFERENCES [dbo].[cts_methods] ([ctsm_met_code])
GO
ALTER TABLE [dbo].[pr_products_variables] ADD CONSTRAINT [FK_pr_products_variables_nm_formats] FOREIGN KEY ([prdv_fmt_code__premium]) REFERENCES [dbo].[nm_formats] ([fmt_fmt_code])
GO
ALTER TABLE [dbo].[pr_products_variables] ADD CONSTRAINT [FK_pr_products_variables_nm_formats1] FOREIGN KEY ([prdv_fmt_code__franchise_mini]) REFERENCES [dbo].[nm_formats] ([fmt_fmt_code])
GO
ALTER TABLE [dbo].[pr_products_variables] ADD CONSTRAINT [FK_pr_products_variables_nm_formats2] FOREIGN KEY ([prdv_fmt_code__franchise_maxi]) REFERENCES [dbo].[nm_formats] ([fmt_fmt_code])
GO
ALTER TABLE [dbo].[pr_products_variables] ADD CONSTRAINT [FK_pr_products_variables_nm_formats3] FOREIGN KEY ([prdv_fmt_code__franchise]) REFERENCES [dbo].[nm_formats] ([fmt_fmt_code])
GO
ALTER TABLE [dbo].[pr_products_variables] ADD CONSTRAINT [FK_pr_products_variables_nm_formats4] FOREIGN KEY ([prdv_fmt_code__capital]) REFERENCES [dbo].[nm_formats] ([fmt_fmt_code])
GO
ALTER TABLE [dbo].[pr_products_variables] ADD CONSTRAINT [FK_pr_products_variables_nm_masterbroker_comm_types] FOREIGN KEY ([prdv_type_code__mbcomm]) REFERENCES [dbo].[nm_masterbroker_comm_types] ([mbct_type_code])
GO
ALTER TABLE [dbo].[pr_products_variables] ADD CONSTRAINT [FK_pr_products_variables_nm_umr1] FOREIGN KEY ([prdv_umr_code]) REFERENCES [dbo].[nm_umr] ([umr_umr_code])
GO
ALTER TABLE [dbo].[pr_products_variables] ADD CONSTRAINT [FK_pr_products_variables_pr_families_cies] FOREIGN KEY ([prdv_family_code], [prdv_company_id]) REFERENCES [dbo].[pr_families_cies] ([prfc_family_code], [prfc_company_id])
GO
ALTER TABLE [dbo].[pr_products_variables] ADD CONSTRAINT [FK_pr_products_variables_pr_families_variables] FOREIGN KEY ([prdv_family_code], [prdv_variable_code]) REFERENCES [dbo].[pr_families_variables] ([pfv_family_code], [pfv_variable_code]) ON UPDATE CASCADE
GO
ALTER TABLE [dbo].[pr_products_variables] ADD CONSTRAINT [FK_pr_products_variables_pr_products] FOREIGN KEY ([prdv_product_code]) REFERENCES [dbo].[pr_products] ([prd_product_code])
GO
