Write-Offs Queries

1. Executive Summary

This document catalogs every query operation in the write-offs domain, covering the four core tables — write_off_packet, packet_receivable, packet_status_history, and packet_document — plus cross-domain queries that reach into billing_item_detail, billing_item, party, deal, users, comment, cash_receipt_worksheet, cash_receipt_split, and billing_item_document.

The queries fall into five functional areas:

Packet management — Creating, reading, updating, deleting, and searching write-off packets. The most complex query in this group is the enriched packet-with-client lookup, which joins six aliased instances of the users table to resolve the names of every actor (creator, updater, submitter, completer, rejector, recoverer).
Receivable management — Adding receivables to packets, removing them, enriching them with billing and party data, and searching for eligible receivables that are not yet in any packet. The eligible receivable search applies the core constraint that only REV-type, non-written-off, non-packeted billing item details qualify.
Approval workflow — Status transitions driven by the approval state machine, including submit, approve, reject, resubmit, complete, and recover. These are primarily writes (status updates, history inserts), but they include read queries for permission checks and role matching.
Status history — Querying the immutable audit trail for timeline displays, approval-only views, rejection checks, and latest-status lookups. History queries join to users to resolve actor names.
Document management — Uploading, retrieving, and organizing documents at both the packet level and the individual receivable level. Includes aggregate queries that combine both levels into a unified document list.

All queries are described in technology-neutral terms. Column names use snake_case. The data model reference at ../data-model/write-offs.md provides the full schema definitions, status lifecycle, and validation rules.

2. Key Queries

2.1 Packet Queries

2.1.1 Get Packet by ID

Operation: getPacketById

Input Parameters:

write_off_packet_id: UUID (required) — write_off_packet.write_off_packet_id

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`write_off_packet`	—	—	—	Base record only; no joins required

Filters:

write_off_packet.write_off_packet_id = :write_off_packet_id
LIMIT 1

Computed Values:

None

Returns: Single write_off_packet row or null.

2.1.2 Get Packet with Client

Operation: getPacketWithClient

Input Parameters:

write_off_packet_id: UUID (required) — write_off_packet.write_off_packet_id

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`write_off_packet`	LEFT	`party`	`write_off_packet.client_id = party.party_id`	Resolve client display name
`write_off_packet`	LEFT	`users` (as `created_by_user`)	`write_off_packet.created_by_user_id = users.user_id`	Resolve creator name
`write_off_packet`	LEFT	`users` (as `updated_by_user`)	`write_off_packet.updated_by_user_id = users.user_id`	Resolve last updater name
`write_off_packet`	LEFT	`users` (as `submitted_by_user`)	`write_off_packet.submitted_by_user_id = users.user_id`	Resolve submitter name
`write_off_packet`	LEFT	`users` (as `completed_by_user`)	`write_off_packet.completed_by_user_id = users.user_id`	Resolve final approver name
`write_off_packet`	LEFT	`users` (as `rejected_by_user`)	`write_off_packet.rejected_by_user_id = users.user_id`	Resolve rejector name
`write_off_packet`	LEFT	`users` (as `recovered_by_user`)	`write_off_packet.recovered_by_user_id = users.user_id`	Resolve recoverer name

Filters:

write_off_packet.write_off_packet_id = :write_off_packet_id
LIMIT 1

Computed Values:

client_name: party.display_name
created_by_user_name: users.first_name || ' ' || users.last_name (from created_by_user alias)
updated_by_user_name: users.first_name || ' ' || users.last_name (from updated_by_user alias)
submitted_by_user_name: users.first_name || ' ' || users.last_name (from submitted_by_user alias)
completed_by_user_name: users.first_name || ' ' || users.last_name (from completed_by_user alias)
rejected_by_user_name: users.first_name || ' ' || users.last_name (from rejected_by_user alias)
recovered_by_user_name: users.first_name || ' ' || users.last_name (from recovered_by_user alias)

Returns: Single enriched packet row with client name and all actor display names, or null.

NOTE

Six aliased joins to users are required because the packet tracks six distinct actor references. All joins are LEFT to handle nullable timestamps (e.g., a packet in DRAFT has no submitted/completed/rejected/recovered user).

2.1.3 Get Packet with Full Details

Operation: getPacketWithDetails

Input Parameters:

write_off_packet_id: UUID (required) — write_off_packet.write_off_packet_id

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`write_off_packet`	LEFT	`party`	`write_off_packet.client_id = party.party_id`	Same as `getPacketWithClient` (Step 1)
`write_off_packet`	LEFT	`users` (6 aliases)	See 2.1.2	Resolve all actor names (Step 1)
`packet_receivable`	—	—	`packet_receivable.write_off_packet_id = :write_off_packet_id`	Fetch associated receivables (Step 2)
`packet_status_history`	—	—	`packet_status_history.write_off_packet_id = :write_off_packet_id`	Fetch status history (Step 3)

Filters:

Step 1: write_off_packet.write_off_packet_id = :write_off_packet_id
Step 2: packet_receivable.write_off_packet_id = :write_off_packet_id
Step 3: packet_status_history.write_off_packet_id = :write_off_packet_id, ordered by action_dt DESC

Computed Values:

Same as getPacketWithClient for actor name fields

Returns: Enriched packet row combined with an array of packet_receivable rows and an array of packet_status_history rows ordered most-recent-first.

NOTE

This is a composite query executed in three sequential fetches and merged in application code. Step 1 reuses getPacketWithClient. Steps 2 and 3 are separate queries filtered by write_off_packet_id.

2.1.4 Search Packets

Operation: searchPackets

Input Parameters:

packet_name: String (optional) — write_off_packet.packet_name — partial, case-insensitive match
client_id: Integer (optional) — write_off_packet.client_id
packet_status_cd: String or array of strings (optional) — write_off_packet.packet_status_cd
current_approver_role: String (optional) — write_off_packet.current_approver_role
submitted_from_dt: Date (optional) — lower bound on write_off_packet.submitted_dt
submitted_to_dt: Date (optional) — upper bound on write_off_packet.submitted_dt
created_by_user_id: Integer (optional) — write_off_packet.created_by_user_id

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`write_off_packet`	LEFT	`party`	`write_off_packet.client_id = party.party_id`	Resolve client display name for list view

Filters:

If packet_name provided: write_off_packet.packet_name ILIKE '%:packet_name%'
If client_id provided: write_off_packet.client_id = :client_id
If packet_status_cd is a single value: write_off_packet.packet_status_cd = :status
If packet_status_cd is an array: write_off_packet.packet_status_cd IN (:statuses)
If current_approver_role provided: write_off_packet.current_approver_role = :role
If submitted_from_dt provided: write_off_packet.submitted_dt >= :submitted_from_dt
If submitted_to_dt provided: write_off_packet.submitted_dt <= :submitted_to_dt
If created_by_user_id provided: write_off_packet.created_by_user_id = :created_by_user_id
Default order: write_off_packet.created_dt DESC

Computed Values:

client_name: party.display_name

Returns: Array of enriched packet rows with client names, ordered by created_dt descending.

2.1.5 Get Packets by Status

Operation: getPacketsByStatus

Input Parameters:

packet_status_cd: String (required) — write_off_packet.packet_status_cd

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`write_off_packet`	LEFT	`party`	`write_off_packet.client_id = party.party_id`	Resolve client display name

Filters:

write_off_packet.packet_status_cd = :packet_status_cd

Computed Values:

client_name: party.display_name

Returns: Array of enriched packet rows matching the given status.

Variant: Pending Approval by Role

Operation: getPacketsPendingApproval

Additional filter: write_off_packet.current_approver_role = :current_approver_role

All other aspects identical to 2.1.4 Search Packets.

2.1.6 Check Packet Name Uniqueness

Operation: isPacketNameUnique

Input Parameters:

packet_name: String (required) — write_off_packet.packet_name
exclude_packet_id: UUID (optional) — write_off_packet.write_off_packet_id — when provided, excludes the named packet from the check (used for updates)

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`write_off_packet`	—	—	—	Name uniqueness check; no joins required

Filters:

write_off_packet.packet_name = :packet_name
When exclude_packet_id provided: result is unique if the count is zero or the only matching row has write_off_packet_id = :exclude_packet_id
LIMIT 1

Computed Values:

None

Returns: Boolean — true if the name is available for use.

2.1.7 Get Worksheet ID by Cash Receipt ID

Operation: getWorksheetIdByCashReceiptId

Input Parameters:

cash_receipt_id: Integer (required) — cash_receipt_split.cash_receipt_id

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`cash_receipt_worksheet`	INNER	`cash_receipt_split`	`cash_receipt_worksheet.cash_receipt_split_id = cash_receipt_split.cash_receipt_split_id`	Resolve the split's parent receipt

Filters:

cash_receipt_split.cash_receipt_id = :cash_receipt_id
Order by cash_receipt_worksheet.cash_receipt_worksheet_id DESC
LIMIT 1

Computed Values:

None

Returns: The most recent cash_receipt_worksheet_id (integer) linked to the given cash receipt, or null.

2.2 Receivable Queries

2.2.1 Get Receivable by ID

Operation: getReceivableById

Input Parameters:

packet_receivable_id: UUID (required) — packet_receivable.packet_receivable_id

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`packet_receivable`	—	—	—	Base record only; no joins required

Filters:

packet_receivable.packet_receivable_id = :packet_receivable_id
LIMIT 1

Computed Values:

None

Returns: Single packet_receivable row (base fields only, without billing or client enrichment), or null.

2.2.2 Get Receivable with Details

Operation: getReceivableWithDetails

Input Parameters:

packet_receivable_id: UUID (required) — packet_receivable.packet_receivable_id

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`packet_receivable`	INNER	`billing_item_detail`	`packet_receivable.billing_item_detail_id = billing_item_detail.billing_item_detail_id`	Fetch amount, type, and write-off status
`packet_receivable`	INNER	`billing_item`	`billing_item_detail.billing_item_id = billing_item.billing_item_id`	Fetch billing item identifiers and client FK
`packet_receivable`	LEFT	`party`	`billing_item.client_id = party.party_id`	Resolve client display name

Filters:

packet_receivable.packet_receivable_id = :packet_receivable_id
LIMIT 1

Computed Values:

client_name: party.display_name
invoice_id: correlated subquery — SELECT document_id FROM billing_item_document WHERE billing_item_id = billing_item.billing_item_id AND document_type_cd = CASE WHEN billing_item_detail_type_cd = 'REV' THEN 'CI' WHEN billing_item_detail_type_cd = 'PAY' THEN 'BI' END LIMIT 1

Returns: Single receivable row enriched with billing item detail amounts, billing item identifiers, client name, and invoice document ID, or null.

2.2.3 Get Receivables by Packet

Operation: getReceivablesByPacket

Input Parameters:

write_off_packet_id: UUID (required) — packet_receivable.write_off_packet_id

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`packet_receivable`	INNER	`billing_item_detail`	`packet_receivable.billing_item_detail_id = billing_item_detail.billing_item_detail_id`	Fetch amount, type, write-off status
`packet_receivable`	INNER	`billing_item`	`billing_item_detail.billing_item_id = billing_item.billing_item_id`	Fetch billing item identifiers and FKs
`packet_receivable`	LEFT	`party`	`billing_item.client_id = party.party_id`	Resolve client display name
`packet_receivable`	LEFT	`deal`	`billing_item.deal_id = deal.deal_id`	Resolve deal name
`packet_receivable`	LEFT	`party` (as `buyer_party`)	`billing_item.buyer_id = buyer_party.party_id`	Resolve buyer display name

Filters:

packet_receivable.write_off_packet_id = :write_off_packet_id
Order by packet_receivable.created_dt ASC

Computed Values:

client_name: party.display_name
buyer_name: buyer_party.display_name
deal_name: deal.deal_name
invoice_id: correlated subquery — same pattern as 2.2.2
latest_comment_text: correlated subquery — SELECT comment_text FROM comment WHERE commentable_type = 'RECEIVABLE' AND commentable_id = packet_receivable.packet_receivable_id ORDER BY comment_version DESC LIMIT 1
comment_count: correlated subquery — SELECT COUNT(*)::integer FROM comment WHERE commentable_type = 'RECEIVABLE' AND commentable_id = packet_receivable.packet_receivable_id

Returns: Array of fully enriched receivable rows with billing details, client name, buyer name, deal name, latest comment, and comment count.

NOTE

The comment table uses a polymorphic pattern where commentable_type + commentable_id identify the owning entity. For receivable-level comments, commentable_type = 'RECEIVABLE' and commentable_id = packet_receivable.packet_receivable_id.

2.2.4 Search Eligible Receivables

Operation: searchEligibleReceivables

Input Parameters:

client_id: Integer (optional) — billing_item.client_id
min_amount: Decimal (optional) — lower bound on billing_item_detail.billing_item_detail_amt
max_amount: Decimal (optional) — upper bound on billing_item_detail.billing_item_detail_amt

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`billing_item_detail`	INNER	`billing_item`	`billing_item_detail.billing_item_id = billing_item.billing_item_id`	Fetch billing item identifiers and client FK
`billing_item_detail`	LEFT	`party`	`billing_item.client_id = party.party_id`	Resolve client display name

Filters:

Always applied: billing_item_detail.billing_item_detail_type_cd = 'REV'
Always applied: billing_item_detail.write_off_status_cd = 'NOT_WRITTEN_OFF'
Always applied: billing_item_detail.write_off_packet_id IS NULL
If client_id provided: billing_item.client_id = :client_id
If min_amount provided: billing_item_detail.billing_item_detail_amt >= :min_amount
If max_amount provided: billing_item_detail.billing_item_detail_amt <= :max_amount

Computed Values:

client_name: party.display_name
invoice_id: correlated subquery — same pattern as 2.2.2
Null placeholders for packet_receivable_id, write_off_packet_id, and eligibility_criteria_cd (rows are not yet assigned to any packet)

Returns: Array of candidate receivable rows with billing details, suitable for display in the "Add Receivables" selection dialog.

IMPORTANT

The three always-applied filters enforce the core write-off eligibility constraints: only REV-type, only non-written-off, only un-packeted. billing_item_detail.write_off_packet_id IS NULL ensures the receivable is not already claimed by a completed packet.

2.2.5 Search Eligible Receivables (Service-Level Variant)

Operation: searchEligibleReceivablesFromBilling

Input Parameters:

client_id: Integer (optional) — billing_item.client_id
open_item_only: Boolean (fixed true) — billing_item.open_item_ind
current_item_only: Boolean (fixed true) — billing_item.current_item_ind

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`billing_item`	INNER	`billing_item_detail`	`billing_item.billing_item_id = billing_item_detail.billing_item_id`	Calculate REV and PAY balances

Filters:

billing_item.open_item_ind = true
billing_item.current_item_ind = true
If client_id provided: billing_item.client_id = :client_id
Post-query in-memory filter: rev_balance > 0

Computed Values:

rev_balance: calculated balance from billing_item_detail.billing_item_detail_amt (REV type) minus sum of applied amounts from cash_receipt_application

Returns: Array of billing item display records with positive REV balances, suitable for write-off candidate selection.

NOTE

PoC Artifact: This service-level variant delegates to the billing repository's general display query and applies in-memory filtering. The filtering for items already in active packets is noted as incomplete (TODO in the PoC). Production should push all eligibility filters to the database query.

2.2.6 Check Receivable in Any Active Packet

Operation: isReceivableInAnyPacket

Input Parameters:

billing_item_detail_id: Integer (required) — packet_receivable.billing_item_detail_id

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`packet_receivable`	INNER	`write_off_packet`	`packet_receivable.write_off_packet_id = write_off_packet.write_off_packet_id`	Check the packet's current status

Filters:

packet_receivable.billing_item_detail_id = :billing_item_detail_id
write_off_packet.packet_status_cd IN ('DRAFT', 'SUBMITTED', 'APPROVED_AGENT', 'APPROVED_DEPT_HEAD', 'APPROVED_VP', 'APPROVED_CFO')

Computed Values:

COUNT(*) cast to integer

Returns: Boolean — true if count > 0, meaning the receivable is already in an active (non-terminal) packet.

NOTE

The status list represents all non-terminal, non-rejected statuses. A receivable in a COMPLETE or RECOVERED packet is considered free for re-packaging.

2.2.7 Get Receivables by Client

Operation: getReceivablesByClient

Input Parameters:

client_id: Integer (required) — billing_item.client_id

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`packet_receivable`	INNER	`billing_item_detail`	`packet_receivable.billing_item_detail_id = billing_item_detail.billing_item_detail_id`	Fetch billing detail fields
`packet_receivable`	INNER	`billing_item`	`billing_item_detail.billing_item_id = billing_item.billing_item_id`	Fetch billing item identifiers and client FK
`packet_receivable`	LEFT	`party`	`billing_item.client_id = party.party_id`	Resolve client display name

Filters:

billing_item.client_id = :client_id

Computed Values:

client_name: party.display_name
invoice_id: correlated subquery — same pattern as 2.2.2

Returns: Array of enriched receivable rows across all packets for the given client, used for client-level reporting and audit views.

2.2.8 Count Receivables in Packet

Operation: countReceivablesInPacket

Input Parameters:

write_off_packet_id: UUID (required) — packet_receivable.write_off_packet_id

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`packet_receivable`	—	—	—	Count only; no joins required

Filters:

packet_receivable.write_off_packet_id = :write_off_packet_id

Computed Values:

COUNT(*) cast to integer

Returns: Integer count of receivables in the specified packet.

2.3 Status History Queries

2.3.1 Get History by Packet

Operation: getHistoryByPacket

Input Parameters:

write_off_packet_id: UUID (required) — packet_status_history.write_off_packet_id

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`packet_status_history`	—	—	—	Base records only; no joins required

Filters:

packet_status_history.write_off_packet_id = :write_off_packet_id
Order by packet_status_history.action_dt ASC

Computed Values:

None

Returns: Array of packet_status_history records in chronological order.

2.3.2 Get History with User Details

Operation: getHistoryWithUsersByPacket

Input Parameters:

write_off_packet_id: UUID (required) — packet_status_history.write_off_packet_id

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`packet_status_history`	LEFT	`users`	`packet_status_history.action_by_user_id = users.user_id`	Resolve actor display name and email

Filters:

packet_status_history.write_off_packet_id = :write_off_packet_id
Order by packet_status_history.action_dt ASC

Computed Values:

user_name: users.first_name || ' ' || users.last_name
user_email: users.email

Returns: Array of enriched history records with actor names and emails in chronological order, used for the packet timeline / activity log display.

2.3.3 Get Approval History

Operation: getApprovalHistory

Input Parameters:

write_off_packet_id: UUID (required) — packet_status_history.write_off_packet_id

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`packet_status_history`	LEFT	`users`	`packet_status_history.action_by_user_id = users.user_id`	Resolve actor display name and email

Filters:

packet_status_history.write_off_packet_id = :write_off_packet_id
Post-query in-memory filter: action_cd IN ('APPROVE', 'REJECT')
Order by packet_status_history.action_dt ASC

Computed Values:

user_name: users.first_name || ' ' || users.last_name
user_email: users.email

Returns: Array of history records restricted to approval and rejection actions only, in chronological order, for displaying the approval chain trail.

NOTE

PoC Artifact: The current implementation fetches all history records for the packet and filters to action_cd IN ('APPROVE', 'REJECT') in application memory. Production should push the action_cd predicate into the SQL WHERE clause.

2.3.4 Get Latest History

Operation: getLatestHistory

Input Parameters:

write_off_packet_id: UUID (required) — packet_status_history.write_off_packet_id

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`packet_status_history`	—	—	—	Base records only; no joins required

Filters:

packet_status_history.write_off_packet_id = :write_off_packet_id
Order by packet_status_history.action_dt DESC
LIMIT 1

Computed Values:

None

Returns: Single most-recent packet_status_history record for the packet, or null.

2.3.5 Get History for Specific Status

Operation: getHistoryForStatus

Input Parameters:

write_off_packet_id: UUID (required) — packet_status_history.write_off_packet_id
to_status_cd: String (required) — packet_status_history.to_status_cd

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`packet_status_history`	—	—	—	Base records only; no joins required

Filters:

packet_status_history.write_off_packet_id = :write_off_packet_id
Order by packet_status_history.action_dt DESC
LIMIT 1
Post-query in-memory filter: to_status_cd = :to_status_cd

Computed Values:

None

Returns: Single packet_status_history record representing the most recent transition to the specified target status, or null.

2.3.6 Has Been Rejected

Operation: hasBeenRejected

Input Parameters:

write_off_packet_id: UUID (required) — packet_status_history.write_off_packet_id

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`packet_status_history`	—	—	—	Base records only; no joins required

Filters:

packet_status_history.write_off_packet_id = :write_off_packet_id
Post-query in-memory check: any record has action_cd = 'REJECT'

Computed Values:

None

Returns: Boolean — true if the packet has ever been rejected at any point in its lifecycle.

2.3.7 Count Status Changes

Operation: countStatusChanges

Input Parameters:

write_off_packet_id: UUID (required) — packet_status_history.write_off_packet_id

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`packet_status_history`	—	—	—	Count only; no joins required

Filters:

packet_status_history.write_off_packet_id = :write_off_packet_id

Computed Values:

COUNT(*) cast to integer

Returns: Integer count of total status transitions recorded for the packet.

2.4 Document Queries

2.4.1 Get Document by ID

Operation: getDocumentById

Input Parameters:

packet_document_id: UUID (required) — packet_document.packet_document_id

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`packet_document`	—	—	—	Base record only; no joins required

Filters:

packet_document.packet_document_id = :packet_document_id
LIMIT 1

Computed Values:

None

Returns: Single packet_document row or null.

2.4.2 Get Document with Details

Operation: getDocumentWithDetails

Input Parameters:

packet_document_id: UUID (required) — packet_document.packet_document_id

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`packet_document`	LEFT	`write_off_packet`	`packet_document.write_off_packet_id = write_off_packet.write_off_packet_id`	Resolve parent packet name

Filters:

packet_document.packet_document_id = :packet_document_id
LIMIT 1

Computed Values:

packet_name: write_off_packet.packet_name
is_packet_level: packet_document.packet_receivable_id IS NULL
is_receivable_level: packet_document.packet_receivable_id IS NOT NULL

Returns: Single enriched document row with parent packet name and level indicators, or null.

2.4.3 Get Packet-Level Documents

Operation: getPacketLevelDocuments

Input Parameters:

write_off_packet_id: UUID (required) — packet_document.write_off_packet_id

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`packet_document`	—	—	—	Filter only; no joins required

Filters:

packet_document.write_off_packet_id = :write_off_packet_id
packet_document.packet_receivable_id IS NULL

Computed Values:

None

Returns: Array of packet-level documents shared across all receivables in the packet.

2.4.4 Get Receivable-Level Documents

Operation: getReceivableLevelDocuments

Input Parameters:

packet_receivable_id: UUID (required) — packet_document.packet_receivable_id

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`packet_document`	—	—	—	Filter only; no joins required

Filters:

packet_document.packet_receivable_id = :packet_receivable_id

Computed Values:

None

Returns: Array of documents attached to the specified receivable.

2.4.5 Get All Documents for Packet

Operation: getAllDocumentsForPacket

Input Parameters:

write_off_packet_id: UUID (required) — write_off_packet.write_off_packet_id

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`packet_document`	LEFT	`write_off_packet`	`packet_document.write_off_packet_id = write_off_packet.write_off_packet_id`	Resolve packet name (Query 1 — packet-level docs)
`packet_document`	INNER	`packet_receivable`	`packet_document.packet_receivable_id = packet_receivable.packet_receivable_id`	Scope to packet's receivables (Query 2 — receivable-level docs)
`packet_document`	LEFT	`write_off_packet`	`packet_receivable.write_off_packet_id = write_off_packet.write_off_packet_id`	Resolve packet name (Query 2)

Filters:

Query 1 (packet-level): packet_document.write_off_packet_id = :write_off_packet_id AND packet_document.packet_receivable_id IS NULL
Query 2 (receivable-level): packet_receivable.write_off_packet_id = :write_off_packet_id AND packet_document.packet_receivable_id IS NOT NULL

Computed Values:

packet_name: write_off_packet.packet_name
is_packet_level: packet_document.packet_receivable_id IS NULL
is_receivable_level: packet_document.packet_receivable_id IS NOT NULL

Returns: Combined array of all documents at both packet-level and receivable-level for the specified packet.

NOTE

The two queries are executed separately and merged in application code. Query 2 joins through packet_receivable to ensure it only picks up documents belonging to receivables within the target packet.

2.4.6 Count Packet-Level Documents

Operation: countPacketLevelDocuments

Input Parameters:

write_off_packet_id: UUID (required) — packet_document.write_off_packet_id

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`packet_document`	—	—	—	Count only; no joins required

Filters:

packet_document.write_off_packet_id = :write_off_packet_id
packet_document.packet_receivable_id IS NULL

Computed Values:

COUNT(*) cast to integer

Returns: Integer count of packet-level documents.

2.4.7 Count Receivable-Level Documents

Operation: countReceivableLevelDocuments

Input Parameters:

packet_receivable_id: UUID (required) — packet_document.packet_receivable_id

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`packet_document`	—	—	—	Count only; no joins required

Filters:

packet_document.packet_receivable_id = :packet_receivable_id

Computed Values:

COUNT(*) cast to integer

Returns: Integer count of documents attached to the specified receivable.

2.5 Write-Off Execution Queries

These queries span beyond the four core write-off tables and into the cash receipt and billing domains. They are invoked when a packet reaches COMPLETE status or is subsequently RECOVERED.

2.5.1 Get Cash Receipt ID for Packet

Operation: getCashReceiptIdForPacket

Input Parameters:

write_off_packet_id: UUID (required) — write_off_packet.write_off_packet_id

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`write_off_packet`	—	—	—	Field read only; no joins required

Filters:

write_off_packet.write_off_packet_id = :write_off_packet_id
LIMIT 1

Computed Values:

None

Returns: write_off_packet.cash_receipt_id (integer) or null, used during recovery to locate the original write-off receipt.

2.5.2 Get Current Worksheet for Cash Receipt

Operation: getCurrentWorksheetForCashReceipt

Input Parameters:

cash_receipt_id: Integer (required) — cash_receipt_split.cash_receipt_id

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`cash_receipt_worksheet`	INNER	`cash_receipt_split`	`cash_receipt_worksheet.cash_receipt_split_id = cash_receipt_split.cash_receipt_split_id`	Resolve the split's parent receipt

Filters:

cash_receipt_split.cash_receipt_id = :cash_receipt_id
cash_receipt_worksheet.current_item_ind = true

Computed Values:

None

Returns: Worksheet and split records for the active (current) worksheet associated with the write-off cash receipt, used during recovery to identify the original worksheet to be superseded.

2.5.3 Get Billing Item Display for Write-Off

Operation: getBillingItemDisplayForWriteOff

Input Parameters:

billing_item_id: Integer (required) — billing_item.billing_item_id

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`billing_item`	INNER	`billing_item_detail`	`billing_item.billing_item_id = billing_item_detail.billing_item_id`	Fetch REV and PAY details for balance calculation

Filters:

billing_item.billing_item_id = :billing_item_id

Computed Values:

rev_balance: billing_item_detail.billing_item_detail_amt (REV type) minus sum of cash_receipt_application.cash_receipt_amt_applied for REV applications
pay_balance: billing_item_detail.billing_item_detail_amt (PAY type) minus sum of cash_receipt_application.cash_receipt_amt_applied for PAY applications
rev_id: billing_item_detail.billing_item_detail_id where billing_item_detail_type_cd = 'REV'
pay_id: billing_item_detail.billing_item_detail_id where billing_item_detail_type_cd = 'PAY'
currency_cd: billing_item.currency_cd

Returns: Billing item display record with calculated REV and PAY balances, used during write-off execution to determine application amounts.

3. Calculations & Formulas

3.1 Packet Aggregate Calculation

text

filtered_receivables = WHERE billing_item_detail_type_cd = 'REV'

receivable_count = COUNT(filtered_receivables)

total_commission_amt = SUM(
    CAST(billing_item_detail_amt AS DECIMAL)
    FOR EACH row IN filtered_receivables
)

total_commission_amt = ROUND(total_commission_amt, 2)

Source: packet_receivable.billing_item_detail_id → billing_item_detail.billing_item_detail_amt, billing_item_detail.billing_item_detail_type_cd
Target: write_off_packet.total_commission_amt, write_off_packet.receivable_count
Precision: decimal(20,2)
Trigger: Recalculated and written to write_off_packet whenever receivables are added to or removed from the packet
Note: Only REV-type receivables are counted. Amounts are parsed from string to decimal before summing.

3.2 Days Outstanding Calculation

text

IF invoice_date IS NULL THEN
    days_outstanding = 0
ELSE
    days_outstanding = FLOOR((current_date - invoice_date) / (1000 * 60 * 60 * 24))
    days_outstanding = MAX(days_outstanding, 0)
END

Source: billing_item.billing_item_due_dt (invoice date proxy)
Precision: Integer (days)
Example: Invoice date 2024-01-01, current date 2026-03-02 → 791 days outstanding

3.3 Write-Off Eligibility Determination

text

Rules evaluated in order:

1. IF write_off_status_cd = 'WRITTEN_OFF'
   → Not eligible

2. IF CAST(billing_item_detail_amt AS DECIMAL) < 100.00
   → Not eligible

3. IF days_outstanding >= 180
   → Eligible, recommended criteria = 'AGED'

4. All other cases
   → Eligible, no automatic criteria recommendation (manual review required)

Source: billing_item_detail.write_off_status_cd, billing_item_detail.billing_item_detail_amt, days outstanding (from formula 3.2)
Thresholds: AGED_DAYS = 180 days; MIN_AMOUNT_FOR_WRITEOFF = $100.00
Output: Boolean is_eligible, string recommended_criteria (null if no automatic recommendation)

3.4 Approval Routing Calculation

text

Inputs: current_status, total_commission_amt

State transition table:

SUBMITTED         → APPROVED_AGENT  (next approver: DEPT_HEAD)
APPROVED_AGENT    → APPROVED_DH     (next approver: VP_CLIENT_ACCT)
APPROVED_DH       → APPROVED_VP     (next approver: CFO if amt >= $50K, else COMPLETE)
APPROVED_VP       → COMPLETE        (if amt < $50K)
APPROVED_VP       → APPROVED_CFO    (if amt >= $50K; next approver: MD if amt > $250K, else COMPLETE)
APPROVED_CFO      → COMPLETE        (if amt <= $250K)
APPROVED_CFO      → APPROVED_MD     (if amt > $250K; next approver: MD)
APPROVED_MD       → COMPLETE        (always)
RESUBMITTED       → APPROVED_AGENT  (next approver: DEPT_HEAD)

Rejection mapping:
  AGENT role         → REJECTED_AGENT
  DEPT_HEAD role     → REJECTED_DH
  VP_CLIENT_ACCT     → REJECTED_VP
  CFO role           → REJECTED_CFO
  MD role            → REJECTED_MD

Source: write_off_packet.packet_status_cd, write_off_packet.total_commission_amt, write_off_packet.current_approver_role
Thresholds: vp_max_amount = $50,000; cfo_max_amount = $250,000
Output: next_status_cd, next_approver_role (null if packet reaches COMPLETE)

3.5 Write-Off Cash Receipt Amount Calculation

text

FOR EACH unique billing_item IN packet_receivables:
    rev_balance = billing_item_detail_amt(REV) - SUM(applied_amounts for REV detail)
    pay_balance = billing_item_detail_amt(PAY) - SUM(applied_amounts for PAY detail)
    item_total  = rev_balance + pay_balance

total_receipt_amt = SUM(item_total FOR ALL billing_items)

Source: billing_item_detail.billing_item_detail_amt, cash_receipt_application.cash_receipt_amt_applied
Target: cash_receipt.cash_receipt_amt (new receipt with receipt_type_cd = 'WRITE_OFF')
Precision: decimal(15,2)
Note: Both REV and PAY balances are included to fully zero-out the billing item. The receipt uses receipt_type_cd = 'WRITE_OFF' to distinguish it from regular cash receipts.

3.6 Submission Validation

text

All rules must pass before packet can be submitted:

1. COUNT(packet_receivable WHERE write_off_packet_id = :id) >= 1
   → Error if 0: NO_RECEIVABLES

2. All packet_receivable.billing_item_detail → billing_item.client_id
   must equal write_off_packet.client_id
   → Error if mismatch: CLIENT_MISMATCH / MULTIPLE_CLIENTS

3. All packet_receivable → billing_item_detail.billing_item_detail_type_cd = 'REV'
   → Error if any PAY: INVALID_TYPE

4. All packet_receivable.eligibility_criteria_cd IS NOT NULL
   → Error if any null: MISSING_ELIGIBILITY

5. Documentation check (currently disabled in PoC):
   Every receivable must have at least one document (packet-level or receivable-level)
   → Error if missing: MISSING_DOCUMENTATION / NO_PACKET_DOCUMENTATION

6. No packet_receivable in any other active packet (see 2.2.6)
   → Error if found: ALREADY_IN_PACKET

Warning condition (non-blocking):
   Multiple distinct eligibility_criteria_cd values across receivables
   → Warning: MIXED_ELIGIBILITY

Source: packet_receivable, billing_item_detail, billing_item, write_off_packet, packet_document
Output: is_valid boolean, array of errors (with error_cd and message), array of warnings

NOTE

PoC Artifact: Documentation validation (rule 5) is disabled in the PoC to simplify the demo workflow. Re-enable for production deployment.

4. Cross-References

Document	Relationship
Write-Offs Data Model	Full schema definitions, status lifecycle diagrams, validation rules, and code master values for `write_off_packet`, `packet_receivable`, `packet_status_history`, and `packet_document`.
Billing Items Data Model	`packet_receivable.billing_item_detail_id` → `billing_item_detail.billing_item_detail_id`. The write-off fields on `billing_item_detail` (`write_off_status_cd`, `write_off_packet_id`, `write_off_dt`, `recovered_dt`, `exclude_from_cecl_ind`) are owned by the Billing Items model and updated as a side-effect of packet completion and recovery. Write-off execution also sets `billing_item.open_item_ind = false`; recovery reverses this to `true`.
Cash Receipts Data Model	`write_off_packet.cash_receipt_id` → `cash_receipt.cash_receipt_id`. When a packet reaches `COMPLETE`, a `WRITE_OFF` type `cash_receipt` is created. Recovery creates a reversal `cash_receipt_worksheet` with negative application amounts and sets the original worksheet's `current_item_ind = false`.
Parties Data Model	`write_off_packet.client_id` → `party.party_id`. All receivable client validation compares against this value. `party.display_name` is joined in both packet search results and receivable enrichment queries. A second `party` alias (`buyer_party`) resolves `billing_item.buyer_id` in the receivables-by-packet query.
Billing Items Queries	The service-level eligible receivable search (2.2.5) delegates to the billing repository's general display query, which is documented in the Billing Items Queries document.

Sample Deals

Touring Deal

Endorsement Deal

Sports Endo Deal

Write-Offs Queries ​

1. Executive Summary ​

2. Key Queries ​

2.1 Packet Queries ​

2.1.1 Get Packet by ID ​

2.1.2 Get Packet with Client ​

2.1.3 Get Packet with Full Details ​

2.1.4 Search Packets ​

2.1.5 Get Packets by Status ​

Variant: Pending Approval by Role ​

2.1.6 Check Packet Name Uniqueness ​

2.1.7 Get Worksheet ID by Cash Receipt ID ​

2.2 Receivable Queries ​

2.2.1 Get Receivable by ID ​

2.2.2 Get Receivable with Details ​

2.2.3 Get Receivables by Packet ​

2.2.4 Search Eligible Receivables ​

2.2.5 Search Eligible Receivables (Service-Level Variant) ​

2.2.6 Check Receivable in Any Active Packet ​

2.2.7 Get Receivables by Client ​

2.2.8 Count Receivables in Packet ​

2.3 Status History Queries ​

2.3.1 Get History by Packet ​

2.3.2 Get History with User Details ​

2.3.3 Get Approval History ​

2.3.4 Get Latest History ​

2.3.5 Get History for Specific Status ​

2.3.6 Has Been Rejected ​

2.3.7 Count Status Changes ​

2.4 Document Queries ​

2.4.1 Get Document by ID ​

2.4.2 Get Document with Details ​

2.4.3 Get Packet-Level Documents ​

2.4.4 Get Receivable-Level Documents ​

2.4.5 Get All Documents for Packet ​

2.4.6 Count Packet-Level Documents ​

2.4.7 Count Receivable-Level Documents ​

2.5 Write-Off Execution Queries ​

2.5.1 Get Cash Receipt ID for Packet ​

2.5.2 Get Current Worksheet for Cash Receipt ​

2.5.3 Get Billing Item Display for Write-Off ​

3. Calculations & Formulas ​

3.1 Packet Aggregate Calculation ​

3.2 Days Outstanding Calculation ​

3.3 Write-Off Eligibility Determination ​

3.4 Approval Routing Calculation ​

3.5 Write-Off Cash Receipt Amount Calculation ​

3.6 Submission Validation ​

4. Cross-References ​

Write-Offs Queries

1. Executive Summary

2. Key Queries

2.1 Packet Queries

2.1.1 Get Packet by ID

2.1.2 Get Packet with Client

2.1.3 Get Packet with Full Details

2.1.4 Search Packets

2.1.5 Get Packets by Status

Variant: Pending Approval by Role

2.1.6 Check Packet Name Uniqueness

2.1.7 Get Worksheet ID by Cash Receipt ID

2.2 Receivable Queries

2.2.1 Get Receivable by ID

2.2.2 Get Receivable with Details

2.2.3 Get Receivables by Packet

2.2.4 Search Eligible Receivables

2.2.5 Search Eligible Receivables (Service-Level Variant)

2.2.6 Check Receivable in Any Active Packet

2.2.7 Get Receivables by Client

2.2.8 Count Receivables in Packet

2.3 Status History Queries

2.3.1 Get History by Packet

2.3.2 Get History with User Details

2.3.3 Get Approval History

2.3.4 Get Latest History

2.3.5 Get History for Specific Status

2.3.6 Has Been Rejected

2.3.7 Count Status Changes

2.4 Document Queries

2.4.1 Get Document by ID

2.4.2 Get Document with Details

2.4.3 Get Packet-Level Documents

2.4.4 Get Receivable-Level Documents

2.4.5 Get All Documents for Packet

2.4.6 Count Packet-Level Documents

2.4.7 Count Receivable-Level Documents

2.5 Write-Off Execution Queries

2.5.1 Get Cash Receipt ID for Packet

2.5.2 Get Current Worksheet for Cash Receipt

2.5.3 Get Billing Item Display for Write-Off

3. Calculations & Formulas

3.1 Packet Aggregate Calculation

3.2 Days Outstanding Calculation

3.3 Write-Off Eligibility Determination

3.4 Approval Routing Calculation

3.5 Write-Off Cash Receipt Amount Calculation

3.6 Submission Validation

4. Cross-References