Results for “trans_qty_usage”

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

Overview

The APPS.IC_TRAN_PND_OM_VW1 view is a reporting object within the Oracle EBS Process Manufacturing Financials (GMF) product family. It exposes pending inventory transactions that originate from Oracle Order Management (OM), providing a consolidated read-only presentation of order-driven inventory movements that have not yet been fully processed or posted to the General Ledger. The view carries a VALID status in the APPS schema and is one of several IC_TRAN_PND-based views used to surface pending transaction data specific to a source application.

In Oracle EBS 12.1.1 and 12.2.2, this view plays an integration and reconciliation role. It joins pending transaction rows in IC_TRAN_PND to delivery, order management, and drop-ship source tables so that order fulfillment quantities can be reported alongside their inventory transaction context. The view is primarily consumed by reporting, reconciliation, and cost-of-goods analysis queries rather than by the transaction-processing engine itself.

Underlying Base Objects

The documented base objects referenced by this view are:

• IC_TRAN_PND (synonym) — the pending inventory transaction table that drives the view; filtered on DOC_TYPE = 'OMSO', COMPLETED_IND = 1, and DELETE_MARK = 0.
• WSH_NEW_DELIVERIES — delivery header information supplying the delivery name and delivery ID.
• WSH_DELIVERY_DETAILS — delivery line detail supplying shipped quantities and source line references.
• WSH_DELIVERY_ASSIGNMENTS — links delivery headers to delivery details.
• OE_ORDER_LINES_ALL — order line attributes including shipping quantity UOM, ordered quantity, and ship-from organization.
• OE_ORDER_HEADERS_ALL — order header data used in the bill-only branch for order number and header ID.
• OE_DROP_SHIP_SOURCES — drop-ship source information consumed in the UNION ALL drop-ship branch.

The view is constructed as a UNION ALL of several queries, each handling a distinct fulfillment scenario: standard delivery shipments, bill-only transactions (identified by LINE_DETAIL_ID = -999), and drop-ship flows. This structure reflects the multiple ways an OM order line can generate pending inventory activity.

Key Columns

• DOC_TYPE — document type; restricted to 'OMSO' (Order Management Sales Order) in this view.
• DOC_ID, LINE_ID, ITEM_ID — identifiers for the source document, line, and inventory item.
• ORGN_CODE, WHSE_CODE — organization and warehouse codes; bill-only rows expose a '(BILL-ONLY) ' prefixed organization code.
• GL_POSTED_IND — indicates whether the pending transaction has been posted to the General Ledger.
• NAME, DELIVERY_ID — the delivery name and ID for shipped rows, or the order number and header ID for bill-only rows.
• LINE_NO — source line number, or a composite line/shipment number for bill-only rows.
• TRANS_QTY_USAGE — the transaction quantity. Note that despite the name, this value is derived from SUM(WD.SHIPPED_QUANTITY) in the delivery branch and AVG(OL.ORDERED_QUANTITY) in the bill-only branch, not from a discrete quantity column on the pending table.
• TRANS_QTY_USAGE_UM — the unit of measure, taken from OL.SHIPPING_QUANTITY_UOM or OL.ORDER_QUANTITY_UOM.
• SHIP_FROM_ORG_ID — the shipping organization for the order line.

Common Use Cases and Queries

Because the view surfaces pending, order-originated transactions with associated quantities, it is commonly used to reconcile shipped quantities against pending inventory activity and to inspect unposted OM-driven transactions. A representative query filtered on the TRANS_QTY_USAGE column follows:

• Reconciling delivered order lines to pending inventory transactions by delivery and item.
• Identifying OM pending transactions that have not yet posted to the GL (GL_POSTED_IND = 'N').
• Aggregating pending usage quantities by item or organization for COGS and costing analysis.

Sample SQL:

SELECT item_id,
       orgn_code,
       gl_posted_ind,
       SUM(trans_qty_usage) total_trans_qty_usage,
       trans_qty_usage_um
FROM   apps.ic_tran_pnd_om_vw1
WHERE  gl_posted_ind = 'N'
GROUP BY item_id, orgn_code, gl_posted_ind, trans_qty_usage_um;

Analysts should note that the row-level meaning of TRANS_QTY_USAGE varies by branch of the underlying UNION ALL; validation against the base delivery and order tables is recommended before using the quantity for reconciliation.