SSR_RS_SUMTR_VW

(SQL View)
 Index Back

Research Supervisors view

Used in matriculation edits equation

SELECT SUP.EMPLID , SUP.INSTITUTION ,SUP.SSR_RS_CANDIT_NBR , MAX(ST1.ssr_Rs_status_lvl) FROM PS_SSR_RS_SUPRVSRS SUP , PS_SSR_RS_STATUS_D ST1 , PS_SSR_RS_STATUS_H SH1 WHERE SUP.EFFDT = ( SELECT MAX(SUPEF1.EFFDT) FROM PS_SSR_RS_SUPRVSRS SUPEF1 WHERE SUP.emplid=SUPEF1.emplid AND SUP.institution=SUPEF1.institution AND SUP.ssr_Rs_candit_nbr=SUPEF1.ssr_rs_candit_nbr AND SUP.ssr_Rs_super_nbr=SUPEF1.ssr_Rs_super_nbr AND SUPEF1.effdt <= %CurrentDateIn) AND SUP.EFFSEQ = ( SELECT MAX(SUPEF2.EFFSEQ) FROM PS_SSR_RS_SUPRVSRS SUPEF2 WHERE SUP.emplid=SUPEF2.emplid AND SUP.institution=SUPEF2.institution AND SUP.ssr_Rs_candit_nbr=SUPEF2.ssr_rs_candit_nbr AND SUP.ssr_Rs_super_nbr=SUPEF2.ssr_Rs_super_nbr AND SUP.effdt=SUPEF2.effdt) AND SUP.eff_status='A' AND ST1.institution = SH1.institution AND ST1.ssr_Rs_statusgrpcd = SH1.ssr_Rs_statusgrpcd AND SH1.ssr_rs_sysstsgrpcd = 'SUPR' AND ST1.institution= SUP.INSTITUTION AND ST1.ssr_Rs_status = SUP.SSR_RS_STATUS AND %EffdtCheck(SSR_RS_STATUS_D STEF1, ST1,%currentdatein ) GROUP BY SUP.EMPLID , SUP.INSTITUTION , SUP.SSR_RS_CANDIT_NBR

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

Prompt Table: PEOPLE_SRCH

2 INSTITUTION Character(5) VARCHAR2(5) NOT NULL Academic Institution

Prompt Table: INSTITUTION_TBL

3 SSR_RS_CANDIT_NBR Character(12) VARCHAR2(12) NOT NULL Last Candidate Number
4 SSR_RS_STATUS_LVL Character(2) VARCHAR2(2) NOT NULL Status level associated with the status. Used to denote a hierarchy.