Many a Times we noticed duplicate data in oe_drop_ship_sources , or we try to perform virtual receiving in the system and receiving Transaction processor error out with following error message “ORA-01422: exact fetch returns more than requested number of rows
I did few test case and notice that once we
1.Enter and Book drop ship Sales Order.
2.Run Workflow Background process.
System inserts the data in Requisition Interface tables and oe_drop_ship_sources. At this time requisition and PO related columns in oe_drop_ship_sources are NULL.BUT Once we have requisition and PO got created these columns in oe_drop_ship_sources populated with respective values.
Now if we cancel an Approved PO, ideally system should erase the data from po_header_id,po_line_id, line_location_id columns of oe_drop_ship_sources , and once we creates a new PO from requisition via autocreate , system will update the old oe_drop_ship_sources record for PO related columns.
But once in a while I have notice that because of some error /issues data from po_header_id,po_line_id, line_location_id columns of oe_drop_ship_sources not erased at the time of cancellation of PO ,In such cases at the time of creation of New PO via autocreate from same requisition(already created from Drop Ship SO), a duplicate Record gets created in OE_DROP_SHIP_SOURCES Table ( when I say duplicate I mean exactly same as the previous record) . And now if we try to receive the good against these dropship purchase Order Receiving Transaction process will error out with 'ORA-01422: exact fetch returns more than requested number of rows ' error.Not only that any attempt to cancel the Sales Order lines alos return same error.