|
SELECT DISTINCT A.INSTITUTION , A.EMPLID , A.SSR_ACTIVITY_ID , A.SSR_ACT_ID_SEQ_NBR , A.SSR_RSLT_TYPE , A.SSR_RSLT_SEQ , A.SSR_IAM_RSLT_STAT , B.SSR_ACT_ID_ROOT , C.SSR_AC_CONTENT_TP , DD.SSR_COMPONENT , D.SSR_PERIOD_ID , D.SSR_AC_EXAM_ONLY , F.ACAD_CAREER , E.STRM , H.TERM_BEGIN_DT , E.ACAD_ORG , E.SESSION_CODE , E.CRSE_ID , E.CRSE_OFFER_NBR , E.SUBJECT , E.CATALOG_NBR , E.CLASS_NBR , I.SSR_APT_INSTANCE , L.SSR_APT_ITEM_SEQ , E.DESCR , CASE WHEN I.SSR_APT_INSTANCE = 0 THEN 9 WHEN M.EMPLID IS NOT NULL AND I.SSR_APT_INSTANCE = M.SSR_APT_INSTANCE THEN 1 WHEN I.ACAD_PROG = F.ACAD_PROG THEN 2 ELSE 3 END AS SSR_ACAD_PROG_FLAG FROM PS_SSR_IAM_RESULT A , PS_SSR_IAM B , PS_SSR_ACTIVITY C , PS_SSR_ACT_XREF D , PS_SSR_ACT_XREF DD , PS_CLASS_TBL E , PS_STDNT_ENRL F LEFT OUTER JOIN PS_SSR_APT_ENRL M ON M.EMPLID = F.EMPLID AND M.INSTITUTION = F.INSTITUTION AND M.SRC_TERM = F.STRM AND M.CLASS_NBR = F.CLASS_NBR , PS_STDNT_CAR_TERM G , PS_SSR_IAM_PE_EFF H , PS_ACAD_PROG I , PS_SSR_AC_RSLTMAP J , PS_SSR_RSLT_ITMTYP K , PS_SSR_IAM_APTSQVW L WHERE A.SSR_INCL_IN_CALC = 'Y' AND A.SSR_RSLT_SEQ = ( SELECT MAX(A2.SSR_RSLT_SEQ) FROM PS_SSR_IAM_RESULT A2 WHERE A2.EMPLID = A.EMPLID AND A2.INSTITUTION = A.INSTITUTION AND A2.SSR_ACTIVITY_ID = A.SSR_ACTIVITY_ID AND A2.SSR_RSLT_TYPE = A.SSR_RSLT_TYPE) AND A.SSR_RSLT_SEQ NOT IN ( SELECT P.SSR_RSLT_SEQ FROM PS_SSR_IAM_RSLTPST P WHERE P.EMPLID = A.EMPLID AND P.INSTITUTION = A.INSTITUTION AND P.SSR_ACTIVITY_ID = A.SSR_ACTIVITY_ID AND P.SSR_ACT_ID_SEQ_NBR = A.SSR_ACT_ID_SEQ_NBR AND P.SSR_RSLT_SEQ = A.SSR_RSLT_SEQ AND (P.SSR_IAM_RSLTPST_ST = 'C' OR P.SSR_IAM_RSLTPST_ST = 'P')) AND B.EMPLID = A.EMPLID AND B.INSTITUTION = A.INSTITUTION AND B.SSR_ACTIVITY_ID = A.SSR_ACTIVITY_ID AND B.SSR_ACT_ID_SEQ_NBR = A.SSR_ACT_ID_SEQ_NBR AND C.INSTITUTION = A.INSTITUTION AND C.SSR_ACTIVITY_ID = A.SSR_ACTIVITY_ID AND C.SSR_AC_CONTENT_TP <> '1000' AND D.SSR_ACTIVITY_ID = B.SSR_ACT_ID_ROOT AND D.SSR_AC_CONTENT_TP = '1000' AND D.SSR_AC_EXAM_ONLY <> 'Y' AND DD.SSR_ACTIVITY_ID = A.SSR_ACTIVITY_ID AND E.CRSE_ID = D.CRSE_ID AND E.CRSE_OFFER_NBR = D.CRSE_OFFER_NBR AND E.STRM = D.STRM AND E.SESSION_CODE = D.SESSION_CODE AND F.EMPLID = A.EMPLID AND F.INSTITUTION = A.INSTITUTION AND F.STRM = D.STRM AND F.CLASS_NBR = E.CLASS_NBR AND F.GRADING_BASIS_ENRL <> 'NON' AND G.EMPLID = A.EMPLID AND G.INSTITUTION = A.INSTITUTION AND G.ACAD_CAREER = F.ACAD_CAREER AND G.STRM = D.STRM AND H.INSTITUTION = A.INSTITUTION AND H.ACAD_CAREER = F.ACAD_CAREER AND H.STRM = D.STRM AND I.EMPLID = A.EMPLID AND I.ACAD_CAREER = F.ACAD_CAREER AND I.EFFDT = ( SELECT MAX(I2.EFFDT) FROM PS_ACAD_PROG I2 WHERE I2.EMPLID = I.EMPLID AND I2.ACAD_CAREER = I.ACAD_CAREER AND I2.STDNT_CAR_NBR = I.STDNT_CAR_NBR AND I2.EFFDT <= H.EFFDT) AND I.EFFSEQ = ( SELECT MAX(I3.EFFSEQ) FROM PS_ACAD_PROG I3 WHERE I3.EMPLID = I.EMPLID AND I3.ACAD_CAREER = I.ACAD_CAREER AND I3.STDNT_CAR_NBR = I.STDNT_CAR_NBR AND I3.EFFDT = I.EFFDT) AND I.SSR_APT_INSTANCE <> 0 AND J.SSR_RSLT_TYPE = A.SSR_RSLT_TYPE AND J.SSR_AC_CONTX_TYPE = C.SSR_AC_CONTX_TYPE AND J.SSR_AC_CONTENT_TP = C.SSR_AC_CONTENT_TP AND J.EFFDT = ( SELECT MAX(J2.EFFDT) FROM PS_SSR_AC_RSLTMAP J2 WHERE J2.SSR_RSLT_TYPE = J.SSR_RSLT_TYPE AND J2.SSR_AC_CONTX_TYPE = J.SSR_AC_CONTX_TYPE AND J2.SSR_AC_CONTENT_TP = J.SSR_AC_CONTENT_TP AND J2.EFFDT <= H.TERM_BEGIN_DT) AND K.SSR_RSLT_TYPE = A.SSR_RSLT_TYPE AND K.SSR_ITEM_TYPE = J.SSR_ITEM_TYPE AND K.SSR_RSLT_PEAM = 'Y' AND K.EFFDT = ( SELECT MAX(K2.EFFDT) FROM PS_SSR_RSLT_ITMTYP K2 WHERE K2.SSR_RSLT_TYPE = K.SSR_RSLT_TYPE AND K2.SSR_ITEM_TYPE = K.SSR_ITEM_TYPE AND K2.EFFDT <= H.TERM_BEGIN_DT) AND L.EMPLID = A.EMPLID AND L.INSTITUTION = A.INSTITUTION AND L.CLASS_NBR = E.CLASS_NBR AND L.SSR_ITEM_TYPE = J.SSR_ITEM_TYPE AND (L.SSR_COMPONENT = DD.SSR_COMPONENT OR L.SSR_COMPONENT IS NULL)
|
INSTITUTION