pk80001_legalnet

WCIS_DBA.pk80001_legalnet is present in Oracle and exposes the LegalNet package routines used by this library for appeal, case-note, case-outcome, legal-case, schedule, file-location, acknowledgement, mail-date, and consolidation workflows.

Overview

Field	Value
Schema	WCIS_DBA
Type	Package
Database	Oracle (Case Management)
Calling libraries	SAIF.Business.Claim.Legal
Calling classes	clsAcknowledge, clsAppeal, clsAppealCollection, clsAppealExtension, clsCaseNote, clsCaseOutcome, clsCaseOutcomeCollection, clsConsolidations, clsCreateFile, clsLegalCase, clsLegalCaseEvaluation, clsLegalFileLocation, clsLegalSchedule, clsMailDate

Purpose

This package centralizes server-side persistence, retrieval, and derived-update logic for legal cases and closely related entities. The confirmed routines cover appeal and appeal-extension CRUD, case-note and case-outcome maintenance, hearing schedule retrieval, file-location history, acknowledgement updates, mail-date stamping, consolidation updates, and shared legal-case status/hearing recalculation helpers.

Procedures / Methods

Appeals and appeal extensions

Routine	Type	Called by	Description
getAppeal	Procedure	clsAppealCollection.MyRetrieveSQL, clsAppeal.BeforeRetrieve	Returns all appeal rows for a legal case ordered by appeal_seq_num.
insertAppeal	Function	clsAppeal.InsertSQL	Inserts an APPEAL row including review-level, filing, brief, attorney, and linked schedule metadata.
updateAppeal	Function	clsAppeal.UpdateSQL	Updates the persisted appeal row and returns SQL%ROWCOUNT.
deleteappeal	Function	clsAppeal.DeleteSQL	Deletes the appeal, clears dependent extensions, optionally deletes the linked schedule, and re-evaluates downstream case/file state.
getAppealExtension	Procedure	clsAppealExtension.MyRetrieveSQL	Returns all extension requests for an appeal.
insertAppealExtension	Function	clsAppealExtension.InsertSQL	Inserts an APPEAL_EXTENSION row with request date and requested days.
updateAppealExtension	Function	clsAppealExtension.UpdateSQL	Updates an existing extension request.

Case notes and outcomes

Routine	Type	Called by	Description
GetCaseNote	Procedure	clsCaseNote.RetrieveSQL	Retrieves case notes for a legal case ordered by newest create_date first.
case_note_insert	Function	clsCaseNote.InsertSQL	Inserts a case note row.
case_note_update	Function	clsCaseNote.UpdateSQL	Updates the category, text, and audit fields on a case note.
case_note_delete	Function	clsCaseNote.DeleteSQL	Deletes a case note by sequence number.
GetCaseOutcome	Procedure	clsCaseOutcomeCollection.MyRetrieveSQL	Returns case-outcome rows including assigned attorney and linked appeal metadata.
InsertCaseOutcome	Function	clsCaseOutcome.InsertSQL	Inserts a case outcome and stamps both create/update audit columns.
updateCaseOutcome	Function	clsCaseOutcome.UpdateSQL	Updates an existing case outcome and refreshes update_date to SYSDATE.
DeleteCaseOutcome	Function	clsCaseOutcome.DeleteSQL	Deletes child OUTCOME_ISSUE and OUTCOME_STAFF rows before deleting the parent outcome.

Legal case, schedule, file, and correspondence helpers

Routine	Type	Called by	Description
getLCE, insertLCE, updateLCE	Procedure, function, function	clsLegalCaseEvaluation	Retrieves and persists legal-case evaluation rows.
getLegalCase, InsertLegalCase, UpdateLegalCase, DeleteLegalCase	Procedure, function, function, function	clsLegalCase	Retrieves and maintains the core legal-case header row.
getSchedule, insertSchedule, updateSchedule, deleteSchedule	Procedure, function, function, function	clsLegalSchedule	Retrieves and maintains hearing/schedule rows, including consolidated-case visibility.
getFileLocation, insertFileLocation, updateFileLocation	Procedure, function, function	clsLegalFileLocation	Retrieves file-location history and persists location changes.
updateAcknowledged	Function	clsAcknowledge.BeforeUpdate	Updates either the WCB or DCBS case number plus acknowledge_date.
setFileCreateDate	Procedure	clsCreateFile.UpdateCreateFile	Stamps file-creation metadata for a legal case.
setMailOARFlag, setMailDate, mailDateList, setNoMailDate	Function, procedure, procedure, function	clsMailDate	Supports mail-date lookup and updates for OAR and recon packet workflows.
updateCaseStatus, updateHearingSeqNum, UpdateConsolidatedCase	Procedure, procedure, procedure	clsConsolidations, clsLegalCase	Keeps consolidated cases, hearing pointers, and case status aligned after structural changes.

Business Rules

BR-CLM-LGL-026 Legal case search falls through case number hierarchy

Field	Value
Statement	findCase searches by wcb_num first, then by dcbs_num only when the input length is 7 or less, then by owcp_num, oalj_num, brb_num, and finally by APPEAL.appeal_num.
Trigger	Caller invokes the package search routine with a case-number-like value.
Enforcement	findCase executes a chained COUNT(*) / OPEN recordset FOR sequence across those identifiers.
Outcome	The first matching identifier path determines the returned legal-case recordset.
Source	WCIS_DBA.pk80001_legalnet.findCase

BR-CLM-LGL-027 Case status propagates to consolidated secondary cases

Field	Value
Statement	Updating a primary legal case status also updates every LEGAL_CASE row whose sequence appears as a CONSOLIDATED_CASE.secondary_case_seq_num for that primary case.
Trigger	updateCaseStatus is called for a legal case.
Enforcement	The routine performs one direct LEGAL_CASE update and a second update driven by CONSOLIDATED_CASE.
Outcome	Consolidated secondary cases carry the same status and audit values as the primary case.
Source	WCIS_DBA.pk80001_legalnet.updateCaseStatus

BR-CLM-LGL-028 Appeal deletion cascades schedule and state cleanup

Field	Value
Statement	Deleting an appeal first deletes its extension rows, then the appeal row, then deletes the linked schedule when one exists, recalculates the most recent hearing sequence, and may repopulate case status or file location when review-level or attorney/file-location state changes.
Trigger	deleteappeal is called for an appeal sequence number.
Enforcement	deleteappeal calls DELETE on APPEAL_EXTENSION and APPEAL, invokes deleteschedule and updatehearingseqnum, and conditionally calls popcasestatus / popfilelocation.
Outcome	Appeal removal cascades dependent cleanup instead of leaving orphaned schedule or file-state data.
Source	WCIS_DBA.pk80001_legalnet.deleteappeal

BR-CLM-LGL-029 Hearing sequence fallback prefers consolidated HEAR then direct HEAR then other hearing-like schedules

Field	Value
Statement	The most-recent hearing sequence is resolved first from a consolidated primary case HEAR row, then from the case's own HEAR row, and finally from CCALL, INCON, or MED schedules if no HEAR row exists.
Trigger	updateHearingSeqNum recalculates LEGAL_CASE.most_recent_hearing_seq_num.
Enforcement	The routine performs three ordered MAX(schedule_seq_num) lookups before updating the case and its consolidated secondaries.
Outcome	Legal cases retain a best-available hearing pointer even when a standard HEAR row is absent.
Source	WCIS_DBA.pk80001_legalnet.updateHearingSeqNum

BR-CLM-LGL-030 Schedule retrieval includes consolidated-primary schedules for secondary cases

Field	Value
Statement	getSchedule returns both schedules directly owned by the requested legal case and schedules from the primary case when the requested case is a consolidated secondary case.
Trigger	getSchedule is called for a legal case sequence number.
Enforcement	The routine UNIONs a direct SCHEDULE query with a SCHEDULE + CONSOLIDATED_CASE query keyed by secondary_case_seq_num.
Outcome	Consolidated secondary cases see the primary hearing schedule in the returned recordset.
Source	WCIS_DBA.pk80001_legalnet.getSchedule

BR-CLM-LGL-031 Mail and acknowledgement updates are type-driven

Field	Value
Statement	setMailDate updates oar_mailed_date only for inType = 'OAR' and otherwise updates recon_packet_received_date, while updateAcknowledged only updates WCB or DCBS identifiers plus acknowledge_date.
Trigger	Mail-date or acknowledgement routines are invoked from the library.
Enforcement	setMailDate branches on inType; updateAcknowledged branches on inType = 'WCB' and inType = 'DCBS'.
Outcome	Correspondence updates touch only the status/date columns associated with the caller's declared identifier type.
Source	WCIS_DBA.pk80001_legalnet.setMailDate, WCIS_DBA.pk80001_legalnet.updateAcknowledged

Operations

Manage appeal records and extensions

Field	Value
Description	Creates, updates, retrieves, and deletes APPEAL rows and their APPEAL_EXTENSION child rows, including cleanup of linked schedules during appeal deletion.
Key routines	getAppeal, insertAppeal, updateAppeal, deleteappeal, getAppealExtension, insertAppealExtension, updateAppealExtension
Triggered by	clsAppeal, clsAppealCollection, clsAppealExtension

Maintain case notes and outcomes

Field	Value
Description	Retrieves and persists case notes and case outcomes, and deletes dependent issue/staff rows before removing a case outcome.
Key routines	GetCaseNote, case_note_insert, case_note_update, case_note_delete, GetCaseOutcome, InsertCaseOutcome, updateCaseOutcome, DeleteCaseOutcome
Triggered by	clsCaseNote, clsCaseOutcome, clsCaseOutcomeCollection

Maintain hearing schedules and consolidated case state

Field	Value
Description	Retrieves schedule rows, recalculates most-recent hearing pointers, propagates primary-case status, and aligns consolidation metadata across primary and secondary cases.
Key routines	getSchedule, insertSchedule, updateSchedule, deleteSchedule, updateCaseStatus, updateHearingSeqNum, UpdateConsolidatedCase
Triggered by	clsLegalSchedule, clsConsolidations, clsLegalCase

Stamp acknowledgements, file creation, and mail dates

Field	Value
Description	Updates case numbering acknowledgements, file-creation dates, file-location history, OAR flags, and mail/recon dates used by downstream legal correspondence workflows.
Key routines	updateAcknowledged, setFileCreateDate, getFileLocation, insertFileLocation, updateFileLocation, setMailOARFlag, setMailDate, mailDateList, setNoMailDate
Triggered by	clsAcknowledge, clsCreateFile, clsLegalFileLocation, clsMailDate

Calling Code References

Procedure	Class	Method/Property	Source File	Matched Oracle routine	Match
updateAcknowledged	clsAcknowledge	UpdateSQL/BeforeUpdate	SAIF.Business.Claim.Legal\LegalNet\clsAcknowledge.vb	updateAcknowledged	Exact
insertAppeal	clsAppeal	InsertSQL/BeforeInsert	SAIF.Business.Claim.Legal\LegalNet\clsAppeal.vb	insertAppeal	Exact
updateAppeal	clsAppeal	UpdateSQL/BeforeUpdate	SAIF.Business.Claim.Legal\LegalNet\clsAppeal.vb	updateAppeal	Exact
DeleteAppeal	clsAppeal	DeleteSQL/BeforeDelete	SAIF.Business.Claim.Legal\LegalNet\clsAppeal.vb	deleteappeal	Exact
getAppeal	clsAppealCollection	MyRetrieveSQL/BeforeRetrieve	SAIF.Business.Claim.Legal\LegalNet\clsAppeal.vb	getAppeal	Exact
insertAppealExtension	clsAppealExtension	InsertSQL/BeforeInsert	SAIF.Business.Claim.Legal\LegalNet\clsAppeal.vb	insertAppealExtension	Exact
updateAppealExtension	clsAppealExtension	UpdateSQL/BeforeUpdate	SAIF.Business.Claim.Legal\LegalNet\clsAppeal.vb	updateAppealExtension	Exact
GetCaseNote	clsCaseNote	RetrieveSQL/BeforeRetrieve	SAIF.Business.Claim.Legal\LegalNet\clsCaseNote.vb	GetCaseNote	Exact
case_note_insert	clsCaseNote	InsertSQL/BeforeInsert	SAIF.Business.Claim.Legal\LegalNet\clsCaseNote.vb	case_note_insert	Exact
case_note_update	clsCaseNote	UpdateSQL/BeforeUpdate	SAIF.Business.Claim.Legal\LegalNet\clsCaseNote.vb	case_note_update	Exact
case_note_delete	clsCaseNote	DeleteSQL/BeforeDelete	SAIF.Business.Claim.Legal\LegalNet\clsCaseNote.vb	case_note_delete	Exact
GetCaseOutcome	clsCaseOutcomeCollection	MyRetrieveSQL/BeforeRetrieve	SAIF.Business.Claim.Legal\LegalNet\clsCaseOutcome.vb	GetCaseOutcome	Exact
InsertCaseOutcome	clsCaseOutcome	InsertSQL/BeforeInsert	SAIF.Business.Claim.Legal\LegalNet\clsCaseOutcome.vb	InsertCaseOutcome	Exact
updateCaseOutcome	clsCaseOutcome	UpdateSQL/BeforeUpdate	SAIF.Business.Claim.Legal\LegalNet\clsCaseOutcome.vb	updateCaseOutcome	Exact
DeleteCaseOutcome	clsCaseOutcome	DeleteSQL/BeforeDelete	SAIF.Business.Claim.Legal\LegalNet\clsCaseOutcome.vb	DeleteCaseOutcome	Exact
getLCE	clsLegalCaseEvaluation	MyRetrieveSQL/BeforeRetrieve	SAIF.Business.Claim.Legal\LegalNet\clsLegalCaseEvaluation.vb	getLCE	Exact
InsertLCE*	clsLegalCaseEvaluation	InsertSQL/BeforeInsert	SAIF.Business.Claim.Legal\LegalNet\clsLegalCaseEvaluation.vb	insertLCE	Exact
updateLCE*	clsLegalCaseEvaluation	UpdateSQL/BeforeUpdate	SAIF.Business.Claim.Legal\LegalNet\clsLegalCaseEvaluation.vb	updateLCE	Exact
getFileLocation	clsLegalFileLocation	MyRetrieveSQL/BeforeRetrieve	SAIF.Business.Claim.Legal\LegalNet\clsLegalFileLocation.vb	getFileLocation	Exact
insert proc* (name not captured)	clsLegalFileLocation	InsertSQL/BeforeInsert	SAIF.Business.Claim.Legal\LegalNet\clsLegalFileLocation.vb	insertFileLocation	Inferred from class responsibility*
update proc* (name not captured)	clsLegalFileLocation	UpdateSQL/BeforeUpdate	SAIF.Business.Claim.Legal\LegalNet\clsLegalFileLocation.vb	updateFileLocation	Inferred from class responsibility*
getSchedule*	clsLegalSchedule	RetrieveSQL/BeforeRetrieve	SAIF.Business.Claim.Legal\LegalNet\clsLegalSchedule.vb	getSchedule	Exact
insertSchedule*	clsLegalSchedule	InsertSQL/BeforeInsert	SAIF.Business.Claim.Legal\LegalNet\clsLegalSchedule.vb	insertSchedule	Exact
updateSchedule*	clsLegalSchedule	UpdateSQL/BeforeUpdate	SAIF.Business.Claim.Legal\LegalNet\clsLegalSchedule.vb	updateSchedule	Exact
deleteSchedule*	clsLegalSchedule	DeleteSQL/BeforeDelete	SAIF.Business.Claim.Legal\LegalNet\clsLegalSchedule.vb	deleteSchedule	Exact
getLegalCase*	clsLegalCase	RetrieveSQL/BeforeRetrieve	SAIF.Business.Claim.Legal\LegalNet\clsLegalCase.vb	getLegalCase	Exact
InsertLegalCase*	clsLegalCase	InsertSQL/BeforeInsert	SAIF.Business.Claim.Legal\LegalNet\clsLegalCase.vb	InsertLegalCase	Exact
UpdateLegalCase*	clsLegalCase	UpdateSQL/BeforeUpdate	SAIF.Business.Claim.Legal\LegalNet\clsLegalCase.vb	UpdateLegalCase	Exact
DeleteLegalCase*	clsLegalCase	DeleteSQL/BeforeDelete	SAIF.Business.Claim.Legal\LegalNet\clsLegalCase.vb	DeleteLegalCase	Exact
updateHearingSeqNum	clsLegalCase	UpdateHearingSeq (custom method)	SAIF.Business.Claim.Legal\LegalNet\clsLegalCase.vb	updateHearingSeqNum	Exact
setFileCreateDate	clsCreateFile	UpdateCreateFile (custom method)	SAIF.Business.Claim.Legal\LegalNet\clsCreateFile.vb	setFileCreateDate	Exact
setMailOARFlag	clsMailDate	UpdateSQL/BeforeUpdate	SAIF.Business.Claim.Legal\LegalNet\clsMailDate.vb	setMailOARFlag	Exact
setMailDate*	clsMailDate	setMailDate (list-level custom method)	SAIF.Business.Claim.Legal\LegalNet\clsMailDate.vb	setMailDate	Exact
UpdateConsolidatedCase	clsConsolidations	custom method	SAIF.Business.Claim.Legal\LegalNet\clsConsolidations.vb	UpdateConsolidatedCase	Exact
updateCaseStatus	clsConsolidations	custom method	SAIF.Business.Claim.Legal\LegalNet\clsConsolidations.vb	updateCaseStatus	Exact

Source Code

Appeal and appeal-extension excerpts

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 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;

Case-note and case-outcome excerpts

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;

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;

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;

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;

Schedule, consolidation, and correspondence excerpts

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;

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 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 || ')';
        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 || ')';
          EXECUTE IMMEDIATE v_updatestmt;
   END IF;

   END setMailDate;

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;

Related Documentation

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

Note: Statements marked with * were inferred from code structure, naming conventions, or contextual evidence where direct confirmation was not available in source artifacts.

Sources

• Oracle retrieval JSON: C:\Users\chrfre\AppData\Local\Temp\oracle-sources\pk80001_legalnet.json
• Oracle package body/specification: WCIS_DBA.pk80001_legalnet
• SAIF.Business.Claim.Legal\LegalNet\clsAcknowledge.vb
• SAIF.Business.Claim.Legal\LegalNet\clsAppeal.vb
• SAIF.Business.Claim.Legal\LegalNet\clsCaseNote.vb
• SAIF.Business.Claim.Legal\LegalNet\clsCaseOutcome.vb
• SAIF.Business.Claim.Legal\LegalNet\clsLegalCaseEvaluation.vb
• SAIF.Business.Claim.Legal\LegalNet\clsLegalFileLocation.vb
• SAIF.Business.Claim.Legal\LegalNet\clsLegalSchedule.vb
• SAIF.Business.Claim.Legal\LegalNet\clsLegalCase.vb
• SAIF.Business.Claim.Legal\LegalNet\clsCreateFile.vb
• SAIF.Business.Claim.Legal\LegalNet\clsMailDate.vb
• SAIF.Business.Claim.Legal\LegalNet\clsConsolidations.vb