Search Results oe_contacts_v




Overview
Explore in AI Chat

Overview

OE_CONTACTS_V is a reporting view owned by the APPS schema in Oracle E-Business Suite releases 12.1.1 and 12.2.2. It belongs to the ONT (Order Management) product family and, per the ETRM metadata, is based on the RA_CONTACTS lineage of data — though its actual view text reads primarily from the Oracle Trading Community Architecture (TCA) customer and relationship tables (HZ_*). The view exposes a flattened, denormalized representation of customer contacts associated with a customer account and account site, allowing Order Management and downstream reporting components to retrieve contact name, job title, status, and e-mail address without navigating the multi-table TCA model directly.

Functionally, the view acts as a convenience layer over the party-relationship model. Order Management uses contact information for tasks such as order acknowledgements, ship-to contact assignment, and sales-order-related communications. Because the view pre-joins the relationship directionality, role type, and e-mail resolution, consumers receive a single-row-per-contact result set suitable for LOV queries, concurrent-program extracts, and ad hoc operational reporting.

Underlying Base Objects

The documented referenced base objects (all exposed via synonyms owned by APPS) are:

The joins enforce that a contact role, its relationship record, its organization contact detail, and the corresponding party e-mail all belong to the same directional relationship chain. The effective grain is one row per cust_account_role_id (exposed as CONTACT_ID).

Key Columns

  • CONTACT_ID — sourced from ACCT_ROLE.CUST_ACCOUNT_ROLE_ID; the unique identifier of the contact role record and the natural primary key of the view row.
  • ORG_ID — sourced from ACCT_ROLE.CUST_ACCT_SITE_ID, representing the customer account site (operating unit context is not implied here; the alias ORG_ID reflects the account-site identifier used by Order Management).
  • CUSTOMER_ID — the CUST_ACCOUNT_ID linking the contact to its customer account.
  • NAME — concatenation of PARTY.PERSON_LAST_NAME and PARTY.PERSON_FIRST_NAME in the form "Last, First".
  • JOB_TITLE — from HZ_ORG_CONTACTS, the contact's title within the organization.
  • STATUS — the role status from HZ_CUST_ACCOUNT_ROLES, indicating active/inactive role assignment.
  • EMAIL_ADDRESS — the e-mail resolved from the relationship party (REL_PARTY).

Common Use Cases and Queries

Typical scenarios include resolving the primary contact for an order's ship-to site, populating contact LOVs in Order Management forms, and extracting contact lists for acknowledgement or notification programs.

SELECT contact_id,
       org_id,
       customer_id,
       name,
       job_title,
       status,
       email_address
  FROM apps.oe_contacts_v
 WHERE customer_id = :p_customer_id
   AND status = 'A';

Order-site lookups join the view to order headers or ship-to sites on ORG_ID:

SELECT o.order_number,
       c.name,
       c.job_title,
       c.email_address
  FROM oe_order_headers_all o,
       oe_contacts_v        c
 WHERE c.customer_id = o.customer_id
   AND c.org_id      = o.ship_to_org_id;

Because the view performs several inner joins across HZ tables, contacts lacking an organization contact record or a resolvable relationship party e-mail will not appear; queries requiring all contacts, including incomplete records, should be written against the base HZ tables directly.