IBY Payment Process Request Details – Oracle EBS SQL Report

Oracle E-Business Suite SQL report from the Enginatics Library powered by Blitz Report™.

Overview

Payment manager process request details including statuses, paid or rejected documents, amounts and rejection eror messages

Report Parameters

Operating Unit, Payment Process Request, Payment Process Request Status, Payment Status, Request Creation Date From, Request Creation Date To, Request Created By, Internal Bank Account, Trading Partner, Rejected or Failed Only, Exclude Removed Documents, Expand Error Details, Exchange Rate Type, Show Invoice Distributions

Oracle EBS Tables Used

iby_payments_all, gl_daily_conversion_types, ce_bank_accounts, iby_validation_sets_vl, iby_pay_service_requests, ap_inv_selection_criteria_all, iby_docs_payable_all, ap_invoices_all, ap_invoice_distributions_all, ap_checks_all, hz_parties, hz_party_sites, fnd_application_vl, iby_ext_bank_accounts_v, iby_ext_bank_accounts, iby_payment_reasons_vl, iby_payment_methods_vl, iby_acct_pmt_profiles_vl, iby_formats_vl, iby_delivery_channels_vl, iby_trxn_types_vl, iby_pay_instructions_all, hr_operating_units, gl_ledgers, xle_entity_profiles, iby_transaction_errors, mo_glob_org_access_tmp, dual

Report Categories

Enginatics, R12 only

Running This SQL Without Blitz Report

Some Oracle EBS SQL reports in this library require functions from the utility package xxen_util. Install it before running the SQL directly against your Oracle EBS database.

Download & Import Options

Resource	Link
Excel Example Output	IBY Payment Process Request Details 25-Jan-2021 024851.xlsx
Blitz Report™ XML Import	IBY_Payment_Process_Request_Details.xml
Full SQL on Enginatics	www.enginatics.com/reports/iby-payment-process-request-details/

IBY Payment Process Request Details - Case Study & Technical Analysis

Executive Summary

The IBY Payment Process Request Details report provides a deep dive into the “Payment Process Request” (PPR) in Oracle Payments (IBY). A PPR is the central object that groups invoices for payment. This report details the lifecycle of a payment batch: which invoices were selected, which were rejected (and why), the status of the payment file generation, and the final payment amounts. It is a critical tool for the Accounts Payable department to manage mass payment runs.

Business Use Cases

Payment Run Validation: Before confirming a payment batch, the AP Manager reviews this report to ensure the correct invoices are included and the total cash outflow matches expectations.
Rejection Analysis: Identifies invoices that were pulled into the batch but rejected due to validation errors (e.g., “Missing Bank Account”, “Vendor on Hold”), allowing users to fix the issues and re-run.
Audit Trail: Documents exactly which invoices were paid in a specific batch, providing a link between the bank withdrawal and the AP liability.
Vendor Communication: Helps answer vendor queries like “Was my invoice included in the last payment run?”

Technical Analysis

Core Tables

IBY_PAY_SERVICE_REQUESTS: The header table for the Payment Process Request.
IBY_DOCS_PAYABLE_ALL: The link between the PPR and the underlying AP Invoices (AP_INVOICES_ALL).
IBY_PAYMENTS_ALL: The resulting payment records (Checks, Wires).
IBY_TRANSACTION_ERRORS: Stores validation error messages.
AP_INV_SELECTION_CRITERIA_ALL: Stores the criteria used to select invoices for the PPR.

Key Joins & Logic

Selection to Payment: The report traces the flow from AP_INV_SELECTION_CRITERIA_ALL -> IBY_PAY_SERVICE_REQUESTS -> IBY_DOCS_PAYABLE_ALL -> IBY_PAYMENTS_ALL.
Status Logic: It decodes the complex status columns of the PPR (e.g., “INSERTED”, “SUBMITTED”, “TERMINATED”) and the individual documents.
Error Handling: Joins to IBY_TRANSACTION_ERRORS to display specific reasons why a document or payment failed validation.

Key Parameters

Payment Process Request: The specific batch name.
Payment Process Request Status: Filter by status (e.g., “Completed”, “Terminated”).
Rejected or Failed Only: A useful flag to focus solely on exceptions.

IBY Payment Process Request Details | Oracle EBS SQL Report