Bank Ingestion Workflow

1. Executive Summary

Purpose

The bank ingestion workflow is the entry point for all cash into the Client Payments system. It receives ISO 20022 bank statement files (CAMT.052 intraday and CAMT.053 end-of-day) from UTA's banking partners — Bank of America, JPMorgan Chase, and City National Bank — parses them, deduplicates their contents, normalizes every transaction into a golden record, and makes those records available for cash receipt generation. Without this workflow, there is no bank_transaction record, and without a bank_transaction record, there is no cash_receipt, and without a cash_receipt, no worksheet can be created to apply cash against receivables. The workflow also surfaces a daily cash position dashboard so treasury and operations staff can monitor reconciliation status across all accounts before dispatching receipts for downstream processing.

Scope

Covered:

Uploading ISO 20022 XML files (CAMT.052 and CAMT.053) via the Bank File Ingestion screen
File-level deduplication (content hash and message ID strategies)
Bank identity validation at upload time
Gatekeeper filtering (credit-only, BAI code whitelist, reversal exclusion)
Transaction normalization and golden-record upsert into bank_transaction
Status promotion from PDNG to BOOK when end-of-day files settle intraday records
Auto-grouping of CAMT.052 intraday files under CAMT.053 end-of-day files
Manual file grouping when auto-grouping fails
Audit trail via bank_transaction_history
Reversal detection, manual review (accept/reject), and downstream notification
Cash receipt generation from a booked bank transaction
Daily cash position and reconciliation dashboard

Not covered (documented separately):

Cash receipt splits, references, and adjustments — see Cash Receipts Workflow
Worksheet creation and cash application — see Worksheets Workflow
Outbound payments and bank transmission — see Payments Workflow

Key Objectives

Prevent duplicate transactions from entering the golden record regardless of how many files contain the same transaction.
Ensure only settled, credit, and whitelisted-code transactions are admitted for cash application.
Give treasury staff a real-time daily cash position across all accounts before receipts are created.
Require human review of every reversal before downstream cash application is affected.
Bridge the bank data domain to the cash receipts domain by generating cash_receipt records from booked transactions.

2. Process Overview

mermaid

flowchart TD
    A[User selects bank and XML file] --> B[File format validation]
    B --> C{Valid ISO 20022 XML?}
    C -->|No| ERR1[Error: Unsupported format]
    C -->|Yes| D[Bank identity detection from file content]
    D --> E{Identity match with selected bank?}
    E -->|Mismatch - HIGH confidence| ERR2[Error: Bank mismatch]
    E -->|Match or LOW/MEDIUM confidence| F[Deduplication: content hash then message ID]
    F --> G{Duplicate detected?}
    G -->|Yes| ERR3[Error: Already uploaded - original file ID returned]
    G -->|No| H[Gatekeeper filter: credits only, BAI whitelist, no reversal flags]
    H --> I{Any transactions pass filter?}
    I -->|No| ERR4[Error: No transactions accepted]
    I -->|Yes| J[INSERT bank_file with status = PENDING]
    J --> K[Extract and INSERT bank_file_header, bank_file_balance, bank_file_summary]
    K --> L[Match account IDs in file to bank_account records]
    L --> M[INSERT bank_file_account per matched account]
    M --> N[Upsert each transaction: golden record INSERT or UPDATE bank_transaction]
    N --> O[INSERT bank_transaction_history per transaction]
    O --> P[UPDATE bank_file.status = PROCESSED]
    P --> Q{File type = CAMT053?}
    Q -->|Yes| R[Auto-group matching CAMT.052 files into bank_file_group]
    Q -->|No| S[Upload complete]
    R --> S

    S --> T{Reversal transactions in this file?}
    T -->|Yes| U[bank_transaction.reversal_status = PENDING]
    T -->|No| V[Transactions visible in Transactions tab]
    U --> W{Cash processor reviews reversal}
    W -->|Accept| X[reversal_status = ACCEPTED - manual downstream review required]
    W -->|Reject| Y[reversal_status = REJECTED - no downstream action]
    V --> Z[User generates cash receipt from BOOK credit transaction]
    Z --> AA[cash_receipt + default cash_receipt_split created]

Walkthrough

File selection and bank identification — The user selects a source bank from a dropdown populated from bank.bank_id and chooses an XML file with a .xml extension. Before any database write, the system validates that the file is a valid ISO 20022 document (BkToCstmrStmt for CAMT.053 or BkToCstmrAcctRpt for CAMT.052) and that the bank embedded in the file's BIC code, servicer name, and routing patterns matches the user's selection with high confidence.
Deduplication — A SHA-256 hash of the raw file content is computed and checked against bank_file.content_hash. If no hash match is found, the system also checks whether the same (source_bank_id, message_id) pair already exists. A match on either strategy causes the upload to be rejected before any writes occur; the error message includes the original bank_file_id for reference.
Gatekeeper filtering — The parser reads all <Ntry> elements and applies three sequential filters: reject debit entries (CdtDbtInd = DBIT), reject reversal-flagged entries (RvslInd = true), and reject entries whose proprietary BAI code is not in the accepted whitelist (165, 195, 208). Entries with no proprietary code pass through. If no entries survive, the upload is rejected.
File record and metadata creation — A bank_file row is inserted with status = 'PENDING'. Immediately after, the system extracts and inserts file header fields into bank_file_header, balance records (one per <Bal> element) into bank_file_balance, and the transaction summary block into bank_file_summary.
Account matching — Each <Stmt> or <Rpt> block in the XML carries an account identifier (IBAN or other ID). The system resolves these identifiers to bank_account.bank_account_id values by querying bank_account where bank_id = source_bank_id and bank_account_no matches. Unmatched identifiers are skipped with a warning; if no accounts match at all, the upload is rejected.
Transaction upsert (golden record) — For each transaction that passed the gatekeeper filter and belongs to a matched account, the system looks up an existing bank_transaction row by the composite key (bank_account_id, bank_reference_id). If found, the row is updated in place: status is promoted, bank_file_id is updated to the latest source, and booking_date is refreshed. If not found, a new row is inserted with cash_receipt_status = 'UNMATCHED'. Every INSERT or UPDATE produces one bank_transaction_history record.
File status finalization — After all transactions are processed, bank_file.status is set to 'PROCESSED' and bank_file.transaction_count is set to the total number of accepted transactions.
Auto-grouping (CAMT.053 only) — If the uploaded file is a CAMT.053 end-of-day file, the system searches for existing CAMT.052 intraday files with the same source_bank_id, bank_account_id, and statement_date. Unlinked matches are inserted into bank_file_group with matching_criteria = 'AUTO'.
Reversal review — Reversal transactions that were admitted (those with sub-family code RVSL or ARET) arrive with reversal_status = 'PENDING' and appear in the Transactions tab with Accept and Reject actions. A cash manager must explicitly decide on each reversal before any downstream action is taken on linked cash receipts.
Cash receipt generation — For any BOOK-status, non-reversal credit transaction that does not yet have a linked cash_receipt, a user can trigger receipt generation. This creates one cash_receipt row and one default cash_receipt_split, bridging the bank ingestion domain to the cash receipts workflow.

3. Business Rules

3.1 Golden Record: One Transaction Per Account-Reference Pair

Business rule: A bank transaction is uniquely identified by the combination of the bank account it arrived in (bank_account_id) and the bank's own reference for that transaction (bank_reference_id). This composite key must never produce duplicates regardless of how many bank files report the same transaction. When an intraday CAMT.052 file and a subsequent end-of-day CAMT.053 file both contain the same transaction, the existing bank_transaction row is updated in place rather than inserting a second row.

Foundation reference: Golden Record Upsert via Composite Unique Key

Workflow context: The result is visible in the Transaction History drawer. A transaction first seen in a CAMT.052 file shows one bank_transaction_history entry with action_type = 'CREATE' and to_status = 'PDNG'. When the CAMT.053 arrives, a second history entry appears with action_type = 'UPDATE', from_status = 'PDNG', and to_status = 'BOOK'.

3.2 File-Level Deduplication

Business rule: A bank file that has already been successfully processed must not create duplicate records. Deduplication operates on two signals checked in priority order: exact content identity (SHA-256 hash match) and semantic identity (same source_bank_id and message_id pair). A match on either signal causes the upload to be rejected before any write to the database.

Foundation reference: File-Level Deduplication

Workflow context: The rejection error message includes the original bank_file_id and specifies whether the match was by content hash ("exact content match") or by message ID ("same message ID from this bank"), helping the user verify the situation.

3.3 Gatekeeper Filter: Credit-Only, BAI Whitelist, No Reversal Flags

Business rule: The ingestion pipeline admits only incoming credit transactions matching approved payment types. Debit transactions (outgoing payments, fee charges), entries with <RvslInd> = true at the XML level, and entries with proprietary BAI codes outside the approved whitelist (165, 195, 208) are filtered out before reaching the golden record. Entries with no proprietary code at all are admitted.

Foundation reference: Gatekeeper Filter: Credit-Only, No Reversals, Code Whitelist

Workflow context: The count of transactions that survived the filter appears in bank_file.transaction_count after processing. If zero entries survive, the upload is rejected with a message indicating the file may have been entirely filtered by gatekeeper rules; no bank_file record is persisted in that case.

3.4 Bank Identity Validation

Business rule: The bank selected by the user at upload must match the bank that produced the file. If the file's BIC code, servicer name, or routing number patterns identify a specific bank with HIGH confidence and that bank does not match the user's selection, the upload is rejected before any database write.

Foundation reference: Bank Identity Validation at Upload

Workflow context: The error message names both the selected bank and the detected bank so the user can correct their selection. Medium- and low-confidence mismatches produce a server-side warning log but allow the upload to proceed with the user-selected bank recorded in bank_file.source_bank_id.

3.5 Lump-Sum Amount Rule

Business rule: A single bank entry (<Ntry>) can contain multiple sub-transactions (<TxDtls>) representing a batch payment. The system always records the full entry amount from <Ntry><Amt> as bank_transaction.amount. Sub-transaction amounts from <TxDtls><Amt> are never summed or used to set the transaction amount. This prevents accounting discrepancies when sub-transactions are in different currencies or are only partially enriched.

Foundation reference: Lump-Sum Amount Rule

3.6 Reversal Transactions Require Manual Review

Business rule: Reversal entries that pass the gatekeeper (those with sub-family code RVSL or ARET) represent events where money previously received is being clawed back. Every such transaction is inserted with bank_transaction.is_reversal = 1 and bank_transaction.reversal_status = 'PENDING'. Cash receipt generation is blocked for these transactions. A cash manager must explicitly accept or reject each reversal before any downstream action is taken on linked cash receipts or applications.

Foundation reference: Reversal Transactions Require Manual Review

WARNING

Accepting a reversal (reversal_status = 'ACCEPTED') does not automatically unapply any cash receipts. The cash processor must manually investigate the original transaction's linked cash_receipt records — found via bank_transaction.reversal_of_transaction_id → original transaction → cash_receipt.bank_transaction_id — and initiate a worksheet return or receipt void as appropriate.

3.7 Auto-Grouping Triggered Only by CAMT.053 Upload

Business rule: CAMT.052 intraday files are never grouped automatically when uploaded — they wait to be claimed. Auto-grouping only executes when a CAMT.053 end-of-day file is uploaded successfully. The system then searches for existing CAMT.052 files from the same bank, same account, and same statement date, and links them in bank_file_group with matching_criteria = 'AUTO'.

Foundation reference: Auto-Grouping Triggered Only by CAMT.053

3.8 Cash Receipt Generation Requires a BOOK-Status Non-Reversal Credit

Business rule: A cash_receipt can only be generated from a bank transaction that is a credit direction (direction = 'CREDIT'), has entry status status = 'BOOK', is not a reversal (is_reversal = 0), and does not already have a linked cash_receipt on the same (bank_account_id, bank_reference_id) composite key. If a receipt for the same composite key already exists with a different entry_status, the existing receipt is updated rather than creating a duplicate.

Foundation reference: Generate Cash Receipt from Bank Transaction

Workflow context: The "Generate Cash Receipt" action in the row actions menu is visible for non-reversal credit rows. Once a receipt exists, the action is replaced by a display of the cash_receipt_id. A PDNG-status credit may also be used to generate a receipt; the resulting cash_receipt.entry_status = 'PDNG' warns downstream users to apply with caution because the transaction may still reverse.

4. Data Access & Operations References

4.1 Queries Used

Operation	Foundation Doc	Purpose in This Workflow
`checkForDuplicate`	Check for Duplicate File	Run before any database write during file upload to detect prior uploads by content hash or message ID
`getAllBankFiles`	Get All Bank Files	Populates the Files tab listing all uploaded files ordered by upload date descending
`getRawXmlByFileId`	Get Raw XML by File ID	Loads `bank_file.raw_content` for the Raw XML viewer dialog when the user clicks the Raw button on a file row
`getAllTransactions`	Get All Transactions (Enriched)	Populates the Transactions tab; joins `bank_file`, `bank_account`, and `cash_receipt` to show file source, account name, and receipt linkage status per row
`searchTransactions`	Search Transactions	Supports optional server-side filtering by `bank_reference_id`, `status`, `currency`, `bank_file_id`, or `bank_account_id`
`getTransactionHistory`	Get Transaction History	Loads the processing timeline shown in the Transaction History side drawer when a user clicks a transaction row
`getHeaderByFileId`	Get Bank File Header	Loads `bank_file_header` metadata during upload processing; used internally
`getBalancesByFileId`	Get Bank File Balances	Retrieves `OPBD` and `CLBD` balance records per file for the daily reconciliation cards
`getDailyCashSummary`	Daily Cash Summary	Aggregates opening/closing balances, credits, debits, and pending counts by account for the Dashboard tab
`getCreditTransactionStatus`	Credit Transaction Status	Populates the Cash Application Insights card showing how many credits have receipts vs. those still needing them
`getFileGroupByParent`	Get File Group by Parent	Resolves CAMT.052 children under a CAMT.053 parent for the file group accordion in the Files tab
`getFilesGroupedByDate`	Get Files Grouped by Date for Account	Builds the hierarchical file tree per account and date for the multi-account reconciliation view
`findMatchingIntradayFiles`	Find Matching Intraday Files	Called during CAMT.053 upload to locate existing CAMT.052 files for auto-grouping
`getReconciliationByAccount`	TODO: Document in foundation/queries/bank-ingestion.md	Drives the multi-account reconciliation view; returns per-account balance, credit, debit, and variance data

4.2 Procedures Used

Operation	Foundation Doc	Trigger in This Workflow
`uploadBankFile`	Upload and Process a Bank File	User clicks the Upload button after selecting a bank and XML file on the Upload tab
`upsertBankTransaction`	Upsert Bank Transaction (Golden Record)	Called internally during `uploadBankFile` for each transaction that passes the gatekeeper filter
`autoGroupIntradayFiles`	Auto-Group Intraday Files Under an End-of-Day File	Called automatically at the end of `uploadBankFile` when the uploaded file type is `CAMT053`
`manualGroupFile`	Manually Group an Intraday File Under an End-of-Day File	User explicitly associates a CAMT.052 file with a CAMT.053 parent when auto-grouping did not find a match
`acceptReversal`	Accept a Reversal Transaction	User clicks "Accept Reversal" on a `PENDING` reversal transaction in the Transactions tab
`rejectReversal`	Reject a Reversal Transaction	User clicks "Reject Reversal" on a `PENDING` reversal transaction in the Transactions tab
`generateCashReceipt`	Generate Cash Receipt from Bank Transaction	User clicks "Generate Cash Receipt" in the row actions menu for a non-reversal credit transaction

5.1 Upload a Bank File

Preconditions:

User has the CASH_MANAGER or IT role.
An active bank record exists for the target bank (bank.active_ind = true).
The file has a .xml extension and contains a valid BkToCstmrStmt or BkToCstmrAcctRpt root element.
No bank_file record exists with the same content_hash or the same (source_bank_id, message_id) pair.
At least one <Ntry> element survives the gatekeeper filter (credit direction, whitelisted BAI code, no reversal flag).

Procedure reference: Upload and Process a Bank File

Steps:

Navigate to the Upload tab on the Bank File Ingestion page.
Select a source bank from the bank dropdown.
Click Browse Files and select an .xml file; the file name and size appear once selected.
Click the Upload button to submit.
The system runs validation, deduplication, parsing, account matching, and transaction upsert sequentially.
On success, a toast notification confirms upload and shows the count of processed transactions; the Files and Transactions tabs refresh automatically.
On failure (duplicate, bank mismatch, invalid format, zero accepted transactions), a toast notification shows the specific reason.

Postconditions:

bank_file.status = 'PROCESSED' and bank_file.transaction_count is set to the number of accepted transactions.
One bank_file_header row exists for the file.
Zero or more bank_file_balance rows exist (one per <Bal> element in the file).
Zero or one bank_file_summary row exists.
One bank_file_account row exists per matched bank_account in the file.
All accepted transactions are present in bank_transaction with status = 'PDNG' (CAMT.052) or 'BOOK' (CAMT.053) and cash_receipt_status = 'UNMATCHED'.
One bank_transaction_history row exists per transaction that was created or updated.
If file type is CAMT053, all matching CAMT.052 files are linked via bank_file_group with matching_criteria = 'AUTO'.

UI trigger: Upload button. Visible always on the Upload tab. Enabled only when a bank is selected and a file is selected; shows "Processing..." with a spinner while isUploading = true.

5.2 View Raw XML Content

Preconditions:

A bank_file record exists with bank_file.raw_content populated.

Procedure reference: Get Raw XML by File ID

Steps:

Navigate to the Files tab.
Click the Raw XML icon button in any file row.
A dialog opens; the system fetches bank_file.raw_content and displays it with formatted indentation.

Postconditions:

No data changes. Read-only operation.

UI trigger: Raw XML icon button (file code icon) in the Files table row Actions column. Visible for every row.

5.3 View Transaction History

Preconditions:

A bank_transaction record exists with at least one bank_transaction_history row.

Procedure reference: Get Transaction History

Steps:

Navigate to the Transactions tab.
Click any transaction row.
A side drawer opens showing a summary (bank reference, amount, current status, last source file, booking date) and a vertically stacked processing timeline.
Each timeline entry shows: action type (Created / Updated), status transition (from bank_transaction_history.from_status to bank_transaction_history.to_status), source file name and type, booking date, timestamp, and user who made the change.

Postconditions:

No data changes. Read-only operation.

UI trigger: Click anywhere on a transaction row. Available for every row in the Transactions table.

5.4 Generate Cash Receipt

Preconditions:

bank_transaction.direction = 'CREDIT'.
bank_transaction.is_reversal = 0.
No cash_receipt already exists with the composite key (bank_account_id, bank_ref_id) matching this transaction.
User has the CASH_MANAGER or IT role.

Procedure reference: Generate Cash Receipt from Bank Transaction

Steps:

Navigate to the Transactions tab.
Locate a credit transaction whose Receipt column shows no ID.
Open the row actions dropdown menu and click "Generate Cash Receipt".
The system validates eligibility, inserts one cash_receipt row and one default cash_receipt_split row.
On success, a toast notification confirms creation and shows the bank reference; the Transactions table refreshes silently to display the new cash_receipt_id in the Receipt column.

Postconditions:

One cash_receipt row exists with posting_status_cd = 'U', bank_transaction_id set, original_receipt_amt / receipt_amt / net_receipt_amt all equal to bank_transaction.amount, currency_cd and original_currency_cd from bank_transaction.currency, and entry_status copied from bank_transaction.status.
One cash_receipt_split row exists with split_amt = net_receipt_amt and split_status_cd = 'N'.
The receipt is ready to enter the split submission and worksheet creation workflow.

UI trigger: "Generate Cash Receipt" item in the row actions dropdown. Visible only when direction = 'CREDIT' and is_reversal = 0. Disabled with tooltip "Receipt Generated" when a cash_receipt_id already exists for the row.

5.5 Accept a Reversal

Preconditions:

bank_transaction.is_reversal = 1.
bank_transaction.reversal_status = 'PENDING'.
User has the CASH_MANAGER or IT role.

Procedure reference: Accept a Reversal Transaction

Steps:

Navigate to the Transactions tab.
Identify the reversal transaction by its reversal badge and PENDING reversal status indicator.
Open the row actions dropdown and click "Accept Reversal".
The system sets bank_transaction.reversal_status = 'ACCEPTED', reversal_accepted_at = current timestamp, and reversal_accepted_by = current user identifier.
On success, a toast notification confirms acceptance; the Transactions table and Dashboard tab refresh.
The cash processor must then manually locate the original transaction via bank_transaction.reversal_of_transaction_id, find any linked cash_receipt, and determine whether a worksheet return or receipt void is necessary.

Postconditions:

bank_transaction.reversal_status = 'ACCEPTED'.
bank_transaction.reversal_accepted_at and bank_transaction.reversal_accepted_by are set.
No automatic change to linked cash_receipt or cash_receipt_application records.

UI trigger: "Accept Reversal" item in the row actions dropdown. Visible only when is_reversal = 1 and reversal_status = 'PENDING'.

5.6 Reject a Reversal

Preconditions:

bank_transaction.is_reversal = 1.
bank_transaction.reversal_status = 'PENDING'.
User has the CASH_MANAGER or IT role.

Procedure reference: Reject a Reversal Transaction

Steps:

Navigate to the Transactions tab.
Identify the reversal transaction.
Open the row actions dropdown and click "Reject Reversal".
The system sets bank_transaction.reversal_status = 'REJECTED'.
On success, a toast notification confirms rejection; the Transactions table and Dashboard tab refresh.

Postconditions:

bank_transaction.reversal_status = 'REJECTED'.
No downstream action is taken on linked cash receipts or applications.
The reversal is treated as non-actionable from this point forward.

UI trigger: "Reject Reversal" item in the row actions dropdown. Visible only when is_reversal = 1 and reversal_status = 'PENDING'.

5.7 Manually Group an Intraday File

Preconditions:

A CAMT.053 bank_file record exists (file_type = 'CAMT053', status = 'PROCESSED').
A CAMT.052 bank_file record exists (file_type = 'CAMT052', status = 'PROCESSED', is_duplicate = false).
The combination (parent_file_id, child_file_id, bank_account_id) does not already exist in bank_file_group.
User has the CASH_MANAGER or IT role.

Procedure reference: Manually Group an Intraday File Under an End-of-Day File

Steps:

Navigate to the Files tab and identify the ungrouped CAMT.052 file and the target CAMT.053 parent file.
Use the manual grouping action to associate the intraday file with the end-of-day file.
The system inserts one bank_file_group row with matching_criteria = 'MANUAL'.

Postconditions:

One bank_file_group row exists with parent_file_id pointing to the CAMT.053 file, child_file_id pointing to the CAMT.052 file, and matching_criteria = 'MANUAL'.
The intraday file appears as a child under the end-of-day file in the file group accordion.

UI trigger: Manual grouping action in the Files tab.

NOTE

PoC Artifact: The manual grouping action is defined in the service and data model but the PoC UI does not expose a dedicated manual grouping button in the Files tab. Auto-grouping handles the standard case; manual grouping is a gap to address in production when auto-grouping fails to find a match (e.g., different statement dates, missing intraday file, multi-account file edge cases).

6. Permissions & Role-Based Access

Action	CASH_MANAGER	CASH_PROCESSOR	SETTLEMENT_APPROVER	IT
Upload bank file	Yes	—	—	Yes
View Files tab	Yes	Yes	Yes	Yes
View Transactions tab	Yes	Yes	Yes	Yes
View raw XML content	Yes	Yes	Yes	Yes
View transaction history	Yes	Yes	Yes	Yes
Generate cash receipt	Yes	—	—	Yes
Accept reversal	Yes	—	—	Yes
Reject reversal	Yes	—	—	Yes
Manual file grouping	Yes	—	—	Yes
View Dashboard tab	Yes	Yes	Yes	Yes

Field-level restrictions:

bank_file.raw_content is displayed read-only in the XML viewer dialog; no role can edit raw content through the UI.
bank_transaction.reversal_accepted_by is set automatically from the server-side user identity when a reversal is accepted; it cannot be edited directly.
Dashboard date selection is open to all roles that can view the Dashboard tab.

NOTE

PoC Artifact: Role enforcement in the PoC uses a hardcoded userId = "system" placeholder in the upload server action (actions.ts). Production must replace this with authenticated session-based role checks on all server actions in this workflow.

7. Integration Points

7.1 Upstream

Source	Data Provided	Mechanism
Bank of America (BOFA)	ISO 20022 CAMT.053 / CAMT.052 XML files with booked and pending credit transactions, balance snapshots, and remittance data	User-initiated file upload via the Upload tab; parsed by `Iso20022Adapter`; BAI codes `165`, `195`, `208` are accepted
JPMorgan Chase (JPM)	ISO 20022 CAMT.053 / CAMT.052 XML files	Same upload path and adapter; bank identity detected via JPM BIC codes and routing indicators
City National Bank (CNB)	CNB EASI Link JSON format (proprietary)	Same upload path; `CnbJsonAdapter` normalizes CNB JSON to the same internal `GenericBankTransactionDTO` structure used by the ISO 20022 adapter; `bank.file_format` = `'CNB_JSON'` selects the adapter
`bank` reference table	Active bank list for the source bank dropdown	FK lookup at upload time via `getBankOptions`
`bank_account` reference table	Account identifiers for matching file account blocks to UTA's bank accounts	Queried during the account matching step of `uploadBankFile`

7.2 Downstream

Consumer	Data Consumed	Mechanism
Cash Receipts Workflow	`cash_receipt` and `cash_receipt_split` records created from bank transactions	`generateCashReceipt` server action writes to `cash_receipt` (FK `cash_receipt.bank_transaction_id`) and auto-creates the default `cash_receipt_split`
Worksheets Workflow	`cash_receipt_split` as the starting point for worksheet creation	No direct FK from `cash_receipt_worksheet` to bank ingestion tables; the chain is `bank_transaction` → `cash_receipt` → `cash_receipt_split` → `cash_receipt_worksheet`
Accounting / GL Reconciliation	`bank_file_balance` closing booked balance (`CLBD`) records as daily cash position reconciliation anchors	Batch GL job reads `bank_file_balance` records for cash account reconciliation
Daily Cash Position Dashboard	`bank_transaction` and `bank_file_balance` records aggregated by date and account	`getDailyCashSummary` and `getCreditTransactionStatus` server actions compute in-memory summaries returned to the Dashboard tab

7.3 External Integrations

System	Direction	Protocol	Notes
Bank of America	Inbound	ISO 20022 XML (CAMT.052 / CAMT.053)	Files are obtained outside the system (e.g., via SFTP or bank portal) and manually uploaded by users
JPMorgan Chase	Inbound	ISO 20022 XML (CAMT.052 / CAMT.053)	Same manual upload path; bank identity detected via JPM BIC code and routing number patterns
City National Bank	Inbound	CNB EASI Link JSON	`CnbJsonAdapter` handles CNB's proprietary JSON format; `bank.adapter_class` = `CnbEasiAdapter` and `bank.file_format` = `CNB_JSON` drive adapter selection in the registry

8. Functional Screen Requirements

8.1 Bank File Ingestion Page

Route: /cash-management/bank-files

Data loading:

getBankOptions — loads active bank records for the source bank dropdown on the Upload tab
getBankFiles — loads all bank_file records via getAllBankFiles for the Files tab
getBankTransactions — loads all bank_transaction records enriched with file, account, and receipt data via getAllTransactions for the Transactions tab
getBankCodeLookups — loads code master display labels for transaction type, direction, entry status, and cash receipt status codes used in the Transactions table tooltips
getReconciliationData — loads daily cash position summary data via getDailyCashSummary and getReconciliationByAccount for the Dashboard tab
getCreditTransactionStatus — loads credit transaction receipt coverage breakdown via getCreditTransactionStatus for the Cash Application Insights card

The page uses four tabs: Dashboard, Upload, Files, and Transactions. All tabs are available on initial load; the Dashboard tab is the default.

Dashboard Tab

A date-driven daily cash position view. The user selects a date using a date input; all dashboard cards refresh when the date changes.

Sub-section: Multi-Account Reconciliation View

Contains sub-tabs: "By Account" (default when single currency), "Aggregate", and "By Currency" (shown only when multiple currencies are present across accounts).

By Account sub-tab — per-account card:

Field / Column	Source	Editable?	Condition
Bank ID badge	`bank.bank_id` via `bank_account.bank_id`	No	Always visible
Account number (last 4 digits)	`bank_account.bank_account_no`	No	Always visible
Opening balance	`bank_file_balance.amount` where `balance_type` = `'OPBD'`, sign-adjusted by `credit_debit_indicator`	No	Always visible; shows $0 when no balance record exists for the date
Closing balance	`bank_file_balance.amount` where `balance_type` = `'CLBD'`; falls back to `'ITBD'` for intraday-only days	No	Always visible
Net change	Computed: `closing_balance - opening_balance`	No	Always visible
Files processed	Count of `bank_file` records associated with this account and date	No	Always visible
Transaction count	Count of `bank_transaction` records for this account and date	No	Always visible
Pending amount alert	Sum of `bank_transaction.amount` where `status` = `'PDNG'`	No	Visible with warning icon when `pendingCount > 0`
All Booked indicator	Computed: true when all transactions have `status` = `'BOOK'`	No	Visible with checkmark when no pending transactions
Credit total	Sum of `bank_transaction.amount` where `direction` = `'CREDIT'`	No	Always visible in expanded card
Debit total	Sum of `bank_transaction.amount` where `direction` = `'DEBIT'`	No	Always visible in expanded card
Balance variance indicator	Computed: `isBalanced` flag (credits + opening should equal closing)	No	"All Balanced" badge when balanced; "Variance Detected" badge when not

Aggregate sub-tab:

Field / Column	Source	Editable?	Condition
Opening balance (aggregate)	Sum of `OPBD` balances across all accounts	No	Shown in single-currency scenario
Total credits (aggregate)	Sum of credit transaction amounts across all accounts	No	Shown in single-currency scenario
Total debits (aggregate)	Sum of debit transaction amounts across all accounts	No	Shown in single-currency scenario
Closing balance (aggregate)	Sum of `CLBD` balances across all accounts	No	Shown in single-currency scenario
Interim booked balance	Sum of `ITBD` balances across accounts that have them	No	Shown only when intraday data exists for the date
Multi-currency warning	Computed: triggered when accounts span more than one currency	No	Shown instead of aggregate amounts when multiple currencies are present
Account count	Count of accounts with transaction activity	No	Always shown
Total files processed	Count of `bank_file` records across all accounts	No	Always shown
Total transactions	Count of `bank_transaction` records across all accounts	No	Always shown

Sub-section: Cash Application Insights Card

Field / Column	Source	Editable?	Condition
Total credit transactions count	Count of `bank_transaction` where `direction` = `'CREDIT'` for the selected date	No	Always visible
Total credit amount	Sum of `bank_transaction.amount` where `direction` = `'CREDIT'`	No	Single-currency display; "See breakdown below" in multi-currency
Receipt Generated count/amount	Count/sum of credits where `cash_receipt_id` IS NOT NULL	No	Always visible
Booked – No Receipt count/amount	Count/sum where `status` = `'BOOK'` and `cash_receipt_id` IS NULL	No	Always visible
Pending – No Receipt count/amount	Count/sum where `status` = `'PDNG'` and `cash_receipt_id` IS NULL	No	Always visible; highlighted when count > 0
Receipt generation progress bar	Computed: percentage of credits with receipts	No	Always visible
By Currency breakdown	Per-currency totals from `bank_transaction.currency`	No	Shown when accounts have transactions in multiple currencies

Sub-section: Quick Summary Card

Field / Column	Source	Editable?	Condition
Files Uploaded	Count of all `bank_file` records (all dates)	No	Always visible
Total Transactions	Count of all `bank_transaction` records (all dates)	No	Always visible
Credits	Count of `bank_transaction` where `direction` = `'CREDIT'` (all dates)	No	Always visible
Debits	Count of `bank_transaction` where `direction` = `'DEBIT'` (all dates)	No	Always visible

Grid features:

No data grids on the Dashboard tab. Content consists of summary cards only.

Conditional display:

"By Currency" sub-tab appears only when the accounts active for the date span more than one currency.
"All Balanced" badge appears on the Multi-Account Reconciliation View header when data.totals.isBalanced = true; "Variance Detected" badge otherwise.
"All Have Receipts" badge on the Cash Application Insights card when all credits for the date have receipts.
Loading indicator shown when isLoadingDashboard = true.
"No credit transaction data for this date" empty state shown when creditTxStatus is null.

Upload Tab

Allows users to upload an ISO 20022 XML bank statement file.

Field / Column	Source	Editable?	Condition
Source Bank dropdown	Active banks from `bank.bank_id` / `bank.bank_name`	Yes	Always visible; disabled while `isLoadingBanks` = `true` or `isUploading` = `true`
Browse Files button	Browser file picker; accepts `.xml` files only	Yes	Always visible; disabled while `isUploading` = `true`
Selected file display	Local browser file object	No	Visible when a file has been chosen; shows file name and size in KB
Upload button	—	Yes	Always visible; enabled only when both bank and file are non-empty and `isUploading` = `false`

Conditional display:

Upload button shows "Processing..." with a spinner icon while isUploading = true.
Selected file display card appears only after the user has chosen a file.
Source Bank dropdown shows "Loading..." while isLoadingBanks = true.

Files Tab

Lists all uploaded bank_file records.

Field / Column	Source	Editable?	Condition
Created	`bank_file.created_dt`	No	Always visible; formatted as MM/DD/YYYY HH:MM
File Name	`bank_file.file_name`	No	Always visible; monospace font
Source Bank	`bank_file.source_bank_id`	No	Always visible; badge with full bank name as tooltip
File Type	`bank_file.file_type`	No	Always visible; `CAMT053` shown as "End of Day", `CAMT052` as "Intraday"; hover tooltip explains balance types and entry statuses
Seq #	`bank_file.electronic_seq_nb`	No	Visible; shown as `-` when null; tooltip: "Electronic Sequence Number - Order of files within the day"
Dup	`bank_file.is_duplicate`	No	Duplicate badge visible only when `is_duplicate` = `true`; hover tooltip shows `duplicate_of_file_id`
XSD	`bank_file.xsd_name`	No	Visible; `-` when null
Message ID	`bank_file.message_id`	No	Truncated to 20 characters; full value in tooltip when longer
Ccy	`bank_file.statement_currency`	No	Badge; `-` when null
Txns	`bank_file.transaction_count`	No	Count of accepted transactions; `-` when null
Status	`bank_file.status`	No	Badge: `PROCESSED` (green), `ERROR` (red), `DUPLICATE` (orange), `PENDING` (yellow)
Uploaded By	`bank_file.created_by`	No	Always visible; `-` when null
Raw	Action button	No	Always visible; icon button opens the Raw XML viewer dialog

Grid features:

Sortable columns: Created, File Name, Source Bank, File Type, Seq #, Status, Uploaded By.
Filters: None exposed in the PoC UI.
Row selection: None.
Pagination: Yes; default page size determined by the shared DataTable component.

Conditional display:

Duplicate badge visible only when bank_file.is_duplicate = true.
Raw XML dialog opens over the page when the Raw icon button is clicked; shows bank_file.raw_content with pretty-print indentation.
"No bank files uploaded yet" empty state shown when the data array is empty.
"Loading bank files..." indicator shown when isLoadingFiles = true.

Transactions Tab

Lists all bank_transaction records enriched with file, account, and receipt linkage data.

Field / Column	Source	Editable?	Condition
Booking Date	`bank_transaction.booking_date`	No	Always visible
Reference	`bank_transaction.bank_reference_id`	No	Always visible; monospace font
Amount	`bank_transaction.amount` + `bank_transaction.currency`	No	Always visible; prefixed `+` for credits, `-` for debits
Status	`bank_transaction.status` (`BOOK`, `PDNG`, `INFO`, `FUTR`)	No	Always visible; status badge with status description tooltip
Direction	`bank_transaction.direction` (`CREDIT` / `DEBIT`)	No	Always visible; directional indicator
Account	`bank_account.bank_account_name` + `bank_account.bank_account_no`	No	Always visible
Source File	`bank_file.file_name`	No	Always visible
Tx Code	`bank_transaction.bank_transaction_code`	No	Shown with hover popover from `bank_code_mapping` data
Payer	`bank_transaction.payer_name`	No	Shown as `-` when null
Invoice #	`bank_transaction.invoice_number`	No	Shown as `-` when null
Reversal badge	`bank_transaction.is_reversal`, `bank_transaction.reversal_status`, `bank_transaction.reversal_of_transaction_id`	No	Reversal badge and reversal status badge visible when `is_reversal` = `1`; link to original transaction reference shown when `reversal_of_transaction_id` is set
Receipt	`cash_receipt.cash_receipt_id` via enriched join on `(bank_account_id, bank_reference_id)`	No	Shows `#[id]` link when a receipt exists; blank when `cash_receipt_status` = `'UNMATCHED'`
Actions	Row actions dropdown	No	Always visible

Grid features:

Sortable columns: Booking Date, Reference, Amount, Status, Direction, Account, Source File.
Filters: None exposed via UI in PoC. The searchTransactions query supports server-side filtering by bank_reference_id, status, currency, bank_file_id, or bank_account_id.
Row selection: None. Clicking a row opens the Transaction History side drawer.
Pagination: Yes; default page size determined by the shared DataTable component.

Conditional display:

"Accept Reversal" action visible in the row actions dropdown only when is_reversal = 1 and reversal_status = 'PENDING'.
"Reject Reversal" action visible only when is_reversal = 1 and reversal_status = 'PENDING'.
"Generate Cash Receipt" action visible when direction = 'CREDIT' and is_reversal = 0; disabled and shows "Receipt Generated" tooltip when cashReceiptId is non-null.
"Reversal - Cannot Generate" tooltip shown in place of the Generate action when is_reversal = 1.
Reversal status badge (PENDING / ACCEPTED / REJECTED) shown alongside the reversal indicator badge when is_reversal = 1.
"Loading bank transactions..." indicator shown when isLoading = true.
Transaction count shown in the tab heading when isLoading = false and transactions.length > 0.

8.2 Transaction History Side Drawer

Route: Overlay panel on /cash-management/bank-files; no dedicated route.

Data loading:

getTransactionHistory — loads all bank_transaction_history records for the selected bank_transaction_id, enriched with bank_file.file_name and bank_file.file_type, ordered by created_dt descending.

Transaction Summary Region

Shows key fields for the selected transaction at the top of the drawer.

Field / Column	Source	Editable?	Condition
Bank Reference	`bank_transaction.bank_reference_id`	No	Always visible; monospace font
Amount	`bank_transaction.amount` + `bank_transaction.currency`	No	Always visible; prefixed `+` for credits, `-` for debits
Current Status	`bank_transaction.status`	No	Always visible; status badge
Last Source File	`bank_file.file_name`	No	Always visible
Booking Date	`bank_transaction.booking_date`	No	Visible when `booking_date` is non-null

Processing Timeline Region

Append-only audit trail showing every state change for this transaction.

Field / Column	Source	Editable?	Condition
Action type badge	`bank_transaction_history.action_type` (`CREATE` → "Created", `UPDATE` → "Updated")	No	Always visible per entry
From status	`bank_transaction_history.from_status`	No	Visible only on UPDATE entries (null on initial CREATE)
To status	`bank_transaction_history.to_status`	No	Always visible per entry
Source file name	`bank_file.file_name`	No	Always visible; truncated with full name as tooltip
File type badge	`bank_file.file_type` (`CAMT052` → "Intraday", `CAMT053` → "End of Day")	No	Always visible per entry
Booking date	`bank_transaction_history.booking_date`	No	Visible when non-null
Timestamp	`bank_transaction_history.created_dt`	No	Always visible per entry
Changed by	`bank_transaction_history.created_by`	No	Visible when non-null

Grid features:

No data grid. The timeline is a vertically stacked list with connecting lines between entries.

Conditional display:

"Loading timeline..." shown while the history is being fetched.
"No history available" empty state shown when the history array is empty.
The drawer is visible when triggered by a transaction row click; closes via the sheet close button or clicking outside.

9. Additional Diagrams

File Processing Status Lifecycle

mermaid

stateDiagram-v2
    [*] --> PENDING : bank_file record created on upload
    PENDING --> PROCESSED : All transactions upserted successfully; transaction_count set
    PENDING --> ERROR : Parse failure or unrecoverable error; raw_content preserved
    PENDING --> DUPLICATE : content_hash or (source_bank_id, message_id) matches existing file

Transaction Entry Status Promotion

mermaid

stateDiagram-v2
    [*] --> PDNG : CAMT.052 intraday file creates new transaction
    [*] --> BOOK : CAMT.053 end-of-day creates new transaction with no prior intraday record
    PDNG --> BOOK : CAMT.053 end-of-day file upserts matching (bank_account_id, bank_reference_id)
    FUTR --> BOOK : End-of-day file confirms future-dated settlement

Reversal Status Lifecycle

mermaid

stateDiagram-v2
    [*] --> PENDING : Reversal transaction inserted during file parse (is_reversal = 1)
    PENDING --> ACCEPTED : Cash manager accepts reversal; reversal_accepted_at and _by set
    PENDING --> REJECTED : Cash manager rejects reversal; no downstream action

Cash Receipt Reconciliation Status

mermaid

stateDiagram-v2
    [*] --> UNMATCHED : bank_transaction inserted; default cash_receipt_status
    UNMATCHED --> MATCHED : Cash receipt generated with amount equal to transaction amount
    UNMATCHED --> PARTIAL : Cash receipt generated with amount less than transaction amount
    PARTIAL --> MATCHED : Additional receipt(s) cover remaining balance

File Ingestion Sequence: CAMT.052 Followed by CAMT.053

mermaid

sequenceDiagram
    participant U as User
    participant SYS as System
    participant DB as Database

    U->>SYS: Upload CAMT.052 (intraday) file, bank = BOFA
    SYS->>DB: Dedup check — no match
    SYS->>DB: INSERT bank_file (status=PENDING, fileType=CAMT052)
    SYS->>DB: INSERT bank_file_header, bank_file_balance, bank_file_summary
    SYS->>DB: INSERT bank_transaction (status=PDNG) × N
    SYS->>DB: INSERT bank_transaction_history (action=CREATE, toStatus=PDNG) × N
    SYS->>DB: UPDATE bank_file (status=PROCESSED)
    SYS-->>U: Success toast: N transactions processed

    U->>SYS: Upload CAMT.053 (end-of-day) file, same date and bank
    SYS->>DB: Dedup check — no match
    SYS->>DB: INSERT bank_file (status=PENDING, fileType=CAMT053)
    SYS->>DB: INSERT bank_file_header, bank_file_balance (OPBD+CLBD), bank_file_summary
    SYS->>DB: UPDATE bank_transaction (status=PDNG→BOOK) × N
    SYS->>DB: INSERT bank_transaction_history (action=UPDATE, fromStatus=PDNG, toStatus=BOOK) × N
    SYS->>DB: UPDATE bank_file (status=PROCESSED)
    SYS->>DB: INSERT bank_file_group (matchingCriteria=AUTO) linking CAMT.053 → CAMT.052
    SYS-->>U: Success toast: N transactions processed; intraday file grouped

10. Cross-References

Document	Relationship
Bank Ingestion Data Model	Defines all tables (`bank_file`, `bank_file_header`, `bank_file_balance`, `bank_file_summary`, `bank_file_account`, `bank_file_group`, `bank_transaction`, `bank_transaction_history`, `bank_code_mapping`), status codes, constraints, and code master values used throughout this workflow
Bank Ingestion Queries	Specifies every retrieval, aggregation, and enriched read operation referenced in Section 4.1 and Section 8
Bank Ingestion Procedures	Specifies every data mutation procedure triggered by user actions in this workflow (upload, upsert, grouping, reversal, receipt generation)
Cash Receipts Data Model	`cash_receipt.bank_transaction_id` → `bank_transaction.bank_transaction_id`; `cash_receipt.bank_ref_id` + `cash_receipt.bank_account_id` form the composite link back to the originating bank transaction
Cash Receipts Workflow	Downstream: cash receipts generated by this workflow enter the split submission, approval, and worksheet creation workflow described there
Worksheets Workflow	Further downstream: worksheets created from approved splits apply cash against billing item receivables; no direct FK from worksheets to bank ingestion tables
Payments Workflow	Unrelated in the upstream direction; outbound payments use `CnbEasiAdapter` and `Iso20022Adapter` for pain.001 transmission to banks, separate from the inbound file ingestion path

11. Gherkin Scenarios

gherkin

Feature: Bank Ingestion - File Upload and Processing

  Scenario: Successfully upload a CAMT.053 end-of-day file from Bank of America
    Given the user has the CASH_MANAGER role
    And an active bank record exists where bank.bank_id = 'BOFA'
    And a bank_account record exists where bank_account.bank_id = 'BOFA'
      and bank_account.bank_account_no matches the account block in the file
    And no bank_file record exists with content_hash matching the file
      or with (source_bank_id = 'BOFA', message_id = 'MSG20260201001')
    When the user selects bank 'BOFA' on the Upload tab
      and uploads file 'bofa_stmt_20260201.xml' containing 3 credit entries with BAI code 195
    Then bank_file.status = 'PROCESSED' and bank_file.transaction_count = 3
    And 3 bank_transaction records exist with status = 'BOOK' and cash_receipt_status = 'UNMATCHED'
    And 3 bank_transaction_history records exist with action_type = 'CREATE' and to_status = 'BOOK'
    And 1 bank_file_header record exists for the new bank_file_id
    And bank_file_balance records exist for each balance element in the file

  Scenario: CAMT.053 upload promotes a PDNG transaction to BOOK
    Given a bank_transaction record exists where:
      bank_account_id = 5, bank_reference_id = 'REF20260201001', status = 'PDNG'
    And 1 bank_transaction_history record exists for it with action_type = 'CREATE', to_status = 'PDNG'
    When the user uploads a CAMT.053 file containing the same bank_reference_id for bank_account_id = 5
    Then bank_transaction.status = 'BOOK' for (bank_account_id = 5, bank_reference_id = 'REF20260201001')
    And a second bank_transaction_history record exists with:
      action_type = 'UPDATE', from_status = 'PDNG', to_status = 'BOOK'
    And no duplicate bank_transaction row is created for this composite key

  Scenario: Duplicate file upload is rejected
    Given a bank_file record exists where content_hash = 'sha256abc123' and status = 'PROCESSED'
    When the user uploads a file with identical XML content (same SHA-256 hash = 'sha256abc123')
    Then the upload is rejected with an error containing "Duplicate file detected"
      and the original bank_file_id
    And no new bank_file record is created
    And no bank_transaction records are created or modified

  Scenario: Bank mismatch with HIGH confidence is rejected
    Given an active bank record exists where bank.bank_id = 'JPM'
    And an active bank record exists where bank.bank_id = 'BOFA'
    And the file's BIC code and servicer name clearly identify the file as from 'BOFA' with HIGH confidence
    When the user selects bank 'JPM' and uploads the Bank of America file
    Then the upload is rejected with an error containing "Bank mismatch" naming both 'JPM' and 'BOFA'
    And no bank_file record is created

  Scenario: File with zero transactions surviving the gatekeeper filter is rejected
    Given a valid CAMT.053 XML file where all entries have CdtDbtInd = 'DBIT'
    When the user selects the correct bank and uploads the file
    Then the upload is rejected with error:
      "No transactions found in the file (may have been filtered by gatekeeper rules)"
    And no bank_file record is created

  Scenario: CAMT.053 upload auto-groups previously uploaded CAMT.052 file
    Given a bank_file record exists where:
      source_bank_id = 'BOFA', file_type = 'CAMT052', is_duplicate = false
    And a bank_file_account record links it to bank_account_id = 3 with statement_date = '2026-02-01'
    And no bank_file_group record references this CAMT.052 file
    When the user uploads a CAMT.053 file for bank_account_id = 3 with statement_date = '2026-02-01'
    Then a bank_file_group record is created with:
      parent_file_id = new CAMT.053 bank_file_id
      child_file_id = existing CAMT.052 bank_file_id
      matching_criteria = 'AUTO'

Feature: Bank Ingestion - Reversal Management

  Scenario: Cash manager accepts a pending reversal
    Given a bank_transaction record exists where:
      bank_transaction_id = 77, is_reversal = 1, reversal_status = 'PENDING'
      reversal_of_transaction_id = 42
    And bank_transaction 42 has cash_receipt_status = 'MATCHED'
    When the user clicks "Accept Reversal" on transaction 77
    Then bank_transaction.reversal_status = 'ACCEPTED' for bank_transaction_id = 77
    And bank_transaction.reversal_accepted_at is set to the current timestamp
    And bank_transaction.reversal_accepted_by is set to the current user identifier
    And bank_transaction 42 is NOT automatically modified
    And the cash processor must manually review cash receipts linked to transaction 42

  Scenario: Cash manager rejects a reversal as a false positive
    Given a bank_transaction record exists where is_reversal = 1, reversal_status = 'PENDING'
    When the user clicks "Reject Reversal"
    Then bank_transaction.reversal_status = 'REJECTED'
    And no cash_receipt or cash_receipt_application records are modified

  Scenario: Cannot accept an already-accepted reversal
    Given a bank_transaction record exists where is_reversal = 1, reversal_status = 'ACCEPTED'
    When the user attempts to call the acceptReversal procedure for this transaction
    Then the operation returns { success: false, error: 'Reversal already accepted' }
    And bank_transaction.reversal_status remains 'ACCEPTED'

  Scenario: Cannot reject a reversal that is not in PENDING status
    Given a bank_transaction record exists where is_reversal = 1, reversal_status = 'ACCEPTED'
    When the user attempts to call the rejectReversal procedure for this transaction
    Then the operation returns { success: false, error: 'Reversal is not pending' }
    And bank_transaction.reversal_status remains 'ACCEPTED'

Feature: Bank Ingestion - Cash Receipt Generation

  Scenario: Generate cash receipt from a booked credit transaction
    Given a bank_transaction record exists where:
      bank_transaction_id = 101, bank_account_id = 3, bank_reference_id = 'REF20260203005'
      status = 'BOOK', direction = 'CREDIT', is_reversal = 0
      amount = 50000.00, currency = 'USD', booking_date = '2026-02-03'
    And no cash_receipt exists where bank_account_id = 3 and bank_ref_id = 'REF20260203005'
    When the user clicks "Generate Cash Receipt" on this transaction
    Then a cash_receipt record is created with:
      bank_transaction_id = 101, bank_account_id = 3, bank_ref_id = 'REF20260203005'
      original_receipt_amt = 50000.00, receipt_amt = 50000.00, net_receipt_amt = 50000.00
      currency_cd = 'USD', original_currency_cd = 'USD'
      posting_status_cd = 'U', entry_status = 'BOOK'
      deposit_date = '2026-02-03', booking_date = '2026-02-03'
    And a cash_receipt_split record is created with:
      split_amt = 50000.00, split_status_cd = 'N'
    And the Transactions table refreshes to show the new cash_receipt_id

  Scenario: Cannot generate cash receipt for a reversal transaction
    Given a bank_transaction record exists where is_reversal = 1, direction = 'CREDIT'
    When the user views the transaction in the Transactions tab
    Then the row actions menu does not show "Generate Cash Receipt"
      and shows "Reversal - Cannot Generate" in its place

  Scenario: Cannot generate a second cash receipt when one already exists
    Given a cash_receipt record exists with bank_account_id = 3, bank_ref_id = 'REF20260203005'
    And a bank_transaction record exists for the same (bank_account_id, bank_reference_id)
    When the user views the transaction in the Transactions tab
    Then the "Generate Cash Receipt" button in the row actions menu is disabled
      and shows "Receipt Generated" with the existing cash_receipt_id

  Scenario: Receipt generation blocked for debit-direction transaction
    Given a bank_transaction record exists where direction = 'DEBIT'
    When the user views the transaction in the Transactions tab
    Then the "Generate Cash Receipt" action is not shown for this row

Feature: Bank Ingestion - Daily Cash Position Dashboard

  Scenario: Daily cash position shows expected balances and all-booked status
    Given 2 bank_transaction records exist for bank_account_id = 2 on booking_date = '2026-02-03'
      with direction = 'CREDIT', status = 'BOOK', total amount = 250000.00 USD
    And bank_file_balance records exist for the same account and date:
      balance_type = 'OPBD', amount = 5000000.00, credit_debit_indicator = 'CRDT'
      balance_type = 'CLBD', amount = 5250000.00, credit_debit_indicator = 'CRDT'
    When the user selects date '2026-02-03' on the Dashboard tab
    Then the By Account view for bank_account_id = 2 shows:
      opening_balance = 5000000.00
      closing_balance = 5250000.00
      net_change = 250000.00
      total_credits = 250000.00
    And the "All Booked" indicator is visible for this account
    And the balance status badge shows "All Balanced"

  Scenario: Variance detected when net change does not equal reported transaction totals
    Given bank_file_balance records show OPBD = 5000000.00 and CLBD = 5300000.00
      for bank_account_id = 2 on date '2026-02-01'
    And bank_transaction records for that account and date total only 250000.00 in credits
    When the user selects date '2026-02-01' on the Dashboard tab
    Then net_change = 300000.00 but total_credits = 250000.00
    And the balance status badge shows "Variance Detected" for this account

  Scenario: All credits have receipts - dashboard shows fully processed state
    Given 4 bank_transaction records exist for date '2026-02-03'
      with status = 'BOOK', direction = 'CREDIT'
    And all 4 have a linked cash_receipt (cash_receipt_id IS NOT NULL in the enriched query)
    When the user selects date '2026-02-03' on the Dashboard tab
    Then the Cash Application Insights card shows:
      total_credits.count = 4, with_receipt.count = 4
      booked_no_receipt.count = 0, pending_no_receipt.count = 0
    And the card displays "All Have Receipts" badge

Sample Deals

Touring Deal

Endorsement Deal

Sports Endo Deal

Bank Ingestion Workflow ​

1. Executive Summary ​

Purpose ​

Scope ​

Key Objectives ​

2. Process Overview ​

Walkthrough ​

3. Business Rules ​

3.1 Golden Record: One Transaction Per Account-Reference Pair ​

3.2 File-Level Deduplication ​

3.3 Gatekeeper Filter: Credit-Only, BAI Whitelist, No Reversal Flags ​

3.4 Bank Identity Validation ​

3.5 Lump-Sum Amount Rule ​

3.6 Reversal Transactions Require Manual Review ​

3.7 Auto-Grouping Triggered Only by CAMT.053 Upload ​

3.8 Cash Receipt Generation Requires a BOOK-Status Non-Reversal Credit ​

4. Data Access & Operations References ​

4.1 Queries Used ​

4.2 Procedures Used ​

5. Key User Actions ​

5.1 Upload a Bank File ​

5.2 View Raw XML Content ​

5.3 View Transaction History ​

5.4 Generate Cash Receipt ​

5.5 Accept a Reversal ​

5.6 Reject a Reversal ​

5.7 Manually Group an Intraday File ​

6. Permissions & Role-Based Access ​

7. Integration Points ​

7.1 Upstream ​

7.2 Downstream ​

7.3 External Integrations ​

8. Functional Screen Requirements ​

8.1 Bank File Ingestion Page ​

Dashboard Tab ​

Upload Tab ​

Files Tab ​

Transactions Tab ​

8.2 Transaction History Side Drawer ​

Transaction Summary Region ​

Processing Timeline Region ​

9. Additional Diagrams ​

File Processing Status Lifecycle ​

Transaction Entry Status Promotion ​

Reversal Status Lifecycle ​

Cash Receipt Reconciliation Status ​

File Ingestion Sequence: CAMT.052 Followed by CAMT.053 ​

10. Cross-References ​

11. Gherkin Scenarios ​

Bank Ingestion Workflow

1. Executive Summary

Purpose

Scope

Key Objectives

2. Process Overview

Walkthrough

3. Business Rules

3.1 Golden Record: One Transaction Per Account-Reference Pair

3.2 File-Level Deduplication

3.3 Gatekeeper Filter: Credit-Only, BAI Whitelist, No Reversal Flags

3.4 Bank Identity Validation

3.5 Lump-Sum Amount Rule

3.6 Reversal Transactions Require Manual Review

3.7 Auto-Grouping Triggered Only by CAMT.053 Upload

3.8 Cash Receipt Generation Requires a BOOK-Status Non-Reversal Credit

4. Data Access & Operations References

4.1 Queries Used

4.2 Procedures Used

5. Key User Actions

5.1 Upload a Bank File

5.2 View Raw XML Content

5.3 View Transaction History

5.4 Generate Cash Receipt

5.5 Accept a Reversal

5.6 Reject a Reversal

5.7 Manually Group an Intraday File

6. Permissions & Role-Based Access

7. Integration Points

7.1 Upstream

7.2 Downstream

7.3 External Integrations

8. Functional Screen Requirements

8.1 Bank File Ingestion Page

Dashboard Tab

Upload Tab

Files Tab

Transactions Tab

8.2 Transaction History Side Drawer

Transaction Summary Region

Processing Timeline Region

9. Additional Diagrams

File Processing Status Lifecycle

Transaction Entry Status Promotion

Reversal Status Lifecycle

Cash Receipt Reconciliation Status

File Ingestion Sequence: CAMT.052 Followed by CAMT.053

10. Cross-References

11. Gherkin Scenarios