Downloads
Accounting system

Sage 100

A real accounting system layout with hundreds of tables for accounts receivable, accounts payable, general ledger, inventory and orders. Explore with care: use the schema explorer.

sage100SQL Server 434 tables
AR_CustomerAR_InvoiceHistor…AP_VendorGL_AccountIM_ItemWarehouse

Good for

Real-world table namesFinding the right tableAR and AP analysis

Questions to try

  • Who are the customers (AR_Customer)?
  • Which AP invoices are open?
  • How are the tables for one module related?

Write each as a query in the SQL editor, or ask an AI assistant to walk you through it.

Connect to it

Server learnsql.cpetoday.com,1433, database sage100, in Excel or Power BI (Import mode) or DBeaver. In queries use the schema-qualified name shown for each table (for example sage100.AP_AgedInvoiceWrk).

Download the Excel connection (.odc)

How to use it: download the file and open it. Excel opens it as a connection to sage100 and asks for the SQL Server username and password from your CPE Today course. The file holds no password. The first table is selected by default, and you can pick others from the Data window. Setup details for other tools are on Connect.

Tables (434)

Click a table to preview it in the Playground.

Data dictionary

What each column means, its type and keys, and for codes and statuses the values seen in this copy. 11 of 434 tables are described so far. Read it as Markdown.

Click a table in the diagram above to jump to it.

AP_InvoiceHistoryHeader one row per posted vendor invoice 79 columns

Posted vendor invoices (the payables history). One row per invoice.

ColumnTypeKeyMeaningValues seen
InvoiceNo nvarchar(10) PK FK to AP_OpenInvoice Vendor's invoice number.
HeaderSeqNo nvarchar(6) PK Not described yet
ModuleCode nvarchar(3) Not described yet
InvoiceType nvarchar(1) Not described yet
APDivisionNo nvarchar(2) FK to AP_OpenInvoice Division. 01 18 02 4
VendorNo nvarchar(7) FK to AP_OpenInvoice Vendor who sent the invoice. AIRWAY 6 ANDERS 6 ALLCLIM 5 AIRTRAV 1 AMEX 1 CONSULT 1 OFFICE 1 STEV 1
InvoiceDate datetime2 Invoice date. 2010-05-01T00:00:00 13 2010-05-02T00:00:00 3 2010-05-15T00:00:00 3 2010-05-17T00:00:00 2 2010-05-11T00:00:00 1
InvoiceDueDate datetime2 Date payment is due. 2010-05-30T00:00:00 7 2010-05-01T00:00:00 3 2010-05-11T00:00:00 3 2010-05-31T00:00:00 3 2010-05-12T00:00:00 1 2010-05-25T00:00:00 1 2010-06-01T00:00:00 1 2010-06-10T00:00:00 1 2010-06-14T00:00:00 1 2010-06-16T00:00:00 1
InvoiceDiscountDate datetime2 Not described yet
TransactionDate datetime2 Not described yet
TermsCode nvarchar(2) Not described yet
HoldPayment nvarchar(1) Not described yet
Comment nvarchar(40) Not described yet
PrepaidApplyToInvoiceNo nvarchar(10) Not described yet
PrepaidPaymentDate datetime2 Not described yet
PrepaidCheckNo nvarchar(10) Not described yet
SourceJournal nvarchar(2) FK to GL_SourceJournal Not described yet
SourceJournalNo nvarchar(6) Not described yet
Form1099 nvarchar(1) Not described yet
Box1099 nvarchar(3) Not described yet
SeparateCheck nvarchar(1) Not described yet
JobNo nvarchar(7) Not described yet
VendorName nvarchar(30) Not described yet
TaxSchedule nvarchar(9) Not described yet
TaxClass nvarchar(2) Not described yet
UseTax nvarchar(1) Not described yet
PurchaseOrderNo nvarchar(7) Not described yet
ReceiptNo nvarchar(6) Not described yet
ReceiptDate datetime2 Not described yet
PurchaseAddressCode nvarchar(4) Not described yet
PurchaseName nvarchar(30) Not described yet
PurchaseAddress1 nvarchar(30) Not described yet
PurchaseAddress2 nvarchar(30) Not described yet
PurchaseAddress3 nvarchar(30) Not described yet
PurchaseCity nvarchar(20) Not described yet
PurchaseState nvarchar(2) Not described yet
PurchaseZipCode nvarchar(10) Not described yet
PurchaseCountryCode nvarchar(3) Not described yet
ShipToCode nvarchar(4) Not described yet
ShipToName nvarchar(30) Not described yet
ShipToAddress1 nvarchar(30) Not described yet
ShipToAddress2 nvarchar(30) Not described yet
ShipToAddress3 nvarchar(30) Not described yet
ShipToCity nvarchar(20) Not described yet
ShipToState nvarchar(2) Not described yet
ShipToZipCode nvarchar(10) Not described yet
ShipToCountryCode nvarchar(3) Not described yet
RequiredDate datetime2 Not described yet
ShipVia nvarchar(15) Not described yet
FOB nvarchar(15) Not described yet
WarehouseCode nvarchar(3) Not described yet
ConfirmTo nvarchar(30) Not described yet
ARDivisionNo nvarchar(2) Not described yet
CustomerNo nvarchar(7) Not described yet
CODCheckNo nvarchar(10) Not described yet
BankCode nvarchar(1) Not described yet
PurchaseOrderType nvarchar(1) Not described yet
PurchaseOrderDate datetime2 Not described yet
CODNo nvarchar(10) Not described yet
UserKey nvarchar(10) Not described yet
InvoiceAmt float Invoice amount.
NetInvoiceAmt float Invoice amount after discounts.
DiscountAmt float Early-payment discount amount. 0.0 16 70.81 3 -141.62 1 17.96 1 24.0 1
RetentionAmt float Not described yet
PrepaymentAmt float Not described yet
TaxableAmt float Not described yet
NonTaxableAmt float Not described yet
FreightAmt float Not described yet
TaxAmt float Not described yet
NonRecoverableAmt float Not described yet
TaxableMiscChargeAmt float Not described yet
NonTaxableMiscChargeAmt float Not described yet
TotalCostForFrghtAlloc float Not described yet
PrepaidFreight float Not described yet
CODAmount float Not described yet
DateUpdated datetime2 Not described yet
TimeUpdated nvarchar(8) Not described yet
UserUpdatedKey nvarchar(10) Not described yet
SSMA_TimeStamp timestamp Not described yet
AP_Vendor one row per vendor (within a division) 52 columns

Accounts payable vendors: name, terms, balances and last-paid information.

ColumnTypeKeyMeaningValues seen
APDivisionNo nvarchar(2) PK Accounts payable division. 02 18 01 11
VendorNo nvarchar(7) PK Vendor number.
VendorName nvarchar(30) Vendor name.
AddressLine1 nvarchar(30) Not described yet
AddressLine2 nvarchar(30) Not described yet
AddressLine3 nvarchar(30) Not described yet
City nvarchar(20) Not described yet
State nvarchar(2) Not described yet
ZipCode nvarchar(10) Not described yet
CountryCode nvarchar(3) Not described yet
PrimaryContact nvarchar(10) Not described yet
TelephoneNo nvarchar(17) Not described yet
TelephoneExt nvarchar(5) Not described yet
FaxNo nvarchar(17) Not described yet
EmailAddress nvarchar(50) Not described yet
URLAddress nvarchar(50) Not described yet
TermsCode nvarchar(2) Payment terms code. 03 13 01 10 02 4 00 2
Reference nvarchar(15) Not described yet
TemporaryVendor nvarchar(1) Not described yet
CreditCardVendor nvarchar(1) Not described yet
HoldPayment nvarchar(1) Set when payments to the vendor are on hold.
ExemptionNoOnFile nvarchar(1) Not described yet
TaxSchedule nvarchar(9) Not described yet
TaxClass nvarchar(2) Not described yet
AccountKey nvarchar(9) Not described yet
SeparateCheck nvarchar(1) Not described yet
Comment nvarchar(30) Not described yet
Sort nvarchar(10) Not described yet
BatchFax nvarchar(1) Not described yet
UsePOReceiptOfInvForVendor nvarchar(1) Not described yet
PrimaryPurchaseAddressCode nvarchar(4) Not described yet
VendorType nvarchar(1) Not described yet
Form1099 nvarchar(1) Set when the vendor is tracked for Form 1099. N 26 M 3
TaxpayerIDSocialSecurityNo nvarchar(9) Not described yet
Box1099 nvarchar(3) Not described yet
MiscBox9 nvarchar(1) Not described yet
LastPurchaseDate datetime2 Date of the most recent purchase. 2010-05-31T00:00:00 25 2010-05-01T00:00:00 2 (blank) 1 2010-02-28T00:00:00 1
LastPaymentDate datetime2 Date of the most recent payment. 2010-05-31T00:00:00 15 (blank) 7 2010-04-30T00:00:00 3 2010-05-01T00:00:00 3 2010-03-01T00:00:00 1
LastCheckNo nvarchar(10) Not described yet
LastCheckAmt float Not described yet
RetentionRate float Not described yet
AverageDaysToPay float Average days taken to pay this vendor. 0.0 23 4.0 1 5.0 1 6.0 1 10.0 1 15.0 1 31.0 1
AverageDaysOverDue float Not described yet
BalanceDue float Amount currently owed to the vendor.
NumberOfInvToUseInCalc float Not described yet
DateCreated datetime2 Not described yet
TimeCreated nvarchar(8) Not described yet
UserCreatedKey nvarchar(10) Not described yet
DateUpdated datetime2 Not described yet
TimeUpdated nvarchar(8) Not described yet
UserUpdatedKey nvarchar(10) Not described yet
SSMA_TimeStamp timestamp Not described yet
AR_Customer one row per customer (within a division) 75 columns

Accounts receivable customers: name, terms, credit limit and balances. The balance fields are running totals kept by Sage 100.

ColumnTypeKeyMeaningValues seen
ARDivisionNo nvarchar(2) PK FK to AR_Division Accounts receivable division (business unit) that owns the customer. 02 10 01 6
CustomerNo nvarchar(7) PK FK to AR_CustomerCreditCard Customer number. Together with ARDivisionNo it identifies the customer.
CustomerName nvarchar(30) Customer name.
AddressLine1 nvarchar(30) Not described yet
AddressLine2 nvarchar(30) Not described yet
AddressLine3 nvarchar(30) Not described yet
City nvarchar(20) Not described yet
State nvarchar(2) Not described yet
ZipCode nvarchar(10) Not described yet
CountryCode nvarchar(3) Not described yet
TelephoneNo nvarchar(17) Not described yet
TelephoneExt nvarchar(5) Not described yet
FaxNo nvarchar(17) Not described yet
EmailAddress nvarchar(50) Not described yet
URLAddress nvarchar(50) Not described yet
EBMEnabled nvarchar(1) Not described yet
EBMConsumerUserID nvarchar(15) Not described yet
BatchFax nvarchar(1) Not described yet
DefaultCreditCardPmtType nvarchar(5) Not described yet
ContactCode nvarchar(10) FK to AR_CustomerContact Not described yet
ShipMethod nvarchar(15) Not described yet
TaxSchedule nvarchar(9) Not described yet
TaxExemptNo nvarchar(15) Not described yet
TermsCode nvarchar(2) Payment terms code (AR_TermsCode). 01 8 02 7 03 1
SalespersonDivisionNo nvarchar(2) FK to AR_Salesperson Not described yet
SalespersonNo nvarchar(4) FK to AR_Salesperson Assigned salesperson number. 0300 5 0400 5 0200 4 0100 2
Comment nvarchar(30) Not described yet
SortField nvarchar(10) Not described yet
TemporaryCustomer nvarchar(1) Not described yet
OpenItemCustomer nvarchar(1) Not described yet
ResidentialAddress nvarchar(1) Not described yet
StatementCycle nvarchar(1) Not described yet
PrintDunningMessage nvarchar(1) Not described yet
CustomerType nvarchar(4) Customer type code used for grouping and reports. A4 5 A1 4 A2 2 A3 2 (blank) 1 A5 1 A9 1
PriceLevel nvarchar(1) Not described yet
DateLastActivity datetime2 Not described yet
DateLastPayment datetime2 Date of the most recent payment. 2017-05-31T00:00:00 8 (blank) 5 2017-05-21T00:00:00 2 2017-05-17T00:00:00 1
DateLastStatement datetime2 Not described yet
DateLastFinanceChrg datetime2 Not described yet
DateLastAging datetime2 Not described yet
DefaultItemCode nvarchar(15) Not described yet
CreditHold nvarchar(1) Set when the customer is on credit hold.
PrimaryShipToCode nvarchar(4) Not described yet
DateEstablished datetime2 Not described yet
EncryptedCreditCardNo nvarchar(24) FK to AR_CustomerCreditCard Not described yet
DefaultPaymentType nvarchar(5) FK to AR_PaymentType Not described yet
EmailStatements nvarchar(1) Not described yet
NumberOfInvToUseInCalc float Not described yet
AvgDaysPaymentInvoice float Not described yet
AvgDaysOverDue float Not described yet
CustomerDiscountRate float Not described yet
ServiceChargeRate float Not described yet
CreditLimit float Maximum open balance allowed. 10000.0 3 25000.0 3 0.0 2 20000.0 2 1000.0 1 2500.0 1 7500.0 1 15000.0 1 60000.0 1 120000.0 1
LastPaymentAmt float Not described yet
HighestStmntBalance float Not described yet
UnpaidServiceChrg float Not described yet
BalanceForward float Balance carried forward from the last statement cycle. 0.0 12 64.35 1 636.24 1 3298.75 1 7928.89 1
CurrentBalance float Current open balance.
AgingCategory1 float Open amount in the first aging bucket. 0.0 9 18.95 1 1000.0 1 1085.98 1 1257.4 1 3057.64 1 4476.31 1 6251.31 1
AgingCategory2 float Open amount in the second aging bucket. 0.0 10 31.7 1 835.43 1 1500.0 1 3057.64 1 3335.4 1 6406.53 1
AgingCategory3 float Open amount in the third aging bucket. 0.0 14 12.75 1 4607.18 1
AgingCategory4 float Open amount in the fourth aging bucket.
OpenOrderAmt float Not described yet
RetentionCurrent float Not described yet
RetentionAging1 float Not described yet
RetentionAging2 float Not described yet
RetentionAging3 float Not described yet
RetentionAging4 float Not described yet
DateCreated datetime2 Not described yet
TimeCreated nvarchar(8) Not described yet
UserCreatedKey nvarchar(10) Not described yet
DateUpdated datetime2 Not described yet
TimeUpdated nvarchar(8) Not described yet
UserUpdatedKey nvarchar(10) Not described yet
SSMA_TimeStamp timestamp Not described yet
AR_InvoiceHistoryDetail one row per invoice line 57 columns

Line detail for posted invoices: item, quantity and extended amount.

ColumnTypeKeyMeaningValues seen
InvoiceNo nvarchar(7) PK Invoice the line belongs to (AR_InvoiceHistoryHeader.InvoiceNo).
HeaderSeqNo nvarchar(6) PK Header sequence the line belongs to.
DetailSeqNo nvarchar(6) PK Line number within the invoice. 000000 18 000001 8 000002 4 000003 1
ItemCode nvarchar(15) Inventory item sold (IM_Item.ItemCode).
ItemType nvarchar(1) Not described yet
ItemCodeDesc nvarchar(30) Description of the item on the line. (blank) 12 8" x 10" Widgets 3 HON 2 DRAWER LETTER FLE W/O LK 3 22" Widget with Hinges 2 DESK FILE 8" CAP 50 2 Monthly Maintenance Billing 2 Widget 10 Pack 2 ART SPECIALTY EMP CANDLE LAMP 1 ART SPECIALTY WALNUT CNDL LAMP 1 BOOK BIN W/FLUORESCENT LIGHT 1 DESK 72" X 30" 1 PAPER CADDY 18"W 14"D 3"H 1
ExtendedDescriptionKey nvarchar(10) Not described yet
SalesAcctKey nvarchar(9) Not described yet
CostOfGoodsSoldAcctKey nvarchar(9) Cost of goods sold account the line posts to. 450010000 23 450100000 8
InventoryAcctKey nvarchar(9) Not described yet
UnitOfMeasure nvarchar(4) Not described yet
SubjectToExemption nvarchar(1) Not described yet
Commissionable nvarchar(1) Not described yet
TaxClass nvarchar(2) Not described yet
Discount nvarchar(1) Not described yet
DropShip nvarchar(1) Not described yet
WarehouseCode nvarchar(3) Warehouse the item shipped from. 001 17 (blank) 9 000 2 002 2 098 1
PriceLevel nvarchar(1) Not described yet
ProductLine nvarchar(4) Not described yet
Valuation nvarchar(1) Not described yet
PriceOverridden nvarchar(1) Not described yet
OrderWarehouse nvarchar(3) Not described yet
Revision nvarchar(3) Not described yet
BillOption1 nvarchar(2) Not described yet
BillOption2 nvarchar(2) Not described yet
BillOption3 nvarchar(2) Not described yet
BillOption4 nvarchar(2) Not described yet
BillOption5 nvarchar(2) Not described yet
BillOption6 nvarchar(2) Not described yet
BillOption7 nvarchar(2) Not described yet
BillOption8 nvarchar(2) Not described yet
BillOption9 nvarchar(2) Not described yet
KitItem nvarchar(1) Not described yet
ExplodedKitItem nvarchar(1) Not described yet
SkipPrintCompLine nvarchar(1) Not described yet
StandardKitBill nvarchar(1) Not described yet
AliasItemNo nvarchar(30) Not described yet
CustomerAction nvarchar(1) Not described yet
ItemAction nvarchar(1) Not described yet
WarrantyCode nvarchar(10) Not described yet
ExpirationDate datetime2 Not described yet
ExpirationOverridden nvarchar(1) Not described yet
CostCode nvarchar(9) Not described yet
CostType nvarchar(1) Not described yet
CommentText nvarchar Not described yet
PromiseDate datetime2 Not described yet
QuantityShipped float Quantity shipped.
QuantityOrdered float Not described yet
QuantityBackordered float Not described yet
UnitPrice float Price per unit.
UnitCost float Cost per unit, used for gross profit.
UnitOfMeasureConvFactor float Not described yet
CommissionAmt float Not described yet
LineDiscountPercent float Not described yet
QuantityPerBill float Not described yet
ExtensionAmt float Line amount (quantity times price).
SSMA_TimeStamp timestamp Not described yet
AR_InvoiceHistoryHeader one row per posted invoice 116 columns

Posted sales invoices and credit memos (the history after posting). One row per invoice.

ColumnTypeKeyMeaningValues seen
InvoiceNo nvarchar(7) PK Invoice number.
HeaderSeqNo nvarchar(6) Sequence of the header record; use it with InvoiceNo as the key.
ModuleCode nvarchar(3) Not described yet
InvoiceType nvarchar(2) Type of document, such as an invoice (I), a credit memo (C) or a debit memo (D). IN 15 XD 5 CM 3
InvoiceDate datetime2 Invoice date. 2010-05-31T00:00:00 5 2010-05-01T00:00:00 3 2010-05-15T00:00:00 3 2010-05-21T00:00:00 3 2010-04-01T00:00:00 2 2010-05-16T00:00:00 2 2010-03-01T00:00:00 1 2010-04-15T00:00:00 1 2010-05-10T00:00:00 1 2010-05-27T00:00:00 1 2010-05-30T00:00:00 1
TransactionDate datetime2 Not described yet
ARDivisionNo nvarchar(2) FK to SO_ShipToAddress Division of the customer. 01 15 02 7 (blank) 1
CustomerNo nvarchar(7) FK to SO_ShipToAddress Customer billed (AR_Customer). ABF 10 BRESLIN 3 ORANGE 3 (blank) 1 AUTOCR 1 AVNET 1 CUSTOM 1 GREALAR 1 JELLCO 1 SHEPARD 1
TermsCode nvarchar(2) Payment terms. 01 13 02 6 03 3 00 1
TaxSchedule nvarchar(9) Not described yet
TaxExemptNo nvarchar(15) Not described yet
SalespersonDivisionNo nvarchar(2) FK to AR_Salesperson Not described yet
SalespersonNo nvarchar(4) FK to AR_Salesperson Not described yet
CustomerPONo nvarchar(15) Not described yet
ApplyToInvoiceNo nvarchar(7) Not described yet
Comment nvarchar(30) Not described yet
RepetitiveInvoiceRefNo nvarchar(4) Not described yet
JobNo nvarchar(7) Not described yet
InvoiceDueDate datetime2 Date payment is due.
DiscountDueDate datetime2 Not described yet
SourceJournal nvarchar(2) FK to GL_SourceJournal Not described yet
JournalNoGLBatchNo nvarchar(6) Not described yet
BatchFax nvarchar(1) Not described yet
FaxNo nvarchar(17) Not described yet
ShippingInvoice nvarchar(1) Not described yet
SalesOrderNo nvarchar(7) Sales order the invoice came from, if any.
OrderType nvarchar(1) Not described yet
OrderDate datetime2 Not described yet
BillToName nvarchar(30) Not described yet
BillToAddress1 nvarchar(30) Not described yet
BillToAddress2 nvarchar(30) Not described yet
BillToAddress3 nvarchar(30) Not described yet
BillToCity nvarchar(20) Not described yet
BillToState nvarchar(2) Not described yet
BillToZipCode nvarchar(10) Not described yet
BillToCountryCode nvarchar(3) Not described yet
ShipToCode nvarchar(4) FK to SO_ShipToAddress Not described yet
ShipToName nvarchar(30) Not described yet
ShipToAddress1 nvarchar(30) Not described yet
ShipToAddress2 nvarchar(30) Not described yet
ShipToAddress3 nvarchar(30) Not described yet
ShipToCity nvarchar(20) Not described yet
ShipToState nvarchar(2) Not described yet
ShipToZipCode nvarchar(10) Not described yet
ShipToCountryCode nvarchar(3) Not described yet
ShipDate datetime2 Date the goods shipped. (blank) 4 2009-05-01T00:00:00 4 2010-05-30T00:00:00 4 2010-05-31T00:00:00 3 2010-03-01T00:00:00 1 2010-04-01T00:00:00 1 2010-04-13T00:00:00 1 2010-05-05T00:00:00 1 2010-05-10T00:00:00 1 2010-05-11T00:00:00 1 2010-05-15T00:00:00 1 2010-05-22T00:00:00 1
ShipVia nvarchar(15) Not described yet
ShipZone nvarchar(5) Not described yet
FOB nvarchar(15) Not described yet
ConfirmTo nvarchar(30) Not described yet
CheckNoForDeposit nvarchar(7) Not described yet
SplitCommissions nvarchar(1) Not described yet
SalespersonDivisionNo2 nvarchar(2) Not described yet
SalespersonNo2 nvarchar(4) Not described yet
SalespersonDivisionNo3 nvarchar(2) Not described yet
SalespersonNo3 nvarchar(4) Not described yet
SalespersonDivisionNo4 nvarchar(2) Not described yet
SalespersonNo4 nvarchar(4) Not described yet
SalespersonDivisionNo5 nvarchar(2) Not described yet
SalespersonNo5 nvarchar(4) Not described yet
PaymentType nvarchar(5) FK to AR_PaymentType Not described yet
PaymentTypeCategory nvarchar(1) Not described yet
OtherPaymentTypeRefNo nvarchar(24) Not described yet
CorporateCustIDPurchOrder nvarchar(25) Not described yet
CorporateTaxOverrd nvarchar(1) Not described yet
DepositCorporateTaxOverrd nvarchar(1) Not described yet
CardholderName nvarchar(30) Not described yet
ExpirationDateYear nvarchar(4) Not described yet
ExpirationDateMonth nvarchar(2) Not described yet
EncryptedCreditCardNo nvarchar(24) FK to AR_CustomerCreditCard Not described yet
Last4UnencryptedCreditCardNos nvarchar(4) Not described yet
AuthorizationCode nvarchar(16) Not described yet
AuthorizationCodeForDeposit nvarchar(16) Not described yet
CreditCardTransactionID nvarchar(10) Not described yet
CreditCardTransactionIDForDep nvarchar(10) Not described yet
RMANo nvarchar(7) Not described yet
EBMSubmissionType nvarchar(1) Not described yet
EBMUserIDSubmittingThisOrder nvarchar(15) Not described yet
EBMUserType nvarchar(1) Not described yet
ShipperID nvarchar(3) Not described yet
UserKey nvarchar(10) Not described yet
WarehouseCode nvarchar(3) Not described yet
ShipWeight nvarchar(5) Not described yet
ResidentialAddress nvarchar(1) Not described yet
EmailAddress nvarchar(50) Not described yet
CRMUserID nvarchar(11) Not described yet
CRMCompanyID nvarchar(11) Not described yet
CRMPersonID nvarchar(11) Not described yet
CRMOpportunityID nvarchar(11) Not described yet
TaxableSalesAmt float Taxable sales on the invoice. 0.0 12 -165.75 1 -44.66 1 132.5 1 133.04 1 572.5 1 916.2 1 1075.85 1 1601.1 1 1800.0 1 2350.1 1 9130.25 1
NonTaxableSalesAmt float Non-taxable sales on the invoice. 0.0 12 259.0 2 -81.48 1 33.2 1 130.0 1 309.85 1 407.4 1 826.25 1 1000.0 1 1935.25 1 283500.0 1
FreightAmt float Freight charged. 0.0 14 10.0 2 -2.78 1 3.0 1 5.0 1 6.0 1 32.85 1 38.0 1 45.0 1
SalesTaxAmt float Sales tax on the invoice. 0.0 13 -10.77 1 -2.9 1 6.63 1 28.63 1 46.73 1 52.72 1 86.46 1 108.0 1 117.51 1 456.51 1
CostOfSalesAmt float Not described yet
AmountSubjectToDiscount float Not described yet
DiscountRate float Not described yet
DiscountAmt float Discount given on the invoice. 0.0 16 38.85 2 19.96 1 21.52 1 46.48 1 137.43 1 160.11 1
SalesSubjectToComm float Not described yet
CostSubjectToComm float Not described yet
CommissionRate float Not described yet
CommissionAmt float Commission earned on the invoice.
SplitCommRate2 float Not described yet
SplitCommRate3 float Not described yet
SplitCommRate4 float Not described yet
SplitCommRate5 float Not described yet
DepositAmt float Not described yet
CreditCardPaymentBalanceAmt float Not described yet
Weight float Not described yet
RetentionAmt float Not described yet
NumberOfPackages float Not described yet
DepositCorporateSalesTax float Not described yet
CorporateSalesTax float Not described yet
DateUpdated datetime2 Not described yet
TimeUpdated nvarchar(8) Not described yet
UserUpdatedKey nvarchar(10) Not described yet
SSMA_TimeStamp timestamp Not described yet
AR_OpenInvoice one row per open invoice 37 columns

Invoices that are still open (not fully paid). The aging reports are built from this table.

ColumnTypeKeyMeaningValues seen
ARDivisionNo nvarchar(2) PK FK to AR_Division Not described yet
CustomerNo nvarchar(7) PK Customer who owes the invoice.
InvoiceNo nvarchar(7) PK Open invoice number.
InvoiceType nvarchar(2) Not described yet
InvoiceDate datetime2 Invoice date.
InvoiceDueDate datetime2 Date payment is due. Used for aging.
InvoiceDiscountDate datetime2 Last date the early-payment discount applies.
InvoiceHistoryHeaderSeqNo nvarchar(6) Not described yet
PostingDate datetime2 Not described yet
TermsCode nvarchar(2) Not described yet
TaxSchedule nvarchar(9) Not described yet
SalespersonDivisionNo nvarchar(2) FK to AR_Salesperson Not described yet
SalespersonNo nvarchar(4) FK to AR_Salesperson Not described yet
SplitCommissionFlag nvarchar(1) Not described yet
Comment nvarchar(30) Not described yet
CreditMemoInvoiceReference nvarchar(7) Not described yet
JobNo nvarchar(7) Not described yet
CustomerPONo nvarchar(15) Customer purchase order number. (blank) 92 3456 2 789 2 2233 1 9832 1 AJ-890123 1 RA1001 1 VERBAL 1 XX-890200 1
PostingReference nvarchar(10) Not described yet
CheckNo nvarchar(10) Not described yet
CommissionRate float Not described yet
TaxableAmt float Not described yet
NonTaxableAmt float Not described yet
FreightAmt float Not described yet
SalesTaxAmt float Not described yet
CostOfSalesAmt float Not described yet
SalesSubjectToComm float Not described yet
CostSubjectToComm float Not described yet
CommissionAmt float Not described yet
DiscountAmt float Not described yet
PaymentsToday float Not described yet
Balance float Amount still owed on the invoice.
RetentionAmt float Not described yet
DateUpdated datetime2 Not described yet
TimeUpdated nvarchar(8) Not described yet
UserUpdatedKey nvarchar(10) Not described yet
SSMA_TimeStamp timestamp Not described yet
GL_Account one row per G/L account 24 columns

The general ledger chart of accounts. Each row is a G/L account with its type and status.

ColumnTypeKeyMeaningValues seen
AccountKey nvarchar(9) PK Internal account key (use it for joins).
AccountDesc nvarchar(50) Account description.
Account nvarchar(41) Account number as entered (with segments, such as 1000-100).
RawAccount nvarchar(32) Not described yet
MainAccountCode nvarchar(15) Not described yet
DateStart datetime2 Not described yet
DateEnd datetime2 Not described yet
Status nvarchar(1) Account status, such as Active or Inactive.
ClearBalance nvarchar(1) Whether the balance clears at year-end (1 = yes).
AccountType nvarchar(2) FK to GL_AccountType Account type code, such as A (asset), L (liability), Q (equity), S (sales) or E (expense).
CashFlowsType nvarchar(1) How the account is treated in the cash flow statement. N 148 O 40 C 5 I 5 F 3
RollupCode1 nvarchar(20) Not described yet
RollupCode2 nvarchar(20) Not described yet
RollupCode3 nvarchar(20) Not described yet
RollupCode4 nvarchar(20) Not described yet
AccountGroup nvarchar(15) FK to GL_AccountGroup Not described yet
AccountCategory nvarchar(1) FK to GL_AccountCategory Category used in financial reports. X 113 A 33 L 22 C 13 R 12 O 4 E 3 I 1
CompanyCode nvarchar(3) Not described yet
DateCreated datetime2 Not described yet
TimeCreated nvarchar(8) Not described yet
UserCreatedKey nvarchar(10) Not described yet
DateUpdated datetime2 Not described yet
TimeUpdated nvarchar(8) Not described yet
UserUpdatedKey nvarchar(10) Not described yet
IM_Item one row per item 67 columns

Inventory items: description, type, cost and price setup.

ColumnTypeKeyMeaningValues seen
ItemCode nvarchar(15) PK Item code (primary key). R1000 1 R2000 1 R500 1 S1000 1 S2000 1
ItemCodeDesc nvarchar(30) Item description. R Boom Floor Crane, Mdl R1000 1 R Boom Floor Crane, Mdl R2000 1 R Boom Floor Crane, Mdl R500 1 Straddle Floor Crane, Mdl 1000 1 Straddle Floor Crane, Mdl 2000 1
ProductLine nvarchar(4) Product line the item belongs to (IM_ProductLine). RBFC 3 SFC 2
ProductType nvarchar(1) Product type code for the item.
Valuation nvarchar(1) Not described yet
PriceCode nvarchar(4) Not described yet
BackOrdersAllowed nvarchar(1) Not described yet
EBMEnabled nvarchar(1) Not described yet
DetailSalesHistory nvarchar(1) Not described yet
CalculateCommission nvarchar(1) Not described yet
StandardUnitOfMeasure nvarchar(4) Standard unit of measure for the item.
PurchaseUnitOfMeasure nvarchar(4) Not described yet
SalesUnitOfMeasure nvarchar(4) Not described yet
DefaultWarehouseCode nvarchar(3) Not described yet
ShipWeight nvarchar(10) Not described yet
PrimaryAPDivisionNo nvarchar(2) Not described yet
PrimaryVendorNo nvarchar(7) Not described yet
ImageFile nvarchar(30) Not described yet
DateLastSold datetime2 Not described yet
DateLastReceipt datetime2 Not described yet
Category1 nvarchar(10) Not described yet
Category2 nvarchar(10) Not described yet
Category3 nvarchar(10) Not described yet
Category4 nvarchar(10) Not described yet
NameOfSale nvarchar(10) Not described yet
SaleStartingDate datetime2 Not described yet
SaleEndingDate datetime2 Not described yet
SaleDiscountType nvarchar(1) Not described yet
AlternateItems nvarchar(1) Not described yet
PriceByItem nvarchar(1) Not described yet
PriceByCustPriceLevel nvarchar(1) Not described yet
PriceByCustomerNo nvarchar(1) Not described yet
TradeDiscount nvarchar(1) Not described yet
ExplodeKitItems nvarchar(1) Not described yet
ReturnsAllowed nvarchar(1) Not described yet
RestockingMethod nvarchar(1) Not described yet
NextLotSerialNo nvarchar(15) Not described yet
InventoryCycle nvarchar(1) Not described yet
RoutingNo nvarchar(20) Not described yet
ProcurementType nvarchar(1) Not described yet
PlannerCode nvarchar(3) Not described yet
BuyerCode nvarchar(3) Not described yet
PlannedByMrp nvarchar(1) Not described yet
LowLevelCode nvarchar(1) Not described yet
PrintReceiptLabels nvarchar(1) Not described yet
ExtendedDescription nvarchar(1) Not described yet
DetailReceiptHistory nvarchar(1) Not described yet
AllocateLandedCost nvarchar(1) Not described yet
TaxClass nvarchar(2) Not described yet
AllowOverrideRecptCost nvarchar(1) Not described yet
WarrantyCode nvarchar(10) Not described yet
CommissionRate float Not described yet
BaseCommAmt float Not described yet
PurchaseUMConvFctr float Not described yet
SalesUMConvFctr float Not described yet
SuggestedRetailPrice float Not described yet
StandardUnitPrice float Standard selling price.
StandardUnitCost float Standard cost.
LastUnitCost float Not described yet
AverageUnitCost float Not described yet
SalePriceDiscountPercent float Not described yet
TotalQuantityOnHand float Not described yet
AverageBackOrderFillDays float Not described yet
LastAllocatedCost float Not described yet
Volume float Not described yet
RestockingCharge float Not described yet
SSMA_TimeStamp timestamp Not described yet
IM_ItemWarehouse one row per item per warehouse 43 columns

Item quantities by warehouse: on hand, on order, on sales order and year-to-date sales.

ColumnTypeKeyMeaningValues seen
ItemCode nvarchar(15) Item (IM_Item.ItemCode).
WarehouseCode nvarchar(3) Warehouse (IM_Warehouse.WarehouseCode).
BinLocation nvarchar(10) Not described yet
ReorderMethod nvarchar(1) Not described yet
EconomicOrderQuantity float Not described yet
ReorderPointQuantity float Quantity at which to reorder.
MinimumOrderQuantity float Not described yet
MaximumOnHand float Not described yet
BeginningAverageCost float Not described yet
BeginningBalanceQuantity float Not described yet
QuantityReceived float Not described yet
QuantityAdjusted float Not described yet
QuantitySold float Not described yet
QuantityOnHand float Quantity on hand now.
QuantityOnPurchaseOrder float Quantity on open purchase orders.
QuantityOnSalesOrder float Quantity committed to open sales orders.
QuantityOnBackOrder float Quantity on backorder.
KitQuantityReceivedNextPeriod float Not described yet
PTDQuantitySold float Not described yet
PTDQuantityReturned float Not described yet
PTDDollarsSold float Not described yet
PTDCostOfGoodsSold float Not described yet
YTDQuantitySold float Quantity sold this fiscal year.
YTDQuantityReturned float Not described yet
YTDDollarsSold float Sales dollars this fiscal year.
YTDCostOfGoodsSold float Not described yet
PriorYearQuantitySold float Not described yet
PriorYearQuantityReturned float Not described yet
PriorYearDollarsSold float Not described yet
PriorYearCostOfGoodsSold float Not described yet
AverageOnHandQuantity float Not described yet
AverageCost float Average cost of the stock on hand.
QuantityOnWO float Not described yet
QuantityRequiredForWO float Not described yet
QuantityIssuedPTD float Not described yet
QuantityIssuedYTD float Not described yet
QuantityIssuedPriorYear float Not described yet
QuantitySoldNextPeriod float Not described yet
QuantityReturnedNextPeriod float Not described yet
DollarsSoldNextPeriod float Not described yet
CostOfGoodsSoldNextPeriod float Not described yet
QuantityIssuedNextPeriod float Not described yet
SSMA_TimeStamp timestamp Not described yet
PO_PurchaseOrderHeader one row per purchase order 68 columns

Purchase orders sent to vendors.

ColumnTypeKeyMeaningValues seen
PurchaseOrderNo nvarchar(7) Purchase order number.
PurchaseOrderDate datetime2 Date the order was placed.
OrderType nvarchar(1) Not described yet
MasterRepeatingOrderNo nvarchar(7) Not described yet
RequiredExpireDate datetime2 Date the goods are required by.
APDivisionNo nvarchar(2) Not described yet
VendorNo nvarchar(7) Vendor the order is placed with.
PurchaseName nvarchar(30) Not described yet
PurchaseAddress1 nvarchar(30) Not described yet
PurchaseAddress2 nvarchar(30) Not described yet
PurchaseAddress3 nvarchar(30) Not described yet
PurchaseCity nvarchar(20) Not described yet
PurchaseState nvarchar(2) Not described yet
PurchaseZipCode nvarchar(10) Not described yet
PurchaseCountryCode nvarchar(3) Not described yet
PurchaseAddressCode nvarchar(4) Not described yet
ShipToCode nvarchar(4) Not described yet
ShipToName nvarchar(30) Not described yet
ShipToAddress1 nvarchar(30) Not described yet
ShipToAddress2 nvarchar(30) Not described yet
ShipToAddress3 nvarchar(30) Not described yet
ShipToCity nvarchar(20) Not described yet
ShipToState nvarchar(2) Not described yet
ShipToZipCode nvarchar(10) Not described yet
ShipToCountryCode nvarchar(3) Not described yet
OrderStatus nvarchar(1) Order status (see the values).
UseTax nvarchar(1) Not described yet
PrintPurchaseOrders nvarchar(1) Not described yet
OnHold nvarchar(1) Set when the order is on hold.
BatchFax nvarchar(1) Not described yet
CompletionDate datetime2 Not described yet
ShipVia nvarchar(15) Not described yet
FOB nvarchar(15) Not described yet
WarehouseCode nvarchar(3) Not described yet
ConfirmTo nvarchar(30) Not described yet
Comment nvarchar(30) Not described yet
ARDivisionNo nvarchar(2) Not described yet
CustomerNo nvarchar(7) Not described yet
TermsCode nvarchar(2) Not described yet
LastInvoiceDate datetime2 Not described yet
LastInvoiceNo nvarchar(10) Not described yet
Form1099 nvarchar(1) Not described yet
Box1099 nvarchar(3) Not described yet
LastReceiptDate datetime2 Not described yet
LastIssueDate datetime2 Not described yet
LastReceiptNo nvarchar(7) Not described yet
LastIssueNo nvarchar(7) Not described yet
PrepaidCheckNo nvarchar(10) Not described yet
FaxNo nvarchar(17) Not described yet
TaxSchedule nvarchar(9) Not described yet
InvalidTaxCalc nvarchar(1) Not described yet
PrepaidAmt float Not described yet
TaxableAmt float Not described yet
NonTaxableAmt float Not described yet
SalesTaxAmt float Not described yet
FreightAmt float Not described yet
PrepaidFreightAmt float Not described yet
InvoicedAmt float Not described yet
ReceivedAmt float Not described yet
FreightSalesTaxInvAmt float Not described yet
BackOrderLostAmt float Not described yet
DateCreated datetime2 Not described yet
TimeCreated nvarchar(8) Not described yet
UserCreatedKey nvarchar(10) Not described yet
DateUpdated datetime2 Not described yet
TimeUpdated nvarchar(8) Not described yet
UserUpdatedKey nvarchar(10) Not described yet
SSMA_TimeStamp timestamp Not described yet
SO_SalesOrderHeader one row per open sales order 128 columns

Open sales orders. Lines are in SO_SalesOrderDetail.

ColumnTypeKeyMeaningValues seen
SalesOrderNo nvarchar(7) PK Sales order number.
OrderDate datetime2 Date the order was entered. 2010-05-15T00:00:00 4 2010-05-18T00:00:00 3 2010-04-20T00:00:00 2 2010-05-01T00:00:00 2 2010-05-11T00:00:00 2 2010-05-31T00:00:00 2 2010-05-03T00:00:00 1 2010-05-14T00:00:00 1 2010-05-19T00:00:00 1 2010-05-22T00:00:00 1 2010-05-23T00:00:00 1
OrderType nvarchar(1) Type of order. S 13 B 4 R 2 M 1
OrderStatus nvarchar(1) Status of the order (see the values). N 11 O 7 H 2
MasterRepeatingOrderNo nvarchar(7) Not described yet
ShipExpireDate datetime2 Date the order expires if it has not shipped. 2010-05-31T00:00:00 10 2010-05-01T00:00:00 3 2010-04-20T00:00:00 2 2010-05-11T00:00:00 1 2010-05-13T00:00:00 1 2010-05-15T00:00:00 1 2010-05-23T00:00:00 1 5999-12-31T00:00:00 1
ARDivisionNo nvarchar(2) FK to SO_ShipToAddress Not described yet
CustomerNo nvarchar(7) FK to SO_ShipToAddress Customer ordering.
BillToName nvarchar(30) Not described yet
BillToAddress1 nvarchar(30) Not described yet
BillToAddress2 nvarchar(30) Not described yet
BillToAddress3 nvarchar(30) Not described yet
BillToCity nvarchar(20) Not described yet
BillToState nvarchar(2) Not described yet
BillToZipCode nvarchar(10) Not described yet
BillToCountryCode nvarchar(3) Not described yet
ShipToCode nvarchar(4) FK to SO_ShipToAddress Not described yet
ShipToName nvarchar(30) Not described yet
ShipToAddress1 nvarchar(30) Not described yet
ShipToAddress2 nvarchar(30) Not described yet
ShipToAddress3 nvarchar(30) Not described yet
ShipToCity nvarchar(20) Not described yet
ShipToState nvarchar(2) Not described yet
ShipToZipCode nvarchar(10) Not described yet
ShipToCountryCode nvarchar(3) Not described yet
ShipVia nvarchar(15) Not described yet
ShipZone nvarchar(5) Not described yet
ShipZoneActual nvarchar(5) Not described yet
ShipWeight nvarchar(5) Not described yet
CustomerPONo nvarchar(15) Not described yet
FOB nvarchar(15) Not described yet
WarehouseCode nvarchar(3) Not described yet
ConfirmTo nvarchar(30) Not described yet
Comment nvarchar(30) Not described yet
TermsCode nvarchar(2) Payment terms. 01 11 02 7 03 2
TaxSchedule nvarchar(9) Not described yet
TaxExemptNo nvarchar(15) Not described yet
InvalidTaxCalc nvarchar(1) Not described yet
PrintSalesOrders nvarchar(1) Not described yet
PrintPickingSheets nvarchar(1) Not described yet
LastInvoiceOrderDate datetime2 Not described yet
LastInvoiceOrderNo nvarchar(7) Not described yet
CurrentInvoiceNo nvarchar(7) Not described yet
CheckNoForDeposit nvarchar(10) Not described yet
CycleCode nvarchar(2) Not described yet
FaxNo nvarchar(17) Not described yet
BatchFax nvarchar(1) Not described yet
BatchEmail nvarchar(1) Not described yet
EmailAddress nvarchar(50) Not described yet
FreightCalculationMethod nvarchar(1) Not described yet
LotSerialLinesExist nvarchar(1) Not described yet
SalespersonDivisionNo nvarchar(2) FK to AR_Salesperson Not described yet
SalespersonNo nvarchar(4) FK to AR_Salesperson Not described yet
SplitCommissions nvarchar(1) Not described yet
SalespersonDivisionNo2 nvarchar(2) Not described yet
SalespersonNo2 nvarchar(4) Not described yet
SalespersonDivisionNo3 nvarchar(2) Not described yet
SalespersonNo3 nvarchar(4) Not described yet
SalespersonDivisionNo4 nvarchar(2) Not described yet
SalespersonNo4 nvarchar(4) Not described yet
SalespersonDivisionNo5 nvarchar(2) Not described yet
SalespersonNo5 nvarchar(4) Not described yet
EBMUserType nvarchar(1) Not described yet
EBMSubmissionType nvarchar(1) Not described yet
EBMUserIDSubmittingThisOrder nvarchar(15) Not described yet
PaymentType nvarchar(5) FK to AR_PaymentType Not described yet
OtherPaymentTypeRefNo nvarchar(24) Not described yet
CorporateCustIDPurchOrder nvarchar(25) Not described yet
CorporateTaxOverrd nvarchar(1) Not described yet
DepositCorporateTaxOverrd nvarchar(1) Not described yet
CardholderName nvarchar(30) Not described yet
ExpirationDateYear nvarchar(4) Not described yet
ExpirationDateMonth nvarchar(2) Not described yet
EncryptedCreditCardNo nvarchar(24) Not described yet
CancelReasonCode nvarchar(5) FK to SO_CancelReasonCode Not described yet
CreditCardCVV2No nvarchar(5) Not described yet
Last4UnencryptedCreditCardNos nvarchar(4) Not described yet
CreditCardAuthorizationNo nvarchar(16) Not described yet
CreditCardTransactionID nvarchar(10) Not described yet
AuthorizationDate datetime2 Not described yet
AuthorizationTime nvarchar(6) Not described yet
AuthorizationCodeForDeposit nvarchar(16) Not described yet
CreditCardTransactionIDForDep nvarchar(10) Not described yet
PaymentTypeCategory nvarchar(1) Not described yet
PayBalanceByCreditCard nvarchar(1) Not described yet
AVSAddress nvarchar(30) Not described yet
AVSCity nvarchar(20) Not described yet
AVSState nvarchar(2) Not described yet
AVSZipCode nvarchar(10) Not described yet
CreditCardComment nvarchar(30) Not described yet
AuthorizationRequiredFlag nvarchar(1) Not described yet
RMANo nvarchar(7) Not described yet
JobNo nvarchar(7) Not described yet
ResidentialAddress nvarchar(1) Not described yet
CRMUserID nvarchar(11) Not described yet
CRMPersonID nvarchar(11) Not described yet
CRMOpportunityID nvarchar(11) Not described yet
CRMCompanyID nvarchar(11) Not described yet
CRMProspectID nvarchar(11) Not described yet
TaxableSubjectToDiscount float Not described yet
NonTaxableSubjectToDiscount float Not described yet
TaxSubjToDiscPrcntOfTotSubjTo float Not described yet
DiscountRate float Not described yet
DiscountAmt float Not described yet
TaxableAmt float Not described yet
NonTaxableAmt float Not described yet
SalesTaxAmt float Not described yet
CreditCardPreAuthorizationAmt float Not described yet
Weight float Not described yet
FreightAmt float Not described yet
DepositAmt float Not described yet
CreditCardPaymentBalanceAmt float Not described yet
CommissionRate float Not described yet
SplitCommRate2 float Not described yet
SplitCommRate3 float Not described yet
SplitCommRate4 float Not described yet
SplitCommRate5 float Not described yet
NumberOfShippingLabels float Not described yet
LastNoOfShippingLabels float Not described yet
DepositCorporateSalesTax float Not described yet
CorporateSalesTax float Not described yet
DateCreated datetime2 Not described yet
TimeCreated nvarchar(8) Not described yet
UserCreatedKey nvarchar(10) Not described yet
DateUpdated datetime2 Not described yet
TimeUpdated nvarchar(8) Not described yet
UserUpdatedKey nvarchar(10) Not described yet
SSMA_TimeStamp timestamp Not described yet
423 more tables (no notes yet)

← All databases