Search Results ari_customer_search_v




Overview
Explore in AI Chat

Overview

ARI_CUSTOMER_SEARCH_V is a Receivables (AR) view owned by the APPS schema and defined over the Oracle Trading Community Architecture (TCA) customer model. Its purpose is to present a unified, denormalized customer search result set in which each row represents either a customer account or a specific customer account site address. This makes it suitable for LOVs, search pages, and integration extracts that need a single, already-formatted address string rather than the many individual address attributes required by the underlying TCA tables.

The view resolves the common reporting problem of joining HZ_CUST_ACCOUNTS, HZ_PARTIES, HZ_CUST_ACCT_SITES, HZ_PARTY_SITES, HZ_LOCATIONS, and FND_TERRITORIES_TL, then invoking ARP_ADDR_PKG.FORMAT_ADDRESS to produce a display-ready address. It is exposed as a valid object under Oracle EBS 12.1.1 and 12.2.2 with essentially identical column semantics, though the documented metadata is taken from 12.2.2.

Underlying Base Objects

The documented base objects referenced by the view are ARP_ADDR_PKG (a PL/SQL package supplying the address-formatting function), plus synonyms over the following TCA tables: HZ_CUST_ACCOUNTS, HZ_PARTIES, HZ_CUST_ACCT_SITES, HZ_PARTY_SITES, HZ_LOCATIONS, and FND_TERRITORIES_TL.

  • HZ_CUST_ACCOUNTS — provides CUST_ACCOUNT_ID, ACCOUNT_NUMBER, PARTY_ID, and ORG_ID.
  • HZ_PARTIES — supplies PARTY_NAME.
  • HZ_CUST_ACCT_SITES — supplies CUST_ACCT_SITE_ID (the address_id) and links the account to a party site.
  • HZ_PARTY_SITES and HZ_LOCATIONS — supply the raw address columns and LOCATION_ID.
  • FND_TERRITORIES_TL — supplies TERRITORY_SHORT_NAME, joined via LOC.COUNTRY and USERENV('LANG') using outer joins.
  • ARP_ADDR_PKG.FORMAT_ADDRESS — converts the address style plus address elements and territory into the formatted address string.

The view is a UNION of two branches: the 'ADDR' branch returns one row per customer account site with a formatted address, and the 'CUST' branch returns one row per customer account carrying the literal 'ALL_LOCATIONS', used as a placeholder so every account appears even without a site row.

Key Columns

  • CUSTOMER_ID — CUST_ACCOUNT_ID from HZ_CUST_ACCOUNTS.
  • DETAILS_LEVEL — 'ADDR' for a site-level row, 'CUST' for a customer-level row.
  • CUSTOMER_NUMBER — the account number.
  • CUSTOMER_NAME — party name from HZ_PARTIES.
  • ADDRESS_ID — CUST_ACCT_SITE_ID for site rows; -1 for customer-level rows.
  • CONCATENATED_ADDRESS — the formatted address string, or 'ALL_LOCATIONS' on the customer-level branch. This is the column most commonly used in LOV display and search filters.
  • CONTACT_NAME / CONTACT_PHONE — always NULL in this version (TO_CHAR(NULL)); contact data is not sourced here.
  • BILL_TO_SITE_USE_ID — -1 placeholder; not a true site-use identifier.
  • SITE_USES — always NULL in this version.
  • ORG_ID — the operating unit on site rows, and -1 on customer-level rows.

Common Use Cases and Queries

Typical usage includes customer LOVs, search forms that accept a partial address, and integration extracts that require a single formatted address. The user term "concatenated_address" maps directly to the view column of that name.

Find customer sites whose formatted address contains a value:

SELECT customer_id, customer_number, customer_name, address_id, concatenated_address
FROM   apps.ari_customer_search_v
WHERE  details_level = 'ADDR'
AND    upper(concatenated_address) LIKE '%'||upper(:p_address)||'%';

List all rows (customer-level and site-level) for one account:

SELECT details_level, customer_number, customer_name, address_id, concatenated_address, org_id
FROM   apps.ari_customer_search_v
WHERE  customer_id = :p_customer_id
ORDER  BY details_level, address_id;

Retrieve a customer plus its site addresses in one query using the UNION behavior as intended — filter DETAILS_LEVEL to control whether account-level or address-level records are returned, and treat ADDRESS_ID = -1 or BILL_TO_SITE_USE_ID = -1 as sentinel values rather than valid identifiers.