Search Results resource_comp_count

Overview

PA_REP_ORG_RES_COMP_V is an APPS-owned reporting view in the Oracle E-Business Suite Projects (PA) module. As documented in the ETRM repository for releases 12.1.1 and 12.2.2, the view "lists all resource competence details by organizations." It consolidates resource, organizational, job, and competence information into a single queryable structure, allowing report developers and integrators to retrieve competence attributes for billable, schedulable resources without navigating the multiple base tables and PL/SQL utility packages that hold the raw data.

The view is primarily a reporting and integration construct rather than a transactional object. Because competence values are computed at runtime through PA_HR_COMPETENCE_UTILS, the view reflects the current state of competency records whenever it is queried. This makes it suitable for ad hoc reporting, Discoverer workbooks, and custom concurrent programs that need a denormalized, per-resource view of competence data organized by resource organization.

Underlying Base Objects

The documented base objects referenced by PA_REP_ORG_RES_COMP_V are:

PA_RESOURCES_DENORM (synonym) — the driving table, supplying resource identity, organization, manager, billable and schedulable flags, and person type.
PER_JOBS (synonym) — joined on JOB_ID to provide job name, and, via the extra information join, job level and code.
PER_JOB_EXTRA_INFO (synonym) — outer-joined with category 'JOB CATEGORY' to supply JOB_LEVEL (JEI_INFORMATION4) and JOB_CODE (JEI_INFORMATION5).
PER_PEOPLE_F (view) — used in a correlated subquery to restrict to valid person records.
PA_EXPENDITURES_UTILS (package) — supplies GETORGTLNAME for the organization name.
PA_HR_COMPETENCE_UTILS (package) — supplies GET_RES_COMPETENCES, GET_RES_COMPETENCES_COUNT, and GET_RES_COMP_LAST_UPDATED.
HR_GENERAL, HR_PERSON_NAME, HR_SECURITY (packages) — invoked indirectly by the resource and competence utility calls, and relevant to row-level security applied to person data.

The join logic selects only the earliest effective resource row per resource whose effective end date is on or after SYSDATE, filters to UTILIZATION_FLAG = 'Y', and requires a matching PER_PEOPLE_F row.

Key Columns

ORGANIZATION_ID / ORGANIZATION_NAME — the resource organization and its translated name.
RESOURCE_ID / RESOURCE_NAME — resource identifier and display name.
RESOURCE_COMPETENCIES — delimited competence text returned by GET_RES_COMPETENCES.
RESOURCE_COMP_COUNT — the count of competences for the resource, produced by GET_RES_COMPETENCES_COUNT. This is the column most often targeted by searches for "resource_comp_count".
LAST_UPDATED — the most recent competence update date for the person.
MANAGER_ID / MANAGER_NAME — the resource's manager.
JOB_ID / JOB_NAME / JOB_LEVEL / JOB_CODE — job attributes derived from PER_JOBS and PER_JOB_EXTRA_INFO.
SCHEDULABLE_FLAG / BILLABLE_FLAG / PERSON_TYPE — resource classification flags; SCHEDULABLE_FLAG defaults to 'N' when null.

Common Use Cases and Queries

Typical scenarios include identifying resources with a given number of competences, auditing competence currency, and populating competency dashboards by organization.

To list resources with at least one competence:

SELECT resource_id, resource_name, organization_name, resource_comp_count FROM pa_rep_org_res_comp_v WHERE resource_comp_count > 0 ORDER BY resource_comp_count DESC;

To find resources whose competences have not been refreshed recently:

SELECT resource_name, organization_name, resource_comp_count, last_updated FROM pa_rep_org_res_comp_v WHERE last_updated < SYSDATE - 365;

Because the view invokes PL/SQL functions per row, large extracts benefit from filtering by organization or resource before aggregation, and from running outside peak online hours.

View: PA_REP_ORG_RES_COMP_V 12.2.2
owner:APPS, object_type:VIEW, fnd_design_data:PA.PA_REP_ORG_RES_COMP_V, object_name:PA_REP_ORG_RES_COMP_V, status:VALID, product: PA - Projects , description: PA_REP_ORG_RES_COMP_V is a view that lists all resource competence details by organizations , implementation_dba_data: APPS.PA_REP_ORG_RES_COMP_V ,
View: PA_REP_ORG_RES_COMP_V 12.1.1
owner:APPS, object_type:VIEW, fnd_design_data:PA.PA_REP_ORG_RES_COMP_V, object_name:PA_REP_ORG_RES_COMP_V, status:VALID, product: PA - Projects , description: PA_REP_ORG_RES_COMP_V is a view that lists all resource competence details by organizations , implementation_dba_data: APPS.PA_REP_ORG_RES_COMP_V ,
View: PA_REP_PROJ_RES_COMP_V 12.2.2
owner:APPS, object_type:VIEW, fnd_design_data:PA.PA_REP_PROJ_RES_COMP_V, object_name:PA_REP_PROJ_RES_COMP_V, status:VALID, product: PA - Projects , description: PA_REP_ORG_RES_COMP_V is a view that lists all resource competence details by projects , implementation_dba_data: APPS.PA_REP_PROJ_RES_COMP_V ,
View: PA_REP_PROJ_RES_COMP_V 12.1.1
owner:APPS, object_type:VIEW, fnd_design_data:PA.PA_REP_PROJ_RES_COMP_V, object_name:PA_REP_PROJ_RES_COMP_V, status:VALID, product: PA - Projects , description: PA_REP_ORG_RES_COMP_V is a view that lists all resource competence details by projects , implementation_dba_data: APPS.PA_REP_PROJ_RES_COMP_V ,