Your Production Planning Data Template

WITH BaseOrders AS (
    SELECT 
        ProductionOrder, 
        Material AS MaterialNumber, 
        ProductionPlant AS Plant, 
        CreationDate, 
        CreationTime, 
        BasicEndDate AS PlannedEndDate, 
        TechnicalCompletionDate,
        LastChangeDateTime
    FROM I_ProductionOrder
    -- P_CreationDateFrom and P_CreationDateTo are placeholders for your date range
    WHERE CreationDate BETWEEN '[_StartDate]' AND '[_EndDate]'
    -- Add other filters as needed, for example:
    -- AND ProductionPlant IN ('[Your_Plant_1]', '[Your_Plant_2]')
    -- AND ProductionOrderType = '[Your_Order_Type]'
)
SELECT 
    bo.ProductionOrder, 
    'Production Order Created' AS Activity, 
    TO_TIMESTAMP(bo.CreationDate || ' ' || LPAD(bo.CreationTime, 6, '0'), 'YYYYMMDD HH24MISS') AS StartTime,
    '[_SourceSystemName]' AS SourceSystem,
    CURRENT_UTCTIMESTAMP AS LastDataUpdate,
    bo.MaterialNumber,
    bo.Plant,
    NULL AS WorkCenter,
    bo.PlannedEndDate,
    'CRTD' AS OrderStatus
FROM BaseOrders bo

UNION ALL

-- System status changes like Released, Technically Completed, Closed, Cancelled, Material Shortage
SELECT 
    os.ObjectInternalID AS ProductionOrder,
    CASE os.SystemStatus
        WHEN 'I0002' THEN 'Production Order Released'
        WHEN 'I0043' THEN 'Material Availability Checked' -- Status: MACM (Material committed)
        WHEN 'I0045' THEN 'Order Technically Completed'
        WHEN 'I0046' THEN 'Order Financially Closed'
        WHEN 'I0047' THEN 'Production Order Cancelled'
        WHEN 'I0010' THEN 'Material Shortage Occurred' -- Status: MSPT (Material shortage)
    END AS Activity,
    os.StatusChangeDateTime AS StartTime,
    '[_SourceSystemName]' AS SourceSystem,
    CURRENT_UTCTIMESTAMP AS LastDataUpdate,
    bo.MaterialNumber,
    bo.Plant,
    NULL AS WorkCenter,
    bo.PlannedEndDate,
    os.SystemStatus AS OrderStatus
FROM I_ObjectStatus AS os
JOIN BaseOrders AS bo ON os.ObjectInternalID = bo.ProductionOrder
WHERE os.SystemStatus IN ('I0002', 'I0043', 'I0045', 'I0046', 'I0047', 'I0010') AND os.StatusIsInactive = ''

UNION ALL

-- Production Started, inferred from the first goods issue or first confirmation
SELECT
    s.ProductionOrder,
    'Production Started' AS Activity,
    s.StartEventTime AS StartTime,
    '[_SourceSystemName]' AS SourceSystem,
    CURRENT_UTCTIMESTAMP AS LastDataUpdate,
    s.MaterialNumber,
    s.Plant,
    NULL AS WorkCenter,
    s.PlannedEndDate,
    NULL AS OrderStatus
FROM (
    SELECT 
        bo.ProductionOrder, 
        bo.MaterialNumber,
        bo.Plant,
        bo.PlannedEndDate,
        MIN(COALESCE(gm.PostingTime, cf.ConfirmationDateTime)) AS StartEventTime,
        ROW_NUMBER() OVER(PARTITION BY bo.ProductionOrder ORDER BY MIN(COALESCE(gm.PostingTime, cf.ConfirmationDateTime))) as rn
    FROM BaseOrders bo
    LEFT JOIN I_MaterialDocumentItem gm ON bo.ProductionOrder = gm.ProductionOrder AND gm.GoodsMovementType IN ('261', '262') -- Goods Issue
    LEFT JOIN I_ProdOrdConfirmation cf ON bo.ProductionOrder = cf.ProductionOrder
    WHERE gm.ProductionOrder IS NOT NULL OR cf.ProductionOrder IS NOT NULL
    GROUP BY bo.ProductionOrder, bo.MaterialNumber, bo.Plant, bo.PlannedEndDate
) s
WHERE s.rn = 1

UNION ALL

-- Production Confirmation Entered (for each confirmation)
SELECT 
    cf.ProductionOrder,
    'Production Confirmation Entered' AS Activity,
    cf.ConfirmationDateTime AS StartTime,
    '[_SourceSystemName]' AS SourceSystem,
    CURRENT_UTCTIMESTAMP AS LastDataUpdate,
    bo.MaterialNumber,
    bo.Plant,
    cf.WorkCenter,
    bo.PlannedEndDate,
    NULL AS OrderStatus
FROM I_ProdOrdConfirmation cf
JOIN BaseOrders bo ON cf.ProductionOrder = bo.ProductionOrder

UNION ALL

-- Final Confirmation Entered
SELECT 
    cf.ProductionOrder,
    'Final Confirmation Entered' AS Activity,
    cf.ConfirmationDateTime AS StartTime,
    '[_SourceSystemName]' AS SourceSystem,
    CURRENT_UTCTIMESTAMP AS LastDataUpdate,
    bo.MaterialNumber,
    bo.Plant,
    cf.WorkCenter,
    bo.PlannedEndDate,
    NULL AS OrderStatus
FROM I_ProdOrdConfirmation cf
JOIN BaseOrders bo ON cf.ProductionOrder = bo.ProductionOrder
WHERE cf.IsFinalConfirmation = 'X'

UNION ALL

-- Unplanned Halt Occurred (derived from gaps between confirmations)
SELECT 
    ProductionOrder,
    'Unplanned Halt Occurred' AS Activity,
    PreviousConfirmationTime AS StartTime,
    '[_SourceSystemName]' AS SourceSystem,
    CURRENT_UTCTIMESTAMP AS LastDataUpdate,
    MaterialNumber,
    Plant,
    WorkCenter,
    PlannedEndDate,
    NULL AS OrderStatus
FROM (
    SELECT
        cf.ProductionOrder,
        bo.MaterialNumber,
        bo.Plant,
        bo.PlannedEndDate,
        cf.WorkCenter,
        cf.ConfirmationDateTime,
        LAG(cf.ConfirmationDateTime, 1) OVER (PARTITION BY cf.ProductionOrder ORDER BY cf.ConfirmationDateTime) AS PreviousConfirmationTime,
        (SECONDS_BETWEEN(LAG(cf.ConfirmationDateTime, 1) OVER (PARTITION BY cf.ProductionOrder ORDER BY cf.ConfirmationDateTime), cf.ConfirmationDateTime) / 3600.0) as HoursDiff
    FROM I_ProdOrdConfirmation cf
    JOIN BaseOrders bo ON cf.ProductionOrder = bo.ProductionOrder
) AS gaps
WHERE gaps.HoursDiff > 24 -- Configurable threshold in hours, e.g. 24 hours

UNION ALL

-- Goods Receipt Posted
SELECT
    gm.ProductionOrder,
    'Goods Receipt Posted' AS Activity,
    gm.PostingTime AS StartTime,
    '[_SourceSystemName]' AS SourceSystem,
    CURRENT_UTCTIMESTAMP AS LastDataUpdate,
    bo.MaterialNumber,
    bo.Plant,
    NULL AS WorkCenter,
    bo.PlannedEndDate,
    NULL AS OrderStatus
FROM I_MaterialDocumentItem gm
JOIN BaseOrders bo ON gm.ProductionOrder = bo.ProductionOrder
WHERE gm.GoodsMovementType = '101'

UNION ALL

-- Capacity Requirements Planned (using Release event as a proxy)
SELECT 
    os.ObjectInternalID AS ProductionOrder,
    'Capacity Requirements Planned' AS Activity,
    os.StatusChangeDateTime AS StartTime,
    '[_SourceSystemName]' AS SourceSystem,
    CURRENT_UTCTIMESTAMP AS LastDataUpdate,
    bo.MaterialNumber,
    bo.Plant,
    NULL AS WorkCenter,
    bo.PlannedEndDate,
    os.SystemStatus AS OrderStatus
FROM I_ObjectStatus AS os
JOIN BaseOrders AS bo ON os.ObjectInternalID = bo.ProductionOrder
WHERE os.SystemStatus = 'I0002' AND os.StatusIsInactive = '' -- Status 'REL' (Released)

UNION ALL

-- Production Plan Adjusted
SELECT
    ch.ObjectValue AS ProductionOrder,
    'Production Plan Adjusted' AS Activity,
    ch.CreationDateTime AS StartTime,
    '[_SourceSystemName]' AS SourceSystem,
    CURRENT_UTCTIMESTAMP AS LastDataUpdate,
    bo.MaterialNumber,
    bo.Plant,
    NULL as WorkCenter,
    bo.PlannedEndDate,
    NULL as OrderStatus
FROM I_ChangeDocument AS ch
JOIN BaseOrders bo ON ch.ObjectValue = bo.ProductionOrder
WHERE ch.ChangeDocumentObject = 'PRODORDER'
-- Monitoring changes in key fields like Total Quantity (GAMNG) or Basic Finish Date (GLTRP)
AND ch.FieldName IN ('GAMNG', 'GLTRP')
GROUP BY ch.ObjectValue, ch.CreationDateTime, bo.MaterialNumber, bo.Plant, bo.PlannedEndDate

WITH
params AS (
    SELECT
        '[Your client]' AS mandt,
        TO_DATE('[Extraction start date]', 'YYYY-MM-DD') AS date_from,
        TO_DATE('[Extraction end date]', 'YYYY-MM-DD') AS date_to,
        '[Your source system name]' AS source_system,
        CURRENT_UTCTIMESTAMP AS last_data_update
    FROM DUMMY
),
orders AS (
    SELECT
        a.mandt,
        LTRIM(a.aufnr, '0') AS production_order,
        a.aufnr AS production_order_raw,
        a.erdat AS order_created_date,
        a.erfzeit AS order_created_time,
        a.auart AS order_type,
        a.werks AS plant,
        a.objnr AS object_number,
        a.ktext AS order_text,
        p.matnr AS material_number,
        p.psmng AS planned_quantity,
        p.wemng AS delivered_quantity,
        k.gstrp AS planned_start_date,
        k.gltrp AS planned_end_date,
        k.ftrmi AS release_date,
        k.ftrmp AS release_time,
        k.gamng AS total_order_quantity,
        k.plnbez AS header_material_number,
        k.plnty AS task_list_type,
        k.plnnr AS task_list_group,
        k.plnal AS task_list_group_counter
    FROM [Your schema name].AUFK a
    LEFT JOIN [Your schema name].AFPO p
        ON p.mandt = a.mandt
       AND p.aufnr = a.aufnr
    LEFT JOIN [Your schema name].AFKO k
        ON k.mandt = a.mandt
       AND k.aufnr = a.aufnr
    CROSS JOIN params x
    WHERE a.mandt = x.mandt
      AND a.erdat >= x.date_from
      AND a.erdat < ADD_DAYS(x.date_to, 1)
),
status_history AS (
    SELECT
        o.*,
        j.stat,
        j.inact,
        j.chgnr,
        j.udate AS status_date,
        j.utime AS status_time,
        c.usnam AS status_user,
        c.chind AS status_change_indicator
    FROM orders o
    INNER JOIN [Your schema name].JEST j
        ON j.mandt = o.mandt
       AND j.objnr = o.object_number
    LEFT JOIN [Your schema name].JCDS c
        ON c.mandt = j.mandt
       AND c.objnr = j.objnr
       AND c.stat = j.stat
       AND c.chgnr = j.chgnr
    WHERE COALESCE(j.inact, '') <> 'X'
),
operations AS (
    SELECT
        o.production_order,
        o.mandt,
        v.vornr,
        v.arbid,
        v.steus,
        v.ltxa1,
        v.aufpl,
        v.aplzl,
        cr.name1 AS work_center
    FROM orders o
    LEFT JOIN [Your schema name].AFVC v
        ON v.mandt = o.mandt
       AND v.aufpl = (SELECT MAX(k2.aufpl) FROM [Your schema name].AFKO k2 WHERE k2.mandt = o.mandt AND k2.aufnr = o.production_order_raw)
    LEFT JOIN [Your schema name].CRHD cr
        ON cr.mandt = v.mandt
       AND cr.objty = 'A'
       AND cr.objid = v.arbid
),
confirmations AS (
    SELECT
        o.production_order,
        o.mandt,
        r.rmzhl,
        r.vornr,
        r.budat AS confirmation_date,
        r.ersda AS entry_date,
        r.erzet AS entry_time,
        r.ism01,
        r.ism02,
        r.ism03,
        r.ism04,
        r.ism05,
        r.ism06,
        r.xmnga,
        r.stzhl,
        r.stzhl AS reversal_counter,
        r.lmnga AS confirmed_quantity,
        r.xmnge AS scrap_quantity,
        r.aufpl,
        r.aplzl,
        op.work_center
    FROM orders o
    INNER JOIN [Your schema name].AFRU r
        ON r.mandt = o.mandt
       AND r.aufnr = o.production_order_raw
    LEFT JOIN operations op
        ON op.production_order = o.production_order
       AND op.mandt = r.mandt
       AND op.vornr = r.vornr
),
first_confirmation AS (
    SELECT production_order, MIN(TO_TIMESTAMP(TO_VARCHAR(entry_date, 'YYYYMMDD') || LPAD(TO_VARCHAR(entry_time), 6, '0'), 'YYYYMMDDHH24MISS')) AS start_time
    FROM confirmations
    GROUP BY production_order
),
last_confirmation AS (
    SELECT production_order, MAX(TO_TIMESTAMP(TO_VARCHAR(entry_date, 'YYYYMMDD') || LPAD(TO_VARCHAR(entry_time), 6, '0'), 'YYYYMMDDHH24MISS')) AS final_time
    FROM confirmations
    GROUP BY production_order
),
first_goods_issue AS (
    SELECT
        o.production_order,
        MIN(TO_TIMESTAMP(TO_VARCHAR(m.budat_mkpf, 'YYYYMMDD') || LPAD(TO_VARCHAR(m.cputm_mkpf), 6, '0'), 'YYYYMMDDHH24MISS')) AS start_time
    FROM orders o
    INNER JOIN [Your schema name].MSEG m
        ON m.mandt = o.mandt
       AND m.aufnr = o.production_order_raw
       AND m.bwart IN ('261', '262')
    GROUP BY o.production_order
),
first_receipt AS (
    SELECT
        o.production_order,
        MIN(TO_TIMESTAMP(TO_VARCHAR(m.budat_mkpf, 'YYYYMMDD') || LPAD(TO_VARCHAR(m.cputm_mkpf), 6, '0'), 'YYYYMMDDHH24MISS')) AS receipt_time
    FROM orders o
    INNER JOIN [Your schema name].MSEG m
        ON m.mandt = o.mandt
       AND m.aufnr = o.production_order_raw
       AND m.bwart IN ('101', '102')
    GROUP BY o.production_order
),
plan_changes AS (
    SELECT
        o.production_order,
        MIN(TO_TIMESTAMP(TO_VARCHAR(h.udate, 'YYYYMMDD') || LPAD(TO_VARCHAR(h.utime), 6, '0'), 'YYYYMMDDHH24MISS')) AS adjustment_time
    FROM orders o
    INNER JOIN [Your schema name].CDHDR h
        ON h.mandant = o.mandt
       AND h.objectid = o.production_order_raw
    INNER JOIN [Your schema name].CDPOS p
        ON p.mandant = h.mandant
       AND p.objectclas = h.objectclas
       AND p.objectid = h.objectid
       AND p.changenr = h.changenr
    WHERE h.objectclas IN ('ORDER', 'PP_ORDER')
      AND p.tabname IN ('AFKO', 'AFPO', 'AFVC', 'RESB')
      AND p.fname IN ('GAMNG', 'PSMNG', 'GSTRP', 'GLTRP', 'MATNR', 'BDMNG', 'VORNR')
    GROUP BY o.production_order
),
status_times AS (
    SELECT
        production_order,
        MIN(CASE WHEN stat IN ('I0001', 'CRTD') THEN TO_TIMESTAMP(TO_VARCHAR(status_date, 'YYYYMMDD') || LPAD(TO_VARCHAR(status_time), 6, '0'), 'YYYYMMDDHH24MISS') END) AS created_status_time,
        MIN(CASE WHEN stat IN ('I0002', 'REL') THEN TO_TIMESTAMP(TO_VARCHAR(status_date, 'YYYYMMDD') || LPAD(TO_VARCHAR(status_time), 6, '0'), 'YYYYMMDDHH24MISS') END) AS released_time,
        MIN(CASE WHEN stat IN ('I0003', 'MATC', 'MANC') THEN TO_TIMESTAMP(TO_VARCHAR(status_date, 'YYYYMMDD') || LPAD(TO_VARCHAR(status_time), 6, '0'), 'YYYYMMDDHH24MISS') END) AS material_available_time,
        MIN(CASE WHEN stat IN ('I0004', 'CAPC', 'CAPA') THEN TO_TIMESTAMP(TO_VARCHAR(status_date, 'YYYYMMDD') || LPAD(TO_VARCHAR(status_time), 6, '0'), 'YYYYMMDDHH24MISS') END) AS capacity_planned_time,
        MIN(CASE WHEN stat IN ('I0045', 'TECO') THEN TO_TIMESTAMP(TO_VARCHAR(status_date, 'YYYYMMDD') || LPAD(TO_VARCHAR(status_time), 6, '0'), 'YYYYMMDDHH24MISS') END) AS technically_completed_time,
        MIN(CASE WHEN stat IN ('I0046', 'CLSD') THEN TO_TIMESTAMP(TO_VARCHAR(status_date, 'YYYYMMDD') || LPAD(TO_VARCHAR(status_time), 6, '0'), 'YYYYMMDDHH24MISS') END) AS financially_closed_time,
        MIN(CASE WHEN stat IN ('I0076', 'DLFL', 'CANC') THEN TO_TIMESTAMP(TO_VARCHAR(status_date, 'YYYYMMDD') || LPAD(TO_VARCHAR(status_time), 6, '0'), 'YYYYMMDDHH24MISS') END) AS cancelled_time
    FROM status_history
    GROUP BY production_order
),
material_shortages AS (
    SELECT
        o.production_order,
        MIN(TO_TIMESTAMP(TO_VARCHAR(j.status_date, 'YYYYMMDD') || LPAD(TO_VARCHAR(j.status_time), 6, '0'), 'YYYYMMDDHH24MISS')) AS shortage_time
    FROM status_history j
    INNER JOIN orders o
        ON o.production_order = j.production_order
    WHERE j.stat IN ('I0042', 'MANC', 'MSHT')
    GROUP BY o.production_order
),
capacity_times AS (
    SELECT
        production_order,
        MIN(COALESCE(released_time, created_status_time)) AS capacity_time
    FROM status_times
    GROUP BY production_order
),
base_attributes AS (
    SELECT
        o.production_order,
        o.material_number,
        o.plant,
        COALESCE(op.work_center, '') AS work_center,
        o.planned_end_date,
        COALESCE(st.stat, '') AS current_status
    FROM orders o
    LEFT JOIN status_history st
        ON st.production_order = o.production_order
       AND st.inact = ''
    LEFT JOIN operations op
        ON op.production_order = o.production_order
       AND op.vornr = (SELECT MIN(op2.vornr) FROM operations op2 WHERE op2.production_order = o.production_order)
),
events AS (
    SELECT o.production_order, 'Production Order Created' AS activity, COALESCE(st.created_status_time, TO_TIMESTAMP(TO_VARCHAR(o.order_created_date, 'YYYYMMDD') || LPAD(TO_VARCHAR(o.order_created_time), 6, '0'), 'YYYYMMDDHH24MISS')) AS start_time FROM orders o LEFT JOIN status_times st ON st.production_order = o.production_order
    UNION ALL
    SELECT production_order, 'Material Availability Checked', material_available_time FROM status_times WHERE material_available_time IS NOT NULL
    UNION ALL
    SELECT production_order, 'Capacity Requirements Planned', capacity_time FROM capacity_times WHERE capacity_time IS NOT NULL
    UNION ALL
    SELECT production_order, 'Production Order Released', released_time FROM status_times WHERE released_time IS NOT NULL
    UNION ALL
    SELECT o.production_order, 'Production Started', LEAST(COALESCE(fc.start_time, TO_TIMESTAMP('99991231235959', 'YYYYMMDDHH24MISS')), COALESCE(fg.start_time, TO_TIMESTAMP('99991231235959', 'YYYYMMDDHH24MISS'))) FROM orders o LEFT JOIN first_confirmation fc ON fc.production_order = o.production_order LEFT JOIN first_goods_issue fg ON fg.production_order = o.production_order WHERE fc.start_time IS NOT NULL OR fg.start_time IS NOT NULL
    UNION ALL
    SELECT production_order, 'Production Confirmation Entered', TO_TIMESTAMP(TO_VARCHAR(entry_date, 'YYYYMMDD') || LPAD(TO_VARCHAR(entry_time), 6, '0'), 'YYYYMMDDHH24MISS') FROM confirmations
    UNION ALL
    SELECT production_order, 'Material Shortage Occurred', shortage_time FROM material_shortages WHERE shortage_time IS NOT NULL
    UNION ALL
    SELECT production_order, 'Production Plan Adjusted', adjustment_time FROM plan_changes WHERE adjustment_time IS NOT NULL
    UNION ALL
    SELECT c.production_order, 'Unplanned Halt Occurred', ADD_SECONDS(c.confirmation_time, 1) FROM (SELECT production_order, TO_TIMESTAMP(TO_VARCHAR(entry_date, 'YYYYMMDD') || LPAD(TO_VARCHAR(entry_time), 6, '0'), 'YYYYMMDDHH24MISS') AS confirmation_time, LEAD(TO_TIMESTAMP(TO_VARCHAR(entry_date, 'YYYYMMDD') || LPAD(TO_VARCHAR(entry_time), 6, '0'), 'YYYYMMDDHH24MISS')) OVER (PARTITION BY production_order ORDER BY entry_date, entry_time, rmzhl) AS next_confirmation_time FROM confirmations) c WHERE c.next_confirmation_time IS NOT NULL AND SECONDS_BETWEEN(c.confirmation_time, c.next_confirmation_time) >= 86400
    UNION ALL
    SELECT production_order, 'Final Confirmation Entered', final_time FROM last_confirmation WHERE final_time IS NOT NULL
    UNION ALL
    SELECT production_order, 'Goods Receipt Posted', receipt_time FROM first_receipt WHERE receipt_time IS NOT NULL
    UNION ALL
    SELECT production_order, 'Order Technically Completed', technically_completed_time FROM status_times WHERE technically_completed_time IS NOT NULL
    UNION ALL
    SELECT production_order, 'Order Financially Closed', financially_closed_time FROM status_times WHERE financially_closed_time IS NOT NULL
    UNION ALL
    SELECT production_order, 'Production Order Cancelled', cancelled_time FROM status_times WHERE cancelled_time IS NOT NULL
)
SELECT
    e.production_order AS "ProductionOrder",
    e.activity AS "Activity",
    e.start_time AS "StartTime",
    p.source_system AS "SourceSystem",
    p.last_data_update AS "LastDataUpdate",
    b.material_number AS "MaterialNumber",
    b.plant AS "Plant",
    NULLIF(b.work_center, '') AS "WorkCenter",
    b.planned_end_date AS "PlannedEndDate",
    b.current_status AS "OrderStatus"
FROM events e
INNER JOIN base_attributes b
    ON b.production_order = e.production_order
CROSS JOIN params p
WHERE e.start_time IS NOT NULL
ORDER BY e.production_order, e.start_time, e.activity;