Search Results details_level

Overview

AR_CUSTOMER_SEARCH_V is a reporting view owned by the APPS schema in Oracle E-Business Suite Receivables (AR). It presents a flattened, search-optimized projection of customer account and party data, designed to support customer search interfaces and lookup utilities within the Receivables module. The view is documented as VALID in ETRM metadata for both 12.1.1 and 12.2.2, and its structure is stable across those releases.

The view's distinguishing characteristic is its two-tier composition. It returns one row per customer address ("ADDR" level) and one summary row per customer account ("CUST" level), joined through a UNION ALL. This design allows a consuming form, concurrent program, or external integration to present both a roll-up entry ("ALL_LOCATIONS") and individual address records within the same result set, without requiring the caller to issue separate queries. It is a read-only view and should never be used as a target for DML.

Underlying Base Objects

The ETRM metadata documents four referenced base objects: HZ_CUST_ACCOUNTS (synonym), HZ_PARTIES (synonym), AR_ADDRESSES_V (view), and ARP_ADDR_PKG (package).

HZ_CUST_ACCOUNTS supplies the customer identity and account number. HZ_PARTIES supplies the party name, truncated to 50 bytes via SUBSTRB to align with column display limits in the search UI. AR_ADDRESSES_V is the address projection used to derive the "ADDR" level rows and supplies ADDRESS_ID and CONCATENATED_ADDRESS. ARP_ADDR_PKG is referenced indirectly; it backs the address formatting logic exposed through AR_ADDRESSES_V.

Because the view joins the Trading Community Architecture (TCA) model, any query against it inherits TCA semantics — a single party may own multiple customer accounts, and a single customer account may own multiple addresses. The UNION ALL does not deduplicate, which is intentional: the "CUST" row is a synthetic summary entry, not a duplicate of the "ADDR" rows.

Key Columns

• CUSTOMER_ID — Maps to HZ_CUST_ACCOUNTS.CUST_ACCOUNT_ID. This is the account-level identifier, not the party identifier.
• DETAILS_LEVEL — Literal flag with values 'ADDR' or 'CUST', indicating which branch of the union produced the row. This column is the primary discriminator for consumers.
• CUSTOMER_NUMBER — The account number from HZ_CUST_ACCOUNTS.
• CUSTOMER_NAME — Party name from HZ_PARTIES, truncated to 50 bytes.
• ADDRESS_ID — Address identifier for "ADDR" rows; hard-coded to -1 for "CUST" rows.
• CONCATENATED_ADDRESS — Formatted address string for "ADDR" rows; the literal 'ALL_LOCATIONS' for "CUST" rows.
• CONTACT_NAME, CONTACT_PHONE — Cast as TO_CHAR(NULL); the columns exist for interface parity but are not populated.
• BILL_TO_SITE_USE_ID — Hard-coded to -1 in both branches; not populated.
• SITE_USES — Cast as TO_CHAR(NULL); reserved and unpopulated.

Common Use Cases and Queries

The most common scenario, and the one matching the original search intent, is locating a customer account or address by name, number, or partial identifier. The following query returns the account-level roll-up plus every address for accounts whose number or name matches a pattern:

SELECT customer_id, details_level, customer_number,
       customer_name, address_id, concatenated_address
FROM   apps.ar_customer_search_v
WHERE  customer_number LIKE :p_number
OR     customer_name   LIKE :p_name
ORDER BY customer_name, details_level DESC;

To restrict results to address-level records only — for example, when populating an address list of values — filter on the details level:

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

To drive a two-level search form, return both levels and let the form render the "ALL_LOCATIONS" row as a parent node:

SELECT customer_id, details_level, customer_name, concatenated_address
FROM   apps.ar_customer_search_v
WHERE  UPPER(customer_name) LIKE UPPER('%' || :p_key || '%')
ORDER BY customer_name,
         CASE details_level WHEN 'CUST' THEN 1 ELSE 2 END;

Because the view is defined over TCA synonyms and contains no bind-sensitive or session-dependent logic, it is safe for concurrent program extracts, BI Publisher data sets, and inbound integration lookups. Callers should note that CONTACT_NAME, CONTACT_PHONE, BILL_TO_SITE_USE_ID, and SITE_USES return NULL or -1 and must not be relied upon for contact-level reporting; use AR_CONTACTS or the HZ contact views for that purpose.