Search Results pji_time_mv




Overview
Explore in AI Chat

Overview

PJI_TIME_MV is an APPS-owned snapshot table within the PJI – Project Intelligence product family in Oracle E-Business Suite 12.1.1 and 12.2.2. The object name carries the _MV suffix characteristic of materialized views, and the ETRM documentation explicitly describes it as a "snapshot table for snapshot APPS.PJI_TIME_MV." In EBS materialized view architecture, the base object is typically the materialized view itself, while the corresponding _MV table is the physical container that stores the precomputed result set. PJI_TIME_MV therefore serves as the denormalized time-dimension store that Project Intelligence reporting and analytics rely upon, presenting calendar, period, quarter, and year attributes in a single row per period for efficient slicing of project data.

The documented physical schema for release 12.1.1 records the table as residing in the APPS schema with 17 columns and status VALID. The ETRM metadata assigns a heuristic Data Vault classification of standalone, mined from the foreign-key structure. As a modeling suggestion, this indicates the object behaves as a self-contained reference or dimension structure rather than a classic hub, link, or satellite, though its grain is clearly periodic and reference-oriented in nature.

Key Information Stored

The table stores one row per accounting period, enriched with quarter-level and year-level attributes so that a single scan can satisfy multiple time-grain reporting requirements. The most significant columns are:

  • PERIOD_NAME – the human-readable period label (for example, a monthly or weekly period name), the principal business-key candidate for period-level joins.
  • PERIOD_ID – the surrogate identifier for the period, functioning as the primary join key to fact and transaction tables.
  • PERIOD_TYPE – classifies the period grain, distinguishing monthly, weekly, or other calendar types.
  • PERIOD_START_DATE and PERIOD_END_DATE – the date boundaries of the period.
  • START_DATE_TIME and END_DATE_TIME – timestamp-precision boundaries, supporting sub-day analysis and time-zone-sensitive reporting.
  • CALENDAR_ID – identifies the accounting calendar to which the period belongs, essential on multi-calendar or multi-organization deployments.
  • QUARTER_NAME, QUARTER_ID, QUARTER_START_DATE, QUARTER_END_DATE – the quarter-level rollup attributes.
  • YEAR, YEAR_ID, YEAR_START_DATE, YEAR_END_DATE – the fiscal or calendar year attributes.
  • SEQUENCE – an ordinal value that establishes chronological ordering across periods.

The documented foreign key defines YEAR_ID as referencing JAI_FA_AST_YEARS. No unique index is documented in the supplied metadata, so PERIOD_ID should be treated as the practical surrogate key, with PERIOD_NAME plus CALENDAR_ID serving as the natural business-key combination in usage.

Common Use Cases and Queries

The table's principal role is to anchor time-based reporting without repeatedly resolving date logic against Oracle General Ledger period definitions. A typical pattern joins project facts to PJI_TIME_MV on PERIOD_ID to obtain period, quarter, and year labels simultaneously:

  • Period-to-quarter-to-year rollups for project cost, revenue, and budget variance reporting.
  • Trend analysis using SEQUENCE to order periods consecutively and PERIOD_START_DATE / PERIOD_END_DATE to filter reporting windows.
  • Calendar-aware filtering via CALENDAR_ID when multiple accounting calendars coexist.
  • Time-intelligence dashboards that require period and prior-period comparisons within a single query.

A representative query pattern filters by calendar and year, then aggregates at the quarter grain:

SELECT t.QUARTER_NAME, t.YEAR, SUM(f.amount)
FROM project_fact f, PJI_TIME_MV t
WHERE f.period_id = t.PERIOD_ID
AND t.CALENDAR_ID = :calendar_id
AND t.YEAR = :fiscal_year
GROUP BY t.QUARTER_NAME, t.YEAR;

Because the object is a snapshot, refresh timing must be considered: newly opened or adjusted periods may not appear until the materialized view refresh completes.

Related Objects

The documented relationship data identifies the following significant dependencies and associations:

  • JAI_FA_AST_YEARS – referenced by PJI_TIME_MV.YEAR_ID, providing year-level context and validating the fiscal year attribute.
  • PJI_TIME_MV (materialized view) – the base snapshot object whose result set is materialized into this table.
  • GL_PERIODS and GL_CALENDARS – the General Ledger sources from which period and calendar attributes are typically derived.
  • FND_CALENDAR_* / FND_DATE utilities – commonly used alongside the table for date conversion and calendar validation.
  • Project Intelligence fact views and Project Costing reporting objects that join on PERIOD_ID and CALENDAR_ID.

The heuristic standalone classification indicates no propagated child tables depend on this object through further foreign keys, so it functions primarily as a referenced dimension rather than a parent in a cascading hierarchy.