---
title: Audit condition history tracking
summary: Creates, retrieves, and updates historical audit-condition records used to track when a given policy unit/claim/description audit condition was first raised and last seen, plus the collection-level retrieval of all history rows for a policy unit and claim.
status: draft
owner: SAIF
last_reviewed: 2026-07-30
library: SAIF.Business.UnitStat
reference_type: package
operation_id: OP-US-05
trigger: Caller creates a new audit-condition history record, retrieves one or a collection by policy unit/claim/description, or updates an existing record's last-update date
---

# Audit condition history tracking

`clsAuditConditionHistory` preserves a longer-lived record of an audit condition than the working `US_AUDIT_CONDITION` table, keyed by policy unit sequence number, claim ID, and the audit condition description text, tracking when it was first created and last updated. This operation covers the object's create/insert/retrieve/update lifecycle and the collection's retrieval of all history rows for a given policy unit and claim.

| Field | Value |
| --- | --- |
| **Operation ID** | OP-US-05 |
| **Trigger** | `clsAuditConditionHistory.NewAuditConditionHistory` / `GetAuditConditionHistory`, followed by property edits and `Save()`, or `clsAuditConditionHistoryCollection.GetAuditConditionHistoryByPolicyUnitClaimId` |
| **Inputs** | Policy unit sequence number, claim ID, audit condition description, edit type code, error flag |
| **Outputs** | Persisted `US_AUDIT_CONDITION_HISTORY` row; populated `clsAuditConditionHistory` or `clsAuditConditionHistoryCollection` |
| **Primary components** | `clsAuditConditionHistory.BeforeInsert/AfterInsert`, `clsAuditConditionHistory.BeforeRetrieve/AfterRetrieve`, `clsAuditConditionHistory.BeforeUpdate/AfterUpdate`, `clsAuditConditionHistoryCollection.BeforeRetrieve/AfterRetrieve` |
| **Entry point** | `Business\SAIF.Business.UnitStat\clsAuditConditionHistory.vb`, `Business\SAIF.Business.UnitStat\clsAuditConditionHistoryCollection.vb` |

## Steps

1. **Create.** `NewAuditConditionHistory(dataPortal)` creates a blank object (there is no custom `Create()` override, so no key value is pre-populated); the caller is responsible for setting `PolicyUnitSeqNum`, `ClaimID`, and `AuditConditionDescription` before save.
2. **Validate.** `Save()` runs `ValidAuditDescription`, requiring `AuditConditionDescription.Length <> 0`.
   - On failure: the broken-rule collection blocks the save.
3. **Insert.** `BeforeInsert` binds all columns, including `CreateDate` and `LastUpdateDate` from whatever values the caller has already set on the object (there is no automatic stamping to "now" in `BeforeInsert`, unlike other insert paths in this library) ([SQL-002](../sql/SQL-002-us-audit-condition-history-insert.md), [US_AUDIT_CONDITION_HISTORY](../database-tables.md#us-audit-condition-history)). `AfterInsert` checks rows affected:
   - If `RowsAffected <> 1`: adds `InsertError` with the literal "Insert Failed" message.
   - Otherwise: `MarkOld()`.
4. **Retrieve single history record.** `GetAuditConditionHistory(dataPortal, policyUnitSeqNum, claimId, auditMessage)` selects by the composite key (policy unit sequence number, claim ID, audit condition description) ([SQL-010](../sql/SQL-010-us-audit-condition-history-by-composite-key.md)). `AfterRetrieve` fills the object when a row is found and always calls `MarkOld()`.
5. **Retrieve history collection.** `clsAuditConditionHistoryCollection.GetAuditConditionHistoryByPolicyUnitClaimId(dataPortal, policyUnitSeqNum, claimId, message)` selects by the same composite key, returning every matching row ([SQL-011](../sql/SQL-011-us-audit-condition-history-collection-by-composite-key.md)).
6. **Update (object level).** `BeforeUpdate` sets the in-memory `LastUpdateDate` property to `Today`, but the bound SQL statement itself sets `LAST_UPDATE_DATE = trim(sysdate)` (a server-side expression) rather than binding a `:LastUpdateDate` parameter ([SQL-026](../sql/SQL-026-us-audit-condition-history-update-last-update-date.md), [US_AUDIT_CONDITION_HISTORY](../database-tables.md#us-audit-condition-history)) — so the client-side `LastUpdateDate = Today` assignment has no effect on what is actually written; the database's own clock determines the persisted value.* `AfterUpdate` checks rows affected:
   - If `RowsAffected <> 1`: adds `UpdateError` with `clsConstants.Messages.DBContentionError` ([BR-US-020](../business-rules.md#br-us-020-database-contention-on-update-or-delete-is-detected-by-checking-rowsaffected-1)).
   - Otherwise: `MarkOld()`.
7. **Update (collection level, unused).** `clsAuditConditionHistoryCollection.Criteria` separately defines an `UpdateSQL` that binds a `:LastUpdateDate` parameter instead of using `trim(sysdate)` ([SQL-027](../sql/SQL-027-us-audit-condition-history-collection-update.md)), but the collection class has no `BeforeUpdate`/`AfterUpdate` override that references it, so this alternate statement is never actually executed by any code path in this library.** It is dead code as far as the documented public surface is concerned.
8. **No orchestrating check-then-insert-or-update method.** No method inside this library first attempts a retrieve and conditionally inserts or updates a history record; callers must perform that decision themselves (for example, retrieving via `GetAuditConditionHistory` and checking whether the result is new/blank before deciding to insert or update).*

## Process flow

```mermaid
flowchart TD
	Start([Caller creates, retrieves,<br>or updates audit-condition history]) --> Which{Create, retrieve one,<br>retrieve collection,<br>or update?}
	Which -->|Create| Blank[Create blank object;<br>caller sets composite key]
	Blank --> ValidateDesc{AuditConditionDescription<br>non-empty?}
	ValidateDesc -->|No| Blocked([Broken rule blocks save])
	ValidateDesc -->|Yes| Insert[Insert US_AUDIT_CONDITION_HISTORY<br>row using caller-set dates]
	Insert --> InsRows{RowsAffected = 1?}
	InsRows -->|No| InsErr([InsertError: Insert Failed])
	InsRows -->|Yes| MarkOldA([MarkOld])
	Which -->|Retrieve one| RetOne[Select by PolicyUnitSeqNum,<br>ClaimID, AuditConditionDescription]
	RetOne --> FillOne[Fill object if found;<br>MarkOld regardless]
	Which -->|Retrieve collection| RetAll[Select all rows by<br>PolicyUnitSeqNum, ClaimID,<br>AuditConditionDescription]
	Which -->|Update| SetToday[Set LastUpdateDate = Today<br>on the in-memory object]
	SetToday --> DBWrite[SQL sets LAST_UPDATE_DATE<br>= trim sysdate, ignoring<br>the client-side value]
	DBWrite --> UpdRows{RowsAffected = 1?}
	UpdRows -->|No| Contention([UpdateError: DB contention])
	UpdRows -->|Yes| MarkOldB([MarkOld])
```

## Failure and edge-case behavior

- The object's `BeforeInsert` does not stamp `CreateDate`/`LastUpdateDate` automatically; both must already be set on the object by the caller before `Save()`, unlike the automatic `UpdateUser`/`UpdateDate` stamping seen elsewhere in the library.*
- The object-level update writes `LAST_UPDATE_DATE = trim(sysdate)` server-side; setting `LastUpdateDate` on the .NET object before calling `Save()` has no observable effect on the persisted value, since that property is never bound as a SQL parameter in the update statement.*
- The collection-level `UpdateSQL` (bound `:LastUpdateDate` parameter) is defined but never invoked by any `BeforeUpdate`/`AfterUpdate` override in `clsAuditConditionHistoryCollection`, making it unreachable dead code as currently written.**
- No method in this library encapsulates "retrieve, then insert if missing or update if present" logic for a history record; that orchestration, if it exists, lives outside this library's traced source.**

## Related documentation

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

> **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.

## Sources

- `Business\SAIF.Business.UnitStat\clsAuditConditionHistory.vb`
- `Business\SAIF.Business.UnitStat\clsAuditConditionHistoryCollection.vb`