RS_NON_WRKR_WM

(SQL View)
 Index Back

Non-Resource WM View

SUJ--This is a clone of RS_NON_WRKR_VW.The SQL modified a little to ensure employees who are not yet resources under product type (SYSTEM_SOURCE = 'WM') are only listed.

SELECT A.EMPLID , A.LAST_NAME_SRCH , A.FIRST_NAME_SRCH , A.PER_STATUS FROM %Table(PERSONAL_DATA) A , %Table(JOB) B WHERE A.EMPLID = B.EMPLID AND B.EFFDT = ( SELECT MAX(C.EFFDT) FROM %Table(JOB) C WHERE B.EMPLID = C.EMPLID AND B.EMPL_RCD = C.EMPL_RCD AND C.EFFDT <= %CurrentDateIn) AND B.EFFSEQ = ( SELECT MAX(D.EFFSEQ) FROM %Table(JOB) D WHERE B.EMPLID = D.EMPLID AND B.EMPL_RCD = D.EMPL_RCD AND B.EFFDT = D.EFFDT ) AND B.EMPL_RCD = ( SELECT MIN(A1.EMPL_RCD) FROM %Table(JOB) A1 WHERE A1.EMPLID = A.EMPLID AND A1.EMPL_STATUS = 'A' AND A1.EFFDT = ( SELECT MAX(A11.EFFDT) FROM %Table(JOB) A11 WHERE A11.EMPLID = A1.EMPLID AND A11.EMPL_RCD = A1.EMPL_RCD AND A11.EFFDT <= %CurrentDateIn) AND A1.EFFSEQ = ( SELECT MAX(A12.EFFSEQ) FROM %Table(JOB) A12 WHERE A12.EMPLID = A1.EMPLID AND A12.EMPL_RCD = A1.EMPL_RCD AND A12.EFFDT = A1.EFFDT)) AND B.EMPL_STATUS = 'A' AND A.EMPLID NOT IN ( SELECT EMPLID FROM PS_RS_EMPLID_TBL WHERE PERSON_ID <> ' ') AND A.EMPLID NOT IN ( SELECT A.EMPLID FROM %Table(RS_WORKER_TBL) A ,%Table(RS_WRKR_EFFDT) A1 WHERE A1.EMPLID = A.EMPLID AND A1.SYSTEM_SOURCE = 'WM')

# 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 LAST_NAME_SRCH Character(30) VARCHAR2(30) NOT NULL Last Name
3 FIRST_NAME_SRCH Character(30) VARCHAR2(30) NOT NULL First Name
4 PER_STATUS Character(1) VARCHAR2(1) NOT NULL Personnel Status
E=Employee
N=Non-Employee