CREATE TABLE [dbo].[AcctTransactions]
(
[ATPriKey] [int] NOT NULL IDENTITY(1, 1),
[GLNumber] [varchar] (66) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[OpposingGLNumber] [varchar] (66) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[DocumentNumber] [varchar] (21) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[VoucherNumber] [varchar] (17) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ApplyToDocument] [varchar] (21) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[TransactionType] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[TransactionAmount] [money] NOT NULL,
[InvoicePayment] [money] NULL,
[InvoicePaymentAccount] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DocumentDate] [datetime] NOT NULL,
[JournalDate] [datetime] NOT NULL,
[AcctDBName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[ShipperName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ItemDescription] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CustomerNumber] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[VendorNumber] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[PostingUserName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[TransactionDate] [datetime] NULL,
[AccountingPostDate] [datetime] NULL,
[CustomerName] [varchar] (65) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[VendorName] [varchar] (65) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[PONumber] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[VendorInvoiceNumber] [varchar] (21) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ICPriKey] [int] NULL,
[OrdPriKey] [int] NULL,
[BatchNumber] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CustomerNumber2] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[OrderNo] [varchar] (23) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Authority] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[NANumber] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Driver] [varchar] (43) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SalesPerson] [varchar] (43) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ItemCode] [int] NULL,
[Source] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[SourceRecord] [int] NOT NULL,
[BranchID] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[AcctNote] [varchar] (1024) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[InvoiceNumber] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[InvoiceDate] [datetime] NULL,
[1099Flag] [bit] NOT NULL CONSTRAINT [DF_AcctTransactions_1099Flag] DEFAULT ((0)),
[ServiceCode] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ItemCodeDescription] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[OnHoldFlag] [bit] NOT NULL CONSTRAINT [DF_AcctTransactions_OnHoldFlag] DEFAULT ((0)),
[BatProcessFID] [int] NULL,
[StatementHeaderID] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[AcctTransactions] ADD CONSTRAINT [PK_AcctTransactions] PRIMARY KEY NONCLUSTERED ([ATPriKey]) WITH (FILLFACTOR=80) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_AcctTransactions_ApplyToDocument] ON [dbo].[AcctTransactions] ([ApplyToDocument]) WITH (FILLFACTOR=80) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_AcctTransactions_BatchNumber] ON [dbo].[AcctTransactions] ([BatchNumber]) WITH (FILLFACTOR=80) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_AcctTransactions_DocumentNumber] ON [dbo].[AcctTransactions] ([DocumentNumber]) WITH (FILLFACTOR=80) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_AcctTransactions_ICPriKey] ON [dbo].[AcctTransactions] ([ICPriKey]) WITH (FILLFACTOR=80) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_AcctTransactions_InvoiceNumber] ON [dbo].[AcctTransactions] ([InvoiceNumber]) WITH (FILLFACTOR=80) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_AcctTransactions_OrderNo_ItemDescription_Source] ON [dbo].[AcctTransactions] ([OrderNo], [ItemDescription], [Source]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_AcctTransactions_OrdPriKey] ON [dbo].[AcctTransactions] ([OrdPriKey]) WITH (FILLFACTOR=80) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_AcctTransactions_SourceAndSourceRecord] ON [dbo].[AcctTransactions] ([Source], [SourceRecord]) WITH (FILLFACTOR=80) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_AcctTransactions_TransactionType] ON [dbo].[AcctTransactions] ([TransactionType]) WITH (FILLFACTOR=80) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_AcctTransactions_VoucherNumber] ON [dbo].[AcctTransactions] ([VoucherNumber]) WITH (FILLFACTOR=80) ON [PRIMARY]
GO
ALTER TABLE [dbo].[AcctTransactions] ADD CONSTRAINT [FK_AcctTransactions_ItemCode] FOREIGN KEY ([ICPriKey]) REFERENCES [dbo].[ItemCode] ([ICPriKey])
GO
GRANT SELECT ON [dbo].[AcctTransactions] TO [MssExec]
GRANT INSERT ON [dbo].[AcctTransactions] TO [MssExec]
GRANT DELETE ON [dbo].[AcctTransactions] TO [MssExec]
GRANT UPDATE ON [dbo].[AcctTransactions] TO [MssExec]
GO
EXEC sp_addextendedproperty N'MS_Description', N'This table is used to record all accounting transaction ', 'SCHEMA', N'dbo', 'TABLE', N'AcctTransactions', NULL, NULL
GO
EXEC sp_addextendedproperty N'MS_Description', N'Flag indicating whether the transaction is reportable as a 1099 (set to True) or not (False)', 'SCHEMA', N'dbo', 'TABLE', N'AcctTransactions', 'COLUMN', N'1099Flag'
GO
EXEC sp_addextendedproperty N'MS_Description', N'Date entered as the effective posting date', 'SCHEMA', N'dbo', 'TABLE', N'AcctTransactions', 'COLUMN', N'AccountingPostDate'
GO
EXEC sp_addextendedproperty N'MS_Description', N'Name of the database that the transaction was recorded in', 'SCHEMA', N'dbo', 'TABLE', N'AcctTransactions', 'COLUMN', N'AcctDBName'
GO
EXEC sp_addextendedproperty N'MS_Description', N'Note attached to the transaction', 'SCHEMA', N'dbo', 'TABLE', N'AcctTransactions', 'COLUMN', N'AcctNote'
GO
EXEC sp_addextendedproperty N'MS_Description', N'Key linking this table to a particular apply to document', 'SCHEMA', N'dbo', 'TABLE', N'AcctTransactions', 'COLUMN', N'ApplyToDocument'
GO
EXEC sp_addextendedproperty N'MS_Description', N'Primary key of this table', 'SCHEMA', N'dbo', 'TABLE', N'AcctTransactions', 'COLUMN', N'ATPriKey'
GO
EXEC sp_addextendedproperty N'MS_Description', N'Type of authority assigned to the order', 'SCHEMA', N'dbo', 'TABLE', N'AcctTransactions', 'COLUMN', N'Authority'
GO
EXEC sp_addextendedproperty N'MS_Description', N'Key linking this table to a particular batch ', 'SCHEMA', N'dbo', 'TABLE', N'AcctTransactions', 'COLUMN', N'BatchNumber'
GO
EXEC sp_addextendedproperty N'MS_Description', N'Points to the BATProcess record this record came from (useful when BATProcess records fail to get deleted)', 'SCHEMA', N'dbo', 'TABLE', N'AcctTransactions', 'COLUMN', N'BatProcessFID'
GO
EXEC sp_addextendedproperty N'MS_Description', N'Branch affected by the transaction', 'SCHEMA', N'dbo', 'TABLE', N'AcctTransactions', 'COLUMN', N'BranchID'
GO
EXEC sp_addextendedproperty N'MS_Description', N'Name of the customer affected by the transaction', 'SCHEMA', N'dbo', 'TABLE', N'AcctTransactions', 'COLUMN', N'CustomerName'
GO
EXEC sp_addextendedproperty N'MS_Description', N'Identification number for the customer affected by the transaction', 'SCHEMA', N'dbo', 'TABLE', N'AcctTransactions', 'COLUMN', N'CustomerNumber'
GO
EXEC sp_addextendedproperty N'MS_Description', N'Second customer affected by the transaction', 'SCHEMA', N'dbo', 'TABLE', N'AcctTransactions', 'COLUMN', N'CustomerNumber2'
GO
EXEC sp_addextendedproperty N'MS_Description', N'Date that the transaction was created on', 'SCHEMA', N'dbo', 'TABLE', N'AcctTransactions', 'COLUMN', N'DocumentDate'
GO
EXEC sp_addextendedproperty N'MS_Description', N'Key linking this table to a particular document ', 'SCHEMA', N'dbo', 'TABLE', N'AcctTransactions', 'COLUMN', N'DocumentNumber'
GO
EXEC sp_addextendedproperty N'MS_Description', N'Driver associated to the transaction', 'SCHEMA', N'dbo', 'TABLE', N'AcctTransactions', 'COLUMN', N'Driver'
GO
EXEC sp_addextendedproperty N'MS_Description', N'General ledger number used to record the transaction under', 'SCHEMA', N'dbo', 'TABLE', N'AcctTransactions', 'COLUMN', N'GLNumber'
GO
EXEC sp_addextendedproperty N'MS_Description', N'Key linking this table to a particular item code', 'SCHEMA', N'dbo', 'TABLE', N'AcctTransactions', 'COLUMN', N'ICPriKey'
GO
EXEC sp_addextendedproperty N'MS_Description', N'Date of the invoice for the transaction', 'SCHEMA', N'dbo', 'TABLE', N'AcctTransactions', 'COLUMN', N'InvoiceDate'
GO
EXEC sp_addextendedproperty N'MS_Description', N'Key linking this table to a particular invoice', 'SCHEMA', N'dbo', 'TABLE', N'AcctTransactions', 'COLUMN', N'InvoiceNumber'
GO
EXEC sp_addextendedproperty N'MS_Description', N'Dollar amount of the transaction that was applied to a particular invoice', 'SCHEMA', N'dbo', 'TABLE', N'AcctTransactions', 'COLUMN', N'InvoicePayment'
GO
EXEC sp_addextendedproperty N'MS_Description', N'Account affected by the invoice payment', 'SCHEMA', N'dbo', 'TABLE', N'AcctTransactions', 'COLUMN', N'InvoicePaymentAccount'
GO
EXEC sp_addextendedproperty N'MS_Description', N'Item code associated to the transaction', 'SCHEMA', N'dbo', 'TABLE', N'AcctTransactions', 'COLUMN', N'ItemCode'
GO
EXEC sp_addextendedproperty N'MS_Description', N'Full description of the item code used ', 'SCHEMA', N'dbo', 'TABLE', N'AcctTransactions', 'COLUMN', N'ItemCodeDescription'
GO
EXEC sp_addextendedproperty N'MS_Description', N'Specific description associated to the transaction', 'SCHEMA', N'dbo', 'TABLE', N'AcctTransactions', 'COLUMN', N'ItemDescription'
GO
EXEC sp_addextendedproperty N'MS_Description', N'Effective date of the transaction', 'SCHEMA', N'dbo', 'TABLE', N'AcctTransactions', 'COLUMN', N'JournalDate'
GO
EXEC sp_addextendedproperty N'MS_Description', N'Identification number for non-agent affected by the transaction', 'SCHEMA', N'dbo', 'TABLE', N'AcctTransactions', 'COLUMN', N'NANumber'
GO
EXEC sp_addextendedproperty N'MS_Description', N'Flag indicating whether the transaction is on hold (set to True) or not (False)', 'SCHEMA', N'dbo', 'TABLE', N'AcctTransactions', 'COLUMN', N'OnHoldFlag'
GO
EXEC sp_addextendedproperty N'MS_Description', N'General ledger number used to offset entries', 'SCHEMA', N'dbo', 'TABLE', N'AcctTransactions', 'COLUMN', N'OpposingGLNumber'
GO
EXEC sp_addextendedproperty N'MS_Description', N'Order number affected by the transaction', 'SCHEMA', N'dbo', 'TABLE', N'AcctTransactions', 'COLUMN', N'OrderNo'
GO
EXEC sp_addextendedproperty N'MS_Description', N'Key linking this table to a particular order (+) or cash receipt (-)', 'SCHEMA', N'dbo', 'TABLE', N'AcctTransactions', 'COLUMN', N'OrdPriKey'
GO
EXEC sp_addextendedproperty N'MS_Description', N'Purchase order number of the transaction', 'SCHEMA', N'dbo', 'TABLE', N'AcctTransactions', 'COLUMN', N'PONumber'
GO
EXEC sp_addextendedproperty N'MS_Description', N'Name of the MoverSuite user that generated the posting transaction', 'SCHEMA', N'dbo', 'TABLE', N'AcctTransactions', 'COLUMN', N'PostingUserName'
GO
EXEC sp_addextendedproperty N'MS_Description', N'Salesperson associated to the transaction', 'SCHEMA', N'dbo', 'TABLE', N'AcctTransactions', 'COLUMN', N'SalesPerson'
GO
EXEC sp_addextendedproperty N'MS_Description', N'Service code referenced when the transaction was created', 'SCHEMA', N'dbo', 'TABLE', N'AcctTransactions', 'COLUMN', N'ServiceCode'
GO
EXEC sp_addextendedproperty N'MS_Description', N'Name of the shipper affected by the transaction', 'SCHEMA', N'dbo', 'TABLE', N'AcctTransactions', 'COLUMN', N'ShipperName'
GO
EXEC sp_addextendedproperty N'MS_Description', N'Key linking this table to a particular source name', 'SCHEMA', N'dbo', 'TABLE', N'AcctTransactions', 'COLUMN', N'Source'
GO
EXEC sp_addextendedproperty N'MS_Description', N'Key linking this table to a source record', 'SCHEMA', N'dbo', 'TABLE', N'AcctTransactions', 'COLUMN', N'SourceRecord'
GO
EXEC sp_addextendedproperty N'MS_Description', N'The zero padded StatementHeaderID value plus the date entered for the batch for only A/R Cash Receipt TransactionType records', 'SCHEMA', N'dbo', 'TABLE', N'AcctTransactions', 'COLUMN', N'StatementHeaderID'
GO
EXEC sp_addextendedproperty N'MS_Description', N'Dollar amount of the transaction', 'SCHEMA', N'dbo', 'TABLE', N'AcctTransactions', 'COLUMN', N'TransactionAmount'
GO
EXEC sp_addextendedproperty N'MS_Description', N'Date that the transaction occurred on', 'SCHEMA', N'dbo', 'TABLE', N'AcctTransactions', 'COLUMN', N'TransactionDate'
GO
EXEC sp_addextendedproperty N'MS_Description', N'Key linking this table to a particular transaction type', 'SCHEMA', N'dbo', 'TABLE', N'AcctTransactions', 'COLUMN', N'TransactionType'
GO
EXEC sp_addextendedproperty N'MS_Description', N'Invoice number associated to the vendor', 'SCHEMA', N'dbo', 'TABLE', N'AcctTransactions', 'COLUMN', N'VendorInvoiceNumber'
GO
EXEC sp_addextendedproperty N'MS_Description', N'Name of the vendor affected by the transaction', 'SCHEMA', N'dbo', 'TABLE', N'AcctTransactions', 'COLUMN', N'VendorName'
GO
EXEC sp_addextendedproperty N'MS_Description', N'Identification number for the vendor affected by the transaction', 'SCHEMA', N'dbo', 'TABLE', N'AcctTransactions', 'COLUMN', N'VendorNumber'
GO
EXEC sp_addextendedproperty N'MS_Description', N'Key linking this table to a particular document ', 'SCHEMA', N'dbo', 'TABLE', N'AcctTransactions', 'COLUMN', N'VoucherNumber'
GO