Search Results msc_search_orgs_v




Overview
Explore in AI Chat

Overview

MSC_SEARCH_ORGS_V is a read-only database view owned by the APPS schema within the MSC (Advanced Supply Chain Planning) product family of Oracle E-Business Suite. Its documented purpose is to expose the full set of organization-level search criteria available to the planning engine, presenting plan context alongside the organization, product family, and related planning entities that a user may select when narrowing a plan scope. The view is therefore a reporting and integration surface rather than a transactional object: it consolidates selection criteria that planning forms, concurrent programs, and downstream reports consult when a plan is defined or refreshed.

Because it is a view rather than a table, MSC_SEARCH_ORGS_V carries no stored data of its own. Its rows are materialized at query time from the plan tree and organization tree views that the planning schema maintains after a plan has been compiled and its data collected, which makes it inherently dependent on plan completion state.

Underlying Base Objects

The view text shows a UNION ALL over three query branches, each anchored on MSC_PLANS_TREE_V and gated by the predicates PLAN_COMPLETION_DATE IS NOT NULL and DATA_COMPLETION_DATE IS NOT NULL. Only plans that have completed both compilation and data collection contribute rows. The first branch supplies plan-level rows only; the second joins MSC_ORGS_TREE_V to the plan tree by PLAN_ID to emit organization rows; the third joins MSC_PRODUCT_FAMILIES_TREE_V to the plan tree by PLAN_ID to emit product-family rows. Columns not populated by a given branch are padded with TO_NUMBER(NULL) or NULL so that the union remains type-consistent.

The documented metadata lists additional referenced objects, including MSC_SYSTEM_ITEMS, MSC_BOM_COMPONENTS, MSC_ITEM_CATEGORIES, MSC_ITEM_SUPPLIERS, MSC_TRADING_PARTNERS, MSC_RESOURCES_TREE_V, and the MSC_GET_NAME package. These reflect the broader MSC search infrastructure and the item, category, supplier, trading-partner, and resource criteria that planning searches can accept; MSC_GET_NAME is used for name resolution and display formatting.

Key Columns

The remaining positions are generic search-criteria placeholders, returned as NULL in the branches that do not populate them.

Common Use Cases and Queries

Typical usage is to enumerate the organizations and product families that are valid search entries for completed plans, either for a user interface list of values or for a diagnostic report.

List all organizations eligible for search across completed plans:

  • SELECT plan_id, compile_designator, organization_id, organization_code FROM msc_search_orgs_v WHERE organization_id IS NOT NULL ORDER BY plan_id, organization_code;

List product families available for a specific plan designator:

  • SELECT plan_id, product_family_id, pf_number FROM msc_search_orgs_v WHERE compile_designator = :plan AND product_family_id IS NOT NULL;

Because rows appear only after both plan and data completion, an empty result for a given plan generally indicates an incomplete compilation or collection cycle, making the view useful for validating plan readiness before executing downstream planning queries.