SSR_RS_CONS_VW

(SQL View)
 Index Back

Research consumption view

Used in matriculation edits equation

SELECT DISTINCT CN.EMPLID ,CN.INSTITUTION , CN.ssr_Rs_candit_nbr , CA.ACAD_PROG , CA.ACAD_PLAN , %Coalesce(PL.SSR_RS_CNSUM_MNLVL, PR.SSR_RS_CNSUM_MNLVL) , %Coalesce(PL.SSR_RS_TOPIC_MNLVL, PR.SSR_RS_TOPIC_MNLVL) , %Coalesce(PL.SSR_RS_SUPER_MNLVL, PR.SSR_RS_SUPER_MNLVL) FROM PS_SSR_RS_CONSMPTN CN , PS_SSR_RS_CAND_HDR CA LEFT OUTER JOIN PS_SSR_RS_PLNSETUP PL ON CA.Institution=PL.institution AND CA.acad_plan = PL.acad_plan LEFT OUTER JOIN PS_SSR_RS_PRGSETUP PR ON CA.Institution=PR.institution AND CA.acad_plan = PR.acad_prog WHERE CN.institution = CA.institution AND CN.emplid = CA.emplid AND CN.ssr_Rs_candit_nbr = CA.ssr_rs_candit_nbr AND CN.EFFDT = ( SELECT MAX(A.EFFDT) FROM PS_SSR_RS_CONSMPTN A WHERE CN.emplid=A.emplid AND CN.institution=A.institution AND CN.ssr_Rs_candit_nbr=A.ssr_rs_candit_nbr AND A.effdt <= %CurrentDateIn) AND CN.EFFSEQ = ( SELECT MAX(B.EFFSEQ) FROM PS_SSR_RS_CONSMPTN B WHERE CN.emplid=B.emplid AND CN.institution=B.institution AND CN.ssr_Rs_candit_nbr=B.ssr_rs_candit_nbr AND CN.effdt=B.effdt) AND CN.eff_status='A' AND ( PL.EFFDT IS NULL OR ( %EffdtCheck(SSR_RS_PLNSETUP EFF1 , PL, %currentdatein) AND PL.EFF_STATUS='A') ) AND ( PR.EFFDT IS NULL OR (%EffdtCheck(SSR_RS_PRGSETUP EFF2 , PR, %currentdatein) AND PR.EFF_STATUS='A' )) AND EXISTS ( SELECT ST.ssr_Rs_status_lvl FROM PS_SSR_RS_STATUS_D ST , PS_SSR_RS_STATUS_H SH WHERE ST.institution = SH.institution AND ST.ssr_Rs_statusgrpcd = SH.ssr_Rs_statusgrpcd AND SH.ssr_rs_sysstsgrpcd = 'CONS' AND ST.institution= CN.INSTITUTION AND ST.ssr_Rs_status = CN.SSR_RS_STATUS AND ST.ssr_Rs_status_lvl >= %Coalesce(PL.SSR_RS_CNSUM_MNLVL, PR.SSR_RS_CNSUM_MNLVL) AND %EffdtCheck(SSR_RS_STATUS_D EFF3, ST,%currentdatein ))

# 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 ACAD_PROG Character(5) VARCHAR2(5) NOT NULL Academic Program
5 ACAD_PLAN Character(10) VARCHAR2(10) NOT NULL Academic Plan
6 SSR_RS_CNSUM_MNLVL Character(2) VARCHAR2(2) NOT NULL Minimum Consumption level required to denote that the section is complete.
7 SSR_RS_TOPIC_MNLVL Character(2) VARCHAR2(2) NOT NULL Minimum Reseaqrch Topic status level required to denote that the section is complete.
8 SSR_RS_SUPER_MNLVL Character(2) VARCHAR2(2) NOT NULL Minimum Supervisor Status level required to denote that the section is complete.