Results for “ship_to_contact”

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

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:

• HZ_CUST_ACCOUNTS — the customer account header, supplying CUSTOMER_ID and ACCOUNT_NUMBER.
• HZ_CUST_ACCT_SITES — the account-site association, providing CUST_ACCT_SITE_ID and site status.
• HZ_CUST_SITE_USES_ALL — the site-use layer (BILL_TO, SHIP_TO, etc.), driving the SITE_USE_ID and the decoded BILL_TO_SITE / SHIP_TO_SITE / SOLD_TO_SITE indicators.
• HZ_LOCATIONS — the address master, contributing ADDRESS1 through ADDRESS4, CITY, STATE, POSTAL_CODE, and COUNTRY.
• HZ_PARTY_SITES — links locations to parties.
• HZ_PARTIES — supplies PARTY_NAME and EMAIL_ADDRESS.
• HZ_CUST_ACCOUNT_ROLES — the contact/role assignment, yielding CONTACT_ID and the role type used for ROLE_TYPE and ROLE_TYPE_CODE.
• HZ_ROLE_RESPONSIBILITY — filters and classifies roles by responsibility (SHIP_TO, BILL_TO).
• HZ_RELATIONSHIPS — provides the contact party linkage used for CONTACT_EMAIL.
• AR_LOOKUPS — resolves lookup meanings such as contact role titles appended to CONTACT_NAME.

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.