Displays FTE counts by fund type and PCS (Program Classification Structure) for the past five fiscal years, by campus, school/division, and department. The data are based on active (on October 31 of each year) primary appointments. Fund types are: General Operating, Designated, and Restricted by year. PCS categories are 1. Instruction, 2. Research, 3. Public Service, 4. Academic Support, 5. Student Services, 6. Institutional Support, 7. Scholarships and Financial Aid, 8. Other, and 9. Auxiliaries. In order to report school and departmental data, the UIDS departmental structure, as found in the UIDS.UIDS_LINK table, is used
SQL used to create Faculty/Staff Table 1:
( SELECT "UIDS"."EMP_PERS"."FISCAL_YR" as year,
"UIDS"."EMP_PERS"."FED_ID_NO" as ssn,
"UIDS"."EMP_PERS"."NAME" as name,
"UIDS"."EMP_PERS"."FTE" as fte,
"UIDS"."EMP_PERS"."SALARY" as salary,
"UIDS"."EMP_PERS"."DEPT_CD_HOME" as dcode,
"UIDS"."EMP_TITL"."UNIV_OCC_GRP_CD" as grp,
"UIDS"."EMP_TITL"."UNIV_OCC_DESC" as grp_nam,
"UIDS"."EMP_DIST"."ACCT_CD" as account,
"UIDS"."EMP_DIST"."ACCT_AMT" as amt,
"UIDS"."EMP_DIST"."ACCT_FTE_PCT" as acc_fte,
"UIDS"."EMP_DIST"."ACCT_STRT_DT" as beg,
"UIDS"."EMP_DIST"."ACCT_STOP_DT" as end,
"UIDS"."EMP_APPT"."FTE" app_fte,
"UIDS"."EMP_APPT"."APPT_BEG_DT" as appt_beg,
"UIDS"."EMP_APPT"."APPT_END_DT" as appt_end,
"UIDS"."EMP_APPT"."PAY_RATE_CD" as pay_rate,
"UIDS"."EMP_APPT"."APPT_CAT" as cat,
"UIDS"."EMP_APPT"."APPT_SAL_AMT" as pay,
"UIDS"."UIDS_LINK"."UIDS_DIV_CD" as u_div,
"UIDS"."UIDS_LINK"."UIDS_DIV_DESC" as u_divis,
"UIDS"."UIDS_LINK"."UIDS_LINK_CD" as u_code,
"UIDS"."UIDS_LINK"."UIDS_LINK_DESC" as u_dpt,
"UIDS"."EMP_PERS"."EMPLMT_STAT" as emp_stat,
"UIDS"."EMP_APPT"."APPT_TYPE" as a_type,
"UIDS"."EMP_PERS"."BENE_BASE" as b_base
FROM"UIDS"."EMP_PERS",
"UIDS"."EMP_TITL",
"UIDS"."EMP_APPT",
"UIDS"."EMP_DIST"
"UIDS"."UIDS_LINK"
WHERE( "UIDS"."EMP_PERS"."FISCAL_YR" = "UIDS"."EMP_TITL"."FISCAL_YR" ) and
( "UIDS"."EMP_PERS"."FISCAL_YR" = "UIDS"."EMP_APPT"."FISCAL_YR" ) and
( "UIDS"."EMP_PERS"."CMP_CD_HOME" = "UIDS"."EMP_APPT"."CMP_CD_HOME" ) and
( "UIDS"."EMP_PERS"."FED_ID_NO" = "UIDS"."EMP_APPT"."FED_ID_NO" ) and
( "UIDS"."EMP_APPT"."FISCAL_YR" = "UIDS"."EMP_DIST"."FISCAL_YR" ) and
( "UIDS"."EMP_APPT"."CMP_CD_HOME" = "UIDS"."EMP_DIST"."CMP_CD_HOME" ) and
( "UIDS"."EMP_APPT"."FED_ID_NO" = "UIDS"."EMP_DIST"."FED_ID_NO" ) and
( "UIDS"."EMP_APPT"."APPT_CAT" = "UIDS"."EMP_DIST"."APPT_CAT" ) and
( "UIDS"."EMP_DIST"."FISCAL_YR" = "UIDS"."UIDS_LINK"."FISCAL_YR" ) and
( "UIDS"."EMP_PERS"."CMP_CD_HOME" = "UIDS"."UIDS_LINK"."CMP_CD" ) and
( "UIDS"."EMP_PERS"."DEPT_CD_HOME" = "UIDS"."UIDS_LINK"."EMP_DEPT_CD_HOME" ) and
( "UIDS"."EMP_DIST"."TITL_CD_DIST" = "UIDS"."EMP_TITL"."TITL_CD" ) and
( ( UIDS."EMP_PERS"."FISCAL_YR" = '2000' ) AND
( UIDS."EMP_PERS"."EMPLMT_STAT" not in ('C','L') ) AND
( UIDS."EMP_APPT"."APPT_END_DT" >= UIDS."EMP_PERS"."CENSUS" ) AND
( UIDS."EMP_APPT"."APPT_BEG_DT" <= UIDS."EMP_PERS"."CENSUS" ) AND
( UIDS."EMP_PERS"."CMP_CD_HOME" <> 'H' ) AND
( UIDS."EMP_APPT"."APPT_CAT" = 'PRIMARY' ) ) );
Reviewed 2022-04-06