|
SELECT DISTINCT B.STRM , B.CLASS_NBR , A.MILESTONE , G.OPRID FROM %Table(SSR_CRS_MILESTN) A , %Table(CLASS_TBL) B , %Table(SSR_MLSTATM_NZL) C , %Table(STDNT_CAR_MLSTN) D , %Table(GRADE_ROSTER) E , %Table(SCRTY_TBL_MLSTN) G WHERE A.CRSE_ID = B.CRSE_ID AND C.EMPLID = D.EMPLID AND C.INSTITUTION = D.INSTITUTION AND C.ACAD_CAREER = D.ACAD_CAREER AND C.ACAD_PROG = D.ACAD_PROG AND C.ACAD_PLAN = D.ACAD_PLAN AND C.EFFDT = D.EFFDT AND C.MILESTONE_NBR = D.MILESTONE_NBR AND A.MILESTONE = D.MILESTONE AND C.ATTEMPT_NBR = ( SELECT MAX(ATTEMPT_NBR) FROM %Table(SSR_MLSTATM_NZL) C1 WHERE C.EMPLID = C1.EMPLID AND C.INSTITUTION = C1.INSTITUTION AND C.ACAD_CAREER = C1.ACAD_CAREER AND C.ACAD_PROG = C1.ACAD_PROG AND C.ACAD_PLAN = C1.ACAD_PLAN AND C.EFFDT = C1.EFFDT AND C.MILESTONE_NBR = C1.MILESTONE_NBR) AND C.EFFDT = ( SELECT MAX(C2.EFFDT) FROM %Table(MLSTN_ATMPT) C2 WHERE C.EMPLID = C2.EMPLID AND C.INSTITUTION = C2.INSTITUTION AND C.ACAD_CAREER = C2.ACAD_CAREER AND C.ACAD_PROG = C2.ACAD_PROG AND C.ACAD_PLAN = C2.ACAD_PLAN AND C.MILESTONE_NBR = C2.MILESTONE_NBR AND C2.EFFDT <= %CurrentDateIn) AND A.EFFDT = ( SELECT MAX(EFFDT) FROM %Table(SSR_CRS_MILESTN) A1 WHERE A.CRSE_ID = A1.CRSE_ID AND A1.EFFDT <= %CurrentDateIn) AND B.STRM = E.STRM AND B.CLASS_NBR = E.CLASS_NBR AND C.EMPLID = E.EMPLID AND C.ACAD_CAREER = E.ACAD_CAREER AND B.CRSE_ID = C.CRSE_ID AND C.STRM = E.STRM AND D.INSTITUTION = G.INSTITUTION AND G.INQUIRY_USE_IND = 'Y' AND (G.MILESTONE = 'ALL' OR (D.MILESTONE = G.MILESTONE AND NOT EXISTS ( SELECT 'X' FROM %Table(SCRTY_TBL_MLSTN) G1 WHERE G1.OPRID = G.OPRID AND G1.INSTITUTION = G.INSTITUTION AND G1.MILESTONE = 'ALL' AND G1.INQUIRY_USE_IND = 'Y')))
|
STRM