OHIO BOARD OF REGENTS

Edit Rules for Each All Employee (AM) Record

Note: The record identifier for the All Employee (AM) file consists of Campus, Year, and Employee Identifier
 

A.	If there are non-blank characters beyond the allotted record length for this file  write Error 035 on edit report file
B.	If the record is blank, do not run edit checks for this file on this record, and  write Error 011 on edit report file
C.	If the Campus code is not in the Campus Code Column of the Institution/Campus Codes table, or the Campus code is SSCC or CYCC  write Error 001 on edit report file
D.	If Campus code does not correspond to login Institution code  write Error 040 on edit report file
E.	If Employee Identifier is blank in first character position  write Error 379 on edit report file
F.	If the submission file contains more than one record with the same Employee Identifier and the Institution Assigned Identifier Switch is not the same (Y or N) for those Employee Identifiers  write Error 303 on edit report file
G.	If Institution Assigned Identifier Switch is Y and the Employee Identifier is in the Institution SSN table for this institution and the table row has the Institution Assigned Switch as N write Error 301 on edit report file
H.	If Institution Assigned Identifier Switch is N and the Employee Identifier is in the Institution SSN table for this institution and the table row has the Institution Assigned Switch as Y write Error 302 on edit report file
I.	If Institution Assigned Identifier Switch is not Y and not N  write Error 022 on edit report file
J.	If Institution Assigned Identifier Switch is N and Employee Identifier is not numeric write Error 021 on edit report file
K.	If Major Fund Group code is not in the Major Fund Group table for the year indicated in the header record  write Error 381 on edit report file
L.	If Work Category code is not in the Work Category table for the year indicated in the header record write Error 386 on edit report file
M.	If Appointment Status and submission year do not match an Appointment Status code and Year in the Appointment Status table or the Appointment Status code and submission year for this submission do not match an Appointment Status code, year, and submission code in the Appointment Status Exceptions table  write Error 311 on edit report file
N.	If Error 311 and Error 386 did not occur, and the combination of Work Category and Appointment Status is not in the Appointment Status Work Category table  write Warning A05 on edit report file
O.	If Pay Type is not in the Pay Type Codes table for the year indicated in the header record write Error 390 on edit report file
P.	If Salary/Wages is blank OR is not NA or is not numeric  write Error 391 on edit report file
Q.	If Salary/Wages is NA AND Pay Type is S and Appointment Status is not GA or PT and Work Category is not FA write Error 916 on edit report file
R.	If Salary/Wages is numeric and is more than 7500 and Pay Type is W  write Warning A06 on edit report file
S.	If Salary/Wages is numeric and is less than 100000 and the Appointment Status is 09 or 11 and Pay Type is S  write Warning A07 on edit report file
T.	If Sex Code is not in Sex Codes table  write Error 310 on edit report file
U.	If Annualized Salary is NA and Pay Type is S and Appointment Status is not GA or PT and Work Category is not FA write Error 914
W.	If Annualized Salary is not NA or not numeric write Error 915
X.	If Racial/Ethnic code is not blank and not in the Racial/Ethnic Codes table (and set to AU term and for header record year minus one)  write Error 026 on edit report file
Y.	If the Delete Switch is not Y and not N  write Error 013 on edit report file
Z.	If the Delete Switch is Y and there is not a row in the All Employee table with the same Employee Identifier (Regents ID), Year, and Campus  write Error 394 on edit report file
AA.	If Error 394 did not occur, and if the Delete Switch is Y and there is not a row in the All Employee table with the same Campus Code, Employee Identifier (Regents ID), Institution Assigned Switch, Major Fund Group, Work Category, Appointment Status, Pay Type, Salary/Wages, Sex, Race/Ethnicity, and Year  write Error 395 on edit report file
AB.	If the Delete Switch is N and there is a row in the All Employee table with the same Employee Identifier (Regents ID), Campus Code, and All Employee Year, and if there is not a record in this submission file for the same Employee Identifier with the Delete Switch set to Y  write Error 396 on edit report file
AC.	If the same Employee Identifier, Campus Code, and value in the Delete Switch occur in more than one record in the All Employee submission file  write Error 398 on edit report file
AD.	If the Institution Assigned Faculty Identifier Switch is N and the Delete Switch is N and the Faculty Identifier is numeric in each character position and the first three digits, or the fourth and fifth, or the sixth through ninth digits of the Faculty Identifier are zeros,  Or If the Institution Assigned Faculty Identifier Switch is N and the Delete Switch is N and the Faculty Identifier is numeric in each character position and the first three digits are 772 or above.  write Error 018 on edit report file
AE.	If Pay Type is S and Appointment Status is GA or PT and Salary/Wages is numeric or Annualized Base Salary is numeric write Warning B88 on edit report file
AF.	If Pay Type is W and Salary/Wages is NA and Appointment Status is not GA or PT write Error 918 on edit report file
AG.	If Salary/Wages is 000000 and Pay Type is W write Warning B90 on edit report file

Table Build
 

A.	All Employee table  If the Delete Switch is Y  delete the row from the All Employee table with the same Campus, Employee Identifier, Institution Assigned Identifier Switch, Major Fund Group, Work Category Appointment Status, Pay Type, Salary/Wages, Sex, and Race Ethnicity If the Delete Switch is N, and there is not a row with the same Regents ID, Year, and Campus add a row to the All Employee table with data from the submission record
B.	Institution SSN table  If the Delete Switch is Y, and there is no combination of Regents ID and Institution in the All Employee table, the Faculty Demographics table, the Course Sections Taught table, the Student Entrance table, and the Doctoral Caps Exemption table delete the row with this Regents ID and Institution from the Institution/SSN table  If the Delete Switch is N utilize the standard logic for assigning Regents IDs.
C.	When loading data, follow the following exceptions: If, in the submission file, appointment status is PT or GA, load NA for annual_salary column. (Rationale: We do not want to have annualized salary data for part-timers because we consider this data meaningless. We are allowing institutions to report it, because some find it useful, however, we are not loading that field.) If, in the submission file, appointment status is PT or GA AND Pay Type is S, load NA for all_emp_salary column. (Rationale: We do not want to have annualized salary data for part-timers because we consider this data meaningless. We are allowing institutions to report it, because some find it useful, however, we are not loading that field.) If, in the submission file, Work Category is FA, load NA for annual_salary column and load NA for all_emp_salary column (Rationale: We do not want to have faculty salary data in this file, it is not meaningful from a snapshot basis. Faculty salary data will be collected in the FD).

 
 

Summary Edits

Note: The summary edits are performed only after the primary edits have yielded zero errors for all records in a file submission. The scope of the summary edit statistics consists of the net effect of this file plus those AM files previously loaded by this institution for this year.

Summary Edit Results for

[File Name and Abbreviation] (ex. All Employee (AM) File)

[Institution Name]

[Term or Annual] (ex. Annual) (ex. Spring Term)

[Date] at [Time]

Submission Records

The statistics in the following table reflect the number of records reported in this All Employee (AM) submission file and those already loaded to the database (All Employee table) for the institution and year.
 

Total Number of Records on Database after Load
Number of Records on the Database before Load
Number of Records Added by this Submission
Number of Records Deleted by this Submission

Description of Variables in Table:

Sum the number of AM submission file records with a Delete Switch of N, plus the number of AM records already loaded to the database from previous AM file submissions, minus the number of records that will be deleted from the All Employee table by records with a Delete Switch of Y for this institution and year.

Number of Records on the Database before Load:

Sum the number of AM records already loaded to the database from previous AM submissions for this institution and year.

Number of Records Added by this Submission:

Sum the number of AM records with a Delete Switch of N in this submission.

Number of Records Deleted by this Submission:

Sum the number of AM records with a Delete Switch of Y in this submission.
 
 
 

Employee Identifiers, by Campus

Description of Variables in Table:

This is a four-character campus code associated with each campus of an institution for which data exists. The values across each row should reflect only data associated with the campus code in the row.

Number of records with SSNs:

Sum the number of AM records with Employee Identifiers that have an Institution Assigned Identifier Switch set to N, for each campus.

Number of records with Institution Assigned Identifiers:

Sum the number of AM records with Employee Identifiers that have an Institution Assigned Identifier Switch set to Y, for each campus.

Campus Total:

Sum the two columns to the left for each campus (i.e., the total number of employees reported for the campus).

Total-All Campuses, Non-distinct IDs:

Total from the database for all campuses associated with the institution, based on the presence of data in the rows above. The total value in the right-most cell equals the Total Number of Records on Database after Load in the Submission Records table.

Total-All Campuses, Distinct IDs:

Total Employee Identifiers (SSNs and institution-assigned identifiers) from the database for all campuses associated with the institution, based on the presence of data in the campuses' rows above. Count the distinct IDs at the institution.

Multiple Campus Assignments:

The number of different Employee Identifiers (SSNs and institution-assigned identifiers) that occur on more than one campus.
 

Major Fund Group, by Campus

Major Fund Group Codes
 

Campus Code	DI	AS	SS	PO	IS	SB	PS	AU	HO	Total
Total-All Campuses

Description of Variables in Table:

This is a four-character campus code associated with each campus of an institution for which data exist. The values across each row should reflect only data associated with the row campus code.

Major Fund Group:

Sum the number of occurrences of each Major Fund Group code for each campus.

Campus Total:

Sum the values for each Major Fund Group code in the columns to the left for each campus.

Total-All Campuses:

Sum each column for all campuses associated with the institution, based on the presence of data in the rows above.
 

Work Category and Appointment Status, by Campus

[Campus Code here] Work Category Codes
 

Appointment Status Codes	EA	FA	IR	OP	TP	CS	SC	SM	Campus Totals by Appointment Status Code
09
11
PT
GA
Campus Totals by Work Category Codes

Description of Variables in Tables:

This is a four-character campus code associated with each campus of an institution for which data exists.

Matrix of Work Category Codes by Appointment Status Codes:

The value in each cell represents the number of occurrences of each combination of Work Category Code and Appointment Status Code for each campus.

Campus Totals by Work Category Codes:

Sum the values for each Work Category Code in the rows above for each campus. The value in the right cell represents the total number of records for all Work Category Codes and Appointment Status Codes combined, for each campus.

Totals by Appointment Status Code:

Sum the values for each Appointment Status code in the columns to the left for each campus.

Total-All Campuses:

Sum each column of the Campus Totals by Work Category Codes associated with the institution, based on the presence of data in the rows above.
 

Average Salary by Work Category and Appointment Status, by Campus

Campus Code Work Category Codes
 

Appointment Status Codes	EA	FA	IR	OP	TP	CS	SC	SM	Campus Average Salary by Appointment Status Codes
09
11
Campus Average Salary by Work Category Codes

Description of Variables in Table:

This is a four-character campus code associated with each campus of an institution for which data exist. The values across each row should reflect only data associated with the campus code.

Matrix of Work Category Codes by Appointment Status Codes:

The value in each cell represents the average salary for each combination of Work Category Code and Appointment Status Code for the specific campus. This value is calculated by summing the annualized salaries for each combination of Work Category Code and Appointment Status Code, then dividing by the number of records within each combination. Note: Ignore all records that result in NA being associated with annualized salary field.
 
 

Campus Average Salary by Work Category Codes:

Sum the salaries and divide by the number of records for each Work Category Code for all campuses. The value in the right-most cell is the Campus Average Salary (all Work Category Codes and Appointment Status Codes combined).

Campus Average Salary by Appointment Status Codes:

Sum the salaries and divide by the number of records for each Appointment Status Code for all campuses. The value in the lower right cell is the Campus Average Salary (all Work Category Codes and Appointment Status Codes combined).
 

Average Hourly Wage by Work Category and Appointment Status, by Campus

Campus Code Work Category Codes
 

Appointment Status Codes	EA	FA	IR	OP	TP	CS	SC	SM	Campus Average Hourly Wage by Appointment Status Codes
09
11
PT
GA
Campus Average Hourly Wage by Work Category Codes

Description of Variables in Table:

This is a four-character campus code associated with each campus of an institution for which data exist. The values across each row should reflect only data associated with the campus code.

Matrix of Work Category Codes by Appointment Status Codes:

The value in each cell represents the average hourly wage for each combination of Work Category Code and Appointment Status Code for the specific campus. This value is calculated by summing the hourly wages for each combination of Work Category Code and Appointment Status Code, then dividing by the number of records within each combination.

Campus Average Hourly Wage by Work Category Codes:

Sum the hourly wages and divide by the number of records for each Work Category Code for all campuses. The value in the right-most cell is the Campus Average Hourly Wage (all Work Category Codes and Appointment Status Codes combined).

Campus Average Hourly Wage by Appointment Status Codes:

Sum the salaries and divide by the number of records for each Appointment Status Code for all campuses. The value in the lower right cell is the Campus Average Hourly Wage (all Work Category Codes and Appointment Status Codes combined).
 
 

Sex and Race/Ethnicity, by Campus

Racial/Ethnic Codes
 

Campus Code	BL	AI	AS	HS	WH	NR	UK	Campus Total by Sex Codes
Female
Male
Unreported
Campus Total by Racial/Ethnic Codes

Racial/Ethnic Codes
 

	BL	AI	AS	HS	WH	NR	UK	Total by Sex Codes- All Campuses
Female
Male
Unreported
Total by Racial/Ethnic Codes-All Campuses

Description of Variables in Tables:

This is a four-character campus code associated with each campus of an institution for which data exist. The values in each column should reflect only data associated with the specific campus code.

Matrix of Racial/Ethnic Codes by Sex Codes:

The value in each cell represents the number of occurrences of each combination of Sex Code and Racial/Ethnic Code for each campus.

Campus Total by Sex Codes:

Sum the values for each Racial/Ethnic code by Sex Code in the columns for each campus. This value equals the sum of the number of occurrences of each Sex Code for each campus.

Campus Total by Racial/Ethnic Codes:

Sum the values for each Sex Code by Racial/Ethnic Code in the columns for each campus. This value equals the sum of the number of occurrences of each Racial/Ethnic Code for each campus.

Total by Racial/Ethnic Codes -All Campuses:

Sum each column for all campuses of the institution, based on the presence of data in the rows above.

Total, by Sex Codes-All Campuses:

Sum each row for all campuses of the institution, based on the presence of data in the columns to the left.

Load all_emp table as follows:
 
 
 

Field Name

Column Name

Campus	campus code
Employee Identifier	OBRID (OBRID assigned to employee identifier)
Major Fund Group	major fund group code
Work Category	work category code
Appointment Status	appt code
Pay Type	pay type code
Salary/Wages	all emp salary
Annualized Base Salary	annual salary
Sex	sex code
Race/Ethnicity	race ethnic code

 
 
 

Report of Errors and Warnings
 

Edit Code	Edit Type	Edit Text	Display Field
001	E	The Campus code is not in the Institution/Campus Codes table	Campus
011	E	Blank records not allowed	General
013	E	The Delete Switch is not Y or N	Delete Switch
021	E	The Social Security Number is not numeric	Employee Identifier
022	E	The Institution Assigned Identifier Switch is not Y or N	Institution Assigned ID Switch
026	E	The Racial/Ethnic code is not in the Racial/Ethnic Codes table	Race/Ethnicity
035	E	There are non-blank characters beyond the allotted record length for this file	20 characters beyond allotted length of record
040	E	The Campus code in the input record does not correspond to the login Institution code	Campus
301	E	This Institution Assigned Identifier is the same as a SSN in the Institution SSN table	Institution Assigned ID Switch
302	E	This SSN is the same as an Institution Assigned Identifier in the Institution SSN table	Institution Assigned ID Switch
303	E	An Employee Identifier cannot have Institution Assigned Identifier switch values of both Y and N	Employee Identifier/Inst Assigned Identifier Switch
310	E	The Sex code is blank or not in the Sex Code table	Sex
311	E	The Appointment Status is not in the Appointment Status table	Appointment Status
379	E	The Employee Identifier is blank or not left justified	Employee Identifier
381	E	The Major Fund Group code is not in the Major Fund Group table	Major Fund Group
386	E	The Work Category code is not in the Work Category table	Work Category
390	E	The Pay Type is not in the Pay Type Codes table	Pay Type
391	E	The Contract Salary or Wage amount is blank or not numeric	Salary/Wages
394	E	There is not a row for the individual in the All Employee table	Employee Identifier
395	E	There is not a row with the same values for the individual in the All Employee table	Employee Identifier
396	E	There is a row for this individual in the All Employee table with the same campus although the Delete Switch is N	Employee Identifier
398	E	The Identifier and Delete Switch setting occur in more than one record in the All Employee submission file	Employee Identifier
914	E	Annualized Salary is required when Salary/Wages field is reported as S	Annualized Salary
915	E	Annualized Salary is either not NA or is not numeric	Annualized Salary
A05	W	The Instruction/Research Assistant was reported as a full time employee	Work Category /Appointment Status
A06	W	The reported wage is more than $75 per hour	Salary/Wages
A07	W	The reported contract salary is less than $1000	Salary/Wages
B88	W	Next year it will be an error to enter Appointment Status as PT or GA and Pay Type as Salary AND a salary or annualized salary.	Salary/Wages or Annualized Salary
B90	W	000000 was entered in salary/wages for an employee with Pay Type W. Please enter Wages if employee was paid.	Salary/Wages
918	E	NA is not a valid entry for salary/wages when employee is entered with Pay Type W and is not Work Category PT or GA	Salary/Wages

Return to Faculty-Staff Data Area

http://regents.state.oh.us/hei/faculty/amedit.html
Last updated March 9, 2000