Client Framework

Appearance

Settlements Procedures ​

1. Executive Summary ​

This document describes every data-mutation procedure in the settlements and payments domain. Each procedure is presented as a step-by-step sequence of database operations at the table and field level, independent of any specific technology stack.

The procedures fall into six functional groups:

  1. Settlement Lifecycle — Creating settlements from PAY applications, populating deal-party defaults, saving/updating/deleting settlement headers and items.
  2. Payout Creation — Generating cash_receipt_payout records from settlement items, bridging settlements to the payment layer.
  3. Payment Item Creation — Converting payout records into payment_item records at worksheet approval, with initial status determination and back-linking.
  4. Outbound Payment Execution — Transmitting payment items to banks through the adapter pattern, recording execution attempts, and handling success/failure.
  5. Payment Status Polling — Querying bank APIs for status updates on sent payments and propagating terminal statuses back to payment_item.
  6. Void & Cancellation — Voiding unlocked payment items during settlement edits, settlement deletion, and worksheet returns.

Data model reference: Settlements Data Model

Queries reference: Settlements Queries

Tables mutated: participant_settlement, participant_settlement_item, payment_item, outbound_payment_execution, cash_receipt_application, cash_receipt_payout

2. Key Procedures ​

2.1 Settlement Lifecycle ​

2.1.1 Compute Settlement Defaults ​

Operation: computeSettlementDefaults

Trigger: User selects one or more REV receivable rows on an Applied worksheet and clicks "Create Settlement," causing the system to pre-populate the settlement form.

Input Parameters:

  • revBillingItemDetailIds: int[] (required) — IDs of the selected REV billing_item_detail records
  • applicationIds: int[] (optional) — cash_receipt_application IDs scoped to the current worksheet; when provided, restricts amount aggregations to this worksheet only

Calculates the pre-populated values shown when a user starts creating a new settlement from selected receivables. This procedure is read-only — it does not mutate any tables.

Step 1. Resolve Source Details ​

  • Source: billing_item_detail joined to billing_item, revenue_item, and deal for all rows matching revBillingItemDetailIds.
  • Action: SELECT (read-only).
  • Logic:
    • Validate that all rows share the same revenue_item_id. If any row differs, reject with error: "All selected receivables must belong to the same Revenue Item."
    • Extract deal_id, currency_cd, deal_name, and revenue_item_name from the first row.
    • Collect billing_item_id values as source_billing_item_ids.

Step 2. Find Sibling PAY Details ​

  • Source: billing_item_detail filtered by billing_item_id IN source_billing_item_ids and billing_item_detail_type_cd = 'PAY'.
  • Action: SELECT (read-only).
  • Logic:
    • Sum billing_item_detail_total_amt across all matching rows to produce total_pay_amt.
    • Collect the resulting billing_item_detail_id values as pay_detail_ids.

Step 3. Sum REV Gross ​

  • Source: billing_item_detail filtered by billing_item_detail_id IN revBillingItemDetailIds.
  • Action: SELECT (read-only).
  • Logic:
    • Sum billing_item_detail_gross_amt to produce total_rev_amt.

Step 4. Sum REV Applied ​

  • Source: cash_receipt_application filtered by billing_item_detail_id IN revBillingItemDetailIds.
  • Action: SELECT (read-only).
  • Logic:
    • When applicationIds is provided, additionally filter by cash_receipt_application_id IN applicationIds.
    • Sum cash_receipt_amt_applied to produce total_rev_applied.

Step 5. Sum PAY Applied ​

  • Source: cash_receipt_application filtered by billing_item_detail_id IN pay_detail_ids.
  • Action: SELECT (read-only).
  • Logic:
    • When applicationIds is provided, additionally filter by cash_receipt_application_id IN applicationIds.
    • Sum cash_receipt_amt_applied to produce total_pay_applied.

Step 6. Sum Deductions Split by REV and PAY ​

  • Source: cash_receipt_application_deduction joined to cash_receipt_application.
  • Action: SELECT (read-only).
  • Logic:
    • Identify REV application IDs: cash_receipt_application where billing_item_detail_id IN revBillingItemDetailIds (and scoped by applicationIds if provided).
    • Identify PAY application IDs: cash_receipt_application where billing_item_detail_id IN pay_detail_ids (and scoped by applicationIds if provided).
    • Sum deduction_amt_applied for each set separately, producing rev_deduction_applied and pay_deduction_applied.

Step 7. Load Deal Parties ​

  • Source: deal_party joined to party, party_bank_account (where active_ind = true), and bank_account for the resolved deal_id.
  • Action: SELECT (read-only).
  • Logic:
    • For each deal party, return: party_id, display_name, party_role_type_cd, deal_party_commission_flat_ind, deal_party_commission_perc, deal_party_commission_amt, bank_account_id, and a display-friendly bank account name.

Side-effects:

  • None. This procedure is read-only.

Postconditions:

  • Caller receives an aggregated defaults object containing deal name, revenue item name, total_pay_amt, total_rev_amt, total_rev_applied, total_pay_applied, rev_deduction_applied, pay_deduction_applied, pay_detail_ids, currency, and the deal party list with commission defaults and bank accounts.

2.1.2 Create Settlement ​

Operation: createSettlement

Trigger: User completes the settlement form and clicks Save, after selecting one or more PAY receivable applications on an Applied worksheet.

Input Parameters:

  • header: Object — participant_settlement_overrided_ind, participant_settlement_comment
  • items: Object[] — one per payee, each containing payment_party_id, payment_party_bank_id, participant_settlement_commission_flat_ind, participant_settlment_commission_perc, participant_settlement_commission_amt, calc_level_cd, participant_settlement_item_comment, payment_date, do_not_send_ind
  • applicationIds: int[] — the cash_receipt_application records being settled
  • actorId: String — identifier for the user performing the operation

Creates a new participant_settlement header and one or more participant_settlement_item rows, then links the relevant cash_receipt_application records, and auto-creates payout records.

**PoC Artifact:** The PoC performs the settlement-total validation at the server-action layer before calling the service. Production should enforce this validation in the service or at the database level.

Step 1. Validate Settlement Total ​

  • Source: cash_receipt_application joined to billing_item_detail for applicationIds where billing_item_detail_type_cd = 'PAY'.
  • Action: SELECT (validation, read-only).
  • Logic:
    • Sum cash_receipt_amt_applied across matching rows to produce pay_applied_amt.
    • Sum participant_settlement_commission_amt across all input items to produce input_total.
    • Assert |input_total - pay_applied_amt| <= 0.01. If not satisfied, reject with error.

Step 2. Insert Settlement Header ​

  • Source: Input header fields.
  • Action: INSERT into participant_settlement.
  • Logic:
    • Set participant_settlement_status_cd = 'D' (Draft).
    • Set participant_settlement_overrided_ind = from input.
    • Set participant_settlement_comment = from input.
    • Set created_by = actorId, created_dt = current timestamp.
    • Set updated_by = actorId, updated_dt = current timestamp.
    • Capture the returned participant_settlement_id.

Step 3. Insert Settlement Items ​

  • Source: Input items array.
  • Action: INSERT into participant_settlement_item for each item where participant_settlement_commission_amt != 0.
  • Logic:
    • Set participant_settlement_id = from Step 2.
    • Set payment_party_id = payee party ID from input.
    • Set payment_party_bank_id = payee bank account ID from input.
    • Set participant_settlement_commission_flat_ind = from input (true = flat, false = percentage).
    • Set participant_settlment_commission_perc = from input (decimal(7,4)).
    • Set participant_settlement_commission_amt = from input (decimal(15,2)).
    • Set calc_level_cd = from input (default 'DNI' if not specified).
    • Set participant_settlement_item_comment = from input.
    • Set payment_date = from input.
    • Set do_not_send_ind = from input (default false).
    • Set created_by / updated_by = actorId, timestamps = current.
    • Items with participant_settlement_commission_amt = 0 are skipped entirely.
  • Source: cash_receipt_application rows matching cash_receipt_application_id IN applicationIds.
  • Action: UPDATE cash_receipt_application.
  • Logic:
    • Set participant_settlement_id = participant_settlement_id from Step 2.
    • Set updated_by = actorId, updated_dt = current timestamp.

Step 5. Auto-Create Payouts ​

  • Source: Settlement items created in Step 3.
  • Action: Invoke the payout-creation procedure (createPayoutsForWorksheet, section 2.2.1). This is idempotent — items that already have payouts are skipped.
  • Logic:
    • Payouts are created immediately after saving the settlement.
    • Items with zero participant_settlement_commission_amt are skipped.

Side-effects:

  • cash_receipt_payout records of type 'S' are created for each non-zero settlement item that does not already have a payout.
  • cash_receipt_application.participant_settlement_id is populated for all linked application records.

Postconditions:

  • participant_settlement record exists with participant_settlement_status_cd = 'D'.
  • participant_settlement_item records exist for each non-zero payee.
  • All applicationIds now have participant_settlement_id set to the new settlement.
  • cash_receipt_payout records exist for each qualifying settlement item.
  • Returns the new participant_settlement_id.

2.1.3 Update Settlement ​

Operation: updateSettlement

Trigger: User modifies an existing settlement on an Applied worksheet and clicks Save.

Input Parameters:

  • settlementId: Integer — the participant_settlement_id to update
  • header: Object — updated header fields
  • items: Object[] — updated items array (one per payee)
  • applicationIds: int[] — updated set of linked cash_receipt_application IDs
  • actorId: String — identifier for the user performing the operation

Updates an existing settlement header and items. Handles party additions, removals, and amount changes while preserving locked items.

Step 1. Update Header ​

  • Source: Input header fields.
  • Action: UPDATE participant_settlement where participant_settlement_id = settlementId.
  • Logic:
    • Set participant_settlement_overrided_ind, participant_settlement_comment = from input.
    • Set updated_by = actorId, updated_dt = current timestamp.

Step 2. Identify Locked Items ​

  • Source: payment_item where participant_settlement_item_id matches existing items on this settlement.
  • Action: SELECT (read-only).
  • Logic:
    • Exclude voided payment items (payment_item_posting_status_cd = 'X').
    • Build a set of locked participant_settlement_item_id values — those linked to payment items with payment_execution_status_cd IN ('PROCESSING', 'SENT', 'ACKNOWLEDGED', 'PAID').

Step 3. Process Each Incoming Item ​

  • Source: Input items array matched against existing participant_settlement_item records by payment_party_id.
  • Action: UPDATE, INSERT, or skip per item based on lock state and amount.
  • Logic:
    • If an existing item for that party is locked: skip it entirely (preserve as-is).
    • If an existing item for that party is unlocked:
      • If the new amount is zero: mark for deletion (handled in Step 5).
      • If the amount changed and the item has a linked payment_item_id: void the old payment item by setting payment_item_posting_status_cd = 'X', payment_execution_status_cd = 'CANCELLED', return_reason_cd = 'SETTLEMENT_EDIT', returned_dt = now(). Then clear payment_item_id on both the settlement item and its corresponding cash_receipt_payout.
      • Update the settlement item fields: payment_party_bank_id, participant_settlement_commission_flat_ind, participant_settlment_commission_perc, participant_settlement_commission_amt, calc_level_cd, participant_settlement_item_comment, payment_date.
      • Also update the corresponding cash_receipt_payout record fields: payment_item_amt, payment_item_name, payment_date.
    • If no existing item for that party: INSERT a new participant_settlement_item (skip if amount is zero).
  • Source: cash_receipt_application currently linked to this settlement plus the new applicationIds.
  • Action: UPDATE cash_receipt_application.
  • Logic:
    • First, clear participant_settlement_id on all cash_receipt_application rows currently linked to this settlement (set to NULL).
    • Then, set participant_settlement_id = settlementId for all rows in applicationIds.

Step 5. Delete Removed Items ​

  • Source: Existing participant_settlement_item records whose payment_party_id is not in the incoming items set and whose participant_settlement_item_id is not locked.
  • Action: UPDATE payment_item, DELETE cash_receipt_payout, DELETE participant_settlement_item.
  • Logic:
    • If linked to a payment_item: void the payment item using the same pattern as Step 3.
    • Delete the corresponding cash_receipt_payout row.
    • Delete the participant_settlement_item row.

Side-effects:

  • Voided payment items have payment_execution_status_cd = 'CANCELLED', payment_item_posting_status_cd = 'X', return_reason_cd = 'SETTLEMENT_EDIT'.
  • cash_receipt_application.participant_settlement_id is updated to reflect the current application linkage.

Postconditions:

  • participant_settlement header reflects the updated values.
  • Locked items are unchanged.
  • Unlocked items reflect the new amounts, party, and bank assignments.
  • Removed unlocked items and their payouts have been deleted.

2.2 Payout Creation ​

2.2.1 Create Payouts from Settlement Items ​

Operation: createPayoutsForWorksheet

Trigger: Called automatically after settlement save (section 2.1.2), during the worksheet Settle transition (Applied P → Settled T), or at worksheet approval if previously skipped. This procedure is idempotent.

Input Parameters:

  • worksheetId: Integer — the cash_receipt_worksheet_id to process
  • actorId: String — identifier for the user performing the operation

Generates cash_receipt_payout records of type 'S' from settlement items. This bridges the settlement to the payment layer and is idempotent — it skips items that already have payout records and items with zero amounts.

Step 1. Load Settlements for the Worksheet ​

  • Source: cash_receipt_application where cash_receipt_worksheet_id = worksheetId joined to participant_settlement via participant_settlement_id.
  • Action: SELECT (read-only).
  • Logic:
    • Collect all distinct participant_settlement_id values linked to this worksheet.

Step 2. Load Existing Payouts ​

  • Source: cash_receipt_payout where cash_receipt_worksheet_id = worksheetId.
  • Action: SELECT (read-only).
  • Logic:
    • Build a set of participant_settlement_item_id values that already have payout records. These will be skipped in Step 4.

Step 3. Load Full Settlement with Deal Context ​

  • Source: participant_settlement_item for each settlement found in Step 1. Also query cash_receipt_application joined to billing_item_detail joined to billing_item via participant_settlement_id to extract deal context.
  • Action: SELECT (read-only).
  • Logic:
    • For each settlement, load its full item list.
    • Extract deal_id, buyer_id, uta_entity_id, department_id from the first matching billing_item record.

Step 4. Filter Items Needing Payouts ​

  • Source: Settlement items from Step 3.
  • Action: SELECT (read-only, filtering).
  • Logic:
    • Exclude items whose participant_settlement_item_id is already in the existing payout set (Step 2).
    • Exclude items where participant_settlement_commission_amt = 0.

Step 5. Insert Payout Records ​

  • Source: Qualifying settlement items from Step 4.
  • Action: INSERT into cash_receipt_payout for each item.
  • Logic:
    • Set cash_receipt_worksheet_id = worksheetId.
    • Set payout_party_id = participant_settlement_item.payment_party_id.
    • Set payment_party_bank_id = participant_settlement_item.payment_party_bank_id.
    • Set participant_settlement_item_id = the settlement item's participant_settlement_item_id.
    • Set deal_id = from billing item context (Step 3).
    • Set buyer_id = from billing item context.
    • Set uta_entity_id = from billing item context.
    • Set department_id = from billing item context.
    • Set payment_item_amt = participant_settlement_item.participant_settlement_commission_amt.
    • Set payment_item_name = participant_settlement_item.participant_settlement_item_comment or a system default.
    • Set payment_item_type_cd = 'S' (Settlement).
    • Set payment_item_currency_cd = 'USD' (or from billing item if available).
    • Set payment_date = participant_settlement_item.payment_date.
    • Set do_not_send_ind = participant_settlement_item.do_not_send_ind.
    • Set payment_item_id = NULL (populated at worksheet approval).
    • Set created_by / updated_by = actorId, timestamps = current.

Side-effects:

  • cash_receipt_payout records of type 'S' are created for each qualifying settlement item.

Postconditions:

  • Every non-zero settlement item on the worksheet that did not already have a payout now has a corresponding cash_receipt_payout record with payment_item_id = NULL.

2.2.2 Create Passthrough Payment Payout ​

Operation: createPassthroughPayout

Trigger: User adds a passthrough payout entry on the worksheet Payouts tab.

Input Parameters:

  • worksheetId: Integer — the cash_receipt_worksheet_id
  • payoutPartyId: Integer — party.party_id of the payee
  • paymentPartyBankId: Integer — bank_account.bank_account_id for the payee
  • dealId: Integer (optional) — deal.deal_id
  • buyerId: Integer (optional) — party.party_id of the buyer
  • utaEntityId: Integer (optional) — uta_entity.uta_entity_id
  • departmentId: Integer (optional) — department.department_id
  • paymentItemName: String — descriptive name for the payout
  • paymentItemAmt: String (decimal) — amount to pay (decimal(15,2))
  • paymentItemTypeCd: String — 'P' (Passthrough)
  • paymentItemCurrencyCd: String — ISO currency code
  • paymentDate: Date (optional) — requested payment date
  • doNotSendInd: Boolean — whether to hold from transmission
  • actorId: String — identifier for the user performing the operation

Creates a direct payment payout record that bypasses the settlement process. Passthrough payments go from the worksheet to payment without dividing among deal parties via a settlement.

Step 1. Insert Payout Record ​

  • Source: Input fields.
  • Action: INSERT into cash_receipt_payout.
  • Logic:
    • Set cash_receipt_worksheet_id = worksheetId.
    • Set payout_party_id = payoutPartyId.
    • Set payment_party_bank_id = paymentPartyBankId.
    • Set deal_id, buyer_id, uta_entity_id, department_id = from input (may be NULL).
    • Set payment_item_name = from input.
    • Set payment_item_amt = from input.
    • Set payment_item_type_cd = 'P'.
    • Set payment_item_currency_cd = from input (default 'USD').
    • Set payment_date = from input.
    • Set do_not_send_ind = from input (default false).
    • Set participant_settlement_item_id = NULL (no settlement linkage).
    • Set payment_item_id = NULL (populated at worksheet approval).
    • Set created_by / updated_by = actorId, timestamps = current.

Side-effects:

  • None beyond the inserted payout record.

Postconditions:

  • A cash_receipt_payout record with payment_item_type_cd = 'P' and payment_item_id = NULL exists for the worksheet.
  • The payout will be converted to a payment_item when the worksheet is approved (section 2.3.1).

2.2.3 Create VAT Pass-Through Payout ​

Operation: createVatPassthroughPayout

Trigger: The tax engine determines that UK VAT applies to the artist fee on a worksheet receivable.

Input Parameters:

  • worksheetId: Integer — the cash_receipt_worksheet_id
  • billingItemId: Integer — billing_item.billing_item_id for context lookup
  • clientPartyId: Integer — party.party_id of the client
  • vatAmt: String (decimal) — VAT amount to pass through
  • currencyCd: String — ISO currency code
  • actorId: String — identifier for the user performing the operation

Creates a VAT-specific pass-through payout (type 'V') for UK Artist Fee VAT scenarios. This is an idempotent operation.

Step 1. Check for Existing VAT Payout ​

  • Source: cash_receipt_payout where cash_receipt_worksheet_id = worksheetId and payment_item_type_cd = 'V'.
  • Action: SELECT (read-only).
  • Logic:
    • If any rows exist, return without creating a new payout (idempotent guard).

Step 2. Look Up Billing Item Context ​

  • Source: billing_item where billing_item_id = billingItemId.
  • Action: SELECT (read-only).
  • Logic:
    • Extract deal_id, buyer_id, uta_entity_id, department_id for use in the payout record.

Step 3. Insert VAT Payout ​

  • Source: Input fields and billing item context from Step 2.
  • Action: INSERT into cash_receipt_payout.
  • Logic:
    • Set payment_item_type_cd = 'V'.
    • Set payment_item_name = 'UK VAT Pass-Through on Artist Fee'.
    • Set payment_item_amt = vatAmt.
    • Set payment_item_currency_cd = currencyCd.
    • Set deal_id, buyer_id, uta_entity_id, department_id = from billing item context.
    • Set payout_party_id = clientPartyId.
    • Set all other fields following the passthrough pattern (section 2.2.2).

Side-effects:

  • None beyond the inserted payout record if none already existed.

Postconditions:

  • At most one cash_receipt_payout record with payment_item_type_cd = 'V' exists for this worksheet.

2.3 Payment Item Creation ​

2.3.1 Create Payment Items from Payouts ​

Operation: createPaymentItemsFromPayouts

Trigger: Called during worksheet approval (Settled T → Approved A), after settlements are approved, converting payout records into outbound payment requests.

Input Parameters:

  • worksheetId: Integer — the cash_receipt_worksheet_id to process
  • actorId: String — identifier for the user performing the operation

Converts cash_receipt_payout records into payment_item records at worksheet approval. This is where outbound payment requests are materialized. Only payouts with payment_item_id IS NULL are processed.

Step 1. Load Payouts Needing Payment Items ​

  • Source: cash_receipt_payout joined to participant_settlement_item, participant_settlement, cash_receipt_application, billing_item_detail, billing_item, cash_receipt_worksheet, cash_receipt_split, and cash_receipt where cash_receipt_worksheet_id = worksheetId and payment_item_id IS NULL.
  • Action: SELECT (read-only).
  • Logic:
    • Deduplicate by cash_receipt_payout_id — the settlement-to-application join may produce multiple rows per payout.
    • Collect: payment_item_type_cd, payment_item_name, payout_party_id, participant_settlement_item_id, payment_item_amt, payment_item_currency_cd, payment_date, payment_party_bank_id (payout), payment_party_bank_id (settlement item, as fallback), participant_settlement_item_comment, deal_id (payout and billing item), buyer_id (payout and billing item), uta_entity_id (payout and billing item), department_id (payout and billing item), client_id (billing item), contracted_party_id (billing item), bank_account_id from cash_receipt (UTA source account), do_not_send_ind.

Step 2. Determine Initial Execution Status ​

  • Source: payment_date and do_not_send_ind from each payout.
  • Action: Computation (no database write).
  • Logic:
    • If payment_date is in the future OR do_not_send_ind = true: set initial_status = 'WAITING'.
    • Otherwise: set initial_status = 'PENDING'.

Step 3. Insert Payment Item ​

  • Source: Payout data from Step 1 and status from Step 2.
  • Action: INSERT into payment_item.
  • Logic:
    • Set payment_item_type_cd = cash_receipt_payout.payment_item_type_cd (default 'P' if null).
    • Set payment_item_name = cash_receipt_payout.payment_item_name (default 'Commission Payment' if null).
    • Set payment_party_id = cash_receipt_payout.payout_party_id.
    • Set payment_party_bank_id = payout's payment_party_bank_id first; fall back to settlement item's payment_party_bank_id.
    • Set participant_settlement_item_id = cash_receipt_payout.participant_settlement_item_id.
    • Set payment_item_amt = cash_receipt_payout.payment_item_amt.
    • Set payment_item_currency_cd = cash_receipt_payout.payment_item_currency_cd (default 'USD').
    • Set payment_item_comment = participant_settlement_item.participant_settlement_item_comment.
    • Set payment_item_posting_status_cd = 'U' (Unposted).
    • Set payment_execution_status_cd = initial_status from Step 2.
    • Set payment_clearing_status_ind = true.
    • Set payment_date = cash_receipt_payout.payment_date.
    • Set deal_id = payout's deal_id first, then billing item's deal_id.
    • Set client_id = billing item's client_id first; fall back to payout_party_id (for passthroughs where payout party is the client).
    • Set buyer_id = payout's buyer_id first, then billing item's.
    • Set contracted_party_id = billing item's contracted_party_id.
    • Set department_id = payout's department_id first, then billing item's.
    • Set uta_entity_id = payout's uta_entity_id first, then billing item's.
    • Set source_account_id = cash_receipt.bank_account_id (UTA's receiving bank account).
    • Set do_not_send_ind = cash_receipt_payout.do_not_send_ind.
    • Set created_by / updated_by = actorId.
    • Capture the returned payment_item_id.

IMPORTANT

The field priority for deal_id, buyer_id, uta_entity_id, and department_id is: payout fields first, then billing item fields. This ensures passthrough payments (which may not have billing item linkage) are populated correctly from the payout record.

  • Source: The cash_receipt_payout record processed in Step 3.
  • Action: UPDATE cash_receipt_payout.
  • Logic:
    • Set payment_item_id = payment_item_id from Step 3.
    • Set updated_by = actorId, updated_dt = current timestamp.
  • Source: The participant_settlement_item linked to this payout.
  • Action: UPDATE participant_settlement_item.
  • Logic:
    • Set payment_item_id = payment_item_id from Step 3.
    • Set updated_by = actorId, updated_dt = current timestamp.

Side-effects:

  • cash_receipt_payout.payment_item_id is populated for each processed payout.
  • participant_settlement_item.payment_item_id is populated for each linked settlement item.

Postconditions:

  • One payment_item record exists for each qualifying payout (non-zero, previously unlinked).
  • Each new payment item has payment_execution_status_cd = 'WAITING' (future-dated or do-not-send) or 'PENDING' (ready for processing).
  • Each new payment item has payment_item_posting_status_cd = 'U' (GL not yet posted).

2.4 Settlement Status Transitions ​

2.4.1 Submit Settlements for Worksheet (Applied → Settled) ​

Operation: submitSettlementsForWorksheet

Trigger: Worksheet transitions from Applied (P) to Settled (T). Called by the worksheet settle procedure.

Input Parameters:

  • worksheetId: Integer — the cash_receipt_worksheet_id
  • actorId: String — identifier for the user performing the operation

Ensures payouts exist for all settlement items and transitions all settlements on the worksheet to Settled status.

Step 1. Create Payouts (Idempotent) ​

  • Source: All settlements linked to this worksheet.
  • Action: Invoke createPayoutsForWorksheet (section 2.2.1).
  • Logic:
    • Creates payout records for any settlement items that do not yet have them.
    • Skips items that already have payouts and items with zero amounts.

Step 2. Update Settlement Statuses to Settled ​

  • Source: All participant_settlement records linked to this worksheet via cash_receipt_application.participant_settlement_id.
  • Action: UPDATE participant_settlement.
  • Logic:
    • Set participant_settlement_status_cd = 'T' (Settled).
    • Set updated_by = actorId, updated_dt = current timestamp.

Side-effects:

  • All payout records that were missing are now created.

Postconditions:

  • All participant_settlement records on this worksheet have participant_settlement_status_cd = 'T'.
  • All settlement items have corresponding cash_receipt_payout records.

2.4.2 Approve Settlements for Worksheet (Settled → Approved) ​

Operation: approveAllSettlementsForWorksheet

Trigger: Worksheet transitions to Approved (A). Called by the worksheet approval procedure after settling.

Input Parameters:

  • worksheetId: Integer — the cash_receipt_worksheet_id
  • actorId: String — identifier for the user performing the operation

Transitions all settlements on the worksheet to Approved status. Idempotent — settlements already in Approved status are skipped.

Step 1. Load Settlements ​

  • Source: participant_settlement records linked to this worksheet.
  • Action: SELECT (read-only).
  • Logic:
    • Find all participant_settlement_id values for this worksheet via cash_receipt_application.participant_settlement_id.

Step 2. Update Each Settlement Status ​

  • Source: Each participant_settlement record from Step 1.
  • Action: UPDATE participant_settlement for each non-Approved settlement.
  • Logic:
    • If participant_settlement_status_cd = 'A' already: skip (idempotent).
    • Otherwise: set participant_settlement_status_cd = 'A' (Approved).
    • Set updated_by = actorId, updated_dt = current timestamp.

Side-effects:

  • None beyond the status update. Payment items are created in a separate step (section 2.3.1).

Postconditions:

  • All participant_settlement records on this worksheet have participant_settlement_status_cd = 'A'.

2.4.3 Return Settlements (Worksheet Return) ​

Operation: returnSettlementsForWorksheet

Trigger: Worksheet return procedure is initiated for an Approved worksheet.

Input Parameters:

  • worksheetId: Integer — the cash_receipt_worksheet_id being returned
  • actorId: String — identifier for the user performing the operation

Sets all settlements on the worksheet to Returned status as part of the three-document return model.

Step 1. Load Settlements ​

  • Source: participant_settlement records linked to this worksheet.
  • Action: SELECT (read-only).
  • Logic:
    • Find all participant_settlement_id values for this worksheet.

Step 2. Update Settlement Statuses to Returned ​

  • Source: Each participant_settlement from Step 1.
  • Action: UPDATE participant_settlement.
  • Logic:
    • Set participant_settlement_status_cd = 'R' (Returned).
    • Set returned_dt = current timestamp.
    • Set returned_by_user_id = actorId.
    • Set updated_by = actorId, updated_dt = current timestamp.

Side-effects:

  • All settlements on the worksheet are sealed as Returned.

Postconditions:

  • All participant_settlement records on this worksheet have participant_settlement_status_cd = 'R'.
  • Returned settlements are read-only historical records.

2.5 Outbound Payment Execution ​

2.5.1 Process Pending Payment Items ​

Operation: processPendingItems

Trigger: User selects payment items on the Payments page and clicks "Process," or a scheduled job picks up PENDING items.

Input Parameters:

  • paymentItemIds: int[] — IDs of payment_item records to process
  • actorId: String — identifier for the user performing the operation

Transmits pending payment items to banks through the adapter pattern. This is the core outbound payment execution flow.

Step 1. Lock Payment Items ​

  • Source: payment_item records matching paymentItemIds.
  • Action: UPDATE payment_item.
  • Logic:
    • Set payment_execution_status_cd = 'PROCESSING' for all input IDs in a single bulk update.
    • Set updated_by = actorId, updated_dt = current timestamp.

Step 2. Gather Payment Data ​

  • Source: payment_item joined to party (beneficiary) for each payment item.
  • Action: SELECT (read-only).
  • Logic:
    • Query bank_account for source_account_id (UTA's account / debtor information).
    • Query bank_account for payment_party_bank_id (beneficiary's bank account). The bank_id on this record drives adapter selection.
    • Query party_bank_account for preferred_payment_method on the beneficiary's bank account. If 'WIRE', use wire; otherwise default to 'ACH'.

Step 3. Determine Payment Schema ​

  • Source: bank_account.bank_id from the beneficiary's bank account (Step 2).
  • Action: SELECT from code_attribute (read-only).
  • Logic:
    • Query code_attribute where code_master_type = 'BANK', code = bank_id, attribute = 'PAYMENT_REQUEST_SCHEMA'.
    • The value column determines which adapter to use: CNB_EASI_LINK, ISO20022_PAIN001, BOFA_CASHPRO, or JPM_GLOBAL_PAY.
    • If no matching code attribute is found, reject with error.

IMPORTANT

Adapter selection is driven by the PAYMENT_REQUEST_SCHEMA code attribute on the bank, not by bank identity. This decouples the adapter from specific banks — any bank can use any schema.

Step 4. Create Execution Record ​

  • Source: Payment data from Step 2 and schema from Step 3.
  • Action: INSERT into outbound_payment_execution.
  • Logic:
    • Set outbound_payment_execution_id = auto-generated UUID.
    • Set payment_item_id = current payment item ID.
    • Set bank_profile_id = 0 (legacy field, no longer used for routing).
    • Set bank_profile_name = human-readable bank name derived from bank_id.
    • Set execution_status_cd = 'CREATED'.
    • Set payload_format = 'JSON' or 'XML' (from adapter).
    • Set payment_schema = schema value from Step 3.
    • Set requested_execution_date = payment_item.payment_date or today's date.
    • Set payment_amount = payment_item.payment_item_amt.
    • Set payment_currency = payment_item.payment_item_currency_cd.
    • Set service_level = 'WIRE' or 'ACH' from Step 2.
    • Set created_by / updated_by = actorId.

Step 5. Build and Save Payload ​

  • Source: Payment data from Step 2.
  • Action: UPDATE outbound_payment_execution.generated_payload.
  • Logic:
    • Invoke the adapter's buildPayload method with the gathered payment data. The adapter produces the bank-specific format (JSON for EASI Link, XML for ISO 20022 variants).
    • Save the generated payload to outbound_payment_execution.generated_payload before transmission. This ensures the payload is preserved even if transmission fails.

Step 6. Transmit to Bank ​

  • Source: Payload from Step 5.
  • Action: External HTTP call (bank API). UPDATE outbound_payment_execution and payment_item based on result.
  • Logic:
    • Send the payload to the bank API endpoint.
    • On success (HTTP 2xx):
      • Update outbound_payment_execution: set execution_status_cd = 'SENT', bank_reference_id = from bank response, http_response_code = HTTP code.
      • Update payment_item: set payment_execution_status_cd = 'SENT'. The payment is now locked — it cannot be reversed, voided, or modified.
    • On failure (HTTP 4xx/5xx or error):
      • Update outbound_payment_execution: set execution_status_cd = 'FAILED', http_response_code = HTTP code, error_message = error details.
      • Revert payment_item: set payment_execution_status_cd = 'PENDING' (allows retry without manual intervention).

WARNING

On failure, the payment item is reverted to PENDING, not FAILED. This allows immediate retry. The FAILED status on payment_item is reserved for cases where the bank confirms failure after initial acceptance, received via status polling.

Side-effects:

  • outbound_payment_execution record is created with the full payload and result.
  • Payment items that succeed are locked at payment_execution_status_cd = 'SENT'.
  • Payment items that fail revert to payment_execution_status_cd = 'PENDING' for retry.

Postconditions:

  • Each successfully processed payment item has payment_execution_status_cd = 'SENT' and a linked outbound_payment_execution with execution_status_cd = 'SENT' and a bank_reference_id.
  • Each failed payment item has payment_execution_status_cd = 'PENDING' and a linked outbound_payment_execution with execution_status_cd = 'FAILED'.

2.5.2 Retry Failed Execution ​

Operation: retryExecution

Trigger: User clicks "Retry" on a failed execution on the Payments page.

Input Parameters:

  • executionId: UUID — the outbound_payment_execution_id to retry
  • actorId: String — identifier for the user performing the operation

Retries a failed outbound payment execution by reprocessing the original payment item. Creates a new execution record rather than modifying the failed one, preserving the full audit trail.

Step 1. Load Failed Execution ​

  • Source: outbound_payment_execution where outbound_payment_execution_id = executionId.
  • Action: SELECT (read-only).
  • Logic:
    • Validate that execution_status_cd = 'FAILED'. If not, reject with error.
    • Validate that payment_item_id is not null. If null, reject with error.

Step 2. Reprocess Payment Item ​

  • Source: payment_item_id from Step 1.
  • Action: Invoke processPendingItems (section 2.5.1) with [execution.payment_item_id].
  • Logic:
    • A new outbound_payment_execution record is created for the same payment item.
    • The original failed execution record is never modified (audit trail preserved).

NOTE

Execution records are immutable once terminal (ACKNOWLEDGED or FAILED). Retries always create new execution records, providing a complete history of all transmission attempts for a given payment item.

Side-effects:

  • A new outbound_payment_execution record is created for the same payment_item_id.
  • The failed execution record is not modified.

Postconditions:

  • A new outbound_payment_execution exists for the payment item. Its outcome depends on the bank response (same as section 2.5.1).

2.6 Payment Status Polling ​

2.6.1 Poll Payment Status ​

Operation: pollPendingPayments

Trigger: Scheduled polling job runs periodically, or user clicks "Check Status" for a single execution.

Input Parameters:

  • executionId: UUID (optional) — when provided, polls only the specified execution; when omitted, polls all SENT executions
  • actorId: String — identifier for the user performing the operation

Queries bank APIs for status updates on SENT payment items and propagates terminal statuses back to payment_item.

Step 1. Load SENT Executions ​

  • Source: outbound_payment_execution where execution_status_cd = 'SENT'.
  • Action: SELECT (read-only).
  • Logic:
    • When executionId is provided, filter to that single record.
    • When omitted, load all executions in SENT status.

Step 2. Check Adapter Polling Capability ​

  • Source: outbound_payment_execution.payment_schema for each execution.
  • Action: Computation (no database operation).
  • Logic:
    • If the adapter for payment_schema does not support status polling (statusPolling = false): skip this execution.
    • If bank_reference_id is null: skip this execution.

Step 3. Build and Send Status Request ​

  • Source: outbound_payment_execution.bank_reference_id for each eligible execution.
  • Action: External HTTP call (bank status API).
  • Logic:
    • Use the adapter's buildStatusRequest method with the bank_reference_id.
    • Send the status inquiry to the bank's status endpoint.
    • Parse the bank response using the adapter's parseStatusResponse method.

Step 4. Update Execution Record ​

  • Source: Bank response from Step 3.
  • Action: UPDATE outbound_payment_execution.
  • Logic:
    • Map bank-specific status to standardized execution status: COMPLETED → 'ACKNOWLEDGED'; FAILED or REVERSED → 'FAILED'; PENDING or PROCESSING → remain 'SENT'.
    • Set execution_status_cd = mapped status.
    • Set poll_count = incremented by 1.
    • Set last_polled_at = current timestamp.
    • Append to status_history (JSONB): { pollNumber, bankStatus, mappedStatus, timestamp, details }.

Step 5. Propagate Terminal Status to Payment Item ​

  • Source: The status change computed in Step 4.
  • Action: UPDATE payment_item when a terminal status is reached.
  • Logic:
    • If the mapped status changed and reached a terminal state:
      • Bank status COMPLETED: set payment_item.payment_execution_status_cd = 'PAID'.
      • Bank status FAILED or REVERSED: set payment_item.payment_execution_status_cd = 'FAILED'.
      • Bank status PENDING or PROCESSING: no change to payment item (remains 'SENT').

**PoC Artifact:** The bank transport layer in the PoC is a stub that uses "magic numbers" (specific cent values like `.50`, `.99`) to simulate different bank behaviors. Production will replace the transport layer stub with actual HTTP clients for each bank.

Side-effects:

  • outbound_payment_execution.poll_count, last_polled_at, status_history, and execution_status_cd are updated for each polled execution.
  • payment_item.payment_execution_status_cd is updated when the execution reaches ACKNOWLEDGED or FAILED.

Postconditions:

  • Each polled execution has an updated status_history entry reflecting the latest poll.
  • Payment items whose bank status reached a terminal state (PAID or FAILED) have their payment_execution_status_cd updated accordingly.

2.7 Void & Cancellation ​

2.7.1 Void Payment Items on Worksheet Return ​

Operation: voidPaymentItemsForWorksheetReturn

Trigger: Worksheet return procedure initiates for an Approved worksheet.

Input Parameters:

  • worksheetId: Integer — the cash_receipt_worksheet_id being returned
  • actorId: String — identifier for the user performing the operation

When a worksheet is returned from Approved status, unlocked payment items are voided and locked ones are preserved as read-only context on the replacement draft.

Step 1. Identify All Payment Items for the Worksheet ​

  • Source: cash_receipt_payout where cash_receipt_worksheet_id = worksheetId and payment_item_id IS NOT NULL.
  • Action: SELECT (read-only).
  • Logic:
    • Collect all payment_item_id values linked to payouts on this worksheet.

Step 2. Classify Each Payment Item ​

  • Source: payment_item records from Step 1.
  • Action: SELECT (read-only).
  • Logic:
    • Locked: payment_execution_status_cd IN ('PROCESSING', 'SENT', 'ACKNOWLEDGED', 'PAID') — preserve as-is. These are copied to the replacement draft as read-only context.
    • Unlocked: All other statuses (WAITING, PENDING, FAILED, CANCELLED, NULL) — void these.

Step 3. Void Unlocked Payment Items ​

  • Source: Unlocked payment items from Step 2.
  • Action: UPDATE payment_item for each unlocked item.
  • Logic:
    • Set payment_item_posting_status_cd = 'X' (Skipped — payment was never posted to GL, so no GL reversal needed).
    • Set payment_execution_status_cd = 'CANCELLED'.
    • Set return_reason_cd = 'WORKSHEET_RETURN'.
    • Set returned_dt = current timestamp.
    • Set returned_by_user_id = actorId.

WARNING

Voided payment items must have payment_item_posting_status_cd = 'X' (Skipped), not 'U' (Unposted). Setting them to 'X' prevents the GL posting job from ever picking them up. These payments were never sent, so no GL entry was ever posted, and no GL reversal is needed.

Side-effects:

  • Settlement statuses are set to Returned by the separate returnSettlementsForWorksheet procedure (section 2.4.3).

Postconditions:

  • Unlocked payment items have payment_execution_status_cd = 'CANCELLED' and payment_item_posting_status_cd = 'X'.
  • Locked payment items are unchanged.
  • The worksheet return three-document model continues: original sealed as Returned, reversal worksheet created, replacement draft created with only locked items as read-only context.

2.7.2 Delete Settlement ​

Operation: deleteSettlement

Trigger: User clicks "Delete" on a settlement in the worksheet.

Input Parameters:

  • settlementId: Integer — the participant_settlement_id to delete
  • actorId: String — identifier for the user performing the operation

Removes a settlement and all its dependent records. Blocked if any linked payment items are locked.

IMPORTANT

Steps 3 through 8 must execute in this order to respect foreign key constraints. Payment items must be voided before settlement items are deleted. Payout records must be deleted before settlement items (FK dependency). Applications must be unlinked before the settlement header is deleted.

Step 1. Load Settlement Items ​

  • Source: participant_settlement_item where participant_settlement_id = settlementId.
  • Action: SELECT (read-only).
  • Logic:
    • Collect all participant_settlement_item_id values.

Step 2. Check for Locked Payments ​

  • Source: payment_item where participant_settlement_item_id matches the collected item IDs.
  • Action: SELECT (read-only).
  • Logic:
    • Exclude voided payment items (payment_item_posting_status_cd = 'X').
    • If any non-voided payment item has payment_execution_status_cd IN ('PROCESSING', 'SENT', 'ACKNOWLEDGED', 'PAID'): abort with error "Cannot delete settlement with locked payment items."

Step 3. Void Unlocked Payment Items ​

  • Source: Non-voided payment items linked to this settlement's items.
  • Action: UPDATE payment_item.
  • Logic:
    • Set payment_item_posting_status_cd = 'X' (Skipped).
    • Set payment_execution_status_cd = 'CANCELLED'.
    • Set return_reason_cd = 'SETTLEMENT_DELETE'.
    • Set returned_dt = current timestamp.
  • Source: participant_settlement_item records for this settlement.
  • Action: UPDATE participant_settlement_item.
  • Logic:
    • Set payment_item_id = NULL for all items.

Step 5. Delete Payout Records ​

  • Source: cash_receipt_payout where participant_settlement_item_id matches each settlement item ID.
  • Action: DELETE from cash_receipt_payout.
  • Source: cash_receipt_application where participant_settlement_id = settlementId.
  • Action: UPDATE cash_receipt_application.
  • Logic:
    • Set participant_settlement_id = NULL.

Step 7. Delete Settlement Items ​

  • Source: participant_settlement_item where participant_settlement_id = settlementId.
  • Action: DELETE from participant_settlement_item.

Step 8. Delete Settlement Header ​

  • Source: participant_settlement where participant_settlement_id = settlementId.
  • Action: DELETE from participant_settlement.

Side-effects:

  • All unlocked payment items linked to this settlement are voided with payment_execution_status_cd = 'CANCELLED'.
  • cash_receipt_application.participant_settlement_id is cleared for all previously linked applications.

Postconditions:

  • No participant_settlement, participant_settlement_item, or cash_receipt_payout records exist for this settlement.
  • Previously linked cash_receipt_application records have participant_settlement_id = NULL.

2.7.3 Update Payment Item Do-Not-Send Flag ​

Operation: updateDoNotSendFlag

Trigger: User toggles the do-not-send flag on an individual payment item or in bulk on the Payments page or worksheet payout grid.

Input Parameters:

  • paymentItemIds: int[] — one or more payment_item_id values to update
  • doNotSendInd: Boolean — the new flag value
  • actorId: String — identifier for the user performing the operation

Toggles the do_not_send_ind flag on one or more payment items to hold or release them from bank transmission.

Step 1. Update Payment Item Flag ​

  • Source: payment_item records matching paymentItemIds.
  • Action: UPDATE payment_item.
  • Logic:
    • Set do_not_send_ind = doNotSendInd.
    • Set updated_by = actorId, updated_dt = current timestamp.

NOTE

Setting do_not_send_ind = true on a PENDING payment item does not automatically change its status to WAITING. The status transition occurs when the payment processor checks eligibility. Similarly, setting the flag to false on a WAITING item does not auto-transition to PENDING — this requires a separate eligibility check.

Side-effects:

  • None beyond the flag update.

Postconditions:

  • payment_item.do_not_send_ind reflects the new value for all specified payment items.

3. Business Rules & Logic ​

3.1 Locked Payment Rule ​

Business rule: Once a payment item has been transmitted to the bank, it cannot be reversed, voided, or modified. A single locked payment item locks its entire dependency chain.

Data-level enforcement:

  • Read: payment_item.payment_execution_status_cd — locked statuses are 'PROCESSING', 'SENT', 'ACKNOWLEDGED', 'PAID'.
  • Guard: If any payment item in the chain is in a locked status, reject the modification, void, or reversal with an appropriate error.
  • Write: The locked dependency chain includes: the participant_settlement_item linked via participant_settlement_item_id; the participant_settlement header (if any item within it is locked, the entire settlement is locked); the cash_receipt_application records linked via participant_settlement_id; the cash_receipt_payout record linked via participant_settlement_item_id; and the corresponding REV application (bilateral locking).
  • Statuses considered unlocked: 'WAITING', 'PENDING', 'FAILED', 'CANCELLED', and NULL.

3.2 Two-Stage Payment Lifecycle ​

Business rule: GL posting for payments only occurs after the bank confirms the payment has been processed. A payment that was never sent must never post a GL entry.

Data-level enforcement:

  • Read: payment_item.payment_execution_status_cd and payment_item.payment_item_posting_status_cd.
  • Guard: GL posting (transition of payment_item_posting_status_cd from 'U' to 'P') is only permitted when payment_execution_status_cd is 'ACKNOWLEDGED' or 'PAID'.
  • Write: If a payment item is voided before being sent (cancelled at 'PENDING' or 'WAITING'), set payment_item_posting_status_cd = 'X' (Skipped). Setting to 'X' prevents the GL posting job from picking it up. Do not set it to 'P' (Posted) — posting a reversal for a never-posted transaction creates a GL imbalance.

3.3 Settlement Must Cover All PAY Applications ​

Business rule: A worksheet cannot transition to Settled unless every PAY application has a settlement and all settlement payouts balance against total PAY applied.

Data-level enforcement:

  • Read: Query all cash_receipt_application on the worksheet where billing_item_detail.billing_item_detail_type_cd = 'PAY'. Check that each has a non-null participant_settlement_id.
  • Guard: If any PAY application has participant_settlement_id = NULL, reject the Settle transition.
  • Guard: Sum all cash_receipt_payout.payment_item_amt where payment_item_type_cd = 'S' for the worksheet. Sum all cash_receipt_application.cash_receipt_amt_applied for PAY applications. Assert |payout_total - pay_applied_total| <= 0.005. If not satisfied, reject the Settle transition.

3.4 Commission Calculation ​

Business rule: Settlement item amounts can be entered as a flat dollar value or calculated as a percentage of the applicable base amount. The base amount depends on whether deductions are included.

Data-level enforcement:

  • Read: participant_settlement_item.participant_settlement_commission_flat_ind and participant_settlement_item.calc_level_cd.
  • Write (percentage mode): participant_settlement_commission_flat_ind = false. Compute participant_settlement_commission_amt = base_amount * (participant_settlment_commission_perc / 100).
    • If calc_level_cd = 'DNI' (Do Not Ignore Deductions): base_amount = PAY applied amount minus PAY deductions (net). This is the default.
    • If calc_level_cd = 'IGN' (Ignore Deductions): base_amount = PAY applied amount before deductions (gross).
  • Write (flat mode): participant_settlement_commission_flat_ind = true. participant_settlement_commission_amt = directly entered value; participant_settlment_commission_perc is informational only.

3.5 Same Revenue Item Constraint ​

Business rule: All receivables selected for a single settlement must belong to the same revenue item (and therefore the same deal).

Data-level enforcement:

  • Read: billing_item.revenue_item_id for each billing_item_detail_id in the input.
  • Guard: If any resolved revenue_item_id differs from the others, reject the settlement creation with error: "All selected receivables must belong to the same Revenue Item."
  • This ensures deal party information is consistent across all items in the settlement.

3.6 Adapter Selection (Schema-Based) ​

Business rule: The outbound payment adapter is determined by a code attribute on the bank, not by hard-coded bank identity.

Data-level enforcement:

  • Read: Resolve payment_item.payment_party_bank_id → bank_account.bank_id.
  • Read: Query code_attribute where code_master_type = 'BANK', code = bank_id, attribute = 'PAYMENT_REQUEST_SCHEMA'. The value column is one of: CNB_EASI_LINK, ISO20022_PAIN001, BOFA_CASHPRO, JPM_GLOBAL_PAY.
  • Guard: If no matching code attribute is found, reject with error.
  • This design means new banks can be onboarded by configuring a code attribute rather than writing new adapter code, as long as the bank supports one of the existing schemas.

3.7 Service Level Determination ​

Business rule: Whether a payment is sent as WIRE or ACH is determined by the payee's bank account preference.

Data-level enforcement:

  • Read: payment_item.payment_party_bank_id → party_bank_account.preferred_payment_method.
  • Write: If preferred_payment_method = 'WIRE', use wire transfer; otherwise (including NULL), default to 'ACH'.
  • The service level maps to bank-specific payload codes: WIRE = URGP (both CNB EASI Link and ISO 20022); ACH = NURG + CCD (or PPD for individuals).

3.8 Zero-Amount Handling ​

Business rule: Zero-amount settlements items, payouts, and payment items must not be created or carried forward.

Data-level enforcement:

  • Write (settlement items): Items with participant_settlement_commission_amt = 0 are skipped during insert. During update, an existing item whose amount changes to zero is treated as a removal and deleted.
  • Write (payouts): Payouts with payment_item_amt = 0 are not created during payout generation.
  • Write (payment items): Payment items are not created for zero-amount payouts.

4. Field Mapping & Transformation ​

4.1 Settlement Item to Payout (participant_settlement_item → cash_receipt_payout) ​

When a cash_receipt_payout record of type 'S' is created from a participant_settlement_item:

Source Table.FieldTarget Table.FieldTransform
—cash_receipt_payout.cash_receipt_worksheet_idFrom worksheet context
participant_settlement_item.payment_party_idcash_receipt_payout.payout_party_idCopied as-is
participant_settlement_item.payment_party_bank_idcash_receipt_payout.payment_party_bank_idCopied as-is
participant_settlement_item.participant_settlement_item_idcash_receipt_payout.participant_settlement_item_idCopied as-is
billing_item.deal_id (from linked application)cash_receipt_payout.deal_idCopied from billing item context
billing_item.buyer_idcash_receipt_payout.buyer_idCopied from billing item context
billing_item.uta_entity_idcash_receipt_payout.uta_entity_idCopied from billing item context
billing_item.department_idcash_receipt_payout.department_idCopied from billing item context
participant_settlement_item.participant_settlement_item_commentcash_receipt_payout.payment_item_nameCopied as-is (or system default)
—cash_receipt_payout.payment_item_type_cdDefaulted to 'S'
participant_settlement_item.participant_settlement_commission_amtcash_receipt_payout.payment_item_amtCopied as-is
—cash_receipt_payout.payment_item_currency_cdDefaulted to 'USD' (or from billing item)
participant_settlement_item.payment_datecash_receipt_payout.payment_dateCopied as-is
participant_settlement_item.do_not_send_indcash_receipt_payout.do_not_send_indCopied as-is
—cash_receipt_payout.payment_item_idDefaulted to NULL (populated at approval)

4.2 Payout to Payment Item (cash_receipt_payout → payment_item) ​

When a payment_item is created from a cash_receipt_payout at worksheet approval:

Source Table.FieldTarget Table.FieldTransform
cash_receipt_payout.payment_item_type_cdpayment_item.payment_item_type_cdCopied as-is (default 'P' if null)
cash_receipt_payout.payment_item_namepayment_item.payment_item_nameCopied as-is (default 'Commission Payment' if null)
cash_receipt_payout.payout_party_idpayment_item.payment_party_idCopied as-is
cash_receipt_payout.payment_party_bank_id (then participant_settlement_item.payment_party_bank_id)payment_item.payment_party_bank_idPayout field first; settlement item as fallback
cash_receipt_payout.participant_settlement_item_idpayment_item.participant_settlement_item_idCopied as-is
cash_receipt_payout.payment_item_amtpayment_item.payment_item_amtCopied as-is
cash_receipt_payout.payment_item_currency_cdpayment_item.payment_item_currency_cdCopied as-is (default 'USD')
participant_settlement_item.participant_settlement_item_commentpayment_item.payment_item_commentFrom settlement item
—payment_item.payment_item_posting_status_cdDefaulted to 'U' (Unposted)
—payment_item.payment_execution_status_cd'WAITING' if future date or do_not_send_ind = true; else 'PENDING'
—payment_item.payment_clearing_status_indDefaulted to true
cash_receipt_payout.payment_datepayment_item.payment_dateCopied as-is
cash_receipt_payout.deal_id (then billing_item.deal_id)payment_item.deal_idPayout field first; billing item as fallback
billing_item.client_id (then cash_receipt_payout.payout_party_id)payment_item.client_idBilling item first; payout party as fallback for passthroughs
cash_receipt_payout.buyer_id (then billing_item.buyer_id)payment_item.buyer_idPayout field first; billing item as fallback
billing_item.contracted_party_idpayment_item.contracted_party_idFrom billing item only
cash_receipt_payout.department_id (then billing_item.department_id)payment_item.department_idPayout field first; billing item as fallback
cash_receipt_payout.uta_entity_id (then billing_item.uta_entity_id)payment_item.uta_entity_idPayout field first; billing item as fallback
cash_receipt.bank_account_idpayment_item.source_account_idFrom the receipt's bank account (UTA's receiving account)
cash_receipt_payout.do_not_send_indpayment_item.do_not_send_indCopied as-is

4.3 Payment Item to Outbound Execution (payment_item → outbound_payment_execution) ​

When an outbound_payment_execution record is created for a payment item:

Source Table.FieldTarget Table.FieldTransform
payment_item.payment_item_idoutbound_payment_execution.payment_item_idCopied as-is
—outbound_payment_execution.bank_profile_idDefaulted to 0 (legacy field, not used for routing)
Lookup from bank_account.bank_idoutbound_payment_execution.bank_profile_nameHuman-readable bank name from lookup
—outbound_payment_execution.execution_status_cdDefaulted to 'CREATED'
From adapteroutbound_payment_execution.payload_format'JSON' or 'XML' depending on adapter
From code_attribute lookupoutbound_payment_execution.payment_schemaCNB_EASI_LINK, ISO20022_PAIN001, BOFA_CASHPRO, or JPM_GLOBAL_PAY
payment_item.payment_date (or current date)outbound_payment_execution.requested_execution_dateCopied as-is; defaulted to today if null
payment_item.payment_item_amtoutbound_payment_execution.payment_amountSnapshot at time of execution
payment_item.payment_item_currency_cdoutbound_payment_execution.payment_currencySnapshot at time of execution
party_bank_account.preferred_payment_methodoutbound_payment_execution.service_level'WIRE' or 'ACH'
Built by adapter before transmissionoutbound_payment_execution.generated_payloadSaved before transport is attempted

NOTE

The outbound_payment_execution table stores snapshots of amount, currency, and execution date at the time of transmission. These snapshots do not change if the parent payment_item is later modified, providing a reliable audit trail of what was actually sent to the bank.

5. Cross-References ​

DocumentRelationship
Settlements Data ModelTable definitions, field types, status lifecycle diagrams for participant_settlement, participant_settlement_item, payment_item, and outbound_payment_execution. Code master values for PARTICIPANT_SETTLEMENT_STATUS_CD, PAYMENT_ITEM_TYPE_CD, POSTING_STATUS_CD, CALC_LEVEL_CD. Decimal precision rules.
Settlements QueriesRead-only queries used to populate settlement forms, payment item lists, execution history, and lock-status checks.
Worksheets Data ModelWorksheet status transitions drive when settlement procedures are invoked. Draft → Applied creates context for settlement creation. Applied → Settled triggers section 2.4.1. Settled → Approved triggers section 2.4.2 and then section 2.3.1. Worksheet return triggers section 2.7.1 and section 2.4.3. cash_receipt_application.participant_settlement_id links applications to settlements. cash_receipt_payout bridges the worksheet to payment items.
Billing Items Data Modelbilling_item and billing_item_detail provide receivable context for settlements. The deal_id, client_id, buyer_id, uta_entity_id, and department_id from billing_item propagate through the payout to the payment item. billing_item_deduction and cash_receipt_application_deduction affect commission calculations via calc_level_cd.
Parties Data Modeldeal_party provides default commission percentages and party assignments when computing settlement defaults. party_bank_account.preferred_payment_method determines WIRE vs ACH. bank_account.bank_id drives adapter selection via code_attribute.
Accounting Data ModelGL posting occurs only after a payment item reaches ACKNOWLEDGED or PAID status (Stage 2). payment_item.payment_item_posting_status_cd and posting_dt track this. Cancelled payments use payment_item_posting_status_cd = 'X' to indicate no GL entry was ever posted.

Confidential. For internal use only.

Copyright © 2025, United Talent Agency. All rights reserved.