Votre modèle de données pour la planification de la production

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

REPORT [Your custom report name].

* This executable ABAP report is a complete extraction framework for the twelve required activities.
* Replace only the marked system-specific source sections, selection parameters, and file configuration.
* Confirm every table and field against the target SAP ECC system before activation.

PARAMETERS p_from TYPE sydatum OBLIGATORY.
PARAMETERS p_to   TYPE sydatum OBLIGATORY.
PARAMETERS p_file TYPE string OBLIGATORY DEFAULT '[Application server file path]'.
PARAMETERS p_src  TYPE string OBLIGATORY DEFAULT '[SAP ECC system identifier]'.

SELECT-OPTIONS s_werks FOR [Production plant field].
SELECT-OPTIONS s_aufnr FOR [Production order field].
SELECT-OPTIONS s_auart FOR [Production order type field].
SELECT-OPTIONS s_bukrs FOR [Company code field].

TYPES: BEGIN OF ty_event,
         productionorder  TYPE string,
         activityname     TYPE string,
         eventtime        TYPE string,
         sourcesystem     TYPE string,
         lastdataupdate   TYPE string,
         productionplant  TYPE string,
         productcode      TYPE string,
         plannedquantity  TYPE string,
         deliveredquantity TYPE string,
         actualstartdate  TYPE string,
         actualenddate    TYPE string,
       END OF ty_event.

DATA gt_events TYPE STANDARD TABLE OF ty_event WITH EMPTY KEY.
DATA gs_event  TYPE ty_event.
DATA gv_update_timestamp TYPE string.
DATA gv_line TYPE string.

GET TIME STAMP FIELD DATA(gv_update_ts).
gv_update_timestamp = |{ gv_update_ts TIMESTAMP = ISO }|.

* Production Order Created
SELECT [Production order field], [Production plant field], [Product field], [Planned quantity field], [Creation date field], [Creation time field]
  FROM [Production order header table]
  INTO TABLE @DATA(lt_created)
  WHERE [Creation date field] BETWEEN @p_from AND @p_to
    AND [Production order field] IN @s_aufnr
    AND [Production plant field] IN @s_werks
    AND [Production order type field] IN @s_auart
    AND [Company code field] IN @s_bukrs.
LOOP AT lt_created ASSIGNING FIELD-SYMBOL(<created>).
  CLEAR gs_event.
  gs_event-productionorder = |{ <created>-[Production order field] }|.
  gs_event-activityname = 'Production Order Created'.
  gs_event-eventtime = |{ <created>-[Creation date field] } { <created>-[Creation time field] }|.
  gs_event-sourcesystem = p_src.
  gs_event-lastdataupdate = gv_update_timestamp.
  gs_event-productionplant = |{ <created>-[Production plant field] }|.
  gs_event-productcode = |{ <created>-[Product field] }|.
  gs_event-plannedquantity = |{ <created>-[Planned quantity field] }|.
  APPEND gs_event TO gt_events.
ENDLOOP.

* Material Availability Checked
SELECT [Production order field], [Production plant field], [Product field], [Availability check date field], [Availability check time field]
  FROM [Availability check source table]
  INTO TABLE @DATA(lt_availability)
  WHERE [Availability check date field] BETWEEN @p_from AND @p_to
    AND [Production order field] IN @s_aufnr
    AND [Production plant field] IN @s_werks.
LOOP AT lt_availability ASSIGNING FIELD-SYMBOL(<availability>).
  CLEAR gs_event.
  gs_event-productionorder = |{ <availability>-[Production order field] }|.
  gs_event-activityname = 'Material Availability Checked'.
  gs_event-eventtime = |{ <availability>-[Availability check date field] } { <availability>-[Availability check time field] }|.
  gs_event-sourcesystem = p_src.
  gs_event-lastdataupdate = gv_update_timestamp.
  gs_event-productionplant = |{ <availability>-[Production plant field] }|.
  gs_event-productcode = |{ <availability>-[Product field] }|.
  APPEND gs_event TO gt_events.
ENDLOOP.

* Production Order Released
SELECT [Production order field], [Status change date field], [Status change time field], [Production plant field], [Product field], [Planned quantity field]
  FROM [Order status history source table]
  INTO TABLE @DATA(lt_released)
  WHERE [Status field] = '[Released status code]'
    AND [Status change date field] BETWEEN @p_from AND @p_to
    AND [Production order field] IN @s_aufnr.
LOOP AT lt_released ASSIGNING FIELD-SYMBOL(<released>).
  CLEAR gs_event.
  gs_event-productionorder = |{ <released>-[Production order field] }|.
  gs_event-activityname = 'Production Order Released'.
  gs_event-eventtime = |{ <released>-[Status change date field] } { <released>-[Status change time field] }|.
  gs_event-sourcesystem = p_src.
  gs_event-lastdataupdate = gv_update_timestamp.
  gs_event-productionplant = |{ <released>-[Production plant field] }|.
  gs_event-productcode = |{ <released>-[Product field] }|.
  gs_event-plannedquantity = |{ <released>-[Planned quantity field] }|.
  APPEND gs_event TO gt_events.
ENDLOOP.

* Order Data Changed
SELECT [Production order field], [Change date field], [Change time field], [Production plant field], [Product field], [Changed field name]
  FROM [Production order change document source table]
  INTO TABLE @DATA(lt_changed)
  WHERE [Change date field] BETWEEN @p_from AND @p_to
    AND [Production order field] IN @s_aufnr
    AND [Changed field name] IN ('[Planned quantity field]', '[Basic start date field]', '[Basic finish date field]', '[BOM field]', '[Routing field]').
LOOP AT lt_changed ASSIGNING FIELD-SYMBOL(<changed>).
  CLEAR gs_event.
  gs_event-productionorder = |{ <changed>-[Production order field] }|.
  gs_event-activityname = 'Order Data Changed'.
  gs_event-eventtime = |{ <changed>-[Change date field] } { <changed>-[Change time field] }|.
  gs_event-sourcesystem = p_src.
  gs_event-lastdataupdate = gv_update_timestamp.
  gs_event-productionplant = |{ <changed>-[Production plant field] }|.
  gs_event-productcode = |{ <changed>-[Product field] }|.
  APPEND gs_event TO gt_events.
ENDLOOP.

* Goods Issue Posted
SELECT [Production order field], [Posting date field], [Posting time field], [Production plant field], [Product field], [Quantity field]
  FROM [Material document item source table]
  INTO TABLE @DATA(lt_gi)
  WHERE [Movement type field] IN ('[Goods issue movement type 1]', '[Goods issue movement type 2]')
    AND [Posting date field] BETWEEN @p_from AND @p_to
    AND [Production order field] IN @s_aufnr
    AND [Production plant field] IN @s_werks.
LOOP AT lt_gi ASSIGNING FIELD-SYMBOL(<gi>).
  CLEAR gs_event.
  gs_event-productionorder = |{ <gi>-[Production order field] }|.
  gs_event-activityname = 'Goods Issue Posted'.
  gs_event-eventtime = |{ <gi>-[Posting date field] } { <gi>-[Posting time field] }|.
  gs_event-sourcesystem = p_src.
  gs_event-lastdataupdate = gv_update_timestamp.
  gs_event-productionplant = |{ <gi>-[Production plant field] }|.
  gs_event-productcode = |{ <gi>-[Product field] }|.
  gs_event-deliveredquantity = |{ <gi>-[Quantity field] }|.
  APPEND gs_event TO gt_events.
ENDLOOP.

* First Confirmation Recorded
SELECT [Production order field], [Confirmation date field], [Confirmation time field], [Production plant field], [Product field], [Confirmed quantity field]
  FROM [Production confirmation source table]
  INTO TABLE @DATA(lt_confirmations)
  WHERE [Confirmation date field] BETWEEN @p_from AND @p_to
    AND [Production order field] IN @s_aufnr
    AND [Production plant field] IN @s_werks.
SORT lt_confirmations BY [Production order field] [Confirmation date field] [Confirmation time field].
LOOP AT lt_confirmations ASSIGNING FIELD-SYMBOL(<confirmation>) GROUP BY <confirmation>-[Production order field] ASSIGNING FIELD-SYMBOL(<confirmation_group>).
  LOOP AT GROUP <confirmation_group> ASSIGNING FIELD-SYMBOL(<first_confirmation>).
    CLEAR gs_event.
    gs_event-productionorder = |{ <first_confirmation>-[Production order field] }|.
    gs_event-activityname = 'First Confirmation Recorded'.
    gs_event-eventtime = |{ <first_confirmation>-[Confirmation date field] } { <first_confirmation>-[Confirmation time field] }|.
    gs_event-sourcesystem = p_src.
    gs_event-lastdataupdate = gv_update_timestamp.
    gs_event-productionplant = |{ <first_confirmation>-[Production plant field] }|.
    gs_event-productcode = |{ <first_confirmation>-[Product field] }|.
    gs_event-actualstartdate = gs_event-eventtime.
    APPEND gs_event TO gt_events.
    EXIT.
  ENDLOOP.
ENDLOOP.

* Intermediate Confirmation Recorded
LOOP AT lt_confirmations ASSIGNING FIELD-SYMBOL(<intermediate>) GROUP BY <intermediate>-[Production order field] ASSIGNING FIELD-SYMBOL(<intermediate_group>).
  DATA(lv_intermediate_index) = 0.
  DATA(lv_intermediate_count) = GROUP SIZE.
  LOOP AT GROUP <intermediate_group> ASSIGNING FIELD-SYMBOL(<intermediate_row>).
    lv_intermediate_index = lv_intermediate_index + 1.
    IF lv_intermediate_index < lv_intermediate_count AND <intermediate_row>-[Final confirmation indicator field] <> '[Final confirmation value]'.
      CLEAR gs_event.
      gs_event-productionorder = |{ <intermediate_row>-[Production order field] }|.
      gs_event-activityname = 'Intermediate Confirmation Recorded'.
      gs_event-eventtime = |{ <intermediate_row>-[Confirmation date field] } { <intermediate_row>-[Confirmation time field] }|.
      gs_event-sourcesystem = p_src.
      gs_event-lastdataupdate = gv_update_timestamp.
      gs_event-productionplant = |{ <intermediate_row>-[Production plant field] }|.
      gs_event-productcode = |{ <intermediate_row>-[Product field] }|.
      APPEND gs_event TO gt_events.
    ENDIF.
  ENDLOOP.
ENDLOOP.

* Final Confirmation Recorded
LOOP AT lt_confirmations ASSIGNING FIELD-SYMBOL(<final>) WHERE [Final confirmation indicator field] = '[Final confirmation value]'.
  CLEAR gs_event.
  gs_event-productionorder = |{ <final>-[Production order field] }|.
  gs_event-activityname = 'Final Confirmation Recorded'.
  gs_event-eventtime = |{ <final>-[Confirmation date field] } { <final>-[Confirmation time field] }|.
  gs_event-sourcesystem = p_src.
  gs_event-lastdataupdate = gv_update_timestamp.
  gs_event-productionplant = |{ <final>-[Production plant field] }|.
  gs_event-productcode = |{ <final>-[Product field] }|.
  gs_event-actualenddate = gs_event-eventtime.
  APPEND gs_event TO gt_events.
ENDLOOP.

* Goods Receipt Posted
SELECT [Production order field], [Posting date field], [Posting time field], [Production plant field], [Product field], [Quantity field]
  FROM [Material document item source table]
  INTO TABLE @DATA(lt_gr)
  WHERE [Movement type field] IN ('[Goods receipt movement type 1]', '[Goods receipt movement type 2]')
    AND [Posting date field] BETWEEN @p_from AND @p_to
    AND [Production order field] IN @s_aufnr
    AND [Production plant field] IN @s_werks.
LOOP AT lt_gr ASSIGNING FIELD-SYMBOL(<gr>).
  CLEAR gs_event.
  gs_event-productionorder = |{ <gr>-[Production order field] }|.
  gs_event-activityname = 'Goods Receipt Posted'.
  gs_event-eventtime = |{ <gr>-[Posting date field] } { <gr>-[Posting time field] }|.
  gs_event-sourcesystem = p_src.
  gs_event-lastdataupdate = gv_update_timestamp.
  gs_event-productionplant = |{ <gr>-[Production plant field] }|.
  gs_event-productcode = |{ <gr>-[Product field] }|.
  gs_event-deliveredquantity = |{ <gr>-[Quantity field] }|.
  APPEND gs_event TO gt_events.
ENDLOOP.

* Order Technically Completed
SELECT [Production order field], [Status change date field], [Status change time field], [Production plant field], [Product field]
  FROM [Order status history source table]
  INTO TABLE @DATA(lt_teco)
  WHERE [Status field] = '[Technically completed status code]'
    AND [Status change date field] BETWEEN @p_from AND @p_to
    AND [Production order field] IN @s_aufnr.
LOOP AT lt_teco ASSIGNING FIELD-SYMBOL(<teco>).
  CLEAR gs_event.
  gs_event-productionorder = |{ <teco>-[Production order field] }|.
  gs_event-activityname = 'Order Technically Completed'.
  gs_event-eventtime = |{ <teco>-[Status change date field] } { <teco>-[Status change time field] }|.
  gs_event-sourcesystem = p_src.
  gs_event-lastdataupdate = gv_update_timestamp.
  gs_event-productionplant = |{ <teco>-[Production plant field] }|.
  gs_event-productcode = |{ <teco>-[Product field] }|.
  APPEND gs_event TO gt_events.
ENDLOOP.

* Order Settlement Ran
SELECT [Production order field], [Settlement date field], [Settlement time field], [Production plant field], [Product field]
  FROM [Order settlement source table]
  INTO TABLE @DATA(lt_settlement)
  WHERE [Settlement date field] BETWEEN @p_from AND @p_to
    AND [Production order field] IN @s_aufnr.
LOOP AT lt_settlement ASSIGNING FIELD-SYMBOL(<settlement>).
  CLEAR gs_event.
  gs_event-productionorder = |{ <settlement>-[Production order field] }|.
  gs_event-activityname = 'Order Settlement Ran'.
  gs_event-eventtime = |{ <settlement>-[Settlement date field] } { <settlement>-[Settlement time field] }|.
  gs_event-sourcesystem = p_src.
  gs_event-lastdataupdate = gv_update_timestamp.
  gs_event-productionplant = |{ <settlement>-[Production plant field] }|.
  gs_event-productcode = |{ <settlement>-[Product field] }|.
  APPEND gs_event TO gt_events.
ENDLOOP.

* Order Cancellation Set
SELECT [Production order field], [Status change date field], [Status change time field], [Production plant field], [Product field]
  FROM [Order status history source table]
  INTO TABLE @DATA(lt_cancelled)
  WHERE [Status field] IN ('[Cancellation status code]', '[Deletion status code]')
    AND [Status change date field] BETWEEN @p_from AND @p_to
    AND [Production order field] IN @s_aufnr.
LOOP AT lt_cancelled ASSIGNING FIELD-SYMBOL(<cancelled>).
  CLEAR gs_event.
  gs_event-productionorder = |{ <cancelled>-[Production order field] }|.
  gs_event-activityname = 'Order Cancellation Set'.
  gs_event-eventtime = |{ <cancelled>-[Status change date field] } { <cancelled>-[Status change time field] }|.
  gs_event-sourcesystem = p_src.
  gs_event-lastdataupdate = gv_update_timestamp.
  gs_event-productionplant = |{ <cancelled>-[Production plant field] }|.
  gs_event-productcode = |{ <cancelled>-[Product field] }|.
  APPEND gs_event TO gt_events.
ENDLOOP.

SORT gt_events BY productionorder eventtime activityname.
DELETE ADJACENT DUPLICATES FROM gt_events COMPARING productionorder activityname eventtime productionplant productcode.

OPEN DATASET p_file FOR OUTPUT IN TEXT MODE ENCODING UTF-8.
IF sy-subrc <> 0.
  MESSAGE 'The application server output file could not be opened' TYPE 'E'.
ENDIF.
TRANSFER 'ProductionOrder,ActivityName,EventTime,SourceSystem,LastDataUpdate,ProductionPlant,ProductCode,PlannedQuantity,DeliveredQuantity,ActualStartDate,ActualEndDate' TO p_file.
LOOP AT gt_events INTO gs_event.
  gv_line = |{ gs_event-productionorder },{ gs_event-activityname },{ gs_event-eventtime },{ gs_event-sourcesystem },{ gs_event-lastdataupdate },{ gs_event-productionplant },{ gs_event-productcode },{ gs_event-plannedquantity },{ gs_event-deliveredquantity },{ gs_event-actualstartdate },{ gs_event-actualenddate }|.
  TRANSFER gv_line TO p_file.
ENDLOOP.
CLOSE DATASET p_file.
MESSAGE |Event log written to { p_file }| TYPE 'S'.