Hello,
I am working on a custom workflow related to AP Invoices and I have come across this very weird situation where the below query runs perfectly fine when run in any PL/SQL editor like Toad/SQL Developer but when run from within a custom Oracle Workflow, the query raises a NO_DATA_FOUND exception.
I put debug statements to capture the parameter values (lv_invoice_id and lv_hold_id being retrieved from workflow attributes) before and after the SELECT statement and noticed that the parameter values are being set properly.
SELECT match_type
INTO lv_match_type
FROM ap_invoices_all aia, ap_invoice_lines_all ail,ap_holds_all aha
WHERE ail.invoice_id = lv_invoice_id
AND line_type_lookup_code = 'ITEM'
AND aia.invoice_id = ail.invoice_id
AND aha.invoice_id = aia.invoice_id
AND aha.hold_id = lv_hold_id
AND ROWNUM =1;
Can someone suggest what could possibly be wrong with this? Have spent 2 days trying to debug this but no avail.
Any help would be greatly appreciated.