CREATE TABLE [dbo].[CustomerInvoiceReportEDIItem]
(
[CustomerInvoiceReportEDIItemID] [int] NOT NULL IDENTITY(1, 1),
[CustomerInvoiceReportFID] [int] NOT NULL,
[EDIItemCode] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Description] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[UnitOfMeasure1] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[UnitOfMeasure2] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[RevenueGroupFID] [int] NULL,
[ItemCodeFID] [int] NULL,
[PointOfServiceFID] [int] NULL,
[LocationCode1] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[LocationCode2] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[EDIMileageTypeFID] [int] NULL,
[AlternateWeightRequired] [bit] NULL,
[ItemVerificationRequired] [bit] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[CustomerInvoiceReportEDIItem] ADD CONSTRAINT [CK_CustomerInvoiceReportEDIItem_ItemCode_RevenueGroup] CHECK ((NOT ([ItemCodeFID] IS NOT NULL AND [RevenueGroupFID] IS NOT NULL)))
GO
ALTER TABLE [dbo].[CustomerInvoiceReportEDIItem] ADD CONSTRAINT [PK_CustomerInvoiceReportEDIItem] PRIMARY KEY CLUSTERED  ([CustomerInvoiceReportEDIItemID]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_CustomerInvoiceReportEDIItem_Report_EDIItemCode_ItemCode] ON [dbo].[CustomerInvoiceReportEDIItem] ([CustomerInvoiceReportFID], [EDIItemCode], [ItemCodeFID], [PointOfServiceFID], [Description], [CustomerInvoiceReportEDIItemID]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_CustomerInvoiceReportEDIItem_Report_ItemCode] ON [dbo].[CustomerInvoiceReportEDIItem] ([CustomerInvoiceReportFID], [ItemCodeFID]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_CustomerInvoiceReportEDIItem_Report_RevenueGroup_ItemCode] ON [dbo].[CustomerInvoiceReportEDIItem] ([CustomerInvoiceReportFID], [RevenueGroupFID], [ItemCodeFID]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[CustomerInvoiceReportEDIItem] ADD CONSTRAINT [FK_CustomerInvoiceReportEDIItem_CustomerInvoiceReportFID] FOREIGN KEY ([CustomerInvoiceReportFID]) REFERENCES [dbo].[CustomerInvoiceReport] ([CustomerInvoiceReportID])
GO
ALTER TABLE [dbo].[CustomerInvoiceReportEDIItem] ADD CONSTRAINT [FK_CustomerInvoiceReportEDIItem_EDIMileageTypeFID] FOREIGN KEY ([EDIMileageTypeFID]) REFERENCES [dbo].[EDIMileageType] ([EDIMileageTypeID])
GO
ALTER TABLE [dbo].[CustomerInvoiceReportEDIItem] ADD CONSTRAINT [FK_CustomerInvoiceReportEDIItem_ItemCodeFID] FOREIGN KEY ([ItemCodeFID]) REFERENCES [dbo].[ItemCode] ([ICPriKey])
GO
ALTER TABLE [dbo].[CustomerInvoiceReportEDIItem] ADD CONSTRAINT [FK_CustomerInvoiceReportEDIItem_PointOfServiceFID] FOREIGN KEY ([PointOfServiceFID]) REFERENCES [dbo].[PointOfService] ([PointOfServiceID])
GO
ALTER TABLE [dbo].[CustomerInvoiceReportEDIItem] ADD CONSTRAINT [FK_CustomerInvoiceReportEDIItem_RevenueGroupFID] FOREIGN KEY ([RevenueGroupFID]) REFERENCES [dbo].[RevGroups] ([RGPriKey])
GO
GRANT SELECT ON  [dbo].[CustomerInvoiceReportEDIItem] TO [MssExec]
GRANT INSERT ON  [dbo].[CustomerInvoiceReportEDIItem] TO [MssExec]
GRANT DELETE ON  [dbo].[CustomerInvoiceReportEDIItem] TO [MssExec]
GRANT UPDATE ON  [dbo].[CustomerInvoiceReportEDIItem] TO [MssExec]
GO