---
title: Support order retrieval and maintenance
summary: Retrieves child-support withholding order header rows for a worker and creates, updates, or deletes a single order through inline SQL.
status: draft
owner: SAIF
last_reviewed: 2026-07-30
library: SAIF.Business.Claim.SupportOrder
reference_type: package
operation_id: OP-SO-01
trigger: Caller invokes clsClaimSupportOrder.NewClaimSupportOrder / clsClaimSupportOrderCollection.GetClaimSupportOrderCollection, edits header fields, and calls Save()
---

# Support order retrieval and maintenance

This operation owns the `CL_CLAIM_SUPPORT_ORDER` header row for a worker's child-support withholding order. `clsClaimSupportOrder` builds, edits, and persists a single order through inline CRUD SQL, while `clsClaimSupportOrderCollection` retrieves the set of orders for a worker (optionally filtered to all-claim orders) and cascades `Save()` across whatever new, dirty, or deleted items the caller placed in the collection. Unlike the parallel support-order implementation in `SAIF.Business.Claim.Maintenance`, no business rules are registered for either class, so no field-level validation runs — only the insert/update row-count check.

| Field | Value |
| --- | --- |
| **Operation ID** | OP-SO-01 |
| **Trigger** | Caller calls `clsClaimSupportOrder.NewClaimSupportOrder(dpi, workerID)` or `clsClaimSupportOrderCollection.GetClaimSupportOrderCollection(dpi, workerID[, allClaimFlag])`, edits the header fields (or marks the object deleted), and calls `Save()` |
| **Inputs** | `WorkerID`; caller-assigned `SupportOrderID`; header fields `AllClaimFlag`, `ArrearageFlag`, `DelinquentMonthlyAmt`, `EffDate`, `MaxReductionPercent`, `MonthlyAmt`, `ReceiveDate`, `SedAccountNum`, `SourceTxt`, `TermDate`; `UpdateNum` (concurrency token, read-only, populated on load); optional `AllClaimFlag` filter passed to the collection retrieve |
| **Outputs** | Persisted `CL_CLAIM_SUPPORT_ORDER` row; populated `clsClaimSupportOrder` / `clsClaimSupportOrderCollection` objects; `InsertError` / `UpdateError` messages on a rows-affected mismatch |
| **Primary components** | `clsClaimSupportOrder` (`Create`, `BeforeInsert`/`AfterInsert`, `BeforeUpdate`/`AfterUpdate`, `BeforeDelete`/`AfterDelete`, `NewClaimSupportOrder`), `clsClaimSupportOrder.Criteria` (`InsertSQL`, `UpdateSQL`, `DeleteSQL`), `clsClaimSupportOrderCollection` (`GetClaimSupportOrderCollection`, `BeforeRetrieve`/`AfterRetrieve`, `NewClaimSupportOrder`, `Save`, `Item`, `Contains`, `Remove`), `clsClaimSupportOrderCollection.Criteria` (`RetrieveSQL`, `RetrieveAllClaimFlagYSQL`) |
| **Entry point** | `Business\SAIF.Business.Claim.SupportOrder\clsClaimSupportOrder.vb` |

## Steps

1. **Create a new order.** `NewClaimSupportOrder(dataPortal, workerID)` builds a `Criteria` holding `WorkerID` and calls `DataAccessLayer.CreateObject`, which invokes `Create()`.
2. **Default the new order.** `Create()` blanks `AllClaimFlag`, `ArrearageFlag`, `SedAccountNum`, `SourceTxt`, and `UpdateUser`; zeroes `DelinquentMonthlyAmt`, `MaxReductionPercent`, `MonthlyAmt`, `SupportOrderID`, and `WorkerID`; sets `EffDate`/`ReceiveDate` to `Date.MinValue`; and sets `TermDate` and `UpdateDate` to `Now`.
   - No sequence lookup runs for `SupportOrderID` here — unlike the parallel implementation in `SAIF.Business.Claim.Maintenance`, which reserves an identifier from a database sequence before the caller edits the object — so the caller must assign a unique `SupportOrderID` explicitly before saving.*
3. **Caller edits the order.** Each writable property setter (`AllClaimFlag`, `ArrearageFlag`, `DelinquentMonthlyAmt`, `EffDate`, `MaxReductionPercent`, `MonthlyAmt`, `ReceiveDate`, `SedAccountNum`, `SourceTxt`, `SupportOrderID`, `TermDate`) compares the incoming value and calls `MarkDirty()` when it changes.
   - `AddBusinessRules()` registers no rules on either `clsClaimSupportOrder` or `clsClaimSupportOrderCollection`, so no date, amount, or flag validation runs before `Save()` — the only enforced check is the rows-affected concurrency check applied after each insert/update.*
   - `WorkerID`, `UpdateUser`, and `UpdateDate` are read-only properties; they are only populated by `Load()` when a row is retrieved from the database, not by a caller building a brand-new order.
4. **Save the order.** `Save()` is inherited unchanged from `clsBusinessBase` (not overridden in this class) and dispatches to insert, update, or delete based on the object's edit state.
5. **Insert a new order.** `BeforeInsert` binds every header field plus `WorkerID` and issues the insert ([SQL-001](../sql/sql-001-insert.md), [CL_CLAIM_SUPPORT_ORDER](../database-tables.md#cl-claim-support-order)).
   - `UpdateUser` is bound to whatever value the property currently holds. Because `Create()` sets it to an empty string and no public setter exists to populate it with the acting user (unlike `clsClaimSupportOrderConnection`, which does populate its `UpdateUser` from the data-portal info at create time — see [Support order connection management](support-order-connection-management.md)), a newly created order is inserted with a blank `UPDATE_USER` unless something else in the call chain sets the private field first.*
   - `AfterInsert`: when rows affected equals `1`, `markold()` marks the object clean; any other value adds an `InsertError` carrying the DB contention message ([BR-002](../business-rules.md#br-002-support-order-inserts-and-updates-must-affect-one-row)).
6. **Update an existing order.** `BeforeUpdate` binds the same header fields plus `UpdateNum` and issues the update. The statement's `WHERE` clause matches on both `WORKER_ID` and the caller's current `UPDATE_NUM` value and advances the stored value by one on success ([SQL-006](../sql/sql-006-update.md), [CL_CLAIM_SUPPORT_ORDER](../database-tables.md#cl-claim-support-order)) ([BR-001](../business-rules.md#br-001-support-order-updates-require-a-current-row-version)).
   - `AfterUpdate`: rows affected equal to `1` marks the object clean; any other value adds an `UpdateError` carrying the DB contention message ([BR-002](../business-rules.md#br-002-support-order-inserts-and-updates-must-affect-one-row)).
7. **Delete an order.** When the caller marks the object deleted and calls `Save()`, `BeforeDelete` binds only `WorkerID` and issues the delete. Unlike the update statement, the delete has no `UPDATE_NUM` predicate, so it cannot detect a stale concurrency token before removing the row ([SQL-007](../sql/sql-007-delete.md), [CL_CLAIM_SUPPORT_ORDER](../database-tables.md#cl-claim-support-order)).
   - `AfterDelete` exists but its entire body is commented out, so no rows-affected check runs and no error is ever raised after a delete, regardless of whether a row was actually removed.**
8. **Retrieve orders for a worker.** `GetClaimSupportOrderCollection(dataPortal, workerID)` builds a `Criteria` with only `WorkerID` set (leaving `AllClaimFlag` blank) and calls `DataAccessLayer.RetrieveObject`.
   - `GetClaimSupportOrderCollection(dataPortal, workerID, inFlag)` additionally sets the criteria's `AllClaimFlag` to `inFlag` before retrieving.
9. **Choose the retrieve statement.** `BeforeRetrieve` checks `MyCriteria.AllClaimFlag = "Y"`.
   - If true: run the all-claim-only statement ([SQL-004](../sql/sql-004-select.md), [CL_CLAIM_SUPPORT_ORDER](../database-tables.md#cl-claim-support-order)).
   - Otherwise: run the unfiltered per-worker statement ([SQL-003](../sql/sql-003-select.md), [CL_CLAIM_SUPPORT_ORDER](../database-tables.md#cl-claim-support-order)).
10. **Load each row.** `AfterRetrieve` reads the cursor and, for each row, constructs a new `clsClaimSupportOrder`, calls `Load()` — which fills the header fields, builds a fresh `Criteria(dataPortalInfo, WorkerID)`, and marks the object old — and adds it to the collection.
11. **Add a new order to a collection in memory.** `clsClaimSupportOrderCollection.NewClaimSupportOrder(inWorkerID)` calls the header factory from step 1 and adds the resulting new object to the collection's list, so it is included the next time the collection is saved.
12. **Save the collection.** `Save()` calls `ApplyEdit()` and then the inherited collection `Save()`, which persists every new, dirty, or deleted item in the list — each item routes through its own insert/update/delete logic from steps 5–7.
13. **Query the in-memory collection.** `Item(index)`, `Item(workerID)`, `Contains(...)`, `ContainsDeleted(...)`, and `Remove(...)` operate only on the in-memory list and deleted-list; they perform no database access.

## Process flow

The following diagram visualizes this operation's control flow.

```mermaid
flowchart TD
	Start([NewClaimSupportOrder / GetClaimSupportOrderCollection]) --> Which{Create or retrieve?}
	Which -->|Create| Defaults[Create defaults fields;<br>no sequence read]
	Defaults --> Edit[Caller edits header fields;<br>no rules registered]
	Edit --> Save[Save dispatches by edit state]
	Which -->|Retrieve| Flag{AllClaimFlag = Y?}
	Flag -->|Yes| SelY[Run all-claim-only select]
	Flag -->|No| SelAll[Run unfiltered per-worker select]
	SelY --> Load[AfterRetrieve loads each row]
	SelAll --> Load
	Save --> New{IsNew?}
	New -->|Yes| Ins[BeforeInsert / AfterInsert]
	New -->|No| Deleted{IsDeleted?}
	Deleted -->|Yes| Del[BeforeDelete;<br>AfterDelete is a no-op]
	Deleted -->|No| Upd[BeforeUpdate / AfterUpdate]
	Ins --> Rows{RowsAffected = 1?}
	Upd --> Rows2{RowsAffected = 1?}
	Rows -->|No| Contention([InsertError added;<br>object stays dirty])
	Rows -->|Yes| Clean([markold; save succeeded])
	Rows2 -->|No| Contention2([UpdateError added;<br>object stays dirty])
	Rows2 -->|Yes| Clean
	Del --> DelDone([Row deleted;<br>no contention check performed])
```

## Failure and edge-case behavior

- Neither `clsClaimSupportOrder` nor `clsClaimSupportOrderCollection` registers business rules, so amounts, dates, and flag values are never validated before a save; the only safeguard is the insert/update rows-affected check ([BR-002](../business-rules.md#br-002-support-order-inserts-and-updates-must-affect-one-row)).*
- The delete path performs no concurrency check (no `UPDATE_NUM` predicate) and `AfterDelete`'s body is entirely commented out, so a delete can silently remove zero or more than one row without the caller being notified.**
- `SupportOrderID` is never generated by this library; callers must supply a value themselves, unlike the `SAIF.Business.Claim.Maintenance` support-order object, which reserves an identifier from a sequence.*
- New header rows are inserted with a blank `UPDATE_USER` because `UpdateUser` has no public setter and is never populated with the acting user before `BeforeInsert` binds it.*
- The collection's `Save()` cascades to whichever items were added or removed from its in-memory list; it does not automatically save the per-order `Connections` collection (see [Support order connection management](support-order-connection-management.md)) — callers must save connections separately.

> **Note:** Statements marked with `*` were inferred from code structure, naming conventions, or
> contextual evidence where direct confirmation was not available in source artifacts. Statements
> marked with `**` could not be confirmed; treat as unknown until verified.

## Related documentation
- [Operations and processes](../operations-processes.md)
- [Business rules](../business-rules.md)
- [SQL catalog](../sql/index.md)
- [Database tables](../database-tables.md)

## Sources
- `Business\SAIF.Business.Claim.SupportOrder\clsClaimSupportOrder.vb`
- `Business\SAIF.Business.Claim.SupportOrder\clsClaimSupportOrderCollection.vb`