Results for “pa_resources_denorm_n2”

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

Overview

PA_RESOURCES_DENORM is a denormalized table in the Oracle Projects (PA) schema that stores pre-joined resource attributes for reporting and inquiry purposes. Rather than requiring application code or reports to traverse the normalized PA_RESOURCES table and join outward to PER_JOBS, HR_ALL_ORGANIZATION_UNITS, and other HR objects for every resource lookup, PA_RESOURCES_DENORM flattens the most frequently accessed descriptive attributes into a single wide row keyed by person and effective date. It is owned by the PA schema and carries a VALID status in both Oracle E-Business Suite 12.1.1 and 12.2.2.

From a Data Vault modeling perspective, the heuristic classification of PA_RESOURCES_DENORM is link. This classification is derived from its foreign key structure: the table sits at the intersection of PER_JOBS (JOB_ID), HR_ALL_ORGANIZATION_UNITS (RESOURCE_ORGANIZATION_ID), and PA_RESOURCES (RESOURCE_ID), and therefore functions as a relationship-bearing artifact rather than a pure hub of a single business concept or a satellite of descriptive history. The table should be understood as a denormalized convenience structure that materializes relationships and attributes together for performance, not as a system of record.

Key Information Stored

The table contains 30 documented columns. The most significant identity and relationship columns are:

• PERSON_ID — the person identifier carried from HR; together with RESOURCE_EFFECTIVE_START_DATE it forms the unique business key candidate PA_RESOURCES_DENORM_U1.
• RESOURCE_ID — the surrogate primary key of the underlying PA_RESOURCES record; the FK to PA_RESOURCES.
• RESOURCE_EFFECTIVE_START_DATE and RESOURCE_EFFECTIVE_END_DATE — the date-bounded validity window of the denormalized row.
• RESOURCE_NAME, RESOURCE_TYPE, and RESOURCE_PERSON_TYPE — descriptive classification of the resource.
• RESOURCE_ORGANIZATION_ID and RESOURCE_ORG_ID — organization references, the former with an FK to HR_ALL_ORGANIZATION_UNITS.
• RESOURCE_COUNTRY_CODE, RESOURCE_COUNTRY, RESOURCE_REGION, and RESOURCE_CITY — geographic attributes maintained for reporting.
• RESOURCE_JOB_LEVEL and JOB_ID — job classification, with JOB_ID carrying an FK to PER_JOBS.
• MANAGER_ID and MANAGER_NAME — the reporting manager of the resource.
• EMPLOYEE_FLAG, BILLABLE_FLAG, UTILIZATION_FLAG, and SCHEDULABLE_FLAG — operational status indicators used in staffing and utilization reporting.

The surrogate key concept is represented by RESOURCE_ID, while the documented business-key candidate is the composite unique index PA_RESOURCES_DENORM_U1 on PERSON_ID and RESOURCE_EFFECTIVE_START_DATE. Standard audit columns (CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN) and the program/concurrent columns (REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE) are also present.

Common Use Cases and Queries

The primary use case is resource-level reporting where denormalized attributes are needed without joining PA_RESOURCES to HR tables. Typical patterns include a time-bounded resource lookup by person, and a filter on billable or schedulable resources for staffing analysis:

• Point-in-time lookup: SELECT RESOURCE_ID, RESOURCE_NAME, RESOURCE_JOB_LEVEL, MANAGER_NAME FROM PA_RESOURCES_DENORM WHERE PERSON_ID = :p_person_id AND TRUNC(SYSDATE) BETWEEN RESOURCE_EFFECTIVE_START_DATE AND RESOURCE_EFFECTIVE_END_DATE.
• Staffing/capability reporting: filtering by BILLABLE_FLAG, UTILIZATION_FLAG, and SCHEDULABLE_FLAG combined with RESOURCE_ORGANIZATION_ID to produce organization-level resource inventories.
• Geographic distribution reporting: grouping by RESOURCE_COUNTRY, RESOURCE_REGION, and RESOURCE_CITY for resource-allocation dashboards.

Because the table is denormalized, it is generally treated as read-oriented; direct DML should be avoided in favor of the processes that populate it from PA_RESOURCES and HR.

Related Objects

The documented foreign keys and parent relationships anchor PA_RESOURCES_DENORM to the following objects:

• PA_RESOURCES — primary relationship via RESOURCE_ID; the normalized source of resource records.
• PER_JOBS — joined on JOB_ID to supply job definition data.
• HR_ALL_ORGANIZATION_UNITS — joined on RESOURCE_ORGANIZATION_ID for organization attributes.
• PER_ALL_PEOPLE_F — implicit parent of PERSON_ID for person-level attributes across date ranges.
• PER_ALL_ASSIGNMENTS_F — typical upstream source of job and organization context that feeds the denormalized columns.
• PA_RESOURCE_TXN_ATTRIBUTES and PA_PROJECT_PARTIES — commonly queried alongside for resource-role and assignment context.

These relationships should be validated against the specific 12.1.1 or 12.2.2 instance, as denormalized maintenance processes differ by release and patch level.