Oracle Recruiting Cloud Tables and Data Model Complete Guide

Oracle Recruiting Cloud (ORC), also known as Internet Recruiting Cloud (IRC), provides a comprehensive talent acquisition platform within Oracle Fusion HCM. Understanding the underlying database structure is essential for building reports, integrations, and analytics that support your recruiting operations.

This guide covers the core Oracle Recruiting Cloud tables, their relationships, and practical SQL examples for common recruiting scenarios. Whether you're building recruiting dashboards, analyzing time-to-hire metrics, or integrating with external systems, mastering the ORC data model will enhance your talent acquisition capabilities.

Oracle Recruiting Cloud Data Model Overview

The Oracle Recruiting Cloud data model centers around the candidate journey from job posting to hiring. The core entities represent the recruiting lifecycle:

Each stage in this journey corresponds to specific tables and data structures in Oracle Recruiting Cloud, allowing for comprehensive tracking of recruiting metrics and candidate experiences.

Core Recruiting Tables

IRC_REQUISITIONS - Job Requisitions

The IRC_REQUISITIONS table stores information about job openings, including position details, approval workflow, and hiring requirements. This is the starting point for all recruiting activities.

Sample Query: Open Requisitions Report

-- Active requisitions with hiring manager details
SELECT 
    r.REQUISITION_NUMBER,
    r.JOB_TITLE,
    r.DEPARTMENT_NAME,
    r.NUMBER_OF_OPENINGS,
    r.POSTED_DATE,
    r.TARGET_HIRE_DATE,
    p.DISPLAY_NAME as HIRING_MANAGER,
    r.CURRENT_STATE,
    TRUNC(SYSDATE) - TRUNC(r.POSTED_DATE) as DAYS_OPEN
FROM IRC_REQUISITIONS r
LEFT JOIN PER_ALL_PEOPLE_F p 
  ON r.HIRING_MANAGER_ID = p.PERSON_ID
  AND TRUNC(SYSDATE) BETWEEN p.EFFECTIVE_START_DATE 
                          AND p.EFFECTIVE_END_DATE
WHERE r.CURRENT_STATE IN ('Open', 'In Progress')
ORDER BY r.POSTED_DATE DESC;

IRC_CANDIDATES - Candidate Information

The IRC_CANDIDATES table contains candidate profiles, including contact information, work experience, and skills. This table serves as the central repository for all candidate data across multiple job applications.

Sample Query: Candidate Source Analysis

-- Candidate source effectiveness over last 6 months
SELECT 
    c.SOURCE_TYPE,
    COUNT(*) as CANDIDATE_COUNT,
    COUNT(CASE WHEN a.CURRENT_STATE = 'Hired' THEN 1 END) as HIRED_COUNT,
    ROUND(
        COUNT(CASE WHEN a.CURRENT_STATE = 'Hired' THEN 1 END) / COUNT(*) * 100, 2
    ) as HIRE_RATE_PCT
FROM IRC_CANDIDATES c
LEFT JOIN IRC_APPLICATIONS a ON c.CANDIDATE_ID = a.CANDIDATE_ID
WHERE c.REGISTRATION_DATE >= ADD_MONTHS(TRUNC(SYSDATE), -6)
  AND c.SOURCE_TYPE IS NOT NULL
GROUP BY c.SOURCE_TYPE
ORDER BY HIRED_COUNT DESC;

IRC_APPLICATIONS - Job Applications

The IRC_APPLICATIONS table represents the many-to-many relationship between candidates and requisitions. A single candidate can apply to multiple positions, and each application goes through its own workflow.

Sample Query: Recruiting Pipeline Report

-- Current recruiting pipeline by requisition
SELECT 
    r.REQUISITION_NUMBER,
    r.JOB_TITLE,
    r.DEPARTMENT_NAME,
    COUNT(a.APPLICATION_ID) as TOTAL_APPLICATIONS,
    COUNT(CASE WHEN a.CURRENT_STATE = 'New' THEN 1 END) as NEW_APPLICATIONS,
    COUNT(CASE WHEN a.CURRENT_STATE = 'Under Review' THEN 1 END) as UNDER_REVIEW,
    COUNT(CASE WHEN a.CURRENT_STATE = 'Interview' THEN 1 END) as IN_INTERVIEW,
    COUNT(CASE WHEN a.CURRENT_STATE = 'Offer' THEN 1 END) as OFFER_STAGE,
    COUNT(CASE WHEN a.CURRENT_STATE = 'Hired' THEN 1 END) as HIRED,
    COUNT(CASE WHEN a.CURRENT_STATE IN ('Rejected', 'Withdrawn') THEN 1 END) as CLOSED
FROM IRC_REQUISITIONS r
LEFT JOIN IRC_APPLICATIONS a ON r.REQUISITION_ID = a.REQUISITION_ID
WHERE r.CURRENT_STATE IN ('Open', 'In Progress')
GROUP BY r.REQUISITION_ID, r.REQUISITION_NUMBER, r.JOB_TITLE, r.DEPARTMENT_NAME
ORDER BY r.REQUISITION_NUMBER;

IRC_INTERVIEWS - Interview Management

The IRC_INTERVIEWS table tracks interview sessions, including scheduling, participants, and feedback. This table is crucial for managing the interview process and ensuring compliance with hiring practices.

Sample Query: Interview Metrics

-- Interview completion rates and timings by interviewer
SELECT 
    p.DISPLAY_NAME as INTERVIEWER,
    COUNT(i.INTERVIEW_ID) as TOTAL_INTERVIEWS,
    COUNT(CASE WHEN i.INTERVIEW_STATUS = 'Completed' THEN 1 END) as COMPLETED,
    COUNT(CASE WHEN i.INTERVIEW_STATUS = 'Cancelled' THEN 1 END) as CANCELLED,
    ROUND(
        COUNT(CASE WHEN i.INTERVIEW_STATUS = 'Completed' THEN 1 END) / 
        COUNT(i.INTERVIEW_ID) * 100, 2
    ) as COMPLETION_RATE_PCT,
    ROUND(AVG(i.DURATION), 0) as AVG_DURATION_MIN
FROM IRC_INTERVIEWS i
JOIN PER_ALL_PEOPLE_F p 
  ON i.INTERVIEWER_ID = p.PERSON_ID
  AND TRUNC(SYSDATE) BETWEEN p.EFFECTIVE_START_DATE 
                          AND p.EFFECTIVE_END_DATE
WHERE i.SCHEDULED_DATE >= TRUNC(SYSDATE) - 90  -- Last 90 days
GROUP BY p.PERSON_ID, p.DISPLAY_NAME
HAVING COUNT(i.INTERVIEW_ID) >= 5  -- Minimum 5 interviews
ORDER BY COMPLETION_RATE_PCT DESC;

Secondary Tables and Relationships

IRC_OFFERS - Job Offers

The IRC_OFFERS table manages job offers extended to candidates, including compensation details, approval workflow, and acceptance tracking.

IRC_CANDIDATE_SKILLS - Skills and Qualifications

This table stores candidate skills, certifications, and qualifications, enabling skills-based matching and talent pool analysis.

IRC_JOB_REQUIREMENTS - Requisition Requirements

This table defines the skills, experience, and qualifications required for each job requisition, enabling automated candidate matching.

Common Recruiting Queries and Reports

Time-to-Fill Analysis

Understanding how long it takes to fill positions is crucial for recruiting efficiency:

-- Average time-to-fill by department and job title
SELECT 
    r.DEPARTMENT_NAME,
    r.JOB_TITLE,
    COUNT(*) as FILLED_POSITIONS,
    ROUND(AVG(a.HIRE_DATE - r.POSTED_DATE), 1) as AVG_DAYS_TO_FILL,
    MIN(a.HIRE_DATE - r.POSTED_DATE) as MIN_DAYS,
    MAX(a.HIRE_DATE - r.POSTED_DATE) as MAX_DAYS
FROM IRC_REQUISITIONS r
JOIN IRC_APPLICATIONS a 
  ON r.REQUISITION_ID = a.REQUISITION_ID
  AND a.CURRENT_STATE = 'Hired'
WHERE a.HIRE_DATE >= TRUNC(SYSDATE) - 365  -- Last year
  AND r.POSTED_DATE IS NOT NULL
  AND a.HIRE_DATE IS NOT NULL
GROUP BY r.DEPARTMENT_NAME, r.JOB_TITLE
HAVING COUNT(*) >= 3  -- Minimum sample size
ORDER BY AVG_DAYS_TO_FILL DESC;

Recruiter Performance Dashboard

Tracking recruiter effectiveness across multiple metrics:

-- Recruiter performance metrics
WITH recruiter_stats AS (
    SELECT 
        a.RECRUITER_ID,
        p.DISPLAY_NAME as RECRUITER_NAME,
        COUNT(a.APPLICATION_ID) as TOTAL_APPLICATIONS,
        COUNT(CASE WHEN a.CURRENT_STATE = 'Hired' THEN 1 END) as HIRES,
        AVG(CASE WHEN a.HIRE_DATE IS NOT NULL 
            THEN a.HIRE_DATE - a.APPLICATION_DATE END) as AVG_TIME_TO_HIRE,
        COUNT(DISTINCT a.REQUISITION_ID) as REQUISITIONS_WORKED
    FROM IRC_APPLICATIONS a
    JOIN PER_ALL_PEOPLE_F p 
      ON a.RECRUITER_ID = p.PERSON_ID
      AND TRUNC(SYSDATE) BETWEEN p.EFFECTIVE_START_DATE 
                              AND p.EFFECTIVE_END_DATE
    WHERE a.APPLICATION_DATE >= TRUNC(SYSDATE) - 90  -- Last quarter
      AND a.RECRUITER_ID IS NOT NULL
    GROUP BY a.RECRUITER_ID, p.DISPLAY_NAME
)
SELECT 
    RECRUITER_NAME,
    TOTAL_APPLICATIONS,
    HIRES,
    ROUND((HIRES / TOTAL_APPLICATIONS) * 100, 2) as HIRE_RATE_PCT,
    ROUND(AVG_TIME_TO_HIRE, 1) as AVG_DAYS_TO_HIRE,
    REQUISITIONS_WORKED,
    ROUND(TOTAL_APPLICATIONS / REQUISITIONS_WORKED, 1) as AVG_APPS_PER_REQ
FROM recruiter_stats
WHERE TOTAL_APPLICATIONS >= 10  -- Minimum activity level
ORDER BY HIRE_RATE_PCT DESC;

Candidate Conversion Funnel

Analyzing drop-off rates at each stage of the recruiting process:

-- Recruiting funnel analysis
SELECT 
    'Applications Received' as STAGE,
    COUNT(*) as CANDIDATE_COUNT,
    100.0 as CONVERSION_RATE
FROM IRC_APPLICATIONS 
WHERE APPLICATION_DATE >= TRUNC(SYSDATE) - 90

UNION ALL

SELECT 
    'Passed Initial Screening',
    COUNT(*),
    ROUND((COUNT(*) / (
        SELECT COUNT(*) FROM IRC_APPLICATIONS 
        WHERE APPLICATION_DATE >= TRUNC(SYSDATE) - 90
    )) * 100, 2)
FROM IRC_APPLICATIONS 
WHERE CURRENT_PHASE NOT IN ('New', 'Rejected')
  AND APPLICATION_DATE >= TRUNC(SYSDATE) - 90

UNION ALL

SELECT 
    'Interviewed',
    COUNT(DISTINCT a.APPLICATION_ID),
    ROUND((COUNT(DISTINCT a.APPLICATION_ID) / (
        SELECT COUNT(*) FROM IRC_APPLICATIONS 
        WHERE APPLICATION_DATE >= TRUNC(SYSDATE) - 90
    )) * 100, 2)
FROM IRC_APPLICATIONS a
JOIN IRC_INTERVIEWS i ON a.APPLICATION_ID = i.APPLICATION_ID
WHERE a.APPLICATION_DATE >= TRUNC(SYSDATE) - 90
  AND i.INTERVIEW_STATUS = 'Completed'

UNION ALL

SELECT 
    'Received Offer',
    COUNT(DISTINCT a.APPLICATION_ID),
    ROUND((COUNT(DISTINCT a.APPLICATION_ID) / (
        SELECT COUNT(*) FROM IRC_APPLICATIONS 
        WHERE APPLICATION_DATE >= TRUNC(SYSDATE) - 90
    )) * 100, 2)
FROM IRC_APPLICATIONS a
JOIN IRC_OFFERS o ON a.APPLICATION_ID = o.APPLICATION_ID
WHERE a.APPLICATION_DATE >= TRUNC(SYSDATE) - 90

UNION ALL

SELECT 
    'Hired',
    COUNT(*),
    ROUND((COUNT(*) / (
        SELECT COUNT(*) FROM IRC_APPLICATIONS 
        WHERE APPLICATION_DATE >= TRUNC(SYSDATE) - 90
    )) * 100, 2)
FROM IRC_APPLICATIONS 
WHERE CURRENT_STATE = 'Hired'
  AND APPLICATION_DATE >= TRUNC(SYSDATE) - 90

ORDER BY CONVERSION_RATE DESC;

Advanced Analytics and Integrations

Skills Gap Analysis

Identifying skill shortages in your candidate pipeline:

-- Skills gap analysis: Required vs Available
WITH required_skills AS (
    SELECT 
        jr.SKILL_NAME,
        COUNT(DISTINCT r.REQUISITION_ID) as OPEN_POSITIONS,
        AVG(CASE WHEN jr.IMPORTANCE = 'Must Have' THEN 1 ELSE 0 END) as CRITICAL_SKILL_PCT
    FROM IRC_JOB_REQUIREMENTS jr
    JOIN IRC_REQUISITIONS r ON jr.REQUISITION_ID = r.REQUISITION_ID
    WHERE r.CURRENT_STATE IN ('Open', 'In Progress')
    GROUP BY jr.SKILL_NAME
),
available_skills AS (
    SELECT 
        cs.SKILL_NAME,
        COUNT(DISTINCT c.CANDIDATE_ID) as CANDIDATE_COUNT,
        AVG(CASE WHEN cs.PROFICIENCY_LEVEL IN ('Expert', 'Advanced') 
            THEN 1 ELSE 0 END) as HIGH_PROFICIENCY_PCT
    FROM IRC_CANDIDATE_SKILLS cs
    JOIN IRC_CANDIDATES c ON cs.CANDIDATE_ID = c.CANDIDATE_ID
    WHERE c.REGISTRATION_DATE >= TRUNC(SYSDATE) - 365  -- Active candidates
    GROUP BY cs.SKILL_NAME
)
SELECT 
    COALESCE(rs.SKILL_NAME, as_skill.SKILL_NAME) as SKILL,
    NVL(rs.OPEN_POSITIONS, 0) as DEMAND,
    NVL(as_skill.CANDIDATE_COUNT, 0) as SUPPLY,
    CASE 
        WHEN NVL(as_skill.CANDIDATE_COUNT, 0) = 0 THEN 'Critical Shortage'
        WHEN rs.OPEN_POSITIONS > as_skill.CANDIDATE_COUNT THEN 'Shortage'
        WHEN rs.OPEN_POSITIONS * 2 < as_skill.CANDIDATE_COUNT THEN 'Surplus'
        ELSE 'Balanced'
    END as SUPPLY_STATUS,
    ROUND(NVL(rs.CRITICAL_SKILL_PCT, 0) * 100, 1) as CRITICAL_IMPORTANCE_PCT
FROM required_skills rs
FULL OUTER JOIN available_skills as_skill 
  ON rs.SKILL_NAME = as_skill.SKILL_NAME
WHERE NVL(rs.OPEN_POSITIONS, 0) > 0 OR NVL(as_skill.CANDIDATE_COUNT, 0) > 0
ORDER BY NVL(rs.OPEN_POSITIONS, 0) DESC, as_skill.CANDIDATE_COUNT ASC;

Diversity Recruiting Metrics

Tracking diversity at each stage of the recruiting process:

-- Diversity funnel analysis (assuming diversity data is captured)
WITH diversity_funnel AS (
    SELECT 
        c.GENDER,
        c.ETHNICITY,
        COUNT(DISTINCT a.APPLICATION_ID) as APPLICATIONS,
        COUNT(DISTINCT CASE WHEN i.INTERVIEW_STATUS = 'Completed' 
              THEN a.APPLICATION_ID END) as INTERVIEWED,
        COUNT(DISTINCT CASE WHEN o.OFFER_STATUS = 'Extended' 
              THEN a.APPLICATION_ID END) as OFFERS,
        COUNT(DISTINCT CASE WHEN a.CURRENT_STATE = 'Hired' 
              THEN a.APPLICATION_ID END) as HIRES
    FROM IRC_CANDIDATES c
    JOIN IRC_APPLICATIONS a ON c.CANDIDATE_ID = a.CANDIDATE_ID
    LEFT JOIN IRC_INTERVIEWS i ON a.APPLICATION_ID = i.APPLICATION_ID
    LEFT JOIN IRC_OFFERS o ON a.APPLICATION_ID = o.APPLICATION_ID
    WHERE a.APPLICATION_DATE >= TRUNC(SYSDATE) - 180  -- Last 6 months
      AND c.GENDER IS NOT NULL
      AND c.ETHNICITY IS NOT NULL
    GROUP BY c.GENDER, c.ETHNICITY
)
SELECT 
    GENDER,
    ETHNICITY,
    APPLICATIONS,
    INTERVIEWED,
    ROUND((INTERVIEWED / APPLICATIONS) * 100, 2) as INTERVIEW_RATE_PCT,
    OFFERS,
    ROUND((OFFERS / APPLICATIONS) * 100, 2) as OFFER_RATE_PCT,
    HIRES,
    ROUND((HIRES / APPLICATIONS) * 100, 2) as HIRE_RATE_PCT
FROM diversity_funnel
WHERE APPLICATIONS >= 5  -- Minimum sample size
ORDER BY APPLICATIONS DESC;

Integration Considerations

When building integrations with Oracle Recruiting Cloud data, consider these important aspects:

Data Security and Privacy

Performance Optimization

Recruiting tables can grow large over time. Use these optimization strategies:

• Date range filtering: Always include date filters in your queries
• Status filtering: Filter on active/relevant statuses early
• Appropriate indexes: Leverage Oracle's indexes on key columns
• Bulk operations: Use bulk insert/update for data loads

Change Data Capture

For real-time integrations, consider implementing change detection:

-- Identifying recent changes for incremental sync
SELECT 
    a.APPLICATION_ID,
    a.CANDIDATE_ID,
    a.REQUISITION_ID,
    a.CURRENT_STATE,
    a.LAST_UPDATE_DATE,
    'APPLICATION' as RECORD_TYPE
FROM IRC_APPLICATIONS a
WHERE a.LAST_UPDATE_DATE >= :last_sync_timestamp

UNION ALL

SELECT 
    c.CANDIDATE_ID,
    NULL,
    NULL,
    NULL,
    c.LAST_UPDATE_DATE,
    'CANDIDATE'
FROM IRC_CANDIDATES c
WHERE c.LAST_UPDATE_DATE >= :last_sync_timestamp

ORDER BY LAST_UPDATE_DATE;

Best Practices for Recruiting Analytics

1. Establish Clear Metrics

Define key performance indicators (KPIs) that align with business objectives:

• Time-to-fill: Average days from requisition posting to hire
• Cost-per-hire: Total recruiting costs divided by number of hires
• Quality-of-hire: Performance ratings of new hires after 90 days
• Source effectiveness: Hire rates by recruiting source
• Candidate experience: Survey scores and completion rates

2. Implement Data Quality Checks

-- Data quality validation queries
-- Check for missing critical data
SELECT 
    'Missing Requisition Hiring Manager' as ISSUE,
    COUNT(*) as ISSUE_COUNT
FROM IRC_REQUISITIONS 
WHERE CURRENT_STATE IN ('Open', 'In Progress')
  AND HIRING_MANAGER_ID IS NULL

UNION ALL

SELECT 
    'Applications Missing Recruiter',
    COUNT(*)
FROM IRC_APPLICATIONS 
WHERE CURRENT_STATE NOT IN ('Hired', 'Rejected', 'Withdrawn')
  AND RECRUITER_ID IS NULL

UNION ALL

SELECT 
    'Interviews Missing Feedback',
    COUNT(*)
FROM IRC_INTERVIEWS 
WHERE INTERVIEW_STATUS = 'Completed'
  AND OVERALL_RATING IS NULL
  AND SCHEDULED_DATE >= TRUNC(SYSDATE) - 30;

3. Regular Reporting Cadence

Establish regular reporting schedules:

• Daily: New applications, interview schedules, urgent requisitions
• Weekly: Pipeline status, recruiter activity, hiring manager updates
• Monthly: Time-to-fill trends, source effectiveness, diversity metrics
• Quarterly: Strategic metrics, ROI analysis, process improvements

Mastering Oracle Recruiting Cloud's data model enables powerful talent acquisition analytics and reporting. Whether you're tracking recruiting efficiency, analyzing candidate quality, or optimizing your hiring process, understanding these core tables and relationships is essential.

Combine recruiting data with core HCM entities for comprehensive workforce analytics, and leverage REST APIs for real-time integrations with external recruiting tools and platforms.