Client Framework

Appearance

Worksheets Procedures ​

1. Executive Summary ​

Purpose ​

This document describes every data mutation operation in the worksheet domain — the step-by-step procedures that create, update, reverse, and delete records across the cash_receipt_worksheet, cash_receipt_application, cash_receipt_application_deduction, cash_receipt_client_ledger, and cash_receipt_payout tables. It serves as the definitive reference for implementing the worksheet lifecycle (Draft, Applied, Settled, Approved, Returned) and all associated sub-operations including receivable application, client ledger application, payout management, deduction handling, and the three-document return process.

Scope ​

Covered:

  • Worksheet creation from a cash receipt split
  • Adding, updating, and removing receivable applications (cash_receipt_application)
  • Adding, updating, and removing client ledger applications (cash_receipt_client_ledger)
  • Adding, updating, and removing payout records (cash_receipt_payout)
  • Application deduction management (cash_receipt_application_deduction)
  • Status transitions: Draft to Applied, Applied to Settled, Settled to Approved
  • Reverse status transitions (rejections): Settled to Applied, Applied to Draft
  • The return (reopen) process from Approved status producing three documents
  • Worksheet abandonment and deletion
  • Locking, classification, and bilateral lock rules for sent payments
  • Balance and total applied calculations
  • Billing item closure on approval

Not covered (documented separately):

2. Key Procedures ​

2.1 Create Worksheet ​

Operation: createWorksheet

Trigger: User with CASH_MANAGER or IT role initiates worksheet creation for a cash receipt split.

Input Parameters:

  • cashReceiptSplitId: Integer — cash_receipt_split.cash_receipt_split_id
  • userId: String — identifier for the authenticated user performing the operation

Creates a new Draft worksheet linked to a specific cash receipt split and locks the parent receipt for editing.

Step 1. Validate Split Exists ​

  • Source: cash_receipt_split where cash_receipt_split_id = input cashReceiptSplitId.
  • Action: SELECT from cash_receipt_split.
  • Logic:
    • If no row is found, abort with error "Cash receipt split not found".

Step 2. Validate Receipt Eligibility ​

  • Source: cash_receipt via cash_receipt_split.cash_receipt_id.
  • Action: SELECT from cash_receipt.
  • Logic:
    • If cash_receipt.posting_status_cd = 'V', abort: "Cannot create worksheet for a voided cash receipt".
    • If cash_receipt.posting_status_cd = 'P', abort: "Cannot create worksheet for a posted cash receipt".

Step 3. Check for Existing Active Worksheet ​

  • Source: cash_receipt_worksheet where cash_receipt_split_id = input and current_item_ind = true.
  • Action: SELECT from cash_receipt_worksheet.
  • Logic:
    • If a record is found, abort: "Active worksheet already exists for this cash receipt split".

Step 4. Check Receipt Lock ​

  • Source: cash_receipt.locked_by_user_id.
  • Action: Read lock field from cash_receipt.
  • Logic:
    • If locked_by_user_id is non-null and does not match userId, abort: "This receipt is currently being worked on by another user".

Step 5. Lock the Receipt ​

  • Source: cash_receipt record from Step 2.
  • Action: UPDATE cash_receipt.
  • Logic:
    • Set cash_receipt.locked_by_user_id = userId.

Step 6. Create Worksheet ​

  • Source: Validated cashReceiptSplitId from Step 1.
  • Action: INSERT into cash_receipt_worksheet.
  • Logic:
    • Set cash_receipt_split_id = input cashReceiptSplitId.
    • Set cash_receipt_worksheet_status_cd = 'D' (Draft).
    • Set current_item_ind = true.
    • Set created_by = userId, updated_by = userId, created_dt = now, updated_dt = now.

Side-effects:

  • Sets cash_receipt.locked_by_user_id to userId, preventing concurrent worksheet creation by another user.

Postconditions:

  • A new cash_receipt_worksheet record exists with cash_receipt_worksheet_status_cd = 'D' and current_item_ind = true.
  • The parent cash_receipt is locked by userId.

2.2 Get or Create Worksheet ​

Operation: getOrCreateWorksheet

Trigger: User opens a cash receipt for editing; the system finds or creates the current worksheet automatically.

Input Parameters:

  • cashReceiptId: Integer — cash_receipt.cash_receipt_id
  • userId: String — identifier for the authenticated user

Finds the existing current worksheet for a cash receipt, or creates one using the default split. Ensures the receipt is locked for the current user.

Step 1. Check for Existing Worksheet ​

  • Source: cash_receipt_worksheet joined to cash_receipt_split joined to cash_receipt, where cash_receipt_id = input and current_item_ind = true.
  • Action: SELECT from cash_receipt_worksheet.
  • Logic:
    • If found, validate that cash_receipt.locked_by_user_id is null or matches userId; if another user holds the lock, abort.
    • If found and lock is clear, set cash_receipt.locked_by_user_id = userId and return the existing worksheet.

Step 2. Find Default Split ​

  • Source: cash_receipt_split where cash_receipt_id = input.
  • Action: SELECT from cash_receipt_split.
  • Logic:
    • If no split exists, INSERT a new cash_receipt_split with split_amt = '0' and split_sequence = 1.

Step 3. Delegate to Create Worksheet ​

  • Source: cash_receipt_split_id from Step 2.
  • Action: Delegate to Create Worksheet using the found or newly created cash_receipt_split_id.
  • Logic:

Side-effects:

  • May INSERT a new cash_receipt_split if none exists.
  • Locks the parent cash_receipt by setting locked_by_user_id = userId.

Postconditions:

  • A current worksheet exists for the receipt and is accessible to userId.
  • The parent cash_receipt is locked by userId.

2.3 Add Receivable Application ​

Operation: addApplication

Trigger: User selects a billing item on the worksheet and enters an amount to apply.

Input Parameters:

  • cashReceiptWorksheetId: Integer — cash_receipt_worksheet.cash_receipt_worksheet_id
  • billingItemDetailId: Integer — billing_item_detail.billing_item_detail_id
  • cashReceiptAmtApplied: Decimal(15,2) — amount to apply (transmitted as string)
  • deductionAmtApplied: Decimal(15,2), optional — deduction amount (transmitted as string)
  • userId: String — identifier for the authenticated user
  • skipTotalValidation: Boolean, optional — bypass total validation during batch operations

Creates a cash_receipt_application record linking a worksheet to a billing item detail (REV or PAY).

Step 1. Validate Worksheet Status ​

  • Source: cash_receipt_worksheet where cash_receipt_worksheet_id = input.
  • Action: SELECT from cash_receipt_worksheet.
  • Logic:
    • If cash_receipt_worksheet_status_cd != 'D', abort: "Cannot modify worksheet in Submitted or Approved status".

Step 2. Validate Billing Item Detail Exists ​

  • Source: billing_item_detail joined to billing_item where billing_item_detail_id = input.
  • Action: SELECT from billing_item_detail.
  • Logic:
    • If no row found, abort: "Billing item not found".

Step 3. Validate Currency Match ​

  • Source: cash_receipt via the worksheet → split → receipt chain.
  • Action: Compare billing_item.billing_item_currency_cd to cash_receipt.currency_cd.
  • Logic:
    • If currencies differ, abort with "Currency mismatch: Cash receipt is <X>, billing item is <Y>".

Step 4. Validate Total Does Not Exceed Receipt Amount ​

  • Source: Computed sum of all cash_receipt_application.cash_receipt_amt_applied + cash_receipt_client_ledger.cash_receipt_amt_applied + non-settlement cash_receipt_payout.payment_item_amt + cash_receipt_application_deduction.deduction_amt_applied for the worksheet.
  • Action: Compare currentTotal + newAmount to cash_receipt.net_receipt_amt.
  • Logic:
    • If currentTotal + newAmount > net_receipt_amt + 0.005, abort: over-application.
    • If skipTotalValidation = true, skip this step entirely.

Step 5. Insert Application ​

  • Source: Validated input parameters.
  • Action: INSERT into cash_receipt_application.
  • Logic:
    • Set cash_receipt_worksheet_id = input.
    • Set billing_item_detail_id = input.
    • Set cash_receipt_amt_applied = input amount.
    • Set created_by = userId, updated_by = userId.

Step 6. Insert Deduction (Conditional) ​

  • Source: billing_item_deduction where billing_item_detail_id = input, to determine deduction type.
  • Action: INSERT into cash_receipt_application_deduction (only if deductionAmtApplied is provided and non-zero).
  • Logic:
    • Set cash_receipt_application_id = newly created application ID.
    • Set deduction_amt_applied = input deduction amount.
    • Set billing_item_deduction_type_cd = first matching type from billing item deductions if available.

NOTE

Negative application amounts are permitted to allow credit adjustments. Over-application to individual billing item details is allowed; the only constraint is the worksheet total against the receipt amount.

Side-effects:

  • None beyond the inserted records.

Postconditions:

  • A new cash_receipt_application record exists linked to the worksheet and billing item detail.
  • If a deduction was provided, a corresponding cash_receipt_application_deduction record exists.

2.4 Add Receivable (REV + PAY Pair) ​

Operation: addReceivable

Trigger: User adds a billing item to the worksheet; both REV and PAY sides are applied in a single operation.

Input Parameters:

  • worksheetId: Integer — cash_receipt_worksheet.cash_receipt_worksheet_id
  • billingItemId: Integer — billing_item.billing_item_id
  • revDetailId: Integer, optional — billing_item_detail.billing_item_detail_id for the REV detail
  • payDetailId: Integer, optional — billing_item_detail.billing_item_detail_id for the PAY detail
  • revAmount: Decimal(15,2) — REV amount to apply
  • payAmount: Decimal(15,2) — PAY amount to apply
  • revDeductionAmount: Decimal(15,2), optional — REV deduction
  • payDeductionAmount: Decimal(15,2), optional — PAY deduction

Creates both REV and PAY application records for a single billing item in one atomic operation.

Step 1. Apply REV Side ​

  • Source: revDetailId and revAmount from input.
  • Action: Delegate to Add Receivable Application with REV parameters.
  • Logic:
    • Execute only if revAmount >= 0 and revDetailId is provided.
    • Total validation runs for the REV application.

Step 2. Apply PAY Side ​

  • Source: payDetailId and payAmount from input.
  • Action: Delegate to Add Receivable Application with PAY parameters.
  • Logic:
    • Execute only if payAmount >= 0 and payDetailId is provided.
    • Set skipTotalValidation = true because the REV application in Step 1 already validated. Both REV and PAY are a single logical add.

NOTE

Zero amounts are allowed to add an item to the worksheet without applying cash. This supports scenarios where the user wants to track a receivable on the worksheet before deciding on amounts.

Side-effects:

Postconditions:

  • Up to two cash_receipt_application records exist (one REV, one PAY) for the billing item.

2.5 Update Receivable Application ​

Operation: updateApplication

Trigger: User changes the amount on an existing receivable application row in the worksheet.

Input Parameters:

  • cashReceiptApplicationId: Integer — cash_receipt_application.cash_receipt_application_id
  • cashReceiptAmtApplied: Decimal(15,2) — new amount (transmitted as string)
  • deductionAmtApplied: Decimal(15,2), optional — new deduction amount
  • userId: String — identifier for the authenticated user
  • skipTotalValidation: Boolean, optional — bypass total validation during batch operations

Updates the applied amount on an existing cash_receipt_application record.

Step 1. Validate Application Exists ​

  • Source: cash_receipt_application where cash_receipt_application_id = input.
  • Action: SELECT from cash_receipt_application.
  • Logic:
    • If no row found, abort.

Step 2. Check Lock Status ​

  • Source: participant_settlement → participant_settlement_item → payment_item chain linked to the application.
  • Action: Evaluate payment_item.payment_execution_status_cd for all linked payment items.
  • Logic:
    • If any linked payment_item.payment_execution_status_cd is in ['PROCESSING', 'SENT', 'PAID', 'ACKNOWLEDGED'], the application is locked.
    • If locked, abort.

Step 3. Validate Worksheet Is in Draft ​

  • Source: Parent cash_receipt_worksheet.
  • Action: SELECT cash_receipt_worksheet_status_cd.
  • Logic:
    • cash_receipt_worksheet_status_cd must be 'D'. Otherwise abort.

Step 4. Validate Total Does Not Exceed Receipt Amount ​

  • Source: Computed worksheet total (same as Add Receivable Application Step 4), accounting for the difference between old and new amounts.
  • Action: Compare new projected total to cash_receipt.net_receipt_amt.
  • Logic:
    • Skip if skipTotalValidation = true.

Step 5. Update Application ​

  • Source: Validated cashReceiptApplicationId.
  • Action: UPDATE cash_receipt_application.
  • Logic:
    • Set cash_receipt_amt_applied = new amount.
    • Set updated_by = userId, updated_dt = now.

Step 6. Update Deductions (Conditional) ​

  • Source: Existing cash_receipt_application_deduction records for this application.
  • Action: DELETE existing deductions; conditionally INSERT new ones.
  • Logic:
    • Delete all cash_receipt_application_deduction where cash_receipt_application_id = input.
    • If new deductionAmtApplied is non-zero:
      • Fetch billing_item_deduction records for the application's billing_item_detail_id.
      • If a single deduction type: insert one cash_receipt_application_deduction with the full amount.
      • If multiple deduction types: prorate across types. For each type: proportion = typeBalance / totalBalance, proratedAmount = deductionAmount * proportion. Insert one row per type.

Side-effects:

  • All prior deductions for the application are deleted and replaced.

Postconditions:

  • cash_receipt_application.cash_receipt_amt_applied reflects the new amount.
  • cash_receipt_application_deduction records reflect the new deduction, if provided.

2.6 Remove Receivable Application ​

Operation: removeApplication

Trigger: User removes a receivable application row from the worksheet.

Input Parameters:

  • applicationId: Integer — cash_receipt_application.cash_receipt_application_id
  • userId: String — identifier for the authenticated user

Deletes a cash_receipt_application record and its dependent deductions.

Step 1. Validate Application Exists ​

  • Source: cash_receipt_application where cash_receipt_application_id = input.
  • Action: SELECT from cash_receipt_application.
  • Logic:
    • If no row found, abort.

Step 2. Validate Worksheet Is in Draft ​

  • Source: Parent cash_receipt_worksheet.
  • Action: SELECT cash_receipt_worksheet_status_cd.
  • Logic:
    • cash_receipt_worksheet_status_cd must be 'D'. Otherwise abort.

Step 3. Check Lock Status ​

Step 4. Check Bilateral Lock on REV Applications ​

  • Source: Paired PAY application on the same billing_item_id with the same participant_settlement_id.
  • Action: Evaluate is_read_only state of the paired PAY application.
  • Logic:
    • If this is a REV application and its positionally paired PAY application is locked (read-only), abort: cannot remove REV when its paired PAY is locked.

Step 5. Delete Deductions ​

  • Source: cash_receipt_application_deduction where cash_receipt_application_id = input.
  • Action: DELETE from cash_receipt_application_deduction.
  • Logic:
    • Remove all child deduction records before deleting the parent.

Step 6. Delete Application ​

  • Source: cash_receipt_application where cash_receipt_application_id = input.
  • Action: DELETE from cash_receipt_application.
  • Logic:
    • Remove the application record.

Side-effects:

  • All associated cash_receipt_application_deduction records are deleted.

Postconditions:

  • The application and all its deductions no longer exist.

2.7 Add Client Ledger Application ​

Operation: addClientLedgerApplication

Trigger: User applies cash to a client on-account ledger entry rather than a specific receivable.

Input Parameters:

  • cashReceiptWorksheetId: Integer — cash_receipt_worksheet.cash_receipt_worksheet_id
  • clientLedgerId: Integer — client_ledger.client_ledger_id
  • dealId: Integer, optional — deal.deal_id
  • utaEntityId: Integer, optional — uta_entity.uta_entity_id
  • departmentId: Integer, optional — department.department_id
  • clientId: Integer, optional — party.party_id
  • cashReceiptAmtApplied: Decimal(15,2) — amount applied (transmitted as string)
  • userId: String — identifier for the authenticated user

Creates a cash_receipt_client_ledger record on a worksheet.

Step 1. Insert Client Ledger Application ​

  • Source: Validated input parameters.
  • Action: INSERT into cash_receipt_client_ledger.
  • Logic:
    • Set cash_receipt_worksheet_id = input.
    • Set client_ledger_id = input.
    • Set deal_id = input (nullable).
    • Set uta_entity_id = input (nullable).
    • Set department_id = input (nullable).
    • Set client_id = input (nullable).
    • Set cash_receipt_amt_applied = input amount.
    • Set created_by = userId, updated_by = userId.

Side-effects:

  • None beyond the inserted record.

Postconditions:

  • A new cash_receipt_client_ledger record exists linked to the worksheet and client ledger.

2.8 Create On-Account Client Ledger ​

Operation: createOnAccountClientLedger

Trigger: User creates a new on-account ledger entry and immediately links it to the worksheet.

Input Parameters:

  • clientId: Integer — party.party_id
  • clientLedgerName: String — descriptive name for the ledger entry
  • worksheetId: Integer — cash_receipt_worksheet.cash_receipt_worksheet_id
  • dealId: Integer, optional — deal.deal_id
  • buyerId: Integer, optional — party.party_id
  • utaEntityId: Integer, optional — uta_entity.uta_entity_id
  • departmentId: Integer, optional — department.department_id

Creates a new client_ledger record and immediately adds a cash_receipt_client_ledger application for it.

Step 1. Create Client Ledger Entry ​

  • Source: Input parameters.
  • Action: INSERT into client_ledger.
  • Logic:
    • Set client_id = input.
    • Set contracted_party_id = input clientId.
    • Set client_ledger_type_cd = 'OA' (On-Account).
    • Set client_ledger_status_cd = 'C' (Current/closed initial state).
    • Set client_ledger_name = input name.
    • Set client_ledger_amt = '0.00'.
    • Set client_ledger_currency_cd = 'USD'.
    • Set client_ledger_open_item_ind = true.
    • Set posting_status_cd = 'D' (Draft).
  • Source: Newly created client_ledger_id from Step 1.
  • Action: INSERT into cash_receipt_client_ledger.
  • Logic:
    • Set cash_receipt_worksheet_id = input worksheetId.
    • Set client_ledger_id = newly created ledger ID.
    • Set cash_receipt_amt_applied = '0.00'.
    • Set all dimensional FKs (deal_id, uta_entity_id, department_id, client_id) from input.

Side-effects:

  • Creates a new client_ledger record.

Postconditions:

  • A new client_ledger record exists with client_ledger_open_item_ind = true.
  • A corresponding cash_receipt_client_ledger record links it to the worksheet with a zero amount.

2.9 Update Client Ledger Application ​

Operation: updateClientLedgerApplication

Trigger: User changes the amount on a client ledger application in the worksheet.

Input Parameters:

  • cashReceiptClientLedgerId: Integer — cash_receipt_client_ledger.cash_receipt_client_ledger_id
  • cashReceiptAmtApplied: Decimal(15,2) — new amount (transmitted as string)
  • userId: String — identifier for the authenticated user

Updates the amount on an existing cash_receipt_client_ledger record.

Step 1. Update Client Ledger Application ​

  • Source: cash_receipt_client_ledger where cash_receipt_client_ledger_id = input.
  • Action: UPDATE cash_receipt_client_ledger.
  • Logic:
    • Set cash_receipt_amt_applied = new amount.
    • Set updated_by = userId, updated_dt = now.

Side-effects:

  • None beyond the updated record.

Postconditions:

  • cash_receipt_client_ledger.cash_receipt_amt_applied reflects the new amount.

2.10 Remove Client Ledger Application ​

Operation: removeClientLedgerApplication

Trigger: User removes a client ledger application row from the worksheet.

Input Parameters:

  • cashReceiptClientLedgerId: Integer — cash_receipt_client_ledger.cash_receipt_client_ledger_id
  • userId: String — identifier for the authenticated user

Deletes a cash_receipt_client_ledger record from a worksheet.

Step 1. Delete Client Ledger Application ​

  • Source: cash_receipt_client_ledger where cash_receipt_client_ledger_id = input.
  • Action: DELETE from cash_receipt_client_ledger.
  • Logic:
    • Remove the record unconditionally.

Side-effects:

  • None beyond the deleted record.

Postconditions:

  • The cash_receipt_client_ledger record no longer exists.

2.11 Create Payout Record ​

Operation: createPayout

Trigger: User adds a passthrough, VAT pass-through, or loan payout line to the worksheet; or settlements service creates settlement payouts during the settlement process.

Input Parameters:

  • worksheetId: Integer — cash_receipt_worksheet.cash_receipt_worksheet_id
  • payoutPartyId: Integer — party.party_id
  • paymentPartyBankId: Integer, optional — bank_account.bank_account_id
  • paymentItemName: String — description
  • paymentItemAmt: Decimal(15,2) — amount (transmitted as string)
  • paymentItemTypeCd: String — 'S', 'P', 'L', 'R', or 'V'
  • paymentItemCurrencyCd: String — ISO currency code, default 'USD'
  • paymentDate: Date, optional — target payment date
  • doNotSendInd: Boolean, optional — default false
  • dealId, buyerId, utaEntityId, departmentId: Integer, optional — dimensional FKs
  • userId: String — identifier for the authenticated user

Creates a cash_receipt_payout record on a worksheet.

Step 1. Insert Payout ​

  • Source: Validated input parameters.
  • Action: INSERT into cash_receipt_payout.
  • Logic:
    • Map all input fields directly to corresponding columns.
    • Set payout_status_cd = 'PENDING'.
    • Set created_by = userId, updated_by = userId.

NOTE

Settlement payouts (payment_item_type_cd = 'S') are excluded from the worksheet balance total calculation. Only non-settlement payouts (types 'P', 'L', 'R', 'V') contribute to the total applied.

Side-effects:

  • None beyond the inserted record.

Postconditions:

  • A new cash_receipt_payout record exists with payout_status_cd = 'PENDING'.

2.12 Create VAT Pass-Through Payout ​

Operation: createVatPassThroughPayout

Trigger: System detects UK VAT applies to a billing item and creates the VAT pass-through payout. Idempotent — safe to call multiple times.

Input Parameters:

  • worksheetId: Integer — cash_receipt_worksheet.cash_receipt_worksheet_id
  • billingItemId: Integer — billing_item.billing_item_id
  • clientPartyId: Integer — party.party_id
  • vatAmt: Decimal(15,2) — VAT amount (transmitted as string)
  • currencyCd: String — ISO currency code
  • userId: String — identifier for the authenticated user

Creates a VAT pass-through payout for UK Artist Fee VAT, checking for idempotency before inserting.

Step 1. Check for Existing VAT Payout ​

  • Source: cash_receipt_payout where cash_receipt_worksheet_id = input and payment_item_type_cd = 'V'.
  • Action: SELECT from cash_receipt_payout.
  • Logic:
    • If a record already exists, return immediately with no insert (idempotent behavior).

Step 2. Fetch Deal Context ​

  • Source: billing_item where billing_item_id = input.
  • Action: SELECT deal_id, buyer_id, uta_entity_id, department_id from billing_item.
  • Logic:
    • Collect dimensional foreign keys for the payout record.

Step 3. Delegate to Create Payout ​

  • Source: Validated parameters from Steps 1 and 2.
  • Action: Delegate to Create Payout Record.
  • Logic:
    • Set paymentItemTypeCd = 'V'.
    • Set paymentItemName = 'UK VAT Pass-Through on Artist Fee'.
    • Set paymentItemAmt = input vatAmt.
    • Set payoutPartyId = input clientPartyId.

Side-effects:

  • None if a VAT payout already exists (idempotent).

Postconditions:

  • Exactly one cash_receipt_payout record with payment_item_type_cd = 'V' exists for the worksheet.

2.13 Update Payout Record ​

Operation: updatePayout

Trigger: User changes the amount or do_not_send_ind flag on a payout row in the worksheet.

Input Parameters:

  • cashReceiptPayoutId: Integer — cash_receipt_payout.cash_receipt_payout_id
  • paymentItemAmt: Decimal(15,2), optional — new amount (transmitted as string)
  • doNotSendInd: Boolean, optional — new flag value
  • userId: String — identifier for the authenticated user

Updates amount and/or do_not_send_ind on a cash_receipt_payout record.

Step 1. Update Payout ​

  • Source: cash_receipt_payout where cash_receipt_payout_id = input.
  • Action: UPDATE cash_receipt_payout.
  • Logic:
    • Set provided fields (only those supplied in the update call).
    • Set updated_by = userId, updated_dt = now.

Side-effects:

  • None beyond the updated record.

Postconditions:

  • cash_receipt_payout reflects the new amount and/or flag value.

2.14 Remove Payout Record ​

Operation: removePayout

Trigger: User removes a payout row from the worksheet.

Input Parameters:

  • cashReceiptPayoutId: Integer — cash_receipt_payout.cash_receipt_payout_id
  • userId: String — identifier for the authenticated user

Deletes a cash_receipt_payout record.

Step 1. Delete Payout ​

  • Source: cash_receipt_payout where cash_receipt_payout_id = input.
  • Action: DELETE from cash_receipt_payout.
  • Logic:
    • Remove the record unconditionally.

Side-effects:

  • None beyond the deleted record.

Postconditions:

  • The cash_receipt_payout record no longer exists.

2.15 Manage Application Deductions ​

Operation: manageApplicationDeduction

Trigger: User adds, updates, or removes a deduction on an individual receivable application.

Input Parameters:

  • cashReceiptApplicationDeductionId: Integer, optional — cash_receipt_application_deduction.cash_receipt_application_deduction_id (required for update/delete)
  • cashReceiptApplicationId: Integer — cash_receipt_application.cash_receipt_application_id (required for create)
  • billingItemDeductionTypeCd: String, optional — deduction type code
  • deductionAmtApplied: Decimal(15,2) — amount (transmitted as string)
  • userId: String — identifier for the authenticated user

Manages cash_receipt_application_deduction records independently of the parent application save flow. Supports create, update, and delete operations.

Step 1. Create Deduction ​

  • Source: Validated cashReceiptApplicationId.
  • Action: INSERT into cash_receipt_application_deduction.
  • Logic:
    • Set cash_receipt_application_id = input.
    • Set billing_item_deduction_type_cd = input (nullable).
    • Set deduction_amt_applied = input amount.
    • Set created_by = userId, updated_by = userId, created_dt = now, updated_dt = now.

Step 2. Update Deduction (alternative path) ​

  • Source: cash_receipt_application_deduction where cash_receipt_application_deduction_id = input.
  • Action: UPDATE cash_receipt_application_deduction.
  • Logic:
    • Set deduction_amt_applied and/or billing_item_deduction_type_cd as provided.
    • Set updated_by = userId, updated_dt = now.

Step 3. Delete Deduction (alternative path) ​

  • Source: cash_receipt_application_deduction where cash_receipt_application_deduction_id = input.
  • Action: DELETE from cash_receipt_application_deduction.
  • Logic:
    • Remove the record unconditionally.

Side-effects:

  • None beyond the inserted, updated, or deleted record.

Postconditions:

  • The cash_receipt_application_deduction state reflects the requested create, update, or delete operation.

2.16 Apply Worksheet (D to P) ​

Operation: applyWorksheet

Trigger: User with CASH_MANAGER or IT role clicks Apply on a Draft worksheet.

Input Parameters:

  • worksheetId: Integer — cash_receipt_worksheet.cash_receipt_worksheet_id
  • canApplyWorksheet: Boolean — permission flag for the calling user
  • userId: String — identifier for the authenticated user

Transitions a worksheet from Draft to Applied status. Finalizes cash applications and stages REV entries and client ledger entries for GL posting.

Step 1. Validate Preconditions ​

  • Source: cash_receipt_worksheet where cash_receipt_worksheet_id = input.
  • Action: SELECT and validate.
  • Logic:
    • cash_receipt_worksheet_status_cd must be 'D'. Otherwise abort (APPLY-01).
    • At least one record must exist across cash_receipt_application, cash_receipt_client_ledger, or cash_receipt_payout for this worksheet. Otherwise abort (APPLY-02).
    • canApplyWorksheet must be true. Otherwise abort (APPLY-04).
    • Partial application is allowed — worksheet balance does not need to equal zero (APPLY-03).

Step 2. Update Worksheet Status ​

  • Source: Validated worksheetId.
  • Action: UPDATE cash_receipt_worksheet.
  • Logic:
    • Set cash_receipt_worksheet_status_cd = 'P' (Applied).
    • Set posting_status_cd = 'U' (Unposted — ready for GL pickup).
    • Set applied_dt = now.
    • Set applied_by = user name.
    • Set updated_dt = now, updated_by = user name.

IMPORTANT

posting_status_cd = 'U' signals to the accounting job that this worksheet's REV applications and client ledger entries are ready for GL posting. PAY applications do NOT post to GL at Apply time — they post only after bank confirmation of the payment.

Side-effects:

  • The GL posting job will pick up this worksheet's REV applications and client ledger entries on its next run.

Postconditions:

  • cash_receipt_worksheet.cash_receipt_worksheet_status_cd = 'P'.
  • cash_receipt_worksheet.posting_status_cd = 'U'.

2.17 Settle Worksheet (P to T) ​

Operation: settleWorksheet

Trigger: User with CASH_PROCESSOR or IT role clicks Settle on an Applied worksheet.

Input Parameters:

  • worksheetId: Integer — cash_receipt_worksheet.cash_receipt_worksheet_id
  • canSettleWorksheet: Boolean — permission flag for the calling user
  • userId: String — identifier for the authenticated user

Transitions a worksheet from Applied to Settled status. All PAY applications must have settlements before this transition.

Step 1. Validate Preconditions ​

  • Source: cash_receipt_worksheet and linked cash_receipt_application records.
  • Action: SELECT and validate.
  • Logic:
    • cash_receipt_worksheet_status_cd must be 'P'. Otherwise abort (SETTLE-01).
    • All PAY-type applications must have a non-null participant_settlement_id. Otherwise abort (SETTLE-02).
    • canSettleWorksheet must be true. Otherwise abort (SETTLE-03).

Step 2. Update Worksheet Status ​

  • Source: Validated worksheetId.
  • Action: UPDATE cash_receipt_worksheet.
  • Logic:
    • Set cash_receipt_worksheet_status_cd = 'T' (Settled).
    • Set settled_dt = now.
    • Set settled_by = user name.
    • Set updated_dt = now, updated_by = user name.

Step 3. Update Linked Settlements ​

  • Source: All participant_settlement records linked to this worksheet's PAY applications.
  • Action: UPDATE participant_settlement.
  • Logic:
    • Set participant_settlement_status_cd = 'T' (Settled) for each.

Side-effects:

  • All linked participant_settlement records are updated to Settled status.

Postconditions:

  • cash_receipt_worksheet.cash_receipt_worksheet_status_cd = 'T'.
  • All linked participant_settlement.participant_settlement_status_cd = 'T'.

2.18 Approve Worksheet (T/D/S to A) ​

Operation: approveWorksheet

Trigger: User with SETTLEMENT_APPROVER or IT role clicks Approve on a Settled (or Draft with elevated privilege) worksheet.

Input Parameters:

  • worksheetId: Integer — cash_receipt_worksheet.cash_receipt_worksheet_id
  • hasApprovePrivilege: Boolean — permission flag; required to be true when approving from Draft status
  • userId: String — identifier for the authenticated user

Transitions a worksheet to Approved status. Creates payment items from settlement payouts, resets do_not_send_ind on unlocked payments, and closes fully paid billing items.

Step 1. Validate Preconditions ​

  • Source: cash_receipt_worksheet and linked applications.
  • Action: SELECT and validate.
  • Logic:
    • cash_receipt_worksheet_status_cd must be 'D', 'S' (legacy), or 'T'. Otherwise abort.
    • If status is 'D', hasApprovePrivilege must be true. Otherwise abort.
    • At least one application must exist. Otherwise abort.

Step 2. Update Worksheet Status ​

  • Source: Validated worksheetId.
  • Action: UPDATE cash_receipt_worksheet.
  • Logic:
    • Set cash_receipt_worksheet_status_cd = 'A' (Approved).
    • Set approved_dt = now.
    • Set approved_by = userId.
    • Set updated_dt = now, updated_by = userId.

Step 3. Release Receipt Lock ​

  • Source: Parent cash_receipt linked to the worksheet.
  • Action: UPDATE cash_receipt.
  • Logic:
    • Set locked_by_user_id = null.

Step 4. Submit Settlements (if Draft shortcut) ​

  • Source: All participant_settlement records linked to this worksheet.
  • Action: Delegate to settlement service.
  • Logic:
    • Only if worksheet was in Draft status ('D'): call settlement service to submit settlements, which creates payouts from settlement items.

Step 5. Approve All Linked Settlements ​

  • Source: All participant_settlement records linked to this worksheet.
  • Action: UPDATE participant_settlement.
  • Logic:
    • Set participant_settlement_status_cd = 'A' (Approved) for each.

Step 6. Create Payment Items from Payouts ​

  • Source: All cash_receipt_payout records for this worksheet with a linked participant_settlement_item.
  • Action: INSERT into payment_item; UPDATE cash_receipt_payout.
  • Logic:
    • For each participant_settlement_item, create a corresponding payment_item record.
    • Update cash_receipt_payout.payment_item_id to reference the new payment item.

Step 7. Reset doNotSendInd on Unlocked Payment Items ​

  • Source: All cash_receipt_payout records with a linked payment_item_id.
  • Action: UPDATE payment_item and participant_settlement_item.
  • Logic:
    • If the payout's do_not_send_ind = true, keep the payment's do_not_send_ind = true (leave unchanged).
    • If the payment is locked (payment_execution_status_cd in ['PROCESSING', 'SENT', 'PAID', 'ACKNOWLEDGED']), leave unchanged.
    • Otherwise, set payment_item.do_not_send_ind = false and participant_settlement_item.do_not_send_ind = false.

Step 8. Close Fully Paid Billing Items ​

  • Source: All unique billing_item_id values referenced by this worksheet's applications.
  • Action: UPDATE billing_item.
  • Logic:
    • For each billing item, calculate balance: (revTotalAmt + payTotalAmt) - (revCash + payCash).
    • If |balance| < 0.01, set billing_item.open_item_ind = false.

Side-effects:

  • cash_receipt.locked_by_user_id is set to null.
  • All linked participant_settlement records are updated to Approved status.
  • payment_item records are created for each settlement payout.
  • Fully paid billing items have open_item_ind = false.

Postconditions:

  • cash_receipt_worksheet.cash_receipt_worksheet_status_cd = 'A'.
  • payment_item records exist for all settlement payouts.
  • Unlocked payment items have do_not_send_ind = false and are eligible for bank transmission.

2.19 Reject Worksheet from Applied (P to D) ​

Operation: rejectWorksheetFromApplied

Trigger: User with CASH_PROCESSOR or IT role rejects an Applied worksheet to allow further editing.

Input Parameters:

  • worksheetId: Integer — cash_receipt_worksheet.cash_receipt_worksheet_id
  • canSettleWorksheet: Boolean — permission flag
  • userId: String — identifier for the authenticated user

Moves a worksheet back from Applied to Draft status so receivable applications can be edited.

Step 1. Validate Preconditions ​

  • Source: cash_receipt_worksheet.
  • Action: SELECT and validate.
  • Logic:
    • cash_receipt_worksheet_status_cd must be 'P'. Otherwise abort.
    • canSettleWorksheet must be true. Otherwise abort.

Step 2. Update Worksheet Status ​

  • Source: Validated worksheetId.
  • Action: UPDATE cash_receipt_worksheet.
  • Logic:
    • Set cash_receipt_worksheet_status_cd = 'D' (Draft).
    • Set applied_dt = null, applied_by = null.
    • Set posting_status_cd = null (no longer ready for GL).
    • Set rejected_dt = now, rejected_by = user name.

WARNING

This is a backward status transition. It is distinct from the Return (reopen) process. No reversal transactions are created. This simply re-enables editing of the receivable applications.

Side-effects:

  • None beyond the worksheet status update.

Postconditions:

  • cash_receipt_worksheet.cash_receipt_worksheet_status_cd = 'D'.
  • GL staging is no longer active (posting_status_cd = null).

2.20 Reject Worksheet from Settled (T to P) ​

Operation: rejectWorksheetFromSettled

Trigger: User with SETTLEMENT_APPROVER or IT role rejects a Settled worksheet to allow settlements to be edited.

Input Parameters:

  • worksheetId: Integer — cash_receipt_worksheet.cash_receipt_worksheet_id
  • canApproveWorksheet: Boolean — permission flag
  • userId: String — identifier for the authenticated user

Moves a worksheet back from Settled to Applied status so settlements can be edited.

Step 1. Validate Preconditions ​

  • Source: cash_receipt_worksheet.
  • Action: SELECT and validate.
  • Logic:
    • cash_receipt_worksheet_status_cd must be 'T'. Otherwise abort.
    • canApproveWorksheet must be true. Otherwise abort.

Step 2. Update Worksheet Status ​

  • Source: Validated worksheetId.
  • Action: UPDATE cash_receipt_worksheet.
  • Logic:
    • Set cash_receipt_worksheet_status_cd = 'P' (Applied).
    • Set settled_dt = null, settled_by = null.
    • Set rejected_dt = now, rejected_by = user name.

Step 3. Revert Linked Settlements to Draft ​

  • Source: All participant_settlement records linked to this worksheet.
  • Action: UPDATE participant_settlement.
  • Logic:
    • Set participant_settlement_status_cd = 'D' (Draft) for each.

Side-effects:

  • All linked participant_settlement records are reverted to Draft status.

Postconditions:

  • cash_receipt_worksheet.cash_receipt_worksheet_status_cd = 'P'.
  • All linked participant_settlement.participant_settlement_status_cd = 'D'.

2.21 Add Billing Items to Existing Worksheet ​

Operation: addBillingItemsToWorksheet

Trigger: User adds one or more billing items to a worksheet that may be in Draft, Applied, or Settled status.

Input Parameters:

  • worksheetId: Integer — cash_receipt_worksheet.cash_receipt_worksheet_id
  • items: Array — each with billingItemId (Integer), optional revAmtApplied (Decimal), payAmtApplied (Decimal)
  • userId: String — identifier for the authenticated user

Adds one or more billing items to an existing worksheet, rolling back status to Draft if needed.

Step 1. Validate Worksheet Is Editable ​

  • Source: cash_receipt_worksheet.
  • Action: SELECT and validate.
  • Logic:
    • cash_receipt_worksheet_status_cd must be 'D', 'P', or 'T' and current_item_ind must be true. Otherwise abort.

Step 2. Roll Back Settled Status (Conditional) ​

  • Source: cash_receipt_worksheet with cash_receipt_worksheet_status_cd = 'T'.
  • Action: UPDATE participant_settlement and cash_receipt_worksheet.
  • Logic:
    • If status is 'T': reset all linked participant_settlement.participant_settlement_status_cd = 'D'.
    • Update worksheet: cash_receipt_worksheet_status_cd = 'P', clear settled_dt, settled_by.

Step 3. Roll Back Applied Status (Conditional) ​

  • Source: cash_receipt_worksheet with cash_receipt_worksheet_status_cd = 'P' (or just reverted from 'T').
  • Action: UPDATE cash_receipt_worksheet.
  • Logic:
    • Set cash_receipt_worksheet_status_cd = 'D'.
    • Clear applied_dt, applied_by, posting_status_cd.

Step 4. Add Each Billing Item ​

  • Source: Input items array; cash_receipt_application to detect already-added items.
  • Action: Delegate to Add Receivable Application for each item not already on the worksheet.
  • Logic:
    • If explicit revAmtApplied / payAmtApplied provided, use those amounts.
    • Otherwise, use auto-apply calculator: allocate remaining cash proportionally between REV and PAY based on revPercent, accounting for deduction balances.

Side-effects:

  • Worksheet may be rolled back from Settled to Applied and then to Draft.
  • Linked settlements may be reverted to Draft status.

Postconditions:

  • All specified billing items have cash_receipt_application records on the worksheet.
  • cash_receipt_worksheet.cash_receipt_worksheet_status_cd = 'D'.

2.22 Abandon Worksheet ​

Operation: abandonWorksheet

Trigger: User discards a Draft worksheet without deleting it (soft delete).

Input Parameters:

  • worksheetId: Integer — cash_receipt_worksheet.cash_receipt_worksheet_id
  • userId: String — identifier for the authenticated user

Soft-deletes a Draft worksheet by marking it as non-current, preserving it as an audit record.

Step 1. Validate Worksheet Is Draft ​

  • Source: cash_receipt_worksheet.
  • Action: SELECT and validate.
  • Logic:
    • cash_receipt_worksheet_status_cd must be 'D'. Otherwise abort.

Step 2. Mark as Non-Current ​

  • Source: Validated worksheetId.
  • Action: UPDATE cash_receipt_worksheet.
  • Logic:
    • Set current_item_ind = false.
    • Set updated_by = userId, updated_dt = now.

Step 3. Release Receipt Lock ​

  • Source: cash_receipt linked to the worksheet.
  • Action: UPDATE cash_receipt.
  • Logic:
    • Set locked_by_user_id = null.

Side-effects:

  • cash_receipt.locked_by_user_id is set to null.

Postconditions:

  • cash_receipt_worksheet.current_item_ind = false.
  • The worksheet record is preserved for audit purposes.
  • The parent cash_receipt is unlocked.

2.23 Delete Worksheet ​

Operation: deleteWorksheet

Trigger: User permanently deletes a Draft worksheet and all its children.

Input Parameters:

  • worksheetId: Integer — cash_receipt_worksheet.cash_receipt_worksheet_id
  • userId: String — identifier for the authenticated user

Permanently deletes a Draft worksheet and all dependent records in cascade order.

Step 1. Validate Worksheet Is Draft ​

  • Source: cash_receipt_worksheet.
  • Action: SELECT and validate.
  • Logic:
    • cash_receipt_worksheet_status_cd must be 'D'. Otherwise abort.

Step 2. Release Receipt Lock ​

  • Source: cash_receipt linked to the worksheet.
  • Action: UPDATE cash_receipt.
  • Logic:
    • Set locked_by_user_id = null.

Step 3. Delete Deductions ​

  • Source: All cash_receipt_application_deduction records where cash_receipt_application_id is in the set of applications for this worksheet.
  • Action: DELETE from cash_receipt_application_deduction.
  • Logic:
    • First collect all cash_receipt_application_id values for the worksheet.
    • Delete all associated deduction records.

Step 4. Delete Applications ​

  • Source: cash_receipt_application where cash_receipt_worksheet_id = input.
  • Action: DELETE from cash_receipt_application.
  • Logic:
    • Remove all application records for the worksheet.

Step 5. Delete Client Ledger Applications ​

  • Source: cash_receipt_client_ledger where cash_receipt_worksheet_id = input.
  • Action: DELETE from cash_receipt_client_ledger.
  • Logic:
    • Remove all client ledger application records for the worksheet.

Step 6. Delete Payouts ​

  • Source: cash_receipt_payout where cash_receipt_worksheet_id = input.
  • Action: DELETE from cash_receipt_payout.
  • Logic:
    • Remove all payout records for the worksheet.

Step 7. Delete Worksheet ​

  • Source: cash_receipt_worksheet where cash_receipt_worksheet_id = input.
  • Action: DELETE from cash_receipt_worksheet.
  • Logic:
    • Remove the worksheet header record.

IMPORTANT

Deletion order matters due to foreign key constraints. Child records must be deleted before parent records. All steps execute within a transaction.

Side-effects:

  • cash_receipt.locked_by_user_id is set to null.
  • All child records are permanently deleted.

Postconditions:

  • No records remain in cash_receipt_worksheet, cash_receipt_application, cash_receipt_application_deduction, cash_receipt_client_ledger, or cash_receipt_payout for the given worksheetId.

2.24 Return (Reopen) Worksheet ​

Operation: reopenWorksheet

Trigger: User with SETTLEMENT_APPROVER or IT role initiates a Return on an Approved worksheet.

Input Parameters:

  • worksheetId: Integer — cash_receipt_worksheet.cash_receipt_worksheet_id
  • reopenReason: String — reason for returning the worksheet
  • userId: String — identifier for the authenticated user

The most complex procedure. Returns an Approved worksheet by producing three documents: (1) sealed original, (2) reversal worksheet with negative entries for all items, and (3) replacement draft. This is the only way to modify an Approved worksheet.

Step 1. Validate Reopen Eligibility ​

  • Source: cash_receipt_worksheet and parent cash_receipt.
  • Action: SELECT and validate.
  • Logic:
    • cash_receipt_worksheet_status_cd must be 'A'. Otherwise abort.
    • cash_receipt_split_id must be non-null. Otherwise abort: "Worksheet has no split ID, cannot reopen".
    • cash_receipt.receipt_type_cd must not be 'WRITE_OFF'. Otherwise abort: "Write-off worksheets cannot be reopened. Use the packet recovery process instead."

Step 2. Load All Worksheet Data ​

  • Source: cash_receipt_application, cash_receipt_client_ledger, cash_receipt_payout for the worksheet.
  • Action: SELECT all three data sets in parallel.
  • Logic:
    • Load all billing item applications (with detail type enrichment).
    • Load all client ledger applications.
    • Load all payout records (with payment item linkage).

Step 3. Classify Items by Lock Status ​

  • Source: participant_settlement_item and payment_item records linked to the worksheet's PAY applications.
  • Action: Evaluate payment_item.payment_execution_status_cd for all linked payment items.
  • Logic:
    • Collect all unique participant_settlement_id values from PAY applications.
    • For each settlement, fetch all participant_settlement_item records. For each item, find linked payment_item records excluding voided (payment_item_posting_status_cd = 'X').
    • If any linked payment has payment_execution_status_cd in ['PROCESSING', 'SENT', 'PAID', 'ACKNOWLEDGED'], mark that payment item as locked.
    • If ANY settlement item within a settlement is locked, mark ALL items in that settlement as locked (settlement-level lock propagation). Lock all PAY applications belonging to that settlement.
    • Apply bilateral lock for REV applications: group REV and PAY applications by billing_item_id, sort each group by cash_receipt_application_id ascending. If the positionally paired PAY is locked, the REV at the same position is also locked.
    • For payouts: settlement payouts are locked if their participant_settlement_item_id is in the locked set. Passthrough payouts are independently checked via their own payment_item.payment_execution_status_cd.

Step 4. Create Reversal Worksheet ​

  • Source: Original worksheet and classified data from Steps 2–3.
  • Action: INSERT cash_receipt_worksheet; INSERT reversal applications, settlements, client ledger entries, and payouts.
  • Logic:
    • Insert cash_receipt_worksheet with cash_receipt_split_id = same as original, cash_receipt_worksheet_status_cd = 'R', current_item_ind = false, worksheet_type_cd = 'REVERSAL', previous_worksheet_id = original ID, approved_dt = now, approved_by = userId, posting_status_cd = 'U', return_reason = 'Reversal of worksheet #<id>: <reason>'.
    • Reverse ALL REV applications: for each, insert cash_receipt_application with cash_receipt_amt_applied = original * -1, reversal_of_application_id = original ID, reversal_reason_cd = 'WORKSHEET_REOPEN'. Copy all deductions with negated amounts.
    • Create reversal settlements: for each unique participant_settlement_id, insert participant_settlement with participant_settlement_status_cd = 'R'. For each participant_settlement_item: set all linked payment_item.do_not_send_ind = true; for unlocked payments also set payment_execution_status_cd = 'WAITING'; insert reversal item with negated participant_settlement_commission_amt. Build mapping: original settlement ID → reversal settlement ID.
    • Reverse ALL PAY applications: same as REV reversal, linking to reversal settlement via mapping.
    • Reverse ALL client ledger entries: insert with cash_receipt_amt_applied = original * -1, reversal_of_ledger_id = original ID, reversal_reason_cd = 'WORKSHEET_REOPEN'.
    • Reverse ALL payouts: for passthrough payouts with a payment_item_id, set payment_item.do_not_send_ind = true (and payment_execution_status_cd = 'WAITING' if unlocked). Insert reversal payout with payment_item_amt = original * -1, payment_item_name = 'Reversal: <original name>', reversal_of_payout_id = original payout ID, payment_item_id = null.

Step 5. Create Replacement Draft ​

  • Source: Original worksheet data and classification from Steps 2–3.
  • Action: INSERT cash_receipt_worksheet; copy all applications, settlements, ledger entries, and payouts.
  • Logic:
    • Insert cash_receipt_worksheet with cash_receipt_worksheet_status_cd = 'D', current_item_ind = true, cash_receipt_split_id = same as original, worksheet_type_cd = 'REPLACEMENT', previous_worksheet_id = original ID.
    • Copy ALL REV applications (excluding reversal records): insert cash_receipt_application with same billing_item_detail_id and cash_receipt_amt_applied. Copy all deductions at same (positive) amounts.
    • Copy ALL PAY applications: same logic as REV copy.
    • Copy ALL settlements: for each, insert new participant_settlement with participant_settlement_status_cd = 'D'. Link new PAY applications via participant_settlement_id. For each participant_settlement_item: insert new item with do_not_send_ind = true; re-point all existing payment_item.participant_settlement_item_id to the new item; for unlocked payments set payment_execution_status_cd = 'WAITING'; all payments get do_not_send_ind = true. Build settlement item ID mapping (original → new) for payout FK remapping.
    • Copy ALL client ledger entries (excluding reversal records): always editable; cash_receipt_amt_applied is copied at same (positive) amount.
    • Copy ALL payouts (excluding reversal records): set do_not_send_ind = true on all copied payouts; preserve payment_item_id link; remap participant_settlement_item_id using the settlement item mapping.

Step 6. Seal Original Worksheet ​

  • Source: Original cash_receipt_worksheet record.
  • Action: UPDATE cash_receipt_worksheet (original).
  • Logic:
    • Set cash_receipt_worksheet_status_cd = 'R'.
    • Set current_item_ind = false.
    • Set returned_dt = now, returned_by = userId.
    • Set return_reason = input reason.
    • Set worksheet_type_cd = 'ORIGINAL'.
    • Set replaced_by_worksheet_id = replacement draft ID.

Step 7. Return Result ​

  • Source: Replacement draft worksheet from Step 5.
  • Action: Return the replacement draft with message indicating reversal and replacement IDs.
  • Logic:
    • Return data = replacement draft worksheet.
    • Return message = 'Worksheet reopened. Reversal #<reversalId>, replacement draft #<replacementId> created.'

Side-effects:

  • Original worksheet is sealed as Returned (current_item_ind = false, worksheet_type_cd = 'ORIGINAL').
  • A new reversal worksheet exists with negative entries for ALL items and posting_status_cd = 'U'.
  • A new replacement draft is the active worksheet (current_item_ind = true).
  • All payment items have do_not_send_ind = true (to prevent premature bank transmission).
  • Unlocked payment items have payment_execution_status_cd = 'WAITING'.

Postconditions:

  • Three worksheets now exist for the split: the sealed original (worksheet_type_cd = 'ORIGINAL'), the reversal (worksheet_type_cd = 'REVERSAL'), and the replacement draft (worksheet_type_cd = 'REPLACEMENT').
  • Only the replacement draft has current_item_ind = true.
  • The original's replaced_by_worksheet_id points to the replacement draft.
  • Both the reversal and replacement have previous_worksheet_id pointing to the original.

2.25 Save Settlement ​

Operation: saveSettlement

Trigger: User on the Applied worksheet selects PAY receivables and saves a settlement specifying how PAY is divided among the client's party.

Input Parameters:

  • dto: Object — settlement data including items[] (settlement items) and applicationIds[] (linked PAY application IDs)
  • settlementId: Integer, optional — participant_settlement.participant_settlement_id (for updates)
  • userId: String — identifier for the authenticated user

Creates or updates a settlement for selected PAY applications, then immediately creates draft payouts from the settlement items.

Step 1. Validate Settlement Total ​

  • Source: cash_receipt_application for each ID in dto.applicationIds; dto.items amounts.
  • Action: Compare sums.
  • Logic:
    • Sum all items[].participantSettlementCommissionAmt.
    • Sum all PAY cash_receipt_application.cash_receipt_amt_applied for the given application IDs.
    • If |settlementTotal - payApplied| > 0.01, abort: settlement total does not match PAY applied.

Step 2. Create or Update Settlement ​

  • Source: Validated dto and optional settlementId.
  • Action: Delegate to settlement service saveSettlement.
  • Logic:
    • If settlementId is null, INSERT new participant_settlement and participant_settlement_item records.
    • If settlementId is provided, UPDATE existing records.
    • Link each PAY application in dto.applicationIds by setting cash_receipt_application.participant_settlement_id = new/updated settlement ID.

Step 3. Create Draft Payouts ​

  • Source: Newly saved participant_settlement_item records.
  • Action: INSERT into cash_receipt_payout via createPayoutsForWorksheetInDraft (idempotent).
  • Logic:
    • For each participant_settlement_item without an existing payout, insert a cash_receipt_payout with payment_item_type_cd = 'S'.
    • Skip items that already have a corresponding payout (idempotent behavior).

Side-effects:

  • PAY applications are linked to the settlement via participant_settlement_id.
  • Settlement-derived payouts are immediately created as draft records.

Postconditions:

  • A participant_settlement record exists (created or updated).
  • All specified PAY applications have participant_settlement_id populated.
  • cash_receipt_payout records with payment_item_type_cd = 'S' exist for each settlement item.

2.26 Delete Settlement ​

Operation: deleteSettlement

Trigger: User deletes a settlement from the Applied worksheet.

Input Parameters:

  • settlementId: Integer — participant_settlement.participant_settlement_id
  • userId: String — identifier for the authenticated user

Removes a settlement and its items, voids unlocked payment items, and unlinks PAY applications.

Step 1. Check for Locked Payment Items ​

  • Source: participant_settlement_item joined to payment_item for the settlement.
  • Action: SELECT and evaluate lock status.
  • Logic:
    • If any linked payment_item.payment_execution_status_cd is in ['PROCESSING', 'SENT', 'PAID', 'ACKNOWLEDGED'], abort: settlement cannot be deleted because a payment has been sent.

Step 2. Void Unlocked Payment Items ​

  • Source: payment_item records linked to settlement items, excluding locked ones.
  • Action: UPDATE payment_item.
  • Logic:
    • Set payment_item_posting_status_cd = 'X' (Skipped/Voided).
    • Set payment_execution_status_cd = 'CANCELLED'.
    • Set return_reason_cd = 'SETTLEMENT_DELETE'.

Step 3. Delete Linked Payouts ​

  • Source: cash_receipt_payout records linked to settlement items.
  • Action: DELETE from cash_receipt_payout.
  • Logic:
    • Remove all payout records that reference any participant_settlement_item_id belonging to this settlement.
  • Source: cash_receipt_application where participant_settlement_id = input settlement ID.
  • Action: UPDATE cash_receipt_application.
  • Logic:
    • Set participant_settlement_id = null for all linked applications.

Step 5. Delete Settlement Items ​

  • Source: participant_settlement_item where participant_settlement_id = input.
  • Action: DELETE from participant_settlement_item.
  • Logic:
    • Remove all settlement item records.

Step 6. Delete Settlement Header ​

  • Source: participant_settlement where participant_settlement_id = input.
  • Action: DELETE from participant_settlement.
  • Logic:
    • Remove the settlement header record.

Side-effects:

  • Unlocked payment_item records are voided (payment_item_posting_status_cd = 'X').
  • All linked cash_receipt_payout records are deleted.
  • PAY applications have participant_settlement_id set to null.

Postconditions:

  • No participant_settlement, participant_settlement_item, or linked cash_receipt_payout records remain for the given settlement.
  • PAY applications that were linked to the settlement are unlinked and available to be added to a new settlement.

3. Business Rules & Logic ​

3.1 Worksheet Status Lifecycle ​

Business rule: A worksheet follows a strict forward-only lifecycle through five statuses. Corrections to Approved worksheets are handled through the return mechanism, not backward transitions.

Data-level enforcement:

Draft (D) → Applied (P) → Settled (T) → Approved (A) → Returned (R)
TransitionDirectionCreates Reversal?Permission
D → PForwardNocanApplyWorksheet
P → TForwardNocanSettleWorksheet
T → AForwardNocanApproveWorksheet
A → RForward (return)Yes (three documents)canApproveWorksheet
P → DBackward (reject)NocanSettleWorksheet
T → PBackward (reject)NocanApproveWorksheet
  • Read: cash_receipt_worksheet.cash_receipt_worksheet_status_cd
  • Guard: Each transition validates the current status before allowing the update.
  • Write: Update cash_receipt_worksheet.cash_receipt_worksheet_status_cd to the new status code.

IMPORTANT

No worksheet ever moves backward from Approved. A return from Approved always produces three documents (original sealed, reversal, replacement draft). Backward transitions (reject) are only available for P → D and T → P.

3.2 Locked Payment Rule ​

Business rule: A payment item is locked once money has begun leaving the building. Locked payment items cannot be voided or reversed; their associated applications are copied as read-only to the replacement draft.

Data-level enforcement:

  • Read: payment_item.payment_execution_status_cd
  • Guard: If payment_execution_status_cd ∈ ['PROCESSING', 'SENT', 'PAID', 'ACKNOWLEDGED'], the payment is locked. If payment_item_posting_status_cd = 'X' (voided), exclude from lock checks entirely.
  • Write: Locked items: do_not_send_ind = true (status preserved). Unlocked items: payment_execution_status_cd = 'WAITING', do_not_send_ind = true.

3.3 Bilateral Lock Rule ​

Business rule: A locked PAY application also locks its paired REV application for the same billing item. REV and PAY are a matched pair — you cannot reverse one without the other.

Data-level enforcement:

  • Read: Group cash_receipt_application by billing_item_id, separate by billing_item_detail_type_cd (REV vs. PAY). Sort each group by cash_receipt_application_id ascending.
  • Guard: Pair REV[0] with PAY[0], REV[1] with PAY[1], etc. If PAY at position N is locked, REV at position N is also locked.
  • Write: Add REV application ID to lockedApplicationIds set. When copying to replacement draft, both locked REV and PAY are copied as read-only.

3.4 Partial Application Rule ​

Business rule: Worksheets can be applied without using the full split balance. This allows unused funds to be transferred to other splits after approval.

Data-level enforcement:

  • Read: cash_receipt_worksheet.cash_receipt_worksheet_status_cd
  • Guard: No balance-equals-zero check is performed at any status transition. Only the total-exceeds-receipt check (Total Applied <= net_receipt_amt + 0.005) is enforced.
  • Write: N/A — this rule removes a constraint rather than adding one.

3.5 Total Applied Calculation ​

Business rule: The worksheet total applied is the sum of receivable applications, client ledger applications, non-settlement payouts, and deductions. Settlement payouts are excluded because they represent the division of PAY among parties, not additional cash allocation.

Data-level enforcement:

  • Read: Four sources: cash_receipt_application.cash_receipt_amt_applied, cash_receipt_client_ledger.cash_receipt_amt_applied, cash_receipt_payout.payment_item_amt (where payment_item_type_cd != 'S'), cash_receipt_application_deduction.deduction_amt_applied.
text
Total Applied = SUM(cash_receipt_application.cash_receipt_amt_applied)
              + SUM(cash_receipt_client_ledger.cash_receipt_amt_applied)
              + SUM(cash_receipt_payout.payment_item_amt WHERE payment_item_type_cd != 'S')
              + SUM(cash_receipt_application_deduction.deduction_amt_applied)
  • Guard: Total Applied <= cash_receipt.net_receipt_amt + 0.005

3.6 Over-Application to Individual Receivables ​

Business rule: Over-application to an individual billing item detail is permitted. The system only enforces that the total worksheet applied amount does not exceed the receipt amount.

Data-level enforcement:

  • Read: cash_receipt_application.cash_receipt_amt_applied per billing_item_detail_id
  • Guard: No per-receivable limit is enforced. Only the worksheet-level total is checked.
  • Write: N/A — this rule removes a constraint rather than adding one.

3.7 Currency Match Rule ​

Business rule: A receivable application can only be added if the billing item's currency matches the cash receipt's currency. Mismatched currency pairs are rejected.

Data-level enforcement:

  • Read: billing_item.billing_item_currency_cd and cash_receipt.currency_cd
  • Guard: If billing_item_currency_cd != cash_receipt.currency_cd, reject the application with a currency mismatch error.
  • Write: N/A — this is a guard only.

3.8 Single Active Worksheet Per Split ​

Business rule: At any point in time, only one worksheet per cash receipt split may be active (current). The return process enforces this by deactivating the original and activating the replacement.

Data-level enforcement:

  • Read: cash_receipt_worksheet.current_item_ind where cash_receipt_split_id = target split
  • Guard: On worksheet creation, if a row with current_item_ind = true already exists for the split, abort.
  • Write: On return: set original current_item_ind = false; set replacement current_item_ind = true.

3.9 GL Posting Rules ​

Business rule: REV applications and client ledger entries are staged for GL posting at Apply time. PAY applications are not posted to GL until the bank confirms payment completion. You cannot reverse a GL entry that was never posted.

Data-level enforcement:

Record TypeGL Staged at Apply?GL Staged at Approval?GL Staged on Return?
REV applicationYes (posting_status_cd = 'U')N/AYes (reversal worksheet posting_status_cd = 'U')
Client ledgerYesN/AYes (reversal)
PAY applicationNoNo (waits for bank confirmation)Depends on payment status
Payment itemN/AOnly after bank confirms (Stage 2)Only if original was posted
  • Read: cash_receipt_worksheet.posting_status_cd
  • Guard: The GL posting job only picks up worksheets where posting_status_cd = 'U'.
  • Write: Set posting_status_cd = 'U' at Apply. Reversal worksheets always get posting_status_cd = 'U'. Set posting_status_cd = 'P' after posting. Set posting_status_cd = 'X' to skip.

3.10 Write-Off Worksheet Restriction ​

Business rule: Worksheets created from write-off receipts cannot be returned or reopened through the normal return mechanism. The packet recovery process must be used instead.

Data-level enforcement:

  • Read: cash_receipt.receipt_type_cd via the worksheet's split chain
  • Guard: If receipt_type_cd = 'WRITE_OFF', reject the return with: "Write-off worksheets cannot be reopened. Use the packet recovery process instead."
  • Write: N/A — this is a guard only.

3.11 Settlement Total Validation ​

Business rule: The sum of settlement item amounts must equal the sum of PAY applied amounts for the linked applications. Settlements cannot be saved if the totals are mismatched.

Data-level enforcement:

  • Read: participant_settlement_item.participant_settlement_commission_amt (sum); cash_receipt_application.cash_receipt_amt_applied where billing_item_detail_type_cd = 'PAY' (sum)
  • Guard: If |settlementTotal - payApplied| > 0.01, abort.
  • Write: N/A — this is a guard only.

3.12 Deduction Proration Rule ​

Business rule: When a billing item detail has multiple deduction types and the user enters a lump-sum deduction, the amount is prorated across types proportionally based on each type's remaining balance.

Data-level enforcement:

  • Read: billing_item_deduction.billing_item_deduction_type_cd and balance per type for the billing_item_detail_id
  • Guard: N/A — this is a calculation, not a restriction.
  • Write: For each deduction type: proportion = typeBalance / totalRemainingBalance; proratedAmount = deductionAmount * proportion. Insert one cash_receipt_application_deduction row per type.
text
For each deduction type:
  typeBalance = billedAmount - previouslyAppliedAmount
  proportion  = typeBalance / totalRemainingBalance
  prorated    = deductionAmount * proportion

4. Field Mapping & Transformation ​

4.1 Original Worksheet → Reversal Worksheet ​

Source Table.FieldTarget Table.FieldTransform
cash_receipt_worksheet.cash_receipt_split_idcash_receipt_worksheet.cash_receipt_split_idCopied as-is
—cash_receipt_worksheet.cash_receipt_worksheet_status_cdDefaulted to 'R'
—cash_receipt_worksheet.current_item_indDefaulted to false
—cash_receipt_worksheet.worksheet_type_cdDefaulted to 'REVERSAL'
cash_receipt_worksheet.cash_receipt_worksheet_idcash_receipt_worksheet.previous_worksheet_idLinks back to original
—cash_receipt_worksheet.approved_dtSystem-generated (now)
—cash_receipt_worksheet.approved_byCurrent userId
—cash_receipt_worksheet.posting_status_cdDefaulted to 'U' (ready for GL pickup)
Input reopenReasoncash_receipt_worksheet.return_reason'Reversal of worksheet #<id>: <reason>'

4.2 Original Worksheet → Replacement Draft ​

Source Table.FieldTarget Table.FieldTransform
cash_receipt_worksheet.cash_receipt_split_idcash_receipt_worksheet.cash_receipt_split_idCopied as-is
cash_receipt_worksheet.worksheet_sequencecash_receipt_worksheet.worksheet_sequenceCopied as-is
—cash_receipt_worksheet.cash_receipt_worksheet_status_cdDefaulted to 'D' (Draft)
—cash_receipt_worksheet.current_item_indDefaulted to true
—cash_receipt_worksheet.worksheet_type_cdDefaulted to 'REPLACEMENT'
cash_receipt_worksheet.cash_receipt_worksheet_idcash_receipt_worksheet.previous_worksheet_idLinks back to original

4.3 Original Worksheet → Sealed Original ​

Source Table.FieldTarget Table.FieldTransform
—cash_receipt_worksheet.cash_receipt_worksheet_status_cdSet to 'R' (Returned)
—cash_receipt_worksheet.current_item_indSet to false
—cash_receipt_worksheet.returned_dtSystem-generated (now)
—cash_receipt_worksheet.returned_byCurrent userId
Input reopenReasoncash_receipt_worksheet.return_reasonCopied as-is
—cash_receipt_worksheet.worksheet_type_cdSet to 'ORIGINAL'
Replacement draft IDcash_receipt_worksheet.replaced_by_worksheet_idForward link to replacement

4.4 Application Records: Original → Reversal ​

Source Table.FieldTarget Table.FieldTransform
Reversal worksheet IDcash_receipt_application.cash_receipt_worksheet_idNew reversal worksheet
cash_receipt_application.billing_item_detail_idcash_receipt_application.billing_item_detail_idCopied as-is
cash_receipt_application.cash_receipt_amt_appliedcash_receipt_application.cash_receipt_amt_appliedNegated (-1 * value)
Reversal settlement ID (via mapping)cash_receipt_application.participant_settlement_idMapped from original settlement
cash_receipt_application.cash_receipt_application_idcash_receipt_application.reversal_of_application_idLinks to original
—cash_receipt_application.reversal_reason_cdDefaulted to 'WORKSHEET_REOPEN'

4.5 Application Records: Original → Replacement Draft ​

Source Table.FieldTarget Table.FieldTransform
Replacement draft IDcash_receipt_application.cash_receipt_worksheet_idNew replacement worksheet
cash_receipt_application.billing_item_detail_idcash_receipt_application.billing_item_detail_idCopied as-is
cash_receipt_application.cash_receipt_amt_appliedcash_receipt_application.cash_receipt_amt_appliedCopied as-is (positive, not negated)
New settlement ID (via mapping)cash_receipt_application.participant_settlement_idMapped to new settlement
—cash_receipt_application.reversal_of_application_idnull
—cash_receipt_application.reversal_reason_cdnull

4.6 Application Deductions: Original → Reversal ​

Source Table.FieldTarget Table.FieldTransform
Reversal application IDcash_receipt_application_deduction.cash_receipt_application_idNew reversal application
cash_receipt_application_deduction.billing_item_deduction_type_cdcash_receipt_application_deduction.billing_item_deduction_type_cdCopied as-is
cash_receipt_application_deduction.deduction_amt_appliedcash_receipt_application_deduction.deduction_amt_appliedNegated (-1 * value)

4.7 Application Deductions: Original → Replacement Draft ​

Source Table.FieldTarget Table.FieldTransform
Replacement application IDcash_receipt_application_deduction.cash_receipt_application_idNew replacement application
cash_receipt_application_deduction.billing_item_deduction_type_cdcash_receipt_application_deduction.billing_item_deduction_type_cdCopied as-is
cash_receipt_application_deduction.deduction_amt_appliedcash_receipt_application_deduction.deduction_amt_appliedCopied as-is (positive, not negated)

4.8 Client Ledger Entries: Original → Reversal ​

Source Table.FieldTarget Table.FieldTransform
Reversal worksheet IDcash_receipt_client_ledger.cash_receipt_worksheet_idNew reversal worksheet
cash_receipt_client_ledger.client_ledger_idcash_receipt_client_ledger.client_ledger_idCopied as-is
cash_receipt_client_ledger.deal_idcash_receipt_client_ledger.deal_idCopied as-is
cash_receipt_client_ledger.uta_entity_idcash_receipt_client_ledger.uta_entity_idCopied as-is
cash_receipt_client_ledger.department_idcash_receipt_client_ledger.department_idCopied as-is
cash_receipt_client_ledger.client_idcash_receipt_client_ledger.client_idCopied as-is
cash_receipt_client_ledger.cash_receipt_amt_appliedcash_receipt_client_ledger.cash_receipt_amt_appliedNegated (-1 * value)
cash_receipt_client_ledger.cash_receipt_client_ledger_idcash_receipt_client_ledger.reversal_of_ledger_idLinks to original
—cash_receipt_client_ledger.reversal_reason_cdDefaulted to 'WORKSHEET_REOPEN'

4.9 Client Ledger Entries: Original → Replacement Draft ​

Source Table.FieldTarget Table.FieldTransform
Replacement draft IDcash_receipt_client_ledger.cash_receipt_worksheet_idNew replacement worksheet
cash_receipt_client_ledger.client_ledger_idcash_receipt_client_ledger.client_ledger_idCopied as-is
cash_receipt_client_ledger.deal_idcash_receipt_client_ledger.deal_idCopied as-is
cash_receipt_client_ledger.uta_entity_idcash_receipt_client_ledger.uta_entity_idCopied as-is
cash_receipt_client_ledger.department_idcash_receipt_client_ledger.department_idCopied as-is
cash_receipt_client_ledger.client_idcash_receipt_client_ledger.client_idCopied as-is
cash_receipt_client_ledger.cash_receipt_amt_appliedcash_receipt_client_ledger.cash_receipt_amt_appliedCopied as-is (positive)
—cash_receipt_client_ledger.reversal_of_ledger_idnull
—cash_receipt_client_ledger.reversal_reason_cdnull

4.10 Payouts: Original → Reversal ​

Source Table.FieldTarget Table.FieldTransform
Reversal worksheet IDcash_receipt_payout.cash_receipt_worksheet_idNew reversal worksheet
—cash_receipt_payout.payment_item_idnull
—cash_receipt_payout.participant_settlement_item_idnull
cash_receipt_payout.payout_party_idcash_receipt_payout.payout_party_idCopied as-is
cash_receipt_payout.payment_item_namecash_receipt_payout.payment_item_namePrefixed with 'Reversal: '
cash_receipt_payout.payment_item_type_cdcash_receipt_payout.payment_item_type_cdCopied as-is
cash_receipt_payout.payment_item_amtcash_receipt_payout.payment_item_amtNegated (-1 * value)
cash_receipt_payout.payment_item_currency_cdcash_receipt_payout.payment_item_currency_cdCopied as-is
cash_receipt_payout.cash_receipt_payout_idcash_receipt_payout.reversal_of_payout_idLinks to original
—cash_receipt_payout.reversal_reason_cdDefaulted to 'WORKSHEET_REOPEN'

4.11 Payouts: Original → Replacement Draft ​

Source Table.FieldTarget Table.FieldTransform
Replacement draft IDcash_receipt_payout.cash_receipt_worksheet_idNew replacement worksheet
cash_receipt_payout.payment_item_idcash_receipt_payout.payment_item_idPreserved (link maintained)
Remapped via settlement item mappingcash_receipt_payout.participant_settlement_item_idRemapped to new settlement item ID
All other fieldsAll other fieldsCopied as-is
—cash_receipt_payout.do_not_send_indDefaulted to true (all payouts held)

4.12 Payment Items: Effects of Return Process ​

Payment Lock StatusReversal EffectReplacement Effect
Unlockeddo_not_send_ind = true; payment_execution_status_cd = 'WAITING'Re-pointed to new settlement item; do_not_send_ind = true; payment_execution_status_cd = 'WAITING'
Lockeddo_not_send_ind = true (execution status preserved)Re-pointed to new settlement item; do_not_send_ind = true (execution status preserved)

NOTE

On worksheet approval, do_not_send_ind is reset to false on unlocked payment items (unless the payout itself has do_not_send_ind = true), allowing the bank job to pick them up.

5. Cross-References ​

DocumentRelationship
Worksheets Data ModelTable definitions, columns, types, and ER diagram for all tables mutated by these procedures.
Worksheets QueriesRead queries, search patterns, and balance calculations used to validate and display worksheet state.
Settlements Data Modelparticipant_settlement and participant_settlement_item tables created and managed during the Settle and Approve transitions.
Cash Receipts Data Modelcash_receipt_split.cash_receipt_split_id → parent of worksheet creation. cash_receipt.posting_status_cd and locked_by_user_id gated by worksheet lifecycle.
Billing Items Data Modelbilling_item_detail.billing_item_detail_id — the target of each cash_receipt_application. billing_item.open_item_ind is set to false at approval when fully paid.
Settlements ProceduresPayment item lifecycle, bank transmission, and how payment_execution_status_cd drives the locking logic during returns.
Tax Withholding Data ModelTax withholding deductions recorded as cash_receipt_application_deduction entries during application.
Write-Offs Data ModelWrite-off worksheets (receipt_type_cd = 'WRITE_OFF') cannot be returned; uses separate packet recovery process.
Accounting Data ModelGL posting job picks up worksheets where posting_status_cd = 'U' (set at Apply for REV entries and client ledger; set on reversal worksheets).

Confidential. For internal use only.

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