Attachment 16 - Wheel Loading Functions.pdf

Wheel Loading Functions

Web-Based Jury Management System Requirements

Version 1.0

Attachment 16

Contents Overview

Users and Roles Stakeholders

Initial Wheel Loading

Refilling New Wheel

Supplementing a Wheel

Property of the Administrative Office of the U.S. Courts Attachment 16 1

Overview

The current Jury Management System (JMS) uses old technology and is being upgraded to new server technologies as well as being upgraded to a web-based application. The look and feel of the web-based application is expected to be substantially different from the current user-driven interface.

An important function of the Jury Management System is loading the Jury Wheel. The Wheel Loading process should be done when no users are logged in to the Jury Management as new records will be inserted in to the Bioform table. The steps in this document are in chronological order and should be followed exactly as listed. The following Wheel Loading business functions are required.

Users and Roles

Jury Administrators, Jury Clerks, and other jury-related user roles

Stakeholders

JMS users

Attachment 16 2

Initial Wheel Loading The file containing the Jury Wheel must be uploaded to the database server. Once the wheel has been uploaded, the wheel data must be inserted into the database. This process is accomplished by using the dbload utility that is included with the database engine and by completing the steps listed below.

• Log in to the JMS Linux system as the database user.

• Change the working directory to the directory containing the uploaded wheel file.

• Review the “load.ctl” file to ensure that it is based on 45 columns and includes the court’s wheel file name.

For example:

FILE “Courts Wheel File Name”.TXT DELIMITER ‘~’; INSERT INTO voters;

• Execute the dbload utility.

dbload -d <dbname> -c load.ctl -l load.log -e ###

### is replaced by the number of errors where “dbload” aborts. The number should not exceed 100, to allow the System Administrator to correct errors.

As “dbload” executes, several lines of text will be displayed. An unsuccessful load will produce messages that are written to the “load.log” file. The messages list the records that did not load into the table and include the reason. Information that may be in the log could be records formatted incorrectly, invalid characters, or invalid addresses.

The System Administrator should discuss the errors found in the log file with the Jury Administrator.

• After the wheel has been successfully loaded into the database, the System Administrator must run the insert_bioform_county.sql. This function will insert a new record into the Bioform table for each juror.

dbaccess <your_test_database_name> insert _bioform_county.sql

• Log into web-based JMS.

• Select the new wheel.

o Set the wheel year.

o Enter wheel date.

o Enter beginning and ending record numbers.

o Enter number of divisions in the wheel.

o Specify the wheel the system will use to create pools, etc.

o Verify the correct court type is selected (1-Step or 2-Step).

Attachment 16 3

Refilling New Wheel

The process of loading an initial wheel or refilling a new wheel is identical except for the juror numbers and record numbers. When source data is converted for new wheel filling, the beginning record number that is listed on the command line should be greater than the largest record number of the current jury wheel (to avoid overlapping record numbers between wheels).

• Run the following statement to find the minimum and maximum record numbers:

dbaccess <dbname> maxmin.sql

• Log in to the web-based JMS.

• Specify the following parameters:

a. Set the wheel year.

b. Enter wheel date.

c. Enter beginning and ending record numbers.

d. Select the source list (i.e., voter registration, MVD, etc.).

e. Choose the wheel the system will use to create pools, etc.

f. Verify the correct court type is selected (One-Step or Two-Step).

Supplementing a Wheel

The process for supplementing a juror wheel is similar to the initial loading and refilling of a juror wheel. The supplemental wheel data file being uploaded to the server must have a beginning record number that is greater than the largest record number of the current wheel.

• Run the following statement to find the minimum and maximum record numbers:

dbaccess <dbname> maxmin.sql

• Log into the web-based JMS as the System Administrator.

• Select the wheel to be supplemented.

• Change the maximum record number to match the last record number of the new data that was loaded.

# Field Name Type & Length

Description Example Required Notes

1 part_no numeric 9 participant number (juror number), uniquely identifies prospective jurors

100000001 Yes When creating a new wheel, enter the first value and it automatically increments by one for each record in the file. See Note 1.

2 county_code char 3 county code, 3-digit number used by courts to identify counties

002 Yes Max length is 3 in JUROR/JMS. Should be included in source list data from vendor. It is suggested to use FIPS codes.

3 lname char 20 last name Smith Yes 4 fname char 20 first name, including middle name or initial

Joan S. Yes

5 address char 40 address 123 M St. Yes FAST only accepts 30 characters.

6 city char 20 city Chicago Yes 7 state char 2 state IL Yes Use postal standard abbreviations.

8 zip char 10 postal code, including postal extension

12345-6789 Yes Formatting characters should be included in the data (XXXXX-XXXX i.e. include hyphens (-)). If zip is only 5 characters DO NOT include hyphen (-).

9 sex char 1 gender M No (M) Male, (F) Female, (U) Unknown, (O) Other, or leave null

10 race char 1 ethnicity 1 No 1 = Black, 3 = White, 5 = Native American Indian, 6 = Asian, 7 = Other, 9 = Unknown

11 status char 2 status 0 if 2-step Yes See Note 2.

5 if 1-step

12 dob datetime year to second date of birth YYYY-MM-

DD

HH:MM:SS

No Datetime year to second format. Time should be 00:00:00

13 h_phone char 12 home phone 1112223333 No No formatting characters should be included in the data (i.e., no hyphen (-) or parentheses ()).

14 voter_div char 5 voter's division, captured if available

No Leave null

15 voter_reg char 12 voter's registration number, captured if using voter info to make up source list

No Leave null

16 jud_code char 2 judge code, used in the JMS application

No Leave null

17 d_licence char 15 driver's license number

No Leave null. This information can be entered manually in the application.

18 sin char 11 social security number

111223333 No No formatting characters should be included in the data (i.e., no hyphens (-)).

19 date_selected1 datetime year to second date selected to pool

No Leave null

20 date_selected2 datetime year to second date that the juror ID was Added-To-The- Qualified-Wheel

No Leave null

21 date_selected3 datetime year to second date that the questionnaire was printed (or re-printed)

No Leave null

22 rec_num numeric 9 record number, part_no – 100000000

(100000234- 100000000)

Yes Used in utility of wheel maintenance within application.

23 perm_disqual char 1 used by JMS No Leave null 24 scan_code Varchar 10 used by JMS No Leave null 25 division_code char 1 divisional office code 1 Yes Primary key within the database, record will not load without this code defined, use traditional divisional office codes.

26 official_use char 2 official use field No Leave null 27 court_emp char 1 used by JMS No Leave null 28 Hispanic char 1 hispanic (Yes, No, Unknown, No or Null) 29 Notes Varchar used by JMS No

30 Citizen char 1 used by JMS No 31 date_upd date used by JMS No 32 Source Char 10 file source V Yes (Voters, Drivers, Other) 33 cell_phone Char 1 used by JMS No 34 w_phone Char 12 used by JMS No 35 w_ph_local Char 4 used by JMS No 36 email Varchar populated by Online Public Access Juror Management

No

37 pending_ind Numeric 1 used by JMS 0 Yes Used by Online Public Access Juror Management - set to 0 (zero)

38 pending_expires datetime year to second used by JMS No

39 loc_code Char 3 used by JMS No 40 name_pend_ind Char 1 used by JMS N Yes Must be entered as N 41 name_pend_expires datetime year to second used by JMS No

42 Login_lock Char 1 used by JMS No 43 date_selected4 Date used by JMS No Leave null 44 qualified_date Date used by JMS No Leave null 45 pool_creation_date datetime year to second

Yes

Note 1: Because a wheel is defined as a range of numbers, the juror number MUST be 9 digits in length and should be consecutive within the file.

For example, jurors 100000001 through 100500000 make up wheel 98A that was formed after the 1998 elections. It is HIGHLY recommended to start off with low numbers, i.e., 100000001 as the first juror number for the master wheel load, since subsequent wheels will need to have higher, non-redundant numbers.

Note 2: This field must be one of two values (0 or 5). 0 = not qualified and is to be used for loading master wheels for 2-Step courts. 5 = qualified but not summoned; this should be used by one-step courts for loading master wheels. For one-step courts, loading master wheel records with this status (qualified but not summoned) may seem misleading; however, the Jury Management System as designed to consider all 1-Step master wheel records qualified until processing after selection for pools disqualifies or excuses the prospective juror.