Votre modèle de données pour la gestion de la qualité

SELECT
  noti.QualityNotification AS "QualityEvent",
  'Quality Notification Created' AS "ActivityName",
  noti.CreationDateTime AS "EventTimestamp",
  noti.CreatedByUser AS "ChangedBy",
  loc.WorkCenterInternalID AS "ResponsibleDepartment",
  noti.QualityNotificationType AS "QualityNotificationType",
  noti.Material AS "MaterialNumber",
  noti.NotificationPriority AS "NotificationPriority",
  NULL AS "RootCauseCode",
  noti.RequiredEndDate AS "TargetResolutionDate"
FROM I_QualityNotification AS noti
LEFT JOIN I_QltyNfnWorkCenterLocation AS loc ON noti.QualityNotification = loc.QualityNotification
WHERE noti.CreationDate BETWEEN '[START_DATE]' AND '[END_DATE]'

UNION ALL

SELECT
  noti.QualityNotification AS "QualityEvent",
  'Notification Put In Process' AS "ActivityName",
  noti.LastChangeDateTime AS "EventTimestamp",
  noti.LastChangedByUser AS "ChangedBy",
  loc.WorkCenterInternalID AS "ResponsibleDepartment",
  noti.QualityNotificationType AS "QualityNotificationType",
  noti.Material AS "MaterialNumber",
  noti.NotificationPriority AS "NotificationPriority",
  NULL AS "RootCauseCode",
  noti.RequiredEndDate AS "TargetResolutionDate"
FROM I_QualityNotification AS noti
LEFT JOIN I_QltyNfnWorkCenterLocation AS loc ON noti.QualityNotification = loc.QualityNotification
WHERE noti.NotificationProcessStartDate IS NOT NULL AND noti.CreationDate BETWEEN '[START_DATE]' AND '[END_DATE]'

UNION ALL

SELECT
  task.QualityNotification AS "QualityEvent",
  'Investigation Task Assigned' AS "ActivityName",
  task.CreationDateTime AS "EventTimestamp",
  task.CreatedByUser AS "ChangedBy",
  task.ExecutingDepartment AS "ResponsibleDepartment",
  noti.QualityNotificationType AS "QualityNotificationType",
  noti.Material AS "MaterialNumber",
  noti.NotificationPriority AS "NotificationPriority",
  NULL AS "RootCauseCode",
  noti.RequiredEndDate AS "TargetResolutionDate"
FROM I_QualityNotificationTask AS task
INNER JOIN I_QualityNotification AS noti ON task.QualityNotification = noti.QualityNotification
WHERE task.QltyNotificationTaskCodeGroup = '[Your Investigation Task Code Group]' AND noti.CreationDate BETWEEN '[START_DATE]' AND '[END_DATE]'

UNION ALL

SELECT
  task.QualityNotification AS "QualityEvent",
  'Root Cause Analysis Completed' AS "ActivityName",
  task.CompletionDateTime AS "EventTimestamp",
  task.LastChangedByUser AS "ChangedBy",
  task.ExecutingDepartment AS "ResponsibleDepartment",
  noti.QualityNotificationType AS "QualityNotificationType",
  noti.Material AS "MaterialNumber",
  noti.NotificationPriority AS "NotificationPriority",
  task.QualityNotificationTaskCause AS "RootCauseCode",
  noti.RequiredEndDate AS "TargetResolutionDate"
FROM I_QualityNotificationTask AS task
INNER JOIN I_QualityNotification AS noti ON task.QualityNotification = noti.QualityNotification
WHERE task.QltyNotificationTaskCodeGroup = '[Your Root Cause Task Code Group]' AND task.CompletionDateTime IS NOT NULL AND noti.CreationDate BETWEEN '[START_DATE]' AND '[END_DATE]'

UNION ALL

SELECT 
  ud.QualityNotification AS "QualityEvent",
  'Usage Decision Made' AS "ActivityName",
  ud.UsageDecisionDateTime AS "EventTimestamp",
  ud.UsageDecisionMadeByUser AS "ChangedBy",
  noti.ExecutingWorkCenter AS "ResponsibleDepartment",
  noti.QualityNotificationType AS "QualityNotificationType",
  noti.Material AS "MaterialNumber",
  noti.NotificationPriority AS "NotificationPriority",
  NULL AS "RootCauseCode",
  noti.RequiredEndDate AS "TargetResolutionDate"
FROM I_InspectionLotUsageDecision AS ud
INNER JOIN I_QualityNotification AS noti ON ud.QualityNotification = noti.QualityNotification
WHERE ud.QualityNotification IS NOT NULL AND noti.CreationDate BETWEEN '[START_DATE]' AND '[END_DATE]'

UNION ALL

SELECT
  task.QualityNotification AS "QualityEvent",
  'Corrective Action Proposed' AS "ActivityName",
  task.CreationDateTime AS "EventTimestamp",
  task.CreatedByUser AS "ChangedBy",
  task.ExecutingDepartment AS "ResponsibleDepartment",
  noti.QualityNotificationType AS "QualityNotificationType",
  noti.Material AS "MaterialNumber",
  noti.NotificationPriority AS "NotificationPriority",
  NULL AS "RootCauseCode",
  noti.RequiredEndDate AS "TargetResolutionDate"
FROM I_QualityNotificationTask AS task
INNER JOIN I_QualityNotification AS noti ON task.QualityNotification = noti.QualityNotification
WHERE task.QltyNotificationTaskCodeGroup = '[Your Corrective Action Task Code Group]' AND noti.CreationDate BETWEEN '[START_DATE]' AND '[END_DATE]'

UNION ALL

SELECT
  task.QualityNotification AS "QualityEvent",
  'Action Plan Approved' AS "ActivityName",
  task.ReleaseDateTime AS "EventTimestamp",
  task.LastChangedByUser AS "ChangedBy",
  task.ExecutingDepartment AS "ResponsibleDepartment",
  noti.QualityNotificationType AS "QualityNotificationType",
  noti.Material AS "MaterialNumber",
  noti.NotificationPriority AS "NotificationPriority",
  NULL AS "RootCauseCode",
  noti.RequiredEndDate AS "TargetResolutionDate"
FROM I_QualityNotificationTask AS task
INNER JOIN I_QualityNotification AS noti ON task.QualityNotification = noti.QualityNotification
WHERE task.QltyNotificationTaskCodeGroup IN ('[Your Corrective Action Task Code Group]', '[Your Preventive Action Task Code Group]') AND task.ReleaseDateTime IS NOT NULL AND noti.CreationDate BETWEEN '[START_DATE]' AND '[END_DATE]'

UNION ALL

SELECT
  task.QualityNotification AS "QualityEvent",
  'Corrective Action Implemented' AS "ActivityName",
  task.CompletionDateTime AS "EventTimestamp",
  task.LastChangedByUser AS "ChangedBy",
  task.ExecutingDepartment AS "ResponsibleDepartment",
  noti.QualityNotificationType AS "QualityNotificationType",
  noti.Material AS "MaterialNumber",
  noti.NotificationPriority AS "NotificationPriority",
  NULL AS "RootCauseCode",
  noti.RequiredEndDate AS "TargetResolutionDate"
FROM I_QualityNotificationTask AS task
INNER JOIN I_QualityNotification AS noti ON task.QualityNotification = noti.QualityNotification
WHERE task.QltyNotificationTaskCodeGroup = '[Your Corrective Action Task Code Group]' AND task.CompletionDateTime IS NOT NULL AND noti.CreationDate BETWEEN '[START_DATE]' AND '[END_DATE]'

UNION ALL

SELECT
  task.QualityNotification AS "QualityEvent",
  'Preventive Action Proposed' AS "ActivityName",
  task.CreationDateTime AS "EventTimestamp",
  task.CreatedByUser AS "ChangedBy",
  task.ExecutingDepartment AS "ResponsibleDepartment",
  noti.QualityNotificationType AS "QualityNotificationType",
  noti.Material AS "MaterialNumber",
  noti.NotificationPriority AS "NotificationPriority",
  NULL AS "RootCauseCode",
  noti.RequiredEndDate AS "TargetResolutionDate"
FROM I_QualityNotificationTask AS task
INNER JOIN I_QualityNotification AS noti ON task.QualityNotification = noti.QualityNotification
WHERE task.QltyNotificationTaskCodeGroup = '[Your Preventive Action Task Code Group]' AND noti.CreationDate BETWEEN '[START_DATE]' AND '[END_DATE]'

UNION ALL

SELECT
  task.QualityNotification AS "QualityEvent",
  'Preventive Action Implemented' AS "ActivityName",
  task.CompletionDateTime AS "EventTimestamp",
  task.LastChangedByUser AS "ChangedBy",
  task.ExecutingDepartment AS "ResponsibleDepartment",
  noti.QualityNotificationType AS "QualityNotificationType",
  noti.Material AS "MaterialNumber",
  noti.NotificationPriority AS "NotificationPriority",
  NULL AS "RootCauseCode",
  noti.RequiredEndDate AS "TargetResolutionDate"
FROM I_QualityNotificationTask AS task
INNER JOIN I_QualityNotification AS noti ON task.QualityNotification = noti.QualityNotification
WHERE task.QltyNotificationTaskCodeGroup = '[Your Preventive Action Task Code Group]' AND task.CompletionDateTime IS NOT NULL AND noti.CreationDate BETWEEN '[START_DATE]' AND '[END_DATE]'

UNION ALL

SELECT
  task.QualityNotification AS "QualityEvent",
  'Effectiveness Check Required' AS "ActivityName",
  task.CreationDateTime AS "EventTimestamp",
  task.CreatedByUser AS "ChangedBy",
  task.ExecutingDepartment AS "ResponsibleDepartment",
  noti.QualityNotificationType AS "QualityNotificationType",
  noti.Material AS "MaterialNumber",
  noti.NotificationPriority AS "NotificationPriority",
  NULL AS "RootCauseCode",
  noti.RequiredEndDate AS "TargetResolutionDate"
FROM I_QualityNotificationTask AS task
INNER JOIN I_QualityNotification AS noti ON task.QualityNotification = noti.QualityNotification
WHERE task.QltyNotificationTaskCodeGroup = '[Your Effectiveness Check Task Code Group]' AND noti.CreationDate BETWEEN '[START_DATE]' AND '[END_DATE]'

UNION ALL

SELECT
  task.QualityNotification AS "QualityEvent",
  'Action Effectiveness Verified' AS "ActivityName",
  task.CompletionDateTime AS "EventTimestamp",
  task.LastChangedByUser AS "ChangedBy",
  task.ExecutingDepartment AS "ResponsibleDepartment",
  noti.QualityNotificationType AS "QualityNotificationType",
  noti.Material AS "MaterialNumber",
  noti.NotificationPriority AS "NotificationPriority",
  NULL AS "RootCauseCode",
  noti.RequiredEndDate AS "TargetResolutionDate"
FROM I_QualityNotificationTask AS task
INNER JOIN I_QualityNotification AS noti ON task.QualityNotification = noti.QualityNotification
WHERE task.QltyNotificationTaskCodeGroup = '[Your Effectiveness Check Task Code Group]' AND task.CompletionDateTime IS NOT NULL AND noti.CreationDate BETWEEN '[START_DATE]' AND '[END_DATE]'

UNION ALL

SELECT
  task.QualityNotification AS "QualityEvent",
  'Stakeholders Notified' AS "ActivityName",
  task.CompletionDateTime AS "EventTimestamp",
  task.LastChangedByUser AS "ChangedBy",
  task.ExecutingDepartment AS "ResponsibleDepartment",
  noti.QualityNotificationType AS "QualityNotificationType",
  noti.Material AS "MaterialNumber",
  noti.NotificationPriority AS "NotificationPriority",
  NULL AS "RootCauseCode",
  noti.RequiredEndDate AS "TargetResolutionDate"
FROM I_QualityNotificationTask AS task
INNER JOIN I_QualityNotification AS noti ON task.QualityNotification = noti.QualityNotification
WHERE task.QltyNotificationTaskCodeGroup = '[Your Stakeholder Notification Task Code Group]' AND task.CompletionDateTime IS NOT NULL AND noti.CreationDate BETWEEN '[START_DATE]' AND '[END_DATE]'

UNION ALL

SELECT
  noti.QualityNotification AS "QualityEvent",
  'Notification Completed' AS "ActivityName",
  noti.CompletionDate AS "EventTimestamp",
  noti.LastChangedByUser AS "ChangedBy",
  loc.WorkCenterInternalID AS "ResponsibleDepartment",
  noti.QualityNotificationType AS "QualityNotificationType",
  noti.Material AS "MaterialNumber",
  noti.NotificationPriority AS "NotificationPriority",
  NULL AS "RootCauseCode",
  noti.RequiredEndDate AS "TargetResolutionDate"
FROM I_QualityNotification AS noti
LEFT JOIN I_QltyNfnWorkCenterLocation AS loc ON noti.QualityNotification = loc.QualityNotification
WHERE noti.IsCompleted = 'X' AND noti.CreationDate BETWEEN '[START_DATE]' AND '[END_DATE]'

UNION ALL

SELECT
  noti.QualityNotification AS "QualityEvent",
  'Notification Closed' AS "ActivityName",
  noti.ArchivingDate AS "EventTimestamp",
  noti.LastChangedByUser AS "ChangedBy",
  loc.WorkCenterInternalID AS "ResponsibleDepartment",
  noti.QualityNotificationType AS "QualityNotificationType",
  noti.Material AS "MaterialNumber",
  noti.NotificationPriority AS "NotificationPriority",
  NULL AS "RootCauseCode",
  noti.RequiredEndDate AS "TargetResolutionDate"
FROM I_QualityNotification AS noti
LEFT JOIN I_QltyNfnWorkCenterLocation AS loc ON noti.QualityNotification = loc.QualityNotification
WHERE noti.IsArchived = 'X' AND noti.CreationDate BETWEEN '[START_DATE]' AND '[END_DATE]'

WITH
notification_base AS (
    SELECT
        q.QualityEvent,
        q.ActivityName,
        q.EventTimestamp,
        q.ChangedBy,
        q.ResponsibleDepartment,
        q.QualityNotificationType,
        q.MaterialNumber,
        q.NotificationPriority,
        q.RootCauseCode,
        q.TargetResolutionDate
    FROM (
        SELECT
            CAST(n.QMNUM AS NVARCHAR(50)) AS QualityEvent,
            'Quality Notification Created' AS ActivityName,
            CAST(n.ERDAT AS TIMESTAMP) AS EventTimestamp,
            CAST(n.ERNAM AS NVARCHAR(50)) AS ChangedBy,
            CAST(n.QMART AS NVARCHAR(50)) AS ResponsibleDepartment,
            CAST(n.QMART AS NVARCHAR(50)) AS QualityNotificationType,
            CAST(n.MATNR AS NVARCHAR(50)) AS MaterialNumber,
            CAST(n.PRIOK AS NVARCHAR(50)) AS NotificationPriority,
            CAST(n.URCOD AS NVARCHAR(50)) AS RootCauseCode,
            CAST(n.QMDAT AS DATE) AS TargetResolutionDate
        FROM [Your HANA schema].QMEL n
        WHERE CAST(n.ERDAT AS DATE) BETWEEN CAST('[Start date]' AS DATE) AND CAST('[End date]' AS DATE)
          AND ([Company code filter] IS NULL OR n.BUKRS = [Company code filter])
          AND ([Notification type filter] IS NULL OR n.QMART = [Notification type filter])

        UNION ALL

        SELECT
            CAST(n.QMNUM AS NVARCHAR(50)),
            'Notification Put In Process',
            CAST(n.AEDAT AS TIMESTAMP),
            CAST(n.AENAM AS NVARCHAR(50)),
            CAST(n.QMART AS NVARCHAR(50)),
            CAST(n.QMART AS NVARCHAR(50)),
            CAST(n.MATNR AS NVARCHAR(50)),
            CAST(n.PRIOK AS NVARCHAR(50)),
            CAST(n.URCOD AS NVARCHAR(50)),
            CAST(n.QMDAT AS DATE)
        FROM [Your HANA schema].QMEL n
        WHERE CAST(n.AEDAT AS DATE) BETWEEN CAST('[Start date]' AS DATE) AND CAST('[End date]' AS DATE)
          AND [Configure based on your system: notification in-process status condition]

        UNION ALL

        SELECT
            CAST(n.QMNUM AS NVARCHAR(50)),
            'Investigation Task Assigned',
            CAST(n.AEDAT AS TIMESTAMP),
            CAST(n.AENAM AS NVARCHAR(50)),
            CAST(n.QMART AS NVARCHAR(50)),
            CAST(n.QMART AS NVARCHAR(50)),
            CAST(n.MATNR AS NVARCHAR(50)),
            CAST(n.PRIOK AS NVARCHAR(50)),
            CAST(n.URCOD AS NVARCHAR(50)),
            CAST(n.QMDAT AS DATE)
        FROM [Your HANA schema].QMEL n
        INNER JOIN [Your HANA schema].QMFE f ON f.QMNUM = n.QMNUM
        WHERE CAST(n.AEDAT AS DATE) BETWEEN CAST('[Start date]' AS DATE) AND CAST('[End date]' AS DATE)
          AND [Configure based on your system: investigation task creation and assignment condition]

        UNION ALL

        SELECT
            CAST(n.QMNUM AS NVARCHAR(50)),
            'Root Cause Analysis Completed',
            CAST(n.AEDAT AS TIMESTAMP),
            CAST(n.AENAM AS NVARCHAR(50)),
            CAST(n.QMART AS NVARCHAR(50)),
            CAST(n.QMART AS NVARCHAR(50)),
            CAST(n.MATNR AS NVARCHAR(50)),
            CAST(n.PRIOK AS NVARCHAR(50)),
            CAST(n.URCOD AS NVARCHAR(50)),
            CAST(n.QMDAT AS DATE)
        FROM [Your HANA schema].QMEL n
        INNER JOIN [Your HANA schema].QMFE f ON f.QMNUM = n.QMNUM
        WHERE CAST(n.AEDAT AS DATE) BETWEEN CAST('[Start date]' AS DATE) AND CAST('[End date]' AS DATE)
          AND [Configure based on your system: completed Root Cause Analysis task condition]

        UNION ALL

        SELECT
            CAST(n.QMNUM AS NVARCHAR(50)),
            'Usage Decision Made',
            CAST(n.AEDAT AS TIMESTAMP),
            CAST(n.AENAM AS NVARCHAR(50)),
            CAST(n.QMART AS NVARCHAR(50)),
            CAST(n.QMART AS NVARCHAR(50)),
            CAST(n.MATNR AS NVARCHAR(50)),
            CAST(n.PRIOK AS NVARCHAR(50)),
            CAST(n.URCOD AS NVARCHAR(50)),
            CAST(n.QMDAT AS DATE)
        FROM [Your HANA schema].QMEL n
        INNER JOIN [Your HANA schema].QALS l ON l.PRUEFLOS = n.PRUEFLOS
        INNER JOIN [Your HANA schema].QAVE v ON v.PRUEFLOS = l.PRUEFLOS
        WHERE CAST(n.AEDAT AS DATE) BETWEEN CAST('[Start date]' AS DATE) AND CAST('[End date]' AS DATE)
          AND [Configure based on your system: usage decision saved condition]

        UNION ALL

        SELECT
            CAST(n.QMNUM AS NVARCHAR(50)),
            'Corrective Action Proposed',
            CAST(n.AEDAT AS TIMESTAMP),
            CAST(n.AENAM AS NVARCHAR(50)),
            CAST(n.QMART AS NVARCHAR(50)),
            CAST(n.QMART AS NVARCHAR(50)),
            CAST(n.MATNR AS NVARCHAR(50)),
            CAST(n.PRIOK AS NVARCHAR(50)),
            CAST(n.URCOD AS NVARCHAR(50)),
            CAST(n.QMDAT AS DATE)
        FROM [Your HANA schema].QMEL n
        INNER JOIN [Your HANA schema].QMFE f ON f.QMNUM = n.QMNUM
        WHERE CAST(n.AEDAT AS DATE) BETWEEN CAST('[Start date]' AS DATE) AND CAST('[End date]' AS DATE)
          AND [Configure based on your system: Corrective Action task creation condition]

        UNION ALL

        SELECT
            CAST(n.QMNUM AS NVARCHAR(50)),
            'Action Plan Approved',
            CAST(n.AEDAT AS TIMESTAMP),
            CAST(n.AENAM AS NVARCHAR(50)),
            CAST(n.QMART AS NVARCHAR(50)),
            CAST(n.QMART AS NVARCHAR(50)),
            CAST(n.MATNR AS NVARCHAR(50)),
            CAST(n.PRIOK AS NVARCHAR(50)),
            CAST(n.URCOD AS NVARCHAR(50)),
            CAST(n.QMDAT AS DATE)
        FROM [Your HANA schema].QMEL n
        INNER JOIN [Your HANA schema].QMFE f ON f.QMNUM = n.QMNUM
        WHERE CAST(n.AEDAT AS DATE) BETWEEN CAST('[Start date]' AS DATE) AND CAST('[End date]' AS DATE)
          AND [Configure based on your system: released action task condition]

        UNION ALL

        SELECT
            CAST(n.QMNUM AS NVARCHAR(50)),
            'Corrective Action Implemented',
            CAST(n.AEDAT AS TIMESTAMP),
            CAST(n.AENAM AS NVARCHAR(50)),
            CAST(n.QMART AS NVARCHAR(50)),
            CAST(n.QMART AS NVARCHAR(50)),
            CAST(n.MATNR AS NVARCHAR(50)),
            CAST(n.PRIOK AS NVARCHAR(50)),
            CAST(n.URCOD AS NVARCHAR(50)),
            CAST(n.QMDAT AS DATE)
        FROM [Your HANA schema].QMEL n
        INNER JOIN [Your HANA schema].QMFE f ON f.QMNUM = n.QMNUM
        WHERE CAST(n.AEDAT AS DATE) BETWEEN CAST('[Start date]' AS DATE) AND CAST('[End date]' AS DATE)
          AND [Configure based on your system: completed Corrective Action task condition]

        UNION ALL

        SELECT
            CAST(n.QMNUM AS NVARCHAR(50)),
            'Preventive Action Proposed',
            CAST(n.AEDAT AS TIMESTAMP),
            CAST(n.AENAM AS NVARCHAR(50)),
            CAST(n.QMART AS NVARCHAR(50)),
            CAST(n.QMART AS NVARCHAR(50)),
            CAST(n.MATNR AS NVARCHAR(50)),
            CAST(n.PRIOK AS NVARCHAR(50)),
            CAST(n.URCOD AS NVARCHAR(50)),
            CAST(n.QMDAT AS DATE)
        FROM [Your HANA schema].QMEL n
        INNER JOIN [Your HANA schema].QMFE f ON f.QMNUM = n.QMNUM
        WHERE CAST(n.AEDAT AS DATE) BETWEEN CAST('[Start date]' AS DATE) AND CAST('[End date]' AS DATE)
          AND [Configure based on your system: Preventive Action task creation condition]

        UNION ALL

        SELECT
            CAST(n.QMNUM AS NVARCHAR(50)),
            'Preventive Action Implemented',
            CAST(n.AEDAT AS TIMESTAMP),
            CAST(n.AENAM AS NVARCHAR(50)),
            CAST(n.QMART AS NVARCHAR(50)),
            CAST(n.QMART AS NVARCHAR(50)),
            CAST(n.MATNR AS NVARCHAR(50)),
            CAST(n.PRIOK AS NVARCHAR(50)),
            CAST(n.URCOD AS NVARCHAR(50)),
            CAST(n.QMDAT AS DATE)
        FROM [Your HANA schema].QMEL n
        INNER JOIN [Your HANA schema].QMFE f ON f.QMNUM = n.QMNUM
        WHERE CAST(n.AEDAT AS DATE) BETWEEN CAST('[Start date]' AS DATE) AND CAST('[End date]' AS DATE)
          AND [Configure based on your system: completed Preventive Action task condition]

        UNION ALL

        SELECT
            CAST(n.QMNUM AS NVARCHAR(50)),
            'Effectiveness Check Required',
            CAST(n.AEDAT AS TIMESTAMP),
            CAST(n.AENAM AS NVARCHAR(50)),
            CAST(n.QMART AS NVARCHAR(50)),
            CAST(n.QMART AS NVARCHAR(50)),
            CAST(n.MATNR AS NVARCHAR(50)),
            CAST(n.PRIOK AS NVARCHAR(50)),
            CAST(n.URCOD AS NVARCHAR(50)),
            CAST(n.QMDAT AS DATE)
        FROM [Your HANA schema].QMEL n
        WHERE CAST(n.AEDAT AS DATE) BETWEEN CAST('[Start date]' AS DATE) AND CAST('[End date]' AS DATE)
          AND [Configure based on your system: effectiveness check required condition]

        UNION ALL

        SELECT
            CAST(n.QMNUM AS NVARCHAR(50)),
            'Action Effectiveness Verified',
            CAST(n.AEDAT AS TIMESTAMP),
            CAST(n.AENAM AS NVARCHAR(50)),
            CAST(n.QMART AS NVARCHAR(50)),
            CAST(n.QMART AS NVARCHAR(50)),
            CAST(n.MATNR AS NVARCHAR(50)),
            CAST(n.PRIOK AS NVARCHAR(50)),
            CAST(n.URCOD AS NVARCHAR(50)),
            CAST(n.QMDAT AS DATE)
        FROM [Your HANA schema].QMEL n
        INNER JOIN [Your HANA schema].QMFE f ON f.QMNUM = n.QMNUM
        WHERE CAST(n.AEDAT AS DATE) BETWEEN CAST('[Start date]' AS DATE) AND CAST('[End date]' AS DATE)
          AND [Configure based on your system: completed effectiveness check condition]

        UNION ALL

        SELECT
            CAST(n.QMNUM AS NVARCHAR(50)),
            'Stakeholders Notified',
            CAST(n.AEDAT AS TIMESTAMP),
            CAST(n.AENAM AS NVARCHAR(50)),
            CAST(n.QMART AS NVARCHAR(50)),
            CAST(n.QMART AS NVARCHAR(50)),
            CAST(n.MATNR AS NVARCHAR(50)),
            CAST(n.PRIOK AS NVARCHAR(50)),
            CAST(n.URCOD AS NVARCHAR(50)),
            CAST(n.QMDAT AS DATE)
        FROM [Your HANA schema].QMEL n
        WHERE CAST(n.AEDAT AS DATE) BETWEEN CAST('[Start date]' AS DATE) AND CAST('[End date]' AS DATE)
          AND [Configure based on your system: stakeholder notification evidence condition]

        UNION ALL

        SELECT
            CAST(n.QMNUM AS NVARCHAR(50)),
            'Notification Completed',
            CAST(n.AEDAT AS TIMESTAMP),
            CAST(n.AENAM AS NVARCHAR(50)),
            CAST(n.QMART AS NVARCHAR(50)),
            CAST(n.QMART AS NVARCHAR(50)),
            CAST(n.MATNR AS NVARCHAR(50)),
            CAST(n.PRIOK AS NVARCHAR(50)),
            CAST(n.URCOD AS NVARCHAR(50)),
            CAST(n.QMDAT AS DATE)
        FROM [Your HANA schema].QMEL n
        WHERE CAST(n.AEDAT AS DATE) BETWEEN CAST('[Start date]' AS DATE) AND CAST('[End date]' AS DATE)
          AND [Configure based on your system: notification completed status condition]

        UNION ALL

        SELECT
            CAST(n.QMNUM AS NVARCHAR(50)),
            'Notification Closed',
            CAST(n.AEDAT AS TIMESTAMP),
            CAST(n.AENAM AS NVARCHAR(50)),
            CAST(n.QMART AS NVARCHAR(50)),
            CAST(n.QMART AS NVARCHAR(50)),
            CAST(n.MATNR AS NVARCHAR(50)),
            CAST(n.PRIOK AS NVARCHAR(50)),
            CAST(n.URCOD AS NVARCHAR(50)),
            CAST(n.QMDAT AS DATE)
        FROM [Your HANA schema].QMEL n
        WHERE CAST(n.AEDAT AS DATE) BETWEEN CAST('[Start date]' AS DATE) AND CAST('[End date]' AS DATE)
          AND [Configure based on your system: notification closed status condition]
    ) q
)
SELECT
    QualityEvent,
    ActivityName,
    EventTimestamp,
    ChangedBy,
    ResponsibleDepartment,
    QualityNotificationType,
    MaterialNumber,
    NotificationPriority,
    RootCauseCode,
    TargetResolutionDate
FROM notification_base
WHERE QualityEvent IS NOT NULL
  AND ActivityName IS NOT NULL
  AND EventTimestamp IS NOT NULL
ORDER BY QualityEvent, EventTimestamp, ActivityName;

import pandas as pd
from pyrfc import Connection
from datetime import datetime, timedelta

class=class="hl-string">"hl-comment"># --- 1. SAP Connection and Extraction Parameters ---
class=class="hl-string">"hl-comment"># IMPORTANT: Use a secure method like environment variables or a key vault for credentials.
SAP_CONNECTION_PARAMS = {
    class="hl-string">"user": class="hl-string">"[Your SAP User]",
    class="hl-string">"passwd": class="hl-string">"[Your Password]",
    class="hl-string">"ashost": class="hl-string">"[Your SAP Host IP/Hostname]",
    class="hl-string">"sysnr": class="hl-string">"[Your System Number]",
    class="hl-string">"client": class="hl-string">"[Your Client]",
}

class=class="hl-string">"hl-comment"># Define the extraction period
end_date = datetime.now()
start_date = end_date - timedelta(days=180) class=class="hl-string">"hl-comment"># Default to last 6 months

class=class="hl-string">"hl-comment"># Define filters (optional)
class=class="hl-string">"hl-comment"># Example: [class="hl-string">'F1', class="hl-string">'F2'] for Customer Complaint and Internal Problem
notification_types = [] class=class="hl-string">"hl-comment"># Leave empty for all types

class=class="hl-string">"hl-comment"># IMPORTANT: Configure your system's specific task codes
TASK_CODE_CONFIG = {
    class="hl-string">'INVESTIGATION': class="hl-string">'[Your Investigation Task Code]',
    class="hl-string">'ROOT_CAUSE': class="hl-string">'[Your Root Cause Task Code]',
    class="hl-string">'CORRECTIVE_ACTION': class="hl-string">'[Your Corrective Action Task Code]',
    class="hl-string">'PREVENTIVE_ACTION': class="hl-string">'[Your Preventive Action Task Code]',
    class="hl-string">'EFFECTIVENESS_CHECK': class="hl-string">'[Your Effectiveness Check Task Code]',
    class="hl-string">'STAKEHOLDER_NOTIFICATION': class="hl-string">'[Your Stakeholder Notification Task Code]'
}

def format_sap_timestamp(sap_date, sap_time):
    class="hl-string">"""Combine SAP date and time fields into a single datetime object."""
    if not sap_date or sap_date == class="hl-string">'00000000':
        return None
    if not sap_time or len(sap_time) < 6:
        sap_time = class="hl-string">'000000'
    return datetime.strptime(fclass="hl-string">"{sap_date}{sap_time}", class="hl-string">'%Y%m%d%H%M%S')

class=class="hl-string">"hl-comment"># --- 2. Main Extraction Logic ---
def extract_quality_events():
    event_log = []
    try:
        class=class="hl-string">"hl-comment"># Establish connection
        conn = Connection(**SAP_CONNECTION_PARAMS)
        print(class="hl-string">"Successfully connected to SAP.")

        class=class="hl-string">"hl-comment"># Get list of Quality Notifications
        print(fclass="hl-string">"Fetching notifications from {start_date.strftime('%Y%m%d')} to {end_date.strftime('%Y%m%d')}...")
        notif_list_result = conn.call(class="hl-string">'BAPI_QUALNOT_GETLIST',
                                      DATE_FROM=start_date.strftime(class="hl-string">'%Y%m%d'),
                                      DATE_TO=end_date.strftime(class="hl-string">'%Y%m%d'),
                                      NOTIF_TYPE=notification_types)

        notif_numbers = [item[class="hl-string">'NOTIF_NO'] for item in notif_list_result[class="hl-string">'NOTIF_LIST']]
        print(fclass="hl-string">"Found {len(notif_numbers)} notifications to process.")

        class=class="hl-string">"hl-comment"># Process each notification to get details
        for i, notif_no in enumerate(notif_numbers):
            print(fclass="hl-string">"Processing notification {i+1}/{len(notif_numbers)}: {notif_no}")
            try:
                detail_result = conn.call(class="hl-string">'BAPI_QUALNOT_GETDETAIL', NOTIF_NO=notif_no)
                header = detail_result.get(class="hl-string">'NOTIFHEADER_EXPORT', {})
                tasks = detail_result.get(class="hl-string">'NOTIFTASK_EXPORT', [])
                statuses = detail_result.get(class="hl-string">'NOTIFSTATUS', [])

                if not header:
                    continue

                class=class="hl-string">"hl-comment"># Common attributes for all events for this case
                common_attrs = {
                    class="hl-string">'QualityEvent': header.get(class="hl-string">'NOTIF_NO'),
                    class="hl-string">'QualityNotificationType': header.get(class="hl-string">'NOTIF_TYPE'),
                    class="hl-string">'MaterialNumber': header.get(class="hl-string">'MATERIAL'),
                    class="hl-string">'NotificationPriority': header.get(class="hl-string">'PRIORITY'),
                    class="hl-string">'TargetResolutionDate': format_sap_timestamp(header.get(class="hl-string">'REQ_END_D'), header.get(class="hl-string">'REQ_END_T')),
                    class="hl-string">'ResponsibleDepartment': header.get(class="hl-string">'DEPT_RESP'),
                    class=class="hl-string">"hl-comment"># Root Cause is often at cause level, this is a simplification
                    class="hl-string">'RootCauseCode': detail_result.get(class="hl-string">'NOTIFCAUS_EXPORT', [{}])[0].get(class="hl-string">'CAUSE_CODE')
                }

                class=class="hl-string">"hl-comment"># --- Generate events based on BAPI data ---

                class=class="hl-string">"hl-comment"># 1. Quality Notification Created
                created_ts = format_sap_timestamp(header.get(class="hl-string">'CREATE_DATE'), header.get(class="hl-string">'CREATE_TIME'))
                if created_ts:
                    event_log.append({**common_attrs, class="hl-string">'ActivityName': class="hl-string">'Quality Notification Created', class="hl-string">'EventTimestamp': created_ts, class="hl-string">'ChangedBy': header.get(class="hl-string">'CREATED_BY')})

                class=class="hl-string">"hl-comment"># 2. Notification Put In Process
                in_process_status = next((s for s in statuses if s[class="hl-string">'STAT'].startswith(class="hl-string">'I0002')), None)
                if in_process_status:
                    class=class="hl-string">"hl-comment"># BAPI doesn't provide easy access to status change timestamp, using header change date as approximation
                    changed_ts = format_sap_timestamp(header.get(class="hl-string">'CHANGE_DATE'), header.get(class="hl-string">'CHANGE_TIME'))
                    event_log.append({**common_attrs, class="hl-string">'ActivityName': class="hl-string">'Notification Put In Process', class="hl-string">'EventTimestamp': changed_ts, class="hl-string">'ChangedBy': header.get(class="hl-string">'CHANGED_BY')})

                class=class="hl-string">"hl-comment"># 3-13. Events from Tasks
                for task in tasks:
                    task_code = task.get(class="hl-string">'TASK_CODE')
                    task_created_ts = format_sap_timestamp(task.get(class="hl-string">'CREATE_DATE'), task.get(class="hl-string">'CREATE_TIME'))
                    task_completed_ts = format_sap_timestamp(task.get(class="hl-string">'COMPLETE_DATE'), task.get(class="hl-string">'COMPLETE_TIME'))
                    task_released_ts = format_sap_timestamp(task.get(class="hl-string">'RELEASE_DATE'), None) class=class="hl-string">"hl-comment"># Time not available for release
                    user = task.get(class="hl-string">'COMPLETED_BY') or task.get(class="hl-string">'CREATED_BY')

                    if task_code == TASK_CODE_CONFIG[class="hl-string">'INVESTIGATION'] and task_created_ts:
                        event_log.append({**common_attrs, class="hl-string">'ActivityName': class="hl-string">'Investigation Task Assigned', class="hl-string">'EventTimestamp': task_created_ts, class="hl-string">'ChangedBy': task.get(class="hl-string">'CREATED_BY')})
                    elif task_code == TASK_CODE_CONFIG[class="hl-string">'ROOT_CAUSE'] and task_completed_ts:
                        event_log.append({**common_attrs, class="hl-string">'ActivityName': class="hl-string">'Root Cause Analysis Completed', class="hl-string">'EventTimestamp': task_completed_ts, class="hl-string">'ChangedBy': user})
                    elif task_code == TASK_CODE_CONFIG[class="hl-string">'CORRECTIVE_ACTION']:
                        if task_created_ts:
                            event_log.append({**common_attrs, class="hl-string">'ActivityName': class="hl-string">'Corrective Action Proposed', class="hl-string">'EventTimestamp': task_created_ts, class="hl-string">'ChangedBy': task.get(class="hl-string">'CREATED_BY')})
                        if task_released_ts: class=class="hl-string">"hl-comment"># Approximating approval with release
                            event_log.append({**common_attrs, class="hl-string">'ActivityName': class="hl-string">'Action Plan Approved', class="hl-string">'EventTimestamp': task_released_ts, class="hl-string">'ChangedBy': task.get(class="hl-string">'CHANGED_BY')})
                        if task_completed_ts:
                            event_log.append({**common_attrs, class="hl-string">'ActivityName': class="hl-string">'Corrective Action Implemented', class="hl-string">'EventTimestamp': task_completed_ts, class="hl-string">'ChangedBy': user})
                    elif task_code == TASK_CODE_CONFIG[class="hl-string">'PREVENTIVE_ACTION']:
                        if task_created_ts:
                            event_log.append({**common_attrs, class="hl-string">'ActivityName': class="hl-string">'Preventive Action Proposed', class="hl-string">'EventTimestamp': task_created_ts, class="hl-string">'ChangedBy': task.get(class="hl-string">'CREATED_BY')})
                        if task_completed_ts:
                            event_log.append({**common_attrs, class="hl-string">'ActivityName': class="hl-string">'Preventive Action Implemented', class="hl-string">'EventTimestamp': task_completed_ts, class="hl-string">'ChangedBy': user})
                    elif task_code == TASK_CODE_CONFIG[class="hl-string">'EFFECTIVENESS_CHECK']:
                        if task_created_ts:
                            event_log.append({**common_attrs, class="hl-string">'ActivityName': class="hl-string">'Effectiveness Check Required', class="hl-string">'EventTimestamp': task_created_ts, class="hl-string">'ChangedBy': task.get(class="hl-string">'CREATED_BY')})
                        if task_completed_ts:
                            event_log.append({**common_attrs, class="hl-string">'ActivityName': class="hl-string">'Action Effectiveness Verified', class="hl-string">'EventTimestamp': task_completed_ts, class="hl-string">'ChangedBy': user})
                    elif task_code == TASK_CODE_CONFIG[class="hl-string">'STAKEHOLDER_NOTIFICATION'] and task_completed_ts:
                        event_log.append({**common_attrs, class="hl-string">'ActivityName': class="hl-string">'Stakeholders Notified', class="hl-string">'EventTimestamp': task_completed_ts, class="hl-string">'ChangedBy': user})
                
                class=class="hl-string">"hl-comment"># 5. Usage Decision Made (Conceptual - requires Inspection Lot logic)
                class=class="hl-string">"hl-comment"># This is a placeholder. A full implementation would check for a linked inspection lot
                class=class="hl-string">"hl-comment"># (e.g., in notification long text or object key tables) and then call another BAPI
                class=class="hl-string">"hl-comment"># like BAPI_INSPLOT_GETUSAGEDECISION to get the decision date.

                class=class="hl-string">"hl-comment"># 14. Notification Completed
                completed_status = next((s for s in statuses if s[class="hl-string">'STAT'].startswith(class="hl-string">'I0045')), None)
                if completed_status:
                    completed_ts = format_sap_timestamp(header.get(class="hl-string">'COMPLETE_DATE'), header.get(class="hl-string">'COMPLETE_TIME'))
                    if completed_ts:
                        event_log.append({**common_attrs, class="hl-string">'ActivityName': class="hl-string">'Notification Completed', class="hl-string">'EventTimestamp': completed_ts, class="hl-string">'ChangedBy': header.get(class="hl-string">'CHANGED_BY')})

                class=class="hl-string">"hl-comment"># 15. Notification Closed
                closed_status = next((s for s in statuses if s[class="hl-string">'STAT'].startswith(class="hl-string">'I0046')), None)
                if closed_status:
                    class=class="hl-string">"hl-comment"># Using header change date as approximation for close date
                    changed_ts = format_sap_timestamp(header.get(class="hl-string">'CHANGE_DATE'), header.get(class="hl-string">'CHANGE_TIME'))
                    event_log.append({**common_attrs, class="hl-string">'ActivityName': class="hl-string">'Notification Closed', class="hl-string">'EventTimestamp': changed_ts, class="hl-string">'ChangedBy': header.get(class="hl-string">'CHANGED_BY')})

            except Exception as e:
                print(fclass="hl-string">"  - Error processing notification {notif_no}: {e}")

        conn.close()
        print(class="hl-string">"Connection to SAP closed.")

    except Exception as e:
        print(fclass="hl-string">"An error occurred: {e}")
        return None

    if not event_log:
        print(class="hl-string">"No events were generated.")
        return pd.DataFrame()

    class=class="hl-string">"hl-comment"># Convert to DataFrame and sort for chronological order
    df = pd.DataFrame(event_log)
    df = df.sort_values(by=[class="hl-string">'QualityEvent', class="hl-string">'EventTimestamp']).reset_index(drop=True)
    return df

class=class="hl-string">"hl-comment"># --- 3. Run Extraction and Save to CSV ---
if __name__ == class="hl-string">"__main__":
    event_log_df = extract_quality_events()

    if event_log_df is not None and not event_log_df.empty:
        print(class="hl-string">"\nExtraction complete. Event log preview:")
        print(event_log_df.head())
        print(fclass="hl-string">"\nTotal events extracted: {len(event_log_df)}")
        
        class=class="hl-string">"hl-comment"># Save to CSV
        output_filename = class="hl-string">'quality_management_event_log.csv'
        event_log_df.to_csv(output_filename, index=False, date_format=class="hl-string">'%Y-%m-%dT%H:%M:%S')
        print(fclass="hl-string">"Event log saved to {output_filename}")

REPORT ZQM_PROCESS_EXTRACTION.

*&---------------------------------------------------------------------*
*&-- Data Structure for the Final Event Log
*&---------------------------------------------------------------------*
TYPES: BEGIN OF ty_event_log,
         qualityevent          TYPE qmnum,
         activityname          TYPE string,
         eventtimestamp        TYPE timestamp,
         changedby             TYPE ernam,
         responsibledepartment TYPE veran_q,
         qualitynotificationtype TYPE qmart,
         materialnumber        TYPE matnr,
         notificationpriority  TYPE qmpr,
         rootcausecode         TYPE urcod,
         targetresolutiondate  TYPE qtrdd,
       END OF ty_event_log.

DATA: lt_event_log TYPE TABLE OF ty_event_log,
      ls_event_log TYPE ty_event_log.

*&---------------------------------------------------------------------*
*&-- Selection Screen Parameters
*&---------------------------------------------------------------------*
SELECT-OPTIONS: s_erdat FOR qmel-erdat OBLIGATORY,
                s_qmart FOR qmel-qmart.
PARAMETERS: p_file TYPE string DEFAULT '/usr/sap/trans/tmp/qm_event_log.csv'.

*&---------------------------------------------------------------------*
*&-- Main Program Logic
*&---------------------------------------------------------------------*
START-OF-SELECTION.

  DATA: lv_timestamp TYPE string.

*-- 1. Quality Notification Created
  SELECT qmnum, erdat, erzeit, ernam, veran, qmart, matnr, qmpr, qtrdd
    FROM qmel
    INTO TABLE @DATA(lt_qmel)
    WHERE erdat IN @s_erdat
      AND qmart IN @s_qmart.

  IF lt_qmel IS NOT INITIAL.
    LOOP AT lt_qmel INTO DATA(ls_qmel).
      ls_event_log-qualityevent          = ls_qmel-qmnum.
      ls_event_log-activityname          = 'Quality Notification Created'.
      CONCATENATE ls_qmel-erdat ls_qmel-erzeit INTO lv_timestamp.
      CONVERT DATE ls_qmel-erdat TIME ls_qmel-erzeit INTO TIME STAMP ls_event_log-eventtimestamp TIME ZONE sy-zonlo.
      ls_event_log-changedby             = ls_qmel-ernam.
      ls_event_log-responsibledepartment = ls_qmel-veran.
      ls_event_log-qualitynotificationtype = ls_qmel-qmart.
      ls_event_log-materialnumber        = ls_qmel-matnr.
      ls_event_log-notificationpriority  = ls_qmel-qmpr.
      ls_event_log-targetresolutiondate  = ls_qmel-qtrdd.
      CLEAR ls_event_log-rootcausecode.
      APPEND ls_event_log TO lt_event_log.
    ENDLOOP.
  ENDIF.

*-- 2, 14, 15. Status Changes: Put In Process, Completed, Closed
  SELECT qmel~qmnum, qmel~veran, qmel~qmart, qmel~matnr, qmel~qmpr, qmel~qtrdd, jcds~udate, jcds~utime, jcds~usnam, jcds~stat
    FROM jcds
    INNER JOIN jhst ON jhst~objnr = jcds~objnr AND jhst~spras = sy-langu
    INNER JOIN qmel ON qmel~objnr = jcds~objnr
    INTO TABLE @DATA(lt_jcds)
    WHERE qmel~erdat IN @s_erdat
      AND qmel~qmart IN @s_qmart
      AND jcds~stat IN ('I0002', 'I0045', 'I0046') "NOIN, NOCO, NOCL
      AND jcds~chgnr > 1 " Ignore initial creation status
      AND jcds~inact = ''.

  LOOP AT lt_jcds INTO DATA(ls_jcds).
    ls_event_log-qualityevent = ls_jcds-qmnum.
    CASE ls_jcds-stat.
      WHEN 'I0002'.
        ls_event_log-activityname = 'Notification Put In Process'.
      WHEN 'I0045'.
        ls_event_log-activityname = 'Notification Completed'.
      WHEN 'I0046'.
        ls_event_log-activityname = 'Notification Closed'.
    ENDCASE.
    CONVERT DATE ls_jcds-udate TIME ls_jcds-utime INTO TIME STAMP ls_event_log-eventtimestamp TIME ZONE sy-zonlo.
    ls_event_log-changedby             = ls_jcds-usnam.
    ls_event_log-responsibledepartment = ls_jcds-veran.
    ls_event_log-qualitynotificationtype = ls_jcds-qmart.
    ls_event_log-materialnumber        = ls_jcds-matnr.
    ls_event_log-notificationpriority  = ls_jcds-qmpr.
    ls_event_log-targetresolutiondate  = ls_jcds-qtrdd.
    CLEAR ls_event_log-rootcausecode.
    APPEND ls_event_log TO lt_event_log.
  ENDLOOP.

*-- 3, 4, 6, 8, 9, 10, 11, 12, 13. Task-based events
  SELECT qmel~qmnum, qmel~veran, qmel~qmart, qmel~matnr, qmel~qmpr, qmel~qtrdd,
         qmsm~erdat, qmsm~erzeit, qmsm~ernam, qmsm~pster, qmsm~pstur, qmsm~paenr, qmsm~mnkat, qmsm~mngrp, qmsm~mncod
    FROM qmsm
    INNER JOIN qmel ON qmel~qmnum = qmsm~qmnum
    INTO TABLE @DATA(lt_qmsm)
    WHERE qmel~erdat IN @s_erdat
      AND qmel~qmart IN @s_qmart.

*-- Root Cause from QMUR
  SELECT qmnum, ursac, urgrp, urcod
    FROM qmur
    INTO TABLE @DATA(lt_qmur)
    FOR ALL ENTRIES IN @lt_qmel
    WHERE qmnum = @lt_qmel-qmnum.

  LOOP AT lt_qmsm INTO DATA(ls_qmsm).
*-- Task Assigned / Action Proposed
    CLEAR ls_event_log.
    ls_event_log-qualityevent = ls_qmsm-qmnum.
    CASE ls_qmsm-mngrp.
      WHEN '[Your Investigate Task Group]'. ls_event_log-activityname = 'Investigation Task Assigned'.
      WHEN '[Your Corrective Task Group]'. ls_event_log-activityname = 'Corrective Action Proposed'.
      WHEN '[Your Preventive Task Group]'. ls_event_log-activityname = 'Preventive Action Proposed'.
      WHEN '[Your Effectiveness Task Group]'. ls_event_log-activityname = 'Effectiveness Check Required'.
      WHEN '[Your Stakeholder Notify Task Group]'. ls_event_log-activityname = 'Stakeholders Notified'.
      WHEN OTHERS. CONTINUE. " Skip tasks not relevant for process mining
    ENDCASE.
    CONVERT DATE ls_qmsm-erdat TIME ls_qmsm-erzeit INTO TIME STAMP ls_event_log-eventtimestamp TIME ZONE sy-zonlo.
    ls_event_log-changedby = ls_qmsm-ernam.
    ls_event_log-responsibledepartment = ls_qmsm-veran.
    ls_event_log-qualitynotificationtype = ls_qmsm-qmart.
    ls_event_log-materialnumber = ls_qmsm-matnr.
    ls_event_log-notificationpriority = ls_qmsm-qmpr.
    ls_event_log-targetresolutiondate = ls_qmsm-qtrdd.
    APPEND ls_event_log TO lt_event_log.

*-- Task Completed / Action Implemented / Verified
    IF ls_qmsm-pster IS NOT INITIAL AND ls_qmsm-pster <> '00000000'.
      CLEAR ls_event_log.
      ls_event_log-qualityevent = ls_qmsm-qmnum.
      CASE ls_qmsm-mngrp.
        WHEN '[Your Investigate Task Group]'. ls_event_log-activityname = 'Root Cause Analysis Completed'.
        WHEN '[Your Corrective Task Group]'. ls_event_log-activityname = 'Corrective Action Implemented'.
        WHEN '[Your Preventive Task Group]'. ls_event_log-activityname = 'Preventive Action Implemented'.
        WHEN '[Your Effectiveness Task Group]'. ls_event_log-activityname = 'Action Effectiveness Verified'.
        WHEN OTHERS. CONTINUE.
      ENDCASE.
      CONVERT DATE ls_qmsm-pster TIME ls_qmsm-pstur INTO TIME STAMP ls_event_log-eventtimestamp TIME ZONE sy-zonlo.
      ls_event_log-changedby = ls_qmsm-paenr.
      ls_event_log-responsibledepartment = ls_qmsm-veran.
      ls_event_log-qualitynotificationtype = ls_qmsm-qmart.
      ls_event_log-materialnumber = ls_qmsm-matnr.
      ls_event_log-notificationpriority = ls_qmsm-qmpr.
      ls_event_log-targetresolutiondate = ls_qmsm-qtrdd.
      READ TABLE lt_qmur INTO DATA(ls_qmur) WITH KEY qmnum = ls_qmsm-qmnum.
      IF sy-subrc = 0. ls_event_log-rootcausecode = ls_qmur-urcod. ENDIF.
      APPEND ls_event_log TO lt_event_log.
    ENDIF.
  ENDLOOP.

*-- 5. Usage Decision Made
  SELECT qmel~qmnum, qmel~veran, qmel~qmart, qmel~matnr, qmel~qmpr, qmel~qtrdd,
         qave~vdatum, qave~vzeit, qave~vaenr
    FROM qamb
    INNER JOIN qals ON qals~prueflos = qamb~prueflos
    INNER JOIN qave ON qave~prueflos = qals~prueflos
    INNER JOIN qmel ON qmel~qmnum = qamb~qmnum
    INTO TABLE @DATA(lt_qave)
    WHERE qmel~erdat IN @s_erdat
      AND qmel~qmart IN @s_qmart
      AND qave~vdatum <> '00000000'.

  LOOP AT lt_qave INTO DATA(ls_qave).
    ls_event_log-qualityevent          = ls_qave-qmnum.
    ls_event_log-activityname          = 'Usage Decision Made'.
    CONVERT DATE ls_qave-vdatum TIME ls_qave-vzeit INTO TIME STAMP ls_event_log-eventtimestamp TIME ZONE sy-zonlo.
    ls_event_log-changedby             = ls_qave-vaenr.
    ls_event_log-responsibledepartment = ls_qave-veran.
    ls_event_log-qualitynotificationtype = ls_qave-qmart.
    ls_event_log-materialnumber        = ls_qave-matnr.
    ls_event_log-notificationpriority  = ls_qave-qmpr.
    ls_event_log-targetresolutiondate  = ls_qave-qtrdd.
    CLEAR ls_event_log-rootcausecode.
    APPEND ls_event_log TO lt_event_log.
  ENDLOOP.

*-- NOTE: 'Action Plan Approved' is highly specific. It might be a task status change.
*-- This requires reading JCDS again for task object numbers (from QMSM-OBJNR)
*-- This example omits it for simplicity but it can be added here.

*-- Sort final table chronologically
  SORT lt_event_log BY qualityevent eventtimestamp.

*-- Write to file
  OPEN DATASET p_file FOR OUTPUT IN TEXT MODE ENCODING DEFAULT.
  IF sy-subrc <> 0.
    MESSAGE 'Error opening file.' TYPE 'E'.
    RETURN.
  ENDIF.

  DATA: lv_line TYPE string.
  DATA(lv_header) = 'QualityEvent;ActivityName;EventTimestamp;ChangedBy;ResponsibleDepartment;QualityNotificationType;MaterialNumber;NotificationPriority;RootCauseCode;TargetResolutionDate'.
  TRANSFER lv_header TO p_file.

  LOOP AT lt_event_log INTO ls_event_log.
    DATA(lv_ts_char) = |{ ls_event_log-eventtimestamp TIMESTAMP = ISO }|.
    CONCATENATE ls_event_log-qualityevent
                ls_event_log-activityname
                lv_ts_char
                ls_event_log-changedby
                ls_event_log-responsibledepartment
                ls_event_log-qualitynotificationtype
                ls_event_log-materialnumber
                ls_event_log-notificationpriority
                ls_event_log-rootcausecode
                ls_event_log-targetresolutiondate
      INTO lv_line SEPARATED BY ';'.
    TRANSFER lv_line TO p_file.
  ENDLOOP.

  CLOSE DATASET p_file.

  WRITE: / 'Extraction complete. File created at:', p_file.