In Items Form INVIDITM, LOV Does Not Include "No Control" For Lot Expiration (Shelf Life) Control Attribute (文档 ID 2081458.1)

转到底部

In this Document

Symptoms

Changes

Cause

Solution

References

APPLIES TO:

Oracle Inventory Management - Version 11.5.10.1 and later
Oracle Item Master - Version 12.1.3 to 12.1.3 [Release 12.1]
Information in this document applies to any platform.
Cannot update SHELF_LIFE_CODE to "No Control"
INVIDITM.fmb, LOV value "No Control" is missing for Lot Expiration (Shelf Life) Control Attribute
INCOIN: INV_LOT_QOH_CANNOT_UPDATE when trying to update SHELF_LIFE_CODE

SYMPTOMS

ACTUAL BEHAVIOR

When attempting to modify the Lot Expiration (Shelf Life) Control Attribute in the Organization Items Form (INVIDITM.fmb), Inventory Tab the user cannot access option 'No Control' in the List of Values (LOV).

NOTE:
When attempting to update the same attribute (SHELF_LIFE_CODE) via Import Items, the error is returned:
COLUMN NAME : SHELF_LIFE_CODE
MESSAGE NAME : INV_LOT_QOH_CANNOT_UPDATE
ERROR MESSAGE : You cannot update this field when
1) there is on-hand quantity
OR
2) transactions exist
OR
3) lots exist.

EXPECTED BEHAVIOR

Should be able to access and select attribute value 'No Control'

STEPS

The issue can be reproduced at will with the following steps:
1) Responsibility: Inventory Super User
2) Navigation: Items / Organization Items
3) Select an organization and query an item.
4) Click on the Inventory Tab and click in the LOV for attribute Lot Expiration (Shelf Life) Control
5) The Option 'No Control' does not present.

CHANGES

CAUSE

There is On-hand, or there are Pending Transactions for this Item.

Per Development: The 'No Control' option does not show when there are pending transactions in one of the following tables:

  MTL_ONHAND_QUANTITIES_DETAIL
  MTL_TXN_REQUEST_LINES
  MTL_SUPPLY
  MTL_DEMAND
  MTL_MATERIAL_TRANSACTIONS_TEMP

SOLUTION

To implement the solution, please execute the following steps:

1. Run the following script to confirm control level of the attribute.

											  SELECT ATTRIBUTE_NAME, USER_ATTRIBUTE_NAME_GUI,

  DECODE(CONTROL_LEVEL,1,'MASTER',2,'ORGANIZATION',3,'VIEW ONLY') CTL_LVL

  FROM MTL_ITEM_ATTRIBUTES

  WHERE ATTRIBUTE_NAME LIKE 'MTL_SYSTEM_ITEMS.%SHELF_LIFE_CODE%';

2. Determine the ORGANIZATION_ID and INVENTORY_ITEM_ID associated with the item with the issue:

											  SELECT INVENTORY_ITEM_ID, ORGANIZATION_ID, SEGMENT1, LOT_CONTROL_CODE

  FROM MTL_SYSTEM_ITEMS_B

  WHERE SEGMENT1 = '&ITEMNAME';

(If the attribute being changed is master controlled, you will need to obtain the MASTER_ORGANIZATION_ID)

											  SELECT ORGANIZATION_ID, ORGANIZATION_CODE

  FROM MTL_PARAMETERS

  WHERE ORGANIZATION_CODE IN ('&ORGCODE', '&MASTER_ORG_CODE');

3. Check files for pending Inventory transactions:

a) Are there uncosted transactions?

											  SELECT *

  FROM MTL_MATERIAL_TRANSACTIONS

  WHERE INVENTORY_ITEM_ID = &ITEM_ID

  AND ORGANIZATION_ID = &ORG_ID -- comment this line if attribute master controlled

  -- AND (ORGANIZATION_ID IN (SELECT ORGANIZATION_ID FROM MTL_PARAMETERS WHERE MASTER_ORGANIZATION_ID = &MASTERORGID))

  -- uncomment the previous line if attribute is master controlled

  AND COSTED_FLAG IS NOT NULL;

b) Are there pending transactions in MTL_MATERIAL_TRANSACTIONS_TEMP?

											  SELECT *

  FROM MTL_MATERIAL_TRANSACTIONS_TEMP

  WHERE INVENTORY_ITEM_ID = &ITEM_ID

  AND ORGANIZATION_ID = &ORG_ID -- comment this line if attribute master controlled

  -- AND (ORGANIZATION_ID IN (SELECT ORGANIZATION_ID FROM MTL_PARAMETERS WHERE MASTER_ORGANIZATION_ID = &MASTERORGID))

  -- uncomment the previous line if attribute is master controlled

;

c) Are there pending transactions in MTL_TRANSACTIONS_INTERFACE?

											  SELECT *

  FROM MTL_TRANSACTIONS_INTERFACE

  WHERE INVENTORY_ITEM_ID = &ITEM_ID

  AND ORGANIZATION_ID = &ORG_ID -- comment this line if attribute master controlled

  -- AND (ORGANIZATION_ID IN (SELECT ORGANIZATION_ID FROM MTL_PARAMETERS WHERE MASTER_ORGANIZATION_ID = &MASTERORGID))

  -- uncomment the previous line if attribute is master controlled

;

RESOLUTION: Correct and process any transactions returned.
For Assistance see Note 1069492.1 : Resolving Period Close Pending Transaction R12

4. Check for on-hand quantities:

											  SELECT * FROM MTL_ONHAND_QUANTITIES_DETAIL

  WHERE INVENTORY_ITEM_ID = &ITEM_ID

  AND ORGANIZATION_ID = &ORG_ID -- comment this line if attribute master controlled

  -- AND (ORGANIZATION_ID IN (SELECT ORGANIZATION_ID FROM MTL_PARAMETERS WHERE MASTER_ORGANIZATION_ID = &MASTERORGID))

  -- uncomment the previous line if attribute is master controlled

;

RESOLUTION: Issue associated quantities out-of-stores before trying to update the item.

5. Check for pending Supply or Demand
a) Pending supply

											  SELECT *

  FROM MTL_SUPPLY

  WHERE ITEM_ID = &ITEM_ID 

  AND (TO_ORGANIZATION_ID = &ORG_ID OR FROM_ORGANIZATION_ID = &ORG_ID) -- comment line if attribute master controlled

  -- AND((TO_ORGANIZATION_ID IN (SELECT ORGANIZATION_ID FROM MTL_PARAMETERS WHERE MASTER_ORGANIZATION_ID = &MASTERORGID))

  -- OR (FROM_ORGANIZATION_ID IN (SELECT ORGANIZATION_ID FROM MTL_PARAMETERS WHERE MASTER_ORGANIZATION_ID = &MASTERORGID)))

  -- uncomment the previous lines if attribute is master controlled

;

RESOLUTION:
Process or cancel associated Requisitions or Purchase Orders. Log a service request with Purchasing if help is needed to resolve

b) Pending Demand

											  SELECT *

  FROM MTL_DEMAND

  WHERE INVENTORY_ITEM_ID = &ITEM_ID

  AND ORGANIZATION_ID = &ORG_ID -- comment this line if attribute master controlled

  -- AND (ORGANIZATION_ID IN (SELECT ORGANIZATION_ID FROM MTL_PARAMETERS WHERE MASTER_ORGANIZATION_ID = &MASTERORG_ID))

  -- uncomment the previous line if attribute is master controlled

  ;

RESOLUTION: Process or Cancel associated Sales or Internal Orders. Log a service request with Order Management if you need help clearing these records.

6. Search for pending move orders

											  SELECT MTRL.LINE_ID, MTRH.HEADER_ID, MTRH.REQUEST_NUMBER, MTRL.ORGANIZATION_ID, FU.USER_NAME

  FROM MTL_TXN_REQUEST_LINES MTRL, MTL_TXN_REQUEST_HEADERS MTRH, FND_USER FU

  WHERE MTRL.INVENTORY_ITEM_ID = &ITEM_ID

  AND ORGANIZATION_ID = &ORG_ID -- comment this line if attribute master controlled

  -- AND (ORGANIZATION_ID IN (SELECT ORGANIZATION_ID FROM MTL_PARAMETERS WHERE MASTER_ORGANIZATION_ID = &MASTERORGID))

  -- uncomment the previous line if attribute is master controlled

  AND MTRL.LINE_STATUS = 7 --- PREAPPROVED STATUS

  AND MTRL.HEADER_ID = MTRH.HEADER_ID

  AND MTRL.CREATED_BY = FU.USER_ID;

RESOLUTION: Log a service request with Inventory / Move Orders if you need help clearing these records.

7. Confirm that the data is corrected when viewed in the Oracle Applications.

You can use the following steps:
a) Responsibility: Inventory Super User
b) Navigation: Items / Organization Items
c) Select an organization and query an item.
d) Click on the Inventory Tab and click in the LOV for attribute Lot Expiration (Shelf Life) Control
e) The Option 'No Control' should now be included.

8. If you are satisfied that the issue is resolved, migrate the solution as appropriate to other environments.

REFERENCES