COMP_SUMMUSF_VW

(SQL View)
 Index Back

Compensation Summary for USF

COMP_SUMM_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 several Salary Administration panels.

SELECT A.EMPLID ,A.EMPL_RCD ,A.EFFDT ,A.EFFSEQ ,A.ACTION ,A.ACTION_REASON ,A.BUSINESS_UNIT ,A.JOBCODE ,A.COMP_FREQUENCY ,A.COMPRATE ,A.CHANGE_AMT ,A.CHANGE_PCT ,A.ANNUAL_RT ,A.HOURLY_RT ,A.CURRENCY_CD ,B.SETID ,A.SAL_ADMIN_PLAN ,A.GRADE ,A.STEP ,A.GRADE_ENTRY_DT ,%subrec(GVT_CMPSUM_SBR, C) FROM PS_JOB A , PS_GVT_JOB C , PS_SET_CNTRL_REC B WHERE (A.CHANGE_AMT <> 0 OR A.ACTION IN('HIR','REH','ASG')) AND A.EMPL_STATUS IN('A','L','P','S','W') AND B.SETCNTRLVALUE=A.BUSINESS_UNIT AND B.RECNAME='SAL_PLAN_TBL' AND A.EMPLID = C.EMPLID AND A.EMPL_RCD = C.EMPL_RCD AND A.EFFDT = C.EFFDT AND A.EFFSEQ = C.EFFSEQ

# 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 ACTION Character(3) VARCHAR2(3) NOT NULL Action

Prompt Table: ACTION_TBL

6 ACTION_REASON Character(3) VARCHAR2(3) NOT NULL Reason Code
7 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
8 JOBCODE Character(6) VARCHAR2(6) NOT NULL Job 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

Prompt Table: COMP_FREQ_VW

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

Prompt Table: CURRENCY_CD_TBL

16 SETID_SALARY Character(5) VARCHAR2(5) NOT NULL Salary SetID
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 GVT_PAY_PLAN Character(2) VARCHAR2(2) NOT NULL Pay Plan
22 GVT_STEP Character(2) VARCHAR2(2) NOT NULL US Federal Step
23 GVT_NOA_CODE Character(3) VARCHAR2(3) NOT NULL Nature of Action Code
24 GVT_LOCALITY_ADJ Number(8,2) DECIMAL(7,2) NOT NULL Locality Adjustment
25 GVT_RTND_PAY_PLAN Character(2) VARCHAR2(2) NOT NULL Retained Pay Plan
26 GVT_RTND_SAL_PLAN Character(4) VARCHAR2(4) NOT NULL Retained Pay Table
27 GVT_RTND_GRADE Character(3) VARCHAR2(3) NOT NULL Retained Grade
28 GVT_RTND_GVT_STEP Character(2) VARCHAR2(2) NOT NULL Retained US Federal Step
29 GVT_PAY_RATE_DETER Character(1) VARCHAR2(1) NOT NULL Pay Rate Determinant
0=Regular Rate
2=Saved Rate - Indefinite
3=Retained Pay - Spec Rt Adjust
4=Saved Rate - Other
5=Special & Superior Quals
6=Special Rate
7=Superior Qualifications Rate
A=Retained Grade - Diff Posn
B=Retained Grade - Same Posn
C=Critical Position Pay
E=Rtnd Grd & Spec Rt - Diff Posn
F=Rtnd Grd & Spec Rt - Same Posn
J=Retained Pay - Same Position
K=Retained Pay - Diff Posn
M=Continued IGA Pay
P=Preserved IGA Pay
R=Retained Pay - SES Removal
S=Continued SES Basic Pay
U=Rtnd Grade & Pay - Same Posn
V=Rtnd Grade & Pay - Diff Posn
30 GVT_PAY_BASIS Character(2) VARCHAR2(2) NOT NULL Pay Basis
BW=Bi-weekly
FB=Fee Basis
PA=Per Annum
PD=Per Diem
PH=Per Hour
PM=Per Month
PW=Piece Work
SM=Semi-monthly
SY=School Year
WC=Without Compensation
31 STEP_ENTRY_DT Date(10) DATE Step Entry Date
32 STD_HOURS Number(7,2) DECIMAL(6,2) NOT NULL Standard Hours
33 GVT_COMPRATE Number(19,6) DECIMAL(18,6) NOT NULL Base Pay