CREATE TABLE [dbo].[ris_risks_situations]
(
[riss_risk_situation_id] [int] NOT NULL,
[riss_risk_id] [int] NOT NULL,
[riss_rowver] [timestamp] NOT NULL,
[riss_policy_id] [int] NOT NULL,
[riss_policy_situation_id] [int] NOT NULL,
[riss_number] [nvarchar] (128) COLLATE French_CI_AS NOT NULL,
[riss_movement_code] [dbo].[ais_code_short] NOT NULL,
[riss_movement_date] [date] NOT NULL,
[riss_entrance_date] [date] NOT NULL,
[riss_modify_date] [date] NULL,
[riss_retire_date] [date] NULL,
[riss_formula_code] [dbo].[ais_code_short] NULL,
[riss_nature_code] [dbo].[ais_code_short] NULL,
[riss_operation_utc_date] [datetime] NULL,
[riss_lot_acc_id] [int] NULL,
[riss_risk_situation_id_old] [int] NULL,
[riss_obsolete] [bit] NOT NULL CONSTRAINT [DF__ris_risks_situations__riss_obsolete] DEFAULT ((0)),
[riss_bonus_value] [dbo].[ais_decimal2] NULL,
[riss_bonus50_years] [tinyint] NULL,
[riss_address1__1] [nvarchar] (64) COLLATE French_CI_AS NULL,
[riss_address2__1] [nvarchar] (64) COLLATE French_CI_AS NULL,
[riss_address3__1] [nvarchar] (64) COLLATE French_CI_AS NULL,
[riss_country_code__1] [dbo].[ais_code_short] NULL,
[riss_dpi_code__1] [dbo].[ais_code_short] NULL,
[riss_postcode_code__1] [dbo].[ais_code_medium] NULL,
[riss_city__1] [nvarchar] (64) COLLATE French_CI_AS NULL,
[riss_address1__2] [nvarchar] (64) COLLATE French_CI_AS NULL,
[riss_address2__2] [nvarchar] (64) COLLATE French_CI_AS NULL,
[riss_address3__2] [nvarchar] (64) COLLATE French_CI_AS NULL,
[riss_country_code__2] [dbo].[ais_code_short] NULL,
[riss_dpi_code__2] [dbo].[ais_code_short] NULL,
[riss_postcode_code__2] [dbo].[ais_code_medium] NULL,
[riss_city__2] [nvarchar] (64) COLLATE French_CI_AS NULL,
[riss_address1__3] [nvarchar] (64) COLLATE French_CI_AS NULL,
[riss_address2__3] [nvarchar] (64) COLLATE French_CI_AS NULL,
[riss_address3__3] [nvarchar] (64) COLLATE French_CI_AS NULL,
[riss_country_code__3] [dbo].[ais_code_short] NULL,
[riss_dpi_code__3] [dbo].[ais_code_short] NULL,
[riss_postcode_code__3] [dbo].[ais_code_medium] NULL,
[riss_city__3] [nvarchar] (64) COLLATE French_CI_AS NULL,
[riss_perception_invoice_start_date] [date] NULL,
[riss_perception_invoice_end_date] [date] NULL,
[riss_reimboursement_invoice_start_date] [date] NULL,
[riss_reimboursement_invoice_end_date] [date] NULL
) ON [PRIMARY]
ALTER TABLE [dbo].[ris_risks_situations] ADD
CONSTRAINT [FK_ris_risks_situations_nm_countries_departments] FOREIGN KEY ([riss_country_code__1], [riss_dpi_code__1]) REFERENCES [dbo].[nm_countries_departments] ([nmctrd_country_code], [nmctrd_dpi_code])

ALTER TABLE [dbo].[ris_risks_situations] ADD 
CONSTRAINT [PK_ris_risks_situations] PRIMARY KEY CLUSTERED  ([riss_risk_situation_id]) ON [PRIMARY]
ALTER TABLE [dbo].[ris_risks_situations] ADD
CONSTRAINT [CK_ris_risks_situations_riss_bonus50_years] CHECK (([riss_bonus_value] IS NULL AND [riss_bonus50_years] IS NULL OR [riss_bonus_value]=(0.5) AND [riss_bonus50_years]>=(0) OR [riss_bonus_value]>(0.5) AND [riss_bonus50_years] IS NULL))
ALTER TABLE [dbo].[ris_risks_situations] ADD
CONSTRAINT [CK_ris_risks_situations_riss_bonus_value] CHECK ((isnull([riss_bonus_value],(0.5))>=(0.5) AND isnull([riss_bonus_value],(0.5))<=(3.5)))


GO


ALTER TABLE [dbo].[ris_risks_situations] ADD CONSTRAINT [FK_ris_risks_situations_nm_risks_movements] FOREIGN KEY ([riss_movement_code]) REFERENCES [dbo].[nm_risks_movements] ([rmvt_movement_code])
GO
ALTER TABLE [dbo].[ris_risks_situations] ADD CONSTRAINT [FK_ris_risks_situations_pol_policies_situations] FOREIGN KEY ([riss_policy_situation_id], [riss_policy_id]) REFERENCES [dbo].[pol_policies_situations] ([pols_policy_situation_id], [pols_policy_id])
GO
ALTER TABLE [dbo].[ris_risks_situations] ADD CONSTRAINT [FK_ris_risks_situations_ris_risks] FOREIGN KEY ([riss_risk_id]) REFERENCES [dbo].[ris_risks] ([ris_risk_id])
GO
