Cash Receipts Queries

Data-model reference: ../data-model/cash-receipts.md
Domain tables: cash_receipt, cash_receipt_split, cash_receipt_reference, cash_receipt_adjustment

1. Executive Summary

This document catalogues every read and computed-value operation that the application performs against the four cash-receipt-domain tables. The queries are organized into four groups that mirror the user's workflow:

Cash Receipt queries — finding, filtering, and aggregating receipt records.
Cash Receipt Split queries — listing, counting, and computing balances for the splits that subdivide each receipt.
Cash Receipt Adjustment queries — retrieving and summing the adjustments that reduce a receipt's net amount.
Cash Receipt Reference queries — resolving the informational tags (client, deal, buyer, etc.) attached to each split.

Each section also documents the computed values that the service layer derives in memory from the raw query results, such as available balance, net receipt amount, and split-total validation.

2. Key Queries

2.1 Cash Receipt Queries

2.1.1 Get Cash Receipt by ID

Operation: getCashReceiptById

Input Parameters:

cashReceiptId: Integer (required) — cash_receipt.cash_receipt_id

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`cash_receipt`	—	—	—	Single-table lookup; no joins required

Filters:

cash_receipt.cash_receipt_id = :cashReceiptId

Computed Values:

net_receipt_amt: defaults to receipt_amt when the stored value is null

Returns: Single cash_receipt row or null.

2.1.2 Get Cash Receipt with Parties

Operation: getCashReceiptWithParties

Input Parameters:

cashReceiptId: Integer (required) — cash_receipt.cash_receipt_id

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`cash_receipt`	—	—	—	Single-table lookup; no joins required

Filters:

cash_receipt.cash_receipt_id = :cashReceiptId

Computed Values:

None

Returns: Single cash receipt row with all core display fields, or null.

2.1.3 Search Cash Receipts

Operation: searchCashReceipts

Input Parameters:

cashReceiptRef: String (optional) — cash_receipt.cash_receipt_ref, partial case-insensitive match
bankAccountId: Integer (optional) — cash_receipt.bank_account_id, equality match
depositDateFrom: Date (optional) — lower bound on cash_receipt.deposit_date
depositDateTo: Date (optional) — upper bound on cash_receipt.deposit_date
filename: String (optional) — cash_receipt.filename, partial case-insensitive match
postingStatusCd: String (optional) — cash_receipt.posting_status_cd, equality match

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`cash_receipt`	LEFT	`bank_account`	`cash_receipt.bank_account_id = bank_account.bank_account_id`	Bank account name for display
`cash_receipt`	LEFT	`users` (as `created_by_user`)	`cash_receipt.created_by = CAST(users.user_id AS TEXT)`	Resolve created-by display name
`cash_receipt`	LEFT	`users` (as `updated_by_user`)	`cash_receipt.updated_by = CAST(users.user_id AS TEXT)`	Resolve updated-by display name
`cash_receipt`	LEFT	`users` (as `locked_by_user`)	`cash_receipt.locked_by_user_id = users.user_id`	Resolve lock-holder display name

Filters:

Dynamic AND of all supplied criteria
cashReceiptRef and filename: case-insensitive LIKE (%value%)
depositDateFrom: cash_receipt.deposit_date >= :depositDateFrom
depositDateTo: cash_receipt.deposit_date <= :depositDateTo
bankAccountId, postingStatusCd: equality comparisons
Results ordered by cash_receipt.created_dt DESC, cash_receipt.cash_receipt_id DESC

Computed Values:

created_by_name: COALESCE(first_name || ' ' || last_name, created_by) from created_by_user alias
updated_by_name: COALESCE(first_name || ' ' || last_name, updated_by) from updated_by_user alias
locked_by_user_name: COALESCE(first_name || ' ' || last_name, CAST(locked_by_user_id AS TEXT)) from locked_by_user alias

Returns: Array of cash receipt rows enriched with bank account name and resolved user display names, ordered by creation date descending.

2.1.4 Get Cash Receipts by Client ID

Operation: getCashReceiptsByClientId

Input Parameters:

clientId: Integer (required) — billing_item.client_id

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`cash_receipt`	LEFT	`cash_receipt_split`	`cash_receipt.cash_receipt_id = cash_receipt_split.cash_receipt_id`	Traverse to worksheets
`cash_receipt_split`	LEFT	`cash_receipt_worksheet`	`cash_receipt_split.cash_receipt_split_id = cash_receipt_worksheet.cash_receipt_split_id`	Traverse to applications
`cash_receipt_worksheet`	LEFT	`cash_receipt_application`	`cash_receipt_worksheet.cash_receipt_worksheet_id = cash_receipt_application.cash_receipt_worksheet_id`	Traverse to billing item details
`cash_receipt_application`	LEFT	`billing_item_detail`	`cash_receipt_application.billing_item_detail_id = billing_item_detail.billing_item_detail_id`	Filter by REV type
`billing_item_detail`	LEFT	`billing_item`	`billing_item_detail.billing_item_id = billing_item.billing_item_id`	Filter by client and resolve deal context
`billing_item`	LEFT	`deal`	`billing_item.deal_id = deal.deal_id`	Deal name for display
`billing_item`	LEFT	`party` (as `buyer_party`)	`billing_item.buyer_id = party.party_id`	Buyer display name
`billing_item`	LEFT	`party` (as `client_party`)	`billing_item.client_id = party.party_id`	Client display name
`billing_item`	LEFT	`uta_entity`	`billing_item.uta_entity_id = uta_entity.uta_entity_id`	Entity name for display
`billing_item`	LEFT	`department`	`billing_item.department_id = department.department_id`	Department name for display

Filters:

billing_item.client_id = :clientId
billing_item_detail.billing_item_detail_type_cd = 'REV'
cash_receipt_worksheet.current_item_ind = true

Computed Values:

Post-query deduplication: keeps first row per cash_receipt_id (the join fans out through applications)
Service layer enriches with posting_status_desc, currency_desc, original_currency_desc from code_master

Returns: Deduplicated array of receipt rows with deal, buyer, client, entity, and department names for the client-detail payment history view.

2.1.5 Find Cash Receipt by Bank Reference

Operation: findByBankReference

Input Parameters:

bankAccountId: Integer (required) — cash_receipt.bank_account_id
bankRefId: String (required) — cash_receipt.bank_ref_id

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`cash_receipt`	—	—	—	Single-table lookup using composite unique key; no joins required

Filters:

cash_receipt.bank_account_id = :bankAccountId
cash_receipt.bank_ref_id = :bankRefId

Computed Values:

None

Returns: Single cash_receipt row or null.

NOTE

The (bank_account_id, bank_ref_id) pair is enforced as a unique index on the table, preventing duplicate receipts from bank ingestion. This query is called during the "living mirror" sync to detect whether a receipt already exists for an imported bank transaction.

2.1.6 Get Unposted Receipts Metric

Operation: getUnpostedMetric

Input Parameters:

None

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`cash_receipt`	—	—	—	Aggregate scan; no joins required

Filters:

cash_receipt.posting_status_cd = 'U'

Computed Values:

count: COUNT(*) of matching rows
total_amount: COALESCE(SUM(net_receipt_amt), 0)

Returns: Single row containing { count: integer, total_amount: decimal } for the dashboard unposted receipts card.

2.1.7 Get Cash Received Today Total

Operation: getCashReceivedTodayTotal

Input Parameters:

None

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`cash_receipt`	—	—	—	Aggregate scan; no joins required

Filters:

cash_receipt.deposit_date = CURRENT_DATE

Computed Values:

COALESCE(SUM(net_receipt_amt), 0)

Returns: Single decimal string representing the total net amount of all receipts deposited today.

2.1.8 Get Cash Receipt Totals by Date Range

Operation: getCashReceiptTotalByDateRange

Input Parameters:

startDate: Date (required) — lower bound on cash_receipt.deposit_date
endDate: Date (required) — upper bound on cash_receipt.deposit_date

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`cash_receipt`	—	—	—	Aggregate scan grouped by date; no joins required

Filters:

cash_receipt.deposit_date >= :startDate
cash_receipt.deposit_date <= :endDate

Computed Values:

COALESCE(SUM(net_receipt_amt), 0) grouped by deposit_date

Returns: Array of { date, amount } rows ordered by deposit_date ASC, used for charting cash inflows over a period.

2.1.9 Get Unposted Receipts Up to Date

Operation: getUnpostedReceiptsUpToDate

Input Parameters:

cutoffDate: Date (required) — upper bound on cash_receipt.deposit_date

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`cash_receipt`	LEFT	`bank_account`	`cash_receipt.bank_account_id = bank_account.bank_account_id`	Bank account name and `uta_account_id` for GL posting
`cash_receipt`	LEFT	`cash_receipt_split`	`cash_receipt.cash_receipt_id = cash_receipt_split.cash_receipt_id`	Traverse to current worksheet
`cash_receipt_split`	LEFT	`cash_receipt_worksheet`	`cash_receipt_split.cash_receipt_split_id = cash_receipt_worksheet.cash_receipt_split_id` AND `cash_receipt_worksheet.current_item_ind = true`	Current worksheet status
`cash_receipt`	LEFT	`users` (as `created_by_user`)	`cash_receipt.created_by = CAST(users.user_id AS TEXT)`	Resolve created-by display name
`cash_receipt`	LEFT	`users` (as `updated_by_user`)	`cash_receipt.updated_by = CAST(users.user_id AS TEXT)`	Resolve updated-by display name
`cash_receipt`	LEFT	`users` (as `locked_by_user`)	`cash_receipt.locked_by_user_id = users.user_id`	Resolve lock-holder display name

Filters:

cash_receipt.posting_status_cd = 'U'
cash_receipt.deposit_date <= :cutoffDate

Computed Values:

created_by_name: COALESCE(first_name || ' ' || last_name, created_by) from created_by_user alias
updated_by_name: COALESCE(first_name || ' ' || last_name, updated_by) from updated_by_user alias
locked_by_user_name: COALESCE(first_name || ' ' || last_name, CAST(locked_by_user_id AS TEXT)) from locked_by_user alias

Returns: Array of cash receipt rows with bank account name, uta_account_id, resolved user names, and current worksheet status, used by the GL posting batch job.

2.1.10 Get Worksheets for Receipts (Bulk)

Operation: getWorksheetsForReceipts

Input Parameters:

cashReceiptIds: Integer array (required) — cash_receipt_split.cash_receipt_id

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`cash_receipt_worksheet`	INNER	`cash_receipt_split`	`cash_receipt_worksheet.cash_receipt_split_id = cash_receipt_split.cash_receipt_split_id`	Link worksheet back to receipt ID

Filters:

cash_receipt_split.cash_receipt_id IN (:cashReceiptIds)
cash_receipt_worksheet.current_item_ind = true
Results ordered by cash_receipt_split.cash_receipt_id, cash_receipt_worksheet.worksheet_sequence

Computed Values:

Post-query grouping by cash_receipt_id
worksheet_count: count of worksheets per receipt
total_claimed: SUM(split_amt) across worksheets per receipt

Returns: Map of cash_receipt_id to { worksheets[], total_claimed, worksheet_count }, used by the cash receipt list page to populate expandable worksheet rows without N+1 queries.

2.2 Cash Receipt Split Queries

2.2.1 Get Split by ID

Operation: getSplitById

Input Parameters:

cashReceiptSplitId: Integer (required) — cash_receipt_split.cash_receipt_split_id

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`cash_receipt_split`	—	—	—	Single-table lookup; no joins required

Filters:

cash_receipt_split.cash_receipt_split_id = :cashReceiptSplitId

Computed Values:

None

Returns: Single split row or null.

2.2.2 Get Split Display by ID

Operation: getSplitDisplayById

Input Parameters:

cashReceiptSplitId: Integer (required) — cash_receipt_split.cash_receipt_split_id

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`cash_receipt_split`	LEFT	`cash_receipt_worksheet`	`cash_receipt_split.cash_receipt_split_id = cash_receipt_worksheet.cash_receipt_split_id`	Current worksheet status for the split
`cash_receipt_split`	LEFT	`cash_receipt`	`cash_receipt_split.cash_receipt_id = cash_receipt.cash_receipt_id`	Parent receipt amount and reference

Filters:

cash_receipt_split.cash_receipt_split_id = :cashReceiptSplitId

Computed Values:

available_balance: set to null at query level; calculated by the service layer (see Section 3)

Returns: Single split display row or null.

NOTE

receipt_amt in the display result is sourced from cash_receipt.net_receipt_amt, not cash_receipt.receipt_amt.

2.2.3 Get Splits by Receipt ID

Operation: getSplitsByReceiptId

Input Parameters:

cashReceiptId: Integer (required) — cash_receipt_split.cash_receipt_id

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`cash_receipt_split`	LEFT	`cash_receipt_worksheet`	`cash_receipt_split.cash_receipt_split_id = cash_receipt_worksheet.cash_receipt_split_id`	Current worksheet status for each split
`cash_receipt_split`	LEFT	`cash_receipt`	`cash_receipt_split.cash_receipt_id = cash_receipt.cash_receipt_id`	Parent receipt amount and reference

Filters:

cash_receipt_split.cash_receipt_id = :cashReceiptId
Results ordered by cash_receipt_split.split_sequence ASC

Computed Values:

available_balance: calculated by service layer after fetch (see Section 3)

Returns: Array of split display rows ordered by split_sequence.

2.2.4 Get Splits with Worksheets by Receipt ID

Operation: getSplitsWithWorksheetsByReceiptId

Input Parameters:

cashReceiptId: Integer (required) — cash_receipt_split.cash_receipt_id

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`cash_receipt_split`	LEFT	`cash_receipt_worksheet`	`cash_receipt_split.cash_receipt_split_id = cash_receipt_worksheet.cash_receipt_split_id` AND `cash_receipt_worksheet.current_item_ind = true`	Current worksheet with lock details
`cash_receipt_worksheet`	LEFT	`users`	`cash_receipt_worksheet.locked_by_user_id = users.user_id`	Resolve lock-holder display name

Filters:

cash_receipt_split.cash_receipt_id = :cashReceiptId
Worksheet join additionally filtered by cash_receipt_worksheet.current_item_ind = true
Results ordered by cash_receipt_split.split_sequence ASC

Computed Values:

locked_by_user_name: CONCAT(users.first_name, ' ', users.last_name)
applied_amt and remaining_amt: calculated post-query by the service layer

Returns: Array of split-with-worksheet rows ordered by split_sequence.

2.2.5 Get Splits with Worksheets by Receipt IDs (Bulk)

Operation: getSplitsWithWorksheetsByReceiptIds

Input Parameters:

cashReceiptIds: Integer array (required) — cash_receipt_split.cash_receipt_id

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`cash_receipt_split`	LEFT	`cash_receipt_worksheet`	`cash_receipt_split.cash_receipt_split_id = cash_receipt_worksheet.cash_receipt_split_id` AND `cash_receipt_worksheet.current_item_ind = true`	Current worksheet with lock details
`cash_receipt_worksheet`	LEFT	`users`	`cash_receipt_worksheet.locked_by_user_id = users.user_id`	Resolve lock-holder display name

Filters:

cash_receipt_split.cash_receipt_id IN (:cashReceiptIds)
cash_receipt_worksheet.current_item_ind = true
Results ordered by cash_receipt_split.cash_receipt_id, cash_receipt_split.split_sequence

Computed Values:

locked_by_user_name: CONCAT(users.first_name, ' ', users.last_name)
Post-query grouping by cash_receipt_id into a map

Returns: Map of cash_receipt_id to array of split-with-worksheet rows; every requested ID is guaranteed to have a map entry (empty array if no splits exist).

2.2.6 Get Splits by IDs (Batch)

Operation: getSplitsByIds

Input Parameters:

splitIds: Integer array (required) — cash_receipt_split.cash_receipt_split_id

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`cash_receipt_split`	—	—	—	Batch lookup by primary key; no joins required

Filters:

cash_receipt_split.cash_receipt_split_id IN (:splitIds)

Computed Values:

None

Returns: Array of split rows (order not guaranteed), used by the combine-splits flow to fetch all candidate splits in a single query.

2.2.7 Get Split Count

Operation: getSplitCount

Input Parameters:

cashReceiptId: Integer (required) — cash_receipt_split.cash_receipt_id

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`cash_receipt_split`	—	—	—	Count aggregate; no joins required

Filters:

cash_receipt_split.cash_receipt_id = :cashReceiptId

Computed Values:

COUNT(*)

Returns: Integer count of splits belonging to the receipt, used to enforce the "cannot delete last split" rule.

2.2.8 Get Next Split Sequence

Operation: getNextSplitSequence

Input Parameters:

cashReceiptId: Integer (required) — cash_receipt_split.cash_receipt_id

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`cash_receipt_split`	—	—	—	Aggregate; no joins required

Filters:

cash_receipt_split.cash_receipt_id = :cashReceiptId

Computed Values:

MAX(split_sequence) + 1; returns 1 when no splits exist

Returns: Integer sequence number to use when creating a new split for the receipt.

2.2.9 Get Total Split Amount

Operation: getTotalSplitAmount

Input Parameters:

cashReceiptId: Integer (required) — cash_receipt_split.cash_receipt_id

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`cash_receipt_split`	—	—	—	Aggregate; no joins required

Filters:

cash_receipt_split.cash_receipt_id = :cashReceiptId

Computed Values:

COALESCE(SUM(split_amt), 0)

Returns: Decimal string representing the sum of all split amounts for the receipt, used for split-total validation against cash_receipt.net_receipt_amt.

2.2.10 Get Splits for Receipts (Bulk with References)

Operation: getSplitsForReceipts

Input Parameters:

cashReceiptIds: Integer array (required) — cash_receipt_split.cash_receipt_id

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`cash_receipt_split`	LEFT	`cash_receipt_worksheet`	`cash_receipt_split.cash_receipt_split_id = cash_receipt_worksheet.cash_receipt_split_id` AND `cash_receipt_worksheet.current_item_ind = true`	Current worksheet status per split
`cash_receipt_worksheet`	LEFT	`users`	`cash_receipt_worksheet.locked_by_user_id = users.user_id`	Lock-holder display name
`cash_receipt_reference`	—	—	`cash_receipt_reference.cash_receipt_split_id IN (:all_split_ids)`	Reference tags (parallel query)
`file_upload`	—	—	`file_upload.file_upload_id IN (:all_source_file_ids)`	Source file entity types (parallel query)
`party`	—	—	`party.party_id IN (:party_id_set)`	Resolve party display names (parallel query)
`deal`	—	—	`deal.deal_id IN (:deal_id_set)`	Resolve deal display names (parallel query)

Filters:

All queries filter by the provided receipt IDs or their derived split/file ID sets
Reference display name resolution applies only to CLIENT_ID, CONTRACTED_PARTY_ID, BUYER_ID (resolved via party) and DEAL_ID (resolved via deal); other reference types return raw ref_or_id

Computed Values:

total_split_amt: SUM(split_amt) per receipt computed in memory
has_multiple_splits: split_count > 1
match_count: count of references per split
display_label per reference: party.display_name (fallback full_name, then "Party {id}") or deal.deal_name (fallback "Deal {id}")
source_entity_type: looked up from file_upload.entity_type for source-file tracking

Returns: Map of cash_receipt_id to { splits[], split_count, total_split_amt, has_multiple_splits }, used by the cash receipt list page to populate expandable split rows without N+1 queries.

2.3 Cash Receipt Adjustment Queries

2.3.1 Get Adjustment by ID

Operation: getById

Input Parameters:

cashReceiptAdjustmentId: Integer (required) — cash_receipt_adjustment.cash_receipt_adjustment_id

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`cash_receipt_adjustment`	—	—	—	Single-table lookup; no joins required

Filters:

cash_receipt_adjustment.cash_receipt_adjustment_id = :cashReceiptAdjustmentId

Computed Values:

None

Returns: Single adjustment row or null, used during deletion to validate posting status and type before allowing removal.

2.3.2 Get Adjustments by Cash Receipt ID

Operation: getByCashReceiptId

Input Parameters:

cashReceiptId: Integer (required) — cash_receipt_adjustment.cash_receipt_id

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`cash_receipt_adjustment`	—	—	—	Single-table lookup; no joins required

Filters:

cash_receipt_adjustment.cash_receipt_id = :cashReceiptId
Results ordered by created_dt ASC

Computed Values:

None

Returns: Array of adjustment rows ordered by creation date, displayed in the receipt-detail adjustment panel.

2.3.3 Sum Adjustments by Cash Receipt ID

Operation: sumByCashReceiptId

Input Parameters:

cashReceiptId: Integer (required) — cash_receipt_adjustment.cash_receipt_id

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`cash_receipt_adjustment`	—	—	—	Aggregate; no joins required

Filters:

cash_receipt_adjustment.cash_receipt_id = :cashReceiptId

Computed Values:

COALESCE(SUM(adjustment_amt), 0)

Returns: Decimal string representing the total adjustment amount, used when recalculating cash_receipt.net_receipt_amt after creating or deleting an adjustment.

2.4 Cash Receipt Reference Queries

2.4.1 Get References by Split ID

Operation: getReferencesBySplitId

Input Parameters:

cashReceiptSplitId: Integer (required) — cash_receipt_reference.cash_receipt_split_id

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`cash_receipt_reference`	—	—	—	Single-table lookup; no joins required

Filters:

cash_receipt_reference.cash_receipt_split_id = :cashReceiptSplitId

Computed Values:

None

Returns: Array of { type_cd, ref_or_id } rows, used when calculating tax warnings to find party references and in the split-detail display.

2.4.2 Get References by Split IDs (Bulk)

Operation: getReferencesBySplitIds

Input Parameters:

cashReceiptSplitIds: Integer array (required) — cash_receipt_reference.cash_receipt_split_id

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`cash_receipt_reference`	—	—	—	Batch lookup; no joins required

Filters:

cash_receipt_reference.cash_receipt_split_id IN (:cashReceiptSplitIds)

Computed Values:

None at query level; results grouped by cash_receipt_split_id in memory by the caller

Returns: Array of reference rows, grouped by the caller into a map of split_id to references, used by the Splits for Receipts bulk query to avoid N+1 queries when loading the receipt list page.

3. Calculations & Formulas

3.1 Net Receipt Amount

text

net_receipt_amt = receipt_amt - SUM(adjustment_amt)

Source: cash_receipt.receipt_amt, cash_receipt_adjustment.adjustment_amt
Precision: decimal(15,2)
Trigger: Recalculated every time an adjustment is created or deleted via sumByCashReceiptId. receipt_amt is the gross converted amount; adjustments reduce it to net_receipt_amt.
Example: $10,000.00 - $250.00 = $9,750.00

3.2 Receipt Amount from Currency Conversion

text

IF currency_cd != original_currency_cd AND fx_rate IS NOT NULL:
    receipt_amt = original_receipt_amt * fx_rate
ELSE:
    receipt_amt = original_receipt_amt

Source: cash_receipt.original_receipt_amt, cash_receipt.fx_rate, cash_receipt.currency_cd, cash_receipt.original_currency_cd
Precision: decimal(15,2) result; real fx_rate
Trigger: Applied at receipt creation and when amount or currency fields are updated, unless receipt_amt is explicitly provided.
Example: £5,000.00 * 1.27 = $6,350.00

3.3 Split Available Balance

text

available_balance = split_amt - applied_amt

Source: cash_receipt_split.split_amt; applied_amt is the sum of approved applications on the split's current worksheet (queried via the worksheet repository)
Precision: decimal(15,2)
Tolerance: 0.005 for balance comparisons
Example: $5,000.00 - $3,000.00 = $2,000.00

When no worksheet exists, available_balance = split_amt.

3.4 Worksheet Applied and Remaining Amounts

text

applied_amt   = SUM(approved application amounts) for the worksheet
remaining_amt = split_amt - applied_amt

Source: cash_receipt_application amounts summed by cash_receipt_worksheet_id; cash_receipt_split.split_amt
Precision: decimal(15,2)
Trigger: Computed by the service layer after fetching splits with worksheets, using a bulk sum query against cash_receipt_application grouped by cash_receipt_worksheet_id.

3.5 Split Total Validation

text

is_valid = ABS(net_receipt_amt - SUM(split_amt)) < 0.005

Source: cash_receipt.net_receipt_amt, cash_receipt_split.split_amt
Precision: decimal(15,2)
Tolerance: 0.005 accounts for floating-point rounding in decimal-to-float conversions
Rule: All split amounts for a receipt must sum to the receipt's net_receipt_amt.

3.6 Adjust Amount Eligibility (per Split)

text

can_adjust = TRUE  when:
    - split_status_cd != 'V'
    - AND (no worksheet exists OR worksheet_status_cd = 'D')

can_adjust = FALSE when:
    - split_status_cd = 'V'
    - OR worksheet_status_cd IN ('P', 'T', 'A', 'R', 'S')

Source: cash_receipt_split.split_status_cd, cash_receipt_worksheet.cash_receipt_worksheet_status_cd
Rule: The receipt-level can_adjust is true if any split is individually adjustable.

3.7 Unapplied Amount (per Split in Eligibility Check)

text

unapplied_amt = split_amt - applied_amt

Source: cash_receipt_split.split_amt; applied_amt from the worksheet's approved application sum
Precision: decimal(15,2)
Usage: Displayed in the adjustment eligibility UI so users see how much headroom each split has.

3.8 Receipt Amount after Split Adjustment

text

new_receipt_amt = SUM(split_amt) across all non-voided splits

Source: cash_receipt_split.split_amt for splits where split_status_cd != 'V'
Precision: decimal(15,2)
Side-effect: If the new total is zero, all splits are voided and cash_receipt.posting_status_cd is set to 'V'.

3.9 Transfer FX Conversion

text

dest_amount = transfer_amt * (dest_currency_rate / source_currency_rate)

Source: Transfer amount and currency rates relative to USD
Precision: decimal(15,2)
Trigger: When transferring cash to a different bank account with a different currency. A new receipt is created on the destination bank with the converted amount.

4. Cross-References

Tables Queried Outside This Domain

The cash-receipt queries join to tables documented in other foundation areas:

Table	Joined From	Purpose	Foundation Area
`bank_account`	`cash_receipt.bank_account_id`	Bank account name display; `uta_account_id` for GL posting	Bank Accounts
`users`	`cash_receipt.created_by`, `.updated_by`, `.locked_by_user_id`; `cash_receipt_worksheet.locked_by_user_id`	Resolve user display names	Users & Authentication
`cash_receipt_worksheet`	`cash_receipt_split.cash_receipt_split_id`	Worksheet status, lock info, application sums	Worksheets Queries
`cash_receipt_application`	`cash_receipt_worksheet.cash_receipt_worksheet_id`	Compute applied amounts; link to billing items for client search	Worksheets Queries
`billing_item_detail`	`cash_receipt_application.billing_item_detail_id`	Filter by REV type for client search	Billing Items Queries
`billing_item`	`billing_item_detail.billing_item_id`	Client, buyer, deal, entity, department for client search	Billing Items Queries
`deal`	`billing_item.deal_id`	Deal name resolution for client search and reference display	TODO: See deals-and-bookings/queries.md (not yet written)
`party`	`billing_item.buyer_id`, `.client_id`; reference resolution	Party display names	TODO: See parties/queries.md (not yet written)
`uta_entity`	`billing_item.uta_entity_id`	Entity name for client search	Organization
`department`	`billing_item.department_id`	Department name for client search	Organization
`file_upload`	`cash_receipt_split.source_file_upload_id`	Entity type for source-file tracking on splits	File Uploads
`code_master`	Service-level enrichment	Posting status descriptions, currency descriptions	Reference Data

Downstream Consumers of Cash Receipt Data

These areas query cash-receipt tables or depend on computed values from this domain:

Consumer	Usage
Worksheets	`cash_receipt_worksheet` is created per split; worksheet queries join back to `cash_receipt_split` for amount context.
GL Posting Job	Reads unposted receipts via `getUnpostedReceiptsUpToDate` to stage for General Ledger.
Bank Ingestion	`findByBankReference` is called during bank-statement import to detect existing receipts (living-mirror pattern).
Dashboard Metrics	`getUnpostedMetric` and `getCashReceivedTodayTotal` power the dashboard cards.
Write-Off Packets	Write-off approval creates a special receipt (`receipt_type_cd = 'WRITE_OFF'`) with an auto-created split.
Tax Warning Calculation	Reads `cash_receipt_reference` for a split to find party references, then calculates withholding tax warnings stored back on `cash_receipt_split.tax_warning_ind` and `tax_warning_json`.

Sample Deals

Touring Deal

Endorsement Deal

Sports Endo Deal

Cash Receipts Queries ​

1. Executive Summary ​

2. Key Queries ​

2.1 Cash Receipt Queries ​

2.1.1 Get Cash Receipt by ID ​

2.1.2 Get Cash Receipt with Parties ​

2.1.3 Search Cash Receipts ​

2.1.4 Get Cash Receipts by Client ID ​

2.1.5 Find Cash Receipt by Bank Reference ​

2.1.6 Get Unposted Receipts Metric ​

2.1.7 Get Cash Received Today Total ​

2.1.8 Get Cash Receipt Totals by Date Range ​

2.1.9 Get Unposted Receipts Up to Date ​

2.1.10 Get Worksheets for Receipts (Bulk) ​

2.2 Cash Receipt Split Queries ​

2.2.1 Get Split by ID ​

2.2.2 Get Split Display by ID ​

2.2.3 Get Splits by Receipt ID ​

2.2.4 Get Splits with Worksheets by Receipt ID ​

2.2.5 Get Splits with Worksheets by Receipt IDs (Bulk) ​

2.2.6 Get Splits by IDs (Batch) ​

2.2.7 Get Split Count ​

2.2.8 Get Next Split Sequence ​

2.2.9 Get Total Split Amount ​

2.2.10 Get Splits for Receipts (Bulk with References) ​

2.3 Cash Receipt Adjustment Queries ​

2.3.1 Get Adjustment by ID ​

2.3.2 Get Adjustments by Cash Receipt ID ​

2.3.3 Sum Adjustments by Cash Receipt ID ​

2.4 Cash Receipt Reference Queries ​

2.4.1 Get References by Split ID ​

2.4.2 Get References by Split IDs (Bulk) ​

3. Calculations & Formulas ​

3.1 Net Receipt Amount ​

3.2 Receipt Amount from Currency Conversion ​

3.3 Split Available Balance ​

3.4 Worksheet Applied and Remaining Amounts ​

3.5 Split Total Validation ​

3.6 Adjust Amount Eligibility (per Split) ​

3.7 Unapplied Amount (per Split in Eligibility Check) ​

3.8 Receipt Amount after Split Adjustment ​

3.9 Transfer FX Conversion ​

4. Cross-References ​

Tables Queried Outside This Domain ​

Downstream Consumers of Cash Receipt Data ​

Cash Receipts Queries

1. Executive Summary

2. Key Queries

2.1 Cash Receipt Queries

2.1.1 Get Cash Receipt by ID

2.1.2 Get Cash Receipt with Parties

2.1.3 Search Cash Receipts

2.1.4 Get Cash Receipts by Client ID

2.1.5 Find Cash Receipt by Bank Reference

2.1.6 Get Unposted Receipts Metric

2.1.7 Get Cash Received Today Total

2.1.8 Get Cash Receipt Totals by Date Range

2.1.9 Get Unposted Receipts Up to Date

2.1.10 Get Worksheets for Receipts (Bulk)

2.2 Cash Receipt Split Queries

2.2.1 Get Split by ID

2.2.2 Get Split Display by ID

2.2.3 Get Splits by Receipt ID

2.2.4 Get Splits with Worksheets by Receipt ID

2.2.5 Get Splits with Worksheets by Receipt IDs (Bulk)

2.2.6 Get Splits by IDs (Batch)

2.2.7 Get Split Count

2.2.8 Get Next Split Sequence

2.2.9 Get Total Split Amount

2.2.10 Get Splits for Receipts (Bulk with References)

2.3 Cash Receipt Adjustment Queries

2.3.1 Get Adjustment by ID

2.3.2 Get Adjustments by Cash Receipt ID

2.3.3 Sum Adjustments by Cash Receipt ID

2.4 Cash Receipt Reference Queries

2.4.1 Get References by Split ID

2.4.2 Get References by Split IDs (Bulk)

3. Calculations & Formulas

3.1 Net Receipt Amount

3.2 Receipt Amount from Currency Conversion

3.3 Split Available Balance

3.4 Worksheet Applied and Remaining Amounts

3.5 Split Total Validation

3.6 Adjust Amount Eligibility (per Split)

3.7 Unapplied Amount (per Split in Eligibility Check)

3.8 Receipt Amount after Split Adjustment

3.9 Transfer FX Conversion

4. Cross-References

Tables Queried Outside This Domain

Downstream Consumers of Cash Receipt Data