Datatemplate voor de verwerking van retouren en terugbetalingen

REPORT Z_PM_RETURN_EXTRACTION.

" =======================================================================
" DATA DECLARATIONS
" =======================================================================

TYPES: BEGIN OF ty_event_log,
         returncaseid           TYPE vbeln_va,
         activityname         TYPE string,
         eventtime            TYPE timestamp,
         sourcesystem         TYPE sysysid,
         lastdataupdate       TYPE timestamp,
         processingagent      TYPE ernam,
         returnreason         TYPE augru,
         materialnumber       TYPE matnr,
         returnchannel        TYPE string, " Placeholder, needs custom logic
         requestedrefundamount TYPE netwr,
         actualrefundamount   TYPE netwr,
       END OF ty_event_log.

DATA: lt_event_log TYPE TABLE OF ty_event_log,
      ls_event_log TYPE ty_event_log.

DATA: lv_timestamp     TYPE timestamp,
      lv_last_update   TYPE timestamp.

" =======================================================================
" SELECTION SCREEN
" =======================================================================

SELECT-OPTIONS: s_erdat FOR sy-datum OBLIGATORY,
                s_auart FOR vbak-auart.
PARAMETERS:     p_bukrs TYPE bukrs OBLIGATORY.

" =======================================================================
" MAIN LOGIC
" =======================================================================

START-OF-SELECTION.

  " Set static values for the extraction run
  GET TIME STAMP FIELD lv_last_update.

  " 1. Return Order Created & 6. Credit Memo Request Created
  " Note: In many SAP standard processes, the Return Order itself acts as the Credit Memo Request.
  SELECT h~vbeln, h~erdat, h~erzet, h~ernam, h~augru, i~matnr, i~netwr
    INTO TABLE @DATA(lt_return_orders)
    FROM vbak AS h
    INNER JOIN vbap AS i ON h~vbeln = i~vbeln
    WHERE h~erdat IN @s_erdat
      AND h~auart IN @s_auart
      AND h~bukrs_vf = @p_bukrs.

  LOOP AT lt_return_orders INTO DATA(ls_return_order).
    " Activity: Return Order Created
    ls_event_log-returncaseid = ls_return_order-vbeln.
    ls_event_log-activityname = 'Return Order Created'.
    CONVERT DATE ls_return_order-erdat TIME ls_return_order-erzet INTO TIME STAMP ls_event_log-eventtime TIME ZONE sy-zonlo.
    ls_event_log-sourcesystem = sy-sysid.
    ls_event_log-lastdataupdate = lv_last_update.
    ls_event_log-processingagent = ls_return_order-ernam.
    ls_event_log-returnreason = ls_return_order-augru.
    ls_event_log-materialnumber = ls_return_order-matnr.
    ls_event_log-requestedrefundamount = ls_return_order-netwr.
    APPEND ls_event_log TO lt_event_log.

    " Activity: Credit Memo Request Created
    ls_event_log-activityname = 'Credit Memo Request Created'.
    APPEND ls_event_log TO lt_event_log.
  ENDLOOP.

  IF lt_return_orders IS NOT INITIAL.
    " 2. Return Order Block Removed (Example for Delivery Block)
    SELECT objectid, udate, utime, username
      FROM cdhdr
      INTO TABLE @DATA(lt_block_changes)
      FOR ALL ENTRIES IN @lt_return_orders
      WHERE objectclas = 'VERKBELEG'
        AND objectid   = @lt_return_orders-vbeln
        AND tcode      = 'VA02'.

    IF sy-subrc = 0.
      SELECT objectid, fname, value_new
        INTO TABLE @DATA(lt_cdpos_block)
        FROM cdpos
        FOR ALL ENTRIES IN @lt_block_changes
        WHERE objectclas = @lt_block_changes-objectclas
          AND objectid   = @lt_block_changes-objectid
          AND changenr   = @lt_block_changes-changenr
          AND tabname    = 'VBAK'
          AND fname      = 'LIFSK' " Delivery Block field
          AND value_new  = ''.

      LOOP AT lt_cdpos_block INTO DATA(ls_cdpos_block).
        READ TABLE lt_block_changes INTO DATA(ls_block_change) WITH KEY objectid = ls_cdpos_block-objectid.
        IF sy-subrc = 0.
          ls_event_log-returncaseid = ls_cdpos_block-objectid.
          ls_event_log-activityname = 'Return Order Block Removed'.
          CONVERT DATE ls_block_change-udate TIME ls_block_change-utime INTO TIME STAMP ls_event_log-eventtime TIME ZONE sy-zonlo.
          ls_event_log-sourcesystem = sy-sysid.
          ls_event_log-lastdataupdate = lv_last_update.
          ls_event_log-processingagent = ls_block_change-username.
          CLEAR: ls_event_log-returnreason, ls_event_log-materialnumber, ls_event_log-requestedrefundamount, ls_event_log-actualrefundamount.
          APPEND ls_event_log TO lt_event_log.
        ENDIF.
      ENDLOOP.
    ENDIF.

    " Get subsequent documents from flow
    SELECT vbelv, vbtyp_n, vbeln, rfbsk
      INTO TABLE @DATA(lt_doc_flow)
      FROM vbfa
      FOR ALL ENTRIES IN @lt_return_orders
      WHERE vbelv = @lt_return_orders-vbeln
        AND vbtyp_n IN ('J', 'R', 'M'). " J=Delivery, R=Goods Mvmt, M=Invoice

    " 3. Return Delivery Created
    SELECT vbeln, erdat, erzet, ernam FROM likp
      INTO TABLE @DATA(lt_deliveries)
      FOR ALL ENTRIES IN @lt_doc_flow
      WHERE vbeln = @lt_doc_flow-vbeln AND @lt_doc_flow-vbtyp_n = 'J'.

    LOOP AT lt_deliveries INTO DATA(ls_delivery).
      READ TABLE lt_doc_flow INTO DATA(ls_doc_flow_del) WITH KEY vbeln = ls_delivery-vbeln.
      ls_event_log-returncaseid = ls_doc_flow_del-vbelv.
      ls_event_log-activityname = 'Return Delivery Created'.
      CONVERT DATE ls_delivery-erdat TIME ls_delivery-erzet INTO TIME STAMP ls_event_log-eventtime TIME ZONE sy-zonlo.
      ls_event_log-sourcesystem = sy-sysid.
      ls_event_log-lastdataupdate = lv_last_update.
      ls_event_log-processingagent = ls_delivery-ernam.
      APPEND ls_event_log TO lt_event_log.
    ENDLOOP.

    " 4. Goods Receipt Posted
    SELECT mblnr, mjahr, budat, cputm, usnam FROM mkpf
      INTO TABLE @DATA(lt_goods_receipt)
      FOR ALL ENTRIES IN @lt_doc_flow
      WHERE xblnr = @lt_doc_flow-vbeln AND @lt_doc_flow-vbtyp_n = 'R'.

    LOOP AT lt_goods_receipt INTO DATA(ls_goods_receipt).
      READ TABLE lt_doc_flow INTO DATA(ls_doc_flow_gr) WITH KEY vbeln = ls_goods_receipt-mblnr.
      ls_event_log-returncaseid = ls_doc_flow_gr-vbelv.
      ls_event_log-activityname = 'Goods Receipt Posted'.
      CONVERT DATE ls_goods_receipt-budat TIME ls_goods_receipt-cputm INTO TIME STAMP ls_event_log-eventtime TIME ZONE sy-zonlo.
      ls_event_log-sourcesystem = sy-sysid.
      ls_event_log-lastdataupdate = lv_last_update.
      ls_event_log-processingagent = ls_goods_receipt-usnam.
      APPEND ls_event_log TO lt_event_log.
    ENDLOOP.

    " 5. Usage Decision Made (Requires QM Module implementation)
    SELECT q~prueflos, q~vdatu, q~vzeit, q~vname
      FROM qals AS q
      INNER JOIN qave AS v ON q~prueflos = v~prueflos
      INTO TABLE @DATA(lt_usage_decisions)
      FOR ALL ENTRIES IN @lt_deliveries
      WHERE q~kdauf = @lt_deliveries-vbeln.

    LOOP AT lt_usage_decisions INTO DATA(ls_ud).
       SELECT SINGLE vbelv FROM vbfa INTO @DATA(lv_vbelv_ud) WHERE vbeln = @ls_ud-prueflos.
       ls_event_log-returncaseid = lv_vbelv_ud.
       ls_event_log-activityname = 'Usage Decision Made'.
       CONVERT DATE ls_ud-vdatu TIME ls_ud-vzeit INTO TIME STAMP ls_event_log-eventtime TIME ZONE sy-zonlo.
       ls_event_log-sourcesystem = sy-sysid.
       ls_event_log-lastdataupdate = lv_last_update.
       ls_event_log-processingagent = ls_ud-vname.
       APPEND ls_event_log TO lt_event_log.
    ENDLOOP.

    " 7. Credit Memo Created
    SELECT h~vbeln, h~erdat, h~erzet, h~ernam, i~netwr, i~vgbel
      INTO TABLE @DATA(lt_cred_memos)
      FROM vbrk AS h
      INNER JOIN vbrp AS i ON h~vbeln = i~vbeln
      FOR ALL ENTRIES IN @lt_doc_flow
      WHERE h~vbeln = @lt_doc_flow-vbeln AND @lt_doc_flow-vbtyp_n = 'M'.

    LOOP AT lt_cred_memos INTO DATA(ls_cred_memo).
      ls_event_log-returncaseid = ls_cred_memo-vgbel.
      ls_event_log-activityname = 'Credit Memo Created'.
      CONVERT DATE ls_cred_memo-erdat TIME ls_cred_memo-erzet INTO TIME STAMP ls_event_log-eventtime TIME ZONE sy-zonlo.
      ls_event_log-sourcesystem = sy-sysid.
      ls_event_log-lastdataupdate = lv_last_update.
      ls_event_log-processingagent = ls_cred_memo-ernam.
      ls_event_log-actualrefundamount = ls_cred_memo-netwr.
      APPEND ls_event_log TO lt_event_log.

      " 8. Credit Memo Posted To FI
      SELECT SINGLE budat, cputime, usnam FROM bkpf
        INTO @DATA(ls_bkpf_post)
        WHERE awkey = @ls_cred_memo-vbeln AND awtyp = 'VBRK'.
      IF sy-subrc = 0.
        ls_event_log-activityname = 'Credit Memo Posted To FI'.
        CONVERT DATE ls_bkpf_post-budat TIME ls_bkpf_post-cputime INTO TIME STAMP ls_event_log-eventtime TIME ZONE sy-zonlo.
        ls_event_log-processingagent = ls_bkpf_post-usnam.
        APPEND ls_event_log TO lt_event_log.
      ENDIF.

      " 12. Credit Memo Cleared
      SELECT SINGLE augdt FROM bseg
        INTO @DATA(lv_augdt)
        WHERE bukrs = @p_bukrs
          AND belnr = @ls_cred_memo-vbeln
          AND augdt IS NOT NULL.
      IF sy-subrc = 0 AND lv_augdt IS NOT INITIAL.
        ls_event_log-activityname = 'Credit Memo Cleared'.
        CONVERT DATE lv_augdt INTO TIME STAMP ls_event_log-eventtime TIME ZONE sy-zonlo.
        CLEAR: ls_event_log-processingagent.
        APPEND ls_event_log TO lt_event_log.
      ENDIF.
    ENDLOOP.

    " 9. Return Order Item Completed
    SELECT vbeln, erdat, erzet, ernam, abgru FROM vbap
      INTO TABLE @DATA(lt_items_completed)
      FOR ALL ENTRIES IN @lt_return_orders
      WHERE vbeln = @lt_return_orders-vbeln
        AND gbsta = 'C'.

    LOOP AT lt_items_completed INTO DATA(ls_item_completed).
      ls_event_log-returncaseid = ls_item_completed-vbeln.
      ls_event_log-activityname = 'Return Order Item Completed'.
      CONVERT DATE ls_item_completed-erdat TIME ls_item_completed-erzet INTO TIME STAMP ls_event_log-eventtime TIME ZONE sy-zonlo.
      ls_event_log-sourcesystem = sy-sysid.
      ls_event_log-lastdataupdate = lv_last_update.
      ls_event_log-processingagent = ls_item_completed-ernam.
      APPEND ls_event_log TO lt_event_log.
    ENDLOOP.

    " 10. Return Order Completed
    SELECT vbeln, erdat, erzet, ernam FROM vbak
      INTO TABLE @DATA(lt_orders_completed)
      FOR ALL ENTRIES IN @lt_return_orders
      WHERE vbeln = @lt_return_orders-vbeln
        AND gbsta = 'C'.

    LOOP AT lt_orders_completed INTO DATA(ls_order_completed).
      ls_event_log-returncaseid = ls_order_completed-vbeln.
      ls_event_log-activityname = 'Return Order Completed'.
      CONVERT DATE ls_order_completed-erdat TIME ls_order_completed-erzet INTO TIME STAMP ls_event_log-eventtime TIME ZONE sy-zonlo.
      ls_event_log-sourcesystem = sy-sysid.
      ls_event_log-lastdataupdate = lv_last_update.
      ls_event_log-processingagent = ls_order_completed-ernam.
      APPEND ls_event_log TO lt_event_log.
    ENDLOOP.

    " 11. Return Order Item Rejected
    SELECT vbeln, erdat, erzet, ernam FROM vbap
      INTO TABLE @DATA(lt_items_rejected)
      FOR ALL ENTRIES IN @lt_return_orders
      WHERE vbeln = @lt_return_orders-vbeln
        AND abgru IS NOT NULL AND abgru <> ''.

    LOOP AT lt_items_rejected INTO DATA(ls_item_rejected).
      ls_event_log-returncaseid = ls_item_rejected-vbeln.
      ls_event_log-activityname = 'Return Order Item Rejected'.
      CONVERT DATE ls_item_rejected-erdat TIME ls_item_rejected-erzet INTO TIME STAMP ls_event_log-eventtime TIME ZONE sy-zonlo.
      ls_event_log-sourcesystem = sy-sysid.
      ls_event_log-lastdataupdate = lv_last_update.
      ls_event_log-processingagent = ls_item_rejected-ernam.
      APPEND ls_event_log TO lt_event_log.
    ENDLOOP.

  ENDIF.

  " =======================================================================
  " OUTPUT
  " =======================================================================
  cl_salv_table=>factory(
    IMPORTING
      r_salv_table = DATA(lo_alv)
    CHANGING
      t_table      = lt_event_log ).

  lo_alv->display( ).

WITH BaseReturnOrders AS (
  SELECT 
    VBAK.VBELN AS ReturnOrderNumber,
    VBAP.POSNR AS ReturnOrderItemNumber
  FROM VBAK
  JOIN VBAP ON VBAK.VBELN = VBAP.VBELN
  WHERE VBAK.AUART IN ('[YOUR_RETURN_ORDER_TYPES]') -- e.g., 'RE'
  AND VBAK.ERDAT BETWEEN '[START_DATE]' AND '[END_DATE]' -- e.g., '20230101' and '20231231'
)
-- 1. Return Order Created
SELECT
  CONCAT(B.ReturnOrderNumber, '-', B.ReturnOrderItemNumber) AS "ReturnCaseId",
  'Return Order Created' AS "ActivityName",
  TO_TIMESTAMP(CONCAT(VBAK.ERDAT, VBAK.ERZET), 'YYYYMMDDHH24MISS') AS "EventTime",
  'SAP ECC' AS "SourceSystem",
  CURRENT_TIMESTAMP AS "LastDataUpdate",
  VBAK.ERNAM AS "ProcessingAgent",
  VBAK.AUGRU AS "ReturnReason",
  VBAP.MATNR AS "MaterialNumber",
  VBAK.VTWEG AS "ReturnChannel",
  VBAP.NETWR AS "RequestedRefundAmount",
  NULL AS "ActualRefundAmount"
FROM BaseReturnOrders B
JOIN VBAK ON B.ReturnOrderNumber = VBAK.VBELN
JOIN VBAP ON B.ReturnOrderNumber = VBAP.VBELN AND B.ReturnOrderItemNumber = VBAP.POSNR

UNION ALL

-- 2. Return Order Block Removed
SELECT
  CONCAT(B.ReturnOrderNumber, '-', B.ReturnOrderItemNumber) AS "ReturnCaseId",
  'Return Order Block Removed' AS "ActivityName",
  TO_TIMESTAMP(CONCAT(CDHDR.UDATE, CDHDR.UTIME), 'YYYYMMDDHH24MISS') AS "EventTime",
  'SAP ECC' AS "SourceSystem",
  CURRENT_TIMESTAMP AS "LastDataUpdate",
  CDHDR.USERNAME AS "ProcessingAgent",
  VBAK.AUGRU AS "ReturnReason",
  VBAP.MATNR AS "MaterialNumber",
  VBAK.VTWEG AS "ReturnChannel",
  VBAP.NETWR AS "RequestedRefundAmount",
  NULL AS "ActualRefundAmount"
FROM BaseReturnOrders B
JOIN CDHDR ON B.ReturnOrderNumber = CDHDR.OBJECTID
JOIN CDPOS ON CDHDR.OBJECTCLAS = CDPOS.OBJECTCLAS AND CDHDR.OBJECTID = CDPOS.OBJECTID AND CDHDR.CHANGENR = CDPOS.CHANGENR
JOIN VBAK ON B.ReturnOrderNumber = VBAK.VBELN
JOIN VBAP ON B.ReturnOrderNumber = VBAP.VBELN AND B.ReturnOrderItemNumber = VBAP.POSNR
WHERE CDHDR.OBJECTCLAS = 'VERKBELEG'
  AND CDPOS.TABNAME = 'VBAK'
  AND CDPOS.FNAME IN ('LIFSK', 'FAKSK')
  AND CDPOS.VALUE_NEW = ''
  AND CDPOS.VALUE_OLD <> ''

UNION ALL

-- 3. Return Delivery Created
SELECT
  CONCAT(B.ReturnOrderNumber, '-', B.ReturnOrderItemNumber) AS "ReturnCaseId",
  'Return Delivery Created' AS "ActivityName",
  TO_TIMESTAMP(CONCAT(LIKP.ERDAT, LIKP.ERZET), 'YYYYMMDDHH24MISS') AS "EventTime",
  'SAP ECC' AS "SourceSystem",
  CURRENT_TIMESTAMP AS "LastDataUpdate",
  LIKP.ERNAM AS "ProcessingAgent",
  VBAK.AUGRU AS "ReturnReason",
  VBAP.MATNR AS "MaterialNumber",
  VBAK.VTWEG AS "ReturnChannel",
  VBAP.NETWR AS "RequestedRefundAmount",
  NULL AS "ActualRefundAmount"
FROM BaseReturnOrders B
JOIN VBAK ON B.ReturnOrderNumber = VBAK.VBELN
JOIN VBAP ON B.ReturnOrderNumber = VBAP.VBELN AND B.ReturnOrderItemNumber = VBAP.POSNR
JOIN VBFA ON B.ReturnOrderNumber = VBFA.VBELV AND B.ReturnOrderItemNumber = VBFA.POSNV AND VBFA.VBTYP_N = 'J'
JOIN LIKP ON VBFA.VBELN = LIKP.VBELN

UNION ALL

-- 4. Goods Receipt Posted
SELECT
  CONCAT(B.ReturnOrderNumber, '-', B.ReturnOrderItemNumber) AS "ReturnCaseId",
  'Goods Receipt Posted' AS "ActivityName",
  TO_TIMESTAMP(CONCAT(MKPF.CPUDT, MKPF.CPUTM), 'YYYYMMDDHH24MISS') AS "EventTime",
  'SAP ECC' AS "SourceSystem",
  CURRENT_TIMESTAMP AS "LastDataUpdate",
  MKPF.USNAM AS "ProcessingAgent",
  VBAK.AUGRU AS "ReturnReason",
  VBAP.MATNR AS "MaterialNumber",
  VBAK.VTWEG AS "ReturnChannel",
  VBAP.NETWR AS "RequestedRefundAmount",
  NULL AS "ActualRefundAmount"
FROM BaseReturnOrders B
JOIN VBAK ON B.ReturnOrderNumber = VBAK.VBELN
JOIN VBAP ON B.ReturnOrderNumber = VBAP.VBELN AND B.ReturnOrderItemNumber = VBAP.POSNR
JOIN VBFA ON B.ReturnOrderNumber = VBFA.VBELV AND B.ReturnOrderItemNumber = VBFA.POSNV AND VBFA.VBTYP_N = 'J'
JOIN LIPS ON VBFA.VBELN = LIPS.VBELN AND VBFA.POSNN = LIPS.POSNR
JOIN MSEG ON LIPS.VBELN = MSEG.LFBNR AND LIPS.POSNR = MSEG.LFPOS
JOIN MKPF ON MSEG.MBLNR = MKPF.MBLNR AND MSEG.MJAHR = MKPF.MJAHR
WHERE MSEG.BWART IN ('651', '653', '655') -- Standard returns movement types

UNION ALL

-- 5. Usage Decision Made
SELECT
  CONCAT(B.ReturnOrderNumber, '-', B.ReturnOrderItemNumber) AS "ReturnCaseId",
  'Usage Decision Made' AS "ActivityName",
  TO_TIMESTAMP(CONCAT(QAVE.ADAT, QAVE.ATIM), 'YYYYMMDDHH24MISS') AS "EventTime",
  'SAP ECC' AS "SourceSystem",
  CURRENT_TIMESTAMP AS "LastDataUpdate",
  QAVE.AENAME AS "ProcessingAgent",
  VBAK.AUGRU AS "ReturnReason",
  VBAP.MATNR AS "MaterialNumber",
  VBAK.VTWEG AS "ReturnChannel",
  VBAP.NETWR AS "RequestedRefundAmount",
  NULL AS "ActualRefundAmount"
FROM BaseReturnOrders B
JOIN VBAK ON B.ReturnOrderNumber = VBAK.VBELN
JOIN VBAP ON B.ReturnOrderNumber = VBAP.VBELN AND B.ReturnOrderItemNumber = VBAP.POSNR
JOIN VBFA ON B.ReturnOrderNumber = VBFA.VBELV AND B.ReturnOrderItemNumber = VBFA.POSNV AND VBFA.VBTYP_N = 'J'
JOIN QALS ON VBFA.VBELN = QALS.VBELN AND VBFA.POSNN = QALS.POSNR -- Linking inspection lot to delivery item
JOIN QAVE ON QALS.PRUEFLOS = QAVE.PRUEFLOS

UNION ALL

-- 6. Credit Memo Request Created (assumes a separate doc, or can be the return order itself)
SELECT 
  CONCAT(B.ReturnOrderNumber, '-', B.ReturnOrderItemNumber) AS "ReturnCaseId",
  'Credit Memo Request Created' AS "ActivityName",
  TO_TIMESTAMP(CONCAT(CMR_VBAK.ERDAT, CMR_VBAK.ERZET), 'YYYYMMDDHH24MISS') AS "EventTime",
  'SAP ECC' AS "SourceSystem",
  CURRENT_TIMESTAMP AS "LastDataUpdate",
  CMR_VBAK.ERNAM AS "ProcessingAgent",
  VBAK.AUGRU AS "ReturnReason",
  VBAP.MATNR AS "MaterialNumber",
  VBAK.VTWEG AS "ReturnChannel",
  VBAP.NETWR AS "RequestedRefundAmount",
  NULL AS "ActualRefundAmount"
FROM BaseReturnOrders B
JOIN VBAK ON B.ReturnOrderNumber = VBAK.VBELN
JOIN VBAP ON B.ReturnOrderNumber = VBAP.VBELN AND B.ReturnOrderItemNumber = VBAP.POSNR
JOIN VBFA ON B.ReturnOrderNumber = VBFA.VBELV AND B.ReturnOrderItemNumber = VBFA.POSNV AND VBFA.VBTYP_N = 'K' -- 'K' is Credit Memo Request
JOIN VBAK AS CMR_VBAK ON VBFA.VBELN = CMR_VBAK.VBELN

UNION ALL

-- 7. Credit Memo Created
SELECT
  CONCAT(B.ReturnOrderNumber, '-', B.ReturnOrderItemNumber) AS "ReturnCaseId",
  'Credit Memo Created' AS "ActivityName",
  TO_TIMESTAMP(CONCAT(VBRK.ERDAT, VBRK.ERZET), 'YYYYMMDDHH24MISS') AS "EventTime",
  'SAP ECC' AS "SourceSystem",
  CURRENT_TIMESTAMP AS "LastDataUpdate",
  VBRK.ERNAM AS "ProcessingAgent",
  VBAK.AUGRU AS "ReturnReason",
  VBAP.MATNR AS "MaterialNumber",
  VBAK.VTWEG AS "ReturnChannel",
  VBAP.NETWR AS "RequestedRefundAmount",
  VBRP.NETWR AS "ActualRefundAmount"
FROM BaseReturnOrders B
JOIN VBAK ON B.ReturnOrderNumber = VBAK.VBELN
JOIN VBAP ON B.ReturnOrderNumber = VBAP.VBELN AND B.ReturnOrderItemNumber = VBAP.POSNR
JOIN VBFA ON B.ReturnOrderNumber = VBFA.VBELV AND B.ReturnOrderItemNumber = VBFA.POSNV AND VBFA.VBTYP_N = 'M'
JOIN VBRK ON VBFA.VBELN = VBRK.VBELN
JOIN VBRP ON VBRK.VBELN = VBRP.VBELN AND VBFA.POSNN = VBRP.POSNR
WHERE VBRK.FKART IN ('[YOUR_CREDIT_MEMO_TYPES]') -- e.g., 'G2'

UNION ALL

-- 8. Credit Memo Posted To FI
SELECT
  CONCAT(B.ReturnOrderNumber, '-', B.ReturnOrderItemNumber) AS "ReturnCaseId",
  'Credit Memo Posted To FI' AS "ActivityName",
  TO_TIMESTAMP(CONCAT(BKPF.CPUDT, BKPF.CPUTM), 'YYYYMMDDHH24MISS') AS "EventTime",
  'SAP ECC' AS "SourceSystem",
  CURRENT_TIMESTAMP AS "LastDataUpdate",
  BKPF.USNAM AS "ProcessingAgent",
  VBAK.AUGRU AS "ReturnReason",
  VBAP.MATNR AS "MaterialNumber",
  VBAK.VTWEG AS "ReturnChannel",
  VBAP.NETWR AS "RequestedRefundAmount",
  VBRP.NETWR AS "ActualRefundAmount"
FROM BaseReturnOrders B
JOIN VBAK ON B.ReturnOrderNumber = VBAK.VBELN
JOIN VBAP ON B.ReturnOrderNumber = VBAP.VBELN AND B.ReturnOrderItemNumber = VBAP.POSNR
JOIN VBFA ON B.ReturnOrderNumber = VBFA.VBELV AND B.ReturnOrderItemNumber = VBFA.POSNV AND VBFA.VBTYP_N = 'M'
JOIN VBRK ON VBFA.VBELN = VBRK.VBELN
JOIN VBRP ON VBRK.VBELN = VBRP.VBELN AND VBFA.POSNN = VBRP.POSNR
JOIN BKPF ON VBRK.VBELN = BKPF.AWKEY AND BKPF.AWTYP = 'VBRK'

UNION ALL

-- 9. Return Order Item Completed
SELECT 
  CONCAT(B.ReturnOrderNumber, '-', B.ReturnOrderItemNumber) AS "ReturnCaseId",
  'Return Order Item Completed' AS "ActivityName",
  TO_TIMESTAMP(CONCAT(CDHDR.UDATE, CDHDR.UTIME), 'YYYYMMDDHH24MISS') AS "EventTime",
  'SAP ECC' AS "SourceSystem",
  CURRENT_TIMESTAMP AS "LastDataUpdate",
  CDHDR.USERNAME AS "ProcessingAgent",
  VBAK.AUGRU AS "ReturnReason",
  VBAP.MATNR AS "MaterialNumber",
  VBAK.VTWEG AS "ReturnChannel",
  VBAP.NETWR AS "RequestedRefundAmount",
  NULL AS "ActualRefundAmount"
FROM BaseReturnOrders B
JOIN CDHDR ON CONCAT(B.ReturnOrderNumber, LPAD(B.ReturnOrderItemNumber, 6, '0')) = CDHDR.OBJECTID
JOIN CDPOS ON CDHDR.OBJECTCLAS = CDPOS.OBJECTCLAS AND CDHDR.OBJECTID = CDPOS.OBJECTID AND CDHDR.CHANGENR = CDPOS.CHANGENR
JOIN VBAK ON B.ReturnOrderNumber = VBAK.VBELN
JOIN VBAP ON B.ReturnOrderNumber = VBAP.VBELN AND B.ReturnOrderItemNumber = VBAP.POSNR
WHERE CDHDR.OBJECTCLAS = 'VERKBELEG'
  AND CDPOS.TABNAME = 'VBUP'
  AND CDPOS.FNAME = 'GBSTA'
  AND CDPOS.VALUE_NEW = 'C'

UNION ALL

-- 10. Return Order Completed
SELECT
  CONCAT(B.ReturnOrderNumber, '-', B.ReturnOrderItemNumber) AS "ReturnCaseId",
  'Return Order Completed' AS "ActivityName",
  TO_TIMESTAMP(CONCAT(CDHDR.UDATE, CDHDR.UTIME), 'YYYYMMDDHH24MISS') AS "EventTime",
  'SAP ECC' AS "SourceSystem",
  CURRENT_TIMESTAMP AS "LastDataUpdate",
  CDHDR.USERNAME AS "ProcessingAgent",
  VBAK.AUGRU AS "ReturnReason",
  VBAP.MATNR AS "MaterialNumber",
  VBAK.VTWEG AS "ReturnChannel",
  VBAP.NETWR AS "RequestedRefundAmount",
  NULL AS "ActualRefundAmount"
FROM BaseReturnOrders B
JOIN CDHDR ON B.ReturnOrderNumber = CDHDR.OBJECTID
JOIN CDPOS ON CDHDR.OBJECTCLAS = CDPOS.OBJECTCLAS AND CDHDR.OBJECTID = CDPOS.OBJECTID AND CDHDR.CHANGENR = CDPOS.CHANGENR
JOIN VBAK ON B.ReturnOrderNumber = VBAK.VBELN
JOIN VBAP ON B.ReturnOrderNumber = VBAP.VBELN AND B.ReturnOrderItemNumber = VBAP.POSNR
WHERE CDHDR.OBJECTCLAS = 'VERKBELEG'
  AND CDPOS.TABNAME = 'VBUK'
  AND CDPOS.FNAME = 'GBSTK'
  AND CDPOS.VALUE_NEW = 'C'

UNION ALL

-- 11. Return Order Item Rejected
SELECT 
  CONCAT(B.ReturnOrderNumber, '-', B.ReturnOrderItemNumber) AS "ReturnCaseId",
  'Return Order Item Rejected' AS "ActivityName",
  TO_TIMESTAMP(CONCAT(CDHDR.UDATE, CDHDR.UTIME), 'YYYYMMDDHH24MISS') AS "EventTime",
  'SAP ECC' AS "SourceSystem",
  CURRENT_TIMESTAMP AS "LastDataUpdate",
  CDHDR.USERNAME AS "ProcessingAgent",
  CDPOS.VALUE_NEW AS "ReturnReason", -- Using rejection reason for this specific event
  VBAP.MATNR AS "MaterialNumber",
  VBAK.VTWEG AS "ReturnChannel",
  VBAP.NETWR AS "RequestedRefundAmount",
  NULL AS "ActualRefundAmount"
FROM BaseReturnOrders B
JOIN CDHDR ON CONCAT(B.ReturnOrderNumber, LPAD(B.ReturnOrderItemNumber, 6, '0')) = CDHDR.OBJECTID
JOIN CDPOS ON CDHDR.OBJECTCLAS = CDPOS.OBJECTCLAS AND CDHDR.OBJECTID = CDPOS.OBJECTID AND CDHDR.CHANGENR = CDPOS.CHANGENR
JOIN VBAK ON B.ReturnOrderNumber = VBAK.VBELN
JOIN VBAP ON B.ReturnOrderNumber = VBAP.VBELN AND B.ReturnOrderItemNumber = VBAP.POSNR
WHERE CDHDR.OBJECTCLAS = 'VERKBELEG'
  AND CDPOS.TABNAME = 'VBAP'
  AND CDPOS.FNAME = 'ABGRU'
  AND CDPOS.VALUE_NEW <> ''

UNION ALL

-- 12. Credit Memo Cleared
SELECT
  CONCAT(B.ReturnOrderNumber, '-', B.ReturnOrderItemNumber) AS "ReturnCaseId",
  'Credit Memo Cleared' AS "ActivityName",
  TO_TIMESTAMP(BSAD.AUGDT, 'YYYYMMDD') AS "EventTime",
  'SAP ECC' AS "SourceSystem",
  CURRENT_TIMESTAMP AS "LastDataUpdate",
  CLEAR_DOC.USNAM AS "ProcessingAgent",
  VBAK.AUGRU AS "ReturnReason",
  VBAP.MATNR AS "MaterialNumber",
  VBAK.VTWEG AS "ReturnChannel",
  VBAP.NETWR AS "RequestedRefundAmount",
  VBRP.NETWR AS "ActualRefundAmount"
FROM BaseReturnOrders B
JOIN VBAK ON B.ReturnOrderNumber = VBAK.VBELN
JOIN VBAP ON B.ReturnOrderNumber = VBAP.VBELN AND B.ReturnOrderItemNumber = VBAP.POSNR
JOIN VBFA ON B.ReturnOrderNumber = VBFA.VBELV AND B.ReturnOrderItemNumber = VBFA.POSNV AND VBFA.VBTYP_N = 'M'
JOIN VBRK ON VBFA.VBELN = VBRK.VBELN
JOIN VBRP ON VBRK.VBELN = VBRP.VBELN AND VBFA.POSNN = VBRP.POSNR
JOIN BSAD ON VBRK.VBELN = BSAD.VBELN
JOIN BKPF AS CLEAR_DOC ON BSAD.AUGBL = CLEAR_DOC.BELNR AND BSAD.BUKRS = CLEAR_DOC.BUKRS AND BSAD.GJAHR_AUGL = CLEAR_DOC.GJAHR;