Inventory Standard Datafix Instruction #12,13,14: Cannot Issue Locator Controlled Item - Locator and Onhand Subinventory Do Not Match

(Doc ID 282480.1)

Last updated on FEBRUARY 16, 2017

Applies to:

Oracle Inventory Management - Version 11.5.10.2 and later
Information in this document applies to any platform.
FORM: INVTTMTX.FMB - Inventory Transactions
Inventory Standard Datafix Instruction #12,13,14
subnoloc_moqloc.sql
subloc_moqmismatch.sql
subloc_moqdnoloc_mod.sql

Symptoms

This document provides the detailed instructions for fixing locator issues related to mismatches of locator controlled items and onhand quantity. Also this document will show all the available information regarding the Root Cause of this data corruption.

Please follow up this document in the following order:
A. Identification Scripts: This will confirm if you are having this data corruption.
B. Verifying your file versions: This will allow you and support to confirm if you are already in a known fixed version.
C. Root Cause Analysis. It shows all the relevant information about this issue Root Cause.
D. Datafix Instructions. It will provide steps for fixing the data corruption.

A - Identification Scripts.

Please run the identification script (INV12.13.14_Ident.sql) uploaded into this document. Here is the text of the script as well:

PROMPT Identification script from Note:282480.1
PROMPT Inventory Standard Datafix Instruction #12,13,14: Cannot Issue Locator Controlled Item

PROMPT #1: Bug 4264604: Datafix: subloc_moqmismatch.sql
PROMPT Check for non-matching locators in the onhand quantity.

select moqd.ORGANIZATION_ID, moqd.SUBINVENTORY_CODE, moqd.INVENTORY_ITEM_ID, moqd.LOT_NUMBER, moqd.REVISION, moqd.LOCATOR_ID, count(*)
from MTL_ONHAND_QUANTITIES_DETAIL moqd
where moqd.LOCATOR_ID is not null
and not exists (select 1 from MTL_ITEM_LOCATIONS mil
      where mil.ORGANIZATION_ID = moqd.ORGANIZATION_ID
       and  mil.SUBINVENTORY_CODE = moqd.SUBINVENTORY_CODE
       and  mil.INVENTORY_LOCATION_ID = moqd.LOCATOR_ID)
and exists (select 1 from MTL_SECONDARY_INVENTORIES
       where SECONDARY_INVENTORY_NAME = moqd.subinventory_code
       and   ORGANIZATION_ID = moqd.ORGANIZATION_ID
       and   LOCATOR_TYPE in (2, 3) )
and exists (select 1 from MTL_PARAMETERS
       where ORGANIZATION_ID = moqd.ORGANIZATION_ID
       and  STOCK_LOCATOR_CONTROL_CODE = 4
       and  NOT (WMS_ENABLED_FLAG = 'Y' OR PROCESS_ENABLED_FLAG = 'Y'))
and exists (select 1 from MTL_SYSTEM_ITEMS msit
       where msit.ORGANIZATION_ID = moqd.ORGANIZATION_ID
       and   msit.INVENTORY_ITEM_ID = moqd.INVENTORY_ITEM_ID
       and   msit.SERIAL_NUMBER_CONTROL_CODE in (1, 6) )
group by moqd.ORGANIZATION_ID, moqd.SUBINVENTORY_CODE, moqd.INVENTORY_ITEM_ID, moqd.LOT_NUMBER, moqd.REVISION, moqd.LOCATOR_ID;


PROMPT #2: Bug 4264580: Datafix: subnoloc_moq_loc.sql
PROMPT Check if organization locator control is ‘None’ or Determined at subinventory level and the locator control at subinventory level is ‘None’.
select moqd.ORGANIZATION_ID, moqd.SUBINVENTORY_CODE, moqd.INVENTORY_ITEM_ID, moqd.LOCATOR_ID, moqd.LOT_NUMBER, moqd.REVISION, count(*)
from MTL_ONHAND_QUANTITIES_DETAIL moqd
where LOCATOR_ID is not null
and exists (select 1 from MTL_SECONDARY_INVENTORIES msi
      where msi.SECONDARY_INVENTORY_NAME = moqd.SUBINVENTORY_CODE
      and   msi.ORGANIZATION_ID = moqd.ORGANIZATION_ID
      and   msi.LOCATOR_TYPE = 1)
and exists (select 1 from MTL_PARAMETERS
      where ORGANIZATION_ID = moqd.ORGANIZATION_ID
      and  STOCK_LOCATOR_CONTROL_CODE in (1, 4)
      and  NOT (WMS_ENABLED_FLAG = 'Y' OR PROCESS_ENABLED_FLAG = 'Y'))
and exists (select 1 from MTL_SYSTEM_ITEMS msit
      where msit.ORGANIZATION_ID = moqd.ORGANIZATION_ID
      and   msit.INVENTORY_ITEM_ID = moqd.INVENTORY_ITEM_ID
      and   msit.SERIAL_NUMBER_CONTROL_CODE in (1, 6) )
group by moqd.ORGANIZATION_ID, moqd.SUBINVENTORY_CODE, moqd.INVENTORY_ITEM_ID, moqd.LOCATOR_ID, moqd.LOT_NUMBER, moqd.REVISION;


PROMPT #3a: Bug 4264563: Datafix: subloc_moqdnoloc.sql
PROMPT Onhand have records with Null Locator when Subinventory is locator controlled.

select moqd.ORGANIZATION_ID, moqd.SUBINVENTORY_CODE, moqd.INVENTORY_ITEM_ID, moqd.LOT_NUMBER, moqd.REVISION, moqd.LOCATOR_ID, count(*)
from MTL_ONHAND_QUANTITIES_DETAIL moqd
where LOCATOR_ID is null
and exists (select 1 from MTL_SECONDARY_INVENTORIES msi
       where msi.SECONDARY_INVENTORY_NAME = moqd.SUBINVENTORY_CODE
       and  msi.ORGANIZATION_ID = moqd.ORGANIZATION_ID
       and  msi.LOCATOR_TYPE in (2,3) )
and exists (select 1 from MTL_PARAMETERS
       where ORGANIZATION_ID = moqd.ORGANIZATION_ID
       and  STOCK_LOCATOR_CONTROL_CODE = 4
       and  NOT (WMS_ENABLED_FLAG = 'Y' OR PROCESS_ENABLED_FLAG = 'Y'))
and exists (select 1 from MTL_SYSTEM_ITEMS msit
       where msit.ORGANIZATION_ID = moqd.ORGANIZATION_ID
       and  msit.INVENTORY_ITEM_ID = moqd.INVENTORY_ITEM_ID
       and  msit.SERIAL_NUMBER_CONTROL_CODE in (1, 6) )
group by moqd.ORGANIZATION_ID, moqd.SUBINVENTORY_CODE, moqd.INVENTORY_ITEM_ID, moqd.LOT_NUMBER, moqd.REVISION, moqd.LOCATOR_ID;


PROMPT #3b: Bug 4264563: Datafix: subloc_moqdnoloc_itm.sql
PROMPT Onhand have records with Null Locator when when the locator control is enabled at the item level where the organization control and subinventory level controls indicate item level control.

SELECT
moqd.ORGANIZATION_ID org,
moqd.SUBINVENTORY_CODE sub,
moqd.INVENTORY_ITEM_ID item,
moqd.LOT_NUMBER,
moqd.REVISION,
moqd.LOCATOR_ID,
sum(PRIMARY_TRANSACTION_QUANTITY) trx_qty
from MTL_ONHAND_QUANTITIES_DETAIL moqd
where LOCATOR_ID is null
AND PLANNING_ORGANIZATION_ID = ORGANIZATION_ID
AND OWNING_ORGANIZATION_ID = ORGANIZATION_ID
AND PLANNING_TP_TYPE = 2
AND OWNING_TP_TYPE = 2
AND NVL(SECONDARY_TRANSACTION_QUANTITY,0) = 0
and exists (select 1 from MTL_SECONDARY_INVENTORIES msi
       where msi.SECONDARY_INVENTORY_NAME = moqd.SUBINVENTORY_CODE
       and   msi.ORGANIZATION_ID = moqd.ORGANIZATION_ID
       and   msi.LOCATOR_TYPE = 5 )
and exists (select 1 from MTL_PARAMETERS
       where ORGANIZATION_ID = moqd.ORGANIZATION_ID
       and  STOCK_LOCATOR_CONTROL_CODE = 4
       AND  NOT (WMS_ENABLED_FLAG = 'Y' OR PROCESS_ENABLED_FLAG = 'Y'))
and exists (select 1 from MTL_SYSTEM_ITEMS msit
       where msit.ORGANIZATION_ID = moqd.ORGANIZATION_ID
       and   msit.INVENTORY_ITEM_ID = moqd.INVENTORY_ITEM_ID
       and   msit.SERIAL_NUMBER_CONTROL_CODE in (1, 6)
       and   msit.LOCATION_CONTROL_CODE in (2,3) )
group by moqd.ORGANIZATION_ID, moqd.SUBINVENTORY_CODE, moqd.INVENTORY_ITEM_ID, moqd.LOT_NUMBER,moqd.REVISION,moqd.LOCATOR_ID;

Script Limitations

The script will NOT identify corruptions in warehouse management (WMS) or process manufacturing (OPM) organizations. If the flag is enabled for WMS or OPM, the data is not reviewed.

Symptoms

Find onhand quantity for a locator controlled subinventory without locators.

Attempting to select a locator during a miscellaneous transaction results in the following error.

Note: You might also want to check out: <Note 568012.1> "FAQ: Inventory Standard Datafixes" for a full list of common datafixes and the related root-cause patches or existing bugs.

Error
You have entered an invalid locator.

Steps
Goto Inventory: Transactions > Miscellaneous Transactions (Misc Issue)
Select a locator controlled item with onhand quantity.
Try to enter or select the locator.
Receive the error.

Business Impact
Due to this issue, users cannot issue the onhand quantity out of inventory.

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