Search Results msc_resources_tree_v




Overview
Explore in AI Chat

Overview

MSC_RESOURCES_TREE_V is a reporting view owned by the APPS schema in Oracle E-Business Suite, defined within the MSC (Advanced Supply Chain Planning) product family. As its name and description indicate, the view presents "all the resources in the tree" — that is, it consolidates resources, departments, and related planning entities into a unified, hierarchy-oriented result set that mirrors the resource structure used by Advanced Supply Chain Planning (ASCP). The object is recorded as VALID and documented in ETRM for both release 12.1.1 and 12.2.2, confirming its continued availability across the two application versions.

Functionally, the view serves as a denormalized access layer over planning data. Rather than forcing report developers and integrators to reconcile three distinct source queries against department resources, inter-org shipping methods, and supplies, MSC_RESOURCES_TREE_V exposes them through a single consistent column projection. This makes it suitable for both ad-hoc SQL reporting and programmatic integration where a stable resource-and-department listing is required per plan and source instance.

Underlying Base Objects

The documented referenced base objects are:

The view text is a three-branch UNION ALL. The first branch selects department-level resource rows directly from MSC_DEPARTMENT_RESOURCES, producing the core resource listing. The second branch derives inter-organization shipping method entries from MSC_INTERORG_SHIP_METHODS, invoking MSC_GET_NAME.ORG_CODE(FROM_ORGANIZATION_ID, SR_INSTANCE_ID) to resolve an organization code and assigning a LINE_FLAG of 3 while nulling the batchable flag. The third branch joins MSC_SUPPLIES to MSC_DEPARTMENT_RESOURCES on LINE_ID = DEPARTMENT_ID (plus organization, source instance, PLAN_ID = -1, RESOURCE_ID = -1, and non-null LINE_ID), emitting distinct line-level rows with a LINE_FLAG of 1. The SYNONYM designations indicate that the underlying MSC tables are accessed through APPS synonyms in the EBS data model.

Key Columns

The view exposes fourteen columns:

  • PLAN_ID — identifies the ASCP plan (or the sentinel value -1 for unplanned/template data) to which the row belongs.
  • SR_INSTANCE_ID — the source (instance) identifier for the planning data.
  • ORGANIZATION_ID — the inventory organization context of the resource or entity.
  • DEPARTMENT_CLASS — classification of the department associated with the resource.
  • RESOURCE_GROUP_CODE / RESOURCE_GROUP_NAME — the resource group key and its descriptive name.
  • DEPARTMENT_ID / DEPARTMENT_CODE / DEPT_DESCRIPTION — department identifiers and descriptive text. For inter-org rows these are populated from organization/shipping data.
  • RESOURCE_ID / RESOURCE_CODE / RES_DESCRIPTION — the resource identifiers; RESOURCE_ID of -1 is used in the supplies branch.
  • LINE_FLAG — discriminator indicating the row's source type (1 = supplies/line-derived, 3 = inter-org shipping method).
  • BATCHABLE_FLAG — indicates whether the resource is batchable; cast to NULL in the inter-org branch.

Common Use Cases and Queries

Typical usages include resource-tree exploration reports, ASCP department-to-resource listings, and inter-org shipping-method analysis. Filtering on LINE_FLAG isolates a specific branch's semantics.

All departments and resources for a plan:

  • SELECT PLAN_ID, ORGANIZATION_ID, DEPARTMENT_CODE, RESOURCE_CODE, RESOURCE_DESCRIPTION FROM MSC_RESOURCES_TREE_V WHERE PLAN_ID = :p_plan_id AND SR_INSTANCE_ID = :p_sr_instance ORDER BY ORGANIZATION_ID, DEPARTMENT_CODE, RESOURCE_CODE;

Inter-org shipping-method resources (LINE_FLAG = 3):

  • SELECT ORGANIZATION_ID, RESOURCE_CODE, RES_DESCRIPTION FROM MSC_RESOURCES_TREE_V WHERE LINE_FLAG = 3 AND SR_INSTANCE_ID = :p_sr_instance;

Distinct resource groups:

  • SELECT DISTINCT RESOURCE_GROUP_CODE, RESOURCE_GROUP_NAME FROM MSC_RESOURCES_TREE_V WHERE RESOURCE_GROUP_CODE IS NOT NULL;

Because the view aggregates multiple logical branches, queries should always constrain PLAN_ID and SR_INSTANCE_ID to avoid cross-plan ambiguity, and use LINE_FLAG where branch-specific rows are intended.