Search Results formatted_bill_to_address




Overview
Explore in AI Chat

Overview

APPS.AR_CONS_INV_V is a consolidated billing inquiry view in Oracle E-Business Suite Release 12.1.1 and 12.2.2. It presents the summarized outcome of the Consolidated Billing Invoice (CBI) process, in which transactions belonging to a single customer account are grouped and billed together on a recurring billing cycle rather than individually. The view flattens the denormalized data held in the AR_CONS_INV table and enriches it with customer, party, account, address, operating unit, term, and territory attributes sourced from the Trading Community Architecture (TCA) and Oracle Receivables reference objects.

Its principal role is to serve as a reporting and inquiry layer. Rather than requiring report developers to re-derive customer names, profile classes, formatted addresses, and currency descriptions, the view exposes these values in a single row per consolidated billing number, aggregated across the constituent transactions. It is the object behind consolidated billing queries in Receivables reporting and is frequently referenced by custom extracts and integrations that must reproduce the consolidated invoice layout outside the standard Oracle Reports and OAF pages.

Underlying Base Objects

The primary driving table is AR_CONS_INV (referenced through a SYNONYM), which stores the consolidated billing header and its aggregated balances, aging buckets, and billing cycle attributes. The view self-joins AR_CONS_INV as ci_p to retrieve descriptive flexfield columns (attribute_category and attribute1 through attribute15) associated with the consolidated invoice.

Customer and party context is drawn from HZ_CUST_ACCOUNTS (account_number), HZ_PARTIES (party_name), HZ_CUSTOMER_PROFILES (via HZ_CUST_PROFILE_CLASSES for profile_class_name), and HZ_CUST_ACCT_SITES and HZ_CUST_SITE_USES for site-level usage. Address components come from HZ_LOCATIONS, which supplies ADDRESS1 through ADDRESS4, CITY, COUNTY, STATE, PROVINCE, and POSTAL_CODE for both the bill-to and remit-to locations. Territory names are resolved from FND_TERRITORIES_VL. Operating unit context is obtained from HR_OPERATING_UNITS, and payment terms from RA_TERMS_VL. The formatted addresses themselves are produced by the HZ_FORMAT_PUB package, which is invoked directly in the SELECT list rather than being pre-stored.

Key Columns

  • customer_id, party_name, account_number, profile_class_name — customer identification and classification attributes inherited from TCA.
  • cons_billing_number, cons_inv_type, bill_level — the consolidated billing identifier, its type, and the hierarchy level (hard-coded as 'ACCOUNT').
  • formatted_bill_to_address — generated by HZ_FORMAT_PUB.format_address on the bill-to location_id; the object of the user's search and the most commonly requested column.
  • formatted_remit_to_address — the equivalent formatted remit-to address.
  • ADDRESS1–ADDRESS4, CITY, COUNTY, STATE, PROVINCE, POSTAL_CODE, TERRITORY_SHORT_NAME — discrete address elements for bill-to and remit-to parties.
  • org_id, operating_unit_name — multi-org security and operating unit context.
  • due_date, last_billing_date, billing_date, issue_date, term_id, term_name, currency_code — billing cycle and payment term attributes.
  • Aggregate measures — beginning_balance, ending_balance, total_receipts_amt, total_adjustments_amt, total_credits_amt, total_finance_charges_amt, total_trx_amt, total_tax_amt, and aging_bucket1_amt through aging_bucket7_amt, each summed across the consolidated transactions.

Common Use Cases and Queries

The view is typically queried to list consolidated invoice headers with customer names and formatted addresses, or to reconcile aging balances at the account level.

  • Consolidated billing detail reports filtered by billing cycle or billing date.
  • Customer-facing statements requiring formatted bill-to and remit-to addresses.
  • Aging analysis joining on currency_code, org_id, and term_id.
  • Extracts feeding external collections or print vendors.

A representative query:

SELECT cons_billing_number, party_name, account_number, currency_code, billing_date, formatted_bill_to_address, sum(total_trx_amt) FROM apps.ar_cons_inv_v WHERE org_id = :p_org_id AND billing_date BETWEEN :p_from AND :p_to GROUP BY cons_billing_number, party_name, account_number, currency_code, billing_date, formatted_bill_to_address;

Because formatted addresses are computed at runtime by HZ_FORMAT_PUB, performance-sensitive queries should filter on indexed columns such as cons_billing_number or org_id before the address formatting is evaluated.