Search Results plan_fk

Overview

VIEW: APPS.EDW_PLAN_NAME_FKV is a foreign key (FKV) view within the Oracle E-Business Suite applications schema. It forms part of the Enterprise Data Warehouse (EDW) integration layer that exposes flattened, ETL-ready representations of Oracle Manufacturing and Materials Planning data. The view produces a synthetic composite key combining the plan designator, the organization identifier, and an instance code, and it serves as a plan-name dimension source for downstream analytics and data-warehouse loads.

The view is directly relevant to the search term "mrp_plan_type" because its defining query joins to MFG_LOOKUPS on lookup_type = 'MRP_PLAN_TYPE'. This lookup restricts the rows returned to plans whose plan type is a valid, enabled MRP plan type defined in the application's lookup registry. In practice, the view acts as a validated, human-readable enumeration of finished MRP plans.

Underlying Base Objects

Although ETRM metadata documents no referenced base objects, the view definition itself is explicit about its sources. Four tables participate in the join:

• MRP_PLANS (MPL) — the primary source, supplying compile_designator, organization_id, plan_type, and plan_completion_date.
• MTL_PARAMETERS (MP) — validated by mp.organization_id = mpl.organization_id, confirming the plan organization is a valid inventory organization.
• MFG_LOOKUPS (ML1) — joined on ml1.lookup_type = 'MRP_PLAN_TYPE' and ml1.lookup_code = mpl.plan_type, ensuring only recognized plan types are returned.
• EDW_LOCAL_INSTANCE (INST) — supplies instance_code, identifying the E-Business Suite instance that owns the plan record.

The filter mpl.plan_completion_date IS NOT NULL excludes plans that have not completed, which typically removes in-progress or unrun plan definitions from the result set. The combined effect is a curated list of completed plans with valid types, valid organizations, and instance provenance.

Key Columns

• Composite key expression — MPL.COMPILE_DESIGNATOR || '-' || MPL.ORGANIZATION_ID || '-' || INST.INSTANCE_CODE. This concatenated value is the effective foreign key exposed by the view and uniquely identifies a plan across multiple instances and organizations.
• COMPILE_DESIGNATOR — the plan name or designator as defined in MRP_PLANS; the business-facing identifier of the plan.
• ORGANIZATION_ID — the inventory organization to which the plan belongs, matching MTL_PARAMETERS.
• INSTANCE_CODE — the instance identifier from EDW_LOCAL_INSTANCE, used to disambiguate plans originating from different EBS environments consolidated into the warehouse.

Note that plan_type and plan_completion_date are used as join and filter predicates but are not projected in the SELECT list; consumers needing the decoded plan type must join MRP_PLANS or MFG_LOOKUPS separately.

Common Use Cases and Queries

Typical usage includes populating plan-name dimension tables in the EDW, resolving plan foreign keys during fact loads, and validating plan reference integrity across instances.

SELECT compile_designator, organization_id
FROM   apps.edw_plan_name_fkv
WHERE  organization_id = :org_id;

To enumerate distinct plan types present on completed plans, join back to the lookup table:

SELECT f.compile_designator, ml.meaning
FROM   apps.edw_plan_name_fkv f,
       mrp_plans mpl,
       mfg_lookups ml
WHERE  mpl.compile_designator = f.compile_designator
AND    mpl.organization_id = f.organization_id
AND    ml.lookup_type = 'MRP_PLAN_TYPE'
AND    ml.lookup_code = mpl.plan_type;

Because the view already enforces valid plan types and completed plans, queries against it are efficient and safe for dimension staging without additional lookup validation logic.