Uw template voor onkostenbeheer data

WITH last_update AS (
  SELECT CURRENT_TIMESTAMP AS last_data_update
)
SELECT
  er.id AS ExpenseReportId,
  'Expense Report Created' AS ActivityName,
  er."created-at" AS EventTime,
  er."updated-at" AS EndTime,
  u.full_name AS UserName,
  u.department_name AS EmployeeDepartment,
  er.total AS TotalAmount,
  er.status AS ExpenseReportStatus,
  NULL AS RejectionReason,
  er.has_policy_violations AS PolicyViolationFlag,
  'Coupa' AS SourceSystem,
  lu.last_data_update AS LastDataUpdate
FROM stg_expense_reports er
LEFT JOIN stg_users u ON er.user_id = u.id
CROSS JOIN last_update lu

UNION ALL

SELECT
  er.id AS ExpenseReportId,
  'Receipts Attached' AS ActivityName,
  att."created-at" AS EventTime,
  att."updated-at" AS EndTime,
  u.full_name AS UserName,
  u.department_name AS EmployeeDepartment,
  er.total AS TotalAmount,
  er.status AS ExpenseReportStatus,
  NULL AS RejectionReason,
  er.has_policy_violations AS PolicyViolationFlag,
  'Coupa' AS SourceSystem,
  lu.last_data_update AS LastDataUpdate
FROM stg_expense_report_attachments att
JOIN stg_expense_reports er ON att.expense_report_id = er.id
LEFT JOIN stg_users u ON er.user_id = u.id
CROSS JOIN last_update lu

UNION ALL

SELECT
  er.id AS ExpenseReportId,
  'Expense Report Submitted' AS ActivityName,
  er."submitted-at" AS EventTime,
  er."updated-at" AS EndTime,
  u.full_name AS UserName,
  u.department_name AS EmployeeDepartment,
  er.total AS TotalAmount,
  er.status AS ExpenseReportStatus,
  NULL AS RejectionReason,
  er.has_policy_violations AS PolicyViolationFlag,
  'Coupa' AS SourceSystem,
  lu.last_data_update AS LastDataUpdate
FROM stg_expense_reports er
LEFT JOIN stg_users u ON er.user_id = u.id
CROSS JOIN last_update lu
WHERE er."submitted-at" IS NOT NULL

UNION ALL

SELECT
  era.expense_report_id AS ExpenseReportId,
  'Manager Approved' AS ActivityName,
  era."updated-at" AS EventTime,
  era."updated-at" AS EndTime,
  approver.full_name AS UserName,
  u.department_name AS EmployeeDepartment,
  er.total AS TotalAmount,
  er.status AS ExpenseReportStatus,
  NULL AS RejectionReason,
  er.has_policy_violations AS PolicyViolationFlag,
  'Coupa' AS SourceSystem,
  lu.last_data_update AS LastDataUpdate
FROM stg_expense_report_approvals era
JOIN stg_expense_reports er ON era.expense_report_id = er.id
LEFT JOIN stg_users u ON er.user_id = u.id
LEFT JOIN stg_users approver ON era.approver_id = approver.id
CROSS JOIN last_update lu
WHERE era.status = 'approved' AND approver.role LIKE '%Manager%'

UNION ALL

SELECT
  era.expense_report_id AS ExpenseReportId,
  'Manager Rejected' AS ActivityName,
  era."updated-at" AS EventTime,
  era."updated-at" AS EndTime,
  approver.full_name AS UserName,
  u.department_name AS EmployeeDepartment,
  er.total AS TotalAmount,
  er.status AS ExpenseReportStatus,
  era.reason AS RejectionReason,
  er.has_policy_violations AS PolicyViolationFlag,
  'Coupa' AS SourceSystem,
  lu.last_data_update AS LastDataUpdate
FROM stg_expense_report_approvals era
JOIN stg_expense_reports er ON era.expense_report_id = er.id
LEFT JOIN stg_users u ON er.user_id = u.id
LEFT JOIN stg_users approver ON era.approver_id = approver.id
CROSS JOIN last_update lu
WHERE era.status = 'rejected' AND approver.role LIKE '%Manager%'

UNION ALL

SELECT
  era.expense_report_id AS ExpenseReportId,
  'Sent Back For Revision' AS ActivityName,
  era."updated-at" AS EventTime,
  era."updated-at" AS EndTime,
  approver.full_name AS UserName,
  u.department_name AS EmployeeDepartment,
  er.total AS TotalAmount,
  er.status AS ExpenseReportStatus,
  era.reason AS RejectionReason,
  er.has_policy_violations AS PolicyViolationFlag,
  'Coupa' AS SourceSystem,
  lu.last_data_update AS LastDataUpdate
FROM stg_expense_report_approvals era
JOIN stg_expense_reports er ON era.expense_report_id = er.id
LEFT JOIN stg_users u ON er.user_id = u.id
LEFT JOIN stg_users approver ON era.approver_id = approver.id
CROSS JOIN last_update lu
WHERE era.status = 'sent_back'

UNION ALL

SELECT
  er.id AS ExpenseReportId,
  'Policy Check Performed' AS ActivityName,
  er."updated-at" AS EventTime,
  er."updated-at" AS EndTime,
  'System' AS UserName,
  u.department_name AS EmployeeDepartment,
  er.total AS TotalAmount,
  er.status AS ExpenseReportStatus,
  NULL AS RejectionReason,
  er.has_policy_violations AS PolicyViolationFlag,
  'Coupa' AS SourceSystem,
  lu.last_data_update AS LastDataUpdate
FROM stg_expense_reports er
LEFT JOIN stg_users u ON er.user_id = u.id
CROSS JOIN last_update lu
WHERE er.has_policy_violations = TRUE

UNION ALL

SELECT
  era.expense_report_id AS ExpenseReportId,
  'Finance Approved' AS ActivityName,
  era."updated-at" AS EventTime,
  era."updated-at" AS EndTime,
  approver.full_name AS UserName,
  u.department_name AS EmployeeDepartment,
  er.total AS TotalAmount,
  er.status AS ExpenseReportStatus,
  NULL AS RejectionReason,
  er.has_policy_violations AS PolicyViolationFlag,
  'Coupa' AS SourceSystem,
  lu.last_data_update AS LastDataUpdate
FROM stg_expense_report_approvals era
JOIN stg_expense_reports er ON era.expense_report_id = er.id
LEFT JOIN stg_users u ON er.user_id = u.id
LEFT JOIN stg_users approver ON era.approver_id = approver.id
CROSS JOIN last_update lu
WHERE era.status = 'approved' AND approver.role LIKE '%Finance%'

UNION ALL

SELECT
  era.expense_report_id AS ExpenseReportId,
  'Finance Rejected' AS ActivityName,
  era."updated-at" AS EventTime,
  era."updated-at" AS EndTime,
  approver.full_name AS UserName,
  u.department_name AS EmployeeDepartment,
  er.total AS TotalAmount,
  er.status AS ExpenseReportStatus,
  era.reason AS RejectionReason,
  er.has_policy_violations AS PolicyViolationFlag,
  'Coupa' AS SourceSystem,
  lu.last_data_update AS LastDataUpdate
FROM stg_expense_report_approvals era
JOIN stg_expense_reports er ON era.expense_report_id = er.id
LEFT JOIN stg_users u ON er.user_id = u.id
LEFT JOIN stg_users approver ON era.approver_id = approver.id
CROSS JOIN last_update lu
WHERE era.status = 'rejected' AND approver.role LIKE '%Finance%'

UNION ALL

SELECT
  er.id AS ExpenseReportId,
  'Expense Report Withdrawn' AS ActivityName,
  er."updated-at" AS EventTime,
  er."updated-at" AS EndTime,
  u.full_name AS UserName,
  u.department_name AS EmployeeDepartment,
  er.total AS TotalAmount,
  er.status AS ExpenseReportStatus,
  NULL AS RejectionReason,
  er.has_policy_violations AS PolicyViolationFlag,
  'Coupa' AS SourceSystem,
  lu.last_data_update AS LastDataUpdate
FROM stg_expense_reports er
LEFT JOIN stg_users u ON er.user_id = u.id
CROSS JOIN last_update lu
WHERE er.status = 'withdrawn'

UNION ALL

SELECT
  inv.expense_report_id AS ExpenseReportId,
  'Reimbursement Scheduled' AS ActivityName,
  inv.scheduled_payment_date AS EventTime,
  inv.updated_at AS EndTime,
  'System' AS UserName,
  u.department_name AS EmployeeDepartment,
  er.total AS TotalAmount,
  er.status AS ExpenseReportStatus,
  NULL AS RejectionReason,
  er.has_policy_violations AS PolicyViolationFlag,
  'Coupa' AS SourceSystem,
  lu.last_data_update AS LastDataUpdate
FROM stg_invoices_from_expense_reports inv
JOIN stg_expense_reports er ON inv.expense_report_id = er.id
LEFT JOIN stg_users u ON er.user_id = u.id
CROSS JOIN last_update lu
WHERE inv.scheduled_payment_date IS NOT NULL

UNION ALL

SELECT
  inv.expense_report_id AS ExpenseReportId,
  'Reimbursement Executed' AS ActivityName,
  inv.paid_date AS EventTime,
  inv.updated_at AS EndTime,
  'System' AS UserName,
  u.department_name AS EmployeeDepartment,
  er.total AS TotalAmount,
  er.status AS ExpenseReportStatus,
  NULL AS RejectionReason,
  er.has_policy_violations AS PolicyViolationFlag,
  'Coupa' AS SourceSystem,
  lu.last_data_update AS LastDataUpdate
FROM stg_invoices_from_expense_reports inv
JOIN stg_expense_reports er ON inv.expense_report_id = er.id
LEFT JOIN stg_users u ON er.user_id = u.id
CROSS JOIN last_update lu
WHERE inv.paid_date IS NOT NULL

UNION ALL

SELECT
  inv.expense_report_id AS ExpenseReportId,
  'Accounting Posted' AS ActivityName,
  inv.updated_at AS EventTime,
  inv.updated_at AS EndTime,
  'System' AS UserName,
  u.department_name AS EmployeeDepartment,
  er.total AS TotalAmount,
  er.status AS ExpenseReportStatus,
  NULL AS RejectionReason,
  er.has_policy_violations AS PolicyViolationFlag,
  'Coupa' AS SourceSystem,
  lu.last_data_update AS LastDataUpdate
FROM stg_invoices_from_expense_reports inv
JOIN stg_expense_reports er ON inv.expense_report_id = er.id
LEFT JOIN stg_users u ON er.user_id = u.id
CROSS JOIN last_update lu
WHERE inv.exported = TRUE;

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 Coupa instance URL and API Key
COUPA_URL = class="hl-string">"https://[your_company].coupahost.com"
API_KEY = class="hl-string">"[your_api_key]"

class=class="hl-string">"hl-comment"># Define the date range for the extraction
class=class="hl-string">"hl-comment"># Format: YYYY-MM-DDTHH:MM:SSZ (UTC)
START_DATE = class="hl-string">"2023-01-01T00:00:00Z"
END_DATE = class="hl-string">"2023-12-31T23:59:59Z"

class=class="hl-string">"hl-comment"># API request headers
HEADERS = {
    class="hl-string">"Accept": class="hl-string">"application/json",
    class="hl-string">"X-COUPA-API-KEY": API_KEY
}

class=class="hl-string">"hl-comment"># Static values for the event log
SOURCE_SYSTEM = class="hl-string">"Coupa Expenses"
LAST_DATA_UPDATE = datetime.datetime.utcnow().isoformat() + class="hl-string">'Z'

def get_paginated_data(endpoint, params):
    class="hl-string">"""Helper function to fetch all pages for a given API endpoint."""
    all_data = []
    offset = 0
    limit = 50
    while True:
        params[class="hl-string">'offset'] = offset
        params[class="hl-string">'limit'] = limit
        try:
            response = requests.get(endpoint, headers=HEADERS, params=params)
            response.raise_for_status() class=class="hl-string">"hl-comment"># Raises an HTTPError for bad responses (4xx or 5xx)
            data = response.json()
            if not data:
                break
            all_data.extend(data)
            offset += limit
        except requests.exceptions.RequestException as e:
            print(fclass="hl-string">"Error fetching data from {endpoint}: {e}")
            break
    return all_data

def main():
    class="hl-string">"""Main function to extract, transform, and save expense data."""
    print(class="hl-string">"Starting expense report extraction...")
    event_log = []

    class=class="hl-string">"hl-comment"># 1. Fetch all expense report headers in the date range
    reports_endpoint = fclass="hl-string">"{COUPA_URL}/api/expense_reports"
    reports_params = {
        class="hl-string">"created-at[gt]": START_DATE,
        class="hl-string">"created-at[lt]": END_DATE,
        class="hl-string">"fields": class="hl-string">"["idclass="hl-string">", "created-atclass="hl-string">", "updated-atclass="hl-string">", "submitted-atclass="hl-string">", "withdrawn-atclass="hl-string">", "statusclass="hl-string">", "totalclass="hl-string">", "userclass="hl-string">", "exportedclass="hl-string">", "expense-linesclass="hl-string">", "policy-violationsclass="hl-string">"]"
    }
    expense_reports = get_paginated_data(reports_endpoint, reports_params)

    print(fclass="hl-string">"Found {len(expense_reports)} expense reports to process.")

    class=class="hl-string">"hl-comment"># 2. Process each expense report to create events
    for i, report in enumerate(expense_reports):
        print(fclass="hl-string">"Processing report {i+1}/{len(expense_reports)}: ID {report['id']}")
        
        class=class="hl-string">"hl-comment"># Common attributes for all events of this case
        base_event = {
            class="hl-string">'ExpenseReportId': report[class="hl-string">'id'],
            class="hl-string">'TotalAmount': float(report.get(class="hl-string">'total', 0.0)),
            class="hl-string">'ExpenseReportStatus': report.get(class="hl-string">'status'),
            class="hl-string">'EmployeeDepartment': report.get(class="hl-string">'user', {}).get(class="hl-string">'department', {}).get(class="hl-string">'name') if report.get(class="hl-string">'user', {}).get(class="hl-string">'department') else None,
            class="hl-string">'SourceSystem': SOURCE_SYSTEM,
            class="hl-string">'LastDataUpdate': LAST_DATA_UPDATE,
            class="hl-string">'EndTime': None, class=class="hl-string">"hl-comment"># EndTime can be populated if duration is meaningful
            class="hl-string">'RejectionReason': None, class=class="hl-string">"hl-comment"># Will be populated by rejection events
            class="hl-string">'PolicyViolationFlag': bool(report.get(class="hl-string">'policy-violations'))
        }

        class=class="hl-string">"hl-comment"># Event: Expense Report Created
        event_log.append({
            **base_event,
            class="hl-string">'ActivityName': class="hl-string">'Expense Report Created',
            class="hl-string">'EventTime': report[class="hl-string">'created-at'],
            class="hl-string">'UserName': report.get(class="hl-string">'user', {}).get(class="hl-string">'fullname')
        })
        
        class=class="hl-string">"hl-comment"># Event: Expense Report Submitted
        if report.get(class="hl-string">'submitted-at'):
            event_log.append({
                **base_event,
                class="hl-string">'ActivityName': class="hl-string">'Expense Report Submitted',
                class="hl-string">'EventTime': report[class="hl-string">'submitted-at'],
                class="hl-string">'UserName': report.get(class="hl-string">'user', {}).get(class="hl-string">'fullname')
            })

        class=class="hl-string">"hl-comment"># Event: Expense Report Withdrawn
        if report.get(class="hl-string">'withdrawn-at'):
            event_log.append({
                **base_event,
                class="hl-string">'ActivityName': class="hl-string">'Expense Report Withdrawn',
                class="hl-string">'EventTime': report[class="hl-string">'withdrawn-at'],
                class="hl-string">'UserName': report.get(class="hl-string">'user', {}).get(class="hl-string">'fullname')
            })
        
        class=class="hl-string">"hl-comment"># Event: Policy Check Performed (inferred)
        if base_event[class="hl-string">'PolicyViolationFlag']:
             event_log.append({
                **base_event,
                class="hl-string">'ActivityName': class="hl-string">'Policy Check Performed',
                class="hl-string">'EventTime': report[class="hl-string">'updated-at'], class=class="hl-string">"hl-comment"># Using updated-at as a proxy timestamp
                class="hl-string">'UserName': class="hl-string">'System'
            })

        class=class="hl-string">"hl-comment"># 3. Fetch attachments for class="hl-string">'Receipts Attached' event
        attachments_endpoint = fclass="hl-string">"{COUPA_URL}/api/expense_reports/{report['id']}/attachments"
        attachments = get_paginated_data(attachments_endpoint, {})
        if attachments:
            first_attachment_time = min(att[class="hl-string">'created-at'] for att in attachments if class="hl-string">'created-at' in att)
            event_log.append({
                **base_event,
                class="hl-string">'ActivityName': class="hl-string">'Receipts Attached',
                class="hl-string">'EventTime': first_attachment_time,
                class="hl-string">'UserName': report.get(class="hl-string">'user', {}).get(class="hl-string">'fullname')
            })

        class=class="hl-string">"hl-comment"># 4. Fetch approvals for approval/rejection events
        approvals_endpoint = fclass="hl-string">"{COUPA_URL}/api/expense_reports/{report['id']}/approvals"
        approvals = get_paginated_data(approvals_endpoint, {})
        
        is_first_approval = True
        for approval in sorted(approvals, key=lambda x: x[class="hl-string">'updated-at']):
            activity = None
            rejection_reason = approval.get(class="hl-string">'reason')
            
            if approval[class="hl-string">'status'] == class="hl-string">'approved':
                activity = class="hl-string">'Manager Approved' if is_first_approval else class="hl-string">'Finance Approved'
                is_first_approval = False
            elif approval[class="hl-string">'status'] == class="hl-string">'rejected':
                activity = class="hl-string">'Manager Rejected' if is_first_approval else class="hl-string">'Finance Rejected'
                is_first_approval = False
            elif approval[class="hl-string">'status'] in [class="hl-string">'sent_back', class="hl-string">'pending_resubmit']:
                activity = class="hl-string">'Sent Back For Revision'

            if activity:
                event_log.append({
                    **base_event,
                    class="hl-string">'ActivityName': activity,
                    class="hl-string">'EventTime': approval[class="hl-string">'updated-at'],
                    class="hl-string">'UserName': approval.get(class="hl-string">'approver', {}).get(class="hl-string">'fullname'),
                    class="hl-string">'RejectionReason': rejection_reason
                })
        
        class=class="hl-string">"hl-comment"># 5. Infer payment and accounting events from status
        if report[class="hl-string">'status'] == class="hl-string">'processing_payment':
            event_log.append({
                **base_event,
                class="hl-string">'ActivityName': class="hl-string">'Reimbursement Scheduled',
                class="hl-string">'EventTime': report[class="hl-string">'updated-at'], class=class="hl-string">"hl-comment"># Inferred from last update timestamp
                class="hl-string">'UserName': class="hl-string">'System'
            })
            
        if report[class="hl-string">'status'] == class="hl-string">'paid':
            event_log.append({
                **base_event,
                class="hl-string">'ActivityName': class="hl-string">'Reimbursement Executed',
                class="hl-string">'EventTime': report[class="hl-string">'updated-at'], class=class="hl-string">"hl-comment"># Inferred from last update timestamp
                class="hl-string">'UserName': class="hl-string">'System'
            })

        class=class="hl-string">"hl-comment"># class="hl-string">'exported' flag indicates posting to an ERP system
        if report.get(class="hl-string">'exported') == True:
            event_log.append({
                **base_event,
                class="hl-string">'ActivityName': class="hl-string">'Accounting Posted',
                class="hl-string">'EventTime': report[class="hl-string">'updated-at'], class=class="hl-string">"hl-comment"># Inferred from last update timestamp
                class="hl-string">'UserName': class="hl-string">'System'
            })

    class=class="hl-string">"hl-comment"># 6. Create DataFrame and save to CSV
    if not event_log:
        print(class="hl-string">"No events were generated. Exiting.")
        return
        
    df = pd.DataFrame(event_log)
    class=class="hl-string">"hl-comment"># Reorder columns for clarity
    column_order = [
        class="hl-string">'ExpenseReportId', class="hl-string">'ActivityName', class="hl-string">'EventTime', class="hl-string">'EndTime', class="hl-string">'UserName',
        class="hl-string">'EmployeeDepartment', class="hl-string">'TotalAmount', class="hl-string">'ExpenseReportStatus',
        class="hl-string">'RejectionReason', class="hl-string">'PolicyViolationFlag', class="hl-string">'SourceSystem', class="hl-string">'LastDataUpdate'
    ]
    df = df[column_order]
    df.sort_values(by=[class="hl-string">'ExpenseReportId', class="hl-string">'EventTime'], inplace=True)
    
    output_filename = class="hl-string">'coupa_expense_event_log.csv'
    df.to_csv(output_filename, index=False)
    print(fclass="hl-string">"Extraction complete. Event log saved to {output_filename}")

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