SSR_RS_TPMTR_VW

(SQL View)
 Index Back

Research Topics view

Used in matriculation edits equation

SELECT DISTINCT TPC.EMPLID , TPC.INSTITUTION , TPC.ssr_Rs_candit_nbr , TPC.ssr_Rs_topic_seq , MAX(ST2.ssr_Rs_status_lvl) FROM PS_SSR_RS_TOPIC TPC ,PS_SSR_RS_STATUS_D ST2 , PS_SSR_RS_STATUS_H SH2 WHERE TPC.EFFDT = ( SELECT MAX(TPEF1.EFFDT) FROM PS_SSR_RS_TOPIC TPEF1 WHERE TPC.emplid=TPEF1.emplid AND TPC.institution=TPEF1.institution AND TPC.ssr_Rs_candit_nbr=TPEF1.ssr_rs_candit_nbr AND TPC.SSR_RS_TOPIC_SEQ = TPEF1.SSR_RS_TOPIC_SEQ AND TPEF1.effdt <= %CurrentDateIn) AND TPC.EFFSEQ = ( SELECT MAX(TPEF2.EFFSEQ) FROM PS_SSR_RS_TOPIC TPEF2 WHERE TPC.emplid=TPEF2.emplid AND TPC.institution=TPEF2.institution AND TPC.ssr_Rs_candit_nbr=TPEF2.ssr_rs_candit_nbr AND TPC.SSR_RS_TOPIC_SEQ = TPEF2.SSR_RS_TOPIC_SEQ AND TPC.effdt=TPEF2.effdt) AND TPC.eff_status='A' AND ST2.institution = SH2.institution AND ST2.ssr_Rs_statusgrpcd = SH2.ssr_Rs_statusgrpcd AND SH2.ssr_rs_sysstsgrpcd = 'TPIC' AND ST2.institution= TPC.INSTITUTION AND ST2.ssr_Rs_status = TPC.SSR_RS_STATUS AND %EffdtCheck(SSR_RS_STATUS_D STEF2, ST2,%currentdatein ) GROUP BY TPC.INSTITUTION , TPC.EMPLID , TPC.ssr_Rs_candit_nbr, TPC.ssr_Rs_topic_seq

# 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_TOPIC_SEQ Number(3,0) SMALLINT NOT NULL This field is used to create a unique key when multiple topics exist for a research project.
5 SSR_RS_STATUS_LVL Character(2) VARCHAR2(2) NOT NULL Status level associated with the status. Used to denote a hierarchy.