Search Results details_level




Overview
Explore in AI Chat

Overview

APPS.AR_CUSTOMER_SEARCH_V is a read-only Oracle EBS view that presents a denormalized, search-oriented projection of customer account data sourced from the Receivables and Trading Community (HZ) schema. Its principal design objective is to expose a single, uniform result set suitable for customer search and lookup interfaces, reporting, and integration consumers that require either account-level or address-level rows in one structure.

The defining characteristic of the view is the DETAILS_LEVEL column, which acts as a row-type discriminator. It allows a consumer to receive, within one query, both a summary row for each customer account and a detailed row for each address associated with that account. This union-based design is the reason users frequently search for "details_level" in connection with this object — the column is the pivot that governs how the result set must be filtered and interpreted.

Underlying Base Objects

The documented base objects referenced by the view are:

  • HZ_CUST_ACCOUNTS (accessed via synonym) — supplies the customer account identifier (CUST_ACCOUNT_ID), the account number, and the foreign key to the party record.
  • HZ_PARTIES (accessed via synonym) — supplies the party name used as the customer name, joined on PARTY_ID.
  • AR_ADDRESSES_V (view) — supplies address-level detail, joined on CUSTOMER_ID equal to the customer account identifier.
  • ARP_ADDR_PKG (package) — the address package documented as a referenced base object, underpinning the address view used in the address branch.

The view is a UNION ALL of two branches. The first branch joins HZ_CUST_ACCOUNTS, HZ_PARTIES, and AR_ADDRESSES_V to produce one row per customer address, tagged with DETAILS_LEVEL of 'ADDR'. The second branch joins only HZ_CUST_ACCOUNTS and HZ_PARTIES to produce one summary row per customer account, tagged with DETAILS_LEVEL of 'CUST'. Because UNION ALL is used rather than UNION, no duplicate elimination occurs, and each branch may present rows that are logically paired.

Key Columns

  • CUSTOMER_ID — the customer account identifier (CUST.CUST_ACCOUNT_ID).
  • DETAILS_LEVEL — the row-type discriminator: 'ADDR' for address-level rows and 'CUST' for account summary rows.
  • CUSTOMER_NUMBER — the account number from HZ_CUST_ACCOUNTS.
  • CUSTOMER_NAME — the party name, truncated to 50 bytes via SUBSTRB.
  • ADDRESS_ID — the address identifier for address rows; set to -1 in the customer summary branch.
  • CONCATENATED_ADDRESS — the formatted address for address rows; the literal 'ALL_LOCATIONS' for the customer summary row.
  • CONTACT_NAME and CONTACT_PHONE — exposed as NULL placeholders (TO_CHAR(NULL)) in both branches.
  • BILL_TO_SITE_USE_ID — set to -1 in both branches, indicating no specific site-use is resolved by this view.
  • SITE_USES — exposed as NULL in both branches.

Common Use Cases and Queries

The view is typically queried by a search screen or report that needs to show matching customer accounts and, on demand, the addresses beneath them. Filtering by DETAILS_LEVEL is the standard technique for obtaining either summary or detail rows.

To retrieve account summary rows only:

  • SELECT customer_id, customer_number, customer_name FROM apps.ar_customer_search_v WHERE details_level = 'CUST';

To retrieve address-level rows for a given account:

  • SELECT customer_id, customer_number, customer_name, address_id, concatenated_address FROM apps.ar_customer_search_v WHERE details_level = 'ADDR' AND customer_id = :p_customer_id;

To count rows by level for validation or reconciliation:

  • SELECT details_level, COUNT(*) FROM apps.ar_customer_search_v GROUP BY details_level;

Consumers should note that CONTACT_NAME, CONTACT_PHONE, and SITE_USES are always NULL, and BILL_TO_SITE_USE_ID is always -1; the view supplies no contact or site-use information despite exposing those columns.