Search Results d_cust

Overview

The MSD_CONSTRAINED_FORECAST_V view is a Demand Planning (MSD) reporting object owned by the APPS schema. As its ETRM description — "Constrained Plan View" — indicates, it exposes forecast demand quantities that have been generated within the constrained planning process, where supply and capacity limitations have been applied to the demand signal. In Oracle EBS 12.1.1 and 12.2.2, this view is used almost exclusively for reporting and downstream data extraction rather than for transactional processing. It presents demand records at a resolved planning level key, allowing report writers, Discoverer workbooks, and integration routines to query constrained forecast data without having to reconstruct the complex joins between demand, plan, item, trading partner, and level-value tables themselves. The view abstracts several internal constructs — level PK resolution, customer/organization key lookups, and status filtering — into a flat, query-friendly projection.

Underlying Base Objects

The view is defined over a set of documented base objects, all accessed through APPS synonyms where applicable:

• MSC_DEMANDS (synonym) — the primary source of demand rows; supplies plan, item, organization, customer instance, and quantity-by-due-date columns.
• MSC_PLANS (synonym) — provides COMPILE_DESIGNATOR, the plan designator exposed as CS_NAME.
• MSC_SYSTEM_ITEMS (synonym) — joined twice (as MSI and MSIA) for the demand item and its using assembly, enforcing item-level filters such as ATO_FORECAST_CONTROL and MRP_PLANNING_CODE.
• MSC_TRADING_PARTNERS (synonym) — referenced in the demand-to-partner join.
• MSD_LEVEL_VALUES (synonym) — aliased three times as D_ORG, D_ITM, and D_CUST to resolve organization, product, and customer level primary keys.
• MSD_COMMON_UTILITIES (package) — supplies GET_LOC_KEY, used to derive a default customer/location key when no customer level is present.
• MSD_LEVEL_ASSOCIATIONS, MSD_CS_DATA, MSD_CS_DEFINITIONS (synonyms) — listed as referenced base objects supporting level and planning structure resolution.
• FND_PROFILE (package) and DUAL (synonym) — supporting objects referenced during evaluation.

The inner query is wrapped by an outer SELECT that renames and projects the resolved columns from the aliased TOTAL inline view.

Key Columns

The view exposes the following principal columns:

• PLAN_ID — identifier of the owning plan; records with PLAN_ID = -1 are excluded.
• CS_NAME — the plan compile designator, serving as a human-readable plan label.
• ORG_LEVEL_VALUE_PK / ORG_LEVEL_ID — the resolved organization level key and its level identifier.
• PRD_LEVEL_VALUE_PK / PRD_LEVEL_ID — the resolved product (item) level key and level identifier.
• GEO_LEVEL_VALUE_PK / GEO_LEVEL_ID — the customer/geography level key; when no customer is present the key is derived via GET_LOC_KEY(..., '-777', NULL, 15) and GEO_LEVEL_ID defaults to 15.
• TIME_LEVEL_ID — hard-coded to 9, reflecting the time bucket granularity of the forecast.
• QUANTITY — the constrained demand quantity (QUANTITY_BY_DUE_DATE from MSC_DEMANDS).
• END_DATE — the truncated using-assembly demand date, defining the forecast bucket end.
• CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN — standard audit columns.

Common Use Cases and Queries

Typical uses include constrained forecast reporting, comparison of constrained versus unconstrained demand, and extraction feeds into external planning or BI systems. The view filters demand to specific ORIGINATION_TYPE values (6, 7, 8, 9, 11, 29, 30, 42, 22), excludes non-planning items, and enforces ATO and MRP planning constraints, so it is well suited to "clean" forecast extracts. A representative query:

• Forecast by plan and item: SELECT cs_name, prd_level_value_pk, end_date, SUM(quantity) FROM apps.msd_constrained_forecast_v WHERE plan_id = :p_plan GROUP BY cs_name, prd_level_value_pk, end_date;
• Forecast by customer/geography: filter and group on geo_level_value_pk for demand-mix analysis.
• Period-over-period comparison: join the view to itself on plan_id and end_date to trend constrained quantities across planning cycles.

Because the view is read-only and joins multiple high-volume planning tables, queries should always be filtered by PLAN_ID to avoid full-table scans.