> Continuous Process Improvement > Recruitment & Talent Acquisition > Oracle HCM Recruiting > Data Template

Your Recruitment & Talent Acquisition Data Template

Oracle HCM Recruiting

Recruitment & Talent Acquisition Attributes (19) Recruitment & Talent Acquisition Activities (14) Extraction Guides (2)

Your Recruitment & Talent Acquisition Data Template

This template provides a comprehensive guide to collecting the necessary data for optimizing your recruitment and talent acquisition process. It outlines essential data attributes, key activities to track, and practical guidance for extracting this information from your source system. Use this resource to ensure a smooth and effective data preparation phase.

Recommended attributes to collect for thorough analysis
Key process activities and milestones to track
Guidance on data extraction from your system

New to event logs? Learn how to create a process mining event log.

Download Template

Recruitment & Talent Acquisition Attributes

These are the recommended data fields to include in your event log for a comprehensive analysis of your recruitment and talent acquisition process.

5 Required 5 Recommended 9 Optional

	Name	Description
	Activity ActivityName	The name of the specific recruitment activity or event that occurred.
Description This attribute describes a single step or milestone in the recruitment process, such as 'Application Screened', 'Interview Conducted', or 'Offer Extended'. Each activity represents a distinct event in the application's lifecycle. It is a fundamental component for process mining, as it defines the nodes in the process map. Analyzing the sequence and frequency of activities helps identify common process paths, bottlenecks, deviations from the standard procedure, and rework loops. Why it matters This attribute defines the steps in the recruitment process, forming the basis of the process map and enabling analysis of process flow and variations. Where to get Typically derived from event or status history tables related to the job application object in Oracle HCM Recruiting. Examples Application ScreenedInterview ConductedOffer ExtendedCandidate Hired
	Job Application JobApplicationId	A unique identifier for each candidate's job application.
Description The Job Application ID is the primary key that tracks a candidate's entire journey for a specific job requisition. It links all related activities, from the initial submission to the final hiring decision, forming a single, coherent process instance. In process mining, this attribute is essential for defining the case, allowing for the reconstruction of each candidate's unique path. Analysts use it to visualize end-to-end application flows, measure overall cycle times, and analyze conversion rates between different stages of the recruitment funnel. Why it matters It uniquely identifies each recruitment case, making it possible to trace the full lifecycle of a candidate's application from start to finish. Where to get This is a primary identifier within the Oracle HCM Recruiting module, typically associated with candidate submission records. Examples APP-2024-00123APP-2024-00124APP-2024-00125
	Start Time EventTime	The timestamp indicating when the recruitment activity started.
Description This attribute captures the precise date and time that a specific activity occurred. It provides the chronological context for ordering events within each job application case. The start time is critical for all time-based process mining analyses. It is used to calculate cycle times between activities, identify delays, measure overall time-to-hire, and check adherence to service level agreements or performance targets. Why it matters It provides the chronological order of events, which is essential for calculating durations, discovering bottlenecks, and analyzing process performance. Where to get This timestamp is usually found alongside the activity name in event logs or status history tables within Oracle HCM Recruiting. Examples 2023-10-01T10:00:00Z2023-10-05T14:30:00Z2023-11-12T09:15:00Z
	Last Data Update LastDataUpdate	The timestamp when the data was last refreshed or extracted from the source system.
Description This attribute indicates the date and time of the most recent data extraction. It provides context on the freshness of the data being analyzed. This is important for users to understand how current the process analysis is and to manage expectations about the visibility of very recent activities. It is a key piece of metadata for any dashboard or report. Why it matters Informs users about the timeliness of the data, ensuring they understand the period covered by the analysis. Where to get This timestamp is generated and added during the data extraction, transformation, and loading (ETL) process. Examples 2024-05-20T08:00:00Z2024-05-21T08:00:00Z
	Source System SourceSystem	The system from which the data originated.
Description This attribute identifies the source information system where the recruitment data was recorded. For this process, it will typically be 'Oracle HCM Recruiting'. In environments with multiple systems, this field is crucial for data lineage and troubleshooting. It helps confirm that the data is coming from the expected source and can be used to filter analyses for data from specific platforms. Why it matters Identifies the origin of the data, which is crucial for data governance, validation, and ensuring the analysis is based on the correct information source. Where to get This is often a static value added during the data extraction, transformation, and loading (ETL) process to label the dataset. Examples Oracle HCM RecruitingOracle Fusion Cloud HCM
	Application Source ApplicationSource	The channel or source through which the application was received.
Description This attribute tracks how a candidate found the job opening, for example, 'LinkedIn', 'Company Website', 'Employee Referral', or 'Job Board'. It is critical for the Application Source Effectiveness dashboard, as it allows the organization to measure the return on investment for different sourcing channels. By analyzing which sources yield the most hires or have the fastest time-to-hire, the talent acquisition team can optimize its sourcing strategy. Why it matters Helps determine the most effective channels for attracting and hiring qualified candidates, optimizing recruitment marketing spend. Where to get This information is typically captured when a candidate submits an application in Oracle HCM Recruiting. Examples LinkedInEmployee ReferralCompany Careers PageIndeed
	Application Status ApplicationStatus	The current or final status of the job application.
Description This attribute indicates the disposition of the application, such as 'Active', 'Hired', 'Rejected', or 'Withdrawn'. It provides a snapshot of where the application stands in the process or its final outcome. Analyzing the final status is fundamental to calculating key metrics like hire rates and drop-off rates. It helps in understanding the overall effectiveness of the recruitment funnel by showing how many candidates successfully reach the final stage. Why it matters Provides the outcome of the recruitment process, which is essential for calculating conversion rates, hire rates, and drop-off rates. Where to get A standard field on the main job application record in Oracle HCM Recruiting. Examples HiredRejectedWithdrawn by CandidateOffer Accepted
	Department Department	The department or business unit for which the candidate is being hired.
Description This attribute specifies the organizational unit, such as 'Engineering', 'Sales', or 'Marketing', that has the open position. It provides an organizational context for each job application. Segmenting the recruitment process by department is essential for performance benchmarking. It allows leadership to compare time-to-hire, drop-off rates, and other key metrics across different parts of the organization, as required by the Departmental Recruitment Performance Benchmarking dashboard. Why it matters Enables comparison of recruitment performance and process variations across different business units or departments. Where to get Typically part of the job requisition data linked to the application in Oracle HCM Recruiting. Examples EngineeringSalesFinanceHuman Resources
	Hiring Manager HiringManager	The name or ID of the hiring manager for the associated job requisition.
Description This attribute identifies the manager of the team or department with the open position. The hiring manager is a key stakeholder, often involved in screening, interviewing, and making the final hiring decision. Analyzing process performance by hiring manager can reveal bottlenecks related to their availability for interviews or delays in providing feedback. It helps identify opportunities for training or process improvement to help managers execute their hiring responsibilities more efficiently. Why it matters Identifies a key stakeholder in the process, allowing for analysis of delays or efficiencies related to hiring manager involvement. Where to get Stored on the job requisition object, which is linked to the job application, within Oracle HCM Recruiting. Examples Robert BrownSusan WhiteMichael Green
	Recruiter Recruiter	The name or ID of the recruiter responsible for managing the job application.
Description This attribute identifies the talent acquisition professional assigned to the application. This person is typically responsible for guiding the candidate through the process. Analyzing the process by recruiter allows for workload balancing, performance benchmarking, and identifying best practices. It helps answer questions such as which recruiters have the fastest cycle times or the highest offer acceptance rates, supporting the Recruiter Workload and Efficiency dashboard. Why it matters Attributes activities to specific individuals, enabling analysis of recruiter workload, efficiency, and performance. Where to get Associated with the job application or requisition object in Oracle HCM Recruiting. Examples John SmithJane DoeEmily Jones
	End Time EventEndTime	The timestamp indicating when the recruitment activity was completed.
Description The End Time marks the completion of an activity. The difference between the Start Time and End Time represents the processing time of a single activity. In process mining, this is used to calculate the duration of specific tasks, such as the time taken to screen an application or conduct an interview. Analyzing activity durations helps identify which specific steps are consuming the most time within the overall process. Why it matters Enables the calculation of individual activity durations, which is key to pinpointing inefficient steps and measuring task-level performance. Where to get May be available in event log tables or can be derived from the Start Time of the subsequent activity. Examples 2023-10-01T10:30:00Z2023-10-05T15:00:00Z2023-11-12T11:15:00Z
	Is Internal Candidate IsInternalCandidate	A flag indicating if the candidate is a current employee.
Description This boolean attribute distinguishes between internal candidates, who are existing employees, and external candidates. Internal and external hiring processes often have different steps, SLAs, and success rates. Analyzing this attribute allows for a comparison between internal mobility and external hiring processes. It can help determine if the company is effectively promoting from within and whether the internal hiring process is more efficient than the external one. Why it matters Separates internal and external candidates, whose recruitment paths and performance metrics often differ significantly, allowing for more accurate, segmented analysis. Where to get This is typically a flag on the candidate or application profile in Oracle HCM Recruiting. Examples truefalse
	Is Rework IsRework	A flag that indicates if an activity is being performed for a second or subsequent time.
Description This calculated attribute identifies instances where an activity is repeated within the same case. For example, if an application goes from 'Interview' back to 'Screening' and then back to 'Interview', the second 'Screening' and 'Interview' activities would be flagged as rework. Analyzing rework is key to discovering process inefficiencies and hidden costs. It helps pinpoint areas where the process is failing, requiring steps to be repeated. This directly supports the Application Rework and Bottleneck Analysis dashboard by quantifying the frequency and impact of such loops. Why it matters Identifies process inefficiencies and loops where work is repeated, helping to uncover hidden costs and opportunities for streamlining. Where to get Calculated during data preparation by checking if the same activity has already occurred for a given CaseId. Examples truefalse
	Job Location JobLocation	The geographical location or office where the job is based.
Description This attribute specifies the city, state, or country associated with the job requisition. It can also indicate if a position is remote. This dimension allows for regional analysis of recruitment processes. It can help answer questions like whether time-to-hire varies significantly between different locations or if certain regions have higher candidate drop-off rates. This is useful for tailoring recruitment strategies to specific labor markets. Why it matters Allows for geographical analysis of recruitment metrics, helping to identify regional differences in hiring performance and challenges. Where to get Stored on the job requisition object in Oracle HCM Recruiting. Examples New York, NYLondon, UKRemoteSan Francisco, CA
	Job Requisition ID JobRequisitionId	The unique identifier for the job opening to which the candidate applied.
Description The Job Requisition ID links multiple job applications to a single, approved job opening. It represents the demand side of the recruitment process. This attribute is crucial for aggregating application data to analyze the overall health of a specific hiring effort. It allows analysis of how many candidates are in the pipeline for a given role, the effectiveness of sourcing for that role, and the total time taken to fill the position. Why it matters Groups all applications for the same job opening, enabling analysis of the entire talent pipeline for a specific role. Where to get This is a primary key on the job requisition object, linked to each application in Oracle HCM Recruiting. Examples REQ-2024-501REQ-2024-502REQ-2024-503
	Offer Amount OfferAmount	The salary amount of the job offer extended to the candidate.
Description This attribute captures the proposed compensation for the role. It is a critical piece of information in the final stages of the recruitment process. While sensitive, analyzing offer amounts can provide insights into compensation competitiveness. It can be correlated with offer acceptance rates to understand if compensation is a factor in candidates declining offers. It can also be used to ensure pay equity across different departments and roles. Why it matters Allows for analysis of compensation trends and their impact on offer acceptance rates, contributing to a more competitive hiring strategy. Where to get Found within the job offer details section for an application in Oracle HCM Recruiting. Examples 8500012000065000
	Processing Time ProcessingTime	The duration of a single activity.
Description This metric represents the time spent on a specific task, calculated as the difference between the End Time and Start Time of an activity. It measures the active processing duration for a step. This calculated attribute is vital for identifying which specific activities are the most time-consuming. For example, it can highlight if interview feedback submission is taking too long or if offer approvals are delayed. This helps focus improvement efforts on the most inefficient steps. Why it matters Pinpoints the exact duration of individual process steps, making it possible to identify and address the most time-consuming activities. Where to get Calculated during data transformation by subtracting the EventTime of an activity from the EventEndTime. Examples 360086400604800
	Rejection Reason RejectionReason	The reason why a candidate's application was rejected.
Description This attribute provides context when an application is moved to a 'Rejected' status. Reasons might include 'Not a culture fit', 'Lacks required skills', or 'Hired another candidate'. Analyzing rejection reasons at different stages of the process provides invaluable feedback. It can highlight issues with job descriptions, misaligned expectations, or problems in the screening process, helping to refine recruitment strategy and improve the quality of the candidate pool. Why it matters Provides insight into why candidates are not progressing, which is crucial for improving job descriptions, screening criteria, and overall process effectiveness. Where to get Typically captured in a specific field or notes section when a recruiter or hiring manager rejects a candidate in Oracle HCM Recruiting. Examples Does not meet minimum qualificationsMore qualified candidate selectedCompensation expectations not aligned
	Time To Hire Target TimeToHireTarget	The target duration, in days, for filling the job requisition.
Description This attribute defines the expected timeframe for completing the hiring process, from opening the requisition to a candidate accepting an offer. This target is often set based on the role's level or department standards. This is essential for the Time-to-Hire Target Adherence KPI. By comparing the actual time-to-hire against this target, the organization can measure performance against its strategic goals and proactively identify requisitions that are at risk of taking too long to fill. Why it matters Provides a benchmark to measure actual time-to-hire performance against, directly supporting KPI calculations for target adherence. Where to get This may be a field on the job requisition object in Oracle HCM Recruiting or could be maintained in a separate system. Examples 30456090

Required Recommended Optional

Recruitment & Talent Acquisition Activities

These are the key process steps and milestones to capture in your event log for accurate process discovery and bottleneck identification.

6 Recommended 8 Optional

	Activity	Description
	Application Received	Marks the creation of a new job application when a candidate applies for a position. This event is typically captured explicitly when the application record is first created in the system.
Why it matters This is the starting point for every case, essential for calculating end-to-end process durations like Time-to-Hire and analyzing application volumes over time. Where to get This event is captured from the creation timestamp of a candidate's submission record in Oracle Recruiting, likely from a table such as IRC_SUBMISSIONS. Capture From the creation timestamp of the job application record. Event type explicit
	Application Rejected	Indicates that the company has decided not to move forward with a candidate at any stage of the process. This is captured by a status change on the application.
Why it matters This is a common end point for applications. Analyzing at which stage rejections occur is key to understanding drop-off rates and process efficiency. Where to get Inferred from any terminal status change on the job application, such as 'Rejected', 'Not Selected', or a similar configured value. Capture Inferred from job application status change to a terminal 'Rejected' state. Event type inferred
	Candidate Hired	Represents the final, successful conclusion of the recruitment process. This event occurs when the candidate's status is officially changed to 'Hired', triggering the handoff to core HR.
Why it matters This is the primary success outcome for the process. It serves as the end point for calculating Time-to-Hire and other key efficiency metrics. Where to get This is a key status change on the job application record in IRC_SUBMISSIONS. It is triggered by the 'Hire' or 'Move to HR' action in Oracle Recruiting. Capture Logged when recruiter executes the final 'Hire' action. Event type explicit
	Interview Conducted	Marks the completion of a candidate interview. This is often inferred when an interviewer or recruiter updates the status of the scheduled interview to 'Completed' or 'Conducted'.
Why it matters This is a critical milestone in the recruitment process. The cycle time from this activity to 'Offer Extended' is a key performance indicator. Where to get Inferred from a status change on the scheduled interview record within Oracle Recruiting. The timestamp of this status change serves as the event time. Capture Inferred from interview status changing to 'Completed' or 'Finished'. Event type inferred
	Offer Accepted	Indicates that the candidate has formally accepted the job offer. This event is captured when the candidate accepts via the career portal or a recruiter manually updates the status.
Why it matters This is a primary success milestone in the process. It is the basis for calculating the Offer Acceptance Rate and signals the start of post-offer activities. Where to get Captured from a status update on the offer record to 'Accepted'. This can be triggered by the candidate online or entered manually by the recruiter. Capture From the timestamp when the offer status changes to 'Accepted'. Event type explicit
	Offer Extended	Marks the formal extension of a job offer to a candidate. This is an explicit event, logged when a recruiter officially sends the offer through the Oracle Recruiting system.
Why it matters This is a key milestone for measuring the Offer Acceptance Rate and the duration from the final interview to the offer, a critical part of the candidate experience. Where to get Explicitly captured when the job offer status is moved to 'Extended' or 'Sent'. The timestamp of this action is recorded on the offer object. Capture Logged when the recruiter executes the 'Extend Offer' action. Event type explicit
	Application Screened	Represents the initial review of a candidate's application by a recruiter to check for basic qualifications. This is typically inferred when the application status changes from 'New' to 'Under Review'.
Why it matters Analyzing the time spent in this stage helps identify bottlenecks in the initial review process. It is also key to measuring the Rework Rate for Application Screening KPI. Where to get Inferred from the history of the job application status field in Oracle Recruiting. Look for a status change in the IRC_SUBMISSIONS table history. Capture Inferred from job application status change to 'Screening' or 'Under Review'. Event type inferred
	Application Withdrawn	Represents a candidate's decision to remove themselves from consideration for the role. This is captured when the application status is updated to 'Withdrawn by Candidate'.
Why it matters Tracking withdrawals helps identify stages where candidate experience may be poor, leading to high drop-off. It is crucial for analyzing the Candidate Drop-off Rate KPI. Where to get Inferred from a status change on the job application to 'Withdrawn'. This can be initiated by the candidate in the portal or by a recruiter on their behalf. Capture Inferred from job application status change to 'Withdrawn by Candidate'. Event type inferred
	Assessment Completed	Indicates that a candidate has completed a required pre-screening assessment, such as a skills test or personality evaluation. This event is typically recorded when the assessment platform returns a completion status to Oracle HCM.
Why it matters This activity helps track candidate engagement and analyze the time taken for candidates to complete assessments, which can be a point of drop-off. Where to get Captured from a status update pushed from an integrated third-party assessment tool or a manual status change by a recruiter within the candidate's application profile. Capture Logged when assessment provider API updates the candidate status. Event type explicit
	Background Check Initiated	Marks the initiation of a background check for a candidate after they have accepted an offer. This event is often triggered by a status change or integration with a third-party service.
Why it matters This activity is crucial for tracking post-offer, pre-hire timelines and ensuring compliance. Delays here can impact the candidate's start date. Where to get Inferred from a job application status change to 'Background Check' or logged via an API call to an integrated background check vendor. Capture Inferred from application status change to 'Background Check in Progress'. Event type inferred
	Interview Feedback Submitted	Indicates that an interviewer has submitted their feedback and evaluation of the candidate post-interview. This is an explicit event captured when the feedback form is saved.
Why it matters Delays between the interview and feedback submission can significantly slow down the hiring process. Tracking this helps identify bottlenecks in decision-making. Where to get Explicitly logged when an interviewer submits their evaluation through the Oracle Recruiting portal. The creation timestamp of the feedback record is used. Capture From the creation timestamp of the interview feedback record. Event type explicit
	Interview Scheduled	Represents the point when an interview with a candidate is officially scheduled in the system. This is usually captured when an interview is created and associated with the job application.
Why it matters Tracking this helps measure the time it takes to arrange interviews, which is a key component of the candidate experience and the overall Time-to-Hire. Where to get This event is explicitly logged in Oracle Recruiting's interview scheduling module. Data would be found in interview-related tables linked to the job application. Capture From the creation record of an interview event linked to the application. Event type explicit
	Offer Declined	Represents the candidate's decision to decline the job offer. This is an explicit status change on the offer record, often accompanied by a reason.
Why it matters This is a critical failure point in the process. Analyzing when and why offers are declined provides insights into compensation competitiveness and candidate experience. Where to get Captured from a status update on the offer record to 'Declined' or 'Rejected by Candidate'. Capture From the timestamp when the offer status changes to 'Declined'. Event type explicit
	Offer Details Approved	Represents the completion of an internal approval workflow for a job offer before it is extended to the candidate. This is inferred from the final approval status on the offer record.
Why it matters This activity helps analyze the duration and efficiency of the internal offer approval process, which can be a significant source of delay. Where to get Inferred from the status change history of the job offer record within Oracle Recruiting, tracking when it moves into an 'Approved' state. Capture Inferred from the offer object status changing to 'Approved'. Event type inferred

Recommended Optional

Extraction Guides

How to get your data from Oracle HCM Recruiting

Steps

Navigate to OTBI: Log in to Oracle HCM Cloud. Use the Navigator menu to go to Tools, then select Reports and Analytics.
Create a New Analysis: Inside the Reports and Analytics work area, click Create and then select Analysis. Do not use the Report option, as Analysis provides the flexibility needed.
Select Subject Area: When prompted, choose the 'Recruiting - Recruiting Real Time' subject area. This is the data model that contains all the necessary job application, interview, and offer details.
Access Advanced Configuration: After selecting the subject area, you will be taken to the analysis editor. Navigate to the Advanced tab in the top right corner. This allows direct manipulation of the logical SQL query.
Paste the Logical SQL: In the 'Analysis XML' section, find the saw:query tag. Replace the existing SQL query inside this tag with the complete logical SQL provided in the 'Query' section of this document. It is crucial to replace the entire default query.
Set Date Range Parameters: Before running the query, replace the placeholder values for [Start Date] and [End Date] with your desired date range in 'YYYY-MM-DD' format. This is critical for controlling the amount of data extracted.
Validate the Query: Click the 'Validate SQL' button to ensure there are no syntax errors. If the validation is successful, you can proceed. Note that this only checks syntax, not if the columns exist in your specific configuration.
View the Results: Navigate back to the Results tab. The analysis will execute, and a preview of the event log data will be displayed. Verify that the columns and data appear as expected.
Configure Export Settings: For large datasets, it is recommended to schedule the analysis as a job rather than running it interactively. In the analysis editor, you can set export properties to ensure the CSV format is clean.
Export the Data: Once satisfied with the results, click the Export link and choose the CSV format. This will download the complete event log file.
Prepare for Upload: Open the downloaded CSV file. Ensure the column headers match the required attributes (JobApplicationId, ActivityName, EventTime, etc.) and that the date format is consistent. The file is now ready for upload into a process mining tool.

Configuration

Subject Area: The entire query is built upon the Recruiting - Recruiting Real Time subject area. The user running the report must have the necessary permissions to access this subject area.
Date Range Filtering: The query includes placeholders [Start Date] and [End Date]. It is critical to define a specific date range to manage performance and data volume. We recommend starting with a recent period of 3-6 months.
Data Security: OTBI enforces Oracle HCM data security roles. The user running the report will only see data for the business units, departments, and legal employers they are authorized to view.
Performance Considerations: For large organizations with high recruiting volumes, running this query over a long period can be slow or may time out in the interactive user interface. For large data extracts, it is best practice to save the analysis and schedule it to run as a background job, delivering the output to a content server or email.
Customization: The provided query uses standard fields. If your organization uses Descriptive Flexfields (DFFs) or custom objects to track specific recruitment events, you will need to modify the logical SQL to include them.

a Sample Query config

SELECT
    "Recruiting - Recruiting Real Time"."Job Application - General"."Job Application Identifier" as "JobApplicationId",
    'Application Received' as "ActivityName",
    CAST("Recruiting - Recruiting Real Time"."Job Application - General"."Submission Date" AS TIMESTAMP) as "EventTime",
    'Oracle HCM Recruiting' as "SourceSystem",
    CURRENT_TIMESTAMP as "LastDataUpdate",
    "Recruiting - Recruiting Real Time"."Primary Recruiter"."Recruiter Display Name" as "Recruiter",
    "Recruiting - Recruiting Real Time"."Hiring Manager"."Hiring Manager Display Name" as "HiringManager",
    "Recruiting - Recruiting Real Time"."Requisition - General"."Department Name" as "Department",
    "Recruiting - Recruiting Real Time"."Job Application - Current Phase and State"."Current State" as "ApplicationStatus",
    "Recruiting - Recruiting Real Time"."Job Application - Source"."Source Name" as "ApplicationSource"
FROM "Recruiting - Recruiting Real Time"
WHERE "Recruiting - Recruiting Real Time"."Job Application - General"."Submission Date" IS NOT NULL
AND "Recruiting - Recruiting Real Time"."Job Application - General"."Submission Date" >= TIMESTAMP '[Start Date]'
AND "Recruiting - Recruiting Real Time"."Job Application - General"."Submission Date" < TIMESTAMP '[End Date]'

UNION ALL

SELECT
    T1."Job Application Identifier" as "JobApplicationId",
    'Application Screened' as "ActivityName",
    CAST(T2."Start Date" AS TIMESTAMP) as "EventTime",
    'Oracle HCM Recruiting' as "SourceSystem",
    CURRENT_TIMESTAMP as "LastDataUpdate",
    T4."Recruiter Display Name" as "Recruiter",
    T5."Hiring Manager Display Name" as "HiringManager",
    T6."Department Name" as "Department",
    T3."Current State" as "ApplicationStatus",
    T7."Source Name" as "ApplicationSource"
FROM
    "Recruiting - Recruiting Real Time"."Job Application - General" T1,
    "Recruiting - Recruiting Real Time"."Job Application - Phase and State History" T2,
    "Recruiting - Recruiting Real Time"."Job Application - Current Phase and State" T3,
    "Recruiting - Recruiting Real Time"."Primary Recruiter" T4,
    "Recruiting - Recruiting Real Time"."Hiring Manager" T5,
    "Recruiting - Recruiting Real Time"."Requisition - General" T6,
    "Recruiting - Recruiting Real Time"."Job Application - Source" T7
WHERE T1."Job Application Identifier" = T2."Job Application Identifier"
AND T1."Job Application Identifier" = T3."Job Application Identifier"
AND T1."Recruiter Login Name" = T4."Recruiter Login Name"
AND T1."Hiring Manager Login Name" = T5."Hiring Manager Login Name"
AND T1."Requisition Number" = T6."Requisition Number"
AND T1."Source ID" = T7."Source ID"
AND T2."State Name" = 'Screen'
AND T2."Start Date" IS NOT NULL
AND T2."Start Date" >= TIMESTAMP '[Start Date]'
AND T2."Start Date" < TIMESTAMP '[End Date]'

UNION ALL

SELECT
    "Recruiting - Recruiting Real Time"."Job Application - General"."Job Application Identifier" as "JobApplicationId",
    'Assessment Completed' as "ActivityName",
    CAST("Recruiting - Recruiting Real Time"."Job Application - Assessment"."Assessment Completion Date" AS TIMESTAMP) as "EventTime",
    'Oracle HCM Recruiting' as "SourceSystem",
    CURRENT_TIMESTAMP as "LastDataUpdate",
    "Recruiting - Recruiting Real Time"."Primary Recruiter"."Recruiter Display Name" as "Recruiter",
    "Recruiting - Recruiting Real Time"."Hiring Manager"."Hiring Manager Display Name" as "HiringManager",
    "Recruiting - Recruiting Real Time"."Requisition - General"."Department Name" as "Department",
    "Recruiting - Recruiting Real Time"."Job Application - Current Phase and State"."Current State" as "ApplicationStatus",
    "Recruiting - Recruiting Real Time"."Job Application - Source"."Source Name" as "ApplicationSource"
FROM "Recruiting - Recruiting Real Time"
WHERE "Recruiting - Recruiting Real Time"."Job Application - Assessment"."Assessment Completion Date" IS NOT NULL
AND "Recruiting - Recruiting Real Time"."Job Application - Assessment"."Assessment Completion Date" >= TIMESTAMP '[Start Date]'
AND "Recruiting - Recruiting Real Time"."Job Application - Assessment"."Assessment Completion Date" < TIMESTAMP '[End Date]'

UNION ALL

SELECT
    "Recruiting - Recruiting Real Time"."Job Application - General"."Job Application Identifier" as "JobApplicationId",
    'Interview Scheduled' as "ActivityName",
    CAST("Recruiting - Recruiting Real Time"."Job Application - Interview Schedule"."Interview Scheduled Date" AS TIMESTAMP) as "EventTime",
    'Oracle HCM Recruiting' as "SourceSystem",
    CURRENT_TIMESTAMP as "LastDataUpdate",
    "Recruiting - Recruiting Real Time"."Primary Recruiter"."Recruiter Display Name" as "Recruiter",
    "Recruiting - Recruiting Real Time"."Hiring Manager"."Hiring Manager Display Name" as "HiringManager",
    "Recruiting - Recruiting Real Time"."Requisition - General"."Department Name" as "Department",
    "Recruiting - Recruiting Real Time"."Job Application - Current Phase and State"."Current State" as "ApplicationStatus",
    "Recruiting - Recruiting Real Time"."Job Application - Source"."Source Name" as "ApplicationSource"
FROM "Recruiting - Recruiting Real Time"
WHERE "Recruiting - Recruiting Real Time"."Job Application - Interview Schedule"."Interview Scheduled Date" IS NOT NULL
AND "Recruiting - Recruiting Real Time"."Job Application - Interview Schedule"."Interview Scheduled Date" >= TIMESTAMP '[Start Date]'
AND "Recruiting - Recruiting Real Time"."Job Application - Interview Schedule"."Interview Scheduled Date" < TIMESTAMP '[End Date]'

UNION ALL

SELECT
    "Recruiting - Recruiting Real Time"."Job Application - General"."Job Application Identifier" as "JobApplicationId",
    'Interview Conducted' as "ActivityName",
    CAST("Recruiting - Recruiting Real Time"."Job Application - Interview Feedback"."Interview Completion Date" AS TIMESTAMP) as "EventTime",
    'Oracle HCM Recruiting' as "SourceSystem",
    CURRENT_TIMESTAMP as "LastDataUpdate",
    "Recruiting - Recruiting Real Time"."Primary Recruiter"."Recruiter Display Name" as "Recruiter",
    "Recruiting - Recruiting Real Time"."Hiring Manager"."Hiring Manager Display Name" as "HiringManager",
    "Recruiting - Recruiting Real Time"."Requisition - General"."Department Name" as "Department",
    "Recruiting - Recruiting Real Time"."Job Application - Current Phase and State"."Current State" as "ApplicationStatus",
    "Recruiting - Recruiting Real Time"."Job Application - Source"."Source Name" as "ApplicationSource"
FROM "Recruiting - Recruiting Real Time"
WHERE "Recruiting - Recruiting Real Time"."Job Application - Interview Feedback"."Interview Event Overall Status" = 'Completed'
AND "Recruiting - Recruiting Real Time"."Job Application - Interview Feedback"."Interview Completion Date" IS NOT NULL
AND "Recruiting - Recruiting Real Time"."Job Application - Interview Feedback"."Interview Completion Date" >= TIMESTAMP '[Start Date]'
AND "Recruiting - Recruiting Real Time"."Job Application - Interview Feedback"."Interview Completion Date" < TIMESTAMP '[End Date]'

UNION ALL

SELECT
    "Recruiting - Recruiting Real Time"."Job Application - General"."Job Application Identifier" as "JobApplicationId",
    'Interview Feedback Submitted' as "ActivityName",
    CAST("Recruiting - Recruiting Real Time"."Job Application - Interview Feedback"."Feedback Submission Date" AS TIMESTAMP) as "EventTime",
    'Oracle HCM Recruiting' as "SourceSystem",
    CURRENT_TIMESTAMP as "LastDataUpdate",
    "Recruiting - Recruiting Real Time"."Primary Recruiter"."Recruiter Display Name" as "Recruiter",
    "Recruiting - Recruiting Real Time"."Hiring Manager"."Hiring Manager Display Name" as "HiringManager",
    "Recruiting - Recruiting Real Time"."Requisition - General"."Department Name" as "Department",
    "Recruiting - Recruiting Real Time"."Job Application - Current Phase and State"."Current State" as "ApplicationStatus",
    "Recruiting - Recruiting Real Time"."Job Application - Source"."Source Name" as "ApplicationSource"
FROM "Recruiting - Recruiting Real Time"
WHERE "Recruiting - Recruiting Real Time"."Job Application - Interview Feedback"."Feedback Submission Date" IS NOT NULL
AND "Recruiting - Recruiting Real Time"."Job Application - Interview Feedback"."Feedback Submission Date" >= TIMESTAMP '[Start Date]'
AND "Recruiting - Recruiting Real Time"."Job Application - Interview Feedback"."Feedback Submission Date" < TIMESTAMP '[End Date]'

UNION ALL

SELECT
    "Recruiting - Recruiting Real Time"."Job Application - General"."Job Application Identifier" as "JobApplicationId",
    'Offer Details Approved' as "ActivityName",
    CAST("Recruiting - Recruiting Real Time"."Job Application - Offer"."Offer Approval Date" AS TIMESTAMP) as "EventTime",
    'Oracle HCM Recruiting' as "SourceSystem",
    CURRENT_TIMESTAMP as "LastDataUpdate",
    "Recruiting - Recruiting Real Time"."Primary Recruiter"."Recruiter Display Name" as "Recruiter",
    "Recruiting - Recruiting Real Time"."Hiring Manager"."Hiring Manager Display Name" as "HiringManager",
    "Recruiting - Recruiting Real Time"."Requisition - General"."Department Name" as "Department",
    "Recruiting - Recruiting Real Time"."Job Application - Current Phase and State"."Current State" as "ApplicationStatus",
    "Recruiting - Recruiting Real Time"."Job Application - Source"."Source Name" as "ApplicationSource"
FROM "Recruiting - Recruiting Real Time"
WHERE "Recruiting - Recruiting Real Time"."Job Application - Offer"."Offer Approval Date" IS NOT NULL
AND "Recruiting - Recruiting Real Time"."Job Application - Offer"."Offer Approval Date" >= TIMESTAMP '[Start Date]'
AND "Recruiting - Recruiting Real Time"."Job Application - Offer"."Offer Approval Date" < TIMESTAMP '[End Date]'

UNION ALL

SELECT
    "Recruiting - recruiting Real Time"."Job Application - General"."Job Application Identifier" as "JobApplicationId",
    'Offer Extended' as "ActivityName",
    CAST("Recruiting - Recruiting Real Time"."Job Application - Offer"."Offer Extended Date" AS TIMESTAMP) as "EventTime",
    'Oracle HCM Recruiting' as "SourceSystem",
    CURRENT_TIMESTAMP as "LastDataUpdate",
    "Recruiting - Recruiting Real Time"."Primary Recruiter"."Recruiter Display Name" as "Recruiter",
    "Recruiting - Recruiting Real Time"."Hiring Manager"."Hiring Manager Display Name" as "HiringManager",
    "Recruiting - Recruiting Real Time"."Requisition - General"."Department Name" as "Department",
    "Recruiting - Recruiting Real Time"."Job Application - Current Phase and State"."Current State" as "ApplicationStatus",
    "Recruiting - Recruiting Real Time"."Job Application - Source"."Source Name" as "ApplicationSource"
FROM "Recruiting - Recruiting Real Time"
WHERE "Recruiting - Recruiting Real Time"."Job Application - Offer"."Offer Extended Date" IS NOT NULL
AND "Recruiting - Recruiting Real Time"."Job Application - Offer"."Offer Extended Date" >= TIMESTAMP '[Start Date]'
AND "Recruiting - Recruiting Real Time"."Job Application - Offer"."Offer Extended Date" < TIMESTAMP '[End Date]'

UNION ALL

SELECT
    "Recruiting - Recruiting Real Time"."Job Application - General"."Job Application Identifier" as "JobApplicationId",
    'Offer Accepted' as "ActivityName",
    CAST("Recruiting - Recruiting Real Time"."Job Application - Offer"."Offer Accepted Date" AS TIMESTAMP) as "EventTime",
    'Oracle HCM Recruiting' as "SourceSystem",
    CURRENT_TIMESTAMP as "LastDataUpdate",
    "Recruiting - Recruiting Real Time"."Primary Recruiter"."Recruiter Display Name" as "Recruiter",
    "Recruiting - Recruiting Real Time"."Hiring Manager"."Hiring Manager Display Name" as "HiringManager",
    "Recruiting - Recruiting Real Time"."Requisition - General"."Department Name" as "Department",
    "Recruiting - Recruiting Real Time"."Job Application - Current Phase and State"."Current State" as "ApplicationStatus",
    "Recruiting - Recruiting Real Time"."Job Application - Source"."Source Name" as "ApplicationSource"
FROM "Recruiting - Recruiting Real Time"
WHERE "Recruiting - Recruiting Real Time"."Job Application - Offer"."Offer Accepted Date" IS NOT NULL
AND "Recruiting - Recruiting Real Time"."Job Application - Offer"."Offer Accepted Date" >= TIMESTAMP '[Start Date]'
AND "Recruiting - Recruiting Real Time"."Job Application - Offer"."Offer Accepted Date" < TIMESTAMP '[End Date]'

UNION ALL

SELECT
    "Recruiting - Recruiting Real Time"."Job Application - General"."Job Application Identifier" as "JobApplicationId",
    'Offer Declined' as "ActivityName",
    CAST("Recruiting - Recruiting Real Time"."Job Application - Offer"."Offer Declined Date" AS TIMESTAMP) as "EventTime",
    'Oracle HCM Recruiting' as "SourceSystem",
    CURRENT_TIMESTAMP as "LastDataUpdate",
    "Recruiting - Recruiting Real Time"."Primary Recruiter"."Recruiter Display Name" as "Recruiter",
    "Recruiting - Recruiting Real Time"."Hiring Manager"."Hiring Manager Display Name" as "HiringManager",
    "Recruiting - Recruiting Real Time"."Requisition - General"."Department Name" as "Department",
    "Recruiting - Recruiting Real Time"."Job Application - Current Phase and State"."Current State" as "ApplicationStatus",
    "Recruiting - Recruiting Real Time"."Job Application - Source"."Source Name" as "ApplicationSource"
FROM "Recruiting - Recruiting Real Time"
WHERE "Recruiting - Recruiting Real Time"."Job Application - Offer"."Offer Declined Date" IS NOT NULL
AND "Recruiting - Recruiting Real Time"."Job Application - Offer"."Offer Declined Date" >= TIMESTAMP '[Start Date]'
AND "Recruiting - Recruiting Real Time"."Job Application - Offer"."Offer Declined Date" < TIMESTAMP '[End Date]'

UNION ALL

SELECT
    T1."Job Application Identifier" as "JobApplicationId",
    'Background Check Initiated' as "ActivityName",
    CAST(T2."Start Date" AS TIMESTAMP) as "EventTime",
    'Oracle HCM Recruiting' as "SourceSystem",
    CURRENT_TIMESTAMP as "LastDataUpdate",
    T4."Recruiter Display Name" as "Recruiter",
    T5."Hiring Manager Display Name" as "HiringManager",
    T6."Department Name" as "Department",
    T3."Current State" as "ApplicationStatus",
    T7."Source Name" as "ApplicationSource"
FROM
    "Recruiting - Recruiting Real Time"."Job Application - General" T1,
    "Recruiting - Recruiting Real Time"."Job Application - Phase and State History" T2,
    "Recruiting - Recruiting Real Time"."Job Application - Current Phase and State" T3,
    "Recruiting - Recruiting Real Time"."Primary Recruiter" T4,
    "Recruiting - Recruiting Real Time"."Hiring Manager" T5,
    "Recruiting - Recruiting Real Time"."Requisition - General" T6,
    "Recruiting - Recruiting Real Time"."Job Application - Source" T7
WHERE T1."Job Application Identifier" = T2."Job Application Identifier"
AND T1."Job Application Identifier" = T3."Job Application Identifier"
AND T1."Recruiter Login Name" = T4."Recruiter Login Name"
AND T1."Hiring Manager Login Name" = T5."Hiring Manager Login Name"
AND T1."Requisition Number" = T6."Requisition Number"
AND T1."Source ID" = T7."Source ID"
AND T2."State Name" = 'Background Check'
AND T2."Start Date" IS NOT NULL
AND T2."Start Date" >= TIMESTAMP '[Start Date]'
AND T2."Start Date" < TIMESTAMP '[End Date]'

UNION ALL

SELECT
    T1."Job Application Identifier" as "JobApplicationId",
    'Candidate Hired' as "ActivityName",
    CAST(T2."Start Date" AS TIMESTAMP) as "EventTime",
    'Oracle HCM Recruiting' as "SourceSystem",
    CURRENT_TIMESTAMP as "LastDataUpdate",
    T4."Recruiter Display Name" as "Recruiter",
    T5."Hiring Manager Display Name" as "HiringManager",
    T6."Department Name" as "Department",
    T3."Current State" as "ApplicationStatus",
    T7."Source Name" as "ApplicationSource"
FROM
    "Recruiting - Recruiting Real Time"."Job Application - General" T1,
    "Recruiting - Recruiting Real Time"."Job Application - Phase and State History" T2,
    "Recruiting - Recruiting Real Time"."Job Application - Current Phase and State" T3,
    "Recruiting - Recruiting Real Time"."Primary Recruiter" T4,
    "Recruiting - Recruiting Real Time"."Hiring Manager" T5,
    "Recruiting - Recruiting Real Time"."Requisition - General" T6,
    "Recruiting - Recruiting Real Time"."Job Application - Source" T7
WHERE T1."Job Application Identifier" = T2."Job Application Identifier"
AND T1."Job Application Identifier" = T3."Job Application Identifier"
AND T1."Recruiter Login Name" = T4."Recruiter Login Name"
AND T1."Hiring Manager Login Name" = T5."Hiring Manager Login Name"
AND T1."Requisition Number" = T6."Requisition Number"
AND T1."Source ID" = T7."Source ID"
AND T2."State Name" = 'Hired'
AND T2."Start Date" IS NOT NULL
AND T2."Start Date" >= TIMESTAMP '[Start Date]'
AND T2."Start Date" < TIMESTAMP '[End Date]'

UNION ALL

SELECT
    T1."Job Application Identifier" as "JobApplicationId",
    'Application Rejected' as "ActivityName",
    CAST(T2."Start Date" AS TIMESTAMP) as "EventTime",
    'Oracle HCM Recruiting' as "SourceSystem",
    CURRENT_TIMESTAMP as "LastDataUpdate",
    T4."Recruiter Display Name" as "Recruiter",
    T5."Hiring Manager Display Name" as "HiringManager",
    T6."Department Name" as "Department",
    T3."Current State" as "ApplicationStatus",
    T7."Source Name" as "ApplicationSource"
FROM
    "Recruiting - Recruiting Real Time"."Job Application - General" T1,
    "Recruiting - Recruiting Real Time"."Job Application - Phase and State History" T2,
    "Recruiting - Recruiting Real Time"."Job Application - Current Phase and State" T3,
    "Recruiting - Recruiting Real Time"."Primary Recruiter" T4,
    "Recruiting - Recruiting Real Time"."Hiring Manager" T5,
    "Recruiting - Recruiting Real Time"."Requisition - General" T6,
    "Recruiting - Recruiting Real Time"."Job Application - Source" T7
WHERE T1."Job Application Identifier" = T2."Job Application Identifier"
AND T1."Job Application Identifier" = T3."Job Application Identifier"
AND T1."Recruiter Login Name" = T4."Recruiter Login Name"
AND T1."Hiring Manager Login Name" = T5."Hiring Manager Login Name"
AND T1."Requisition Number" = T6."Requisition Number"
AND T1."Source ID" = T7."Source ID"
AND T2."State Name" LIKE '%Rejected%'
AND T2."Start Date" IS NOT NULL
AND T2."Start Date" >= TIMESTAMP '[Start Date]'
AND T2."Start Date" < TIMESTAMP '[End Date]'

UNION ALL

SELECT
    T1."Job Application Identifier" as "JobApplicationId",
    'Application Withdrawn' as "ActivityName",
    CAST(T2."Start Date" AS TIMESTAMP) as "EventTime",
    'Oracle HCM Recruiting' as "SourceSystem",
    CURRENT_TIMESTAMP as "LastDataUpdate",
    T4."Recruiter Display Name" as "Recruiter",
    T5."Hiring Manager Display Name" as "HiringManager",
    T6."Department Name" as "Department",
    T3."Current State" as "ApplicationStatus",
    T7."Source Name" as "ApplicationSource"
FROM
    "Recruiting - Recruiting Real Time"."Job Application - General" T1,
    "Recruiting - Recruiting Real Time"."Job Application - Phase and State History" T2,
    "Recruiting - Recruiting Real Time"."Job Application - Current Phase and State" T3,
    "Recruiting - Recruiting Real Time"."Primary Recruiter" T4,
    "Recruiting - Recruiting Real Time"."Hiring Manager" T5,
    "Recruiting - Recruiting Real Time"."Requisition - General" T6,
    "Recruiting - Recruiting Real Time"."Job Application - Source" T7
WHERE T1."Job Application Identifier" = T2."Job Application Identifier"
AND T1."Job Application Identifier" = T3."Job Application Identifier"
AND T1."Recruiter Login Name" = T4."Recruiter Login Name"
AND T1."Hiring Manager Login Name" = T5."Hiring Manager Login Name"
AND T1."Requisition Number" = T6."Requisition Number"
AND T1."Source ID" = T7."Source ID"
AND T2."State Name" = 'Withdrawn by Candidate'
AND T2."Start Date" IS NOT NULL
AND T2."Start Date" >= TIMESTAMP '[Start Date]'
AND T2."Start Date" < TIMESTAMP '[End Date]'

Steps

Prerequisites: Ensure you have Python 3 installed on your system along with the requests library. You can install it using pip: pip install requests. You will also need valid user credentials (username and password) and the hostname for your Oracle HCM Cloud instance with permissions to access the Recruiting REST API.
Script Initialization: Create a new Python script file, for example extract_hcm_data.py. Begin by importing the necessary libraries: requests, csv, and datetime.
Define Configuration: At the top of your script, define variables for your Oracle HCM hostname, username, password, and the desired date range for the extraction. Using placeholders for credentials is a security best practice.
Establish API Session: Create a requests.Session object. Set the session's authentication property with your username and password. This will ensure credentials are sent with every subsequent API request made using this session.
Implement Pagination Loop: The script must fetch data in pages to handle large volumes and respect API limits. Initialize an offset variable to 0 and a limit, for example 500. Create a while loop that continues as long as the API returns data. In each iteration, the script will call the API with the current offset and then increment the offset by the limit for the next page.
API Request: Inside the loop, construct the full API request URL for the recruitingCEJobApplications endpoint. Use the fields parameter to specify all required attributes, and the expand parameter to include related objects like Phases and Offers. Use the q parameter to filter by CreationDate based on your configured date range.
Process JSON Response: For each API call, check for a successful HTTP status code (200). Parse the JSON response. The applications will be in the items array of the response body.
Iterate and Generate Events: Loop through each application object returned by the API. For each application, extract the common attributes like ApplicationId, Recruiter, and HiringManager.
Map Statuses to Activities: This is the core logic. For each application, you must generate a separate row for each of the 14 required activities. This involves inspecting the CreationDate, the Phases collection, and the Offers collection. For example, use the CreationDate for 'Application Received'. Iterate through the Phases array to create rows for 'Application Screened', 'Interview Scheduled', 'Candidate Hired', 'Application Rejected', etc., using the timestamp of when each phase was entered. Similarly, iterate through the Offers array for 'Offer Extended', 'Offer Accepted', and 'Offer Declined' events.
Construct Event Log: As each activity event is generated, store it as a dictionary with keys corresponding to the event log column headers (JobApplicationId, ActivityName, EventTime, etc.). Append each dictionary to a master list that will form your complete event log.
Export to CSV: Once the pagination loop is complete, use Python's built-in csv module to write the master list of event dictionaries to a CSV file. Ensure the file is UTF-8 encoded and that the headers in the CSV file exactly match the required attribute names for ProcessMind ingestion.

Configuration

API Credentials: A dedicated service account with a role granting access to the Recruiting REST API, such as 'Recruiting Manager' or a custom role with 'Recruiting REST API Access' privileges, is required.
API Endpoint: The primary endpoint used is recruitingCEJobApplications. It is crucial to use the expand query parameter (e.g., expand=Phases,Offers) to retrieve nested objects containing status history and offer details in a single call, which is more efficient.
Date Range Filtering: Use the q query parameter to filter applications by date. A filter on CreationDate is recommended for managing data volume. For an initial analysis, a date range of the last 3 to 6 months is advisable. Example: q=CreationDate>'2023-01-01T00:00:00Z'.
Field Selection: To optimize performance and reduce network payload, use the fields parameter to request only the necessary attributes. Include fields like ApplicationId, CreationDate, LastUpdateDate, Recruiter, HiringManager, Requisition, Phases, and Offers.
Pagination Limit: The limit parameter controls how many records are returned per API call. A value between 200 and 500 is typically a good balance between the number of API calls and the size of each response.
Status Mappings: The script contains a dictionary to map phase and status codes from Oracle HCM to the standardized activity names. This mapping may need to be customized based on your organization's specific recruiting workflow configuration.

a Sample Query python

import requests
import csv
import json
from datetime import datetime

class=class="hl-string">"hl-comment"># --- Configuration Settings ---
HCM_HOST = class="hl-string">"https://[Your HCM Host].fa.us2.oraclecloud.com"
USERNAME = class="hl-string">"[Your Username]"
PASSWORD = class="hl-string">"[Your Password]"

class=class="hl-string">"hl-comment"># Date range for the query (ISO 8601 format)
START_DATE = class="hl-string">"2023-01-01T00:00:00+00:00"
END_DATE = class="hl-string">"2023-12-31T23:59:59+00:00"

class=class="hl-string">"hl-comment"># API and File settings
API_ENDPOINT = class="hl-string">"/hcmRestApi/resources/11.13.18.05/recruitingCEJobApplications"
CSV_FILENAME = class="hl-string">"recruitment_event_log.csv"
API_LIMIT = 500

class=class="hl-string">"hl-comment"># --- Main Extraction Logic ---
def main():
    class="hl-string">"""Main function to orchestrate the extraction process."""
    event_log = []
    offset = 0
    has_more = True

    session = requests.Session()
    session.auth = (USERNAME, PASSWORD)

    print(class="hl-string">"Starting data extraction from Oracle HCM...")

    while has_more:
        print(fclass="hl-string">"Fetching applications, offset: {offset}")
        try:
            params = {
                class="hl-string">'q': fclass="hl-string">"CreationDate between '{START_DATE}' and '{END_DATE}'",
                class="hl-string">'fields': class="hl-string">'ApplicationId,CreationDate,LastUpdateDate,ApplicationStatus,ApplicationSource,Recruiter,HiringManager,Department,Phases,Offers',
                class="hl-string">'expand': class="hl-string">'Phases,Offers',
                class="hl-string">'limit': API_LIMIT,
                class="hl-string">'offset': offset
            }
            response = session.get(HCM_HOST + API_ENDPOINT, params=params)
            response.raise_for_status()  class=class="hl-string">"hl-comment"># Raises an exception for bad status codes

            data = response.json()
            applications = data.get(class="hl-string">'items', [])

            if not applications:
                print(class="hl-string">"No more applications found.")
                break

            process_applications(applications, event_log)

            has_more = data.get(class="hl-string">'hasMore', False)
            offset += API_LIMIT

        except requests.exceptions.HTTPError as http_err:
            print(fclass="hl-string">"HTTP error occurred: {http_err} - {response.text}")
            break
        except Exception as err:
            print(fclass="hl-string">"An error occurred: {err}")
            break

    if event_log:
        write_to_csv(event_log)
        print(fclass="hl-string">"Extraction complete. {len(event_log)} events written to {CSV_FILENAME}")
    else:
        print(class="hl-string">"No events were generated.")

def process_applications(applications, event_log):
    class="hl-string">"""Processes a batch of applications and generates event log entries."""
    now_iso = datetime.utcnow().isoformat() + class="hl-string">'Z'

    for app in applications:
        base_event = {
            class="hl-string">'JobApplicationId': app.get(class="hl-string">'ApplicationId'),
            class="hl-string">'Recruiter': app.get(class="hl-string">'Recruiter'),
            class="hl-string">'HiringManager': app.get(class="hl-string">'HiringManager'),
            class="hl-string">'Department': app.get(class="hl-string">'Department'),
            class="hl-string">'ApplicationSource': app.get(class="hl-string">'ApplicationSource'),
            class="hl-string">'ApplicationStatus': app.get(class="hl-string">'ApplicationStatus'),
            class="hl-string">'SourceSystem': class="hl-string">'Oracle HCM Recruiting',
            class="hl-string">'LastDataUpdate': now_iso
        }

        class=class="hl-string">"hl-comment"># 1. Application Received
        event_log.append({**base_event, class="hl-string">'ActivityName': class="hl-string">'Application Received', class="hl-string">'EventTime': app.get(class="hl-string">'CreationDate')})

        class=class="hl-string">"hl-comment"># Process phases for status-based events
        for phase in app.get(class="hl-string">'Phases', []):
            phase_name = phase.get(class="hl-string">'PhaseName', class="hl-string">'').lower()
            status_name = phase.get(class="hl-string">'StateName', class="hl-string">'').lower()
            entry_date = phase.get(class="hl-string">'EntryDate')

            if not entry_date: continue

            activity_map = {
                class="hl-string">'screen': class="hl-string">'Application Screened',
                class="hl-string">'assessment': class="hl-string">'Assessment Completed',
                class="hl-string">'interview': class="hl-string">'Interview Scheduled',
                class="hl-string">'offer': class="hl-string">'Offer Details Approved', class=class="hl-string">"hl-comment"># Approximation, happens before offer is made
                class="hl-string">'background check': class="hl-string">'Background Check Initiated',
                class="hl-string">'hired': class="hl-string">'Candidate Hired',
                class="hl-string">'rejected': class="hl-string">'Application Rejected',
                class="hl-string">'withdrawn': class="hl-string">'Application Withdrawn'
            }

            class=class="hl-string">"hl-comment"># Simple mapping by phase name
            for key, activity in activity_map.items():
                if key in phase_name:
                    event_log.append({**base_event, class="hl-string">'ActivityName': activity, class="hl-string">'EventTime': entry_date})

            class=class="hl-string">"hl-comment"># Specific logic for Interview Conducted
            if class="hl-string">'interview' in phase_name and status_name == class="hl-string">'completed':
                 event_log.append({**base_event, class="hl-string">'ActivityName': class="hl-string">'Interview Conducted', class="hl-string">'EventTime': entry_date})

            class=class="hl-string">"hl-comment"># Specific logic for Feedback Submitted (approximation)
            if class="hl-string">'interview' in phase_name and status_name == class="hl-string">'completed':
                 class=class="hl-string">"hl-comment"># This is an approximation, assuming feedback is submitted upon completion
                 event_log.append({**base_event, class="hl-string">'ActivityName': class="hl-string">'Interview Feedback Submitted', class="hl-string">'EventTime': entry_date})


        class=class="hl-string">"hl-comment"># Process offers for offer-related events
        for offer in app.get(class="hl-string">'Offers', []):
            offer_status = offer.get(class="hl-string">'OfferStatus', class="hl-string">'').lower()
            offer_date = offer.get(class="hl-string">'OfferDate') or offer.get(class="hl-string">'LastUpdateDate')

            if not offer_date: continue

            if offer_status == class="hl-string">'extended':
                event_log.append({**base_event, class="hl-string">'ActivityName': class="hl-string">'Offer Extended', class="hl-string">'EventTime': offer_date})
            elif offer_status == class="hl-string">'accepted':
                event_log.append({**base_event, class="hl-string">'ActivityName': class="hl-string">'Offer Accepted', class="hl-string">'EventTime': offer_date})
            elif offer_status == class="hl-string">'declined':
                event_log.append({**base_event, class="hl-string">'ActivityName': class="hl-string">'Offer Declined', class="hl-string">'EventTime': offer_date})


def write_to_csv(event_log):
    class="hl-string">"""Writes the event log data to a CSV file."""
    if not event_log:
        return

    headers = [
        class="hl-string">'JobApplicationId', class="hl-string">'ActivityName', class="hl-string">'EventTime', class="hl-string">'SourceSystem', class="hl-string">'LastDataUpdate',
        class="hl-string">'Recruiter', class="hl-string">'HiringManager', class="hl-string">'Department', class="hl-string">'ApplicationStatus', class="hl-string">'ApplicationSource'
    ]

    class=class="hl-string">"hl-comment"># Deduplicate and sort events to ensure chronological order per application
    unique_events = {tuple(event.items()) for event in event_log}
    sorted_events = sorted([dict(event) for event in unique_events], key=lambda x: (x[class="hl-string">'JobApplicationId'], x[class="hl-string">'EventTime']))

    with open(CSV_FILENAME, class="hl-string">'w', newline=class="hl-string">'', encoding=class="hl-string">'utf-8') as csvfile:
        writer = csv.DictWriter(csvfile, fieldnames=headers)
        writer.writeheader()
        writer.writerows(sorted_events)

if __name__ == class="hl-string">"__main__":
    main()

Your Recruitment & Talent Acquisition Data Template

Your Recruitment & Talent Acquisition Data Template

Recruitment & Talent Acquisition Attributes

Recruitment & Talent Acquisition Activities

Extraction Guides

Oracle HCM OTBI Report for Recruitment Data Export

Steps

Configuration

a Sample Query config

Python Script Extraction using Oracle HCM REST API

Steps

Configuration

a Sample Query python

Ready to Get Started?

Optimize Your Recruitment & Talent Acquisition Today

Your Recruitment & Talent Acquisition Data Template

Your Recruitment & Talent Acquisition Data Template

Recruitment & Talent Acquisition Attributes

Recruitment & Talent Acquisition Activities

Extraction Guides

Oracle HCM OTBI Report for Recruitment Data Export

Steps

Configuration

a Sample Query config

Python Script Extraction using Oracle HCM REST API

Steps

Configuration

a Sample Query python

We value your privacy

Ready to Get Started?

Optimize Your Recruitment & Talent Acquisition Today