R12 – Queries to Troubleshoot Payments

The following queries can be used to Troubleshoot Payments in R12.    In the example below replace ‘PAYMENT_PROCESS_REQUEST_NAME’ with your Payment file name.

select distinct * from ap_inv_selection_criteria_all where checkrun_name = ‘PAYMENT_PROCESS_REQUEST_NAME’;

select distinct * from iby_pay_service_requests where call_app_pay_service_req_code = ‘PAYMENT_PROCESS_REQUEST_NAME’;

select distinct * from IBY_PAY_INSTRUCTIONS_ALL where PAY_ADMIN_ASSIGNED_REF_CODE = ‘PAYMENT_PROCESS_REQUEST_NAME’;

select * from ap_invoices_all where invoice_id in
( select calling_app_doc_unique_ref2 from iby_docs_payable_all where payment_service_request_id in
( select a.payment_service_request_id from iby_pay_service_requests a where a.call_app_pay_service_req_code = ‘PAYMENT_PROCESS_REQUEST_NAME’));

select distinct * from ap_selected_invoices_all where checkrun_name = ‘PAYMENT_PROCESS_REQUEST_NAME’;

select distinct * from ap_selected_invoice_checks_all where checkrun_name = ‘PAYMENT_PROCESS_REQUEST_NAME’;

select distinct * from ap_checks_all where checkrun_name = ‘PAYMENT_PROCESS_REQUEST_NAME’;

select distinct * from ap_payment_schedules_all where checkrun_id in
( select a.checkrun_id from ap_inv_selection_criteria_all a where checkrun_name = ‘PAYMENT_PROCESS_REQUEST_NAME’);

select * from iby_docs_payable_all where payment_service_request_id in
( select a.payment_service_request_id from iby_pay_service_requests a where a.call_app_pay_service_req_code = ‘PAYMENT_PROCESS_REQUEST_NAME’);

select * from iby_payments_all where payment_service_request_id in
( select a.payment_service_request_id from iby_pay_service_requests a where a.call_app_pay_service_req_code = ‘PAYMENT_PROCESS_REQUEST_NAME’);

/*
INSTRUCTION_CREATED
FORMATTED
SUBMITTED_FOR_PRINTING
ISSUED
*/

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 b.payment_instruction_id FROM iby_payments_all b WHERE b.payment_service_request_id in
( SELECT a.payment_service_request_id FROM iby_pay_service_requests a WHERE a.calling_app_id = 200 AND a.call_app_pay_service_req_code = ‘PAYMENT_PROCESS_REQUEST_NAME’)));

select distinct * from iby_acct_pmt_profiles_b where PAYMENT_PROFILE_ID in
( select a.PAYMENT_PROFILE_ID from iby_pay_service_requests a where a.call_app_pay_service_req_code = ‘PAYMENT_PROCESS_REQUEST_NAME’);

select distinct * from iby_acct_pmt_profiles_tl where PAYMENT_PROFILE_ID in
( select a.PAYMENT_PROFILE_ID from iby_pay_service_requests a where a.call_app_pay_service_req_code = ‘PAYMENT_PROCESS_REQUEST_NAME’) ;

select distinct * from iby_sys_pmt_profiles_b where SYSTEM_PROFILE_CODE in
( select b.SYSTEM_PROFILE_CODE from iby_acct_pmt_profiles_b b where PAYMENT_PROFILE_ID in
( select a.PAYMENT_PROFILE_ID from iby_pay_service_requests a where a.call_app_pay_service_req_code = ‘PAYMENT_PROCESS_REQUEST_NAME’));

select distinct * from iby_sys_pmt_profiles_tl where SYSTEM_PROFILE_CODE in
( select b.SYSTEM_PROFILE_CODE from iby_acct_pmt_profiles_b b where PAYMENT_PROFILE_ID in
( select a.PAYMENT_PROFILE_ID from iby_pay_service_requests a where a.call_app_pay_service_req_code = ‘PAYMENT_PROCESS_REQUEST_NAME’));

select distinct * from iby_formats_b where FORMAT_CODE in
( select c.PAYMENT_FORMAT_CODE from iby_sys_pmt_profiles_b c where c.SYSTEM_PROFILE_CODE in
( select b.SYSTEM_PROFILE_CODE from iby_acct_pmt_profiles_b b where PAYMENT_PROFILE_ID in
( select a.PAYMENT_PROFILE_ID from iby_pay_service_requests a where a.call_app_pay_service_req_code = ‘PAYMENT_PROCESS_REQUEST_NAME’)));

select distinct * from xdo_templates_b where template_code in
( select d.FORMAT_TEMPLATE_CODE from iby_formats_b d where d.FORMAT_CODE in
( select c.PAYMENT_FORMAT_CODE from iby_sys_pmt_profiles_b c where c.SYSTEM_PROFILE_CODE in
( select b.SYSTEM_PROFILE_CODE from iby_acct_pmt_profiles_b b where PAYMENT_PROFILE_ID in
( select a.PAYMENT_PROFILE_ID from iby_pay_service_requests a where a.call_app_pay_service_req_code = ‘PAYMENT_PROCESS_REQUEST_NAME’))));

select distinct * from iby_applicable_pmt_profs where SYSTEM_PROFILE_CODE in
( select b.SYSTEM_PROFILE_CODE from iby_acct_pmt_profiles_b b where PAYMENT_PROFILE_ID in
( select a.PAYMENT_PROFILE_ID from iby_pay_service_requests a where a.call_app_pay_service_req_code = ‘PAYMENT_PROCESS_REQUEST_NAME’));

select distinct * from iby_instr_creation_rules where SYSTEM_PROFILE_CODE in
( select b.SYSTEM_PROFILE_CODE from iby_acct_pmt_profiles_b b where PAYMENT_PROFILE_ID in
( select a.PAYMENT_PROFILE_ID from iby_pay_service_requests a where a.call_app_pay_service_req_code = ‘PAYMENT_PROCESS_REQUEST_NAME’));

select distinct * from iby_pmt_creation_rules where SYSTEM_PROFILE_CODE in
( select b.SYSTEM_PROFILE_CODE from iby_acct_pmt_profiles_b b where PAYMENT_PROFILE_ID in
( select a.PAYMENT_PROFILE_ID from iby_pay_service_requests a where a.call_app_pay_service_req_code = ‘PAYMENT_PROCESS_REQUEST_NAME’));

select distinct * from iby_remit_advice_setup where SYSTEM_PROFILE_CODE in
( select b.SYSTEM_PROFILE_CODE from iby_acct_pmt_profiles_b b where PAYMENT_PROFILE_ID in
( select a.PAYMENT_PROFILE_ID from iby_pay_service_requests a where a.call_app_pay_service_req_code = ‘PAYMENT_PROCESS_REQUEST_NAME’));

select distinct * from ap_currency_group where checkrun_id in ( select a.checkrun_id from ap_inv_selection_criteria_all a where a.checkrun_name = ‘PAYMENT_PROCESS_REQUEST_NAME’);

select distinct * from ap_ou_group where checkrun_id in ( select a.checkrun_id from ap_inv_selection_criteria_all a where a.checkrun_name = ‘PAYMENT_PROCESS_REQUEST_NAME’);

select distinct * from ap_le_group where checkrun_id in ( select a.checkrun_id from ap_inv_selection_criteria_all a where a.checkrun_name = ‘PAYMENT_PROCESS_REQUEST_NAME’);

select distinct * from ap_pay_group where checkrun_id in ( select a.checkrun_id from ap_inv_selection_criteria_all a where a.checkrun_name = ‘PAYMENT_PROCESS_REQUEST_NAME’);

select * from iby_payment_profiles where payment_profile_id in
( select distinct(payment_profile_id) 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 = ‘PAYMENT_PROCESS_REQUEST_NAME’));

select distinct * from ce_bank_accounts where bank_account_id in
( select a.internal_bank_account_id from IBY_PAY_INSTRUCTIONS_ALL a where a.PAY_ADMIN_ASSIGNED_REF_CODE = ‘PAYMENT_PROCESS_REQUEST_NAME’);

select * from iby_ext_bank_accounts where ext_bank_account_id in
( select external_bank_account_id 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 = ‘PAYMENT_PROCESS_REQUEST_NAME’));

select * from iby_external_payees_all where payee_party_id in
( SELECT payee_party_id 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 = ‘PAYMENT_PROCESS_REQUEST_NAME’));

SELECT * FROM iby_pmt_instr_uses_all WHERE payment_flow = ‘DISBURSEMENTS’ AND ext_pmt_party_id IN
( select ext_payee_id from iby_external_payees_all where payee_party_id in
( SELECT payee_party_id 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 = ‘PAYMENT_PROCESS_REQUEST_NAME’)));

select distinct * from iby_process_conc_requests where object_type = ‘PAYMENT_REQUEST’ and object_id in
( select ipsr.payment_service_request_id from iby_pay_service_requests ipsr where ipsr.call_app_pay_service_req_code = ‘PAYMENT_PROCESS_REQUEST_NAME’) union select distinct * from iby_process_conc_requests where object_type = ‘PAYMENT_INSTRUCTION’ and object_id in
( select ipi.payment_instruction_id from iby_pay_instructions_all ipi where ipi.pay_admin_assigned_ref_code = ‘PAYMENT_PROCESS_REQUEST_NAME’);

select distinct * from fnd_concurrent_requests where request_id in
( select ais.request_id from ap_inv_selection_criteria_all ais where ais.checkrun_name = ‘PAYMENT_PROCESS_REQUEST_NAME’ union
select ipi.request_id from IBY_PAY_INSTRUCTIONS_ALL ipi where ipi.PAY_ADMIN_ASSIGNED_REF_CODE = ‘PAYMENT_PROCESS_REQUEST_NAME’);

References:

https://oracleappsstore.wordpress.com/2012/06/04/602/