Results for “msc_flp_onhand_v”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

MSC_FLP_ONHAND_V is a read-only database view owned by the APPS schema within the MSC (Advanced Supply Chain Planning) product family in Oracle E-Business Suite 12.1.1 and 12.2.2. Its name denotes its function: "FLP" refers to the Full Pegging engine used by Advanced Supply Chain Planning, and "ONHAND" indicates that the view surfaces on-hand supply records together with their demand-side pegging relationships. The view is designed to present a flattened, human-readable projection of planning data, joining supply records to their corresponding demand records through the MSC_FULL_PEGGING table.

Rather than exposing raw planning tables, the view applies numerous database function calls from the MSC_GET_NAME package to translate internal numeric codes into meaningful values. Dates are resolved through DEMAND_DATE and SUPPLY_DATE functions, organization codes through ORG_CODE, item identification through ITEM_NAME and ITEM_DESC, and project/task references through PROJECT and TASK functions. Supply types are converted to descriptive text through LOOKUP_MEANING, which is central to the user's interest in the supply_type column. This view supports reporting and integration scenarios where planners or custom applications need to examine supply/demand matching without navigating the normalized planning schema directly.

Underlying Base Objects

According to documented ETRM metadata for 12.2.2, the view references the following base objects:

• MSC_FULL_PEGGING (synonym) — the primary source, aliased within the view as MFP2, MFP3 (and MFP4/MFP5 in the full definition). Supplies and their downward pegging relationships originate here.
• MSC_SUPPLIES (synonym) — aliased as MR2, supplying order-level attributes such as NEW_ORDER_QUANTITY, PROJECT_ID, TASK_ID, and ORGANIZATION_ID.
• MSC_DEMANDS (synonym) — supplying demand-side attributes and origination information.
• MSC_ITEMS (synonym) — aliased as MIF2 and MIF3 to resolve ITEM_NAME for both the supply and demand sides.
• MSC_GET_NAME (package) — provides the scalar functions used throughout the SELECT list.

The joins are driven by PEGGING_ID, PLAN_ID, ORGANIZATION_ID, INVENTORY_ITEM_ID, DEMAND_ID, and SR_INSTANCE_ID, which together form the composite keys linking planning records across these objects.

Key Columns

• PLAN_ID — identifies the planning instance to which the record belongs.
• ORGANIZATION_ID — the inventory organization in which the supply resides.
• INVENTORY_ITEM_ID and ITEM_NAME/ORG_CODE — internal item key plus a concatenated display value built via MSC_GET_NAME.
• PEGGING_ID / PREV_PEGGING_ID — the pegging chain references that link supply to demand.
• SUPPLY_TYPE — the numeric supply classification. It appears directly as a column and is also translated via MSC_GET_NAME.LOOKUP_MEANING('MRP_FLP_SUPPLY_DEMAND_TYPE', MFP2.SUPPLY_TYPE), producing the descriptive supply type text commonly reported.
• DEMAND_QUANTITY / ALLOCATED_QUANTITY — demand-side quantity and the portion allocated to the supply, both rounded to two decimals.
• END_ITEM_USAGE — used in a DECODE to derive an allocation ratio.
• Supply date — resolved via MSC_GET_NAME.SUPPLY_DATE or DEMAND_DATE depending on whether the demand identifier equals -1 (supply-only) or otherwise.
• Project / Task — resolved through MSC_GET_NAME.PROJECT and MSC_GET_NAME.TASK.

Common Use Cases and Queries

The most frequent use is investigating supply pegging composition, particularly filtering or grouping by supply_type. The following query lists on-hand supply records for a plan, showing the descriptive supply type:

SELECT plan_id,
       organization_id,
       inventory_item_id,
       supply_type,
       MSC_GET_NAME.LOOKUP_MEANING('MRP_FLP_SUPPLY_DEMAND_TYPE', supply_type) supply_type_meaning,
       demand_quantity,
       allocated_quantity
  FROM apps.msc_flp_onhand_v
 WHERE plan_id = :p_plan_id
   AND organization_id = :p_org_id;

Because the view already applies the lookup translation, reports can group by the derived text. A second scenario aggregates allocated quantity by supply type to validate supply usage:

SELECT MSC_GET_NAME.LOOKUP_MEANING('MRP_FLP_SUPPLY_DEMAND_TYPE', supply_type) supply_type_meaning,
       ROUND(SUM(allocated_quantity), 2) total_allocated
  FROM apps.msc_flp_onhand_v
 WHERE plan_id = :p_plan_id
 GROUP BY MSC_GET_NAME.LOOKUP_MEANING('MRP_FLP_SUPPLY_DEMAND_TYPE', supply_type);

Performance is a consideration: because each row invokes multiple MSC_GET_NAME function calls, queries returning large pegging populations can be resource-intensive. Restricting by PLAN_ID and ORGANIZATION_ID and avoiding unnecessary columns reduces the function invocations. The view is read-only; all maintenance is performed by the planning engine against the underlying MSC tables.