JOBCODE_RANK(SQL View) |
Index Back |
---|---|
EE Compensatn Ranking by JobCdJOBCODE_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 A.EMPL_RCD = P.EMPL_RCD 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 Set ID |
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 Record |
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 Set ID |
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 |