Muhasebe Kayıtlarından Raporlamaya - Yevmiye Kaydı Veri Template'inuz

WITH JournalEntryAmountCTE AS (
    SELECT
        CompanyCode,
        AccountingDocument,
        FiscalYear,
        SUM(AmountInCompanyCodeCurrency) AS AmountInLocalCurrency
    FROM I_JournalEntryItem
    GROUP BY CompanyCode, AccountingDocument, FiscalYear
),
JournalEntryBaseCTE AS (
    SELECT
        JE.CompanyCode,
        JE.AccountingDocument,
        JE.FiscalYear,
        JE.CreatedByUser,
        JE.CreationDateTime,
        JE.PostingDateTime,
        JE.PostingDate,
        JE.AccountingDocumentType,
        JE.DocumentIsParked,
        JE.ReversedJournalEntry,
        JE.TransactionCode,
        JEA.AmountInLocalCurrency
    FROM I_JournalEntry AS JE
    LEFT JOIN JournalEntryAmountCTE AS JEA
        ON JE.CompanyCode = JEA.CompanyCode
        AND JE.AccountingDocument = JEA.AccountingDocument
        AND JE.FiscalYear = JEA.FiscalYear
    WHERE JE.CompanyCode IN ('[YourCompanyCode]')
      AND JE.CreationDateTime BETWEEN '[StartDate]' AND '[EndDate]'
)
-- 1. Journal Entry Created
SELECT
    BJE.AccountingDocument AS "JournalEntryId",
    'Journal Entry Created' AS "ActivityName",
    BJE.CreationDateTime AS "EventTime",
    BJE.CreatedByUser AS "CreatedByUser",
    BJE.CompanyCode AS "CompanyCode",
    BJE.AccountingDocumentType AS "JournalEntryType",
    BJE.PostingDate AS "PostingDate",
    BJE.AmountInLocalCurrency AS "AmountInLocalCurrency"
FROM JournalEntryBaseCTE AS BJE

UNION ALL

-- 2. Journal Entry Parked
SELECT
    BJE.AccountingDocument AS "JournalEntryId",
    'Journal Entry Parked' AS "ActivityName",
    BJE.CreationDateTime AS "EventTime",
    BJE.CreatedByUser AS "CreatedByUser",
    BJE.CompanyCode AS "CompanyCode",
    BJE.AccountingDocumentType AS "JournalEntryType",
    BJE.PostingDate AS "PostingDate",
    BJE.AmountInLocalCurrency AS "AmountInLocalCurrency"
FROM JournalEntryBaseCTE AS BJE
WHERE BJE.DocumentIsParked = 'X'

UNION ALL

-- 3. Supporting Documentation Attached
SELECT
    BJE.AccountingDocument AS "JournalEntryId",
    'Supporting Documentation Attached' AS "ActivityName",
    TO_TIMESTAMP(SOOD.CREDAT || ' ' || SOOD.CRETIM, 'YYYYMMDD HH24MISS') AS "EventTime",
    SOOD.OWNER AS "CreatedByUser",
    BJE.CompanyCode AS "CompanyCode",
    BJE.AccountingDocumentType AS "JournalEntryType",
    BJE.PostingDate AS "PostingDate",
    BJE.AmountInLocalCurrency AS "AmountInLocalCurrency"
FROM JournalEntryBaseCTE AS BJE
JOIN SRGBREL ON SRGBREL.INSTID_A = CONCAT(BJE.CompanyCode, BJE.AccountingDocument, BJE.FiscalYear)
    AND SRGBREL.TYPEID_A = 'BKPF'
    AND SRGBREL.CATID_A = 'BO'
JOIN SOOD ON SOOD.OBJTP = SRGBREL.TYPEID_B
    AND SOOD.OBJYR = SRGBREL.INSTID_B(3)
    AND SOOD.OBJNO = SRGBREL.INSTID_B(5)

UNION ALL

-- 4. Journal Submitted For Review
SELECT
    BJE.AccountingDocument AS "JournalEntryId",
    'Journal Submitted For Review' AS "ActivityName",
    LOG.END_TS AS "EventTime",
    LOG.EXEC_USER AS "CreatedByUser",
    BJE.CompanyCode AS "CompanyCode",
    BJE.AccountingDocumentType AS "JournalEntryType",
    BJE.PostingDate AS "PostingDate",
    BJE.AmountInLocalCurrency AS "AmountInLocalCurrency"
FROM JournalEntryBaseCTE AS BJE
JOIN SWW_WI2OBJ AS WF_LINK ON WF_LINK.INSTID = CONCAT(BJE.CompanyCode, BJE.AccountingDocument, BJE.FiscalYear)
    AND WF_LINK.TYPEID = 'BKPF'
JOIN SWWLOGHIST AS LOG ON LOG.WI_ID = WF_LINK.WI_ID
WHERE LOG.METHOD = '[Workflow Submitted Task ID]'

UNION ALL

-- 5. Journal Entry Rejected
SELECT
    BJE.AccountingDocument AS "JournalEntryId",
    'Journal Entry Rejected' AS "ActivityName",
    LOG.END_TS AS "EventTime",
    LOG.EXEC_USER AS "CreatedByUser",
    BJE.CompanyCode AS "CompanyCode",
    BJE.AccountingDocumentType AS "JournalEntryType",
    BJE.PostingDate AS "PostingDate",
    BJE.AmountInLocalCurrency AS "AmountInLocalCurrency"
FROM JournalEntryBaseCTE AS BJE
JOIN SWW_WI2OBJ AS WF_LINK ON WF_LINK.INSTID = CONCAT(BJE.CompanyCode, BJE.AccountingDocument, BJE.FiscalYear)
    AND WF_LINK.TYPEID = 'BKPF'
JOIN SWWLOGHIST AS LOG ON LOG.WI_ID = WF_LINK.WI_ID
WHERE LOG.METHOD = '[Workflow Rejected Task ID]'

UNION ALL

-- 6. Journal Entry Corrected (changed while parked)
SELECT DISTINCT
    BJE.AccountingDocument AS "JournalEntryId",
    'Journal Entry Corrected' AS "ActivityName",
    TO_TIMESTAMP(CH.UDATE || ' ' || CH.UTIME, 'YYYYMMDD HH24MISS') AS "EventTime",
    CH.USERNAME AS "CreatedByUser",
    BJE.CompanyCode AS "CompanyCode",
    BJE.AccountingDocumentType AS "JournalEntryType",
    BJE.PostingDate AS "PostingDate",
    BJE.AmountInLocalCurrency AS "AmountInLocalCurrency"
FROM JournalEntryBaseCTE AS BJE
JOIN CDHDR AS CH ON CH.OBJECTID = CONCAT(BJE.CompanyCode, BJE.AccountingDocument, BJE.FiscalYear)
    AND CH.OBJECTCLASS = 'BELEG'
WHERE BJE.DocumentIsParked = 'X'

UNION ALL

-- 7. Journal Entry Approved
SELECT
    BJE.AccountingDocument AS "JournalEntryId",
    'Journal Entry Approved' AS "ActivityName",
    LOG.END_TS AS "EventTime",
    LOG.EXEC_USER AS "CreatedByUser",
    BJE.CompanyCode AS "CompanyCode",
    BJE.AccountingDocumentType AS "JournalEntryType",
    BJE.PostingDate AS "PostingDate",
    BJE.AmountInLocalCurrency AS "AmountInLocalCurrency"
FROM JournalEntryBaseCTE AS BJE
JOIN SWW_WI2OBJ AS WF_LINK ON WF_LINK.INSTID = CONCAT(BJE.CompanyCode, BJE.AccountingDocument, BJE.FiscalYear)
    AND WF_LINK.TYPEID = 'BKPF'
JOIN SWWLOGHIST AS LOG ON LOG.WI_ID = WF_LINK.WI_ID
WHERE LOG.METHOD = '[Workflow Approved Task ID]'

UNION ALL

-- 8. Manual Posting Identified
SELECT
    BJE.AccountingDocument AS "JournalEntryId",
    'Manual Posting Identified' AS "ActivityName",
    BJE.PostingDateTime AS "EventTime",
    BJE.CreatedByUser AS "CreatedByUser",
    BJE.CompanyCode AS "CompanyCode",
    BJE.AccountingDocumentType AS "JournalEntryType",
    BJE.PostingDate AS "PostingDate",
    BJE.AmountInLocalCurrency AS "AmountInLocalCurrency"
FROM JournalEntryBaseCTE AS BJE
WHERE BJE.PostingDateTime IS NOT NULL AND BJE.TransactionCode IN ('FB01', 'F-02', 'FB50', 'FV50', 'FBB1', 'FBV1')

UNION ALL

-- 9. Journal Entry Posted
SELECT
    BJE.AccountingDocument AS "JournalEntryId",
    'Journal Entry Posted' AS "ActivityName",
    BJE.PostingDateTime AS "EventTime",
    BJE.CreatedByUser AS "CreatedByUser",
    BJE.CompanyCode AS "CompanyCode",
    BJE.AccountingDocumentType AS "JournalEntryType",
    BJE.PostingDate AS "PostingDate",
    BJE.AmountInLocalCurrency AS "AmountInLocalCurrency"
FROM JournalEntryBaseCTE AS BJE
WHERE BJE.PostingDateTime IS NOT NULL

UNION ALL

-- 10. Journal Entry Changed After Posting
SELECT DISTINCT
    BJE.AccountingDocument AS "JournalEntryId",
    'Journal Entry Changed After Posting' AS "ActivityName",
    TO_TIMESTAMP(CH.UDATE || ' ' || CH.UTIME, 'YYYYMMDD HH24MISS') AS "EventTime",
    CH.USERNAME AS "CreatedByUser",
    BJE.CompanyCode AS "CompanyCode",
    BJE.AccountingDocumentType AS "JournalEntryType",
    BJE.PostingDate AS "PostingDate",
    BJE.AmountInLocalCurrency AS "AmountInLocalCurrency"
FROM JournalEntryBaseCTE AS BJE
JOIN CDHDR AS CH ON CH.OBJECTID = CONCAT(BJE.CompanyCode, BJE.AccountingDocument, BJE.FiscalYear)
    AND CH.OBJECTCLASS = 'BELEG'
WHERE BJE.PostingDateTime IS NOT NULL AND TO_TIMESTAMP(CH.UDATE || ' ' || CH.UTIME, 'YYYYMMDD HH24MISS') > BJE.PostingDateTime

UNION ALL

-- 11. Journal Entry Cleared
SELECT
    JEI.AccountingDocument AS "JournalEntryId",
    'Journal Entry Cleared' AS "ActivityName",
    MIN(JEI.ClearingDateTime) AS "EventTime",
    BJE.CreatedByUser AS "CreatedByUser", -- Note: Clearing user is not directly available here
    BJE.CompanyCode AS "CompanyCode",
    BJE.AccountingDocumentType AS "JournalEntryType",
    BJE.PostingDate AS "PostingDate",
    BJE.AmountInLocalCurrency AS "AmountInLocalCurrency"
FROM I_JournalEntryItem AS JEI
JOIN JournalEntryBaseCTE AS BJE ON JEI.AccountingDocument = BJE.AccountingDocument
    AND JEI.CompanyCode = BJE.CompanyCode
    AND JEI.FiscalYear = BJE.FiscalYear
WHERE JEI.ClearingDateTime IS NOT NULL
GROUP BY JEI.AccountingDocument, BJE.CreatedByUser, BJE.CompanyCode, BJE.AccountingDocumentType, BJE.PostingDate, BJE.AmountInLocalCurrency

UNION ALL

-- 12. Journal Entry Reversal Processed
SELECT
    OriginalDoc.AccountingDocument AS "JournalEntryId",
    'Journal Entry Reversal Processed' AS "ActivityName",
    ReversalDoc.PostingDateTime AS "EventTime",
    ReversalDoc.CreatedByUser AS "CreatedByUser",
    OriginalDoc.CompanyCode AS "CompanyCode",
    OriginalDoc.AccountingDocumentType AS "JournalEntryType",
    OriginalDoc.PostingDate AS "PostingDate",
    OriginalDoc.AmountInLocalCurrency AS "AmountInLocalCurrency"
FROM JournalEntryBaseCTE AS ReversalDoc
JOIN JournalEntryBaseCTE AS OriginalDoc
    ON ReversalDoc.ReversedJournalEntry = OriginalDoc.AccountingDocument
    AND ReversalDoc.CompanyCode = OriginalDoc.CompanyCode
    AND ReversalDoc.ReversalFiscalYear = OriginalDoc.FiscalYear
WHERE ReversalDoc.ReversedJournalEntry IS NOT NULL;

WITH JournalEntryAmountCTE AS (
    SELECT
        CompanyCode,
        AccountingDocument,
        FiscalYear,
        SUM(AmountInCompanyCodeCurrency) AS AmountInLocalCurrency
    FROM I_JournalEntryItem
    GROUP BY CompanyCode, AccountingDocument, FiscalYear
),
JournalEntryBaseCTE AS (
    SELECT
        JE.CompanyCode,
        JE.AccountingDocument,
        JE.FiscalYear,
        JE.CreatedByUser,
        JE.CreationDateTime,
        JE.PostingDateTime,
        JE.PostingDate,
        JE.AccountingDocumentType,
        JE.DocumentIsParked,
        JE.ReversedJournalEntry,
        JE.TransactionCode,
        JEA.AmountInLocalCurrency
    FROM I_JournalEntry AS JE
    LEFT JOIN JournalEntryAmountCTE AS JEA
        ON JE.CompanyCode = JEA.CompanyCode
        AND JE.AccountingDocument = JEA.AccountingDocument
        AND JE.FiscalYear = JEA.FiscalYear
    WHERE JE.CompanyCode IN ('[YourCompanyCode]')
      AND JE.CreationDateTime BETWEEN '[StartDate]' AND '[EndDate]'
)
-- 1. Journal Entry Created
SELECT
    BJE.AccountingDocument AS "JournalEntryId",
    'Journal Entry Created' AS "ActivityName",
    BJE.CreationDateTime AS "EventTime",
    BJE.CreatedByUser AS "CreatedByUser",
    BJE.CompanyCode AS "CompanyCode",
    BJE.AccountingDocumentType AS "JournalEntryType",
    BJE.PostingDate AS "PostingDate",
    BJE.AmountInLocalCurrency AS "AmountInLocalCurrency"
FROM JournalEntryBaseCTE AS BJE

UNION ALL

-- 2. Journal Entry Parked
SELECT
    BJE.AccountingDocument AS "JournalEntryId",
    'Journal Entry Parked' AS "ActivityName",
    BJE.CreationDateTime AS "EventTime",
    BJE.CreatedByUser AS "CreatedByUser",
    BJE.CompanyCode AS "CompanyCode",
    BJE.AccountingDocumentType AS "JournalEntryType",
    BJE.PostingDate AS "PostingDate",
    BJE.AmountInLocalCurrency AS "AmountInLocalCurrency"
FROM JournalEntryBaseCTE AS BJE
WHERE BJE.DocumentIsParked = 'X'

UNION ALL

-- 3. Supporting Documentation Attached
SELECT
    BJE.AccountingDocument AS "JournalEntryId",
    'Supporting Documentation Attached' AS "ActivityName",
    TO_TIMESTAMP(SOOD.CREDAT || ' ' || SOOD.CRETIM, 'YYYYMMDD HH24MISS') AS "EventTime",
    SOOD.OWNER AS "CreatedByUser",
    BJE.CompanyCode AS "CompanyCode",
    BJE.AccountingDocumentType AS "JournalEntryType",
    BJE.PostingDate AS "PostingDate",
    BJE.AmountInLocalCurrency AS "AmountInLocalCurrency"
FROM JournalEntryBaseCTE AS BJE
JOIN SRGBREL ON SRGBREL.INSTID_A = CONCAT(BJE.CompanyCode, BJE.AccountingDocument, BJE.FiscalYear)
    AND SRGBREL.TYPEID_A = 'BKPF'
    AND SRGBREL.CATID_A = 'BO'
JOIN SOOD ON SOOD.OBJTP = SRGBREL.TYPEID_B
    AND SOOD.OBJYR = SRGBREL.INSTID_B(3)
    AND SOOD.OBJNO = SRGBREL.INSTID_B(5)

UNION ALL

-- 4. Journal Submitted For Review
SELECT
    BJE.AccountingDocument AS "JournalEntryId",
    'Journal Submitted For Review' AS "ActivityName",
    LOG.END_TS AS "EventTime",
    LOG.EXEC_USER AS "CreatedByUser",
    BJE.CompanyCode AS "CompanyCode",
    BJE.AccountingDocumentType AS "JournalEntryType",
    BJE.PostingDate AS "PostingDate",
    BJE.AmountInLocalCurrency AS "AmountInLocalCurrency"
FROM JournalEntryBaseCTE AS BJE
JOIN SWW_WI2OBJ AS WF_LINK ON WF_LINK.INSTID = CONCAT(BJE.CompanyCode, BJE.AccountingDocument, BJE.FiscalYear)
    AND WF_LINK.TYPEID = 'BKPF'
JOIN SWWLOGHIST AS LOG ON LOG.WI_ID = WF_LINK.WI_ID
WHERE LOG.METHOD = '[Workflow Submitted Task ID]'

UNION ALL

-- 5. Journal Entry Rejected
SELECT
    BJE.AccountingDocument AS "JournalEntryId",
    'Journal Entry Rejected' AS "ActivityName",
    LOG.END_TS AS "EventTime",
    LOG.EXEC_USER AS "CreatedByUser",
    BJE.CompanyCode AS "CompanyCode",
    BJE.AccountingDocumentType AS "JournalEntryType",
    BJE.PostingDate AS "PostingDate",
    BJE.AmountInLocalCurrency AS "AmountInLocalCurrency"
FROM JournalEntryBaseCTE AS BJE
JOIN SWW_WI2OBJ AS WF_LINK ON WF_LINK.INSTID = CONCAT(BJE.CompanyCode, BJE.AccountingDocument, BJE.FiscalYear)
    AND WF_LINK.TYPEID = 'BKPF'
JOIN SWWLOGHIST AS LOG ON LOG.WI_ID = WF_LINK.WI_ID
WHERE LOG.METHOD = '[Workflow Rejected Task ID]'

UNION ALL

-- 6. Journal Entry Corrected (changed while parked)
SELECT DISTINCT
    BJE.AccountingDocument AS "JournalEntryId",
    'Journal Entry Corrected' AS "ActivityName",
    TO_TIMESTAMP(CH.UDATE || ' ' || CH.UTIME, 'YYYYMMDD HH24MISS') AS "EventTime",
    CH.USERNAME AS "CreatedByUser",
    BJE.CompanyCode AS "CompanyCode",
    BJE.AccountingDocumentType AS "JournalEntryType",
    BJE.PostingDate AS "PostingDate",
    BJE.AmountInLocalCurrency AS "AmountInLocalCurrency"
FROM JournalEntryBaseCTE AS BJE
JOIN CDHDR AS CH ON CH.OBJECTID = CONCAT(BJE.CompanyCode, BJE.AccountingDocument, BJE.FiscalYear)
    AND CH.OBJECTCLASS = 'BELEG'
WHERE BJE.DocumentIsParked = 'X'

UNION ALL

-- 7. Journal Entry Approved
SELECT
    BJE.AccountingDocument AS "JournalEntryId",
    'Journal Entry Approved' AS "ActivityName",
    LOG.END_TS AS "EventTime",
    LOG.EXEC_USER AS "CreatedByUser",
    BJE.CompanyCode AS "CompanyCode",
    BJE.AccountingDocumentType AS "JournalEntryType",
    BJE.PostingDate AS "PostingDate",
    BJE.AmountInLocalCurrency AS "AmountInLocalCurrency"
FROM JournalEntryBaseCTE AS BJE
JOIN SWW_WI2OBJ AS WF_LINK ON WF_LINK.INSTID = CONCAT(BJE.CompanyCode, BJE.AccountingDocument, BJE.FiscalYear)
    AND WF_LINK.TYPEID = 'BKPF'
JOIN SWWLOGHIST AS LOG ON LOG.WI_ID = WF_LINK.WI_ID
WHERE LOG.METHOD = '[Workflow Approved Task ID]'

UNION ALL

-- 8. Manual Posting Identified
SELECT
    BJE.AccountingDocument AS "JournalEntryId",
    'Manual Posting Identified' AS "ActivityName",
    BJE.PostingDateTime AS "EventTime",
    BJE.CreatedByUser AS "CreatedByUser",
    BJE.CompanyCode AS "CompanyCode",
    BJE.AccountingDocumentType AS "JournalEntryType",
    BJE.PostingDate AS "PostingDate",
    BJE.AmountInLocalCurrency AS "AmountInLocalCurrency"
FROM JournalEntryBaseCTE AS BJE
WHERE BJE.PostingDateTime IS NOT NULL AND BJE.TransactionCode IN ('FB01', 'F-02', 'FB50', 'FV50', 'FBB1', 'FBV1')

UNION ALL

-- 9. Journal Entry Posted
SELECT
    BJE.AccountingDocument AS "JournalEntryId",
    'Journal Entry Posted' AS "ActivityName",
    BJE.PostingDateTime AS "EventTime",
    BJE.CreatedByUser AS "CreatedByUser",
    BJE.CompanyCode AS "CompanyCode",
    BJE.AccountingDocumentType AS "JournalEntryType",
    BJE.PostingDate AS "PostingDate",
    BJE.AmountInLocalCurrency AS "AmountInLocalCurrency"
FROM JournalEntryBaseCTE AS BJE
WHERE BJE.PostingDateTime IS NOT NULL

UNION ALL

-- 10. Journal Entry Changed After Posting
SELECT DISTINCT
    BJE.AccountingDocument AS "JournalEntryId",
    'Journal Entry Changed After Posting' AS "ActivityName",
    TO_TIMESTAMP(CH.UDATE || ' ' || CH.UTIME, 'YYYYMMDD HH24MISS') AS "EventTime",
    CH.USERNAME AS "CreatedByUser",
    BJE.CompanyCode AS "CompanyCode",
    BJE.AccountingDocumentType AS "JournalEntryType",
    BJE.PostingDate AS "PostingDate",
    BJE.AmountInLocalCurrency AS "AmountInLocalCurrency"
FROM JournalEntryBaseCTE AS BJE
JOIN CDHDR AS CH ON CH.OBJECTID = CONCAT(BJE.CompanyCode, BJE.AccountingDocument, BJE.FiscalYear)
    AND CH.OBJECTCLASS = 'BELEG'
WHERE BJE.PostingDateTime IS NOT NULL AND TO_TIMESTAMP(CH.UDATE || ' ' || CH.UTIME, 'YYYYMMDD HH24MISS') > BJE.PostingDateTime

UNION ALL

-- 11. Journal Entry Cleared
SELECT
    JEI.AccountingDocument AS "JournalEntryId",
    'Journal Entry Cleared' AS "ActivityName",
    MIN(JEI.ClearingDateTime) AS "EventTime",
    BJE.CreatedByUser AS "CreatedByUser", -- Note: Clearing user is not directly available here
    BJE.CompanyCode AS "CompanyCode",
    BJE.AccountingDocumentType AS "JournalEntryType",
    BJE.PostingDate AS "PostingDate",
    BJE.AmountInLocalCurrency AS "AmountInLocalCurrency"
FROM I_JournalEntryItem AS JEI
JOIN JournalEntryBaseCTE AS BJE ON JEI.AccountingDocument = BJE.AccountingDocument
    AND JEI.CompanyCode = BJE.CompanyCode
    AND JEI.FiscalYear = BJE.FiscalYear
WHERE JEI.ClearingDateTime IS NOT NULL
GROUP BY JEI.AccountingDocument, BJE.CreatedByUser, BJE.CompanyCode, BJE.AccountingDocumentType, BJE.PostingDate, BJE.AmountInLocalCurrency

UNION ALL

-- 12. Journal Entry Reversal Processed
SELECT
    OriginalDoc.AccountingDocument AS "JournalEntryId",
    'Journal Entry Reversal Processed' AS "ActivityName",
    ReversalDoc.PostingDateTime AS "EventTime",
    ReversalDoc.CreatedByUser AS "CreatedByUser",
    OriginalDoc.CompanyCode AS "CompanyCode",
    OriginalDoc.AccountingDocumentType AS "JournalEntryType",
    OriginalDoc.PostingDate AS "PostingDate",
    OriginalDoc.AmountInLocalCurrency AS "AmountInLocalCurrency"
FROM JournalEntryBaseCTE AS ReversalDoc
JOIN JournalEntryBaseCTE AS OriginalDoc
    ON ReversalDoc.ReversedJournalEntry = OriginalDoc.AccountingDocument
    AND ReversalDoc.CompanyCode = OriginalDoc.CompanyCode
    AND ReversalDoc.ReversalFiscalYear = OriginalDoc.FiscalYear
WHERE ReversalDoc.ReversedJournalEntry IS NOT NULL;

REPORT Z_PM_JE_EXTRACT.

*&---------------------------------------------------------------------*
*&--                  Data Structures for Event Log                  --*
*&---------------------------------------------------------------------*
TYPES: BEGIN OF ty_event_log,
         journalentryid         TYPE string,
         activityname           TYPE string,
         eventtime              TYPE string,
         createdbyuser          TYPE uname,
         companycode            TYPE bukrs,
         journalentrytype       TYPE blart,
         postingdate            TYPE budat,
         amountinlocalcurrency  TYPE wrbtr,
       END OF ty_event_log.

*&---------------------------------------------------------------------*
*&--                    Selection Screen Definition                  --*
*&---------------------------------------------------------------------*
SELECTION-SCREEN BEGIN OF BLOCK b1 WITH FRAME TITLE TEXT-001.
  PARAMETERS: p_erdat_fr TYPE dats OBLIGATORY DEFAULT sy-datum-30,
              p_erdat_to TYPE dats OBLIGATORY DEFAULT sy-datum.
  SELECT-OPTIONS: so_bukrs FOR bkpf-bukrs OBLIGATORY.
  PARAMETERS: p_fpath TYPE string OBLIGATORY DEFAULT '/usr/sap/trans/tmp/je_event_log.csv'.
SELECTION-SCREEN END OF BLOCK b1.

*&---------------------------------------------------------------------*
*&--                        Internal Tables                          --*
*&---------------------------------------------------------------------*
DATA: gt_event_log TYPE TABLE OF ty_event_log.

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

  PERFORM get_created_posted_events.
  PERFORM get_parked_events.
  PERFORM get_attachment_events.
  PERFORM get_workflow_events.
  PERFORM get_change_events.
  PERFORM get_cleared_events.
  PERFORM get_reversal_events.

  SORT gt_event_log BY journalentryid eventtime.
  PERFORM write_output_file.

*&---------------------------------------------------------------------*
*&--      Subroutines for Extracting Individual Activities           --*
*&---------------------------------------------------------------------*
FORM get_created_posted_events.
  DATA: lt_bkpf TYPE TABLE OF bkpf,
        ls_event_log TYPE ty_event_log,
        lv_timestamp TYPE string.

  SELECT * FROM bkpf INTO TABLE lt_bkpf
    WHERE bukrs IN so_bukrs
      AND cpudt BETWEEN p_erdat_fr AND p_erdat_to.

  LOOP AT lt_bkpf ASSIGNING FIELD-SYMBOL(<fs_bkpf>).
    CLEAR ls_event_log.
    CONCATENATE <fs_bkpf>-bukrs <fs_bkpf>-belnr <fs_bkpf>-gjahr INTO ls_event_log-journalentryid.
    ls_event_log-companycode = <fs_bkpf>-bukrs.
    ls_event_log-journalentrytype = <fs_bkpf>-blart.
    ls_event_log-postingdate = <fs_bkpf>-budat.
    ls_event_log-createdbyuser = <fs_bkpf>-usnam.

    " Timestamp format YYYY-MM-DDTHH:MI:SS
    CONCATENATE <fs_bkpf>-cpudt(4) '-' <fs_bkpf>-cpudt+4(2) '-' <fs_bkpf>-cpudt+6(2) 'T' <fs_bkpf>-cputm(2) ':' <fs_bkpf>-cputm+2(2) ':' <fs_bkpf>-cputm+4(2) INTO lv_timestamp.
    ls_event_log-eventtime = lv_timestamp.

    " Activity: Journal Entry Created
    ls_event_log-activityname = 'Journal Entry Created'.
    SELECT SUM( hsl ) INTO ls_event_log-amountinlocalcurrency FROM acdoca WHERE belnr = <fs_bkpf>-belnr AND gjahr = <fs_bkpf>-gjahr AND bukrs = <fs_bkpf>-bukrs.
    APPEND ls_event_log TO gt_event_log.

    " Activity: Journal Entry Posted (if not parked)
    IF <fs_bkpf>-bstat = ' '.
      ls_event_log-activityname = 'Journal Entry Posted'.
      APPEND ls_event_log TO gt_event_log.

      " Activity: Manual Posting Identified (based on T-Code)
      CASE <fs_bkpf>-tcode.
        WHEN 'FB01' OR 'F-02' OR 'FB50' OR 'F-22' OR 'F-43'.
          ls_event_log-activityname = 'Manual Posting Identified'.
          APPEND ls_event_log TO gt_event_log.
      ENDCASE.
    ENDIF.
  ENDLOOP.
ENDFORM.

FORM get_parked_events.
  DATA: ls_event_log TYPE ty_event_log, lv_timestamp TYPE string.

  SELECT * FROM vbkpf
    WHERE bukrs IN so_bukrs
    AND cpudt BETWEEN p_erdat_fr AND p_erdat_to.

    CLEAR ls_event_log.
    CONCATENATE vbkpf-bukrs vbkpf-belnr vbkpf-gjahr INTO ls_event_log-journalentryid.
    CONCATENATE vbkpf-cpudt(4) '-' vbkpf-cpudt+4(2) '-' vbkpf-cpudt+6(2) 'T' vbkpf-cputm(2) ':' vbkpf-cputm+2(2) ':' vbkpf-cputm+4(2) INTO lv_timestamp.
    ls_event_log-activityname = 'Journal Entry Parked'.
    ls_event_log-eventtime = lv_timestamp.
    ls_event_log-createdbyuser = vbkpf-usnam.
    ls_event_log-companycode = vbkpf-bukrs.
    ls_event_log-journalentrytype = vbkpf-blart.
    ls_event_log-postingdate = vbkpf-budat.
    APPEND ls_event_log TO gt_event_log.
  ENDSELECT.
ENDFORM.

FORM get_attachment_events.
  DATA: lt_bdocs TYPE TABLE OF srgbtbrel, ls_event_log TYPE ty_event_log, lv_timestamp TYPE string.

  SELECT * FROM srgbtbrel INTO TABLE lt_bdocs
    WHERE typeid_a = 'BUS2081' " Object type for Accounting Document
      AND catid_a = 'BO'.

  LOOP AT lt_bdocs ASSIGNING FIELD-SYMBOL(<fs_bdocs>).
    CHECK <fs_bdocs>-instid_a(4) IN so_bukrs.
    DATA(lv_bukrs) = <fs_bdocs>-instid_a(4).
    DATA(lv_belnr) = <fs_bdocs>-instid_a+4(10).
    DATA(lv_gjahr) = <fs_bdocs>-instid_a+14(4).

    SELECT SINGLE cpudt, cputm, usnam, blart, budat FROM bkpf
      INTO (DATA(lv_cpudt), DATA(lv_cputm), DATA(lv_usnam), DATA(lv_blart), DATA(lv_budat))
      WHERE bukrs = lv_bukrs AND belnr = lv_belnr AND gjahr = lv_gjahr.
    IF sy-subrc = 0 AND lv_cpudt BETWEEN p_erdat_fr AND p_erdat_to.
      CLEAR ls_event_log.
      CONCATENATE lv_bukrs lv_belnr lv_gjahr INTO ls_event_log-journalentryid.
      " Note: Using document creation time as a proxy for attachment time.
      CONCATENATE lv_cpudt(4) '-' lv_cpudt+4(2) '-' lv_cpudt+6(2) 'T' lv_cputm(2) ':' lv_cputm+2(2) ':' lv_cputm+4(2) INTO lv_timestamp.
      ls_event_log-activityname = 'Supporting Documentation Attached'.
      ls_event_log-eventtime = lv_timestamp.
      ls_event_log-createdbyuser = lv_usnam.
      ls_event_log-companycode = lv_bukrs.
      ls_event_log-journalentrytype = lv_blart.
      ls_event_log-postingdate = lv_budat.
      APPEND ls_event_log TO gt_event_log.
    ENDIF.
  ENDLOOP.
ENDFORM.

FORM get_workflow_events.
  " This is a simplified example. Real workflow logic can be complex.
  " You must identify your specific Task IDs for these events.
  DATA: ls_event_log TYPE ty_event_log, lv_timestamp TYPE string.
  DATA: BEGIN OF ls_wi, wi_id TYPE sww_wiid, cr_date TYPE sww_cd, cr_time TYPE sww_ct, task TYPE sww_task, instid TYPE swo_typeid, END OF ls_wi.

  SELECT h~wi_id h~cr_date h~cr_time h~wi_rh_task o~instid
    FROM swwwihead AS h
    JOIN sww_wi2obj AS o ON h~wi_id = o~wi_id
    INTO @ls_wi
    WHERE o~typeid = 'BUS2081' AND o~catid = 'BO'
    AND h~cr_date BETWEEN @p_erdat_fr AND @p_erdat_to.

    DATA(lv_bukrs) = ls_wi-instid(4).
    DATA(lv_belnr) = ls_wi-instid+4(10).
    DATA(lv_gjahr) = ls_wi-instid+14(4).

    IF lv_bukrs IN so_bukrs.
      CLEAR ls_event_log.
      CONCATENATE lv_bukrs lv_belnr lv_gjahr INTO ls_event_log-journalentryid.
      CONCATENATE ls_wi-cr_date(4) '-' ls_wi-cr_date+4(2) '-' ls_wi-cr_date+6(2) 'T' ls_wi-cr_time(2) ':' ls_wi-cr_time+2(2) ':' ls_wi-cr_time+4(2) INTO lv_timestamp.
      ls_event_log-eventtime = lv_timestamp.
      ls_event_log-companycode = lv_bukrs.
      
      CASE ls_wi-task.
        WHEN '[Your Submit Task ID]'. " e.g., TS20000139
          ls_event_log-activityname = 'Journal Submitted For Review'.
          APPEND ls_event_log TO gt_event_log.
        WHEN '[Your Approve Task ID]'. " e.g., TS20000142
          ls_event_log-activityname = 'Journal Entry Approved'.
          APPEND ls_event_log TO gt_event_log.
        WHEN '[Your Reject Task ID]'. " e.g., TS20000141
          ls_event_log-activityname = 'Journal Entry Rejected'.
          APPEND ls_event_log TO gt_event_log.
      ENDCASE.
    ENDIF.
  ENDSELECT.
ENDFORM.

FORM get_change_events.
  DATA: lt_cdhdr TYPE TABLE OF cdhdr, ls_event_log TYPE ty_event_log, lv_timestamp TYPE string.

  SELECT * FROM cdhdr INTO TABLE lt_cdhdr
    WHERE objectclas = 'BELEG'
      AND udate BETWEEN p_erdat_fr AND p_erdat_to.

  LOOP AT lt_cdhdr ASSIGNING FIELD-SYMBOL(<fs_cdhdr>).
    DATA(lv_bukrs) = <fs_cdhdr>-objectid(4).
    DATA(lv_belnr) = <fs_cdhdr>-objectid+4(10).
    DATA(lv_gjahr) = <fs_cdhdr>-objectid+14(4).

    IF lv_bukrs IN so_bukrs.
      SELECT SINGLE bstat, budat, blart FROM bkpf
        INTO (DATA(lv_bstat), DATA(lv_budat), DATA(lv_blart))
        WHERE bukrs = lv_bukrs AND belnr = lv_belnr AND gjahr = lv_gjahr.
      IF sy-subrc = 0.
        CLEAR ls_event_log.
        CONCATENATE lv_bukrs lv_belnr lv_gjahr INTO ls_event_log-journalentryid.
        CONCATENATE <fs_cdhdr>-udate(4) '-' <fs_cdhdr>-udate+4(2) '-' <fs_cdhdr>-udate+6(2) 'T' <fs_cdhdr>-utime(2) ':' <fs_cdhdr>-utime+2(2) ':' <fs_cdhdr>-utime+4(2) INTO lv_timestamp.
        ls_event_log-eventtime = lv_timestamp.
        ls_event_log-createdbyuser = <fs_cdhdr>-username.
        ls_event_log-companycode = lv_bukrs.
        ls_event_log-journalentrytype = lv_blart.
        ls_event_log-postingdate = lv_budat.

        IF lv_bstat = ' ' AND <fs_cdhdr>-udate > lv_budat.
          ls_event_log-activityname = 'Journal Entry Changed After Posting'.
          APPEND ls_event_log TO gt_event_log.
        ELSEIF lv_bstat <> ' '.
          ls_event_log-activityname = 'Journal Entry Corrected'.
          APPEND ls_event_log TO gt_event_log.
        ENDIF.
      ENDIF.
    ENDIF.
  ENDLOOP.
ENDFORM.

FORM get_cleared_events.
  DATA: ls_event_log TYPE ty_event_log, lv_timestamp TYPE string.
  DATA: BEGIN OF ls_clear, belnr TYPE belnr_d, gjahr TYPE gjahr, bukrs TYPE bukrs, augdt TYPE augdt, END OF ls_clear, lt_clear LIKE TABLE OF ls_clear.

  SELECT belnr, gjahr, bukrs, MAX( augdt ) AS augdt FROM acdoca
    INTO TABLE @lt_clear
    WHERE bukrs IN @so_bukrs
      AND augdt NE '00000000'
      AND augdt BETWEEN @p_erdat_fr AND @p_erdat_to
    GROUP BY belnr, gjahr, bukrs.

  LOOP AT lt_clear INTO ls_clear.
    SELECT SINGLE usnam, blart, budat FROM bkpf
      INTO (DATA(lv_usnam), DATA(lv_blart), DATA(lv_budat))
      WHERE bukrs = ls_clear-bukrs AND belnr = ls_clear-belnr AND gjahr = ls_clear-gjahr.
    IF sy-subrc = 0.
      CLEAR ls_event_log.
      CONCATENATE ls_clear-bukrs ls_clear-belnr ls_clear-gjahr INTO ls_event_log-journalentryid.
      CONCATENATE ls_clear-augdt(4) '-' ls_clear-augdt+4(2) '-' ls_clear-augdt+6(2) 'T12:00:00' INTO lv_timestamp. " Clearing date has no time, use midday
      ls_event_log-activityname = 'Journal Entry Cleared'.
      ls_event_log-eventtime = lv_timestamp.
      ls_event_log-createdbyuser = lv_usnam.
      ls_event_log-companycode = ls_clear-bukrs.
      ls_event_log-journalentrytype = lv_blart.
      ls_event_log-postingdate = lv_budat.
      APPEND ls_event_log TO gt_event_log.
    ENDIF.
  ENDLOOP.
ENDFORM.

FORM get_reversal_events.
  DATA: lt_reversals TYPE TABLE OF bkpf, ls_event_log TYPE ty_event_log, lv_timestamp TYPE string.

  SELECT * FROM bkpf INTO TABLE lt_reversals
    WHERE bukrs IN so_bukrs
      AND cpudt BETWEEN p_erdat_fr AND p_erdat_to
      AND stblg IS NOT NULL.

  LOOP AT lt_reversals ASSIGNING FIELD-SYMBOL(<fs_rev>).
    SELECT SINGLE usnam, blart, budat FROM bkpf
      INTO (DATA(lv_usnam), DATA(lv_blart), DATA(lv_budat))
      WHERE bukrs = <fs_rev>-bukrs AND belnr = <fs_rev>-stblg AND gjahr = <fs_rev>-gjahr.
    IF sy-subrc = 0.
      CLEAR ls_event_log.
      CONCATENATE <fs_rev>-bukrs <fs_rev>-stblg <fs_rev>-gjahr INTO ls_event_log-journalentryid.
      CONCATENATE <fs_rev>-cpudt(4) '-' <fs_rev>-cpudt+4(2) '-' <fs_rev>-cpudt+6(2) 'T' <fs_rev>-cputm(2) ':' <fs_rev>-cputm+2(2) ':' <fs_rev>-cputm+4(2) INTO lv_timestamp.
      ls_event_log-activityname = 'Journal Entry Reversal Processed'.
      ls_event_log-eventtime = lv_timestamp.
      ls_event_log-createdbyuser = lv_usnam.
      ls_event_log-companycode = <fs_rev>-bukrs.
      ls_event_log-journalentrytype = lv_blart.
      ls_event_log-postingdate = lv_budat.
      APPEND ls_event_log TO gt_event_log.
    ENDIF.
  ENDLOOP.
ENDFORM.

FORM write_output_file.
  DATA: lv_line TYPE string.
  FIELD-SYMBOLS: <fs_event_log> TYPE ty_event_log.

  OPEN DATASET p_fpath FOR OUTPUT IN TEXT MODE ENCODING UTF-8.
  IF sy-subrc NE 0.
    MESSAGE 'Error opening file.' TYPE 'E'.
    RETURN.
  ENDIF.

  " Write Header
  lv_line = 'JournalEntryId,ActivityName,EventTime,CreatedByUser,CompanyCode,JournalEntryType,PostingDate,AmountInLocalCurrency'.
  TRANSFER lv_line TO p_fpath.

  LOOP AT gt_event_log ASSIGNING <fs_event_log>.
    CONCATENATE <fs_event_log>-journalentryid <fs_event_log>-activityname <fs_event_log>-eventtime <fs_event_log>-createdbyuser <fs_event_log>-companycode <fs_event_log>-journalentrytype <fs_event_log>-postingdate <fs_event_log>-amountinlocalcurrency
      INTO lv_line SEPARATED BY ','.
    TRANSFER lv_line TO p_fpath.
  ENDLOOP.

  CLOSE DATASET p_fpath.
  WRITE: / 'File successfully written to', p_fpath.
ENDFORM.