GPS_STF_ASFTE_V

(SQL View)
 Index Back

Select assigned FTE of payee

calculate assigned FTE of person from post tables per payee.

SELECT E.EMPLID , E.EMPL_RCD , SUM(E.GPS_FRACTION) GPS_FRACTION , COUNT(*) GPS_DIVIDED_FRAC FROM PS_GPS_STF_DTL_VW D JOIN PS_GPS_POST_EE E ON D.EFFDT = E.EFFDT AND D.EFFSEQ = E.EFFSEQ AND D.SETID = E.SETID AND D.GPS_POST_ID = E.GPS_POST_ID WHERE D.GPS_STATUS IN ('R', 'O', 'B', 'S') AND D.EFFDT = ( SELECT MAX(EFFDT) FROM PS_GPS_STF_DTL_VW DD WHERE DD.SETID = E.SETID AND DD.EFFDT <=%CurrentDateIn AND DD.GPS_POST_ID = D.GPS_POST_ID) AND D.EFFSEQ = ( SELECT MAX(EFFSEQ) FROM PS_GPS_STF_DTL_VW DDD WHERE DDD.EFFDT = D.EFFDT AND DDD.SETID = D.SETID AND DDD.GPS_POST_ID = D.GPS_POST_ID) GROUP BY E.EMPLID, E.EMPL_RCD

# 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 GPS_FRACTION Number(9,2) DECIMAL(8,2) NOT NULL GPS Fraction
4 GPS_DIVIDED_FRAC Number(2,0) SMALLINT NOT NULL Number of EE splitted .