Search Results sub_inv_code

Overview

The view MRP_AP_ONHAND_TRX_SUPPLIES_V belongs to the Oracle EBS Master Scheduling/MRP (MRP) product family and forms part of the Oracle Advanced Supply Chain Planning (ASCP) and Master Demand Schedule (MDS) data extraction layer. It exposes on-hand and transaction-based supply records that the MRP planning engine consumes during a planning run. Specifically, the view reconciles on-hand quantities held in inventory — aggregated from subinventory, locator, lot, and serial-level detail — against open project/task supply and discrete transaction source references. It is one of a family of "MRP_AP_" helper views (the _SN suffix denotes a pre-processed snapshot used during plan generation) that materialize the internal planning workspace from the operational transaction tables before the memory-based planner consumes them.

The view is classified in the ETRM repository as a multi-branch UNION ALL query, sourcing its rows entirely from other MRP planning snapshot objects rather than the standard inventory transaction tables directly. Its primary role is to feed the planner with a normalized list of supply elements — quantity by item, organization, subinventory, locator, and serial — annotated with planning group and ranking information.

Underlying Base Objects

The documented view text references the following base objects (all in the MRP snapshot schema):

• MRP_AP_MTRX_TMP_SN — transaction matrix temporary snapshot; supplies the organization, item, subinventory, locator, serial number, project, task, and primary quantity for each supply row.
• MRP_AP_SYS_ITEMS_SN — system items snapshot; joined on organization and inventory item, filtered by EFFECTIVITY_CONTROL = 1. This is the object most closely associated with the user's search term mrp_ap_sys_items_sn.
• MRP_AP_PRJ_PARAS_SN — project parameters snapshot; outer-joined on organization and project to supply project-level planning parameters and grouping.
• MRP_AP_MRP_PARAS_SN — MRP parameters snapshot; joined on organization to supply planning group and rank columns.
• MTL_ITEM_LOCATIONS_KFV — key flexfield view providing the concatenated LOCATOR_NAME.

Note that the ETRM metadata records no base tables as formally documented for this view and marks it "Not implemented in this database" in the source instance, meaning the definition is provided for reference only and must be validated against the target environment.

Key Columns

• ORGANIZATION_ID, INVENTORY_ITEM_ID — planning entity keys.
• SUB_INV_CODE, LOCATOR_ID, LOCATOR_NAME — physical location of the supply.
• SERIAL_NUMBER — serialized supply reference.
• PROJECT_ID, TASK_ID — project/task association for project supply.
• PLANNING_GROUP — grouping used by the planner for netting.
• END_ITEM_UNIT_NUMBER — pulled via PJM_UNIT_EFF.RCV_UNIT_NUMBER or WIP_UNIT_NUMBER depending on transaction source; NULL in the first union branch.
• QUANTITY — summed PRIMARY_QUANTITY.
• SOURCE_TYPE — literal discriminator (3 for project supply, 4 for transaction-derived supply).
• RN1–RN4 — rank columns used internally for deduplication and ordering.

Common Use Cases and Queries

Typical usage is diagnostic: confirming that on-hand balances and transaction supplies are visible to the planner, or reconciling planning snapshots against on-hand queries.

SELECT organization_id, inventory_item_id, sub_inv_code,
       locator_name, serial_number, quantity, source_type
FROM   mrp_ap_onhand_trx_supplies_v
WHERE  organization_id = :org_id
AND    inventory_item_id = :item_id;

SELECT source_type, SUM(quantity)
FROM   mrp_ap_onhand_trx_supplies_v
GROUP BY source_type;

Because the view depends on the MRP snapshot tables, results are only meaningful during or immediately after a planning run; outside that window the snapshot tables may be empty or stale. It should not be used as a substitute for transactional on-hand reporting from MTL_ONHAND_QUANTITIES.