Search Results msc_resource_items_tree_v




Overview
Explore in AI Chat

Overview

MSC_RESOURCE_ITEMS_TREE_V is a read-only database view owned by the APPS schema within Oracle E-Business Suite, delivered as part of the MSC (Advanced Supply Chain Planning) product family. It exposes the relationship between resources and the items they are associated with in the planning data model, joining resource requirement records, department resource definitions, supplies, and routings against system items. The view serves reporting and integration consumers that need a flattened, plan-scoped picture of how resources map to assemblies, supplies, and operation sequences without requiring the caller to navigate the numerous normalized planning tables directly.

The view is registered as VALID in ETRM metadata and is available in both the 12.1.1 and 12.2.2 releases. Because it is defined over MSC planning tables, the data it returns reflects the state of a specific plan instance rather than transactional execution data; the PLAN_ID and SR_INSTANCE_ID columns are therefore present on every row and are essential as query predicates.

Underlying Base Objects

The view is defined over eight documented base objects, all referenced through APPS synonyms: MSC_DEPARTMENT_RESOURCES, MSC_OPERATION_RESOURCES, MSC_OPERATION_RESOURCE_SEQS, MSC_RESOURCE_REQUIREMENTS, MSC_ROUTINGS, MSC_ROUTING_OPERATIONS, MSC_SUPPLIES, and MSC_SYSTEM_ITEMS. These correspond to the synonym sources MSC_DEPARTMENT_RESOURCES, MSC_OPERATION_RESOURCES, MSC_OPERATION_RESOURCE_SEQS and the remaining planning entities listed in the ETRM metadata.

The view text is a UNION ALL of two branches. The first branch joins MSC_SYSTEM_ITEMS to MSC_SUPPLIES on INVENTORY_ITEM_ID, ORGANIZATION_ID, SR_INSTANCE_ID and PLAN_ID, then joins MSC_RESOURCE_REQUIREMENTS to MSC_DEPARTMENT_RESOURCES on SR_INSTANCE_ID, PLAN_ID, ORGANIZATION_ID, DEPARTMENT_ID and RESOURCE_ID. The link to supplies is made through SUP.TRANSACTION_ID = REQ.SUPPLY_ID, and the branch restricts rows with NVL(REQ.PARENT_ID,2) = 2, isolating root-level resource requirements.

The second branch traverses the routing side of the model. MSC_ROUTINGS is joined to MSC_SYSTEM_ITEMS on ASSEMBLY_ITEM_ID, SR_INSTANCE_ID and ORGANIZATION_ID, then to MSC_ROUTING_OPERATIONS, MSC_OPERATION_RESOURCE_SEQS and MSC_OPERATION_RESOURCES. DISTINCT is applied in this branch to eliminate duplication introduced by the routing/resource expansion.

Key Columns

Common Use Cases and Queries

Typical consumers use this view to reconcile resource demand against item supply, to build resource-to-item cross references for planning extracts, or to feed custom dashboards. A basic filtered query follows:

  • SELECT resource_code, item_name, department_code FROM msc_resource_items_tree_v WHERE plan_id = :plan_id AND organization_id = :org_id;
  • Aggregate resource counts per item: SELECT inventory_item_id, COUNT(DISTINCT resource_id) FROM msc_resource_items_tree_v WHERE plan_id = :plan_id GROUP BY inventory_item_id;
  • Filter by resource group for capacity reporting: SELECT * FROM msc_resource_items_tree_v WHERE plan_id = :plan_id AND resource_group_name = :group;

Because the view performs multi-table joins and a UNION ALL with DISTINCT in one branch, queries should always constrain PLAN_ID, SR_INSTANCE_ID and ORGANIZATION_ID to avoid full scans across planning collections.