GPAU_LMP_TOTVW

(SQL View)
 Index Back

STP LumpE Total View

STP LumpE Total View

SELECT A.EMPLID , C.PAY_ENTITY , B.BALANCE_GRP_NUM , D.PRD_BGN_DT , D.PRD_END_DT , SUM(A.GP_AMT) FROM PS_GP_PI_MNL_DATA A ,PS_GP_CAL_RUN_DTL D , PS_JOB_JR B , PS_GP_PYGRP C WHERE B.EMPLID = A.EMPLID AND B.EMPL_RCD = A.EMPL_RCD AND A.CAL_ID = D.CAL_ID AND A.GP_PAYGROUP = D.GP_PAYGROUP AND C.GP_PAYGROUP = A.GP_PAYGROUP AND B.EFFDT = ( SELECT MAX(B_ED.EFFDT) FROM PS_JOB_JR B_ED WHERE B.EMPLID = B_ED.EMPLID AND B.EMPL_RCD = B_ED.EMPL_RCD AND B_ED.EFFDT <= %CurrentDateIn ) AND B.EFFSEQ = ( SELECT MAX(B_ES.EFFSEQ) FROM PS_JOB_JR B_ES WHERE B.EMPLID = B_ES.EMPLID AND B.EMPL_RCD = B_ES.EMPL_RCD AND B.EFFDT = B_ES.EFFDT ) AND C.GP_PAYGROUP = A.GP_PAYGROUP AND A.PIN_NUM IN ( SELECT D.PIN_MBR_NUM FROM PS_GP_ACM_MBR D , PS_GP_PIN E WHERE D.PIN_NUM = E.PIN_NUM AND E.PIN_CODE = 'RETRO PI PTD AUS' ) GROUP BY A.EMPLID, C.PAY_ENTITY , B.BALANCE_GRP_NUM,D.PRD_BGN_DT , D.PRD_END_DT

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
2 PAY_ENTITY Character(10) VARCHAR2(10) NOT NULL Pay Entity
3 BALANCE_GRP_NUM Character(3) VARCHAR2(3) NOT NULL This field will be used to determine the grouping of balances from the different jobs of the employee.
4 PRD_BGN_DT Date(10) DATE Period Begin Date
5 PRD_END_DT Date(10) DATE Pay Period End Date
6 GP_AMT Signed Number(20,6) DECIMAL(18,6) NOT NULL Amount