Data Template: Van Bestelling tot Betaling - Verkooporderverwerking

/*
-- =============================================================================================
-- Process: Order to Cash - Sales Order Processing
-- System: SAP S/4HANA
-- Method: CDS View Extraction
-- Description: Extracts a detailed event log for the Order to Cash process using standard CDS views.
-- =============================================================================================
*/

WITH SalesOrders AS (
    -- Select the base set of sales orders to be analyzed.
    -- *** Set your extraction parameters here ***
    SELECT SalesDocument, CreationDate, CreationTime, LastChangeDate, SalesDocumentType, SalesOrganization, SoldToParty, LastChangedByUser, RequestedDeliveryDate
    FROM I_SalesDocument
    WHERE CreationDate BETWEEN :START_DATE AND :END_DATE -- Example: '20230101' AND '20230630'
    -- AND SalesOrganization = '[Your Sales Org]'
    -- AND SalesDocumentType = '[Your Order Type]'
),

ChangeDocs AS (
    -- Pre-select change document data for performance.
    SELECT 
        OBJECTCLAS, 
        OBJECTID, 
        CHANGENR, 
        TABNAME, 
        FNAME, 
        CHNGIND, 
        VALUE_NEW, 
        VALUE_OLD,
        CAST(CDHDR_UDATE AS VARCHAR(8)) || CAST(CDHDR_UTIME AS VARCHAR(6)) as EventTimestamp
    FROM I_ChangeDocument
    WHERE OBJECTCLAS = 'VERKBELEG' -- Sales Document Object Class
      AND OBJECTID IN (SELECT SalesDocument FROM SalesOrders)
)

-- 1. Sales Order Created
SELECT
    SD.SalesDocument AS "Vbeln",
    'Sales Order Created' AS "ActivityName",
    TO_TIMESTAMP(SD.CreationDate || LPAD(SD.CreationTime, 6, '0'), 'YYYYMMDDHH24MISS') AS "EventTime",
    SD.LastChangedByUser AS "Ernam",
    SD.SalesDocumentType AS "Auart",
    SD.SalesOrganization AS "Vkorg",
    SD.SoldToParty AS "Kunnr",
    CAST(NULL AS NVARCHAR(40)) AS "Matnr",
    CAST(NULL AS DECIMAL(15,2)) AS "Netwr",
    SD.RequestedDeliveryDate AS "Vdatu"
FROM SalesOrders AS SD

UNION ALL

-- 2. Credit Check Performed (Inferred from status change)
SELECT DISTINCT
    CD.OBJECTID AS "Vbeln",
    'Credit Check Performed' AS "ActivityName",
    TO_TIMESTAMP(SUBSTRING(CD.EventTimestamp, 1, 8) || SUBSTRING(CD.EventTimestamp, 9, 6), 'YYYYMMDDHH24MISS') AS "EventTime",
    I_CDHDR.USERNAME AS "Ernam",
    SO.SalesDocumentType AS "Auart",
    SO.SalesOrganization AS "Vkorg",
    SO.SoldToParty AS "Kunnr",
    CAST(NULL AS NVARCHAR(40)) AS "Matnr",
    CAST(NULL AS DECIMAL(15,2)) AS "Netwr",
    SO.RequestedDeliveryDate AS "Vdatu"
FROM ChangeDocs AS CD
JOIN I_ChangeDocumentHeader AS I_CDHDR ON CD.CHANGENR = I_CDHDR.CHANGENR
JOIN SalesOrders SO ON CD.OBJECTID = SO.SalesDocument
WHERE CD.TABNAME = 'VBAK' AND CD.FNAME = 'CMGST' AND CD.VALUE_NEW <> 'A' -- Status changed to something other than 'Not Performed'

UNION ALL

-- 3. Sales Order Block Set
SELECT DISTINCT
    CD.OBJECTID AS "Vbeln",
    'Sales Order Block Set' AS "ActivityName",
    TO_TIMESTAMP(SUBSTRING(CD.EventTimestamp, 1, 8) || SUBSTRING(CD.EventTimestamp, 9, 6), 'YYYYMMDDHH24MISS') AS "EventTime",
    I_CDHDR.USERNAME AS "Ernam",
    SO.SalesDocumentType AS "Auart",
    SO.SalesOrganization AS "Vkorg",
    SO.SoldToParty AS "Kunnr",
    CAST(NULL AS NVARCHAR(40)) AS "Matnr",
    CAST(NULL AS DECIMAL(15,2)) AS "Netwr",
    SO.RequestedDeliveryDate AS "Vdatu"
FROM ChangeDocs AS CD
JOIN I_ChangeDocumentHeader AS I_CDHDR ON CD.CHANGENR = I_CDHDR.CHANGENR
JOIN SalesOrders SO ON CD.OBJECTID = SO.SalesDocument
WHERE CD.TABNAME = 'VBAK' AND CD.FNAME IN ('LIFSK', 'FAKSK') AND CD.VALUE_NEW <> ''

UNION ALL

-- 4. Sales Order Block Removed
SELECT DISTINCT
    CD.OBJECTID AS "Vbeln",
    'Sales Order Block Removed' AS "ActivityName",
    TO_TIMESTAMP(SUBSTRING(CD.EventTimestamp, 1, 8) || SUBSTRING(CD.EventTimestamp, 9, 6), 'YYYYMMDDHH24MISS') AS "EventTime",
    I_CDHDR.USERNAME AS "Ernam",
    SO.SalesDocumentType AS "Auart",
    SO.SalesOrganization AS "Vkorg",
    SO.SoldToParty AS "Kunnr",
    CAST(NULL AS NVARCHAR(40)) AS "Matnr",
    CAST(NULL AS DECIMAL(15,2)) AS "Netwr",
    SO.RequestedDeliveryDate AS "Vdatu"
FROM ChangeDocs AS CD
JOIN I_ChangeDocumentHeader AS I_CDHDR ON CD.CHANGENR = I_CDHDR.CHANGENR
JOIN SalesOrders SO ON CD.OBJECTID = SO.SalesDocument
WHERE CD.TABNAME = 'VBAK' AND CD.FNAME IN ('LIFSK', 'FAKSK') AND CD.VALUE_NEW = '' AND CD.VALUE_OLD <> ''

UNION ALL

-- 5. Sales Order Changed (Significant Fields)
SELECT DISTINCT
    CD.OBJECTID AS "Vbeln",
    'Sales Order Changed' AS "ActivityName",
    TO_TIMESTAMP(SUBSTRING(CD.EventTimestamp, 1, 8) || SUBSTRING(CD.EventTimestamp, 9, 6), 'YYYYMMDDHH24MISS') AS "EventTime",
    I_CDHDR.USERNAME AS "Ernam",
    SO.SalesDocumentType AS "Auart",
    SO.SalesOrganization AS "Vkorg",
    SO.SoldToParty AS "Kunnr",
    CAST(NULL AS NVARCHAR(40)) AS "Matnr",
    CAST(NULL AS DECIMAL(15,2)) AS "Netwr",
    SO.RequestedDeliveryDate AS "Vdatu"
FROM ChangeDocs AS CD
JOIN I_ChangeDocumentHeader AS I_CDHDR ON CD.CHANGENR = I_CDHDR.CHANGENR
JOIN SalesOrders SO ON CD.OBJECTID = SO.SalesDocument
WHERE CD.TABNAME IN ('VBAP', 'VBAK') AND CD.FNAME IN ('KWMENG', 'NETWR', 'VDATU', 'KETDAT')

UNION ALL

-- 6. Sales Order Confirmed
SELECT DISTINCT
    SDI.SalesDocument AS "Vbeln",
    'Sales Order Confirmed' AS "ActivityName",
    TO_TIMESTAMP(SDS.CreationDate || '000000', 'YYYYMMDDHH24MISS') AS "EventTime",
    SD.LastChangedByUser AS "Ernam",
    SD.SalesDocumentType AS "Auart",
    SD.SalesOrganization AS "Vkorg",
    SD.SoldToParty AS "Kunnr",
    SDI.Material AS "Matnr",
    SDI.NetAmount AS "Netwr",
    SD.RequestedDeliveryDate AS "Vdatu"
FROM I_SalesDocumentScheduleLine AS SDS
JOIN I_SalesDocumentItem AS SDI ON SDS.SalesDocument = SDI.SalesDocument AND SDS.SalesDocumentItem = SDI.SalesDocumentItem
JOIN SalesOrders SD ON SDI.SalesDocument = SD.SalesDocument
WHERE SDS.ConfirmedQuantity > 0

UNION ALL

-- 7. Delivery Created
SELECT
    DF.PrecedingDocument AS "Vbeln",
    'Delivery Created' AS "ActivityName",
    TO_TIMESTAMP(DD.CreationDate || LPAD(DD.CreationTime, 6, '0'), 'YYYYMMDDHH24MISS') AS "EventTime",
    DD.CreatedByUser AS "Ernam",
    SO.SalesDocumentType AS "Auart",
    SO.SalesOrganization AS "Vkorg",
    SO.SoldToParty AS "Kunnr",
    CAST(NULL AS NVARCHAR(40)) AS "Matnr",
    CAST(NULL AS DECIMAL(15,2)) AS "Netwr",
    SO.RequestedDeliveryDate AS "Vdatu"
FROM I_DocumentFlow AS DF
JOIN I_DeliveryDocument AS DD ON DF.SubsequentDocument = DD.DeliveryDocument
JOIN SalesOrders AS SO ON DF.PrecedingDocument = SO.SalesDocument
WHERE DF.PrecedingDocumentCategory = 'C' AND DF.SubsequentDocumentCategory = 'J'

UNION ALL

-- 8. Goods Picked (Inferred from Picking Status Completion)
SELECT
    DF.PrecedingDocument AS "Vbeln",
    'Goods Picked' AS "ActivityName",
    TO_TIMESTAMP(DDI.LastChangeDate || '120000', 'YYYYMMDDHH24MISS') AS "EventTime", -- Note: Time is an approximation
    DDI.LastChangedByUser AS "Ernam",
    SO.SalesDocumentType AS "Auart",
    SO.SalesOrganization AS "Vkorg",
    SO.SoldToParty AS "Kunnr",
    DDI.Material AS "Matnr",
    CAST(NULL AS DECIMAL(15,2)) AS "Netwr",
    SO.RequestedDeliveryDate AS "Vdatu"
FROM I_DocumentFlow AS DF
JOIN I_DeliveryDocumentItem AS DDI ON DF.SubsequentDocument = DDI.DeliveryDocument
JOIN SalesOrders AS SO ON DF.PrecedingDocument = SO.SalesDocument
WHERE DF.PrecedingDocumentCategory = 'C' AND DDI.PickingStatus = 'C' AND DDI.LastChangeDate IS NOT NULL

UNION ALL

-- 9. Goods Issue Posted
SELECT
    DF.PrecedingDocument AS "Vbeln",
    'Goods Issue Posted' AS "ActivityName",
    TO_TIMESTAMP(DD.ActualGoodsMovementDate || LPAD(DD.ActualGoodsMovementTime, 6, '0'), 'YYYYMMDDHH24MISS') AS "EventTime",
    DD.LastChangedByUser AS "Ernam",
    SO.SalesDocumentType AS "Auart",
    SO.SalesOrganization AS "Vkorg",
    SO.SoldToParty AS "Kunnr",
    CAST(NULL AS NVARCHAR(40)) AS "Matnr",
    CAST(NULL AS DECIMAL(15,2)) AS "Netwr",
    SO.RequestedDeliveryDate AS "Vdatu"
FROM I_DocumentFlow AS DF
JOIN I_DeliveryDocument AS DD ON DF.SubsequentDocument = DD.DeliveryDocument
JOIN SalesOrders AS SO ON DF.PrecedingDocument = SO.SalesDocument
WHERE DF.PrecedingDocumentCategory = 'C' AND DF.SubsequentDocumentCategory = 'R' AND DD.ActualGoodsMovementDate IS NOT NULL

UNION ALL

-- 10. Invoice Created
SELECT
    DF.PrecedingDocument AS "Vbeln",
    'Invoice Created' AS "ActivityName",
    TO_TIMESTAMP(BD.CreationDate || LPAD(BD.CreationTime, 6, '0'), 'YYYYMMDDHH24MISS') AS "EventTime",
    BD.CreatedByUser AS "Ernam",
    SO.SalesDocumentType AS "Auart",
    SO.SalesOrganization AS "Vkorg",
    SO.SoldToParty AS "Kunnr",
    CAST(NULL AS NVARCHAR(40)) AS "Matnr",
    CAST(NULL AS DECIMAL(15,2)) AS "Netwr",
    SO.RequestedDeliveryDate AS "Vdatu"
FROM I_DocumentFlow AS DF
JOIN I_BillingDocument AS BD ON DF.SubsequentDocument = BD.BillingDocument
JOIN SalesOrders AS SO ON DF.PrecedingDocument = SO.SalesDocument
WHERE DF.PrecedingDocumentCategory IN ('C', 'J') AND DF.SubsequentDocumentCategory = 'M'

UNION ALL

-- 11. Accounting Document Created
SELECT
    DF_SO_INV.PrecedingDocument AS "Vbeln",
    'Accounting Document Created' AS "ActivityName",
    TO_TIMESTAMP(JE.CreationDate || LPAD(JE.CreationTime, 6, '0'), 'YYYYMMDDHH24MISS') AS "EventTime",
    JE.CreatedByUser AS "Ernam",
    SO.SalesDocumentType AS "Auart",
    SO.SalesOrganization AS "Vkorg",
    SO.SoldToParty AS "Kunnr",
    CAST(NULL AS NVARCHAR(40)) AS "Matnr",
    CAST(NULL AS DECIMAL(15,2)) AS "Netwr",
    SO.RequestedDeliveryDate AS "Vdatu"
FROM I_DocumentFlow AS DF_SO_INV
JOIN I_BillingDocument BD ON DF_SO_INV.SubsequentDocument = BD.BillingDocument
JOIN I_JournalEntry JE ON BD.AccountingDocument = JE.AccountingDocument
JOIN SalesOrders AS SO ON DF_SO_INV.PrecedingDocument = SO.SalesDocument
WHERE DF_SO_INV.PrecedingDocumentCategory = 'C' AND DF_SO_INV.SubsequentDocumentCategory = 'M' AND BD.AccountingDocument IS NOT NULL

UNION ALL

-- 12. Invoice Sent to Customer (Inferred from output request processing)
SELECT 
    DF.PrecedingDocument AS "Vbeln",
    'Invoice Sent to Customer' AS "ActivityName",
    TO_TIMESTAMP(BOR.CreationDate || LPAD(BOR.CreationTime, 6, '0'), 'YYYYMMDDHH24MISS') AS "EventTime",
    BOR.CreatedByUser AS "Ernam",
    SO.SalesDocumentType AS "Auart",
    SO.SalesOrganization AS "Vkorg",
    SO.SoldToParty AS "Kunnr",
    CAST(NULL AS NVARCHAR(40)) AS "Matnr",
    CAST(NULL AS DECIMAL(15,2)) AS "Netwr",
    SO.RequestedDeliveryDate AS "Vdatu"
FROM I_DocumentFlow AS DF
JOIN I_BillingDocumentOutputRequest AS BOR ON DF.SubsequentDocument = BOR.BillingDocument
JOIN SalesOrders AS SO ON DF.PrecedingDocument = SO.SalesDocument
WHERE DF.SubsequentDocumentCategory = 'M' AND BOR.ProcessingStatus = '3' -- Successfully processed

UNION ALL

-- 13. Payment Received (Inferred from FI Clearing Document)
SELECT
    DF_SO_INV.PrecedingDocument AS "Vbeln",
    'Payment Received' AS "ActivityName",
    TO_TIMESTAMP(JEI.ClearingDate || '120000', 'YYYYMMDDHH24MISS') AS "EventTime", -- Note: Time is an approximation
    JEI.LastChangedByUser AS "Ernam",
    SO.SalesDocumentType AS "Auart",
    SO.SalesOrganization AS "Vkorg",
    SO.SoldToParty AS "Kunnr",
    CAST(NULL AS NVARCHAR(40)) AS "Matnr",
    CAST(NULL AS DECIMAL(15,2)) AS "Netwr",
    SO.RequestedDeliveryDate AS "Vdatu"
FROM I_DocumentFlow AS DF_SO_INV
JOIN I_BillingDocument BD ON DF_SO_INV.SubsequentDocument = BD.BillingDocument
JOIN I_JournalEntryItem JEI ON BD.AccountingDocument = JEI.SourceDocument
JOIN SalesOrders AS SO ON DF_SO_INV.PrecedingDocument = SO.SalesDocument
WHERE DF_SO_INV.PrecedingDocumentCategory = 'C' AND DF_SO_INV.SubsequentDocumentCategory = 'M' AND JEI.ClearingDate IS NOT NULL AND JEI.Customer IS NOT NULL

UNION ALL

-- 14. Sales Order Item Rejected
SELECT DISTINCT
    CD.OBJECTID AS "Vbeln",
    'Sales Order Item Rejected' AS "ActivityName",
    TO_TIMESTAMP(SUBSTRING(CD.EventTimestamp, 1, 8) || SUBSTRING(CD.EventTimestamp, 9, 6), 'YYYYMMDDHH24MISS') AS "EventTime",
    I_CDHDR.USERNAME AS "Ernam",
    SO.SalesDocumentType AS "Auart",
    SO.SalesOrganization AS "Vkorg",
    SO.SoldToParty AS "Kunnr",
    CAST(NULL AS NVARCHAR(40)) AS "Matnr", -- Note: Can be enhanced to find the specific item material
    CAST(NULL AS DECIMAL(15,2)) AS "Netwr",
    SO.RequestedDeliveryDate AS "Vdatu"
FROM ChangeDocs AS CD
JOIN I_ChangeDocumentHeader AS I_CDHDR ON CD.CHANGENR = I_CDHDR.CHANGENR
JOIN SalesOrders SO ON CD.OBJECTID = SO.SalesDocument
WHERE CD.TABNAME = 'VBAP' AND CD.FNAME = 'ABGRU' AND CD.VALUE_NEW <> ''

UNION ALL

-- 15. Order Closed (Inferred from Overall Status)
SELECT DISTINCT
    CD.OBJECTID AS "Vbeln",
    'Order Closed' AS "ActivityName",
    TO_TIMESTAMP(SUBSTRING(CD.EventTimestamp, 1, 8) || SUBSTRING(CD.EventTimestamp, 9, 6), 'YYYYMMDDHH24MISS') AS "EventTime",
    I_CDHDR.USERNAME AS "Ernam",
    SO.SalesDocumentType AS "Auart",
    SO.SalesOrganization AS "Vkorg",
    SO.SoldToParty AS "Kunnr",
    CAST(NULL AS NVARCHAR(40)) AS "Matnr",
    CAST(NULL AS DECIMAL(15,2)) AS "Netwr",
    SO.RequestedDeliveryDate AS "Vdatu"
FROM ChangeDocs AS CD
JOIN I_ChangeDocumentHeader AS I_CDHDR ON CD.CHANGENR = I_CDHDR.CHANGENR
JOIN SalesOrders SO ON CD.OBJECTID = SO.SalesDocument
WHERE CD.TABNAME = 'VBUK' AND CD.FNAME = 'GBSTK' AND CD.VALUE_NEW = 'C' -- Overall status = 'Completed'

/*
-- =============================================================================================
-- Process: Order to Cash - Sales Order Processing
-- System: SAP S/4HANA
-- Method: CDS View Extraction
-- Description: Extracts a detailed event log for the Order to Cash process using standard CDS views.
-- =============================================================================================
*/

WITH SalesOrders AS (
    -- Select the base set of sales orders to be analyzed.
    -- *** Set your extraction parameters here ***
    SELECT SalesDocument, CreationDate, CreationTime, LastChangeDate, SalesDocumentType, SalesOrganization, SoldToParty, LastChangedByUser, RequestedDeliveryDate
    FROM I_SalesDocument
    WHERE CreationDate BETWEEN :START_DATE AND :END_DATE -- Example: '20230101' AND '20230630'
    -- AND SalesOrganization = '[Your Sales Org]'
    -- AND SalesDocumentType = '[Your Order Type]'
),

ChangeDocs AS (
    -- Pre-select change document data for performance.
    SELECT 
        OBJECTCLAS, 
        OBJECTID, 
        CHANGENR, 
        TABNAME, 
        FNAME, 
        CHNGIND, 
        VALUE_NEW, 
        VALUE_OLD,
        CAST(CDHDR_UDATE AS VARCHAR(8)) || CAST(CDHDR_UTIME AS VARCHAR(6)) as EventTimestamp
    FROM I_ChangeDocument
    WHERE OBJECTCLAS = 'VERKBELEG' -- Sales Document Object Class
      AND OBJECTID IN (SELECT SalesDocument FROM SalesOrders)
)

-- 1. Sales Order Created
SELECT
    SD.SalesDocument AS "Vbeln",
    'Sales Order Created' AS "ActivityName",
    TO_TIMESTAMP(SD.CreationDate || LPAD(SD.CreationTime, 6, '0'), 'YYYYMMDDHH24MISS') AS "EventTime",
    SD.LastChangedByUser AS "Ernam",
    SD.SalesDocumentType AS "Auart",
    SD.SalesOrganization AS "Vkorg",
    SD.SoldToParty AS "Kunnr",
    CAST(NULL AS NVARCHAR(40)) AS "Matnr",
    CAST(NULL AS DECIMAL(15,2)) AS "Netwr",
    SD.RequestedDeliveryDate AS "Vdatu"
FROM SalesOrders AS SD

UNION ALL

-- 2. Credit Check Performed (Inferred from status change)
SELECT DISTINCT
    CD.OBJECTID AS "Vbeln",
    'Credit Check Performed' AS "ActivityName",
    TO_TIMESTAMP(SUBSTRING(CD.EventTimestamp, 1, 8) || SUBSTRING(CD.EventTimestamp, 9, 6), 'YYYYMMDDHH24MISS') AS "EventTime",
    I_CDHDR.USERNAME AS "Ernam",
    SO.SalesDocumentType AS "Auart",
    SO.SalesOrganization AS "Vkorg",
    SO.SoldToParty AS "Kunnr",
    CAST(NULL AS NVARCHAR(40)) AS "Matnr",
    CAST(NULL AS DECIMAL(15,2)) AS "Netwr",
    SO.RequestedDeliveryDate AS "Vdatu"
FROM ChangeDocs AS CD
JOIN I_ChangeDocumentHeader AS I_CDHDR ON CD.CHANGENR = I_CDHDR.CHANGENR
JOIN SalesOrders SO ON CD.OBJECTID = SO.SalesDocument
WHERE CD.TABNAME = 'VBAK' AND CD.FNAME = 'CMGST' AND CD.VALUE_NEW <> 'A' -- Status changed to something other than 'Not Performed'

UNION ALL

-- 3. Sales Order Block Set
SELECT DISTINCT
    CD.OBJECTID AS "Vbeln",
    'Sales Order Block Set' AS "ActivityName",
    TO_TIMESTAMP(SUBSTRING(CD.EventTimestamp, 1, 8) || SUBSTRING(CD.EventTimestamp, 9, 6), 'YYYYMMDDHH24MISS') AS "EventTime",
    I_CDHDR.USERNAME AS "Ernam",
    SO.SalesDocumentType AS "Auart",
    SO.SalesOrganization AS "Vkorg",
    SO.SoldToParty AS "Kunnr",
    CAST(NULL AS NVARCHAR(40)) AS "Matnr",
    CAST(NULL AS DECIMAL(15,2)) AS "Netwr",
    SO.RequestedDeliveryDate AS "Vdatu"
FROM ChangeDocs AS CD
JOIN I_ChangeDocumentHeader AS I_CDHDR ON CD.CHANGENR = I_CDHDR.CHANGENR
JOIN SalesOrders SO ON CD.OBJECTID = SO.SalesDocument
WHERE CD.TABNAME = 'VBAK' AND CD.FNAME IN ('LIFSK', 'FAKSK') AND CD.VALUE_NEW <> ''

UNION ALL

-- 4. Sales Order Block Removed
SELECT DISTINCT
    CD.OBJECTID AS "Vbeln",
    'Sales Order Block Removed' AS "ActivityName",
    TO_TIMESTAMP(SUBSTRING(CD.EventTimestamp, 1, 8) || SUBSTRING(CD.EventTimestamp, 9, 6), 'YYYYMMDDHH24MISS') AS "EventTime",
    I_CDHDR.USERNAME AS "Ernam",
    SO.SalesDocumentType AS "Auart",
    SO.SalesOrganization AS "Vkorg",
    SO.SoldToParty AS "Kunnr",
    CAST(NULL AS NVARCHAR(40)) AS "Matnr",
    CAST(NULL AS DECIMAL(15,2)) AS "Netwr",
    SO.RequestedDeliveryDate AS "Vdatu"
FROM ChangeDocs AS CD
JOIN I_ChangeDocumentHeader AS I_CDHDR ON CD.CHANGENR = I_CDHDR.CHANGENR
JOIN SalesOrders SO ON CD.OBJECTID = SO.SalesDocument
WHERE CD.TABNAME = 'VBAK' AND CD.FNAME IN ('LIFSK', 'FAKSK') AND CD.VALUE_NEW = '' AND CD.VALUE_OLD <> ''

UNION ALL

-- 5. Sales Order Changed (Significant Fields)
SELECT DISTINCT
    CD.OBJECTID AS "Vbeln",
    'Sales Order Changed' AS "ActivityName",
    TO_TIMESTAMP(SUBSTRING(CD.EventTimestamp, 1, 8) || SUBSTRING(CD.EventTimestamp, 9, 6), 'YYYYMMDDHH24MISS') AS "EventTime",
    I_CDHDR.USERNAME AS "Ernam",
    SO.SalesDocumentType AS "Auart",
    SO.SalesOrganization AS "Vkorg",
    SO.SoldToParty AS "Kunnr",
    CAST(NULL AS NVARCHAR(40)) AS "Matnr",
    CAST(NULL AS DECIMAL(15,2)) AS "Netwr",
    SO.RequestedDeliveryDate AS "Vdatu"
FROM ChangeDocs AS CD
JOIN I_ChangeDocumentHeader AS I_CDHDR ON CD.CHANGENR = I_CDHDR.CHANGENR
JOIN SalesOrders SO ON CD.OBJECTID = SO.SalesDocument
WHERE CD.TABNAME IN ('VBAP', 'VBAK') AND CD.FNAME IN ('KWMENG', 'NETWR', 'VDATU', 'KETDAT')

UNION ALL

-- 6. Sales Order Confirmed
SELECT DISTINCT
    SDI.SalesDocument AS "Vbeln",
    'Sales Order Confirmed' AS "ActivityName",
    TO_TIMESTAMP(SDS.CreationDate || '000000', 'YYYYMMDDHH24MISS') AS "EventTime",
    SD.LastChangedByUser AS "Ernam",
    SD.SalesDocumentType AS "Auart",
    SD.SalesOrganization AS "Vkorg",
    SD.SoldToParty AS "Kunnr",
    SDI.Material AS "Matnr",
    SDI.NetAmount AS "Netwr",
    SD.RequestedDeliveryDate AS "Vdatu"
FROM I_SalesDocumentScheduleLine AS SDS
JOIN I_SalesDocumentItem AS SDI ON SDS.SalesDocument = SDI.SalesDocument AND SDS.SalesDocumentItem = SDI.SalesDocumentItem
JOIN SalesOrders SD ON SDI.SalesDocument = SD.SalesDocument
WHERE SDS.ConfirmedQuantity > 0

UNION ALL

-- 7. Delivery Created
SELECT
    DF.PrecedingDocument AS "Vbeln",
    'Delivery Created' AS "ActivityName",
    TO_TIMESTAMP(DD.CreationDate || LPAD(DD.CreationTime, 6, '0'), 'YYYYMMDDHH24MISS') AS "EventTime",
    DD.CreatedByUser AS "Ernam",
    SO.SalesDocumentType AS "Auart",
    SO.SalesOrganization AS "Vkorg",
    SO.SoldToParty AS "Kunnr",
    CAST(NULL AS NVARCHAR(40)) AS "Matnr",
    CAST(NULL AS DECIMAL(15,2)) AS "Netwr",
    SO.RequestedDeliveryDate AS "Vdatu"
FROM I_DocumentFlow AS DF
JOIN I_DeliveryDocument AS DD ON DF.SubsequentDocument = DD.DeliveryDocument
JOIN SalesOrders AS SO ON DF.PrecedingDocument = SO.SalesDocument
WHERE DF.PrecedingDocumentCategory = 'C' AND DF.SubsequentDocumentCategory = 'J'

UNION ALL

-- 8. Goods Picked (Inferred from Picking Status Completion)
SELECT
    DF.PrecedingDocument AS "Vbeln",
    'Goods Picked' AS "ActivityName",
    TO_TIMESTAMP(DDI.LastChangeDate || '120000', 'YYYYMMDDHH24MISS') AS "EventTime", -- Note: Time is an approximation
    DDI.LastChangedByUser AS "Ernam",
    SO.SalesDocumentType AS "Auart",
    SO.SalesOrganization AS "Vkorg",
    SO.SoldToParty AS "Kunnr",
    DDI.Material AS "Matnr",
    CAST(NULL AS DECIMAL(15,2)) AS "Netwr",
    SO.RequestedDeliveryDate AS "Vdatu"
FROM I_DocumentFlow AS DF
JOIN I_DeliveryDocumentItem AS DDI ON DF.SubsequentDocument = DDI.DeliveryDocument
JOIN SalesOrders AS SO ON DF.PrecedingDocument = SO.SalesDocument
WHERE DF.PrecedingDocumentCategory = 'C' AND DDI.PickingStatus = 'C' AND DDI.LastChangeDate IS NOT NULL

UNION ALL

-- 9. Goods Issue Posted
SELECT
    DF.PrecedingDocument AS "Vbeln",
    'Goods Issue Posted' AS "ActivityName",
    TO_TIMESTAMP(DD.ActualGoodsMovementDate || LPAD(DD.ActualGoodsMovementTime, 6, '0'), 'YYYYMMDDHH24MISS') AS "EventTime",
    DD.LastChangedByUser AS "Ernam",
    SO.SalesDocumentType AS "Auart",
    SO.SalesOrganization AS "Vkorg",
    SO.SoldToParty AS "Kunnr",
    CAST(NULL AS NVARCHAR(40)) AS "Matnr",
    CAST(NULL AS DECIMAL(15,2)) AS "Netwr",
    SO.RequestedDeliveryDate AS "Vdatu"
FROM I_DocumentFlow AS DF
JOIN I_DeliveryDocument AS DD ON DF.SubsequentDocument = DD.DeliveryDocument
JOIN SalesOrders AS SO ON DF.PrecedingDocument = SO.SalesDocument
WHERE DF.PrecedingDocumentCategory = 'C' AND DF.SubsequentDocumentCategory = 'R' AND DD.ActualGoodsMovementDate IS NOT NULL

UNION ALL

-- 10. Invoice Created
SELECT
    DF.PrecedingDocument AS "Vbeln",
    'Invoice Created' AS "ActivityName",
    TO_TIMESTAMP(BD.CreationDate || LPAD(BD.CreationTime, 6, '0'), 'YYYYMMDDHH24MISS') AS "EventTime",
    BD.CreatedByUser AS "Ernam",
    SO.SalesDocumentType AS "Auart",
    SO.SalesOrganization AS "Vkorg",
    SO.SoldToParty AS "Kunnr",
    CAST(NULL AS NVARCHAR(40)) AS "Matnr",
    CAST(NULL AS DECIMAL(15,2)) AS "Netwr",
    SO.RequestedDeliveryDate AS "Vdatu"
FROM I_DocumentFlow AS DF
JOIN I_BillingDocument AS BD ON DF.SubsequentDocument = BD.BillingDocument
JOIN SalesOrders AS SO ON DF.PrecedingDocument = SO.SalesDocument
WHERE DF.PrecedingDocumentCategory IN ('C', 'J') AND DF.SubsequentDocumentCategory = 'M'

UNION ALL

-- 11. Accounting Document Created
SELECT
    DF_SO_INV.PrecedingDocument AS "Vbeln",
    'Accounting Document Created' AS "ActivityName",
    TO_TIMESTAMP(JE.CreationDate || LPAD(JE.CreationTime, 6, '0'), 'YYYYMMDDHH24MISS') AS "EventTime",
    JE.CreatedByUser AS "Ernam",
    SO.SalesDocumentType AS "Auart",
    SO.SalesOrganization AS "Vkorg",
    SO.SoldToParty AS "Kunnr",
    CAST(NULL AS NVARCHAR(40)) AS "Matnr",
    CAST(NULL AS DECIMAL(15,2)) AS "Netwr",
    SO.RequestedDeliveryDate AS "Vdatu"
FROM I_DocumentFlow AS DF_SO_INV
JOIN I_BillingDocument BD ON DF_SO_INV.SubsequentDocument = BD.BillingDocument
JOIN I_JournalEntry JE ON BD.AccountingDocument = JE.AccountingDocument
JOIN SalesOrders AS SO ON DF_SO_INV.PrecedingDocument = SO.SalesDocument
WHERE DF_SO_INV.PrecedingDocumentCategory = 'C' AND DF_SO_INV.SubsequentDocumentCategory = 'M' AND BD.AccountingDocument IS NOT NULL

UNION ALL

-- 12. Invoice Sent to Customer (Inferred from output request processing)
SELECT 
    DF.PrecedingDocument AS "Vbeln",
    'Invoice Sent to Customer' AS "ActivityName",
    TO_TIMESTAMP(BOR.CreationDate || LPAD(BOR.CreationTime, 6, '0'), 'YYYYMMDDHH24MISS') AS "EventTime",
    BOR.CreatedByUser AS "Ernam",
    SO.SalesDocumentType AS "Auart",
    SO.SalesOrganization AS "Vkorg",
    SO.SoldToParty AS "Kunnr",
    CAST(NULL AS NVARCHAR(40)) AS "Matnr",
    CAST(NULL AS DECIMAL(15,2)) AS "Netwr",
    SO.RequestedDeliveryDate AS "Vdatu"
FROM I_DocumentFlow AS DF
JOIN I_BillingDocumentOutputRequest AS BOR ON DF.SubsequentDocument = BOR.BillingDocument
JOIN SalesOrders AS SO ON DF.PrecedingDocument = SO.SalesDocument
WHERE DF.SubsequentDocumentCategory = 'M' AND BOR.ProcessingStatus = '3' -- Successfully processed

UNION ALL

-- 13. Payment Received (Inferred from FI Clearing Document)
SELECT
    DF_SO_INV.PrecedingDocument AS "Vbeln",
    'Payment Received' AS "ActivityName",
    TO_TIMESTAMP(JEI.ClearingDate || '120000', 'YYYYMMDDHH24MISS') AS "EventTime", -- Note: Time is an approximation
    JEI.LastChangedByUser AS "Ernam",
    SO.SalesDocumentType AS "Auart",
    SO.SalesOrganization AS "Vkorg",
    SO.SoldToParty AS "Kunnr",
    CAST(NULL AS NVARCHAR(40)) AS "Matnr",
    CAST(NULL AS DECIMAL(15,2)) AS "Netwr",
    SO.RequestedDeliveryDate AS "Vdatu"
FROM I_DocumentFlow AS DF_SO_INV
JOIN I_BillingDocument BD ON DF_SO_INV.SubsequentDocument = BD.BillingDocument
JOIN I_JournalEntryItem JEI ON BD.AccountingDocument = JEI.SourceDocument
JOIN SalesOrders AS SO ON DF_SO_INV.PrecedingDocument = SO.SalesDocument
WHERE DF_SO_INV.PrecedingDocumentCategory = 'C' AND DF_SO_INV.SubsequentDocumentCategory = 'M' AND JEI.ClearingDate IS NOT NULL AND JEI.Customer IS NOT NULL

UNION ALL

-- 14. Sales Order Item Rejected
SELECT DISTINCT
    CD.OBJECTID AS "Vbeln",
    'Sales Order Item Rejected' AS "ActivityName",
    TO_TIMESTAMP(SUBSTRING(CD.EventTimestamp, 1, 8) || SUBSTRING(CD.EventTimestamp, 9, 6), 'YYYYMMDDHH24MISS') AS "EventTime",
    I_CDHDR.USERNAME AS "Ernam",
    SO.SalesDocumentType AS "Auart",
    SO.SalesOrganization AS "Vkorg",
    SO.SoldToParty AS "Kunnr",
    CAST(NULL AS NVARCHAR(40)) AS "Matnr", -- Note: Can be enhanced to find the specific item material
    CAST(NULL AS DECIMAL(15,2)) AS "Netwr",
    SO.RequestedDeliveryDate AS "Vdatu"
FROM ChangeDocs AS CD
JOIN I_ChangeDocumentHeader AS I_CDHDR ON CD.CHANGENR = I_CDHDR.CHANGENR
JOIN SalesOrders SO ON CD.OBJECTID = SO.SalesDocument
WHERE CD.TABNAME = 'VBAP' AND CD.FNAME = 'ABGRU' AND CD.VALUE_NEW <> ''

UNION ALL

-- 15. Order Closed (Inferred from Overall Status)
SELECT DISTINCT
    CD.OBJECTID AS "Vbeln",
    'Order Closed' AS "ActivityName",
    TO_TIMESTAMP(SUBSTRING(CD.EventTimestamp, 1, 8) || SUBSTRING(CD.EventTimestamp, 9, 6), 'YYYYMMDDHH24MISS') AS "EventTime",
    I_CDHDR.USERNAME AS "Ernam",
    SO.SalesDocumentType AS "Auart",
    SO.SalesOrganization AS "Vkorg",
    SO.SoldToParty AS "Kunnr",
    CAST(NULL AS NVARCHAR(40)) AS "Matnr",
    CAST(NULL AS DECIMAL(15,2)) AS "Netwr",
    SO.RequestedDeliveryDate AS "Vdatu"
FROM ChangeDocs AS CD
JOIN I_ChangeDocumentHeader AS I_CDHDR ON CD.CHANGENR = I_CDHDR.CHANGENR
JOIN SalesOrders SO ON CD.OBJECTID = SO.SalesDocument
WHERE CD.TABNAME = 'VBUK' AND CD.FNAME = 'GBSTK' AND CD.VALUE_NEW = 'C' -- Overall status = 'Completed'

import pandas as pd
from pyrfc import Connection, ABAPApplicationError, LogonError
from datetime import datetime

class=class="hl-string">"hl-comment"># --- 1. Configuration: Update with your SAP system details and filters ---
sap_config = {
    class="hl-string">"ashost": class="hl-string">"[your_sap_host]",
    class="hl-string">"sysnr": class="hl-string">"[your_system_number]",
    class="hl-string">"client": class="hl-string">"[your_client]",
    class="hl-string">"user": class="hl-string">"[your_rfc_user]",
    class="hl-string">"passwd": class="hl-string">"[your_password]",
}

filter_params = {
    class="hl-string">"date_from": class="hl-string">"YYYYMMDD",  class=class="hl-string">"hl-comment"># Example: class="hl-string">"20230101"
    class="hl-string">"date_to": class="hl-string">"YYYYMMDD",    class=class="hl-string">"hl-comment"># Example: class="hl-string">"20230630"
    class="hl-string">"sales_orgs": [class="hl-string">"[your_sales_org]"], class=class="hl-string">"hl-comment"># Example: [class="hl-string">"1000", class="hl-string">"1010"]
    class="hl-string">"doc_types": [class="hl-string">"[your_doc_type]"]    class=class="hl-string">"hl-comment"># Example: [class="hl-string">"OR"]
}

class=class="hl-string">"hl-comment"># --- Helper function to query SAP tables ---
def query_sap_table(conn, table_name, fields, options):
    try:
        result = conn.call(class="hl-string">'RFC_READ_TABLE', QUERY_TABLE=table_name, DELIMITER=class="hl-string">'|', FIELDS=fields, OPTIONS=options)
        data = [dict(zip(fields, row[class="hl-string">'WA'].strip().split(class="hl-string">'|'))) for row in result[class="hl-string">'DATA']]
        return data
    except ABAPApplicationError as e:
        print(fclass="hl-string">"Error querying table {table_name}: {e}")
        return []

class=class="hl-string">"hl-comment"># --- Main Extraction Logic ---
def main():
    event_log = []

    try:
        print(class="hl-string">"Connecting to SAP...")
        conn = Connection(**sap_config)
        print(class="hl-string">"Connection successful.")

        class=class="hl-string">"hl-comment"># --- 2. Get list of sales orders based on filters ---
        print(class="hl-string">"Fetching sales orders...")
        vbak_options = [
            fclass="hl-string">"ERDAT GE '{filter_params['date_from']}'",
            fclass="hl-string">"AND ERDAT LE '{filter_params['date_to']}'",
            fclass="hl-string">"AND VKORG IN ({','.join([f\"class="hl-string">'{so}'\class="hl-string">" for so in filter_params['sales_orgs']])})",
            fclass="hl-string">"AND AUART IN ({','.join([f\"class="hl-string">'{dt}'\class="hl-string">" for dt in filter_params['doc_types']])})"
        ]
        sales_orders = query_sap_table(conn, class="hl-string">'VBAK', [class="hl-string">'VBELN'], vbak_options)
        
        if not sales_orders:
            print(class="hl-string">"No sales orders found for the given criteria.")
            return

        print(fclass="hl-string">"Found {len(sales_orders)} sales orders to process.")

        class=class="hl-string">"hl-comment"># --- 3. Loop through each sales order to extract events ---
        for i, so in enumerate(sales_orders):
            vbeln = so[class="hl-string">'VBELN']
            print(fclass="hl-string">"Processing SO {vbeln} ({i+1}/{len(sales_orders)})... ")

            class=class="hl-string">"hl-comment"># Fetch common data for this SO
            vbak_data = query_sap_table(conn, class="hl-string">'VBAK', [class="hl-string">'ERDAT', class="hl-string">'ERZET', class="hl-string">'ERNAM', class="hl-string">'AUART', class="hl-string">'VKORG', class="hl-string">'KUNNR', class="hl-string">'VDATU'], [fclass="hl-string">"VBELN = '{vbeln}'"])[0]
            vbap_data = query_sap_table(conn, class="hl-string">'VBAP', [class="hl-string">'MATNR', class="hl-string">'NETWR'], [fclass="hl-string">"VBELN = '{vbeln}'"])
            doc_flow = query_sap_table(conn, class="hl-string">'VBFA', [class="hl-string">'VBELN', class="hl-string">'POSNN', class="hl-string">'VBTYP_N', class="hl-string">'ERDAT', class="hl-string">'ERZET'], [fclass="hl-string">"VBELV = '{vbeln}'"])
            cdhdr_data = query_sap_table(conn, class="hl-string">'CDHDR', [class="hl-string">'CHANGENR', class="hl-string">'UDATE', class="hl-string">'UTIME'], [fclass="hl-string">"OBJECTCLAS = 'VERKBELEG' AND OBJECTID = '{vbeln}'"])
            
            change_numbers = [c[class="hl-string">'CHANGENR'] for c in cdhdr_data]
            cdpos_data = []
            if change_numbers:
                cdpos_options = [fclass="hl-string">"OBJECTCLAS = 'VERKBELEG' AND OBJECTID = '{vbeln}' AND CHANGENR IN ({','.join([f\"class="hl-string">'{cn}'\class="hl-string">" for cn in change_numbers])})"]
                cdpos_data = query_sap_table(conn, class="hl-string">'CDPOS', [class="hl-string">'CHANGENR', class="hl-string">'TABNAME', class="hl-string">'TABKEY', class="hl-string">'FNAME', class="hl-string">'VALUE_NEW', class="hl-string">'VALUE_OLD'], cdpos_options)

            class=class="hl-string">"hl-comment"># Helper to create a base event dictionary
            def create_event(activity, timestamp_str, user=vbak_data[class="hl-string">'ERNAM']):
                return {
                    class="hl-string">'Vbeln': vbeln,
                    class="hl-string">'ActivityName': activity,
                    class="hl-string">'EventTime': timestamp_str,
                    class="hl-string">'Ernam': user,
                    class="hl-string">'Auart': vbak_data[class="hl-string">'AUART'],
                    class="hl-string">'Vkorg': vbak_data[class="hl-string">'VKORG'],
                    class="hl-string">'Kunnr': vbak_data[class="hl-string">'KUNNR'],
                    class="hl-string">'Matnr': class="hl-string">",".join(set(item[class="hl-string">'MATNR'] for item in vbap_data if class="hl-string">'MATNR' in item)),
                    class="hl-string">'Netwr': sum(float(item[class="hl-string">'NETWR']) for item in vbap_data if item.get(class="hl-string">'NETWR') and item[class="hl-string">'NETWR'].strip()),
                    class="hl-string">'Vdatu': vbak_data[class="hl-string">'VDATU']
                }

            class=class="hl-string">"hl-comment"># --- Activity: Sales Order Created ---
            created_ts = fclass="hl-string">"{vbak_data['ERDAT']} {vbak_data['ERZET']}"
            event_log.append(create_event(class="hl-string">'Sales Order Created', datetime.strptime(created_ts, class="hl-string">'%Y%m%d %H%M%S').strftime(class="hl-string">'%Y-%m-%d %H:%M:%S')))

            class=class="hl-string">"hl-comment"># --- Activities from Change Logs (CDHDR/CDPOS) ---
            change_log_map = {c[class="hl-string">'CHANGENR']: c for c in cdhdr_data}
            for change in cdpos_data:
                change_header = change_log_map.get(change[class="hl-string">'CHANGENR'])
                if not change_header: continue
                
                ts_str = fclass="hl-string">"{change_header['UDATE']} {change_header['UTIME']}"
                event_ts = datetime.strptime(ts_str, class="hl-string">'%Y%m%d %H%M%S').strftime(class="hl-string">'%Y-%m-%d %H:%M:%S')

                class=class="hl-string">"hl-comment"># Sales Order Block Set/Removed
                if change[class="hl-string">'FNAME'] in [class="hl-string">'FAKSK', class="hl-string">'LIFSK']:
                    if change[class="hl-string">'VALUE_NEW'].strip():
                        event_log.append(create_event(class="hl-string">'Sales Order Block Set', event_ts))
                    else:
                        event_log.append(create_event(class="hl-string">'Sales Order Block Removed', event_ts))
                class=class="hl-string">"hl-comment"># Credit Check Performed (status change)
                if change[class="hl-string">'TABNAME'] == class="hl-string">'VBUK' and change[class="hl-string">'FNAME'] == class="hl-string">'CMGST' and change[class="hl-string">'VALUE_NEW'].strip() in [class="hl-string">'B', class="hl-string">'C', class="hl-string">'D']:
                     event_log.append(create_event(class="hl-string">'Credit Check Performed', event_ts))
                class=class="hl-string">"hl-comment"># Sales Order Changed (e.g., quantity)
                if change[class="hl-string">'FNAME'] == class="hl-string">'KWMENG':
                    event_log.append(create_event(class="hl-string">'Sales Order Changed', event_ts))
                class=class="hl-string">"hl-comment"># Sales Order Item Rejected
                if change[class="hl-string">'FNAME'] == class="hl-string">'ABGRU' and change[class="hl-string">'VALUE_NEW'].strip():
                    event_log.append(create_event(class="hl-string">'Sales Order Item Rejected', event_ts))
                class=class="hl-string">"hl-comment"># Order Closed
                if change[class="hl-string">'TABNAME'] == class="hl-string">'VBUK' and change[class="hl-string">'FNAME'] == class="hl-string">'GBSTK' and change[class="hl-string">'VALUE_NEW'].strip() == class="hl-string">'C':
                    event_log.append(create_event(class="hl-string">'Order Closed', event_ts))

            class=class="hl-string">"hl-comment"># --- Activity: Sales Order Confirmed ---
            vbep_data = query_sap_table(conn, class="hl-string">'VBEP', [class="hl-string">'EDATU', class="hl-string">'EZEIT'], [fclass="hl-string">"VBELN = '{vbeln}' AND BMENG > 0"])
            if vbep_data:
                confirmed_item = vbep_data[0] class=class="hl-string">"hl-comment"># Take the first confirmed schedule line
                ts_str = fclass="hl-string">"{confirmed_item['EDATU']} {confirmed_item['EZEIT']}"
                event_ts = datetime.strptime(ts_str, class="hl-string">'%Y%m%d %H%M%S').strftime(class="hl-string">'%Y-%m-%d %H:%M:%S')
                event_log.append(create_event(class="hl-string">'Sales Order Confirmed', event_ts))

            class=class="hl-string">"hl-comment"># --- Activities from Document Flow (VBFA) ---
            delivery_docs = [d for d in doc_flow if d[class="hl-string">'VBTYP_N'] == class="hl-string">'J']
            invoice_docs = [d for d in doc_flow if d[class="hl-string">'VBTYP_N'] == class="hl-string">'M']

            if delivery_docs:
                delivery_vbeln = delivery_docs[0][class="hl-string">'VBELN']
                class=class="hl-string">"hl-comment"># Activity: Delivery Created
                likp_data = query_sap_table(conn, class="hl-string">'LIKP', [class="hl-string">'ERDAT', class="hl-string">'ERZET'], [fclass="hl-string">"VBELN = '{delivery_vbeln}'"])
                if likp_data:
                    ts_str = fclass="hl-string">"{likp_data[0]['ERDAT']} {likp_data[0]['ERZET']}"
                    event_ts = datetime.strptime(ts_str, class="hl-string">'%Y%m%d %H%M%S').strftime(class="hl-string">'%Y-%m-%d %H:%M:%S')
                    event_log.append(create_event(class="hl-string">'Delivery Created', event_ts))

                class=class="hl-string">"hl-comment"># Activity: Goods Picked (inferred from picking status on delivery change log)
                del_cdhdr = query_sap_table(conn, class="hl-string">'CDHDR', [class="hl-string">'CHANGENR', class="hl-string">'UDATE', class="hl-string">'UTIME'], [fclass="hl-string">"OBJECTCLAS = 'LIEFERUNG' AND OBJECTID = '{delivery_vbeln}'"])
                if del_cdhdr:
                    del_cnrs = [c[class="hl-string">'CHANGENR'] for c in del_cdhdr]
                    del_cdpos = query_sap_table(conn, class="hl-string">'CDPOS', [class="hl-string">'CHANGENR', class="hl-string">'FNAME', class="hl-string">'VALUE_NEW'], [fclass="hl-string">"OBJECTCLAS = 'LIEFERUNG' AND OBJECTID = '{delivery_vbeln}' AND FNAME = 'KOSTA' AND VALUE_NEW = 'C' AND CHANGENR IN ({','.join([f\"class="hl-string">'{cn}'\class="hl-string">" for cn in del_cnrs])})"])
                    if del_cdpos:
                        change_header = [c for c in del_cdhdr if c[class="hl-string">'CHANGENR'] == del_cdpos[0][class="hl-string">'CHANGENR']][0]
                        ts_str = fclass="hl-string">"{change_header['UDATE']} {change_header['UTIME']}"
                        event_ts = datetime.strptime(ts_str, class="hl-string">'%Y%m%d %H%M%S').strftime(class="hl-string">'%Y-%m-%d %H:%M:%S')
                        event_log.append(create_event(class="hl-string">'Goods Picked', event_ts))

                class=class="hl-string">"hl-comment"># Activity: Goods Issue Posted
                gi_doc = [d for d in doc_flow if d[class="hl-string">'VBTYP_N'] == class="hl-string">'R' and d[class="hl-string">'VBELN'] == delivery_vbeln]
                if gi_doc:
                    ts_str = fclass="hl-string">"{gi_doc[0]['ERDAT']} {gi_doc[0]['ERZET']}"
                    event_ts = datetime.strptime(ts_str, class="hl-string">'%Y%m%d %H%M%S').strftime(class="hl-string">'%Y-%m-%d %H:%M:%S')
                    event_log.append(create_event(class="hl-string">'Goods Issue Posted', event_ts))

            if invoice_docs:
                invoice_vbeln = invoice_docs[0][class="hl-string">'VBELN']
                class=class="hl-string">"hl-comment"># Activity: Invoice Created
                vbrk_data = query_sap_table(conn, class="hl-string">'VBRK', [class="hl-string">'ERDAT', class="hl-string">'ERZET'], [fclass="hl-string">"VBELN = '{invoice_vbeln}'"])
                if vbrk_data:
                    ts_str = fclass="hl-string">"{vbrk_data[0]['ERDAT']} {vbrk_data[0]['ERZET']}"
                    event_ts = datetime.strptime(ts_str, class="hl-string">'%Y%m%d %H%M%S').strftime(class="hl-string">'%Y-%m-%d %H:%M:%S')
                    event_log.append(create_event(class="hl-string">'Invoice Created', event_ts))

                    class=class="hl-string">"hl-comment"># Activity: Accounting Document Created
                    bkpf_data = query_sap_table(conn, class="hl-string">'BKPF', [class="hl-string">'BELNR', class="hl-string">'CPUDT', class="hl-string">'CPUTM'], [fclass="hl-string">"AWKEY LIKE '{invoice_vbeln}%' AND AWTYP = 'VBRK'"])
                    if bkpf_data:
                        ac_doc_belnr = bkpf_data[0][class="hl-string">'BELNR']
                        ts_str = fclass="hl-string">"{bkpf_data[0]['CPUDT']} {bkpf_data[0]['CPUTM']}"
                        event_ts = datetime.strptime(ts_str, class="hl-string">'%Y%m%d %H%M%S').strftime(class="hl-string">'%Y-%m-%d %H:%M:%S')
                        event_log.append(create_event(class="hl-string">'Accounting Document Created', event_ts))

                        class=class="hl-string">"hl-comment"># Activity: Payment Received
                        bsad_data = query_sap_table(conn, class="hl-string">'BSAD', [class="hl-string">'AUGDT'], [fclass="hl-string">"BELNR = '{ac_doc_belnr}' AND KUNNR = '{vbak_data['KUNNR']}'"])
                        if bsad_data:
                            event_ts = datetime.strptime(bsad_data[0][class="hl-string">'AUGDT'], class="hl-string">'%Y%m%d').strftime(class="hl-string">'%Y-%m-%d %H:%M:%S')
                            event_log.append(create_event(class="hl-string">'Payment Received', event_ts))

                class=class="hl-string">"hl-comment"># Activity: Invoice Sent to Customer
                nast_data = query_sap_table(conn, class="hl-string">'NAST', [class="hl-string">'DATVR', class="hl-string">'UHRVR'], [fclass="hl-string">"OBJKY = '{invoice_vbeln}' AND VSTAT = '1' AND KSCHL LIKE '[your_output_type]'"])
                if nast_data and nast_data[0][class="hl-string">'DATVR'] != class="hl-string">'00000000':
                    ts_str = fclass="hl-string">"{nast_data[0]['DATVR']} {nast_data[0]['UHRVR']}"
                    event_ts = datetime.strptime(ts_str, class="hl-string">'%Y%m%d %H%M%S').strftime(class="hl-string">'%Y-%m-%d %H:%M:%S')
                    event_log.append(create_event(class="hl-string">'Invoice Sent to Customer', event_ts))

        conn.close()
        print(class="hl-string">"Connection closed.")

        class=class="hl-string">"hl-comment"># --- 4. Create and save the event log ---
        if event_log:
            print(class="hl-string">"Creating DataFrame and saving to CSV...")
            df = pd.DataFrame(event_log)
            df[class="hl-string">'EventTime'] = pd.to_datetime(df[class="hl-string">'EventTime'])
            df = df.sort_values(by=[class="hl-string">'Vbeln', class="hl-string">'EventTime'])
            df.to_csv(class="hl-string">'sales_order_event_log.csv', index=False)
            print(class="hl-string">"Extraction complete. File 'sales_order_event_log.csv' created.")
        else:
            print(class="hl-string">"No events were generated.")

    except (LogonError, ABAPApplicationError) as e:
        print(fclass="hl-string">"An SAP error occurred: {e}")

if __name__ == class="hl-string">"__main__":
    main()

REPORT Z_PM_O2C_EXTRACTOR.

" ====================================================================
" 1. SELECTION SCREEN
" ====================================================================
SELECT-OPTIONS: s_erdat FOR vbak-erdat OBLIGATORY, " Creation Date
                  s_vkorg FOR vbak-vkorg, " Sales Organization
                  s_auart FOR vbak-auart. " Sales Document Type

PARAMETERS: p_file TYPE string DEFAULT '/usr/sap/trans/tmp/o2c_extract.csv'.

" ====================================================================
" 2. DATA DECLARATIONS
" ====================================================================
TYPES: BEGIN OF ty_event_log,
         vbeln TYPE vbeln_va, " Sales Order
         activityname TYPE char50,
         eventtime TYPE string,
         ernam TYPE ernam,
         auart TYPE auart,
         vkorg TYPE vkorg,
         kunnr TYPE kunnr,
         matnr TYPE matnr,
         netwr TYPE netwr_ak,
         vdatu TYPE vdatu,
       END OF ty_event_log.

DATA: lt_event_log TYPE TABLE OF ty_event_log,
      ls_event_log LIKE LINE OF lt_event_log.

DATA: lt_vbak TYPE TABLE OF vbak,
      lt_vbap TYPE TABLE OF vbap.

" ====================================================================
" 3. MAIN PROGRAM LOGIC
" ====================================================================
START-OF-SELECTION.

  " --- Get Base Sales Orders ---
  SELECT * FROM vbak INTO TABLE lt_vbak
    WHERE erdat IN s_erdat
    AND vkorg IN s_vkorg
    AND auart IN s_auart.

  IF lt_vbak IS NOT INITIAL.
    SELECT * FROM vbap INTO TABLE lt_vbap
      FOR ALL ENTRIES IN lt_vbak
      WHERE vbeln = lt_vbak-vbeln.
  END IF.

  " --- Loop through base data to extract events ---
  LOOP AT lt_vbak ASSIGNING FIELD-SYMBOL(<fs_vbak>).
    DATA(lv_vbeln) = <fs_vbak>-vbeln.

    " --- 1. Sales Order Created ---
    ls_event_log-vbeln        = lv_vbeln.
    ls_event_log-activityname = 'Sales Order Created'.
    CONCATENATE <fs_vbak>-erdat <fs_vbak>-erzet INTO ls_event_log-eventtime.
    ls_event_log-ernam        = <fs_vbak>-ernam.
    ls_event_log-auart        = <fs_vbak>-auart.
    ls_event_log-vkorg        = <fs_vbak>-vkorg.
    ls_event_log-kunnr        = <fs_vbak>-kunnr.
    ls_event_log-netwr        = <fs_vbak>-netwr.
    ls_event_log-vdatu        = <fs_vbak>-vdatu.
    CLEAR ls_event_log-matnr.
    APPEND ls_event_log TO lt_event_log.

    " --- Extract other events using SELECTs inside the loop ---
    " NOTE: In a real-world scenario, you would optimize by selecting data
    " in bulk before the loop. This simplified example uses selects inside
    " the loop for clarity on a per-activity basis.

    " --- 2. Credit Check Performed (Example using CDHDR/CDPOS on Status) ---
    SELECT h~udate, h~utime, h~username
      FROM cdhdr AS h INNER JOIN cdpos AS p ON h~changenr = p~changenr
      INTO TABLE @DATA(lt_credit)
      WHERE h~objectclas = 'VERKBELEG'
      AND h~objectid = @lv_vbeln
      AND p~tabname = 'VBUK'
      AND p~fname = 'CMGST'
      AND p~value_new <> p~value_old.
    LOOP AT lt_credit INTO DATA(ls_credit).
      ls_event_log-activityname = 'Credit Check Performed'.
      CONCATENATE ls_credit-udate ls_credit-utime INTO ls_event_log-eventtime.
      ls_event_log-ernam        = ls_credit-username.
      APPEND ls_event_log TO lt_event_log.
    ENDLOOP.

    " --- 3. & 4. Sales Order Block Set / Removed (Example for Delivery Block) ---
    SELECT h~udate, h~utime, h~username, p~value_new, p~value_old
      FROM cdhdr AS h INNER JOIN cdpos AS p ON h~changenr = p~changenr
      INTO TABLE @DATA(lt_block)
      WHERE h~objectclas = 'VERKBELEG'
      AND h~objectid = @lv_vbeln
      AND p~tabname = 'VBAK'
      AND p~fname = 'LIFSK'.
    LOOP AT lt_block INTO DATA(ls_block).
      IF ls_block-value_new IS NOT INITIAL AND ls_block-value_old IS INITIAL.
        ls_event_log-activityname = 'Sales Order Block Set'.
      ELSEIF ls_block-value_new IS INITIAL AND ls_block-value_old IS NOT INITIAL.
        ls_event_log-activityname = 'Sales Order Block Removed'.
      ELSE.
        CONTINUE.
      ENDIF.
      CONCATENATE ls_block-udate ls_block-utime INTO ls_event_log-eventtime.
      ls_event_log-ernam        = ls_block-username.
      APPEND ls_event_log TO lt_event_log.
    ENDLOOP.

    " --- 5. Sales Order Changed (Example for Item Quantity Change) ---
    SELECT h~udate, h~utime, h~username
      FROM cdhdr AS h INNER JOIN cdpos AS p ON h~changenr = p~changenr
      INTO TABLE @DATA(lt_change)
      WHERE h~objectclas = 'VERKBELEG'
      AND h~objectid = @lv_vbeln
      AND p~tabname = 'VBAP'
      AND p~fname = 'KWMENG'.
    LOOP AT lt_change INTO DATA(ls_change).
      ls_event_log-activityname = 'Sales Order Changed'.
      CONCATENATE ls_change-udate ls_change-utime INTO ls_event_log-eventtime.
      ls_event_log-ernam        = ls_change-username.
      APPEND ls_event_log TO lt_event_log.
    ENDLOOP.

    " --- 6. Sales Order Confirmed (Based on Schedule Line Creation) ---
    SELECT erdat, erzet, ernam FROM vbep INTO TABLE @DATA(lt_conf)
      WHERE vbeln = @lv_vbeln AND bmeng > 0.
    LOOP AT lt_conf INTO DATA(ls_conf).
      ls_event_log-activityname = 'Sales Order Confirmed'.
      CONCATENATE ls_conf-erdat ls_conf-erzet INTO ls_event_log-eventtime.
      ls_event_log-ernam        = ls_conf-ernam.
      APPEND ls_event_log TO lt_event_log.
    ENDLOOP.

    " --- 7. Delivery Created ---
    SELECT DISTINCT b~erdat, b~erzet, b~ernam
      FROM lips AS a INNER JOIN likp AS b ON a~vbeln = b~vbeln
      INTO TABLE @DATA(lt_deliv)
      WHERE a~vgbel = @lv_vbeln.
    LOOP AT lt_deliv INTO DATA(ls_deliv).
      ls_event_log-activityname = 'Delivery Created'.
      CONCATENATE ls_deliv-erdat ls_deliv-erzet INTO ls_event_log-eventtime.
      ls_event_log-ernam        = ls_deliv-ernam.
      APPEND ls_event_log TO lt_event_log.
    ENDLOOP.

    " --- 8. & 9. Goods Picked & Goods Issue Posted ---
    SELECT a~vbeln, b~wadat_ist, b~wazei_ist, b~ernam
      FROM lips AS a INNER JOIN likp AS b ON a~vbeln = b~vbeln
      INTO TABLE @DATA(lt_gi)
      WHERE a~vgbel = @lv_vbeln.
    LOOP AT lt_gi INTO DATA(ls_gi).
      " Check change logs for Picking status on LIPS-KOSTA going to 'C'
      " ... (logic similar to blocks) ... Append 'Goods Picked' event
      IF ls_gi-wadat_ist IS NOT INITIAL.
        ls_event_log-activityname = 'Goods Issue Posted'.
        CONCATENATE ls_gi-wadat_ist ls_gi-wazei_ist INTO ls_event_log-eventtime.
        ls_event_log-ernam        = ls_gi-ernam.
        APPEND ls_event_log TO lt_event_log.
      ENDIF.
    ENDLOOP.

    " --- 10. Invoice Created ---
    SELECT DISTINCT b~erdat, b~erzet, b~ernam, b~belnr
      FROM vbrp AS a INNER JOIN vbrk AS b ON a~vbeln = b~vbeln
      INTO TABLE @DATA(lt_inv)
      WHERE a~aubel = @lv_vbeln.
    LOOP AT lt_inv INTO DATA(ls_inv).
      ls_event_log-activityname = 'Invoice Created'.
      CONCATENATE ls_inv-erdat ls_inv-erzet INTO ls_event_log-eventtime.
      ls_event_log-ernam        = ls_inv-ernam.
      APPEND ls_event_log TO lt_event_log.

      " --- 11. Accounting Document Created ---
      SELECT cpudt, cputm, usnam FROM bkpf INTO @DATA(ls_bkpf)
        WHERE awkey = @ls_inv-belnr AND awsys = @sy-sysid.
      IF sy-subrc = 0.
        ls_event_log-activityname = 'Accounting Document Created'.
        CONCATENATE ls_bkpf-cpudt ls_bkpf-cputm INTO ls_event_log-eventtime.
        ls_event_log-ernam        = ls_bkpf-usnam.
        APPEND ls_event_log TO lt_event_log.

        " --- 13. Payment Received ---
        SELECT augdt FROM bsad INTO @DATA(lv_augdt)
          WHERE belnr = @ls_inv-belnr AND bukrs = @<fs_vbak>-bukrs.
        IF sy-subrc = 0 AND lv_augdt IS NOT INITIAL.
          ls_event_log-activityname = 'Payment Received'.
          CONCATENATE lv_augdt '000000' INTO ls_event_log-eventtime.
          ls_event_log-ernam        = ''. " Clearing user often not relevant
          APPEND ls_event_log TO lt_event_log.
        ENDIF.
      ENDIF.
    ENDLOOP.

    " --- 12. Invoice Sent to Customer (from NAST) ---
    SELECT erdat, erzet FROM nast INTO TABLE @DATA(lt_nast)
      WHERE kappl = 'V3' AND objky = @lv_vbeln AND vstat = '1'.
    LOOP AT lt_nast INTO DATA(ls_nast).
      ls_event_log-activityname = 'Invoice Sent to Customer'.
      CONCATENATE ls_nast-erdat ls_nast-erzet INTO ls_event_log-eventtime.
      ls_event_log-ernam        = ''.
      APPEND ls_event_log TO lt_event_log.
    ENDLOOP.

    " --- 14. Sales Order Item Rejected ---
    SELECT h~udate, h~utime, h~username
      FROM cdhdr AS h INNER JOIN cdpos AS p ON h~changenr = p~changenr
      INTO TABLE @DATA(lt_reject)
      WHERE h~objectclas = 'VERKBELEG'
      AND h~objectid = @lv_vbeln
      AND p~tabname = 'VBAP'
      AND p~fname = 'ABGRU'
      AND p~value_new IS NOT INITIAL.
    LOOP AT lt_reject INTO DATA(ls_reject).
      ls_event_log-activityname = 'Sales Order Item Rejected'.
      CONCATENATE ls_reject-udate ls_reject-utime INTO ls_event_log-eventtime.
      ls_event_log-ernam        = ls_reject-username.
      APPEND ls_event_log TO lt_event_log.
    ENDLOOP.

    " --- 15. Order Closed ---
    SELECT h~udate, h~utime, h~username
      FROM cdhdr AS h INNER JOIN cdpos AS p ON h~changenr = p~changenr
      INTO TABLE @DATA(lt_closed)
      WHERE h~objectclas = 'VERKBELEG'
      AND h~objectid = @lv_vbeln
      AND p~tabname = 'VBUK'
      AND p~fname = 'GBSTK'
      AND p~value_new = 'C'.
    LOOP AT lt_closed INTO DATA(ls_closed).
      ls_event_log-activityname = 'Order Closed'.
      CONCATENATE ls_closed-udate ls_closed-utime INTO ls_event_log-eventtime.
      ls_event_log-ernam        = ls_closed-username.
      APPEND ls_event_log TO lt_event_log.
    ENDLOOP.

  ENDLOOP.

  " --- Write to file ---
  OPEN DATASET p_file FOR OUTPUT IN TEXT MODE ENCODING UTF-8.
  IF sy-subrc = 0.
    " Header Row
    DATA(lv_header) = 'SalesOrder,ActivityName,EventTime,UserName,DocumentType,SalesOrg,Customer,Material,NetAmount,RequestedDeliveryDate'.
    TRANSFER lv_header TO p_file.

    LOOP AT lt_event_log INTO ls_event_log.
      " Create CSV line
      CONCATENATE ls_event_log-vbeln
                  ls_event_log-activityname
                  ls_event_log-eventtime
                  ls_event_log-ernam
                  ls_event_log-auart
                  ls_event_log-vkorg
                  ls_event_log-kunnr
                  ls_event_log-matnr
                  ls_event_log-netwr
                  ls_event_log-vdatu
        INTO DATA(lv_line) SEPARATED BY ','.
      TRANSFER lv_line TO p_file.
    ENDLOOP.
    CLOSE DATASET p_file.
  ENDIF.