Search Results pji_fm_extr_plan




Overview
Explore in AI Chat

Overview

PJI_FM_EXTR_PLAN is a table in the PJI (Project Intelligence) schema within Oracle E-Business Suite, appearing in both 12.1.1 and 12.2.2. The PJI product family provides the Project Intelligence analytical foundation that supports project costing, billing, revenue recognition, and forecasting analytics, and it serves as the semantic layer over the EBS project accounting fact tables. PJI_FM_EXTR_PLAN is a forecast and plan extraction staging table: the "FM" designator (Forecast Management) combined with "EXTR" (extract) indicates that this object holds materialized extracts of plan and forecast amounts distributed across accounting periods, ready to be consumed by Project Intelligence reporting, budget-versus-actual comparisons, and forecast-versus-commitment analytics.

The object is classified heuristically as a standalone data vault construct. The classification reflects the fact that the table is an extraction/aggregate store rather than a normalized transactional entity; although it carries a foreign key to VEA_VERSIONS, its design is denormalized by intent — each row is a self-contained plan fact across a period, project, resource, and version tuple. Modelers should treat it as a persistent staging/snapshot object rather than a transactional hub.

Key Information Stored

PJI_FM_EXTR_PLAN has 21 documented columns in the 12.2.2 physical schema. The columns of greatest analytical importance are:

  • VERSION_ID — foreign key to VEA_VERSIONS, identifying the forecast or budget version the row belongs to. This is the primary business-key qualifier for plan rows.
  • PROJECT_ID and PROJECT_ORG_ID — the project and the owning operating unit / organization context.
  • PERIOD_ID and PERIOD_NAME — the accounting period the plan amount covers, enabling period-by-period comparison with actuals.
  • START_DATE and END_DATE — the effective span for the planned row, useful when calendar type does not align with accounting periods.
  • CALENDAR_TYPE_CODE — indicates the calendar basis (for example, accounting versus project calendar) used to derive period boundaries.
  • PLAN_TYPE_CODE — the classification of the plan line (budget, forecast, baseline, and similar).
  • LINE_TYPE — distinguishes the nature of the extracted line, for example revenue versus cost versus labor.
  • REVENUE, RAW_COST, BURDENED_COST — the three principal financial measures. Raw cost represents unburdened cost, burdened cost includes overheads, and revenue captures the planned revenue amount.
  • LABOR_HRS — the planned labor quantity, which is essential for hours-based forecasting and rate analysis.
  • WORKER_ID — the resource or person associated with the planned line, supporting resource-level plan reporting.
  • BATCH_ID — the extraction batch identifier, used to trace a run and partition or purge stale extracts.
  • PF_CURRENCY_CODE and CURRENCY_TYPE — the currency in which the plan amounts are expressed and its role (for example, project functional versus foreign).
  • TIME_DANGLING_FLAG, RATE_DANGLING_FLAG, RATE2_DANGLING_FLAG — data-quality indicators calling out plan rows that could not be fully reconciled to a valid time or rate dimension.

Common Use Cases and Queries

The most common use is period-based forecast reporting alongside actual costs and commitments. A typical query joins the plan extract to the version and project dimensions and aggregates by version and period:

SELECT p.VERSION_ID, p.PERIOD_NAME, p.PROJECT_ID, SUM(p.REVENUE) REV, SUM(p.BURDENED_COST) COST, SUM(p.LABOR_HRS) HRS FROM PJI.PJI_FM_EXTR_PLAN p WHERE p.VERSION_ID = :version AND p.BATCH_ID = :batch GROUP BY p.VERSION_ID, p.PERIOD_NAME, p.PROJECT_ID;

Analysts also use the dangling flags to isolate forecast rows with incomplete rate or time alignment before publishing dashboards, and use BATCH_ID to identify the most recent successful extraction run. Because the table stores both revenue and cost measures at project, period, and worker granularity, it supports margin forecasting, earned-value style variance analysis, and staffing plan versus actual hours comparisons.

Related Objects

  • VEA_VERSIONS — referenced via the documented foreign key PJI_FM_EXTR_PLAN.VERSION_ID to VEA_VERSIONS. Join on VERSION_ID to resolve version names and types.
  • PJI_FM_EXTR_PLAN dimension/owner joins — PROJECT_ID to project-related PJI and PA views to resolve project names, and PROJECT_ORG_ID to organization references.
  • PERIOD_ID / PERIOD_NAME — links to the GL or PA period dimension used within PJI for period labeling.
  • WORKER_ID — joins to resource/worker dimension views in PJI for person-level forecast reporting.
  • PF_CURRENCY_CODE — joins to currency dimension views used by Project Intelligence.