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:

• 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:

Setup

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.

Orders

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

Shipments

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

Invoices

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

Statistics

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).

Summary

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.

 

About these ads