SQL-007: Retrieve pending premium audits for a processing window

This is the queue read that starts every premium audit batch run. The scheduled job resolves its processing window from configuration, then calls dbo.usp_GetPendingAudits to obtain the set of audits whose due date falls in that window and whose status has not yet advanced past Pending. The optional @PolicyNumber argument supports the operator-initiated single-policy rerun described in the operational runbook; when it is omitted the procedure returns the full window. Rows are returned ordered by due date so that the oldest backlog is worked first, and the procedure applies its own NOLOCK-free read because the queue table is written concurrently by the policy system.

Classification

Field	Value
SQL ID	SQL-007
Operation type	Read
Execution method	Stored procedure
Database	SQL Server
Schema	dbo
Command type	StoredProcedure
Business object	N/A (Application layer)
Criteria property	N/A
Lifecycle method	N/A
Application class	PendingAuditRepository

Parameters

Parameter	Direction	Type	Max length	Description
@AuditPeriodStart	Input	DateTime	N/A	Inclusive start of the processing window; audits with an earlier due date are excluded.
@AuditPeriodEnd	Input	DateTime	N/A	Inclusive end of the processing window; supplied as the run date plus the configured look-ahead days.
@PolicyNumber	Input	VarChar	12	Optional single-policy filter for operator-initiated reruns. Pass NULL to process the whole window.
@ReturnValue	ReturnValue	Integer	N/A	Count of queue rows returned; 0 indicates an empty window and causes the batch to exit with return code 3.

Tables

Table	Access	Columns used
PremiumAuditQueue	SELECT	AuditQueueId, PolicyNumber, AuditDueDate, AuditStatusCode, AttemptCount, LockedByProcess
Policy	JOIN	PolicyNumber, PolicyEffectiveDate, PolicyExpirationDate, ServicingBranchCode
AuditStatus	JOIN	AuditStatusCode, AuditStatusName, IsTerminal

Sample SQL

-- Stored procedure call issued by PendingAuditRepository.GetPendingAudits
EXEC dbo.usp_GetPendingAudits
  @AuditPeriodStart = @AuditPeriodStart,
  @AuditPeriodEnd = @AuditPeriodEnd,
  @PolicyNumber = @PolicyNumber
-- Procedure body resides in the SQL Server database and is version-controlled
-- in db/Procedures/usp_GetPendingAudits.sql

-- Procedure body, from db/Procedures/usp_GetPendingAudits.sql
SELECT
  q.AuditQueueId,
  q.PolicyNumber,
  q.AuditDueDate,
  q.AuditStatusCode,
  q.AttemptCount,
  p.PolicyEffectiveDate,
  p.PolicyExpirationDate,
  p.ServicingBranchCode,
  s.AuditStatusName
FROM dbo.PremiumAuditQueue AS q
  INNER JOIN dbo.Policy AS p
    ON p.PolicyNumber = q.PolicyNumber
  INNER JOIN dbo.AuditStatus AS s
    ON s.AuditStatusCode = q.AuditStatusCode
WHERE q.AuditDueDate >= @AuditPeriodStart
  AND q.AuditDueDate <= @AuditPeriodEnd
  AND s.IsTerminal = 0
  AND q.LockedByProcess IS NULL
  AND (@PolicyNumber IS NULL OR q.PolicyNumber = @PolicyNumber)
ORDER BY
  q.AuditDueDate ASC,
  q.AuditQueueId ASC

Operations and processes

• Load pending audit queue
• Rerun single policy audit

Business rules

• BR-AUDIT-004 Audits are processed oldest due date first
• BR-AUDIT-011 Terminal audit statuses are never reprocessed
• BR-AUDIT-012 Queue rows locked by another process are skipped

Sources

• src/SAIF.PremiumAudit.Batch/Data/PendingAuditRepository.vb
• src/SAIF.PremiumAudit.Batch/Data/SqlCommandFactory.vb
• db/Procedures/usp_GetPendingAudits.sql