Search Results jai_ap_match_inv_tax_v




Overview
Explore in AI Chat

Overview

JAI_AP_MATCH_INV_TAX_V is a read-only database view owned by the APPS schema in Oracle E-Business Suite, delivered as part of the JA (Asia/Pacific Localizations) product family. Its documented purpose is to fetch tax information for invoices that are not standalone invoices — that is, invoices created through a matching process against purchase orders or receipts rather than entered directly into Payables. The view is marked VALID in both Oracle EBS 12.1.1 and 12.2.2, and its structure is identical across the two releases, which simplifies migration and upgrade testing.

Functionally, the view reconciles tax lines recorded on the procurement side (PO and receiving) with the tax amounts captured against the matched Payables invoice. It exposes a normalized result set in which each row carries a MATCH_TYPE discriminator, allowing downstream reporting, reconciliation, and localization-specific tax reporting (notably India and other Asia/Pacific tax regimes) to distinguish item-to-PO matches from item-to-receipt matches. The view is typically consumed by concurrent programs, localization reports, and custom reconciliation queries rather than by the standard Payables EDI or invoice entry forms.

Underlying Base Objects

The view is defined over four documented base objects, all referenced through APPS synonyms:

The view body is a UNION ALL of two SELECT statements. The first branch, labelled ITEM_TO_PO, joins JAI_PO_TAXES to JAI_AP_MATCH_INV_TAXES filtering B.RCV_TRANSACTION_ID IS NULL, and returns VENDOR_SITE_ID as NULL. The second branch, labelled ITEM_TO_RECEIPT, joins JAI_RCV_LINE_TAXES to JAI_AP_MATCH_INV_TAXES and RCV_TRANSACTIONS where RCV_TRANSACTION_ID IS NOT NULL. Both branches project the same tax attribute columns, and the outer query applies SUM(NVL(TAX_LINE.TAX_AMOUNT,0)) with a GROUP BY over the non-aggregated columns, so the view returns one consolidated tax amount per unique combination of match type, invoice, transaction, and tax attributes.

Key Columns

  • MATCH_TYPE — literal value ITEM_TO_PO or ITEM_TO_RECEIPT; the primary discriminator in the view.
  • INVOICE_ID — Payables invoice identifier; the principal join key to AP_INVOICES_ALL.
  • PARENT_INVOICE_LINE_NUMBER — links the tax line back to the parent invoice distribution line.
  • TRANSACTION_ID — carries LINE_LOCATION_ID for PO matches and the receiving TRANSACTION_ID for receipt matches.
  • TAX_LINE_NO, TAX_ID, TAX_TYPE — tax line sequence, tax regime/rate reference, and classification (for example, VAT or excise).
  • PRECEDENCE_1 through PRECEDENCE_10 — the tax precedence matrix used by Asia/Pacific tax calculation to determine compounding order.
  • CURRENCY, TAX_RATE, QTY_RATE, UOM — currency of the tax, rate applied, quantity-based rate, and unit of measure.
  • TAX_AMOUNT — aggregated tax amount after the NVL-to-zero and SUM treatment.
  • VENDOR_ID, VENDOR_SITE_ID — supplier identifiers; VENDOR_SITE_ID is populated only in the receipt branch.
  • MODVAT_FLAG — indicates modified VAT treatment applicable to the localization.

Common Use Cases and Queries

The view supports tax reconciliation between matched Payables invoices and their originating procurement documents, audit of tax amounts by match type, and localization reporting where standalone invoices must be excluded.

  • Listing all tax lines for a matched invoice:
SELECT match_type, invoice_id, parent_invoice_line_number,
       tax_line_no, tax_id, tax_rate, tax_amount, modvat_flag
FROM   apps.jai_ap_match_inv_tax_v
WHERE  invoice_id = :p_invoice_id
ORDER  BY match_type, tax_line_no;
  • Summarising tax by match type for a period:
SELECT match_type, SUM(tax_amount) total_tax
FROM   apps.jai_ap_match_inv_tax_v
WHERE  invoice_id IN (SELECT invoice_id FROM apps.ap_invoices_all
                      WHERE  invoice_date BETWEEN :p_from AND :p_to)
GROUP  BY match_type;
  • Identifying receipt-matched tax lines with their origin transaction:
SELECT v.invoice_id, v.transaction_id, v.tax_id, v.tax_amount,
       r.transaction_type, r.transaction_date
FROM   apps.jai_ap_match_inv_tax_v v,
       apps.rcv_transactions r
WHERE  v.match_type = 'ITEM_TO_RECEIPT'
AND    v.transaction_id = r.transaction_id;

Because the view is read-only and aggregates tax amounts, it should be used for reporting only; tax corrections must be applied to the underlying localization tables. Queries benefit from filters on INVOICE_ID or a date-restricted subquery against AP_INVOICES_ALL to limit the UNION ALL scan.