Uw Record to Report - Periodeafsluiting & Afstemming Data Template

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]';

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]';