Search Results csc_hz_per_profiles_v




Overview
Explore in AI Chat

Overview

The view APPS.CSC_HZ_PER_PROFILES_V is a Customer Care (CSC) module database object belonging to the Oracle E-Business Suite (EBS) 12.1.1 and 12.2.2 releases. It consolidates person-level profile data with organizational contact attributes and party relationship context, exposing a single denormalized result set that describes a party in detail. The view is classified as VALID within the APPS schema and serves as a reporting and integration access point for Customer Care functionality where person profiles and their associated organizational roles must be presented together.

Because the object is a view rather than a base table, it carries no storage of its own; all data is derived at query time from its underlying sources. The ETRM documentation explicitly notes that it is a view of HZ_PERSON_PROFILES and HZ_PARTY_RELATIONSHIPS, with detailed information about a party available by referring to the base tables. This makes the view a convenient abstraction over the TCA (Trading Community Architecture) model, which normalizes parties, persons, organizations, and their relationships into separate entities.

Underlying Base Objects

The documented base objects referenced by the view are HZ_ORG_CONTACTS (synonym), HZ_PERSON_PROFILES (synonym), and HZ_RELATIONSHIPS (synonym). The ETRM view text joins HZ_PERSON_PROFILES PERSON_PROFILE, HZ_ORG_CONTACTS ORG_CONT, and HZ_PARTY_RELATIONSHIPS PARTY_REL. The join condition links ORG_CONT.PARTY_RELATIONSHIP_ID to PARTY_REL.PARTY_RELATIONSHIP_ID, and matches the person profile party to either the subject or object side of the relationship (PARTY_REL.SUBJECT_ID = PERSON_PROFILE.PARTY_ID OR PARTY_REL.OBJECT_ID = PERSON_PROFILE.PARTY_ID).

The view is defined as a UNION. The first branch returns persons who participate in a party relationship with organizational contact detail. The second branch returns persons with no relationship record at all — using NOT EXISTS subqueries against HZ_PARTY_RELATIONSHIPS on both the subject and object sides — and pads the organizational contact columns with TO_NUMBER(NULL), TO_DATE(NULL), and NULL. This guarantees that every effective person profile appears at least once, whether or not an organizational contact relationship exists. The effective-dating predicate (EFFECTIVE_END_DATE IS NULL OR SYSDATE BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE) restricts the person profile rows to currently effective records.

Key Columns

Common Use Cases and Queries

Typical use cases include Customer Care agent screens, party 360 reporting, contact-role analysis, and integration extracts that require person demographics alongside organizational contact attributes. A simple retrieval by party follows:

  • SELECT party_id, known_as, person_title, org_contact_id, job_title, department FROM csc_hz_per_profiles_v WHERE party_id = :p_party_id;
  • SELECT party_id, known_as FROM csc_hz_per_profiles_v WHERE org_contact_id IS NULL; — isolates persons without an organizational contact relationship, corresponding to the second UNION branch.
  • SELECT party_id, known_as, job_title, decision_maker_flag FROM csc_hz_per_profiles_v WHERE decision_maker_flag = 'Y'; — identifies decision makers for account planning.

Queries should filter on PARTY_ID or ORG_CONTACT_ID to avoid full scans, since the UNION and the NOT EXISTS subqueries against HZ_PARTY_RELATIONSHIPS can be costly on large TCA volumes. Users searching on party_rel should note that the view does not expose PARTY_RELATIONSHIP_ID as a projected column; it is used only as a join key, so queries requiring relationship identifiers must access HZ_PARTY_RELATIONSHIPS directly.