Stephen Smith's Blog

All things Sage ERP…

Tables and Data Flow of the Accpac Order Entry Module

with 6 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:

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

Written by smist08

July 23, 2011 at 8:09 pm

Posted in Sage 300 ERP

Tagged with , , ,

6 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: http://smist08.wordpress.com/2010/08/28/writing-server-side-code-for-accpac-6-web-uis/. 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.

      smist08

      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

    Eli

    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.

    Bodlela

    May 29, 2014 at 1:02 pm


Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 274 other followers

%d bloggers like this: