SAIF.Application.Claim.Legal database tables

This page catalogs the Oracle database tables accessed by this library. This library contains zero IBusinessCriteria implementations; all SQL cataloged here is application-layer SQL authored directly in AppManager-style classes, bypassing the business/IBusinessCriteria layer*. No SQL in this library uses an explicit OWNER.TABLE_NAME schema qualifier, so every table's schema is recorded as [unqualified]**.

Table catalog

Table	Database	Schema/Link	CRUD	Purpose	Primary business object
POLICY	Oracle	[unqualified]**	R	Distinct policy name lookup for attorney-assignment maintenance	clsAttorneyTableManager
DURATION_CODE	Oracle	[unqualified]**	R	Hearing/schedule duration code lookup	clsAttorneyTableManager, clsHearingLocationManager
HEARING_LOCATION_CODE	Oracle	[unqualified]**	R	Hearing location code lookup	clsAttorneyTableManager, clsHearingLocationManager
ATTORNEY_SCHEDULE_TYPE_CODE	Oracle	[unqualified]**	R	Attorney schedule type code lookup	clsAttorneyTableManager
ADP_EMPLOYEE	Oracle	[unqualified]**	R	Employee source for adjuster and attorney name lookups	clsAttorneyTableManager
ATTORNEY_PROFILE	Oracle	[unqualified]**	R	Attorney profile records, incl. assigned secretary	clsAttorneyTableManager, clsSummaryManager
LEGAL_CODE_TABLE	Oracle	[unqualified]**	R	Generic maintenance-window code table	clsAttorneyTableManager, clsLegalPersonManager
LEGAL_PERSON	Oracle	[unqualified]**	R	Legal person master records (ALJs, board members, attorneys)	clsAttorneyTableManager, clsOtherPartiesManager, clsUpdateCaseManager
LEGAL_PERSON_TYPE	Oracle	[unqualified]**	R	Type/category classification for LEGAL_PERSON rows	clsAttorneyTableManager, clsOtherPartiesManager, clsUpdateCaseManager
OTHER_FIRM_TYPE_CODE	Oracle	[unqualified]**	R	Other-firm type code lookup	clsLegalPersonManager
LEGAL_PERSON_TYPE_CODE	Oracle	[unqualified]**	R	Legal person-type code lookup	clsLegalPersonManager
OTHER_FIRM	Oracle	[unqualified]**	R	Firm-level attorney/employer/insurer records	clsOtherPartiesManager, clsUpdateCaseManager
APPEAL	Oracle	[unqualified]**	R	Case appeal records	clsOutcomeManager
REVIEW_LEVEL_CODE	Oracle	[unqualified]**	R	Review/hearing level code lookup	clsOutcomeManager
CASE_STATUS_CODE	Oracle	[unqualified]**	R	Case status code lookup, filtered by prior status history	clsUpdateCaseManager
DUAL	Oracle	N/A (pseudo-table)	R	Oracle pseudo-table for scalar function calls and placeholder rows	clsScheduleManager, clsOtherPartiesManager, clsOutcomeManager

POLICY

Field	Value
Database	Oracle
Schema/Link	[unqualified]**
Purpose	Provides a distinct policy number/name lookup for the attorney-assignment maintenance screen.
Access pattern	Direct SQL
CRUD operations	Read
Key columns used	POLICY_NUM, POLICY_NAME
Sequence	N/A
Related tables	None identified
Business objects	clsAttorneyTableManager (App class)

SQL referencing this table

• SQL-008: Retrieve distinct policy name list

---

DURATION_CODE

Field	Value
Database	Oracle
Schema/Link	[unqualified]**
Purpose	Supplies the duration code list used by attorney-assignment and hearing-location maintenance screens.
Access pattern	Direct SQL
CRUD operations	Read
Key columns used	CODE, CODE_DESCR
Sequence	N/A
Related tables	None identified
Business objects	clsAttorneyTableManager, clsHearingLocationManager (App classes)

SQL referencing this table

• SQL-006: Retrieve hearing duration code list
• SQL-010: Retrieve duration code list (hearing location screen)

---

HEARING_LOCATION_CODE

Field	Value
Database	Oracle
Schema/Link	[unqualified]**
Purpose	Supplies the hearing location code list used by attorney-assignment and hearing-location maintenance screens.
Access pattern	Direct SQL
CRUD operations	Read
Key columns used	CODE, CODE_DESCR
Sequence	N/A
Related tables	None identified
Business objects	clsAttorneyTableManager, clsHearingLocationManager (App classes)

Business rules referencing this table

• BR-LGL-015: A hearing location cannot be deleted while in use*

SQL referencing this table

• SQL-007: Retrieve hearing location code list
• SQL-011: Retrieve hearing location code list (hearing location screen)

---

ATTORNEY_SCHEDULE_TYPE_CODE

Field	Value
Database	Oracle
Schema/Link	[unqualified]**
Purpose	Supplies the attorney schedule type code list for attorney-assignment maintenance.
Access pattern	Direct SQL
CRUD operations	Read
Key columns used	CODE, CODE_DESCR
Sequence	N/A
Related tables	None identified
Business objects	clsAttorneyTableManager (App class)

SQL referencing this table

• SQL-005: Retrieve attorney schedule type codes

---

ADP_EMPLOYEE

Field	Value
Database	Oracle
Schema/Link	[unqualified]**
Purpose	Source of adjuster and attorney employee names; joined to ATTORNEY_PROFILE to resolve attorney display names.
Access pattern	Direct SQL
CRUD operations	Read
Key columns used	EMPLID, LAST_NAME, FIRST_NAME, PREFERRED_NAME, EMPL_STATUS, DESCR
Sequence	N/A
Related tables	ATTORNEY_PROFILE
Business objects	clsAttorneyTableManager (App class)

SQL referencing this table

• SQL-002: Retrieve claims adjuster code list
• SQL-004: Retrieve attorney list with names

---

ATTORNEY_PROFILE

Field	Value
Database	Oracle
Schema/Link	[unqualified]**
Purpose	Attorney profile records, joined to ADP_EMPLOYEE to build attorney lists, and queried directly to resolve an assigned attorney's secretary employee ID.
Access pattern	Direct SQL
CRUD operations	Read
Key columns used	PROFILE_EMPLID, SECRETARY_EMPLID
Sequence	N/A
Related tables	ADP_EMPLOYEE
Business objects	clsAttorneyTableManager, clsSummaryManager (App classes)

SQL referencing this table

• SQL-004: Retrieve attorney list with names
• SQL-021: Retrieve legal secretary employee ID by attorney

---

LEGAL_CODE_TABLE

Field	Value
Database	Oracle
Schema/Link	[unqualified]**
Purpose	Generic maintenance-window code table, filtered by TABLE_MAINT_WIN_NAME to drive attorney-assignment and person-maintenance code lookups.
Access pattern	Direct SQL
CRUD operations	Read
Key columns used	TABLE_NAME, TABLE_MAINT_WIN_NAME
Sequence	N/A
Related tables	None identified
Business objects	clsAttorneyTableManager, clsLegalPersonManager (App classes)

SQL referencing this table

• SQL-009: Retrieve attorney-assignment maintenance table list
• SQL-014: Retrieve legal code tables for person maintenance

---

LEGAL_PERSON

Field	Value
Database	Oracle
Schema/Link	[unqualified]**
Purpose	Legal person master records (ALJs, board members, attorneys), joined to LEGAL_PERSON_TYPE to build person/attorney selection lists across multiple maintenance screens.
Access pattern	Direct SQL
CRUD operations	Read
Key columns used	LEGAL_PERSON_SEQ_NUM, LAST_NAME, FIRST_NAME
Sequence	N/A
Related tables	LEGAL_PERSON_TYPE
Business objects	clsAttorneyTableManager, clsOtherPartiesManager, clsUpdateCaseManager (App classes)

SQL referencing this table

• SQL-003: Retrieve administrative law judge (ALJ) list
• SQL-017: Retrieve attorney person list for other parties
• SQL-023: Retrieve attorney person list for case update

---

LEGAL_PERSON_TYPE

Field	Value
Database	Oracle
Schema/Link	[unqualified]**
Purpose	Type/category classification for LEGAL_PERSON rows (e.g. ALJ, BOARD, FJ, ATTY, OUTC), including expiration tracking.
Access pattern	Direct SQL
CRUD operations	Read
Key columns used	LEGAL_PERSON_SEQ_NUM, LEGAL_PERSON_TYPE_CODE, EXP_DATE
Sequence	N/A
Related tables	LEGAL_PERSON
Business objects	clsAttorneyTableManager, clsOtherPartiesManager, clsUpdateCaseManager (App classes)

SQL referencing this table

• SQL-003: Retrieve administrative law judge (ALJ) list
• SQL-017: Retrieve attorney person list for other parties
• SQL-023: Retrieve attorney person list for case update

---

OTHER_FIRM_TYPE_CODE

Field	Value
Database	Oracle
Schema/Link	[unqualified]**
Purpose	Other-firm type code lookup for legal-person/firm maintenance.
Access pattern	Direct SQL
CRUD operations	Read
Key columns used	CODE, CODE_DESCR
Sequence	N/A
Related tables	None identified
Business objects	clsLegalPersonManager (App class)

Business rules referencing this table

• BR-LGL-016: A legal person cannot be deleted while in use*

SQL referencing this table

• SQL-013: Retrieve other-firm type code list

---

LEGAL_PERSON_TYPE_CODE

Field	Value
Database	Oracle
Schema/Link	[unqualified]**
Purpose	Legal person-type code lookup for legal-person/firm maintenance.
Access pattern	Direct SQL
CRUD operations	Read
Key columns used	CODE, CODE_DESCR
Sequence	N/A
Related tables	None identified
Business objects	clsLegalPersonManager (App class)

Business rules referencing this table

• BR-LGL-016: A legal person cannot be deleted while in use*

SQL referencing this table

• SQL-015: Retrieve legal person type code list

---

OTHER_FIRM

Field	Value
Database	Oracle
Schema/Link	[unqualified]**
Purpose	Firm-level attorney/employer/insurer records used as an alternative to LEGAL_PERSON/LEGAL_PERSON_TYPE for other-parties and update-case attorney lists.
Access pattern	Direct SQL
CRUD operations	Read
Key columns used	OTHER_FIRM_SEQ_NUM, FIRM_NAME, OTHER_FIRM_TYPE_CODE
Sequence	N/A
Related tables	None (queried as a parallel, non-joined alternative to LEGAL_PERSON/LEGAL_PERSON_TYPE, not a foreign-key relationship)*
Business objects	clsOtherPartiesManager, clsUpdateCaseManager (App classes)

SQL referencing this table

• SQL-016: Retrieve attorney firm list for other parties
• SQL-018: Retrieve employer/insurer firm list for other parties
• SQL-022: Retrieve attorney firm list for case update

---

APPEAL

Field	Value
Database	Oracle
Schema/Link	[unqualified]**
Purpose	Case appeal records, joined to REVIEW_LEVEL_CODE to build the appeal/hearing-level selection list for case outcome maintenance.
Access pattern	Direct SQL
CRUD operations	Read
Key columns used	APPEAL_SEQ_NUM, APPEAL_NUM, LEGAL_CASE_SEQ_NUM, REVIEW_LEVEL_CODE
Sequence	N/A
Related tables	REVIEW_LEVEL_CODE
Business objects	clsOutcomeManager (App class)

SQL referencing this table

• SQL-019: Retrieve appeal hearing level list by case

---

REVIEW_LEVEL_CODE

Field	Value
Database	Oracle
Schema/Link	[unqualified]**
Purpose	Lookup of review/hearing level codes and descriptions, left-outer-joined from APPEAL.
Access pattern	Direct SQL
CRUD operations	Read
Key columns used	CODE, CODE_DESCR
Sequence	N/A
Related tables	APPEAL
Business objects	clsOutcomeManager (App class)

SQL referencing this table

• SQL-019: Retrieve appeal hearing level list by case

---

CASE_STATUS_CODE

Field	Value
Database	Oracle
Schema/Link	[unqualified]**
Purpose	Case status code lookup, filtered to a case's prior status history values, for the case-status maintenance dropdown.
Access pattern	Direct SQL
CRUD operations	Read
Key columns used	CODE, CODE_DESCR
Sequence	N/A
Related tables	LEGAL_CASE (owned by the referenced SAIF.Business.Claim.Legal library; not queried directly by this library — relationship inferred from the _STATUS_CODE naming convention, not a JOIN visible in this library's own SQL)*
Business objects	clsUpdateCaseManager (App class)

Business rules referencing this table

• 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*

SQL referencing this table

• SQL-024: Retrieve case status codes by prior status

---

DUAL

Field	Value
Database	Oracle
Schema/Link	N/A (pseudo-table)
Purpose	Oracle system pseudo-table used for a scalar PL/SQL function call (assigned attorney's manager email) and to prepend a blank/"no selection" row onto several code lists via UNION.
Access pattern	Direct SQL
CRUD operations	Read
Key columns used	N/A (pseudo-table; no columns)
Sequence	N/A
Related tables	None
Business objects	clsScheduleManager, clsOtherPartiesManager, clsOutcomeManager (App classes)

SQL referencing this table

• SQL-016: Retrieve attorney firm list for other parties
• SQL-018: Retrieve employer/insurer firm list for other parties
• SQL-019: Retrieve appeal hearing level list by case
• SQL-020: Retrieve assigned attorney's manager email address

---

Table relationship diagram

The following diagram shows the confirmed and inferred relationships between tables used by this library's SQL.

erDiagram
    ADP_EMPLOYEE ||--o{ ATTORNEY_PROFILE : "EMPLID = PROFILE_EMPLID"
    LEGAL_PERSON ||--o{ LEGAL_PERSON_TYPE : "LEGAL_PERSON_SEQ_NUM join"
    APPEAL }o--|| REVIEW_LEVEL_CODE : "REVIEW_LEVEL_CODE lookup (inferred)*"

OTHER_FIRM is queried as a parallel, non-joined alternative to LEGAL_PERSON/LEGAL_PERSON_TYPE and is not shown with a relationship line. CASE_STATUS_CODE's relationship to the LEGAL_CASE table (owned by SAIF.Business.Claim.Legal) is inferred from naming convention only and is not diagrammed, since LEGAL_CASE is not part of this library's own table set*.

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

• Index
• Overview
• Architecture
• Dependencies
• Operations and processes
• Business rules
• SQL catalog
• API reference

Sources

• SAIF.Application.Claim.Legal\clsAttorneyTableManager.vb
• SAIF.Application.Claim.Legal\clsHearingLocationManager.vb
• SAIF.Application.Claim.Legal\clsLegalPersonManager.vb
• SAIF.Application.Claim.Legal\clsLegalData.vb
• SAIF.Application.Claim.Legal\LegalManagerBase.vb
• SAIF.Application.Claim.Legal\LegalNet\clsOtherPartiesManager.vb
• SAIF.Application.Claim.Legal\LegalNet\clsOutcomeManager.vb
• SAIF.Application.Claim.Legal\LegalNet\clsUpdateCaseManager.vb
• SAIF.Application.Claim.Legal\LegalNet\clsSummaryManager.vb
• SAIF.Application.Claim.Legal\LegalNet\clsScheduleManager.vb