Search Results msc_search_projects_v




Overview
Explore in AI Chat

Overview

MSC_SEARCH_PROJECTS_V is an APPS-owned database view within the Advanced Supply Chain Planning (MSC) product family of Oracle E-Business Suite 12.1.1 and 12.2.2. Its documented purpose is to expose all search criteria associated with projects that participate in the planning process. The view is a UNION ALL construct, meaning it consolidates several distinct result sets into a single horizontally aligned structure, each branch contributing rows from a different planning context: plan-level records, organization-level records, and project-level records.

Because supply chain planning operates across plans, planned organizations, and planned instances, the "search" abstraction is valuable to both the planning engine and to external reporting. Rather than forcing a caller to query MSC_PLANS_TREE_V, MSC_ORGS_TREE_V, and MSC_PROJECTS independently, this view normalizes them into a consistent column layout. The recurring filter across branches is PLAN_COMPLETION_DATE IS NOT NULL AND DATA_COMPLETION_DATE IS NOT NULL, which restricts output to plans whose planning run has completed and whose data has been fully loaded. This makes the view safe for read-only inquiry after plan completion, rather than mid-refresh. In Oracle EBS, such views are typically consumed by OAF-based plan search pages, concurrent programs, and downstream BI Publisher or Discoverer reports.

Underlying Base Objects

The ETRM 12.2.2 metadata documents the following referenced base objects:

Each branch selects the same positional column list, using TO_NUMBER(NULL) and NULL as placeholders so that unrelated columns collapse cleanly. MSC_PLANS_TREE_V contributes the shared plan context; the organization and project joins add their specific discriminators.

Key Columns

  • PLAN_ID — the planning instance key; present in all branches and the primary correlation column.
  • COMPILE_DESIGNATOR / PLAN_TYPE — identify the plan by compile designator and plan type (e.g., unconstrained, constrained).
  • PLANNED_INSTANCE_ID and PLANNED_ORGANIZATION — the planned instance and planned organization associated with the plan.
  • SR_INSTANCE_ID / ORGANIZATION_ID / ORGANIZATION_CODE — populated in the organization branch, identifying the source instance and inventory organization.
  • PLANNING_GROUP — populated in the projects branch, representing the grouping criterion applied to projects during planning.
  • NULL / TO_NUMBER(NULL) placeholders — positional columns retained solely to align union branches; not meaningful data.

Common Use Cases and Queries

The most frequent scenario is driving a plan or project search list, particularly where the user query concerned planned_organization. Typical query:

  • SELECT DISTINCT plan_id, compile_designator, planned_organization FROM msc_search_projects_v WHERE planned_organization IS NOT NULL;
  • SELECT plan_id, organization_code FROM msc_search_projects_v WHERE organization_code = 'M1'; — retrieves the organization-scoped search criteria for a given org.
  • SELECT plan_id, planning_group FROM msc_search_projects_v WHERE planning_group IS NOT NULL; — lists project planning groups available as search criteria.

Because the view is read-only and filtered on completed plans, it is appropriate for reporting joins to MSC_PLANS_TREE_V and for populating LOVs. Analytical consumers can rely on it to enumerate every valid combination of plan, planned organization, and project that the planning application recognizes as a searchable entity. Its UNION ALL structure means duplicate PLAN_ID values may appear across different criteria scopes, so DISTINCT or an explicit discriminator column should be used where uniqueness is required.