Search Results ar_cons_inv_all




Overview
Explore in AI Chat

Overview

The AR_CONS_INV_ALL table is a core data structure within Oracle E-Business Suite Receivables (AR) that stores the master definition for consolidated billing invoices. A consolidated invoice is a single invoice document that aggregates multiple transactions for a specific customer and bill-to location over a defined period, simplifying the billing and payment process. This table acts as the parent or header record for consolidated invoicing functionality, capturing the essential metadata about the consolidation run, such as its status, date range, and the customer context. Its role is critical for managing high-volume billing operations, enabling efficient statement generation and customer communication by grouping related charges.

Key Information Stored

While the provided metadata does not list specific columns, based on its description and foreign key relationships, the table typically stores key identifiers and control attributes for a consolidated invoice. The documented foreign keys indicate the presence of a CUSTOMER_ID column, linking to HZ_CUST_ACCOUNTS to identify the customer, and a SITE_USE_ID column, linking to HZ_CUST_SITE_USES_ALL to specify the exact bill-to site use address. Other common columns would include a unique CONS_INV_ID (primary key), a CONS_INV_NUMBER, consolidation run dates (FROM_DATE, TO_DATE), status flags (e.g., STATUS), and standard Oracle EBS audit columns (CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY).

Common Use Cases and Queries

This table is central to reporting on and troubleshooting consolidated billing activities. Common use cases include auditing the history of consolidated invoice generations for a customer, identifying consolidated invoices within a specific date range for period-end closing, and diagnosing issues where transactions were not properly included. A typical query would join to customer tables to retrieve meaningful names. For example, to find all consolidated invoices for a customer number in a given period:

  • SELECT aci.cons_inv_number, aci.from_date, aci.to_date, aci.status, hca.account_number, hcsu.location
  • FROM ar_cons_inv_all aci, hz_cust_accounts hca, hz_cust_site_uses_all hcsu
  • WHERE aci.customer_id = hca.cust_account_id
  • AND aci.site_use_id = hcsu.site_use_id
  • AND hca.account_number = 'CUST123'
  • AND aci.creation_date BETWEEN :p_start_date AND :p_end_date;

Related Objects

The AR_CONS_INV_ALL table has documented foreign key relationships with two foundational Trading Community Architecture (TCA) tables, establishing its place in the data model. These relationships are:

  • HZ_CUST_ACCOUNTS: Joined via the AR_CONS_INV_ALL.CUSTOMER_ID column. This links the consolidated invoice to the customer master record.
  • HZ_CUST_SITE_USES_ALL: Joined via the AR_CONS_INV_ALL.SITE_USE_ID column. This links the consolidated invoice to the specific customer site (bill-to address) used for the consolidation.

In practice, this table is also the parent to transaction-level detail tables (such as AR_CONS_INV_TRX_ALL, though not listed in the provided metadata), which store the individual transactions included in each consolidated invoice batch.