Monday, January 5, 2015

Apparent bug in eConnect taSopSerial stored procedure for invoice with negative quantities

By Steve Endow

This is an obscure one, but I thought I would document it for the Google-verse.

I developed an eConnect SOP Invoice import for a GP 2010 customer that sells and services lots of serialized items.  When they visit a customer, they may find that an item needs service, but the customer needs a loaner item in the meantime.

To record this transaction, they will create an invoice with the loaner item with quantity 1, and then have a second line for the serialized item being brought back to the shop for repair with a quantity of -1.  It is a little unusual, but if you have an invoice with a serialized item with quantity -1, it allows you to bring that serialized item into inventory.  

This process works well for invoices entered directly into Dynamics GP.  But if you try and import an invoice with a serialized item with a negative quantity, you'll get this error.

"taSopSerial Error - Serial Number does not exist in Item Serial Number Master -IV00200"

Notice that the Quantity node is -1.  The customer is trying to receive the serial number into inventory, but eConnect isn't allowing it.

If you review the SQL script for the eConnect taSopSerial stored procedure, you'll see this tidbit somewhere around line 215-230, depending on the formatting.

      if ( @I_vSOPTYPE = 3 
         and @I_vQUANTITY = -1 ) 
          select @I_vQUANTITY = 1 

For some reason, the taSopSerial procedure is converting quantities of -1 to 1 for invoices.  The eConnect procedures don't include any comments, so it isn't clear why this is occurring in the 1800+ line procedure.

A few lines later in the procedure, there is validation for quantities of -1 and 1, so this would seem to indicate that the above lines aren't valid.  This code is explicitly acknowledging that an invoice item can have a quantity of -1.

        if ( @I_vQUANTITY not in ( -1, 1 ) ) 
        or ( @I_vQUANTITY = -1 
             and @I_vSOPTYPE <> 3 ) 

And only changing the value of lines with quantity of -1 seems pretty odd (why not all negative quantities?), and it just happens to affect my customer's import of invoices with serialized items that have a -1 quantity.

Since the GP client allows the entry of -1 for serialized items on an invoice, it isn't clear why eConnect would not, and whether this is a bug, or if there is a specific reason why eConnect would not allow a -1 line item quantity.

To work around this, we commented out those 5 lines, and that resolved the issue.  The invoices with -1 quantities for the serialized items import just fine, and after using the modified taSopSerial procedure for over a year, the client hasn't had any issues.

Except when they update or upgrade GP.  They recently applied a service pack for the 2014 year end updates, and that update apparently dropped and recreated the taSopSerial procedure.  So whenever they do an update, we have to remember why the error is occurring again, and then modify the new taSopSerial procedure.

I verified that this "problem" code also exists in GP 2013.  I have not yet checked GP 2015.

Steve Endow is a Microsoft MVP for Dynamics GP and a Dynamics GP Certified IT Professional in Los Angeles.  He is the owner of Precipio Services, which provides Dynamics GP integrations, customizations, and automation solutions.

You can also find him on Google+ and Twitter

No comments: