My Oracle Support Banner

Multiple Errors/Issues Caused By A Cancelled Order Line With Open Delivery Details Like Pick Batch Fails (Doc ID 1291007.1)

Last updated on JUNE 14, 2022

Applies to:

Oracle Purchasing - Version 11.5.10.2 to 12.2.8 [Release 11.5 to 12.2]
Oracle Inventory Management - Version 11.5.10.2 to 12.2.8 [Release 11.5.10 to 12.2]
Oracle Shipping Execution - Version 11.5.10.2 to 12.2.8 [Release 11.5.10 to 12.2]
Oracle Order Management - Version 11.5.10.2 to 12.2.8 [Release 11.5.10 to 12.2]
Information in this document applies to any platform.
Executable:WSHREL - Pick Release
ConcurrentProgram:WSHPRS - Pick Release - SR
Reviewed for currency 27-June-2019


Symptoms

The query given in the CAUSE section will return one or more delivery details that are still open (shipped, ready to release, backordered, or released to warehouse, staged) when their associated order lines  are closed or cancelled.  You may have discovered this data corruption while attempting to clean up open delivery details, or one of following three symptoms may occur.

SYMPTOM #1 Pick Release Fails:

When you attempted to run Pick Release for many eligible orders,  an error similar to the following 3 errors appears at the end of the Pick Selection List Generation log file.

Example error 1

Error occurred in Inv_Pick_Release_Pub.Pick_Release
Can not create reservation for a closed sales order.
Error: Could not query reservations.
ORA-01407: cannot update ("INV"."MTL_TXN_REQUEST_LINES"."QUANTITY") to NULL in Package INV_Pick_Release_PUB Procedure Pick_Release
Unexpected error from INV_Pick_Release_Pub.Pick_Release. Exiting
SQLCODE: 1 SQLERRM: User-Defined Exception
Exception occurred in WSH_PICK_LIST

 

Example error 2

PICKREL: return error from update shipping attributes
Error occurred in Inv_Pick_Release_Pub.Pick_Release
Picking  rule fails to find a source locator  that meets the rules restrictions
Unexpected Error: Error in Routine WSH_USA_INV_PVT.Update_Inventory_Info,  Oracle Error - 100
ORA-01403: no data found
Unexpected error from INV_Pick_Release_Pub.Pick_Release. Exiting
SQLCODE: 1 SQLERRM: User-Defined Exception
Exception occurred in WSH_PICK_LIST

 

Example error 3

SQLCODE: -6502 SQLERRM: ORA-06502: PL/SQL: numeric or value error
Exception occurred in WSH_PICK_LIST.Release_Batch_Sub
Unexpected error has occured. Oracle error message is ORA-06502: PL/SQL: numeric or value error
Exiting WSH_PICK_LIST.RELEASE_BATCH_SUB - EXCEPTION:OTHERS (01/14/2014 05:39:11 PM,  156.12 seconds)
Return from Release_Batch_Sub
retcode ==> 2
errbuf ==> Exception occurred in WSH_PICK_LIST
Error occurred in Release_Batch_Sub
SQLCODE: 1 SQLERRM: User-Defined Exception
Exception occurred in WSH_PICK_LIST


There may be different error messages for this issue, but you will always see "Exception occurred in WSH_PICK_LIST" as the last error message.

 

Steps to reproduce this issue
-----------------------
The issue can be reproduced at will with the following steps:
1. Order Management super user
2. Navigate to Release Orders for Picking form, or run scheduled Pick Release SRS process
3. Enter pick release criteria to pick release any eligible delivery detail.  If running Pick Release SRS, the Pick Release Rule used will have criteria to select any eligible delivery detail.

Similar errors NOT caused by open delivery details on closed order lines:


Example 1
Error in Routine WSH_DELIVERY_AUTOCREATE.AUTOCREATE_DELIVERIES
Please reference the following notes:
Doc ID 738688.1, Doc ID 752084.1, and Doc ID 603019.1


Example 2
Pick_Confirm: error from inv_trx_mgr.process_trx_batch Total serial numbers does not match the transaction quantity
Error occurred in INV_Pick_Wave_Pick_Confirm_PUB.Pick_Confirm

NOTE :  If you see this error with the WSH_PICK_LIST exception, then this is an Inventory issue. Reference Doc ID 1390139.1.
 

 


SYMPTOM # 2:  Interface Trip Stop fails

 

Interface Trip Stop completes with the following error: 

error ORA-20002: 3133: Activity instance 'SHIP_LINE' is not a notified activity for item 'OEOL/nnnnnn'   (nnnnnn is the line_id of the affected order line)

There are many other reasons why this error may occur,  but on examining the Sales Order Analyzer output or other Order Information output,  you see that a cancelled order line has a delivery detail that is still opened, and quite possibly shows as shipped but not interfaced to OM or Inventory.

 

 

SYMPTOM #3: Inactivating Item Issue:


When attempting to inactivate an item from Oracle Inventory,  the following error results:  

This field cannot be updated as there are open Sales Order Lines


Steps to reproduce this issue
-----------------------
1.   As the Inventory user,  navigate to the Organization Items form
2.   Search for the item to be inactivated
3.   In the Main tab,  attempt to set the item status to 'inactive'
4.   Error Results   User cannot inactivate the item.
5.   In order Management,  search for open order lines with this item.
6.   No open order lines are found.

 


Symptom #4:  Unable to close an internal requisition in Purchasing, for an Internal order.

 

When attempting to close an internal requisition, the following error occurs:  

You cannot Finally Close this Requisition, since it has existing open intransit shipments.


Symptom #5:  Unable to purge old sales orders.

When attempting to run the Order Management Purge Sales orders concurrent request,  the request ends with the following error:

Entering OE_ORDER_PURGE_PVT.Check_And_Get_Detail : nnnnn
Open deliveries exist for this order.

Changes

 

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.