CREATE TABLE [dbo].[CommStmtDetail]
(
[MSID] [int] NOT NULL IDENTITY(1, 1),
[CommStmtVendor] [int] NOT NULL,
[DocumentNumber] [varchar] (21) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[DocumentDate] [datetime] NOT NULL,
[DocumentAmount] [money] NOT NULL,
[OrderNumber] [varchar] (23) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[ShipperName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[InvoiceAmount] [money] NOT NULL CONSTRAINT [DF_CommStmtDetail_InvoiceAmount] DEFAULT ((0)),
[RevenueAmount] [money] NOT NULL CONSTRAINT [DF_CommStmtDetail_RevenueAmount] DEFAULT ((0)),
[CommissionType] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[CommissionBasis] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[CommissionAmount] [money] NOT NULL,
[CommissionPerc] [float] NOT NULL,
[CommissionFactor] [money] NOT NULL,
[OrderWeight] [int] NOT NULL,
[AmountAfterSplits] [money] NOT NULL,
[ItemDescription] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[VoucherNumber] [varchar] (21) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_CommStmtDetail_VoucherNumber] DEFAULT (''),
[DocType] [int] NOT NULL CONSTRAINT [DF_CommStmtDetail_DocType] DEFAULT (0),
[NetAmount] [money] NOT NULL CONSTRAINT [DF_CommStmtDetail_NetAmount] DEFAULT ((0)),
[AllocatedItemDescription] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Miles] [int] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[CommStmtDetail] ADD CONSTRAINT [PK_CommStmtDetail] PRIMARY KEY CLUSTERED  ([MSID]) WITH (FILLFACTOR=80) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_CommStmtDetail_CommStmtVendor] ON [dbo].[CommStmtDetail] ([CommStmtVendor]) WITH (FILLFACTOR=80) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_CommStmtDetail_DocumentNumber] ON [dbo].[CommStmtDetail] ([DocumentNumber], [CommissionType]) INCLUDE ([MSID]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_CommStmtDetail_OrderNumber] ON [dbo].[CommStmtDetail] ([OrderNumber]) WITH (FILLFACTOR=80) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_CommStmtDetail_VoucherNumber] ON [dbo].[CommStmtDetail] ([VoucherNumber]) WITH (FILLFACTOR=80) ON [PRIMARY]
GO
ALTER TABLE [dbo].[CommStmtDetail] ADD CONSTRAINT [FK_CommStmtDetail_CommStmtVendor] FOREIGN KEY ([CommStmtVendor]) REFERENCES [dbo].[CommStmtVendor] ([MSID]) ON DELETE CASCADE
GO
GRANT SELECT ON  [dbo].[CommStmtDetail] TO [MssExec]
GRANT INSERT ON  [dbo].[CommStmtDetail] TO [MssExec]
GRANT DELETE ON  [dbo].[CommStmtDetail] TO [MssExec]
GRANT UPDATE ON  [dbo].[CommStmtDetail] TO [MssExec]
GO