Tables [dbo].[AcctTransactions]
This table is used to record all accounting transaction
Properties
PropertyValue
CollationSQL_Latin1_General_CP1_CI_AS
HeapYes
Row Count (~)405905
Created4:57:06 PM Thursday, September 7, 2006
Last Modified7:31:41 PM Tuesday, June 11, 2024
Columns
NameData TypeMax Length (Bytes)Allow NullsIdentityDefaultDescription
Primary Key PK_AcctTransactions: ATPriKeyATPriKeyint4
No
1 - 1Primary key of this table
GLNumbervarchar(66)66
No
General ledger number used to record the transaction under
OpposingGLNumbervarchar(66)66
No
General ledger number used to offset entries
Indexes IX_AcctTransactions_DocumentNumber: DocumentNumberDocumentNumbervarchar(21)21
No
Key linking this table to a particular document
Indexes IX_AcctTransactions_VoucherNumber: VoucherNumberVoucherNumbervarchar(17)17
Yes
Key linking this table to a particular document
Indexes IX_AcctTransactions_ApplyToDocument: ApplyToDocumentApplyToDocumentvarchar(21)21
Yes
Key linking this table to a particular apply to document
Indexes IX_AcctTransactions_TransactionType: TransactionTypeTransactionTypevarchar(30)30
No
Key linking this table to a particular transaction type
TransactionAmountmoney8
No
Dollar amount of the transaction
InvoicePaymentmoney8
Yes
Dollar amount of the transaction that was applied to a particular invoice
InvoicePaymentAccountvarchar(50)50
Yes
Account affected by the invoice payment
DocumentDatedatetime8
No
Date that the transaction was created on
JournalDatedatetime8
No
Effective date of the transaction
AcctDBNamevarchar(50)50
No
Name of the database that the transaction was recorded in
ShipperNamevarchar(50)50
Yes
Name of the shipper affected by the transaction
Indexes IX_AcctTransactions_OrderNo_ItemDescription_Source: OrderNo\ItemDescription\SourceItemDescriptionvarchar(128)128
Yes
Specific description associated to the transaction
CustomerNumbervarchar(15)15
Yes
Identification number for the customer affected by the transaction
VendorNumbervarchar(15)15
Yes
Identification number for the vendor affected by the transaction
PostingUserNamevarchar(50)50
Yes
Name of the MoverSuite user that generated the posting transaction
TransactionDatedatetime8
Yes
Date that the transaction occurred on
AccountingPostDatedatetime8
Yes
Date entered as the effective posting date
CustomerNamevarchar(65)65
Yes
Name of the customer affected by the transaction
VendorNamevarchar(65)65
Yes
Name of the vendor affected by the transaction
PONumbervarchar(50)50
Yes
Purchase order number of the transaction
VendorInvoiceNumbervarchar(21)21
Yes
Invoice number associated to the vendor
Foreign Keys FK_AcctTransactions_ItemCode: [dbo].[ItemCode].ICPriKeyIndexes IX_AcctTransactions_ICPriKey: ICPriKeyICPriKeyint4
Yes
Key linking this table to a particular item code
Indexes IX_AcctTransactions_OrdPriKey: OrdPriKeyOrdPriKeyint4
Yes
Key linking this table to a particular order (+) or cash receipt (-)
Indexes IX_AcctTransactions_BatchNumber: BatchNumberBatchNumbervarchar(15)15
Yes
Key linking this table to a particular batch
CustomerNumber2varchar(15)15
Yes
Second customer affected by the transaction
Indexes IX_AcctTransactions_OrderNo_ItemDescription_Source: OrderNo\ItemDescription\SourceOrderNovarchar(23)23
Yes
Order number affected by the transaction
Authorityvarchar(30)30
Yes
Type of authority assigned to the order
NANumbervarchar(15)15
Yes
Identification number for non-agent affected by the transaction
Drivervarchar(43)43
Yes
Driver associated to the transaction
SalesPersonvarchar(43)43
Yes
Salesperson associated to the transaction
ItemCodeint4
Yes
Item code associated to the transaction
Indexes IX_AcctTransactions_OrderNo_ItemDescription_Source: OrderNo\ItemDescription\SourceIndexes IX_AcctTransactions_SourceAndSourceRecord: Source\SourceRecordSourcevarchar(50)50
No
Key linking this table to a particular source name
Indexes IX_AcctTransactions_SourceAndSourceRecord: Source\SourceRecordSourceRecordint4
No
Key linking this table to a source record
BranchIDvarchar(5)5
Yes
Branch affected by the transaction
AcctNotevarchar(1024)1024
Yes
Note attached to the transaction
Indexes IX_AcctTransactions_InvoiceNumber: InvoiceNumberInvoiceNumbervarchar(64)64
Yes
Key linking this table to a particular invoice
InvoiceDatedatetime8
Yes
Date of the invoice for the transaction
1099Flagbit1
No
((0))Flag indicating whether the transaction is reportable as a 1099 (set to True) or not (False)
ServiceCodevarchar(4)4
Yes
Service code referenced when the transaction was created
ItemCodeDescriptionvarchar(128)128
Yes
Full description of the item code used
OnHoldFlagbit1
No
((0))Flag indicating whether the transaction is on hold (set to True) or not (False)
BatProcessFIDint4
Yes
Points to the BATProcess record this record came from (useful when BATProcess records fail to get deleted)
StatementHeaderIDvarchar(15)15
Yes
The zero padded StatementHeaderID value plus the date entered for the batch for only A/R Cash Receipt TransactionType records
Indexes Indexes
NameColumnsUniqueFill Factor
Primary Key PK_AcctTransactions: ATPriKeyPK_AcctTransactionsATPriKey
Yes
80
IX_AcctTransactions_ApplyToDocumentApplyToDocument80
IX_AcctTransactions_BatchNumberBatchNumber80
IX_AcctTransactions_DocumentNumberDocumentNumber80
IX_AcctTransactions_ICPriKeyICPriKey80
IX_AcctTransactions_InvoiceNumberInvoiceNumber80
IX_AcctTransactions_OrderNo_ItemDescription_SourceOrderNo, ItemDescription, Source
IX_AcctTransactions_OrdPriKeyOrdPriKey80
IX_AcctTransactions_SourceAndSourceRecordSource, SourceRecord80
IX_AcctTransactions_TransactionTypeTransactionType80
IX_AcctTransactions_VoucherNumberVoucherNumber80
Foreign Keys Foreign Keys
NameColumns
FK_AcctTransactions_ItemCodeICPriKey->[dbo].[ItemCode].[ICPriKey]
Permissions
TypeActionOwning Principal
GrantDeleteMssExec
GrantInsertMssExec
GrantSelectMssExec
GrantUpdateMssExec
SQL Script
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
Uses
Used By