Settlements Queries

1. Executive Summary

This document catalogs every query the system executes against the settlements and payments domain. The queries fall into five functional areas:

Settlement CRUD — Creating, reading, updating, and deleting settlement headers and items, including lock-awareness and enrichment with party/bank data.
Settlement Defaults — Computing the pre-populated values (deal parties, PAY totals, deduction amounts) shown when a user starts a new settlement.
Payout Lifecycle — Creating, linking, and managing cash_receipt_payout records that bridge settlements to payment items.
Payment Item Operations — Creating payment items from payouts at worksheet approval, querying them for the Payments management page, enriching them with origin and execution data, and managing posting status.
Outbound Payment Execution — Recording bank API transmission attempts, querying execution history, and retrieving failed executions for retry.

Each query is described in a technology-neutral format. Column names use snake_case matching the database schema. The document does not prescribe an ORM, query builder, or SQL dialect.

2. Key Queries

2.1 Settlement CRUD

2.1.1 Get Settlement by ID

Operation: getSettlement

Input Parameters:

settlementId: integer (required) — participant_settlement.participant_settlement_id

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`participant_settlement`	—	—	—	Fetch header row by PK.
`participant_settlement`	INNER	`participant_settlement_item`	`participant_settlement_item.participant_settlement_id = participant_settlement.participant_settlement_id`	Fetch all items for this settlement.
`participant_settlement_item`	LEFT	`payment_item`	`payment_item.participant_settlement_item_id IN (all item IDs from step above)`	Determine lock status per item.

Filters:

participant_settlement.participant_settlement_id = :settlementId — exact match on PK
payment_item.payment_item_posting_status_cd IS NULL OR payment_item.payment_item_posting_status_cd != 'X' — exclude voided payment items; voided items (posting_status_cd = 'X') were reversed in a prior return and must not count toward locking

Computed Values:

is_read_only (per item): true if the item's participant_settlement_item_id has a linked payment_item with payment_execution_status_cd IN (PROCESSING, SENT, ACKNOWLEDGED, PAID) AND payment_item_posting_status_cd != 'X'
is_read_only (header): true if ANY item has is_read_only = true (settlement-level locking rule)

Returns: One settlement header record with all its items as a nested array, each item annotated with a per-item is_read_only flag and the header annotated with a header-level is_read_only flag; returns null if no record matches :settlementId.

2.1.2 Get Settlement Enriched

Operation: getSettlementEnriched

Input Parameters:

settlementId: integer (required) — participant_settlement.participant_settlement_id

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
(result of `getSettlement`)	—	—	—	Base settlement with items and lock flags from query 2.1.1.
`participant_settlement_item`	LEFT	`party`	`party.party_id = participant_settlement_item.payment_party_id`	Payee display name per item.
`party`	LEFT	`party_role`	`party_role.party_id = party.party_id AND party_role.party_role_active_ind = true`	Active role for the payee.
`party`	LEFT	`party_bank_account`	`party_bank_account.party_id = party.party_id AND party_bank_account.active_ind = true`	Active bank link for the payee.
`party_bank_account`	LEFT	`bank_account`	`bank_account.bank_account_id = party_bank_account.bank_account_id`	Bank account name and number.

Filters:

party_role.party_role_active_ind = true — only active roles
party_bank_account.active_ind = true — only active bank associations
LIMIT 1 per item — one party record per settlement item is sufficient

Computed Values:

None beyond the is_read_only flags inherited from getSettlement (2.1.1)

Returns: Same structure as getSettlement (2.1.1), with each item additionally carrying party_name, party_role_type_cd, and bank_account_name enriched from joined party and bank tables.

2.1.3 Get Settlement by Application IDs

Operation: getSettlementByApplicationIds

Input Parameters:

applicationIds: integer[] (required) — cash_receipt_application.cash_receipt_application_id values

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`cash_receipt_application`	—	—	—	Look up `participant_settlement_id` from one of the provided application IDs.

Filters:

cash_receipt_application.cash_receipt_application_id IN (:applicationIds) — match provided applications
LIMIT 1 — all provided applications should share the same settlement; one row suffices

Computed Values:

None — if a participant_settlement_id is found, delegates to getSettlement (2.1.1) for the full result including lock status

Returns: Full settlement record in the same shape as getSettlement (2.1.1), or null if no settlement is linked to the provided application IDs.

2.1.4 Get Settlements by Worksheet ID

Operation: getSettlementsByWorksheetId

Input Parameters:

worksheetId: integer (required) — cash_receipt_application.cash_receipt_worksheet_id

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`cash_receipt_application`	INNER	`participant_settlement`	`cash_receipt_application.participant_settlement_id = participant_settlement.participant_settlement_id`	Resolve distinct settlement IDs from applications on this worksheet.

Filters:

cash_receipt_application.cash_receipt_worksheet_id = :worksheetId — scope to the target worksheet
GROUP BY participant_settlement.participant_settlement_id — deduplicate; multiple applications may share one settlement

Computed Values:

None — for each distinct participant_settlement_id found, delegates to getSettlement (2.1.1) for the full result including lock status and items

Returns: Array of settlement objects in the same shape as getSettlement (2.1.1), one per distinct settlement linked to the worksheet; returns an empty array if no settlements are found.

2.1.5 Check Locked Payments Before Settlement Deletion

Operation: checkLockedPaymentsForDeletion

Input Parameters:

settlementId: integer (required) — participant_settlement.participant_settlement_id

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`participant_settlement_item`	—	—	—	Get all items for this settlement.
`participant_settlement_item`	LEFT	`payment_item`	`payment_item.participant_settlement_item_id IN (all item IDs)`	Find linked payment items to check lock status.

Filters:

participant_settlement_item.participant_settlement_id = :settlementId — scope to target settlement
payment_item.payment_item_posting_status_cd IS NULL OR payment_item.payment_item_posting_status_cd != 'X' — exclude voided payment items

Computed Values:

has_locked_payments: true if ANY payment_item has payment_execution_status_cd IN (PROCESSING, SENT, ACKNOWLEDGED, PAID) AND payment_item_posting_status_cd != 'X'; statuses WAITING, PENDING, FAILED, CANCELLED, and null are considered unlocked

Returns: One record containing has_locked_payments (boolean), the list of settlement_item_ids belonging to the settlement, and the list of unlocked_payment_item_ids that are safe to void if deletion proceeds.

2.2 Settlement Defaults

2.2.1 Get Settlement Defaults for Selected Receivables

Operation: getSettlementDefaults

Input Parameters:

revBillingItemDetailIds: integer[] (required) — billing_item_detail.billing_item_detail_id values for the REV details selected by the user
applicationIds: integer[] (optional) — cash_receipt_application.cash_receipt_application_id values; scopes applied amounts to a specific worksheet when provided

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`billing_item_detail` (REV)	INNER	`billing_item`	`billing_item_detail.billing_item_id = billing_item.billing_item_id`	Get deal, revenue item, currency from the billing item.
`billing_item`	LEFT	`revenue_items`	`billing_item.revenue_item_id = revenue_items.revenue_item_id`	Revenue item name.
`billing_item`	LEFT	`deal`	`billing_item.deal_id = deal.deal_id`	Deal name.
`billing_item`	INNER	`billing_item_detail` (PAY)	`billing_item_detail.billing_item_id = billing_item.billing_item_id AND billing_item_detail.billing_item_detail_type_cd = 'PAY'`	Find sibling PAY details for total amounts.
`cash_receipt_application`	LEFT	`billing_item_detail`	`cash_receipt_application.billing_item_detail_id = billing_item_detail.billing_item_detail_id`	Applied amounts for REV and PAY details.
`cash_receipt_application`	LEFT	`cash_receipt_application_deduction`	`cash_receipt_application_deduction.cash_receipt_application_id = cash_receipt_application.cash_receipt_application_id`	Sum deductions split by REV and PAY.
`deal`	LEFT	`deal_party`	`deal_party.deal_id = deal.deal_id`	Deal party commission defaults.
`deal_party`	LEFT	`party`	`party.party_id = deal_party.party_id`	Party display names.
`party`	LEFT	`party_bank_account`	`party_bank_account.party_id = party.party_id AND party_bank_account.active_ind = true`	Active bank accounts per party.
`party_bank_account`	LEFT	`bank_account`	`bank_account.bank_account_id = party_bank_account.bank_account_id`	Bank account details.

Filters:

billing_item_detail.billing_item_detail_id IN (:revBillingItemDetailIds) — scope to the selected REV receivables
billing_item_detail.billing_item_detail_type_cd = 'PAY' — isolate sibling PAY details
If :applicationIds provided: cash_receipt_application.cash_receipt_application_id IN (:applicationIds) — scope amounts to the current worksheet
party_bank_account.active_ind = true — only active bank relationships
All selected REV details must share the same billing_item.revenue_item_id; if not, the query returns a validation error

Computed Values:

billing_item_detail_total_amt (PAY total): SUM(billing_item_detail.billing_item_detail_total_amt) for sibling PAY details
billing_gross_amt (REV gross): SUM(billing_item_detail.billing_item_detail_gross_amt) for selected REV details
rev_applied_amt: SUM(cash_receipt_application.cash_receipt_amt_applied) for REV applications scoped to :revBillingItemDetailIds
pay_applied_amt: SUM(cash_receipt_application.cash_receipt_amt_applied) for PAY applications scoped to sibling PAY detail IDs
rev_deduction_applied: SUM(cash_receipt_application_deduction.deduction_amt_applied) for deductions on REV applications
pay_deduction_applied: SUM(cash_receipt_application_deduction.deduction_amt_applied) for deductions on PAY applications
billing_deduction_amt: rev_deduction_applied + pay_deduction_applied

Returns: One object containing deal name, revenue item name, PAY total from billing items, REV gross from billing items, total deductions applied, PAY amount applied on worksheet, REV amount applied on worksheet, individual REV/PAY deduction amounts, PAY detail IDs for linking, currency, and a nested array of deal parties each with commission percentage, flat amount, flat indicator, and active bank account details.

2.2.2 Validate Settlement Total Against PAY Applied

Operation: validateSettlementTotal

Input Parameters:

items: settlement_item[] (required) — proposed settlement items with participant_settlement_commission_amt values
applicationIds: integer[] (required) — cash_receipt_application.cash_receipt_application_id values to scope PAY amounts

Tables & Joins:

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`	Filter to PAY-type applications only.

Filters:

cash_receipt_application.cash_receipt_application_id IN (:applicationIds) — scope to provided applications
billing_item_detail.billing_item_detail_type_cd = 'PAY' — only PAY applications

Computed Values:

settlement_total: SUM(items[].participant_settlement_commission_amt) from input data
pay_applied_amt: SUM(cash_receipt_application.cash_receipt_amt_applied) for PAY applications from the database
is_valid: ABS(settlement_total - pay_applied_amt) <= 0.01

Returns: One record containing is_valid (boolean), settlement_total (decimal(15,2)), and pay_applied_amt (decimal(15,2)).

2.3 Payout Lifecycle

2.3.1 Create Payouts for Worksheet

Operation: createPayoutsForWorksheet

Input Parameters:

worksheetId: integer (required) — cash_receipt_worksheet.cash_receipt_worksheet_id

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`cash_receipt_payout`	—	—	—	Check which settlement items already have payouts (idempotency check).
`cash_receipt_application`	—	—	—	Get deal, buyer, UTA entity, department from applications linked to each settlement.
`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.
`billing_item_detail`	INNER	`billing_item`	`billing_item_detail.billing_item_id = billing_item.billing_item_id`	Get deal, entity, and department context fields.

Filters:

cash_receipt_payout.cash_receipt_worksheet_id = :worksheetId — check existing payouts for this worksheet
Settlement item participant_settlement_item_id not already present in existing payouts — skip items that already have payouts (idempotency)
ABS(participant_settlement_item.participant_settlement_commission_amt) > 0 — skip zero-amount items

Computed Values:

cash_receipt_worksheet_id: from input parameter
payout_party_id: participant_settlement_item.payment_party_id
payment_party_bank_id: participant_settlement_item.payment_party_bank_id
deal_id, buyer_id, uta_entity_id, department_id: from billing_item via the application chain
payment_item_amt: participant_settlement_item.participant_settlement_commission_amt
payment_item_type_cd: 'S' (Settlement)
payment_item_currency_cd: 'USD' (default)
payment_date: participant_settlement_item.payment_date if set
do_not_send_ind: participant_settlement_item.do_not_send_ind

Returns: No rows returned; the side effect is that cash_receipt_payout records are inserted for each qualifying settlement item on the worksheet.

2.3.2 Get Payment Payouts for Worksheet

Operation: getPaymentPayouts

Input Parameters:

worksheetId: integer (required) — cash_receipt_worksheet.cash_receipt_worksheet_id

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`cash_receipt_payout`	—	—	—	Base table filtered by worksheet.

Filters:

cash_receipt_payout.cash_receipt_worksheet_id = :worksheetId — scope to target worksheet

Computed Values:

None

Returns: Array of payout records for the worksheet, enriched with party names, bank accounts, and deal names as resolved by the worksheet repository's payout enrichment joins.

2.3.3 Check Existing VAT Pass-Through Payout

Operation: checkExistingVatPayout

Input Parameters:

worksheetId: integer (required) — cash_receipt_worksheet.cash_receipt_worksheet_id

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`cash_receipt_payout`	—	—	—	Check for an existing VAT pass-through payout on this worksheet.

Filters:

cash_receipt_payout.cash_receipt_worksheet_id = :worksheetId — scope to target worksheet
cash_receipt_payout.payment_item_type_cd = 'V' — filter to VAT pass-through type only

Computed Values:

None

Returns: One record containing exists (boolean) and cash_receipt_payout_id (integer or null) indicating whether a VAT pass-through payout already exists for this worksheet.

2.4 Payment Item Operations

2.4.1 Create Payment Items from Payouts

Operation: createPaymentItemsFromPayouts

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	`participant_settlement_item`	`cash_receipt_payout.participant_settlement_item_id = participant_settlement_item.participant_settlement_item_id`	Get settlement item details and bank account.
`participant_settlement_item`	LEFT	`participant_settlement`	`participant_settlement_item.participant_settlement_id = participant_settlement.participant_settlement_id`	Traverse to settlement header.
`cash_receipt_payout`	LEFT	`cash_receipt_worksheet`	`cash_receipt_payout.cash_receipt_worksheet_id = cash_receipt_worksheet.cash_receipt_worksheet_id`	Get worksheet to reach the receipt.
`cash_receipt_worksheet`	LEFT	`cash_receipt_split`	`cash_receipt_worksheet.cash_receipt_split_id = cash_receipt_split.cash_receipt_split_id`	Traverse to split.
`cash_receipt_split`	LEFT	`cash_receipt`	`cash_receipt_split.cash_receipt_id = cash_receipt.cash_receipt_id`	Get UTA bank account (`bank_account_id`) for `source_account_id`.
`participant_settlement`	LEFT	`cash_receipt_application`	`participant_settlement.participant_settlement_id = cash_receipt_application.participant_settlement_id`	Traverse settlement back to applications.
`cash_receipt_application`	LEFT	`billing_item_detail`	`cash_receipt_application.billing_item_detail_id = billing_item_detail.billing_item_detail_id`	Resolve to billing item.
`billing_item_detail`	LEFT	`billing_item`	`billing_item_detail.billing_item_id = billing_item.billing_item_id`	Get deal, client, buyer, UTA entity, department.

Filters:

cash_receipt_payout.cash_receipt_worksheet_id = :worksheetId — scope to target worksheet
cash_receipt_payout.payment_item_id IS NULL — only payouts that do not yet have payment items

Computed Values:

payment_item_type_cd: from cash_receipt_payout.payment_item_type_cd (default 'P')
payment_item_name: from cash_receipt_payout.payment_item_name (default 'Commission Payment')
payment_party_id: cash_receipt_payout.payout_party_id
payment_party_bank_id: cash_receipt_payout.payment_party_bank_id; fallback to participant_settlement_item.payment_party_bank_id
participant_settlement_item_id: from cash_receipt_payout.participant_settlement_item_id
payment_item_amt: cash_receipt_payout.payment_item_amt
payment_item_currency_cd: cash_receipt_payout.payment_item_currency_cd (default 'USD')
payment_item_comment: participant_settlement_item.participant_settlement_item_comment
posting_status_cd: 'U' (Unposted)
payment_execution_status_cd: 'WAITING' if payment_date is in the future OR do_not_send_ind = true; otherwise 'PENDING'
payment_clearing_status_ind: true
deal_id: payout field first; fallback to billing_item.deal_id
client_id: billing_item.client_id; fallback to payout_party_id for passthrough payments
buyer_id: payout field first; fallback to billing_item.buyer_id
contracted_party_id: billing_item.contracted_party_id
department_id: payout field first; fallback to billing_item.department_id
uta_entity_id: payout field first; fallback to billing_item.uta_entity_id
source_account_id: cash_receipt.bank_account_id
do_not_send_ind: cash_receipt_payout.do_not_send_ind

NOTE

The settlement-to-application join can produce multiple rows per payout when one settlement links to multiple applications. Results are deduplicated by cash_receipt_payout_id before processing. After each payment_item is created, two back-link updates are issued: cash_receipt_payout.payment_item_id is set to the new ID, and participant_settlement_item.payment_item_id is set to the new ID.

Returns: No rows returned; the side effects are that payment_item records are inserted for each qualifying payout, cash_receipt_payout.payment_item_id is updated, and participant_settlement_item.payment_item_id is updated.

2.4.2 Get Payment Items with Origin Data

Operation: getPaymentItemsWithOrigin

Input Parameters:

None — returns all payment items

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`payment_item`	LEFT	`party` (as `payee_party`)	`payment_item.payment_party_id = payee_party.party_id`	Payee display name.
`payment_item`	LEFT	`bank_account`	`payment_item.payment_party_bank_id = bank_account.bank_account_id`	Payee bank details (`bank_account_name`, `bank_account_routing_no`, `bank_account_no`, `bank_id`).
`payment_item`	LEFT	`bank_account` (as `source_bank`)	`payment_item.source_account_id = source_bank.bank_account_id`	UTA source bank name.
`payment_item`	LEFT	`deal`	`payment_item.deal_id = deal.deal_id`	Deal name.
`payment_item`	LEFT	`participant_settlement_item`	`payment_item.participant_settlement_item_id = participant_settlement_item.participant_settlement_item_id`	Link to settlement item.
`participant_settlement_item`	LEFT	`participant_settlement`	`participant_settlement_item.participant_settlement_id = participant_settlement.participant_settlement_id`	Settlement ID for origin classification.

Filters:

None on the main query; supplementary batch queries apply their own filters (see Computed Values)

Computed Values:

origin_type: 'SETTLEMENT' if payment_item_type_cd = 'S' AND settlement_id IS NOT NULL; 'CLIENT_LEDGER' if 'L'; 'REFUND' if 'R'; 'OTHER' otherwise
origin_reference: descriptive string (e.g., "Settlement #42", "Client Ledger", "Refund")
origin_description: context string (e.g., "Deal: ABC Tour 2025", "Loan/Advance")
is_returned: true if payment_item.return_reason_cd IS NOT NULL OR payment_item.returned_dt IS NOT NULL
client_name: from a batch party lookup map using client_id
loanout_name: from a batch party lookup map using contracted_party_id
buyer_name: from a batch party lookup map using buyer_id
bank_name: from a batch code_master lookup (where code_master_type = 'BANK') using bank_id
cash_receipt_worksheet_id: from a batch cash_receipt_payout lookup map keyed by payment_item_id
latest_execution_id, latest_execution_status, latest_bank_reference, latest_execution_date: from a batch outbound_payment_execution lookup grouped by payment_item_id, ordered by created_dt DESC, keeping the first per group

Returns: Array of payment item rows ordered by payment_item.created_dt descending, each row enriched with payee party name, deal name, bank details, source bank name, origin classification fields, worksheet link, return status, and latest execution summary.

2.4.3 Get Single Payment Item with Origin Data

Operation: getPaymentItemWithOriginById

Input Parameters:

paymentItemId: integer (required) — payment_item.payment_item_id

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`payment_item`	LEFT	`party` (as `payee_party`)	`payment_item.payment_party_id = payee_party.party_id`	Payee display name.
`payment_item`	LEFT	`bank_account`	`payment_item.payment_party_bank_id = bank_account.bank_account_id`	Payee bank details.
`payment_item`	LEFT	`bank_account` (as `source_bank`)	`payment_item.source_account_id = source_bank.bank_account_id`	UTA source bank name.
`payment_item`	LEFT	`deal`	`payment_item.deal_id = deal.deal_id`	Deal name.
`payment_item`	LEFT	`participant_settlement_item`	`payment_item.participant_settlement_item_id = participant_settlement_item.participant_settlement_item_id`	Link to settlement item.
`participant_settlement_item`	LEFT	`participant_settlement`	`participant_settlement_item.participant_settlement_id = participant_settlement.participant_settlement_id`	Settlement ID for origin classification.

Filters:

payment_item.payment_item_id = :paymentItemId — single item lookup
LIMIT 1 — ensure single result

Computed Values:

Same origin classification and enrichment fields as getPaymentItemsWithOrigin (2.4.2), with targeted supplementary queries: party scoped to party_id IN (:clientId, :contractedPartyId, :buyerId); outbound_payment_execution scoped to payment_item_id = :paymentItemId ORDER BY created_dt DESC LIMIT 1; cash_receipt_payout scoped to payment_item_id = :paymentItemId

Returns: One row in the same shape as a single element of getPaymentItemsWithOrigin (2.4.2), or null if no record matches :paymentItemId.

2.4.4 Get Payment Items by Client ID

Operation: getPaymentItemsByClientId

Input Parameters:

clientId: integer (required) — payment_item.client_id

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`payment_item`	LEFT	`bank_account`	`payment_item.payment_party_bank_id = bank_account.bank_account_id`	Payee bank account name and number.
`payment_item`	LEFT	`deal`	`payment_item.deal_id = deal.deal_id`	Deal name.
`payment_item`	LEFT	`party` (as `buyer_party`)	`payment_item.buyer_id = buyer_party.party_id`	Buyer display name.
`payment_item`	LEFT	`party` (as `client_party`)	`payment_item.client_id = client_party.party_id`	Client display name.
`payment_item`	LEFT	`uta_entity`	`payment_item.uta_entity_id = uta_entity.uta_entity_id`	UTA entity name.
`payment_item`	LEFT	`department`	`payment_item.department_id = department.department_id`	Department name.

Filters:

payment_item.client_id = :clientId — filter to the target client

Computed Values:

None

Returns: Array of payment item rows for the specified client, each row enriched with deal name, buyer name, client name, UTA entity name, department name, bank account name, and bank account number.

2.4.5 Get Unposted Payment Items Up to Date

Operation: getUnpostedItemsUpToDate

Input Parameters:

cutoffDate: date (required) — payment_item.created_dt upper bound for the GL posting batch run

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`payment_item`	LEFT	`bank_account`	`payment_item.source_account_id = bank_account.bank_account_id`	Get `uta_account_id` from the UTA source bank account.
`payment_item`	LEFT	`participant_settlement_item`	`payment_item.participant_settlement_item_id = participant_settlement_item.participant_settlement_item_id`	Traverse to settlement.
`participant_settlement_item`	LEFT	`participant_settlement`	`participant_settlement_item.participant_settlement_id = participant_settlement.participant_settlement_id`	Traverse to settlement header.
`participant_settlement`	LEFT	`cash_receipt_application`	`participant_settlement.participant_settlement_id = cash_receipt_application.participant_settlement_id`	Get linked applications.
`cash_receipt_application`	LEFT	`billing_item_detail`	`cash_receipt_application.billing_item_detail_id = billing_item_detail.billing_item_detail_id`	Resolve to billing item.
`billing_item_detail`	LEFT	`billing_item`	`billing_item_detail.billing_item_id = billing_item.billing_item_id`	Get `payment_term_ref` for ERP.
`billing_item`	LEFT	`revenue_items`	`billing_item.revenue_item_id = revenue_items.revenue_item_id`	Get `sales_item_ref` for ERP.

Filters:

payment_item.payment_item_posting_status_cd = 'U' — only unposted items
payment_item.created_dt <= :cutoffDate — cutoff for the batch run

Computed Values:

payment_term_refs: distinct set of billing_item.payment_term_ref values collected per payment_item_id (multiple rows may exist due to the settlement-to-application join)
sales_item_refs: distinct set of revenue_items.sales_item_ref values collected per payment_item_id
Results are grouped by payment_item_id to deduplicate rows produced by the multi-application join

Returns: Array of records, one per distinct payment_item_id, each containing the full payment_item record, the uta_account_id from the source bank account, and deduplicated arrays of payment_term_refs and sales_item_refs for ERP reference resolution.

2.4.6 Get Sum Payments by Client

Operation: getSumPaymentsByClientMetric

Input Parameters:

None

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`payment_item`	LEFT	`party` (as `client_party`)	`payment_item.client_id = client_party.party_id`	Client display name for grouping.

Filters:

payment_item.payment_clearing_status_ind = false — only uncleared payments

Computed Values:

total_amount: COALESCE(SUM(payment_item.payment_item_amt), 0) per group, returned as a string to preserve decimal(15,2) precision
count: COUNT(*) per group
client_name: COALESCE(client_party.display_name, 'Unknown')

Returns: Array of records grouped by client_party.display_name, each containing client_name, total_amount (as string), and count of uncleared payment items for that client.

2.4.7 Reset Posted Payment Items

Operation: resetItemsPostedOnOrAfterDate

Input Parameters:

cutoffDate: date (required) — payment_item.posting_dt lower bound for the GL posting rollback

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`payment_item`	—	—	—	Direct update on the base table.

Filters:

payment_item.posting_dt >= :cutoffDate — target items posted on or after the rollback date

Computed Values:

None — this is an UPDATE operation: payment_item.payment_item_posting_status_cd is set to 'U' and payment_item.posting_dt is set to NULL

Returns: One record containing rows_affected (integer) indicating how many payment items were reset to unposted status.

2.5 Outbound Payment Execution

2.5.1 Get Execution by ID

Operation: getExecutionById

Input Parameters:

outboundPaymentExecutionId: uuid (required) — outbound_payment_execution.outbound_payment_execution_id

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`outbound_payment_execution`	—	—	—	Direct lookup by PK.

Filters:

outbound_payment_execution.outbound_payment_execution_id = :outboundPaymentExecutionId — exact match on PK
LIMIT 1 — single result

Computed Values:

None

Returns: One execution record with all fields from outbound_payment_execution, or null if no record matches the provided UUID.

2.5.2 Get Executions by Payment Item ID

Operation: getExecutionsByPaymentItemId

Input Parameters:

paymentItemId: integer (required) — outbound_payment_execution.payment_item_id

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`outbound_payment_execution`	LEFT	`payment_item`	`outbound_payment_execution.payment_item_id = payment_item.payment_item_id`	Get payment item name for display.
`payment_item`	LEFT	`party`	`payment_item.payment_party_id = party.party_id`	Get beneficiary display name.

Filters:

outbound_payment_execution.payment_item_id = :paymentItemId — scope to the target payment item

Computed Values:

None

Returns: Array of execution records ordered by outbound_payment_execution.created_dt descending, each enriched with payment_item_name from payment_item and beneficiary_name from party.

2.5.3 Get Recent Executions

Operation: getRecentExecutions

Input Parameters:

limit: integer (optional, default 50) — maximum number of records to return

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`outbound_payment_execution`	LEFT	`payment_item`	`outbound_payment_execution.payment_item_id = payment_item.payment_item_id`	Get payment item name for display.
`payment_item`	LEFT	`party`	`payment_item.payment_party_id = party.party_id`	Get beneficiary display name.

Filters:

None — all executions across all payment items

Computed Values:

None

Returns: Array of the most recent execution records up to :limit rows, ordered by outbound_payment_execution.created_dt descending, each enriched with payment_item_name and beneficiary_name in the same shape as getExecutionsByPaymentItemId (2.5.2).

2.5.4 Get Failed Executions

Operation: getFailedExecutions

Input Parameters:

None

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`outbound_payment_execution`	LEFT	`payment_item`	`outbound_payment_execution.payment_item_id = payment_item.payment_item_id`	Get payment item name for display.
`payment_item`	LEFT	`party`	`payment_item.payment_party_id = party.party_id`	Get beneficiary display name.

Filters:

outbound_payment_execution.execution_status_cd = 'FAILED' — only failed executions

Computed Values:

None

Returns: Array of all execution records in FAILED status ordered by outbound_payment_execution.created_dt descending, each enriched with payment_item_name and beneficiary_name in the same shape as getExecutionsByPaymentItemId (2.5.2).

2.5.5 Gather Payment Data for Bank Transmission

Operation: gatherPaymentData

Input Parameters:

paymentItemId: integer (required) — payment_item.payment_item_id

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`payment_item`	LEFT	`party`	`payment_item.payment_party_id = party.party_id`	Beneficiary name and party type (`ORGANIZATION` vs `INDIVIDUAL` via presence of `company_name`).
`payment_item`	LEFT	`bank_account` (source)	`payment_item.source_account_id = bank_account.bank_account_id`	UTA source/debtor bank account details (routing, account number, currency).
`payment_item`	LEFT	`bank_account` (beneficiary)	`payment_item.payment_party_bank_id = bank_account.bank_account_id`	Payee bank account details and `bank_id` that drives adapter selection.
`bank_account` (beneficiary)	LEFT	`party_bank_account`	`party_bank_account.bank_account_id = payment_item.payment_party_bank_id`	Get `preferred_payment_method` (`WIRE` or `ACH`).

Filters:

payment_item.payment_item_id = :paymentItemId — target payment item
LIMIT 1 for each sub-query — single record expected per join

Computed Values:

service_level: 'WIRE' if party_bank_account.preferred_payment_method = 'WIRE'; otherwise 'ACH' (default)
party_type: 'ORGANIZATION' if party.company_name is present; otherwise 'INDIVIDUAL'
requested_execution_date: payment_item.payment_date formatted as YYYY-MM-DD; fallback to current date
bank_id: from bank_account.bank_id on the beneficiary's bank account; drives adapter selection via the PAYMENT_REQUEST_SCHEMA code attribute on the bank

Returns: One assembled payment data object containing payment_item_id, amount, currency, nested source_account object (routing number, account number, account type DDA, bank name, currency), nested beneficiary object (party ID, name, account number, routing number, account type DDA, party type), remittance_info from payment_item.payment_item_comment, requested_execution_date, service_level, and bank_id.

3. Calculations & Formulas

3.1 Settlement Commission Calculation

text

-- Percentage-based (participant_settlement_item.participant_settlement_commission_flat_ind = false)
commission_amt = base_amount * (participant_settlment_commission_perc / 100)

-- Flat amount (participant_settlement_item.participant_settlement_commission_flat_ind = true)
commission_amt = deal_party_commission_amt  (from deal_party defaults loaded by getSettlementDefaults)

Source: participant_settlement_item.participant_settlement_commission_flat_ind, participant_settlement_item.participant_settlment_commission_perc, participant_settlement_item.participant_settlement_commission_amt
Base amount by calc_level_cd:
- DNI (Do Not Ignore Deductions): base_amount = pay_applied_amt - pay_deduction_applied (net of deductions)
- IGN (Ignore Deductions): base_amount = pay_applied_amt (gross, before deductions)
Precision: decimal(15,2) for amounts; decimal(7,4) for percentages

WARNING

The column participant_settlement_item.participant_settlment_commission_perc contains a known typo (missing 'e' in "settlement"). This name is preserved from the schema and must be used exactly as-is in all queries.

3.2 Settlement Total Validation

text

settlement_total = SUM(participant_settlement_item.participant_settlement_commission_amt)
                   for all items in the settlement

pay_applied_total = SUM(cash_receipt_application.cash_receipt_amt_applied)
                    for all PAY applications linked to the settlement

is_valid = ABS(settlement_total - pay_applied_total) <= 0.01

Source: participant_settlement_item.participant_settlement_commission_amt, cash_receipt_application.cash_receipt_amt_applied
Tolerance: 0.01 (accounts for rounding in percentage-based commission splits; broader than the standard 0.005 balance epsilon)
Example: settlement total $8,500.00, PAY applied $8,500.00 → is_valid = true

3.3 Payment Item Initial Status Determination

text

payment_execution_status_cd =
    IF (payment_date > current_date OR do_not_send_ind = true)
        THEN 'WAITING'
    ELSE 'PENDING'

Source: participant_settlement_item.payment_date, participant_settlement_item.do_not_send_ind
Example: payment_date = 2026-04-01 with current_date = 2026-03-02 → WAITING

3.4 Lock Status Determination

text

-- Per-item lock
is_locked(settlement_item) =
    EXISTS payment_item WHERE
        payment_item.participant_settlement_item_id = settlement_item.participant_settlement_item_id
        AND payment_item.payment_execution_status_cd IN ('PROCESSING', 'SENT', 'ACKNOWLEDGED', 'PAID')
        AND (payment_item.payment_item_posting_status_cd IS NULL
             OR payment_item.payment_item_posting_status_cd != 'X')

-- Header-level lock
is_locked(settlement) = ANY(item.is_locked FOR item IN settlement.items)

Source: payment_item.payment_execution_status_cd, payment_item.payment_item_posting_status_cd
Locked statuses: PROCESSING, SENT, ACKNOWLEDGED, PAID
Unlocked statuses: WAITING, PENDING, FAILED, CANCELLED, and null
Note: Payment items with payment_item_posting_status_cd = 'X' (voided) are excluded from lock evaluation regardless of execution status

3.5 Origin Type Classification

text

origin_type =
    IF (payment_item_type_cd = 'S' AND settlement_id IS NOT NULL) THEN 'SETTLEMENT'
    ELSE IF (payment_item_type_cd = 'L')                          THEN 'CLIENT_LEDGER'
    ELSE IF (payment_item_type_cd = 'R')                          THEN 'REFUND'
    ELSE                                                           'OTHER'

Source: payment_item.payment_item_type_cd, participant_settlement.participant_settlement_id
Example: payment_item_type_cd = 'S' with a non-null settlement ID → origin_type = 'SETTLEMENT', origin_reference = "Settlement #42"

3.6 Decimal Precision Rules

text

monetary_field  → decimal(15,2)  -- max $9,999,999,999,999.99
commission_perc → decimal(7,4)   -- max 999.9999%

Source: All *_amt fields on participant_settlement_item, payment_item, cash_receipt_payout; participant_settlment_commission_perc on participant_settlement_item
Tolerance: 0.005 for standard balance comparisons; 0.01 for settlement total validation
Transmission format: Monetary amounts must be transmitted as strings to preserve precision when passed between layers

4. Cross-References

Document	Relationship
Settlements Data Model	Full table definitions, ER diagram, status lifecycle diagrams for all five status machines (settlement, payment item execution, payment item GL posting, outbound execution, payout), code master values, and business validation constraints.
Worksheets Queries	Worksheet status transitions trigger settlement queries: Apply (D→P) is a prerequisite for settlement creation; Settle (P→T) triggers `createPayoutsForWorksheet` (2.3.1) and bulk settlement status update to `T`; Approve (T→A) triggers `createPaymentItemsFromPayouts` (2.4.1) and bulk settlement status update to `A`; Return (A→R) triggers `checkLockedPaymentsForDeletion` (2.1.5), status update to `R`, and selective voiding of unlocked payment items.
Billing Items Data Model	Settlement defaults (2.2.1) traverses into billing items to resolve `billing_item_detail.billing_item_detail_total_amt` (PAY amount), `billing_item_detail.billing_item_detail_gross_amt` (REV gross), deal/buyer/entity/department context fields propagated to `payment_item`, deduction amounts for commission calculation, and `billing_item.payment_term_ref` / `revenue_items.sales_item_ref` ERP references for GL posting (2.4.5).
Parties Data Model	`participant_settlement_item.payment_party_id` and `payment_item.payment_party_id` → `party.party_id` (payee identity). Deal party defaults loaded from `deal_party` during settlement creation. `payment_item.payment_party_bank_id` (payee destination) and `payment_item.source_account_id` (UTA source/debtor) reference `bank_account`. `party_bank_account.preferred_payment_method` drives `service_level` on `outbound_payment_execution`.
Accounting Data Model	`payment_item.payment_item_posting_status_cd` and `payment_item.posting_dt` track GL posting. The unposted items query (2.4.5) feeds the GL posting batch job. The reset query (2.4.7) supports GL posting rollback.

Sample Deals

Touring Deal

Endorsement Deal

Sports Endo Deal

Settlements Queries ​

1. Executive Summary ​

2. Key Queries ​

2.1 Settlement CRUD ​

2.1.1 Get Settlement by ID ​

2.1.2 Get Settlement Enriched ​

2.1.3 Get Settlement by Application IDs ​

2.1.4 Get Settlements by Worksheet ID ​

2.1.5 Check Locked Payments Before Settlement Deletion ​

2.2 Settlement Defaults ​

2.2.1 Get Settlement Defaults for Selected Receivables ​

2.2.2 Validate Settlement Total Against PAY Applied ​

2.3 Payout Lifecycle ​

2.3.1 Create Payouts for Worksheet ​

2.3.2 Get Payment Payouts for Worksheet ​

2.3.3 Check Existing VAT Pass-Through Payout ​

2.4 Payment Item Operations ​

2.4.1 Create Payment Items from Payouts ​

2.4.2 Get Payment Items with Origin Data ​

2.4.3 Get Single Payment Item with Origin Data ​

2.4.4 Get Payment Items by Client ID ​

2.4.5 Get Unposted Payment Items Up to Date ​

2.4.6 Get Sum Payments by Client ​

2.4.7 Reset Posted Payment Items ​

2.5 Outbound Payment Execution ​

2.5.1 Get Execution by ID ​

2.5.2 Get Executions by Payment Item ID ​

2.5.3 Get Recent Executions ​

2.5.4 Get Failed Executions ​

2.5.5 Gather Payment Data for Bank Transmission ​

3. Calculations & Formulas ​

3.1 Settlement Commission Calculation ​

3.2 Settlement Total Validation ​

3.3 Payment Item Initial Status Determination ​

3.4 Lock Status Determination ​

3.5 Origin Type Classification ​

3.6 Decimal Precision Rules ​

4. Cross-References ​

Settlements Queries

1. Executive Summary

2. Key Queries

2.1 Settlement CRUD

2.1.1 Get Settlement by ID

2.1.2 Get Settlement Enriched

2.1.3 Get Settlement by Application IDs

2.1.4 Get Settlements by Worksheet ID

2.1.5 Check Locked Payments Before Settlement Deletion

2.2 Settlement Defaults

2.2.1 Get Settlement Defaults for Selected Receivables

2.2.2 Validate Settlement Total Against PAY Applied

2.3 Payout Lifecycle

2.3.1 Create Payouts for Worksheet

2.3.2 Get Payment Payouts for Worksheet

2.3.3 Check Existing VAT Pass-Through Payout

2.4 Payment Item Operations

2.4.1 Create Payment Items from Payouts

2.4.2 Get Payment Items with Origin Data

2.4.3 Get Single Payment Item with Origin Data

2.4.4 Get Payment Items by Client ID

2.4.5 Get Unposted Payment Items Up to Date

2.4.6 Get Sum Payments by Client

2.4.7 Reset Posted Payment Items

2.5 Outbound Payment Execution

2.5.1 Get Execution by ID

2.5.2 Get Executions by Payment Item ID

2.5.3 Get Recent Executions

2.5.4 Get Failed Executions

2.5.5 Gather Payment Data for Bank Transmission

3. Calculations & Formulas

3.1 Settlement Commission Calculation

3.2 Settlement Total Validation

3.3 Payment Item Initial Status Determination

3.4 Lock Status Determination

3.5 Origin Type Classification

3.6 Decimal Precision Rules

4. Cross-References