Search Results pji_rep_xbs_denorm




Overview
Explore in AI Chat

Overview

PJI_REP_XBS_DENORM is a denormalized reporting table residing in the PJI schema, owned by the Project Intelligence product within Oracle E-Business Suite. Project Intelligence (PJI) was an analytical and reporting layer that sat above Oracle Projects, providing pre-aggregated and pre-joined structures to accelerate hierarchical reporting against project and program structures. As the object's status indicates, PJI is marked Obsolete, meaning the module is no longer actively shipped or supported in current releases of EBS 12.1.1 and 12.2.2; the table typically persists only in environments that were upgraded from earlier releases where Project Intelligence had been implemented.

Functionally, the table stores denormalized information describing the relationship between project and program hierarchy elements. Rather than requiring a recursive traversal of the Project Management hierarchy, the denormalization flattens ancestor-descendant relationships into discrete rows, making rollup and drill-down reporting substantially cheaper at query time. The heuristic Data Vault classification derived from the foreign key structure is satellite-leaning: the table carries descriptive attributes and relationship metadata keyed by PROJECT_ID referencing PA_PROJECTS_ALL, which is characteristic of a satellite attached to a project-level hub rather than an independent hub or an association link.

Key Information Stored

The documented physical schema contains 19 columns. The most significant are described below.

  • PROJECT_ID — Foreign key to PA_PROJECTS_ALL. This is the principal business-key column and the anchor for nearly all joins; it identifies the project to which the denormalized hierarchy row belongs.
  • PARENT_ELEMENT_ID and CHILD_ELEMENT_ID — The two endpoints of the hierarchical relationship being denormalized. Together they encode an ancestor-descendant pairing within the project/program structure.
  • SUP_PROJECT_ID — The superior (parent) project reference, useful for building program-level rollups.
  • PRG_FLAG and PRG_ROLLUP_FLAG — Flags indicating whether the row participates in program-level hierarchy and program rollup processing respectively.
  • ROLLUP_FLAG and DISPLAY_CHILD_FLAG — Control whether the child element is included in monetary rollups and whether it should be displayed in the reporting hierarchy.
  • NAME — The descriptive label of the hierarchy element, denormalized to avoid a join to the source name table.
  • COMPLETED_PERCENTAGE — Physical percent complete associated with the element, carried into the denormalized row for progress reporting.
  • WBS_VERSION_ID and OWNER_WBS_VERSION_ID — References to the WBS structure version, enabling reporting against a specific structural baseline.
  • RELATIONSHIP_TYPE — Classifies the nature of the parent-child association.
  • DISPLAY_SEQUENCE — Ordering column for presenting hierarchy nodes in a report.
  • CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN — Standard EBS WHO columns for audit and concurrency tracking.

The metadata does not document a distinct surrogate primary key column; PROJECT_ID functions as the FK to PA_PROJECTS_ALL and is the natural business-key candidate for joining. No unique index is enumerated in the provided metadata.

Common Use Cases and Queries

The primary use case is hierarchical project and program reporting where flattened relationships avoid recursive queries against PJI and PA structures. Typical patterns include program rollup reports, project hierarchy drill-downs, and percent-complete aggregation across a WBS tree.

A representative query joining back to the project master is:

  • SELECT d.PROJECT_ID, d.PARENT_ELEMENT_ID, d.CHILD_ELEMENT_ID, d.NAME, d.COMPLETED_PERCENTAGE FROM PJI.PJI_REP_XBS_DENORM d WHERE d.ROLLUP_FLAG = 'Y' AND d.PRG_ROLLUP_FLAG = 'Y' ORDER BY d.DISPLAY_SEQUENCE;
  • SELECT p.segment1, d.NAME, d.COMPLETED_PERCENTAGE FROM PJI.PJI_REP_XBS_DENORM d JOIN PA.PA_PROJECTS_ALL p ON p.PROJECT_ID = d.PROJECT_ID WHERE d.PROJECT_ID = :project_id;

Because the table is denormalized and marked obsolete, reporting solutions should treat it as a read-only source and validate its population freshness before relying on it for operational decisions.

Related Objects

The following objects are most significant in relation to this table, based on the documented foreign key and the surrounding Oracle Projects schema.

  • PA_PROJECTS_ALL — Referenced via PJI_REP_XBS_DENORM.PROJECT_ID; the authoritative project master and the mandatory join for project attributes.
  • PA_PROJECTS — Date-effective variant of the project master, used for historical reporting.
  • PA_TASKS and PA_TASK_STRUCTURES — Supply WBS structural context consistent with WBS_VERSION_ID.
  • PJI_REP_XBS_DENORM — Self-referencing hierarchy traversal via PARENT_ELEMENT_ID and CHILD_ELEMENT_ID.
  • PA_PROJECT_CLASSES and PA_PROJECT_STATUSES — Classification and status dimensions frequently joined for program reporting.
  • PA_PROJECTS_ALL related PJI reporting tables — Sibling denormalized structures that share the PROJECT_ID key and are typically refreshed together.