Search Results hcp_em

Overview

APPS.CSI_PARTY_CONTACT_DETAILS_V is a reporting and integration view in Oracle E-Business Suite (EBS) 12.1.1 and 12.2.2 that consolidates party, contact, relationship, telephone, and email information associated with instances tracked in the Converged Services Infrastructure (CSI). It joins the CSI instance-party relationship model to the Trading Community Architecture (TCA) party model (HZ_PARTIES, HZ_RELATIONSHIPS, HZ_CONTACT_POINTS) and to Oracle Contacts/Resources and Phone structures (JTF_RS_GROUPS_VL, JTF_RS_TEAMS_VL, PER_ALL_PEOPLE_F, PER_PHONES).

The view provides a single denormalized row per instance-party relationship, so that a consumer can retrieve the linked party name and number, the relationship type, contact flags, effective dates, and formatted work/home phone numbers plus an email address without writing custom joins. The presence of the column alias HCP_EM.EMAIL_ADDRESS is what frequently leads users searching for "hcp_em" to this view — the alias refers to the joined HZ_CONTACT_POINTS row filtered for the email contact point type.

Underlying Base Objects

The documented base objects are: CSI_IPA_RELATION_TYPES (synonym), CSI_I_PARTIES (synonym), CSI_LOOKUPS (view), FND_GLOBAL (package), HZ_CONTACT_POINTS (synonym), HZ_PARTIES (synonym), HZ_RELATIONSHIPS (synonym), JTF_RS_GROUPS_VL (view), JTF_RS_TEAMS_VL (view), PER_ALL_PEOPLE_F (synonym), PER_PHONES (synonym), PO_VENDOR_CONTACTS (view), and PO_VENDOR_SITES_ALL (view).

• CSI_I_PARTIES (CIP) — the driving table; supplies instance/party relationship attributes (contact flag, preferred flag, primary flag, active dates, descriptive flexfield attributes, and OVN).
• CSI_IPA_RELATION_TYPES / CSI_LOOKUPS (CIR, CL) — supply the relationship name/description and the decoded meaning for PARTY_SOURCE_TABLE.
• HZ_PARTIES (HP) — supplies PARTY_NAME, PARTY_NUMBER, and address attributes (ADDRESS1–ADDRESS4, CITY).
• HZ_RELATIONSHIPS — links the party to a related party context used for contact resolution.
• HZ_CONTACT_POINTS (HCP_WP, HCP_HP, HCP_EM) — joined three times to extract work phone, home phone, and email contact points respectively.
• PER_PHONES, PER_ALL_PEOPLE_F, JTF_RS_GROUPS_VL, JTF_RS_TEAMS_VL — support employee/resource-derived contacts and group/team lookups.
• PO_VENDOR_CONTACTS, PO_VENDOR_SITES_ALL — support supplier-side contacts and sites.
• FND_GLOBAL — supplies session context (e.g., user, responsibility, org) for multi-org and security filtering.

Key Columns

• INSTANCE_PARTY_ID / INSTANCE_ID / PARTY_ID / CONTACT_IP_ID — core keys identifying the instance, the related party, and the contact instance-party record.
• PARTY_SOURCE_TABLE / PARTY_SOURCE_MEANING — indicate the origin of the party record (e.g., customer, supplier, employee) and its decoded lookup meaning.
• RELATIONSHIP_TYPE_CODE / RELATIONSHIP_NAME / RELATIONSHIP_DESCR — the relationship classification between instance and party.
• CONTACT_FLAG / PREFERRED_FLAG / PRIMARY_FLAG — qualify the party as a contact and identify the preferred or primary contact.
• ACTIVE_START_DATE / ACTIVE_END_DATE — effective date range for the relationship.
• ATTRIBUTE1–ATTRIBUTE15 / CONTEXT — descriptive flexfield attributes on CSI_I_PARTIES.
• PARTY_NAME / PARTY_NUMBER — the TCA party identification.
• WORK_PHONE_NUMBER / HOME_PHONE_NUMBER — formatted concatenations (country code, area code in parentheses, number, extension with 'x') from HZ_CONTACT_POINTS phone records.
• EMAIL_ADDRESS — the email contact point address (the HCP_EM alias).
• ADDRESS1–ADDRESS4 / CITY — party location attributes from HZ_PARTIES.
• Audit columns — CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN, OBJECT_VERSION_NUMBER.

Common Use Cases and Queries

Typical uses include instance 360-degree contact reporting, integration extracts that push contact details to downstream systems, and troubleshooting that explains where a displayed email or phone originates. A basic query to return contacts for an instance:

SELECT instance_id,
       party_name,
       party_number,
       relationship_name,
       preferred_flag,
       primary_flag,
       work_phone_number,
       home_phone_number,
       email_address
  FROM apps.csi_party_contact_details_v
 WHERE instance_id = :p_instance_id
   AND contact_flag = 'Y';

Filtering on preferred contacts for a party name search:

SELECT party_name, party_number, email_address, work_phone_number
  FROM apps.csi_party_contact_details_v
 WHERE preferred_flag = 'Y'
   AND UPPER(party_name) LIKE UPPER(:p_name || '%');

To trace the source of an email used in a report, query PARTY_SOURCE_TABLE and PARTY_SOURCE_MEANING together with EMAIL_ADDRESS. Because the view is read-only and driven from CSI_I_PARTIES, it is safe to use in concurrent-program extracts and in OAF/ADF or BI Publisher queries; apply effective-date predicates against ACTIVE_START_DATE and ACTIVE_END_DATE to restrict results to currently valid relationships.