SSR_HE_QAWR_VW

(SQL View)
 Index Back

Qualification ID view


SELECT A.INSTITUTION , A.SSR_HE_QUALID , ( SELECT A1.SSR_HE_TITLE FROM PS_SSR_HE_QUAL_SUP A1 WHERE A1.EFFDT=( SELECT MAX(A2.EFFDT) FROM PS_SSR_HE_QUAL_SUP A2 WHERE A2.INSTITUTION=A1.INSTITUTION AND A2.DEGREE=A1.DEGREE AND A2.ACAD_PLAN=A1.ACAD_PLAN AND A2.SSR_HE_ACAD_PLAN2=A1.SSR_HE_ACAD_PLAN2 AND A2.EFF_STATUS = A1.EFF_STATUS AND A2.EFFDT <= %CurrentDateIn ) AND A1.EFF_STATUS='A' AND A1.INSTITUTION=A.INSTITUTION AND A1.SSR_HE_QUALID=A.SSR_HE_QUALID) AS TITLE FROM PS_SSR_HE_PROG A WHERE A.EFFDT=( SELECT MAX(A1.EFFDT) FROM PS_SSR_HE_PROG A1 WHERE A1.INSTITUTION=A.INSTITUTION AND A1.ACAD_PROG=A.ACAD_PROG AND A1.EFFDT<=%CurrentDateIn) AND A.SSR_HE_QUALID<>' ' UNION SELECT A.INSTITUTION , A.SSR_HE_QUALID , ( SELECT A1.SSR_HE_TITLE FROM PS_SSR_HE_QUAL_SUP A1 WHERE A1.EFFDT=( SELECT MAX(A2.EFFDT) FROM PS_SSR_HE_QUAL_SUP A2 WHERE A2.INSTITUTION=A1.INSTITUTION AND A2.DEGREE=A1.DEGREE AND A2.ACAD_PLAN=A1.ACAD_PLAN AND A2.SSR_HE_ACAD_PLAN2=A1.SSR_HE_ACAD_PLAN2 AND A2.EFF_STATUS = A1.EFF_STATUS AND A2.EFFDT <= %CurrentDateIn ) AND A1.EFF_STATUS='A' AND A1.INSTITUTION=A.INSTITUTION AND A1.SSR_HE_QUALID=A.SSR_HE_QUALID) AS TITLE FROM PS_SSR_HE_PLAN A WHERE A.EFFDT=( SELECT MAX(A1.EFFDT) FROM PS_SSR_HE_PLAN A1 WHERE A1.INSTITUTION=A.INSTITUTION AND A1.ACAD_PLAN=A.ACAD_PLAN AND A1.EFFDT<=%CurrentDateIn) AND A.SSR_HE_QUALID<>' ' UNION SELECT A.INSTITUTION , A.SSR_HE_QUALID , ( SELECT A1.SSR_HE_TITLE FROM PS_SSR_HE_QUAL_SUP A1 WHERE A1.EFFDT=( SELECT MAX(A2.EFFDT) FROM PS_SSR_HE_QUAL_SUP A2 WHERE A2.INSTITUTION=A1.INSTITUTION AND A2.DEGREE=A1.DEGREE AND A2.ACAD_PLAN=A1.ACAD_PLAN AND A2.SSR_HE_ACAD_PLAN2=A1.SSR_HE_ACAD_PLAN2 AND A2.EFF_STATUS = A1.EFF_STATUS AND A2.EFFDT <= %CurrentDateIn ) AND A1.EFF_STATUS='A' AND A1.INSTITUTION=A.INSTITUTION AND A1.SSR_HE_QUALID=A.SSR_HE_QUALID) AS TITLE FROM PS_SSR_HE_PLNOFR A WHERE A.EFFDT=( SELECT MAX(A1.EFFDT) FROM PS_SSR_HE_PLNOFR A1 WHERE A1.INSTITUTION=A.INSTITUTION AND A1.ACAD_PLAN=A.ACAD_PLAN AND A1.ACAD_LOAD_APPR=A.ACAD_LOAD_APPR AND A1.SSR_HE_OFSTRM=A.SSR_HE_OFSTRM AND A1.SSR_HE_OFSTSESN=A.SSR_HE_OFSTSESN AND A1.CAMPUS=A.CAMPUS AND A1.EFFDT<=%CurrentDateIn) AND A.SSR_HE_QUALID<>' ' UNION SELECT A.INSTITUTION , A.SSR_HE_QUALID , ( SELECT A1.SSR_HE_TITLE FROM PS_SSR_HE_QUAL_SUP A1 WHERE A1.EFFDT=( SELECT MAX(A2.EFFDT) FROM PS_SSR_HE_QUAL_SUP A2 WHERE A2.INSTITUTION=A1.INSTITUTION AND A2.DEGREE=A1.DEGREE AND A2.ACAD_PLAN=A1.ACAD_PLAN AND A2.SSR_HE_ACAD_PLAN2=A1.SSR_HE_ACAD_PLAN2 AND A2.EFF_STATUS = A1.EFF_STATUS AND A2.EFFDT <= %CurrentDateIn ) AND A1.EFF_STATUS='A' AND A1.INSTITUTION=A.INSTITUTION AND A1.SSR_HE_QUALID=A.SSR_HE_QUALID) AS TITLE FROM PS_SSR_HE_SUBPLAN A WHERE A.EFFDT=( SELECT MAX(A1.EFFDT) FROM PS_SSR_HE_SUBPLAN A1 WHERE A1.INSTITUTION=A.INSTITUTION AND A1.ACAD_PLAN=A.ACAD_PLAN AND A1.ACAD_SUB_PLAN=A.ACAD_SUB_PLAN AND A1.EFFDT<=%CurrentDateIn) AND A.SSR_HE_QUALID<>' ' UNION SELECT A.INSTITUTION , A.SSR_HE_QUALID , ( SELECT A1.SSR_HE_TITLE FROM PS_SSR_HE_QUAL_SUP A1 WHERE A1.EFFDT=( SELECT MAX(A2.EFFDT) FROM PS_SSR_HE_QUAL_SUP A2 WHERE A2.INSTITUTION=A1.INSTITUTION AND A2.DEGREE=A1.DEGREE AND A2.ACAD_PLAN=A1.ACAD_PLAN AND A2.SSR_HE_ACAD_PLAN2=A1.SSR_HE_ACAD_PLAN2 AND A2.EFF_STATUS = A1.EFF_STATUS AND A2.EFFDT <= %CurrentDateIn ) AND A1.EFF_STATUS='A' AND A1.INSTITUTION=A.INSTITUTION AND A1.SSR_HE_QUALID=A.SSR_HE_QUALID) AS TITLE FROM PS_SSR_HE_SPLNOFR A WHERE A.EFFDT=( SELECT MAX(A1.EFFDT) FROM PS_SSR_HE_SPLNOFR A1 WHERE A1.INSTITUTION=A.INSTITUTION AND A1.ACAD_PLAN=A.ACAD_PLAN AND A1.ACAD_SUB_PLAN=A.ACAD_SUB_PLAN AND A1.SSR_HE_OFSTRM=A.SSR_HE_OFSTRM AND A1.SSR_HE_OFSTSESN=A.SSR_HE_OFSTSESN AND A1.CAMPUS=A.CAMPUS AND A1.EFFDT<=%CurrentDateIn) AND A.SSR_HE_QUALID<>' '

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 INSTITUTION Character(5) VARCHAR2(5) NOT NULL Academic Institution

Prompt Table: INSTITUTION_TBL

2 SSR_HE_QUALID Character(50) VARCHAR2(50) NOT NULL Qualification ID
3 SSR_HE_TITLE Character(250) VARCHAR2(250) NOT NULL HESA Title