Search Results csc_hz_parties_srch_rel_v

Overview

CSC_HZ_PARTIES_SRCH_REL_V is a read-only database view owned by the APPS schema in Oracle E-Business Suite, delivered as part of the CSC (Customer Care) product family. It is documented in the E-Business Suite Technical Reference Manual (ETRM) with a status of VALID and is available in both release 12.1.1 and 12.2.2. The view was designed specifically to support the Contact Center Search window, a component of the Customer Care agent-facing application. Its purpose is to expose a unified, denormalized result set that combines Customer Party, Contact Party, and Relationship Party information in a single row structure, so the search UI can present matching parties and their associated contacts and relationships without repeated round trips to the database.

Because the view flattens the deeply normalized TCA (Trading Community Architecture) model into a search-friendly projection, it is primarily consumed by the Customer Care application layer rather than by end-user reporting tools. Nevertheless, it is frequently referenced in custom extensions, diagnostic queries, and integration scripts that need the same party search semantics used by Contact Center. The row identifier (ROWID) exposed by the view allows the framework to navigate back to specific underlying records where required.

Underlying Base Objects

According to the documented view metadata, CSC_HZ_PARTIES_SRCH_REL_V is defined over the following base objects: HZ_PARTIES, HZ_RELATIONSHIPS, AR_LOOKUPS, and FND_TERRITORIES_TL. In the ETRM these appear as synonyms or views resolved within the APPS schema, with HZ_PARTIES and HZ_RELATIONSHIPS being the principal TCA tables, AR_LOOKUPS supplying lookup decoding, and FND_TERRITORIES_TL providing territory and country information.

• HZ_PARTIES — the central TCA registry table holding party numbers, party names, party types, person and organization attributes, addresses, and the ATTRIBUTE1 through ATTRIBUTE30 flex columns.
• HZ_RELATIONSHIPS — the relationship graph table that links one party to another, enabling the view to surface relationship party data alongside customer and contact parties.
• AR_LOOKUPS — supplied as a view, used to decode lookup-coded values such as party status into displayable meanings.
• FND_TERRITORIES_TL — the translated territories table, used to resolve country and territory codes into descriptive values.

The view text performs the joins between these objects and aliases every HZ_PARTIES attribute column with an OBJ_ prefix (for example, OBJ_ATTRIBUTE2) to avoid column-name collisions when relationship or contact columns are added by the outer query layers.

Key Columns

The projection exposes a broad set of party attributes. Core identity columns include ROWID, PARTY_ID, PARTY_NUMBER, PARTY_NAME, PARTY_TYPE, and LAST_UPDATE_DATE. Person-specific columns are exposed with an OBJ_PERSON_ prefix and include OBJ_PERSON_PRE_NAME_ADJUNCT, OBJ_PERSON_FIRST_NAME, OBJ_PERSON_MIDDLE_NAME, OBJ_PERSON_LAST_NAME, OBJ_PERSON_NAME_SUFFIX, and OBJ_PERSON_TITLE. Address attributes are combined into a single concatenated OBJ_ADDRESS column (ADDRESS1 through ADDRESS4 joined by spaces) while the individual OBJ_ADDRESS1 through OBJ_ADDRESS4 columns are retained for structured access. Additional location columns include OBJ_CITY, OBJ_STATE, OBJ_PROVINCE, OBJ_POSTAL_CODE, OBJ_COUNTRY, and OBJ_COUNTY. Contact channel attributes include OBJ_URL and OBJ_EMAIL_ADDRESS, and OBJ_STATUS carries the decoded or raw party status. Phonetic search support is provided through OBJ_ORGANIZATION_NAME_PHONETIC and OBJ_PERSON_FIRST_NAME_PHONETIC, which underpin fuzzy name matching in the Contact Center Search window. The full ATTRIBUTE1 through ATTRIBUTE24 (and beyond, per the view text) DFF columns are aliased with the OBJ_ prefix, preserving descriptive flexfield context for downstream display.

Common Use Cases and Queries

The most common use is replicating or diagnosing the Contact Center Search results. A typical query filters on party name or number to locate a party and its related parties:

• Locating a customer party by number: SELECT party_id, party_number, party_name, obj_status FROM csc_hz_parties_srch_rel_v WHERE party_number = :p_number;
• Searching by email or URL for contact parties: SELECT party_id, obj_person_first_name, obj_person_last_name, obj_email_address FROM csc_hz_parties_srch_rel_v WHERE obj_email_address = :p_email;
• Phonetic-style name lookups using the phonetic columns when exact spelling is unknown.
• Extracting a concatenated address for display or letter generation via OBJ_ADDRESS.
• Feeding integration or data-migration routines that must resolve relationship parties in the same shape the Customer Care application expects.

Because the view is owned by APPS and marked VALID, custom SQL should reference it as APPS.CSC_HZ_PARTIES_SRCH_REL_V, and consumers should treat it as a read-only projection. Performance depends on the underlying HZ_PARTIES and HZ_RELATIONSHIPS indexes, so predicates on PARTY_ID, PARTY_NUMBER, and PARTY_NAME are recommended.