Search Results job_code

Overview

PA_REP_PROJ_RES_COMP_V is a reporting view owned by the APPS schema in Oracle E-Business Suite, defined within the Projects (PA) product family. As documented in the ETRM metadata, it lists all resource competence details organized by project. The view consolidates resource identity, organizational assignment, job classification, and HR competence information into a single queryable structure, which allows project and resource managers, as well as integration developers, to retrieve competence attributes alongside project and resource context without navigating the multiple base tables individually.

The view is particularly significant because competence data is generated dynamically through PL/SQL functions rather than being stored as static columns. The functions PA_HR_COMPETENCE_UTILS.GET_RES_COMPETENCES, GET_RES_COMPETENCES_COUNT, and GET_RES_COMP_LAST_UPDATED are invoked within the view's SELECT list, meaning every query against the view triggers real-time competence retrieval for the associated person. This makes the view a convenient abstraction layer but also introduces performance considerations for large datasets.

Underlying Base Objects

The view is constructed from several documented base objects, joined primarily on resource, project, and job identifiers:

• PA_RESOURCES_DENORM — the denormalized resource table supplying resource ID, name, person ID, manager details, job ID, schedulable and billable flags, and person type.
• HR_ORGANIZATION_UNITS — provides the owning organization ID and name.
• PA_PROJECTS_ALL — supplies project ID, name, and segment1 (project number).
• PA_PROJECT_ASSIGNMENTS — used in an inline distinct subquery (PROJ_RES_INFO) to associate resources with the projects they are assigned to.
• PER_JOBS — joined on JOB_ID to return the job name.
• PER_JOB_EXTRA_INFO — outer-joined on JOB_ID with the category 'JOB CATEGORY' to surface job-level descriptive information (JEI_INFORMATION4 and 5, exposed as JOB_LEVEL and JOB_CODE).
• PA_HR_COMPETENCE_UTILS — the package supplying the competence functions.
• HR_GENERAL and HR_SECURITY — referenced package objects supporting HR security and general HR utilities.

Notable filter predicates restrict rows to resources whose earliest effective record is current (RESOURCE_EFFECTIVE_START_DATE equals the minimum start date where the end date is on or after SYSDATE), resources marked UTILIZATION_FLAG = 'Y', and only those with an existing project assignment.

Key Columns

• ORGANIZATION_ID / ORGANIZATION_NAME — the resource's home organization.
• PROJECT_ID / PROJECT_NAME / PROJECT_NUMBER — project context, with PROJECT_NUMBER mapping to SEGMENT1.
• RESOURCE_ID / RESOURCE_NAME — the resource identity.
• RESOURCE_COMPETENCIES / RESOURCE_COMP_COUNT / LAST_UPDATED — output of the competence utility functions, giving the competence listing, count, and last-update timestamp.
• MANAGER_ID / MANAGER_NAME — the resource's manager.
• JOB_ID / JOB_NAME / JOB_LEVEL / JOB_CODE — job classification; JOB_LEVEL and JOB_CODE derive from PER_JOB_EXTRA_INFO columns JEI_INFORMATION4 and 5.
• SCHEDULABLE_FLAG / BILLABLE_FLAG — resource flags, with the schedulable flag defaulting to 'N' when null.
• PERSON_TYPE — the resource person type.

Common Use Cases and Queries

A frequent requirement is finding resources by job classification. Because the view exposes JOB_CODE (via PER_JOB_EXTRA_INFO), it supports competency and staffing reports filtered on job code:

• List all resources with a given job code on active projects.
• Count competent resources per project for staffing analysis.
• Identify resources whose competence data was recently updated.

A representative query follows:

SELECT PROJECT_NUMBER, PROJECT_NAME, RESOURCE_NAME, JOB_NAME, JOB_CODE, RESOURCE_COMP_COUNT FROM APPS.PA_REP_PROJ_RES_COMP_V WHERE JOB_CODE = :p_job_code AND BILLABLE_FLAG = 'Y' ORDER BY PROJECT_NUMBER, RESOURCE_NAME;

Because competence values are computed per row via PL/SQL calls, queries should filter aggressively on ORGANIZATION_ID, PROJECT_ID, or JOB_CODE to limit the row set before competence functions execute.