Field Name

Key Type

Req ?

Data Type

Field Size

Valid Values

Other

Assign_resource

Assign_resource_inits

PK, FK

Y

Char

3

Must be in/from USER

References: USER

Assign_workreq_id

PK, FK

Y

Uint

4

Must be in/from Workreq_activity

References: Workreq_activity

Assign_workreq_act_id

PK, FK

Y

Uint

4

Must be in/from Workreq_activity

References: Workreq_activity

Assign_status

Y

Char

9

Assigned, Cancelled

Probably not a validation table.

Assign_hours_est

Y

Real

8

Assign_comment

N

Text

125

No validation

Assign_last_upd_ts

Y

Char

17

From System date/time

Assign_last_upd_inits

Y

Char

3

From Login_initials

Login_admin

Login_ID

PK

Y

Char

8

Mainframe ID?

Login_user_inits

FK

Y

Char

3

Must be in/from USER

References: USER

Login_last_upd_ts

Y

Char

17

From System date/time

Login_last_upd_inits

Y

Char

3

From Login_initials

USER

User_inits

PK

Y

Char

3

Unique

User_name

Y

Text

30

No validation

User_access_privilege_cd

Y

Char

1

Client, ACS

Internally a code, externally text

User_active_ind

Y

Char

1

Y (Active), N (Not active)

User_auth_system_codes

FK

N

Char

40

Must be in/from SYSTEM_LOOKUP

References: SYSTEM_LOOKUP Codes are concatenated in XXX,XXX,XXX format and define the system(s) on which this user is authorized to report. ACS users can report on all systems

User_phone

Y

Char

20

(nnn)nnn-nnnn

User_email

N

Text

125

No validation

User_comment

N

Text

125

No validation

User_last_upd_ts

Y

Char

17

From System date/time

User_last_upd_inits

Y

Char

3

From Login_initials

Workreq_activity

Workreq_id

PK

Y

Uint

4

System-generated

Workreq_act_id

PK

Y

Uint

4

Zeroes indicate 'this is a work request'

Workreq_act_name

Y

Char

20

No validation

Workreq_act_type

FK

Y (for activity)

Char

20

Must be in/from WORKREQ_ACTIVITY_TYPE_LOOKUP

References: WORKREQ_ACTIVITY_TYPE_LOOKUP

Workreq_act_status

FK

Y

Char

20

Must be in/from WORKREQ_ACTIVITY_STATUS_LOOKUP

References: WORKREQ_ACTIVITY_STATUS_LOOKUP

Workreq_leader_inits

FK

Y (for Workreq)

Char

3

Must be in/from USER

User_access_privilege_code must be 'ACS'

References: USER

Workreq_request_dt

Y (for Workreq)

Date

4

If entered, must be a valid date

Workreq_due_dt

N

Date

4

If entered, must be a valid date

Workreq_system_cd

FK

Y (for Workreq)

Char

20

Must be in/from SYSTEM_LOOKUP

References: SYSTEM_LOOKUP See instruction brochure for filling out the Work Request.

Workreq_requestor_name

Y

Text

30

No validation

Workreq_requestor_phone

Y

Char

20

No validation

Workreq_requestor_email

N

Text

125

No validation

Workreq_auth_mgr_inits

FK

Y

Char

3

For Clients who are logged in, pulled from Login_initials. Pull down list of all login names for ACS

References: LOGIN_ADMIN or USER

Workreq_reason

FK

N

Text

125

Must be in/from WORKREQ_ _REASON__LOOKUP

References: WORKREQ_ _REASON__LOOKUP

If 'Other' is selected, text can be appended.

Workreq_pgm_name

N

Text

125

No validation

Workreq_description

Y (for Workreq)

Text

125

No validation

Workreq_func_specs

Y (for Workreq)

Text

125

No validation

Workreq_improvement

N

Text

125

No validation

Workreq_due_dt_reason

N

Text

125

No validation

Workreq_act_comment

N

Text

125

No validation

Workreq_submitted_dt

Y

Date

4

The date a Client enters the Work Request into the system or the fax or postmark date when an ACS person enters the request

From System date or key entry

Workreq_received_ts

N

Char

17

From System date/time

Workreq_received_inits

FK

N

Char

3

Must be in/from USER

From Login_initials

Workreq_approved_dt

N

Date

4

From System date

Workreq_assigned_to_inits

FK

N

Char

3

Must be in/from USER

Selected from a pull down of any user with User_access_privilege_code = 'ACS'

References: USER

Workreq_hours_est

N

Real

8

Roll up of assign_hours_est in ASSIGN_RESOURCE

Calculated from ASSIGN_RESOURCE, not stored in the database

Workreq_start_dt

N

Date

4

Valid date keyed by the Resource who first starts working on the project.

Workreq_target_complete_dt

N

Date

4

Valid date keyed by ACS Manager.

Workreq_signoff_name

FK

N

Text

30

Must be in/from USER

Name keyed by ACS Manager

References: USER

Workreq_signoff_dt

N

Date

4

Valid date keyed by ACS Manager

Workreq_tested_by_name

N

Text

30

Name keyed by ACS Manager

Workreq_tested_dt

N

Date

4

Valid date keyed by ACS Manager

Workreq_act_anticip_start_dt

N

Date

4

Valid date keyed by ACS Manager

Workreq_act_actual_start_date

N

Date

4

Valid date keyed by ACS Manager

Workreq_act_anticip_complete_dt

N

Date

4

Valid date keyed by ACS Manager

Workreq_act_actual_complete_dt

N

Date

4

Valid date keyed by ACS Manager

Workreq_act_last_upd_ts

Y

Char

17

From System date/time

Workreq_act_last_upd_inits

Y

Char

3

From Login_initials

TIMESHEET

Timesheet_resource_inits

PK, FK

Y

Char

3

Must be in/from USER

References: USER

Timesheet_workreq_id

PK, FK

Y

Uint

4

Must be in/from Workreq_activity

References: Workreq_activity

Timesheet_workreq_activity_id

PK, FK

Y

Uint

4

Must be in/from Workreq_activity

References: Workreq_activity

Timesheet_date

PK??

Y

Date

4

Valid date keyed by the Resource

Timesheet_hours

Y

Real

8

Hours keyed in by the Resource

Timesheet_last_upd_ts

Y

Char

17

From System date/time

Timesheet_last_upd_inits

Y

Char

3

From Login_initials

SYSTEM_LOOKUP

System_cd

PK

Y

Char

3

System_name

Y

Text

30

Accounting, Accounts Payable, Admissions, Alumni, Budget, Bursar, Cashiers, Collections, Co-op, Financial Aid, HRM, Institutional Research, Payroll, Purchasing, Registrar, Residential Life, Web Server, ACS

System_last_upd_ts

Y

Char

17

From System date/time

System_last_upd_inits

Y

Char

3

Keyed in by system administrator

WORKREQ_ACTIVITY_TYPE_LOOKUP

Workreq_act_type

PK

Y

Char

20

Activity: requirements analysis, definition, design, coding, testing, implementation, documentation

For Admin. WR: Holiday, Floating Holiday, Personal, Vacation, Sick, Training, Conferences, Reviews, Bereavement, Leave of Absence

????

Workreq: Blank

There is no validation to prevent Admin-related or Maint-related Activity Types from being used for other types of activities.

Workreq_act_type_valid_for_cd

Y

Char

1

A=Valid for an Activity

W=Valid for a Work Request

Workreq_act_type_last_upd_ts

Y

Char

17

From System date/time

Workreq_act_type_last_upd_inits

Y

Char

3

Keyed in by system administrator

WORKREQ_ACTIVITY_STATUS_LOOKUP

Workreq_act_status

PK

Y

Char

20

Activity: Unassigned, Assigned, In Progress, Complete, Cancelled.

Workreq: Submitted, Received, Approved, In Progress, Complete, Cancelled.

Workreq_act_status_valid_for_cd

Y

Char

1

A=Valid for an Activity

W=Valid for a Work Request

Workreq_act_status_last_upd_ts

Y

Char

17

From System date/time

Workreq_act_status_last_upd_inits

Y

Char

3

Keyed in by system administrator

WORKREQ_REASON_LOOKUP

Workreq_reason

PK

Y

Char

125

Significant potential cost savings, Efficiency-Automate current manual process, Enhancement, Regulatory Request, Correction to Existing Program, Other

Workreq_reason_last_upd_ts

Y

Char

17

From System date/time

Workreq_reason_last_upd_inits

Y

Char

3

Keyed in by system administrator