CREATE TABLE [dbo].[PlasticDeal]
(
[DealID] [int] NOT NULL IDENTITY(1, 1),
[InstrumentFID] [int] NOT NULL,
[DealType] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Description] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[TransactionCode] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ApprovalCode] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ProcessorCode] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[BillingCycle] [bigint] NULL,
[OriginalAmount] [money] NOT NULL,
[ApprovedAmount] [money] NOT NULL,
[OriginFID] [int] NOT NULL,
[StatementDetailFID] [int] NULL,
[TransactionDate] [datetime] NULL,
[ResponseCode] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ResponseMessage] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CscResponseCode] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[TransactionStatus] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SysUserFID] [int] NOT NULL,
[ActivityDate] [datetime] NOT NULL CONSTRAINT [DF_PlasticDeal_ActivityDate] DEFAULT (getutcdate()),
[StorageInvoiceFID] [int] NULL,
[BatchStatusFID] [int] NULL,
[BatchErrorCodeFID] [int] NULL,
[WasReprocessed] [bit] NULL,
[WebDealFID] [int] NULL,
[WebTransactionCode] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[TransactionID] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[PurchaseOrderNo] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[HppSessionID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[MssTransactionID] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CurrencyCode] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[AuthorizationDate] [datetime] NULL,
[AuthorizationByFID] [int] NULL,
[AuthorizationAmount] [money] NULL,
[AuthorizationTransId] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[AuthorizationMssTransID] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[OrderFID] [int] NULL,
[CustomerNumber] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[BranchFID] [int] NULL,
[FinalizedDate] [datetime] NULL,
[FinalizedByFID] [int] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[PlasticDeal] ADD CONSTRAINT [CK_PlasticDeal_DealType] CHECK (([DealType]='V' OR [DealType]='T' OR [DealType]='S' OR [DealType]='R' OR [DealType]='A'))
GO
ALTER TABLE [dbo].[PlasticDeal] ADD CONSTRAINT [PK_PlasticDeal] PRIMARY KEY CLUSTERED  ([DealID]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_PlasticDeal_AuthorizationByFID] ON [dbo].[PlasticDeal] ([AuthorizationByFID]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_PlasticDeal_FinalizedByFID] ON [dbo].[PlasticDeal] ([FinalizedByFID]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_PlasticDeal_Instrument] ON [dbo].[PlasticDeal] ([InstrumentFID]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_PlasticDeal_StatementDetailFID] ON [dbo].[PlasticDeal] ([StatementDetailFID]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_PlasticDeal_StorageInvoiceFID] ON [dbo].[PlasticDeal] ([StorageInvoiceFID]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_PlasticDeal_SysUserFID] ON [dbo].[PlasticDeal] ([SysUserFID]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_PlasticDeal_TransactionCode] ON [dbo].[PlasticDeal] ([TransactionCode]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_PlasticDeal_TransactionID] ON [dbo].[PlasticDeal] ([TransactionID]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_PlasticDeal_WebTransactionCode] ON [dbo].[PlasticDeal] ([WebTransactionCode]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[PlasticDeal] ADD CONSTRAINT [FK_PlasticDeal_AuthorizationByFID] FOREIGN KEY ([AuthorizationByFID]) REFERENCES [dbo].[Sysuser] ([SysUserID])
GO
ALTER TABLE [dbo].[PlasticDeal] ADD CONSTRAINT [FK_PlasticDeal_BatchErrorCodeFID] FOREIGN KEY ([BatchErrorCodeFID]) REFERENCES [dbo].[ErrorCode] ([ECPriKey])
GO
ALTER TABLE [dbo].[PlasticDeal] ADD CONSTRAINT [FK_PlasticDeal_BatchStatusFID] FOREIGN KEY ([BatchStatusFID]) REFERENCES [dbo].[PlasticBatchStatus] ([BatchStatusID])
GO
ALTER TABLE [dbo].[PlasticDeal] ADD CONSTRAINT [FK_PlasticDeal_BranchFID] FOREIGN KEY ([BranchFID]) REFERENCES [dbo].[Branch] ([BranchPriKey])
GO
ALTER TABLE [dbo].[PlasticDeal] ADD CONSTRAINT [FK_PlasticDeal_FinalizedByFID] FOREIGN KEY ([FinalizedByFID]) REFERENCES [dbo].[Sysuser] ([SysUserID])
GO
ALTER TABLE [dbo].[PlasticDeal] ADD CONSTRAINT [FK_PlasticDeal_Instrument] FOREIGN KEY ([InstrumentFID]) REFERENCES [dbo].[PlasticInstrument] ([InstrumentID])
GO
ALTER TABLE [dbo].[PlasticDeal] ADD CONSTRAINT [FK_PlasticDeal_OrderFID] FOREIGN KEY ([OrderFID]) REFERENCES [dbo].[Orders] ([PriKey])
GO
ALTER TABLE [dbo].[PlasticDeal] ADD CONSTRAINT [FK_PlasticDeal_OriginFID] FOREIGN KEY ([OriginFID]) REFERENCES [dbo].[PlasticOrigin] ([OriginID])
GO
ALTER TABLE [dbo].[PlasticDeal] ADD CONSTRAINT [FK_PlasticDeal_StatementDetailFID] FOREIGN KEY ([StatementDetailFID]) REFERENCES [dbo].[StatementDetail] ([SDPriKey])
GO
ALTER TABLE [dbo].[PlasticDeal] ADD CONSTRAINT [FK_PlasticDeal_StorageInvoiceFID] FOREIGN KEY ([StorageInvoiceFID]) REFERENCES [dbo].[StorageInvoice] ([StorageInvoicePriKey])
GO
ALTER TABLE [dbo].[PlasticDeal] ADD CONSTRAINT [FK_PlasticDeal_SysUserFID] FOREIGN KEY ([SysUserFID]) REFERENCES [dbo].[Sysuser] ([SysUserID])
GO
ALTER TABLE [dbo].[PlasticDeal] ADD CONSTRAINT [FK_PlasticDeal_WebDealFID] FOREIGN KEY ([WebDealFID]) REFERENCES [dbo].[PlasticWebDeal] ([WebDealID])
GO
GRANT SELECT ON  [dbo].[PlasticDeal] TO [MssExec]
GRANT INSERT ON  [dbo].[PlasticDeal] TO [MssExec]
GRANT DELETE ON  [dbo].[PlasticDeal] TO [MssExec]
GRANT UPDATE ON  [dbo].[PlasticDeal] TO [MssExec]
GO