Results for “pji_org_denorm”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

PJI_ORG_DENORM is a denormalized summary table owned by the PJI (Project Intelligence) schema in Oracle EBS 12.1.1 and 12.2.2. Per the ETRM metadata, it "stores the last summarized PA_RBS_DENORM structure for the financial plan." In other words, this table materializes a flattened, pre-aggregated view of the Resource Breakdown Structure (RBS) hierarchy used by Project Intelligence reporting and analytics. Rather than re-reading the far larger PA_RBS_DENORM structure at runtime, PJI writes a summarized snapshot into this compact four-column table so that downstream dashboards, financial plan inquiries, and organizational roll-ups can be served with minimal I/O.

The table carries only four documented columns, making it a small but strategically positioned cache of hierarchy levels keyed to organizations. Its role is organizational and hierarchical rather than transactional: it exists to accelerate "by organization" and "by sub-organization" slicing of the summarized RBS structure.

Applying a heuristic Data Vault classification based on the mined foreign-key structure, PJI_ORG_DENORM is best modeled as a link table. It resolves the relationship between the RBS summary structure and the HR organization hierarchy (organization and sub-organization), functioning as a connective artifact between two business entities rather than a standalone descriptive satellite.

Key Information Stored

The documented physical schema is intentionally narrow — four columns in total, all of which are relevant to the hierarchy mapping:

• ORGANIZATION_ID — The organization identifier. This is a foreign key to HR_ALL_ORGANIZATION_UNITS and forms half of the composite business key that ties the denormalized row to the enterprise organization model.
• SUB_ORGANIZATION_ID — The subordinate organization identifier, also a foreign key to HR_ALL_ORGANIZATION_UNITS. This captures the child level in the organizational breakdown.
• ORGANIZATION_LEVEL — The hierarchy level associated with the organization, enabling level-based filtering and roll-up logic in Project Intelligence reports.
• SUB_ORGANIZATION_LEVEL — The hierarchy level associated with the sub-organization, completing the two-dimensional level context.

The ETRM metadata does not document a distinct surrogate primary key column or a named unique index. Based on the FK structure, the composite of ORGANIZATION_ID and SUB_ORGANIZATION_ID should be treated as the effective business-key candidate, with the level columns acting as descriptive attributes. Because no surrogate key is documented, the table should be understood as a keyed link rather than a keyed hub.

Common Use Cases and Queries

The principal use case is serving summarized financial-plan RBS data filtered by organizational hierarchy. A typical query joins the denormalized table back to HR organizations to resolve names:

• Organizational roll-up reporting — Aggregate financial plan amounts by ORGANIZATION_LEVEL to produce level-based summaries without traversing the full RBS structure.
• Sub-organization drill-down — Join SUB_ORGANIZATION_ID to HR_ALL_ORGANIZATION_UNITS to resolve sub-organization names for drill-down reports.
• Level filtering — Use ORGANIZATION_LEVEL and SUB_ORGANIZATION_LEVEL predicates to constrain reports to specific tiers of the hierarchy.
• Refresh/validation checks — Compare row counts between PJI_ORG_DENORM and the source PA_RBS_DENORM structure to verify that the summarized snapshot is current.

A representative pattern follows:

SELECT d.ORGANIZATION_ID, d.SUB_ORGANIZATION_ID, d.ORGANIZATION_LEVEL, d.SUB_ORGANIZATION_LEVEL, o.NAME
FROM PJI.PJI_ORG_DENORM d, HR_ALL_ORGANIZATION_UNITS o
WHERE d.ORGANIZATION_ID = o.ORGANIZATION_ID
AND d.ORGANIZATION_LEVEL = :level;

Related Objects

The following objects are the most significant in relation to PJI_ORG_DENORM:

• HR_ALL_ORGANIZATION_UNITS — Referenced twice, via ORGANIZATION_ID and SUB_ORGANIZATION_ID foreign keys. This is the primary dependency and the source of organization names and hierarchy attributes.
• PA_RBS_DENORM — The source denormalized RBS structure that this table summarizes for the financial plan; the relationship between the two defines the table's purpose.
• PJI financial plan summary tables — Downstream PJI reporting objects that consume the summarized RBS structure, joining on organization keys.
• PA_RBS_* structure tables — The underlying RBS hierarchy tables whose flattened output feeds the denormalization process.
• PJI dashboards and OBIEE repository objects — Reporting layers in Project Intelligence that query this table through the organization join path.

Because the table is a link artifact with two FKs to the same parent, any join must alias HR_ALL_ORGANIZATION_UNITS twice to distinguish the organization from the sub-organization context.