Search Results op_ordr_sts




Overview
Explore in AI Chat

Overview

APPS.GML_GASNO_ORDERS_V is a reporting and integration view within the Oracle E-Business Suite Process Manufacturing (OPM) module, specifically belonging to the Oracle Enterprise Technical Reference Model (ETRM) domain for Gas and Natural Orders. The view consolidates order header, order status, shipping, and customer contact information into a single denormalized result set, allowing downstream reports, interfaces, and concurrent programs to retrieve sales order data without performing multi-table joins directly against the transactional base tables.

The view is particularly relevant to the "op_ordr_sts" search context, as its definition joins OP_ORDR_HDR to OP_ORDR_STS to resolve the human-readable order status code alongside the numeric status flag. This makes the view a natural access point when status-driven filtering, status code decoding, or order lifecycle reporting is required. In Oracle EBS 12.1.1 and 12.2.2, the view remains defined in the APPS schema and is accessible to users with the appropriate responsibilities and grants. Because it filters out soft-deleted records (delete_mark = 0) and cancelled orders (order_status != -1), it presents only active, reportable order data.

Unlike base tables, this view exposes a stable, business-friendly interface. It is commonly consumed by custom reports, Discoverer workbooks, BI Publisher data templates, and outbound integration extracts that need order-level attributes in a single query.

Underlying Base Objects

The documented base objects referenced by GML_GASNO_ORDERS_V are:

  • OP_ORDR_HDR (SYNONYM) — Order header, aliased as oh; supplies order_id, order_status, order_no, order_date, custpo_no, orgn_code, order_comment, and the 30 SO_HEADER_ATTRIBUTE columns.
  • OP_ORDR_DTL (SYNONYM) — Order detail, aliased as od; joined on order_id and joined to OP_BILL_LAD via bol_id.
  • OP_ORDR_STS (SYNONYM) — Order status, aliased as os; provides the decoded order_status_code through the join on oh.order_status = os.order_status.
  • OP_BILL_LAD (SYNONYM) — Bill of lading / shipment, aliased as bl; linked through od.bol_id = bl.bol_id.
  • OP_CUST_MST (SYNONYM) — Customer master, aliased as cm; supplies customer-level data.
  • GML_EC_Contact_V (VIEW) — Contact details view, aliased as ecc; supplies contact information associated with the order.
  • FND_PROFILE (PACKAGE) — Oracle Application Object Library profile package; referenced within the view or its dependent logic to resolve profile option values.

The joins enforce referential integrity across the sales order lifecycle: header to status for decoding, header to detail for line context, detail to bill of lading for shipment context, and customer and contact objects for party information.

Key Columns

  • ORDER_ID — Primary key of the order header; the join anchor across the view.
  • ORDER_STATUS — Numeric status value stored on OP_ORDR_HDR, used for internal filtering.
  • ORDER_STATUS_CODE — Decoded status text sourced from OP_ORDR_STS; the column most relevant to "op_ordr_sts" lookups.
  • ORDER_NO — The user-facing sales order number.
  • CUSTPO_NO — Customer purchase order reference provided on the order.
  • SO_ORGN_CODE — Operating unit / organization code owning the order.
  • SO_DATE — Order date.
  • ORDER_COMMENT — Header-level free-text comment.
  • SO_HEADER_CATEGORY and SO_HEADER_ATTRIBUTE1 .. SO_HEADER_ATTRIBUTE30 — Descriptive flexfield context and segment values captured on the order header, exposed with descriptive aliases for reporting.

An important structural characteristic is the correlated subquery on OP_ORDR_DTL that restricts results to the first line (min(line_id)) of each order within a shipment, grouped by bol_id and order_id. This prevents order duplication when multiple lines exist, ensuring one row per qualifying order-shipment combination.

Common Use Cases and Queries

The view supports status reporting, order backlog analysis, shipment tracking, and integration extracts. A typical query filtering on the decoded status code (the "op_ordr_sts" reference) is:

  • SELECT order_no, order_status_code, custpo_no, so_date, so_orgn_code FROM apps.gml_gasno_orders_v WHERE order_status_code = 'OPEN';
  • Order listing for a given operating unit: SELECT order_no, order_status_code, so_date FROM apps.gml_gasno_orders_v WHERE so_orgn_code = :p_orgn ORDER BY so_date DESC;
  • Backlog extract joined to customer data via the underlying OP_CUST_MST relationship to enrich reporting with party attributes.
  • Integration pulls that require header flexfield attributes, selecting SO_HEADER_CATEGORY and the specific SO_HEADER_ATTRIBUTEn columns.
  • Status distribution summaries: SELECT order_status_code, COUNT(*) FROM apps.gml_gasno_orders_v GROUP BY order_status_code;

Because the view already filters delete_mark and cancelled orders, consumers should not attempt to reintroduce those records; queries built on the view reflect the active order universe as defined by OPM. Performance depends on the supporting indexes on OP_ORDR_HDR, OP_ORDR_DTL, and OP_ORDR_STS, and the correlated subquery on line_id makes selective predicates on order identifiers or status especially valuable.