Results for “pa_res_availability”

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

Overview

PA_RES_AVAILABILITY is a table in the PA (Projects) schema of Oracle E-Business Suite, present and valid in both release 12.1.1 and 12.2.2. It stores the available and overcommitted percentage and hours of a resource for a given period. In practical terms, the table acts as the resource-availability ledger used by Oracle Projects resource management functionality: for each resource and each planning period, it records how much of the resource's capacity remains available, or conversely how far the resource has been overcommitted, expressed both as a percentage and as an absolute number of hours.

The ETRM metadata classifies the object heuristically as satellite-leaning in a Data Vault sense. This is a modeling suggestion rather than a physical attribute: the table behaves as a descriptive, time-variant satellite attached to the PA_RESOURCES hub through its RESOURCE_ID foreign key, capturing period-by-period measurements rather than defining a new business entity or resolving a many-to-many relationship. The table is owned by PA and contains eleven documented columns.

Key Information Stored

The primary key is PA_RES_AVAILABILITY_PK, defined on the composite of RESOURCE_ID, START_DATE, and RECORD_TYPE. A unique index, PA_RES_AVAILABILITY_U1, extends this business-key candidate set with PERCENT (RESOURCE_ID, START_DATE, RECORD_TYPE, PERCENT). The principal columns are:

• RESOURCE_ID — foreign key to PA_RESOURCES; identifies the resource whose availability is measured. This is the hub reference that anchors the satellite.
• START_DATE and END_DATE — the effective period boundaries for the recorded availability figures.
• RECORD_TYPE — discriminator distinguishing the category of availability record, and part of the composite primary key.
• PERCENT — the available or overcommitted percentage for the resource in the period; included in the unique business-key index.
• HOURS — the corresponding available or overcommitted hours for the same period.
• CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN — standard Oracle EBS audit columns recording who created and last modified each row and when.

The surrogate composite primary key (RESOURCE_ID, START_DATE, RECORD_TYPE) is distinct from the fuller business-key candidate captured by PA_RES_AVAILABILITY_U1, which adds PERCENT. Report authors should be aware that the same resource and period can legitimately carry multiple percentage rows under different RECORD_TYPE values.

Common Use Cases and Queries

Typical uses include resource utilization reporting, overcommitment dashboards, and capacity planning extracts. A representative query retrieves the availability profile for a resource over a date range:

• SELECT resource_id, start_date, end_date, record_type, percent, hours FROM pa.pa_res_availability WHERE resource_id = :resource_id AND start_date BETWEEN :from_date AND :to_date ORDER BY start_date, record_type;
• Overcommitment reporting joins PA_RESOURCES to obtain the resource name and filters rows where percent or hours indicate negative availability.
• Period-level aggregation groups by start_date and record_type to summarize total available and overcommitted capacity across a resource group.

Related Objects

The dominant relationship is the foreign key from PA_RES_AVAILABILITY.RESOURCE_ID to PA_RESOURCES, making PA_RESOURCES the primary parent for any join that needs resource names, organization, or resource group attributes. Because availability is consumed by resource management and project planning screens, related objects also include the resource management and assignment tables and views that read availability to schedule or staff project work, as well as the standard PA audit and lookup objects referenced by RECORD_TYPE. Any reporting extract should join PA_RESOURCES on RESOURCE_ID and use START_DATE and END_DATE to constrain the desired planning horizon.