Search Results so_order_status_v




Overview
Explore in AI Chat

Overview

SO_ORDER_STATUS_V is a read-only database view owned by the APPS schema in Oracle E-Business Suite, defined over the Order Management (OE) tables that store order header and order line workflow status. In both release 12.1.1 and 12.2.2 the object carries a status of VALID. Its purpose is to present a denormalized, uniformly shaped result set that reports the current outcome of every seeded order and line action — such as booking, pick release, shipment, invoicing, or cancellation — together with the date on which that action reached its result.

Rather than forcing callers to interpret the thirty generic flag and date columns (S1/S1_DATE through S30/S30_DATE) that exist on the base entities, the view resolves those codes through the action and result definition tables and exposes a readable ACTION name, a RESULT name, and a computed ACTION_DATE. Because the view unions header-level and line-level activity into a single structure distinguished by the ACTION_CODE column ('ORDER' versus 'LINE'), it is well suited to reporting, OBIEE or XML Publisher extracts, and integration interfaces that need a flattened status feed without embedding Order Management workflow knowledge. The view is defined with an explicit ORDERED INDEX hint set targeting SO_ACTIONS_U2, SO_HEADERS_U1, SO_LINES_U1 and SO_RESULTS_U1, indicating it was tuned for indexed access driven by header or line identifiers.

Underlying Base Objects

The documented base objects of SO_ORDER_STATUS_V are SO_ACTIONS, SO_RESULTS, SO_HEADERS, SO_LINES and SO_LOOKUPS, with a dependency on the FND_GLOBAL package. SO_ACTIONS supplies the action definitions, RESULT_TABLE, RESULT_COLUMN and display name; SO_RESULTS supplies the result identifier and its name; SO_HEADERS and SO_LINES, exposed as views (SO_HEADERS@ and SO_LINES@ synonyms resolve to these), provide the S1..S30 result identifiers and the matching S1_DATE..S30_DATE timestamps. SO_LOOKUPS is joined with LOOKUP_TYPE = 'SO_ACTION_LEVEL' and LOOKUP_CODE = 'SO_HEADERS' to translate the action level into the ACTION_LEVEL meaning.

The view is a UNION of a header branch and a line branch. In each branch, a DECODE on A.RESULT_COLUMN maps the generic column name to the corresponding H.Sn or L.Sn value and to the H.Sn_DATE or L.Sn_DATE value, and only rows whose action is scoped to the correct result table (SO_HEADERS for the header branch) are retained. FND_GLOBAL supplies the session context used by the underlying Order Management logic.

Key Columns

  • ORDER_SOURCE_ID — HEADER_ID in the header branch, LINE_ID in the line branch; the primary identifier for the entity being reported.
  • ACTION_ID and ACTION — the action identifier and its descriptive name (for example, Booking, Pick Release, Ship Confirm).
  • RESULT_ID and RESULT — the outcome of the action, resolved from SO_RESULTS via the DECODE on the S1..S30 result column.
  • ACTION_LEVEL — meaning derived from SO_LOOKUPS indicating the level at which the action operates.
  • ACTION_CODE — literal value 'ORDER' or 'LINE' identifying which branch produced the row.
  • ACTION_DATE — the timestamp resolved from the matching Sn_DATE column for the action's result column.
  • DUMMY_ID — a constant 0 placeholder that keeps both UNION branches column-aligned.
  • CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN — standard audit columns inherited from SO_HEADERS.
  • ROW_ID — the ROWID of the underlying header record, available for direct row addressing.

Common Use Cases and Queries

Typical scenarios include populating order status dashboards, feeding downstream order tracking systems, driving exception reports for stalled orders, and correlating order lines to shipment or invoice milestones.

Retrieve all actions recorded against a specific order header:

  • SELECT action_code, action, result, action_date FROM apps.so_order_status_v WHERE order_source_id = :p_header_id AND action_code = 'ORDER';

Find orders that failed or stalled at a given action within a date window:

  • SELECT order_source_id, action, result, action_date FROM apps.so_order_status_v WHERE action = 'Booking' AND result = 'Failed' AND action_date >= :p_from AND action_date < :p_to;

List line-level actions for a set of lines, distinguishing level via ACTION_CODE:

  • SELECT order_source_id, action, result, action_date FROM apps.so_order_status_v WHERE action_code = 'LINE' AND action_date IS NOT NULL ORDER BY action_date DESC;

Because the view applies index hints and DECODE logic across the Order Management workflow tables, queries perform best when filtered by ORDER_SOURCE_ID or constrained by ACTION and a bounded ACTION_DATE range. Reports should treat the union as additive, using ACTION_CODE as the mandatory discriminator to prevent header and line rows from being conflated.