Onhand Quantities Form (INVTVQOH) Shows Zero Quantity For Locators

(Doc ID 282928.1)

Last updated on AUGUST 09, 2010

Applies to:

Oracle Inventory Management - Version: 11.5.8 to 11.5.10.CU2 - Release: 11.5 to 11.5
Information in this document applies to any platform.
INVMATWB.fmb INVTVQOH.fmb MTL_ONHAND_QUANTITIES

Symptoms

When checking the onhand quantities for locator controlled items, users see '0' (Zero) instead of 'no records found'.
Onhand quantity can be reviewed in a number of forms including the onhand quantity form (INVTVQOH.fmb) as well as the material workbench form (INVMATWB.fmb).  See <> for details on the various forms.
Both forms show onhand quantity of '0' (Zero) instead of 'no records found'.

EXPECTED RESULTS

Expect items with onhand of zero not to display in the onhand quantity form.
Expect the search for these items to result in the following message:
"On-hand quantity is not available"

Run the following script and get results from either SQL#1 or SQL#2 where the balance of the onhand quantity is zero. Expect the queries to return no rows.

SQL #1:
SELECT
        MSI.SEGMENT1 ITEM_NUMBER,
        OH.INVENTORY_ITEM_ID,
        OH.ORGANIZATION_ID ORG,
        OH.SUBINVENTORY_CODE SUB,
        NVL(OH.REVISION,'!@#') REV,
        NVL(OH.LOCATOR_ID, -999) LOC
FROM
        MTL_ONHAND_QUANTITIES_DETAIL OH,
        MTL_SYSTEM_ITEMS_B MSI
WHERE
        MSI.INVENTORY_ITEM_ID = OH.INVENTORY_ITEM_ID
        AND MSI.ORGANIZATION_ID = OH.ORGANIZATION_ID
GROUP BY
        MSI.SEGMENT1,
        OH.INVENTORY_ITEM_ID,
        OH.ORGANIZATION_ID,
        OH.SUBINVENTORY_CODE,
        OH.REVISION,
        OH.LOCATOR_ID
HAVING SUM(OH.TRANSACTION_QUANTITY) = 0
/
Returns rows when it should not.

SQL #2:
SELECT
        PRIMARY_TRANSACTION_QUANTITY,
        TRANSACTION_QUANTITY,
        COUNT(*)
FROM
        MTL_ONHAND_QUANTITIES_DETAIL
WHERE
        PRIMARY_TRANSACTION_QUANTITY = 0 OR TRANSACTION_QUANTITY = 0
GROUP BY
        PRIMARY_TRANSACTION_QUANTITY, TRANSACTION_QUANTITY
/
Returns rows when it should not.

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms