SALPLN_GRP_C_VW

(SQL View)
 Index Back

EE Current Job Data - Sal Plan

SALPLN_GRP_C_VW is a view which retrieves the compensation data for employees which was current as of the salary plan start date. It is used in the Salary Planning application.

SELECT A.EMPLID ,A.EMPL_RCD ,G.EFFDT ,G.EFFSEQ ,C.COMP_EFFSEQ ,C.COMP_RATECD ,A.EFFDT ,A.EFFSEQ ,A.EMPL_STATUS ,C.COMP_FREQUENCY ,C.COMPRATE ,C.CURRENCY_CD ,C.COMP_RATE_POINTS ,C.COMP_PCT ,C.RATE_CODE_GROUP ,C.CHANGE_AMT ,C.CHANGE_PCT ,C.CHANGE_PTS ,C.COMP_FREQUENCY ,C.CURRENCY_CD ,C.RATE_CODE_GROUP ,C.COMPRATE ,C.COMP_RATE_POINTS ,C.COMP_PCT ,C.CONVERT_COMPRT ,'N' ,C.FTE_INDICATOR ,C.CMP_SRC_IND ,B.GB_GROUP_ID ,B.BUDGET_START_DT ,G.BUDGET_ID FROM PS_JOB A ,PS_GROUP_INCR_CNTL B ,PS_GRP_INCR_BUDGET G ,PS_COMPENSATION C ,PS_SAL_PLN_GRP_RES R WHERE B.GB_GROUP_ID = G.GB_GROUP_ID AND B.BUDGET_START_DT = G.BUDGET_START_DT AND G.EFFDT >= B.BUDGET_START_DT AND B.GB_GROUP_ID = R.GB_GROUP_ID AND A.EMPLID = R.EMPLID AND A.EMPL_RCD = R.EMPL_RCD AND R.VERSIONGBQDM = ( SELECT MAX(R2.VERSIONGBQDM) FROM PS_SAL_PLN_GRP_RES R2 WHERE R.GB_GROUP_ID = R2.GB_GROUP_ID) AND R.JOB_EFFDT = ( SELECT MAX(R3.JOB_EFFDT) FROM PS_SAL_PLN_GRP_RES R3 WHERE R.GB_GROUP_ID = R3.GB_GROUP_ID AND R.EMPLID = R3.EMPLID AND R.EMPL_RCD = R3.EMPL_RCD AND R3.VERSIONGBQDM = R.VERSIONGBQDM AND R3.JOB_EFFDT<=B.SAL_PLAN_START_DT) AND R.JOB_EFFSEQ = ( SELECT MAX(R4.JOB_EFFSEQ) FROM PS_SAL_PLN_GRP_RES R4 WHERE R.GB_GROUP_ID = R4.GB_GROUP_ID AND R.EMPLID = R4.EMPLID AND R.EMPL_RCD = R4.EMPL_RCD AND R4.VERSIONGBQDM = R.VERSIONGBQDM AND R.JOB_EFFDT = R4.JOB_EFFDT) AND A.EFFDT = ( SELECT MAX(A1.EFFDT) FROM PS_JOB A1 WHERE A.EMPLID=A1.EMPLID AND A.EMPL_RCD=A1.EMPL_RCD AND A1.EFFDT <= G.EFFDT) AND A.EFFSEQ = ( SELECT MAX(A2.EFFSEQ) FROM PS_JOB A2 WHERE A.EMPLID=A2.EMPLID AND A.EMPL_RCD=A2.EMPL_RCD AND A2.EFFDT = A.EFFDT) AND C.EMPLID = A.EMPLID AND C.EMPL_RCD = A.EMPL_RCD AND C.EFFDT = A.EFFDT AND C.EFFSEQ = A.EFFSEQ AND B.ANNIVERSARY_SW = 'N'

# 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 COMP_EFFSEQ Number(3,0) SMALLINT NOT NULL Compensation Eff Sequence
6 COMP_RATECD Character(6) VARCHAR2(6) NOT NULL Comp Rate Code
7 JOB_EFFDT Date(10) DATE Job Effective Date
8 JOB_EFFSEQ Number(3,0) SMALLINT NOT NULL Job Effective Sequence
9 EMPL_STATUS Character(1) VARCHAR2(1) NOT NULL Payroll Status
A=Active
D=Deceased
L=Leave of Absence
P=Leave With Pay
Q=Retired With Pay
R=Retired
S=Suspended
T=Terminated
U=Terminated With Pay
V=Terminated Pension Pay Out
W=Short Work Break
X=Retired-Pension Administration
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 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code
13 COMP_RATE_POINTS Number(5,0) INTEGER NOT NULL Comp Rate Points
14 COMP_PCT Number(7,3) DECIMAL(6,3) NOT NULL Comp Percent
15 RATE_CODE_GROUP Character(6) VARCHAR2(6) NOT NULL Rate Code Group Name
16 CHANGE_AMT Signed Number(20,6) DECIMAL(18,6) NOT NULL Change Amount
17 CHANGE_PCT Signed Number(8,3) DECIMAL(6,3) NOT NULL Change Percent
18 CHANGE_PTS Signed Number(6,0) DECIMAL(5) NOT NULL Change Points
19 NEW_COMP_FREQUENCY Character(5) VARCHAR2(5) NOT NULL New Compensation Frequency
A=Annual
B=Biweekly
H=Hourly
M=Monthly
S=Semimonthly
W=Weekly
20 NEW_CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL New Currency Code
21 NEW_RATE_CODE_GRP Character(6) VARCHAR2(6) NOT NULL New Rate Code Group Name
22 NEW_COMPRATE Number(19,6) DECIMAL(18,6) NOT NULL New Compensation Rate
23 NEW_COMP_RATE_PTS Number(5,0) INTEGER NOT NULL New Comp Rate Points
24 NEW_COMP_PCT Number(7,3) DECIMAL(6,3) NOT NULL New Comp Percent
25 CONVERT_COMPRT Number(19,6) DECIMAL(18,6) NOT NULL Converted Comp Rate
26 MANUAL_SW Character(1) VARCHAR2(1) NOT NULL Manual Switch
27 FTE_INDICATOR Character(1) VARCHAR2(1) NOT NULL Apply FTE for Annualization
28 CMP_SRC_IND Character(1) VARCHAR2(1) NOT NULL Rate Code Source Indicator
A=Absorbing Premium
B=Non-Absorbing Premium
C=Combination Rule
E=Seniority Pay
J=Job Code
M=Manual
N=None
P=Payroll
R=Employee Review
S=Salary Step
29 GB_GROUP_ID Character(15) VARCHAR2(15) NOT NULL Group Build ID.
30 BUDGET_START_DT Date(10) DATE Budget Start Date
31 BUDGET_ID Character(10) VARCHAR2(10) NOT NULL Budget ID