RS_SRCH_INFO_VW

(SQL View)
 Index Back

Active Worker Search

This view is used to show any worker that has been ever activated in Resource Management. They also have an EMPL_STATUS of Active on the JOB table.

SELECT A.EMPLID , J.LOCATION , E.DESCR , J.JOBCODE , F.DESCR FROM %Table(SET_CNTRL_REC) D , %Table(SET_CNTRL_REC) B , %Table(JOBCODE_TBL) F , %Table(LOCATION_TBL) E , %Table(JOB) J , %Table(RS_WRKR_EFFDT) C ,%Table(RS_WORKER_TBL) A WHERE A.EMPLID = C.EMPLID AND C.EFFDT = ( SELECT MAX(M.EFFDT) FROM %Table(RS_WRKR_EFFDT) M WHERE M.EMPLID = A.EMPLID AND M.EFFDT <= %CurrentDateIn) AND C.EMPLID = J.EMPLID AND C.EMPL_RCD = J.EMPL_RCD AND J.EFFDT = ( SELECT MAX(H.EFFDT) FROM %Table(JOB) H WHERE J.EMPLID = H.EMPLID AND J.EMPL_RCD = H.EMPL_RCD AND H.EMPL_STATUS = 'A' AND H.EFFDT <= %CurrentDateIn) AND J.EFFSEQ = ( SELECT MAX(I.EFFSEQ) FROM %Table(JOB) I WHERE J.EMPLID = I.EMPLID AND J.EMPL_RCD = I.EMPL_RCD AND J.EFFDT = I.EFFDT AND I.EMPL_STATUS = 'A' ) AND J.EMPL_STATUS = 'A' AND J.BUSINESS_UNIT = D.SETCNTRLVALUE AND D.RECNAME = 'LOCATION_TBL' AND D.SETID = E.SETID AND J.LOCATION = E.LOCATION AND E.EFFDT = ( SELECT MAX(G.EFFDT) FROM %Table(LOCATION_TBL) G WHERE E.LOCATION = G.LOCATION AND G.SETID = D.SETID AND G.EFF_STATUS = 'A' AND G.EFFDT <= %CurrentDateIn) AND E.EFF_STATUS = 'A' AND J.BUSINESS_UNIT = B.SETCNTRLVALUE AND B.RECNAME = 'JOBCODE_TBL' AND B.SETID = F.SETID AND J.JOBCODE = F.JOBCODE AND F.EFFDT = ( SELECT MAX(K.EFFDT) FROM %Table(JOBCODE_TBL) K WHERE F.JOBCODE = K.JOBCODE AND F.SETID = K.SETID AND K.EFF_STATUS = 'A' AND K.EFFDT <= %CurrentDateIn) AND F.EFF_STATUS = 'A'

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
2 LOCATION Character(10) VARCHAR2(10) NOT NULL Location Code
3 LOCATION_NAME Character(30) VARCHAR2(30) NOT NULL Location Name
4 JOBCODE Character(6) VARCHAR2(6) NOT NULL Job Code

Prompt Table: JOBCODE_TBL

5 JOBDESCR Character(30) VARCHAR2(30) NOT NULL Last job