Worksheets Queries

1. Executive Summary

This document catalogs the data access patterns for the worksheets domain — the central feature in Client Processing. Worksheets are where incoming cash is allocated against receivables, on-account ledger entries, and outbound payments. Because worksheets sit at the intersection of cash receipts, billing items, settlements, and payments, their queries span more tables and involve more complex joins than any other domain.

The queries are organized into functional groups: worksheet retrieval and listing, application management (receivables, client ledger, payouts), balance and sum calculations, receivable search, settlement integration, status transitions, and dashboard metrics. Each query is documented with its input parameters, table joins, filters, computed values, and return shape.

Data model reference: ../data-model/worksheets.md

Tables covered: cash_receipt_worksheet, cash_receipt_application, cash_receipt_application_deduction, cash_receipt_client_ledger, cash_receipt_payout, client_ledger

2. Key Queries

2.1 Worksheet Retrieval

2.1.1 Get Worksheet by ID

Operation: getWorksheetById

Input Parameters:

worksheetId: integer (required) — cash_receipt_worksheet.cash_receipt_worksheet_id

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`cash_receipt_worksheet`	—	—	—	Direct single-row lookup by primary key

Filters:

cash_receipt_worksheet.cash_receipt_worksheet_id = :worksheetId

Computed Values:

None

Returns: One cash_receipt_worksheet row matching the given primary key, including all status, lifecycle timestamp, posting, and return-chain fields, or null if not found.

2.1.2 Get Worksheet by ID with Receipt Context

Operation: getWorksheetByIdWithReceipt

Input Parameters:

worksheetId: integer (required) — cash_receipt_worksheet.cash_receipt_worksheet_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`	Resolve split context
`cash_receipt_worksheet`	INNER	`cash_receipt` (via split)	`cash_receipt_split.cash_receipt_id = cash_receipt.cash_receipt_id`	Resolve receipt amount, currency, and metadata
`cash_receipt_worksheet`	LEFT	`bank_account`	`cash_receipt.bank_account_id = bank_account.bank_account_id`	Bank account display name
`cash_receipt_worksheet`	LEFT	`users` (as `createdByUser`)	`cash_receipt_worksheet.created_by = CAST(users.user_id AS TEXT)`	Creator display name
`cash_receipt_worksheet`	LEFT	`users` (as `updatedByUser`)	`cash_receipt_worksheet.updated_by = CAST(users.user_id AS TEXT)`	Last updater display name
`cash_receipt_worksheet`	LEFT	`users` (as `submittedByUser`)	`cash_receipt_worksheet.submitted_by = CAST(users.user_id AS TEXT)`	Legacy submit display name
`cash_receipt_worksheet`	LEFT	`users` (as `approvedByUser`)	`cash_receipt_worksheet.approved_by = CAST(users.user_id AS TEXT)`	Approver display name
`cash_receipt_worksheet`	LEFT	`users` (as `rejectedByUser`)	`cash_receipt_worksheet.rejected_by = CAST(users.user_id AS TEXT)`	Legacy reject display name
`cash_receipt_worksheet`	LEFT	`users` (as `returnedByUser`)	`cash_receipt_worksheet.returned_by = CAST(users.user_id AS TEXT)`	Return initiator display name

Filters:

cash_receipt_worksheet.cash_receipt_worksheet_id = :worksheetId

Computed Values:

effective_receipt_amt: COALESCE(cash_receipt_split.split_amt, cash_receipt.net_receipt_amt, cash_receipt.receipt_amt, '0')
total_applied_amt: sum from getTotalAppliedForWorksheet (see Section 3.1)
unapplied_amount: effective_receipt_amt - total_applied_amt
User display names: users.first_name || ' ' || users.last_name for each of the six aliased user joins

Returns: One composite object combining all cash_receipt_worksheet fields, all cash_receipt fields (cash_receipt_id, net_receipt_amt, currency_cd, cash_receipt_ref, cash_receipt_comment, receipt_type_cd, deposit_date, filename, entry_status, remittance_info), bank_account_name, total_applied_amt, unapplied_amount, and user display names for all six lifecycle roles, or null if not found.

2.1.3 Get Current Worksheet by Cash Receipt ID

Operation: getCurrentWorksheetByCashReceiptId

Input Parameters:

cashReceiptId: integer (required) — cash_receipt.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`	Traverse from worksheet to split
`cash_receipt_worksheet`	INNER	`cash_receipt` (via split)	`cash_receipt_split.cash_receipt_id = cash_receipt.cash_receipt_id`	Filter by receipt ID

Filters:

cash_receipt_split.cash_receipt_id = :cashReceiptId
cash_receipt_worksheet.current_item_ind = true

Computed Values:

unapplied_amount: effective_receipt_amt - total_applied_amt (computed via Section 3.1 and Section 3.4)

Returns: One cash_receipt_worksheet record with unapplied_amount appended, or null if no active worksheet exists for this receipt.

2.1.4 Get Worksheet by Split ID

Operation: getWorksheetBySplitId

Input Parameters:

splitId: integer (required) — cash_receipt_split.cash_receipt_split_id

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`cash_receipt_worksheet`	—	—	—	Direct lookup with status and current filters

Filters:

cash_receipt_worksheet.cash_receipt_split_id = :splitId
cash_receipt_worksheet.current_item_ind = true

Computed Values:

None

Returns: One cash_receipt_worksheet record for the active worksheet on the given split, or null if none exists.

2.2 Worksheet Listing

2.2.1 Get Worksheets by Status

Operation: getWorksheetsByStatus

Input Parameters:

statusCd: string (required) — one of D, P, T, A, R, S

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`	Resolve split amount
`cash_receipt_worksheet`	INNER	`cash_receipt` (via split)	`cash_receipt_split.cash_receipt_id = cash_receipt.cash_receipt_id`	Resolve receipt context and currency

Filters:

cash_receipt_worksheet.cash_receipt_worksheet_status_cd = :statusCd
cash_receipt_worksheet.current_item_ind = true

Computed Values:

effective_receipt_amt: COALESCE(cash_receipt_split.split_amt, cash_receipt.net_receipt_amt, cash_receipt.receipt_amt, '0')
total_applied: bulk sum via Section 3.2 across all matching worksheet IDs in one pass
unapplied_amount: effective_receipt_amt - total_applied
Sort: cash_receipt_worksheet.created_dt DESC

Returns: Array of worksheet summary objects, each containing cash_receipt_worksheet_id, cash_receipt_id, cash_receipt_split_id, cash_receipt_worksheet_status_cd, receipt_amt, unapplied_amount, currency_cd, created_dt, created_by, and worksheet_sequence.

2.2.2 Get Eligible Worksheets for Billing Items

Operation: getEligibleWorksheetsForBillingItems

Input Parameters:

currencyCd: string (optional) — cash_receipt.currency_cd

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`	Resolve split amount
`cash_receipt_worksheet`	INNER	`cash_receipt` (via split)	`cash_receipt_split.cash_receipt_id = cash_receipt.cash_receipt_id`	Currency filter and receipt context

Filters:

cash_receipt_worksheet.cash_receipt_worksheet_status_cd IN ('D', 'P', 'T')
cash_receipt_worksheet.current_item_ind = true
If currencyCd provided: cash_receipt.currency_cd = :currencyCd
Post-filter: only rows where unapplied_amount > 0.005 (epsilon threshold) are returned

Computed Values:

effective_receipt_amt: COALESCE(split_amt, net_receipt_amt, receipt_amt, '0')
total_applied: bulk sum via Section 3.2
unapplied_amount: effective_receipt_amt - total_applied

Returns: Array of worksheet summary objects with unapplied_amount, restricted to worksheets in editable statuses that have a positive remaining balance available for new applications.

2.2.3 Get Worksheets for Client

Operation: getWorksheetsForClient

Input Parameters:

clientId: integer (required) — billing_item.client_id / cash_receipt_reference.ref_or_id

This query uses two parallel discovery paths that are then deduplicated.

Path A — Split References:

Base Table	Join	Joined Table	Condition	Purpose
`cash_receipt_reference`	INNER	`cash_receipt_split`	`cash_receipt_reference.cash_receipt_split_id = cash_receipt_split.cash_receipt_split_id`	Locate split with client reference
`cash_receipt_reference`	INNER	`cash_receipt_worksheet` (via split)	`cash_receipt_split.cash_receipt_split_id = cash_receipt_worksheet.cash_receipt_split_id`	Resolve to worksheet

Filters: cash_receipt_reference.type_cd = 'CLIENT_ID' AND cash_receipt_reference.ref_or_id = :clientId AND current_item_ind = true AND status_cd IN ('D', 'P', 'T'). Returns DISTINCT cash_receipt_worksheet_id.

Path B — Billing Item Applications:

Base Table	Join	Joined Table	Condition	Purpose
`cash_receipt_worksheet`	INNER	`cash_receipt_application`	`cash_receipt_worksheet.cash_receipt_worksheet_id = cash_receipt_application.cash_receipt_worksheet_id`	Find worksheets with applications
`cash_receipt_worksheet`	INNER	`billing_item_detail` (via application)	`cash_receipt_application.billing_item_detail_id = billing_item_detail.billing_item_detail_id`	Resolve detail to item
`cash_receipt_worksheet`	INNER	`billing_item` (via detail)	`billing_item_detail.billing_item_id = billing_item.billing_item_id`	Filter by client

Filters: billing_item.client_id = :clientId AND current_item_ind = true AND status_cd IN ('D', 'P', 'T'). Returns DISTINCT cash_receipt_worksheet_id.

Filters:

Both paths execute in parallel; results are deduplicated by cash_receipt_worksheet_id
Full worksheet + split + receipt data fetched for deduplicated IDs via INNER JOINs through split to receipt

Computed Values:

effective_receipt_amt: COALESCE(split_amt, net_receipt_amt, receipt_amt, '0')
total_applied: bulk sum via Section 3.2
unapplied_amount: effective_receipt_amt - total_applied

Returns: Array of worksheet summary objects enriched with cash_receipt_ref, cash_receipt_comment, deposit_date, split_notes, total_applied_amt, and unapplied_amount.

2.2.4 Get Worksheet Reopen History

Operation: getWorksheetReopenHistory

Input Parameters:

cashReceiptId: integer (required) — cash_receipt.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`	Traverse to receipt
`cash_receipt_worksheet`	INNER	`cash_receipt` (via split)	`cash_receipt_split.cash_receipt_id = cash_receipt.cash_receipt_id`	Filter by receipt
`cash_receipt_worksheet`	LEFT	`users` (as `returnedByUser`)	`cash_receipt_worksheet.returned_by = CAST(users.user_id AS TEXT)`	Return initiator display name

Filters:

cash_receipt.cash_receipt_id = :cashReceiptId
cash_receipt_worksheet.returned_dt IS NOT NULL

Computed Values:

returned_by_user_name: users.first_name || ' ' || users.last_name
Sort: cash_receipt_worksheet.returned_dt DESC

Returns: Array of returned worksheet records, each with returned_by_user_name, receipt_amt, and currency_cd, ordered from most recent to oldest return.

2.3 Worksheet Queue

2.3.1 Get Worksheets for Processing Queue

Operation: getWorksheetsForQueue

Input Parameters:

statusFilter: string array (optional, default ['D', 'P', 'T', 'R'])

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`	Split amount
`cash_receipt_worksheet`	INNER	`cash_receipt` (via split)	`cash_receipt_split.cash_receipt_id = cash_receipt.cash_receipt_id`	Receipt context
`cash_receipt_worksheet`	LEFT	`bank_account`	`cash_receipt.bank_account_id = bank_account.bank_account_id`	Bank account name
`cash_receipt_worksheet`	LEFT	`users` (as `createdByUser`)	`cash_receipt_worksheet.created_by = CAST(users.user_id AS TEXT)`	Creator name
`cash_receipt_worksheet`	LEFT	`users` (as `returnedByUser`)	`cash_receipt_worksheet.returned_by = CAST(users.user_id AS TEXT)`	Return initiator name
`cash_receipt_worksheet`	LEFT	`users` (as `lockedByUser`)	`cash_receipt_worksheet.locked_by_user_id = users.user_id`	Lock holder name

Filters:

cash_receipt_worksheet.current_item_ind = true
cash_receipt_worksheet.cash_receipt_worksheet_status_cd IN (:statusFilter)
Sort: cash_receipt_worksheet.created_dt DESC

Computed Values:

Application Summary (bulk sub-query): joins cash_receipt_application INNER billing_item_detail, filters by cash_receipt_worksheet_id IN (:worksheet_ids), groups by cash_receipt_worksheet_id, computes SUM(CASE WHEN billing_item_detail_type_cd = 'REV' THEN cash_receipt_amt_applied ELSE 0 END) as rev_total, same for PAY as pay_total, and COUNT(DISTINCT billing_item_detail_id) as app_count
Settlement Summary (bulk sub-query): joins cash_receipt_application INNER participant_settlement INNER participant_settlement_item LEFT party, groups by cash_receipt_worksheet_id, computes per-worksheet count of unique settlements, sum of commission amounts (deduplicated by participant_settlement_item_id), party names, commission percentages, and primary settlement status
Document Counts (bulk lookup): file count per worksheet and per settlement via file storage service

Returns: Array of queue items, each containing all worksheet, receipt, split, and bank fields plus created_by_name, returned_by_name, locked_by_user_name, rev_applied_total, pay_applied_total, application_count, settlement_count, settlement_total, settlement_parties, settlement_status_cd, document_count, and settlement_document_count.

2.3.2 Get Worksheet Status Counts

Operation: getWorksheetStatusCounts

Input Parameters:

None

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`cash_receipt_worksheet`	—	—	—	Aggregate-only, no joins needed

Filters:

cash_receipt_worksheet.current_item_ind = true

Computed Values:

COUNT(*) grouped by cash_receipt_worksheet_status_cd

Returns: One row per status code with its count, covering all statuses present among current worksheets (e.g., { D: n, P: n, T: n, A: n, R: n }).

2.3.3 Get Settled Worksheets with Settlements

Operation: getSettledWorksheetsWithSettlements

Input Parameters:

None

This query executes in two steps.

Step 1 — Worksheets in Settled status:

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`	Split amount and reference
`cash_receipt_worksheet`	INNER	`cash_receipt` (via split)	`cash_receipt_split.cash_receipt_id = cash_receipt.cash_receipt_id`	Currency and deposit date

Filters: current_item_ind = true AND cash_receipt_worksheet_status_cd = 'T'.

Step 2 — Settlement details for those worksheets:

Base Table	Join	Joined Table	Condition	Purpose
`cash_receipt_application`	INNER	`participant_settlement`	`cash_receipt_application.participant_settlement_id = participant_settlement.participant_settlement_id`	Settlement header
`cash_receipt_application`	INNER	`participant_settlement_item` (via settlement)	`participant_settlement.participant_settlement_id = participant_settlement_item.participant_settlement_id`	Settlement line items
`cash_receipt_application`	LEFT	`party` (via settlement item)	`participant_settlement_item.payment_party_id = party.party_id`	Payee display name

Filters: cash_receipt_application.cash_receipt_worksheet_id IN (:worksheet_ids).

Filters:

Combined results: settlements grouped by worksheet, then by participant_settlement_id, with participant_settlement_item_id deduplicated

Computed Values:

Per settlement: total commission amount, party details (name, commission percentage, commission amount, flat indicator), settlement status
Per worksheet group: total_settlement_amount, settlement_count, document counts from file service

Returns: Array of worksheet group headers, each containing cash_receipt_worksheet_id, split_amount, cash_receipt_ref, deposit_date, currency_cd, total_settlement_amount, settlement_count, document_count, and a nested array of settlement queue items with participant_settlement_id, status_cd, settlement_amount, party_details[], and document_count.

2.4 Application Queries

2.4.1 Get Applications by Worksheet ID

Operation: getApplicationsByWorksheetId

Input Parameters:

worksheetId: integer (required) — cash_receipt_worksheet.cash_receipt_worksheet_id

This query executes in two steps, plus per-row sub-queries.

Step 1 — Identify billing items with applications on this worksheet:

Base Table	Join	Joined Table	Condition	Purpose
`cash_receipt_application`	INNER	`billing_item_detail`	`cash_receipt_application.billing_item_detail_id = billing_item_detail.billing_item_detail_id`	Resolve to billing item

Filters: cash_receipt_application.cash_receipt_worksheet_id = :worksheetId. Returns DISTINCT billing_item_id.

Step 2 — Fetch all details for those billing items, with application context:

Base Table	Join	Joined Table	Condition	Purpose
`billing_item_detail`	INNER	`billing_item`	`billing_item_detail.billing_item_id = billing_item.billing_item_id`	Item-level metadata
`billing_item_detail`	LEFT	`revenue_items` (via billing_item)	`billing_item.revenue_item_id = revenue_items.revenue_item_id`	Sales item reference and name
`billing_item_detail`	LEFT	`billing_item_detail` (as `rev_detail`)	same `billing_item_id` AND `billing_item_detail_type_cd = 'REV'`	Gross amount from REV sibling detail
`billing_item_detail`	LEFT	`cash_receipt_application`	`billing_item_detail.billing_item_detail_id = cash_receipt_application.billing_item_detail_id` AND `cash_receipt_worksheet_id = :worksheetId`	Application on this worksheet
`billing_item_detail`	LEFT	`deal` (via billing_item)	`billing_item.deal_id = deal.deal_id`	Deal reference and name
`billing_item_detail`	LEFT	`party` (as `client_party`)	`billing_item.client_id = party.party_id`	Client display name
`billing_item_detail`	LEFT	`party` (as `buyer_party`)	`billing_item.buyer_id = party.party_id`	Buyer display name
`billing_item_detail`	LEFT	`uta_entity`	`billing_item.uta_entity_id = uta_entity.uta_entity_id`	UTA entity name
`billing_item_detail`	LEFT	`department`	`billing_item.department_id = department.department_id`	Department name

Filters:

billing_item_detail.billing_item_id IN (:billing_item_ids from Step 1)

Computed Values:

is_locked (per application): EXISTS subquery traversing cash_receipt_application → participant_settlement → participant_settlement_item → cash_receipt_payout → payment_item where payment_execution_status_cd IN ('PROCESSING', 'SENT', 'PAID', 'ACKNOWLEDGED')
total_cash_applied (per detail): SUM(cash_receipt_application.cash_receipt_amt_applied) across all worksheets (no status filter; reversal entries naturally offset)
total_billed_deductions (per detail): SUM(billing_item_deduction.billing_item_deduction_amt) for this billing_item_detail_id
total_application_deductions (per detail): SUM(cash_receipt_application_deduction.deduction_amt_applied) via join through cash_receipt_application for this billing_item_detail_id
remaining_balance: billing_item_detail_total_amt - total_application_deductions - total_cash_applied (see Section 3.3)
deductions_balance: total_billed_deductions - total_application_deductions
deductions_applied (per application): SUM(cash_receipt_application_deduction.deduction_amt_applied) where cash_receipt_application_id matches

Returns: Array of objects per billing item detail — one object per detail regardless of whether an application exists on this worksheet — each containing cash_receipt_application_id (null if no application), billing_item_detail_id, billing_item_id, cash_receipt_amt_applied, billing_item_detail_type_cd, billing_item_detail_total_amt, billing_item_detail_gross_amt, billing_item_detail_percent, billing_item_detail_remaining_amt, billing_item_detail_deductions, deductions_balance, deductions_applied, is_read_only (lock status), deal_id, deal_name, deal_reference, client_id, client_name, buyer_id, buyer_name, uta_entity_id, uta_entity_name, department_id, department_name, currency_cd, write_off_status_cd, and write_off_dt.

2.4.2 Get Client Ledger Applications by Worksheet ID

Operation: getClientLedgerApplicationsByWorksheetId

Input Parameters:

worksheetId: integer (required) — cash_receipt_worksheet.cash_receipt_worksheet_id

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`cash_receipt_client_ledger`	INNER	`client_ledger`	`cash_receipt_client_ledger.client_ledger_id = client_ledger.client_ledger_id`	Ledger entry details
`cash_receipt_client_ledger`	LEFT	`party` (as `client_party`)	`client_ledger.client_id = party.party_id`	Client display name
`cash_receipt_client_ledger`	LEFT	`party` (as `buyer_party`)	`client_ledger.buyer_id = party.party_id`	Buyer display name
`cash_receipt_client_ledger`	LEFT	`deal`	`client_ledger.deal_id = deal.deal_id`	Deal name
`cash_receipt_client_ledger`	LEFT	`uta_entity`	`client_ledger.uta_entity_id = uta_entity.uta_entity_id`	UTA entity name
`cash_receipt_client_ledger`	LEFT	`department`	`client_ledger.department_id = department.department_id`	Department name

Filters:

cash_receipt_client_ledger.cash_receipt_worksheet_id = :worksheetId

Computed Values:

None

Returns: Array of objects, each containing cash_receipt_client_ledger_id, cash_receipt_worksheet_id, client_ledger_id, cash_receipt_amt_applied, client_ledger_type_cd, client_ledger_amt, client_ledger_name, client_id, client_name, buyer_id, buyer_name, deal_id, deal_name, uta_entity_id, uta_entity_name, department_id, and department_name.

2.4.3 Get Payment Payouts by Worksheet ID

Operation: getPaymentItemPayoutsByWorksheetId

Input Parameters:

worksheetId: integer (required) — cash_receipt_worksheet.cash_receipt_worksheet_id

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`cash_receipt_payout`	LEFT	`payment_item`	`cash_receipt_payout.payment_item_id = payment_item.payment_item_id`	Execution status and posting status post-approval
`cash_receipt_payout`	LEFT	`party`	`cash_receipt_payout.payout_party_id = party.party_id`	Payee display name
`cash_receipt_payout`	LEFT	`deal` (as `payout_deal`)	`cash_receipt_payout.deal_id = payout_deal.deal_id`	Deal name from payout (pre-approval)
`cash_receipt_payout`	LEFT	`deal` (as `payment_deal`)	`payment_item.deal_id = payment_deal.deal_id`	Deal name from payment item (post-approval fallback)
`cash_receipt_payout`	LEFT	`party` (as `payout_buyer`)	`cash_receipt_payout.buyer_id = payout_buyer.party_id`	Buyer from payout
`cash_receipt_payout`	LEFT	`party` (as `payment_buyer`)	`payment_item.buyer_id = payment_buyer.party_id`	Buyer from payment item (fallback)
`cash_receipt_payout`	LEFT	`uta_entity` (as `payout_entity`)	`cash_receipt_payout.uta_entity_id = payout_entity.uta_entity_id`	Entity from payout
`cash_receipt_payout`	LEFT	`uta_entity` (as `payment_entity`)	`payment_item.uta_entity_id = payment_entity.uta_entity_id`	Entity from payment item (fallback)
`cash_receipt_payout`	LEFT	`department` (as `payout_dept`)	`cash_receipt_payout.department_id = payout_dept.department_id`	Department from payout
`cash_receipt_payout`	LEFT	`department` (as `payment_dept`)	`payment_item.department_id = payment_dept.department_id`	Department from payment item (fallback)
`cash_receipt_payout`	LEFT	`bank_account`	`cash_receipt_payout.payment_party_bank_id = bank_account.bank_account_id`	Destination bank account name

Filters:

cash_receipt_payout.cash_receipt_worksheet_id = :worksheetId
Sort: cash_receipt_payout.cash_receipt_payout_id ASC

Computed Values:

deal_name: COALESCE(payout_deal.deal_name, payment_deal.deal_name) — payout field is primary (pre-approval), payment_item field is fallback (post-approval)
buyer_name, uta_entity_name, department_name: same COALESCE(payout_source, payment_source) pattern
is_read_only: true when payment_item.payment_execution_status_cd IN ('PROCESSING', 'SENT', 'PAID', 'ACKNOWLEDGED')
do_not_send_ind: payment_item.do_not_send_ind if payment exists, else cash_receipt_payout.do_not_send_ind

Returns: Array of payout objects containing cash_receipt_payout_id, payout_party_id, payout_party_name, payment_item_id, participant_settlement_item_id, payment_item_name, payment_item_type_cd, payment_item_amt, payment_item_currency_cd, payment_date, do_not_send_ind, deal_id, deal_name, buyer_id, buyer_name, uta_entity_id, uta_entity_name, department_id, department_name, bank_account_name, payment_execution_status_cd, reversal_reason_cd, reversal_of_payout_id, is_read_only, client_currency_conversion_ind, target_currency_cd, and a nested payment_item object with payment_item_posting_status_cd, payment_clearing_status_ind, payment_item_type_cd, and do_not_send_ind.

2.5 Receivable Search

2.5.1 Search Receivables

Operation: searchReceivables

Input Parameters:

searchTerm: string (optional) — matched via ILIKE against billing item name, party display names, deal reference
billingItemDetailTypeCd: string (optional) — billing_item_detail.billing_item_detail_type_cd
currencyCd: string (optional) — billing_item.billing_item_currency_cd
clientId: integer (optional) — billing_item.client_id
buyerId: integer (optional) — billing_item.buyer_id
dealId: integer (optional) — billing_item.deal_id
openItemOnly: boolean (optional) — billing_item.open_item_ind
showOnlyWithBalance: boolean (optional) — post-filter on computed remaining_balance
limit: integer (optional, default 50)

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`billing_item_detail`	INNER	`billing_item`	`billing_item_detail.billing_item_id = billing_item.billing_item_id`	Item-level fields and filters
`billing_item_detail`	LEFT	`deal`	`billing_item.deal_id = deal.deal_id`	Deal reference for search and display
`billing_item_detail`	LEFT	`party` (as `client_party`)	`billing_item.client_id = party.party_id`	Client name for search and display
`billing_item_detail`	LEFT	`party` (as `buyer_party`)	`billing_item.buyer_id = party.party_id`	Buyer name for search and display

Filters:

Always: billing_item.current_item_ind = true
If searchTerm: billing_item.billing_item_name ILIKE :searchTerm OR client_party.display_name ILIKE :searchTerm OR buyer_party.display_name ILIKE :searchTerm OR deal.deal_reference ILIKE :searchTerm
Each additional optional parameter adds an AND condition on its corresponding field
Post-filter if showOnlyWithBalance = true: excludes rows where remaining_balance <= 0

Computed Values:

total_cash_applied: SUM(cash_receipt_application.cash_receipt_amt_applied) across all worksheets for this billing_item_detail_id
total_billed_deductions: SUM(billing_item_deduction.billing_item_deduction_amt) for this billing_item_detail_id
total_application_deductions: SUM(cash_receipt_application_deduction.deduction_amt_applied) via join through cash_receipt_application
remaining_balance: billing_item_detail_total_amt - total_billed_deductions - total_application_deductions - total_cash_applied (see Section 3.3)

Returns: Array of objects containing billing_item_detail_id, billing_item_id, billing_item_name, billing_item_detail_type_cd, billing_item_detail_total_amt, billing_item_detail_gross_amt, billing_item_detail_remaining_amt, billing_item_currency_cd, billing_item_due_dt, open_item_ind, deal_id, deal_reference, deal_name, client_id, client_name, buyer_id, and buyer_name.

2.5.2 Get Billing Item Detail with Balance

Operation: getBillingItemDetailWithBalance

Input Parameters:

billingItemDetailId: integer (required) — billing_item_detail.billing_item_detail_id

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`billing_item_detail`	INNER	`billing_item`	`billing_item_detail.billing_item_id = billing_item.billing_item_id`	Item name and currency

Filters:

billing_item_detail.billing_item_detail_id = :billingItemDetailId

Computed Values:

total_applied: SUM(cash_receipt_application.cash_receipt_amt_applied) restricted to applications on Approved worksheets (cash_receipt_worksheet_status_cd = 'A') OR applications that are individually locked (EXISTS subquery on the settlement → payment item chain where payment_execution_status_cd IN ('PROCESSING', 'SENT', 'PAID', 'ACKNOWLEDGED'))
total_billed_deductions: SUM(billing_item_deduction.billing_item_deduction_amt) for this billing_item_detail_id
total_application_deductions: SUM(cash_receipt_application_deduction.deduction_amt_applied) via join through cash_receipt_application
remaining_balance: billing_item_detail_total_amt - total_billed_deductions - total_application_deductions - total_applied

Returns: One object containing billing_item_detail_id, billing_item_id, billing_item_detail_type_cd, billing_item_detail_total_amt, billing_item_detail_remaining_amt, billing_item_name, and billing_item_currency_cd.

2.5.3 Search Client Ledger Items

Operation: searchClientLedgerItems

Input Parameters:

clientName: string (optional) — ILIKE match on party.display_name
clientId: integer (optional) — client_ledger.client_id
clientIds: integer array (optional) — client_ledger.client_id IN list
dealId: integer (optional) — client_ledger.deal_id
clientLedgerTypeCd: string (optional) — client_ledger.client_ledger_type_cd
clientLedgerStatusCd: string (optional) — client_ledger.client_ledger_status_cd
currencyCd: string (optional) — client_ledger.client_ledger_currency_cd
openItemsOnly: boolean (optional, default true) — client_ledger.client_ledger_open_item_ind
minAmount / maxAmount: decimal (optional) — post-filter on remaining_amt

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`client_ledger`	LEFT	`party` (as `client_party`)	`client_ledger.client_id = party.party_id`	Client display name
`client_ledger`	LEFT	`party` (as `buyer_party`)	`client_ledger.buyer_id = party.party_id`	Buyer display name
`client_ledger`	LEFT	`deal`	`client_ledger.deal_id = deal.deal_id`	Deal name
`client_ledger`	LEFT	`uta_entity`	`client_ledger.uta_entity_id = uta_entity.uta_entity_id`	UTA entity name
`client_ledger`	LEFT	`department`	`client_ledger.department_id = department.department_id`	Department name

Filters:

Each optional parameter adds an AND condition on its corresponding field
If openItemsOnly: client_ledger.client_ledger_open_item_ind = true
Post-filter: if openItemsOnly, rows where remaining_amt <= 0 are excluded; minAmount / maxAmount applied in application code against remaining_amt
Sort: client_ledger.created_dt ASC

Computed Values:

applied_amt (inline subquery): SUM(cash_receipt_client_ledger.cash_receipt_amt_applied) FROM cash_receipt_client_ledger INNER JOIN cash_receipt_worksheet WHERE client_ledger_id matches AND cash_receipt_worksheet.current_item_ind = true AND cash_receipt_worksheet_status_cd = 'A'
remaining_amt: COALESCE(client_ledger_amt, 0) - applied_amt (see Section 3.5)

Returns: Array of objects containing all client_ledger fields plus client_name, buyer_name, deal_name, uta_entity_name, department_name, applied_amt, and remaining_amt.

2.6 Balance Aggregations

2.6.1 Get Total Applied for Worksheet (Single)

Operation: getTotalAppliedForWorksheet

Input Parameters:

worksheetId: integer (required) — cash_receipt_worksheet.cash_receipt_worksheet_id

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`cash_receipt_application`	—	—	—	First of four UNION ALL sub-selects
`cash_receipt_client_ledger`	—	—	—	Second sub-select
`cash_receipt_payout`	—	—	—	Third sub-select (non-settlement payouts only)
`cash_receipt_application_deduction`	INNER	`cash_receipt_application`	`cash_receipt_application_deduction.cash_receipt_application_id = cash_receipt_application.cash_receipt_application_id`	Fourth sub-select (deductions via application)

Filters:

All sub-selects filter on cash_receipt_worksheet_id = :worksheetId
Third sub-select additionally filters: payment_item_type_cd != 'S' (settlement payouts excluded to prevent double-counting)

Computed Values:

grand_total: COALESCE(SUM(total), 0) over the four UNION ALL branches (see Section 3.1 for the complete formula)

Returns: One string value representing the grand total of all cash applied on the worksheet across receivable applications, client ledger applications, non-settlement payouts, and application-level deductions.

2.6.2 Get Total Applied for Worksheets (Bulk)

Operation: getApprovedApplicationsSumBulk

Input Parameters:

worksheetIds: integer array (required) — cash_receipt_worksheet.cash_receipt_worksheet_id list

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`cash_receipt_application`	—	—	—	First of four parallel queries
`cash_receipt_client_ledger`	—	—	—	Second parallel query
`cash_receipt_payout`	—	—	—	Third parallel query (non-settlement payouts)
`cash_receipt_application_deduction`	INNER	`cash_receipt_application`	`cash_receipt_application_deduction.cash_receipt_application_id = cash_receipt_application.cash_receipt_application_id`	Fourth parallel query

Filters:

All four queries filter on cash_receipt_worksheet_id IN (:worksheetIds), grouped by cash_receipt_worksheet_id
Third query additionally filters: payment_item_type_cd != 'S'

Computed Values:

Per worksheet: SUM from each of the four queries combined in application code into Map<worksheetId, totalAppliedString> (see Section 3.2)

Returns: A map from worksheet_id to total applied string, with "0.00" as the default for worksheets with no applications, used for bulk unapplied amount display in list views.

2.7 Status Transition Validation

2.7.1 Validate Unsettled PAY Applications

Operation: getUnsettledPayApplications

Input Parameters:

worksheetId: integer (required) — cash_receipt_worksheet.cash_receipt_worksheet_id

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`cash_receipt_application`	—	—	—	Direct scan of applications

Filters:

cash_receipt_application.cash_receipt_worksheet_id = :worksheetId
billing_item_detail_id IS NOT NULL (exclude settlement-only records)
participant_settlement_id IS NULL (identify unsettled PAY applications)

Computed Values:

None

Returns: Array of application records that are PAY-type with no linked settlement; an empty array indicates all PAY applications have been settled and the worksheet may transition to Settled status.

2.7.2 Validate Application Count for Status Transitions

Operation: getApplicationCountForWorksheet

Input Parameters:

worksheetId: integer (required) — cash_receipt_worksheet.cash_receipt_worksheet_id

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`cash_receipt_application`	—	—	—	First of three parallel COUNT queries
`cash_receipt_client_ledger`	—	—	—	Second parallel COUNT query
`cash_receipt_payout`	—	—	—	Third parallel COUNT query

Filters:

All three queries filter on cash_receipt_worksheet_id = :worksheetId

Computed Values:

total_count: sum of all three COUNT(*) results

Returns: One integer representing the total number of application records across all three child tables; a value of zero blocks Apply and Approve transitions.

2.8 Reopen Workflow Queries

2.8.1 Load Worksheet Data for Reopen

Operation: loadWorksheetDataForReopen

Input Parameters:

worksheetId: integer (required) — cash_receipt_worksheet.cash_receipt_worksheet_id

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
(see 2.4.1)	—	—	—	`getApplicationsByWorksheetId`
(see 2.4.2)	—	—	—	`getClientLedgerApplicationsByWorksheetId`
(see 2.4.3)	—	—	—	`getPaymentItemPayoutsByWorksheetId`

Filters:

All three queries filter on cash_receipt_worksheet_id = :worksheetId

Computed Values:

None (delegated to sub-queries)

Returns: One composite object { billing_applications[], client_ledger_applications[], payment_payouts[] } containing all child records needed to execute the three-document return process.

2.8.2 Classify Items for Reopen (Lock Detection)

Operation: classifyItemsForReopen

Input Parameters:

billingApplications: array (from Section 2.8.1)
paymentPayouts: array (from Section 2.8.1)

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`participant_settlement_item`	—	—	`participant_settlement_id = :settlementId`	Settlement line items per settlement
`participant_settlement_item`	(separate)	`payment_item`	`participant_settlement_item_id = :itemId` AND `payment_item_posting_status_cd != 'X'`	Payment items, excluding voided

Filters:

Voided payment items (payment_item_posting_status_cd = 'X') are excluded from lock evaluation
Lock rule: payment_item.payment_execution_status_cd IN ('PROCESSING', 'SENT', 'PAID', 'ACKNOWLEDGED')

Computed Values:

When ANY settlement item in a settlement is locked, the entire settlement locks — including all associated PAY applications and their bilaterally paired REV applications
positional_rev_settlement_map: maps REV application IDs to their paired settlement for bilateral lock propagation

Returns: Sets of locked IDs: locked_payment_item_ids, locked_settlement_item_ids, locked_application_ids, locked_payout_ids, locked_settlement_ids, and positional_rev_settlement_map.

2.9 Tax and Dashboard Queries

2.9.1 Get Tax Previews for Worksheet

Operation: getTaxPreviewsForWorksheet

Input Parameters:

worksheetId: integer (required) — cash_receipt_worksheet.cash_receipt_worksheet_id
billingItemIds: integer array (required) — billing_item.billing_item_id list

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`billing_item`	—	—	`billing_item_id IN (:billingItemIds)`	Item metadata for tax context
`billing_item_detail`	(separate)	—	`billing_item_id IN (:billingItemIds)` AND `billing_item_detail_type_cd = 'REV'`	Gross amount and commission rate
`cash_receipt_worksheet`	INNER (separate)	`cash_receipt_split`	on `cash_receipt_split_id`	Split amount for context
`party_tax_info`	(separate)	—	by `party_id` from `billing_item.client_id` or `billing_item.contracted_party_id`	Party tax profile
`tax_rate_config`	(separate)	—	by jurisdiction, filtered to active date range	Active withholding rates

Filters:

billing_item.billing_item_id IN (:billingItemIds)
party_tax_info filtered by party and active date ranges
tax_rate_config filtered by jurisdiction and active date range

Computed Values:

gross_amt: from REV detail's billing_item_detail_gross_amt
commission_rate: from REV detail's billing_item_detail_percent
split_amt: from the worksheet's parent cash_receipt_split.split_amt
Tax calculation (withholding amount, jurisdiction, rate, form requirements) performed by the service layer using party tax profile, entity determination, and jurisdiction rules

Returns: A map from billing_item_id to TaxDeductionResult (jurisdiction, rate, deduction amount, tax form requirements).

2.9.2 Get Unapproved Current Worksheets Metric

Operation: getUnapprovedWorksheetsMetric

Input Parameters:

None

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`	Traverse to receipt
`cash_receipt_worksheet`	INNER	`cash_receipt` (via split)	`cash_receipt_split.cash_receipt_id = cash_receipt.cash_receipt_id`	Receipt amount for total

Filters:

cash_receipt_worksheet.current_item_ind = true
cash_receipt_worksheet.cash_receipt_worksheet_status_cd != 'A'

Computed Values:

count: COUNT(*)
total_receipt_amount: COALESCE(SUM(cash_receipt.net_receipt_amt), 0)

Returns: One object { count: number, total_receipt_amount: string } representing the volume of work pending approval.

2.9.3 Get Payouts Approved Today Total

Operation: getPayoutsApprovedTodayTotal

Input Parameters:

None

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`cash_receipt_payout`	INNER	`cash_receipt_worksheet`	`cash_receipt_payout.cash_receipt_worksheet_id = cash_receipt_worksheet.cash_receipt_worksheet_id`	Filter by worksheet approval date

Filters:

cash_receipt_worksheet.cash_receipt_worksheet_status_cd = 'A'
DATE(cash_receipt_worksheet.approved_dt) = CURRENT_DATE

Computed Values:

count: COUNT(cash_receipt_payout.cash_receipt_payout_id)
total_amount: COALESCE(SUM(cash_receipt_payout.payment_item_amt), 0)

Returns: One object { count: number, total_amount: string } representing today's approved payout volume.

2.9.4 Application Summary by REV/PAY Type (Bulk)

Operation: getApplicationSummaryBulk

Input Parameters:

worksheetIds: integer array (required) — cash_receipt_worksheet.cash_receipt_worksheet_id list

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`cash_receipt_application`	LEFT	`billing_item_detail`	`cash_receipt_application.billing_item_detail_id = billing_item_detail.billing_item_detail_id`	Resolve detail type for REV/PAY split

Filters:

cash_receipt_application.cash_receipt_worksheet_id IN (:worksheetIds)
cash_receipt_application.billing_item_detail_id IS NOT NULL

Computed Values:

rev_total: SUM(CASE WHEN billing_item_detail_type_cd = 'REV' THEN cash_receipt_amt_applied ELSE 0 END) grouped by cash_receipt_worksheet_id
pay_total: SUM(CASE WHEN billing_item_detail_type_cd = 'PAY' THEN cash_receipt_amt_applied ELSE 0 END) grouped by cash_receipt_worksheet_id
app_count: COUNT(DISTINCT billing_item_detail_id) grouped by cash_receipt_worksheet_id

Returns: A map from worksheet_id to { rev_total, pay_total, app_count } used for the REV/PAY split display in the processing queue.

3. Calculations & Formulas

3.1 Total Applied for Worksheet (Single)

text

total_applied =
    SUM(cash_receipt_application.cash_receipt_amt_applied
        WHERE cash_receipt_worksheet_id = :id)
  + SUM(cash_receipt_client_ledger.cash_receipt_amt_applied
        WHERE cash_receipt_worksheet_id = :id)
  + SUM(cash_receipt_payout.payment_item_amt
        WHERE cash_receipt_worksheet_id = :id
          AND payment_item_type_cd != 'S')
  + SUM(cash_receipt_application_deduction.deduction_amt_applied
        via cash_receipt_application
        WHERE cash_receipt_worksheet_id = :id)

Source: cash_receipt_application.cash_receipt_amt_applied, cash_receipt_client_ledger.cash_receipt_amt_applied, cash_receipt_payout.payment_item_amt, cash_receipt_application_deduction.deduction_amt_applied
Precision: decimal(15,2), result formatted to 2 decimal places
Key rule: Settlement payouts (payment_item_type_cd = 'S') are excluded; their amounts are already captured in the PAY applications they derive from. Including them would double-count.
Key rule: Application deductions are included because they represent cash consumed by withholding or bank charges and reduce the available balance.
Example: $10,000.00 receivable application + $500.00 ledger application + $200.00 passthrough payout + $150.00 withholding deduction = $10,850.00 total applied

3.2 Total Applied for Worksheets (Bulk)

text

For each worksheet in :worksheetIds:
    total_applied[id] =
        app_sum[id]        (from cash_receipt_application grouped by worksheet_id)
      + ledger_sum[id]     (from cash_receipt_client_ledger grouped by worksheet_id)
      + payout_sum[id]     (from cash_receipt_payout WHERE type != 'S', grouped by worksheet_id)
      + deduction_sum[id]  (from cash_receipt_application_deduction via application, grouped by worksheet_id)

Source: same four tables as Section 3.1, executed as four parallel grouped queries instead of one UNION ALL
Precision: decimal(15,2), combined in application code into Map<worksheetId, string>
Default: worksheets with no applications default to "0.00"
Example: For 50 worksheets in a queue, four queries execute in parallel and are merged in O(n) application code instead of 50 individual UNION ALL queries.

3.3 Billing Item Detail Remaining Balance

text

remaining_balance = billing_item_detail_total_amt
                  - total_billed_deductions
                  - total_application_deductions
                  - total_cash_applied

Where:

total_cash_applied = SUM(cash_receipt_application.cash_receipt_amt_applied) for all applications on this billing_item_detail_id across all worksheets (no status filter; reversal entries naturally offset)
total_billed_deductions = SUM(billing_item_deduction.billing_item_deduction_amt) for this billing_item_detail_id
total_application_deductions = SUM(cash_receipt_application_deduction.deduction_amt_applied) for all applications on this billing_item_detail_id (via join through cash_receipt_application)
Source: billing_item_detail.billing_item_detail_total_amt, billing_item_deduction.billing_item_deduction_amt, cash_receipt_application_deduction.deduction_amt_applied, cash_receipt_application.cash_receipt_amt_applied
Precision: decimal(15,2)
Tolerance: 0.005 for eligibility comparisons (see Section 3.7)
Variant — Approved/Locked only (used in Section 2.5.2): When checking available balance before adding a new application, total_cash_applied is restricted to applications on Approved worksheets (cash_receipt_worksheet_status_cd = 'A') OR applications that are individually locked via EXISTS subquery on the settlement → payment item chain.
Example: Detail total $5,000.00 - billed deductions $0.00 - application deductions $150.00 - cash applied $3,000.00 = $1,850.00 remaining

3.4 Unapplied Worksheet Amount

text

effective_receipt_amt = COALESCE(
    cash_receipt_split.split_amt,
    cash_receipt.net_receipt_amt,
    cash_receipt.receipt_amt,
    '0'
)

unapplied_amount = effective_receipt_amt - total_applied

Source: cash_receipt_split.split_amt, cash_receipt.net_receipt_amt, cash_receipt.receipt_amt, total_applied from Section 3.1 or 3.2
Precision: decimal(15,2)
Key rule: Split amount is the primary source; worksheets always belong to a split. Net receipt amount and gross receipt amount are fallbacks for legacy worksheets that predate the split model.
Key rule: Unapplied amount can be negative if applications exceed the split amount.
Example: Split amount $10,000.00 - total applied $7,500.00 = $2,500.00 unapplied

3.5 Client Ledger Remaining Balance

text

applied_amt = SUM(cash_receipt_client_ledger.cash_receipt_amt_applied)
              FROM cash_receipt_client_ledger
              INNER JOIN cash_receipt_worksheet
                  ON cash_receipt_client_ledger.cash_receipt_worksheet_id
                   = cash_receipt_worksheet.cash_receipt_worksheet_id
              WHERE cash_receipt_client_ledger.client_ledger_id = :client_ledger_id
                AND cash_receipt_worksheet.current_item_ind = true
                AND cash_receipt_worksheet.cash_receipt_worksheet_status_cd = 'A'

remaining_amt = COALESCE(client_ledger_amt, 0) - COALESCE(applied_amt, 0)

Source: client_ledger.client_ledger_amt, cash_receipt_client_ledger.cash_receipt_amt_applied
Precision: decimal(20,2) for client_ledger_amt; decimal(15,2) for applications
Key rule: Only Approved (A) worksheet applications count toward the applied amount. Draft and other in-progress applications are excluded to show the true confirmed balance.
Example: Ledger amount $20,000.00 - approved applications $5,000.00 = $15,000.00 remaining

3.6 Fully Paid Billing Item Check

text

total_amount     = rev_billing_item_detail_total_amt + pay_billing_item_detail_total_amt
total_applied    = rev_cash_applied + pay_cash_applied (across all approved worksheets)
balance          = total_amount - total_applied

if abs(balance) < 0.01:
    billing_item.open_item_ind = false

Source: billing_item_detail.billing_item_detail_total_amt (for both REV and PAY details), cash_receipt_application.cash_receipt_amt_applied (approved/locked only)
Precision: decimal(15,2)
Tolerance: 0.01 for fully-paid determination (see Section 3.7)
Trigger: Evaluated after worksheet approval for each billing item referenced by applications on the worksheet.
Example: REV total $2,000.00 + PAY total $8,000.00 = $10,000.00 total; if applied equals $10,000.00 (within $0.01), open_item_ind is set to false

3.7 Epsilon Comparison

Context	Epsilon	Usage
Worksheet balance eligibility	`0.005`	Filter out worksheets with negligible unapplied balance in Section 2.2.2
Billing item fully paid	`0.01`	Determine whether to close a billing item as fully collected (Section 3.6)
Settlement total vs PAY applied	`0.01`	Validate that settlement commission amounts equal PAY applied before Settle transition

3.8 Deduction Proration

When a billing item detail has multiple deduction types and a single deduction amount is applied, the amount is prorated across deduction types based on their remaining balance proportion.

text

For each deduction type on billing_item_deduction:
    billed_amt    = billing_item_deduction.billing_item_deduction_amt
    applied_amt   = SUM(cash_receipt_application_deduction.deduction_amt_applied)
                    for this type across all applications for this billing_item_detail_id
    balance       = billed_amt - applied_amt

total_balance = SUM(balance) for all types where balance > 0

For each type with balance > 0:
    proportion      = balance / total_balance
    prorated_amount = deduction_amount * proportion

Source: billing_item_deduction.billing_item_deduction_amt, cash_receipt_application_deduction.deduction_amt_applied
Precision: decimal(15,2), result per type rounded to 2 decimal places
Example: Two types — Type A billed $300, applied $100, balance $200; Type B billed $200, applied $0, balance $200. Total balance $400. New deduction $100: Type A gets $50 (50%), Type B gets $50 (50%).

4. Cross-References

Document	Relationship
Worksheets Data Model	Authoritative table and field definitions for all tables queried in this document. All column names and status codes referenced here originate in that document.
Cash Receipts Data Model	Worksheets are created from `cash_receipt_split`. Queries in Sections 2.1–2.3 join through `cash_receipt_split` to `cash_receipt` for amount, currency, bank, and deposit date. Receipt `posting_status_cd` gates worksheet creation.
Billing Items Data Model	Target of cash application. Section 2.4.1 joins `billing_item_detail` and `billing_item` for receivable context. Section 2.5.1 searches across billing items. Section 3.3 calculates billing item detail remaining balance using cross-worksheet application sums. Section 3.6 closes billing items after full payment at approval.
Settlements Data Model	Downstream of PAY applications. Sections 2.3.1, 2.3.3, and 2.9.4 aggregate settlement data per worksheet. Section 2.8.2 traverses the settlement chain for lock detection. Section 2.7.1 validates settlement completeness for the Settle transition. Settlement payouts drive payout creation in Section 2.4.3.
Settlements Queries — Payment Item Operations	Created at approval from `cash_receipt_payout`. Section 2.4.3 joins `payment_item` for execution status and posting status. Section 2.8.2 checks `payment_execution_status_cd` to determine lock status. Payment item execution status is the single most important cross-domain dependency in the return/reopen workflow.
Tax Withholding Data Model	Section 2.9.1 retrieves tax contexts from `party_tax_info` and `tax_rate_config` for worksheet tax previews. Application deductions in Section 3.1 include tax withholding amounts in the total applied calculation.
Write-Offs Data Model	Section 2.4.1 includes `write_off_status_cd` and `write_off_dt` on receivable application rows for display. The reopen preconditions block return of worksheets whose parent receipt has `receipt_type_cd = 'WRITE_OFF'`.

Sample Deals

Touring Deal

Endorsement Deal

Sports Endo Deal

Worksheets Queries ​

1. Executive Summary ​

2. Key Queries ​

2.1 Worksheet Retrieval ​

2.1.1 Get Worksheet by ID ​

2.1.2 Get Worksheet by ID with Receipt Context ​

2.1.3 Get Current Worksheet by Cash Receipt ID ​

2.1.4 Get Worksheet by Split ID ​

2.2 Worksheet Listing ​

2.2.1 Get Worksheets by Status ​

2.2.2 Get Eligible Worksheets for Billing Items ​

2.2.3 Get Worksheets for Client ​

2.2.4 Get Worksheet Reopen History ​

2.3 Worksheet Queue ​

2.3.1 Get Worksheets for Processing Queue ​

2.3.2 Get Worksheet Status Counts ​

2.3.3 Get Settled Worksheets with Settlements ​

2.4 Application Queries ​

2.4.1 Get Applications by Worksheet ID ​

2.4.2 Get Client Ledger Applications by Worksheet ID ​

2.4.3 Get Payment Payouts by Worksheet ID ​

2.5 Receivable Search ​

2.5.1 Search Receivables ​

2.5.2 Get Billing Item Detail with Balance ​

2.5.3 Search Client Ledger Items ​

2.6 Balance Aggregations ​

2.6.1 Get Total Applied for Worksheet (Single) ​

2.6.2 Get Total Applied for Worksheets (Bulk) ​

2.7 Status Transition Validation ​

2.7.1 Validate Unsettled PAY Applications ​

2.7.2 Validate Application Count for Status Transitions ​

2.8 Reopen Workflow Queries ​

2.8.1 Load Worksheet Data for Reopen ​

2.8.2 Classify Items for Reopen (Lock Detection) ​

2.9 Tax and Dashboard Queries ​

2.9.1 Get Tax Previews for Worksheet ​

2.9.2 Get Unapproved Current Worksheets Metric ​

2.9.3 Get Payouts Approved Today Total ​

2.9.4 Application Summary by REV/PAY Type (Bulk) ​

3. Calculations & Formulas ​

3.1 Total Applied for Worksheet (Single) ​

3.2 Total Applied for Worksheets (Bulk) ​

3.3 Billing Item Detail Remaining Balance ​

3.4 Unapplied Worksheet Amount ​

3.5 Client Ledger Remaining Balance ​

3.6 Fully Paid Billing Item Check ​

3.7 Epsilon Comparison ​

3.8 Deduction Proration ​

4. Cross-References ​

Worksheets Queries

1. Executive Summary

2. Key Queries

2.1 Worksheet Retrieval

2.1.1 Get Worksheet by ID

2.1.2 Get Worksheet by ID with Receipt Context

2.1.3 Get Current Worksheet by Cash Receipt ID

2.1.4 Get Worksheet by Split ID

2.2 Worksheet Listing

2.2.1 Get Worksheets by Status

2.2.2 Get Eligible Worksheets for Billing Items

2.2.3 Get Worksheets for Client

2.2.4 Get Worksheet Reopen History

2.3 Worksheet Queue

2.3.1 Get Worksheets for Processing Queue

2.3.2 Get Worksheet Status Counts

2.3.3 Get Settled Worksheets with Settlements

2.4 Application Queries

2.4.1 Get Applications by Worksheet ID

2.4.2 Get Client Ledger Applications by Worksheet ID

2.4.3 Get Payment Payouts by Worksheet ID

2.5 Receivable Search

2.5.1 Search Receivables

2.5.2 Get Billing Item Detail with Balance

2.5.3 Search Client Ledger Items

2.6 Balance Aggregations

2.6.1 Get Total Applied for Worksheet (Single)

2.6.2 Get Total Applied for Worksheets (Bulk)

2.7 Status Transition Validation

2.7.1 Validate Unsettled PAY Applications

2.7.2 Validate Application Count for Status Transitions

2.8 Reopen Workflow Queries

2.8.1 Load Worksheet Data for Reopen

2.8.2 Classify Items for Reopen (Lock Detection)

2.9 Tax and Dashboard Queries

2.9.1 Get Tax Previews for Worksheet

2.9.2 Get Unapproved Current Worksheets Metric

2.9.3 Get Payouts Approved Today Total

2.9.4 Application Summary by REV/PAY Type (Bulk)

3. Calculations & Formulas

3.1 Total Applied for Worksheet (Single)

3.2 Total Applied for Worksheets (Bulk)

3.3 Billing Item Detail Remaining Balance

3.4 Unapplied Worksheet Amount

3.5 Client Ledger Remaining Balance

3.6 Fully Paid Billing Item Check

3.7 Epsilon Comparison

3.8 Deduction Proration

4. Cross-References