Results for “oefv_order_headers”

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

Overview

APPS.OEFV_ORDER_HEADERS is a denormalized reporting view owned by the APPS schema within the Oracle Order Management (ONT) module. It consolidates order header information from OE_ORDER_HEADERS_ALL with customer, contact, address, shipping, and reference data drawn from the Oracle Receivables and Oracle Trading Community Architecture (HZ) tables. The view is intended to provide a single, human-readable row per sales order, exposing order number, customer name and number, ship-to and bill-to addresses, ship-from organization, order type, order source, pricing list, agreement, and salesperson details. Because it presents descriptive labels (names, codes, formatted address strings, formatted contact names) rather than raw foreign keys, OEFV_ORDER_HEADERS is well suited as a source for ad-hoc reporting, operational queries, and as a query basis for order inquiry or integration extracts. The schema owner is APPS and the object status is VALID in the documented ETRM 12.2.2 metadata; it is also present in 12.1.1 environments.

Underlying Base Objects

The view is defined over a large set of base objects, predominantly synonyms to the underlying base tables. The ETRM metadata lists the following referenced objects: AR_LOOKUPS (VIEW), FND_CURRENCIES (SYNONYM), FND_USER (SYNONYM), HZ_CONTACT_POINTS (SYNONYM), HZ_CUST_ACCOUNTS (SYNONYM), HZ_CUST_ACCOUNT_ROLES (SYNONYM), HZ_CUST_ACCT_SITES_ALL (SYNONYM), HZ_CUST_SITE_USES_ALL (SYNONYM), HZ_LOCATIONS (SYNONYM), HZ_PARTIES (SYNONYM), HZ_PARTY_SITES (SYNONYM), HZ_RELATIONSHIPS (SYNONYM), MTL_PARAMETERS (SYNONYM), OE_AGREEMENTS_TL (SYNONYM), OE_BIS_SALESPERSON (PACKAGE), OE_ORDER_HEADERS_ALL (SYNONYM), OE_ORDER_SOURCES (SYNONYM), OE_TRANSACTION_TYPES_TL (SYNONYM), QP_LIST_HEADERS_TL (SYNONYM), RA_RULES (SYNONYM), and RA_TERMS (SYNONYM). The central driving table is OE_ORDER_HEADERS_ALL. Customer, party, site, and address attributes are resolved through the HZ_* entities (HZ_CUST_ACCOUNTS, HZ_CUST_ACCT_SITES_ALL, HZ_CUST_SITE_USES_ALL, HZ_PARTY_SITES, HZ_LOCATIONS, HZ_PARTIES, HZ_CONTACT_POINTS, HZ_RELATIONSHIPS). Lookups such as contact role meanings come from AR_LOOKUPS; agreement, order source, order type, and price list labels come from OE_AGREEMENTS_TL, OE_ORDER_SOURCES, OE_TRANSACTION_TYPES_TL, and QP_LIST_HEADERS_TL respectively; terms and rules from RA_TERMS and RA_RULES; and the salesperson name is derived by calling the OE_BIS_SALESPERSON package. Because the view references many objects, its perceived performance is heavily dependent on the indexing and volume of these base tables.

Key Columns

• ORDER_NUMBER – the user-visible order number from OE_ORDER_HEADERS_ALL.
• CUSTOMER_NUMBER / CUSTOMER – the customer account number and party name.
• CUSTOMER_CONTACT, CUSTOMER_CONTACT_PHONE_NUMBER, CUSTOMER_CONTACT_EMAIL – formatted contact name, phone, and email for the sold-to customer.
• AGREEMENT – agreement name associated with the order.
• PL.NAME – the price list name.
• SHIP_TO_LOCATION, SHIP_TO_ADDRESS1..4, SHIP_TO_CTY_ST_ZIP_CTRY, SHIP_TO_CONTACT – formatted ship-to address and contact information.
• SHIP_FROM_ORG – the organization code of the ship-from organization, sourced from MTL_PARAMETERS (shown as SHIP_FROM_ORG.ORGANIZATION_CODE). This is the column returned when searching for "ship_from_org".
• ORDER_SOURCE / ORDER_TYPE – names from OE_ORDER_SOURCES and OE_TRANSACTION_TYPES_TL.
• Salesperson name – returned via OE_BIS_SALESPERSON.GET_SALESPERSON_NAME(HEADER.SALESREP_ID).
• BILL_TO, BILL_TO_ADDRESS1..4 – formatted bill-to location and address lines.

Common Use Cases and Queries

The view is typically used for order-status reporting, shipment analysis by ship-from organization, and customer-facing order extracts. A common query filters by the ship-from organization code:

SELECT order_number, customer, ship_from_org, order_type
FROM apps.oefv_order_headers
WHERE ship_from_org = :org_code;

To report shipped orders by customer and ship-to location:

SELECT order_number, customer, ship_to_location, ship_to_cty_st_zip_ctry
FROM apps.oefv_order_headers
WHERE customer = :customer_name;

Because the view joins many large HZ and OE tables and invokes a PL/SQL function for the salesperson name, queries should be constrained by selective predicates (for example ORDER_NUMBER, CUSTOMER, or SHIP_FROM_ORG) to avoid full scans. Where additional order attributes are required beyond those exposed, the view must be joined back to OE_ORDER_HEADERS_ALL on ORDER_NUMBER.