Search Results msc_bis_inv_detail




Overview
Explore in AI Chat

Overview

MSC_BIS_INV_DETAIL is a planning table in the MSC schema, owned by Oracle Advanced Supply Chain Planning (ASCP). It stores inventory balance details that the planning engine collects from source applications, principally Oracle Inventory, Work in Process, Purchasing, and Order Management. The table functions as a staging and analytical store: the collections process populates it with supply chain balances for a given plan, and the planner and other planning routines read it to compute key performance indicators such as inventory value, service level, carrying cost, and demand penalty cost.

The name follows the MSC_BIS prefix used for Business Intelligence and Supply Chain Intelligence tables in the Advanced Planning suite. Because the rows are keyed by plan, organization, instance, item, and detail date, the table behaves as an analytical fact store rather than a transactional ledger. Rows are refreshed or replaced each time the plan collects, so the table reflects a point-in-time snapshot rather than an auditable history.

From a data vault modeling perspective, the metadata classifies this object heuristically as a standalone table. No foreign keys besides the reference to WMS_ZONES_B are documented, which suggests the table should be modeled as an isolated fact-style structure rather than as a dependent satellite. Any dimensional modeling would be applied through the plan, organization, and item reference columns rather than through enforced referential constraints.

Key Information Stored

With 54 documented columns, MSC_BIS_INV_DETAIL is a wide analytical table. The following columns carry the greatest reporting significance:

The table does not have a documented surrogate primary key or unique business index in the supplied metadata. In practice, rows are distinguished by the combination of PLAN_ID, ORGANIZATION_ID, SR_INSTANCE_ID, DETAIL_DATE, and INVENTORY_ITEM_ID, and in some cases PROJECT_ID and TASK_ID. This composite should be treated as the business-key candidate for deduplication and joins. Audit columns such as LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, and REQUEST_ID follow the standard Oracle Applications who-columns pattern and trace the collection program that wrote each row.

Common Use Cases and Queries

The most frequent scenario is inventory valuation reporting across a plan horizon. Planners select by plan and organization, then aggregate inventory value at the item or category level. A representative query sums valuation measures for a single plan and date:

SELECT ORGANIZATION_ID, INVENTORY_ITEM_ID, SUM(INVENTORY_VALUE) INV_VALUE, SUM(INVENTORY_QUANTITY) INV_QTY FROM MSC.MSC_BIS_INV_DETAIL WHERE PLAN_ID = :plan_id AND DETAIL_DATE = :detail_date GROUP BY ORGANIZATION_ID, INVENTORY_ITEM_ID;

A second use case compares posted and unposted valuation to reconcile planning values against the Inventory accounting ledger. The INVENTORY_VALUE_POST and INVENTORY_VALUE_NO_POST columns, together with the corresponding cost columns, support this comparison without touching the inventory subledger directly.

A third scenario evaluates total supply chain cost. Summing PRODUCTION_COST, PURCHASING_COST, CARRYING_COST, TRANSPORTATION_COST, and OTHER_COST yields the TOTAL_COST driver, which supports trade-off analyses between service level and cost. Service-level performance is analyzed through SERVICE_LEVEL, SERVICE_LEVEL_1, and SERVICE_LEVEL_2 in conjunction with DEMAND_PENALTY_COST.

Supplier and customer utilization analyses join SUPPLIER_ID or CUSTOMER_ID to their respective master tables, while ZONE_ID joins to WMS_ZONES_B for warehouse zone reporting. Because the table is plan-scoped and snapshot-based, all queries should filter on PLAN_ID and usually on DETAIL_DATE to avoid mixing collections.

Related Objects

The documented foreign key links ZONE_ID to WMS_ZONES_B, the warehouse zone base table, so zone-level reporting can be produced by joining those two objects on ZONE_ID. Beyond that enforced relationship, the table participates in logical joins to standard Advanced Supply Chain Planning and EBS master data:

  • MSC_BIS_INV_DETAIL.ZONE_ID → WMS_ZONES_B — the only documented foreign key, exposing warehouse zone attributes.
  • MTL_SYSTEM_ITEMS_B via INVENTORY_ITEM_ID and ORGANIZATION_ID — item descriptions, categories, and planning attributes.
  • MTL_PARAMETERS via ORGANIZATION_ID — organization name and inventory accounting defaults.
  • MSC_PLANS via PLAN_ID — the plan definition and horizon over which the collection was run.
  • MSC_SYSTEM_ITEMS and MSC_ITEM_CATEGORIES — planning-side item and category dimensions used for aggregation.
  • PO_VENDORS and PO_VENDOR_SITES_ALL via SUPPLIER_ID and SUPPLIER_SITE_ID — supplier and supplier site attributes.
  • HZ_CUST_ACCOUNTS and HZ_CUST_SITE_USES_ALL via CUSTOMER_ID and CUSTOMER_SITE_ID — customer and ship-to information.
  • FND_CONCURRENT_REQUESTS via REQUEST_ID — the collection program run that populated the rows.

These relationships are logical rather than enforced, which is consistent with the table's standalone data vault classification and its role as a denormalized planning fact store.