Results for “pji_rm_res_wt_f”

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

Overview

PJI_RM_RES_WT_F is a summary (fact-style) table in the PJI schema that stores pre-aggregated utilization and availability measures for Project Intelligence, an obsolete module in Oracle EBS 12.1.1 and 12.2.2. Rather than reading raw expenditure and timecard transactions at report-run time, Project Intelligence materializes resource-and-worktype rollups into this table so that utilization dashboards and analytical inquiries can retrieve results without expensive joins against transactional sources. The table contains 23 documented columns and is keyed primarily by a composite of period, time, resource, job, worktype, project, and organization identifiers, with two distinct organization columns (EXPENDITURE_ORGANIZATION_ID and EXPENDITURE_ORG_ID) preserved for backward compatibility across the PA organization model.

Based on its foreign key structure—five outbound references to master and transactional entities—the heuristic Data Vault classification for this object is link. This is a modeling suggestion only: the table behaves as an intersection or relationship record that connects resources, jobs, work types, projects, and organizations to a set of numeric measures, which is the classic shape of a link table carrying descriptive context.

Key Information Stored

The most significant columns fall into dimensional keys and additive measures.

• PROJECT_ID — identifies the project whose resource utilization is summarized; foreign key to PA_PROJECTS_ALL.
• WORK_TYPE_ID — the work type classification (for example, billable versus internal); foreign key to PA_WORK_TYPES_B.
• JOB_ID — the job of the assigned resource; foreign key to PER_JOBS.
• PERSON_ID — the resource whose availability and utilization are measured.
• EXPENDITURE_ORGANIZATION_ID and EXPENDITURE_ORG_ID — the expenditure organization context; both reference HR_ALL_ORGANIZATION_UNITS.
• TIME_ID, PERIOD_TYPE_ID, and CALENDAR_TYPE — the period, calendar, and time bucket dimensions that define the reporting grain.
• RECORD_TYPE — discriminates the kind of summarized record being stored.
• TOTAL_HRS_A and CAPACITY_HRS_A — total and available capacity hours for the resource in the period.
• CONF_HRS_S, PROV_HRS_S, and UNASSIGNED_HRS_S — confirmed, provisional, and unassigned hours measures.
• BILL_HRS_A, CONF_OVERCOM_HRS_S, and PROV_OVERCOM_HRS_S — billable hours and confirmed/provisional over-commitment hours.
• CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, and LAST_UPDATE_LOGIN — standard EBS audit columns.

The metadata does not document a single surrogate primary key column or a named unique index. In practice the business-key candidate is the composite of the dimensional columns (period, time, person, job, worktype, project, and organization); any query should treat the full dimensional set as the logical key.

Common Use Cases and Queries

Because the table is a pre-aggregated summary, its principal use is reporting on resource utilization and availability. Typical scenarios include capacity-versus-demand analysis, billable utilization percentage, over-commitment detection, and unassigned-hours trending by organization or job.

A representative query aggregates confirmed, provisional, and total hours by organization and time period:

• SELECT EXPENDITURE_ORGANIZATION_ID, TIME_ID, SUM(CONF_HRS_S), SUM(PROV_HRS_S), SUM(TOTAL_HRS_A), SUM(CAPACITY_HRS_A) FROM PJI.PJI_RM_RES_WT_F GROUP BY EXPENDITURE_ORGANIZATION_ID, TIME_ID;

Drill-down queries filter by PERSON_ID or JOB_ID to examine a single resource's utilization across work types. Because PROJECT_ID, WORK_TYPE_ID, JOB_ID, and the organization columns are all foreign keys, joins to their parent tables yield human-readable report output, and RECORD_TYPE can be used to separate measure families when the table stores more than one record category.

Related Objects

The FK metadata establishes the following significant relationships:

• PA_PROJECTS_ALL — via PROJECT_ID; supplies project attributes and names.
• PA_WORK_TYPES_B — via WORK_TYPE_ID; supplies work type names and billable flags.
• PER_JOBS — via JOB_ID; resolves job titles for utilization reporting.
• HR_ALL_ORGANIZATION_UNITS — referenced twice, via EXPENDITURE_ORGANIZATION_ID and EXPENDITURE_ORG_ID; provides organizational hierarchy context.
• PER_ALL_PEOPLE_F — the principal dependent lookup for PERSON_ID, joining to the person's current assignment (not defined as a formal FK but required for name and assignment resolution).

As PJI is an obsolete module, no supported public APIs or concurrent programs remain active against this table in current releases. Its content should be treated as historical, and consumers should confirm population status before relying on it for live utilization reporting.