Oracle HCM Performance Management Tables Guide — Appraisals, Goals & Talent Review

-- Active appraisals by status for current period
WITH current_period AS (
    SELECT APPRAISAL_PERIOD_ID, PERIOD_NAME
    FROM HRA_APPRAISAL_PERIODS
    WHERE STATUS = 'OPEN'
      AND TRUNC(SYSDATE) BETWEEN START_DATE AND END_DATE
)
SELECT
    ap.PERIOD_NAME,
    ha.STATUS_CODE,
    COUNT(*) as APPRAISAL_COUNT,
    COUNT(DISTINCT ha.MANAGER_ID) as MANAGER_COUNT,
    ROUND(100.0 * COUNT(*) /
          SUM(COUNT(*)) OVER (PARTITION BY ap.PERIOD_NAME), 1) as PERCENTAGE
FROM HRA_APPRAISALS ha
JOIN current_period ap ON ha.APPRAISAL_PERIOD_ID = ap.APPRAISAL_PERIOD_ID
GROUP BY ap.PERIOD_NAME, ha.STATUS_CODE
ORDER BY ap.PERIOD_NAME,
         CASE ha.STATUS_CODE
           WHEN 'DRAFT' THEN 1
           WHEN 'IN_PROGRESS' THEN 2
           WHEN 'SUBMITTED' THEN 3
           WHEN 'APPROVED' THEN 4
           WHEN 'CLOSED' THEN 5
         END;

-- Performance rating distribution by department
SELECT
    org.NAME as DEPARTMENT,
    rm.RATING_LEVEL_DESCRIPTION,
    COUNT(*) as EMPLOYEE_COUNT,
    ROUND(100.0 * COUNT(*) /
          SUM(COUNT(*)) OVER (PARTITION BY org.NAME), 1) as DISTRIBUTION_PCT
FROM HRA_APPRAISALS ha
JOIN HRA_PERF_RATINGS hpr
  ON ha.APPRAISAL_ID = hpr.APPRAISAL_ID
  AND hpr.SECTION_ID IS NULL  -- Overall rating only
JOIN HRA_RATING_MODELS rm
  ON hpr.RATING_MODEL_ID = rm.RATING_MODEL_ID
  AND TRUNC(SYSDATE) BETWEEN rm.EFFECTIVE_START_DATE
                         AND rm.EFFECTIVE_END_DATE
JOIN PER_ALL_PEOPLE_F p
  ON ha.PERSON_ID = p.PERSON_ID
  AND TRUNC(SYSDATE) BETWEEN p.EFFECTIVE_START_DATE
                         AND p.EFFECTIVE_END_DATE
JOIN PER_ALL_ASSIGNMENTS_F a
  ON p.PERSON_ID = a.PERSON_ID
  AND TRUNC(SYSDATE) BETWEEN a.EFFECTIVE_START_DATE
                         AND a.EFFECTIVE_END_DATE
  AND a.PRIMARY_FLAG = 'Y'
JOIN HR_ALL_ORGANIZATION_UNITS org
  ON a.ORGANIZATION_ID = org.ORGANIZATION_ID
WHERE ha.STATUS_CODE = 'APPROVED'
  AND ha.APPRAISAL_PERIOD_ID IN (
      SELECT APPRAISAL_PERIOD_ID FROM HRA_APPRAISAL_PERIODS
      WHERE EXTRACT(YEAR FROM END_DATE) = EXTRACT(YEAR FROM SYSDATE)
  )
GROUP BY org.NAME, rm.RATING_LEVEL_DESCRIPTION
ORDER BY org.NAME, rm.NUMERIC_RATING;

-- Goal completion rate analysis
WITH goal_summary AS (
    SELECT
        hg.PERSON_ID,
        COUNT(*) as TOTAL_GOALS,
        SUM(CASE WHEN hg.STATUS_CODE = 'ACHIEVED' THEN 1 ELSE 0 END) as ACHIEVED_GOALS,
        ROUND(100.0 * SUM(CASE WHEN hg.STATUS_CODE = 'ACHIEVED' THEN 1 ELSE 0 END) /
              COUNT(*), 1) as ACHIEVEMENT_PCT
    FROM HRA_GOALS hg
    WHERE hg.START_DATE >= TRUNC(SYSDATE, 'YYYY')  -- Current year
    GROUP BY hg.PERSON_ID
)
SELECT
    org.NAME as DEPARTMENT,
    COUNT(*) as EMPLOYEE_COUNT,
    ROUND(AVG(gs.TOTAL_GOALS), 1) as AVG_GOALS_PER_EMPLOYEE,
    ROUND(AVG(gs.ACHIEVED_GOALS), 1) as AVG_ACHIEVED_GOALS,
    ROUND(AVG(gs.ACHIEVEMENT_PCT), 1) as AVG_ACHIEVEMENT_PCT,
    ROUND(PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY gs.ACHIEVEMENT_PCT), 1) as MEDIAN_ACHIEVEMENT_PCT
FROM goal_summary gs
JOIN PER_ALL_PEOPLE_F p
  ON gs.PERSON_ID = p.PERSON_ID
  AND TRUNC(SYSDATE) BETWEEN p.EFFECTIVE_START_DATE
                         AND p.EFFECTIVE_END_DATE
JOIN PER_ALL_ASSIGNMENTS_F a
  ON p.PERSON_ID = a.PERSON_ID
  AND TRUNC(SYSDATE) BETWEEN a.EFFECTIVE_START_DATE
                         AND a.EFFECTIVE_END_DATE
  AND a.PRIMARY_FLAG = 'Y'
JOIN HR_ALL_ORGANIZATION_UNITS org
  ON a.ORGANIZATION_ID = org.ORGANIZATION_ID
GROUP BY org.NAME
ORDER BY org.NAME;

-- Overdue appraisals not yet approved
SELECT
    p.PERSON_NUMBER,
    p.DISPLAY_NAME,
    ap.PERIOD_NAME,
    ap.APPROVAL_DUE_DATE,
    TRUNC(SYSDATE - ap.APPROVAL_DUE_DATE) as DAYS_OVERDUE,
    ha.STATUS_CODE,
    ha.SUBMITTED_DATE,
    m.DISPLAY_NAME as MANAGER_NAME
FROM HRA_APPRAISALS ha
JOIN HRA_APPRAISAL_PERIODS ap
  ON ha.APPRAISAL_PERIOD_ID = ap.APPRAISAL_PERIOD_ID
JOIN PER_ALL_PEOPLE_F p
  ON ha.PERSON_ID = p.PERSON_ID
  AND TRUNC(SYSDATE) BETWEEN p.EFFECTIVE_START_DATE
                         AND p.EFFECTIVE_END_DATE
LEFT JOIN PER_ALL_PEOPLE_F m
  ON ha.MANAGER_ID = m.PERSON_ID
WHERE ap.APPROVAL_DUE_DATE < TRUNC(SYSDATE)
  AND ha.STATUS_CODE NOT IN ('APPROVED', 'CLOSED')
  AND ap.STATUS = 'OPEN'
ORDER BY DAYS_OVERDUE DESC;

-- Manager vs self-rating gap analysis
WITH rating_comparison AS (
    SELECT
        ha.APPRAISAL_ID,
        ha.PERSON_ID,
        hpr.RATING_LEVEL_CODE,
        LEAD(hpr.RATING_LEVEL_CODE) OVER (
            PARTITION BY ha.APPRAISAL_ID ORDER BY hpr.RATING_DATE
        ) as SELF_RATING,
        rm.NUMERIC_RATING,
        LEAD(rm.NUMERIC_RATING) OVER (
            PARTITION BY ha.APPRAISAL_ID ORDER BY hpr.RATING_DATE
        ) as SELF_NUMERIC_RATING
    FROM HRA_APPRAISALS ha
    JOIN HRA_PERF_RATINGS hpr
      ON ha.APPRAISAL_ID = hpr.APPRAISAL_ID
      AND hpr.SECTION_ID IS NULL  -- Overall rating only
    JOIN HRA_RATING_MODELS rm
      ON hpr.RATING_MODEL_ID = rm.RATING_MODEL_ID
      AND TRUNC(hpr.RATING_DATE) BETWEEN rm.EFFECTIVE_START_DATE
                                    AND rm.EFFECTIVE_END_DATE
    WHERE ha.STATUS_CODE = 'APPROVED'
)
SELECT
    p.PERSON_NUMBER,
    p.DISPLAY_NAME,
    rc.RATING_LEVEL_CODE as MANAGER_RATING,
    rc.SELF_RATING,
    rc.NUMERIC_RATING as MANAGER_NUMERIC,
    rc.SELF_NUMERIC_RATING,
    (rc.SELF_NUMERIC_RATING - rc.NUMERIC_RATING) as RATING_GAP,
    CASE
        WHEN rc.SELF_NUMERIC_RATING > rc.NUMERIC_RATING THEN 'Self-Inflated'
        WHEN rc.SELF_NUMERIC_RATING < rc.NUMERIC_RATING THEN 'Self-Modest'
        ELSE 'Aligned'
    END as ALIGNMENT_TYPE
FROM rating_comparison rc
JOIN PER_ALL_PEOPLE_F p
  ON rc.PERSON_ID = p.PERSON_ID
  AND TRUNC(SYSDATE) BETWEEN p.EFFECTIVE_START_DATE
                         AND p.EFFECTIVE_END_DATE
WHERE rc.SELF_RATING IS NOT NULL
ORDER BY ABS(RATING_GAP) DESC;

-- 360 feedback participation and completion tracking
WITH feedback_status AS (
    SELECT
        ha.APPRAISAL_ID,
        ha.PERSON_ID,
        COUNT(*) as TOTAL_PARTICIPANTS,
        SUM(CASE WHEN hp.SUBMISSION_DATE IS NOT NULL THEN 1 ELSE 0 END) as COMPLETED_FEEDBACK,
        ROUND(100.0 * SUM(CASE WHEN hp.SUBMISSION_DATE IS NOT NULL THEN 1 ELSE 0 END) /
              COUNT(*), 1) as COMPLETION_PCT
    FROM HRA_APPRAISALS ha
    LEFT JOIN HRA_PARTICIPANTS hp
      ON ha.APPRAISAL_ID = hp.APPRAISAL_ID
    WHERE ha.STATUS_CODE IN ('IN_PROGRESS', 'SUBMITTED', 'APPROVED')
    GROUP BY ha.APPRAISAL_ID, ha.PERSON_ID
)
SELECT
    p.PERSON_NUMBER,
    p.DISPLAY_NAME,
    ap.PERIOD_NAME,
    fs.TOTAL_PARTICIPANTS,
    fs.COMPLETED_FEEDBACK,
    fs.COMPLETION_PCT,
    CASE
        WHEN fs.COMPLETION_PCT >= 100 THEN 'Complete'
        WHEN fs.COMPLETION_PCT >= 75 THEN 'On Track'
        WHEN fs.COMPLETION_PCT >= 50 THEN 'In Progress'
        ELSE 'Just Started'
    END as STATUS
FROM feedback_status fs
JOIN HRA_APPRAISALS ha
  ON fs.APPRAISAL_ID = ha.APPRAISAL_ID
JOIN HRA_APPRAISAL_PERIODS ap
  ON ha.APPRAISAL_PERIOD_ID = ap.APPRAISAL_PERIOD_ID
JOIN PER_ALL_PEOPLE_F p
  ON fs.PERSON_ID = p.PERSON_ID
  AND TRUNC(SYSDATE) BETWEEN p.EFFECTIVE_START_DATE
                         AND p.EFFECTIVE_END_DATE
WHERE fs.TOTAL_PARTICIPANTS > 0
ORDER BY p.DISPLAY_NAME;

-- Year-over-year performance rating trends
WITH annual_ratings AS (
    SELECT
        p.PERSON_ID,
        p.DISPLAY_NAME,
        EXTRACT(YEAR FROM ap.END_DATE) as REVIEW_YEAR,
        rm.RATING_LEVEL_DESCRIPTION,
        rm.NUMERIC_RATING,
        ROW_NUMBER() OVER (
            PARTITION BY p.PERSON_ID, EXTRACT(YEAR FROM ap.END_DATE)
            ORDER BY ha.APPROVED_DATE DESC
        ) as RN
    FROM HRA_APPRAISALS ha
    JOIN HRA_PERF_RATINGS hpr
      ON ha.APPRAISAL_ID = hpr.APPRAISAL_ID
      AND hpr.SECTION_ID IS NULL  -- Overall rating
    JOIN HRA_RATING_MODELS rm
      ON hpr.RATING_MODEL_ID = rm.RATING_MODEL_ID
    JOIN HRA_APPRAISAL_PERIODS ap
      ON ha.APPRAISAL_PERIOD_ID = ap.APPRAISAL_PERIOD_ID
    JOIN PER_ALL_PEOPLE_F p
      ON ha.PERSON_ID = p.PERSON_ID
    WHERE ha.STATUS_CODE = 'APPROVED'
      AND ap.PERIOD_TYPE = 'ANNUAL'
      AND EXTRACT(YEAR FROM ap.END_DATE) IN (EXTRACT(YEAR FROM SYSDATE) - 1,
                                             EXTRACT(YEAR FROM SYSDATE))
)
SELECT
    ar.PERSON_ID,
    ar.DISPLAY_NAME,
    MAX(CASE WHEN ar.REVIEW_YEAR = EXTRACT(YEAR FROM SYSDATE) - 1
             THEN ar.NUMERIC_RATING END) as PRIOR_YEAR_RATING,
    MAX(CASE WHEN ar.REVIEW_YEAR = EXTRACT(YEAR FROM SYSDATE) - 1
             THEN ar.RATING_LEVEL_DESCRIPTION END) as PRIOR_YEAR_DESCRIPTION,
    MAX(CASE WHEN ar.REVIEW_YEAR = EXTRACT(YEAR FROM SYSDATE)
             THEN ar.NUMERIC_RATING END) as CURRENT_YEAR_RATING,
    MAX(CASE WHEN ar.REVIEW_YEAR = EXTRACT(YEAR FROM SYSDATE)
             THEN ar.RATING_LEVEL_DESCRIPTION END) as CURRENT_YEAR_DESCRIPTION,
    MAX(CASE WHEN ar.REVIEW_YEAR = EXTRACT(YEAR FROM SYSDATE)
             THEN ar.NUMERIC_RATING END) -
    MAX(CASE WHEN ar.REVIEW_YEAR = EXTRACT(YEAR FROM SYSDATE) - 1
             THEN ar.NUMERIC_RATING END) as RATING_CHANGE
FROM annual_ratings ar
WHERE ar.RN = 1
GROUP BY ar.PERSON_ID, ar.DISPLAY_NAME
HAVING MAX(CASE WHEN ar.REVIEW_YEAR = EXTRACT(YEAR FROM SYSDATE) - 1
              THEN ar.NUMERIC_RATING END) IS NOT NULL
   AND MAX(CASE WHEN ar.REVIEW_YEAR = EXTRACT(YEAR FROM SYSDATE)
              THEN ar.NUMERIC_RATING END) IS NOT NULL
ORDER BY ABS(RATING_CHANGE) DESC;

-- Headcount by rating level for workforce calibration
SELECT
    org.NAME as DEPARTMENT,
    j.NAME as JOB_TITLE,
    rm.RATING_LEVEL_DESCRIPTION,
    COUNT(DISTINCT p.PERSON_ID) as HEADCOUNT,
    ROUND(100.0 * COUNT(DISTINCT p.PERSON_ID) /
          SUM(COUNT(DISTINCT p.PERSON_ID)) OVER (PARTITION BY org.NAME), 1) as DEPT_PERCENTAGE,
    ROUND(100.0 * COUNT(DISTINCT p.PERSON_ID) /
          SUM(COUNT(DISTINCT p.PERSON_ID)) OVER (PARTITION BY org.NAME, j.NAME), 1) as JOB_PERCENTAGE,
    ROUND(AVG(SALARY_COMPONENTS.SALARY_AMOUNT), 0) as AVG_SALARY,
    COUNT(DISTINCT CASE WHEN FLOOR(MONTHS_BETWEEN(TRUNC(SYSDATE), wr.START_DATE) / 12) < 1
                        THEN p.PERSON_ID END) as NEW_HIRES_UNDER_1YR
FROM HRA_APPRAISALS ha
JOIN HRA_PERF_RATINGS hpr
  ON ha.APPRAISAL_ID = hpr.APPRAISAL_ID
  AND hpr.SECTION_ID IS NULL
JOIN HRA_RATING_MODELS rm
  ON hpr.RATING_MODEL_ID = rm.RATING_MODEL_ID
JOIN PER_ALL_PEOPLE_F p
  ON ha.PERSON_ID = p.PERSON_ID
  AND TRUNC(SYSDATE) BETWEEN p.EFFECTIVE_START_DATE
                         AND p.EFFECTIVE_END_DATE
JOIN PER_ALL_ASSIGNMENTS_F a
  ON p.PERSON_ID = a.PERSON_ID
  AND TRUNC(SYSDATE) BETWEEN a.EFFECTIVE_START_DATE
                         AND a.EFFECTIVE_END_DATE
  AND a.PRIMARY_FLAG = 'Y'
LEFT JOIN PER_JOBS j
  ON a.JOB_ID = j.JOB_ID
LEFT JOIN HR_ALL_ORGANIZATION_UNITS org
  ON a.ORGANIZATION_ID = org.ORGANIZATION_ID
LEFT JOIN PER_WORK_RELATIONSHIPS wr
  ON p.PERSON_ID = wr.PERSON_ID
  AND wr.PRIMARY_FLAG = 'Y'
LEFT JOIN (
    SELECT PERSON_ID, SUM(SALARY_AMOUNT) as SALARY_AMOUNT
    FROM CMP_SALARY
    WHERE TRUNC(SYSDATE) BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE
    GROUP BY PERSON_ID
) SALARY_COMPONENTS
  ON p.PERSON_ID = SALARY_COMPONENTS.PERSON_ID
WHERE ha.STATUS_CODE = 'APPROVED'
  AND EXTRACT(YEAR FROM (SELECT MAX(END_DATE)
                          FROM HRA_APPRAISAL_PERIODS
                          WHERE APPRAISAL_PERIOD_ID = ha.APPRAISAL_PERIOD_ID))
      = EXTRACT(YEAR FROM SYSDATE)
GROUP BY org.NAME, j.NAME, rm.RATING_LEVEL_DESCRIPTION, rm.NUMERIC_RATING
ORDER BY org.NAME, j.NAME, rm.NUMERIC_RATING DESC;

AND TRUNC(SYSDATE) BETWEEN table_alias.EFFECTIVE_START_DATE
                         AND table_alias.EFFECTIVE_END_DATE

HRA_APPRAISALS → PER_ALL_PEOPLE_F → PER_ALL_ASSIGNMENTS_F → HR_ALL_ORGANIZATION_UNITS

JOIN HRA_RATING_MODELS rm
  ON hpr.RATING_MODEL_ID = rm.RATING_MODEL_ID
  AND TRUNC(hpr.RATING_DATE) BETWEEN rm.EFFECTIVE_START_DATE
                                AND rm.EFFECTIVE_END_DATE

WHERE hpr.SECTION_ID IS NULL  -- Only overall ratings