CREATE TABLE [dbo].[pol_policies_situations]
(
[pols_policy_situation_id] [int] NOT NULL,
[pols_policy_id] [int] NOT NULL,
[pols_rowver] [timestamp] NOT NULL,
[pols_number] [dbo].[ais_code_long] NOT NULL,
[pols_sequence_code] [dbo].[ais_code_short] NOT NULL,
[pols_mvt_date] [date] NOT NULL,
[pols_mvt_code] [dbo].[ais_code_short] NOT NULL,
[pols_end_date] [date] NULL,
[pols_period_code] [dbo].[ais_code_short] NULL,
[pols_act_code] [dbo].[ais_code_short] NOT NULL,
[pols_operation_code] [dbo].[ais_code_long] NOT NULL,
[pols_operation_utc_date] [datetime] NOT NULL,
[pols_renew_type_code] [dbo].[ais_code_short] NOT NULL,
[pols_renew_split_code] [dbo].[ais_code_short] NOT NULL,
[pols_term_date] [varchar] (5) COLLATE French_CI_AS NOT NULL,
[pols_policy_situation_id_old] [int] NULL,
[pols_next_renewal_date] [date] NULL,
[pols_next_principal_renewal_date] [date] NULL,
[pols_last_principal_renewal_date] [date] NULL,
[pols_next_renewal_debit] AS (datepart(year,[pols_next_renewal_date])*(100)+datepart(month,[pols_next_renewal_date])),
[pols_user_code] [dbo].[ais_code_medium] NOT NULL,
[pols_batch_id] [int] NULL,
[pols_quotation_expiration_date] [date] NULL,
[pols_ann_tf] [dbo].[ais_decimal2] NULL,
[pols_ann_taxes] [dbo].[ais_decimal2] NULL,
[pols_ann_ti] [dbo].[ais_decimal2] NULL,
[pols_bonus_value] [dbo].[ais_decimal2] NULL,
[pols_bonus50_years] [tinyint] NULL,
[pols_index_code] [dbo].[ais_code_medium] NULL,
[pols_index_previous_value] [dbo].[ais_decimal4] NULL,
[pols_index_current_value] [dbo].[ais_decimal4] NULL,
[pols_monthly_ti] [dbo].[ais_decimal2] NULL,
[pols_quaterly_ti] [dbo].[ais_decimal2] NULL,
[pols_biannual_ti] [dbo].[ais_decimal2] NULL,
[pols_incomplete] [bit] NOT NULL CONSTRAINT [DF__pol_policies_situations__pols_incomplete] DEFAULT ((0)),
[pols_endorsmentpattern_code] [dbo].[ais_code_short] NULL,
[pols_next_principal_renewal_debit] AS (datepart(year,[pols_next_principal_renewal_date])*(100)+datepart(month,[pols_next_principal_renewal_date])),
[pols_input_data_dump_header_id] [int] NULL,
[pols_custom_number] [bit] NOT NULL CONSTRAINT [DF__pol_policies_situations__pols_custom_number] DEFAULT ((0)),
[pols_period_number] [smallint] NULL,
[pols_endorsment_description] [nvarchar] (255) COLLATE French_CI_AS NULL,
[pols_monthly_payments] [bit] NOT NULL CONSTRAINT [DF__pol_policies_situations__pols_monthly_payments] DEFAULT ((0)),
[pols_do_not_generate_policy_fees] [bit] NOT NULL CONSTRAINT [DF__pol_policies_situations__pols_do_not_generate_policy_fees] DEFAULT ((0))
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[pol_policies_situations] ADD CONSTRAINT [CK_pol_policies_situations_pols_term_date] CHECK ((len([pols_term_date])=(5)))
GO
ALTER TABLE [dbo].[pol_policies_situations] ADD CONSTRAINT [PK_pol_policies_situations] PRIMARY KEY CLUSTERED  ([pols_policy_situation_id]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_pol_policies_situations_pols_next_renewal_debit] ON [dbo].[pol_policies_situations] ([pols_next_renewal_debit]) ON [PRIMARY]
GO
CREATE UNIQUE NONCLUSTERED INDEX [IX_pol_policies_situations_policy_id] ON [dbo].[pol_policies_situations] ([pols_policy_situation_id], [pols_policy_id]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[pol_policies_situations] ADD CONSTRAINT [FK_pol_policies_situations_ba_batches] FOREIGN KEY ([pols_batch_id]) REFERENCES [dbo].[ba_batches] ([ba_batch_id])
GO
ALTER TABLE [dbo].[pol_policies_situations] ADD CONSTRAINT [FK_pol_policies_situations_input_data_dump_header] FOREIGN KEY ([pols_input_data_dump_header_id]) REFERENCES [dbo].[input_data_dump_header] ([iddh_header_id])
GO
ALTER TABLE [dbo].[pol_policies_situations] ADD CONSTRAINT [FK_pol_policies_situations_nm_endorsement_patterns] FOREIGN KEY ([pols_act_code], [pols_endorsmentpattern_code]) REFERENCES [dbo].[nm_endorsement_patterns] ([nmep_act_code], [nmep_endorsmentpattern_code])
GO
ALTER TABLE [dbo].[pol_policies_situations] ADD CONSTRAINT [FK_pol_policies_situations_nm_indices] FOREIGN KEY ([pols_index_code]) REFERENCES [dbo].[nm_indices] ([idx_index_code])
GO
ALTER TABLE [dbo].[pol_policies_situations] ADD CONSTRAINT [FK_pol_policies_situations_nm_pol_operations] FOREIGN KEY ([pols_operation_code]) REFERENCES [dbo].[nm_pol_operations] ([nmpop_operation_code])
GO
ALTER TABLE [dbo].[pol_policies_situations] ADD CONSTRAINT [FK_pol_policies_situations_nm_pol_renew_types] FOREIGN KEY ([pols_renew_type_code]) REFERENCES [dbo].[nm_pol_renew_types] ([nmprt_renew_type_code])
GO
ALTER TABLE [dbo].[pol_policies_situations] ADD CONSTRAINT [FK_pol_policies_situations_nm_pol_renew_types_split] FOREIGN KEY ([pols_renew_type_code], [pols_renew_split_code]) REFERENCES [dbo].[nm_pol_renew_types_split] ([nmprts_renew_type_code], [nmprts_renew_split_code])
GO
ALTER TABLE [dbo].[pol_policies_situations] ADD CONSTRAINT [FK_pol_policies_situations_pol_acts] FOREIGN KEY ([pols_act_code]) REFERENCES [dbo].[pol_acts] ([pact_act_code])
GO
ALTER TABLE [dbo].[pol_policies_situations] ADD CONSTRAINT [FK_pol_policies_situations_pol_mvts] FOREIGN KEY ([pols_mvt_code]) REFERENCES [dbo].[pol_mvts] ([pmvt_mvt_code])
GO
ALTER TABLE [dbo].[pol_policies_situations] ADD CONSTRAINT [FK_pol_policies_situations_pol_periods] FOREIGN KEY ([pols_period_code]) REFERENCES [dbo].[pol_periods] ([per_period_code])
GO
ALTER TABLE [dbo].[pol_policies_situations] ADD CONSTRAINT [FK_pol_policies_situations_pol_policies] FOREIGN KEY ([pols_policy_id]) REFERENCES [dbo].[pol_policies] ([pol_policy_id])
GO
ALTER TABLE [dbo].[pol_policies_situations] ADD CONSTRAINT [FK_pol_policies_situations_sq_sequences] FOREIGN KEY ([pols_sequence_code]) REFERENCES [dbo].[sq_sequences] ([seq_sequence_code])
GO
ALTER TABLE [dbo].[pol_policies_situations] ADD CONSTRAINT [FK_pol_policies_situations_usr_users] FOREIGN KEY ([pols_user_code]) REFERENCES [dbo].[usr_users] ([usr_user_code])
GO
