SQL Queries for AP Payment Process Request (PPR) Process

a. Payment process request:

SELECT *
FROM iby_pay_service_requests
WHERE calling_app_id = 200
AND call_app_pay_service_req_code = '&&checkrun_name';

b. AP Payment Batch

select *
from AP_INV_SELECTION_CRITERIA_ALL
where checkrun_name = '&checkrun_name';

c. Payment Instructions

select pi.*
from IBY_PAY_INSTRUCTIONS_ALL pi
where pi.payment_instruction_id in
(SELECT payment_instruction_id
FROM iby_payments_all
WHERE payment_service_request_id in
(SELECT payment_service_request_id
FROM iby_pay_service_requests
WHERE calling_app_id = 200
AND call_app_pay_service_req_code = '&checkrun_name'));

d. Payments

SELECT *
FROM iby_payments_all
WHERE payment_service_request_id in
(SELECT payment_service_request_id
FROM iby_pay_service_requests
WHERE calling_app_id = 200
AND call_app_pay_service_req_code = '&checkrun_name');

e. Payment Documents

select *
from IBY_DOCS_PAYABLE_ALL
where PAYMENT_SERVICE_REQUEST_ID in
(SELECT payment_service_request_id
FROM iby_pay_service_requests
WHERE calling_app_id = 200
AND call_app_pay_service_req_code = '&checkrun_name');

f. Bank Account Payment Document

select *
from ce_payment_documents
where payment_document_id in
(select pi.payment_document_id
from IBY_PAY_INSTRUCTIONS_ALL pi
where pi.payment_instruction_id in
(SELECT payment_instruction_id
FROM iby_payments_all
WHERE payment_service_request_id in
(SELECT payment_service_request_id
FROM iby_pay_service_requests
WHERE calling_app_id = 200
AND call_app_pay_service_req_code = '&checkrun_name')));

g. Payment schedules

select *
from ap_payment_schedules_all
where invoice_id in
(select calling_app_doc_unique_ref2
from IBY_DOCS_PAYABLE_ALL
where PAYMENT_SERVICE_REQUEST_ID in
(SELECT payment_service_request_id
FROM iby_pay_service_requests
WHERE calling_app_id = 200
AND call_app_pay_service_req_code = '&checkrun_name'));

h. Checks

SELECT c.*
FROM ap_checks_all c
WHERE c.payment_id in
(SELECT ip.payment_id
FROM iby_payments_all ip
WHERE payment_service_request_id in
(SELECT payment_service_request_id
FROM iby_pay_service_requests
WHERE calling_app_id = 200
AND call_app_pay_service_req_code = '&checkrun_name'));

i. AP payment batch temp data for invoices

select *
from AP_SELECTED_INVOICES_ALL
where checkrun_name = '&checkrun_name';

j. AP payment batch temp data for checks

select *
from AP_SELECTED_INVOICE_CHECKS_ALL
where checkrun_name = '&checkrun_name';