Search Results hz_relationship




Overview
Explore in AI Chat

Overview

JTF_PARTY_ALL_CONTACTS_V is a CRM Foundation (JTF) view owned by the APPS schema in Oracle E-Business Suite 12.1.1 and 12.2.2. Its purpose is to present all contacts defined in the Trading Community Architecture (TCA) model, using the HZ_RELATIONSHIPS table as the driving table. The view exposes data for both the subject party and the object party of a relationship, effectively allowing a caller to resolve the "who is the contact of whom" question without navigating the full TCA relationship schema.

Because TCA models contacts as party-to-party relationships rather than as records stored in a dedicated contacts table, this view is the standard reporting and integration access point for contact data. It is widely referenced by CRM Foundation components and by downstream modules that require a flat, denormalized representation of contacts. The view is documented as not joining to HZ_ORG_CONTACTS, which distinguishes it from views that rely on the legacy organization-contact association. Applications requiring the org-contact linkage must obtain it separately.

Underlying Base Objects

Per the ETRM metadata, the view is defined over four referenced base objects, all accessed through synonyms in the APPS schema:

  • HZ_RELATIONSHIPS — the driving table, aliased as REL. It supplies the relationship identity, type, direction, start and end dates, and the subject and object identifiers.
  • HZ_PARTIES — joined twice, once as PARTY_SUBJECT (on REL.SUBJECT_ID = PARTY_SUBJECT.PARTY_ID) and once as PARTY_OBJECT (on PARTY_OBJECT.PARTY_ID = REL.OBJECT_ID). These joins supply the names, numbers, and person-level attributes.
  • HZ_RELATIONSHIP_TYPES — aliased as RELTYPE, joined to REL on relationship type.
  • HZ_CODE_ASSIGNMENTS — joined to the relationship type to constrain the result set to the relationship type group that represents contacts.

The view applies several restrictions in its WHERE clause: DIRECTIONAL_FLAG must equal 'F', both SUBJECT_TABLE_NAME and OBJECT_TABLE_NAME must equal 'HZ_PARTIES', and an EXISTS subquery requires a code assignment with CLASS_CATEGORY = 'RELATIONSHIP_TYPE_GROUP' and CLASS_CODE = 'PARTY_REL_GRP_CONTACTS'. This last predicate is the mechanism that filters the universe of relationships down to those classified as contacts, rather than permitting arbitrary party relationships to appear. Because the view is not joined to HZ_ORG_CONTACTS, it is relationship-centric; organization contact assignments maintained separately are not reflected here.

Key Columns

The view exposes identity and descriptive attributes for both sides of each contact relationship, together with the relationship metadata itself. Important columns include:

Common Use Cases and Queries

Typical scenarios include contact list reporting, CRM integration extracts, customer 360 views, and data migration validation where contact relationships must be enumerated. A common query retrieves all contacts associated with a given object party:

  • SELECT subject_party_id, subject_party_name, object_party_id, object_party_name, relationship_type, start_date, end_date FROM jtf_party_all_contacts_v WHERE object_party_id = :p_party_id;
  • SELECT subject_party_id, subject_party_name, subject_email_address FROM jtf_party_all_contacts_v WHERE subject_party_number = :p_party_number;
  • SELECT object_party_id, COUNT(subject_party_id) FROM jtf_party_all_contacts_v WHERE (end_date IS NULL OR end_date >= SYSDATE) GROUP BY object_party_id;

When the organization-contact relationship data is also required, the view must be supplemented with a separate join to HZ_ORG_CONTACTS, since the documented view definition deliberately omits that join.