Results for “pa_candidate_reviews”

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

Overview

The PA_CANDIDATE_REVIEWS table, owned by the PA schema within the Oracle EBS Projects (PA) module, stores the assignment review log of candidates. In the context of Oracle EBS 12.1.1 and 12.2.2, this table functions as a transactional audit trail that records the outcome of candidate reviews performed against project assignments, capturing reviewer identity, review date, status decisions, and associated commentary. It supports the candidate screening and approval workflow that precedes formal assignment of resources to project roles.

From a data modeling perspective, the heuristic Data Vault classification for this object is satellite-leaning. This is a modeling suggestion rather than a physical EBS fact: the table carries a descriptive, versioned payload (status, reviewer, comments) keyed by a surrogate identifier, and it is anchored to the PA_CANDIDATES entity. Because the primary key is the surrogate CANDIDATE_REVIEW_ID itself, the table resembles a satellite whose grain is one review event per candidate. Designers building a Data Vault representation should treat PA_CANDIDATES as the hub and PA_CANDIDATE_REVIEWS as the descriptive satellite, with the review event date driving record versioning.

Key Information Stored

The documented physical schema for release 12.2.2 comprises 29 columns. The most consequential are summarized below; the surrogate key is distinguished from the business-key candidates identified in the unique index metadata.

• CANDIDATE_REVIEW_ID — Surrogate primary key, enforced by PA_CANDIDATE_REVIEWS_PK and also the sole column in the unique index PA_CANDIDATE_REVIEWS_U1. This is the identifier most frequently referenced by downstream objects.
• CANDIDATE_ID — Foreign key linking the review record to the candidate in PA_CANDIDATES. This is the principal business join column.
• RECORD_VERSION_NUMBER — Supports optimistic concurrency and version tracking of the review record.
• STATUS_CODE — The review decision or lifecycle state assigned during the review (for example, approved, rejected, or pending).
• REVIEWER_PERSON_ID — Identifies the person responsible for conducting the review.
• REVIEW_DATE — Effective date of the review event, central to any chronological audit report.
• CHANGE_REASON_CODE — Coded justification when a review alters a prior decision.
• REVIEW_COMMENTS — Free-text remarks captured by the reviewer.
• ATTRIBUTE_CATEGORY and ATTRIBUTE1 through ATTRIBUTE15 — The standard EBS descriptive flexfield (DFF) segment columns, enabling client-specific extensions without schema change.
• CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN — The mandatory WHO audit columns maintained by EBS.

Common Use Cases and Queries

Typical reporting scenarios include candidate review history, reviewer workload analysis, decision turnaround time, and audit evidence for assignment governance. A frequent query joins the review log to the candidate master:

• Review history by candidate: SELECT r.candidate_review_id, r.status_code, r.review_date, r.reviewer_person_id FROM pa.pa_candidate_reviews r WHERE r.candidate_id = :candidate_id ORDER BY r.review_date DESC;
• Latest review status per candidate: apply an analytic ranking of RECORD_VERSION_NUMBER or REVIEW_DATE over the partition by CANDIDATE_ID to return the most recent decision.
• Reviewer volume reporting: group by REVIEWER_PERSON_ID to measure throughput across a review period.
• Audit and compliance extraction: report CHANGE_REASON_CODE and REVIEW_COMMENTS alongside WHO columns for internal control testing.

Because the DFF columns are populated only where a flexfield is configured, reporting tools should treat ATTRIBUTE1–15 as optional and resolve ATTRIBUTE_CATEGORY before interpreting them.

Related Objects

• PA_CANDIDATES — The candidate master. Joined on PA_CANDIDATE_REVIEWS.CANDIDATE_ID = PA_CANDIDATES.CANDIDATE_ID; the metadata also documents a CANDIDATE_REVIEW_ID reference into PA_CANDIDATES.
• PA_CANDIDATES_REV_AR — An archive/revision table that references this table via PA_CANDIDATES_REV_AR.CANDIDATE_REVIEW_ID, used for historical retention.
• PA_CANDIDATE_REVIEWS_PK — The primary key constraint on CANDIDATE_REVIEW_ID, used by dependent foreign keys.
• PA_CANDIDATE_REVIEWS_U1 — The unique index on CANDIDATE_REVIEW_ID, serving as the business-key candidate for this object.

The table is normally accessed through the Projects resource management and candidate assignment application flows rather than through a public PL/SQL API, so integrations should read it directly or through the associated concurrent programs.