Search Results tax_recovery_rate

Overview

The JG_AP_TAX_LINES_SUMMARY_V view in the APPS schema belongs to the JG (Regional Localizations) product family within Oracle E-Business Suite 12.1.1 and 12.2.2. It is not a general-purpose reporting view available in all EBS instances; it exists specifically to support regional/localization tax reporting and integration requirements, particularly where a tax group is applied to an Accounts Payable invoice distribution and the individual tax lines must be expanded for downstream processing.

The view presents Accounts Payable invoice distribution data expanded across the constituent tax codes of an associated tax group. Where an invoice distribution has been assigned a tax group, the view fans out one output row per tax code belonging to that tax group, as indicated by the inline comment in the view definition: "FOR TRANSACTIONS WITH A TAX GROUP. EXPAND ROWS FOR EACH TAX CODE IN THIS TAX GROUP." This expansion is essential for statutory reporting where tax amounts, recovery rates, and tax account combinations must be reported at the individual tax-code level rather than at the aggregate distribution level.

The view is defined over AP invoice, distribution, supplier, tax code, and tax group tables, combined with two PL/SQL packages that supply rounding and tax-rate calculation logic. This makes it a derived, calculation-heavy view rather than a simple join of transactional tables.

Underlying Base Objects

The documented base objects referenced by the view are:

• AP_INVOICES (synonym) — invoice header attributes such as invoice number, date, currency, exchange rate, and vendor identifiers.
• AP_INVOICE_DISTRIBUTIONS (synonym) — distribution-level attributes including distribution line number, line type, amount, and tax-related flags.
• AP_SUPPLIERS (synonym) — supplier name and segment.
• AP_SUPPLIER_SITES (synonym) — supplier site geography (country, state, county, ZIP).
• AP_TAX_CODES (synonym) — tax code identifier, name, tax type, tax rate, recovery rate, and tax code combination.
• AR_TAX_GROUP_CODES (synonym) — the tax group used to expand rows into individual tax codes.
• FINANCIALS_SYSTEM_PARAMETERS (synonym) — provides the non-recoverable tax flag that influences account combination resolution.
• AP_TAX_ENGINE_PKG (package) — supplies SUM_TAX_GROUP_RATE for computing the aggregate tax group rate.
• AP_UTILITIES_PKG (package) — supplies AP_ROUND_CURRENCY used in tax amount calculations.

The view performs substantial arithmetic: for distributions where AMOUNT_INCLUDES_TAX_FLAG = 'Y', it derives the tax-exclusive base by subtracting the aggregated group tax amount, then applies each tax code's rate (or absolute rate for OFFSET tax types) and re-rounds to the invoice currency.

Key Columns

Important columns exposed include:

• INVOICE_ID, INVOICE_NUM, INVOICE_DATE, INVOICE_CURRENCY_CODE, EXCHANGE_RATE — invoice header context.
• INVOICE_DISTRIBUTION_ID, DISTRIBUTION_LINE_NUMBER, LINE_TYPE_LOOKUP_CODE — distribution identity.
• PARENT_INVOICE_ID, PO_DISTRIBUTION_ID — linkage to sourcing and parent documents; the PO distribution is nulled for OFFSET tax types.
• VENDOR_ID, VENDOR_NAME, VENDOR_SITE_ID, plus site geography columns — supplier reporting.
• TAX_ID, NAME, TAX_RATE, TAX_RECOVERY_RATE — the individual tax code applied to each expanded row.
• TAX_CODE_OVERRIDE_FLAG, TAX_RECOVERY_OVERRIDE_FLAG — indicate manual overrides.
• Distribution account code combination ID, resolved conditionally based on NON_RECOVERABLE_TAX_FLAG.
• USSGL_TRANSACTION_CODE and context — federal/statutory posting attributes.

Common Use Cases and Queries

Typical use cases include statutory tax reporting for JG localizations, reconciliation of withheld versus recoverable tax, and integration extracts feeding downstream tax engines. Note that the search term offset_tax_code_id does not appear as a documented column in the metadata provided; the view does, however, handle OFFSET tax types through DECODE(ATC1.TAX_TYPE, 'OFFSET', ...) logic affecting rate, recovery rate, and PO distribution. Callers should filter tax type when isolating offset entries.

SELECT invoice_id
     , invoice_num
     , invoice_date
     , vendor_name
     , tax_id
     , name            AS tax_code_name
     , tax_rate
     , tax_recovery_rate
  FROM apps.jg_ap_tax_lines_summary_v
 WHERE invoice_date BETWEEN :p_from AND :p_to
   AND name = :p_tax_code_name;

Because the view invokes AP_TAX_ENGINE_PKG and AP_UTILITIES_PKG, queries should restrict the driving row set with invoice or date predicates to avoid excessive package invocations across the full distribution population.