Search Results planned_instance_id




Overview
Explore in AI Chat

Overview

APPS.MSC_SEARCH_PROJECTS_V is a reporting and integration view within the Oracle Advanced Supply Chain Planning (ASCP) schema. It consolidates project-level and organization-level supply chain planning context into a single, flat result set that can be queried for search, lookup, and downstream integration purposes. The view does not store data; it is a UNION ALL construct that projects the same logical column layout across multiple contributing sources. Its principal role is to expose plan identifiers, planning instance context, and project or organization attributes in a uniform structure, allowing callers to retrieve hierarchy metadata without joining the individual tree views directly.

The view is defined strictly over pre-existing planning tree and project objects, and its output is filtered so that only plans with completed plan and data compilation are returned. This makes it suitable for reporting against plans that have finished their planning run rather than in-flight plan definitions.

Underlying Base Objects

The documented base objects referenced by APPS.MSC_SEARCH_PROJECTS_V are MSC_ORGS_TREE_V (VIEW), MSC_PLANS_TREE_V (VIEW), MSC_PROJECTS (SYNONYM), MSC_PROJECT_TASKS (SYNONYM), and MSC_PROJECT_TASK_ITEMS_TREE_V (VIEW). Each branch of the UNION ALL draws from one or more of these sources:

  • MSC_PLANS_TREE_V supplies the core plan attributes: plan_id, compile_designator, plan_type, planned_instance_id, and planned_organization.
  • MSC_ORGS_TREE_V contributes organization-level rows (sr_instance_id, organization_id, organization_code) joined to plans by plan_id.
  • MSC_PROJECTS provides project rows, including sr_instance_id, organization_id, and planning_group.
  • MSC_PROJECT_TASKS and MSC_PROJECT_TASK_ITEMS_TREE_V extend the union to project task and task item granularity.

Because the contributing objects are themselves views or synonyms, MSC_SEARCH_PROJECTS_V is a purely derived construct layered on top of the ASCP planning hierarchy.

Key Columns

The view exposes a consistent column set across every branch of the union. Of these, the following are most significant:

  • plan_id — Identifier of the ASCP plan, the primary join key against MSC_PLANS_TREE_V and the other contributing sources.
  • planned_instance_id — The planning instance (application instance) associated with the plan, used to scope results to a specific instance context. This is the column most frequently searched by users.
  • planned_organization — The organization identifier for which the plan was compiled.
  • compile_designator — Designator identifying the compilation of the plan output.
  • plan_type — Classification of the plan (for example, unconstrained, constrained, or production plan variants).
  • sr_instance_id — Source instance identifier for organization or project rows.
  • organization_id / organization_code — Organization key and code populated for organization-sourced rows.
  • planning_group — Planning group attribute carried from MSC_PROJECTS for project rows.

Columns not relevant to a given branch are populated with NULL casts (for example, to_number(null)) to preserve the union column order.

Common Use Cases and Queries

Typical usage centers on resolving planned_instance_id and plan context for a given project, organization, or plan. A representative query filtering on the searched attribute is:

  • SELECT plan_id, planned_instance_id, planned_organization, plan_type FROM apps.msc_search_projects_v WHERE planned_instance_id = :instance_id;
  • Retrieving all plans and their planned organizations for reporting: SELECT DISTINCT plan_id, planned_organization, compile_designator FROM apps.msc_search_projects_v;
  • Joining to MSC_PLANS_TREE_V to enrich plan naming or completion metadata for compiled plans only (the view already restricts to plans with completed plan and data compilation).
  • Driving integration extracts that need a single source combining plan, organization, project, and task hierarchy context.

Because the view is read-only and derived, it is safe for ad-hoc querying and for use in concurrent program data sources where a consolidated planning hierarchy is required.