Search Results department_type




Overview
Explore in AI Chat

Overview

AST_RELATIONSHIP_DETAILS_V is a read-only database view owned by the APPS schema in Oracle E-Business Suite, defined within the AST (TeleSales) product family. It consolidates contact and relationship information for parties recorded in Oracle's Trading Community Architecture (TCA) tables, presenting a denormalized, user-friendly projection of organization contacts, their primary role, job title, department, primary address, primary phone, and primary e-mail. The view is available in both EBS 12.1.1 and 12.2.2, where it remains VALID.

Its principal function is to support TeleSales and related CRM reporting, where agents and downstream integrations require a single row per party contact relationship that resolves role, title, and department lookup meanings without requiring the caller to join multiple TCA and lookup tables. Because it joins HZ_RELATIONSHIPS with a directional filter of 'F' and constrains contact roles and contact points to their primary records, the view yields the principal contact record for each relationship rather than the full contact history. This makes it well suited for list-of-values queries, dashboards, and outbound integration feeds. The user search term "primary_role" corresponds directly to the PRIMARY_ROLE column, which is derived from the contact role lookup.

Underlying Base Objects

The view is defined over the following documented base objects: HZ_PARTIES, HZ_RELATIONSHIPS, HZ_ORG_CONTACTS, HZ_ORG_CONTACT_ROLES, HZ_CONTACT_POINTS, FND_TERRITORIES_TL, and AR_LOOKUPS. Except for AR_LOOKUPS (a view) all are exposed in the view definition as synonyms resolving to their TCA base tables.

  • HZ_RELATIONSHIPS supplies REL.PARTY_ID and the RELATIONSHIP_ID used to anchor org contacts; the DIRECTIONAL_FLAG = 'F' predicate restricts output to forward relationships.
  • HZ_ORG_CONTACTS links to relationships via ORG_CONTACT.PARTY_RELATIONSHIP_ID = REL.RELATIONSHIP_ID and contributes JOB_TITLE and DEPARTMENT.
  • HZ_ORG_CONTACT_ROLES is outer-joined on ORG_CONTACT_ID with PRIMARY_FLAG = 'Y' to yield the single primary role per contact.
  • HZ_PARTIES provides party name, address components, and e-mail address.
  • HZ_CONTACT_POINTS is outer-joined on the party for primary phone contact points.
  • FND_TERRITORIES_TL is outer-joined on country code and language to supply territory short name.
  • AR_LOOKUPS appears three times to decode contact role type, responsibility (job title), and department code into meanings.

Key Columns

Common Use Cases and Queries

Typical usage includes resolving the primary contact for an organization in TeleSales screens, building contact directories, and exporting party contact data for campaign or integration purposes. Because PRIMARY_ROLE is a decoded meaning, filtering should use the lookup meaning rather than the underlying code.

  • Retrieve all contact details for a specific party:

SELECT party_id, relationship_name, primary_role, job_title, department, primary_phone_number, primary_email FROM apps.ast_relationship_details_v WHERE party_id = :p_party_id;

  • List parties by primary role:

SELECT relationship_name, primary_role, primary_phone_number FROM apps.ast_relationship_details_v WHERE primary_role = 'Decision Maker';

  • Joining to other views to enrich campaign or service data, using PARTY_ID as the correlation key.

Note that the view performs primary-flag filtering internally, so consumers receive one principal role and one primary phone per relationship without additional DISTINCT or ranking logic.