Datatemplate voor assetonderhoud

/* 1. Work Order Created */
SELECT 
    evt_code AS WorkOrderNumber,
    'Work Order Created' AS ActivityName,
    evt_created AS EventTimestamp,
    'Hexagon EAM' AS SourceSystem,
    GETDATE() AS LastDataUpdate,
    evt_type AS WorkOrderType,
    evt_priority AS PriorityCode,
    evt_mrc AS MaintenanceDepartment,
    evt_updatedby AS UpdatedBy,
    evt_target AS TargetCompletionDate,
    evt_object AS AssetCode,
    NULL AS ActualLaborHours
FROM r5events
WHERE evt_type IN ('JOB', 'PPM') -- Filter for relevant WO types
  AND evt_created >= '2023-01-01' -- [Set Start Date]
  AND evt_org = 'YOUR_ORG'        -- [Set Organization]

UNION ALL

/* 2. Priority Updated (Requires Audit Log) */
SELECT 
    aud_code AS WorkOrderNumber,
    'Priority Updated' AS ActivityName,
    aud_date AS EventTimestamp,
    'Hexagon EAM' AS SourceSystem,
    GETDATE() AS LastDataUpdate,
    NULL AS WorkOrderType,
    aud_value AS PriorityCode,
    NULL AS MaintenanceDepartment,
    aud_user AS UpdatedBy,
    NULL AS TargetCompletionDate,
    NULL AS AssetCode,
    NULL AS ActualLaborHours
FROM r5auditvalues
WHERE aud_table = 'R5EVENTS' 
  AND aud_field = 'EVT_PRIORITY'
  AND aud_org = 'YOUR_ORG'

UNION ALL

/* 3. Status Changes (Approved, Scheduled, Started, Completed, Signed Off, Closed) */
/* This block maps generic status codes to specific ProcessMind activities */
SELECT 
    est_event AS WorkOrderNumber,
    CASE est_status
        WHEN 'A' THEN 'Work Order Approved'
        WHEN 'S' THEN 'Work Order Scheduled'
        WHEN 'IP' THEN 'Work Order Started'
        WHEN 'C' THEN 'Work Order Completed'
        WHEN 'QC' THEN 'Quality Control Passed'
        WHEN 'SO' THEN 'Work Order Signed Off'
        WHEN 'CL' THEN 'Work Order Closed'
        ELSE 'Status Change: ' + est_status
    END AS ActivityName,
    est_date AS EventTimestamp,
    'Hexagon EAM' AS SourceSystem,
    GETDATE() AS LastDataUpdate,
    NULL AS WorkOrderType,
    NULL AS PriorityCode,
    NULL AS MaintenanceDepartment,
    est_auth AS UpdatedBy,
    NULL AS TargetCompletionDate,
    NULL AS AssetCode,
    NULL AS ActualLaborHours
FROM r5eventstatus
WHERE est_status IN ('A', 'S', 'IP', 'C', 'QC', 'SO', 'CL')
  AND est_org = 'YOUR_ORG'

UNION ALL

/* 4. Material Requisition Created */
SELECT 
    req_event AS WorkOrderNumber,
    'Material Requisition Created' AS ActivityName,
    req_created AS EventTimestamp,
    'Hexagon EAM' AS SourceSystem,
    GETDATE() AS LastDataUpdate,
    NULL AS WorkOrderType,
    NULL AS PriorityCode,
    NULL AS MaintenanceDepartment,
    req_createdby AS UpdatedBy,
    NULL AS TargetCompletionDate,
    NULL AS AssetCode,
    NULL AS ActualLaborHours
FROM r5requisitions
WHERE req_event IS NOT NULL
  AND req_org = 'YOUR_ORG'

UNION ALL

/* 5. Material Issued */
SELECT 
    trl_event AS WorkOrderNumber,
    'Material Issued' AS ActivityName,
    trl_date AS EventTimestamp,
    'Hexagon EAM' AS SourceSystem,
    GETDATE() AS LastDataUpdate,
    NULL AS WorkOrderType,
    NULL AS PriorityCode,
    NULL AS MaintenanceDepartment,
    trl_user AS UpdatedBy,
    NULL AS TargetCompletionDate,
    trl_part AS AssetCode, -- Storing Part ID in Asset column for context
    NULL AS ActualLaborHours
FROM r5translines
WHERE trl_type = 'I' -- I = Issue
  AND trl_event IS NOT NULL
  AND trl_org = 'YOUR_ORG'

UNION ALL

/* 6. Labor Hours Recorded */
SELECT 
    boo_event AS WorkOrderNumber,
    'Labor Hours Recorded' AS ActivityName,
    boo_entered AS EventTimestamp,
    'Hexagon EAM' AS SourceSystem,
    GETDATE() AS LastDataUpdate,
    NULL AS WorkOrderType,
    NULL AS PriorityCode,
    boo_mrc AS MaintenanceDepartment,
    boo_person AS UpdatedBy,
    NULL AS TargetCompletionDate,
    NULL AS AssetCode,
    boo_hours AS ActualLaborHours
FROM r5bookedhours
WHERE boo_event IS NOT NULL
  AND boo_org = 'YOUR_ORG'

UNION ALL

/* 7. Checklist Item Verified */
SELECT 
    ckv_event AS WorkOrderNumber,
    'Checklist Item Verified' AS ActivityName,
    ckv_entrydate AS EventTimestamp,
    'Hexagon EAM' AS SourceSystem,
    GETDATE() AS LastDataUpdate,
    NULL AS WorkOrderType,
    NULL AS PriorityCode,
    NULL AS MaintenanceDepartment,
    ckv_enteredby AS UpdatedBy,
    NULL AS TargetCompletionDate,
    NULL AS AssetCode,
    NULL AS ActualLaborHours
FROM r5checklistvalues
WHERE ckv_completed = '+' -- Logic for completed item depends on config
  AND ckv_event IS NOT NULL
  AND ckv_org = 'YOUR_ORG'

/* 1. Work Order Created */
SELECT
    E.EVT_CODE AS WorkOrderNumber,
    'Work Order Created' AS ActivityName,
    E.EVT_DATE AS EventTimestamp,
    'Hexagon EAM' AS SourceSystem,
    GETDATE() AS LastDataUpdate,
    E.EVT_TYPE AS WorkOrderType,
    E.EVT_PRIORITY AS PriorityCode,
    E.EVT_MRC AS MaintenanceDepartment,
    E.EVT_CREATEDBY AS UpdatedBy,
    E.EVT_TARGET AS TargetCompletionDate,
    E.EVT_OBJECT AS AssetCode,
    NULL AS ActualLaborHours
FROM R5EVENTS E
WHERE E.EVT_TYPE IN ('JOB', 'PPM') -- Filter for Standard and Preventive WOs

UNION ALL

/* 2. Priority Updated (From Audit Log) */
SELECT
    E.EVT_CODE AS WorkOrderNumber,
    'Priority Updated' AS ActivityName,
    A.AUD_DATE AS EventTimestamp,
    'Hexagon EAM' AS SourceSystem,
    GETDATE() AS LastDataUpdate,
    E.EVT_TYPE AS WorkOrderType,
    A.AUD_VALUE AS PriorityCode,
    E.EVT_MRC AS MaintenanceDepartment,
    A.AUD_USER AS UpdatedBy,
    E.EVT_TARGET AS TargetCompletionDate,
    E.EVT_OBJECT AS AssetCode,
    NULL AS ActualLaborHours
FROM R5EVENTS E
JOIN R5AUDITVALUES A ON E.EVT_CODE = A.AUD_KEY1
WHERE A.AUD_TABLE = 'R5EVENTS'
  AND A.AUD_FIELD = 'EVT_PRIORITY'

UNION ALL

/* 3. Work Order Approved */
SELECT
    E.EVT_CODE AS WorkOrderNumber,
    'Work Order Approved' AS ActivityName,
    S.STA_DATE AS EventTimestamp,
    'Hexagon EAM' AS SourceSystem,
    GETDATE() AS LastDataUpdate,
    E.EVT_TYPE AS WorkOrderType,
    E.EVT_PRIORITY AS PriorityCode,
    E.EVT_MRC AS MaintenanceDepartment,
    S.STA_UPDATEUSER AS UpdatedBy,
    E.EVT_TARGET AS TargetCompletionDate,
    E.EVT_OBJECT AS AssetCode,
    NULL AS ActualLaborHours
FROM R5EVENTS E
JOIN R5STATUS S ON E.EVT_CODE = S.STA_EVENT
WHERE S.STA_STATUS = 'APPR' -- Replace with your Approved status code

UNION ALL

/* 4. Material Requisition Created */
SELECT
    E.EVT_CODE AS WorkOrderNumber,
    'Material Requisition Created' AS ActivityName,
    R.REQ_DATE AS EventTimestamp,
    'Hexagon EAM' AS SourceSystem,
    GETDATE() AS LastDataUpdate,
    E.EVT_TYPE AS WorkOrderType,
    E.EVT_PRIORITY AS PriorityCode,
    E.EVT_MRC AS MaintenanceDepartment,
    R.REQ_CREATEDBY AS UpdatedBy,
    E.EVT_TARGET AS TargetCompletionDate,
    E.EVT_OBJECT AS AssetCode,
    NULL AS ActualLaborHours
FROM R5EVENTS E
JOIN R5REQUISITIONS R ON E.EVT_CODE = R.REQ_EVENT

UNION ALL

/* 5. Work Order Scheduled */
SELECT
    E.EVT_CODE AS WorkOrderNumber,
    'Work Order Scheduled' AS ActivityName,
    S.STA_DATE AS EventTimestamp,
    'Hexagon EAM' AS SourceSystem,
    GETDATE() AS LastDataUpdate,
    E.EVT_TYPE AS WorkOrderType,
    E.EVT_PRIORITY AS PriorityCode,
    E.EVT_MRC AS MaintenanceDepartment,
    S.STA_UPDATEUSER AS UpdatedBy,
    E.EVT_TARGET AS TargetCompletionDate,
    E.EVT_OBJECT AS AssetCode,
    NULL AS ActualLaborHours
FROM R5EVENTS E
JOIN R5STATUS S ON E.EVT_CODE = S.STA_EVENT
WHERE S.STA_STATUS = 'SCHD' -- Replace with your Scheduled status code

UNION ALL

/* 6. Work Order Started */
SELECT
    E.EVT_CODE AS WorkOrderNumber,
    'Work Order Started' AS ActivityName,
    S.STA_DATE AS EventTimestamp,
    'Hexagon EAM' AS SourceSystem,
    GETDATE() AS LastDataUpdate,
    E.EVT_TYPE AS WorkOrderType,
    E.EVT_PRIORITY AS PriorityCode,
    E.EVT_MRC AS MaintenanceDepartment,
    S.STA_UPDATEUSER AS UpdatedBy,
    E.EVT_TARGET AS TargetCompletionDate,
    E.EVT_OBJECT AS AssetCode,
    NULL AS ActualLaborHours
FROM R5EVENTS E
JOIN R5STATUS S ON E.EVT_CODE = S.STA_EVENT
WHERE S.STA_STATUS IN ('STRT', 'INPRG') -- Replace with your Started/In Progress status codes

UNION ALL

/* 7. Material Issued */
SELECT
    E.EVT_CODE AS WorkOrderNumber,
    'Material Issued' AS ActivityName,
    T.TRN_DATE AS EventTimestamp,
    'Hexagon EAM' AS SourceSystem,
    GETDATE() AS LastDataUpdate,
    E.EVT_TYPE AS WorkOrderType,
    E.EVT_PRIORITY AS PriorityCode,
    E.EVT_MRC AS MaintenanceDepartment,
    T.TRN_USER AS UpdatedBy,
    E.EVT_TARGET AS TargetCompletionDate,
    E.EVT_OBJECT AS AssetCode,
    NULL AS ActualLaborHours
FROM R5EVENTS E
JOIN R5TRANSACTIONS T ON E.EVT_CODE = T.TRN_EVENT
WHERE T.TRN_TYPE = 'ISSUE' -- Indicates material issue from store

UNION ALL

/* 8. Labor Hours Recorded */
SELECT
    E.EVT_CODE AS WorkOrderNumber,
    'Labor Hours Recorded' AS ActivityName,
    B.BOO_DATE AS EventTimestamp,
    'Hexagon EAM' AS SourceSystem,
    GETDATE() AS LastDataUpdate,
    E.EVT_TYPE AS WorkOrderType,
    E.EVT_PRIORITY AS PriorityCode,
    E.EVT_MRC AS MaintenanceDepartment,
    B.BOO_PERSON AS UpdatedBy,
    E.EVT_TARGET AS TargetCompletionDate,
    E.EVT_OBJECT AS AssetCode,
    B.BOO_HOURS AS ActualLaborHours
FROM R5EVENTS E
JOIN R5BOOKEDHOURS B ON E.EVT_CODE = B.BOO_EVENT

UNION ALL

/* 9. Checklist Item Verified */
SELECT
    E.EVT_CODE AS WorkOrderNumber,
    'Checklist Item Verified' AS ActivityName,
    C.CKV_DATE AS EventTimestamp,
    'Hexagon EAM' AS SourceSystem,
    GETDATE() AS LastDataUpdate,
    E.EVT_TYPE AS WorkOrderType,
    E.EVT_PRIORITY AS PriorityCode,
    E.EVT_MRC AS MaintenanceDepartment,
    C.CKV_ENTEREDBY AS UpdatedBy,
    E.EVT_TARGET AS TargetCompletionDate,
    E.EVT_OBJECT AS AssetCode,
    NULL AS ActualLaborHours
FROM R5EVENTS E
JOIN R5CHECKLISTVALUES C ON E.EVT_CODE = C.CKV_EVENT
WHERE C.CKV_COMPLETED = '+' -- Assuming '+' indicates checked/complete

UNION ALL

/* 10. Work Order Completed */
SELECT
    E.EVT_CODE AS WorkOrderNumber,
    'Work Order Completed' AS ActivityName,
    S.STA_DATE AS EventTimestamp,
    'Hexagon EAM' AS SourceSystem,
    GETDATE() AS LastDataUpdate,
    E.EVT_TYPE AS WorkOrderType,
    E.EVT_PRIORITY AS PriorityCode,
    E.EVT_MRC AS MaintenanceDepartment,
    S.STA_UPDATEUSER AS UpdatedBy,
    E.EVT_TARGET AS TargetCompletionDate,
    E.EVT_OBJECT AS AssetCode,
    NULL AS ActualLaborHours
FROM R5EVENTS E
JOIN R5STATUS S ON E.EVT_CODE = S.STA_EVENT
WHERE S.STA_STATUS = 'COMP' -- Replace with your Completed status code

UNION ALL

/* 11. Quality Control Passed */
SELECT
    E.EVT_CODE AS WorkOrderNumber,
    'Quality Control Passed' AS ActivityName,
    S.STA_DATE AS EventTimestamp,
    'Hexagon EAM' AS SourceSystem,
    GETDATE() AS LastDataUpdate,
    E.EVT_TYPE AS WorkOrderType,
    E.EVT_PRIORITY AS PriorityCode,
    E.EVT_MRC AS MaintenanceDepartment,
    S.STA_UPDATEUSER AS UpdatedBy,
    E.EVT_TARGET AS TargetCompletionDate,
    E.EVT_OBJECT AS AssetCode,
    NULL AS ActualLaborHours
FROM R5EVENTS E
JOIN R5STATUS S ON E.EVT_CODE = S.STA_EVENT
WHERE S.STA_STATUS = 'QC' -- Replace with your QC Passed status code

UNION ALL

/* 12. Work Order Signed Off */
SELECT
    E.EVT_CODE AS WorkOrderNumber,
    'Work Order Signed Off' AS ActivityName,
    S.STA_DATE AS EventTimestamp,
    'Hexagon EAM' AS SourceSystem,
    GETDATE() AS LastDataUpdate,
    E.EVT_TYPE AS WorkOrderType,
    E.EVT_PRIORITY AS PriorityCode,
    E.EVT_MRC AS MaintenanceDepartment,
    S.STA_UPDATEUSER AS UpdatedBy,
    E.EVT_TARGET AS TargetCompletionDate,
    E.EVT_OBJECT AS AssetCode,
    NULL AS ActualLaborHours
FROM R5EVENTS E
JOIN R5STATUS S ON E.EVT_CODE = S.STA_EVENT
WHERE S.STA_STATUS = 'SIGNOFF' -- Replace with your Sign Off status code

UNION ALL

/* 13. Work Order Closed */
SELECT
    E.EVT_CODE AS WorkOrderNumber,
    'Work Order Closed' AS ActivityName,
    S.STA_DATE AS EventTimestamp,
    'Hexagon EAM' AS SourceSystem,
    GETDATE() AS LastDataUpdate,
    E.EVT_TYPE AS WorkOrderType,
    E.EVT_PRIORITY AS PriorityCode,
    E.EVT_MRC AS MaintenanceDepartment,
    S.STA_UPDATEUSER AS UpdatedBy,
    E.EVT_TARGET AS TargetCompletionDate,
    E.EVT_OBJECT AS AssetCode,
    NULL AS ActualLaborHours
FROM R5EVENTS E
JOIN R5STATUS S ON E.EVT_CODE = S.STA_EVENT
WHERE S.STA_STATUS = 'CLOS' -- Replace with your Closed status code

WITH
created_events AS (
    SELECT
        CAST(c.[WorkOrderNumber] AS VARCHAR(100)) AS WorkOrderNumber,
        CAST('Work Order Created' AS VARCHAR(100)) AS ActivityName,
        CAST(c.[CreatedTimestamp] AS TIMESTAMP) AS EventTimestamp,
        CAST('Hexagon EAM' AS VARCHAR(100)) AS SourceSystem,
        CAST(c.[LastDataUpdate] AS TIMESTAMP) AS LastDataUpdate,
        CAST(c.[WorkOrderType] AS VARCHAR(100)) AS WorkOrderType,
        CAST(c.[PriorityCode] AS VARCHAR(100)) AS PriorityCode,
        CAST(c.[MaintenanceDepartment] AS VARCHAR(100)) AS MaintenanceDepartment,
        CAST(c.[CreatedBy] AS VARCHAR(100)) AS UpdatedBy,
        CAST(c.[TargetCompletionDate] AS TIMESTAMP) AS TargetCompletionDate,
        CAST(c.[AssetCode] AS VARCHAR(100)) AS AssetCode,
        CAST(c.[ActualLaborHours] AS DECIMAL(18,2)) AS ActualLaborHours
    FROM [Your work order creation table or view] c
    WHERE c.[CreatedTimestamp] >= CAST('[Start date parameter]' AS TIMESTAMP)
      AND c.[CreatedTimestamp] < CAST('[End date parameter]' AS TIMESTAMP)
      AND [Your company filter]
),
priority_events AS (
    SELECT
        CAST(p.[WorkOrderNumber] AS VARCHAR(100)) AS WorkOrderNumber,
        CAST('Priority Updated' AS VARCHAR(100)) AS ActivityName,
        CAST(p.[PriorityChangedTimestamp] AS TIMESTAMP) AS EventTimestamp,
        CAST('Hexagon EAM' AS VARCHAR(100)) AS SourceSystem,
        CAST(p.[LastDataUpdate] AS TIMESTAMP) AS LastDataUpdate,
        CAST(w.[WorkOrderType] AS VARCHAR(100)) AS WorkOrderType,
        CAST(p.[NewPriorityCode] AS VARCHAR(100)) AS PriorityCode,
        CAST(w.[MaintenanceDepartment] AS VARCHAR(100)) AS MaintenanceDepartment,
        CAST(p.[UpdatedBy] AS VARCHAR(100)) AS UpdatedBy,
        CAST(w.[TargetCompletionDate] AS TIMESTAMP) AS TargetCompletionDate,
        CAST(w.[AssetCode] AS VARCHAR(100)) AS AssetCode,
        CAST(w.[ActualLaborHours] AS DECIMAL(18,2)) AS ActualLaborHours
    FROM [Your priority history table or view] p
    LEFT JOIN [Your work order current or history table or view] w ON w.[WorkOrderNumber] = p.[WorkOrderNumber]
    WHERE p.[PriorityChangedTimestamp] >= CAST('[Start date parameter]' AS TIMESTAMP)
      AND p.[PriorityChangedTimestamp] < CAST('[End date parameter]' AS TIMESTAMP)
      AND [Your company filter]
),
approved_events AS (
    SELECT
        CAST(s.[WorkOrderNumber] AS VARCHAR(100)) AS WorkOrderNumber,
        CAST('Work Order Approved' AS VARCHAR(100)) AS ActivityName,
        CAST(s.[StatusChangedTimestamp] AS TIMESTAMP) AS EventTimestamp,
        CAST('Hexagon EAM' AS VARCHAR(100)) AS SourceSystem,
        CAST(s.[LastDataUpdate] AS TIMESTAMP) AS LastDataUpdate,
        CAST(w.[WorkOrderType] AS VARCHAR(100)) AS WorkOrderType,
        CAST(w.[PriorityCode] AS VARCHAR(100)) AS PriorityCode,
        CAST(w.[MaintenanceDepartment] AS VARCHAR(100)) AS MaintenanceDepartment,
        CAST(s.[UpdatedBy] AS VARCHAR(100)) AS UpdatedBy,
        CAST(w.[TargetCompletionDate] AS TIMESTAMP) AS TargetCompletionDate,
        CAST(w.[AssetCode] AS VARCHAR(100)) AS AssetCode,
        CAST(w.[ActualLaborHours] AS DECIMAL(18,2)) AS ActualLaborHours
    FROM [Your work order status history table or view] s
    LEFT JOIN [Your work order current or history table or view] w ON w.[WorkOrderNumber] = s.[WorkOrderNumber]
    WHERE s.[NewStatus] = '[Your approved status value]'
      AND s.[StatusChangedTimestamp] >= CAST('[Start date parameter]' AS TIMESTAMP)
      AND s.[StatusChangedTimestamp] < CAST('[End date parameter]' AS TIMESTAMP)
      AND [Your company filter]
),
requisition_events AS (
    SELECT
        CAST(r.[WorkOrderNumber] AS VARCHAR(100)) AS WorkOrderNumber,
        CAST('Material Requisition Created' AS VARCHAR(100)) AS ActivityName,
        CAST(r.[RequisitionCreatedTimestamp] AS TIMESTAMP) AS EventTimestamp,
        CAST('Hexagon EAM' AS VARCHAR(100)) AS SourceSystem,
        CAST(r.[LastDataUpdate] AS TIMESTAMP) AS LastDataUpdate,
        CAST(w.[WorkOrderType] AS VARCHAR(100)) AS WorkOrderType,
        CAST(w.[PriorityCode] AS VARCHAR(100)) AS PriorityCode,
        CAST(w.[MaintenanceDepartment] AS VARCHAR(100)) AS MaintenanceDepartment,
        CAST(r.[CreatedBy] AS VARCHAR(100)) AS UpdatedBy,
        CAST(w.[TargetCompletionDate] AS TIMESTAMP) AS TargetCompletionDate,
        CAST(w.[AssetCode] AS VARCHAR(100)) AS AssetCode,
        CAST(w.[ActualLaborHours] AS DECIMAL(18,2)) AS ActualLaborHours
    FROM [Your material requisition table or view] r
    LEFT JOIN [Your work order current or history table or view] w ON w.[WorkOrderNumber] = r.[WorkOrderNumber]
    WHERE r.[RequisitionCreatedTimestamp] >= CAST('[Start date parameter]' AS TIMESTAMP)
      AND r.[RequisitionCreatedTimestamp] < CAST('[End date parameter]' AS TIMESTAMP)
      AND [Your company filter]
),
scheduled_events AS (
    SELECT
        CAST(a.[WorkOrderNumber] AS VARCHAR(100)) AS WorkOrderNumber,
        CAST('Work Order Scheduled' AS VARCHAR(100)) AS ActivityName,
        CAST(a.[ScheduledTimestamp] AS TIMESTAMP) AS EventTimestamp,
        CAST('Hexagon EAM' AS VARCHAR(100)) AS SourceSystem,
        CAST(a.[LastDataUpdate] AS TIMESTAMP) AS LastDataUpdate,
        CAST(w.[WorkOrderType] AS VARCHAR(100)) AS WorkOrderType,
        CAST(w.[PriorityCode] AS VARCHAR(100)) AS PriorityCode,
        CAST(w.[MaintenanceDepartment] AS VARCHAR(100)) AS MaintenanceDepartment,
        CAST(a.[UpdatedBy] AS VARCHAR(100)) AS UpdatedBy,
        CAST(a.[ScheduledStartDate] AS TIMESTAMP) AS TargetCompletionDate,
        CAST(w.[AssetCode] AS VARCHAR(100)) AS AssetCode,
        CAST(w.[ActualLaborHours] AS DECIMAL(18,2)) AS ActualLaborHours
    FROM [Your work order scheduling table or view] a
    LEFT JOIN [Your work order current or history table or view] w ON w.[WorkOrderNumber] = a.[WorkOrderNumber]
    WHERE a.[ScheduledTimestamp] >= CAST('[Start date parameter]' AS TIMESTAMP)
      AND a.[ScheduledTimestamp] < CAST('[End date parameter]' AS TIMESTAMP)
      AND [Your company filter]
),
started_events AS (
    SELECT
        CAST(s.[WorkOrderNumber] AS VARCHAR(100)) AS WorkOrderNumber,
        CAST('Work Order Started' AS VARCHAR(100)) AS ActivityName,
        CAST(s.[StatusChangedTimestamp] AS TIMESTAMP) AS EventTimestamp,
        CAST('Hexagon EAM' AS VARCHAR(100)) AS SourceSystem,
        CAST(s.[LastDataUpdate] AS TIMESTAMP) AS LastDataUpdate,
        CAST(w.[WorkOrderType] AS VARCHAR(100)) AS WorkOrderType,
        CAST(w.[PriorityCode] AS VARCHAR(100)) AS PriorityCode,
        CAST(w.[MaintenanceDepartment] AS VARCHAR(100)) AS MaintenanceDepartment,
        CAST(s.[UpdatedBy] AS VARCHAR(100)) AS UpdatedBy,
        CAST(w.[TargetCompletionDate] AS TIMESTAMP) AS TargetCompletionDate,
        CAST(w.[AssetCode] AS VARCHAR(100)) AS AssetCode,
        CAST(w.[ActualLaborHours] AS DECIMAL(18,2)) AS ActualLaborHours
    FROM [Your work order status history table or view] s
    LEFT JOIN [Your work order current or history table or view] w ON w.[WorkOrderNumber] = s.[WorkOrderNumber]
    WHERE s.[NewStatus] IN ('[Your in progress status value]', '[Your started status value]')
      AND s.[StatusChangedTimestamp] >= CAST('[Start date parameter]' AS TIMESTAMP)
      AND s.[StatusChangedTimestamp] < CAST('[End date parameter]' AS TIMESTAMP)
      AND [Your company filter]
),
issued_events AS (
    SELECT
        CAST(i.[WorkOrderNumber] AS VARCHAR(100)) AS WorkOrderNumber,
        CAST('Material Issued' AS VARCHAR(100)) AS ActivityName,
        CAST(i.[IssueTimestamp] AS TIMESTAMP) AS EventTimestamp,
        CAST('Hexagon EAM' AS VARCHAR(100)) AS SourceSystem,
        CAST(i.[LastDataUpdate] AS TIMESTAMP) AS LastDataUpdate,
        CAST(w.[WorkOrderType] AS VARCHAR(100)) AS WorkOrderType,
        CAST(w.[PriorityCode] AS VARCHAR(100)) AS PriorityCode,
        CAST(w.[MaintenanceDepartment] AS VARCHAR(100)) AS MaintenanceDepartment,
        CAST(i.[IssuedBy] AS VARCHAR(100)) AS UpdatedBy,
        CAST(w.[TargetCompletionDate] AS TIMESTAMP) AS TargetCompletionDate,
        CAST(w.[AssetCode] AS VARCHAR(100)) AS AssetCode,
        CAST(w.[ActualLaborHours] AS DECIMAL(18,2)) AS ActualLaborHours
    FROM [Your material issue table or view] i
    LEFT JOIN [Your work order current or history table or view] w ON w.[WorkOrderNumber] = i.[WorkOrderNumber]
    WHERE i.[IssueTimestamp] >= CAST('[Start date parameter]' AS TIMESTAMP)
      AND i.[IssueTimestamp] < CAST('[End date parameter]' AS TIMESTAMP)
      AND [Your company filter]
),
labor_events AS (
    SELECT
        CAST(l.[WorkOrderNumber] AS VARCHAR(100)) AS WorkOrderNumber,
        CAST('Labor Hours Recorded' AS VARCHAR(100)) AS ActivityName,
        CAST(l.[LaborEntryTimestamp] AS TIMESTAMP) AS EventTimestamp,
        CAST('Hexagon EAM' AS VARCHAR(100)) AS SourceSystem,
        CAST(l.[LastDataUpdate] AS TIMESTAMP) AS LastDataUpdate,
        CAST(w.[WorkOrderType] AS VARCHAR(100)) AS WorkOrderType,
        CAST(w.[PriorityCode] AS VARCHAR(100)) AS PriorityCode,
        CAST(w.[MaintenanceDepartment] AS VARCHAR(100)) AS MaintenanceDepartment,
        CAST(l.[RecordedBy] AS VARCHAR(100)) AS UpdatedBy,
        CAST(w.[TargetCompletionDate] AS TIMESTAMP) AS TargetCompletionDate,
        CAST(w.[AssetCode] AS VARCHAR(100)) AS AssetCode,
        CAST(l.[LaborHours] AS DECIMAL(18,2)) AS ActualLaborHours
    FROM [Your labor transaction table or view] l
    LEFT JOIN [Your work order current or history table or view] w ON w.[WorkOrderNumber] = l.[WorkOrderNumber]
    WHERE l.[LaborEntryTimestamp] >= CAST('[Start date parameter]' AS TIMESTAMP)
      AND l.[LaborEntryTimestamp] < CAST('[End date parameter]' AS TIMESTAMP)
      AND [Your company filter]
),
checklist_events AS (
    SELECT
        CAST(k.[WorkOrderNumber] AS VARCHAR(100)) AS WorkOrderNumber,
        CAST('Checklist Item Verified' AS VARCHAR(100)) AS ActivityName,
        CAST(k.[VerifiedTimestamp] AS TIMESTAMP) AS EventTimestamp,
        CAST('Hexagon EAM' AS VARCHAR(100)) AS SourceSystem,
        CAST(k.[LastDataUpdate] AS TIMESTAMP) AS LastDataUpdate,
        CAST(w.[WorkOrderType] AS VARCHAR(100)) AS WorkOrderType,
        CAST(w.[PriorityCode] AS VARCHAR(100)) AS PriorityCode,
        CAST(w.[MaintenanceDepartment] AS VARCHAR(100)) AS MaintenanceDepartment,
        CAST(k.[VerifiedBy] AS VARCHAR(100)) AS UpdatedBy,
        CAST(w.[TargetCompletionDate] AS TIMESTAMP) AS TargetCompletionDate,
        CAST(w.[AssetCode] AS VARCHAR(100)) AS AssetCode,
        CAST(w.[ActualLaborHours] AS DECIMAL(18,2)) AS ActualLaborHours
    FROM [Your checklist verification table or view] k
    LEFT JOIN [Your work order current or history table or view] w ON w.[WorkOrderNumber] = k.[WorkOrderNumber]
    WHERE k.[VerifiedTimestamp] >= CAST('[Start date parameter]' AS TIMESTAMP)
      AND k.[VerifiedTimestamp] < CAST('[End date parameter]' AS TIMESTAMP)
      AND [Your company filter]
),
completed_events AS (
    SELECT
        CAST(s.[WorkOrderNumber] AS VARCHAR(100)) AS WorkOrderNumber,
        CAST('Work Order Completed' AS VARCHAR(100)) AS ActivityName,
        CAST(s.[StatusChangedTimestamp] AS TIMESTAMP) AS EventTimestamp,
        CAST('Hexagon EAM' AS VARCHAR(100)) AS SourceSystem,
        CAST(s.[LastDataUpdate] AS TIMESTAMP) AS LastDataUpdate,
        CAST(w.[WorkOrderType] AS VARCHAR(100)) AS WorkOrderType,
        CAST(w.[PriorityCode] AS VARCHAR(100)) AS PriorityCode,
        CAST(w.[MaintenanceDepartment] AS VARCHAR(100)) AS MaintenanceDepartment,
        CAST(s.[UpdatedBy] AS VARCHAR(100)) AS UpdatedBy,
        CAST(w.[TargetCompletionDate] AS TIMESTAMP) AS TargetCompletionDate,
        CAST(w.[AssetCode] AS VARCHAR(100)) AS AssetCode,
        CAST(w.[ActualLaborHours] AS DECIMAL(18,2)) AS ActualLaborHours
    FROM [Your work order status history table or view] s
    LEFT JOIN [Your work order current or history table or view] w ON w.[WorkOrderNumber] = s.[WorkOrderNumber]
    WHERE s.[NewStatus] = '[Your completed status value]'
      AND s.[StatusChangedTimestamp] >= CAST('[Start date parameter]' AS TIMESTAMP)
      AND s.[StatusChangedTimestamp] < CAST('[End date parameter]' AS TIMESTAMP)
      AND [Your company filter]
),
quality_events AS (
    SELECT
        CAST(q.[WorkOrderNumber] AS VARCHAR(100)) AS WorkOrderNumber,
        CAST('Quality Control Passed' AS VARCHAR(100)) AS ActivityName,
        CAST(q.[QualityApprovalTimestamp] AS TIMESTAMP) AS EventTimestamp,
        CAST('Hexagon EAM' AS VARCHAR(100)) AS SourceSystem,
        CAST(q.[LastDataUpdate] AS TIMESTAMP) AS LastDataUpdate,
        CAST(w.[WorkOrderType] AS VARCHAR(100)) AS WorkOrderType,
        CAST(w.[PriorityCode] AS VARCHAR(100)) AS PriorityCode,
        CAST(w.[MaintenanceDepartment] AS VARCHAR(100)) AS MaintenanceDepartment,
        CAST(q.[ApprovedBy] AS VARCHAR(100)) AS UpdatedBy,
        CAST(w.[TargetCompletionDate] AS TIMESTAMP) AS TargetCompletionDate,
        CAST(w.[AssetCode] AS VARCHAR(100)) AS AssetCode,
        CAST(w.[ActualLaborHours] AS DECIMAL(18,2)) AS ActualLaborHours
    FROM [Your quality approval table or view] q
    LEFT JOIN [Your work order current or history table or view] w ON w.[WorkOrderNumber] = q.[WorkOrderNumber]
    WHERE q.[QualityResult] = '[Your quality passed value]'
      AND q.[QualityApprovalTimestamp] >= CAST('[Start date parameter]' AS TIMESTAMP)
      AND q.[QualityApprovalTimestamp] < CAST('[End date parameter]' AS TIMESTAMP)
      AND [Your company filter]
),
signoff_events AS (
    SELECT
        CAST(f.[WorkOrderNumber] AS VARCHAR(100)) AS WorkOrderNumber,
        CAST('Work Order Signed Off' AS VARCHAR(100)) AS ActivityName,
        CAST(f.[SignOffTimestamp] AS TIMESTAMP) AS EventTimestamp,
        CAST('Hexagon EAM' AS VARCHAR(100)) AS SourceSystem,
        CAST(f.[LastDataUpdate] AS TIMESTAMP) AS LastDataUpdate,
        CAST(w.[WorkOrderType] AS VARCHAR(100)) AS WorkOrderType,
        CAST(w.[PriorityCode] AS VARCHAR(100)) AS PriorityCode,
        CAST(w.[MaintenanceDepartment] AS VARCHAR(100)) AS MaintenanceDepartment,
        CAST(f.[SignedOffBy] AS VARCHAR(100)) AS UpdatedBy,
        CAST(w.[TargetCompletionDate] AS TIMESTAMP) AS TargetCompletionDate,
        CAST(w.[AssetCode] AS VARCHAR(100)) AS AssetCode,
        CAST(w.[ActualLaborHours] AS DECIMAL(18,2)) AS ActualLaborHours
    FROM [Your work order sign-off table or view] f
    LEFT JOIN [Your work order current or history table or view] w ON w.[WorkOrderNumber] = f.[WorkOrderNumber]
    WHERE f.[SignOffTimestamp] >= CAST('[Start date parameter]' AS TIMESTAMP)
      AND f.[SignOffTimestamp] < CAST('[End date parameter]' AS TIMESTAMP)
      AND [Your company filter]
),
closed_events AS (
    SELECT
        CAST(s.[WorkOrderNumber] AS VARCHAR(100)) AS WorkOrderNumber,
        CAST('Work Order Closed' AS VARCHAR(100)) AS ActivityName,
        CAST(s.[StatusChangedTimestamp] AS TIMESTAMP) AS EventTimestamp,
        CAST('Hexagon EAM' AS VARCHAR(100)) AS SourceSystem,
        CAST(s.[LastDataUpdate] AS TIMESTAMP) AS LastDataUpdate,
        CAST(w.[WorkOrderType] AS VARCHAR(100)) AS WorkOrderType,
        CAST(w.[PriorityCode] AS VARCHAR(100)) AS PriorityCode,
        CAST(w.[MaintenanceDepartment] AS VARCHAR(100)) AS MaintenanceDepartment,
        CAST(s.[UpdatedBy] AS VARCHAR(100)) AS UpdatedBy,
        CAST(w.[TargetCompletionDate] AS TIMESTAMP) AS TargetCompletionDate,
        CAST(w.[AssetCode] AS VARCHAR(100)) AS AssetCode,
        CAST(w.[ActualLaborHours] AS DECIMAL(18,2)) AS ActualLaborHours
    FROM [Your work order status history table or view] s
    LEFT JOIN [Your work order current or history table or view] w ON w.[WorkOrderNumber] = s.[WorkOrderNumber]
    WHERE s.[NewStatus] = '[Your closed status value]'
      AND s.[StatusChangedTimestamp] >= CAST('[Start date parameter]' AS TIMESTAMP)
      AND s.[StatusChangedTimestamp] < CAST('[End date parameter]' AS TIMESTAMP)
      AND [Your company filter]
)
SELECT WorkOrderNumber, ActivityName, EventTimestamp, SourceSystem, LastDataUpdate, WorkOrderType, PriorityCode, MaintenanceDepartment, UpdatedBy, TargetCompletionDate, AssetCode, ActualLaborHours FROM created_events
UNION ALL
SELECT WorkOrderNumber, ActivityName, EventTimestamp, SourceSystem, LastDataUpdate, WorkOrderType, PriorityCode, MaintenanceDepartment, UpdatedBy, TargetCompletionDate, AssetCode, ActualLaborHours FROM priority_events
UNION ALL
SELECT WorkOrderNumber, ActivityName, EventTimestamp, SourceSystem, LastDataUpdate, WorkOrderType, PriorityCode, MaintenanceDepartment, UpdatedBy, TargetCompletionDate, AssetCode, ActualLaborHours FROM approved_events
UNION ALL
SELECT WorkOrderNumber, ActivityName, EventTimestamp, SourceSystem, LastDataUpdate, WorkOrderType, PriorityCode, MaintenanceDepartment, UpdatedBy, TargetCompletionDate, AssetCode, ActualLaborHours FROM requisition_events
UNION ALL
SELECT WorkOrderNumber, ActivityName, EventTimestamp, SourceSystem, LastDataUpdate, WorkOrderType, PriorityCode, MaintenanceDepartment, UpdatedBy, TargetCompletionDate, AssetCode, ActualLaborHours FROM scheduled_events
UNION ALL
SELECT WorkOrderNumber, ActivityName, EventTimestamp, SourceSystem, LastDataUpdate, WorkOrderType, PriorityCode, MaintenanceDepartment, UpdatedBy, TargetCompletionDate, AssetCode, ActualLaborHours FROM started_events
UNION ALL
SELECT WorkOrderNumber, ActivityName, EventTimestamp, SourceSystem, LastDataUpdate, WorkOrderType, PriorityCode, MaintenanceDepartment, UpdatedBy, TargetCompletionDate, AssetCode, ActualLaborHours FROM issued_events
UNION ALL
SELECT WorkOrderNumber, ActivityName, EventTimestamp, SourceSystem, LastDataUpdate, WorkOrderType, PriorityCode, MaintenanceDepartment, UpdatedBy, TargetCompletionDate, AssetCode, ActualLaborHours FROM labor_events
UNION ALL
SELECT WorkOrderNumber, ActivityName, EventTimestamp, SourceSystem, LastDataUpdate, WorkOrderType, PriorityCode, MaintenanceDepartment, UpdatedBy, TargetCompletionDate, AssetCode, ActualLaborHours FROM checklist_events
UNION ALL
SELECT WorkOrderNumber, ActivityName, EventTimestamp, SourceSystem, LastDataUpdate, WorkOrderType, PriorityCode, MaintenanceDepartment, UpdatedBy, TargetCompletionDate, AssetCode, ActualLaborHours FROM completed_events
UNION ALL
SELECT WorkOrderNumber, ActivityName, EventTimestamp, SourceSystem, LastDataUpdate, WorkOrderType, PriorityCode, MaintenanceDepartment, UpdatedBy, TargetCompletionDate, AssetCode, ActualLaborHours FROM quality_events
UNION ALL
SELECT WorkOrderNumber, ActivityName, EventTimestamp, SourceSystem, LastDataUpdate, WorkOrderType, PriorityCode, MaintenanceDepartment, UpdatedBy, TargetCompletionDate, AssetCode, ActualLaborHours FROM signoff_events
UNION ALL
SELECT WorkOrderNumber, ActivityName, EventTimestamp, SourceSystem, LastDataUpdate, WorkOrderType, PriorityCode, MaintenanceDepartment, UpdatedBy, TargetCompletionDate, AssetCode, ActualLaborHours FROM closed_events
ORDER BY WorkOrderNumber, EventTimestamp, ActivityName;