Search Results iby_pay_invoicepmts_pk




Overview
Explore in AI Chat

Overview

The IBY_PAY_INVOICEPMTS table is a core data object within the Oracle Payments (IBY) module of Oracle E-Business Suite (EBS) releases 12.1.1 and 12.2.2. It functions as a junction table that establishes a critical link between a payment transaction created in the Payments module and the specific invoice(s) from the Payables module that the payment is intended to settle. Its primary role is to store a reference to the invoice in the calling payable programs, ensuring a clear and auditable trail from the payment instruction back to the original liability. This relationship is fundamental for payment reconciliation, reporting, and maintaining data integrity across the Procure-to-Pay cycle.

Key Information Stored

The table's structure is designed to capture the essential relationship between a payment and an invoice. Its composite primary key uniquely identifies each payment-invoice pairing. The key columns are:

  • PMT_TRXN_ID: A foreign key column that references the IBY_PAY_PAYMENTS_ALL table. This column stores the unique identifier for the payment transaction record.
  • INVOICE_NUMBER: Stores the identifier of the invoice being paid. This is the invoice number as referenced in the Accounts Payable (AP) module.

Together, these columns form the primary key (IBY_PAY_INVOICEPMTS_PK), meaning that for a single payment transaction (PMT_TRXN_ID), there can be multiple associated invoice records, each with a distinct INVOICE_NUMBER. This supports the common business practice of creating a single payment for multiple invoices.

Common Use Cases and Queries

This table is central to any reporting or data extraction that requires linking payment details to the underlying invoices. A typical use case is generating a payment register that lists all payments along with the invoice numbers paid. Support and implementation consultants often query this table to troubleshoot payment reconciliation issues or validate that payments were created for the correct invoices. A fundamental query pattern involves joining IBY_PAY_INVOICEPMTS to the main payments table to retrieve this linked data.

Sample Query: To retrieve a list of payments with their associated invoice numbers, a common SQL pattern is:
SELECT ipmt.pmt_trxn_id, ipmt.invoice_number, ipa.payment_amount, ipa.payment_date
FROM iby_pay_invoicepmts ipmt
JOIN iby_pay_payments_all ipa ON ipa.pmt_trxn_id = ipmt.pmt_trxn_id
WHERE ipa.payment_date > SYSDATE - 30;

Related Objects

The IBY_PAY_INVOICEPMTS table has a direct and documented foreign key relationship with a core Payments table, as per the provided metadata:

  • IBY_PAY_PAYMENTS_ALL: This is the primary table referenced by IBY_PAY_INVOICEPMTS. The relationship is defined by the column IBY_PAY_INVOICEPMTS.PMT_TRXN_ID, which joins to the corresponding column in IBY_PAY_PAYMENTS_ALL. This foreign key ensures that every invoice payment record is associated with a valid, existing payment transaction.

While not explicitly listed in the provided excerpt, this table is also intrinsically related to Accounts Payables invoice tables (such as AP_INVOICES_ALL), typically linked via the INVOICE_NUMBER column, to obtain full invoice details like amount, supplier, and accounting date.