pk80005_assignment

WCIS_DBA.pk80005_assignment is present in Oracle and implements the attorney-assignment engine used for conflict screening, schedule assignment, calendar review, and assignment-result reporting.

Overview

Field	Value
Schema	WCIS_DBA
Type	Package
Database	Oracle (Case Management)
Calling libraries	SAIF.Business.Claim.Legal
Calling classes	clsAssignmentProcess, clsQuickCal

Purpose

This package evaluates assignment candidates for pending hearings, applies conflict and workload checks, writes assignment-result audit rows, updates schedule ownership when an attorney is successfully assigned, and exposes review/reporting queries for assignment calendars and conflicts.

Procedures / Methods

Conflict and workload evaluators

Routine	Type	Called by	Description
adjuster_conflict_check	Function	assignIt, assignItBalanced	Counts active attorney conflicts tied to the claim adjuster for the case.
employer_conflict_check	Function	assignIt, assignItBalanced	Counts active attorney conflicts tied to the claim's employer/policy.
attorney_conflict_check	Function	assignIt, assignItBalanced	Counts active conflicts between the candidate attorney and the worker attorney firm/provider location on the case.
calendar_conflict_check	Function	assignIt, assignItBalanced	Detects same-day schedule conflicts, honoring duration rules for partial-day versus multi-day events.
vacation_conflict_check	Function	assignIt, assignItBalanced	Detects attorney leave/vacation overlap and long-leave blackout windows.
rotation_conflict_check	Function	assignIt, assignItBalanced	Rejects out-of-town rotation assignments when the active rotation location does not match the hearing location.
location_conflict_check	Function	assignIt, assignItBalanced	Detects same-day assignments to a different hearing location.
case_load, period_case_load, get_weekly_case_load, period_case_count, get_hearing_days, get_vacation_days	Functions	assignIt, assignItBalanced, calendar_review, get_assign_list	Calculate assignment load, hearing counts, and leave metrics used by the assignment engine and reporting queries.

Assignment execution routines

Routine	Type	Called by	Description
assignIt	Function	assign_rotation_cases, remaining-case logic	Runs the standard assignment decision tree and either assigns the attorney or records one or more failure result codes.
assignItBalanced	Function	remaining-case logic	Runs the same decision tree with balanced-load tracing via DBMS_OUTPUT.
updateSchedule	Procedure	assignIt, assignItBalanced	Persists the assigned attorney, assignment date, and audit fields on the selected schedule row.
insert_results	Procedure	assignIt, assignItBalanced	Writes ASSIGNMENT_RESULT audit rows for both successful and failed assignment attempts.
max_result_seq	Function	clsAssignmentProcess.GetMaxSeq	Returns the current maximum assignment-result sequence, defaulting to zero when the table is empty.

Selection and review queries

Routine	Type	Called by	Description
assign_rotation_cases	Procedure	batch assignment processing	Selects pending unassigned hearing rows eligible for out-of-town rotation assignment and evaluates them through assignIt.
calendar_review	Procedure	clsQuickCal	Returns attorney profile, schedule, load, vacation, and hearing-day data for a requested location or for all locations.
get_assign_results	Procedure	assignment-process reporting	Returns latest assignment-result rows either after a sequence number or for a specific date.
get_assign_list	Procedure	assignment-process reporting	Returns currently assigned hearing rows, including attorney load and outside-counsel cases.
getAttorneyScheduleConflict	Procedure	assignment-process reporting	Returns future schedule overlaps where an attorney is double-booked against different ALJs or hearing locations.

Business Rules

BR-CLM-LGL-032 Assignment only succeeds when every blocker count is zero

Field	Value
Statement	assignIt and assignItBalanced only assign an attorney when the sum of adjuster, ALJ, rotation, calendar, load, vacation, holiday, employer, location, and attorney-conflict results is zero.
Trigger	The assignment engine evaluates a candidate attorney for a hearing.
Enforcement	Both functions calculate final_result as the sum of all blocker counts and branch on IF final_result = 0.
Outcome	Successful assignments write a CA result, update SCHEDULE.asgn_attorney_emplid, and call Pk80001_Legalnet.updateCaseStatus(..., 'ASSGN', ...); otherwise the candidate remains unassigned.
Source	WCIS_DBA.pk80005_assignment.assignIt, WCIS_DBA.pk80005_assignment.assignItBalanced

BR-CLM-LGL-033 Weekly load thresholds vary by attorney load percent

Field	Value
Statement	When weekly-load checking is enabled, attorneys with load percent below 34 are allowed one hearing day before load blocks them, attorneys below 67 are allowed two, and all others are allowed three.
Trigger	assignIt evaluates a candidate with weekly_case_load > 0.
Enforcement	assignIt sets hearing_days to 1, 2, or 3 from in_load_percent, then only treats the load as blocking when get_hearing_days(...) is greater than or equal to that threshold.
Outcome	Lower-capacity attorneys hit load-based assignment rejection sooner than higher-capacity attorneys.
Source	WCIS_DBA.pk80005_assignment.assignIt

BR-CLM-LGL-034 Rotation assignment only targets pending unassigned hearing rows in active rotation coverage

Field	Value
Statement	Rotation assignment evaluates only PENDA legal cases with HEAR schedules in SET or RESET status, no assigned attorney or outside counsel, an active matching out-of-town rotation row for the hearing location/date, and no recon issue on the case.
Trigger	assign_rotation_cases runs.
Enforcement	The cursor query filters on case_status_code = 'PENDA', schedule type/status, null assignment columns, out_of_town_rotation_assgn coverage, and a NOT EXISTS recon-issue subquery.
Outcome	Rotation assignment is limited to unresolved in-scope hearings and excludes recon cases.
Source	WCIS_DBA.pk80005_assignment.assign_rotation_cases

BR-CLM-LGL-035 Conflict and load helper functions treat missing data as zero blockers

Field	Value
Statement	The conflict and load helper functions normalize NO_DATA_FOUND and null aggregates to zero so that missing related rows do not fail the assignment engine.
Trigger	Any helper such as adjuster_conflict_check, employer_conflict_check, alj_conflict_check, attorney_conflict_check, calendar_conflict_check, case_load, or get_weekly_case_load runs.
Enforcement	Each routine catches NO_DATA_FOUND and/or assigns zero when the aggregate result is null.
Outcome	Candidate evaluation proceeds as "no blocker found" when supporting rows are absent.
Source	WCIS_DBA.pk80005_assignment.adjuster_conflict_check, WCIS_DBA.pk80005_assignment.employer_conflict_check, WCIS_DBA.pk80005_assignment.alj_conflict_check, WCIS_DBA.pk80005_assignment.attorney_conflict_check, WCIS_DBA.pk80005_assignment.calendar_conflict_check, WCIS_DBA.pk80005_assignment.case_load, WCIS_DBA.pk80005_assignment.get_weekly_case_load

BR-CLM-LGL-036 Calendar review filters to active attorney profiles and requested coverage locations

Field	Value
Statement	calendar_review only reports attorneys with active ATTORNEY_PROFILE rows and, unless the caller asks for ALL, only those whose primary, secondary, or tertiary hearing-location code matches the requested location.
Trigger	calendar_review is called from the quick-calendar flow.
Enforcement	The routine always filters a.EXP_DATE IS NULL; the location-specific branch further filters on the three hearing-location code columns.
Outcome	Calendar review reports current attorney coverage for the selected location set.
Source	WCIS_DBA.pk80005_assignment.calendar_review

BR-CLM-LGL-037 Assignment result reporting keeps the latest result per case in scope

Field	Value
Statement	get_assign_results reports only the latest ASSIGNMENT_RESULT row per legal case for the requested sequence or date scope, and splits assigned (CA, CAC) versus not-assigned result rows into separate query branches.
Trigger	Assignment-process reporting requests results by sequence number or date.
Enforcement	The query filters each branch to MAX(assignment_result_seq_num) per case (and date when applicable) and branches on assignment_result_code IN ('CA', 'CAC').
Outcome	Reports show the current terminal assignment result for each case rather than intermediate attempts.
Source	WCIS_DBA.pk80005_assignment.get_assign_results

Operations

Evaluate assignment conflicts and availability

Field	Value
Description	Evaluates adjuster, employer, ALJ, attorney, calendar, vacation, location, rotation, holiday, and workload conditions before a hearing can be assigned.
Key routines	adjuster_conflict_check, employer_conflict_check, alj_conflict_check, attorney_conflict_check, calendar_conflict_check, vacation_conflict_check, rotation_conflict_check, location_conflict_check, case_load, get_weekly_case_load
Triggered by	assignIt, assignItBalanced

Execute attorney assignment

Field	Value
Description	Attempts attorney assignment, writes assignment-result audit rows, updates the schedule row, and changes the legal-case status to ASSGN on success.
Key routines	assignIt, assignItBalanced, insert_results, updateSchedule
Triggered by	Batch assignment processing in clsAssignmentProcess

Assign rotation hearings

Field	Value
Description	Pulls pending unassigned hearing rows covered by out-of-town rotation assignments and evaluates the primary rotation attorney.
Key routines	assign_rotation_cases, assignIt
Triggered by	Rotation-based attorney assignment runs

Review attorney assignment calendar

Field	Value
Description	Returns attorney/location/schedule/load/vacation metrics for quick calendar review and separate future schedule-conflict reporting.
Key routines	calendar_review, getAttorneyScheduleConflict
Triggered by	clsQuickCal and assignment review screens

Publish assignment result feeds

Field	Value
Description	Returns assignment-result and assigned-hearing feeds for downstream review, email, and operational dashboards.
Key routines	get_assign_results, get_assign_list, max_result_seq, Send_Assignment_Info
Triggered by	clsAssignmentProcess custom methods and batch follow-up workflows

Calling Code References

Procedure	Class	Method/Property	Source File	Matched Oracle routine	Match
max_result_seq (function-style)**	clsAssignmentProcess	GetMaxSeq (custom method)	SAIF.Business.Claim.Legal\LegalNet\clsAssignmentProcess.vb	max_result_seq	Exact
CalendarReview**	clsQuickCal	custom method	SAIF.Business.Claim.Legal\LegalNet\clsQuickCal.vb	calendar_review	Exact

Source Code

Conflict and workload helper excerpts

FUNCTION adjuster_conflict_check (in_case_seq   IN NUMBER,
                                  in_emplid     IN VARCHAR2)
   RETURN NUMBER
AS
   v_conflict_count   NUMBER;
BEGIN
   BEGIN
      v_conflict_count := 0;

      SELECT COUNT (*)
        INTO v_conflict_count
        FROM attorney_conflict a,
             legal_case l,
             CLM_DBA.temp_data_person_address c,
             adp_employee e
       WHERE     l.legal_case_seq_num = in_Case_seq
             AND a.exp_date IS NULL
             AND l.claim_id = c.clm_id
             AND a.attorney_emplid = in_emplid
             AND TRIM (c.adjuster_user_id) = TRIM (e.user_id)
             AND e.emplid = a.adjuster_emplid;
   EXCEPTION
      WHEN NO_DATA_FOUND
      THEN
         v_conflict_count := 0;
   END;

   IF v_conflict_count IS NULL
   THEN
      v_conflict_count := 0;
   END IF;

   RETURN v_conflict_count;
END adjuster_conflict_check;

FUNCTION calendar_conflict_check (in_emplid          IN VARCHAR2,
                                  in_date            IN DATE,
                                  in_duration_code   IN VARCHAR2,
                                  in_hear_loc        IN NUMBER,
                                  in_alj             IN NUMBER)
   RETURN NUMBER
AS
   v_conflict_count   NUMBER;
BEGIN
   IF in_duration_code = 'AM' OR in_duration_code = 'PM'
   THEN
      BEGIN
         SELECT COUNT (*)
           INTO v_conflict_count
           FROM schedule s, duration_code d
          WHERE     schedule_status_code IN ('SET', 'RESET')
                AND asgn_attorney_emplid = in_Emplid
                AND in_date BETWEEN schedule_date
                                AND   schedule_date
                                    + DECODE (days_num,
                                              0, 0,
                                              days_num - 1)
                AND (s.duration_code = in_Duration_code OR days_num > 0)
                AND (   NVL (alj_person_seq_num, 0) <> in_alj
                     OR NVL (hearing_location_seq_num, 0) <> in_hear_Loc)
                AND s.duration_code = d.code;
      EXCEPTION
         WHEN NO_DATA_FOUND
         THEN
            v_conflict_count := 0;
      END;
   ELSE
      BEGIN
         SELECT COUNT (*)
           INTO v_conflict_count
           FROM schedule s, duration_code d
          WHERE     schedule_status_code IN ('SET', 'RESET')
                AND asgn_attorney_emplid = in_Emplid
                AND in_date BETWEEN schedule_date
                                AND   schedule_date
                                    + DECODE (days_num,
                                              0, 0,
                                              days_num - 1)
                AND (   NVL (alj_person_seq_num, 0) <> in_alj
                     OR NVL (hearing_location_seq_num, 0) <> in_hear_Loc)
                AND s.duration_code = d.code;
      EXCEPTION
         WHEN NO_DATA_FOUND
         THEN
            v_conflict_count := 0;
      END;
   END IF;

   IF v_conflict_count IS NULL
   THEN
      v_conflict_count := 0;
   END IF;

   RETURN v_conflict_count;
END calendar_conflict_check;

Assignment execution excerpts

FUNCTION assignIt (in_code           IN VARCHAR2,
                   in_emplid         IN VARCHAR2,
                   in_load_percent   IN NUMBER,
                   in_assign_rec     IN assignment_rec)
   RETURN VARCHAR2
AS
   v_assign          VARCHAR2 (1) := 'N';
   calendar_result   NUMBER := 0;
   adjuster_result   NUMBER := 0;
   rotation_result   NUMBER := 0;
   employer_result   NUMBER := 0;
   alj_result        NUMBER := 0;
   attorney_result   NUMBER := 0;
   load_result       NUMBER := 0;
   final_result      NUMBER := 0;
   vacation_result   NUMBER := 0;
   holiday_result    NUMBER := 0;
   location_result   NUMBER := 0;
   hearing_days      NUMBER := 3;
   v_date            DATE;
BEGIN
   BEGIN
      v_assign := 'N';
      calendar_result := 0;
      adjuster_result := 0;
      rotation_result := 0;
      employer_result := 0;
      alj_result := 0;
      load_result := 0;
      final_result := 0;
      vacation_result := 0;
      holiday_result := 0;
      location_result := 0;

      SELECT sp72353_next_work_day (in_assign_rec.schedule_Date)
        INTO v_date
        FROM DUAL;

      IF v_date = in_assign_rec.schedule_Date
      THEN
         holiday_result := 0;
      ELSE
         holiday_result := 1;
      END IF;

      IF in_assign_rec.weekly_case_load > 0
      THEN
         load_result :=
            get_hearing_days (in_emplid, in_assign_rec.schedule_Date);

         IF in_load_percent < 34
         THEN
            hearing_days := 1;
         ELSE
            IF in_load_percent < 67
            THEN
               hearing_days := 2;
            END IF;
         END IF;
      END IF;

      final_result :=
           adjuster_result
         + alj_result
         + rotation_result
         + calendar_result
         + load_result
         + vacation_result
         + holiday_result
         + employer_result
         + location_result
         + attorney_result;

      IF final_result = 0
      THEN
         insert_results (in_assign_rec.legal_case_Seq_num,
                         in_code,
                         in_assign_rec.schedule_seq_num,
                         'CA',
                         IN_emplid);
         updateSchedule (in_assign_rec.schedule_seq_num,
                         in_emplid,
                         SYSDATE,
                         SYSDATE,
                         'Assignment');
         Pk80001_Legalnet.updatecasestatus (
            in_assign_rec.legal_case_Seq_num,
            'ASSGN',
            SYSDATE,
            'Assignment');
         v_assign := 'Y';
      ELSE
         v_assign := 'N';

         IF holiday_result > 0
         THEN
            insert_results (in_assign_rec.legal_case_Seq_num,
                            in_code,
                            in_assign_rec.schedule_seq_num,
                            'NAHOL',
                            IN_emplid);
         END IF;

         IF employer_result > 0
         THEN
            insert_results (in_assign_rec.legal_case_Seq_num,
                            in_code,
                            in_assign_rec.schedule_seq_num,
                            'NAEMP',
                            IN_emplid);
         END IF;

         IF adjuster_result > 0
         THEN
            insert_results (in_assign_rec.legal_case_Seq_num,
                            in_code,
                            in_assign_rec.schedule_seq_num,
                            'NAADJ',
                            IN_emplid);
         END IF;

PROCEDURE updateSchedule (schedSeqNum   IN NUMBER,
                          attyEmplid    IN VARCHAR2,
                          asgnDate      IN DATE,
                          updateDate    IN DATE,
                          updateUser    IN VARCHAR2)
AS
BEGIN
   UPDATE schedule
      SET asgn_attorney_emplid = attyEmplid,
          assigned_date = asgnDate,
          update_date = updateDate,
          update_user = updateUser
    WHERE schedule_seq_num = schedSeqNum;
END updateSchedule;

PROCEDURE insert_results (in_case_seq       IN NUMBER,
                          in_rule_code      IN VARCHAR2,
                          in_schedule_num   IN NUMBER,
                          in_result_code    IN VARCHAR2,
                          in_emplid         IN VARCHAR2)
IS
BEGIN
   INSERT INTO assignment_result (assignment_result_seq_num,
                                  legal_case_seq_num,
                                  schedule_seq_num,
                                  assignment_rule_code,
                                  assignment_attorney_emplid,
                                  assignment_result_code,
                                  create_date)
        VALUES (assignment_result_seq.NEXTVAL,
                in_case_seq,
                NVL (in_schedule_num, 2),
                IN_rule_code,
                in_emplid,
                in_result_code,
                SYSDATE);
END insert_results;

Rotation, review, and reporting excerpts

PROCEDURE assign_rotation_cases (adjuster_flag      IN VARCHAR2,
                                 alj_flag           IN VARCHAR2,
                                 rotation_flag      IN VARCHAR2,
                                 calendar_flag      IN VARCHAR2,
                                 employer_flag      IN VARCHAR2,
                                 weekly_case_load   IN NUMBER,
                                 location_flag      IN VARCHAR2)
IS
   assigns_cursor   assign_cursor;
   assign_rec       assigns_cursor%ROWTYPE;
   v_assigned       VARCHAR2 (1);
BEGIN
   OPEN assigns_cursor FOR
        SELECT /*+ FIRST_ROWS */
              l.legal_case_seq_num,
               s.schedule_date,
               s.hearing_location_seq_num,
               s.alj_person_seq_num,
               s.duration_code,
               s.schedule_seq_num,
               h.hearing_location_code,
               o.attorney_emplid,
               'None' AS secondary_emplid,
               'None' AS tertiary_emplid,
               adjuster_flag,
               alj_flag,
               rotation_flag,
               calendar_flag,
               employer_flag,
               weekly_case_load,
               location_flag,
               wcb_num
          FROM legal_case l,
               schedule s,
               hearing_location h,
               out_of_town_rotation_assgn o
         WHERE     l.legal_case_seq_num = s.legal_case_seq_num
               AND l.case_status_code = 'PENDA'
               AND s.schedule_type_code = 'HEAR'
               AND (   s.schedule_status_code = 'SET'
                    OR s.schedule_status_code = 'RESET')
               AND asgn_attorney_emplid IS NULL
               AND asgn_other_firm_seq_num IS NULL
               AND s.hearing_location_seq_num = h.hearing_location_seq_num
               AND h.hearing_location_code = o.hearing_location_code
               AND s.schedule_date BETWEEN o.eff_date AND o.exp_date
               AND NOT EXISTS
                          (SELECT 1
                             FROM request_issue r, request_issue_code rc
                            WHERE     s.legal_case_seq_num =
                                         r.legal_case_seq_num
                                  AND r.request_issue_code = rc.code
                                  AND rc.recon_issue_flag = 'Y')
      ORDER BY schedule_date;

PROCEDURE calendar_review (in_loc_code   IN     VARCHAR2,
                           in_date       IN     DATE,
                           recordset        OUT ref_cursor)
AS
BEGIN
   IF in_loc_code = 'ALL'
   THEN
      OPEN recordset FOR
           SELECT /*+ FIRST_ROWS */
                 profile_emplid,
                     e.LAST_NAME
                  || ', '
                  || DECODE (e.preferred_name,
                             ' ', e.FIRST_NAME,
                             e.preferred_name)
                     AS ATTORNEY_NAME,
                  C.CODE_DESCR AS Location,
                  L.LOCATION_NAME AS Description,
                  DECODE (P.LAST_NAME,
                          NULL, ' ',
                          p.last_name || ', ' || P.FIRST_NAME)
                     AS ALJ,
                  s.schedule_time,
                  s.schedule_status_code,
                  Pk80001_Legalnet.get_case_num (
                     NVL (s.legal_case_seq_num, 0))
                     AS case_num,
                  load_percent,
                  primary_hear_loc_code,
                  secondary_hear_loc_code,
                  tertiary_hear_loc_code,
                  pk80005_assignment.get_weekly_case_load (profile_emplid,
                                                           in_date)
                     AS weekly_load,
                  pk80005_assignment.period_case_load (profile_emplid,
                                                       in_date)
                     AS case_load,
                  pk80005_assignment.get_vacation_days (profile_emplid,
                                                        in_date,
                                                        5,
                                                        0)
                     AS vacation_days,
                  pk80005_assignment.get_hearing_days (profile_emplid,
                                                       in_date)
                     AS hearing_days
             FROM adp_employee e,
                  attorney_profile a
                  LEFT OUTER JOIN schedule s
                     ON     a.profile_emplid = s.asgn_attorney_emplid
                        AND NVL (S.SCHEDULE_TYPE_CODE, 'HEAR') NOT IN ('DEP',
                                                                       'FDEP')
                        AND s.schedule_date = in_date
            WHERE e.emplid = a.profile_emplid AND a.EXP_DATE IS NULL;
END calendar_review;

FUNCTION max_result_seq
   RETURN NUMBER
AS
   v_result_seq   NUMBER;
BEGIN
   BEGIN
      SELECT MAX (assignment_result_seq_num)
        INTO v_result_seq
        FROM assignment_result;
   EXCEPTION
      WHEN NO_DATA_FOUND
      THEN
         v_result_seq := 0;
   END;

   IF v_result_seq IS NULL
   THEN
      v_result_seq := 0;
   END IF;

   RETURN v_result_seq;
END max_result_seq;

Related Documentation

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

Note: Statements marked with ** could not be confirmed; treat as unknown until verified.

Sources

• Oracle retrieval JSON: C:\Users\chrfre\AppData\Local\Temp\oracle-sources\pk80005_assignment.json
• Oracle package body/specification: WCIS_DBA.pk80005_assignment
• SAIF.Business.Claim.Legal\LegalNet\clsAssignmentProcess.vb
• SAIF.Business.Claim.Legal\LegalNet\clsQuickCal.vb