Search Results order_status_code

Overview

IBE_ORDER_HEADER_ADV_V is an APPS-owned reporting view within the Oracle E-Business Suite iStore (IBE) module. Its status is VALID in both 12.1.1 and 12.2.2. The view presents a denormalized, business-friendly projection of order header information sourced primarily from OE_ORDER_HEADERS_ALL, enriched with customer, party, site-use, and account-site attributes drawn from the Oracle Trading Community Architecture (HZ) tables. It also resolves the order flow status into a translatable meaning via OE_LOOKUPS.

The view's central purpose is to expose order headers alongside the sold-to, invoice-to (bill-to), and ship-to account identifiers in a single flat record, so that iStore and downstream reporting or integration processes do not need to independently join the underlying Order Management and HZ base tables. This makes it particularly valuable for queries involving SHIP_TO_ACCOUNT_ID, the term for which this object is frequently searched.

Underlying Base Objects

The documented referenced base objects are: HZ_CUST_ACCOUNTS (synonym), HZ_CUST_ACCT_SITES_ALL (synonym), HZ_CUST_SITE_USES_ALL (synonym), HZ_PARTIES (synonym), OE_LOOKUPS (view), and OE_ORDER_HEADERS_ALL (synonym).

The driving table is OE_ORDER_HEADERS_ALL, aliased OH. The sold-to relationship is mandatory: OH.SOLD_TO_ORG_ID joins to HZ_CUST_ACCOUNTS, which in turn joins to HZ_PARTIES on PARTY_ID. The invoice-to and ship-to relationships are outer joins: OH.INVOICE_TO_ORG_ID and OH.SHIP_TO_ORG_ID join to HZ_CUST_SITE_USES_ALL, which joins to HZ_CUST_ACCT_SITES_ALL. The flow status code is resolved against OE_LOOKUPS where LOOKUP_TYPE = 'FLOW_STATUS'.

Key Columns

• HEADER_ID — Primary key of the order header; the principal join key to order lines and other OM objects.
• ORDER_NUMBER — User-visible order identifier.
• ORDER_STATUS / ORDER_STATUS_CODE — The decoded MEANING and raw FLOW_STATUS_CODE respectively.
• CUST_ACCOUNT_ID, PARTY_ID, CUSTOMER_NAME — Sold-to account, its party, and the party name.
• BILL_TO_ACCOUNT_ID / BILL_TO_ACCT_SITE_ID — Invoice-to account and site-use identifiers derived from the outer join.
• SHIP_TO_ACCOUNT_ID — The customer account identifier for the ship-to account site. Because the ship-to join is an outer join, this column can be null where no ship-to site use exists.
• END_CUST_ACCOUNT_ID — The end customer account associated with the order header.
• PRICING_AGREEMENT_ID — Pricing agreement reference (AGREEMENT_ID in the header).
• CREATED_BY, ORG_ID — Audit and multi-org operating unit context.

Common Use Cases and Queries

A frequent scenario is confirming the ship-to account and party for booked orders, for example to reconcile fulfillment records. The following query returns order number, ship-to account, and customer name:

• SELECT order_number, ship_to_account_id, customer_name FROM apps.ibe_order_header_adv_v WHERE ship_to_account_id IS NOT NULL AND org_id = :p_org_id;

Because SHIP_TO_ACCOUNT_ID is populated through the outer join to the ship-to site use and account site, queries that require a ship-to value should filter out nulls. To map a specific ship-to site use back to its accounting context, join to HZ_CUST_ACCT_SITES_ALL on the account site identifier. The view is also common in iStore order-confirmation and post-order reporting where sold-to, bill-to, and ship-to must be presented together, and in integration extracts that feed external order management or analytics platforms.