Search Results edw_plan_name_fkv




Overview
Explore in AI Chat

Overview

EDW_PLAN_NAME_FKV is a foreign key view (FKV) delivered within the Oracle Supply Chain Intelligence (ISC) product family, an Enterprise Data Warehouse (EDW) layer historically bundled with Oracle E-Business Suite. In Oracle EBS 12.1.1 and 12.2.2, the object is classified as Obsolete, and ETRM documentation explicitly records that it is "Not implemented in this database." Consequently, EDW_PLAN_NAME_FKV should be regarded as a legacy data warehouse construct rather than an operational EBS view, even though its definition references live EBS tables in the MRP and MTL schemas.

The view's documented purpose is to allow fact views within the Supply Chain Intelligence star schema to derive the surrogate foreign key for the Plan Name dimension. By exposing a deterministic concatenated key, it standardizes how ETL routines and analytical queries join transactional facts to the plan dimension, ensuring plan names are resolved consistently across instances and organizations.

Underlying Base Objects

ETRM metadata lists no referenced base objects for EDW_PLAN_NAME_FKV, but the documented view text reveals its dependencies directly. The defining query joins four tables:

The presence of EDW_LOCAL_INSTANCE confirms the view is a warehouse-specific artifact that relies on the instance registry constructed by the ISC ETL process, which explains why it is not implemented in a plain EBS database.

Key Columns

  • PLAN_FK — the composite foreign key, built as COMPILE_DESIGNATOR || '-' || ORGANIZATION_ID || '-' || INSTANCE_CODE. This concatenated value links fact rows to the Plan Name dimension.
  • COMPILE_DESIGNATOR — the plan identifier (for example, a plan name or code) from MRP_PLANS.
  • ORGANIZATION_ID — the inventory organization to which the plan belongs.

Common Use Cases and Queries

In classic ISC deployments, this view was referenced by fact view definitions during ETL to populate plan dimension keys. A representative query follows:

SELECT PLAN_FK,
       COMPILE_DESIGNATOR,
       ORGANIZATION_ID
FROM   EDW_PLAN_NAME_FKV
WHERE  ORGANIZATION_ID = :org_id
ORDER  BY COMPILE_DESIGNATOR;

Analysts joining aggregated supply chain facts back to plan metadata could use PLAN_FK as the join predicate against the Plan Name dimension, while the PARENT dimension attribute would expose the human-readable plan name.

Because ETRM shows no base objects and marks the view obsolete, any query in 12.1.1 or 12.2.2 should first verify existence in ALL_VIEWS. Where the ISC schema is absent, the equivalent logic can be reconstructed from MRP_PLANS, MTL_PARAMETERS, and MFG_LOOKUPS directly.