Search Results pji_pji_extraction_utils




Overview
Explore in AI Chat

Overview

APPS.PJI_PJI_EXTRACTION_UTILS is a valid PL/SQL package body in the Oracle E-Business Suite APPS schema belonging to the Project Intelligence (PJI) product family. It provides the utility layer that supports the extraction of project performance data from the transactional EBS tables into the PJI summary and denormalized reporting structures. In release 12.1.1 and 12.2.2 this component is part of the Oracle Project Analytics / Project Intelligence extraction infrastructure, which stages project, resource, organization, and work type information for downstream analytical reporting against the PJI materialized views.

The package operates alongside companion utilities such as PJI_EXTRACTION_UTIL, PJI_PROCESS_UTIL, PJI_UTILS, PJI_FM_SUM_MAIN and PJI_RM_SUM_MAIN. Its primary responsibility is the maintenance of extraction scope, denormalized organization and resource hierarchies, and the statistics and staging tables that the extraction processes depend upon.

Key Procedures and Functions

Nine documented procedures are exposed by the package, each supporting a discrete step of the extraction lifecycle:

  • UPDATE_PJI_EXTR_SCOPE — establishes or refreshes the set of projects and entities that fall within the current extraction scope, aligning the extraction with active project definitions.
  • POPULATE_ORG_EXTR_INFO — seeds organization-related extraction information used to resolve the reporting hierarchy for extracted project facts.
  • UPDATE_ORG_EXTR_INFO — maintains and refreshes the organization extraction records after source hierarchy changes.
  • ANALYZE_PJI_FACTS — invokes statistics collection against PJI fact tables to keep optimizer statistics current for extraction and reporting queries.
  • SEED_PJI_RM_STATS — initializes resource management statistics rows used during the resource-based extraction cycle.
  • UPDATE_PJI_RM_WORK_TYPE_INFO — refreshes the work type information relied upon by resource extraction processing.
  • UPDATE_PJI_ORG_HRCHY — rebuilds the organization hierarchy information consumed by PJI denormalized organization tables.
  • UPDATE_RESOURCE_DATA — refreshes denormalized resource data, including resource-related delta processing used by the resource summary mains.
  • TRUNCATE_PJI_TABLES — clears transient PJI staging and denormalized tables ahead of a full extraction run.

Tables Accessed

The package interacts with configuration, staging, and source tables through APPS synonyms. It reads and writes PJI control and staging tables including PJI_SYSTEM_SETTINGS, PJI_SYSTEM_PARAMETERS, PJI_SYSTEM_CONFIG_HIST, PJI_MT_PRC_STEPS, and PJI_RM_WORK_TYPE_INFO, which govern extraction parameters and processing state. Project source data is obtained from PA_PROJECTS_ALL, PA_PROJECT_TYPES_ALL, PA_PJI_PROJ_EVENTS_LOG and PA_WORK_TYPES_B, while resource and organization denormalization draws on PA_RESOURCES_DENORM, PJI_RESOURCES_DENORM, PJI_ORG_DENORM, PJI_ROWID_ORG_DENORM, PJI_ROWID_RESOURCES_DENORM, PJI_RES_DELTA and HRI_ORG_HRCHY_SUMMARY. Environment and metadata queries use FND_PROFILE, FND_PROFILE_OPTIONS, FND_PROFILE_OPTION_VALUES, FND_MESSAGE, FND_GLOBAL, DUAL, V$PARAMETER and DBA_REGISTERED_MVIEWS; BIS_MV_REFRESH and BIS_OBJ_DEPENDENCY support refresh orchestration.

Usage Notes

This package is not referenced by any other database object and is intended to be called from the PJI extraction concurrent programs and orchestration packages rather than invoked manually by end users. Typical invocation occurs when administrators run the PJI extraction and summarization programs, during which TRUNCATE_PJI_TABLES and the update procedures are executed in sequence before the fact-summary mains process the staged data. Because it manipulates scope, statistics, and hierarchy information, the package should be run only within the supported extraction window, and customizations that call it should preserve the documented processing order to avoid inconsistent denormalized data.