Results for “csd_contact_details_v”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

CSD_CONTACT_DETAILS_V is a reporting view owned by the APPS schema in Oracle E-Business Suite 12.1.1 and 12.2.2, delivered as part of the CSD (Depot Repair) product family. The view presents the contact persons associated with a given party record, resolving the many-to-many relationship defined in the TCA (Trading Community Architecture) registry into a flat, denormalized result set. Its principal role is to expose contact name, telephone, and e-mail information for a party's designated contacts — most importantly the "contact of" relationship that links a person to the organization or party they represent — so that call-center, depot-repair, and service-request screens and reports do not have to traverse HZ_RELATIONSHIPS and HZ_CONTACT_POINTS directly.

The object is registered in ETRM with a status of VALID. In practice it is used wherever a depot-repair or order-entry function must print or display "contact name, phone, e-mail" for the owning party on a repair order, estimate, or RMA document.

Underlying Base Objects

The ETRM metadata documents three referenced base objects, all reached through public synonyms: HZ_PARTIES, HZ_RELATIONSHIPS, and HZ_CONTACT_POINTS. The view text joins these as follows:

• HZ_PARTIES (aliased HP1) — the object side of the relationship; supplies the contact's PARTY_ID, which is exposed as CONTACT_PARTY_ID and CONTACT_PARTY_NUMBER.
• HZ_PARTIES (aliased HP) — the subject side of the relationship; supplies PARTY_ID and constrains PARTY_TYPE = 'PERSON', so the view returns person contacts rather than organizations.
• HZ_RELATIONSHIPS (aliased HPR) — the bridge table defining the association. The join requires SUBJECT_TABLE_NAME = 'HZ_PARTIES', OBJECT_TABLE_NAME = 'HZ_PARTIES', DIRECTIONAL_FLAG = 'F', and RELATIONSHIP_CODE = 'CONTACT_OF'. This is the critical filter: only relationships explicitly coded as CONTACT_OF are returned.
• HZ_CONTACT_POINTS (HC1, HC2, HC3) — outer-joined three times to retrieve PHONE, WEB, and EMAIL contact points. The phone, WEB, and e-mail accesses are all written with the Oracle outer-join operator (+), so a contact missing any one of these attributes still returns a row with the remaining values.

Because the phone, WEB, and e-mail contact points can be multi-valued in the underlying tables, the view can produce multiple rows per contact party, and consumers should expect and account for that duplication.

Key Columns

According to the documented column list, the view projects:

• ROW_ID — the ROWID of the HZ_PARTIES row (HP1) that represents the contact; useful for direct-row addressing but not a stable identifier.
• PARTY_ID — the party identifier of the person contact (HP1.PARTY_ID).
• CONTACT_PARTY_ID — the party identifier of the related contact as resolved through HZ_RELATIONSHIPS (HP.PARTY_ID); distinct from PARTY_ID in the join context.
• CONTACT_PARTY_NUMBER — the human-readable party number for the contact.
• FIRST_NAME / LAST_NAME — mapped from PERSON_FIRST_NAME and PERSON_LAST_NAME on HP; the documented column names are FIRST_NAME and LAST_NAME.
• PHONE_AREA_CODE, PHONE_NUMBER — from the PHONE-type contact point (HC1).
• EMAIL_ADDRESS — from the EMAIL-type contact point (HC3). Note that the view text selects HC2.EMAIL_ADDRESS while the predicate on HC2 filters CONTACT_POINT_TYPE = 'WEB'; the metadata is reproduced here as documented.

Common Use Cases and Queries

The view is typically consumed to answer "who is the contact for this party, and how do we reach them?" in depot-repair and service workflows.

Retrieve all contacts with their telephone and e-mail for a specific party:

• SELECT party_id, contact_party_id, contact_party_number, first_name, last_name, phone_area_code, phone_number, email_address FROM apps.csd_contact_details_v WHERE party_id = :p_party_id;

Locate a contact by name:

• SELECT * FROM apps.csd_contact_details_v WHERE UPPER(last_name) = UPPER(:p_last_name);

Because of the multi-valued contact points, a query that must return exactly one row per contact should aggregate — for example, using MIN or MAX on the phone and e-mail columns grouped by PARTY_ID and CONTACT_PARTY_ID. The view is a read-only reporting object built entirely on TCA synonyms; no DML should be issued against it. For best performance, ensure that the underlying HZ_PARTIES, HZ_RELATIONSHIPS, and HZ_CONTACT_POINTS indexes on PARTY_ID, SUBJECT_ID, OBJECT_ID, OWNER_TABLE_ID, and CONTACT_POINT_TYPE are present and analyzed, since the outer joins and multi-column predicates make the plan sensitive to statistics.