債権管理・回収データテンプレート

SELECT
    INV.AccountingDocument AS "InvoiceNumber",
    'Invoice Generated' AS "ActivityName",
    INV.CreationDateTime AS "EventTime",
    INV.CreatedByUser AS "UserName",
    INV.Customer AS "CustomerNumber",
    INV.AmountInTransactionCurrency AS "InvoiceAmount",
    INV.NetDueDate AS "PaymentDueDate",
    0 AS "DaysOverdue"
FROM I_OperationalAcctgDocItemCube AS INV
WHERE INV.CompanyCode IN ('[Your Company Code]') AND INV.AccountingDocumentType = '[Your Invoice Doc Type]' AND INV.CreationDate BETWEEN '[Start Date]' AND '[End Date]'

UNION ALL

SELECT
    INV.AccountingDocument AS "InvoiceNumber",
    'Invoice Sent To Customer' AS "ActivityName",
    OUTP.OutputRequestDateTime AS "EventTime",
    OUTP.LastChangedByUser AS "UserName",
    INV.Customer AS "CustomerNumber",
    INV.AmountInTransactionCurrency AS "InvoiceAmount",
    INV.NetDueDate AS "PaymentDueDate",
    CASE WHEN INV.NetDueDate < CAST(OUTP.OutputRequestDateTime AS DATE) THEN DATEDIFF(day, INV.NetDueDate, CAST(OUTP.OutputRequestDateTime AS DATE)) ELSE 0 END AS "DaysOverdue"
FROM I_BillingDocumentOutputRequest AS OUTP
JOIN I_OperationalAcctgDocItemCube AS INV ON OUTP.BillingDocument = INV.AccountingDocument AND INV.CompanyCode IN ('[Your Company Code]') AND INV.AccountingDocumentType = '[Your Invoice Doc Type]'
WHERE CAST(OUTP.OutputRequestDateTime AS DATE) BETWEEN '[Start Date]' AND '[End Date]'

UNION ALL

SELECT
    INV.AccountingDocument AS "InvoiceNumber",
    'Payment Due Date Passed' AS "ActivityName",
    INV.NetDueDate AS "EventTime",
    'SYSTEM' AS "UserName",
    INV.Customer AS "CustomerNumber",
    INV.AmountInTransactionCurrency AS "InvoiceAmount",
    INV.NetDueDate AS "PaymentDueDate",
    0 AS "DaysOverdue"
FROM I_OperationalAcctgDocItemCube AS INV
WHERE INV.CompanyCode IN ('[Your Company Code]') AND INV.AccountingDocumentType = '[Your Invoice Doc Type]' AND INV.IsCleared = '' AND INV.NetDueDate BETWEEN '[Start Date]' AND '[End Date]' AND INV.NetDueDate < CURRENT_DATE

UNION ALL

SELECT
    REL.RelatedAccountingDocument AS "InvoiceNumber",
    'Dispute Case Registered' AS "ActivityName",
    DCASE.CreationDateTime AS "EventTime",
    DCASE.CreatedByUser AS "UserName",
    INV.Customer AS "CustomerNumber",
    INV.AmountInTransactionCurrency AS "InvoiceAmount",
    INV.NetDueDate AS "PaymentDueDate",
    CASE WHEN INV.NetDueDate < CAST(DCASE.CreationDateTime AS DATE) THEN DATEDIFF(day, INV.NetDueDate, CAST(DCASE.CreationDateTime AS DATE)) ELSE 0 END AS "DaysOverdue"
FROM I_DisputeCase AS DCASE
JOIN I_DisputeCaseRelatedObject AS REL ON DCASE.DisputeCase = REL.DisputeCase
JOIN I_OperationalAcctgDocItemCube AS INV ON REL.RelatedAccountingDocument = INV.AccountingDocument AND REL.RelatedCompanyCode = INV.CompanyCode AND INV.AccountingDocumentType = '[Your Invoice Doc Type]'
WHERE INV.CompanyCode IN ('[Your Company Code]') AND CAST(DCASE.CreationDateTime AS DATE) BETWEEN '[Start Date]' AND '[End Date]'

UNION ALL

SELECT
    DUN.AccountingDocument AS "InvoiceNumber",
    'Dunning Procedure Initiated' AS "ActivityName",
    DUN.DunningDate AS "EventTime",
    DUN.DunningRunUser AS "UserName",
    INV.Customer AS "CustomerNumber",
    INV.AmountInTransactionCurrency AS "InvoiceAmount",
    INV.NetDueDate AS "PaymentDueDate",
    CASE WHEN INV.NetDueDate < DUN.DunningDate THEN DATEDIFF(day, INV.NetDueDate, DUN.DunningDate) ELSE 0 END AS "DaysOverdue"
FROM I_DunningHistoryItem AS DUN
JOIN I_OperationalAcctgDocItemCube AS INV ON DUN.AccountingDocument = INV.AccountingDocument AND DUN.CompanyCode = INV.CompanyCode AND INV.AccountingDocumentType = '[Your Invoice Doc Type]'
WHERE DUN.CompanyCode IN ('[Your Company Code]') AND DUN.DunningDate BETWEEN '[Start Date]' AND '[End Date]'

UNION ALL

-- Note: This activity requires a custom view. Replace '[Your Custom Contact Log View]' with your specific implementation.
SELECT
    LOG.InvoiceReference AS "InvoiceNumber",
    'Collection Call Made' AS "ActivityName",
    LOG.CreationDateTime AS "EventTime",
    LOG.CreatedByUser AS "UserName",
    INV.Customer AS "CustomerNumber",
    INV.AmountInTransactionCurrency AS "InvoiceAmount",
    INV.NetDueDate AS "PaymentDueDate",
    CASE WHEN INV.NetDueDate < CAST(LOG.CreationDateTime AS DATE) THEN DATEDIFF(day, INV.NetDueDate, CAST(LOG.CreationDateTime AS DATE)) ELSE 0 END AS "DaysOverdue"
FROM [Your Custom Contact Log View] AS LOG
JOIN I_OperationalAcctgDocItemCube AS INV ON LOG.InvoiceReference = INV.AccountingDocument AND INV.CompanyCode IN ('[Your Company Code]') AND INV.AccountingDocumentType = '[Your Invoice Doc Type]'
WHERE CAST(LOG.CreationDateTime AS DATE) BETWEEN '[Start Date]' AND '[End Date]'

UNION ALL

SELECT
    P2P.OriginalReferenceDocument AS "InvoiceNumber",
    'Promise To Pay Created' AS "ActivityName",
    P2P.CreationDateTime AS "EventTime",
    P2P.CreatedByUser AS "UserName",
    INV.Customer AS "CustomerNumber",
    INV.AmountInTransactionCurrency AS "InvoiceAmount",
    INV.NetDueDate AS "PaymentDueDate",
    CASE WHEN INV.NetDueDate < CAST(P2P.CreationDateTime AS DATE) THEN DATEDIFF(day, INV.NetDueDate, CAST(P2P.CreationDateTime AS DATE)) ELSE 0 END AS "DaysOverdue"
FROM I_PromiseToPay AS P2P
JOIN I_OperationalAcctgDocItemCube AS INV ON P2P.OriginalReferenceDocument = INV.AccountingDocument AND P2P.CompanyCode = INV.CompanyCode AND INV.AccountingDocumentType = '[Your Invoice Doc Type]'
WHERE P2P.CompanyCode IN ('[Your Company Code]') AND CAST(P2P.CreationDateTime AS DATE) BETWEEN '[Start Date]' AND '[End Date]'

UNION ALL

SELECT
    INV.AccountingDocument AS "InvoiceNumber",
    'Payment Received' AS "ActivityName",
    INV.ClearingDate AS "EventTime",
    INV.ClearingJournalEntryUser AS "UserName",
    INV.Customer AS "CustomerNumber",
    INV.AmountInTransactionCurrency AS "InvoiceAmount",
    INV.NetDueDate AS "PaymentDueDate",
    CASE WHEN INV.NetDueDate < INV.ClearingDate THEN DATEDIFF(day, INV.NetDueDate, INV.ClearingDate) ELSE 0 END AS "DaysOverdue"
FROM I_OperationalAcctgDocItemCube AS INV
WHERE INV.CompanyCode IN ('[Your Company Code]') AND INV.AccountingDocumentType = '[Your Invoice Doc Type]' AND INV.IsCleared = 'X' AND INV.ClearingDate BETWEEN '[Start Date]' AND '[End Date]'

UNION ALL

SELECT
    INV.AccountingDocument AS "InvoiceNumber",
    'Payment Posted' AS "ActivityName",
    INV.ClearingDate AS "EventTime", -- Often same as Payment Received timestamp
    INV.ClearingJournalEntryUser AS "UserName",
    INV.Customer AS "CustomerNumber",
    INV.AmountInTransactionCurrency AS "InvoiceAmount",
    INV.NetDueDate AS "PaymentDueDate",
    CASE WHEN INV.NetDueDate < INV.ClearingDate THEN DATEDIFF(day, INV.NetDueDate, INV.ClearingDate) ELSE 0 END AS "DaysOverdue"
FROM I_OperationalAcctgDocItemCube AS INV
WHERE INV.CompanyCode IN ('[Your Company Code]') AND INV.AccountingDocumentType = '[Your Invoice Doc Type]' AND INV.IsCleared = 'X' AND INV.ClearingDate BETWEEN '[Start Date]' AND '[End Date]'

UNION ALL

SELECT
    REL.RelatedAccountingDocument AS "InvoiceNumber",
    'Dispute Resolved' AS "ActivityName",
    DCASE.LastChangeDateTime AS "EventTime",
    DCASE.LastChangedByUser AS "UserName",
    INV.Customer AS "CustomerNumber",
    INV.AmountInTransactionCurrency AS "InvoiceAmount",
    INV.NetDueDate AS "PaymentDueDate",
    CASE WHEN INV.NetDueDate < CAST(DCASE.LastChangeDateTime AS DATE) THEN DATEDIFF(day, INV.NetDueDate, CAST(DCASE.LastChangeDateTime AS DATE)) ELSE 0 END AS "DaysOverdue"
FROM I_DisputeCase AS DCASE
JOIN I_DisputeCaseRelatedObject AS REL ON DCASE.DisputeCase = REL.DisputeCase
JOIN I_OperationalAcctgDocItemCube AS INV ON REL.RelatedAccountingDocument = INV.AccountingDocument AND REL.RelatedCompanyCode = INV.CompanyCode AND INV.AccountingDocumentType = '[Your Invoice Doc Type]'
WHERE INV.CompanyCode IN ('[Your Company Code]') AND DCASE.DisputeCaseProcessingStatus = '[Your Dispute Case Closed Status]' AND CAST(DCASE.LastChangeDateTime AS DATE) BETWEEN '[Start Date]' AND '[End Date]'

UNION ALL

SELECT
    INV.AccountingDocument AS "InvoiceNumber",
    'Invoice Settled' AS "ActivityName",
    INV.ClearingDate AS "EventTime",
    INV.ClearingJournalEntryUser AS "UserName",
    INV.Customer AS "CustomerNumber",
    INV.AmountInTransactionCurrency AS "InvoiceAmount",
    INV.NetDueDate AS "PaymentDueDate",
    CASE WHEN INV.NetDueDate < INV.ClearingDate THEN DATEDIFF(day, INV.NetDueDate, INV.ClearingDate) ELSE 0 END AS "DaysOverdue"
FROM I_OperationalAcctgDocItemCube AS INV
WHERE INV.CompanyCode IN ('[Your Company Code]') AND INV.AccountingDocumentType = '[Your Invoice Doc Type]' AND INV.IsCleared = 'X' AND INV.ClearingDate BETWEEN '[Start Date]' AND '[End Date]'

UNION ALL

SELECT
    INV.AccountingDocument AS "InvoiceNumber",
    'Invoice Written Off' AS "ActivityName",
    INV.ClearingDate AS "EventTime",
    INV.ClearingJournalEntryUser AS "UserName",
    INV.Customer AS "CustomerNumber",
    INV.AmountInTransactionCurrency AS "InvoiceAmount",
    INV.NetDueDate AS "PaymentDueDate",
    CASE WHEN INV.NetDueDate < INV.ClearingDate THEN DATEDIFF(day, INV.NetDueDate, INV.ClearingDate) ELSE 0 END AS "DaysOverdue"
FROM I_OperationalAcctgDocItemCube AS INV
WHERE INV.CompanyCode IN ('[Your Company Code]') AND INV.AccountingDocumentType = '[Your Invoice Doc Type]' AND INV.IsCleared = 'X' AND INV.ClearingReason = '[Your Write-off Reason Code]' AND INV.ClearingDate BETWEEN '[Start Date]' AND '[End Date]'

SELECT
    INV.AccountingDocument AS "InvoiceNumber",
    'Invoice Generated' AS "ActivityName",
    INV.CreationDateTime AS "EventTime",
    INV.CreatedByUser AS "UserName",
    INV.Customer AS "CustomerNumber",
    INV.AmountInTransactionCurrency AS "InvoiceAmount",
    INV.NetDueDate AS "PaymentDueDate",
    0 AS "DaysOverdue"
FROM I_OperationalAcctgDocItemCube AS INV
WHERE INV.CompanyCode IN ('[Your Company Code]') AND INV.AccountingDocumentType = '[Your Invoice Doc Type]' AND INV.CreationDate BETWEEN '[Start Date]' AND '[End Date]'

UNION ALL

SELECT
    INV.AccountingDocument AS "InvoiceNumber",
    'Invoice Sent To Customer' AS "ActivityName",
    OUTP.OutputRequestDateTime AS "EventTime",
    OUTP.LastChangedByUser AS "UserName",
    INV.Customer AS "CustomerNumber",
    INV.AmountInTransactionCurrency AS "InvoiceAmount",
    INV.NetDueDate AS "PaymentDueDate",
    CASE WHEN INV.NetDueDate < CAST(OUTP.OutputRequestDateTime AS DATE) THEN DATEDIFF(day, INV.NetDueDate, CAST(OUTP.OutputRequestDateTime AS DATE)) ELSE 0 END AS "DaysOverdue"
FROM I_BillingDocumentOutputRequest AS OUTP
JOIN I_OperationalAcctgDocItemCube AS INV ON OUTP.BillingDocument = INV.AccountingDocument AND INV.CompanyCode IN ('[Your Company Code]') AND INV.AccountingDocumentType = '[Your Invoice Doc Type]'
WHERE CAST(OUTP.OutputRequestDateTime AS DATE) BETWEEN '[Start Date]' AND '[End Date]'

UNION ALL

SELECT
    INV.AccountingDocument AS "InvoiceNumber",
    'Payment Due Date Passed' AS "ActivityName",
    INV.NetDueDate AS "EventTime",
    'SYSTEM' AS "UserName",
    INV.Customer AS "CustomerNumber",
    INV.AmountInTransactionCurrency AS "InvoiceAmount",
    INV.NetDueDate AS "PaymentDueDate",
    0 AS "DaysOverdue"
FROM I_OperationalAcctgDocItemCube AS INV
WHERE INV.CompanyCode IN ('[Your Company Code]') AND INV.AccountingDocumentType = '[Your Invoice Doc Type]' AND INV.IsCleared = '' AND INV.NetDueDate BETWEEN '[Start Date]' AND '[End Date]' AND INV.NetDueDate < CURRENT_DATE

UNION ALL

SELECT
    REL.RelatedAccountingDocument AS "InvoiceNumber",
    'Dispute Case Registered' AS "ActivityName",
    DCASE.CreationDateTime AS "EventTime",
    DCASE.CreatedByUser AS "UserName",
    INV.Customer AS "CustomerNumber",
    INV.AmountInTransactionCurrency AS "InvoiceAmount",
    INV.NetDueDate AS "PaymentDueDate",
    CASE WHEN INV.NetDueDate < CAST(DCASE.CreationDateTime AS DATE) THEN DATEDIFF(day, INV.NetDueDate, CAST(DCASE.CreationDateTime AS DATE)) ELSE 0 END AS "DaysOverdue"
FROM I_DisputeCase AS DCASE
JOIN I_DisputeCaseRelatedObject AS REL ON DCASE.DisputeCase = REL.DisputeCase
JOIN I_OperationalAcctgDocItemCube AS INV ON REL.RelatedAccountingDocument = INV.AccountingDocument AND REL.RelatedCompanyCode = INV.CompanyCode AND INV.AccountingDocumentType = '[Your Invoice Doc Type]'
WHERE INV.CompanyCode IN ('[Your Company Code]') AND CAST(DCASE.CreationDateTime AS DATE) BETWEEN '[Start Date]' AND '[End Date]'

UNION ALL

SELECT
    DUN.AccountingDocument AS "InvoiceNumber",
    'Dunning Procedure Initiated' AS "ActivityName",
    DUN.DunningDate AS "EventTime",
    DUN.DunningRunUser AS "UserName",
    INV.Customer AS "CustomerNumber",
    INV.AmountInTransactionCurrency AS "InvoiceAmount",
    INV.NetDueDate AS "PaymentDueDate",
    CASE WHEN INV.NetDueDate < DUN.DunningDate THEN DATEDIFF(day, INV.NetDueDate, DUN.DunningDate) ELSE 0 END AS "DaysOverdue"
FROM I_DunningHistoryItem AS DUN
JOIN I_OperationalAcctgDocItemCube AS INV ON DUN.AccountingDocument = INV.AccountingDocument AND DUN.CompanyCode = INV.CompanyCode AND INV.AccountingDocumentType = '[Your Invoice Doc Type]'
WHERE DUN.CompanyCode IN ('[Your Company Code]') AND DUN.DunningDate BETWEEN '[Start Date]' AND '[End Date]'

UNION ALL

-- Note: This activity requires a custom view. Replace '[Your Custom Contact Log View]' with your specific implementation.
SELECT
    LOG.InvoiceReference AS "InvoiceNumber",
    'Collection Call Made' AS "ActivityName",
    LOG.CreationDateTime AS "EventTime",
    LOG.CreatedByUser AS "UserName",
    INV.Customer AS "CustomerNumber",
    INV.AmountInTransactionCurrency AS "InvoiceAmount",
    INV.NetDueDate AS "PaymentDueDate",
    CASE WHEN INV.NetDueDate < CAST(LOG.CreationDateTime AS DATE) THEN DATEDIFF(day, INV.NetDueDate, CAST(LOG.CreationDateTime AS DATE)) ELSE 0 END AS "DaysOverdue"
FROM [Your Custom Contact Log View] AS LOG
JOIN I_OperationalAcctgDocItemCube AS INV ON LOG.InvoiceReference = INV.AccountingDocument AND INV.CompanyCode IN ('[Your Company Code]') AND INV.AccountingDocumentType = '[Your Invoice Doc Type]'
WHERE CAST(LOG.CreationDateTime AS DATE) BETWEEN '[Start Date]' AND '[End Date]'

UNION ALL

SELECT
    P2P.OriginalReferenceDocument AS "InvoiceNumber",
    'Promise To Pay Created' AS "ActivityName",
    P2P.CreationDateTime AS "EventTime",
    P2P.CreatedByUser AS "UserName",
    INV.Customer AS "CustomerNumber",
    INV.AmountInTransactionCurrency AS "InvoiceAmount",
    INV.NetDueDate AS "PaymentDueDate",
    CASE WHEN INV.NetDueDate < CAST(P2P.CreationDateTime AS DATE) THEN DATEDIFF(day, INV.NetDueDate, CAST(P2P.CreationDateTime AS DATE)) ELSE 0 END AS "DaysOverdue"
FROM I_PromiseToPay AS P2P
JOIN I_OperationalAcctgDocItemCube AS INV ON P2P.OriginalReferenceDocument = INV.AccountingDocument AND P2P.CompanyCode = INV.CompanyCode AND INV.AccountingDocumentType = '[Your Invoice Doc Type]'
WHERE P2P.CompanyCode IN ('[Your Company Code]') AND CAST(P2P.CreationDateTime AS DATE) BETWEEN '[Start Date]' AND '[End Date]'

UNION ALL

SELECT
    INV.AccountingDocument AS "InvoiceNumber",
    'Payment Received' AS "ActivityName",
    INV.ClearingDate AS "EventTime",
    INV.ClearingJournalEntryUser AS "UserName",
    INV.Customer AS "CustomerNumber",
    INV.AmountInTransactionCurrency AS "InvoiceAmount",
    INV.NetDueDate AS "PaymentDueDate",
    CASE WHEN INV.NetDueDate < INV.ClearingDate THEN DATEDIFF(day, INV.NetDueDate, INV.ClearingDate) ELSE 0 END AS "DaysOverdue"
FROM I_OperationalAcctgDocItemCube AS INV
WHERE INV.CompanyCode IN ('[Your Company Code]') AND INV.AccountingDocumentType = '[Your Invoice Doc Type]' AND INV.IsCleared = 'X' AND INV.ClearingDate BETWEEN '[Start Date]' AND '[End Date]'

UNION ALL

SELECT
    INV.AccountingDocument AS "InvoiceNumber",
    'Payment Posted' AS "ActivityName",
    INV.ClearingDate AS "EventTime", -- Often same as Payment Received timestamp
    INV.ClearingJournalEntryUser AS "UserName",
    INV.Customer AS "CustomerNumber",
    INV.AmountInTransactionCurrency AS "InvoiceAmount",
    INV.NetDueDate AS "PaymentDueDate",
    CASE WHEN INV.NetDueDate < INV.ClearingDate THEN DATEDIFF(day, INV.NetDueDate, INV.ClearingDate) ELSE 0 END AS "DaysOverdue"
FROM I_OperationalAcctgDocItemCube AS INV
WHERE INV.CompanyCode IN ('[Your Company Code]') AND INV.AccountingDocumentType = '[Your Invoice Doc Type]' AND INV.IsCleared = 'X' AND INV.ClearingDate BETWEEN '[Start Date]' AND '[End Date]'

UNION ALL

SELECT
    REL.RelatedAccountingDocument AS "InvoiceNumber",
    'Dispute Resolved' AS "ActivityName",
    DCASE.LastChangeDateTime AS "EventTime",
    DCASE.LastChangedByUser AS "UserName",
    INV.Customer AS "CustomerNumber",
    INV.AmountInTransactionCurrency AS "InvoiceAmount",
    INV.NetDueDate AS "PaymentDueDate",
    CASE WHEN INV.NetDueDate < CAST(DCASE.LastChangeDateTime AS DATE) THEN DATEDIFF(day, INV.NetDueDate, CAST(DCASE.LastChangeDateTime AS DATE)) ELSE 0 END AS "DaysOverdue"
FROM I_DisputeCase AS DCASE
JOIN I_DisputeCaseRelatedObject AS REL ON DCASE.DisputeCase = REL.DisputeCase
JOIN I_OperationalAcctgDocItemCube AS INV ON REL.RelatedAccountingDocument = INV.AccountingDocument AND REL.RelatedCompanyCode = INV.CompanyCode AND INV.AccountingDocumentType = '[Your Invoice Doc Type]'
WHERE INV.CompanyCode IN ('[Your Company Code]') AND DCASE.DisputeCaseProcessingStatus = '[Your Dispute Case Closed Status]' AND CAST(DCASE.LastChangeDateTime AS DATE) BETWEEN '[Start Date]' AND '[End Date]'

UNION ALL

SELECT
    INV.AccountingDocument AS "InvoiceNumber",
    'Invoice Settled' AS "ActivityName",
    INV.ClearingDate AS "EventTime",
    INV.ClearingJournalEntryUser AS "UserName",
    INV.Customer AS "CustomerNumber",
    INV.AmountInTransactionCurrency AS "InvoiceAmount",
    INV.NetDueDate AS "PaymentDueDate",
    CASE WHEN INV.NetDueDate < INV.ClearingDate THEN DATEDIFF(day, INV.NetDueDate, INV.ClearingDate) ELSE 0 END AS "DaysOverdue"
FROM I_OperationalAcctgDocItemCube AS INV
WHERE INV.CompanyCode IN ('[Your Company Code]') AND INV.AccountingDocumentType = '[Your Invoice Doc Type]' AND INV.IsCleared = 'X' AND INV.ClearingDate BETWEEN '[Start Date]' AND '[End Date]'

UNION ALL

SELECT
    INV.AccountingDocument AS "InvoiceNumber",
    'Invoice Written Off' AS "ActivityName",
    INV.ClearingDate AS "EventTime",
    INV.ClearingJournalEntryUser AS "UserName",
    INV.Customer AS "CustomerNumber",
    INV.AmountInTransactionCurrency AS "InvoiceAmount",
    INV.NetDueDate AS "PaymentDueDate",
    CASE WHEN INV.NetDueDate < INV.ClearingDate THEN DATEDIFF(day, INV.NetDueDate, INV.ClearingDate) ELSE 0 END AS "DaysOverdue"
FROM I_OperationalAcctgDocItemCube AS INV
WHERE INV.CompanyCode IN ('[Your Company Code]') AND INV.AccountingDocumentType = '[Your Invoice Doc Type]' AND INV.IsCleared = 'X' AND INV.ClearingReason = '[Your Write-off Reason Code]' AND INV.ClearingDate BETWEEN '[Start Date]' AND '[End Date]'

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

class=class="hl-string">"hl-comment"># --- SAP Connection Configuration ---
class=class="hl-string">"hl-comment"># Replace with your SAP system's connection details
sap_config = {
    class="hl-string">'ashost': class="hl-string">'[Your SAP Application Server Host]',
    class="hl-string">'sysnr': class="hl-string">'[Your System Number]',
    class="hl-string">'client': class="hl-string">'[Your Client ID]',
    class="hl-string">'user': class="hl-string">'[Your SAP User]',
    class="hl-string">'passwd': class="hl-string">'[Your SAP Password]'
}

class=class="hl-string">"hl-comment"># --- Helper Functions ---
def rfc_read_to_df(conn, table_name, fields, options):
    class="hl-string">"""Generic function to read an SAP table and return a pandas DataFrame."""
    result = conn.call(class="hl-string">'RFC_READ_TABLE',
                       QUERY_TABLE=table_name,
                       DELIMITER=class="hl-string">',',
                       FIELDS=fields,
                       OPTIONS=options)
    data = result[class="hl-string">'DATA']
    if not data:
        return pd.DataFrame(columns=[f[class="hl-string">'FIELDNAME'] for f in fields])
    
    records = [dict(zip([f[class="hl-string">'FIELDNAME'] for f in fields], row[class="hl-string">'WA'].strip().split(class="hl-string">','))) for row in data]
    return pd.DataFrame(records)

def format_sap_date(date_str):
    class="hl-string">"""Converts SAP date string (YYYYMMDD) to datetime object."""
    if date_str and date_str != class="hl-string">'00000000':
        return datetime.strptime(date_str, class="hl-string">'%Y%m%d')
    return None

def format_sap_time(time_str):
    class="hl-string">"""Converts SAP time string (HHMMSS) to timedelta object."""
    if time_str and len(time_str) == 6:
        return timedelta(hours=int(time_str[0:2]), minutes=int(time_str[2:4]), seconds=int(time_str[4:6]))
    return timedelta(0)

class=class="hl-string">"hl-comment"># --- Activity Extraction Functions ---

def get_invoices_generated(conn, company_codes, start_date, end_date):
    options = [fclass="hl-string">"(BUKRS IN ('{','.join(company_codes)}') AND BUDAT >= '{start_date}' AND BUDAT <= '{end_date}' AND BLART IN ('RV', 'RE'))"] class=class="hl-string">"hl-comment"># Filter for customer invoices
    bkpf_fields = [{class="hl-string">'FIELDNAME': class="hl-string">'BELNR'}, {class="hl-string">'FIELDNAME': class="hl-string">'GJAHR'}, {class="hl-string">'FIELDNAME': class="hl-string">'BUKRS'}, {class="hl-string">'FIELDNAME': class="hl-string">'BUDAT'}, {class="hl-string">'FIELDNAME': class="hl-string">'CPUDT'}, {class="hl-string">'FIELDNAME': class="hl-string">'CPUTM'}, {class="hl-string">'FIELDNAME': class="hl-string">'USNAM'}]
    bseg_fields = [{class="hl-string">'FIELDNAME': class="hl-string">'BELNR'}, {class="hl-string">'FIELDNAME': class="hl-string">'GJAHR'}, {class="hl-string">'FIELDNAME': class="hl-string">'BUKRS'}, {class="hl-string">'FIELDNAME': class="hl-string">'KUNNR'}, {class="hl-string">'FIELDNAME': class="hl-string">'WRBTR'}, {class="hl-string">'FIELDNAME': class="hl-string">'ZFBDT'}]
    
    df_bkpf = rfc_read_to_df(conn, class="hl-string">'BKPF', bkpf_fields, options)
    if df_bkpf.empty: return pd.DataFrame()

    class=class="hl-string">"hl-comment"># Fetch line item details
    invoice_keys = [fclass="hl-string">"(BELNR = '{row.BELNR}' AND GJAHR = '{row.GJAHR}' AND BUKRS = '{row.BUKRS}')" for index, row in df_bkpf.iterrows()]
    bseg_options = [class="hl-string">" OR ".join(invoice_keys[i:i+50]) for i in range(0, len(invoice_keys), 50)] class=class="hl-string">"hl-comment"># Batch options
    df_bseg_list = [rfc_read_to_df(conn, class="hl-string">'BSEG', bseg_fields, [opt]) for opt in bseg_options]
    df_bseg = pd.concat(df_bseg_list)
    df_bseg = df_bseg[df_bseg[class="hl-string">'KUNNR'] != class="hl-string">''].drop_duplicates([class="hl-string">'BELNR', class="hl-string">'GJAHR', class="hl-string">'BUKRS'])
    
    df = pd.merge(df_bkpf, df_bseg, on=[class="hl-string">'BELNR', class="hl-string">'GJAHR', class="hl-string">'BUKRS'])

    df[class="hl-string">'ActivityName'] = class="hl-string">'Invoice Generated'
    df[class="hl-string">'EventTime'] = df.apply(lambda row: format_sap_date(row[class="hl-string">'CPUDT']) + format_sap_time(row[class="hl-string">'CPUTM']), axis=1)
    df.rename(columns={class="hl-string">'BELNR': class="hl-string">'InvoiceNumber', class="hl-string">'USNAM': class="hl-string">'UserName', class="hl-string">'KUNNR': class="hl-string">'CustomerNumber', class="hl-string">'WRBTR': class="hl-string">'InvoiceAmount', class="hl-string">'ZFBDT': class="hl-string">'PaymentDueDate'}, inplace=True)
    df[class="hl-string">'PaymentDueDate'] = df[class="hl-string">'PaymentDueDate'].apply(format_sap_date)
    return df[[class="hl-string">'InvoiceNumber', class="hl-string">'ActivityName', class="hl-string">'EventTime', class="hl-string">'UserName', class="hl-string">'CustomerNumber', class="hl-string">'InvoiceAmount', class="hl-string">'PaymentDueDate']]

def get_invoices_sent(conn, company_codes, start_date, end_date):
    options = [fclass="hl-string">"KAPPL = 'V3' AND DATUM >= '{start_date}' AND DATUM <= '{end_date}'"] class=class="hl-string">"hl-comment"># V3 is for Billing
    nkey_prefix = [fclass="hl-string">"{client}{cc}" for client in [sap_config[class="hl-string">'client']] for cc in company_codes]
    options[0] += fclass="hl-string">" AND (LEFT(OBJKY, 5) IN ('{','.join(nkey_prefix)}'))"

    fields = [{class="hl-string">'FIELDNAME': class="hl-string">'OBJKY'}, {class="hl-string">'FIELDNAME': class="hl-string">'DATUM'}, {class="hl-string">'FIELDNAME': class="hl-string">'UZEIT'}, {class="hl-string">'FIELDNAME': class="hl-string">'USNAM'}]
    df = rfc_read_to_df(conn, class="hl-string">'NAST', fields, options)
    if df.empty: return pd.DataFrame()

    df[class="hl-string">'InvoiceNumber'] = df[class="hl-string">'OBJKY'].str[5:15].str.lstrip(class="hl-string">'0')
    df[class="hl-string">'ActivityName'] = class="hl-string">'Invoice Sent To Customer'
    df[class="hl-string">'EventTime'] = df.apply(lambda row: format_sap_date(row[class="hl-string">'DATUM']) + format_sap_time(row[class="hl-string">'UZEIT']), axis=1)
    df.rename(columns={class="hl-string">'USNAM': class="hl-string">'UserName'}, inplace=True)
    return df[[class="hl-string">'InvoiceNumber', class="hl-string">'ActivityName', class="hl-string">'EventTime', class="hl-string">'UserName']]

def get_payment_due_passed(conn, company_codes):
    options = [fclass="hl-string">"BUKRS IN ('{','.join(company_codes)}') AND BLART IN ('RV', 'RE') AND ZFBDT < '{datetime.now().strftime('%Y%m%d')}'"]
    fields = [{class="hl-string">'FIELDNAME': class="hl-string">'BELNR'}, {class="hl-string">'FIELDNAME': class="hl-string">'KUNNR'}, {class="hl-string">'FIELDNAME': class="hl-string">'ZFBDT'}]
    df = rfc_read_to_df(conn, class="hl-string">'BSID', fields, options)
    if df.empty: return pd.DataFrame()

    df[class="hl-string">'ActivityName'] = class="hl-string">'Payment Due Date Passed'
    df[class="hl-string">'EventTime'] = df[class="hl-string">'ZFBDT'].apply(lambda x: format_sap_date(x) + timedelta(days=1))
    df.rename(columns={class="hl-string">'BELNR': class="hl-string">'InvoiceNumber', class="hl-string">'KUNNR': class="hl-string">'CustomerNumber'}, inplace=True)
    return df[[class="hl-string">'InvoiceNumber', class="hl-string">'ActivityName', class="hl-string">'EventTime', class="hl-string">'CustomerNumber']]

def get_dispute_cases(conn, company_codes, start_date, end_date):
    options = [fclass="hl-string">"COMPANY_CODE IN ('{','.join(company_codes)}') AND CREATED_AT >= '{start_date}'"]
    fields = [{class="hl-string">'FIELDNAME': class="hl-string">'CASE_GUID'}, {class="hl-string">'FIELDNAME': class="hl-string">'CREATED_AT'}, {class="hl-string">'FIELDNAME': class="hl-string">'CREATED_BY'}, {class="hl-string">'FIELDNAME': class="hl-string">'CLOSING_DATE'}, {class="hl-string">'FIELDNAME': class="hl-string">'PROCESSOR'}]
    df_case = rfc_read_to_df(conn, class="hl-string">'UDM_SPS_CASE_ATTR', fields, options)
    if df_case.empty: return pd.DataFrame()

    case_guids = df_case[class="hl-string">'CASE_GUID'].unique().tolist()
    obj_options = [fclass="hl-string">"CASE_GUID IN ('{','.join(case_guids)}')"]
    obj_fields = [{class="hl-string">'FIELDNAME': class="hl-string">'CASE_GUID'}, {class="hl-string">'FIELDNAME': class="hl-string">'OBJECT_ID'}]
    df_obj = rfc_read_to_df(conn, class="hl-string">'UDM_SPS_OBJECT', obj_fields, obj_options)

    df = pd.merge(df_case, df_obj, on=class="hl-string">'CASE_GUID')
    df[class="hl-string">'InvoiceNumber'] = df[class="hl-string">'OBJECT_ID'].str[7:17].str.lstrip(class="hl-string">'0')

    df_registered = df.copy()
    df_registered[class="hl-string">'ActivityName'] = class="hl-string">'Dispute Case Registered'
    df_registered[class="hl-string">'EventTime'] = df_registered[class="hl-string">'CREATED_AT'].apply(format_sap_date)
    df_registered.rename(columns={class="hl-string">'CREATED_BY': class="hl-string">'UserName'}, inplace=True)
    
    df_resolved = df[df[class="hl-string">'CLOSING_DATE'].notna() & (df[class="hl-string">'CLOSING_DATE'] != class="hl-string">'00000000')].copy()
    df_resolved[class="hl-string">'ActivityName'] = class="hl-string">'Dispute Resolved'
    df_resolved[class="hl-string">'EventTime'] = df_resolved[class="hl-string">'CLOSING_DATE'].apply(format_sap_date)
    df_resolved.rename(columns={class="hl-string">'PROCESSOR': class="hl-string">'UserName'}, inplace=True)

    return pd.concat([df_registered[[class="hl-string">'InvoiceNumber', class="hl-string">'ActivityName', class="hl-string">'EventTime', class="hl-string">'UserName']], 
                      df_resolved[[class="hl-string">'InvoiceNumber', class="hl-string">'ActivityName', class="hl-string">'EventTime', class="hl-string">'UserName']]])

def get_dunning(conn, company_codes, start_date, end_date):
    options = [fclass="hl-string">"BUKRS IN ('{','.join(company_codes)}') AND LAUFD >= '{start_date}'"]
    h_fields = [{class="hl-string">'FIELDNAME': class="hl-string">'LAUFD'}, {class="hl-string">'FIELDNAME': class="hl-string">'LAUFI'}, {class="hl-string">'FIELDNAME': class="hl-string">'KOART'}]
    df_h = rfc_read_to_df(conn, class="hl-string">'MHNK', h_fields, options)
    df_h = df_h[df_h[class="hl-string">'KOART'] == class="hl-string">'D'] class=class="hl-string">"hl-comment"># Only customers
    if df_h.empty: return pd.DataFrame()

    d_fields = [{class="hl-string">'FIELDNAME': class="hl-string">'LAUFD'}, {class="hl-string">'FIELDNAME': class="hl-string">'LAUFI'}, {class="hl-string">'FIELDNAME': class="hl-string">'BELNR'}]
    dunning_keys = [fclass="hl-string">"(LAUFD = '{row.LAUFD}' AND LAUFI = '{row.LAUFI}')" for index, row in df_h.iterrows()]
    d_options = [class="hl-string">" OR ".join(dunning_keys[i:i+50]) for i in range(0, len(dunning_keys), 50)]
    df_d_list = [rfc_read_to_df(conn, class="hl-string">'MHND', d_fields, [opt]) for opt in d_options]
    df_d = pd.concat(df_d_list).drop_duplicates()

    df = pd.merge(df_h, df_d, on=[class="hl-string">'LAUFD', class="hl-string">'LAUFI'])
    df[class="hl-string">'ActivityName'] = class="hl-string">'Dunning Procedure Initiated'
    df[class="hl-string">'EventTime'] = df[class="hl-string">'LAUFD'].apply(format_sap_date)
    df.rename(columns={class="hl-string">'BELNR': class="hl-string">'InvoiceNumber'}, inplace=True)
    return df[[class="hl-string">'InvoiceNumber', class="hl-string">'ActivityName', class="hl-string">'EventTime']]

def get_promise_to_pay(conn, company_codes, start_date, end_date):
    options = [fclass="hl-string">"COMPANY_CODE IN ('{','.join(company_codes)}') AND CREATION_DATE >= '{start_date}'"]
    fields = [{class="hl-string">'FIELDNAME': class="hl-string">'INVOICE_REF'}, {class="hl-string">'FIELDNAME': class="hl-string">'CREATION_DATE'}, {class="hl-string">'FIELDNAME': class="hl-string">'CREATION_TIME'}, {class="hl-string">'FIELDNAME': class="hl-string">'CREATED_BY'}]
    df = rfc_read_to_df(conn, class="hl-string">'UDM_P2P_ATTR', fields, options)
    if df.empty: return pd.DataFrame()
    
    df[class="hl-string">'InvoiceNumber'] = df[class="hl-string">'INVOICE_REF'].str[7:17].str.lstrip(class="hl-string">'0')
    df[class="hl-string">'ActivityName'] = class="hl-string">'Promise To Pay Created'
    df[class="hl-string">'EventTime'] = df.apply(lambda row: format_sap_date(row[class="hl-string">'CREATION_DATE']) + format_sap_time(row[class="hl-string">'CREATION_TIME']), axis=1)
    df.rename(columns={class="hl-string">'CREATED_BY': class="hl-string">'UserName'}, inplace=True)
    return df[[class="hl-string">'InvoiceNumber', class="hl-string">'ActivityName', class="hl-string">'EventTime', class="hl-string">'UserName']]

def get_cleared_items(conn, company_codes, start_date, end_date):
    options = [fclass="hl-string">"BUKRS IN ('{','.join(company_codes)}') AND AUGDT >= '{start_date}' AND AUGDT <= '{end_date}' AND BLART IN ('RV', 'RE')"]
    fields = [{class="hl-string">'FIELDNAME': class="hl-string">'BELNR'}, {class="hl-string">'FIELDNAME': class="hl-string">'AUGDT'}, {class="hl-string">'FIELDNAME': class="hl-string">'AUGBL'}, {class="hl-string">'FIELDNAME': class="hl-string">'RSTGR'}, {class="hl-string">'FIELDNAME': class="hl-string">'USNAM'}, {class="hl-string">'FIELDNAME': class="hl-string">'KUNNR'}]
    df = rfc_read_to_df(conn, class="hl-string">'BSAD', fields, options)
    if df.empty: return pd.DataFrame()
    df.rename(columns={class="hl-string">'BELNR': class="hl-string">'InvoiceNumber', class="hl-string">'KUNNR': class="hl-string">'CustomerNumber', class="hl-string">'USNAM': class="hl-string">'UserName'}, inplace=True)
    df[class="hl-string">'EventTime'] = df[class="hl-string">'AUGDT'].apply(format_sap_date)

    class=class="hl-string">"hl-comment"># Write-offs (Reason Code for write-off needs configuration)
    df_woff = df[df[class="hl-string">'RSTGR'].isin([class="hl-string">'[Your Write-Off Reason Code]'])].copy()
    df_woff[class="hl-string">'ActivityName'] = class="hl-string">'Invoice Written Off'

    class=class="hl-string">"hl-comment"># Normal Payments
    df_paid = df[~df[class="hl-string">'RSTGR'].isin([class="hl-string">'[Your Write-Off Reason Code]'])].copy()
    
    df_received = df_paid.copy()
    df_received[class="hl-string">'ActivityName'] = class="hl-string">'Payment Received'

    df_posted = df_paid.copy()
    df_posted[class="hl-string">'ActivityName'] = class="hl-string">'Payment Posted'
    df_posted[class="hl-string">'EventTime'] = df_posted[class="hl-string">'EventTime'] + timedelta(seconds=1) class=class="hl-string">"hl-comment"># Small offset for ordering

    df_settled = df_paid.copy()
    df_settled[class="hl-string">'ActivityName'] = class="hl-string">'Invoice Settled'
    df_settled[class="hl-string">'EventTime'] = df_settled[class="hl-string">'EventTime'] + timedelta(seconds=2)

    return pd.concat([df_received[[class="hl-string">'InvoiceNumber', class="hl-string">'ActivityName', class="hl-string">'EventTime', class="hl-string">'UserName', class="hl-string">'CustomerNumber']], 
                      df_posted[[class="hl-string">'InvoiceNumber', class="hl-string">'ActivityName', class="hl-string">'EventTime', class="hl-string">'UserName', class="hl-string">'CustomerNumber']], 
                      df_settled[[class="hl-string">'InvoiceNumber', class="hl-string">'ActivityName', class="hl-string">'EventTime', class="hl-string">'UserName', class="hl-string">'CustomerNumber']],
                      df_woff[[class="hl-string">'InvoiceNumber', class="hl-string">'ActivityName', class="hl-string">'EventTime', class="hl-string">'UserName', class="hl-string">'CustomerNumber']]])

def get_collection_calls(conn, company_codes, start_date, end_date):
    class=class="hl-string">"hl-comment"># This is a placeholder for a custom activity. 
    class=class="hl-string">"hl-comment"># Assumes a custom Z-table class="hl-string">'ZCOLL_CALLS' exists.
    class=class="hl-string">"hl-comment"># Fields: BELNR, CALL_DATE, CALL_TIME, UNAME, KUNNR
    class=class="hl-string">"hl-comment"># Please adapt table and field names to your specific implementation.
    try:
        options = [fclass="hl-string">"LEFT(BELNR, 4) IN ('{','.join(company_codes)}') AND CALL_DATE >= '{start_date}'"]
        fields = [{class="hl-string">'FIELDNAME': class="hl-string">'BELNR'}, {class="hl-string">'FIELDNAME': class="hl-string">'CALL_DATE'}, {class="hl-string">'FIELDNAME': class="hl-string">'CALL_TIME'}, {class="hl-string">'FIELDNAME': class="hl-string">'UNAME'}]
        df = rfc_read_to_df(conn, class="hl-string">'[Your Custom Table for Call Logs]', fields, options)
        if df.empty: return pd.DataFrame()

        df[class="hl-string">'ActivityName'] = class="hl-string">'Collection Call Made'
        df[class="hl-string">'EventTime'] = df.apply(lambda row: format_sap_date(row[class="hl-string">'CALL_DATE']) + format_sap_time(row[class="hl-string">'CALL_TIME']), axis=1)
        df.rename(columns={class="hl-string">'BELNR': class="hl-string">'InvoiceNumber', class="hl-string">'UNAME': class="hl-string">'UserName'}, inplace=True)
        return df[[class="hl-string">'InvoiceNumber', class="hl-string">'ActivityName', class="hl-string">'EventTime', class="hl-string">'UserName']]
    except Exception as e:
        print(fclass="hl-string">"Could not extract 'Collection Call Made'. This is expected if a custom log table is not used. Error: {e}")
        return pd.DataFrame()

class=class="hl-string">"hl-comment"># --- Main Execution Logic ---
if __name__ == class="hl-string">"__main__":
    class=class="hl-string">"hl-comment"># --- Main Parameters ---
    company_codes = [class="hl-string">'[Your Company Code]'] class=class="hl-string">"hl-comment"># e.g., [class="hl-string">'1000', class="hl-string">'1010']
    start_date = class="hl-string">'[Start Date YYYYMMDD]' class=class="hl-string">"hl-comment"># e.g., class="hl-string">'20230101'
    end_date = class="hl-string">'[End Date YYYYMMDD]' class=class="hl-string">"hl-comment"># e.g., class="hl-string">'20231231'

    all_events = []
    try:
        with Connection(**sap_config) as conn:
            print(class="hl-string">"Connection successful. Starting extraction...")
            
            print(class="hl-string">"1/8: Extracting 'Invoice Generated'...")
            all_events.append(get_invoices_generated(conn, company_codes, start_date, end_date))
            
            print(class="hl-string">"2/8: Extracting 'Invoice Sent To Customer'...")
            all_events.append(get_invoices_sent(conn, company_codes, start_date, end_date))

            print(class="hl-string">"3/8: Extracting 'Payment Due Date Passed'...")
            all_events.append(get_payment_due_passed(conn, company_codes))

            print(class="hl-string">"4/8: Extracting Dispute Cases ('Registered', 'Resolved')...")
            all_events.append(get_dispute_cases(conn, company_codes, start_date, end_date))

            print(class="hl-string">"5/8: Extracting 'Dunning Procedure Initiated'...")
            all_events.append(get_dunning(conn, company_codes, start_date, end_date))

            print(class="hl-string">"6/8: Extracting 'Promise To Pay Created'...")
            all_events.append(get_promise_to_pay(conn, company_codes, start_date, end_date))
            
            print(class="hl-string">"7/8: Extracting Cleared Items ('Payment Received', 'Posted', 'Settled', 'Written Off')...")
            all_events.append(get_cleared_items(conn, company_codes, start_date, end_date))

            print(class="hl-string">"8/8: Extracting 'Collection Call Made' (custom)...")
            all_events.append(get_collection_calls(conn, company_codes, start_date, end_date))

    except Exception as e:
        print(fclass="hl-string">"An error occurred during RFC call: {e}")

    print(class="hl-string">"\nCombining all data...")
    final_df = pd.concat([df for df in all_events if not df.empty])

    if not final_df.empty:
        class=class="hl-string">"hl-comment"># Post-processing
        final_df = final_df.sort_values(by=[class="hl-string">'InvoiceNumber', class="hl-string">'EventTime']).reset_index(drop=True)
        final_df[class="hl-string">'InvoiceAmount'] = pd.to_numeric(final_df[class="hl-string">'InvoiceAmount'], errors=class="hl-string">'coerce')
        final_df[class="hl-string">'PaymentDueDate'] = pd.to_datetime(final_df[class="hl-string">'PaymentDueDate'], errors=class="hl-string">'coerce')

        class=class="hl-string">"hl-comment"># Fill forward attributes for a complete event log
        final_df[class="hl-string">'CustomerNumber'] = final_df.groupby(class="hl-string">'InvoiceNumber')[class="hl-string">'CustomerNumber'].ffill().bfill()
        final_df[class="hl-string">'InvoiceAmount'] = final_df.groupby(class="hl-string">'InvoiceNumber')[class="hl-string">'InvoiceAmount'].ffill().bfill()
        final_df[class="hl-string">'PaymentDueDate'] = final_df.groupby(class="hl-string">'InvoiceNumber')[class="hl-string">'PaymentDueDate'].ffill().bfill()
        
        class=class="hl-string">"hl-comment"># Calculate DaysOverdue
        final_df[class="hl-string">'DaysOverdue'] = (final_df[class="hl-string">'EventTime'].dt.date - final_df[class="hl-string">'PaymentDueDate'].dt.date).apply(lambda x: x.days if pd.notnull(x) and x.days > 0 else 0)

        class=class="hl-string">"hl-comment"># Save to CSV
        output_file = class="hl-string">'collections_event_log.csv'
        final_df.to_csv(output_file, index=False, date_format=class="hl-string">'%Y-%m-%d %H:%M:%S')
        print(fclass="hl-string">"Extraction complete. Event log saved to {output_file}")
    else:
        print(class="hl-string">"Extraction resulted in no data. Please check your parameters and SAP system.")

REPORT Z_PM_CREDIT_COLLECTIONS_EXTRACT.

*&---------------------------------------------------------------------*
*& Data Types for Final Event Log
*&---------------------------------------------------------------------*
TYPES: BEGIN OF ty_event_log,
         invoicenumber  TYPE belnr_d,
         activityname   TYPE string,
         eventtime      TYPE timestamp,
         username       TYPE uname,
         customernumber TYPE kunnr,
         invoiceamount  TYPE wrbtr,
         paymentduedate TYPE dats,
         daysoverdue    TYPE int4,
       END OF ty_event_log.

DATA: lt_event_log TYPE TABLE OF ty_event_log.

*&---------------------------------------------------------------------*
*& Selection Screen
*&---------------------------------------------------------------------*
SELECT-OPTIONS: s_bukrs FOR bkpf-bukrs OBLIGATORY,
                s_bldat FOR bkpf-bldat OBLIGATORY,
                s_kunnr FOR bseg-kunnr,
                s_blart FOR bkpf-blart.

PARAMETERS: p_fpath TYPE string DEFAULT '/usr/sap/trans/tmp/collections_log.csv'.

*&---------------------------------------------------------------------*
*& Main Processing Block
*&---------------------------------------------------------------------*
START-OF-SELECTION.

  " 1. Invoice Generated
  SELECT
    b~belnr AS invoicenumber,
    'Invoice Generated' AS activityname,
    CAST( CONCAT( h~bldat, h~cpuzt ) AS p DECIMALS 0 ) AS eventtime,
    h~usnam AS username,
    b~kunnr AS customernumber,
    b~wrbtr AS invoiceamount,
    b~netdt AS paymentduedate,
    0       AS daysoverdue
  FROM bkpf AS h
  INNER JOIN bseg AS b ON h~bukrs = b~bukrs AND h~belnr = b~belnr AND h~gjahr = b~gjahr
  WHERE h~bukrs IN @s_bukrs
    AND h~bldat IN @s_bldat
    AND b~kunnr IN @s_kunnr
    AND h~blart IN @s_blart
    AND b~koart = 'D' " Customer items
    AND b~shkzg = 'S' " Debit
  INTO CORRESPONDING FIELDS OF TABLE @lt_event_log.

  " 2. Invoice Sent To Customer (Example from NAST)
  SELECT
    a~belnr AS invoicenumber,
    'Invoice Sent To Customer' AS activityname,
    CAST( CONCAT( n~datvr, n~uhrvr ) AS p DECIMALS 0 ) AS eventtime,
    n~usnam AS username,
    a~kunnr AS customernumber,
    a~wrbtr AS invoiceamount,
    a~netdt AS paymentduedate,
    0       AS daysoverdue
  FROM nast AS n
  INNER JOIN vbrk AS v ON n~objky = v~vbeln
  INNER JOIN bseg AS a ON v~xblnr = a~belnr
  WHERE n~kschl = '[Your Output Type]' " e.g., 'RD00'
    AND n~vstat = '1' " Processed successfully
    AND a~bukrs IN @s_bukrs
    AND a~gjahr = SUBSTRING( CONV string( n~datvr ), 0, 4 )
    AND a~kunnr IN @s_kunnr
  APPENDING CORRESPONDING FIELDS OF TABLE @lt_event_log.

  " 3. Payment Due Date Passed (Calculated Event)
  SELECT
    belnr AS invoicenumber,
    'Payment Due Date Passed' AS activityname,
    CAST( CONCAT( netdt + 1, '000000' ) AS p DECIMALS 0 ) AS eventtime,
    ''    AS username, " System event, no user
    kunnr AS customernumber,
    wrbtr AS invoiceamount,
    netdt AS paymentduedate,
    1     AS daysoverdue
  FROM bsid " Open customer items
  WHERE bukrs IN @s_bukrs
    AND bldat IN @s_bldat
    AND kunnr IN @s_kunnr
    AND netdt < sy-datum
  APPENDING CORRESPONDING FIELDS OF TABLE @lt_event_log.

  " 4. Dispute Case Registered
  SELECT
    l~ref_doc_nr AS invoicenumber,
    'Dispute Case Registered' AS activityname,
    CAST( CONCAT( h~created_at_date, h~created_at_time ) AS p DECIMALS 0 ) AS eventtime,
    h~created_by AS username,
    i~kunnr AS customernumber,
    i~wrbtr AS invoiceamount,
    i~netdt AS paymentduedate,
    0       AS daysoverdue
  FROM udm_dispute AS h
  INNER JOIN udm_dispute_link AS l ON h~case_guid = l~case_guid
  INNER JOIN bseg AS i ON l~ref_doc_nr = i~belnr AND l~ref_bukrs = i~bukrs AND l~ref_gjahr = i~gjahr
  WHERE l~ref_bukrs IN @s_bukrs
    AND i~bldat IN @s_bldat
    AND i~kunnr IN @s_kunnr
  APPENDING CORRESPONDING FIELDS OF TABLE @lt_event_log.

  " 5. Dunning Procedure Initiated
  SELECT
    d~belnr AS invoicenumber,
    'Dunning Procedure Initiated' AS activityname,
    CAST( CONCAT( h~laufd, '000000' ) AS p DECIMALS 0 ) AS eventtime,
    h~usnam AS username,
    d~kunnr AS customernumber,
    d~swnet AS invoiceamount, " Dunning amount
    b~netdt AS paymentduedate,
    d~vzsk1 AS daysoverdue
  FROM mhnk AS h
  INNER JOIN mhnd AS d ON h~laufd = d~laufd AND h~laufi = d~laufi
  INNER JOIN bseg AS b ON d~belnr = b~belnr AND d~bukrs = b~bukrs AND d~gjahr = b~gjahr
  WHERE d~bukrs IN @s_bukrs
    AND b~bldat IN @s_bldat
    AND d~kunnr IN @s_kunnr
    AND d~mslev = '1' " First dunning level
  APPENDING CORRESPONDING FIELDS OF TABLE @lt_event_log.

  " 6. Collection Call Made (Assuming logged in FDM_COLL_IN contact history)
  SELECT
    i~belnr AS invoicenumber,
    'Collection Call Made' AS activityname,
    CAST( CONCAT( c~contact_date, c~contact_time ) AS p DECIMALS 0 ) AS eventtime,
    c~processor AS username,
    c~kunnr AS customernumber,
    i~wrbtr AS invoiceamount,
    i~netdt AS paymentduedate,
    0 AS daysoverdue
  FROM fdm_coll_in AS c
  INNER JOIN bseg AS i ON c~kunnr = i~kunnr AND c~bukrs = i~bukrs
  WHERE c~bukrs IN @s_bukrs
    AND i~bldat IN @s_bldat
    AND c~kunnr IN @s_kunnr
    AND c~result = '[Your Call Result Code]'
  APPENDING CORRESPONDING FIELDS OF TABLE @lt_event_log.

  " 7. Promise To Pay Created
  SELECT
    s~belnr AS invoicenumber,
    'Promise To Pay Created' AS activityname,
    CAST( CONCAT( h~creation_date, h~creation_time ) AS p DECIMALS 0 ) AS eventtime,
    h~created_by AS username,
    s~kunnr AS customernumber,
    s~dmbtr AS invoiceamount,
    s~netdt AS paymentduedate,
    0 AS daysoverdue
  FROM fdm_p2p_attr AS h
  INNER JOIN bseg AS s ON h~opbel = s~belnr AND h~opbuk = s~bukrs AND h~opgjr = s~gjahr
  WHERE h~opbuk IN @s_bukrs
    AND s~bldat IN @s_bldat
    AND s~kunnr IN @s_kunnr
  APPENDING CORRESPONDING FIELDS OF TABLE @lt_event_log.

  " 8. Payment Received, 9. Payment Posted, 11. Invoice Settled
  SELECT
    belnr AS invoicenumber,
    'Invoice Settled' AS activityname,
    CAST( CONCAT( augdt, '000000' ) AS p DECIMALS 0 ) AS eventtime,
    augbl AS username, " Often batch user ID in clearing doc header
    kunnr AS customernumber,
    wrbtr AS invoiceamount,
    netdt AS paymentduedate,
    augdt - netdt AS daysoverdue
  FROM bsad " Cleared customer items
  WHERE bukrs IN @s_bukrs
    AND bldat IN @s_bldat
    AND kunnr IN @s_kunnr
    AND augdt <> '00000000'
    AND rstgr = '' " Exclude write-offs
  APPENDING CORRESPONDING FIELDS OF TABLE @lt_event_log.

  " 10. Dispute Resolved
  SELECT
    l~ref_doc_nr AS invoicenumber,
    'Dispute Resolved' AS activityname,
    CAST( CONCAT( h~closing_date, h~closing_time ) AS p DECIMALS 0 ) AS eventtime,
    h~closed_by AS username,
    i~kunnr AS customernumber,
    i~wrbtr AS invoiceamount,
    i~netdt AS paymentduedate,
    0 AS daysoverdue
  FROM udm_dispute AS h
  INNER JOIN udm_dispute_link AS l ON h~case_guid = l~case_guid
  INNER JOIN bseg AS i ON l~ref_doc_nr = i~belnr AND l~ref_bukrs = i~bukrs AND l~ref_gjahr = i~gjahr
  WHERE l~ref_bukrs IN @s_bukrs
    AND i~bldat IN @s_bldat
    AND i~kunnr IN @s_kunnr
    AND h~closing_date <> '00000000'
  APPENDING CORRESPONDING FIELDS OF TABLE @lt_event_log.

  " 12. Invoice Written Off
  SELECT
    belnr AS invoicenumber,
    'Invoice Written Off' AS activityname,
    CAST( CONCAT( augdt, '000000' ) AS p DECIMALS 0 ) AS eventtime,
    augbl AS username,
    kunnr AS customernumber,
    wrbtr AS invoiceamount,
    netdt AS paymentduedate,
    augdt - netdt AS daysoverdue
  FROM bsad " Cleared customer items
  WHERE bukrs IN @s_bukrs
    AND bldat IN @s_bldat
    AND kunnr IN @s_kunnr
    AND rstgr <> '' " Reason code exists for write-off
  APPENDING CORRESPONDING FIELDS OF TABLE @lt_event_log.

  " Output to file
  DATA: lv_string TYPE string.
  OPEN DATASET p_fpath FOR OUTPUT IN TEXT MODE ENCODING UTF-8.
  IF sy-subrc = 0.
    " Header
    lv_string = 'InvoiceNumber,ActivityName,EventTime,UserName,CustomerNumber,InvoiceAmount,PaymentDueDate,DaysOverdue'.
    TRANSFER lv_string TO p_fpath.
    " Data
    LOOP AT lt_event_log ASSIGNING FIELD-SYMBOL(<fs_event>).
      CONCATENATE <fs_event>-invoicenumber
                  <fs_event>-activityname
                  <fs_event>-eventtime
                  <fs_event>-username
                  <fs_event>-customernumber
                  <fs_event>-invoiceamount
                  <fs_event>-paymentduedate
                  <fs_event>-daysoverdue
      INTO lv_string SEPARATED BY ','.
      TRANSFER lv_string TO p_fpath.
    ENDLOOP.
    CLOSE DATASET p_fpath.
  ELSE.
    MESSAGE 'Error opening file.' TYPE 'E'.
  ENDIF.

  WRITE: 'Extraction complete. File created at:', p_fpath.