Search Results ap_invoice_prepays_pk

Overview

AP_INVOICE_PREPAYS_ALL is the Oracle Payables (AP) schema table that stores the association between prepayment invoices and the standard invoices against which those prepayments are applied. In Oracle E-Business Suite 12.1.1 and 12.2.2 it functions as the application ledger that records how much of a given prepayment has been consumed by a specific invoice, allowing Payables to track remaining prepayment balances and to release the funds correctly during payment processing. Because prepayments are themselves invoices of type "Prepayment," the table effectively links two rows of AP_INVOICES_ALL: the prepayment invoice and the applied-to invoice.

The implementation metadata documents a composite primary key, AP_INVOICE_PREPAYS_PK, defined over (PREPAY_ID, INVOICE_ID), with an identical unique index AP_INVOICE_PREPAYS_U1. Both columns are foreign keys to AP_INVOICES_ALL. Under the heuristic Data Vault classification supplied in the metadata, this object is best modeled as a link table, since it resolves a many-to-many relationship between prepayment records and applied invoices and carries a small amount of descriptive payload (the amount applied and audit columns) rather than serving as a standalone hub or satellite.

Key Information Stored

The documented physical schema of AP_INVOICE_PREPAYS_ALL contains ten columns. The most significant are:

• PREPAY_ID — Identifies the prepayment invoice. Part of the composite primary key and a foreign key to AP_INVOICES_ALL.
• INVOICE_ID — Identifies the invoice to which the prepayment is applied. Also part of the composite primary key and a foreign key to AP_INVOICES_ALL.
• PREPAYMENT_AMOUNT_APPLIED — The monetary amount of the prepayment consumed by the referenced invoice; this is the principal business measure of the row.
• ORG_ID — The operating unit that owns the record, used for multi-org security and reporting.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN — Who last modified the row and when, supporting audit and concurrency.
• CREATION_DATE, CREATED_BY — Insertion audit columns recording the creation of the link row.
• DATE_REPORTED — A date attribute associated with the prepayment application, typically used for reporting and period-sensitive analysis.

The surrogate primary key is the composite (PREPAY_ID, INVOICE_ID), which is also the documented business-key candidate via AP_INVOICE_PREPAYS_U1. There is no separate single-column surrogate identifier; the pair of invoice references constitutes the natural key.

Common Use Cases and Queries

Typical reporting scenarios include reconciling applied prepayments, determining the unapplied or remaining balance of a prepayment, and tracing which invoices have consumed a given prepayment. The following pattern joins the link back to AP_INVOICES_ALL twice to retrieve both sides of the relationship:

SELECT p.prepay_id,
       inv.invoice_num     applied_to_invoice,
       pre.invoice_num     prepayment_num,
       p.prepayment_amount_applied,
       p.org_id
FROM   ap.ap_invoice_prepays_all p,
       ap.ap_invoices_all inv,
       ap.ap_invoices_all pre
WHERE  p.invoice_id = inv.invoice_id
AND    p.prepay_id  = pre.invoice_id
AND    p.org_id     = :org_id;

Aggregating PREPAYMENT_AMOUNT_APPLIED by PREPAY_ID yields the total amount consumed per prepayment, which can be compared against the prepayment invoice total to derive the remaining balance. Filtering on ORG_ID supports operating-unit-restricted reporting, while DATE_REPORTED supports period-based analysis of prepayment activity. These queries are commonly embedded in reconciliation reports and in custom extensions that monitor unapplied prepayments.

Related Objects

The most significant related objects are those referenced by the documented foreign keys:

• AP_INVOICES_ALL — Referenced twice: AP_INVOICE_PREPAYS_ALL.PREPAY_ID → AP_INVOICES_ALL and AP_INVOICE_PREPAYS_ALL.INVOICE_ID → AP_INVOICES_ALL. This is the central invoice table that supplies both the prepayment and the applied-to invoice.
• AP_INVOICES_HEADER_ALL — The header-level companion to invoice data, frequently joined for invoice type and source reporting.
• AP_INVOICE_DISTRIBUTIONS_ALL — Holds the accounting distributions generated when prepayments are applied, related through the invoice identifiers.
• AP_PAYMENT_SCHEDULES_ALL — Stores payment schedule lines for both prepayment and applied invoices, useful for payment timing analysis.
• AP_CHECKS_ALL — Links payment documents back to the invoices, supporting end-to-end payment reconciliation.

These objects collectively support the complete Payables lifecycle for prepayments—creation, application, distribution, and payment—making AP_INVOICE_PREPAYS_ALL the pivotal link between a prepayment invoice and the invoices it funds.