OHIO BOARD OF REGENTS
 

Edit and Load Specifications

Faculty and Instructional Non-faculty Demographics (FD) File
Revised November 2, 2000

Edit Rules for Each Faculty and Instructional Non-faculty Demographics (FD) Record

A.  If there are non-blank characters beyond the allotted record length for this file
write Error 035
B.  If the record is blank, do not run edit checks for this file on this record
write Error 011 on edit message file
C.  If the Faculty Identifier is blank in the first character position
write Error 300 on edit message file
D.  If Institution Assigned Identifier Switch is Y and the Faculty 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 message file
E.  If Institution Assigned Identifier Switch is N and the Faculty 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 message file
F. If Institution Assigned Identifier Switch is not Y and not N
write Error 022 on edit message file
G. If Institution Assigned Identifier Switch is N and Faculty Identifier is not numeric
write Error 021 on edit message file
H.  If Appointment Status and submission year do not match an Appointment Status code and Year in the Appointment Status table and 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 message file
I. If Faculty Rank and submission year do not match a Faculty Rank code and year in the Faculty Rank table
write Error 321 on edit message file
J.  If Error 311 and Error 321 did not occur, and the combination of Appointment Status and Faculty Rank is not in the Appointment Status Faculty Rank table
write Error 322 on edit message file 
K.  If Tenure Status is blank
write Error 315 on edit message file
L. If Tenure Status and submission year do not match a Faculty Tenure code and year in the Faculty Tenure table 
write Error 316 on edit message file
M.  If Error 316 did not occur and the combination of Tenure Status and Institution Code is not in the Faculty/Institution Tenure table for this institution and year
write Error 317 on edit message file
N.  If Work Category code and submission year do not match a Work Category code and year in the Work Category table 
write Error 386 on edit message file
O.  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 message file
P.  If Total Salary is not numeric
write Error 327 edit message file
Q.  If Annualized Salary is NA AND Appointment Status is not GA or PT
write Error 917 edit message file
S.  If Annualized Salary is not NA or not numeric
write Error 915 edit message file
T.  If Total Fringe Benefits is not numeric
write Error 328 edit message file
U. If Highest Degree Earned and submission year do not match a Highest Degree Earned code and year in the Faculty Degree table 
write Error 326 on edit message file
V.  If Year of Birth is not numeric in each character position and not UNKN
write Error 025 on edit message file
W.  If Year of Birth is numeric and (current year minus Year of Birth is less than 20 or more than 100)
write Warning A01 on edit message file
X.  If Sex code and submission year and spring term do not match a Sex code and year in the Sex table 
write Error 310 on edit message file
Y. If Racial/Ethnic code, submission year, and spring term do not match a Racial/Ethnic code, year, and term in the Race/Ethnicity table 
write Error 026 on edit message file
Z.  If Delete Switch is not Y and not N
write Error 013 on edit message file
AA. If the same Faculty Identifier and value in the Delete Switch occur in more than one record in the Faculty Demographics submission file
write Error 335 on edit message file
AB. If the Delete Switch is N and there is a row in the Faculty Demographics table with the same Faculty Identifier (Regents ID), Main Campus or Institution Code, and Year, and if there is not a record in this submission file for the same Faculty Identifier with the Delete Switch set to Y
write Error 336 on edit message file
AC. If the Delete Switch is Y and there is not a row in the Faculty Demographics table with the same Faculty Identifier (Regents ID), Institution Code, and Year
write Error 337 on edit message file
AD.  If Error 337 did not occur, and if the Delete Switch is Y and there is not a row in the Faculty Demographics table with the same Faculty Identifier (Regents ID), Institution Code, Year, Appointment Status, Tenure Status, Faculty Rank, Work Category, Total Salary, Total Fringe Benefits, Highest Degree Earned, Year of Birth, Sex, and Race/Ethnicity
write Error 338 on edit message file
AE. If faculty identifier is in the Course Sections Taught table for campuses of this institution, for any term within the fiscal year, and if the Faculty Identifier is not in the Faculty Generic Identifier table for any term within the fiscal year, and the Faculty Identifier is neither in the Faculty Demographics table for this institution and fiscal year nor in this Faculty Demographics submission file with a delete switch of N
write Warning A83 on edit message file
AF.

This edit verifies whether the SSN meets the rules for issuing SSN's by the Social Security Administration. The rules can be found at http://www.ssa.gov/employer/stateweb.htm and http://www.ssa.gov/employer/ssnweb.htm.

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
AG. If Appointment Status is GA or PT and Annualized Base Salary is numeric
Write Warning B89 on edit report file

 Table Build

A. Faculty Demographics table

If the Delete Switch is Y

delete the corresponding row from the Faculty Demographics table


If the Delete Switch is N

add a row to the Faculty Demographics 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 Faculty Demographics table, the All Employee 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.)
Load faculty demographics table as follows:
 
Field Name Column Name
Employee Identifier OBRID (OBRID assigned to employee identifier)
Appointment Status appt code
Tenure Status faculty tenure code
Faculty Rank faculty rank code
Major Fund Group major fund group code
Work Category work category code
Total Salary (I&G plus non-I&G) total salary
Total Fringe Benefits (I&G plus non-I&G) total benefits
Annualized Base Salary annual salary
Highest Degree Earned faculty highest degree code
Year of Birth faculty birth year
Sex sex code
Race/Ethnicity race ethnic code
 

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 all the FD files submitted by this institution for this term.
 

Submission Records

The statistics in the following table reflect the number of records reported in this Faculty and Instructional Non-faculty Demographics (FD) submission file and those already loaded to the database (faculty demographics 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:

Total Number of Records on Database after Load:

Sum the number of FD submission file records with a Delete Switch of N, plus the number of FD records already loaded to the database from previous FD file submissions, minus the number of records that will be deleted from the faculty demographics 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 FD records already loaded to the database from previous FD submissions for this institution and year.

Number of Records Added by this Submission:

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

Number of Records Deleted by this Submission:

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

Employee Identifiers

The purpose of this table is to display the number of employees submitted in the Faculty and Instructional Non-faculty Demographics (FD) files relative to the type of employee identifier (SSN or institution assigned). The statistics reflect the number of employee identifiers reported in this FD submission file and those already loaded to the database (faculty demographics table) for the institution in this year.
 
Number of records with SSNs Number of records with Institution Assigned Identifiers
Total
     

Description of Variables in Table:

Number of records with SSNs:

Sum the number of FD records with Faculty Identifiers that have an Institution Assigned Identifier Switch set to N, for the institution.

Number of records with Institution Assigned Identifiers:

Sum the number of FD records with Faculty Identifiers that have an Institution Assigned Identifier Switch set to Y, for the institution.

Total:

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

Faculty Rank

The purpose of this table is to display the distribution of faculty by Faculty Rank for the institution. The statistics reflect the number of unique employee identifiers reported in the Faculty and Instructional Non-faculty Demographics (FD) submission files and contained on the database for the institution in this year.

Faculty Rank Codes
 
  PR AP AS IN LE OT NR UK Total
                   

Description of Variables in Table:

Faculty Rank:

Sum the number of occurrences of each Faculty Rank code for the institution.

Total:

Sum the values for each Faculty Rank code in the columns to the left for the institution.
 

Work Category and Appointment Status

The statistics in the following table reflect the number of occurrences of each Work Category code and Appointment Status code submitted in all the Faculty and Instructional Non-faculty Demographics (FD) files for this year

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

Description of Variables in Table:

Work Category:

Sum the number of occurrences of each Work Category code for the institution.

Work Category Totals:

Sum the values for each Work Category code in the rows above for the institution.

Appointment Status:

Sum the number of occurrences of each Appointment Status code for the institution.

Appointment Status Totals:

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

Tenure and Appointment Status

The statistics in the following table reflect the number of occurrences of each Tenure code and Appointment Status code submitted in all the Faculty and Instructional Non-faculty Demographics (FD) files for this year.

Tenure Status Codes
 
Appointment Status Codes TT NT TE Appointment Status Totals
'09'        
'11'        
'PT'        
'GA'        
Tenure Status Totals        

Description of Variables in Table:

Tenure Status:

Sum the number of occurrences for each Tenure Status code for this institution.

Tenure Status Totals:

Sum the values for each Tenure Status code in the rows above for this institution.

Appointment Status:

Sum the number of occurrences of each Appointment Status code for this institution.

Appointment Status Totals:

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

Average Salary by Work Category

The statistics in the following table reflect the average salary for each Work Category for this year for the institution. The cell values reflect only those records reported in the Faculty Demographics (FD) submission files and contained on the database where the Pay Type is S and the Appointment Status is 09 or 11.

Work Category Codes
 
  EA
FA
IR
OP
TP
CS
SC
SM
Total Average Salary
Average Salary                  

Description of Variables in Table:

Average Salary:

Calculate the annualized average salary for each Work Category code for the institution by summing the annualized salaries for each work category and dividing by the number of records for each work category for the institution. Note: Ignore all records that result in NA being associated with annualized salary field.

Institution Average Salary:

Calculate the annualized average salary from the columns to the left for the institution by summing the annualized salaries for the entire institution and dividing by the number of records for the institution. Note: Ignore all records that result in NA being associated with annualized salary field.
 

Highest Degree Earned

The purpose of this table is to display the distribution of Highest Degree Earned Codes by Faculty Rank. The statistics reflect the number of unique employee identifiers reported in the Faculty and Instructional Non-faculty Demographics (FD) submission files and contained on the database for this institution in this year.

Highest Degree Earned Codes
 
  C A B M E
D
P
N
 Total
PR                  
AP                  
AS                  
IN                  
LE                  
OT                  
NR                  
UK                  
Total                  

Description of Variables in Table:

Faculty Rank:

Sum the number of occurrences of each Highest Degree Earned code for the institution.

Total--Highest Degree:

Sum the values for each Highest Degree Earned code in the rows above for the institution.

Total--Faculty Rank:

Sum the values for each Faculty Rank code in the columns to the left for the institution
 

Sex and Race/Ethnicity

The statistics in the following table reflect the number of occurrences of each Racial/Ethnic code and Sex code submitted in all Faculty and Instructional Non-faculty Demographics (FD) files for this year for the institution.

Racial/Ethnic Codes
 
Sex Codes
 BL
AI
AS
HS
WH
NR
UK
Total Sex Codes
F                
M                
N                
Total Racial/Ethnic Codes                

Description of Variables in Table:

Racial/Ethnic Codes:

Sum the number of occurrences of each combination of Racial/Ethnic code and Sex Code for the institution.

Sex Codes:

Sum the number of occurrences of each combination of Sex code and Racial/Ethnic code for the institution.

Total Racial/Ethnic Codes:

Sum the values for each Racial/Ethnic code in the rows above for the institution.

Total Sex Codes:

Sum the values for each Sex code in the columns to the left for the institution.
 

Report of Errors and Warnings
 
Edit Code
Edit Type
Edit Text
Display Field
011 E Blank records not allowed Blank Records
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
025 E The Year of Birth is not numeric or UNKN Year of Birth
026   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
300 E The Faculty Identifier is blank or not left justified Faculty Identifier
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
310 E The Sex code is not in the Sex Code table Sex 
311 E The Appointment Status is not in the Appointment Status table Appointment Status
315 E The Tenure Status is not provided Tenure Status
316 E The Tenure Status is not in the Faculty Tenure table Tenure Status
317 E Institutions which do not grant tenure must report NT for Tenure Status Tenure Status
321 E The Faculty Rank is not in the Faculty Rank table Faculty Rank
322 E The combination of Appointment Status and Faculty Rank is not in the Appointment Status Faculty Rank table Appointment Status Faculty Rank
326 E The Highest Degree Earned is not in the Faculty Degree table Highest Degree Earned
327 E The Total Salary is not numeric Total Salary
328 E The Total Fringe Benefits is not numeric Total Fringe Benefits
335 E There is more than one record for the individual in the Faculty Demographics submission file Submission File
336 E The individual already has a row in the Faculty Demographics table Faculty Identifier
337 E There is not a row for the individual in the Faculty Demographics table Faculty Identifier
338 E There is not a row with the same values for the individual in the Faculty Demographics table Faculty Identifier
386 E The Work Category code is not in the Work Category table Work Category
917 E Annualized Salary is required for all records with appointment status of Full-time 09 or Full-time Annualized Salary
915 E Annualized Salary is either not NA or is not numeric Annualized Salary
A01 W The Year of Birth indicates that the individual is less than 20 years of age or more than 100 years of age Year of Birth
A05 W The Instruction/Research Assistant was not reported as a part-time employee or GA Appointment Status Work Category
A83 W The Faculty ID in the Course Sections Taught table is not in either the Faculty Demographics table or in the FD file submission Faculty Identifier
B89 W NA should be submitted for Annualized Base Salary for a GA or PT record. Next year this will result in an error. Salary/Wages or Annualized Salary

Return to Faculty-Staff Data Area

http://regents.ohio.gov/test/don/fdedit.html
Last updated January 18, 2005