Tables and Data Flow of the Accpac Order Entry Module
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:
- Data Entry
- 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 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
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.
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
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
OECOMM (OE0200): Commissions
OESHHD (OE0690): Sales History
OESHDT (OE0685): Sales History Details
OESTATS (OE0700): Sales Statistics
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.