Faculty table

Oracle Field/Calc

DW Field

Description or Sample

term_code_key

TERM

200710 (Fall = 10, Spring = 20, Summer = 30)

substr(decode(term_code_key,null,null,

f_student_get_desc('STVTERM',term_code_key,30)),1,30)

TERM_DESC

Fall 2006

pidm_key

PIDM

Up to 10-digit unique Banner id

eligible_to_advise_ind

ELIGIBLE_TO_ADVISE_IND

advising_ind

ADVISING_IND

eligible_to_be_scheduled_ind

ELIGIBLE_TO_BE_SCHEDULED_IND

Scheduled_ind

SCHEDULED_IND

Id

ID

Banner Id (@12345678)

Ssn

SSN

123456789

last_name

LAST_NAME

first_name

FIRST_NAME

middle_name

MIDDLE_NAME

middle_initial

MIDDLE_INITIAL

name_prefix

NAME_PREFIX

name_suffix

NAME_SUFFIX

birth_date

BIRTH_DATE

Age

AGE

Gender

GENDER

1-char

ethn_code

ETHN_CODE

1-char

substr(decode(ethn_code,null,null,

f_student_get_desc('STVETHN',ethn_code,30)),1,30)

ETHN_DESC

deceased_ind

DECEASED_IND

1-char (Y/N)

deceased_date

DECEASED_DATE

street1_line1

STREET1_LINE1

street1_line2

STREET1_LINE2

street1_line3

STREET1_LINE3

city1

CITY1

state1

STATE1

zip1

ZIP1

cnty_code1

CNTY_CODE1

County Code: 003 to 115 and 999 (Other)

substr(decode(cnty_code1,null,null,

f_student_get_desc('STVCNTY',cnty_code1,30)),1,30)

CNTY_DESC1

natn_code1

NATN_CODE1

Nation Code: 2-char

substr(decode(natn_code1,null,null,

f_student_get_desc('STVNATN',natn_code1,30)),1,30)

NATN_DESC1

atyp_code1

ADDRESS_TYPE1

phone_area_code1

PHONE_AREA_CODE1

phone_number1

PHONE_NUMBER1

1234567

tele_code1

PHONE_TYPE1

4-char

street2_line1

STREET2_LINE1

street2_line2

STREET2_LINE2

street2_line3

STREET2_LINE3

city2

CITY2

state2

STATE2

zip2

ZIP2

cnty_code2

CNTY_CODE2

County Code: 003 to 115 and 999 (Other)

substr(decode(cnty_code2,null,null,

f_student_get_desc('STVCNTY',cnty_code2,30)),1,30)

CNTY_DESC2

natn_code2

NATN_CODE2

Nation Code: 2-char

substr(decode(natn_code2,null,null,

f_student_get_desc('STVNATN',natn_code2,30)),1,30)

NATN_DESC2

atyp_code2

ADDRESS_TYPE2

phone_area_code2

PHONE_AREA_CODE2

phone_number2

PHONE_NUMBER2

1234567

tele_code2

PHONE_TYPE2

4-char

tenr_code

TENR_CODE

AJ: Adjunct

AN: Administrator No Tenure

P1-P4: Probationary 1 to 4

T: Tenured

TP: Full-time temporary

substr(decode(tenr_code,null,null,f_get_tenure_desc(tenr_code)),1,30)

TENR_DESC

Tenure Description (see TENR_CODE)

tenure_date

TENURE_DATE

tenure_review_date

TENURE_REVIEW_DATE

last_sabbatical_date

LAST_SABBATICAL_DATE

next_sabbatical_date

NEXT_SABBATICAL_DATE

years_experience

YEARS_EXPERIENCE

birth_state

BIRTH_STATE

aaup_member_ind

AAUP_MEMBER_IND

Y/N

academic_title

ACADEMIC_TITLE

primary_activity

PRIMARY_ACTIVITY

term_code_effective

TERM_CODE_EFFECTIVE

Term when became faculty

fcst_code

FCST_CODE

Faculty Status Code (A: Active / IN: Inactive)

substr(decode(fcst_code,null,null,

f_student_get_desc('STVFCST',fcst_code,30)),1,30)

FCST_DESC

Faculty Status Code description

faculty_member_status_date

FACULTY_MEMBER_STATUS_DATE

Date of FCST code change

fctg_code

FCTG_CODE

Faculty Category Code:

A: Administrator Only

A_ED: Educational Administrator

FNC_C: Faculty Non-Class Counselor

FNC_H Faculty Non-Class Health Services:

FNC_L: Faculty Non-Class Librarian

F_INST: Full-Time Instructor

JNC_C: Adjunct Non-Class Counselor

JNC_H: Adjunct Non-Class Health Services

JNC_L: Adjunct Non-Class Librarian

J_INST: Adjunct Instructor

substr(decode(fctg_code,null,null,

f_student_get_desc('STVFCTG',fctg_code,30)),1,30)

FCTG_DESC

Faculty Category Code Description (see FCTG_CODE)

fstp_code

FSTP_CODE

Faculty Staff Type Code:

A: Educ Admin or Administrator

F: Tenure or Tenure Track

J: Adjunct

TE: Temporary Contract

substr(decode(fstp_code,null,null,

f_student_get_desc('STVFSTP',fstp_code,30)),1,30)

FSTP_DESC

Faculty Staff Type Code Description (see FSTP_CODE)

term_code_effective_coll_dept,

TERM_CODE_EFFECTIVE_COLL_DEPT

200710 (Term when faculty became part of department)

substr(decode(term_code_effective_coll_dept,null,null, f_student_get_desc('STVTERM',term_code_effective_coll_dept,30)),1,30)

TERM_DESC_EFFECTIVE_COLL_DEPT

Fall 2006

coll_code

COLL_CODE

College Code

00: COS

99: Not used in standing

substr(decode(coll_code,null,null,

f_student_get_desc(‘STVCOLL’,coll_code,30)),1,30)

COLL_DESC

College Code description (see COLL_CODE)

dept_code

DEPT_CODE

From STVDEPT Oracle table. 4-char department code

substr(decode(dept_code,null,null,

f_student_get_desc(‘STVDEPT’,dept_code,30)),1,30)

DEPT_DESC

Description of DEPT_CODE

home_dept_coll_ind

HOME_DEPT_COLL_IND

(Y/N) Y for primary dept

percentage_of_responsibility

PERCENTAGE_OF_RESPONSIBILITY

Percentage of responsibility in department

fatt_code1

FATT_CODE1

Faculty attribute. Not currently used at COS

substr(decode(fatt_code1,null,null,

f_student_get_desc('STVFATT',fatt_code1,30)),1,30)

FATT_DESC1

fatt_code2

FATT_CODE2

substr(decode(fatt_code2,null,null,

FATT_DESC2

fatt_code3

FATT_CODE3

substr(decode(fatt_code3,null,null,

f_student_get_desc('STVFATT',fatt_code3,30)),1,30)

FATT_DESC3

fatt_code4

FATT_CODE4

substr(decode(fatt_code4,null,null,

f_student_get_desc('STVFATT',fatt_code4,30)),1,30)

FATT_DESC4

fatt_code5

FATT_CODE5

substr(decode(fatt_code5,null,null,

f_student_get_desc('STVFATT',fatt_code5,30)),1,30)

FATT_DESC5

additional_attributes_ind

ADDITIONAL_ATTRIBUTES_IND

Not used, but if it was, this would be Y/N if the faculty member has more than 5 attributues

nist_workload1

NIST_WORKLOAD1

Non-Instructional Workload (units)

nist_code1

NIST_CODE1

Faculty Non-Instructional Code:

ATHL: Coaching Reassignment

BANK: Banked Time Being Used

CFWD: LHE Carry Forward

COST: COSTA Reassignment

COUN: Counselor

DC: Division Chair

LIBR: Librarian

LOA: Leave of Absence

MLOA: Medical Leave of Absence

OTHR: Reassigned by District or MA

RD: Reassigned-District

RF: Reassigned-Faculty MA

SAB: Sabbatical Leave

SENA: Senate Reassignment

SHRT: Short LHE for Workload for Sem

STVC: Othr StSvcs Academic Employee

WB: Willie Brown Contract

substr(decode(nist_code1,null,null,

f_student_get_desc('STVNIST',nist_code1,30)),1,30)

NIST_DESC1

Description of NIST_CODE

coll_code_nist1

COLL_CODE_NIST1

College code for the first Instructional Workload

substr(decode(coll_code_nist1,null,null,

f_student_get_desc('STVCOLL',coll_code_nist1,30)),1,30)

COLL_DESC_NIST1

College code description

dept_code_nist1

DEPT_CODE_NIST1

substr(decode(dept_code_nist1,null,null,

DEPT_DESC_NIST1

nist_workload2

NIST_WORKLOAD2

nist_code2

NIST_CODE2

See NIST_CODE1

substr(decode(nist_code2,null,null,

NIST_DESC2

Description of NIST_CODE2

coll_code_nist2

COLL_CODE_NIST2

substr(decode(coll_code_nist2,null,null,

COLL_DESC_NIST2

dept_code_nist2

DEPT_CODE_NIST2

substr(decode(dept_code_nist2,null,null,

f_student_get_desc('STVDEPT',dept_code_nist2,30)),1,30)

DEPT_DESC_NIST2

nist_workload3

NIST_WORKLOAD3

nist_code3

NIST_CODE3

See NIST_CODE1

substr(decode(nist_code3,null,null,

NIST_DESC3

Description of NIST_CODE3

coll_code_nist3

COLL_CODE_NIST3

substr(decode(coll_code_nist3,null,null,

f_student_get_desc('STVCOLL',coll_code_nist3,30)),1,30)

COLL_DESC_NIST3

dept_code_nist3

DEPT_CODE_NIST3

substr(decode(dept_code_nist3,null,null,

f_student_get_desc('STVDEPT',dept_code_nist3,30)),1,30)

DEPT_DESC_NIST3

nist_workload4

NIST_WORKLOAD4

nist_code4

NIST_CODE4

See NIST_CODE1

substr(decode(nist_code4,null,null,

f_student_get_desc('STVNIST',nist_code4,30)),1,30)

NIST_DESC4

Description of NIST_CODE4

coll_code_nist4

COLL_CODE_NIST4

substr(decode(coll_code_nist4,null,null,

f_student_get_desc('STVCOLL',coll_code_nist4,30)),1,30)

COLL_DESC_NIST4

dept_code_nist4

DEPT_CODE_NIST4

substr(decode(dept_code_nist4,null,null,

DEPT_DESC_NIST4

nist_workload5

NIST_WORKLOAD5

nist_code5

NIST_CODE5

See NIST_CODE1

substr(decode(nist_code5,null,null,

NIST_DESC5

Description of NIST_CODE5

coll_code_nist5

COLL_CODE_NIST5

substr(decode(coll_code_nist5,null,null,

f_student_get_desc('STVCOLL',coll_code_nist5,30)),1,30)

COLL_DESC_NIST5

dept_code_nist5

DEPT_CODE_NIST5

substr(decode(dept_code_nist5,null,null,

f_student_get_desc('STVDEPT',dept_code_nist5,30)),1,30)

DEPT_DESC_NIST5

nist_workload6

NIST_WORKLOAD6

nist_code6

NIST_CODE6

See NIST_CODE1

substr(decode(nist_code6,null,null,

f_student_get_desc('STVNIST',nist_code6,30)),1,30)

NIST_DESC6

Description of NIST_CODE6

coll_code_nist6

COLL_CODE_NIST6

substr(decode(coll_code_nist6,null,null,

f_student_get_desc('STVCOLL',coll_code_nist6,30)),1,30)

COLL_DESC_NIST6

dept_code_nist6

DEPT_CODE_NIST6

substr(decode(dept_code_nist6,null,null,

f_student_get_desc('STVDEPT',dept_code_nist6,30)),1,30)

DEPT_DESC_NIST6

additional_nist_ind,

ADDITIONAL_NIST_IND

WORKLOAD_CODE (from sibinst table) WKLD_CODE

WORKLOAD_CODE

A_0160:Administrator Also Teaching

A_SUM: Administrator Teaching Summer

FT_SUM: Full-Time Temp Teaching Summer

F_100: Full-Time

F_80: Full-Time 80 Percent Workload

F_BANK: Full-Time Using Bank

F_LOAU: Full-Time Leave_Absence-Unpaid

F_S075Full-Time Semester Sabbatical

F_S100Full-Time Year Sabbatical

F_SUMFull-Time Teaching Summer

F_TEMP: Full-Time Temporary

F_WB70: Full-Time WB 70 Percent

F_WB75: Full-Time WB 75 Percent

J_0121: Adjunct Non-Class 1 to 21 hrs

J_0160: Adjunct 1 to 60 Percent

J_6174: Adjunct 60.1 to 74.99 Percent

J_SUM:Adjunct Teaching Summer

f_get_wkld_code(WORKLOAD_CODE)

WORKLOAD_DESC

Description of Workload Code

SIRICNT_FCNT_CODE

FACULTY_CONTRACT_CODE

A: Administrator Only

AE: Educational Administrator

FT: Full-Time Faculty

JP: Adjunct Faculty

OV:Overload Full-Time Faculty

TE: Temporary, Contract Faculty

f_get_fcnt_code(SIRICNT_FCNT_CODE)

FACULTY_CONTRACT_DESC

Description of Faculty_Contract_Code

SIRICNT_CNTR_CODE

CONTRACT_CODE

Type of Contract the Faculty member has. 6-char, from STVCNTR, such as F_FA (Full-time Fine Arts). Same as Faculty Department on the Faculty_Load view.

f_get_CNTR_code(SIRICNT_CNTR_CODE)

CONTRACT_DESC

Description of Contract_Code

RANK_CODE

RANK_CODE

Position information. Type of Faculty code (1: Instructor, 2: Adjunct Instructor)

f_get_rank_code(RANK_CODE)

RANK_DESC

Description of Rank_Code

DISCIPLINE_CODE

DISCIPLINE_CODE

Position information. 5-char discipline code

f_get_disp_code(DISCIPLINE_CODE)

DISCIPLINE_DESC

Position information. Description for Discipline_Code

substr(cos_f_get_email(pidm_key),1,40)

PRIMARY_EMAIL

The primary email for the faculty member

f_get_faculty_units (id, term_code_key) – this function uses decode(ssbsect_credit_hrs, null, scbcrse_credit_hr_low, ssbsect_credit_hrs)

UNITS_TAUGHT

The total number of credit hours the faculty member is teaching this term

DECODE(szrccap_pidm, 99999999, '', SUBSTR ( f_get_desc_fnc ('STVETCT', (SUBSTR(

f_get_ethn_category(REPORTSNET.f_get_ethnicity(

SZRCCAP_PIDM), 'ETCT'), 1, 1)), 30), 1, 30) )

ETHNICITY

Ethnicity description (old)

SUBSTR(REPORTSNET.f_get_new_ethnicity_desc(

SZRCCAP_PIDM),1, 20)

NEW_ETHNICITY

Ethnicity description using new values established in 2009. ‘Hispanic’ or ‘Not Hispanic’

SUBSTR(f_get_races (SZRCCAP_PIDM), 1, 60)

RACES

20 possible race codes, each separated by a pipe (|). Each code is up to 2 characters in length, with a period (.) being used as the 2nd character when the code has only one character (like W. for White)

SUBSTR (f_get_races_desc (SZRCCAP_PIDM), 1, 700)

RACES_DESC

Descriptions of the codes in the RACES field. Again, separated by a pipe.