Hi everyone,

I wanted to talk to you about the ‘On Order’ amount on the Item Inquiry/Item Quantity Maintenance window. I’ve taken quite a few calls where the customer noticed the ‘On Order’ amount does not tie to the Purchase Order Processing Item inquiry window (zooming on the On order amount field). Either nothing shows in the list that is on order or the lines/quantities showing in the list are not adding up to the same amount as the ‘On Order’ amount.

The ‘On Order’ field displays the current quantity on purchase orders for the selected item. Depending on whether you've chosen to view item information for all the sites or for a specific site, the on-order quantity will display the on order quantities for all sites or for the specific site.

If you’re using Purchase Order Processing, the quantity displayed is increased as line items are entered on purchase orders and decreased as line items are entered on receipts. Only exception are line items entered on drop-ship purchase orders because drop ships don't update the ‘On Order’ quantity.

 

When the quantities do not tie, there are a couple of options to fix/determine the discrepancy.

  1. Run Purchase Order Processing reconcile by going to Purchasing > Utilities > Reconcile Purchasing Documents. Run this for all Purchase Orders and then recheck the ‘On Order’ value to zoom back and see if the values now tie. If not, move on to step 2.
  2. There may be an issue with the table. Look at the data in the backend to see what document(s) might be causing the issue.

a. Start by using the inquiry window to narrow in on the site that might be the issue. That just helps narrow down which documents to look at later. So if you notice the issue is with just the “WAREHOUSE” site when you compare the on order to the Purchase Order Processing Item inquiry , you can add the site to the script to narrow down the data to review.

b, Next, review the POP10110 (Purchase Order Line table) for the item in question and see what pulls. Example script below. Enter item number where I have XXX.

SELECT * FROM POP10110 WHERE ITEMNMBR = 'XXX'

 

If you determined it’s an issue with the number from a specific site use this script. YYY represents the site you want to focus in on.

SELECT * FROM POP10110 WHERE ITEMNMBR = 'XXX' and LOCNCODE = 'YYY'

 

If you see any data returned, focus on Purchase Orders with a line status (POLNESTA) of 1, 2 or 3 as they should be impacting the ‘On Order’ value.

POLNESTA value and meanings below:

1 = New

2 = Released/Printed but may have received some of the line.

3 = Change order  

4 = Received – Fully received line

5 = Closed

6 = Cancelled

 

Again, Status 1, 2 and 3 are what impacts the ‘On Order’ field. The other statuses do not.

Take a glance at the lines that impact the ‘On Order’ field. If any of the lines look damaged (missing required fields for example) use the ALL PO script to view the PO data in its entirety and see what needs to be done to fix the Purchase Order before running the reconcile again. I’ve seen situations where the data is just damaged overall and it’s best to delete the document from the tables. I’ve seen situations where the line is okay, but the header is missing or damaged or vise versa. You will need to be familiar with the Purchase order tables to know what to look for and how to fix. If you need assistance feel free to create a case for support and attach the results of all the scripts mentioned above so we can analyze the data for you.

 

Instructions for ALL PO.

GOAL OF THIS SQL STATEMENT – The goal of this statement is to review ALL data related to a specific Purchase Order. It is only a SELECT statement.  It will not update/delete data.

declare @PONUMBER char(20)
select @PONUMBER = 'POXXXX'

  1. Enter your Purchase Order number in place of POXXXXX
  2. Press (Ctrl + T) or click the Results To Text Button on the menu bar before executing to send the results to Text

      3. Execute the results against your company database.

      4. Right click the results, and Save As .rpt. if you need to send into support for help to review.

All POP scripts found here:

https://mbs2.microsoft.com/fileexchange/?fileID=71b0741f-cf46-4e27-bba6-8b64e5490f3e

 

3.  Lastly, if nothing is standing out as wrong and/or you fixed and problem POs, disable any 3rd party products that integrate with Microsoft Dynamics GP and run POP reconcile again to see if it fixes the issue. If it does, reach out to your 3rd party for help. We have come across a situation where a 3rd party was incorrectly updating the ‘On Order’ amount so I wanted to mention it.

 

Hope this is helpful!

 

 

Warmest Regards,

Angela Ebensteiner | SR Technical Advisor | Microsoft Dynamics GP