Results for “pa_candidates_v”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

PA_CANDIDATES_V is a Projects (PA) module view owned by the APPS schema in Oracle E-Business Suite 12.1.1 and 12.2.2. It presents consolidated information about candidate nominations, that is, resources who have been put forward for a specific project assignment and whose nomination is tracked through the candidate workflow. The view denormalizes the core nomination record in PA_CANDIDATES by joining it to resource, assignment, project, status, and organization data, so that a single query returns a recruiter-friendly, reporting-ready row per nomination.

Its principal role is reporting and integration. Rather than requiring report developers to reconstruct the nomination context from several base tables, the view exposes descriptive attributes such as the candidate resource name, the project name and number, assignment dates, candidate status, and the identity of the nominator. The column NOMINATED_BY is of particular interest because it resolves the nominator's person identifier into a display name through a utility function call, which makes the view directly consumable by Oracle Reports, BI Publisher, OBIEE, and custom PL/SQL that must surface "who nominated this candidate."

Underlying Base Objects

The view is defined over seven documented base objects, joined in a single SELECT with an outer join to organization units:

• PA_CANDIDATES (alias CAND) — the driving table holding candidate, nomination, ranking, and status data.
• PA_RESOURCES_DENORM (alias RES) — supplies resource name, person, job level, organization, and schedulable flag; also the effective-dating source for the assignment join.
• PA_PROJECT_ASSIGNMENTS (alias ASGMT) — the assignment being staffed, providing assignment name, project, and start/end dates.
• PA_PROJECTS_ALL (alias PROJ) — project name and segment1 (project number).
• PA_PROJECT_STATUSES (alias PS) — candidate status name and system status, restricted to STATUS_TYPE = 'CANDIDATE'.
• HR_ALL_ORGANIZATION_UNITS (alias ORG) — organization name, outer-joined on resource organization.
• PA_CANDIDATE_UTILS and PA_RESOURCE_UTILS — PL/SQL packages invoked within the view to derive nominated-by resource id and nominator name.

The assignment join is effective-dated: the assignment start date must fall between the resource effective start date and the resource effective end date (defaulted to assignment start plus one day). Only resources with SCHEDULABLE_FLAG = 'Y' are eligible.

Key Columns

• CANDIDATE_NUMBER / CANDIDATE_NAME — identifier and name of the nominated resource.
• CANDIDATE_RESOURCE_ID / CANDIDATE_PERSON_ID — resource and HR person keys for the candidate.
• PROJECT_ID / PROJECT_NAME / PROJECT_NUMBER — the project the nomination relates to.
• ASSIGNMENT_ID / ASSIGNMENT_NAME / ASSIGNMENT_START_DATE / ASSIGNMENT_END_DATE — the specific assignment under consideration.
• JOB_LEVEL, ORGANIZATION_NAME, ORGANIZATION_ID — resource job level and owning organization.
• STATUS_CODE / STATUS_NAME / SYSTEM_STATUS_CODE — candidate status drawn from PA_PROJECT_STATUSES.
• NOMINATED_BY_PERSON_ID / NOMINATED_BY_RESOURCE_ID / NOMINATED_BY — the nominator, with NOMINATED_BY resolved to a display name via PA_CANDIDATE_UTILS.GET_NOMINATOR_NAME.
• NOMINATION_DATE / NOMINATION_COMMENTS / CANDIDATE_RANKING — nomination context and ranking.
• RECORD_VERSION_NUMBER — optimistic locking version.

Common Use Cases and Queries

Typical uses include staffing reports listing open candidates per project, workload analysis by nominator, and integrations that pull candidate nominations into external talent systems.

Nominations for a given project:

SELECT candidate_name, assignment_name, status_name, nominated_by, nomination_date
FROM   apps.pa_candidates_v
WHERE  project_number = :project_number
ORDER BY candidate_ranking;

Nominations made by a specific person (the "nominated_by" search):

SELECT project_number, candidate_name, assignment_name, nomination_date
FROM   apps.pa_candidates_v
WHERE  nominated_by_person_id = :person_id
ORDER BY nomination_date DESC;

Candidate counts by status for a project:

SELECT status_name, COUNT(*) candidate_count
FROM   apps.pa_candidates_v
WHERE  project_id = :project_id
GROUP BY status_name;