SQL catalog

This library's Stage 1b database analysis cites 113 individual SQL findings (DB-CLL-001 through DB-CLL-113) plus one consolidated code-table lookup group (DB-CLL-CT-001, ~30 variants). This catalog individually documents every distinctly-cited SQL finding from the Stage 1b database analysis (TSK1B-002), consolidating two large repeating groups to keep the set navigable: the ~30 code-table lookup variants in mdlCodeTables.vb are documented as a single consolidated entry (DB-CLL-CT-001), and the ~40 standard CRUD statements across 8 TableMaint reference-data objects are documented as a single consolidated entry (DB-CLL-065-104). Objects whose IBusinessCriteria properties throw ApplicationException (no SQL) are noted below and are not itemized as individual SQL documents.

This catalog documents 65 SQL pages in total, covering all distinctly-cited findings.

C

SQL ID	Business object	Description	Page
DB-CLL-009	clsAppeal	Generate the next appeal sequence value	DB-CLL-009

D

SQL ID	Business object	Description	Page
DB-CLL-004	clsCaseCost	Delete a case cost record	DB-CLL-004
DB-CLL-012	clsAppeal	Delete an appeal record	DB-CLL-012
DB-CLL-019	clsCaseNote	Delete a case note	DB-CLL-019
DB-CLL-023	clsCaseOutcome	Delete a case outcome	DB-CLL-023
DB-CLL-039	clsLegalFileLocation	Delete a legal file location record	DB-CLL-039
DB-CLL-043	clsLegalSchedule	Delete a legal schedule entry	DB-CLL-043
DB-CLL-049	clsLegalCase	Delete a legal case header record	DB-CLL-049

I

SQL ID	Business object	Description	Page
DB-CLL-002	clsCaseCost	Insert a new case cost record	DB-CLL-002
DB-CLL-010	clsAppeal	Insert a new appeal record	DB-CLL-010
DB-CLL-014	clsAppealExtension	Insert an appeal extension record	DB-CLL-014
DB-CLL-017	clsCaseNote	Insert a new case note	DB-CLL-017
DB-CLL-021	clsCaseOutcome	Insert a new case outcome	DB-CLL-021
DB-CLL-034	clsLegalCaseEvaluation	Insert a legal case evaluation	DB-CLL-034
DB-CLL-037	clsLegalFileLocation	Insert a legal file location record	DB-CLL-037
DB-CLL-041	clsLegalSchedule	Insert a legal schedule entry	DB-CLL-041
DB-CLL-047	clsLegalCase	Insert a new legal case header record	DB-CLL-047

Misc

SQL ID	Business object	Description	Page
DB-CLL-045	clsLegalScheduleCollection	Retrieve the full schedule list for a legal case	DB-CLL-045
DB-CLL-050	clsLegalCase.UpdateHearingSeq	Update the hearing sequence number on a case	DB-CLL-050
DB-CLL-052	clsCreateFile.UpdateCreateFile	Stamp the file-create date on a legal case	DB-CLL-052
DB-CLL-054	clsMailDate	Set the mail OAR flag on a legal case	DB-CLL-054
DB-CLL-055	clsMailDate (list-level)	Batch-set the mail date across legal cases	DB-CLL-055
DB-CLL-056	clsConsolidations	Update a case consolidation link	DB-CLL-056
DB-CLL-057	clsConsolidations	Cascade a case status update during consolidation	DB-CLL-057
DB-CLL-058	clsLetterRequest	Look up a case number for a printed letter	DB-CLL-058
DB-CLL-059	clsAssignmentProcess.GetMaxSeq	Fetch the maximum assignment result sequence	DB-CLL-059
DB-CLL-060	clsQuickCal	Trigger an Oracle-side calendar review	DB-CLL-060

R

SQL ID	Business object	Description	Page
DB-CLL-001	clsCaseCost	Retrieve a single case cost by ID	DB-CLL-001
DB-CLL-006	clsCaseCostCollection	Retrieve case cost list for a legal case	DB-CLL-006
DB-CLL-007	clsCaseStatusHistoryCollection	Retrieve case status history, newest first	DB-CLL-007
DB-CLL-013	clsAppealCollection	Retrieve appeals for a legal case	DB-CLL-013
DB-CLL-016	clsCaseNote	Retrieve case notes for a legal case	DB-CLL-016
DB-CLL-020	clsCaseOutcomeCollection	Retrieve case outcomes for a legal case	DB-CLL-020
DB-CLL-024	clsOutcomeIssue	Retrieve a single outcome issue	DB-CLL-024
DB-CLL-025	clsOutcomeIssue collection ctor	Retrieve the outcome issue list for an outcome	DB-CLL-025
DB-CLL-026	clsOutcomeStaff single	Retrieve a single outcome staff assignment	DB-CLL-026
DB-CLL-027	clsOutcomeStaff collection ctor	Retrieve the outcome staff list for an outcome	DB-CLL-027
DB-CLL-028	clsRequestIssue	Retrieve a single request issue	DB-CLL-028
DB-CLL-029	clsRequestIssue collection ctor	Retrieve the request issue list for a case	DB-CLL-029
DB-CLL-030	clsOtherLegalParty	Retrieve a single other-legal-party record	DB-CLL-030
DB-CLL-031	clsOtherLegalParty collection ctor	Retrieve the other-legal-party list for a case	DB-CLL-031
DB-CLL-032	clsHearingLocationInfo	Retrieve the full hearing-location lookup list	DB-CLL-032
DB-CLL-033	clsLegalCaseEvaluation	Retrieve legal case evaluations	DB-CLL-033
DB-CLL-036	clsLegalFileLocation	Retrieve file locations for a legal case	DB-CLL-036
DB-CLL-040	clsLegalSchedule	Retrieve legal schedule entries for a case	DB-CLL-040
DB-CLL-044	clsLegalSchedule	Resolve a secretary/ALJ display name for a schedule entry	DB-CLL-044
DB-CLL-046	clsLegalCase	Retrieve a legal case header record	DB-CLL-046
DB-CLL-051	clsLegalCaseCollection	Retrieve the full legal case list	DB-CLL-051
DB-CLL-053	clsCreateFile.GetNextDocSeqNum	Generate the next print-document sequence number	DB-CLL-053
DB-CLL-061	clsLegalPerson.CanDeleteLegalPerson	Check referential-integrity blockers before delete	DB-CLL-061
DB-CLL-062	clsHearingLocation.CanDeleteLocation	Check whether a hearing location has active schedule rows	DB-CLL-062
DB-CLL-063	clsHearingLocation.ValidateState	Validate a state code (non-parameterized — flagged)	DB-CLL-063
DB-CLL-064	clsOtherFirm.ValidateState	Validate a state code (non-parameterized — flagged)	DB-CLL-064
DB-CLL-CT-001	mdlCodeTables	Retrieve a code-table lookup list for dropdowns	DB-CLL-CT-001

R,I,U,D

SQL ID	Business object	Description	Page
DB-CLL-065-104	clsAttorneyConflict, clsAttorneyWorkerAssignment (+ clsAttorneyPolicyAssignment, clsAttorneySchedule), clsLegalPersonType, clsOtherFirm, clsOutOFTownRotationAssgn, clsHearingLocation	Standard reference-data CRUD across 8 TableMaint objects	DB-CLL-065-104

U

SQL ID	Business object	Description	Page
DB-CLL-003	clsCaseCost	Update case cost amounts and details	DB-CLL-003
DB-CLL-008	clsAcknowledge	Update an acknowledgement record for a case	DB-CLL-008
DB-CLL-011	clsAppeal	Update an existing appeal record	DB-CLL-011
DB-CLL-015	clsAppealExtension	Update an appeal extension record	DB-CLL-015
DB-CLL-018	clsCaseNote	Update an existing case note	DB-CLL-018
DB-CLL-022	clsCaseOutcome	Update an existing case outcome	DB-CLL-022
DB-CLL-035	clsLegalCaseEvaluation	Update a legal case evaluation	DB-CLL-035
DB-CLL-038	clsLegalFileLocation	Update a legal file location record	DB-CLL-038
DB-CLL-042	clsLegalSchedule	Update a legal schedule entry	DB-CLL-042
DB-CLL-048	clsLegalCase	Update a legal case header record	DB-CLL-048

Objects with no direct SQL

The following business objects' IBusinessCriteria implementations intentionally throw ApplicationException for all CRUD members (retrieve-only/API-backed/DTO objects) or otherwise issue no direct SQL, per TSK1B-002 §2 (findings DB-CLL-105 through DB-CLL-113). They are not itemized as individual SQL pages because there is no SQL text to render:

• clsAssignmentResults, clsAssignmentDetail, clsAssignmentReport (LegalNet\clsAssignmentResult.vb)
• clsBoardLog (LegalNet\clsBoardLog.vb)
• clsAttorneyMessages (LegalNet\clsAttorneyMessages.vb)
• clsCaseSearchCriteria, clsCaseSearchResult (LegalNet\clsFindCase.vb family — search delegates to LegalApi)
• Print DTO classes under Print\ (Print\*.vb — populated from LegalApi responses, no direct SQL)

Related documentation

• Database tables
• Business rules
• Operations and processes
• Stored procedures
• Architecture

Sources

• SAIF.Business.Claim.Legal\LegalNet\ (all .vb files)
• SAIF.Business.Claim.Legal\TableMaint\ (all .vb files)
• SAIF.Business.Claim.Legal\mdlCodeTables.vb