Results for “oe_cust_site_phone_v”

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

Overview

The OE_CUST_SITE_PHONE_V view is a denormalized reporting view owned by the APPS schema within the Oracle E-Business Suite Order Management (ONT) product family. It consolidates customer site, party, location, and contact point telephone information into a single flattened structure intended for reporting, inquiry, and integration purposes. Rather than navigating the normalized Trading Community Architecture (TCA) model across multiple joined tables, consumers of this view can retrieve customer address and phone details in one query, which simplifies custom reports, concurrent programs, and outbound interfaces.

Because the view exposes a SOLD_TO_SITE flag driven by the HZ_CUST_SITE_USES_ALL table, it is frequently consulted when order entry logic or ad hoc reporting needs to identify whether a given customer site is designated as a sold-to site. Users searching for sold_to_site typically encounter this view when building queries against order management data that must resolve the selling party's site and associated telephone contact information.

Underlying Base Objects

Per the documented ETRM metadata for 12.2.2, the view is defined over the following base objects, all referenced through APPS synonyms:

• HZ_CUST_ACCOUNTS — provides the customer account identifier and account number.
• HZ_CUST_ACCT_SITES / HZ_CUST_ACCT_SITES_ALL — supply the account site records and site status.
• HZ_CUST_SITE_USES_ALL — supplies site use codes (BILL_TO, SHIP_TO, SOLD_TO, DELIVER_TO) and their active status.
• HZ_PARTIES — provides the customer name and e-mail address.
• HZ_PARTY_SITES — links parties to locations.
• HZ_LOCATIONS — supplies address1 through address4, city, state, postal code, and country.
• HZ_CONTACT_POINTS — supplies telephone area code, phone number, extension, and the transposed phone number.

These objects form the core TCA customer model. The view joins them to produce a site-centric phone listing with accompanying address and account context.

Key Columns

• CUSTOMER_ID / CUSTOMER_NUMBER — customer account identifier and account number from HZ_CUST_ACCOUNTS.
• CUSTOMER_NAME — party name from HZ_PARTIES.
• ORGANIZATION_ID — mapped to SITE.SITE_USE_ID.
• ADDRESS_LINE_1 through ADDRESS_LINE_5 — location address components; ADDRESS_LINE_4 concatenates city, state, postal code, and country.
• SOLD_TO_SITE, BILL_TO_SITE, SHIP_TO_SITE — derived flags returning 'YES'/'NO' based on active site uses.
• ACCT_SITE_STATUS, CUST_ACCT_STATUS, SITE_STATUS — status indicators for the site and account.
• AREA_CODE, PHONE_NUMBER, PHONE_EXTENSION, TRANSPOSED_PHONE_NUMBER — contact telephone details from HZ_CONTACT_POINTS.
• CONTACT_ID — hard-coded to -1, indicating this view does not expose individual contacts.

Common Use Cases and Queries

A typical use case is retrieving all sold-to sites for a customer along with their telephone numbers.

SELECT customer_name, address_line_1, city,
       area_code, phone_number
FROM   oe_cust_site_phone_v
WHERE  sold_to_site = 'YES'
AND    customer_number = :p_account_number;

The view is also used to list phone numbers for all active bill-to sites, or to drive customer-facing correspondence that requires a site address and contact telephone. Because several columns are hard-coded NULL (FIRST_NAME, LAST_NAME, CONTACT_EMAIL, CONTACT_NAME), it should not be used to resolve contact-level details; a contact-oriented view should be substituted when that granularity is required.