Search Results order_category_desc




Overview
Explore in AI Chat

Overview

SO_ORDER_NUMBERS_V is a reporting view in the Oracle E-Business Suite Order Entry (OE) module. It presents a consolidated, denormalized projection of sales order header information, joining order headers to order types, order category lookup values, and customer records. The view is intended to simplify queries that need to display human-readable order identifiers alongside descriptive attributes such as order type name, order category description, and customer name and number, without requiring the developer to reconstruct the underlying join logic.

In Oracle EBS 12.1.1 and 12.2.2, the view is documented in the ETRM repository as belonging to the OE product. The ETRM metadata records the implementation status as "Not implemented in this database," indicating that the view is a documented dictionary object rather than a mandatory seeded database object in every environment. Its principal role is in reporting and integration scenarios where order number, order category, order type, and customer context are required together, including extraction of the ORIGINAL_SYSTEM_SOURCE_CODE and ORIGINAL_SYSTEM_REFERENCE attributes used for legacy and external system traceability.

Underlying Base Objects

The view is defined over four base objects joined in a single SELECT statement:

  • SO_HEADERS HD — the primary sales order header table, supplying order number, header identifier, ordered date, category, order type identifier, purchase order number, original system source code and reference, customer identifier, shipping attributes, and open flag.
  • SO_ORDER_TYPES OT — joined on ORDER_TYPE_ID to supply the order type name (OT.NAME).
  • SO_LOOKUPS SL — joined on ORDER_CATEGORY with LOOKUP_TYPE = 'ORDER_CATEGORY' to supply the category meaning.
  • RA_CUSTOMERS RC — outer-joined on CUSTOMER_ID to supply the customer name and customer number, preserving orders that have no matching customer record.

The ETRM metadata records the owner as blank and lists no documented referenced base objects, so the join relationships above are derived from the documented view text. The view contains no aggregation and no DISTINCT clause, so it returns one row per matching SO_HEADERS record.

Key Columns

  • ORDER_NUMBER and HEADER_ID — the business-facing order number and the internal primary key of the order header.
  • DATE_ORDERED — the date on which the order was placed.
  • ORDER_CATEGORY and ORDER_CATEGORY_DESC — the coded category value and its decoded meaning from SO_LOOKUPS.
  • ORDER_TYPE and ORDER_TYPE_ID — the descriptive order type name and its identifier.
  • PURCHASE_ORDER_NUM — the customer purchase order reference captured on the order.
  • ORIGINAL_SYSTEM_SOURCE_CODE and ORIGINAL_SYSTEM_REFERENCE — attributes identifying the originating system and its reference, relevant to the search term "original_system_source_code" and to order migration or integration auditing.
  • CUSTOMER_NAME, CUSTOMER_NUMBER, and CUSTOMER_ID — customer identification from RA_CUSTOMERS.
  • SHIP_METHOD_CODE and SHIPMENT_PRIORITY_CODE — shipping method and priority indicators.
  • OPEN_FLAG — indicates whether the order remains open.

Common Use Cases and Queries

Typical uses include order status reporting, reconciliation of externally originated orders, and integration extracts that require decoded order type and category values. A representative query retrieving orders with their originating system information is:

  • SELECT ORDER_NUMBER, ORDER_TYPE, ORDER_CATEGORY_DESC, ORIGINAL_SYSTEM_SOURCE_CODE, ORIGINAL_SYSTEM_REFERENCE, CUSTOMER_NAME, DATE_ORDERED, OPEN_FLAG FROM SO_ORDER_NUMBERS_V WHERE ORIGINAL_SYSTEM_SOURCE_CODE IS NOT NULL ORDER BY DATE_ORDERED;
  • SELECT ORDER_NUMBER, CUSTOMER_NUMBER, PURCHASE_ORDER_NUM FROM SO_ORDER_NUMBERS_V WHERE OPEN_FLAG = 'Y' AND ORDER_CATEGORY = 'ORDER';
  • SELECT ORDER_TYPE, COUNT(*) FROM SO_ORDER_NUMBERS_V GROUP BY ORDER_TYPE ORDER BY 2 DESC;

Because the customer join is an outer join, queries filtering on CUSTOMER_NAME will exclude orders lacking a customer match. Consumers should also confirm object availability in each environment, since the ETRM metadata indicates the view is not implemented in every database.