Integrate BAI2 and camt.053 statement ingestion with a Treasury Management System via SFTP and normalize transaction codes across both formats

Verified steps

1. Establish SFTP connectivity to each bank's delivery server; configure the SFTP client with host-based key verification (store the server public key fingerprint at setup to prevent MITM substitution) and use key-pair authentication rather than password for the SFTP session
2. Implement format detection logic: read the first bytes of each retrieved file — BAI2 files begin with '01,' as the File Header record type; camt.053 files begin with XML declaration and the BankToCustomerStatementV namespace URI — route each file to the appropriate parser
3. Parse BAI2 files extracting record type, account number, currency code, BAI balance type codes (009 closing ledger, 015 closing ledger with float, 045 closing available, etc.), and Type-16 transaction records with their three-digit BAI transaction codes; parse camt.053 files extracting Stmt IBAN, Bal elements by type code (OPBD opening booked, CLBD closing booked, ITBD intraday booked), and Ntry elements with BkTxCd Domain/Family/SubFamily classification
4. Build a normalization mapping table that translates both BAI transaction type codes and camt.053 BkTxCd code triplets to a unified internal transaction category taxonomy; include bank-specific proprietary codes (which appear in BAI Type-16 with a preceding comma or in camt.053 under Prtry) in the mapping to prevent unclassified entries
5. Upsert normalized transaction records into your TMS using account number and bank-assigned transaction sequence as a composite deduplication key; post normalized balance snapshots tagged with effective date and balance type (ledger vs available vs float) to your cash position store
6. Generate a daily format health report tracking the percentage of transaction codes successfully mapped vs falling to the unmapped-proprietary bucket; use this report to identify new proprietary codes introduced by bank system upgrades and add them to the mapping table proactively