GRP_INC_CMP_VW

(SQL View)
 Index Back

Budgeting by Group View

This view will be used in Salary Planning by Group in the calculation of the increases for each employee in the group

SELECT R.GB_GROUP_ID , B.BUDGET_START_DT , B.BUDGET_ID , C.COMP_RATECD , C.COMP_FREQUENCY , C.CURRENCY_CD , C.FTE_INDICATOR , J.FTE , %Round(SUM(C.COMPRATE),3) , COUNT(*) FROM PS_COMPENSATION C , PS_SAL_PLN_GRP_RES R , PS_GRP_INCR_BUDGT1 B , PS_JOB J WHERE C.EMPLID = R.EMPLID AND C.EMPL_RCD = R.EMPL_RCD AND C.EFFDT = R.JOB_EFFDT AND C.EFFSEQ = R.JOB_EFFSEQ AND C.EMPLID = J.EMPLID AND C.EMPL_RCD = J.EMPL_RCD AND C.EFFDT = J.EFFDT AND C.EFFSEQ = J.EFFSEQ AND C.EFFDT = ( SELECT MAX(C2.EFFDT) FROM PS_COMPENSATION C2 WHERE C.EMPLID = C2.EMPLID AND C.EMPL_RCD = C2.EMPL_RCD AND C.EFFDT <= B.BUDGET_START_DT) AND C.EFFSEQ = ( SELECT MAX(C3.EFFSEQ) FROM PS_COMPENSATION C3 WHERE C.EMPLID = C3.EMPLID AND C.EMPL_RCD = C3.EMPL_RCD AND C.EFFDT <= C3.EFFDT) AND B.GB_GROUP_ID = R.GB_GROUP_ID AND R.VERSIONGBQDM = ( SELECT MAX(R2.VERSIONGBQDM) FROM PS_SAL_PLN_GRP_RES R2 WHERE R.GB_GROUP_ID = R2.GB_GROUP_ID) GROUP BY R.GB_GROUP_ID, B.BUDGET_START_DT, B.BUDGET_ID, C.COMP_RATECD, C.COMP_FREQUENCY, C.CURRENCY_CD,C.FTE_INDICATOR, J.FTE

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 GB_GROUP_ID Character(15) VARCHAR2(15) NOT NULL Group Build ID.
2 BUDGET_START_DT Date(10) DATE Budget Period Start Date
3 BUDGET_ID Character(10) VARCHAR2(10) NOT NULL Budget ID
4 COMP_RATECD Character(6) VARCHAR2(6) NOT NULL Comp Rate Code
5 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
6 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code
7 FTE_INDICATOR Character(1) VARCHAR2(1) NOT NULL Apply FTE for Annualization
8 FTE Number(8,6) DECIMAL(7,6) NOT NULL This field represent Full Time Equivalence
9 ANNUAL_RT Number(19,3) DECIMAL(18,3) NOT NULL Annual Rate
10 TOTAL_COUNT Number(7,0) INTEGER NOT NULL Total Count