Search Results invoice_price_variance

Overview

AP_INVOICE_PRICE_VAR_V is a Payables (AP) reporting view owned by the APPS schema in Oracle E-Business Suite 12.1.1 and 12.2.2. It exposes invoice price variance (IPV) and exchange rate variance (ERV) information at the invoice distribution level by self-joining AP_INVOICE_DISTRIBUTIONS to its related distribution lines. The view presents a consolidated result set that combines standard item and accrual distributions with their associated IPV and ERV allocations, and it also includes price correction invoices through a UNION ALL branch. This makes it a convenient single-source object for reporting and integration scenarios where variance data must be presented alongside the originating invoice and distribution attributes, without the analyst needing to write the underlying self-join logic.

Underlying Base Objects

The documented base objects referenced by the view are AP_INVOICES (SYNONYM) and AP_INVOICE_DISTRIBUTIONS (SYNONYM). The view text shows three aliases of AP_INVOICE_DISTRIBUTIONS in the first branch: AID (the primary item/accrual distribution), AIDIPV (the related IPV distribution, joined via RELATED_ID and LINE_TYPE_LOOKUP_CODE = 'IPV'), and AIDERV (the related ERV distribution, joined via RELATED_ID and LINE_TYPE_LOOKUP_CODE = 'ERV'). The joins to AIDIPV and AIDERV are outer joins (denoted by the (+) operator) and are further restricted to posted distributions (POSTED_FLAG = 'Y'). A second UNION ALL branch repeats the same structure to capture price correction invoices. The connection to AP_INVOICES is through INVOICE_ID.

Key Columns

• INVOICE_NUM, INVOICE_DATE, INVOICE_RATE, INVOICE_CURRENCY — Invoice header attributes sourced from AP_INVOICES.
• QUANTITY_INVOICED, INVOICE_AMOUNT, INVOICE_BASE_AMOUNT, INVOICE_PRICE — Distribution-level values: the invoiced quantity, distribution amount, converted base amount, and unit price.
• INVOICE_PRICE_VARIANCE, BASE_INV_PRICE_VAR — The IPV amount and its base-currency equivalent, pulled from the related IPV distribution (AIDIPV), defaulted to zero via NVL.
• EX_RATE_VARI — The exchange rate variance base amount sourced from the related ERV distribution (AIDERV), defaulted to zero.
• PO_DISTRIBUTION_ID, RCV_TRANSACTION_ID, ACCOUNTING_DATE — Cross-references to purchasing and receiving, plus the accounting date used for subledger accounting.

Common Use Cases and Queries

Typical uses include reconciling invoice price variances against receipts and purchase orders, analyzing exchange rate variance exposure on foreign-currency invoices, and feeding IPV/ERV data into custom reports or downstream integrations. The following query returns IPV detail for a specific invoice:

• SELECT invoice_num, invoice_date, quantity_invoiced, invoice_price, invoice_currency, invoice_price_variance, base_inv_price_var FROM apps.ap_invoice_price_var_v WHERE invoice_num = :p_invoice_num ORDER BY invoice_date;

For variance analysis by currency over a period, filter on INVOICE_DATE and aggregate the variance columns:

• SELECT invoice_currency, SUM(invoice_price_variance) total_ipv, SUM(ex_rate_vari) total_erv FROM apps.ap_invoice_price_var_v WHERE invoice_date BETWEEN :start_date AND :end_date GROUP BY invoice_currency;

Because the view defaults missing IPV and ERV amounts to zero and restricts the related distributions to posted lines, results reflect only accounted variance activity. Note that the view exposes no invoice_id or distribution_id columns, so joins back to underlying tables must be made on invoice_num, po_distribution_id, or rcv_transaction_id.