Search Results bill_level_flag

Overview

AR_CONS_INV_ALL is the Oracle Receivables (AR) master table that stores information about consolidated billing invoices. In Oracle E-Business Suite Release 12.1.1 and 12.2.2, consolidated billing allows a single invoice to summarize transactions, receipts, adjustments, and credits across multiple transactions for a customer or customer site. The table resides in the AR schema, is documented as VALID, and contains 58 columns in the ETRM 12.2.2 physical schema.

From a Data Vault modeling perspective, the mined classification for AR_CONS_INV_ALL is a link. This is a heuristic suggestion rather than a native EBS concept: the table records associations between a customer, a customer site use, and an aggregated billing event. The links are evidenced by the foreign keys to HZ_CUST_SITE_USES_ALL and HZ_CUST_ACCOUNTS, while the surrounding columns capture the measurable facts of the consolidated billing run. Practically, AR_CONS_INV_ALL behaves as a transaction-like record sitting between customer master data and the consolidated invoice output presented to the customer.

Key Information Stored

The table is anchored by a surrogate primary key, CONS_INV_ID, enforced through the unique index AR_CONS_INV_U1. This is the business-key candidate documented in the metadata and is the standard join key to dependent objects.

• CONS_INV_ID — Surrogate primary key and unique business-key candidate identifying each consolidated invoice.
• CONS_BILLING_NUMBER — Human-readable consolidated billing number.
• CUSTOMER_ID — Foreign key to HZ_CUST_ACCOUNTS identifying the customer.
• SITE_USE_ID — Foreign key to HZ_CUST_SITE_USES_ALL identifying the customer site use.
• CONS_INV_TYPE — Type of consolidated invoice.
• STATUS / PRINT_STATUS — Processing and printing states of the invoice.
• TERM_ID — Payment terms applied to the consolidated invoice.
• ISSUE_DATE, CUT_OFF_DATE, DUE_DATE, BILLING_DATE, BILLING_CYCLE_ID — Core billing lifecycle dates and cycle reference.
• CURRENCY_CODE — Currency of the aggregated amounts.
• BEGINNING_BALANCE / ENDING_BALANCE — Opening and closing balances for the billing period.
• TOTAL_RECEIPTS_AMT, TOTAL_ADJUSTMENTS_AMT, TOTAL_CREDITS_AMT, TOTAL_FINANCE_CHARGES_AMT, TOTAL_TRX_AMT, TOTAL_TAX_AMT — Aggregated monetary components.
• AGING_BUCKET1_AMT through AGING_BUCKET7_AMT — Aging distribution of the outstanding balance.
• ORG_ID — Multi-org operating unit identifier.

The table also includes standard WHO audit columns (CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN), the ATTRIBUTE_CATEGORY and ATTRIBUTE1 through ATTRIBUTE15 descriptive flexfield columns, and additional supporting columns such as START_DATE, BILL_LEVEL_FLAG, UNPAID_REASON, LAST_BILLING_DATE, LAST_CHARGE_DATE, and REMIT_TO_ADDRESS_ID.

Common Use Cases and Queries

AR_CONS_INV_ALL supports consolidated billing reporting, customer balance reconciliation, aging analysis, and audit of aggregated invoice components. Typical query patterns join to customers and site uses to present billing history:

• Retrieve consolidated invoices for a customer, with site and billing details.
• Reconcile BEGINNING_BALANCE to ENDING_BALANCE using the TOTAL_* columns.
• Produce aging reports from AGING_BUCKET1_AMT through AGING_BUCKET7_AMT.
• Monitor processing status through STATUS and PRINT_STATUS.

A representative join is:

SELECT ci.cons_billing_number, ci.issue_date, ci.ending_balance FROM ar.ar_cons_inv_all ci WHERE ci.customer_id = :customer_id AND ci.org_id = :org_id;

Related Objects

• HZ_CUST_ACCOUNTS — joined via AR_CONS_INV_ALL.CUSTOMER_ID = HZ_CUST_ACCOUNTS.CUST_ACCOUNT_ID.
• HZ_CUST_SITE_USES_ALL — joined via AR_CONS_INV_ALL.SITE_USE_ID = HZ_CUST_SITE_USES_ALL.SITE_USE_ID.
• AR_CONS_INV_LINES_ALL — line-level detail associated with the consolidated invoice.
• RA_CUSTOMER_TRX_ALL — transactions aggregated into the consolidated billing.
• AR_CASH_RECEIPTS_ALL — receipts reflected in TOTAL_RECEIPTS_AMT.
• AR_ADJUSTMENTS_ALL — adjustments reflected in TOTAL_ADJUSTMENTS_AMT.
• AR_TERMS — referenced through TERM_ID.

Together these objects form the consolidated billing data model, with AR_CONS_INV_ALL as the central link between customer master data and the aggregated billing facts.