Skip to content

Audit condition maintenance

An audit condition records a data-quality edit finding (rate, source, ARD, exposure, or loss discrepancy) raised against a policy unit during Unit Stat processing. This operation covers clsAuditCondition's create/insert/retrieve lifecycle, clsAuditConditionCollection's multi-shape retrieval and error-flag evaluation, and the collection's shared bulk-delete functions used to clear conditions before a re-audit.

Field Value
Operation ID OP-US-04
Trigger clsAuditCondition.NewAuditCondition / GetAuditCondition, clsAuditConditionCollection.GetAuditConditions / GetPolicyPeriodAudits / GetPolicyUnitAudits / GetUpdateUserAudits, or any of the shared Delete* functions
Inputs Policy unit sequence number, exposure summary sequence number, claim ID, audit condition description, edit type code, error flag, policy number, policy period effective date, update user
Outputs Persisted US_AUDIT_CONDITION row(s); populated clsAuditCondition or clsAuditConditionCollection; HasPolicyUnitErrors/HasExposureErrors/HasLossErrors booleans; rows-deleted count from bulk deletes
Primary components clsAuditCondition.Create, clsAuditCondition.BeforeInsert/AfterInsert, clsAuditCondition.BeforeRetrieve/AfterRetrieve, clsAuditConditionCollection.BeforeRetrieve/AfterRetrieve, clsAuditConditionCollection.Delete* shared functions
Entry point Business\SAIF.Business.UnitStat\clsAuditCondition.vb, Business\SAIF.Business.UnitStat\clsAuditConditionCollection.vb

Steps

  1. Create. NewAuditCondition(dataPortal) invokes Create(), which allocates AuditConditionSeqNum from the US_AUDIT_CONDITION_SEQ sequence.
  2. Validate. Save() runs ValidAuditDescription, requiring AuditConditionDescription.Length <> 0.
  3. On failure: the broken-rule collection blocks the save.
  4. Insert. BeforeInsert stamps UpdateUser/UpdateDate and binds every column (SQL-001, US_AUDIT_CONDITION). AfterInsert checks rows affected:
  5. If RowsAffected <> 1: adds InsertError with the literal "Insert Failed" message.
  6. Otherwise: MarkOld().
  7. No update or delete path exists on the individual clsAuditCondition object; corrections are made by deleting and re-inserting via the collection's shared delete functions (step 7) followed by a fresh audit run.
  8. Retrieve single audit condition. GetAuditCondition(dataPortal, auditConditionSeqNum) selects by sequence number (SQL-005). AfterRetrieve fills the object when a row is found and always calls MarkOld().
  9. Retrieve audit-condition collection. Criteria.RetrieveSQL dispatches on RetrieveType:
  10. GetAuditConditions(dataPortal, policyNumber, policyPeriodEffectiveDate) — despite accepting a policy number and period, this factory constructs New Criteria(dataPortal) (the all-rows constructor), so the two arguments are silently ignored and every audit condition in the table is returned (SQL-006).*
  11. GetPolicyPeriodAudits(dataPortal, policyNumber, policyPeriodEffectiveDate) — the correct policy-period-scoped retrieval, filtering to policy units belonging to the given policy and period (SQL-007).
  12. GetPolicyUnitAudits(dataPortal, policyUnitSeqNum) — filters to a single policy unit (SQL-008); this is the retrieval used to lazily load a policy unit's AuditConditions child collection (see Policy unit CRUD).
  13. GetUpdateUserAudits(dataPortal, updateUser) — constructs a criteria with RetrieveType = RetrieveTypes.UpdateUser, but both Criteria.IsRetrieveByPolicyUnit and Criteria.IsRetrieveByUpdateUser test RetrieveType = RetrieveTypes.PolicyUnit, so neither branch (nor any other) in RetrieveSQL's dispatch matches an UpdateUser retrieve type; the call falls through to the Else branch and raises an ApplicationException* rather than returning results.
  14. Collection error-flag evaluation. HasPolicyUnitErrors, HasExposureErrors, and HasLossErrors are computed in memory over an already-loaded collection (no additional query): HasPolicyUnitErrors is true if any member has both ExposureSummarySeqNum and ClaimID populated (BR-US-024); HasExposureErrors is true if any member has ExposureSummarySeqNum populated; HasLossErrors is true if any member has ClaimID populated.
  15. Bulk delete. The shared functions on clsAuditConditionCollection build parameterized DELETE US_AUDIT_CONDITION statements from combinations of the WHERE-clause fragments and return the affected row count:
  16. DeletePolicyPeriodAudits(dataPortal, policyNumber, policyPeriodEffectiveDate) — deletes all conditions for policy units in the policy period, with no edit-type filter (SQL-031).
  17. DeletePolicyPeriodExposureAudits / one overload of DeletePolicyPeriodLossAudits route through DeletePolicyPeriodAuditsByEditType(dataPortal, policyNumber, policyPeriodEffectiveDate, editType) — deletes conditions for the policy period filtered to a specific edit type (Constants.EXPOSURE_AUDIT or Constants.CLAIM_AUDIT) (SQL-032).
  18. The other DeletePolicyPeriodLossAudits overload routes through DeletePolicyUnitAuditsByType(dataPortal, policyUnitSeqNum, editType) — deletes conditions for a single policy unit filtered to a specific edit type (SQL-033).
  19. DeleteAllAuditsByType(dataPortal, editTypeCode) — deletes every condition of a given edit type across all policy units, with no policy-period or policy-unit scoping (SQL-030).

Process flow

flowchart TD
    Start([Caller creates, retrieves,<br>or deletes audit conditions]) --> Which{Create, retrieve one,<br>retrieve collection,<br>or bulk delete?}
    Which -->|Create| Seq[Allocate AuditConditionSeqNum<br>from US_AUDIT_CONDITION_SEQ]
    Seq --> ValidateDesc{AuditConditionDescription<br>non-empty?}
    ValidateDesc -->|No| Blocked([Broken rule blocks save])
    ValidateDesc -->|Yes| Insert[Insert US_AUDIT_CONDITION row]
    Insert --> InsRows{RowsAffected = 1?}
    InsRows -->|No| InsErr([InsertError: Insert Failed])
    InsRows -->|Yes| MarkOldA([MarkOld])
    Which -->|Retrieve one| RetOne[Select by AuditConditionSeqNum]
    RetOne --> FillOne[Fill object if found;<br>MarkOld regardless]
    Which -->|Retrieve collection| RetKind{Which factory method?}
    RetKind -->|GetAuditConditions| RetAllBug([Returns ALL rows;<br>policy number/period args ignored])
    RetKind -->|GetPolicyPeriodAudits| RetPeriod[Select by policy period]
    RetKind -->|GetPolicyUnitAudits| RetUnit[Select by policy unit]
    RetKind -->|GetUpdateUserAudits| RetUserBug([Dispatch falls through:<br>ApplicationException raised])
    Which -->|Bulk delete| DelKind{Which shared function?}
    DelKind -->|DeletePolicyPeriodAudits| DelPeriod[Delete by policy period]
    DelKind -->|DeletePolicyPeriodAuditsByEditType| DelPeriodType[Delete by policy period<br>and edit type]
    DelKind -->|DeletePolicyUnitAuditsByType| DelUnitType[Delete by policy unit<br>and edit type]
    DelKind -->|DeleteAllAuditsByType| DelAllType[Delete by edit type<br>across all policy units]

Failure and edge-case behavior

  • GetAuditConditions(dataPortal, policyNumber, policyPeriodEffectiveDate) silently ignores both of its parameters and retrieves every US_AUDIT_CONDITION row in the table, because its implementation constructs New Criteria(dataPortal) (the all-rows constructor) instead of the policy-period constructor used by GetPolicyPeriodAudits; callers of this specific overload should use GetPolicyPeriodAudits instead to get policy-period-scoped results.*
  • GetUpdateUserAudits raises an ApplicationException (InvalidSQLProperty) at retrieve time rather than returning update-user-scoped results, because Criteria.IsRetrieveByPolicyUnit and Criteria.IsRetrieveByUpdateUser both compare RetrieveType against RetrieveTypes.PolicyUnit, so a RetrieveTypes.UpdateUser criteria never matches any dispatch branch in RetrieveSQL.*
  • There is no object-level update or delete on clsAuditCondition; corrections are performed exclusively through the collection's bulk Delete* shared functions followed by re-insertion of corrected conditions.
  • Insert failures use the literal "Insert Failed" message rather than the shared DBContentionError message used elsewhere in the library for update/delete contention.
  • The three Has*Errors properties operate only on whatever audit conditions are already loaded into the collection instance; they do not re-query the database, so a stale in-memory collection can yield stale error results.

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\clsAuditCondition.vb
  • Business\SAIF.Business.UnitStat\clsAuditConditionCollection.vb