Results for “create_transaction_id”

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

Overview

MTL_ONHAND_QUANTITIES is an Oracle EBS Inventory (INV) view owned by the APPS schema. It presents a filtered, security-relevant projection of on-hand inventory balances: items that are owned, planned, and physically belonging to the same inventory organization. Unlike MTL_ONHAND_QUANTITIES_DETAIL, which stores the granular and unfiltered on-hand rows, this view exposes only the subset of records where IS_CONSIGNED = 2. In Oracle's on-hand data model, the IS_CONSIGNED flag distinguishes owned stock from consigned (supplier-owned) stock and is central to determining which quantities can be transacted against in the owning organization. The view therefore acts as the reporting and integration layer for owned on-hand balances across subinventories, locators, revisions, lots, cost groups, and projects.

For customers searching on DATE_RECEIVED, the view is particularly relevant: it is one of the primary Inventory objects exposing DATE_RECEIVED and ORIG_DATE_RECEIVED alongside transaction quantities, making it a natural starting point for receipt-dated on-hand reporting, lot dating analysis, and reconciliation of received inventory to subinventory and locator assignments.

Underlying Base Objects

The ETRM metadata documents that the view is defined over a single base object: MTL_ONHAND_QUANTITIES_DETAIL, referenced through a synonym. The view text confirms a direct selection with a restricting predicate, aliasing the base table as MOQD. Only one filter is applied: WHERE MOQD.IS_CONSIGNED = 2. No joins are performed, so every column exposed by the view is a column passed through from the detail table. Consequently, the view inherits the transaction-level grain of its base table — one row per combination of item, organization, subinventory, locator, revision, lot, cost group, project, task, and status — and inherits all of its transactional columns (primary and secondary quantities, transaction IDs, audit columns). Because the view is a synonym-backed, single-table view, Oracle does not materialize data; queries reflect current detail table contents in real time.

Key Columns

• INVENTORY_ITEM_ID / ORGANIZATION_ID — Primary keys identifying the item and the inventory organization in which the on-hand resides.
• DATE_RECEIVED / ORIG_DATE_RECEIVED — The receipt date and the original receipt date for the on-hand row. These are the columns most frequently used for aging analysis, lot dating, and receipt-date-driven reporting.
• TRANSACTION_QUANTITY — Exposed from the base column PRIMARY_TRANSACTION_QUANTITY; the primary on-hand quantity in the item's primary unit of measure.
• SECONDARY_UOM_CODE / SECONDARY_TRANSACTION_QUANTITY — Secondary unit of measure and corresponding quantity, where dual UOM control is enabled.
• SUBINVENTORY_CODE / LOCATOR_ID — The physical storage location within the organization.
• LOT_NUMBER / REVISION — Lot and revision identifiers for lot-controlled and revision-controlled items.
• COST_GROUP_ID — Cost grouping used for valuation and receiving accounting.
• PROJECT_ID / TASK_ID — Project and task references for project-referenced inventory.
• CONTAINERIZED_FLAG — Indicates whether the on-hand is held in a container (relevant to warehouse management and LPN flows).
• STATUS_ID — Material status applied to the on-hand, governing which transaction types are permitted.
• CREATE_TRANSACTION_ID / UPDATE_TRANSACTION_ID — Links the row to the inventory transaction that created and last updated it, supporting drill-back to transaction history.
• Audit columns — CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN.

Common Use Cases and Queries

Typical uses include owned on-hand reports, receipt-date aging, subinventory/locator stock listings, lot and revision holdings, and data feeds for planning or custom integrations. A representative query returning receipt-dated owned on-hand for an organization is:

SELECT inventory_item_id,
       organization_id,
       date_received,
       orig_date_received,
       subinventory_code,
       locator_id,
       lot_number,
       revision,
       transaction_quantity,
       status_id
FROM   apps.mtl_onhand_quantities
WHERE  organization_id = :org_id
AND    date_received >= TRUNC(:from_date)
ORDER BY date_received, subinventory_code;

The view is equally suitable for aggregation, such as totalling owned on-hand by subinventory, and for joining to MTL_SYSTEM_ITEMS_VL for descriptive item attributes. Because consigned stock is excluded by the IS_CONSIGNED = 2 predicate, queries requiring supplier-owned balances must target MTL_ONHAND_QUANTITIES_DETAIL directly or alternate views.