Home   >   Training   >   Application Administrator   >   Faculty Data Import

Faculty Data Import

This article provides an overview of how to import faculty data into UC OATS. The information provided for faculty will drive the following system functionality: 1) general identifying information, 2) policies applied, 3) routing and approval workflow, 4) threshold calculated (both effort and earnings). The faculty data can be updated as often as needed using the UC OATS data import feature or by editing the faculty member's individual record. 

Warning: 
  • The faculty data file you upload must include all faculty appointments.
  • Faculty data uploads wipe out all current faculty data and any manual changes that were made to a faculty's record in UC OATS. 
  • Faculty members cannot have two appointments within the same department. For example, if a faculty member is also a dean for the department, you would change the faculty type to a dean appointment (240 in 025 Context or 240 HSCP Context 671). 

Data Preparation

To prepare for the faculty data import:

  1. Download the Faculty Data Elements Template.
    Please note:
    • The data sheets generated are multi-record data sets, not flat files. Multiple rows of data can apply to a single faculty member.
    • Multiple faculty records are associated with an individual by using the employee ID number.
    • All columns, not marked as deprecated, are required in the file. The value for the column can be empty if it is designated as “Required=N”.
    • Uploads must include the FULL faculty record (including all appointments and salary data for 671 faculty) every time. An incomplete faculty record will override the existing record (for example, making a faculty appear to have a partial appointment instead of a joint appointment). 
  2. Review the faculty data elements descriptions below and complete the Faculty Data Elements Template. Once the file is complete, save it as a "Text (Tab delimited) (*.txt) file or CSV (Comma delimited) (*.csv).

Faculty Data Elements Description: (Note, Scroll to the right to see the full table)

Data Element Name Description Max size Required (Y/N) Accepted Format Example Note UCPath_Entity

UCPath_Attribute

employee_id

A unique ID number that does not change over the life time of a faculty person’s association with a UC campus. This ID will serve as a unique identifier in UC OATS to identify the user and associate incoming data.

 50 characters N

Alphanumeric

123456789

At UCLA this variable is called the UID. 

 PS_UC_EXT_SYSTEM

UC_EXT_SYSTEM_ID

emp_lname

Last name of faculty member.

50

characters

 Y Text; spaces, hyphens, and apostrophes accepted

Brown
Brown Smith 
Brown-Smith

 

 PS_NAMES

LAST_NAME

emp_fname

First name of faculty member.

50

characters

 Y

Text; spaces, hyphens, and apostrophes accepted

Mary
Mary Ann
Mary-Ann

   PS_NAMES

FIRST_NAME

emp_mname

Middle name of faculty member

 50 characters N

Text; spaces, hyphens, and apostrophes accepted

John
John Joseph
John-Joseph

 

 PS_NAMES

MIDDLE_NAME

dept_code

The department code for the department with which the faculty is associated. This field dictates the thresholds for which a faculty is subject. For the 671 faculty, this department code should be the department associated with the faculty’s comp plan. When no division is provided, the department code will dictate the routing and approval of forms.

 30 character Y Alphanumeric
 

1400

If a person has multiple department associations, a new record must be present for each department for that faculty.  

 PS_JOB

DEPTID
AND
JOB_INDICATOR

level_1_review
_code

The unit (department, division, etc.) code with which a faculty is associated. This field dictates the routing and approval workflow for the initial review stage for each form.

 30 characters N Alphanumeric

1400

The level 1 review code could be a division within the department with which the faculty is associated. This field is used to dictate the routing and approval workflow for this faculty relative to their division. This field does not affect the thresholds for faculty. If no data source exists for divisions, campus will have to provide a distinguishing ID 

The level 1 review code could also be the department code if review should begin at the department level. In this case, the value would be identical to dept_code or NULL.

 N/A; Locally provided  

percent_
appointment

 This is the percent appointment of this faculty member to the departmental unit referenced by dept_code in this record. If a faculty is only associated with one unit (department) and is working full-time then this value will be 100. If a faculty has multiple department/unit appointments, then this parameter will have a value greater than  0 and less than 100, and there will be multiple records in this data set for the same faculty member.   However the sum of percent appointments among department codes for the same faculty cannot be greater than 100. 3 digits Y Integer value greater than zero but less or equal to 100

1
33
45
100

If a faculty appointment to dept_code 1 is 75% and to dept_code 2 is 25, then dept_code 1 has the value 75 and dept_code 2 has the value 25.

The percent appointments do not have to equal 100 if faculty is part-time, or there are additional title codes that are not subject to policy.

 PS_JOB

FTE

primary_
appointment_p

If a faculty member has multiple departmental appointments, this parameter will indicate which department is the faculty’s primary appointment where 1 = Yes and 0 = No. Faculty with a singular departmental appointment should be set to 1. Faculty must have only one primary department  appointment.  This field will determine the routing and approval workflow for the faculty.

 1 digit Y

Integer value, 1 = yes and 0 = no

1
0

A faculty has a joint appointment with the Department of Biology, Chemistry, and Physics. If the primary appointment is Biology, then the line corresponding to department code of Biology will be 1 while the rest of the departments will be 0.

This field dictates the workflow and thus where final approval for certain forms will occur at the department level.

 PS_JOB

JOB_INDICATOR

base_salary
(671 ONLY)

 For faculty participating in a compensation plan governed by APM 671, a base salary must be provided in the format 100000. Commas will be removed and decimals will be rounded up to an integer.   The earnings threshold for outside activity is scaled from this value.
For 025 faculty, leave this value as empty. 		 15 decimal digits N

Do include “$” in value. Integer value will be rounded up to an integer.

 100000
82599

The system is currently using Scale 0 to calculate earning thresholds.

Base salary parameters should be updated on an annual basis to account for salary changes. Data feeds may be updated concurrent with fiscal year start (or retroactively applied). 

 PS_JOB

ANNUAL_RT

academic_term

This is an indicator to track terms for faculty. APM 671: F = fiscal, APM 025: A = academic, F = fiscal. Only the summer indicator will affect system functionality.

 1 character Y A = academic
F = fiscal
Case insensitive		 A or a
F or f
 

Required:  a null value will result in record not being processed.

 N/A; Locally provided  

emp_email

University email address used by the faculty. Faculty emails are used for automated emails from UC OATS. This is limited to one email address per faculty member.

 100 characters Y Email address user@
campus.edu
 

Limited to one email per faculty

 PS_EMAIL_ADDRESSES

EMAIL_ADDR

CC_policy

 The conflict of commitment policy governing this faculty member. Acceptable formats: 
025
671
240.5 (Dean, General Campus)
240.1 (Dean, Health Sciences)
246.5 (Faculty Admin, General Campus)
246.1 (Faculty Admin, Health Sciences)
Other data formats will not be accepted.		 5 characters Y One of:
025
671
240.5
240.1
246.5
246.1
 

For a faculty administrator under the Health Sciences comp plan, value should be 246.1

In general, campuses can generate the information for this field based on title code.

This data can be manipulated directly through the UC OATS administrative console if needed. In rare instances a campus might wish to include certain title codes under a CC policy. 

 N/A; Locally provided  

UCpath_
employee_id

UCPATH employee ID when available. This will replace the employee_id field when possible.

 500 characters Y Numeric 12345678   PS_UC_EXT_SYSTEM

EMPLID

Import Faculty Data

To upload faculty data:

  1. Click on the "App Admin" button to open the Application Administrator workspace.
    Application Administrator workspace page
  2. Make sure you are in the correct fiscal year. You can move forward and back through fiscal years by clicking the arrows.
  3. Select the "Data Import" tab and open the "Faculty Data Import" section.
  4. Click on the "Upload Faculty Ingest" button.
  5. Click the "Choose File" button and locate your data file. Then click "Upload".
    uploading a file
  6. The system will process your file and one of the following statuses will appear.
    • Invalid - There is an issue with the file. (1) The file is not in "Text (Tab delimited) (*.txt) file or CSV (Comma delimited) (*.csv). format, (2) there is no data in the file, (3) file columns have been removed.
    • Valid and processed / Complete Successful- The file was successfully uploaded and processed. The changes will be reflected in UC OATS.
    • Complete with Exceptions - The system was unable to process some of the records. To resolve data errors follow steps 7-10 to fix the issue.
  7. Click on the file name to view the "Ingest File Details" log.
    • The "Records" tab will show the records that were processed.
    • The Error Log will show any records that were not processed, along with the reason why.
  8. If the file status is "Complete with Exception", click the "Download" button to download the data set that did not get processed.
    faculty ingest file details
  9. Open the downloaded data file in Excel. The file will show only records with errors. The reason for the errors will be in the errors column. 
    example of data exported to a spreadsheet
  10. Fix the errors, upload the data file.

 

 

 

Last Modified: 2023-01-27 08:23:17.119264-08

Copyright ©