RS_DF_WO_ELG_VW

(SQL View)
 Index Back

Active Resources

This view lists all Resources whose status as of date is not 'I','D','S','T','U','V'.

SELECT A.EMPLID , A.LONGITUDE , A.LONG_COMPASS , A.LATITUDE , A. LAT_COMPASS , A.DISTANCE , B.EMPL_RCD , C.STD_HOURS , C.STD_HRS_FREQUENCY , A.SUN , A.MON , A.TUES , A.WED , A.THURS , A.FRI , A.SAT FROM %Table(RS_WORKER_TBL) A , %Table(RS_WRKR_EFFDT) B , %Table(JOB) C WHERE B.EMPLID = A.EMPLID AND B.SYSTEM_SOURCE = 'RS' AND B.EFFDT = ( SELECT MAX(D.EFFDT) FROM %Table(RS_WRKR_EFFDT) D WHERE D.EMPLID = B.EMPLID AND D.SYSTEM_SOURCE = B.SYSTEM_SOURCE AND D.EFFDT <= %CurrentDateIn ) AND C.EMPLID = B.EMPLID AND C.EMPL_RCD = B.EMPL_RCD AND C.EFFDT = ( SELECT MAX(E.EFFDT) FROM %Table(JOB) E WHERE E.EMPLID = C.EMPLID AND E.EMPL_RCD = C.EMPL_RCD AND E.EFFDT <= %CurrentDateIn) AND C.EFFSEQ = ( SELECT MAX(F.EFFSEQ) FROM %Table(JOB) F WHERE F.EMPLID = C.EMPLID AND F.EMPL_RCD = C.EMPL_RCD AND F.EFFDT = C.EFFDT) AND C.EMPL_STATUS NOT IN ('I','D','S','T','U','V','Q','R','X')

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID

Prompt Table: PERSONAL_DATA
Set Control Field: EMPLID

2 LONGITUDE Signed Number(12,7) DECIMAL(10,7) NOT NULL Longitude
3 LONG_COMPASS Character(1) VARCHAR2(1) NOT NULL Longitude Compass
4 LATITUDE Signed Number(12,7) DECIMAL(10,7) NOT NULL Latitude
5 LAT_COMPASS Character(1) VARCHAR2(1) NOT NULL Latitude Compass
6 DISTANCE Number(8,2) DECIMAL(7,2) NOT NULL Distance Traveled
7 EMPL_RCD Number(3,0) SMALLINT NOT NULL Empl Rcd Nbr
8 STD_HOURS Number(7,2) DECIMAL(6,2) NOT NULL Standard Hours
9 STD_HRS_FREQUENCY Character(5) VARCHAR2(5) NOT NULL Standard Work Period
10 SUN Character(1) VARCHAR2(1) NOT NULL Sunday
11 MON Character(1) VARCHAR2(1) NOT NULL Monday
12 TUES Character(1) VARCHAR2(1) NOT NULL Tuesday
13 WED Character(1) VARCHAR2(1) NOT NULL Wednesday
14 THURS Character(1) VARCHAR2(1) NOT NULL Thursday
15 FRI Character(1) VARCHAR2(1) NOT NULL Friday
16 SAT Character(1) VARCHAR2(1) NOT NULL Saturday