Results for “change_reason”

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

Overview

PA_CANDIDATE_REVIEWS_V is an Oracle E-Business Suite (EBS) reporting view owned by the APPS schema and delivered as part of the PA (Projects) product family. As documented in the ETRM metadata for release 12.1.1 and 12.2.2, the view stores information about candidate reviews — the review records captured against project resource candidates before those candidates are confirmed onto a project assignment. Each row represents a single review action recorded in PA_CANDIDATE_REVIEWS, enriched with the reviewer's identity, the candidate status name, the change reason meaning, and a computed candidate score.

The view is primarily consumed by Oracle Project Resource Management and related staffing flows, where reviewers evaluate proposed candidates. Because it resolves personal identifiers into person names and derives resource identifiers and scores through PL/SQL utility functions, it is a convenient single source for reporting and integration rather than a stored table. The user search term reviewer_person_id refers directly to the column in this view that identifies the HR person record for the individual who performed the candidate review.

Underlying Base Objects

The view is defined over a set of base synonyms, views and packages, all resolving into the APPS schema. The documented referenced objects are PA_CANDIDATE_REVIEWS (the primary fact synonym), PA_CANDIDATES, PA_PROJECT_ASSIGNMENTS, PA_PROJECT_STATUSES, PA_RESOURCES_DENORM, PER_PEOPLE_X, PA_LOOKUPS, and the packages PA_CANDIDATE_UTILS, HR_GENERAL, HR_PERSON_NAME and HR_SECURITY.

Structurally, PA_CANDIDATE_REVIEWS is joined to PA_CANDIDATES on CANDIDATE_ID, which in turn links to PA_RESOURCES_DENORM on RESOURCE_ID and to PA_PROJECT_ASSIGNMENTS on ASSIGNMENT_ID. The assignment start date must fall within the resource's effective date range, and the resource must be schedulable (SCHEDULABLE_FLAG = 'Y'). PA_PROJECT_STATUSES and PA_LOOKUPS are outer-joined to translate status codes and change reason codes into user-facing names and meanings. PA_CANDIDATE_UTILS.GET_RESOURCE_ID converts the reviewer person identifier into a resource identifier, while PA_CANDIDATE_UTILS.GET_CANDIDATE_SCORE derives a numeric score. HR_GENERAL, HR_PERSON_NAME and HR_SECURITY support person-name resolution and security access.

Key Columns

• CANDIDATE_REVIEW_ID — Primary identifier for the candidate review record.
• CANDIDATE_ID — The candidate being reviewed, linking to PA_CANDIDATES.
• STATUS_CODE / STATUS_NAME — The candidate status code and its descriptive name from PA_PROJECT_STATUSES.
• REVIEWER_PERSON_ID — The PERSON_ID of the reviewer in PER_PEOPLE_X; the key search term for this view.
• REVIEWER_RESOURCE_ID — Resource identifier derived via PA_CANDIDATE_UTILS.GET_RESOURCE_ID(REVIEWER_PERSON_ID).
• REVIEWER_NAME — Reviewer's full name resolved through PER_PEOPLE_X / HR_PERSON_NAME.
• REVIEW_DATE — Date the review was performed.
• CHANGE_REASON_CODE / CHANGE_REASON — Status change reason code and its lookup meaning from PA_LOOKUPS.
• REVIEW_COMMENTS — Free-text reviewer comments.
• CANDIDATE_SCORE — Computed score returned by PA_CANDIDATE_UTILS.GET_CANDIDATE_SCORE.

Common Use Cases and Queries

Typical scenarios include auditing who reviewed which candidates, reporting candidate scores for staffing decisions, and integrating review outcomes into external HR or staffing systems. Because the score is recomputed at query time, the view should not be treated as a historical snapshot.

Sample query returning reviews by a specific reviewer:

• SELECT candidate_review_id, candidate_id, status_name, reviewer_person_id, reviewer_name, review_date, candidate_score FROM pa_candidate_reviews_v WHERE reviewer_person_id = :reviewer_person_id;

A broader reporting query aggregating reviews and average scores:

• SELECT reviewer_person_id, reviewer_name, COUNT(*) review_count, AVG(candidate_score) avg_score FROM pa_candidate_reviews_v GROUP BY reviewer_person_id, reviewer_name ORDER BY review_count DESC;

Joining to candidates for detail reporting:

• SELECT v.candidate_id, v.reviewer_name, v.review_date, v.change_reason, v.review_comments FROM pa_candidate_reviews_v v WHERE v.status_code = :status_code ORDER BY v.review_date DESC;

Note that data access is subject to HR security applied through the underlying PER_PEOPLE_X and HR_SECURITY objects. Operating units and person security profiles therefore constrain which reviewer records a given user can retrieve.