Datatemplate voor supplychainmanagement

WITH SalesOrderLink AS (
    SELECT DISTINCT
        sd.SalesDocument AS SalesOrder,
        pr.PurchaseRequisition AS PurchaseRequisition,
        po.PurchaseOrder AS PurchaseOrder,
        od.DeliveryDocument AS OutboundDelivery,
        bd.BillingDocument AS BillingDocument
    FROM I_SalesDocItemProcessFlow AS pf
    LEFT JOIN I_SalesDocumentItem AS sd ON pf.PrecedingDocument = sd.SalesDocument AND pf.PrecedingDocumentItem = sd.SalesDocumentItem
    LEFT JOIN I_PurchaseRequisitionItem AS pr ON pf.SubsequentDocument = pr.PurchaseRequisition AND pf.SubsequentDocumentItem = pr.PurchaseRequisitionItem
    LEFT JOIN I_PurchaseOrderItem AS po ON pf.SubsequentDocument = po.PurchaseOrder AND pf.SubsequentDocumentItem = po.PurchaseOrderItem
    LEFT JOIN I_OutboundDeliveryItem AS od ON pf.SubsequentDocument = od.DeliveryDocument AND pf.SubsequentDocumentItem = od.DeliveryDocumentItem
    LEFT JOIN I_BillingDocumentItem AS bd ON pf.SubsequentDocument = bd.BillingDocument AND pf.SubsequentDocumentItem = bd.BillingDocumentItem
    WHERE sd.SalesDocument IS NOT NULL
)
SELECT
    so.SalesOrder AS "LogisticsOrder",
    'Sales Order Created' AS "ActivityName",
    so.CreationDate || ' ' || so.CreationTime AS "EventTime",
    so.CreatedByUser AS "ExecutingUser",
    cust.CustomerName AS "CustomerName",
    NULL AS "SupplierName",
    soi.Material AS "MaterialNumber",
    soi.Plant AS "Plant",
    soi.RequestedDeliveryDate AS "RequestedDeliveryDate",
    NULL AS "QualityInspectionResult",
    'SAP S/4HANA' AS "SourceSystem",
    CURRENT_UTCTIMESTAMP AS "LastDataUpdate"
FROM I_SalesOrder AS so
JOIN I_SalesOrderItem AS soi ON so.SalesOrder = soi.SalesOrder
LEFT JOIN I_Customer AS cust ON so.SoldToParty = cust.Customer
WHERE so.CreationDate BETWEEN 'YYYY-MM-DD' AND 'YYYY-MM-DD'

UNION ALL

SELECT
    so.SalesOrder AS "LogisticsOrder",
    'Inventory Availability Checked' AS "ActivityName",
    so.CreationDate || ' ' || so.CreationTime AS "EventTime",
    so.CreatedByUser AS "ExecutingUser",
    cust.CustomerName AS "CustomerName",
    NULL AS "SupplierName",
    soi.Material AS "MaterialNumber",
    soi.Plant AS "Plant",
    soi.RequestedDeliveryDate AS "RequestedDeliveryDate",
    NULL AS "QualityInspectionResult",
    'SAP S/4HANA' AS "SourceSystem",
    CURRENT_UTCTIMESTAMP AS "LastDataUpdate"
FROM I_SalesOrder AS so
JOIN I_SalesOrderItem AS soi ON so.SalesOrder = soi.SalesOrder
LEFT JOIN I_Customer AS cust ON so.SoldToParty = cust.Customer
WHERE so.CreationDate BETWEEN 'YYYY-MM-DD' AND 'YYYY-MM-DD'

UNION ALL

SELECT
    sl.SalesOrder AS "LogisticsOrder",
    'Purchase Requisition Created' AS "ActivityName",
    pr.CreationDate || ' ' || '00:00:00' AS "EventTime",
    pr.CreatedByUser AS "ExecutingUser",
    NULL AS "CustomerName",
    NULL AS "SupplierName",
    pri.Material AS "MaterialNumber",
    pri.Plant AS "Plant",
    pri.DeliveryDate AS "RequestedDeliveryDate",
    NULL AS "QualityInspectionResult",
    'SAP S/4HANA' AS "SourceSystem",
    CURRENT_UTCTIMESTAMP AS "LastDataUpdate"
FROM I_PurchaseRequisition AS pr
JOIN I_PurchaseRequisitionItem AS pri ON pr.PurchaseRequisition = pri.PurchaseRequisition
JOIN SalesOrderLink sl ON pr.PurchaseRequisition = sl.PurchaseRequisition
WHERE pr.CreationDate BETWEEN 'YYYY-MM-DD' AND 'YYYY-MM-DD' AND pr.CompanyCode = 'YourCompanyCode'

UNION ALL

SELECT
    sl.SalesOrder AS "LogisticsOrder",
    'Purchase Order Issued' AS "ActivityName",
    po.PurchaseOrderDate || ' ' || '00:00:00' AS "EventTime",
    po.CreatedByUser AS "ExecutingUser",
    NULL AS "CustomerName",
    sup.SupplierName AS "SupplierName",
    poi.Material AS "MaterialNumber",
    poi.Plant AS "Plant",
    poi.DeliveryDate AS "RequestedDeliveryDate",
    NULL AS "QualityInspectionResult",
    'SAP S/4HANA' AS "SourceSystem",
    CURRENT_UTCTIMESTAMP AS "LastDataUpdate"
FROM I_PurchaseOrder AS po
JOIN I_PurchaseOrderItem AS poi ON po.PurchaseOrder = poi.PurchaseOrder
LEFT JOIN I_Supplier AS sup ON po.Supplier = sup.Supplier
JOIN SalesOrderLink sl ON po.PurchaseOrder = sl.PurchaseOrder
WHERE po.PurchaseOrderDate BETWEEN 'YYYY-MM-DD' AND 'YYYY-MM-DD' AND po.CompanyCode = 'YourCompanyCode'

UNION ALL

SELECT
    sl.SalesOrder AS "LogisticsOrder",
    'Goods Receipt For PO Posted' AS "ActivityName",
    md.DocumentDate || ' ' || md.CreationTime AS "EventTime",
    md.CreatedByUser AS "ExecutingUser",
    NULL AS "CustomerName",
    sup.SupplierName AS "SupplierName",
    mdi.Material AS "MaterialNumber",
    mdi.Plant AS "Plant",
    poi.DeliveryDate AS "RequestedDeliveryDate",
    NULL AS "QualityInspectionResult",
    'SAP S/4HANA' AS "SourceSystem",
    CURRENT_UTCTIMESTAMP AS "LastDataUpdate"
FROM I_MaterialDocumentHeader AS md
JOIN I_MaterialDocumentItem AS mdi ON md.MaterialDocument = mdi.MaterialDocument AND md.MaterialDocumentYear = mdi.MaterialDocumentYear
JOIN I_PurchaseOrderItem AS poi ON mdi.PurchaseOrder = poi.PurchaseOrder AND mdi.PurchaseOrderItem = poi.PurchaseOrderItem
LEFT JOIN I_Supplier AS sup ON poi.Supplier = sup.Supplier
JOIN SalesOrderLink sl ON poi.PurchaseOrder = sl.PurchaseOrder
WHERE md.DocumentDate BETWEEN 'YYYY-MM-DD' AND 'YYYY-MM-DD' AND mdi.GoodsMovementType = '101' AND mdi.CompanyCode = 'YourCompanyCode'

UNION ALL

SELECT
    sl.SalesOrder AS "LogisticsOrder",
    'Production Order Created' AS "ActivityName",
    po.CreationDate || ' ' || po.CreationTime AS "EventTime",
    po.CreatedByUser AS "ExecutingUser",
    NULL AS "CustomerName",
    NULL AS "SupplierName",
    po.Material AS "MaterialNumber",
    po.ProductionPlant AS "Plant",
    NULL AS "RequestedDeliveryDate",
    NULL AS "QualityInspectionResult",
    'SAP S/4HANA' AS "SourceSystem",
    CURRENT_UTCTIMESTAMP AS "LastDataUpdate"
FROM I_ProductionOrder AS po
JOIN SalesOrderLink sl ON po.SalesOrder = sl.SalesOrder
WHERE po.CreationDate BETWEEN 'YYYY-MM-DD' AND 'YYYY-MM-DD'

UNION ALL

SELECT
    sl.SalesOrder AS "LogisticsOrder",
    'Goods Produced' AS "ActivityName",
    md.DocumentDate || ' ' || md.CreationTime AS "EventTime",
    md.CreatedByUser AS "ExecutingUser",
    NULL AS "CustomerName",
    NULL AS "SupplierName",
    mdi.Material AS "MaterialNumber",
    mdi.Plant AS "Plant",
    NULL AS "RequestedDeliveryDate",
    NULL AS "QualityInspectionResult",
    'SAP S/4HANA' AS "SourceSystem",
    CURRENT_UTCTIMESTAMP AS "LastDataUpdate"
FROM I_MaterialDocumentHeader AS md
JOIN I_MaterialDocumentItem AS mdi ON md.MaterialDocument = mdi.MaterialDocument AND md.MaterialDocumentYear = mdi.MaterialDocumentYear
JOIN I_ProductionOrder AS po ON mdi.ManufacturingOrder = po.ManufacturingOrder
JOIN SalesOrderLink sl ON po.SalesOrder = sl.SalesOrder
WHERE md.DocumentDate BETWEEN 'YYYY-MM-DD' AND 'YYYY-MM-DD' AND mdi.GoodsMovementType = '101'

UNION ALL

SELECT
    qi.SalesOrder AS "LogisticsOrder",
    'Quality Inspection Performed' AS "ActivityName",
    qi.InspLotUsageDecisionDate || ' ' || qi.InspLotUsageDecisionTime AS "EventTime",
    qi.InspLotUsageDecisionMadeByUser AS "ExecutingUser",
    NULL AS "CustomerName",
    NULL AS "SupplierName",
    qi.Material AS "MaterialNumber",
    qi.Plant AS "Plant",
    NULL AS "RequestedDeliveryDate",
    qi.InspLotUsageDecisionCode AS "QualityInspectionResult",
    'SAP S/4HANA' AS "SourceSystem",
    CURRENT_UTCTIMESTAMP AS "LastDataUpdate"
FROM I_QualityInspection AS qi
WHERE qi.InspLotUsageDecisionDate BETWEEN 'YYYY-MM-DD' AND 'YYYY-MM-DD' AND qi.SalesOrder IS NOT NULL

UNION ALL

SELECT
    odi.SalesOrder AS "LogisticsOrder",
    'Outbound Delivery Created' AS "ActivityName",
    od.CreationDate || ' ' || od.CreationTime AS "EventTime",
    od.CreatedByUser AS "ExecutingUser",
    cust.CustomerName AS "CustomerName",
    NULL AS "SupplierName",
    odi.Material AS "MaterialNumber",
    odi.Plant AS "Plant",
    odi.RequestedDeliveryDate AS "RequestedDeliveryDate",
    NULL AS "QualityInspectionResult",
    'SAP S/4HANA' AS "SourceSystem",
    CURRENT_UTCTIMESTAMP AS "LastDataUpdate"
FROM I_OutboundDelivery AS od
JOIN I_OutboundDeliveryItem AS odi ON od.OutboundDelivery = odi.OutboundDelivery
LEFT JOIN I_Customer AS cust ON od.SoldToParty = cust.Customer
WHERE od.CreationDate BETWEEN 'YYYY-MM-DD' AND 'YYYY-MM-DD'

UNION ALL

SELECT
    odi.SalesOrder AS "LogisticsOrder",
    'Picking Completed' AS "ActivityName",
    od.PickingDate || ' ' || od.PickingTime AS "EventTime",
    od.LastChangedByUser AS "ExecutingUser",
    cust.CustomerName AS "CustomerName",
    NULL AS "SupplierName",
    odi.Material AS "MaterialNumber",
    odi.Plant AS "Plant",
    odi.RequestedDeliveryDate AS "RequestedDeliveryDate",
    NULL AS "QualityInspectionResult",
    'SAP S/4HANA' AS "SourceSystem",
    CURRENT_UTCTIMESTAMP AS "LastDataUpdate"
FROM I_OutboundDelivery AS od
JOIN I_OutboundDeliveryItem AS odi ON od.OutboundDelivery = odi.OutboundDelivery
LEFT JOIN I_Customer AS cust ON od.SoldToParty = cust.Customer
WHERE od.PickingDate BETWEEN 'YYYY-MM-DD' AND 'YYYY-MM-DD' AND od.OverallPickingStatus = 'C'

UNION ALL

SELECT
    odi.SalesOrder AS "LogisticsOrder",
    'Goods Issue Posted' AS "ActivityName",
    od.ActualGoodsMovementDate || ' ' || od.ActualGoodsMovementTime AS "EventTime",
    od.LastChangedByUser AS "ExecutingUser",
    cust.CustomerName AS "CustomerName",
    NULL AS "SupplierName",
    odi.Material AS "MaterialNumber",
    odi.Plant AS "Plant",
    odi.RequestedDeliveryDate AS "RequestedDeliveryDate",
    NULL AS "QualityInspectionResult",
    'SAP S/4HANA' AS "SourceSystem",
    CURRENT_UTCTIMESTAMP AS "LastDataUpdate"
FROM I_OutboundDelivery AS od
JOIN I_OutboundDeliveryItem AS odi ON od.OutboundDelivery = odi.OutboundDelivery
LEFT JOIN I_Customer AS cust ON od.SoldToParty = cust.Customer
WHERE od.ActualGoodsMovementDate BETWEEN 'YYYY-MM-DD' AND 'YYYY-MM-DD' AND od.OverallGoodsMovementStatus = 'C'

UNION ALL

SELECT
    sl.SalesOrder AS "LogisticsOrder",
    'Shipment Created' AS "ActivityName",
    sh.CreationDate || ' ' || sh.CreationTime AS "EventTime",
    sh.CreatedByUser AS "ExecutingUser",
    NULL AS "CustomerName",
    NULL AS "SupplierName",
    NULL AS "MaterialNumber",
    sh.ShippingPoint AS "Plant",
    NULL AS "RequestedDeliveryDate",
    NULL AS "QualityInspectionResult",
    'SAP S/4HANA' AS "SourceSystem",
    CURRENT_UTCTIMESTAMP AS "LastDataUpdate"
FROM I_Shipment AS sh
JOIN I_ShipmentDelivery AS sd ON sh.Shipment = sd.Shipment
JOIN SalesOrderLink sl ON sd.Delivery = sl.OutboundDelivery
WHERE sh.CreationDate BETWEEN 'YYYY-MM-DD' AND 'YYYY-MM-DD'

UNION ALL

SELECT
    odi.SalesOrder AS "LogisticsOrder",
    'Proof Of Delivery Confirmed' AS "ActivityName",
    od.PODActualDate || ' ' || '00:00:00' AS "EventTime",
    od.LastChangedByUser AS "ExecutingUser",
    cust.CustomerName AS "CustomerName",
    NULL AS "SupplierName",
    odi.Material AS "MaterialNumber",
    odi.Plant AS "Plant",
    odi.RequestedDeliveryDate AS "RequestedDeliveryDate",
    NULL AS "QualityInspectionResult",
    'SAP S/4HANA' AS "SourceSystem",
    CURRENT_UTCTIMESTAMP AS "LastDataUpdate"
FROM I_OutboundDelivery AS od
JOIN I_OutboundDeliveryItem AS odi ON od.OutboundDelivery = odi.OutboundDelivery
LEFT JOIN I_Customer AS cust ON od.SoldToParty = cust.Customer
WHERE od.PODActualDate BETWEEN 'YYYY-MM-DD' AND 'YYYY-MM-DD' AND od.OverallPODStatus = 'C'

UNION ALL

SELECT
    bdi.SalesDocument AS "LogisticsOrder",
    'Customer Invoice Created' AS "ActivityName",
    bd.BillingDocumentDate || ' ' || bd.CreationTime AS "EventTime",
    bd.CreatedByUser AS "ExecutingUser",
    cust.CustomerName AS "CustomerName",
    NULL AS "SupplierName",
    bdi.Material AS "MaterialNumber",
    bdi.Plant AS "Plant",
    NULL AS "RequestedDeliveryDate",
    NULL AS "QualityInspectionResult",
    'SAP S/4HANA' AS "SourceSystem",
    CURRENT_UTCTIMESTAMP AS "LastDataUpdate"
FROM I_BillingDocument AS bd
JOIN I_BillingDocumentItem AS bdi ON bd.BillingDocument = bdi.BillingDocument
LEFT JOIN I_Customer AS cust ON bd.SoldToParty = cust.Customer
WHERE bd.BillingDocumentDate BETWEEN 'YYYY-MM-DD' AND 'YYYY-MM-DD' AND bd.CompanyCode = 'YourCompanyCode' AND bdi.SalesDocument IS NOT NULL;

WITH
parameters AS (
    SELECT
        CAST('[Start date parameter]' AS TIMESTAMP) AS start_ts,
        CAST('[End date parameter]' AS TIMESTAMP) AS end_ts,
        CAST('[Last data update expression]' AS TIMESTAMP) AS last_data_update,
        CAST('[Source system label]' AS NVARCHAR(100)) AS source_system,
        CAST('[Company Code filter]' AS NVARCHAR(20)) AS company_code_filter,
        CAST('[Sales Organization filter]' AS NVARCHAR(20)) AS sales_org_filter,
        CAST('[Plant filter]' AS NVARCHAR(20)) AS plant_filter,
        CAST('[Purchasing Organization filter]' AS NVARCHAR(20)) AS purchasing_org_filter,
        CAST('[Document Type filter]' AS NVARCHAR(20)) AS document_type_filter
    FROM DUMMY
),
base_events AS (
    SELECT
        CAST('[Sales order correlation column]' AS NVARCHAR(100)) AS LogisticsOrder,
        CAST('Sales Order Created' AS NVARCHAR(100)) AS ActivityName,
        CAST('[Sales order creation timestamp]' AS TIMESTAMP) AS EventTime,
        CAST('[Executing user column]' AS NVARCHAR(100)) AS ExecutingUser,
        CAST('[Supplier name column]' AS NVARCHAR(255)) AS SupplierName,
        CAST('[Customer name column]' AS NVARCHAR(255)) AS CustomerName,
        CAST('[Material number column]' AS NVARCHAR(100)) AS MaterialNumber,
        CAST('[Plant column]' AS NVARCHAR(100)) AS Plant,
        CAST('[Requested delivery date column]' AS DATE) AS RequestedDeliveryDate,
        CAST('[Quality inspection result column]' AS NVARCHAR(100)) AS QualityInspectionResult
    FROM [Your sales order source object]
    CROSS JOIN parameters p
    WHERE CAST('[Sales order creation timestamp]' AS TIMESTAMP) >= p.start_ts
      AND CAST('[Sales order creation timestamp]' AS TIMESTAMP) < p.end_ts
      AND CAST('[Sales order company code column]' AS NVARCHAR(20)) = p.company_code_filter
      AND CAST('[Sales order sales organization column]' AS NVARCHAR(20)) = p.sales_org_filter
      AND CAST('[Sales order document type column]' AS NVARCHAR(20)) = p.document_type_filter

    UNION ALL

    SELECT
        CAST('[Inventory check correlation column]' AS NVARCHAR(100)),
        CAST('Inventory Availability Checked' AS NVARCHAR(100)),
        CAST('[Inventory check timestamp]' AS TIMESTAMP),
        CAST('[Inventory check user column]' AS NVARCHAR(100)),
        CAST('[Inventory supplier name column]' AS NVARCHAR(255)),
        CAST('[Inventory customer name column]' AS NVARCHAR(255)),
        CAST('[Inventory material number column]' AS NVARCHAR(100)),
        CAST('[Inventory plant column]' AS NVARCHAR(100)),
        CAST('[Inventory requested delivery date column]' AS DATE),
        CAST(NULL AS NVARCHAR(100))
    FROM [Your inventory availability source object]
    CROSS JOIN parameters p
    WHERE CAST('[Inventory check timestamp]' AS TIMESTAMP) >= p.start_ts
      AND CAST('[Inventory check timestamp]' AS TIMESTAMP) < p.end_ts
      AND CAST('[Inventory company code column]' AS NVARCHAR(20)) = p.company_code_filter
      AND CAST('[Inventory sales organization column]' AS NVARCHAR(20)) = p.sales_org_filter

    UNION ALL

    SELECT
        CAST('[Purchase requisition correlation column]' AS NVARCHAR(100)),
        CAST('Purchase Requisition Created' AS NVARCHAR(100)),
        CAST('[Purchase requisition creation timestamp]' AS TIMESTAMP),
        CAST('[Purchase requisition user column]' AS NVARCHAR(100)),
        CAST('[Purchase requisition supplier name column]' AS NVARCHAR(255)),
        CAST('[Purchase requisition customer name column]' AS NVARCHAR(255)),
        CAST('[Purchase requisition material number column]' AS NVARCHAR(100)),
        CAST('[Purchase requisition plant column]' AS NVARCHAR(100)),
        CAST('[Purchase requisition requested delivery date column]' AS DATE),
        CAST(NULL AS NVARCHAR(100))
    FROM [Your purchase requisition source object]
    CROSS JOIN parameters p
    WHERE CAST('[Purchase requisition creation timestamp]' AS TIMESTAMP) >= p.start_ts
      AND CAST('[Purchase requisition creation timestamp]' AS TIMESTAMP) < p.end_ts
      AND CAST('[Purchase requisition plant column]' AS NVARCHAR(20)) = p.plant_filter

    UNION ALL

    SELECT
        CAST('[Purchase order correlation column]' AS NVARCHAR(100)),
        CAST('Purchase Order Issued' AS NVARCHAR(100)),
        CAST('[Purchase order issue timestamp]' AS TIMESTAMP),
        CAST('[Purchase order user column]' AS NVARCHAR(100)),
        CAST('[Purchase order supplier name column]' AS NVARCHAR(255)),
        CAST('[Purchase order customer name column]' AS NVARCHAR(255)),
        CAST('[Purchase order material number column]' AS NVARCHAR(100)),
        CAST('[Purchase order plant column]' AS NVARCHAR(100)),
        CAST('[Purchase order requested delivery date column]' AS DATE),
        CAST(NULL AS NVARCHAR(100))
    FROM EKKO po_header
    INNER JOIN EKPO po_item
        ON po_header.[Purchase order number column] = po_item.[Purchase order number column]
    CROSS JOIN parameters p
    WHERE CAST('[Purchase order issue timestamp]' AS TIMESTAMP) >= p.start_ts
      AND CAST('[Purchase order issue timestamp]' AS TIMESTAMP) < p.end_ts
      AND CAST('[Purchase order company code column]' AS NVARCHAR(20)) = p.company_code_filter
      AND CAST('[Purchase order purchasing organization column]' AS NVARCHAR(20)) = p.purchasing_org_filter
      AND CAST('[Purchase order document type column]' AS NVARCHAR(20)) = p.document_type_filter

    UNION ALL

    SELECT
        CAST('[Goods receipt correlation column]' AS NVARCHAR(100)),
        CAST('Goods Receipt For PO Posted' AS NVARCHAR(100)),
        CAST('[Goods receipt posting timestamp]' AS TIMESTAMP),
        CAST('[Goods receipt user column]' AS NVARCHAR(100)),
        CAST('[Goods receipt supplier name column]' AS NVARCHAR(255)),
        CAST('[Goods receipt customer name column]' AS NVARCHAR(255)),
        CAST('[Goods receipt material number column]' AS NVARCHAR(100)),
        CAST('[Goods receipt plant column]' AS NVARCHAR(100)),
        CAST('[Goods receipt requested delivery date column]' AS DATE),
        CAST(NULL AS NVARCHAR(100))
    FROM [Your material document header source object] material_header
    INNER JOIN [Your material document item source object] material_item
        ON material_header.[Material document number column] = material_item.[Material document number column]
       AND material_header.[Material document year column] = material_item.[Material document year column]
    CROSS JOIN parameters p
    WHERE CAST('[Goods receipt posting timestamp]' AS TIMESTAMP) >= p.start_ts
      AND CAST('[Goods receipt posting timestamp]' AS TIMESTAMP) < p.end_ts
      AND CAST('[Goods receipt plant column]' AS NVARCHAR(20)) = p.plant_filter
      AND CAST('[Goods receipt movement type column]' AS NVARCHAR(20)) = '[Goods receipt movement type value]'

    UNION ALL

    SELECT
        CAST('[Production order correlation column]' AS NVARCHAR(100)),
        CAST('Production Order Created' AS NVARCHAR(100)),
        CAST('[Production order creation timestamp]' AS TIMESTAMP),
        CAST('[Production order user column]' AS NVARCHAR(100)),
        CAST('[Production order supplier name column]' AS NVARCHAR(255)),
        CAST('[Production order customer name column]' AS NVARCHAR(255)),
        CAST('[Production order material number column]' AS NVARCHAR(100)),
        CAST('[Production order plant column]' AS NVARCHAR(100)),
        CAST('[Production order requested delivery date column]' AS DATE),
        CAST(NULL AS NVARCHAR(100))
    FROM [Your production order source object]
    CROSS JOIN parameters p
    WHERE CAST('[Production order creation timestamp]' AS TIMESTAMP) >= p.start_ts
      AND CAST('[Production order creation timestamp]' AS TIMESTAMP) < p.end_ts
      AND CAST('[Production order plant column]' AS NVARCHAR(20)) = p.plant_filter

    UNION ALL

    SELECT
        CAST('[Production receipt correlation column]' AS NVARCHAR(100)),
        CAST('Goods Produced' AS NVARCHAR(100)),
        CAST('[Production receipt timestamp]' AS TIMESTAMP),
        CAST('[Production receipt user column]' AS NVARCHAR(100)),
        CAST('[Production receipt supplier name column]' AS NVARCHAR(255)),
        CAST('[Production receipt customer name column]' AS NVARCHAR(255)),
        CAST('[Production receipt material number column]' AS NVARCHAR(100)),
        CAST('[Production receipt plant column]' AS NVARCHAR(100)),
        CAST('[Production receipt requested delivery date column]' AS DATE),
        CAST(NULL AS NVARCHAR(100))
    FROM [Your production receipt source object]
    CROSS JOIN parameters p
    WHERE CAST('[Production receipt timestamp]' AS TIMESTAMP) >= p.start_ts
      AND CAST('[Production receipt timestamp]' AS TIMESTAMP) < p.end_ts
      AND CAST('[Production receipt plant column]' AS NVARCHAR(20)) = p.plant_filter

    UNION ALL

    SELECT
        CAST('[Quality inspection correlation column]' AS NVARCHAR(100)),
        CAST('Quality Inspection Performed' AS NVARCHAR(100)),
        CAST('[Quality inspection completion timestamp]' AS TIMESTAMP),
        CAST('[Quality inspection user column]' AS NVARCHAR(100)),
        CAST('[Quality supplier name column]' AS NVARCHAR(255)),
        CAST('[Quality customer name column]' AS NVARCHAR(255)),
        CAST('[Quality material number column]' AS NVARCHAR(100)),
        CAST('[Quality plant column]' AS NVARCHAR(100)),
        CAST('[Quality requested delivery date column]' AS DATE),
        CAST('[Quality inspection result column]' AS NVARCHAR(100))
    FROM [Your quality inspection source object]
    CROSS JOIN parameters p
    WHERE CAST('[Quality inspection completion timestamp]' AS TIMESTAMP) >= p.start_ts
      AND CAST('[Quality inspection completion timestamp]' AS TIMESTAMP) < p.end_ts
      AND CAST('[Quality plant column]' AS NVARCHAR(20)) = p.plant_filter

    UNION ALL

    SELECT
        CAST('[Outbound delivery correlation column]' AS NVARCHAR(100)),
        CAST('Outbound Delivery Created' AS NVARCHAR(100)),
        CAST('[Outbound delivery creation timestamp]' AS TIMESTAMP),
        CAST('[Outbound delivery user column]' AS NVARCHAR(100)),
        CAST('[Outbound delivery supplier name column]' AS NVARCHAR(255)),
        CAST('[Outbound delivery customer name column]' AS NVARCHAR(255)),
        CAST('[Outbound delivery material number column]' AS NVARCHAR(100)),
        CAST('[Outbound delivery plant column]' AS NVARCHAR(100)),
        CAST('[Outbound delivery requested delivery date column]' AS DATE),
        CAST(NULL AS NVARCHAR(100))
    FROM LIKP delivery_header
    INNER JOIN LIPS delivery_item
        ON delivery_header.[Delivery number column] = delivery_item.[Delivery number column]
    CROSS JOIN parameters p
    WHERE CAST('[Outbound delivery creation timestamp]' AS TIMESTAMP) >= p.start_ts
      AND CAST('[Outbound delivery creation timestamp]' AS TIMESTAMP) < p.end_ts
      AND CAST('[Outbound delivery plant column]' AS NVARCHAR(20)) = p.plant_filter

    UNION ALL

    SELECT
        CAST('[Picking correlation column]' AS NVARCHAR(100)),
        CAST('Picking Completed' AS NVARCHAR(100)),
        CAST('[Picking completion timestamp]' AS TIMESTAMP),
        CAST('[Picking user column]' AS NVARCHAR(100)),
        CAST('[Picking supplier name column]' AS NVARCHAR(255)),
        CAST('[Picking customer name column]' AS NVARCHAR(255)),
        CAST('[Picking material number column]' AS NVARCHAR(100)),
        CAST('[Picking plant column]' AS NVARCHAR(100)),
        CAST('[Picking requested delivery date column]' AS DATE),
        CAST(NULL AS NVARCHAR(100))
    FROM [Your picking status source object]
    CROSS JOIN parameters p
    WHERE CAST('[Picking completion timestamp]' AS TIMESTAMP) >= p.start_ts
      AND CAST('[Picking completion timestamp]' AS TIMESTAMP) < p.end_ts
      AND CAST('[Picking plant column]' AS NVARCHAR(20)) = p.plant_filter
      AND CAST('[Picking completion status column]' AS NVARCHAR(50)) = '[Picking completed status value]'

    UNION ALL

    SELECT
        CAST('[Goods issue correlation column]' AS NVARCHAR(100)),
        CAST('Goods Issue Posted' AS NVARCHAR(100)),
        CAST('[Goods issue posting timestamp]' AS TIMESTAMP),
        CAST('[Goods issue user column]' AS NVARCHAR(100)),
        CAST('[Goods issue supplier name column]' AS NVARCHAR(255)),
        CAST('[Goods issue customer name column]' AS NVARCHAR(255)),
        CAST('[Goods issue material number column]' AS NVARCHAR(100)),
        CAST('[Goods issue plant column]' AS NVARCHAR(100)),
        CAST('[Goods issue requested delivery date column]' AS DATE),
        CAST(NULL AS NVARCHAR(100))
    FROM [Your goods issue source object]
    CROSS JOIN parameters p
    WHERE CAST('[Goods issue posting timestamp]' AS TIMESTAMP) >= p.start_ts
      AND CAST('[Goods issue posting timestamp]' AS TIMESTAMP) < p.end_ts
      AND CAST('[Goods issue plant column]' AS NVARCHAR(20)) = p.plant_filter
      AND CAST('[Goods issue movement type column]' AS NVARCHAR(20)) = '[Goods issue movement type value]'

    UNION ALL

    SELECT
        CAST('[Shipment correlation column]' AS NVARCHAR(100)),
        CAST('Shipment Created' AS NVARCHAR(100)),
        CAST('[Shipment creation timestamp]' AS TIMESTAMP),
        CAST('[Shipment user column]' AS NVARCHAR(100)),
        CAST('[Shipment supplier name column]' AS NVARCHAR(255)),
        CAST('[Shipment customer name column]' AS NVARCHAR(255)),
        CAST('[Shipment material number column]' AS NVARCHAR(100)),
        CAST('[Shipment plant column]' AS NVARCHAR(100)),
        CAST('[Shipment requested delivery date column]' AS DATE),
        CAST(NULL AS NVARCHAR(100))
    FROM [Your shipment source object]
    CROSS JOIN parameters p
    WHERE CAST('[Shipment creation timestamp]' AS TIMESTAMP) >= p.start_ts
      AND CAST('[Shipment creation timestamp]' AS TIMESTAMP) < p.end_ts
      AND CAST('[Shipment plant column]' AS NVARCHAR(20)) = p.plant_filter

    UNION ALL

    SELECT
        CAST('[Unloading correlation column]' AS NVARCHAR(100)),
        CAST('Goods Unloaded at Destination' AS NVARCHAR(100)),
        CAST('[Unloading timestamp]' AS TIMESTAMP),
        CAST('[Unloading user column]' AS NVARCHAR(100)),
        CAST('[Unloading supplier name column]' AS NVARCHAR(255)),
        CAST('[Unloading customer name column]' AS NVARCHAR(255)),
        CAST('[Unloading material number column]' AS NVARCHAR(100)),
        CAST('[Unloading plant column]' AS NVARCHAR(100)),
        CAST('[Unloading requested delivery date column]' AS DATE),
        CAST(NULL AS NVARCHAR(100))
    FROM [Your unloading or carrier event source object]
    CROSS JOIN parameters p
    WHERE CAST('[Unloading timestamp]' AS TIMESTAMP) >= p.start_ts
      AND CAST('[Unloading timestamp]' AS TIMESTAMP) < p.end_ts

    UNION ALL

    SELECT
        CAST('[POD correlation column]' AS NVARCHAR(100)),
        CAST('Proof Of Delivery Confirmed' AS NVARCHAR(100)),
        CAST('[POD confirmation timestamp]' AS TIMESTAMP),
        CAST('[POD user column]' AS NVARCHAR(100)),
        CAST('[POD supplier name column]' AS NVARCHAR(255)),
        CAST('[POD customer name column]' AS NVARCHAR(255)),
        CAST('[POD material number column]' AS NVARCHAR(100)),
        CAST('[POD plant column]' AS NVARCHAR(100)),
        CAST('[POD requested delivery date column]' AS DATE),
        CAST(NULL AS NVARCHAR(100))
    FROM [Your proof of delivery source object]
    CROSS JOIN parameters p
    WHERE CAST('[POD confirmation timestamp]' AS TIMESTAMP) >= p.start_ts
      AND CAST('[POD confirmation timestamp]' AS TIMESTAMP) < p.end_ts

    UNION ALL

    SELECT
        CAST('[Billing correlation column]' AS NVARCHAR(100)),
        CAST('Customer Invoice Created' AS NVARCHAR(100)),
        CAST('[Billing creation timestamp]' AS TIMESTAMP),
        CAST('[Billing user column]' AS NVARCHAR(100)),
        CAST('[Billing supplier name column]' AS NVARCHAR(255)),
        CAST('[Billing customer name column]' AS NVARCHAR(255)),
        CAST('[Billing material number column]' AS NVARCHAR(100)),
        CAST('[Billing plant column]' AS NVARCHAR(100)),
        CAST('[Billing requested delivery date column]' AS DATE),
        CAST(NULL AS NVARCHAR(100))
    FROM [Your billing document source object]
    CROSS JOIN parameters p
    WHERE CAST('[Billing creation timestamp]' AS TIMESTAMP) >= p.start_ts
      AND CAST('[Billing creation timestamp]' AS TIMESTAMP) < p.end_ts
)
SELECT
    LogisticsOrder,
    ActivityName,
    EventTime,
    parameters.source_system AS SourceSystem,
    parameters.last_data_update AS LastDataUpdate,
    ExecutingUser,
    SupplierName,
    CustomerName,
    MaterialNumber,
    Plant,
    RequestedDeliveryDate,
    QualityInspectionResult
FROM base_events
CROSS JOIN parameters
WHERE LogisticsOrder IS NOT NULL
  AND ActivityName IS NOT NULL
  AND EventTime IS NOT NULL
ORDER BY LogisticsOrder, EventTime, ActivityName;

REPORT Z_PM_SCM_EXTRACTION.

*&---------------------------------------------------------------------*
*& Selection Screen
*&---------------------------------------------------------------------*
PARAMETERS: p_file TYPE string LOWER CASE DEFAULT '/usr/sap/trans/tmp/scm_event_log.csv'.
SELECT-OPTIONS: s_erdat FOR sy-datum OBLIGATORY,
                  s_vkorg FOR vbak-vkorg,
                  s_bukrs FOR vbak-bukrs.

*&---------------------------------------------------------------------*
*& Data Type Definitions
*&---------------------------------------------------------------------*
TYPES: BEGIN OF ty_event_log,
         LogisticsOrder         TYPE vbeln_va,
         ActivityName           TYPE string,
         EventTime              TYPE string,
         SourceSystem           TYPE sysysid,
         LastDataUpdate         TYPE string,
         ExecutingUser          TYPE ernam,
         SupplierName           TYPE name1_gp,
         CustomerName           TYPE name1_gp,
         MaterialNumber         TYPE matnr,
         Plant                  TYPE werks_d,
         RequestedDeliveryDate  TYPE vdatu,
         QualityInspectionResult TYPE string,
       END OF ty_event_log.

*&---------------------------------------------------------------------*
*& Data Declarations
*&---------------------------------------------------------------------*
DATA: lt_event_log TYPE TABLE OF ty_event_log,
      ls_event_log TYPE ty_event_log,
      lv_sysid     TYPE sysysid,
      lv_last_update TYPE string.

*&---------------------------------------------------------------------*
*& START-OF-SELECTION
*&---------------------------------------------------------------------*
START-OF-SELECTION.

  lv_sysid = sy-sysid.
  CONCATENATE sy-datum sy-uzeit INTO lv_last_update.

  " Select base data: Sales Orders
  SELECT h~vbeln, h~erdat, h~erzet, h~ernam, h~kunnr, h~bukrs, h~vdatu, i~posnr, i~matnr, i~werks
    INTO TABLE @DATA(lt_so)
    FROM vbak AS h
    INNER JOIN vbap AS i ON h~vbeln = i~vbeln
    WHERE h~erdat IN @s_erdat
      AND h~vkorg IN @s_vkorg
      AND h~bukrs IN @s_bukrs.

  IF lt_so IS INITIAL.
    MESSAGE 'No sales orders found for the given criteria.' TYPE 'I'.
    RETURN.
  ENDIF.

  " Select related documents using Document Flow (VBFA)
  SELECT *
    INTO TABLE @DATA(lt_vbfa)
    FROM vbfa
    FOR ALL ENTRIES IN @lt_so
    WHERE vbelv = @lt_so-vbeln
      AND posnv = @lt_so-posnr.

  " Collect all unique document numbers
  DATA: lt_vbeln_pr  TYPE RANGE OF banfn,
        lt_vbeln_po  TYPE RANGE OF ebeln,
        lt_vbeln_dn  TYPE RANGE OF vbeln_vl,
        lt_vbeln_gi  TYPE RANGE OF mblnr,
        lt_vbeln_auf TYPE RANGE OF aufnr,
        lt_vbeln_inv TYPE RANGE OF vbeln_vf,
        lt_vbeln_shp TYPE RANGE OF tknum.

  LOOP AT lt_vbfa INTO DATA(ls_vbfa).
    CASE ls_vbfa-vbtyp_n.
      WHEN 'H'. " Purchase Requisition
        APPEND ls_vbfa-vbeln TO lt_vbeln_pr.
      WHEN 'K'. " Purchase Order
        APPEND ls_vbfa-vbeln TO lt_vbeln_po.
      WHEN 'J'. " Delivery
        APPEND ls_vbfa-vbeln TO lt_vbeln_dn.
      WHEN 'R'. " Goods Movement (GI)
        APPEND ls_vbfa-vbeln TO lt_vbeln_gi.
      WHEN 'L'. " Production Order
        APPEND ls_vbfa-vbeln TO lt_vbeln_auf.
      WHEN 'M'. " Invoice
        APPEND ls_vbfa-vbeln TO lt_vbeln_inv.
      WHEN '8'. " Shipment
        APPEND ls_vbfa-vbeln TO lt_vbeln_shp.
    ENDCASE.
  ENDLOOP.

  SORT lt_vbeln_pr. DELETE ADJACENT DUPLICATES FROM lt_vbeln_pr.
  SORT lt_vbeln_po. DELETE ADJACENT DUPLICATES FROM lt_vbeln_po.
  SORT lt_vbeln_dn. DELETE ADJACENT DUPLICATES FROM lt_vbeln_dn.
  SORT lt_vbeln_gi. DELETE ADJACENT DUPLICATES FROM lt_vbeln_gi.
  SORT lt_vbeln_auf. DELETE ADJACENT DUPLICATES FROM lt_vbeln_auf.
  SORT lt_vbeln_inv. DELETE ADJACENT DUPLICATES FROM lt_vbeln_inv.
  SORT lt_vbeln_shp. DELETE ADJACENT DUPLICATES FROM lt_vbeln_shp.

  " Select detailed data for each document type
  SELECT banfn, badat, ernam FROM eban INTO TABLE @DATA(lt_eban) FOR ALL ENTRIES IN @lt_so WHERE bnfpo = @lt_so-posnr AND banfn IN @lt_vbeln_pr.
  SELECT ebeln, aedat, ernam, lifnr FROM ekko INTO TABLE @DATA(lt_ekko) WHERE ebeln IN @lt_vbeln_po.
  SELECT vbeln, erdat, erzet, ernam, kodat, wadat_ist, podat FROM likp INTO TABLE @DATA(lt_likp) WHERE vbeln IN @lt_vbeln_dn.
  SELECT mblnr, mjahr, budat, usnam FROM mkpf INTO TABLE @DATA(lt_mkpf) WHERE mblnr IN @lt_vbeln_gi.
  SELECT mblnr, mjahr, zeile, bwart, lfbnr, ebeln, aufnr FROM mseg INTO TABLE @DATA(lt_mseg) FOR ALL ENTRIES IN @lt_mkpf WHERE mblnr = @lt_mkpf-mblnr AND mjahr = @lt_mkpf-mjahr.
  SELECT aufnr, erdat, ernam FROM aufk INTO TABLE @DATA(lt_aufk) WHERE aufnr IN @lt_vbeln_auf.
  SELECT prueflos, vdatum, vcode FROM qave INTO TABLE @DATA(lt_qave) FOR ALL ENTRIES IN @lt_so WHERE aufnr IN @lt_vbeln_auf.
  SELECT vbeln, erdat, erzet, ernam FROM vbrk INTO TABLE @DATA(lt_vbrk) WHERE vbeln IN @lt_vbeln_inv.
  SELECT tknum, erdat, erzet FROM vttk INTO TABLE @DATA(lt_vttk) WHERE tknum IN @lt_vbeln_shp.
  SELECT kunnr, name1 FROM kna1 INTO TABLE @DATA(lt_kna1) FOR ALL ENTRIES IN @lt_so WHERE kunnr = @lt_so-kunnr.
  SELECT lifnr, name1 FROM lfa1 INTO TABLE @DATA(lt_lfa1) FOR ALL ENTRIES IN @lt_ekko WHERE lifnr = @lt_ekko-lifnr.

  " Assemble Event Log
  LOOP AT lt_so INTO DATA(ls_so).
    CLEAR ls_event_log.
    READ TABLE lt_kna1 INTO DATA(ls_kna1) WITH KEY kunnr = ls_so-kunnr BINARY SEARCH.

    ls_event_log-LogisticsOrder = ls_so-vbeln.
    ls_event_log-SourceSystem = lv_sysid.
    ls_event_log-LastDataUpdate = lv_last_update.
    ls_event_log-CustomerName = ls_kna1-name1.
    ls_event_log-MaterialNumber = ls_so-matnr.
    ls_event_log-Plant = ls_so-werks.
    ls_event_log-RequestedDeliveryDate = ls_so-vdatu.

    " 1. Sales Order Created
    ls_event_log-ActivityName = 'Sales Order Created'.
    CONCATENATE ls_so-erdat ls_so-erzet INTO ls_event_log-EventTime.
    ls_event_log-ExecutingUser = ls_so-ernam.
    APPEND ls_event_log TO lt_event_log.

    " 2. Inventory Availability Checked (proxy event)
    ls_event_log-ActivityName = 'Inventory Availability Checked'.
    CONCATENATE ls_so-erdat ls_so-erzet INTO ls_event_log-EventTime. " Using SO creation time as a proxy
    ls_event_log-ExecutingUser = ls_so-ernam.
    APPEND ls_event_log TO lt_event_log.

    " Find related documents for this SO item
    LOOP AT lt_vbfa INTO ls_vbfa WHERE vbelv = ls_so-vbeln AND posnv = ls_so-posnr.

      CASE ls_vbfa-vbtyp_n.
        WHEN 'H'. " 3. Purchase Requisition Created
          READ TABLE lt_eban INTO DATA(ls_eban) WITH KEY banfn = ls_vbfa-vbeln.
          IF sy-subrc = 0.
            ls_event_log-ActivityName = 'Purchase Requisition Created'.
            CONCATENATE ls_eban-badat '000000' INTO ls_event_log-EventTime. " PR has no time field
            ls_event_log-ExecutingUser = ls_eban-ernam.
            APPEND ls_event_log TO lt_event_log.
          ENDIF.

        WHEN 'K'. " 4. Purchase Order Issued
          READ TABLE lt_ekko INTO DATA(ls_ekko) WITH KEY ebeln = ls_vbfa-vbeln.
          IF sy-subrc = 0.
            READ TABLE lt_lfa1 INTO DATA(ls_lfa1) WITH KEY lifnr = ls_ekko-lifnr BINARY SEARCH.
            ls_event_log-ActivityName = 'Purchase Order Issued'.
            CONCATENATE ls_ekko-aedat '000000' INTO ls_event_log-EventTime.
            ls_event_log-ExecutingUser = ls_ekko-ernam.
            ls_event_log-SupplierName = ls_lfa1-name1.
            APPEND ls_event_log TO lt_event_log.
          ENDIF.

        WHEN 'L'. " 6. Production Order Created
          READ TABLE lt_aufk INTO DATA(ls_aufk) WITH KEY aufnr = ls_vbfa-vbeln.
          IF sy-subrc = 0.
            ls_event_log-ActivityName = 'Production Order Created'.
            CONCATENATE ls_aufk-erdat '000000' INTO ls_event_log-EventTime.
            ls_event_log-ExecutingUser = ls_aufk-ernam.
            APPEND ls_event_log TO lt_event_log.

            " 8. Quality Inspection Performed
            READ TABLE lt_qave INTO DATA(ls_qave) WITH KEY prueflos = ls_vbfa-vbeln. " Approximation linking lot to order
            IF sy-subrc = 0.
              ls_event_log-ActivityName = 'Quality Inspection Performed'.
              CONCATENATE ls_qave-vdatum '000000' INTO ls_event_log-EventTime.
              ls_event_log-QualityInspectionResult = ls_qave-vcode.
              APPEND ls_event_log TO lt_event_log.
              CLEAR ls_event_log-QualityInspectionResult.
            ENDIF.
          ENDIF.

        WHEN 'J'. " 9. Outbound Delivery Created
          READ TABLE lt_likp INTO DATA(ls_likp) WITH KEY vbeln = ls_vbfa-vbeln.
          IF sy-subrc = 0.
            ls_event_log-ActivityName = 'Outbound Delivery Created'.
            CONCATENATE ls_likp-erdat ls_likp-erzet INTO ls_event_log-EventTime.
            ls_event_log-ExecutingUser = ls_likp-ernam.
            APPEND ls_event_log TO lt_event_log.

            " 10. Picking Completed
            IF ls_likp-kodat IS NOT INITIAL.
              ls_event_log-ActivityName = 'Picking Completed'.
              CONCATENATE ls_likp-kodat '120000' INTO ls_event_log-EventTime. " Using Picking Date as proxy
              APPEND ls_event_log TO lt_event_log.
            ENDIF.

            " 14. Proof Of Delivery Confirmed
            IF ls_likp-podat IS NOT INITIAL.
              ls_event_log-ActivityName = 'Proof Of Delivery Confirmed'.
              CONCATENATE ls_likp-podat '000000' INTO ls_event_log-EventTime.
              APPEND ls_event_log TO lt_event_log.
            ENDIF.
          ENDIF.

        WHEN 'M'. " 15. Customer Invoice Created
          READ TABLE lt_vbrk INTO DATA(ls_vbrk) WITH KEY vbeln = ls_vbfa-vbeln.
          IF sy-subrc = 0.
            ls_event_log-ActivityName = 'Customer Invoice Created'.
            CONCATENATE ls_vbrk-erdat ls_vbrk-erzet INTO ls_event_log-EventTime.
            ls_event_log-ExecutingUser = ls_vbrk-ernam.
            APPEND ls_event_log TO lt_event_log.
          ENDIF.

        WHEN '8'. " 12. Shipment Created
          READ TABLE lt_vttk INTO DATA(ls_vttk) WITH KEY tknum = ls_vbfa-vbeln.
          IF sy-subrc = 0.
            ls_event_log-ActivityName = 'Shipment Created'.
            CONCATENATE ls_vttk-erdat ls_vttk-erzet INTO ls_event_log-EventTime.
            APPEND ls_event_log TO lt_event_log.
          ENDIF.

      ENDCASE.
    ENDLOOP.

    " Find material movements (MSEG) not directly in VBFA
    " 5. Goods Receipt For PO Posted
    LOOP AT lt_mseg INTO DATA(ls_mseg_po) WHERE ebeln IN (SELECT ebeln FROM ekpo WHERE banfn IN (SELECT banfn FROM eban WHERE vbeln = ls_so-vbeln) ) AND bwart = '101'.
      READ TABLE lt_mkpf INTO DATA(ls_mkpf_po) WITH KEY mblnr = ls_mseg_po-mblnr mjahr = ls_mseg_po-mjahr.
      IF sy-subrc = 0.
        ls_event_log-ActivityName = 'Goods Receipt For PO Posted'.
        CONCATENATE ls_mkpf_po-budat '000000' INTO ls_event_log-EventTime.
        ls_event_log-ExecutingUser = ls_mkpf_po-usnam.
        APPEND ls_event_log TO lt_event_log.
      ENDIF.
    ENDLOOP.

    " 7. Goods Produced
    LOOP AT lt_mseg INTO DATA(ls_mseg_pp) WHERE aufnr IN (SELECT aufnr FROM afpo WHERE kdauf = ls_so-vbeln) AND bwart = '101'.
      READ TABLE lt_mkpf INTO DATA(ls_mkpf_pp) WITH KEY mblnr = ls_mseg_pp-mblnr mjahr = ls_mseg_pp-mjahr.
      IF sy-subrc = 0.
        ls_event_log-ActivityName = 'Goods Produced'.
        CONCATENATE ls_mkpf_pp-budat '000000' INTO ls_event_log-EventTime.
        ls_event_log-ExecutingUser = ls_mkpf_pp-usnam.
        APPEND ls_event_log TO lt_event_log.
      ENDIF.
    ENDLOOP.

    " 11. Goods Issue Posted
    LOOP AT lt_mseg INTO DATA(ls_mseg_gi) WHERE lfbnr IN (SELECT vbeln FROM lips WHERE vgbel = ls_so-vbeln) AND bwart = '601'.
      READ TABLE lt_mkpf INTO DATA(ls_mkpf_gi) WITH KEY mblnr = ls_mseg_gi-mblnr mjahr = ls_mseg_gi-mjahr.
      IF sy-subrc = 0.
        ls_event_log-ActivityName = 'Goods Issue Posted'.
        CONCATENATE ls_mkpf_gi-budat '000000' INTO ls_event_log-EventTime.
        ls_event_log-ExecutingUser = ls_mkpf_gi-usnam.
        APPEND ls_event_log TO lt_event_log.
      ENDIF.
    ENDLOOP.

  ENDLOOP.

  " Remove duplicate events for the same case
  SORT lt_event_log BY LogisticsOrder ActivityName EventTime.
  DELETE ADJACENT DUPLICATES FROM lt_event_log COMPARING LogisticsOrder ActivityName EventTime.

  " Write data to file
  DATA: lt_output TYPE TABLE OF string.
  APPEND 'LogisticsOrder,ActivityName,EventTime,SourceSystem,LastDataUpdate,ExecutingUser,SupplierName,CustomerName,MaterialNumber,Plant,RequestedDeliveryDate,QualityInspectionResult' TO lt_output.
  LOOP AT lt_event_log INTO ls_event_log.
    DATA(lv_line) = |
      { ls_event_log-LogisticsOrder },
      { ls_event_log-ActivityName },
      { ls_event_log-EventTime },
      { ls_event_log-SourceSystem },
      { ls_event_log-LastDataUpdate },
      { ls_event_log-ExecutingUser },
      { ls_event_log-SupplierName },
      { ls_event_log-CustomerName },
      { ls_event_log-MaterialNumber },
      { ls_event_log-Plant },
      { ls_event_log-RequestedDeliveryDate },
      { ls_event_log-QualityInspectionResult }|
    .
    REPLACE ALL OCCURRENCES OF ',' IN lv_line WITH ' '.
    REPLACE ALL OCCURRENCES OF REGEX '\s+' IN lv_line WITH '' LEADING.
    CONDENSE lv_line.
    APPEND lv_line TO lt_output.
  ENDLOOP.

  cl_gui_frontend_services=>gui_download(
    EXPORTING
      filename                = p_file
      filetype                = 'ASC'
    CHANGING
      data_tab                = lt_output
    EXCEPTIONS
      OTHERS                  = 24
  ).

  IF sy-subrc <> 0.
    MESSAGE 'Error downloading file.' TYPE 'E'.
  ELSE.
    MESSAGE |File downloaded successfully to { p_file }| TYPE 'S'.
  ENDIF.