Search Results msc_flp_demand_supply_v2




Overview
Explore in AI Chat

Overview

MSC_FLP_DEMAND_SUPPLY_V2 is an APPS-owned database view within the Oracle Advanced Supply Chain Planning (MSC) module. It exposes the demand and supply pegging structure held in the planner's full pegging tables, presenting horizontal demand-to-supply relationships alongside derived usage and date attributes. The view's "V2" designation distinguishes it from earlier revisions of the full-pegging demand/supply presentation, and it is the object surfaced in the Oracle ETRM reference library for release 12.2.2.

The view's principal analytical contribution is the ratio it computes between an allocated quantity and the end item usage quantity. This ratio (returned as a rounded decimal) expresses how much of a given component allocation is consumed per unit of the end assembly, making the view particularly relevant to users who search on the term end_item_usage. In practice the view serves as a read-only reporting and integration layer over the pegging engine's output, suitable for custom reports, ad hoc SQL, and downstream extract processes that must not touch the underlying planning tables directly.

Underlying Base Objects

The documented base objects referenced by the view are:

The view joins the pegging rows to MSC_DEMANDS and MSC_SUPPLIES (aliased MR2, MR3, MGR2 and MGR3 in the view text) to obtain order quantities, schedule dates, order types and numbers, and using-requirement quantities. Because MSC_FULL_PEGGING can hold multiple rows for a given demand, the view deliberately aliases that table twice, producing both the demand-level context and the corresponding supply-level context in a single row.

Key Columns

The most significant columns exposed are:

  • END_ITEM_USAGE — the per-assembly usage quantity against which allocation is measured; used directly in the ratio calculation and in the DECODE that guards against division by null.
  • ALLOCATED_QUANTITY — the quantity of the component pegged to the demand, rounded to two decimals.
  • Usage ratio — DECODE(END_ITEM_USAGE, NULL, 0, ROUND(ALLOCATED_QUANTITY/END_ITEM_USAGE, 2)); returns zero when usage is null, otherwise the allocated quantity per unit of end item.
  • PLAN_ID, ORGANIZATION_ID, PEGGING_ID, PREV_PEGGING_ID, DEMAND_ID, TRANSACTION_ID — the planning and pegging keys that establish row identity.
  • INVENTORY_ITEM_ID with the concatenated item name and organization code.
  • NEW_ORDER_QUANTITY, NEW_SCHEDULE_DATE, ORDER_TYPE, ORDER_NUMBER — supply-side scheduling attributes.
  • USING_REQUIREMENT_QUANTITY — the quantity of the parent requirement that the supply is consumed by.
  • Demand and supply dates — resolved through MSC_GET_NAME.DEMAND_DATE and MSC_GET_NAME.SUPPLY_DATE.
  • Project and task — resolved through MSC_GET_NAME.PROJECT and MSC_GET_NAME.TASK.
  • Lookup meanings — MRP_ORDER_TYPE, MRP_FLP_SUPPLY_DEMAND_TYPE and MRP_DEMAND_ORIGINATION decoded to display names.

Common Use Cases and Queries

Typical scenarios include component-usage analysis per end assembly, pegging trace reports, and extract feeds for planning dashboards. To locate rows where the end item usage is populated and inspect the derived ratio:

SELECT plan_id, organization_id, pegging_id, demand_id,
       inventory_item_id, end_item_usage,
       ROUND(allocated_quantity, 2) AS allocated_quantity
FROM   apps.msc_flp_demand_supply_v2
WHERE  end_item_usage IS NOT NULL
AND    plan_id = :plan_id;

To identify demands whose usage ratio exceeds a threshold, the ratio can be recomputed or filtered in an outer query. Because the view text derives the ratio as an anonymous expression, users should replicate the DECODE logic rather than expect a named column. Reports joining this view back to MSC_ITEMS or to planning plan names should filter by PLAN_ID and ORGANIZATION_ID to keep result sets bounded. All access is read-only; no DML should be issued against the view or its synonyms.