There are times when the Quantity and Amount of a specific item in a Transaction Saved Search shows a Negative value, even if you are expecting it to show as positive
The most common reason is: the GL accounts are reversed under the Item Record’s Accounting tab.
The normal account used for Item Accounts should be as follows:
- COGS Account field = COGS Account type
- Income Account field = Income Account type
It is possible that the accounts are interchanged as follows:
- COGS Account field = Income Account type
- Income Account field = COGS Account type
For example, I have an item which the Income Account field is populated with another account type (Expense). It seems that the display is based on the expected GL impact for a sales transaction.
Here is a sample of the search results:
This behavior is as system design. The Transaction Saved Search results shows Items on Sales Order which booked to an Income Account as positive values for their Quantity and Amount and if the item it uses a non-income account, it shows as a negative value in the search results.
It is important to note that the sign of the quantity in the search result does not necessarily reflect the inventory movement. It relates more to the account posted with it instead.
A possible solution for this one is to use a the ABS function or a CASE formula (if the quantity field is used in a formula) to get the positive values for the quantity:
Results tab of the search:
Formula used:
- Quantity: ABS({quantity})
- Qty to Pick: CASE WHEN {quantity} > 0 THEN {quantity}-nvl({quantityshiprecv},0) ELSE ABS({quantity})-ABS(nvl({quantityshiprecv},0)) END
- Qty in Backorder: CASE WHEN {quantity} > 0 THEN {quantity}-nvl({quantityshiprecv},0)-nvl({quantitycommitted},0) ELSE ABS({quantity})-ABS(nvl({quantityshiprecv},0))-ABS(nvl({quantitycommitted},0)) END
Hope you found this information useful! Have you encountered a similar scenario with this one? Feel free to share your workaround with the community as well! ?
