COMP_HIST_VW

(SQL View)
Index Back

Compensation History View

A view of an employee's salary history, based on their Job records. Only their hire record and any record with a change in salary are retrieved by this view. It is used to display compensation history in several Salary Administration panels. We joined in the salary_plan data to get a valid level two relationship between salary plan data and this historical data. The effdt and effseq data are from the salary_plan table and reflect the possible future salary raise dates. The job_effdt and job_effseq show the actual data coming from job.

SELECT A.EMPLID ,A.EMPL_RCD ,A.EFFDT ,A.EFFSEQ ,A.EFFDT ,A.EFFSEQ ,A.ACTION ,A.ACTION_REASON ,A.COMP_FREQUENCY ,A.COMPRATE ,A.CHANGE_AMT ,A.CHANGE_PCT ,A.ANNUAL_RT ,A.HOURLY_RT ,A.CURRENCY_CD ,C.SETID ,A.SAL_ADMIN_PLAN ,A.GRADE ,A.STEP ,A.GRADE_ENTRY_DT ,A.JOBCODE FROM PS_JOB A , PS_SET_CNTRL_REC C WHERE (A.CHANGE_AMT <> 0 OR A.ACTION IN('HIR','REH','ASG','ADL')) AND A.HR_STATUS = 'A' AND C.SETCNTRLVALUE=A.BUSINESS_UNIT AND C.RECNAME='SAL_PLAN_TBL'

# 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 Record
3 EFFDT Date(10) DATE NOT NULL Effective Date
4 EFFSEQ Number(3,0) SMALLINT NOT NULL Effective Sequence
5 JOB_EFFDT Date(10) DATE Job Effective Date
6 JOB_EFFSEQ Number(3,0) SMALLINT NOT NULL Job Effective Sequence
7 ACTION Character(3) VARCHAR2(3) NOT NULL Action

Prompt Table: ACTION_TBL

8 ACTION_REASON Character(3) VARCHAR2(3) NOT NULL Reason Code
9 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
10 COMPRATE Number(19,6) DECIMAL(18,6) NOT NULL Compensation Rate
11 CHANGE_AMT Signed Number(20,6) DECIMAL(18,6) NOT NULL Change Amount
12 CHANGE_PCT Signed Number(8,3) DECIMAL(6,3) NOT NULL Change Percent
13 ANNUAL_RT Number(19,3) DECIMAL(18,3) NOT NULL Annual Rate
14 HOURLY_RT Number(19,6) DECIMAL(18,6) NOT NULL Hourly Rate
15 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code
16 SETID_SALARY Character(5) VARCHAR2(5) NOT NULL Salary Set ID
17 SAL_ADMIN_PLAN Character(4) VARCHAR2(4) NOT NULL Salary Administration Plan
18 GRADE Character(3) VARCHAR2(3) NOT NULL Salary Grade
19 STEP Number(2,0) SMALLINT NOT NULL Step
20 GRADE_ENTRY_DT Date(10) DATE Grade Entry Date
21 JOBCODE Character(6) VARCHAR2(6) NOT NULL Job Code