Search Results pa_resource_availability_v




Overview
Explore in AI Chat

Overview

PA_RESOURCE_AVAILABILITY_V is an Oracle EBS Projects (PA) reporting view owned by the APPS schema. It is documented as VALID in ETRM for both 12.1.1 and 12.2.2. Its stated purpose is to present all schedulable resources together with their resource manager and their current availability context. The view consolidates denormalized resource records with current person and organization attributes, and it enriches each row with the resource's current project via packaged utility calls.

The object is read-only and exposes no maintenance path; it exists for inquiry, reporting, and integration. Because it is defined only over base objects and PL/SQL utility functions, it can be safely joined to other Projects and HR views in custom reports without risk of DML side effects.

The searched term "current_project_id" is a direct match for the view column CURRENT_PROJECT_ID. That column is populated by the packaged function PA_RESOURCE_UTILS.GET_CURRENT_PROJECT_ID rather than by a table join, which is a defining characteristic of this view and the reason users frequently search for it by column name.

Underlying Base Objects

The documented base objects are PA_RESOURCES_DENORM (synonym), PER_ALL_PEOPLE_F (synonym), HR_ALL_ORGANIZATION_UNITS_TL (synonym), and the package PA_RESOURCE_UTILS. The view aliases PA_RESOURCES_DENORM as RES, joins PER_ALL_PEOPLE_F twice (PER for the resource person, PER1 for the manager), and joins HR_ALL_ORGANIZATION_UNITS_TL as ORG for the organization name.

  • RES (PA_RESOURCES_DENORM) supplies the resource identity, organization, manager reference, job level, effective dates, and location attributes.
  • PER (PER_ALL_PEOPLE_F) supplies the resource person's email address and constrains the resource effective start date to a valid person effective period.
  • PER1 (PER_ALL_PEOPLE_F) is an outer-joined manager record; the join is skipped when MANAGER_ID is null, which permits managerless resources to appear.
  • ORG (HR_ALL_ORGANIZATION_UNITS_TL) supplies the translated organization name for the session language.
  • PA_RESOURCE_UTILS supplies GET_CURRENT_PROJECT_ID and GET_CURRENT_PROJECT_NAMENUMBER, evaluated per resource row.

Availability is effectively expressed through the SCHEDULABLE_FLAG = 'Y' filter and the effective-date logic: only the earliest resource effective start date whose end date is on or after TRUNC(SYSDATE) is retained, so one current row per schedulable resource is returned.

Key Columns

Common Use Cases and Queries

Typical uses include resource availability listings for staffing, manager-to-resource assignment reporting, and determining which project a resource is currently assigned to.

  • List all currently schedulable resources with manager and current project.
  • Filter by organization or country for regional availability analysis.
  • Identify resources with a given CURRENT_PROJECT_ID to confirm project staffing.
  • Join to PA_PROJECTS_ALL on CURRENT_PROJECT_ID for project detail.

Example:

SELECT resource_id, resource_name, organization_name, manager_name, job_level, current_project_id, current_project_name_number, resource_email_address FROM apps.pa_resource_availability_v WHERE current_project_id IS NOT NULL ORDER BY organization_name, resource_name;

Because CURRENT_PROJECT_ID and CURRENT_PROJECT_NAME_NUMBER are computed by PA_RESOURCE_UTILS per row, queries returning large numbers of resources evaluate the function repeatedly; restrict the driving set with organization or resource filters before referencing these columns to reduce execution cost.