Search Results iby_payments_all




Overview
Explore in AI Chat

Overview

The IBY_PAYMENTS_ALL table is a core transactional entity within the Oracle Payments (IBY) module of Oracle E-Business Suite (EBS) 12.1.1 and 12.2.2. It serves as the master repository for all payments generated by the system's 'Build Payments' process. Each record represents a distinct payment instrument, which can be either a physical check or an electronic funds transfer (EFT). The table captures the complete lifecycle of a payment, from its creation through to its final status, and establishes the critical financial relationship between a first-party payer (the paying entity) and a third-party payee. Its multi-org structure, indicated by the "_ALL" suffix, allows it to store payment data across multiple operating units, making it fundamental for consolidated payment processing and reporting.

Key Information Stored

The table's primary key is PAYMENT_ID, which uniquely identifies each payment. Essential foreign keys define the payment's context and attributes, linking to master data and controlling documents. Key columns include PAYMENT_INSTRUCTION_ID, which groups payments for batch processing; PAYMENT_SERVICE_REQUEST_ID, linking to the originating payment batch request; and PAYMENT_METHOD_CODE (e.g., CHECK, EFT). The table stores critical financial identifiers such as PAYMENT_AMOUNT, PAYMENT_CURRENCY_CODE, and PAYMENT_DATE. It also holds bank account details via EXTERNAL_BANK_ACCOUNT_ID (payee's account) and INTERNAL_BANK_ACCOUNT_ID (payer's account). Status tracking is managed through columns like PAYMENT_STATUS, PAYMENT_PROCESS_FLAG, and PAYMENT_CREATE_DATE, which document the payment's progression through the payment execution workflow.

Common Use Cases and Queries

This table is central for payment inquiry, reconciliation, and audit reporting. Common scenarios include tracing payment history for a specific invoice, analyzing payment batch details, and investigating payment failures. A typical query joins to related documents payable to retrieve payment details for a transaction.

  • Payment Details for an Invoice: SELECT p.payment_id, p.payment_amount, p.payment_date, dp.payment_status FROM iby_payments_all p, iby_docs_payable_all dp WHERE p.payment_id = dp.payment_id AND dp.calling_app_doc_unique_ref = '<Invoice_Num>';
  • Payments within an Instruction: SELECT payment_id, payment_amount, payee_name FROM iby_payments_all WHERE payment_instruction_id = <ID> ORDER BY payment_id;
  • Payment Status Summary: SELECT payment_status, COUNT(*), SUM(payment_amount) FROM iby_payments_all WHERE payment_date > SYSDATE - 30 GROUP BY payment_status;

Related Objects

IBY_PAYMENTS_ALL has extensive relationships with other core Payments and Cash Management tables. It is a parent table to payment documents, as shown by foreign keys from IBY_DOCS_PAYABLE_ALL (linking payments to invoices) and AP_HISTORY_CHECKS_ALL. It is a child table to numerous setup and control entities, including IBY_PAY_INSTRUCTIONS_ALL (batch header), IBY_PAY_SERVICE_REQUESTS (batch request), IBY_PAYMENT_METHODS_B, IBY_ACCT_PMT_PROFILES_B, and CE_BANK_ACCOUNTS (for both internal and external accounts). These relationships form the backbone of the payment execution data model, ensuring referential integrity from the payment instruction down to the individual paid invoice.