Results for “pji_resources_denorm”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

PJI_RESOURCES_DENORM is a denormalized table within the PJI (Project Intelligence) product family of Oracle E-Business Suite. Project Intelligence historically provided analytical and reporting infrastructure for project-based data, and this table was designed to hold resource attributes in a flattened structure optimized for query and reporting performance rather than strict normalization. The ETRM classification of this object places it in the PJI schema, which is documented as obsolete in both Oracle EBS 12.1.1 and 12.2.2, meaning that the object is retained for reference and historical compatibility but is not implemented in current databases.

From a data modeling perspective, the heuristic Data Vault classification for PJI_RESOURCES_DENORM is link. This classification is derived from the foreign key structure, which shows the table acting as a connective entity between person records and job records. In Data Vault terms, a link table captures relationships between business entities, and this table's foreign keys to PER_ALL_PEOPLE_F and PER_JOBS fit that pattern. It should be emphasized that this is a modeling suggestion inferred from the documented constraints rather than an explicit Data Vault design decision.

Key Information Stored

The documented physical schema for this table in ETRM 12.1.1 shows eight columns: PERSON_ID, RESOURCE_ID, RESOURCE_NAME, START_DATE, END_DATE, JOB_ID, ORGANIZATION_ID, and UTILIZATION_FLAG. The most significant of these are described below.

• RESOURCE_ID — Serves as the surrogate primary key candidate for the resource record and is the most likely unique identifier within this denormalized structure.
• PERSON_ID — A foreign key referencing PER_ALL_PEOPLE_F, linking the resource to the underlying person or employee record in Oracle HRMS.
• JOB_ID — A foreign key referencing PER_JOBS, tying the resource to a defined job or position within the HR schema.
• RESOURCE_NAME — A descriptive attribute holding the display name of the resource, carried in denormalized form to avoid joins during reporting.
• START_DATE and END_DATE — Define the effective date range during which the resource attribute record is valid. The ETRM metadata records END_DATE as part of the composite relationship to PER_ALL_PEOPLE_F.
• ORGANIZATION_ID — Identifies the organization context to which the resource belongs, supporting organizational reporting and filtering.
• UTILIZATION_FLAG — A flag indicating whether the resource is considered available or utilized for project staffing purposes.

Business-key candidates include PERSON_ID combined with START_DATE and END_DATE, reflecting the effective-dated nature of the relationship to PER_ALL_PEOPLE_F.

Common Use Cases and Queries

Because the table is denormalized, it was intended to support reporting queries that would otherwise require multiple joins across HR and project schemas. A typical reporting query might join PJI_RESOURCES_DENORM to PER_ALL_PEOPLE_F on PERSON_ID to retrieve resource details, or to PER_JOBS on JOB_ID to report resources by job classification. Analytical queries might aggregate UTILIZATION_FLAG values by ORGANIZATION_ID to derive utilization metrics.

A representative pattern is:

• SELECT r.RESOURCE_NAME, r.START_DATE, r.END_DATE, j.JOB_NAME FROM PJI_RESOURCES_DENORM r, PER_JOBS j WHERE r.JOB_ID = j.JOB_ID
• SELECT r.ORGANIZATION_ID, COUNT(*) FROM PJI_RESOURCES_DENORM r WHERE r.UTILIZATION_FLAG = 'Y' GROUP BY r.ORGANIZATION_ID

Given the table's obsolete status, these patterns are primarily of historical or migration interest rather than active production use.

Related Objects

The FK and relationship data identify the following significant related objects:

• PER_ALL_PEOPLE_F — Joined on PERSON_ID; the primary source of person and employee attributes referenced by this table.
• PER_JOBS — Joined on JOB_ID; supplies job name and job definition information for resources.
• PER_ALL_ASSIGNMENTS_F — Commonly associated with PER_ALL_PEOPLE_F for assignment-level reporting in Project Intelligence contexts.
• PJI_RESOURCES — The likely normalized counterpart from which this denormalized table is derived.
• PJI_PROJECT_RESOURCES — Related Project Intelligence resource-to-project association structures.

These relationships should be validated against the specific EBS release in use, as the PJI schema is documented as obsolete.