Search Results pji_org_denorm




Overview
Explore in AI Chat

Overview

The PJI_ORG_DENORM table is a core data object within the Project Intelligence (PJI) module of Oracle E-Business Suite (EBS) versions 12.1.1 and 12.2.2. It functions as a denormalized summary table, specifically designed to store the last consolidated organizational structure derived from the PA_RBS_DENORM table for financial planning purposes. Its primary role is to provide a pre-aggregated, performance-optimized view of the organizational hierarchy, which is critical for the efficient generation of financial reports and analytics within Project Intelligence. By maintaining this summarized snapshot, the table supports the complex data roll-ups and calculations required for project costing, budgeting, and forecasting.

Key Information Stored

While the provided metadata does not list specific columns, the table's description and foreign key relationships define its core data model. The table stores denormalized organizational information, with key fields logically including identifiers for the summarized organizational units. Crucially, the documented foreign keys reveal two essential columns: ORGANIZATION_ID and SUB_ORGANIZATION_ID. Both columns reference HR_ALL_ORGANIZATION_UNITS, indicating that the table captures hierarchical relationships between parent and child organizations. This structure likely includes additional attributes such as organization names, codes, and hierarchy levels to support reporting without requiring joins to the HR source tables for common lookups.

Common Use Cases and Queries

The primary use case for PJI_ORG_DENORM is to serve as a master reference for organizational roll-ups in PJI financial reporting. It enables efficient queries for listing organizations under a specific hierarchy or validating organizational assignments. Common SQL patterns involve joining this table to fact tables in the PJI schema to filter or aggregate data by organization. A typical reporting query might retrieve the full organizational tree for a financial plan.

  • Sample Query: Selecting a summarized organizational hierarchy: SELECT organization_id, sub_organization_id FROM pji_org_denorm WHERE organization_id = :p_org_id ORDER BY sub_organization_id;
  • Reporting Use: This table is integral to reports analyzing project financial performance across business units, departments, or cost centers, as it provides the definitive mapping for data summarization.

Related Objects

PJI_ORG_DENORM is centrally connected to several key EBS objects. Its data is sourced from and is a summarized version of PA_RBS_DENORM, a table from the Project Accounting (PA) module that holds the Resource Breakdown Structure in a denormalized form. As documented, it has foreign key dependencies on the HR_ALL_ORGANIZATION_UNITS table, which is the master source for all organization definitions in EBS. Within the PJI module, it is expected to be referenced by various fact and summary tables for financial plans, as well as by OLAP cubes and pre-built BI Publisher reports that deliver Project Intelligence analytics.