JOBCODE_RANK

(SQL View)
 Index Back

EE Compensatn Ranking by JobCd

JOBCODE_RANK is a view of the Job record which selects employees by Job Code and Salary Administration Plan and ranks them in descending order by annual salary within currency. It is used in the Salary Administration application.

SELECT A.SETID_JOBCODE ,A.JOBCODE ,S.SETID ,A.SAL_ADMIN_PLAN ,A.GRADE ,A.STEP ,A.CURRENCY_CD ,A.ANNUAL_RT ,A.EMPLID ,A.EMPL_RCD ,A.EFFDT ,A.EFFSEQ ,A.COMP_FREQUENCY ,A.COMPANY ,L.SETID ,A.LOCATION ,A.GRADE_ENTRY_DT ,A.COMPRATE FROM PS_JOB A , PS_PER_ORG_ASGN P , PS_SAL_PLAN_TBL S , PS_LOCATION_TBL L WHERE A.EMPLID = P.EMPLID AND P.PER_ORG = 'EMP' AND A.HR_STATUS = 'A' AND A.EFFDT = ( SELECT MAX(B.EFFDT) FROM PS_JOB B WHERE B.EMPLID = A.EMPLID AND B.EMPL_RCD = A.EMPL_RCD AND B.EFFDT <= %CurrentDateIn) AND A.EFFSEQ = ( SELECT MAX(C.EFFSEQ) FROM PS_JOB C WHERE C.EMPLID = A.EMPLID AND C.EMPL_RCD = A.EMPL_RCD AND C.EFFDT = A.EFFDT) AND S.SETID = A.SETID_SALARY AND S.SAL_ADMIN_PLAN= A.SAL_ADMIN_PLAN AND S.EFFDT = ( SELECT MAX(S1.EFFDT) FROM PS_SAL_PLAN_TBL S1 WHERE S1.SETID = A.SETID_SALARY AND S1.SAL_ADMIN_PLAN = A.SAL_ADMIN_PLAN AND S1.EFFDT <= A.EFFDT) AND S.EFF_STATUS = 'A' AND L.SETID = A.SETID_LOCATION AND L.LOCATION = A.LOCATION AND L.EFFDT = ( SELECT MAX(L1.EFFDT) FROM PS_LOCATION_TBL L1 WHERE L1.SETID = A.SETID_LOCATION AND L1.LOCATION = A.LOCATION AND L1.EFFDT <= A.EFFDT) AND L.EFF_STATUS = 'A'

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 SETID Character(5) VARCHAR2(5) NOT NULL SetID
2 JOBCODE Character(6) VARCHAR2(6) NOT NULL Job Code
3 SETID_SALARY Character(5) VARCHAR2(5) NOT NULL Salary SetID
4 SAL_ADMIN_PLAN Character(4) VARCHAR2(4) NOT NULL Salary Administration Plan
5 GRADE Character(3) VARCHAR2(3) NOT NULL Salary Grade
6 STEP Number(2,0) SMALLINT NOT NULL Step
7 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code

Prompt Table: CURRENCY_CD_TBL

8 ANNUAL_RT Number(19,3) DECIMAL(18,3) NOT NULL Annual Rate
9 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
10 EMPL_RCD Number(3,0) SMALLINT NOT NULL Empl Rcd Nbr
11 EFFDT Date(10) DATE Effective Date

Default Value: %date

12 EFFSEQ Number(3,0) SMALLINT NOT NULL Effective Sequence
13 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
14 COMPANY Character(3) VARCHAR2(3) NOT NULL Company
15 SETID_LOCATION Character(5) VARCHAR2(5) NOT NULL Location SetID
16 LOCATION Character(10) VARCHAR2(10) NOT NULL Location Code
17 GRADE_ENTRY_DT Date(10) DATE Grade Entry Date
18 COMPRATE Number(19,6) DECIMAL(18,6) NOT NULL Compensation Rate