Search Results ap_invoice_payments_pk

Overview

AP_INVOICE_PAYMENTS_ALL is the core Payables table in Oracle E-Business Suite (12.1.1 and 12.2.2) that stores the intersection between invoices and the payments that settle them. Each row records the application of a specific payment (identified by CHECK_ID) to a specific invoice (identified by INVOICE_ID), along with the accounting, currency, discount, and gain/loss information associated with that settlement. It is owned by the AP schema and represents a fundamental accounting-level artifact: the point at which an invoice liability is cleared and a cash or clearing entry is created.

Because the table records the resolution of a relationship between two master entities (invoices and payments), its structure is largely an associative or transactional construct. The heuristic Data Vault classification provided in the metadata is satellite-leaning, which suggests that the table is best modeled as a satellite attached to a hub or link (the invoice-to-payment relationship) rather than as a standalone hub. In practice, practitioners typically treat AP_INVOICE_PAYMENTS_ALL as a transaction fact or associative detail table keyed by INVOICE_PAYMENT_ID.

Key Information Stored

The primary key is AP_INVOICE_PAYMENTS_PK, defined on the surrogate column INVOICE_PAYMENT_ID. The unique index AP_INVOICE_PAYMENTS_U1 also covers INVOICE_PAYMENT_ID, confirming it as the single documented uniqueness constraint. The most operationally significant columns include:

• INVOICE_PAYMENT_ID — surrogate primary key uniquely identifying each payment application.
• INVOICE_ID — foreign key to AP_INVOICES_ALL (and AP_PAYMENT_SCHEDULES_ALL), linking the settlement to its invoice.
• CHECK_ID — foreign key to AP_CHECKS_ALL, identifying the disbursement (check or electronic payment) applied.
• SET_OF_BOOKS_ID — foreign key to GL_SETS_OF_BOOKS_11I, identifying the ledger under which the accounting belongs.
• ACCOUNTING_EVENT_ID — foreign key to AP_ACCOUNTING_EVENTS_ALL, tying the row to its subledger accounting event.
• AMOUNT — the amount of the invoice settled by this payment.
• PAYMENT_NUM — the payment number associated with the application.
• ACCOUNTING_DATE and PERIOD_NAME — the accounting date and GL period in which the settlement was recognized.
• EXCHANGE_RATE, EXCHANGE_RATE_TYPE, EXCHANGE_DATE — currency conversion details (EXCHANGE_RATE_TYPE is a foreign key to GL_DAILY_CONVERSION_TYPES).
• DISCOUNT_TAKEN and DISCOUNT_LOST — captured or forgone early-payment discounts.
• GAIN_CODE_COMBINATION_ID and LOSS_CODE_COMBINATION_ID — accounting for exchange rate gains and losses.
• INVOICE_BASE_AMOUNT and PAYMENT_BASE_AMOUNT — ledger-currency equivalents of the invoice and payment amounts.
• POSTED_FLAG, ACCRUAL_POSTED_FLAG, CASH_POSTED_FLAG — status indicators for accounting posting.
• INVOICING_PARTY_ID / INVOICING_PARTY_SITE_ID / INVOICING_VENDOR_SITE_ID — supplier party and site references (to HZ_PARTIES, HZ_PARTY_SITES, and AP_SUPPLIER_SITES_ALL respectively).

The table contains 95 documented columns in 12.2.2, including 15 standard ATTRIBUTE columns and 20 GLOBAL_ATTRIBUTE columns commonly used for localization and extensibility, plus MRC_ (Multiple Reporting Currency) columns.

Common Use Cases and Queries

The table is central to reporting on invoice settlement, payment reconciliation, and subledger-to-GL drill-downs. A typical query joining payments to invoices and checks reconciles amounts paid against invoice totals:

• Payment-to-invoice reconciliation — joining INVOICE_ID to AP_INVOICES_ALL and CHECK_ID to AP_CHECKS_ALL to reconcile supplier balances.
• Discount analysis — aggregating DISCOUNT_TAKEN and DISCOUNT_LOST by supplier or period to evaluate early-payment behavior.
• Exchange gain/loss reporting — summarizing GAIN_CODE_COMBINATION_ID and LOSS_CODE_COMBINATION_ID entries for foreign-currency invoices.
• Posting verification — filtering on POSTED_FLAG, CASH_POSTED_FLAG, or ACCRUAL_POSTED_FLAG to identify unposted applications before period close.
• Audit trails — tracing from an invoice payment back through ACCOUNTING_EVENT_ID to the underlying subledger accounting entries.

These scenarios commonly underpin standard reports such as the Invoice Payments Register, Payables Trial Balance, and cash-basis reporting extracts.

Related Objects

AP_INVOICE_PAYMENTS_ALL sits at the center of a dense foreign-key network. The most significant related objects include:

• AP_INVOICES_ALL — joined on INVOICE_ID; the parent invoice record.
• AP_CHECKS_ALL — joined on CHECK_ID; the disbursement record.
• AP_PAYMENT_SCHEDULES_ALL — joined on INVOICE_ID and PAYMENT_NUM; drives scheduled payment amounts.
• AP_PAYMENT_DISTRIBUTIONS_ALL — references INVOICE_PAYMENT_ID; stores distribution-level accounting for each payment.
• AP_PAYMENT_HIST_DISTS — references INVOICE_PAYMENT_ID; stores historical payment distribution detail.
• AP_INVOICE_DISTRIBUTIONS_ALL — references INVOICE_PAYMENT_ID via AWT_INVOICE_PAYMENT_ID; links withholding tax applications.
• AP_ACCOUNTING_EVENTS_ALL — joined on ACCOUNTING_EVENT_ID; connects the settlement to subledger accounting.
• AP_AWT_TEMP_DISTRIBUTIONS_ALL — references INVOICE_PAYMENT_ID for withholding tax processing.
• FV_TREASURY_CONFIRMATIONS_ALL and FV_BD_CONTROLS — Treasury module dependencies on INVOICE_PAYMENT_ID.
• AP_SELECTED_INVOICES_ALL — references INVOICE_PAYMENT_ID during payment batch selection.

These relationships confirm that AP_INVOICE_PAYMENTS_ALL is a high-fan-in, dependency-rich table whose integrity is critical to Payables and Treasury processing and to downstream General Ledger reconciliation.