Sage 500 Unexpected Error running Stored Procedure spsoGenInvcForShipment

The purpose of this post is to show how to replicate a Sage 500 ERP error "Error: Unexpected Error running Stored Procedure spsoGenInvcForShipment" and how to correct it. This has been reported to Sage Support as a bug. It does have a workaround so it is does not appear to be on the list of bugs to correct.

Is there are workaround? I won't say yes in my opinion, especially if you are a client that has to do this 5,000 plus times a day. We do however discuss how to get around the issue near the end of this post. We do have a case open with Sage but it may or may not be fixed in future versions and updates based on our discussions.

SYMPTOM:

We had a client report this error. They are running Sage 500 ERP version 7.40.5 (Sep10 w/Sep10 HotFix applied). This is able to be replicated in version 2013 and 2014 both with Jan14 PU.

NOTE: The steps below were done in version 2013 Jan14 PU (7.50.5) using the Demo Company "SOA". Your transaction numbers may differ depending on your prior usage, etc.

Steps to Replicate:

1. Open SO Module/Activities/Edit Shipments
2. Select Shipment with 'Pending' status, Use Shipment '0000000557-SH'

1. Click Generate Invoice and click Yes if prompted with Warnings

1. Click on Print Documents and preview or print

1. Click Commit button on toolbar to commit

1. Click Yes to ignore warnings (out of fiscal period)
2. Select next Shipment following steps above, use Shipment '0000000561'

1. Click on Generate Invoice button and error presents

   You will see the following messagebox, Error: Unexpected Error running Stored Procedure spsoGenInvcForShipment.

Workaround:

The interesting thing is you can actually process the out of box screen successfully if you do one of 2 actions.

• Close the screen and reopen it
• Click on a tab other than the Header (e.g. Lines tab), then click back on the Header tab

Additional background about the issue:
The fix ended up being in the Screen code not the database.  There is a flag set when the commit process runs successfully but it is never cleared.  The screen code effected includes: sozdr001.exe and sozdrdl1.dll.

After the commit process is completed, if the flag has been set to true (success) then we reset the flag back to false at the end of the commit process. Thus allowing additional invoices to be created and processed.

Cause:

This issue is caused because during the commit process there is a flag/variable (mbCommitSuccess) that is set to true if/when the commit process completes successfully. This same flag is used during the invoice creation process to determine if the AR Invoice API staging tables should be populated (specifically the #tarAPIValid). Because the flag is set to true the staging tables are not populated. However, there is nothing in the process of Invoice Creation that tells the system that the staging tables have not been populated. So the invoice creation process continues. The stored procedure (spsoGenInvcForShipment) is called which then calls a sub procedure (spsoAssignKeyTranNo). This prodedure (spsoAssignKeyTranNo) returns the unsuccessful code (because there is no data in the #tarAPIValid table to process), which causes the procedure (spsoGenInvcForShipment) to return an unsuccessful code. And the Edit Shipments screen returns an error to the user saying the stored Proceedure spsoGenInvcForShipment had an error. The error is not a code error but rather a data error that does not tell the users what is wrong or missing. A SQL trace does not capture this this either because it this not a code error, it's a missing data error.

I want to thank RKL Team members Dean and Carolyn for their efforts in uncovering and correcting the issue.