Datatemplate voor Hire to Retire, de levenscyclus van medewerkers

/*
Integration Center does not use a single script. Instead, you configure multiple integrations through a graphical interface.
The following describes the configuration for each required integration to produce the final event log.
*/

// ======== Integration 1: Job Requisition Created ======== 
Integration.Name: "ProcessMind_JobRequisition_Created"
Starting.Entity: JobRequisition
Output.Columns:
  - ActivityName = "Job Requisition Created" (Calculated, Fixed String)
  - EventTime = jobReqGUId.createdDateTime (Source Field)
  - JobRequisitionId = jobReqId (Source Field)
  - EmployeeId = NULL (Calculated, Empty String)
  - HiringManager = hiringManager.usersSysId (Navigated Field)
  - Department = department.name (Navigated Field)
  - JobRole = jobTitle (Source Field)
  - SourceSystem = "SAP SuccessFactors Recruiting"
  - LastDataUpdate = NOW() (Calculated, Current Timestamp)
Filters:
  - lastModifiedDateTime > [Last Run Time]

// ======== Integration 2: Recruiting Application Events ======== 
Integration.Name: "ProcessMind_Recruiting_Application_Events"
Starting.Entity: JobApplication
Output.Columns:
  - ActivityName = IF(jobApplication.status.statusName CONTAINS 'Applied', 'Candidate Applied', IF(jobApplication.status.statusName CONTAINS 'Offer Accepted', 'Offer Accepted', NULL)) (Calculated Field)
  - EventTime = IF(ActivityName == 'Candidate Applied', jobApplication.createdDateTime, jobApplication.offerResponseDate) (Calculated Field)
  - JobRequisitionId = jobApplication.jobReqId (Source Field)
  - EmployeeId = jobApplication.candidate.usersSysId (Navigated Field, Note: this is the Candidate User ID, needs mapping to Employee ID post-hire)
  - HiringManager = jobApplication.jobRequisition.hiringManager.usersSysId (Navigated Field)
  - Department = jobApplication.jobRequisition.department.name (Navigated Field)
  - JobRole = jobApplication.jobRequisition.jobTitle (Navigated Field)
  - SourceSystem = "SAP SuccessFactors Recruiting"
  - LastDataUpdate = NOW() (Calculated, Current Timestamp)
Filters:
  - lastModifiedDateTime > [Last Run Time]
  - ActivityName != NULL

// ======== Integration 3: Onboarding & Offboarding Process Events ======== 
Integration.Name: "ProcessMind_Onboarding_Events"
Starting.Entity: ONB2Process
Output.Columns:
  // Multiple rows can be generated from one ONB2Process record. This logic is best handled in the post-processing script.
  // This example flattens it for simplicity, assuming one activity per record based on current status.
  - ActivityName = CASE(ONB2Process.processStatus.code, 'READY_TO_HIRE', 'Ready To Hire Status Set', 'COMPLETED', 'Onboarding Initiated', 'ONB_TASKS_COMPLETED', 'New Hire Data Collection Completed', 'MNGR_TASKS_COMPLETED', 'Manager Tasks Completed', 'OFF_COMPLETED', 'Offboarding Tasks Completed') (Calculated Field)
  - EventTime = ONB2Process.lastModifiedDateTime (Source Field)
  - EmployeeId = ONB2Process.personId (Source Field)
  - JobRequisitionId = ONB2Process.requisitionId (Source Field)
  - HiringManager = [Configure based on your system]
  - Department = [Configure based on your system]
  - JobRole = [Configure based on your system]
  - SourceSystem = "SAP SuccessFactors Onboarding"
  - LastDataUpdate = NOW() (Calculated, Current Timestamp)
Filters:
  - lastModifiedDateTime > [Last Run Time]

// ======== Integration 4: Employee Central Lifecycle Events ======== 
Integration.Name: "ProcessMind_EmployeeCentral_Events"
Starting.Entity: EmpJob
Output.Columns:
  - ActivityName = CASE(EmpJob.eventReason, 'HIRE', 'Employee Profile Activated', 'PROMO', 'Promotion Approved', 'TERML', 'Termination Initiated', 'TERM', 'Employee Terminated', 'PAYSETUP', 'Payroll Setup Completed') (Calculated Field)
  - EventTime = EmpJob.startDate (Source Field for most events) OR EmpJob.createdDate (For 'Termination Initiated')
  - EmployeeId = EmpJob.userId (Source Field)
  - EndTime = IF(ActivityName == 'Employee Terminated', EmpJob.endDate, NULL) (Calculated Field)
  - JobRequisitionId = [Configure if custom field exists]
  - HiringManager = EmpJob.managerId (Source Field)
  - Department = EmpJob.department (Source Field)
  - JobRole = EmpJob.jobTitle (Source Field)
  - SourceSystem = "SAP SuccessFactors Employee Central"
  - LastDataUpdate = NOW() (Calculated, Current Timestamp)
Filters:
  - lastModifiedDate > [Last Run Time]
  - eventReason IN ('HIRE', 'PROMO', 'TERML', 'TERM', 'PAYSETUP')

// ======== Integration 5: Performance Management Events ======== 
Integration.Name: "ProcessMind_Performance_Events"
Starting.Entity: FormHeader
Output.Columns:
  - ActivityName = "Performance Review Conducted" (Calculated, Fixed String)
  - EventTime = FormHeader.formLastModifiedDate (Source Field)
  - EmployeeId = FormHeader.formSubject.userId (Navigated Field)
  - HiringManager = [Configure based on your system, often a form participant]
  - Department = [Configure based on your system]
  - JobRole = [Configure based on your system]
  - SourceSystem = "SAP SuccessFactors Performance & Goals"
  - LastDataUpdate = NOW() (Calculated, Current Timestamp)
Filters:
  - lastModified > [Last Run Time]
  - formTemplateType = 'Review'
  - formStatus = 'Completed' // Use the numeric value for completed status

// ======== Integration 6: Compliance Form Events ======== 
Integration.Name: "ProcessMind_Compliance_Events"
Starting.Entity: ONB2DataCollectionUserConfig // Or specific compliance entity
Output.Columns:
  - ActivityName = "Compliance Form Signed" (Calculated, Fixed String)
  - EventTime = ONB2DataCollectionUserConfig.lastModifiedDateTime (Source Field)
  - EmployeeId = ONB2DataCollectionUserConfig.process.personId (Navigated Field)
  - JobRequisitionId = ONB2DataCollectionUserConfig.process.requisitionId (Navigated Field)
  - HiringManager = [Configure based on your system]
  - Department = [Configure based on your system]
  - JobRole = [Configure based on your system]
  - SourceSystem = "SAP SuccessFactors Onboarding"
  - LastDataUpdate = NOW() (Calculated, Current Timestamp)
Filters:
  - lastModifiedDateTime > [Last Run Time]
  - status == 'COMPLETED'

SELECT
    EmployeeId,
    ActivityName,
    EventTime,
    SourceSystem,
    LastDataUpdate,
    EndTime,
    JobRequisitionId,
    HiringManager,
    Department,
    JobRole,
    OnboardingCycleTime,
    TimeToHire
FROM [Your exported Report Center event dataset]
WHERE EventTime >= [Start date]
  AND EventTime < [End date]
  AND ([Company Code filter] IS NULL OR CompanyCode = [Company Code filter])
ORDER BY EmployeeId, EventTime, ActivityName;

-- Configure the Report Center custom report to produce the following 15 explicit activity rows.
-- The source entity and field names below must be mapped to fields exposed by your tenant.
-- Replace each bracketed source reference with the corresponding Report Center field.

SELECT
    [Employee ID field] AS EmployeeId,
    'Job Requisition Created' AS ActivityName,
    [Requisition Created Timestamp field] AS EventTime,
    'Recruiting' AS SourceSystem,
    [Last Data Update field] AS LastDataUpdate,
    NULL AS EndTime,
    [Job Requisition ID field] AS JobRequisitionId,
    [Hiring Manager field] AS HiringManager,
    [Department field] AS Department,
    [Job Role field] AS JobRole,
    NULL AS OnboardingCycleTime,
    NULL AS TimeToHire
FROM [Your Recruiting requisition entity]
WHERE [Requisition Created Timestamp field] >= [Start date]
  AND [Requisition Created Timestamp field] < [End date]

UNION ALL

SELECT
    [Employee ID field] AS EmployeeId,
    'Candidate Applied' AS ActivityName,
    [Application Submitted Timestamp field] AS EventTime,
    'Recruiting' AS SourceSystem,
    [Last Data Update field] AS LastDataUpdate,
    NULL AS EndTime,
    [Job Requisition ID field] AS JobRequisitionId,
    [Hiring Manager field] AS HiringManager,
    [Department field] AS Department,
    [Job Role field] AS JobRole,
    NULL AS OnboardingCycleTime,
    NULL AS TimeToHire
FROM [Your Recruiting application entity]
WHERE [Application Submitted Timestamp field] >= [Start date]
  AND [Application Submitted Timestamp field] < [End date]

UNION ALL

SELECT
    [Employee ID field] AS EmployeeId,
    'Offer Accepted' AS ActivityName,
    [Offer Accepted Timestamp field] AS EventTime,
    'Recruiting' AS SourceSystem,
    [Last Data Update field] AS LastDataUpdate,
    NULL AS EndTime,
    [Job Requisition ID field] AS JobRequisitionId,
    [Hiring Manager field] AS HiringManager,
    [Department field] AS Department,
    [Job Role field] AS JobRole,
    NULL AS OnboardingCycleTime,
    NULL AS TimeToHire
FROM [Your Recruiting offer or application status entity]
WHERE [Offer Accepted Timestamp field] >= [Start date]
  AND [Offer Accepted Timestamp field] < [End date]

UNION ALL

SELECT
    [Employee ID field] AS EmployeeId,
    'Onboarding Initiated' AS ActivityName,
    [Onboarding Initiated Timestamp field] AS EventTime,
    'Onboarding' AS SourceSystem,
    [Last Data Update field] AS LastDataUpdate,
    NULL AS EndTime,
    [Job Requisition ID field] AS JobRequisitionId,
    [Hiring Manager field] AS HiringManager,
    [Department field] AS Department,
    [Job Role field] AS JobRole,
    NULL AS OnboardingCycleTime,
    NULL AS TimeToHire
FROM [Your Onboarding process entity]
WHERE [Onboarding Initiated Timestamp field] >= [Start date]
  AND [Onboarding Initiated Timestamp field] < [End date]

UNION ALL

SELECT
    [Employee ID field] AS EmployeeId,
    'New Hire Data Collection Completed' AS ActivityName,
    [New Hire Data Collection Completed Timestamp field] AS EventTime,
    'Onboarding' AS SourceSystem,
    [Last Data Update field] AS LastDataUpdate,
    [New Hire Data Collection Completed Timestamp field] AS EndTime,
    [Job Requisition ID field] AS JobRequisitionId,
    [Hiring Manager field] AS HiringManager,
    [Department field] AS Department,
    [Job Role field] AS JobRole,
    NULL AS OnboardingCycleTime,
    NULL AS TimeToHire
FROM [Your Onboarding data collection task entity]
WHERE [New Hire Data Collection Completed Timestamp field] >= [Start date]
  AND [New Hire Data Collection Completed Timestamp field] < [End date]

UNION ALL

SELECT
    [Employee ID field] AS EmployeeId,
    'Compliance Form Signed' AS ActivityName,
    [Compliance Form Signed Timestamp field] AS EventTime,
    'Onboarding' AS SourceSystem,
    [Last Data Update field] AS LastDataUpdate,
    [Compliance Form Signed Timestamp field] AS EndTime,
    [Job Requisition ID field] AS JobRequisitionId,
    [Hiring Manager field] AS HiringManager,
    [Department field] AS Department,
    [Job Role field] AS JobRole,
    NULL AS OnboardingCycleTime,
    NULL AS TimeToHire
FROM [Your Onboarding compliance document entity]
WHERE [Compliance Form Signed Timestamp field] >= [Start date]
  AND [Compliance Form Signed Timestamp field] < [End date]

UNION ALL

SELECT
    [Employee ID field] AS EmployeeId,
    'Manager Tasks Completed' AS ActivityName,
    [Manager Tasks Completed Timestamp field] AS EventTime,
    'Onboarding' AS SourceSystem,
    [Last Data Update field] AS LastDataUpdate,
    [Manager Tasks Completed Timestamp field] AS EndTime,
    [Job Requisition ID field] AS JobRequisitionId,
    [Hiring Manager field] AS HiringManager,
    [Department field] AS Department,
    [Job Role field] AS JobRole,
    NULL AS OnboardingCycleTime,
    NULL AS TimeToHire
FROM [Your Onboarding manager task entity]
WHERE [Manager Tasks Completed Timestamp field] >= [Start date]
  AND [Manager Tasks Completed Timestamp field] < [End date]

UNION ALL

SELECT
    [Employee ID field] AS EmployeeId,
    'Ready To Hire Status Set' AS ActivityName,
    [Ready To Hire Status Timestamp field] AS EventTime,
    'Onboarding' AS SourceSystem,
    [Last Data Update field] AS LastDataUpdate,
    NULL AS EndTime,
    [Job Requisition ID field] AS JobRequisitionId,
    [Hiring Manager field] AS HiringManager,
    [Department field] AS Department,
    [Job Role field] AS JobRole,
    NULL AS OnboardingCycleTime,
    NULL AS TimeToHire
FROM [Your Onboarding status entity]
WHERE [Ready To Hire Status Timestamp field] >= [Start date]
  AND [Ready To Hire Status Timestamp field] < [End date]

UNION ALL

SELECT
    [Employee ID field] AS EmployeeId,
    'Employee Profile Activated' AS ActivityName,
    [Employee Profile Activated Timestamp field] AS EventTime,
    'Employee Central' AS SourceSystem,
    [Last Data Update field] AS LastDataUpdate,
    NULL AS EndTime,
    [Job Requisition ID field] AS JobRequisitionId,
    [Hiring Manager field] AS HiringManager,
    [Department field] AS Department,
    [Job Role field] AS JobRole,
    NULL AS OnboardingCycleTime,
    NULL AS TimeToHire
FROM [Your Employee Central employee profile entity]
WHERE [Employee Profile Activated Timestamp field] >= [Start date]
  AND [Employee Profile Activated Timestamp field] < [End date]

UNION ALL

SELECT
    [Employee ID field] AS EmployeeId,
    'Payroll Setup Completed' AS ActivityName,
    [Payroll Setup Completed Timestamp field] AS EventTime,
    'Payroll' AS SourceSystem,
    [Last Data Update field] AS LastDataUpdate,
    [Payroll Setup Completed Timestamp field] AS EndTime,
    [Job Requisition ID field] AS JobRequisitionId,
    [Hiring Manager field] AS HiringManager,
    [Department field] AS Department,
    [Job Role field] AS JobRole,
    NULL AS OnboardingCycleTime,
    NULL AS TimeToHire
FROM [Your payroll setup or payroll integration status entity]
WHERE [Payroll Setup Completed Timestamp field] >= [Start date]
  AND [Payroll Setup Completed Timestamp field] < [End date]

UNION ALL

SELECT
    [Employee ID field] AS EmployeeId,
    'Performance Review Conducted' AS ActivityName,
    [Performance Review Completed Timestamp field] AS EventTime,
    'Performance and Goals' AS SourceSystem,
    [Last Data Update field] AS LastDataUpdate,
    [Performance Review Completed Timestamp field] AS EndTime,
    [Job Requisition ID field] AS JobRequisitionId,
    [Hiring Manager field] AS HiringManager,
    [Department field] AS Department,
    [Job Role field] AS JobRole,
    NULL AS OnboardingCycleTime,
    NULL AS TimeToHire
FROM [Your Performance and Goals review entity]
WHERE [Performance Review Completed Timestamp field] >= [Start date]
  AND [Performance Review Completed Timestamp field] < [End date]

UNION ALL

SELECT
    [Employee ID field] AS EmployeeId,
    'Promotion Approved' AS ActivityName,
    [Promotion Approved Timestamp field] AS EventTime,
    'Employee Central' AS SourceSystem,
    [Last Data Update field] AS LastDataUpdate,
    NULL AS EndTime,
    [Job Requisition ID field] AS JobRequisitionId,
    [Hiring Manager field] AS HiringManager,
    [Department field] AS Department,
    [Job Role field] AS JobRole,
    NULL AS OnboardingCycleTime,
    NULL AS TimeToHire
FROM [Your Employee Central job information change entity]
WHERE [Promotion Approved Timestamp field] >= [Start date]
  AND [Promotion Approved Timestamp field] < [End date]
  AND [Promotion Event Reason field] = [Promotion event reason value]

UNION ALL

SELECT
    [Employee ID field] AS EmployeeId,
    'Termination Initiated' AS ActivityName,
    [Termination Initiated Timestamp field] AS EventTime,
    'Employee Central' AS SourceSystem,
    [Last Data Update field] AS LastDataUpdate,
    NULL AS EndTime,
    [Job Requisition ID field] AS JobRequisitionId,
    [Hiring Manager field] AS HiringManager,
    [Department field] AS Department,
    [Job Role field] AS JobRole,
    NULL AS OnboardingCycleTime,
    NULL AS TimeToHire
FROM [Your Employee Central termination entity]
WHERE [Termination Initiated Timestamp field] >= [Start date]
  AND [Termination Initiated Timestamp field] < [End date]

UNION ALL

SELECT
    [Employee ID field] AS EmployeeId,
    'Offboarding Tasks Completed' AS ActivityName,
    [Offboarding Tasks Completed Timestamp field] AS EventTime,
    'Offboarding' AS SourceSystem,
    [Last Data Update field] AS LastDataUpdate,
    [Offboarding Tasks Completed Timestamp field] AS EndTime,
    [Job Requisition ID field] AS JobRequisitionId,
    [Hiring Manager field] AS HiringManager,
    [Department field] AS Department,
    [Job Role field] AS JobRole,
    NULL AS OnboardingCycleTime,
    NULL AS TimeToHire
FROM [Your Offboarding checklist entity]
WHERE [Offboarding Tasks Completed Timestamp field] >= [Start date]
  AND [Offboarding Tasks Completed Timestamp field] < [End date]

UNION ALL

SELECT
    [Employee ID field] AS EmployeeId,
    'Employee Terminated' AS ActivityName,
    [Employee Terminated Timestamp field] AS EventTime,
    'Employee Central' AS SourceSystem,
    [Last Data Update field] AS LastDataUpdate,
    NULL AS EndTime,
    [Job Requisition ID field] AS JobRequisitionId,
    [Hiring Manager field] AS HiringManager,
    [Department field] AS Department,
    [Job Role field] AS JobRole,
    NULL AS OnboardingCycleTime,
    NULL AS TimeToHire
FROM [Your Employee Central employment status entity]
WHERE [Employee Terminated Timestamp field] >= [Start date]
  AND [Employee Terminated Timestamp field] < [End date]

ORDER BY EmployeeId, EventTime, ActivityName;