Data Elements in the Personnel Data Warehouse Per_FY_Financ_yyyy
   
This is a view which combines data from the Employee, Appointment, and Distribution FY Tables with data from the final Financial Attribute Table for the same fiscal year
   
Click on data element name for CPS data dictionary information
   
Data Element Name CPS FOCUS Name Size Comment
Employee Table Data
Eff_Date_FY No Cycle field in CPS FY file 7 Format of this field is mmmyyyy (i.e., MAR2001)
Location_1 CCAMPUS 2  
Employee_Number EMPNO 9  
Employee_Name EMPNAME 35  
Social_Security_Number SOCSECNO 9  
Home_Address_Discl_Code ADRDISCIND 1  
Title_Code_Pri_Appt TITLEPRI 5  
Title_Code_Pri_Appt_PPS PRITPPS 4  
Active_This_Month_Empl EMPACTIVT 1  
Active_Last_Month_Empl EMPACTIVL 1  
Multiple_Appt_Code MULTAPPT 1  
Feberal_Occup_Code FEDOCPRI 2  
Appt_Type_Code_Pri APTYPRI 1  
Personnel_Program_Primary PROGPRI 1  
Class_Ttl_Outl_Code_Pri CCTOA 3  
UCOP_Employee_Ind COP 1 "A" if DANR, "P" if UCOP
Merced_Employee_Ind MER 1 "M" if Merced employee
Sex_Code CSEX 1  
Date_of_Birth BDATE 6  
Citizen_Status_Code CCITSTAT 1  
Ethnic_Origin_Code CETHNIC 2  
Retirement_System_Code CRETSYS 1  
FICA_Medicare_Elig_Code FICADED 1  
Unemployment_Ins_Cov_Code UNINSCVG 1  
UC_Student_Status_Code CUCSTUDENT 1  
Collective_Barg_Unit_Code CCBU 2  
Empl_Org_Code_Dues_Ben1 CEMPORG1 4  
Empl_Org_Code_Dues_Ben2 CEMPORG2 4  
Empl_Org_Code_Dues_Ben3 CEMPORG3 4  
Empl_Org_Code_Dues_Ben4 CEMPORG4 4  
Empl_Rel_Pos_Type_Code CEMPRELPOS 1  
Empl_Rel_Confid_Code EMPRELCONF 1  
Empl_Unit_Code EUC 2  
Empl_Represent_Code CEMPREP 1  
Date_of_Most_Recent_Hire EMPDT 6  
Original_Date_of_Hire ORIGHIREDT 6 MM/DD/YY
Next_Salary_Rev_Date SALREVDT 5 MM/YY
Next_Salary_Rev_Type_Code CSALREVTYP 1  
Employment_Status_Code CEMPSTAT 1  
Merit_Pct MERITPCT 4  
Separation_Date SEPDT 6 MM/DD/YY
Separation_Rsn_Code CSEPRSN 2  
Leave_Of_Abs_Beg_Date LOABEGDT 6 MM/DD/YY
Leave_Of_Abs_Rtn_Date LOARETDT 6 MM/DD/YY
Leave_Of_Abs_Type_Code CLOATYP 2  
Monthly_Earnings_Code_July MONERNJUL 1 "Y" if there are July earnings
Monthly_Earnings_Code_August MONERNAUG 1 "Y" if there are August earnings
Monthly_Earnings_Code_September MONERNSEP 1 "Y" if there are September earnings
Monthly_Earnings_Code_October MONERNOCT 1 "Y" if there are October earnings
Monthly_Earnings_Code_November MONERNNOV 1 "Y" if there are November earnings
Monthly_Earnings_Code_December MONERNDEC 1 "Y" if there are December earnings
Monthly_Earnings_Code_January MONERNJAN 1 "Y" if there are January earnings
Monthly_Earnings_Code_February MONERNFEB 1 "Y" if there are February earnings
Monthly_Earnings_Code_March MONERNMAR 1 "Y" if there are March earnings
Monthly_Earnings_Code_April MONERNAPR 1 "Y" if there are April earnings
Monthly_Earnings_Code_May MONERNMAY 1 "Y" if there are May earnings
Monthly_Earnings_Code_June MONERNJUN 1 "Y" if there are June earnings
ADD_Eff_Date ADEFFDT 6 MM/DD/YY
ADD_Coverage_Code CADCVG 1  
ADD_Principal_Sum ADPRINSUM 3  
Dental_Plan_Eff_Date DENTEFFDT 6 MM/DD/YY
Dental_Plan_Code CDENTPLN 2  
Dental_Plan_Name not in CPS 21 translation of Dental Plan Code
Life_Ins_Cov_Eff_Date_Empl LFEFFDT 6 MM/DD/YY
Life_Ins_Plan_Code CLFPLN 1  
Life_Ins_Salary_Base_Empl LFBASAMT 3  
Dep_Life_Ins_Plan_Code LFDEPPLN 1  
Med_Ins_Plan_Cov_Eff_Date HPPEFFDT 6 MM/DD/YY
Med_Ins_Plan_Code CHPPPLN 2  
Med_Ins_Plan_Cov_Code CHPPCVG 3  
Medical_Plan_Name not in CPS 21 Translation of Med Ins Plan Code
Temp_Dis_Ins_Code CNDIPLN 1  
TIP_Enrollment CINSRED 1  
Personnel_Action_Code1 CPERACT1 2  
Personnel_Action_Code2 CPERACT2 2  
Personnel_Action_Code3 CPERACT3 2  
Personnel_Action_Code4 CPERACT4 2  
Personnel_Action_Code5 CPERACT5 2  
Personnel_Action_Date PERACTDT 6 MM/DD/YY
Prior_Service_Code CPRSVC 1  
Prior_Institution_Code CPRINST 3 Only used for faculty
Education_Level_Code CEDLVL 1  
Education_Level_Year EDLVLYR 2  
Education_Inst_Code CEDINST 3  
Education_Field_Code CEDFLD 4  
Separated_Empl_Dest_Code CEMPDEST 1  
Future_Institution_Code CFUTINST 3  
Separation_Rsn_Code_Acad CSEPRSNA 2  
Dep_Care_Salary_Red_Plan DEPCAREPLN 1  
Dental_Plan_Cov_Code CDENTCVG 3  
Optical_Plan_Cov_Eff_Date VISEFFDT 6 MM/DD/YY
Optical_Plan_Code CVISPLN 2  
Optical_Plan_Cov_Code CVISCVG 3  
Optical_Plan_Name Not in CPS 21 Translation of Optical Plan Code
Life_Ins_Salary_Base_UC LFBASAMTUC 3  
Life_Ins_Cov_Eff_Date_UC LFEFFDTUC 6 MM/DD/YY
Vietnam_Era_Vet_Status_Code CVETSTAT 1  
WCE_Vet_Status_Code WCEVETSTAT 1  
Veteran_Dis_Status_Code CVETDISAB 1  
Handicapped_Status_Code CHNDCPSTAT 1  
Legal_Plan_Eff_Date LGLEFFDT 6 MM/DD/YY
Legal_Plan_Code CLGLPLN 2  
Legal_Plan_Cov_Code CLGLCVG 3  
Legal_Plan_Name Not in CPS 21 Translation of Legal Plan Code
Life_Ins_Exec_Cov_Code EXECLFPLN 1  
Life_Ins_Exec_Salary_Base EXECLFAMT 3  
Life_Ins_Exec_Eff_Date EXECLFEFFDT 10  
Exec_Sev_Pay_Tot_Int EXSPINTR 9  
Exec_Sev_Pay_Tot_Cont_And_Int EXSPCONTP 9  
Exec_Sev_Pay_Tot_Cont_No_Int EXSPCONTNP 9  
Exec_Sev_Pay_Recent_Mo_Cont EXSPCMCONT 9  
BELI_Asgnd BELIASI 1  
BELI_Derivd BELIDER 1  
BELI_Confl_Dte BELICDT 4 YYMM
Pri_BELI_Status_Qual_Code BELISQC1 2  
Pri_BELI_Status_Qual_Date BELISQD1 6 YY/MM/DD
Sec_BELI_Status_Qual_Code BELISQC1 2  
Sec_BELI_Status_Qual_Date BELISQD1 6 YY/MM/DD
Opt_Out_Ind_Medical MEDOPTOUT 1  
Opt_Out_Ind_Dental DENOPTOUT 1  
Opt_Out_Ind_Optical VISOPTOUT 1  
Avg_Paid_Hours_Wk AVGHRSWK 4  
Dep_Life_Cov_Eff_Date LFDEPEFFDT 6 MM/DD/YY
Total_Gross_Cal_YTD YTDTOTGR 9  
Taxable_Gross_Cal_YTD YTDFWTGR 9  
Dep_Care_Ded_YTD YTDDEPDE 7  
DCP_Fund_Select_Cd DCPFUND 1  
Total_Gross_Cal_YTD_Prior PRYTDTOT 9  
Taxable_Gross_Cal_Prior PRYTDFWT 9  
Last_Day_On_Pay_Status LPAYDT 6 MM/DD/YY
Empl_Pd_Dis_Ins_Plan_Code EDISPLN 1  
Empl_Pd_Dis_Ins_Wait_Per EDISWTPER 3  
Empl_Pd_Dis_Ins_Base_Amt EDISBSAMT 5  
Empl_Pd_Dis_Ins_Eff_Date EDISEFFDT 6 MM/DD/YY
Title_Code_Pri_Appt_PPS PRITPPS 4  
Temp_Dis_Ins_Eff_Date NDIEFFDT 6 MM/DD/YY
Taxable_Gross_Fiscal_YTD FYTDFWTG 9  
Home_Department_Code CHOMEDEPT 6  
Home_Department_Name HOMEDEPT 30  
Country_of_Origin_Code CCOUNTRYRES 2  
Country_of_Origin  COUNTRYRES 30  
Visa_Type VISATYPE 2  
Num_Hrs_Reg_UC_Units NUMREGUC 6 1 decimal place
Retire_FICA_Deriv_Ind RETDEVIND 1  
Hrs_Toward_Benefits_Elig BENELHRS 11 2 decimal places
Hrs_Toward_Career_Elig CARELHRS 11 2 decimal places
Exec_Sev_Pay_YTD_Total YTDSEVPAY 13 Fiscal year cumulative total. Not effective until 2001-2002 fiscal year.
Med_Contri_Base_Curr_Yr MEDBASCUR 3 Annual base salary divided by 1000
Med_Contri_Base_Next_Yr MEDBASNXT 3 Annual base salary divided by 1000
Sep_Vet_Date SEPVETDT 4 YY/MM
HCRA_Indicator HCRAIND 1 "Y" if enrolled in HCRA program
HCRA_Annual_Amount HCRAAMT 7 no decimals
HCRA_Termination_Date HCRATDT 6  
HCRA_YTD_Deductions HCRAYTD 11 2 decimal places
START Reduction Percent STARTPCT 2  
START Begin Date STARTBEGDT 6 YY/MM/DD
START End Date STARTENDDT 6 YY/MM/DD
Sr_Mgmt Sev Pay_Exc_Ind SMSEVEXCIND 1 Y or N
Sr_Mgmt Over Contr Rate SMSEVORCNRT 1  
Home_zip PERMPOST 5  
Depcare_Annual_Amt DEPANNAMT 5  
Depcare_Term_Date DEPTERMDT 6  
Appointment Table Data
Appointment_Number APPTNO 2 A unique number assigned to each appointment for an employee
Title_Code CTITLE 4  
Sub_Location SUBLOC 2 Not used currently
Active_This_Month_Appt APTACTIVT 1  
Active_Last_Month_Appt APTACTIVL 1  
Appt_Basis_Code APTBASIS 2  
Appt_Begin_Date APTBEGDT 6 MM/DD/YY
Appt_End_Date APTENDDT 6 MM/DD/YY
Appt_Pay_Rate APTPAYRT 10  
Appt_Rate_Code CAPTRT 1  
Appt_Type_Code CAPTTYP 1  
Off_Above_Scale_Code COFFABSCA 1  
Paid_Over_Code CPDOVER 2  
Pay_Schedule_Code CPAYSKED 2  
Title_Unit_Code_PPS TUC 2  
Salary_Step_Grade SALMGTGD 2  
Salary_Step SALSTEP 6 Not populated at SF
Time_Appt_Anticipated APTPCT 5  
Title_Name TITLE 30  
Class_Ttl_Outl_Code_Appt CCTOB 3  
Class_Ttl_Outl_Code_Appt1 Not in CPS 1 First character of CCTOB
Class_Ttl_Outl_Code_Appt2 Not in CPS 1 Second character of CCTOB
FOCSUB CFOCSUB 2  
Academic_Rank_Code CACRANK 1  
Sabbatical_Accrual_Code SABACCR 1  
Leave_Accrual_Code_Sick CLVACCSICK 1  
Leave_Accrual_Code_Vac CLVACCVAC 1  
Title_Spec_Handling_Code      
Appt_WOS_Ind CWOS 1  
Appt_Rep_Code CAPTREP 1  
FLSA Status Code FLSASTAT 1  
Personnel_Program CAPTTYPT 1  
Grade_Ind_TCS GRADEIND 1  
Academic_Service_Limit ACSVCLIM 2  
Frozen_Title_Flag FROZNFLG 1  
Standard_Occup_Class_Code SOC 3  
Representation_Flag REPIND 1  
Related_99_TUC REL99 2  
Bargaining_Type BARGAIN 1  
Salary_Range_Minimum SALMIN 12  
Salary_Range_Midpoint SALMID 12  
Salary_Range_Maximum SALMAX 12  
Number_of_Whole_Steps NUMSTEPS 2  
Use_of_Title_Code CUSETITLE 1  
Restricted_Title_Flag RESTRFLG 1  
Overtime_Exempt_Status_Code OTEXEMP 1  
Retirement_Code1 CRETIRE1 1  
Retirement_Code2 CRETIRE2 1  
Academic_Appt_Basis_Code CACAPTBAS 1  
Academic_Appt_Type_Code CACAPTTYP 1  
Academic_Pay_Period_Code CACPAYPER 1  
Academic_Sick_Lv_Elig_Code ACSICKELIG 1  
Staff_Step_Scale_Except_Flag STFEXCPFLG 1  
By_Agreement_Code BYAGREE 1  
Title_Unit_Code_TCS TUCT 2  
Supervisory_Flag SUPVFLAG 1  
Shift_Differential_Elig_Code SDELIG 1  
On_Call_Rate_Elig_code ONCALLELIG 1  
Linkage_Code CLINKAGE 3  
Merit_Flag MERIT 1  
Distribution Table Data
Location_2 CLOC2 1  
Location_3 CLOC3 1  
Account ACCTNO 6  
Fund FUNDNO 5  
Sub_Account CSUBACCT 2  
DOS_Code_CPS CDOS 2  
DOS_Code_PPS PDOS 3  
Distrib_Rate_Code CDISTRT 1  
Distrib_Unit_Code DUC 1  
Loc Code LOCCD 2 Used for sorting locations in specific order-19 locations including AG, OP, and campus medical centers
Locnm 2 Not in CPS 2 2-character location abbreviation-includes OP and AG, medical centers not differentiated
Locnm 5 Not in CPS 5 5-character location abbrev-includes PRES, ANR, and campus medical centers
Locnm 13 Not in CPS 13 13-character location abbrev-includes UCOP, ANR, and campus medical centers
SAU_Code SAU 1  
Distrib_Pay_Rate DSTPAYRT 10  
Work_Study_Cur_Yr_Gross_Earn WORKSTDY 14 2 decimal places
Org_3_Code CORGUNIT 7  
Org_3 Title ORGUNIT 30  
Org_1 Code CORGGRP 7  
Org 1 Title ORGGRP 30  
UAS_Discipline_Code CUASDISC 3  
NSF_Code CNSF 3  
Org_2_Code CORG2 7  
Org_2 Title CORG2NM 30  
Account_Group_Code ACCTGROUP 6  
Fund_Group_Code FUNDGROUP 6  
Function_Code FUNCTION 2  
Budget_Code BDGTCODE 1  
Gross_Earnings_July MONGRSJUL 14 2 decimal places
Gross_Earnings_August MONGRSAUG 14 2 decimal places
Gross_Earnings_September MONGRSSEP 14 2 decimal places
Gross_Earnings_October MONGRSOCT 14 2 decimal places
Gross_Earnings_November MONGRSNOV 14 2 decimal places
Gross_Earnings_December MONGRSDEC 14 2 decimal places
Gross_Earnings_January MONGRSJAN 14 2 decimal places
Gross_Earnings_February MONGRSFEB 14 2 decimal places
Gross_Earnings_March  MONGRSMAR 14 2 decimal places
Gross_Earnings_April MONGRSAPR 14 2 decimal places
Gross_Earnings_May MONGRSMAY 14 2 decimal places
Gross_Earnings_June MONGRSJUN 14 2 decimal places
Hours_Paid_July MONHRSJUL 8 2 decimal places
Hours_Paid_August MONHRSAUG 8 2 decimal places
Hours_Paid_September MONHRSSEP 8 2 decimal places
Hours_Paid_October MONHRSOCT 8 2 decimal places
Hours_Paid_November MONHRSNOV 8 2 decimal places
Hours_Paid_December MONHRSDEC 8 2 decimal places
Hours_Paid_January MONHRSJAN 8 2 decimal places
Hours_Paid_February MONHRSFEB 8 2 decimal places
Hours_Paid_March MONHRSMAR 8 2 decimal places
Hours_Paid_April MONHRSAPR 8 2 decimal places
Hours_Paid_May MONHRSMAY 8 2 decimal places
Hours_Paid_June MONHRSJUN 8 2 decimal places
Time_Distrib_July MONFTEJUL 8 4 decimal places
Time_Distrib_August MONFTEAUG 8 4 decimal places
Time_Distrib_September MONFTESEP 8 4 decimal places
Time_Distrib_October MONFTEOCT 8 4 decimal places
Time_Distrib_November MONFTENOV 8 4 decimal places
Time_Distrib_December MONFTEDEC 8 4 decimal places
Time_Distrib_January MONFTEJAN 8 4 decimal places
Time_Distrib_February MONFTEFEB 8 4 decimal places
Time_Distrib_March MONFTEMAR 8 4 decimal places
Time_Distrib_April MONFTEAPR 8 4 decimal places
Time_Distrib_May MONFTEMAY 8 4 decimal places
Time_Distrib_June MONFTEJUN 8 4 decimal places
Gross_Earnings_Prior_FY PRFYGRS 17 2 decimal places
Hours_Paid_Prior_FY PRFYHRS 12 2 decimal places
Time_Distrib_Prior_FY PRFYFTE 12 4 decimal places
Account Attribute Table Data
Alpha Location   1  
Account Title   35  
Account_Group_1   1  
Account_Group_2   2  
Account_Group_3   3  
Account_Group_4   4  
Account_Group_5   5  
Account_Group_1_Title   35  
Account_Group_2_Title   35  
Account_Group_3_Title   35  
Account_Group_4_Title   35  
Account_Group_5_Title   35  
Account_Group_6_Title   35  
Arc_Code   6  
Arc_Function   2  
Arc_College   2  
Arc_Department   2  
DANR_Flag   1  
UAS_Code   6  
UAS Discipline    3  
UAS Program Title   20  
UAS Program Code   2  
UAS Structure Title   20  
UAS Structure   10  
UAS_Subprogram_Title   20  
UAS_Subprogram   4  
NSF Code   3  
NSF_Title   20  
Dept_Report_Code   6  
Dept_Report_Title   29  
SAU_Title   22  
Fund_Balance_Ind   1  
UCOP   1  
Fund Title   35  
Fund_Group_Budget   25  
Fund_Group_1   1  
Fund_Group_2   3  
Fund_Group_3   4  
Fund_Group_1_Title   35  
Fund_Group_2_Title   35  
Fund_Group_3_Title   35  
Fund_Group_4_Title   35  
Fund Restriction Code   1  
Fund Restriction Title   22  
Budgeted Fund Code   1  
MOP_Code   2  
Endowment Restriction    5  
Fed_Flow_Thru   1  
Award_Type   1  
CFDA_Code   5  
IDC_Rate   4 1 decimal place
IDC_Base   1  
On/Off Campus   1  
Sponsor Code   4  
Sponsor_Title   70  
Sponsor_Foreign   1  
Sponsor_Country   20  
Agency_Code   2  
Agency_Title   35  
Agency_Short_Title   4  
Sponsor Category Code   2  
Subagency_Code   2  
Subagency_Title   35  
Subagency_Short_Title   4  
Special_Fund_Ind   1  
   
Last Updated: March 7, 2004  
   
Return to Personnel Data Warehouse Description of Tables