データテンプレート: 受注から入金まで - 受注処理

REPORT Z_O2C_PM_EXTRACTOR.

*&---------------------------------------------------------------------*
*& Data Declarations
*&---------------------------------------------------------------------*
TABLES: vbak.

TYPES: BEGIN OF ty_event_log,
         salesorder         TYPE vbeln_va,
         activity           TYPE string,
         starttime          TYPE string,
         sourcesystem       TYPE logsys,
         lastdataupdate     TYPE string,
         user               TYPE ernam,
         customernumber     TYPE kunnr,
         salesorganization  TYPE vkorg,
         netamount          TYPE netwr,
         materialnumber     TYPE matnr,
         deliveryblock      TYPE lifsk,
         rejectionreason    TYPE abgru,
         salesordercycletime TYPE string, " Placeholder for calculation
       END OF ty_event_log.

DATA: gt_event_log TYPE TABLE OF ty_event_log.
DATA: gs_event_log TYPE ty_event_log.

DATA: gv_sysid       TYPE logsys.
DATA: gv_last_update TYPE string.

*&---------------------------------------------------------------------*
*& Selection Screen
*&---------------------------------------------------------------------*
SELECT-OPTIONS: s_erdat FOR vbak-erdat OBLIGATORY,
                s_vkorg FOR vbak-vkorg,
                s_auart FOR vbak-auart.

PARAMETERS: p_file TYPE rlgrap-filename OBLIGATORY DEFAULT 'C:\temp\o2c_event_log.csv'.

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

  CALL FUNCTION 'OWN_LOGICAL_SYSTEM_GET'
    IMPORTING
      own_logical_system = gv_sysid.

  CONCATENATE sy-datum sy-uzeit INTO gv_last_update.

  PERFORM get_base_data.
  PERFORM write_output_file.

*&---------------------------------------------------------------------*
*&      Form  get_base_data
*&---------------------------------------------------------------------*
FORM get_base_data.

  TYPES: BEGIN OF ty_order_item,
           vbeln TYPE vbeln_va,
           posnr TYPE posnr_va,
           erdat TYPE erdat,
           erzet TYPE erzet,
           ernam TYPE ernam,
           kunnr TYPE kunnr,
           vkorg TYPE vkorg,
           netwr TYPE netwr_ak,
           matnr TYPE matnr,
           lifsk TYPE lifsk,
           abgru TYPE abgru,
         END OF ty_order_item.

  DATA: lt_order_items TYPE TABLE OF ty_order_item.

  SELECT h~vbeln i~posnr h~erdat h~erzet h~ernam h~kunnr h~vkorg h~netwr i~matnr h~lifsk i~abgru
    INTO TABLE lt_order_items
    FROM vbak AS h
    INNER JOIN vbap AS i ON h~vbeln = i~vbeln
    WHERE h~erdat IN s_erdat
      AND h~vkorg IN s_vkorg
      AND h~auart IN s_auart.

  CHECK sy-subrc = 0.

  DATA(lt_vbeln_range) = VALUE rsdsselopt_t(
    FOR <fs_item> IN lt_order_items WHERE ( vbeln = <fs_item>-vbeln )
      ( sign = 'I' option = 'EQ' low = <fs_item>-vbeln ) ).
  SORT lt_vbeln_range BY low.
  DELETE ADJACENT DUPLICATES FROM lt_vbeln_range COMPARING low.

  PERFORM extract_order_created USING lt_order_items.
  PERFORM extract_changes USING lt_vbeln_range lt_order_items.
  PERFORM extract_doc_flow_events USING lt_vbeln_range lt_order_items.

ENDFORM.

*&---------------------------------------------------------------------*
*&      Form  extract_order_created
*&---------------------------------------------------------------------*
FORM extract_order_created USING it_order_items TYPE ANY TABLE.
  FIELD-SYMBOLS: <fs_item> TYPE any.

  DATA: lt_unique_orders TYPE HASHED TABLE OF vbeln_va WITH UNIQUE KEY table_line.
  lt_unique_orders = VALUE #( FOR <order> IN it_order_items ( CONV vbeln_va( <order>-vbeln ) ) ).

  LOOP AT it_order_items ASSIGNING <fs_item> WHERE table_line IN lt_unique_orders.
    CLEAR gs_event_log.
    gs_event_log-salesorder = <fs_item>-vbeln.
    gs_event_log-activity = 'Sales Order Created'.
    CONCATENATE <fs_item>-erdat <fs_item>-erzet INTO gs_event_log-starttime.
    gs_event_log-user = <fs_item>-ernam.
    gs_event_log-customernumber = <fs_item>-kunnr.
    gs_event_log-salesorganization = <fs_item>-vkorg.
    gs_event_log-netamount = <fs_item>-netwr.
    APPEND gs_event_log TO gt_event_log.
    DELETE lt_unique_orders WHERE table_line = <fs_item>-vbeln.
  ENDLOOP.
ENDFORM.

*&---------------------------------------------------------------------*
*&      Form  extract_changes
*&---------------------------------------------------------------------*
FORM extract_changes USING it_vbeln_range TYPE rsdsselopt_t it_order_items TYPE ANY TABLE.
  DATA: lt_cdhdr TYPE TABLE OF cdhdr,
        lt_cdpos TYPE TABLE OF cdpos.

  SELECT * INTO TABLE lt_cdhdr FROM cdhdr
    WHERE objectclas = 'VERKBELEG'
      AND objectid IN it_vbeln_range
      AND tcode = 'VA02'.

  IF sy-subrc = 0.
    SELECT * INTO TABLE lt_cdpos FROM cdpos
      FOR ALL ENTRIES IN lt_cdhdr
      WHERE objectclas = lt_cdhdr-objectclas
        AND objectid = lt_cdhdr-objectid
        AND changenr = lt_cdhdr-changenr.
  ENDIF.

  LOOP AT lt_cdhdr ASSIGNING FIELD-SYMBOL(<fs_cdhdr>).
    DATA(lv_order_info) = REF #( it_order_items[ vbeln = <fs_cdhdr>-objectid ] ).
    IF lv_order_info IS NOT BOUND. CONTINUE. ENDIF.

    CLEAR gs_event_log.
    gs_event_log-salesorder = <fs_cdhdr>-objectid.
    gs_event_log-user = <fs_cdhdr>-username.
    CONCATENATE <fs_cdhdr>-udate <fs_cdhdr>-utime INTO gs_event_log-starttime.
    gs_event_log-customernumber = lv_order_info->kunnr.
    gs_event_log-salesorganization = lv_order_info->vkorg.
    gs_event_log-netamount = lv_order_info->netwr.

    " Generic Change Event
    gs_event_log-activity = 'Sales Order Changed'.
    APPEND gs_event_log TO gt_event_log.

    LOOP AT lt_cdpos ASSIGNING FIELD-SYMBOL(<fs_cdpos>)
      WHERE objectclas = <fs_cdhdr>-objectclas
        AND objectid   = <fs_cdhdr>-objectid
        AND changenr   = <fs_cdhdr>-changenr.

      CASE <fs_cdpos>-fname.
        WHEN 'LIFSK'. " Delivery Block
          gs_event_log-activity = 'Delivery Block Set'.
          gs_event_log-deliveryblock = <fs_cdpos>-value_new.
          APPEND gs_event_log TO gt_event_log.
        WHEN 'CMGST'. " Credit Status
          IF <fs_cdpos>-value_new = 'B'. " B = Credit Check OK
            gs_event_log-activity = 'Credit Check Performed'.
            APPEND gs_event_log TO gt_event_log.
          ENDIF.
        WHEN 'ABGRU'. " Rejection Reason
          IF <fs_cdpos>-value_new IS NOT INITIAL.
            gs_event_log-activity = 'Order Cancelled'.
            gs_event_log-rejectionreason = <fs_cdpos>-value_new.
            APPEND gs_event_log TO gt_event_log.
          ENDIF.
      ENDCASE.
    ENDLOOP.
  ENDLOOP.
ENDFORM.

*&---------------------------------------------------------------------*
*&      Form  extract_doc_flow_events
*&---------------------------------------------------------------------*
FORM extract_doc_flow_events USING it_vbeln_range TYPE rsdsselopt_t it_order_items TYPE ANY TABLE.
  DATA: lt_vbfa TYPE TABLE OF vbfa,
        lt_vbrk TYPE TABLE OF vbrk,
        lt_likp TYPE TABLE OF likp,
        lt_mseg TYPE TABLE OF mseg,
        lt_bsad TYPE TABLE OF bsad,
        lt_vbup TYPE TABLE OF vbup.

  SELECT * INTO TABLE lt_vbfa FROM vbfa
    WHERE vbelv IN it_vbeln_range
      AND ( vbtyp_n = 'J'  " Delivery
         OR vbtyp_n = 'M'  " Invoice
         OR vbtyp_n = 'N'  " Invoice Cancellation
         OR vbtyp_n = 'R' ). " Goods Movement

  IF lt_vbfa IS INITIAL. RETURN. ENDIF.

  SELECT vbeln, erdat, erzet, ernam, fksto, belnr FROM vbrk INTO TABLE lt_vbrk
    FOR ALL ENTRIES IN lt_vbfa
    WHERE vbeln = lt_vbfa-vbeln
      AND ( lt_vbfa-vbtyp_n = 'M' OR lt_vbfa-vbtyp_n = 'N' ).

  SELECT vbeln, erdat, erzet, ernam, podat FROM likp INTO TABLE lt_likp
    FOR ALL ENTRIES IN lt_vbfa
    WHERE vbeln = lt_vbfa-vbeln AND lt_vbfa-vbtyp_n = 'J'.

  SELECT mblnr, mjahr, zeile, bwart, budat, cpuzt, usnam FROM mseg INTO TABLE lt_mseg
    FOR ALL ENTRIES IN lt_vbfa
    WHERE mblnr = lt_vbfa-vbeln AND mjahr = lt_vbfa-mjahr AND zeile = lt_vbfa-posnn AND lt_vbfa-vbtyp_n = 'R' AND bwart = '601'.

  SELECT augdt, belnr, gjahr, kunnr FROM bsad INTO TABLE lt_bsad
    FOR ALL ENTRIES IN lt_vbrk
    WHERE belnr = lt_vbrk-belnr AND gjahr = SUBSTRING( val = lt_vbrk-erdat len = 4 ).

  SELECT vbeln, posnr, gbsta FROM vbup INTO TABLE lt_vbup
    FOR ALL ENTRIES IN lt_vbfa
    WHERE vbeln = lt_vbfa-vbelv AND posnr = lt_vbfa-posnv.

  LOOP AT lt_vbfa ASSIGNING FIELD-SYMBOL(<fs_vbfa>).
    DATA(lv_order_info) = REF #( it_order_items[ vbeln = <fs_vbfa>-vbelv ] ).
    IF lv_order_info IS NOT BOUND. CONTINUE. ENDIF.

    CLEAR gs_event_log.
    gs_event_log-salesorder = <fs_vbfa>-vbelv.
    gs_event_log-customernumber = lv_order_info->kunnr.
    gs_event_log-salesorganization = lv_order_info->vkorg.
    gs_event_log-netamount = lv_order_info->netwr.
    gs_event_log-materialnumber = lv_order_info->matnr.

    CASE <fs_vbfa>-vbtyp_n.
      WHEN 'J'. " Delivery
        READ TABLE lt_likp ASSIGNING FIELD-SYMBOL(<fs_likp>) WITH KEY vbeln = <fs_vbfa>-vbeln.
        IF sy-subrc = 0.
          gs_event_log-activity = 'Delivery Created'.
          CONCATENATE <fs_likp>-erdat <fs_likp>-erzet INTO gs_event_log-starttime.
          gs_event_log-user = <fs_likp>-ernam.
          APPEND gs_event_log TO gt_event_log.
          " Picking Completed - simplified logic, check status
          gs_event_log-activity = 'Picking Completed'. APPEND gs_event_log TO gt_event_log.
          " POD Confirmed
          IF <fs_likp>-podat IS NOT INITIAL.
             gs_event_log-activity = 'Proof Of Delivery Confirmed'.
             gs_event_log-starttime = <fs_likp>-podat.
             APPEND gs_event_log TO gt_event_log.
          ENDIF.
        ENDIF.

      WHEN 'R'. " Goods Issue
        READ TABLE lt_mseg ASSIGNING FIELD-SYMBOL(<fs_mseg>) WITH KEY mblnr = <fs_vbfa>-vbeln mjahr = <fs_vbfa>-mjahr zeile = <fs_vbfa>-posnn.
        IF sy-subrc = 0.
          gs_event_log-activity = 'Goods Issued'.
          CONCATENATE <fs_mseg>-budat <fs_mseg>-cpuzt INTO gs_event_log-starttime.
          gs_event_log-user = <fs_mseg>-usnam.
          APPEND gs_event_log TO gt_event_log.
        ENDIF.

      WHEN 'M'. " Invoice
        READ TABLE lt_vbrk ASSIGNING FIELD-SYMBOL(<fs_vbrk>) WITH KEY vbeln = <fs_vbfa>-vbeln.
        IF sy-subrc = 0.
          gs_event_log-activity = 'Invoice Created'.
          CONCATENATE <fs_vbrk>-erdat <fs_vbrk>-erzet INTO gs_event_log-starttime.
          gs_event_log-user = <fs_vbrk>-ernam.
          APPEND gs_event_log TO gt_event_log.
          " Payment Received
          READ TABLE lt_bsad ASSIGNING FIELD-SYMBOL(<fs_bsad>) WITH KEY belnr = <fs_vbrk>-belnr.
          IF sy-subrc = 0 AND <fs_bsad>-augdt IS NOT INITIAL.
            gs_event_log-activity = 'Payment Received'.
            gs_event_log-starttime = <fs_bsad>-augdt.
            APPEND gs_event_log TO gt_event_log.
          ENDIF.
        ENDIF.

      WHEN 'N'. " Invoice Cancellation
        READ TABLE lt_vbrk ASSIGNING <fs_vbrk> WITH KEY vbeln = <fs_vbfa>-vbeln.
        IF sy-subrc = 0 AND <fs_vbrk>-fksto = 'X'.
          gs_event_log-activity = 'Invoice Cancelled'.
          CONCATENATE <fs_vbrk>-erdat <fs_vbrk>-erzet INTO gs_event_log-starttime.
          gs_event_log-user = <fs_vbrk>-ernam.
          APPEND gs_event_log TO gt_event_log.
        ENDIF.
    ENDCASE.
  ENDLOOP.

  " Infer other events from status
  LOOP AT lt_vbup ASSIGNING FIELD-SYMBOL(<fs_vbup>).
    IF <fs_vbup>-gbsta = 'C'.
       DATA(lv_order_info_stat) = REF #( it_order_items[ vbeln = <fs_vbup>-vbeln ] ).
       IF lv_order_info_stat IS NOT BOUND. CONTINUE. ENDIF.
       gs_event_log-salesorder = <fs_vbup>-vbeln.
       gs_event_log-activity = 'Order Item Closed'.
       " Timestamp for closed is harder, using current time as placeholder
       CONCATENATE sy-datum sy-uzeit INTO gs_event_log-starttime.
       gs_event_log-user = sy-uname.
       gs_event_log-customernumber = lv_order_info_stat->kunnr.
       APPEND gs_event_log TO gt_event_log.
    ENDIF.
  ENDLOOP.

  " Order Confirmed (Simplified - assumes if not blocked it's confirmed)
  LOOP AT it_order_items ASSIGNING FIELD-SYMBOL(<fs_item>).
     IF <fs_item>-lifsk IS INITIAL.
        gs_event_log-salesorder = <fs_item>-vbeln.
        gs_event_log-activity = 'Order Confirmed'.
        CONCATENATE <fs_item>-erdat <fs_item>-erzet INTO gs_event_log-starttime.
        gs_event_log-user = <fs_item>-ernam.
        gs_event_log-customernumber = <fs_item>-kunnr.
        APPEND gs_event_log TO gt_event_log.
     ENDIF.
  ENDLOOP.
ENDFORM.

*&---------------------------------------------------------------------*
*&      Form  write_output_file
*&---------------------------------------------------------------------*
FORM write_output_file.
  DATA: lt_final_output TYPE TABLE OF ty_event_log.

  " Add common fields
  LOOP AT gt_event_log ASSIGNING FIELD-SYMBOL(<fs_event>).
    <fs_event>-sourcesystem = gv_sysid.
    <fs_event>-lastdataupdate = gv_last_update.
  ENDLOOP.

  SORT gt_event_log BY salesorder starttime.
  DELETE ADJACENT DUPLICATES FROM gt_event_log COMPARING ALL FIELDS.
  lt_final_output = gt_event_log.

  DATA: lt_fieldnames TYPE TABLE OF string.
  APPEND 'SalesOrder' TO lt_fieldnames.
  APPEND 'Activity' TO lt_fieldnames.
  APPEND 'StartTime' TO lt_fieldnames.
  APPEND 'SourceSystem' TO lt_fieldnames.
  APPEND 'LastDataUpdate' TO lt_fieldnames.
  APPEND 'User' TO lt_fieldnames.
  APPEND 'CustomerNumber' TO lt_fieldnames.
  APPEND 'SalesOrganization' TO lt_fieldnames.
  APPEND 'NetAmount' TO lt_fieldnames.
  APPEND 'MaterialNumber' TO lt_fieldnames.
  APPEND 'DeliveryBlock' TO lt_fieldnames.
  APPEND 'RejectionReason' TO lt_fieldnames.
  APPEND 'SalesOrderCycleTime' TO lt_fieldnames.

  DATA(lv_header) = REDUCE string(
    INIT s = ''
    FOR field IN lt_fieldnames
    NEXT s = s && COND #( WHEN s = '' THEN field ELSE |,{ field }| ) ).

  DATA: lt_file_content TYPE TABLE OF string.
  APPEND lv_header TO lt_file_content.

  LOOP AT lt_final_output INTO DATA(ls_output).
    DATA(lv_line) = |"{ ls_output-salesorder }","{ ls_output-activity }","{ ls_output-starttime }","{ ls_output-sourcesystem }","{ ls_output-lastdataupdate }","{ ls_output-user }","{ ls_output-customernumber }","{ ls_output-salesorganization }",{ ls_output-netamount },"{ ls_output-materialnumber }","{ ls_output-deliveryblock }","{ ls_output-rejectionreason }","{ ls_output-salesordercycletime }"|.
    APPEND lv_line TO lt_file_content.
  ENDLOOP.

  cl_gui_frontend_services=>gui_download(
    EXPORTING
      filename = p_file
      filetype = 'ASC'
    CHANGING
      data_tab = lt_file_content ).

ENDFORM.

WITH SalesOrders AS (
  SELECT VBELN
  FROM VBAK
  WHERE ERDAT BETWEEN '{StartDate}' AND '{EndDate}' -- Filter by creation date
    AND VKORG IN ('{SalesOrgs}') -- Filter by Sales Organization(s)
    AND AUART IN ('{DocTypes}') -- Filter by Sales Document Type(s)
)
-- 1. Sales Order Created
SELECT
  vbak.VBELN AS "SalesOrder",
  'Sales Order Created' AS "Activity",
  [Your DB-specific timestamp function](vbak.ERDAT, vbak.ERZET) AS "StartTime",
  '{SourceSystemName}' AS "SourceSystem",
  CURRENT_TIMESTAMP AS "LastDataUpdate",
  vbak.ERNAM AS "User",
  vbak.KUNNR AS "CustomerNumber",
  vbak.VKORG AS "SalesOrganization",
  vbak.NETWR AS "NetAmount",
  NULL AS "MaterialNumber",
  vbak.LIFSK AS "DeliveryBlock",
  NULL AS "RejectionReason"
FROM VBAK vbak
JOIN SalesOrders so ON vbak.VBELN = so.VBELN

UNION ALL

-- 2. Sales Order Changed
SELECT
  cdhdr.OBJECTID AS "SalesOrder",
  'Sales Order Changed' AS "Activity",
  [Your DB-specific timestamp function](cdhdr.UDATE, cdhdr.UZEIT) AS "StartTime",
  '{SourceSystemName}' AS "SourceSystem",
  CURRENT_TIMESTAMP AS "LastDataUpdate",
  cdhdr.USERNAME AS "User",
  vbak.KUNNR AS "CustomerNumber",
  vbak.VKORG AS "SalesOrganization",
  vbak.NETWR AS "NetAmount",
  NULL AS "MaterialNumber",
  vbak.LIFSK AS "DeliveryBlock",
  NULL AS "RejectionReason"
FROM CDHDR cdhdr
JOIN SalesOrders so ON cdhdr.OBJECTID = so.VBELN
JOIN VBAK vbak ON so.VBELN = vbak.VBELN
WHERE cdhdr.OBJECTCLASS = 'VERKBELEG' AND cdhdr.TCODE IN ('VA02')

UNION ALL

-- 3. Credit Check Performed (Release)
SELECT
  cdhdr.OBJECTID AS "SalesOrder",
  'Credit Check Performed' AS "Activity",
  [Your DB-specific timestamp function](cdhdr.UDATE, cdhdr.UZEIT) AS "StartTime",
  '{SourceSystemName}' AS "SourceSystem",
  CURRENT_TIMESTAMP AS "LastDataUpdate",
  cdhdr.USERNAME AS "User",
  vbak.KUNNR AS "CustomerNumber",
  vbak.VKORG AS "SalesOrganization",
  vbak.NETWR AS "NetAmount",
  NULL AS "MaterialNumber",
  vbak.LIFSK AS "DeliveryBlock",
  NULL AS "RejectionReason"
FROM CDHDR cdhdr
JOIN CDPOS cdpos ON cdhdr.CHANGENR = cdpos.CHANGENR
JOIN SalesOrders so ON cdhdr.OBJECTID = so.VBELN
JOIN VBAK vbak ON so.VBELN = vbak.VBELN
WHERE cdhdr.OBJECTCLASS = 'VERKBELEG'
  AND cdpos.TABNAME = 'VBUK'
  AND cdpos.FNAME = 'CMGST'
  AND cdpos.VALUE_NEW = 'B' -- Credit status 'Released'

UNION ALL

-- 4. Order Confirmed (Overall status not blocked)
SELECT
  cdhdr.OBJECTID AS "SalesOrder",
  'Order Confirmed' AS "Activity",
  [Your DB-specific timestamp function](cdhdr.UDATE, cdhdr.UZEIT) AS "StartTime",
  '{SourceSystemName}' AS "SourceSystem",
  CURRENT_TIMESTAMP AS "LastDataUpdate",
  cdhdr.USERNAME AS "User",
  vbak.KUNNR AS "CustomerNumber",
  vbak.VKORG AS "SalesOrganization",
  vbak.NETWR AS "NetAmount",
  NULL AS "MaterialNumber",
  NULL AS "DeliveryBlock",
  NULL AS "RejectionReason"
FROM CDHDR cdhdr
JOIN CDPOS cdpos ON cdhdr.CHANGENR = cdpos.CHANGENR
JOIN SalesOrders so ON cdhdr.OBJECTID = so.VBELN
JOIN VBAK vbak ON so.VBELN = vbak.VBELN
WHERE cdhdr.OBJECTCLASS = 'VERKBELEG'
  AND cdpos.TABNAME = 'VBUK'
  AND cdpos.FNAME = 'GBSTK'
  AND cdpos.VALUE_OLD <> 'A' AND cdpos.VALUE_NEW = 'A' -- Status changes to 'Not yet processed'

UNION ALL

-- 5. Delivery Block Set
SELECT
  cdhdr.OBJECTID AS "SalesOrder",
  'Delivery Block Set' AS "Activity",
  [Your DB-specific timestamp function](cdhdr.UDATE, cdhdr.UZEIT) AS "StartTime",
  '{SourceSystemName}' AS "SourceSystem",
  CURRENT_TIMESTAMP AS "LastDataUpdate",
  cdhdr.USERNAME AS "User",
  vbak.KUNNR AS "CustomerNumber",
  vbak.VKORG AS "SalesOrganization",
  vbak.NETWR AS "NetAmount",
  NULL AS "MaterialNumber",
  cdpos.VALUE_NEW AS "DeliveryBlock",
  NULL AS "RejectionReason"
FROM CDHDR cdhdr
JOIN CDPOS cdpos ON cdhdr.CHANGENR = cdpos.CHANGENR
JOIN SalesOrders so ON cdhdr.OBJECTID = so.VBELN
JOIN VBAK vbak ON so.VBELN = vbak.VBELN
WHERE cdhdr.OBJECTCLASS = 'VERKBELEG'
  AND cdpos.TABNAME = 'VBAK'
  AND cdpos.FNAME = 'LIFSK'
  AND cdpos.VALUE_NEW IS NOT NULL AND cdpos.VALUE_NEW <> ''

UNION ALL

-- 6. Delivery Created
SELECT
  vbfa.VBELV AS "SalesOrder",
  'Delivery Created' AS "Activity",
  [Your DB-specific timestamp function](likp.ERDAT, likp.ERZET) AS "StartTime",
  '{SourceSystemName}' AS "SourceSystem",
  CURRENT_TIMESTAMP AS "LastDataUpdate",
  likp.ERNAM AS "User",
  vbak.KUNNR AS "CustomerNumber",
  vbak.VKORG AS "SalesOrganization",
  vbak.NETWR AS "NetAmount",
  NULL AS "MaterialNumber",
  vbak.LIFSK AS "DeliveryBlock",
  NULL AS "RejectionReason"
FROM VBFA vbfa
JOIN SalesOrders so ON vbfa.VBELV = so.VBELN
JOIN LIKP likp ON vbfa.VBELN = likp.VBELN
JOIN VBAK vbak ON so.VBELN = vbak.VBELN
WHERE vbfa.VBTYP_V = 'C' AND vbfa.VBTYP_N = 'J'

UNION ALL

-- 7. Picking Completed
SELECT
  vbfa.VBELV AS "SalesOrder",
  'Picking Completed' AS "Activity",
  [Your DB-specific timestamp function](cdhdr.UDATE, cdhdr.UZEIT) AS "StartTime",
  '{SourceSystemName}' AS "SourceSystem",
  CURRENT_TIMESTAMP AS "LastDataUpdate",
  cdhdr.USERNAME AS "User",
  vbak.KUNNR AS "CustomerNumber",
  vbak.VKORG AS "SalesOrganization",
  vbak.NETWR AS "NetAmount",
  NULL AS "MaterialNumber",
  NULL AS "DeliveryBlock",
  NULL AS "RejectionReason"
FROM VBFA vbfa
JOIN SalesOrders so ON vbfa.VBELV = so.VBELN
JOIN CDHDR cdhdr ON vbfa.VBELN = cdhdr.OBJECTID
JOIN CDPOS cdpos ON cdhdr.CHANGENR = cdpos.CHANGENR
JOIN VBAK vbak ON so.VBELN = vbak.VBELN
WHERE vbfa.VBTYP_V = 'C' AND vbfa.VBTYP_N = 'J'
  AND cdhdr.OBJECTCLASS = 'LIEFERUNG'
  AND cdpos.TABNAME = 'VBUK'
  AND cdpos.FNAME = 'PKSTK'
  AND cdpos.VALUE_NEW = 'C'

UNION ALL

-- 8. Goods Issued
SELECT
  vbfa_gi.VBELV AS "SalesOrder",
  'Goods Issued' AS "Activity",
  [Your DB-specific timestamp function](mkpf.BUDAT, mkpf.CPUTM) AS "StartTime",
  '{SourceSystemName}' AS "SourceSystem",
  CURRENT_TIMESTAMP AS "LastDataUpdate",
  mkpf.USNAM AS "User",
  vbak.KUNNR AS "CustomerNumber",
  vbak.VKORG AS "SalesOrganization",
  vbak.NETWR AS "NetAmount",
  NULL AS "MaterialNumber",
  NULL AS "DeliveryBlock",
  NULL AS "RejectionReason"
FROM VBFA vbfa_gi
JOIN SalesOrders so ON vbfa_gi.VBELV = so.VBELN
JOIN MKPF mkpf ON vbfa_gi.VBELN = mkpf.XBLNR -- XBLNR is Reference Document Number
JOIN VBAK vbak ON so.VBELN = vbak.VBELN
WHERE vbfa_gi.VBTYP_V = 'J' AND vbfa_gi.VBTYP_N = 'R'

UNION ALL

-- 9. Proof Of Delivery Confirmed
SELECT 
  vbfa.VBELV AS "SalesOrder",
  'Proof Of Delivery Confirmed' AS "Activity",
  [Your DB-specific timestamp function](likp.PODAT, '000000') AS "StartTime", -- PODAT is only a date
  '{SourceSystemName}' AS "SourceSystem",
  CURRENT_TIMESTAMP AS "LastDataUpdate",
  likp.AENAM AS "User",
  vbak.KUNNR AS "CustomerNumber",
  vbak.VKORG AS "SalesOrganization",
  vbak.NETWR AS "NetAmount",
  NULL AS "MaterialNumber",
  NULL AS "DeliveryBlock",
  NULL AS "RejectionReason"
FROM VBFA vbfa
JOIN SalesOrders so ON vbfa.VBELV = so.VBELN
JOIN LIKP likp ON vbfa.VBELN = likp.VBELN
JOIN VBAK vbak ON so.VBELN = vbak.VBELN
WHERE vbfa.VBTYP_V = 'C' AND vbfa.VBTYP_N = 'J' AND likp.PODAT IS NOT NULL AND likp.PODAT <> '00000000'

UNION ALL

-- 10. Invoice Created
SELECT
  vbfa.VBELV AS "SalesOrder",
  'Invoice Created' AS "Activity",
  [Your DB-specific timestamp function](vbrk.ERDAT, vbrk.ERZET) AS "StartTime",
  '{SourceSystemName}' AS "SourceSystem",
  CURRENT_TIMESTAMP AS "LastDataUpdate",
  vbrk.ERNAM AS "User",
  vbak.KUNNR AS "CustomerNumber",
  vbak.VKORG AS "SalesOrganization",
  vbak.NETWR AS "NetAmount",
  NULL AS "MaterialNumber",
  NULL AS "DeliveryBlock",
  NULL AS "RejectionReason"
FROM VBFA vbfa
JOIN SalesOrders so ON vbfa.VBELV = so.VBELN
JOIN VBRK vbrk ON vbfa.VBELN = vbrk.VBELN
JOIN VBAK vbak ON so.VBELN = vbak.VBELN
WHERE vbfa.VBTYP_V = 'C' AND vbfa.VBTYP_N = 'M'

UNION ALL

-- 11. Invoice Cancelled
SELECT
  vbfa.VBELV AS "SalesOrder",
  'Invoice Cancelled' AS "Activity",
  [Your DB-specific timestamp function](vbrk.ERDAT, vbrk.ERZET) AS "StartTime",
  '{SourceSystemName}' AS "SourceSystem",
  CURRENT_TIMESTAMP AS "LastDataUpdate",
  vbrk.ERNAM AS "User",
  vbak.KUNNR AS "CustomerNumber",
  vbak.VKORG AS "SalesOrganization",
  vbak.NETWR AS "NetAmount",
  NULL AS "MaterialNumber",
  NULL AS "RejectionReason"
FROM VBFA vbfa
JOIN SalesOrders so ON vbfa.VBELV = so.VBELN
JOIN VBRK vbrk ON vbfa.VBELN = vbrk.VBELN
JOIN VBAK vbak ON so.VBELN = vbak.VBELN
WHERE vbfa.VBTYP_V = 'M' AND vbfa.VBTYP_N = 'N'

UNION ALL

-- 12. Payment Received
SELECT
  vbfa.VBELV AS "SalesOrder",
  'Payment Received' AS "Activity",
  [Your DB-specific timestamp function](bsad.AUGDT, '000000') AS "StartTime",
  '{SourceSystemName}' AS "SourceSystem",
  CURRENT_TIMESTAMP AS "LastDataUpdate",
  NULL AS "User", -- Clearing user not readily available here
  vbak.KUNNR AS "CustomerNumber",
  vbak.VKORG AS "SalesOrganization",
  vbak.NETWR AS "NetAmount",
  NULL AS "MaterialNumber",
  NULL AS "DeliveryBlock",
  NULL AS "RejectionReason"
FROM VBFA vbfa
JOIN SalesOrders so ON vbfa.VBELV = so.VBELN
JOIN VBRK vbrk ON vbfa.VBELN = vbrk.VBELN
JOIN BSAD bsad ON vbrk.VBELN = bsad.VBLNR
JOIN VBAK vbak ON so.VBELN = vbak.VBELN
WHERE vbfa.VBTYP_V = 'C' AND vbfa.VBTYP_N = 'M' 
  AND bsad.AUGDT IS NOT NULL AND bsad.AUGDT <> '00000000'

UNION ALL

-- 13. Order Item Closed
SELECT DISTINCT
  cdhdr.OBJECTID AS "SalesOrder",
  'Order Item Closed' AS "Activity",
  [Your DB-specific timestamp function](cdhdr.UDATE, cdhdr.UZEIT) AS "StartTime",
  '{SourceSystemName}' AS "SourceSystem",
  CURRENT_TIMESTAMP AS "LastDataUpdate",
  cdhdr.USERNAME AS "User",
  vbak.KUNNR AS "CustomerNumber",
  vbak.VKORG AS "SalesOrganization",
  vbak.NETWR AS "NetAmount",
  vbap.MATNR AS "MaterialNumber",
  NULL AS "DeliveryBlock",
  vbap.ABGRU AS "RejectionReason"
FROM CDHDR cdhdr
JOIN CDPOS cdpos ON cdhdr.CHANGENR = cdpos.CHANGENR
JOIN VBAP vbap ON cdhdr.OBJECTID = vbap.VBELN AND SUBSTRING(cdpos.TABKEY, 4, 6) = vbap.POSNR
JOIN SalesOrders so ON cdhdr.OBJECTID = so.VBELN
JOIN VBAK vbak ON so.VBELN = vbak.VBELN
WHERE cdhdr.OBJECTCLASS = 'VERKBELEG'
  AND cdpos.TABNAME = 'VBUP'
  AND cdpos.FNAME = 'GBSTA'
  AND cdpos.VALUE_NEW = 'C' -- Item is completely processed

UNION ALL

-- 14. Order Cancelled
SELECT DISTINCT
  cdhdr.OBJECTID AS "SalesOrder",
  'Order Cancelled' AS "Activity",
  [Your DB-specific timestamp function](cdhdr.UDATE, cdhdr.UZEIT) AS "StartTime",
  '{SourceSystemName}' AS "SourceSystem",
  CURRENT_TIMESTAMP AS "LastDataUpdate",
  cdhdr.USERNAME AS "User",
  vbak.KUNNR AS "CustomerNumber",
  vbak.VKORG AS "SalesOrganization",
  vbak.NETWR AS "NetAmount",
  vbap.MATNR AS "MaterialNumber",
  NULL AS "DeliveryBlock",
  cdpos.VALUE_NEW AS "RejectionReason"
FROM CDHDR cdhdr
JOIN CDPOS cdpos ON cdhdr.CHANGENR = cdpos.CHANGENR
JOIN VBAP vbap ON cdhdr.OBJECTID = vbap.VBELN AND SUBSTRING(cdpos.TABKEY, 4, 6) = vbap.POSNR
JOIN SalesOrders so ON cdhdr.OBJECTID = so.VBELN
JOIN VBAK vbak ON so.VBELN = vbak.VBELN
WHERE cdhdr.OBJECTCLASS = 'VERKBELEG'
  AND cdpos.TABNAME = 'VBAP'
  AND cdpos.FNAME = 'ABGRU'
  AND cdpos.VALUE_NEW IS NOT NULL AND cdpos.VALUE_NEW <> '';