Overview

Step-by-Step Workflow

1. Initial Setup & Exploration

• List workspace files to locate claim archives and policy PDF.
• Connect to Snowflake: verify database, schema, and table structures.
• Read the policy PDF to extract destination-specific caps per employee level.

2. Extract & Organize Claims

• Extract all .tar.gz archives to a temporary directory (/workspace/dumps/workspace/extracted/).
• Each archive contains:
  • One *_main.pdf: The expense claim form with header info and itemized expenses.
  • Multiple *_invoice_*.pdf: Supporting invoices, numbered sequentially.

3. Parse & Validate Each Claim

For each claim directory:

• Read the main PDF to extract:
  • Claim metadata (ID, employee, destination, dates, total)
  • Itemized expenses (date, category, amount)
• Read all corresponding invoice PDFs to extract:
  • Invoice amount and category
  • Verify receipt exists (no "No receipt available" messages)
• Validation Checks:
  • Document Completeness: Every claim item must have a corresponding invoice.
  • Amount Matching: Claimed amount must match invoice amount (within 0.01 CNY tolerance).
  • Policy Compliance: Each expense category must not exceed daily/trip caps for the employee's level at that destination.

4. Categorize & Act on Results

Based on validation:

• Document Issues: Missing receipts or amount mismatches → Send "Expense Claim Review Required: {claim_id}" email.
• Over-Cap Issues: All documents valid but expenses exceed caps → Insert into database with FLAG = 1 and send "Expense Over-Cap Notice: {claim_id}" email.
• Valid Claims: No issues → Insert into database with FLAG = 0.

5. Database Operations

• Insert claims into TRAVEL_EXPENSE_REIMBURSEMENT.PUBLIC."2024Q4REIMBURSEMENT" with appropriate flag.
• Required columns: CLAIM_ID, EMPLOYEE_ID, EMPLOYEE_NAME, DEPARTMENT, DEST_CITY, DEST_COUNTRY, TRIP_START, TRIP_END, NIGHTS, TOTAL_CLAIMED, FLAG.

6. Email Notifications

• Use emails-send_email tool.
• To: Employee email (from ENTERPRISE_CONTACTS)
• CC: Manager email (from ENTERPRISE_CONTACTS)
• Subject: As specified in requirements
• Body: Clear description of issues found (list specific items and amounts).

Key Decisions & Edge Cases

• Missing Invoices: If an invoice PDF says "No receipt available", treat as missing receipt.
• Amount Discrepancies: Flag any difference > 0.01 CNY between claim and invoice.
• Policy Caps: Use the most specific cap available (city + employee level). If city not in policy, cannot validate caps.
• Multiple Claims per Employee: Process each claim independently.
• Empty Database: Handle case where 2024Q4REIMBURSEMENT table is empty (start ID from 1).

Tools Required

• filesystem-list_directory, filesystem-directory_tree
• pdf-tools-read_pdf_pages, pdf-tools-get_pdf_info
• snowflake-list_databases, snowflake-list_tables, snowflake-describe_table, snowflake-read_query, snowflake-write_query
• local-python-execute (for complex parsing/analysis)
• emails-send_email

Output

• Database records inserted into 2024Q4REIMBURSEMENT
• Email notifications sent to employees and managers
• Console summary of processed claims and issues found