Datatemplate voor assetonderhoud

/* Activity 1: Maintenance Notification Created */
SELECT
    CAST(N.MaintenanceOrder AS VARCHAR(20)) AS MaintenanceWorkOrder,
    'Maintenance Notification Created' AS Activity,
    TO_TIMESTAMP(CONCAT(CONCAT(N.CreationDate, ' '), N.CreationTime), 'YYYYMMDD HH24MISS') AS EventTimestamp,
    'SAP S/4HANA' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    N.MaintenanceOrderType,
    N.MaintenancePriority,
    N.Equipment AS EquipmentNumber,
    N.FunctionalLocation,
    N.MaintenancePlant,
    N.MaintenancePlannerGroup AS ResponsiblePlannerGroup,
    N.MainWorkCenter,
    N.MaintenanceNotification AS NotificationNumber,
    CAST(NULL AS DATE) AS TargetFinishDate,
    N.CreatedByUser,
    O.SystemStatusName AS SystemStatus
FROM I_MaintenanceNotification N
JOIN I_MaintenanceOrder O ON N.MaintenanceOrder = O.MaintenanceOrder
WHERE N.MaintenanceOrder IS NOT NULL AND N.MaintenanceOrder <> ''

UNION ALL

/* Activity 2: Maintenance Order Created */
SELECT
    CAST(O.MaintenanceOrder AS VARCHAR(20)) AS MaintenanceWorkOrder,
    'Maintenance Order Created' AS Activity,
    O.CreationDate AS EventTimestamp,
    'SAP S/4HANA' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    O.MaintenanceOrderType,
    O.MaintenancePriority,
    O.Equipment AS EquipmentNumber,
    O.FunctionalLocation,
    O.MaintenancePlant,
    O.ResponsiblePlannerGroup,
    O.MainWorkCenter,
    O.MaintenanceNotification AS NotificationNumber,
    O.BasicEndDateTime AS TargetFinishDate,
    O.CreatedByUser,
    O.SystemStatusName AS SystemStatus
FROM I_MaintenanceOrder O

UNION ALL

/* Activity 3: Maintenance Priority Changed */
SELECT
    CAST(O.MaintenanceOrder AS VARCHAR(20)) AS MaintenanceWorkOrder,
    'Maintenance Priority Changed' AS Activity,
    TO_TIMESTAMP(CONCAT(CONCAT(H.UDATE, ' '), H.UTIME), 'YYYYMMDD HH24MISS') AS EventTimestamp,
    'SAP S/4HANA' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    O.MaintenanceOrderType,
    O.MaintenancePriority,
    O.Equipment AS EquipmentNumber,
    O.FunctionalLocation,
    O.MaintenancePlant,
    O.ResponsiblePlannerGroup,
    O.MainWorkCenter,
    O.MaintenanceNotification AS NotificationNumber,
    O.BasicEndDateTime AS TargetFinishDate,
    H.USERNAME AS CreatedByUser,
    O.SystemStatusName AS SystemStatus
FROM CDHDR H
JOIN CDPOS P ON H.OBJECTCLAS = P.OBJECTCLAS AND H.OBJECTID = P.OBJECTID AND H.CHANGENR = P.CHANGENR
JOIN I_MaintenanceOrder O ON O.MaintenanceOrder = H.OBJECTID
WHERE H.OBJECTCLAS = 'ORDER' 
  AND P.TABNAME = 'AFKO' 
  AND P.FNAME = 'PRIOK'

UNION ALL

/* Activity 4: Maintenance Order Released */
SELECT
    CAST(O.MaintenanceOrder AS VARCHAR(20)) AS MaintenanceWorkOrder,
    'Maintenance Order Released' AS Activity,
    TO_TIMESTAMP(CONCAT(CONCAT(J.UDATE, ' '), J.UTIME), 'YYYYMMDD HH24MISS') AS EventTimestamp,
    'SAP S/4HANA' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    O.MaintenanceOrderType,
    O.MaintenancePriority,
    O.Equipment AS EquipmentNumber,
    O.FunctionalLocation,
    O.MaintenancePlant,
    O.ResponsiblePlannerGroup,
    O.MainWorkCenter,
    O.MaintenanceNotification AS NotificationNumber,
    O.BasicEndDateTime AS TargetFinishDate,
    J.USNAM AS CreatedByUser,
    O.SystemStatusName AS SystemStatus
FROM JCDS J
JOIN I_MaintenanceOrder O ON J.OBJNR = CONCAT('OR', O.MaintenanceOrder)
WHERE J.STAT = 'I0002' /* REL Status */
  AND J.INACT = ' '

UNION ALL

/* Activity 5: Material Component Added */
SELECT
    CAST(C.MaintenanceOrder AS VARCHAR(20)) AS MaintenanceWorkOrder,
    'Material Component Added' AS Activity,
    TO_TIMESTAMP(CONCAT(CONCAT(C.CreationDate, ' '), COALESCE(C.CreationTime, '000000')), 'YYYYMMDD HH24MISS') AS EventTimestamp,
    'SAP S/4HANA' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    O.MaintenanceOrderType,
    O.MaintenancePriority,
    O.Equipment AS EquipmentNumber,
    O.FunctionalLocation,
    O.MaintenancePlant,
    O.ResponsiblePlannerGroup,
    O.MainWorkCenter,
    O.MaintenanceNotification AS NotificationNumber,
    O.BasicEndDateTime AS TargetFinishDate,
    O.CreatedByUser,
    O.SystemStatusName AS SystemStatus
FROM I_MaintenanceOrderComponent C
JOIN I_MaintenanceOrder O ON C.MaintenanceOrder = O.MaintenanceOrder

UNION ALL

/* Activity 6: Purchase Requisition Created */
SELECT
    CAST(O.MaintenanceOrder AS VARCHAR(20)) AS MaintenanceWorkOrder,
    'Purchase Requisition Created' AS Activity,
    PR.CreationDate AS EventTimestamp,
    'SAP S/4HANA' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    O.MaintenanceOrderType,
    O.MaintenancePriority,
    O.Equipment AS EquipmentNumber,
    O.FunctionalLocation,
    O.MaintenancePlant,
    O.ResponsiblePlannerGroup,
    O.MainWorkCenter,
    O.MaintenanceNotification AS NotificationNumber,
    O.BasicEndDateTime AS TargetFinishDate,
    PR.CreatedByUser,
    O.SystemStatusName AS SystemStatus
FROM I_PurchasingDocumentItem PR
JOIN I_MaintenanceOrder O ON PR.ServiceMaintenanceOrder = O.MaintenanceOrder
WHERE PR.PurchasingDocumentCategory = 'B' /* Requisition */

UNION ALL

/* Activity 7: Goods Issue Posted */
SELECT
    CAST(O.MaintenanceOrder AS VARCHAR(20)) AS MaintenanceWorkOrder,
    'Goods Issue Posted' AS Activity,
    TO_TIMESTAMP(CONCAT(CONCAT(MD.CreationDate, ' '), MD.CreationTime), 'YYYYMMDD HH24MISS') AS EventTimestamp,
    'SAP S/4HANA' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    O.MaintenanceOrderType,
    O.MaintenancePriority,
    O.Equipment AS EquipmentNumber,
    O.FunctionalLocation,
    O.MaintenancePlant,
    O.ResponsiblePlannerGroup,
    O.MainWorkCenter,
    O.MaintenanceNotification AS NotificationNumber,
    O.BasicEndDateTime AS TargetFinishDate,
    MD.CreatedByUser,
    O.SystemStatusName AS SystemStatus
FROM I_MaterialDocumentItem MD
JOIN I_MaintenanceOrder O ON MD.MaintenanceOrder = O.MaintenanceOrder
WHERE MD.GoodsMovementType = '261'

UNION ALL

/* Activity 8: Work Order Scheduled (Operation Dispatched) */
SELECT
    CAST(O.MaintenanceOrder AS VARCHAR(20)) AS MaintenanceWorkOrder,
    'Work Order Scheduled' AS Activity,
    TO_TIMESTAMP(CONCAT(CONCAT(J.UDATE, ' '), J.UTIME), 'YYYYMMDD HH24MISS') AS EventTimestamp,
    'SAP S/4HANA' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    O.MaintenanceOrderType,
    O.MaintenancePriority,
    O.Equipment AS EquipmentNumber,
    O.FunctionalLocation,
    O.MaintenancePlant,
    O.ResponsiblePlannerGroup,
    O.MainWorkCenter,
    O.MaintenanceNotification AS NotificationNumber,
    O.BasicEndDateTime AS TargetFinishDate,
    J.USNAM AS CreatedByUser,
    O.SystemStatusName AS SystemStatus
FROM JCDS J
JOIN I_MaintenanceOrderOperation OP ON J.OBJNR = OP.MaintenanceOrderOperationInternalID
JOIN I_MaintenanceOrder O ON OP.MaintenanceOrder = O.MaintenanceOrder
WHERE J.STAT = 'I0019' /* DSP Dispatched Status */
  AND J.INACT = ' '

UNION ALL

/* Activity 9: Order Operation Confirmed */
SELECT
    CAST(CNF.MaintenanceOrder AS VARCHAR(20)) AS MaintenanceWorkOrder,
    'Order Operation Confirmed' AS Activity,
    TO_TIMESTAMP(CONCAT(CONCAT(CNF.CreationDate, ' '), CNF.CreationTime), 'YYYYMMDD HH24MISS') AS EventTimestamp,
    'SAP S/4HANA' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    O.MaintenanceOrderType,
    O.MaintenancePriority,
    O.Equipment AS EquipmentNumber,
    O.FunctionalLocation,
    O.MaintenancePlant,
    O.ResponsiblePlannerGroup,
    O.MainWorkCenter,
    O.MaintenanceNotification AS NotificationNumber,
    O.BasicEndDateTime AS TargetFinishDate,
    CNF.CreatedByUser,
    O.SystemStatusName AS SystemStatus
FROM I_MaintOrderConfirmation CNF
JOIN I_MaintenanceOrder O ON CNF.MaintenanceOrder = O.MaintenanceOrder
WHERE CNF.IsReversed = ' '

UNION ALL

/* Activity 10: Measurement Reading Recorded */
SELECT
    CAST(O.MaintenanceOrder AS VARCHAR(20)) AS MaintenanceWorkOrder,
    'Measurement Reading Recorded' AS Activity,
    TO_TIMESTAMP(CONCAT(CONCAT(M.CreationDate, ' '), M.CreationTime), 'YYYYMMDD HH24MISS') AS EventTimestamp,
    'SAP S/4HANA' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    O.MaintenanceOrderType,
    O.MaintenancePriority,
    O.Equipment AS EquipmentNumber,
    O.FunctionalLocation,
    O.MaintenancePlant,
    O.ResponsiblePlannerGroup,
    O.MainWorkCenter,
    O.MaintenanceNotification AS NotificationNumber,
    O.BasicEndDateTime AS TargetFinishDate,
    M.CreatedByUser,
    O.SystemStatusName AS SystemStatus
FROM I_MeasurementDocument M
JOIN I_MaintenanceOrder O ON M.MaintenanceOrder = O.MaintenanceOrder

UNION ALL

/* Activity 11: Technical Completion Set */
SELECT
    CAST(O.MaintenanceOrder AS VARCHAR(20)) AS MaintenanceWorkOrder,
    'Technical Completion Set' AS Activity,
    TO_TIMESTAMP(CONCAT(CONCAT(J.UDATE, ' '), J.UTIME), 'YYYYMMDD HH24MISS') AS EventTimestamp,
    'SAP S/4HANA' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    O.MaintenanceOrderType,
    O.MaintenancePriority,
    O.Equipment AS EquipmentNumber,
    O.FunctionalLocation,
    O.MaintenancePlant,
    O.ResponsiblePlannerGroup,
    O.MainWorkCenter,
    O.MaintenanceNotification AS NotificationNumber,
    O.BasicEndDateTime AS TargetFinishDate,
    J.USNAM AS CreatedByUser,
    O.SystemStatusName AS SystemStatus
FROM JCDS J
JOIN I_MaintenanceOrder O ON J.OBJNR = CONCAT('OR', O.MaintenanceOrder)
WHERE J.STAT = 'I0045' /* TECO Status */
  AND J.INACT = ' '

UNION ALL

/* Activity 12: Technical Completion Reversed */
SELECT
    CAST(O.MaintenanceOrder AS VARCHAR(20)) AS MaintenanceWorkOrder,
    'Technical Completion Reversed' AS Activity,
    TO_TIMESTAMP(CONCAT(CONCAT(J.UDATE, ' '), J.UTIME), 'YYYYMMDD HH24MISS') AS EventTimestamp,
    'SAP S/4HANA' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    O.MaintenanceOrderType,
    O.MaintenancePriority,
    O.Equipment AS EquipmentNumber,
    O.FunctionalLocation,
    O.MaintenancePlant,
    O.ResponsiblePlannerGroup,
    O.MainWorkCenter,
    O.MaintenanceNotification AS NotificationNumber,
    O.BasicEndDateTime AS TargetFinishDate,
    J.USNAM AS CreatedByUser,
    O.SystemStatusName AS SystemStatus
FROM JCDS J
JOIN I_MaintenanceOrder O ON J.OBJNR = CONCAT('OR', O.MaintenanceOrder)
WHERE J.STAT = 'I0045' /* TECO Status */
  AND J.INACT = 'X' /* Inactive indicates it was removed/reversed */
  AND J.CHIND = 'U' /* Update indicator */

UNION ALL

/* Activity 13: Order Cost Settled */
SELECT
    CAST(O.MaintenanceOrder AS VARCHAR(20)) AS MaintenanceWorkOrder,
    'Order Cost Settled' AS Activity,
    JE.CreationDateTime AS EventTimestamp,
    'SAP S/4HANA' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    O.MaintenanceOrderType,
    O.MaintenancePriority,
    O.Equipment AS EquipmentNumber,
    O.FunctionalLocation,
    O.MaintenancePlant,
    O.ResponsiblePlannerGroup,
    O.MainWorkCenter,
    O.MaintenanceNotification AS NotificationNumber,
    O.BasicEndDateTime AS TargetFinishDate,
    JE.CreatedByUser,
    O.SystemStatusName AS SystemStatus
FROM I_ActualPlanJrnlEntryItem JE
JOIN I_MaintenanceOrder O ON JE.OrderID = O.MaintenanceOrder
WHERE JE.BusinessTransactionType = 'KOAO' /* Settlement */

UNION ALL

/* Activity 14: Business Completion Set */
SELECT
    CAST(O.MaintenanceOrder AS VARCHAR(20)) AS MaintenanceWorkOrder,
    'Business Completion Set' AS Activity,
    TO_TIMESTAMP(CONCAT(CONCAT(J.UDATE, ' '), J.UTIME), 'YYYYMMDD HH24MISS') AS EventTimestamp,
    'SAP S/4HANA' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    O.MaintenanceOrderType,
    O.MaintenancePriority,
    O.Equipment AS EquipmentNumber,
    O.FunctionalLocation,
    O.MaintenancePlant,
    O.ResponsiblePlannerGroup,
    O.MainWorkCenter,
    O.MaintenanceNotification AS NotificationNumber,
    O.BasicEndDateTime AS TargetFinishDate,
    J.USNAM AS CreatedByUser,
    O.SystemStatusName AS SystemStatus
FROM JCDS J
JOIN I_MaintenanceOrder O ON J.OBJNR = CONCAT('OR', O.MaintenanceOrder)
WHERE J.STAT = 'I0046' /* CLSD Status */
  AND J.INACT = ' '

WITH
params AS (
    SELECT
        CAST('[Start date and time]' AS TIMESTAMP) AS start_ts,
        CAST('[End date and time]' AS TIMESTAMP) AS end_ts,
        CAST('[Your SAP system identifier]' AS VARCHAR(100)) AS source_system,
        CAST('[Your extraction timestamp]' AS TIMESTAMP) AS last_data_update
),
order_base AS (
    SELECT
        a.[Order number field] AS MaintenanceWorkOrder,
        a.[Order type field] AS MaintenanceOrderType,
        a.[Priority field] AS MaintenancePriority,
        a.[Equipment field] AS EquipmentNumber,
        a.[Functional location field] AS FunctionalLocation,
        a.[Maintenance plant field] AS MaintenancePlant,
        a.[Planner group field] AS ResponsiblePlannerGroup,
        a.[Main work center field] AS MainWorkCenter,
        a.[Notification number field] AS NotificationNumber,
        a.[Target finish date field] AS TargetFinishDate,
        a.[Created by field] AS CreatedByUser,
        a.[System status field] AS SystemStatus,
        a.[Creation date field] AS OrderCreationDate,
        a.[Creation time field] AS OrderCreationTime
    FROM AUFK a
    WHERE a.[Order number field] IS NOT NULL
      AND a.[Company code field] = '[Your company code filter]'
),
base_events AS (
    SELECT
        o.MaintenanceWorkOrder,
        'Maintenance Notification Created' AS Activity,
        CAST(q.[Notification creation timestamp field] AS TIMESTAMP) AS EventTimestamp,
        o.MaintenanceOrderType, o.MaintenancePriority, o.EquipmentNumber,
        o.FunctionalLocation, o.MaintenancePlant, o.ResponsiblePlannerGroup,
        o.MainWorkCenter, o.NotificationNumber, o.TargetFinishDate,
        o.CreatedByUser, o.SystemStatus
    FROM order_base o
    JOIN QMEL q ON q.[Notification number field] = o.NotificationNumber
    CROSS JOIN params p
    WHERE q.[Notification creation timestamp field] >= p.start_ts
      AND q.[Notification creation timestamp field] < p.end_ts

    UNION ALL
    SELECT o.MaintenanceWorkOrder, 'Maintenance Order Created',
           CAST(o.OrderCreationDate || ' ' || o.OrderCreationTime AS TIMESTAMP),
           o.MaintenanceOrderType, o.MaintenancePriority, o.EquipmentNumber,
           o.FunctionalLocation, o.MaintenancePlant, o.ResponsiblePlannerGroup,
           o.MainWorkCenter, o.NotificationNumber, o.TargetFinishDate,
           o.CreatedByUser, o.SystemStatus
    FROM order_base o CROSS JOIN params p
    WHERE CAST(o.OrderCreationDate || ' ' || o.OrderCreationTime AS TIMESTAMP) >= p.start_ts
      AND CAST(o.OrderCreationDate || ' ' || o.OrderCreationTime AS TIMESTAMP) < p.end_ts

    UNION ALL
    SELECT o.MaintenanceWorkOrder, 'Maintenance Priority Changed',
           CAST(c.[Change timestamp field] AS TIMESTAMP),
           o.MaintenanceOrderType, c.[New priority value field], o.EquipmentNumber,
           o.FunctionalLocation, o.MaintenancePlant, o.ResponsiblePlannerGroup,
           o.MainWorkCenter, o.NotificationNumber, o.TargetFinishDate,
           o.CreatedByUser, o.SystemStatus
    FROM order_base o
    JOIN [Your order change document table] h ON h.[Order object identifier field] = o.MaintenanceWorkOrder
    JOIN [Your change document item table] c ON c.[Change document number field] = h.[Change document number field]
    CROSS JOIN params p
    WHERE c.[Changed field name field] = '[Your priority field name]'
      AND CAST(c.[Change timestamp field] AS TIMESTAMP) >= p.start_ts
      AND CAST(c.[Change timestamp field] AS TIMESTAMP) < p.end_ts

    UNION ALL
    SELECT o.MaintenanceWorkOrder, 'Maintenance Order Released',
           CAST(s.[Status change timestamp field] AS TIMESTAMP),
           o.MaintenanceOrderType, o.MaintenancePriority, o.EquipmentNumber,
           o.FunctionalLocation, o.MaintenancePlant, o.ResponsiblePlannerGroup,
           o.MainWorkCenter, o.NotificationNumber, o.TargetFinishDate,
           o.CreatedByUser, 'REL'
    FROM order_base o
    JOIN [Your status history table] s ON s.[Order number field] = o.MaintenanceWorkOrder
    CROSS JOIN params p
    WHERE s.[Status code field] = 'REL'
      AND s.[Status change timestamp field] >= p.start_ts
      AND s.[Status change timestamp field] < p.end_ts

    UNION ALL
    SELECT o.MaintenanceWorkOrder, 'Material Component Added',
           CAST(r.[Reservation creation timestamp field] AS TIMESTAMP),
           o.MaintenanceOrderType, o.MaintenancePriority, o.EquipmentNumber,
           o.FunctionalLocation, o.MaintenancePlant, o.ResponsiblePlannerGroup,
           o.MainWorkCenter, o.NotificationNumber, o.TargetFinishDate,
           o.CreatedByUser, o.SystemStatus
    FROM order_base o
    JOIN RESB r ON r.[Order number field] = o.MaintenanceWorkOrder
    CROSS JOIN params p
    WHERE r.[Reservation line creation timestamp field] >= p.start_ts
      AND r.[Reservation line creation timestamp field] < p.end_ts

    UNION ALL
    SELECT o.MaintenanceWorkOrder, 'Purchase Requisition Created',
           CAST(e.[Purchase requisition creation timestamp field] AS TIMESTAMP),
           o.MaintenanceOrderType, o.MaintenancePriority, o.EquipmentNumber,
           o.FunctionalLocation, o.MaintenancePlant, o.ResponsiblePlannerGroup,
           o.MainWorkCenter, o.NotificationNumber, o.TargetFinishDate,
           o.CreatedByUser, o.SystemStatus
    FROM order_base o
    JOIN EBAN e ON e.[Order number field] = o.MaintenanceWorkOrder
    CROSS JOIN params p
    WHERE e.[Purchase requisition creation timestamp field] >= p.start_ts
      AND e.[Purchase requisition creation timestamp field] < p.end_ts

    UNION ALL
    SELECT o.MaintenanceWorkOrder, 'Goods Issue Posted',
           CAST(m.[Material document timestamp field] AS TIMESTAMP),
           o.MaintenanceOrderType, o.MaintenancePriority, o.EquipmentNumber,
           o.FunctionalLocation, o.MaintenancePlant, o.ResponsiblePlannerGroup,
           o.MainWorkCenter, o.NotificationNumber, o.TargetFinishDate,
           o.CreatedByUser, o.SystemStatus
    FROM order_base o
    JOIN MSEG m ON m.[Order number field] = o.MaintenanceWorkOrder
    JOIN MKPF k ON k.[Material document number field] = m.[Material document number field]
               AND k.[Material document year field] = m.[Material document year field]
    CROSS JOIN params p
    WHERE m.[Movement type field] IN ('201', '261')
      AND k.[Material document timestamp field] >= p.start_ts
      AND k.[Material document timestamp field] < p.end_ts

    UNION ALL
    SELECT o.MaintenanceWorkOrder, 'Work Order Scheduled',
           CAST(v.[Dispatch status timestamp field] AS TIMESTAMP),
           o.MaintenanceOrderType, o.MaintenancePriority, o.EquipmentNumber,
           o.FunctionalLocation, o.MaintenancePlant, o.ResponsiblePlannerGroup,
           o.MainWorkCenter, o.NotificationNumber, o.TargetFinishDate,
           o.CreatedByUser, o.SystemStatus
    FROM order_base o
    JOIN AFVC v ON v.[Order number field] = o.MaintenanceWorkOrder
    CROSS JOIN params p
    WHERE v.[Dispatch status field] = '[Your dispatch status value]'
      AND v.[Dispatch status timestamp field] >= p.start_ts
      AND v.[Dispatch status timestamp field] < p.end_ts

    UNION ALL
    SELECT o.MaintenanceWorkOrder, 'Order Operation Confirmed',
           CAST(f.[Confirmation timestamp field] AS TIMESTAMP),
           o.MaintenanceOrderType, o.MaintenancePriority, o.EquipmentNumber,
           o.FunctionalLocation, o.MaintenancePlant, o.ResponsiblePlannerGroup,
           o.MainWorkCenter, o.NotificationNumber, o.TargetFinishDate,
           o.CreatedByUser, o.SystemStatus
    FROM order_base o
    JOIN AFRU f ON f.[Order number field] = o.MaintenanceWorkOrder
    CROSS JOIN params p
    WHERE f.[Confirmation timestamp field] >= p.start_ts
      AND f.[Confirmation timestamp field] < p.end_ts

    UNION ALL
    SELECT o.MaintenanceWorkOrder, 'Measurement Reading Recorded',
           CAST(i.[Measurement document timestamp field] AS TIMESTAMP),
           o.MaintenanceOrderType, o.MaintenancePriority, o.EquipmentNumber,
           o.FunctionalLocation, o.MaintenancePlant, o.ResponsiblePlannerGroup,
           o.MainWorkCenter, o.NotificationNumber, o.TargetFinishDate,
           o.CreatedByUser, o.SystemStatus
    FROM order_base o
    JOIN IMRG i ON i.[Equipment field] = o.EquipmentNumber
    CROSS JOIN params p
    WHERE i.[Measurement document timestamp field] >= p.start_ts
      AND i.[Measurement document timestamp field] < p.end_ts

    UNION ALL
    SELECT o.MaintenanceWorkOrder, 'Technical Completion Set',
           CAST(s.[Status change timestamp field] AS TIMESTAMP),
           o.MaintenanceOrderType, o.MaintenancePriority, o.EquipmentNumber,
           o.FunctionalLocation, o.MaintenancePlant, o.ResponsiblePlannerGroup,
           o.MainWorkCenter, o.NotificationNumber, o.TargetFinishDate,
           o.CreatedByUser, 'TECO'
    FROM order_base o
    JOIN [Your status history table] s ON s.[Order number field] = o.MaintenanceWorkOrder
    CROSS JOIN params p
    WHERE s.[Status code field] = 'TECO'
      AND s.[Status change timestamp field] >= p.start_ts
      AND s.[Status change timestamp field] < p.end_ts

    UNION ALL
    SELECT o.MaintenanceWorkOrder, 'Technical Completion Reversed',
           CAST(s.[Status change timestamp field] AS TIMESTAMP),
           o.MaintenanceOrderType, o.MaintenancePriority, o.EquipmentNumber,
           o.FunctionalLocation, o.MaintenancePlant, o.ResponsiblePlannerGroup,
           o.MainWorkCenter, o.NotificationNumber, o.TargetFinishDate,
           o.CreatedByUser, s.[Resulting system status field]
    FROM order_base o
    JOIN [Your status history table] s ON s.[Order number field] = o.MaintenanceWorkOrder
    CROSS JOIN params p
    WHERE s.[Previous status code field] = 'TECO'
      AND s.[Status code field] <> 'TECO'
      AND s.[Status change timestamp field] >= p.start_ts
      AND s.[Status change timestamp field] < p.end_ts

    UNION ALL
    SELECT o.MaintenanceWorkOrder, 'Order Cost Settled',
           CAST(c.[Controlling document timestamp field] AS TIMESTAMP),
           o.MaintenanceOrderType, o.MaintenancePriority, o.EquipmentNumber,
           o.FunctionalLocation, o.MaintenancePlant, o.ResponsiblePlannerGroup,
           o.MainWorkCenter, o.NotificationNumber, o.TargetFinishDate,
           o.CreatedByUser, o.SystemStatus
    FROM order_base o
    JOIN [Your controlling document table] c ON c.[Order number field] = o.MaintenanceWorkOrder
    CROSS JOIN params p
    WHERE c.[Controlling document timestamp field] >= p.start_ts
      AND c.[Controlling document timestamp field] < p.end_ts

    UNION ALL
    SELECT o.MaintenanceWorkOrder, 'Business Completion Set',
           CAST(s.[Status change timestamp field] AS TIMESTAMP),
           o.MaintenanceOrderType, o.MaintenancePriority, o.EquipmentNumber,
           o.FunctionalLocation, o.MaintenancePlant, o.ResponsiblePlannerGroup,
           o.MainWorkCenter, o.NotificationNumber, o.TargetFinishDate,
           o.CreatedByUser, 'CLSD'
    FROM order_base o
    JOIN [Your status history table] s ON s.[Order number field] = o.MaintenanceWorkOrder
    CROSS JOIN params p
    WHERE s.[Status code field] = 'CLSD'
      AND s.[Status change timestamp field] >= p.start_ts
      AND s.[Status change timestamp field] < p.end_ts
)
SELECT
    e.MaintenanceWorkOrder,
    e.Activity,
    e.EventTimestamp,
    p.source_system AS SourceSystem,
    p.last_data_update AS LastDataUpdate,
    e.MaintenanceOrderType,
    e.MaintenancePriority,
    e.EquipmentNumber,
    e.FunctionalLocation,
    e.MaintenancePlant,
    e.ResponsiblePlannerGroup,
    e.MainWorkCenter,
    e.NotificationNumber,
    e.TargetFinishDate,
    e.CreatedByUser,
    e.SystemStatus
FROM base_events e
CROSS JOIN params p
WHERE e.MaintenanceWorkOrder IS NOT NULL
  AND e.EventTimestamp IS NOT NULL
  AND e.EventTimestamp >= p.start_ts
  AND e.EventTimestamp < p.end_ts
ORDER BY e.MaintenanceWorkOrder, e.EventTimestamp, e.Activity;