Search Results igs_pe_contacts_v

Overview

IGS_PE_CONTACTS_V is a PL/SQL view owned by the APPS schema in the Oracle E-Business Suite 12.1.1 and 12.2.2 environments. It belongs to the IGS – Student System product family and exposes contact point information maintained against the Oracle Trading Community Architecture (TCA) contact model, extended with student-system-specific telephone, EDI, email, and web attributes. The view is a denormalized reporting and integration artifact: rather than requiring callers to join HZ_CONTACT_POINTS directly and resolve lookup meanings themselves, it presents the base contact point rows with decoded status, phone line type, and email format descriptions already in place.

Because the view preserves the primary key CONTACT_POINT_ID alongside the ORIG_SYSTEM_REFERENCE, OWNER_TABLE_NAME, and OWNER_TABLE_ID columns, it functions as a stable interface for downstream processes — including concurrent programs, inbound/outbound EDI interfaces, and third-party student information system integrations — that need to resolve a contact point back to its owning entity (person, organization, or student record). The view includes a WHERE clause filtering by REGISTRY_STATUS lookup and language, so result sets reflect the session language and exclude contact points with unrecognized status codes.

Underlying Base Objects

The view text is defined as a join over four objects:

• HZ_CONTACT_POINTS CP — the driving TCA table holding all contact point records.
• FND_LOOKUP_VALUES L1 — outer-joined on CP.STATUS to LOOKUP_CODE, restricted to LOOKUP_TYPE = 'REGISTRY_STATUS', LANGUAGE = USERENV('LANG'), VIEW_APPLICATION_ID = 222, and SECURITY_GROUP_ID = 0. Provides STATUS_MEANING.
• FND_LOOKUP_VALUES L2 — outer-joined to decode PHONE_LINE_TYPE into PHONE_LINE_TYPE_MEANING.
• FND_LOOKUP_VALUES L3 — outer-joined to decode EMAIL_FORMAT into EMAIL_FORMAT_MEANING.

All three lookup joins are outer (+) joins, so a contact point is never suppressed solely because its lookup value is missing. The ETRM 12.2.2 metadata documents no referenced base objects explicitly; the definition above is derived from the view text excerpt.

Key Columns

• CONTACT_POINT_ID — primary key identifying the contact point row.
• CONTACT_POINT_TYPE and STATUS / STATUS_MEANING — classify the record and give its decoded lifecycle state.
• OWNER_TABLE_NAME / OWNER_TABLE_ID / ORIG_SYSTEM_REFERENCE — locate the owning entity for the contact point.
• EDI_TP_HEADER_ID and EDI_ECE_TP_LOCATION_CODE — EDI trading partner and location mapping used in e-commerce transactions; this is the column the user searched for.
• PHONE_AREA_CODE, PHONE_COUNTRY_CODE, PHONE_NUMBER, PHONE_EXTENSION, PHONE_LINE_TYPE / PHONE_LINE_TYPE_MEANING — telephony details with decoded line type.
• EMAIL_ADDRESS, EMAIL_FORMAT / EMAIL_FORMAT_MEANING, URL, WEB_TYPE — electronic contact channels.
• PRIMARY_FLAG, DO_NOT_USE_FLAG, PRIORITY_OF_USE_CODE, PHONE_PREFERRED_ORDER — indicate preference and suppression.
• ATTRIBUTE1–ATTRIBUTE20, ATTRIBUTE_CATEGORY — descriptive flexfield context and values.
• OBJECT_VERSION_NUMBER, CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE — standard audit and concurrency columns.

Common Use Cases and Queries

Typical usage includes extracting active contact points for a student, reporting primary phone or email per owner, and resolving EDI location codes for trading partner interfaces.

To filter by the EDI location code that prompted the search:

SELECT contact_point_id, owner_table_id, edi_tp_header_id,
       edi_ece_tp_location_code, status_meaning
FROM   apps.igs_pe_contacts_v
WHERE  edi_ece_tp_location_code IS NOT NULL
AND    status_meaning = 'Active';

To list preferred telephone contacts for a given owner:

SELECT contact_point_id, phone_country_code, phone_area_code,
       phone_number, phone_line_type_meaning
FROM   apps.igs_pe_contacts_v
WHERE  owner_table_name = 'HZ_PARTIES'
AND    owner_table_id = :party_id
AND    contact_point_type = 'PHONE'
AND    do_not_use_flag = 'N'
ORDER BY phone_preferred_order;

To retrieve decoded email channels:

SELECT contact_point_id, email_address, email_format_meaning,
       primary_flag
FROM   apps.igs_pe_contacts_v
WHERE  contact_point_type = 'EMAIL'
AND    email_address IS NOT NULL;

Because lookup meaning columns depend on USERENV('LANG'), results vary by session language, and callers should be aware that the view filters on the REGISTRY_STATUS lookup type — contact points without a matching status entry may be excluded.