Tuesday, November 4, 2014

Purchase Order Cannot be Received after GP Crash During PO Receipt

A user who was in the middle of receiving a PO and GP 2013 crashed. Assuming the transaction would have been rolled back; the user logged back in and went to receive the PO, but received the following message "All lines have been received".
Looking at the PO showed nothing received and status of PO was Released.
Analysis of the POP10500 table, showed a receipt which does not exist in GP and status is still ZERO but quantity shipped shows data. Reconciling the PO does not resolve the issue.
Since the issue appeared to be caused by the errant receipt in the POP10500 table the first approach is to use the system to solve the problem. Try to open the Receipt in Dynamics GP and delete it. If this does not work, then there is one more possibility for an in system fix - Check Links.
Check Links is a maintenance tool, which validates the data in GP tables, and removes or recreates information based on what it finds. Goto Microsoft Dynamics GP > Maintenance > Check Links.  Change the series to Purchasing and then select the Purchasing Transactions table, click Insert and then click OK to process. Alternatively you can insert all Purchasing Tables. Then click Process. Check Links will produce a report of changes made to the system. Often times, these changes will make it possible to access transaction in GP again, edit, delete or post them as appropriate.
If Check Links doesn't resolve the issue, the next approach would be to delete the specific Receipt in the POP10500 by using the following SQL Script
DELETE POP10500 WHERE POPRCTNM='XXXX'
Note replace the 'XXXX' with the actual receipt number.  
Finally, run Check Links a second time to be sure the deleted record doesn't cause any problems, and then try to receive your PO.
If removing the errant record from the PO Receipt table does not resolve the issue. You can use the following scripts to analyze the Purchase Order itself to determine if the problem is the PO Line Status. If POLNESTA (PO Line Status) is set to Received (4), it will need to be reset to Released (2), in order to receive against it.
/* Set the PO Number below by changing 'XXXX' */
DECLARE @PONumber as VARCHAR(15)
SET @PONumber = 'XXXX'

select
POH.PONUMBER,
CASE POH.POSTATUS
      WHEN 1 THEN 'New'
      WHEN 2 ThEN 'Released'
      WHEN 3 THEN 'Change Order'
      WHEN 4 THEN 'Received'
      WHEN 5 THEN 'Closed'
      WHEN 6 THEN 'Cancelled'
      ELSE 'ERROR'
END PO_Status,
POL.LineNumber,
CASE POL.POLNESTA
      WHEN 1 THEN 'New'
      WHEN 2 THEN 'Released'
      WHEN 3 THEN 'Change Order'
      WHEN 4 THEN 'Received'
      WHEN 5 THEN 'Closed'
      When 6 THEN 'Cancelled'
      ELSE 'ERROR'
END PO_Line_Status,
POH.VENDORID Vendor_ID,
POH.VENDNAME Vendor_Name,
POL.ITEMNMBR Item_Number,
POL.ITEMDESC Item_Description,
* from POP10100 POH
Inner Join POP10110 POL on POH.PONUMBER = POL.PONUMBER
WHERE POH.PONUMBER = @PONumber

/* Run these queries only to change the PO Header and PO Line Status back to Released
Change 'XXXX' to the target PO Number prior to running these queries  */
--Update POP10110 set POLNESTA = '2' WHERE PONUMBER = 'XXXX'

1 comment:

  1. Thank you for this blog. This one solve my problem.

    ReplyDelete