Client Framework

Appearance

Cash Receipts Procedures ​

1. Executive Summary ​

Purpose ​

This document describes every data-mutation operation in the cash receipts domain: how cash_receipt, cash_receipt_split, cash_receipt_reference, and cash_receipt_adjustment records are created, updated, deleted, and voided. Each procedure is defined as a sequence of numbered steps specifying which records are read, which records are written, what field values are set, and what side-effects cascade to related tables. The procedures documented here are the behavioral specification from which the production system should be implemented.

Scope ​

This document covers the following procedures:

  • Cash Receipt creation — manual entry and bank-transaction sync (Living Mirror)
  • Cash Receipt update — inline field edits with posting-status guards
  • Cash Receipt amount adjustment — split-level amount changes that recalculate the receipt total
  • Cash Receipt voiding — zeroing out a receipt via the adjustment mechanism
  • Cash Receipt deletion — hard-delete of receipt and child records
  • Cash Receipt locking and unlocking — concurrency control
  • Cash Receipt Split creation — default 1:1 split and additional carve-out splits
  • Cash Receipt Split adjustment — changing a split's amount
  • Cash Receipt Split update — notes, amount, and file-link changes
  • Cash Receipt Split fund transfer — moving funds between splits atomically
  • Cash Receipt Split deletion — with fund transfer and worksheet cleanup
  • Cash Receipt Split combination — merging multiple splits into one
  • Cash Receipt Split voiding — cancelling a split during receipt zeroing
  • Cash Receipt Split tax warning update — storing withholding-tax warnings
  • Cash Receipt Adjustment creation — bank fees and corrective adjustments
  • Cash Receipt Adjustment deletion — reversing an unposted adjustment
  • Cash Receipt Transfer — moving funds to a different bank account
  • Write-Off Cash Receipt creation — system-generated receipt from packet approval
  • Write-Off Reversal Worksheet — recovery of a written-off receipt
  • Reset Receipts for GL Reposting — batch status reset

Reference documents:

2. Key Procedures ​

2.1 Create Cash Receipt (Manual) ​

Operation: createCashReceipt

Trigger: User submits a new cash receipt form with amount, currency, bank account, and deposit date fields.

Input Parameters:

  • original_receipt_amt: decimal(15,2) (required) — cash_receipt.original_receipt_amt; must be greater than zero
  • original_currency_cd: varchar(50) (required) — cash_receipt.original_currency_cd
  • currency_cd: varchar(50) (optional) — cash_receipt.currency_cd; defaults to original_currency_cd when omitted
  • fx_rate: real (conditionally required) — cash_receipt.fx_rate; required and must be > 0 when currency_cd differs from original_currency_cd
  • bank_account_id: integer (optional) — cash_receipt.bank_account_id
  • deposit_date: date (optional) — cash_receipt.deposit_date
  • cash_receipt_ref: varchar(150) (optional) — cash_receipt.cash_receipt_ref
  • cash_receipt_comment: varchar(255) (optional) — cash_receipt.cash_receipt_comment
  • actorId: String — identifier for the user performing the operation

Creates a new cash receipt from user input, computes derived amount fields, and auto-creates a default 1:1 split with a Draft worksheet.

Step 1. Validate Inputs ​

  • Source: Caller-supplied parameters.
  • Action: No database write. Validation only.
  • Logic:
    • Reject if original_receipt_amt is missing, zero, or negative.
    • Reject if original_currency_cd is missing.
    • Reject if currency_cd differs from original_currency_cd and fx_rate is missing or <= 0.

Step 2. Compute Derived Amounts ​

  • Source: Caller-supplied original_receipt_amt, fx_rate, currency_cd, original_currency_cd.
  • Action: No database write. In-memory calculation.
  • Logic:
    • If currency_cd differs from original_currency_cd and fx_rate is provided: set receipt_amt = original_receipt_amt * fx_rate, rounded to 2 decimal places.
    • Otherwise: set receipt_amt = original_receipt_amt.
    • Set net_receipt_amt = receipt_amt (no adjustments yet on creation).

Step 3. Insert Cash Receipt Record ​

  • Source: Validated and computed values from steps 1–2.
  • Action: INSERT into cash_receipt.
  • Logic:
    • Set original_receipt_amt, original_currency_cd, currency_cd, fx_rate from input.
    • Set receipt_amt, net_receipt_amt from step 2 computation.
    • Set bank_account_id, deposit_date, cash_receipt_ref, cash_receipt_comment from input.
    • Set posting_status_cd = 'U' (default — Unposted).
    • Set receipt_type_cd = 'NORMAL' (default).
    • Set created_by = actorId, updated_by = actorId, created_dt = NOW(), updated_dt = NOW().

Step 4. Create Default 1:1 Split ​

  • Source: Newly inserted cash_receipt_id and net_receipt_amt from step 3.
  • Action: Delegates to the Create Default Split procedure (see 2.2 Create Default Split).
  • Logic:
    • Calls createDefaultSplit with cash_receipt_id, net_receipt_amt, actorId.
    • One cash_receipt_split and one cash_receipt_worksheet are created as a result.

IMPORTANT

The split creation is not wrapped in a database transaction with the receipt insert. If split creation fails, the receipt exists without a split. Production implementations should wrap both operations in a single transaction.

Side-effects:

  • Creates one cash_receipt_split record with split_amt = net_receipt_amt and split_status_cd = 'N'.
  • Creates one cash_receipt_worksheet record with cash_receipt_worksheet_status_cd = 'D' and current_item_ind = true.

Postconditions:

  • cash_receipt exists with posting_status_cd = 'U' and receipt_type_cd = 'NORMAL'.
  • Exactly one cash_receipt_split exists for this receipt.
  • Exactly one cash_receipt_worksheet exists in Draft status for that split.

2.2 Create Default Split ​

Operation: createDefaultSplit

Trigger: Called internally by createCashReceipt and syncFromBankTransaction immediately after inserting a new cash_receipt record.

Input Parameters:

  • cash_receipt_id: integer (required) — cash_receipt_split.cash_receipt_id
  • receipt_amt: decimal(15,2) (required) — amount to assign to the default split, taken from cash_receipt.net_receipt_amt
  • actorId: String — identifier for the user performing the operation

Creates the initial 1:1 split for a newly created receipt, plus a Draft worksheet attached to it.

Step 1. Verify Receipt Exists ​

  • Source: cash_receipt WHERE cash_receipt_id = :cash_receipt_id.
  • Action: SELECT only. No write.
  • Logic:
    • Reject if no row found.

Step 2. Check No Splits Exist ​

  • Source: COUNT from cash_receipt_split WHERE cash_receipt_id = :cash_receipt_id.
  • Action: SELECT only. No write.
  • Logic:
    • If count > 0, abort with error "Default split already exists."

Step 3. Insert Cash Receipt Split ​

  • Source: cash_receipt_id and receipt_amt.
  • Action: INSERT into cash_receipt_split.
  • Logic:
    • Set cash_receipt_id = :cash_receipt_id.
    • Set split_sequence = 1.
    • Set split_amt = :receipt_amt.
    • Set split_status_cd = 'N' (default — New).
    • Set created_by = actorId, updated_by = actorId, created_dt = NOW(), updated_dt = NOW().

Step 4. Insert Draft Worksheet ​

  • Source: cash_receipt_split_id from step 3.
  • Action: INSERT into cash_receipt_worksheet.
  • Logic:
    • Set cash_receipt_split_id from step 3.
    • Set cash_receipt_worksheet_status_cd = 'D'.
    • Set current_item_ind = true.
    • Set created_by = actorId, updated_by = actorId.

Side-effects:

  • None beyond the two records created in steps 3 and 4.

Postconditions:

  • Exactly one cash_receipt_split exists for this receipt with split_amt = receipt_amt and split_status_cd = 'N'.
  • Exactly one cash_receipt_worksheet exists for that split with cash_receipt_worksheet_status_cd = 'D'.

2.3 Sync from Bank Transaction (Living Mirror) ​

Operation: syncFromBankTransaction

Trigger: User clicks "Generate Cash Receipt" on a bank transaction row, or the bank ingestion service processes a new file that updates a previously ingested transaction's status.

Input Parameters:

  • bank_account_id: integer (required) — cash_receipt.bank_account_id; must not be null
  • bank_reference_id: varchar(100) (required) — cash_receipt.bank_ref_id; AcctSvcrRef from the bank; must not be null
  • bank_transaction_id: integer (optional) — FK to bank_transaction for audit linkage
  • status: varchar(20) (required) — bank entry status (PDNG or BOOK); maps to cash_receipt.entry_status
  • booking_date: date (optional) — cash_receipt.booking_date
  • remittance_info: text (optional) — cash_receipt.remittance_info; snapshot, not updated after creation
  • amount: decimal(15,2) (required) — transaction amount; maps to cash_receipt.original_receipt_amt
  • currency: varchar(50) (required) — transaction currency; maps to cash_receipt.original_currency_cd
  • file_name: varchar(255) (optional) — source bank file name; maps to cash_receipt.filename
  • actorId: String — identifier for the user or system process performing the operation

Creates or updates a cash receipt from a bank transaction. If a receipt already exists for this bank reference, the procedure cascades the status change; if not, it creates a new receipt with a default split and Draft worksheet.

Step 1. Validate Required Fields ​

  • Source: Caller-supplied parameters.
  • Action: No database write. Validation only.
  • Logic:
    • Reject if bank_account_id is null.
    • Reject if bank_reference_id is null or empty.

Step 2. Look Up Existing Receipt by Composite Key ​

  • Source: cash_receipt WHERE bank_account_id = :bank_account_id AND bank_ref_id = :bank_reference_id.
  • Action: SELECT using the unique index uq_cash_receipt_bank_ref.
  • Logic:
    • If a row is found, proceed to step 3 (update branch).
    • If no row is found, proceed to step 4 (create branch).

Step 3. Update Existing Receipt (Status Cascade) ​

  • Source: Existing cash_receipt row from step 2.
  • Action: UPDATE cash_receipt if entry_status has changed; otherwise no write.
  • Logic:
    • If existing.entry_status != :status: UPDATE cash_receipt SET entry_status = :status, booking_date = :booking_date, bank_transaction_id = :bank_transaction_id, updated_by = actorId, updated_dt = NOW().
    • If existing.entry_status = :status: no mutation. Return the existing receipt as-is.

Step 4. Create New Receipt (When None Exists) ​

  • Source: Caller-supplied transaction data.
  • Action: INSERT into cash_receipt.
  • Logic:
    • Set entry_status = :status.
    • Set bank_ref_id = :bank_reference_id.
    • Set bank_transaction_id = :bank_transaction_id.
    • Set booking_date = :booking_date.
    • Set remittance_info = :remittance_info.
    • Set bank_account_id = :bank_account_id.
    • Set deposit_date = :booking_date (uses booking date as deposit date).
    • Set cash_receipt_ref = :bank_reference_id.
    • Set filename = :file_name.
    • Set original_receipt_amt = :amount.
    • Set receipt_amt = :amount (no FX conversion applied for bank-originated receipts).
    • Set net_receipt_amt = :amount.
    • Set original_currency_cd = :currency.
    • Set currency_cd = :currency.
    • Set posting_status_cd = 'U'.
    • Set receipt_type_cd = 'NORMAL'.

NOTE

Creation is not blocked for BOOK status transactions. Intraday bank files can contain already-booked items. The system creates receipts regardless of entry status.

Side-effects:

  • On new receipt creation: delegates to createDefaultSplit to create one cash_receipt_split and one Draft cash_receipt_worksheet.
  • On status update: no side-effects beyond the receipt row change.

Postconditions:

  • A cash_receipt exists for the combination (bank_account_id, bank_ref_id) with entry_status reflecting the latest bank status.
  • On new creation: one cash_receipt_split and one Draft cash_receipt_worksheet also exist.

2.4 Update Cash Receipt ​

Operation: updateCashReceipt

Trigger: User edits fields on the cash receipt detail form and saves changes.

Input Parameters:

  • cash_receipt_id: integer (required) — cash_receipt.cash_receipt_id
  • Any updatable field: type varies — field-level editability depends on posting_status_cd (see business rules below)
  • actorId: String — identifier for the user performing the operation

Updates mutable fields on an existing cash receipt, with field-level editability gated by the receipt's current posting_status_cd.

Step 1. Read Current Receipt ​

  • Source: cash_receipt WHERE cash_receipt_id = :cash_receipt_id.
  • Action: SELECT only. No write.
  • Logic:
    • Reject if no row found.

Step 2. Enforce Posting-Status Guards ​

  • Source: cash_receipt.posting_status_cd from step 1.
  • Action: No database write. Validation only.
  • Logic:
    • If posting_status_cd = 'V' (Voided): only cash_receipt_ref and cash_receipt_comment may appear in the update payload. Any other field triggers an error.
    • If posting_status_cd = 'P' (Posted): only cash_receipt_comment and net_receipt_amt (via adjustment recalculation) may appear in the update payload. Any other field triggers an error.
    • If posting_status_cd = 'U' (Unposted): all fields are editable.

Step 3. Recalculate Derived Amounts (Conditional) ​

  • Source: Updated original_receipt_amt, fx_rate, currency_cd, original_currency_cd from payload; current record from step 1.
  • Action: No database write. In-memory recalculation.
  • Logic:
    • If original_receipt_amt, fx_rate, currency_cd, or original_currency_cd changed and receipt_amt was not explicitly provided:
      • If currency_cd differs from original_currency_cd and fx_rate is present: receipt_amt = original_receipt_amt * fx_rate.
      • Otherwise: receipt_amt = original_receipt_amt.
    • net_receipt_amt is preserved from the current record unless explicitly provided in the payload (to retain existing adjustment effects).

Step 4. Update Cash Receipt Record ​

  • Source: Changed fields from payload, computed values from step 3.
  • Action: UPDATE cash_receipt SET changed fields, updated_by = actorId, updated_dt = NOW().
  • Logic:
    • Only the fields present in the update payload (plus updated_by, updated_dt) are written.

Step 5. Cascade Split Amount (Conditional) ​

  • Source: Updated net_receipt_amt; count and state of splits for this receipt.
  • Action: UPDATE cash_receipt_split SET split_amt = :new_net_receipt_amt (conditional).
  • Logic:
    • Only if the receipt amount changed AND the receipt has exactly one split AND that split has no worksheet.
    • In all other cases, skip this step.

**PoC Artifact:**

The cascade to update the single split is implemented in the server action layer rather than the service layer. Production should move this logic into the service for transactional consistency.

Side-effects:

  • When net_receipt_amt changes and the single-split cascade condition is met: cash_receipt_split.split_amt is updated to match.

Postconditions:

  • cash_receipt reflects the updated field values within the permissions allowed by posting_status_cd.

2.5 Adjust Receipt Amount ​

Operation: adjustReceiptAmount

Trigger: User uses the "Adjust Amount" action on a receipt and enters a new amount for a specific split.

Input Parameters:

  • cash_receipt_id: integer (required) — cash_receipt.cash_receipt_id
  • split_id: integer (required) — cash_receipt_split.cash_receipt_split_id; the split whose amount is being changed
  • new_split_amount: decimal(15,2) (required) — the desired new amount for the split; may be zero
  • original_receipt_amt: decimal(15,2) (optional) — override for cash_receipt.original_receipt_amt
  • actorId: String — identifier for the user performing the operation

Changes a receipt's total by adjusting a specific split's amount and recalculating the receipt total from all splits. Zeroing all splits voids the receipt. This procedure replaces a separate "void receipt" action.

Step 1. Validate Receipt Is Not Voided ​

  • Source: cash_receipt WHERE cash_receipt_id = :cash_receipt_id.
  • Action: SELECT only. No write.
  • Logic:
    • Reject if posting_status_cd = 'V'.

Step 2. Validate Split Belongs to Receipt ​

  • Source: All splits with worksheets for the receipt.
  • Action: SELECT only. No write.
  • Logic:
    • Locate the split with cash_receipt_split_id = :split_id in the result set.
    • Reject if not found.

Step 3. Enforce Draft-Only Worksheet Constraint ​

  • Source: Worksheet associated with the target split from step 2.
  • Action: No write.
  • Logic:
    • If the split has a worksheet and cash_receipt_worksheet_status_cd != 'D', abort with an error naming the current status.
    • This constraint is stricter than adjustSplitAmount, which also permits Approved ('A') worksheets.

Step 4. Branch: Zero Amount with Multiple Splits ​

  • Source: new_split_amount from caller; count of splits from step 2 result.
  • Action: Conditional delegation.
  • Logic:
    • If new_split_amount = 0 AND more than one split exists:
      • Call adjustSplitAmount with new_amount = '0' for the target split.
      • Call deleteSplit without a target (since amount is now $0).
    • Otherwise proceed to step 5.

Step 5. Normal Adjustment ​

  • Source: new_split_amount from caller.
  • Action: Delegates to adjustSplitAmount for the target split.
  • Logic:

Step 6. Recalculate Receipt Total ​

  • Source: Re-read all non-voided splits for the receipt after step 4 or 5.
  • Action: SELECT only. No write at this step.
  • Logic:
    • new_receipt_total = SUM(split_amt) across all remaining splits.

Step 7. Void Receipt If Total Is Zero ​

  • Source: new_receipt_total from step 6.
  • Action: Conditional updates.
  • Logic:
    • If new_receipt_total = 0:
      • For each remaining split, call voidSplit (see 2.11 Void Split).
      • Add posting_status_cd = 'V' to the receipt update in step 8.

Step 8. Update Receipt Amounts ​

  • Source: new_receipt_total from step 6; original_receipt_amt from caller (if provided).
  • Action: UPDATE cash_receipt SET receipt_amt = :new_receipt_total, net_receipt_amt = :new_receipt_total, updated_by = actorId, updated_dt = NOW().
  • Logic:
    • If original_receipt_amt was provided, also set original_receipt_amt = :original_receipt_amt.
    • If posting_status_cd = 'V' was flagged in step 7, also set posting_status_cd = 'V'.

IMPORTANT

There is no standalone "void receipt" action. Voiding is the consequence of reducing the receipt total to zero. The split and adjustment records remain as an audit trail explaining why the receipt was cancelled.

Side-effects:

  • cash_receipt_split.split_amt is updated for the target split.
  • If total reaches zero: all remaining splits are set to split_status_cd = 'V'; empty Draft worksheets are deleted.
  • cash_receipt.receipt_amt and cash_receipt.net_receipt_amt are updated.
  • If total reaches zero: cash_receipt.posting_status_cd is set to 'V'.

Postconditions:

  • cash_receipt.net_receipt_amt equals the sum of all non-voided split amounts.
  • If zeroed out: cash_receipt.posting_status_cd = 'V' and all splits have split_status_cd = 'V'.

2.6 Create Additional Split (Carve-Out) ​

Operation: createSplit

Trigger: User initiates a "Re-Split" action on a receipt and specifies a new split amount and source split.

Input Parameters:

  • cash_receipt_id: integer (required) — cash_receipt_split.cash_receipt_id
  • amount: decimal(15,2) (required) — cash_receipt_split.split_amt for the new split; must be > 0
  • source_split_id: integer (required) — cash_receipt_split.cash_receipt_split_id of the split to carve from
  • notes: text (optional) — cash_receipt_split.notes for the new split
  • source_file_upload_id: integer (optional) — cash_receipt_split.source_file_upload_id
  • actorId: String — identifier for the user performing the operation

Creates a new split by carving funds from an existing source split. The source split's amount is reduced accordingly. If the source is fully emptied, it is automatically deleted.

Step 1. Validate Amount Is Positive ​

  • Source: Caller-supplied amount.
  • Action: No write.
  • Logic:
    • Reject if amount <= 0 or is not a valid number.

Step 2. Validate Source Split Can Be Modified ​

  • Source: cash_receipt_split and associated cash_receipt_worksheet for source_split_id.
  • Action: No write.
  • Logic:
    • Apply the Split Modifiability rule (see 3.1 Split Modifiability).
    • Reject if the source split is voided or its worksheet is in Applied, Settled, Returned, or Submitted status.

Step 3. Validate Sufficient Available Balance ​

  • Source: cash_receipt_split.split_amt and approved applications sum for source_split_id.
  • Action: No write.
  • Logic:
    • available_balance = split_amt - sum_of_approved_applications.
    • Reject if available_balance < amount.

Step 4. Read Source Split ​

  • Source: cash_receipt_split WHERE cash_receipt_split_id = :source_split_id.
  • Action: SELECT only.
  • Logic:
    • Capture split_amt.
    • Calculate new_source_amount = split_amt - amount.

Step 5. Branch: Source Would Be Emptied (new_source_amount < 0.01) ​

  • Source: Source worksheet status and applied amount.
  • Action: Conditional creates and deletes.
  • Logic:
    • Validate that the source worksheet (if any) is in Draft status with no applications. If not, reject.
    • INSERT cash_receipt_split with split_amt = :amount, split_status_cd = 'N', parent_split_id = :source_split_id, split_sequence = MAX(split_sequence) + 1, notes, source_file_upload_id.
    • INSERT cash_receipt_worksheet for the new split with cash_receipt_worksheet_status_cd = 'D', current_item_ind = true.
    • DELETE cash_receipt_worksheet for the source split (if it exists).
    • DELETE cash_receipt_split WHERE cash_receipt_split_id = :source_split_id.

Step 6. Branch: Normal Carve-Out (Source Retains Funds) ​

  • Source: new_source_amount from step 4.
  • Action: UPDATE source split; INSERT new split and worksheet.
  • Logic:
    • UPDATE cash_receipt_split SET split_amt = :new_source_amount, updated_by = actorId, updated_dt = NOW() WHERE cash_receipt_split_id = :source_split_id.
    • INSERT cash_receipt_split with split_amt = :amount, split_status_cd = 'N', parent_split_id = :source_split_id, split_sequence = MAX + 1, notes, source_file_upload_id.
    • INSERT cash_receipt_worksheet for the new split with cash_receipt_worksheet_status_cd = 'D', current_item_ind = true.

Step 7. Validate Split Totals (Sanity Check) ​

  • Source: cash_receipt.net_receipt_amt; SUM of cash_receipt_split.split_amt for the receipt.
  • Action: SELECT only. No write.
  • Logic:
    • Log a warning if ABS(net_receipt_amt - SUM(split_amt)) >= 0.005.

Side-effects:

  • Source split split_amt is reduced (or source split is deleted if fully emptied).
  • New cash_receipt_split is created with split_status_cd = 'N'.
  • New cash_receipt_worksheet is created in Draft status for the new split.

Postconditions:

  • A new split exists with split_amt = :amount and a Draft worksheet.
  • Total of all splits for the receipt still equals cash_receipt.net_receipt_amt within tolerance of 0.005.
  • The source split either has a reduced amount or has been removed.

2.7 Adjust Split Amount ​

Operation: adjustSplitAmount

Trigger: User directly changes the amount on an individual split, typically via the split edit form.

Input Parameters:

  • split_id: integer (required) — cash_receipt_split.cash_receipt_split_id
  • new_amount: decimal(15,2) (required) — target cash_receipt_split.split_amt; must be >= 0
  • actorId: String — identifier for the user performing the operation

Changes a single split's amount directly. Used for amount corrections, not for transfers between splits.

Step 1. Validate Amount Is Non-Negative ​

  • Source: Caller-supplied new_amount.
  • Action: No write.
  • Logic:
    • Reject if new_amount < 0 or is not a valid number.

Step 2. Validate Split Can Be Modified ​

  • Source: cash_receipt_split and associated worksheet for split_id.
  • Action: No write.
  • Logic:

Step 3. Read Current Split ​

  • Source: cash_receipt_split WHERE cash_receipt_split_id = :split_id.
  • Action: SELECT only.
  • Logic:
    • Capture split_amt.

Step 4. Validate Floor Constraint ​

  • Source: Available balance for the split (from approved applications sum on the worksheet).
  • Action: No write.
  • Logic:
    • applied_amt = current_split_amt - available_balance.
    • Reject if new_amount < applied_amt.

Step 5. Update Split Amount ​

  • Source: new_amount from caller.
  • Action: UPDATE cash_receipt_split SET split_amt = :new_amount, updated_by = actorId, updated_dt = NOW() WHERE cash_receipt_split_id = :split_id.
  • Logic:
    • No other fields are changed.

Side-effects:

  • None beyond the split_amt update.

Postconditions:

  • cash_receipt_split.split_amt = :new_amount.
  • The split amount is at or above the applied amount floor.

2.8 Update Split ​

Operation: updateSplit

Trigger: User edits split details (notes, amount, or document attachment) on the split form and saves.

Input Parameters:

  • split_id: integer (required) — cash_receipt_split.cash_receipt_split_id
  • amount: decimal(15,2) (optional) — new cash_receipt_split.split_amt
  • notes: text (optional) — cash_receipt_split.notes
  • source_file_upload_id: integer (optional) — cash_receipt_split.source_file_upload_id
  • actorId: String — identifier for the user performing the operation

Updates split details. Notes-only updates are allowed even on voided splits; amount or file-link changes require the split to be modifiable.

Step 1. Validate Split Can Be Modified ​

  • Source: cash_receipt_split and associated worksheet for split_id.
  • Action: No write.
  • Logic:
    • If only notes is changing (no amount or source_file_upload_id): allow even if split_status_cd = 'V' (notes-only exception).
    • Otherwise apply the full Split Modifiability rule (see 3.1 Split Modifiability).

Step 2. Validate Amount Floor (Conditional) ​

  • Source: Approved applications sum for the split's worksheet.
  • Action: No write.
  • Logic:
    • Only if amount is provided.
    • Reject if amount < 0.
    • Reject if amount < applied_amt (same floor constraint as 2.7 Adjust Split Amount).

Step 3. Update Split ​

  • Source: Provided field values.
  • Action: UPDATE cash_receipt_split SET provided fields, updated_by = actorId, updated_dt = NOW() WHERE cash_receipt_split_id = :split_id.
  • Logic:
    • Only the fields included in the input are written.

Side-effects:

  • None beyond the updated fields on cash_receipt_split.

Postconditions:

  • cash_receipt_split reflects the updated amount, notes, and/or source_file_upload_id as supplied.

2.9 Transfer Funds Between Splits ​

Operation: transferFunds

Trigger: User selects a source split, specifies a target split, and enters a transfer amount on the fund-transfer form.

Input Parameters:

  • from_split_id: integer (required) — source cash_receipt_split.cash_receipt_split_id
  • to_split_id: integer (required) — target cash_receipt_split.cash_receipt_split_id
  • amount: decimal(15,2) (required) — transfer amount; must be > 0
  • actorId: String — identifier for the user performing the operation

Atomically moves funds from one split to another within the same receipt. If the source is emptied, the system attempts to auto-delete it.

Step 1. Validate Amount Is Positive ​

  • Source: Caller-supplied amount.
  • Action: No write.
  • Logic:
    • Reject if amount <= 0 or is not a valid number.

Step 2. Validate Both Splits Can Be Modified ​

  • Source: Each split and its associated worksheet.
  • Action: No write.
  • Logic:
    • Apply Split Modifiability rule (see 3.1 Split Modifiability) independently for source and target.
    • Reject with a labeled error message if either is blocked.

Step 3. Validate Source Has Sufficient Available Balance ​

  • Source: Available balance for from_split_id.
  • Action: No write.
  • Logic:
    • available_balance >= amount.
    • Reject if insufficient.

Step 4. Read Both Splits ​

  • Source: cash_receipt_split WHERE cash_receipt_split_id IN (:from_split_id, :to_split_id).
  • Action: SELECT only.
  • Logic:
    • Verify both rows exist.
    • Reject if from_split.cash_receipt_id != to_split.cash_receipt_id.

Step 5. Calculate New Amounts ​

  • Source: split_amt values from step 4.
  • Action: No database write. In-memory.
  • Logic:
    • new_from_amount = from_split.split_amt - amount.
    • new_to_amount = to_split.split_amt + amount.

Step 6. Perform Atomic Transfer ​

  • Source: New amounts from step 5.
  • Action: Two UPDATEs within a single database transaction.
  • Logic:
    • UPDATE cash_receipt_split SET split_amt = :new_from_amount, updated_by = actorId, updated_dt = NOW() WHERE cash_receipt_split_id = :from_split_id.
    • UPDATE cash_receipt_split SET split_amt = :new_to_amount, updated_by = actorId, updated_dt = NOW() WHERE cash_receipt_split_id = :to_split_id.

IMPORTANT

Both updates execute within a single database transaction. If either fails, both are rolled back. This prevents funds from being lost between splits.

Step 7. Auto-Delete Empty Source (Conditional) ​

  • Source: new_from_amount from step 5; source worksheet status and applied amount.
  • Action: Conditional DELETEs.
  • Logic:
    • Only if new_from_amount < 0.01 AND more than one split exists for the receipt.
    • Check source worksheet: if it exists, it must be in Draft status with no applications.
    • If deletable: DELETE cash_receipt_worksheet for the source (if exists), then DELETE cash_receipt_split WHERE cash_receipt_split_id = :from_split_id.
    • If not deletable: source remains as a zero-amount split with its worksheet intact.

Side-effects:

  • cash_receipt_split.split_amt is reduced on from_split and increased on to_split.
  • If source is emptied and deletable: the source cash_receipt_split and its Draft worksheet are deleted.

Postconditions:

  • Total of all splits for the receipt is unchanged (transfer is zero-sum).
  • to_split.split_amt has increased by amount; from_split.split_amt has decreased by amount (or source split no longer exists).

2.10 Delete Split ​

Operation: deleteSplit

Trigger: User selects a split and chooses the "Delete Split" action, optionally specifying a target split to receive any remaining funds.

Input Parameters:

  • split_id: integer (required) — cash_receipt_split.cash_receipt_split_id
  • target_split_id: integer (conditionally required) — target cash_receipt_split.cash_receipt_split_id to receive funds; required if split_amt >= 0.01
  • actorId: String — identifier for the user performing the operation

Removes a split from a receipt, transferring any remaining funds to a target split first if needed.

Step 1. Read Split ​

  • Source: cash_receipt_split WHERE cash_receipt_split_id = :split_id.
  • Action: SELECT only.
  • Logic:
    • Reject if not found.

Step 2. Enforce Last-Split Protection ​

  • Source: COUNT of splits for split.cash_receipt_id.
  • Action: SELECT only. No write.
  • Logic:
    • Reject if count <= 1 (cannot delete the last split for a receipt).

Step 3. Transfer Funds (Conditional) ​

  • Source: split_amt from step 1; target_split from cash_receipt_split.
  • Action: UPDATE cash_receipt_split for target split (conditional).
  • Logic:
    • If split_amt >= 0.01:
      • Reject if target_split_id is not provided.
      • Read target split; reject if it belongs to a different receipt.
      • UPDATE cash_receipt_split SET split_amt = target.split_amt + split.split_amt, updated_by = actorId, updated_dt = NOW() WHERE cash_receipt_split_id = :target_split_id.

Step 4. Check and Delete Worksheet ​

  • Source: cash_receipt_worksheet WHERE cash_receipt_split_id = :split_id.
  • Action: SELECT; conditional DELETE.
  • Logic:
    • If a worksheet exists:
      • Reject if cash_receipt_worksheet_status_cd != 'D'.
      • Reject if total applied for worksheet > 0.
      • DELETE cash_receipt_worksheet WHERE cash_receipt_worksheet_id = :worksheet_id.

Step 5. Delete Split ​

  • Source: split_id.
  • Action: DELETE from cash_receipt_split WHERE cash_receipt_split_id = :split_id.
  • Logic:
    • cash_receipt_reference rows linked to this split are deleted first (cascade or explicit delete).

Side-effects:

  • If split had funds: target_split.split_amt is increased.
  • Any cash_receipt_reference records for this split are deleted.
  • The Draft worksheet (if any) is deleted.

Postconditions:

  • cash_receipt_split WHERE cash_receipt_split_id = :split_id no longer exists.
  • Total of all remaining splits for the receipt equals cash_receipt.net_receipt_amt.

2.11 Void Split ​

Operation: voidSplit

Trigger: Called internally by adjustReceiptAmount when the receipt total reaches zero and all splits must be cancelled.

Input Parameters:

  • split_id: integer (required) — cash_receipt_split.cash_receipt_split_id
  • actorId: String — identifier for the user or system process performing the operation

Cancels a split by setting its status to Void and amount to zero. Deletes the associated Draft worksheet if it has no applications.

Step 1. Read Split ​

  • Source: cash_receipt_split WHERE cash_receipt_split_id = :split_id.
  • Action: SELECT only.
  • Logic:
    • Reject if not found.

Step 2. Clean Up Empty Draft Worksheet (Conditional) ​

  • Source: cash_receipt_worksheet WHERE cash_receipt_split_id = :split_id.
  • Action: Conditional DELETE.
  • Logic:
    • If a worksheet exists AND cash_receipt_worksheet_status_cd = 'D' AND total applied for worksheet = 0:
      • DELETE cash_receipt_worksheet WHERE cash_receipt_worksheet_id = :worksheet_id.

Step 3. Set Split to Void ​

  • Source: split_id.
  • Action: UPDATE cash_receipt_split SET split_status_cd = 'V', split_amt = '0.00', updated_by = actorId, updated_dt = NOW() WHERE cash_receipt_split_id = :split_id.
  • Logic:
    • Both split_status_cd and split_amt are updated in a single operation.

Side-effects:

  • Empty Draft worksheet is deleted if it existed.

Postconditions:

  • cash_receipt_split.split_status_cd = 'V' and split_amt = '0.00'.
  • No Draft worksheet with zero applications exists for this split.

2.12 Combine Splits ​

Operation: combineSplits

Trigger: User selects two or more splits on the receipt detail and chooses the "Combine Splits" action.

Input Parameters:

  • split_ids: integer[] (required) — array of cash_receipt_split.cash_receipt_split_id values; minimum 2 elements
  • actorId: String — identifier for the user performing the operation

Merges two or more splits into a single split. All selected splits must have no worksheet. The split with the lowest sequence number is kept; others are deleted.

Step 1. Validate Minimum Count ​

  • Source: split_ids.
  • Action: No write.
  • Logic:
    • Reject if split_ids contains fewer than 2 elements.

Step 2. Fetch All Splits ​

  • Source: cash_receipt_split WHERE cash_receipt_split_id IN (:split_ids).
  • Action: SELECT only.
  • Logic:
    • Reject if the number of rows returned does not equal the number of IDs requested.
    • Reject if all splits do not share the same cash_receipt_id.

Step 3. Verify No Worksheets Exist ​

  • Source: cash_receipt_worksheet WHERE cash_receipt_split_id = each split ID.
  • Action: SELECT only. No write.
  • Logic:
    • For each split, check for any associated worksheet.
    • Reject with a specific error identifying which split is blocked if any worksheet is found.

Step 4. Determine Keeper Split and Combined Values ​

  • Source: Fetched splits from step 2.
  • Action: No write. In-memory.
  • Logic:
    • Sort splits by split_sequence ASC. The first split (lowest sequence) is the keeper.
    • combined_amount = SUM(split_amt) across all selected splits.
    • combined_notes = JOIN(non-empty notes values, ' | ').

Step 5. Update Keeper Split ​

  • Source: combined_amount and combined_notes from step 4.
  • Action: UPDATE cash_receipt_split SET split_amt = :combined_amount, notes = :combined_notes, updated_by = actorId, updated_dt = NOW() WHERE cash_receipt_split_id = :keeper_id.
  • Logic:
    • No other fields on the keeper are changed.

Step 6. Delete Non-Keeper Splits ​

  • Source: All split IDs excluding the keeper.
  • Action: For each non-keeper split: DELETE cash_receipt_reference WHERE cash_receipt_split_id = :split_id; then DELETE cash_receipt_split WHERE cash_receipt_split_id = :split_id.
  • Logic:
    • References are deleted before the split to maintain referential integrity.

Side-effects:

  • All non-keeper cash_receipt_split records are permanently deleted.
  • All cash_receipt_reference records for non-keeper splits are deleted.

Postconditions:

  • Only the keeper split remains; it holds the combined amount and concatenated notes.
  • Total of all splits for the receipt still equals cash_receipt.net_receipt_amt.

2.13 Update Split Tax Warning ​

Operation: updateSplitTaxWarning

Trigger: A party reference is added to a split and the tax service evaluates the party's withholding tax status, or the tax warning is explicitly cleared.

Input Parameters:

  • split_id: integer (required) — cash_receipt_split.cash_receipt_split_id
  • warning: object or null (required) — structured payload { warnings: [...], calculatedDt, partyId, jurisdiction }, or null to clear
  • actorId: String — identifier for the user or system performing the operation

Stores or clears withholding-tax warning data on a split.

Step 1. Read Split ​

  • Source: cash_receipt_split WHERE cash_receipt_split_id = :split_id.
  • Action: SELECT only.
  • Logic:
    • Reject if not found.

Step 2. Update Tax Warning Fields ​

  • Source: warning payload from caller.
  • Action: UPDATE cash_receipt_split SET tax_warning_ind, tax_warning_json, updated_by = actorId, updated_dt = NOW() WHERE cash_receipt_split_id = :split_id.
  • Logic:
    • Set tax_warning_ind = true if warning is not null and warning.warnings has at least one entry; otherwise false.
    • Set tax_warning_json = :warning (the full JSON payload), or null to clear.

Side-effects:

  • None beyond the updated fields on cash_receipt_split.

Postconditions:

  • cash_receipt_split.tax_warning_ind and cash_receipt_split.tax_warning_json reflect the current warning state.

2.14 Create Adjustment ​

Operation: createAdjustment

Trigger: User adds a bank fee or corrective adjustment on the receipt detail form.

Input Parameters:

  • cash_receipt_id: integer (required) — cash_receipt_adjustment.cash_receipt_id
  • cash_receipt_split_id: integer (required) — the split to reduce
  • adjustment_amt: decimal(15,2) (required) — cash_receipt_adjustment.adjustment_amt; must be > 0
  • comment: varchar(255) (required) — cash_receipt_adjustment.comment; must be non-empty
  • actorId: String — identifier for the user performing the operation

Creates a fee or corrective adjustment on a receipt, reducing both the associated split's amount and the receipt's net amount.

Step 1. Validate Inputs ​

  • Source: Caller-supplied parameters.
  • Action: No write.
  • Logic:
    • Reject if adjustment_amt <= 0.
    • Reject if comment is empty.

Step 2. Validate Receipt Is Not Voided ​

  • Source: cash_receipt WHERE cash_receipt_id = :cash_receipt_id.
  • Action: SELECT only.
  • Logic:
    • Reject if posting_status_cd = 'V'.

Step 3. Validate Split Belongs to Receipt ​

  • Source: cash_receipt_split WHERE cash_receipt_split_id = :cash_receipt_split_id.
  • Action: SELECT only.
  • Logic:
    • Reject if cash_receipt_id on the split does not match :cash_receipt_id.

Step 4. Validate Worksheet Status ​

  • Source: cash_receipt_worksheet for the split.
  • Action: SELECT only.
  • Logic:
    • Reject if a worksheet exists and cash_receipt_worksheet_status_cd != 'D'.

Step 5. Validate Adjustment Does Not Exceed Split ​

  • Source: cash_receipt_split.split_amt; approved applications sum from the worksheet.
  • Action: No write.
  • Logic:
    • Reject if split_amt - adjustment_amt < 0.
    • If the split has a worksheet, also reject if split_amt - adjustment_amt < applied_amt (with 0.005 tolerance).

Step 6. Insert Adjustment Record ​

  • Source: Validated input values.
  • Action: INSERT into cash_receipt_adjustment.
  • Logic:
    • Set cash_receipt_id = :cash_receipt_id.
    • Set adjustment_type_cd = 'ADJ'.
    • Set adjustment_amt = :adjustment_amt.
    • Set comment = :comment.
    • Set posting_status_cd = 'U'.
    • Set created_by = actorId, updated_by = actorId.

Step 7. Reduce Split Amount ​

  • Source: adjustment_amt from caller; current split_amt.
  • Action: UPDATE cash_receipt_split SET split_amt = split_amt - :adjustment_amt, updated_by = actorId, updated_dt = NOW() WHERE cash_receipt_split_id = :cash_receipt_split_id.
  • Logic:
    • Direct arithmetic update.

Step 8. Recalculate Receipt Net Amount ​

  • Source: SUM of cash_receipt_adjustment.adjustment_amt WHERE cash_receipt_id = :cash_receipt_id.
  • Action: UPDATE cash_receipt SET net_receipt_amt = :calculated_net, updated_by = actorId, updated_dt = NOW().
  • Logic:
    • net_receipt_amt = receipt_amt - SUM(adjustment_amt) across all adjustments for the receipt.

Side-effects:

  • cash_receipt_split.split_amt is decreased by adjustment_amt.
  • cash_receipt.net_receipt_amt is decreased accordingly.

Postconditions:

  • cash_receipt_adjustment record exists with posting_status_cd = 'U'.
  • cash_receipt_split.split_amt reflects the reduced amount.
  • cash_receipt.net_receipt_amt = cash_receipt.receipt_amt - SUM(adjustment_amt).

2.15 Delete Adjustment ​

Operation: deleteAdjustment

Trigger: User removes an adjustment from the receipt detail form.

Input Parameters:

  • cash_receipt_adjustment_id: integer (required) — cash_receipt_adjustment.cash_receipt_adjustment_id
  • cash_receipt_id: integer (required) — cash_receipt.cash_receipt_id
  • cash_receipt_split_id: integer (required) — the split to restore
  • actorId: String — identifier for the user performing the operation

Removes an unposted adjustment and restores the associated split's amount.

Step 1. Validate Receipt Is Not Voided ​

  • Source: cash_receipt WHERE cash_receipt_id = :cash_receipt_id.
  • Action: SELECT only.
  • Logic:
    • Reject if posting_status_cd = 'V'.

Step 2. Validate Adjustment Can Be Deleted ​

  • Source: cash_receipt_adjustment WHERE cash_receipt_adjustment_id = :cash_receipt_adjustment_id.
  • Action: SELECT only.
  • Logic:
    • Reject if not found.
    • Reject if posting_status_cd = 'P' (posted adjustments are immutable).
    • Reject if adjustment_type_cd = 'TR' (transfer adjustments cannot be deleted).

Step 3. Restore Split Amount ​

  • Source: adjustment_amt from step 2.
  • Action: UPDATE cash_receipt_split SET split_amt = split_amt + :adjustment_amt, updated_by = actorId, updated_dt = NOW() WHERE cash_receipt_split_id = :cash_receipt_split_id.
  • Logic:
    • Reverses the reduction applied when the adjustment was created.

Step 4. Delete Adjustment Record ​

  • Source: cash_receipt_adjustment_id.
  • Action: DELETE from cash_receipt_adjustment WHERE cash_receipt_adjustment_id = :cash_receipt_adjustment_id.
  • Logic:
    • Hard delete.

Step 5. Recalculate Receipt Net Amount ​

  • Source: SUM of remaining cash_receipt_adjustment.adjustment_amt for the receipt.
  • Action: UPDATE cash_receipt SET net_receipt_amt = :recalculated_net, updated_by = actorId, updated_dt = NOW().
  • Logic:

Side-effects:

  • cash_receipt_split.split_amt is restored by the deleted adjustment amount.
  • cash_receipt.net_receipt_amt is increased accordingly.

Postconditions:

  • cash_receipt_adjustment WHERE cash_receipt_adjustment_id = :id no longer exists.
  • cash_receipt.net_receipt_amt reflects the removal of the deleted adjustment.

2.16 Create Transfer (Inter-Bank) ​

Operation: createTransfer

Trigger: User initiates a "Transfer" action on a receipt, specifying a destination bank account and transfer amount.

Input Parameters:

  • cash_receipt_id: integer (required) — source cash_receipt.cash_receipt_id
  • cash_receipt_split_id: integer (required) — source split to debit
  • transfer_amt: decimal(15,2) (required) — amount to transfer; must be > 0
  • dest_bank_account_id: integer (required) — destination bank_account.bank_account_id
  • actorId: String — identifier for the user performing the operation

Transfers funds from one receipt to a new receipt on a different bank account, with optional FX conversion.

Step 1. Validate Amount Is Positive ​

  • Source: Caller-supplied transfer_amt.
  • Action: No write.
  • Logic:
    • Reject if transfer_amt <= 0.

Step 2. Validate Source Receipt Is Not Voided ​

  • Source: cash_receipt WHERE cash_receipt_id = :cash_receipt_id.
  • Action: SELECT only.
  • Logic:
    • Reject if posting_status_cd = 'V'.

Step 3. Validate Destination Bank Account ​

  • Source: bank_account WHERE bank_account_id = :dest_bank_account_id.
  • Action: SELECT only.
  • Logic:
    • Reject if not found or active_ind = false.

Step 4. Validate Source Split and Worksheet ​

  • Source: cash_receipt_split and associated worksheet for :cash_receipt_split_id.
  • Action: SELECT only.
  • Logic:
    • Verify split belongs to the source receipt.
    • Worksheet must be in Draft status or not exist.

Step 5. Validate Transfer Does Not Exceed Available Balance ​

  • Source: Applied amount floor for the source split.
  • Action: No write.
  • Logic:
    • transfer_amt <= split_amt - applied_amt (with 0.005 tolerance).

Step 6. Compute FX Conversion ​

  • Source: Currency codes from source receipt and destination bank account; current FX rates.
  • Action: No write. In-memory.
  • Logic:
    • fx_rate = dest_currency_rate / source_currency_rate (both relative to USD).
    • dest_amount = transfer_amt * fx_rate.

Step 7. Create Transfer Adjustment on Source Receipt ​

  • Source: Computed values from step 6; destination bank name.
  • Action: INSERT into cash_receipt_adjustment.
  • Logic:
    • Set cash_receipt_id = :cash_receipt_id.
    • Set adjustment_type_cd = 'TR'.
    • Set adjustment_amt = :transfer_amt.
    • Set comment = 'Transferred to {dest_bank_name}'.
    • Set posting_status_cd = 'U'.

Step 8. Reduce Source Split Amount ​

  • Source: transfer_amt.
  • Action: UPDATE cash_receipt_split SET split_amt = split_amt - :transfer_amt, updated_by = actorId, updated_dt = NOW() WHERE cash_receipt_split_id = :cash_receipt_split_id.
  • Logic:
    • Direct arithmetic update.

Step 9. Recalculate Source Receipt Net Amount ​

  • Source: SUM of adjustments for source receipt.
  • Action: UPDATE cash_receipt on source.
  • Logic:

Step 10. Destination Receipt ​

This will come in from the destination bank as a new deposit.

2.17 Lock / Unlock Cash Receipt ​

Operation: lockCashReceipt / unlockCashReceipt

Trigger: User opens a cash receipt for editing (lock) or closes/saves the form (unlock).

Input Parameters:

  • cash_receipt_id: integer (required) — cash_receipt.cash_receipt_id
  • user_id: integer (required for lock) — cash_receipt.locked_by_user_id; the user claiming the lock
  • actorId: String — identifier for the user performing the operation

Provides optimistic concurrency control by recording which user is currently editing a receipt.

Step 1. Lock ​

  • Source: cash_receipt_id, user_id.
  • Action: UPDATE cash_receipt SET locked_by_user_id = :user_id, updated_by = actorId, updated_dt = NOW() WHERE cash_receipt_id = :cash_receipt_id.
  • Logic:
    • locked_by_user_id is set to the requesting user's ID.

Step 2. Unlock ​

  • Source: cash_receipt_id.
  • Action: UPDATE cash_receipt SET locked_by_user_id = NULL, updated_by = actorId, updated_dt = NOW() WHERE cash_receipt_id = :cash_receipt_id.
  • Logic:
    • locked_by_user_id is cleared to NULL.

Side-effects:

  • None beyond the locked_by_user_id field change.

Postconditions:

  • After lock: cash_receipt.locked_by_user_id = :user_id.
  • After unlock: cash_receipt.locked_by_user_id IS NULL.

2.18 Delete Cash Receipt ​

Operation: deleteCashReceipt

Trigger: User selects a receipt and chooses the "Delete" action.

Input Parameters:

  • cash_receipt_id: integer (required) — cash_receipt.cash_receipt_id
  • actorId: String — identifier for the user performing the operation

Hard-deletes a cash receipt and its child records. Only succeeds if the receipt has no cash applications.

Step 1. Verify Receipt Exists ​

  • Source: cash_receipt WHERE cash_receipt_id = :cash_receipt_id.
  • Action: SELECT only.
  • Logic:
    • Reject if not found.

Step 2. Delete Receipt ​

  • Source: cash_receipt_id.
  • Action: DELETE from cash_receipt WHERE cash_receipt_id = :cash_receipt_id.
  • Logic:
    • If the delete affects zero rows (e.g., due to FK constraints from cash_receipt_application records), return error "Cannot delete cash receipt with applications."

**PoC Artifact:**

The PoC uses a simple hard-delete with no explicit cascade cleanup of splits, worksheets, or references before the receipt delete. Database FK constraints (ON DELETE CASCADE or RESTRICT) determine the actual behavior. Production should explicitly delete child records in the correct order or use a soft-delete pattern.

Side-effects:

  • Cascades to related child records depending on FK constraint configuration.

Postconditions:

  • cash_receipt WHERE cash_receipt_id = :cash_receipt_id no longer exists.

2.19 Create Write-Off Cash Receipt ​

Operation: createWriteOffCashReceipt

Trigger: A write-off packet transitions to approved status, triggering the write-off service to create the corresponding receipt.

Input Parameters:

  • packet_id: varchar (required) — write_off_packet.write_off_packet_id
  • actorId: String — identifier for the system process performing the operation

System-generated receipt created when a write-off packet is approved. Creates a receipt, split, auto-approved worksheet, and applications for all receivables in the packet.

Step 1. Fetch Packet Receivables ​

  • Source: packet_receivable WHERE write_off_packet_id = :packet_id.
  • Action: SELECT only.
  • Logic:
    • Collect all billing item references in the packet.

Step 2. Build Billing Item Map ​

  • Source: billing_item and billing_item_detail for each receivable.
  • Action: SELECT only.
  • Logic:
    • For each billing item, fetch the REV detail balance and PAY detail balance.

Step 3. Calculate Total Receipt Amount ​

  • Source: Billing item balances from step 2.
  • Action: No write. In-memory.
  • Logic:
    • total = SUM(rev_balance + pay_balance) across all billing items.

Step 4. Create Cash Receipt ​

  • Source: total from step 3; currency from billing items.
  • Action: INSERT into cash_receipt.
  • Logic:
    • Set posting_status_cd = 'U'.
    • Set receipt_type_cd = 'WRITE_OFF'.
    • Set original_receipt_amt = :total.
    • Set receipt_amt = :total.
    • Set net_receipt_amt = :total.
    • Set currency_cd from billing items.
    • Set cash_receipt_comment = 'Write-off for packet'.
  • Source: New cash_receipt_id from step 4.
  • Action: UPDATE write_off_packet SET cash_receipt_id = :new_receipt_id.
  • Logic:
    • Establishes the 1:1 link between packet and receipt.

Step 6. Create Split ​

  • Source: cash_receipt_id from step 4; total from step 3.
  • Action: INSERT into cash_receipt_split.
  • Logic:
    • Set split_amt = :total.
    • Set split_status_cd = 'U' (Unposted — write-off splits are not subject to the standard N/S/A workflow).
    • Set split_sequence = 1.

Step 7. Create Auto-Approved Worksheet ​

  • Source: cash_receipt_split_id from step 6.
  • Action: INSERT into cash_receipt_worksheet; UPDATE to set approval metadata.
  • Logic:
    • INSERT with cash_receipt_worksheet_status_cd = 'A' (Approved — skips Draft/Applied/Settled for write-offs).
    • Set current_item_ind = true.
    • UPDATE to set approved_dt = NOW(), approved_by = actorId.

Step 8. Create Applications for Each Billing Item ​

  • Source: Billing item balances from step 2; cash_receipt_worksheet_id from step 7.
  • Action: INSERT into cash_receipt_application for each balance > 0.
  • Logic:
    • If REV balance >= 0.01: INSERT cash_receipt_application with billing_item_detail_id = :rev_detail_id, cash_receipt_amt_applied = :rev_balance.
    • If PAY balance >= 0.01: INSERT cash_receipt_application with billing_item_detail_id = :pay_detail_id, cash_receipt_amt_applied = :pay_balance.

Step 9. Close Billing Items ​

  • Source: Each billing_item and its REV/PAY details.
  • Action: UPDATE billing_item and billing_item_detail for each item.
  • Logic:
    • UPDATE billing_item SET open_item_ind = false.
    • UPDATE billing_item_detail (REV) SET write_off_status_cd = 'WRITTEN_OFF', write_off_packet_id = :packet_id, write_off_dt = NOW(), exclude_from_cecl_ind = true.
    • UPDATE billing_item_detail (PAY) SET write_off_status_cd = 'WRITTEN_OFF', write_off_packet_id = :packet_id, write_off_dt = NOW(), exclude_from_cecl_ind = true.

Side-effects:

  • write_off_packet.cash_receipt_id is set to the new receipt.
  • billing_item.open_item_ind is set to false for all items in the packet.
  • billing_item_detail.write_off_status_cd is set to 'WRITTEN_OFF' for both REV and PAY details.

Postconditions:

  • A cash_receipt with receipt_type_cd = 'WRITE_OFF' exists and is linked to the packet.
  • An Approved worksheet with applications covering all written-off balances exists.
  • All billing items in the packet are closed with write_off_status_cd = 'WRITTEN_OFF'.

2.20 Create Write-Off Reversal Worksheet ​

Operation: createWriteOffReversalWorksheet

Trigger: A write-off packet is recovered (status changes to RECOVERED), triggering the write-off service to create a reversal worksheet.

Input Parameters:

  • packet_id: varchar (required) — write_off_packet.write_off_packet_id
  • actorId: String — identifier for the system process performing the operation

Creates a reversal worksheet when a write-off packet is recovered, negating all original applications and reopening billing items.

Step 1. Find Cash Receipt for the Packet ​

  • Source: write_off_packet WHERE write_off_packet_id = :packet_id.
  • Action: SELECT only.
  • Logic:
    • Capture cash_receipt_id.

Step 2. Find Original Current Worksheet ​

  • Source: cash_receipt_worksheet joined to cash_receipt_split WHERE cash_receipt_id = :cash_receipt_id AND current_item_ind = true.
  • Action: SELECT only.
  • Logic:
    • Capture cash_receipt_worksheet_id and all associated applications.

Step 3. Get Original Applications ​

  • Source: cash_receipt_application WHERE cash_receipt_worksheet_id = :original_worksheet_id.
  • Action: SELECT only.
  • Logic:
    • Collect all application records for reversal.

Step 4. Mark Original Worksheet as Non-Current ​

  • Source: original_worksheet_id from step 2.
  • Action: UPDATE cash_receipt_worksheet SET current_item_ind = false, updated_by = actorId, updated_dt = NOW() WHERE cash_receipt_worksheet_id = :original_worksheet_id.
  • Logic:
    • Marks the original as historical.

Step 5. Create Reversal Worksheet ​

  • Source: cash_receipt_split_id from step 2.
  • Action: INSERT into cash_receipt_worksheet; UPDATE to set approval and linkage metadata.
  • Logic:
    • INSERT with cash_receipt_worksheet_status_cd = 'A' (terminal — no further processing on the reversal).
    • Set current_item_ind = false.
    • UPDATE to set approved_dt = NOW(), approved_by = actorId, previous_worksheet_id = :original_worksheet_id.

Step 6. Create Reversal Applications ​

  • Source: Original applications from step 3; reversal_worksheet_id from step 5.
  • Action: INSERT into cash_receipt_application for each original application.
  • Logic:
    • Set cash_receipt_worksheet_id = :reversal_worksheet_id.
    • Set cash_receipt_amt_applied = -(original amount).
    • Set reversal_of_application_id = :original_app_id.
    • Set reversal_reason_cd = 'PACKET_RECOVERY'.

Step 7. Update Billing Item Detail Statuses ​

  • Source: Each reversed application's billing_item_detail_id.
  • Action: UPDATE billing_item_detail for each.
  • Logic:
    • Set write_off_status_cd = 'RECOVERED'.
    • Set recovered_dt = NOW().

Step 8. Reopen Billing Items ​

  • Source: Distinct billing_item_id values from the reversed applications.
  • Action: UPDATE billing_item for each unique item.
  • Logic:
    • Set open_item_ind = true.

Side-effects:

  • Original worksheet's current_item_ind is set to false.
  • billing_item_detail.write_off_status_cd is set to 'RECOVERED' for all reversed details.
  • billing_item.open_item_ind is restored to true.

Postconditions:

  • A reversal worksheet exists with negated applications and cash_receipt_worksheet_status_cd = 'A'.
  • All billing items from the original write-off are reopened with write_off_status_cd = 'RECOVERED'.

3. Business Rules & Logic ​

3.1 Split Modifiability ​

Business rule: A split can be structurally modified (amount changed, funds transferred, deleted) only when it has no worksheet or when its worksheet is in Draft or Approved status. Voided splits cannot be modified except for notes-only updates. Splits whose worksheets are in Applied, Settled, Returned, or Submitted status cannot have their amounts changed.

Data-level enforcement:

  • Read: cash_receipt_split.split_status_cd; cash_receipt_worksheet.cash_receipt_worksheet_status_cd for the split.
  • Guard: If split_status_cd = 'V': block all modifications except notes-only updates (amount and source_file_upload_id absent from payload). If worksheet status is 'P', 'T', 'R', or 'S': block amount and structural changes.
  • Write: Allowed when split_status_cd != 'V' AND (no worksheet OR cash_receipt_worksheet_status_cd IN ('D', 'A')).

3.2 Split Amount Floor ​

Business rule: A split's amount cannot be reduced below the total amount already applied to receivables on its worksheet. You cannot take money away from a split that has been applied.

Data-level enforcement:

  • Read: cash_receipt_split.split_amt; SUM of cash_receipt_application.cash_receipt_amt_applied on the split's current cash_receipt_worksheet.
  • Guard: If new_amount < applied_amt, reject the operation.
  • Write: split_amt may only be set to a value >= applied_amt.

3.3 Split Total Invariant ​

Business rule: Every dollar of a receipt must be accounted for in a split. The sum of all split amounts must equal the receipt's net amount. Splits cannot be created from thin air — they must be carved from existing splits.

Data-level enforcement:

  • Read: cash_receipt.net_receipt_amt; SUM of cash_receipt_split.split_amt WHERE cash_receipt_id = :id.
  • Guard: ABS(net_receipt_amt - SUM(split_amt)) >= 0.005 indicates a violation; log a warning and investigate.
  • Write: All split create and modify procedures carve amounts from existing splits to maintain this invariant.

3.4 Last-Split Protection ​

Business rule: The last remaining split for a receipt cannot be deleted. At least one split must always exist to maintain the receipt structure.

Data-level enforcement:

  • Read: COUNT of cash_receipt_split WHERE cash_receipt_id = :id.
  • Guard: If count <= 1, reject the deletion.
  • Write: Not applicable — this is a guard-only rule.

3.5 Receipt Posting-Status Edit Guards ​

Business rule: Once a receipt has been posted to the General Ledger, most fields become immutable. Voided receipts are read-only except for administrative reference and comment fields.

Data-level enforcement:

  • Read: cash_receipt.posting_status_cd.
  • Guard:
    • If posting_status_cd = 'U': all fields editable.
    • If posting_status_cd = 'P': only cash_receipt_comment and net_receipt_amt (via adjustment recalculation) are editable; any other field in the update payload triggers an error.
    • If posting_status_cd = 'V': only cash_receipt_ref and cash_receipt_comment are editable; any other field triggers an error.
  • Write: Field-level guards enforced before the UPDATE.

3.6 Voided Receipt Block ​

Business rule: A voided receipt cannot have adjustments, transfers, or amount changes applied to it. Only reference and comment fields remain accessible.

Data-level enforcement:

  • Read: cash_receipt.posting_status_cd.
  • Guard: Any procedure that modifies cash_receipt (except comment and reference updates) checks posting_status_cd != 'V' before proceeding.
  • Write: Not applicable — this is a guard-only rule.

3.7 Adjustment Immutability ​

Business rule: Adjustments cannot be deleted once they have been posted to the General Ledger. Transfer-type adjustments (adjustment_type_cd = 'TR') can never be deleted regardless of posting status.

Data-level enforcement:

  • Read: cash_receipt_adjustment.posting_status_cd; cash_receipt_adjustment.adjustment_type_cd.
  • Guard: If posting_status_cd = 'P', reject deletion. If adjustment_type_cd = 'TR', reject deletion.
  • Write: Not applicable — this is a guard-only rule.

3.8 Bank Reference Deduplication ​

Business rule: A given bank reference ID can produce at most one cash receipt per bank account. When a bank file is re-processed or a status-update file arrives, the system updates the existing receipt rather than creating a duplicate.

Data-level enforcement:

  • Read: cash_receipt WHERE (bank_account_id, bank_ref_id) matches the incoming transaction.
  • Guard: Unique index uq_cash_receipt_bank_ref on (bank_account_id, bank_ref_id) prevents duplicate inserts at the database level.
  • Write: syncFromBankTransaction uses the composite key lookup to decide between UPDATE (existing) and INSERT (new).

3.9 Write-Off Receipt Type Guard ​

Business rule: Write-off receipts (receipt_type_cd = 'WRITE_OFF') are created exclusively by the write-off approval workflow. Users cannot create them manually. These receipts follow a different worksheet lifecycle (auto-approved, no settlement required).

Data-level enforcement:

  • Read: cash_receipt.receipt_type_cd.
  • Guard: Manual receipt creation always sets receipt_type_cd = 'NORMAL'. The 'WRITE_OFF' code is only set programmatically by createWriteOffCashReceipt.
  • Write: receipt_type_cd = 'WRITE_OFF' must only be set by the write-off service.

3.10 Currency Conversion Requirement ​

Business rule: When a receipt is recorded in a different working currency than the currency it was received in, an FX rate must be provided. The system will not guess at the conversion.

Data-level enforcement:

  • Read: cash_receipt.currency_cd; cash_receipt.original_currency_cd; cash_receipt.fx_rate.
  • Guard: If currency_cd != original_currency_cd AND currency_cd IS NOT NULL: reject if fx_rate is missing or <= 0.
  • Write: receipt_amt = original_receipt_amt * fx_rate when currencies differ.

4. Field Mapping & Transformation ​

4.1 Bank Transaction to Cash Receipt ​

When creating a cash receipt from a bank transaction (syncFromBankTransaction), fields are mapped as follows:

Source Table.FieldTarget Table.FieldTransform
bank_transaction.statuscash_receipt.entry_statusCopied as-is (PDNG or BOOK)
bank_transaction.bank_reference_idcash_receipt.bank_ref_idCopied as-is
bank_transaction.bank_reference_idcash_receipt.cash_receipt_refSame value used for receipt reference
bank_transaction.bank_transaction_idcash_receipt.bank_transaction_idCopied as-is
bank_transaction.booking_datecash_receipt.booking_dateCopied as-is
bank_transaction.booking_datecash_receipt.deposit_dateSame value used for deposit date
bank_transaction.remittance_infocash_receipt.remittance_infoSnapshot at creation; not updated afterward
bank_transaction.bank_account_idcash_receipt.bank_account_idCopied as-is
bank_transaction.file_namecash_receipt.filenameCopied as-is
bank_transaction.amountcash_receipt.original_receipt_amtCopied as-is
bank_transaction.amountcash_receipt.receipt_amtSame (no FX conversion for bank-originated receipts)
bank_transaction.amountcash_receipt.net_receipt_amtSame (no adjustments on creation)
bank_transaction.currencycash_receipt.original_currency_cdCopied as-is
bank_transaction.currencycash_receipt.currency_cdSame (no conversion)
—cash_receipt.posting_status_cdDefaulted to 'U'
—cash_receipt.receipt_type_cdDefaulted to 'NORMAL'

4.2 Net Receipt Amount Calculation ​

Source Table.FieldTarget Table.FieldTransform
cash_receipt.receipt_amtcash_receipt.net_receipt_amtBase value before adjustments
cash_receipt_adjustment.adjustment_amt (SUM)cash_receipt.net_receipt_amtSubtracted from receipt_amt; net_receipt_amt = receipt_amt - SUM(adjustment_amt)

4.3 Receipt Amount from Currency Conversion ​

Source Table.FieldTarget Table.FieldTransform
cash_receipt.original_receipt_amtcash_receipt.receipt_amtWhen currency_cd = original_currency_cd: copied as-is
cash_receipt.original_receipt_amt * cash_receipt.fx_ratecash_receipt.receipt_amtWhen currency_cd != original_currency_cd: multiplied by FX rate, rounded to decimal(15,2)

4.4 Transfer FX Conversion ​

Source Table.FieldTarget Table.FieldTransform
Source cash_receipt.currency_cd rate (relative to USD)—Determines source exchange rate
bank_account.currency_cd rate (relative to USD) for destination—Determines destination exchange rate
Computed fx_rate = dest_rate / source_ratecash_receipt.fx_rate (destination)Applied to compute destination amount
transfer_amt * fx_ratecash_receipt.original_receipt_amt (destination)Final destination receipt amount

4.5 Write-Off Original to Reversal Applications ​

When createWriteOffReversalWorksheet creates reversal applications, each original application is negated:

Source Table.FieldTarget Table.FieldTransform
cash_receipt_application.cash_receipt_worksheet_idcash_receipt_application.cash_receipt_worksheet_idSet to the new reversal worksheet ID
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 * original amount)
cash_receipt_application.cash_receipt_application_idcash_receipt_application.reversal_of_application_idReferences the original application
—cash_receipt_application.reversal_reason_cdSet to 'PACKET_RECOVERY'

5. Cross-References ​

DocumentRelationship
Cash Receipts Data ModelSchema definitions, status lifecycles, validation constraints, and code-master values for the four tables covered by these procedures.
Cash Receipts QueriesAll read operations and computed values referenced by the procedures in this document.
Worksheets ProceduresWorksheet creation is a side-effect of split creation. Worksheet deletion is a prerequisite for split deletion. The worksheet lifecycle (D, P, T, A, R) gates split modifiability.
Write-Offs ProceduresWrite-off packet approval triggers createWriteOffCashReceipt. Write-off recovery triggers createWriteOffReversalWorksheet.
Bank Ingestion ProceduresBank file parsing feeds bank_transaction records to syncFromBankTransaction.
Accounting ProceduresThe GL posting batch job reads unposted receipts and transitions posting_status_cd from 'U' to 'P'. resetReceiptsForGLReposting reverses this when needed.
Billing Items ProcedurescreateWriteOffCashReceipt and createWriteOffReversalWorksheet update billing_item.open_item_ind and billing_item_detail.write_off_status_cd.
Tax & Withholding ProceduresTax warning calculations store results on splits via updateSplitTaxWarning.

Confidential. For internal use only.

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