Search Results msc_vmi_item_v




Overview
Explore in AI Chat

Overview

MSC_VMI_ITEM_V is a read-only database view owned by the APPS schema in Oracle E-Business Suite 12.1.1 and 12.2.2. It belongs to the MSC product family, Advanced Supply Chain Planning, and exposes the set of inventory items for which Vendor Managed Inventory (VMI) is enabled within a specific planning context. Because the view is defined as a simple, denormalized projection over supplier and item planning data, it is primarily used for reporting, extract, and integration purposes rather than for transactional processing. Third-party VMI portals, supplier collaboration dashboards, and custom concurrent programs frequently reference this view to reconcile which items a supplier is responsible for replenishing. The view is registered as VALID in the ETRM data dictionary, confirming that its definition compiles successfully against the referenced base objects.

Underlying Base Objects

The view is defined over two MSC synonyms: MSC_ITEM_SUPPLIERS and MSC_ITEMS. The ETRM metadata also identifies MSC_SYSTEM_ITEMS and the MSC_X_UTIL package as referenced objects, which are used internally for planning context and utility resolution. The view text joins MSC_ITEM_SUPPLIERS (aliased I) to MSC_ITEMS (aliased S) on INVENTORY_ITEM_ID, filtering with the predicate I.PLAN_ID = -1 AND I.VMI_FLAG = 1. The literal PLAN_ID of -1 represents the non-planning or unplanned context, meaning the row set is not tied to a named plan run. The VMI_FLAG = 1 predicate restricts output to items explicitly flagged as vendor-managed. Because the SELECT uses DISTINCT, duplicate item-supplier combinations arising from multiple supplier sites are collapsed. No joins to inventory or purchasing base tables are performed, so item descriptions, costs, and on-hand balances are not available from this view alone.

Key Columns

  • PLAN_ID — Numeric plan identifier; in this view it is always -1 due to the filter predicate.
  • SR_INSTANCE_ID — Source system instance identifier used to scope records to a specific source instance in multi-instance or data-hub environments.
  • ORGANIZATION_ID — Inventory organization to which the VMI item belongs.
  • INVENTORY_ITEM_ID — Surrogate key of the item; the primary join column between MSC_ITEM_SUPPLIERS and MSC_ITEMS.
  • SUPPLIER_ID and SUPPLIER_SITE_ID — Identifiers of the vendor and vendor site that manages the item under the VMI agreement.
  • ITEM_NAME — Display name of the item, sourced from MSC_ITEMS.
  • PLANNER_CODE — Planner responsible for the item, used for workload routing and buyer/planner analytics.

Common Use Cases and Queries

Typical usage includes validating supplier-item mappings before loading VMI agreements, generating supplier-facing item catalogs, and auditing planner assignment coverage. Because PLAN_ID is fixed at -1, no plan parameter is required at runtime. A representative query returning all VMI items with supplier and planner detail follows:

SELECT organization_id, inventory_item_id, item_name,
       supplier_id, supplier_site_id, planner_code
  FROM apps.msc_vmi_item_v
 WHERE organization_id = :p_org_id
 ORDER BY supplier_id, item_name;

To count VMI items per supplier, aggregate over SUPPLIER_ID. To identify items missing planner assignment, filter on planner_code IS NULL. Note that the DISTINCT clause can hide multiple supplier sites for the same supplier-item pair; when site-level granularity is required, query MSC_ITEM_SUPPLIERS directly and reapply the VMI_FLAG predicate. Query performance is generally acceptable for reporting volumes, but filtering on ORGANIZATION_ID is recommended in large multi-org deployments.