Search Results bill_level

Overview

AR_CONS_INV_V is a consolidated billing inquiry view owned by the APPS schema in Oracle E-Business Suite Receivables (AR). It is documented as VALID in both release 12.1.1 and 12.2.2. The view exposes summarized consolidated invoice data for reporting and integration, aggregating balances, receipts, adjustments, credits, finance charges, transaction amounts, tax amounts, and aging buckets across the AR_CONS_INV base table. It joins customer, party, account, site, location, territory, operating unit, and payment term reference data to present a single denormalized row per consolidated billing grouping.

This view is the standard access point for consolidated billing inquiries where the underlying AR_CONS_INV rows must be rolled up and enriched with customer and remit-to/bill-to details. The object queried in this article, TOTAL_TAX_AMT, is present as SUM(CI.TOTAL_TAX_AMT), meaning tax figures are aggregated across the grouped consolidated invoice records.

Underlying Base Objects

The view is defined over AR_CONS_INV as the primary source, self-joined as AR_CONS_INV CI_P to retrieve attribute columns. Supporting objects provide descriptive context: HZ_CUST_ACCOUNTS and HZ_CUST_ACCT_SITES supply account and site data; HZ_CUST_SITE_USES identifies bill-to and remit-to site uses; HZ_LOCATIONS holds address elements; HZ_PARTIES and HZ_PARTY_SITES resolve party name and site linkage; HZ_CUSTOMER_PROFILES and HZ_CUST_PROFILE_CLASSES provide profile classification; HR_OPERATING_UNITS resolves the operating unit name; FND_TERRITORIES_VL supplies territory short names; and RA_TERMS_VL supplies the payment term name. HZ_FORMAT_PUB is a package whose FORMAT_ADDRESS function produces formatted bill-to and remit-to address strings, which are returned as virtual columns rather than stored values. Several referenced objects are synonyms over the underlying HZ and AR tables rather than the base tables themselves.

Because the view aggregates AR_CONS_INV, the grain of the result differs from the base table: multiple consolidated invoice lines collapse into grouped totals.

Key Columns

• CUSTOMER_ID, PARTY_NAME, ACCOUNT_NUMBER, PROFILE_CLASS_NAME — customer identity and profile classification.
• ORG_ID, OPERATING_UNIT_NAME — operating unit context, required for multi-org reporting.
• CONS_BILLING_NUMBER, DUE_DATE, CURRENCY_CODE, CONS_INV_TYPE, BILLING_CYCLE_ID, BILLING_DATE, LAST_BILLING_DATE — consolidated invoice header attributes.
• FORMATTED_BILL_TO_ADDRESS, FORMATTED_REMIT_TO_ADDRESS and component address columns — bill-to and remit-to detail sourced from HZ_LOCATIONS via HZ_FORMAT_PUB.
• TERM_ID, TERM_NAME — payment term reference.
• BEGINNING_BALANCE, ENDING_BALANCE, TOTAL_RECEIPTS_AMT, TOTAL_ADJUSTMENTS_AMT, TOTAL_CREDITS_AMT, TOTAL_FINANCE_CHARGES_AMT, TOTAL_TRX_AMT — aggregated monetary measures.
• TOTAL_TAX_AMT — SUM(CI.TOTAL_TAX_AMT); the aggregated tax amount, the column most commonly searched for tax reporting.
• AGING_BUCKET1_AMT through AGING_BUCKET7_AMT — aggregated aging analysis amounts.
• ATTRIBUTE1–ATTRIBUTE15 — descriptive flexfield context and segments.

Common Use Cases and Queries

Typical uses include consolidated billing reconciliation, tax exposure reporting, aging analysis, and downstream extracts for data warehousing. Because AR_CONS_INV_V performs aggregation, a report filtered on TOTAL_TAX_AMT should apply conditions at the customer or consolidated billing level rather than expecting transactional granularity.

Sample query retrieving tax by consolidated billing number and operating unit:

SELECT cons_billing_number,
       party_name,
       currency_code,
       total_tax_amt,
       total_trx_amt,
       ending_balance
  FROM apps.ar_cons_inv_v
 WHERE org_id = :p_org_id
   AND total_tax_amt > 0
   AND billing_date BETWEEN :p_start AND :p_end
 ORDER BY total_tax_amt DESC;

A second common pattern aggregates tax by customer:

SELECT customer_id,
       party_name,
       SUM(total_tax_amt) tax_total,
       SUM(ending_balance) balance_total
  FROM apps.ar_cons_inv_v
 GROUP BY customer_id, party_name;

Note that the view uses aggregate functions without an explicit GROUP BY in the SELECT text, so the grouping is implied by the non-aggregated columns, and NULL literals are returned for SITE_USE_ID and one unspecified column. Consumers should be aware of this behavior when joining the view to transactional sources.