Mẫu dữ liệu quản lý tín dụng và thu hồi công nợ

/*
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()

REPORT z_processmind_credit_extract.

* Complete executable ABAP template. Replace bracketed placeholders with validated
* system-specific sources, fields, and business rules before activation.

PARAMETERS p_bukrs TYPE bukrs OBLIGATORY.
PARAMETERS p_datef TYPE sydatum OBLIGATORY.
PARAMETERS p_datet TYPE sydatum OBLIGATORY.
PARAMETERS p_eval  TYPE sydatum OBLIGATORY.
PARAMETERS p_file  TYPE string LOWER CASE OBLIGATORY.
SELECT-OPTIONS s_kunnr FOR bseg-kunnr.
SELECT-OPTIONS s_belnr FOR bkpf-belnr.
SELECT-OPTIONS s_blart FOR bkpf-blart.

TYPES: BEGIN OF ty_event,
         invoicenumber   TYPE string,
         activityname    TYPE string,
         eventtime       TYPE string,
         username        TYPE string,
         kunnr           TYPE string,
         customersegment TYPE string,
         dmbtr           TYPE string,
         netduedate      TYPE string,
         sachp           TYPE string,
         mahns           TYPE string,
         daysoverdue     TYPE string,
       END OF ty_event.

DATA gt_event TYPE STANDARD TABLE OF ty_event WITH EMPTY KEY.
DATA gs_event TYPE ty_event.

TYPES: BEGIN OF ty_fi,
         bukrs TYPE bkpf-bukrs,
         belnr TYPE bkpf-belnr,
         gjahr TYPE bkpf-gjahr,
         blart TYPE bkpf-blart,
         budat TYPE bkpf-budat,
         cpudt TYPE bkpf-cpudt,
         cputm TYPE bkpf-cputm,
         usnam TYPE bkpf-usnam,
         kunnr TYPE bseg-kunnr,
         buzei TYPE bseg-buzei,
         dmbtr TYPE bseg-dmbtr,
         zfbdt TYPE bseg-zfbdt,
         zbd1t TYPE bseg-zbd1t,
         zbd2t TYPE bseg-zbd2t,
         zbd3t TYPE bseg-zbd3t,
         zlsch TYPE bseg-zlsch,
         zlspr TYPE bseg-zlspr,
         augbl TYPE bseg-augbl,
         augdt TYPE bseg-augdt,
         shkzg TYPE bseg-shkzg,
       END OF ty_fi.

DATA gt_fi TYPE STANDARD TABLE OF ty_fi WITH EMPTY KEY.
DATA gs_fi TYPE ty_fi.

FORM add_event USING iv_invoice TYPE string
                     iv_activity TYPE string
                     iv_date TYPE sydatum
                     iv_time TYPE syuzeit
                     iv_user TYPE string
                     iv_kunnr TYPE string
                     iv_amount TYPE bseg-dmbtr
                     iv_due TYPE sydatum
                     iv_sachp TYPE string
                     iv_mahns TYPE string.
  DATA lv_days TYPE i.
  CLEAR gs_event.
  gs_event-invoicenumber = iv_invoice.
  gs_event-activityname = iv_activity.
  IF iv_date IS INITIAL.
    iv_date = p_eval.
  ENDIF.
  IF iv_time IS INITIAL.
    iv_time = '000000'.
  ENDIF.
  CONCATENATE iv_date iv_time INTO gs_event-eventtime SEPARATED BY space.
  gs_event-username = iv_user.
  gs_event-kunnr = iv_kunnr.
  gs_event-dmbtr = |{ iv_amount NUMBER = RAW }|.
  gs_event-netduedate = |{ iv_due DATE = ISO }|.
  gs_event-sachp = iv_sachp.
  gs_event-mahns = iv_mahns.
  IF iv_due IS NOT INITIAL AND p_eval > iv_due.
    lv_days = p_eval - iv_due.
    gs_event-daysoverdue = |{ lv_days }|.
  ENDIF.
  APPEND gs_event TO gt_event.
ENDFORM.

START-OF-SELECTION.

  SELECT a~bukrs, a~belnr, a~gjahr, a~blart, a~budat, a~cpudt,
         a~cputm, a~usnam, b~kunnr, b~buzei, b~dmbtr, b~zfbdt,
         b~zbd1t, b~zbd2t, b~zbd3t, b~zlsch, b~zlspr, b~augbl,
         b~augdt, b~shkzg
    FROM bkpf AS a
    INNER JOIN bseg AS b
      ON b~bukrs = a~bukrs
     AND b~belnr = a~belnr
     AND b~gjahr = a~gjahr
    WHERE a~bukrs = @p_bukrs
      AND a~budat BETWEEN @p_datef AND @p_datet
      AND b~kunnr IN @s_kunnr
      AND a~belnr IN @s_belnr
      AND a~blart IN @s_blart
    INTO TABLE @gt_fi.

  LOOP AT gt_fi INTO gs_fi.
    DATA(lv_invoice) = |{ gs_fi-bukrs }/{ gs_fi-belnr }/{ gs_fi-gjahr }|.
    DATA(lv_due) = gs_fi-zfbdt.
    IF lv_due IS INITIAL.
      lv_due = gs_fi-budat.
    ENDIF.
    IF gs_fi-zbd1t IS NOT INITIAL.
      lv_due = lv_due + gs_fi-zbd1t.
    ELSEIF gs_fi-zbd2t IS NOT INITIAL.
      lv_due = lv_due + gs_fi-zbd2t.
    ELSEIF gs_fi-zbd3t IS NOT INITIAL.
      lv_due = lv_due + gs_fi-zbd3t.
    ENDIF.

    IF gs_fi-shkzg = 'S'.
      PERFORM add_event USING lv_invoice 'Invoice Posted' gs_fi-budat gs_fi-cputm gs_fi-usnam gs_fi-kunnr gs_fi-dmbtr lv_due '' ''.
    ENDIF.

    IF gs_fi-zlspr IS NOT INITIAL.
      PERFORM add_event USING lv_invoice 'Invoice Blocked For Payment' gs_fi-budat gs_fi-cputm gs_fi-usnam gs_fi-kunnr gs_fi-dmbtr lv_due '' ''.
    ENDIF.

    IF lv_due < p_eval.
      PERFORM add_event USING lv_invoice 'Payment Due Date Passed' lv_due '000000' '' gs_fi-kunnr gs_fi-dmbtr lv_due '' ''.
    ENDIF.

    IF gs_fi-augbl IS NOT INITIAL AND gs_fi-augdt IS NOT INITIAL.
      PERFORM add_event USING lv_invoice 'Invoice Cleared' gs_fi-augdt '000000' '' gs_fi-kunnr gs_fi-dmbtr lv_due '' ''.
    ENDIF.

    IF gs_fi-shkzg = 'H' AND gs_fi-blart IN s_blart.
      PERFORM add_event USING lv_invoice 'Incoming Payment Document Posted' gs_fi-budat gs_fi-cputm gs_fi-usnam gs_fi-kunnr gs_fi-dmbtr lv_due '' ''.
    ENDIF.

    IF gs_fi-shkzg = 'S' AND gs_fi-blart IN s_blart.
      PERFORM add_event USING lv_invoice 'Credit Memo Posted' gs_fi-budat gs_fi-cputm gs_fi-usnam gs_fi-kunnr gs_fi-dmbtr lv_due '' ''.
    ENDIF.
  ENDLOOP.

  SELECT a~bukrs, a~belnr, a~gjahr, a~blart, a~budat, a~cpudt,
         a~cputm, a~usnam, b~kunnr, b~buzei, b~dmbtr, b~zfbdt,
         b~zbd1t, b~zbd2t, b~zbd3t, b~zlsch, b~zlspr, b~augbl,
         b~augdt, b~shkzg
    FROM bkpf AS a
    INNER JOIN bseg AS b
      ON b~bukrs = a~bukrs
     AND b~belnr = a~belnr
     AND b~gjahr = a~gjahr
    WHERE a~bukrs = @p_bukrs
      AND a~budat BETWEEN @p_datef AND @p_datet
      AND b~kunnr IN @s_kunnr
      AND a~belnr IN @s_belnr
      AND a~blart IN @s_blart
      AND b~augbl IS NOT INITIAL
      AND b~augdt IS NOT INITIAL
    INTO TABLE @DATA(lt_clearing).

  LOOP AT lt_clearing INTO DATA(ls_clearing).
    DATA(lv_clear_invoice) = |{ ls_clearing-bukrs }/{ ls_clearing-belnr }/{ ls_clearing-gjahr }|.
    DATA(lv_clear_due) = ls_clearing-zfbdt.
    IF lv_clear_due IS INITIAL.
      lv_clear_due = ls_clearing-budat.
    ENDIF.
    IF ls_clearing-augdt < p_eval AND ls_clearing-dmbtr IS NOT INITIAL.
      PERFORM add_event USING lv_clear_invoice 'Residual Item Created' ls_clearing-augdt '000000' '' ls_clearing-kunnr ls_clearing-dmbtr lv_clear_due '' ''.
    ENDIF.
    IF ls_clearing-shkzg = 'H'.
      PERFORM add_event USING lv_clear_invoice 'Invoice Written Off' ls_clearing-augdt '000000' '' ls_clearing-kunnr ls_clearing-dmbtr lv_clear_due '' ''.
    ENDIF.
  ENDLOOP.

  " Replace the following source-specific sections with validated SELECT statements.
  " Each section must append rows to gt_event using PERFORM add_event.
  " The placeholder sources and fields are intentionally explicit because SAP ECC
  " implementations differ and no universal source can be assumed.

  " Dunning Run Executed from [Your dunning source].
  " Required mapping: invoice reference, dunning date, dunning level, user, customer, amount, due date.
  " Example implementation pattern:
  " SELECT [invoice reference], [dunning date], [dunning level], [user], [customer], [amount], [due date]
  "   FROM [Your dunning source]
  "   WHERE [company code] = @p_bukrs AND [dunning date] BETWEEN @p_datef AND @p_datet
  "   INTO TABLE @DATA(lt_dunning).
  " LOOP AT lt_dunning INTO DATA(ls_dunning).
  "   PERFORM add_event USING ls_dunning-[invoice reference] 'Dunning Run Executed' ls_dunning-[dunning date] '000000' ls_dunning-[user] ls_dunning-[customer] ls_dunning-[amount] ls_dunning-[due date] '' ls_dunning-[dunning level].
  " ENDLOOP.

  " Promise To Pay Created from [Your promise to pay source].
  " Promise To Pay Broken must be emitted only after validated payment matching by promise date.
  " Dispute Case Created and Dispute Case Resolved must be read from [Your dispute case source].
  " Collection Contact Logged must be read from [Your collection contact source].
  " These sections require replacement of bracketed source names and fields before activation.

  SORT gt_event BY invoicenumber eventtime activityname.

  OPEN DATASET p_file FOR OUTPUT IN TEXT MODE ENCODING UTF-8.
  IF sy-subrc <> 0.
    MESSAGE 'Unable to open output file' TYPE 'E'.
  ENDIF.
  TRANSFER 'InvoiceNumber;ActivityName;EventTime;UserName;Kunnr;CustomerSegment;Dmbtr;NetDueDate;Sachp;Mahns;DaysOverdue' TO p_file.
  LOOP AT gt_event INTO gs_event.
    DATA(lv_line) = |{ gs_event-invoicenumber };{ gs_event-activityname };{ gs_event-eventtime };{ gs_event-username };{ gs_event-kunnr };{ gs_event-customersegment };{ gs_event-dmbtr };{ gs_event-netduedate };{ gs_event-sachp };{ gs_event-mahns };{ gs_event-daysoverdue }|.
    TRANSFER lv_line TO p_file.
  ENDLOOP.
  CLOSE DATASET p_file.