...
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) |
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) |