SSR_RS_SUPNBRVW

(SQL View)
 Index Back

SupervisorNumber prompt for 3C

used as prompt in COMM_VAR_DATA 3Cs subrecord

SELECT DISTINCT A.emplid ,A.SSR_RS_CANDIT_NBR ,A.ssr_Rs_super_nbr , C.NAME_DISPLAY FROM %Table(SSR_RS_SUPR_HDR) A JOIN %Table(SSR_RS_SUPRVSRS) B ON A.emplid=B.emplid AND A.institution=B.institution AND A.SSR_RS_CANDIT_NBR=B.SSR_RS_CANDIT_NBR AND A.SSR_RS_SUPER_NBR = B.SSR_RS_SUPER_NBR LEFT OUTER JOIN %Table(PERSON_NAME) C ON B.ssr_Rs_super_id= C.EMPLID WHERE B.EFFDT = ( SELECT MAX(ef1.EFFDT) FROM %Table(SSR_RS_SUPRVSRS) ef1 WHERE ef1.emplid = B.emplid AND ef1.institution = B.institution AND ef1.SSR_RS_CANDIT_NBR = B.SSR_RS_CANDIT_NBR AND ef1.SSR_RS_SUPER_NBR = B.SSR_RS_SUPER_NBR ) AND B.EFFSEQ = ( SELECT MAX(ef2.EFFSEQ) FROM %Table(SSR_RS_SUPRVSRS) ef2 WHERE ef2.emplid = B.emplid AND ef2.institution = B.institution AND ef2.SSR_RS_CANDIT_NBR = B.SSR_RS_CANDIT_NBR AND ef2.SSR_RS_SUPER_NBR = B.SSR_RS_SUPER_NBR AND ef2.EFFDT = B.effdt )

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
2 SSR_RS_CANDIT_NBR Character(12) VARCHAR2(12) NOT NULL Last Candidate Number
3 SSR_RS_SUPER_NBR Number(3,0) SMALLINT NOT NULL Last Candidate Number
4 DESCR100 Character(100) VARCHAR2(100) NOT NULL Length 100 Description