Search Results pa_project_roles

Overview

APPS.PA_PROJECT_OPEN_ASSNS_STAFF_V is a reporting view in Oracle EBS Projects (PA) that exposes project staffing assignments — open assignments and their associated project, role, location, and search criteria — in a single denormalized result set. Its principal consumer is the Oracle Project Resource Management (PRM) user interface and the Staffing/Assignment search pages, where project managers locate open assignments to place candidates. Because the view resolves many display attributes (descriptive flexfield names, lookup meanings, manager names, organization names, country and territory names) at query time, it is equally suitable for custom reports, Oracle XML Publisher data templates, and integration extracts that need assignment data without re-implementing PA's internal lookup logic.

In the context of the user search term pa_project_roles, the view is significant because it exposes the assignment's project role through the Project Role Types lookup: the columns project_role_id and project_role_name are sourced via PA_PROJECT_ROLE_TYPES (VIEW), which in turn resolves the project role meaning through PA_LOOKUPS. This makes PA_PROJECT_OPEN_ASSNS_STAFF_V the primary published object for querying which project roles are attached to open assignments, without needing to join PA_LOOKUPS manually. The ETRM metadata is documented for 12.2.2 and the object is compatible with 12.1.1; there are no APPS-private data structures exposed.

Underlying Base Objects

The view is defined over several categories of base objects. The core transactional object is PA_PROJECT_ASSIGNMENTS (SYNONYM), which supplies assignment_id, assignment_name, assignment_type, assignment_number, start_date, end_date, status_code, staffing_priority_code, min/max_resource_job_level, competence_match_weighting, availability_match_weighting, job_level_match_weighting, project_role_id, work_type_id, and the search criteria columns. Project header attributes come from PA_PROJECTS_ALL, joined to HR_ALL_ORGANIZATION_UNITS for the carrying-out organization name and to PA_PROJECT_STATUSES for the project status name.

Assignment status and staffing priority meanings are resolved through the PA_LOOKUPS (VIEW), and the project role name through PA_PROJECT_ROLE_TYPES (VIEW). Location details come from PA_LOCATIONS, with country and search country names resolved through FND_TERRITORIES_TL, and calendar name through JTF_CALENDARS_TL. Work type descriptions come from PA_WORK_TYPES_V, and subteam information from PA_PROJECT_SUBTEAMS, PA_PROJECT_SUBTEAM_PARTIES, and PA_PROJECT_SUBTEAM_PARTIES. Several PL/SQL utilities are invoked as scalar subqueries or function calls: PA_PROJECT_PARTIES_UTILS.GET_CURRENT_PROJECT_MANAGER and GET_CURRENT_PROJ_MANAGER_NAME, PA_PROJECTS_MAINT_UTILS.GET_PRIMARY_CUSTOMER_NAME, PA_EXPENDITURES_UTILS.GetOrgTlName, PA_RESOURCE_PVT.GET_MANAGER_RESOURCE_ID, and PA_ROLE_JOB_BG_UTILS. Security and access filtering may draw on FND_USER, FND_GRANTS, FND_OBJECTS, FND_OBJECT_INSTANCE_SETS, FND_FORM_FUNCTIONS, FND_COMPILED_MENU_FUNCTIONS, PA_SECURITY_PVT, and PA_ROLE_STATUS_MENU_MAP.

Key Columns

• project_id, name, segment1 — Project identifier, project name, and project number; segment1 is commonly concatenated with name as a display label.
• carrying_out_organization_id and organization name — The delivering organization, resolved from HR_ALL_ORGANIZATION_UNITS.
• manager_resource_id, current project manager name — Derived from PA_PROJECT_PARTIES_UTILS and PA_RESOURCE_PVT.
• assignment_id, assignment_name, assignment_number, assignment_type — The assignment identity and its classification.
• start_date, end_date, assignment_duration, assignment_effort — Assignment window; duration is computed as (trunc(end_date) - trunc(start_date) + 1).
• status_code, project_status_name — Assignment status code and the underlying project status name.
• project_role_id, project_role_name — The project role attached to the assignment, resolved through PA_PROJECT_ROLE_TYPES; this is the principal link to the pa_project_roles search term.
• min_resource_job_level, max_resource_job_level — Job-level range used for matching candidates.
• staffing_priority_code and meaning — Priority of the open assignment.
• competence_match_weighting, availability_match_weighting, job_level_match_weighting — Weightings applied during automated candidate search.
• search_min_availability, search_min_candidate_score, search_exp_org_hierarchy, search_country_name — Candidate search parameters stored on the assignment.
• location_id, city, region, country_code, country — Assignment location details.
• calendar_name, work type name, subteam name — Supporting descriptive attributes.

Common Use Cases and Queries

Typical uses include: reporting all open assignments by project role; identifying assignments whose end dates fall within a period for staffing escalation; extracting open assignments for a specific country or expenditure organization; and feeding external candidate-matching interfaces with the search weightings and criteria columns stored on the assignment.

SELECT project_id
     , name
     , segment1
     , assignment_id
     , assignment_name
     , project_role_name
     , start_date
     , end_date
     , assignment_duration
     , staffing_priority_code
  FROM apps.pa_project_open_assns_staff_v
 WHERE project_role_name = 'Developer'
   AND end_date >= TRUNC(SYSDATE)
 ORDER BY start_date, assignment_name;

SELECT segment1
     , name
     , assignment_number
     , project_role_name
     , country
     , search_min_candidate_score
     , search_min_availability
  FROM apps.pa_project_open_assns_staff_v
 WHERE country = 'US'
   AND project_id = :p_project_id;

Because the view calls PL/SQL utility packages per row, large unfiltered extracts can be expensive; filter on project_id, start_date/end_date, or project_role_id before scanning. The view is owned by APPS and should be referenced with the APPS schema prefix, and its lookup-derived columns are subject to the language of the session for the underlying _TL tables.