Unable To UnAssign Items From An Organization BOM_DELETE_ITM_CON71 - OPM financial data exist for this item(CM_CMPT_DTL,GMF_LOT_COSTS,GMF_PERIOD_BALANCES) (Doc ID 2465941.1)

Last updated on APRIL 18, 2023

Applies to:

Symptoms

On : 12.2.4 version, Item Issues

Unable to un assign items from an Organization

Using 'Item Delete' screen encountered an Deletion Constraint that 'ITM_CON71' and Failure messages states:

We do have cost associated with these items in Cost organization but not in the organization that we are trying to unassign.
 

ERROR
-----------------------
OPM financial data exist for this item(CM_CMPT_DTL,GMF_LOT_COSTS,GMF_PERIOD_BALANCES)

Changes

 Validate checks:
-------------------

If the following queries returns a record, it means that there is OPM data exist for the item, the item could not be deleted, that's the expected behavior and should not be regarded as a bug.

SELECT 1
FROM DUAL
WHERE EXISTS (SELECT 1
FROM cm_cmpt_dtl
WHERE inventory_item_id = &item_id
AND organization_id = &organization_id)
OR EXISTS (SELECT 1
FROM cm_cmpt_dtl
WHERE inventory_item_id = &item_id
AND organization_id IN
(SELECT cost_organization_id
FROM cm_whse_asc
WHERE organization_id = &organization_id))
OR EXISTS (SELECT 1
FROM gmf_lot_costs
WHERE inventory_item_id = &item_id
AND organization_id = &organization_id)
OR EXISTS (SELECT 1
FROM gmf_period_balances
WHERE inventory_item_id = &item_id
AND organization_id = &organization_id)

Please check result for following query, if any query return records, it means item can not be deleted . It is the expected behavior:

Query 1:
---------
SELECT 1
FROM GMD_SPECIFICATIONS_B
WHERE INVENTORY_ITEM_ID = &ITEM_ID
AND OWNER_ORGANIZATION_ID = &ORGANIZATION_ID
and spec_status <> 1000
.
Query 3:
---------
SELECT 1
FROM GMD_TECHNICAL_DATA_HDR
WHERE INVENTORY_ITEM_ID = &ITEM_ID
AND ORGANIZATION_ID = &ORGANIZATION_ID
.
Query 3:
---------
SELECT 1
FROM GMD_TECHNICAL_SEQUENCE_HDR
WHERE INVENTORY_ITEM_ID = &ITEM_ID
AND ORGANIZATION_ID = &ORGANIZATION_ID
.
Query 4:
---------
SELECT 1
FROM LM_SPRD_DTL
WHERE INVENTORY_ITEM_ID = &ITEM_ID
AND ORGANIZATION_ID = &ORGANIZATION_ID
.
Query 5:
---------
SELECT 1
FROM GMD_RECIPE_VALIDITY_RULES
WHERE INVENTORY_ITEM_ID = &ITEM_ID
AND ORGANIZATION_ID = &ORGANIZATION_ID
.
Query 6:
---------
SELECT 1
FROM GMD_ITEM_SUBSTITUTION_HDR_B
WHERE ORIGINAL_INVENTORY_ITEM_ID = &ITEM_ID
AND OWNER_ORGANIZATION_ID = &ORGANIZATION_ID
.
Query 7:
---------
SELECT 1
FROM GMD_ITEM_SUBSTITUTION_DTL
WHERE INVENTORY_ITEM_ID = &ITEM_ID

For Applications 12.2, please apply patch 25565158:R12.ITM.D

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.