Votre modèle de données Record to Report : clôture de période et rapprochement

SELECT
  CONCAT(CAST(PostingDate AS DATE FORMAT 'YYYY'), '-', CAST(PostingDate AS DATE FORMAT 'MM')) AS FinancialPeriod,
  'Period Opened For Posting' AS ActivityName,
  CAST(CreationDateTime AS TIMESTAMP) AS EventTime,
  CAST(CreationDateTime AS TIMESTAMP) AS EndTime,
  CreatedByUser AS ResponsibleUser,
  CompanyCode,
  NULL AS GlAccountNumber,
  NULL AS DocumentNumber,
  NULL AS ReconciliationStatus,
  NULL AS TargetCompletionDate,
  0 AS ProcessingTime
FROM I_FinPostingPeriodControl -- This is a proxy; this activity is a configuration change often found in change logs (CDHDR/CDPOS for table T001B)
WHERE PostingPeriodVariant = '[Your Variant]' AND FiscalYear = '[Your Fiscal Year]' AND FromPostingPeriod = 1 AND AuthorizationGroup IS NOT NULL -- Logic to detect the opening event

UNION ALL

SELECT
  CONCAT(SUBSTRING(JobName, STRPOS(JobName, '_') + 1, 4), '-', SUBSTRING(JobName, STRPOS(JobName, '_') + 5, 2)) AS FinancialPeriod, -- Infer period from Job Name if patterned, otherwise use job start date
  'Subledger Data Transfer Completed' AS ActivityName,
  CAST(JobStartDate || JobStartTime AS TIMESTAMP) AS EventTime,
  CAST(JobEndDate || JobEndTime AS TIMESTAMP) AS EndTime,
  JobCreatedByUser AS ResponsibleUser,
  '[Configure based on your system]' AS CompanyCode,
  NULL AS GlAccountNumber,
  NULL AS DocumentNumber,
  NULL AS ReconciliationStatus,
  NULL AS TargetCompletionDate,
  SECONDS_BETWEEN(CAST(JobStartDate || JobStartTime AS TIMESTAMP), CAST(JobEndDate || JobEndTime AS TIMESTAMP)) AS ProcessingTime
FROM I_BatchJob
WHERE JobName LIKE '[Subledger Job Prefix]%' AND JobStatus = 'F' -- 'F' for Finished
  AND JobEndDate BETWEEN '[Start Date]' AND '[End Date]'

UNION ALL

SELECT
  CONCAT(CAST(CreationDate AS DATE FORMAT 'YYYY'), '-', CAST(CreationDate AS DATE FORMAT 'MM')) AS FinancialPeriod,
  'Intercompany Reconciliation Started' AS ActivityName,
  CAST(CreationDateTime AS TIMESTAMP) AS EventTime,
  NULL AS EndTime,
  CreatedByUser AS ResponsibleUser,
  LeadingCompanyCode AS CompanyCode,
  NULL AS GlAccountNumber,
  ReconciliationCase AS DocumentNumber,
  ReconciliationCaseStatus AS ReconciliationStatus,
  NULL AS TargetCompletionDate,
  NULL AS ProcessingTime
FROM I_ICRcnCase -- Part of SAP S/4HANA for group reporting
WHERE CreationDate BETWEEN '[Start Date]' AND '[End Date]'

UNION ALL

SELECT
  CONCAT(FiscalYear, '-', LPAD(FiscalPeriod, 2, '0')) AS FinancialPeriod,
  'Accruals And Provisions Posted' AS ActivityName,
  CAST(CreationDateTime AS TIMESTAMP) AS EventTime,
  CAST(CreationDateTime AS TIMESTAMP) AS EndTime,
  CreatedByUser AS ResponsibleUser,
  CompanyCode,
  GLAccountNumber,
  AccountingDocument AS DocumentNumber,
  NULL AS ReconciliationStatus,
  NULL AS TargetCompletionDate,
  0 AS ProcessingTime
FROM I_JournalEntryItem
WHERE DocumentType IN ('[Accrual Doc Type 1]', '[Accrual Doc Type 2]') -- e.g., 'SA', 'AC'
  AND PostingDate BETWEEN '[Start Date]' AND '[End Date]'

UNION ALL

SELECT
  CONCAT(SUBSTRING(JobName, STRPOS(JobName, '_') + 1, 4), '-', SUBSTRING(JobName, STRPOS(JobName, '_') + 5, 2)) AS FinancialPeriod,
  'Automated Clearing Run Executed' AS ActivityName,
  CAST(JobStartDate || JobStartTime AS TIMESTAMP) AS EventTime,
  CAST(JobEndDate || JobEndTime AS TIMESTAMP) AS EndTime,
  JobCreatedByUser AS ResponsibleUser,
  '[Configure based on your system]' AS CompanyCode,
  NULL AS GlAccountNumber,
  NULL AS DocumentNumber,
  NULL AS ReconciliationStatus,
  NULL AS TargetCompletionDate,
  SECONDS_BETWEEN(CAST(JobStartDate || JobStartTime AS TIMESTAMP), CAST(JobEndDate || JobEndTime AS TIMESTAMP)) AS ProcessingTime
FROM I_BatchJob
WHERE JobName LIKE 'SAPF124%' AND JobStatus = 'F'
  AND JobEndDate BETWEEN '[Start Date]' AND '[End Date]'

UNION ALL

SELECT
  CONCAT(SUBSTRING(JobName, STRPOS(JobName, '_') + 1, 4), '-', SUBSTRING(JobName, STRPOS(JobName, '_') + 5, 2)) AS FinancialPeriod,
  'Foreign Currency Valuation Run' AS ActivityName,
  CAST(JobStartDate || JobStartTime AS TIMESTAMP) AS EventTime,
  CAST(JobEndDate || JobEndTime AS TIMESTAMP) AS EndTime,
  JobCreatedByUser AS ResponsibleUser,
  '[Configure based on your system]' AS CompanyCode,
  NULL AS GlAccountNumber,
  NULL AS DocumentNumber,
  NULL AS ReconciliationStatus,
  NULL AS TargetCompletionDate,
  SECONDS_BETWEEN(CAST(JobStartDate || JobStartTime AS TIMESTAMP), CAST(JobEndDate || JobEndTime AS TIMESTAMP)) AS ProcessingTime
FROM I_BatchJob
WHERE JobName LIKE 'FAGL_FCV%' AND JobStatus = 'F'
  AND JobEndDate BETWEEN '[Start Date]' AND '[End Date]'

UNION ALL

-- This activity often requires a custom view on a custom table or an add-on like SAP Account Substantiation and Automation.
-- The following is a placeholder for such a custom view.
SELECT
  CONCAT(FiscalYear, '-', LPAD(FiscalPeriod, 2, '0')) AS FinancialPeriod,
  'Account Reconciliation Started' AS ActivityName,
  CAST(ChangedAt AS TIMESTAMP) AS EventTime,
  NULL AS EndTime,
  ChangedBy AS ResponsibleUser,
  CompanyCode,
  GLAccountNumber,
  ReconciliationID AS DocumentNumber,
  'In Progress' AS ReconciliationStatus,
  PlannedCompletionDate AS TargetCompletionDate,
  NULL AS ProcessingTime
FROM Z_I_ACCOUNTRECONCILIATION -- Placeholder for your custom reconciliation status view
WHERE Status = 'IN_PROGRESS' AND OldStatus = 'NEW'
  AND ChangeDate BETWEEN '[Start Date]' AND '[End Date]'

UNION ALL

SELECT
  CONCAT(FiscalYear, '-', LPAD(FiscalPeriod, 2, '0')) AS FinancialPeriod,
  'Adjusting Journal Entry Posted' AS ActivityName,
  CAST(CreationDateTime AS TIMESTAMP) AS EventTime,
  CAST(CreationDateTime AS TIMESTAMP) AS EndTime,
  CreatedByUser AS ResponsibleUser,
  CompanyCode,
  GLAccountNumber,
  AccountingDocument AS DocumentNumber,
  NULL AS ReconciliationStatus,
  NULL AS TargetCompletionDate,
  0 AS ProcessingTime
FROM I_JournalEntryItem
WHERE DocumentType IN ('[Adjustment Doc Type 1]', '[Adjustment Doc Type 2]') -- e.g., 'AJ'
  AND PostingDate BETWEEN '[Start Date]' AND '[End Date]'

UNION ALL

-- This activity often requires a custom view on a custom table or an add-on product.
-- The following is a placeholder for such a custom view.
SELECT
  CONCAT(FiscalYear, '-', LPAD(FiscalPeriod, 2, '0')) AS FinancialPeriod,
  'Account Reconciliation Approved' AS ActivityName,
  CAST(ChangedAt AS TIMESTAMP) AS EventTime,
  CAST(ChangedAt AS TIMESTAMP) AS EndTime,
  ApprovedBy AS ResponsibleUser,
  CompanyCode,
  GLAccountNumber,
  ReconciliationID AS DocumentNumber,
  'Approved' AS ReconciliationStatus,
  PlannedCompletionDate AS TargetCompletionDate,
  NULL AS ProcessingTime
FROM Z_I_ACCOUNTRECONCILIATION -- Placeholder for your custom reconciliation status view
WHERE Status = 'APPROVED'
  AND ChangeDate BETWEEN '[Start Date]' AND '[End Date]'

UNION ALL

SELECT
  CONCAT(FiscalYear, '-', LPAD(FiscalPeriod, 2, '0')) AS FinancialPeriod,
  'Consolidation Tasks Executed' AS ActivityName,
  CAST(TaskExecutionUTCTimestamp AS TIMESTAMP) AS EventTime,
  CAST(TaskCompletionUTCTimestamp AS TIMESTAMP) AS EndTime,
  ExecutedByUser AS ResponsibleUser,
  ConsolidationUnitCompanyCode AS CompanyCode, -- Assuming mapping exists
  NULL AS GlAccountNumber,
  DocumentNumber,
  NULL AS ReconciliationStatus,
  NULL AS TargetCompletionDate,
  SECONDS_BETWEEN(CAST(TaskExecutionUTCTimestamp AS TIMESTAMP), CAST(TaskCompletionUTCTimestamp AS TIMESTAMP)) AS ProcessingTime
FROM I_ConsolidationTaskLog
WHERE ConsolidationTaskStatus = 'S' -- Successful
  AND TaskExecutionUTCDate BETWEEN '[Start Date]' AND '[End Date]'

UNION ALL

SELECT
  CONCAT(SUBSTRING(JobName, STRPOS(JobName, '_') + 1, 4), '-', SUBSTRING(JobName, STRPOS(JobName, '_') + 5, 2)) AS FinancialPeriod,
  'Trial Balance Prepared' AS ActivityName,
  CAST(JobStartDate || JobStartTime AS TIMESTAMP) AS EventTime,
  CAST(JobEndDate || JobEndTime AS TIMESTAMP) AS EndTime,
  JobCreatedByUser AS ResponsibleUser,
  '[Configure based on your system]' AS CompanyCode,
  NULL AS GlAccountNumber,
  NULL AS DocumentNumber,
  NULL AS ReconciliationStatus,
  NULL AS TargetCompletionDate,
  SECONDS_BETWEEN(CAST(JobStartDate || JobStartTime AS TIMESTAMP), CAST(JobEndDate || JobEndTime AS TIMESTAMP)) AS ProcessingTime
FROM I_BatchJob
WHERE JobName LIKE '[Trial Balance Job Prefix]%' AND JobStatus = 'F' -- Example: FAGL_TRIAL_BALANCE_RUN
  AND JobEndDate BETWEEN '[Start Date]' AND '[End Date]'

UNION ALL

SELECT
  CONCAT(SUBSTRING(JobName, STRPOS(JobName, '_') + 1, 4), '-', SUBSTRING(JobName, STRPOS(JobName, '_') + 5, 2)) AS FinancialPeriod,
  'Financial Statements Generated' AS ActivityName,
  CAST(JobStartDate || JobStartTime AS TIMESTAMP) AS EventTime,
  CAST(JobEndDate || JobEndTime AS TIMESTAMP) AS EndTime,
  JobCreatedByUser AS ResponsibleUser,
  '[Configure based on your system]' AS CompanyCode,
  NULL AS GlAccountNumber,
  NULL AS DocumentNumber,
  NULL AS ReconciliationStatus,
  NULL AS TargetCompletionDate,
  SECONDS_BETWEEN(CAST(JobStartDate || JobStartTime AS TIMESTAMP), CAST(JobEndDate || JobEndTime AS TIMESTAMP)) AS ProcessingTime
FROM I_BatchJob
WHERE JobName LIKE '[Fin Statement Job Prefix]%' AND JobStatus = 'F'
  AND JobEndDate BETWEEN '[Start Date]' AND '[End Date]'

UNION ALL

-- Approval is often a manual step. This requires a custom solution to capture.
-- The following is a placeholder for such a custom view.
SELECT
  CONCAT(ApprovalYear, '-', LPAD(ApprovalPeriod, 2, '0')) AS FinancialPeriod,
  'Financial Statements Approved' AS ActivityName,
  CAST(ApprovalTimestamp AS TIMESTAMP) AS EventTime,
  CAST(ApprovalTimestamp AS TIMESTAMP) AS EndTime,
  ApproverUser AS ResponsibleUser,
  CompanyCode,
  NULL AS GlAccountNumber,
  FinancialStatementVersion AS DocumentNumber,
  'Approved' AS ReconciliationStatus,
  NULL AS TargetCompletionDate,
  0 AS ProcessingTime
FROM Z_I_FINSTATEMENTAPPROVAL -- Placeholder for custom approval tracking view
WHERE ApprovalDate BETWEEN '[Start Date]' AND '[End Date]'

UNION ALL

SELECT
  CONCAT(CAST(LastChangeDate AS DATE FORMAT 'YYYY'), '-', CAST(LastChangeDate AS DATE FORMAT 'MM')) AS FinancialPeriod,
  'Period Closed For Posting' AS ActivityName,
  CAST(LastChangeDateTime AS TIMESTAMP) AS EventTime,
  CAST(LastChangeDateTime AS TIMESTAMP) AS EndTime,
  LastChangedByUser AS ResponsibleUser,
  CompanyCode,
  NULL AS GlAccountNumber,
  NULL AS DocumentNumber,
  NULL AS ReconciliationStatus,
  NULL AS TargetCompletionDate,
  0 AS ProcessingTime
FROM I_FinPostingPeriodControl -- Proxy, see note on 'Period Opened'
WHERE PostingPeriodStatus = 'C' -- 'C' for Closed
  AND LastChangeDate BETWEEN '[Start Date]' AND '[End Date]';

WITH
params AS (
    SELECT
        TO_DATE('[Start date YYYY-MM-DD]') AS start_date,
        TO_DATE('[End date YYYY-MM-DD]') AS end_date,
        '[Fiscal year]' AS fiscal_year_filter,
        '[Fiscal period]' AS fiscal_period_filter,
        '[Company code filter]' AS company_code_filter
    FROM DUMMY
),
base_events AS (
    SELECT
        TO_NVARCHAR(a.RYEAR) || '-' || LPAD(TO_NVARCHAR(a.POPER), 2, '0') AS FinancialPeriod,
        'Accruals And Provisions Posted' AS ActivityName,
        CAST(a.BUDAT AS TIMESTAMP) AS EventTime,
        CAST(NULL AS TIMESTAMP) AS EndTime,
        CAST(a.USNAM AS NVARCHAR(80)) AS ResponsibleUser,
        CAST(a.RBUKRS AS NVARCHAR(20)) AS CompanyCode,
        CAST(a.RACCT AS NVARCHAR(40)) AS GlAccountNumber,
        CAST(a.BELNR AS NVARCHAR(40)) AS DocumentNumber,
        CAST(NULL AS NVARCHAR(40)) AS ReconciliationStatus,
        CAST(NULL AS DATE) AS TargetCompletionDate
    FROM [Your SAP schema].ACDOCA a
    CROSS JOIN params p
    WHERE a.BUDAT >= p.start_date
      AND a.BUDAT < ADD_DAYS(p.end_date, 1)
      AND (p.fiscal_year_filter = '[Fiscal year]' OR TO_NVARCHAR(a.RYEAR) = p.fiscal_year_filter)
      AND (p.fiscal_period_filter = '[Fiscal period]' OR TO_NVARCHAR(a.POPER) = p.fiscal_period_filter)
      AND (p.company_code_filter = '[Company code filter]' OR TO_NVARCHAR(a.RBUKRS) = p.company_code_filter)
      AND a.BLART IN ('[Accrual document type]', '[Provision document type]')

    UNION ALL

    SELECT
        TO_NVARCHAR(a.RYEAR) || '-' || LPAD(TO_NVARCHAR(a.POPER), 2, '0'),
        'Adjusting Journal Entry Posted',
        CAST(a.BUDAT AS TIMESTAMP),
        CAST(NULL AS TIMESTAMP),
        CAST(a.USNAM AS NVARCHAR(80)),
        CAST(a.RBUKRS AS NVARCHAR(20)),
        CAST(a.RACCT AS NVARCHAR(40)),
        CAST(a.BELNR AS NVARCHAR(40)),
        CAST(NULL AS NVARCHAR(40)),
        CAST(NULL AS DATE)
    FROM [Your SAP schema].ACDOCA a
    CROSS JOIN params p
    WHERE a.BUDAT >= p.start_date
      AND a.BUDAT < ADD_DAYS(p.end_date, 1)
      AND (p.fiscal_year_filter = '[Fiscal year]' OR TO_NVARCHAR(a.RYEAR) = p.fiscal_year_filter)
      AND (p.fiscal_period_filter = '[Fiscal period]' OR TO_NVARCHAR(a.POPER) = p.fiscal_period_filter)
      AND (p.company_code_filter = '[Company code filter]' OR TO_NVARCHAR(a.RBUKRS) = p.company_code_filter)
      AND a.BLART IN ('[Adjusting journal entry document type]')

    UNION ALL

    SELECT
        TO_NVARCHAR(a.RYEAR) || '-' || LPAD(TO_NVARCHAR(a.POPER), 2, '0'),
        'Foreign Currency Valuation Run',
        CAST(a.BUDAT AS TIMESTAMP),
        CAST(NULL AS TIMESTAMP),
        CAST(a.USNAM AS NVARCHAR(80)),
        CAST(a.RBUKRS AS NVARCHAR(20)),
        CAST(a.RACCT AS NVARCHAR(40)),
        CAST(a.BELNR AS NVARCHAR(40)),
        CAST(NULL AS NVARCHAR(40)),
        CAST(NULL AS DATE)
    FROM [Your SAP schema].ACDOCA a
    CROSS JOIN params p
    WHERE a.BUDAT >= p.start_date
      AND a.BUDAT < ADD_DAYS(p.end_date, 1)
      AND (p.fiscal_year_filter = '[Fiscal year]' OR TO_NVARCHAR(a.RYEAR) = p.fiscal_year_filter)
      AND (p.fiscal_period_filter = '[Fiscal period]' OR TO_NVARCHAR(a.POPER) = p.fiscal_period_filter)
      AND (p.company_code_filter = '[Company code filter]' OR TO_NVARCHAR(a.RBUKRS) = p.company_code_filter)
      AND a.BLART IN ('[Foreign currency valuation document type]')

    UNION ALL

    SELECT
        TO_NVARCHAR(a.RYEAR) || '-' || LPAD(TO_NVARCHAR(a.POPER), 2, '0'),
        'Automated Clearing Run Executed',
        CAST(a.BUDAT AS TIMESTAMP),
        CAST(NULL AS TIMESTAMP),
        CAST(a.USNAM AS NVARCHAR(80)),
        CAST(a.RBUKRS AS NVARCHAR(20)),
        CAST(a.RACCT AS NVARCHAR(40)),
        CAST(a.BELNR AS NVARCHAR(40)),
        CAST(NULL AS NVARCHAR(40)),
        CAST(NULL AS DATE)
    FROM [Your SAP schema].ACDOCA a
    CROSS JOIN params p
    WHERE a.BUDAT >= p.start_date
      AND a.BUDAT < ADD_DAYS(p.end_date, 1)
      AND (p.fiscal_year_filter = '[Fiscal year]' OR TO_NVARCHAR(a.RYEAR) = p.fiscal_year_filter)
      AND (p.fiscal_period_filter = '[Fiscal period]' OR TO_NVARCHAR(a.POPER) = p.fiscal_period_filter)
      AND (p.company_code_filter = '[Company code filter]' OR TO_NVARCHAR(a.RBUKRS) = p.company_code_filter)
      AND a.BLART IN ('[Automated clearing document type]')

    UNION ALL

    SELECT
        CAST(j.[Financial period column] AS NVARCHAR(20)),
        'Subledger Data Transfer Completed',
        CAST(j.[Job end timestamp column] AS TIMESTAMP),
        CAST(j.[Job end timestamp column] AS TIMESTAMP),
        CAST(j.[Job user column] AS NVARCHAR(80)),
        CAST(j.[Company code column] AS NVARCHAR(20)),
        CAST(NULL AS NVARCHAR(40)),
        CAST(j.[Job name column] AS NVARCHAR(40)),
        CAST(NULL AS NVARCHAR(40)),
        CAST(NULL AS DATE)
    FROM [Your SAP schema].TBTCO j
    CROSS JOIN params p
    WHERE j.[Job end timestamp column] >= p.start_date
      AND j.[Job end timestamp column] < ADD_DAYS(p.end_date, 1)
      AND j.[Job status column] = '[Successful job status]'
      AND j.[Job name column] IN ('[AR transfer job]', '[AP transfer job]', '[Asset Accounting transfer job]')

    UNION ALL

    SELECT
        CAST(j.[Financial period column] AS NVARCHAR(20)),
        'Intercompany Reconciliation Started',
        CAST(j.[Job start timestamp column] AS TIMESTAMP),
        CAST(j.[Job end timestamp column] AS TIMESTAMP),
        CAST(j.[Job user column] AS NVARCHAR(80)),
        CAST(j.[Company code column] AS NVARCHAR(20)),
        CAST(NULL AS NVARCHAR(40)),
        CAST(j.[Job name column] AS NVARCHAR(40)),
        CAST(NULL AS NVARCHAR(40)),
        CAST(NULL AS DATE)
    FROM [Your SAP schema].TBTCO j
    CROSS JOIN params p
    WHERE j.[Job start timestamp column] >= p.start_date
      AND j.[Job start timestamp column] < ADD_DAYS(p.end_date, 1)
      AND j.[Job name column] IN ('[Intercompany reconciliation job]')

    UNION ALL

    SELECT
        CAST(r.[Financial period column] AS NVARCHAR(20)),
        'Intercompany Reconciliation Started',
        CAST(r.[Status change timestamp column] AS TIMESTAMP),
        CAST(NULL AS TIMESTAMP),
        CAST(r.[Responsible user column] AS NVARCHAR(80)),
        CAST(r.[Company code column] AS NVARCHAR(20)),
        CAST(NULL AS NVARCHAR(40)),
        CAST(r.[Reconciliation case column] AS NVARCHAR(40)),
        CAST(r.[Status column] AS NVARCHAR(40)),
        CAST(r.[Target completion date column] AS DATE)
    FROM [Your table name for intercompany reconciliation] r
    CROSS JOIN params p
    WHERE r.[Status change timestamp column] >= p.start_date
      AND r.[Status change timestamp column] < ADD_DAYS(p.end_date, 1)
      AND r.[Status column] IN ('Started', 'In Progress')

    UNION ALL

    SELECT
        CAST(r.[Financial period column] AS NVARCHAR(20)),
        'Account Reconciliation Started',
        CAST(r.[Status change timestamp column] AS TIMESTAMP),
        CAST(NULL AS TIMESTAMP),
        CAST(r.[Responsible user column] AS NVARCHAR(80)),
        CAST(r.[Company code column] AS NVARCHAR(20)),
        CAST(r.[GL account column] AS NVARCHAR(40)),
        CAST(r.[Reconciliation case column] AS NVARCHAR(40)),
        CAST(r.[Status column] AS NVARCHAR(40)),
        CAST(r.[Target completion date column] AS DATE)
    FROM [Your table name for account reconciliation] r
    CROSS JOIN params p
    WHERE r.[Status change timestamp column] >= p.start_date
      AND r.[Status change timestamp column] < ADD_DAYS(p.end_date, 1)
      AND r.[Status column] IN ('In Progress')

    UNION ALL

    SELECT
        CAST(r.[Financial period column] AS NVARCHAR(20)),
        'Account Reconciliation Approved',
        CAST(r.[Status change timestamp column] AS TIMESTAMP),
        CAST(NULL AS TIMESTAMP),
        CAST(r.[Approver user column] AS NVARCHAR(80)),
        CAST(r.[Company code column] AS NVARCHAR(20)),
        CAST(r.[GL account column] AS NVARCHAR(40)),
        CAST(r.[Reconciliation case column] AS NVARCHAR(40)),
        CAST(r.[Status column] AS NVARCHAR(40)),
        CAST(r.[Target completion date column] AS DATE)
    FROM [Your table name for account reconciliation] r
    CROSS JOIN params p
    WHERE r.[Status change timestamp column] >= p.start_date
      AND r.[Status change timestamp column] < ADD_DAYS(p.end_date, 1)
      AND r.[Status column] = 'Approved'

    UNION ALL

    SELECT
        CAST(c.[Financial period column] AS NVARCHAR(20)),
        'Consolidation Tasks Executed',
        CAST(c.[Execution start timestamp column] AS TIMESTAMP),
        CAST(c.[Execution end timestamp column] AS TIMESTAMP),
        CAST(c.[Responsible user column] AS NVARCHAR(80)),
        CAST(c.[Company code column] AS NVARCHAR(20)),
        CAST(NULL AS NVARCHAR(40)),
        CAST(c.[Task identifier column] AS NVARCHAR(40)),
        CAST(c.[Status column] AS NVARCHAR(40)),
        CAST(c.[Target completion date column] AS DATE)
    FROM [Your table name for consolidation execution] c
    CROSS JOIN params p
    WHERE c.[Execution start timestamp column] >= p.start_date
      AND c.[Execution start timestamp column] < ADD_DAYS(p.end_date, 1)
      AND c.[Status column] IN ('Completed', 'Successful')

    UNION ALL

    SELECT
        CAST(t.[Financial period column] AS NVARCHAR(20)),
        'Trial Balance Prepared',
        CAST(t.[Report saved timestamp column] AS TIMESTAMP),
        CAST(t.[Report saved timestamp column] AS TIMESTAMP),
        CAST(t.[Responsible user column] AS NVARCHAR(80)),
        CAST(t.[Company code column] AS NVARCHAR(20)),
        CAST(NULL AS NVARCHAR(40)),
        CAST(t.[Report identifier column] AS NVARCHAR(40)),
        CAST(t.[Status column] AS NVARCHAR(40)),
        CAST(t.[Target completion date column] AS DATE)
    FROM [Your table name for trial balance output] t
    CROSS JOIN params p
    WHERE t.[Report saved timestamp column] >= p.start_date
      AND t.[Report saved timestamp column] < ADD_DAYS(p.end_date, 1)
      AND t.[Status column] IN ('Saved', 'Completed')

    UNION ALL

    SELECT
        CAST(f.[Financial period column] AS NVARCHAR(20)),
        'Financial Statements Generated',
        CAST(f.[Generation timestamp column] AS TIMESTAMP),
        CAST(f.[Generation timestamp column] AS TIMESTAMP),
        CAST(f.[Responsible user column] AS NVARCHAR(80)),
        CAST(f.[Company code column] AS NVARCHAR(20)),
        CAST(NULL AS NVARCHAR(40)),
        CAST(f.[Report identifier column] AS NVARCHAR(40)),
        CAST(f.[Status column] AS NVARCHAR(40)),
        CAST(f.[Target completion date column] AS DATE)
    FROM [Your table name for financial statement output] f
    CROSS JOIN params p
    WHERE f.[Generation timestamp column] >= p.start_date
      AND f.[Generation timestamp column] < ADD_DAYS(p.end_date, 1)
      AND f.[Status column] IN ('Generated', 'Saved', 'Completed')

    UNION ALL

    SELECT
        CAST(f.[Financial period column] AS NVARCHAR(20)),
        'Financial Statements Approved',
        CAST(f.[Approval timestamp column] AS TIMESTAMP),
        CAST(f.[Approval timestamp column] AS TIMESTAMP),
        CAST(f.[Approver user column] AS NVARCHAR(80)),
        CAST(f.[Company code column] AS NVARCHAR(20)),
        CAST(NULL AS NVARCHAR(40)),
        CAST(f.[Report identifier column] AS NVARCHAR(40)),
        CAST(f.[Status column] AS NVARCHAR(40)),
        CAST(f.[Target completion date column] AS DATE)
    FROM [Your table name for financial statement approval] f
    CROSS JOIN params p
    WHERE f.[Approval timestamp column] >= p.start_date
      AND f.[Approval timestamp column] < ADD_DAYS(p.end_date, 1)
      AND f.[Status column] = 'Approved'

    UNION ALL

    SELECT
        CAST(x.[Financial period column] AS NVARCHAR(20)),
        'Period Opened For Posting',
        CAST(x.[Change timestamp column] AS TIMESTAMP),
        CAST(x.[Change timestamp column] AS TIMESTAMP),
        CAST(x.[Changed by column] AS NVARCHAR(80)),
        CAST(x.[Company code column] AS NVARCHAR(20)),
        CAST(NULL AS NVARCHAR(40)),
        CAST(x.[Change document number column] AS NVARCHAR(40)),
        CAST(x.[New status column] AS NVARCHAR(40)),
        CAST(NULL AS DATE)
    FROM [Your table name for posting period changes] x
    CROSS JOIN params p
    WHERE x.[Change timestamp column] >= p.start_date
      AND x.[Change timestamp column] < ADD_DAYS(p.end_date, 1)
      AND x.[New status column] = 'Open'

    UNION ALL

    SELECT
        CAST(x.[Financial period column] AS NVARCHAR(20)),
        'Period Closed For Posting',
        CAST(x.[Change timestamp column] AS TIMESTAMP),
        CAST(x.[Change timestamp column] AS TIMESTAMP),
        CAST(x.[Changed by column] AS NVARCHAR(80)),
        CAST(x.[Company code column] AS NVARCHAR(20)),
        CAST(NULL AS NVARCHAR(40)),
        CAST(x.[Change document number column] AS NVARCHAR(40)),
        CAST(x.[New status column] AS NVARCHAR(40)),
        CAST(NULL AS DATE)
    FROM [Your table name for posting period changes] x
    CROSS JOIN params p
    WHERE x.[Change timestamp column] >= p.start_date
      AND x.[Change timestamp column] < ADD_DAYS(p.end_date, 1)
      AND x.[New status column] = 'Closed'
),
activity_list AS (
    SELECT 'Period Opened For Posting' AS ActivityName FROM DUMMY
    UNION ALL SELECT 'Subledger Data Transfer Completed' FROM DUMMY
    UNION ALL SELECT 'Intercompany Reconciliation Started' FROM DUMMY
    UNION ALL SELECT 'Accruals And Provisions Posted' FROM DUMMY
    UNION ALL SELECT 'Automated Clearing Run Executed' FROM DUMMY
    UNION ALL SELECT 'Foreign Currency Valuation Run' FROM DUMMY
    UNION ALL SELECT 'Account Reconciliation Started' FROM DUMMY
    UNION ALL SELECT 'Adjusting Journal Entry Posted' FROM DUMMY
    UNION ALL SELECT 'Account Reconciliation Approved' FROM DUMMY
    UNION ALL SELECT 'Consolidation Tasks Executed' FROM DUMMY
    UNION ALL SELECT 'Trial Balance Prepared' FROM DUMMY
    UNION ALL SELECT 'Financial Statements Generated' FROM DUMMY
    UNION ALL SELECT 'Financial Statements Approved' FROM DUMMY
    UNION ALL SELECT 'Period Closed For Posting' FROM DUMMY
),
missing_activities AS (
    SELECT
        CAST(NULL AS NVARCHAR(20)) AS FinancialPeriod,
        'Activity Coverage Validation' AS ActivityName,
        CAST(NULL AS TIMESTAMP) AS EventTime,
        CAST(NULL AS TIMESTAMP) AS EndTime,
        CAST(NULL AS NVARCHAR(80)) AS ResponsibleUser,
        CAST(NULL AS NVARCHAR(20)) AS CompanyCode,
        CAST(NULL AS NVARCHAR(40)) AS GlAccountNumber,
        CAST(NULL AS NVARCHAR(40)) AS DocumentNumber,
        CAST('Missing activity: ' || al.ActivityName AS NVARCHAR(40)) AS ReconciliationStatus,
        CAST(NULL AS DATE) AS TargetCompletionDate
    FROM activity_list al
    LEFT JOIN base_events b ON b.ActivityName = al.ActivityName
    WHERE b.ActivityName IS NULL
)
SELECT
    FinancialPeriod,
    ActivityName,
    EventTime,
    EndTime,
    ResponsibleUser,
    CompanyCode,
    GlAccountNumber,
    DocumentNumber,
    ReconciliationStatus,
    TargetCompletionDate
FROM base_events
UNION ALL
SELECT
    FinancialPeriod,
    ActivityName,
    EventTime,
    EndTime,
    ResponsibleUser,
    CompanyCode,
    GlAccountNumber,
    DocumentNumber,
    ReconciliationStatus,
    TargetCompletionDate
FROM missing_activities
ORDER BY FinancialPeriod, EventTime, ActivityName