Search Results pji_pjp

Overview

PJI_PJP_SUM_DENORM is a PL/SQL package body owned by the APPS schema in Oracle E-Business Suite. It is part of the Oracle Projects (PA) and Project Intelligence (PJI) technology stack, and its central responsibility is the population, maintenance, and cleanup of denormalized project summary data. In Oracle Projects, denormalization is the mechanism by which the hierarchical relationships between projects, their structures (WBS), and their resource breakdown structures (RBS) are flattened and persisted into physical summary tables. Flattening this data is essential for reporting, aggregation, and Project Intelligence analytics, because querying hierarchical project structures dynamically is prohibitively expensive against high volumes of transactional project data.

The package therefore sits between the normalized project definition tables and the pre-aggregated fact tables used by Project Intelligence. It maintains the XBS and RBS denormalized views of project structure, supporting both full refresh (batch) and incremental (online) processing modes. The header version recorded in the source is PJISP03B.pls, indicating the package belongs to the Project Intelligence subset of the Project Intelligence/PJT family of programs. The API classification is recorded as OTHER, reflecting its role as a foundational data-maintenance utility rather than a public, user-facing API.

Key Procedures and Functions

The package exposes fifteen documented procedures, organized by the denormalization domain they serve:

• POPULATE_XBS_DENORM — Orchestrates population of XBS (project structure) denormalized data. It makes internal calls to PRG_DENORM, WBS_DENORM, and MERGE_XBS_DENORM, and accepts a worker identifier, a denormalization type of 'WBS', 'PRG', or 'ALL', a structure version identifier, and two process group parameters reserved for future online processing support.
• COPY_XBS_DENORM — Copies XBS denormalized data, typically used to propagate or stage denormalized structure between versions or temporary and permanent tables.
• POPULATE_RBS_DENORM and POPULATE_RBS_DENORM_UPGRADE — Populate RBS (resource breakdown structure) denormalized data. The upgrade variant supports conversion or re-derivation of denormalized structures during an EBS upgrade path.
• PRG_DENORM and WBS_DENORM — Perform the actual denormalization of program (PRG) and work breakdown structure (WBS) levels respectively, writing into the XBS denormalization tables.
• PRG_DENORM_ONLINE and WBS_DENORM_ONLINE — The incremental counterparts of the batch denormalization routines, invoked when individual project structure changes occur at runtime rather than through a scheduled full refresh.
• RBS_DENORM, RBS_DENORM_RBS, and RBS_DENORM_ONLINE — Denormalize resource breakdown structures in full, per-RBS, and online modes.
• MERGE_XBS_DENORM and MERGE_RBS_DENORM — Merge staged denormalized rows from temporary tables into the permanent denormalized tables, typically after the corresponding POPULATE step has loaded the temporary area.
• CLEANUP_XBS_DENORM and CLEANUP_RBS_DENORM — Remove or reconcile obsolete denormalized rows, maintaining consistency of the permanent XBS and RBS structures after deletions or structural changes.

Tables Accessed

The package reads source project definition data from a set of PA tables and writes denormalized results into the summary tables. It reads PA_PROJECTS_ALL for the project master list, PA_OBJECT_RELATIONSHIPS to resolve relationships between projects and structures, PA_PROJ_ELEMENTS and PA_PROJ_ELEMENT_VERSIONS to obtain the WBS element hierarchy and its versioned definitions, and PA_RBS_ELEMENTS and PA_RBS_PRJ_ASSIGNMENTS to determine RBS element definitions and their assignment to projects. It writes to PA_XBS_DENORM and PA_XBS_DENORM_TEMP for the WBS/program denormalization and merge cycle, and to PA_RBS_DENORM for the RBS denormalization results. It also references the Project Intelligence aggregation tables PJI_FP_AGGR_XBS, PJI_FP_AGGR_XBS_T, PJI_FP_AGGR_RBS, and PJI_FP_AGGR_RBS_T, which hold the pre-aggregated fact data consumed by Project Intelligence reporting. PJI_PA_PROJ_EVENTS_LOG records project events that drive incremental processing, while PJI_PJP_PROJ_BATCH_MAP maps projects to processing batches.

Usage Notes

PJI_PJP_SUM_DENORM is not a user-facing API and is not typically called directly from forms; instead, it is invoked by concurrent programs and internal Project Intelligence processes. The batch procedures are executed during scheduled denormalization and full-refresh concurrent requests, while the online procedures are triggered by project structure change events recorded in PJI_PA_PROJ_EVENTS_LOG. Because the package maintains denormalized summary data consumed by PJI aggregation tables, it is executed after structural changes and before reporting or aggregation cycles. The package is referenced by six other packages, indicating it is a shared dependency within the Project Intelligence and Projects summary architecture. Debug output is gated by the g_pa_debug_mode flag and written through Pji_Utils.WRITE2LOG, which is useful when diagnosing denormalization failures. No direct customer invocation or extension is documented; custom code should not depend on these internal procedures because their signatures and behavior are subject to change across EBS releases.