GRP_INC_CMP3_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.COMPANY , J.FTE , SUM(C.COMP_RATE_POINTS) , COUNT(*) FROM PS_COMPENSATION C , PS_SAL_PLN_GRP_RES R , PS_GRP_INCR_BUDGT1 B , PS_JOB J WHERE C.EMPLID = J.EMPLID AND C.EMPL_RCD = J.EMPL_RCD AND C.EFFDT = J.EFFDT AND C.EFFSEQ = J.EFFSEQ AND 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.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.COMPANY,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 COMPANY Character(3) VARCHAR2(3) NOT NULL Company
9 FTE Number(8,6) DECIMAL(7,6) NOT NULL This field represent Full Time Equivalence
10 COMP_RATE_POINTS Number(5,0) INTEGER NOT NULL Comp Rate Points
11 TOTAL_COUNT Number(7,0) INTEGER NOT NULL Total Count