Hi all,


There are times when you need to manually close lines on a purchase order or the entire purchase order themselves within the Purchase Order Processing module. I’ve received many questions around closing purchase orders that are old (or stuck) via a SQL scripting solution verses manually doing it through the front end.


Typically, if needed, you would close a purchase order and/or line(s) manually under the Edit Purchase Order Status window. (Transactions >> Purchasing >> Edit Purchase Orders) This is the recommended way from Microsoft Dynamics support standpoint. We do not recommend using a SQL scripting solution to update the purchase order statuses and here is why.


We always recommend using the Edit Purchase Order Status window to close purchase orders because there is code that runs when you select the Process option.   This code checks the status of the data at that time and determines based on what it finds if anything needs to occur from an inventory perceptive (costs) and/or General Ledger (financial adjustments). Yes, closing a purchase order can create cost adjustments and a journal entry to General Ledger!


When using scripts to update the values, this check is skipped, and because of this you could potentially be causing a negative impact to your Microsoft Dynamics GP system. Adjustments to the Inventory Purchase Receipts Work table’s (IV10200) cost layer could be missed as well as adjustments to General Ledger could be skipped causing an out of balance Accrued Purchases account.


Below is a link to an article that I wrote a few years back that discusses what happens if you close a purchase order manually depending on the status of the data. This gives you an example of both scenarios; if you close a purchase order where ONLY the shipment was completed with no invoice OR if you receive a shipment and partially invoice the line.   If this check is skipped by simply updating the values using a SQL scripting solution, you could potentially be causing more harm than good.


The Closing of Purchase Orders does not always generate a journal entry backing out Accrued Purchases in Microsoft Dynamics GP https://support.microsoft.com/en-us/help/2021543/the-closing-of-purchase-orders-does-not-always-generate-a-journal-entr 


Note - if you did a shipment only for a single line or the shipment matches the invoice quantity amount on the line, you could possibly write SQL scripting solution to close those scenarios without affecting anything.   You may have to adjust out the Accrued Purchases account for that line from a General Ledger perspective if nothing was invoiced against it.


Now, for those lines where the Shipment quantity is greater than the Invoice quantity and you close it, you would NOT want to close those via a SQL scripting solution.  When using the Edit Purchase Order Status window for manually closing, Accrued Purchases will be backed out in General Ledger for you automatically and the Item's cost will be adjusted.

Those scenarios you would NOT want to update via SQL scripting solution, or you will lose those adjustments.  The article I have linked above describes both scenarios in further detail.


I hope this information has proven helpful when considering whether to update a purchase order status in Purchase Order Processing or to use a SQL scripting solution.


Warmest Regards,

Angela Ebensteiner | Sr. Technical Advisor | Microsoft Dynamics GP