HISTORY

(SQL View)
 Index Back

EMPLOYEES Only

HISTORY is a view that combines personal/demographic data, employment data, and job/salary history for an employee for a date range (from-date and to-date). This view is used primarily for reporting.

SELECT PER.EMPLID ,W.EMPL_RCD ,C.EFFDT ,C.EFFSEQ ,PER.NAME ,PER.NAME_PREFIX ,W.PER_ORG ,O.ORIG_HIRE_DT ,PER.SEX ,PER.BIRTHDATE ,PER.BIRTHPLACE ,PER.DT_OF_DEATH ,PER.MAR_STATUS ,PER.FT_STUDENT ,W.BENEFIT_RCD_NBR ,W.HOME_HOST_CLASS ,W.HIRE_DT ,W.LAST_HIRE_DT ,W.CMPNY_SENIORITY_DT ,W.SERVICE_DT ,W.EXPECTED_RETURN_DT ,W.TERMINATION_DT ,W.LAST_DATE_WORKED ,W.LAST_INCREASE_DT ,W.OWN_5PERCENT_CO ,W.BUSINESS_TITLE ,C.REPORTS_TO ,C.SUPERVISOR_ID ,W.PROBATION_DT ,C.DEPTID ,C.JOBCODE ,C.POSITION_NBR ,C.EMPL_STATUS ,C.ACTION ,C.ACTION_DT ,C.ACTION_REASON ,C.LOCATION ,C.JOB_ENTRY_DT ,C.DEPT_ENTRY_DT ,C.POSITION_ENTRY_DT ,C.SHIFT ,C.REG_TEMP ,C.FULL_PART_TIME ,C.FLSA_STATUS ,C.OFFICER_CD ,C.COMPANY ,C.PAYGROUP ,C.EMPL_TYPE ,C.HOLIDAY_SCHEDULE ,C.STD_HOURS ,C.EEO_CLASS ,C.SAL_ADMIN_PLAN ,C.GRADE ,C.GRADE_ENTRY_DT ,C.STEP ,C.STEP_ENTRY_DT ,C.GL_PAY_TYPE ,C.COMP_FREQUENCY ,C.COMPRATE ,C.CHANGE_AMT ,C.CHANGE_PCT ,C.ANNUAL_RT ,C.MONTHLY_RT ,C.DAILY_RT ,C.HOURLY_RT ,C.ANNL_BENEF_BASE_RT ,C.SHIFT_RT ,C.SHIFT_FACTOR ,C.CURRENCY_CD ,C.BUSINESS_UNIT ,D.DESCR ,D.DESCRSHORT ,D.EEO1CODE ,D.EEO4CODE ,D.EEO5CODE ,D.EEO6CODE ,D.EEO_JOB_GROUP ,D.JOB_FAMILY ,D.JOB_KNOWHOW_POINTS ,D.JOB_ACCNTAB_POINTS ,D.JOB_PROBSLV_POINTS ,D.JOB_POINTS_TOTAL ,D.JOB_KNOWHOW_PCT ,D.JOB_ACCNTAB_PCT ,D.JOB_PROBSLV_PCT ,E.DESCR ,E.DESCRSHORT ,E.MANAGER_ID ,E.EEO4_FUNCTION ,F.FROMDATE ,F.ASOFDATE ,D.IPEDSSCODE ,C.ADDS_TO_FTE_ACTUAL ,C.CLASS_INDC ,C.FICA_STATUS_EE ,C.FTE ,C.PRORATE_CNT_AMT ,C.ENCUMB_OVERRIDE ,C.PAY_SYSTEM_FLG FROM PS_PERSONAL_DT_FST PER , PS_PER_ORG_ASGN_VW W , PS_JOB C , PS_JOBCODE_TBL D , PS_DEPT_TBL E , PSASOFDATE F , PS_ORIG_HIR_EMP_VW O WHERE W.PER_ORG = 'EMP' AND W.EMPLID = PER.EMPLID AND O.EMPLID = PER.EMPLID AND C.EMPLID = W.EMPLID AND C.EMPL_RCD = W.EMPL_RCD AND C.EFFDT BETWEEN F.FROMDATE AND F.ASOFDATE AND D.JOBCODE = C.JOBCODE AND D.EFFDT = ( SELECT MAX(J.EFFDT) FROM PS_JOBCODE_TBL J WHERE J.JOBCODE = D.JOBCODE AND J.EFFDT <= C.EFFDT AND J.SETID = C.SETID_JOBCODE) AND D.SETID = C.SETID_JOBCODE AND E.DEPTID = C.DEPTID AND E.EFFDT = ( SELECT MAX(K.EFFDT) FROM PS_DEPT_TBL K WHERE K.DEPTID = E.DEPTID AND K.EFFDT <= C.EFFDT AND K.SETID = C.SETID_DEPT) AND E.SETID = C.SETID_DEPT

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
2 EMPL_RCD Number(3,0) SMALLINT NOT NULL Empl Rcd Nbr
3 EFFDT Date(10) DATE NOT NULL Effective Date
4 EFFSEQ Number(3,0) SMALLINT NOT NULL Effective Sequence
5 NAME Character(50) VARCHAR2(50) NOT NULL Name
6 NAME_PREFIX Character(4) VARCHAR2(4) NOT NULL Name Prefix
7 PER_ORG Character(3) VARCHAR2(3) NOT NULL Defines the Organizational Relationship(s) that a Person has to the Organization. These are Employee, Contingent Worker, and Persons of Interest.
CWR=Contingent Worker
EMP=Employee
POI=Person of Interest
8 ORIG_HIRE_DT Date(10) DATE Original Start Date
9 SEX Character(1) VARCHAR2(1) NOT NULL Gender
F=Female
M=Male
U=Unknown
X=Indeterminate/Intersex/Unspec
10 BIRTHDATE Date(10) DATE Date of Birth
11 BIRTHPLACE Character(30) VARCHAR2(30) NOT NULL Birth Location
12 DT_OF_DEATH Date(10) DATE Date of Death
13 MAR_STATUS Character(1) VARCHAR2(1) NOT NULL Marital Status
C=Common-Law
D=Divorced
E=Separated
H=Head of Household
L=DissDeclLost Civil Partner
M=Married
P=Civil Partnership
S=Single
T=Surviving Civil Partner
U=Unknown
V=Dissolved Civil Partnership
W=Widowed
14 FT_STUDENT Character(1) VARCHAR2(1) NOT NULL Full-Time Student

Y/N Table Edit

15 BENEFIT_RCD_NBR Number(3,0) SMALLINT NOT NULL Specifies a sequence number associated with each of multiple concurrent jobs for a one employee. It is used to determine the applicable benefit program for each job. The first job entered for an employee is automatically assigned a Benefit Record Number of zero (0). This field is linked with EMPL_RCD#, the Employment Record Number.
16 HOME_HOST_CLASS Character(1) VARCHAR2(1) NOT NULL Home/Host Classification
M=Home
S=Host
17 HIRE_DT Date(10) DATE First Start Date
18 REHIRE_DT Date(10) DATE Rehire Date
19 CMPNY_SENIORITY_DT Date(10) DATE Company Seniority Date
20 SERVICE_DT Date(10) DATE Service Date
21 EXPECTED_RETURN_DT Date(10) DATE Expected Return Date
22 TERMINATION_DT Date(10) DATE Termination Date
23 LAST_DATE_WORKED Date(10) DATE Last Date Worked
24 LAST_INCREASE_DT Date(10) DATE Date Last Increase
25 OWN_5PERCENT_CO Character(1) VARCHAR2(1) NOT NULL A flag that indicates whether or not (Y or N) an employee owns 5% or more of the company. This value is used in nondiscrimination testing.

Y/N Table Edit

26 BUSINESS_TITLE Character(30) VARCHAR2(30) NOT NULL Business Title
27 REPORTS_TO Character(8) VARCHAR2(8) NOT NULL Reports To Position Number
28 SUPERVISOR_ID Character(11) VARCHAR2(11) NOT NULL Supervisor ID

Prompt Table: PERSON

29 PROBATION_DT Date(10) DATE Probation Date
30 DEPTID Character(10) VARCHAR2(10) NOT NULL Department

Prompt Table: DEPT_TBL

31 JOBCODE Character(6) VARCHAR2(6) NOT NULL Job Code

Prompt Table: JOBCODE_TBL

32 POSITION_NBR Character(8) VARCHAR2(8) NOT NULL Position Number
33 EMPL_STATUS Character(1) VARCHAR2(1) NOT NULL Payroll Status
A=Active
D=Deceased
L=Leave of Absence
P=Leave With Pay
Q=Retired With Pay
R=Retired
S=Suspended
T=Terminated
U=Terminated With Pay
V=Terminated Pension Pay Out
W=Short Work Break
X=Retired-Pension Administration
34 ACTION Character(3) VARCHAR2(3) NOT NULL Action

Prompt Table: ACTION_TBL

35 ACTION_DT Date(10) DATE Action Date
36 ACTION_REASON Character(3) VARCHAR2(3) NOT NULL Reason Code

Prompt Table: ACTN_REASON_TBL

37 LOCATION Character(10) VARCHAR2(10) NOT NULL Location Code

Prompt Table: LOCATION_TBL

38 JOB_ENTRY_DT Date(10) DATE Job Entry Date
39 DEPT_ENTRY_DT Date(10) DATE Department Entry Date
40 POSITION_ENTRY_DT Date(10) DATE Position Entry Date
41 SHIFT Character(1) VARCHAR2(1) NOT NULL Regular Shift
1=1
2=2
3=3
A=Any
C=Compressed
N=Not Applicable
R=Rotating
42 REG_TEMP Character(1) VARCHAR2(1) NOT NULL Regular/Temporary
R=Regular
T=Temporary
43 FULL_PART_TIME Character(1) VARCHAR2(1) NOT NULL Full/Part Time
D=On Demand
F=Full-Time
P=Part-Time
44 FLSA_STATUS Character(1) VARCHAR2(1) NOT NULL FLSA Status field
A=Administrative
C=Computer Employee
E=Executive
H=Highly Compensated Employees
M=Management
N=Nonexempt
O=Outside Salesperson
P=Professional
V=Nonexempt Alt Overtime
X=No FLSA Required
45 OFFICER_CD Character(1) VARCHAR2(1) NOT NULL Officer Code
C=Chairman
D=Director
N=None
O=Officer
P=President
S=Secretary
T=Treasurer
V=Vice President
46 COMPANY Character(3) VARCHAR2(3) NOT NULL Company

Prompt Table: COMPANY_TBL

47 PAYGROUP Character(3) VARCHAR2(3) NOT NULL Pay Group

Prompt Table: PAYGROUP_TBL

48 EMPL_TYPE Character(1) VARCHAR2(1) NOT NULL Employee Type
E=Exception Hourly
H=Hourly
N=Not Applicable
S=Salaried

Prompt Table: PAYGRP_EMPLTYPE

49 HOLIDAY_SCHEDULE Character(6) VARCHAR2(6) NOT NULL Holiday Schedule

Prompt Table: HOLIDAY_TBL

50 STD_HOURS Number(7,2) DECIMAL(6,2) NOT NULL Standard Hours
51 EEO_CLASS Character(1) VARCHAR2(1) NOT NULL EEO Classification
E=Exclude from Count
N=None of the Above
P=Production Trainee
W=White-Collar Trainee
52 SAL_ADMIN_PLAN Character(4) VARCHAR2(4) NOT NULL Salary Administration Plan
53 GRADE Character(3) VARCHAR2(3) NOT NULL Salary Grade

Prompt Table: SAL_GRADE_TBL

54 GRADE_ENTRY_DT Date(10) DATE Grade Entry Date
55 STEP Number(2,0) SMALLINT NOT NULL Step

Prompt Table: SAL_STEP_TBL

56 STEP_ENTRY_DT Date(10) DATE Step Entry Date
57 GL_PAY_TYPE Character(6) VARCHAR2(6) NOT NULL General Ledger Pay Type
58 COMP_FREQUENCY Character(5) VARCHAR2(5) NOT NULL Compensation Frequency
A=Annual
B=Biweekly
C=Contract
D=Daily
H=Hourly
M=Monthly
S=Semimonthly
W=Weekly

Prompt Table: COMP_FREQ_VW

59 COMPRATE Number(19,6) DECIMAL(18,6) NOT NULL Compensation Rate
60 CHANGE_AMT Signed Number(20,6) DECIMAL(18,6) NOT NULL Change Amount
61 CHANGE_PCT Signed Number(8,3) DECIMAL(6,3) NOT NULL Change Percent
62 ANNUAL_RT Number(19,3) DECIMAL(18,3) NOT NULL Annual Rate
63 MONTHLY_RT Number(19,3) DECIMAL(18,3) NOT NULL Monthly Rate
64 DAILY_RT Number(19,3) DECIMAL(18,3) NOT NULL Daily Rate
65 HOURLY_RT Number(19,6) DECIMAL(18,6) NOT NULL Hourly Rate
66 ANNL_BENEF_BASE_RT Number(19,3) DECIMAL(18,3) NOT NULL Specifies a compensation amount used to calculate benefits and benefit deductions for an employee. The base compensation amount may include commissions and bonuses in addition to regular compensation. If no Annual Benefits Base Rate is entered, then benefits calculations are based on an employee's regular compensation.
67 SHIFT_RT Number(19,6) DECIMAL(18,6) NOT NULL Shift Differential Rate
68 SHIFT_FACTOR Number(5,3) DECIMAL(4,3) NOT NULL Shift Differential Factor
69 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code

Prompt Table: CURRENCY_CD_TBL

70 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
71 JOBTITLE Character(30) VARCHAR2(30) NOT NULL Job Title
72 JOBTITLE_ABBRV Character(10) VARCHAR2(10) NOT NULL Short Name
73 EEO1CODE Character(1) VARCHAR2(1) NOT NULL EEO-1 Job Category
1=Officials and Managers
2=Professionals
3=Technicians
4=Sales Workers
5=Administrative Support Workers
6=Craft Workers
7=Operatives
8=Laborers and Helpers
9=Service Workers
A=Executive/Sr Level Officials
B=First/Mid Level Officials
N=No EEO-1 Reporting
74 EEO4CODE Character(1) VARCHAR2(1) NOT NULL EEO-4 Job Category
1=Officials and Administrators
2=Professionals
3=Technicians
4=Protective Service
5=Paraprofessionals
6=Office/Clerical
7=Skilled Craft
8=Service Maintenance
N=No EEO-4 Reporting
75 EEO5CODE Character(2) VARCHAR2(2) NOT NULL EEO-5 Job Category
1=Officials,Administrators,Mgrs
10=Librarian/Audiovisual
11=Consultants/Supv.of Instructn
12=Other Professional Staff
13=Teacher Aides
14=Technicians
15=Clerical/Secretarial
16=Service Workers
17=Craft Workers (Skilled)
18=Laborers (Unskilled)
2=Principals
3=Asst. Principals-Teaching
4=Asst. Principals-Nonteaching
5=Elementary Classroom Teachers
6=Secondary Classroom Teachers
7=Other Classroom Teachers
8=Guidance
9=Psychological
N=No EEO-5 Reporting
76 EEO6CODE Character(1) VARCHAR2(1) NOT NULL EEO-6 Job Category
1=Executive/Admin/Managerial
2=Faculty
3=Professional Nonfaculty
4=Secretarial/Clerical
5=Technical/Paraprofessional
6=Skilled Crafts
7=Service Maintenance Workers
N=No EEO-6 Reporting
77 EEO_JOB_GROUP Character(4) VARCHAR2(4) NOT NULL EEO Job Group
78 JOB_FAMILY Character(6) VARCHAR2(6) NOT NULL Job Family
79 JOB_KNOWHOW_POINTS Number(5,0) INTEGER NOT NULL Knowhow-Points
80 JOB_ACCNTAB_POINTS Number(5,0) INTEGER NOT NULL Accountability-Points
81 JOB_PROBSLV_POINTS Number(5,0) INTEGER NOT NULL Problem-Solving-Points
82 JOB_POINTS_TOTAL Number(5,0) INTEGER NOT NULL Total Points
83 JOB_KNOWHOW_PCT Number(5,1) DECIMAL(4,1) NOT NULL Knowhow-Percent
84 JOB_ACCNTAB_PCT Number(5,1) DECIMAL(4,1) NOT NULL Accountability-Percent
85 JOB_PROBSLV_PCT Number(5,1) DECIMAL(4,1) NOT NULL Problem-Solving-Percent
86 DEPTNAME Character(30) VARCHAR2(30) NOT NULL Department
87 DEPTNAME_ABBRV Character(10) VARCHAR2(10) NOT NULL Short Name
88 MANAGER_ID Character(11) VARCHAR2(11) NOT NULL Manager ID

Prompt Table: PERSON

89 EEO4_FUNCTION Character(2) VARCHAR2(2) NOT NULL U.S. EEO4 Function
01=Financial Administration
02=Streets and Highways
03=Public Welfare
04=Police Protection
05=Fire Protection
06=Natural Resources
07=Hospitals & Sanatoriums
08=Health
09=Housing
10=Community Development
11=Corrections
12=Utilities & Transportation
13=Sanitation & Sewage
14=Employment Security
15=Other
99=Not Counted for EEO Reporting
90 FROMDATE Date(10) DATE From Date
91 ASOFDATE Date(10) DATE As of Date
92 IPEDSSCODE Character(1) VARCHAR2(1) NOT NULL IPEDS-S Job Category
93 ADDS_TO_FTE_ACTUAL Character(1) VARCHAR2(1) NOT NULL Adds to FTE Actual Count
94 CLASS_INDC Character(1) VARCHAR2(1) NOT NULL Classified/Unclassified Indc
B=All
C=Classified
T=Temporary
U=Unclassified
95 FICA_STATUS_EE Character(1) VARCHAR2(1) NOT NULL FICA Status-Employee
E=Exempt
H=ER FICA Exmpt
M=Medicare only
N=Subject
96 FTE Number(8,6) DECIMAL(7,6) NOT NULL This field represent Full Time Equivalence
97 PRORATE_CNT_AMT Character(1) VARCHAR2(1) NOT NULL Prorate Contract Change Amount
C=Prorate Over Contract Period
N=No Proration of Change Amt.
P=Prorate over Payment Period
98 ENCUMB_OVERRIDE Character(1) VARCHAR2(1) NOT NULL Encumbrance Override
99 PAY_SYSTEM_FLG Character(2) VARCHAR2(2) NOT NULL Payroll System
GP=Global Payroll
NA=Payroll for North America
OT=Other
PI=Payroll Interface