자산 유지보수 데이터 템플릿

/* Create a Database View or Run directly to extract Event Log */

/* 1. Maintenance Request Created */
SELECT 
    W.WONUM AS WorkOrderNumber,
    'Maintenance Request Created' AS Activity,
    W.REPORTDATE AS EventTimestamp,
    'Maximo' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    W.WORKTYPE AS WorkType,
    W.WOPRIORITY AS Priority,
    W.ASSETNUM AS AssetNumber,
    W.LEAD AS AssignedResource,
    W.ACTFINISH AS ActualFinishDate,
    W.TARGCOMPDATE AS TargetCompletionDate,
    0 AS ActualLaborHours,
    W.ASSETLOCPRIORITY AS AssetCriticality,
    W.STATUS AS Status
FROM WORKORDER W
WHERE W.REPORTDATE IS NOT NULL

UNION ALL

/* 2. Status Driven Activities (Approved, Scheduled, Commenced, Completed, Closed, Cancelled, etc.) */
SELECT 
    S.WONUM AS WorkOrderNumber,
    CASE 
        WHEN S.STATUS = 'APPR' THEN 'Work Order Approved'
        WHEN S.STATUS = 'WMATL' THEN 'Material Requisition Submitted'
        WHEN S.STATUS = 'WSCH' THEN 'Resources Scheduled'
        WHEN S.STATUS = 'INPRG' THEN 'Work Commenced'
        WHEN S.STATUS = 'INSP' THEN 'Inspection Completed' /* Verify Synonym */
        WHEN S.STATUS = 'COMP' THEN 'Work Order Completed'
        WHEN S.STATUS = 'REJECT' THEN 'Quality Check Failed' /* Verify Synonym */
        WHEN S.STATUS = 'CLOSE' THEN 'Work Order Closed'
        WHEN S.STATUS = 'CAN' THEN 'Work Order Cancelled'
        ELSE 'Status Change: ' || S.STATUS
    END AS Activity,
    S.CHANGEDATE AS EventTimestamp,
    'Maximo' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    W.WORKTYPE AS WorkType,
    W.WOPRIORITY AS Priority,
    W.ASSETNUM AS AssetNumber,
    S.CHANGEBY AS AssignedResource,
    W.ACTFINISH AS ActualFinishDate,
    W.TARGCOMPDATE AS TargetCompletionDate,
    0 AS ActualLaborHours,
    W.ASSETLOCPRIORITY AS AssetCriticality,
    S.STATUS AS Status
FROM WOSTATUS S
JOIN WORKORDER W ON S.WONUM = W.WONUM AND S.SITEID = W.SITEID
WHERE S.STATUS IN ('APPR', 'WMATL', 'WSCH', 'INPRG', 'INSP', 'COMP', 'REJECT', 'CLOSE', 'CAN')

UNION ALL

/* 3. Labor Hours Recorded */
SELECT 
    L.REFWO AS WorkOrderNumber,
    'Labor Hours Recorded' AS Activity,
    L.STARTDATE AS EventTimestamp,
    'Maximo' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    W.WORKTYPE AS WorkType,
    W.WOPRIORITY AS Priority,
    W.ASSETNUM AS AssetNumber,
    L.LABORCODE AS AssignedResource,
    W.ACTFINISH AS ActualFinishDate,
    W.TARGCOMPDATE AS TargetCompletionDate,
    L.REGULARHRS AS ActualLaborHours,
    W.ASSETLOCPRIORITY AS AssetCriticality,
    W.STATUS AS Status
FROM WOLABTRANS L
JOIN WORKORDER W ON L.REFWO = W.WONUM AND L.SITEID = W.SITEID

UNION ALL

/* 4. Material Issued */
SELECT 
    M.REFWO AS WorkOrderNumber,
    'Material Issued' AS Activity,
    M.TRANSDATE AS EventTimestamp,
    'Maximo' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    W.WORKTYPE AS WorkType,
    W.WOPRIORITY AS Priority,
    W.ASSETNUM AS AssetNumber,
    M.ISSUETO AS AssignedResource,
    W.ACTFINISH AS ActualFinishDate,
    W.TARGCOMPDATE AS TargetCompletionDate,
    0 AS ActualLaborHours,
    W.ASSETLOCPRIORITY AS AssetCriticality,
    W.STATUS AS Status
FROM MATUSETRANS M
JOIN WORKORDER W ON M.REFWO = W.WONUM AND M.SITEID = W.SITEID
WHERE M.ISSUETYPE = 'ISSUE'

UNION ALL

/* 5. Target Date Updated (Requires Audit Table) */
SELECT 
    A.WONUM AS WorkOrderNumber,
    'Target Date Updated' AS Activity,
    A.AUDITSTAMP AS EventTimestamp,
    'Maximo' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    W.WORKTYPE AS WorkType,
    W.WOPRIORITY AS Priority,
    W.ASSETNUM AS AssetNumber,
    A.AUDITUSER AS AssignedResource,
    W.ACTFINISH AS ActualFinishDate,
    A.TARGCOMPDATE AS TargetCompletionDate,
    0 AS ActualLaborHours,
    W.ASSETLOCPRIORITY AS AssetCriticality,
    W.STATUS AS Status
FROM A_WORKORDER A
JOIN WORKORDER W ON A.WONUM = W.WONUM AND A.SITEID = W.SITEID
WHERE A.TARGCOMPDATE IS NOT NULL 
  AND A.TARGCOMPDATE <> COALESCE((SELECT TOP 1 PREV.TARGCOMPDATE FROM A_WORKORDER PREV WHERE PREV.WONUM = A.WONUM AND PREV.AUDITSTAMP < A.AUDITSTAMP ORDER BY PREV.AUDITSTAMP DESC), '1900-01-01')

SELECT
    W.WONUM AS WorkOrderNumber,
    'Maintenance Request Created' AS Activity,
    W.REPORTDATE AS EventTimestamp,
    'Maximo' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    W.WORKTYPE AS WorkType,
    W.WOPRIORITY AS Priority,
    W.ASSETNUM AS AssetNumber,
    W.LEAD AS AssignedResource,
    W.ACTFINISH AS ActualFinishDate,
    W.TARGCOMPDATE AS TargetCompletionDate,
    W.ACTLABHRS AS ActualLaborHours,
    W.ASSETLOCPRIORITY AS AssetCriticality,
    W.STATUS AS Status
FROM
    WORKORDER W
WHERE
    W.REPORTDATE >= '2023-01-01'

UNION ALL

SELECT
    WS.WONUM AS WorkOrderNumber,
    CASE 
        WHEN WS.STATUS = 'APPR' THEN 'Work Order Approved'
        WHEN WS.STATUS = 'WMATL' THEN 'Material Requisition Submitted'
        WHEN WS.STATUS = 'WSCH' THEN 'Resources Scheduled'
        WHEN WS.STATUS = 'INPRG' THEN 'Work Commenced'
        WHEN WS.STATUS = 'INSP' THEN 'Inspection Completed'
        WHEN WS.STATUS = 'COMP' THEN 'Work Order Completed'
        WHEN WS.STATUS = 'REJECT' THEN 'Quality Check Failed'
        WHEN WS.STATUS = 'CLOSE' THEN 'Work Order Closed'
        WHEN WS.STATUS = 'CAN' THEN 'Work Order Cancelled'
        ELSE 'Status Change: ' || WS.STATUS
    END AS Activity,
    WS.CHANGEDATE AS EventTimestamp,
    'Maximo' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    W.WORKTYPE AS WorkType,
    W.WOPRIORITY AS Priority,
    W.ASSETNUM AS AssetNumber,
    WS.CHANGEBY AS AssignedResource,
    W.ACTFINISH AS ActualFinishDate,
    W.TARGCOMPDATE AS TargetCompletionDate,
    W.ACTLABHRS AS ActualLaborHours,
    W.ASSETLOCPRIORITY AS AssetCriticality,
    WS.STATUS AS Status
FROM
    WOSTATUS WS
JOIN
    WORKORDER W ON WS.WONUM = W.WONUM AND WS.SITEID = W.SITEID
WHERE
    W.REPORTDATE >= '2023-01-01'
    AND WS.STATUS IN ('APPR', 'WMATL', 'WSCH', 'INPRG', 'INSP', 'COMP', 'REJECT', 'CLOSE', 'CAN')

UNION ALL

SELECT
    L.REFWO AS WorkOrderNumber,
    'Labor Hours Recorded' AS Activity,
    L.STARTDATE AS EventTimestamp,
    'Maximo' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    W.WORKTYPE AS WorkType,
    W.WOPRIORITY AS Priority,
    W.ASSETNUM AS AssetNumber,
    L.LABORCODE AS AssignedResource,
    W.ACTFINISH AS ActualFinishDate,
    W.TARGCOMPDATE AS TargetCompletionDate,
    W.ACTLABHRS AS ActualLaborHours,
    W.ASSETLOCPRIORITY AS AssetCriticality,
    W.STATUS AS Status
FROM
    LABTRANS L
JOIN
    WORKORDER W ON L.REFWO = W.WONUM AND L.SITEID = W.SITEID
WHERE
    W.REPORTDATE >= '2023-01-01'

UNION ALL

SELECT
    M.REFWO AS WorkOrderNumber,
    'Material Issued' AS Activity,
    M.TRANSDATE AS EventTimestamp,
    'Maximo' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    W.WORKTYPE AS WorkType,
    W.WOPRIORITY AS Priority,
    W.ASSETNUM AS AssetNumber,
    M.ENTERBY AS AssignedResource,
    W.ACTFINISH AS ActualFinishDate,
    W.TARGCOMPDATE AS TargetCompletionDate,
    W.ACTLABHRS AS ActualLaborHours,
    W.ASSETLOCPRIORITY AS AssetCriticality,
    W.STATUS AS Status
FROM
    MATUSETRANS M
JOIN
    WORKORDER W ON M.REFWO = W.WONUM AND M.SITEID = W.SITEID
WHERE
    W.REPORTDATE >= '2023-01-01'
    AND M.ISSUETYPE = 'ISSUE'

UNION ALL

SELECT
    WC.WONUM AS WorkOrderNumber,
    'Target Date Updated' AS Activity,
    WC.CHANGEDATE AS EventTimestamp,
    'Maximo' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    W.WORKTYPE AS WorkType,
    W.WOPRIORITY AS Priority,
    W.ASSETNUM AS AssetNumber,
    WC.CHANGEBY AS AssignedResource,
    W.ACTFINISH AS ActualFinishDate,
    W.TARGCOMPDATE AS TargetCompletionDate,
    W.ACTLABHRS AS ActualLaborHours,
    W.ASSETLOCPRIORITY AS AssetCriticality,
    W.STATUS AS Status
FROM
    WOCHANGE WC
JOIN
    WORKORDER W ON WC.WONUM = W.WONUM AND WC.SITEID = W.SITEID
WHERE
    W.REPORTDATE >= '2023-01-01'
    AND (WC.MODIFIEDATTRIBUTE = 'TARGCOMPDATE' OR WC.MODIFIEDATTRIBUTE = 'SCHEDFINISH')