Search Results relationship_start_date

Overview

CSI_HZPTY_CONTACTS_V is an APPS-owned database view in the Oracle E-Business Suite Release 12.1.1 and 12.2.2 environments. It resides in the CSI (Install Base) product family and is described in the ETRM repository as a "Customer Party Contacts View for the record groups." Published with a VALID status, the view consolidates party, party relationship, and organization contact information into a single denormalized result set, allowing reports and integration components to retrieve contact data without directly navigating the underlying Trading Community Architecture (TCA) tables. Because its object type is a view rather than a table, it stores no data of its own; it derives all output at runtime from its joined base objects.

Underlying Base Objects

The documented view metadata lists four referenced base objects: AR_LOOKUPS (view), HZ_ORG_CONTACTS (synonym), HZ_PARTIES (synonym), and HZ_RELATIONSHIPS (synonym). The view text joins HZ_PARTIES (aliased HP) to HZ_PARTY_RELATIONSHIPS (HPR) on the relationship subject identifier, then joins HZ_ORG_CONTACTS (HOC) to the party relationship identifier. Two instances of AR_LOOKUPS (AL1, AL2) are outer-joined to resolve lookup codes into user-facing meanings — AL1 for the party type and AL2 for the party relationship type. The join to HZ_ORG_CONTACTS is an inner join, so only relationships that also exist as organization contacts are surfaced. The lookup joins use the Oracle outer-join operator (+), so rows are retained even when a matching lookup meaning is absent.

Key Columns

• PARTY_NAME, PARTY_NUMBER, PARTY_ID, PARTY_TYPE — identity attributes sourced from HZ_PARTIES. PARTY_TYPE distinguishes persons from organizations.
• MEANING — the decoded lookup meaning for PARTY_TYPE, resolved through AR_LOOKUPS (AL1) where LOOKUP_TYPE = 'PARTY_TYPE'.
• SORT_ORDER — a DECODE expression that maps party type 'PERSON' to the literal 'AAAA'; other values pass through unchanged, enabling persons to sort ahead of organizations.
• ORIGINAL_PARTY_ID — HPR.OBJECT_ID, the object side of the relationship.
• RELATIONSHIP_TYPE and RELATIONSHIP_MEANING — the relationship type code from HZ_PARTY_RELATIONSHIPS and its decoded meaning from AR_LOOKUPS (AL2) where LOOKUP_TYPE = 'PARTY_RELATIONS_TYPE'. This is the column that satisfies the common search term relationship_meaning.
• ORG_CONTACT_STATUS — status of the corresponding organization contact record.
• RELATIONSHIP_STATUS, RELATIONSHIP_START_DATE, RELATIONSHIP_END_DATE — lifecycle attributes of the party relationship.
• ADDRESS — a concatenation of HP.ADDRESS1 through ADDRESS4, CITY, STATE, POSTAL_CODE, and COUNTRY into a single display string.

Common Use Cases and Queries

This view is typically consumed by concurrent programs, record group definitions, and custom reports in the Install Base module that require a flattened contact listing. Typical queries filter on relationship status or party type to produce active contact lists:

• Listing active contacts with decoded relationship meanings: SELECT PARTY_NAME, PARTY_NUMBER, RELATIONSHIP_MEANING, ORG_CONTACT_STATUS FROM CSI_HZPTY_CONTACTS_V WHERE RELATIONSHIP_STATUS = 'A' ORDER BY SORT_ORDER, PARTY_NAME;
• Restricting to persons: SELECT PARTY_ID, PARTY_NAME, RELATIONSHIP_TYPE FROM CSI_HZPTY_CONTACTS_V WHERE PARTY_TYPE = 'PERSON';
• Resolving a specific relationship: SELECT PARTY_NAME, RELATIONSHIP_MEANING, RELATIONSHIP_START_DATE, RELATIONSHIP_END_DATE FROM CSI_HZPTY_CONTACTS_V WHERE ORIGINAL_PARTY_ID = :party_id;

Because AR_LOOKUPS is read through outer joins, RELATIONSHIP_MEANING may be null where no lookup row exists; report logic should tolerate this. All queries execute under APPS or a synonym with appropriate TCA read privileges.