決算・レポーティングの仕訳データテンプレート

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
accounting_base AS (
    SELECT
        b.[Client field] AS ClientId,
        b.[Company code field] AS CompanyCode,
        b.[Fiscal year field] AS FiscalYear,
        b.[Document number field] AS AccountingDocumentNumber,
        b.[Document type field] AS JournalEntryType,
        b.[Created by field] AS CreatedByUser,
        b.[Document date field] AS DocumentDate,
        b.[Posting date field] AS PostingDate,
        b.[Creation date field] AS CreationDate,
        b.[Creation time field] AS CreationTime,
        b.[Reversal document field] AS ReversalDocumentNumber,
        b.[Reversed document field] AS ReversedDocumentNumber,
        b.[Reversal fiscal year field] AS ReversalFiscalYear,
        SUM(a.[Local currency amount field]) AS AmountInLocalCurrency,
        MAX(a.[Local currency field]) AS LocalCurrency
    FROM [Your schema].[BKPF] b
    INNER JOIN [Your schema].[ACDOCA] a
        ON a.[Client field] = b.[Client field]
       AND a.[Company code field] = b.[Company code field]
       AND a.[Fiscal year field] = b.[Fiscal year field]
       AND a.[Document number field] = b.[Document number field]
    WHERE b.[Client field] = '[Client parameter]'
      AND b.[Company code field] IN ([Company code filter])
      AND b.[Posting date field] BETWEEN '[Start date parameter]' AND '[End date parameter]'
      AND b.[Document type field] IN ([Document type filter])
    GROUP BY
        b.[Client field],
        b.[Company code field],
        b.[Fiscal year field],
        b.[Document number field],
        b.[Document type field],
        b.[Created by field],
        b.[Document date field],
        b.[Posting date field],
        b.[Creation date field],
        b.[Creation time field],
        b.[Reversal document field],
        b.[Reversed document field],
        b.[Reversal fiscal year field]
),
base_cases AS (
    SELECT
        ClientId,
        CompanyCode,
        FiscalYear,
        AccountingDocumentNumber,
        CAST(CompanyCode || '/' || FiscalYear || '/' || AccountingDocumentNumber AS NVARCHAR(100)) AS JournalEntryId,
        JournalEntryType,
        CreatedByUser,
        PostingDate,
        AmountInLocalCurrency,
        LocalCurrency,
        CreationDate,
        CreationTime,
        ReversalDocumentNumber,
        ReversedDocumentNumber,
        ReversalFiscalYear
    FROM accounting_base
),
created_events AS (
    SELECT
        JournalEntryId,
        'Journal Entry Created' AS ActivityName,
        CAST(CreationDate || ' ' || CreationTime AS TIMESTAMP) AS EventTime,
        CreatedByUser,
        CompanyCode,
        JournalEntryType,
        PostingDate,
        AmountInLocalCurrency,
        LocalCurrency
    FROM base_cases
),
parked_events AS (
    SELECT
        CAST(w.[Journal entry ID field] AS NVARCHAR(100)) AS JournalEntryId,
        'Journal Entry Parked' AS ActivityName,
        CAST(w.[Event timestamp field] AS TIMESTAMP) AS EventTime,
        w.[User field] AS CreatedByUser,
        w.[Company code field] AS CompanyCode,
        w.[Journal entry type field] AS JournalEntryType,
        w.[Posting date field] AS PostingDate,
        w.[Amount in local currency field] AS AmountInLocalCurrency,
        w.[Local currency field] AS LocalCurrency
    FROM [Your schema].[Workflow event source] w
    WHERE w.[Client field] = '[Client parameter]'
      AND w.[Event type field] = '[Parked event type]'
      AND CAST(w.[Event timestamp field] AS DATE) BETWEEN '[Start date parameter]' AND '[End date parameter]'
),
attachment_events AS (
    SELECT
        CAST(x.[Journal entry ID field] AS NVARCHAR(100)) AS JournalEntryId,
        'Supporting Documentation Attached' AS ActivityName,
        CAST(x.[Event timestamp field] AS TIMESTAMP) AS EventTime,
        x.[User field] AS CreatedByUser,
        x.[Company code field] AS CompanyCode,
        x.[Journal entry type field] AS JournalEntryType,
        x.[Posting date field] AS PostingDate,
        x.[Amount in local currency field] AS AmountInLocalCurrency,
        x.[Local currency field] AS LocalCurrency
    FROM [Your schema].[Attachment event source] x
    WHERE x.[Client field] = '[Client parameter]'
      AND x.[Event type field] = '[Attachment created event type]'
      AND CAST(x.[Event timestamp field] AS DATE) BETWEEN '[Start date parameter]' AND '[End date parameter]'
),
submitted_events AS (
    SELECT
        CAST(w.[Journal entry ID field] AS NVARCHAR(100)) AS JournalEntryId,
        'Journal Submitted For Review' AS ActivityName,
        CAST(w.[Event timestamp field] AS TIMESTAMP) AS EventTime,
        w.[User field] AS CreatedByUser,
        w.[Company code field] AS CompanyCode,
        w.[Journal entry type field] AS JournalEntryType,
        w.[Posting date field] AS PostingDate,
        w.[Amount in local currency field] AS AmountInLocalCurrency,
        w.[Local currency field] AS LocalCurrency
    FROM [Your schema].[Workflow event source] w
    WHERE w.[Client field] = '[Client parameter]'
      AND w.[Event type field] = '[Submitted event type]'
      AND CAST(w.[Event timestamp field] AS DATE) BETWEEN '[Start date parameter]' AND '[End date parameter]'
),
rejected_events AS (
    SELECT
        CAST(w.[Journal entry ID field] AS NVARCHAR(100)) AS JournalEntryId,
        'Journal Entry Rejected' AS ActivityName,
        CAST(w.[Event timestamp field] AS TIMESTAMP) AS EventTime,
        w.[User field] AS CreatedByUser,
        w.[Company code field] AS CompanyCode,
        w.[Journal entry type field] AS JournalEntryType,
        w.[Posting date field] AS PostingDate,
        w.[Amount in local currency field] AS AmountInLocalCurrency,
        w.[Local currency field] AS LocalCurrency
    FROM [Your schema].[Workflow event source] w
    WHERE w.[Client field] = '[Client parameter]'
      AND w.[Event type field] = '[Rejected event type]'
      AND CAST(w.[Event timestamp field] AS DATE) BETWEEN '[Start date parameter]' AND '[End date parameter]'
),
corrected_events AS (
    SELECT
        CAST(w.[Journal entry ID field] AS NVARCHAR(100)) AS JournalEntryId,
        'Journal Entry Corrected' AS ActivityName,
        CAST(w.[Event timestamp field] AS TIMESTAMP) AS EventTime,
        w.[User field] AS CreatedByUser,
        w.[Company code field] AS CompanyCode,
        w.[Journal entry type field] AS JournalEntryType,
        w.[Posting date field] AS PostingDate,
        w.[Amount in local currency field] AS AmountInLocalCurrency,
        w.[Local currency field] AS LocalCurrency
    FROM [Your schema].[Workflow event source] w
    WHERE w.[Client field] = '[Client parameter]'
      AND w.[Event type field] = '[Corrected event type]'
      AND CAST(w.[Event timestamp field] AS DATE) BETWEEN '[Start date parameter]' AND '[End date parameter]'
),
approved_events AS (
    SELECT
        CAST(w.[Journal entry ID field] AS NVARCHAR(100)) AS JournalEntryId,
        'Journal Entry Approved' AS ActivityName,
        CAST(w.[Event timestamp field] AS TIMESTAMP) AS EventTime,
        w.[User field] AS CreatedByUser,
        w.[Company code field] AS CompanyCode,
        w.[Journal entry type field] AS JournalEntryType,
        w.[Posting date field] AS PostingDate,
        w.[Amount in local currency field] AS AmountInLocalCurrency,
        w.[Local currency field] AS LocalCurrency
    FROM [Your schema].[Workflow event source] w
    WHERE w.[Client field] = '[Client parameter]'
      AND w.[Event type field] = '[Approved event type]'
      AND CAST(w.[Event timestamp field] AS DATE) BETWEEN '[Start date parameter]' AND '[End date parameter]'
),
manual_events AS (
    SELECT
        JournalEntryId,
        'Manual Posting Identified' AS ActivityName,
        CAST(PostingDate AS TIMESTAMP) AS EventTime,
        CreatedByUser,
        CompanyCode,
        JournalEntryType,
        PostingDate,
        AmountInLocalCurrency,
        LocalCurrency
    FROM base_cases
    WHERE [Manual posting condition verified for this system]
),
posted_events AS (
    SELECT
        JournalEntryId,
        'Journal Entry Posted' AS ActivityName,
        CAST(PostingDate AS TIMESTAMP) AS EventTime,
        CreatedByUser,
        CompanyCode,
        JournalEntryType,
        PostingDate,
        AmountInLocalCurrency,
        LocalCurrency
    FROM base_cases
    WHERE [Posted document condition verified for this system]
),
changed_events AS (
    SELECT
        CAST(c.[Journal entry ID field] AS NVARCHAR(100)) AS JournalEntryId,
        'Journal Entry Changed After Posting' AS ActivityName,
        CAST(c.[Change timestamp field] AS TIMESTAMP) AS EventTime,
        c.[User field] AS CreatedByUser,
        c.[Company code field] AS CompanyCode,
        c.[Journal entry type field] AS JournalEntryType,
        c.[Posting date field] AS PostingDate,
        c.[Amount in local currency field] AS AmountInLocalCurrency,
        c.[Local currency field] AS LocalCurrency
    FROM [Your schema].[Change history source] c
    WHERE c.[Client field] = '[Client parameter]'
      AND c.[Post posting change indicator field] = '[Post posting change value]'
      AND CAST(c.[Change timestamp field] AS DATE) BETWEEN '[Start date parameter]' AND '[End date parameter]'
),
cleared_events AS (
    SELECT
        CAST(cl.[Journal entry ID field] AS NVARCHAR(100)) AS JournalEntryId,
        'Journal Entry Cleared' AS ActivityName,
        CAST(cl.[Clearing timestamp field] AS TIMESTAMP) AS EventTime,
        cl.[User field] AS CreatedByUser,
        cl.[Company code field] AS CompanyCode,
        cl.[Journal entry type field] AS JournalEntryType,
        cl.[Posting date field] AS PostingDate,
        cl.[Amount in local currency field] AS AmountInLocalCurrency,
        cl.[Local currency field] AS LocalCurrency
    FROM [Your schema].[Clearing source] cl
    WHERE cl.[Client field] = '[Client parameter]'
      AND CAST(cl.[Clearing timestamp field] AS DATE) BETWEEN '[Start date parameter]' AND '[End date parameter]'
),
reversal_events AS (
    SELECT
        CAST(r.[Original journal entry ID field] AS NVARCHAR(100)) AS JournalEntryId,
        'Journal Entry Reversal Processed' AS ActivityName,
        CAST(r.[Reversal timestamp field] AS TIMESTAMP) AS EventTime,
        r.[User field] AS CreatedByUser,
        r.[Company code field] AS CompanyCode,
        r.[Journal entry type field] AS JournalEntryType,
        r.[Posting date field] AS PostingDate,
        r.[Amount in local currency field] AS AmountInLocalCurrency,
        r.[Local currency field] AS LocalCurrency
    FROM [Your schema].[Reversal source] r
    WHERE r.[Client field] = '[Client parameter]'
      AND CAST(r.[Reversal timestamp field] AS DATE) BETWEEN '[Start date parameter]' AND '[End date parameter]'
)
SELECT JournalEntryId, ActivityName, EventTime, CreatedByUser, CompanyCode, JournalEntryType, PostingDate, AmountInLocalCurrency, LocalCurrency FROM created_events
UNION ALL
SELECT JournalEntryId, ActivityName, EventTime, CreatedByUser, CompanyCode, JournalEntryType, PostingDate, AmountInLocalCurrency, LocalCurrency FROM parked_events
UNION ALL
SELECT JournalEntryId, ActivityName, EventTime, CreatedByUser, CompanyCode, JournalEntryType, PostingDate, AmountInLocalCurrency, LocalCurrency FROM attachment_events
UNION ALL
SELECT JournalEntryId, ActivityName, EventTime, CreatedByUser, CompanyCode, JournalEntryType, PostingDate, AmountInLocalCurrency, LocalCurrency FROM submitted_events
UNION ALL
SELECT JournalEntryId, ActivityName, EventTime, CreatedByUser, CompanyCode, JournalEntryType, PostingDate, AmountInLocalCurrency, LocalCurrency FROM rejected_events
UNION ALL
SELECT JournalEntryId, ActivityName, EventTime, CreatedByUser, CompanyCode, JournalEntryType, PostingDate, AmountInLocalCurrency, LocalCurrency FROM corrected_events
UNION ALL
SELECT JournalEntryId, ActivityName, EventTime, CreatedByUser, CompanyCode, JournalEntryType, PostingDate, AmountInLocalCurrency, LocalCurrency FROM approved_events
UNION ALL
SELECT JournalEntryId, ActivityName, EventTime, CreatedByUser, CompanyCode, JournalEntryType, PostingDate, AmountInLocalCurrency, LocalCurrency FROM manual_events
UNION ALL
SELECT JournalEntryId, ActivityName, EventTime, CreatedByUser, CompanyCode, JournalEntryType, PostingDate, AmountInLocalCurrency, LocalCurrency FROM posted_events
UNION ALL
SELECT JournalEntryId, ActivityName, EventTime, CreatedByUser, CompanyCode, JournalEntryType, PostingDate, AmountInLocalCurrency, LocalCurrency FROM changed_events
UNION ALL
SELECT JournalEntryId, ActivityName, EventTime, CreatedByUser, CompanyCode, JournalEntryType, PostingDate, AmountInLocalCurrency, LocalCurrency FROM cleared_events
UNION ALL
SELECT JournalEntryId, ActivityName, EventTime, CreatedByUser, CompanyCode, JournalEntryType, PostingDate, AmountInLocalCurrency, LocalCurrency FROM reversal_events
ORDER BY JournalEntryId, EventTime, ActivityName;

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.