Search Results acct_site

Overview

APPS.PA_CUSTOMER_CONTACT_NAMES_V is a reporting view in the Oracle E-Business Suite Projects (PA) module. It exposes a denormalized, presentation-ready list of customer contact names drawn from the Oracle Trading Community Architecture (TCA) / Oracle Receivables (HZ) data model. The view's purpose is to present the concatenated full name of each contact party associated with a customer account role, together with the role's job title, responsibility type, and the account and account-site identifiers that anchor the relationship. It is a "names" view in the traditional EBS sense: a lightweight, read-only projection intended for list-of-values, reporting, and integration consumers rather than for transactional updates.

Because the view queries the TCA party and relationship tables directly, it allows Projects-side and custom reporting to resolve contact identities without navigating the full HZ relationship model. The presence of the ACCT_SITE reference in the query (via HZ_CUST_ACCT_SITES) is significant to any user searching on "acct_site": the view deliberately handles both account-level contacts (where CUST_ACCT_SITE_ID IS NULL) and site-level contacts (where CUST_ACCT_SITE_ID joins to a valid site record).

Underlying Base Objects

The view is defined over six TCA base tables, all exposed to APPS through synonyms:

• HZ_PARTIES — supplies the contact person's first and last name through PERSON_FIRST_NAME and PERSON_LAST_NAME.
• HZ_CUST_ACCOUNT_ROLES — the central driver table, holding the contact role, CUST_ACCOUNT_ID, CUST_ACCT_SITE_ID, and role state.
• HZ_RELATIONSHIPS — links the contact party to the related organization party, filtered to SUBJECT_TYPE = 'PERSON' with subject and object tables of HZ_PARTIES.
• HZ_ORG_CONTACTS — provides the contact's JOB_TITLE through the party relationship.
• HZ_ROLE_RESPONSIBILITY — supplies the RESPONSIBILITY_TYPE associated with the customer account role.
• HZ_CUST_ACCT_SITES — joined in the second half of the UNION ALL to resolve site-level contacts.

The view is the UNION ALL of two structurally similar queries: one returning account-level contacts (site ID null) and one returning site-level contacts (site ID populated). Both halves are filtered to active roles via NVL(CURRENT_ROLE_STATE,'A') = 'A' and to ROLE_TYPE = 'CONTACT'. The final ORDER BY 1 sorts on the concatenated name column.

Key Columns

• Contact Name — SUBSTRB(PERSON_LAST_NAME,1,50)||', '||SUBSTRB(PERSON_FIRST_NAME,1,40), i.e., "Last, First".
• JOB_TITLE — the contact's title from HZ_ORG_CONTACTS.
• CUST_ACCOUNT_ROLE_ID — unique identifier of the customer account role row.
• CUST_ACCOUNT_ID — the customer account (party account) the role belongs to.
• RESPONSIBILITY_TYPE — the role responsibility classification from HZ_ROLE_RESPONSIBILITY.
• CUST_ACCT_SITE_ID — the account site identifier; NULL for account-level roles, populated for site-level roles.

Common Use Cases and Queries

The view is typically used to populate contact pick lists and to report the contacts attached to a customer or to a specific account site. A representative query retrieving contacts for a given account is:

• SELECT contact_name, job_title, cust_account_id, cust_acct_site_id FROM ap.pa_customer_contact_names_v WHERE cust_account_id = :p_account_id ORDER BY 1;

To isolate contacts bound to a particular site (the "acct_site" scenario), filter on the populated site identifier:

• SELECT contact_name, job_title FROM ap.pa_customer_contact_names_v WHERE cust_acct_site_id = :p_site_id ORDER BY 1;

Conversely, account-level contacts only are returned with WHERE cust_acct_site_id IS NULL. Because the view applies DISTINCT to each UNION branch, callers should not expect duplicate role rows to survive; conversely, callers requiring role IDs for update operations must join back to HZ_CUST_ACCOUNT_ROLES directly, since this view is read-only and truncates name fields to 50/40 bytes via SUBSTRB.