My Oracle Support Banner

Revision vs. Onhand / MOQD Mismatch : Revision Controlled Items Have On Hand Quantities With No Revision, Or Have Revision But Onhand Is NOT Revision Controlled (Doc ID 287290.1)

Last updated on AUGUST 16, 2022

Applies to:

Oracle Inventory Management - Version 11.5.10.2 and later
Information in this document applies to any platform.
INVIDITM.fmb : Item Master Form
Revision vs. Onhand / MOQD Mismatch
Standard Datafix (From Inventory PQE) : revloc_mismatch_fix.sql

Symptoms

Find a revision mismatch compared to onhand quantity (MTL_ONHAND_QUANTITIES_DETAIL).

There are two possible cases:

1) Item Revision Controlled But Onhand Has No Revision:

Revision controlled items have quantities on hand without the revision. The items cannot be used on transactions as the revision is required in order to move them.

2) Item NOT Revision Controlled But Onhand Has Revision:

The item should NOT have revision but there are onhand records with revision.

* Note: These statements and identification scripts focus on the onhand but a similar check could be done on the transaction data. Both the onhand and transaction data should be corrected.

Identification script

The following SQL checks the onhand table and reports any mismatches where revision control does not match the item setup.

Example Output

The following is example output:

Example Output
Onhand Revision Topic Revision Control Revision_QTY_Control_Code Inventory_Item_ID Organization_ID Primary_transaction_quantity Subinventory_Code Onhand_Quantities_ID
AA Item NOT Revision Controlled But Onhand Has Revision Disabled 1 155 207 173 CustReturn 272
<NULL> Item Revision Controlled But Onhand Has No Revision Enabled 2 441963 207 100 FGI 6520826

Changes

Upgrade and conversion of data. For example, you could have upgraded from 11i to R12 or 10.7 to 11i. 

Cause

To view full details, 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 a vibrant support community of peers and Oracle experts.