COMPSUMMRPT_VW

(SQL View)
 Index Back

Compensation Summary Report Vw

COMPSUMMRPT_VW is 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 Salary Administration reports.

SELECT A.EMPLID ,B.NAME_DISPLAY ,A.EMPL_RCD ,A.EFFDT ,A.EFFSEQ ,A.DEPTID ,A.JOBCODE ,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 ,A.SAL_ADMIN_PLAN ,A.GRADE ,A.STEP ,A.GRADE_ENTRY_DT ,A.BUSINESS_UNIT FROM PS_JOB A , PS_PERSONAL_DT_FST B , PS_SET_CNTRL_REC S WHERE B.EMPLID = A.EMPLID AND(A.CHANGE_AMT <> 0 OR A.ACTION IN('HIR','REH','ASG')) AND A.HR_STATUS = 'A' AND S.SETCNTRLVALUE = A.BUSINESS_UNIT AND S.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 NAME Character(50) VARCHAR2(50) NOT NULL Name
3 EMPL_RCD Number(3,0) SMALLINT NOT NULL Empl Record
4 EFFDT Date(10) DATE NOT NULL Effective Date
5 EFFSEQ Number(3,0) SMALLINT NOT NULL Effective Sequence
6 DEPTID Character(10) VARCHAR2(10) NOT NULL Department
7 JOBCODE Character(6) VARCHAR2(6) NOT NULL Job Code
8 ACTION Character(3) VARCHAR2(3) NOT NULL Action

Prompt Table: ACTION_TBL

9 ACTION_REASON Character(3) VARCHAR2(3) NOT NULL Reason Code
10 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
11 COMPRATE Number(19,6) DECIMAL(18,6) NOT NULL Compensation Rate
12 CHANGE_AMT Signed Number(20,6) DECIMAL(18,6) NOT NULL Change Amount
13 CHANGE_PCT Signed Number(8,3) DECIMAL(6,3) NOT NULL Change Percent
14 ANNUAL_RT Number(19,3) DECIMAL(18,3) NOT NULL Annual Rate
15 HOURLY_RT Number(19,6) DECIMAL(18,6) NOT NULL Hourly Rate
16 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code
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 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit