Results for “ap_unapply_prepays_fr_prepay_v”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

The APPS.AP_UNAPPLY_PREPAYS_FR_PREPAY_V view is a Payables module object that exposes prepayment application data prepared in the exact shape required by the Invoice Workbench's Unapply Prepayments form (the "FR_PREPAY" form region). Its purpose is to present, for a given prepayment invoice, the set of prepayment application lines that currently consume a portion of that prepayment's available balance and that may therefore be candidates for unapplication. The view is the data source the form queries at runtime rather than a general-purpose reporting object, but it is frequently used for reporting and integration because it joins prepayment application lines to their originating invoice, vendor, purchase order, and receiving context.

The name is significant: UNAPPLY_PREPAYS identifies the functional flow, FR_PREPAY identifies the Oracle Forms block that invokes the query. The final column of the SELECT projects AIL.INVOICE_ID aliased as PREPAY_ID, so callers that filter by prepayment use PREPAY_ID, while the PREPAY_INVOICE_ID column (the key the user searched for) carries the identifier of the prepayment invoice being applied against each application line.

Underlying Base Objects

Per the ETRM 12.2.2 documented metadata, the view resolves over the following objects in the APPS schema: AP_INVOICES_ALL, AP_INVOICE_LINES, PO_HEADERS_ALL, RCV_TRANSACTIONS, RCV_SHIPMENT_HEADERS, RCV_SHIPMENT_LINES, PO_VENDORS, PO_VENDOR_SITES_ALL, and the FND_GLOBAL package. AP_INVOICES_ALL and AP_INVOICE_LINES form the core of the join, providing the prepayment application line rows and their invoice headers. PO_HEADERS_ALL is outer-joined via PO_HEADER_ID to supply the purchase order number, and RCV_TRANSACTIONS, RCV_SHIPMENT_LINES, and RCV_SHIPMENT_HEADERS are chained by outer joins to supply receipt context. PO_VENDORS and PO_VENDOR_SITES_ALL provide supplier name, supplier number, and supplier site code.

The driving predicate is restrictive: AIL.AMOUNT < 0 selects negative-amount lines (application lines reduce the prepayment's outstanding balance), AIL.LINE_TYPE_LOOKUP_CODE = 'PREPAY' restricts rows to prepayment lines, and NVL(AIL.DISCARDED_FLAG,'N') <> 'Y' excludes discarded lines. The invoice type filters AI.INVOICE_TYPE_LOOKUP_CODE NOT IN ('PREPAYMENT','CREDIT','DEBIT') so that credit, debit, and prepayment-type documents are not surfaced as applications.

Key Columns

• ROW_ID — the ROWID of the AP_INVOICE_LINES row; used by the form to identify the line for unapplication.
• INVOICE_ID / INVOICE_LINE_NUMBER — identifier and line number of the invoice that is being charged (the consuming invoice).
• PREPAY_INVOICE_ID / PREPAY_LINE_NUMBER — identifier and line number of the prepayment being applied; this is the prepay_invoice_id column searched for.
• PREPAY_AMOUNT_APPLIED — derived as (-1)*(AMOUNT - NVL(INCLUDED_TAX_AMOUNT,0)), yielding a positive amount applied against the prepayment, tax-exclusive.
• TAX_AMOUNT_APPLIED — derived as NULLIF((-1)*(TOTAL_REC_TAX_AMOUNT + TOTAL_NREC_TAX_AMOUNT),0), suppressed to NULL when zero.
• ACCOUNTING_DATE, PERIOD_NAME, SET_OF_BOOKS_ID, ORG_ID — accounting and multi-org context for the application line.
• PO_NUMBER, RECEIPT_NUMBER, PO_LINE_LOCATION_ID, PO_DISTRIBUTION_ID, RCV_TRANSACTION_ID — sourcing and receipt context when the application is tied to a purchase order or receipt; all nullable through the outer joins.
• VENDOR_ID, VENDOR_NAME, VENDOR_NUMBER, VENDOR_SITE_ID, VENDOR_SITE_CODE — supplier details resolved from the invoice header.
• PREPAY_ID — alias of INVOICE_ID projected last, so a form or query can key the outer block on the prepayment invoice.

Common Use Cases and Queries

The primary use case is identifying which invoices have consumed a prepayment so the application can be reversed. A typical query resolves all unreversed applications against a specific prepayment:

• SELECT invoice_num, prepay_invoice_id, prepay_line_number, prepay_amount_applied, tax_amount_applied FROM ap.ap_unapply_prepays_fr_prepay_v WHERE prepay_id = :p_prepay_invoice_id;

A reconciliation query lists total applied amounts per prepayment for a supplier:

• SELECT prepay_id, SUM(prepay_amount_applied) applied_total FROM ap.ap_unapply_prepays_fr_prepay_v WHERE vendor_id = :p_vendor_id GROUP BY prepay_id;

A sourcing query returns the PO and receipt context for applied prepayments:

• SELECT invoice_num, prepay_invoice_id, po_number, receipt_number, prepay_amount_applied FROM ap.ap_unapply_prepays_fr_prepay_v WHERE po_number IS NOT NULL AND accounting_date BETWEEN :p_start AND :p_end ORDER BY accounting_date;

Because the view is a form-support object, its joining conditions reflect 12.1.x and 12.2.x behavior; in 12.2 it continues to be defined in the APPS schema against the same base objects. Queries should therefore always be qualified with APPS and joined to the standard AP tables on INVOICE_ID or PREPAY_INVOICE_ID, and date-bound using ACCOUNTING_DATE or PERIOD_NAME to keep cost reasonable.