Šablona dat pro proces od nákupu k platbě, objednávky

REPORT Z_PM_PO_EXTRACT.

TABLES: ekko, ekpo, eban.

*&---------------------------------------------------------------------*
*& Data Structures for Event Log
*&---------------------------------------------------------------------*
TYPES: BEGIN OF ty_event_log,
         purchaseorder  TYPE ebeln,
         activity       TYPE string,
         eventtime      TYPE timestamp,
         username       TYPE ernam,
         vendornumber   TYPE lifnr,
         orderamount    TYPE netwr_ak,
         materialgroup  TYPE matkl,
         companycode    TYPE bukrs,
         documenttype   TYPE bsart,
       END OF ty_event_log.

DATA: gt_event_log TYPE TABLE OF ty_event_log.

*&---------------------------------------------------------------------*
*& Selection Screen
*&---------------------------------------------------------------------*
SELECT-OPTIONS: s_aedat FOR ekko-aedat OBLIGATORY,  " PO Creation Date
                s_bukrs FOR ekko-bukrs,  " Company Code
                s_bsart FOR ekko-bsart,  " PO Document Type
                s_ebeln FOR ekko-ebeln.  " PO Number

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

  PERFORM get_po_headers.

  IF gt_event_log IS NOT INITIAL.
    PERFORM get_pr_created.
    PERFORM get_pr_approved.
    PERFORM get_po_created.
    PERFORM get_po_release_events. " Approved, Rejected, Approval Requested
    PERFORM get_po_sent_to_vendor.
    PERFORM get_po_changed.
    PERFORM get_goods_receipt_posted.
    PERFORM get_services_confirmed.
    PERFORM get_quality_inspection.
    PERFORM get_goods_returned.
    PERFORM get_po_completed.
    PERFORM get_po_deleted.
    PERFORM download_to_csv.
  ELSE.
    MESSAGE 'No Purchase Orders found for the given criteria.' TYPE 'I'.
  ENDIF.

*&---------------------------------------------------------------------*
*&      Form  GET_PO_HEADERS (Base data)
*&---------------------------------------------------------------------*
FORM get_po_headers.
  SELECT h~ebeln, h~lifnr, h~bukrs, h~bsart, p~netwr, p~matkl
    FROM ekko AS h
    INNER JOIN ekpo AS p ON h~ebeln = p~ebeln
    INTO TABLE @DATA(lt_po_base)
    WHERE h~aedat IN @s_aedat
      AND h~bukrs IN @s_bukrs
      AND h~bsart IN @s_bsart
      AND h~ebeln IN @s_ebeln.

  SORT lt_po_base BY ebeln.
ENDFORM.

*&---------------------------------------------------------------------*
*&      Form  GET_PR_CREATED
*&---------------------------------------------------------------------*
FORM get_pr_created.
  DATA: lt_pr_events TYPE TABLE OF ty_event_log.

  SELECT p~ebeln AS purchaseorder,
         'Purchase Requisition Created' AS activity,
         b~erdat AS event_date,
         '000000' AS event_time,
         b~ernam AS username,
         h~lifnr AS vendornumber,
         p~netwr AS orderamount,
         p~matkl AS materialgroup,
         h~bukrs AS companycode,
         h~bsart AS documenttype
  FROM ekpo AS p
  JOIN eban AS b ON p~banfn = b~banfn AND p~bnfpo = b~bnfpo
  JOIN ekko AS h ON p~ebeln = h~ebeln
  WHERE p~ebeln IN @s_ebeln
    AND p~banfn IS NOT NULL AND p~banfn <> ''
    AND h~aedat IN @s_aedat
    AND h~bukrs IN @s_bukrs
    AND h~bsart IN @s_bsart
  INTO TABLE @DATA(lt_pr_created).

  LOOP AT lt_pr_created ASSIGNING FIELD-SYMBOL(<fs_pr>).
    DATA(ls_event) = CORRESPONDING ty_event_log(<fs_pr>).
    CONCATENATE <fs_pr>-event_date <fs_pr>-event_time INTO DATA(lv_ts).
    CONVERT DATE <fs_pr>-event_date TIME '000000' INTO TIME STAMP ls_event-eventtime TIME ZONE sy-zonlo.
    APPEND ls_event TO gt_event_log.
  ENDLOOP.
ENDFORM.

*&---------------------------------------------------------------------*
*&      Form  GET_PR_APPROVED
*&---------------------------------------------------------------------*
FORM get_pr_approved.
  DATA: lt_pr_list TYPE TABLE OF eban-banfn.
  SELECT DISTINCT p~banfn FROM ekpo AS p
      JOIN ekko AS h ON p~ebeln = h~ebeln
      WHERE h~aedat IN @s_aedat
      AND h~bukrs IN @s_bukrs
      AND p~banfn IS NOT NULL AND p~banfn <> ''
      INTO TABLE @lt_pr_list.

  IF lt_pr_list IS INITIAL. RETURN. ENDIF.

  SELECT h~objectid, h~username, h~udate, h~utime, p~fname, p~value_new
    FROM cdhdr AS h
    JOIN cdpos AS p ON h~objectid = p~objectid AND h~changenr = p~changenr
    FOR ALL ENTRIES IN @lt_pr_list
    WHERE h~objectclas = 'BANF'
      AND h~objectid   = @lt_pr_list-table_line
      AND p~tabname    = 'EBAN'
      AND p~fname      = 'FRGZU'
    INTO TABLE @DATA(lt_cd_pr).

  LOOP AT lt_cd_pr ASSIGNING FIELD-SYMBOL(<fs_cd>) WHERE <fs_cd>-value_new = 'X'.
    SELECT SINGLE p~ebeln, p~netwr, p~matkl, h~lifnr, h~bukrs, h~bsart
      FROM ekpo AS p
      JOIN ekko AS h ON p~ebeln = h~ebeln
      WHERE p~banfn = @<fs_cd>-objectid(10)
      INTO @DATA(ls_po_info).
    IF sy-subrc = 0.
      DATA(ls_event) = VALUE ty_event_log(
          purchaseorder = ls_po_info-ebeln
          activity      = 'Purchase Requisition Approved'
          username      = <fs_cd>-username
          vendornumber  = ls_po_info-lifnr
          orderamount   = ls_po_info-netwr
          materialgroup = ls_po_info-matkl
          companycode   = ls_po_info-bukrs
          documenttype  = ls_po_info-bsart
      ).
      CONVERT DATE <fs_cd>-udate TIME <fs_cd>-utime INTO TIME STAMP ls_event-eventtime TIME ZONE sy-zonlo.
      APPEND ls_event TO gt_event_log.
    ENDIF.
  ENDLOOP.
ENDFORM.

*&---------------------------------------------------------------------*
*&      Form  GET_PO_CREATED
*&---------------------------------------------------------------------*
FORM get_po_created.
  LOOP AT lt_po_base ASSIGNING FIELD-SYMBOL(<fs_po>).
    SELECT SINGLE aedat, ernam FROM ekko INTO @DATA(ls_ekko)
      WHERE ebeln = @<fs_po>-ebeln.
    IF sy-subrc = 0.
      DATA(ls_event) = VALUE ty_event_log(
        purchaseorder = <fs_po>-ebeln
        activity      = 'Purchase Order Created'
        username      = ls_ekko-ernam
        vendornumber  = <fs_po>-lifnr
        orderamount   = <fs_po>-netwr
        materialgroup = <fs_po>-matkl
        companycode   = <fs_po>-bukrs
        documenttype  = <fs_po>-bsart
      ).
      CONVERT DATE ls_ekko-aedat TIME '000000' INTO TIME STAMP ls_event-eventtime TIME ZONE sy-zonlo.
      APPEND ls_event TO gt_event_log.
    ENDIF.
  ENDLOOP.
ENDFORM.

*&---------------------------------------------------------------------*
*&      Form  GET_PO_RELEASE_EVENTS
*&---------------------------------------------------------------------*
FORM get_po_release_events.
  DATA: lt_ebeln TYPE RANGE OF ebeln, ls_ebeln LIKE LINE OF lt_ebeln.
  LOOP AT lt_po_base INTO DATA(ls_po_base). 
    ls_ebeln-sign = 'I'. ls_ebeln-option = 'EQ'. ls_ebeln-low = ls_po_base-ebeln.
    APPEND ls_ebeln TO lt_ebeln.
  ENDLOOP.
  IF lt_ebeln IS INITIAL. RETURN. ENDIF.

  SELECT h~objectid, h~username, h~udate, h~utime, p~value_new
    FROM cdhdr AS h
    JOIN cdpos AS p ON h~objectid = p~objectid AND h~changenr = p~changenr
    WHERE h~objectclas = 'EINKBELEG'
      AND h~objectid IN lt_ebeln
      AND p~tabname    = 'EKKO'
      AND p~fname      = 'FRGKE'
    INTO TABLE @DATA(lt_cd_po).

  LOOP AT lt_cd_po ASSIGNING FIELD-SYMBOL(<fs_cd>).
    READ TABLE lt_po_base ASSIGNING FIELD-SYMBOL(<fs_po>) WITH KEY ebeln = <fs_cd>-objectid.
    IF sy-subrc <> 0. CONTINUE. ENDIF.
    
    DATA(ls_event) = VALUE ty_event_log(
        purchaseorder = <fs_po>-ebeln
        username      = <fs_cd>-username
        vendornumber  = <fs_po>-lifnr
        orderamount   = <fs_po>-netwr
        materialgroup = <fs_po>-matkl
        companycode   = <fs_po>-bukrs
        documenttype  = <fs_po>-bsart
    ).
    CONVERT DATE <fs_cd>-udate TIME <fs_cd>-utime INTO TIME STAMP ls_event-eventtime TIME ZONE sy-zonlo.

    CASE <fs_cd>-value_new.
      WHEN '2' OR 'R'. " Final Release
        ls_event-activity = 'Purchase Order Approved'.
      WHEN '1'. " Blocked
        ls_event-activity = 'Purchase Order Rejected'.
      WHEN OTHERS. " Any other change implies a pending state
        ls_event-activity = 'Purchase Order Approval Requested'.
    ENDCASE.
    APPEND ls_event TO gt_event_log.
  ENDLOOP.
ENDFORM.

*&---------------------------------------------------------------------*
*&      Form  GET_PO_SENT_TO_VENDOR
*&---------------------------------------------------------------------*
FORM get_po_sent_to_vendor.
  DATA: lt_ebeln TYPE RANGE OF ebeln, ls_ebeln LIKE LINE OF lt_ebeln.
  LOOP AT lt_po_base INTO DATA(ls_po_base). 
    ls_ebeln-sign = 'I'. ls_ebeln-option = 'EQ'. ls_ebeln-low = ls_po_base-ebeln.
    APPEND ls_ebeln TO lt_ebeln.
  ENDLOOP.
  IF lt_ebeln IS INITIAL. RETURN. ENDIF.

  SELECT objky, erdat, eruhr, ernam
    FROM nast
    WHERE kapol = 'EF' AND objky IN lt_ebeln AND vstat = '1'
    INTO TABLE @DATA(lt_nast).

  LOOP AT lt_nast ASSIGNING FIELD-SYMBOL(<fs_nast>).
    READ TABLE lt_po_base ASSIGNING FIELD-SYMBOL(<fs_po>) WITH KEY ebeln = <fs_nast>-objky.
    IF sy-subrc <> 0. CONTINUE. ENDIF.
    DATA(ls_event) = VALUE ty_event_log(
        purchaseorder = <fs_po>-ebeln
        activity      = 'Purchase Order Sent to Vendor'
        username      = <fs_nast>-ernam
        vendornumber  = <fs_po>-lifnr
        orderamount   = <fs_po>-netwr
        materialgroup = <fs_po>-matkl
        companycode   = <fs_po>-bukrs
        documenttype  = <fs_po>-bsart
    ).
    CONVERT DATE <fs_nast>-erdat TIME <fs_nast>-eruhr INTO TIME STAMP ls_event-eventtime TIME ZONE sy-zonlo.
    APPEND ls_event TO gt_event_log.
  ENDLOOP.
ENDFORM.

*&---------------------------------------------------------------------*
*&      Form  GET_PO_CHANGED
*&---------------------------------------------------------------------*
FORM get_po_changed.
  DATA: lt_ebeln TYPE RANGE OF ebeln, ls_ebeln LIKE LINE OF lt_ebeln.
  LOOP AT lt_po_base INTO DATA(ls_po_base). 
    ls_ebeln-sign = 'I'. ls_ebeln-option = 'EQ'. ls_ebeln-low = ls_po_base-ebeln.
    APPEND ls_ebeln TO lt_ebeln.
  ENDLOOP.
  IF lt_ebeln IS INITIAL. RETURN. ENDIF.

  SELECT DISTINCT objectid, username, udate, utime
    FROM cdhdr
    WHERE objectclas = 'EINKBELEG' AND objectid IN lt_ebeln AND tcode <> 'ME21N' AND tcode <> 'ME22'
    INTO TABLE @DATA(lt_cdhdr_chg).

  LOOP AT lt_cdhdr_chg ASSIGNING FIELD-SYMBOL(<fs_cd>).
    READ TABLE lt_po_base ASSIGNING FIELD-SYMBOL(<fs_po>) WITH KEY ebeln = <fs_cd>-objectid.
    IF sy-subrc <> 0. CONTINUE. ENDIF.
    DATA(ls_event) = VALUE ty_event_log(
        purchaseorder = <fs_po>-ebeln
        activity      = 'Purchase Order Changed'
        username      = <fs_cd>-username
        vendornumber  = <fs_po>-lifnr
        orderamount   = <fs_po>-netwr
        materialgroup = <fs_po>-matkl
        companycode   = <fs_po>-bukrs
        documenttype  = <fs_po>-bsart
    ).
    CONVERT DATE <fs_cd>-udate TIME <fs_cd>-utime INTO TIME STAMP ls_event-eventtime TIME ZONE sy-zonlo.
    APPEND ls_event TO gt_event_log.
  ENDLOOP.
ENDFORM.

*&---------------------------------------------------------------------*
*&      Form  GET_GOODS_RECEIPT_POSTED
*&---------------------------------------------------------------------*
FORM get_goods_receipt_posted.
  DATA: lt_ebeln TYPE RANGE OF ebeln, ls_ebeln LIKE LINE OF lt_ebeln.
  LOOP AT lt_po_base INTO DATA(ls_po_base). 
    ls_ebeln-sign = 'I'. ls_ebeln-option = 'EQ'. ls_ebeln-low = ls_po_base-ebeln.
    APPEND ls_ebeln TO lt_ebeln.
  ENDLOOP.
  IF lt_ebeln IS INITIAL. RETURN. ENDIF.

  SELECT k~ebeln, m~cpudt, m~cputm, m~usnam, k~bewtp
    FROM ekbe AS k JOIN mkpf AS m ON k~belnr = m~mblnr AND k~gjahr = m~mjahr
    WHERE k~ebeln IN lt_ebeln AND k~bewtp = 'E' AND k~shkzg = 'S'
    INTO TABLE @DATA(lt_gr).

  LOOP AT lt_gr ASSIGNING FIELD-SYMBOL(<fs_gr>).
    READ TABLE lt_po_base ASSIGNING FIELD-SYMBOL(<fs_po>) WITH KEY ebeln = <fs_gr>-ebeln.
    IF sy-subrc <> 0. CONTINUE. ENDIF.
    DATA(ls_event) = VALUE ty_event_log(
        purchaseorder = <fs_po>-ebeln
        activity      = 'Goods Receipt Posted'
        username      = <fs_gr>-usnam
        vendornumber  = <fs_po>-lifnr
        orderamount   = <fs_po>-netwr
        materialgroup = <fs_po>-matkl
        companycode   = <fs_po>-bukrs
        documenttype  = <fs_po>-bsart
    ).
    CONVERT DATE <fs_gr>-cpudt TIME <fs_gr>-cputm INTO TIME STAMP ls_event-eventtime TIME ZONE sy-zonlo.
    APPEND ls_event TO gt_event_log.
  ENDLOOP.
ENDFORM.

*&---------------------------------------------------------------------*
*&      Form  GET_SERVICES_CONFIRMED
*&---------------------------------------------------------------------*
FORM get_services_confirmed.
  DATA: lt_ebeln TYPE RANGE OF ebeln, ls_ebeln LIKE LINE OF lt_ebeln.
  LOOP AT lt_po_base INTO DATA(ls_po_base). 
    ls_ebeln-sign = 'I'. ls_ebeln-option = 'EQ'. ls_ebeln-low = ls_po_base-ebeln.
    APPEND ls_ebeln TO lt_ebeln.
  ENDLOOP.
  IF lt_ebeln IS INITIAL. RETURN. ENDIF.

  SELECT l~ebeln, h~erdat, h~eruhr, h~ernam
    FROM essr AS h JOIN esll AS l ON h~lblni = l~lblni
    WHERE l~ebeln IN lt_ebeln
    INTO TABLE @DATA(lt_ses).

  LOOP AT lt_ses ASSIGNING FIELD-SYMBOL(<fs_ses>).
    READ TABLE lt_po_base ASSIGNING FIELD-SYMBOL(<fs_po>) WITH KEY ebeln = <fs_ses>-ebeln.
    IF sy-subrc <> 0. CONTINUE. ENDIF.
    DATA(ls_event) = VALUE ty_event_log(
        purchaseorder = <fs_po>-ebeln
        activity      = 'Services Confirmation Entered'
        username      = <fs_ses>-ernam
        vendornumber  = <fs_po>-lifnr
        orderamount   = <fs_po>-netwr
        materialgroup = <fs_po>-matkl
        companycode   = <fs_po>-bukrs
        documenttype  = <fs_po>-bsart
    ).
    CONVERT DATE <fs_ses>-erdat TIME <fs_ses>-eruhr INTO TIME STAMP ls_event-eventtime TIME ZONE sy-zonlo.
    APPEND ls_event TO gt_event_log.
  ENDLOOP.
ENDFORM.

*&---------------------------------------------------------------------*
*&      Form  GET_QUALITY_INSPECTION
*&---------------------------------------------------------------------*
FORM get_quality_inspection.
  DATA: lt_ebeln TYPE RANGE OF ebeln, ls_ebeln LIKE LINE OF lt_ebeln.
  LOOP AT lt_po_base INTO DATA(ls_po_base). 
    ls_ebeln-sign = 'I'. ls_ebeln-option = 'EQ'. ls_ebeln-low = ls_po_base-ebeln.
    APPEND ls_ebeln TO lt_ebeln.
  ENDLOOP.
  IF lt_ebeln IS INITIAL. RETURN. ENDIF.

  SELECT q~ebeln, v~vdatum, v~vzeit, v~vname
    FROM qals AS q JOIN qave AS v ON q~prueflos = v~prueflos
    WHERE q~ebeln IN lt_ebeln
    INTO TABLE @DATA(lt_qm).

  LOOP AT lt_qm ASSIGNING FIELD-SYMBOL(<fs_qm>).
    READ TABLE lt_po_base ASSIGNING FIELD-SYMBOL(<fs_po>) WITH KEY ebeln = <fs_qm>-ebeln.
    IF sy-subrc <> 0. CONTINUE. ENDIF.
    DATA(ls_event) = VALUE ty_event_log(
        purchaseorder = <fs_po>-ebeln
        activity      = 'Quality Inspection Performed'
        username      = <fs_qm>-vname
        vendornumber  = <fs_po>-lifnr
        orderamount   = <fs_po>-netwr
        materialgroup = <fs_po>-matkl
        companycode   = <fs_po>-bukrs
        documenttype  = <fs_po>-bsart
    ).
    CONVERT DATE <fs_qm>-vdatum TIME <fs_qm>-vzeit INTO TIME STAMP ls_event-eventtime TIME ZONE sy-zonlo.
    APPEND ls_event TO gt_event_log.
  ENDLOOP.
ENDFORM.

*&---------------------------------------------------------------------*
*&      Form  GET_GOODS_RETURNED
*&---------------------------------------------------------------------*
FORM get_goods_returned.
  DATA: lt_ebeln TYPE RANGE OF ebeln, ls_ebeln LIKE LINE OF lt_ebeln.
  LOOP AT lt_po_base INTO DATA(ls_po_base). 
    ls_ebeln-sign = 'I'. ls_ebeln-option = 'EQ'. ls_ebeln-low = ls_po_base-ebeln.
    APPEND ls_ebeln TO lt_ebeln.
  ENDLOOP.
  IF lt_ebeln IS INITIAL. RETURN. ENDIF.

  SELECT k~ebeln, m~cpudt, m~cputm, m~usnam
    FROM ekbe AS k JOIN mkpf AS m ON k~belnr = m~mblnr AND k~gjahr = m~mjahr
    WHERE k~ebeln IN lt_ebeln AND k~bwart = '122'
    INTO TABLE @DATA(lt_ret).

  LOOP AT lt_ret ASSIGNING FIELD-SYMBOL(<fs_ret>).
    READ TABLE lt_po_base ASSIGNING FIELD-SYMBOL(<fs_po>) WITH KEY ebeln = <fs_ret>-ebeln.
    IF sy-subrc <> 0. CONTINUE. ENDIF.
    DATA(ls_event) = VALUE ty_event_log(
        purchaseorder = <fs_po>-ebeln
        activity      = 'Goods Returned'
        username      = <fs_ret>-usnam
        vendornumber  = <fs_po>-lifnr
        orderamount   = <fs_po>-netwr
        materialgroup = <fs_po>-matkl
        companycode   = <fs_po>-bukrs
        documenttype  = <fs_po>-bsart
    ).
    CONVERT DATE <fs_ret>-cpudt TIME <fs_ret>-cputm INTO TIME STAMP ls_event-eventtime TIME ZONE sy-zonlo.
    APPEND ls_event TO gt_event_log.
  ENDLOOP.
ENDFORM.

*&---------------------------------------------------------------------*
*&      Form  GET_PO_COMPLETED
*&---------------------------------------------------------------------*
FORM get_po_completed.
  DATA: lt_ebeln TYPE RANGE OF ebeln, ls_ebeln LIKE LINE OF lt_ebeln.
  LOOP AT lt_po_base INTO DATA(ls_po_base). 
    ls_ebeln-sign = 'I'. ls_ebeln-option = 'EQ'. ls_ebeln-low = ls_po_base-ebeln.
    APPEND ls_ebeln TO lt_ebeln.
  ENDLOOP.
  IF lt_ebeln IS INITIAL. RETURN. ENDIF.

  SELECT h~objectid, h~username, h~udate, h~utime
    FROM cdhdr AS h JOIN cdpos AS p ON h~changenr = p~changenr AND h~objectid = p~objectid
    WHERE h~objectclas = 'EINKBELEG' AND h~objectid IN lt_ebeln AND p~tabname = 'EKPO' AND p~fname = 'ELIKZ' AND p~value_new = 'X'
    INTO TABLE @DATA(lt_cd_comp).

  LOOP AT lt_cd_comp ASSIGNING FIELD-SYMBOL(<fs_cd>).
    READ TABLE lt_po_base ASSIGNING FIELD-SYMBOL(<fs_po>) WITH KEY ebeln = <fs_cd>-objectid.
    IF sy-subrc <> 0. CONTINUE. ENDIF.
    DATA(ls_event) = VALUE ty_event_log(
        purchaseorder = <fs_po>-ebeln
        activity      = 'Purchase Order Completed'
        username      = <fs_cd>-username
        vendornumber  = <fs_po>-lifnr
        orderamount   = <fs_po>-netwr
        materialgroup = <fs_po>-matkl
        companycode   = <fs_po>-bukrs
        documenttype  = <fs_po>-bsart
    ).
    CONVERT DATE <fs_cd>-udate TIME <fs_cd>-utime INTO TIME STAMP ls_event-eventtime TIME ZONE sy-zonlo.
    APPEND ls_event TO gt_event_log.
  ENDLOOP.
ENDFORM.

*&---------------------------------------------------------------------*
*&      Form  GET_PO_DELETED
*&---------------------------------------------------------------------*
FORM get_po_deleted.
  DATA: lt_ebeln TYPE RANGE OF ebeln, ls_ebeln LIKE LINE OF lt_ebeln.
  LOOP AT lt_po_base INTO DATA(ls_po_base). 
    ls_ebeln-sign = 'I'. ls_ebeln-option = 'EQ'. ls_ebeln-low = ls_po_base-ebeln.
    APPEND ls_ebeln TO lt_ebeln.
  ENDLOOP.
  IF lt_ebeln IS INITIAL. RETURN. ENDIF.

  SELECT h~objectid, h~username, h~udate, h~utime
    FROM cdhdr AS h JOIN cdpos AS p ON h~changenr = p~changenr AND h~objectid = p~objectid
    WHERE h~objectclas = 'EINKBELEG' AND h~objectid IN lt_ebeln AND p~tabname = 'EKPO' AND p~fname = 'LOEKZ' AND p~value_new = 'L'
    INTO TABLE @DATA(lt_cd_del).

  LOOP AT lt_cd_del ASSIGNING FIELD-SYMBOL(<fs_cd>).
    READ TABLE lt_po_base ASSIGNING FIELD-SYMBOL(<fs_po>) WITH KEY ebeln = <fs_cd>-objectid.
    IF sy-subrc <> 0. CONTINUE. ENDIF.
    DATA(ls_event) = VALUE ty_event_log(
        purchaseorder = <fs_po>-ebeln
        activity      = 'Purchase Order Deleted'
        username      = <fs_cd>-username
        vendornumber  = <fs_po>-lifnr
        orderamount   = <fs_po>-netwr
        materialgroup = <fs_po>-matkl
        companycode   = <fs_po>-bukrs
        documenttype  = <fs_po>-bsart
    ).
    CONVERT DATE <fs_cd>-udate TIME <fs_cd>-utime INTO TIME STAMP ls_event-eventtime TIME ZONE sy-zonlo.
    APPEND ls_event TO gt_event_log.
  ENDLOOP.
ENDFORM.

*&---------------------------------------------------------------------*
*&      Form  DOWNLOAD_TO_CSV
*&---------------------------------------------------------------------*
FORM download_to_csv.
  DATA: lv_filename TYPE string.
  DATA: lt_fieldnames TYPE TABLE OF string.

  APPEND 'PurchaseOrder' TO lt_fieldnames.
  APPEND 'Activity' TO lt_fieldnames.
  APPEND 'EventTime' TO lt_fieldnames.
  APPEND 'UserName' TO lt_fieldnames.
  APPEND 'VendorNumber' TO lt_fieldnames.
  APPEND 'OrderAmount' TO lt_fieldnames.
  APPEND 'MaterialGroup' TO lt_fieldnames.
  APPEND 'CompanyCode' TO lt_fieldnames.
  APPEND 'DocumentType' TO lt_fieldnames.

  DATA(lv_header) = REDUCE string( INIT h = '' FOR f IN lt_fieldnames NEXT h = h && f && cl_abap_char_utilities=>horizontal_tab ).
  REPLACE LAST OCCURRENCE OF cl_abap_char_utilities=>horizontal_tab IN lv_header WITH cl_abap_char_utilities=>cr_lf.

  DATA(lv_file_content) = lv_header.

  LOOP AT gt_event_log ASSIGNING FIELD-SYMBOL(<fs_log>).
    DATA lv_line TYPE string.
    DATA lv_eventtime_str TYPE string.
    lv_eventtime_str = |{ <fs_log>-eventtime TIMESTAMP = ISO }|.
    lv_line = <fs_log>-purchaseorder && cl_abap_char_utilities=>horizontal_tab &&
              <fs_log>-activity      && cl_abap_char_utilities=>horizontal_tab &&
              lv_eventtime_str       && cl_abap_char_utilities=>horizontal_tab &&
              <fs_log>-username      && cl_abap_char_utilities=>horizontal_tab &&
              <fs_log>-vendornumber  && cl_abap_char_utilities=>horizontal_tab &&
              <fs_log>-orderamount   && cl_abap_char_utilities=>horizontal_tab &&
              <fs_log>-materialgroup && cl_abap_char_utilities=>horizontal_tab &&
              <fs_log>-companycode    && cl_abap_char_utilities=>horizontal_tab &&
              <fs_log>-documenttype  && cl_abap_char_utilities=>cr_lf.
    CONCATENATE lv_file_content lv_line INTO lv_file_content.
  ENDLOOP.

  CALL METHOD cl_gui_frontend_services=>gui_download
    EXPORTING
      filename = 'C:\temp\po_event_log.csv'
      filetype = 'ASC'
    CHANGING
      data_tab = lv_file_content.

WITH PO_BASE AS (
  SELECT
    H.EBELN, -- Purchase Order Number
    I.EBELP, -- Purchase Order Item
    H.LIFNR, -- Vendor Number
    H.BUKRS, -- Company Code
    H.BSART, -- Document Type
    I.NETWR, -- Order Amount (Item Level)
    I.MATKL, -- Material Group
    I.BANFN, -- Purchase Requisition Number
    I.BNFPO  -- Purchase Requisition Item
  FROM [Your SAP Schema].EKKO AS H
  JOIN [Your SAP Schema].EKPO AS I ON H.EBELN = I.EBELN
  WHERE H.AEDAT BETWEEN '[START_DATE]' AND '[END_DATE]' -- Filter on PO Change Date, e.g., '20230101' and '20231231'
    AND H.BUKRS IN ('[COMPANY_CODE_1]', '[COMPANY_CODE_2]') -- Specify Company Codes
    AND H.BSART IN ('[DOC_TYPE_1]', '[DOC_TYPE_2]') -- Specify PO Document Types
)
-- 1. Purchase Requisition Created
SELECT
  po.EBELN AS "PurchaseOrder",
  'Purchase Requisition Created' AS "Activity",
  TO_TIMESTAMP(CONCAT(pr.ERDAT, '000000'), 'YYYYMMDDHH24MISS') AS "EventTime", -- Time is not available in EBAN
  pr.ERNAM AS "UserName",
  po.LIFNR AS "VendorNumber",
  po.NETWR AS "OrderAmount",
  po.MATKL AS "MaterialGroup",
  po.BUKRS AS "CompanyCode",
  po.BSART AS "DocumentType"
FROM PO_BASE po
JOIN [Your SAP Schema].EBAN pr ON po.BANFN = pr.BANFN AND po.BNFPO = pr.BNFPO
WHERE po.BANFN IS NOT NULL AND po.BANFN <> ''

UNION ALL

-- 2. Purchase Requisition Approved
SELECT
  po.EBELN AS "PurchaseOrder",
  'Purchase Requisition Approved' AS "Activity",
  TO_TIMESTAMP(CONCAT(ch.UDATE, ' ', ch.UTIME), 'YYYYMMDD HH24MISS') AS "EventTime",
  ch.USERNAME AS "UserName",
  po.LIFNR AS "VendorNumber",
  po.NETWR AS "OrderAmount",
  po.MATKL AS "MaterialGroup",
  po.BUKRS AS "CompanyCode",
  po.BSART AS "DocumentType"
FROM PO_BASE po
JOIN [Your SAP Schema].CDHDR ch ON ch.OBJECTCLASS = 'BANF' AND ch.OBJECTID = po.BANFN
JOIN [Your SAP Schema].CDPOS cp ON ch.OBJECTCLASS = cp.OBJECTCLASS AND ch.OBJECTID = cp.OBJECTID AND ch.CHANGENR = cp.CHANGENR
WHERE cp.TABNAME = 'EBAN' AND cp.FNAME = 'FRGZU' AND cp.VALUE_NEW = 'X' -- Release indicator set to 'released'

UNION ALL

-- 3. Purchase Order Created
SELECT
  po.EBELN AS "PurchaseOrder",
  'Purchase Order Created' AS "Activity",
  TO_TIMESTAMP(CONCAT(ekko.ERDAT, ' ', ekko.ERZET), 'YYYYMMDD HH24MISS') AS "EventTime",
  ekko.ERNAM AS "UserName",
  po.LIFNR AS "VendorNumber",
  po.NETWR AS "OrderAmount",
  po.MATKL AS "MaterialGroup",
  po.BUKRS AS "CompanyCode",
  po.BSART AS "DocumentType"
FROM PO_BASE po
JOIN [Your SAP Schema].EKKO ekko ON po.EBELN = ekko.EBELN

UNION ALL

-- 4. Purchase Order Approval Requested / 5. Approved / 6. Rejected (from Change Docs)
SELECT
  po.EBELN AS "PurchaseOrder",
  CASE
    WHEN cp.VALUE_NEW > cp.VALUE_OLD THEN 'Purchase Order Approval Requested'
    WHEN cp.VALUE_NEW = ekko.FRGKE AND ekko.FRGKE = 'R' THEN 'Purchase Order Approved'
    ELSE 'Purchase Order Rejected' -- Simplified logic, may need adjustment
  END AS "Activity",
  TO_TIMESTAMP(CONCAT(ch.UDATE, ' ', ch.UTIME), 'YYYYMMDD HH24MISS') AS "EventTime",
  ch.USERNAME AS "UserName",
  po.LIFNR AS "VendorNumber",
  po.NETWR AS "OrderAmount",
  po.MATKL AS "MaterialGroup",
  po.BUKRS AS "CompanyCode",
  po.BSART AS "DocumentType"
FROM PO_BASE po
JOIN [Your SAP Schema].EKKO ekko ON po.EBELN = ekko.EBELN
JOIN [Your SAP Schema].CDHDR ch ON ch.OBJECTCLASS = 'EINKBELEG' AND ch.OBJECTID = po.EBELN
JOIN [Your SAP Schema].CDPOS cp ON ch.OBJECTCLASS = cp.OBJECTCLASS AND ch.OBJECTID = cp.OBJECTID AND ch.CHANGENR = cp.CHANGENR
WHERE cp.TABNAME = 'EKKO' AND cp.FNAME = 'FRGZU' -- Release status

UNION ALL

-- 7. Purchase Order Sent to Vendor
SELECT
  po.EBELN AS "PurchaseOrder",
  'Purchase Order Sent to Vendor' AS "Activity",
  TO_TIMESTAMP(CONCAT(na.ERDAT, ' ', na.ERUHR), 'YYYYMMDD HH24MISS') AS "EventTime",
  na.USNAM AS "UserName",
  po.LIFNR AS "VendorNumber",
  po.NETWR AS "OrderAmount",
  po.MATKL AS "MaterialGroup",
  po.BUKRS AS "CompanyCode",
  po.BSART AS "DocumentType"
FROM PO_BASE po
JOIN [Your SAP Schema].NAST na ON na.OBJKY = po.EBELN AND na.KSCHL = '[Your PO Output Type]' -- e.g., 'NEU'
WHERE na.VSTAT = '1' -- Successfully processed

UNION ALL

-- 8. Purchase Order Changed
SELECT
  po.EBELN AS "PurchaseOrder",
  'Purchase Order Changed' AS "Activity",
  TO_TIMESTAMP(CONCAT(ch.UDATE, ' ', ch.UTIME), 'YYYYMMDD HH24MISS') AS "EventTime",
  ch.USERNAME AS "UserName",
  po.LIFNR AS "VendorNumber",
  po.NETWR AS "OrderAmount",
  po.MATKL AS "MaterialGroup",
  po.BUKRS AS "CompanyCode",
  po.BSART AS "DocumentType"
FROM PO_BASE po
JOIN [Your SAP Schema].CDHDR ch ON ch.OBJECTCLASS = 'EINKBELEG' AND ch.OBJECTID = po.EBELN
WHERE ch.TCODE IN ('ME22', 'ME22N') -- Filter for change transactions

UNION ALL

-- 9. Goods Receipt Posted
SELECT
  ekbe.EBELN AS "PurchaseOrder",
  'Goods Receipt Posted' AS "Activity",
  TO_TIMESTAMP(CONCAT(mkpf.CPUDT, ' ', mkpf.CPUTM), 'YYYYMMDD HH24MISS') AS "EventTime",
  mkpf.USNAM AS "UserName",
  po.LIFNR AS "VendorNumber",
  po.NETWR AS "OrderAmount",
  po.MATKL AS "MaterialGroup",
  po.BUKRS AS "CompanyCode",
  po.BSART AS "DocumentType"
FROM [Your SAP Schema].EKBE AS ekbe
JOIN [Your SAP Schema].MKPF AS mkpf ON ekbe.BELNR = mkpf.MBLNR AND ekbe.GJAHR = mkpf.MJAHR
JOIN PO_BASE AS po ON ekbe.EBELN = po.EBELN AND ekbe.EBELP = po.EBELP
WHERE ekbe.BEWTP = 'E' -- Goods Receipt
  AND ekbe.SHKZG = 'S' -- Debit/Credit Indicator: Goods Receipt

UNION ALL

-- 10. Services Confirmation Entered
SELECT
  po.EBELN AS "PurchaseOrder",
  'Services Confirmation Entered' AS "Activity",
  TO_TIMESTAMP(CONCAT(essr.ERDAT, ' ', essr.ERZET), 'YYYYMMDD HH24MISS') AS "EventTime",
  essr.ERNAM AS "UserName",
  po.LIFNR AS "VendorNumber",
  po.NETWR AS "OrderAmount",
  po.MATKL AS "MaterialGroup",
  po.BUKRS AS "CompanyCode",
  po.BSART AS "DocumentType"
FROM PO_BASE po
JOIN [Your SAP Schema].EKBE ekbe ON po.EBELN = ekbe.EBELN AND po.EBELP = ekbe.EBELP
JOIN [Your SAP Schema].ESSR essr ON ekbe.LBLNI = essr.LBLNI
WHERE ekbe.BEWTP = 'L' -- Service Entry Sheet

UNION ALL

-- 11. Quality Inspection Performed
SELECT
  po.EBELN AS "PurchaseOrder",
  'Quality Inspection Performed' AS "Activity",
  TO_TIMESTAMP(CONCAT(qave.VDATUM, ' ', qave.VZEIT), 'YYYYMMDD HH24MISS') AS "EventTime",
  qave.VNAME AS "UserName",
  po.LIFNR AS "VendorNumber",
  po.NETWR AS "OrderAmount",
  po.MATKL AS "MaterialGroup",
  po.BUKRS AS "CompanyCode",
  po.BSART AS "DocumentType"
FROM PO_BASE po
JOIN [Your SAP Schema].EKBE ekbe ON po.EBELN = ekbe.EBELN AND po.EBELP = ekbe.EBELP
JOIN [Your SAP Schema].QALS qals ON qals.MBLNR = ekbe.BELNR AND qals.MJAHR = ekbe.GJAHR
JOIN [Your SAP Schema].QAVE qave ON qals.PRUEFLOS = qave.PRUEFLOS
WHERE ekbe.BEWTP = 'E' -- Linked to a Goods Receipt

UNION ALL

-- 12. Goods Returned
SELECT
  ekbe.EBELN AS "PurchaseOrder",
  'Goods Returned' AS "Activity",
  TO_TIMESTAMP(CONCAT(mkpf.CPUDT, ' ', mkpf.CPUTM), 'YYYYMMDD HH24MISS') AS "EventTime",
  mkpf.USNAM AS "UserName",
  po.LIFNR AS "VendorNumber",
  po.NETWR AS "OrderAmount",
  po.MATKL AS "MaterialGroup",
  po.BUKRS AS "CompanyCode",
  po.BSART AS "DocumentType"
FROM [Your SAP Schema].EKBE AS ekbe
JOIN [Your SAP Schema].MKPF AS mkpf ON ekbe.BELNR = mkpf.MBLNR AND ekbe.GJAHR = mkpf.MJAHR
JOIN PO_BASE AS po ON ekbe.EBELN = po.EBELN AND ekbe.EBELP = po.EBELP
WHERE ekbe.BEWTP = 'E' -- Goods Movement
  AND ekbe.SHKZG = 'H' -- Debit/Credit Indicator: Return
  AND ekbe.BWART = '122' -- Movement type for return to vendor

UNION ALL

-- 13. Purchase Order Completed
SELECT
  po.EBELN AS "PurchaseOrder",
  'Purchase Order Completed' AS "Activity",
  TO_TIMESTAMP(CONCAT(ch.UDATE, ' ', ch.UTIME), 'YYYYMMDD HH24MISS') AS "EventTime",
  ch.USERNAME AS "UserName",
  po.LIFNR AS "VendorNumber",
  po.NETWR AS "OrderAmount",
  po.MATKL AS "MaterialGroup",
  po.BUKRS AS "CompanyCode",
  po.BSART AS "DocumentType"
FROM PO_BASE po
JOIN [Your SAP Schema].CDHDR ch ON ch.OBJECTCLASS = 'EINKBELEG' AND ch.OBJECTID LIKE CONCAT(po.EBELN, po.EBELP, '%')
JOIN [Your SAP Schema].CDPOS cp ON ch.OBJECTCLASS = cp.OBJECTCLASS AND ch.OBJECTID = cp.OBJECTID AND ch.CHANGENR = cp.CHANGENR
WHERE cp.TABNAME = 'EKPO' AND cp.FNAME = 'ELIKZ' AND cp.VALUE_NEW = 'X' -- Delivery completed indicator

UNION ALL

-- 14. Purchase Order Deleted
SELECT
  po.EBELN AS "PurchaseOrder",
  'Purchase Order Deleted' AS "Activity",
  TO_TIMESTAMP(CONCAT(ch.UDATE, ' ', ch.UTIME), 'YYYYMMDD HH24MISS') AS "EventTime",
  ch.USERNAME AS "UserName",
  po.LIFNR AS "VendorNumber",
  po.NETWR AS "OrderAmount",
  po.MATKL AS "MaterialGroup",
  po.BUKRS AS "CompanyCode",
  po.BSART AS "DocumentType"
FROM PO_BASE po
JOIN [Your SAP Schema].CDHDR ch ON ch.OBJECTCLASS = 'EINKBELEG' AND ch.OBJECTID LIKE CONCAT(po.EBELN, po.EBELP, '%')
JOIN [Your SAP Schema].CDPOS cp ON ch.OBJECTCLASS = cp.OBJECTCLASS AND ch.OBJECTID = cp.OBJECTID AND ch.CHANGENR = cp.CHANGENR
WHERE cp.TABNAME = 'EKPO' AND cp.FNAME = 'LOEKZ' AND cp.VALUE_NEW = 'L'; -- Deletion indicator

/*
This is a logical representation of the transformations performed within the ETL tool.
The tool's graphical interface will be used to configure these separate data flows, which are then combined with a UNION transformation.
Placeholders like [Your ETL Tool Functions] and [Filter Values] must be configured in the tool.
*/

-- 1. Purchase Requisition Created
SELECT 
    ekpo.EBELN AS PurchaseOrder,
    'Purchase Requisition Created' AS Activity,
    [Your ETL Tool Functions].DateTime(eban.ERDAT, eban.ERZET) AS EventTime,
    eban.ERNAM AS UserName,
    ekko.LIFNR AS VendorNumber,
    ekpo.NETWR AS OrderAmount,
    ekpo.MATKL AS MaterialGroup,
    ekko.BUKRS AS CompanyCode,
    ekko.BSART AS DocumentType
FROM EBAN AS eban
INNER JOIN EKPO AS ekpo ON eban.BANFN = ekpo.BANFN AND eban.BNFPO = ekpo.BNFPO
INNER JOIN EKKO AS ekko ON ekpo.EBELN = ekko.EBELN
WHERE ekko.AEDAT BETWEEN '[START_DATE]' AND '[END_DATE]' AND ekko.BUKRS IN ([YOUR_COMPANY_CODES]);

UNION ALL

-- 2. Purchase Requisition Approved (inferred from change documents)
SELECT
    ekpo.EBELN AS PurchaseOrder,
    'Purchase Requisition Approved' AS Activity,
    [Your ETL Tool Functions].DateTime(cdhdr.UDATE, cdhdr.UTIME) AS EventTime,
    cdhdr.USERNAME AS UserName,
    ekko.LIFNR AS VendorNumber,
    ekpo.NETWR AS OrderAmount,
    ekpo.MATKL AS MaterialGroup,
    ekko.BUKRS AS CompanyCode,
    ekko.BSART AS DocumentType
FROM CDHDR AS cdhdr
INNER JOIN CDPOS AS cdpos ON cdhdr.CHANGENR = cdpos.CHANGENR
INNER JOIN EBAN AS eban ON cdhdr.OBJECTID = eban.BANFN
INNER JOIN EKPO AS ekpo ON eban.BANFN = ekpo.BANFN AND eban.BNFPO = ekpo.BNFPO
INNER JOIN EKKO AS ekko ON ekpo.EBELN = ekko.EBELN
WHERE cdhdr.OBJECTCLAS = 'BANF' AND cdpos.TABNAME = 'EBAN' AND cdpos.FNAME = 'FRGZU' AND cdpos.VALUE_NEW = '[Final Release Indicator for PR]'
AND ekko.AEDAT BETWEEN '[START_DATE]' AND '[END_DATE]' AND ekko.BUKRS IN ([YOUR_COMPANY_CODES]);

UNION ALL

-- 3. Purchase Order Created
SELECT 
    EBELN AS PurchaseOrder,
    'Purchase Order Created' AS Activity,
    [Your ETL Tool Functions].DateTime(AEDAT, ERZET) AS EventTime,
    ERNAM AS UserName,
    LIFNR AS VendorNumber,
    NULL AS OrderAmount, -- Amount is at item level
    NULL AS MaterialGroup, -- Attribute is at item level
    BUKRS AS CompanyCode,
    BSART AS DocumentType
FROM EKKO
WHERE AEDAT BETWEEN '[START_DATE]' AND '[END_DATE]' AND BUKRS IN ([YOUR_COMPANY_CODES]);

UNION ALL

-- 4. Purchase Order Approval Requested / 5. Approved / 6. Rejected (inferred from change documents)
SELECT 
    ekpo.EBELN AS PurchaseOrder,
    CASE 
        WHEN cdpos.VALUE_NEW = '[Final Release Code]' THEN 'Purchase Order Approved'
        WHEN cdpos.VALUE_NEW = '[Rejection Release Code]' THEN 'Purchase Order Rejected'
        ELSE 'Purchase Order Approval Requested'
    END AS Activity,
    [Your ETL Tool Functions].DateTime(cdhdr.UDATE, cdhdr.UTIME) AS EventTime,
    cdhdr.USERNAME AS UserName,
    ekko.LIFNR AS VendorNumber,
    ekpo.NETWR AS OrderAmount,
    ekpo.MATKL AS MaterialGroup,
    ekko.BUKRS AS CompanyCode,
    ekko.BSART AS DocumentType
FROM CDHDR AS cdhdr
INNER JOIN CDPOS AS cdpos ON cdhdr.CHANGENR = cdpos.CHANGENR
INNER JOIN EKKO AS ekko ON SUBSTRING(cdhdr.OBJECTID, 1, 10) = ekko.EBELN
INNER JOIN EKPO AS ekpo ON ekko.EBELN = ekpo.EBELN
WHERE cdhdr.OBJECTCLAS = 'EINKBELEG' AND cdpos.TABNAME = 'EKKO' AND cdpos.FNAME = 'FRGKE'
AND ekko.AEDAT BETWEEN '[START_DATE]' AND '[END_DATE]' AND ekko.BUKRS IN ([YOUR_COMPANY_CODES]);

UNION ALL

-- 7. Purchase Order Sent to Vendor
SELECT
    ekko.EBELN AS PurchaseOrder,
    'Purchase Order Sent to Vendor' AS Activity,
    [Your ETL Tool Functions].DateTime(nast.ERDAT, nast.UHR) AS EventTime,
    nast.USNAM AS UserName,
    ekko.LIFNR AS VendorNumber,
    ekpo.NETWR AS OrderAmount,
    ekpo.MATKL AS MaterialGroup,
    ekko.BUKRS AS CompanyCode,
    ekko.BSART AS DocumentType
FROM NAST AS nast
INNER JOIN EKKO AS ekko ON nast.OBJKY = ekko.EBELN
INNER JOIN EKPO AS ekpo ON ekko.EBELN = ekpo.EBELN
WHERE nast.KAPPL = 'EF' AND nast.VSTAT = '1' AND nast.KSCHL IN ([Your PO Output Types])
AND ekko.AEDAT BETWEEN '[START_DATE]' AND '[END_DATE]' AND ekko.BUKRS IN ([YOUR_COMPANY_CODES]);

UNION ALL

-- 8. Purchase Order Changed (inferred from change documents, simplified example)
SELECT DISTINCT
    ekko.EBELN AS PurchaseOrder,
    'Purchase Order Changed' AS Activity,
    [Your ETL Tool Functions].DateTime(cdhdr.UDATE, cdhdr.UTIME) AS EventTime,
    cdhdr.USERNAME AS UserName,
    ekko.LIFNR AS VendorNumber,
    ekpo.NETWR AS OrderAmount,
    ekpo.MATKL AS MaterialGroup,
    ekko.BUKRS AS CompanyCode,
    ekko.BSART AS DocumentType
FROM CDHDR AS cdhdr
INNER JOIN CDPOS AS cdpos ON cdhdr.CHANGENR = cdpos.CHANGENR
INNER JOIN EKKO AS ekko ON SUBSTRING(cdhdr.OBJECTID, 1, 10) = ekko.EBELN
INNER JOIN EKPO AS ekpo ON ekko.EBELN = ekpo.EBELN
WHERE cdhdr.OBJECTCLAS = 'EINKBELEG' AND cdpos.FNAME NOT IN ('FRGKE', 'FRGZU', 'LOEKZ', 'ELIKZ')
AND ekko.AEDAT BETWEEN '[START_DATE]' AND '[END_DATE]' AND ekko.BUKRS IN ([YOUR_COMPANY_CODES]);

UNION ALL

-- 9. Goods Receipt Posted
SELECT
    mseg.EBELN AS PurchaseOrder,
    'Goods Receipt Posted' AS Activity,
    [Your ETL Tool Functions].DateTime(mkpf.CPUDT, mkpf.CPUTM) AS EventTime,
    mkpf.USNAM AS UserName,
    ekko.LIFNR AS VendorNumber,
    ekpo.NETWR AS OrderAmount,
    ekpo.MATKL AS MaterialGroup,
    ekko.BUKRS AS CompanyCode,
    ekko.BSART AS DocumentType
FROM MSEG AS mseg
INNER JOIN MKPF AS mkpf ON mseg.MBLNR = mkpf.MBLNR AND mseg.MJAHR = mkpf.MJAHR
INNER JOIN EKPO AS ekpo ON mseg.EBELN = ekpo.EBELN AND mseg.EBELP = ekpo.EBELP
INNER JOIN EKKO AS ekko ON ekpo.EBELN = ekko.EBELN
WHERE mseg.BWART = '101' AND ekko.AEDAT BETWEEN '[START_DATE]' AND '[END_DATE]' AND ekko.BUKRS IN ([YOUR_COMPANY_CODES]);

UNION ALL

-- 10. Services Confirmation Entered
SELECT
    essr.EBELN AS PurchaseOrder,
    'Services Confirmation Entered' AS Activity,
    [Your ETL Tool Functions].DateTime(essr.ERDAT, essr.ERZET) AS EventTime,
    essr.ERNAM AS UserName,
    ekko.LIFNR AS VendorNumber,
    ekpo.NETWR AS OrderAmount,
    ekpo.MATKL AS MaterialGroup,
    ekko.BUKRS AS CompanyCode,
    ekko.BSART AS DocumentType
FROM ESSR AS essr
INNER JOIN EKKO AS ekko ON essr.EBELN = ekko.EBELN
INNER JOIN EKPO AS ekpo ON essr.EBELN = ekpo.EBELN AND essr.EBELP = ekpo.EBELP
WHERE ekko.AEDAT BETWEEN '[START_DATE]' AND '[END_DATE]' AND ekko.BUKRS IN ([YOUR_COMPANY_CODES]);

UNION ALL

-- 11. Quality Inspection Performed
SELECT 
    qals.EBELN AS PurchaseOrder,
    'Quality Inspection Performed' AS Activity,
    [Your ETL Tool Functions].DateTime(qals.PASTRTERM, '000000') AS EventTime, -- Time is often not available
    qals.PRUEFER AS UserName,
    ekko.LIFNR AS VendorNumber,
    ekpo.NETWR AS OrderAmount,
    ekpo.MATKL AS MaterialGroup,
    ekko.BUKRS AS CompanyCode,
    ekko.BSART AS DocumentType
FROM QALS AS qals
INNER JOIN EKKO AS ekko ON qals.EBELN = ekko.EBELN
INNER JOIN EKPO AS ekpo ON qals.EBELN = ekpo.EBELN AND qals.EBELP = ekpo.EBELP
WHERE qals.VCODE <> '' -- A usage decision code exists
AND ekko.AEDAT BETWEEN '[START_DATE]' AND '[END_DATE]' AND ekko.BUKRS IN ([YOUR_COMPANY_CODES]);

UNION ALL

-- 12. Goods Returned
SELECT
    mseg.EBELN AS PurchaseOrder,
    'Goods Returned' AS Activity,
    [Your ETL Tool Functions].DateTime(mkpf.CPUDT, mkpf.CPUTM) AS EventTime,
    mkpf.USNAM AS UserName,
    ekko.LIFNR AS VendorNumber,
    ekpo.NETWR AS OrderAmount,
    ekpo.MATKL AS MaterialGroup,
    ekko.BUKRS AS CompanyCode,
    ekko.BSART AS DocumentType
FROM MSEG AS mseg
INNER JOIN MKPF AS mkpf ON mseg.MBLNR = mkpf.MBLNR AND mseg.MJAHR = mkpf.MJAHR
INNER JOIN EKPO AS ekpo ON mseg.EBELN = ekpo.EBELN AND mseg.EBELP = ekpo.EBELP
INNER JOIN EKKO AS ekko ON ekpo.EBELN = ekko.EBELN
WHERE mseg.BWART = '122' AND ekko.AEDAT BETWEEN '[START_DATE]' AND '[END_DATE]' AND ekko.BUKRS IN ([YOUR_COMPANY_CODES]);

UNION ALL

-- 13. Purchase Order Completed (inferred from change documents)
SELECT 
    ekpo.EBELN AS PurchaseOrder,
    'Purchase Order Completed' AS Activity,
    [Your ETL Tool Functions].DateTime(cdhdr.UDATE, cdhdr.UTIME) AS EventTime,
    cdhdr.USERNAME AS UserName,
    ekko.LIFNR AS VendorNumber,
    ekpo.NETWR AS OrderAmount,
    ekpo.MATKL AS MaterialGroup,
    ekko.BUKRS AS CompanyCode,
    ekko.BSART AS DocumentType
FROM CDHDR AS cdhdr
INNER JOIN CDPOS AS cdpos ON cdhdr.CHANGENR = cdpos.CHANGENR
INNER JOIN EKPO AS ekpo ON SUBSTRING(cdhdr.OBJECTID, 1, 10) = ekpo.EBELN AND SUBSTRING(cdhdr.OBJECTID, 11, 5) = ekpo.EBELP
INNER JOIN EKKO AS ekko ON ekpo.EBELN = ekko.EBELN
WHERE cdhdr.OBJECTCLAS = 'EINKBELEG' AND cdpos.TABNAME = 'EKPO' AND cdpos.FNAME = 'ELIKZ' AND cdpos.VALUE_NEW = 'X'
AND ekko.AEDAT BETWEEN '[START_DATE]' AND '[END_DATE]' AND ekko.BUKRS IN ([YOUR_COMPANY_CODES]);

UNION ALL

-- 14. Purchase Order Deleted (inferred from change documents)
SELECT 
    ekpo.EBELN AS PurchaseOrder,
    'Purchase Order Deleted' AS Activity,
    [Your ETL Tool Functions].DateTime(cdhdr.UDATE, cdhdr.UTIME) AS EventTime,
    cdhdr.USERNAME AS UserName,
    ekko.LIFNR AS VendorNumber,
    ekpo.NETWR AS OrderAmount,
    ekpo.MATKL AS MaterialGroup,
    ekko.BUKRS AS CompanyCode,
    ekko.BSART AS DocumentType
FROM CDHDR AS cdhdr
INNER JOIN CDPOS AS cdpos ON cdhdr.CHANGENR = cdpos.CHANGENR
INNER JOIN EKPO AS ekpo ON SUBSTRING(cdhdr.OBJECTID, 1, 10) = ekpo.EBELN AND SUBSTRING(cdhdr.OBJECTID, 11, 5) = ekpo.EBELP
INNER JOIN EKKO AS ekko ON ekpo.EBELN = ekko.EBELN
WHERE cdhdr.OBJECTCLAS = 'EINKBELEG' AND cdpos.TABNAME = 'EKPO' AND cdpos.FNAME = 'LOEKZ' AND cdpos.VALUE_NEW = 'L'
AND ekko.AEDAT BETWEEN '[START_DATE]' AND '[END_DATE]' AND ekko.BUKRS IN ([YOUR_COMPANY_CODES]);