Search Results ap_invoice_payments_u1

Overview

AP.AP_INVOICE_PAYMENTS_ALL is a core transactional table in the Oracle Payables (AP) module of Oracle E-Business Suite. It stores the association between invoices and the payments made against them. Each row represents a single payment applied to a single invoice, establishing a one-to-one relationship at the row level between the invoice and the payment recorded for it. The table is updated by Oracle Payables when an automatic payment batch is confirmed, when a manual payment is entered, or when a Quick payment is processed. When a payment is voided, Oracle Payables inserts an additional row representing the negative of the original payment line, preserving the audit trail rather than deleting the original entry.

In Data Vault modeling terms, the heuristic classification of this table is satellite-leaning. This suggests it is best modeled as a satellite attached to a hub or link representing the payment-invoice association, capturing descriptive and state attributes (amounts, accounting flags, exchange information) that change over time, rather than as a pure hub or link.

Key Information Stored

The table contains 95 documented columns. The most significant columns for functional and reporting purposes include:

• INVOICE_PAYMENT_ID — The surrogate primary key (AP_INVOICE_PAYMENTS_PK). This is the sole documented unique index (AP_INVOICE_PAYMENTS_U1) and the primary business-key candidate referenced by downstream objects.
• INVOICE_ID — Foreign key to AP_INVOICES_ALL, identifying the invoice being paid.
• CHECK_ID — Foreign key to AP_CHECKS_ALL, identifying the payment instrument.
• AMOUNT — The payment amount applied to the invoice.
• ACCOUNTING_DATE and PERIOD_NAME — The accounting date and GL period for the payment.
• ACCOUNTING_EVENT_ID — Links the row to its accounting event in AP_ACCOUNTING_EVENTS_ALL.
• POSTED_FLAG, ACCRUAL_POSTED_FLAG, CASH_POSTED_FLAG — Accounting status indicators (Y/N) for general, accrual-basis, and cash-basis accounting respectively.
• SET_OF_BOOKS_ID — Foreign key to GL_SETS_OF_BOOKS_11I, identifying the ledger.
• EXCHANGE_RATE, EXCHANGE_DATE, EXCHANGE_RATE_TYPE — Currency conversion details.
• ORG_ID — Multi-org operating unit identifier.
• PAYMENT_NUM — The payment number associated with the payment.
• INVOICE_PAYMENT_TYPE and REVERSAL_FLAG — Payment type classification and void/reversal identification.
• INVOICING_PARTY_ID, INVOICING_PARTY_SITE_ID, INVOICING_VENDOR_SITE_ID — Supplier/party identifiers linking to HZ and supplier site tables.

Additional accounting columns such as DISCOUNT_TAKEN, DISCOUNT_LOST, GAIN_CODE_COMBINATION_ID, and LOSS_CODE_COMBINATION_ID support foreign exchange and discount accounting.

Common Use Cases and Queries

This table is central to payables reporting, reconciliation, and accounting analysis. Common scenarios include identifying all payments made against a given invoice, reconciling payment amounts to check records, and analyzing unaccounted payments.

• Payments for a specific invoice: Join to AP_INVOICES_ALL on INVOICE_ID and to AP_CHECKS_ALL on CHECK_ID to retrieve payment and check details.
• Unaccounted payments: Query rows where POSTED_FLAG = 'N' or ACCRUAL_POSTED_FLAG = 'N' to identify accounting gaps, joining to AP_ACCOUNTING_EVENTS_ALL via ACCOUNTING_EVENT_ID.
• Foreign exchange analysis: Aggregate AMOUNT against INVOICE_BASE_AMOUNT and PAYMENT_BASE_AMOUNT using EXCHANGE_RATE.
• Void reconciliation: Identify original and reversal rows using REVERSAL_FLAG and REVERSAL_INV_PMT_ID.
• Supplier-level payment reporting: Join on INVOICING_PARTY_ID to HZ_PARTIES and on ORG_ID for operating unit segmentation.

A representative query pattern filters by accounting_date range and POSTED_FLAG, joining to AP_CHECKS_ALL for check numbers and to AP_INVOICES_ALL for invoice numbers.

Related Objects

The table has extensive referential relationships. Significant parents (foreign keys from AP_INVOICE_PAYMENTS_ALL) include:

• AP_INVOICES_ALL — via INVOICE_ID.
• AP_CHECKS_ALL — via CHECK_ID.
• AP_SUPPLIER_SITES_ALL — via INVOICING_VENDOR_SITE_ID.
• AP_ACCOUNTING_EVENTS_ALL — via ACCOUNTING_EVENT_ID.
• GL_SETS_OF_BOOKS_11I — via SET_OF_BOOKS_ID; GL_DAILY_CONVERSION_TYPES — via EXCHANGE_RATE_TYPE.
• HZ_PARTIES and HZ_PARTY_SITES — via INVOICING_PARTY_ID and INVOICING_PARTY_SITE_ID.

Significant child tables that reference this object via INVOICE_PAYMENT_ID include AP_PAYMENT_DISTRIBUTIONS_ALL, AP_PAYMENT_HIST_DISTS, AP_INVOICE_DISTRIBUTIONS_ALL (via AWT_INVOICE_PAYMENT_ID), AP_AWT_TEMP_DISTRIBUTIONS_ALL, and AP_SELECTED_INVOICES_ALL, along with specialized objects such as FV_TREASURY_CONFIRMATIONS_ALL and JAI_AP_TDS_INV_PAYMENTS.