Templat data orientasi karyawan

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

WITH
source_events AS (
    SELECT
        [Onboarding case identifier] AS OnboardingCase,
        [Source activity code] AS SourceActivityCode,
        [Source status] AS SourceStatus,
        [Source event timestamp] AS SourceEventTime,
        [Source event end timestamp] AS SourceEventEndTime,
        [Performed by user] AS PerformedByUser,
        [Department] AS NewHireDepartment,
        [Job title] AS NewHireJobTitle,
        [Official start date] AS OfficialStartDate,
        [Compliance due date] AS ComplianceDueDate,
        [Hiring manager] AS HiringManager
    FROM [Your onboarding event source]
    WHERE [Source event timestamp] >= [Start date parameter]
      AND [Source event timestamp] < [End date parameter]
      AND ([Company or legal employer filter] IS NULL OR [Company or legal employer] = [Company or legal employer filter])
),
normalized_events AS (
    SELECT OnboardingCase, 'Offer Accepted' AS ActivityName, SourceEventTime AS EventTime, SourceEventEndTime AS EventEndTime, PerformedByUser, NewHireDepartment, NewHireJobTitle, OfficialStartDate, ComplianceDueDate, HiringManager FROM source_events WHERE SourceActivityCode = 'OFFER_ACCEPTED' OR (SourceActivityCode = 'OFFER_STATUS' AND SourceStatus = 'Offer Accepted')
    UNION ALL
    SELECT OnboardingCase, 'Background Check Initiated', SourceEventTime, SourceEventEndTime, PerformedByUser, NewHireDepartment, NewHireJobTitle, OfficialStartDate, ComplianceDueDate, HiringManager FROM source_events WHERE SourceActivityCode = 'BACKGROUND_CHECK_INITIATED'
    UNION ALL
    SELECT OnboardingCase, 'Background Check Completed', SourceEventTime, SourceEventEndTime, PerformedByUser, NewHireDepartment, NewHireJobTitle, OfficialStartDate, ComplianceDueDate, HiringManager FROM source_events WHERE SourceActivityCode = 'BACKGROUND_CHECK_COMPLETED'
    UNION ALL
    SELECT OnboardingCase, 'Pre-Employment Paperwork Sent', SourceEventTime, SourceEventEndTime, PerformedByUser, NewHireDepartment, NewHireJobTitle, OfficialStartDate, ComplianceDueDate, HiringManager FROM source_events WHERE SourceActivityCode = 'PRE_EMPLOYMENT_PAPERWORK_SENT'
    UNION ALL
    SELECT OnboardingCase, 'Pre-Employment Paperwork Completed', SourceEventTime, SourceEventEndTime, PerformedByUser, NewHireDepartment, NewHireJobTitle, OfficialStartDate, ComplianceDueDate, HiringManager FROM source_events WHERE SourceActivityCode = 'PRE_EMPLOYMENT_PAPERWORK_COMPLETED'
    UNION ALL
    SELECT OnboardingCase, 'HR Profile Created', SourceEventTime, SourceEventEndTime, PerformedByUser, NewHireDepartment, NewHireJobTitle, OfficialStartDate, ComplianceDueDate, HiringManager FROM source_events WHERE SourceActivityCode = 'HR_PROFILE_CREATED'
    UNION ALL
    SELECT OnboardingCase, 'Onboarding Journey Assigned', SourceEventTime, SourceEventEndTime, PerformedByUser, NewHireDepartment, NewHireJobTitle, OfficialStartDate, ComplianceDueDate, HiringManager FROM source_events WHERE SourceActivityCode = 'ONBOARDING_JOURNEY_ASSIGNED'
    UNION ALL
    SELECT OnboardingCase, 'IT Account Provisioning Initiated', SourceEventTime, SourceEventEndTime, PerformedByUser, NewHireDepartment, NewHireJobTitle, OfficialStartDate, ComplianceDueDate, HiringManager FROM source_events WHERE SourceActivityCode = 'IT_ACCOUNT_PROVISIONING_INITIATED'
    UNION ALL
    SELECT OnboardingCase, 'Equipment Ordered', SourceEventTime, SourceEventEndTime, PerformedByUser, NewHireDepartment, NewHireJobTitle, OfficialStartDate, ComplianceDueDate, HiringManager FROM source_events WHERE SourceActivityCode = 'EQUIPMENT_ORDERED'
    UNION ALL
    SELECT OnboardingCase, 'Equipment Delivered', SourceEventTime, SourceEventEndTime, PerformedByUser, NewHireDepartment, NewHireJobTitle, OfficialStartDate, ComplianceDueDate, HiringManager FROM source_events WHERE SourceActivityCode = 'EQUIPMENT_DELIVERED'
    UNION ALL
    SELECT OnboardingCase, 'First Day Orientation Conducted', SourceEventTime, SourceEventEndTime, PerformedByUser, NewHireDepartment, NewHireJobTitle, OfficialStartDate, ComplianceDueDate, HiringManager FROM source_events WHERE SourceActivityCode = 'FIRST_DAY_ORIENTATION_CONDUCTED'
    UNION ALL
    SELECT OnboardingCase, 'Mandatory Training Assigned', SourceEventTime, SourceEventEndTime, PerformedByUser, NewHireDepartment, NewHireJobTitle, OfficialStartDate, ComplianceDueDate, HiringManager FROM source_events WHERE SourceActivityCode = 'MANDATORY_TRAINING_ASSIGNED'
    UNION ALL
    SELECT OnboardingCase, 'Mandatory Training Completed', SourceEventTime, SourceEventEndTime, PerformedByUser, NewHireDepartment, NewHireJobTitle, OfficialStartDate, ComplianceDueDate, HiringManager FROM source_events WHERE SourceActivityCode = 'MANDATORY_TRAINING_COMPLETED'
    UNION ALL
    SELECT OnboardingCase, 'Team Introduction Completed', SourceEventTime, SourceEventEndTime, PerformedByUser, NewHireDepartment, NewHireJobTitle, OfficialStartDate, ComplianceDueDate, HiringManager FROM source_events WHERE SourceActivityCode = 'TEAM_INTRODUCTION_COMPLETED'
    UNION ALL
    SELECT OnboardingCase, '30-Day Check-in Performed', SourceEventTime, SourceEventEndTime, PerformedByUser, NewHireDepartment, NewHireJobTitle, OfficialStartDate, ComplianceDueDate, HiringManager FROM source_events WHERE SourceActivityCode = '30_DAY_CHECK_IN_PERFORMED'
    UNION ALL
    SELECT OnboardingCase, 'Onboarding Completed', SourceEventTime, SourceEventEndTime, PerformedByUser, NewHireDepartment, NewHireJobTitle, OfficialStartDate, ComplianceDueDate, HiringManager FROM source_events WHERE SourceActivityCode = 'ONBOARDING_COMPLETED' OR (SourceActivityCode = 'ONBOARDING_STATUS' AND SourceStatus = 'Completed')
)
SELECT
    OnboardingCase,
    ActivityName,
    EventTime,
    EventEndTime,
    PerformedByUser,
    NewHireDepartment,
    NewHireJobTitle,
    OfficialStartDate,
    ComplianceDueDate,
    HiringManager
FROM normalized_events
WHERE OnboardingCase IS NOT NULL
  AND ActivityName IS NOT NULL
  AND EventTime IS NOT NULL
ORDER BY OnboardingCase, EventTime, ActivityName;

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'

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()