เทมเพลตข้อมูล Record to Report - รายการบัญชีของคุณ

REPORT Z_PM_JE_EXTRACTION.

*&---------------------------------------------------------------------*
*& Data Structures for Final Event Log
*&---------------------------------------------------------------------*
TYPES: BEGIN OF ty_event_log,
         journalentryid TYPE string,
         activityname   TYPE string,
         eventtime      TYPE timestamp,
         sourcesystem   TYPE string,
         lastdataupdate TYPE timestamp,
         username       TYPE uname,
         companycode    TYPE bukrs,
         documenttype   TYPE blart,
         postingdate    TYPE budat,
         transactioncode TYPE tcode,
         isreversed     TYPE abap_bool,
       END OF ty_event_log.

DATA: lt_final_log TYPE STANDARD TABLE OF ty_event_log.
DATA: ls_event     TYPE ty_event_log.

*&---------------------------------------------------------------------*
*& Selection Screen Parameters
*&---------------------------------------------------------------------*
SELECT-OPTIONS: s_bukrs FOR bkpf-bukrs OBLIGATORY,
                  s_blart FOR bkpf-blart,
                  s_cpudt FOR bkpf-cpudt OBLIGATORY.

PARAMETERS: p_sysid TYPE sy-sysid DEFAULT sy-sysid.

*&---------------------------------------------------------------------*
*& Main Logic
*&---------------------------------------------------------------------*
START-OF-SELECTION.

  DATA(lv_last_update) = cl_abap_context_info=>get_system_timestamp( ).

  " 1. Journal Entry Parked
  SELECT CONCAT( a~bukrs, a~belnr, a~gjahr ) AS journalentryid,
         'Journal Entry Parked' AS activityname,
         a~cpudt, a~cputm,
         a~usnam AS username,
         a~bukrs AS companycode,
         a~blart AS documenttype,
         a~bldat AS postingdate,
         a~tcode AS transactioncode
    FROM bkpf AS a
    WHERE a~bukrs IN s_bukrs
      AND a~blart IN s_blart
      AND a~cpudt IN s_cpudt
      AND a~bstat = 'V' " Parked Document
    INTO TABLE @DATA(lt_parked).

  IF sy-subrc = 0.
    LOOP AT lt_parked ASSIGNING FIELD-SYMBOL(<fs_parked>).
      ls_event-journalentryid = <fs_parked>-journalentryid.
      ls_event-activityname   = <fs_parked>-activityname.
      CONVERT DATE <fs_parked>-cpudt TIME <fs_parked>-cputm INTO TIME STAMP ls_event-eventtime TIME ZONE sy-zonlo.
      ls_event-sourcesystem   = p_sysid.
      ls_event-lastdataupdate = lv_last_update.
      ls_event-username       = <fs_parked>-username.
      ls_event-companycode    = <fs_parked>-companycode.
      ls_event-documenttype   = <fs_parked>-documenttype.
      ls_event-postingdate    = <fs_parked>-postingdate.
      ls_event-transactioncode = <fs_parked>-transactioncode.
      APPEND ls_event TO lt_final_log.
    ENDLOOP.
  ENDIF.

  " 2. Journal Entry Created (directly posted, not parked first)
  " 9. Journal Entry Posted
  " These two events happen at the same time for a direct posting.
  SELECT CONCAT( bukrs, belnr, gjahr ) AS journalentryid,
         cpudt, cputm, usnam, bukrs, blart, budat, tcode, stblg
    FROM bkpf
    WHERE bukrs IN s_bukrs
      AND blart IN s_blart
      AND cpudt IN s_cpudt
      AND bstat = '' " Normal, posted document
    INTO TABLE @DATA(lt_posted).

  IF sy-subrc = 0.
    LOOP AT lt_posted ASSIGNING FIELD-SYMBOL(<fs_posted>).
      " Activity: Journal Entry Created
      ls_event-journalentryid = <fs_posted>-journalentryid.
      ls_event-activityname   = 'Journal Entry Created'.
      CONVERT DATE <fs_posted>-cpudt TIME <fs_posted>-cputm INTO TIME STAMP ls_event-eventtime TIME ZONE sy-zonlo.
      ls_event-sourcesystem   = p_sysid.
      ls_event-lastdataupdate = lv_last_update.
      ls_event-username       = <fs_posted>-usnam.
      ls_event-companycode    = <fs_posted>-bukrs.
      ls_event-documenttype   = <fs_posted>-blart.
      ls_event-postingdate    = <fs_posted>-budat.
      ls_event-transactioncode = <fs_posted>-tcode.
      ls_event-isreversed     = COND #( WHEN <fs_posted>-stblg IS NOT INITIAL THEN abap_true ELSE abap_false ).
      APPEND ls_event TO lt_final_log.

      " Activity: Journal Entry Posted
      ls_event-activityname   = 'Journal Entry Posted'.
      APPEND ls_event TO lt_final_log.
    ENDLOOP.
  ENDIF.

  " 3. Documentation Attached (via GOS)
  SELECT a~instid_a, c~cr_timestamp
      FROM srgbtbrel AS a
      INNER JOIN sood AS b ON a~instid_b = b~objid
      INNER JOIN socf AS c ON b~filid = c~filid
      WHERE a~typeid_a = 'BKPF'
        AND a~bukrs IN s_bukrs
      INTO TABLE @DATA(lt_attachments).

  IF sy-subrc = 0.
    LOOP AT lt_attachments ASSIGNING FIELD-SYMBOL(<fs_attach>).
      ls_event-journalentryid = |{ <fs_attach>-instid_a(4) }{ <fs_attach>-instid_a+4(10) }{ <fs_attach>-instid_a+14(4) }|.
      ls_event-activityname   = 'Documentation Attached'.
      ls_event-eventtime      = <fs_attach>-cr_timestamp.
      " Other attributes may need to be looked up from BKPF if needed.
      APPEND ls_event TO lt_final_log.
    ENDLOOP.
  ENDIF.

  " 4, 5, 6, 7, 8: Workflow events (Submitted, Changes Requested, Corrected, Approved, Rejected)
  " This is a simplified example. Real logic depends on specific workflow templates.
  SELECT a~instid, b~wi_cd, b~wi_ct, b~wi_aagent, b~wi_text
    FROM sww_wi2obj AS a
    INNER JOIN swwloghist AS b ON a~wi_id = b~wi_id
    WHERE a~typeid = 'BKPF'
      AND a~catid = 'BO'
      AND a~bukrs IN s_bukrs
      AND b~wi_cd BETWEEN s_cpudt-low AND s_cpudt-high
    INTO TABLE @DATA(lt_workflow).
  IF sy-subrc = 0.
    LOOP AT lt_workflow ASSIGNING FIELD-SYMBOL(<fs_wf>).
      ls_event-journalentryid = |{ <fs_wf>-instid(4) }{ <fs_wf>-instid+4(10) }{ <fs_wf>-instid+14(4) }|.
      ls_event-activityname   = CASE <fs_wf>-wi_text. " Simplified logic based on work item text
                                WHEN '[Placeholder for Submit Text]' THEN 'Journal Entry Submitted'
                                WHEN '[Placeholder for Approve Text]' THEN 'Journal Entry Approved'
                                WHEN '[Placeholder for Reject Text]' THEN 'Journal Entry Rejected'
                                WHEN '[Placeholder for Rework Text]' THEN 'Journal Entry Changes Requested'
                                ELSE ''
                              ENDCASE.
      IF ls_event-activityname IS NOT INITIAL.
        CONVERT DATE <fs_wf>-wi_cd TIME <fs_wf>-wi_ct INTO TIME STAMP ls_event-eventtime TIME ZONE sy-zonlo.
        ls_event-username       = <fs_wf>-wi_aagent.
        APPEND ls_event TO lt_final_log.
      ENDIF.
    ENDLOOP.
  ENDIF.

  " 10. Manual Entry Identified & 11. Cross-Company Posting Identified
  SELECT bukrs, belnr, gjahr, tcode FROM bkpf
    WHERE bukrs IN s_bukrs AND blart IN s_blart AND cpudt IN s_cpudt
    INTO TABLE @DATA(lt_calc_base).

  LOOP AT lt_calc_base ASSIGNING FIELD-SYMBOL(<fs_calc>).
    ls_event-journalentryid = |{ <fs_calc>-bukrs }{ <fs_calc>-belnr }{ <fs_calc>-gjahr }|.
    " Check for manual entry T-Codes
    IF <fs_calc>-tcode = 'FB01' OR <fs_calc>-tcode = 'F-02' OR <fs_calc>-tcode = 'FB50'.
      ls_event-activityname = 'Manual Entry Identified'.
      APPEND ls_event TO lt_final_log.
    ENDIF.
    " Check for cross-company posting
    SELECT SINGLE bukrs FROM bseg WHERE belnr = <fs_calc>-belnr AND gjahr = <fs_calc>-gjahr AND bukrs <> <fs_calc>-bukrs INTO @DATA(lv_cross_bukrs).
    IF sy-subrc = 0.
        ls_event-activityname = 'Cross-Company Posting Identified'.
        APPEND ls_event TO lt_final_log.
    ENDIF.
  ENDLOOP.

  " 12. Journal Entry Line Item Cleared
  SELECT a~bukrs, a~belnr, a~gjahr, a~augdt, a~augbl
    FROM bsas AS a " G/L Cleared Items
    WHERE a~bukrs IN s_bukrs
      AND a~budat IN s_cpudt
    INTO TABLE @DATA(lt_cleared_gl).

  IF sy-subrc = 0.
    LOOP AT lt_cleared_gl ASSIGNING FIELD-SYMBOL(<fs_clr>).
        ls_event-journalentryid = |{ <fs_clr>-bukrs }{ <fs_clr>-belnr }{ <fs_clr>-gjahr }|.
        ls_event-activityname   = 'Journal Entry Line Item Cleared'.
        CONVERT DATE <fs_clr>-augdt INTO TIME STAMP ls_event-eventtime TIME ZONE sy-zonlo.
        " User is often not directly available for clearing events
        APPEND ls_event TO lt_final_log.
    ENDLOOP.
  ENDIF.

  " 13. Parked Journal Entry Deleted & 6. Journal Entry Corrected
  SELECT objectid, changenr, username, udate, utime FROM cdhdr
    WHERE objectclas = 'BELEG'
      AND udate IN s_cpudt
      INTO TABLE @DATA(lt_cdhdr).

  LOOP AT lt_cdhdr ASSIGNING FIELD-SYMBOL(<fs_cdhdr>).
      SELECT SINGLE tcode FROM cdpos WHERE changenr = <fs_cdhdr>-changenr AND fname = 'BSTAT' AND value_new = 'Z' INTO @DATA(lv_deleted_tcode).
      ls_event-journalentryid = |{ <fs_cdhdr>-objectid(4) }{ <fs_cdhdr>-objectid+4(10) }{ <fs_cdhdr>-objectid+14(4) }|.
      CONVERT DATE <fs_cdhdr>-udate TIME <fs_cdhdr>-utime INTO TIME STAMP ls_event-eventtime TIME ZONE sy-zonlo.
      ls_event-username = <fs_cdhdr>-username.
      IF sy-subrc = 0.
        ls_event-activityname = 'Parked Journal Entry Deleted'.
        APPEND ls_event TO lt_final_log.
      ELSE.
        ls_event-activityname = 'Journal Entry Corrected'.
        APPEND ls_event TO lt_final_log.
      ENDIF.
  ENDLOOP.

  " 14. Journal Entry Reversal Processed
  SELECT CONCAT( a~bukrs, a~belnr, a~gjahr ) AS journalentryid,
         a~cpudt, a~cputm, a~usnam
    FROM bkpf AS a
    WHERE a~bukrs IN s_bukrs
      AND a~blart IN s_blart
      AND a~cpudt IN s_cpudt
      AND a~stblg IS NOT NULL " Document is a reversal
    INTO TABLE @DATA(lt_reversals).

  IF sy-subrc = 0.
    LOOP AT lt_reversals ASSIGNING FIELD-SYMBOL(<fs_rev>).
      ls_event-journalentryid = <fs_rev>-journalentryid.
      ls_event-activityname   = 'Journal Entry Reversal Processed'.
      CONVERT DATE <fs_rev>-cpudt TIME <fs_rev>-cputm INTO TIME STAMP ls_event-eventtime TIME ZONE sy-zonlo.
      ls_event-username       = <fs_rev>-usnam.
      APPEND ls_event TO lt_final_log.
    ENDLOOP.
  ENDIF.

  " Final step: Output to file
  DATA(lv_filename) = |/tmp/je_extraction_{ sy-datum }_{ sy-uzeit }.csv|.
  OPEN DATASET lv_filename FOR OUTPUT IN TEXT MODE ENCODING UTF-8.
  IF sy-subrc = 0.
    " Write header
    DATA(lv_header) = 'JournalEntryId,ActivityName,EventTime,SourceSystem,LastDataUpdate,User,CompanyCode,DocumentType,PostingDate,TransactionCode,IsReversed'.
    TRANSFER lv_header TO lv_filename.
    LOOP AT lt_final_log INTO ls_event.
      DATA(lv_line) = |"{ ls_event-journalentryid }","|
                     |{ ls_event-activityname }","|
                     |{ ls_event-eventtime }","|
                     |{ ls_event-sourcesystem }","|
                     |{ ls_event-lastdataupdate }","|
                     |{ ls_event-username }","|
                     |{ ls_event-companycode }","|
                     |{ ls_event-documenttype }","|
                     |{ ls_event-postingdate }","|
                     |{ ls_event-transactioncode }","|
                     |{ ls_event-isreversed }"|.
      TRANSFER lv_line TO lv_filename.
    ENDLOOP.
    CLOSE DATASET lv_filename.
  ENDIF.

WITH DOC_HEADERS AS (
  SELECT
    BUKRS,
    BELNR,
    GJAHR,
    BLART,
    BLDAT,
    BUDAT,
    CPUDT,
    CPUTM,
    USNAM,
    TCODE,
    BSTAT,
    STBLG,
    XRECH
  FROM BKPF
  WHERE CPUDT BETWEEN '[START_DATE]' AND '[END_DATE]'
    AND BUKRS IN ('[COMPANY_CODE_1]', '[COMPANY_CODE_2]')
)
-- Event 1: Journal Entry Created (Directly Posted)
SELECT
  CONCAT(H.BUKRS, H.BELNR, H.GJAHR) AS "JournalEntryId",
  'Journal Entry Created' AS "ActivityName",
  TO_TIMESTAMP(CONCAT(H.CPUDT, H.CPUTM), 'YYYYMMDDHH24MISS') AS "EventTime",
  U.NAME_TEXT AS "User",
  H.BUKRS AS "CompanyCode",
  H.BLART AS "DocumentType",
  H.BUDAT AS "PostingDate",
  H.TCODE AS "TransactionCode",
  CASE WHEN H.STBLG IS NOT NULL AND H.STBLG <> '' THEN TRUE ELSE FALSE END AS "IsReversed"
FROM DOC_HEADERS H
LEFT JOIN V_USERNAME U ON H.USNAM = U.BNAME
WHERE H.BSTAT = '' OR H.BSTAT = 'U'

UNION ALL

-- Event 2: Journal Entry Parked
SELECT
  CONCAT(H.BUKRS, H.BELNR, H.GJAHR) AS "JournalEntryId",
  'Journal Entry Parked' AS "ActivityName",
  TO_TIMESTAMP(CONCAT(H.CPUDT, H.CPUTM), 'YYYYMMDDHH24MISS') AS "EventTime",
  U.NAME_TEXT AS "User",
  H.BUKRS AS "CompanyCode",
  H.BLART AS "DocumentType",
  H.BUDAT AS "PostingDate",
  H.TCODE AS "TransactionCode",
  FALSE AS "IsReversed"
FROM DOC_HEADERS H
LEFT JOIN V_USERNAME U ON H.USNAM = U.BNAME
WHERE H.BSTAT = 'V'

UNION ALL

-- Event 3: Journal Entry Posted (from Parked state)
SELECT 
    CONCAT(H.BUKRS, H.BELNR, H.GJAHR) AS "JournalEntryId",
    'Journal Entry Posted' AS "ActivityName",
    TO_TIMESTAMP(CONCAT(C.UDATE, C.UTIME), 'YYYYMMDDHH24MISS') AS "EventTime",
    U.NAME_TEXT AS "User",
    H.BUKRS AS "CompanyCode",
    H.BLART AS "DocumentType",
    H.BUDAT AS "PostingDate",
    C.TCODE AS "TransactionCode",
    CASE WHEN H.STBLG IS NOT NULL AND H.STBLG <> '' THEN TRUE ELSE FALSE END AS "IsReversed"
FROM DOC_HEADERS H
JOIN CDHDR C ON C.OBJECTCLAS = 'BELEG' AND C.OBJECTID = CONCAT(H.BUKRS, H.BELNR, H.GJAHR)
JOIN CDPOS P ON C.CHANGENR = P.CHANGENR AND P.OBJECTCLAS = 'BELEG' AND P.OBJECTID = C.OBJECTID
LEFT JOIN V_USERNAME U ON C.USERNAME = U.BNAME
WHERE H.BSTAT <> 'V'
  AND P.TABNAME = 'BKPF' 
  AND P.FNAME = 'BSTAT' 
  AND P.VALUE_OLD = 'V'
  AND P.VALUE_NEW <> 'V'

UNION ALL

-- Event 4: Parked Journal Entry Deleted
SELECT 
    CONCAT(H.BUKRS, H.BELNR, H.GJAHR) AS "JournalEntryId",
    'Parked Journal Entry Deleted' AS "ActivityName",
    TO_TIMESTAMP(CONCAT(C.UDATE, C.UTIME), 'YYYYMMDDHH24MISS') AS "EventTime",
    U.NAME_TEXT AS "User",
    H.BUKRS AS "CompanyCode",
    H.BLART AS "DocumentType",
    H.BUDAT AS "PostingDate",
    C.TCODE AS "TransactionCode",
    FALSE AS "IsReversed"
FROM DOC_HEADERS H
JOIN CDHDR C ON C.OBJECTCLAS = 'BELEG' AND C.OBJECTID = CONCAT(H.BUKRS, H.BELNR, H.GJAHR) AND C.TCODE = 'FBV0'
JOIN CDPOS P ON C.CHANGENR = P.CHANGENR AND P.OBJECTCLAS = 'BELEG' AND P.OBJECTID = C.OBJECTID
LEFT JOIN V_USERNAME U ON C.USERNAME = U.BNAME
WHERE P.TABNAME = 'BKPF' 
  AND P.FNAME = 'BSTAT' 
  AND P.VALUE_OLD = 'V' 
  AND P.VALUE_NEW = 'Z'

UNION ALL

-- Event 5: Journal Entry Reversal Processed
SELECT 
    CONCAT(H.BUKRS, H.STBLG, H.GJAHR) AS "JournalEntryId", -- Linking to the original document
    'Journal Entry Reversal Processed' AS "ActivityName",
    TO_TIMESTAMP(CONCAT(H.CPUDT, H.CPUTM), 'YYYYMMDDHH24MISS') AS "EventTime",
    U.NAME_TEXT AS "User",
    H.BUKRS AS "CompanyCode",
    H.BLART AS "DocumentType",
    H.BUDAT AS "PostingDate",
    H.TCODE AS "TransactionCode",
    TRUE AS "IsReversed"
FROM DOC_HEADERS H
LEFT JOIN V_USERNAME U ON H.USNAM = U.BNAME
WHERE H.STBLG IS NOT NULL AND H.STBLG <> ''

UNION ALL

-- Event 6: Journal Entry Line Item Cleared
SELECT 
    CONCAT(B.BUKRS, B.BELNR, B.GJAHR) AS "JournalEntryId",
    'Journal Entry Line Item Cleared' AS "ActivityName",
    TO_TIMESTAMP(B.AUGDT, 'YYYYMMDD') AS "EventTime", -- Clearing date used as event time
    U.NAME_TEXT AS "User",
    B.BUKRS AS "CompanyCode",
    H.BLART AS "DocumentType",
    H.BUDAT AS "PostingDate",
    NULL AS "TransactionCode", -- Clearing transaction is in the clearing document header, complex to retrieve here
    CASE WHEN H.STBLG IS NOT NULL AND H.STBLG <> '' THEN TRUE ELSE FALSE END AS "IsReversed"
FROM BSEG B
JOIN DOC_HEADERS H ON B.BUKRS = H.BUKRS AND B.BELNR = H.BELNR AND B.GJAHR = H.GJAHR
LEFT JOIN V_USERNAME U ON H.USNAM = U.BNAME
WHERE B.AUGBL IS NOT NULL AND B.AUGBL <> '' AND B.AUGDT <> '00000000'

UNION ALL

-- Event 7: Journal Entry Corrected (changes to a parked document)
SELECT
    CONCAT(H.BUKRS, H.BELNR, H.GJAHR) AS "JournalEntryId",
    'Journal Entry Corrected' AS "ActivityName",
    TO_TIMESTAMP(CONCAT(C.UDATE, C.UTIME), 'YYYYMMDDHH24MISS') AS "EventTime",
    U.NAME_TEXT AS "User",
    H.BUKRS AS "CompanyCode",
    H.BLART AS "DocumentType",
    H.BUDAT AS "PostingDate",
    C.TCODE AS "TransactionCode",
    FALSE AS "IsReversed"
FROM DOC_HEADERS H
JOIN CDHDR C ON C.OBJECTCLAS = 'BELEG' AND C.OBJECTID = CONCAT(H.BUKRS, H.BELNR, H.GJAHR)
LEFT JOIN V_USERNAME U ON C.USERNAME = U.BNAME
WHERE H.BSTAT = 'V' AND C.TCODE IN ('FBV2', 'FBV4') -- FBV2 is change parked doc, FBV4 is change parked doc header

UNION ALL

-- Event 8: Documentation Attached (inferred from GOS attachment creation, requires configuration)
SELECT
  CONCAT(H.BUKRS, H.BELNR, H.GJAHR) AS "JournalEntryId",
  'Documentation Attached' AS "ActivityName",
  TO_TIMESTAMP(CONCAT(REL.RECDATE, '000000'), 'YYYYMMDDHH24MISS') AS "EventTime",
  U.NAME_TEXT AS "User",
  H.BUKRS AS "CompanyCode",
  H.BLART AS "DocumentType",
  H.BUDAT AS "PostingDate",
  H.TCODE AS "TransactionCode",
  FALSE AS "IsReversed"
FROM DOC_HEADERS H
JOIN SRGBTBREL REL ON REL.INSTID_A = CONCAT('BUS2081', H.BUKRS, H.BELNR, H.GJAHR) -- BUS2081 is object type for BKPF
LEFT JOIN V_USERNAME U ON REL.RECUNAM = U.BNAME
WHERE REL.TYPEID_A = 'BUS2081' AND REL.RELTYPE = 'ATTA'

UNION ALL

-- Events 9-13 from Workflow (Submitted, Changes Requested, Approved, Rejected) requires specific workflow config
-- This is a generic template. The WI_RH_TASK must be adapted to your system.
SELECT
  CONCAT(H.BUKRS, H.BELNR, H.GJAHR) AS "JournalEntryId",
  CASE 
    WHEN LOG.WI_RH_TASK = '[WF_TASK_ID_SUBMIT]' THEN 'Journal Entry Submitted'
    WHEN LOG.WI_RH_TASK = '[WF_TASK_ID_APPROVE]' AND LOG.METHOD = 'DECISION' AND LOG.EVT_ID = 'COMPLETED' THEN 'Journal Entry Approved'
    WHEN LOG.WI_RH_TASK = '[WF_TASK_ID_REJECT]' AND LOG.METHOD = 'DECISION' AND LOG.EVT_ID = 'COMPLETED' THEN 'Journal Entry Rejected'
    WHEN LOG.WI_RH_TASK = '[WF_TASK_ID_CHANGES_REQ]' AND LOG.METHOD = 'DECISION' AND LOG.EVT_ID = 'COMPLETED' THEN 'Journal Entry Changes Requested'
    ELSE NULL 
  END AS "ActivityName",
  TO_TIMESTAMP(CONCAT(LOG.EVT_DATE, LOG.EVT_TIME), 'YYYYMMDDHH24MISS') AS "EventTime",
  U.NAME_TEXT AS "User",
  H.BUKRS AS "CompanyCode",
  H.BLART AS "DocumentType",
  H.BUDAT AS "PostingDate",
  NULL AS "TransactionCode",
  FALSE AS "IsReversed"
FROM DOC_HEADERS H
JOIN SWW_WI2OBJ WIOBJ ON WIOBJ.INSTID = CONCAT(H.BUKRS, H.BELNR, H.GJAHR) AND WIOBJ.TYPEID = 'BKPF'
JOIN SWWLOGHIST LOG ON WIOBJ.WI_ID = LOG.WI_ID
LEFT JOIN V_USERNAME U ON LOG.EXEC_USER = U.BNAME
WHERE LOG.WI_RH_TASK IN ('[WF_TASK_ID_SUBMIT]', '[WF_TASK_ID_APPROVE]', '[WF_TASK_ID_REJECT]', '[WF_TASK_ID_CHANGES_REQ]')

UNION ALL

-- Event 14: Manual Entry Identified
SELECT
  CONCAT(H.BUKRS, H.BELNR, H.GJAHR) AS "JournalEntryId",
  'Manual Entry Identified' AS "ActivityName",
  TO_TIMESTAMP(CONCAT(H.CPUDT, H.CPUTM), 'YYYYMMDDHH24MISS') AS "EventTime",
  U.NAME_TEXT AS "User",
  H.BUKRS AS "CompanyCode",
  H.BLART AS "DocumentType",
  H.BUDAT AS "PostingDate",
  H.TCODE AS "TransactionCode",
  CASE WHEN H.STBLG IS NOT NULL AND H.STBLG <> '' THEN TRUE ELSE FALSE END AS "IsReversed"
FROM DOC_HEADERS H
LEFT JOIN V_USERNAME U ON H.USNAM = U.BNAME
WHERE H.TCODE IN ('FB01', 'F-02', 'FB50', 'F-04', 'F-22', 'F-43', 'FB60', 'FB70', 'FV50', 'FV60', 'FV70')

UNION ALL

-- Event 15: Cross-Company Posting Identified
SELECT
  CONCAT(H.BUKRS, H.BELNR, H.GJAHR) AS "JournalEntryId",
  'Cross-Company Posting Identified' AS "ActivityName",
  TO_TIMESTAMP(CONCAT(H.CPUDT, H.CPUTM), 'YYYYMMDDHH24MISS') AS "EventTime",
  U.NAME_TEXT AS "User",
  H.BUKRS AS "CompanyCode",
  H.BLART AS "DocumentType",
  H.BUDAT AS "PostingDate",
  H.TCODE AS "TransactionCode",
  CASE WHEN H.STBLG IS NOT NULL AND H.STBLG <> '' THEN TRUE ELSE FALSE END AS "IsReversed"
FROM DOC_HEADERS H
LEFT JOIN V_USERNAME U ON H.USNAM = U.BNAME
WHERE H.XRECH = 'X'

/*
 This is a logical representation of the extraction configuration in a third-party ETL tool.
 It is not executable SQL but defines the sources, joins, and transformations for each activity.
 Placeholders like [Your SAP Source], [Date Filter], and [Company Code Filter] must be configured in the tool.
*/

-- Extraction block for 'Journal Entry Parked'
SELECT
  CONCAT(v.BUKRS, v.VBELN, v.GJAHR) AS JournalEntryId,
  'Journal Entry Parked' AS ActivityName,
  CAST(CONCAT(v.CPUDT, v.CPUTM) AS TIMESTAMP) AS EventTime,
  'SAP ECC' AS SourceSystem,
  NOW() AS LastDataUpdate,
  v.USNAM AS User,
  v.BUKRS AS CompanyCode,
  v.BLART AS DocumentType,
  v.BUDAT AS PostingDate,
  v.TCODE AS TransactionCode,
  FALSE AS IsReversed
FROM [Your SAP Source].VBSEGK v
WHERE [Date Filter on v.CPUDT] AND [Company Code Filter on v.BUKRS]

UNION ALL

-- Extraction block for 'Journal Entry Created'
SELECT
  CONCAT(h.BUKRS, h.BELNR, h.GJAHR) AS JournalEntryId,
  'Journal Entry Created' AS ActivityName,
  CAST(CONCAT(h.CPUDT, h.CPUTM) AS TIMESTAMP) AS EventTime,
  'SAP ECC' AS SourceSystem,
  NOW() AS LastDataUpdate,
  h.USNAM AS User,
  h.BUKRS AS CompanyCode,
  h.BLART AS DocumentType,
  h.BUDAT AS PostingDate,
  h.TCODE AS TransactionCode,
  FALSE AS IsReversed
FROM [Your SAP Source].BKPF h
LEFT JOIN [Your SAP Source].VBSEGK v ON h.AWKEY = CONCAT(v.BUKRS, v.VBELN, v.GJAHR)
WHERE h.BSTAT = '' AND v.VBELN IS NULL AND h.STBLG IS NULL
  AND [Date Filter on h.CPUDT] AND [Company Code Filter on h.BUKRS]

UNION ALL

-- Extraction block for 'Journal Entry Posted' (from parked)
SELECT
  CONCAT(h.BUKRS, h.BELNR, h.GJAHR) AS JournalEntryId,
  'Journal Entry Posted' AS ActivityName,
  CAST(CONCAT(h.CPUDT, h.CPUTM) AS TIMESTAMP) AS EventTime, -- Or a more precise posting time from change logs if available
  'SAP ECC' AS SourceSystem,
  NOW() AS LastDataUpdate,
  h.USNAM AS User,
  h.BUKRS AS CompanyCode,
  h.BLART AS DocumentType,
  h.BUDAT AS PostingDate,
  h.TCODE AS TransactionCode,
  FALSE AS IsReversed
FROM [Your SAP Source].BKPF h
JOIN [Your SAP Source].VBSEGK v ON h.AWKEY = CONCAT(v.BUKRS, v.VBELN, v.GJAHR)
WHERE [Date Filter on h.CPUDT] AND [Company Code Filter on h.BUKRS]

UNION ALL

-- Extraction block for 'Journal Entry Submitted', 'Approved', 'Rejected', 'Changes Requested'
SELECT
  CONCAT(SUBSTRING(o.INSTID, 3, 4), SUBSTRING(o.INSTID, 7, 10), SUBSTRING(o.INSTID, 17, 4)) AS JournalEntryId,
  CASE 
    WHEN wl.WI_TEXT LIKE '%Submit%' THEN 'Journal Entry Submitted'
    WHEN wl.WI_TEXT LIKE '%Approve%' THEN 'Journal Entry Approved'
    WHEN wl.WI_TEXT LIKE '%Reject%' THEN 'Journal Entry Rejected'
    WHEN wl.WI_TEXT LIKE '%Request Changes%' THEN 'Journal Entry Changes Requested'
  END AS ActivityName,
  CAST(CONCAT(wl.WI_CD, wl.WI_CT) AS TIMESTAMP) AS EventTime,
  'SAP ECC' AS SourceSystem,
  NOW() AS LastDataUpdate,
  wl.EXEC_USER AS User,
  SUBSTRING(o.INSTID, 3, 4) AS CompanyCode,
  NULL AS DocumentType,
  NULL AS PostingDate,
  NULL AS TransactionCode,
  FALSE AS IsReversed
FROM [Your SAP Source].SWW_WI2OBJ o
JOIN [Your SAP Source].SWWLOGHIST wl ON o.WI_ID = wl.WI_ID
WHERE o.TYPEID = 'BKPF' AND o.CATID = 'BO'
  AND wl.WI_TEXT IN ('[Your Submit Task Name]', '[Your Approve Task Name]', '[Your Reject Task Name]', '[Your Changes Request Task Name]')
  AND [Date Filter on wl.WI_CD]

UNION ALL

-- Extraction block for 'Journal Entry Corrected'
SELECT
  CONCAT(cd.OBJECTID_LONG_CHAR(3,4), cd.OBJECTID_LONG_CHAR(7,10), cd.OBJECTID_LONG_CHAR(17,4)) AS JournalEntryId,
  'Journal Entry Corrected' AS ActivityName,
  CAST(CONCAT(cd.UDATE, cd.UTIME) AS TIMESTAMP) AS EventTime,
  'SAP ECC' AS SourceSystem,
  NOW() AS LastDataUpdate,
  cd.USERNAME AS User,
  cd.OBJECTID_LONG_CHAR(3,4) AS CompanyCode,
  NULL AS DocumentType,
  NULL AS PostingDate,
  cd.TCODE AS TransactionCode,
  FALSE AS IsReversed
FROM [Your SAP Source].CDHDR cd
WHERE cd.OBJECTCLAS = 'FIPP' AND cd.CHANGE_IND = 'U'
  AND [Date Filter on cd.UDATE]

UNION ALL

-- Extraction block for 'Parked Journal Entry Deleted'
SELECT
  CONCAT(cd.OBJECTID_LONG_CHAR(3,4), cd.OBJECTID_LONG_CHAR(7,10), cd.OBJECTID_LONG_CHAR(17,4)) AS JournalEntryId,
  'Parked Journal Entry Deleted' AS ActivityName,
  CAST(CONCAT(cd.UDATE, cd.UTIME) AS TIMESTAMP) AS EventTime,
  'SAP ECC' AS SourceSystem,
  NOW() AS LastDataUpdate,
  cd.USERNAME AS User,
  cd.OBJECTID_LONG_CHAR(3,4) AS CompanyCode,
  NULL AS DocumentType,
  NULL AS PostingDate,
  cd.TCODE AS TransactionCode,
  FALSE AS IsReversed
FROM [Your SAP Source].CDHDR cd
WHERE cd.OBJECTCLAS = 'FIPP' AND cd.CHANGE_IND = 'D'
  AND [Date Filter on cd.UDATE]

UNION ALL

-- Extraction block for 'Documentation Attached'
SELECT
  CONCAT(SUBSTRING(r.INSTID_A, 3, 4), SUBSTRING(r.INSTID_A, 7, 10), SUBSTRING(r.INSTID_A, 17, 4)) AS JournalEntryId,
  'Documentation Attached' AS ActivityName,
  -- Note: A precise timestamp is often unavailable. Using document creation time as a proxy.
  CAST(CONCAT(h.CPUDT, h.CPUTM) AS TIMESTAMP) AS EventTime,
  'SAP ECC' AS SourceSystem,
  NOW() AS LastDataUpdate,
  h.USNAM AS User,
  h.BUKRS AS CompanyCode,
  h.BLART AS DocumentType,
  h.BUDAT AS PostingDate,
  h.TCODE AS TransactionCode,
  FALSE AS IsReversed
FROM [Your SAP Source].SRGBTBREL r
JOIN [Your SAP Source].BKPF h ON h.BUKRS = SUBSTRING(r.INSTID_A, 3, 4) AND h.BELNR = SUBSTRING(r.INSTID_A, 7, 10) AND h.GJAHR = SUBSTRING(r.INSTID_A, 17, 4)
WHERE r.TYPEID_A = 'BKPF' AND r.RELTYPE = '[Configure based on your system]'
  AND [Date Filter on h.CPUDT] AND [Company Code Filter on h.BUKRS]

UNION ALL

-- Extraction block for 'Journal Entry Reversal Processed'
SELECT
  CONCAT(h.BUKRS, h.BELNR, h.GJAHR) AS JournalEntryId,
  'Journal Entry Reversal Processed' AS ActivityName,
  CAST(CONCAT(h.CPUDT, h.CPUTM) AS TIMESTAMP) AS EventTime,
  'SAP ECC' AS SourceSystem,
  NOW() AS LastDataUpdate,
  h.USNAM AS User,
  h.BUKRS AS CompanyCode,
  h.BLART AS DocumentType,
  h.BUDAT AS PostingDate,
  h.TCODE AS TransactionCode,
  TRUE AS IsReversed
FROM [Your SAP Source].BKPF h
WHERE h.STBLG IS NOT NULL AND h.STBLG <> ''
  AND [Date Filter on h.CPUDT] AND [Company Code Filter on h.BUKRS]

UNION ALL

-- Extraction block for 'Is Reversed' flag on original document
SELECT
  CONCAT(h_orig.BUKRS, h_orig.BELNR, h_orig.GJAHR) AS JournalEntryId,
  'Is Reversed' AS ActivityName, -- This is an attribute update, modeled as an event
  CAST(CONCAT(h_rev.CPUDT, h_rev.CPUTM) AS TIMESTAMP) AS EventTime,
  'SAP ECC' AS SourceSystem,
  NOW() AS LastDataUpdate,
  h_rev.USNAM AS User,
  h_orig.BUKRS AS CompanyCode,
  h_orig.BLART AS DocumentType,
  h_orig.BUDAT AS PostingDate,
  h_orig.TCODE AS TransactionCode,
  TRUE AS IsReversed
FROM [Your SAP Source].BKPF h_rev
JOIN [Your SAP Source].BKPF h_orig ON h_rev.STBLG = h_orig.BELNR AND h_rev.BUKRS = h_orig.BUKRS AND h_rev.GJAHR_S = h_orig.GJAHR
WHERE h_rev.STBLG IS NOT NULL AND h_rev.STBLG <> ''
  AND [Date Filter on h_rev.CPUDT] AND [Company Code Filter on h_rev.BUKRS]

UNION ALL

-- Extraction block for 'Journal Entry Line Item Cleared'
SELECT
  CONCAT(i.BUKRS, i.BELNR, i.GJAHR) AS JournalEntryId,
  'Journal Entry Line Item Cleared' AS ActivityName,
  CAST(i.AUGDT AS TIMESTAMP) AS EventTime,
  'SAP ECC' AS SourceSystem,
  NOW() AS LastDataUpdate,
  NULL AS User, -- User who performed clearing is on the clearing document header
  i.BUKRS AS CompanyCode,
  NULL AS DocumentType,
  NULL AS PostingDate,
  NULL AS TransactionCode,
  FALSE AS IsReversed
FROM [Your SAP Source].BSEG i
WHERE i.AUGBL IS NOT NULL AND i.AUGBL <> ''
  AND [Date Filter on i.AUGDT] AND [Company Code Filter on i.BUKRS]

UNION ALL

-- Extraction block for 'Manual Entry Identified'
SELECT
  CONCAT(h.BUKRS, h.BELNR, h.GJAHR) AS JournalEntryId,
  'Manual Entry Identified' AS ActivityName,
  CAST(CONCAT(h.CPUDT, h.CPUTM) AS TIMESTAMP) AS EventTime, -- Same time as creation
  'SAP ECC' AS SourceSystem,
  NOW() AS LastDataUpdate,
  h.USNAM AS User,
  h.BUKRS AS CompanyCode,
  h.BLART AS DocumentType,
  h.BUDAT AS PostingDate,
  h.TCODE AS TransactionCode,
  FALSE AS IsReversed
FROM [Your SAP Source].BKPF h
WHERE h.TCODE IN ('FB01', 'F-02', 'FB50', 'FV50', '[Add other manual T-Codes]')
  AND [Date Filter on h.CPUDT] AND [Company Code Filter on h.BUKRS]

UNION ALL

-- Extraction block for 'Cross-Company Posting Identified'
SELECT
  JournalEntryId,
  'Cross-Company Posting Identified' AS ActivityName,
  EventTime, -- Same time as creation
  'SAP ECC' AS SourceSystem,
  NOW() AS LastDataUpdate,
  User,
  CompanyCode,
  DocumentType,
  PostingDate,
  TransactionCode,
  IsReversed
FROM (
  SELECT
    CONCAT(h.BUKRS, h.BELNR, h.GJAHR) AS JournalEntryId,
    CAST(CONCAT(h.CPUDT, h.CPUTM) AS TIMESTAMP) AS EventTime,
    h.USNAM AS User,
    h.BUKRS AS CompanyCode,
    h.BLART AS DocumentType,
    h.BUDAT AS PostingDate,
    h.TCODE AS TransactionCode,
    FALSE AS IsReversed,
    (SELECT COUNT(DISTINCT i.BUKRS) FROM [Your SAP Source].BSEG i WHERE i.BELNR = h.BELNR AND i.BUKRS = h.BUKRS AND i.GJAHR = h.GJAHR) as CompanyCodeCount
  FROM [Your SAP Source].BKPF h
  WHERE [Date Filter on h.CPUDT] AND [Company Code Filter on h.BUKRS]
) AS CrossCompanyCheck
WHERE CompanyCodeCount > 1