CREATE TABLE [dbo].[claim_claims]
(
[cl_claim_id] [int] NOT NULL,
[cl_rowver] [timestamp] NOT NULL,
[cl_policy_situation_id] [int] NOT NULL,
[cl_risk_situation_id] [int] NULL,
[cl_internal_reference] [dbo].[ais_code_medium] NOT NULL,
[cl_external_reference] [dbo].[ais_code_long] NULL,
[cl_sequence_code] [dbo].[ais_code_short] NULL,
[cl_mvt_code] [dbo].[ais_code_short] NOT NULL,
[cl_smvt_code] [dbo].[ais_code_short] NULL,
[cl_occurence_date] [datetime] NOT NULL,
[cl_declaration_date] [date] NOT NULL,
[cl_user_code] [dbo].[ais_code_medium] NOT NULL,
[cl_date_setup] [datetime] NOT NULL,
[cl_freeze] [bit] NOT NULL CONSTRAINT [DF__claim_claims__cl_freeze] DEFAULT ((0)),
[cl_closing_date] [date] NULL,
[cl_damages] [bit] NOT NULL CONSTRAINT [DF__claim_claims__cl_damages] DEFAULT ((0)),
[cl_injuries] [bit] NOT NULL CONSTRAINT [DF__claim_claims__cl_injuries] DEFAULT ((0)),
[cl_branch_id] [int] NOT NULL,
[cl_department_code] [dbo].[ais_code_medium] NOT NULL,
[cl_responsability_percent] [dbo].[ais_decimal2] NOT NULL CONSTRAINT [DF_claim_claims_cl_responsability_percent] DEFAULT ((100)),
[cl_address1__1] [nvarchar] (64) COLLATE French_CI_AS NULL,
[cl_address2__1] [nvarchar] (64) COLLATE French_CI_AS NULL,
[cl_address3__1] [nvarchar] (64) COLLATE French_CI_AS NULL,
[cl_country_code__1] [dbo].[ais_code_short] NULL,
[cl_dpi_code__1] [dbo].[ais_code_short] NULL,
[cl_postcode_code__1] [dbo].[ais_code_medium] NULL,
[cl_city__1] [nvarchar] (64) COLLATE French_CI_AS NULL,
[cl_address1__2] [nvarchar] (64) COLLATE French_CI_AS NULL,
[cl_address2__2] [nvarchar] (64) COLLATE French_CI_AS NULL,
[cl_address3__2] [nvarchar] (64) COLLATE French_CI_AS NULL,
[cl_country_code__2] [dbo].[ais_code_short] NULL,
[cl_dpi_code__2] [dbo].[ais_code_short] NULL,
[cl_postcode_code__2] [dbo].[ais_code_medium] NULL,
[cl_city__2] [nvarchar] (64) COLLATE French_CI_AS NULL,
[cl_address1__3] [nvarchar] (64) COLLATE French_CI_AS NULL,
[cl_address2__3] [nvarchar] (64) COLLATE French_CI_AS NULL,
[cl_address3__3] [nvarchar] (64) COLLATE French_CI_AS NULL,
[cl_country_code__3] [dbo].[ais_code_short] NULL,
[cl_dpi_code__3] [dbo].[ais_code_short] NULL,
[cl_postcode_code__3] [dbo].[ais_code_medium] NULL,
[cl_city__3] [nvarchar] (64) COLLATE French_CI_AS NULL,
[cl_convention_code] [dbo].[ais_code_short] NULL,
[cl_nature_code] [dbo].[ais_code_short] NULL,
[cl_obs] [nvarchar] (64) COLLATE French_CI_AS NULL,
[cl_circonstances] [nvarchar] (1024) COLLATE French_CI_AS NULL,
[cl_event_code] [dbo].[ais_code_medium] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[claim_claims] ADD CONSTRAINT [CK_claim_claims_damages_injuries] CHECK (([cl_damages]<>(0) OR [cl_injuries]<>(0)))
GO
ALTER TABLE [dbo].[claim_claims] ADD CONSTRAINT [CK_claim_claims_occurence_declaration] CHECK (([cl_declaration_date]>=CONVERT([date],[cl_occurence_date],(0))))
GO
ALTER TABLE [dbo].[claim_claims] ADD CONSTRAINT [CK_claim_claims_setup_occurence] CHECK (([cl_occurence_date]<=[cl_date_setup]))
GO
ALTER TABLE [dbo].[claim_claims] ADD CONSTRAINT [CK_claim_claims_cl_responsability_percent] CHECK (([cl_responsability_percent]>=(0) AND [cl_responsability_percent]<=(100)))
GO
ALTER TABLE [dbo].[claim_claims] ADD CONSTRAINT [CK_claim_claims_closing_declaration] CHECK ((isnull([cl_closing_date],'01/01/2100')>=[cl_declaration_date]))
GO
ALTER TABLE [dbo].[claim_claims] ADD CONSTRAINT [PK_claim_claims] PRIMARY KEY CLUSTERED  ([cl_claim_id]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[claim_claims] ADD CONSTRAINT [FK_claim_claims_act_branches_departments] FOREIGN KEY ([cl_branch_id], [cl_department_code]) REFERENCES [dbo].[act_branches_departments] ([brdep_branch_id], [brdep_department_code])
GO
ALTER TABLE [dbo].[claim_claims] ADD CONSTRAINT [FK_claim_claims_claim_events] FOREIGN KEY ([cl_event_code]) REFERENCES [dbo].[claim_events] ([evt_event_code])
GO
ALTER TABLE [dbo].[claim_claims] ADD CONSTRAINT [FK_claim_claims_claim_mvts] FOREIGN KEY ([cl_mvt_code]) REFERENCES [dbo].[claim_mvts] ([cmvt_mvt_code])
GO
ALTER TABLE [dbo].[claim_claims] ADD CONSTRAINT [FK_claim_claims_claim_smvts] FOREIGN KEY ([cl_mvt_code], [cl_smvt_code]) REFERENCES [dbo].[claim_smvts] ([csmvt_mvt_code], [csmvt_smvt_code])
GO
ALTER TABLE [dbo].[claim_claims] ADD CONSTRAINT [FK_claim_claims_nm_claims_conventions] FOREIGN KEY ([cl_convention_code]) REFERENCES [dbo].[nm_claims_conventions] ([cnv_convention_code])
GO
ALTER TABLE [dbo].[claim_claims] ADD CONSTRAINT [FK_claim_claims_nm_claims_natures] FOREIGN KEY ([cl_convention_code], [cl_nature_code]) REFERENCES [dbo].[nm_claims_natures] ([nat_convention_code], [nat_nature_code])
GO
ALTER TABLE [dbo].[claim_claims] ADD CONSTRAINT [FK_claim_claims_nm_countries_departments] FOREIGN KEY ([cl_country_code__1], [cl_dpi_code__1]) REFERENCES [dbo].[nm_countries_departments] ([nmctrd_country_code], [nmctrd_dpi_code])
GO
ALTER TABLE [dbo].[claim_claims] ADD CONSTRAINT [FK_claim_claims_pol_policies_situations] FOREIGN KEY ([cl_policy_situation_id]) REFERENCES [dbo].[pol_policies_situations] ([pols_policy_situation_id])
GO
ALTER TABLE [dbo].[claim_claims] ADD CONSTRAINT [FK_claim_claims_ris_risks_situations] FOREIGN KEY ([cl_risk_situation_id]) REFERENCES [dbo].[ris_risks_situations] ([riss_risk_situation_id])
GO
ALTER TABLE [dbo].[claim_claims] ADD CONSTRAINT [FK_claim_claims_sq_sequences] FOREIGN KEY ([cl_sequence_code]) REFERENCES [dbo].[sq_sequences] ([seq_sequence_code])
GO
ALTER TABLE [dbo].[claim_claims] ADD CONSTRAINT [FK_claim_claims_usr_users] FOREIGN KEY ([cl_user_code]) REFERENCES [dbo].[usr_users] ([usr_user_code])
GO
EXEC sp_addextendedproperty N'MS_Description', N'Check responsability percent', 'SCHEMA', N'dbo', 'TABLE', N'claim_claims', 'CONSTRAINT', N'CK_claim_claims_cl_responsability_percent'
GO
EXEC sp_addextendedproperty N'MS_Description', N'date declaration > occurence date', 'SCHEMA', N'dbo', 'TABLE', N'claim_claims', 'CONSTRAINT', N'CK_claim_claims_occurence_declaration'
GO
