Search Results ctl_inv_tax

Overview

APPS.AR_TAX_SUMM_V is a reporting and integration view in the Oracle E-Business Suite Receivables (AR) module that consolidates tax line information from customer transactions into a summarized, tax-code-level presentation. The view aggregates tax amounts by tax precedence, tax code, tax-inclusive flag, tax rate, and customer transaction identifier, producing one summarized row per distinct tax grouping for a given transaction. Unlike the underlying transactional table RA_CUSTOMER_TRX_LINES, which stores each invoice, freight, and tax line as an individual record, AR_TAX_SUMM_V collapses the tax detail into a form that is convenient for tax reporting, reconciliation, and downstream integration. Because the view lives in the APPS schema, it is accessible to any database user granted the appropriate APPS synonyms and privileges, making it a common source for custom reports, Oracle Discoverer worksheets, and tax extracts.

Underlying Base Objects

ETRM metadata documents exactly two referenced base objects for this view: the synonym AR_VAT_TAX and the synonym RA_CUSTOMER_TRX_LINES. Although only two synonyms are formally catalogued, the view text references RA_CUSTOMER_TRX_LINES four times under distinct aliases (CTL_INV_LINE, CTL_INV_TAX, CTL_LINE, and CTL_TAX) and AR_VAT_TAX twice (INV_VAT and VAT). This self-join structure is significant: it uses RA_CUSTOMER_TRX_LINES once for the invoice or freight line (CTL_LINE), once for the tax line itself (CTL_TAX), and twice (CTL_INV_TAX, CTL_INV_LINE) to resolve the "previous" line relationship through the PREVIOUS_CUSTOMER_TRX_LINE_ID column. AR_VAT_TAX supplies the tax code, joined via VAT_TAX_ID using an outer join so that tax lines lacking a matching VAT tax definition are still returned. The tax line is identified by the filter CTL_TAX.LINE_TYPE = 'TAX', and CTL_TAX.LINK_TO_CUST_TRX_LINE_ID ties the tax back to its parent invoice line.

Key Columns

• TAX_PRECEDENCE (from CTL_TAX) — the ordering applied when multiple taxes are stacked on a single line; also used as a grouping key.
• TAX_CODE (from VAT, i.e., AR_VAT_TAX) — the user-defined tax code identifying the tax regime; grouped and used in reporting labels.
• AMOUNT_INCLUDES_TAX_FLAG (from CTL_TAX) — indicates whether the underlying amount is tax-inclusive or tax-exclusive, critical for correct tax computation.
• TAX_RATE (from CTL_TAX) — the effective tax rate applied to the line.
• CUSTOMER_TRX_ID (from CTL_TAX) — the transaction header identifier, linking the summarized result back to the transaction in RA_CUSTOMER_TRX_ALL.
• SUM(CTL_TAX.EXTENDED_AMOUNT) — the aggregated tax amount for the grouping, calculated as the sum of the extended tax line amounts.

Note that the view exposes no tax line ID or invoice line ID; it is intentionally denormalized and therefore unsuited to line-level updates. All columns are read-only projections derived from the join and aggregate.

Common Use Cases and Queries

The view is typically used to obtain a compact tax summary per transaction, for example when reconciling total tax charged against tax accounting entries or when feeding a tax reporting extract. The classic join path from the transactional lines table to this summary is through CUSTOMER_TRX_ID and TAX_CODE, since AR_TAX_SUMM_V already groups away the line detail.

A representative query joining the summary to transaction headers:

• SELECT s.customer_trx_id, s.tax_code, s.tax_rate, s.amount_includes_tax_flag, s.tax_precedence, s.amount FROM ap_tax_summ_v ... is not valid; use instead:
  SELECT h.trx_number, s.tax_code, s.tax_rate, s.tax_precedence, s.tax_amount
FROM apps.ar_tax_summ_v s, apps.ra_customer_trx_all h
WHERE s.customer_trx_id = h.customer_trx_id
AND h.org_id = :org_id;
• To compare the summarized tax against the raw lines:
  SELECT l.customer_trx_id, SUM(l.extended_amount) line_tax, s.tax_amount
FROM apps.ra_customer_trx_lines l, apps.ar_tax_summ_v s
WHERE l.customer_trx_id = s.customer_trx_id
AND l.line_type = 'TAX'
GROUP BY l.customer_trx_id, s.tax_amount;

Because the view performs aggregation and outer joins to AR_VAT_TAX, users should expect a NULL TAX_CODE when a tax line has no matching VAT definition. Performance is generally acceptable for transaction-scoped queries but less so for large multi-org scans, where filtering on CUSTOMER_TRX_ID or joining to RA_CUSTOMER_TRX_ALL with an ORG_ID predicate is recommended.