SQL-019 Retrieve appeal hearing level list by case

Retrieves APPEAL rows for the current legal case, left-outer-joined to REVIEW_LEVEL_CODE for the description, unioned with a leading "Hearings Level" placeholder row from DUAL, for the APEALNUMLEVELSQL code-table lookup used by the case outcome/appeal maintenance UI.

Classification

Field	Value
SQL ID	SQL-019
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	clsOutcomeManager

Parameters

Parameter	Direction	Type	Max length	Description
CurrentCaseID	N/A — concatenated literal, not a bind parameter*	Decimal (.ToString)	N/A	Legal case ID appended directly onto the APEALNUMLEVELSQL string via + concatenation rather than an Oracle bind variable — no SQL-injection risk in practice since the value is a Decimal property, but it deviates from the bind-variable pattern used elsewhere in this library*.

Tables

Table	Access	Columns used
APPEAL	SELECT, JOIN	APPEAL_SEQ_NUM, APPEAL_NUM, LEGAL_CASE_SEQ_NUM, REVIEW_LEVEL_CODE
REVIEW_LEVEL_CODE	SELECT, JOIN (LEFT OUTER)	CODE, CODE_DESCR
DUAL	SELECT	N/A (pseudo-table; placeholder blank row)

Sample SQL

Select '0', 'Hearings Level'
from dual
union
Select
  to_char(APPEAL.APPEAL_SEQ_NUM) as code,
  CODE_DESCR || decode(appeal.appeal_num, null, '', '-' || APPEAL.APPEAL_NUM) as description
From
  APPEAL LEFT OUTER JOIN REVIEW_LEVEL_CODE On REVIEW_LEVEL_CODE = CODE
Where
  APPEAL.LEGAL_CASE_SEQ_NUM = <CurrentCaseID>

<CurrentCaseID> denotes runtime .ToString-concatenation of the CurrentCaseID decimal property directly onto the constant string (APEALNUMLEVELSQL + CurrentCaseID.ToString) rather than an Oracle bind variable*.

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\clsOutcomeManager.vb