Search Results mrp_ap_sys_items_sn




Overview
Explore in AI Chat

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

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.