Search Results pji_rm_res_wt_f

Overview

The table PJI_RM_RES_WT_F is a core data repository within the Oracle E-Business Suite (EBS) Project Intelligence (PJI) module, specifically for releases 12.1.1 and 12.2.2. It functions as a fact table designed to store aggregated historical metrics for resource management analysis. Its primary role is to support performance reporting by holding pre-summarized data for key measures related to resource utilization and availability. This aggregation is performed across critical dimensions such as resource, work type, and project, enabling efficient analytical queries for project portfolio reporting. It is important to note that the PJI module itself is marked as obsolete in the provided documentation, indicating that while this table exists in the schema, its associated application functionality may be deprecated or superseded in later versions or cloud offerings.

Key Information Stored

The table stores summarized factual data, which typically includes numeric measures aggregated by various dimension keys. While the specific measure columns are not detailed in the provided excerpt, based on its description and standard data warehousing design for PJI, the table would contain aggregated values for metrics like capacity hours, scheduled hours, actual hours, utilization percentages, and availability hours. The key dimension foreign key columns stored, as evidenced by the foreign key relationships, are PROJECT_ID, WORK_TYPE_ID, JOB_ID, EXPENDITURE_ORGANIZATION_ID, and EXPENDITURE_ORG_ID. These columns link the summarized facts to master data in other EBS tables, providing context for the resource, the type of work performed, the associated project, and the responsible organizations. The table likely also includes time period columns (e.g., PERIOD_ID, PERIOD_TYPE_ID) for temporal analysis.

Common Use Cases and Queries

This table is primarily accessed for historical trend analysis and standardized operational reporting within the Project Intelligence framework. Common use cases include analyzing resource utilization trends over time by job role or organization, comparing planned versus actual effort by work type, and generating capacity planning reports. A typical analytical query would join this fact table to its dimension tables to produce a readable report. For example, to analyze utilization by job and organization, a query might follow this pattern:

• SELECT j.NAME JOB_NAME, hou.NAME ORGANIZATION, SUM(f.UTILIZATION_HRS) UTIL_HRS, SUM(f.AVAILABILITY_HRS) AVAIL_HRS FROM PJI_RM_RES_WT_F f, PER_JOBS j, HR_ALL_ORGANIZATION_UNITS hou WHERE f.JOB_ID = j.JOB_ID AND f.EXPENDITURE_ORG_ID = hou.ORGANIZATION_ID AND f.PERIOD_ID = :P_PERIOD GROUP BY j.NAME, hou.NAME;

Direct queries against this table are generally performed by pre-built PJI reports or custom data extracts for downstream business intelligence systems.

Related Objects

PJI_RM_RES_WT_F is centrally connected to several key master data tables in the EBS schema via foreign key constraints, as documented. These relationships are fundamental for enriching the aggregated facts with descriptive information:

• PA_PROJECTS_ALL: Joined via PJI_RM_RES_WT_F.PROJECT_ID to provide project details.
• PA_WORK_TYPES_B: Joined via PJI_RM_RES_WT_F.WORK_TYPE_ID to provide work type classification.
• PER_JOBS: Joined via PJI_RM_RES_WT_F.JOB_ID to provide job/role definitions.
• HR_ALL_ORGANIZATION_UNITS: Joined via two columns: PJI_RM_RES_WT_F.EXPENDITURE_ORGANIZATION_ID and PJI_RM_RES_WT_F.EXPENDITURE_ORG_ID, to provide organizational context.

This table is also likely a source for various PJI reporting views and may be populated by concurrent processes or ETL jobs from transactional tables in the Project Accounting (PA) and Human Resources (HR) modules.