与信管理および債権回収データテンプレート

/*
NOTE: SAP Query (SQVI/SQ01) is a visual tool and does not use a single script. 
The following ABAP SQL code is a logical representation of the data that must be extracted
across multiple, separate queries which are then combined manually. 
This script consolidates the logic for all 14 activities into one block for clarity.
*/

SELECT
    bseg~belnr AS InvoiceNumber,
    'Invoice Posted' AS ActivityName,
    bkpf~cpudt AS EventTime,
    bkpf~usnam AS UserName,
    bkpf~kunnr AS Kunnr,
    kna1~ktokd AS CustomerSegment,
    bseg~dmbtr AS Dmbtr,
    bseg~zfbdt AS NetDueDate,
    t001w~sachp AS Collector, -- Note: Collector can be stored in different places
    CAST( NULL AS CHAR(1)) AS Mahns,
    CAST( NULL AS INT4) AS DaysOverdue
FROM bkpf
INNER JOIN bseg ON bkpf~bukrs = bseg~bukrs AND bkpf~belnr = bseg~belnr AND bkpf~gjahr = bseg~gjahr
LEFT JOIN kna1 ON bkpf~kunnr = kna1~kunnr
WHERE bkpf~bukrs = '[Your Company Code]' AND bkpf~budat BETWEEN '[Start Date]' AND '[End Date]' AND bkpf~blart IN ('RV', 'DR')

UNION ALL

SELECT
    bseg~belnr AS InvoiceNumber,
    'Invoice Blocked For Payment' AS ActivityName,
    bkpf~aedat AS EventTime, -- Use change date as best approximation
    bkpf~usnam AS UserName,
    bseg~kunnr AS Kunnr,
    kna1~ktokd AS CustomerSegment,
    bseg~dmbtr AS Dmbtr,
    bseg~zfbdt AS NetDueDate,
    t001w~sachp AS Collector,
    CAST( NULL AS CHAR(1)) AS Mahns,
    CAST( NULL AS INT4) AS DaysOverdue
FROM bseg
INNER JOIN bkpf ON bkpf~bukrs = bseg~bukrs AND bkpf~belnr = bseg~belnr AND bkpf~gjahr = bseg~gjahr
LEFT JOIN kna1 ON bseg~kunnr = kna1~kunnr
WHERE bseg~bukrs = '[Your Company Code]' AND bseg~zlspr <> '' AND bkpf~budat BETWEEN '[Start Date]' AND '[End Aate]'

UNION ALL

SELECT
    belnr AS InvoiceNumber,
    'Payment Due Date Passed' AS ActivityName,
    zfbdt AS EventTime, -- The due date itself is the event time
    usnam AS UserName,
    kunnr AS Kunnr,
    kna1~ktokd AS CustomerSegment,
    dmbtr AS Dmbtr,
    zfbdt AS NetDueDate,
    t001w~sachp AS Collector,
    CAST( NULL AS CHAR(1)) AS Mahns,
    CAST( NULL AS INT4) AS DaysOverdue
FROM bsid
LEFT JOIN kna1 ON bsid~kunnr = kna1~kunnr
WHERE bukrs = '[Your Company Code]' AND zfbdt < CURRENT_DATE() AND budat BETWEEN '[Start Date]' AND '[End Date]'

UNION ALL

SELECT
    mhnk~belnr AS InvoiceNumber,
    'Dunning Run Executed' AS ActivityName,
    mhnk~laufd AS EventTime, -- Dunning run date
    laufi~usnam AS UserName,
    mhnk~kunnr AS Kunnr,
    kna1~ktokd AS CustomerSegment,
    bsid~dmbtr AS Dmbtr,
    bsid~zfbdt AS NetDueDate,
    t001w~sachp AS Collector,
    mhnk~mahns AS Mahns,
    CAST( NULL AS INT4) AS DaysOverdue
FROM mhnk
INNER JOIN bsid ON mhnk~bukrs = bsid~bukrs AND mhnk~belnr = bsid~belnr AND mhnk~gjahr = bsid~gjahr
INNER JOIN mahn AS laufi ON mhnk~laufd = laufi~laufd AND mhnk~laufi = laufi~laufi
LEFT JOIN kna1 ON mhnk~kunnr = kna1~kunnr
WHERE mhnk~bukrs = '[Your Company Code]' AND mhnk~laufd BETWEEN '[Start Date]' AND '[End Date]'

UNION ALL

-- Activities from SAP FSCM (Promise to Pay, Dispute Case). Tables/fields might vary.
-- This logic assumes SAP FSCM Collections and Dispute Management is in use.

SELECT
    p2p~invoice_ref AS InvoiceNumber,
    'Promise To Pay Created' AS ActivityName,
    p2p~creation_date AS EventTime,
    p2p~created_by AS UserName,
    p2p~customer AS Kunnr,
    kna1~ktokd AS CustomerSegment,
    CAST( NULL AS DEC(13,2)) AS Dmbtr,
    p2p~promise_date AS NetDueDate,
    p2p~processor AS Collector,
    CAST( NULL AS CHAR(1)) AS Mahns,
    CAST( NULL AS INT4) AS DaysOverdue
FROM udm_p2p_attr AS p2p
LEFT JOIN kna1 ON p2p~customer = kna1~kunnr
WHERE p2p~company_code = '[Your Company Code]' AND p2p~creation_date BETWEEN '[Start Date]' AND '[End Date]'

UNION ALL

SELECT
    p2p~invoice_ref AS InvoiceNumber,
    'Promise To Pay Broken' AS ActivityName,
    p2p~promise_date AS EventTime, -- Event happens on the day the promise was broken
    p2p~created_by AS UserName,
    p2p~customer AS Kunnr,
    kna1~ktokd AS CustomerSegment,
    CAST( NULL AS DEC(13,2)) AS Dmbtr,
    p2p~promise_date AS NetDueDate,
    p2p~processor AS Collector,
    CAST( NULL AS CHAR(1)) AS Mahns,
    CAST( NULL AS INT4) AS DaysOverdue
FROM udm_p2p_attr AS p2p
LEFT JOIN kna1 ON p2p~customer = kna1~kunnr
WHERE p2p~company_code = '[Your Company Code]' AND p2p~state = 'BROKEN' AND p2p~promise_date BETWEEN '[Start Date]' AND '[End Date]'

UNION ALL

SELECT
    obj~value AS InvoiceNumber,
    'Dispute Case Created' AS ActivityName,
    scase~creation_time AS EventTime,
    scase~created_by AS UserName,
    scase~partner AS Kunnr,
    kna1~ktokd AS CustomerSegment,
    CAST( NULL AS DEC(13,2)) AS Dmbtr,
    CAST( NULL AS DATS) AS NetDueDate,
    scase~processor AS Collector,
    CAST( NULL AS CHAR(1)) AS Mahns,
    CAST( NULL AS INT4) AS DaysOverdue
FROM scase
INNER JOIN scmg_t_case_attr ON scase~guid = scmg_t_case_attr~case_guid
INNER JOIN scmg_t_obj_attr AS obj ON scase~guid = obj~case_guid
LEFT JOIN kna1 ON scase~partner = kna1~kunnr
WHERE scase~case_type = '[Your Dispute Case Type]' AND obj~obj_type = 'BSEG' AND scase~creation_time BETWEEN '[Start Date]' AND '[End Date]'

UNION ALL

SELECT
    obj~value AS InvoiceNumber,
    'Dispute Case Resolved' AS ActivityName,
    scase~closing_time AS EventTime,
    scase~closed_by AS UserName,
    scase~partner AS Kunnr,
    kna1~ktokd AS CustomerSegment,
    CAST( NULL AS DEC(13,2)) AS Dmbtr,
    CAST( NULL AS DATS) AS NetDueDate,
    scase~processor AS Collector,
    CAST( NULL AS CHAR(1)) AS Mahns,
    CAST( NULL AS INT4) AS DaysOverdue
FROM scase
INNER JOIN scmg_t_case_attr ON scase~guid = scmg_t_case_attr~case_guid
INNER JOIN scmg_t_obj_attr AS obj ON scase~guid = obj~case_guid
LEFT JOIN kna1 ON scase~partner = kna1~kunnr
WHERE scase~case_type = '[Your Dispute Case Type]' AND obj~obj_type = 'BSEG' AND scase~status = '[Your Resolved Status]' AND scase~closing_time BETWEEN '[Start Date]' AND '[End Date]'

UNION ALL

-- For Collection Contact Logged, this is highly dependent on implementation. Assuming notes in FSCM.
SELECT
    obj~value AS InvoiceNumber,
    'Collection Contact Logged' AS ActivityName,
    note~creation_tstmp AS EventTime,
    note~created_by AS UserName,
    scase~partner AS Kunnr,
    kna1~ktokd AS CustomerSegment,
    CAST( NULL AS DEC(13,2)) AS Dmbtr,
    CAST( NULL AS DATS) AS NetDueDate,
    scase~processor AS Collector,
    CAST( NULL AS CHAR(1)) AS Mahns,
    CAST( NULL AS INT4) AS DaysOverdue
FROM scmg_t_note_attr AS note
INNER JOIN scase ON note~case_guid = scase~guid
INNER JOIN scmg_t_obj_attr AS obj ON scase~guid = obj~case_guid
LEFT JOIN kna1 ON scase~partner = kna1~kunnr
WHERE note~creation_tstmp BETWEEN '[Start Date]' AND '[End Date]'

UNION ALL

SELECT 
    augbl AS InvoiceNumber,
    'Incoming Payment Document Posted' AS ActivityName,
    budat AS EventTime,
    usnam AS UserName,
    kunnr AS Kunnr,
    kna1~ktokd AS CustomerSegment,
    dmbtr AS Dmbtr,
    zfbdt AS NetDueDate,
    t001w~sachp AS Collector,
    mahns AS Mahns,
    CAST( NULL AS INT4) AS DaysOverdue
FROM bsad 
LEFT JOIN kna1 ON bsad~kunnr = kna1~kunnr
WHERE bukrs = '[Your Company Code]' AND blart = 'DZ' AND budat BETWEEN '[Start Date]' AND '[End Date]'

UNION ALL

SELECT
    bsad~belnr AS InvoiceNumber,
    'Residual Item Created' AS ActivityName,
    bsad~augdt AS EventTime,
    bkpf~usnam AS UserName,
    bsad~kunnr AS Kunnr,
    kna1~ktokd AS CustomerSegment,
    bsad~dmbtr AS Dmbtr,
    bsad~zfbdt AS NetDueDate,
    t001w~sachp AS Collector,
    bsad~mahns AS Mahns,
    CAST( NULL AS INT4) AS DaysOverdue
FROM bsad
INNER JOIN bkpf ON bsad~augbl = bkpf~belnr AND bsad~bukrs = bkpf~bukrs AND bsad~augjg = bkpf~gjahr
LEFT JOIN kna1 ON bsad~kunnr = kna1~kunnr
WHERE bsad~bukrs = '[Your Company Code]' AND bsad~augdt BETWEEN '[Start Date]' AND '[End Date]' AND EXISTS (SELECT 1 FROM bseg WHERE bseg~augbl = bsad~augbl AND bseg~rebzg IS NOT NULL)

UNION ALL

SELECT
    bseg~belnr AS InvoiceNumber,
    'Credit Memo Posted' AS ActivityName,
    bkpf~cpudt AS EventTime,
    bkpf~usnam AS UserName,
    bkpf~kunnr AS Kunnr,
    kna1~ktokd AS CustomerSegment,
    bseg~dmbtr * -1 AS Dmbtr,
    bseg~zfbdt AS NetDueDate,
    t001w~sachp AS Collector, 
    CAST( NULL AS CHAR(1)) AS Mahns,
    CAST( NULL AS INT4) AS DaysOverdue
FROM bkpf
INNER JOIN bseg ON bkpf~bukrs = bseg~bukrs AND bkpf~belnr = bseg~belnr AND bkpf~gjahr = bseg~gjahr
LEFT JOIN kna1 ON bkpf~kunnr = kna1~kunnr
WHERE bkpf~bukrs = '[Your Company Code]' AND bkpf~budat BETWEEN '[Start Date]' AND '[End Date]' AND bkpf~blart IN ('DG', 'DZ')

UNION ALL

SELECT
    belnr AS InvoiceNumber,
    'Invoice Cleared' AS ActivityName,
    augdt AS EventTime, -- Clearing date
    usnam AS UserName,
    kunnr AS Kunnr,
    kna1~ktokd AS CustomerSegment,
    dmbtr AS Dmbtr,
    zfbdt AS NetDueDate,
    t001w~sachp AS Collector,
    mahns AS Mahns,
    DATEDIFF(day, zfbdt, augdt) AS DaysOverdue
FROM bsad
LEFT JOIN kna1 ON bsad~kunnr = kna1~kunnr
WHERE bukrs = '[Your Company Code]' AND augdt BETWEEN '[Start Date]' AND '[End Date]' AND blart IN ('RV', 'DR')

UNION ALL

-- Write-off logic can vary. This is a simplified example looking for clearing via a specific reason code.
SELECT
    belnr AS InvoiceNumber,
    'Invoice Written Off' AS ActivityName,
    augdt AS EventTime,
    usnam AS UserName,
    kunnr AS Kunnr,
    kna1~ktokd AS CustomerSegment,
    dmbtr AS Dmbtr,
    zfbdt AS NetDueDate,
    t001w~sachp AS Collector,
    mahns AS Mahns,
    CAST( NULL AS INT4) AS DaysOverdue
FROM bsad
LEFT JOIN kna1 ON bsad~kunnr = kna1~kunnr
WHERE bukrs = '[Your Company Code]' AND augdt BETWEEN '[Start Date]' AND '[End Date]' AND auggr = '[Your Write-Off Reason Code]'

WITH Invoices AS (
    SELECT 
        h.MANDT,
        h.BUKRS,
        h.BELNR, 
        h.GJAHR,
        h.BLART,
        h.BUDAT,
        h.CPUDT,
        h.CPUTM,
        h.USNAM, 
        i.KUNNR,
        i.DMBTR,
        i.ZFBDT AS NetDueDate -- ZFBDT is often the baseline date for payment
    FROM BKPF h
    JOIN BSEG i ON h.MANDT = i.MANDT AND h.BUKRS = i.BUKRS AND h.BELNR = i.BELNR AND h.GJAHR = i.GJAHR
    WHERE h.BUKRS IN ('[Your Company Code]') -- Placeholder: e.g., '1000', '2000'
      AND h.BUDAT BETWEEN '[YYYY-MM-DD]' AND '[YYYY-MM-DD]' -- Placeholder: e.g., '2023-01-01' AND '2023-12-31'
      AND h.BLART IN ('DR', 'RV') -- Typical customer invoice document types
      AND i.KOART = 'D' -- Account Type 'D' for Customer
      AND i.BSCHL LIKE '0%'
)
-- 1. Invoice Posted
SELECT
    i.BELNR AS "InvoiceNumber",
    'Invoice Posted' AS "ActivityName",
    CAST(i.CPUDT AS VARCHAR(8)) || ' ' || CAST(i.CPUTM AS VARCHAR(6)) AS "EventTime",
    i.USNAM AS "UserName",
    i.KUNNR AS "Kunnr",
    k.KDGRP AS "CustomerSegment",
    i.DMBTR AS "Dmbtr",
    i.NetDueDate AS "NetDueDate",
    b.SACHP AS "Collector",
    NULL AS "Mahns",
    NULL AS "DaysOverdue"
FROM Invoices i
LEFT JOIN KNA1 k ON i.MANDT = k.MANDT AND i.KUNNR = k.KUNNR
LEFT JOIN KNB1 b ON i.MANDT = b.MANDT AND i.KUNNR = b.KUNNR AND i.BUKRS = b.BUKRS

UNION ALL

-- 2. Invoice Blocked For Payment (from Change Logs)
SELECT
    i.BELNR AS "InvoiceNumber",
    'Invoice Blocked For Payment' AS "ActivityName",
    CAST(ch.UDATE AS VARCHAR(8)) || ' ' || CAST(ch.UTIME AS VARCHAR(6)) AS "EventTime",
    ch.USERNAME AS "UserName",
    i.KUNNR AS "Kunnr",
    NULL AS "CustomerSegment",
    i.DMBTR AS "Dmbtr",
    i.NetDueDate AS "NetDueDate",
    NULL AS "Collector",
    NULL AS "Mahns",
    NULL AS "DaysOverdue"
FROM Invoices i
JOIN CDHDR ch ON ch.OBJECTCLAS = 'BELEG' AND ch.OBJECTID = CONCAT(i.BUKRS, i.BELNR, i.GJAHR)
JOIN CDPOS cp ON ch.OBJECTCLAS = cp.OBJECTCLAS AND ch.OBJECTID = cp.OBJECTID AND ch.CHANGENR = cp.CHANGENR
WHERE cp.TABNAME = 'BSEG' AND cp.FNAME = 'ZLSPR' AND cp.VALUE_NEW <> ''

UNION ALL

-- 3. Payment Due Date Passed (Calculated)
SELECT 
    i.BELNR AS "InvoiceNumber",
    'Payment Due Date Passed' AS "ActivityName",
    CAST(i.NetDueDate AS VARCHAR(8)) || ' 00:00:00' AS "EventTime",
    'SYSTEM' AS "UserName",
    i.KUNNR AS "Kunnr",
    NULL AS "CustomerSegment",
    i.DMBTR AS "Dmbtr",
    i.NetDueDate AS "NetDueDate",
    NULL AS "Collector",
    NULL AS "Mahns",
    0 AS "DaysOverdue"
FROM Invoices i
WHERE i.NetDueDate < CURRENT_DATE

UNION ALL

-- 4. Dunning Run Executed
SELECT
    m.BELNR AS "InvoiceNumber",
    'Dunning Run Executed' AS "ActivityName",
    CAST(h.LAUFD AS VARCHAR(8)) || ' 00:00:00' AS "EventTime",
    h.USNAM AS "UserName",
    m.KUNNR AS "Kunnr",
    NULL AS "CustomerSegment",
    m.SKBET AS "Dmbtr",
    NULL AS "NetDueDate",
    NULL AS "Collector",
    m.MAHNS AS "Mahns",
    NULL AS "DaysOverdue"
FROM MHND m
JOIN MHNK h ON m.MANDT = h.MANDT AND m.LAUFD = h.LAUFD AND m.LAUFI = h.LAUFI
JOIN Invoices i ON m.MANDT = i.MANDT AND m.BUKRS = i.BUKRS AND m.BELNR = i.BELNR AND m.GJAHR = i.GJAHR

UNION ALL

-- 5. Promise To Pay Created (FSCM)
SELECT 
    p2p.INVOICE_ID AS "InvoiceNumber",
    'Promise To Pay Created' AS "ActivityName",
    p2p.CREATION_DATE || ' ' || p2p.CREATION_TIME AS "EventTime",
    p2p.CREATION_USER AS "UserName",
    i.KUNNR AS "Kunnr",
    NULL AS "CustomerSegment",
    p2p.PROMISED_AMOUNT AS "Dmbtr",
    i.NetDueDate AS "NetDueDate",
    NULL AS "Collector",
    NULL AS "Mahns",
    NULL AS "DaysOverdue"
FROM UDM_P2P_ATTR p2p
JOIN Invoices i ON p2p.INVOICE_ID = i.BELNR AND p2p.COMPANY_CODE = i.BUKRS

UNION ALL

-- 6. Promise To Pay Broken (FSCM, Calculated)
SELECT 
    p2p.INVOICE_ID AS "InvoiceNumber",
    'Promise To Pay Broken' AS "ActivityName",
    p2p.FULFILLMENT_DATE || ' 00:00:00' AS "EventTime",
    'SYSTEM' AS "UserName",
    i.KUNNR AS "Kunnr",
    NULL AS "CustomerSegment",
    p2p.PROMISED_AMOUNT AS "Dmbtr",
    i.NetDueDate AS "NetDueDate",
    NULL AS "Collector",
    NULL AS "Mahns",
    NULL AS "DaysOverdue"
FROM UDM_P2P_ATTR p2p
JOIN Invoices i ON p2p.INVOICE_ID = i.BELNR AND p2p.COMPANY_CODE = i.BUKRS
WHERE p2p.STATE IN ('BROKEN', 'PARTIALLY_KEPT_BROKEN') AND p2p.FULFILLMENT_DATE < CURRENT_DATE

UNION ALL

-- 7. Dispute Case Created (FSCM)
SELECT 
    COALESCE(inv.REF_VALUE, d.CASE_ID) AS "InvoiceNumber",
    'Dispute Case Created' AS "ActivityName",
    d.CREATION_DATE || ' ' || d.CREATION_TIME AS "EventTime",
    d.CREATED_BY AS "UserName",
    d.PARTNER AS "Kunnr",
    NULL AS "CustomerSegment",
    d.DISPUTED_AMOUNT AS "Dmbtr",
    NULL AS "NetDueDate",
    d.PROCESSOR AS "Collector",
    NULL AS "Mahns",
    NULL AS "DaysOverdue"
FROM UDM_DISPUTE d
LEFT JOIN SCMG_T_CASE_ATTR inv ON d.CLIENT = inv.CLIENT AND d.CASE_GUID = inv.CASE_GUID AND inv.ATTR_ID = 'INVOICE'
JOIN Invoices i ON COALESCE(inv.REF_VALUE, d.CASE_ID) = i.BELNR AND d.COMPANY_CODE = i.BUKRS

UNION ALL

-- 8. Collection Contact Logged (FSCM)
SELECT
    i.BELNR AS "InvoiceNumber",
    'Collection Contact Logged' AS "ActivityName",
    c.CONTACT_DATE || ' 00:00:00' AS "EventTime",
    c.CREATED_BY AS "UserName",
    i.KUNNR AS "Kunnr",
    NULL AS "CustomerSegment",
    i.DMBTR AS "Dmbtr",
    i.NetDueDate AS "NetDueDate",
    c.PROCESSOR AS "Collector",
    NULL AS "Mahns",
    NULL AS "DaysOverdue"
FROM UDM_COLL_ITEM c
JOIN Invoices i ON c.DOCUMENT_NUMBER = i.BELNR AND c.COMPANY_CODE = i.BUKRS
WHERE c.OBJECT_TYPE = 'CONTACT'

UNION ALL

-- 9. Dispute Case Resolved (FSCM)
SELECT 
    COALESCE(inv.REF_VALUE, d.CASE_ID) AS "InvoiceNumber",
    'Dispute Case Resolved' AS "ActivityName",
    d.CLOSING_DATE || ' ' || d.CLOSING_TIME AS "EventTime",
    d.CLOSED_BY AS "UserName",
    d.PARTNER AS "Kunnr",
    NULL AS "CustomerSegment",
    d.DISPUTED_AMOUNT AS "Dmbtr",
    NULL AS "NetDueDate",
    d.PROCESSOR AS "Collector",
    NULL AS "Mahns",
    NULL AS "DaysOverdue"
FROM UDM_DISPUTE d
LEFT JOIN SCMG_T_CASE_ATTR inv ON d.CLIENT = inv.CLIENT AND d.CASE_GUID = inv.CASE_GUID AND inv.ATTR_ID = 'INVOICE'
JOIN Invoices i ON COALESCE(inv.REF_VALUE, d.CASE_ID) = i.BELNR AND d.COMPANY_CODE = i.BUKRS
WHERE d.STATE = 'CLOSED'

UNION ALL

-- 10. Incoming Payment Document Posted
SELECT
    i.BELNR AS "InvoiceNumber",
    'Incoming Payment Document Posted' AS "ActivityName",
    CAST(p.BUDAT AS VARCHAR(8)) || ' ' || CAST(p.CPUTM AS VARCHAR(6)) AS "EventTime",
    p.USNAM AS "UserName",
    i.KUNNR AS "Kunnr",
    NULL AS "CustomerSegment",
    NULL AS "Dmbtr",
    i.NetDueDate AS "NetDueDate",
    NULL AS "Collector",
    NULL AS "Mahns",
    NULL AS "DaysOverdue"
FROM Invoices i
JOIN BSAD c ON i.MANDT = c.MANDT AND i.BELNR = c.BELNR AND i.GJAHR = c.GJAHR AND i.BUKRS = c.BUKRS
JOIN BKPF p ON c.MANDT = p.MANDT AND c.AUGBL = p.BELNR AND c.AUGGJ = p.GJAHR AND c.BUKRS = p.BUKRS
WHERE p.BLART IN ('DZ', 'ZP') -- Typical customer payment document types

UNION ALL

-- 11. Residual Item Created (Simplified)
SELECT
    i.BELNR AS "InvoiceNumber",
    'Residual Item Created' AS "ActivityName",
    CAST(c.AUGDT AS VARCHAR(8)) || ' 00:00:00' AS "EventTime",
    c.USNAM_TMP AS "UserName",
    i.KUNNR AS "Kunnr",
    NULL AS "CustomerSegment",
    res.DMBTR AS "Dmbtr",
    i.NetDueDate AS "NetDueDate",
    NULL AS "Collector",
    NULL AS "Mahns",
    NULL AS "DaysOverdue"
FROM Invoices i
JOIN BSAD c ON i.MANDT = c.MANDT AND i.BELNR = c.BELNR AND i.GJAHR = c.GJAHR AND i.BUKRS = c.BUKRS
JOIN BSEG res ON c.MANDT = res.MANDT AND c.AUGBL = res.AUGBL AND c.AUGGJ = res.GJAHR AND c.BUKRS = res.BUKRS
WHERE res.BSCHL = '06' -- Posting key for residual item

UNION ALL

-- 12. Credit Memo Posted
SELECT 
    h.REBZG AS "InvoiceNumber", -- Reference to original invoice
    'Credit Memo Posted' AS "ActivityName",
    CAST(h.CPUDT AS VARCHAR(8)) || ' ' || CAST(h.CPUTM AS VARCHAR(6)) AS "EventTime",
    h.USNAM AS "UserName",
    i.KUNNR AS "Kunnr",
    NULL AS "CustomerSegment",
    i.DMBTR AS "Dmbtr",
    NULL AS "NetDueDate",
    NULL AS "Collector",
    NULL AS "Mahns",
    NULL AS "DaysOverdue"
FROM BKPF h
JOIN BSEG i ON h.MANDT = i.MANDT AND h.BUKRS = i.BUKRS AND h.BELNR = i.BELNR AND h.GJAHR = i.GJAHR
JOIN Invoices inv ON h.REBZG = inv.BELNR AND h.BUKRS = inv.BUKRS
WHERE h.BLART IN ('DG', 'G2') -- Typical credit memo doc types
  AND i.KOART = 'D'

UNION ALL

-- 13. Invoice Cleared
SELECT
    c.BELNR AS "InvoiceNumber",
    'Invoice Cleared' AS "ActivityName",
    CAST(c.AUGDT AS VARCHAR(8)) || ' 00:00:00' AS "EventTime",
    c.USNAM_TMP AS "UserName",
    c.KUNNR AS "Kunnr",
    NULL AS "CustomerSegment",
    c.DMBTR AS "Dmbtr",
    NULL AS "NetDueDate",
    NULL AS "Collector",
    NULL AS "Mahns",
    (c.AUGDT - i.NetDueDate) AS "DaysOverdue"
FROM BSAD c
JOIN Invoices i ON c.MANDT = i.MANDT AND c.KUNNR = i.KUNNR AND c.BELNR = i.BELNR AND c.GJAHR = i.GJAHR

UNION ALL

-- 14. Invoice Written Off
SELECT 
    c.BELNR AS "InvoiceNumber",
    'Invoice Written Off' AS "ActivityName",
    CAST(c.AUGDT AS VARCHAR(8)) || ' 00:00:00' AS "EventTime",
    c.USNAM_TMP AS "UserName",
    c.KUNNR AS "Kunnr",
    NULL AS "CustomerSegment",
    c.DMBTR AS "Dmbtr",
    NULL AS "NetDueDate",
    NULL AS "Collector",
    NULL AS "Mahns",
    NULL AS "DaysOverdue"
FROM BSAD c
JOIN BSEG cl_item ON c.MANDT = cl_item.MANDT AND c.AUGBL = cl_item.BELNR AND c.BUKRS = cl_item.BUKRS AND c.AUGGJ = cl_item.GJAHR
JOIN Invoices i ON c.MANDT = i.MANDT AND c.BELNR = i.BELNR AND c.GJAHR = i.GJAHR
WHERE cl_item.HKONT IN ('[Your Bad Debt GL Account]') -- Placeholder: e.g., '800100'
  AND cl_item.KOART = 'S'; -- G/L Account Item

import pandas as pd
from pyrfc import Connection
from datetime import datetime, timedelta

class=class="hl-string">"hl-comment"># --- 1. CONFIGURATION ---
class=class="hl-string">"hl-comment"># Replace with your SAP system connection details
SAP_CONNECTION_DETAILS = {
    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_username]",
    class="hl-string">"passwd": class="hl-string">"[your_password]"
}

class=class="hl-string">"hl-comment"># Define extraction scope
COMPANY_CODES = [class="hl-string">'1000'] class=class="hl-string">"hl-comment"># Example: [class="hl-string">'1000', class="hl-string">'2000']
START_DATE = class="hl-string">"20230101"
END_DATE = class="hl-string">"20231231"

class=class="hl-string">"hl-comment"># --- Helper function to call RFC_READ_TABLE and return a pandas DataFrame ---
def read_sap_table(conn, table_name, fields, options):
    print(fclass="hl-string">"Reading table: {table_name}...")
    try:
        result = conn.call(class="hl-string">'RFC_READ_TABLE',
                             QUERY_TABLE=table_name,
                             DELIMITER=class="hl-string">'|',
                             FIELDS=fields,
                             OPTIONS=options)
        
        data = [row[class="hl-string">'WA'].split(class="hl-string">'|') for row in result[class="hl-string">'DATA']]
        df = pd.DataFrame(data, columns=fields)
        print(fclass="hl-string">"Successfully read {len(df)} rows from {table_name}.")
        return df
    except Exception as e:
        print(fclass="hl-string">"Error reading table {table_name}: {e}")
        return pd.DataFrame(columns=fields)

class=class="hl-string">"hl-comment"># --- Placeholder functions for custom FSCM RFCs ---
class=class="hl-string">"hl-comment"># These must be implemented by an ABAP developer in SAP
def get_fscm_promises(conn, company_codes, start_date, end_date):
    print(class="hl-string">"INFO: 'get_fscm_promises' is a placeholder. Implement a custom RFC to read promise-to-pay data.")
    class=class="hl-string">"hl-comment"># Example custom RFC: Z_PM_GET_PROMISES
    class=class="hl-string">"hl-comment"># This RFC should read from UDM_P2P_ATTR and related tables.
    class=class="hl-string">"hl-comment"># It should return fields like: BUKRS, BELNR, GJAHR, P2P_CREATE_DATE, P2P_DATE, P2P_USER
    return pd.DataFrame(columns=[class="hl-string">'BELNR', class="hl-string">'GJAHR', class="hl-string">'BUKRS', class="hl-string">'P2P_CREATE_DATE', class="hl-string">'P2P_DATE', class="hl-string">'P2P_USER'])

def get_fscm_disputes(conn, company_codes, start_date, end_date):
    print(class="hl-string">"INFO: 'get_fscm_disputes' is a placeholder. Implement a custom RFC to read dispute case data.")
    class=class="hl-string">"hl-comment"># Example custom RFC: Z_PM_GET_DISPUTES
    class=class="hl-string">"hl-comment"># This RFC should read from UDM_SPS_DC_ATTR and related tables.
    class=class="hl-string">"hl-comment"># It should return fields like: BUKRS, BELNR, GJAHR, CREATE_DATE, CREATE_USER, RESOLVE_DATE
    return pd.DataFrame(columns=[class="hl-string">'BELNR', class="hl-string">'GJAHR', class="hl-string">'BUKRS', class="hl-string">'CREATE_DATE', class="hl-string">'RESOLVE_DATE', class="hl-string">'CREATE_USER'])

def get_fscm_contacts(conn, company_codes, start_date, end_date):
    print(class="hl-string">"INFO: 'get_fscm_contacts' is a placeholder. Implement a custom RFC to read collection contact data.")
    class=class="hl-string">"hl-comment"># Example custom RFC: Z_PM_GET_COLLECTION_CONTACTS
    class=class="hl-string">"hl-comment"># It should read from relevant collection contact log tables.
    class=class="hl-string">"hl-comment"># It should return fields like: BUKRS, BELNR, GJAHR, CONTACT_DATE, CONTACT_USER
    return pd.DataFrame(columns=[class="hl-string">'BELNR', class="hl-string">'GJAHR', class="hl-string">'BUKRS', class="hl-string">'CONTACT_DATE', class="hl-string">'CONTACT_USER'])

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

    try:
        conn = Connection(**SAP_CONNECTION_DETAILS)
        print(class="hl-string">"Successfully connected to SAP.")

        company_code_filter = class="hl-string">" OR ".join([fclass="hl-string">"BUKRS = '{cc}'" for cc in COMPANY_CODES])
        date_filter = fclass="hl-string">"BUDAT >= '{START_DATE}' AND BUDAT <= '{END_DATE}'"
        doc_type_filter = class="hl-string">"(BLART = 'RV' OR BLART = 'DR')" class=class="hl-string">"hl-comment"># Standard Invoice Types
        
        class=class="hl-string">"hl-comment"># 1. Invoice Posted
        bkpf_fields = [class="hl-string">'BELNR', class="hl-string">'GJAHR', class="hl-string">'BUKRS', class="hl-string">'BUDAT', class="hl-string">'CPUDT', class="hl-string">'CPUTM', class="hl-string">'USNAM', class="hl-string">'BLART']
        bkpf_options = [fclass="hl-string">"({company_code_filter}) AND ({date_filter}) AND {doc_type_filter}"]
        df_bkpf = read_sap_table(conn, class="hl-string">'BKPF', bkpf_fields, bkpf_options)

        if not df_bkpf.empty:
            bseg_fields = [class="hl-string">'BELNR', class="hl-string">'GJAHR', class="hl-string">'BUKRS', class="hl-string">'BUZEI', class="hl-string">'KOART', class="hl-string">'KUNNR', class="hl-string">'DMBTR', class="hl-string">'ZFBDT', class="hl-string">'SACHP', class="hl-string">'ZLSPR']
            class=class="hl-string">"hl-comment"># Create batches for BSEG query to avoid hitting RFC limits
            df_bseg_list = []
            for i in range(0, len(df_bkpf), 500):
                batch = df_bkpf.iloc[i:i+500]
                bseg_options = [class="hl-string">" OR ".join([fclass="hl-string">"(BELNR = '{row.BELNR}' AND GJAHR = '{row.GJAHR}' AND BUKRS = '{row.BUKRS}')" for index, row in batch.iterrows()])]
                df_bseg_list.append(read_sap_table(conn, class="hl-string">'BSEG', bseg_fields, bseg_options))
            df_bseg = pd.concat(df_bseg_list)
            df_bseg_customer = df_bseg[df_bseg[class="hl-string">'KOART'] == class="hl-string">'D'].copy()
            df_base = pd.merge(df_bkpf, df_bseg_customer, on=[class="hl-string">'BELNR', class="hl-string">'GJAHR', class="hl-string">'BUKRS'])

            class=class="hl-string">"hl-comment"># Activity: Invoice Posted
            evt_posted = df_base[[class="hl-string">'BELNR', class="hl-string">'GJAHR', class="hl-string">'BUKRS', class="hl-string">'CPUDT', class="hl-string">'CPUTM', class="hl-string">'USNAM', class="hl-string">'KUNNR', class="hl-string">'DMBTR', class="hl-string">'ZFBDT', class="hl-string">'SACHP']].copy()
            evt_posted[class="hl-string">'ActivityName'] = class="hl-string">'Invoice Posted'
            evt_posted[class="hl-string">'EventTime'] = pd.to_datetime(evt_posted[class="hl-string">'CPUDT'] + evt_posted[class="hl-string">'CPUTM'], format=class="hl-string">'%Y%m%d%H%M%S')
            all_events.append(evt_posted)

            class=class="hl-string">"hl-comment"># Activity: Invoice Blocked For Payment (based on current status)
            evt_blocked = df_base[df_base[class="hl-string">'ZLSPR'] != class="hl-string">''].copy()
            if not evt_blocked.empty:
                evt_blocked[class="hl-string">'ActivityName'] = class="hl-string">'Invoice Blocked For Payment'
                evt_blocked[class="hl-string">'EventTime'] = pd.to_datetime(evt_blocked[class="hl-string">'CPUDT'] + evt_blocked[class="hl-string">'CPUTM'], format=class="hl-string">'%Y%m%d%H%M%S') class=class="hl-string">"hl-comment"># Note: This is posting time, not block time.
                all_events.append(evt_blocked)

            class=class="hl-string">"hl-comment"># 2. Invoice Cleared & Residuals & Write-Offs
            bsad_fields = [class="hl-string">'BELNR', class="hl-string">'GJAHR', class="hl-string">'BUKRS', class="hl-string">'KUNNR', class="hl-string">'AUGDT', class="hl-string">'AUGBL', class="hl-string">'DMBTR', class="hl-string">'ZFBDT', class="hl-string">'SACHP', class="hl-string">'RSTGR']
            bsad_options = [fclass="hl-string">"({company_code_filter}) AND AUGDT >= '{START_DATE}' AND AUGDT <= '{END_DATE}' AND (BLART = 'RV' OR BLART = 'DR')"]
            df_bsad = read_sap_table(conn, class="hl-string">'BSAD', bsad_fields, bsad_options)

            if not df_bsad.empty:
                evt_cleared = df_bsad.copy()
                evt_cleared[class="hl-string">'ActivityName'] = class="hl-string">'Invoice Cleared'
                evt_cleared[class="hl-string">'EventTime'] = pd.to_datetime(evt_cleared[class="hl-string">'AUGDT'], format=class="hl-string">'%Y%m%d')
                evt_cleared[class="hl-string">'USNAM'] = class="hl-string">'' class=class="hl-string">"hl-comment"># Clearing user is in the clearing document header
                all_events.append(evt_cleared)

                class=class="hl-string">"hl-comment"># Activity: Residual Item Created (simplified logic)
                evt_residual = df_bsad[df_bsad[class="hl-string">'RSTGR'] != class="hl-string">''].copy()
                if not evt_residual.empty:
                    evt_residual[class="hl-string">'ActivityName'] = class="hl-string">'Residual Item Created'
                    evt_residual[class="hl-string">'EventTime'] = pd.to_datetime(evt_residual[class="hl-string">'AUGDT'], format=class="hl-string">'%Y%m%d')
                    evt_residual[class="hl-string">'USNAM'] = class="hl-string">''
                    all_events.append(evt_residual)

            class=class="hl-string">"hl-comment"># 3. Dunning Run Executed
            mhnk_fields = [class="hl-string">'MABER', class="hl-string">'LAUFD']
            mhnk_options = [fclass="hl-string">"LAUFD >= '{START_DATE}' AND LAUFD <= '{END_DATE}'"]
            df_mhnk = read_sap_table(conn, class="hl-string">'MHNK', mhnk_fields, mhnk_options)
            if not df_mhnk.empty:
                mhnd_fields = [class="hl-string">'MABER', class="hl-string">'LAUFD', class="hl-string">'BELNR', class="hl-string">'GJAHR', class="hl-string">'BUKRS', class="hl-string">'MAHNS', class="hl-string">'KUNNR']
                mhnd_options = [class="hl-string">" OR ".join([fclass="hl-string">"(MABER = '{row.MABER}' AND LAUFD = '{row.LAUFD}')" for index, row in df_mhnk.iterrows()])]
                df_mhnd = read_sap_table(conn, class="hl-string">'MHND', mhnd_fields, mhnd_options)
                if not df_mhnd.empty:
                    evt_dunning = df_mhnd.copy()
                    evt_dunning[class="hl-string">'ActivityName'] = class="hl-string">'Dunning Run Executed'
                    evt_dunning[class="hl-string">'EventTime'] = pd.to_datetime(evt_dunning[class="hl-string">'LAUFD'], format=class="hl-string">'%Y%m%d')
                    evt_dunning[class="hl-string">'USNAM'] = class="hl-string">'SAP_BATCH' class=class="hl-string">"hl-comment"># Dunning is usually a batch job
                    all_events.append(evt_dunning)

            class=class="hl-string">"hl-comment"># 4. Other Document Types (Payments, Credit Memos)
            other_docs_filter = fclass="hl-string">"(BLART = 'DZ' OR BLART = 'DG')" class=class="hl-string">"hl-comment"># DZ=Payment, DG=Credit Memo
            other_bkpf_options = [fclass="hl-string">"({company_code_filter}) AND ({date_filter}) AND {other_docs_filter}"]
            df_other_bkpf = read_sap_table(conn, class="hl-string">'BKPF', bkpf_fields, other_bkpf_options)
            if not df_other_bkpf.empty:
                class=class="hl-string">"hl-comment"># Simplified: Link via customer and date. Proper linking uses clearing doc.
                class=class="hl-string">"hl-comment"># Here we just log the event. You would need to refine logic to link it to a specific invoice.
                class=class="hl-string">"hl-comment"># The presence of these events on the customer timeline is still valuable.
                df_other_bkpf[class="hl-string">'ActivityName'] = df_other_bkpf[class="hl-string">'BLART'].apply(lambda x: class="hl-string">'Incoming Payment Document Posted' if x == class="hl-string">'DZ' else class="hl-string">'Credit Memo Posted')
                df_other_bkpf[class="hl-string">'EventTime'] = pd.to_datetime(df_other_bkpf[class="hl-string">'CPUDT'] + df_other_bkpf[class="hl-string">'CPUTM'], format=class="hl-string">'%Y%m%d%H%M%S')
                class=class="hl-string">"hl-comment"># This part lacks invoice-level detail but is included for completeness.
                print(class="hl-string">"WARNING: Payment/Credit Memo events are logged but not directly linked to a specific invoice in this simplified script.")

            class=class="hl-string">"hl-comment"># 5. FSCM Activities (Requires custom RFCs)
            df_promises = get_fscm_promises(conn, COMPANY_CODES, START_DATE, END_DATE)
            if not df_promises.empty:
                evt_promise_created = df_promises.copy()
                evt_promise_created[class="hl-string">'ActivityName'] = class="hl-string">'Promise To Pay Created'
                evt_promise_created[class="hl-string">'EventTime'] = pd.to_datetime(evt_promise_created[class="hl-string">'P2P_CREATE_DATE'], format=class="hl-string">'%Y%m%d')
                evt_promise_created.rename(columns={class="hl-string">'P2P_USER': class="hl-string">'USNAM'}, inplace=True)
                all_events.append(evt_promise_created)

            df_disputes = get_fscm_disputes(conn, COMPANY_CODES, START_DATE, END_DATE)
            if not df_disputes.empty:
                evt_dispute_created = df_disputes.copy()
                evt_dispute_created[class="hl-string">'ActivityName'] = class="hl-string">'Dispute Case Created'
                evt_dispute_created[class="hl-string">'EventTime'] = pd.to_datetime(evt_dispute_created[class="hl-string">'CREATE_DATE'], format=class="hl-string">'%Y%m%d')
                evt_dispute_created.rename(columns={class="hl-string">'CREATE_USER': class="hl-string">'USNAM'}, inplace=True)
                all_events.append(evt_dispute_created)

                evt_dispute_resolved = df_disputes[df_disputes[class="hl-string">'RESOLVE_DATE'].notna()].copy()
                evt_dispute_resolved[class="hl-string">'ActivityName'] = class="hl-string">'Dispute Case Resolved'
                evt_dispute_resolved[class="hl-string">'EventTime'] = pd.to_datetime(evt_dispute_resolved[class="hl-string">'RESOLVE_DATE'], format=class="hl-string">'%Y%m%d')
                evt_dispute_resolved[class="hl-string">'USNAM'] = class="hl-string">'' class=class="hl-string">"hl-comment"># Resolve user might be different, needs custom RFC logic
                all_events.append(evt_dispute_resolved)

            df_contacts = get_fscm_contacts(conn, COMPANY_CODES, START_DATE, END_DATE)
            if not df_contacts.empty:
                evt_contacts = df_contacts.copy()
                evt_contacts[class="hl-string">'ActivityName'] = class="hl-string">'Collection Contact Logged'
                evt_contacts[class="hl-string">'EventTime'] = pd.to_datetime(evt_contacts[class="hl-string">'CONTACT_DATE'], format=class="hl-string">'%Y%m%d')
                evt_contacts.rename(columns={class="hl-string">'CONTACT_USER': class="hl-string">'USNAM'}, inplace=True)
                all_events.append(evt_contacts)
            
            class=class="hl-string">"hl-comment"># --- 6. Consolidate and Process --- 
            final_df = pd.concat(all_events, ignore_index=True)

            class=class="hl-string">"hl-comment"># Combine document number and year for a unique ID
            final_df[class="hl-string">'InvoiceNumber'] = final_df[class="hl-string">'BELNR'].astype(str) + class="hl-string">'-' + final_df[class="hl-string">'GJAHR'].astype(str)

            class=class="hl-string">"hl-comment"># Rename columns to match target schema
            final_df.rename(columns={
                class="hl-string">'KUNNR': class="hl-string">'Kunnr',
                class="hl-string">'DMBTR': class="hl-string">'Dmbtr',
                class="hl-string">'ZFBDT': class="hl-string">'NetDueDate',
                class="hl-string">'USNAM': class="hl-string">'UserName',
                class="hl-string">'SACHP': class="hl-string">'Sachp',
                class="hl-string">'MAHNS': class="hl-string">'DunningLevel'
            }, inplace=True)
            
            class=class="hl-string">"hl-comment"># --- 7. Derive Calculated Events --- 
            class=class="hl-string">"hl-comment"># Payment Due Date Passed
            df_due_date = df_base[[class="hl-string">'BELNR', class="hl-string">'GJAHR', class="hl-string">'ZFBDT']].copy()
            df_due_date.dropna(subset=[class="hl-string">'ZFBDT'], inplace=True)
            df_due_date = df_due_date[df_due_date[class="hl-string">'ZFBDT'] != class="hl-string">'00000000']
            df_due_date[class="hl-string">'EventTime'] = pd.to_datetime(df_due_date[class="hl-string">'ZFBDT'], format=class="hl-string">'%Y%m%d')
            df_due_date = df_due_date[df_due_date[class="hl-string">'EventTime'] < datetime.now()]
            df_due_date[class="hl-string">'ActivityName'] = class="hl-string">'Payment Due Date Passed'
            df_due_date[class="hl-string">'InvoiceNumber'] = df_due_date[class="hl-string">'BELNR'].astype(str) + class="hl-string">'-' + df_due_date[class="hl-string">'GJAHR'].astype(str)
            final_df = pd.concat([final_df, df_due_date], ignore_index=True)

            class=class="hl-string">"hl-comment"># Promise to Pay Broken (example logic)
            if not df_promises.empty and not df_bsad.empty:
                promises_with_due_date = df_promises.copy()
                promises_with_due_date[class="hl-string">'P2P_DATE_DT'] = pd.to_datetime(promises_with_due_date[class="hl-string">'P2P_DATE'], format=class="hl-string">'%Y%m%d')
                cleared_docs = df_bsad[[class="hl-string">'BELNR', class="hl-string">'GJAHR']].copy()
                cleared_docs[class="hl-string">'cleared'] = True
                promises_with_due_date = pd.merge(promises_with_due_date, cleared_docs, on=[class="hl-string">'BELNR', class="hl-string">'GJAHR'], how=class="hl-string">'left')
                broken_promises = promises_with_due_date[(promises_with_due_date[class="hl-string">'cleared'].isna()) & (promises_with_due_date[class="hl-string">'P2P_DATE_DT'] < datetime.now())]
                if not broken_promises.empty:
                    evt_broken = broken_promises.copy()
                    evt_broken[class="hl-string">'ActivityName'] = class="hl-string">'Promise To Pay Broken'
                    evt_broken[class="hl-string">'EventTime'] = evt_broken[class="hl-string">'P2P_DATE_DT']
                    evt_broken[class="hl-string">'InvoiceNumber'] = evt_broken[class="hl-string">'BELNR'].astype(str) + class="hl-string">'-' + evt_broken[class="hl-string">'GJAHR'].astype(str)
                    final_df = pd.concat([final_df, evt_broken], ignore_index=True)

            class=class="hl-string">"hl-comment"># --- 8. Final Formatting & Export --- 
            class=class="hl-string">"hl-comment"># Standardize column set
            final_columns = [
                class="hl-string">'InvoiceNumber', class="hl-string">'ActivityName', class="hl-string">'EventTime', class="hl-string">'UserName',
                class="hl-string">'Kunnr', class="hl-string">'Dmbtr', class="hl-string">'NetDueDate', class="hl-string">'Sachp', class="hl-string">'DunningLevel'
            ]
            class=class="hl-string">"hl-comment"># Add missing columns with None
            for col in final_columns:
                if col not in final_df.columns:
                    final_df[col] = None

            class=class="hl-string">"hl-comment"># Select and order columns
            final_df = final_df[final_columns]

            class=class="hl-string">"hl-comment"># Sort by case and time
            final_df.sort_values(by=[class="hl-string">'InvoiceNumber', class="hl-string">'EventTime'], inplace=True)
            
            class=class="hl-string">"hl-comment"># Export to CSV
            output_filename = class="hl-string">'credit_management_event_log.csv'
            final_df.to_csv(output_filename, index=False, date_format=class="hl-string">'%Y-%m-%d %H:%M:%S')
            print(fclass="hl-string">"\nExtraction complete. Event log saved to {output_filename}")

    except Exception as e:
        print(fclass="hl-string">"An error occurred: {e}")
    finally:
        if class="hl-string">'conn' in locals() and conn.alive:
            conn.close()
            print(class="hl-string">"SAP connection closed.")

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

/*
NOTE: SAP Query (SQVI/SQ01) is a visual tool and does not use a single script. 
The following ABAP SQL code is a logical representation of the data that must be extracted
across multiple, separate queries which are then combined manually. 
This script consolidates the logic for all 14 activities into one block for clarity.
*/

SELECT
    bseg~belnr AS InvoiceNumber,
    'Invoice Posted' AS ActivityName,
    bkpf~cpudt AS EventTime,
    bkpf~usnam AS UserName,
    bkpf~kunnr AS Kunnr,
    kna1~ktokd AS CustomerSegment,
    bseg~dmbtr AS Dmbtr,
    bseg~zfbdt AS NetDueDate,
    t001w~sachp AS Collector, -- Note: Collector can be stored in different places
    CAST( NULL AS CHAR(1)) AS Mahns,
    CAST( NULL AS INT4) AS DaysOverdue
FROM bkpf
INNER JOIN bseg ON bkpf~bukrs = bseg~bukrs AND bkpf~belnr = bseg~belnr AND bkpf~gjahr = bseg~gjahr
LEFT JOIN kna1 ON bkpf~kunnr = kna1~kunnr
WHERE bkpf~bukrs = '[Your Company Code]' AND bkpf~budat BETWEEN '[Start Date]' AND '[End Date]' AND bkpf~blart IN ('RV', 'DR')

UNION ALL

SELECT
    bseg~belnr AS InvoiceNumber,
    'Invoice Blocked For Payment' AS ActivityName,
    bkpf~aedat AS EventTime, -- Use change date as best approximation
    bkpf~usnam AS UserName,
    bseg~kunnr AS Kunnr,
    kna1~ktokd AS CustomerSegment,
    bseg~dmbtr AS Dmbtr,
    bseg~zfbdt AS NetDueDate,
    t001w~sachp AS Collector,
    CAST( NULL AS CHAR(1)) AS Mahns,
    CAST( NULL AS INT4) AS DaysOverdue
FROM bseg
INNER JOIN bkpf ON bkpf~bukrs = bseg~bukrs AND bkpf~belnr = bseg~belnr AND bkpf~gjahr = bseg~gjahr
LEFT JOIN kna1 ON bseg~kunnr = kna1~kunnr
WHERE bseg~bukrs = '[Your Company Code]' AND bseg~zlspr <> '' AND bkpf~budat BETWEEN '[Start Date]' AND '[End Aate]'

UNION ALL

SELECT
    belnr AS InvoiceNumber,
    'Payment Due Date Passed' AS ActivityName,
    zfbdt AS EventTime, -- The due date itself is the event time
    usnam AS UserName,
    kunnr AS Kunnr,
    kna1~ktokd AS CustomerSegment,
    dmbtr AS Dmbtr,
    zfbdt AS NetDueDate,
    t001w~sachp AS Collector,
    CAST( NULL AS CHAR(1)) AS Mahns,
    CAST( NULL AS INT4) AS DaysOverdue
FROM bsid
LEFT JOIN kna1 ON bsid~kunnr = kna1~kunnr
WHERE bukrs = '[Your Company Code]' AND zfbdt < CURRENT_DATE() AND budat BETWEEN '[Start Date]' AND '[End Date]'

UNION ALL

SELECT
    mhnk~belnr AS InvoiceNumber,
    'Dunning Run Executed' AS ActivityName,
    mhnk~laufd AS EventTime, -- Dunning run date
    laufi~usnam AS UserName,
    mhnk~kunnr AS Kunnr,
    kna1~ktokd AS CustomerSegment,
    bsid~dmbtr AS Dmbtr,
    bsid~zfbdt AS NetDueDate,
    t001w~sachp AS Collector,
    mhnk~mahns AS Mahns,
    CAST( NULL AS INT4) AS DaysOverdue
FROM mhnk
INNER JOIN bsid ON mhnk~bukrs = bsid~bukrs AND mhnk~belnr = bsid~belnr AND mhnk~gjahr = bsid~gjahr
INNER JOIN mahn AS laufi ON mhnk~laufd = laufi~laufd AND mhnk~laufi = laufi~laufi
LEFT JOIN kna1 ON mhnk~kunnr = kna1~kunnr
WHERE mhnk~bukrs = '[Your Company Code]' AND mhnk~laufd BETWEEN '[Start Date]' AND '[End Date]'

UNION ALL

-- Activities from SAP FSCM (Promise to Pay, Dispute Case). Tables/fields might vary.
-- This logic assumes SAP FSCM Collections and Dispute Management is in use.

SELECT
    p2p~invoice_ref AS InvoiceNumber,
    'Promise To Pay Created' AS ActivityName,
    p2p~creation_date AS EventTime,
    p2p~created_by AS UserName,
    p2p~customer AS Kunnr,
    kna1~ktokd AS CustomerSegment,
    CAST( NULL AS DEC(13,2)) AS Dmbtr,
    p2p~promise_date AS NetDueDate,
    p2p~processor AS Collector,
    CAST( NULL AS CHAR(1)) AS Mahns,
    CAST( NULL AS INT4) AS DaysOverdue
FROM udm_p2p_attr AS p2p
LEFT JOIN kna1 ON p2p~customer = kna1~kunnr
WHERE p2p~company_code = '[Your Company Code]' AND p2p~creation_date BETWEEN '[Start Date]' AND '[End Date]'

UNION ALL

SELECT
    p2p~invoice_ref AS InvoiceNumber,
    'Promise To Pay Broken' AS ActivityName,
    p2p~promise_date AS EventTime, -- Event happens on the day the promise was broken
    p2p~created_by AS UserName,
    p2p~customer AS Kunnr,
    kna1~ktokd AS CustomerSegment,
    CAST( NULL AS DEC(13,2)) AS Dmbtr,
    p2p~promise_date AS NetDueDate,
    p2p~processor AS Collector,
    CAST( NULL AS CHAR(1)) AS Mahns,
    CAST( NULL AS INT4) AS DaysOverdue
FROM udm_p2p_attr AS p2p
LEFT JOIN kna1 ON p2p~customer = kna1~kunnr
WHERE p2p~company_code = '[Your Company Code]' AND p2p~state = 'BROKEN' AND p2p~promise_date BETWEEN '[Start Date]' AND '[End Date]'

UNION ALL

SELECT
    obj~value AS InvoiceNumber,
    'Dispute Case Created' AS ActivityName,
    scase~creation_time AS EventTime,
    scase~created_by AS UserName,
    scase~partner AS Kunnr,
    kna1~ktokd AS CustomerSegment,
    CAST( NULL AS DEC(13,2)) AS Dmbtr,
    CAST( NULL AS DATS) AS NetDueDate,
    scase~processor AS Collector,
    CAST( NULL AS CHAR(1)) AS Mahns,
    CAST( NULL AS INT4) AS DaysOverdue
FROM scase
INNER JOIN scmg_t_case_attr ON scase~guid = scmg_t_case_attr~case_guid
INNER JOIN scmg_t_obj_attr AS obj ON scase~guid = obj~case_guid
LEFT JOIN kna1 ON scase~partner = kna1~kunnr
WHERE scase~case_type = '[Your Dispute Case Type]' AND obj~obj_type = 'BSEG' AND scase~creation_time BETWEEN '[Start Date]' AND '[End Date]'

UNION ALL

SELECT
    obj~value AS InvoiceNumber,
    'Dispute Case Resolved' AS ActivityName,
    scase~closing_time AS EventTime,
    scase~closed_by AS UserName,
    scase~partner AS Kunnr,
    kna1~ktokd AS CustomerSegment,
    CAST( NULL AS DEC(13,2)) AS Dmbtr,
    CAST( NULL AS DATS) AS NetDueDate,
    scase~processor AS Collector,
    CAST( NULL AS CHAR(1)) AS Mahns,
    CAST( NULL AS INT4) AS DaysOverdue
FROM scase
INNER JOIN scmg_t_case_attr ON scase~guid = scmg_t_case_attr~case_guid
INNER JOIN scmg_t_obj_attr AS obj ON scase~guid = obj~case_guid
LEFT JOIN kna1 ON scase~partner = kna1~kunnr
WHERE scase~case_type = '[Your Dispute Case Type]' AND obj~obj_type = 'BSEG' AND scase~status = '[Your Resolved Status]' AND scase~closing_time BETWEEN '[Start Date]' AND '[End Date]'

UNION ALL

-- For Collection Contact Logged, this is highly dependent on implementation. Assuming notes in FSCM.
SELECT
    obj~value AS InvoiceNumber,
    'Collection Contact Logged' AS ActivityName,
    note~creation_tstmp AS EventTime,
    note~created_by AS UserName,
    scase~partner AS Kunnr,
    kna1~ktokd AS CustomerSegment,
    CAST( NULL AS DEC(13,2)) AS Dmbtr,
    CAST( NULL AS DATS) AS NetDueDate,
    scase~processor AS Collector,
    CAST( NULL AS CHAR(1)) AS Mahns,
    CAST( NULL AS INT4) AS DaysOverdue
FROM scmg_t_note_attr AS note
INNER JOIN scase ON note~case_guid = scase~guid
INNER JOIN scmg_t_obj_attr AS obj ON scase~guid = obj~case_guid
LEFT JOIN kna1 ON scase~partner = kna1~kunnr
WHERE note~creation_tstmp BETWEEN '[Start Date]' AND '[End Date]'

UNION ALL

SELECT 
    augbl AS InvoiceNumber,
    'Incoming Payment Document Posted' AS ActivityName,
    budat AS EventTime,
    usnam AS UserName,
    kunnr AS Kunnr,
    kna1~ktokd AS CustomerSegment,
    dmbtr AS Dmbtr,
    zfbdt AS NetDueDate,
    t001w~sachp AS Collector,
    mahns AS Mahns,
    CAST( NULL AS INT4) AS DaysOverdue
FROM bsad 
LEFT JOIN kna1 ON bsad~kunnr = kna1~kunnr
WHERE bukrs = '[Your Company Code]' AND blart = 'DZ' AND budat BETWEEN '[Start Date]' AND '[End Date]'

UNION ALL

SELECT
    bsad~belnr AS InvoiceNumber,
    'Residual Item Created' AS ActivityName,
    bsad~augdt AS EventTime,
    bkpf~usnam AS UserName,
    bsad~kunnr AS Kunnr,
    kna1~ktokd AS CustomerSegment,
    bsad~dmbtr AS Dmbtr,
    bsad~zfbdt AS NetDueDate,
    t001w~sachp AS Collector,
    bsad~mahns AS Mahns,
    CAST( NULL AS INT4) AS DaysOverdue
FROM bsad
INNER JOIN bkpf ON bsad~augbl = bkpf~belnr AND bsad~bukrs = bkpf~bukrs AND bsad~augjg = bkpf~gjahr
LEFT JOIN kna1 ON bsad~kunnr = kna1~kunnr
WHERE bsad~bukrs = '[Your Company Code]' AND bsad~augdt BETWEEN '[Start Date]' AND '[End Date]' AND EXISTS (SELECT 1 FROM bseg WHERE bseg~augbl = bsad~augbl AND bseg~rebzg IS NOT NULL)

UNION ALL

SELECT
    bseg~belnr AS InvoiceNumber,
    'Credit Memo Posted' AS ActivityName,
    bkpf~cpudt AS EventTime,
    bkpf~usnam AS UserName,
    bkpf~kunnr AS Kunnr,
    kna1~ktokd AS CustomerSegment,
    bseg~dmbtr * -1 AS Dmbtr,
    bseg~zfbdt AS NetDueDate,
    t001w~sachp AS Collector, 
    CAST( NULL AS CHAR(1)) AS Mahns,
    CAST( NULL AS INT4) AS DaysOverdue
FROM bkpf
INNER JOIN bseg ON bkpf~bukrs = bseg~bukrs AND bkpf~belnr = bseg~belnr AND bkpf~gjahr = bseg~gjahr
LEFT JOIN kna1 ON bkpf~kunnr = kna1~kunnr
WHERE bkpf~bukrs = '[Your Company Code]' AND bkpf~budat BETWEEN '[Start Date]' AND '[End Date]' AND bkpf~blart IN ('DG', 'DZ')

UNION ALL

SELECT
    belnr AS InvoiceNumber,
    'Invoice Cleared' AS ActivityName,
    augdt AS EventTime, -- Clearing date
    usnam AS UserName,
    kunnr AS Kunnr,
    kna1~ktokd AS CustomerSegment,
    dmbtr AS Dmbtr,
    zfbdt AS NetDueDate,
    t001w~sachp AS Collector,
    mahns AS Mahns,
    DATEDIFF(day, zfbdt, augdt) AS DaysOverdue
FROM bsad
LEFT JOIN kna1 ON bsad~kunnr = kna1~kunnr
WHERE bukrs = '[Your Company Code]' AND augdt BETWEEN '[Start Date]' AND '[End Date]' AND blart IN ('RV', 'DR')

UNION ALL

-- Write-off logic can vary. This is a simplified example looking for clearing via a specific reason code.
SELECT
    belnr AS InvoiceNumber,
    'Invoice Written Off' AS ActivityName,
    augdt AS EventTime,
    usnam AS UserName,
    kunnr AS Kunnr,
    kna1~ktokd AS CustomerSegment,
    dmbtr AS Dmbtr,
    zfbdt AS NetDueDate,
    t001w~sachp AS Collector,
    mahns AS Mahns,
    CAST( NULL AS INT4) AS DaysOverdue
FROM bsad
LEFT JOIN kna1 ON bsad~kunnr = kna1~kunnr
WHERE bukrs = '[Your Company Code]' AND augdt BETWEEN '[Start Date]' AND '[End Date]' AND auggr = '[Your Write-Off Reason Code]'