An expected file is a scheduled or otherwise anticipated document that an organization must receive or process. In an ACH company dashboard, it supports proactive monitoring by showing whether required files have arrived and when the next file is due. section 10.0 section 10.8
An expected-file record commonly supports these operations:
- Associate the file with a company and store its filename, schedule, and last received date. section 10.1 section 10.6.2
- Calculate whether the file has been loaded and determine the next expected date from daily, weekly, monthly, quarterly, semi-annual, or annual schedules. section 10.6.2
- Update last_file_date and updated_at after a file is uploaded successfully. section 10.6.2
Expected-file API workflow
A test retrieves a company by tax ID, calls get_expected_files_for_company, and verifies that two expected files are returned. When an uploaded file is parsed successfully, the ingestion flow updates its last receipt date. A later enhancement can adjust the next date to the following Monday when a calculated date falls on a weekend.
Listing 10.35 SQL for the Expected Files component
WITH future_file_dates AS ( #1
SELECT ef.company_expected_file_id, #1
ef.company_id, #1
ef.file_name, #1
CASE ef.schedule #1
WHEN 'daily' THEN ef.last_file_date + INTERVAL '1 day' #1
WHEN 'weekly' THEN ef.last_file_date + INTERVAL '1 week' #1
WHEN 'bi-weekly' THEN ef.last_file_date + INTERVAL '2 weeks' #1
WHEN 'monthly' THEN ef.last_file_date + INTERVAL '1 month' #1
WHEN 'quarterly' THEN ef.last_file_date #1
+ INTERVAL '3 months' #1
WHEN 'semi-annually' THEN ef.last_file_date #1
+ INTERVAL '6 months' #1
WHEN 'annually' THEN ef.last_file_date + INTERVAL '1 year' #1
ELSE ef.last_file_date #1
END AS next_file_date #1
FROM company_expected_files AS ef #1
WHERE ef.company_id = %s #1
) #1
SELECT ef.company_expected_file_id, ef.file_name, ef.schedule, #1
ef.last_file_date, ffd.next_file_date, #1
CASE #2
WHEN af.file_name IS NOT NULL #2
AND af.created_at::date = ef.last_file_date::date #2
THEN TRUE #2
ELSE FALSE #2
END AS file_loaded #2
FROM company_expected_files AS ef #2
INNER JOIN future_file_dates AS ffd USING (company_expected_file_id)
LEFT JOIN ach_files AS af ON ef.file_name = af.file_name
WHERE ef.company_id = %s;
Inspect the SQL that derives next_file_date from each supported schedule and exposes calculated monitoring fields. section 10.6.2
