Jouw datatemplate voor Record to Report, periodeafsluiting en reconciliatie

// Workiva Process Report Configuration
// This is not an executable script, but a representation of the settings to be configured in the Workiva UI.

ReportType: "Process Task Detail" or "Process Audit Trail"

// Select the primary process to report on
ProcessFilter: "[Your Period Close Process Name]"

// Define the time frame for the data export
DateRange: {
  StartDate: "[YYYY-MM-DD]",
  EndDate: "[YYYY-MM-DD]"
}

// Columns to include in the exported report
// These fields will be mapped to the event log attributes during data transformation
Columns: [
  "Process Name",         // Maps to FinancialPeriod
  "Task Name",            // Used to derive ActivityName
  "Current Status",       // Used to derive ActivityName
  "Status Change Date",   // Maps to EventTimestamp
  "Assigned To User",     // Maps to ResponsibleUser
  "Completed By User",    // Maps to ResponsibleUser
  "Start Date",           // Potential timestamp for 'Started' activities
  "Completion Date",      // Potential timestamp for 'Completed' activities

  // Add custom metadata fields configured in your Workiva process
  "[Custom Field for Company Code]", // Maps to CompanyCode
  "[Custom Field for Department]",   // Maps to Department
  "[Custom Field for GL Account]",   // Maps to GLAccount
  "[Custom Field for Reconciliation Category]" // Maps to ReconciliationCategory
]

// Filters to apply to the dataset
Filters: {
  // Add any specific filters if needed, for example, by company code
  "[Custom Field for Company Code]": "[Filter by specific company codes]"
}

// Export Format
Format: "CSV"

WITH
period_close_initiated AS (
    SELECT
        FinancialPeriod,
        'Period Close Initiated' AS ActivityName,
        EventTimestamp,
        SourceSystem,
        LastDataUpdate,
        ResponsibleUser,
        Department,
        CompanyCode,
        GLAccount,
        ReconciliationStatus,
        ReconciliationCategory
    FROM [Your period close event source]
    WHERE EventTimestamp >= [Start date parameter]
      AND EventTimestamp < [End date parameter]
      AND ([Company Code filter] IS NULL OR CompanyCode = [Company Code filter])
      AND [Source activity or status condition for Period Close Initiated]
),
source_data_imported AS (
    SELECT
        FinancialPeriod,
        'Source Data Imported' AS ActivityName,
        EventTimestamp,
        SourceSystem,
        LastDataUpdate,
        ResponsibleUser,
        Department,
        CompanyCode,
        GLAccount,
        ReconciliationStatus,
        ReconciliationCategory
    FROM [Your source data load event source]
    WHERE EventTimestamp >= [Start date parameter]
      AND EventTimestamp < [End date parameter]
      AND ([Company Code filter] IS NULL OR CompanyCode = [Company Code filter])
      AND [Source activity or status condition for Source Data Imported]
),
account_reconciliation_started AS (
    SELECT
        FinancialPeriod,
        'Account Reconciliation Started' AS ActivityName,
        EventTimestamp,
        SourceSystem,
        LastDataUpdate,
        ResponsibleUser,
        Department,
        CompanyCode,
        GLAccount,
        ReconciliationStatus,
        ReconciliationCategory
    FROM [Your account reconciliation event source]
    WHERE EventTimestamp >= [Start date parameter]
      AND EventTimestamp < [End date parameter]
      AND ([Company Code filter] IS NULL OR CompanyCode = [Company Code filter])
      AND [Source activity or status condition for Account Reconciliation Started]
),
intercompany_reconciliation_started AS (
    SELECT
        FinancialPeriod,
        'Intercompany Reconciliation Started' AS ActivityName,
        EventTimestamp,
        SourceSystem,
        LastDataUpdate,
        ResponsibleUser,
        Department,
        CompanyCode,
        GLAccount,
        ReconciliationStatus,
        ReconciliationCategory
    FROM [Your intercompany reconciliation event source]
    WHERE EventTimestamp >= [Start date parameter]
      AND EventTimestamp < [End date parameter]
      AND ([Company Code filter] IS NULL OR CompanyCode = [Company Code filter])
      AND [Source activity or status condition for Intercompany Reconciliation Started]
),
reconciliation_submitted_for_review AS (
    SELECT
        FinancialPeriod,
        'Reconciliation Submitted for Review' AS ActivityName,
        EventTimestamp,
        SourceSystem,
        LastDataUpdate,
        ResponsibleUser,
        Department,
        CompanyCode,
        GLAccount,
        ReconciliationStatus,
        ReconciliationCategory
    FROM [Your reconciliation status history source]
    WHERE EventTimestamp >= [Start date parameter]
      AND EventTimestamp < [End date parameter]
      AND ([Company Code filter] IS NULL OR CompanyCode = [Company Code filter])
      AND [Source status condition for Reconciliation Submitted for Review]
),
reconciliation_reviewed_and_approved AS (
    SELECT
        FinancialPeriod,
        'Reconciliation Reviewed and Approved' AS ActivityName,
        EventTimestamp,
        SourceSystem,
        LastDataUpdate,
        ResponsibleUser,
        Department,
        CompanyCode,
        GLAccount,
        ReconciliationStatus,
        ReconciliationCategory
    FROM [Your reconciliation status history source]
    WHERE EventTimestamp >= [Start date parameter]
      AND EventTimestamp < [End date parameter]
      AND ([Company Code filter] IS NULL OR CompanyCode = [Company Code filter])
      AND [Source status condition for Reconciliation Reviewed and Approved]
),
reconciliation_rejected AS (
    SELECT
        FinancialPeriod,
        'Reconciliation Rejected' AS ActivityName,
        EventTimestamp,
        SourceSystem,
        LastDataUpdate,
        ResponsibleUser,
        Department,
        CompanyCode,
        GLAccount,
        ReconciliationStatus,
        ReconciliationCategory
    FROM [Your reconciliation status history source]
    WHERE EventTimestamp >= [Start date parameter]
      AND EventTimestamp < [End date parameter]
      AND ([Company Code filter] IS NULL OR CompanyCode = [Company Code filter])
      AND [Source status condition for Reconciliation Rejected]
),
adjusting_journal_entry_posted AS (
    SELECT
        FinancialPeriod,
        'Adjusting Journal Entry Posted' AS ActivityName,
        EventTimestamp,
        SourceSystem,
        LastDataUpdate,
        ResponsibleUser,
        Department,
        CompanyCode,
        GLAccount,
        ReconciliationStatus,
        ReconciliationCategory
    FROM [Your journal entry event source]
    WHERE EventTimestamp >= [Start date parameter]
      AND EventTimestamp < [End date parameter]
      AND ([Company Code filter] IS NULL OR CompanyCode = [Company Code filter])
      AND [Source status or journal type condition for Adjusting Journal Entry Posted]
),
trial_balance_prepared AS (
    SELECT
        FinancialPeriod,
        'Trial Balance Prepared' AS ActivityName,
        EventTimestamp,
        SourceSystem,
        LastDataUpdate,
        ResponsibleUser,
        Department,
        CompanyCode,
        GLAccount,
        ReconciliationStatus,
        ReconciliationCategory
    FROM [Your trial balance milestone source]
    WHERE EventTimestamp >= [Start date parameter]
      AND EventTimestamp < [End date parameter]
      AND ([Company Code filter] IS NULL OR CompanyCode = [Company Code filter])
      AND [Source activity or status condition for Trial Balance Prepared]
),
financial_statements_generated AS (
    SELECT
        FinancialPeriod,
        'Financial Statements Generated' AS ActivityName,
        EventTimestamp,
        SourceSystem,
        LastDataUpdate,
        ResponsibleUser,
        Department,
        CompanyCode,
        GLAccount,
        ReconciliationStatus,
        ReconciliationCategory
    FROM [Your financial statement generation source]
    WHERE EventTimestamp >= [Start date parameter]
      AND EventTimestamp < [End date parameter]
      AND ([Company Code filter] IS NULL OR CompanyCode = [Company Code filter])
      AND [Source activity or status condition for Financial Statements Generated]
),
financial_statements_reviewed AS (
    SELECT
        FinancialPeriod,
        'Financial Statements Reviewed' AS ActivityName,
        EventTimestamp,
        SourceSystem,
        LastDataUpdate,
        ResponsibleUser,
        Department,
        CompanyCode,
        GLAccount,
        ReconciliationStatus,
        ReconciliationCategory
    FROM [Your financial statement review source]
    WHERE EventTimestamp >= [Start date parameter]
      AND EventTimestamp < [End date parameter]
      AND ([Company Code filter] IS NULL OR CompanyCode = [Company Code filter])
      AND [Source activity or status condition for Financial Statements Reviewed]
),
financial_statements_approved AS (
    SELECT
        FinancialPeriod,
        'Financial Statements Approved' AS ActivityName,
        EventTimestamp,
        SourceSystem,
        LastDataUpdate,
        ResponsibleUser,
        Department,
        CompanyCode,
        GLAccount,
        ReconciliationStatus,
        ReconciliationCategory
    FROM [Your financial statement approval source]
    WHERE EventTimestamp >= [Start date parameter]
      AND EventTimestamp < [End date parameter]
      AND ([Company Code filter] IS NULL OR CompanyCode = [Company Code filter])
      AND [Source activity or status condition for Financial Statements Approved]
),
period_close_completed AS (
    SELECT
        FinancialPeriod,
        'Period Close Completed' AS ActivityName,
        EventTimestamp,
        SourceSystem,
        LastDataUpdate,
        ResponsibleUser,
        Department,
        CompanyCode,
        GLAccount,
        ReconciliationStatus,
        ReconciliationCategory
    FROM [Your period close event source]
    WHERE EventTimestamp >= [Start date parameter]
      AND EventTimestamp < [End date parameter]
      AND ([Company Code filter] IS NULL OR CompanyCode = [Company Code filter])
      AND [Source activity or status condition for Period Close Completed]
),
period_reopened AS (
    SELECT
        FinancialPeriod,
        'Period Re-Opened' AS ActivityName,
        EventTimestamp,
        SourceSystem,
        LastDataUpdate,
        ResponsibleUser,
        Department,
        CompanyCode,
        GLAccount,
        ReconciliationStatus,
        ReconciliationCategory
    FROM [Your period close status history source]
    WHERE EventTimestamp >= [Start date parameter]
      AND EventTimestamp < [End date parameter]
      AND ([Company Code filter] IS NULL OR CompanyCode = [Company Code filter])
      AND [Source status transition condition for Period Re-Opened]
),
all_events AS (
    SELECT * FROM period_close_initiated
    UNION ALL
    SELECT * FROM source_data_imported
    UNION ALL
    SELECT * FROM account_reconciliation_started
    UNION ALL
    SELECT * FROM intercompany_reconciliation_started
    UNION ALL
    SELECT * FROM reconciliation_submitted_for_review
    UNION ALL
    SELECT * FROM reconciliation_reviewed_and_approved
    UNION ALL
    SELECT * FROM reconciliation_rejected
    UNION ALL
    SELECT * FROM adjusting_journal_entry_posted
    UNION ALL
    SELECT * FROM trial_balance_prepared
    UNION ALL
    SELECT * FROM financial_statements_generated
    UNION ALL
    SELECT * FROM financial_statements_reviewed
    UNION ALL
    SELECT * FROM financial_statements_approved
    UNION ALL
    SELECT * FROM period_close_completed
    UNION ALL
    SELECT * FROM period_reopened
),
deduplicated_events AS (
    SELECT DISTINCT
        FinancialPeriod,
        ActivityName,
        EventTimestamp,
        SourceSystem,
        LastDataUpdate,
        ResponsibleUser,
        Department,
        CompanyCode,
        GLAccount,
        ReconciliationStatus,
        ReconciliationCategory
    FROM all_events
)
SELECT
    FinancialPeriod,
    ActivityName,
    EventTimestamp,
    SourceSystem,
    LastDataUpdate,
    ResponsibleUser,
    Department,
    CompanyCode,
    GLAccount,
    ReconciliationStatus,
    ReconciliationCategory
FROM deduplicated_events
ORDER BY FinancialPeriod, EventTimestamp, ActivityName;

import requests
import pandas as pd
import datetime
import time

class=class="hl-string">"hl-comment"># --- Configuration Settings --- #
class=class="hl-string">"hl-comment"># Replace with your Workiva API credentials
CLIENT_ID = class="hl-string">'YOUR_CLIENT_ID'
CLIENT_SECRET = class="hl-string">'YOUR_CLIENT_SECRET'

class=class="hl-string">"hl-comment"># Define the date range for the extraction
START_DATE = class="hl-string">'YYYY-MM-DD'
END_DATE = class="hl-string">'YYYY-MM-DD'

class=class="hl-string">"hl-comment"># Define the Process Template IDs for your Record to Report processes
PROCESS_TEMPLATE_IDS = [class="hl-string">'YOUR_PROCESS_TEMPLATE_ID_1', class="hl-string">'YOUR_PROCESS_TEMPLATE_ID_2']

class=class="hl-string">"hl-comment"># API Base URL
BASE_URL = class="hl-string">'https://api.workiva.com/v1'

class=class="hl-string">"hl-comment"># --- Function to get an OAuth2 Token --- #
def get_access_token():
    url = fclass="hl-string">'{BASE_URL}/oauth2/token'
    payload = {
        class="hl-string">'grant_type': class="hl-string">'client_credentials',
        class="hl-string">'client_id': CLIENT_ID,
        class="hl-string">'client_secret': CLIENT_SECRET
    }
    response = requests.post(url, data=payload)
    response.raise_for_status() class=class="hl-string">"hl-comment"># Raises an exception for bad status codes
    return response.json()[class="hl-string">'access_token']

class=class="hl-string">"hl-comment"># --- Function to handle paginated API calls --- #
def get_all_pages(url, headers, params):
    all_items = []
    while url:
        response = requests.get(url, headers=headers, params=params)
        response.raise_for_status()
        data = response.json()
        all_items.extend(data[class="hl-string">'data'])
        url = data.get(class="hl-string">'metadata', {}).get(class="hl-string">'nextPageUrl')
        params = None class=class="hl-string">"hl-comment"># Subsequent requests use the full nextPageUrl
        if url:
            print(fclass="hl-string">'Fetching next page: {url}')
            time.sleep(0.5) class=class="hl-string">"hl-comment"># Be respectful of API rate limits
    return all_items

class=class="hl-string">"hl-comment"># --- Main Extraction Logic --- #
def main():
    print(class="hl-string">'Starting Workiva data extraction...')
    access_token = get_access_token()
    headers = {class="hl-string">'Authorization': fclass="hl-string">'Bearer {access_token}'}
    event_log = []
    last_data_update = datetime.datetime.utcnow().isoformat() + class="hl-string">'Z'

    class=class="hl-string">"hl-comment"># 1. Fetch relevant processes (our cases)
    print(class="hl-string">'Fetching processes...')
    process_params = {
        class="hl-string">'filter[templateId]': class="hl-string">','.join(PROCESS_TEMPLATE_IDS),
        class="hl-string">'filter[created]': fclass="hl-string">'ge({START_DATE});lt({END_DATE})'
    }
    processes = get_all_pages(fclass="hl-string">'{BASE_URL}/processes', headers, process_params)
    print(fclass="hl-string">'Found {len(processes)} processes to analyze.')

    class=class="hl-string">"hl-comment"># 2. Iterate through each process to fetch tasks and create events
    for process in processes:
        financial_period = process.get(class="hl-string">'name', process[class="hl-string">'id'])
        process_id = process[class="hl-string">'id']
        process_owner_id = process.get(class="hl-string">'owner', {}).get(class="hl-string">'id')

        class=class="hl-string">"hl-comment"># Activity: Period Close Initiated
        event_log.append({
            class="hl-string">'FinancialPeriod': financial_period,
            class="hl-string">'ActivityName': class="hl-string">'Period Close Initiated',
            class="hl-string">'EventTimestamp': process[class="hl-string">'created'],
            class="hl-string">'ResponsibleUser': process_owner_id,
            class="hl-string">'CompanyCode': process.get(class="hl-string">'customProperties', {}).get(class="hl-string">'CompanyCode'),
            class="hl-string">'Department': None,
            class="hl-string">'GLAccount': None,
            class="hl-string">'ReconciliationStatus': None,
            class="hl-string">'ReconciliationCategory': None
        })

        class=class="hl-string">"hl-comment"># 3. Fetch all tasks for the current process
        print(fclass="hl-string">'Fetching tasks for process: {financial_period}')
        tasks = get_all_pages(fclass="hl-string">'{BASE_URL}/tasks?filter[processId]={process_id}', headers, None)

        for task in tasks:
            task_name = task.get(class="hl-string">'name', class="hl-string">'').lower()
            task_assignee_id = task.get(class="hl-string">'assignee', {}).get(class="hl-string">'id')
            task_status = task.get(class="hl-string">'status', {}).get(class="hl-string">'value', class="hl-string">'').lower()
            reconciliation_category = task.get(class="hl-string">'customProperties', {}).get(class="hl-string">'ReconciliationCategory')
            gl_account = task.get(class="hl-string">'customProperties', {}).get(class="hl-string">'GLAccount')

            class=class="hl-string">"hl-comment"># Map task creation to class="hl-string">'Started' activities
            activity_map_start = {
                class="hl-string">'import source data': class="hl-string">'Source Data Imported',
                class="hl-string">'reconcile account': class="hl-string">'Account Reconciliation Started',
                class="hl-string">'intercompany reconciliation': class="hl-string">'Intercompany Reconciliation Started',
                class="hl-string">'prepare trial balance': class="hl-string">'Trial Balance Prepared',
                class="hl-string">'generate financial statements': class="hl-string">'Financial Statements Generated',
                class="hl-string">'review financial statements': class="hl-string">'Financial Statements Reviewed',
                class="hl-string">'post adjusting journal entry': class="hl-string">'Adjusting Journal Entry Posted'
            }
            for key, activity in activity_map_start.items():
                if key in task_name:
                    event_log.append({
                        class="hl-string">'FinancialPeriod': financial_period,
                        class="hl-string">'ActivityName': activity,
                        class="hl-string">'EventTimestamp': task[class="hl-string">'created'],
                        class="hl-string">'ResponsibleUser': task_assignee_id,
                        class="hl-string">'CompanyCode': process.get(class="hl-string">'customProperties', {}).get(class="hl-string">'CompanyCode'),
                        class="hl-string">'Department': task.get(class="hl-string">'customProperties', {}).get(class="hl-string">'Department'),
                        class="hl-string">'GLAccount': gl_account,
                        class="hl-string">'ReconciliationStatus': class="hl-string">'In Progress',
                        class="hl-string">'ReconciliationCategory': reconciliation_category
                    })

            class=class="hl-string">"hl-comment"># Map task status changes and completion to other activities
            if task.get(class="hl-string">'completed'):
                activity_map_completed = {
                    class="hl-string">'reconcile account': class="hl-string">'Reconciliation Submitted for Review',
                    class="hl-string">'intercompany reconciliation': class="hl-string">'Reconciliation Submitted for Review',
                    class="hl-string">'approve reconciliation': class="hl-string">'Reconciliation Reviewed and Approved',
                    class="hl-string">'approve financial statements': class="hl-string">'Financial Statements Approved',
                    class="hl-string">'close period': class="hl-string">'Period Close Completed'
                }
                for key, activity in activity_map_completed.items():
                    if key in task_name:
                         event_log.append({
                            class="hl-string">'FinancialPeriod': financial_period,
                            class="hl-string">'ActivityName': activity,
                            class="hl-string">'EventTimestamp': task[class="hl-string">'completed'],
                            class="hl-string">'ResponsibleUser': task.get(class="hl-string">'completedBy',{}).get(class="hl-string">'id'),
                            class="hl-string">'CompanyCode': process.get(class="hl-string">'customProperties', {}).get(class="hl-string">'CompanyCode'),
                            class="hl-string">'Department': task.get(class="hl-string">'customProperties', {}).get(class="hl-string">'Department'),
                            class="hl-string">'GLAccount': gl_account,
                            class="hl-string">'ReconciliationStatus': class="hl-string">'Approved' if class="hl-string">'approve' in key else class="hl-string">'Submitted',
                            class="hl-string">'ReconciliationCategory': reconciliation_category
                        })

            if task_status == class="hl-string">'rejected':
                 event_log.append({
                    class="hl-string">'FinancialPeriod': financial_period,
                    class="hl-string">'ActivityName': class="hl-string">'Reconciliation Rejected',
                    class="hl-string">'EventTimestamp': task.get(class="hl-string">'updated'), class=class="hl-string">"hl-comment"># Use last updated time for rejection event
                    class="hl-string">'ResponsibleUser': task_assignee_id,
                    class="hl-string">'CompanyCode': process.get(class="hl-string">'customProperties', {}).get(class="hl-string">'CompanyCode'),
                    class="hl-string">'Department': task.get(class="hl-string">'customProperties', {}).get(class="hl-string">'Department'),
                    class="hl-string">'GLAccount': gl_account,
                    class="hl-string">'ReconciliationStatus': class="hl-string">'Rejected',
                    class="hl-string">'ReconciliationCategory': reconciliation_category
                })

        class=class="hl-string">"hl-comment"># Activity: Period Re-Opened (Example logic, assumes a status on the process itself)
        if process.get(class="hl-string">'status', {}).get(class="hl-string">'value') == class="hl-string">'re-opened':
            event_log.append({
                class="hl-string">'FinancialPeriod': financial_period,
                class="hl-string">'ActivityName': class="hl-string">'Period Re-Opened',
                class="hl-string">'EventTimestamp': process[class="hl-string">'updated'],
                class="hl-string">'ResponsibleUser': process_owner_id,
                class="hl-string">'CompanyCode': process.get(class="hl-string">'customProperties', {}).get(class="hl-string">'CompanyCode'),
                class="hl-string">'Department': None,
                class="hl-string">'GLAccount': None,
                class="hl-string">'ReconciliationStatus': None,
                class="hl-string">'ReconciliationCategory': None
            })

    if not event_log:
        print(class="hl-string">'No events found for the given criteria.')
        return

    class=class="hl-string">"hl-comment"># 4. Create and save the DataFrame
    print(class="hl-string">'Creating DataFrame and saving to CSV...')
    df = pd.DataFrame(event_log)

    class=class="hl-string">"hl-comment"># Add static columns
    df[class="hl-string">'SourceSystem'] = class="hl-string">'Workiva'
    df[class="hl-string">'LastDataUpdate'] = last_data_update

    class=class="hl-string">"hl-comment"># Reorder columns to a logical format
    final_columns = [
        class="hl-string">'FinancialPeriod', class="hl-string">'ActivityName', class="hl-string">'EventTimestamp', class="hl-string">'SourceSystem', class="hl-string">'LastDataUpdate',
        class="hl-string">'ResponsibleUser', class="hl-string">'Department', class="hl-string">'CompanyCode', class="hl-string">'GLAccount', class="hl-string">'ReconciliationStatus', class="hl-string">'ReconciliationCategory'
    ]
    df = df[final_columns]

    df.to_csv(class="hl-string">'workiva_event_log.csv', index=False)
    print(class="hl-string">'Extraction complete. Output saved to workiva_event_log.csv')

if __name__ == class="hl-string">'__main__':
    main()