Search Results email_last_update_date




Overview
Explore in AI Chat

Overview

CSC_GS_CONTACTS_V is a query-only database view owned by the APPS schema in Oracle E-Business Suite, validated and shipped as part of the CSC (Customer Care) product family. Its purpose, as documented, is to expose only the contact details of customers by returning phone and e-mail contact points recorded against trading community parties. In practice it serves as a simplified, presentation-oriented layer over the Oracle Trading Community Architecture (TCA) contact model, hiding the complexity of the underlying HZ_CONTACT_POINTS entity and pre-joining telephone and e-mail records into a single denormalized row per party. The view is therefore relevant to reporting, diagnostics, and lightweight integration scenarios where a concise picture of a customer's phone and e-mail information is required without navigating the full TCA contact-point data model.

Underlying Base Objects

The ETRM metadata records a single referenced base object for this view: HZ_CONTACT_POINTS, accessed through a synonym. The view text confirms this dependency and reveals an important implementation detail: HZ_CONTACT_POINTS is referenced twice, using the aliases PH (phone) and EM (e-mail). The two instances are joined by a correlated outer-join condition on OWNER_TABLE_ID, so that each phone contact point is returned together with any e-mail contact point belonging to the same party. Both instances are filtered by OWNER_TABLE_NAME = 'HZ_PARTIES', restricting results to contact points owned by parties rather than by other TCA entities. The phone instance is further constrained to CONTACT_POINT_TYPE = 'PHONE', and the e-mail instance to CONTACT_POINT_TYPE = 'EMAIL' via an outer join, which means parties holding phone data but no e-mail data are still returned. Because the join is not guaranteed to be one-to-one, parties with multiple e-mail addresses may yield multiple rows per phone record.

Key Columns

The view exposes twelve columns. PARTY_ID carries the party identifier, sourced from PH.OWNER_TABLE_ID, and is the principal key for linking back to HZ_PARTIES and related TCA entities. CONTACT_POINT_ID is the identifier of the phone contact point, while EMAIL_ID holds the corresponding e-mail contact point identifier. PHONE_NUMBER, PHONE_COUNTRY_CODE, PHONE_AREA_CODE, and PHONE_EXTENSION together describe the telephone record; TELEPHONE_TYPE and PHONE_LINE_TYPE classify it (for example, by usage or line category). EMAIL_ADDRESS holds the e-mail address. LAST_UPDATE_DATE reflects the phone contact point's last modification, whereas EMAIL_LAST_UPDATE_DATE carries the equivalent timestamp for the e-mail contact point, allowing incremental extraction logic to distinguish changes between the two record types.

Common Use Cases and Queries

Typical uses include customer contact verification reports, outbound notification and campaign extracts, and data-quality checks where parties are missing e-mail addresses or telephone numbers. Because the view is a simple selection over contact points, it is well suited to concurrent-program extracts and embedded report queries. A representative query listing active contact details for a given party is:

  • SELECT party_id, phone_number, email_address FROM csc_gs_contacts_v WHERE party_id = :p_party_id;
  • SELECT party_id, phone_number, phone_area_code, email_address FROM csc_gs_contacts_v WHERE telephone_type = 'GEN';
  • SELECT v.party_id, v.phone_number, v.email_address FROM csc_gs_contacts_v v, hz_parties p WHERE v.party_id = p.party_id AND p.party_name LIKE :p_name;
  • SELECT party_id, COUNT(*) FROM csc_gs_contacts_v GROUP BY party_id HAVING COUNT(*) > 1;

Consumers should note two practical constraints. First, the view returns phone-centric rows; a party with no phone contact point will not appear, even if e-mail data exists. Second, the outer join can multiply rows where multiple e-mail addresses are recorded, so aggregate queries should account for this duplication. For authoritative, single-record contact resolution, callers should fall back to the HZ_CONTACT_POINTS base table or the TCA public APIs.