Search Results org_cont




Overview
Explore in AI Chat

Overview

PA_CUSTOMER_CONTACT_NAMES_V is a reporting view owned by the APPS schema in Oracle E-Business Suite, defined within the Projects (PA) product family. It exposes customer contact information drawn from the Oracle Trading Community Architecture (TCA) model, presenting a flattened, human-readable list of contacts associated with customer accounts and, where applicable, customer account sites. The view is defined as a UNION ALL of two branches that differ only in whether the customer account site identifier is null or populated, ensuring that both account-level and site-level contact relationships are returned with the correct address context.

The view is commonly used in Oracle Projects reporting and integration scenarios where project or contract personnel need to identify the named contacts associated with a customer account—typically for billing, correspondence, or interface purposes. Because the underlying data originates in TCA (HZ) tables rather than in Projects tables proper, the view acts as a bridge object that shields Projects-facing queries from the complexity of the TCA relationship and role model. The representation stored in the column set is a concatenated contact name (last name, comma, first name), a job title, role identifiers, customer identifiers, a usage/responsibility code, and an address identifier.

Underlying Base Objects

The documented base objects referenced by PA_CUSTOMER_CONTACT_NAMES_V are all TCA synonyms: HZ_CUST_ACCOUNT_ROLES, HZ_CUST_ACCT_SITES, HZ_ORG_CONTACTS, HZ_PARTIES, HZ_RELATIONSHIPS, and HZ_ROLE_RESPONSIBILITY. The join pattern is driven by HZ_CUST_ACCOUNT_ROLES, which holds the 'CONTACT' role type records. Each contact role is linked to HZ_ROLE_RESPONSIBILITY on CUST_ACCOUNT_ROLE_ID, supplying the responsibility (usage) type. The contact party itself is resolved through HZ_RELATIONSHIPS, which connects the role's party to the subject party identified by the relationship, and through HZ_ORG_CONTACTS, which supplies the contact's job title via PARTY_RELATIONSHIP_ID.

The HZ_PARTIES table provides the person's last and first names, while the person's identity is ensured by filters on SUBJECT_TYPE, OBJECT_TABLE_NAME, and SUBJECT_TABLE_NAME. HZ_CUST_ACCT_SITES participates in the second branch of the UNION ALL, where CUST_ACCT_SITE_ID is non-null, to attach a specific site address context. Only active contact roles are returned, since the predicate NVL(CURRENT_ROLE_STATE,'A') = 'A' excludes inactive roles.

Key Columns

  • CONTACT_NAME — A concatenated display name composed of SUBSTRB truncations of the last name (max 50) and first name (max 40), formatted as "Last, First". This is the primary user-facing attribute.
  • JOB_TITLE — The job title of the contact, sourced from HZ_ORG_CONTACTS.
  • CONTACT_ID — The customer account role identifier (CUST_ACCOUNT_ROLE_ID), uniquely identifying the contact's role record and used as the join key to role responsibility data.
  • CUSTOMER_ID — The customer account identifier (CUST_ACCOUNT_ID) to which the contact role belongs.
  • USAGE_CODE — The responsibility type (RESPONSIBILITY_TYPE) from HZ_ROLE_RESPONSIBILITY, indicating the business purpose of the contact, such as billing or shipping.
  • ADDRESS_ID — The customer account site identifier (CUST_ACCT_SITE_ID), populated in the site-level branch of the UNION and null for account-level contacts.

The DISTINCT keyword suppresses duplicate rows across the six-table join, and the ORDER BY 1 clause sorts the result set by the computed contact name.

Common Use Cases and Queries

The view is typically queried to obtain a lightweight contact picklist for a given customer or account site, or to feed interfaces and concurrent programs that require contact names and roles without navigating TCA directly. A representative query retrieving all contacts for a specific customer follows:

  • SELECT contact_name, job_title, usage_code, address_id FROM pa_customer_contact_names_v WHERE customer_id = :customer_id ORDER BY contact_name;
  • SELECT DISTINCT usage_code FROM pa_customer_contact_names_v WHERE customer_id = :customer_id;
  • SELECT contact_name, job_title FROM pa_customer_contact_names_v WHERE address_id = :site_id;

Because the view enforces the active-role predicate internally, callers do not need to reapply role-state filtering. Queries should still filter on CUSTOMER_ID or ADDRESS_ID to bound the result set, as the full view scans all active TCA contact roles. The view does not expose email or phone attributes, so deployments requiring full contact details must join back to HZ_CONTACT_POINTS or related TCA tables using the CONTACT_ID value.