My Oracle Support Banner

Upon attepting to Inactivate Item - This field cannot be updated as there are Open Sales order lines (Doc ID 2545398.1)

Last updated on JULY 08, 2020

Applies to:

Oracle Inventory Management - Version 12.1.3 and later
Information in this document applies to any platform.

Symptoms

On : 12.1.3 version, Other Inventory Issues

When trying to inactivate the item get an error this field cannot be updated as there are Open Sales order lines. There are no sales order lines pending. no move orders and no pending transactions.

ERROR
-----------------------
This field cannot be updated as there are Open Sales order lines

STEPS TAKEN:
1) Executing INVORGASG.sql per APP-INV-05479 Standard Datafix To Find Orphan Item Records (Doc ID 1065107.1) did not resolve issue.

However there are records in the following tables per queries in Note 1065107.1:

mtl_item_revisions_b
cst_item_costs rev
eni_oltp_item_star eois

 

2) Inactivate Item Error 'This field can not be updated as there are either Open Sales Order lines, Pending Delivery transactions or Incoming Supply transaction. ' (Doc ID 2401577.1)

Did not resolve issue

 

3) Query:

select *
from wsh_delivery_details
where inventory_item_id = &p_item_id
and pickable_flag = 'Y'
and inv_interfaced_flag IN ('N','P')
and released_status <> 'D'
and organization_id =& p_org_id;

-- Did not return any records

 

4) All of the following scripts did not return any records:

1)
SELECT *
FROM wsh_delivery_details
WHERE inventory_item_id = &p_item_id
AND pickable_flag = 'Y'
AND inv_interfaced_flag IN ('N','P')
AND released_status <> 'D'
AND organization_id = &p_org_id
2)
SELECT *
FROM oe_order_lines_all
WHERE source_type_code = 'EXTERNAL'
AND open_flag = 'Y'
AND nvl (shipped_quantity
,0) = 0
AND item_type_code IN ('MODEL','STANDARD','OPTION')
AND flow_status_code = 'AWAITING_RECEIPT'
AND inventory_item_id = &p_item_id
AND ship_from_org_id = &p_org_id ;
3)
SELECT *
FROM oe_order_lines_all l
WHERE booked_flag = 'Y'
AND nvl (shipped_quantity
,0) = 0
AND inventory_item_id = &p_item_id
AND open_flag = 'Y'
AND ship_from_org_id = &p_org_id
AND EXISTS
(
SELECT 1
FROM mtl_transactions_interface
WHERE trx_source_line_id = l.line_id
AND transaction_source_type_id IN (2,8)
AND source_code = 'ORDER ENTRY'
);
4)
SELECT *
FROM mtl_supply
WHERE item_id = &p_item_id
AND to_organization_id = &p_org_id
5)
SELECT *
FROM wsh_delivery_details
WHERE inventory_item_id = &p_item_id
AND pickable_flag = 'Y'
AND inv_interfaced_flag IN ('N','P')
AND released_status <> 'D'
AND organization_id = i.organization_id;

6)

SELECT *
FROM oe_order_lines_all
WHERE source_type_code = 'EXTERNAL'
AND open_flag = 'Y'
AND nvl (shipped_quantity
,0) = 0
AND item_type_code IN ('MODEL','STANDARD','OPTION')
AND flow_status_code = 'AWAITING_RECEIPT'
AND inventory_item_id = &p_item_id
AND ship_from_org_id IN
(
SELECT organization_id
FROM mtl_parameters
WHERE master_organization_id = &l_master_org
);
7)
SELECT *
FROM oe_order_lines_all l
WHERE booked_flag = 'Y'
AND nvl (shipped_quantity
,0) = 0
AND inventory_item_id = &p_item_id
AND open_flag = 'Y'
AND ship_from_org_id IN
(
SELECT organization_id
FROM mtl_parameters
WHERE master_organization_id = &l_master_org
)
AND EXISTS
(
SELECT 1
FROM mtl_transactions_interface
WHERE trx_source_line_id = l.line_id
AND transaction_source_type_id IN (2,8)
AND source_code = 'ORDER ENTRY'
);
8)
SELECT *
FROM mtl_supply
WHERE item_id = & p_item_id
AND to_organization_id = i.organization_id;

 

5) All of the following scripts did not return any records:

select process_enabled_flag
from mtl_parameters
where organization_id = &org_id;

-----------------------
select *
from mtl_onhand_quantities_detail
where inventory_item_id = &item_id
and organization_id = &org_id

-----------------------
select *
from mtl_material_transactions_temp
where inventory_item_id = &item_id
and organization_id = &org_id

-----------------------
select *
from mtl_supply ms
where item_id = &item_id
AND (from_organization_id = &org_id OR to_organization_id = &org_id)

-----------------------
select *
from mtl_demand
where inventory_item_id = &item_id
and organization_id = &org_id

-----------------------
select *
from mtl_txn_request_lines
where inventory_item_id = &item_id
and organization_id = &org_id
and line_status = 7

-----------------------
select *
from mtl_lot_numbers
where inventory_item_id = &item_id
and organization_id = &org_id

------------------------
SELECT *
FROM csi_txn_errors cte, mtl_material_transactions mtl
WHERE cte.inv_material_transaction_id = mtl.transaction_id
AND cte.inv_material_transaction_id IS NOT NULL
AND mtl.inventory_item_id = &item_id
AND cte.processed_flag = 'E' ;

 

6) All scripts in When Inactivating Items in Oracle Item Master / Inventory Management get
error: This field can not be updated as there are Open Sales Order lines (Doc
ID 1501868.1)

...were executed and none returned any records.

 

 

7) All of the following scripts did not return any records:


1.
select *
from wsh_delivery_details
where inventory_item_id = :p_item_id
and pickable_flag = 'Y'
and inv_interfaced_flag IN ('N','P')
and released_status <> 'D'
and organization_id = :p_org_id
and rownum = 1;

2.
select *
from oe_order_lines_all
where source_type_code = 'EXTERNAL'
and open_flag = 'Y'
and nvl(shipped_quantity,0) = 0
and item_type_code in ('MODEL','STANDARD','OPTION')
and FLOW_STATUS_CODE = 'AWAITING_RECEIPT'
and inventory_item_id = :p_item_id
and ship_from_org_id = :p_org_id
and rownum = 1;

3.
select *
from oe_order_lines_all l
where booked_flag = 'Y'
and nvl(shipped_quantity,0) = 0
and inventory_item_id = :p_item_id
and open_flag = 'Y'
and ship_from_org_id = :p_org_id
and exists (select 1
from mtl_transactions_interface
where trx_source_line_id = l.line_id
and transaction_source_type_id in (2,8)
and source_code = 'ORDER ENTRY');

4.
select *
from mtl_supply
where item_id = :p_item_id
and to_organization_id = :p_org_id;

5.
SELECT *
FROM GME_MATERIAL_DETAILS D, GME_BATCH_HEADER H
WHERE D.INVENTORY_ITEM_ID = :p_item_id
AND D.ORGANIZATION_ID = :p_org_id
AND D.BATCH_ID = H.BATCH_ID
AND (NVL(H.GL_POSTED_IND, 0) = 0
AND H.BATCH_STATUS IN (1, 2, 3, 4));

 

 

8) All of the following scripts did not return any records:

Note: This is client-specific data. This will need to be modified for the specific client and their data.

1)
select *
from gmf_xla_extract_headers
where source_document_id in (56617412, 56617413, 62150427, 62150428);

2)
select gtv.*
from gmf_transaction_valuation gtv,
gmf_xla_extract_headers gxeh
where gtv.header_id = gxeh.header_id
and gxeh.source_document_id in (56617412, 56617413, 62150427, 62150428);

3)
select *
from mtl_material_transactions
where transaction_source_id in (56617412, 56617413, 62150427, 62150428);

4)
select giml.*
from gmf_incoming_material_layers giml,
mtl_material_transactions mmt
where giml.mmt_transaction_id = mmt.transaction_id
AND mmt.transaction_source_id in (56617412, 56617413, 62150427, 62150428);

5)
select *
from gme_resource_txns
where doc_id IN (56617412, 56617413, 62150427, 62150428);

 

9) All of the following scripts did not return any records:

1.
select *
from wsh_delivery_details
where inventory_item_id = :p_item_id
and pickable_flag = 'Y'
and inv_interfaced_flag IN ('N','P')
and released_status <> 'D'
and organization_id = :p_org_id
and rownum = 1;

2.
select *
from oe_order_lines_all
where source_type_code = 'EXTERNAL'
and open_flag = 'Y'
and nvl(shipped_quantity,0) = 0
and item_type_code in ('MODEL','STANDARD','OPTION')
and FLOW_STATUS_CODE = 'AWAITING_RECEIPT'
and inventory_item_id = :p_item_id
and ship_from_org_id = :p_org_id
and rownum = 1;

3.
select *
from oe_order_lines_all l
where booked_flag = 'Y'
and nvl(shipped_quantity,0) = 0
and inventory_item_id = :p_item_id
and open_flag = 'Y'
and ship_from_org_id = :p_org_id
and exists (select 1
from mtl_transactions_interface
where trx_source_line_id = l.line_id
and transaction_source_type_id in (2,8)
and source_code = 'ORDER ENTRY');

4.
select *
from mtl_supply
where item_id = :p_item_id
and to_organization_id = :p_org_id;

5.
SELECT *
FROM GME_MATERIAL_DETAILS D, GME_BATCH_HEADER H
WHERE D.INVENTORY_ITEM_ID = :p_item_id
AND D.ORGANIZATION_ID = :p_org_id
AND D.BATCH_ID = H.BATCH_ID
AND (NVL(H.GL_POSTED_IND, 0) = 0
AND H.BATCH_STATUS IN (1, 2, 3, 4));

 

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!


In this Document
Symptoms
Cause
Solution
References


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