> Continuous Process Improvement > Employee Onboarding > Oracle HCM Onboarding > Data Template

Your Employee Onboarding Data Template

Oracle HCM Onboarding

Employee Onboarding Attributes (22) Employee Onboarding Activities (16) Extraction Guides (4)

Your Employee Onboarding Data Template

This template provides a clear roadmap for collecting the essential data needed to analyze your employee onboarding process. It outlines the crucial attributes and activities to track, along with practical guidance for extracting this information directly from Oracle HCM Onboarding. Use this resource to streamline your data collection and prepare for insightful process analysis.

Recommended attributes for comprehensive analysis
Key activities to monitor across the onboarding journey
Step-by-step data extraction guidance for Oracle HCM Onboarding

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

Download Template

Employee Onboarding Attributes

These are the recommended data fields to include in your event log for a comprehensive analysis of your employee onboarding process.

3 Required 8 Recommended 11 Optional

	Name	Description
	Activity Name ActivityName	The name of a specific event or task that occurred during the employee onboarding process.
Description This attribute describes a single step or milestone within the onboarding journey, such as 'Offer Accepted', 'Background Check Completed', or 'First Day Orientation Conducted'. These activities form the building blocks of the process map. Analyzing the sequence and duration of these activities is fundamental to process mining. It helps identify bottlenecks, deviations from the standard process, and opportunities for automation or improvement. Understanding the flow of activities is critical for dashboards related to cycle time and process variants. Why it matters This attribute defines the steps in the process, allowing for the visualization and analysis of the onboarding workflow, bottlenecks, and variations. Where to get This is typically sourced from the event or task logs within the Oracle HCM Onboarding module. It may be a standardized task name or a status change record. Examples Offer AcceptedBackground Check InitiatedPre-Employment Paperwork CompletedFirst Day Orientation Conducted
	Event Time EventTime	The timestamp indicating when a specific onboarding activity or event occurred.
Description Event Time captures the precise date and time that a task was completed or a milestone was reached. This chronological data is the foundation for calculating all time-based metrics in process mining. This attribute is used to order events correctly, calculate the duration between activities, measure the total cycle time of the onboarding process, and identify delays. It is essential for nearly all dashboards and KPIs, including bottleneck analysis and cycle time tracking. Why it matters This timestamp is critical for ordering events, calculating durations, and measuring process performance against time-based targets. Where to get This information is usually recorded alongside each activity in the transaction or event logs of the Oracle HCM Onboarding module. Examples 2024-05-20T10:00:00Z2024-05-21T14:35:10Z2024-05-22T09:00:00Z
	Onboarding Case OnboardingCase	The unique identifier that groups all events and activities for a single new employee's onboarding journey.
Description The Onboarding Case serves as the central identifier for the entire onboarding process, from offer acceptance through full integration. Each case represents one new hire's complete lifecycle. In process mining, this attribute is essential for stitching together all related activities into a coherent end-to-end process map. It allows for the analysis of process variants, cycle times, and outcomes for individual onboarding journeys, enabling a comprehensive view of how different new hires experience the process. Why it matters This is the core identifier that connects all related onboarding activities, making it possible to analyze the entire process flow for each new hire. Where to get This identifier is typically generated within the Oracle HCM Onboarding module when a candidate's journey is initiated. Consult Oracle HCM Onboarding documentation or a system administrator to identify the specific field. Examples ONB-2024-00123ONB-2024-00124ONB-2024-00125
	Activity Duration ActivityDuration	The calculated time taken to complete a specific onboarding activity.
Description This metric represents the processing time of an activity, calculated as the difference between its end and start timestamps. If an end time is not available, it can be estimated as the time until the next activity begins. Activity duration is a cornerstone of process mining analysis, directly feeding into the 'Activity Bottleneck Analysis' dashboard. By highlighting which steps take the most time, it allows process owners to focus their improvement efforts on the areas with the greatest impact on overall cycle time. Why it matters Directly measures the time spent on each task, making it essential for identifying bottlenecks and prioritizing process improvement efforts. Where to get This is a calculated metric, derived by subtracting the 'EventTime' (StartTime) from the 'EventEndTime'. Examples 25920086400604800
	Compliance Due Date ComplianceDueDate	The deadline by which mandatory training or compliance tasks must be completed.
Description This attribute specifies the target completion date for critical onboarding tasks, particularly those related to legal, regulatory, or company policy requirements. It acts as a service level agreement (SLA) for compliance activities. In analysis, this date is compared against the actual completion timestamp of activities like 'Mandatory Training Completed' to determine if compliance goals are being met. It is the basis for the 'On-Time Training Completion Rate' KPI and the 'Compliance Adherence Tracking' dashboard. Why it matters This date is essential for monitoring compliance, calculating on-time completion rates for mandatory tasks, and mitigating corporate risk. Where to get This date is often defined in the onboarding task setup or journey template within Oracle HCM, potentially calculated based on the hire date. Examples 2024-06-302024-07-152024-08-30
	Event End Time EventEndTime	The timestamp when an activity with a measurable duration concluded.
Description While Event Time (StartTime) marks the beginning or a point-in-time event, Event End Time marks the completion. The presence of both start and end times allows for precise duration measurement of activities like 'Background Check'. This attribute is essential for calculating the `ProcessingTime` or `ActivityDuration` for tasks that are not instantaneous. It enables more accurate bottleneck analysis by distinguishing between the waiting time before a task and the actual time spent executing it. Why it matters Enables the precise calculation of activity processing times, which is crucial for accurately identifying process bottlenecks and inefficiencies. Where to get Consult Oracle HCM Onboarding documentation. This may be available in task or workflow tables alongside the start time. Examples 2024-05-23T11:05:00Z2024-05-24T16:20:15Z2024-05-25T10:00:00Z
	Hiring Manager HiringManager	The name or ID of the manager for the new hire.
Description This attribute identifies the manager who will be responsible for the new employee. The hiring manager is a key stakeholder in the onboarding process. Analyzing data by hiring manager can reveal variations in how different managers engage with the onboarding process. For instance, it can show which managers are prompt in completing their tasks like 'Team Introduction' and which may require more support, helping to improve manager effectiveness and new hire integration. Why it matters Helps analyze the involvement and effectiveness of hiring managers in the onboarding process, which directly impacts new hire experience. Where to get This information is a core part of the employee record in Oracle HCM, linking an employee to their line manager. Examples manager_user1Emily Whitemanager_user3
	New Hire Department NewHireDepartment	The department or organizational unit that the new employee is joining.
Description This attribute specifies the department, such as 'Sales', 'Engineering', or 'Finance', to which the new hire is assigned. It is a key dimension for segmenting and comparing the onboarding process. By filtering or comparing by department, analysts can determine if the onboarding experience or efficiency differs across the organization. This can highlight best practices in certain departments or identify others that may need additional support or process improvements. It's crucial for understanding how process performance varies by business context. Why it matters Allows for the segmentation of onboarding data to compare process efficiency and experience across different parts of the organization. Where to get This is a core attribute of the new hire's position and is stored in their employee profile within Oracle HCM. Examples Sales and MarketingSoftware EngineeringFinanceHuman Resources
	New Hire Job Title NewHireJobTitle	The official job title of the new employee.
Description This attribute contains the specific role of the new hire, for example 'Senior Software Engineer' or 'Account Executive'. It provides another layer of detail for process analysis. Different job roles may have unique onboarding requirements, such as specialized training or equipment. Analyzing the process by job title can help ensure that these unique needs are being met efficiently and can reveal patterns, such as longer IT provisioning times for technical roles. Why it matters Provides context about the new hire's role, enabling analysis to see if onboarding processes differ for various job functions or levels. Where to get This is part of the core job information for a new hire, stored within their employee record in Oracle HCM. Examples Senior Software EngineerAccount ExecutiveHR GeneralistProduct Manager
	Official Start Date OfficialStartDate	The new employee's official first day of employment.
Description This attribute is a critical date that serves as a key milestone in the onboarding process. It separates pre-employment activities from post-hire integration activities. This date is used as a baseline for several important KPIs, including the 'Pre-Employment Cycle Time', 'Time to Team Introduction', and the 'First Day Ready Rate'. Analyzing activities relative to the start date helps assess whether the organization is adequately preparing for the new hire's arrival, ensuring they can be productive from day one. Why it matters Serves as a critical milestone to measure pre-employment efficiency and to assess if the new hire is equipped for success on their first day. Where to get This is a fundamental field in the new hire's record within the Oracle HCM Core HR module. Examples 2024-06-012024-07-152024-08-01
	Performed By User PerformedByUser	The user ID or name of the employee or system that executed the onboarding activity.
Description This attribute identifies the human resource, such as an HR specialist, a hiring manager, or the new hire themselves, responsible for completing a task. It can also indicate automated actions performed by the system. Analyzing who performs which activities helps in understanding resource allocation, workload distribution, and automation levels. It can reveal if delays are associated with specific teams or roles and is useful for identifying opportunities for targeted training or process redesign. Why it matters Identifies the actor for each activity, enabling analysis of workload, resource performance, and automation levels within the process. Where to get This information is commonly found in the audit trail or event history logs within Oracle HCM Onboarding, associated with each task or transaction. Examples john.doejane.smithHCM_SYSTEM_AUTOMATION recruiter_team
	Background Check Status BackgroundCheckStatus	The outcome or status of the background check process.
Description This attribute tracks the result of the background check, which is a critical, and often lengthy, step in the pre-employment phase. Statuses can include 'In Progress', 'Clear', or 'Requires Review'. Monitoring this status is important for the 'Pre-Employment Cycle Time' dashboard. Delays or issues with the background check can significantly impact the new hire's start date, and tracking this attribute helps to identify and manage these potential roadblocks proactively. Why it matters Provides visibility into a critical pre-hire step, helping to track progress and identify delays that could impact the new hire's start date. Where to get This information may be stored in Oracle HCM Onboarding, or it might need to be sourced from an integrated third-party background check vendor. Examples ClearRequires ReviewIn ProgressNot Initiated
	Employment Type EmploymentType	Indicates the employment arrangement for the new hire, such as full-time, part-time, or contractor.
Description This attribute categorizes the new hire based on their employment contract. Different types of employees often have distinct onboarding paths and requirements. Using this attribute for filtering allows for the analysis of specific onboarding processes, for example, comparing the cycle time for contractors versus full-time employees. This can help tailor and optimize the onboarding journey for different segments of the workforce. Why it matters Enables comparison of onboarding processes for different worker classifications, ensuring each path is optimized and appropriate. Where to get This is a standard field within the employee's job or assignment record in Oracle HCM. Examples Full-TimePart-TimeContractorIntern
	Is First Day Ready IsFirstDayReady	A calculated flag indicating if essential equipment was delivered before the new hire's start date.
Description This boolean attribute is derived by comparing the completion timestamp of 'Equipment Delivered' and other readiness activities against the 'Official Start Date'. It is true if all prerequisites are met before the first day. This attribute directly powers the 'First Day Ready Rate' KPI. A high rate indicates a smooth and positive start for new hires, allowing them to be productive immediately. A low rate signals systemic issues in the provisioning process that need to be addressed. Why it matters Measures the success of pre-employment preparations, which is crucial for new hire productivity and their initial experience with the company. Where to get This is a calculated attribute, derived by comparing the 'Equipment Delivered' timestamp against the 'Official Start Date' attribute for each case. Examples truefalse
	Is HR Profile Rework IsHrProfileRework	A flag indicating if the 'HR Profile Created' activity occurred more than once for a new hire.
Description This boolean attribute is used to identify instances of rework in the HR profile creation step. It is set to true if the 'HR Profile Created' activity, or a related correction activity, appears multiple times within the same onboarding case. This attribute directly supports the 'HR Profile Rework Rate' KPI. A high incidence of rework suggests issues with data quality, system integrations, or user error, all of which introduce delays and inefficiencies into the onboarding process. Why it matters Helps quantify process inefficiency by flagging cases that require corrections, pointing to potential issues in data entry or process design. Where to get This is a calculated attribute derived by analyzing the sequence of activities within the process mining tool to detect repeated steps. Examples falsetrue
	Is Training On Time IsTrainingOnTime	A calculated flag indicating if mandatory training was completed by the compliance due date.
Description This boolean attribute is derived by comparing the completion timestamp of the 'Mandatory Training Completed' activity against the 'Compliance Due Date'. It is true if the training was completed on or before the deadline. This is the core metric for the 'On-Time Training Completion Rate' KPI. It provides a clear, binary outcome for compliance adherence, making it easy to track performance over time and identify cases that are at risk of non-compliance. Why it matters Provides a clear indicator of compliance adherence, helping to track and manage risks associated with overdue mandatory training. Where to get This is a calculated attribute, derived by comparing the 'Mandatory Training Completed' timestamp against the 'Compliance Due Date' attribute. Examples truefalse
	Last Data Update LastDataUpdate	The timestamp indicating the last time the data for this record was refreshed or extracted.
Description This attribute provides the date and time of the most recent data load. It is a metadata field that is crucial for understanding the freshness of the analysis. Users rely on this information to know if the dashboards and analyses they are viewing are up-to-date. It helps manage expectations about data latency and is important for validating data quality and refresh schedules. Why it matters Indicates the freshness of the data, which is critical for ensuring that analyses and dashboards are based on current information. Where to get This timestamp is generated and added during the data extraction and transformation (ETL) process. Examples 2024-06-10T04:00:00Z2024-06-11T04:00:00Z
	New Hire Country NewHireCountry	The country where the new employee is based.
Description This attribute specifies the geographical location of the new hire. Onboarding processes can vary significantly between countries due to different labor laws and cultural practices. Analyzing the process by country helps identify regional differences in efficiency and compliance. This information is key to standardizing processes where possible, while accommodating necessary local variations. Why it matters Allows for geographical segmentation of the onboarding process to identify regional variations in performance and compliance. Where to get This is part of the new hire's location or assignment details within their core employee profile in Oracle HCM. Examples United StatesGermanyIndiaUnited Kingdom
	Onboarding Journey Type OnboardingJourneyType	The specific type of onboarding plan or journey assigned to the new hire.
Description Organizations often have different onboarding programs, such as 'Standard Hire', 'Executive', 'Intern', or 'Rehire'. This attribute categorizes the onboarding case based on the assigned plan. This is a powerful dimension for analysis, as it allows for direct comparison between different onboarding programs. It can help answer questions like 'Is our executive onboarding process faster than our standard process?' and is fundamental to understanding process variants. Why it matters Categorizes onboarding cases by the type of program, enabling direct comparison of different onboarding paths and experiences. Where to get This would be determined by the specific onboarding checklist or journey template applied to the new hire in Oracle HCM Onboarding. Examples Standard Corporate HireExecutive OnboardingCampus HireInternal Transfer
	Required Training Status RequiredTrainingStatus	The current status of the new hire's mandatory training assignments.
Description This attribute tracks the progress of mandatory training, with possible values like 'Assigned', 'In Progress', or 'Completed'. It provides a snapshot of the new hire's compliance readiness. This status is crucial for the 'Mandatory Training Completion Rates' dashboard, allowing HR and compliance teams to monitor progress and intervene if training is overdue. It provides a more granular view than just the completion event itself. Why it matters Provides a clear status on compliance training, helping to monitor progress and ensure timely completion of mandatory requirements. Where to get This information is likely available in the Oracle Learning Cloud module or a similar training module integrated with Oracle HCM. Examples AssignedIn ProgressCompletedOverdue
	Source System SourceSystem	The system of record from which the onboarding data was extracted.
Description This attribute identifies the origin of the data, which is typically 'Oracle HCM Onboarding' for this process. It can be a static value added during data transformation. In environments with multiple integrated systems, this field is crucial for data governance and for understanding the context of the events. It helps differentiate onboarding activities that might occur in different platforms, such as a separate background check vendor system. Why it matters Provides essential context about data origin, which is important for data governance and troubleshooting in multi-system environments. Where to get This is often a static value added during the data extraction and transformation (ETL) process to label the dataset. Examples Oracle HCM OnboardingHCM_PRODOracleCloudHCM
	Total Onboarding Cycle Time TotalOnboardingCycleTime	The total time from when an offer is accepted until the 30-day check-in is completed.
Description This is a case-level metric that measures the end-to-end duration of the core onboarding process. It is calculated as the time difference between the first activity ('Offer Accepted') and a key integration milestone ('30-Day Check-in Performed'). This attribute is the primary measure for the 'Average Onboarding Cycle Time' KPI and the 'Overall Onboarding Cycle Time' dashboard. It provides a high-level summary of process efficiency and is a key indicator of the new hire's time-to-productivity. Why it matters Measures the end-to-end efficiency of the onboarding process, directly impacting how quickly a new hire becomes a fully integrated and productive employee. Where to get This is a calculated metric, derived from the timestamps of the 'Offer Accepted' and '30-Day Check-in Performed' activities for each Onboarding Case. Examples 345600043200002592000

Required Recommended Optional

Employee Onboarding Activities

These are the key process steps and milestones to capture in your event log for accurate discovery of your onboarding journey.

6 Recommended 10 Optional

	Activity	Description
	30-Day Check-in Performed	This activity marks the completion of the first formal check-in between the new hire and their manager, typically after 30 days. It is captured when the manager completes the corresponding task in the onboarding journey.
Why it matters This serves as a key milestone indicating the end of the initial integration phase. It is often used as the end point for measuring the overall onboarding cycle time and effectiveness. Where to get Capture the completion timestamp of the '30-Day Check-in' task within the new hire's Onboarding Journey in Oracle HCM. Capture Timestamp of the 30-day check-in task being marked as 'Completed'. Event type inferred
	Background Check Completed	Marks the completion of the background screening process. This event is typically recorded when the corresponding onboarding task is marked as complete, either manually or through an automated integration.
Why it matters This activity unblocks subsequent steps and is a key milestone for meeting compliance requirements. The duration between initiation and completion is a critical KPI. Where to get Capture the completion timestamp of the 'Background Check' task within the employee's assigned Onboarding Journey in Oracle HCM. Capture Timestamp of the background check task status change to 'Completed' or 'Closed'. Event type inferred
	HR Profile Created	This activity marks the creation of the official employee record in the core HR system, converting the candidate to an employee. This is a fundamental event logged in Oracle HCM when a new person record is created.
Why it matters A timely HR profile is critical for enabling downstream activities like IT provisioning and payroll setup. Delays here have a cascading negative impact on the entire process. Where to get Captured from the creation date of the person record (PER_ALL_PEOPLE_F table) in Oracle Fusion HCM, linked to the onboarding case. Capture Use the CREATION_DATE field from the core HR person record table. Event type explicit
	Mandatory Training Completed	Marks the point when a new hire successfully completes an assigned mandatory training module. This event is recorded automatically within Oracle Learning Cloud upon course completion.
Why it matters Crucial for compliance and audit purposes. Comparing this event's timestamp to due dates allows for monitoring on-time completion rates. Where to get This is an explicit event captured from the completion date of the enrollment record in the Oracle Learning Cloud module. Capture Timestamp of the learning enrollment status changing to 'Completed'. Event type explicit
	Offer Accepted	This activity marks the official start of the onboarding process, occurring when a candidate formally accepts the job offer. This is typically captured when the candidate's status is updated to 'Offer Accepted' or a similar state within the Oracle Recruiting or Onboarding module.
Why it matters This is the critical starting point for every onboarding case. Analyzing the time from this event to others reveals the total pre-boarding and onboarding cycle time. Where to get This event is typically inferred from a status change on the candidate's job application record in Oracle Recruiting Cloud, which integrates with the Onboarding module. Capture Identify the timestamp when the candidate's application status changes to 'Offer Accepted' or equivalent. Event type inferred
	Pre-Employment Paperwork Completed	Represents the moment the new hire has successfully completed and submitted all required pre-employment documents. This is captured when the document-related tasks in the onboarding journey are marked as complete.
Why it matters Ensures compliance and readiness for the new hire's start date. The time between sending and completion highlights bottlenecks in the new hire's response or the system's usability. Where to get Capture the completion timestamp of the relevant document collection tasks within the employee's Onboarding Journey in Oracle HCM. Capture Timestamp when all required document tasks change status to 'Completed'. Event type inferred
	Background Check Initiated	Represents the point at which the background check process is started for the new hire, often via an integration with a third-party vendor. This is usually recorded as the start of a specific task within the new hire's onboarding journey or checklist.
Why it matters The duration of background checks is a common bottleneck. Tracking its start and end points is crucial for identifying delays that can postpone the official start date. Where to get Capture the creation or initiation timestamp of the 'Background Check' task within the employee's assigned Onboarding Journey in Oracle HCM. Capture Timestamp of the background check task creation or status change to 'In Progress'. Event type inferred
	Equipment Delivered	Marks the confirmation that the new hire's equipment has been successfully delivered. This is typically captured by the manual completion of an 'Equipment Delivered' task in the onboarding journey by an IT or facilities team member.
Why it matters This is a key milestone for first-day readiness. The cycle time from 'Equipment Ordered' to this activity is a critical KPI for IT and facilities performance. Where to get Capture the completion timestamp of the 'Confirm Equipment Delivery' task within the new hire's Onboarding Journey in Oracle HCM. Capture Timestamp of status change to 'Completed' for the equipment delivery confirmation task. Event type inferred
	Equipment Ordered	This activity is recorded when a request for the new hire's equipment, such as a laptop or phone, is officially submitted. This is often a task within the onboarding journey that, when completed, signifies the order has been placed.
Why it matters Timely equipment ordering is critical for ensuring a new hire is 'first day ready'. This activity helps measure how quickly procurement processes are initiated. Where to get Capture the completion timestamp of an 'Order Equipment' task in the Onboarding Journey, or an integration event with a procurement system. Capture Timestamp of status change to 'Completed' for the equipment ordering task. Event type inferred
	First Day Orientation Conducted	Represents the completion of the new hire's first-day orientation session. This is typically a checklist item in the onboarding journey that is marked as complete by an HR representative or the hiring manager.
Why it matters Ensures that all new hires receive a consistent and complete orientation experience. Tracking this helps monitor the standardization of the onboarding process. Where to get Capture the completion timestamp of the 'First Day Orientation' task within the employee's Onboarding Journey. Capture Timestamp when the orientation task status is updated to 'Completed'. Event type inferred
	IT Account Provisioning Initiated	Represents the start of the IT account creation process for the new hire. This is typically captured when a specific task for IT provisioning is created or triggered within the onboarding journey.
Why it matters This activity is crucial for ensuring the new hire has system access on their first day. Tracking its initiation helps measure the efficiency of IT fulfillment. Where to get Capture the creation or initiation timestamp of the 'IT Provisioning' task within the new hire's Onboarding Journey. Capture Timestamp of task creation or status change to 'In Progress' for IT-related tasks. Event type inferred
	Mandatory Training Assigned	This activity occurs when required compliance or role-based training is assigned to the new hire. This is often captured via an integration with Oracle Learning Cloud, where enrollments are created.
Why it matters This is the starting point for tracking compliance training. It helps ensure that training is assigned promptly after the hire date. Where to get This event can be captured from the enrollment creation date in the Oracle Learning Cloud module, which is part of the HCM suite. Capture Timestamp of the learning item enrollment record creation. Event type explicit
	Onboarding Completed	Represents the formal closure of the entire onboarding process for a new hire. This is captured when the overall status of the onboarding journey or case is set to 'Completed' in the system.
Why it matters This activity provides a definitive end point for the process, allowing for accurate measurement of total cycle time and throughput. Where to get Inferred from the status of the overall allocated journey or checklist for the employee changing to 'Completed' in the PER_ALLOCATED_CHECKLISTS table. Capture Timestamp when the journey's master status is updated to 'Completed'. Event type inferred
	Onboarding Journey Assigned	Occurs when an HR administrator assigns a specific onboarding journey or checklist to the new hire. This journey contains the sequence of tasks the new hire and other stakeholders must complete.
Why it matters This formalizes the start of the guided onboarding process within the system. Analyzing this helps understand if processes are standardized and initiated promptly. Where to get This is an explicit event captured in the Journey management tables (PER_ALLOCATED_CHECKLISTS) when a checklist is allocated to a person. Capture Timestamp of the record creation in the table that manages assigned journeys or checklists. Event type explicit
	Pre-Employment Paperwork Sent	This activity occurs when required pre-employment documents, such as contracts and policy acknowledgements, are sent to the new hire. This is often an automated step within an onboarding journey that distributes electronic documents for signature.
Why it matters Tracking when paperwork is sent is the first step in measuring the efficiency of the document management process and identifying delays. Where to get This is often an explicit event or task creation logged within the Oracle Onboarding checklist or Journey when documents are dispatched to the new hire. Capture Event log or creation timestamp for the 'Distribute Documents' task. Event type explicit
	Team Introduction Completed	Represents the formal introduction of the new hire to their team. This is typically a soft task on the onboarding checklist that is marked as complete by the hiring manager.
Why it matters This activity is an indicator of social integration, which is important for engagement and retention. Tracking its completion helps ensure managers are fulfilling this key responsibility. Where to get Capture the completion timestamp of the 'Introduce New Hire to Team' task within the Onboarding Journey. Capture Timestamp of the team introduction task status changing to 'Completed'. Event type inferred

Recommended Optional

Extraction Guides

How to get your data from Oracle HCM Onboarding

Steps

Navigate to Oracle Transactional Business Intelligence (OTBI): Log in to your Oracle HCM Cloud instance. Use the Navigator menu to go to Tools, then select Reports and Analytics. This will open the Business Intelligence Catalog.
Create a New Analysis: Within the BI Catalog, click the "Create" button in the toolbar and select "Analysis" from the dropdown menu. This will prompt you to choose a subject area.
Select the Subject Area: In the "Select Subject Area" dialog, search for and choose "Recruiting - Onboarding Real Time". This subject area contains the necessary data for tracking the employee onboarding process.
Access the Advanced SQL Editor: After selecting the subject area, you will be taken to the analysis editor. Instead of dragging and dropping columns in the Criteria tab, navigate to the "Advanced" tab. This allows you to input a custom Logical SQL query.
Paste the Logical SQL Query: In the SQL editor window under the "Advanced" tab, copy and paste the complete Logical SQL provided in the "query" section of this document. This query is designed to union data from multiple sources to create a comprehensive event log.
Set Query Filters: The provided query includes placeholders like [START_DATE] and [END_DATE]. It is highly recommended to use the "Filters" section in the analysis builder to create prompts for these dates. This allows users to specify a date range when running the report, improving performance. Create a filter on the "Onboarding Process Details"."Process Creation Date" column and set the operator to "is between".
Review and Save the Analysis: Navigate to the "Results" tab to preview the data. Ensure that columns like OnboardingCase, ActivityName, and EventTime are populated correctly. If the results look correct, save the analysis in a suitable shared folder, giving it a descriptive name like "Process Mining - Employee Onboarding Event Log".
Run and Export the Report: Execute the saved analysis for the desired date range. Once the report has finished running, use the "Export" option located at the bottom of the results page.
Format for Process Mining: Choose to export the data in either "CSV" or "XML" format. The CSV file is generally preferred for its simplicity. Ensure the exported file headers match the required attributes for your process mining tool. No further data transformation should be needed if the SQL query is used as provided.

Configuration

Subject Area: The analysis must use the "Recruiting - Onboarding Real Time" subject area. This is the primary source for onboarding process and task data in Oracle HCM.
Date Range Filters: It is critical to apply a date range filter to manage performance and data volume. A recommended filter is on "Onboarding Process Details"."Process Creation Date" for a lookback period of 3 to 6 months. For very high volume organizations, a shorter period may be necessary.
Data Security: The data returned by the report is subject to the user's security profile. Ensure the user running the report has the necessary permissions to view all relevant onboarding data across different business units and legal entities.
Onboarding Process Status: Consider adding a filter for "Onboarding Process Details"."Process Status" to include only relevant cases, such as 'Completed', 'In Progress', and 'Pending'. This can help exclude canceled or erroneous onboarding processes.
Prerequisites: The user creating and running the report must have a role with BI Author privileges, such as "Human Capital Management Application Administrator" or a custom role with equivalent access to OTBI and the required subject areas.

a Sample Query config

SELECT
    "Recruiting - Onboarding Real Time"."Onboarding Process Details"."Onboarding Process Identifier" AS OnboardingCase,
    'Offer Accepted' AS ActivityName,
    CAST("Recruiting - Onboarding Real Time"."Offer"."Accepted On Date" AS TIMESTAMP) AS EventTime,
    NULL AS EventEndTime,
    "Recruiting - Onboarding Real Time"."Offer"."Accepted By Person Name" AS PerformedByUser,
    "Recruiting - Onboarding Real Time"."New Hire"."Department Name" AS NewHireDepartment,
    "Recruiting - Onboarding Real Time"."New Hire"."Job Name" AS NewHireJobTitle,
    CAST("Recruiting - Onboarding Real Time"."New Hire"."Start Date" AS DATE) AS OfficialStartDate,
    NULL AS ComplianceDueDate,
    "Recruiting - Onboarding Real Time"."New Hire"."Manager Name" AS HiringManager,
    NULL AS ActivityDuration
FROM "Recruiting - Onboarding Real Time" WHERE "Recruiting - Onboarding Real Time"."Offer"."Accepted On Date" IS NOT NULL

UNION ALL

SELECT
    T.OnboardingCase, T.ActivityName, T.EventTime, T.EventEndTime, T.PerformedByUser, T.NewHireDepartment, T.NewHireJobTitle, T.OfficialStartDate, T.ComplianceDueDate, T.HiringManager, T.ActivityDuration
FROM (
SELECT
    "Recruiting - Onboarding Real Time"."Onboarding Process Details"."Onboarding Process Identifier" AS OnboardingCase,
    CASE 
        WHEN "Recruiting - Onboarding Real Time"."Onboarding Task"."Task Name" LIKE '%Background Check%' AND "Recruiting - Onboarding Real Time"."Onboarding Task"."Actual Start Date" IS NOT NULL THEN 'Background Check Initiated'
        WHEN "Recruiting - Onboarding Real Time"."Onboarding Task"."Task Name" LIKE '%Background Check%' AND "Recruiting - Onboarding Real Time"."Onboarding Task"."Actual Completion Date" IS NOT NULL THEN 'Background Check Completed'
        WHEN "Recruiting - Onboarding Real Time"."Onboarding Task"."Task Name" LIKE '%Paperwork%' AND "Recruiting - Onboarding Real Time"."Onboarding Task"."Task Type" = 'Configurable Form' AND "Recruiting - Onboarding Real Time"."Onboarding Task"."Actual Start Date" IS NOT NULL THEN 'Pre-Employment Paperwork Sent'
        WHEN "Recruiting - Onboarding Real Time"."Onboarding Task"."Task Name" LIKE '%Paperwork%' AND "Recruiting - Onboarding Real Time"."Onboarding Task"."Task Type" = 'Configurable Form' AND "Recruiting - Onboarding Real Time"."Onboarding Task"."Actual Completion Date" IS NOT NULL THEN 'Pre-Employment Paperwork Completed'
        WHEN "Recruiting - Onboarding Real Time"."Onboarding Task"."Task Name" LIKE '%IT Account%' AND "Recruiting - Onboarding Real Time"."Onboarding Task"."Actual Start Date" IS NOT NULL THEN 'IT Account Provisioning Initiated'
        WHEN "Recruiting - Onboarding Real Time"."Onboarding Task"."Task Name" LIKE '%Equipment Order%' AND "Recruiting - Onboarding Real Time"."Onboarding Task"."Actual Completion Date" IS NOT NULL THEN 'Equipment Ordered'
        WHEN "Recruiting - Onboarding Real Time"."Onboarding Task"."Task Name" LIKE '%Equipment Delivered%' AND "Recruiting - Onboarding Real Time"."Onboarding Task"."Actual Completion Date" IS NOT NULL THEN 'Equipment Delivered'
        WHEN "Recruiting - Onboarding Real Time"."Onboarding Task"."Task Name" LIKE '%Orientation%' AND "Recruiting - Onboarding Real Time"."Onboarding Task"."Actual Completion Date" IS NOT NULL THEN 'First Day Orientation Conducted'
        WHEN "Recruiting - Onboarding Real Time"."Onboarding Task"."Task Name" LIKE '%Training%' AND "Recruiting - Onboarding Real Time"."Onboarding Task"."Task Type" = 'Learning' AND "Recruiting - Onboarding Real Time"."Onboarding Task"."Actual Start Date" IS NOT NULL THEN 'Mandatory Training Assigned'
        WHEN "Recruiting - Onboarding Real Time"."Onboarding Task"."Task Name" LIKE '%Training%' AND "Recruiting - Onboarding Real Time"."Onboarding Task"."Task Type" = 'Learning' AND "Recruiting - Onboarding Real Time"."Onboarding Task"."Actual Completion Date" IS NOT NULL THEN 'Mandatory Training Completed'
        WHEN "Recruiting - Onboarding Real Time"."Onboarding Task"."Task Name" LIKE '%Team Introduction%' AND "Recruiting - Onboarding Real Time"."Onboarding Task"."Actual Completion Date" IS NOT NULL THEN 'Team Introduction Completed'
        WHEN "Recruiting - Onboarding Real Time"."Onboarding Task"."Task Name" LIKE '%30-Day Check-in%' AND "Recruiting - Onboarding Real Time"."Onboarding Task"."Actual Completion Date" IS NOT NULL THEN '30-Day Check-in Performed'
        ELSE NULL
    END AS ActivityName,
    CASE
        WHEN "Recruiting - Onboarding Real Time"."Onboarding Task"."Task Name" LIKE '%Background Check%' AND "Recruiting - Onboarding Real Time"."Onboarding Task"."Actual Start Date" IS NOT NULL THEN CAST("Recruiting - Onboarding Real Time"."Onboarding Task"."Actual Start Date" AS TIMESTAMP)
        WHEN "Recruiting - Onboarding Real Time"."Onboarding Task"."Task Name" LIKE '%Paperwork%' AND "Recruiting - Onboarding Real Time"."Onboarding Task"."Task Type" = 'Configurable Form' AND "Recruiting - Onboarding Real Time"."Onboarding Task"."Actual Start Date" IS NOT NULL THEN CAST("Recruiting - Onboarding Real Time"."Onboarding Task"."Actual Start Date" AS TIMESTAMP)
        WHEN "Recruiting - Onboarding Real Time"."Onboarding Task"."Task Name" LIKE '%IT Account%' AND "Recruiting - Onboarding Real Time"."Onboarding Task"."Actual Start Date" IS NOT NULL THEN CAST("Recruiting - Onboarding Real Time"."Onboarding Task"."Actual Start Date" AS TIMESTAMP)
        WHEN "Recruiting - Onboarding Real Time"."Onboarding Task"."Task Name" LIKE '%Training%' AND "Recruiting - Onboarding Real Time"."Onboarding Task"."Task Type" = 'Learning' AND "Recruiting - Onboarding Real Time"."Onboarding Task"."Actual Start Date" IS NOT NULL THEN CAST("Recruiting - Onboarding Real Time"."Onboarding Task"."Actual Start Date" AS TIMESTAMP)
        ELSE CAST("Recruiting - Onboarding Real Time"."Onboarding Task"."Actual Completion Date" AS TIMESTAMP)
    END AS EventTime,
    CAST("Recruiting - Onboarding Real Time"."Onboarding Task"."Actual Completion Date" AS TIMESTAMP) AS EventEndTime,
    "Recruiting - Onboarding Real Time"."Onboarding Task"."Completed By Display Name" AS PerformedByUser,
    "Recruiting - Onboarding Real Time"."New Hire"."Department Name" AS NewHireDepartment,
    "Recruiting - Onboarding Real Time"."New Hire"."Job Name" AS NewHireJobTitle,
    CAST("Recruiting - Onboarding Real Time"."New Hire"."Start Date" AS DATE) AS OfficialStartDate,
    CAST("Recruiting - Onboarding Real Time"."Onboarding Task"."Due Date" AS DATE) AS ComplianceDueDate,
    "Recruiting - Onboarding Real Time"."New Hire"."Manager Name" AS HiringManager,
    TIMESTAMPDIFF(SQL_TSI_DAY, "Recruiting - Onboarding Real Time"."Onboarding Task"."Actual Start Date", "Recruiting - Onboarding Real Time"."Onboarding Task"."Actual Completion Date") AS ActivityDuration
FROM "Recruiting - Onboarding Real Time"
) T WHERE T.ActivityName IS NOT NULL

UNION ALL

SELECT
    "Recruiting - Onboarding Real Time"."Onboarding Process Details"."Onboarding Process Identifier" AS OnboardingCase,
    'HR Profile Created' AS ActivityName,
    CAST("Recruiting - Onboarding Real Time"."New Hire"."Creation Date" AS TIMESTAMP) AS EventTime,
    NULL AS EventEndTime,
    "Recruiting - Onboarding Real Time"."New Hire"."Created By" AS PerformedByUser,
    "Recruiting - Onboarding Real Time"."New Hire"."Department Name" AS NewHireDepartment,
    "Recruiting - Onboarding Real Time"."New Hire"."Job Name" AS NewHireJobTitle,
    CAST("Recruiting - Onboarding Real Time"."New Hire"."Start Date" AS DATE) AS OfficialStartDate,
    NULL AS ComplianceDueDate,
    "Recruiting - Onboarding Real Time"."New Hire"."Manager Name" AS HiringManager,
    NULL AS ActivityDuration
FROM "Recruiting - Onboarding Real Time" WHERE "Recruiting - Onboarding Real Time"."New Hire"."Creation Date" IS NOT NULL

UNION ALL

SELECT
    "Recruiting - Onboarding Real Time"."Onboarding Process Details"."Onboarding Process Identifier" AS OnboardingCase,
    'Onboarding Journey Assigned' AS ActivityName,
    CAST("Recruiting - Onboarding Real Time"."Onboarding Process Details"."Process Creation Date" AS TIMESTAMP) AS EventTime,
    NULL AS EventEndTime,
    "Recruiting - Onboarding Real Time"."Onboarding Process Details"."Process Created By" AS PerformedByUser,
    "Recruiting - Onboarding Real Time"."New Hire"."Department Name" AS NewHireDepartment,
    "Recruiting - Onboarding Real Time"."New Hire"."Job Name" AS NewHireJobTitle,
    CAST("Recruiting - Onboarding Real Time"."New Hire"."Start Date" AS DATE) AS OfficialStartDate,
    NULL AS ComplianceDueDate,
    "Recruiting - Onboarding Real Time"."New Hire"."Manager Name" AS HiringManager,
    NULL AS ActivityDuration
FROM "Recruiting - Onboarding Real Time"

UNION ALL

SELECT
    "Recruiting - Onboarding Real Time"."Onboarding Process Details"."Onboarding Process Identifier" AS OnboardingCase,
    'Onboarding Completed' AS ActivityName,
    CAST("Recruiting - Onboarding Real Time"."Onboarding Process Details"."Process Completion Date" AS TIMESTAMP) AS EventTime,
    NULL AS EventEndTime,
    "Recruiting - Onboarding Real Time"."Onboarding Process Details"."Process Last Updated By" AS PerformedByUser,
    "Recruiting - Onboarding Real Time"."New Hire"."Department Name" AS NewHireDepartment,
    "Recruiting - Onboarding Real Time"."New Hire"."Job Name" AS NewHireJobTitle,
    CAST("Recruiting - Onboarding Real Time"."New Hire"."Start Date" AS DATE) AS OfficialStartDate,
    NULL AS ComplianceDueDate,
    "Recruiting - Onboarding Real Time"."New Hire"."Manager Name" AS HiringManager,
    NULL AS ActivityDuration
FROM "Recruiting - Onboarding Real Time" WHERE "Recruiting - Onboarding Real Time"."Onboarding Process Details"."Process Status" = 'Completed'

FETCH FIRST 65000 ROWS ONLY

Steps

Navigate to Oracle Transactional Business Intelligence (OTBI): Log in to your Oracle HCM Cloud instance. Use the Navigator menu to go to Tools, then select Reports and Analytics. This will open the Business Intelligence Catalog.
Create a New Analysis: Within the BI Catalog, click the "Create" button in the toolbar and select "Analysis" from the dropdown menu. This will prompt you to choose a subject area.
Select the Subject Area: In the "Select Subject Area" dialog, search for and choose "Recruiting - Onboarding Real Time". This subject area contains the necessary data for tracking the employee onboarding process.
Access the Advanced SQL Editor: After selecting the subject area, you will be taken to the analysis editor. Instead of dragging and dropping columns in the Criteria tab, navigate to the "Advanced" tab. This allows you to input a custom Logical SQL query.
Paste the Logical SQL Query: In the SQL editor window under the "Advanced" tab, copy and paste the complete Logical SQL provided in the "query" section of this document. This query is designed to union data from multiple sources to create a comprehensive event log.
Set Query Filters: The provided query includes placeholders like [START_DATE] and [END_DATE]. It is highly recommended to use the "Filters" section in the analysis builder to create prompts for these dates. This allows users to specify a date range when running the report, improving performance. Create a filter on the "Onboarding Process Details"."Process Creation Date" column and set the operator to "is between".
Review and Save the Analysis: Navigate to the "Results" tab to preview the data. Ensure that columns like OnboardingCase, ActivityName, and EventTime are populated correctly. If the results look correct, save the analysis in a suitable shared folder, giving it a descriptive name like "Process Mining - Employee Onboarding Event Log".
Run and Export the Report: Execute the saved analysis for the desired date range. Once the report has finished running, use the "Export" option located at the bottom of the results page.
Format for Process Mining: Choose to export the data in either "CSV" or "XML" format. The CSV file is generally preferred for its simplicity. Ensure the exported file headers match the required attributes for your process mining tool. No further data transformation should be needed if the SQL query is used as provided.

Configuration

Subject Area: The analysis must use the "Recruiting - Onboarding Real Time" subject area. This is the primary source for onboarding process and task data in Oracle HCM.
Date Range Filters: It is critical to apply a date range filter to manage performance and data volume. A recommended filter is on "Onboarding Process Details"."Process Creation Date" for a lookback period of 3 to 6 months. For very high volume organizations, a shorter period may be necessary.
Data Security: The data returned by the report is subject to the user's security profile. Ensure the user running the report has the necessary permissions to view all relevant onboarding data across different business units and legal entities.
Onboarding Process Status: Consider adding a filter for "Onboarding Process Details"."Process Status" to include only relevant cases, such as 'Completed', 'In Progress', and 'Pending'. This can help exclude canceled or erroneous onboarding processes.
Prerequisites: The user creating and running the report must have a role with BI Author privileges, such as "Human Capital Management Application Administrator" or a custom role with equivalent access to OTBI and the required subject areas.

a Sample Query config

SELECT
    "Recruiting - Onboarding Real Time"."Onboarding Process Details"."Onboarding Process Identifier" AS OnboardingCase,
    'Offer Accepted' AS ActivityName,
    CAST("Recruiting - Onboarding Real Time"."Offer"."Accepted On Date" AS TIMESTAMP) AS EventTime,
    NULL AS EventEndTime,
    "Recruiting - Onboarding Real Time"."Offer"."Accepted By Person Name" AS PerformedByUser,
    "Recruiting - Onboarding Real Time"."New Hire"."Department Name" AS NewHireDepartment,
    "Recruiting - Onboarding Real Time"."New Hire"."Job Name" AS NewHireJobTitle,
    CAST("Recruiting - Onboarding Real Time"."New Hire"."Start Date" AS DATE) AS OfficialStartDate,
    NULL AS ComplianceDueDate,
    "Recruiting - Onboarding Real Time"."New Hire"."Manager Name" AS HiringManager,
    NULL AS ActivityDuration
FROM "Recruiting - Onboarding Real Time" WHERE "Recruiting - Onboarding Real Time"."Offer"."Accepted On Date" IS NOT NULL

UNION ALL

SELECT
    T.OnboardingCase, T.ActivityName, T.EventTime, T.EventEndTime, T.PerformedByUser, T.NewHireDepartment, T.NewHireJobTitle, T.OfficialStartDate, T.ComplianceDueDate, T.HiringManager, T.ActivityDuration
FROM (
SELECT
    "Recruiting - Onboarding Real Time"."Onboarding Process Details"."Onboarding Process Identifier" AS OnboardingCase,
    CASE 
        WHEN "Recruiting - Onboarding Real Time"."Onboarding Task"."Task Name" LIKE '%Background Check%' AND "Recruiting - Onboarding Real Time"."Onboarding Task"."Actual Start Date" IS NOT NULL THEN 'Background Check Initiated'
        WHEN "Recruiting - Onboarding Real Time"."Onboarding Task"."Task Name" LIKE '%Background Check%' AND "Recruiting - Onboarding Real Time"."Onboarding Task"."Actual Completion Date" IS NOT NULL THEN 'Background Check Completed'
        WHEN "Recruiting - Onboarding Real Time"."Onboarding Task"."Task Name" LIKE '%Paperwork%' AND "Recruiting - Onboarding Real Time"."Onboarding Task"."Task Type" = 'Configurable Form' AND "Recruiting - Onboarding Real Time"."Onboarding Task"."Actual Start Date" IS NOT NULL THEN 'Pre-Employment Paperwork Sent'
        WHEN "Recruiting - Onboarding Real Time"."Onboarding Task"."Task Name" LIKE '%Paperwork%' AND "Recruiting - Onboarding Real Time"."Onboarding Task"."Task Type" = 'Configurable Form' AND "Recruiting - Onboarding Real Time"."Onboarding Task"."Actual Completion Date" IS NOT NULL THEN 'Pre-Employment Paperwork Completed'
        WHEN "Recruiting - Onboarding Real Time"."Onboarding Task"."Task Name" LIKE '%IT Account%' AND "Recruiting - Onboarding Real Time"."Onboarding Task"."Actual Start Date" IS NOT NULL THEN 'IT Account Provisioning Initiated'
        WHEN "Recruiting - Onboarding Real Time"."Onboarding Task"."Task Name" LIKE '%Equipment Order%' AND "Recruiting - Onboarding Real Time"."Onboarding Task"."Actual Completion Date" IS NOT NULL THEN 'Equipment Ordered'
        WHEN "Recruiting - Onboarding Real Time"."Onboarding Task"."Task Name" LIKE '%Equipment Delivered%' AND "Recruiting - Onboarding Real Time"."Onboarding Task"."Actual Completion Date" IS NOT NULL THEN 'Equipment Delivered'
        WHEN "Recruiting - Onboarding Real Time"."Onboarding Task"."Task Name" LIKE '%Orientation%' AND "Recruiting - Onboarding Real Time"."Onboarding Task"."Actual Completion Date" IS NOT NULL THEN 'First Day Orientation Conducted'
        WHEN "Recruiting - Onboarding Real Time"."Onboarding Task"."Task Name" LIKE '%Training%' AND "Recruiting - Onboarding Real Time"."Onboarding Task"."Task Type" = 'Learning' AND "Recruiting - Onboarding Real Time"."Onboarding Task"."Actual Start Date" IS NOT NULL THEN 'Mandatory Training Assigned'
        WHEN "Recruiting - Onboarding Real Time"."Onboarding Task"."Task Name" LIKE '%Training%' AND "Recruiting - Onboarding Real Time"."Onboarding Task"."Task Type" = 'Learning' AND "Recruiting - Onboarding Real Time"."Onboarding Task"."Actual Completion Date" IS NOT NULL THEN 'Mandatory Training Completed'
        WHEN "Recruiting - Onboarding Real Time"."Onboarding Task"."Task Name" LIKE '%Team Introduction%' AND "Recruiting - Onboarding Real Time"."Onboarding Task"."Actual Completion Date" IS NOT NULL THEN 'Team Introduction Completed'
        WHEN "Recruiting - Onboarding Real Time"."Onboarding Task"."Task Name" LIKE '%30-Day Check-in%' AND "Recruiting - Onboarding Real Time"."Onboarding Task"."Actual Completion Date" IS NOT NULL THEN '30-Day Check-in Performed'
        ELSE NULL
    END AS ActivityName,
    CASE
        WHEN "Recruiting - Onboarding Real Time"."Onboarding Task"."Task Name" LIKE '%Background Check%' AND "Recruiting - Onboarding Real Time"."Onboarding Task"."Actual Start Date" IS NOT NULL THEN CAST("Recruiting - Onboarding Real Time"."Onboarding Task"."Actual Start Date" AS TIMESTAMP)
        WHEN "Recruiting - Onboarding Real Time"."Onboarding Task"."Task Name" LIKE '%Paperwork%' AND "Recruiting - Onboarding Real Time"."Onboarding Task"."Task Type" = 'Configurable Form' AND "Recruiting - Onboarding Real Time"."Onboarding Task"."Actual Start Date" IS NOT NULL THEN CAST("Recruiting - Onboarding Real Time"."Onboarding Task"."Actual Start Date" AS TIMESTAMP)
        WHEN "Recruiting - Onboarding Real Time"."Onboarding Task"."Task Name" LIKE '%IT Account%' AND "Recruiting - Onboarding Real Time"."Onboarding Task"."Actual Start Date" IS NOT NULL THEN CAST("Recruiting - Onboarding Real Time"."Onboarding Task"."Actual Start Date" AS TIMESTAMP)
        WHEN "Recruiting - Onboarding Real Time"."Onboarding Task"."Task Name" LIKE '%Training%' AND "Recruiting - Onboarding Real Time"."Onboarding Task"."Task Type" = 'Learning' AND "Recruiting - Onboarding Real Time"."Onboarding Task"."Actual Start Date" IS NOT NULL THEN CAST("Recruiting - Onboarding Real Time"."Onboarding Task"."Actual Start Date" AS TIMESTAMP)
        ELSE CAST("Recruiting - Onboarding Real Time"."Onboarding Task"."Actual Completion Date" AS TIMESTAMP)
    END AS EventTime,
    CAST("Recruiting - Onboarding Real Time"."Onboarding Task"."Actual Completion Date" AS TIMESTAMP) AS EventEndTime,
    "Recruiting - Onboarding Real Time"."Onboarding Task"."Completed By Display Name" AS PerformedByUser,
    "Recruiting - Onboarding Real Time"."New Hire"."Department Name" AS NewHireDepartment,
    "Recruiting - Onboarding Real Time"."New Hire"."Job Name" AS NewHireJobTitle,
    CAST("Recruiting - Onboarding Real Time"."New Hire"."Start Date" AS DATE) AS OfficialStartDate,
    CAST("Recruiting - Onboarding Real Time"."Onboarding Task"."Due Date" AS DATE) AS ComplianceDueDate,
    "Recruiting - Onboarding Real Time"."New Hire"."Manager Name" AS HiringManager,
    TIMESTAMPDIFF(SQL_TSI_DAY, "Recruiting - Onboarding Real Time"."Onboarding Task"."Actual Start Date", "Recruiting - Onboarding Real Time"."Onboarding Task"."Actual Completion Date") AS ActivityDuration
FROM "Recruiting - Onboarding Real Time"
) T WHERE T.ActivityName IS NOT NULL

UNION ALL

SELECT
    "Recruiting - Onboarding Real Time"."Onboarding Process Details"."Onboarding Process Identifier" AS OnboardingCase,
    'HR Profile Created' AS ActivityName,
    CAST("Recruiting - Onboarding Real Time"."New Hire"."Creation Date" AS TIMESTAMP) AS EventTime,
    NULL AS EventEndTime,
    "Recruiting - Onboarding Real Time"."New Hire"."Created By" AS PerformedByUser,
    "Recruiting - Onboarding Real Time"."New Hire"."Department Name" AS NewHireDepartment,
    "Recruiting - Onboarding Real Time"."New Hire"."Job Name" AS NewHireJobTitle,
    CAST("Recruiting - Onboarding Real Time"."New Hire"."Start Date" AS DATE) AS OfficialStartDate,
    NULL AS ComplianceDueDate,
    "Recruiting - Onboarding Real Time"."New Hire"."Manager Name" AS HiringManager,
    NULL AS ActivityDuration
FROM "Recruiting - Onboarding Real Time" WHERE "Recruiting - Onboarding Real Time"."New Hire"."Creation Date" IS NOT NULL

UNION ALL

SELECT
    "Recruiting - Onboarding Real Time"."Onboarding Process Details"."Onboarding Process Identifier" AS OnboardingCase,
    'Onboarding Journey Assigned' AS ActivityName,
    CAST("Recruiting - Onboarding Real Time"."Onboarding Process Details"."Process Creation Date" AS TIMESTAMP) AS EventTime,
    NULL AS EventEndTime,
    "Recruiting - Onboarding Real Time"."Onboarding Process Details"."Process Created By" AS PerformedByUser,
    "Recruiting - Onboarding Real Time"."New Hire"."Department Name" AS NewHireDepartment,
    "Recruiting - Onboarding Real Time"."New Hire"."Job Name" AS NewHireJobTitle,
    CAST("Recruiting - Onboarding Real Time"."New Hire"."Start Date" AS DATE) AS OfficialStartDate,
    NULL AS ComplianceDueDate,
    "Recruiting - Onboarding Real Time"."New Hire"."Manager Name" AS HiringManager,
    NULL AS ActivityDuration
FROM "Recruiting - Onboarding Real Time"

UNION ALL

SELECT
    "Recruiting - Onboarding Real Time"."Onboarding Process Details"."Onboarding Process Identifier" AS OnboardingCase,
    'Onboarding Completed' AS ActivityName,
    CAST("Recruiting - Onboarding Real Time"."Onboarding Process Details"."Process Completion Date" AS TIMESTAMP) AS EventTime,
    NULL AS EventEndTime,
    "Recruiting - Onboarding Real Time"."Onboarding Process Details"."Process Last Updated By" AS PerformedByUser,
    "Recruiting - Onboarding Real Time"."New Hire"."Department Name" AS NewHireDepartment,
    "Recruiting - Onboarding Real Time"."New Hire"."Job Name" AS NewHireJobTitle,
    CAST("Recruiting - Onboarding Real Time"."New Hire"."Start Date" AS DATE) AS OfficialStartDate,
    NULL AS ComplianceDueDate,
    "Recruiting - Onboarding Real Time"."New Hire"."Manager Name" AS HiringManager,
    NULL AS ActivityDuration
FROM "Recruiting - Onboarding Real Time" WHERE "Recruiting - Onboarding Real Time"."Onboarding Process Details"."Process Status" = 'Completed'

FETCH FIRST 65000 ROWS ONLY

Steps

Navigate to BI Publisher: Log in to your Oracle Fusion Cloud HCM instance. Use the Navigator menu to go to Tools > Reports and Analytics. Click the 'Browse Catalog' button to open the BI Publisher interface.
Create a New Data Model: In the BI Publisher catalog, navigate to a custom folder, for example /Shared Folders/Custom/Process Mining. Click the 'New' icon and select 'Data Model'.
Define the SQL Query Data Set: In the Data Model editor, click the '+' icon and select 'SQL Query'. A new dialog will appear. Name the data set something descriptive, like 'OnboardingEventLog'. Select 'ApplicationDB_HCM' as the Data Source.
Enter the SQL Query: Copy the complete SQL query provided in the 'query' section of this document and paste it into the SQL Query text area.
Define Parameters: The query uses parameters :P_START_DATE and :P_END_DATE to filter the date range. In the Data Model editor, go to the 'Parameters' section. Create two new parameters named P_START_DATE and P_END_DATE. Set their data type to 'Date' and provide a user-friendly display label.
Save the Data Model: Review the data model settings and click the 'Save' icon. Save it in your custom folder with a descriptive name like 'Onboarding Event Log Data Model'. You can view sample data by clicking the 'Data' tab and entering dates before saving.
Create a New Report: Navigate back to the catalog. Click 'New' and select 'Report'. In the 'Create Report' wizard, select 'Use Data Model' and find the data model you just saved.
Configure the Report Layout: In the report wizard, choose a simple layout. Select the 'Table' layout and drag and drop all columns into the table. This ensures the output file contains all required data. Click 'Finish' and save the report with a name like 'Onboarding Event Log Report'.
Schedule the Report: View the newly created report and click 'Actions' > 'Schedule'. This will take you to the scheduling configuration page.
Configure Job Details: In the 'General' tab, set the output format to CSV. In the 'Output' tab, define the delivery destination. Common options include sending to a content server (UCM), an SFTP server, or email. The SFTP option is often preferred for automated ingestion.
Set the Schedule: In the 'Schedule' tab, define the frequency of the extraction. You can set it to run daily, weekly, or on another recurring basis. Set the start and end dates for the schedule itself. Ensure you populate the date parameters (P_START_DATE, P_END_DATE) for the report run.
Finalize and Monitor: Submit the scheduled job. You can monitor its execution from the 'Job History' page. The generated CSV file from this job is the final event log, ready to be uploaded to ProcessMind.

Configuration

Data Source: The query must be configured to use the ApplicationDB_HCM data source within BI Publisher to access live transactional HCM data.
Date Range Parameters: It is critical to use the :P_START_DATE and :P_END_DATE parameters when scheduling the report. Extracting data for a period of 3 to 6 months is recommended for an initial analysis, which helps balance data volume and process insight. For ongoing monitoring, schedule daily or weekly extractions for the preceding period.
Organizational Filters: The query can be extended with WHERE clauses to filter by specific Business Unit, Legal Entity, or Department. This is useful for phased rollouts or focusing the analysis on a particular part of the organization.
Performance Considerations: The UNION ALL structure combines multiple queries, which can be resource-intensive. For organizations with very high hiring volumes, schedule the report to run during off-peak hours to minimize impact on system performance. Consider adding indexes to key columns if performance is a significant issue.
Prerequisites: The user creating the data model and report must have a role with sufficient privileges, such as 'BI Administrator' or a custom BI role. This includes permissions to create objects in the BI catalog and access to the underlying HCM data via appropriate data security profiles.

a Sample Query sql

WITH OnboardingBase AS (
    SELECT
        pcb.person_id,
        pcb.checklist_id,
        ppnf.full_name AS new_hire_name,
        papf_mgr.full_name AS hiring_manager_name,
        paam.assignment_id,
        paam.effective_start_date AS official_start_date,
        pjt.name AS job_title,
        hout.name AS department_name
    FROM
        ORA_CHECKLISTS_B pcb
        JOIN PER_ALL_ASSIGNMENTS_M paam ON pcb.person_id = paam.person_id
        JOIN PER_PERIODS_OF_SERVICE ppos ON paam.period_of_service_id = ppos.period_of_service_id AND ppos.date_start = paam.effective_start_date
        JOIN PER_ALL_PEOPLE_F ppnf ON pcb.person_id = ppnf.person_id
        JOIN PER_ALL_ASSIGNMENTS_M paam_mgr ON paam.manager_assignment_id = paam_mgr.assignment_id
        JOIN PER_ALL_PEOPLE_F papf_mgr ON paam_mgr.person_id = papf_mgr.person_id
        JOIN PER_JOBS_F_TL pjt ON paam.job_id = pjt.job_id AND pjt.language = 'US'
        JOIN HR_ORGANIZATION_UNITS_F_TL hout ON paam.organization_id = hout.organization_id AND hout.language = 'US'
    WHERE
        pcb.checklist_category = 'ONBOARDING'
        AND paam.assignment_type IN ('E', 'C') AND paam.primary_flag = 'Y' AND paam.assignment_status_type = 'ACTIVE'
        AND SYSDATE BETWEEN ppnf.effective_start_date AND ppnf.effective_end_date
        AND SYSDATE BETWEEN paam.effective_start_date AND paam.effective_end_date
        AND SYSDATE BETWEEN papf_mgr.effective_start_date AND papf_mgr.effective_end_date
        AND SYSDATE BETWEEN pjt.effective_start_date AND pjt.effective_end_date
        AND SYSDATE BETWEEN hout.effective_start_date AND hout.effective_end_date
        AND pcb.creation_date BETWEEN TO_DATE(:P_START_DATE, 'YYYY-MM-DD') AND TO_DATE(:P_END_DATE, 'YYYY-MM-DD')
)
-- 1. Offer Accepted
SELECT
    irc_cand.candidate_id AS OnboardingCase,
    'Offer Accepted' AS ActivityName,
    irc_sub.last_update_date AS EventTime,
    NULL AS EventEndTime,
    irc_sub.last_updated_by AS PerformedByUser,
    ob.department_name AS NewHireDepartment,
    ob.job_title AS NewHireJobTitle,
    ob.official_start_date AS OfficialStartDate,
    NULL AS ComplianceDueDate,
    ob.hiring_manager_name AS HiringManager,
    NULL AS ActivityDuration
FROM
    IRC_SUBMISSIONS irc_sub
    JOIN IRC_CANDIDATES irc_cand ON irc_sub.candidate_id = irc_cand.candidate_id
    JOIN OnboardingBase ob ON irc_cand.person_id = ob.person_id
WHERE
    irc_sub.status_code = 'ORA_OFFER_ACCEPTED'

UNION ALL

-- 2. Background Check Initiated
SELECT
    ob.person_id AS OnboardingCase,
    'Background Check Initiated' AS ActivityName,
    hct.creation_date AS EventTime,
    NULL AS EventEndTime,
    hct.created_by AS PerformedByUser,
    ob.department_name AS NewHireDepartment,
    ob.job_title AS NewHireJobTitle,
    ob.official_start_date AS OfficialStartDate,
    hct.target_end_date AS ComplianceDueDate,
    ob.hiring_manager_name AS HiringManager,
    NULL AS ActivityDuration
FROM
    HRC_DL_TASKS_B hct
    JOIN OnboardingBase ob ON hct.allocated_to_id = ob.person_id AND hct.checklist_id = ob.checklist_id
WHERE
    hct.task_name LIKE 'Background Check%'

UNION ALL

-- 3. Background Check Completed
SELECT
    ob.person_id AS OnboardingCase,
    'Background Check Completed' AS ActivityName,
    hct.actual_completion_date AS EventTime,
    NULL AS EventEndTime,
    hct.last_updated_by AS PerformedByUser,
    ob.department_name AS NewHireDepartment,
    ob.job_title AS NewHireJobTitle,
    ob.official_start_date AS OfficialStartDate,
    NULL AS ComplianceDueDate,
    ob.hiring_manager_name AS HiringManager,
    (hct.actual_completion_date - hct.creation_date) AS ActivityDuration
FROM
    HRC_DL_TASKS_B hct
    JOIN OnboardingBase ob ON hct.allocated_to_id = ob.person_id AND hct.checklist_id = ob.checklist_id
WHERE
    hct.task_name LIKE 'Background Check%' AND hct.status = 'COMPLETED'

UNION ALL

-- 4. Pre-Employment Paperwork Sent
SELECT
    ob.person_id AS OnboardingCase,
    'Pre-Employment Paperwork Sent' AS ActivityName,
    hct.creation_date AS EventTime,
    NULL AS EventEndTime,
    hct.created_by AS PerformedByUser,
    ob.department_name AS NewHireDepartment,
    ob.job_title AS NewHireJobTitle,
    ob.official_start_date AS OfficialStartDate,
    hct.target_end_date AS ComplianceDueDate,
    ob.hiring_manager_name AS HiringManager,
    NULL AS ActivityDuration
FROM
    HRC_DL_TASKS_B hct
    JOIN OnboardingBase ob ON hct.allocated_to_id = ob.person_id AND hct.checklist_id = ob.checklist_id
WHERE
    hct.task_type = 'DOCUMENT'

UNION ALL

-- 5. Pre-Employment Paperwork Completed
SELECT
    ob.person_id AS OnboardingCase,
    'Pre-Employment Paperwork Completed' AS ActivityName,
    hct.actual_completion_date AS EventTime,
    NULL AS EventEndTime,
    hct.last_updated_by AS PerformedByUser,
    ob.department_name AS NewHireDepartment,
    ob.job_title AS NewHireJobTitle,
    ob.official_start_date AS OfficialStartDate,
    NULL AS ComplianceDueDate,
    ob.hiring_manager_name AS HiringManager,
    (hct.actual_completion_date - hct.creation_date) AS ActivityDuration
FROM
    HRC_DL_TASKS_B hct
    JOIN OnboardingBase ob ON hct.allocated_to_id = ob.person_id AND hct.checklist_id = ob.checklist_id
WHERE
    hct.task_type = 'DOCUMENT' AND hct.status = 'COMPLETED'

UNION ALL

-- 6. HR Profile Created
SELECT
    ob.person_id AS OnboardingCase,
    'HR Profile Created' AS ActivityName,
    ppos.creation_date AS EventTime,
    NULL AS EventEndTime,
    ppos.created_by AS PerformedByUser,
    ob.department_name AS NewHireDepartment,
    ob.job_title AS NewHireJobTitle,
    ob.official_start_date AS OfficialStartDate,
    NULL AS ComplianceDueDate,
    ob.hiring_manager_name AS HiringManager,
    NULL AS ActivityDuration
FROM
    PER_PERIODS_OF_SERVICE ppos
    JOIN OnboardingBase ob ON ppos.person_id = ob.person_id
WHERE ppos.person_type_id IN (SELECT person_type_id FROM PER_PERSON_TYPES_VL WHERE system_person_type = 'EMP')

UNION ALL

-- 7. Onboarding Journey Assigned
SELECT
    ob.person_id AS OnboardingCase,
    'Onboarding Journey Assigned' AS ActivityName,
    pcb.creation_date AS EventTime,
    NULL AS EventEndTime,
    pcb.created_by AS PerformedByUser,
    ob.department_name AS NewHireDepartment,
    ob.job_title AS NewHireJobTitle,
    ob.official_start_date AS OfficialStartDate,
    NULL AS ComplianceDueDate,
    ob.hiring_manager_name AS HiringManager,
    NULL AS ActivityDuration
FROM
    ORA_CHECKLISTS_B pcb
    JOIN OnboardingBase ob ON pcb.checklist_id = ob.checklist_id

UNION ALL

-- 8. IT Account Provisioning Initiated
SELECT
    ob.person_id AS OnboardingCase,
    'IT Account Provisioning Initiated' AS ActivityName,
    hct.creation_date AS EventTime,
    NULL AS EventEndTime,
    hct.created_by AS PerformedByUser,
    ob.department_name AS NewHireDepartment,
    ob.job_title AS NewHireJobTitle,
    ob.official_start_date AS OfficialStartDate,
    hct.target_end_date AS ComplianceDueDate,
    ob.hiring_manager_name AS HiringManager,
    NULL AS ActivityDuration
FROM
    HRC_DL_TASKS_B hct
    JOIN OnboardingBase ob ON hct.allocated_to_id = ob.person_id AND hct.checklist_id = ob.checklist_id
WHERE
    hct.task_name LIKE 'IT Account Provisioning%'

UNION ALL

-- 9. Equipment Ordered
SELECT
    ob.person_id AS OnboardingCase,
    'Equipment Ordered' AS ActivityName,
    hct.actual_completion_date AS EventTime,
    NULL AS EventEndTime,
    hct.last_updated_by AS PerformedByUser,
    ob.department_name AS NewHireDepartment,
    ob.job_title AS NewHireJobTitle,
    ob.official_start_date AS OfficialStartDate,
    NULL AS ComplianceDueDate,
    ob.hiring_manager_name AS HiringManager,
    NULL AS ActivityDuration
FROM
    HRC_DL_TASKS_B hct
    JOIN OnboardingBase ob ON hct.allocated_to_id = ob.person_id AND hct.checklist_id = ob.checklist_id
WHERE
    hct.task_name LIKE 'Order Equipment%' AND hct.status = 'COMPLETED'

UNION ALL

-- 10. Equipment Delivered
SELECT
    ob.person_id AS OnboardingCase,
    'Equipment Delivered' AS ActivityName,
    hct.actual_completion_date AS EventTime,
    NULL AS EventEndTime,
    hct.last_updated_by AS PerformedByUser,
    ob.department_name AS NewHireDepartment,
    ob.job_title AS NewHireJobTitle,
    ob.official_start_date AS OfficialStartDate,
    NULL AS ComplianceDueDate,
    ob.hiring_manager_name AS HiringManager,
    NULL AS ActivityDuration
FROM
    HRC_DL_TASKS_B hct
    JOIN OnboardingBase ob ON hct.allocated_to_id = ob.person_id AND hct.checklist_id = ob.checklist_id
WHERE
    hct.task_name LIKE 'Confirm Equipment Delivered%' AND hct.status = 'COMPLETED'

UNION ALL

-- 11. First Day Orientation Conducted
SELECT
    ob.person_id AS OnboardingCase,
    'First Day Orientation Conducted' AS ActivityName,
    hct.actual_completion_date AS EventTime,
    NULL AS EventEndTime,
    hct.last_updated_by AS PerformedByUser,
    ob.department_name AS NewHireDepartment,
    ob.job_title AS NewHireJobTitle,
    ob.official_start_date AS OfficialStartDate,
    NULL AS ComplianceDueDate,
    ob.hiring_manager_name AS HiringManager,
    NULL AS ActivityDuration
FROM
    HRC_DL_TASKS_B hct
    JOIN OnboardingBase ob ON hct.allocated_to_id = ob.person_id AND hct.checklist_id = ob.checklist_id
WHERE
    hct.task_name LIKE 'First Day Orientation%' AND hct.status = 'COMPLETED'

UNION ALL

-- 12. Mandatory Training Assigned
SELECT
    ob.person_id AS OnboardingCase,
    'Mandatory Training Assigned' AS ActivityName,
    we.creation_date AS EventTime,
    NULL AS EventEndTime,
    we.created_by AS PerformedByUser,
    ob.department_name AS NewHireDepartment,
    ob.job_title AS NewHireJobTitle,
    ob.official_start_date AS OfficialStartDate,
    we.due_date AS ComplianceDueDate,
    ob.hiring_manager_name AS HiringManager,
    NULL AS ActivityDuration
FROM
    WLF_ENROLLMENTS we
    JOIN OnboardingBase ob ON we.person_id = ob.person_id
WHERE
    we.learning_item_type = 'ORA_COURSE' AND we.enrollment_status_type_id IN (SELECT status.enrollment_status_type_id FROM WLF_LEARNING_STATUSES_VL status WHERE status.status_group = 'ACTIVE')

UNION ALL

-- 13. Mandatory Training Completed
SELECT
    ob.person_id AS OnboardingCase,
    'Mandatory Training Completed' AS ActivityName,
    we.completion_date AS EventTime,
    NULL AS EventEndTime,
    we.last_updated_by AS PerformedByUser,
    ob.department_name AS NewHireDepartment,
    ob.job_title AS NewHireJobTitle,
    ob.official_start_date AS OfficialStartDate,
    NULL AS ComplianceDueDate,
    ob.hiring_manager_name AS HiringManager,
    (we.completion_date - we.creation_date) AS ActivityDuration
FROM
    WLF_ENROLLMENTS we
    JOIN OnboardingBase ob ON we.person_id = ob.person_id
WHERE
    we.learning_item_type = 'ORA_COURSE' AND we.enrollment_status_type_id IN (SELECT status.enrollment_status_type_id FROM WLF_LEARNING_STATUSES_VL status WHERE status.status_group = 'COMPLETED')

UNION ALL

-- 14. Team Introduction Completed
SELECT
    ob.person_id AS OnboardingCase,
    'Team Introduction Completed' AS ActivityName,
    hct.actual_completion_date AS EventTime,
    NULL AS EventEndTime,
    hct.last_updated_by AS PerformedByUser,
    ob.department_name AS NewHireDepartment,
    ob.job_title AS NewHireJobTitle,
    ob.official_start_date AS OfficialStartDate,
    NULL AS ComplianceDueDate,
    ob.hiring_manager_name AS HiringManager,
    NULL AS ActivityDuration
FROM
    HRC_DL_TASKS_B hct
    JOIN OnboardingBase ob ON hct.allocated_to_id = ob.person_id AND hct.checklist_id = ob.checklist_id
WHERE
    hct.task_name LIKE 'Team Introduction%' AND hct.status = 'COMPLETED'

UNION ALL

-- 15. 30-Day Check-in Performed
SELECT
    ob.person_id AS OnboardingCase,
    '30-Day Check-in Performed' AS ActivityName,
    hct.actual_completion_date AS EventTime,
    NULL AS EventEndTime,
    hct.last_updated_by AS PerformedByUser,
    ob.department_name AS NewHireDepartment,
    ob.job_title AS NewHireJobTitle,
    ob.official_start_date AS OfficialStartDate,
    NULL AS ComplianceDueDate,
    ob.hiring_manager_name AS HiringManager,
    NULL AS ActivityDuration
FROM
    HRC_DL_TASKS_B hct
    JOIN OnboardingBase ob ON hct.allocated_to_id = ob.person_id AND hct.checklist_id = ob.checklist_id
WHERE
    hct.task_name LIKE '30-Day Check-in%' AND hct.status = 'COMPLETED'

UNION ALL

-- 16. Onboarding Completed
SELECT
    ob.person_id AS OnboardingCase,
    'Onboarding Completed' AS ActivityName,
    pcb.last_update_date AS EventTime,
    NULL AS EventEndTime,
    pcb.last_updated_by AS PerformedByUser,
    ob.department_name AS NewHireDepartment,
    ob.job_title AS NewHireJobTitle,
    ob.official_start_date AS OfficialStartDate,
    NULL AS ComplianceDueDate,
    ob.hiring_manager_name AS HiringManager,
    (pcb.last_update_date - pcb.creation_date) AS ActivityDuration
FROM
    ORA_CHECKLISTS_B pcb
    JOIN OnboardingBase ob ON pcb.checklist_id = ob.checklist_id
WHERE
    pcb.status = 'COMPLETED'

Steps

Prerequisites: Ensure you have Python 3.6 or later installed on your system. You will also need an Oracle HCM Cloud user account with the appropriate security roles, such as 'Human Capital Management Integration Specialist', to access the necessary REST APIs.
Install Python Libraries: Open your terminal or command prompt and install the required Python libraries for making API requests and handling data. Run the command: pip install requests pandas.
Save the Python Script: Copy the complete Python script provided in the 'query' section and save it as a file named extract_onboarding_log.py.
Configure Script Parameters: Open the saved script in a text editor. Locate the configuration section at the top of the file and replace the placeholder values for HCM_URL, USERNAME, PASSWORD, START_DATE, and END_DATE with your specific Oracle HCM environment details and desired date range.
Identify Task Names: The script maps specific task names from Oracle HCM to standardized activity names. Review the TASK_ACTIVITY_MAPPING dictionary in the script and update the keys (e.g., 'Background Screening', 'Complete New Hire Paperwork') to match the exact names of the tasks used in your organization's onboarding journeys.
Execute the Script: Run the script from your terminal or command prompt using the command: python extract_onboarding_log.py. The script will begin authenticating and fetching data.
Monitor Execution: The script will print progress messages to the console, indicating how many journeys have been processed. For large data volumes, this process may take some time as it paginates through API results.
Review the Output: Once the script completes successfully, it will generate a CSV file named onboarding_event_log.csv in the same directory. This file contains the extracted event log.
Prepare for Upload: Open the onboarding_event_log.csv file. Verify that the column headers match the required attributes: OnboardingCase, ActivityName, EventTime, and other optional fields. The file is now ready for upload into ProcessMind.

Configuration

API Credentials: The script requires a USERNAME and PASSWORD for an Oracle HCM user with permissions to access the /hcmRestApi/resources/11.13.18.05/onboardingJourneys and /hcmRestApi/resources/11.13.18.05/workers endpoints.
HCM URL: The HCM_URL variable must be set to the base URL of your Oracle HCM Cloud instance, for example, https://[your-pod].fa.em2.oraclecloud.com.
Date Range: The START_DATE and END_DATE parameters (in 'YYYY-MM-DD' format) filter the onboarding journeys based on their last update date. It is recommended to start with a 3 to 6 month period to manage the data volume.
Task Name Mapping: The TASK_ACTIVITY_MAPPING dictionary is crucial for correct activity extraction. You must customize this mapping to reflect the task names configured in your specific Oracle HCM Onboarding setup.
API Pagination Limit: The LIMIT variable controls how many records are fetched per API call. The default of 100 is generally safe, but can be adjusted based on system performance and API rate limits.
Security Roles: The user account must have a role that grants access to the 'Recruiting and Candidate Experience Management' and 'Person Management' functional areas, typically covered by a role like 'Human Capital Management Integration Specialist'.

a Sample Query python

import requests
import pandas as pd
import json
from datetime import datetime

class=class="hl-string">"hl-comment"># --- Configuration ---
HCM_URL = class="hl-string">"https://[your-pod].fa.em2.oraclecloud.com"
USERNAME = class="hl-string">"[your_api_user]"
PASSWORD = class="hl-string">"[your_api_password]"
START_DATE = class="hl-string">"2023-01-01"
END_DATE = class="hl-string">"2023-12-31"

class=class="hl-string">"hl-comment"># --- Mappings ---
class=class="hl-string">"hl-comment"># IMPORTANT: Update the keys in this dictionary to match the exact Task Names in your Oracle HCM setup.
TASK_ACTIVITY_MAPPING = {
    class="hl-string">'Background Check': (class="hl-string">'Background Check Initiated', class="hl-string">'Background Check Completed'),
    class="hl-string">'Pre-Employment Paperwork': (class="hl-string">'Pre-Employment Paperwork Sent', class="hl-string">'Pre-Employment Paperwork Completed'),
    class="hl-string">'IT Account Provisioning': (class="hl-string">'IT Account Provisioning Initiated', None),
    class="hl-string">'Order Equipment': (class="hl-string">'Equipment Ordered', None),
    class="hl-string">'Confirm Equipment Delivery': (None, class="hl-string">'Equipment Delivered'),
    class="hl-string">'First Day Orientation': (None, class="hl-string">'First Day Orientation Conducted'),
    class="hl-string">'Assign Mandatory Training': (class="hl-string">'Mandatory Training Assigned', None),
    class="hl-string">'Complete Mandatory Training': (None, class="hl-string">'Mandatory Training Completed'),
    class="hl-string">'Introduce New Hire to Team': (None, class="hl-string">'Team Introduction Completed'),
    class="hl-string">'30-Day Manager Check-in': (None, class="hl-string">'30-Day Check-in Performed')
}

class=class="hl-string">"hl-comment"># --- Main Extraction Logic ---
def get_paginated_data(session, url, params):
    class="hl-string">"""Fetches all pages of data from a given HCM REST API endpoint."""
    all_items = []
    offset = 0
    limit = 100
    while True:
        params[class="hl-string">'offset'] = offset
        params[class="hl-string">'limit'] = limit
        try:
            response = session.get(url, params=params)
            response.raise_for_status()
            data = response.json()
            items = data.get(class="hl-string">'items', [])
            all_items.extend(items)
            if not data.get(class="hl-string">'hasMore', False):
                break
            offset += limit
        except requests.exceptions.RequestException as e:
            print(fclass="hl-string">"Error fetching data from {url}: {e}")
            break
    return all_items

def get_worker_details(session, person_id):
    class="hl-string">"""Fetches details for a single worker."""
    url = fclass="hl-string">"{HCM_URL}/hcmRestApi/resources/11.13.18.05/workers"
    params = {
        class="hl-string">'q': fclass="hl-string">'PersonId={person_id}',
        class="hl-string">'fields': class="hl-string">'CreationDate,assignments,personNumber',
        class="hl-string">'expand': class="hl-string">'assignments'
    }
    worker_data = get_paginated_data(session, url, params)
    if worker_data and worker_data[0]:
        return worker_data[0]
    return None

def main():
    class="hl-string">"""Main function to orchestrate the extraction process."""
    event_log = []
    processed_journeys = 0

    with requests.Session() as session:
        session.auth = (USERNAME, PASSWORD)

        journeys_url = fclass="hl-string">"{HCM_URL}/hcmRestApi/resources/11.13.18.05/onboardingJourneys"
        journeys_params = {
            class="hl-string">'q': fclass="hl-string">'LastUpdateDate between \'{START_DATE}\class="hl-string">' and \'{END_DATE}\class="hl-string">'',
            class="hl-string">'fields': class="hl-string">'JourneyId,PersonId,PersonNumber,CreationDate,CompletedDate,JourneyName,tasks',
            class="hl-string">'expand': class="hl-string">'tasks'
        }

        print(class="hl-string">"Fetching onboarding journeys...")
        journeys = get_paginated_data(session, journeys_url, journeys_params)
        print(fclass="hl-string">"Found {len(journeys)} journeys to process.")

        for journey in journeys:
            person_id = journey.get(class="hl-string">'PersonId')
            if not person_id:
                continue

            case_id = journey.get(class="hl-string">'PersonNumber') or person_id

            class=class="hl-string">"hl-comment"># Fetch worker details for additional attributes
            worker = get_worker_details(session, person_id)
            if not worker:
                continue
            
            assignment = worker.get(class="hl-string">'assignments', [{}])[0] if worker.get(class="hl-string">'assignments') else {}
            new_hire_dept = assignment.get(class="hl-string">'DepartmentName')
            new_hire_job = assignment.get(class="hl-string">'JobName')
            official_start_date = assignment.get(class="hl-string">'EffectiveStartDate')
            hiring_manager_id = assignment.get(class="hl-string">'ManagerId')

            class=class="hl-string">"hl-comment"># 1. HR Profile Created
            if worker.get(class="hl-string">'CreationDate'):
                event_log.append({
                    class="hl-string">'OnboardingCase': case_id,
                    class="hl-string">'ActivityName': class="hl-string">'HR Profile Created',
                    class="hl-string">'EventTime': worker[class="hl-string">'CreationDate'],
                    class="hl-string">'PerformedByUser': class="hl-string">'System',
                    class="hl-string">'NewHireDepartment': new_hire_dept,
                    class="hl-string">'NewHireJobTitle': new_hire_job,
                    class="hl-string">'OfficialStartDate': official_start_date,
                    class="hl-string">'HiringManager': hiring_manager_id
                })
            
            class=class="hl-string">"hl-comment"># 2. Offer Accepted (using start date as a proxy)
            if official_start_date:
                event_log.append({
                    class="hl-string">'OnboardingCase': case_id,
                    class="hl-string">'ActivityName': class="hl-string">'Offer Accepted',
                    class="hl-string">'EventTime': official_start_date, class=class="hl-string">"hl-comment"># Approximation, actual acceptance might be earlier
                    class="hl-string">'PerformedByUser': journey.get(class="hl-string">'PersonNumber'),
                    class="hl-string">'NewHireDepartment': new_hire_dept,
                    class="hl-string">'NewHireJobTitle': new_hire_job,
                    class="hl-string">'OfficialStartDate': official_start_date,
                    class="hl-string">'HiringManager': hiring_manager_id
                })

            class=class="hl-string">"hl-comment"># 3. Onboarding Journey Assigned
            if journey.get(class="hl-string">'CreationDate'):
                event_log.append({
                    class="hl-string">'OnboardingCase': case_id,
                    class="hl-string">'ActivityName': class="hl-string">'Onboarding Journey Assigned',
                    class="hl-string">'EventTime': journey[class="hl-string">'CreationDate'],
                    class="hl-string">'PerformedByUser': class="hl-string">'System',
                    class="hl-string">'NewHireDepartment': new_hire_dept,
                    class="hl-string">'NewHireJobTitle': new_hire_job,
                    class="hl-string">'OfficialStartDate': official_start_date,
                    class="hl-string">'HiringManager': hiring_manager_id
                })

            class=class="hl-string">"hl-comment"># 4. Process Tasks from the Journey
            tasks = journey.get(class="hl-string">'tasks', [])
            for task in tasks:
                task_name = task.get(class="hl-string">'TaskName')
                initiating_activity, completing_activity = None, None
                
                class=class="hl-string">"hl-comment"># Find a matching activity from the mapping
                for key in TASK_ACTIVITY_MAPPING:
                    if key.lower() in task_name.lower():
                        initiating_activity, completing_activity = TASK_ACTIVITY_MAPPING[key]
                        break
                
                class=class="hl-string">"hl-comment"># Create event for initiated/sent activities
                if initiating_activity and task.get(class="hl-string">'CreationDate'):
                    event_log.append({
                        class="hl-string">'OnboardingCase': case_id,
                        class="hl-string">'ActivityName': initiating_activity,
                        class="hl-string">'EventTime': task[class="hl-string">'CreationDate'],
                        class="hl-string">'PerformedByUser': task.get(class="hl-string">'PerformerName', class="hl-string">'System'),
                        class="hl-string">'NewHireDepartment': new_hire_dept,
                        class="hl-string">'NewHireJobTitle': new_hire_job,
                        class="hl-string">'OfficialStartDate': official_start_date,
                        class="hl-string">'HiringManager': hiring_manager_id,
                        class="hl-string">'ComplianceDueDate': task.get(class="hl-string">'DueDate')
                    })

                class=class="hl-string">"hl-comment"># Create event for completed activities
                if completing_activity and task.get(class="hl-string">'CompletedDate'):
                    event_log.append({
                        class="hl-string">'OnboardingCase': case_id,
                        class="hl-string">'ActivityName': completing_activity,
                        class="hl-string">'EventTime': task[class="hl-string">'CompletedDate'],
                        class="hl-string">'EventEndTime': task.get(class="hl-string">'CompletedDate'),
                        class="hl-string">'PerformedByUser': task.get(class="hl-string">'PerformerName', class="hl-string">'System'),
                        class="hl-string">'NewHireDepartment': new_hire_dept,
                        class="hl-string">'NewHireJobTitle': new_hire_job,
                        class="hl-string">'OfficialStartDate': official_start_date,
                        class="hl-string">'HiringManager': hiring_manager_id,
                        class="hl-string">'ActivityDuration': (datetime.fromisoformat(task[class="hl-string">'CompletedDate'].replace(class="hl-string">'Z', class="hl-string">'+00:00')) - datetime.fromisoformat(task[class="hl-string">'CreationDate'].replace(class="hl-string">'Z', class="hl-string">'+00:00'))).total_seconds() if task.get(class="hl-string">'CreationDate') else None
                    })

            class=class="hl-string">"hl-comment"># 5. Onboarding Completed
            if journey.get(class="hl-string">'CompletedDate'):
                event_log.append({
                    class="hl-string">'OnboardingCase': case_id,
                    class="hl-string">'ActivityName': class="hl-string">'Onboarding Completed',
                    class="hl-string">'EventTime': journey[class="hl-string">'CompletedDate'],
                    class="hl-string">'PerformedByUser': class="hl-string">'System',
                    class="hl-string">'NewHireDepartment': new_hire_dept,
                    class="hl-string">'NewHireJobTitle': new_hire_job,
                    class="hl-string">'OfficialStartDate': official_start_date,
                    class="hl-string">'HiringManager': hiring_manager_id
                })

            processed_journeys += 1
            if processed_journeys % 50 == 0:
                print(fclass="hl-string">"Processed {processed_journeys}/{len(journeys)} journeys...")

    print(fclass="hl-string">"Total events created: {len(event_log)}")

    if not event_log:
        print(class="hl-string">"No events were generated. Please check your configuration and date range.")
        return

    class=class="hl-string">"hl-comment"># Create DataFrame and save to CSV
    df = pd.DataFrame(event_log)
    df_sorted = df.sort_values(by=[class="hl-string">'OnboardingCase', class="hl-string">'EventTime'])
    df_sorted.to_csv(class="hl-string">'onboarding_event_log.csv', index=False)
    print(class="hl-string">"Extraction complete. Event log saved to onboarding_event_log.csv")

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

Your Employee Onboarding Data Template

Your Employee Onboarding Data Template

Employee Onboarding Attributes

Employee Onboarding Activities

Extraction Guides

Oracle HCM OTBI Report for Onboarding Process

Steps

Configuration

a Sample Query config

Oracle HCM Extracts Utility for Onboarding Data

Steps

Configuration

a Sample Query config

Oracle BI Publisher SQL Data Model Extraction

Steps

Configuration

a Sample Query sql

Python Script Extraction via Oracle HCM REST API

Steps

Configuration

a Sample Query python

Ready to Get Started?

Start Optimizing Your Employee Onboarding Now

Your Employee Onboarding Data Template

Your Employee Onboarding Data Template

Employee Onboarding Attributes

Employee Onboarding Activities

Extraction Guides

Oracle HCM OTBI Report for Onboarding Process

Steps

Configuration

a Sample Query config

Oracle HCM Extracts Utility for Onboarding Data

Steps

Configuration

a Sample Query config

Oracle BI Publisher SQL Data Model Extraction

Steps

Configuration

a Sample Query sql

Python Script Extraction via Oracle HCM REST API

Steps

Configuration

a Sample Query python

We value your privacy

Ready to Get Started?

Start Optimizing Your Employee Onboarding Now