Search Results msc_search_suppliers_v




Overview
Explore in AI Chat

Overview

MSC_SEARCH_SUPPLIERS_V is a database view owned by the APPS schema in Oracle E-Business Suite, residing within the MSC (Advanced Supply Chain Planning) product family. As documented in ETRM, the view "shows the search criteria for suppliers." It functions as a flattened, denormalized access layer over the planning data model, presenting a consistent row shape that downstream planning engines, search/list-of-values facilities, and custom reports can consume without joining the numerous underlying MSC tables themselves. The view is reported with a status of VALID and is present in both the 12.1.1 and 12.2.2 releases.

The view text is a UNION ALL of multiple SELECT branches, each producing an identical column signature. Because of this construction, every branch must emit the same number and data type of columns (hence the pervasive TO_NUMBER(NULL) and NULL placeholders that pad branches which do not populate a given attribute). This is a classic EBS "search view" pattern: a single row type is reused across heterogeneous planning contexts so that a caller can query one object regardless of whether the source relates to a plan-level, item-level, or supplier-level record.

Underlying Base Objects

Per the documented ETRM metadata, MSC_SEARCH_SUPPLIERS_V references the following objects:

The first branch returns only plan-level columns from MSC_PLANS_TREE_V. The second branch joins items, categories, suppliers, and trading partners to produce item/supplier-level rows. A further DISTINCT branch operates directly on MSC_ITEM_SUPPLIERS. All branches filter on PLAN_COMPLETION_DATE IS NOT NULL and DATA_COMPLETION_DATE IS NOT NULL, ensuring only fully compiled and loaded plans are exposed.

Key Columns

  • PLAN_ID / COMPILE_DESIGNATOR / PLAN_TYPE — identify the plan and its compilation instance.
  • PLANNED_INSTANCE_ID / PLANNED_ORGANIZATION — the planning instance and organization scope.
  • SR_INSTANCE_ID / ORGANIZATION_ID / ORGANIZATION_CODE — source system instance and inventory organization.
  • CATEGORY_SET_ID / SR_CATEGORY_ID / CATEGORY_NAME — category-set and category identifiers for item classification.
  • INVENTORY_ITEM_ID / ITEM_NAME — the planned item.
  • PLANNER_CODE / BUYER_ID / BUYER_NAME — planner and buyer responsibility assignments.
  • ABC_CLASS / ABC_CLASS_NAME — ABC classification of the item.
  • SUPPLIER_ID / PARTNER_NAME — the supplier, resolved from MSC_TRADING_PARTNERS (restricted to PARTNER_TYPE = 1).

Unused positions appear as NULL or TO_NUMBER(NULL), matching the uniform row contract described above.

Common Use Cases and Queries

The view is typically used to drive supplier search dialogs and planning reports. A representative query is:

SELECT plan_id, inventory_item_id, item_name,
       supplier_id, partner_name
  FROM apps.msc_search_suppliers_v
 WHERE plan_id = :p_plan_id
   AND organization_id = :p_org_id;

A second pattern restricts results to a planner or buyer:

SELECT DISTINCT item_name, planner_code, buyer_name
  FROM apps.msc_search_suppliers_v
 WHERE planner_code = :p_planner
   AND partner_name IS NOT NULL;

Because row counts can be large and multiple branches are combined, queries should filter on PLAN_ID, ORGANIZATION_ID, or SUPPLIER_ID and select DISTINCT where duplicates are possible. Developers should also guard against NULL placeholder columns when consuming the result set in custom code or BI Publisher reports.