Results for “gr_order_info_v”

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

Overview

GR_ORDER_INFO_V is a consolidated reporting view owned by the APPS schema in Oracle E-Business Suite 12.1.1 and 12.2.2. It belongs to the Process Manufacturing Regulatory Management (GR) product family, where it supports regulatory and fulfillment reporting that must span both discrete Order Management (OM) transactions and Process Manufacturing order fulfillment records. The view presents a unified projection of order, line, shipment, and delivery information drawn from Order Fulfillment tables and Order Management tables, allowing downstream reports, concurrent programs, and integrations to query a single object rather than joining the disparate OM and OP schemas directly.

The definition is a UNION ALL of two branches. The first branch reads Oracle Order Management headers and lines (OE_ORDER_HEADERS_ALL, OE_ORDER_LINES_ALL) and joins them to shipping execution delivery data (WSH_NEW_DELIVERIES, WSH_DELIVERY_DETAILS, WSH_DELIVERY_ASSIGNMENTS) and transaction types (OE_TRANSACTION_TYPES_TL). The second branch reads Process Manufacturing order fulfillment tables (OP_ORDR_HDR, OP_ORDR_DTL, OP_BILL_LAD, OP_HOLD_CDS, OP_ORDR_TYP_VL). Because the two branches expose the same column list, the view behaves as a single logical source of order-fulfillment detail regardless of which subsystem originated the order.

Underlying Base Objects

The ETRM metadata documents the following referenced objects, all resolved through APPS synonyms except OP_ORDR_TYP_VL, which is itself a view:

• OE_ORDER_HEADERS_ALL — order header attributes such as ORDER_NUMBER, HEADER_ID, SHIP_FROM_ORG_ID, INVOICE_TO_ORG_ID, and SHIP_TO_ORG_ID.
• OE_ORDER_LINES_ALL — line and shipment detail (LINE_ID, LINE_NUMBER, SHIPMENT_NUMBER, INVENTORY_ITEM_ID, ACTUAL_SHIPMENT_DATE).
• OE_TRANSACTION_TYPES_TL — translated order/transaction type names.
• WSH_NEW_DELIVERIES, WSH_DELIVERY_DETAILS, WSH_DELIVERY_ASSIGNMENTS — delivery number, delivery ID, and shipment-level delivery attributes.
• OP_ORDR_HDR, OP_ORDR_DTL, OP_BILL_LAD, OP_HOLD_CDS, OP_ORDR_TYP_VL — Process Manufacturing order header, detail, bill of lading, hold code, and order type lookup data.

Joins in the first branch link lines to headers, lines to delivery details, delivery details to assignments, assignments to deliveries (outer join on WSH_NEW_DELIVERIES), and headers to transaction types. The second branch links header and detail on ORDER_ID and FROM_WHSE, applies hold-code logic across OP_HOLD_CDS, outer-joins the bill of lading, and joins the order type view.

Key Columns

The user's search term, shipaddr_id, maps directly to SHIPADDR_ID, sourced from OP_ORDR_HDR.SHIPADDR_ID in the Process Manufacturing branch and corresponding to the ship-to organization in the OM branch (SHIP_TO_ORG_ID at header and line level). Other significant columns include:

• ORDER_NO / ORDER_COMMENT — order identifier and free-text comment (OM branch supplies the order number, OP branch supplies ORDER_NO and ORDER_COMMENT).
• ORDER_ID / HEADER_ID — primary key references to OP_ORDR_HDR and OE_ORDER_HEADERS_ALL respectively.
• LINE_NO and LINE_ID — line or shipment identifier; the OM branch synthesizes LINE_NO as LINE_NUMBER concatenated with SHIPMENT_NUMBER.
• ITEM_ID / INVENTORY_ITEM_ID — inventory item reference.
• BILLCUST_ID, SHIPC, AFROM_WHSE, FROM_WHSE — customer and warehouse attributes.
• ACTUAL_SHIP_DATE / ACTUAL_SHIPMENT_DATE — actual shipment date.
• ROW_ID — row identifier carried from the source header ROWID.
• Audit columns (CREATED_BY, CREATION_DATE, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN) for change tracking.

Common Use Cases and Queries

The view is typically used to produce combined order-fulfillment extracts, regulatory shipment reports, and reconciliation between OM and Process Manufacturing shipments. A representative query filtering on the searched column is:

• SELECT order_no, line_no, item_id, shipaddr_id, actual_ship_date FROM apps.gr_order_info_v WHERE shipaddr_id = :p_ship_to_org;
• SELECT order_id, order_no, billcust_id, shipaddr_id FROM apps.gr_order_info_v WHERE actual_ship_date BETWEEN :p_from AND :p_to;
• SELECT order_no, order_comment, line_no, from_whse, bill_id FROM apps.gr_order_info_v WHERE from_whse = :p_warehouse ORDER BY order_no, line_no;

Because the view is a UNION ALL, column names in a WHERE clause must be the view's exposed names (for example SHIPADDR_ID), not base-table names; the same caveat applies to ORDER_NO and LINE_NO. Filtering on indexed base columns such as ORDER_ID or LINE_ID generally performs better than filtering on derived or concatenated values. Query performance also benefits from restricting the date range, since both branches scan large transactional tables.