Datatemplate voor onderhoud van bedrijfsmiddelen

{
  "extractionConfig": {
    "sourceSystem": "Infor EAM",
    "module": "Databridge",
    "targetFormat": "ProcessMind_EventLog",
    "globalFilters": {
      "organization": "[Your Organization Code]",
      "dateRangeStart": "2023-01-01",
      "excludedTypes": ["Standing Work Order", "Campaign"]
    },
    "mappings": [
      {
        "activityName": "Maintenance Request Received",
        "sourceDocument": "MP0094_SyncWorkOrder",
        "triggerCondition": "EVT_TYPE = 'R' AND EVT_DATE_CREATED IS NOT NULL",
        "timestampField": "EVT_DATE_CREATED",
        "attributes": {
          "WorkOrderNumber": "EVT_CODE",
          "MaintenanceType": "EVT_TYPE",
          "AssetCode": "EVT_OBJECT",
          "DepartmentCode": "EVT_MRC",
          "Priority": "EVT_PRIORITY"
        }
      },
      {
        "activityName": "Work Order Generated",
        "sourceDocument": "MP0094_SyncWorkOrder",
        "triggerCondition": "Previous_EVT_STATUS IS NULL AND EVT_STATUS = 'R'",
        "timestampField": "EVT_RTYPE_DATE",
        "attributes": {
          "WorkOrderNumber": "EVT_CODE",
          "TechnicianName": "EVT_PERSON",
          "EstimatedCost": "EVT_COST_EST"
        }
      },
      {
        "activityName": "Planning and Estimating Finished",
        "sourceDocument": "MP0094_SyncWorkOrder",
        "triggerCondition": "EVT_STATUS changes to 'READY' (or system equivalent)",
        "timestampField": "EVT_STATUS_DATE",
        "attributes": {
          "WorkOrderNumber": "EVT_CODE",
          "SlaTargetDate": "EVT_TARGET"
        }
      },
      {
        "activityName": "Work Order Approved",
        "sourceDocument": "MP0094_SyncWorkOrder",
        "triggerCondition": "EVT_STATUS changes to 'A' (Approved)",
        "timestampField": "EVT_STATUS_DATE",
        "attributes": {
          "WorkOrderNumber": "EVT_CODE",
          "EstimatedCost": "EVT_COST_EST"
        }
      },
      {
        "activityName": "Material Requisition Submitted",
        "sourceDocument": "MP0026_SyncRequisition",
        "triggerCondition": "REQ_STATUS = 'R' AND REQ_EVT IS NOT NULL",
        "timestampField": "REQ_DATE_CREATED",
        "attributes": {
          "WorkOrderNumber": "REQ_EVT",
          "DepartmentCode": "REQ_MRC"
        }
      },
      {
        "activityName": "Resources Scheduled",
        "sourceDocument": "MP0098_SyncWorkOrderActivity",
        "triggerCondition": "ACT_PERSON IS NOT NULL OR ACT_TRADE IS NOT NULL",
        "timestampField": "ACT_START_DATE",
        "attributes": {
          "WorkOrderNumber": "ACT_EVENT",
          "TechnicianName": "ACT_PERSON"
        }
      },
      {
        "activityName": "Work Commenced",
        "sourceDocument": "MP0094_SyncWorkOrder",
        "triggerCondition": "EVT_STATUS changes to 'IP' (In Progress)",
        "timestampField": "EVT_STATUS_DATE",
        "attributes": {
          "WorkOrderNumber": "EVT_CODE"
        }
      },
      {
        "activityName": "Material Issued",
        "sourceDocument": "MP3036_SyncTransaction",
        "triggerCondition": "TRN_TYPE = 'ISSUE'",
        "timestampField": "TRN_DATE",
        "attributes": {
          "WorkOrderNumber": "TRN_EVENT",
          "ActualTotalCost": "TRN_VALUE",
          "AssetCode": "TRN_PART"
        }
      },
      {
        "activityName": "Maintenance Task Executed",
        "sourceDocument": "MP0024_SyncBookedHours",
        "triggerCondition": "BOO_HOURS > 0",
        "timestampField": "BOO_DATE",
        "attributes": {
          "WorkOrderNumber": "BOO_EVENT",
          "TechnicianName": "BOO_PERSON",
          "ActualTotalCost": "BOO_COST"
        }
      },
      {
        "activityName": "Maintenance Records Updated",
        "sourceDocument": "MP0094_SyncWorkOrder",
        "triggerCondition": "Update to EVT_UDF_CHECKLIST OR EVT_NOTE is updated",
        "timestampField": "LastUpdateTimestamp",
        "attributes": {
          "WorkOrderNumber": "EVT_CODE"
        }
      },
      {
        "activityName": "Quality Control Testing Performed",
        "sourceDocument": "MP0094_SyncWorkOrder",
        "triggerCondition": "EVT_STATUS changes to 'QC' or 'TEST'",
        "timestampField": "EVT_STATUS_DATE",
        "attributes": {
          "WorkOrderNumber": "EVT_CODE"
        }
      },
      {
        "activityName": "Work Order Completed",
        "sourceDocument": "MP0094_SyncWorkOrder",
        "triggerCondition": "EVT_STATUS changes to 'C' (Completed)",
        "timestampField": "EVT_COMPLETED",
        "attributes": {
          "WorkOrderNumber": "EVT_CODE",
          "SlaTargetDate": "EVT_TARGET"
        }
      },
      {
        "activityName": "Financial Settlement Processed",
        "sourceDocument": "MP0094_SyncWorkOrder",
        "triggerCondition": "EVT_DATE_POSTED IS NOT NULL AND Previous_EVT_DATE_POSTED IS NULL",
        "timestampField": "EVT_DATE_POSTED",
        "attributes": {
          "WorkOrderNumber": "EVT_CODE",
          "ActualTotalCost": "EVT_COST_ACT"
        }
      },
      {
        "activityName": "Work Order Closed",
        "sourceDocument": "MP0094_SyncWorkOrder",
        "triggerCondition": "EVT_STATUS changes to 'CLOSE'",
        "timestampField": "EVT_STATUS_DATE",
        "attributes": {
          "WorkOrderNumber": "EVT_CODE"
        }
      }
    ]
  }
}

/* 1. Work Order Generated (Creation) */
SELECT
    EVT_CODE AS WorkOrderNumber,
    'Work Order Generated' AS EventName,
    EVT_DATE AS EventTimestamp,
    'Infor EAM' AS SourceSystem,
    GETDATE() AS LastDataUpdate,
    EVT_TYPE AS MaintenanceType,
    EVT_OBJECT AS AssetCode,
    EVT_MRC AS DepartmentCode,
    NULL AS TechnicianName,
    EVT_PRIORITY AS WorkOrderPriority,
    NULL AS EstimatedCost,
    NULL AS ActualTotalCost,
    EVT_TARGET AS SlaTargetDate
FROM R5EVENTS
WHERE EVT_DATE >= '[StartDate]' AND EVT_ORG = '[Your Organization Code]'

UNION ALL

/* 2. Maintenance Request Received (If derived from origin date) */
SELECT
    EVT_CODE,
    'Maintenance Request Received',
    EVT_CREATED,
    'Infor EAM',
    GETDATE(),
    EVT_TYPE,
    EVT_OBJECT,
    EVT_MRC,
    NULL,
    EVT_PRIORITY,
    NULL,
    NULL,
    EVT_TARGET
FROM R5EVENTS
WHERE EVT_CREATED < EVT_DATE /* Only if created earlier than WO generation */
AND EVT_DATE >= '[StartDate]' AND EVT_ORG = '[Your Organization Code]'

UNION ALL

/* 3. Planning and Estimating Finished (Status Change) */
SELECT
    S.STA_KEYVALUE,
    'Planning and Estimating Finished',
    S.STA_DATE,
    'Infor EAM',
    GETDATE(),
    E.EVT_TYPE,
    E.EVT_OBJECT,
    E.EVT_MRC,
    NULL,
    E.EVT_PRIORITY,
    NULL,
    NULL,
    E.EVT_TARGET
FROM R5STATUS S
JOIN R5EVENTS E ON S.STA_KEYVALUE = E.EVT_CODE
WHERE S.STA_TABLE = 'EVT' AND S.STA_RSTATUS IN ('R', 'PLANNED') /* Adjust status code */
AND E.EVT_DATE >= '[StartDate]' AND E.EVT_ORG = '[Your Organization Code]'

UNION ALL

/* 4. Work Order Approved */
SELECT
    S.STA_KEYVALUE,
    'Work Order Approved',
    S.STA_DATE,
    'Infor EAM',
    GETDATE(),
    E.EVT_TYPE,
    E.EVT_OBJECT,
    E.EVT_MRC,
    NULL,
    E.EVT_PRIORITY,
    NULL,
    NULL,
    E.EVT_TARGET
FROM R5STATUS S
JOIN R5EVENTS E ON S.STA_KEYVALUE = E.EVT_CODE
WHERE S.STA_TABLE = 'EVT' AND S.STA_RSTATUS = 'A' /* Standard Approved Code */
AND E.EVT_DATE >= '[StartDate]'

UNION ALL

/* 5. Material Requisition Submitted */
SELECT
    TRL_EVENT,
    'Material Requisition Submitted',
    TRL_DATE,
    'Infor EAM',
    GETDATE(),
    E.EVT_TYPE,
    E.EVT_OBJECT,
    E.EVT_MRC,
    NULL,
    E.EVT_PRIORITY,
    NULL,
    NULL,
    E.EVT_TARGET
FROM R5TRANSLINES T
JOIN R5EVENTS E ON T.TRL_EVENT = E.EVT_CODE
WHERE T.TRL_TYPE = 'REQ' /* Requisition Line */
AND E.EVT_DATE >= '[StartDate]'

UNION ALL

/* 6. Resources Scheduled */
SELECT
    ACT_EVENT,
    'Resources Scheduled',
    ACT_START,
    'Infor EAM',
    GETDATE(),
    E.EVT_TYPE,
    E.EVT_OBJECT,
    E.EVT_MRC,
    ACT_PERSON,
    E.EVT_PRIORITY,
    ACT_EST_COST,
    NULL,
    E.EVT_TARGET
FROM R5ACTIVITIES A
JOIN R5EVENTS E ON A.ACT_EVENT = E.EVT_CODE
WHERE A.ACT_START IS NOT NULL
AND E.EVT_DATE >= '[StartDate]'

UNION ALL

/* 7. Work Commenced */
SELECT
    S.STA_KEYVALUE,
    'Work Commenced',
    S.STA_DATE,
    'Infor EAM',
    GETDATE(),
    E.EVT_TYPE,
    E.EVT_OBJECT,
    E.EVT_MRC,
    NULL,
    E.EVT_PRIORITY,
    NULL,
    NULL,
    E.EVT_TARGET
FROM R5STATUS S
JOIN R5EVENTS E ON S.STA_KEYVALUE = E.EVT_CODE
WHERE S.STA_TABLE = 'EVT' AND S.STA_RSTATUS IN ('SO', 'IP') /* Start Order or In Progress */
AND E.EVT_DATE >= '[StartDate]'

UNION ALL

/* 8. Material Issued */
SELECT
    TRN_EVENT,
    'Material Issued',
    TRN_DATE,
    'Infor EAM',
    GETDATE(),
    E.EVT_TYPE,
    E.EVT_OBJECT,
    E.EVT_MRC,
    NULL,
    E.EVT_PRIORITY,
    NULL,
    TRN_VALUE,
    E.EVT_TARGET
FROM R5TRANSACTIONS T
JOIN R5EVENTS E ON T.TRN_EVENT = E.EVT_CODE
WHERE T.TRN_TYPE = 'I' /* Issue Transaction */
AND E.EVT_DATE >= '[StartDate]'

UNION ALL

/* 9. Maintenance Task Executed (Labor Booking) */
SELECT
    BOO_EVENT,
    'Maintenance Task Executed',
    BOO_DATE,
    'Infor EAM',
    GETDATE(),
    E.EVT_TYPE,
    E.EVT_OBJECT,
    E.EVT_MRC,
    BOO_PERSON,
    E.EVT_PRIORITY,
    NULL,
    BOO_VALUE,
    E.EVT_TARGET
FROM R5BOOKEDHOURS B
JOIN R5EVENTS E ON B.BOO_EVENT = E.EVT_CODE
WHERE E.EVT_DATE >= '[StartDate]'

UNION ALL

/* 10. Maintenance Records Updated (Comments) */
SELECT
    COM_CODE,
    'Maintenance Records Updated',
    COM_DATE,
    'Infor EAM',
    GETDATE(),
    E.EVT_TYPE,
    E.EVT_OBJECT,
    E.EVT_MRC,
    COM_PERSON,
    E.EVT_PRIORITY,
    NULL,
    NULL,
    E.EVT_TARGET
FROM R5COMMENTS C
JOIN R5EVENTS E ON C.COM_CODE = E.EVT_CODE
WHERE C.COM_TYPE = 'EVT' /* Linked to Event */
AND E.EVT_DATE >= '[StartDate]'

UNION ALL

/* 11. Quality Control Testing Performed */
SELECT
    TST_EVENT,
    'Quality Control Testing Performed',
    TST_DATE,
    'Infor EAM',
    GETDATE(),
    E.EVT_TYPE,
    E.EVT_OBJECT,
    E.EVT_MRC,
    TST_ENTEREDBY,
    E.EVT_PRIORITY,
    NULL,
    NULL,
    E.EVT_TARGET
FROM R5TESTRESULTS T
JOIN R5EVENTS E ON T.TST_EVENT = E.EVT_CODE
WHERE E.EVT_DATE >= '[StartDate]'

UNION ALL

/* 12. Work Order Completed */
SELECT
    S.STA_KEYVALUE,
    'Work Order Completed',
    S.STA_DATE,
    'Infor EAM',
    GETDATE(),
    E.EVT_TYPE,
    E.EVT_OBJECT,
    E.EVT_MRC,
    NULL,
    E.EVT_PRIORITY,
    NULL,
    NULL,
    E.EVT_TARGET
FROM R5STATUS S
JOIN R5EVENTS E ON S.STA_KEYVALUE = E.EVT_CODE
WHERE S.STA_TABLE = 'EVT' AND S.STA_RSTATUS = 'C' /* Completed/Technically Complete */
AND E.EVT_DATE >= '[StartDate]'

UNION ALL

/* 13. Financial Settlement Processed */
SELECT
    S.STA_KEYVALUE,
    'Financial Settlement Processed',
    S.STA_DATE,
    'Infor EAM',
    GETDATE(),
    E.EVT_TYPE,
    E.EVT_OBJECT,
    E.EVT_MRC,
    NULL,
    E.EVT_PRIORITY,
    NULL,
    NULL,
    E.EVT_TARGET
FROM R5STATUS S
JOIN R5EVENTS E ON S.STA_KEYVALUE = E.EVT_CODE
WHERE S.STA_TABLE = 'EVT' AND S.STA_RSTATUS = 'AC' /* Accounting Close/Review */
AND E.EVT_DATE >= '[StartDate]'

UNION ALL

/* 14. Work Order Closed */
SELECT
    S.STA_KEYVALUE,
    'Work Order Closed',
    S.STA_DATE,
    'Infor EAM',
    GETDATE(),
    E.EVT_TYPE,
    E.EVT_OBJECT,
    E.EVT_MRC,
    NULL,
    E.EVT_PRIORITY,
    NULL,
    E.EVT_COST, /* Final Cost usually captured here */
    E.EVT_TARGET
FROM R5STATUS S
JOIN R5EVENTS E ON S.STA_KEYVALUE = E.EVT_CODE
WHERE S.STA_TABLE = 'EVT' AND S.STA_RSTATUS = 'CL' /* Hard Close */
AND E.EVT_DATE >= '[StartDate]'