pk80001_legalnet¶

Overview¶

Field	Value
Schema	`WCIS_DBA`
Type	Package
Database	Oracle
Calling libraries	`SAIF.Application.Claim.Legal`
Calling classes	`LegalManagerBase`, `clsLegalData`, `clsScheduleManager`

The retrieved Oracle source declares PACKAGE Pk80001_Legalnet. This library references the same package as both pk80001_LEGALNET and pk80001_legalNet; Oracle identifier casing does not change package resolution.

Purpose¶

Pk80001_Legalnet centralizes LegalNet database behavior for legal-case retrieval and maintenance, including schedules, appeals, case notes, letter requests, and attorney/staff lookups. For this library, the relevant entry points create LETTER_REQUEST rows, resolve the manager email for the attorney attached to a case's most recent hearing, and expose the case-header values used to populate the application's current legal-case context*.

Procedures / Methods¶

insertLetterRequest¶

Field	Value
Returns	`requestNum IN OUT NUMBER`
Called by	`LegalManagerBase.insertLetterRequest`

Description: Allocates LETTER_REQUEST_SEQ.NEXTVAL, inserts a LETTER_REQUEST row, and stamps CREATE_DATE = SYSDATE. The retrieved body accepts aClaimId but does not reference it during the insert.

Parameters¶

Parameter	Direction	Type	Default	Description
`letterTypeCode`	IN	`VARCHAR2`	—	Letter type to queue, such as the DEF or recon-request letter code.
`aLegalCaseNum`	IN	`NUMBER`	—	Legal case sequence number written to `LETTER_REQUEST.LEGAL_CASE_SEQ_NUM`.
`holdDate`	IN	`DATE`	—	Hold or mail date stored on the request row.
`aClaimId`	IN	`NUMBER`	—	Claim identifier accepted by the signature but unused in the retrieved body.
`createUser`	IN	`VARCHAR2`	—	User ID written to `LETTER_REQUEST.CREATE_USER`.
`requestNum`	IN OUT	`NUMBER`	—	Output sequence populated from `LETTER_REQUEST_SEQ.NEXTVAL`.

getHeader inferred mapping¶

Field	Value
Returns	`REF CURSOR` header row contract observed in `clsLegalData`*
Called by	`clsLegalData.New`

Description: This library calls pk80001_legalNet.getHeader, but the retrieved WCIS_DBA.Pk80001_Legalnet source does not contain an exact getHeader declaration. The closest retrieved header-producing routines are getLegalCase, get_case_num, get_asgn_atty, and get_hearing_date, which collectively expose the same case-number, attorney, and hearing-date concepts that clsLegalData reads*.

Parameters¶

Parameter	Direction	Type	Default	Description
`aLegalCaseNum`	IN	`NUMBER`	—	Legal case sequence number supplied by `clsLegalData`.
`recordset`	OUT	`REF CURSOR`	—	Cursor contract expected by `clsLegalData` for header values*

Relevant retrieved routines¶

Routine	Type	Relevant behavior
`getLegalCase`	Procedure	Returns the core `LEGAL_CASE` row and computes `CASE_NUM` with `get_case_num`.
`get_case_num`	Function	Chooses the first populated identifier in WCB → DCBS → OWCP → OALJ → BRB order.
`get_asgn_atty`	Function	Resolves attorney display text from the latest appeal attorney, then the most recent hearing attorney, then the outside counsel firm.
`get_hearing_date`	Function	Returns the latest hearing date, including consolidated-case hearing lookup logic.

get_atty_mngr_email¶

Field	Value
Returns	`VARCHAR2`
Called by	`clsScheduleManager.getAttyMngrEmail`

Description: Resolves the manager email for the attorney tied to the case's most recent hearing. If the assigned-attorney lookup fails, the function falls back to the OUTSIDECNCL attorney profile when an outside-firm assignment exists; if neither path resolves, it returns NONE.

Parameters¶

Parameter	Direction	Type	Default	Description
`aLegalCaseNum`	IN	`NUMBER`	—	Legal case sequence number used to locate the most recent hearing and assigned attorney context.

Business Rules¶

Rules extracted from this package's logic:

Rule ID	Rule	Condition	Action
BR-LGL-018	Letter requests use a database sequence and Oracle server timestamp.	`insertLetterRequest` is invoked from the DEF or recon-letter flows.	The package assigns `LETTER_REQUEST_SEQ.NEXTVAL`, inserts the request row, and sets `CREATE_DATE = SYSDATE`.
BR-LGL-019	Manager-email lookup falls back from assigned attorney to outside counsel.	`get_atty_mngr_email` cannot find a manager email for the most recent hearing's assigned attorney.	The package retries against the `OUTSIDECNCL` profile when `ASGN_OTHER_FIRM_SEQ_NUM` is present, then returns `NONE` if no email is found.
BR-LGL-020	Header case number prefers WCB, then DCBS, OWCP, OALJ, and BRB*	This library's `getHeader` call is matched to the retrieved `get_case_num` helper*	The first populated identifier in that precedence order is returned for display*
BR-LGL-021	Header attorney display prefers appeal attorney, then hearing attorney, then outside counsel*	This library's `getHeader` call is matched to the retrieved `get_asgn_atty` helper*	The first available attorney or firm display value in that order is returned*
BR-LGL-022	Header hearing date uses the latest hearing and consolidation context*	This library's `getHeader` call is matched to the retrieved `get_hearing_date` helper*	The latest applicable hearing date is returned for the case or its consolidated primary-case context*

Operations¶

Operations and processes implemented in this package for this library:

Operation	Description	Trigger
Record letter request	Inserts a `LETTER_REQUEST` row and returns the generated request sequence.	`LegalManagerBase.insertLetterRequest` runs from DEF or recon-request flows.
Resolve attorney manager email	Looks up the assigned attorney manager email for the most recent hearing, with outside-counsel fallback.	`clsScheduleManager.SendDEFEmail` calls `getAttyMngrEmail`.
Assemble legal case header*	Supplies case-number, attorney, and hearing-date header values through retrieved header helper routines*	`clsLegalData.New` calls `pk80001_legalNet.getHeader`*

Source Code¶

Package specification excerpt

PACKAGE                   Pk80001_Legalnet AS

TYPE ref_cursor IS REF CURSOR;

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

FUNCTION get_asgn_atty(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_case_num(aLegalCaseNum IN NUMBER) RETURN VARCHAR2;

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

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

insertLetterRequest body

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;

get_atty_mngr_email body

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;

Header-related body excerpts

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

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;

Calling Code References¶

Class	Method	Source File
`LegalManagerBase`	`insertLetterRequest`	`SAIF.Application.Claim.Legal\LegalManagerBase.vb`
`clsLegalData`	`New` / `RetrieveSQL` (`pk80001_legalNet.getHeader`)	`SAIF.Application.Claim.Legal\clsLegalData.vb`
`clsScheduleManager`	`getAttyMngrEmail`	`SAIF.Application.Claim.Legal\LegalNet\clsScheduleManager.vb`

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 source retrieval: C:\Users\chrfre\AppData\Local\Temp\oracle-sources\pk80001_legalnet.json
SAIF.Application.Claim.Legal\LegalManagerBase.vb
SAIF.Application.Claim.Legal\clsLegalData.vb
SAIF.Application.Claim.Legal\LegalNet\clsScheduleManager.vb

pk80001_legalnet¶

Overview¶

Purpose¶

Procedures / Methods¶

insertLetterRequest¶

Parameters¶

getHeader inferred mapping¶

Parameters¶

Relevant retrieved routines¶

get_atty_mngr_email¶

Parameters¶

Business Rules¶

Operations¶

Source Code¶

Calling Code References¶

Related documentation¶

Sources¶