Search Results gml_ec_contact_v




Overview
Explore in AI Chat

Overview

GML_EC_CONTACT_V is a read-only database view owned by the APPS schema in Oracle E-Business Suite. It belongs to the GML product family — Process Manufacturing Logistics — and is documented in ETRM as a "Customer contact view." Its purpose is to present a denormalized, flattened representation of customer contacts together with their associated address, site use, and trading-partner header information, so that downstream reporting, integration, and EDI-style processing can retrieve contact detail in a single query rather than joining the underlying Oracle Receivables and ECE tables directly.

The view is particularly relevant to Oracle's e-Commerce Gateway / trading-partner data model. The presence of TP_HEADER_ID, TP_REFERENCE_EXT1, and TP_REFERENCE_EXT2 in the projection indicates that the view exposes trading-partner reference values alongside standard customer contact attributes. This makes GML_EC_CONTACT_V a natural access point for outbound or inbound contact-oriented interfaces where the trading-partner context must be carried through to the contact record.

Underlying Base Objects

The view text is defined over six base tables. ECE_TP_HEADERS (aliased ETH) supplies the trading-partner header and the TP_REFERENCE_EXT columns. RA_ADDRESSES_ALL (RAD) supplies the customer address and the ECE_TP_LOCATION_CODE stored in ORIG_SYSTEM_REFERENCE. RA_CUSTOMERS (RCU) supplies the customer identity and name. RA_SITE_USES_ALL (RSU) provides site use identifiers and locations. RA_CONTACTS (RAC) supplies contact name and job title data. RA_PHONES (RAP) supplies the general-purpose primary phone number.

The joins are driven from ECE_TP_HEADERS to RA_ADDRESSES_ALL on TP_HEADER_ID, then sequentially through RA_CUSTOMERS, RA_SITE_USES_ALL, RA_CONTACTS, and RA_PHONES. The RA_CONTACTS and RA_PHONES joins are outer joins (denoted by the (+), making it an important detail that the view includes addresses without a matching contact or phone record. The RAP join is further constrained to PHONE_TYPE = 'GEN' and PRIMARY_FLAG = 'Y'. Note that the ETRM documented metadata for 12.2.2 lists only DUAL as a referenced base object, which reflects dependency-tracking artifacts in the data dictionary rather than the actual runtime definition shown in the view text.

Key Columns

  • SITE_USE_ID, CUSTOMER_ID, ADDRESS_ID, CONTACT_ID — primary identifiers joining the view back to the transactional tables.
  • TP_HEADER_ID, TP_REFERENCE_EXT1, TP_REFERENCE_EXT2 — trading-partner header linkage and its two reference extension columns; TP_REFERENCE_EXT2 is the column most commonly targeted by searches such as "tp_reference_ext2".
  • ORIG_SYSTEM_REFERENCE / TP_LOCATION_CODE_EXT — location code carried from the address record for external trading-partner identification.
  • CUSTOMER_NAME, ADDRESS1–ADDRESS4, CITY, STATE, PROVINCE, COUNTY, POSTAL_CODE, COUNTRY — the customer mailing address block.
  • LAST_NAME, FIRST_NAME, JOB_TITLE — contact-person attributes, populated only when a matching RA_CONTACTS row exists.
  • AREA_CODE, PHONE_NUMBER — the general primary phone number for the contact.

Common Use Cases and Queries

Typical uses include building contact extracts for EDI trading-partner feeds, validating that TP_REFERENCE_EXT2 values are populated correctly across a customer base, and producing contact lists for logistics or order-management reporting.

SELECT tp_reference_ext2, customer_name, last_name, first_name, phone_number
FROM   apps.gml_ec_contact_v
WHERE  tp_reference_ext2 IS NOT NULL;

To locate a specific contact by trading-partner reference:

SELECT customer_id, contact_id, address1, city, country
FROM   apps.gml_ec_contact_v
WHERE  tp_reference_ext2 = :p_tp_ref
AND    tp_header_id = :p_tp_header_id;

Because the contact and phone joins are outer joins, queries filtering on LAST_NAME or PHONE_NUMBER implicitly exclude address/site records that lack a contact, which should be accounted for when reconciling counts.