Mẫu dữ liệu cho quy trình từ ghi nhận đến báo cáo: khóa sổ kỳ và đối soát

/*
LOGICAL REPRESENTATION FOR SAP QUERY (SQVI)
This is not a single executable script. Due to limitations in SQVI, you must create a base query joining BKPF and BSEG, then run it multiple times using different selection screen variants to extract each activity below. The results must be combined manually.
A separate query joining CDHDR and CDPOS is needed for period open/close activities.
*/

-- Activity 1: Period Opened For Posting
-- Required Query: Join CDHDR and CDPOS in SQVI.
SELECT
  CONCAT(T001B.VONJ1, T001B.POPER) AS FinancialPeriod,
  'Period Opened For Posting' AS ActivityName,
  TO_TIMESTAMP(CONCAT(CDHDR.UDATE, CDHDR.UTIME), 'YYYYMMDDHH24MISS') AS EventTime, 
  '[SAP ECC SID]' AS SourceSystem,
  CURRENT_TIMESTAMP AS LastDataUpdate,
  T001B.BUKRS AS BUKRS,
  NULL AS BELNR,
  CDHDR.USERNAME AS USNAM,
  NULL AS HKONT,
  NULL AS BLART,
  NULL AS DMBTR,
  NULL AS JournalEntryType
FROM CDHDR
JOIN CDPOS ON CDHDR.CHANGENR = CDPOS.CHANGENR
JOIN T001B ON SUBSTRING(CDPOS.TABKEY, 4, 4) = T001B.BUKRS
WHERE CDHDR.OBJECTCLAS = 'DEBI_BUKRS' AND CDPOS.TABNAME = 'T001B' AND CDPOS.FNAME = 'OFPER' AND CDPOS.VALUE_NEW > CDPOS.VALUE_OLD;

UNION ALL

-- Activity 2: Accrual Or Provision Posted
SELECT
  CONCAT(BKPF.GJAHR, BKPF.MONAT) AS FinancialPeriod,
  'Accrual Or Provision Posted' AS ActivityName,
  TO_TIMESTAMP(CONCAT(BKPF.CPUDT, BKPF.CPUTM), 'YYYYMMDDHH24MISS') AS EventTime, 
  '[SAP ECC SID]' AS SourceSystem,
  CURRENT_TIMESTAMP AS LastDataUpdate,
  BKPF.BUKRS AS BUKRS,
  BKPF.BELNR AS BELNR,
  BKPF.USNAM AS USNAM,
  BSEG.HKONT AS HKONT,
  BKPF.BLART AS BLART,
  BSEG.DMBTR AS DMBTR,
  'Accrual' AS JournalEntryType
FROM BKPF
JOIN BSEG ON BKPF.BUKRS = BSEG.BUKRS AND BKPF.BELNR = BSEG.BELNR AND BKPF.GJAHR = BSEG.GJAHR
WHERE BKPF.BLART IN ('SA', '[Your Accrual Doc Type]'); -- Filter by document types used for accruals

UNION ALL

-- Activity 3: Intercompany Document Cleared
SELECT
  CONCAT(BKPF.GJAHR, BKPF.MONAT) AS FinancialPeriod,
  'Intercompany Document Cleared' AS ActivityName,
  TO_TIMESTAMP(CONCAT(BKPF.CPUDT, BKPF.CPUTM), 'YYYYMMDDHH24MISS') AS EventTime, 
  '[SAP ECC SID]' AS SourceSystem,
  CURRENT_TIMESTAMP AS LastDataUpdate,
  BKPF.BUKRS AS BUKRS,
  BKPF.AUGBL AS BELNR, -- The clearing document number
  BKPF.USNAM AS USNAM,
  BSEG.HKONT AS HKONT,
  BKPF.BLART AS BLART,
  BSEG.DMBTR AS DMBTR,
  'Clearing' AS JournalEntryType
FROM BKPF
JOIN BSEG ON BKPF.BUKRS = BSEG.BUKRS AND BKPF.BELNR = BSEG.BELNR AND BKPF.GJAHR = BSEG.GJAHR
WHERE BSEG.AUGBL IS NOT NULL AND BSEG.VBUND IS NOT NULL; -- Identify cleared items with an intercompany trading partner

UNION ALL

-- Activity 4: Foreign Currency Valuation Run
SELECT
  CONCAT(BKPF.GJAHR, BKPF.MONAT) AS FinancialPeriod,
  'Foreign Currency Valuation Run' AS ActivityName,
  TO_TIMESTAMP(CONCAT(BKPF.CPUDT, BKPF.CPUTM), 'YYYYMMDDHH24MISS') AS EventTime, 
  '[SAP ECC SID]' AS SourceSystem,
  CURRENT_TIMESTAMP AS LastDataUpdate,
  BKPF.BUKRS AS BUKRS,
  BKPF.BELNR AS BELNR,
  BKPF.USNAM AS USNAM,
  BSEG.HKONT AS HKONT,
  BKPF.BLART AS BLART,
  BSEG.DMBTR AS DMBTR,
  'Valuation' AS JournalEntryType
FROM BKPF
JOIN BSEG ON BKPF.BUKRS = BSEG.BUKRS AND BKPF.BELNR = BSEG.BELNR AND BKPF.GJAHR = BSEG.GJAHR
WHERE BKPF.TCODE IN ('FAGL_FCV', 'FBB1'); -- Filter by FCV transaction code

UNION ALL

-- Activity 5: GR/IR Clearing Run Executed
SELECT
  CONCAT(BKPF.GJAHR, BKPF.MONAT) AS FinancialPeriod,
  'GR/IR Clearing Run Executed' AS ActivityName,
  TO_TIMESTAMP(CONCAT(BKPF.CPUDT, BKPF.CPUTM), 'YYYYMMDDHH24MISS') AS EventTime, 
  '[SAP ECC SID]' AS SourceSystem,
  CURRENT_TIMESTAMP AS LastDataUpdate,
  BKPF.BUKRS AS BUKRS,
  BKPF.BELNR AS BELNR,
  BKPF.USNAM AS USNAM,
  BSEG.HKONT AS HKONT,
  BKPF.BLART AS BLART,
  BSEG.DMBTR AS DMBTR,
  'Clearing' AS JournalEntryType
FROM BKPF
JOIN BSEG ON BKPF.BUKRS = BSEG.BUKRS AND BKPF.BELNR = BSEG.BELNR AND BKPF.GJAHR = BSEG.GJAHR
WHERE BKPF.TCODE IN ('F.13', 'F13E'); -- Filter by automated clearing transaction codes

UNION ALL

-- Activity 6: Account Reconciliation Started (Proxy)
-- This is a proxy. It takes the earliest adjusting journal entry timestamp for the period.
SELECT
  A.FinancialPeriod, 
  'Account Reconciliation Started' AS ActivityName, 
  MIN(A.EventTime) AS EventTime,
  '[SAP ECC SID]' AS SourceSystem,
  CURRENT_TIMESTAMP AS LastDataUpdate,
  A.BUKRS,
  NULL AS BELNR,
  NULL AS USNAM,
  NULL AS HKONT,
  NULL AS BLART,
  NULL AS DMBTR,
  NULL AS JournalEntryType
FROM (
  SELECT CONCAT(BKPF.GJAHR, BKPF.MONAT) AS FinancialPeriod, 
  TO_TIMESTAMP(CONCAT(BKPF.CPUDT, BKPF.CPUTM), 'YYYYMMDDHH24MISS') AS EventTime, 
  BKPF.BUKRS
  FROM BKPF
  WHERE BKPF.BLART IN ('AB', '[Your Adjusting Doc Type]')
) AS A
GROUP BY A.FinancialPeriod, A.BUKRS;

UNION ALL

-- Activity 7: Adjusting Journal Entry Posted
SELECT
  CONCAT(BKPF.GJAHR, BKPF.MONAT) AS FinancialPeriod,
  'Adjusting Journal Entry Posted' AS ActivityName,
  TO_TIMESTAMP(CONCAT(BKPF.CPUDT, BKPF.CPUTM), 'YYYYMMDDHH24MISS') AS EventTime, 
  '[SAP ECC SID]' AS SourceSystem,
  CURRENT_TIMESTAMP AS LastDataUpdate,
  BKPF.BUKRS AS BUKRS,
  BKPF.BELNR AS BELNR,
  BKPF.USNAM AS USNAM,
  BSEG.HKONT AS HKONT,
  BKPF.BLART AS BLART,
  BSEG.DMBTR AS DMBTR,
  'Adjusting' AS JournalEntryType
FROM BKPF
JOIN BSEG ON BKPF.BUKRS = BSEG.BUKRS AND BKPF.BELNR = BSEG.BELNR AND BKPF.GJAHR = BSEG.GJAHR
WHERE BKPF.BLART IN ('AB', '[Your Adjusting Doc Type]'); -- Filter by document types for adjustments

UNION ALL

-- Activity 8: Reversal Entry Posted
SELECT
  CONCAT(BKPF.GJAHR, BKPF.MONAT) AS FinancialPeriod,
  'Reversal Entry Posted' AS ActivityName,
  TO_TIMESTAMP(CONCAT(BKPF.CPUDT, BKPF.CPUTM), 'YYYYMMDDHH24MISS') AS EventTime, 
  '[SAP ECC SID]' AS SourceSystem,
  CURRENT_TIMESTAMP AS LastDataUpdate,
  BKPF.BUKRS AS BUKRS,
  BKPF.BELNR AS BELNR,
  BKPF.USNAM AS USNAM,
  BSEG.HKONT AS HKONT,
  BKPF.BLART AS BLART,
  BSEG.DMBTR AS DMBTR,
  'Reversal' AS JournalEntryType
FROM BKPF
JOIN BSEG ON BKPF.BUKRS = BSEG.BUKRS AND BKPF.BELNR = BSEG.BELNR AND BKPF.GJAHR = BSEG.GJAHR
WHERE BKPF.STBLG IS NOT NULL; -- STBLG (Reversed Document Number) is populated for reversals

UNION ALL

-- Activity 9: Period Closed For Posting
-- Required Query: Join CDHDR and CDPOS in SQVI.
SELECT
  CONCAT(T001B.VONJ1, T001B.POPER) AS FinancialPeriod,
  'Period Closed For Posting' AS ActivityName,
  TO_TIMESTAMP(CONCAT(CDHDR.UDATE, CDHDR.UTIME), 'YYYYMMDDHH24MISS') AS EventTime, 
  '[SAP ECC SID]' AS SourceSystem,
  CURRENT_TIMESTAMP AS LastDataUpdate,
  T001B.BUKRS AS BUKRS,
  NULL AS BELNR,
  CDHDR.USERNAME AS USNAM,
  NULL AS HKONT,
  NULL AS BLART,
  NULL AS DMBTR,
  NULL AS JournalEntryType
FROM CDHDR
JOIN CDPOS ON CDHDR.CHANGENR = CDPOS.CHANGENR
JOIN T001B ON SUBSTRING(CDPOS.TABKEY, 4, 4) = T001B.BUKRS
WHERE CDHDR.OBJECTCLAS = 'DEBI_BUKRS' AND CDPOS.TABNAME = 'T001B' AND CDPOS.FNAME = 'OFPER' AND CDPOS.VALUE_NEW < CDPOS.VALUE_OLD;

/*
-- Activities Not Extractable via this Method
-- The following activities are not data postings and cannot be reliably extracted by querying finance tables BKPF/BSEG.
-- Extraction would require analyzing application logs (SLG1), system logs (STAT), or custom-built logging solutions.

-- Activity: Trial Balance Report Generated
-- Activity: Financial Statements Generated
-- Activity: Reconciliation Reviewed
*/

REPORT Z_PM_R2R_EXTRACTION.

" ====================================================================
" DATA DECLARATIONS
" ====================================================================
TYPES: BEGIN OF ty_event_log,
         FinancialPeriod  TYPE string,
         ActivityName     TYPE string,
         EventTime        TYPE timestamp,
         SourceSystem     TYPE string,
         LastDataUpdate   TYPE timestamp,
         BUKRS            TYPE bukrs,
         BELNR            TYPE belnr_d,
         USNAM            TYPE usnam,
         HKONT            TYPE hkont,
         BLART            TYPE blart,
         DMBTR            TYPE dmbtr,
         JournalEntryType TYPE string,
       END OF ty_event_log.

DATA: lt_final_log TYPE STANDARD TABLE OF ty_event_log,
      ls_log_entry LIKE LINE OF lt_final_log.

DATA: lv_source_system TYPE string VALUE 'SAP ECC'.
GET TIME STAMP FIELD ls_log_entry-LastDataUpdate.

" ====================================================================
" SELECTION SCREEN
" ====================================================================
SELECT-OPTIONS: s_bukrs FOR ls_log_entry-bukrs OBLIGATORY,
                  s_gjahr FOR bkpf-gjahr OBLIGATORY,
                  s_monat FOR bkpf-monat OBLIGATORY.

PARAMETERS: p_accrl TYPE blart DEFAULT 'SA', " Example Doc Type for Accruals
              p_adjst TYPE blart DEFAULT 'AJ', " Example Doc Type for Adjustments
              p_fcv   TYPE btcjob DEFAULT 'FAGL_FCV*', " Program for FC Valuation
              p_grir  TYPE btcjob DEFAULT 'SAPF124*',  " Program for GR/IR Clearing
              p_fs    TYPE btcjob DEFAULT 'RFBILA00'.   " Program for Fin. Statements

" ====================================================================
" MAIN LOGIC
" ====================================================================
START-OF-SELECTION.

  " --- 1. Period Opened For Posting ---
  " Note: This is complex. This example queries change documents for posting period config (T001B).
  SELECT h~objectid, h~udate, h~utime, h~username
    INTO TABLE @DATA(lt_period_open)
    FROM cdhdr AS h
    WHERE h~objectclas = 'V_T001B' AND h~change_ind = 'U'.
  LOOP AT lt_period_open INTO DATA(ls_period_open).
    " Further logic needed to parse OBJECTID and check CDPOS for opening a period.
    " This is a simplified placeholder for a complex logic.
    ls_log_entry-ActivityName = 'Period Opened For Posting'.
    " Populate other fields... append to lt_final_log
  ENDLOOP.

  " --- 2. Accrual Or Provision Posted ---
  SELECT h~bukrs, h~belnr, h~gjahr, h~monat, h~blart, h~usnam, h~cputm, h~cpudt, i~hkont, i~dmbtr
    FROM bkpf AS h JOIN bseg AS i ON h~belnr = i~belnr AND h~bukrs = i~bukrs AND h~gjahr = i~gjahr
    INTO CORRESPONDING FIELDS OF TABLE @DATA(lt_accrual)
    WHERE h~bukrs IN @s_bukrs AND h~gjahr IN @s_gjahr AND h~monat IN @s_monat AND h~blart = @p_accrl.
  LOOP AT lt_accrual INTO DATA(ls_accrual).
    CLEAR ls_log_entry.
    CONVERT DATE ls_accrual-cpudt TIME ls_accrual-cputm INTO TIME STAMP ls_log_entry-EventTime TIME ZONE sy-zonlo.
    CONCATENATE ls_accrual-gjahr ls_accrual-monat INTO ls_log_entry-FinancialPeriod.
    ls_log_entry-ActivityName = 'Accrual Or Provision Posted'.
    ls_log_entry-BUKRS = ls_accrual-bukrs.
    ls_log_entry-BELNR = ls_accrual-belnr.
    ls_log_entry-USNAM = ls_accrual-usnam.
    ls_log_entry-HKONT = ls_accrual-hkont.
    ls_log_entry-BLART = ls_accrual-blart.
    ls_log_entry-DMBTR = ls_accrual-dmbtr.
    ls_log_entry-JournalEntryType = 'Accrual'.
    GET TIME STAMP FIELD ls_log_entry-LastDataUpdate.
    ls_log_entry-SourceSystem = lv_source_system.
    APPEND ls_log_entry TO lt_final_log.
  ENDLOOP.

  " --- 3. Intercompany Document Cleared ---
  SELECT h~bukrs, h~belnr, h~gjahr, h~monat, h~blart, h~usnam, h~cputm, h~cpudt, i~hkont, i~dmbtr
    FROM bkpf AS h JOIN bseg AS i ON h~belnr = i~belnr AND h~bukrs = i~bukrs AND h~gjahr = i~gjahr
    INTO CORRESPONDING FIELDS OF TABLE @DATA(lt_ic_clear)
    WHERE h~bukrs IN @s_bukrs AND h~gjahr IN @s_gjahr AND h~monat IN @s_monat
      AND h~blart = 'AB' AND i~vbund <> space.
  LOOP AT lt_ic_clear INTO DATA(ls_ic_clear).
    " Populate ls_log_entry and append to lt_final_log, setting ActivityName = 'Intercompany Document Cleared'
  ENDLOOP.

  " --- 4. Foreign Currency Valuation Run ---
  SELECT j~jobname, j~sdlstrtda, j~sdlstrttm, j~sdluname
    INTO TABLE @DATA(lt_fcv_jobs)
    FROM tbtco AS j
    WHERE j~jobname LIKE @p_fcv AND j~status = 'F'. " F = Finished
  LOOP AT lt_fcv_jobs INTO DATA(ls_fcv_job).
    " Populate ls_log_entry, deriving FinancialPeriod from date, setting ActivityName = 'Foreign Currency Valuation Run'
  ENDLOOP.

  " --- 5. GR/IR Clearing Run Executed ---
  SELECT j~jobname, j~sdlstrtda, j~sdlstrttm, j~sdluname
    INTO TABLE @DATA(lt_grir_jobs)
    FROM tbtco AS j
    WHERE j~jobname LIKE @p_grir AND j~status = 'F'.
  LOOP AT lt_grir_jobs INTO DATA(ls_grir_job).
     " Populate ls_log_entry, deriving FinancialPeriod from date, setting ActivityName = 'GR/IR Clearing Run Executed'
  ENDLOOP.

  " --- 6. Account Reconciliation Started (Inferred) ---
  " Note: Inferring this by first run of a report, e.g., FAGLB03. Requires custom logging or complex logic.
  " Placeholder: Query a custom log table if available.
  " SELECT ... FROM zreco_log ...

  " --- 7. Adjusting Journal Entry Posted ---
  SELECT h~bukrs, h~belnr, h~gjahr, h~monat, h~blart, h~usnam, h~cputm, h~cpudt, i~hkont, i~dmbtr
    FROM bkpf AS h JOIN bseg AS i ON h~belnr = i~belnr AND h~bukrs = i~bukrs AND h~gjahr = i~gjahr
    INTO CORRESPONDING FIELDS OF TABLE @DATA(lt_adjust)
    WHERE h~bukrs IN @s_bukrs AND h~gjahr IN @s_gjahr AND h~monat IN @s_monat AND h~blart = @p_adjst.
  LOOP AT lt_adjust INTO DATA(ls_adjust).
    " Populate ls_log_entry and append, setting ActivityName = 'Adjusting Journal Entry Posted', JournalEntryType = 'Adjusting'
  ENDLOOP.

  " --- 8. Reversal Entry Posted ---
  SELECT h~bukrs, h~belnr, h~gjahr, h~monat, h~blart, h~usnam, h~cputm, h~cpudt, i~hkont, i~dmbtr
    FROM bkpf AS h JOIN bseg AS i ON h~belnr = i~belnr AND h~bukrs = i~bukrs AND h~gjahr = i~gjahr
    INTO CORRESPONDING FIELDS OF TABLE @DATA(lt_reversal)
    WHERE h~bukrs IN @s_bukrs AND h~gjahr IN @s_gjahr AND h~monat IN @s_monat AND h~stblg <> space.
  LOOP AT lt_reversal INTO DATA(ls_reversal).
    " Populate ls_log_entry and append, setting ActivityName = 'Reversal Entry Posted', JournalEntryType = 'Reversal'
  ENDLOOP.

  " --- 9. Trial Balance Report Generated (Inferred from Job) ---
  " Similar to other jobs, query TBTCO for Trial Balance report programs.
  " SELECT ... FROM tbtco ... WHERE jobname LIKE '[Your Trial Balance Program]'.

  " --- 10. Financial Statements Generated ---
  SELECT j~jobname, j~sdlstrtda, j~sdlstrttm, j~sdluname
    INTO TABLE @DATA(lt_fs_jobs)
    FROM tbtco AS j
    WHERE j~jobname LIKE @p_fs AND j~status = 'F'.
  LOOP AT lt_fs_jobs INTO DATA(ls_fs_job).
    " Populate ls_log_entry, deriving FinancialPeriod from date, setting ActivityName = 'Financial Statements Generated'
  ENDLOOP.

  " --- 11. Reconciliation Reviewed (Inferred) ---
  " Note: This activity is almost always managed outside of SAP or in a custom solution.
  " Placeholder: Query a custom approval/log table if available.
  " SELECT ... FROM zreco_approval ...

  " --- 12. Period Closed For Posting ---
  " Note: Similar to 'Period Opened', this is very complex. Query change documents for T001B.
  SELECT h~objectid, h~udate, h~utime, h~username
    INTO TABLE @DATA(lt_period_close)
    FROM cdhdr AS h
    WHERE h~objectclas = 'V_T001B' AND h~change_ind = 'U'.
  LOOP AT lt_period_close INTO DATA(ls_period_close).
    " Further logic needed to parse OBJECTID and check CDPOS for closing a period.
    ls_log_entry-ActivityName = 'Period Closed For Posting'.
    " Populate other fields... append to lt_final_log
  ENDLOOP.

  " ... Code to display or download the lt_final_log internal table ...

ENDFORMS.

SELECT
    CONCAT(CAST(b.GJAHR AS VARCHAR(4)), '-', RIGHT(CONCAT('00', b.MONAT), 2)) AS FinancialPeriod,
    'Period Opened For Posting' AS ActivityName,
    c.UDATE + c.UTIME AS EventTime,
    'SAP_ECC' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    p.TABKEY AS CompanyCode,
    NULL AS DocumentNumber,
    c.USERNAME AS ResponsibleUser,
    NULL AS GLAccount,
    NULL AS DocumentType,
    NULL AS AmountInLocalCurrency,
    NULL AS JournalEntryType
FROM CDHDR c
JOIN CDPOS p ON c.OBJECTCLAS = p.OBJECTCLAS AND c.OBJECTID = p.OBJECTID AND c.CHANGENR = p.CHANGENR
WHERE c.OBJECTCLAS = 'V_T001B'
  AND p.TABNAME = 'T001B'
  AND p.FNAME = 'FRPE1'
  AND c.UDATE BETWEEN '{{start_date}}' AND '{{end_date}}'

UNION ALL

SELECT
    CONCAT(CAST(b.GJAHR AS VARCHAR(4)), '-', RIGHT(CONCAT('00', b.MONAT), 2)) AS FinancialPeriod,
    'Period Closed For Posting' AS ActivityName,
    c.UDATE + c.UTIME AS EventTime,
    'SAP_ECC' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    p.TABKEY AS CompanyCode,
    NULL AS DocumentNumber,
    c.USERNAME AS ResponsibleUser,
    NULL AS GLAccount,
    NULL AS DocumentType,
    NULL AS AmountInLocalCurrency,
    NULL AS JournalEntryType
FROM CDHDR c
JOIN CDPOS p ON c.OBJECTCLAS = p.OBJECTCLAS AND c.OBJECTID = p.OBJECTID AND c.CHANGENR = p.CHANGENR
WHERE c.OBJECTCLAS = 'V_T001B'
  AND p.TABNAME = 'T001B'
  AND p.FNAME = 'TOPE1'
  AND c.UDATE BETWEEN '{{start_date}}' AND '{{end_date}}'

UNION ALL

SELECT
    CONCAT(CAST(h.GJAHR AS VARCHAR(4)), '-', RIGHT(CONCAT('00', h.MONAT), 2)) AS FinancialPeriod,
    'Accrual Or Provision Posted' AS ActivityName,
    h.CPUDT + h.CPUTM AS EventTime,
    'SAP_ECC' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    h.BUKRS AS CompanyCode,
    h.BELNR AS DocumentNumber,
    h.USNAM AS ResponsibleUser,
    d.HKONT AS GLAccount,
    h.BLART AS DocumentType,
    d.DMBTR AS AmountInLocalCurrency,
    'Accrual' AS JournalEntryType
FROM BKPF h
JOIN BSEG d ON h.MANDT = d.MANDT AND h.BUKRS = d.BUKRS AND h.BELNR = d.BELNR AND h.GJAHR = d.GJAHR
WHERE h.BUDAT BETWEEN '{{start_date}}' AND '{{end_date}}'
  AND h.BUKRS IN ({{company_codes}})
  AND h.BLART IN ('SA', '[Your Accrual Doc Type]')

UNION ALL

SELECT
    CONCAT(CAST(h.GJAHR AS VARCHAR(4)), '-', RIGHT(CONCAT('00', h.MONAT), 2)) AS FinancialPeriod,
    'Intercompany Document Cleared' AS ActivityName,
    h.AUGDT AS EventTime, -- Clearing Date
    'SAP_ECC' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    h.BUKRS AS CompanyCode,
    h.BELNR AS DocumentNumber,
    h.USNAM AS ResponsibleUser,
    d.HKONT AS GLAccount,
    h.BLART AS DocumentType,
    d.DMBTR AS AmountInLocalCurrency,
    'Clearing' AS JournalEntryType
FROM BKPF h
JOIN BSEG d ON h.MANDT = d.MANDT AND h.BUKRS = d.BUKRS AND h.BELNR = d.BELNR AND h.GJAHR = d.GJAHR
WHERE h.AUGDT BETWEEN '{{start_date}}' AND '{{end_date}}'
  AND h.BUKRS IN ({{company_codes}})
  AND h.BLART IN ('[Your Intercompany Doc Type]')
  AND h.AUGBL IS NOT NULL

UNION ALL

SELECT
    CONCAT(CAST(YEAR(j.SDLSTRTDT) AS VARCHAR(4)), '-', RIGHT(CONCAT('00', MONTH(j.SDLSTRTDT)), 2)) AS FinancialPeriod,
    'Foreign Currency Valuation Run' AS ActivityName,
    j.SDLSTRTDT + j.SDLSTRTTM AS EventTime,
    'SAP_ECC' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    NULL AS CompanyCode,
    j.JOBNAME AS DocumentNumber,
    j.SDLUNAME AS ResponsibleUser,
    NULL AS GLAccount,
    NULL AS DocumentType,
    NULL AS AmountInLocalCurrency,
    NULL AS JournalEntryType
FROM TBTCO j
JOIN TBTCP p ON j.JOBNAME = p.JOBNAME AND j.JOBCOUNT = p.JOBCOUNT
WHERE p.PROGNAME = 'FAGL_FCV' OR p.PROGNAME = 'SAPF100'
  AND j.SDLSTRTDT BETWEEN '{{start_date}}' AND '{{end_date}}'
  AND j.STATUS = 'F' -- Finished

UNION ALL

SELECT
    CONCAT(CAST(YEAR(j.SDLSTRTDT) AS VARCHAR(4)), '-', RIGHT(CONCAT('00', MONTH(j.SDLSTRTDT)), 2)) AS FinancialPeriod,
    'GR/IR Clearing Run Executed' AS ActivityName,
    j.SDLSTRTDT + j.SDLSTRTTM AS EventTime,
    'SAP_ECC' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    NULL AS CompanyCode,
    j.JOBNAME AS DocumentNumber,
    j.SDLUNAME AS ResponsibleUser,
    NULL AS GLAccount,
    NULL AS DocumentType,
    NULL AS AmountInLocalCurrency,
    NULL AS JournalEntryType
FROM TBTCO j
JOIN TBTCP p ON j.JOBNAME = p.JOBNAME AND j.JOBCOUNT = p.JOBCOUNT
WHERE p.PROGNAME = 'SAPF124' -- Program for F.13
  AND j.SDLSTRTDT BETWEEN '{{start_date}}' AND '{{end_date}}'
  AND j.STATUS = 'F'

UNION ALL

SELECT
    CONCAT(CAST(YEAR(j.SDLSTRTDT) AS VARCHAR(4)), '-', RIGHT(CONCAT('00', MONTH(j.SDLSTRTDT)), 2)) AS FinancialPeriod,
    'Account Reconciliation Started' AS ActivityName, -- Proxy event
    j.SDLSTRTDT + j.SDLSTRTTM AS EventTime,
    'SAP_ECC' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    NULL AS CompanyCode,
    j.JOBNAME AS DocumentNumber,
    j.SDLUNAME AS ResponsibleUser,
    NULL AS GLAccount,
    NULL AS DocumentType,
    NULL AS AmountInLocalCurrency,
    NULL AS JournalEntryType
FROM TBTCO j
JOIN TBTCP p ON j.JOBNAME = p.JOBNAME AND j.JOBCOUNT = p.JOBCOUNT
WHERE p.PROGNAME IN ('FAGLL03', 'FBL3N') -- Common reconciliation reports
  AND j.SDLSTRTDT BETWEEN '{{start_date}}' AND '{{end_date}}'
  AND j.STATUS = 'F'

UNION ALL

SELECT
    CONCAT(CAST(h.GJAHR AS VARCHAR(4)), '-', RIGHT(CONCAT('00', h.MONAT), 2)) AS FinancialPeriod,
    'Adjusting Journal Entry Posted' AS ActivityName,
    h.CPUDT + h.CPUTM AS EventTime,
    'SAP_ECC' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    h.BUKRS AS CompanyCode,
    h.BELNR AS DocumentNumber,
    h.USNAM AS ResponsibleUser,
    d.HKONT AS GLAccount,
    h.BLART AS DocumentType,
    d.DMBTR AS AmountInLocalCurrency,
    'Adjusting' AS JournalEntryType
FROM BKPF h
JOIN BSEG d ON h.MANDT = d.MANDT AND h.BUKRS = d.BUKRS AND h.BELNR = d.BELNR AND h.GJAHR = d.GJAHR
WHERE h.BUDAT BETWEEN '{{start_date}}' AND '{{end_date}}'
  AND h.BUKRS IN ({{company_codes}})
  AND h.BLART IN ('[Your Adjusting Doc Type]')

UNION ALL

SELECT
    CONCAT(CAST(h.GJAHR AS VARCHAR(4)), '-', RIGHT(CONCAT('00', h.MONAT), 2)) AS FinancialPeriod,
    'Reversal Entry Posted' AS ActivityName,
    h.CPUDT + h.CPUTM AS EventTime,
    'SAP_ECC' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    h.BUKRS AS CompanyCode,
    h.BELNR AS DocumentNumber,
    h.USNAM AS ResponsibleUser,
    d.HKONT AS GLAccount,
    h.BLART AS DocumentType,
    d.DMBTR AS AmountInLocalCurrency,
    'Reversal' AS JournalEntryType
FROM BKPF h
JOIN BSEG d ON h.MANDT = d.MANDT AND h.BUKRS = d.BUKRS AND h.BELNR = d.BELNR AND h.GJAHR = d.GJAHR
WHERE h.BUDAT BETWEEN '{{start_date}}' AND '{{end_date}}'
  AND h.BUKRS IN ({{company_codes}})
  AND h.STBLG IS NOT NULL -- STBLG links to the reversed document

UNION ALL

SELECT
    CONCAT(CAST(YEAR(j.SDLSTRTDT) AS VARCHAR(4)), '-', RIGHT(CONCAT('00', MONTH(j.SDLSTRTDT)), 2)) AS FinancialPeriod,
    'Trial Balance Report Generated' AS ActivityName,
    j.SDLSTRTDT + j.SDLSTRTTM AS EventTime,
    'SAP_ECC' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    NULL AS CompanyCode,
    j.JOBNAME AS DocumentNumber,
    j.SDLUNAME AS ResponsibleUser,
    NULL AS GLAccount,
    NULL AS DocumentType,
    NULL AS AmountInLocalCurrency,
    NULL AS JournalEntryType
FROM TBTCO j
JOIN TBTCP p ON j.JOBNAME = p.JOBNAME AND j.JOBCOUNT = p.JOBCOUNT
WHERE p.PROGNAME IN ('RFSSLD00', 'S_ALR_87012310')
  AND j.SDLSTRTDT BETWEEN '{{start_date}}' AND '{{end_date}}'
  AND j.STATUS = 'F'

UNION ALL

SELECT
    CONCAT(CAST(YEAR(j.SDLSTRTDT) AS VARCHAR(4)), '-', RIGHT(CONCAT('00', MONTH(j.SDLSTRTDT)), 2)) AS FinancialPeriod,
    'Financial Statements Generated' AS ActivityName,
    j.SDLSTRTDT + j.SDLSTRTTM AS EventTime,
    'SAP_ECC' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    NULL AS CompanyCode,
    j.JOBNAME AS DocumentNumber,
    j.SDLUNAME AS ResponsibleUser,
    NULL AS GLAccount,
    NULL AS DocumentType,
    NULL AS AmountInLocalCurrency,
    NULL AS JournalEntryType
FROM TBTCO j
JOIN TBTCP p ON j.JOBNAME = p.JOBNAME AND j.JOBCOUNT = p.JOBCOUNT
WHERE p.PROGNAME = 'RFBILA00' -- Program for F.01
  AND j.SDLSTRTDT BETWEEN '{{start_date}}' AND '{{end_date}}'
  AND j.STATUS = 'F'

-- UNION ALL

-- Note: 'Reconciliation Reviewed' is typically not a standard, logged event in SAP ECC.
-- This activity often happens offline or in a custom tool.
-- The following is a placeholder to be adapted if a custom (Z-table) log exists.
-- SELECT
--     CONCAT(CAST(YEAR(z.REVIEW_DATE) AS VARCHAR(4)), '-', RIGHT(CONCAT('00', MONTH(z.REVIEW_DATE)), 2)) AS FinancialPeriod,
--     'Reconciliation Reviewed' AS ActivityName,
--     z.REVIEW_DATE AS EventTime,
--     'SAP_ECC' AS SourceSystem,
--     CURRENT_TIMESTAMP AS LastDataUpdate,
--     z.BUKRS AS CompanyCode,
--     z.HKONT AS DocumentNumber, -- Using GL Account as a proxy identifier
--     z.REVIEWER_USER AS ResponsibleUser,
--     z.HKONT AS GLAccount,
--     NULL AS DocumentType,
--     NULL AS AmountInLocalCurrency,
--     NULL AS JournalEntryType
-- FROM ZRECON_LOG z -- Replace with your custom table for reconciliation status
-- WHERE z.REVIEW_DATE BETWEEN '{{start_date}}' AND '{{end_date}}'
--   AND z.STATUS = 'Reviewed'
;