RS_WC_WRKR_BNCH

(SQL View)
 Index Back

Unassigned Resources


SELECT DISTINCT A.EMPLID , ' ' , MIN(B.EFFDT) , %DateDiff(MIN(B.EFFDT),%CurrentDateIn) , A.BUSINESS_UNIT , ' ' , A.SUPERVISOR_ID , A.DEPTID , A.LOCATION FROM PS_JOB A , PS_RS_WRKR_EFFDT B , PS_RS_WORKER_TBL C WHERE A.EMPLID = B.EMPLID AND A.EMPLID = C.EMPLID AND B.SYSTEM_SOURCE = 'RS' AND B.EFFDT = ( SELECT MIN(D.EFFDT) FROM PS_RS_WRKR_EFFDT D WHERE D.EMPLID = B.EMPLID AND D.SYSTEM_SOURCE = B.SYSTEM_SOURCE) AND A.EMPL_RCD = B.EMPL_RCD AND A.EFFDT = ( SELECT MAX(E.EFFDT) FROM PS_JOB E WHERE E.EMPLID = A.EMPLID AND E.EMPL_RCD = A.EMPL_RCD AND E.EFFDT <= %CurrentDateIn) AND A.EFFSEQ = ( SELECT MAX(F.EFFSEQ) FROM PS_JOB F WHERE F.EMPLID = A.EMPLID AND F.EMPL_RCD = A.EMPL_RCD AND F.EFFDT = A.EFFDT) AND A.EMPL_STATUS NOT IN ('I','T','S','D','U','V','Q','R','X') GROUP BY A.EMPLID,A.BUSINESS_UNIT,A.SUPERVISOR_ID,A.DEPTID,A.LOCATION

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
2 RS_APPT_ID Character(15) VARCHAR2(15) NOT NULL Appointment Id
3 RS_END_DATE Date(10) DATE End date
4 DURATION Number(5,0) INTEGER NOT NULL Recurrence Duration
5 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
6 PROJ_ROLE Character(15) VARCHAR2(15) NOT NULL Project Role
7 SUPERVISOR_ID Character(11) VARCHAR2(11) NOT NULL Supervisor ID
8 DEPTID Character(10) VARCHAR2(10) NOT NULL Department
9 LOCATION Character(10) VARCHAR2(10) NOT NULL Location Code