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.
Great! You save my night. Thanks
ReplyDelete