DynDeveloper.com

Summary

When trying to update a purchase order via an integration eConnect sometimes throws error '11482' (Cancel amount cannot be greater than quantity remaining). After doing some research I have discovered that this error is thrown when a POP10110 (PO Line) record has a negative 'QTYUNCMTBASE' value. Below I go into detail about why this error is thrown but I need help determining how best to avoid it. Does anyone have more information about how this error interacts with negative 'QTYUNCMTBASE' values?

Why is the Error Being Thrown?

The error is thrown from the below SQL block and uses a number of different parameters.

IF (@I_vQTYCANCE * @cUMQTYINB) > (@cQTYUNCMTBASE + (@cQTYCANCE * @cUMQTYINB))
BEGIN
SELECT @O_iErrorState = 11482

EXEC @iStatus = taUpdateString @O_iErrorState,
@oErrString,
@oErrString OUTPUT,
@O_oErrorState OUTPUT
END

The parameters that start with '@I_v' are given by the user/application. Ones starting with '@c' are taken directly from the database.

For the initial analysis I focused on a single purchase order. For that particular order the first line item was throwing the error. I boiled down the above if statement to determine why this was true.

IF (@I_vQTYCANCE * @cUMQTYINB) > (@cQTYUNCMTBASE + (@cQTYCANCE * @cUMQTYINB))

I knew the given value of the '@I_vQTYCANCE' was '0'. So the statement then became…

IF (0 * @cUMQTYINB) > (@cQTYUNCMTBASE + (@cQTYCANCE * @cUMQTYINB))
IF (0) > (@cQTYUNCMTBASE + (@cQTYCANCE * @cUMQTYINB))

I also knew that the current database value for the '@cQTYCANCE' was '0'. So the statement progressed…

IF (0) > (@cQTYUNCMTBASE + (0 * @cUMQTYINB))
IF (0) > (@cQTYUNCMTBASE + (0))

Which ultimately became…

IF (0) > (@cQTYUNCMTBASE)

Looking at this it's easy to see what's happening. The error will be thrown if the '@cQTYUNCMTBASE' parameter has a negative value. The '@cQTYUNCMTBASE' parameter is simply the current value of the 'QTYUNCMTBASE' on the 'POP10110' record at the time the eConnect stored procedure is run. So, I looked at the problem 'POP10110' record to determine if the 'QTYUNCMTBASE' value was indeed negative and confirmed that it was.

So we know the error is being thrown because the 'QTYUNCMTBASE' value is negative... but why is it negative? Is that an accepted value?

How Did the 'QTYUNCMTBASE' Value Become Negative?

I looked at one of the orders throwing the eConnect error and found that the line item in question did indeed have a negative 'QTYUNCMTBASE' value. The 'QTYUNCMTBASE' column was set to '-9175' for the first line of the 'P0474258' order. But why -9175?

When editing a PO eConnect sets the 'QTYUNCMTBASE' value using the following logic.

QTYUNCMTBASE = CASE 
WHEN @I_vPOLNESTA = 1
THEN ((@I_vQUANTITY - @I_vQTYCANCE) * @UMQTYINB)
ELSE ((@I_vQUANTITY - @I_vQTYCANCE) * @UMQTYINB) - @cQTYSHPPD1
END

I know the 'I_vPOLNESTA' of the line is not '1' in this case (it pretty much never is in my case) so I looked at the 'ELSE' block.

ELSE ((@I_vQUANTITY - @I_vQTYCANCE) * @UMQTYINB) - @cQTYSHPPD1

The first parameters are easy to identify.

The last one is a calculated field that appears further up in the stored procedure and sums together all the 'QTYSHPPD' values on associated 'POP10500' records.

SELECT @cQTYSHPPD1 = isnull(sum(QTYSHPPD * UMQTYINB), 0)
FROM POP10500(NOLOCK)
WHERE PONUMBER = @I_vPONUMBER
AND POLNENUM = @I_vORD
AND STATUS = 1

Now that we know where these values are coming from we can evaluate the ELSE block that is responsible for setting the 'QTYUNCMTBASE' value on the line. In this case it boils down to...

ELSE ((@I_vQUANTITY - @I_vQTYCANCE) * @UMQTYINB) - @cQTYSHPPD1

I know that the '@I_vQTYCANCE' is '0'...

ELSE ((@I_vQUANTITY - 0) * @UMQTYINB) - @cQTYSHPPD1

ELSE ((@I_vQUANTITY) * @UMQTYINB) - @cQTYSHPPD1

I know that the '@UMQTYINB' is '1'...

ELSE ((@I_vQUANTITY) * 1) - @cQTYSHPPD1

So then we have...

ELSE (@I_vQUANTITY - @cQTYSHPPD1)

And in the case of the first line of PO 'P0474258' the values ended up being.

ELSE (75,000 - 84,175)

Which, when you do the math, equals -9175... the value we see after updating the order. This means eConnect is setting the value of the lines 'QTYUNCMTBASE' to a negative value when updating an order.

Is It Okay for This Value to Be Negative?

Unfortunately, I don't know. I don't yet understand why the sum of the 'QTYSHPPD' values of associated 'POP10500' records is larger than the quantity on the PO line. Does this mean that a user has "over-received" product for a line? Something else?

Potential Fixes

When using the Dynamics GP purchasing reconcile utility on these orders the tool will revert the 'QTYUNCMTBASE' to '0' if it's negative. This will prevent the issue from occurring but eConnect will set the 'QTYUNCMTBASE' to a negative value again after a successful update (see above) if the sum of the 'QTYSHPPD' values for the receipts on the line are greater than the quantity ordered. So this tool would need to be run after anyone updates a line with these specific conditions.

We could update the 'taPoLinePost' procedure to correct these negative 'QTYUNCMTBASE' values after updating an order but I still don't know enough about the data to feel extremely comfortable doing that.

Conclusion

There appears to be some purchase orders where the sum of all the received quantities is more than the quantity listed on the purchase order line. When editing an order like this via eConnect, the 'QTYUNCMTBASE' is set to a negative number. When the order is then edited again eConnect is using the 'QTYUNCMTBASE' in the if statement that determines if error 11482 should be thrown. The if statement becomes 'true' because 'QTYUNCMTBASE' has a negative value and the stored procedure cannot complete successfully.

There does not appear to be anything the user can do to avoid this issue once you get a negative 'QTYUNCMTBASE' value.

Does anyone know more about this situation or have any idea how to avoid this eConnect error?