PO Variances by Item / PO/ Vendor

Have a client that would like to be able to easily track PO purchases variances by line item and vendor.



You can see the posting in the G/L in the PO Purchases Variance account by the vendor,  but it really doesn't show the item.


Don't really see a file that keeps track of the variances - pre calculated.


The PO receipts history file appears to show the the receipts of goods records although for some odd reason we were not seeing the receipt of invoice records?     Not sure at this point if I'm just doing something wrong and accidentally filtered out the receipt of invoice records or they just aren't there.


Shouldn't I be seeing receipt of invoice records?   Or am I making this too hard and there is an easier way to do this?


Client is currently in 4.3.

  • Do they have the PO Receipt/Invoice Variance Register Report turned on?

  • Thomas, no, receipts of invoices don't write lines to PO_ReceiptHistoryDetail. You aren't doing anything wrong on your report.

    I'm wondering if comparing the receipt history detail (PO_receipthistorydetail) to the purchase order detail (po_purchaseorderdetail) could give you what you need.  Is it that the Purchases Clearing Report is not giving the customer the information they need? If it's not, please feel free to drop us an idea telling us where it is lacking.


  • Purchases history is only available until the ordered, received and invoiced quantities are all equal, after that there is nothing available. 


    And of course once a purchase order is completed,  it eventually goes away so you can't even see what the original cost entered on the PO is. 


    Hmm...would there be enough detail in the A/P history detail?   There is an Original Unit Cost Field in A/P History Detail.   Question would then become, what is the source of that info.   Is that the original amount on the P.O. or is that the amount from receipt of goods?




    Client's goal is to try to evaluate vendors historically by whether the quoted prices equal the amount the eventually are billed.   You can see this in the general ledger po variances account but you can't easily sort those out by item and vendor.


    Secondarily this is a check on whether the persons doing the purchase orders and receipt of goods are paying attention and updating costs as they should.   They have a fairly signicant amount in their variances account because they weren't watching their costs at purchase order entry and receipt of goods entry time.

  • Steve,


    Yes they do.  But the goal is to be able to see easily all the variances that have happened previously and summarize them.

  • Tom,


    If they are on paperless, I wonder if someone can glean the appropriate data into a worksheet and then perhaps access to give you the data to work with.  Maybe the job for a temp?  I know a lot can be done with Acrobat Pro but I'm not sure about this specific task.  Depending on the size of the problem, it might give them what they want.

  • Tom:

    There's probably enough info in AP invoice history detail for the receipt of invoice, but there's no record for the receipt of goods. The PO will hang around till it meets the number of days to retain, so it 'could' be around for quite some time.


    I hope you are able to get the information you need.

  • Probably can be done with A/P history and receipt history although it could be a little painful getting everything to match up. 


    This is one of those projects where the client wants it as long as it doesn't cost  too much (budget about two hours) so was hoping for a simple data source.  But it doesn't look like I'm going to be able to meet that constraint by the time it's debugged.

  • in reply to TomTarget


    Were you ever able to create such a report?  I have just received the exact same request from our Accounting team where they want to see the PO Variance Amount by Item, not by PO.  I have been looking through PO and AP files and couldn't find one that contains this variance amount by item, nor could determine what files I could possibly link and do a calculation on.  

    Any input you can provide would be helpful and much appreciated, if it can be done.

    We are on Sage 100c Advanced v2022.1

  • in reply to Kenny98

    Recently Sage added PO invoice line data to receipt history tables, so you might be able to look there for transactions posted after you upgraded to a version with this change. 

    Before that, you'd have to deal with linking AP invoice history with the RoG data in PO receipt history, which is not an easy task.

    Either way you are looking at a custom report, and you have to filter out amounts that are currently on the Purchases Clearing report (quantity received <> quantity invoiced).

  • in reply to Kevin M

    Thanks for the info Kevin.  I found the Variance field in PO Receipt History Detail file and created a crystal report that will help going forward.  As you stated, nothing prior to our upgrade to v2022.

    I'll try working with the AP invoice history and PO receipt history files to see what can come out of it, but based on what I see so far and your comment, this one's going to take awhile.

    Thanks again