Start here
Lessons and reference
Look things up
Start here
SQL
Power Query
Data modeling
Power BI and DAX
Finance playbooks
Run queries
Build and check
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.
Write each as a query in the SQL editor, or ask an AI assistant to walk you through 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.
Click a table to preview it in the Playground.
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.
Posted vendor invoices (the payables history). One row per invoice.
| Column | Type | Key | Meaning | Values 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 |
Accounts payable vendors: name, terms, balances and last-paid information.
| Column | Type | Key | Meaning | Values 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 |
Accounts receivable customers: name, terms, credit limit and balances. The balance fields are running totals kept by Sage 100.
| Column | Type | Key | Meaning | Values 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 |
Line detail for posted invoices: item, quantity and extended amount.
| Column | Type | Key | Meaning | Values 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 |
Posted sales invoices and credit memos (the history after posting). One row per invoice.
| Column | Type | Key | Meaning | Values 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 |
Invoices that are still open (not fully paid). The aging reports are built from this table.
| Column | Type | Key | Meaning | Values 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 |
The general ledger chart of accounts. Each row is a G/L account with its type and status.
| Column | Type | Key | Meaning | Values 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 |
Inventory items: description, type, cost and price setup.
| Column | Type | Key | Meaning | Values 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 |
Item quantities by warehouse: on hand, on order, on sales order and year-to-date sales.
| Column | Type | Key | Meaning | Values 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 |
Purchase orders sent to vendors.
| Column | Type | Key | Meaning | Values 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 |
Open sales orders. Lines are in SO_SalesOrderDetail.
| Column | Type | Key | Meaning | Values 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 |