A single music track, movie, event, product… changes the life for many & in my case it was a single API by Oracle!
“oe_order_pub.process_order” that comes with Oracle Applications (We are using EBS R12)
The firm for which I work is using an inhouse developed module for the complete retailing & I take the credit of developing the full solution using this ONE API/around this API.
From a mere “Oracle Forms & Reports” developer with some knowledge about Oracle database, developing around this single API to facilitate Sales Orders and Returns (RMA) slowly shaped me in to whatever I am today.
We are using this custom module from last 11 years and I wouldn’t say there were no issues. We’ve many sales outlets and most of them are connected to the datacenter using ADSL data lines. Sometimes the connections caused, other times code caused, few other times some internal bugs caused problems were there, however against the volume of sales transactions those we make yearly, limited to numbers those could be counted in fingers.
So, recently I was contacted by the sales team, to resolve an issue with a sales return, with lines stuck “Awaiting Return” status. While inspecting the transaction, I realized that the salesmen tried to return this SO multiple times and instead of 2 lines against the sales order, there were 20 lines (10 attempts). I cleared whole those lines with errors and tried to receive the materials once again, bringing up the error:
RVTPT-020: Subroutine rvtoe_RmaPushApi() - EQuantity cannot be greater than original ordered quantity. returned error Cause: Subroutine rvtoe_RmaPushApi() - EQuantity cannot be greater than original ordered quantity. returned an in
Eventually, I landed upon the Oracle support document “RMA Receipt Error:RVTPT-020: Subroutine rvtoe_RmaPushApi() – EQuantity Cannot Be Greater Than Original Ordered Quantity (Doc ID 2409611.1)” & according to the document, this situation arises when there are multiple transactions trying to do a RMA against the same quantities! So I ran couple of quick queries like below:
Select * from oe_order_lines_all where header_id = (Select header_id from oe_order_headers_all where order_number='18016698');
Fetched all the line ids from the lines table against the order number & then tried to see where exactly the line ids were refernced. Whenever a RMA is facilitated the lines table fills in the columns “REFERENCE_HEADER_ID” and “REFERENCE_LINE_ID” with the header_id and line_id values from the original sales order. All I had to make sure that the line ids were referenced multiple times.
Select * from oe_order_lines_all where reference_line_id IN (4656844, 4656845);
As expected, I was able to find four lines (expected 2 lines only) and was able to track down the 2nd RMA that was automatically created by the API due to some unknown reasons (I said there were few problems using the API)
Based on the suggestions available with the support document, I cancelled the duplicate RMA transaction (Actually another Sales Order with the next immediate document number) & created a new receipt for the Sales Order that was stuck with lines having “Awaiting Return” flow status.
Hope this helps few out there.