Results for “gms_phone_contacts_v”

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

Overview

GMS_PHONE_CONTACTS_V is a read-only database view owned by the APPS schema and shipped as part of the Oracle Grants Accounting (GMS) product family. It exposes phone, telex, and related contact point information for customers that are linked to grant-funded activities, presenting a denormalized, reporting-friendly projection of communication data held in the Oracle Receivables and Oracle Trading Community Architecture (TCA) model. The view carries a VALID status in both release 12.1.1 and 12.2.2 and is classified in the ETRM under GMS - Grants Accounting.

Its practical role is to support inquiry screens, grant management reports, and integration interfaces that need a customer's telephone contact details without navigating the full TCA contact point structure. Because the view already joins the customer account role, the lookup meaning, and the contact point record, it is well suited to concurrent program data sources, Oracle Reports, BI Publisher datasets, and lightweight PL/SQL lookups.

Underlying Base Objects

The ETRM 12.2.2 metadata documents three referenced base objects: AR_LOOKUPS (VIEW), HZ_CONTACT_POINTS (SYNONYM), and HZ_CUST_ACCOUNT_ROLES (SYNONYM). The view text joins these as follows:

• HZ_CONTACT_POINTS CONT_POINT — the primary source of phone and telex records. Only records whose OWNER_TABLE_NAME is 'HZ_PARTIES' are eligible, and contact point types of 'EDI', 'EMAIL', and 'WEB' are explicitly excluded.
• HZ_CUST_ACCOUNT_ROLES CAR — matched on CAR.PARTY_ID = CONT_POINT.OWNER_TABLE_ID, binding the contact point to a specific customer account role and hence to a CUST_ACCOUNT_ID.
• AR_LOOKUPS LOOK — joined on LOOK.LOOKUP_CODE equal to NVL(CONT_POINT.PHONE_LINE_TYPE, CONT_POINT.CONTACT_POINT_TYPE), restricted to LOOKUP_TYPE = 'COMMUNICATION_TYPE'. This provides the human-readable phone type meaning.

The join is effectively an inner join across all three objects, so rows appear only where a communication type lookup and a customer account role both exist.

Key Columns

• PHONE_ID — maps to CONT_POINT.CONTACT_POINT_ID; the unique identifier of the underlying contact point record.
• PHONE_NUMBER — a DECODE expression returning TELEX_NUMBER when the contact point type is 'TLX', otherwise PHONE_NUMBER.
• STATUS — the contact point status flag from HZ_CONTACT_POINTS.
• PHONE_TYPE — NVL(PHONE_LINE_TYPE, CONTACT_POINT_TYPE), the effective communication classification.
• PHONE_TYPE_MEANING — the lookup MEANING for that classification, e.g. Business, Mobile, Fax.
• CUSTOMER_ID — CAR.CUST_ACCOUNT_ID, the customer account owning the role.
• CONTACT_ID — CAR.CUST_ACCOUNT_ROLE_ID, identifying the specific account role.
• AREA_CODE, EXTENSION, PRIMARY_FLAG — the dialing attributes and the primary-contact indicator.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN — standard audit columns; ROW_ID is returned as NULL.

Common Use Cases and Queries

A frequent requirement is listing all phone contacts for a customer account, respecting the primary flag:

• SELECT phone_number, phone_type_meaning, area_code, extension FROM gms_phone_contacts_v WHERE customer_id = :p_cust_account_id AND status = 'A' ORDER BY primary_flag DESC;

For jurisdiction-wide grant reporting, the view can be joined to award or customer tables on CUSTOMER_ID:

• SELECT c.customer_id, c.phone_number, c.phone_type_meaning FROM gms_phone_contacts_v c, hz_cust_accounts a WHERE c.customer_id = a.cust_account_id AND a.status = 'A';

Integration and data-quality checks commonly filter on phone type or missing values:

• SELECT contact_id, phone_number FROM gms_phone_contacts_v WHERE phone_type_meaning = 'Mobile' AND phone_number IS NULL;

Because the view filters out EDI, EMAIL, and WEB contact points, it should not be used for email or electronic address retrieval; those must be sourced directly from HZ_CONTACT_POINTS. Queries benefit from indexed access on CUST_ACCOUNT_ID and CONTACT_ID, and the view is generally treated as read-only, with all maintenance performed against the underlying TCA tables.