Tables and Data Flow of the Accpac Purchase Order Module

This blog posting will be looking at the structure of some of the parts of the Purchase Order (P/O) 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 169 tables in P/O, 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 P/O can be categorized as:

• Setup
• Data Entry
• Processing
• Periodic Processing

Purchase Order is one of the main entry points for data into the Accpac system. 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 from Purchase Order then flow into A/P, 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:

POOPT (PO00600): Options. Various global options for the P/O module.

POVIA (PO0900): Ship Via Addresses.

POPLAT (PO0605): P/O Templates.

POACST (PO0300): Additional Costs.

POACD (PO0290): Additional Cost Taxes.

POACSTO (PO0299): Additional Cost Optional Fields.

POMSG (PO0540): E-mail Messages.

POOFH (PO0585): P/O Optional Field Locations.

POOFD (PO0580): P/O Optional Fields.

POVUPR (PO0181): Vendor Contract Costs.

POVUMB (PO0191): Vendor Contract Cost Base Units.

POVUMS (PO0192): Vendor Contract Cost Sale Units.

POVUTX (PO0183): Vendor Contract Included Taxes.

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

Data Entry

Data Entry is the largest part of P/O. There are many tables involved in just entering a Purchase Order. Then you have requisitions, receipts, invoices, returns, and credit/debit notes. Several  of these involve tracking serial numbers and lot numbers. Quite a few tables have associated optional field tables. Many of the tables (like POPORHx) are quite large records with many fields and span two physical tables (one ending in 1 and the other 2).

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 data entry Views perform many calculations as you enter data. This includes sales tax calculations and a very sophisticated proration engine.

A general workflow might be to start with a Requisition then from this generate a Purchase Order then from the Purchase Order generate a Receipt then from the Receipt generate an Invoice. Then optionally a Return might be generated or some Credit/Debit Notes. Generally whenever you create on document from another, you can in fact create a document from multiple of the preceding documents. So for instance you can create 1 Invoice for a collection of Purchase Orders.

Requisitions

Requisitions are usually the documents that start the purchasing process, here you are requesting something, but need approval or more data before generating a Purchase Order.

PORQNH1,2 (PO0760): Requisition Headers. Notice that the View spans two physical tables. For each P/O Requisition, there is 1 record each in PORQNH1 and PORQNH2.

PORQNL (PO0770): Requisition Details.

PORQNLO (PO0773): Requisition Detail Optional Fields.

PORQNC (PO0750): Requisition Comments .

PORQNG (PO0759): Requisition Functions.

PORQNHO (PO0763): Requisition Header Optional Fields.

PORQNLV (PO0777): Requisition Vendors.

POGNRQN (PO0359): SuperView to create POs from Requisitions.

Calls the following

POGNVD (PO0361): Create from Requisitions by Vendor.

Purchase Orders

Purchase Orders can either be created new, or can be generated from one or more Requisitions.

POPORH1,2 (PO0620): PO Headers. There are two physical tables for each P/O.

POPORL (PO0630): PO Details.

POPORLO (PO0633): PO Detail Optional Fields.

POPORC (PO0610): PO Comments.

POPORR (PO0632): PO (from) Requisitions.

POPORG (PO0619): PO Functions.

POPORHO (PO0623): PO Header Optional Fields.

POFRQNL (PO0345): Requisitions (lookup helper).

POPORI (PO0621): PO Postings. Result of POST button.

POGENIC (PO0350): Create POs from IC                – Auto-generate from IC.

POGENOE (PO0352): Create POs from OE – Auto-generate from OE.

POGNOE (PO0355): Create PO from OE Order by Vendor.

POGNOEB (PO0357): Create PO from OE Order by Order Number.

Receipts

Next come Receipts which are usually generated from one or more Purchase Orders.

PORCPH1,2 (PO0700): Receipt Header. There are two physical tables for this and each record spans both tables.

PORCPL (PO0710): Receipt Lines.

PORCPC (PO0695): Receipt Comments.

PORCPV (PO0718): Receipt Vendors.

PORCPS (PO0714): Receipt Additional Costs.

PORCPR (PO0705): Receipt (from) Purchase Orders.

PORCPG (PO0699): Receipt Functions.

PORCPHO (PO0703): Receipt Optional Fields.

PORCPD (PO0696): Receipt Cost Distributions.

PORCPI (PO0701): Receipt Postings – Result of POST button.

PORCPM (PO0711): Receipt Postings Lines.

PORCPT (PO0715): Receipt Postings Add. Costs.

Invoices

Now it’s time to pay with an Invoice document.

POINVH1,2 (PO0420): Invoice Headers. Each Invoice header spans two physical records.

POINVP (PO0436): Invoice Payment Schedules.

POINVL (PO0430): Invoice Lines.

POINVLO (PO0433): Invoice Detail Optional Fields.

POINVLL (PO0819): Invoice Detail Lot Numbers.

POINVLS (PO0810): Invoice Line Serial Numbers.

POINVS (PO0440): Invoice Additional Costs.

POINVC (PO0416): Invoice Comments.

POINVG (PO0419): Invoice Functions.

POINVHO (PO0423): Invoice Header Optional Fields.

POINVD (PO0415): Invoice Cost Distributions.

POINVB (PO0414): Duplicate Invoices.

POINVI (PO0421): Invoice Postings Header. Result of POST button.

POINVT (PO0441): Invoice Post Cost Lines.

POINVM (PO0431): Invoice Postings Lines.

Returns

If you don’t like what you purchased then return it.

PORETH1,2 (PO0731): Returns Headers.

PORETL (PO0735): Return Lines.

PORETLO (PO0739): Return Detail Optional Fields.

PORETLL (PO0799): Return Line Lot Numbers.

PORETLS (PO0790): Return Line Serial Numbers.

PORETC (PO0729): Return Comments.

PORETG (PO0730): Return Functions.

PORETHO (PO0738): Return Header Optional Fields.

PORETI (PO0732): Return Postings. Result of POST button.

PORETM (PO0736): Return Posting Lines.

Credit/Debit Notes

POCRNH1,2 (PO0311): Credit/Debit Note Headers.

POCRNL (PO0315): Credit/Debit Note Details.

POCRNLO (PO0318): Credit/Debit Note Line Optional Fields.

POCRNLL (PO0829): Credit/Debit Note Line Lot Numbers.

POCRNLS (PO0820): Credit/Debit Note Line Serial Numbers.

POCRNC (PO0309): Credit/Debit Note Comments.

POCRNS (PO0320): Credit/Debit Note Add’l Costs .

POCRNG (PO0310): Credit/Debit Note Functions.

POCRNHO (PO0314): Credit/Debit Note Header Optional Fields.

POCRND (PO0326): Credit/Debit Note Cost Distributions.

POCRNB (PO0308): Cr/Dr Duplicate Notes.

POCRNI (PO0312): Credit/Debit Note Posting. Result of POST button.

POCRNM (PO0316): Credit/Debit Note Posting Line.

POCRNT (PO0312): Posting Additional Costs.

Data Processing

Most data processing in P/O 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). For more on Day End Processing check out this blog post.

Day End Processing

PODEPR (PO0335): PO Day end Processing.

Purchase Orders

POPORZ (PO0636): PO Posting.

POHSTH (PO0380): Purchase Order History.

POHSTL (PO0384): Purchase Order History Lines.

POPORAH (PO0612): Purchase Order Audit.

POPORAL (PO0614): Purchase Order Audit Lines.

Receipts

PORCPZ (PO0720): Receipt Posting.

PORCPAH (PO0686): Receipt Audit Headers.

PORCPAL (PO0688): Receipt Audit Lines.

PORCPAQ (PO0690): Receipt Audit Prorate Lines.

PORCPAS (PO0692): Receipt Audit Costs.

PORCPJ (PO0702): Receipt Day End History Headers.

PORCPN (PO0712): Receipt Day End Lines.

PORCPU (PO0716): Receipt Day End Costs.

Invoices

POINVZ (PO0445):  PO Invoice Posting.

POINVAH (PO0409): Invoice Audit Headers.

POINVAL (PO0410): Invoice Audit Lines.

POINVAQ (PO0411): Invoice Audit Prorate Lines.

POINVAS (PO0412): Invoice Audit Costs.

POINVJ (PO0422): Invoice Day End History Headers.

POINVN (PO0432): Invoice Day End Lines.

POINVU (PO0442): Invoice Day End Additional Costs.

Returns

PORETZ (PO0744): Return Posting.

PORETAH (PO0724): Return Audit Headers.

PORETAL (PO0726): Return Audit Lines.

PORETAQ (PO0727): Return Audit Prorate Lines.

PORETAS (PO0728): Return Audit Costs.

PORETJ (PO0733): Returns Day End History Headers.

PORETN (PO0734): Returns Day End Lines.

Debit/Credit Notes

POCRNZ (PO0330): Dr/Cr Note Posting.

POCRNAH (PO0304): Dr/Cr Note Audit Headers.

POCRNAL (PO0305): Dr/Cr Note Audit Lines.

POCRNAQ (PO0306): Dr/Cr Note Audit Prorate Lines.

POCRNAS (PO0307): Dr/Cr Note Audit Costs.

POCRNJ (PO0313): Dr/Cr Note Day End History Headers.

POCRNN (PO0317): Dr/Cr Note Day End History Lines.

POCRNU (PO0322): Dr/Cr Note Historical Add’l Costs.

Day End Helpers

Updated during day end

POLINEZ (PO0520): Generic Line Data.

POCOSTZ (PO0295): Generic Cost Data.

POPRXH (PO0656): Cost Prorate Headers.

POPRXL (PO0658): Cost Prorate Lines.

POPRXP (PO0660): Line Cost Prorate Lines.

POPRXC (PO0650): Line Cost Prorate Details.

POSTTL (PO0800): Statistics – from Day End.

Periodic Processing

POCH (PO0302): PO Clear History.

POGLTR (PO0353): Create GL Batch.

Printing Helpers

POLBLS (PO0500): Mailing Labels.

POFLAG (PO0340): Mark Posting Journals Printed.

POPRNT (PO0640): Mark Documents Posted.

Miscellaneous

PODLDN (PO0336): Drill Down.

POINIT (PO0405): PO Activation (new).

POUPGD (PO0850): PO Activation Upgrade.

POINTCK (PO0407): Integrity Checker.

Summary

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