Client Framework

Appearance

Write-Offs Procedures ​

1. Executive Summary ​

This document defines every data mutation operation in the write-off domain — the step-by-step procedures for creating, populating, submitting, approving, executing, rejecting, recovering, and deleting write-off packets. Each procedure is described at the data level: which tables are read, which rows are inserted or updated, what field values are set, and what validation conditions must hold.

The write-off lifecycle spans four core tables — write_off_packet, packet_receivable, packet_status_history, packet_document — and reaches into the billing and cash receipt domains during write-off execution and recovery. The procedures break into seven functional areas:

  1. Packet lifecycle — Create, update, delete, and aggregate recalculation on write_off_packet.
  2. Receivable management — Add, remove, update eligibility, and bulk operations on packet_receivable.
  3. Submission — Validate and transition a packet from DRAFT to SUBMITTED.
  4. Approval workflow — Multi-level approval chain driving write_off_packet status transitions and packet_status_history inserts.
  5. Rejection and resubmission — Return packets to a rejected state and restart the approval chain.
  6. Write-off execution — Mark billing_item_detail records as written off, create offsetting cash receipt structures.
  7. Recovery — Reverse a completed write-off, create reversal worksheet, restore billing item detail statuses.

Each procedure lists preconditions, the ordered sequence of writes, and the resulting data state. Business rules and validation logic are consolidated in Section 3.

**PoC Artifact:** The PoC implements two parallel approval paths. A **simplified** approve/reject flow (DRAFT/SUBMITTED to APPROVED, with cash receipt creation) exists alongside the **full** multi-level approval state machine (SUBMITTED through APPROVED_AGENT/DH/VP/CFO/MD to COMPLETE, with amount-based routing). The production system should use only the multi-level path. This document describes both, noting the simplified path where it diverges.

2. Key Procedures ​

2.1 Create Write-Off Packet ​

Operation: createPacket

Trigger: User creates a new write-off packet by supplying a name and selecting a client.

Input Parameters:

  • packetName: String (required) — write_off_packet.packet_name
  • clientId: Integer (required) — write_off_packet.client_id → party.party_id
  • packetEligibilityCriteriaCd: String (optional) — write_off_packet.packet_eligibility_criteria_cd
  • actorId: Integer — identifier for the user performing the operation

Creates a new write-off packet in DRAFT status for the given client, enforcing name uniqueness, and seeds the audit trail with an initial CREATE history record.

Step 1. Validate Uniqueness ​

  • Source: write_off_packet (all existing rows).
  • Action: SELECT count from write_off_packet.
  • Logic:
    • Query write_off_packet for any row where packet_name = :packetName.
    • If a match exists, reject with "Packet name already exists."

Step 2. Insert Packet ​

  • Source: Input parameters.
  • Action: INSERT into write_off_packet.
  • Logic:
    • Set write_off_packet_id = auto-generated UUID.
    • Set packet_name = provided value.
    • Set client_id = provided value.
    • Set packet_eligibility_criteria_cd = provided value, or NULL if omitted.
    • Set cash_receipt_id = NULL.
    • Set total_commission_amt = '0.00'.
    • Set receivable_count = 0.
    • Set packet_status_cd = 'DRAFT'.
    • Set current_approver_role = NULL.
    • Set all timestamp and user ID fields (submitted_dt, submitted_by_user_id, completed_dt, completed_by_user_id, recovered_dt, recovered_by_user_id, rejected_dt, rejected_by_user_id, rejection_reason) = NULL.
    • Set created_by_user_id = acting user ID, created_dt = current timestamp.
    • Set updated_by_user_id = acting user ID, updated_dt = current timestamp.

Step 3. Insert Initial Status History ​

  • Source: Newly created write_off_packet from Step 2.
  • Action: INSERT into packet_status_history.
  • Logic:
    • Set packet_status_history_id = auto-generated UUID.
    • Set write_off_packet_id = newly created packet ID.
    • Set from_status_cd = NULL (initial creation).
    • Set to_status_cd = 'DRAFT'.
    • Set action_cd = 'CREATE'.
    • Set approver_role = NULL.
    • Set comment_text = 'Packet created'.
    • Set action_by_user_id = acting user ID.
    • Set action_dt = current timestamp.

Side-effects:

  • packet_status_history receives one immutable CREATE record.

Postconditions:

  • write_off_packet exists with packet_status_cd = 'DRAFT', total_commission_amt = '0.00', receivable_count = 0.
  • One packet_status_history row records the creation event.

2.2 Update Write-Off Packet ​

Operation: updatePacket

Trigger: User edits the packet name or packet-level eligibility criteria on a draft or rejected packet.

Input Parameters:

  • writeOffPacketId: UUID (required) — write_off_packet.write_off_packet_id
  • packetName: String (optional) — write_off_packet.packet_name
  • packetEligibilityCriteriaCd: String (optional) — write_off_packet.packet_eligibility_criteria_cd
  • actorId: Integer — identifier for the user performing the operation

Modifies editable fields on a packet that is in an editable status (DRAFT or any REJECTED_*).

Step 1. Fetch and Validate Packet ​

  • Source: write_off_packet (record matching writeOffPacketId).
  • Action: SELECT from write_off_packet.
  • Logic:
    • If not found, reject with "Packet not found."
    • Evaluate packet_status_cd: packet is editable only when packet_status_cd = 'DRAFT' or packet_status_cd starts with 'REJECTED_'.
    • If not editable, reject with an appropriate error message.

Step 2. Validate Name Uniqueness (Conditional) ​

  • Source: write_off_packet (all rows excluding current packet).
  • Action: SELECT count from write_off_packet.
  • Logic:
    • Only applies when packetName is provided and differs from current.packet_name.
    • Query write_off_packet where packet_name = :packetName and write_off_packet_id != :writeOffPacketId.
    • If a match exists, reject with "Packet name already exists."

Step 3. Update Packet Fields ​

  • Source: Input parameters.
  • Action: UPDATE write_off_packet.
  • Logic:
    • Set provided fields (packet_name, packet_eligibility_criteria_cd) to new values.
    • Set updated_by_user_id = acting user ID.
    • Set updated_dt = current timestamp.
    • No packet_status_history record is created for field edits. Only status transitions generate history records.

Side-effects:

  • None beyond the field update on write_off_packet.

Postconditions:

  • write_off_packet reflects the updated field values.
  • packet_status_cd is unchanged.

2.3 Delete Write-Off Packet ​

Operation: deletePacket

Trigger: User deletes a draft packet that is no longer needed.

Input Parameters:

  • writeOffPacketId: UUID (required) — write_off_packet.write_off_packet_id
  • actorId: Integer — identifier for the user performing the operation

Permanently removes a DRAFT packet and all its related data via cascade. Only packets in DRAFT status can be deleted.

Step 1. Fetch and Validate Packet ​

  • Source: write_off_packet (record matching writeOffPacketId).
  • Action: SELECT from write_off_packet.
  • Logic:
    • If not found, reject with "Packet not found."
    • If packet_status_cd != 'DRAFT', reject with "Only draft packets can be deleted."

Step 2. Delete Packet and Cascade ​

  • Source: write_off_packet (record from Step 1).
  • Action: DELETE from write_off_packet.
  • Logic:
    • Delete the write_off_packet row matching write_off_packet_id.
    • The database cascade deletes all related rows:
      • All packet_receivable rows for this packet.
      • All packet_status_history rows for this packet.
      • All packet_document rows for this packet (both packet-level and receivable-level).

There is no `CANCELLED` status. Draft packets that are no longer needed are deleted outright. Once a packet has been submitted, it can only be rejected (and then edited or resubmitted) — it cannot be deleted.

Side-effects:

  • All packet_receivable, packet_status_history, and packet_document rows belonging to this packet are removed via cascade delete.

Postconditions:

  • No write_off_packet row exists for this packet ID.
  • No orphaned child records remain.

2.4 Add Receivable to Packet ​

Operation: addReceivableToPacket

Trigger: User selects a billing item detail from the eligible receivables search and adds it to an open packet.

Input Parameters:

  • writeOffPacketId: UUID (required) — write_off_packet.write_off_packet_id
  • billingItemDetailId: Integer (required) — packet_receivable.billing_item_detail_id → billing_item_detail.billing_item_detail_id
  • eligibilityCriteriaCd: String (required) — packet_receivable.eligibility_criteria_cd
  • usePacketDocumentInd: Boolean (optional, default false) — packet_receivable.use_packet_document_ind
  • actorId: Integer — identifier for the user performing the operation

Associates a billing_item_detail with a write-off packet, enforcing single-packet exclusivity.

Step 1. Validate Packet ​

  • Source: write_off_packet (record matching writeOffPacketId).
  • Action: SELECT from write_off_packet.
  • Logic:
    • If not found, reject with "Packet not found."
    • If not editable (packet_status_cd is not 'DRAFT' and does not start with 'REJECTED_'), reject.

Step 2. Check Single-Packet Exclusivity ​

  • Source: packet_receivable joined to write_off_packet.
  • Action: SELECT count from packet_receivable.
  • Logic:
    • Query packet_receivable joined to write_off_packet where billing_item_detail_id = :billingItemDetailId and write_off_packet.packet_status_cd IN ('DRAFT', 'SUBMITTED', 'APPROVED_AGENT', 'APPROVED_DEPT_HEAD', 'APPROVED_VP', 'APPROVED_CFO').
    • If count > 0, reject with "Receivable is already in another active packet."

Step 3. Insert Packet Receivable ​

  • Source: Input parameters.
  • Action: INSERT into packet_receivable.
  • Logic:
    • Set packet_receivable_id = auto-generated UUID.
    • Set write_off_packet_id = provided value.
    • Set billing_item_detail_id = provided value.
    • Set eligibility_criteria_cd = provided value (or empty string if not specified).
    • Set use_packet_document_ind = provided value, defaulting to false.
    • Set created_by_user_id = acting user ID, created_dt = current timestamp.

The aggregate totals on `write_off_packet` (`total_commission_amt`, `receivable_count`) are **not** automatically updated by this operation. They must be recalculated separately via procedure 2.9.

Side-effects:

  • None beyond the inserted packet_receivable row.

Postconditions:

  • A packet_receivable row exists linking the billing_item_detail to the packet.
  • write_off_packet.total_commission_amt and receivable_count remain stale until procedure 2.9 is run.

2.5 Bulk Add Receivables to Packet ​

Operation: bulkAddReceivablesToPacket

Trigger: User selects multiple receivables from the eligible receivables search and adds them all at once.

Input Parameters:

  • packetId: UUID (required) — write_off_packet.write_off_packet_id
  • receivables: Array of objects, each containing billingItemDetailId, eligibilityCriteriaCd, usePacketDocumentInd (optional)
  • actorId: Integer — identifier for the user performing the operation

Adds multiple receivables to a packet in one operation, skipping duplicates and reporting per-item errors rather than failing the entire batch.

Step 1. Validate Packet ​

  • Source: write_off_packet (record matching packetId).
  • Action: SELECT from write_off_packet.
  • Logic:
    • If not found, reject with "Packet not found."
    • If not editable, reject with an appropriate error message.

Step 2. Process Each Receivable ​

  • Source: Input receivables array.
  • Action: For each element, check exclusivity then INSERT into packet_receivable.
  • Logic:
    • For each receivable, check single-packet exclusivity (same check as procedure 2.4 Step 2).
    • If the receivable is already in an active packet, record the error message and skip to the next item.
    • Otherwise, insert a packet_receivable row with the same field values as procedure 2.4 Step 3.

Step 3. Return Summary ​

  • Source: Counters accumulated in Step 2.
  • Action: Return summary data (no further database writes).
  • Logic:
    • Return { added: addedCount, errors: [errorMessages] } to the caller.

**PoC Artifact:** This operation is not transactional. Individual failures do not roll back previously added receivables within the same bulk operation.

Side-effects:

  • One packet_receivable row inserted per successfully added receivable.

Postconditions:

  • All receivables that passed exclusivity checks are now linked to the packet.
  • Items that failed are reported in the error list and not added.

2.6 Remove Receivable from Packet ​

Operation: removeReceivableFromPacket

Trigger: User removes a receivable from a packet via the receivables table.

Input Parameters:

  • packetReceivableId: UUID (required) — packet_receivable.packet_receivable_id
  • actorId: Integer — identifier for the user performing the operation

Dissociates a receivable from a write-off packet. Receivable-level documents are also removed via cascade.

Step 1. Fetch Receivable and Validate Parent Packet ​

  • Source: packet_receivable (record matching packetReceivableId), then write_off_packet.
  • Action: SELECT from packet_receivable, then SELECT from write_off_packet.
  • Logic:
    • If packet_receivable not found, reject with "Receivable not found."
    • Fetch the parent write_off_packet by write_off_packet_id.
    • If packet not found, reject.
    • If packet not editable (packet_status_cd is not 'DRAFT' and does not start with 'REJECTED_'), reject.

Step 2. Delete Receivable ​

  • Source: packet_receivable (record from Step 1).
  • Action: DELETE from packet_receivable.
  • Logic:
    • Delete the packet_receivable row matching packet_receivable_id.
    • The database cascade deletes any packet_document rows where packet_receivable_id matches this record.

As with adding, removing a receivable does not automatically recalculate packet aggregates. Procedure 2.9 must be run separately.

Side-effects:

  • Any packet_document rows scoped to this receivable are removed via cascade.

Postconditions:

  • No packet_receivable row exists for this packet_receivable_id.
  • write_off_packet.total_commission_amt and receivable_count remain stale until procedure 2.9 is run.

2.7 Update Receivable Eligibility Criteria ​

Operation: updateReceivableEligibilityCriteria

Trigger: User changes the eligibility reason for a single receivable in the packet.

Input Parameters:

  • packetReceivableId: UUID (required) — packet_receivable.packet_receivable_id
  • eligibilityCriteriaCd: String (required) — packet_receivable.eligibility_criteria_cd
  • actorId: Integer — identifier for the user performing the operation

Changes the eligibility_criteria_cd on a single receivable within an editable packet.

Step 1. Fetch Receivable and Validate Parent Packet ​

  • Source: packet_receivable (record matching packetReceivableId), then write_off_packet.
  • Action: SELECT from packet_receivable, then SELECT from write_off_packet.
  • Logic:
    • If packet_receivable not found, reject with "Receivable not found."
    • Fetch the parent packet by write_off_packet_id.
    • If packet not editable, reject with appropriate error message.

Step 2. Update Eligibility Criteria ​

  • Source: Input parameter eligibilityCriteriaCd.
  • Action: UPDATE packet_receivable.
  • Logic:
    • Set eligibility_criteria_cd = provided new value.
    • Set updated_by_user_id = acting user ID.
    • Set updated_dt = current timestamp.

Side-effects:

  • None beyond the field update on packet_receivable.

Postconditions:

  • packet_receivable.eligibility_criteria_cd reflects the new value.

2.8 Bulk Update Blank Eligibility Criteria ​

Operation: bulkUpdateBlankEligibility

Trigger: User applies a single eligibility code to all receivables that have no eligibility assigned yet.

Input Parameters:

  • packetId: UUID (required) — write_off_packet.write_off_packet_id
  • eligibilityCriteriaCd: String (required) — packet_receivable.eligibility_criteria_cd
  • actorId: Integer — identifier for the user performing the operation

Sets eligibility_criteria_cd on all packet_receivable rows in the packet that currently have a blank or null eligibility code.

Step 1. Validate Packet ​

  • Source: write_off_packet (record matching packetId).
  • Action: SELECT from write_off_packet.
  • Logic:
    • If not found, reject with "Packet not found."
    • If not editable, reject with appropriate error message.

Step 2. Fetch and Filter Receivables ​

  • Source: packet_receivable rows for this packet.
  • Action: SELECT from packet_receivable.
  • Logic:
    • Retrieve all packet_receivable rows where write_off_packet_id = :packetId.
    • Filter to rows where eligibility_criteria_cd is NULL or empty string.

Step 3. Update Matching Rows ​

  • Source: Filtered receivable rows from Step 2.
  • Action: UPDATE packet_receivable for each matching row.
  • Logic:
    • For each filtered row, set eligibility_criteria_cd = provided value.
    • Set updated_by_user_id = acting user ID.
    • Set updated_dt = current timestamp.

Side-effects:

  • None beyond the field updates on the matching packet_receivable rows.

Postconditions:

  • All packet_receivable rows that previously had blank eligibility now have eligibility_criteria_cd = :eligibilityCriteriaCd.
  • Rows that already had an eligibility code are unchanged.

2.9 Recalculate Packet Aggregates ​

Operation: recalculatePacketAggregates

Trigger: Called after any add or remove receivable operation to synchronize denormalized totals.

Input Parameters:

  • packetId: UUID (required) — write_off_packet.write_off_packet_id
  • actorId: Integer — identifier for the user performing the operation

Recomputes the denormalized total_commission_amt and receivable_count fields on write_off_packet based on current packet_receivable data. These fields drive approval routing thresholds and must be accurate before submission.

Step 1. Fetch Receivable Amounts ​

  • Source: packet_receivable joined to billing_item_detail.
  • Action: SELECT from packet_receivable inner joined to billing_item_detail.
  • Logic:
    • Retrieve all packet_receivable rows for write_off_packet_id = :packetId.
    • Join to billing_item_detail to obtain billing_item_detail_amt and billing_item_detail_type_cd.
    • Filter to rows where billing_item_detail_type_cd = 'REV' (only REV-type receivables count toward the aggregate).

Step 2. Calculate Aggregates ​

  • Source: Filtered receivable rows from Step 1.
  • Action: In-memory calculation (no database write in this step).
  • Logic:
    • receivable_count = count of REV-type receivable rows.
    • total_commission_amt = sum of billing_item_detail_amt for all REV-type rows, rounded to 2 decimal places.

Step 3. Update Packet ​

  • Source: Computed values from Step 2.
  • Action: UPDATE write_off_packet.
  • Logic:
    • Set total_commission_amt = calculated sum (formatted as string with 2 decimal places).
    • Set receivable_count = calculated count.
    • Set updated_by_user_id = acting user ID.
    • Set updated_dt = current timestamp.

`total_commission_amt` drives the approval routing thresholds. Inaccurate aggregates can cause packets to be routed to the wrong approver level. This procedure must be called after any receivable add or remove operation.

Side-effects:

  • None beyond the two field updates on write_off_packet.

Postconditions:

  • write_off_packet.total_commission_amt and receivable_count accurately reflect the current REV-type receivables in the packet.

2.10 Submit Packet for Approval ​

Operation: submitPacket

Trigger: User clicks the "Submit for Approval" button on a draft packet.

Input Parameters:

  • packetId: UUID (required) — write_off_packet.write_off_packet_id
  • actorId: Integer — identifier for the user performing the operation

Transitions a packet from DRAFT to SUBMITTED, validates all submission rules, sets current_approver_role to 'AGENT', and records the submission in packet_status_history.

Step 1. Fetch and Validate Packet Status ​

  • Source: write_off_packet (record matching packetId).
  • Action: SELECT from write_off_packet.
  • Logic:
    • If not found, reject with "Packet not found."
    • If packet_status_cd != 'DRAFT', reject with an appropriate error message.

Step 2. Run Submission Validation ​

  • Source: packet_receivable rows for this packet, joined to billing_item_detail.
  • Action: SELECT receivables with details; evaluate all validation rules.
  • Logic:
    • Fetch all receivables with enriched billing item detail data.
    • Apply all rules from Section 3.2 (at least one receivable, all REV-type, all have eligibility, all have documentation, no duplicates in active packets, all belong to the same client).
    • If any rule fails, reject with the combined error messages.

Step 3. Update Packet Status ​

  • Source: Input parameters and validation result.
  • Action: UPDATE write_off_packet.
  • Logic:
    • Set packet_status_cd = 'SUBMITTED'.
    • Set current_approver_role = 'AGENT'.
    • Set updated_by_user_id = acting user ID.
    • Set updated_dt = current timestamp.

Step 4. Record Submission Timestamps ​

  • Source: Current timestamp and acting user.
  • Action: UPDATE write_off_packet.
  • Logic:
    • Set submitted_dt = current timestamp.
    • Set submitted_by_user_id = acting user ID.

Step 5. Insert Status History ​

  • Source: Packet state from Steps 1–4.
  • Action: INSERT into packet_status_history.
  • Logic:
    • Set from_status_cd = 'DRAFT'.
    • Set to_status_cd = 'SUBMITTED'.
    • Set action_cd = 'SUBMIT'.
    • Set approver_role = 'AGENT'.
    • Set comment_text = 'Packet submitted for approval'.
    • Set action_by_user_id = acting user ID.
    • Set action_dt = current timestamp.

Side-effects:

  • packet_status_history receives one immutable SUBMIT record.

Postconditions:

  • write_off_packet.packet_status_cd = 'SUBMITTED'.
  • write_off_packet.current_approver_role = 'AGENT'.
  • write_off_packet.submitted_dt and submitted_by_user_id are populated.
  • Packet is read-only until rejected or approved.

2.11 Approve Packet (Multi-Level Workflow) ​

Operation: approvePacket

Trigger: An approver at the current approval level clicks "Approve" on a packet pending their review.

Input Parameters:

  • packetId: UUID (required) — write_off_packet.write_off_packet_id
  • approverRole: String (required) — the role of the acting approver (AGENT, DEPT_HEAD, VP_CLIENT_ACCT, CFO, MD)
  • commentText: String (optional) — approval comment
  • actorId: Integer — identifier for the user performing the operation

Advances a packet through the multi-level approval chain. Uses the state machine with current_status and total_commission_amt to determine the next status and approver. If the approval completes the chain, invokes write-off execution (procedure 2.16).

Step 1. Fetch and Validate Packet ​

  • Source: write_off_packet (record matching packetId).
  • Action: SELECT from write_off_packet.
  • Logic:
    • If not found, reject with "Packet not found."
    • If packet_status_cd is not in an approvable state (SUBMITTED, RESUBMITTED, APPROVED_AGENT, APPROVED_DH, APPROVED_VP, APPROVED_CFO), reject.
    • If current_approver_role != approverRole, reject with "Packet is pending approval from {current_approver_role}, not {approverRole}."

Step 2. Compute Next State ​

  • Source: write_off_packet.packet_status_cd and write_off_packet.total_commission_amt.

  • Action: Evaluate state machine (pure function, no database write).

  • Logic:

    • Use the state machine to determine next_status, next_approver_role, and is_complete based on the table below.
    Current Statustotal_commission_amtNext StatusNext Approver RoleComplete?
    SUBMITTED or RESUBMITTEDAnyAPPROVED_AGENTDEPT_HEADNo
    APPROVED_AGENTAnyAPPROVED_DHVP_CLIENT_ACCTNo
    APPROVED_DH< $50,000APPROVED_VPNULLYes
    APPROVED_DH>= $50,000APPROVED_VPCFONo
    APPROVED_VP< $50,000COMPLETENULLYes
    APPROVED_VP>= $50,000 and <= $250,000APPROVED_CFONULLNo
    APPROVED_VP> $250,000APPROVED_CFOMDNo
    APPROVED_CFO<= $250,000COMPLETENULLYes
    APPROVED_CFO> $250,000APPROVED_MDNULLNo
    APPROVED_MDAnyCOMPLETENULLYes

Step 3. Update Packet Status ​

  • Source: Computed state from Step 2.
  • Action: UPDATE write_off_packet.
  • Logic:
    • Set packet_status_cd = computed next_status.
    • Set current_approver_role = computed next_approver_role (NULL if complete).
    • Set updated_by_user_id = acting user ID.
    • Set updated_dt = current timestamp.

Step 4. Execute Write-Off (Conditional) ​

  • Source: is_complete flag from Step 2.
  • Action: Invoke procedure 2.16 if is_complete = true.
  • Logic:
    • If next_status = 'COMPLETE', call the Execute Write-Off procedure (2.16).

Step 5. Insert Status History ​

  • Source: Packet state before and after transition.
  • Action: INSERT into packet_status_history.
  • Logic:
    • Set from_status_cd = previous packet_status_cd.
    • Set to_status_cd = computed next_status.
    • Set action_cd = 'APPROVE'.
    • Set approver_role = acting user's approver role.
    • Set comment_text = provided comment, or 'Approved by {approverRole}' if not provided.
    • Set action_by_user_id = acting user ID.
    • Set action_dt = current timestamp.

Side-effects:

  • If is_complete = true: billing_item_detail records are marked WRITTEN_OFF (see procedure 2.16); write_off_packet.completed_dt and completed_by_user_id are set.
  • packet_status_history receives one immutable APPROVE record.

Postconditions:

  • write_off_packet.packet_status_cd = computed next_status.
  • write_off_packet.current_approver_role = computed next_approver_role (or NULL if complete).
  • If COMPLETE: all billing_item_detail records in the packet have write_off_status_cd = 'WRITTEN_OFF'.

2.12 Approve Packet (Simplified Workflow) ​

Operation: approvePacketSimplified

**PoC Artifact:** This simplified approval flow exists for demo purposes. It skips the multi-level chain and goes directly from DRAFT or SUBMITTED to APPROVED, then triggers cash receipt creation. The production system should use the multi-level workflow (2.11) exclusively.

Trigger: User clicks "Approve" in the simplified PoC flow.

Input Parameters:

  • packetId: UUID (required) — write_off_packet.write_off_packet_id
  • actorId: Integer — identifier for the user performing the operation

Approves a packet in one step, bypassing the multi-level chain. Triggers write-off cash receipt creation (procedure 2.17).

Step 1. Fetch and Validate Packet ​

  • Source: write_off_packet (record matching packetId).
  • Action: SELECT from write_off_packet.
  • Logic:
    • If not found, reject.
    • If packet_status_cd is not 'DRAFT' or 'SUBMITTED', reject.
    • If receivable_count < 1, reject with "Cannot approve packet without at least one receivable."

Step 2. Update Packet to APPROVED ​

  • Source: Input parameters.
  • Action: UPDATE write_off_packet.
  • Logic:
    • Set packet_status_cd = 'APPROVED'.
    • Set current_approver_role = NULL.
    • Set completed_dt = current timestamp.
    • Set completed_by_user_id = acting user ID.
    • Set updated_by_user_id = acting user ID.
    • Set updated_dt = current timestamp.

Step 3. Insert Status History ​

  • Source: Packet state before and after transition.
  • Action: INSERT into packet_status_history.
  • Logic:
    • Set action_cd = 'APPROVE'.
    • Set to_status_cd = 'APPROVED'.
    • Set all other fields as per procedure 2.23.

Step 4. Invoke Write-Off Cash Receipt Creation ​

  • Source: packetId.
  • Action: Invoke procedure 2.17.
  • Logic:
    • Call Execute Write-Off with Cash Receipt (procedure 2.17) to create the offsetting cash receipt structures.
    • Failure to create the cash receipt does not reverse the APPROVED status; it must be handled separately.

Side-effects:

  • packet_status_history receives one immutable APPROVE record.
  • Procedure 2.17 creates cash_receipt, cash_receipt_split, cash_receipt_worksheet, and cash_receipt_application records and updates billing_item_detail and billing_item.

Postconditions:

  • write_off_packet.packet_status_cd = 'APPROVED'.
  • write_off_packet.cash_receipt_id is populated (if cash receipt creation succeeded).

2.13 Reject Packet (Multi-Level Workflow) ​

Operation: rejectPacket

Trigger: An approver at the current approval level clicks "Reject" on a packet pending their review.

Input Parameters:

  • packetId: UUID (required) — write_off_packet.write_off_packet_id
  • approverRole: String (required) — the role of the acting approver
  • reason: String (required) — rejection reason text
  • actorId: Integer — identifier for the user performing the operation

Rejects a packet at the current approval level, assigning it a role-specific REJECTED_* status and making it editable again. The approval chain must restart from AGENT on resubmission.

Step 1. Fetch and Validate Packet ​

  • Source: write_off_packet (record matching packetId).
  • Action: SELECT from write_off_packet.
  • Logic:
    • If not found, reject with "Packet not found."
    • If not in an approvable status, reject with appropriate error.
    • If current_approver_role != approverRole, reject with role mismatch error.
    • If reason is empty, reject with "Rejection reason is required."

Step 2. Compute Rejection Status ​

  • Source: approverRole input parameter.
  • Action: Map role to rejection status (no database write).
  • Logic:
    • AGENT → REJECTED_AGENT
    • DEPT_HEAD → REJECTED_DH
    • VP_CLIENT_ACCT → REJECTED_VP
    • CFO → REJECTED_CFO
    • MD → REJECTED_MD

Step 3. Update Packet Status ​

  • Source: Computed rejection status from Step 2.
  • Action: UPDATE write_off_packet.
  • Logic:
    • Set packet_status_cd = computed rejection status.
    • Set current_approver_role = 'AGENT' (back to agent for resubmission routing).
    • Set updated_by_user_id = acting user ID.
    • Set updated_dt = current timestamp.

Step 4. Insert Status History ​

  • Source: Packet state before and after transition.
  • Action: INSERT into packet_status_history.
  • Logic:
    • Set from_status_cd = previous packet_status_cd.
    • Set to_status_cd = computed rejection status.
    • Set action_cd = 'REJECT'.
    • Set approver_role = acting user's approver role.
    • Set comment_text = rejection reason (required).
    • Set action_by_user_id = acting user ID.
    • Set action_dt = current timestamp.

Side-effects:

  • packet_status_history receives one immutable REJECT record with the rejection reason.

Postconditions:

  • write_off_packet.packet_status_cd = role-specific REJECTED_* value.
  • write_off_packet.current_approver_role = 'AGENT'.
  • Packet becomes editable again (REJECTED_* is an editable status).

2.14 Reject Packet (Simplified Workflow) ​

Operation: rejectPacketSimplified

**PoC Artifact:** The simplified rejection returns the packet directly to `DRAFT` status rather than using role-specific rejection statuses. It also accumulates rejection reasons in `write_off_packet.rejection_reason`. The production system should use the multi-level workflow (2.13) exclusively.

Trigger: User rejects a packet in the simplified PoC flow.

Input Parameters:

  • packetId: UUID (required) — write_off_packet.write_off_packet_id
  • reason: String (required) — rejection reason text
  • actorId: Integer — identifier for the user performing the operation

Returns the packet to DRAFT status, accumulating the rejection reason in write_off_packet.rejection_reason.

Step 1. Fetch and Validate Packet ​

  • Source: write_off_packet (record matching packetId).
  • Action: SELECT from write_off_packet.
  • Logic:
    • If not found, reject.
    • If packet_status_cd is not 'DRAFT' or 'SUBMITTED', reject.
    • If reason is empty, reject with "Rejection reason is required."

Step 2. Build Accumulated Rejection Reason ​

  • Source: write_off_packet.rejection_reason (existing value).
  • Action: In-memory string construction (no database write in this step).
  • Logic:
    • Format new rejection entry: [{ISO_timestamp}|{userId}] {reason}.
    • If rejection_reason already has a value, append with double newline separator.
    • If empty, use the new entry alone.

Step 3. Update Packet to DRAFT ​

  • Source: Computed rejection reason from Step 2.
  • Action: UPDATE write_off_packet.
  • Logic:
    • Set packet_status_cd = 'DRAFT'.
    • Set current_approver_role = NULL.
    • Set rejected_dt = current timestamp.
    • Set rejected_by_user_id = acting user ID.
    • Set rejection_reason = combined reason string.
    • Set submitted_dt = NULL (cleared).
    • Set submitted_by_user_id = NULL (cleared).
    • Set updated_by_user_id = acting user ID.
    • Set updated_dt = current timestamp.

Step 4. Insert Status History ​

  • Source: Packet state before and after transition.
  • Action: INSERT into packet_status_history.
  • Logic:
    • Set action_cd = 'REJECT'.
    • Set to_status_cd = 'DRAFT'.
    • Set comment_text = rejection reason.
    • Set all other fields as per procedure 2.23.

Side-effects:

  • packet_status_history receives one immutable REJECT record.

Postconditions:

  • write_off_packet.packet_status_cd = 'DRAFT'.
  • write_off_packet.rejection_reason contains the full accumulated rejection history.
  • Packet is fully editable again.

2.15 Resubmit Packet ​

Operation: resubmitPacket

Trigger: User resubmits a rejected packet after making corrections.

Input Parameters:

  • packetId: UUID (required) — write_off_packet.write_off_packet_id
  • actorId: Integer — identifier for the user performing the operation

Transitions a packet from any REJECTED_* status to SUBMITTED and resets current_approver_role to 'AGENT', restarting the approval chain from the beginning.

Step 1. Fetch and Validate Packet ​

  • Source: write_off_packet (record matching packetId).
  • Action: SELECT from write_off_packet.
  • Logic:
    • If not found, reject with "Packet not found."
    • If packet_status_cd is not one of REJECTED_AGENT, REJECTED_DH, REJECTED_VP, REJECTED_CFO, REJECTED_MD, reject with "Only rejected packets can be resubmitted."

Step 2. Update Packet Status ​

  • Source: Input parameters.
  • Action: UPDATE write_off_packet.
  • Logic:
    • Set packet_status_cd = 'SUBMITTED'.
    • Set current_approver_role = 'AGENT' (restarts approval chain from the beginning).
    • Set updated_by_user_id = acting user ID.
    • Set updated_dt = current timestamp.

Step 3. Insert Status History ​

  • Source: Packet state before and after transition.
  • Action: INSERT into packet_status_history.
  • Logic:
    • Set from_status_cd = previous rejected status.
    • Set to_status_cd = 'SUBMITTED'.
    • Set action_cd = 'RESUBMIT'.
    • Set approver_role = 'AGENT'.
    • Set comment_text = 'Packet resubmitted for approval'.
    • Set action_by_user_id = acting user ID.
    • Set action_dt = current timestamp.

Side-effects:

  • packet_status_history receives one immutable RESUBMIT record.

Postconditions:

  • write_off_packet.packet_status_cd = 'SUBMITTED'.
  • write_off_packet.current_approver_role = 'AGENT'.
  • The approval chain begins again from the Agent level.

2.16 Execute Write-Off (Multi-Level Completion) ​

Operation: executeWriteOff

Trigger: Called internally when the multi-level approval chain determines that the final required approver has approved (i.e., next_status = 'COMPLETE' in procedure 2.11).

Input Parameters:

  • packetId: UUID (required) — write_off_packet.write_off_packet_id
  • actorId: Integer — identifier for the user performing the operation

Marks all billing_item_detail records associated with the packet's receivables as WRITTEN_OFF and updates write_off_packet completion timestamps.

Step 1. Fetch Packet Receivables ​

  • Source: packet_receivable rows for this packet.
  • Action: SELECT from packet_receivable.
  • Logic:
    • Retrieve all packet_receivable rows where write_off_packet_id = :packetId.

Step 2. Mark Each Billing Item Detail as Written Off ​

  • Source: packet_receivable rows from Step 1.
  • Action: For each receivable, UPDATE billing_item_detail.
  • Logic:
    • Set write_off_status_cd = 'WRITTEN_OFF'.
    • Set write_off_packet_id = packet ID (UUID).
    • Set write_off_dt = current timestamp.

Step 3. Mark Packet as Completed ​

  • Source: write_off_packet (record matching packetId).
  • Action: UPDATE write_off_packet.
  • Logic:
    • Set completed_dt = current timestamp.
    • Set completed_by_user_id = acting user ID.
    • Set updated_by_user_id = acting user ID.
    • Set updated_dt = current timestamp.

The multi-level execution does **not** create offsetting cash receipt structures (`cash_receipt`, `cash_receipt_split`, `cash_receipt_worksheet`, `cash_receipt_application`). This gap compared to the simplified flow (2.17) must be closed in production.

Side-effects:

  • billing_item_detail.write_off_status_cd, write_off_packet_id, and write_off_dt are updated for every receivable in the packet.

Postconditions:

  • All billing_item_detail records in the packet have write_off_status_cd = 'WRITTEN_OFF'.
  • write_off_packet.completed_dt and completed_by_user_id are populated.

2.17 Execute Write-Off with Cash Receipt (Simplified Completion) ​

Operation: createWriteOffCashReceipt

**PoC Artifact:** This procedure is triggered by the simplified approval flow (procedure 2.12). It creates full cash receipt structures for the write-off. The production system should incorporate this cash receipt creation into the multi-level completion (2.16).

Trigger: Called after the simplified approval sets packet_status_cd = 'APPROVED' (procedure 2.12, Step 4).

Input Parameters:

  • packetId: UUID (required) — write_off_packet.write_off_packet_id
  • actorId: Integer — identifier for the user performing the operation

Creates the full set of cash structures to record the write-off: one cash_receipt of type WRITE_OFF, one cash_receipt_split, one auto-approved cash_receipt_worksheet, and one cash_receipt_application per billing item detail (REV and PAY) with a positive balance.

Step 1. Fetch Receivables and Billing Item Balances ​

  • Source: packet_receivable rows for this packet; billing_item_detail display data for each unique billing item.
  • Action: SELECT from packet_receivable; for each unique billing_item_id, fetch billing display data.
  • Logic:
    • Retrieve all packet_receivable rows for write_off_packet_id = :packetId.
    • For each unique billing_item_id, fetch billing display data to obtain rev_id, rev_balance, pay_id, pay_balance, and currency_cd.

Step 2. Calculate Total Receipt Amount ​

  • Source: Billing item balance data from Step 1.
  • Action: In-memory calculation.
  • Logic:
    • total_receipt_amt = SUM(rev_balance + pay_balance) for all unique billing items.
    • currency_cd = currency from the billing items (assumes uniform currency).

Step 3. Insert Cash Receipt ​

  • Source: Computed totals from Step 2.
  • Action: INSERT into cash_receipt.
  • Logic:
    • Set receipt_type_cd = 'WRITE_OFF'.
    • Set receipt_amt = total_receipt_amt (formatted to 2 decimal places).
    • Set net_receipt_amt = total_receipt_amt.
    • Set currency_cd = from billing item data.
    • Set cash_receipt_comment = 'Write-off for packet'.
  • Source: Newly created cash_receipt from Step 3.
  • Action: UPDATE write_off_packet.
  • Logic:
    • Set cash_receipt_id = newly created cash receipt ID.

Step 5. Insert Cash Receipt Split ​

  • Source: Cash receipt from Step 3.
  • Action: INSERT into cash_receipt_split.
  • Logic:
    • Set cash_receipt_id = cash receipt ID from Step 3.
    • Set split_amt = total_receipt_amt.
    • Set split_status_cd = 'U'.
    • Set split_sequence = 1.

Step 6. Insert Auto-Approved Worksheet ​

  • Source: Split from Step 5.
  • Action: INSERT into cash_receipt_worksheet; UPDATE cash_receipt_worksheet with approval fields.
  • Logic:
    • Set cash_receipt_split_id = split ID from Step 5.
    • Set cash_receipt_worksheet_status_cd = 'A' (auto-approved).
    • Set current_item_ind = true.
    • Set approved_dt = current timestamp.
    • Set approved_by = acting user ID.

Step 7. Create Applications and Update Billing Records ​

  • Source: Billing item balance data from Step 1; worksheet from Step 6.
  • Action: For each billing item: INSERT cash_receipt_application rows; UPDATE billing_item; UPDATE billing_item_detail.
  • Logic:
    • If rev_balance >= $0.01: INSERT cash_receipt_application with billing_item_detail_id = rev_id and cash_receipt_amt_applied = rev_balance.
    • If pay_balance >= $0.01: INSERT cash_receipt_application with billing_item_detail_id = pay_id and cash_receipt_amt_applied = pay_balance.
    • UPDATE billing_item: set open_item_ind = false (closes the receivable).
    • UPDATE billing_item_detail for REV: set write_off_status_cd = 'WRITTEN_OFF', write_off_packet_id = packet ID, write_off_dt = current timestamp, exclude_from_cecl_ind = true.
    • UPDATE billing_item_detail for PAY: set write_off_status_cd = 'WRITTEN_OFF', write_off_packet_id = packet ID, write_off_dt = current timestamp, exclude_from_cecl_ind = true.

Both REV **and** PAY billing item details are marked `WRITTEN_OFF` and their balances are applied to the write-off cash receipt. While only REV represents the company's loss from a business perspective, the PAY portion is also closed out to fully zero the billing item. See Section 3.1 for the business rule.

Side-effects:

  • write_off_packet.cash_receipt_id is populated.
  • billing_item.open_item_ind is set to false for each billing item.
  • Both REV and PAY billing_item_detail records have write_off_status_cd = 'WRITTEN_OFF' and exclude_from_cecl_ind = true.

Postconditions:

  • One cash_receipt (type WRITE_OFF) exists and is linked to the packet.
  • One cash_receipt_split, one cash_receipt_worksheet (status 'A'), and N cash_receipt_application rows exist.
  • All billing items in the packet are closed (open_item_ind = false).

2.18 Recover Write-Off (Multi-Level) ​

Operation: recoverPacket

Trigger: User initiates recovery of a write-off on a completed packet, indicating the receivable has become collectable again.

Input Parameters:

  • packetId: UUID (required) — write_off_packet.write_off_packet_id
  • reason: String (required) — recovery reason text
  • actorId: Integer — identifier for the user performing the operation

Reverses a completed write-off by resetting billing_item_detail.write_off_status_cd to 'NOT_WRITTEN_OFF', updating packet status to 'RECOVERED', and recording the recovery in packet_status_history.

Step 1. Fetch and Validate Packet ​

  • Source: write_off_packet (record matching packetId).
  • Action: SELECT from write_off_packet.
  • Logic:
    • If not found, reject with "Packet not found."
    • If packet_status_cd != 'COMPLETE', reject with "Only completed packets can be recovered."
    • If reason is empty, reject with "Recovery reason is required."

Step 2. Revert Billing Item Details ​

  • Source: packet_receivable rows for this packet.
  • Action: For each receivable, UPDATE billing_item_detail.
  • Logic:
    • Retrieve all packet_receivable rows for this packet.
    • For each, update the associated billing_item_detail:
      • Set write_off_status_cd = 'NOT_WRITTEN_OFF'.
      • Set write_off_packet_id = NULL.
      • Set write_off_dt = NULL.
      • Set recovered_dt = current timestamp.

Step 3. Update Packet Status ​

  • Source: Input parameters.
  • Action: UPDATE write_off_packet.
  • Logic:
    • Set packet_status_cd = 'RECOVERED'.
    • Set current_approver_role = NULL.
    • Set recovered_dt = current timestamp.
    • Set recovered_by_user_id = acting user ID.
    • Set updated_by_user_id = acting user ID.
    • Set updated_dt = current timestamp.

Step 4. Insert Status History ​

  • Source: Packet state before and after transition.
  • Action: INSERT into packet_status_history.
  • Logic:
    • Set from_status_cd = 'COMPLETE'.
    • Set to_status_cd = 'RECOVERED'.
    • Set action_cd = 'RECOVER'.
    • Set comment_text = recovery reason.
    • Set action_by_user_id = acting user ID.
    • Set action_dt = current timestamp.

The multi-level recovery reverts `billing_item_detail.write_off_status_cd` to `'NOT_WRITTEN_OFF'`. This differs from the simplified recovery (2.19), which sets it to `'RECOVERED'`. GL posting reversal is handled separately and is outside the scope of these procedures.

Side-effects:

  • billing_item_detail.write_off_status_cd, write_off_packet_id, and write_off_dt are reset for every receivable in the packet.
  • billing_item_detail.recovered_dt is populated for every receivable.
  • packet_status_history receives one immutable RECOVER record.

Postconditions:

  • write_off_packet.packet_status_cd = 'RECOVERED' (terminal state).
  • All associated billing_item_detail records have write_off_status_cd = 'NOT_WRITTEN_OFF'.
  • Packet is read-only and cannot be further modified.

2.19 Recover Write-Off with Reversal Worksheet (Simplified) ​

Operation: createReversalWorksheet

**PoC Artifact:** This procedure is triggered by the simplified recovery flow. It creates a reversal worksheet in addition to updating packet and billing item statuses. The production system should incorporate this reversal worksheet creation into the multi-level recovery (2.18).

Trigger: Called after the simplified recovery sets packet_status_cd = 'RECOVERED' in the simplified flow.

Input Parameters:

  • packetId: UUID (required) — write_off_packet.write_off_packet_id
  • actorId: Integer — identifier for the user performing the operation

Creates a reversal worksheet with negative application amounts to undo the write-off cash receipt entries, reopens billing items, and sets billing_item_detail.write_off_status_cd to 'RECOVERED'.

Step 1. Locate Original Cash Receipt and Worksheet ​

  • Source: write_off_packet (for cash_receipt_id); cash_receipt_worksheet joined to cash_receipt_split.
  • Action: SELECT from write_off_packet; SELECT from cash_receipt_worksheet joined to cash_receipt_split.
  • Logic:
    • Fetch cash_receipt_id from write_off_packet where write_off_packet_id = :packetId.
    • If no cash_receipt_id, reject with "No cash receipt found for this packet."
    • Find the current worksheet: query cash_receipt_worksheet inner joined to cash_receipt_split where cash_receipt_split.cash_receipt_id = :cashReceiptId and cash_receipt_worksheet.current_item_ind = true.
    • If no current worksheet, reject.

Step 2. Mark Original Worksheet as Non-Current ​

  • Source: Original cash_receipt_worksheet from Step 1.
  • Action: UPDATE cash_receipt_worksheet.
  • Logic:
    • Set current_item_ind = false.

Step 3. Insert Reversal Worksheet ​

  • Source: Original cash_receipt_worksheet from Step 1.
  • Action: INSERT into cash_receipt_worksheet.
  • Logic:
    • Set cash_receipt_split_id = same split ID as original worksheet.
    • Set cash_receipt_worksheet_status_cd = 'A' (terminal).
    • Set current_item_ind = false (terminal — no current worksheet after recovery).
    • Set approved_dt = current timestamp.
    • Set approved_by = acting user ID.
    • Set previous_worksheet_id = original worksheet ID.

Step 4. Create Reversal Applications and Update Billing Records ​

  • Source: Original cash_receipt_application rows from the original worksheet; reversal worksheet from Step 3.
  • Action: For each original application, INSERT cash_receipt_application; UPDATE billing_item_detail; UPDATE billing_item.
  • Logic:
    • Fetch all cash_receipt_application rows from the original worksheet.
    • For each original application with a cash_receipt_amt_applied value:
      • Insert a reversal cash_receipt_application row:
        • Set cash_receipt_worksheet_id = reversal worksheet ID.
        • Set billing_item_detail_id = same as original.
        • Set cash_receipt_amt_applied = original_amount * -1 (negated).
        • Set reversal_of_application_id = original cash_receipt_application_id.
        • Set reversal_reason_cd = 'PACKET_RECOVERY'.
      • UPDATE the associated billing_item_detail: set write_off_status_cd = 'RECOVERED', recovered_dt = current timestamp.
    • For each unique billing_item_id encountered: UPDATE billing_item — set open_item_ind = true (reopens the receivable).

Side-effects:

  • Original cash_receipt_worksheet.current_item_ind is set to false.
  • billing_item_detail.write_off_status_cd = 'RECOVERED' for all affected details.
  • billing_item.open_item_ind = true for all affected billing items.

Postconditions:

  • A reversal cash_receipt_worksheet (status 'A') exists with negative application amounts.
  • The original worksheet is non-current.
  • All billing items in the packet are reopened.

2.20 Upload Document ​

Operation: uploadDocument

Trigger: User attaches a document to a packet or to a specific receivable within a packet.

Input Parameters:

  • writeOffPacketId: UUID (required) — packet_document.write_off_packet_id
  • packetReceivableId: UUID (optional) — packet_document.packet_receivable_id; omit for packet-level documents
  • documentName: String (required) — packet_document.document_name
  • documentTypeCd: String (required) — packet_document.document_type_cd
  • fileData: Binary — file contents
  • actorId: Integer — identifier for the user performing the operation

Validates the file, uploads it to object storage, and records the document metadata in packet_document.

Step 1. Validate Packet and File ​

  • Source: write_off_packet (record matching writeOffPacketId); file metadata.
  • Action: SELECT from write_off_packet; validate file properties.
  • Logic:
    • If packet not found or not editable, reject.
    • Validate file size <= 10,485,760 bytes (10 MB).
    • Validate MIME type is in the allowed list: application/pdf, image/jpeg, image/png, image/gif, application/vnd.ms-excel, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet, application/msword, application/vnd.openxmlformats-officedocument.wordprocessingml.document, text/plain, text/csv.

Step 2. Upload File to Object Storage ​

  • Source: File data from input.
  • Action: Upload to object storage; receive URL.
  • Logic:
    • Upload file to object storage and obtain the storage URL.

**PoC Artifact:** Object storage integration is not implemented. The service generates placeholder URLs in the format `s3://write-off-docs/{packetId}/{timestamp}`. Production must implement actual file upload.

Step 3. Insert Document Record ​

  • Source: Upload URL from Step 2; input parameters.
  • Action: INSERT into packet_document.
  • Logic:
    • Set packet_document_id = auto-generated UUID.
    • Set write_off_packet_id = provided packet ID.
    • Set packet_receivable_id = provided receivable ID, or NULL if packet-level.
    • Set document_name = provided file name.
    • Set document_type_cd = provided type code.
    • Set document_url = URL from Step 2.
    • Set document_size_bytes = file size in bytes.
    • Set mime_type = detected or provided MIME type.
    • Set uploaded_by_user_id = acting user ID.
    • Set uploaded_dt = current timestamp.

Side-effects:

  • File is persisted in object storage.

Postconditions:

  • A packet_document row exists with the document metadata and storage URL.
  • If packet_receivable_id is populated, the document is scoped to that specific receivable.
  • If packet_receivable_id is null, the document is shared across all receivables in the packet.

2.21 Delete Document ​

Operation: deleteDocument

Trigger: User removes an uploaded document from a packet.

Input Parameters:

  • packetDocumentId: UUID (required) — packet_document.packet_document_id
  • actorId: Integer — identifier for the user performing the operation

Removes a document from object storage and deletes the corresponding packet_document row.

Step 1. Fetch Document and Validate Parent Packet ​

  • Source: packet_document (record matching packetDocumentId); write_off_packet.
  • Action: SELECT from packet_document; SELECT from write_off_packet.
  • Logic:
    • If packet_document not found, reject.
    • Fetch parent write_off_packet by write_off_packet_id.
    • If packet not editable, reject.

Step 2. Delete from Object Storage ​

  • Source: packet_document.document_url.
  • Action: Delete file from object storage.
  • Logic:
    • Remove the stored file at the URL recorded in document_url.

Step 3. Delete Document Record ​

  • Source: packet_document (record from Step 1).
  • Action: DELETE from packet_document.
  • Logic:
    • Delete the packet_document row matching packet_document_id.

Side-effects:

  • File is removed from object storage.

Postconditions:

  • No packet_document row exists for this packet_document_id.
  • The stored file is removed from object storage.

2.22 Update Document Metadata ​

Operation: updateDocumentMetadata

Trigger: User modifies the display name or type classification of an existing document.

Input Parameters:

  • packetDocumentId: UUID (required) — packet_document.packet_document_id
  • documentName: String (optional) — packet_document.document_name
  • documentTypeCd: String (optional) — packet_document.document_type_cd
  • actorId: Integer — identifier for the user performing the operation

Modifies the display name or type of an existing document without changing the underlying file.

Step 1. Fetch Document and Validate Parent Packet ​

  • Source: packet_document (record matching packetDocumentId); write_off_packet.
  • Action: SELECT from packet_document; SELECT from write_off_packet.
  • Logic:
    • If packet_document not found, reject.
    • Fetch parent write_off_packet and validate editable status.

Step 2. Update Document Fields ​

  • Source: Input parameters.
  • Action: UPDATE packet_document.
  • Logic:
    • Set provided fields (document_name, document_type_cd) to new values.
    • Only the document metadata is changed; the file in object storage is unaffected.

Side-effects:

  • None beyond the field update on packet_document.

Postconditions:

  • packet_document reflects the updated document_name and/or document_type_cd.

2.23 Record Status History ​

Operation: recordStatusChange

Trigger: Called as the final step of every status-changing procedure in this document.

Input Parameters:

  • writeOffPacketId: UUID (required) — packet_status_history.write_off_packet_id
  • fromStatusCd: String (optional) — packet_status_history.from_status_cd; null for initial creation
  • toStatusCd: String (required) — packet_status_history.to_status_cd
  • actionCd: String (required) — packet_status_history.action_cd
  • approverRole: String (optional) — packet_status_history.approver_role; null for non-approval actions
  • commentText: String (optional) — packet_status_history.comment_text; required when actionCd = 'REJECT'
  • actionByUserId: Integer (required) — packet_status_history.action_by_user_id

Creates an immutable audit record for a status transition. Never called standalone — invoked as the final step of any status-changing operation.

Step 1. Insert Status History Record ​

  • Source: Input parameters.
  • Action: INSERT into packet_status_history.
  • Logic:
    • Set packet_status_history_id = auto-generated UUID.
    • Set write_off_packet_id = provided packet ID.
    • Set from_status_cd = provided value (null for initial creation).
    • Set to_status_cd = provided value.
    • Set action_cd = provided action code (CREATE, SUBMIT, APPROVE, REJECT, RESUBMIT, RECOVER, COMPLETE).
    • Set approver_role = provided role (null for non-approval actions such as SUBMIT, RESUBMIT, RECOVER).
    • Set comment_text = provided comment (required when action_cd = 'REJECT').
    • Set action_by_user_id = provided user ID.
    • Set action_dt = current timestamp.

History records are **immutable**. They are never updated or deleted after creation (except via cascade when a DRAFT packet is deleted). This provides a complete, tamper-resistant audit trail for compliance.

Side-effects:

  • None beyond the inserted packet_status_history row.

Postconditions:

  • One immutable packet_status_history row exists recording the transition.

3. Business Rules & Logic ​

3.1 REV-Only Write-Off Constraint ​

Business rule: Only REV-type billing item details can be written off. PAY-type details are never written off because the client's payout obligation persists regardless of collectability. The company absorbs the loss on its own commission (REV) while the PAY obligation remains unchanged.

Data-level enforcement:

  • Read: billing_item_detail.billing_item_detail_type_cd must equal 'REV' for each receivable added to a packet.
  • Guard (eligible receivable search): Only returns billing_item_detail rows where billing_item_detail_type_cd = 'REV' and revBalance > 0.
  • Guard (submission validation): Rule INVALID_TYPE rejects any receivable where billing_item_detail_type_cd != 'REV'.
  • Write (aggregate calculation): Only REV-type receivables contribute to write_off_packet.total_commission_amt and receivable_count.

Despite the REV-only business rule, the simplified write-off cash receipt flow (procedure 2.17) closes out **both** REV and PAY billing item details on the billing item. This is intentional — the full billing item is zeroed out through the write-off receipt, even though only the REV portion represents the company's loss.

3.2 Submission Validation Rules ​

Business rule: A packet must meet all of the following conditions before it can transition from DRAFT to SUBMITTED.

Data-level enforcement:

RuleError CodeReadGuard
At least one receivableNO_RECEIVABLESCount of packet_receivable for packetIf count < 1, reject
Single clientMULTIPLE_CLIENTS / CLIENT_MISMATCHbilling_item_detail.client_id for each receivableIf any client_id differs from write_off_packet.client_id, reject
REV-type onlyINVALID_TYPEbilling_item_detail.billing_item_detail_type_cd for each receivableIf any value != 'REV', reject
Eligibility assignedMISSING_ELIGIBILITYpacket_receivable.eligibility_criteria_cd for each receivableIf any value is null or empty, reject
Documentation presentMISSING_DOCUMENTATION / NO_PACKET_DOCUMENTATIONpacket_document rows for packet and receivablesIf any receivable lacks coverage, reject
No duplicate packetsALREADY_IN_PACKETpacket_receivable joined to write_off_packet where packet_status_cd is activeIf any receivable is in another active packet, reject

Warning conditions (non-blocking):

  • MIXED_ELIGIBILITY: packet contains receivables with multiple different eligibility_criteria_cd values.

**PoC Artifact:** The documentation validation rule (`MISSING_DOCUMENTATION` / `NO_PACKET_DOCUMENTATION`) is currently **disabled** in the PoC. The validation function exists but returns `passed: true` unconditionally for the documentation check. Re-enable for production.

3.3 Approval Amount Thresholds ​

Business rule: The depth of the approval chain is determined by write_off_packet.total_commission_amt. Larger write-offs require more senior approval.

Data-level enforcement:

  • Read: write_off_packet.total_commission_amt (parsed as a decimal).
  • Guard: The state machine reads total_commission_amt at each approval step to determine whether the packet completes or advances to the next approver.
  • Write: write_off_packet.current_approver_role is set to the next approver's role code, or NULL if the chain is complete.
total_commission_amtRequired Approvers
< $50,000AGENT, DEPT_HEAD, VP_CLIENT_ACCT
$50,000 to $250,000AGENT, DEPT_HEAD, VP_CLIENT_ACCT, CFO
> $250,000AGENT, DEPT_HEAD, VP_CLIENT_ACCT, CFO, MD

These thresholds are configurable via the `ApprovalThresholds` configuration object (`vp_max_amount = $50,000`, `cfo_max_amount = $250,000`). The values above are the defaults.

3.4 Status Editability Rules ​

Business rule: A packet is editable only in DRAFT or any REJECTED_* status. All other statuses are read-only.

Data-level enforcement:

  • Read: write_off_packet.packet_status_cd.
  • Guard: If packet_status_cd != 'DRAFT' and packet_status_cd does not start with 'REJECTED_', reject all receivable add/remove, field edit, and document upload/delete operations.

3.5 Deletion Rules ​

Business rule: Only DRAFT packets can be deleted. There is no CANCELLED status. Once submitted, a packet persists permanently.

Data-level enforcement:

  • Read: write_off_packet.packet_status_cd.
  • Guard: If packet_status_cd != 'DRAFT', reject the delete operation with "Only draft packets can be deleted."

3.6 Single-Packet Exclusivity ​

Business rule: A billing_item_detail can exist in at most one active packet at a time.

Data-level enforcement:

  • Read: packet_receivable joined to write_off_packet where billing_item_detail_id = :id and write_off_packet.packet_status_cd IN ('DRAFT', 'SUBMITTED', 'APPROVED_AGENT', 'APPROVED_DEPT_HEAD', 'APPROVED_VP', 'APPROVED_CFO').
  • Guard: If count > 0, reject with "Receivable is already in another active packet."

3.7 Eligibility Criteria ​

Business rule: Each receivable requires one of four eligibility criteria codes before the packet can be submitted.

Data-level enforcement:

  • Read: packet_receivable.eligibility_criteria_cd for each receivable.
  • Guard (submission): If any receivable has a null or empty eligibility_criteria_cd, submission is rejected with MISSING_ELIGIBILITY.
CodeAutomatic Assignment?Documentation Type
AGEDRecommended when days_outstanding >= 180 from invoice dateAny
UNCOLLECTIBLEManualCOLLECTION_LOG
BANKRUPTCYManualCOURT_DOC
AGENT_REQUESTManualAGENT_REQUEST

3.8 Minimum Amount Threshold ​

Business rule: Receivables with billing_item_detail_amt < $100.00 are not eligible for write-off, preventing approval workflow overhead for trivially small amounts.

Data-level enforcement:

  • Read: billing_item_detail.billing_item_detail_amt during the eligible receivable search.
  • Guard: Items where billing_item_detail_amt < 100.00 are excluded from the eligible receivables query results.

3.9 Recovery Rules ​

Business rule: Recovery is terminal. Only COMPLETE (multi-level) or APPROVED (simplified) packets can be recovered. Once recovered, a packet cannot be modified.

Data-level enforcement:

  • Read: write_off_packet.packet_status_cd.
  • Guard: If packet_status_cd != 'COMPLETE' (multi-level) or packet_status_cd != 'APPROVED' (simplified), reject.
  • Write: Sets packet_status_cd to 'RECOVERED' — terminal state. No further status changes are permitted.

3.10 Packet Name Uniqueness ​

Business rule: write_off_packet.packet_name must be unique across all packets (including those in all statuses).

Data-level enforcement:

  • Read: write_off_packet.packet_name (all rows).
  • Guard: On create and on name-change updates, query for any other row with the same packet_name. If found, reject.
  • Write: Enforced at the database level by the write_off_packet_packet_name_unique unique constraint.

3.11 Rejection Reason Accumulation (Simplified Workflow) ​

Business rule: In the simplified workflow, rejection reasons accumulate over the packet's lifetime in a single rejection_reason field.

Data-level enforcement:

  • Read: write_off_packet.rejection_reason (existing value).
  • Write: Each new rejection appends to rejection_reason in the format [{ISO_timestamp}|{userId}] {reason}, separated by double newlines. In the multi-level workflow, rejection details are stored exclusively in packet_status_history records rather than being accumulated on the packet.

4. Field Mapping & Transformation ​

4.1 write_off_packet Status Update Fields ​

Each status-changing procedure updates a specific subset of fields beyond packet_status_cd and current_approver_role.

ProcedureAdditional Fields Updated
Submit (2.10)submitted_dt, submitted_by_user_id
Complete / Execute Write-Off (2.16, 2.17)completed_dt, completed_by_user_id, cash_receipt_id (simplified only via 2.17)
Reject Simplified (2.14)rejected_dt, rejected_by_user_id, rejection_reason, submitted_dt (cleared), submitted_by_user_id (cleared)
Recover (2.18, 2.19)recovered_dt, recovered_by_user_id

4.2 billing_item_detail Write-Off Fields ​

Source Table.FieldTarget Table.FieldTransform
Packet approval reaching COMPLETEbilling_item_detail.write_off_status_cdSet to 'WRITTEN_OFF'
write_off_packet.write_off_packet_idbilling_item_detail.write_off_packet_idCopied as-is
Current timestamp (on write-off)billing_item_detail.write_off_dtSystem-generated timestamp
Literal truebilling_item_detail.exclude_from_cecl_indDefaulted to true on write-off (simplified flow only; multi-level flow does not set this field)
Multi-level recoverybilling_item_detail.write_off_status_cdReverted to 'NOT_WRITTEN_OFF'
Multi-level recoverybilling_item_detail.write_off_packet_idSet to NULL
Multi-level recoverybilling_item_detail.write_off_dtSet to NULL
Simplified recoverybilling_item_detail.write_off_status_cdSet to 'RECOVERED'
Current timestamp (on recovery)billing_item_detail.recovered_dtSystem-generated timestamp (both flows)

4.3 Aggregate Calculation Formula ​

text
REV_receivables = packet_receivable
    WHERE billing_item_detail_type_cd = 'REV'

receivable_count = COUNT(REV_receivables)

total_commission_amt = ROUND(
    SUM(CAST(billing_item_detail_amt AS DECIMAL)
        FOR EACH REV_receivable),
    2
)
  • Source: packet_receivable joined to billing_item_detail.billing_item_detail_amt
  • Precision: decimal(20,2) on write_off_packet.total_commission_amt
  • Filter: Only billing_item_detail_type_cd = 'REV' rows count

4.4 Original Write-Off Worksheet → Reversal Worksheet ​

Source Table.FieldTarget Table.FieldTransform
cash_receipt_worksheet.cash_receipt_split_idcash_receipt_worksheet.cash_receipt_split_idCopied as-is (same split reused)
—cash_receipt_worksheet.cash_receipt_worksheet_status_cdDefaulted to 'A' (auto-approved)
—cash_receipt_worksheet.current_item_indDefaulted to false (terminal)
cash_receipt_worksheet.cash_receipt_worksheet_idcash_receipt_worksheet.previous_worksheet_idCopied as-is (links reversal to original)
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 (original * -1)
cash_receipt_application.cash_receipt_application_idcash_receipt_application.reversal_of_application_idCopied as-is (links reversal application to original)
—cash_receipt_application.reversal_reason_cdDefaulted to 'PACKET_RECOVERY'

4.5 Approval State Machine Inputs and Outputs ​

Input: write_off_packet.packet_status_cd + write_off_packet.total_commission_amt (parsed as decimal)

Output: next_status (new packet_status_cd) + next_approver_role (new current_approver_role, or NULL if complete) + is_complete (boolean)

The state machine is a pure function with no side effects. It does not write to the database; it only computes the next state. The calling procedure (2.11) is responsible for applying the state change and invoking write-off execution when is_complete = true.

5. Cross-References ​

DocumentRelationship
Write-Offs Data ModelComplete field definitions, types, nullability, defaults, cascade behavior, status lifecycle diagram, validation constraints, and code master values for all four write-off tables plus the billing_item_detail write-off fields.
Write-Offs QueriesEvery read query in the write-off domain: packet retrieval, receivable enrichment, eligible receivable search, status history retrieval, and document queries. Includes join specifications, computed values, and filter conditions.
Billing Items Data Modelpacket_receivable.billing_item_detail_id → billing_item_detail.billing_item_detail_id. Write-off procedures update write_off_status_cd, write_off_packet_id, write_off_dt, recovered_dt, and exclude_from_cecl_ind on billing_item_detail. Write-off execution also updates billing_item.open_item_ind.
Cash Receipts Data Modelwrite_off_packet.cash_receipt_id → cash_receipt.cash_receipt_id. Write-off completion creates cash_receipt, cash_receipt_split, cash_receipt_worksheet, and cash_receipt_application records. Recovery creates reversal worksheets with negative application amounts.
Worksheets Data ModelWrite-off worksheets are auto-approved (status 'A') and use cash_receipt_worksheet.current_item_ind to track the active worksheet. Reversal worksheets link to the original via previous_worksheet_id.
Parties Data Modelwrite_off_packet.client_id → party.party_id. Each packet is scoped to a single client; all receivables must belong to that client.

Confidential. For internal use only.

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