Search Results oe_customer_site_contacts_v




Overview
Explore in AI Chat

Overview

OE_CUSTOMER_SITE_CONTACTS_V is a read-only Oracle E-Business Suite view owned by the APPS schema and validated in releases 12.1.1 and 12.2.2. It belongs to the Order Management (ONT) product family and presents a denormalized, report-friendly projection of customer site and contact information drawn primarily from the Oracle Trading Community Architecture (TCA) and Receivables customer model. The view joins customer accounts, account sites, site uses, party sites, locations, parties, and customer account roles into a single flattened structure, adding descriptive address lines, contact name composition, and decoded site-role flags. Its principal purpose is to spare report developers and integration builders from reconstructing the multi-table TCA customer hierarchy manually, exposing ready-to-consume columns for customer name, address, contact, and site status that can be embedded in concurrent programs, BI Publisher data templates, Discoverer workbooks, and inbound/outbound interface logic that references Order Management customer data.

Underlying Base Objects

The view is defined over the following documented objects, most of which are synonyms resolving to TCA base tables in the 12.2.2 data model:

The joins traverse from the account down through account sites and site uses, and laterally across to the party and location entities, producing one row per qualifying site-use/contact combination.

Key Columns

  • CUSTOMER_ID / CUSTOMER_NUMBER — CUST_ACCOUNT_ID and ACCOUNT_NUMBER from HZ_CUST_ACCOUNTS, identifying the customer.
  • CUSTOMER_NAME — PARTY_NAME for the customer party.
  • ORGANIZATION_ID — mapped to SITE_USE_ID, not to an inventory organization; used to identify the specific site use.
  • LOCATION_CODE / LOCATION — the site's location identifier.
  • ADDRESS_LINE_1 through ADDRESS_LINE_5, CITY, STATE, POSTAL_CODE, COUNTRY — assembled from HZ_LOCATIONS, with ADDRESS_LINE_4 concatenating city, state, postal code, and country.
  • FIRST_NAME / LAST_NAME / CONTACT_NAME — contact party names, with CONTACT_NAME composing last name, first name, and a lookup-derived role meaning.
  • CONTACT_EMAIL — e-mail from the related contact party.
  • CONTACT_ID — CUST_ACCOUNT_ROLE_ID of the contact.
  • ROLE_TYPE / ROLE_TYPE_CODE — decoded responsibility type, normalized to SHIP_TO or BILL_TO where applicable.
  • SITE_STATUS, ACCT_SITE_STATUS, CUST_ACCT_STATUS — status flags at the site-use, account-site, and account levels.
  • BILL_TO_SITE, SHIP_TO_SITE — subquery-derived YES/NO indicators for active bill-to and ship-to usage.

Common Use Cases and Queries

Typical uses include customer master extracts, order-entry validation, notification lists for shipping and billing contacts, and data migration or interface population. A representative query listing active ship-to contacts for a given account:

  • SELECT customer_number, customer_name, contact_name, contact_email, address_line_1, city, state, postal_code FROM oe_customer_site_contacts_v WHERE customer_number = :p_account AND ship_to_site = 'YES' AND site_status = 'A';
  • SELECT customer_id, customer_name, bill_to_site, ship_to_site FROM oe_customer_site_contacts_v WHERE cust_acct_status = 'A' AND site_status = 'A';

Because the view joins several TCA objects, queries should filter on high-selectivity columns such as CUSTOMER_ID, CUSTOMER_NUMBER, or SITE_USE_ID rather than scanning the full result set, and should be mindful that a customer with multiple sites and contacts returns multiple rows.