When the user needs to process invoice receipts from PDF files in a workspace and update a purchase invoice database with payment tracking. This skill automatically scans workspace directories for PDF receipts, extracts invoice data (invoice ID, supplier, amount, purchaser email, date, payment status), inserts records into database tables, sets up payment tracking with outstanding flags, identifies unpaid or partially paid invoices, and sends email notifications to relevant purchasing managers with specific filenames requiring attention. Key triggers include processing receipts, updating invoice databases, tracking outstanding payments, sending payment reminder emails, and handling PDF invoice files with payment status indicators.
/workspace/dumps/workspace directory to locate the files folder containing PDF receipts.PURCHASE_INVOICE database, then list tables in the PUBLIC schema.INVOICES and INVOICE_PAYMENTS tables. Read sample data to understand existing records and column formats.files directory.PAID or checkmark (✓): Fully paid.PARTIAL or square (■): Partially paid. Extract the paid amount if available.UNPAID, Awaiting payment, Pending, Verification in process: Outstanding (unpaid).PURCHASE_INVOICE.PUBLIC.INVOICES with columns: INVOICE_ID, SUPPLIER_NAME, INVOICE_AMOUNT, PURCHASER_EMAIL, INVOICE_DATE.PURCHASE_INVOICE.PUBLIC.INVOICE_PAYMENTS:
INVOICE_ID: The extracted invoice ID.PAYMENT_AMOUNT:
PAID: Set to the full invoice amount.PARTIAL: Set to the extracted paid amount.0.OUTSTANDING_FLAG:
0 if status is PAID.1 for all other statuses (PARTIAL, UNPAID, Awaiting, Pending, Verification).ALTER TABLE statement to set the comment/description for the OUTSTANDING_FLAG column in INVOICE_PAYMENTS to exactly: 0=Paid, 1=Outstanding. Verify the description was applied.JOIN between INVOICES and INVOICE_PAYMENTS where OUTSTANDING_FLAG = 1. Group results by PURCHASER_EMAIL.INVOICE_ID back to the original PDF filename (e.g., INV-2024-013 → INV-2024-013.pdf).PURCHASER_EMAIL):
Process Outstanding Invoices