Order to Cash - 請求・入金データテンプレート

REPORT Z_PM_O2C_INVOICE_EXTRACT.

*&---------------------------------------------------------------------*
*& Tables
*&---------------------------------------------------------------------*
TABLES: VBRK, BKPF.

*&---------------------------------------------------------------------*
*& Type Definitions for Event Log Output
*&---------------------------------------------------------------------*
TYPES: BEGIN OF ty_event_log,
         invoicenumber      TYPE vbrk-vbeln,
         activityname       TYPE string,
         eventtime          TYPE timestamp,
         username           TYPE xubname,
         billingdocumenttype TYPE vbrk-vbtyp,
         customernumber     TYPE vbrk-kunnr,
         companycode        TYPE vbrk-bukrs,
         totalinvoiceamount TYPE vbrk-netwr,
       END OF ty_event_log.

*&---------------------------------------------------------------------*
*& Data Declarations
*&---------------------------------------------------------------------*
DATA: gt_event_log TYPE TABLE OF ty_event_log,
      gs_event_log TYPE ty_event_log.

DATA: BEGIN OF gs_invoice,
        vbeln TYPE vbrk-vbeln, " SD Doc (Invoice)
        awkey TYPE bkpf-awkey, " Accounting Doc Reference Key
        bukrs TYPE vbrk-bukrs, " Company Code
        kunnr TYPE vbrk-kunnr, " Customer
        vbtyp TYPE vbrk-vbtyp, " SD Doc Type
        netwr TYPE vbrk-netwr, " Net Value
        waerk TYPE vbrk-waerk, " Currency
        fkdat TYPE vbrk-fkdat, " Billing Date
        erdat TYPE vbrk-erdat, " Creation Date
        erzet TYPE vbrk-erzet, " Creation Time
        ernam TYPE vbrk-ernam, " Creator
        belnr TYPE bkpf-belnr, " Acct Doc
        gjahr TYPE bkpf-gjahr, " Fiscal Year
        cpudt TYPE bkpf-cpudt, " Acct Doc Entry Date
        cputm TYPE bkpf-cputm, " Acct Doc Entry Time
        usnam TYPE bkpf-usnam, " Acct Doc User
        stblg TYPE bkpf-stblg, " Reversal Doc
      END OF gs_invoice.

DATA: gt_invoices LIKE TABLE OF gs_invoice.

*&---------------------------------------------------------------------*
*& Selection Screen
*&---------------------------------------------------------------------*
SELECT-OPTIONS: s_erdat FOR vbrk-erdat OBLIGATORY,
                s_bukrs FOR vbrk-bukrs OBLIGATORY,
                s_vbtyp FOR vbrk-vbtyp.

PARAMETERS: p_path TYPE string DEFAULT '/usr/sap/trans/tmp/invoice_extract.csv' OBLIGATORY.

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

  " 1. Select base set of invoices
  SELECT vbrk~vbeln, vbrk~bukrs, vbrk~kunnr, vbrk~vbtyp, vbrk~netwr, vbrk~waerk,
         vbrk~fkdat, vbrk~erdat, vbrk~erzet, vbrk~ernam, 
         bkpf~belnr, bkpf~gjahr, bkpf~cpudt, bkpf~cputm, bkpf~usnam, bkpf~stblg, bkpf~awkey
    INTO CORRESPONDING FIELDS OF TABLE gt_invoices
    FROM vbrk
    INNER JOIN bkpf ON bkpf~awkey = vbrk~vbeln AND bkpf~awtyp = 'VBRK'
    WHERE vbrk~erdat IN s_erdat
      AND vbrk~bukrs IN s_bukrs
      AND vbrk~vbtyp IN s_vbtyp.

  IF gt_invoices IS INITIAL.
    MESSAGE 'No invoices found for the selected criteria.' TYPE 'I'.
    RETURN.
  ENDIF.

  LOOP AT gt_invoices INTO gs_invoice.

    CLEAR gs_event_log.
    gs_event_log-invoicenumber      = gs_invoice-vbeln.
    gs_event_log-billingdocumenttype = gs_invoice-vbtyp.
    gs_event_log-customernumber     = gs_invoice-kunnr.
    gs_event_log-companycode        = gs_invoice-bukrs.
    gs_event_log-totalinvoiceamount = gs_invoice-netwr.

    " Activity: Invoice Generated (using accounting doc creation)
    gs_event_log-activityname = 'Invoice Generated'.
    gs_event_log-username     = gs_invoice-usnam.
    CONCATENATE gs_invoice-cpudt gs_invoice-cputm INTO DATA(lv_ts_gen).
    CONVERT DATE gs_invoice-cpudt TIME gs_invoice-cputm INTO TIME STAMP gs_event_log-eventtime TIME ZONE sy-zonlo.
    APPEND gs_event_log TO gt_event_log.
    
    " Activity: Invoice Posted (same as generated for non-parked docs)
    gs_event_log-activityname = 'Invoice Posted'.
    gs_event_log-username     = gs_invoice-usnam.
    CONVERT DATE gs_invoice-cpudt TIME gs_invoice-cputm INTO TIME STAMP gs_event_log-eventtime TIME ZONE sy-zonlo.
    APPEND gs_event_log TO gt_event_log.
    
    " Activity: Invoice Approved (inferred by posting)
    gs_event_log-activityname = 'Invoice Approved'.
    APPEND gs_event_log TO gt_event_log. 

    " Activity: Invoice Sent To Customer
    SELECT SINGLE addat, aduhr FROM nast
      INTO (DATA(lv_nast_date), DATA(lv_nast_time))
      WHERE kappl = 'V3' AND objky = gs_invoice-vbeln AND vszst > '0'.
    IF sy-subrc = 0.
      gs_event_log-activityname = 'Invoice Sent To Customer'.
      gs_event_log-username     = sy-uname.
      CONVERT DATE lv_nast_date TIME lv_nast_time INTO TIME STAMP gs_event_log-eventtime TIME ZONE sy-zonlo.
      APPEND gs_event_log TO gt_event_log.
    ENDIF.

    " Activity: Invoice Corrected / Reversed
    IF gs_invoice-stblg IS NOT INITIAL.
      SELECT SINGLE cpudt, cputm, usnam FROM bkpf
        INTO (DATA(lv_rev_date), DATA(lv_rev_time), DATA(lv_rev_user))
        WHERE belnr = gs_invoice-stblg AND gjahr = gs_invoice-gjahr.
      IF sy-subrc = 0.
        gs_event_log-activityname = 'Invoice Corrected'.
        gs_event_log-username     = lv_rev_user.
        CONVERT DATE lv_rev_date TIME lv_rev_time INTO TIME STAMP gs_event_log-eventtime TIME ZONE sy-zonlo.
        APPEND gs_event_log TO gt_event_log.
      ENDIF.
    ENDIF.

    " Activity: Payment Applied, Cleared, Due Date, Written Off (from BSEG)
    SELECT SINGLE augdt, augbl, zfBDT, hkont FROM bseg
        INTO (DATA(lv_augdt), DATA(lv_augbl), DATA(lv_zfbdt), DATA(lv_hkont))
        WHERE bukrs = gs_invoice-bukrs
          AND belnr = gs_invoice-belnr
          AND gjahr = gs_invoice-gjahr
          AND koart = 'D'. " Customer line
    IF sy-subrc = 0.
      " Due Date Reached (Calculated event)
      IF lv_zfbdt IS NOT INITIAL.
        gs_event_log-activityname = 'Invoice Due Date Reached'.
        gs_event_log-username     = 'System'.
        CONVERT DATE lv_zfbdt INTO TIME STAMP gs_event_log-eventtime TIME ZONE sy-zonlo.
        APPEND gs_event_log TO gt_event_log.
      ENDIF.

      " Cleared, Applied, Write-Off
      IF lv_augdt IS NOT INITIAL.
          SELECT SINGLE usnam, cpudt, cputm, blart FROM bkpf
              INTO (DATA(lv_clear_user), DATA(lv_clear_date), DATA(lv_clear_time), DATA(lv_clear_type))
              WHERE belnr = lv_augbl AND bukrs = gs_invoice-bukrs.
          IF sy-subrc = 0.
              gs_event_log-username = lv_clear_user.
              CONVERT DATE lv_clear_date TIME lv_clear_time INTO TIME STAMP gs_event_log-eventtime TIME ZONE sy-zonlo.

              IF lv_clear_type = 'DZ'. " Standard Customer Payment
                  gs_event_log-activityname = 'Customer Payment Received'. APPEND gs_event_log TO gt_event_log.
                  gs_event_log-activityname = 'Payment Applied To Invoice'. APPEND gs_event_log TO gt_event_log.
                  gs_event_log-activityname = 'Invoice Cleared'. APPEND gs_event_log TO gt_event_log.
              ELSE. " Assuming other clearing doc types could be write-offs
                  gs_event_log-activityname = 'Invoice Written Off'.
                  APPEND gs_event_log TO gt_event_log.
              ENDIF.
          ENDIF.
       ENDIF.
    ENDIF.

    " Activity: Payment Reminder Issued (Dunning)
    SELECT COUNT(*) FROM mhnk WHERE kunnr = gs_invoice-kunnr AND bukrs = gs_invoice-bukrs AND lafdn > gs_invoice-cpudt.
    IF sy-subrc = 0 AND sy-dbcnt > 0.
      SELECT SINGLE lafdn FROM mhnk
        INTO DATA(lv_dunning_date)
        WHERE kunnr = gs_invoice-kunnr AND bukrs = gs_invoice-bukrs AND lafdn > gs_invoice-cpudt.
      gs_event_log-activityname = 'Payment Reminder Issued'.
      gs_event_log-username     = 'System'.
      CONVERT DATE lv_dunning_date INTO TIME STAMP gs_event_log-eventtime TIME ZONE sy-zonlo.
      APPEND gs_event_log TO gt_event_log.
    ENDIF.

    " Activity: Invoice Parked (Example from VBKPF, may require system specific logic)
    SELECT SINGLE cpudt, cputm, usnam FROM vbkpf
      INTO (DATA(lv_park_date), DATA(lv_park_time), DATA(lv_park_user))
      WHERE awkey = gs_invoice-vbeln AND awsys = 'LOG' AND bstat = 'V'.
    IF sy-subrc = 0.
       gs_event_log-activityname = 'Invoice Parked'.
       gs_event_log-username     = lv_park_user.
       CONVERT DATE lv_park_date TIME lv_park_time INTO TIME STAMP gs_event_log-eventtime TIME ZONE sy-zonlo.
       APPEND gs_event_log TO gt_event_log.
    ENDIF.

    " Activity: Dispute Case Created (Requires Dispute Management module)
    SELECT SINGLE create_date, create_time, create_user FROM udm_case_attr00
        INTO (DATA(lv_disp_date), DATA(lv_disp_time), DATA(lv_disp_user))
        WHERE [Your logic to link invoice to dispute case, e.g., via a custom field or object link].
     IF sy-subrc = 0.
        gs_event_log-activityname = 'Dispute Case Created'.
        gs_event_log-username     = lv_disp_user.
        CONVERT DATE lv_disp_date TIME lv_disp_time INTO TIME STAMP gs_event_log-eventtime TIME ZONE sy-zonlo.
        APPEND gs_event_log TO gt_event_log.
     ENDIF.

  ENDLOOP.

*&---------------------------------------------------------------------*
*& Write data to file
*&---------------------------------------------------------------------*
  OPEN DATASET p_path FOR OUTPUT IN TEXT MODE ENCODING UTF-8.
  IF sy-subrc <> 0.
    MESSAGE 'Error opening file.' TYPE 'E'.
  ENDIF.

  " Header
  DATA(lv_header) = 'InvoiceNumber,ActivityName,EventTime,UserName,BillingDocumentType,CustomerNumber,CompanyCode,TotalInvoiceAmount'.
  TRANSFER lv_header TO p_path.

  LOOP AT gt_event_log INTO gs_event_log.
    DATA(lv_line) = |
{ gs_event_log-invoicenumber }|
,{ gs_event_log-activityname }|
,{ gs_event_log-eventtime }|
,{ gs_event_log-username }|
,{ gs_event_log-billingdocumenttype }|
,{ gs_event_log-customernumber }|
,{ gs_event_log-companycode }|
,{ gs_event_log-totalinvoiceamount }|.
    TRANSFER lv_line TO p_path.
  ENDLOOP.

  CLOSE DATASET p_path.

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

WITH InvoiceBase AS (
  SELECT 
    VBRK.VBELN AS InvoiceNumber,
    VBRK.FKART AS BillingDocumentType,
    VBRK.KUNRG AS CustomerNumber,
    VBRK.BUKRS AS CompanyCode,
    VBRK.NETWR AS TotalInvoiceAmount,
    VBRK.ERNAM AS CreatorName,
    VBRK.ERDAT AS CreationDate,
    VBRK.ERZET AS CreationTime
  FROM VBRK
  WHERE VBRK.ERDAT BETWEEN '20230101' AND '20231231' -- Filter by Invoice Creation Date
    AND VBRK.BUKRS IN ('1000') -- Filter by Company Code
    AND VBRK.FKART NOT IN ('S1', 'S2') -- Exclude cancelled invoices
)
-- 1. Invoice Generated
SELECT
  ib.InvoiceNumber,
  'Invoice Generated' AS ActivityName,
  CAST(CONCAT(ib.CreationDate, ib.CreationTime) AS TIMESTAMP) AS EventTime,
  ib.CreatorName AS UserName,
  ib.BillingDocumentType,
  ib.CustomerNumber,
  ib.CompanyCode,
  ib.TotalInvoiceAmount
FROM InvoiceBase ib

UNION ALL

-- 2. Invoice Parked
SELECT 
  SUBSTRING(b.AWKEY, 1, 10) AS InvoiceNumber,
  'Invoice Parked' AS ActivityName,
  CAST(CONCAT(b.CPUDT, b.CPUTM) AS TIMESTAMP) AS EventTime,
  b.USNAM AS UserName,
  ib.BillingDocumentType,
  ib.CustomerNumber,
  b.BUKRS AS CompanyCode,
  ib.TotalInvoiceAmount
FROM BKPF b
JOIN InvoiceBase ib ON SUBSTRING(b.AWKEY, 1, 10) = ib.InvoiceNumber
WHERE b.AWTYP = 'VBRK' AND b.BSTAT = 'V' AND b.CPUDT BETWEEN '20230101' AND '20231231'

UNION ALL

-- 3. Invoice Posted
SELECT 
  SUBSTRING(b.AWKEY, 1, 10) AS InvoiceNumber,
  'Invoice Posted' AS ActivityName,
  CAST(CONCAT(b.CPUDT, b.CPUTM) AS TIMESTAMP) AS EventTime,
  b.USNAM AS UserName,
  ib.BillingDocumentType,
  ib.CustomerNumber,
  b.BUKRS AS CompanyCode,
  ib.TotalInvoiceAmount
FROM BKPF b
JOIN InvoiceBase ib ON SUBSTRING(b.AWKEY, 1, 10) = ib.InvoiceNumber
WHERE b.AWTYP = 'VBRK' AND b.BSTAT = '' AND b.CPUDT BETWEEN '20230101' AND '20231231'

UNION ALL

-- 4. Invoice Approved (from Parked to Posted)
SELECT
  SUBSTRING(h.OBJECTID, 4, 10) AS InvoiceNumber,
  'Invoice Approved' as ActivityName,
  CAST(CONCAT(h.UDATE, h.UTIME) AS TIMESTAMP) AS EventTime,
  h.USERNAME AS UserName,
  ib.BillingDocumentType,
  ib.CustomerNumber,
  ib.CompanyCode,
  ib.TotalInvoiceAmount
FROM CDHDR h
JOIN CDPOS p ON h.MANDANT = p.MANDANT AND h.OBJECTCLAS = p.OBJECTCLAS AND h.OBJECTID = p.OBJECTID AND h.CHANGENR = p.CHANGENR
JOIN InvoiceBase ib ON SUBSTRING(h.OBJECTID, 4, 10) = ib.InvoiceNumber
WHERE h.OBJECTCLAS = 'BELEGV'
  AND p.TABNAME = 'BKPF'
  AND p.FNAME = 'BSTAT'
  AND p.VALUE_OLD = 'V'
  AND p.VALUE_NEW = ' '
  AND h.UDATE BETWEEN '20230101' AND '20231231'

UNION ALL

-- 5. Invoice Sent To Customer
SELECT
  n.OBJKY AS InvoiceNumber,
  'Invoice Sent To Customer' AS ActivityName,
  CAST(CONCAT(n.DATVR, n.UHRVR) AS TIMESTAMP) AS EventTime,
  n.VSTAT AS UserName, -- User who processed is not directly available, using processing status as a proxy
  ib.BillingDocumentType,
  ib.CustomerNumber,
  ib.CompanyCode,
  ib.TotalInvoiceAmount
FROM NAST n
JOIN InvoiceBase ib ON n.OBJKY = ib.InvoiceNumber
WHERE n.KSCHL = '[Your Invoice Output Type]' -- E.g., 'RD00'
  AND n.VSTAT = '1' -- Processed successfully
  AND n.DATVR BETWEEN '20230101' AND '20231231'

UNION ALL

-- 6. Invoice Corrected (Reversed)
SELECT
  SUBSTRING(orig_doc.AWKEY, 1, 10) AS InvoiceNumber,
  'Invoice Corrected' AS ActivityName,
  CAST(CONCAT(rev_doc.CPUDT, rev_doc.CPUTM) AS TIMESTAMP) AS EventTime,
  rev_doc.USNAM AS UserName,
  ib.BillingDocumentType,
  ib.CustomerNumber,
  rev_doc.BUKRS AS CompanyCode,
  ib.TotalInvoiceAmount
FROM BKPF orig_doc
JOIN BKPF rev_doc ON orig_doc.STBLG = rev_doc.BELNR AND orig_doc.BUKRS = rev_doc.BUKRS AND orig_doc.GJAHR = rev_doc.STJAH
JOIN InvoiceBase ib ON SUBSTRING(orig_doc.AWKEY, 1, 10) = ib.InvoiceNumber
WHERE orig_doc.AWTYP = 'VBRK' AND orig_doc.STBLG IS NOT NULL AND rev_doc.CPUDT BETWEEN '20230101' AND '20231231'

UNION ALL

-- 7. Invoice Due Date Reached
SELECT
  SUBSTRING(b.AWKEY, 1, 10) AS InvoiceNumber,
  'Invoice Due Date Reached' AS ActivityName,
  CAST(CONCAT(bs.ZFBDT, '000000') AS TIMESTAMP) AS EventTime,
  'System' AS UserName,
  ib.BillingDocumentType,
  ib.CustomerNumber,
  b.BUKRS AS CompanyCode,
  ib.TotalInvoiceAmount
FROM BSEG bs
JOIN BKPF b ON bs.MANDT = b.MANDT AND bs.BUKRS = b.BUKRS AND bs.BELNR = b.BELNR AND bs.GJAHR = b.GJAHR
JOIN InvoiceBase ib ON SUBSTRING(b.AWKEY, 1, 10) = ib.InvoiceNumber
WHERE b.AWTYP = 'VBRK' AND bs.KOART = 'D' AND bs.ZFBDT BETWEEN '20230101' AND '20231231'

UNION ALL

-- 8. Payment Reminder Issued
SELECT 
  SUBSTRING(b.AWKEY, 1, 10) AS InvoiceNumber,
  'Payment Reminder Issued' AS ActivityName,
  CAST(CONCAT(h.LAUFD, '000000') AS TIMESTAMP) AS EventTime, 
  h.LAUFI AS UserName, -- Dunning Run ID
  ib.BillingDocumentType, 
  ib.CustomerNumber, 
  d.BUKRS AS CompanyCode, 
  ib.TotalInvoiceAmount
FROM MHND d
JOIN MHNK h ON d.MANDT = h.MANDT AND d.LAUFD = h.LAUFD AND d.LAUFI = h.LAUFI
JOIN BKPF b ON d.MANDT = b.MANDT AND d.BUKRS = b.BUKRS AND d.BELNR = b.BELNR AND d.GJAHR = b.GJAHR
JOIN InvoiceBase ib ON SUBSTRING(b.AWKEY, 1, 10) = ib.InvoiceNumber
WHERE h.LAUFD BETWEEN '20230101' AND '20231231'

UNION ALL

-- 9. Dispute Case Created
SELECT
  attr.ATTR_VALUE AS InvoiceNumber,
  'Dispute Case Created' AS ActivityName,
  sc.CREATE_TIME AS EventTime,
  sc.CREATED_BY AS UserName,
  ib.BillingDocumentType,
  ib.CustomerNumber,
  ib.CompanyCode,
  ib.TotalInvoiceAmount
FROM SCMG_T_CASE_ATTR attr
JOIN SCASE sc ON attr.CASE_GUID = sc.CASE_GUID
JOIN InvoiceBase ib ON attr.ATTR_VALUE = ib.InvoiceNumber
WHERE attr.ATTR_NAME = '[Your Dispute Case Invoice Attribute]' -- e.g., 'INVOICE_ID'
  AND CAST(sc.CREATE_TIME AS DATE) BETWEEN '20230101' AND '20231231'

UNION ALL

-- 10, 11, 12. Clearing Events (Payment, Clearing, Write-Off)
SELECT 
  InvoiceNumber,
  ActivityName,
  EventTime,
  UserName,
  BillingDocumentType,
  CustomerNumber,
  CompanyCode,
  TotalInvoiceAmount
FROM (
  SELECT
    bsad.XBLNR AS InvoiceNumber,
    CASE 
      WHEN clearing_item.HKONT = '[Your Bad Debt G/L Account]' THEN 'Invoice Written Off'
      ELSE 'Customer Payment Received'
    END AS ActivityName,
    CAST(CONCAT(clearing_doc.CPUDT, clearing_doc.CPUTM) AS TIMESTAMP) AS EventTime,
    clearing_doc.USNAM AS UserName,
    ib.BillingDocumentType, 
    ib.CustomerNumber, 
    bsad.BUKRS AS CompanyCode,
    ib.TotalInvoiceAmount
  FROM BSAD bsad
  JOIN InvoiceBase ib ON bsad.XBLNR = ib.InvoiceNumber
  JOIN BKPF clearing_doc ON bsad.MANDT = clearing_doc.MANDT AND bsad.BUKRS = clearing_doc.BUKRS AND bsad.AUGBL = clearing_doc.BELNR AND bsad.AUGGJ = clearing_doc.GJAHR
  LEFT JOIN BSEG clearing_item ON clearing_doc.MANDT = clearing_item.MANDT AND clearing_doc.BUKRS = clearing_item.BUKRS AND clearing_doc.BELNR = clearing_item.BELNR AND clearing_doc.GJAHR = clearing_item.GJAHR AND clearing_item.HKONT = '[Your Bad Debt G/L Account]' -- e.g. '148000'
  WHERE bsad.AUGDT BETWEEN '20230101' AND '20231231' AND bsad.UMSKZ = ''

  UNION ALL

  SELECT
    bsad.XBLNR AS InvoiceNumber,
    'Payment Applied To Invoice' AS ActivityName,
    CAST(CONCAT(bsad.AUGDT, '000000') AS TIMESTAMP) AS EventTime,
    clearing_doc.USNAM AS UserName,
    ib.BillingDocumentType, 
    ib.CustomerNumber, 
    bsad.BUKRS AS CompanyCode,
    ib.TotalInvoiceAmount
  FROM BSAD bsad
  JOIN InvoiceBase ib ON bsad.XBLNR = ib.InvoiceNumber
  JOIN BKPF clearing_doc ON bsad.MANDT = clearing_doc.MANDT AND bsad.BUKRS = clearing_doc.BUKRS AND bsad.AUGBL = clearing_doc.BELNR AND bsad.AUGGJ = clearing_doc.GJAHR
  WHERE bsad.AUGDT BETWEEN '20230101' AND '20231231' AND bsad.UMSKZ = ''

  UNION ALL

  SELECT
    bsad.XBLNR AS InvoiceNumber,
    'Invoice Cleared' AS ActivityName,
    CAST(CONCAT(bsad.AUGDT, '235959') AS TIMESTAMP) AS EventTime, -- Add time to separate from 'Payment Applied'
    clearing_doc.USNAM AS UserName,
    ib.BillingDocumentType, 
    ib.CustomerNumber, 
    bsad.BUKRS AS CompanyCode,
    ib.TotalInvoiceAmount
  FROM BSAD bsad
  JOIN InvoiceBase ib ON bsad.XBLNR = ib.InvoiceNumber
  JOIN BKPF clearing_doc ON bsad.MANDT = clearing_doc.MANDT AND bsad.BUKRS = clearing_doc.BUKRS AND bsad.AUGBL = clearing_doc.BELNR AND bsad.AUGGJ = clearing_doc.GJAHR
  WHERE bsad.AUGDT BETWEEN '20230101' AND '20231231' AND bsad.UMSKZ = ''
) AS ClearingEvents

// ETL Data Extraction Logic for SAP Order-to-Cash Invoicing
// This represents the configuration logic within a graphical ETL tool.

// == Global Parameters ==
// $StartDate: '[Start Date]' (e.g., '2023-01-01')
// $EndDate: '[End Date]' (e.g., '2023-06-30')
// $CompanyCodes: '[Company Code(s)]' (e.g., '1000', '2000')
// $BillingDocTypes: '[Billing Document Type(s)]' (e.g., 'F1', 'F2')

// == Source 1: Invoice Generated ==
// Tables: VBRK
DATA_SOURCE generated_invoices FROM VBRK WHERE
  ERDAT >= $StartDate AND ERDAT <= $EndDate
  AND BUKRS IN ($CompanyCodes)
  AND FKART IN ($BillingDocTypes)
MAP {
  InvoiceNumber: VBELN,
  ActivityName: 'Invoice Generated',
  EventTime: ERDAT + ERZET, // Combine date and time
  UserName: ERNAM,
  BillingDocumentType: FKART,
  CustomerNumber: KUNAG,
  CompanyCode: BUKRS,
  TotalInvoiceAmount: NETWR
}

// == Source 2: Invoice Posted ==
// Tables: BKPF joined with VBRK
DATA_SOURCE posted_invoices FROM BKPF as A
  INNER JOIN VBRK as B ON (A.AWKEY = B.VBELN AND A.AWTYP = 'VBRK')
  WHERE A.BUDAT >= $StartDate AND A.BUDAT <= $EndDate
  AND A.BUKRS IN ($CompanyCodes)
  AND A.BSTAT = ' '
MAP {
  InvoiceNumber: B.VBELN,
  ActivityName: 'Invoice Posted',
  EventTime: A.BUDAT + A.CPUTM, // Posting date and entry time
  UserName: A.USNAM,
  BillingDocumentType: B.FKART,
  CustomerNumber: B.KUNAG,
  CompanyCode: A.BUKRS,
  TotalInvoiceAmount: B.NETWR
}

// == Source 3: Invoice Parked ==
// Tables: BKPF joined with VBRK
DATA_SOURCE parked_invoices FROM BKPF as A
  INNER JOIN VBRK as B ON (A.AWKEY = B.VBELN AND A.AWTYP = 'VBRK')
  WHERE A.CPUDT >= $StartDate AND A.CPUDT <= $EndDate
  AND A.BUKRS IN ($CompanyCodes)
  AND A.BSTAT = 'V'
MAP {
  InvoiceNumber: B.VBELN,
  ActivityName: 'Invoice Parked',
  EventTime: A.CPUDT + A.CPUTM,
  UserName: A.USNAM,
  BillingDocumentType: B.FKART,
  CustomerNumber: B.KUNAG,
  CompanyCode: A.BUKRS,
  TotalInvoiceAmount: B.NETWR
}

// == Source 4: Invoice Approved (Transition from Parked to Posted) ==
// Tables: BKPF joined with VBRK
DATA_SOURCE approved_invoices FROM BKPF as A
  INNER JOIN VBRK as B ON (A.AWKEY = B.VBELN AND A.AWTYP = 'VBRK')
  WHERE A.BUDAT >= $StartDate AND A.BUDAT <= $EndDate
  AND A.BUKRS IN ($CompanyCodes)
  AND A.BSTAT = ' '
  AND EXISTS (SELECT 1 FROM VBELEGV C WHERE C.BELNR = A.BELNR) // Check if it was ever parked
MAP {
  InvoiceNumber: B.VBELN,
  ActivityName: 'Invoice Approved',
  EventTime: A.BUDAT + A.CPUTM, // Use posting date as approval date
  UserName: A.USNAM,
  BillingDocumentType: B.FKART,
  CustomerNumber: B.KUNAG,
  CompanyCode: A.BUKRS,
  TotalInvoiceAmount: B.NETWR
}

// == Source 5: Invoice Sent To Customer ==
// Tables: NAST joined with VBRK
DATA_SOURCE sent_invoices FROM NAST as A
  INNER JOIN VBRK as B ON (A.OBJKY = B.VBELN)
  WHERE A.ERDAT >= $StartDate AND A.ERDAT <= $EndDate
  AND B.BUKRS IN ($CompanyCodes)
  AND A.VSTAT = '1' // Successfully processed
MAP {
  InvoiceNumber: B.VBELN,
  ActivityName: 'Invoice Sent To Customer',
  EventTime: A.ERDAT + A.ERUHR,
  UserName: A.USNAM,
  BillingDocumentType: B.FKART,
  CustomerNumber: B.KUNAG,
  CompanyCode: B.BUKRS,
  TotalInvoiceAmount: B.NETWR
}

// == Source 6: Invoice Corrected (Reversed) ==
// Tables: VBRK (for the reversal document)
DATA_SOURCE corrected_invoices FROM VBRK as A
  WHERE A.ERDAT >= $StartDate AND A.ERDAT <= $EndDate
  AND A.BUKRS IN ($CompanyCodes)
  AND A.SFAKN <> '' // SFAKN is the original cancelled invoice
MAP {
  InvoiceNumber: A.SFAKN, // Case ID is the original invoice
  ActivityName: 'Invoice Corrected',
  EventTime: A.ERDAT + A.ERZET,
  UserName: A.ERNAM,
  BillingDocumentType: A.FKART,
  CustomerNumber: A.KUNAG,
  CompanyCode: A.BUKRS,
  TotalInvoiceAmount: NULL // Amount belongs to the reversal doc, not original
}

// == Source 7: Invoice Due Date Reached ==
// Tables: BSEG joined with VBRK
DATA_SOURCE due_invoices FROM BSEG as A
  INNER JOIN BKPF as H ON (A.BUKRS = H.BUKRS AND A.BELNR = H.BELNR AND A.GJAHR = H.GJAHR)
  INNER JOIN VBRK as B ON (H.AWKEY = B.VBELN AND H.AWTYP = 'VBRK')
  WHERE A.NETDT >= $StartDate AND A.NETDT <= $EndDate
  AND A.BUKRS IN ($CompanyCodes)
  AND A.KOART = 'D' // Customer line item
MAP {
  InvoiceNumber: B.VBELN,
  ActivityName: 'Invoice Due Date Reached',
  EventTime: A.NETDT, // Net due date
  UserName: 'System',
  BillingDocumentType: B.FKART,
  CustomerNumber: B.KUNAG,
  CompanyCode: A.BUKRS,
  TotalInvoiceAmount: B.NETWR
}

// == Source 8: Payment Reminder Issued ==
// Tables: MHNK, MHND, VBRK
DATA_SOURCE reminders FROM MHNK as A
  INNER JOIN MHND as D ON (A.LAUFD = D.LAUFD AND A.LAUFI = D.LAUFI)
  INNER JOIN VBRK as B ON (SUBSTRING(D.XBLNR, 1, 10) = B.VBELN) // XBLNR may need parsing
  WHERE A.LAUFD >= $StartDate AND A.LAUFD <= $EndDate
  AND D.BUKRS IN ($CompanyCodes)
MAP {
  InvoiceNumber: B.VBELN,
  ActivityName: 'Payment Reminder Issued',
  EventTime: A.LAUFD, // Dunning date
  UserName: A.IDAPS,
  BillingDocumentType: B.FKART,
  CustomerNumber: B.KUNAG,
  CompanyCode: D.BUKRS,
  TotalInvoiceAmount: B.NETWR
}

// == Source 9: Dispute Case Created ==
// Tables: UDM_CASE_ATTR00
DATA_SOURCE disputes FROM UDM_CASE_ATTR00 as A
  WHERE A.CREATE_TIMESTAMP >= $StartDate // Timestamp format may vary
  AND A.FIN_COMP_CODE IN ($CompanyCodes)
  AND A.PROCESS = 'FIN_FSCM_DIS'
MAP {
  InvoiceNumber: A.BILL_DOC_ID,
  ActivityName: 'Dispute Case Created',
  EventTime: A.CREATE_TIMESTAMP,
  UserName: A.CREATE_USER,
  BillingDocumentType: NULL,
  CustomerNumber: A.BP_NUMBER,
  CompanyCode: A.FIN_COMP_CODE,
  TotalInvoiceAmount: A.DISPUTED_AMOUNT
}

// == Source 10: Customer Payment Received ==
// Tables: BKPF
DATA_SOURCE payments FROM BKPF
  WHERE BUDAT >= $StartDate AND BUDAT <= $EndDate
  AND BUKRS IN ($CompanyCodes)
  AND BLART = 'DZ' // Example for Customer Payment
MAP {
  InvoiceNumber: NULL, // Invoice not yet known
  ActivityName: 'Customer Payment Received',
  EventTime: BUDAT + CPUTM,
  UserName: USNAM,
  BillingDocumentType: NULL,
  CustomerNumber: NULL, // Requires join to BSEG to get customer
  CompanyCode: BUKRS,
  TotalInvoiceAmount: NULL
}

// == Source 11 & 12: Payment Applied To Invoice & Invoice Cleared ==
// Tables: BSEG joined with VBRK
DATA_SOURCE cleared_items FROM BSEG as A
  INNER JOIN BKPF as H ON (A.BUKRS = H.BUKRS AND A.BELNR = H.BELNR AND A.GJAHR = H.GJAHR)
  INNER JOIN VBRK as B ON (H.AWKEY = B.VBELN AND H.AWTYP = 'VBRK')
  WHERE A.AUGDT >= $StartDate AND A.AUGDT <= $EndDate
  AND A.BUKRS IN ($CompanyCodes)
  AND A.AUGBL <> ''
// Generate two records from this source
MAP {
  InvoiceNumber: B.VBELN,
  ActivityName: 'Payment Applied To Invoice',
  EventTime: A.AUGDT, // Clearing Date
  UserName: H.USNAM, // User from header of original invoice doc
  BillingDocumentType: B.FKART,
  CustomerNumber: B.KUNAG,
  CompanyCode: A.BUKRS,
  TotalInvoiceAmount: B.NETWR
}
UNION WITH {
  InvoiceNumber: B.VBELN,
  ActivityName: 'Invoice Cleared',
  EventTime: A.AUGDT, // Clearing Date
  UserName: H.USNAM,
  BillingDocumentType: B.FKART,
  CustomerNumber: B.KUNAG,
  CompanyCode: A.BUKRS,
  TotalInvoiceAmount: B.NETWR
}

// == Source 13: Invoice Written Off ==
// Tables: BSEG (for the invoice line) and BKPF (for clearing doc type)
DATA_SOURCE written_off FROM BSEG as A
  INNER JOIN BKPF as H ON (A.BUKRS = H.BUKRS AND A.BELNR = H.BELNR AND A.GJAHR = H.GJAHR)
  INNER JOIN VBRK as B ON (H.AWKEY = B.VBELN AND H.AWTYP = 'VBRK')
  INNER JOIN BKPF as C ON (A.AUGBL = C.BELNR AND A.BUKRS = C.BUKRS AND A.AUGGJ = C.GJAHR)
  WHERE A.AUGDT >= $StartDate AND A.AUGDT <= $EndDate
  AND A.BUKRS IN ($CompanyCodes)
  AND C.BLART = '[Your Write-Off Document Type]' // e.g., 'AB'
MAP {
  InvoiceNumber: B.VBELN,
  ActivityName: 'Invoice Written Off',
  EventTime: A.AUGDT,
  UserName: C.USNAM, // User who posted the write-off
  BillingDocumentType: B.FKART,
  CustomerNumber: B.KUNAG,
  CompanyCode: A.BUKRS,
  TotalInvoiceAmount: B.NETWR
}

// == Final Union of all sources ==
OUTPUT generated_invoices
  UNION ALL posted_invoices
  UNION ALL parked_invoices
  UNION ALL approved_invoices
  UNION ALL sent_invoices
  UNION ALL corrected_invoices
  UNION ALL due_invoices
  UNION ALL reminders
  UNION ALL disputes
  UNION ALL payments
  UNION ALL cleared_items
  UNION ALL written_off