Search 800 + Posts

Nov 16, 2010

error in handle_nonbulk_mode ORA-01403: no data found( Related to Order Line Sets)

error in handle_nonbulk_mode ORA-01403: no data found

This error is very common error and result of pure data-corruption. There are various reasons for data-corruption.
1. Custom Code
2. Triggers
3. Back end triggers etc
These days I am working on Sets, so I will talk about this error in context of sets. The way Oracle is designed the Sets concept (Ship/Arrival) is, when we associate Ship/Arrival Sets with the Order lines it store this information in
  • SHIP_SET_ID
  • ARRIVAL_SET_ID

Columns of OE_ORDER_LINES_ALL, as well as INSERT data into OE_SETS table. And once we book the sales order system insert sets data into Shipping WSH_DELIVERY_DETAILS tables in column (SHIP_SET_ID, ARRIVAL_SET_ID). Please note that system doesn’t store fulfillment Set information in WSH_DELIVERY_DETAILS /OE_ORDER_LINES_ALL table, fulfillment set information captured in OE_SETS and OE_LINE_SETS table.

 
Sets deletion process is same as Insert process, moment we remove Ship/Arrival set information from order lines , system will remove the data from the SHIP_SET_ID/ARRIVAL_SET_ID columns of oe_order_lines_all and WSH_DELIVERY_DETAILS table and delete the data from OE_SETS table.

But many a times, because of data corruption, Sets data got deleted/removed from the OE_ORDER_LINES_ALL /OE_SETS table, but it still exists in WSH_DELIVERY_DETAILS table, and due to this inconsistency when we execute the ITS , system try to validate the these SETS information that exists in Shipping table against the OE_SETS table and result in NO_DATA_FOUND Error.

Workaround I discovered to resolve this issue -

Navigate to Shipping Transaction UI > Query for Delivery

Go to Content Tab

Help> Diagnostics > Examine > in the Field Select “SHIP_SET_ID” , Clear the Value and Press Save .

Run the Query

select w.ship_set_id,w.arrival_set_id

from wsh_delivery_details w

where source_header_id = xxxxxx


 If there is no Data , Run the ITS , It should completed Successfully.

  
Hope that helps

1 comment: