Stephen Smith's Blog

Musings on Machine Learning…

Tables and Data Flow of the Accpac Purchase Order Module

with 13 comments


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.

 

Advertisements

Written by smist08

September 10, 2011 at 9:01 pm

13 Responses

Subscribe to comments with RSS.

  1. Wow, PO is sure complex when one looks under the hood. Is there a simple way to import PO history from outside Sage Accpac ERP? We have prospects that need their PO history imported into Sage Accpac but there doesnt’ seem to be any good import tools (Like Sage MAS 90’s Import Master)to keep cost of the import reasonable. (PS Need one for SO Invoice History, too!)

    Thanks, Free

    Free Polazzo

    September 11, 2011 at 12:21 am

    • The easiest way is import the documents into the data entry functions and then to post and day end those. The major problem to directly import those records in is to sort out the proration, which is extremely complex, so it is the best to let the program sort it out for you.

      smist08

      September 12, 2011 at 3:07 pm

  2. This process is not so great for someone with thousands of invoices and p/o’s that go back for many years. As our prospect base becomes more and more sophisticated because they already have in house ERP systems something like an “Import Master” module would surely be a competative tool for Sage to offer.

    Our customer is using the Sage Businessworks ERP and thinks that Sage is a good place to stay in order to avoid the hassles of many upgrade issues they have seen with other companies. They like the Sage Accpac product but can’t understand the difficulty in moveing their data from one Sage product to another.

    Is Sage doing anything to make it easier to move the transaction type data (which is gold to many companies) from one Sage product to another? It’s not always Sage Simply Acctg. to Sage Accpac anymore.

    Free Polazzo

    September 12, 2011 at 5:02 pm

    • There is a team working on product migration tools, but I don’t know where Businessworks fits into their plans. Generally we do want to make it much easier to move between our products.

      smist08

      September 13, 2011 at 2:34 am

  3. Hello Smith,
    Quick question; is there way to PO-receive bonus (free) qty. only effecting inventory but without booking liability. There are many fields in table[PORCPL] for extra qty., any use of that. Appreciate your suggestion. Thanks

    Shahid Akhtar

    November 15, 2012 at 10:01 am

    • If the item is free, simply set the unit cost/extended cost to zero during purchase order entry/receipt entry will do the job. As a result the item quantity will increase, but the cost of these items (which is zero) will have no impact to the inventory (liability) account.

      smist08

      November 16, 2012 at 12:57 am

      • hi – what if you didn’t know you were getting free items at time of PO creation and only found out upon supplier’s delivery?

        Joan

        May 6, 2015 at 7:23 am

      • You would need to add these to the PO before generating the Receipt.

        smist08

        May 6, 2015 at 5:30 pm

  4. Hi Smith,

    Customer having Day end error :
    Item existence status change (2019 PORCPZ1.c) can you guide me where to check.
    Day end stop after process a PO receipt number.
    Customer key in non-inventory item(without create in IC Item) like ‘MMN/SP’ on PO and then do PO Receiving.And they said they work like this for long time and no error.

    Nakarat

    February 17, 2016 at 2:02 am

    • I don’t know exactly, but the error is coming from a routine called UpdateLoadReceiptLaydownLine. Its comparing the receipt line item exists flag PORCPL->ITEMEXISTS to the ITEMEXISTS field in POLINEZ (Generic Line data). If they aren’t equal then it throws this error. Perhaps this might give a clue.

      smist08

      February 24, 2016 at 4:53 pm

  5. Hi Stephen.

    Thanks for continuing to share your knowledge. We truly appreciate your decision to share and educate.

    There is something I would like you to help me understand in relation to how Sage 300 handles additional charges on PO Receipts. Not sure you have had a reason to test this before, however during purchasing of inventory items there are often additional charges that are to be prorated to the form total cost of the inventory. Consider a scenario in which there is a delay in determining the additional charges. The PO Receipt was posted and all the items received were sold or issued to production. The expected result was for the system to realize the item has been used (sold or issued) and write the charges to either Cost of Goods Sold or Usage account. Instead the inventory general ledger control account is further increased.

    I was hoping after a subsequent editing of that PO Receipt and input of the charges, Sage 300 would realize the original quantity received have been exhausted and therefore not put the charges to Inventory. Such behavior results in an increase in the Inventory, even though all have been used up.

    Is that your understanding also of how Sage 300 handles Additional Charges on PO Receipts?

    Thanks,
    Leroy Gordon

    Leroy Gordon

    October 26, 2016 at 4:28 am

    • If you think the behavior is wrong, you might want to ask tech support or submit a feedback request from the help menu. I know PO is very complicated and there are a lot of considerations in this whole process. Others with deeper knowledge of PO might know a better way to deal with this, but as a last resort you can always enter some additional G/L entries to fix things up.

      smist08

      October 26, 2016 at 3:23 pm

      • Thanks for the feedback. I will certainly act on your suggestions offered.

        Leroy Gordon

        October 29, 2016 at 11:47 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

%d bloggers like this: