Search Results msc_search_resources_v




Overview
Explore in AI Chat

Overview

MSC_SEARCH_RESOURCES_V is a read-only database view owned by the APPS schema in Oracle E-Business Suite. It belongs to the MSC product family, Advanced Supply Chain Planning, and is delivered with a VALID status in both EBS 12.1.1 and 12.2.2. As its description states, the view exposes all search criteria for resources — that is, it presents the hierarchical navigation paths (plan, organization, product family, and resource) that a planner can traverse when locating a resource inside an Advanced Supply Chain Planning plan.

The view is not a transactional object. It functions as a reporting and integration surface that flattens a set of tree-based and search-helper objects into a single, uniform result set. Each row represents one node within the searchable resource hierarchy, with the remaining positional columns populated as NULL depending on the level of the node. Because it returns a stable set of columns for every level, the view simplifies dynamic LOV, flexfield, and concurrent-program queries that would otherwise need to union multiple tree views manually.

Underlying Base Objects

The documented base objects for MSC_SEARCH_RESOURCES_V are MSC_GET_NAME (PACKAGE), MSC_ORGS_TREE_V (VIEW), MSC_PLANS_TREE_V (VIEW), MSC_PRODUCT_FAMILIES_TREE_V (VIEW), MSC_RESOURCES_TREE_V (VIEW), MSC_RESOURCE_ITEMS_TREE_V (VIEW), and MSC_TRADING_PARTNERS (SYNONYM).

The view text is built as a UNION ALL across these tree views. The plan-level branch selects from MSC_PLANS_TREE_V, restricted by P.PLAN_COMPLETION_DATE IS NOT NULL and P.DATA_COMPLETION_DATE IS NOT NULL, so only plans whose data collection has completed are considered searchable. The organization branch joins MSC_ORGS_TREE_V to MSC_PLANS_TREE_V on PLAN_ID, the product family branch joins MSC_PRODUCT_FAMILIES_TREE_V on PLAN_ID, and additional branches cover resources and resource items. In every branch, columns that do not apply to that hierarchy level are projected as TO_NUMBER(NULL) or NULL to preserve a consistent column list. The MSC_GET_NAME package supplies decoded name values, while MSC_TRADING_PARTNERS supports partner-related search context.

Key Columns

  • PLAN_ID — Identifier of the supply chain plan the node belongs to.
  • COMPILE_DESIGNATOR — The plan's compile designator, used to distinguish plan versions.
  • PLAN_TYPE — Type of the plan (for example, unconstrained, constrained, or detailed).
  • PLANNED_INSTANCE_ID — Instance identifier of the planning run associated with the node.
  • PLANNED_ORGANIZATION — Organization context in which the plan is executed.
  • SR_INSTANCE_ID — Search-resource instance identifier for organization, product family, or resource nodes.
  • ORGANIZATION_ID / ORGANIZATION_CODE — Organization identifier and code for nodes below the plan level.
  • PRODUCT_FAMILY_ID / PF_NUMBER — Product family identifier and member number, populated only at the product family level.

Common Use Cases and Queries

The view is typically used to drive resource search lists, LOVs, and conversion routines that must resolve a resource or product family within a specific plan. A basic query retrieving all search criteria for a plan is:

SELECT plan_id, compile_designator, plan_type, organization_id, organization_code
FROM apps.msc_search_resources_v
WHERE plan_id = :p_plan_id;

To restrict results to organization-level nodes only, filter on a non-null ORG identifier:

SELECT plan_id, organization_id, organization_code
FROM apps.msc_search_resources_v
WHERE plan_id = :p_plan_id
AND organization_id IS NOT NULL;

Because the view enforces completed plan and data collection dates, callers can rely on it for consistent, searchable data without additional validation. It is also useful in concurrent programs and OAF pages that must present a unified resource picker across plan, organization, product family, and resource levels.