Employee table
- Jordan Lamb
This table provides information about employees and their current job(s) at COS, including the information that is provided in the MIS EB file. The EB file includes only employees who are currently employed at COS; this table includes those as well as employees who no longer work at COS. Near the end of the table are 5 groups of fields, 1 for each job currently held by the employee here at COS.
Some of the older data before 2004 is incomplete due to the fact that the data was not brought into Banner from the old Legacy system, so you may see employees with only a MAX_TERMINATION_DATE but no job information.
HOME_ORG and PEBEMPL_CLASS_DESC are the organizational location fields.
Updated: weekly (entire table is updated)
This table shows the name of the fields in the Employee table that correspond to a field in the MIS EB file:
EB file field name | Employee field name | Notes |
EB00 EMPLOYEE-IDENTIFIER | SSN | Â |
EB01 EMPLOYEE-IDENTIFIER-STATUS | ----- | Â |
EB02 EMPLOYEE-BIRTH-DATE | BirthDate | mm/dd/yyyy format |
EB03 EMPLOYEE-GENDER | Gender | M, F |
EB04 EMPLOYEE-ETHNICITY | Ethnicity_Code | Code form STVETHN (2 character), Lives on SPAIDEN |
EB05 EMPLOYEE-CITIZENSHIP | Citz_Code | C, N as described in MIS Data Element Dictionary |
EB06 EMPLOYEE-DISABILITY-STATUS | Disability_Code | 1: Disabled, 2: Not Disabled as described in MIS Data Element Dictionary |
EB07 EMPLOYEE-EE06-OCCUPATIONAL-ACTIVITY | EE06_Occ_Code | 1-8 as described in MIS Data Element Dictionary |
EB08 EMPLOYEE-EMPLOYMENT-CLASSIFICATION | Empl_Class | T, C, R, A, P as described in MIS Data Element Dictionary |
EB09 EMPLOYEE-EMPLOYMENT-STATUS | Empl_Status | C, N, P, R, 1, 2 as described in MIS Data Element Dictionary |
EB10 deleted | Â | Â |
EB11 EMPLOYEE-EMPLOYMENT-CONTRACT-DURATION | Empl_Contr_Dura | 1-3 as described in MIS Data Element Dictionary |
EB12 EMPLOYEE-ANNUAL-SALARY | Job1_Annual_Salary | Â |
EB13 EMPLOYEE-ADDITIONAL-MONETARY-COMPENSATION | Stipend | Â |
Full Employee table description
Oracle Field/Calc | DW Field | Description or Sample |
pidm | PIDM | Â |
id | ID | Banner Id (@12345678) |
ssn | SSN | No dashes |
name_last | LASTNAME | Â |
name_first | FIRSTNAME | Â |
name_mi | MIDDLENAME | May be single initial or entire middle name |
hia.spraddr_street_line1 | ADDR_LINE1 | Â |
hia.spraddr_city | ADDR_CITY | Â |
hia.spraddr_stat_code | ADDR_STATE | Â |
hia.spraddr_zip | ADDR_ZIP | Â |
substr(f_get_desc_fnc('STVNATN',hia.spraddr_natn_code,30),1,30) | ADDR_NATION_DESC | Â |
deceased_ind | DECEASED_IND | Y, N |
deceased_date | DECEASED_DATE | mm/dd/yyyy |
birth_date | BIRTH_DATE | mm/dd/yyyy |
f_calculate_age(null, birth_date, deceased_date) | AGE | Â |
ethnicity_code | ETHNICITY_CODE | Code form STVETHN (2 character), Lives on SPAIDEN |
substr(decode(ethnicity_code, null, null, f_get_desc('STVETHN', ethnicity_code, 30)), 1, 30) | ETHNICITY_DESC Â | Description of Ethnicity_Code (see STVETHN and SPAIDEN) |
gender_code | GENDER | M, F |
substr(cos_f_get_email(PIDM),1,90) | PRIMARY_EMAIL | Â |
substr(cos_f_get_tele(PIDM, 'PR'),1,14) | PRIMARY_PHONE | Â |
confidentiality_ind | CONFIDENTIALITY_IND | Y-Yes, N-No (Previously Yes), Null |
original_hire_date | ORIGINAL_HIRE_DATE | The date of hire |
current_hire_date | CURRENT_HIRE_DATE | The date of hire into current job |
decode(citizenship_code,'1','C','2','C','N' | CITZ_CODE | C, N as described in MIS Data Element Dictionary |
decode(substr(fw_get_empl_disab_ind(pidm),1,1),'N',2,1) | DISABILITY_CODE | 1: Disabled, 2: Not Disabled as described in MIS Data Element Dictionary |
substr(f_get_EE06(pidm),1,1) | EE06_OCC_CODE | 1-8 as described in MIS Data Element Dictionary |
hzwempl.EMPL_EGRP_CODE | EMPL_CLASS | T, C, R, A, P as described in MIS Data Element Dictionary. Will be blank for terminated employees. |
substr(f_get_EMPL_STATUS(pidm),1,2) [this function returns the PTVJCTY_CODE] | EMPL_STATUS | C, N, P, R, 1, 2 as described in MIS Data Element Dictionary. Will be blank for terminated employees. |
substr(f_get_EMPL_CONTRACT_DURATION(pidm),1,1) | EMPL_CONTR_DURA | 1-3 as described in MIS Data Element Dictionary |
f_get_stipend(pidm) | STIPEND | Â |
baninst1.COS_F_EMPL_TERMINATION_DATE(pidm, sysdate) | MAX_TERMINATION_DATE | Blank if the job is ongoing, otherwise end date of most recently ended job. This is the separation date. |
substr(f_get_desc_fnc('FTVORGN', orgn_code_home,30),1,30) | HOME_ORG | Home Organization (250 possible organizations from the FTVORGN table) |
EMPL_CLASS_CODE | PEBEMPL_ECLS_CODE | Employee Class Code (see PTRECLS) |
substr(f_get_desc('PTRECLS_LONG', empl_class_code,30),1,30) | PEBEMPL_CLASS_DESC | PEBEMPL_ECLS_CODE Description |
F_LATEST_PAYCHECK (PIDM) | LAST_PAYCHECK | The date of the most recent paycheck |
reportsnet.f_get_visa_type (PIDM, sysdate, sysdate) | VISA_TYPE | see VISA type code in Appendix |
a.nbbposn_type | JOB1_POS_TYPE | Position Type: S = SingleP = Pooled |
b.nbrbjob_contract_type | JOB1_CONTR_TYPE | Contract Type: P = primaryS = secondaryO = overload |
b.nbrbjob_begin_date | JOB1_BEGIN_DATE | Â |
b.nbrbjob_end_date | JOB1_END_DATE | Â |
decode(b.nbrbjob_posn,null,null, b.nbrbjob_posn || '-' || b.nbrbjob_suff) | JOB1_POS_SUFFIX, Â | Position and Suffix of job |
c.nbrjobs_desc | JOB1_TITLE | Â |
c.nbrjobs_status | JOB1_STATUS | A = activeB = leave without pay with benefitsL = leave without pay without benefitsF = Leave with full pay and benefitsP = Leave with partial pay and benefits T = Terminated |
c.nbrjobs_appt_pct | JOB1_APPT_PCT | The Appointment percent attached to the Job. Used to establish the percentage of full time appointment for an employee in a position. Cannot exceed 100. |
c.nbrjobs_ann_salary | JOB1_ANNUAL_SALARY | The annual salary attached to the job based on the calendar year. |
d.nbbposn_type | JOB2_POS_TYPE | (see JOB1 fields) |
e.nbrbjob_contract_type | JOB2_CONTR_TYPE | (see JOB1 fields) |
e.nbrbjob_begin_date | JOB2_BEGIN_DATE | (see JOB1 fields) |
e.nbrbjob_end_date | JOB2_END_DATE | (see JOB1 fields) |
decode(e.nbrbjob_posn,null,null, e.nbrbjob_posn || '-' || e.nbrbjob_suff) | JOB2_POS_SUFFIX | (see JOB1 fields) |
f.nbrjobs_desc | JOB2_TITLE | (see JOB1 fields) |
f.nbrjobs_status | JOB2_STATUS | (see JOB1 fields) |
f.nbrjobs_appt_pct | JOB2_APPT_PCT | (see JOB1 fields) |
f.nbrjobs_ann_salary | JOB2_ANNUAL_SALARY | (see JOB1 fields) |
g.nbbposn_type | JOB3_POS_TYPE | (see JOB1 fields) |
h.nbrbjob_contract_type | JOB3_CONTR_TYPE | (see JOB1 fields) |
h.nbrbjob_begin_date | JOB3_BEGIN_DATE | (see JOB1 fields) |
h.nbrbjob_end_date | JOB3_END_DATE | (see JOB1 fields) |
decode(h.nbrbjob_posn,null,null, h.nbrbjob_posn || '-' || h.nbrbjob_suff) | JOB3_POS_SUFFIX | (see JOB1 fields) |
i.nbrjobs_desc | JOB3_TITLE | (see JOB1 fields) |
i.nbrjobs_status | JOB3_STATUS | (see JOB1 fields) |
i.nbrjobs_appt_pct | JOB3_APPT_PCT | (see JOB1 fields) |
i.nbrjobs_ann_salary | JOB3_ANNUAL_SALARY | (see JOB1 fields) |
j.nbbposn_type | JOB4_POS_TYPE | (see JOB1 fields) |
k.nbrbjob_contract_type | JOB4_CONTR_TYPE | (see JOB1 fields) |
k.nbrbjob_begin_date | JOB4_BEGIN_DATE | (see JOB1 fields) |
k.nbrbjob_end_date | JOB4_END_DATE | (see JOB1 fields) |
decode(k.nbrbjob_posn,null,null, k.nbrbjob_posn || '-' || k.nbrbjob_suff) | JOB4_POS_SUFFIX | (see JOB1 fields) |
l.nbrjobs_desc | JOB4_TITLE | (see JOB1 fields) |
l.nbrjobs_status | JOB4_STATUS | (see JOB1 fields) |
l.nbrjobs_appt_pct | JOB4_APPT_PCT | (see JOB1 fields) |
l.nbrjobs_ann_salary | JOB4_ANNUAL_SALARY | (see JOB1 fields) |
m.nbbposn_type | JOB5_POS_TYPE | (see JOB1 fields) |
n.nbrbjob_contract_type | JOB5_CONTR_TYPE | (see JOB1 fields) |
n.nbrbjob_begin_date | JOB5_BEGIN_DATE | (see JOB1 fields) |
n.nbrbjob_end_date | JOB5_END_DATE | (see JOB1 fields) |
decode(n.nbrbjob_posn,null,null, n.nbrbjob_posn || '-' || n.nbrbjob_suff) | JOB5_POS_SUFFIX | (see JOB1 fields) |
o.nbrjobs_desc | JOB5_TITLE | (see JOB1 fields) |
o.nbrjobs_status | JOB5_STATUS | (see JOB1 fields) |
o.nbrjobs_appt_pc | JOB5_APPT_PCT | (see JOB1 fields) |
o.nbrjobs_ann_salary | JOB5_ANNUAL_SALARY | (see JOB1 fields) |