Search Results ap_invoice_prepays_n1

Overview

AP.AP_INVOICE_PREPAYS_ALL is a transactional table in the Oracle E-Business Suite Payables (AP) module that records the application of a prepayment to a standard invoice. Each row represents one application event, capturing how much of a prepayment invoice has been applied against a target invoice. The table resides in the APPS_TS_TX_DATA tablespace with PCT Free 10 and is designated Oracle Internal Use Only; Oracle Corporation supports access only through standard Oracle Applications programs rather than direct DML.

Both PREPAY_ID and INVOICE_ID are foreign keys to AP_INVOICES_ALL, meaning the table stores the relationship between two invoice records — the prepayment and the invoice it offsets. From a Data Vault modeling perspective, this object is best classified as a link table: it resolves a many-to-many style relationship between invoice entities and carries a small number of descriptive attributes, notably PREPAYMENT_AMOUNT_APPLIED and DATE_REPORTED. The operational status is VALID, and the FND Design Data reference is SQLAP.AP_INVOICE_PREPAYS_ALL.

Key Information Stored

The physical schema documents 10 columns. The most significant are:

• PREPAY_ID — prepayment identifier; foreign key to AP_INVOICES_ALL and part of the composite primary key AP_INVOICE_PREPAYS_PK.
• INVOICE_ID — invoice identifier; foreign key to AP_INVOICES_ALL and the second component of the composite primary key.
• PREPAYMENT_AMOUNT_APPLIED — the monetary amount of the prepayment applied against the target invoice.
• DATE_REPORTED — the reported date associated with the application.
• ORG_ID — organization identifier, supporting multi-org (Operating Unit) partitioning.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATION_DATE, CREATED_BY — standard WHO audit columns tracking creation and modification history.

The surrogate composite primary key (PREPAY_ID, INVOICE_ID) is enforced by the unique index AP_INVOICE_PREPAYS_U1 on those same columns in tablespace APPS_TS_TX_IDX. This index is the business-key candidate the user searched for ("ap_invoice_prepays_u1"). A second index, AP_INVOICE_PREPAYS_N1, is a non-unique index on INVOICE_ID and supports lookup from the invoice side of the relationship.

Common Use Cases and Queries

Typical reporting needs include identifying which prepayments remained unapplied, reconciling prepayment balances, and analytics of prepayment application by organization and date. Standard SQL selection follows the documented query pattern:

SELECT p.PREPAY_ID, p.INVOICE_ID, p.PREPAYMENT_AMOUNT_APPLIED,
       p.DATE_REPORTED, p.ORG_ID
FROM   AP.AP_INVOICE_PREPAYS_ALL p
WHERE  p.PREPAY_ID = :prepay_id;

To trace all invoices against which a given prepayment was applied, join the table to AP_INVOICES_ALL on both key columns. Reporting use cases frequently aggregate PREPAYMENT_AMOUNT_APPLIED by ORG_ID or by DATE_REPORTED to produce period-close summaries, and analytics teams use the table to reconstruct verification relationships for audit. Because Oracle restricts direct access, these queries are appropriate for read-only reporting and analysis rather than transactional modification, which should be performed through supported Payables APIs.

Related Objects

• AP.AP_INVOICES_ALL — referenced twice by the two foreign keys: PREPAY_ID and INVOICE_ID both join to AP_INVOICES_ALL.INVOICE_ID (the invoice entity table holding both prepayment and standard invoices).
• AP.AP_INVOICE_PREPAYS_ALL# — the internal editioning/upgrade companion object documented as depending on this table.
• AP.AP_INVOICE_PREPAYS_U1 — the unique composite index (PREPAY_ID, INVOICE_ID) enforcing the primary key.
• AP.AP_INVOICE_PREPAYS_N1 — the non-unique INVOICE_ID index enabling invoice-side lookups.
• FND_USER — referenced logically by LAST_UPDATED_BY and CREATED_BY WHO columns.
• FND_LOGINS — referenced logically by LAST_UPDATE_LOGIN.

No foreign keys emanate from other AP tables into this object per the documented dependencies; AP_INVOICE_PREPAYS_ALL itself does not reference any database object other than through its column-level foreign keys to AP_INVOICES_ALL.