Payment Batch Status

In Rel12, in order to check payment batch status, one has to look at following three tables

1. ap_inv_selection_criteria_all


2. iby_pay_service_requests


3. iby_pay_instructions_all

SELECT * FROM ( SELECT checkrun_id , checkrun_name , status checkrun_status , ( SELECT payment_service_request_status FROM iby.iby_pay_service_requests WHERE calling_app_id = 200 AND call_app_pay_service_req_code = isc.checkrun_name ) pay_service_req_status , ( SELECT pia.payment_instruction_status FROM iby.iby_pay_instructions_all pia WHERE pay_admin_assigned_ref_code = isc.checkrun_name ) instruction_status , last_update_date FROM ap.ap_inv_selection_criteria_all isc ) WHERE 1 = 1 -- -- these conditions will return payment batches in process -- --AND checkrun_status NOT IN ('CANCELLED NO PAYMENTS','CANCELED','CONFIRMED') --AND NVL(pay_service_req_status,'X') NOT IN ('COMPLETED', 'TERMINATED', 'CONFIRMED') --AND NVL(instruction_status ,'X') NOT IN ('TERMINATED') ORDER BY last_update_date DESC;