Database tables

This library references 32 distinct tables/views (per TSK1B-002 §1), spanning core legal-case tables, TableMaint reference-data tables, and a small number of cross-schema views. In addition, mdlCodeTables.vb reads from ~21 additional code-table lookup tables (e.g. CASE_STATUS_CODE, HEARING_LOCATION_CODE, SCHEDULE_TYPE_CODE) consolidated below in § Code-table lookup pattern rather than itemized individually, per TSK1B-002 §1.1.

Entity relationship diagram

erDiagram
    LEGAL_CASE ||--o{ CASE_COST : "has"
    LEGAL_CASE ||--o{ CASE_STATUS_HISTORY : "has"
    LEGAL_CASE ||--o{ APPEAL : "has"
    APPEAL ||--o{ APPEAL_EXTENSION : "extended by"
    LEGAL_CASE ||--o{ CASE_NOTE : "has"
    LEGAL_CASE ||--o{ CASE_OUTCOME : "has"
    CASE_OUTCOME ||--o{ OUTCOME_ISSUE : "has"
    CASE_OUTCOME ||--o{ OUTCOME_STAFF : "has"
    LEGAL_CASE ||--o{ REQUEST_ISSUE : "has"
    LEGAL_CASE ||--o{ LEGAL_CASE_EMPLOYER : "has"
    LEGAL_CASE ||--o{ LEGAL_CASE_EVALUATION : "has"
    LEGAL_CASE ||--o{ LEGAL_FILE_LOCATION : "has"
    LEGAL_CASE ||--o{ LEGAL_SCHEDULE : "has"
    LEGAL_CASE ||--o{ CONSOLIDATED_CASE : "consolidates"
    HEARING_LOCATION ||--o{ SCHEDULE : "hosts"
    HEARING_LOCATION ||--o{ ALL_STATES : "validates state via"
    ATTORNEY_WORKER_ASSIGNMENT ||--o{ ATTORNEY_POLICY_ASSIGNMENT : "includes"
    ATTORNEY_WORKER_ASSIGNMENT ||--o{ ATTORNEY_SCHEDULE : "includes"
    LEGAL_PERSON ||--o{ ATTORNEY_CONFLICT : "checked against"
    LEGAL_PERSON ||--o{ LEGAL_PERSON_TYPE : "typed by"
    OTHER_FIRM ||--o{ OUT_OF_TOWN_ROTATION_ASSGN : "rotation source"

This diagram reflects observed foreign-key-like usage across business objects in this library; it is not a verified database schema export.

Table summary

Table / View	Schema	CRUD (this library)	Business objects
CASE_COST	(default/APP)	R, I, U, D	clsCaseCost, clsCaseCostCollection
CASE_STATUS_HISTORY	(default/APP)	R (collection only; no I/U/D Criteria found — likely proc-driven)	clsCaseStatusHistoryCollection
ATTORNEY_CONFLICT	(default/APP)	R, I, U, D	clsAttorneyConflict
ATTORNEY_POLICY_ASSIGNMENT	(default/APP)	R, I, U, D	clsAttorneyWorkerAssignment (assignment-adjacent)
ATTORNEY_SCHEDULE	(default/APP)	R, I, U, D	clsAttorneyWorkerAssignment-family
ATTORNEY_WORKER_ASSIGNMENT	(default/APP)	R, I, U, D	clsAttorneyWorkerAssignment, clsOutOFTownRotationAssgn
HEARING_LOCATION	(default/APP)	R, I, U, D	clsHearingLocationInfo (LegalNet, read-only), clsHearingLocation (TableMaint, full CRUD)
LEGAL_PERSON	(default/APP)	R, I, U, D	clsLegalPerson, clsLegalPersonList
LEGAL_PERSON_TYPE	(default/APP)	R, I, U, D	clsLegalPersonType
OTHER_FIRM	(default/APP)	R, I, U, D	clsOtherFirm
OUT_OF_TOWN_ROTATION_ASSGN	(default/APP)	R, I, U, D	clsOutOFTownRotationAssgn
OUTCOME_ISSUE	(default/APP)	R (Criteria + collection)	clsOutcomeIssue
OUTCOME_STAFF	(default/APP)	R (Criteria + collection)	clsOutcomeStaff
REQUEST_ISSUE	(default/APP)	R (Criteria + collection)	clsRequestIssue
LEGAL_CASE_EMPLOYER	(default/APP)	R (Criteria + collection)	clsOtherLegalParty
legal.attorney_profile	LEGAL (schema-qualified)	R	clsAttorneyProfile-style lookup
LEGAL.vw_cases	LEGAL (view)	R (scalar)	clsLetterRequest (ad-hoc case-number lookup)
SCHEDULE	(default/APP)	R (subquery only, inside CanDeleteLocation)	clsHearingLocation
ALL_STATES	(default/APP, reference)	R (scalar, string-concatenated — flagged)	clsHearingLocation.ValidateState, clsOtherFirm.ValidateState
ADP_EMPLOYEE	(default/APP)	R (joined from code-table lookups)	mdlCodeTables code-table classes
xprint_control_seq (sequence object)	(default/APP)	sequence .NEXTVAL only	clsCreateFile.GetNextDocSeqNum
ACKNOWLEDGE (or similarly named)	(default/APP)	U (via Pk80001_Legalnet.updateAcknowledged)	clsAcknowledge
APPEAL	(default/APP)	R, I, U, D (all via pk80001_legalNet.*Appeal procs)	clsAppeal, clsAppealCollection
APPEAL_EXTENSION	(default/APP)	I, U (via pk80001_legalnet.*AppealExtension procs)	clsAppealExtension
CASE_NOTE	(default/APP)	R, I, U, D (all via Pk80001_Legalnet.CaseNote/case_note_* procs)	clsCaseNote
CASE_OUTCOME	(default/APP)	R, I, U, D (via pk80001_legalnet.*CaseOutcome/GetCaseOutcome procs)	clsCaseOutcome, clsCaseOutcomeCollection
LEGAL_CASE_EVALUATION	(default/APP)	R, I, U (via pk80001_legalnet.*LCE/getLCE procs)	clsLegalCaseEvaluation
LEGAL_FILE_LOCATION	(default/APP)	R, I, U (via procs), D (inline SQL confirmed)	clsLegalFileLocation
LEGAL_SCHEDULE / SCHEDULE (proc target unclear)	(default/APP)	R, I, U, D (via getSchedule/insertSchedule/updateSchedule/deleteSchedule procs)	clsLegalSchedule
LEGAL_CASE	(default/APP)	R, I, U, D (via getLegalCase/InsertLegalCase/UpdateLegalCase/DeleteLegalCase procs, plus ad-hoc updateHearingSeqNum, setFileCreateDate, setMailDate, setMailOARFlag, updateCaseStatus)	clsLegalCase, clsLegalCaseCollection, clsCreateFile, clsMailDate, clsConsolidations
CONSOLIDATED_CASE	(default/APP)	U (via pk80001_LEGALNET.UpdateConsolidatedCase)	clsConsolidations
pk80005_assignment-related table(s) (unidentified)	(default/APP)	scalar read (max_result_seq), proc call (CalendarReview)	clsAssignmentProcess, clsQuickCal

CASE_COST

Field	Value
Schema	(default/APP)
CRUD (this library)	R, I, U, D
Sequence / PK source	not observed (Create throws — no PK-issuing SQL found in Criteria)
Business objects	clsCaseCost, clsCaseCostCollection
Confidence	verified
Source files	LegalNet\clsCaseCost.vb

CASE_STATUS_HISTORY

Field	Value
Schema	(default/APP)
CRUD (this library)	R (collection only; no I/U/D Criteria found — likely proc-driven)
Sequence / PK source	n/a
Business objects	clsCaseStatusHistoryCollection
Confidence	verified (R)
Source files	LegalNet\clsCaseStatusHistory.vb

ATTORNEY_CONFLICT

Field	Value
Schema	(default/APP)
CRUD (this library)	R, I, U, D
Sequence / PK source	ATTORNEY_CONFLICT_SEQ_NUM_NEXTVAL (unusual form — flagged)
Business objects	clsAttorneyConflict
Confidence	verified
Source files	TableMaint\clsAttorneyConflict.vb

ATTORNEY_POLICY_ASSIGNMENT

Field	Value
Schema	(default/APP)
CRUD (this library)	R, I, U, D
Sequence / PK source	inline sequence select
Business objects	clsAttorneyWorkerAssignment (assignment-adjacent)
Confidence	verified
Source files	TableMaint\clsAttorneyWorkerAssignment.vb

ATTORNEY_SCHEDULE

Field	Value
Schema	(default/APP)
CRUD (this library)	R, I, U, D
Sequence / PK source	inline sequence select
Business objects	clsAttorneyWorkerAssignment-family
Confidence	verified
Source files	TableMaint\clsAttorneyWorkerAssignment.vb

ATTORNEY_WORKER_ASSIGNMENT

Field	Value
Schema	(default/APP)
CRUD (this library)	R, I, U, D
Sequence / PK source	inline sequence select
Business objects	clsAttorneyWorkerAssignment, clsOutOFTownRotationAssgn
Confidence	verified
Source files	TableMaint\clsAttorneyWorkerAssignment.vb, TableMaint\clsOutOFTownRotationAssgn.vb

HEARING_LOCATION

Field	Value
Schema	(default/APP)
CRUD (this library)	R, I, U, D
Sequence / PK source	HEARING_LOCATION_SEQ_NUM sequence
Business objects	clsHearingLocationInfo (LegalNet, read-only), clsHearingLocation (TableMaint, full CRUD)
Confidence	verified
Source files	LegalNet\clsHearingLocationInfo.vb, TableMaint\clsHearingLocation.vb — **duplication flag**: two distinct business objects target the same table for different use-cases

LEGAL_PERSON

Field	Value
Schema	(default/APP)
CRUD (this library)	R, I, U, D
Sequence / PK source	DataPortalInfo.GetNextSeqNum("LEGAL_PERSON_SEQ") (API-based sequence)
Business objects	clsLegalPerson, clsLegalPersonList
Confidence	verified
Source files	TableMaint\clsLegalPerson.vb, clsLegalPersonList.vb

LEGAL_PERSON_TYPE

Field	Value
Schema	(default/APP)
CRUD (this library)	R, I, U, D
Sequence / PK source	inline sequence select
Business objects	clsLegalPersonType
Confidence	verified
Source files	TableMaint\clsLegalPersonType.vb

OTHER_FIRM

Field	Value
Schema	(default/APP)
CRUD (this library)	R, I, U, D
Sequence / PK source	inline sequence select
Business objects	clsOtherFirm
Confidence	verified
Source files	TableMaint\clsOtherFirm.vb

OUT_OF_TOWN_ROTATION_ASSGN

Field	Value
Schema	(default/APP)
CRUD (this library)	R, I, U, D
Sequence / PK source	inline sequence select
Business objects	clsOutOFTownRotationAssgn
Confidence	verified
Source files	TableMaint\clsOutOFTownRotationAssgn.vb

OUTCOME_ISSUE

Field	Value
Schema	(default/APP)
CRUD (this library)	R (Criteria + collection)
Sequence / PK source	n/a observed
Business objects	clsOutcomeIssue
Confidence	verified
Source files	LegalNet\clsOutcomeIssue.vb

OUTCOME_STAFF

Field	Value
Schema	(default/APP)
CRUD (this library)	R (Criteria + collection)
Sequence / PK source	n/a observed
Business objects	clsOutcomeStaff
Confidence	verified
Source files	LegalNet\clsOutcomeStaff.vb

REQUEST_ISSUE

Field	Value
Schema	(default/APP)
CRUD (this library)	R (Criteria + collection)
Sequence / PK source	n/a observed
Business objects	clsRequestIssue
Confidence	verified
Source files	LegalNet\clsRequestIssue.vb

LEGAL_CASE_EMPLOYER

Field	Value
Schema	(default/APP)
CRUD (this library)	R (Criteria + collection)
Sequence / PK source	n/a observed
Business objects	clsOtherLegalParty
Confidence	verified
Source files	LegalNet\clsOtherLegalParty.vb

legal.attorney_profile

Field	Value
Schema	LEGAL (schema-qualified)
CRUD (this library)	R
Sequence / PK source	n/a observed
Business objects	clsAttorneyProfile-style lookup
Confidence	verified
Source files	grep hit under LegalNet\

LEGAL.vw_cases

Field	Value
Schema	LEGAL (view)
CRUD (this library)	R (scalar)
Sequence / PK source	n/a
Business objects	clsLetterRequest (ad-hoc case-number lookup)
Confidence	verified
Source files	LegalNet\clsLetterRequest.vb

SCHEDULE

Field	Value
Schema	(default/APP)
CRUD (this library)	R (subquery only, inside CanDeleteLocation)
Sequence / PK source	n/a
Business objects	clsHearingLocation
Confidence	verified (subquery)
Source files	TableMaint\clsHearingLocation.vb

ALL_STATES

Field	Value
Schema	(default/APP, reference)
CRUD (this library)	R (scalar, string-concatenated — flagged)
Sequence / PK source	n/a
Business objects	clsHearingLocation.ValidateState, clsOtherFirm.ValidateState
Confidence	verified
Source files	TableMaint\clsHearingLocation.vb, TableMaint\clsOtherFirm.vb

ADP_EMPLOYEE

Field	Value
Schema	(default/APP)
CRUD (this library)	R (joined from code-table lookups)
Sequence / PK source	n/a
Business objects	mdlCodeTables code-table classes
Confidence	verified
Source files	mdlCodeTables.vb

xprint_control_seq (sequence object)

Field	Value
Schema	(default/APP)
CRUD (this library)	sequence .NEXTVAL only
Sequence / PK source	—
Business objects	clsCreateFile.GetNextDocSeqNum
Confidence	verified
Source files	LegalNet\clsCreateFile.vb

ACKNOWLEDGE (or similarly named)

Field	Value
Schema	(default/APP)
CRUD (this library)	U (via Pk80001_Legalnet.updateAcknowledged)
Sequence / PK source	n/a
Business objects	clsAcknowledge
Confidence	inferred*
Source files	LegalNet\clsAcknowledge.vb

APPEAL

Field	Value
Schema	(default/APP)
CRUD (this library)	R, I, U, D (all via pk80001_legalNet.*Appeal procs)
Sequence / PK source	inferred sequence via APPEAL_SEQ_NUM_NEXTVAL*
Business objects	clsAppeal, clsAppealCollection
Confidence	inferred*
Source files	LegalNet\clsAppeal.vb

APPEAL_EXTENSION

Field	Value
Schema	(default/APP)
CRUD (this library)	I, U (via pk80001_legalnet.*AppealExtension procs)
Sequence / PK source	n/a
Business objects	clsAppealExtension
Confidence	inferred*
Source files	LegalNet\clsAppeal.vb

CASE_NOTE

Field	Value
Schema	(default/APP)
CRUD (this library)	R, I, U, D (all via Pk80001_Legalnet.CaseNote/case_note_* procs)
Sequence / PK source	n/a
Business objects	clsCaseNote
Confidence	inferred*
Source files	LegalNet\clsCaseNote.vb

CASE_OUTCOME

Field	Value
Schema	(default/APP)
CRUD (this library)	R, I, U, D (via pk80001_legalnet.*CaseOutcome/GetCaseOutcome procs)
Sequence / PK source	n/a
Business objects	clsCaseOutcome, clsCaseOutcomeCollection
Confidence	inferred*
Source files	LegalNet\clsCaseOutcome.vb

LEGAL_CASE_EVALUATION

Field	Value
Schema	(default/APP)
CRUD (this library)	R, I, U (via pk80001_legalnet.*LCE/getLCE procs)
Sequence / PK source	n/a
Business objects	clsLegalCaseEvaluation
Confidence	inferred*
Source files	LegalNet\clsLegalCaseEvaluation.vb

LEGAL_FILE_LOCATION

Field	Value
Schema	(default/APP)
CRUD (this library)	R, I, U (via procs), D (inline SQL confirmed)
Sequence / PK source	n/a
Business objects	clsLegalFileLocation
Confidence	mixed: D verified, R/I/U inferred*
Source files	LegalNet\clsLegalFileLocation.vb

LEGAL_SCHEDULE SCHEDULE (proc target unclear)

Field	Value
Schema	(default/APP)
CRUD (this library)	R, I, U, D (via getSchedule/insertSchedule/updateSchedule/deleteSchedule procs)
Sequence / PK source	n/a
Business objects	clsLegalSchedule
Confidence	inferred*
Source files	LegalNet\clsLegalSchedule.vb

LEGAL_CASE

Field	Value
Schema	(default/APP)
CRUD (this library)	R, I, U, D (via getLegalCase/InsertLegalCase/UpdateLegalCase/DeleteLegalCase procs, plus ad-hoc updateHearingSeqNum, setFileCreateDate, setMailDate, setMailOARFlag, updateCaseStatus)
Sequence / PK source	n/a
Business objects	clsLegalCase, clsLegalCaseCollection, clsCreateFile, clsMailDate, clsConsolidations
Confidence	inferred* (core CRUD) / verified (ad-hoc call sites)
Source files	LegalNet\clsLegalCase.vb, clsCreateFile.vb, clsMailDate.vb, clsConsolidations.vb

CONSOLIDATED_CASE

Field	Value
Schema	(default/APP)
CRUD (this library)	U (via pk80001_LEGALNET.UpdateConsolidatedCase)
Sequence / PK source	n/a
Business objects	clsConsolidations
Confidence	inferred*
Source files	LegalNet\clsConsolidations.vb

pk80005_assignment-related table(s) (unidentified)

Field	Value
Schema	(default/APP)
CRUD (this library)	scalar read (max_result_seq), proc call (CalendarReview)
Sequence / PK source	n/a
Business objects	clsAssignmentProcess, clsQuickCal
Confidence	unknown**
Source files	LegalNet\clsAssignmentProcess.vb, clsQuickCal.vb

Code-table lookup pattern

mdlCodeTables.vb (library root) is a VB Module containing ~30 nested reference-data classes, each executing a simple SELECT CODE, CODE_DESCR [, extra columns] FROM {table} [WHERE <filter>] ORDER BY CODE — the "application-layer SQL" pattern. Documented as one consolidated group (DB-CLL-CT-001) rather than ~30 individual SQL IDs, per TSK1B-002 §1.1. Tables referenced:

ATTORNEY_SCHEDULE_TYPE_CODE, ATTORNEY_TYPE_CODE, LEGAL_BODY_PART_CODE, CANCELLED_BY_CODE, CASE_STATUS_CODE, DURATION_CODE, LEGAL_FILE_LOCATION_CODE, HEARING_LOCATION_CODE, NATURE_CODE, NOTE_CATEGORY_CODE, OUTCOME_GROUNDS_CODE, OUTCOME_ISSUE_CODE, ORDER_TYPE_CODE, OTHER_FIRM_TYPE_CODE, PETITIONER_RESPONDENT_CODE, REQUESTED_BY_CODE, REQUEST_ISSUE_CODE (or similar), REQUEST_TYPE_CODE, REVIEW_LEVEL_CODE, SCHEDULE_STATUS_CODE, SCHEDULE_TYPE_CODE, SERVICE_CODE, plus joined lookups against ADP_EMPLOYEE, ATTORNEY_PROFILE, LEGAL_PERSON/LEGAL_PERSON_TYPE, OTHER_FIRM, HEARING_LOCATION.

Notes

• Duplication flag: HEARING_LOCATION is targeted by two distinct business objects for different use-cases — clsHearingLocationInfo (LegalNet, case-facing read-only lookup) and clsHearingLocation (TableMaint, reference-data maintenance with full CRUD). See architecture.md.
• Security note: the ValidateState() ad-hoc query in both clsHearingLocation and clsOtherFirm builds SQL via raw string concatenation of a user-suppliable input rather than a bind parameter — a non-parameterized query and potential SQL-injection surface. See business-rules.md and SQL-063/SQL-064 ValidateState pages.

  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.

Related documentation

• Overview
• Architecture
• Dependencies
• Operations and processes
• Business rules
• SQL catalog

Sources

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