Results for “review_comments”

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

Overview

PA_CANDIDATE_LOG_AMG_V is an APPS-owned database view within the Oracle E-Business Suite Projects (PA) module. Its documented purpose is to present all candidate reviews, providing a denormalized, read-only projection that consolidates candidate assignment records with their associated review activity. The view is marked VALID in the ETRM metadata for release 12.2.2 and is equally relevant to 12.1.1, since the underlying candidate and candidate review tables retained the same core design across both releases.

In practice, the view functions as a reporting and integration surface. Rather than forcing developers and report authors to join staffing candidates, resource name resolution, project status lookups, and candidate reviews manually, the view encapsulates those joins and exposes a single flat recordset. This makes it suitable for concurrent program data sources, Oracle Reports or BI Publisher datasets, OAF-based inquiry pages, and ad hoc SQL used by functional consultants reviewing the staffing pipeline.

Underlying Base Objects

The view is defined over four APPS synonyms that resolve to base PA tables. The primary driving table is PA_CANDIDATES (aliased CAND), which holds candidate assignments to requirements. It is joined to PA_CANDIDATE_REVIEWS (aliased PCR) on CANDIDATE_ID, supplying the review transaction context. Resource details are obtained through PA_RESOURCES_DENORM (aliased RES), joined on RESOURCE_ID, with a correlated subquery restricting the resource row to the maximum RESOURCE_EFFECTIVE_START_DATE for each resource, thereby returning only the most current effective-dated resource record. Status decoding is provided by PA_PROJECT_STATUSES (aliased PS), joined on STATUS_CODE with the additional predicate STATUS_TYPE = 'CANDIDATE', which ensures the status description originates from the candidate status set rather than a project or other status set. The join conditions therefore establish a candidate-to-review grain, enriched with current resource naming and controlled status descriptions.

Key Columns

• CANDIDATE_REVIEW_ID — Primary identifier of the candidate review record from PA_CANDIDATE_REVIEWS; unique at the review grain.
• CANDIDATE_ID — Identifier of the candidate record in PA_CANDIDATES, linking the review back to the staffing candidate.
• CANDIDATE_NAME — Exposed on the view and intended to carry the candidate resource name, populated from the resource denormalization join.
• REQUIREMENT_ID — Identifier of the requirement against which the candidate is being considered; this is the column most frequently used to filter the view back to a specific staffing requirement.
• STATUS_CODE / STATUS_NAME — The candidate status code and its decoded project status name, with the decode constrained to the CANDIDATE status type.
• SYSTEM_STATUS_CODE — The underlying system status associated with the candidate status, useful where seeded system statuses drive processing logic.
• CHANGE_REASON_CODE — Reason code recorded on the review, indicating why the review action or status change occurred.
• REVIEW_COMMENTS — Free-text comments captured by the reviewer during the candidate review.

Common Use Cases and Queries

A frequent requirement is to list all reviews associated with a single staffing requirement, which the user's search for "requirement_id" suggests. A representative query is:

SELECT candidate_review_id, candidate_id, candidate_name, requirement_id, status_code, status_name, change_reason_code, review_comments FROM apps.pa_candidate_log_amg_v WHERE requirement_id = :p_requirement_id;

Other practical scenarios include producing a staffing pipeline report of all reviews grouped by status, auditing change reasons for rejected or withdrawn candidates, and extracting review comments for management review. Because the view restricts resource rows to the latest effective-dated entry, it reports current resource names rather than historical ones, and consumers requiring historical naming must query PA_RESOURCES_DENORM directly with explicit effective dates. Query performance benefits from predicates on REQUIREMENT_ID and CANDIDATE_ID, since these reduce the candidate and review sets before the resource denormalization and status lookup joins are applied.