Search Results cleanup_xbs_denorm

Overview

PJI_PJP_SUM_DENORM is an Oracle Projects (PA) PL/SQL package in the APPS schema that maintains denormalized summary data used by the Project Intelligence (PJI) reporting and analytics infrastructure. Its central responsibility is the population, maintenance, and cleanup of denormalized structures for two hierarchical reporting dimensions: the Work Breakdown Structure (WBS), stored in the PA_XBS_DENORM family of tables, and the Resource Breakdown Structure (RBS), stored in the PA_RBS_DENORM family. Denormalization flattens the parent-child relationships and versioned element lineages of these structures so that project summarization, cross-project aggregation, and PJI reporting queries can be executed without repeatedly traversing the normalized PA_PROJ_ELEMENTS, PA_PROJ_ELEMENT_VERSIONS, and PA_RBS_ELEMENTS tables.

The package supports both batch and online extraction paths. The presence of "ONLINE" variants and worker-id parameters indicates that it is designed to run in Oracle Projects' multi-worker concurrent processing model, where summary generation is partitioned across parallel workers coordinated by the PJI project batch mapping tables. The naming of procedures such as POPULATE_RBS_DENORM_UPGRADE also indicates a role in version upgrades and data migration of the denormalized structures between releases.

Key Procedures and Functions

• POPULATE_XBS_DENORM — Populates the WBS (XBS) denormalized table for a specified WBS version, accepting a worker identifier, denormalization type, and optional project grouping parameters.
• COPY_XBS_DENORM — Copies denormalized WBS records from one WBS version to another, supporting a copy mode that controls how source records are selected.
• POPULATE_RBS_DENORM — Populates the RBS denormalized table for a given RBS version and worker.
• POPULATE_RBS_DENORM_UPGRADE — Upgrade-specific variant that populates RBS denormalized data and returns standard return status, message count, and message data outputs.
• PRG_DENORM and PRG_DENORM_ONLINE — Batch and online denormalization drivers for project/resource group aggregation; the online variant additionally scopes processing to a project group.
• WBS_DENORM and WBS_DENORM_ONLINE — Batch and online denormalization drivers for WBS data at a specified WBS version.
• RBS_DENORM, RBS_DENORM_RBS, and RBS_DENORM_ONLINE — Batch, resource-specific, and online denormalization drivers for RBS data.
• MERGE_XBS_DENORM and MERGE_RBS_DENORM — Merge staging/temporary denormalized rows into the permanent denormalized tables.
• CLEANUP_XBS_DENORM and CLEANUP_RBS_DENORM — Remove obsolete or stale denormalized rows following population, merge, or re-versioning activity.

Tables Accessed

The package reads foundational Projects dictionary and transaction tables, including PA_PROJECTS_ALL, PA_PROJ_ELEMENTS, PA_PROJ_ELEMENT_VERSIONS, PA_OBJECT_RELATIONSHIPS, PA_RBS_ELEMENTS, and PA_RBS_PRJ_ASSIGNMENTS, to derive the hierarchy and assignment context for denormalization. It writes and maintains the denormalized targets PA_XBS_DENORM and PA_RBS_DENORM, using PA_XBS_DENORM_TEMP as a staging area during merge operations. Aggregation and reporting summary data is sourced from PJI_FP_AGGR_XBS, PJI_FP_AGGR_XBS_T, PJI_FP_AGGR_RBS, and PJI_FP_AGGR_RBS_T. PJI_PA_PROJ_EVENTS_LOG and PJI_PJP_PROJ_BATCH_MAP provide event tracking and parallel worker batch coordination respectively.

Usage Notes

PJI_PJP_SUM_DENORM is not an end-user API. It is invoked internally by Oracle Projects and Project Intelligence concurrent programs responsible for summary generation, WBS/RBS structure maintenance, and product upgrade, and it is referenced by six other packages in the APPS schema. The worker-id and version-id parameters confirm that callers are typically parallel concurrent workers operating under a batch map. The wbs_denorm procedure is the entry point most commonly associated with the user's search term. Custom code should not call this package directly; denormalized data should be regenerated through the supported PJI concurrent programs so that batch mapping, event logging, and worker coordination remain consistent.