ACH Remittance

Daily workflow that reads previous-day ACH payments from QuickBooks, generates a per-vendor Excel attachment, and sends one remittance email per vendor from remittance@wasteologygroup.com.

What It Does

Each weekday at 9:00 AM ET, the ach-remittance-daily Prefect flow:

1. Queries qbo.stg_qbo_bill_payments for previous-business-day ACH payments
2. Normalizes vendor names (e.g., all regional Waste Management variants → "Waste Management - National")
3. Loads active vendor contacts from qbo.ach_vendor_contacts
4. For each vendor: builds an Excel attachment and sends a remittance email
5. Sends an exception report to remittance@wasteologygroup.com for vendors with no contact

The flow exits cleanly if no payments are found (e.g., holidays, weekends).

Deployment

Detail	Value
Deployment name	ach-remittance-daily
Source repo	wg-orchestration (workflows/ach_remittance/)
Image	wasteologypipelinesacr.azurecr.io/ach-remittance:latest
Work pool	enevo-aci-pool (Prod subscription)
Schedule	Weekdays 9:00 AM ET

ACH Payment Detection

Two detection rules — the bank changed 2026-03-17:

Era	Filter
Before 2026-03-17 (Stock Yards)	check_bank_account_name LIKE '%ACH payments only%'
2026-03-17+ (PNC)	check_bank_account_name = 'PNC Bank Checking - x9513' AND doc_number LIKE '%-ACH-%'

QB date offset: QB records batch payments one day before the ACH initiation date. The flow queries previous_business_day - 1.

Account extraction: The private_note field encodes "{account_code} {CieTrade-UUID-or-URL}". The flow strips the trailing URL/hash via regex to extract the clean account code.

Email Format

• From: remittance@wasteologygroup.com (via Microsoft Graph API)
• Subject: ACH Payment Remittance – {vendor_name} – {date}
• Attachment: ACH_Remittance_{vendor_name}_{date}.xlsx

Excel layout: 8 columns for single-QB vendors (Transaction Number, ACH Date, Vendor Name, Invoice Date, Invoice Number, Account, Invoice Amount, Payment Amount). 7 columns for merged vendors (Transaction Number omitted).

App registration: ach-remittance-sender (ID aa14cd58), Mail.Send application permission, restricted to remittance mailbox only.

Vendor Normalization

QuickBooks vendor name	Output name	Notes
Waste Management - *	Waste Management - National	All regional WM variants merged
American Disposal Systems*	American Disposal Systems	NJ + PA combined
GFL* (account starts with digit)	GFL - Numbers	Account code split
GFL* (account starts with letter)	GFL - Letters	Account code split
All others	unchanged	Pass-through

Contact Management

Vendor contacts are managed exclusively via the Contacts tab in the QuickBooks page of Palantiri. The N8N form was decommissioned 2026-05-28.

• Contacts are stored in qbo.ach_vendor_contacts (108 rows, 106 active as of 2026-05-28)
• Vendor name must match QuickBooks exactly — no fuzzy matching
• Vendors with no active contact are skipped and appear in the exception report

Proofing Before Live Runs

Use the proof script to validate without sending real emails:

cd ~/projects/wg-orchestration

# Phase 1: Excel dump — writes one .xlsx per vendor to proof_output/
uv run python scripts/proof_ach_remittance.py --date 2026-04-21

# Phase 2: Dry-run emails — sends all to you with [DRY RUN] prefix
uv run python scripts/proof_ach_remittance.py --date 2026-04-21 --email kgray@wasteologygroup.com

# Phase 3: Single vendor test
uv run python scripts/proof_ach_remittance.py --date 2026-04-21 --vendor "Standard Waste Services" --email kgray@wasteologygroup.com

Or trigger a dry-run from Prefect Cloud → Custom Run with parameter dry_run_recipient=remittance@wasteologygroup.com.

Rebuilding the Image

cd ~/projects/wg-orchestration
docker build --no-cache -t wasteologypipelinesacr.azurecr.io/ach-remittance:latest -f docker/Dockerfile .
docker push wasteologypipelinesacr.azurecr.io/ach-remittance:latest
source .env && uv run python scripts/deploy_ach_remittance.py

Always use --no-cache — __pycache__ is excluded by .dockerignore but layers need to be rebuilt from current source.