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:
- Data Entry
- 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:
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 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 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 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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
POCH (PO0302): PO Clear History.
POGLTR (PO0353): Create GL Batch.
POLBLS (PO0500): Mailing Labels.
POFLAG (PO0340): Mark Posting Journals Printed.
POPRNT (PO0640): Mark Documents Posted.
PODLDN (PO0336): Drill Down.
POINIT (PO0405): PO Activation (new).
POUPGD (PO0850): PO Activation Upgrade.
POINTCK (PO0407): Integrity Checker.
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.