Search Results pa_project_open_assns_staff_v




Overview
Explore in AI Chat

Overview

PA_PROJECT_OPEN_ASSNS_STAFF_V is an Oracle E-Business Suite Projects (PA) view owned by the APPS schema. It presents open project staffing assignments — that is, assignment records associated with projects that are candidates for resource search and staffing activity. The view consolidates project header information, assignment details, project manager data, location, calendar, work type, project role, and staffing search parameters into a single denormalized result set that supports the Project Resource Management staffing workflow.

The view is marked VALID in the ETRM 12.2.2 metadata and is available in both 12.1.1 and 12.2.2. Its role is primarily operational and reporting-oriented: it feeds staffing pages, resource search screens, and candidate matching engines that depend on scoped assignment attributes such as competence weighting, availability weighting, job-level weighting, and minimum candidate score. Because it embeds calls to PL/SQL functions and packaged utilities, it should be treated as a convenience API rather than a base table for high-volume bulk extraction.

Underlying Base Objects

The view is defined over a broad set of base tables, synonyms, views, and PL/SQL packages. The documented referenced objects include PA_PROJECTS_ALL (project header), PA_PROJECT_ASSIGNMENTS (assignment records), PA_PROJECT_SUBTEAMS and PA_PROJECT_SUBTEAM_PARTIES (subteam membership), PA_PROJECT_PARTIES (project party relationships), PA_PROJECT_STATUSES, PA_LOCATIONS and HR_ALL_ORGANIZATION_UNITS (location and organization name resolution), JTF_CALENDARS_TL (calendar names), FND_TERRITORIES_TL (country names), and PA_LOOKUPS (lookup meanings for status and priority). It also references PA_WORK_TYPES_V, PA_PROJECT_ROLE_TYPES, PER_JOBS, PER_JOB_GROUPS, PER_ORGANIZATION_STRUCTURES, and PER_ORG_STRUCTURE_VERSIONS to resolve job, role, and search organization hierarchy values.

Packaged logic is central to the view. PA_RESOURCE_PVT.GET_MANAGER_ID, GET_MANAGER_NAME, and GET_MANAGER_RESOURCE_ID supply the project manager and manager resource identifiers. PA_PROJECTS_MAINT_UTILS.GET_PRIMARY_CUSTOMER_NAME resolves the customer name, and PA_EXPENDITURES_UTILS.GETORGTLNAME translates expenditure organization and start organization identifiers. Additional security and role logic involves PA_SECURITY_PVT, PA_RESOURCE_UTILS, PA_ROLE_JOB_BG_UTILS, PA_UTILS, and PA_PROJECT_PARTIES_UTILS. Because these are function calls executed per row, query performance depends heavily on row filtering pushed to the assignment and project base tables.

Key Columns

Common Use Cases and Queries

The view is commonly used to drive staffing dashboards, identify assignments open for candidate search, and report on staffing priority and search configuration by project. A typical filtered query restricts by project and assignment status:

SELECT PROJECT_ID, NAME, ASSIGNMENT_ID, ASSIGNMENT_NAME, START_DATE, END_DATE, ASSIGNMENT_DURATION, STAFFING_PRIORITY_CODE FROM APPS.PA_PROJECT_OPEN_ASSNS_STAFF_V WHERE PROJECT_ID = :p_project_id ORDER BY START_DATE;

Resource managers frequently query for open assignments in a given organization or location with active search enabled:

SELECT PROJECT_ID, NAME, ASSIGNMENT_NAME, CITY, COUNTRY_CODE, SEARCH_MIN_CANDIDATE_SCORE FROM APPS.PA_PROJECT_OPEN_ASSNS_STAFF_V WHERE CARRYING_OUT_ORGANIZATION_ID = :p_org_id AND ENABLE_AUTOMATED_SEARCH = 'Y';

Because function-based columns invoke PL/SQL for each returned row, always apply selective WHERE predicates on PROJECT_ID, ASSIGNMENT_ID, or organization columns, and avoid unbounded full scans. Joins to PA_PROJECTS_ALL are unnecessary since project attributes are already denormalized, and output is best consumed for interactive staffing screens rather than large batch extracts.