Search Results pji_time_day_mv




Overview
Explore in AI Chat

Overview

PJI_TIME_DAY_MV is a materialized view object in the Oracle E-Business Suite Applications (APPS) schema, historically associated with the Project Intelligence (PJI) product family. Within the ETRM 12.1.1 documentation set, the object carries a Product designation of "PJI - Project Intelligence (Obsolete)," and its Implementation/DBA Data notation records that it is "Not implemented in this database." In practice, therefore, PJI_TIME_DAY_MV is best understood as a legacy calendar/time-dimension construct that was used by the obsolete Project Intelligence reporting layer to pre-aggregate date and period attributes for analytical queries against project data. Because PJI was superseded by later Oracle project analytics and Business Intelligence capabilities, the object persists largely as a metadata artifact rather than an actively maintained runtime structure.

From a dimensional modeling perspective, the mined relationship data suggests a standalone Data Vault classification. This should be read as a heuristic modeling suggestion rather than a documented design decision: the view holds no foreign keys pointing to it from other objects, and its only documented outbound reference is a single FK from YEAR_ID to JAI_FA_AST_YEARS. As a standalone classification, it functions conceptually like a reference or lookup structure supplying calendar context, rather than as a hub, link, or satellite participating in an integrated Data Vault network.

Key Information Stored

The documented physical schema for ETRM 12.1.1 lists the APPS owner and 17 columns. The most significant columns, grouped by role, are as follows:

No explicit primary key or unique index constraint is documented in the ETRM excerpt. Where a key is implied, REPORT_DATE combined with the period or calendar identifiers (CALENDAR_ID, PERIOD_ID) serves as the natural business-key candidate, since a single calendar day maps to exactly one period and one quarter within a given calendar. YEAR_ID is the only documented referential relationship, pointing to JAI_FA_AST_YEARS.

Common Use Cases and Queries

The principal use case for a time-day structure of this kind is joining discrete transactional dates to their enclosing period, quarter, and year labels, thereby enabling consistent fiscal reporting. A typical pattern pivots the period boundaries from the row set:

  • Querying period-level activity: select PERIOD_NAME, sum(amount) from transactions t, PJI_TIME_DAY_MV d where t.transaction_date = d.REPORT_DATE group by PERIOD_NAME.
  • Calendar-versus-fiscal alignment: join on CALENDAR_ID and PERIOD_ID to compare a source calendar to its translated name (PERIOD_ORIGINAL_NAME).
  • Boundary analysis: use PERIOD_START_DATE, PERIOD_END_DATE, QTR_END_DATE, and YEAR_END_DATE to compute period-to-date and year-to-date cutoffs.
  • Continuous-range filtering: leverage REPORT_DATE_JULIAN for gap-free date ranges without calendar-table lookups.

Because the object is documented as not implemented, any production query should first verify the view's existence in the target database; references to it in unmodified 12.1.1 or 12.2.2 environments typically indicate leftover metadata rather than a functioning reporting dependency.

Related Objects

Relationship data for PJI_TIME_DAY_MV is deliberately narrow. The only formally documented tie is:

  • JAI_FA_AST_YEARS — referenced via the FK PJI_TIME_DAY_MV.YEAR_ID, providing the year definition that anchors the annual attributes.

By extension, the internal identifier columns CALENDAR_ID, PERIOD_ID, and QTR_ID imply conceptual associations with the standard Oracle General Ledger calendar and period definitions (for example, GL_PERIODS and the GL calendar tables), which supply the authoritative period and quarter metadata. In a functioning Project Intelligence configuration, a date-dimension of this type would also support the PJI project performance fact structures and the associated financial fact views used in Project Intelligence dashboards. Given the obsolete status and the "not implemented" annotation, integrators should treat these relationships as design lineage only and confirm actual usage with a data dictionary query against ALL_MVIEWS, ALL_TAB_COLUMNS, and ALL_CONSTRAINTS before relying on the object in any interface or report.