Search Results sub_job_title




Overview
Explore in AI Chat

Overview

CSC_HZ_PARTY_RELATIONSHIPS_V is a reporting view owned by the APPS schema in Oracle E-Business Suite. It is delivered as part of the CSC – Customer Care product family and is documented as VALID in both the 12.1.1 and 12.2.2 releases referenced by the ETRM repository. The view is defined over the Trading Community Architecture (TCA) relationship model and presents party-to-party relationship details in a denormalized, query-friendly form. Its stated purpose is to expose records from HZ_RELATIONSHIPS together with the identifying attributes of the subject and object parties involved in each relationship.

The view is primarily consumed by Customer Care inquiry screens, service request and interaction reporting, and downstream integrations that need to resolve how one party relates to another. Because it resolves the subject and object parties to their party names, party types, and party numbers in a single pass, it removes the need for callers to join HZ_PARTIES twice. It also decodes the relationship code into a user-facing meaning through AR_LOOKUPS, which makes the view suitable for direct presentation in reports and BI Publisher extracts. The user search term sub_party_name maps to the SUB_PARTY_NAME column, which carries the name of the subject party in the relationship.

Underlying Base Objects

The view text is constructed from five base objects. The documented referenced objects are HZ_PARTIES, HZ_RELATIONSHIPS, HZ_ORG_CONTACTS, and AR_LOOKUPS; HZ_PARTIES is joined three times through aliases to supply the primary party, the subject party, and the object party. HZ_RELATIONSHIPS supplies the relationship record itself, including identifiers, direction, status, and effective dating. HZ_ORG_CONTACTS is joined on PARTY_RELATIONSHIP_ID to supply the job title where the related party is an organization contact. AR_LOOKUPS is an Oracle Applications lookup view filtered to LOOKUP_TYPE = 'PARTY_RELATIONS_TYPE', and it supplies the decoded relationship meaning.

Joins between HZ_PARTIES and HZ_RELATIONSHIPS are outer on the primary party (PARTY.PARTY_ID(+) = PARTY_REL.PARTY_ID), while the subject and object party joins are inner and are driven by SUBJECT_ID and OBJECT_ID respectively. The ORG_CONT and AR_LOOKUPS joins are inner, so only relationships with a resolvable lookup code and an organization contact record survive. Notably, the effective-date predicates in the original definition are commented out, meaning expired relationships are not filtered by default.

Key Columns

  • PARTY_ID / PARTY_NUMBER / ROW_ID — identifier, human-readable number, and row identifier for the primary party.
  • PARTY_RELATIONSHIP_ID — the relationship identifier from HZ_RELATIONSHIPS; also exposed as RELATIONSHIP_ID in the view text.
  • SUBJECT_ID and SUB_PARTY_NAME — the subject party's identifier and name; SUB_PARTY_NAME is the column surfaced by the term sub_party_name.
  • OBJECT_ID and OBJECT_NAME — the object party's identifier and name.
  • SUB_PARTY_TYPE / OBJ_PARTY_TYPE — party type (for example, PERSON or ORGANIZATION) for each side of the relationship.
  • RELATION / RELATIONSHIP_CODE / RELATIONSHIP_MEANING — the relationship type code and its decoded meaning from AR_LOOKUPS.
  • DIRECTIONAL_FLAG — indicates whether the relationship is directional or reciprocal.
  • SUB_JOB_TITLE — job title sourced from HZ_ORG_CONTACTS.
  • EFFECTIVE_START_DATE / EFFECTIVE_END_DATE / STATUS — relationship dating and status.
  • OBJ_LAST_UPDATE_DATE and COMMENTS — audit and free-text annotation.

Common Use Cases and Queries

Typical scenarios include listing all relationships for a given party, identifying contacts attached to an organization, and producing relationship reports that require both party names and decoded relationship meanings.

  • Retrieve relationships where a given party is the subject, returning the related object party name and relationship meaning.
  • Produce a contact listing for an organization account using SUB_JOB_TITLE and OBJECT_NAME.
  • Feed integration extracts that require party numbers and relationship codes without additional TCA joins.

Sample query:

SELECT party_id, party_number, sub_party_name, object_name, relation, relationship_meaning, status, effective_start_date, effective_end_date FROM apps.csc_hz_party_relationships_v WHERE sub_party_name = :party_name AND status = 'A' ORDER BY object_name;

Because the effective-date predicates are disabled in the view definition, callers that require only currently active relationships should add explicit date or status filters in the calling query.