Search Results intransit_lead_time




Overview
Explore in AI Chat

Overview

APPS.MSD_CONSTRAINED_FORECAST_V is a reporting view in the Oracle EBS Advanced Supply Chain Planning (ASCP) and Enterprise Transaction Reporting (ETRM) schema. It exposes a constrained forecast derived from demand records, combining demand quantities with shipment and arrival dates calculated against planning schedules and intransit lead times. The view flattens data from demand, plan, item, trading partner, and dimensional level tables into a single denormalized result set suitable for reporting, analytics, and downstream integration.

Its primary role is to present forecast quantities at the intersection of organization, product, geography, and distribution center (DCS) levels, each tagged with a level identifier and level primary key. Because the arrival_date logic incorporates the intransit_lead_time attribute through NVL-based fallbacks, this view is frequently referenced when users investigate how intransit lead times influence constrained arrival dates within a plan.

Underlying Base Objects

The view is documented as being defined over the following base objects: FND_PROFILE (PACKAGE), MSC_DEMANDS (SYNONYM), MSC_PLANS (SYNONYM), MSC_SYSTEM_ITEMS (SYNONYM), MSC_TRADING_PARTNERS (SYNONYM), MSD_COMMON_UTILITIES (PACKAGE), MSD_CS_DATA (SYNONYM), MSD_CS_DEFINITIONS (SYNONYM), MSD_LEVEL_ASSOCIATIONS (SYNONYM), MSD_LEVEL_VALUES (SYNONYM), and DUAL (SYNONYM).

MSC_DEMANDS supplies the core demand rows, including quantity_by_due_date, request_date, promise_date, schedule_arrival_date, and the using_assembly_demand_date that anchors arrival computations. MSC_PLANS provides the compile_designator (aliased cs_name) and schedule_by attribute. MSC_SYSTEM_ITEMS and MSC_TRADING_PARTNERS are joined to resolve item and partner context. MSD_LEVEL_VALUES resolves organization, product, and DCS level keys, while MSD_COMMON_UTILITIES supplies helper functions such as get_lvl_pk_from_tp_id, get_loc_key, and get_dcs_key. MSD_CS_DATA, MSD_CS_DEFINITIONS, and MSD_LEVEL_ASSOCIATIONS support the constrained-supply (CS) dimensional framework, and FND_PROFILE resolves environment/profile settings used in the inline subquery.

Key Columns

  • plan_id — Identifier of the plan from which the demand is sourced.
  • cs_name — Compile designator of the plan (from MSC_PLANS).
  • org_level_value_pk / org_level_id — Organization dimension level key and level type.
  • prd_level_value_pk / prd_level_id — Product dimension level key and level type.
  • geo_level_value_pk / geo_level_id — Geography dimension key, level id fixed at 15.
  • dcs_level_value_pk / dcs_level_id — Distribution center level key, level id fixed at 34.
  • time_level_id — Time dimension level, fixed at 9.
  • quantity — Demand quantity by due date.
  • ship_date — Truncated using_assembly_demand_date.
  • arrival_date — Computed via DECODE on schedule_by, falling back to request_date, promise_date, schedule_arrival_date, or using_assembly_demand_date plus intransit_lead_time.

Common Use Cases and Queries

Typical usage includes building constrained forecast extracts for BI reporting, validating arrival-date derivation when intransit_lead_time is set, and reconciling demand quantities by dimension level.

  • Filter by plan to retrieve constrained forecast lines:
    SELECT plan_id, cs_name, prd_level_value_pk, quantity, ship_date, arrival_date FROM apps.msd_constrained_forecast_v WHERE plan_id = :p_plan_id;
  • Audit arrival-date behavior attributable to intransit lead time:
    SELECT plan_id, quantity, ship_date, arrival_date FROM apps.msd_constrained_forecast_v WHERE arrival_date > ship_date;
  • Aggregate forecast quantity by organization and product level:
    SELECT org_level_value_pk, prd_level_value_pk, SUM(quantity) FROM apps.msd_constrained_forecast_v GROUP BY org_level_value_pk, prd_level_value_pk;