Office of the Chief Information Officer

Corporate Database Definitions

ADDRESS

This tables stores addresses of an individual

Column Name

Data Type and Length

Description

SSN
CHAR (9)
A person's 9 character social security number or 9 character unique ID.
VARCHAR2(15)
This is the code to describe the types of addressess in the corporate database. A person can have multiple addresses. The source of many of these address types is AMCAS. Tables are used in applicant records (AMCAS); AVC and other CorpDB applications.
STREET
VARCHAR2(100)
The street address that an individual designates.
CITY
VARCHAR2(40)
This is the city that an individual designates.
COUNTY
VARCHAR2(20)
This is the county that an individual designates.
VARCHAR2(50)
This is the country that an individual designates.
RURAL_ROUTE
VARCHAR2(7)
This is generally used for those persons that do not have an actual street address and live in rural areas of the country. (e.g. RR 1, RR 2)
MAIL_STOP
VARCHAR2(5)
The delivery point where Mail Services delivers and collects mail; every floor in a bldg may have a designated mail stop. (e.g. In Zip Code 20102-401, the mail stop is 401)
LAST_UPDATE
DATE
This is the last time the address record was updated.
ROOM
VARCHAR2(20)
A person's designated office space; also location where a person receives mail.
SITE_CODE
CHAR(4)
This field is used to indicate where a medical student is performing his/her rotation. It is also used to denote a location where a USU employee may be physically working.
DUTY
VARCHAR2(50)
This field indicates the individual's duty station.
PREFERRED_ADDR_TYPE
VARCHAR2(4)
This field indicates which address type is preferred.
PREFERRED_YN
VARCHAR2(1)
This field indicates that a selected address is preferred. This is used in various Corporate Database applications.

EMAIL

This table stores a person?s e-mail address.

Column Name

Data Type and Length

Description

SSN
CHAR (9)
A person's 9 character social security number or 9 character unique ID.
VARCHAR2(15)
This is the type of email address. Click the column name to see a detailed list.
EMAIL_ADDR
VARCHAR2(100)
This is a person's email address.
LAST_UPDATE
DATE
This is the date the email address was last updated.
PREFERRED_YN
VARCHAR2(1)
This field indicates that a selected email address is preferred. This is used in various Corporate Database applications.

PERSON

This tables stores the master record for a person

Column Name

Data Type and Length

Description

SSN
CHAR (9)
A person's 9 character social security number or 9 character unique ID.
VARCHAR2(7)
A unique key assigned to every corporate database person record. It is also utilized for some corporate database applications.
LASTNAME
VARCHAR2(55)
A person's legal last name.
FIRSTNAME
VARCHAR2(55)
A person's legal first name.
MIDDLENAME
VARCHAR2(55)
A person's legal middle name.
MINITIAL
CHAR(1)
A person's middle initial.
MAIDEN_NAME
VARCHAR2(35)
A birth name for a female prior to marriage.
PREFIX
VARCHAR2(30)
A formal title that occurs before a person's first name (e.g.. Dr. Maj., 2LT).
SUFFIX
VARCHAR2(5)
A formal ending that occurs after a person's last name (e.g.. Jr., II, III, Ph.D., MD)
BIRTHDATE
DATE
A person's date of birth.
AGE
NUMBER(2)
Chronological age of a person that is automatically calculated based on birth date.
GENDER
CHAR(1)
The gender indicates whether a person is male or female.
ETHNIC_CODE
VARCHAR2(2)
Ethnic Code is used to identify the ethnicity of non-faculty. (AMCAS)
MARITAL_STATUS
VARCHAR2(10)
The legal standing of a person in regards to his or her marriage state. Status can be Married, Single, Widowed or Divorced
DEPENDENTS
NUMBER(3)
The number of dependents a person has under his/her care.
CITIZENSHIP
VARCHAR2(50)
The legal country of which a person is a citizen.
GENDER
CHAR(1)
The gender indicates whether a person is male or female.
VISA_TYPE
VARCHAR2(50)
This indicates the type of Visa a person possesses The source for this data is AMCAS, GEO. (e.g.. F1-Student, e.g.J1-Exchange Visitor, PR-Permanent Resident.
LIVING_INDICATOR
CHAR(1)
Indicates whether the person is living or deceased. (Y/N)
ORGANIZATION
VARCHAR2(30)
An internal or external organization associated with a person. The organization for internal persons is equivalent to USU.
POSITION
VARCHAR2(20)
Departmental description of a person's role at USU.
TITLE
VARCHAR2(60)
A person's official title as defined by the core department.
PRIOR_SERVICE
CHAR(1)
Indicates whether a person had prior military service. (Y/N)
LAST_UPDATE
DATE
The last date a person's record was updated.
LAST_UPDATED_BY
VARCHAR2(30)
Indicates the last person who updated one of more fields in the person table.
PREV_LASTNAME
VARCHAR2(55)
Indicates the prior last name that a person had that was legally changed.
VARCHAR2(3)
The three character designator for a person's department. This department code can only be changed by a core department. Click the column name to see a detailed list.
MILITARY_YN
CHAR(1)
Indicates whether a person is in the military. (Y/N)
CORPS
VARCHAR2(3)
Indicates the group in which a military person has chose to be a part. (e.g... MC, NS, MSC, BSC)
BRANCH_CODE
VARCHAR2(6)
Indicates the military branch a person has entered.
VARCHAR2(6)
IA combination of the pay grade and branch of service for a military person. Click the column name to see a detailed list.
ACTIVATE_YN
CHAR(1)
Indicates the working status of an individual at here at USU. Y = Active, N = Inactive X = Awaiting Arrival, S = Suspended.
CURRENT_SERVICE_CODE
NUMBER(2)
A code that indicates the current service status of a military person. (e.g.. 0, 1, 2, 3)
SERVICE_ENTRY_DATE
DATE
The date a person entered the military.
RANK_DATE
DATE
The date the military rank was changed.
PAY_PLAN
CHAR(1)
The gender indicates whether a person is male or female.
GENDER
VARCHAR2(2)
Identifies the pay system under which a person's position is covered under the federal government.
GRADE
VARCHAR2(2)
This field is the code that determines the base pay of an individual; both military and federal government employees. (e.g.. Mil - O-3, E-6/Civ - 09, 11, 13)
SERIES
VARCHAR2(5)
The occupational group that a position is under in the federal government.
DATE_ENTERED
DATE
Indicates the initial date that a person's record was created.
ENTERED_BY
VARCHAR2(30)
Indicates the person who created the initial record.
GENDER
CHAR(1)
The gender indicates whether a person is male or female.
GENDER
CHAR(1)
The gender indicates whether a person is male or female.

PERSON_ROLE

This table stores the role category codes of people within the university. See Attachment B (Role Code & Category Definitions)

Column Name

Data Type and Length

Description

SSN
CHAR (9)
A person's 9 character social security number or 9 character unique ID.
VARCHAR2(10)
This field gives additional details about the capacity in which a person serves the organization. Click the column name to see a detailed list.
VARCHAR2(7)
Identifies a function performed by an individual in an organization. Click the column name to see a detailed list.
STATUS
VARCHAR2(30)
This is a text field that holds the status of a person's role while here at the university. (e.g.. Temporary, Active Duty, Long Term Volunteer, Permanent)
START_DATE
DATE
This field indicates the start date for the current role at the university.
END_DATE
DATE
This field indicates the end date (EOD) for the current role at the university.
CREATED_BY
VARCHAR2(30)
Indicates the name of the person who created the person role record.
CREATED_DATE
DATE
Indicates the date the person role record was created.
ADJ_APP_YNSSNE
CHAR(1)
Those faculty who support USU on a part time or episodic basis (as defined in USU SOM Instruction 1100) are considered to have an adjunct appointment and are indicated in this field using a checkbox. role code of contractor (CTR).
CONTRACT_ID
VARCHAR2(30)
This is the identification number that is associated with the external company for those persons who have a role code of contractor (CTR).
LAST_UPDATE
DATE
The last date a person's record was updated.
LAST_UPDATE_BY
VARCHAR2(30)
Indicates the last person who updated one of more fields in the person role table.

PHONE

This table stores data about phone numbers for an individual

Column Name

Data Type and Length

Description

SSN
CHAR (9)
A person's 9 character social security number or 9 character unique ID.
VARCHAR2(15)
Indicates the type of phone number.
PHONE
VARCHAR2(20)
A person's phone number.
LAST_UPDATE
DATE
The last date a person's record was updated.
PREFERRED_YN
VARCHAR2(1)
This field indicates that a selected phone number is preferred. This is used in various Corporate Database applications.