pk80001_legalnet

Overview

Field	Value
Schema	WCIS_DBA
Type	Package
Database	WCIS_DBA / Oracle
Calling libraries	SAIF.Business.Claim.Legal
Calling classes	clsCaseNote, clsCaseSearch, clsConsolidations, clsCreateFile, clsAppeal, clsAppealExtension, clsCaseOutcome, clsLegalCase, clsLegalSchedule, clsHearingLocationInfo, clsAcknowledgeList, clsAttorneyProfile, clsBoardLog, clsLegalFileLocation, clsLegalCaseEvaluation, clsAttorneyWorkerAssignment, clsMailDate, clsImageAlerts, mdlCodeTables.clsLegalStaffList
Member count	98 (55 members previously catalogued from referencing .NET code, plus 43 additional members present in the retrieved package spec that have no confirmed .NET call site*)

Package name casing is inconsistent across the source (pk80001_legalnet, Pk80001_Legalnet, pk80001_legalNet, PK80001_LEGALNET). Oracle identifiers are case-insensitive when unquoted, so these are all the same package; names below are normalised to the casing declared in the retrieved spec.

Purpose

pk80001_legalnet is the primary Oracle PL/SQL package behind SAIF's LegalNet module. It implements CRUD and lookup logic for legal cases (LEGAL_CASE), hearing schedules (SCHEDULE), appeals and appeal extensions (APPEAL, APPEAL_EXTENSION), case outcomes and their issues/staff (CASE_OUTCOME, OUTCOME_ISSUE, OUTCOME_STAFF), case notes (CASE_NOTE), case costs (CASE_COST), legal file locations (LEGAL_FILE_LOCATION), case consolidation (CONSOLIDATED_CASE), attorney profiles and schedules (ATTORNEY_PROFILE, ATTORNEY_SCHEDULE), legal case employer/other-firm associations (LEGAL_CASE_EMPLOYER, OTHER_FIRM), mail-date/OAR/reconsideration-letter tracking, acknowledgement letters, board logs, letter requests, and image-alert history. It also derives case-search results, assigned-attorney lookups, and hearing-sequence-number bookkeeping used throughout the LegalNet UI and related batch jobs.

Procedures / Methods

findCase

Field	Value
Returns	recordset (REF CURSOR)
Called by	clsCaseSearch

Description: Looks up a single legal case by an arbitrary case-identifier string, trying identifier types in priority order: WCB number first; if the input is 7 characters or fewer, DCBS number next; then OWCP number; then OALJ number; then BRB number; and finally the appeal number as a last resort. Returns the matching LEGAL_CASE row(s) via recordset.

Parameters

Parameter	Direction	Type	Default	Description
recordset	OUT	REF CURSOR	—	Matching legal case row(s).
in_num	IN	VARCHAR2	—	Case identifier to resolve (WCB/DCBS/OWCP/OALJ/BRB/appeal number).

get_hearing_date

Field	Value
Returns	DATE
Called by	No confirmed .NET call site*

Description: Returns the scheduled hearing date for the given legal case's most recent hearing schedule row.*

Parameters

Parameter	Direction	Type	Default	Description
in_case_seq	IN	LEGAL_CASE.legal_case_seq_num%TYPE	—	Legal case sequence number.

get_consolidated_hearing_seq

Field	Value
Returns	NUMBER
Called by	No confirmed .NET call site*

Description: Resolves the most-recent HEAR schedule sequence number for a case's consolidation primary, used by updateHearingSeqNum when a case is a secondary in a consolidation.*

Parameters

Parameter	Direction	Type	Default	Description
in_case_seq	IN	LEGAL_CASE.legal_case_seq_num%TYPE	—	Legal case sequence number.

get_hearingID

Field	Value
Returns	NUMBER
Called by	clsHearingLocationInfo

Description: Returns the schedule sequence number of the case's most recent hearing, used to look up hearing-location detail.

Parameters

Parameter	Direction	Type	Default	Description
in_case_seq	IN	LEGAL_CASE.legal_case_seq_num%TYPE	—	Legal case sequence number.

get_asgn_atty

Field	Value
Returns	VARCHAR2
Called by	clsLegalCase

Description: Resolves the assigned attorney's display name for a case using a three-tier fallback: (1) the attorney on the case's most recent appeal, (2) the attorney assigned to the case's scheduled hearing, (3) the assigned other-firm name; returns an empty string if none is found (BR-PKG1-005).

Parameters

Parameter	Direction	Type	Default	Description
IN_case_Seq_num	IN	LEGAL_CASE.legal_case_seq_num%TYPE	—	Legal case sequence number.

get_asgn_atty_id

Field	Value
Returns	VARCHAR2
Called by	No confirmed .NET call site*

Description: Resolves the assigned attorney's employee/person identifier using the same three-tier fallback order as get_asgn_atty (BR-PKG1-005), returning the identifier rather than the display name.*

Parameters

Parameter	Direction	Type	Default	Description
IN_case_Seq_num	IN	LEGAL_CASE.legal_case_seq_num%TYPE	—	Legal case sequence number.

get_atty_mngr_email

Field	Value
Returns	VARCHAR2
Called by	No confirmed .NET call site*

Description: Returns the email address of the manager of the attorney assigned to the case, used for notification routing.*

Parameters

Parameter	Direction	Type	Default	Description
aLegalCaseNum	IN	NUMBER	—	Legal case sequence number.

get_mailid_for_emplid

Field	Value
Returns	VARCHAR2
Called by	No confirmed .NET call site*

Description: Translates an employee ID into the corresponding mail/user ID (adp_employee.user_id).*

Parameters

Parameter	Direction	Type	Default	Description
inEmplid	IN	VARCHAR2	—	Employee ID to translate.

get_case_num

Field	Value
Returns	VARCHAR2
Called by	No confirmed .NET call site*

Description: Resolves a single display case number for the given legal case by taking the first non-null value in priority order: WCB number, DCBS number, OWCP number, OALJ number, then BRB number (BR-PKG1-006).

Parameters

Parameter	Direction	Type	Default	Description
aLegalCaseNum	IN	NUMBER	—	Legal case sequence number.

InsertLegalCase

Field	Value
Returns	NUMBER (new legal_case_seq_num)
Called by	clsLegalCase

Description: Inserts a new LEGAL_CASE row from the full set of case attributes (request type, WCB/DCBS/OWCP/OALJ/BRB numbers, case status, attorney assignment, OAR/acknowledge/DEF dates, stress-claim flag, consolidation flag, etc.) and returns the new sequence number.

Parameters

Parameter	Direction	Type	Default	Description
LegalCaseSeqNum	IN	LEGAL_CASE.LEGAL_CASE_SEQ_NUM%TYPE	—	Sequence number to assign to the new case.
CaseTypeCode	IN	LEGAL_CASE.REQUEST_TYPE_CODE%TYPE	—	Case type code.
RequestTypeCode	IN	LEGAL_CASE.REQUEST_TYPE_CODE%TYPE	—	Request type code.
WcbNum	IN	LEGAL_CASE.WCB_NUM%TYPE	—	Workers' Compensation Board case number.
DcbsNum	IN	LEGAL_CASE.DCBS_NUM%TYPE	—	DCBS case number.
CaseStatusCode	IN	LEGAL_CASE.CASE_STATUS_CODE%TYPE	—	Case status code.
claimID	IN	LEGAL_CASE.claim_id%TYPE	—	Associated claim ID.
RequestDate	IN	LEGAL_CASE.request_date%TYPE	—	Legal request date.
WorkerAttyProvLocID	IN	LEGAL_CASE.WORKER_ATTY_PROV_LOC_ID%TYPE	—	Worker's attorney provider location ID.
WorkerAttyFirmProvLocID	IN	LEGAL_CASE.WORKER_ATTY_FIRM_PROV_LOC_ID%TYPE	—	Worker's attorney firm provider location ID.
OarDueDate	IN	LEGAL_CASE.OAR_DUE_DATE%TYPE	—	Order allowing/reconsideration due date.
OarMailedDate	IN	LEGAL_CASE.OAR_MAILED_DATE%TYPE	—	OAR mailed date.
LegalFileCreateDate	IN	LEGAL_CASE.LEGAL_FILE_CREATE_DATE%TYPE	—	Legal file creation date.
CaseWeightValue	IN	LEGAL_CASE.CASE_WEIGHT_VALUE%TYPE	—	Case weighting value (BR-PKG1-007).
AcknowledgeDate	IN	LEGAL_CASE.ACKNOWLEDGE_DATE%TYPE	—	Acknowledgement date.
RequestNewWcbNumFlag	IN	LEGAL_CASE.REQUEST_ALL_DAY_HEARING_FLAG%TYPE	—	Flag requesting a new WCB number.
RequestedByCode	IN	LEGAL_CASE.REQUESTED_BY_CODE%TYPE	—	Code identifying who requested the case.
RequestReceiptDate	IN	LEGAL_CASE.REQUEST_RECEIPT_DATE%TYPE	—	Date the request was received.
RequestPostmarkDate	IN	LEGAL_CASE.REQUEST_POSTMARK_DATE%TYPE	—	Postmark date on the request.
RequestAllDayHearingFlag	IN	LEGAL_CASE.REQUEST_ALL_DAY_HEARING_FLAG%TYPE	—	All-day-hearing request flag.
StressClaimFlag	IN	LEGAL_CASE.STRESS_CLAIM_FLAG%TYPE	—	Stress-claim flag (BR-PKG1-007).
ReconLetterMailedDate	IN	LEGAL_CASE.RECON_LETTER_MAILED_DATE%TYPE	—	Reconsideration letter mailed date.
ReconPacketReceivedDate	IN	LEGAL_CASE.RECON_PACKET_RECEIVED_DATE%TYPE	—	Reconsideration packet received date.
DismissalDate	IN	LEGAL_CASE.DISMISSAL_DATE%TYPE	—	Dismissal date.
SettlementDate	IN	LEGAL_CASE.SETTLEMENT_DATE%TYPE	—	Settlement date.
SendDefFlag	IN	LEGAL_CASE.SEND_DEF_FLAG%TYPE	—	Flag to send DEF letter.
DefSentDate	IN	LEGAL_CASE.DEF_SENT_DATE%TYPE	—	DEF letter sent date.
EmployerEvaluationNum	IN	LEGAL_CASE.EMPLOYER_EVALUATION_NUM%TYPE	—	Employer evaluation number.
ConsolidateCaseFlag	IN	LEGAL_CASE.CONSOLIDATE_CASE_FLAG%TYPE	—	Flag indicating case participates in consolidation.
OwcpNum	IN	LEGAL_CASE.OWCP_NUM%TYPE	—	OWCP case number.
OaljNum	IN	LEGAL_CASE.OALJ_NUM%TYPE	—	OALJ case number.
BrbNum	IN	LEGAL_CASE.BRB_NUM%TYPE	—	BRB case number.
CreateDate	IN	LEGAL_CASE.CREATE_DATE%TYPE	—	Audit creation timestamp.
CreateUser	IN	LEGAL_CASE.CREATE_USER%TYPE	—	Audit creation user.
MostRecentHearingSeqNum	IN	LEGAL_CASE.MOST_RECENT_HEARING_SEQ_NUM%TYPE	—	Most recent hearing schedule sequence number.
ClaimImageAlertsFlag	IN	LEGAL_CASE.CLAIM_IMAGE_ALERTS_FLAG%TYPE	—	Claim image-alerts flag.

UpdateLegalCase

Field	Value
Returns	NUMBER (rows-affected/status indicator)
Called by	clsLegalCase

Description: Updates an existing LEGAL_CASE row with the same full set of attributes as InsertLegalCase, replacing CreateDate/CreateUser with UpdateDate/UpdateUser.

Parameters

Parameter	Direction	Type	Default	Description
LegalCaseSeqNum	IN	LEGAL_CASE.LEGAL_CASE_SEQ_NUM%TYPE	—	Case to update.
CaseTypeCode through ClaimImageAlertsFlag	IN	(see InsertLegalCase)	—	Same attribute set as InsertLegalCase, with UpdateDate/UpdateUser replacing CreateDate/CreateUser.

CREATE_REQUEST_ISSUE

Field	Value
Returns	none (procedure)
Called by	No confirmed .NET call site*

Description: Inserts a REQUEST_ISSUE row recording the issue code/text associated with a legal case request; used by UPDATE_LEGALCASE_REQUEST when tracking reconsideration issues (BR-PKG1-007).

Parameters

Parameter	Direction	Type	Default	Description
in_legal_case_seq	IN	LEGAL_CASE.legal_Case_seq_num%TYPE	—	Legal case sequence number.
in_issue_code	IN	REQUEST_ISSUE.REQUEST_ISSUE_CODE%TYPE	—	Request issue code.
in_issue_text	IN	REQUEST_ISSUE.REQUEST_ISSUE_TEXT%TYPE	—	Request issue free text.

get_case_issue_codes

Field	Value
Returns	VARCHAR2 (delimited list of codes)
Called by	No confirmed .NET call site*

Description: Returns a concatenated list of REQUEST_ISSUE codes recorded for the given case.*

Parameters

Parameter	Direction	Type	Default	Description
in_case_seq_num	IN	NUMBER	—	Legal case sequence number.

get_case_issue_text

Field	Value
Returns	VARCHAR2 (concatenated text)
Called by	clsLegalCase

Description: Returns the concatenated free-text issue descriptions recorded for the given case's request issues.

Parameters

Parameter	Direction	Type	Default	Description
in_case_seq_num	IN	NUMBER	—	Legal case sequence number.

Casesearch

Field	Value
Returns	recordset (search_cursor REF CURSOR of search_rec: WCB number, case status, attorney name, hearing date, request issue, requested-by, case seq num, claim ID)
Called by	clsCaseSearch

Description: Searches legal cases for a comma-delimited list of claim IDs, applying a closed-case filter: when includeClosed = 'Y' no cases are excluded by status, otherwise cases with CASE_STATUS_CODE = 'CLOSE' are excluded (BR-PKG1-001). This is the single procedure backing the legacy CaseSearch/findCase .NET call sites.

Parameters

Parameter	Direction	Type	Default	Description
recordset	OUT	search_cursor	—	Matching case search rows.
claimIdsCommaDelimited	IN	VARCHAR2	—	Comma-delimited list of claim IDs to search.
includeClosed	IN	VARCHAR2	—	'Y' to include closed cases, otherwise closed cases are excluded.

updateCaseStatus

Field	Value
Returns	none (procedure)
Called by	clsConsolidations

Description: Updates a legal case's status code and, because a status change on a consolidation primary must be reflected on its secondary cases, cascades the same status update to all secondary cases consolidated under the given case (BR-PKG1-002).

Parameters

Parameter	Direction	Type	Default	Description
aLegalCaseNum	IN	NUMBER	—	Legal case sequence number.
newStatus	IN	VARCHAR2	—	New case status code.
updateDate	IN	DATE	—	Audit update timestamp.
updateUser	IN	VARCHAR2	—	Audit update user.

updateHearingSeqNum

Field	Value
Returns	none (procedure)
Called by	clsLegalCase

Description: Recomputes and stores LEGAL_CASE.MOST_RECENT_HEARING_SEQ_NUM using a three-tier fallback: (1) if the case is a consolidated secondary, use its primary's most recent HEAR schedule via get_consolidated_hearing_seq; (2) otherwise use the case's own most recent HEAR schedule; (3) if neither exists, fall back to the case's own most recent CCALL/INCON/MED schedule. The recalculated value then cascades to any consolidated secondary cases (BR-PKG1-003).

Parameters

Parameter	Direction	Type	Default	Description
aLegalCaseNum	IN	NUMBER	—	Legal case sequence number to recompute.

getLCE

Field	Value
Returns	Recordset (REF CURSOR)
Called by	clsLegalCaseEvaluation

Description: Returns the legal case evaluation (LCE) row(s) for a given legal case.

Parameters

Parameter	Direction	Type	Default	Description
LegalCaseID	IN	NUMBER	—	Legal case sequence number.
Recordset	OUT	REF CURSOR	—	LCE row(s).

updateLCE

Field	Value
Returns	NUMBER (status indicator)
Called by	clsLegalCaseEvaluation

Description: Updates an existing LCE row's excused flag, due/filed dates, adjuster, and case-transfer date.

Parameters

Parameter	Direction	Type	Default	Description
EvaluationID	IN	NUMBER	—	LCE sequence number to update.
IsExcused	IN	VARCHAR2	—	Excused flag.
dueDate	IN	DATE	—	Evaluation due date.
filedDate	IN	DATE	—	Evaluation filed date.
AdjusterEmployeeID	IN	VARCHAR2	—	Adjuster's employee ID.
casetransferDate	IN	DATE	—	Case transfer date.
updateDate	IN	DATE	—	Audit update timestamp.
updateUser	IN	VARCHAR2	—	Audit update user.
AsgnAttyEmplid	IN	VARCHAR2	—	Assigned attorney employee ID.

insertLCE

Field	Value
Returns	NUMBER (status indicator)
Called by	clsLegalCaseEvaluation

Description: Inserts a new LCE row for a legal case. Wraps the insert in a WHEN OTHERS handler that re-raises via RAISE_APPLICATION_ERROR(-20004, <message>) on failure.

Parameters

Parameter	Direction	Type	Default	Description
EvaluationID	IN	NUMBER	—	New LCE sequence number.
LegalCaseID	IN	NUMBER	—	Legal case sequence number.
IsExcused	IN	VARCHAR2	—	Excused flag.
dueDate	IN	DATE	—	Evaluation due date.
filedDate	IN	DATE	—	Evaluation filed date.
AdjusterEmployeeID	IN	VARCHAR2	—	Adjuster's employee ID.
casetransferDate	IN	DATE	—	Case transfer date.
createDate	IN	DATE	—	Audit creation timestamp.
createUser	IN	VARCHAR2	—	Audit creation user.
AsgnAttyEmplid	IN	VARCHAR2	—	Assigned attorney employee ID.

getSchedule

Field	Value
Returns	recordset (REF CURSOR)
Called by	clsLegalSchedule

Description: Returns all hearing schedule rows for a legal case.

Parameters

Parameter	Direction	Type	Default	Description
LegalCaseSeqNum	IN	NUMBER	—	Legal case sequence number.
recordset	OUT	REF CURSOR	—	Schedule row(s).

updateSchedule

Field	Value
Returns	NUMBER (status indicator)
Called by	clsLegalSchedule

Description: Updates an existing SCHEDULE row (date/time, ALJ, hearing location, assigned attorney, duration, type/status codes, reminder date, assigned other-firm/person) and triggers recalculation of the case's hearing sequence number.*

Parameters

Parameter	Direction	Type	Default	Description
ScheduleSeqNum	IN	NUMBER	—	Schedule row to update.
ScheduleDate	IN	DATE	—	Hearing date.
ScheduleTime	IN	VARCHAR2	—	Hearing time.
AljPersonSeqNum	IN	NUMBER	—	Assigned ALJ person sequence number.
HearingLocationSeqNum	IN	NUMBER	—	Hearing location sequence number.
AsgnAttorneyEmplid	IN	VARCHAR2	—	Assigned attorney employee ID.
DurationCode	IN	VARCHAR2	—	Hearing duration code.
ScheduleTypeCode	IN	VARCHAR2	—	Schedule type code (e.g. HEAR/CCALL/INCON/MED).
ScheduleStatusCode	IN	VARCHAR2	—	Schedule status code.
KeepAttorneyFlag	IN	VARCHAR2	—	Flag to retain attorney assignment.
AssignedDate	IN	DATE	—	Assignment date.
ReminderDate	IN	DATE	—	Reminder date.
ScheduleText	IN	VARCHAR2	—	Free-text schedule notes.
UpdateDate	IN	DATE	—	Audit update timestamp.
UpdateUser	IN	VARCHAR2	—	Audit update user.
AsgnOtherFirmSeqNum	IN	NUMBER	—	Assigned other-firm sequence number.
AsgnPersonSeqNum	IN	NUMBER	—	Assigned person sequence number.

insertSchedule

Field	Value
Returns	NUMBER (status indicator)
Called by	clsLegalSchedule

Description: Inserts a new SCHEDULE row for a legal case with the same attribute set as updateSchedule, and triggers recalculation of the case's hearing sequence number.*

Parameters

Parameter	Direction	Type	Default	Description
ScheduleSeqNum	IN	NUMBER	—	New schedule sequence number.
LegalCaseSeqNum	IN	NUMBER	—	Legal case sequence number.
ScheduleDate through asgnPersonSeqNum	IN	(see updateSchedule)	—	Same attribute set as updateSchedule, with createDate/createUser replacing UpdateDate/UpdateUser.

deleteSchedule

Field	Value
Returns	NUMBER (status indicator)
Called by	clsLegalSchedule

Description: Deletes a SCHEDULE row and triggers recalculation of the owning case's hearing sequence number.*

Parameters

Parameter	Direction	Type	Default	Description
ScheduleSeqNum	IN	NUMBER	—	Schedule row to delete.

getLegalCase

Field	Value
Returns	recordset (IN OUT REF CURSOR)
Called by	clsLegalCase

Description: Returns the LEGAL_CASE row for a given legal case sequence number and/or claim ID.

Parameters

Parameter	Direction	Type	Default	Description
LegalCaseSeqNum	IN	NUMBER	—	Legal case sequence number.
ClaimId	IN	NUMBER	—	Associated claim ID.
recordset	IN OUT	REF CURSOR	—	Legal case row.

getClaimImageAlertsUpdateDate

Field	Value
Returns	DATE
Called by	No confirmed .NET call site*

Description: Returns the last-updated date associated with the claim's image-alert flag for the given legal case.*

Parameters

Parameter	Direction	Type	Default	Description
aLegalCaseNum	IN	NUMBER	—	Legal case sequence number.

UPDATE_LEGALCASE_REQUEST

Field	Value
Returns	none (procedure)
Called by	No confirmed .NET call site*

Description: Updates the request-related fields of a legal case (issue type, WCB-requested flag, stress flag, requested-by, request/received/postmark dates, attorney firm/provider IDs, reconsideration letter flag, LCE-excused flag, OWCP flag). Calculates OAR_DUE_DATE as the postmark date plus 10 days, but only when the case has no existing reconsideration-flagged request issue (v_recon_cnt = 0); sets CASE_WEIGHT_VALUE to 3 when the stress-claim flag is 'Y', otherwise 1 (BR-PKG1-007).

Parameters

Parameter	Direction	Type	Default	Description
aLegalCaseSeq	IN	NUMBER	—	Legal case sequence number.
aIssueType	IN	LEGAL_CASE.REQUEST_TYPE_CODE%TYPE	—	Issue/request type code.
aWCBRequested	IN	LEGAL_CASE.request_new_wcb_num_flag%TYPE	—	Flag requesting a new WCB number.
aStress	IN	LEGAL_CASE.stress_claim_flag%TYPE	—	Stress-claim flag (BR-PKG1-007).
aRequestedBy	IN	LEGAL_CASE.REQUESTED_BY_CODE%TYPE	—	Requested-by code.
aRequestDate	IN	VARCHAR2	—	Request date (string).
aReceivedDate	IN	VARCHAR2	—	Received date (string).
aPostmarkDate	IN	VARCHAR2	—	Postmark date (string), basis of OAR due date (BR-PKG1-007).
aAttyFirmProvID	IN	NUMBER	—	Attorney firm provider location ID.
aReconLetter	IN	VARCHAR2	—	Reconsideration letter flag.
aAttyProvID	IN	NUMBER	—	Attorney provider location ID.
aLCEExcused	IN	VARCHAR2	—	LCE excused flag (BR-PKG1-008).
aOWCP	IN	VARCHAR2	—	OWCP flag.
aUserID	IN	VARCHAR2	—	Audit user.

update_lce_Excused

Field	Value
Returns	none (procedure)
Called by	No confirmed .NET call site*

Description: Synchronizes the LCE excused flag for a case: if an LCE row already exists and its excused flag differs from the new value, updates it; if no LCE row exists and the new value is 'Y', inserts a new LCE row (BR-PKG1-008).

Parameters

Parameter	Direction	Type	Default	Description
aLegalCaseSeq	IN	NUMBER	—	Legal case sequence number.
aLCEExcused	IN	VARCHAR2	—	New excused flag value.
aUserID	IN	VARCHAR2	—	Audit user.

setFileCreateDate

Field	Value
Returns	none (procedure)
Called by	clsCreateFile

Description: Sets LEGAL_FILE_CREATE_DATE on one or more legal cases identified by a delimited list of sequence numbers.

Parameters

Parameter	Direction	Type	Default	Description
seqNums	IN	VARCHAR2	—	Delimited list of legal case sequence numbers.
inDate	IN	DATE	—	File-create date to set.
userid	IN	VARCHAR2	—	Audit user.

mailDateList

Field	Value
Returns	recordset (REF CURSOR)
Called by	clsMailDate

Description: Returns the list of legal cases eligible for mailing based on inType (OAR or reconsideration). A case is eligible for OAR mailing when its mail_oar_flag = 'Y', it is not closed, OAR_DUE_DATE IS NOT NULL, and OAR_MAILED_DATE IS NULL; a case is eligible for reconsideration-packet follow-up when RECON_LETTER_MAILED_DATE IS NOT NULL and RECON_PACKET_RECEIVED_DATE IS NULL (BR-PKG1-009).

Parameters

Parameter	Direction	Type	Default	Description
recordset	OUT	REF CURSOR	—	Eligible case rows.
inType	IN	VARCHAR2	—	Mail list type (OAR or reconsideration).

setMailDate

Field	Value
Returns	none (procedure)
Called by	clsMailDate

Description: Sets the mail date (OAR mailed date or reconsideration letter mailed date, per inType) for one or more legal cases identified by a delimited list of sequence numbers.

Parameters

Parameter	Direction	Type	Default	Description
seqNums	IN	VARCHAR2	—	Delimited list of legal case sequence numbers.
inDate	IN	DATE	—	Mail date to set.
inType	IN	VARCHAR2	—	Mail type (OAR or reconsideration).
userid	IN	VARCHAR2	—	Audit user.

setNoMailDate

Field	Value
Returns	NUMBER (status indicator)
Called by	No confirmed .NET call site*

Description: Marks a case as not requiring a mail date. For a consolidated secondary case, the mail date (OAR mailed date or recon packet received date, per inType) is inherited from the case's consolidation primary rather than being set independently (BR-PKG1-010).

Parameters

Parameter	Direction	Type	Default	Description
InseqNum	IN	NUMBER	—	Legal case sequence number.
inDate	IN	DATE	—	Date to record.
inType	IN	VARCHAR2	—	Mail type (OAR or reconsideration).
userid	IN	VARCHAR2	—	Audit user.

setMailOARFlag

Field	Value
Returns	NUMBER (status indicator)
Called by	clsMailDate

Description: Sets LEGAL_CASE.MAIL_OAR_FLAG = 'Y' for a legal case, marking it eligible for OAR mail-date processing by mailDateList (BR-PKG1-009).

Parameters

Parameter	Direction	Type	Default	Description
InseqNum	IN	NUMBER	—	Legal case sequence number.
userid	IN	VARCHAR2	—	Audit user.

insertAppeal

Field	Value
Returns	NUMBER (new appeal_seq_num / status indicator)
Called by	clsAppeal

Description: Inserts a new APPEAL row (review level, due/filed dates, petitioner/respondent code, approved flag, attorney, brief 1/2/3 due/filed dates, appeal number, linked schedule). Wraps the insert in a WHEN OTHERS handler that re-raises via RAISE_APPLICATION_ERROR(-20004, <message>) on failure.

Parameters

Parameter	Direction	Type	Default	Description
AppealID	IN	NUMBER	—	New appeal sequence number.
LegalCaseID	IN	NUMBER	—	Legal case sequence number.
reviewLevel	IN	VARCHAR2	—	Appeal review level code.
dueDate	IN	DATE	—	Appeal due date.
filedDate	IN	DATE	—	Appeal filed date.
PetitionerRespondentCode	IN	VARCHAR2	—	Petitioner/respondent code.
approvedFlag	IN	VARCHAR2	—	Approved flag.
attyEmplid	IN	VARCHAR2	—	Assigned attorney employee ID.
brief1DueDate	IN	DATE	—	Brief 1 due date.
brief1FiledDate	IN	DATE	—	Brief 1 filed date.
brief2DueDate	IN	DATE	—	Brief 2 due date.
brief2FiledDate	IN	DATE	—	Brief 2 filed date.
brief3DueDate	IN	DATE	—	Brief 3 due date.
brief3FiledDate	IN	DATE	—	Brief 3 filed date.
appealNum	IN	VARCHAR2	—	External appeal number.
ScheduleID	IN	NUMBER	—	Linked schedule sequence number.
createDate	IN	DATE	—	Audit creation timestamp.
createUser	IN	VARCHAR2	—	Audit creation user.

updateAppeal

Field	Value
Returns	NUMBER (status indicator)
Called by	clsAppeal

Description: Updates an existing APPEAL row with the same attribute set as insertAppeal, replacing createDate/createUser with updateDate/updateUser.

Parameters

Parameter	Direction	Type	Default	Description
AppealID	IN	NUMBER	—	Appeal row to update.
LegalCaseID through ScheduleID	IN	(see insertAppeal)	—	Same attribute set as insertAppeal.
updateDate	IN	DATE	—	Audit update timestamp.
updateUser	IN	VARCHAR2	—	Audit update user.

getAppeal

Field	Value
Returns	Recordset (REF CURSOR)
Called by	clsAppeal, clsAppealExtension

Description: Returns all APPEAL rows for a legal case.

Parameters

Parameter	Direction	Type	Default	Description
LegalCaseID	IN	NUMBER	—	Legal case sequence number.
Recordset	OUT	REF CURSOR	—	Appeal row(s).

getAppealExtension

Field	Value
Returns	Recordset (REF CURSOR)
Called by	No confirmed .NET call site*

Description: Returns all APPEAL_EXTENSION rows for a given appeal.*

Parameters

Parameter	Direction	Type	Default	Description
appealSeqNum	IN	NUMBER	—	Appeal sequence number.
Recordset	OUT	REF CURSOR	—	Appeal extension row(s).

updateAppealExtension

Field	Value
Returns	NUMBER (status indicator)
Called by	clsAppealExtension

Description: Updates an existing APPEAL_EXTENSION row's request date and days requested.

Parameters

Parameter	Direction	Type	Default	Description
appealExtSeqNum	IN	NUMBER	—	Appeal extension row to update.
appealSeqNum	IN	NUMBER	—	Owning appeal sequence number.
requestDate	IN	DATE	—	Extension request date.
daysRequested	IN	NUMBER	—	Number of days requested.

insertAppealExtension

Field	Value
Returns	NUMBER (status indicator)
Called by	clsAppealExtension

Description: Inserts a new APPEAL_EXTENSION row for an appeal.

Parameters

Parameter	Direction	Type	Default	Description
appealExtSeqNum	IN	NUMBER	—	New appeal extension sequence number.
appealSeqNum	IN	NUMBER	—	Owning appeal sequence number.
requestDate	IN	DATE	—	Extension request date.
daysRequested	IN	NUMBER	—	Number of days requested.

deleteappeal

Field	Value
Returns	NUMBER (status indicator)
Called by	clsAppeal

Description: Deletes an APPEAL row and cascades cleanup: deletes the appeal's linked schedule (if any) and recalculates the case's hearing sequence number; conditionally calls popcasestatus to revert the case status when the deleted appeal's review level changed the case's status history, and/or popfilelocation to revert the file location when the assigned attorney/location code on the deleted appeal matches the current file location (including the special-case APEAL location code) (BR-PKG1-004).

Parameters

Parameter	Direction	Type	Default	Description
inlegalcaseseqnum	IN	NUMBER	—	Legal case sequence number.
inappealseqnum	IN	NUMBER	—	Appeal sequence number to delete.

popcasestatus

Field	Value
Returns	none (procedure)
Called by	No confirmed .NET call site*

Description: Reverts a legal case's status to its prior value from case-status history, invoked by deleteappeal when deleting an appeal changes the case's status lineage (BR-PKG1-004).

Parameters

Parameter	Direction	Type	Default	Description
inlegalcaseseqnum	IN	LEGAL_CASE.legal_case_seq_num%TYPE	—	Legal case sequence number.

popfilelocation

Field	Value
Returns	none (procedure)
Called by	No confirmed .NET call site*

Description: Reverts a legal case's file location by deleting the most recent LEGAL_FILE_LOCATION row (file location is versioned, ordered by move_date descending, with the most recent row authoritative), invoked by deleteappeal (BR-PKG1-004, BR-PKG1-011).

Parameters

Parameter	Direction	Type	Default	Description
inlegalcaseseqnum	IN	LEGAL_CASE.legal_case_seq_num%TYPE	—	Legal case sequence number.

getBoardLog

Field	Value
Returns	Recordset (REF CURSOR)
Called by	clsBoardLog

Description: Returns board-log reference data (e.g. review-level/appeal board lookup rows) used to populate the LegalNet appeal UI.*

Parameters

Parameter	Direction	Type	Default	Description
Recordset	OUT	REF CURSOR	—	Board log rows.

updateAppealAtty

Field	Value
Returns	none (procedure)
Called by	No confirmed .NET call site*

Description: Updates the attorney assigned to a specific appeal.*

Parameters

Parameter	Direction	Type	Default	Description
appealSeqNum	IN	NUMBER	—	Appeal sequence number.
attyEmplid	IN	VARCHAR2	—	Attorney employee ID to assign.

getAcknowledgeList

Field	Value
Returns	recordset (REF CURSOR)
Called by	clsAcknowledgeList

Description: Returns the list of legal cases pending acknowledgement-letter processing for a given type.*

Parameters

Parameter	Direction	Type	Default	Description
recordset	OUT	REF CURSOR	—	Pending acknowledgement rows.
inType	IN	VARCHAR2	—	Acknowledgement list type.

updateAcknowledged

Field	Value
Returns	NUMBER (status indicator)
Called by	clsAcknowledgeList

Description: Records that a legal case's acknowledgement letter of the given type was sent on the given date.*

Parameters

Parameter	Direction	Type	Default	Description
inType	IN	VARCHAR2	—	Acknowledgement type.
inNum	IN	VARCHAR2	—	Case identifier.
inDate	IN	DATE	—	Date acknowledged.
CaseId	IN	NUMBER	—	Legal case sequence number.

updateDEFfields

Field	Value
Returns	none (procedure)
Called by	No confirmed .NET call site*

Description: Updates a legal case's "Denial/Extension/Filing" (DEF) tracking fields — a case identifier, a date, and a flag.*

Parameters

Parameter	Direction	Type	Default	Description
aLegalCaseNum	IN	NUMBER	—	Legal case sequence number.
inNum	IN	VARCHAR2	—	DEF-related case identifier.
inDate	IN	DATE	—	DEF-related date.
inFlag	IN	VARCHAR2	—	DEF-related flag.
inUser	IN	VARCHAR2	—	Audit user.

getFileLocation

Field	Value
Returns	Recordset (REF CURSOR)
Called by	clsLegalFileLocation

Description: Returns the LEGAL_FILE_LOCATION history for a legal case, most recent first (BR-PKG1-011).

Parameters

Parameter	Direction	Type	Default	Description
LegalCaseID	IN	NUMBER	—	Legal case sequence number.
Recordset	OUT	REF CURSOR	—	File location row(s).

updateFileLocation

Field	Value
Returns	NUMBER (status indicator)
Called by	clsLegalFileLocation

Description: Updates an existing LEGAL_FILE_LOCATION row's location person, location code, and move date.

Parameters

Parameter	Direction	Type	Default	Description
LegalFileLocationID	IN	NUMBER	—	File location row to update.
LegalCaseID	IN	NUMBER	—	Legal case sequence number.
LocationPersonEmplid	IN	VARCHAR2	—	Employee holding the file.
LegalFileLocationCode	IN	VARCHAR2	—	Location code.
moveDate	IN	DATE	—	Date of the move.

insertFileLocation

Field	Value
Returns	NUMBER (status indicator)
Called by	clsLegalFileLocation

Description: Inserts a new LEGAL_FILE_LOCATION row, versioning the file's location history for the case (BR-PKG1-011).

Parameters

Parameter	Direction	Type	Default	Description
LegalFileLocationID	IN	NUMBER	—	New file location sequence number.
LegalCaseID	IN	NUMBER	—	Legal case sequence number.
LocationPersonEmplid	IN	VARCHAR2	—	Employee holding the file.
LegalFileLocationCode	IN	VARCHAR2	—	Location code.
moveDate	IN	DATE	—	Date of the move.
createDate	IN	DATE	—	Audit creation timestamp.
createUser	IN	VARCHAR2	—	Audit creation user.

getAttyProfile

Field	Value
Returns	Recordset (REF CURSOR)
Called by	clsAttorneyProfile

Description: Returns all ATTORNEY_PROFILE rows (claim-type flags, load percent, hearing location assignments, claim team, attorney type, secretary/manager, OSB number).

Parameters

Parameter	Direction	Type	Default	Description
Recordset	OUT	REF CURSOR	—	Attorney profile row(s).

updateAttyProfile

Field	Value
Returns	NUMBER (status indicator)
Called by	clsAttorneyProfile

Description: Updates an existing ATTORNEY_PROFILE row's effective/expiration dates, claim-type eligibility flags, load percent, hearing location assignments, claim team, attorney type, secretary/manager, and OSB number.

Parameters

Parameter	Direction	Type	Default	Description
profileEmplid	IN	VARCHAR2	—	Attorney employee ID.
EffDate	IN	DATE	—	Profile effective date.
ExpDate	IN	DATE	—	Profile expiration date.
CcuClaimsFlag	IN	VARCHAR2	—	Eligible for CCU claims flag.
PtdClaimsFlag	IN	VARCHAR2	—	Eligible for PTD claims flag.
PpdClaimsFlag	IN	VARCHAR2	—	Eligible for PPD claims flag.
ConfidentialClaimsFlag	IN	VARCHAR2	—	Eligible for confidential claims flag.
HealthIssueClaimsFlag	IN	VARCHAR2	—	Eligible for health-issue claims flag.
DcbsClaimsFlag	IN	VARCHAR2	—	Eligible for DCBS claims flag.
FederalClaimsFlag	IN	VARCHAR2	—	Eligible for federal claims flag.
LoadPercent	IN	NUMBER	—	Caseload percentage.
PrimaryHearLocCode	IN	VARCHAR2	—	Primary hearing location code.
SecondaryHearLocCode	IN	VARCHAR2	—	Secondary hearing location code.
ClaimTeamCode	IN	VARCHAR2	—	Claim team code.
AttorneyTypeCode	IN	VARCHAR2	—	Attorney type code.
SecretaryEmplid	IN	VARCHAR2	—	Secretary's employee ID.
ManagerEmplid	IN	VARCHAR2	—	Manager's employee ID.
TertiaryHearLocCode	IN	VARCHAR2	—	Tertiary hearing location code.
osbNumber	IN	VARCHAR2	—	Oregon State Bar number.

insertAttyProfile

Field	Value
Returns	NUMBER (status indicator)
Called by	clsAttorneyProfile

Description: Inserts a new ATTORNEY_PROFILE row with the same attribute set as updateAttyProfile.

Parameters

Parameter	Direction	Type	Default	Description
profileEmplid through osbNumber	IN	(see updateAttyProfile)	—	Same attribute set as updateAttyProfile.

getAttySchedule

Field	Value
Returns	Recordset (attySched_cursor REF CURSOR: schedule seq num, emplid, eff/exp date, type code, duration code)
Called by	No confirmed .NET call site*

Description: Returns all attorney out-of-office/schedule-block rows (ATTORNEY_SCHEDULE).*

Parameters

Parameter	Direction	Type	Default	Description
Recordset	OUT	attySched_cursor	—	Attorney schedule row(s).

updateAttySchedule

Field	Value
Returns	none (procedure)
Called by	No confirmed .NET call site*

Description: Updates an existing ATTORNEY_SCHEDULE row's effective/expiration dates, type code, and duration code.*

Parameters

Parameter	Direction	Type	Default	Description
attySchedSeqNum	IN	NUMBER	—	Attorney schedule row to update.
scheduleEmplid	IN	VARCHAR2	—	Attorney employee ID.
effDate	IN	DATE	—	Effective date.
expDate	IN	DATE	—	Expiration date.
typeCode	IN	VARCHAR2	—	Attorney schedule type code.
durationCode	IN	VARCHAR2	—	Duration code.

insertAttySchedule

Field	Value
Returns	none (procedure)
Called by	No confirmed .NET call site*

Description: Inserts a new ATTORNEY_SCHEDULE row with the same attribute set as updateAttySchedule.*

Parameters

Parameter	Direction	Type	Default	Description
attySchedSeqNum	IN	NUMBER	—	New attorney schedule sequence number.
scheduleEmplid through durationCode	IN	(see updateAttySchedule)	—	Same attribute set as updateAttySchedule.

deleteAttySchedule

Field	Value
Returns	none (procedure)
Called by	No confirmed .NET call site*

Description: Deletes an ATTORNEY_SCHEDULE row.*

Parameters

Parameter	Direction	Type	Default	Description
attySchedSeqNum	IN	NUMBER	—	Attorney schedule row to delete.

getConsolidatedCases

Field	Value
Returns	recordset (REF CURSOR)
Called by	clsConsolidations

Description: Returns the set of consolidated cases for a comma-delimited list of claim IDs. A case is displayed if it is not closed and is not itself a consolidated secondary, OR if it is a consolidated secondary regardless of closed status — so secondary/consolidated cases remain visible even after closure (BR-PKG1-012).

Parameters

Parameter	Direction	Type	Default	Description
recordset	OUT	REF CURSOR	—	Consolidated case rows.
claimIdsCommaDelimited	IN	VARCHAR2	—	Comma-delimited list of claim IDs.

UpdateConsolidatedCase

Field	Value
Returns	none (procedure)
Called by	clsConsolidations

Description: Updates a CONSOLIDATED_CASE link between a primary and secondary case. Reassigning a secondary to a new primary migrates the most recent hearing schedule to the new primary; deleting the link (aConsolFlag = 'N') recalculates and resets MOST_RECENT_HEARING_SEQ_NUM on the now-independent secondary case based on its own most recent HEAR schedule (BR-PKG1-013).

Parameters

Parameter	Direction	Type	Default	Description
aConsolidatedSeq	IN	CONSOLIDATED_CASE.consolidated_case_seq_num%TYPE	—	Consolidation link sequence number.
aPrimarySeq	IN	CONSOLIDATED_CASE.primary_case_seq_num%TYPE	—	Primary case sequence number.
aSecondarySeq	IN	CONSOLIDATED_CASE.secondary_case_seq_num%TYPE	—	Secondary case sequence number.
aConsolFlag	IN	VARCHAR2	—	'Y' to establish/reassign the link, 'N' to remove it (BR-PKG1-013).
UpdateDate	IN	DATE	—	Audit update timestamp.
UpdateUser	IN	VARCHAR2	—	Audit update user.

getAttorneyNames

Field	Value
Returns	recordset (attorney_cursor REF CURSOR: attorney name, legal person seq num)
Called by	No confirmed .NET call site*

Description: Returns the list of attorneys (legal persons) for populating attorney-selection lookups.*

Parameters

Parameter	Direction	Type	Default	Description
recordset	OUT	attorney_cursor	—	Attorney name/ID rows.

insertLetterRequest

Field	Value
Returns	requestNum (IN OUT NUMBER)
Called by	No confirmed .NET call site*

Description: Records a request for a legal letter (e.g. acknowledgement or DEF letter) to be generated for a case, returning/assigning the request number.*

Parameters

Parameter	Direction	Type	Default	Description
letterTypeCode	IN	VARCHAR2	—	Letter type code.
aLegalCaseNum	IN	NUMBER	—	Legal case sequence number.
holdDate	IN	DATE	—	Hold/scheduled date for the letter.
aClaimId	IN	NUMBER	—	Associated claim ID.
createUser	IN	VARCHAR2	—	Audit creation user.
requestNum	IN OUT	NUMBER	—	Letter request number (assigned if not supplied).

getHearingInfo

Field	Value
Returns	recordset (HearingCursor REF CURSOR: schedule date/time, duration, keep-attorney flag, assigned/reminder date, schedule text, attorney/secretary mail IDs and names, ALJ name, hearing location detail, hearing status, all-day flag)
Called by	clsHearingLocationInfo

Description: Returns full hearing detail for a specific schedule row, joining attorney, secretary, ALJ, and hearing-location reference data.

Parameters

Parameter	Direction	Type	Default	Description
in_HearingSeq	IN	SCHEDULE.schedule_seq_num%TYPE	—	Schedule sequence number.
recordset	OUT	HearingCursor	—	Hearing detail row.

getEmployerAddr

Field	Value
Returns	recordset (EmployerCursor REF CURSOR: policy name, address lines, city/state/zip, mail-address flag, phone, location seq num)
Called by	No confirmed .NET call site*

Description: Returns the employer's mailing address for a policy number.*

Parameters

Parameter	Direction	Type	Default	Description
in_Policy	IN	POLICY.policy_num%TYPE	—	Policy number.
recordset	OUT	EmployerCursor	—	Employer address row(s).

getOtherFirmList

Field	Value
Returns	recordset (OtherFirmListCursor REF CURSOR: type code, legal case employer seq num, other firm seq num, discovery flags, firm name/type, address, phone/fax)
Called by	No confirmed .NET call site*

Description: Returns the list of other-firm (employer/attorney) associations recorded for a legal case.*

Parameters

Parameter	Direction	Type	Default	Description
aLegalCaseNum	IN	NUMBER	—	Legal case sequence number.
recordset	OUT	OtherFirmListCursor	—	Other-firm association row(s).

GetEmployeebyMailid

Field	Value
Returns	recordset (EmployeeCursor REF CURSOR: emplid, name fields, email, status, phone, location, dept, reports-to, user ID)
Called by	No confirmed .NET call site*

Description: Looks up ADP employee detail by mail/user ID.*

Parameters

Parameter	Direction	Type	Default	Description
in_MailId	IN	VARCHAR	—	Mail/user ID to look up.
recordset	OUT	EmployeeCursor	—	Employee detail row.

GetEmployeeName

Field	Value
Returns	VARCHAR2
Called by	No confirmed .NET call site*

Description: Returns the display name of an ADP employee for a given mail/user ID.*

Parameters

Parameter	Direction	Type	Default	Description
in_mailid	IN	VARCHAR	—	Mail/user ID.

IsLetterRequested

Field	Value
Returns	out_requested (OUT VARCHAR2)
Called by	No confirmed .NET call site*

Description: Indicates whether a letter of the given type has already been requested for a legal case, to prevent duplicate letter requests.*

Parameters

Parameter	Direction	Type	Default	Description
aLegalCaseNum	IN	NUMBER	—	Legal case sequence number.
aLetterType	IN	VARCHAR2	—	Letter type code.
out_requested	OUT	VARCHAR2	—	'Y'/'N' indicating whether already requested.

GetCaseCost

Field	Value
Returns	recordset (CaseCostCursor REF CURSOR)
Called by	No confirmed .NET call site*

Description: Returns all CASE_COST rows (service dates, service code, amount paid/billed, hourly rate, hours billed, prepayment flag, authorized date, payee firm/person provider location, authorizer, cancelled-by code) for a legal case.*

Parameters

Parameter	Direction	Type	Default	Description
aLegalCaseNum	IN	NUMBER	—	Legal case sequence number.
recordset	OUT	CaseCostCursor	—	Case cost row(s).

case_cost_update

Field	Value
Returns	none (procedure)
Called by	No confirmed .NET call site*

Description: Updates an existing CASE_COST row's service dates, service code, amounts, prepayment/authorization info, and payee/authorizer identifiers.*

Parameters

Parameter	Direction	Type	Default	Description
case_cost_seq_num_in	IN	NUMBER	—	Case cost row to update.
legal_case_seq_num_in	IN	NUMBER	—	Legal case sequence number.
service_from_date_in	IN	DATE	—	Service period start date.
service_to_date_in	IN	DATE	—	Service period end date.
service_code_in	IN	VARCHAR2	—	Service code.
amount_paid_in	IN	NUMBER	—	Amount paid.
amount_billed_in	IN	NUMBER	—	Amount billed.
amount_per_hour_in	IN	NUMBER	—	Hourly rate.
hours_billed_in	IN	NUMBER	—	Hours billed.
prepayment_flag_in	IN	VARCHAR2	—	Prepayment flag.
authorized_date_in	IN	DATE	—	Authorization date.
payee_firm_prov_loc_id_in	IN	NUMBER	—	Payee firm provider location ID.
payee_person_prov_loc_id_in	IN	NUMBER	—	Payee person provider location ID.
authorizer_emplid_in	IN	VARCHAR2	—	Authorizer employee ID.
cancelled_by_code_in	IN	VARCHAR2	—	Cancelled-by code.
update_date_in	IN	DATE	—	Audit update timestamp.
update_user_in	IN	VARCHAR2	—	Audit update user.

case_cost_insert

Field	Value
Returns	none (procedure)
Called by	No confirmed .NET call site*

Description: Inserts a new CASE_COST row with the same attribute set as case_cost_update, replacing update_date_in/update_user_in with create_date_in/create_user_in.*

Parameters

Parameter	Direction	Type	Default	Description
case_cost_seq_num_in through cancelled_by_code_in	IN	(see case_cost_update)	—	Same attribute set as case_cost_update.
create_date_in	IN	DATE	—	Audit creation timestamp.
create_user_in	IN	VARCHAR2	—	Audit creation user.

case_cost_delete

Field	Value
Returns	none (procedure)
Called by	No confirmed .NET call site*

Description: Deletes a CASE_COST row.*

Parameters

Parameter	Direction	Type	Default	Description
case_cost_seq_num_in	IN	NUMBER	—	Case cost row to delete.

DeleteLegalCase

Field	Value
Returns	NUMBER (status indicator)
Called by	clsLegalCase

Description: Deletes a LEGAL_CASE row and its sequence number.

Parameters

Parameter	Direction	Type	Default	Description
LegalCaseSeqNum	IN	LEGAL_CASE.legal_case_Seq_num%TYPE	—	Legal case sequence number to delete.

GetCaseNote

Field	Value
Returns	recordset (REF CURSOR)
Called by	clsCaseNote

Description: Returns all CASE_NOTE rows for a legal case.

Parameters

Parameter	Direction	Type	Default	Description
CaseId	IN	NUMBER	—	Legal case sequence number.
recordset	OUT	REF CURSOR	—	Case note row(s).

case_note_insert

Field	Value
Returns	NUMBER (status indicator)
Called by	clsCaseNote

Description: Inserts a new CASE_NOTE row (category code and free text) for a legal case.

Parameters

Parameter	Direction	Type	Default	Description
noteId	IN	NUMBER	—	New case note sequence number.
caseId	IN	NUMBER	—	Legal case sequence number.
categoryCode	IN	VARCHAR2	—	Note category code.
Notetext	IN	VARCHAR2	—	Note free text.
createDate	IN	DATE	—	Audit creation timestamp.
createUser	IN	VARCHAR2	—	Audit creation user.

case_note_update

Field	Value
Returns	NUMBER (status indicator)
Called by	clsCaseNote

Description: Updates an existing CASE_NOTE row's category code and free text.

Parameters

Parameter	Direction	Type	Default	Description
noteId	IN	NUMBER	—	Case note row to update.
caseId	IN	NUMBER	—	Legal case sequence number.
categoryCode	IN	VARCHAR2	—	Note category code.
NoteText	IN	VARCHAR2	—	Note free text.
updateDate	IN	DATE	—	Audit update timestamp.
updateUser	IN	VARCHAR2	—	Audit update user.

case_note_delete

Field	Value
Returns	NUMBER (status indicator)
Called by	clsCaseNote

Description: Deletes a CASE_NOTE row.

Parameters

Parameter	Direction	Type	Default	Description
noteId	IN	NUMBER	—	Case note row to delete.

GetSAIFAtty

Field	Value
Returns	recordset (REF CURSOR)
Called by	attorney lookup (clsLegalCase area)

Description: Returns the list of SAIF legal-staff attorneys for a legal case, used to populate attorney-assignment dropdowns.

Parameters

Parameter	Direction	Type	Default	Description
aLegalCaseNum	IN	NUMBER	—	Legal case sequence number.
recordset	OUT	REF CURSOR	—	Attorney row(s).

GetCaseOutcome

Field	Value
Returns	recordset (REF CURSOR)
Called by	clsCaseOutcome

Description: Returns all CASE_OUTCOME rows (order type, signed/filed dates, assigned attorney, appeal link) for a legal case.

Parameters

Parameter	Direction	Type	Default	Description
LegalCaseID	IN	NUMBER	—	Legal case sequence number.
recordset	OUT	REF CURSOR	—	Case outcome row(s).

GetOutcomeIssue

Field	Value
Returns	recordset (OutcomeIssueCursor REF CURSOR: outcome issue seq num, case outcome seq num, issue code, win/loss flag, fee amount, penalty flag, primary-issue flag, audit fields, summary text, grounds/body-part/nature codes)
Called by	No confirmed .NET call site*

Description: Returns all OUTCOME_ISSUE rows for a legal case's outcome(s).*

Parameters

Parameter	Direction	Type	Default	Description
aLegalCaseNum	IN	NUMBER	—	Legal case sequence number.
recordset	OUT	OutcomeIssueCursor	—	Outcome issue row(s).

getAljList

Field	Value
Returns	recordset (ALJListCursor REF CURSOR: legal person seq num, last name, legal person type code, code description, selected flag)
Called by	No confirmed .NET call site*

Description: Returns the list of ALJs (Administrative Law Judges) available for a legal case's outcome, flagging the currently selected one.*

Parameters

Parameter	Direction	Type	Default	Description
aLegalCaseNum	IN	NUMBER	—	Legal case sequence number.
recordset	OUT	ALJListCursor	—	ALJ row(s).

GetOutcomeStaff

Field	Value
Returns	recordset (OutcomeStaffCursor REF CURSOR: outcome staff seq num, case outcome seq num, outcome staff person seq num, last name)
Called by	No confirmed .NET call site*

Description: Returns the staff persons associated with a legal case's outcome(s).*

Parameters

Parameter	Direction	Type	Default	Description
aLegalCaseNum	IN	NUMBER	—	Legal case sequence number.
recordset	OUT	OutcomeStaffCursor	—	Outcome staff row(s).

updateCaseOutcome

Field	Value
Returns	NUMBER (status indicator)
Called by	clsCaseOutcome

Description: Updates an existing CASE_OUTCOME row's order type, signed/filed dates, assigned attorney, and appeal link. When SetFinalOutcome-eligible order types are recorded, the case outcome becomes the case's final outcome of record (BR-PKG1-014).

Parameters

Parameter	Direction	Type	Default	Description
CaseOutcomeID	IN	NUMBER	—	Case outcome row to update.
LegalCaseID	IN	NUMBER	—	Legal case sequence number.
OrderTypeCode	IN	VARCHAR2	—	Order type code.
OrderSignedDate	IN	DATE	NULL	Order signed date.
OrderFiledDate	IN	DATE	NULL	Order filed date.
UpdateUser	IN	VARCHAR2	NULL	Audit update user.
AsgnAttyEmplid	IN	VARCHAR2	NULL	Assigned attorney employee ID.
AppealId	IN	NUMBER	NULL	Linked appeal sequence number.

insertCaseOutcome

Field	Value
Returns	NUMBER (status indicator)
Called by	clsCaseOutcome

Description: Inserts a new CASE_OUTCOME row with the same attribute set as updateCaseOutcome.

Parameters

Parameter	Direction	Type	Default	Description
CaseOutcomeID through AppealId	IN	(see updateCaseOutcome)	—	Same attribute set as updateCaseOutcome.

updateOutcomeIssue

Field	Value
Returns	none (procedure)
Called by	No confirmed .NET call site*

Description: Upserts an OUTCOME_ISSUE row: updates the row if outcome_issue_seq_num_in already exists, otherwise inserts a new row (BR-PKG1-015).*

Parameters

Parameter	Direction	Type	Default	Description
outcome_issue_seq_num_in	IN	NUMBER	—	Outcome issue sequence number (upsert key).
case_outcome_seq_num_in	IN	NUMBER	—	Owning case outcome sequence number.
outcome_issue_code_in	IN	VARCHAR2	—	Outcome issue code.
win_loss_flag_in	IN	VARCHAR2	—	Win/loss flag.
fee_amount_in	IN	NUMBER	—	Fee amount.
penalty_flag_in	IN	VARCHAR2	—	Penalty flag.
primary_issue_flag_in	IN	VARCHAR2	—	Primary-issue flag.
summary_text_in	IN	VARCHAR2	—	Summary text.
outcome_grounds_code_in	IN	VARCHAR2	—	Outcome grounds code.
body_part_code_in	IN	VARCHAR2	—	Legal body part code.
nature_code_in	IN	VARCHAR2	—	Nature code.
userid_in	IN	VARCHAR2	—	Audit user.

updateOutcomeStaff

Field	Value
Returns	none (procedure)
Called by	No confirmed .NET call site*

Description: Upserts an OUTCOME_STAFF row: updates the row if outcome_staff_seq_num_in already exists, otherwise inserts a new row (BR-PKG1-015).*

Parameters

Parameter	Direction	Type	Default	Description
outcome_staff_seq_num_in	IN	NUMBER	—	Outcome staff sequence number (upsert key).
case_outcome_seq_num_in	IN	NUMBER	—	Owning case outcome sequence number.
staff_person_seq_num_in	IN	NUMBER	—	Staff legal-person sequence number.

DeleteCaseOutcome

Field	Value
Returns	NUMBER (status indicator)
Called by	clsCaseOutcome

Description: Deletes a CASE_OUTCOME row.

Parameters

Parameter	Direction	Type	Default	Description
case_outcome_seq_num_in	IN	NUMBER	—	Case outcome row to delete.

DeleteOutcomeIssue

Field	Value
Returns	none (procedure)
Called by	No confirmed .NET call site*

Description: Deletes an OUTCOME_ISSUE row.*

Parameters

Parameter	Direction	Type	Default	Description
outcome_issue_seq_num_in	IN	NUMBER	—	Outcome issue row to delete.

DeleteOutcomeStaff

Field	Value
Returns	none (procedure)
Called by	No confirmed .NET call site*

Description: Deletes an OUTCOME_STAFF row.*

Parameters

Parameter	Direction	Type	Default	Description
outcome_staff_seq_num_in	IN	NUMBER	—	Outcome staff row to delete.

SetFinalOutcome

Field	Value
Returns	none (procedure)
Called by	No confirmed .NET call site*

Description: Marks the final outcome of record for a legal case, driving case-closure eligibility (BR-PKG1-014).*

Parameters

Parameter	Direction	Type	Default	Description
legal_case_seq_num_in	IN	NUMBER	—	Legal case sequence number.

GetLegalCaseEmployer

Field	Value
Returns	recordset (LegalCaseEmployerCursor REF CURSOR: legal case employer seq num, legal case seq num, employer/attorney other-firm seq nums, attorney legal person seq num, employer/attorney discovery flags)
Called by	No confirmed .NET call site*

Description: Returns the employer and opposing-attorney association row(s) for a legal case, including discovery-request flags.*

Parameters

Parameter	Direction	Type	Default	Description
aLegalCaseNum	IN	NUMBER	—	Legal case sequence number.
recordset	OUT	LegalCaseEmployerCursor	—	Legal case employer row(s).

legal_case_employer_insert

Field	Value
Returns	none (procedure)
Called by	No confirmed .NET call site*

Description: Inserts a new LEGAL_CASE_EMPLOYER row associating an employer and/or opposing attorney other-firm with a legal case, including discovery-request flags.*

Parameters

Parameter	Direction	Type	Default	Description
legal_case_employer_seq_num_in	IN	NUMBER	—	New legal case employer sequence number.
legal_case_seq_num_in	IN	NUMBER	—	Legal case sequence number.
other_firm_seq_num_in	IN	NUMBER	—	Employer other-firm sequence number.
discovery_flag_in	IN	VARCHAR2	—	Employer discovery flag.
attorney_discovery_flag_in	IN	VARCHAR2	—	Attorney discovery flag.
atty_other_firm_seq_num_in	IN	NUMBER	—	Attorney other-firm sequence number.
atty_legal_person_seq_num_in	IN	NUMBER	—	Attorney legal-person sequence number.

legal_case_employer_update

Field	Value
Returns	none (procedure)
Called by	No confirmed .NET call site*

Description: Updates an existing LEGAL_CASE_EMPLOYER row with the same attribute set as legal_case_employer_insert.*

Parameters

Parameter	Direction	Type	Default	Description
legal_case_employer_seq_num_in through atty_legal_person_seq_num_in	IN	(see legal_case_employer_insert)	—	Same attribute set as legal_case_employer_insert.

GetWorkerAtty

Field	Value
Returns	recordset (REF CURSOR)
Called by	clsAttorneyWorkerAssignment

Description: Returns the attorney(s) assigned to a worker for a given firm/attorney location, used for worker-attorney assignment lookups.

Parameters

Parameter	Direction	Type	Default	Description
recordset	OUT	REF CURSOR	—	Assigned attorney row(s).
inFirmLocID	IN	NUMBER	—	Firm provider location ID.
inAttyLocID	IN	NUMBER	—	Attorney provider location ID.

GetAttyLeaveReport

Field	Value
Returns	recordset (AttyLeaveCursor REF CURSOR: schedule emplid, attorney name, vacation days, medical-leave days)
Called by	No confirmed .NET call site*

Description: Returns a leave-usage summary (vacation and medical days) per attorney across a date range, aggregating ATTORNEY_SCHEDULE rows.*

Parameters

Parameter	Direction	Type	Default	Description
inStartDate	IN	DATE	—	Report period start date.
inStopDate	IN	DATE	—	Report period end date.
recordset	OUT	AttyLeaveCursor	—	Attorney leave summary row(s).

alertlegalcase

Field	Value
Returns	OutAlertFlag (OUT VARCHAR2)
Called by	No confirmed .NET call site*

Description: Indicates whether a claim has an active legal-case alert (e.g. open legal case requiring attention), for surfacing an alert indicator on the claim.*

Parameters

Parameter	Direction	Type	Default	Description
inClaimId	IN	NUMBER	—	Claim ID to check.
OutAlertFlag	OUT	VARCHAR2	—	'Y'/'N' alert indicator.

SelectImageAlertsHist

Field	Value
Returns	recordset (REF CURSOR)
Called by	clsImageAlerts

Description: Returns the image-alert history for a legal case.

Parameters

Parameter	Direction	Type	Default	Description
inCaseSeqNum	IN	NUMBER	—	Legal case sequence number.
recordset	OUT	REF CURSOR	—	Image alert history row(s).

SelectLegalEmployees

Field	Value
Returns	recordset (REF CURSOR)
Called by	mdlCodeTables.clsLegalStaffList

Description: Returns the list of legal-department employees for staff-selection lookups.

Parameters

Parameter	Direction	Type	Default	Description
recordset	OUT	REF CURSOR	—	Legal employee row(s).

getPolicyAddresses

Field	Value
Returns	recordset (REF CURSOR)
Called by	No confirmed .NET call site*

Description: Returns mailing address(es) associated with a policy number.*

Parameters

Parameter	Direction	Type	Default	Description
recordset	OUT	REF CURSOR	—	Policy address row(s).
inPolicyNum	IN	NUMBER	—	Policy number to look up.

Business Rules

Rule ID	Rule	Condition	Action
BR-PKG1-001	Case search excludes closed cases by default	Casesearch called with includeClosed <> 'Y'	Excludes rows where CASE_STATUS_CODE = 'CLOSE' from the result set.
BR-PKG1-002	Case status changes cascade to consolidated secondary cases	updateCaseStatus changes the status of a case that is a consolidation primary	Applies the same status update to all consolidated secondary cases linked to the primary.
BR-PKG1-003	Hearing sequence number fallback order	updateHearingSeqNum recalculates MOST_RECENT_HEARING_SEQ_NUM	Uses the consolidation primary's most recent HEAR schedule if the case is a consolidated secondary; otherwise falls back to the case's own most recent HEAR schedule, then its own most recent CCALL/INCON/MED schedule.
BR-PKG1-004	Appeal deletion cascades status and file location reversion	deleteappeal removes an APPEAL row	Deletes the appeal's linked schedule and recalculates hearing sequence; conditionally calls popcasestatus to revert case status and popfilelocation to revert file location when the deleted appeal affected them.
BR-PKG1-005	Assigned attorney resolution fallback order	get_asgn_atty / get_asgn_atty_id resolve the case's assigned attorney	Uses the attorney from the case's most recent appeal if present; otherwise the attorney assigned to the case's scheduled hearing; otherwise the assigned other-firm attorney name.
BR-PKG1-006	Case number display priority	get_case_num resolves the case number to display	Returns the first non-null case number in order: WCB, DCBS, OWCP, OALJ, BRB.
BR-PKG1-007	OAR due date and case weight calculation	UPDATE_LEGALCASE_REQUEST sets request fields on a legal case	Sets OAR_DUE_DATE to postmark date + 10 days only when no existing reconsideration-flagged request issue exists for the case; sets CASE_WEIGHT_VALUE to 3 when the stress-claim flag is 'Y', else 1.
BR-PKG1-008	LCE excused flag uses conditional upsert logic	update_lce_Excused sets the LCE excused flag for a case	Updates the existing LCE row only if the excused flag actually changed; if no LCE row exists and the new flag is 'Y', inserts a new LCE row instead.
BR-PKG1-009	OAR and reconsideration mail eligibility	mailDateList selects cases pending mail processing	A case is OAR-eligible when MAIL_OAR_FLAG='Y', not closed, OAR_DUE_DATE IS NOT NULL, and OAR_MAILED_DATE IS NULL; reconsideration-eligible when RECON_LETTER_MAILED_DATE IS NOT NULL and RECON_PACKET_RECEIVED_DATE IS NULL.
BR-PKG1-010	Consolidated secondary cases inherit mail date from primary	setNoMailDate is called for a consolidated secondary case	The mail date is not set independently on the secondary; it is inherited from the consolidation primary case.
BR-PKG1-011	File location is versioned by move date	getFileLocation / popfilelocation access LEGAL_FILE_LOCATION history	The most recent row ordered by move_date descending is authoritative; popfilelocation reverts location by deleting that most recent row.
BR-PKG1-012	Consolidated secondary cases remain visible when closed	getConsolidatedCases filters the case list	A case is included if it is not closed and not a consolidated secondary, OR if it is a consolidated secondary regardless of its closed status.
BR-PKG1-013	Consolidation link changes migrate or reset hearing sequence	UpdateConsolidatedCase changes a primary/secondary link	Reassigning a secondary to a new primary migrates its most recent hearing schedule to the new primary; removing the link (aConsolFlag='N') recalculates MOST_RECENT_HEARING_SEQ_NUM on the now-independent secondary from its own schedule.
BR-PKG1-014	Final outcome recording drives case closure eligibility	updateCaseOutcome / SetFinalOutcome record a case outcome	When a qualifying final order type/outcome is recorded, the case outcome becomes the case's outcome of record, driving closure eligibility.
BR-PKG1-015	Outcome issue and outcome staff use upsert semantics	updateOutcomeIssue / updateOutcomeStaff are called with a sequence number	Updates the row if the sequence number already exists in OUTCOME_ISSUE/OUTCOME_STAFF, otherwise inserts a new row.

Operations

Operation	Description	Trigger
Legal Case CRUD Lifecycle	Creation, retrieval, update, and deletion of a LEGAL_CASE record, including its request fields, case number resolution, and hearing-sequence/attorney assignment.	A new legal proceeding is opened, edited, or closed against a claim in LegalNet.
Case Search	Search for legal cases by claim, case number, status, or other criteria, excluding closed cases unless explicitly requested.	User performs a case search in the LegalNet UI.
Hearing Schedule Lifecycle	Creation, update, and deletion of SCHEDULE rows (hearings, conference calls, mediations), with automatic recalculation of the case's most recent hearing sequence number.	A hearing or related event is scheduled, rescheduled, or cancelled for a legal case.
Appeal Lifecycle	Creation, update, and deletion of APPEAL rows and their extensions, with cascading reversion of case status and file location when an appeal is deleted.	An appeal is filed, amended, extended, or withdrawn for a legal case.
Case Outcome Lifecycle	Recording of CASE_OUTCOME, OUTCOME_ISSUE, and OUTCOME_STAFF rows, culminating in marking a final outcome of record.	An order is signed/filed or a hearing outcome is recorded for a legal case.
Consolidation Lifecycle	Linking and unlinking of consolidated secondary cases to a primary case, with cascading status updates and hearing-sequence migration/reset.	Related claims/cases are consolidated for joint legal handling, or a consolidation link is removed.
File Location Lifecycle	Tracking of the physical/custodial location of a legal case file over time via versioned LEGAL_FILE_LOCATION rows.	The case file is transferred between staff, attorneys, or storage locations.
Mail Date / OAR Lifecycle	Tracking and processing of Order on Appeal (OAR) and reconsideration-letter mail dates, including eligibility list generation and consolidated-case inheritance.	An OAR or reconsideration letter becomes due, is mailed, or a related packet is received.
Attorney Profile & Assignment Lifecycle	Maintenance of attorney profile records (claim-type eligibility, caseload, hearing locations) and assignment of attorneys to cases, hearings, and appeals.	An attorney's profile changes, or an attorney is assigned/reassigned to a case, hearing, or appeal.
Case Note Lifecycle	Creation, update, and deletion of free-text CASE_NOTE entries against a legal case.	A user records a note on a legal case.
Case Cost Lifecycle	Creation, update, and deletion of CASE_COST billing records associated with a legal case.	A legal service cost/billing entry is recorded, corrected, or removed for a case.

Source Code

PL/SQL package specification

PACKAGE                   Pk80001_Legalnet AS

TYPE ref_cursor IS REF CURSOR;

 PROCEDURE findCase(recordset OUT ref_cursor, in_num IN VARCHAR2);

FUNCTION get_hearing_date (in_case_seq IN LEGAL_CASE.legal_case_seq_num%TYPE)
  RETURN DATE;

FUNCTION get_consolidated_hearing_seq (in_case_seq IN LEGAL_CASE.legal_case_seq_num%TYPE)
   RETURN NUMBER;

FUNCTION get_hearingID (in_case_seq IN LEGAL_CASE.legal_case_seq_num%TYPE)
  RETURN NUMBER;

FUNCTION get_asgn_atty(IN_case_Seq_num IN LEGAL_CASE.legal_case_seq_num%TYPE)
  RETURN VARCHAR2;

FUNCTION get_asgn_atty_id(IN_case_Seq_num IN LEGAL_CASE.legal_case_seq_num%TYPE)
  RETURN VARCHAR2;

FUNCTION get_atty_mngr_email(aLegalCaseNum IN NUMBER) RETURN VARCHAR2;

FUNCTION get_mailid_for_emplid(inEmplid IN VARCHAR2) RETURN VARCHAR2;

FUNCTION get_case_num(aLegalCaseNum IN NUMBER) RETURN VARCHAR2;

Function InsertLegalCase(LegalCaseSeqNum in LEGAL_CASE.LEGAL_CASE_SEQ_NUM%TYPE,
                         CaseTypeCode IN LEGAL_CASE.REQUEST_TYPE_CODE%TYPE,
                         RequestTypeCode in LEGAL_CASE.REQUEST_TYPE_CODE%TYPE,
                         WcbNum in LEGAL_CASE.WCB_NUM%TYPE,
                         DcbsNum in LEGAL_CASE.DCBS_NUM%Type,
                         CaseStatusCode in LEGAL_CASE.CASE_STATUS_CODE%Type,
                         claimID        IN LEGAL_CASE.claim_id%TYPE,
                         RequestDate    IN LEGAL_CASE.request_date%TYPE,
                         WorkerAttyProvLocID in LEGAL_CASE.WORKER_ATTY_PROV_LOC_ID%Type,
                         WorkerAttyFirmProvLocID in LEGAL_CASE.WORKER_ATTY_FIRM_PROV_LOC_ID%Type,
                         OarDueDate      IN LEGAL_CASE.OAR_DUE_DATE%type,
                         OarMailedDate   IN LEGAL_CASE.OAR_MAILED_DATE%type,
                         LegalFileCreateDate IN LEGAL_CASE.LEGAL_FILE_CREATE_DATE%Type,
                         CaseWeightValue  IN LEGAL_CASE.CASE_WEIGHT_VALUE%Type,
                         AcknowledgeDate  IN LEGAL_CASE.ACKNOWLEDGE_DATE%type,
                         RequestNewWcbNumFlag IN LEGAL_CASE.REQUEST_ALL_DAY_HEARING_FLAG%Type,
                         RequestedByCode      IN LEGAL_CASE.REQUESTED_BY_CODE%TYPE,
                         RequestReceiptDate   in LEGAL_CASE.REQUEST_RECEIPT_DATE%Type,
                         RequestPostmarkDate  in LEGAL_CASE.REQUEST_POSTMARK_DATE%type,
                         RequestAllDayHearingFlag in LEGAL_CASE.REQUEST_ALL_DAY_HEARING_FLAG%type,
                         StressClaimFlag      in LEGAL_CASE.STRESS_CLAIM_FLAG%type,
                         ReconLetterMailedDate in LEGAL_CASE.RECON_LETTER_MAILED_DATE%type,
                         ReconPacketReceivedDate in LEGAL_CASE.RECON_PACKET_RECEIVED_DATE%type,
                         DismissalDate         in LEGAL_CASE.DISMISSAL_DATE%Type,
                         SettlementDate        in LEGAL_CASE.SETTLEMENT_DATE%Type,
                         SendDefFlag           in LEGAL_CASE.SEND_DEF_FLAG%type,
                         DefSentDate           in LEGAL_CASE.DEF_SENT_DATE%type,
                         EmployerEvaluationNum in LEGAL_CASE.EMPLOYER_EVALUATION_NUM%type,
                         ConsolidateCaseFlag   in LEGAL_CASE.CONSOLIDATE_CASE_FLAG%type,
                         OwcpNum               in LEGAL_CASE.OWCP_NUM%Type,
                         OaljNum               in LEGAL_CASE.OALJ_NUM%type,
                         BrbNum                in LEGAL_CASE.BRB_NUM%type,
                         CreateDate            in LEGAL_CASE.CREATE_DATE%type,
                         CreateUser            in LEGAL_CASE.CREATE_USER%type,
                         MostRecentHearingSeqNum in LEGAL_CASE.MOST_RECENT_HEARING_SEQ_NUM%type,
                         ClaimImageAlertsFlag   in LEGAL_CASE.CLAIM_IMAGE_ALERTS_FLAG%type)
         Return Number;

Function UpdateLegalCase(LegalCaseSeqNum in LEGAL_CASE.LEGAL_CASE_SEQ_NUM%TYPE,
                         CaseTypeCode IN LEGAL_CASE.REQUEST_TYPE_CODE%TYPE,
                         RequestTypeCode in LEGAL_CASE.REQUEST_TYPE_CODE%TYPE,
                         WcbNum in LEGAL_CASE.WCB_NUM%TYPE,
                         DcbsNum in LEGAL_CASE.DCBS_NUM%Type,
                         CaseStatusCode in LEGAL_CASE.CASE_STATUS_CODE%Type,
                         claimID        IN LEGAL_CASE.claim_id%TYPE,
                         RequestDate    IN LEGAL_CASE.request_date%TYPE,
                         WorkerAttyProvLocID in LEGAL_CASE.WORKER_ATTY_PROV_LOC_ID%Type,
                         WorkerAttyFirmProvLocID in LEGAL_CASE.WORKER_ATTY_FIRM_PROV_LOC_ID%Type,
                         OarDueDate      IN LEGAL_CASE.OAR_DUE_DATE%type,
                         OarMailedDate   IN LEGAL_CASE.OAR_MAILED_DATE%type,
                         LegalFileCreateDate IN LEGAL_CASE.LEGAL_FILE_CREATE_DATE%Type,
                         CaseWeightValue  IN LEGAL_CASE.CASE_WEIGHT_VALUE%Type,
                         AcknowledgeDate  IN LEGAL_CASE.ACKNOWLEDGE_DATE%type,
                         RequestNewWcbNumFlag IN LEGAL_CASE.REQUEST_ALL_DAY_HEARING_FLAG%Type,
                         RequestedByCode      IN LEGAL_CASE.REQUESTED_BY_CODE%TYPE,
                         RequestReceiptDate   in LEGAL_CASE.REQUEST_RECEIPT_DATE%Type,
                         RequestPostmarkDate  in LEGAL_CASE.REQUEST_POSTMARK_DATE%type,
                         RequestAllDayHearingFlag in LEGAL_CASE.REQUEST_ALL_DAY_HEARING_FLAG%type,
                         StressClaimFlag      in LEGAL_CASE.STRESS_CLAIM_FLAG%type,
                         ReconLetterMailedDate in LEGAL_CASE.RECON_LETTER_MAILED_DATE%type,
                         ReconPacketReceivedDate in LEGAL_CASE.RECON_PACKET_RECEIVED_DATE%type,
                         DismissalDate         in LEGAL_CASE.DISMISSAL_DATE%Type,
                         SettlementDate        in LEGAL_CASE.SETTLEMENT_DATE%Type,
                         SendDefFlag           in LEGAL_CASE.SEND_DEF_FLAG%type,
                         DefSentDate           in LEGAL_CASE.DEF_SENT_DATE%type,
                         EmployerEvaluationNum in LEGAL_CASE.EMPLOYER_EVALUATION_NUM%type,
                         ConsolidateCaseFlag   in LEGAL_CASE.CONSOLIDATE_CASE_FLAG%type,
                         OwcpNum               in LEGAL_CASE.OWCP_NUM%Type,
                         OaljNum               in LEGAL_CASE.OALJ_NUM%type,
                         BrbNum                in LEGAL_CASE.BRB_NUM%type,
                         UpdateDate            in LEGAL_CASE.CREATE_DATE%type,
                         UpdateUser            in LEGAL_CASE.CREATE_USER%type,
                         MostRecentHearingSeqNum in LEGAL_CASE.MOST_RECENT_HEARING_SEQ_NUM%type,
                         ClaimImageAlertsFlag   in LEGAL_CASE.CLAIM_IMAGE_ALERTS_FLAG%type)
         Return Number;
PROCEDURE CREATE_REQUEST_ISSUE(in_legal_case_seq IN LEGAL_CASE.legal_Case_seq_num%TYPE,
                               in_issue_code     IN REQUEST_ISSUE.REQUEST_ISSUE_CODE%TYPE,
                               in_issue_text     IN REQUEST_ISSUE.REQUEST_ISSUE_TEXT%TYPE);

FUNCTION get_case_issue_codes (in_case_seq_num NUMBER) RETURN VARCHAR2;

FUNCTION get_case_issue_text (in_case_seq_num NUMBER) RETURN VARCHAR2;

TYPE search_rec IS RECORD(
       wcb_num          VARCHAR2(15),
       case_status      VARCHAR2(65),
       atty_name        VARCHAR2(50),
       hearing_date     VARCHAR2(10),
       REQUEST_ISSUE    VARCHAR2(30),
       requested_by     VARCHAR2(65),
       legal_case_seq_num NUMBER,
       claim_id           NUMBER);

 TYPE search_cursor IS REF CURSOR RETURN search_rec;

PROCEDURE Casesearch(recordset OUT search_cursor,
                        claimIdsCommaDelimited IN VARCHAR2,
                        includeClosed IN VARCHAR2);

 PROCEDURE updateCaseStatus(aLegalCaseNum IN NUMBER, newStatus IN VARCHAR2,
            updateDate IN DATE, updateUser IN VARCHAR2);

 PROCEDURE updateHearingSeqNum(aLegalCaseNum IN NUMBER);

 PROCEDURE getLCE(LegalCaseID IN NUMBER,
       Recordset OUT ref_cursor);

 Function updateLCE(EvaluationID IN NUMBER, IsExcused IN VARCHAR2, dueDate IN DATE,
              filedDate IN DATE, AdjusterEmployeeID IN VARCHAR2, casetransferDate IN DATE,
              updateDate IN DATE, updateUser IN VARCHAR2,
              AsgnAttyEmplid IN VARCHAR2)
              return number;

 Function insertLCE(EvaluationID IN NUMBER, LegalCaseID in number,
              IsExcused IN VARCHAR2, dueDate IN DATE,
              filedDate IN DATE, AdjusterEmployeeID IN VARCHAR2, casetransferDate IN DATE,
              createDate IN DATE, createUser IN VARCHAR2,
              AsgnAttyEmplid IN VARCHAR2)
              return number;

 PROCEDURE getSchedule(LegalCaseSeqNum IN NUMBER,
           recordset OUT ref_cursor);

 Function updateSchedule(ScheduleSeqNum IN NUMBER,
    ScheduleDate IN DATE, ScheduleTime IN VARCHAR2, AljPersonSeqNum IN NUMBER,
    HearingLocationSeqNum IN NUMBER, AsgnAttorneyEmplid IN VARCHAR2, DurationCode IN VARCHAR2,
    ScheduleTypeCode IN VARCHAR2, ScheduleStatusCode IN VARCHAR2, KeepAttorneyFlag IN VARCHAR2,
    AssignedDate IN DATE, ReminderDate IN DATE, ScheduleText IN VARCHAR2,
    UpdateDate IN DATE, UpdateUser IN VARCHAR2,
    AsgnOtherFirmSeqNum IN NUMBER, AsgnPersonSeqNum IN NUMBER)
    return Number;

 Function insertSchedule(ScheduleSeqNum IN NUMBER, LegalCaseSeqNum IN NUMBER,
      ScheduleDate IN DATE, ScheduleTime IN VARCHAR2, AljPersonSeqNum IN NUMBER,
      HearingLocationSeqNum IN NUMBER, AsgnAttorneyEmplid IN VARCHAR2, DurationCode IN VARCHAR2,
      ScheduleTypeCode IN VARCHAR2, ScheduleStatusCode IN VARCHAR2, KeepAttorneyFlag IN VARCHAR2,
      AssignedDate IN DATE, ReminderDate IN DATE, ScheduleText IN VARCHAR2,
      createDate IN DATE, createUser IN VARCHAR2,
      asgnOtherFirmSeqNum IN NUMBER, asgnPersonSeqNum IN NUMBER)
      return number;

 Function deleteSchedule(ScheduleSeqNum IN NUMBER)
 return number;

 PROCEDURE getLegalCase(LegalCaseSeqNum IN NUMBER,
       ClaimId in Number,
       recordset IN OUT ref_cursor);

 FUNCTION getClaimImageAlertsUpdateDate(aLegalCaseNum IN NUMBER)
     RETURN DATE;

PROCEDURE UPDATE_LEGALCASE_REQUEST(aLegalCaseSeq IN NUMBER,
                                   aIssueType IN LEGAL_CASE.REQUEST_TYPE_CODE%TYPE,
                                   aWCBRequested IN LEGAL_CASE.request_new_wcb_num_flag%TYPE,
                                   aStress IN LEGAL_CASE.stress_claim_flag%TYPE,
                                   aRequestedBy IN LEGAL_CASE.REQUESTED_BY_CODE%TYPE,
                                   aRequestDate IN VARCHAR2,
                                   aReceivedDate IN VARCHAR2,
                                   aPostmarkDate IN VARCHAR2,
                                   aAttyFirmProvID IN NUMBER,
                                   aReconLetter IN VARCHAR2,
                                   aAttyProvID IN NUMBER,
                                   aLCEExcused IN VARCHAR2,
                                   aOWCP IN VARCHAR2,
                                   aUserID IN VARCHAR2);

PROCEDURE update_lce_Excused(aLegalCaseSeq IN NUMBER,
                             aLCEExcused IN VARCHAR2,
                             aUserID IN VARCHAR2);


 PROCEDURE setFileCreateDate(seqNums IN VARCHAR2, inDate IN DATE,
           userid IN VARCHAR2);

 PROCEDURE mailDateList(recordset OUT ref_cursor, inType IN VARCHAR2);

 PROCEDURE setMailDate(seqNums IN VARCHAR2, inDate IN DATE,
           inType IN VARCHAR2, userid IN VARCHAR2);

 Function setNoMailDate(InseqNum IN NUMBER, inDate IN DATE,
           inType IN VARCHAR2, userid IN VARCHAR2)
          RETURN NUMBER;

  Function  setMailOARFlag(InseqNum IN NUMBER, userid IN VARCHAR2)
       RETURN NUMBER;

 Function insertAppeal(AppealID IN NUMBER, LegalCaseID IN NUMBER,
              reviewLevel IN VARCHAR2, dueDate IN DATE, filedDate IN DATE,
              PetitionerRespondentCode IN VARCHAR2, approvedFlag IN VARCHAR2,
              attyEmplid IN VARCHAR2,
              brief1DueDate IN DATE, brief1FiledDate IN DATE,
              brief2DueDate IN DATE, brief2FiledDate IN DATE,
              brief3DueDate IN DATE, brief3FiledDate IN DATE,
              appealNum IN VARCHAR2, ScheduleID IN NUMBER,
              createDate IN DATE, createUser IN VARCHAR2)
         RETURN NUMBER;

 FUNCTION updateAppeal(AppealID IN NUMBER, LegalCaseID IN NUMBER,
              reviewLevel IN VARCHAR2, dueDate IN DATE, filedDate IN DATE,
              PetitionerRespondentCode IN VARCHAR2, approvedFlag IN VARCHAR2,
              attyEmplid IN VARCHAR2,
              brief1DueDate IN DATE, brief1FiledDate IN DATE,
              brief2DueDate IN DATE, brief2FiledDate IN DATE,
              brief3DueDate IN DATE, brief3FiledDate IN DATE,
              appealNum IN VARCHAR2, ScheduleID IN NUMBER,
              updateDate IN DATE, updateUser IN VARCHAR2)
           RETURN NUMBER;

     PROCEDURE getAppeal(LegalCaseID IN NUMBER,
       Recordset OUT ref_cursor);

    PROCEDURE getAppealExtension(appealSeqNum IN NUMBER,
       Recordset OUT ref_cursor);

    Function updateAppealExtension(
         appealExtSeqNum IN NUMBER,
         appealSeqNum    IN NUMBER,
         requestDate     IN DATE,
         daysRequested   IN NUMBER)
          RETURN NUMBER;

     Function insertAppealExtension(
         appealExtSeqNum IN NUMBER,
         appealSeqNum    IN NUMBER,
         requestDate     IN DATE,
         daysRequested   IN NUMBER)
       RETURN NUMBER;

    Function deleteappeal (inlegalcaseseqnum IN NUMBER, inappealseqnum IN NUMBER)
     RETURN NUMBER;

    PROCEDURE popcasestatus(inlegalcaseseqnum IN LEGAL_CASE.legal_case_seq_num%TYPE);

    PROCEDURE popfilelocation(inlegalcaseseqnum IN LEGAL_CASE.legal_case_seq_num%TYPE);

    PROCEDURE getBoardLog(Recordset OUT ref_cursor);

    PROCEDURE updateAppealAtty(appealSeqNum IN NUMBER,
       attyEmplid IN VARCHAR2);

   PROCEDURE getAcknowledgeList(recordset OUT ref_cursor, inType IN VARCHAR2);

   Function updateAcknowledged(inType IN VARCHAR2, inNum IN VARCHAR2,
       inDate IN DATE, CaseId IN NUMBER)
        RETURN NUMBER;

   PROCEDURE updateDEFfields(aLegalCaseNum IN NUMBER, inNum IN VARCHAR2,
       inDate IN DATE, inFlag IN VARCHAR2, inUser IN VARCHAR2);


    PROCEDURE getFileLocation(LegalCaseID IN NUMBER,
       Recordset OUT ref_cursor);

    Function updateFileLocation(
         LegalFileLocationID   IN NUMBER,
         LegalCaseID           IN NUMBER,
         LocationPersonEmplid  IN VARCHAR2,
         LegalFileLocationCode IN VARCHAR2,
         moveDate              IN DATE
        ) return number;

     Function insertFileLocation(
         LegalFileLocationID   IN NUMBER,
         LegalCaseID           IN NUMBER,
         LocationPersonEmplid  IN VARCHAR2,
         LegalFileLocationCode IN VARCHAR2,
         moveDate              IN DATE,
         createDate            in date,
         createUser            in varchar2)
       return number;

    PROCEDURE getAttyProfile(Recordset OUT ref_cursor);

    Function updateAttyProfile(
       profileEmplid      IN   VARCHAR2,
       EffDate            IN   DATE,
       ExpDate            IN   DATE,
       CcuClaimsFlag      IN   VARCHAR2,
       PtdClaimsFlag      IN   VARCHAR2,
       PpdClaimsFlag      IN   VARCHAR2,
       ConfidentialClaimsFlag IN VARCHAR2,
       HealthIssueClaimsFlag  IN VARCHAR2,
       DcbsClaimsFlag         IN VARCHAR2,
       FederalClaimsFlag      IN VARCHAR2,
       LoadPercent            IN NUMBER,
       PrimaryHearLocCode     IN VARCHAR2,
       SecondaryHearLocCode   IN VARCHAR2,
       ClaimTeamCode          IN VARCHAR2,
       AttorneyTypeCode       IN VARCHAR2,
       SecretaryEmplid        IN VARCHAR2,
       ManagerEmplid          IN VARCHAR2,
       TertiaryHearLocCode    IN VARCHAR2,
       osbNumber              IN VARCHAR2
       ) RETURN NUMBER;

     Function insertAttyProfile(
       profileEmplid      IN   VARCHAR2,
       EffDate            IN   DATE,
       ExpDate            IN   DATE,
       CcuClaimsFlag      IN   VARCHAR2,
       PtdClaimsFlag      IN   VARCHAR2,
       PpdClaimsFlag      IN   VARCHAR2,
       ConfidentialClaimsFlag IN VARCHAR2,
       HealthIssueClaimsFlag  IN VARCHAR2,
       DcbsClaimsFlag         IN VARCHAR2,
       FederalClaimsFlag      IN VARCHAR2,
       LoadPercent            IN NUMBER,
       PrimaryHearLocCode     IN VARCHAR2,
       SecondaryHearLocCode   IN VARCHAR2,
       ClaimTeamCode          IN VARCHAR2,
       AttorneyTypeCode       IN VARCHAR2,
       SecretaryEmplid        IN VARCHAR2,
       ManagerEmplid          IN VARCHAR2,
       TertiaryHearLocCode    IN VARCHAR2,
       osbNumber              IN VARCHAR2
        ) RETURN NUMBER;

   TYPE attySched_rec IS RECORD(
       attorney_schedule_seq_num  NUMBER,
       schedule_emplid            VARCHAR2(11),
       eff_date                   DATE,
       exp_date                   DATE,
       ATTORNEY_SCHEDULE_TYPE_CODE VARCHAR2(5),
       DURATION_CODE               VARCHAR2(5));

    TYPE attySched_cursor IS REF CURSOR RETURN attySched_rec;

    PROCEDURE getAttySchedule(Recordset OUT attySched_cursor);

    PROCEDURE updateAttySchedule(
         attySchedSeqNum IN NUMBER,
         scheduleEmplid  IN VARCHAR2,
         effDate         IN DATE,
         expDate         IN DATE,
         typeCode        IN VARCHAR2,
         durationCode    IN VARCHAR2);

     PROCEDURE insertAttySchedule(
       attySchedSeqNum IN NUMBER,
         scheduleEmplid  IN VARCHAR2,
         effDate         IN DATE,
         expDate         IN DATE,
         typeCode        IN VARCHAR2,
         durationCode    IN VARCHAR2);

     PROCEDURE deleteAttySchedule(attySchedSeqNum IN NUMBER);

PROCEDURE getConsolidatedCases(recordset OUT ref_cursor,
                        claimIdsCommaDelimited IN VARCHAR2);

 PROCEDURE UpdateConsolidatedCase(aConsolidatedSeq IN CONSOLIDATED_CASE.consolidated_case_seq_num%TYPE,
                                   aPrimarySeq IN CONSOLIDATED_CASE.primary_case_seq_num%TYPE,
                                   aSecondarySeq IN CONSOLIDATED_CASE.secondary_case_seq_num%TYPE,
                                   aConsolFlag IN VARCHAR2,
                                   UpdateDate in DATE,
                                   UpdateUser in Varchar2);
TYPE attorney_rec IS RECORD(
     atty_name VARCHAR2(50),
     legal_person_seq_num LEGAL_PERSON.legal_person_seq_num%TYPE);
TYPE attorney_cursor IS REF CURSOR RETURN attorney_rec;

PROCEDURE getAttorneyNames(recordset OUT attorney_cursor);

PROCEDURE insertLetterRequest(
         letterTypeCode  IN VARCHAR2,
         aLegalCaseNum   IN NUMBER,
         holdDate        IN DATE,
         aClaimId        IN NUMBER,
         createUser      IN VARCHAR2,
         requestNum      IN OUT NUMBER);

TYPE HearingInfo_rec IS RECORD(
schedule_seq_num SCHEDULE.schedule_seq_num%TYPE,
schedule_date SCHEDULE.schedule_date%TYPE,
schedule_time SCHEDULE.schedule_time%TYPE,
DURATION_CODE SCHEDULE.DURATION_CODE%TYPE,
keep_attorney_flag NUMBER(1,0),
assigned_date SCHEDULE.assigned_date%TYPE,
reminder_date SCHEDULE.reminder_date%TYPE,
schedule_text SCHEDULE.schedule_text%TYPE,
atty_mailid adp_employee.user_id%TYPE,
sec_mailid adp_employee.user_id%TYPE,
sec_name VARCHAR2(50),
alj_name LEGAL_PERSON.last_name%TYPE,
secretary_emplid ATTORNEY_PROFILE.secretary_emplid%TYPE,
HEARING_LOCATION_CODE HEARING_LOCATION.HEARING_LOCATION_CODE%TYPE,
hearing_loc_city VARCHAR2(50),
location_name HEARING_LOCATION.location_name%TYPE,
hearing_status SCHEDULE_STATUS_CODE.code_descr%TYPE,
all_day_hearing_flag NUMBER(1,0));
TYPE HearingCursor IS REF CURSOR RETURN HearingInfo_rec;

PROCEDURE getHearingInfo(in_HearingSeq IN SCHEDULE.schedule_seq_num%TYPE,
                         recordset    OUT HearingCursor);

TYPE EmployerAddr_rec IS RECORD(
policy_name     POLICY.policy_name%TYPE,
Loc_addr1       LOCATION.loc_addr1%TYPE,
loc_addr2       LOCATION.loc_Addr2%TYPE,
loc_city        LOCATION.loc_city%TYPE,
loc_state       LOCATION.loc_state%TYPE,
loc_zip         LOCATION.loc_zip%TYPE,
loc_zip4        LOCATION.loc_zip4%TYPE,
mail_addr_flag  LOCATION.mail_addr_flag%TYPE,
loc_phone       VARCHAR2(20),
loc_seq_num     LOCATION.loc_seq_num%TYPE);
TYPE EmployerCursor IS REF CURSOR RETURN EmployerAddr_rec;
PROCEDURE getEmployerAddr(in_Policy IN POLICY.policy_num%TYPE,
                         recordset    OUT EmployerCursor);
TYPE OtherFirmList_rec IS RECORD(
type_cd         VARCHAR2(10),
legal_case_employer_seq_num LEGAL_CASE_EMPLOYER.legal_case_employer_seq_num%TYPE,
employer_other_firm_seq_num OTHER_FIRM.other_firm_seq_num%TYPE,
employer_discovery_flag NUMBER(1,0),
attorney_discovery_flag NUMBER(1,0),
firm_name OTHER_FIRM.firm_name%TYPE,
OTHER_FIRM_TYPE_CODE OTHER_FIRM.OTHER_FIRM_TYPE_CODE%TYPE,
addr1 OTHER_FIRM.addr1%TYPE,
addr2 OTHER_FIRM.addr2%TYPE,
city OTHER_FIRM.city%TYPE,
state OTHER_FIRM.state%TYPE,
zip OTHER_FIRM.zip%TYPE,
zip4 OTHER_FIRM.zip4%TYPE,
PHONE OTHER_FIRM.PHONE%TYPE,
fax   OTHER_FIRM.fax%TYPE);
TYPE OtherFirmListCursor IS REF CURSOR RETURN OtherFirmList_rec;
PROCEDURE getOtherFirmList(aLegalCaseNum IN NUMBER,
                           recordset OUT OtherFirmListCursor);
TYPE employee_rec IS RECORD(
    EMPLID adp_employee.emplid%TYPE,
    LAST_NAME adp_employee.last_name%TYPE,
    FIRST_NAME adp_employee.first_name%TYPE,
    MIDDLE_NAME adp_employee.middle_name%TYPE,
    EMAIL_ADDRESS adp_employee.EMAIL_ADDRESS%TYPE,
    EMPL_STATUS adp_employee.empl_status%TYPE,
    PHONE adp_employee.PHONE%TYPE,
    PHONE_EXTENSION adp_employee.phone_extension%TYPE,
    LOCATION adp_employee.LOCATION%TYPE,
    PREFERRED_NAME adp_employee.preferred_name%TYPE,
    DEPTID adp_employee.deptid%TYPE,
    DESCR adp_employee.descr%TYPE,
    REPORTS_TO adp_employee.reports_to%TYPE,
    USER_ID adp_employee.user_id%TYPE);
TYPE EmployeeCursor IS REF CURSOR RETURN Employee_rec;

PROCEDURE GetEmployeebyMailid(in_MailId IN VARCHAR,
                      recordset OUT EmployeeCursor);

FUNCTION GetEmployeeName(in_mailid IN VARCHAR)
  RETURN VARCHAR2;

PROCEDURE IsLetterRequested(aLegalCaseNum IN NUMBER,
                           aLetterType   IN VARCHAR2,
                           out_requested OUT VARCHAR2);

TYPE case_cost_rec IS RECORD(
    case_cost_seq_num_out  CASE_COST.case_cost_seq_num%TYPE,
    legal_case_seq_num_out CASE_COST.legal_case_seq_num%TYPE,
    service_from_date_out  CASE_COST.service_from_date%TYPE,
    service_to_date_out    CASE_COST.service_to_date%TYPE,
    service_code_out       CASE_COST.SERVICE_CODE%TYPE,
    amount_paid_out        CASE_COST.amount_paid%TYPE,
    amount_billed_out      CASE_COST.amount_billed%TYPE,
    amount_per_hour_out    CASE_COST.amount_per_hour%TYPE,
    hours_billed_out       CASE_COST.hours_billed%TYPE,
    prepayment_flag_out    VARCHAR2(5),
    authorized_date_out    CASE_COST.authorized_date%TYPE,
    payee_firm_prov_loc_id_out CASE_COST.payee_firm_prov_loc_id%TYPE,
    payee_person_prov_loc_id_out CASE_COST.payee_person_prov_loc_id%TYPE,
    authorizer_emplid_out  CASE_COST.authorizer_emplid%TYPE,
    cancelled_by_code_out  CASE_COST.CANCELLED_BY_CODE%TYPE,
    create_date            DATE,
    create_user         VARCHAR2(30),
    update_date         DATE,
    update_user         VARCHAR2(30));

TYPE CaseCostCursor IS REF CURSOR RETURN case_cost_rec;

PROCEDURE GetCaseCost(aLegalCaseNum IN  NUMBER,
                      recordset OUT CaseCostCursor);
/********************************************
** UPDATE PROCEDURE FOR TABLE "LEGAL.CASE_COST"
** DATE: 28-JUL-03 13:22:19
*********************************************/

PROCEDURE case_cost_update
(
    case_cost_seq_num_in IN CASE_COST.case_cost_seq_num%TYPE,
    legal_case_seq_num_in IN CASE_COST.legal_case_seq_num%TYPE,
    service_from_date_in IN CASE_COST.service_from_date%TYPE,
    service_to_date_in IN CASE_COST.service_to_date%TYPE,
    service_code_in IN CASE_COST.SERVICE_CODE%TYPE,
    amount_paid_in IN CASE_COST.amount_paid%TYPE,
    amount_billed_in IN CASE_COST.amount_billed%TYPE,
    amount_per_hour_in IN CASE_COST.amount_per_hour%TYPE,
    hours_billed_in IN    CASE_COST.hours_billed%TYPE,
    prepayment_flag_in IN CASE_COST.prepayment_flag%TYPE,
    authorized_date_in IN CASE_COST.authorized_date%TYPE,
    payee_firm_prov_loc_id_in IN CASE_COST.PAYEE_firm_PROV_LOC_ID%TYPE,
    payee_person_prov_loc_id_in IN CASE_COST.payee_person_prov_loc_id%TYPE,
    authorizer_emplid_in IN CASE_COST.authorizer_emplid%TYPE,
    cancelled_by_code_in IN CASE_COST.CANCELLED_BY_CODE%TYPE,
    update_date_in IN CASE_COST.update_date%TYPE,
    update_user_in IN CASE_COST.update_user%TYPE);

/********************************************
** INSERT PROCEDURE FOR TABLE "LEGAL.CASE_COST"
** DATE: 28-JUL-03 13:22:19
*********************************************/
PROCEDURE case_cost_insert
(
    case_cost_seq_num_in IN CASE_COST.case_cost_seq_num%TYPE,
    legal_case_seq_num_in IN CASE_COST.legal_case_seq_num%TYPE,
    service_from_date_in IN CASE_COST.service_from_date%TYPE,
    service_to_date_in IN CASE_COST.service_to_date%TYPE,
    service_code_in IN CASE_COST.SERVICE_CODE%TYPE,
    amount_paid_in IN CASE_COST.amount_paid%TYPE,
    amount_billed_in IN CASE_COST.amount_billed%TYPE,
    amount_per_hour_in IN CASE_COST.amount_per_hour%TYPE,
    hours_billed_in IN    CASE_COST.hours_billed%TYPE,
    prepayment_flag_in IN CASE_COST.prepayment_flag%TYPE,
    authorized_date_in IN CASE_COST.authorized_date%TYPE,
    payee_firm_prov_loc_id_in IN CASE_COST.PAYEE_firm_PROV_LOC_ID%TYPE,
    payee_person_prov_loc_id_in IN CASE_COST.payee_person_prov_loc_id%TYPE,
    authorizer_emplid_in IN CASE_COST.authorizer_emplid%TYPE,
    cancelled_by_code_in IN CASE_COST.CANCELLED_BY_CODE%TYPE,
    create_date_in IN CASE_COST.create_date%TYPE,
    create_user_in IN CASE_COST.create_user%TYPE);

/********************************************
** DELETE PROCEDURE FOR TABLE "LEGAL.CASE_COST"
** DATE: 02-DEC-03 16:12:45
*********************************************/
PROCEDURE case_cost_delete
(
    case_cost_seq_num_in IN CASE_COST.case_cost_seq_num%TYPE
);

Function DeleteLegalCase(LegalCaseSeqNum IN
LEGAL_CASE.legal_case_Seq_num%TYPE)
    RETURN NUMBER;


PROCEDURE GetCaseNote(CaseId IN  NUMBER,
                      recordset OUT ref_cursor);

/********************************************
** INSERT PROCEDURE FOR TABLE "LEGAL.CASE_NOTE"
** DATE: 06-AUG-03 10:20:33
*********************************************/
FUNCTION case_note_insert
(
    noteId          IN CASE_NOTE.case_note_seq_num%TYPE,
    caseId          IN CASE_NOTE.legal_case_seq_num%TYPE,
    categoryCode    IN CASE_NOTE.NOTE_CATEGORY_CODE%TYPE,
    Notetext        IN CASE_NOTE.text%TYPE,
    createDate      IN CASE_NOTE.create_date%TYPE,
    createUser      IN CASE_NOTE.create_user%TYPE)
   RETURN NUMBER;

/********************************************
** UPDATE PROCEDURE FOR TABLE "LEGAL.CASE_NOTE"
** DATE: 06-AUG-03 10:21:50
*********************************************/
Function case_note_update
(
    noteId       IN CASE_NOTE.case_note_seq_num%TYPE,
    caseId       IN CASE_NOTE.legal_case_seq_num%TYPE,
    categoryCode IN CASE_NOTE.NOTE_CATEGORY_CODE%TYPE,
    NoteText     IN CASE_NOTE.text%TYPE,
    updateDate   IN CASE_NOTE.update_date%TYPE,
    updateUser   IN CASE_NOTE.update_user%TYPE)
    RETURN NUMBER;

/********************************************
** DELETE PROCEDURE FOR TABLE "LEGAL.CASE_NOTE"
** DATE: 07-AUG-03 10:21:48
*********************************************/
Function case_note_delete
(
    noteId CASE_NOTE.case_note_seq_num%TYPE)
    RETURN NUMBER;

PROCEDURE GetSAIFAtty(aLegalCaseNum IN  NUMBER,
                      recordset OUT ref_cursor);

PROCEDURE GetCaseOutcome(LegalCaseID IN  NUMBER,
                      recordset OUT ref_Cursor);

TYPE outcomeIssue_rec IS RECORD
( outcome_issue_seq_num OUTCOME_ISSUE.outcome_issue_seq_num%TYPE,
  case_outcome_seq_num  OUTCOME_ISSUE.case_outcome_seq_num%TYPE,
  OUTCOME_ISSUE_CODE    OUTCOME_ISSUE.OUTCOME_ISSUE_CODE%TYPE,
  win_loss_flag         OUTCOME_ISSUE.win_loss_flag%TYPE,
  fee_amount            OUTCOME_ISSUE.fee_amount%TYPE,
  penalty_flag          NUMBER,
  primary_issue_flag    NUMBER,
  create_date           OUTCOME_ISSUE.create_date%TYPE,
  create_user           OUTCOME_ISSUE.create_user%TYPE,
  summary_text          OUTCOME_ISSUE.summary_text%TYPE,
  OUTCOME_GROUNDS_CODE  OUTCOME_ISSUE.OUTCOME_GROUNDS_CODE%TYPE,
  LEGAL_BODY_PART_CODE  OUTCOME_ISSUE.LEGAL_BODY_PART_CODE%TYPE,
  NATURE_CODE           OUTCOME_ISSUE.NATURE_CODE%TYPE,
  update_date           OUTCOME_ISSUE.update_date%TYPE,
  update_user           OUTCOME_ISSUE.update_user%TYPE
);
TYPE OutcomeIssueCursor IS REF CURSOR RETURN outcomeIssue_rec;
PROCEDURE GetOutcomeIssue(aLegalCaseNum IN  NUMBER,
                      recordset OUT OutcomeIssueCursor);

TYPE ALJList_rec IS RECORD
(  legal_person_seq_num LEGAL_PERSON.legal_person_seq_num%TYPE,
   last_name            LEGAL_PERSON.last_name%TYPE,
   LEGAL_PERSON_TYPE_CODE LEGAL_PERSON_TYPE.LEGAL_PERSON_TYPE_CODE%TYPE,
   code_descr             VARCHAR2(65),
   Selected               NUMBER );
TYPE ALJListCursor IS REF CURSOR RETURN ALJList_rec;

PROCEDURE getAljList(aLegalCaseNum IN  NUMBER,
                      recordset OUT ALJListCursor);

TYPE OutcomeStaff_rec IS RECORD
( outcome_staff_seq_num OUTCOME_STAFF.outcome_staff_seq_num%TYPE,
  case_outcome_seq_num  OUTCOME_STAFF.case_outcome_seq_num%TYPE,
  outcome_staff_person_seq_num OUTCOME_STAFF.outcome_staff_person_seq_num%TYPE,
  last_name  LEGAL_PERSON.last_name%TYPE
);
TYPE OutcomeStaffCursor IS REF CURSOR RETURN OutcomeStaff_rec;
PROCEDURE GetOutcomeStaff(aLegalCaseNum IN  NUMBER,
                      recordset OUT OutcomeStaffCursor);

Function updateCaseOutcome( CaseOutcomeID IN CASE_OUTCOME.case_outcome_seq_num%TYPE,
                             LegalCaseID IN CASE_OUTCOME.legal_case_seq_num%TYPE,
                             OrderTypeCode IN CASE_OUTCOME.ORDER_TYPE_CODE%TYPE,
                             OrderSignedDate IN CASE_OUTCOME.order_signed_date%TYPE := NULL,
                             OrderFiledDate IN CASE_OUTCOME.order_filed_date%TYPE := NULL,
                             UpdateUser IN CASE_OUTCOME.update_user%TYPE := NULL,
                             AsgnAttyEmplid CASE_OUTCOME.asgn_atty_emplid%TYPE := NULL,
                             AppealId CASE_OUTCOME.APPEAL_SEQ_NUM%TYPE := Null)
                             return number;

 Function insertCaseOutcome( CaseOutcomeID IN CASE_OUTCOME.case_outcome_seq_num%TYPE,
                             LegalCaseID IN CASE_OUTCOME.legal_case_seq_num%TYPE,
                             OrderTypeCode IN CASE_OUTCOME.ORDER_TYPE_CODE%TYPE,
                             OrderSignedDate IN CASE_OUTCOME.order_signed_date%TYPE := NULL,
                             OrderFiledDate IN CASE_OUTCOME.order_filed_date%TYPE := NULL,
                             UpdateUser IN CASE_OUTCOME.update_user%TYPE := NULL,
                             AsgnAttyEmplid CASE_OUTCOME.asgn_atty_emplid%TYPE := NULL,
                             AppealId CASE_OUTCOME.APPEAL_SEQ_NUM%TYPE := Null)
                             return number;

PROCEDURE updateOutcomeIssue(outcome_issue_seq_num_in IN OUTCOME_ISSUE.outcome_issue_seq_num%TYPE,
    case_outcome_seq_num_in IN OUTCOME_ISSUE.case_outcome_seq_num%TYPE,
    outcome_issue_code_in IN OUTCOME_ISSUE.OUTCOME_ISSUE_CODE%TYPE,
    win_loss_flag_in IN OUTCOME_ISSUE.win_loss_flag%TYPE,
    fee_amount_in IN OUTCOME_ISSUE.fee_amount%TYPE,
    penalty_flag_in IN OUTCOME_ISSUE.penalty_flag%TYPE,
    primary_issue_flag_in IN OUTCOME_ISSUE.primary_issue_flag%TYPE,
    summary_text_in IN OUTCOME_ISSUE.summary_text%TYPE,
    outcome_grounds_code_in IN OUTCOME_ISSUE.OUTCOME_GROUNDS_CODE%TYPE,
    body_part_code_in IN OUTCOME_ISSUE.LEGAL_BODY_PART_CODE%TYPE,
    nature_code_in IN OUTCOME_ISSUE.NATURE_CODE%TYPE,
    userid_in IN OUTCOME_ISSUE.update_user%TYPE);

PROCEDURE updateOutcomeStaff( outcome_staff_seq_num_in IN OUTCOME_STAFF.outcome_staff_seq_num%TYPE,
                              case_outcome_seq_num_in IN OUTCOME_STAFF.case_outcome_seq_num%TYPE,
                              staff_person_seq_num_in IN OUTCOME_STAFF.outcome_staff_person_seq_num%TYPE );

FUNCTION DeleteCaseOutcome(case_outcome_seq_num_in IN
                            CASE_OUTCOME.case_outcome_seq_num%TYPE) RETURN NUMBER;

PROCEDURE DeleteOutcomeIssue(outcome_issue_seq_num_in IN
                             OUTCOME_ISSUE.outcome_issue_seq_num%TYPE);

PROCEDURE DeleteOutcomeStaff(outcome_staff_seq_num_in IN
                             OUTCOME_STAFF.outcome_staff_seq_num%TYPE);

PROCEDURE SetFinalOutcome( legal_case_seq_num_in IN
                           LEGAL_CASE.legal_case_seq_num%TYPE);

TYPE LegalCaseEmployer_rec IS RECORD
( legal_case_employer_seq_num   NUMBER,
  legal_case_seq_num            NUMBER,
  EMPLOYER_OTHER_FIRM_SEQ_NUM   NUMBER,
  ATTORNEY_OTHER_FIRM_SEQ_NUM   NUMBER,
  ATTORNEY_LEGAL_PERSON_SEQ_NUM NUMBER,
  EMPLOYER_DISCOVERY_FLAG       VARCHAR2(1),
  ATTORNEY_DISCOVERY_FLAG       VARCHAR2(1));

TYPE LegalCaseEmployerCursor IS REF CURSOR RETURN LegalCaseEmployer_rec;

PROCEDURE GetLegalCaseEmployer(aLegalCaseNum IN  NUMBER,
                      recordset OUT LegalCaseEmployerCursor);

/********************************************
** INSERT PROCEDURE FOR TABLE "LEGAL.LEGAL_CASE_EMPLOYER"
** DATE: 03-SEP-03 08:41:55
*********************************************/
PROCEDURE legal_case_employer_insert
(
    legal_case_employer_seq_num_in IN LEGAL_CASE_EMPLOYER.legal_case_employer_seq_num%TYPE,
    legal_case_seq_num_in IN LEGAL_CASE_EMPLOYER.legal_case_seq_num%TYPE,
    other_firm_seq_num_in IN LEGAL_CASE_EMPLOYER.employer_other_firm_seq_num%TYPE,
    discovery_flag_in IN LEGAL_CASE_EMPLOYER.employer_discovery_flag%TYPE,
    attorney_discovery_flag_in IN LEGAL_CASE_EMPLOYER.attorney_discovery_flag%TYPE,
    atty_other_firm_seq_num_in IN LEGAL_CASE_EMPLOYER.attorney_other_firm_seq_num%TYPE,
    atty_legal_person_seq_num_in IN LEGAL_CASE_EMPLOYER.attorney_legal_person_seq_num%TYPE );
/********************************************
** UPDATE PROCEDURE FOR TABLE "LEGAL.LEGAL_CASE_EMPLOYER"
** DATE: 03-SEP-03 08:43:30
*********************************************/
PROCEDURE legal_case_employer_update
(
    legal_case_employer_seq_num_in IN LEGAL_CASE_EMPLOYER.legal_case_employer_seq_num%TYPE,
    legal_case_seq_num_in IN LEGAL_CASE_EMPLOYER.legal_case_seq_num%TYPE,
    other_firm_seq_num_in IN LEGAL_CASE_EMPLOYER.employer_other_firm_seq_num%TYPE,
    discovery_flag_in IN LEGAL_CASE_EMPLOYER.employer_discovery_flag%TYPE,
    attorney_discovery_flag_in IN LEGAL_CASE_EMPLOYER.attorney_discovery_flag%TYPE,
    atty_other_firm_seq_num_in IN LEGAL_CASE_EMPLOYER.attorney_other_firm_seq_num%TYPE,
    atty_legal_person_seq_num_in IN LEGAL_CASE_EMPLOYER.attorney_legal_person_seq_num%TYPE
);

PROCEDURE GetWorkerAtty(recordset OUT ref_cursor, inFirmLocID IN NUMBER, inAttyLocID IN NUMBER);

TYPE AttyLeave_rec IS RECORD
(schedule_emplid ATTORNEY_SCHEDULE.schedule_emplid%TYPE,
 atty_name       VARCHAR2(80),
 vac_days        NUMBER,
 med_days          NUMBER);
TYPE AttyLeaveCursor IS REF CURSOR RETURN AttyLeave_rec;

PROCEDURE GetAttyLeaveReport(inStartDate IN DATE,
                             inStopDate IN DATE,
                             recordset OUT AttyLeaveCursor);

PROCEDURE alertlegalcase(inClaimId IN NUMBER, OutAlertFlag OUT VARCHAR2 ) ;

PROCEDURE SelectImageAlertsHist(inCaseSeqNum IN NUMBER, recordset OUT ref_cursor);

PROCEDURE SelectLegalEmployees(recordset OUT ref_cursor);

PROCEDURE getPolicyAddresses(inPolicyNum IN NUMBER,
       recordset OUT ref_cursor);
END;

PL/SQL package/procedure body

PACKAGE BODY                   Pk80001_Legalnet AS

/*****************************************************************
*Package Title  :
*Name           : pk80001_legalnet
*****************************************************************
*Description:
*   find a legal case based on claim, wcb or dcbs number.
*
*PROGRAMMER     ANALYST                 DATE       MOD #
* Melhil                             03/11/2010     Remove legacy log, get supervisor from ADP not claim_supervisor table
* Tony Busch                        07/27/2005     Change PROVIDER_LOCATION_ID to PROVIDER_LOCATION_SEQ_NUM.
* Melhil                            01/06/2008     Add amount per hour to case cost
*******************************************************************/

  PROCEDURE findCase(recordset OUT ref_cursor, in_num IN VARCHAR2)
    AS
       v_count NUMBER;
    BEGIN
       SELECT COUNT(*) INTO v_count
       FROM LEGAL_CASE
       WHERE wcb_num = in_num;
    IF v_count > 0 THEN
       OPEN recordset FOR
        SELECT
        DISTINCT l.legal_case_seq_num LegalCaseSeqNum,
        l.wcb_num as WcbNum,
        l.claim_id claimId,
         l.dcbs_num dcbsNum,
           l.owcp_num  owcpNum,
       l.oalj_num  oaljNum,
       brb_num brbNum,
       s.code_descr AS caseStatus,
       t.code_descr AS caseTypeCode
       FROM LEGAL_CASE l,
            CASE_STATUS_CODE s, CASE_TYPE_CODE t
       WHERE l.wcb_num = in_num
         AND l.CASE_STATUS_CODE = s.code
         and L.CASE_TYPE_CODE = T.CODE;
  ELSE
    IF LENGTH(in_num) <= 7 THEN
       SELECT COUNT(*) INTO v_count
       FROM LEGAL_CASE
       WHERE dcbs_num = in_num;
       IF v_count > 0 THEN
        OPEN recordset FOR
        SELECT
           DISTINCT  l.legal_case_seq_num LegalCaseSeqNum,
        l.wcb_num as WcbNum,
        l.claim_id claimId,
         l.dcbs_num dcbsNum,
           l.owcp_num  owcpNum,
       l.oalj_num  oaljNum,
       brb_num brbNum,
       s.code_descr AS caseStatus,
       t.code_descr AS caseTypeCode
        FROM LEGAL_CASE l,
            CASE_STATUS_CODE s, CASE_TYPE_CODE t
        WHERE l.dcbs_num = in_num
          AND l.CASE_STATUS_CODE = s.code
           and L.CASE_TYPE_CODE = T.CODE;
   ELSE
      SELECT COUNT(*) INTO v_count
       FROM LEGAL_CASE
       WHERE owcp_num = in_num;
       IF v_count > 0 THEN
          OPEN recordset FOR
        SELECT
           DISTINCT  l.legal_case_seq_num LegalCaseSeqNum,
        l.wcb_num as WcbNum,
        l.claim_id claimId,
         l.dcbs_num dcbsNum,
           l.owcp_num  owcpNum,
       l.oalj_num  oaljNum,
       brb_num brbNum,
       s.code_descr AS caseStatus,
       t.code_descr AS caseTypeCode
        FROM LEGAL_CASE l,
            CASE_STATUS_CODE s, CASE_TYPE_CODE t
        WHERE (l.owcp_num = in_num)
          AND l.CASE_STATUS_CODE = s.code
           and L.CASE_TYPE_CODE = T.CODE;
    ELSE
       SELECT COUNT(*) INTO v_count
       FROM LEGAL_CASE
       WHERE oalj_num = in_num;
       IF v_count > 0 THEN
        OPEN recordset FOR
        SELECT
           DISTINCT l.legal_case_seq_num LegalCaseSeqNum,
        l.wcb_num as WcbNum,
        l.claim_id claimId,
         l.dcbs_num dcbsNum,
           l.owcp_num  owcpNum,
       l.oalj_num  oaljNum,
       brb_num brbNum,
       s.code_descr AS caseStatus,
       t.code_descr AS caseTypeCode
        FROM LEGAL_CASE l,
            CASE_STATUS_CODE s, CASE_TYPE_CODE t
       WHERE oalj_num = in_num
         AND l.CASE_STATUS_CODE = s.code
          and L.CASE_TYPE_CODE = T.CODE;
    ELSE
      SELECT COUNT(*) INTO v_count
       FROM LEGAL_CASE
       WHERE brb_num = in_num;
    IF v_count > 0 THEN
        OPEN recordset FOR
        SELECT
           DISTINCT  l.legal_case_seq_num LegalCaseSeqNum,
        l.wcb_num as WcbNum,
        l.claim_id claimId,
         l.dcbs_num dcbsNum,
           l.owcp_num  owcpNum,
       l.oalj_num  oaljNum,
       brb_num brbNum,
       s.code_descr AS caseStatus,
       t.code_descr AS caseTypeCode
        FROM LEGAL_CASE l,
            CASE_STATUS_CODE s, CASE_TYPE_CODE t
       WHERE l.brb_num = in_num
          AND l.CASE_STATUS_CODE = s.code
           and L.CASE_TYPE_CODE = T.CODE;
    ELSE
        OPEN recordset FOR
        SELECT
           DISTINCT  l.legal_case_seq_num LegalCaseSeqNum,
        l.wcb_num as WcbNum,
        l.claim_id claimId,
         l.dcbs_num dcbsNum,
           l.owcp_num  owcpNum,
       l.oalj_num  oaljNum,
       brb_num brbNum,
       s.code_descr AS caseStatus,
       t.code_descr AS caseTypeCode
        FROM LEGAL_CASE l,
             CASE_STATUS_CODE s, APPEAL A, CASE_TYPE_CODE t
       WHERE A.appeal_num = in_num
          AND l.CASE_STATUS_CODE = s.code
          AND l.legal_case_seq_num = A.legal_case_seq_num
           and L.CASE_TYPE_CODE = T.CODE;
       END IF;
       END IF;
       END IF;
       END IF;
    END IF;
  END IF;

 END findcase;

FUNCTION get_hearing_date (in_case_seq IN LEGAL_CASE.legal_case_seq_num%TYPE)
  RETURN DATE AS
         v_hear_date DATE;
BEGIN
BEGIN
   SELECT MAX(sch.schedule_date)
     INTO v_hear_date
     FROM LEGAL_CASE cas,
          SCHEDULE sch,
          CONSOLIDATED_CASE c
   WHERE cas.LEGAL_CASE_SEQ_NUM = in_case_seq
    AND sch.schedule_seq_num =
             (SELECT MAX(schedule_seq_num)
              FROM SCHEDULE sch2, CONSOLIDATED_CASE c2
             WHERE ((sch2.LEGAL_CASE_SEQ_NUM = cas.LEGAL_CASE_SEQ_NUM)
                OR (c2.secondary_case_seq_num = in_case_seq
               AND sch2.legal_case_seq_num = c2.primary_case_seq_num))
               AND sch2.SCHEDULE_TYPE_CODE = 'HEAR');
EXCEPTION
    WHEN NO_DATA_FOUND THEN
       NULL;
   END;

 RETURN v_hear_Date;
END get_hearing_date;

FUNCTION get_consolidated_hearing_seq (in_case_seq IN LEGAL_CASE.legal_case_seq_num%TYPE)
   RETURN NUMBER AS
   v_hear_seq NUMBER(15,0);
 BEGIN
   BEGIN
       SELECT cas.most_recent_hearing_seq_num
         INTO v_hear_seq
       FROM LEGAL_CASE cas,
            CONSOLIDATED_CASE con
      WHERE con.secondary_case_seq_num = in_case_seq
        AND con.primary_case_seq_num = cas.legal_case_seq_num;
        EXCEPTION
    WHEN NO_DATA_FOUND THEN
         NULL;
    END;
    RETURN v_hear_seq;

END get_consolidated_hearing_seq;


FUNCTION get_hearingID(in_case_seq IN LEGAL_CASE.legal_case_seq_num%TYPE)
  RETURN NUMBER AS
    v_hear_seq NUMBER(15,0);
BEGIN
BEGIN
   SELECT MAX(sch.SCHEDULE_SEQ_NUM)
     INTO v_hear_seq
     FROM LEGAL_CASE cas,
          SCHEDULE sch
    WHERE cas.LEGAL_CASE_SEQ_NUM = in_case_seq
      AND sch.schedule_seq_num =
             (SELECT MAX(schedule_seq_num)
              FROM SCHEDULE sch2, CONSOLIDATED_CASE c2
             WHERE ((sch2.LEGAL_CASE_SEQ_NUM = cas.LEGAL_CASE_SEQ_NUM)
                OR (c2.secondary_case_seq_num = in_case_seq
               AND sch2.legal_case_seq_num = c2.primary_case_seq_num))
               AND sch2.SCHEDULE_TYPE_CODE = 'HEAR');
EXCEPTION
    WHEN NO_DATA_FOUND THEN
    NULL;
END;
IF v_hear_seq IS NULL THEN
   BEGIN
    SELECT MAX(sch.SCHEDULE_SEQ_NUM)
     INTO v_hear_seq
     FROM LEGAL_CASE cas,
          SCHEDULE sch
   WHERE cas.LEGAL_CASE_SEQ_NUM = in_case_seq
     AND sch.LEGAL_CASE_SEQ_NUM = CAS.LEGAL_CASE_SEQ_NUM
     AND sch.SCHEDULE_TYPE_CODE = 'HEAR';
   EXCEPTION
     WHEN NO_DATA_FOUND THEN
          NULL;
    END;
 END IF;

 RETURN v_hear_seq;
END get_hearingID;



  FUNCTION get_asgn_atty(IN_case_Seq_num IN LEGAL_CASE.legal_case_seq_num%TYPE)
         RETURN VARCHAR2 AS
         v_atty VARCHAR2(50);
   BEGIN
    BEGIN
    SELECT LAST_NAME || ', ' || DECODE(preferred_name, ' ', FIRST_NAME,preferred_name)
      INTO v_atty
         FROM LEGAL_CASE cas,
               APPEAL app,
               adp_employee emp
         WHERE cas.legal_case_seq_num = in_case_Seq_num
            AND app.legal_case_seq_num = cas.legal_case_seq_num
            AND app.appeal_seq_num =
                (SELECT MAX(appeal_seq_num)
                   FROM APPEAL a2
                  WHERE a2.legal_case_Seq_num = cas.legal_case_seq_num)
             AND emp.emplid = app.atty_emplid;
     EXCEPTION
      WHEN NO_DATA_FOUND THEN
      BEGIN
    SELECT LAST_NAME || ', ' || DECODE(preferred_name, ' ', FIRST_NAME,preferred_name)
          INTO v_atty
          FROM LEGAL_CASE cas,
               SCHEDULE sch,
               adp_employee emp
         WHERE cas.legal_case_seq_num = in_case_Seq_num
           AND emp.emplid = sch.asgn_attorney_emplid
           AND cas.most_recent_hearing_seq_num = sch.schedule_seq_num;
  EXCEPTION
      WHEN NO_DATA_FOUND THEN
      BEGIN
      SELECT FIRM_NAME INTO v_atty
        FROM LEGAL_CASE cas,
               SCHEDULE sch,
               OTHER_FIRM frm
         WHERE cas.legal_case_seq_num = in_case_Seq_num
           AND frm.other_firm_seq_num = sch.asgn_other_firm_Seq_num
            AND cas.most_recent_hearing_seq_num = sch.schedule_seq_num;

      EXCEPTION
          WHEN NO_DATA_FOUND THEN
               v_atty := '';
      END;
     END;
    END;
  RETURN v_atty;
END get_asgn_atty;

FUNCTION get_asgn_atty_id(IN_case_Seq_num IN
LEGAL_CASE.legal_case_seq_num%TYPE) RETURN VARCHAR2 AS
         v_atty_id ATTORNEY_PROFILE.profile_emplid%TYPE;
   BEGIN
    BEGIN
    SELECT emp.emplid INTO v_atty_id
         FROM LEGAL_CASE cas,
               APPEAL app,
               adp_employee emp
         WHERE cas.legal_case_seq_num = in_case_Seq_num
            AND app.legal_case_seq_num = cas.legal_case_seq_num
            AND app.appeal_seq_num =
                (SELECT MAX(appeal_seq_num)
                   FROM APPEAL a2
                  WHERE a2.legal_case_Seq_num = cas.legal_case_seq_num)
             AND emp.emplid = app.atty_emplid;
     EXCEPTION
      WHEN NO_DATA_FOUND THEN
      BEGIN
    SELECT emp.emplid INTO v_atty_id
          FROM LEGAL_CASE cas,
               SCHEDULE sch,
               adp_employee emp
         WHERE cas.legal_case_seq_num = in_case_Seq_num
           AND emp.emplid = sch.asgn_attorney_emplid
          AND cas.most_recent_hearing_seq_num = sch.schedule_seq_num;

      EXCEPTION
      WHEN NO_DATA_FOUND THEN
      BEGIN
      SELECT 'OUTSIDECNCL' INTO v_atty_ID
        FROM LEGAL_CASE cas,
               SCHEDULE sch
        WHERE cas.legal_case_seq_num = in_case_Seq_num
         AND sch.asgn_other_firm_Seq_num IS NOT NULL
         AND cas.most_recent_hearing_seq_num = sch.schedule_seq_num;
      EXCEPTION
          WHEN NO_DATA_FOUND THEN
                          v_atty_id := '';
      END;
     END;
    END;
  RETURN v_atty_id;
END get_asgn_atty_id;

FUNCTION get_atty_mngr_email(aLegalCaseNum IN NUMBER)
 RETURN VARCHAR2 AS
       v_email VARCHAR2(50);
   BEGIN
    BEGIN
        SELECT EMAIL_ADDRESS INTO v_email
          FROM LEGAL_CASE cas,
               SCHEDULE sch,
               adp_employee emp,
               ATTORNEY_PROFILE P
         WHERE cas.legal_case_seq_num = aLegalCaseNum
           AND cas.most_recent_hearing_seq_num = sch.schedule_seq_num
           AND P.profile_emplid = sch.asgn_attorney_emplid
           AND P.manager_emplid = emp.emplid;
  EXCEPTION
      WHEN NO_DATA_FOUND THEN
         BEGIN
        SELECT EMAIL_ADDRESS INTO v_email
          FROM LEGAL_CASE cas,
               SCHEDULE sch,
               adp_employee emp,
               ATTORNEY_PROFILE P
         WHERE cas.legal_case_seq_num = aLegalCaseNum
           AND cas.most_recent_hearing_seq_num = sch.schedule_seq_num
           AND P.profile_emplid = 'OUTSIDECNCL'
           AND sch.asgn_other_firm_seq_num IS NOT NULL
           AND P.manager_emplid = emp.emplid;
     EXCEPTION
        WHEN NO_DATA_FOUND THEN
          v_email := 'NONE';
     END;
  END;
RETURN v_email;

 END get_atty_mngr_email;


FUNCTION get_mailid_for_emplid(inEmplid IN VARCHAR2) RETURN VARCHAR2
AS
      v_mailid VARCHAR2(50);
   BEGIN
    BEGIN
        SELECT user_id INTO v_mailid
          FROM adp_employee
         WHERE emplid = inEmplid;
  EXCEPTION
      WHEN NO_DATA_FOUND THEN
          v_mailid := '';
  END;
  RETURN v_mailid;
END get_mailid_for_emplid;

 FUNCTION get_case_num(aLegalCaseNum IN NUMBER)
 RETURN VARCHAR2 AS
       v_num VARCHAR2(15) := ' ';
   BEGIN
    BEGIN
    SELECT wcb_num INTO v_num
      FROM LEGAL_CASE
     WHERE legal_case_seq_num = aLegalCaseNum
       AND wcb_num IS NOT NULL
 UNION
    SELECT dcbs_num
      FROM LEGAL_CASE
     WHERE legal_case_seq_num = aLegalCaseNum
       AND wcb_num IS NULL
       AND dcbs_num IS NOT NULL
 UNION
    SELECT owcp_num
      FROM LEGAL_CASE
     WHERE legal_case_seq_num = aLegalCaseNum
       AND wcb_num IS NULL
       AND dcbs_num IS NULL
       AND owcp_num IS NOT NULL
 UNION
    SELECT oalj_num
      FROM LEGAL_CASE
     WHERE legal_case_seq_num = aLegalCaseNum
       AND wcb_num IS NULL
       AND dcbs_num IS NULL
       AND owcp_num IS NULL
       AND oalj_num IS NOT NULL
 UNION
    SELECT brb_num
      FROM LEGAL_CASE
     WHERE legal_case_seq_num = aLegalCaseNum
       AND wcb_num IS NULL
       AND dcbs_num IS NULL
       AND owcp_num IS NULL
       AND oalj_num IS NULL
       AND brb_num IS NOT NULL;
  EXCEPTION
      WHEN NO_DATA_FOUND THEN
          v_num := ' ';
  END;
  RETURN v_num;

 END get_case_num;

FUNCTION get_case_issue_codes (in_case_seq_num
NUMBER) RETURN VARCHAR2 AS
  v_dummy VARCHAR2(100) := '';
  CURSOR get_issues_cur IS
      SELECT FORM_REQUEST_CODE
        FROM REQUEST_ISSUE_CODE C, REQUEST_ISSUE I
       WHERE I.REQUEST_ISSUE_CODE = c.code
         AND I.legal_case_seq_num = in_case_seq_num
      ORDER BY FORM_REQUEST_CODE;
  issue_rec  get_issues_cur%ROWTYPE;
BEGIN
    OPEN get_issues_cur;
    FETCH get_issues_cur INTO  issue_rec;

    WHILE get_issues_cur%FOUND
    LOOP
        v_dummy := v_dummy || issue_rec.form_request_code || ' ';
        FETCH get_issues_cur INTO  issue_rec;
    END LOOP;

    CLOSE get_issues_cur;
    RETURN v_dummy;
END;

FUNCTION get_case_issue_text (in_case_seq_num
NUMBER) RETURN VARCHAR2 AS
  v_dummy VARCHAR2(200) := '';
  CURSOR get_issues_cur IS
      SELECT trim(SHORT_DESCR) AS code_descr
        FROM REQUEST_ISSUE_CODE C, REQUEST_ISSUE I
       WHERE I.REQUEST_ISSUE_CODE = c.code
         AND I.legal_case_seq_num = in_case_seq_num
      ORDER BY CODE_DESCR;
  issue_rec  get_issues_cur%ROWTYPE;
BEGIN
    OPEN get_issues_cur;
    FETCH get_issues_cur INTO  issue_rec;

    WHILE get_issues_cur%FOUND
    LOOP
        v_dummy := v_dummy || issue_rec.code_descr || ' ';
        FETCH get_issues_cur INTO  issue_rec;
    END LOOP;

    CLOSE get_issues_cur;
    RETURN v_dummy;
END;


PROCEDURE Casesearch(recordset OUT search_cursor,
                        claimIdsCommaDelimited IN VARCHAR2,
                        includeClosed IN VARCHAR2) AS
       v_status_code VARCHAR2(5);
       V_EMPR_NUM NUMBER;

    BEGIN
       IF includeClosed  = 'Y' THEN
          v_status_code := 'NONE';
       ELSE
          v_status_code := 'CLOSE';
       END IF;

    OPEN recordset FOR
       SELECT /*+ FIRST_ROWS */
        get_case_num(cas.legal_case_seq_num) AS wcb_num,
        sts.code_descr AS case_status,
        get_asgn_atty(cas.Legal_Case_seq_Num) AS atty_name,
        TO_CHAR(schedule_date,'MM/DD/YYYY') AS hearing_date,
        get_case_issue_codes(cas.legal_case_seq_num) AS REQUEST_ISSUE,
        req.code_descr AS requested_by,
        cas.legal_case_seq_num,
        CAS.claim_id
       FROM LEGAL_CASE cas,
            SCHEDULE s,
            CASE_STATUS_CODE sts,
            REQUESTED_BY_CODE req
     WHERE
       cas.claim_id IN (
               -- https://blogs.oracle.com/sql/post/split-comma-separated-values-into-rows-in-oracle-database
               with claimsString as (
                  select claimIdsCommaDelimited str from dual
               )
               select regexp_substr (claimIdsCommaDelimited, '[^,]+', 1, level) value
               from   claimsString
               connect by level <=
               length ( claimIdsCommaDelimited ) - length ( replace ( claimIdsCommaDelimited, ',' ) ) + 1
            )
       AND cas.most_recent_hearing_seq_num = s.schedule_seq_num (+)
       AND cas.CASE_STATUS_CODE = sts.code
       AND cas.REQUESTED_BY_CODE = req.code
       AND cas.CASE_STATUS_CODE <> v_status_code
       ORDER BY 1;
END Casesearch;

 PROCEDURE updateCaseStatus(aLegalCaseNum IN NUMBER, newStatus IN VARCHAR2,
            updateDate IN DATE, updateUser IN VARCHAR2)
        AS BEGIN
        UPDATE LEGAL_CASE
           SET CASE_STATUS_CODE = newStatus,
               update_date = updateDate,
               update_user = updateUser
         WHERE legal_case_seq_num = aLegalCaseNum;

          -- Update consolidated Cases too  ---
        UPDATE LEGAL_CASE
           SET CASE_STATUS_CODE = newStatus,
                   update_date = updateDate,
                   update_user = updateUser
         WHERE legal_case_seq_num IN
             (SELECT secondary_case_seq_num
                FROM CONSOLIDATED_CASE
               WHERE primary_case_seq_num =  aLegalCaseNum);

 END updateCaseStatus;

 PROCEDURE updateHearingSeqNum(aLegalCaseNum IN NUMBER)
  AS          v_hear_seq NUMBER(15,0);
   BEGIN
   BEGIN
    SELECT MAX(schedule_seq_num)
      INTO v_hear_seq
      FROM SCHEDULE s, CONSOLIDATED_CASE c
             WHERE c.secondary_case_seq_num = aLegalCaseNum
               AND s.legal_case_seq_num = c.primary_case_seq_num
               AND s.SCHEDULE_TYPE_CODE = 'HEAR';
  EXCEPTION
       WHEN NO_DATA_FOUND THEN
       NULL;
  END;
  IF v_hear_seq IS NULL THEN
      BEGIN
     SELECT MAX(schedule_seq_num)
       INTO v_hear_seq
       FROM SCHEDULE s
      WHERE s.legal_case_seq_num = aLegalCaseNum
        AND s.SCHEDULE_TYPE_CODE = 'HEAR';
     EXCEPTION
          WHEN NO_DATA_FOUND THEN
          NULL;
      END;
  END IF;
   IF v_hear_seq IS NULL THEN
      BEGIN
     SELECT MAX(schedule_seq_num)
       INTO v_hear_seq
       FROM SCHEDULE s
      WHERE s.legal_case_seq_num = aLegalCaseNum
        AND (s.SCHEDULE_TYPE_CODE = 'CCALL'
        OR s.SCHEDULE_TYPE_CODE = 'INCON'
        OR s.SCHEDULE_TYPE_CODE = 'MED');
     EXCEPTION
          WHEN NO_DATA_FOUND THEN
          NULL;
      END;
  END IF;


  UPDATE LEGAL_CASE
     SET most_recent_hearing_seq_num = v_hear_seq
    WHERE legal_case_seq_num = aLegalCaseNum;

  -- Update consolidated Cases too  ---
  UPDATE LEGAL_CASE
     SET most_recent_hearing_seq_num = v_hear_seq
   WHERE legal_case_seq_num IN
         (SELECT secondary_case_seq_num
            FROM CONSOLIDATED_CASE
           WHERE primary_case_seq_num =  aLegalCaseNum);


 END updateHearingSeqNum;

  PROCEDURE getLCE(LegalCaseID IN NUMBER,
       Recordset OUT ref_cursor)
    AS BEGIN OPEN recordset FOR
       SELECT LEGAL_CASE_EVALUATION_SEQ_NUM EvaluationID,
       LEGAL_CASE_SEQ_NUM LegalCaseID,
       LCE_EXCUSED_FLAG IsExcused,
       LCE_DUE_DATE DueDate, LCE_FILED_DATE FiledDate,
       ADJUSTER_EMPLID AdjusterEmployeeID, CASE_TRANSFER_DATE CaseTransferDate,
       CREATE_DATE CreateDate, CREATE_USER CreateUser,
       UPDATE_DATE UpdateDate, UPDATE_USER UpdateUser,
       ASGN_ATTY_EMPLID AsgnAttyEmplid
       FROM LEGAL_CASE_EVALUATION
       WHERE LEGAL_CASE_SEQ_NUM = LegalCaseID
       ORDER BY legal_case_evaluation_seq_num DESC;

    END getLCE;

    Function updateLCE(EvaluationID IN NUMBER, IsExcused IN VARCHAR2, dueDate IN DATE,
              filedDate IN DATE, AdjusterEmployeeID IN VARCHAR2, casetransferDate IN DATE,
              updateDate IN DATE, updateUser IN VARCHAR2,
              AsgnAttyEmplid IN VARCHAR2) return number
       AS BEGIN

       UPDATE LEGAL_CASE_EVALUATION
                 SET lce_excused_flag = IsExcused,
                     lce_due_date = dueDate,
                     lce_filed_date = filedDate,
                     adjuster_emplid = AdjusterEmployeeID,
                     case_transfer_date = caseTransferDate,
                     update_date = updateDate,
                     update_user = updateUser,
                     asgn_atty_emplid = AsgnAttyEmplid
               WHERE legal_case_evaluation_seq_num = EvaluationID;
               return sql%rowcount;
       END updateLCE;

 Function insertLCE(EvaluationID IN NUMBER, LegalCaseID in number,
              IsExcused IN VARCHAR2, dueDate IN DATE,
              filedDate IN DATE, AdjusterEmployeeID IN VARCHAR2, casetransferDate IN DATE,
              createDate IN DATE, createUser IN VARCHAR2,
              AsgnAttyEmplid IN VARCHAR2)
              return number
       AS BEGIN
           INSERT INTO LEGAL_CASE_EVALUATION
      (legal_case_evaluation_seq_num, legal_case_seq_num,
       lce_excused_flag,    lce_due_date,
       lce_filed_date, adjuster_emplid,
       case_transfer_date, create_date, create_user, asgn_atty_emplid)
    VALUES
      (EvaluationID, LegalCaseID, Isexcused, dueDate, filedDate,
       AdjusterEmployeeID, casetransferDate, createDate, createUser,
       AsgnAttyEmplid);
     return sql%rowcount;
    EXCEPTION
         WHEN OTHERS THEN
           RAISE_APPLICATION_ERROR(-20004,
           'Error in insertLCE: '||SQLERRM);
    END insertLCE;

   PROCEDURE getSchedule(LegalCaseSeqNum IN NUMBER,
       recordset OUT ref_cursor) AS
     BEGIN
      OPEN Recordset FOR
      SELECT schedule_seq_num as ScheduleSeqNum,
               legal_case_seq_num as LegalCaseSeqNum,
               schedule_date as ScheduleDate,
               schedule_time as ScheduleTime,
               alj_person_seq_num as AljPersonSeqNum,
               hearing_location_Seq_num as HearingLocationSeqNum,
               asgn_attorney_emplid as AsgnAttorneyEmplid,
               DURATION_CODE as DurationCode,
               SCHEDULE_TYPE_CODE as ScheduleTypeCode,
               SCHEDULE_STATUS_CODE as ScheduleStatusCode,
               KEEP_ATTORNEY_FLAG as KeepAttorneyFlag,
               assigned_date as AssignedDate,
               reminder_date as ReminderDate,
               schedule_text as ScheduleText,
               create_user as CreateUser,
               create_date as CreateDate,
               update_user as UpdateUser,
               update_date as UpdateDate,
               asgn_other_firm_seq_num as AsgnOtherFirmSeqNum,
               asgn_person_seq_num as AsgnPersonSeqNum
          FROM SCHEDULE
         WHERE legal_case_seq_num = LegalCaseSeqNum
       UNION
       SELECT schedule_seq_num,
               legal_case_seq_num,
               schedule_date,
               schedule_time,
               alj_person_seq_num,
               hearing_location_Seq_num,
               asgn_attorney_emplid,
               DURATION_CODE,
               SCHEDULE_TYPE_CODE,
               SCHEDULE_STATUS_CODE,
               keep_attorney_flag,
               assigned_date,
               reminder_date,
               schedule_text,
               s.create_user,
               s.create_date,
               s.update_user,
               s.update_date,
               asgn_other_firm_seq_num,
               asgn_person_seq_num
          FROM SCHEDULE s, CONSOLIDATED_CASE c
         WHERE c.secondary_case_seq_num = LegalCaseSeqNum
           AND c.primary_case_seq_num = s.legal_case_seq_num
         ORDER BY ScheduleSeqNum;

      END getSchedule;

   Function updateSchedule(ScheduleSeqNum IN NUMBER,
    ScheduleDate IN DATE, ScheduleTime IN VARCHAR2, AljPersonSeqNum IN NUMBER,
    HearingLocationSeqNum IN NUMBER, AsgnAttorneyEmplid IN VARCHAR2, DurationCode IN VARCHAR2,
    ScheduleTypeCode IN VARCHAR2, ScheduleStatusCode IN VARCHAR2, KeepAttorneyFlag IN VARCHAR2,
    AssignedDate IN DATE, ReminderDate IN DATE, ScheduleText IN VARCHAR2,
    UpdateDate IN DATE, UpdateUser IN VARCHAR2,
    AsgnOtherFirmSeqNum IN NUMBER, AsgnPersonSeqNum IN NUMBER)
  RETURN NUMBER
    IS
    v_update_count             NUMBER;
    BEGIN
       UPDATE SCHEDULE
                 SET schedule_date = ScheduleDate,
                     schedule_time = ScheduleTime,
                     alj_person_Seq_num = decode(AljPersonSeqNum, 0, null, AljPersonSeqNum),
                     hearing_location_seq_num = decode(HearingLocationSeqNum, 0, null, HearingLocationSeqNum),
                     asgn_attorney_emplid = AsgnAttorneyEmplid,
                     DURATION_CODE = durationCode,
                     SCHEDULE_TYPE_CODE = ScheduleTypeCode,
                     SCHEDULE_STATUS_CODE = ScheduleStatusCode,
                     keep_attorney_flag = KeepAttorneyFlag,
                     assigned_date = AssignedDate,
                     reminder_date = reminderDate,
                     schedule_text = ScheduleText,
                     update_date = updateDate,
                     update_user = updateUser,
                     asgn_Other_Firm_Seq_Num = asgnOtherFirmSeqNum,
                     asgn_Person_Seq_Num = asgnPersonSeqNum
               WHERE schedule_seq_num = ScheduleSeqNum;
           v_update_count := SQL%Rowcount;
    RETURN v_update_count;
    END updateSchedule;

Function insertSchedule(ScheduleSeqNum IN NUMBER, LegalCaseSeqNum IN NUMBER,
      ScheduleDate IN DATE, ScheduleTime IN VARCHAR2, AljPersonSeqNum IN NUMBER,
      HearingLocationSeqNum IN NUMBER, AsgnAttorneyEmplid IN VARCHAR2, DurationCode IN VARCHAR2,
      ScheduleTypeCode IN VARCHAR2, ScheduleStatusCode IN VARCHAR2, KeepAttorneyFlag IN VARCHAR2,
      AssignedDate IN DATE, ReminderDate IN DATE, ScheduleText IN VARCHAR2,
      createDate IN DATE, createUser IN VARCHAR2,
      asgnOtherFirmSeqNum IN NUMBER, asgnPersonSeqNum IN NUMBER)
  RETURN NUMBER
    IS
    v_update_count             NUMBER;
    BEGIN
         INSERT INTO SCHEDULE
      (schedule_seq_num, legal_case_seq_num,
       schedule_date, schedule_time,
       alj_person_Seq_num, hearing_location_seq_num,
       asgn_attorney_emplid, DURATION_CODE,
       SCHEDULE_TYPE_CODE, SCHEDULE_STATUS_CODE,
       keep_attorney_flag, assigned_date,
       reminder_date, schedule_text, create_date, create_user,
       asgn_Other_Firm_Seq_Num, asgn_Person_Seq_Num,
       update_user, update_date)
    VALUES
      (ScheduleSeqNum, LegalCaseSeqNum,
      ScheduleDate, ScheduleTime, decode(aljPersonSeqNum, 0, null, aljPersonSeqNum),
      decode(HearinglocationSeqNum, 0, null, HearinglocationSeqNum), AsgnAttorneyEmplid, durationCode,
      ScheduletypeCode, SchedulestatusCode, keepAttorneyFlag,
      assignedDate, reminderDate, ScheduleText,
      createDate, createUser, asgnOtherFirmSeqNum,
      asgnPersonSeqNum, createUser, createDate);
     v_update_count := SQL%Rowcount;
    RETURN v_update_count;
  END insertSchedule;

  Function deleteSchedule(ScheduleSeqNum IN NUMBER)
      RETURN NUMBER
    IS
    v_update_count             NUMBER;
     v_legalCaseNum NUMBER;
     BEGIN
     UPDATE LEGAL_CASE
        SET most_recent_hearing_seq_num = NULL
      WHERE most_recent_hearing_seq_num  = ScheduleSeqNum;
     UPDATE APPEAL
        SET schedule_seq_num = NULL
      WHERE schedule_seq_num = ScheduleSeqNum;
     DELETE FROM ASSIGNMENT_RESULT WHERE schedule_seq_num = ScheduleSeqNum;
     DELETE FROM SCHEDULE WHERE schedule_seq_num = ScheduleSeqNum;
     v_update_count := SQL%Rowcount;
    RETURN v_update_count;
  END deleteSchedule;

  PROCEDURE getLegalCase(LegalCaseSeqNum IN NUMBER,
       ClaimId in Number,
       recordset IN OUT ref_cursor)

   AS BEGIN OPEN recordset FOR
      SELECT LEGAL_CASE_SEQ_NUM LegalCaseSeqNum,
      CASE_TYPE_CODE CaseTypeCode,
      REQUEST_TYPE_CODE RequestTypeCode,
      WCB_NUM WcbNum,
      get_case_num(LegalCaseSeqNum) AS CASE_NUM,
      DCBS_NUM DcbsNum,
      CASE_STATUS_CODE CaseStatusCode,
      CLAIM_ID ClaimID,
      REQUEST_DATE RequestDate,
      WORKER_ATTY_FIRM_PROV_LOC_ID WorkerAttyFirmProvLocID,
      WORKER_ATTY_PROV_LOC_ID WorkerAttyProvLocID,
      OAR_DUE_DATE OarDueDate,
      OAR_MAILED_DATE OarMailedDate,
      LEGAL_FILE_CREATE_DATE LegalFileCreateDate,
      CASE_WEIGHT_VALUE CaseWeightValue,
      ACKNOWLEDGE_DATE AcknowledgeDate,
      REQUEST_NEW_WCB_NUM_FLAG RequestNewWcbNumFlag,
      REQUESTED_BY_CODE RequestedBYCode,
      REQUEST_RECEIPT_DATE RequestReceiptDate,
      REQUEST_POSTMARK_DATE RequestPostmarkDate,
      REQUEST_ALL_DAY_HEARING_FLAG RequestAllDayHearingFlag,
      STRESS_CLAIM_FLAG StressClaimFlag,
      RECON_PACKET_RECEIVED_DATE ReconPacketReceivedDate,
      DISMISSAL_DATE DismissalDate,
      SETTLEMENT_DATE SettlementDate,
      SEND_DEF_FLAG SendDefFlag,
      RECON_LETTER_MAILED_DATE ReconLetterMailedDate,
      DEF_SENT_DATE DefSentDate,
      EMPLOYER_EVALUATION_NUM EmployerEvaluationNum,
      CONSOLIDATE_CASE_FLAG ConsolidateCaseFlag,
      OWCP_NUM OwcpNum,
      OALJ_NUM OaljNum,
      BRB_NUM BrbNum,
      CREATE_DATE CreateDate,
      CREATE_USER CreateUser,
      UPDATE_DATE UpdateDate,
      UPDATE_USER UpdateUser,
      MOST_RECENT_HEARING_SEQ_NUM as MostRecentHearingSeqNum,
      CLAIM_IMAGE_ALERTS_FLAG ClaimImageAlertsFlag,
      getClaimImageAlertsUpdateDate(LegalCaseSeqNum) AS ClaimImageAlertsUpdateDate
      FROM LEGAL_CASE C
      WHERE (legal_case_seq_num = LegalCaseSeqNum  and ClaimId = 0)
         OR (LegalCaseSeqNum = 0 and Claim_id = ClaimId);
END getLegalCase;

FUNCTION getClaimImageAlertsUpdateDate(aLegalCaseNum IN NUMBER)
RETURN DATE
IS
v_alert_date DATE;
BEGIN
     BEGIN
          SELECT MAX(UPDATE_DATE)
                 INTO v_alert_date
              FROM CLAIM_IMAGE_ALERTS_HISTORY H
          WHERE H.LEGAL_CASE_SEQ_NUM  = aLegalCaseNum;
     EXCEPTION
         WHEN NO_DATA_FOUND THEN
               NULL;
    END;
    RETURN v_alert_date;
END;

      PROCEDURE setFileCreateDate(seqNums IN VARCHAR2, inDate IN DATE,
          userid IN VARCHAR2)
          IS
          v_updatestmt VARCHAR2(5000);

          BEGIN

          v_updatestmt := 'Update legal_case ' ||
            ' set legal_file_create_date = ''' || inDate ||
            ''' , update_date =  sysdate '  ||
            ' , update_user = ''' || userid ||
            ''' where legal_case_seq_num in (' || seqNums || ')';
 --         dbms_output.put_line(v_updatestmt);
          EXECUTE IMMEDIATE v_updatestmt;

     END setFileCreateDate;

 PROCEDURE mailDateList(recordset OUT ref_cursor, inType IN VARCHAR2)
     AS BEGIN
     IF intype = 'OAR' THEN
     OPEN recordset FOR
     SELECT /*+ FIRST_ROWS */
            WCB_NUM wcbNum,
            OAR_DUE_DATE oarDueDate,
            OAR_MAILED_DATE oarMailedDate,
            CAS.LEGAL_CASE_SEQ_NUM caseId,
            CAS.CLAIM_ID claimId,
            'N' AS MAILED,
            'N' AS DONOTMAIL,
            DECODE(NVL(CON.PRIMARY_CASE_SEQ_NUM,CAS.LEGAL_CASE_SEQ_NUM),
                       CAS.LEGAL_CASE_SEQ_NUM,'Y','N') AS PRIMARYFLAG
     FROM LEGAL_CASE cas,
          CONSOLIDATED_CASE CON
    WHERE (OAR_DUE_DATE IS NOT NULL)
      AND (OAR_MAILED_DATE IS NULL)
      AND cas.CASE_STATUS_CODE <> 'CLOSE'
      and cas.mail_oar_flag = 'Y'
      AND CAS.LEGAL_CASE_SEQ_NUM = CON.SECONDARY_CASE_SEQ_NUM (+)
   ORDER BY OAR_DUE_DATE;
    ELSE
     OPEN recordset FOR
     SELECT /*+ FIRST_ROWS */
       WCB_NUM wcbNum,
       RECON_LETTER_MAILED_DATE AS OARDUEDATE,
       RECON_PACKET_RECEIVED_DATE AS OARMAILEDDATE,
       CAS.LEGAL_CASE_SEQ_NUM caseId,
       CAS.CLAIM_ID claimId,
       'N' AS MAILED,
       'N' AS DONOTMAIL,
       DECODE(NVL(CON.PRIMARY_CASE_SEQ_NUM,CAS.LEGAL_CASE_SEQ_NUM),
                  CAS.LEGAL_CASE_SEQ_NUM,'Y','N') AS PRIMARYFLAG
   FROM LEGAL_CASE CAS,
        CONSOLIDATED_CASE CON
  WHERE (RECON_LETTER_MAILED_DATE IS NOT NULL)
   AND (RECON_PACKET_RECEIVED_DATE IS NULL)
   AND cas.CASE_STATUS_CODE <> 'CLOSE'
     and cas.mail_oar_flag = 'Y'
   AND CAS.LEGAL_CASE_SEQ_NUM = CON.SECONDARY_CASE_SEQ_NUM (+)
ORDER BY RECON_LETTER_MAILED_DATE;
   END IF;

  END mailDateList;

  PROCEDURE setMailDate(seqNums IN VARCHAR2, inDate IN DATE,
           inType IN VARCHAR2, userid IN VARCHAR2)
     IS
          v_updatestmt VARCHAR2(5000);

          BEGIN
     IF inType = 'OAR' THEN
        v_updatestmt := 'Update legal_case ' ||
            ' set oar_mailed_date = ''' || inDate ||
            ''' , update_date =  sysdate ' ||
            ' , update_user = ''' || userid ||
            ''' where legal_case_seq_num in (' || seqNums || ')';
 --         dbms_output.put_line(v_updatestmt);
          EXECUTE IMMEDIATE v_updatestmt;
     ELSE
        v_updatestmt := 'Update legal_case ' ||
            ' set recon_packet_received_date = ''' || inDate ||
            ''' , update_date =  sysdate ' ||
            ' , update_user = ''' || userid ||
            ''' where legal_case_seq_num in (' || seqNums || ')';
 --         dbms_output.put_line(v_updatestmt);
            EXECUTE IMMEDIATE v_updatestmt;
     END IF;

     END setMailDate;

     Function setNoMailDate(InseqNum IN NUMBER, inDate IN DATE,
           inType IN VARCHAR2, userid IN VARCHAR2)
       RETURN NUMBER
    IS
    v_update_count             NUMBER;
    Begin
      IF inType = 'OAR' THEN
        UPDATE LEGAL_CASE
        SET oar_mailed_date =
         (SELECT oar_mailed_date
            FROM LEGAL_CASE
          WHERE legal_case_seq_num =
            (SELECT MAX(primary_case_seq_num)
               FROM CONSOLIDATED_CASE
              WHERE secondary_case_seq_num = InSeqNum)),
          update_date = SYSDATE,
          update_user = userid
         WHERE legal_case_seq_num = InSeqNum;
      ELSE
         UPDATE LEGAL_CASE
         SET recon_packet_received_date =
         (SELECT recon_packet_received_date
            FROM LEGAL_CASE
          WHERE legal_case_seq_num =
            (SELECT MAX(primary_case_seq_num)
               FROM CONSOLIDATED_CASE
              WHERE secondary_case_seq_num = InSeqNum)),
                update_date = SYSDATE,
                update_user = userid
                WHERE legal_case_seq_num = InseqNum;
      END IF;

         v_update_count := SQL%Rowcount;
    RETURN v_update_count;
     END setNoMailDate;

 Function  setMailOARFlag(InseqNum IN NUMBER, userid IN VARCHAR2)
       RETURN NUMBER
    IS
    v_update_count             NUMBER;
    Begin
     UPDATE LEGAL_CASE
        SET mail_oar_flag = 'N',
          update_date = SYSDATE,
          update_user = userid
         WHERE legal_case_seq_num = InSeqNum;

         v_update_count := SQL%Rowcount;
    RETURN v_update_count;
     END setMailOARFlag;
  PROCEDURE getAppeal(LegalCaseID IN NUMBER,
       Recordset OUT ref_cursor) AS
     BEGIN
      OPEN Recordset FOR
      SELECT appeal_seq_num AppealID,
       legal_case_seq_num LegalCaseID,
       REVIEW_LEVEL_CODE ReviewLevelCode,
       review_due_date ReviewDueDate,
       review_filed_date ReviewFiledDate,
       PETITIONER_RESPONDENT_CODE PetitionerRespondentCode,
       approved_flag ApprovedFlag,
       atty_emplid AttorneyEmployeeID,
       brief1_due_date Brief1DueDate,
       brief1_filed_date Brief1FiledDate,
       brief2_due_date Brief2DueDate,
       brief2_filed_date Brief2FiledDate,
       brief3_due_date Brief3DueDate,
       brief3_filed_date Brief3FiledDate,
       appeal_num AppealNumber,
       schedule_seq_num ScheduleID,
       create_date CreateDate,
       create_user CreateUser,
       update_date UpdateDate,
       update_user UpdateUser
     FROM APPEAL
       WHERE legal_case_seq_num = LegalCaseID
       ORDER BY appeal_seq_num;

     END getAppeal;

Function insertAppeal(AppealID IN NUMBER, LegalCaseID IN NUMBER,
              reviewLevel IN VARCHAR2, dueDate IN DATE, filedDate IN DATE,
              PetitionerRespondentCode IN VARCHAR2, approvedFlag IN VARCHAR2,
              attyEmplid IN VARCHAR2,
              brief1DueDate IN DATE, brief1FiledDate IN DATE,
              brief2DueDate IN DATE, brief2FiledDate IN DATE,
              brief3DueDate IN DATE, brief3FiledDate IN DATE,
              appealNum IN VARCHAR2, ScheduleID IN NUMBER,
              createDate IN DATE, createUser IN VARCHAR2)
               RETURN NUMBER
       AS BEGIN
           INSERT INTO APPEAL
      (appeal_seq_num, legal_case_seq_num, REVIEW_LEVEL_CODE,
       review_due_date, review_filed_date,
       PETITIONER_RESPONDENT_CODE, approved_flag,
       atty_emplid, brief1_due_date, brief1_filed_date,
       brief2_due_date, brief2_filed_date,
       brief3_due_date, brief3_filed_date,
       appeal_num, schedule_seq_num, create_date, create_user)
    VALUES
      (AppealID, LegalCaseID,
              reviewLevel, dueDate, filedDate,
              PetitionerRespondentCode, approvedFlag,
              attyEmplid,
              brief1DueDate, brief1FiledDate,
              brief2DueDate, brief2FiledDate,
              brief3DueDate, brief3FiledDate,
              appealNum, ScheduleID,
              createDate, createUser);
    Return SQL%Rowcount;
    EXCEPTION
         WHEN OTHERS THEN
           RAISE_APPLICATION_ERROR(-20004,
           'Error in insertAppeal: '||SQLERRM);

    END insertAppeal;

    Function updateAppeal(AppealID IN NUMBER, LegalCaseID IN NUMBER,
              reviewLevel IN VARCHAR2, dueDate IN DATE, filedDate IN DATE,
              PetitionerRespondentCode IN VARCHAR2, approvedFlag IN VARCHAR2,
              attyEmplid IN VARCHAR2,
              brief1DueDate IN DATE, brief1FiledDate IN DATE,
              brief2DueDate IN DATE, brief2FiledDate IN DATE,
              brief3DueDate IN DATE, brief3FiledDate IN DATE,
              appealNum IN VARCHAR2, ScheduleID IN NUMBER,
              updateDate IN DATE, updateUser IN VARCHAR2)
               RETURN NUMBER
       AS BEGIN
       UPDATE APPEAL
       SET REVIEW_LEVEL_CODE = reviewLevel,
       review_due_date = dueDate,
       review_filed_date = filedDate,
       PETITIONER_RESPONDENT_CODE = PetitionerRespondentCode,
       approved_flag = approvedFlag,
       atty_emplid = attyEmplid,
       brief1_due_date = brief1DueDate,
       brief1_filed_date = brief1FiledDate,
       brief2_due_date = brief2DueDate,
       brief2_filed_date = brief2FiledDate,
       brief3_due_date = brief3DueDate,
       brief3_filed_date = brief3FiledDate,
       appeal_num = appealNum,
       schedule_seq_num = ScheduleID,
       update_date = updatedate,
       update_user = updateUser
       WHERE appeal_seq_num = AppealID;
    Return SQL%Rowcount;
    END updateAppeal;

    PROCEDURE getAppealExtension(appealSeqNum IN NUMBER,
       Recordset OUT ref_cursor)

       AS BEGIN OPEN recordset FOR
       SELECT appeal_extension_seq_num,
              appeal_seq_num,
              request_date,
              days_requested
       FROM APPEAL_EXTENSION
       WHERE appeal_seq_num = appealSeqNum;

       END getAppealExtension;

   Function updateAppealExtension(
         appealExtSeqNum IN NUMBER,
         appealSeqNum    IN NUMBER,
         requestDate     IN DATE,
         daysRequested   IN NUMBER)
          RETURN NUMBER
         AS BEGIN
            UPDATE APPEAL_EXTENSION
               SET request_date = requestDate,
                   days_requested = daysRequested
            WHERE appeal_extension_Seq_num = appealExtSeqNum;
            Return SQL%Rowcount;
         END updateAppealExtension;

   Function insertAppealExtension(
         appealExtSeqNum IN NUMBER,
         appealSeqNum    IN NUMBER,
         requestDate     IN DATE,
         daysRequested   IN NUMBER)
          RETURN NUMBER
         AS BEGIN
         INSERT INTO APPEAL_EXTENSION
         (appeal_extension_seq_num,
          appeal_seq_num,
          request_date,
          days_requested)
         VALUES
          (appealExtSeqNum,
           appealSeqNum,
           requestDate,
           daysRequested);
           Return SQL%Rowcount;
         END insertAppealExtension;

   Function deleteappeal (inlegalcaseseqnum IN NUMBER, inappealseqnum IN NUMBER)
    RETURN NUMBER
AS
   v_atty           APPEAL.atty_emplid%TYPE;
   v_review_level   APPEAL.REVIEW_LEVEL_CODE%TYPE;
   v_sched_seq      APPEAL.schedule_seq_num%TYPE;
   v_prior_level    APPEAL.REVIEW_LEVEL_CODE%TYPE;
   v_prior_atty     APPEAL.atty_emplid%TYPE;
   v_prior_appeal   APPEAL.appeal_seq_num%TYPE;
   v_loc_emplid     LEGAL_FILE_LOCATION.location_person_emplid%TYPE;
   v_loc_code       LEGAL_FILE_LOCATION.LEGAL_FILE_LOCATION_CODE%TYPE;
   v_assgn_atty     APPEAL.atty_emplid%TYPE;
   v_cnt            Number;
   v_result         Number;
BEGIN
   BEGIN
      SELECT atty_emplid, NVL(REVIEW_LEVEL_CODE,' '), schedule_seq_num
        INTO v_atty, v_review_level, v_sched_seq
        FROM APPEAL
       WHERE appeal_seq_num = inappealseqnum;
   END;

   BEGIN
      DELETE FROM APPEAL_EXTENSION
            WHERE appeal_seq_num = inappealseqnum;
   EXCEPTION
      WHEN NO_DATA_FOUND THEN
         NULL;
   END;

   BEGIN
      DELETE FROM APPEAL
            WHERE appeal_seq_num = inappealseqnum;
            v_result := SQL%Rowcount;
   EXCEPTION
      WHEN OTHERS THEN
         NULL;
   END;

   IF v_sched_seq IS NOT NULL THEN
      v_cnt := deleteschedule (v_sched_seq);
      updatehearingseqnum (inlegalcaseseqnum);
   END IF;

   BEGIN
      SELECT atty_emplid, REVIEW_LEVEL_CODE, appeal_seq_num
        INTO v_prior_atty, v_prior_level, v_prior_appeal
        FROM APPEAL
       WHERE appeal_seq_num = (SELECT MAX (appeal_seq_num)
                                 FROM APPEAL
                                WHERE legal_case_seq_num = inlegalcaseseqnum);
   EXCEPTION
      WHEN NO_DATA_FOUND THEN
         NULL;
   END;

   v_assgn_atty := get_asgn_atty_id (inlegalcaseseqnum);

   BEGIN
      SELECT location_person_emplid, LEGAL_FILE_LOCATION_CODE
        INTO v_loc_emplid, v_loc_code
        FROM LEGAL_FILE_LOCATION
       WHERE legal_case_seq_num = inlegalcaseseqnum
         AND legal_file_location_seq_num =
                               (SELECT MAX (legal_file_location_seq_num)
                                  FROM LEGAL_FILE_LOCATION
                                 WHERE legal_case_seq_num = inlegalcaseseqnum);
   EXCEPTION
      WHEN NO_DATA_FOUND THEN
         NULL;
   END;

   IF NVL(v_review_level,' ') != NVL(v_prior_level,' ')
   THEN
      popcasestatus (inlegalcaseseqnum);
   END IF;

   IF v_loc_emplid IS NOT NULL THEN
      IF v_loc_emplid != NVL(v_assgn_atty,0) THEN
         popfilelocation (inlegalcaseseqnum);
      END IF;
   ELSE
      IF v_loc_code = 'APEAL' THEN
         IF v_prior_appeal IS NULL THEN
            popfilelocation (inlegalcaseseqnum);
         ELSE
            IF v_prior_atty IS NOT NULL THEN
                popfilelocation (inlegalcaseseqnum);
            END IF;
        END IF;
      END IF;
   END IF;
   return v_result;
END deleteappeal;

PROCEDURE popcasestatus(inlegalcaseseqnum IN LEGAL_CASE.legal_case_seq_num%TYPE) AS
BEGIN
    DELETE FROM CASE_STATUS_HISTORY
    WHERE case_status_history_seq_num =
          (SELECT MAX(case_status_history_seq_num)
             FROM CASE_STATUS_HISTORY
            WHERE legal_case_seq_num = inlegalcaseseqnum);

    UPDATE LEGAL_CASE
      SET CASE_STATUS_CODE =
          (SELECT CASE_STATUS_CODE
             FROM CASE_STATUS_HISTORY
            WHERE case_status_history_seq_num =
                  (SELECT MAX(case_status_history_seq_num)
                     FROM CASE_STATUS_HISTORY
                    WHERE legal_case_seq_num = inlegalcaseseqnum))
      WHERE legal_case_seq_num = inlegalcaseseqnum;
END popcasestatus;

PROCEDURE popfilelocation(inlegalcaseseqnum IN LEGAL_CASE.legal_case_seq_num%TYPE) AS
BEGIN
    DELETE FROM LEGAL_FILE_LOCATION
     WHERE legal_file_location_seq_num =
           (SELECT MAX(legal_file_location_seq_num)
              FROM LEGAL_FILE_LOCATION
             WHERE legal_case_seq_num = inlegalcaseseqnum);
END popfilelocation;


     PROCEDURE getBoardLog(Recordset OUT ref_cursor)
    AS BEGIN OPEN recordset FOR
    SELECT cas.wcb_num WCBNum, CAS.LEGAL_CASE_SEQ_NUM caseId,
           PRC.CODE_DESCR PetitionerRespondent,
           A.brief1_due_date Brief1DueDate,
           A.brief2_due_date Brief2DueDate,
           cas.claim_id Claim_ID
      FROM LEGAL_CASE cas,
           APPEAL A,
           PETITIONER_RESPONDENT_CODE prc
     WHERE A.REVIEW_LEVEL_CODE = 'WCBA'
       AND A.approved_flag = 'Y'
       AND cas.CASE_STATUS_CODE <> 'CLOSE'
       AND A.atty_emplid IS NULL
       AND A.legal_case_seq_num = cas.legal_case_seq_num
       and A.PETITIONER_RESPONDENT_CODE = PRC.CODE;
    END getBoardLog;

    PROCEDURE updateAppealAtty(appealSeqNum IN NUMBER,
              attyEmplid IN VARCHAR2)
      AS BEGIN
      UPDATE APPEAL
         SET atty_emplid = attyEmplid
       WHERE appeal_seq_num = appealSeqNum;

    END updateAppealAtty;

    PROCEDURE getAcknowledgeList(recordset OUT ref_cursor, inType IN VARCHAR2)
    AS BEGIN OPEN recordset FOR
    SELECT L.LEGAL_CASE_SEQ_NUM caseId, L.WCB_NUM wcbNum,
           L.REQUEST_DATE RequestDate,
           get_case_issue_codes(l.legal_case_seq_num) AS Issues,
           L.CLAIM_ID claimId
  FROM LEGAL_CASE L
  WHERE L.CASE_STATUS_CODE <> 'CLOSE'
  AND (L.WCB_NUM IS NULL AND L.DCBS_NUM IS NULL)
  AND L.REQUEST_TYPE_CODE = inType
  ORDER BY REQUEST_DATE;

    END getAcknowledgeList;

   Function updateAcknowledged(inType IN VARCHAR2, inNum IN VARCHAR2,
       inDate IN DATE, CaseId IN NUMBER)
      RETURN NUMBER
    IS
    v_update_count             NUMBER;
   BEGIN
       IF inType = 'WCB' THEN
          UPDATE LEGAL_CASE
             SET wcb_num = inNum,
                 acknowledge_date = inDate
           WHERE legal_case_seq_num = CaseId;
       END IF;
       IF inType = 'DCBS' THEN
          UPDATE LEGAL_CASE
             SET dcbs_num = inNum,
                 acknowledge_date = inDate
           WHERE legal_case_seq_num = CaseId;
       END IF;
       v_update_count := SQL%Rowcount;
    RETURN v_update_count;
    END updateAcknowledged;

    PROCEDURE updateDEFfields(aLegalCaseNum IN NUMBER, inNum IN VARCHAR2,
       inDate IN DATE, inFlag IN VARCHAR2, inUser IN VARCHAR2)
       AS BEGIN
        UPDATE LEGAL_CASE
             SET employer_evaluation_num = inNum,
                 def_sent_date = inDate,
                 send_def_flag = inFlag,
                 update_user = inUser,
                 update_date = SYSDATE
           WHERE legal_case_seq_num = aLegalCaseNum;
    END updateDEFfields;

    PROCEDURE getFileLocation(LegalCaseID IN NUMBER,
       Recordset OUT ref_cursor)
       AS BEGIN OPEN recordset FOR
       SELECT legal_file_location_seq_num LegalFileLocationID,
              legal_case_seq_num LegalCaseID,
              location_person_emplid LocationPersonEmplid,
              LEGAL_FILE_LOCATION_CODE LegalFileLocationCode,
              move_date MoveDate,
              code_descr FileLocationDescr,
              LTRIM(RTRIM(adp.last_name)) || ', ' ||
              LTRIM(RTRIM(DECODE(NVL(LTRIM(RTRIM(adp.preferred_name)),''),'',first_name,
                 preferred_name))) AS FileLocationEmployee,
              LOC.CREATE_DATE CreateDate,
              LOC.CREATE_USER CreateUser
              FROM LEGAL_FILE_LOCATION loc,
                   LEGAL_FILE_LOCATION_CODE cod,
                   adp_employee adp
        WHERE legal_case_seq_num = LegalCaseID
          AND cod.code(+) = loc.LEGAL_FILE_LOCATION_CODE
          AND adp.emplid (+) = loc.location_person_emplid
        ORDER BY TRUNC(move_date) DESC, legal_file_location_seq_num DESC;

       END getFileLocation;

    Function updateFileLocation(
         LegalFileLocationID   IN NUMBER,
         LegalCaseID           IN NUMBER,
         LocationPersonEmplid  IN VARCHAR2,
         LegalFileLocationCode IN VARCHAR2,
         moveDate        IN DATE) return number
     AS BEGIN
        UPDATE LEGAL_FILE_LOCATION
           SET location_person_emplid = LocationPersonEmplid,
               LEGAL_FILE_LOCATION_CODE = LegalFileLocationCode,
               move_date = moveDate
         WHERE legal_file_location_seq_num = LegalFileLocationID;
     return SQL%Rowcount;
     END updateFileLocation;

     Function insertFileLocation(
         LegalFileLocationID   IN NUMBER,
         LegalCaseID           IN NUMBER,
         LocationPersonEmplid  IN VARCHAR2,
         LegalFileLocationCode IN VARCHAR2,
         moveDate        IN DATE,
         createDate      in date,
         createUser      in varchar2)
                  return number
         AS BEGIN
         INSERT INTO LEGAL_FILE_LOCATION
         (legal_file_location_seq_num,
          legal_case_seq_num, location_person_emplid,
          LEGAL_FILE_LOCATION_CODE,
          move_date,
          create_date,
          create_user)
          VALUES (LegalFileLocationID,
            LegalCaseID,
            LocationPersonEmplid,
            LegalFileLocationCode,
            moveDate,
            createDate,
            createUser);
         return SQL%Rowcount;
         END insertFileLocation;

   PROCEDURE getAttyProfile(Recordset OUT ref_cursor)
   AS BEGIN
   OPEN recordset FOR SELECT /*+ FIRST_ROWS */
    profile_emplid,
    adp.LAST_NAME || ', ' || DECODE(NVL(adp.preferred_name, ' '), ' ', adp.FIRST_NAME,adp.preferred_name) AS EMPLOYEE_NAME,
    eff_date,
    exp_date,
    ccu_claims_flag,
    ptd_claims_flag,
    ppd_claims_flag,
    confidential_claims_flag,
    health_issue_claims_flag,
    dcbs_claims_flag,
    federal_claims_flag,
    load_percent,
    primary_hear_loc_code,
    secondary_hear_loc_code,
    claim_team_code,
    ATTORNEY_TYPE_CODE,
    secretary_emplid,
    manager_emplid,
    tertiary_hear_loc_code,
    oregon_state_bar_number
  FROM LEGAL.ATTORNEY_PROFILE, adp_employee adp
  WHERE profile_emplid = adp.emplid;

  END getAttyProfile;

   Function insertAttyProfile(
       profileEmplid      IN   VARCHAR2,
       EffDate            IN   DATE,
       ExpDate            IN   DATE,
       CcuClaimsFlag      IN   VARCHAR2,
       PtdClaimsFlag      IN   VARCHAR2,
       PpdClaimsFlag      IN   VARCHAR2,
       ConfidentialClaimsFlag IN VARCHAR2,
       HealthIssueClaimsFlag  IN VARCHAR2,
       DcbsClaimsFlag         IN VARCHAR2,
       FederalClaimsFlag      IN VARCHAR2,
       LoadPercent            IN NUMBER,
       PrimaryHearLocCode     IN VARCHAR2,
       SecondaryHearLocCode   IN VARCHAR2,
       ClaimTeamCode          IN VARCHAR2,
       AttorneyTypeCode       IN VARCHAR2,
       SecretaryEmplid        IN VARCHAR2,
       ManagerEmplid          IN VARCHAR2,
       TertiaryHearLocCode    IN VARCHAR2,
       osbNumber              IN VARCHAR2)
        RETURN NUMBER
as
    v_update_count             NUMBER;
BEGIN
  INSERT INTO LEGAL.ATTORNEY_PROFILE
(
    profile_emplid,
    eff_date,
    exp_date,
    ccu_claims_flag,
    ptd_claims_flag,
    ppd_claims_flag,
    confidential_claims_flag,
    health_issue_claims_flag,
    dcbs_claims_flag,
    federal_claims_flag,
    load_percent,
    primary_hear_loc_code,
    secondary_hear_loc_code,
    claim_team_code,
    ATTORNEY_TYPE_CODE,
    secretary_emplid,
    manager_emplid,
    tertiary_hear_loc_code,
    create_date,
    CREATE_user,
    OREGON_STATE_BAR_NUMBER
)
VALUES
(  profileEmplid,
       EffDate,
       ExpDate,
       CcuClaimsFlag,
       PtdClaimsFlag,
       PpdClaimsFlag,
       ConfidentialClaimsFlag,
       HealthIssueClaimsFlag,
       DcbsClaimsFlag,
       FederalClaimsFlag,
       LoadPercent,
       PrimaryHearLocCode,
       SecondaryHearLocCode,
       ClaimTeamCode,
       AttorneyTypeCode,
       SecretaryEmplid,
       ManagerEmplid,
       tertiaryHearlocCode,
       SYSDATE,
       'LEGALNET',
       osbNumber);
    v_update_count := SQL%Rowcount;
    RETURN v_update_count;
   END insertAttyProfile;

  Function updateAttyProfile(
       profileEmplid      IN   VARCHAR2,
       EffDate            IN   DATE,
       ExpDate            IN   DATE,
       CcuClaimsFlag      IN   VARCHAR2,
       PtdClaimsFlag      IN   VARCHAR2,
       PpdClaimsFlag      IN   VARCHAR2,
       ConfidentialClaimsFlag IN VARCHAR2,
       HealthIssueClaimsFlag  IN VARCHAR2,
       DcbsClaimsFlag         IN VARCHAR2,
       FederalClaimsFlag      IN VARCHAR2,
       LoadPercent            IN NUMBER,
       PrimaryHearLocCode     IN VARCHAR2,
       SecondaryHearLocCode   IN VARCHAR2,
       ClaimTeamCode          IN VARCHAR2,
       AttorneyTypeCode       IN VARCHAR2,
       SecretaryEmplid        IN VARCHAR2,
       ManagerEmplid          IN VARCHAR2,
       tertiaryHearLocCode    IN VARCHAR2,
       osbNumber              IN VARCHAR2)
  RETURN NUMBER
  as
     v_update_count             NUMBER;
  BEGIN
    UPDATE LEGAL.ATTORNEY_PROFILE SET
    eff_date = effDate,
    ccu_claims_flag = ccuclaimsflag,
    ptd_claims_flag = ptdclaimsflag,
    ppd_claims_flag = ppdclaimsflag,
    confidential_claims_flag = confidentialclaimsflag,
    health_issue_claims_flag = healthissueclaimsflag,
    dcbs_claims_flag = dcbsclaimsflag,
    federal_claims_flag = federalclaimsflag,
    ATTORNEY_TYPE_CODE = attorneytypecode,
    load_percent = loadpercent,
    primary_hear_loc_code = primaryhearloccode,
    secondary_hear_loc_code = secondaryHearLocCode,
    claim_team_code = claimTeamCode,
    exp_date =  expDate,
    secretary_emplid = secretaryEmplid,
    manager_emplid =  managerEmplid,
    tertiary_hear_loc_code = tertiaryHearLocCode,
    update_date = SYSDATE,
    update_user = 'LEGALNET',
    OREGON_STATE_BAR_NUMBER = osbNumber
WHERE
    profile_emplid = profileEmplid;
    v_update_count := SQL%Rowcount;
    RETURN v_update_count;
  END updateAttyProfile;

 PROCEDURE getAttySchedule(Recordset OUT attySched_cursor)
 AS BEGIN OPEN recordset FOR
    SELECT /*+ FIRST_ROWS */ attorney_schedule_seq_num,
           schedule_emplid,
           eff_date,
           exp_date,
           ATTORNEY_SCHEDULE_TYPE_CODE,
           DURATION_CODE
      FROM ATTORNEY_SCHEDULE
     WHERE NVL(exp_date, SYSDATE) > SYSDATE - 1
     ORDER BY eff_date;
 END getAttySchedule;

    PROCEDURE updateAttySchedule(
         attySchedSeqNum IN NUMBER,
         scheduleEmplid  IN VARCHAR2,
         effDate         IN DATE,
         expDate         IN DATE,
         typeCode        IN VARCHAR2,
         durationCode    IN VARCHAR2)
     AS BEGIN
        UPDATE ATTORNEY_SCHEDULE
           SET schedule_emplid = scheduleEmplid,
               eff_date = effDate,
               exp_date = expdate,
               ATTORNEY_SCHEDULE_TYPE_CODE = typeCode,
               DURATION_CODE = durationCode
         WHERE attorney_schedule_seq_num = attySchedSeqNum;
     END updateAttySchedule;

     PROCEDURE insertAttySchedule(
       attySchedSeqNum IN NUMBER,
         scheduleEmplid  IN VARCHAR2,
         effDate         IN DATE,
         expDate         IN DATE,
         typeCode        IN VARCHAR2,
         durationCode   IN VARCHAR2)
      AS BEGIN
         INSERT INTO ATTORNEY_SCHEDULE
         (attorney_schedule_seq_num,
          schedule_emplid,
          eff_date,
          exp_date,
          ATTORNEY_SCHEDULE_TYPE_CODE,
          DURATION_CODE)
          VALUES( attySchedSeqNum,
           scheduleEmplid,
           effDate,
           expDate,
           typeCode,
           durationCode);
      END insertAttySchedule;

       PROCEDURE deleteAttySchedule(attySchedSeqNum IN NUMBER)
       AS BEGIN
       DELETE FROM ATTORNEY_SCHEDULE
       WHERE attorney_schedule_seq_num = attySchedSeqNum;

       END deleteAttySchedule;

Function InsertLegalCase(LegalCaseSeqNum in LEGAL_CASE.LEGAL_CASE_SEQ_NUM%TYPE,
                         CaseTypeCode IN LEGAL_CASE.REQUEST_TYPE_CODE%TYPE,
                         RequestTypeCode in LEGAL_CASE.REQUEST_TYPE_CODE%TYPE,
                         WcbNum in LEGAL_CASE.WCB_NUM%TYPE,
                         DcbsNum in LEGAL_CASE.DCBS_NUM%Type,
                         CaseStatusCode in LEGAL_CASE.CASE_STATUS_CODE%Type,
                         claimID        IN LEGAL_CASE.claim_id%TYPE,
                         RequestDate    IN LEGAL_CASE.request_date%TYPE,
                         WorkerAttyProvLocID in LEGAL_CASE.WORKER_ATTY_PROV_LOC_ID%Type,
                         WorkerAttyFirmProvLocID in LEGAL_CASE.WORKER_ATTY_FIRM_PROV_LOC_ID%Type,
                         OarDueDate      IN LEGAL_CASE.OAR_DUE_DATE%type,
                         OarMailedDate   IN LEGAL_CASE.OAR_MAILED_DATE%type,
                         LegalFileCreateDate IN LEGAL_CASE.LEGAL_FILE_CREATE_DATE%Type,
                         CaseWeightValue  IN LEGAL_CASE.CASE_WEIGHT_VALUE%Type,
                         AcknowledgeDate  IN LEGAL_CASE.ACKNOWLEDGE_DATE%type,
                         RequestNewWcbNumFlag IN LEGAL_CASE.REQUEST_ALL_DAY_HEARING_FLAG%Type,
                         RequestedByCode      IN LEGAL_CASE.REQUESTED_BY_CODE%TYPE,
                         RequestReceiptDate   in LEGAL_CASE.REQUEST_RECEIPT_DATE%Type,
                         RequestPostmarkDate  in LEGAL_CASE.REQUEST_POSTMARK_DATE%type,
                         RequestAllDayHearingFlag in LEGAL_CASE.REQUEST_ALL_DAY_HEARING_FLAG%type,
                         StressClaimFlag      in LEGAL_CASE.STRESS_CLAIM_FLAG%type,
                         ReconLetterMailedDate in LEGAL_CASE.RECON_LETTER_MAILED_DATE%type,
                         ReconPacketReceivedDate in LEGAL_CASE.RECON_PACKET_RECEIVED_DATE%type,
                         DismissalDate         in LEGAL_CASE.DISMISSAL_DATE%Type,
                         SettlementDate        in LEGAL_CASE.SETTLEMENT_DATE%Type,
                         SendDefFlag           in LEGAL_CASE.SEND_DEF_FLAG%type,
                         DefSentDate           in LEGAL_CASE.DEF_SENT_DATE%type,
                         EmployerEvaluationNum in LEGAL_CASE.EMPLOYER_EVALUATION_NUM%type,
                         ConsolidateCaseFlag   in LEGAL_CASE.CONSOLIDATE_CASE_FLAG%type,
                         OwcpNum               in LEGAL_CASE.OWCP_NUM%Type,
                         OaljNum               in LEGAL_CASE.OALJ_NUM%type,
                         BrbNum                in LEGAL_CASE.BRB_NUM%type,
                         CreateDate            in LEGAL_CASE.CREATE_DATE%type,
                         CreateUser            in LEGAL_CASE.CREATE_USER%type,
                         MostRecentHearingSeqNum in LEGAL_CASE.MOST_RECENT_HEARING_SEQ_NUM%type,
                         ClaimImageAlertsFlag   in LEGAL_CASE.CLAIM_IMAGE_ALERTS_FLAG%type)
         RETURN NUMBER
    IS
    v_update_count             NUMBER;
BEGIN
   Insert into
LEGAL_CASE
 (
            LEGAL_CASE_SEQ_NUM,
            CASE_TYPE_CODE,
            REQUEST_TYPE_CODE,
            WCB_NUM,
            DCBS_NUM,
            CASE_STATUS_CODE,
            CLAIM_ID,
            REQUEST_DATE,
            WORKER_ATTY_PROV_LOC_ID,
            WORKER_ATTY_FIRM_PROV_LOC_ID,
            OAR_DUE_DATE,
            OAR_MAILED_DATE,
            LEGAL_FILE_CREATE_DATE,
            CASE_WEIGHT_VALUE,
            ACKNOWLEDGE_DATE,
            REQUEST_NEW_WCB_NUM_FLAG,
            REQUESTED_BY_CODE,
            REQUEST_RECEIPT_DATE,
            REQUEST_POSTMARK_DATE,
            REQUEST_ALL_DAY_HEARING_FLAG,
            STRESS_CLAIM_FLAG,
            RECON_LETTER_MAILED_DATE,
            RECON_PACKET_RECEIVED_DATE,
            DISMISSAL_DATE,
            SETTLEMENT_DATE,
            SEND_DEF_FLAG,
            DEF_SENT_DATE,
            EMPLOYER_EVALUATION_NUM,
            CONSOLIDATE_CASE_FLAG,
            OWCP_NUM,
            OALJ_NUM,
            BRB_NUM,
            CREATE_DATE,
            CREATE_USER,
            MOST_RECENT_HEARING_SEQ_NUM,
            CLAIM_IMAGE_ALERTS_FLAG
 ) Values (
            LegalCaseSeqNum,
            CaseTypeCode,
            RequestTypeCode,
            WcbNum,
            DcbsNum,
            CaseStatusCode,
            ClaimID,
            RequestDate,
            WorkerAttyProvLocID,
            WorkerAttyFirmProvLocID,
            OarDueDate,
            OarMailedDate,
            LegalFileCreateDate,
            CaseWeightValue,
            AcknowledgeDate,
            RequestNewWcbNumFlag,
            RequestedBYCode,
            RequestReceiptDate,
            RequestPostmarkDate,
            RequestAllDayHearingFlag,
            StressClaimFlag,
            ReconLetterMailedDate,
            ReconPacketReceivedDate,
            DismissalDate,
            SettlementDate,
            SendDefFlag,
            DefSentDate,
            EmployerEvaluationNum,
            ConsolidateCaseFlag,
            OwcpNum,
            OaljNum,
            BrbNum,
            CreateDate,
            CreateUser,
            MostRecentHearingSeqNum,
            ClaimImageAlertsFlag);
    v_update_count := SQL%Rowcount;
    RETURN v_update_count;

END InsertLegalCase;

Function UpdateLegalCase(LegalCaseSeqNum in LEGAL_CASE.LEGAL_CASE_SEQ_NUM%TYPE,
                         CaseTypeCode IN LEGAL_CASE.REQUEST_TYPE_CODE%TYPE,
                         RequestTypeCode in LEGAL_CASE.REQUEST_TYPE_CODE%TYPE,
                         WcbNum in LEGAL_CASE.WCB_NUM%TYPE,
                         DcbsNum in LEGAL_CASE.DCBS_NUM%Type,
                         CaseStatusCode in LEGAL_CASE.CASE_STATUS_CODE%Type,
                         claimID        IN LEGAL_CASE.claim_id%TYPE,
                         RequestDate    IN LEGAL_CASE.request_date%TYPE,
                         WorkerAttyProvLocID in LEGAL_CASE.WORKER_ATTY_PROV_LOC_ID%Type,
                         WorkerAttyFirmProvLocID in LEGAL_CASE.WORKER_ATTY_FIRM_PROV_LOC_ID%Type,
                         OarDueDate      IN LEGAL_CASE.OAR_DUE_DATE%type,
                         OarMailedDate   IN LEGAL_CASE.OAR_MAILED_DATE%type,
                         LegalFileCreateDate IN LEGAL_CASE.LEGAL_FILE_CREATE_DATE%Type,
                         CaseWeightValue  IN LEGAL_CASE.CASE_WEIGHT_VALUE%Type,
                         AcknowledgeDate  IN LEGAL_CASE.ACKNOWLEDGE_DATE%type,
                         RequestNewWcbNumFlag IN LEGAL_CASE.REQUEST_ALL_DAY_HEARING_FLAG%Type,
                         RequestedByCode      IN LEGAL_CASE.REQUESTED_BY_CODE%TYPE,
                         RequestReceiptDate   in LEGAL_CASE.REQUEST_RECEIPT_DATE%Type,
                         RequestPostmarkDate  in LEGAL_CASE.REQUEST_POSTMARK_DATE%type,
                         RequestAllDayHearingFlag in LEGAL_CASE.REQUEST_ALL_DAY_HEARING_FLAG%type,
                         StressClaimFlag      in LEGAL_CASE.STRESS_CLAIM_FLAG%type,
                         ReconLetterMailedDate in LEGAL_CASE.RECON_LETTER_MAILED_DATE%type,
                         ReconPacketReceivedDate in LEGAL_CASE.RECON_PACKET_RECEIVED_DATE%type,
                         DismissalDate         in LEGAL_CASE.DISMISSAL_DATE%Type,
                         SettlementDate        in LEGAL_CASE.SETTLEMENT_DATE%Type,
                         SendDefFlag           in LEGAL_CASE.SEND_DEF_FLAG%type,
                         DefSentDate           in LEGAL_CASE.DEF_SENT_DATE%type,
                         EmployerEvaluationNum in LEGAL_CASE.EMPLOYER_EVALUATION_NUM%type,
                         ConsolidateCaseFlag   in LEGAL_CASE.CONSOLIDATE_CASE_FLAG%type,
                         OwcpNum               in LEGAL_CASE.OWCP_NUM%Type,
                         OaljNum               in LEGAL_CASE.OALJ_NUM%type,
                         BrbNum                in LEGAL_CASE.BRB_NUM%type,
                         UpdateDate            in LEGAL_CASE.CREATE_DATE%type,
                         UpdateUser            in LEGAL_CASE.CREATE_USER%type,
                         MostRecentHearingSeqNum in LEGAL_CASE.MOST_RECENT_HEARING_SEQ_NUM%type,
                         ClaimImageAlertsFlag   in LEGAL_CASE.CLAIM_IMAGE_ALERTS_FLAG%type)
                RETURN NUMBER
    IS
    v_update_count             NUMBER;
BEGIN
      Update LEGAL_CASE
      Set   CASE_TYPE_CODE =  CaseTypeCode,
            REQUEST_TYPE_CODE =  RequestTypeCode,
            WCB_NUM =  WcbNum,
            DCBS_NUM =  DcbsNum,
            CASE_STATUS_CODE =  CaseStatusCode,
            REQUEST_DATE =  RequestDate,
            WORKER_ATTY_PROV_LOC_ID =  WorkerAttyProvLocID,
            WORKER_ATTY_FIRM_PROV_LOC_ID =  WorkerAttyFirmProvLocID,
            OAR_DUE_DATE =  OarDueDate,
            OAR_MAILED_DATE =  OarMailedDate,
            LEGAL_FILE_CREATE_DATE =  LegalFileCreateDate,
            CASE_WEIGHT_VALUE =  CaseWeightValue,
            ACKNOWLEDGE_DATE =  AcknowledgeDate,
            REQUEST_NEW_WCB_NUM_FLAG =  RequestNewWcbNumFlag,
            REQUESTED_BY_CODE =  RequestedBYCode,
            REQUEST_RECEIPT_DATE =  RequestReceiptDate,
            REQUEST_POSTMARK_DATE =  RequestPostmarkDate,
            REQUEST_ALL_DAY_HEARING_FLAG =  RequestAllDayHearingFlag,
            STRESS_CLAIM_FLAG =  StressClaimFlag,
            RECON_LETTER_MAILED_DATE =  ReconLetterMailedDate,
            RECON_PACKET_RECEIVED_DATE =  ReconPacketReceivedDate,
            DISMISSAL_DATE =  DismissalDate,
            SETTLEMENT_DATE =  SettlementDate,
            SEND_DEF_FLAG =  SendDefFlag,
            DEF_SENT_DATE =  DefSentDate,
            EMPLOYER_EVALUATION_NUM =  EmployerEvaluationNum,
            CONSOLIDATE_CASE_FLAG =  ConsolidateCaseFlag,
            OWCP_NUM =  OwcpNum,
            OALJ_NUM =  OaljNum,
            BRB_NUM =  BrbNum,
            UPDATE_DATE =  UpdateDate,
            UPDATE_USER =  UpdateUser,
            MOST_RECENT_HEARING_SEQ_NUM =  MostRecentHearingSeqNum,
            CLAIM_IMAGE_ALERTS_FLAG =  ClaimImageAlertsFlag
    Where LEGAL_CASE.LEGAL_CASE_SEQ_NUM =  LegalCaseSeqNum AND
          LEGAL_CASE.CLAIM_ID =  ClaimID;
   v_update_count := SQL%Rowcount;
    RETURN v_update_count;

END UpdateLegalCase;
PROCEDURE CREATE_REQUEST_ISSUE(in_legal_case_seq IN LEGAL_CASE.legal_Case_seq_num%TYPE,
                               in_issue_code     IN REQUEST_ISSUE.REQUEST_ISSUE_CODE%TYPE,
                               in_issue_text     IN REQUEST_ISSUE.REQUEST_ISSUE_TEXT%TYPE) AS
BEGIN
INSERT INTO LEGAL.REQUEST_ISSUE
(
    request_issue_seq_num,
    legal_case_seq_num,
    REQUEST_ISSUE_CODE,
    request_issue_text
)
VALUES
(
    request_issue_Seq.NEXTVAL,
    in_legal_case_Seq,
    in_issue_code,
    in_issue_text
);

END   CREATE_REQUEST_ISSUE;

PROCEDURE UPDATE_LEGALCASE_REQUEST(aLegalCaseSeq IN NUMBER,
                                   aIssueType IN LEGAL_CASE.REQUEST_TYPE_CODE%TYPE,
                                   aWCBRequested IN LEGAL_CASE.request_new_wcb_num_flag%TYPE,
                                   aStress IN LEGAL_CASE.stress_claim_flag%TYPE,
                                   aRequestedBy IN LEGAL_CASE.REQUESTED_BY_CODE%TYPE,
                                   aRequestDate IN VARCHAR2,
                                   aReceivedDate IN VARCHAR2,
                                   aPostmarkDate IN VARCHAR2,
                                   aAttyFirmProvID IN NUMBER,
                                   aReconLetter IN VARCHAR2,
                                   aAttyProvID IN NUMBER,
                                   aLCEExcused IN VARCHAR2,
                                   aOWCP IN VARCHAR2,
                                   aUserID IN VARCHAR2) AS
v_postmark DATE;
v_OARDue DATE;
v_recon_cnt NUMBER;
v_case_weight NUMBER;
--v_lce VARCHAR2(1);

BEGIN

--   v_lce := aLCEExcused;
-- Determine if this is a Reconsideration
   SELECT COUNT(*)
     INTO v_recon_cnt
     FROM REQUEST_ISSUE
    WHERE legal_case_seq_num = aLegalCaseSeq
      AND LTRIM(RTRIM(REQUEST_ISSUE_CODE)) IN
          (SELECT code
            FROM REQUEST_ISSUE_CODE
           WHERE recon_issue_flag = 'Y');

 -- Calculate OAR Due Date
 IF aIssueType = 'WCB' THEN
    IF v_recon_cnt = 0 THEN
       v_postmark :=  TO_DATE(aPostmarkDate, 'MM/DD/YYYY');
       IF NVL(v_postmark,'01-jan-1900') > '01-jan-1900' THEN
          v_OARDue := v_postmark + 10;
       END IF;
    END IF;
-- ELSE
    -- Set LCE Excused
--    v_lce := 'Y';
 END IF;
IF aStress = 'Y' THEN
     v_case_weight := 3;
ELSE
     v_case_weight := 1;
END IF;
BEGIN
UPDATE LEGAL_CASE
  SET  REQUEST_TYPE_CODE = aIssueType,
       request_new_wcb_num_flag = aWCBRequested,
       stress_claim_flag = aStress,
       case_weight_value = v_case_weight,
       REQUESTED_BY_CODE = aRequestedBy,
       request_date = TO_DATE(aRequestDate,'MM/DD/YYYY'),
       request_receipt_date = TO_DATE(aReceivedDate, 'MM/DD/YYYY'),
       request_postmark_date = TO_DATE(aPostmarkDate, 'MM/DD/YYYY'),
       worker_atty_firm_prov_loc_id = aAttyFirmProvID,
       oar_due_date = v_OARDue,
       worker_atty_prov_loc_id = aAttyProvID,
       owcp_num = aOWCP
 WHERE legal_case_seq_num = aLegalCaseSeq;
END;
END UPDATE_LEGALCASE_REQUEST;

PROCEDURE update_lce_Excused(aLegalCaseSeq IN NUMBER,
                             aLCEExcused IN VARCHAR2,
                             aUserID IN VARCHAR2) AS
v_lce_seq NUMBER := 0;
v_lce VARCHAR2(1);
v_lce_excused_flag VARCHAR2(1);
v_result number;
BEGIN
  v_lce := aLceExcused;
  -- Find LCE Row if Exists
  BEGIN
   SELECT legal_case_evaluation_seq_num, LCE_EXCUSED_FLAG
        INTO v_lce_seq, v_lce_excused_flag
        FROM LEGAL_CASE_EVALUATION
       WHERE legal_case_seq_num = aLegalCaseSeq;
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
        NULL;
  END;

  -- IF LCE Exists, Update w/ Change
  IF v_lce_seq > 0 THEN
     IF v_lce <> v_lce_excused_flag THEN
        UPDATE LEGAL_CASE_EVALUATION
           SET lce_excused_flag = v_lce,
               update_date = SYSDATE,
               update_user = aUSERID;
     END IF;
  ELSE
     -- If no LCE row, Insert if LCE is Excused
     IF v_lce = 'Y' THEN
        SELECT legal_case_evaluation_seq.NEXTVAL
          INTO v_lce_seq
          FROM dual;
      v_result :=  insertLCE(v_lce_seq,
                  aLegalCaseSeq,
                  v_lce,
                  NULL, NULL,
                  NULL, NULL,
                  SYSDATE, aUserID, NULL);
     END IF;
  END IF;
END update_lce_Excused;

PROCEDURE getConsolidatedCases(recordset OUT ref_cursor,
                        claimIdsCommaDelimited IN VARCHAR2) AS
  BEGIN
      BEGIN
      OPEN recordset FOR
      SELECT /*+ FIRST_ROWS */ cas.legal_case_seq_num,
             get_case_num(cas.legal_case_seq_num) wcb_num,
             cas.claim_id,
             NVL(con.consolidated_case_Seq_num,0) AS consolidated_case_Seq_num,
             NVL(con.primary_case_seq_num,0) AS primary_case_seq_num,
             NVL(con.secondary_case_seq_num, 0) AS secondary_case_seq_num,
             cod.code_descr AS case_status,
             cas.CASE_STATUS_CODE,
             NVL(con.create_date, sysdate) createDate,
             con.create_user createUser
       FROM LEGAL_CASE cas,
            CONSOLIDATED_CASE con,
            CASE_STATUS_CODE cod
        WHERE cas.claim_id IN (
               -- https://blogs.oracle.com/sql/post/split-comma-separated-values-into-rows-in-oracle-database
               with claimsString as (
                  select claimIdsCommaDelimited str from dual
               )
               select regexp_substr (claimIdsCommaDelimited, '[^,]+', 1, level) value
               from   claimsString
               connect by level <=
               length ( claimIdsCommaDelimited ) - length ( replace ( claimIdsCommaDelimited, ',' ) ) + 1
            )
          AND ( (cas.CASE_STATUS_CODE <> 'CLOSE' AND  DECODE(con.secondary_case_seq_num, cas.legal_case_seq_num,1,0)  = 0)
                          OR
                          DECODE(con.secondary_case_seq_num, cas.legal_case_seq_num,1,0) = 1)
          AND cod.code = cas.CASE_STATUS_CODE
          AND con.secondary_case_Seq_num(+) = cas.legal_case_seq_num
     ORDER BY decode(primary_case_seq_num, 0, 9999999, primary_case_seq_num),
      decode(secondary_case_seq_num, 0, cas.legal_case_seq_num, secondary_case_seq_num);
       EXCEPTION
            WHEN NO_DATA_FOUND THEN
            NULL;
      END;

  END getConsolidatedCases;

  PROCEDURE UpdateConsolidatedCase(aConsolidatedSeq IN CONSOLIDATED_CASE.consolidated_case_seq_num%TYPE,
                                   aPrimarySeq IN CONSOLIDATED_CASE.primary_case_seq_num%TYPE,
                                   aSecondarySeq IN CONSOLIDATED_CASE.secondary_case_seq_num%TYPE,
                                   aConsolFlag IN VARCHAR2,
                                   UpdateDate in DATE,
                                   UpdateUser in Varchar2) AS
  v_old_primary NUMBER;
  v_hearing_seq NUMBER;
  BEGIN

  -- Find the prior Primary Seq
  BEGIN
       SELECT MAX(primary_case_seq_num)
         INTO v_old_primary
         FROM CONSOLIDATED_CASE
        WHERE secondary_case_seq_num = aSecondarySeq;
  EXCEPTION
      WHEN NO_DATA_FOUND THEN
           v_old_primary := 0;
  END;

  IF  v_old_primary > 0
  AND aPrimarySeq   > 0 THEN

       -- Change the Primary Seq on all rows in set
       IF v_old_primary <> aPrimarySeq THEN
          UPDATE CONSOLIDATED_CASE
             SET primary_case_Seq_num = aPrimarySeq
           WHERE primary_case_seq_num = v_old_primary;

          -- Change the hearing to the new primary case
          UPDATE SCHEDULE
            SET  legal_case_seq_num = aPrimarySeq
          WHERE  schedule_seq_num =
                (SELECT most_recent_hearing_seq_num
                   FROM LEGAL_CASE
                 WHERE  legal_case_seq_num = v_old_primary);
       END IF;
  END IF;

  IF aConsolFlag = 'Y' THEN

     -- Update if this is an existing row
     IF aConsolidatedSeq > 0 THEN

      UPDATE CONSOLIDATED_CASE
          SET primary_case_seq_num = aPrimarySeq
             ,secondary_case_seq_num = aSecondarySeq
             , update_date = updatedate
             , update_user = updateuser
        WHERE consolidated_Case_Seq_num = aConsolidatedSeq;

    ELSE
       INSERT INTO CONSOLIDATED_CASE
          (CONSOLIDATED_CASE_SEQ_NUM,
           PRIMARY_CASE_SEQ_NUM,
           SECONDARY_CASE_SEQ_NUM,
           create_date,
           create_user)
       VALUES
          (consolidated_case_seq.NEXTVAL,
           aPrimarySeq,
           aSecondarySeq,
           updatedate,
           updateuser);
    END IF;

     -- Make sure consolidate flag is checked
  UPDATE LEGAL_CASE
     SET consolidate_case_flag = 'Y',
         most_recent_hearing_seq_num =
         (SELECT most_recent_hearing_seq_num
            FROM LEGAL_CASE
           WHERE  legal_case_seq_num = aPrimarySeq),
        CASE_STATUS_CODE =
          (SELECT CASE_STATUS_CODE
             FROM LEGAL_CASE
            WHERE  legal_case_seq_num = aPrimarySeq)
     WHERE legal_case_seq_num = aPrimarySeq
        OR legal_case_seq_num = aSecondarySeq;


 END IF;

  -- Delete if consolidate flag un-checked
  IF aConsolFlag = 'N' THEN
    DELETE FROM CONSOLIDATED_CASE
     WHERE CONSOLIDATED_CASE_SEQ_NUM = aConsolidatedSeq;

    -- Reset most recent hearing seq num
    UPDATE LEGAL_CASE
       SET CONSOLIDATE_CASE_FLAG = 'N',
           MOST_RECENT_HEARING_SEQ_NUM =
           (SELECT MAX(schedule_seq_num)
              FROM SCHEDULE WHERE
             SCHEDULE_TYPE_CODE = 'HEAR'
             AND legal_case_seq_num = aSecondarySeq
             AND schedule_date =
                 (SELECT MAX(schedule_date) FROM
                   SCHEDULE
                   WHERE SCHEDULE_TYPE_CODE = 'HEAR'
             AND legal_case_seq_num = aSecondarySeq))
       WHERE LEGAL_CASE_SEQ_NUM = aSecondarySeq;
  END IF;

  END UpdateConsolidatedCase;

  PROCEDURE getAttorneyNames(recordset OUT attorney_cursor) AS
  BEGIN
  BEGIN
  OPEN recordset FOR
      SELECT trim(last_name) || ', ' || trim(first_name) AS atty_name,
             per.legal_person_Seq_num
        FROM LEGAL_PERSON per, LEGAL_PERSON_TYPE lpt
       WHERE per.legal_person_seq_num = lpt.legal_person_seq_num
         AND lpt.LEGAL_PERSON_TYPE_CODE = 'ATTY'
       UNION
      SELECT '-- None --', 0
        FROM dual
       ORDER BY 1;
         EXCEPTION
      WHEN NO_DATA_FOUND THEN
      NULL;
  END;
END getAttorneyNames;

PROCEDURE insertLetterRequest(
         letterTypeCode  IN VARCHAR2,
         aLegalCaseNum   IN NUMBER,
         holdDate        IN DATE,
         aClaimId        IN NUMBER,
         createUser      IN VARCHAR2,
         requestNum      IN OUT NUMBER)
  AS
   v_send     VARCHAR2(3);
   BEGIN

   v_send := 'YES';

  SELECT letter_request_seq.NEXTVAL
    INTO requestNum
    FROM dual;

  INSERT INTO LETTER_REQUEST
   (letter_request_Seq_num,
    LETTER_TYPE_CODE,
    legal_case_seq_num,
    hold_date,
    create_user,
    create_date)
  VALUES(requestNum,
    letterTypeCode,
    aLegalCaseNum,
    holdDate,
    createUser,
    sysdate);

  END insertLetterRequest;

PROCEDURE getHearingInfo(in_HearingSeq IN SCHEDULE.schedule_seq_num%TYPE,
                         recordset    OUT HearingCursor) AS
BEGIN
OPEN recordset FOR
     SELECT sch.schedule_seq_num, sch.schedule_date, sch.schedule_time,
     sch.DURATION_CODE, DECODE(sch.keep_attorney_flag, 'Y',1,0) AS keep_attorney_flag,
     sch.assigned_date, sch.reminder_date, sch.schedule_text,
     aty.user_id AS atty_mailid,
     sec.user_id AS sec_mailid,
     LTRIM(RTRIM(sec.first_name)) || ' ' || LTRIM(RTRIM(sec.last_name)) AS sec_name,
     LTRIM(RTRIM(per.last_name)) AS alj_name, pro.secretary_emplid AS sec_emplid,
     loc.HEARING_LOCATION_CODE, lcd.code_descr AS hearing_loc_city,
     loc.location_name, ssc.code_descr AS hearing_status,
     DECODE(cas.request_all_day_hearing_flag,'Y',1,0) AS All_day_hearing_flag
FROM SCHEDULE sch,
     ATTORNEY_PROFILE pro,
     adp_employee aty,
     adp_employee sec,
     LEGAL_PERSON per,
     HEARING_LOCATION loc,
     HEARING_LOCATION_CODE lcd,
     SCHEDULE_STATUS_CODE ssc,
     LEGAL_CASE cas
WHERE sch.schedule_seq_num = in_HearingSeq
  AND aty.emplid(+) = sch.asgn_attorney_emplid
  AND pro.profile_emplid(+) = sch.asgn_attorney_emplid
  AND sec.emplid(+) = pro.secretary_emplid
  AND per.legal_person_seq_num(+) = sch.alj_person_seq_num
  AND loc.hearing_location_seq_num(+) = sch.hearing_location_seq_num
  AND lcd.code(+) = loc.HEARING_LOCATION_CODE
  AND ssc.code(+) = sch.SCHEDULE_STATUS_CODE
  AND cas.legal_case_Seq_num = sch.legal_case_seq_num;

  EXCEPTION
    WHEN NO_DATA_FOUND THEN
         NULL;
END getHearingInfo;

PROCEDURE getEmployerAddr(in_Policy IN POLICY.policy_num%TYPE,
                         recordset    OUT EmployerCursor) AS
BEGIN
OPEN recordset FOR
SELECT P.policy_name, l.Loc_addr1, l.loc_addr2, l.loc_city, l.loc_state,
       l.loc_zip,l.loc_zip4, l.mail_addr_flag,
       l.loc_phone || ' ' || l.loc_phone_ext AS loc_phone, l.loc_seq_num
 FROM POLICY P,
      LOCATION l
WHERE P.policy_num = in_policy
  AND l.policy_num = P.policy_num
  AND l.primary_location_flag = 'Y'
  AND TRUNC(l.loc_eff_date) <= TRUNC(SYSDATE)
  AND NVL(TRUNC(l.loc_exp_date), SYSDATE + 1) > TRUNC(SYSDATE);
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
         NULL;
END getEmployerAddr;

PROCEDURE getOtherFirmList(aLegalCaseNum IN NUMBER,
                      recordset OUT OtherFirmListCursor) AS
BEGIN
OPEN recordset FOR
SELECT 'EMPLOYER' AS type_cd,
       emp.legal_case_employer_seq_num,
       emp.employer_other_firm_seq_num,
       --emp.firm_provider_num,
       DECODE(emp.employer_discovery_flag,'Y',1,0) AS  employer_discovery_flag,
       DECODE(emp.attorney_discovery_flag,'Y',1,0) AS  attorney_discovery_flag,
       oth.firm_name,
       oth.OTHER_FIRM_TYPE_CODE,
       oth.addr1,
       oth.addr2,
       oth.city,
       oth.state,
       oth.zip,
       oth.zip4,
       oth.PHONE,
       oth.fax
  FROM LEGAL_CASE_EMPLOYER emp,
       OTHER_FIRM oth
 WHERE oth.other_firm_seq_num = emp.employer_other_firm_seq_num
   AND emp.legal_case_seq_num = aLegalCaseNum;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
         NULL;
END getOtherFirmList;

PROCEDURE GetEmployeebyMailid(in_MailId IN VARCHAR,
                      recordset OUT EmployeeCursor) AS
BEGIN
OPEN recordset FOR
SELECT
    EMPLID,
    LAST_NAME,
    FIRST_NAME,
    MIDDLE_NAME,
    EMAIL_ADDRESS,
    EMPL_STATUS,
    PHONE,
    PHONE_EXTENSION,
    LOCATION,
    PREFERRED_NAME,
    DEPTID,
    DESCR,
    REPORTS_TO,
    USER_ID
FROM LEGAL.ADP_EMPLOYEE
WHERE USER_ID = in_MailId;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
         NULL;
END GetEmployeebyMailid;

FUNCTION GetEmployeeName(in_mailid IN VARCHAR)
  RETURN VARCHAR2
AS
v_full_name VARCHAR2(50);
v_last_name adp_employee.last_name%TYPE;
v_first_name adp_employee.first_name%TYPE;
v_middle_name adp_employee.middle_name%TYPE;
v_preferred_name adp_employee.preferred_name%TYPE;
BEGIN
  BEGIN
    SELECT
      LTRIM(RTRIM(LAST_NAME)),
      LTRIM(RTRIM(FIRST_NAME)),
      LTRIM(RTRIM(MIDDLE_NAME)),
      LTRIM(RTRIM(PREFERRED_NAME))
     INTO v_last_name,
          v_first_name,
          v_middle_name,
          v_preferred_name
     FROM LEGAL.ADP_EMPLOYEE
    WHERE USER_ID = LTRIM(RTRIM(in_MailId));
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
         NULL;
END;

IF v_preferred_name IS NULL THEN
   v_full_name := v_first_name || ' ' || v_last_name;
ELSE
   v_full_name := v_preferred_name || ' ' || v_last_name;
END IF;

RETURN v_full_name;
END GetEmployeeName;


PROCEDURE IsLetterRequested(aLegalCaseNum IN NUMBER,
                           aLetterType   IN VARCHAR2,
                           out_requested OUT VARCHAR2)
AS
v_cnt NUMBER;
BEGIN

out_requested := 'N';

SELECT COUNT(*)
  INTO v_cnt
  FROM LETTER_REQUEST
 WHERE LETTER_TYPE_CODE = aLetterType
   AND legal_case_seq_num = aLegalCaseNum
   AND printed_date IS NULL;

IF v_cnt > 0 THEN
   out_requested := 'Y';
END IF;

END  IsLetterRequested;

PROCEDURE GetCaseCost(aLegalCaseNum IN  NUMBER,
                      recordset OUT CaseCostCursor)
 AS BEGIN
 OPEN recordset FOR
 SELECT case_cost_seq_num
      , legal_case_seq_num
      , service_from_date
      , service_to_date
      , SERVICE_CODE
      , amount_paid
      , amount_billed
      , amount_per_hour
      , hours_billed
      , DECODE(prepayment_flag, 'Y', 'True', 'False') AS prepayment_flag
      , authorized_date
      , payee_firm_prov_loc_id
      , payee_person_prov_loc_id
      , authorizer_emplid
      , CANCELLED_BY_CODE
      , CREATE_DATE
      , CREATE_USER
      , UPDATE_DATE
      , UPDATE_USER
   FROM CASE_COST
  WHERE legal_case_seq_num = aLegalCaseNum
  ORDER BY service_from_date;

 END getCaseCost;

 PROCEDURE case_cost_update
(
    case_cost_seq_num_in IN CASE_COST.case_cost_seq_num%TYPE,
    legal_case_seq_num_in IN CASE_COST.legal_case_seq_num%TYPE,
    service_from_date_in IN CASE_COST.service_from_date%TYPE,
    service_to_date_in IN CASE_COST.service_to_date%TYPE,
    service_code_in IN CASE_COST.SERVICE_CODE%TYPE,
    amount_paid_in IN CASE_COST.amount_paid%TYPE,
    amount_billed_in IN CASE_COST.amount_billed%TYPE,
    amount_per_hour_in IN CASE_COST.amount_per_hour%TYPE,
    hours_billed_in IN    CASE_COST.hours_billed%TYPE,
    prepayment_flag_in IN CASE_COST.prepayment_flag%TYPE,
    authorized_date_in IN CASE_COST.authorized_date%TYPE,
    payee_firm_prov_loc_id_in IN CASE_COST.PAYEE_firm_PROV_LOC_ID%TYPE,
    payee_person_prov_loc_id_in IN CASE_COST.payee_person_prov_loc_id%TYPE,
    authorizer_emplid_in IN CASE_COST.authorizer_emplid%TYPE,
    cancelled_by_code_in IN CASE_COST.CANCELLED_BY_CODE%TYPE,
    update_date_in IN CASE_COST.update_date%TYPE,
    update_user_in IN CASE_COST.update_user%TYPE
)
IS
BEGIN
    UPDATE LEGAL.CASE_COST SET
        legal_case_seq_num = legal_case_seq_num_in,
        service_from_date = service_from_date_in,
        service_to_date = service_to_date_in,
        SERVICE_CODE = service_code_in,
        amount_paid = amount_paid_in,
        amount_billed = amount_billed_in,
        amount_per_hour = amount_per_hour_in,
        hours_billed = hours_billed_in,
        prepayment_flag = prepayment_flag_in,
        authorized_date = authorized_date_in,
        payee_firm_prov_loc_id = payee_firm_prov_loc_id_in,
        PAYEE_PERSON_PROV_LOC_ID = payee_person_prov_loc_id_in,
        authorizer_emplid = authorizer_emplid_in,
        CANCELLED_BY_CODE = cancelled_by_code_in,
        update_date = update_date_in,
        update_user = update_user_in
    WHERE
        case_cost_seq_num = case_cost_seq_num_in;
END case_cost_update;

/********************************************
** INSERT PROCEDURE FOR TABLE "LEGAL.CASE_COST"
** DATE: 28-JUL-03 13:24:16
*********************************************/
PROCEDURE case_cost_insert
(
    case_cost_seq_num_in IN CASE_COST.case_cost_seq_num%TYPE,
    legal_case_seq_num_in IN CASE_COST.legal_case_seq_num%TYPE,
    service_from_date_in IN CASE_COST.service_from_date%TYPE,
    service_to_date_in IN CASE_COST.service_to_date%TYPE,
    service_code_in IN CASE_COST.SERVICE_CODE%TYPE,
    amount_paid_in IN CASE_COST.amount_paid%TYPE,
    amount_billed_in IN CASE_COST.amount_billed%TYPE,
    amount_per_hour_in IN CASE_COST.amount_per_hour%TYPE,
    hours_billed_in IN    CASE_COST.hours_billed%TYPE,
    prepayment_flag_in IN CASE_COST.prepayment_flag%TYPE,
    authorized_date_in IN CASE_COST.authorized_date%TYPE,
    payee_firm_prov_loc_id_in IN CASE_COST.PAYEE_firm_PROV_LOC_ID%TYPE,
    payee_person_prov_loc_id_in IN CASE_COST.payee_person_prov_loc_id%TYPE,
    authorizer_emplid_in IN CASE_COST.authorizer_emplid%TYPE,
    cancelled_by_code_in IN CASE_COST.CANCELLED_BY_CODE%TYPE,
    create_date_in IN CASE_COST.create_date%TYPE,
    create_user_in IN CASE_COST.create_user%TYPE)
IS
BEGIN
    INSERT INTO LEGAL.CASE_COST
    (
        case_cost_seq_num,
        legal_case_seq_num,
        service_from_date,
        service_to_date,
        SERVICE_CODE,
        amount_paid,
        amount_billed,
        amount_per_hour,
        hours_billed,
        prepayment_flag,
        authorized_date,
        payee_firm_prov_loc_id,
        PAYEE_PERSON_PROV_LOC_ID,
        authorizer_emplid,
        CANCELLED_BY_CODE,
        create_date,
        create_user
    )
    VALUES
    (
        case_cost_seq_num_in,
        legal_case_seq_num_in,
        service_from_date_in,
        service_to_date_in,
        service_code_in,
        amount_paid_in,
        amount_billed_in,
        amount_per_hour_in,
        hours_billed_in,
        prepayment_flag_in,
        authorized_date_in,
        payee_firm_prov_loc_id_in,
        payee_person_prov_loc_id_in,
        authorizer_emplid_in,
        cancelled_by_code_in,
        create_date_in,
        create_user_in
    );
END case_cost_insert;

/********************************************
** DELETE PROCEDURE FOR TABLE "LEGAL.CASE_COST"
** DATE: 02-DEC-03 16:12:45
*********************************************/
PROCEDURE case_cost_delete
(
    case_cost_seq_num_in IN CASE_COST.case_cost_seq_num%TYPE
)
IS
BEGIN
    DELETE FROM CASE_COST
    WHERE
        case_cost_seq_num = case_cost_seq_num_in;
END case_cost_delete;

Function DeleteLegalCase(LegalCaseSeqNum IN LEGAL_CASE.legal_case_Seq_num%TYPE)
  RETURN NUMBER
IS
   v_update_count             NUMBER;
BEGIN

-- Delete foreign keys first
BEGIN
DELETE FROM LEGAL_FILE_LOCATION
 WHERE legal_case_seq_num = LegalCaseSeqNum;
 EXCEPTION
    WHEN NO_DATA_FOUND THEN
    NULL;
END;

BEGIN
DELETE FROM LEGAL_CASE_EVALUATION
 WHERE legal_case_Seq_num = LegalCaseSeqNum;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
    NULL;
END;

BEGIN
DELETE FROM CONSOLIDATED_CASE
 WHERE primary_case_Seq_num = LegalCaseSeqNum
    OR secondary_case_seq_num = LegalCaseSeqNum;
 EXCEPTION
    WHEN NO_DATA_FOUND THEN
    NULL;
END;

BEGIN
DELETE FROM REQUEST_ISSUE
 WHERE legal_case_Seq_num = LegalCaseSeqNum;
 EXCEPTION
    WHEN NO_DATA_FOUND THEN
    NULL;
END;

BEGIN
DELETE FROM LETTER_REQUEST
 WHERE legal_case_Seq_num = LegalCaseSeqNum;
 EXCEPTION
    WHEN NO_DATA_FOUND THEN
    NULL;
END;

BEGIN
DELETE FROM LEGAL_CASE_EVALUATION
 WHERE legal_case_Seq_num = LegalCaseSeqNum;
 EXCEPTION
    WHEN NO_DATA_FOUND THEN
    NULL;
END;

BEGIN
DELETE FROM CASE_STATUS_HISTORY
 WHERE legal_case_Seq_num = LegalCaseSeqNum;
 EXCEPTION
    WHEN NO_DATA_FOUND THEN
    NULL;
END;

BEGIN
DELETE FROM LEGAL_CASE_EMPLOYER_CARRIER
 WHERE legal_case_Seq_num = LegalCaseSeqNum;
 EXCEPTION
    WHEN NO_DATA_FOUND THEN
    NULL;
END;

BEGIN
DELETE FROM LEGAL_CASE_EMPLOYER
 WHERE legal_case_Seq_num = LegalCaseSeqNum;
 EXCEPTION
    WHEN NO_DATA_FOUND THEN
    NULL;
END;

BEGIN
DELETE FROM OUTCOME_ISSUE i
WHERE i.case_outcome_seq_num IN
                 (SELECT case_outcome_seq_num
                       FROM CASE_OUTCOME o
                   WHERE o.legal_case_Seq_num = LegalCaseSeqNum);
 EXCEPTION
    WHEN NO_DATA_FOUND THEN
    NULL;
END;

BEGIN
DELETE FROM OUTCOME_STAFF s
WHERE s.case_outcome_seq_num IN
                 (SELECT case_outcome_seq_num
                       FROM CASE_OUTCOME o
                   WHERE o.legal_case_Seq_num = LegalCaseSeqNum);
 EXCEPTION
    WHEN NO_DATA_FOUND THEN
    NULL;
END;

BEGIN
DELETE FROM CASE_OUTCOME
 WHERE legal_case_Seq_num = LegalCaseSeqNum;
 EXCEPTION
    WHEN NO_DATA_FOUND THEN
    NULL;
END;

BEGIN
DELETE FROM CASE_NOTE
 WHERE legal_case_Seq_num = LegalCaseSeqNum;
 EXCEPTION
    WHEN NO_DATA_FOUND THEN
    NULL;
END;

BEGIN
DELETE FROM CASE_COST
 WHERE legal_case_Seq_num = LegalCaseSeqNum;
 EXCEPTION
    WHEN NO_DATA_FOUND THEN
    NULL;
END;

BEGIN
DELETE FROM SCHEDULE
 WHERE legal_case_Seq_num = LegalCaseSeqNum;
 EXCEPTION
    WHEN NO_DATA_FOUND THEN
    NULL;
END;

BEGIN
 DELETE FROM CLAIM_IMAGE_ALERTS_HISTORY
 WHERE legal_case_Seq_num = LegalCaseSeqNum;
 EXCEPTION
    WHEN NO_DATA_FOUND THEN
    NULL;
END;

BEGIN
DELETE FROM LEGAL_CASE
 WHERE legal_case_Seq_num = LegalCaseSeqNum;
END;
    v_update_count := SQL%Rowcount;
RETURN v_update_count;

END DeleteLegalCase;

PROCEDURE GetCaseNote(CaseId IN  NUMBER,
                      recordset OUT ref_cursor)
  AS BEGIN
  OPEN recordset FOR
  SELECT
        case_note_seq_num NoteId,
        legal_case_seq_num CaseId,
        NOTE_CATEGORY_CODE CategoryCode,
        text NoteText,
        create_date CreateDate,
        create_user CreateUser,
        update_date UpdateDate,
        update_user UpdateUser
    FROM CASE_NOTE
    WHERE
        legal_case_seq_num = CaseId
        order by Create_date desc;

END GetCaseNote;
/********************************************
** INSERT PROCEDURE FOR TABLE "LEGAL.CASE_NOTE"
** DATE: 06-AUG-03 10:20:33
*********************************************/
Function case_note_insert
(
    noteId          IN CASE_NOTE.case_note_seq_num%TYPE,
    caseId          IN CASE_NOTE.legal_case_seq_num%TYPE,
    categoryCode    IN CASE_NOTE.NOTE_CATEGORY_CODE%TYPE,
    Notetext        IN CASE_NOTE.text%TYPE,
    createDate      IN CASE_NOTE.create_date%TYPE,
    createUser      IN CASE_NOTE.create_user%TYPE)
    RETURN NUMBER
    IS
    v_update_count             NUMBER;
BEGIN
    INSERT INTO LEGAL.CASE_NOTE
    (
        case_note_seq_num,
        legal_case_seq_num,
        NOTE_CATEGORY_CODE,
        text,
        create_date,
        create_user
    )
    VALUES
    (
        noteId,
        caseId,
        categoryCode,
        Notetext,
        createdate,
        createUser
    );
    v_update_count := SQL%Rowcount;
    RETURN v_update_count;
END case_note_insert;
/********************************************
** UPDATE PROCEDURE FOR TABLE "LEGAL.CASE_NOTE"
** DATE: 06-AUG-03 10:21:50
*********************************************/
Function case_note_update
(
    noteId       IN CASE_NOTE.case_note_seq_num%TYPE,
    caseId       IN CASE_NOTE.legal_case_seq_num%TYPE,
    categoryCode IN CASE_NOTE.NOTE_CATEGORY_CODE%TYPE,
    NoteText     IN CASE_NOTE.text%TYPE,
    updateDate   IN CASE_NOTE.update_date%TYPE,
    updateUser   IN CASE_NOTE.update_user%TYPE)
    RETURN NUMBER
    IS
    v_update_count             NUMBER;
BEGIN
    UPDATE LEGAL.CASE_NOTE SET
        legal_case_seq_num = caseId,
        NOTE_CATEGORY_CODE = categoryCode,
        text = Notetext,
        update_date = updateDate,
        update_user = updateUser
    WHERE
        case_note_seq_num = noteId;
    v_update_count := SQL%Rowcount;
    RETURN v_update_count;
END case_note_update;

/********************************************
** DELETE PROCEDURE FOR TABLE "LEGAL.CASE_NOTE"
** DATE: 07-AUG-03 10:21:48
*********************************************/
Function case_note_delete
(
    noteId IN CASE_NOTE.case_note_seq_num%TYPE
) RETURN NUMBER

IS
v_update_count             NUMBER;
BEGIN
    DELETE FROM CASE_NOTE
    WHERE
        case_note_seq_num = noteId;

    v_update_count := SQL%Rowcount;
    RETURN v_update_count;
END case_note_delete;

PROCEDURE GetSAIFAtty(aLegalCaseNum IN  NUMBER,
                      recordset OUT ref_cursor)
  AS BEGIN
  OPEN recordset FOR
  SELECT pro.profile_emplid AS atty_emplid,
       aty.LAST_NAME || ', ' || DECODE(aty.preferred_name, ' ',
       aty.FIRST_NAME,aty.preferred_name) AS atty_name,
       aty.EMAIL_ADDRESS AS atty_email,
       aty.user_id AS aty_mailid,
       DECODE(aty.phone_extension,' ',SUBSTR(aty.PHONE,9), aty.phone_extension) AS aty_extension,
       sec.emplid AS sec_emplid,
       sec.LAST_NAME || ', ' || DECODE(sec.preferred_name, ' ',
       sec.FIRST_NAME,sec.preferred_name) AS sec_name,
       sec.EMAIL_ADDRESS AS sec_email,
       sec.user_id AS sec_mailid,
       DECODE(sec.phone_extension,' ',SUBSTR(sec.PHONE,9), sec.phone_extension) AS sec_extension,
       nvl(aty.phone, '503.285.8525') as AttorneyPhone,
       nvl(ATY.FAX_NUM, '5035849578') as AttorneyFax,
       DECODE(aty.preferred_name, ' ',
       aty.FIRST_NAME,aty.preferred_name)  || ' ' || aty.LAST_NAME  AS AttorneyNameFL
FROM ATTORNEY_PROFILE pro,
     adp_employee aty,
     adp_employee sec
WHERE aty.emplid (+)    = pro.profile_emplid
   AND sec.emplid (+) = pro.secretary_emplid
   AND pro.profile_emplid = get_asgn_atty_id(aLegalCaseNum);

END GetSAIFAtty;

PROCEDURE GetCaseOutcome(LegalCaseID IN  NUMBER,
                      recordset OUT ref_Cursor)
  AS BEGIN
  OPEN recordset FOR
SELECT cot.case_outcome_seq_num CaseOutcomeID,
       cot.legal_case_seq_num LegalCaseID,
       cot.ORDER_TYPE_CODE OrderTypeCode,
       cot.order_signed_date OrderSignedDate,
       cot.order_filed_date OrderFiledDate,
       cot.create_date CreateDate,
       cot.create_user CreateUser,
       cot.update_date UpdateDate,
       cot.update_user UpdateUser,
       NVL(cot.asgn_atty_emplid, '0') AS AsgnAttyEmplid,
       nvl(COT.APPEAL_SEQ_NUM, 0) as appealId
  FROM CASE_OUTCOME cot
 WHERE cot.legal_case_seq_num = LegalCaseID
 ORDER BY cot.order_signed_date;
END GetCaseOutcome;

PROCEDURE GetOutcomeIssue(aLegalCaseNum IN  NUMBER,
                      recordset OUT OutcomeIssueCursor)
  AS BEGIN
  OPEN recordset FOR
SELECT  OCI.outcome_issue_seq_num,
        OCI.case_outcome_seq_num,
        OCI.OUTCOME_ISSUE_CODE,
        OCI.win_loss_flag,
        OCI.fee_amount,
        DECODE(OCI.penalty_flag,'Y',1,0) AS penalty_flag,
        DECODE(OCI.primary_issue_flag,'Y',1,0) AS primary_issue_flag,
        OCI.create_date,
        OCI.create_user,
        OCI.summary_text,
        NVL(OCI.OUTCOME_GROUNDS_CODE,'0') AS OUTCOME_GROUNDS_CODE,
        NVL(OCI.LEGAL_BODY_PART_CODE,'0') AS LEGAL_BODY_PART_CODE,
        NVL(OCI.NATURE_CODE,'0') AS NATURE_CODE,
        OCI.update_date,
        OCI.update_user
    FROM OUTCOME_ISSUE OCI,
         CASE_OUTCOME COT
   WHERE cot.legal_case_seq_num = aLegalCaseNum
     AND OCI.case_outcome_seq_num = COT.case_outcome_seq_num
     ORDER BY OCI.outcome_issue_seq_num;
END GetOutcomeIssue;


PROCEDURE getAljList(aLegalCaseNum IN  NUMBER,
                      recordset OUT ALJListCursor)
  AS BEGIN
  OPEN recordset FOR
SELECT per.legal_person_seq_num, per.last_name,
       typ.LEGAL_PERSON_TYPE_CODE, cod.code_descr,0 AS Selected
 FROM LEGAL_PERSON per,
      LEGAL_PERSON_TYPE typ,
      LEGAL_PERSON_TYPE_CODE cod
WHERE typ.legal_person_seq_num = per.legal_person_seq_num
  AND TRUNC(SYSDATE) BETWEEN typ.eff_date
                         AND NVL(typ.exp_date, '31-dec-2999')
  AND typ.LEGAL_PERSON_TYPE_CODE IN ('ALJ','BOARD', 'FJ')
  AND cod.code = TYP.LEGAL_PERSON_TYPE_CODE
  ORDER BY selected DESC, last_name;
END getAljList;

PROCEDURE GetOutcomeStaff(aLegalCaseNum IN  NUMBER,
                      recordset OUT OutcomeStaffCursor)
  AS BEGIN
  OPEN recordset FOR
SELECT stf.outcome_staff_seq_num,
       stf.case_outcome_seq_num,
       stf.outcome_staff_person_seq_num,
       per.last_name
FROM OUTCOME_STAFF stf,
     LEGAL_PERSON per,
     CASE_OUTCOME cot
WHERE stf.outcome_staff_person_seq_num = per.legal_person_seq_num
 AND  stf.case_outcome_seq_num = cot.case_outcome_seq_num
 AND cot.legal_case_Seq_num = aLegalCaseNum
 ORDER BY stf.outcome_staff_seq_num;
END GetOutcomeStaff;

Function updateCaseOutcome( CaseOutcomeID IN CASE_OUTCOME.case_outcome_seq_num%TYPE,
                             LegalCaseID IN CASE_OUTCOME.legal_case_seq_num%TYPE,
                             OrderTypeCode IN CASE_OUTCOME.ORDER_TYPE_CODE%TYPE,
                             OrderSignedDate IN CASE_OUTCOME.order_signed_date%TYPE := NULL,
                             OrderFiledDate IN CASE_OUTCOME.order_filed_date%TYPE := NULL,
                             UpdateUser IN CASE_OUTCOME.update_user%TYPE := NULL,
                             AsgnAttyEmplid CASE_OUTCOME.asgn_atty_emplid%TYPE := NULL,
                             AppealId CASE_OUTCOME.APPEAL_SEQ_NUM%TYPE := Null)
                             return number
 AS

BEGIN

     UPDATE CASE_OUTCOME SET
        case_outcome_seq_num = CaseOutcomeID,
        legal_case_seq_num = legalcaseId,
        ORDER_TYPE_CODE = ordertypecode,
        order_signed_date = ordersigneddate,
        order_filed_date = orderfileddate,
        update_date = SYSDATE,
        update_user = UpdateUser,
        asgn_atty_emplid = AsgnAttyEmplid,
        appeal_seq_num = appealId
      WHERE
        case_outcome_seq_num = CaseOutcomeID;
 return SQL%Rowcount;
 end updateCaseOutcome;

 Function InsertCaseOutcome( CaseOutcomeID IN CASE_OUTCOME.case_outcome_seq_num%TYPE,
                             LegalCaseID IN CASE_OUTCOME.legal_case_seq_num%TYPE,
                             OrderTypeCode IN CASE_OUTCOME.ORDER_TYPE_CODE%TYPE,
                             OrderSignedDate IN CASE_OUTCOME.order_signed_date%TYPE := NULL,
                             OrderFiledDate IN CASE_OUTCOME.order_filed_date%TYPE := NULL,
                             UpdateUser IN CASE_OUTCOME.update_user%TYPE := NULL,
                             AsgnAttyEmplid CASE_OUTCOME.asgn_atty_emplid%TYPE := NULL,
                             AppealId CASE_OUTCOME.APPEAL_SEQ_NUM%TYPE := Null)
                             return number
 AS
BEGIN
    INSERT INTO CASE_OUTCOME
    (   case_outcome_seq_num,
        legal_case_seq_num,
        ORDER_TYPE_CODE,
        create_date,
        create_user,
        order_signed_date,
        order_filed_date,
        update_date,
        update_user,
        asgn_atty_emplid,
        appeal_seq_num
    )
    VALUES
    (   CaseOutcomeID,
        legalCaseId,
        orderTypeCode,
        SYSDATE,
        UpdateUser,
        orderSignedDate,
        orderFiledDate,
        SYSDATE,
        UpdateUser,
        AsgnAttyEmplid,
        appealId
    );
 return SQL%Rowcount;
END insertCaseOutcome;

PROCEDURE updateOutcomeIssue(outcome_issue_seq_num_in IN OUTCOME_ISSUE.outcome_issue_seq_num%TYPE,
    case_outcome_seq_num_in IN OUTCOME_ISSUE.case_outcome_seq_num%TYPE,
    outcome_issue_code_in IN OUTCOME_ISSUE.OUTCOME_ISSUE_CODE%TYPE,
    win_loss_flag_in IN OUTCOME_ISSUE.win_loss_flag%TYPE,
    fee_amount_in IN OUTCOME_ISSUE.fee_amount%TYPE,
    penalty_flag_in IN OUTCOME_ISSUE.penalty_flag%TYPE,
    primary_issue_flag_in IN OUTCOME_ISSUE.primary_issue_flag%TYPE,
    summary_text_in IN OUTCOME_ISSUE.summary_text%TYPE,
    outcome_grounds_code_in IN OUTCOME_ISSUE.OUTCOME_GROUNDS_CODE%TYPE,
    body_part_code_in IN OUTCOME_ISSUE.LEGAL_BODY_PART_CODE%TYPE,
    nature_code_in IN OUTCOME_ISSUE.NATURE_CODE%TYPE,
    userid_in IN OUTCOME_ISSUE.update_user%TYPE) AS
v_cnt NUMBER := 0;
BEGIN
BEGIN
SELECT COUNT(*)
  INTO v_cnt
  FROM OUTCOME_ISSUE
 WHERE outcome_issue_seq_num = outcome_issue_seq_num_in;
 EXCEPTION
    WHEN NO_DATA_FOUND THEN
    v_cnt := 0;
END;
BEGIN
  IF v_cnt > 0 THEN
     UPDATE OUTCOME_ISSUE SET
        outcome_issue_seq_num = outcome_issue_seq_num_in,
        case_outcome_seq_num = case_outcome_seq_num_in,
        OUTCOME_ISSUE_CODE = outcome_issue_code_in,
        win_loss_flag = win_loss_flag_in,
        fee_amount = fee_amount_in,
        penalty_flag = penalty_flag_in,
        summary_text = summary_text_in,
        primary_issue_flag = primary_issue_flag_in,
        OUTCOME_GROUNDS_CODE = outcome_grounds_code_in,
        LEGAL_BODY_PART_CODE = body_part_code_in,
        NATURE_CODE = nature_code_in,
        update_date = SYSDATE,
        update_user = userid_in
    WHERE
        outcome_issue_seq_num = outcome_issue_seq_num_in;
  END IF;
END;
BEGIN
IF v_cnt = 0 THEN
INSERT INTO OUTCOME_ISSUE
    (   outcome_issue_seq_num,
        case_outcome_seq_num,
        OUTCOME_ISSUE_CODE,
        win_loss_flag,
        fee_amount,
        penalty_flag,
        summary_text,
        primary_issue_flag,
        create_date,
        create_user,
        OUTCOME_GROUNDS_CODE,
        LEGAL_BODY_PART_CODE,
        NATURE_CODE,
        update_date,
        update_user
    )
    VALUES
    (   outcome_issue_seq_num_in,
        case_outcome_seq_num_in,
        outcome_issue_code_in,
        win_loss_flag_in,
        fee_amount_in,
        penalty_flag_in,
        summary_text_in,
        primary_issue_flag_in,
        SYSDATE,
        userid_in,
        outcome_grounds_code_in,
        body_part_code_in,
        nature_code_in,
        SYSDATE,
        userid_in
    );
END IF;
END;
END updateOutcomeIssue;

PROCEDURE updateOutcomeStaff( outcome_staff_seq_num_in IN OUTCOME_STAFF.outcome_staff_seq_num%TYPE,
                              case_outcome_seq_num_in IN OUTCOME_STAFF.case_outcome_seq_num%TYPE,
                              staff_person_seq_num_in IN OUTCOME_STAFF.outcome_staff_person_seq_num%TYPE )
AS

v_cnt NUMBER := 0;
BEGIN
BEGIN
SELECT COUNT(*)
  INTO v_cnt
  FROM OUTCOME_STAFF
 WHERE outcome_staff_seq_num = outcome_staff_seq_num_in;
 EXCEPTION
    WHEN NO_DATA_FOUND THEN
    v_cnt := 0;
END;
BEGIN
IF v_cnt > 0 THEN
   UPDATE OUTCOME_STAFF SET
        outcome_staff_seq_num = outcome_staff_seq_num_in,
        case_outcome_seq_num = case_outcome_seq_num_in,
        outcome_staff_person_seq_num = staff_person_seq_num_in
    WHERE
        outcome_staff_seq_num = outcome_staff_seq_num_in;
END IF;
END;
BEGIN
IF v_cnt = 0 THEN
   INSERT INTO LEGAL.OUTCOME_STAFF
    (  outcome_staff_seq_num,
       case_outcome_seq_num,
       outcome_staff_person_seq_num
    )
    VALUES
    (  outcome_staff_seq_num_in,
       case_outcome_seq_num_in,
       staff_person_seq_num_in
    );
END IF;
END;

END updateOutcomeStaff;

FUNCTION DeleteCaseOutcome(case_outcome_seq_num_in IN CASE_OUTCOME.case_outcome_seq_num%TYPE) return number
AS
BEGIN

--Delete child issues
BEGIN
DELETE FROM OUTCOME_ISSUE
 WHERE CASE_OUTCOME_SEQ_NUM = case_outcome_seq_num_in;
 EXCEPTION
    WHEN NO_DATA_FOUND THEN
    NULL;
END;

--Delete child staff
BEGIN
DELETE FROM OUTCOME_STAFF
 WHERE CASE_OUTCOME_SEQ_NUM = case_outcome_seq_num_in;
 EXCEPTION
    WHEN NO_DATA_FOUND THEN
    NULL;
END;

--Now delete the outcome
BEGIN
DELETE FROM CASE_OUTCOME
 WHERE CASE_OUTCOME_SEQ_NUM = case_outcome_seq_num_in;
 EXCEPTION
    WHEN NO_DATA_FOUND THEN
    NULL;
END;
RETURN SQL%Rowcount;
END DeleteCaseOutcome;


PROCEDURE DeleteOutcomeIssue(outcome_issue_seq_num_in IN OUTCOME_ISSUE.outcome_issue_seq_num%TYPE)
AS
BEGIN
DELETE FROM OUTCOME_ISSUE
 WHERE outcome_issue_seq_num = outcome_issue_seq_num_in;
 EXCEPTION
    WHEN NO_DATA_FOUND THEN
    NULL;
END DeleteOutcomeIssue;

PROCEDURE DeleteOutcomeStaff(outcome_staff_seq_num_in IN OUTCOME_STAFF.outcome_staff_seq_num%TYPE) AS
BEGIN
DELETE FROM OUTCOME_STAFF
 WHERE outcome_staff_seq_num = outcome_staff_seq_num_in;
 EXCEPTION
    WHEN NO_DATA_FOUND THEN
    NULL;
END DeleteOutcomeStaff;
-----
PROCEDURE SetFinalOutcome( legal_case_seq_num_in IN LEGAL_CASE.legal_case_seq_num%TYPE) AS
v_final_seq NUMBER := 0;
BEGIN
-- Get the Seq Num of the Final Outcome
BEGIN
SELECT MAX(case_outcome_seq_num)
  INTO v_final_seq
  FROM CASE_OUTCOME
  WHERE legal_case_seq_num = legal_case_seq_num_in
    AND order_signed_date  =
        (SELECT MAX(order_signed_date)
           FROM CASE_OUTCOME
           WHERE legal_case_seq_num = legal_case_seq_num_in);
           EXCEPTION
    WHEN NO_DATA_FOUND THEN
    NULL;
END;
-- Set the Final Flag
BEGIN
  UPDATE CASE_OUTCOME
     SET final_order_flag = 'N'
   WHERE legal_case_seq_num = legal_case_seq_num_in
     AND case_outcome_seq_num <> v_final_seq;
   UPDATE CASE_OUTCOME
     SET final_order_flag = 'Y'
   WHERE legal_case_Seq_num =  legal_case_seq_num_in
     AND case_outcome_seq_num = v_final_seq;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
    NULL;
END;
END SetFinalOutcome;


-----


PROCEDURE GetLegalCaseEmployer(aLegalCaseNum IN  NUMBER,
                      recordset OUT LegalCaseEmployerCursor)
AS BEGIN OPEN recordset FOR
 SELECT legal_case_employer_seq_num,
        legal_case_seq_num,
        employer_other_firm_seq_num,
        DECODE(employer_discovery_flag,'Y','true','false') AS employer_discovery_flag,
        DECODE(attorney_discovery_flag, 'Y','true','false') AS attorney_discovery_flag,
        attorney_other_firm_seq_num,
        attorney_legal_person_seq_num
    FROM LEGAL_CASE_EMPLOYER
   WHERE legal_case_seq_num = aLegalCaseNum;

END getLegalCaseEmployer;

/********************************************
** INSERT PROCEDURE FOR TABLE "LEGAL.LEGAL_CASE_EMPLOYER"
** DATE: 03-SEP-03 08:41:55
*********************************************/
PROCEDURE legal_case_employer_insert
(
    legal_case_employer_seq_num_in IN LEGAL_CASE_EMPLOYER.legal_case_employer_seq_num%TYPE,
    legal_case_seq_num_in IN LEGAL_CASE_EMPLOYER.legal_case_seq_num%TYPE,
    other_firm_seq_num_in IN LEGAL_CASE_EMPLOYER.employer_other_firm_seq_num%TYPE,
    discovery_flag_in IN LEGAL_CASE_EMPLOYER.employer_discovery_flag%TYPE,
    attorney_discovery_flag_in IN LEGAL_CASE_EMPLOYER.attorney_discovery_flag%TYPE,
    atty_other_firm_seq_num_in IN LEGAL_CASE_EMPLOYER.attorney_other_firm_seq_num%TYPE,
    atty_legal_person_seq_num_in IN LEGAL_CASE_EMPLOYER.attorney_legal_person_seq_num%TYPE
)
IS
BEGIN
    INSERT INTO LEGAL_CASE_EMPLOYER
    (
        legal_case_employer_seq_num,
        legal_case_seq_num,
        employer_other_firm_seq_num,
        employer_discovery_flag,
        attorney_discovery_flag,
        attorney_other_firm_seq_num,
        attorney_legal_person_seq_num
    )
    VALUES
    (
        legal_case_employer_seq_num_in,
        legal_case_seq_num_in,
        other_firm_seq_num_in,
        discovery_flag_in,
        attorney_discovery_flag_in,
        atty_other_firm_seq_num_in,
        atty_legal_person_seq_num_in
    );
END legal_case_employer_insert;

/********************************************
** UPDATE PROCEDURE FOR TABLE "LEGAL.LEGAL_CASE_EMPLOYER"
** DATE: 03-SEP-03 08:43:30
*********************************************/
PROCEDURE legal_case_employer_update
(
    legal_case_employer_seq_num_in IN LEGAL_CASE_EMPLOYER.legal_case_employer_seq_num%TYPE,
    legal_case_seq_num_in IN LEGAL_CASE_EMPLOYER.legal_case_seq_num%TYPE,
    other_firm_seq_num_in IN LEGAL_CASE_EMPLOYER.employer_other_firm_seq_num%TYPE,
    discovery_flag_in IN LEGAL_CASE_EMPLOYER.employer_discovery_flag%TYPE,
    attorney_discovery_flag_in IN LEGAL_CASE_EMPLOYER.attorney_discovery_flag%TYPE,
    atty_other_firm_seq_num_in IN LEGAL_CASE_EMPLOYER.attorney_other_firm_seq_num%TYPE,
    atty_legal_person_seq_num_in IN LEGAL_CASE_EMPLOYER.attorney_legal_person_seq_num%TYPE
)
IS
BEGIN
    UPDATE LEGAL.LEGAL_CASE_EMPLOYER SET
        legal_case_seq_num = legal_case_seq_num_in,
        employer_other_firm_seq_num = other_firm_seq_num_in,
        employer_discovery_flag = discovery_flag_in,
        attorney_discovery_flag = attorney_discovery_flag_in,
        attorney_other_firm_seq_num = atty_other_firm_seq_num_in,
        attorney_legal_person_seq_num = atty_legal_person_seq_num_in
    WHERE
        legal_case_employer_seq_num = legal_case_employer_seq_num_in;
END legal_case_employer_update;

-- Get the Attorney Address for Printing on Letters
-- IF an attorney exists for the case, get the address from provider
-- Otherwise get the worker address
PROCEDURE GetWorkerAtty(recordset OUT ref_cursor, inFirmLocID IN NUMBER, inAttyLocID IN NUMBER)
AS
v_addr_cur Pk82002_Provider.address_cursor;
v_addr_rec Pk82002_Provider.address_rec;

outFirmName VARCHAR2(100):= '';
outAttyName VARCHAR2(100):= '';
outAddr1 VARCHAR2(100):= '';
outAddr2 VARCHAR2(100):= '';
outAddr3 VARCHAR2(100):= '';
outAddr4 VARCHAR2(100):= '';
outPhone VARCHAR2(100):= '';
BEGIN

IF inFirmLocID > 0 THEN
   -- Get the Firm Address
   Pk82002_Provider.getLocByID(inFirmLocID,'COR',v_addr_cur);
   LOOP
    BEGIN
            FETCH v_addr_cur INTO v_addr_rec;
            EXIT WHEN v_addr_cur%NOTFOUND;
            outFirmName := Sp79013_Initcap_Name(v_addr_rec.location_name);
            outAddr1 := v_addr_rec.addr_line1;
            outAddr2 := v_addr_rec.addr_line2;
            outAddr3 := v_addr_rec.addr_line3;
            outAddr4 := v_addr_rec.addr_line4;
            outPhone := v_addr_rec.phone_num;
            IF v_addr_rec.foreign_addr_flag <> 'Y' THEN
                outAddr4 := v_addr_rec.city_st;
            END IF;
     END;
   END LOOP;
   IF inAttyLocID > 0 THEN
      BEGIN
        outAttyName := Sp79013_Initcap_Name(Pk82002_Provider.getProviderName(inAttyLocID));
      EXCEPTION
        WHEN NO_DATA_FOUND THEN
          outAttyName := '';
      END;
   END IF;
END IF;

OPEN recordset FOR
SELECT /*+ FIRST_ROWS */
            outFirmName FirmName,
            outAttyName AttyName,
            outAddr1 Addr1,
            outAddr2 Addr2,
            outAddr3 Addr3,
            outAddr4 Addr4,
            outPhone Phone
            FROM DUAL;
END GetWorkerAtty;

PROCEDURE GetAttyLeaveReport(inStartDate IN DATE,
                             inStopDate IN DATE,
                             recordset OUT AttyLeaveCursor)
AS BEGIN OPEN recordset FOR
SELECT /*+ FIRST_ROWS */  schedule_emplid,
       MAX(RTRIM(E.last_name) || ', ' || RTRIM(DECODE(NVL(E.preferred_name,' '), ' ', E.FIRST_NAME,E.preferred_name))) AS ATTY_NAME,
       SUM(DECODE(ATTORNEY_SCHEDULE_TYPE_CODE,'VAC', Sp72219_Day_Count(GREATEST(inStartDate,eff_Date),LEAST(inStopDate, exp_date))+ 1, 0)) AS vac_days,
       SUM(DECODE(ATTORNEY_SCHEDULE_TYPE_CODE,'LEAVE', Sp72219_Day_Count(GREATEST(inStartDate,eff_Date),LEAST(inStopDate, exp_date))+ 1, 0)) AS med_days
       FROM ATTORNEY_SCHEDULE s, adp_employee E
       WHERE E.emplid = s.schedule_emplid
       AND  ATTORNEY_SCHEDULE_TYPE_CODE IN ('VAC', 'LEAVE')
       AND  DURATION_CODE = 'FULL'
       AND ( (TRUNC(eff_Date) BETWEEN inStartDate AND inStopDate)
       OR    (TRUNC(exp_date) BETWEEN inStartDate AND inStopDate)
       OR    (TRUNC(eff_Date) < inStartDate AND TRUNC(exp_date) > inStopDate))
       GROUP BY schedule_emplid
       ORDER BY 2;
END GetAttyLeaveReport;

PROCEDURE alertlegalcase(
   inClaimId   IN   NUMBER
, outAlertFlag OUT VARCHAR2)

IS
   v_cases    NUMBER      := 0;
   v_return   VARCHAR2(1) := 'N';

   CURSOR check_legal_cases
   IS
      SELECT COUNT(*)
        FROM LEGAL_CASE lc
       WHERE lc.claim_image_alerts_flag = 'Y'
         AND lc.claim_id = inClaimId;
BEGIN
   IF NOT check_legal_cases%ISOPEN THEN
      OPEN check_legal_cases;
   END IF;

   FETCH check_legal_cases
    INTO v_cases;

   IF check_legal_cases%FOUND THEN
      IF v_cases > 0 THEN
         v_return                   := 'Y';
      END IF;
   END IF;

   IF check_legal_cases%ISOPEN THEN
      CLOSE check_legal_cases;
   END IF;

   outAlertFlag := v_return;
END alertlegalcase;

PROCEDURE SelectImageAlertsHist(inCaseSeqNum IN NUMBER, recordset OUT ref_cursor) AS
BEGIN
OPEN recordset FOR
          SELECT claim_image_alrt_hist_seq_num, legal_case_Seq_num, claim_image_alerts_flag, update_date, update_user
              FROM CLAIM_IMAGE_ALERTS_HISTORY
          WHERE legal_case_Seq_num = inCaseSeqNum
    ORDER BY update_Date DESC;
END SelectImageAlertsHist;

PROCEDURE SelectLegalEmployees(recordset OUT ref_cursor) AS
BEGIN
OPEN recordset FOR
    SELECT    LAST_NAME  || ', '  ||
                 DECODE (NVL (preferred_name, ' '), ' ', FIRST_NAME, preferred_name)  AS EMPLOYEE_NAME,
         USER_ID,  EMPLID,  DESCR,  NVL (ATTORNEY_TYPE_CODE, 'NA') AS ATTORNEY_TYPE_CODE
    FROM    ADP_EMPLOYEE A
         LEFT OUTER JOIN ATTORNEY_PROFILE P ON A.EMPLID = P.PROFILE_EMPLID
   WHERE (DEPTID = '005201'
        OR  (DEPTID = '001101' AND UPPER (DESCR) LIKE '%LEGAL%'))
         AND EMPL_STATUS = 'A'
ORDER BY EMPLOYEE_NAME;

END SelectLegalEmployees;

PROCEDURE getPolicyAddresses(inPolicyNum IN NUMBER,
       recordset OUT ref_cursor) AS
       BEGIN
OPEN recordset FOR
select   loc_seq_num as PolicyLocSeqNum, LOC.location_code as code,
  loc.primary_location_flag as primaryFlag
 ,LCD.SHORT_CODE_DESCR as PolicyLocCode
 from location loc
 left outer join location_code lcd on LOC.LOCATION_CODE = LCD.CODE
 where policy_num =  inPolicyNum
 and to_char(sysdate, 'yyyy/mm/dd') >= to_char(loc_eff_date,'yyyy/mm/dd')
 and to_char(sysdate, 'yyyy/mm/dd') <= nvl(to_char(loc_exp_date,'yyyy/mm/dd'), to_char(sysdate,'yyyy/mm/dd'))
 order by decode(LOC.LOCATION_CODE,'CLM','1','POL','2','BUS','3','4'), primary_location_flag desc;
 END getPolicyAddresses;
END;

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

• Procedure catalog
• Business rules
• Operations and processes
• SQL catalog

Sources

• Oracle package specification and body: WCIS_DBA.pk80001_legalnet (retrieved via legends oracle query --owner SAIF --name pk80001_legalnet on 2026-08-31)
• Legacy calling-class mapping cross-checked against Claims.BOF.Web.LegalMaintenance VB.NET/.NET class usage