お客様の生産計画データテンプレート

/*
The following ABAP-style SQL script represents the complete logic required to build the event log.
This logic must be implemented within the code sections of the SQ02 InfoSet, as a direct SQL query of this nature is not run.
Each SELECT block corresponds to a specific business activity.
*/

-- 1. Production Order Created
SELECT
  AUFK.AUFNR AS ProductionOrder,
  'Production Order Created' AS ActivityName,
  CAST(JCDS.UDATE AS VARCHAR(8)) || ' ' || CAST(JCDS.UTIME AS VARCHAR(6)) AS EventTime,
  AUFK.WERKS AS ProductionPlant,
  AUFK.MATNR AS ProductCode,
  AFKO.GAMNG AS PlannedQuantity,
  AFKO.GASMG AS DeliveredQuantity,
  AUFK.GSTRP AS ActualStartDate,
  AUFK.GETRI AS ActualEndDate,
  SY-SYSID AS SourceSystem,
  CURRENT_TIMESTAMP AS LastDataUpdate
FROM AUFK
JOIN JCDS ON AUFK.OBJNR = JCDS.OBJNR
WHERE JCDS.STAT = 'I0001' AND JCDS.INACT = ''

UNION ALL

-- 2. Material Availability Checked (using 'Material shortage' status as an indicator)
SELECT
  AUFK.AUFNR AS ProductionOrder,
  'Material Availability Checked' AS ActivityName,
  CAST(JCDS.UDATE AS VARCHAR(8)) || ' ' || CAST(JCDS.UTIME AS VARCHAR(6)) AS EventTime,
  AUFK.WERKS AS ProductionPlant,
  AUFK.MATNR AS ProductCode,
  AFKO.GAMNG AS PlannedQuantity,
  AFKO.GASMG AS DeliveredQuantity,
  AUFK.GSTRP AS ActualStartDate,
  AUFK.GETRI AS ActualEndDate,
  SY-SYSID AS SourceSystem,
  CURRENT_TIMESTAMP AS LastDataUpdate
FROM AUFK
JOIN JCDS ON AUFK.OBJNR = JCDS.OBJNR
WHERE JCDS.STAT = 'I0033' AND JCDS.INACT = '' -- I0033 is MSPT (Material Shortage)

UNION ALL

-- 3. Production Order Released
SELECT
  AUFK.AUFNR AS ProductionOrder,
  'Production Order Released' AS ActivityName,
  CAST(JCDS.UDATE AS VARCHAR(8)) || ' ' || CAST(JCDS.UTIME AS VARCHAR(6)) AS EventTime,
  AUFK.WERKS AS ProductionPlant,
  AUFK.MATNR AS ProductCode,
  AFKO.GAMNG AS PlannedQuantity,
  AFKO.GASMG AS DeliveredQuantity,
  AUFK.GSTRP AS ActualStartDate,
  AUFK.GETRI AS ActualEndDate,
  SY-SYSID AS SourceSystem,
  CURRENT_TIMESTAMP AS LastDataUpdate
FROM AUFK
JOIN JCDS ON AUFK.OBJNR = JCDS.OBJNR
WHERE JCDS.STAT = 'I0002' AND JCDS.INACT = ''

UNION ALL

-- 4. Order Data Changed (using last change date as a proxy)
SELECT
  AUFK.AUFNR AS ProductionOrder,
  'Order Data Changed' AS ActivityName,
  CAST(AUFK.AEDAT AS VARCHAR(8)) || ' 000000' AS EventTime, -- Time is not available in AUFK
  AUFK.WERKS AS ProductionPlant,
  AUFK.MATNR AS ProductCode,
  AFKO.GAMNG AS PlannedQuantity,
  AFKO.GASMG AS DeliveredQuantity,
  AUFK.GSTRP AS ActualStartDate,
  AUFK.GETRI AS ActualEndDate,
  SY-SYSID AS SourceSystem,
  CURRENT_TIMESTAMP AS LastDataUpdate
FROM AUFK
WHERE AUFK.AEDAT IS NOT NULL AND AUFK.AEDAT > AUFK.ERDAT

UNION ALL

-- 5. Goods Issue Posted
SELECT
  AUFK.AUFNR AS ProductionOrder,
  'Goods Issue Posted' AS ActivityName,
  CAST(MKPF.BUDAT AS VARCHAR(8)) || ' ' || CAST(MKPF.CPUTM AS VARCHAR(6)) AS EventTime,
  AUFK.WERKS AS ProductionPlant,
  AUFK.MATNR AS ProductCode,
  AFKO.GAMNG AS PlannedQuantity,
  AFKO.GASMG AS DeliveredQuantity,
  AUFK.GSTRP AS ActualStartDate,
  AUFK.GETRI AS ActualEndDate,
  SY-SYSID AS SourceSystem,
  CURRENT_TIMESTAMP AS LastDataUpdate
FROM AUFK
JOIN MSEG ON AUFK.AUFNR = MSEG.AUFNR
JOIN MKPF ON MSEG.MBLNR = MKPF.MBLNR AND MSEG.MJAHR = MKPF.MJAHR
WHERE MSEG.BWART = '261'

UNION ALL

-- 6. First Confirmation Recorded
SELECT
  CONF.ProductionOrder,
  'First Confirmation Recorded' AS ActivityName,
  CONF.EventTime,
  CONF.ProductionPlant,
  CONF.ProductCode,
  CONF.PlannedQuantity,
  CONF.DeliveredQuantity,
  CONF.ActualStartDate,
  CONF.ActualEndDate,
  CONF.SourceSystem,
  CONF.LastDataUpdate
FROM (
    SELECT
        AUFK.AUFNR AS ProductionOrder,
        CAST(AFRU.BUDAT AS VARCHAR(8)) || ' ' || CAST(AFRU.ERFZEIT AS VARCHAR(6)) AS EventTime,
        AUFK.WERKS AS ProductionPlant,
        AUFK.MATNR AS ProductCode,
        AFKO.GAMNG AS PlannedQuantity,
        AFKO.GASMG AS DeliveredQuantity,
        AUFK.GSTRP AS ActualStartDate,
        AUFK.GETRI AS ActualEndDate,
        SY-SYSID AS SourceSystem,
        CURRENT_TIMESTAMP AS LastDataUpdate,
        ROW_NUMBER() OVER(PARTITION BY AUFK.AUFNR ORDER BY AFRU.BUDAT, AFRU.ERFZEIT) as rn
    FROM AUFK
    JOIN AFRU ON AUFK.AUFNR = AFRU.AUFNR
) AS CONF
WHERE CONF.rn = 1

UNION ALL

-- 7. Intermediate Confirmation Recorded
SELECT
  AUFK.AUFNR AS ProductionOrder,
  'Intermediate Confirmation Recorded' AS ActivityName,
  CAST(AFRU.BUDAT AS VARCHAR(8)) || ' ' || CAST(AFRU.ERFZEIT AS VARCHAR(6)) AS EventTime,
  AUFK.WERKS AS ProductionPlant,
  AUFK.MATNR AS ProductCode,
  AFKO.GAMNG AS PlannedQuantity,
  AFKO.GASMG AS DeliveredQuantity,
  AUFK.GSTRP AS ActualStartDate,
  AUFK.GETRI AS ActualEndDate,
  SY-SYSID AS SourceSystem,
  CURRENT_TIMESTAMP AS LastDataUpdate
FROM AUFK
JOIN AFRU ON AUFK.AUFNR = AFRU.AUFNR
WHERE AFRU.AUERU = 'T' -- T = Partial Confirmation

UNION ALL

-- 8. Final Confirmation Recorded
SELECT
  AUFK.AUFNR AS ProductionOrder,
  'Final Confirmation Recorded' AS ActivityName,
  CAST(AFRU.BUDAT AS VARCHAR(8)) || ' ' || CAST(AFRU.ERFZEIT AS VARCHAR(6)) AS EventTime,
  AUFK.WERKS AS ProductionPlant,
  AUFK.MATNR AS ProductCode,
  AFKO.GAMNG AS PlannedQuantity,
  AFKO.GASMG AS DeliveredQuantity,
  AUFK.GSTRP AS ActualStartDate,
  AUFK.GETRI AS ActualEndDate,
  SY-SYSID AS SourceSystem,
  CURRENT_TIMESTAMP AS LastDataUpdate
FROM AUFK
JOIN AFRU ON AUFK.AUFNR = AFRU.AUFNR
WHERE AFRU.AUERU = 'E' -- E = Final Confirmation

UNION ALL

-- 9. Goods Receipt Posted
SELECT
  AUFK.AUFNR AS ProductionOrder,
  'Goods Receipt Posted' AS ActivityName,
  CAST(MKPF.BUDAT AS VARCHAR(8)) || ' ' || CAST(MKPF.CPUTM AS VARCHAR(6)) AS EventTime,
  AUFK.WERKS AS ProductionPlant,
  AUFK.MATNR AS ProductCode,
  AFKO.GAMNG AS PlannedQuantity,
  AFKO.GASMG AS DeliveredQuantity,
  AUFK.GSTRP AS ActualStartDate,
  AUFK.GETRI AS ActualEndDate,
  SY-SYSID AS SourceSystem,
  CURRENT_TIMESTAMP AS LastDataUpdate
FROM AUFK
JOIN MSEG ON AUFK.AUFNR = MSEG.AUFNR
JOIN MKPF ON MSEG.MBLNR = MKPF.MBLNR AND MSEG.MJAHR = MKPF.MJAHR
WHERE MSEG.BWART = '101'

UNION ALL

-- 10. Order Technically Completed
SELECT
  AUFK.AUFNR AS ProductionOrder,
  'Order Technically Completed' AS ActivityName,
  CAST(JCDS.UDATE AS VARCHAR(8)) || ' ' || CAST(JCDS.UTIME AS VARCHAR(6)) AS EventTime,
  AUFK.WERKS AS ProductionPlant,
  AUFK.MATNR AS ProductCode,
  AFKO.GAMNG AS PlannedQuantity,
  AFKO.GASMG AS DeliveredQuantity,
  AUFK.GSTRP AS ActualStartDate,
  AUFK.GETRI AS ActualEndDate,
  SY-SYSID AS SourceSystem,
  CURRENT_TIMESTAMP AS LastDataUpdate
FROM AUFK
JOIN JCDS ON AUFK.OBJNR = JCDS.OBJNR
WHERE JCDS.STAT = 'I0045' AND JCDS.INACT = ''

UNION ALL

-- 11. Order Settlement Ran (using 'Closed' status as proxy)
SELECT
  AUFK.AUFNR AS ProductionOrder,
  'Order Settlement Ran' AS ActivityName,
  CAST(JCDS.UDATE AS VARCHAR(8)) || ' ' || CAST(JCDS.UTIME AS VARCHAR(6)) AS EventTime,
  AUFK.WERKS AS ProductionPlant,
  AUFK.MATNR AS ProductCode,
  AFKO.GAMNG AS PlannedQuantity,
  AFKO.GASMG AS DeliveredQuantity,
  AUFK.GSTRP AS ActualStartDate,
  AUFK.GETRI AS ActualEndDate,
  SY-SYSID AS SourceSystem,
  CURRENT_TIMESTAMP AS LastDataUpdate
FROM AUFK
JOIN JCDS ON AUFK.OBJNR = JCDS.OBJNR
WHERE JCDS.STAT = 'I0046' AND JCDS.INACT = '' -- I0046 is CLSD (Closed)

UNION ALL

-- 12. Order Cancellation Set
SELECT
  AUFK.AUFNR AS ProductionOrder,
  'Order Cancellation Set' AS ActivityName,
  CAST(JCDS.UDATE AS VARCHAR(8)) || ' ' || CAST(JCDS.UTIME AS VARCHAR(6)) AS EventTime,
  AUFK.WERKS AS ProductionPlant,
  AUFK.MATNR AS ProductCode,
  AFKO.GAMNG AS PlannedQuantity,
  AFKO.GASMG AS DeliveredQuantity,
  AUFK.GSTRP AS ActualStartDate,
  AUFK.GETRI AS ActualEndDate,
  SY-SYSID AS SourceSystem,
  CURRENT_TIMESTAMP AS LastDataUpdate
FROM AUFK
JOIN JCDS ON AUFK.OBJNR = JCDS.OBJNR
WHERE JCDS.STAT = 'I0047' AND JCDS.INACT = '' -- I0047 is CNF (Confirmed), use I0076 for Canceled. Recorrecting. Use Deletion Flag instead.
-- Corrected Logic for Cancellation:
SELECT
  AUFK.AUFNR AS ProductionOrder,
  'Order Cancellation Set' AS ActivityName,
  CAST(AUFK.AEDAT AS VARCHAR(8)) || ' 000000' AS EventTime,
  AUFK.WERKS AS ProductionPlant,
  AUFK.MATNR AS ProductCode,
  AFKO.GAMNG AS PlannedQuantity,
  AFKO.GASMG AS DeliveredQuantity,
  AUFK.GSTRP AS ActualStartDate,
  AUFK.GETRI AS ActualEndDate,
  SY-SYSID AS SourceSystem,
  CURRENT_TIMESTAMP AS LastDataUpdate
FROM AUFK
WHERE AUFK.LOEKZ = 'X';

WITH Orders AS (
    SELECT 
        aufk.AUFNR AS ProductionOrder,
        afko.WERKS AS ProductionPlant,
        afko.PLNBEZ AS ProductCode,
        afko.GAMNG AS PlannedQuantity,
        afko.GMEIN AS PlannedUnit,
        afko.GEMNG AS DeliveredQuantity,
        CASE 
            WHEN afko.GSTRI = '00000000' THEN NULL
            ELSE TO_TIMESTAMP(afko.GSTRI || afko.GSUZI, 'YYYYMMDDHH24MISS')
        END AS ActualStartDate,
        CASE
            WHEN afko.GETRI = '00000000' THEN NULL
            ELSE TO_TIMESTAMP(afko.GETRI || afko.GEUZI, 'YYYYMMDDHH24MISS')
        END AS ActualEndDate,
        aufk.OBJNR,
        aufk.ERDAT AS CreationDate,
        aufk.ERFAS AS CreationTime
    FROM 
        "[Your SAP Schema]".AUFK aufk
    JOIN 
        "[Your SAP Schema]".AFKO afko ON aufk.AUFNR = afko.AUFNR
    WHERE 
        aufk.AUTYP = '10' -- Filter for Production Orders
        AND aufk.ERDAT BETWEEN '[Start Date]' AND '[End Date]'
        -- AND afko.WERKS IN ('[Your Plant]', '[Another Plant]') -- Optional: Filter by Plant
        -- AND aufk.AUART IN ('[Your Order Type]') -- Optional: Filter by Order Type
),
Confirmations AS (
    SELECT 
        o.ProductionOrder,
        a.RUECK AS ConfirmationNumber,
        a.RMZHL AS ConfirmationCounter,
        TO_TIMESTAMP(a.BUDAT || a.UZEIT, 'YYYYMMDDHH24MISS') AS EventTime,
        a.AUERU AS IsFinalConfirmation,
        ROW_NUMBER() OVER(PARTITION BY o.ProductionOrder ORDER BY TO_TIMESTAMP(a.BUDAT || a.UZEIT, 'YYYYMMDDHH24MISS') ASC) as rn_asc,
        ROW_NUMBER() OVER(PARTITION BY o.ProductionOrder ORDER BY TO_TIMESTAMP(a.BUDAT || a.UZEIT, 'YYYYMMDDHH24MISS') DESC) as rn_desc
    FROM 
        Orders o
    JOIN 
        "[Your SAP Schema]".AFRU a ON o.ProductionOrder = a.AUFNR
)
-- 1. Production Order Created
SELECT
    o.ProductionOrder,
    'Production Order Created' AS ActivityName,
    TO_TIMESTAMP(o.CreationDate || o.CreationTime, 'YYYYMMDDHH24MISS') AS EventTime,
    '[Your Source System]' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    o.ProductionPlant,
    o.ProductCode,
    o.PlannedQuantity,
    o.DeliveredQuantity,
    o.ActualStartDate,
    o.ActualEndDate
FROM Orders o

UNION ALL

-- 2. Material Availability Checked (using 'Material Committed' status as proxy)
SELECT 
    o.ProductionOrder,
    'Material Availability Checked' AS ActivityName,
    TO_TIMESTAMP(jcds.UDATE || jcds.UTIME, 'YYYYMMDDHH24MISS') AS EventTime,
    '[Your Source System]' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    o.ProductionPlant,
    o.ProductCode,
    o.PlannedQuantity,
    o.DeliveredQuantity,
    o.ActualStartDate,
    o.ActualEndDate
FROM Orders o
JOIN "[Your SAP Schema]".JCDS jcds ON o.OBJNR = jcds.OBJNR
WHERE jcds.STAT = 'I0006' AND jcds.INACT != 'X'

UNION ALL

-- 3. Production Order Released
SELECT 
    o.ProductionOrder,
    'Production Order Released' AS ActivityName,
    TO_TIMESTAMP(jcds.UDATE || jcds.UTIME, 'YYYYMMDDHH24MISS') AS EventTime,
    '[Your Source System]' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    o.ProductionPlant,
    o.ProductCode,
    o.PlannedQuantity,
    o.DeliveredQuantity,
    o.ActualStartDate,
    o.ActualEndDate
FROM Orders o
JOIN "[Your SAP Schema]".JCDS jcds ON o.OBJNR = jcds.OBJNR
WHERE jcds.STAT = 'I0002' AND jcds.INACT != 'X'

UNION ALL

-- 4. Order Data Changed
SELECT 
    o.ProductionOrder,
    'Order Data Changed' AS ActivityName,
    TO_TIMESTAMP(aufk.AEDAT || aufk.AEZEIT, 'YYYYMMDDHH24MISS') AS EventTime,
    '[Your Source System]' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    o.ProductionPlant,
    o.ProductCode,
    o.PlannedQuantity,
    o.DeliveredQuantity,
    o.ActualStartDate,
    o.ActualEndDate
FROM Orders o
JOIN "[Your SAP Schema]".AUFK aufk ON o.ProductionOrder = aufk.AUFNR
WHERE aufk.AEDAT > o.CreationDate

UNION ALL

-- 5. Goods Issue Posted
SELECT 
    o.ProductionOrder,
    'Goods Issue Posted' AS ActivityName,
    TO_TIMESTAMP(mkpf.CPUDT || mkpf.CPUTM, 'YYYYMMDDHH24MISS') AS EventTime,
    '[Your Source System]' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    o.ProductionPlant,
    o.ProductCode,
    o.PlannedQuantity,
    o.DeliveredQuantity,
    o.ActualStartDate,
    o.ActualEndDate
FROM Orders o
JOIN "[Your SAP Schema]".MSEG mseg ON o.ProductionOrder = mseg.AUFNR
JOIN "[Your SAP Schema]".MKPF mkpf ON mseg.MBLNR = mkpf.MBLNR AND mseg.MJAHR = mkpf.MJAHR
WHERE mseg.BWART = '261'

UNION ALL

-- 6. First Confirmation Recorded
SELECT
    c.ProductionOrder,
    'First Confirmation Recorded' AS ActivityName,
    c.EventTime,
    '[Your Source System]' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    o.ProductionPlant,
    o.ProductCode,
    o.PlannedQuantity,
    o.DeliveredQuantity,
    o.ActualStartDate,
    o.ActualEndDate
FROM Confirmations c
JOIN Orders o ON c.ProductionOrder = o.ProductionOrder
WHERE c.rn_asc = 1

UNION ALL

-- 7. Intermediate Confirmation Recorded
SELECT
    c.ProductionOrder,
    'Intermediate Confirmation Recorded' AS ActivityName,
    c.EventTime,
    '[Your Source System]' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    o.ProductionPlant,
    o.ProductCode,
    o.PlannedQuantity,
    o.DeliveredQuantity,
    o.ActualStartDate,
    o.ActualEndDate
FROM Confirmations c
JOIN Orders o ON c.ProductionOrder = o.ProductionOrder
WHERE c.rn_asc > 1 AND c.IsFinalConfirmation IS NULL

UNION ALL

-- 8. Final Confirmation Recorded
SELECT
    c.ProductionOrder,
    'Final Confirmation Recorded' AS ActivityName,
    c.EventTime,
    '[Your Source System]' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    o.ProductionPlant,
    o.ProductCode,
    o.PlannedQuantity,
    o.DeliveredQuantity,
    o.ActualStartDate,
    o.ActualEndDate
FROM Confirmations c
JOIN Orders o ON c.ProductionOrder = o.ProductionOrder
WHERE c.IsFinalConfirmation = 'X'

UNION ALL

-- 9. Goods Receipt Posted
SELECT 
    o.ProductionOrder,
    'Goods Receipt Posted' AS ActivityName,
    TO_TIMESTAMP(mkpf.CPUDT || mkpf.CPUTM, 'YYYYMMDDHH24MISS') AS EventTime,
    '[Your Source System]' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    o.ProductionPlant,
    o.ProductCode,
    o.PlannedQuantity,
    o.DeliveredQuantity,
    o.ActualStartDate,
    o.ActualEndDate
FROM Orders o
JOIN "[Your SAP Schema]".MSEG mseg ON o.ProductionOrder = mseg.AUFNR
JOIN "[Your SAP Schema]".MKPF mkpf ON mseg.MBLNR = mkpf.MBLNR AND mseg.MJAHR = mkpf.MJAHR
WHERE mseg.BWART = '101'

UNION ALL

-- 10. Order Technically Completed
SELECT 
    o.ProductionOrder,
    'Order Technically Completed' AS ActivityName,
    TO_TIMESTAMP(jcds.UDATE || jcds.UTIME, 'YYYYMMDDHH24MISS') AS EventTime,
    '[Your Source System]' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    o.ProductionPlant,
    o.ProductCode,
    o.PlannedQuantity,
    o.DeliveredQuantity,
    o.ActualStartDate,
    o.ActualEndDate
FROM Orders o
JOIN "[Your SAP Schema]".JCDS jcds ON o.OBJNR = jcds.OBJNR
WHERE jcds.STAT = 'I0045' AND jcds.INACT != 'X'

UNION ALL

-- 11. Order Settlement Ran
SELECT 
    o.ProductionOrder,
    'Order Settlement Ran' AS ActivityName,
    TO_TIMESTAMP(jcds.UDATE || jcds.UTIME, 'YYYYMMDDHH24MISS') AS EventTime,
    '[Your Source System]' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    o.ProductionPlant,
    o.ProductCode,
    o.PlannedQuantity,
    o.DeliveredQuantity,
    o.ActualStartDate,
    o.ActualEndDate
FROM Orders o
JOIN "[Your SAP Schema]".JCDS jcds ON o.OBJNR = jcds.OBJNR
WHERE jcds.STAT = 'I0046' AND jcds.INACT != 'X'

UNION ALL

-- 12. Order Cancellation Set
SELECT 
    o.ProductionOrder,
    'Order Cancellation Set' AS ActivityName,
    TO_TIMESTAMP(jcds.UDATE || jcds.UTime, 'YYYYMMDDHH24MISS') AS EventTime,
    '[Your Source System]' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    o.ProductionPlant,
    o.ProductCode,
    o.PlannedQuantity,
    o.DeliveredQuantity,
    o.ActualStartDate,
    o.ActualEndDate
FROM Orders o
JOIN "[Your SAP Schema]".JCDS jcds ON o.OBJNR = jcds.OBJNR
WHERE jcds.STAT = 'I0076' AND jcds.INACT != 'X';

/*
The following ABAP-style SQL script represents the complete logic required to build the event log.
This logic must be implemented within the code sections of the SQ02 InfoSet, as a direct SQL query of this nature is not run.
Each SELECT block corresponds to a specific business activity.
*/

-- 1. Production Order Created
SELECT
  AUFK.AUFNR AS ProductionOrder,
  'Production Order Created' AS ActivityName,
  CAST(JCDS.UDATE AS VARCHAR(8)) || ' ' || CAST(JCDS.UTIME AS VARCHAR(6)) AS EventTime,
  AUFK.WERKS AS ProductionPlant,
  AUFK.MATNR AS ProductCode,
  AFKO.GAMNG AS PlannedQuantity,
  AFKO.GASMG AS DeliveredQuantity,
  AUFK.GSTRP AS ActualStartDate,
  AUFK.GETRI AS ActualEndDate,
  SY-SYSID AS SourceSystem,
  CURRENT_TIMESTAMP AS LastDataUpdate
FROM AUFK
JOIN JCDS ON AUFK.OBJNR = JCDS.OBJNR
WHERE JCDS.STAT = 'I0001' AND JCDS.INACT = ''

UNION ALL

-- 2. Material Availability Checked (using 'Material shortage' status as an indicator)
SELECT
  AUFK.AUFNR AS ProductionOrder,
  'Material Availability Checked' AS ActivityName,
  CAST(JCDS.UDATE AS VARCHAR(8)) || ' ' || CAST(JCDS.UTIME AS VARCHAR(6)) AS EventTime,
  AUFK.WERKS AS ProductionPlant,
  AUFK.MATNR AS ProductCode,
  AFKO.GAMNG AS PlannedQuantity,
  AFKO.GASMG AS DeliveredQuantity,
  AUFK.GSTRP AS ActualStartDate,
  AUFK.GETRI AS ActualEndDate,
  SY-SYSID AS SourceSystem,
  CURRENT_TIMESTAMP AS LastDataUpdate
FROM AUFK
JOIN JCDS ON AUFK.OBJNR = JCDS.OBJNR
WHERE JCDS.STAT = 'I0033' AND JCDS.INACT = '' -- I0033 is MSPT (Material Shortage)

UNION ALL

-- 3. Production Order Released
SELECT
  AUFK.AUFNR AS ProductionOrder,
  'Production Order Released' AS ActivityName,
  CAST(JCDS.UDATE AS VARCHAR(8)) || ' ' || CAST(JCDS.UTIME AS VARCHAR(6)) AS EventTime,
  AUFK.WERKS AS ProductionPlant,
  AUFK.MATNR AS ProductCode,
  AFKO.GAMNG AS PlannedQuantity,
  AFKO.GASMG AS DeliveredQuantity,
  AUFK.GSTRP AS ActualStartDate,
  AUFK.GETRI AS ActualEndDate,
  SY-SYSID AS SourceSystem,
  CURRENT_TIMESTAMP AS LastDataUpdate
FROM AUFK
JOIN JCDS ON AUFK.OBJNR = JCDS.OBJNR
WHERE JCDS.STAT = 'I0002' AND JCDS.INACT = ''

UNION ALL

-- 4. Order Data Changed (using last change date as a proxy)
SELECT
  AUFK.AUFNR AS ProductionOrder,
  'Order Data Changed' AS ActivityName,
  CAST(AUFK.AEDAT AS VARCHAR(8)) || ' 000000' AS EventTime, -- Time is not available in AUFK
  AUFK.WERKS AS ProductionPlant,
  AUFK.MATNR AS ProductCode,
  AFKO.GAMNG AS PlannedQuantity,
  AFKO.GASMG AS DeliveredQuantity,
  AUFK.GSTRP AS ActualStartDate,
  AUFK.GETRI AS ActualEndDate,
  SY-SYSID AS SourceSystem,
  CURRENT_TIMESTAMP AS LastDataUpdate
FROM AUFK
WHERE AUFK.AEDAT IS NOT NULL AND AUFK.AEDAT > AUFK.ERDAT

UNION ALL

-- 5. Goods Issue Posted
SELECT
  AUFK.AUFNR AS ProductionOrder,
  'Goods Issue Posted' AS ActivityName,
  CAST(MKPF.BUDAT AS VARCHAR(8)) || ' ' || CAST(MKPF.CPUTM AS VARCHAR(6)) AS EventTime,
  AUFK.WERKS AS ProductionPlant,
  AUFK.MATNR AS ProductCode,
  AFKO.GAMNG AS PlannedQuantity,
  AFKO.GASMG AS DeliveredQuantity,
  AUFK.GSTRP AS ActualStartDate,
  AUFK.GETRI AS ActualEndDate,
  SY-SYSID AS SourceSystem,
  CURRENT_TIMESTAMP AS LastDataUpdate
FROM AUFK
JOIN MSEG ON AUFK.AUFNR = MSEG.AUFNR
JOIN MKPF ON MSEG.MBLNR = MKPF.MBLNR AND MSEG.MJAHR = MKPF.MJAHR
WHERE MSEG.BWART = '261'

UNION ALL

-- 6. First Confirmation Recorded
SELECT
  CONF.ProductionOrder,
  'First Confirmation Recorded' AS ActivityName,
  CONF.EventTime,
  CONF.ProductionPlant,
  CONF.ProductCode,
  CONF.PlannedQuantity,
  CONF.DeliveredQuantity,
  CONF.ActualStartDate,
  CONF.ActualEndDate,
  CONF.SourceSystem,
  CONF.LastDataUpdate
FROM (
    SELECT
        AUFK.AUFNR AS ProductionOrder,
        CAST(AFRU.BUDAT AS VARCHAR(8)) || ' ' || CAST(AFRU.ERFZEIT AS VARCHAR(6)) AS EventTime,
        AUFK.WERKS AS ProductionPlant,
        AUFK.MATNR AS ProductCode,
        AFKO.GAMNG AS PlannedQuantity,
        AFKO.GASMG AS DeliveredQuantity,
        AUFK.GSTRP AS ActualStartDate,
        AUFK.GETRI AS ActualEndDate,
        SY-SYSID AS SourceSystem,
        CURRENT_TIMESTAMP AS LastDataUpdate,
        ROW_NUMBER() OVER(PARTITION BY AUFK.AUFNR ORDER BY AFRU.BUDAT, AFRU.ERFZEIT) as rn
    FROM AUFK
    JOIN AFRU ON AUFK.AUFNR = AFRU.AUFNR
) AS CONF
WHERE CONF.rn = 1

UNION ALL

-- 7. Intermediate Confirmation Recorded
SELECT
  AUFK.AUFNR AS ProductionOrder,
  'Intermediate Confirmation Recorded' AS ActivityName,
  CAST(AFRU.BUDAT AS VARCHAR(8)) || ' ' || CAST(AFRU.ERFZEIT AS VARCHAR(6)) AS EventTime,
  AUFK.WERKS AS ProductionPlant,
  AUFK.MATNR AS ProductCode,
  AFKO.GAMNG AS PlannedQuantity,
  AFKO.GASMG AS DeliveredQuantity,
  AUFK.GSTRP AS ActualStartDate,
  AUFK.GETRI AS ActualEndDate,
  SY-SYSID AS SourceSystem,
  CURRENT_TIMESTAMP AS LastDataUpdate
FROM AUFK
JOIN AFRU ON AUFK.AUFNR = AFRU.AUFNR
WHERE AFRU.AUERU = 'T' -- T = Partial Confirmation

UNION ALL

-- 8. Final Confirmation Recorded
SELECT
  AUFK.AUFNR AS ProductionOrder,
  'Final Confirmation Recorded' AS ActivityName,
  CAST(AFRU.BUDAT AS VARCHAR(8)) || ' ' || CAST(AFRU.ERFZEIT AS VARCHAR(6)) AS EventTime,
  AUFK.WERKS AS ProductionPlant,
  AUFK.MATNR AS ProductCode,
  AFKO.GAMNG AS PlannedQuantity,
  AFKO.GASMG AS DeliveredQuantity,
  AUFK.GSTRP AS ActualStartDate,
  AUFK.GETRI AS ActualEndDate,
  SY-SYSID AS SourceSystem,
  CURRENT_TIMESTAMP AS LastDataUpdate
FROM AUFK
JOIN AFRU ON AUFK.AUFNR = AFRU.AUFNR
WHERE AFRU.AUERU = 'E' -- E = Final Confirmation

UNION ALL

-- 9. Goods Receipt Posted
SELECT
  AUFK.AUFNR AS ProductionOrder,
  'Goods Receipt Posted' AS ActivityName,
  CAST(MKPF.BUDAT AS VARCHAR(8)) || ' ' || CAST(MKPF.CPUTM AS VARCHAR(6)) AS EventTime,
  AUFK.WERKS AS ProductionPlant,
  AUFK.MATNR AS ProductCode,
  AFKO.GAMNG AS PlannedQuantity,
  AFKO.GASMG AS DeliveredQuantity,
  AUFK.GSTRP AS ActualStartDate,
  AUFK.GETRI AS ActualEndDate,
  SY-SYSID AS SourceSystem,
  CURRENT_TIMESTAMP AS LastDataUpdate
FROM AUFK
JOIN MSEG ON AUFK.AUFNR = MSEG.AUFNR
JOIN MKPF ON MSEG.MBLNR = MKPF.MBLNR AND MSEG.MJAHR = MKPF.MJAHR
WHERE MSEG.BWART = '101'

UNION ALL

-- 10. Order Technically Completed
SELECT
  AUFK.AUFNR AS ProductionOrder,
  'Order Technically Completed' AS ActivityName,
  CAST(JCDS.UDATE AS VARCHAR(8)) || ' ' || CAST(JCDS.UTIME AS VARCHAR(6)) AS EventTime,
  AUFK.WERKS AS ProductionPlant,
  AUFK.MATNR AS ProductCode,
  AFKO.GAMNG AS PlannedQuantity,
  AFKO.GASMG AS DeliveredQuantity,
  AUFK.GSTRP AS ActualStartDate,
  AUFK.GETRI AS ActualEndDate,
  SY-SYSID AS SourceSystem,
  CURRENT_TIMESTAMP AS LastDataUpdate
FROM AUFK
JOIN JCDS ON AUFK.OBJNR = JCDS.OBJNR
WHERE JCDS.STAT = 'I0045' AND JCDS.INACT = ''

UNION ALL

-- 11. Order Settlement Ran (using 'Closed' status as proxy)
SELECT
  AUFK.AUFNR AS ProductionOrder,
  'Order Settlement Ran' AS ActivityName,
  CAST(JCDS.UDATE AS VARCHAR(8)) || ' ' || CAST(JCDS.UTIME AS VARCHAR(6)) AS EventTime,
  AUFK.WERKS AS ProductionPlant,
  AUFK.MATNR AS ProductCode,
  AFKO.GAMNG AS PlannedQuantity,
  AFKO.GASMG AS DeliveredQuantity,
  AUFK.GSTRP AS ActualStartDate,
  AUFK.GETRI AS ActualEndDate,
  SY-SYSID AS SourceSystem,
  CURRENT_TIMESTAMP AS LastDataUpdate
FROM AUFK
JOIN JCDS ON AUFK.OBJNR = JCDS.OBJNR
WHERE JCDS.STAT = 'I0046' AND JCDS.INACT = '' -- I0046 is CLSD (Closed)

UNION ALL

-- 12. Order Cancellation Set
SELECT
  AUFK.AUFNR AS ProductionOrder,
  'Order Cancellation Set' AS ActivityName,
  CAST(JCDS.UDATE AS VARCHAR(8)) || ' ' || CAST(JCDS.UTIME AS VARCHAR(6)) AS EventTime,
  AUFK.WERKS AS ProductionPlant,
  AUFK.MATNR AS ProductCode,
  AFKO.GAMNG AS PlannedQuantity,
  AFKO.GASMG AS DeliveredQuantity,
  AUFK.GSTRP AS ActualStartDate,
  AUFK.GETRI AS ActualEndDate,
  SY-SYSID AS SourceSystem,
  CURRENT_TIMESTAMP AS LastDataUpdate
FROM AUFK
JOIN JCDS ON AUFK.OBJNR = JCDS.OBJNR
WHERE JCDS.STAT = 'I0047' AND JCDS.INACT = '' -- I0047 is CNF (Confirmed), use I0076 for Canceled. Recorrecting. Use Deletion Flag instead.
-- Corrected Logic for Cancellation:
SELECT
  AUFK.AUFNR AS ProductionOrder,
  'Order Cancellation Set' AS ActivityName,
  CAST(AUFK.AEDAT AS VARCHAR(8)) || ' 000000' AS EventTime,
  AUFK.WERKS AS ProductionPlant,
  AUFK.MATNR AS ProductCode,
  AFKO.GAMNG AS PlannedQuantity,
  AFKO.GASMG AS DeliveredQuantity,
  AUFK.GSTRP AS ActualStartDate,
  AUFK.GETRI AS ActualEndDate,
  SY-SYSID AS SourceSystem,
  CURRENT_TIMESTAMP AS LastDataUpdate
FROM AUFK
WHERE AUFK.LOEKZ = 'X';