Stephen Smith's Blog

Musings on Machine Learning…

Tables and Data Flow of the Accpac Order Entry Module

with 14 comments

This blog posting will be looking at the structure of some of the parts of the Order Entry (O/E) module of the Sage ERP Accpac product. You can find the structure of the individual tables in the Accpac Application Object Model (AOM), which must be viewed in IE. However this doesn’t tell you how the tables are related or how data flows from one table to another as you post various transactions. There are 94 tables in O/E, so obviously we can’t describe each in a single blog post. So we’ll just look at a few interesting cases. Understanding these relationships can be especially important to people writing sophisticated custom reports or BI Views.

The main types of database tables in O/E can be categorized as:

  • Setup
  • Data Entry
  • Processing
  • Periodic Processing

Order Entry is one of the main entry points for data into the Accpac system. Notice that unlike other modules, O/E has no master data files. Typically Order Entry will have the most users, often with hundreds of people entering orders at one time. There are multiple sources of orders including the Quote to Orders screen inside SageCRM, the main Accpac Order Entry screen and many ISV solutions (such as Point of Sale retail solutions or Web Stores) that feed transactions into Order Entry. Documents from Order Entry then flow into A/R, inventory is updated in I/C and all financial numbers end up in G/L. Below is a diagram showing this flow:


The Setup Tables are:

OEOPT (OE0480): OE Options. Various global options for the O/E module.

OEPLAT (OE0540): Templates.

OEMISC (OE0440): Miscellaneous Charges.

OEMISCT (OE0460): Misc Charge Taxes.

OEMISCO (OE0450): Misc Charge Optional Fields.

OEVIA (OE0760): Ship Via Codes.

OEOFH (OE0475): Optional Field Locations.

OEOFD (OE0470): Optional Fields.

OEGLREF (OE0272): G/L Reference Integration.

OEMSG (OE0465): E-mail Messages.

Besides the standard tables that each application has, O/E has very few setup tables. However beware that O/E uses the setup tables in A/R and I/C extensively so look to these as well when considering what affects O/E.

Data Entry

Data Entry is the largest part of O/E. There are many tables involved in just entering an order. Then you have shipments, invoices, and credit/debit notes. Each of these involve tracking serial numbers, lot numbers, kitting information, bill of material information and optional fields. Many of the tables (like OEORDH) are quite large records with many fields.

All the data entry tables rely heavily on header/detail relationships. Each level of indentation indicates that the indented level is a detail of the less indented level. For more on header detail relationships check out this. The reason the Order Details table has sub-details is that say you order a widget with quantity 10 which has serial numbers, then the system needs to record the 10 serial numbers in this sub-detail. Similarly if the widget is subject to Lot Tracking then there may be several Lots that the 10 items come from which needs to be tracked.


OEORDH (OE0520): Order Headers. There is one of these for each order. Note that this is one View that is spread over two physical tables: OEORDH and OEORDH1. Each OEORDH record has a matching OEORDH1 record, logically these combine to form one large record.

OEORDD (OE0500): Order Details. There is one of these for each detail line in an order.

OEORDDO (OE0501): Order Detail Optional Fields

OEORDDB (OE0503): Order BOM Details

OEORDDD (OE0502): Order Kitting Details

OEORDDDS (OE0504): Order Kitting Serial Numbers

OEORDDDL (OE0506): Order Kitting Lot Numbers

OEORDDS (OE0508): Order Detail Serial Numbers.

OEORDDL (OE0507): Order Detail Lot Numbers

OECOINO (OE0180): Order Comments

OETERMO (OE0740): Order Payment Schedules

OEORDQ (OE0526): Order from Quotes

OEORDHO (OE0522): Order Header Optional Fields


Whenever you ship anything whether from the Shipment Entry screen or by entering Quantity Shipped in the Order Entry screen then one of these Shipment documents is created either by copying all the relevant information from the Order Entry document that is being shipped or by creating a new stand-alone shipment that has no matching Order.

OESHIH (OE0692): Shipment Headers. One of these for each Shipment.

OESHID (OE0691): Shipment Details

OESHTD (OE0697): Shipment Day End Details

OESHIDO (OE0702): Shipment Detail Optional Fields

OESHIDB (OE0705): Shipment BOM Details

OESHIDD (OE0703): Shipment Kitting Details

OESHIDDS (OE0706): Shipment Kitting Serial Numbers

OESHTDD (OE0699): Shipment Day End Details of Details

OESHIDDL (OE0707): Shipment Kitting Detail Lot Numbers

OESHIDS (OE0709): Shipment Detail Serial Numbers

OESHTDS (OE0671): Shipment Day End Serial Numbers

OESHIDL (OE0708): Shipment Detail Lot Numbers

OESHTDL (OE0670): Shipment Day End Lot Numbers

OECOINS (OE0190): Comments and Instructions

OESHIR (OE0694): Multiple Orders to Shipment

OETERMS (OE0745): Shipment Payment Schedules

OESHIHO (OE0704): Shipment Header Optional Fields


When you Invoice a shipment then one of these documents is created by copying the relevant information from the shipment document. These can be created from the Invoice screen or by specifying “Create Invoice” in the Order Entry screen (as long as you ship something).

OEINVH (OE0420): Invoice Headers

OEINVD (OE0400): Invoice Details

OEINVDO (OE0401): Invoice Detail Optional Fields

OEINVDB (OE0403): Invoice BOM Details

OEINVDD (OE0402): Invoice Kitting Details

OEINVDDS (OE0404): Invoice Kitting Detail Serial Numbers

OEINVDDL (OE0405): Invoice Kitting Detail Lot Numbers

OEINVDS (OE0407): Invoice Detail Serial Numbers

OEINVDL (OE0406): Invoice Detail Lot Numbers

OECOINI (OE0160): Invoice Comments

OETERMI (OE0720): Invoice Payment Schedules

OEINVR (OE0427): Multiple Shipments to Invoice

OEINVHO (OE0422): Invoice Header Optional Fields

Return / Credit Notes

These are the Credit/Debit note documents that are created and much be applied to invoices.

OECRDH (OE0240): Credit Note Headers

OECRDD (OE0220): Credit Note Details

OECRDDO (OE0221): Credit/Debit Detail Optional Fields

OECRDDB (OE0223): Credit/Debit BOM Details

OECRDDD (OE0222): Credit/Debit Kitting Details

                OECRDDDS (OE0224): Credit/Debit Kitting Detail Serial Numbers

                OECRDDDL (OE0225): Credit/Debit Kitting Detail Lot Numbers

OECRDDS (OE0227): Credit/Debit Detail Serial Numbers

OECRDDL (OE0226): Credit/Debit Detail Lot Numbers

OECOINC (OE0140): Credit Note Comments

OECRDHO (OE0242): Credit/Debit Note Optional Fields

Data Flow

Below is a diagram showing the data flow of data from the O/E entry views through Day End into the various history and statistics files.

Data Processing

Most data process in O/E happens at Day End time, whether this is initiated from I/C Day End Processing or you have the perform day end during posting option set. You can also choose to split the operation and do costing during posting and the rest (like sub-ledger batch generation) during I/C Day End. I/C Day End will call the Day End processing View in each application that subscribes to Day End (including O/E and P/O).

Day End Processing

Invoked by the master day end processing superview, ICDEP.

OEDEND (OE0260): OE Day End Master

The following are called to process each type of document

OEPOSTO (OE0600): OE Order Posting

OEPOSTS (OE0605): OE Shipment Posting

OEPOSTI (OE0580): OE Invoice Posting

OEPOSTC (OE0560): OE Credit Note Posting

Audit tables

OEAUDH (OE0120): Posting Journals

OEAUDD (OE0100): Posting Journal Details

OEAUDDP (OE0110): Posting Journal Detail Optional Fields

OEAUDDD (Oe0105): Posting Journal Detail-Detail

OEAUDHP (OE0123): Posting Journal Header Optional Fields

History tables

OECOMM (OE0200): Commissions

OESHHD (OE0690): Sales History

OESHDT (OE0685): Sales History Details


OESTATS (OE0700): Sales Statistics

Periodic Processing

There are a small set of superviews that perform various periodic processing functions. These Views typically don’t have tables behind them and perform maintenance type operations. There are also processing functions built into the entry Views where you set a number of fields and then call viewProcess to perform the operation. Some of the stand-alone superviews include:

OECH (OE0130): Clear History and Entries

OEINTCK (OE0280): OE Integrity Check

OEDLDN (OE0265): Drill Down (called by AR and IC to drill down to OE documents).


Hopefully this blog posting provides a bit more insight into how O/E operates and hopefully helps when you use O/E, interface to O/E or are creating custom O/E reports or forms.


Written by smist08

July 23, 2011 at 8:09 pm

Posted in sage 300

Tagged with , , ,

14 Responses

Subscribe to comments with RSS.

  1. Could you do a post on business logic in 6.1? In previous versions it was in the views. In 6.1 if it is in the views then the web uis will be slow going back to the server all the time. It still has to be (I think) in the views so that 3rd party programs can integrate with Accpac, but how do you solve the ui speed problem? Is some of the logic now meant to be in client-side gwt/java objects, or in the ui? Should some logic go in the sdata layer (but that runs on the server, right?). Is it expected that 3rd party developers would create sdata objects or just use the standard ones that wrap the views?

    dan gibson

    July 26, 2011 at 1:45 am

    • Have a look at the article: SData is a fairly efficient REST based Web Services protocol, so that generally we only make one RPC call to the server when the user does something. That RPC call could result in quite a few View calls to do its job, but then they are all local on the server. We also (in 5.6A) implemented a view notificaitons system so that when you say put a field, we know all the fields that change as a result and then they can be returned in the SData response as deltas to the current record.


      July 26, 2011 at 3:21 am

      • Perfect. Thanks.

        dan gibson

        July 26, 2011 at 3:28 am

  2. […] Notice that unlike other modules (except O/E); P/O has no master data files. Purchase Order mirrors Order Entry to some degree to handle the purchasing side of things while O/E handles the sales side. Documents […]

  3. Thanks for the nice posts, have helped in solving quite a number of issues during implementation


    September 7, 2012 at 8:22 am

  4. Hi Stephen
    Thanks for the nice posts. I wonder how Accpac calculates the Total Outstanding Balance we see on the “AR Customer Inquiry – Credit Status Tab”. I need to be able to get the same figure from SQL queries.
    Please advice.


    May 29, 2014 at 1:02 pm

  5. Hello.

    Greeting of the day!
    Can you please help me to find Invoice payment tables and how they link with OEINVH table(Invoice Header).



    February 11, 2016 at 8:32 am

    • From the O/E Invoice, an A/R Invoice is generated that will have the same document number of the O/E Invoice. Then A/R Receipts are applied against the A/R Invoice. So you can find the correct A/R Invoice based on the O/E Invoice number and then find the receipts that have been applied against it.


      February 11, 2016 at 11:05 pm

  6. Thanks, But I have still confusion. How I link IDINVC field of AROBL with OEINVH Inv Number


    February 12, 2016 at 11:08 am

    • Not entirely sure what you mean. Are you getting these through the Views via the COM or .Net API? Then you would probably set a filter and browse/fetch through the records. Or are you trying to build a SQL statement for some other tool?


      February 12, 2016 at 11:04 pm

  7. Thank you for the posts they are very educative.

    I have 2 questions regarding OE
    1. Is there a way to make OE Order be completed once the order has been invoiced(just like what PO does). I noted that you can do order, ship then invoice and still add more lines to the order.

    2. Is there a way to make the tick “job related”, be constant on certain customers unlike users selecting it everytime. I have a particular customer for this project.

    Mr Mayor

    February 17, 2016 at 5:39 pm

  8. I need to update the coini.coin field using a sql script using the invnumber as a reference point .I was thinking of using a update and inner join script to do this but i cannot find a common column as reference point to join the 2 tables. Please assist. The idea is import a comment from another database using this script , written in an application format. Please assist if possible .Thank you

    Johan Dantuma

    February 21, 2017 at 2:09 pm

    • I’m guessing the CO app must be from a third party? Perhaps contact them. Generally we don’t support updating any tables using SQL directly since it can break referential (or logical) database integrity. You should use the API and Views so any necessary data validation/formatting can be performed.


      February 21, 2017 at 5:59 pm

  9. Dear smist,
    Please help me resolve this issue.One of our user on Sage 300 ERP raise a credit note and it did not reflect in the customers account.


    April 18, 2017 at 11:16 am

Leave a Reply to smist08 Cancel reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: