Oracle Workforce Compensation Tables Complete Guide

Oracle Fusion HCM's Workforce Compensation module provides comprehensive salary management, merit planning, and equity compensation capabilities. Understanding the underlying database structure is essential for compensation analysts, HR professionals, and developers who need to create custom reports, analyze pay equity, or integrate with external compensation systems.

This guide covers the core compensation tables, their relationships, and practical SQL examples for common compensation scenarios. From basic salary reporting to complex merit increase calculations and equity grant tracking, you'll learn how to leverage Oracle's compensation data model effectively.

Compensation Data Model Overview

Oracle's Workforce Compensation follows a flexible data model that supports various compensation components including base salary, variable pay, bonuses, stock options, and benefits. The model accommodates complex compensation structures while maintaining historical accuracy and audit trails.

Compensation Plans

Plan structure

→

Eligibility

Employee access

→

Components

Salary elements

→

Awards

Individual grants

→

Payments

Payroll impact

This architecture supports diverse compensation strategies while ensuring compliance with local regulations and providing the flexibility needed for global organizations.

Core Compensation Tables

CMP_SALARY - Current Salary Information

The CMP_SALARY table stores current base salary information for employees. This is the primary table for salary-related reporting and analysis.

Key Columns	Description	Usage
SALARY_ID	Unique salary record identifier	Primary key
PERSON_ID	Employee identifier	Links to PER_ALL_PEOPLE_F
ASSIGNMENT_ID	Assignment context	Links to PER_ALL_ASSIGNMENTS_F
SALARY_BASIS_ID	Salary frequency/basis	Links to PER_SALARY_BASIS
SALARY_AMOUNT	Base salary amount	Primary compensation value
CURRENCY_CODE	Salary currency	Multi-currency support
EFFECTIVE_START_DATE	When salary became effective	Date-effective tracking
EFFECTIVE_END_DATE	When salary expires/changed	Date-effective tracking
CHANGE_REASON	Reason for salary change	Audit trail and reporting
MULTIPLE	Multiplier for salary basis	Calculation factor

Sample Query: Current Salary Summary

-- Current salary information with employee details
SELECT 
    p.PERSON_NUMBER,
    p.DISPLAY_NAME,
    a.ASSIGNMENT_NUMBER,
    s.SALARY_AMOUNT,
    s.CURRENCY_CODE,
    sb.NAME as SALARY_BASIS,
    s.EFFECTIVE_START_DATE,
    s.CHANGE_REASON,
    org.NAME as DEPARTMENT,
    j.NAME as JOB_TITLE
FROM CMP_SALARY s
JOIN PER_ALL_PEOPLE_F p 
  ON s.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 s.ASSIGNMENT_ID = a.ASSIGNMENT_ID
  AND TRUNC(SYSDATE) BETWEEN a.EFFECTIVE_START_DATE 
                          AND a.EFFECTIVE_END_DATE
LEFT JOIN PER_SALARY_BASIS sb 
  ON s.SALARY_BASIS_ID = sb.SALARY_BASIS_ID
LEFT JOIN HR_ALL_ORGANIZATION_UNITS org 
  ON a.ORGANIZATION_ID = org.ORGANIZATION_ID
LEFT JOIN PER_JOBS j 
  ON a.JOB_ID = j.JOB_ID
  AND TRUNC(SYSDATE) BETWEEN j.EFFECTIVE_START_DATE 
                          AND j.EFFECTIVE_END_DATE
WHERE TRUNC(SYSDATE) BETWEEN s.EFFECTIVE_START_DATE 
                          AND s.EFFECTIVE_END_DATE
  AND p.CURRENT_EMPLOYEE_FLAG = 'Y'
ORDER BY p.PERSON_NUMBER;

CMP_COMPENSATION_PLANS - Plan Configuration

This table defines compensation plans including eligibility rules, budget allocations, and plan parameters used for merit increases, bonuses, and other compensation activities.

Key Columns	Description	Usage
PLAN_ID	Unique plan identifier	Primary key
PLAN_NAME	Compensation plan name	User-friendly identification
PLAN_TYPE_CODE	Type of compensation plan	MERIT, BONUS, EQUITY, etc.
PLAN_PERIOD_START	Plan effective start date	Planning cycle definition
PLAN_PERIOD_END	Plan effective end date	Planning cycle definition
BUDGET_AMOUNT	Total plan budget	Budget management
BUDGET_CURRENCY	Budget currency	Multi-currency support
STATUS	Plan status	ACTIVE, CLOSED, DRAFT
AUTO_APPROVE_FLAG	Automatic approval setting	Workflow configuration
PRORATING_ENABLED	Allow prorated awards	Partial period handling

Sample Query: Active Compensation Plans

-- Active compensation plans with budget utilization
WITH plan_usage AS (
    SELECT 
        ca.PLAN_ID,
        COUNT(*) as TOTAL_AWARDS,
        SUM(ca.AWARD_AMOUNT) as TOTAL_AWARDED,
        AVG(ca.AWARD_AMOUNT) as AVERAGE_AWARD
    FROM CMP_COMPENSATION_AWARDS ca
    WHERE ca.STATUS IN ('APPROVED', 'PROCESSED')
    GROUP BY ca.PLAN_ID
)
SELECT 
    cp.PLAN_NAME,
    cp.PLAN_TYPE_CODE,
    cp.PLAN_PERIOD_START,
    cp.PLAN_PERIOD_END,
    cp.BUDGET_AMOUNT,
    cp.BUDGET_CURRENCY,
    NVL(pu.TOTAL_AWARDED, 0) as AMOUNT_AWARDED,
    cp.BUDGET_AMOUNT - NVL(pu.TOTAL_AWARDED, 0) as REMAINING_BUDGET,
    ROUND((NVL(pu.TOTAL_AWARDED, 0) / cp.BUDGET_AMOUNT) * 100, 2) as BUDGET_UTILIZED_PCT,
    NVL(pu.TOTAL_AWARDS, 0) as NUMBER_OF_AWARDS,
    ROUND(NVL(pu.AVERAGE_AWARD, 0), 2) as AVERAGE_AWARD_AMOUNT
FROM CMP_COMPENSATION_PLANS cp
LEFT JOIN plan_usage pu ON cp.PLAN_ID = pu.PLAN_ID
WHERE cp.STATUS = 'ACTIVE'
  AND TRUNC(SYSDATE) BETWEEN cp.PLAN_PERIOD_START 
                          AND cp.PLAN_PERIOD_END
ORDER BY cp.PLAN_TYPE_CODE, cp.PLAN_NAME;

CMP_COMPENSATION_AWARDS - Individual Awards

This table stores individual compensation awards including merit increases, bonuses, stock options, and other variable compensation components granted to employees.

Key Columns	Description	Usage
AWARD_ID	Unique award identifier	Primary key
PERSON_ID	Award recipient	Links to PER_ALL_PEOPLE_F
PLAN_ID	Compensation plan	Foreign key to CMP_COMPENSATION_PLANS
COMPONENT_ID	Compensation component	Links to CMP_SALARY_COMPONENTS
AWARD_AMOUNT	Monetary value of award	Compensation value
AWARD_CURRENCY	Award currency	Multi-currency support
AWARD_DATE	When award was granted	Grant date tracking
EFFECTIVE_DATE	When award becomes effective	Implementation timing
STATUS	Award status	PENDING, APPROVED, PROCESSED
PERCENTAGE_INCREASE	Percentage increase (for merits)	Increase calculation
JUSTIFICATION	Award rationale	Documentation
MANAGER_ID	Recommending manager	Links to PER_ALL_PEOPLE_F

Sample Query: Merit Increase Analysis

-- Merit increase analysis by department and performance rating
WITH performance_data AS (
    SELECT 
        ca.PERSON_ID,
        ca.AWARD_AMOUNT,
        ca.PERCENTAGE_INCREASE,
        -- Assuming performance rating is stored in a related table
        -- or captured in justification/comments
        CASE 
            WHEN UPPER(ca.JUSTIFICATION) LIKE '%EXCEED%' THEN 'Exceeds Expectations'
            WHEN UPPER(ca.JUSTIFICATION) LIKE '%MEETS%' THEN 'Meets Expectations'
            WHEN UPPER(ca.JUSTIFICATION) LIKE '%BELOW%' THEN 'Below Expectations'
            ELSE 'Not Rated'
        END as PERFORMANCE_RATING
    FROM CMP_COMPENSATION_AWARDS ca
    JOIN CMP_COMPENSATION_PLANS cp ON ca.PLAN_ID = cp.PLAN_ID
    WHERE cp.PLAN_TYPE_CODE = 'MERIT'
      AND ca.STATUS = 'APPROVED'
      AND ca.EFFECTIVE_DATE >= TRUNC(SYSDATE, 'YYYY')  -- Current year
)
SELECT 
    org.NAME as DEPARTMENT,
    pd.PERFORMANCE_RATING,
    COUNT(*) as EMPLOYEE_COUNT,
    ROUND(AVG(pd.PERCENTAGE_INCREASE), 2) as AVG_INCREASE_PCT,
    ROUND(MIN(pd.PERCENTAGE_INCREASE), 2) as MIN_INCREASE_PCT,
    ROUND(MAX(pd.PERCENTAGE_INCREASE), 2) as MAX_INCREASE_PCT,
    ROUND(AVG(pd.AWARD_AMOUNT), 0) as AVG_INCREASE_AMOUNT
FROM performance_data pd
JOIN PER_ALL_PEOPLE_F p 
  ON pd.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 p.CURRENT_EMPLOYEE_FLAG = 'Y'
GROUP BY org.NAME, pd.PERFORMANCE_RATING
ORDER BY org.NAME, pd.PERFORMANCE_RATING;

CMP_SALARY_COMPONENTS - Compensation Components

This table defines the various compensation components such as base salary, bonuses, allowances, and benefits that can be included in compensation plans.

Key Columns	Description	Usage
COMPONENT_ID	Unique component identifier	Primary key
COMPONENT_NAME	Component name	User-friendly identification
COMPONENT_TYPE	Type classification	BASE_SALARY, BONUS, ALLOWANCE, etc.
CALCULATION_METHOD	How component is calculated	FIXED, PERCENTAGE, FORMULA
PAYROLL_ELEMENT_TYPE_ID	Links to payroll elements	Payroll integration
TAXABLE_FLAG	Subject to taxation	Tax reporting
CONTRIBUTION_FLAG	Counts toward benefit calculations	Benefits integration
PRORATION_GROUP	Prorating behavior group	Partial period handling

Equity and Long-Term Incentives

CMP_STOCK_PLANS - Stock Compensation Plans

For organizations offering equity compensation, this table manages stock option plans, restricted stock units (RSUs), and other equity-based awards.

Key Columns	Description	Usage
STOCK_PLAN_ID	Unique stock plan identifier	Primary key
PLAN_NAME	Stock plan name	Plan identification
STOCK_PLAN_TYPE	Type of equity plan	ISO, NQO, RSU, ESPP
GRANT_FREQUENCY	How often grants are made	ANNUAL, QUARTERLY, AD_HOC
VESTING_SCHEDULE_ID	Default vesting schedule	Links to vesting configuration
EXERCISE_PRICE_METHOD	How exercise price is determined	MARKET_PRICE, FIXED, FORMULA
TAX_TREATMENT_CODE	Tax classification	Tax reporting requirements

CMP_STOCK_GRANTS - Individual Equity Awards

This table tracks individual equity grants including options, RSUs, and other stock-based compensation awards.

Key Columns	Description	Usage
GRANT_ID	Unique grant identifier	Primary key
PERSON_ID	Grant recipient	Links to PER_ALL_PEOPLE_F
STOCK_PLAN_ID	Equity plan	Foreign key to CMP_STOCK_PLANS
GRANT_DATE	When grant was awarded	Grant date tracking
SHARES_GRANTED	Number of shares/options	Grant quantity
EXERCISE_PRICE	Strike price (for options)	Option exercise calculation
FAIR_VALUE	Fair value at grant	Accounting valuation
VESTING_START_DATE	When vesting begins	Vesting calculation
EXPIRATION_DATE	When grant expires	Expiration tracking
STATUS	Grant status	ACTIVE, EXERCISED, FORFEITED

Sample Query: Stock Grant Portfolio

-- Employee stock grant portfolio with vesting status
WITH vesting_calc AS (
    SELECT 
        sg.GRANT_ID,
        sg.PERSON_ID,
        sg.SHARES_GRANTED,
        sg.EXERCISE_PRICE,
        sg.FAIR_VALUE,
        sg.GRANT_DATE,
        sg.VESTING_START_DATE,
        sg.EXPIRATION_DATE,
        sp.PLAN_NAME,
        sp.STOCK_PLAN_TYPE,
        -- Simplified vesting calculation (assume 4-year monthly vesting)
        CASE 
            WHEN TRUNC(SYSDATE) < sg.VESTING_START_DATE THEN 0
            WHEN MONTHS_BETWEEN(TRUNC(SYSDATE), sg.VESTING_START_DATE) >= 48 
            THEN sg.SHARES_GRANTED
            ELSE FLOOR(sg.SHARES_GRANTED * 
                      (MONTHS_BETWEEN(TRUNC(SYSDATE), sg.VESTING_START_DATE) / 48))
        END as VESTED_SHARES,
        sg.SHARES_GRANTED - 
        CASE 
            WHEN TRUNC(SYSDATE) < sg.VESTING_START_DATE THEN 0
            WHEN MONTHS_BETWEEN(TRUNC(SYSDATE), sg.VESTING_START_DATE) >= 48 
            THEN sg.SHARES_GRANTED
            ELSE FLOOR(sg.SHARES_GRANTED * 
                      (MONTHS_BETWEEN(TRUNC(SYSDATE), sg.VESTING_START_DATE) / 48))
        END as UNVESTED_SHARES
    FROM CMP_STOCK_GRANTS sg
    JOIN CMP_STOCK_PLANS sp ON sg.STOCK_PLAN_ID = sp.STOCK_PLAN_ID
    WHERE sg.STATUS = 'ACTIVE'
)
SELECT 
    p.PERSON_NUMBER,
    p.DISPLAY_NAME,
    vc.PLAN_NAME,
    vc.STOCK_PLAN_TYPE,
    vc.GRANT_DATE,
    vc.SHARES_GRANTED as TOTAL_SHARES,
    vc.VESTED_SHARES,
    vc.UNVESTED_SHARES,
    vc.EXERCISE_PRICE,
    vc.FAIR_VALUE as GRANT_VALUE,
    vc.EXPIRATION_DATE,
    ROUND((vc.VESTED_SHARES / vc.SHARES_GRANTED) * 100, 2) as VESTING_PCT_COMPLETE
FROM vesting_calc vc
JOIN PER_ALL_PEOPLE_F p 
  ON vc.PERSON_ID = p.PERSON_ID
  AND TRUNC(SYSDATE) BETWEEN p.EFFECTIVE_START_DATE 
                          AND p.EFFECTIVE_END_DATE
WHERE p.CURRENT_EMPLOYEE_FLAG = 'Y'
ORDER BY p.PERSON_NUMBER, vc.GRANT_DATE;

Compensation Analytics and Reporting

Pay Equity Analysis

One of the most critical applications of compensation data is pay equity analysis:

-- Pay equity analysis by job, gender, and tenure
WITH compensation_summary AS (
    SELECT 
        p.PERSON_ID,
        p.DISPLAY_NAME,
        p.GENDER,
        j.JOB_CODE,
        j.NAME as JOB_TITLE,
        org.NAME as DEPARTMENT,
        s.SALARY_AMOUNT,
        s.CURRENCY_CODE,
        wr.START_DATE as HIRE_DATE,
        FLOOR(MONTHS_BETWEEN(TRUNC(SYSDATE), wr.START_DATE) / 12) as TENURE_YEARS,
        -- Normalize to annual salary
        CASE 
            WHEN sb.NAME = 'Monthly' THEN s.SALARY_AMOUNT * 12
            WHEN sb.NAME = 'Weekly' THEN s.SALARY_AMOUNT * 52
            WHEN sb.NAME = 'Hourly' THEN s.SALARY_AMOUNT * 2080  -- 40hrs * 52 weeks
            ELSE s.SALARY_AMOUNT  -- Assume annual
        END as ANNUAL_SALARY
    FROM PER_ALL_PEOPLE_F p
    JOIN CMP_SALARY s 
      ON p.PERSON_ID = s.PERSON_ID
      AND TRUNC(SYSDATE) BETWEEN s.EFFECTIVE_START_DATE 
                              AND s.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 PER_JOBS j 
      ON a.JOB_ID = j.JOB_ID
      AND TRUNC(SYSDATE) BETWEEN j.EFFECTIVE_START_DATE 
                              AND j.EFFECTIVE_END_DATE
    JOIN HR_ALL_ORGANIZATION_UNITS org 
      ON a.ORGANIZATION_ID = org.ORGANIZATION_ID
    JOIN PER_WORK_RELATIONSHIPS wr 
      ON p.PERSON_ID = wr.PERSON_ID
      AND wr.PRIMARY_FLAG = 'Y'
    LEFT JOIN PER_SALARY_BASIS sb 
      ON s.SALARY_BASIS_ID = sb.SALARY_BASIS_ID
    WHERE p.CURRENT_EMPLOYEE_FLAG = 'Y'
      AND TRUNC(SYSDATE) BETWEEN p.EFFECTIVE_START_DATE 
                              AND p.EFFECTIVE_END_DATE
      AND s.CURRENCY_CODE = 'USD'  -- Filter to single currency
)
SELECT 
    JOB_CODE,
    JOB_TITLE,
    DEPARTMENT,
    GENDER,
    COUNT(*) as EMPLOYEE_COUNT,
    ROUND(AVG(ANNUAL_SALARY), 0) as AVG_SALARY,
    ROUND(MIN(ANNUAL_SALARY), 0) as MIN_SALARY,
    ROUND(MAX(ANNUAL_SALARY), 0) as MAX_SALARY,
    ROUND(STDDEV(ANNUAL_SALARY), 0) as SALARY_STDDEV,
    ROUND(AVG(TENURE_YEARS), 1) as AVG_TENURE_YEARS
FROM compensation_summary
WHERE ANNUAL_SALARY > 0
GROUP BY JOB_CODE, JOB_TITLE, DEPARTMENT, GENDER
HAVING COUNT(*) >= 3  -- Minimum sample size for privacy
ORDER BY JOB_CODE, DEPARTMENT, GENDER;

Salary Range Compliance

Monitoring salary ranges and ensuring employees are positioned appropriately within established bands:

-- Salary range compliance analysis
WITH grade_ranges AS (
    -- This assumes grade range tables exist
    -- Structure may vary based on implementation
    SELECT 
        gr.GRADE_ID,
        gr.CURRENCY_CODE,
        gr.MIN_SALARY,
        gr.MID_SALARY,
        gr.MAX_SALARY
    FROM PER_GRADE_RANGES gr
    WHERE gr.CURRENCY_CODE = 'USD'
      AND TRUNC(SYSDATE) BETWEEN gr.EFFECTIVE_START_DATE 
                              AND gr.EFFECTIVE_END_DATE
)
SELECT 
    p.PERSON_NUMBER,
    p.DISPLAY_NAME,
    j.NAME as JOB_TITLE,
    g.NAME as GRADE,
    s.SALARY_AMOUNT,
    gr.MIN_SALARY as RANGE_MIN,
    gr.MID_SALARY as RANGE_MID,
    gr.MAX_SALARY as RANGE_MAX,
    ROUND(((s.SALARY_AMOUNT - gr.MIN_SALARY) / 
           (gr.MAX_SALARY - gr.MIN_SALARY)) * 100, 1) as POSITION_IN_RANGE_PCT,
    CASE 
        WHEN s.SALARY_AMOUNT < gr.MIN_SALARY THEN 'Below Range'
        WHEN s.SALARY_AMOUNT > gr.MAX_SALARY THEN 'Above Range'
        WHEN s.SALARY_AMOUNT BETWEEN gr.MIN_SALARY AND gr.MID_SALARY THEN 'Lower Half'
        ELSE 'Upper Half'
    END as RANGE_POSITION
FROM CMP_SALARY s
JOIN PER_ALL_PEOPLE_F p 
  ON s.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 s.ASSIGNMENT_ID = a.ASSIGNMENT_ID
  AND TRUNC(SYSDATE) BETWEEN a.EFFECTIVE_START_DATE 
                          AND a.EFFECTIVE_END_DATE
JOIN PER_JOBS j 
  ON a.JOB_ID = j.JOB_ID
  AND TRUNC(SYSDATE) BETWEEN j.EFFECTIVE_START_DATE 
                          AND j.EFFECTIVE_END_DATE
JOIN PER_GRADES g 
  ON a.GRADE_ID = g.GRADE_ID
  AND TRUNC(SYSDATE) BETWEEN g.EFFECTIVE_START_DATE 
                          AND g.EFFECTIVE_END_DATE
LEFT JOIN grade_ranges gr 
  ON a.GRADE_ID = gr.GRADE_ID
  AND s.CURRENCY_CODE = gr.CURRENCY_CODE
WHERE p.CURRENT_EMPLOYEE_FLAG = 'Y'
  AND TRUNC(SYSDATE) BETWEEN s.EFFECTIVE_START_DATE 
                          AND s.EFFECTIVE_END_DATE
ORDER BY RANGE_POSITION DESC, POSITION_IN_RANGE_PCT;

Total Compensation Analysis

Calculating total compensation including base salary, bonuses, and equity value:

-- Total compensation analysis including all components
WITH base_compensation AS (
    SELECT 
        s.PERSON_ID,
        s.ASSIGNMENT_ID,
        s.SALARY_AMOUNT as BASE_SALARY,
        s.CURRENCY_CODE
    FROM CMP_SALARY s
    WHERE TRUNC(SYSDATE) BETWEEN s.EFFECTIVE_START_DATE 
                              AND s.EFFECTIVE_END_DATE
),
bonus_awards AS (
    SELECT 
        ca.PERSON_ID,
        SUM(ca.AWARD_AMOUNT) as ANNUAL_BONUS
    FROM CMP_COMPENSATION_AWARDS ca
    JOIN CMP_COMPENSATION_PLANS cp ON ca.PLAN_ID = cp.PLAN_ID
    WHERE cp.PLAN_TYPE_CODE = 'BONUS'
      AND ca.STATUS = 'APPROVED'
      AND ca.EFFECTIVE_DATE >= TRUNC(SYSDATE, 'YYYY')  -- Current year
      AND ca.EFFECTIVE_DATE < ADD_MONTHS(TRUNC(SYSDATE, 'YYYY'), 12)
    GROUP BY ca.PERSON_ID
),
equity_value AS (
    SELECT 
        sg.PERSON_ID,
        SUM(sg.FAIR_VALUE) as ANNUAL_EQUITY_VALUE
    FROM CMP_STOCK_GRANTS sg
    WHERE sg.GRANT_DATE >= TRUNC(SYSDATE, 'YYYY')  -- Current year grants
      AND sg.GRANT_DATE < ADD_MONTHS(TRUNC(SYSDATE, 'YYYY'), 12)
      AND sg.STATUS = 'ACTIVE'
    GROUP BY sg.PERSON_ID
)
SELECT 
    p.PERSON_NUMBER,
    p.DISPLAY_NAME,
    j.NAME as JOB_TITLE,
    org.NAME as DEPARTMENT,
    bc.BASE_SALARY,
    NVL(ba.ANNUAL_BONUS, 0) as ANNUAL_BONUS,
    NVL(ev.ANNUAL_EQUITY_VALUE, 0) as ANNUAL_EQUITY_VALUE,
    bc.BASE_SALARY + 
    NVL(ba.ANNUAL_BONUS, 0) + 
    NVL(ev.ANNUAL_EQUITY_VALUE, 0) as TOTAL_COMPENSATION,
    ROUND((NVL(ba.ANNUAL_BONUS, 0) / bc.BASE_SALARY) * 100, 1) as BONUS_AS_PCT_BASE,
    ROUND((NVL(ev.ANNUAL_EQUITY_VALUE, 0) / bc.BASE_SALARY) * 100, 1) as EQUITY_AS_PCT_BASE
FROM base_compensation bc
JOIN PER_ALL_PEOPLE_F p 
  ON bc.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 bc.ASSIGNMENT_ID = a.ASSIGNMENT_ID
  AND TRUNC(SYSDATE) BETWEEN a.EFFECTIVE_START_DATE 
                          AND a.EFFECTIVE_END_DATE
JOIN PER_JOBS j 
  ON a.JOB_ID = j.JOB_ID
  AND TRUNC(SYSDATE) BETWEEN j.EFFECTIVE_START_DATE 
                          AND j.EFFECTIVE_END_DATE
JOIN HR_ALL_ORGANIZATION_UNITS org 
  ON a.ORGANIZATION_ID = org.ORGANIZATION_ID
LEFT JOIN bonus_awards ba ON bc.PERSON_ID = ba.PERSON_ID
LEFT JOIN equity_value ev ON bc.PERSON_ID = ev.PERSON_ID
WHERE p.CURRENT_EMPLOYEE_FLAG = 'Y'
ORDER BY TOTAL_COMPENSATION DESC;

Advanced Compensation Scenarios

Multi-Currency Compensation

For global organizations, managing compensation across multiple currencies is essential:

-- Multi-currency compensation summary with USD conversion
WITH exchange_rates AS (
    -- This would typically come from a currency exchange rates table
    -- Using static rates for example - in practice, use current rates
    SELECT 'EUR' as CURRENCY_CODE, 1.10 as USD_CONVERSION_RATE FROM DUAL
    UNION ALL
    SELECT 'GBP', 1.25 FROM DUAL
    UNION ALL
    SELECT 'CAD', 0.75 FROM DUAL
    UNION ALL
    SELECT 'USD', 1.00 FROM DUAL
)
SELECT 
    org.NAME as DEPARTMENT,
    s.CURRENCY_CODE,
    COUNT(*) as EMPLOYEE_COUNT,
    ROUND(AVG(s.SALARY_AMOUNT), 0) as AVG_SALARY_LOCAL,
    ROUND(MIN(s.SALARY_AMOUNT), 0) as MIN_SALARY_LOCAL,
    ROUND(MAX(s.SALARY_AMOUNT), 0) as MAX_SALARY_LOCAL,
    ROUND(AVG(s.SALARY_AMOUNT * er.USD_CONVERSION_RATE), 0) as AVG_SALARY_USD,
    ROUND(MIN(s.SALARY_AMOUNT * er.USD_CONVERSION_RATE), 0) as MIN_SALARY_USD,
    ROUND(MAX(s.SALARY_AMOUNT * er.USD_CONVERSION_RATE), 0) as MAX_SALARY_USD
FROM CMP_SALARY s
JOIN PER_ALL_PEOPLE_F p 
  ON s.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 s.ASSIGNMENT_ID = a.ASSIGNMENT_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
LEFT JOIN exchange_rates er 
  ON s.CURRENCY_CODE = er.CURRENCY_CODE
WHERE p.CURRENT_EMPLOYEE_FLAG = 'Y'
  AND TRUNC(SYSDATE) BETWEEN s.EFFECTIVE_START_DATE 
                          AND s.EFFECTIVE_END_DATE
GROUP BY org.NAME, s.CURRENCY_CODE
ORDER BY org.NAME, s.CURRENCY_CODE;

Compensation Budget Management

Tracking compensation budget utilization across departments and plans:

-- Compensation budget utilization by department
WITH dept_budgets AS (
    SELECT 
        a.ORGANIZATION_ID,
        cp.PLAN_ID,
        cp.PLAN_NAME,
        cp.PLAN_TYPE_CODE,
        cp.BUDGET_AMOUNT
    FROM CMP_COMPENSATION_PLANS cp
    CROSS JOIN (
        SELECT DISTINCT ORGANIZATION_ID 
        FROM PER_ALL_ASSIGNMENTS_F 
        WHERE TRUNC(SYSDATE) BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE
    ) a
    WHERE cp.STATUS = 'ACTIVE'
      AND TRUNC(SYSDATE) BETWEEN cp.PLAN_PERIOD_START 
                              AND cp.PLAN_PERIOD_END
),
dept_spending AS (
    SELECT 
        a.ORGANIZATION_ID,
        ca.PLAN_ID,
        SUM(ca.AWARD_AMOUNT) as TOTAL_AWARDED,
        COUNT(*) as AWARD_COUNT
    FROM CMP_COMPENSATION_AWARDS ca
    JOIN PER_ALL_ASSIGNMENTS_F a 
      ON ca.PERSON_ID = a.PERSON_ID
      AND ca.EFFECTIVE_DATE BETWEEN a.EFFECTIVE_START_DATE 
                                AND a.EFFECTIVE_END_DATE
      AND a.PRIMARY_FLAG = 'Y'
    WHERE ca.STATUS IN ('APPROVED', 'PROCESSED')
    GROUP BY a.ORGANIZATION_ID, ca.PLAN_ID
)
SELECT 
    org.NAME as DEPARTMENT,
    db.PLAN_NAME,
    db.PLAN_TYPE_CODE,
    db.BUDGET_AMOUNT,
    NVL(ds.TOTAL_AWARDED, 0) as AMOUNT_SPENT,
    db.BUDGET_AMOUNT - NVL(ds.TOTAL_AWARDED, 0) as REMAINING_BUDGET,
    ROUND((NVL(ds.TOTAL_AWARDED, 0) / db.BUDGET_AMOUNT) * 100, 2) as UTILIZATION_PCT,
    NVL(ds.AWARD_COUNT, 0) as AWARDS_GRANTED
FROM dept_budgets db
JOIN HR_ALL_ORGANIZATION_UNITS org 
  ON db.ORGANIZATION_ID = org.ORGANIZATION_ID
LEFT JOIN dept_spending ds 
  ON db.ORGANIZATION_ID = ds.ORGANIZATION_ID
  AND db.PLAN_ID = ds.PLAN_ID
ORDER BY org.NAME, db.PLAN_TYPE_CODE, UTILIZATION_PCT DESC;

Security and Compliance Considerations

Data Security

Sensitive Data: Compensation data is highly sensitive and regulated. Ensure your queries comply with data protection regulations (GDPR, CCPA) and company policies. Implement appropriate access controls and audit trails for all compensation data access.

Audit Trail Queries

Maintaining comprehensive audit trails for compensation changes:

-- Compensation change audit trail
SELECT 
    p.PERSON_NUMBER,
    p.DISPLAY_NAME,
    s.EFFECTIVE_START_DATE,
    s.EFFECTIVE_END_DATE,
    s.SALARY_AMOUNT,
    s.CURRENCY_CODE,
    s.CHANGE_REASON,
    LAG(s.SALARY_AMOUNT) OVER (
        PARTITION BY s.PERSON_ID 
        ORDER BY s.EFFECTIVE_START_DATE
    ) as PREVIOUS_SALARY,
    s.SALARY_AMOUNT - LAG(s.SALARY_AMOUNT) OVER (
        PARTITION BY s.PERSON_ID 
        ORDER BY s.EFFECTIVE_START_DATE
    ) as SALARY_CHANGE_AMOUNT,
    ROUND(
        (s.SALARY_AMOUNT - LAG(s.SALARY_AMOUNT) OVER (
            PARTITION BY s.PERSON_ID 
            ORDER BY s.EFFECTIVE_START_DATE
        )) / LAG(s.SALARY_AMOUNT) OVER (
            PARTITION BY s.PERSON_ID 
            ORDER BY s.EFFECTIVE_START_DATE
        ) * 100, 2
    ) as SALARY_CHANGE_PCT,
    s.LAST_UPDATE_DATE,
    s.LAST_UPDATED_BY
FROM CMP_SALARY s
JOIN PER_ALL_PEOPLE_F p 
  ON s.PERSON_ID = p.PERSON_ID
  AND s.EFFECTIVE_START_DATE BETWEEN p.EFFECTIVE_START_DATE 
                                 AND p.EFFECTIVE_END_DATE
WHERE s.EFFECTIVE_START_DATE >= TRUNC(SYSDATE) - 365  -- Last year
ORDER BY s.PERSON_ID, s.EFFECTIVE_START_DATE DESC;

Best Practices for Compensation Data Management

1. Regular Data Validation

-- Compensation data quality checks
SELECT 
    'Employees without current salary' as ISSUE_TYPE,
    COUNT(*) as ISSUE_COUNT
FROM PER_ALL_PEOPLE_F p
WHERE p.CURRENT_EMPLOYEE_FLAG = 'Y'
  AND TRUNC(SYSDATE) BETWEEN p.EFFECTIVE_START_DATE 
                          AND p.EFFECTIVE_END_DATE
  AND NOT EXISTS (
      SELECT 1 FROM CMP_SALARY s
      WHERE s.PERSON_ID = p.PERSON_ID
        AND TRUNC(SYSDATE) BETWEEN s.EFFECTIVE_START_DATE 
                                AND s.EFFECTIVE_END_DATE
  )

UNION ALL

SELECT 
    'Salaries with missing currency',
    COUNT(*)
FROM CMP_SALARY s
WHERE s.CURRENCY_CODE IS NULL
  AND TRUNC(SYSDATE) BETWEEN s.EFFECTIVE_START_DATE 
                          AND s.EFFECTIVE_END_DATE

UNION ALL

SELECT 
    'Compensation awards without plan',
    COUNT(*)
FROM CMP_COMPENSATION_AWARDS ca
WHERE NOT EXISTS (
    SELECT 1 FROM CMP_COMPENSATION_PLANS cp
    WHERE cp.PLAN_ID = ca.PLAN_ID
  );

2. Performance Optimization

Index strategy: Ensure proper indexing on PERSON_ID, EFFECTIVE_START_DATE, and status columns
Date filtering: Always include appropriate date filters in queries
Batch processing: Use bulk operations for mass salary updates
Partitioning: Consider table partitioning for large datasets with historical data

3. Integration Considerations

When integrating compensation data with external systems:

Payroll integration: Ensure compensation changes flow to payroll systems appropriately
Benefits integration: Coordinate with benefits calculations that depend on salary
Reporting tools: Provide appropriate data aggregation for BI tools
Mobile applications: Consider data volume and sensitivity for mobile access

Mastering Oracle's Workforce Compensation tables enables sophisticated compensation management, pay equity analysis, and strategic workforce planning. The flexible data model supports complex compensation structures while maintaining the audit trails and security controls required for effective governance.

Whether you're implementing merit increase processes, analyzing pay equity, managing stock option plans, or integrating with payroll systems, understanding these core tables and their relationships is essential. Combine compensation data with core HCM entities for comprehensive workforce analytics, and leverage REST APIs for real-time integrations with external compensation and benefits platforms.

Remember that compensation data directly impacts payroll processing and should be integrated with your broader HCM strategy, including absence management and performance management systems for maximum effectiveness.