SQL-024 Retrieve case status codes by prior status

Retrieves CASE_STATUS_CODE rows matching one or two prior status codes taken from the case's status history, for the clsUpdateCaseManager.CaseStatusList property used by the update-case status maintenance UI. The executable SQL is assembled at runtime from two constants — CASESTATUSCODESQL (opening clause, ending at WHERE code in (') and ORDERSQL (closing clause, ')) — with one or two status codes concatenated between them.

Classification

Field	Value
SQL ID	SQL-024
Operation type	Read
Execution method	Inline SQL, runtime string concatenation (clsCodeCollection.GetCodeCollection)
Database	Oracle
Command type	Text
Business object	N/A (Application layer)
Criteria property	N/A (Application layer)
Lifecycle method	N/A (Application layer)
Application class	clsUpdateCaseManager

Parameters

Parameter	Direction	Type	Max length	Description
CurrentCase.StatusHistory(0).StatusCode, CurrentCase.StatusHistory(1).StatusCode	N/A — concatenated literals, not bind parameters*	String	N/A	One or two prior case-status codes concatenated directly into the IN list text of CASESTATUSCODESQL (see Sample SQL below) rather than bound as Oracle parameters*.

Tables

Table	Access	Columns used
CASE_STATUS_CODE	SELECT	CODE, CODE_DESCR

Sample SQL

SELECT code, code_descr
FROM CASE_STATUS_CODE
WHERE code in ('<StatusCode0>', '<StatusCode1>')

The constant CASESTATUSCODESQL ends at WHERE code in (', and the constant ORDERSQL supplies the closing '). At runtime, clsUpdateCaseManager.CaseStatusList concatenates one or two CurrentCase.StatusHistory(n).StatusCode values between them (CASESTATUSCODESQL + StatusCode0 + "', '" + StatusCode1 + ORDERSQL, or CASESTATUSCODESQL + StatusCode0 + ORDERSQL when only one prior status exists) — the reconstructed executable text above reflects the two-status case; values are concatenated directly into the SQL text, not bound as Oracle parameters*.

Business rules

• BR-LGL-009: Worker-attorney firm change creates a system audit case note*
• BR-LGL-010: Worker attorney change creates a system audit case note*
• BR-LGL-012: Secondary consolidated cases mirror the primary case state*

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

• SAIF.Application.Claim.Legal\LegalNet\clsUpdateCaseManager.vb