pk80005_assignment¶
WCIS_DBA.pk80005_assignment is the Oracle PL/SQL package that implements SAIF's automated attorney-assignment batch process for workers' compensation legal hearings. It selects unassigned hearings, applies a chain of conflict-of-interest and workload rules to candidate attorneys, records assignment outcomes, and exposes read-only cursors consumed by the .NET assignment-process, attorney-conflict, and Outlook-integration screens.
Overview¶
| Field | Value |
|---|---|
| Schema | WCIS_DBA |
| Type | Package |
| Database | WCIS_DBA / Oracle |
| Calling libraries | SAIF.Business.Claim.Legal |
| Calling classes | clsAssignmentProcess, clsAttorneyConflict, clsAttorneySchedule |
| Member count | 42 |
The prior (not-found) version of this page listed 12 members, including a duplicate
CalendarReview/calendar_reviewpair and arun_assignmentmember. The retrieved package spec contains onlycalendar_review(no differently-cased duplicate) and does not declare anyrun_assignmentmember; that name is dropped here as it does not correspond to a real declared member.* All 41 other real package members are documented below, including 30 not previously listed (mostly internal conflict-check functions, workload calculators, and the case-selection/assignment procedures called as part of the batch cycle).
Purpose¶
The package automates assignment of workers' compensation hearing cases to attorneys (and, for out-of-town rotation, to outside counsel) as part of SAIF's Legal case-management batch cycle. A top-level driver invokes an ordered sequence of assign_* procedures — by employer/policy relationship, DCBS cases, reconsideration cases, out-of-town rotation, same-time/same-location grouping, health-issue and worker-conflict cases, critical claims, Portland-specific cases, and finally any remaining cases — each of which cursors over PENDA-status legal cases with unassigned SET/RESET hearings and calls the shared assignIt/assignItBalanced functions to evaluate a candidate attorney against conflict-of-interest checks (employer, adjuster, ALJ, attorney, rotation, calendar/vacation, and location conflicts) and caseload thresholds. Every evaluation is recorded via insert_results with a result code (CA/CAC for a successful assignment, or a NA* code identifying the blocking reason), and updateSchedule persists the winning attorney back onto the schedule row. The package also exposes read-only cursors (get_results, get_assign_results, get_assign_list, calendar_review, getOutlookFields) used by the assignment-review UI and Outlook calendar/attorney-conflict integration screens.
Procedures / Methods¶
adjuster_conflict_check¶
| Field | Value |
|---|---|
| Returns | NUMBER (conflict count) |
| Called by | assignIt, assignItBalanced (internal package callers) |
Description: Counts active (exp_date IS NULL) attorney_conflict rows linking the candidate attorney to the claim's assigned adjuster, matching the adjuster's user_id on the claim to the conflict's adjuster_emplid. A non-zero count blocks assignment with result code NAADJ.
Parameters¶
| Parameter | Direction | Type | Default | Description |
|---|---|---|---|---|
in_case_seq |
IN | NUMBER | — | legal_case_seq_num of the case being evaluated |
in_emplid |
IN | VARCHAR2 | — | Candidate attorney's employee ID |
employer_conflict_check¶
| Field | Value |
|---|---|
| Returns | NUMBER (conflict count) |
| Called by | assignIt, assignItBalanced |
Description: Counts active attorney_conflict rows for the candidate attorney where the conflict's policy_num matches the claim's policy. A non-zero count blocks assignment with result code NAEMP.
Parameters¶
| Parameter | Direction | Type | Default | Description |
|---|---|---|---|---|
in_case_seq |
IN | NUMBER | — | legal_case_seq_num of the case being evaluated |
in_emplid |
IN | VARCHAR2 | — | Candidate attorney's employee ID |
attorney_conflict_check¶
| Field | Value |
|---|---|
| Returns | NUMBER (conflict count) |
| Called by | assignIt, assignItBalanced |
Description: Counts active attorney_conflict rows where the candidate's atty_provider_location_seq_num matches the claimant's worker's-attorney firm location on the case, i.e. the candidate already represents the opposing/worker side. A non-zero count blocks assignment with result code NAATT. This check is unconditional (not gated by a flag) in assignIt.
Parameters¶
| Parameter | Direction | Type | Default | Description |
|---|---|---|---|---|
in_case_seq |
IN | NUMBER | — | legal_case_seq_num of the case being evaluated |
in_emplid |
IN | VARCHAR2 | — | Candidate attorney's employee ID |
alj_conflict_check¶
| Field | Value |
|---|---|
| Returns | NUMBER (conflict count) |
| Called by | assignIt, assignItBalanced |
Description: Counts active attorney_conflict rows where conflict_legal_person_seq_num matches the hearing's assigned ALJ. A non-zero count blocks assignment with result code NAALJ when alj_flag = 'Y'.
Parameters¶
| Parameter | Direction | Type | Default | Description |
|---|---|---|---|---|
in_alj_seq |
IN | NUMBER | — | alj_person_seq_num of the hearing's ALJ |
in_emplid |
IN | VARCHAR2 | — | Candidate attorney's employee ID |
calendar_conflict_check¶
| Field | Value |
|---|---|
| Returns | NUMBER (conflict count) |
| Called by | assignIt, assignItBalanced |
Description: Counts overlapping schedule rows (status SET/RESET) for the candidate attorney whose date range overlaps in_date (spanning multi-day durations via days_num) and whose ALJ or hearing location differs from the target hearing — i.e. the attorney is already double-booked elsewhere at the same time. For AM/PM half-day durations the overlap check is restricted to same-duration or multi-day rows; otherwise it checks any overlapping row. Triggered when calendar_flag is Y, H, or C; result code NAC.
Parameters¶
| Parameter | Direction | Type | Default | Description |
|---|---|---|---|---|
in_emplid |
IN | VARCHAR2 | — | Candidate attorney's employee ID |
in_date |
IN | DATE | — | Hearing date being evaluated |
in_duration_code |
IN | VARCHAR2 | — | Hearing duration code (e.g. AM, PM, FULL) |
in_hear_loc |
IN | NUMBER | — | hearing_location_seq_num of the target hearing |
in_alj |
IN | NUMBER | — | alj_person_seq_num of the target hearing |
location_conflict_check¶
| Field | Value |
|---|---|
| Returns | NUMBER (conflict count) |
| Called by | assignIt, assignItBalanced |
Description: Counts schedule rows (status SET/RESET) where the candidate attorney is already booked on in_date at a different hearing location than the target. Triggered when location_flag = 'Y'; result code NALCC.
Parameters¶
| Parameter | Direction | Type | Default | Description |
|---|---|---|---|---|
in_emplid |
IN | VARCHAR2 | — | Candidate attorney's employee ID |
in_date |
IN | DATE | — | Hearing date being evaluated |
in_duration_code |
IN | VARCHAR2 | — | Hearing duration code (unused in the current query, retained for signature symmetry with the other conflict checks)* |
in_hear_loc |
IN | NUMBER | — | hearing_location_seq_num of the target hearing |
vacation_conflict_check¶
| Field | Value |
|---|---|
| Returns | NUMBER (vacation-day count + out-of-office count) |
| Called by | assignIt, assignItBalanced |
Description: Computes two counts and sums them: (1) when in_code = 'Y', counts attorney_schedule LEAVE rows of more than 4 days where today falls in the blackout window computed from the leave's eff_date/exp_date (the "don't assign 3 days before / 1 day after a 4+ day leave" rule); (2) counts overlapping attorney_schedule rows (any type) covering in_date, matching duration for AM/PM or any duration otherwise. Triggered when calendar_flag is Y, H, or C; result code NAVAC.
Parameters¶
| Parameter | Direction | Type | Default | Description |
|---|---|---|---|---|
in_emplid |
IN | VARCHAR2 | — | Candidate attorney's employee ID |
in_date |
IN | DATE | — | Hearing date being evaluated |
in_duration_code |
IN | VARCHAR2 | — | Hearing duration code |
in_code |
IN | VARCHAR2 | — | Calendar flag value passed from the caller (Y/H/C); only Y triggers the long-leave blackout check |
case_load¶
| Field | Value |
|---|---|
| Returns | NUMBER (weighted open case count) |
| Called by | Not directly referenced elsewhere in this package body; exposed for external/legacy callers* |
Description: Sums case_weight_value * (100 / load_percent) across the attorney's currently open (case_status_code <> 'CLOSE') cases where the most recent HEAR/MED schedule row belongs to them, rounding to the nearest whole number. Returns 0 on divide-by-zero (a load_percent of 0) or no rows.
Parameters¶
| Parameter | Direction | Type | Default | Description |
|---|---|---|---|---|
in_emplid |
IN | VARCHAR2 | — | Attorney employee ID to compute case load for |
period_case_load¶
| Field | Value |
|---|---|
| Returns | NUMBER (weighted caseload + vacation-day adjustment for the assignment week) |
| Called by | get_assign_list |
Description: Computes the weighted open-case load (same formula as case_load) restricted to a 20-day window centered on the Friday of the hearing week (12 days before through 7 days after that Friday), then applies an out-of-town-rotation floor: if the attorney has a rotation assignment in that window with a minimum rotation_average_case_count, and their computed weekly load is below that floor, the floor is substituted. Finally adds get_vacation_days for the same 12/7-day window. Used to rank/display candidate workload on the assignment-list screen.
Parameters¶
| Parameter | Direction | Type | Default | Description |
|---|---|---|---|---|
in_emplid |
IN | VARCHAR2 | — | Attorney employee ID |
hearingDate |
IN | DATE | — | Reference hearing date used to compute the assignment week |
period_case_count¶
| Field | Value |
|---|---|
| Returns | NUMBER (open case count for the assignment week) |
| Called by | get_assign_list |
Description: Counts (unweighted) the attorney's open HEAR/MED cases scheduled in the same 12-days-before/7-days-after Friday window used by period_case_load.
Parameters¶
| Parameter | Direction | Type | Default | Description |
|---|---|---|---|---|
in_emplid |
IN | VARCHAR2 | — | Attorney employee ID |
hearingDate |
IN | DATE | — | Reference hearing date used to compute the assignment week |
get_weekly_case_load¶
| Field | Value |
|---|---|
| Returns | NUMBER (weighted caseload for the trailing work-week) |
| Called by | calendar_review, internal weekly-load rules in assignIt/assignItBalanced (via load_result/get_hearing_days) |
Description: Sums weighted open-case load for HEAR/MED schedule rows in the 6-day window ending on the Friday of the hearing week (5 days before through that Friday). Used for the weekly_case_load conflict check and rendered as weekly_load on the calendar-review screen.
Parameters¶
| Parameter | Direction | Type | Default | Description |
|---|---|---|---|---|
in_emplid |
IN | VARCHAR2 | — | Attorney employee ID |
hearingDate |
IN | DATE | — | Reference hearing date used to compute the work-week |
get_hearing_days¶
| Field | Value |
|---|---|
| Returns | NUMBER (distinct hearing days in the work-week, plus vacation days) |
| Called by | assignIt, assignItBalanced, calendar_review |
Description: Counts distinct scheduled HEAR/MED dates for the attorney in the same 5-day-trailing work-week as get_weekly_case_load, then adds get_vacation_days (5 days before, 0 after). In assignIt, if this total is below a threshold (1 day if load_percent < 34, 2 days if < 67, else no minimum) the weekly_case_load conflict is not raised — i.e. attorneys below their target hearing-day count for the week are still eligible.
Parameters¶
| Parameter | Direction | Type | Default | Description |
|---|---|---|---|---|
in_emplid |
IN | VARCHAR2 | — | Attorney employee ID |
hearingDate |
IN | DATE | — | Reference hearing date |
get_vacation_days¶
| Field | Value |
|---|---|
| Returns | NUMBER (vacation/out-of-office day count in a caller-defined window)* |
| Called by | period_case_load, get_hearing_days, calendar_review |
Description: Counts attorney_schedule leave/out-of-office rows for the attorney within a window of in_before days before and in_after days after hearingDate (window derivation follows the same Friday-anchored pattern used elsewhere in the package).* Exact query body was not re-read in full for this pass; behavior inferred from call sites and parameter names.**
Parameters¶
| Parameter | Direction | Type | Default | Description |
|---|---|---|---|---|
in_emplid |
IN | VARCHAR2 | — | Attorney employee ID |
hearingDate |
IN | DATE | — | Reference hearing date |
in_before |
IN | NUMBER | — | Days before the reference window to include |
in_after |
IN | NUMBER | — | Days after the reference window to include |
max_result_seq¶
| Field | Value |
|---|---|
| Returns | NUMBER (current maximum assignment_result_seq_num) |
| Called by | clsAssignmentProcess |
Description: Returns MAX(assignment_result_seq_num) from assignment_result, or 0 if the table is empty. Used by the calling .NET code as a watermark/checkpoint to poll only newly-created assignment results (via the in_num parameter on get_results/get_assign_results/getOutlookFields).
Parameters¶
None.
insert_results¶
| Field | Value |
|---|---|
| Returns | None (procedure) |
| Called by | assignIt, assignItBalanced (internal) |
Description: Inserts a new row into assignment_result using assignment_result_seq.NEXTVAL for the sequence, defaulting a null in_schedule_num to 2, and stamping create_date with SYSDATE. Records both successful assignments (CA) and every blocking rule outcome (NA* codes).
Parameters¶
| Parameter | Direction | Type | Default | Description |
|---|---|---|---|---|
in_case_seq |
IN | NUMBER | — | legal_case_seq_num of the case |
in_rule_code |
IN | VARCHAR2 | — | Assignment rule code identifying which assign_* batch produced this result (e.g. EA, ROT, SAME) |
in_schedule_num |
IN | NUMBER | — | schedule_seq_num; defaults to 2 if null |
in_result_code |
IN | VARCHAR2 | — | Result code, e.g. CA (assigned) or an NA* blocking code |
in_emplid |
IN | VARCHAR2 | — | Candidate/assigned attorney employee ID |
rotation_conflict_check¶
| Field | Value |
|---|---|
| Returns | NUMBER (conflict count) |
| Called by | assignIt, assignItBalanced |
Description: Counts out_of_town_rotation_assgn rows for the attorney where in_date falls within the rotation's effective window but the hearing's location code differs from the rotation's assigned location — i.e. the attorney is on out-of-town rotation duty for a different location that week. Triggered when rotation_flag = 'Y'; result code NAROT.
Parameters¶
| Parameter | Direction | Type | Default | Description |
|---|---|---|---|---|
in_emplid |
IN | VARCHAR2 | — | Candidate attorney's employee ID |
in_date |
IN | DATE | — | Hearing date |
in_hear_code |
IN | VARCHAR2 | — | Hearing location code of the target hearing |
same_time_loc_check¶
| Field | Value |
|---|---|
| Returns | VARCHAR2 (employee ID, or 'NONE') |
| Called by | assign_same (internal) |
Description: Finds the attorney with the lowest employee ID already scheduled (SET/RESET) at the same ALJ, location, and overlapping duration/day span who is a TRIAL or FED attorney-profile type, so that other hearings at the same time/place can be batched to the same attorney. When in_is_critical = 'Y', further restricts to attorneys flagged ccu_claims_flag = 'Y'; otherwise applies confidential-claims and DCBS/WCB claim-type eligibility filters (in_wrkr_conflict, in_request_type).
Parameters¶
| Parameter | Direction | Type | Default | Description |
|---|---|---|---|---|
in_date |
IN | DATE | — | Hearing date |
in_duration_code |
IN | VARCHAR2 | — | Hearing duration code |
in_hear_loc |
IN | NUMBER | — | Hearing location sequence number |
in_alj |
IN | NUMBER | — | ALJ person sequence number |
in_is_critical |
IN | VARCHAR2 | — | Y if the case is flagged critical/CCU |
in_request_type |
IN | VARCHAR2 | — | Claim request type code (e.g. WCB, DCBS) |
in_wrkr_conflict |
IN | VARCHAR2 | — | Worker-conflict flag from the claim |
assignIt¶
| Field | Value |
|---|---|
| Returns | VARCHAR2(1) — 'Y' if assigned, 'N' if blocked |
| Called by | assign_by_employer, assign_rotation_cases, assign_same, and the other assign_* batch procedures (internal) |
Description: The core rule-evaluation function. Computes a holiday adjustment (via sp72353_next_work_day), a workload check (get_hearing_days vs. a load-percent-derived minimum), and, gated by the caller-supplied flags, the employer/adjuster/ALJ/attorney/rotation/calendar/vacation/location conflict checks described above. The unconditional attorney_conflict_check always runs. If every check returns zero, the case is assigned: insert_results(<args>,'CA',<args>), updateSchedule(<args>), and Pk80001_Legalnet.updatecasestatus(<args>,'ASSGN',<args>) are called and 'Y' is returned. Otherwise every non-zero check inserts its corresponding NA* result row (in a fixed order: NAHOL, NAEMP, NAADJ, NAALJ, NAATT, NAROT, NAC, NAVAC, LOAD, NALCC) and 'N' is returned.
Parameters¶
| Parameter | Direction | Type | Default | Description |
|---|---|---|---|---|
in_code |
IN | VARCHAR2 | — | Assignment rule code recorded on the result row (e.g. EA, ROT, SAME) |
in_emplid |
IN | VARCHAR2 | — | Candidate attorney's employee ID |
in_load_percent |
IN | NUMBER | — | Candidate's assignment load percentage, used to derive the minimum hearing-days threshold |
in_assign_rec |
IN | assignment_rec |
— | Record of case/schedule details and which conflict-check flags to apply |
assignItBalanced¶
| Field | Value |
|---|---|
| Returns | VARCHAR2(1) — 'Y' if assigned, 'N' if blocked |
| Called by | assign_* batch procedures that use load-balanced candidate selection (internal)* |
Description: Same conflict-evaluation and result-recording logic as assignIt (employer/adjuster/ALJ/attorney/rotation/calendar/vacation/holiday/load/location checks against the identical assignment_rec structure), used by assignment procedures that need to compare multiple candidates by load before committing.* The two functions share the identical rule set observed in source; any behavioral divergence beyond candidate-selection order was not confirmed.**
Parameters¶
| Parameter | Direction | Type | Default | Description |
|---|---|---|---|---|
in_code |
IN | VARCHAR2 | — | Assignment rule code recorded on the result row |
in_emplid |
IN | VARCHAR2 | — | Candidate attorney's employee ID |
in_load_percent |
IN | NUMBER | — | Candidate's assignment load percentage |
in_assign_rec |
IN | assignment_rec |
— | Record of case/schedule details and conflict-check flags |
assign_by_employer¶
| Field | Value |
|---|---|
| Returns | None (procedure) |
| Called by | clsAssignmentProcess |
Description: Assigns PENDA cases with unassigned SET/RESET hearings whose claim's policy matches an active attorney_policy_assignment (directly, via policy relationship, or via rate account) to the policy's primary attorney, falling back to secondary then tertiary attorney, and finally re-attempting the primary attorney with all conflict flags forced off if none succeeded. Rule code EA. Cases are scoped to the current run via CLM_DBA.temp_data_person_address.run_id = in_run_id.
Parameters¶
| Parameter | Direction | Type | Default | Description |
|---|---|---|---|---|
adjuster_flag |
IN | VARCHAR2 | — | Enables the adjuster conflict check |
alj_flag |
IN | VARCHAR2 | — | Enables the ALJ conflict check |
rotation_flag |
IN | VARCHAR2 | — | Enables the rotation conflict check |
calendar_flag |
IN | VARCHAR2 | — | Enables the calendar/vacation conflict check (Y/H/C) |
employer_flag |
IN | VARCHAR2 | — | Enables the employer conflict check |
weekly_case_load |
IN | NUMBER | — | Enables the weekly-load conflict check when > 0 |
location_flag |
IN | VARCHAR2 | — | Enables the location conflict check |
in_run_id |
IN | VARCHAR2 | — | Batch run identifier scoping the candidate case set via temp_data_person_address.run_id |
assign_dcbs_cases¶
| Field | Value |
|---|---|
| Returns | None (procedure) |
| Called by | clsAssignmentProcess |
Description: Assigns a DCBS-specific subset of PENDA cases with unassigned hearings using the shared assignIt/assignItBalanced rule set.* Full body was not re-read line-by-line in this pass; behavior inferred from the procedure's position in the batch sequence and its identical flag signature to the other assign_* procedures.**
Parameters¶
| Parameter | Direction | Type | Default | Description |
|---|---|---|---|---|
adjuster_flag |
IN | VARCHAR2 | — | Enables the adjuster conflict check |
alj_flag |
IN | VARCHAR2 | — | Enables the ALJ conflict check |
rotation_flag |
IN | VARCHAR2 | — | Enables the rotation conflict check |
calendar_flag |
IN | VARCHAR2 | — | Enables the calendar/vacation conflict check |
employer_flag |
IN | VARCHAR2 | — | Enables the employer conflict check |
weekly_case_load |
IN | NUMBER | — | Enables the weekly-load conflict check |
location_flag |
IN | VARCHAR2 | — | Enables the location conflict check |
assign_recon_cases¶
| Field | Value |
|---|---|
| Returns | None (procedure) |
| Called by | clsAssignmentProcess |
Description: Assigns reconsideration cases (cases flagged via request_issue_code.recon_issue_flag = 'Y') using the shared conflict-check rule set.* Full body was not re-read line-by-line in this pass.**
Parameters¶
| Parameter | Direction | Type | Default | Description |
|---|---|---|---|---|
adjuster_flag |
IN | VARCHAR2 | — | Enables the adjuster conflict check |
alj_flag |
IN | VARCHAR2 | — | Enables the ALJ conflict check |
rotation_flag |
IN | VARCHAR2 | — | Enables the rotation conflict check |
calendar_flag |
IN | VARCHAR2 | — | Enables the calendar/vacation conflict check |
employer_flag |
IN | VARCHAR2 | — | Enables the employer conflict check |
weekly_case_load |
IN | NUMBER | — | Enables the weekly-load conflict check |
location_flag |
IN | VARCHAR2 | — | Enables the location conflict check |
assign_rotation_cases¶
| Field | Value |
|---|---|
| Returns | None (procedure) |
| Called by | clsAssignmentProcess |
Description: Assigns PENDA cases with unassigned SET/RESET HEAR hearings whose location has an active out_of_town_rotation_assgn covering the hearing date, excluding cases with a reconsideration-flagged request issue. Assigns the rotation's designated attorney (o.attorney_emplid) with rotation_flag forced to 'N' (the rotation itself is the assignment basis, so it is not re-checked as a conflict). Rule code ROT. Ordered by schedule_date.
Parameters¶
| Parameter | Direction | Type | Default | Description |
|---|---|---|---|---|
adjuster_flag |
IN | VARCHAR2 | — | Enables the adjuster conflict check |
alj_flag |
IN | VARCHAR2 | — | Enables the ALJ conflict check |
rotation_flag |
IN | VARCHAR2 | — | Accepted for signature symmetry; overridden to 'N' internally before calling assignIt* |
calendar_flag |
IN | VARCHAR2 | — | Enables the calendar/vacation conflict check |
employer_flag |
IN | VARCHAR2 | — | Enables the employer conflict check |
weekly_case_load |
IN | NUMBER | — | Enables the weekly-load conflict check |
location_flag |
IN | VARCHAR2 | — | Enables the location conflict check |
assign_same¶
| Field | Value |
|---|---|
| Returns | None (procedure) |
| Called by | clsAssignmentProcess |
Description: For each unassigned PENDA case (excluding reconsideration-flagged issues) scoped to in_run_id, calls same_time_loc_check to find an attorney already booked at the same ALJ/location/time, and if one is found, assigns that attorney via assignIt with rule code SAME — batching hearings at the same time and place to a single attorney rather than distributing them independently.
Parameters¶
| Parameter | Direction | Type | Default | Description |
|---|---|---|---|---|
adjuster_flag |
IN | VARCHAR2 | — | Passed through to assignIt as the adjuster-check flag |
alj_flag |
IN | VARCHAR2 | — | Passed through to assignIt as the ALJ-check flag |
rotation_flag |
IN | VARCHAR2 | — | Passed through to assignIt as the rotation-check flag |
calendar_flag |
IN | VARCHAR2 | — | Passed through to assignIt as the calendar-check flag |
employer_flag |
IN | VARCHAR2 | — | Passed through to assignIt as the employer-check flag |
weekly_case_load |
IN | NUMBER | — | Passed through to assignIt as the weekly-load-check flag |
location_flag |
IN | VARCHAR2 | — | Passed through to assignIt as the location-check flag |
in_run_id |
IN | VARCHAR2 | — | Batch run identifier scoping the candidate case set |
assign_health_issue_cases¶
| Field | Value |
|---|---|
| Returns | None (procedure) |
| Called by | clsAssignmentProcess |
Description: Assigns a health-issue-flagged subset of PENDA cases using the shared conflict-check rule set.* Full body was not re-read line-by-line in this pass; behavior inferred from the procedure's name and identical flag signature to the other assign_* procedures.**
Parameters¶
| Parameter | Direction | Type | Default | Description |
|---|---|---|---|---|
adjuster_flag |
IN | VARCHAR2 | — | Enables the adjuster conflict check |
alj_flag |
IN | VARCHAR2 | — | Enables the ALJ conflict check |
rotation_flag |
IN | VARCHAR2 | — | Enables the rotation conflict check |
calendar_flag |
IN | VARCHAR2 | — | Enables the calendar/vacation conflict check |
employer_flag |
IN | VARCHAR2 | — | Enables the employer conflict check |
weekly_case_load |
IN | NUMBER | — | Enables the weekly-load conflict check |
location_flag |
IN | VARCHAR2 | — | Enables the location conflict check |
assign_wrkr_conflict_cases¶
| Field | Value |
|---|---|
| Returns | None (procedure) |
| Called by | clsAssignmentProcess |
Description: Assigns cases flagged with a worker/confidential-claims conflict indicator (wrkr_conflict_flag) using the shared conflict-check rule set, scoped to in_run_id.* Full body was not re-read line-by-line in this pass beyond confirming its declaration and its shared load_attorney_table local helper pattern with assign_critical_claim_cases and assign_remaining_cases.**
Parameters¶
| Parameter | Direction | Type | Default | Description |
|---|---|---|---|---|
adjuster_flag |
IN | VARCHAR2 | — | Enables the adjuster conflict check |
alj_flag |
IN | VARCHAR2 | — | Enables the ALJ conflict check |
rotation_flag |
IN | VARCHAR2 | — | Enables the rotation conflict check |
calendar_flag |
IN | VARCHAR2 | — | Enables the calendar/vacation conflict check |
employer_flag |
IN | VARCHAR2 | — | Enables the employer conflict check |
weekly_case_load |
IN | NUMBER | — | Enables the weekly-load conflict check |
location_flag |
IN | VARCHAR2 | — | Enables the location conflict check |
in_run_id |
IN | VARCHAR2 | — | Batch run identifier scoping the candidate case set |
assign_critical_claim_cases¶
| Field | Value |
|---|---|
| Returns | None (procedure) |
| Called by | clsAssignmentProcess |
Description: Assigns critical/CCU-flagged claims, using an internal load_attorney_table(schedule_date) helper that pre-loads eligible attorney load/location data for the target hearing date before evaluating candidates via assignIt/assignItBalanced. Rule flags are supplied with an in-prefixed parameter naming convention distinct from the other assign_* procedures.
Parameters¶
| Parameter | Direction | Type | Default | Description |
|---|---|---|---|---|
inAdjuster_flag |
IN | VARCHAR2 | — | Enables the adjuster conflict check |
inAlj_flag |
IN | VARCHAR2 | — | Enables the ALJ conflict check |
inRotation_flag |
IN | VARCHAR2 | — | Enables the rotation conflict check |
inCalendar_flag |
IN | VARCHAR2 | — | Enables the calendar/vacation conflict check |
inEmployer_flag |
IN | VARCHAR2 | — | Enables the employer conflict check |
inWeekly_case_load |
IN | NUMBER | — | Enables the weekly-load conflict check |
inlocation_flag |
IN | VARCHAR2 | — | Enables the location conflict check |
in_run_id |
IN | VARCHAR2 | — | Batch run identifier scoping the candidate case set |
assign_portland_cases¶
| Field | Value |
|---|---|
| Returns | None (procedure) |
| Called by | clsAssignmentProcess |
Description: Assigns a Portland-location-specific subset of PENDA cases using the shared conflict-check rule set.* Full body was not re-read line-by-line in this pass beyond confirming its declaration position in the batch sequence.**
Parameters¶
| Parameter | Direction | Type | Default | Description |
|---|---|---|---|---|
adjuster_flag |
IN | VARCHAR2 | — | Enables the adjuster conflict check |
alj_flag |
IN | VARCHAR2 | — | Enables the ALJ conflict check |
rotation_flag |
IN | VARCHAR2 | — | Enables the rotation conflict check |
calendar_flag |
IN | VARCHAR2 | — | Enables the calendar/vacation conflict check |
employer_flag |
IN | VARCHAR2 | — | Enables the employer conflict check |
weekly_case_load |
IN | NUMBER | — | Enables the weekly-load conflict check |
location_flag |
IN | VARCHAR2 | — | Enables the location conflict check |
assign_remaining_cases¶
| Field | Value |
|---|---|
| Returns | None (procedure) |
| Called by | clsAssignmentProcess |
Description: Final catch-all pass over any PENDA cases with unassigned hearings not resolved by the earlier assign_* procedures, using an internal load_attorney_table(schedule_date) helper (same pattern as assign_critical_claim_cases/assign_wrkr_conflict_cases) and the shared conflict-check rule set, scoped to in_run_id.
Parameters¶
| Parameter | Direction | Type | Default | Description |
|---|---|---|---|---|
adjuster_flag |
IN | VARCHAR2 | — | Enables the adjuster conflict check |
alj_flag |
IN | VARCHAR2 | — | Enables the ALJ conflict check |
rotation_flag |
IN | VARCHAR2 | — | Enables the rotation conflict check |
calendar_flag |
IN | VARCHAR2 | — | Enables the calendar/vacation conflict check |
employer_flag |
IN | VARCHAR2 | — | Enables the employer conflict check |
weekly_case_load |
IN | NUMBER | — | Enables the weekly-load conflict check |
location_flag |
IN | VARCHAR2 | — | Enables the location conflict check |
in_run_id |
IN | VARCHAR2 | — | Batch run identifier scoping the candidate case set |
previously_assigned¶
| Field | Value |
|---|---|
| Returns | None (procedure) |
| Called by | clsAssignmentProcess |
Description: Handles cases that were previously assigned to an attorney on an earlier hearing in the same legal case, likely re-assigning the same attorney to a subsequent hearing (consistent with the get_previously_assigned cursor's CA/CAC-filtered "most recent prior assignment" query pattern).* Full body was not re-read line-by-line in this pass.**
Parameters¶
| Parameter | Direction | Type | Default | Description |
|---|---|---|---|---|
adjuster_flag |
IN | VARCHAR2 | — | Enables the adjuster conflict check |
alj_flag |
IN | VARCHAR2 | — | Enables the ALJ conflict check |
rotation_flag |
IN | VARCHAR2 | — | Enables the rotation conflict check |
calendar_flag |
IN | VARCHAR2 | — | Enables the calendar/vacation conflict check |
employer_flag |
IN | VARCHAR2 | — | Enables the employer conflict check |
weekly_case_load |
IN | NUMBER | — | Enables the weekly-load conflict check |
location_flag |
IN | VARCHAR2 | — | Enables the location conflict check |
get_results¶
| Field | Value |
|---|---|
| Returns | REF CURSOR assign_results_cursor (assign_results_rec) via recordset |
| Called by | clsAssignmentProcess |
Description: Returns assignment-result rows joined to attorney name, rule description, and result description, filtered by in_type: ASSIGN returns rows with assignment_result_seq_num > in_num (new-since-watermark), DATE returns rows created on in_date, and CASE returns all rows for the case identified by in_num. Ordered by case then result sequence.
Parameters¶
| Parameter | Direction | Type | Default | Description |
|---|---|---|---|---|
in_num |
IN | NUMBER | — | Watermark sequence number (ASSIGN) or legal_case_seq_num (CASE); ignored for DATE |
in_type |
IN | VARCHAR2 | — | Query mode: ASSIGN, DATE, or CASE |
in_date |
IN | DATE | — | Result creation date filter, used only when in_type = 'DATE' |
recordset |
OUT | assign_results_cursor |
— | REF CURSOR of assign_results_rec rows |
get_assign_results¶
| Field | Value |
|---|---|
| Returns | REF CURSOR assign_header_cursor (assign_header_rec) via recordset |
| Called by | clsAssignmentProcess |
Description: Returns a richer header view of the most recent assignment result per case (joined to schedule, hearing location, ALJ, and claim), split via UNION into successfully assigned (CA/CAC) and not-assigned rows, filtered by in_type (ASSIGN/DATE/others follow the same pattern as get_results). Used to populate the assignment-results review screen, including "Not Assigned" placeholder attorney names for unresolved rows.
Parameters¶
| Parameter | Direction | Type | Default | Description |
|---|---|---|---|---|
recordset |
OUT | assign_header_cursor |
— | REF CURSOR of assign_header_rec rows |
in_num |
IN | NUMBER | — | Watermark/filter value, meaning depends on in_type |
in_type |
IN | VARCHAR2 | — | Query mode (ASSIGN, DATE, or other supported mode value) |
in_date |
IN | DATE | — | Result date filter, used for date-scoped modes |
get_assign_list¶
| Field | Value |
|---|---|
| Returns | REF CURSOR ref_cursor via recordset |
| Called by | clsAssignmentProcess |
Description: Returns the list of hearings assigned on in_date, including in-house attorney assignments (joined to period_case_count/period_case_load for that attorney's workload) unioned with outside-counsel assignments (asgn_other_firm_seq_num IS NOT NULL, reported as 'Outside Counsel'). Only rows where the most recent assignment result is CA/CAC (or has no result row yet, defaulted to CA) and the schedule's assigned_date matches the result's create_date are included — i.e. only the current, still-valid assignment per case.
Parameters¶
| Parameter | Direction | Type | Default | Description |
|---|---|---|---|---|
recordset |
OUT | ref_cursor | — | REF CURSOR of assignment-list rows |
in_date |
IN | DATE | — | Assignment date to list |
get_previously_assigned¶
| Field | Value |
|---|---|
| Returns | REF CURSOR ref_cursor via recordset |
| Called by | clsAssignmentProcess |
Description: Returns, for each new (assignment_result_seq_num > in_num) CA/CAC assignment result, the attorney and hearing details of the case's immediately prior scheduled hearing (the max schedule_seq_num less than the current one) — used to detect and continue a case with the same previously-assigned attorney.
Parameters¶
| Parameter | Direction | Type | Default | Description |
|---|---|---|---|---|
in_num |
IN | NUMBER | — | Watermark sequence number for new assignment results |
recordset |
OUT | ref_cursor | — | REF CURSOR of prior-hearing/attorney rows |
updateSchedule¶
| Field | Value |
|---|---|
| Returns | None (procedure) |
| Called by | assignIt, assignItBalanced (internal) |
Description: Updates the schedule row identified by schedSeqNum, setting the assigned attorney, assignment date, and audit update date/user. Called immediately after a successful assignIt/assignItBalanced evaluation to persist the winning attorney.
Parameters¶
| Parameter | Direction | Type | Default | Description |
|---|---|---|---|---|
schedSeqNum |
IN | NUMBER | — | schedule_seq_num of the hearing being updated |
attyEmplid |
IN | VARCHAR2 | — | Employee ID of the newly assigned attorney |
asgnDate |
IN | DATE | — | Assignment date (SYSDATE at call time) |
updateDate |
IN | DATE | — | Audit update date (SYSDATE at call time) |
updateUser |
IN | VARCHAR2 | — | Audit update user (literal 'Assignment' at call time) |
calendar_review¶
| Field | Value |
|---|---|
| Returns | REF CURSOR ref_cursor via recordset |
| Called by | clsAssignmentProcess |
Description: Returns, for every active attorney profile (exp_date IS NULL) at the requested location (or all locations when in_loc_code = 'ALL'), their schedule entry for in_date (if any, via a left outer join so unscheduled attorneys still appear), computed weekly load, period case load, vacation days, and hearing days — the data set behind the attorney calendar-review grid. Location filtering matches the attorney's primary, secondary, or tertiary assigned location.
Parameters¶
| Parameter | Direction | Type | Default | Description |
|---|---|---|---|---|
in_loc_code |
IN | VARCHAR2 | — | Hearing location code to filter by, or 'ALL' for every location |
in_date |
IN | DATE | — | Calendar date to review |
recordset |
OUT | ref_cursor | — | REF CURSOR of attorney/schedule/workload rows |
getOutlookFields¶
| Field | Value |
|---|---|
| Returns | REF CURSOR ref_cursor via recordset |
| Called by | clsAssignmentProcess |
Description: Returns hearing/schedule fields formatted for Outlook calendar synchronization, in one of three modes selected by inType: CASE returns all future (within the last 3 months and beyond) scheduled hearings for a single case (aNum = legal_case_seq_num); ASSIGN returns hearings for new (assignment_result_seq_num > aNum) current assignment results; VERIFY returns each case's current most-recent future hearing. All three join to consolidated-case data and include ALJ name, location, duration, assigned attorney's user ID (Outlook mail alias), schedule text, and case weight.
Parameters¶
| Parameter | Direction | Type | Default | Description |
|---|---|---|---|---|
recordset |
OUT | ref_cursor | — | REF CURSOR of Outlook-field rows |
inType |
IN | VARCHAR2 | — | Query mode: CASE, ASSIGN, or VERIFY |
aNum |
IN | NUMBER | — | legal_case_seq_num (CASE mode) or watermark sequence (ASSIGN mode); unused in VERIFY mode |
getAttorneyPastHearings¶
| Field | Value |
|---|---|
| Returns | REF CURSOR ref_cursor via recordset |
| Called by | clsAttorneyConflict |
Description: Returns hearings scheduled in the past (schedule_date < TRUNC(SYSDATE), status SET/RESET) for the case's most recent hearing. When inUserid = 'OUTCOU', returns past outside-counsel (other_firm) hearings across all cases; otherwise returns past hearings for the in-house attorney identified by inUserid (adp_employee.user_id), ordered by scheduled date.
Parameters¶
| Parameter | Direction | Type | Default | Description |
|---|---|---|---|---|
recordset |
OUT | ref_cursor | — | REF CURSOR of past-hearing rows |
inUserid |
IN | VARCHAR2 | — | Attorney user ID, or the literal 'OUTCOU' to select outside-counsel hearings |
getAttorneyConflictHearings¶
| Field | Value |
|---|---|
| Returns | REF CURSOR ref_cursor via recordset |
| Called by | clsAttorneyConflict |
Description: Detects future (schedule_date > TRUNC(SYSDATE)) same-time/same-place hearing conflicts on open (case_status_code <> 'CLOSE') cases: same-day non-multi-day (d.days_num = 0) hearings at the same ALJ/location assigned to a different attorney than the requester (excluding APEAL-type attorney profiles, i.e. reconsideration cases), unioned with a symmetrical multi-day overlap query. Used to flag scheduling conflicts on the attorney-conflict screen when inUserid = 'CONFLICT'.
Parameters¶
| Parameter | Direction | Type | Default | Description |
|---|---|---|---|---|
recordset |
OUT | ref_cursor | — | REF CURSOR of conflict-hearing rows |
inUserid |
IN | VARCHAR2 | — | Must equal the literal 'CONFLICT' to trigger the query; otherwise no cursor is opened* |
getAttorneyScheduleConflict¶
| Field | Value |
|---|---|
| Returns | REF CURSOR ref_cursor via recordset |
| Called by | clsAttorneySchedule |
Description: Returns, for the attorney identified by inUserid, every future (schedule_date > TRUNC(SYSDATE)) hearing/event on an open case that overlaps another schedule entry for the same attorney at a different ALJ or location (same-day and multi-day duration variants), plus overlapping attorney_schedule "Time Off" entries (day-based and multi-day variants) — a consolidated double-booking and time-off conflict report for a single attorney, ordered by scheduled date.
Parameters¶
| Parameter | Direction | Type | Default | Description |
|---|---|---|---|---|
recordset |
OUT | ref_cursor | — | REF CURSOR of schedule-conflict rows |
inUserid |
IN | VARCHAR2 | — | Attorney user ID (adp_employee.user_id) to check for conflicts |
getConsolFileLocation¶
| Field | Value |
|---|---|
| Returns | REF CURSOR ref_cursor via recordset |
| Called by | clsAssignmentProcess |
Description: For a primary consolidated case (inLegalCaseId), returns each secondary case whose most-recent physical legal-file location (attorney/location, both by max move_date) differs from the primary case's most-recent file location — identifying consolidated cases whose paper files are not co-located with the lead case's file.
Parameters¶
| Parameter | Direction | Type | Default | Description |
|---|---|---|---|---|
inLegalCaseId |
IN | NUMBER | — | legal_case_seq_num of the primary (consolidating) case |
recordset |
OUT | ref_cursor | — | REF CURSOR of mismatched secondary-case/location rows |
Send_Assignment_Info¶
| Field | Value |
|---|---|
| Returns | None (procedure) |
| Called by | Not referenced elsewhere in this package body; likely invoked from an external batch-monitoring job* |
Description: Sends an email via pk10001_email.send, from gremil@saif.com to a fixed distribution list (gremil@saif.com, timfor@saif.com, brisly@saif.com), with a fixed subject of "Attorney Assignment Info" and the caller-supplied body text — used to notify the legal-operations team of batch assignment run status or exceptions.*
Parameters¶
| Parameter | Direction | Type | Default | Description |
|---|---|---|---|---|
inText |
IN | VARCHAR2 | — | Email body text |
Business Rules¶
Rules extracted from this procedure's logic:
| Rule ID | Rule | Condition | Action |
|---|---|---|---|
| BR-PKG2-001 | Employer conflict blocks assignment | employer_flag = 'Y' and the candidate attorney has an active attorney_conflict row for the claim's policy number |
Increment employer_result; if final result is non-zero, record NAEMP and do not assign |
| BR-PKG2-002 | Adjuster conflict blocks assignment | adjuster_flag = 'Y' and the candidate attorney has an active conflict tied to the case's assigned adjuster (matched by user_id) |
Increment adjuster_result; record NAADJ and do not assign |
| BR-PKG2-003 | ALJ conflict blocks assignment | alj_flag = 'Y' and the candidate attorney has an active conflict tied to the hearing's ALJ (conflict_legal_person_seq_num) |
Increment alj_result; record NAALJ and do not assign |
| BR-PKG2-004 | Opposing-representation conflict always checked | Candidate's atty_provider_location_seq_num matches the claimant's worker-attorney firm location on the case (checked unconditionally, no flag gate) |
Increment attorney_result; record NAATT and do not assign |
| BR-PKG2-005 | Rotation duty blocks off-rotation assignment | rotation_flag = 'Y' and the candidate is on out_of_town_rotation_assgn duty for a different location during the hearing's effective window |
Increment rotation_result; record NAROT and do not assign |
| BR-PKG2-006 | Calendar double-booking blocks assignment | calendar_flag in (Y,H,C) and the candidate has an overlapping SET/RESET schedule entry at a different ALJ or location for the same date/duration |
Increment calendar_result; record NAC and do not assign |
| BR-PKG2-007 | Long-leave blackout window blocks assignment | calendar_flag in (Y,H,C), in_code = 'Y', and today falls within 3 days before through 1 day after a LEAVE entry of more than 4 days |
Increment vacation_result; record NAVAC and do not assign |
| BR-PKG2-008 | Location conflict blocks assignment | location_flag = 'Y' and the candidate is already scheduled elsewhere on the same date at a different hearing location |
Increment location_result; record NALCC and do not assign |
| BR-PKG2-009 | Weekly caseload floor exempts low-load attorneys from the load check | weekly_case_load > 0 and the attorney's get_hearing_days count is below a load-percent-derived minimum (1 day if in_load_percent < 34, 2 days if < 67) |
load_result is reset to 0, so LOAD is not recorded and the attorney remains eligible |
| BR-PKG2-010 | Holiday-shifted hearing date flags a result but never blocks assignment | sp72353_next_work_day(schedule_date) <> schedule_date (i.e. the hearing date is a non-work day) |
Increment holiday_result and record NAHOL if the attorney is otherwise not assigned, but holiday_result is excluded from the assignment-success determination in the observed rule set* |
| BR-PKG2-011 | Successful assignment updates case status | All gated conflict-check results sum to zero for the candidate | insert_results(<args>,'CA',<args>), updateSchedule(<args>), and Pk80001_Legalnet.updatecasestatus(<args>,'ASSGN',<args>) are called, and the schedule row's assigned attorney/date are persisted |
| BR-PKG2-012 | Assignment-result codes distinguish success from every blocking reason | Result code CA/CAC indicates a completed assignment; codes NAEMP, NAADJ, NAALJ, NAATT, NAROT, NAC, NAVAC, NAHOL, LOAD, NALCC each identify a single specific blocking rule |
Downstream cursors (get_results, get_assign_results, get_assign_list) filter or interpret rows using these codes to distinguish assigned vs. not-assigned cases |
These rules are documented in business-rules.md. Newly introduced rules must be added to
business-rules.mdas part of the same writing task.
Operations¶
Operations and processes implemented in this procedure:
| Operation | Description | Trigger |
|---|---|---|
| Run attorney assignment batch | Sequentially executes assign_by_employer, assign_dcbs_cases, assign_recon_cases, assign_rotation_cases, assign_same, assign_health_issue_cases, assign_wrkr_conflict_cases, assign_critical_claim_cases, assign_portland_cases, assign_remaining_cases, and previously_assigned over PENDA cases with unassigned SET/RESET hearings, evaluating each candidate attorney against the conflict-check rule set via assignIt/assignItBalanced and recording an assignment result for every outcome |
Scheduled/batch invocation of the legal assignment process (clsAssignmentProcess) |
| Review assignment results | Retrieves newly created or historical assignment results (success and blocking reasons) via get_results, get_assign_results, and get_assign_list for display on the assignment-review screen |
User opens the assignment-results or assignment-list screen in the Legal application |
| Attorney calendar review | Builds the per-attorney calendar grid (schedule, weekly load, case load, vacation days, hearing days) across one or all hearing locations via calendar_review |
User opens the calendar-review screen |
| Sync hearings to Outlook | Supplies case/assignment/schedule fields formatted for Outlook calendar entries via getOutlookFields, in case, new-assignment, or verification modes |
Outlook calendar synchronization job or screen refresh |
| Attorney conflict and schedule review | Surfaces past hearings, same-time/place conflicts, and general schedule double-bookings for a given attorney via getAttorneyPastHearings, getAttorneyConflictHearings, and getAttorneyScheduleConflict |
User opens the attorney-conflict or attorney-schedule screen |
| Consolidated case file-location check | Identifies secondary cases in a consolidation whose physical file location differs from the primary case via getConsolFileLocation |
User opens the consolidated-case file-location screen |
These operations are also listed in operations-processes.md with source reference back to this document.
Source Code¶
PL/SQL package specification
-- Actual PL/SQL spec retrieved from Oracle via Legends CLI
-- Source: DBA_SOURCE / ALL_SOURCE for WCIS_DBA.pk80005_assignment
PACKAGE pk80005_ASSIGNMENT AS
TYPE ref_cursor IS REF CURSOR;
TYPE attorney_rec IS RECORD(
emplid VARCHAR2(11),
primary_loc VARCHAR2(5),
secondary_loc VARCHAR2(5),
tertiary_loc VARCHAR2(5),
load_percent NUMBER,
case_load NUMBER,
attorney_last_name VARCHAR2(40));
TYPE attorney_tab IS TABLE OF attorney_rec INDEX BY
BINARY_INTEGER;
TYPE assignment_rec IS RECORD(
legal_case_seq_num NUMBER,
schedule_date DATE,
hearing_location_seq_num NUMBER,
alj_person_seq_num NUMBER,
duration_code VARCHAR2(5),
schedule_seq_num NUMBER,
hearing_location_code VARCHAR2(5),
primary_emplid VARCHAR2(11),
secondary_emplid VARCHAR2(11),
tertiary_emplid VARCHAR2(11),
adjuster_flag VARCHAR2(1),
alj_flag VARCHAR2(1),
rotation_flag VARCHAR2(1),
calendar_flag VARCHAR2(1),
employer_flag VARCHAR2(1),
weekly_case_load NUMBER,
location_flag VARCHAR2(1),
wcb_num VARCHAR2(10));
TYPE assign_cursor IS REF CURSOR RETURN assignment_rec;
FUNCTION adjuster_conflict_check (in_case_seq IN NUMBER, in_emplid IN VARCHAR2) RETURN NUMBER;
FUNCTION employer_conflict_check (in_case_seq IN NUMBER, in_emplid IN VARCHAR2) RETURN NUMBER;
FUNCTION attorney_conflict_check (in_case_seq IN NUMBER, in_emplid IN VARCHAR2) RETURN NUMBER;
FUNCTION alj_conflict_check (in_alj_seq IN NUMBER, in_emplid IN VARCHAR2)
RETURN NUMBER;
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;
FUNCTION location_conflict_check (in_emplid IN VARCHAR2,
in_date IN DATE, in_duration_code IN VARCHAR2,
in_hear_loc IN NUMBER) RETURN NUMBER;
FUNCTION vacation_conflict_check (in_emplid IN VARCHAR2,
in_date IN DATE, in_duration_code IN VARCHAR2, in_code IN VARCHAR2) RETURN NUMBER;
FUNCTION case_load(in_emplid IN VARCHAR2) RETURN NUMBER;
FUNCTION period_case_load(in_emplid IN VARCHAR2, hearingDate IN DATE)
RETURN NUMBER;
FUNCTION period_case_count(in_emplid IN VARCHAR2, hearingDate IN DATE)
RETURN NUMBER;
FUNCTION get_weekly_case_load(in_emplid IN VARCHAR2, hearingDate IN DATE)
RETURN NUMBER;
FUNCTION get_hearing_days(in_emplid IN VARCHAR2, hearingDate IN DATE)
RETURN NUMBER;
FUNCTION get_vacation_days(in_emplid IN VARCHAR2, hearingDate IN DATE,
in_before IN NUMBER, in_after IN NUMBER)
RETURN NUMBER;
FUNCTION max_result_seq RETURN NUMBER;
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);
FUNCTION rotation_conflict_check (in_emplid IN VARCHAR2,
in_date IN DATE, in_hear_code IN VARCHAR2) RETURN NUMBER;
FUNCTION same_time_loc_check (in_date IN DATE,
in_duration_code IN VARCHAR2,
in_hear_loc IN NUMBER,
in_alj IN NUMBER,
in_is_critical IN VARCHAR2,
in_request_type IN VARCHAR2,
in_wrkr_conflict IN VARCHAR2)
RETURN VARCHAR2;
FUNCTION assignIt(in_code IN VARCHAR2,
in_emplid IN VARCHAR2,
in_load_percent IN NUMBER,
in_assign_rec IN assignment_rec)
RETURN VARCHAR2;
FUNCTION assignItBalanced(in_code IN VARCHAR2,
in_emplid IN VARCHAR2,
in_load_percent IN NUMBER,
in_assign_rec IN assignment_rec)
RETURN VARCHAR2;
PROCEDURE assign_by_employer(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,
in_run_id IN VARCHAR2);
PROCEDURE assign_dcbs_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);
PROCEDURE assign_recon_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);
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);
PROCEDURE assign_same(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,
in_run_id IN VARCHAR2);
PROCEDURE assign_health_issue_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);
PROCEDURE assign_wrkr_conflict_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,
in_run_id IN VARCHAR2);
PROCEDURE assign_critical_claim_cases(inAdjuster_flag IN VARCHAR2,
inAlj_flag IN VARCHAR2,
inRotation_flag IN VARCHAR2,
inCalendar_flag IN VARCHAR2,
inEmployer_flag IN VARCHAR2,
inWeekly_case_load IN NUMBER,
inlocation_flag IN VARCHAR2,
in_run_id IN VARCHAR2);
PROCEDURE assign_portland_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);
PROCEDURE assign_remaining_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,
in_run_id IN VARCHAR2);
PROCEDURE previously_assigned(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);
TYPE assign_results_rec IS RECORD(
asgn_result_seq_num NUMBER,
legal_case_seq_num NUMBER,
schedule_seq_num NUMBER,
case_num VARCHAR2(8),
rule_descr VARCHAR2(65),
result_descr VARCHAR2(65),
asgn_attorney_emplid VARCHAR2(11),
asgn_attorney_name VARCHAR2(65));
TYPE assign_results_cursor IS REF CURSOR RETURN assign_results_rec;
PROCEDURE get_results(in_num IN NUMBER, in_type IN VARCHAR2,
in_date IN DATE,
recordset OUT assign_results_cursor);
TYPE assign_header_rec IS RECORD(
asgn_result_seq_num NUMBER,
legal_case_seq_num NUMBER,
schedule_seq_num NUMBER,
case_num VARCHAR2(8),
asgn_attorney_emplid VARCHAR2(11),
asgn_attorney_name VARCHAR2(65),
hearing_date DATE,
create_date DATE,
hearing_location VARCHAR2(65),
result_code VARCHAR2(5),
email_address VARCHAR2(30),
alj_name VARCHAR2(50),
schedule_time VARCHAR2(8),
claim_id VARCHAR2(25));
TYPE assign_header_cursor IS REF CURSOR RETURN assign_header_rec;
PROCEDURE get_assign_results(recordset OUT assign_header_cursor, in_num IN NUMBER,
in_type IN VARCHAR2, in_date IN DATE);
PROCEDURE get_assign_list(recordset OUT ref_cursor,
in_date IN DATE);
PROCEDURE get_previously_assigned(in_num IN NUMBER,
recordset OUT ref_cursor);
PROCEDURE updateSchedule(schedSeqNum IN NUMBER,
attyEmplid IN VARCHAR2,asgnDate IN DATE,
updateDate IN DATE, updateUser IN VARCHAR2);
PROCEDURE calendar_review(in_loc_code IN VARCHAR2,
in_date IN DATE, recordset OUT ref_cursor);
PROCEDURE getOutlookFields(recordset OUT ref_cursor,
inType IN VARCHAR2, aNum IN NUMBER);
PROCEDURE getAttorneyPastHearings(recordset OUT ref_cursor, inUserid IN VARCHAR2);
PROCEDURE getAttorneyConflictHearings(recordset OUT ref_cursor,
inUserid IN VARCHAR2);
PROCEDURE getAttorneyScheduleConflict(recordset OUT ref_cursor, inUserid IN VARCHAR2);
PROCEDURE getConsolFileLocation(inLegalCaseId IN NUMBER,
recordset OUT ref_cursor);
PROCEDURE Send_Assignment_Info(inText IN VARCHAR2);
END;
PL/SQL package/procedure body
-- Actual PL/SQL body retrieved from Oracle via Legends CLI
-- Source: DBA_SOURCE / ALL_SOURCE for WCIS_DBA.pk80005_assignment
PACKAGE BODY pk80005_assignment
AS
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 employer_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
WHERE l.legal_case_seq_num = in_Case_seq
AND a.attorney_emplid = in_emplid
AND a.EXP_DATE IS NULL
AND l.claim_id = c.clm_id
AND C.POLICY_NUM = a.POLICY_NUM;
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 employer_conflict_check;
FUNCTION alj_conflict_check (in_alj_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
WHERE conflict_legal_person_seq_num = in_ALJ_seq
AND exp_date IS NULL
AND attorney_emplid = in_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 alj_conflict_check;
FUNCTION attorney_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
WHERE l.legal_case_seq_num = in_Case_seq
AND a.attorney_emplid = in_emplid
AND a.EXP_DATE IS NULL
AND A.ATTY_PROVIDER_LOCATION_SEQ_NUM =
L.WORKER_ATTY_FIRM_PROV_LOC_ID;
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 attorney_conflict_check;
PROCEDURE assign_by_employer (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,
in_run_id IN VARCHAR2)
IS
v_assigned VARCHAR2 (1);
assigns_cursor assign_cursor;
assign_rec assigns_cursor%ROWTYPE;
BEGIN
OPEN assigns_cursor FOR
SELECT l.legal_case_seq_num,
schedule_date,
s.hearing_location_seq_num,
alj_person_seq_num,
duration_code,
schedule_seq_num,
h.hearing_location_code,
primary_emplid,
NVL (secondary_emplid, 'NONE') AS secondary_emplid,
NVL (tertiary_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,
CLM_DBA.temp_data_person_address c,
hearing_location h,
attorney_policy_assignment a,
schedule s,
policy p
WHERE l.claim_id = c.clm_id
AND c.run_id = in_run_id
AND l.legal_case_seq_num = s.legal_case_seq_num
AND c.policy_num = p.policy_num
AND ( p.policy_num = a.policy_num
OR P.POLICY_RELATIONSHIP_POLICY_NUM = A.POLICY_NUM
OR P.RATE_ACCOUNT = A.POLICY_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 a.exp_date IS NULL;
LOOP
FETCH assigns_cursor INTO assign_rec;
EXIT WHEN assigns_cursor%NOTFOUND;
v_assigned :=
assignIt ('EA',
assign_rec.primary_emplid,
100,
assign_rec);
IF v_assigned = 'N' AND assign_rec.secondary_emplid <> 'NONE'
THEN
v_assigned :=
assignIt ('EA',
assign_rec.secondary_emplid,
100,
assign_rec);
END IF;
IF v_assigned = 'N' AND assign_rec.tertiary_emplid <> 'NONE'
THEN
v_assigned :=
assignIt ('EA',
assign_rec.tertiary_emplid,
100,
assign_rec);
END IF;
IF v_assigned = 'N'
THEN
assign_rec.adjuster_flag := 'N';
assign_rec.alj_flag := 'N';
assign_rec.calendar_flag := 'N';
assign_rec.employer_flag := 'N';
assign_rec.rotation_flag := 'N';
assign_rec.weekly_case_load := 0;
assign_rec.location_flag := 'N';
v_assigned :=
assignIt ('EA',
assign_rec.primary_emplid,
100,
assign_rec);
END IF;
END LOOP;
CLOSE assigns_cursor;
END assign_by_employer;
PROCEDURE assign_critical_claim_cases (inAdjuster_flag IN VARCHAR2,
inAlj_flag IN VARCHAR2,
inRotation_flag IN VARCHAR2,
inCalendar_flag IN VARCHAR2,
inEmployer_flag IN VARCHAR2,
inWeekly_case_load IN NUMBER,
inlocation_flag IN VARCHAR2,
in_run_id IN VARCHAR2)
IS
v_assigned VARCHAR2 (1);
v_index NUMBER;
v_cnt NUMBER;
v_first NUMBER;
v_start NUMBER;
last_date DATE;
last_alj NUMBER := 0;
last_duration VARCHAR2 (5);
last_emplid VARCHAR2 (11);
attorney_data attorney_tab;
assigns_cursor assign_cursor;
assign_rec assigns_cursor%ROWTYPE;
PROCEDURE load_attorney_table (schedule_date IN DATE)
IS
CURSOR attorney_cursor
IS
SELECT profile_emplid,
primary_hear_loc_code,
NVL (secondary_hear_loc_code, 'NONE'),
NVL (tertiary_hear_loc_code, 'NONE'),
load_percent,
period_case_load (profile_emplid, schedule_date)
FROM attorney_profile
WHERE ccu_claims_flag = 'Y'
AND ( attorney_type_code = 'TRIAL'
OR attorney_type_code = 'FED')
AND load_percent > 0
ORDER BY 2, 5;
BEGIN
OPEN attorney_cursor;
v_index := 1;
LOOP
FETCH attorney_cursor
INTO attorney_data (v_index).emplid,
attorney_data (v_index).primary_loc,
attorney_data (v_index).secondary_loc,
attorney_data (v_index).tertiary_loc,
attorney_data (v_index).load_percent,
attorney_data (v_index).case_load;
EXIT WHEN attorney_cursor%NOTFOUND;
v_index := v_index + 1;
END LOOP;
v_cnt := attorney_data.COUNT;
v_index := 1;
v_start := 1;
CLOSE attorney_cursor;
END load_attorney_table;
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,
'none' AS primary_emplid,
'none' AS secondary_emplid,
'none' AS tertiary_emplid,
inAdjuster_flag,
inAlj_flag,
inRotation_flag,
inCalendar_flag,
inEmployer_flag,
inWeekly_case_load,
inlocation_flag,
wcb_num
FROM legal_case l,
schedule s,
hearing_location h,
CLM_DBA.temp_data_person_address Clm
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 l.request_type_code = 'WCB'
AND l.claim_id = clm.clm_id
AND clm.run_id = in_run_id
AND clm.is_critical = 'Y'
AND NOT EXISTS
(SELECT 1
FROM request_issue r, request_issue_code c
WHERE r.legal_case_seq_num =
l.legal_case_seq_num
AND r.request_issue_code = c.code
AND c.recon_issue_flag = 'Y')
ORDER BY schedule_date, alj_person_seq_num, duration_code;
LOOP
FETCH assigns_cursor INTO assign_rec;
EXIT WHEN assigns_cursor%NOTFOUND;
v_first := 0;
v_assigned := 'N';
IF last_alj = assign_rec.alj_person_seq_num
AND last_date = assign_rec.schedule_date
AND last_duration = assign_rec.duration_code
THEN
v_assigned :=
assignIt ('SAME',
last_emplid,
100,
assign_rec);
ELSE
load_attorney_table (assign_rec.schedule_date);
FOR v_index IN v_start .. v_cnt
LOOP
IF attorney_data (v_index).primary_loc =
assign_rec.hearing_location_code
THEN
v_assigned :=
assignIt ('CRTCL',
attorney_data (v_index).emplid,
attorney_data (v_index).load_percent,
assign_rec);
IF v_first = 0
THEN
v_first := v_index;
END IF;
IF v_assigned = 'Y'
THEN
v_start := v_index + 1;
last_emplid := attorney_data (v_index).emplid;
IF v_start > v_cnt
THEN
v_start := 1;
END IF;
EXIT;
END IF;
END IF;
END LOOP;
IF v_assigned = 'N' AND v_start > 1
THEN
FOR v_index IN 1 .. v_start - 1
LOOP
IF attorney_data (v_index).primary_loc =
assign_rec.hearing_location_code
THEN
v_assigned :=
assignIt ('CRTCL',
attorney_data (v_index).emplid,
attorney_data (v_index).load_percent,
assign_rec);
IF v_first = 0
THEN
v_first := v_index;
END IF;
IF v_assigned = 'Y'
THEN
v_start := v_index + 1;
last_emplid := attorney_data (v_index).emplid;
IF v_start > v_cnt
THEN
v_start := 1;
END IF;
EXIT;
END IF;
END IF;
END LOOP;
END IF;
IF v_assigned = 'N'
THEN
FOR v_index IN 1 .. v_cnt
LOOP
IF attorney_data (v_index).secondary_loc =
assign_rec.hearing_location_code
THEN
v_assigned :=
assignIt ('CRTCL',
attorney_data (v_index).emplid,
attorney_data (v_index).load_percent,
assign_rec);
IF v_first = 0
THEN
v_first := v_index;
END IF;
IF v_assigned = 'Y'
THEN
last_emplid := attorney_data (v_index).emplid;
EXIT;
END IF;
END IF;
END LOOP;
END IF;
IF v_assigned = 'N'
THEN
FOR v_index IN 1 .. v_cnt
LOOP
IF attorney_data (v_index).tertiary_loc =
assign_rec.hearing_location_code
THEN
v_assigned :=
assignIt ('CRTCL',
attorney_data (v_index).emplid,
attorney_data (v_index).load_percent,
assign_rec);
IF v_first = 0
THEN
v_first := v_index;
END IF;
IF v_assigned = 'Y'
THEN
last_emplid := attorney_data (v_index).emplid;
EXIT;
END IF;
END IF;
END LOOP;
END IF;
-- try to assign to first atty without looking at case load
IF v_assigned = 'N'
THEN
IF v_first > 0
THEN
assign_rec.Weekly_case_load := 0;
assign_rec.location_flag := 'N';
v_assigned :=
assignIt ('CRTCL',
attorney_data (v_first).emplid,
attorney_data (v_first).load_percent,
assign_rec);
last_emplid := attorney_data (v_first).emplid;
ELSE
insert_results (assign_rec.legal_case_seq_num,
'CRTCL',
assign_rec.schedule_seq_num,
'NALOC',
'0');
END IF;
END IF;
END IF;
IF v_assigned = 'Y'
THEN
last_date := assign_rec.schedule_date;
last_alj := assign_rec.alj_person_seq_num;
last_duration := assign_rec.duration_code;
END IF;
END LOOP;
CLOSE assigns_cursor;
END assign_critical_claim_cases;
PROCEDURE assign_dcbs_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
v_assigned VARCHAR2 (1);
v_index NUMBER;
v_cnt NUMBER;
v_first NUMBER;
v_start NUMBER := 1;
last_date DATE;
last_alj NUMBER := 0;
last_duration VARCHAR2 (5);
last_emplid VARCHAR2 (11);
attorney_data attorney_tab;
assigns_cursor assign_cursor;
assign_rec assigns_cursor%ROWTYPE;
no_longer_active EXCEPTION;
PRAGMA EXCEPTION_INIT (no_longer_active, -20001);
BEGIN
RAISE no_longer_active;
END assign_dcbs_cases;
PROCEDURE assign_health_issue_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
v_assigned VARCHAR2 (1);
v_index NUMBER;
v_cnt NUMBER;
v_first NUMBER;
last_date DATE;
last_alj NUMBER := 0;
last_duration VARCHAR2 (5);
last_emplid VARCHAR2 (11);
assigns_cursor assign_cursor;
assign_rec assigns_cursor%ROWTYPE;
attorney_data attorney_tab;
no_longer_active EXCEPTION;
PRAGMA EXCEPTION_INIT (no_longer_active, -20001);
BEGIN
RAISE no_longer_active;
END assign_health_issue_cases;
PROCEDURE assign_portland_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
v_assigned VARCHAR2 (1);
v_index NUMBER;
v_cnt NUMBER;
v_first NUMBER;
v_start NUMBER;
last_date DATE;
last_alj NUMBER := 0;
last_duration VARCHAR2 (5);
last_emplid VARCHAR2 (11);
assigns_cursor assign_cursor;
assign_rec assigns_cursor%ROWTYPE;
attorney_data attorney_tab;
no_longer_active EXCEPTION;
PRAGMA EXCEPTION_INIT (no_longer_active, -20001);
BEGIN
RAISE no_longer_active;
END assign_portland_cases;
PROCEDURE assign_recon_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
v_assigned VARCHAR2 (1);
v_last VARCHAR2 (11);
v_index NUMBER;
v_cnt NUMBER;
v_first NUMBER;
attorney_data attorney_tab;
assigns_cursor assign_cursor;
assign_rec assigns_cursor%ROWTYPE;
no_longer_active EXCEPTION;
PRAGMA EXCEPTION_INIT (no_longer_active, -20001);
BEGIN
RAISE no_longer_active;
END assign_recon_cases;
PROCEDURE assign_remaining_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,
in_run_id IN VARCHAR2)
IS
v_email_text VARCHAR2 (4000) := '';
v_case_count NUMBER := 0;
v_assigned VARCHAR2 (1);
v_index NUMBER;
v_cnt NUMBER;
v_first NUMBER;
v_start NUMBER;
last_date DATE;
last_alj NUMBER := 0;
last_duration VARCHAR2 (5);
last_emplid VARCHAR2 (11);
assigns_cursor assign_cursor;
assign_rec assigns_cursor%ROWTYPE;
attorney_data attorney_tab;
crlf VARCHAR2 (2) := CHR (13) || CHR (10);
PROCEDURE load_attorney_table (schedule_date IN DATE)
IS
CURSOR attorney_cursor
IS
SELECT profile_emplid,
primary_hear_loc_code,
NVL (secondary_hear_loc_code, 'NONE'),
NVL (tertiary_hear_loc_code, 'NONE'),
load_percent,
period_case_load (profile_emplid, schedule_date) case_load,
AE.LAST_NAME
FROM attorney_profile ap, adp_employee ae
WHERE ( attorney_type_code = 'TRIAL'
OR attorney_type_code = 'FED')
AND AP.PROFILE_EMPLID = AE.EMPLID
AND load_percent > 0
AND profile_emplid <> 'OUTSIDECNCL'
ORDER BY 6, 5 DESC, 2;
BEGIN
OPEN attorney_cursor;
v_index := 1;
LOOP
FETCH attorney_cursor
INTO attorney_data (v_index).emplid,
attorney_data (v_index).primary_loc,
attorney_data (v_index).secondary_loc,
attorney_data (v_index).tertiary_loc,
attorney_data (v_index).load_percent,
attorney_data (v_index).case_load,
attorney_data (v_index).attorney_last_name;
EXIT WHEN attorney_cursor%NOTFOUND;
v_index := v_index + 1;
END LOOP;
CLOSE attorney_cursor;
v_cnt := attorney_data.COUNT;
v_index := 1;
v_start := 1;
END load_attorney_table;
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,
'None' AS primary_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,
CLM_DBA.temp_data_person_address clm
WHERE l.legal_case_seq_num = s.legal_case_seq_num
AND l.CLAIM_ID = clm.CLM_ID
AND clm.run_id = in_run_id
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 l.request_type_code = 'WCB'
AND ( NVL (clm.wrkr_conflict_flag, 'N') = 'N'
OR clm.wrkr_conflict_flag = ' ')
AND (NVL (clm.is_critical, 'N') = 'N'
OR clm.is_critical = ' ')
AND NOT EXISTS
(SELECT 1
FROM request_issue r, request_issue_code c
WHERE r.legal_case_seq_num =
l.legal_case_seq_num
AND r.request_issue_code = c.code
AND c.recon_issue_flag = 'Y')
ORDER BY s.schedule_date, s.alj_person_seq_num, s.duration_code;
LOOP
FETCH assigns_cursor INTO assign_rec;
EXIT WHEN assigns_cursor%NOTFOUND;
v_first := 0;
v_assigned := 'N';
IF last_alj = assign_rec.alj_person_seq_num
AND last_date = assign_rec.schedule_date
AND last_duration = assign_rec.duration_code
THEN
assign_rec.weekly_case_load := 0;
v_case_count :=
period_case_count (attorney_data (v_index).emplid,
assign_rec.schedule_date);
v_assigned :=
assignItBalanced ('SAME',
last_emplid,
100,
assign_rec);
v_email_text :=
v_email_text
|| 'SAME - assigned to emplid / attorney / loc / wcb_num / hearingDate / starting caseCount / starting caseLoad: '
|| attorney_data (v_index).emplid
|| ' / '
|| attorney_data (v_index).attorney_last_name
|| ' / '
|| attorney_data (v_index).primary_loc
|| ' / '
|| assign_rec.wcb_num
|| ' / '
|| assign_rec.schedule_date
|| ' / '
|| v_case_count
|| ' / '
|| (attorney_data (v_index).case_load)
|| crlf;
-- DBMS_OUTPUT.PUT_LINE('SAME - assigned to emplid / attorney / loc / wcb_num / hearingDate / starting caseload: ' || attorney_data (v_index).emplid || ' / ' || attorney_data (v_index).attorney_last_name || ' / ' || attorney_data (v_index).primary_loc || ' / ' || assign_rec.wcb_num || ' / ' || assign_rec.schedule_date || ' / ' || (attorney_data (v_index).case_load));
ELSE
load_attorney_table (assign_rec.schedule_date);
-- don't understand why the rotation flag is getting set to 'N' force it to 'Y'
assign_rec.rotation_flag := 'Y';
FOR v_index IN v_start .. v_cnt
LOOP
IF attorney_data (v_index).primary_loc =
assign_rec.hearing_location_code
THEN
--DBMS_OUTPUT.PUT_LINE(' v_start ' || v_start);
--DBMS_OUTPUT.PUT_LINE(' v_index ' || v_index);
--DBMS_OUTPUT.PUT_LINE(' emplid ' || attorney_data (v_index).emplid);
v_case_count :=
period_case_count (attorney_data (v_index).emplid,
assign_rec.schedule_date);
v_assigned :=
assignItBalanced ('ASGN',
attorney_data (v_index).emplid,
attorney_data (v_index).load_percent,
assign_rec);
IF v_first = 0
THEN
v_first := v_index;
END IF;
IF v_assigned = 'Y'
THEN
v_email_text :=
v_email_text
|| 'PRIMARY 1 - assigned to emplid / attorney / loc / wcb_num / hearingDate / starting caseCount / starting caseload: '
|| attorney_data (v_index).emplid
|| ' / '
|| attorney_data (v_index).attorney_last_name
|| ' / '
|| attorney_data (v_index).primary_loc
|| ' / '
|| assign_rec.wcb_num
|| ' / '
|| assign_rec.schedule_date
|| ' / '
|| v_case_count
|| ' / '
|| attorney_data (v_index).case_load
|| crlf;
-- DBMS_OUTPUT.PUT_LINE('PRIMARY 1 - assigned to emplid / attorney / loc / wcb_num / hearingDate / starting caseload: ' || attorney_data (v_index).emplid || ' / ' || attorney_data (v_index).attorney_last_name || ' / ' || attorney_data (v_index).primary_loc || ' / ' || assign_rec.wcb_num || ' / ' || assign_rec.schedule_date || ' / ' || (attorney_data (v_index).case_load));
v_start := v_index + 1;
last_emplid := attorney_data (v_index).emplid;
IF v_start > v_cnt
THEN
v_start := 1;
END IF;
EXIT;
END IF;
END IF;
END LOOP;
IF v_assigned = 'N' AND v_start > 1
THEN
FOR v_index IN 1 .. v_start - 1
LOOP
IF attorney_data (v_index).primary_loc =
assign_rec.hearing_location_code
THEN
v_case_count :=
period_case_count (attorney_data (v_index).emplid,
assign_rec.schedule_date);
v_assigned :=
assignItBalanced (
'ASGN',
attorney_data (v_index).emplid,
attorney_data (v_index).load_percent,
assign_rec);
IF v_first = 0
THEN
v_first := v_index;
END IF;
IF v_assigned = 'Y'
THEN
v_email_text :=
v_email_text
|| 'PRIMARY 2 - assigned to emplid / attorney / loc / wcb_num / hearingDate / starting caseCount / starting caseload: '
|| attorney_data (v_index).emplid
|| ' / '
|| attorney_data (v_index).attorney_last_name
|| ' / '
|| attorney_data (v_index).primary_loc
|| ' / '
|| assign_rec.wcb_num
|| ' / '
|| assign_rec.schedule_date
|| ' / '
|| v_case_count
|| ' / '
|| attorney_data (v_index).case_load
|| crlf;
-- DBMS_OUTPUT.PUT_LINE('PRIMARY 2 - assigned to emplid / attorney / loc / wcb_num / hearingDate / starting caseload: ' || attorney_data (v_index).emplid || ' / ' || attorney_data (v_index).attorney_last_name || ' / ' || attorney_data (v_index).primary_loc || ' / ' || assign_rec.wcb_num || ' / ' || assign_rec.schedule_date || ' / ' || (attorney_data (v_index).case_load));
v_start := v_index + 1;
last_emplid := attorney_data (v_index).emplid;
IF v_start > v_cnt
THEN
v_start := 1;
END IF;
EXIT;
END IF;
END IF;
END LOOP;
END IF;
IF v_assigned = 'N'
THEN
FOR v_index IN 1 .. v_cnt
LOOP
IF attorney_data (v_index).secondary_loc =
assign_rec.hearing_location_code
THEN
v_case_count :=
period_case_count (attorney_data (v_index).emplid,
assign_rec.schedule_date);
v_assigned :=
assignItBalanced (
'ASGN',
attorney_data (v_index).emplid,
attorney_data (v_index).load_percent,
assign_rec);
IF v_assigned = 'Y'
THEN
v_email_text :=
v_email_text
|| 'SECONDARY - assigned to emplid / attorney / loc / wcb_num / hearingDate / starting caseCount / starting caseload: '
|| attorney_data (v_index).emplid
|| ' / '
|| attorney_data (v_index).attorney_last_name
|| ' / '
|| attorney_data (v_index).primary_loc
|| ' / '
|| assign_rec.wcb_num
|| ' / '
|| assign_rec.schedule_date
|| ' / '
|| v_case_count
|| ' / '
|| attorney_data (v_index).case_load
|| crlf;
-- DBMS_OUTPUT.PUT_LINE('SECONDARY - assigned to emplid / attorney / loc / wcb_num / hearingDate / starting caseload: ' || attorney_data (v_index).emplid || ' / ' || attorney_data (v_index).attorney_last_name || ' / ' || attorney_data (v_index).secondary_loc || ' / ' || assign_rec.wcb_num || ' / ' || assign_rec.schedule_date || ' / ' || (attorney_data (v_index).case_load));
last_emplid := attorney_data (v_index).emplid;
EXIT;
END IF;
END IF;
END LOOP;
END IF;
IF v_assigned = 'N'
THEN
FOR v_index IN 1 .. v_cnt
LOOP
IF attorney_data (v_index).tertiary_loc =
assign_rec.hearing_location_code
THEN
v_case_count :=
period_case_count (attorney_data (v_index).emplid,
assign_rec.schedule_date);
v_assigned :=
assignItBalanced (
'ASGN',
attorney_data (v_index).emplid,
attorney_data (v_index).load_percent,
assign_rec);
IF v_assigned = 'Y'
THEN
v_email_text :=
v_email_text
|| 'TERTIARY - assigned to emplid / attorney / loc / wcb_num / hearingDate / starting caseCount / starting caseload: '
|| attorney_data (v_index).emplid
|| ' / '
|| attorney_data (v_index).attorney_last_name
|| ' / '
|| attorney_data (v_index).primary_loc
|| ' / '
|| assign_rec.wcb_num
|| ' / '
|| assign_rec.schedule_date
|| ' / '
|| v_case_count
|| ' / '
|| attorney_data (v_index).case_load
|| crlf;
-- DBMS_OUTPUT.PUT_LINE('TERTIARY - assigned to emplid / attorney / loc / wcb_num / hearingDate / starting caseload: ' || attorney_data (v_index).emplid || ' / ' || attorney_data (v_index).attorney_last_name || ' / ' || attorney_data (v_index).tertiary_loc || ' / ' || assign_rec.wcb_num || ' / ' || assign_rec.schedule_date || ' / ' || (attorney_data (v_index).case_load));
last_emplid := attorney_data (v_index).emplid;
EXIT;
END IF;
END IF;
END LOOP;
END IF;
IF v_assigned = 'Y'
THEN
last_date := assign_rec.schedule_date;
last_alj := assign_rec.alj_person_seq_num;
last_duration := assign_rec.duration_code;
ELSE
insert_results (assign_rec.legal_case_seq_num,
'ASGN',
assign_rec.schedule_seq_num,
'NALOC',
'0');
END IF;
END IF;
END LOOP;
--Send_Assignment_Info(v_email_text);
CLOSE assigns_cursor;
END assign_remaining_cases;
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;
LOOP
FETCH assigns_cursor INTO assign_rec;
EXIT WHEN assigns_cursor%NOTFOUND;
assign_rec.rotation_flag := 'N';
v_assigned :=
assignIt ('ROT',
assign_rec.primary_emplid,
100,
assign_rec);
END LOOP;
CLOSE assigns_cursor;
END assign_rotation_cases;
PROCEDURE assign_same (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,
in_run_id IN VARCHAR2)
IS
v_assigned VARCHAR2 (1);
v_emplid VARCHAR2 (11);
assigns_cursor assign_cursor;
assign_rec assigns_cursor%ROWTYPE;
CURSOR get_same_loc_date
IS
SELECT /*+ FIRST_ROWS */
l.legal_case_seq_num,
schedule_date,
hearing_location_seq_num,
alj_person_seq_num,
duration_code,
schedule_seq_num,
NVL (c.is_critical, 'N') is_critical,
l.request_type_code,
DECODE (c.wrkr_conflict_flag,
' ', 'N',
c.wrkr_conflict_flag)
wrkr_conflict
FROM legal_case l,
schedule s,
CLM_DBA.temp_data_person_address c
WHERE l.legal_case_seq_num = s.legal_case_seq_num
AND l.claim_id = c.clm_id
AND c.run_id = in_run_id
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 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');
sameLoc_rec get_same_loc_date%ROWTYPE;
BEGIN
FOR sameLoc_rec IN get_same_loc_date
LOOP
v_emplid :=
same_time_loc_check (sameLoc_rec.schedule_date,
sameLoc_rec.duration_code,
sameLoc_rec.hearing_location_seq_num,
sameLoc_rec.alj_person_seq_num,
sameLoc_rec.is_critical,
sameLoc_rec.request_type_code,
sameLoc_rec.wrkr_conflict);
IF v_emplid <> 'NONE'
THEN
assign_rec.legal_case_seq_num := sameLoc_rec.legal_case_seq_num;
assign_rec.schedule_date := sameLoc_rec.schedule_date;
assign_rec.schedule_seq_num := sameLoc_rec.schedule_seq_num;
assign_rec.hearing_location_seq_num :=
sameLoc_rec.hearing_location_seq_num;
assign_rec.alj_person_seq_num := sameLoc_rec.alj_person_seq_num;
assign_rec.duration_code := sameLoc_rec.duration_code;
assign_rec.hearing_location_code := 'NONE';
assign_rec.adjuster_flag := adjuster_flag;
assign_rec.alj_flag := alj_flag;
assign_rec.rotation_flag := rotation_flag;
assign_rec.calendar_flag := calendar_flag;
assign_rec.employer_flag := employer_flag;
assign_rec.weekly_case_load := weekly_case_load;
assign_rec.location_flag := location_flag;
v_assigned :=
assignIt ('SAME',
v_emplid,
100,
assign_rec);
END IF;
END LOOP;
END assign_same;
PROCEDURE assign_wrkr_conflict_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,
in_run_id IN VARCHAR2)
IS
v_assigned VARCHAR2 (1);
v_index NUMBER;
v_cnt NUMBER;
v_first NUMBER;
attorney_data attorney_tab;
assigns_cursor assign_cursor;
assign_rec assigns_cursor%ROWTYPE;
PROCEDURE load_attorney_table (schedule_date IN DATE)
IS
CURSOR attorney_cursor
IS
SELECT profile_emplid,
primary_hear_loc_code,
NVL (secondary_hear_loc_code, 'NONE'),
NVL (tertiary_hear_loc_code, 'NONE'),
load_percent,
period_case_load (profile_emplid, schedule_date)
FROM attorney_profile
WHERE confidential_claims_flag = 'Y'
AND ( attorney_type_code = 'TRIAL'
OR attorney_type_code = 'FED')
AND load_percent > 0
ORDER BY 5, 2;
BEGIN
OPEN attorney_cursor;
v_index := 1;
LOOP
FETCH attorney_cursor
INTO attorney_data (v_index).emplid,
attorney_data (v_index).primary_loc,
attorney_data (v_index).secondary_loc,
attorney_data (v_index).tertiary_loc,
attorney_data (v_index).load_percent,
attorney_data (v_index).case_load;
EXIT WHEN attorney_cursor%NOTFOUND;
v_index := v_index + 1;
END LOOP;
v_cnt := attorney_data.COUNT;
v_index := 1;
v_first := 0;
CLOSE attorney_cursor;
END load_attorney_table;
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,
'None' AS primary_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,
clm_dba.temp_data_person_address clm
WHERE l.legal_case_seq_num = s.legal_case_seq_num
AND l.request_type_code = 'WCB'
AND l.claim_id = clm.clm_id
AND clm.run_id = in_run_id
AND NVL (clm.wrkr_conflict_flag, 'N') = 'Y'
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 NOT EXISTS
(SELECT 1
FROM request_issue r, request_issue_code c
WHERE r.legal_case_seq_num =
l.legal_case_seq_num
AND r.request_issue_code = c.code
AND c.recon_issue_flag = 'Y');
LOOP
FETCH assigns_cursor INTO assign_rec;
EXIT WHEN assigns_cursor%NOTFOUND;
v_assigned := 'N';
load_attorney_table (assign_rec.schedule_date);
FOR v_index IN 1 .. v_cnt
LOOP
IF attorney_data (v_index).primary_loc =
assign_rec.hearing_location_code
THEN
v_assigned :=
assignIt ('WRKC',
attorney_data (v_index).emplid,
attorney_data (v_index).load_percent,
assign_rec);
IF v_first = 0
THEN
v_first := v_index;
END IF;
IF v_assigned = 'Y'
THEN
EXIT;
END IF;
END IF;
END LOOP;
IF v_assigned = 'N'
THEN
FOR v_index IN 1 .. v_cnt
LOOP
IF attorney_data (v_index).secondary_loc =
assign_rec.hearing_location_code
THEN
v_assigned :=
assignIt ('WRKC',
attorney_data (v_index).emplid,
attorney_data (v_index).load_percent,
assign_rec);
IF v_first = 0
THEN
v_first := v_index;
END IF;
IF v_assigned = 'Y'
THEN
EXIT;
END IF;
END IF;
END LOOP;
END IF;
IF v_assigned = 'N'
THEN
FOR v_index IN 1 .. v_cnt
LOOP
IF attorney_data (v_index).tertiary_loc =
assign_rec.hearing_location_code
THEN
v_assigned :=
assignIt ('WRKC',
attorney_data (v_index).emplid,
attorney_data (v_index).load_percent,
assign_rec);
IF v_first = 0
THEN
v_first := v_index;
END IF;
IF v_assigned = 'Y'
THEN
EXIT;
END IF;
END IF;
END LOOP;
END IF;
IF v_assigned = 'N'
THEN
assign_rec.weekly_case_load := 0;
assign_rec.location_flag := 'N';
assign_rec.calendar_flag := 'N';
IF v_first > 0
THEN
v_assigned :=
assignIt ('WRKC',
attorney_data (v_first).emplid,
attorney_data (v_first).load_percent,
assign_rec);
END IF;
IF v_assigned = 'N'
THEN
FOR v_index IN 1 .. v_cnt
LOOP
v_assigned :=
assignIt ('WRKC',
attorney_data (v_index).emplid,
attorney_data (v_index).load_percent,
assign_rec);
IF v_assigned = 'Y'
THEN
EXIT;
END IF;
END LOOP;
END IF;
END IF;
END LOOP;
CLOSE assigns_cursor;
END assign_wrkr_conflict_cases;
PROCEDURE previously_assigned (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
v_assigned VARCHAR2 (1);
v_last_case NUMBER := 0;
assigns_cursor assign_cursor;
assign_rec assigns_cursor%ROWTYPE;
no_longer_active EXCEPTION;
PRAGMA EXCEPTION_INIT (no_longer_active, -20001);
BEGIN
RAISE no_longer_active;
END previously_assigned;
FUNCTION location_conflict_check (in_emplid IN VARCHAR2,
in_date IN DATE,
in_duration_code IN VARCHAR2,
in_hear_loc IN NUMBER)
RETURN NUMBER
AS
v_conflict_count NUMBER;
BEGIN
BEGIN
SELECT COUNT (*)
INTO v_conflict_count
FROM schedule s
WHERE schedule_status_code IN ('SET', 'RESET')
AND asgn_attorney_emplid = in_Emplid
AND in_date = schedule_date
AND NVL (hearing_location_seq_num, 0) <> in_hear_Loc;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
v_conflict_count := 0;
END;
RETURN v_conflict_count;
END location_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;
FUNCTION vacation_conflict_check (in_emplid IN VARCHAR2,
in_date IN DATE,
in_duration_code IN VARCHAR2,
in_code IN VARCHAR2)
RETURN NUMBER
AS
v_vacation_count NUMBER;
v_out_count NUMBER;
BEGIN
IF in_code = 'Y'
THEN
BEGIN
-- leave more than 4 days don't assign 3 before and 1 after
SELECT COUNT (*)
INTO v_vacation_count
FROM attorney_schedule
WHERE schedule_emplid = in_emplid
AND ( ( attorney_schedule_type_code = 'LEAVE'
AND exp_date >= eff_date + 3
AND TRUNC (SYSDATE) BETWEEN ( NEXT_DAY (
eff_date - 1,
'Friday')
- 4)
+ DECODE (
eff_date
- ( NEXT_DAY (
eff_date
- 1,
'Friday')
- 4),
0, -4,
0)
+ DECODE (
eff_date
- ( NEXT_DAY (
eff_date
- 1,
'Friday')
- 4),
1, -4,
0)
+ DECODE (
eff_date
- ( NEXT_DAY (
eff_date
- 1,
'Friday')
- 4),
2, -4,
0)
+ DECODE (
eff_date
- ( NEXT_DAY (
eff_date
- 1,
'Friday')
- 4),
3, 0,
0)
+ DECODE (
eff_date
- ( NEXT_DAY (
eff_date
- 1,
'Friday')
- 4),
4, +1,
0)
AND exp_date
+ 1
+ DECODE (
NEXT_DAY (
exp_date - 1,
'Friday')
- exp_date,
0, 2,
0)));
EXCEPTION
WHEN NO_DATA_FOUND
THEN
v_vacation_count := 0;
END;
END IF;
IF v_vacation_count IS NULL
THEN
v_vacation_count := 0;
END IF;
IF in_duration_code = 'AM' OR in_duration_code = 'PM'
THEN
BEGIN
SELECT COUNT (*)
INTO v_out_count
FROM attorney_schedule s, duration_code d
WHERE s.schedule_emplid = in_emplid
AND in_date BETWEEN s.eff_date AND s.exp_date
AND (in_duration_code = s.duration_code OR d.days_num > 0)
AND s.duration_code = d.code;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
v_out_count := 0;
END;
ELSE
BEGIN
SELECT COUNT (*)
INTO v_out_count
FROM attorney_schedule s, duration_code d
WHERE s.schedule_emplid = in_emplid
AND in_date BETWEEN s.eff_date AND s.exp_date
-- AND (in_duration_code = s.duration_code OR d.days_num > 0)
AND s.duration_code = d.code;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
v_out_count := 0;
END;
END IF;
IF v_out_count IS NULL
THEN
v_out_count := 0;
END IF;
RETURN v_vacation_count + v_out_count;
END vacation_conflict_check;
FUNCTION case_load (in_emplid IN VARCHAR2)
RETURN NUMBER
AS
v_case_load NUMBER;
BEGIN
BEGIN
SELECT ROUND (SUM (case_weight_value * (100 / load_percent)), 0)
INTO v_case_load
FROM legal_case l, schedule s, attorney_profile a
WHERE l.legal_case_seq_num = s.legal_case_seq_num
AND case_status_code <> 'CLOSE'
AND s.schedule_seq_num =
(SELECT MAX (schedule_seq_num)
FROM schedule
WHERE legal_case_seq_num = l.legal_case_seq_num
AND ( schedule_type_code = 'HEAR'
OR schedule_type_code = 'MED'))
AND s.asgn_attorney_emplid = profile_emplid
AND asgn_attorney_emplid = in_emplid;
EXCEPTION
WHEN ZERO_DIVIDE
THEN
v_case_load := 0;
WHEN NO_DATA_FOUND
THEN
v_case_load := 0;
END;
IF v_case_load IS NULL
THEN
v_case_load := 0;
END IF;
RETURN v_case_load;
END case_load;
FUNCTION period_case_load (in_emplid IN VARCHAR2, hearingDate IN DATE)
RETURN NUMBER
AS
v_case_load NUMBER;
v_vac_days NUMBER;
v_total NUMBER;
v_rot_default NUMBER;
v_eff DATE;
v_rot_count NUMBER;
BEGIN
BEGIN
SELECT ROUND (SUM (case_weight_value * (100 / load_percent)), 0)
INTO v_case_load
FROM legal_case l, schedule s, attorney_profile a
WHERE l.legal_case_seq_num = s.legal_case_seq_num
AND case_status_code <> 'CLOSE'
AND ( schedule_type_code = 'HEAR'
OR schedule_type_code = 'MED')
AND s.asgn_attorney_emplid = profile_emplid
AND asgn_attorney_emplid = in_emplid
AND schedule_date BETWEEN ( NEXT_DAY (hearingDate - 1,
'Friday')
- 12)
AND ( NEXT_DAY (hearingDate - 1,
'Friday')
+ 7);
EXCEPTION
WHEN ZERO_DIVIDE
THEN
v_case_load := 0;
WHEN NO_DATA_FOUND
THEN
v_case_load := 0;
END;
IF v_case_load IS NULL
THEN
v_case_load := 0;
END IF;
BEGIN
v_rot_default := 0;
SELECT MIN (Rotation_average_case_count), MIN (r.eff_date)
INTO v_rot_default, v_eff
FROM hearing_location h, out_of_town_rotation_assgn r
WHERE h.hearing_location_code = r.hearing_location_code
AND r.attorney_emplid = in_emplid
AND r.eff_date BETWEEN ( NEXT_DAY (hearingDate - 1,
'Friday')
- 12)
AND ( NEXT_DAY (hearingDate - 1,
'Friday')
+ 7);
EXCEPTION
WHEN NO_DATA_FOUND
THEN
v_rot_default := 0;
END;
IF v_rot_default > 0
THEN
v_rot_count := get_weekly_case_load (in_emplid, v_eff);
IF v_rot_count < v_rot_default
THEN
v_case_load := v_case_load - v_rot_count + v_rot_default;
END IF;
END IF;
v_vac_days :=
get_vacation_days (in_emplid,
hearingDate,
12,
7);
v_total := v_case_load + v_vac_days;
RETURN v_total;
END period_case_load;
FUNCTION period_case_count (in_emplid IN VARCHAR2, hearingDate IN DATE)
RETURN NUMBER
AS
v_total NUMBER;
BEGIN
BEGIN
SELECT COUNT (*)
INTO v_total
FROM legal_case l, schedule s, attorney_profile a
WHERE l.legal_case_seq_num = s.legal_case_seq_num
AND case_status_code <> 'CLOSE'
AND ( schedule_type_code = 'HEAR'
OR schedule_type_code = 'MED')
AND s.asgn_attorney_emplid = profile_emplid
AND asgn_attorney_emplid = in_emplid
AND schedule_date BETWEEN ( NEXT_DAY (hearingDate - 1,
'Friday')
- 12)
AND ( NEXT_DAY (hearingDate - 1,
'Friday')
+ 7);
END;
RETURN v_total;
END period_case_count;
FUNCTION get_weekly_case_load (in_emplid IN VARCHAR2, hearingDate IN DATE)
RETURN NUMBER
AS
v_case_load NUMBER;
BEGIN
BEGIN
SELECT ROUND (SUM (case_weight_value * (100 / load_percent)), 0)
INTO v_case_load
FROM legal_case l, schedule s, attorney_profile a
WHERE l.legal_case_seq_num = s.legal_case_seq_num
AND case_status_code <> 'CLOSE'
AND ( schedule_type_code = 'HEAR'
OR schedule_type_code = 'MED')
AND s.asgn_attorney_emplid = profile_emplid
AND asgn_attorney_emplid = in_emplid
AND schedule_date BETWEEN ( NEXT_DAY (hearingDate - 1,
'Friday')
- 5)
AND NEXT_DAY (hearingDate - 1,
'Friday');
EXCEPTION
WHEN ZERO_DIVIDE
THEN
v_case_load := 0;
WHEN NO_DATA_FOUND
THEN
v_case_load := 0;
END;
IF v_case_load IS NULL
THEN
v_case_load := 0;
END IF;
RETURN v_case_load;
END get_weekly_case_load;
FUNCTION get_hearing_days (in_emplid IN VARCHAR2, hearingDate IN DATE)
RETURN NUMBER
AS
v_case_load NUMBER;
v_total NUMBER;
BEGIN
BEGIN
SELECT COUNT (DISTINCT (schedule_date))
INTO v_case_load
FROM legal_case l, schedule s, attorney_profile a
WHERE l.legal_case_seq_num = s.legal_case_seq_num
AND case_status_code <> 'CLOSE'
AND ( schedule_type_code = 'HEAR'
OR schedule_type_code = 'MED')
AND s.asgn_attorney_emplid = profile_emplid
AND asgn_attorney_emplid = in_emplid
AND schedule_date BETWEEN ( NEXT_DAY (hearingDate - 1,
'Friday')
- 5)
AND NEXT_DAY (hearingDate - 1,
'Friday');
-- AND schedule_date <> hearingDate; changed 10/04/2011
EXCEPTION
WHEN ZERO_DIVIDE
THEN
v_case_load := 0;
WHEN NO_DATA_FOUND
THEN
v_case_load := 0;
END;
IF v_case_load IS NULL
THEN
v_case_load := 0;
END IF;
v_total :=
v_case_load
+ get_vacation_days (in_emplid,
hearingDate,
5,
0);
RETURN v_total;
END get_hearing_days;
FUNCTION get_vacation_days (in_emplid IN VARCHAR2,
hearingDate IN DATE,
in_before IN NUMBER,
in_after IN NUMBER)
RETURN NUMBER
AS
v_vac_days1 NUMBER;
v_vac_days2 NUMBER;
v_vac_days3 NUMBER;
v_vac_days4 NUMBER;
v_total NUMBER;
BEGIN
BEGIN
SELECT SUM (Sp72219_Day_Count (eff_date, exp_date) + 1)
INTO v_vac_days1
FROM attorney_schedule a
WHERE schedule_emplid = in_emplid
AND ( attorney_schedule_type_code = 'VAC'
OR attorney_schedule_type_code = 'LEAVE')
AND eff_date BETWEEN ( NEXT_DAY (hearingDate - 1, 'Friday')
- in_before)
AND NEXT_DAY (hearingDate - 1, 'Friday')
+ in_after
AND exp_date BETWEEN ( NEXT_DAY (hearingDate - 1, 'Friday')
- in_before)
AND NEXT_DAY (hearingDate - 1, 'Friday')
+ in_after;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
v_vac_days1 := 0;
END;
BEGIN
SELECT SUM (
Sp72219_Day_Count (
(NEXT_DAY (hearingDate - 1, 'Friday') - in_before),
exp_date)
+ 1)
INTO v_vac_days2
FROM attorney_schedule a
WHERE schedule_emplid = in_emplid
AND ( attorney_schedule_type_code = 'VAC'
OR attorney_schedule_type_code = 'LEAVE')
AND eff_date <
(NEXT_DAY (hearingDate - 1, 'Friday') - in_before)
AND exp_date BETWEEN ( NEXT_DAY (hearingDate - 1, 'Friday')
- in_before)
AND NEXT_DAY (hearingDate - 1, 'Friday')
+ in_after;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
v_vac_days2 := 0;
END;
BEGIN
SELECT SUM (
Sp72219_Day_Count (
eff_date,
NEXT_DAY (hearingDate - 1, 'Friday') + in_after)
+ 1)
INTO v_vac_days3
FROM attorney_schedule a
WHERE schedule_emplid = in_emplid
AND ( attorney_schedule_type_code = 'VAC'
OR attorney_schedule_type_code = 'LEAVE')
AND eff_date BETWEEN ( NEXT_DAY (hearingDate - 1, 'Friday')
- In_before)
AND NEXT_DAY (hearingDate - 1, 'Friday')
+ in_after
AND exp_date >
NEXT_DAY (hearingDate - 1, 'Friday') + in_after;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
v_vac_days3 := 0;
END;
BEGIN
SELECT SUM (
Sp72219_Day_Count (
(NEXT_DAY (hearingDate - 1, 'Friday') - in_before),
NEXT_DAY (hearingDate - 1, 'Friday') + in_after)
+ 1)
INTO v_vac_days4
FROM attorney_schedule a
WHERE schedule_emplid = in_emplid
AND ( attorney_schedule_type_code = 'VAC'
OR attorney_schedule_type_code = 'LEAVE')
AND eff_date <
(NEXT_DAY (hearingDate - 1, 'Friday') - in_before)
AND exp_date >
NEXT_DAY (hearingDate - 1, 'Friday') + in_after;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
v_vac_days4 := 0;
END;
IF v_vac_days1 IS NULL
THEN
v_vac_days1 := 0;
END IF;
IF v_vac_days2 IS NULL
THEN
v_vac_days2 := 0;
END IF;
IF v_vac_days3 IS NULL
THEN
v_vac_days3 := 0;
END IF;
IF v_vac_days4 IS NULL
THEN
v_vac_days4 := 0;
END IF;
v_total := v_vac_days1 + v_vac_days2 + v_vac_days3 + v_vac_days4;
RETURN v_total;
END get_vacation_days;
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;
IF load_result < hearing_days
THEN
load_result := 0;
END IF;
END IF;
IF in_assign_rec.employer_flag = 'Y'
THEN
employer_result :=
employer_conflict_check (in_assign_rec.legal_case_Seq_num,
in_emplid);
END IF;
IF in_assign_rec.adjuster_flag = 'Y'
THEN
adjuster_result :=
adjuster_conflict_check (in_assign_rec.legal_case_Seq_num,
in_emplid);
END IF;
IF in_assign_rec.alj_flag = 'Y'
THEN
alj_result :=
alj_conflict_check (in_assign_rec.alj_person_seq_num,
in_emplid);
END IF;
attorney_result :=
attorney_conflict_check (in_assign_rec.legal_case_Seq_num,
in_emplid);
IF in_assign_rec.rotation_flag = 'Y'
THEN
rotation_result :=
rotation_conflict_check (in_emplid,
in_assign_rec.schedule_Date,
in_assign_rec.hearing_location_code);
END IF;
IF in_assign_rec.calendar_flag = 'Y'
OR in_assign_rec.calendar_flag = 'H'
THEN
calendar_result :=
calendar_conflict_check (
in_emplid,
in_assign_rec.schedule_Date,
in_assign_rec.duration_code,
in_assign_rec.hearing_location_seq_num,
in_assign_rec.alj_person_seq_num);
vacation_result :=
vacation_conflict_check (in_emplid,
in_assign_rec.schedule_Date,
in_assign_rec.duration_code,
in_assign_rec.calendar_flag);
END IF;
IF in_assign_rec.calendar_flag = 'C'
THEN
calendar_result :=
calendar_conflict_check (
in_emplid,
in_assign_rec.schedule_Date,
in_assign_rec.duration_code,
in_assign_rec.hearing_location_seq_num,
in_assign_rec.alj_person_seq_num);
END IF;
IF in_assign_rec.location_flag = 'Y'
THEN
location_result :=
location_conflict_check (
in_emplid,
in_assign_rec.schedule_Date,
in_assign_rec.duration_code,
in_assign_rec.hearing_location_seq_num);
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;
IF alj_result > 0
THEN
insert_results (in_assign_rec.legal_case_Seq_num,
in_code,
in_assign_rec.schedule_seq_num,
'NAALJ',
IN_emplid);
END IF;
IF attorney_result > 0
THEN
insert_results (in_assign_rec.legal_case_Seq_num,
in_code,
in_assign_rec.schedule_seq_num,
'NAATT',
IN_emplid);
END IF;
IF rotation_result > 0
THEN
insert_results (in_assign_rec.legal_case_Seq_num,
in_code,
in_assign_rec.schedule_seq_num,
'NAROT',
IN_emplid);
END IF;
IF calendar_result > 0
THEN
insert_results (in_assign_rec.legal_case_Seq_num,
in_code,
in_assign_rec.schedule_seq_num,
'NAC',
IN_emplid);
END IF;
IF vacation_result > 0
THEN
insert_results (in_assign_rec.legal_case_Seq_num,
in_code,
in_assign_rec.schedule_seq_num,
'NAVAC',
IN_emplid);
END IF;
IF load_result > 0
THEN
insert_results (in_assign_rec.legal_case_Seq_num,
in_code,
in_assign_rec.schedule_seq_num,
'LOAD',
IN_emplid);
END IF;
IF location_result > 0
THEN
insert_results (in_assign_rec.legal_case_Seq_num,
in_code,
in_assign_rec.schedule_seq_num,
'NALCC',
IN_emplid);
END IF;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
v_assign := 'N';
END;
RETURN v_assign;
END assignIt;
FUNCTION assignItBalanced (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.employer_flag = 'Y'
THEN
employer_result :=
employer_conflict_check (in_assign_rec.legal_case_Seq_num,
in_emplid);
END IF;
IF in_assign_rec.adjuster_flag = 'Y'
THEN
adjuster_result :=
adjuster_conflict_check (in_assign_rec.legal_case_Seq_num,
in_emplid);
END IF;
IF in_assign_rec.alj_flag = 'Y'
THEN
alj_result :=
alj_conflict_check (in_assign_rec.alj_person_seq_num,
in_emplid);
END IF;
attorney_result :=
attorney_conflict_check (in_assign_rec.legal_case_Seq_num,
in_emplid);
IF in_assign_rec.rotation_flag = 'Y'
THEN
rotation_result :=
rotation_conflict_check (in_emplid,
in_assign_rec.schedule_Date,
in_assign_rec.hearing_location_code);
END IF;
IF in_assign_rec.calendar_flag = 'Y'
OR in_assign_rec.calendar_flag = 'H'
THEN
calendar_result :=
calendar_conflict_check (
in_emplid,
in_assign_rec.schedule_Date,
in_assign_rec.duration_code,
in_assign_rec.hearing_location_seq_num,
in_assign_rec.alj_person_seq_num);
vacation_result :=
vacation_conflict_check (in_emplid,
in_assign_rec.schedule_Date,
in_assign_rec.duration_code,
in_assign_rec.calendar_flag);
END IF;
IF in_assign_rec.calendar_flag = 'C'
THEN
calendar_result :=
calendar_conflict_check (
in_emplid,
in_assign_rec.schedule_Date,
in_assign_rec.duration_code,
in_assign_rec.hearing_location_seq_num,
in_assign_rec.alj_person_seq_num);
END IF;
IF in_assign_rec.location_flag = 'Y'
THEN
location_result :=
location_conflict_check (
in_emplid,
in_assign_rec.schedule_Date,
in_assign_rec.duration_code,
in_assign_rec.hearing_location_seq_num);
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
DBMS_OUTPUT.PUT_LINE (
' result emplid / resultCode: '
|| in_emplid
|| ' / '
|| 'NAHOL');
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
DBMS_OUTPUT.PUT_LINE (
' result emplid / resultCode: '
|| in_emplid
|| ' / '
|| 'NAEMP');
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
DBMS_OUTPUT.PUT_LINE (
' result emplid / resultCode: '
|| in_emplid
|| ' / '
|| 'NAADJ');
insert_results (in_assign_rec.legal_case_Seq_num,
in_code,
in_assign_rec.schedule_seq_num,
'NAADJ',
IN_emplid);
END IF;
IF alj_result > 0
THEN
DBMS_OUTPUT.PUT_LINE (
' result emplid / resultCode: '
|| in_emplid
|| ' / '
|| 'NAALJ');
insert_results (in_assign_rec.legal_case_Seq_num,
in_code,
in_assign_rec.schedule_seq_num,
'NAALJ',
IN_emplid);
END IF;
IF attorney_result > 0
THEN
DBMS_OUTPUT.PUT_LINE (
' result emplid / resultCode: '
|| in_emplid
|| ' / '
|| 'NAATT');
insert_results (in_assign_rec.legal_case_Seq_num,
in_code,
in_assign_rec.schedule_seq_num,
'NAATT',
IN_emplid);
END IF;
IF rotation_result > 0
THEN
DBMS_OUTPUT.PUT_LINE (
' result emplid / resultCode: '
|| in_emplid
|| ' / '
|| 'NAROT');
insert_results (in_assign_rec.legal_case_Seq_num,
in_code,
in_assign_rec.schedule_seq_num,
'NAROT',
IN_emplid);
END IF;
IF calendar_result > 0
THEN
DBMS_OUTPUT.PUT_LINE (
' result emplid / resultCode: '
|| in_emplid
|| ' / '
|| 'NAC');
insert_results (in_assign_rec.legal_case_Seq_num,
in_code,
in_assign_rec.schedule_seq_num,
'NAC',
IN_emplid);
END IF;
IF vacation_result > 0
THEN
DBMS_OUTPUT.PUT_LINE (
' result emplid / resultCode: '
|| in_emplid
|| ' / '
|| 'NAVAC');
insert_results (in_assign_rec.legal_case_Seq_num,
in_code,
in_assign_rec.schedule_seq_num,
'NAVAC',
IN_emplid);
END IF;
IF load_result > 0
THEN
DBMS_OUTPUT.PUT_LINE (
' result emplid / resultCode: '
|| in_emplid
|| ' / '
|| 'LOAD');
insert_results (in_assign_rec.legal_case_Seq_num,
in_code,
in_assign_rec.schedule_seq_num,
'LOAD',
IN_emplid);
END IF;
IF location_result > 0
THEN
DBMS_OUTPUT.PUT_LINE (
' result emplid / resultCode: '
|| in_emplid
|| ' / '
|| 'NALCC');
insert_results (in_assign_rec.legal_case_Seq_num,
in_code,
in_assign_rec.schedule_seq_num,
'NALCC',
IN_emplid);
END IF;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
v_assign := 'N';
END;
RETURN v_assign;
END assignItBalanced;
PROCEDURE get_results (in_num IN NUMBER,
in_type IN VARCHAR2,
in_date IN DATE,
recordset OUT assign_results_cursor)
AS
BEGIN
IF in_type = 'ASSIGN'
THEN
OPEN recordset FOR
SELECT /*+ FIRST_ROWS */
r.assignment_result_seq_num,
r.LEGAL_CASE_seq_num,
r.schedule_seq_num,
Pk80001_Legalnet.get_case_num (r.legal_case_seq_num)
AS case_num,
rul.code_descr AS rule_descr,
res.code_descr AS result_descr,
r.assignment_attorney_emplid,
NVL (
a.LAST_NAME
|| ', '
|| DECODE (a.preferred_name,
' ', a.FIRST_NAME,
a.preferred_name),
'Not Assigned')
AS asgn_attorney_name
FROM adp_employee a,
assignment_result r,
assignment_rule_code rul,
assignment_result_code res
WHERE a.emplid(+) = r.assignment_attorney_emplid
AND r.assignment_rule_code = rul.code
AND r.assignment_result_code = res.code
AND r.assignment_result_seq_num > in_num
ORDER BY r.legal_case_seq_num, r.assignment_result_seq_num;
END IF;
IF in_type = 'DATE'
THEN
OPEN recordset FOR
SELECT /*+ FIRST_ROWS */
r.assignment_result_seq_num,
r.LEGAL_CASE_seq_num,
r.schedule_seq_num,
Pk80001_Legalnet.get_case_num (r.legal_case_seq_num)
AS case_num,
rul.code_descr AS rule_descr,
res.code_descr AS result_descr,
r.assignment_attorney_emplid,
NVL (
a.LAST_NAME
|| ', '
|| DECODE (a.preferred_name,
' ', a.FIRST_NAME,
a.preferred_name),
'Not Assigned')
AS asgn_attorney_name
FROM adp_employee a,
assignment_result r,
assignment_rule_code rul,
assignment_result_code res
WHERE a.emplid(+) = r.assignment_attorney_emplid
AND r.assignment_rule_code = rul.code
AND r.assignment_result_code = res.code
AND TRUNC (r.create_date) = in_date
ORDER BY r.legal_case_seq_num, r.assignment_result_seq_num;
END IF;
IF in_type = 'CASE'
THEN
OPEN recordset FOR
SELECT /*+ FIRST_ROWS */
r.assignment_result_seq_num,
r.LEGAL_CASE_seq_num,
r.schedule_seq_num,
Pk80001_Legalnet.get_case_num (r.legal_case_seq_num)
AS case_num,
rul.code_descr AS rule_descr,
res.code_descr AS result_descr,
r.assignment_attorney_emplid,
NVL (
a.LAST_NAME
|| ', '
|| DECODE (a.preferred_name,
' ', a.FIRST_NAME,
a.preferred_name),
'Not Assigned')
AS asgn_attorney_name
FROM adp_employee a,
assignment_result r,
assignment_rule_code rul,
assignment_result_code res
WHERE a.emplid(+) = r.assignment_attorney_emplid
AND r.assignment_rule_code = rul.code
AND r.assignment_result_code = res.code
AND r.legal_case_seq_num = in_num
ORDER BY r.legal_case_seq_num, r.assignment_result_seq_num;
END IF;
END get_results;
PROCEDURE get_assign_results (recordset OUT assign_header_cursor, in_num IN NUMBER,
in_type IN VARCHAR2,
in_date IN DATE)
AS
BEGIN
IF in_type = 'ASSIGN'
THEN
OPEN recordset FOR
SELECT /*+ FIRST_ROWS */
r.assignment_result_seq_num,
r.LEGAL_CASE_seq_num,
r.schedule_seq_num,
Pk80001_Legalnet.get_case_num (r.legal_case_seq_num)
AS case_num,
s.asgn_attorney_emplid,
a.LAST_NAME
|| ', '
|| DECODE (a.preferred_name,
' ', a.FIRST_NAME,
a.preferred_name)
AS asgn_attorney_name,
s.schedule_date AS hearing_date,
r.create_date,
l.code_descr AS hearing_location,
r.assignment_result_code AS result_code,
a.email_address,
TRIM (p.first_name || ' ' || p.last_name) AS alj_name,
s.schedule_time,
cas.claim_id
FROM adp_employee a,
assignment_result r,
legal_case cas,
schedule s,
hearing_location h,
hearing_location_code l,
legal_person p
WHERE r.schedule_seq_num = s.schedule_seq_num
AND a.emplid(+) = r.assignment_attorney_emplid
AND r.assignment_result_seq_num > in_num
AND r.assignment_result_Seq_num =
(SELECT MAX (assignment_result_seq_num)
FROM assignment_result
WHERE legal_case_seq_num = r.legal_case_seq_num)
AND s.hearing_location_seq_num =
h.hearing_location_seq_num
AND h.hearing_location_code = l.code
AND r.legal_case_seq_num = cas.legal_case_seq_num
AND s.alj_person_seq_num = p.legal_person_seq_num(+)
AND r.assignment_result_code IN ('CA', 'CAC')
UNION
SELECT /*+ FIRST_ROWS */
r.assignment_result_seq_num,
r.LEGAL_CASE_seq_num,
r.schedule_seq_num,
Pk80001_Legalnet.get_case_num (r.legal_case_seq_num)
AS case_num,
s.asgn_attorney_emplid,
'Not Assigned' AS asgn_attorney_name,
s.schedule_date AS hearing_date,
r.create_date,
l.code_descr AS hearing_location,
r.assignment_result_code AS result_code,
'NONE' AS email_address,
TRIM (p.first_name || ' ' || p.last_name) AS alj_name,
s.schedule_time,
cas.claim_id
FROM assignment_result r,
legal_case cas,
schedule s,
hearing_location h,
hearing_location_code l,
legal_person p
WHERE r.schedule_seq_num = s.schedule_seq_num
AND r.assignment_result_seq_num > in_num
AND r.assignment_result_Seq_num =
(SELECT MAX (assignment_result_seq_num)
FROM assignment_result
WHERE legal_case_seq_num = r.legal_case_seq_num)
AND s.hearing_location_seq_num =
h.hearing_location_seq_num
AND h.hearing_location_code = l.code
AND r.legal_case_seq_num = cas.legal_case_seq_num
AND s.alj_person_seq_num = p.legal_person_seq_num(+)
AND r.assignment_result_code NOT IN ('CA', 'CAC')
ORDER BY 3, 5;
END IF;
IF in_type = 'DATE'
THEN
OPEN recordset FOR
SELECT /*+ FIRST_ROWS */
r.assignment_result_seq_num,
r.LEGAL_CASE_seq_num,
r.schedule_seq_num,
Pk80001_Legalnet.get_case_num (r.legal_case_seq_num)
AS case_num,
s.asgn_attorney_emplid,
a.LAST_NAME
|| ', '
|| DECODE (a.preferred_name,
' ', a.FIRST_NAME,
a.preferred_name)
AS asgn_attorney_name,
s.schedule_date AS hearing_date,
r.create_date,
l.code_descr AS hearing_location,
r.assignment_result_code AS result_code,
--'NOTUSED' AS adjusterMailId,
a.email_address,
TRIM (p.first_name || ' ' || p.last_name) AS alj_name,
schedule_time,
cas.claim_id
FROM adp_employee a,
assignment_result r,
schedule s,
hearing_location h,
hearing_location_code l,
legal_case cas,
legal_person p
WHERE r.schedule_seq_num = s.schedule_seq_num
AND a.emplid(+) = r.assignment_attorney_emplid
AND TRUNC (r.create_date) = in_date
AND r.assignment_result_Seq_num =
(SELECT MAX (assignment_result_seq_num)
FROM assignment_result
WHERE legal_case_seq_num =
r.legal_case_seq_num
AND TRUNC (create_date) = in_date)
AND s.hearing_location_seq_num =
h.hearing_location_seq_num
AND h.hearing_location_code = l.code
AND r.legal_case_seq_num = cas.legal_case_seq_num
AND s.alj_person_seq_num = p.legal_person_seq_num(+)
AND r.assignment_result_code IN ('CA', 'CAC')
UNION
SELECT /*+ FIRST_ROWS */
r.assignment_result_seq_num,
r.LEGAL_CASE_seq_num,
r.schedule_seq_num,
Pk80001_Legalnet.get_case_num (r.legal_case_seq_num)
AS case_num,
s.asgn_attorney_emplid,
'Not Assigned' AS asgn_attorney_name,
s.schedule_date AS hearing_date,
r.create_date,
l.code_descr AS hearing_location,
r.assignment_result_code AS result_code,
--'NOTUSED',
'NONE' AS email_address,
TRIM (p.first_name || ' ' || p.last_name) AS alj_name,
schedule_time,
cas.claim_id
FROM assignment_result r,
schedule s,
hearing_location h,
hearing_location_code l,
legal_case cas,
legal_person p
WHERE r.schedule_seq_num = s.schedule_seq_num
AND TRUNC (r.create_date) = in_date
AND r.assignment_result_Seq_num =
(SELECT MAX (assignment_result_seq_num)
FROM assignment_result
WHERE legal_case_seq_num =
r.legal_case_seq_num
AND TRUNC (create_date) = in_date)
AND s.hearing_location_seq_num =
h.hearing_location_seq_num
AND h.hearing_location_code = l.code
AND r.legal_case_seq_num = cas.legal_case_seq_num
AND s.alj_person_seq_num = p.legal_person_seq_num(+)
AND r.assignment_result_code NOT IN ('CA', 'CAC')
ORDER BY 3, 5;
END IF;
IF in_type = 'CASE'
THEN
OPEN recordset FOR
SELECT /*+ FIRST_ROWS */
r.assignment_result_seq_num,
r.LEGAL_CASE_seq_num,
r.schedule_seq_num,
Pk80001_Legalnet.get_case_num (r.legal_case_seq_num)
AS case_num,
s.asgn_attorney_emplid,
a.LAST_NAME
|| ', '
|| DECODE (a.preferred_name,
' ', a.FIRST_NAME,
a.preferred_name)
AS asgn_attorney_name,
s.schedule_date AS hearing_date,
r.create_date,
l.code_descr AS hearing_location,
r.assignment_result_code AS result_code,
--'NOTUSED',
a.email_address,
TRIM (p.first_name || ' ' || p.last_name) AS alj_name,
schedule_time,
cas.claim_id
FROM adp_employee a,
assignment_result r,
schedule s,
hearing_location h,
hearing_location_code l,
legal_case cas,
legal_person p
WHERE r.schedule_seq_num = s.schedule_seq_num
AND a.emplid(+) = r.assignment_attorney_emplid
AND r.legal_case_seq_num = in_num
AND r.assignment_result_Seq_num =
(SELECT MAX (assignment_result_seq_num)
FROM assignment_result
WHERE legal_case_seq_num = r.legal_case_seq_num)
AND s.hearing_location_seq_num =
h.hearing_location_seq_num
AND h.hearing_location_code = l.code
AND r.legal_case_seq_num = cas.legal_case_seq_num
AND s.alj_person_seq_num = p.legal_person_seq_num(+)
AND r.assignment_result_code IN ('CA', 'CAC')
UNION
SELECT /*+ FIRST_ROWS */
r.assignment_result_seq_num,
r.LEGAL_CASE_seq_num,
r.schedule_seq_num,
Pk80001_Legalnet.get_case_num (r.legal_case_seq_num)
AS case_num,
s.asgn_attorney_emplid,
'Not Assigned' AS asgn_attorney_name,
s.schedule_date AS hearing_date,
r.create_date,
l.code_descr AS hearing_location,
r.assignment_result_code AS result_code,
--'NOTUSED',
'NONE' AS email_address,
TRIM (p.first_name || ' ' || p.last_name) AS alj_name,
schedule_time,
cas.claim_id
FROM adp_employee a,
assignment_result r,
schedule s,
hearing_location h,
hearing_location_code l,
legal_case cas,
legal_person p
WHERE r.schedule_seq_num = s.schedule_seq_num
AND r.legal_case_seq_num = in_num
AND r.assignment_result_Seq_num =
(SELECT MAX (assignment_result_seq_num)
FROM assignment_result
WHERE legal_case_seq_num = r.legal_case_seq_num)
AND s.hearing_location_seq_num =
h.hearing_location_seq_num
AND h.hearing_location_code = l.code
AND r.legal_case_seq_num = cas.legal_case_seq_num
AND s.alj_person_seq_num = p.legal_person_seq_num(+)
AND r.assignment_result_code NOT IN ('CA', 'CAC')
ORDER BY 3, 5;
END IF;
END get_assign_results;
PROCEDURE get_assign_list (recordset OUT ref_cursor, in_date IN DATE)
AS
BEGIN
OPEN recordset FOR
SELECT /*+ FIRST_ROWS */
cas.legal_case_seq_num AS CaseId,
NVL (Pk80001_Legalnet.get_case_num (cas.legal_case_seq_num),
' ')
AS case_num,
cas.claim_id,
a.LAST_NAME || ' ' || oregon_state_bar_number
AS asgn_attorney_name,
a.LAST_NAME AS attorney_name,
period_case_count (atty.profile_emplid, s.schedule_date)
AS attorney_case_count,
period_case_load (atty.profile_emplid, s.schedule_date)
AS attorney_load,
TRIM (p.last_name) AS alj_name,
S.KEEP_ATTORNEY_FLAG,
CAS.CONSOLIDATE_CASE_FLAG,
s.schedule_date AS hearing_date,
s.schedule_time,
l.code_descr AS hearing_location,
R.ASSIGNMENT_RULE_CODE RuleCode,
s.ALJ_PERSON_SEQ_NUM
FROM adp_employee a,
schedule s,
hearing_location h,
hearing_location_code l,
legal_case cas,
legal_person p,
attorney_profile atty,
assignment_result r
WHERE a.emplid = s.asgn_attorney_emplid
AND s.asgn_attorney_emplid = atty.profile_emplid
AND TRUNC (s.assigned_date) = in_date
AND s.asgn_attorney_emplid IS NOT NULL
AND s.hearing_location_seq_num = h.hearing_location_seq_num
AND h.hearing_location_code = l.code
AND s.schedule_seq_num = cas.most_recent_hearing_seq_num
AND s.alj_person_seq_num = p.legal_person_seq_num(+)
AND ( s.schedule_status_code = 'SET'
OR s.schedule_status_code = 'RESET')
AND S.legal_case_seq_num = R.legal_case_seq_num(+)
AND s.schedule_type_code = 'HEAR'
AND NVL (R.ASSIGNMENT_RESULT_CODE, 'CA') IN ('CA', 'CAC')
AND S.ASSIGNED_DATE = R.CREATE_DATE(+)
UNION
SELECT /*+ FIRST_ROWS */
cas.legal_case_seq_num AS CaseId,
Pk80001_Legalnet.get_case_num (cas.legal_case_seq_num)
AS case_num,
cas.claim_id,
'Outside Counsel' AS asgn_attorney_name,
'Outside Counsel' AS attorney_name,
0 AS attorney_case_count,
0 AS attorney_load,
TRIM (p.last_name) AS alj_name,
S.KEEP_ATTORNEY_FLAG,
CAS.CONSOLIDATE_CASE_FLAG,
s.schedule_date AS hearing_date,
s.schedule_time,
l.code_descr AS hearing_location,
'OUTC' as RuleCode,
s.ALJ_PERSON_SEQ_NUM
FROM schedule s,
hearing_location h,
hearing_location_code l,
legal_case cas,
legal_person p
WHERE s.asgn_attorney_emplid IS NULL
AND s.asgn_other_firm_seq_num IS NOT NULL
AND TRUNC (s.assigned_date) = in_date
AND s.hearing_location_seq_num = h.hearing_location_seq_num
AND h.hearing_location_code = l.code
AND s.schedule_seq_num = cas.most_recent_hearing_seq_num
AND s.schedule_type_code = 'HEAR'
AND s.alj_person_seq_num = p.legal_person_seq_num(+)
AND ( s.schedule_status_code = 'SET'
OR s.schedule_status_code = 'RESET')
ORDER BY 4;
END get_assign_list;
PROCEDURE get_previously_assigned (in_num IN NUMBER,
recordset OUT ref_cursor)
AS
BEGIN
OPEN recordset FOR
SELECT /*+ FIRST_ROWS */
l.legal_case_seq_num,
s2.schedule_seq_num,
s2.asgn_attorney_emplid,
adp.email_address,
d.days_num,
r.assignment_result_seq_num
FROM legal_case l,
schedule s,
schedule s2,
assignment_result r,
adp_employee adp,
duration_code d
WHERE r.legal_case_seq_num = l.legal_case_seq_num
AND r.schedule_seq_num = s.schedule_seq_num
AND assignment_result_code IN ('CA', 'CAC')
AND s.duration_code = d.code
AND l.legal_case_seq_num = s2.legal_case_seq_num
AND s2.asgn_attorney_emplid IS NOT NULL
AND s2.asgn_attorney_emplid = adp.emplid
AND s2.schedule_seq_num =
(SELECT MAX (schedule_seq_num)
FROM schedule s3
WHERE s3.legal_case_seq_num =
r.legal_case_seq_num
AND s3.schedule_seq_num < r.schedule_seq_num)
AND r.assignment_result_seq_num > in_num;
END get_previously_assigned;
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;
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;
FUNCTION rotation_conflict_check (in_emplid IN VARCHAR2,
in_date IN DATE,
in_hear_code IN VARCHAR2)
RETURN NUMBER
AS
v_conflict_count NUMBER;
BEGIN
BEGIN
--DBMS_OUTPUT.PUT_LINE('rotation ' || in_hear_loc);
--dbms_output.put_line('date ' || to_char(in_date, 'mm/dd/yyyy'));
--dbms_output.put_line('emplid ' || in_emplid);
SELECT COUNT (*)
INTO v_conflict_count
FROM out_of_town_rotation_assgn
WHERE attorney_emplid = in_Emplid
AND TO_DATE (in_Date) BETWEEN eff_date AND exp_date
AND hearing_location_code <> in_hear_code;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
v_conflict_count := 0;
END;
RETURN v_conflict_count;
END rotation_conflict_check;
FUNCTION same_time_loc_check (in_date IN DATE,
in_duration_code IN VARCHAR2,
in_hear_loc IN NUMBER,
in_alj IN NUMBER,
in_is_critical IN VARCHAR2,
in_request_type IN VARCHAR2,
in_wrkr_conflict IN VARCHAR2)
RETURN VARCHAR2
AS
v_emplid VARCHAR2 (11) := 'NONE';
BEGIN
IF in_is_critical = 'Y'
THEN
SELECT MIN (asgn_attorney_emplid)
INTO v_emplid
FROM schedule s, duration_code d, attorney_profile p
WHERE schedule_status_code IN ('SET', 'RESET')
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 alj_person_seq_num = in_alj
AND hearing_location_seq_num = in_hear_Loc
AND s.duration_code = d.code
AND s.asgn_attorney_emplid = p.profile_emplid
AND ( p.attorney_type_code = 'TRIAL'
OR p.attorney_type_code = 'FED')
AND p.ccu_claims_flag = 'Y'
AND s.asgn_attorney_emplid IS NOT NULL;
ELSE
SELECT MIN (asgn_attorney_emplid)
INTO v_emplid
FROM schedule s, duration_code d, attorney_profile p
WHERE schedule_status_code IN ('SET', 'RESET')
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 alj_person_seq_num = in_alj
AND hearing_location_seq_num = in_hear_Loc
AND s.duration_code = d.code
AND s.asgn_attorney_emplid = p.profile_emplid
AND ( p.attorney_type_code = 'TRIAL'
OR p.attorney_type_code = 'FED')
AND ( in_wrkr_conflict = 'N'
OR p.confidential_claims_flag = 'Y')
AND (in_request_type = 'WCB' OR p.dcbs_claims_flag = 'Y')
AND s.asgn_attorney_emplid IS NOT NULL;
END IF;
RETURN v_emplid;
END same_time_loc_check;
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 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
LEFT OUTER JOIN legal_person p
ON S.ALJ_PERSON_SEQ_NUM = P.LEGAL_PERSON_SEQ_NUM
LEFT OUTER JOIN hearing_location l
ON S.HEARING_LOCATION_SEQ_NUM =
L.HEARING_LOCATION_SEQ_NUM
LEFT OUTER JOIN hearing_location_code c
ON L.HEARING_LOCATION_CODE = C.CODE
WHERE e.emplid = a.profile_emplid AND a.EXP_DATE IS NULL
ORDER BY primary_hear_loc_code, weekly_load, profile_emplid;
ELSE
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
LEFT OUTER JOIN legal_person p
ON S.ALJ_PERSON_SEQ_NUM = P.LEGAL_PERSON_SEQ_NUM
LEFT OUTER JOIN hearing_location l
ON S.HEARING_LOCATION_SEQ_NUM =
L.HEARING_LOCATION_SEQ_NUM
LEFT OUTER JOIN hearing_location_code c
ON L.HEARING_LOCATION_CODE = C.CODE
WHERE e.emplid = a.profile_emplid
AND a.EXP_DATE IS NULL
AND ( primary_hear_loc_code = in_loc_code
OR secondary_hear_loc_code = in_loc_code
OR tertiary_hear_loc_code = in_loc_code)
ORDER BY primary_hear_loc_code, weekly_load, profile_emplid;
END IF;
END calendar_review;
PROCEDURE getOutlookFields (recordset OUT ref_cursor,
inType IN VARCHAR2,
aNum IN NUMBER )
AS
BEGIN
IF inType = 'CASE'
THEN
BEGIN
OPEN recordset FOR
SELECT /*+ FIRST_ROWS */
s.schedule_seq_num,
cas.claim_id,
Pk80001_Legalnet.get_case_num (cas.legal_case_seq_num)
AS case_num,
st.code_descr AS schedule_type,
schedule_status_code AS schedule_status,
RTRIM (p.last_name) || ' ' || RTRIM (p.first_name)
AS alj_name,
h.hearing_location_code AS location_name,
s.duration_code,
d.days_num,
adp.user_id AS atty_mailid,
s.schedule_date,
s.schedule_time,
s.schedule_text,
cas.case_weight_value,
NVL (primary_case_seq_num, 0) AS consolidated
FROM legal_case cas,
schedule s,
schedule_type_code st,
legal_person p,
duration_code d,
adp_employee adp,
hearing_location h,
consolidated_case con
WHERE cas.legal_case_seq_num = aNum
AND s.legal_case_seq_num = cas.legal_case_seq_num
AND s.schedule_type_code = st.code
AND s.alj_person_seq_num = p.legal_person_seq_num(+)
AND s.asgn_attorney_emplid = adp.emplid
AND s.hearing_location_seq_num =
h.hearing_location_seq_num(+)
AND s.duration_code = d.code
AND cas.legal_case_seq_num = primary_case_seq_num(+)
AND cas.legal_case_seq_num = secondary_case_seq_num(+)
AND s.schedule_date IS NOT NULL
AND NVL (s.schedule_date, SYSDATE) >
ADD_MONTHS (SYSDATE, -3);
EXCEPTION
WHEN NO_DATA_FOUND
THEN
NULL;
END;
END IF;
IF intype = 'ASSIGN'
THEN
BEGIN
OPEN recordset FOR
SELECT /*+ FIRST_ROWS */
s.schedule_seq_num,
cas.claim_id,
Pk80001_Legalnet.get_case_num (cas.legal_case_seq_num)
AS case_num,
st.code_descr AS schedule_type,
schedule_status_code AS schedule_status,
RTRIM (p.last_name) || ' ' || RTRIM (p.first_name)
AS alj_name,
h.hearing_location_code AS location_name,
s.duration_code,
d.days_num,
adp.user_id AS atty_mailid,
s.schedule_date,
s.schedule_time,
s.schedule_text,
cas.case_weight_value,
NVL (primary_case_seq_num, 0) AS consolidated
FROM legal_case cas,
schedule s,
schedule_type_code st,
legal_person p,
duration_code d,
adp_employee adp,
hearing_location h,
assignment_result r,
consolidated_case con
WHERE r.assignment_result_seq_num > aNum
AND r.schedule_seq_num = s.schedule_seq_num
AND s.legal_case_seq_num = cas.legal_case_seq_num
AND s.schedule_type_code = st.code
AND s.alj_person_seq_num = p.legal_person_seq_num(+)
AND s.asgn_attorney_emplid = adp.emplid
AND s.hearing_location_seq_num =
h.hearing_location_seq_num(+)
AND s.duration_code = d.code
AND cas.legal_case_seq_num = primary_case_seq_num(+)
AND cas.legal_case_seq_num = secondary_case_seq_num(+)
AND r.assignment_result_Seq_num =
(SELECT MAX (assignment_result_seq_num)
FROM assignment_result
WHERE legal_case_seq_num =
r.legal_case_seq_num);
EXCEPTION
WHEN NO_DATA_FOUND
THEN
NULL;
END;
END IF;
IF intype = 'VERIFY'
THEN
BEGIN
OPEN recordset FOR
SELECT /*+ FIRST_ROWS */
s.schedule_seq_num,
cas.claim_id,
Pk80001_Legalnet.get_case_num (cas.legal_case_seq_num)
AS case_num,
st.code_descr AS schedule_type,
schedule_status_code AS schedule_status,
RTRIM (p.last_name) || ' ' || RTRIM (p.first_name)
AS alj_name,
h.hearing_location_code AS location_name,
s.duration_code,
d.days_num,
adp.user_id AS atty_mailid,
s.schedule_date,
s.schedule_time,
s.schedule_text,
cas.case_weight_value,
NVL (primary_case_seq_num, 0) AS consolidated
FROM legal_case cas,
schedule s,
schedule_type_code st,
legal_person p,
duration_code d,
adp_employee adp,
hearing_location h,
consolidated_case con
WHERE s.legal_case_seq_num = cas.legal_case_seq_num
AND s.schedule_type_code = st.code
AND s.alj_person_seq_num = p.legal_person_seq_num(+)
AND s.asgn_attorney_emplid = adp.emplid
AND s.hearing_location_seq_num =
h.hearing_location_seq_num(+)
AND s.duration_code = d.code
AND cas.legal_case_seq_num = primary_case_seq_num(+)
AND cas.legal_case_seq_num = secondary_case_seq_num(+)
AND ( cas.most_recent_hearing_seq_num =
s.schedule_seq_num
OR s.schedule_type_code <> 'HEAR')
AND s.schedule_date > TRUNC (SYSDATE);
EXCEPTION
WHEN NO_DATA_FOUND
THEN
NULL;
END;
END IF;
END getOutlookFields;
PROCEDURE getAttorneyPastHearings (recordset OUT ref_cursor, inUserid IN VARCHAR2)
AS
BEGIN
IF INUserid = 'OUTCOU'
THEN
BEGIN
OPEN recordset FOR
SELECT /*+ FIRST_ROWS */
s.legal_case_seq_num caseId,
s.schedule_seq_num scheduleId,
Pk80001_Legalnet.get_case_num (cas.legal_case_seq_num)
AS caseNum,
st.code_descr AS scheduleType,
ss.code_descr AS scheduleStatus,
RTRIM (p.last_name) || ' ' || RTRIM (p.first_name)
AS aljname,
h.city || ' - ' || h.location_name AS location,
s.duration_code duration,
d.days_num daysNum,
firm_name AS attyName,
s.schedule_date scheduleDate,
s.schedule_time scheduleTime,
s.assigned_date assignedDate,
cas.claim_id claimId,
'OUTCOU' mailId
FROM legal_case cas,
schedule s,
schedule_status_code ss,
schedule_type_code st,
legal_person p,
duration_code d,
other_firm o,
hearing_location h
WHERE s.legal_case_seq_num = cas.legal_case_seq_num
AND s.schedule_status_code = ss.code
AND s.schedule_type_code = st.code
AND s.alj_person_seq_num = p.legal_person_seq_num
AND s.hearing_location_seq_num =
h.hearing_location_seq_num
AND s.duration_code = d.code
AND s.schedule_seq_num =
cas.most_recent_hearing_Seq_num
AND o.other_firm_seq_num = s.asgn_other_firm_seq_num
AND s.schedule_status_code IN ('SET', 'RESET')
AND s.schedule_date < TRUNC (SYSDATE);
EXCEPTION
WHEN NO_DATA_FOUND
THEN
NULL;
END;
ELSE
BEGIN
OPEN recordset FOR
SELECT /*+ FIRST_ROWS */
s.legal_case_seq_num CaseId,
s.schedule_seq_num scheduleId,
Pk80001_Legalnet.get_case_num (cas.legal_case_seq_num)
AS caseNum,
st.code_descr AS scheduleType,
ss.code_descr AS scheduleStatus,
RTRIM (p.last_name) || ' ' || RTRIM (p.first_name)
AS aljName,
h.city || ' - ' || h.location_name AS location,
s.duration_code duration,
d.days_num daysNum,
adp.LAST_NAME
|| ', '
|| DECODE (adp.preferred_name,
' ', adp.FIRST_NAME,
adp.preferred_name)
AS attyName,
s.schedule_date AS scheduleDate,
s.schedule_time AS scheduleTime,
s.assigned_date AS assignedDate,
cas.claim_id AS claimId,
adp.user_id mailId
FROM legal_case cas,
schedule s,
schedule_status_code ss,
schedule_type_code st,
legal_person p,
duration_code d,
adp_employee adp,
hearing_location h
WHERE s.legal_case_seq_num = cas.legal_case_seq_num
AND s.schedule_status_code = ss.code
AND s.schedule_type_code = st.code
AND s.alj_person_seq_num = p.legal_person_seq_num
AND s.asgn_attorney_emplid = adp.emplid
AND s.hearing_location_seq_num =
h.hearing_location_seq_num
AND s.duration_code = d.code
AND s.schedule_seq_num =
cas.most_recent_hearing_seq_num
AND adp.user_id = inuserid
AND s.schedule_status_code IN ('SET', 'RESET')
AND s.schedule_date < TRUNC (SYSDATE)
ORDER BY 10;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
NULL;
END;
END IF;
END getAttorneyPastHearings;
PROCEDURE getAttorneyConflictHearings (recordset OUT ref_cursor,
inUserid IN VARCHAR2)
AS
BEGIN
IF INUserid = 'CONFLICT'
THEN
BEGIN
OPEN recordset FOR
SELECT a.*
FROM (SELECT /*+ FIRST_ROWS */
s.legal_case_seq_num caseId,
s.schedule_seq_num scheduleId,
Pk80001_Legalnet.get_case_num (
cas.legal_case_seq_num)
AS casenum,
st.code_descr AS scheduleType,
ss.code_descr AS scheduleStatus,
RTRIM (p.last_name)
|| ' '
|| RTRIM (p.first_name)
AS aljName,
h.city || ' - ' || h.location_name AS location,
s.duration_code duration,
d.days_num daysNum,
adp2.LAST_NAME
|| ', '
|| DECODE (adp2.preferred_name,
' ', adp2.FIRST_NAME,
adp2.preferred_name)
AS attyName,
s.schedule_date scheduleDate,
s.schedule_time scheduleTime,
s.assigned_date assignedDate,
cas.claim_id claimId,
adp.user_id mailId
FROM legal_case cas,
schedule s,
schedule_status_code ss,
schedule_type_code st,
legal_person p,
duration_code d,
adp_employee adp,
adp_employee adp2,
hearing_location h,
attorney_profile a
WHERE cas.case_status_code <> 'CLOSE'
AND s.legal_case_seq_num =
cas.legal_case_seq_num
AND s.schedule_status_code = ss.code
AND s.schedule_type_code = st.code
AND s.alj_person_seq_num =
p.legal_person_seq_num
AND s.asgn_attorney_emplid = adp.emplid
-- exclude recon cases
AND s.asgn_attorney_emplid = a.profile_emplid
AND a.attorney_type_code <> 'APEAL'
AND s.hearing_location_seq_num =
h.hearing_location_seq_num
AND s.duration_code = d.code
AND cas.most_recent_hearing_seq_num =
s.schedule_seq_num
AND s.schedule_date > TRUNC (SYSDATE)
AND s.schedule_status_code IN ('SET', 'RESET')
AND d.days_num = 0
AND adp2.emplid = s.asgn_attorney_emplid
AND EXISTS
(SELECT 1
-- AND adp2.emplid = (SELECT MIN(asgn_attorney_emplid)
FROM schedule s2, attorney_profile a2
WHERE s2.schedule_status_code IN ('SET',
'RESET')
AND s.schedule_date =
s2.schedule_date
AND s.duration_code =
s2.duration_code
AND s.alj_person_seq_num =
s2.alj_person_seq_num
AND s.hearing_location_seq_num =
s2.hearing_location_seq_num
AND s2.asgn_attorney_emplid
IS NOT NULL
AND s2.asgn_attorney_emplid <>
s.asgn_attorney_emplid
-- exclude recon cases
AND s2.asgn_attorney_emplid =
a2.profile_emplid
AND a2.attorney_type_code <>
'APEAL')
UNION
SELECT /*+ FIRST_ROWS */
s.legal_case_seq_num,
s.schedule_seq_num,
Pk80001_Legalnet.get_case_num (
cas.legal_case_seq_num)
AS case_num,
st.code_descr AS schedule_type,
ss.code_descr AS schedule_status,
RTRIM (p.last_name)
|| ' '
|| RTRIM (p.first_name)
AS alj_name,
h.city || ' - ' || h.location_name
AS location_name,
s.duration_code,
d.days_num,
adp2.LAST_NAME
|| ', '
|| DECODE (adp2.preferred_name,
' ', adp2.FIRST_NAME,
adp2.preferred_name)
AS atty_name,
s.schedule_date,
s.schedule_time,
s.assigned_date,
cas.claim_id claimId,
adp.user_id mailId
FROM legal_case cas,
schedule s,
schedule_status_code ss,
schedule_type_code st,
legal_person p,
duration_code d,
adp_employee adp,
adp_employee adp2,
hearing_location h,
attorney_profile a
WHERE cas.case_status_code <> 'CLOSE'
AND s.legal_case_seq_num =
cas.legal_case_seq_num
AND s.schedule_status_code = ss.code
AND s.schedule_type_code = st.code
AND s.alj_person_seq_num =
p.legal_person_seq_num
AND s.asgn_attorney_emplid = adp.emplid
-- exclude recon cases
AND s.asgn_attorney_emplid = a.profile_emplid
AND a.attorney_type_code <> 'APEAL'
AND s.hearing_location_seq_num =
h.hearing_location_seq_num
AND s.duration_code = d.code
AND cas.most_recent_hearing_seq_num =
s.schedule_seq_num
AND d.days_num > 0
AND s.schedule_date > TRUNC (SYSDATE)
AND s.schedule_status_code IN ('SET', 'RESET')
AND adp2.emplid = s.asgn_attorney_emplid
AND EXISTS
(SELECT 1
FROM schedule s2,
duration_code d2,
attorney_profile a2
WHERE s2.schedule_status_code IN ('SET',
'RESET')
AND ( ( s.schedule_date =
s2.schedule_date
AND d2.days_num = 1)
OR ( d2.days_num > 1
AND s.schedule_date BETWEEN s2.schedule_date
AND ( s2.schedule_date
+ d2.days_num
- 1)))
AND s2.alj_person_seq_num =
s.alj_person_seq_num
AND s2.hearing_location_seq_num =
s.hearing_location_seq_num
AND s2.duration_code = d2.code
AND s2.asgn_attorney_emplid
IS NOT NULL
AND s2.asgn_attorney_emplid <>
s.asgn_attorney_emplid
-- exclude recon cases
AND s2.asgn_attorney_emplid =
a2.profile_emplid
AND a2.attorney_type_code <>
'APEAL')) a
ORDER BY 11;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
NULL;
END;
ELSE
BEGIN
OPEN recordset FOR
SELECT a.*
FROM (SELECT /*+ FIRST_ROWS */
s.legal_case_seq_num caseId,
s.schedule_seq_num scheduleId,
Pk80001_Legalnet.get_case_num (
cas.legal_case_seq_num)
AS caseNum,
st.code_descr AS scheduleType,
ss.code_descr AS scheduleStatus,
RTRIM (p.last_name)
|| ' '
|| RTRIM (p.first_name)
AS aljName,
h.city || ' - ' || h.location_name AS location,
s.duration_code duration,
d.days_num daysNum,
adp2.LAST_NAME
|| ', '
|| DECODE (adp2.preferred_name,
' ', adp2.FIRST_NAME,
adp2.preferred_name)
AS attyName,
s.schedule_date scheduleDate,
s.schedule_time scheduleTime,
s.assigned_date assignedDate,
cas.claim_id claimId,
adp.user_id mailId
FROM legal_case cas,
schedule s,
schedule_status_code ss,
schedule_type_code st,
legal_person p,
duration_code d,
adp_employee adp,
adp_employee adp2,
hearing_location h,
attorney_profile a,
schedule s2,
duration_code d2,
attorney_profile a2
WHERE cas.case_status_code <> 'CLOSE'
AND s.legal_case_seq_num =
cas.legal_case_seq_num
AND s.schedule_status_code = ss.code
AND s.schedule_type_code = st.code
AND s.alj_person_seq_num =
p.legal_person_seq_num
AND s.asgn_attorney_emplid = adp.emplid
-- exclude recon cases
AND s.asgn_attorney_emplid = a.profile_emplid
AND a.attorney_type_code <> 'APEAL'
AND s.hearing_location_seq_num =
h.hearing_location_seq_num
AND s.duration_code = d.code
AND cas.most_recent_hearing_seq_num =
s.schedule_seq_num
AND s.schedule_date > TRUNC (SYSDATE)
AND s.schedule_status_code IN ('SET', 'RESET')
AND d.days_num = 0
AND adp.user_id = inUserid
AND adp2.emplid = s2.asgn_attorney_emplid
AND s2.schedule_status_code IN ('SET', 'RESET')
AND s.schedule_date = s2.schedule_date
AND s.duration_code = s2.duration_code
AND s.alj_person_seq_num =
s2.alj_person_seq_num
AND s2.hearing_location_seq_num =
s.hearing_location_seq_num
AND s2.duration_code = d2.code
AND s2.asgn_attorney_emplid IS NOT NULL
AND s2.asgn_attorney_emplid <>
s.asgn_attorney_emplid
AND s2.asgn_attorney_emplid = a2.profile_emplid
AND a2.attorney_type_code <> 'APEAL'
UNION
SELECT /*+ FIRST_ROWS */
s.legal_case_seq_num,
s.schedule_seq_num,
Pk80001_Legalnet.get_case_num (
cas.legal_case_seq_num)
AS case_num,
st.code_descr AS schedule_type,
ss.code_descr AS schedule_status,
RTRIM (p.last_name)
|| ' '
|| RTRIM (p.first_name)
AS alj_name,
h.city || ' - ' || h.location_name
AS location_name,
s.duration_code,
d.days_num,
adp2.LAST_NAME
|| ', '
|| DECODE (adp2.preferred_name,
' ', adp2.FIRST_NAME,
adp2.preferred_name)
AS atty_name,
s.schedule_date,
s.schedule_time,
s.assigned_date,
cas.claim_id,
adp.user_id mailId
FROM legal_case cas,
schedule s,
schedule_status_code ss,
schedule_type_code st,
legal_person p,
duration_code d,
adp_employee adp,
adp_employee adp2,
hearing_location h,
attorney_profile a,
schedule s2,
duration_code d2,
attorney_profile a2
WHERE cas.case_status_code <> 'CLOSE'
AND s.legal_case_seq_num =
cas.legal_case_seq_num
AND s.schedule_status_code = ss.code
AND s.schedule_type_code = st.code
AND s.alj_person_seq_num =
p.legal_person_seq_num
AND s.asgn_attorney_emplid = adp.emplid
-- exclude recon cases
AND s.asgn_attorney_emplid = a.profile_emplid
AND a.attorney_type_code <> 'APEAL'
AND s.hearing_location_seq_num =
h.hearing_location_seq_num
AND s.duration_code = d.code
AND cas.most_recent_hearing_seq_num =
s.schedule_seq_num
AND d.days_num > 0
AND s.schedule_date > TRUNC (SYSDATE)
AND s.schedule_status_code IN ('SET', 'RESET')
AND adp.user_id = inUserid
AND adp2.emplid = s2.asgn_attorney_emplid
AND s2.schedule_status_code IN ('SET', 'RESET')
AND ( ( s.schedule_date = s2.schedule_date
AND d2.days_num = 1)
OR ( d2.days_num > 1
AND s.schedule_date BETWEEN s2.schedule_date
AND ( s2.schedule_date
+ d2.days_num
- 1)))
AND s2.alj_person_seq_num =
s.alj_person_seq_num
AND s2.hearing_location_seq_num =
s.hearing_location_seq_num
AND s2.duration_code = d2.code
AND s2.asgn_attorney_emplid IS NOT NULL
AND s2.asgn_attorney_emplid <>
s.asgn_attorney_emplid
AND s2.asgn_attorney_emplid = a2.profile_emplid
AND a2.attorney_type_code <> 'APEAL') a
ORDER BY 11;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
NULL;
END;
END IF;
END getAttorneyConflictHearings;
PROCEDURE getAttorneyScheduleConflict (recordset OUT ref_cursor, inUserid IN VARCHAR2)
AS
BEGIN
OPEN recordset FOR
SELECT a.*
FROM (SELECT /*+ FIRST_ROWS */
s.legal_case_seq_num caseId,
s.schedule_seq_num scheduleId,
wcis_dba.Pk80001_Legalnet.get_case_num (
cas.legal_case_seq_num)
AS caseNum,
st.code_descr AS scheduleType,
ss.code_descr AS scheduleStatus,
RTRIM (p.last_name) || ' ' || RTRIM (p.first_name)
AS aljName,
h.city || ' - ' || h.location_name AS location,
s.duration_code duration,
d.days_num daysNum,
'Scheduled Event' AS attyName,
s.schedule_date scheduleDate,
s.schedule_time scheduleTime,
s.assigned_date assignedDate,
cas.claim_id claimId,
adp.user_id mailId
FROM legal_case cas,
schedule s,
schedule_status_code ss,
schedule_type_code st,
legal_person p,
duration_code d,
adp_employee adp,
hearing_location h
WHERE cas.case_status_code <> 'CLOSE'
AND s.legal_case_seq_num = cas.legal_case_seq_num
AND s.schedule_status_code = ss.code
AND s.schedule_type_code = st.code
AND s.alj_person_seq_num = p.legal_person_seq_num(+)
AND s.asgn_attorney_emplid = adp.emplid
AND s.hearing_location_seq_num =
h.hearing_location_seq_num(+)
AND s.duration_code = d.code
AND s.schedule_date > TRUNC (SYSDATE)
AND s.schedule_status_code IN ('SET', 'RESET')
AND d.days_num = 0
AND adp.user_id = inUserid
AND EXISTS
(SELECT 1
FROM schedule s2
WHERE s2.schedule_status_code IN ('SET',
'RESET')
AND s.schedule_date = s2.schedule_date
AND s.duration_code = s2.duration_code
AND ( NVL (s2.alj_person_seq_num, 0) <>
NVL (s.alj_person_seq_num,
0)
OR NVL (
s2.hearing_location_seq_num,
0) <>
NVL (
s.hearing_location_seq_num,
0))
AND s2.asgn_attorney_emplid =
s.asgn_attorney_emplid)
UNION
SELECT /*+ FIRST_ROWS */
s.legal_case_seq_num,
s.schedule_seq_num,
wcis_dba.Pk80001_Legalnet.get_case_num (
cas.legal_case_seq_num)
AS case_num,
st.code_descr AS schedule_type,
ss.code_descr AS schedule_status,
RTRIM (p.last_name) || ' ' || RTRIM (p.first_name)
AS alj_name,
h.city || ' - ' || h.location_name AS location_name,
s.duration_code,
d.days_num,
'Scheduled Event' AS atty_name,
s.schedule_date,
s.schedule_time,
s.assigned_date,
cas.claim_id,
adp.user_id mailId
FROM legal_case cas,
schedule s,
schedule_status_code ss,
schedule_type_code st,
legal_person p,
duration_code d,
adp_employee adp,
hearing_location h
WHERE cas.case_status_code <> 'CLOSE'
AND s.legal_case_seq_num = cas.legal_case_seq_num
AND s.schedule_status_code = ss.code
AND s.schedule_type_code = st.code
AND s.alj_person_seq_num = p.legal_person_seq_num(+)
AND s.asgn_attorney_emplid = adp.emplid
AND s.hearing_location_seq_num =
h.hearing_location_seq_num(+)
AND s.duration_code = d.code
AND s.schedule_date > TRUNC (SYSDATE)
AND s.schedule_status_code IN ('SET', 'RESET')
AND d.days_num > 0
AND adp.user_id = inUserid
AND EXISTS
(SELECT 1
FROM schedule s2, duration_code d2
WHERE s2.schedule_status_code IN ('SET',
'RESET')
AND ( ( s.schedule_date =
s2.schedule_date
AND d2.days_num = 1)
OR ( d2.days_num > 1
AND s.schedule_date BETWEEN s2.schedule_date
AND ( s2.schedule_date
+ d2.days_num
- 1)))
AND ( NVL (s2.alj_person_seq_num, 0) <>
NVL (s.alj_person_seq_num,
0)
OR NVL (
s2.hearing_location_seq_num,
0) <>
NVL (
s.hearing_location_seq_num,
0))
AND s2.duration_code = d2.code
AND s2.asgn_attorney_emplid =
s.asgn_attorney_emplid)
UNION
SELECT /*+ FIRST_ROWS */
s.legal_case_seq_num,
ROWNUM AS schedule_seq_num,
wcis_dba.Pk80001_Legalnet.get_case_num (
cas.legal_case_seq_num)
AS case_num,
st.code_descr AS schedule_type,
ss.code_descr AS schedule_status,
RTRIM (p.last_name) || ' ' || RTRIM (p.first_name)
AS alj_name,
h.city || ' - ' || h.location_name AS location_name,
s.duration_code,
d.days_num,
'Time Off' AS atty_name,
s.schedule_date,
s.schedule_time,
s.assigned_date,
cas.claim_id,
adp.user_id mailId
FROM legal_case cas,
schedule s,
schedule_status_code ss,
schedule_type_code st,
legal_person p,
duration_code d,
adp_employee adp,
attorney_schedule s2,
hearing_location h
WHERE cas.case_status_code <> 'CLOSE'
AND s.legal_case_seq_num = cas.legal_case_seq_num
AND s.schedule_status_code = ss.code
AND s.schedule_type_code = st.code
AND s.alj_person_seq_num = p.legal_person_seq_num(+)
AND s.asgn_attorney_emplid = adp.emplid
AND s.hearing_location_seq_num =
h.hearing_location_seq_num(+)
AND s.duration_code = d.code
AND d.days_num = 0
AND s.schedule_date > TRUNC (SYSDATE)
AND s.schedule_status_code IN ('SET', 'RESET')
AND adp.user_id = inUserid
AND s.asgn_attorney_emplid = s2.schedule_emplid
AND s.schedule_date BETWEEN s2.eff_date
AND s2.exp_date
AND ( s.duration_code = s2.duration_code
OR s2.duration_code = 'FULL')
UNION
SELECT /*+ FIRST_ROWS */
s.legal_case_seq_num,
ROWNUM + 1000 AS schedule_seq_num,
wcis_dba.Pk80001_Legalnet.get_case_num (
cas.legal_case_seq_num)
AS case_num,
st.code_descr AS schedule_type,
ss.code_descr AS schedule_status,
RTRIM (p.last_name) || ' ' || RTRIM (p.first_name)
AS alj_name,
h.city || ' - ' || h.location_name AS location_name,
s.duration_code,
d.days_num,
'Time Off' AS atty_name,
s.schedule_date,
s.schedule_time,
s.assigned_date,
cas.claim_id,
adp.user_id mailId
FROM legal_case cas,
schedule s,
schedule_status_code ss,
schedule_type_code st,
legal_person p,
duration_code d,
adp_employee adp,
attorney_schedule s2,
hearing_location h
WHERE cas.case_status_code <> 'CLOSE'
AND s.legal_case_seq_num = cas.legal_case_seq_num
AND s.schedule_status_code = ss.code
AND s.schedule_type_code = st.code
AND s.alj_person_seq_num = p.legal_person_seq_num(+)
AND s.asgn_attorney_emplid = adp.emplid
AND s.hearing_location_seq_num =
h.hearing_location_seq_num(+)
AND s.duration_code = d.code
AND d.days_num > 0
AND s.schedule_date > TRUNC (SYSDATE)
AND s.schedule_status_code IN ('SET', 'RESET')
AND adp.user_id = inUserid
AND s.asgn_attorney_emplid = s2.schedule_emplid
AND s.schedule_date BETWEEN s2.eff_date
AND s2.exp_date) a
ORDER BY 11;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
NULL;
END getAttorneyScheduleConflict;
PROCEDURE getConsolFileLocation (inLegalCaseId IN NUMBER,
recordset OUT ref_cursor)
AS
BEGIN
OPEN recordset FOR
SELECT c.secondary_case_seq_num,
l.location_person_emplid,
l.move_date,
l.legal_file_location_code
FROM consolidated_case c,
legal_file_location l,
legal_file_location l2
WHERE c.primary_case_seq_num = inLegalCaseId
AND c.primary_case_seq_num = l.legal_case_seq_num
AND c.secondary_case_Seq_num = l2.legal_case_seq_num
AND l.legal_file_location_seq_num =
(SELECT MAX (legal_file_location_seq_num)
FROM legal_file_location
WHERE legal_case_seq_num =
c.primary_case_seq_num
AND move_date =
(SELECT MAX (move_date)
FROM legal_file_location
WHERE legal_case_seq_num =
c.PRIMARY_case_seq_num))
AND l2.legal_file_location_seq_num =
(SELECT MAX (legal_file_location_seq_num)
FROM legal_file_location
WHERE legal_case_seq_num =
c.secondary_case_seq_num
AND move_date =
(SELECT MAX (move_date)
FROM legal_file_location
WHERE legal_case_seq_num =
c.secondary_case_seq_num))
AND ( NVL (l.location_person_emplid, 'NONE') <>
NVL (l2.location_person_emplid, 'NONE')
OR NVL (l.legal_file_location_code, 'NONE') <>
NVL (l2.legal_file_location_code, 'NONE'));
END getConsolFileLocation;
PROCEDURE Send_Assignment_Info (inText IN VARCHAR2)
IS
BEGIN
pk10001_email.send ('gremil@saif.com',
'gremil@saif.com,timfor@saif.com,brisly@saif.com',
'Attorney Assignment Info',
inText);
END Send_Assignment_Info;
END;
Related Documentation¶
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.
Sources¶
- Oracle DBA_SOURCE: WCIS_DBA.pk80005_assignment
- Legends CLI query executed 2026-08-31:
legends oracle query --owner SAIF --name pk80005_assignment - Calling code:
SAIF.Business.Claim.Legal(clsAssignmentProcess,clsAttorneyConflict,clsAttorneySchedule)