เทมเพลตข้อมูลลูกหนี้การค้า

REPORT Z_AR_PROCESS_MINING_EXTRACT.

* This ABAP logic represents the extraction required to build the event log.
* It simulates what the SQVI join produces plus the necessary transformations
* for the 14 required activities which SQVI cannot natively pivot.

TYPES: BEGIN OF ty_event_log,
         invoice_number TYPE belnr_d,
         activity_name  TYPE char50,
         event_time     TYPE timestamp,
         source_system  TYPE char10,
         amount         TYPE dmbtr,
         doc_type       TYPE blart,
         customer       TYPE kunnr,
       END OF ty_event_log.

DATA: lt_event_log TYPE STANDARD TABLE OF ty_event_log,
      ls_event     TYPE ty_event_log.

* 1. Activity: Accounting Document Created
* Source: BKPF (Header) and BSEG (Item)
SELECT b~belnr AS invoice_number,
       'Accounting Document Created' AS activity_name,
       b~cpudt AS event_date,
       b~cputm AS event_time,
       'SAP_ECC' AS source_system,
       s~dmbtr AS amount,
       b~blart AS doc_type,
       s~kunnr AS customer
  INTO CORRESPONDING FIELDS OF TABLE @lt_event_log
  FROM bkpf AS b
  INNER JOIN bseg AS s ON b~bukrs = s~bukrs AND b~belnr = s~belnr AND b~gjahr = s~gjahr
  WHERE s~koart = 'D'.

* 2. Activity: Invoice Dispatched
* Source: NAST (Message Control) linked to Invoice
SELECT b~belnr AS invoice_number,
       'Invoice Dispatched' AS activity_name,
       n~datvr AS event_date,
       n~uhrvr AS event_time,
       'SAP_ECC' AS source_system
  APPENDING CORRESPONDING FIELDS OF TABLE @lt_event_log
  FROM nast AS n
  INNER JOIN bkpf AS b ON n~objky = b~awkey
  WHERE n~kappl = 'V3' AND n~nacha = '1'. " V3=Billing, 1=Print

* 3. Activity: Payment Terms Changed
* Source: CDHDR/CDPOS (Change Documents)
SELECT objectid AS invoice_number,
       'Payment Terms Changed' AS activity_name,
       udate AS event_date,
       utime AS event_time,
       'SAP_ECC' AS source_system
  APPENDING CORRESPONDING FIELDS OF TABLE @lt_event_log
  FROM cdhdr
  WHERE objectclas = 'BELEG' AND tcode IN ('FB02', 'FB09').

* 4. Activity: Payment Reminder Sent
* Source: MHND (Dunning Data)
SELECT d~belnr AS invoice_number,
       'Payment Reminder Sent' AS activity_name,
       d~ausdt AS event_date,
       '000000' AS event_time,
       'SAP_ECC' AS source_system
  APPENDING CORRESPONDING FIELDS OF TABLE @lt_event_log
  FROM mhnd AS d.

* 5. Activity: Dispute Case Recorded
* Source: SCMG_T_CASE_ATTR (FSCM Case Management)
* Note: Requires linking Case ID to Invoice via Case Link tables

* 6. Activity: Promise to Pay Recorded
* Source: UDM_P2P_ATTR (FSCM Promise to Pay)

* 7. Activity: Credit Memo Posted
* Source: BKPF/BSEG where Document Type implies Credit
SELECT b~belnr AS invoice_number,
       'Credit Memo Posted' AS activity_name,
       b~cpudt AS event_date,
       b~cputm AS event_time,
       'SAP_ECC' AS source_system
  APPENDING CORRESPONDING FIELDS OF TABLE @lt_event_log
  FROM bkpf AS b
  INNER JOIN bseg AS s ON b~bukrs = s~bukrs AND b~belnr = s~belnr
  WHERE s~koart = 'D' AND b~blart = 'DG'.

* 8. Activity: Partial Payment Posted
* Source: BSEG checks for residual items logic

* 9. Activity: Bank Statement Matched
* Source: FEBEP (Electronic Bank Statement Line Items)
SELECT f~belnr AS invoice_number,
       'Bank Statement Matched' AS activity_name,
       f~valut AS event_date,
       '000000' AS event_time
  APPENDING CORRESPONDING FIELDS OF TABLE @lt_event_log
  FROM febep AS f.

* 10. Activity: Payment Received
* Source: BKPF/BSEG of the clearing document
SELECT s~belnr AS invoice_number,
       'Payment Received' AS activity_name,
       b~cpudt AS event_date,
       b~cputm AS event_time
  APPENDING CORRESPONDING FIELDS OF TABLE @lt_event_log
  FROM bkpf AS b
  INNER JOIN bseg AS s ON b~bukrs = s~bukrs AND b~belnr = s~belnr
  WHERE b~blart = 'DZ'.

* 11. Activity: Cash Discount Applied
* Source: BSEG-SKNTO (Cash Discount Amount)
SELECT belnr AS invoice_number,
       'Cash Discount Applied' AS activity_name,
       augdt AS event_date,
       '000000' AS event_time
  APPENDING CORRESPONDING FIELDS OF TABLE @lt_event_log
  FROM bseg
  WHERE sknto > 0 AND koart = 'D'.

* 12. Activity: Invoice Cleared
* Source: BSAD (Cleared Items)
SELECT belnr AS invoice_number,
       'Invoice Cleared' AS activity_name,
       augdt AS event_date,
       '235959' AS event_time,
       'SAP_ECC' AS source_system
  APPENDING CORRESPONDING FIELDS OF TABLE @lt_event_log
  FROM bsad
  WHERE augdt IS NOT INITIAL.

* 13. Activity: Clearing Reversed
* Source: BKPF-STBLG (Reversal Document Number)
SELECT stblg AS invoice_number,
       'Clearing Reversed' AS activity_name,
       cpudt AS event_date,
       cputm AS event_time
  APPENDING CORRESPONDING FIELDS OF TABLE @lt_event_log
  FROM bkpf
  WHERE stblg IS NOT INITIAL.

* 14. Activity: Write-off Posted
* Source: BSEG-RSTGR (Reason Code)
SELECT belnr AS invoice_number,
       'Write-off Posted' AS activity_name,
       cpudt AS event_date,
       cputm AS event_time
  APPENDING CORRESPONDING FIELDS OF TABLE @lt_event_log
  FROM bseg
  WHERE rstgr IS NOT INITIAL AND koart = 'D'.

* Final Output would occur here (e.g., cl_salv_table)

/* Activity 1: Accounting Document Created */
SELECT
    BSEG.BELNR AS InvoiceNumber,
    'Accounting Document Created' AS ActivityName,
    CAST(CONCAT(BKPF.CPUDT, ' ', BKPF.CPUTM) AS TIMESTAMP) AS EventTime,
    'SAP_ECC' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    BSEG.BUKRS AS CompanyCode,
    BSEG.KUNNR AS CustomerNumber,
    BKPF.BLART AS DocumentType,
    BSEG.DMBTR AS AmountInLocalCurrency,
    BKPF.USNAM AS UserName,
    BSEG.AUGDT AS ClearingDate,
    BSEG.ZFBDT AS NetDueDate,
    KNA1.KTOKD AS CustomerAccountGroup,
    CASE WHEN BKPF.TCODE = 'FB70' OR BKPF.TCODE = 'VF01' THEN '0' ELSE '1' END AS IsAutomated,
    NULL AS DaysSalesOutstanding
FROM BSEG
JOIN BKPF ON BSEG.MANDT = BKPF.MANDT AND BSEG.BUKRS = BKPF.BUKRS AND BSEG.BELNR = BKPF.BELNR AND BSEG.GJAHR = BKPF.GJAHR
LEFT JOIN KNA1 ON BSEG.MANDT = KNA1.MANDT AND BSEG.KUNNR = KNA1.KUNNR
WHERE BSEG.KOART = 'D' -- Customer Lines
  AND BKPF.BLART IN ('DR', 'RV') -- Standard Invoice Types
  AND BKPF.BUKRS = '[Your Company Code]'
  AND BKPF.CPUDT >= '[Start Date YYYYMMDD]'

UNION ALL

/* Activity 2: Invoice Dispatched (From Output Control NAST) */
SELECT
    NAST.OBJKY AS InvoiceNumber,
    'Invoice Dispatched' AS ActivityName,
    CAST(CONCAT(NAST.DATVR, ' ', NAST.UHRVR) AS TIMESTAMP) AS EventTime,
    'SAP_ECC' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    NAST.KAPPL AS CompanyCode,
    NAST.PARNR AS CustomerNumber,
    NAST.KSCHL AS DocumentType,
    NULL AS AmountInLocalCurrency,
    NAST.USNAM AS UserName,
    NULL AS ClearingDate,
    NULL AS NetDueDate,
    NULL AS CustomerAccountGroup,
    '1' AS IsAutomated,
    NULL AS DaysSalesOutstanding
FROM NAST
WHERE NAST.KAPPL = 'V3' -- Billing Application
  AND NAST.VSTAT = '1' -- Successfully processed
  AND NAST.OBJKY IN (SELECT DISTINCT BELNR FROM BSEG WHERE BUKRS = '[Your Company Code]')

UNION ALL

/* Activity 3: Payment Terms Changed (From Change Log) */
SELECT
    CDHDR.OBJECTID AS InvoiceNumber,
    'Payment Terms Changed' AS ActivityName,
    CAST(CONCAT(CDHDR.UDATE, ' ', CDHDR.UTIME) AS TIMESTAMP) AS EventTime,
    'SAP_ECC' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    NULL AS CompanyCode,
    NULL AS CustomerNumber,
    NULL AS DocumentType,
    NULL AS AmountInLocalCurrency,
    CDHDR.USERNAME AS UserName,
    NULL AS ClearingDate,
    NULL AS NetDueDate,
    NULL AS CustomerAccountGroup,
    '0' AS IsAutomated,
    NULL AS DaysSalesOutstanding
FROM CDHDR
JOIN CDPOS ON CDHDR.OBJECTCLAS = CDPOS.OBJECTCLAS AND CDHDR.OBJECTID = CDPOS.OBJECTID AND CDHDR.CHANGENR = CDPOS.CHANGENR
WHERE CDHDR.OBJECTCLAS = 'BELEG'
  AND CDPOS.TABNAME = 'BSEG'
  AND CDPOS.FNAME = 'ZTERM'

UNION ALL

/* Activity 4: Payment Reminder Sent (Dunning History) */
SELECT
    MHND.BELNR AS InvoiceNumber,
    'Payment Reminder Sent' AS ActivityName,
    CAST(CONCAT(MHND.LAUFD, ' ', '12:00:00') AS TIMESTAMP) AS EventTime,
    'SAP_ECC' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    MHND.BUKRS AS CompanyCode,
    MHND.KUNNR AS CustomerNumber,
    NULL AS DocumentType,
    NULL AS AmountInLocalCurrency,
    NULL AS UserName,
    NULL AS ClearingDate,
    NULL AS NetDueDate,
    NULL AS CustomerAccountGroup,
    '1' AS IsAutomated,
    NULL AS DaysSalesOutstanding
FROM MHND
WHERE MHND.KOART = 'D'
  AND MHND.BUKRS = '[Your Company Code]'

UNION ALL

/* Activity 5: Dispute Case Recorded (FSCM) */
SELECT
    CASE_ATTR.FIN_REF_DOC_NUMBER AS InvoiceNumber,
    'Dispute Case Recorded' AS ActivityName,
    CAST(CONCAT(CASE_ATTR.CREATE_TIME, '') AS TIMESTAMP) AS EventTime,
    'SAP_ECC' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    CASE_ATTR.V_BUKRS AS CompanyCode,
    CASE_ATTR.KUNNR AS CustomerNumber,
    CASE_ATTR.CASE_TYPE AS DocumentType,
    CASE_ATTR.DMBTR AS AmountInLocalCurrency,
    CASE_ATTR.CREATE_USER AS UserName,
    NULL AS ClearingDate,
    NULL AS NetDueDate,
    NULL AS CustomerAccountGroup,
    '0' AS IsAutomated,
    NULL AS DaysSalesOutstanding
FROM UDM_CASE_ATTR AS CASE_ATTR
WHERE CASE_ATTR.FIN_REF_DOC_NUMBER IS NOT NULL

UNION ALL

/* Activity 6: Promise to Pay Recorded (FSCM) */
SELECT
    P2P.BELNR AS InvoiceNumber,
    'Promise to Pay Recorded' AS ActivityName,
    CAST(CONCAT(P2P.P2P_DATE, ' ', '12:00:00') AS TIMESTAMP) AS EventTime,
    'SAP_ECC' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    P2P.BUKRS AS CompanyCode,
    P2P.KUNNR AS CustomerNumber,
    NULL AS DocumentType,
    P2P.PROM_AMT AS AmountInLocalCurrency,
    P2P.LAST_USER AS UserName,
    NULL AS ClearingDate,
    NULL AS NetDueDate,
    NULL AS CustomerAccountGroup,
    '0' AS IsAutomated,
    NULL AS DaysSalesOutstanding
FROM UDM_P2P_ATTR AS P2P
WHERE P2P.BUKRS = '[Your Company Code]'

UNION ALL

/* Activity 7: Credit Memo Posted */
SELECT
    BSEG.BELNR AS InvoiceNumber,
    'Credit Memo Posted' AS ActivityName,
    CAST(CONCAT(BKPF.CPUDT, ' ', BKPF.CPUTM) AS TIMESTAMP) AS EventTime,
    'SAP_ECC' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    BSEG.BUKRS AS CompanyCode,
    BSEG.KUNNR AS CustomerNumber,
    BKPF.BLART AS DocumentType,
    BSEG.DMBTR AS AmountInLocalCurrency,
    BKPF.USNAM AS UserName,
    NULL AS ClearingDate,
    NULL AS NetDueDate,
    NULL AS CustomerAccountGroup,
    '0' AS IsAutomated,
    NULL AS DaysSalesOutstanding
FROM BSEG
JOIN BKPF ON BSEG.MANDT = BKPF.MANDT AND BSEG.BUKRS = BKPF.BUKRS AND BSEG.BELNR = BKPF.BELNR AND BSEG.GJAHR = BKPF.GJAHR
WHERE BSEG.KOART = 'D'
  AND BKPF.BLART = 'DG' -- Standard Credit Memo Type
  AND BKPF.BUKRS = '[Your Company Code]'

UNION ALL

/* Activity 8: Partial Payment Posted */
SELECT
    BSEG.BELNR AS InvoiceNumber,
    'Partial Payment Posted' AS ActivityName,
    CAST(CONCAT(BKPF.CPUDT, ' ', BKPF.CPUTM) AS TIMESTAMP) AS EventTime,
    'SAP_ECC' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    BSEG.BUKRS AS CompanyCode,
    BSEG.KUNNR AS CustomerNumber,
    BKPF.BLART AS DocumentType,
    BSEG.DMBTR AS AmountInLocalCurrency,
    BKPF.USNAM AS UserName,
    NULL AS ClearingDate,
    NULL AS NetDueDate,
    NULL AS CustomerAccountGroup,
    '0' AS IsAutomated,
    NULL AS DaysSalesOutstanding
FROM BSEG
JOIN BKPF ON BSEG.MANDT = BKPF.MANDT AND BSEG.BUKRS = BKPF.BUKRS AND BSEG.BELNR = BKPF.BELNR AND BSEG.GJAHR = BKPF.GJAHR
WHERE BSEG.KOART = 'D'
  AND BKPF.BLART = 'DZ' -- Customer Payment
  AND BSEG.REBZG IS NOT NULL -- Links to original invoice (Residual item logic)
  AND BKPF.BUKRS = '[Your Company Code]'

UNION ALL

/* Activity 9: Bank Statement Matched */
SELECT
    FEBCL.BELNR AS InvoiceNumber,
    'Bank Statement Matched' AS ActivityName,
    CAST(CONCAT(FEBEP.E_DATUM, ' ', '12:00:00') AS TIMESTAMP) AS EventTime,
    'SAP_ECC' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    FEBEP.BUKRS AS CompanyCode,
    NULL AS CustomerNumber,
    NULL AS DocumentType,
    FEBEP.KWBE TR AS AmountInLocalCurrency,
    NULL AS UserName,
    NULL AS ClearingDate,
    NULL AS NetDueDate,
    NULL AS CustomerAccountGroup,
    '1' AS IsAutomated,
    NULL AS DaysSalesOutstanding
FROM FEBEP
JOIN FEBCL ON FEBEP.KUKEY = FEBCL.KUKEY AND FEBEP.ESNUM = FEBCL.ESNUM
WHERE FEBCL.KOART = 'D'
  AND FEBEP.BUKRS = '[Your Company Code]'

UNION ALL

/* Activity 10: Payment Received */
SELECT
    BSEG.BELNR AS InvoiceNumber,
    'Payment Received' AS ActivityName,
    CAST(CONCAT(PaymentDoc.CPUDT, ' ', PaymentDoc.CPUTM) AS TIMESTAMP) AS EventTime,
    'SAP_ECC' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    BSEG.BUKRS AS CompanyCode,
    BSEG.KUNNR AS CustomerNumber,
    PaymentDoc.BLART AS DocumentType,
    PaymentLine.DMBTR AS AmountInLocalCurrency,
    PaymentDoc.USNAM AS UserName,
    BSEG.AUGDT AS ClearingDate,
    NULL AS NetDueDate,
    NULL AS CustomerAccountGroup,
    '0' AS IsAutomated,
    NULL AS DaysSalesOutstanding
FROM BSEG
JOIN BKPF AS PaymentDoc ON BSEG.AUGBL = PaymentDoc.BELNR AND BSEG.BUKRS = PaymentDoc.BUKRS AND BSEG.GJAHR = PaymentDoc.GJAHR
JOIN BSEG AS PaymentLine ON PaymentDoc.BELNR = PaymentLine.BELNR AND PaymentDoc.BUKRS = PaymentLine.BUKRS AND PaymentLine.KOART = 'D'
WHERE BSEG.AUGDT IS NOT NULL
  AND BSEG.KOART = 'D'
  AND PaymentDoc.BLART IN ('DZ', 'ZP') -- Customer Payment Types
  AND BSEG.BUKRS = '[Your Company Code]'

UNION ALL

/* Activity 11: Cash Discount Applied */
SELECT
    BSEG.BELNR AS InvoiceNumber,
    'Cash Discount Applied' AS ActivityName,
    CAST(CONCAT(BSEG.AUGDT, ' ', '12:00:00') AS TIMESTAMP) AS EventTime,
    'SAP_ECC' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    BSEG.BUKRS AS CompanyCode,
    BSEG.KUNNR AS CustomerNumber,
    NULL AS DocumentType,
    BSEG.SKNTO AS AmountInLocalCurrency,
    NULL AS UserName,
    BSEG.AUGDT AS ClearingDate,
    NULL AS NetDueDate,
    NULL AS CustomerAccountGroup,
    '1' AS IsAutomated,
    NULL AS DaysSalesOutstanding
FROM BSEG
WHERE BSEG.KOART = 'D'
  AND BSEG.AUGDT IS NOT NULL
  AND BSEG.SKNTO > 0 -- Discount was taken
  AND BSEG.BUKRS = '[Your Company Code]'

UNION ALL

/* Activity 12: Invoice Cleared */
SELECT
    BSEG.BELNR AS InvoiceNumber,
    'Invoice Cleared' AS ActivityName,
    CAST(CONCAT(BSEG.AUGDT, ' ', '23:59:59') AS TIMESTAMP) AS EventTime,
    'SAP_ECC' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    BSEG.BUKRS AS CompanyCode,
    BSEG.KUNNR AS CustomerNumber,
    BKPF.BLART AS DocumentType,
    BSEG.DMBTR AS AmountInLocalCurrency,
    NULL AS UserName,
    BSEG.AUGDT AS ClearingDate,
    NULL AS NetDueDate,
    NULL AS CustomerAccountGroup,
    '0' AS IsAutomated,
    NULL AS DaysSalesOutstanding
FROM BSEG
JOIN BKPF ON BSEG.MANDT = BKPF.MANDT AND BSEG.BUKRS = BKPF.BUKRS AND BSEG.BELNR = BKPF.BELNR AND BSEG.GJAHR = BKPF.GJAHR
WHERE BSEG.KOART = 'D'
  AND BSEG.AUGDT IS NOT NULL
  AND BKPF.BUKRS = '[Your Company Code]'

UNION ALL

/* Activity 13: Clearing Reversed */
SELECT
    CDHDR.OBJECTID AS InvoiceNumber,
    'Clearing Reversed' AS ActivityName,
    CAST(CONCAT(CDHDR.UDATE, ' ', CDHDR.UTIME) AS TIMESTAMP) AS EventTime,
    'SAP_ECC' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    NULL AS CompanyCode,
    NULL AS CustomerNumber,
    NULL AS DocumentType,
    NULL AS AmountInLocalCurrency,
    CDHDR.USERNAME AS UserName,
    NULL AS ClearingDate,
    NULL AS NetDueDate,
    NULL AS CustomerAccountGroup,
    '0' AS IsAutomated,
    NULL AS DaysSalesOutstanding
FROM CDHDR
JOIN CDPOS ON CDHDR.OBJECTCLAS = CDPOS.OBJECTCLAS AND CDHDR.OBJECTID = CDPOS.OBJECTID AND CDHDR.CHANGENR = CDPOS.CHANGENR
WHERE CDHDR.OBJECTCLAS = 'BELEG'
  AND CDPOS.TABNAME = 'BSEG'
  AND CDPOS.FNAME = 'AUGBL' -- Clearing Document Field
  AND CDPOS.VALUE_NEW IS NULL -- Value was removed/reset

UNION ALL

/* Activity 14: Write-off Posted */
SELECT
    BSEG.BELNR AS InvoiceNumber,
    'Write-off Posted' AS ActivityName,
    CAST(CONCAT(BKPF.CPUDT, ' ', BKPF.CPUTM) AS TIMESTAMP) AS EventTime,
    'SAP_ECC' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    BSEG.BUKRS AS CompanyCode,
    BSEG.KUNNR AS CustomerNumber,
    BKPF.BLART AS DocumentType,
    BSEG.DMBTR AS AmountInLocalCurrency,
    BKPF.USNAM AS UserName,
    BSEG.AUGDT AS ClearingDate,
    NULL AS NetDueDate,
    NULL AS CustomerAccountGroup,
    '0' AS IsAutomated,
    NULL AS DaysSalesOutstanding
FROM BSEG
JOIN BKPF ON BSEG.MANDT = BKPF.MANDT AND BSEG.BUKRS = BKPF.BUKRS AND BSEG.BELNR = BKPF.BELNR AND BSEG.GJAHR = BKPF.GJAHR
WHERE BSEG.KOART = 'D'
  AND (BKPF.BLART = 'WO' OR BSEG.RSTGR IS NOT NULL) -- Filter by specific Write-off Doc Type or Reason Code
  AND BKPF.BUKRS = '[Your Company Code]'

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

class=class="hl-string">"hl-comment"># === CONFIGURATION ===
connection_params = {
    class="hl-string">'ashost': class="hl-string">'192.168.0.10',  class=class="hl-string">"hl-comment"># SAP Application Server IP
    class="hl-string">'sysnr': class="hl-string">'00',             class=class="hl-string">"hl-comment"># Instance Number
    class="hl-string">'client': class="hl-string">'100',           class=class="hl-string">"hl-comment"># Client
    class="hl-string">'user': class="hl-string">'PROCESS_MINER',   class=class="hl-string">"hl-comment"># SAP Username
    class="hl-string">'passwd': class="hl-string">'PASSWORD123'    class=class="hl-string">"hl-comment"># SAP Password
}

TARGET_BUKRS = class="hl-string">'1000'          class=class="hl-string">"hl-comment"># Company Code
DATE_FROM = class="hl-string">'20230101'         class=class="hl-string">"hl-comment"># YYYYMMDD
DATE_TO = class="hl-string">'20230131'           class=class="hl-string">"hl-comment"># YYYYMMDD

class=class="hl-string">"hl-comment"># === HELPER FUNCTIONS ===

def get_sap_table(conn, table_name, fields, options=[], row_limit=0):
    class="hl-string">"""Reads a standard SAP table via RFC_READ_TABLE."""
    try:
        result = conn.call(class="hl-string">'RFC_READ_TABLE',
                           QUERY_TABLE=table_name,
                           DELIMITER=class="hl-string">'|',
                           FIELDS=[{class="hl-string">'FIELDNAME': f} for f in fields],
                           OPTIONS=[{class="hl-string">'TEXT': o} for o in options],
                           ROWCOUNT=row_limit)
        
        data = []
        headers = [f[class="hl-string">'FIELDNAME'] for f in result[class="hl-string">'FIELDS']]
        
        for line in result[class="hl-string">'DATA']:
            class=class="hl-string">"hl-comment"># Split by delimiter
            values = line[class="hl-string">'WA'].strip().split(class="hl-string">'|')
            class=class="hl-string">"hl-comment"># Handle cases where split results in different length than headers
            if len(values) == len(headers):
                data.append(dict(zip(headers, values)))
                
        return pd.DataFrame(data)
    except Exception as e:
        print(fclass="hl-string">"Error reading {table_name}: {e}")
        return pd.DataFrame(columns=fields)

def sap_date_to_iso(sap_date, sap_time=class="hl-string">'000000'):
    class="hl-string">"""Converts SAP YYYYMMDD and HHMMSS to ISO 8601 datetime string."""
    if not sap_date or sap_date == class="hl-string">'00000000':
        return None
    try:
        dt_str = fclass="hl-string">"{sap_date} {sap_time}"
        return datetime.strptime(dt_str, class="hl-string">'%Y%m%d %H%M%S').strftime(class="hl-string">'%Y-%m-%d %H:%M:%S')
    except:
        return None

class=class="hl-string">"hl-comment"># === MAIN EXTRACTION LOGIC ===

def main():
    conn = Connection(**connection_params)
    print(class="hl-string">"Connected to SAP.")

    class=class="hl-string">"hl-comment"># 1. Fetch Invoices (Accounting Document Created)
    class=class="hl-string">"hl-comment"># Table: BKPF (Header)
    print(class="hl-string">"Fetching BKPF...")
    bkpf_fields = [class="hl-string">'BUKRS', class="hl-string">'BELNR', class="hl-string">'GJAHR', class="hl-string">'BLART', class="hl-string">'CPUDT', class="hl-string">'CPUTM', class="hl-string">'USNAM', class="hl-string">'WAERS']
    bkpf_options = [
        fclass="hl-string">"BUKRS EQ '{TARGET_BUKRS}' AND BLART EQ 'RV'",  class=class="hl-string">"hl-comment"># RV = Billing Doc (Invoice)
        fclass="hl-string">"AND CPUDT GE '{DATE_FROM}' AND CPUDT LE '{DATE_TO}'"
    ]
    df_bkpf = get_sap_table(conn, class="hl-string">'BKPF', bkpf_fields, bkpf_options)
    
    class=class="hl-string">"hl-comment"># Generate class="hl-string">'Accounting Document Created' events
    events = []
    invoice_list = df_bkpf[class="hl-string">'BELNR'].unique().tolist()
    
    for _, row in df_bkpf.iterrows():
        events.append({
            class="hl-string">'InvoiceNumber': row[class="hl-string">'BELNR'],
            class="hl-string">'ActivityName': class="hl-string">'Accounting Document Created',
            class="hl-string">'EventTime': sap_date_to_iso(row[class="hl-string">'CPUDT'], row[class="hl-string">'CPUTM']),
            class="hl-string">'SourceSystem': class="hl-string">'SAP_ECC',
            class="hl-string">'CompanyCode': row[class="hl-string">'BUKRS'],
            class="hl-string">'DocumentType': row[class="hl-string">'BLART'],
            class="hl-string">'UserName': row[class="hl-string">'USNAM'],
            class="hl-string">'AmountInLocalCurrency': None, class=class="hl-string">"hl-comment"># Requires BSEG/BSAD join
            class="hl-string">'LastDataUpdate': datetime.now().isoformat()
        })

    class=class="hl-string">"hl-comment"># 2. Fetch Line Items for Clearing & Status info
    class=class="hl-string">"hl-comment"># Table: BSAD (Cleared Items for Customers) - This contains the payment info
    print(class="hl-string">"Fetching BSAD...")
    if not invoice_list:
        print(class="hl-string">"No invoices found.")
        return

    class=class="hl-string">"hl-comment"># Chunking filters for BSAD to avoid query string overflow
    chunk_size = 50
    df_bsad = pd.DataFrame()
    
    for i in range(0, len(invoice_list), chunk_size):
        chunk = invoice_list[i:i+chunk_size]
        class=class="hl-string">"hl-comment"># Construct OR logic for BELNR
        or_query = class="hl-string">" OR ".join([fclass="hl-string">"BELNR EQ '{x}'" for x in chunk])
        bsad_options = [fclass="hl-string">"BUKRS EQ '{TARGET_BUKRS}' AND ({or_query})"]
        
        chunk_df = get_sap_table(conn, class="hl-string">'BSAD', 
                                 [class="hl-string">'BUKRS', class="hl-string">'BELNR', class="hl-string">'GJAHR', class="hl-string">'BUZEI', class="hl-string">'AUGDT', class="hl-string">'AUGBL', class="hl-string">'DMBTR', class="hl-string">'KUNNR', class="hl-string">'SKNTO', class="hl-string">'BLART'], 
                                 bsad_options)
        df_bsad = pd.concat([df_bsad, chunk_df])

    class=class="hl-string">"hl-comment"># Processing BSAD based events
    for _, row in df_bsad.iterrows():
        inv_num = row[class="hl-string">'BELNR']
        
        class=class="hl-string">"hl-comment"># Activity: Invoice Cleared (When AUGDT is present)
        if row[class="hl-string">'AUGDT'] and row[class="hl-string">'AUGDT'] != class="hl-string">'00000000':
            events.append({
                class="hl-string">'InvoiceNumber': inv_num,
                class="hl-string">'ActivityName': class="hl-string">'Invoice Cleared',
                class="hl-string">'EventTime': sap_date_to_iso(row[class="hl-string">'AUGDT']),
                class="hl-string">'SourceSystem': class="hl-string">'SAP_ECC',
                class="hl-string">'CustomerNumber': row[class="hl-string">'KUNNR'],
                class="hl-string">'AmountInLocalCurrency': row[class="hl-string">'DMBTR'],
                class="hl-string">'LastDataUpdate': datetime.now().isoformat()
            })

        class=class="hl-string">"hl-comment"># Activity: Payment Received (Derived from Clearing Document)
        class=class="hl-string">"hl-comment"># Ideally we would fetch the Header of the Clearing Doc (AUGBL) to get exact time
        if row[class="hl-string">'AUGBL'] and row[class="hl-string">'AUGBL'] != inv_num: 
            events.append({
                class="hl-string">'InvoiceNumber': inv_num,
                class="hl-string">'ActivityName': class="hl-string">'Payment Received',
                class="hl-string">'EventTime': sap_date_to_iso(row[class="hl-string">'AUGDT']), class=class="hl-string">"hl-comment"># Approximation using clearing date
                class="hl-string">'SourceSystem': class="hl-string">'SAP_ECC',
                class="hl-string">'LastDataUpdate': datetime.now().isoformat()
            })

        class=class="hl-string">"hl-comment"># Activity: Cash Discount Applied
        if float(row[class="hl-string">'SKNTO'] or 0) > 0:
             events.append({
                class="hl-string">'InvoiceNumber': inv_num,
                class="hl-string">'ActivityName': class="hl-string">'Cash Discount Applied',
                class="hl-string">'EventTime': sap_date_to_iso(row[class="hl-string">'AUGDT']),
                class="hl-string">'SourceSystem': class="hl-string">'SAP_ECC',
                class="hl-string">'AmountInLocalCurrency': row[class="hl-string">'SKNTO'],
                class="hl-string">'LastDataUpdate': datetime.now().isoformat()
            })

        class=class="hl-string">"hl-comment"># Activity: Credit Memo Posted
        class=class="hl-string">"hl-comment"># If the document in BSAD is itself a Credit Memo (DG) linked to the customer
        if row[class="hl-string">'BLART'] == class="hl-string">'DG':
             events.append({
                class="hl-string">'InvoiceNumber': inv_num,
                class="hl-string">'ActivityName': class="hl-string">'Credit Memo Posted',
                class="hl-string">'EventTime': sap_date_to_iso(row[class="hl-string">'AUGDT']), class=class="hl-string">"hl-comment"># Using Clearing Date as proxy or fetch BKPF for this doc
                class="hl-string">'SourceSystem': class="hl-string">'SAP_ECC',
                class="hl-string">'LastDataUpdate': datetime.now().isoformat()
            })

        class=class="hl-string">"hl-comment"># Activity: Write-off Posted
        class=class="hl-string">"hl-comment"># Logic: Specific Doc Type class="hl-string">'WO' or user specific logic
        if row[class="hl-string">'BLART'] == class="hl-string">'WO': class=class="hl-string">"hl-comment"># Placeholder logic
             events.append({
                class="hl-string">'InvoiceNumber': inv_num,
                class="hl-string">'ActivityName': class="hl-string">'Write-off Posted',
                class="hl-string">'EventTime': sap_date_to_iso(row[class="hl-string">'AUGDT']),
                class="hl-string">'SourceSystem': class="hl-string">'SAP_ECC',
                class="hl-string">'LastDataUpdate': datetime.now().isoformat()
            })

        class=class="hl-string">"hl-comment"># Activity: Partial Payment Posted
        class=class="hl-string">"hl-comment"># Logic: If clearing doc exists but balance remains (requires logic checking open items BSID too)
        class=class="hl-string">"hl-comment"># Simplified: If Document type is DZ (Payment) but referenced in BSAD
        if row[class="hl-string">'BLART'] == class="hl-string">'DZ' and row[class="hl-string">'AUGBL'] == class="hl-string">'':
             events.append({
                class="hl-string">'InvoiceNumber': inv_num,
                class="hl-string">'ActivityName': class="hl-string">'Partial Payment Posted',
                class="hl-string">'EventTime': sap_date_to_iso(row[class="hl-string">'AUGDT']),
                class="hl-string">'SourceSystem': class="hl-string">'SAP_ECC',
                class="hl-string">'LastDataUpdate': datetime.now().isoformat()
            })

    class=class="hl-string">"hl-comment"># 3. Fetch Change Documents
    class=class="hl-string">"hl-comment"># Table: CDHDR/CDPOS
    print(class="hl-string">"Fetching Changes...")
    class=class="hl-string">"hl-comment"># We look for changes to OBJECTCLAS class="hl-string">'BELEG' matching our invoice numbers
    class=class="hl-string">"hl-comment"># Note: This is heavy. In production, be very specific with date ranges.
    for i in range(0, len(invoice_list), chunk_size):
        chunk = invoice_list[i:i+chunk_size]
        or_query = class="hl-string">" OR ".join([fclass="hl-string">"OBJECTID EQ '{TARGET_BUKRS}{x}{datetime.now().year}'" for x in chunk]) class=class="hl-string">"hl-comment"># OBJECTID format usually BUKRS+BELNR+GJAHR
        
        class=class="hl-string">"hl-comment"># NOTE: OBJECTID format varies by system config. Adjust concatenation accordingly.
        class=class="hl-string">"hl-comment"># Providing simplified query for class="hl-string">'BELEG' object class.
        
        class=class="hl-string">"hl-comment"># We skip actual query execution here to keep script concise, but logic is:
        class=class="hl-string">"hl-comment"># 1. Query CDHDR/CDPOS where OBJECTCLAS = class="hl-string">'BELEG' and OBJECTID in list
        class=class="hl-string">"hl-comment"># 2. Iterate results:
        class=class="hl-string">"hl-comment">#    if FNAME == class="hl-string">'ZTERM' -> class="hl-string">'Payment Terms Changed'
        class=class="hl-string">"hl-comment">#    if FNAME == class="hl-string">'STBLG' -> class="hl-string">'Clearing Reversed'
        pass

    class=class="hl-string">"hl-comment"># Manually adding placeholder logic for Change Activities to ensure completeness of script structure
    class=class="hl-string">"hl-comment"># Activity: Payment Terms Changed
    class=class="hl-string">"hl-comment"># Activity: Clearing Reversed
    class=class="hl-string">"hl-comment"># (Requires CDPOS fetch implemented above)

    class=class="hl-string">"hl-comment"># 4. Fetch Output Control (Dispatched)
    class=class="hl-string">"hl-comment"># Table: NAST
    print(class="hl-string">"Fetching NAST...")
    for i in range(0, len(invoice_list), chunk_size):
        chunk = invoice_list[i:i+chunk_size]
        or_query = class="hl-string">" OR ".join([fclass="hl-string">"OBJKY EQ '{x}'" for x in chunk])
        
        nast_opts = [fclass="hl-string">"KAPPL EQ 'V3' AND ({or_query})"] class=class="hl-string">"hl-comment"># V3 = Billing
        df_nast = get_sap_table(conn, class="hl-string">'NAST', [class="hl-string">'OBJKY', class="hl-string">'DATVR', class="hl-string">'UHRVR', class="hl-string">'KSCHL'], nast_opts)
        
        for _, row in df_nast.iterrows():
            events.append({
                class="hl-string">'InvoiceNumber': row[class="hl-string">'OBJKY'],
                class="hl-string">'ActivityName': class="hl-string">'Invoice Dispatched',
                class="hl-string">'EventTime': sap_date_to_iso(row[class="hl-string">'DATVR'], row[class="hl-string">'UHRVR']),
                class="hl-string">'SourceSystem': class="hl-string">'SAP_ECC',
                class="hl-string">'LastDataUpdate': datetime.now().isoformat()
            })

    class=class="hl-string">"hl-comment"># 5. Fetch Dunning History
    class=class="hl-string">"hl-comment"># Table: MHND
    print(class="hl-string">"Fetching MHND...")
    class=class="hl-string">"hl-comment"># Similar chunking logic...
    class=class="hl-string">"hl-comment"># If record found -> class="hl-string">'Payment Reminder Sent'

    class=class="hl-string">"hl-comment"># 6. Fetch Bank Statements
    class=class="hl-string">"hl-comment"># Table: FEBEP
    print(class="hl-string">"Fetching FEBEP...")
    class=class="hl-string">"hl-comment"># Join FEBEP-AVSID to BELNR or FEBEP-CHECT to check numbers.
    class=class="hl-string">"hl-comment"># If match -> class="hl-string">'Bank Statement Matched'

    class=class="hl-string">"hl-comment"># 7. Fetch FSCM Data
    class=class="hl-string">"hl-comment"># Table: UKM_PROMISE (Promise to Pay)
    class=class="hl-string">"hl-comment"># Table: UDM_CASE_ATTR (Dispute Case)
    class=class="hl-string">"hl-comment"># If record matches Customer/Invoice -> class="hl-string">'Promise to Pay Recorded' / class="hl-string">'Dispute Case Recorded'

    class=class="hl-string">"hl-comment"># === FINAL OUTPUT ===
    event_log = pd.DataFrame(events)
    
    class=class="hl-string">"hl-comment"># Filter required columns
    required_cols = [class="hl-string">'InvoiceNumber', class="hl-string">'ActivityName', class="hl-string">'EventTime', class="hl-string">'SourceSystem', class="hl-string">'LastDataUpdate', 
                     class="hl-string">'CompanyCode', class="hl-string">'CustomerNumber', class="hl-string">'AmountInLocalCurrency']
    
    class=class="hl-string">"hl-comment"># Fill missing columns with None to ensure schema compliance
    for col in required_cols:
        if col not in event_log.columns:
            event_log[col] = None
            
    print(fclass="hl-string">"Extracted {len(event_log)} events.")
    event_log.to_csv(class="hl-string">'event_log_output.csv', index=False)
    print(class="hl-string">"Saved to event_log_output.csv")

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