Knowledge Base

This is intended to be a guide on finding specific data in the SSI Database using external tools such as ODBC, which is a middle man between the SSI Database and a tool such as MS Excel or MS Access.

Two Youtube videos related to this topic are:

The following guide is the official SSI documentation for setting up the ODBC driver.

SSI does not provide support for third party software as there are plenty of guides online for how to pull data from a MySQL database into Excel or Access. An example Youtube channel can be found at youtube.com/c/KirtKershaw, but there are others as well.

There is a lot to know about navigating the database, which is why new information is going to be added as time goes on. 

TABLE: A place to store data that is similar to a spreadsheet in the sense that there are columns and rows. Unlike a spreadsheet there are often rules to follow to help keep things organized. The system enforces these rules where appropriate. Examples in the SSI system are inventorymaster, inventorystore and inventoryvendoritem.

DATABASE / VOLUME: A collection of related tables. Examples in the SSI system are VOL01, VOL02, VOL03, graphical_VOL01, etc. Generally the graphical_VOL01 table stores data that text can not access, and VOL01-03 has the data that both graphical and text can access.

JOINING TABLES: This is an SQL term that describes attaching related tables together in a way that allows you to access more data. A simple example would involve trying to see the name of a customer who belongs to a sales order. The sales order stores the customer number, but not the name. To get the customer name in this case the order's header table would need to be connected to the customer's master table by the customer number that they both have in common. Learning to join tables correctly is very important as an incorrect join can result in results with duplicate lines or even data that is simply incorrect.

FIELD: A field is like the column in a spreadsheet.

RECORD: A record is like the row of a spreadsheet.

VOL01: The majority of the system data is stored here. Examples are sales orders, customers, salespeople, clerks, inventory items, contracts, etc. Two topics that tend to not be under VOL01 are AP and GL, although some tables have been redundantly made accessible from VOL01.

VOL02: GL Data that is found under the General Ledger tab in graphical or menu options 18.1.1 in text.

VOL03: AP Data that is found under the Accounts Payable tab in graphical or menu options 18.3 in text.

graphical_VOL01: In contrast to volumes 1-3, the graphical volume table is not tied to text in a way that makes things rigid. This allows programming to add whatever is needed to the system for new graphical features. One useful example is being able to see the previous prices of items on a sales order as well as who made the change in the SalesOrderAudit table.

ReportEngine: This database contains print options as well as 3 months worth of records for all print/email attempts

NOTE: If your system runs multiple companies there could be tables such as VOL61, 62 and 63 as well as 1,2,and 3. Support can help you get a better understanding of what your specific setup is.

Support can answer any questions about other databases as well.

To understand why some data is stored in unusual places a brief history lesson is required. The SSI Business System started out as an order management tool designed for the construction industry and to this day still uses the construction system as it's foundation. As we adapted the system to meet the needs of the office supply industry we found it to be simpler to recycle some unused fields than it would be to add new fields. Some examples are:

Promo ID for an item on a sales order: This is stored in the MechanicNumber field in the sales order's line data. We add the prefix PROMO: before the contract number to make it more obvious that it is not actually a mechanic number.

Furniture Order Flag: This is stored under the order's header flag under a field similar to WorkOrderWarranty with either a Y for furniture or N (sometimes blank) for not furniture.

Customer data is stored in VOL01. The simplest tables to get customer data from are customermaster, customershipto, customerweb, CUST_GENERAL_INFO, CUST_ADDITIONAL_INFO, SUB_ACCOUNT_MASTER and SUB_ACC_ADDRESS. This will allow you simple access to the majority of information for your customers without needing to worry about joining tables.

This is also stored in VOL01. Sales orders always have 3 tables that work together, the header, the lines and the totals. The header is usually information that is true before the sale starts, such as customer number, salesperson number, clerk, address, etc. The lines are basically a list of what was purchased and the totals are what's true after the sale is finalized, obviously calculated taxes and the totals go here but also information such as the tax jurisdiction and when the order was created belong here as well.

Depending on where the order is in the sales flow the data can be in different tables, usually with a prefix of salesorder for open orders, dailywork for anything posted to A/R and invoicehistory for anything posted to history. Taking open orders as an example you would have salesorderheader, salesorderline and salesordertotal. 

There are freetype and comment tables as well. While freetypes and comments both serve similar purposes a freetype is native to the text system and uses a line for every note, whereas a comment lives inside an existing line item and can be grouped with other comments. Freetypes are found in salesorderfreetype, dailyworkfreetype and invoicehistoryfreetype, whereas comments are found in OrderComments and OrderCommentsHistory. 

Also found in VOL01. These would be found under salesmanmaster and CLERK_MASTER respectively.

Also found in VOL01. The tables involved with purchase orders are PO_HEADER, PO_HEADER_1, PO_LINES, the comments tables (PO_COMMENTS, PO_LINE_COMMENTS and more recently POLineComments), as well as the history versions of these aptly named PO_HISTORY_HEADER for example.

A notable difference between purchase orders and sales orders is the use of the term sequence number. A sales order uses a sequence number to indicate what "dash" it is, such as -00 -01 etc, whereas a PO's sequence number is basically the line number.

Also found in VOL01. For dealers who use pricing link the tables involved would be PROFORMA_HEADER, PROFORMA_TYPE_1 - PROFORMA_TYPE_5, and PROFORMA_QTY_BREAKS. The header table is not used by text, but the others are shared between the two systems. The header contains what would be found in the header in Pricing Link, the QTY_BREAKS table has pricing and the TYPE_1-5 tables control contract type (IE: General contract, sales flyer, etc)

There are a number of specific details about the SSI system to keep in mind when working directly with the database. The following is by no means an exhaustive list, but aims to cover a few key examples as a place to start. Support can provide further guidance as needed.

Many features in the SSI system have been implemented using comments. Some notable examples are print groups, SIF data and the long address for the sales order header. It can be more difficult to join on the comment tables because the data stored across multiple lines. For example, you can't just join two tables together on the PO number since there is no PO column, it would need to be joined on comments where the comment starts with PO#.

The comment tables only exist for open orders and history, dailywork shares the open order table. These tables are named OrderComments and OrderCommentsHistory. The same information is used to join these tables, it just goes by a different name:

The tables for purchase orders can be somewhat involved depending on what you are trying to do. It is also fair to point out that the warehouse for 2nd day receiving purposes is not stored in the database at all making it impossible to retrieve without something like the 2nd day receiving report.

Key tables are PO_HEADER, PO_LINES, PO_RECEIPTS, PO_COMMENTS, PO_LINE_COMMENTS and POLineComments, as well as the history versions of these. 

Joining these tables together only involves the PO Number field, as PO numbers are not recycled by the system and return POs have a distinct PO number.

This document will be added to over time.

Another important topic is status codes, which are used to identify the state something is in. Common examples are if an order is in entered, printed or verified status, what type of suspension it is, etc.

-2 = Voided

-1 = Cancelled

0 = Incomplete

1 = Entered

2 = Printed

3 = Verified

4 = Backordered

5 is unused

6 = Posted Invoice Only

7 = Posted Invoice and A/R

8 is unused

9 = Already in A/R

0 = Not Suspended

1 = Suspended by User

2 = Suspended for Bad Margin

3 = Suspended for Route

4 = Suspended for Credit Limit

5 = Suspended for Verification Issues

6 = Suspended for Credit Card issues

7 = Cash on Delivery Order

8 = Quote

NOTE: In text any sales order could be suspended as a quote and given the status of 8, graphical can not suspend an order as a quote, but orders starting with a Q do have the suspend status applied.

1 = Emergency

2 = Special

3 = Direct

0 = Incomplete

1 = Entered

2 = Printed

3 = Transmitted

4 = Confirmed

5-19 is not used

10 = Partially Received / Not Invoiced

11 = Partially Received / Partially Invoiced

12-19 is not used

20 = Completely Received / Not Invoiced

21 = Completely Received / Partially Invoiced

22 = Completely Received / Completely Invoiced

0 = Entered

1 = Re-scheduled

2 = Printed

3 = Received

4 = Invoiced

5 = Completed

This section will be added to over time as support handles questions on this topic.