Worksheets Data Model

1. Executive Summary

The worksheets domain manages the central working document for cash application -- the cash receipt worksheet. A worksheet is where incoming cash (from a buyer's payment) is allocated against specific receivables (billing item details), on-account client ledger entries, and ultimately transformed into outbound payment items destined for the client and their party.

Every dollar that enters the system through a cash receipt must flow through a worksheet before it can be posted to the general ledger (REV) or sent to a bank as an outbound payment (PAY). The worksheet enforces a multi-step approval lifecycle with distinct roles at each stage, ensuring segregation of duties between cash application, settlement creation, and final approval.

Tables covered in this document:

• cash_receipt_worksheet -- The worksheet header: status, lifecycle timestamps, return/reversal linkage
• cash_receipt_application -- Individual cash applications against billing item details (REV and PAY receivables)
• cash_receipt_application_deduction -- Deductions applied at the application level (tax withholding, bank charges, etc.)
• cash_receipt_client_ledger -- Cash applied on-account to a client ledger entry rather than a specific receivable
• cash_receipt_payout -- Outbound payment line items derived from settlements or passthroughs
• client_ledger -- On-account ledger entries for deal/entity/department/client

Position in the financial flow:

Cash Receipt (money arrives)
  --> Cash Receipt Split (portion of receipt)
    --> WORKSHEET (this document)
          |
          |--> cash_receipt_application (allocate to REV / PAY receivables)
          |      `--> cash_receipt_application_deduction (tax, withholding, etc.)
          |
          |--> cash_receipt_client_ledger (allocate to on-account ledger)
          |      `--> client_ledger (the on-account ledger entry itself)
          |
          `--> cash_receipt_payout (payment line items for outbound bank transmission)
                 `--> payment_item (created at Approval)

A worksheet belongs to exactly one cash receipt split. Each split can have at most one active worksheet (current_item_ind = true). Historical worksheets (returned, reversed) remain linked to the split but with current_item_ind = false.

---

2. Data Model

2.0 Entity Relationship Diagram

erDiagram
    CASH_RECEIPT_SPLIT ||--o{ CASH_RECEIPT_WORKSHEET : "has (at most one active)"
    CASH_RECEIPT_WORKSHEET ||--o{ CASH_RECEIPT_APPLICATION : contains
    CASH_RECEIPT_WORKSHEET ||--o{ CASH_RECEIPT_CLIENT_LEDGER : contains
    CASH_RECEIPT_WORKSHEET ||--o{ CASH_RECEIPT_PAYOUT : contains
    CASH_RECEIPT_APPLICATION ||--o{ CASH_RECEIPT_APPLICATION_DEDUCTION : has
    CASH_RECEIPT_APPLICATION }o--|| BILLING_ITEM_DETAIL : "applies to"
    CASH_RECEIPT_APPLICATION }o--o| PARTICIPANT_SETTLEMENT : "linked via"
    CASH_RECEIPT_CLIENT_LEDGER }o--|| CLIENT_LEDGER : "credits"
    CLIENT_LEDGER }o--|| PARTY : "belongs to"
    CLIENT_LEDGER }o--o| DEAL : "scoped to"
    CLIENT_LEDGER }o--o| UTA_ENTITY : "billed by"
    CLIENT_LEDGER }o--o| DEPARTMENT : "within"
    CASH_RECEIPT_PAYOUT }o--o| PAYMENT_ITEM : "creates"
    CASH_RECEIPT_PAYOUT }o--o| PARTICIPANT_SETTLEMENT_ITEM : "derived from"
    CASH_RECEIPT_PAYOUT }o--o| PARTY : "paid to"
    CASH_RECEIPT_PAYOUT }o--o| BANK_ACCOUNT : "sent via"
    CASH_RECEIPT_WORKSHEET ||--o| CASH_RECEIPT_WORKSHEET : "replaced_by / previous"

    CASH_RECEIPT_WORKSHEET {
        serial cash_receipt_worksheet_id PK
        integer cash_receipt_split_id FK
        integer worksheet_sequence
        boolean current_item_ind
        varchar cash_receipt_worksheet_status_cd
        integer locked_by_user_id FK
        varchar worksheet_type_cd
        varchar posting_status_cd
        date posting_dt
        integer replaced_by_worksheet_id FK
        integer previous_worksheet_id FK
    }

    CASH_RECEIPT_APPLICATION {
        serial cash_receipt_application_id PK
        integer cash_receipt_worksheet_id FK
        integer billing_item_detail_id FK
        integer participant_settlement_id FK
        decimal cash_receipt_amt_applied
        integer reversal_of_application_id
        varchar reversal_reason_cd
    }

    CASH_RECEIPT_APPLICATION_DEDUCTION {
        serial cash_receipt_application_deduction_id PK
        integer cash_receipt_application_id FK
        varchar billing_item_deduction_type_cd
        decimal deduction_amt_applied
    }

    CASH_RECEIPT_CLIENT_LEDGER {
        serial cash_receipt_client_ledger_id PK
        integer cash_receipt_worksheet_id FK
        integer client_ledger_id FK
        integer deal_id FK
        integer uta_entity_id FK
        integer department_id FK
        integer client_id FK
        decimal cash_receipt_amt_applied
        integer reversal_of_ledger_id
        varchar reversal_reason_cd
    }

    CLIENT_LEDGER {
        serial client_ledger_id PK
        integer client_id FK
        integer contracted_party_id FK
        integer buyer_id FK
        integer deal_id FK
        integer uta_entity_id FK
        integer department_id FK
        varchar client_ledger_type_cd
        varchar client_ledger_status_cd
        decimal client_ledger_amt
        varchar client_ledger_currency_cd
        boolean client_ledger_open_item_ind
        varchar posting_status_cd
    }

    CASH_RECEIPT_PAYOUT {
        serial cash_receipt_payout_id PK
        integer cash_receipt_worksheet_id FK
        integer payout_party_id FK
        integer payment_party_bank_id FK
        integer payment_item_id FK
        integer participant_settlement_item_id FK
        varchar payment_item_type_cd
        decimal payment_item_amt
        varchar payment_item_currency_cd
        varchar payout_status_cd
        boolean do_not_send_ind
        integer reversal_of_payout_id
    }

2.1 cash_receipt_worksheet

The worksheet header record. Tracks lifecycle status, workflow timestamps, return/reversal linkage, and posting state.

Field	Type	Required	Default	Description
cash_receipt_worksheet_id	serial	Yes	Auto	Primary key.
cash_receipt_split_id	integer	No	null	FK to cash_receipt_split. Null only for legacy worksheets that predate the split model.
worksheet_sequence	integer	No	null	Ordinal within the receipt (1, 2, 3...). Used for display ordering when a receipt has multiple splits.
current_item_ind	boolean	No	true	Whether this is the active worksheet for its split. Set to false when a worksheet is returned or replaced. Only one worksheet per split should have current_item_ind = true.
cash_receipt_worksheet_status_cd	varchar(50)	No	'D'	Current lifecycle status. Values: D (Draft), P (Applied), T (Settled), A (Approved), R (Returned), S (Submitted -- deprecated). See Status Lifecycle section.
locked_by_user_id	integer	No	null	FK to users. The user currently holding an edit lock on this worksheet. Null when unlocked. Prevents concurrent editing.
applied_dt	timestamp	No	null	Timestamp when worksheet transitioned to Applied status.
applied_by	varchar(100)	No	null	Display name of the user who applied the worksheet.
settled_dt	timestamp	No	null	Timestamp when worksheet transitioned to Settled status.
settled_by	varchar(100)	No	null	Display name of the user who settled the worksheet.
approved_dt	timestamp	No	null	Timestamp when worksheet transitioned to Approved status.
approved_by	varchar(100)	No	null	Display name of the user who approved the worksheet.
returned_dt	timestamp	No	null	Timestamp when the worksheet was returned (sealed).
returned_by	varchar(100)	No	null	Display name of the user who returned the worksheet.
return_reason	text	No	null	Free-text reason provided when the worksheet is returned.
replaced_by_worksheet_id	integer	No	null	Self-FK. Points to the replacement draft worksheet created after this worksheet was returned. Set on the original (returned) worksheet.
previous_worksheet_id	integer	No	null	Self-FK. Points to the returned worksheet that this worksheet replaces. Set on the replacement draft worksheet.
worksheet_type_cd	varchar(20)	No	null	Classifies the worksheet's role in the return chain. Values: ORIGINAL, REVERSAL, REPLACEMENT. Null for worksheets that have never been through a return cycle.
posting_status_cd	varchar(50)	No	null	GL posting pickup status. Values: U (Unposted -- ready for pickup), P (Posted), X (Skipped). Set to U when the worksheet transitions to Applied.
posting_dt	date	No	null	The date the worksheet was posted to the general ledger.

Legacy fields (deprecated):

Field	Type	Required	Default	Description
submitted_dt	timestamp	No	null	Timestamp of legacy Submit action.
submitted_by	varchar(100)	No	null	User who performed legacy Submit.
rejected_dt	timestamp	No	null	Timestamp of legacy Reject action.
rejected_by	varchar(100)	No	null	User who performed legacy Reject.
rejection_reason	text	No	null	Reason for legacy rejection.

WARNING

The submitted_dt, submitted_by, rejected_dt, rejected_by, and rejection_reason fields exist for backward compatibility with legacy status S (Submitted). New code must not use these fields. The current lifecycle uses Applied (P) instead of Submitted (S).

Database indexes:

Index	Columns	Purpose
cash_receipt_worksheet_current_item_idx	current_item_ind	Fast lookup of active worksheets
cash_receipt_worksheet_split_idx	cash_receipt_split_id	Split-to-worksheet navigation
cash_receipt_worksheet_status_idx	cash_receipt_worksheet_status_cd	Status-based queue filtering
cash_receipt_worksheet_current_status_idx	current_item_ind, cash_receipt_worksheet_status_cd	Composite for common "active worksheets by status" queries

2.2 cash_receipt_application

Records the application of cash to a specific billing item detail (REV or PAY receivable). Each row represents one application event. The same billing item detail can appear multiple times on the same worksheet as separate application records (for credits, adjustments, or partial applications).

Field	Type	Required	Default	Description
cash_receipt_application_id	serial	Yes	Auto	Primary key.
cash_receipt_worksheet_id	integer	No	null	FK to cash_receipt_worksheet. Links the application to its parent worksheet.
billing_item_detail_id	integer	No	null	FK to billing_item_detail. The specific REV or PAY receivable line being applied against. Nullable when the application targets a settlement directly.
participant_settlement_id	integer	No	null	FK to participant_settlement. Links PAY applications to their settlement. Populated when a settlement is created for this application's PAY receivable.
cash_receipt_amt_applied	decimal(15,2)	No	null	The dollar amount applied. Positive for normal applications, negative for reversal entries.
reversal_of_application_id	integer	No	null	Self-FK. Points to the original cash_receipt_application_id that this record reverses. Populated on reversal worksheet entries.
reversal_reason_cd	varchar(20)	No	null	Reason code for the reversal. Example: WORKSHEET_RETURN.

IMPORTANT

A single billing_item_detail_id can appear multiple times on the same worksheet as separate application records. This supports incremental application events, adjustments, and credits. However, cross-worksheet exclusivity applies: an active (non-returned, non-approved) application for a given billing item detail should not exist on a different active worksheet.

Database indexes:

Index	Columns	Purpose
cash_receipt_app_billing_item_detail_idx	billing_item_detail_id	Fast lookup by receivable
cash_receipt_app_worksheet_idx	cash_receipt_worksheet_id	All applications for a worksheet
cash_receipt_app_worksheet_billing_idx	cash_receipt_worksheet_id, billing_item_detail_id	Composite for worksheet + receivable queries

2.3 cash_receipt_application_deduction

Tracks deductions applied at the individual application level. These are adjustments that reduce or supplement the net application amount -- for example, tax withholding, bank charges, or discounts.

Field	Type	Required	Default	Description
cash_receipt_application_deduction_id	serial	Yes	Auto	Primary key.
cash_receipt_application_id	integer	Yes	--	FK to cash_receipt_application. Every deduction must be linked to a parent application.
billing_item_deduction_type_cd	varchar(50)	No	null	Code master type BILLING_ITEM_DEDUCTION_TYPE_CD. Classifies the deduction (Tax, Withholding, Bank Charge, etc.). See Code Master Values section.
deduction_amt_applied	decimal(15,2)	No	null	The deduction amount.

NOTE

Application-level deductions are distinct from billing item deductions (billing_item_deduction). Billing item deductions exist on the receivable itself; application-level deductions are recorded when cash is applied and may differ from what was originally estimated on the billing item.

2.4 cash_receipt_client_ledger

Records cash applied on-account to a client ledger entry rather than to a specific billing item receivable. Used when cash arrives but cannot yet be matched to a specific deal or billing item, or when cash is explicitly held on account for future application.

Field	Type	Required	Default	Description
cash_receipt_client_ledger_id	serial	Yes	Auto	Primary key.
cash_receipt_worksheet_id	integer	No	null	FK to cash_receipt_worksheet. Links the ledger application to its parent worksheet.
client_ledger_id	integer	No	null	FK to client_ledger. The target on-account ledger entry.
deal_id	integer	No	null	FK to deal. Optional deal context for the on-account entry.
uta_entity_id	integer	No	null	FK to uta_entity. The UTA billing entity associated with the ledger entry.
department_id	integer	No	null	FK to department. The UTA department associated with the ledger entry.
client_id	integer	No	null	FK to party (client). The client whose on-account balance is being credited.
cash_receipt_amt_applied	decimal(15,2)	No	null	Amount applied to the client ledger. Positive for normal credits, negative for reversals.
reversal_of_ledger_id	integer	No	null	Self-FK. Points to the original cash_receipt_client_ledger_id being reversed. Populated on reversal worksheet entries.
reversal_reason_cd	varchar(20)	No	null	Reason code for the reversal. Example: WORKSHEET_RETURN.

IMPORTANT

Client ledger entries are always reversible because the money remains in UTA's accounts. On worksheet return, client ledger entries from the original worksheet are always copied to the replacement draft as editable entries (unlike locked PAY applications which are read-only).

2.5 cash_receipt_payout

Represents an outbound payment line item on the worksheet. Payouts are created during the settlement process and drive the creation of payment_item records at worksheet approval. Each payout represents a specific amount owed to a specific party.

Field	Type	Required	Default	Description
cash_receipt_payout_id	serial	Yes	Auto	Primary key.
cash_receipt_worksheet_id	integer	No	null	FK to cash_receipt_worksheet. Links the payout to its parent worksheet.
payout_party_id	integer	No	null	FK to party. The party who will receive this payment (client, manager, attorney, etc.).
payment_party_bank_id	integer	No	null	FK to bank_account. The destination bank account for this payment.
payment_item_id	integer	No	null	FK to payment_item. Populated after worksheet approval when the business payment record is created.
participant_settlement_item_id	integer	No	null	FK to participant_settlement_item. Links the payout to a specific settlement line item. Required for settlement-derived payouts (type S).
deal_id	integer	No	null	FK to deal. Deal context for the payout.
buyer_id	integer	No	null	FK to party (buyer). The original buyer who paid.
uta_entity_id	integer	No	null	FK to uta_entity. The UTA entity from which the payment is sent.
department_id	integer	No	null	FK to department. The department associated with this payout.
payment_item_name	varchar(500)	No	null	Descriptive label for the payment line item. Displayed on payment advices and bank transmissions.
payment_item_type_cd	varchar(50)	No	null	Code master type PAYMENT_ITEM_TYPE_CD. Classifies the payout. Values: S (Settlement), P (Passthrough), L (Ledger/Loan), R (Reversal), V (VAT Pass-Through).
payment_item_amt	decimal(15,2)	No	null	The payment amount.
payment_item_currency_cd	varchar(10)	No	null	ISO currency code of the payout amount (e.g., USD, GBP, EUR).
payment_date	date	No	null	Target date for the payment to be sent.
client_currency_conversion_ind	boolean	No	null	When true, indicates the payout requires currency conversion before bank transmission.
target_currency_cd	varchar(10)	No	null	The destination currency for conversion. Only relevant when client_currency_conversion_ind = true.
payout_status_cd	varchar(20)	No	null	Payout lifecycle status. Values: PENDING, PAID, RETURNED, CANCELLED.
returned_dt	timestamp	No	null	Timestamp when the payout was returned or cancelled.
returned_by_user_id	integer	No	null	User ID who returned the payout.
reversal_of_payout_id	integer	No	null	Self-FK. Points to the original payout being reversed. Populated on reversal worksheet entries.
reversal_reason_cd	varchar(50)	No	null	Reason code for the reversal. Example: WORKSHEET_RETURN.
do_not_send_ind	boolean	No	false	When true, the associated payment item will not be transmitted to the bank. Used during reopen to prevent premature bank transmission of re-created payment items. Reset to false on worksheet approval for unlocked items.

NOTE

Settlement-derived payouts (payment_item_type_cd = 'S') must total to the PAY amount applied on the worksheet. The sum of all S-type payouts must equal the sum of all PAY applications for the worksheet to be considered fully settled.

2.6 client_ledger

The on-account ledger entry itself. Represents a standing balance for a client scoped to a deal, entity, and department. Cash receipt client ledger records (cash_receipt_client_ledger) reference these entries when applying cash on-account. Client ledger entries support both on-account (OA) and loan (LOAN) types, with their own lifecycle and GL posting state.

Field	Type	Required	Default	Description
client_ledger_id	serial	Yes	Auto	Primary key.
client_id	integer	Yes	--	FK to party. The client who owns this ledger entry.
contracted_party_id	integer	No	null	FK to party. The contracted party (e.g., loan company) associated with this ledger entry.
buyer_id	integer	No	null	FK to party. The buyer associated with this ledger entry.
deal_id	integer	No	null	FK to deal. Deal context for the ledger entry.
uta_entity_id	integer	No	null	FK to uta_entity. The UTA billing entity associated with the ledger entry.
department_id	integer	No	null	FK to department. The UTA department associated with the ledger entry.
client_ledger_type_cd	varchar(50)	No	null	Code master type CLIENT_LEDGER_TYPE_CD. Classifies the ledger entry. Values: OA (On-Account), LOAN (Loan).
client_ledger_status_cd	varchar(50)	No	null	Code master type CLIENT_LEDGER_STATUS_CD. Lifecycle status. Values: D (Draft), V (Void), C (Closed).
client_ledger_name	varchar(255)	No	null	Descriptive label for the ledger entry.
client_ledger_amt	decimal(20,2)	No	null	The ledger entry amount. Precision: 20 digits, 2 decimal places.
client_ledger_currency_cd	varchar(10)	No	null	ISO currency code for the ledger amount (e.g., USD, GBP).
client_ledger_start_dt	date	No	null	Effective start date of the ledger entry (e.g., loan start date).
client_ledger_end_dt	date	No	null	Effective end date of the ledger entry (e.g., loan maturity date).
client_ledger_notes	varchar(2000)	No	null	Free-text notes about the ledger entry.
client_ledger_open_item_ind	boolean	No	null	Whether this ledger entry is still open for cash application. Set to false when fully applied or closed.
client_ledger_payment_item_id	integer	No	null	FK to payment_item. The payment item associated with a loan-type ledger entry.
posting_status_cd	varchar(50)	No	null	GL posting pickup status. Values: U (Unposted), P (Posted), X (Skipped).
posting_dt	date	No	null	The date the ledger entry was posted to the general ledger.

---

3. Status Lifecycle

The worksheet follows a strict forward-only lifecycle. No worksheet ever moves backward in status. Corrections are handled through the return/reopen mechanism which produces new documents rather than modifying the original.

3.1 Worksheet Status

Status	Code	Description	Allowed Transitions
Draft	D	Initial editable state. Users add and modify cash applications.	P (Applied)
Applied	P	Cash applications finalized. REV staged for GL posting. Settlements can be created for PAY.	T (Settled)
Settled	T	All PAY applications have settlements. Awaiting final approval.	A (Approved)
Approved	A	Final approval. Payment items created and ready for bank transmission.	R (Returned)
Returned	R	Worksheet sealed as read-only historical record.	Terminal
Submitted	S	Deprecated. Legacy status between Draft and Approved. Replaced by Applied (P).	A (legacy only)

stateDiagram-v2
    [*] --> D : Create worksheet
    D --> P : Apply
    P --> T : Settle
    T --> A : Approve
    A --> R : Return
    R --> [*]

    state "Draft (D)" as D
    state "Applied (P)" as P
    state "Settled (T)" as T
    state "Approved (A)" as A
    state "Returned (R)" as R

3.2 Transition Details

Draft (D) to Applied (P)

• Trigger: User with canApplyWorksheet permission clicks Apply.
• Preconditions: cash_receipt_worksheet_status_cd = 'D'; at least one application exists across cash_receipt_application, cash_receipt_client_ledger, or cash_receipt_payout. Partial application is allowed (worksheet balance does not need to equal zero).
• Side-effects: posting_status_cd set to U (Unposted), marking REV applications and client ledger entries as ready for GL posting pickup. applied_dt and applied_by are recorded.

Applied (P) to Settled (T)

• Trigger: User with canSettleWorksheet permission clicks Settle.
• Preconditions: cash_receipt_worksheet_status_cd = 'P'; all PAY applications must have a linked participant_settlement_id. Settlement payout totals (type S) must equal total PAY applied.
• Side-effects: settled_dt and settled_by are recorded. All linked participant_settlement records are updated to Settled status (T).

Settled (T) to Approved (A)

• Trigger: User with canApproveWorksheet permission clicks Approve.
• Preconditions: cash_receipt_worksheet_status_cd = 'T' (or 'D'/'S' with elevated privilege for legacy/shortcut flows); at least one application exists.
• Side-effects: approved_dt and approved_by are recorded. All linked settlements are approved. payment_item records are created from cash_receipt_payout entries. do_not_send_ind is reset to false on unlocked payment items so bank transmission jobs pick them up. Fully paid billing items are closed (open_item_ind = false). Receipt lock is released.

Approved (A) to Returned (R)

• Trigger: User with canReopenWorksheet permission initiates a Return (also called "Reopen").

• Preconditions: cash_receipt_worksheet_status_cd = 'A'; receipt type must not be WRITE_OFF (write-off worksheets use a separate recovery process); worksheet must have a cash_receipt_split_id.

• Side-effects: Returning an approved worksheet always produces three documents:

  1. Original -- sealed as Returned (R), current_item_ind = false, worksheet_type_cd = 'ORIGINAL'
  2. Reversal worksheet -- auto-created with negative entries for ALL items (including locked), worksheet_type_cd = 'REVERSAL', current_item_ind = false, posting_status_cd = 'U'
  3. Replacement draft -- only LOCKED items copied as read-only; unlocked items NOT pre-populated, worksheet_type_cd = 'REPLACEMENT', current_item_ind = true

  returned_dt, returned_by, and return_reason are recorded on the original. replaced_by_worksheet_id on the original points to the replacement draft. previous_worksheet_id on the replacement draft points to the original.

IMPORTANT

The replacement draft contains ONLY locked applications copied as read-only context -- those with payment items that have been sent to the bank and cannot be reversed. Unlocked applications are NOT pre-populated. Users must add fresh applications manually. This prevents duplicate accumulation across successive returns.

3.3 Roles per Transition

From	To	Action	Role	Key Preconditions
--	D	Create	CASH_MANAGER, IT	Split exists; receipt not voided/posted; no active worksheet for split
D	P	Apply	CASH_MANAGER, IT	At least one application exists
P	T	Settle	CASH_PROCESSOR, IT	All PAY applications have settlements
T	A	Approve	SETTLEMENT_APPROVER, IT	Settlements reviewed
A	R	Return	SETTLEMENT_APPROVER, IT	Not a write-off worksheet; produces 3 documents

3.4 Client Ledger Status

Status	Code	Description	Allowed Transitions
Draft	D	Ledger entry created, editable.	V (Void), C (Closed)
Void	V	Ledger entry voided/cancelled.	Terminal
Closed	C	Ledger entry fully applied and closed.	Terminal

3.5 Payout Status

Status	Code	Description	Allowed Transitions
Pending	PENDING	Payout created but payment not yet sent to bank.	PAID, RETURNED, CANCELLED
Paid	PAID	Bank has confirmed payment completion.	Terminal
Returned	RETURNED	Payout returned during a worksheet return process.	Terminal
Cancelled	CANCELLED	Payout cancelled before payment was sent.	Terminal

3.6 Payment Item Locking Rules

The locking logic determines what can and cannot be reversed during a return:

Execution Status	Locked?	Behavior on Return
null / empty	No	Unlocked -- payment voided, not copied to replacement draft
WAITING	No	Unlocked -- payment voided, not copied to replacement draft
PENDING	No	Unlocked -- payment voided, not copied to replacement draft
FAILED	No	Unlocked -- payment voided, not copied to replacement draft
PROCESSING	Yes	Locked -- copied as read-only to replacement draft
SENT	Yes	Locked -- copied as read-only to replacement draft
ACKNOWLEDGED	Yes	Locked -- copied as read-only to replacement draft
PAID	Yes	Locked -- copied as read-only to replacement draft

IMPORTANT

When ANY payment item in a settlement is locked, the ENTIRE settlement locks -- all settlement items, associated PAY applications, and bilateral REV applications are locked together. This is the "bilateral lock" rule: a locked PAY application also locks its paired REV application within the same settlement.

---

4. Validation & Database Constraints

4.1 Unique Constraints

Table	Constraint	Columns	Business Rule
cash_receipt_worksheet	One active worksheet per split	cash_receipt_split_id, current_item_ind	Only one worksheet with current_item_ind = true per split. Enforced by business logic at creation time, not by a database unique constraint.
cash_receipt_application_deduction	NOT NULL on parent FK	cash_receipt_application_id	Every deduction must belong to an application. Enforced by database NOT NULL constraint.
client_ledger	NOT NULL on client FK	client_id	Every ledger entry must belong to a client. Enforced by database NOT NULL constraint.

4.2 Business Validation Rules

Worksheet creation:

• The parent cash_receipt_split must exist.
• The grandparent cash_receipt must not have posting_status_cd of V (Voided) or P (Posted).
• No other active worksheet (current_item_ind = true) may exist for the same split.
• The receipt must not be locked by a different user.

Apply transition (D to P):

• At least one record must exist across cash_receipt_application, cash_receipt_client_ledger, or cash_receipt_payout.
• Partial application is allowed -- the worksheet balance does not need to equal zero.

Settle transition (P to T):

• All PAY-type applications (billing_item_detail_type_cd = 'PAY') must have a non-null participant_settlement_id.
• The sum of settlement payouts (type S) must match the total PAY amount applied.

Approve transition (T to A):

• At least one application must exist.
• For legacy/shortcut flows, elevated privilege (SETTLEMENT_APPROVER or IT) allows approval directly from Draft (D) or Submitted (S).

Return/Reopen:

• Only worksheets in Approved (A) status can be returned.
• Write-off worksheets (receipt_type_cd = 'WRITE_OFF') cannot be returned; they use a separate recovery process.
• Worksheet must have a cash_receipt_split_id (legacy worksheets without a split cannot be reopened).

4.3 Amount Precision

Context	Precision	Scale	Max Value
cash_receipt_amt_applied	15	2	9,999,999,999,999.99
deduction_amt_applied	15	2	9,999,999,999,999.99
payment_item_amt	15	2	9,999,999,999,999.99
client_ledger_amt	20	2	99,999,999,999,999,999.99

NOTE

Balance comparison tolerance (epsilon) is 0.005. When checking if a worksheet balance is zero or if a billing item is fully paid, amounts within 0.005 of zero are treated as zero.

---

5. Code Master Values

All code master values are stored in the code_master table with a composite primary key of (code_master_type, code_master_cd).

5.1 CASH_RECEIPT_WORKSHEET_STATUS_CD

Code	Description	Behavior / When Used
D	Draft	Initial editable state. Users add and modify cash applications.
P	Applied	Cash applications finalized. REV staged for GL posting. Settlements can be created for PAY. Not present in legacy seed data -- introduced in the current lifecycle.
T	Settled	All PAY applications have settlements. Awaiting final approval. Not present in legacy seed data -- introduced in the current lifecycle.
S	Submitted	Deprecated. Legacy status between Draft and Approved. Replaced by Applied (P) in the current lifecycle. Retained for backward compatibility.
A	Approved	Final approval. Payment items created and ready for bank transmission.
R	Reversed / Returned	Worksheet sealed as read-only historical record. Accompanied by a reversal worksheet and replacement draft.

NOTE

The seed data lists statuses D, S, A, R. Statuses P (Applied) and T (Settled) were added as part of the current multi-step lifecycle and should be added to the code master seed if not already present.

5.2 POSTING_STATUS_CD

Code	Description	Behavior / When Used
U	Unposted	Ready for pickup by the GL posting process. Set when a worksheet transitions to Applied.
P	Posted	GL posting has been completed for this worksheet.
X	Skipped	GL posting was skipped (e.g., for reversal worksheets where the original was never posted, or for voided items).

5.3 PAYMENT_ITEM_TYPE_CD

Used on cash_receipt_payout.payment_item_type_cd to classify payout line items.

Code	Description	Behavior / When Used
S	Settlement	Derived from a participant settlement. The primary payout type. Settlement payouts must sum to total PAY applied.
P	Passthrough	Direct payment to a party, bypassing the settlement process. Used for one-off payments.
L	Loan / Ledger	Payment against a client ledger loan entry.
R	Reversal / Refund	Reversal of a prior payout, created during the return process.
V	VAT Pass-Through	VAT amount passed through to the payee. Used in UK tax scenarios.

5.4 BILLING_ITEM_DEDUCTION_TYPE_CD

Used on cash_receipt_application_deduction.billing_item_deduction_type_cd to classify application-level deductions.

Code	Description	Behavior / When Used
T	Tax	General tax deduction.
W	Withholding	General withholding deduction.
B	Bank Charge	Bank fees deducted from the application amount.
D	Discount	Discount applied at the time of cash application.
R	Reimbursement	Reimbursement adjustment.
C	Client Request	Deduction at client's request.
DP	Direct Payment	Amount already paid directly, deducted from application.
WH_US_NRA	US NRA Withholding (30%)	US non-resident alien withholding. Applied to payments to non-US persons without a CWA.
WH_UK_FEU	UK FEU Withholding (20%)	UK Foreign Entertainers Unit withholding. Applied when cumulative UK earnings exceed the personal allowance threshold.
VAT_ARTIST	VAT on Artist Fee (20%)	UK VAT on the artist's fee portion. Applied for UK Agency + UK Show + UK Client + VAT Registered scenarios.
VAT_COMM	VAT on Commission (20%)	UK VAT on the commission portion. Applied for UK Agency + UK Client scenarios.

5.5 WORKSHEET_TYPE_CD

Not stored in code_master -- used as enumerated values on cash_receipt_worksheet.worksheet_type_cd.

Code	Description	Behavior / When Used
ORIGINAL	Original worksheet	Set when a worksheet is sealed during a return. The worksheet that was approved and is now being replaced.
REVERSAL	Reversal worksheet	Auto-created during return. Contains negative entries for ALL items (including locked). current_item_ind = false. posting_status_cd = 'U' for GL pickup.
REPLACEMENT	Replacement draft	New draft created during return. Contains only locked items as read-only. current_item_ind = true. Users add fresh applications here.

5.6 PAYOUT_STATUS_CD

Not stored in code_master -- used as enumerated values on cash_receipt_payout.payout_status_cd.

Code	Description	Behavior / When Used
PENDING	Pending	Payout created but payment not yet sent to bank.
PAID	Paid	Bank has confirmed payment completion.
RETURNED	Returned	Payout was returned during a worksheet return process.
CANCELLED	Cancelled	Payout was cancelled before payment was sent.

5.7 REVERSAL_REASON_CD

Used on reversal fields across cash_receipt_application, cash_receipt_client_ledger, and cash_receipt_payout.

Code	Description	Behavior / When Used
WORKSHEET_RETURN	Worksheet Return	The reversal was created as part of the three-document worksheet return process.

5.8 CLIENT_LEDGER_TYPE_CD

Used on client_ledger.client_ledger_type_cd to classify ledger entries.

Code	Description	Behavior / When Used
OA	On-Account	Cash held on account for a client, not yet applied to a specific receivable. Can be applied later via worksheet.
LOAN	Loan	Loan entry for a client. Linked to a payment_item via client_ledger_payment_item_id. Has start and end dates.

5.9 CLIENT_LEDGER_STATUS_CD

Used on client_ledger.client_ledger_status_cd to track ledger entry lifecycle.

Code	Description	Behavior / When Used
D	Draft	Ledger entry created and editable. Open for cash application.
V	Void	Ledger entry has been voided/cancelled. No longer available for cash application.
C	Closed	Ledger entry fully applied and closed. client_ledger_open_item_ind set to false.

---

6. Cross-References

Document	Relationship
Cash Receipts	Parent domain. cash_receipt_worksheet is created from a cash_receipt_split, which belongs to a cash_receipt. The receipt's posting status and void status gate worksheet creation.
Billing Items	Target of cash application. cash_receipt_application.billing_item_detail_id references billing_item_detail, which is typed as REV or PAY. Billing item balance calculations depend on the sum of applications across all worksheets.
Settlements	Downstream of PAY applications. cash_receipt_application.participant_settlement_id links PAY applications to settlements. Settlement items drive cash_receipt_payout creation. The settle transition requires all PAY applications to have settlements.
Settlements	Created at approval. cash_receipt_payout.payment_item_id links to payment_item records created during the approve transition. Payment execution status drives the locking logic during returns.

Key cross-domain rules:

1. Billing item balance = billing_item_detail.total_amt minus the sum of cash_receipt_application.cash_receipt_amt_applied across all active, non-returned worksheets for that detail.

2. Worksheet unapplied amount = split amount minus the sum of all cash_receipt_amt_applied across cash_receipt_application and cash_receipt_client_ledger for the worksheet. This is a derived (computed) value, not stored.

3. Settlement completeness = the worksheet can transition from Applied to Settled only when every PAY application has a linked participant_settlement_id, and the sum of settlement payouts (type S) equals the total PAY applied.

4. Payment item lifecycle = payment_item records are created from cash_receipt_payout at worksheet approval. The payment item's execution status (WAITING through PAID) determines whether the payout is locked during a subsequent return.

5. GL posting = REV applications and client ledger entries post to GL when the worksheet moves to Applied and the posting process picks up records with posting_status_cd = 'U'. PAY applications post to GL only after the bank confirms payment completion (not at worksheet approval time).