Results for “ar_invoice_lines_v”

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

Overview

AR_INVOICE_LINES_V is an Oracle EBS Receivables (AR) reporting view owned by the APPS schema and defined over the AutoInvoice transaction line tables. The view "Collects invoice line item information," presenting a denormalized, presentation-ready projection of transaction lines from RA_CUSTOMER_TRX_LINES joined to RA_CUSTOMER_TRX, unit-of-measure and item master data, tax exemption data, and VAT tax names. Its principal purpose is to provide a stable, query-friendly interface for reports, concurrent programs, and integrations that need line-level invoice detail without reconstructing the underlying joins.

Because the view resolves item descriptions and tax indicators through the AR_INVOICE_SQL_FUNC_PUB package rather than purely through table joins, callers also inherit Receivables business logic — most importantly the derived tax existence flag discussed below.

Underlying Base Objects

The documented base objects underlying AR_INVOICE_LINES_V are:

• RA_CUSTOMER_TRX_LINES — the primary source of line rows, aliased as LINES, supplying customer_trx_line_id, line_type, description, quantities, prices, tax attributes, and Descriptive Flexfield context columns.
• RA_CUSTOMER_TRX — the transaction header parent, joined for header context.
• RA_CUST_TRX_TYPES — transaction type classification.
• MTL_SYSTEM_ITEMS — item master, providing inventory_item_id and GLOBAL_ATTRIBUTE1 for the ITEM_CODE derivation.
• MTL_UNITS_OF_MEASURE — supplies the UOM display name (UNIT_OF_MEASURE_NAME).
• AR_VAT_TAX_VL — tax name lookup that yields PRINTED_TAX_NAME.
• RA_TAX_EXEMPTIONS — exemption detail supporting PERCENT_EXEMPTION and EXEMPTION_TYPE.
• AR_MEMO_LINES — memo line linkage.
• MO_GLOBAL and AR_INVOICE_SQL_FUNC_PUB — packages invoked for organizational context and for the item description and tax-existence functions.

The view uses DECODE and NVL logic so that a line reports a single LINE_NUMBER and a single quantity/price regardless of whether it originates from an invoice, credit memo, or debit memo. This normalization is a key reason the view is preferred over direct table access.

Key Columns

• CUSTOMER_TRX_ID / CUSTOMER_TRX_LINE_ID — primary join keys to the header and to RA_CUSTOMER_TRX_LINES respectively.
• LINE_NUMBER, LINE_TYPE, ITEM_DESCRIPTION — presentation attributes for reporting. ITEM_DESCRIPTION is resolved by AR_INVOICE_SQL_FUNC_PUB.GET_DESCRIPTION, falling back to the stored line description.
• QUANTITY_ORDERED and QUANTITY — normalized quantities derived from ordered, invoiced, or credited values depending on which is populated.
• UNIT_SELLING_PRICE, EXTENDED_AMOUNT — line pricing and extended value.
• ITEM_CODE — derived from MTL_SYSTEM_ITEMS.GLOBAL_ATTRIBUTE1 or the line-level item global attribute.
• TAX_RATE, VAT_TAX_ID, PRINTED_TAX_NAME, LOCATION_RATE_ID — tax determination detail, where LOCATION_RATE_ID is the tax rate (SALES_TAX_ID) applicable to the line.
• TAX_EXEMPTION_ID, PERCENT_EXEMPTION, EXEMPTION_TYPE — exemption treatment, populated only when a tax exemption applies and no overriding sales tax rate exists.
• TAX_EXISTS_FOR_THIS_LINE_FLAG — the column matching the user's search. It is derived by AR_INVOICE_SQL_FUNC_PUB.GET_TAXYN(CUSTOMER_TRX_LINE_ID) and indicates whether tax has been calculated and exists for the line. It is the single most reliable indicator for filtering taxed versus non-taxed lines, since it reflects the package logic that governs tax distribution.
• ORIG_SYSTEM_BATCH_NAME-adjacent program columns, ORG_ID, REQUEST_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE — multi-org and audit context.

Common Use Cases and Queries

The most frequent use is identifying taxed and untaxed lines on posted transactions. A representative query is:

SELECT line.customer_trx_id, line.line_number, line.item_description, line.quantity, line.extended_amount, line.tax_exists_for_this_line_flag FROM ar_invoice_lines_v line WHERE line.org_id = :p_org_id AND line.customer_trx_id = :p_trx_id;

Filtering on the flag is common during tax reconciliation, tax audit extraction, and diagnostics where a transaction appears to have been posted without tax:

SELECT customer_trx_id, customer_trx_line_id, line_number, item_description, extended_amount FROM ar_invoice_lines_v WHERE tax_exists_for_this_line_flag = 'N';

Other scenarios include line-level revenue and credit memo reporting, exemption tracking through PERCENT_EXEMPTION, and feeds into external tax engines or data warehouse extracts. Because the view resolves descriptions and tax flags through PL/SQL functions, heavy ad hoc queries against high volumes should be tuned with appropriate predicates on customer_trx_id, org_id, or date ranges derived from the header.