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

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