Votre modèle de données de maintenance des actifs

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

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