Results for “ar_statements_v”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

AR_STATEMENTS_V is an APPS-owned view in the Oracle Receivables (AR) module that presents customer statement header information for reporting and integration purposes. Its documented description is annotated as "(Release 11.5 Only)," indicating that the object was catalogued against an earlier release and retained in the data dictionary of Oracle EBS 12.1.1 and 12.2.2 with a VALID status even though the underlying statement-generation model evolved in later releases. In practice, the view consolidates statement history rows with customer account, party, site, and statement cycle attributes so that a statement can be reported with a single row per historical statement event, including the customer name, account number, currency, and total amount due.

The view plays a reporting role rather than a transactional one. It is typically consumed by custom reports, extracts, and reconciliation queries that need a denormalized statement header without navigating the normalized model that spans AR_STATEMENTS_HISTORY, the trading partner tables in the HZ schema, and Receivables setup tables.

Underlying Base Objects

The documented base objects are AR_STATEMENTS_HISTORY, AR_STATEMENT_CYCLES, HZP_CUST_PKG, HZ_CUSTOMER_PROFILES, HZ_CUST_ACCOUNTS, HZ_CUST_ACCT_SITES, HZ_CUST_SITE_USES, and HZ_PARTIES. AR_STATEMENTS_HISTORY supplies the driving rows: statement date, bill-to location, finance charges, currency, total amount due, reprint indicator, line cluster, and audit columns. HZ_CUST_ACCOUNTS and HZ_PARTIES are joined on CUSTOMER_ID and PARTY_ID to provide the account number and party name.

The join to HZ_CUSTOMER_PROFILES determines the applicable customer profile, using a DECODE against HZP_CUST_PKG.GET_STATEMENT_SITE to fall back between the site-level and customer-level profile identifiers. AR_STATEMENT_CYCLES provides the cycle name, resolved through NVL of the history cycle and the profile cycle. HZ_CUST_SITE_USES and HZ_CUST_ACCT_SITES resolve location and bill-to/drawee site usage, with an outer join condition on the statement site and a filter restricting SITE_USE_CODE to 'BILL_TO' and 'DRAWEE'. A comment in the view text records a change dated 18-MAY-2000 by J Rautiainen, reflecting the historical evolution of the definition.

Key Columns

• ROW_ID — the ROWID of the AR_STATEMENTS_HISTORY row, retained for row identification.
• STATEMENT_DATE — the date the statement was generated.
• CYCLE — the statement cycle name from AR_STATEMENT_CYCLES.
• CUSTOMER_NAME and CUSTOMER_NUMBER — the party name and account number.
• CUSTOMER_ID — the identifier linking to HZ_CUST_ACCOUNTS.
• CUSTOMER_LOCATION and CUSTOMER_SITE — location and site-use information derived from the HZ site tables.
• CURRENCY_CODE and TOTAL_AMOUNT_DUE — statement currency and outstanding balance.
• FINANCE_CHARGES — finance charges applied on the statement.
• IS_IT_A_REPRINT — indicates whether the statement is a reprint.
• LINE_CLUSTER_ID — grouping identifier for the statement.
• Audit columns including LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN, CREATED_BY, and CREATION_DATE.

Common Use Cases and Queries

The view supports statement register reports, reprint analysis, finance charge review, and integration extracts that publish statement headers to downstream systems. A representative query retrieves recent statements for a customer:

• SELECT customer_number, customer_name, statement_date, currency_code, total_amount_due, finance_charges, is_it_a_reprint FROM ar_statements_v WHERE customer_id = :p_customer_id ORDER BY statement_date DESC;
• SELECT cycle, COUNT(*), SUM(total_amount_due) FROM ar_statements_v WHERE statement_date BETWEEN :p_from AND :p_to GROUP BY cycle;
• SELECT customer_number, statement_date FROM ar_statements_v WHERE is_it_a_reprint = 'Y' AND statement_date >= SYSDATE - 30;

Because of the underlying joins, queries should always constrain by CUSTOMER_ID or STATEMENT_DATE to avoid full scans across the history and HZ tables.