Varlık Bakımı Veri Template'inuz

/* 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 = ' '

/* 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 = ' '