Search Results pji_xbs_denorm




Overview
Explore in AI Chat

Overview

PJI.PJI_XBS_DENORM is a denormalized staging and reporting table within the Project Intelligence (PJI) product family of Oracle E-Business Suite. Its documented purpose is to store normalized work breakdown structure (WBS) and program hierarchy information, flattening hierarchical parent-child relationships between project elements into a form that is efficient for analytical querying, drill-down reporting, and Project Intelligence dashboards. The table is classified as VALID in the data dictionary, though the PJI product itself is flagged as Obsolete in Oracle ETRM documentation for releases 12.1.1 and 12.2.2. Organizations still running PJI-based reporting will continue to encounter this object in their schemas.

The physical schema documented for 12.2.2 contains 20 columns under the PJI schema. Based on the foreign key structure mined from the ETRM metadata, the table exhibits the characteristics of a link in Data Vault modeling terms — that is, a heuristic suggestion rather than a formally declared classification. The three foreign keys all resolve to PA_PROJ_ELEMENT_VERSIONS, indicating that each row represents a relationship between structural elements rather than an independent business entity.

Key Information Stored

The table's rows describe parent/child relationships between project structure elements at a given version. The most significant columns are:

  • STRUCT_VERSION_ID — Foreign key to PA_PROJ_ELEMENT_VERSIONS; identifies the structural version of the project element being described.
  • SUP_ID / SUB_ID — The superior (parent) and subordinate (child) element identifiers, each a foreign key to PA_PROJ_ELEMENT_VERSIONS. Together they define the hierarchy edge.
  • SUP_PROJECT_ID — Project identifier associated with the superior element, enabling grouping by top-level project.
  • SUP_LEVEL / SUB_LEVEL — Numeric depth indicators for parent and child, used to render indentation and compute rollups.
  • SUB_ROLLUP_ID — Identifier used to aggregate or roll up subordinate quantities and costs.
  • SUB_LEAF_FLAG — Boolean indicator marking whether the subordinate is a leaf node, useful for pruning queries.
  • STRUCT_TYPE and PRG_GROUP — Structural type and program group discriminators.
  • RELATIONSHIP_TYPE — Classifies the nature of the parent-child linkage.
  • STRUCT_EMT_ID / SUP_EMT_ID / SUB_EMT_ID / SUBRO_ID — Element-master and subordinate identifiers used for cross-referencing with element master tables.
  • Audit columns — LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, and LAST_UPDATE_LOGIN track row provenance.

No explicit surrogate primary key or unique business key is documented in the ETRM metadata; in practice, the combination of STRUCT_VERSION_ID, SUP_ID, and SUB_ID functions as the natural business key.

Common Use Cases and Queries

The table is typically queried to reconstruct WBS hierarchies for reporting, to flatten parent-child relationships for cost rollups, and to drive Project Intelligence dashboards. A representative query joining to element versions follows:

SELECT d.SUP_ID, d.SUB_ID, d.SUB_LEVEL, d.SUB_LEAF_FLAG
FROM   PJI_XBS_DENORM d, PA_PROJ_ELEMENT_VERSIONS v
WHERE  d.STRUCT_VERSION_ID = v.VERSION_ID
AND    d.SUP_PROJECT_ID = :project_id
AND    d.SUB_LEAF_FLAG = 'Y';

Common scenarios include extracting leaf-level WBS elements for cost collection, validating parent/child integrity against source element records, and building BI Publisher reports that present program hierarchies. Analysts also use SUB_LEVEL and SUP_LEVEL to compute indentation and rollup calculations without recursive SQL.

Related Objects

The principal related objects, derived from documented foreign keys, are:

  • PA_PROJ_ELEMENT_VERSIONS — Referenced through STRUCT_VERSION_ID, SUP_ID, and SUB_ID; the primary source for element version, name, and date-effective information.
  • PA_PROJECTS_ALL — Joined on SUP_PROJECT_ID to resolve project number and name.
  • PA_PROJECT_ELEMENTS / PA_PROJ_ELEMENTS — Source of WBS element definitions reflected in the denormalized rows.
  • PA_TASKS — Correlates task-level detail with the underlying WBS structure.
  • PJI_* reporting views and Materialized Views — Downstream Project Intelligence objects that consume this table for analytic output.

Because PJI is documented as obsolete, DBAs should treat PJI_XBS_DENORM as legacy and confirm whether any installed custom reports or interfaces still depend on it before purging or archiving.