SELECT B.EMPLID, E.LAST_NAME, E.FIRST_NAME, E.MIDDLE_NAME, B.ACAD_PROG, C.ACAD_PLAN, E.EMAIL_ADDR, E.PHONE, F.SEX, G.COUNTRY, G.ADDRESS1, G.ADDRESS2, G.CITY, G.STATE, G.POSTAL, B.PROG_STATUS, B.ADMIT_TERM, H.UNT_TAKEN_PRGRSS, H.TOT_CUMULATIVE, H.CUM_GPA, H.ACAD_LEVEL_BOT, I.SUBJECT, I.CATALOG_NBR, I.SESSION_CODE, I.CLASS_SECTION, I.DESCR, I.UNT_TAKEN, I.CRSE_GRADE_OFF, J.ITEM_TYPE, J.ITEM_AMT, J.APPLIED_AMT, K.DESCR,I.EMPLID,I.ACAD_CAREER,I.INSTITUTION,I.STRM,I.CLASS_NBR,K.SETID,K.ITEM_TYPE,TO_CHAR(K.EFFDT,'YYYY-MM-DD') FROM PS_TERM_TBL A, PS_ACAD_PROG B, PS_ACAD_PLAN C, PS_STDNT_GRPS_HIST D, PS_CU_BIO_VW E, PS_SF_STDNT_PRS_VW F, PS_ADDRESSES4_VW G, PS_STDNT_CAR_TERM H, PS_CLASS_TBL_SE_VW I, PS_ITEM_SF J, PS_ITEM_TYPE_TBL K WHERE ( A.INSTITUTION = :1 AND A.ACAD_CAREER = :2 AND A.STRM = :3 AND A.INSTITUTION = B.INSTITUTION AND A.ACAD_CAREER = B.ACAD_CAREER AND B.EFFDT = (SELECT MAX(B_ED.EFFDT) FROM PS_ACAD_PROG B_ED WHERE B.EMPLID = B_ED.EMPLID AND B.ACAD_CAREER = B_ED.ACAD_CAREER AND B.STDNT_CAR_NBR = B_ED.STDNT_CAR_NBR AND B_ED.EFFDT <= A.TERM_BEGIN_DT) AND B.EFFSEQ = (SELECT MAX(B_ES.EFFSEQ) FROM PS_ACAD_PROG B_ES WHERE B.EMPLID = B_ES.EMPLID AND B.ACAD_CAREER = B_ES.ACAD_CAREER AND B.STDNT_CAR_NBR = B_ES.STDNT_CAR_NBR AND B.EFFDT = B_ES.EFFDT) AND B.EMPLID = C.EMPLID AND B.ACAD_CAREER = C.ACAD_CAREER AND B.STDNT_CAR_NBR = C.STDNT_CAR_NBR AND B.EFFSEQ = C.EFFSEQ AND B.EFFDT = C.EFFDT AND B.EMPLID = D.EMPLID AND D.INSTITUTION = B.INSTITUTION AND D.EFFDT = (SELECT MAX(D_ED.EFFDT) FROM PS_STDNT_GRPS_HIST D_ED WHERE D.EMPLID = D_ED.EMPLID AND D.INSTITUTION = D_ED.INSTITUTION AND D.STDNT_GROUP = D_ED.STDNT_GROUP AND D_ED.EFFDT <= A.TERM_BEGIN_DT) AND D.STDNT_GROUP = :4 AND D.EFF_STATUS = 'A' AND B.EMPLID = E.EMPLID AND B.EMPLID = F.EMPLID AND B.EMPLID = G.EMPLID (+) AND 'HOME' = G.ADDRESS_TYPE (+) AND B.EMPLID = H.EMPLID AND B.ACAD_CAREER = H.ACAD_CAREER AND B.STDNT_CAR_NBR = H.STDNT_CAR_NBR AND H.INSTITUTION = B.INSTITUTION AND H.STRM = :3 AND H.EMPLID = I.EMPLID AND H.ACAD_CAREER = I.ACAD_CAREER AND H.INSTITUTION = I.INSTITUTION AND H.STRM = I.STRM AND I.STDNT_ENRL_STATUS = 'E' AND B.EMPLID = J.EMPLID AND B.INSTITUTION = J.BUSINESS_UNIT AND A.STRM = J.ACCOUNT_TERM AND K.ITEM_TYPE = J.ITEM_TYPE AND K.EFFDT = (SELECT MAX(K_ED.EFFDT) FROM PS_ITEM_TYPE_TBL K_ED WHERE K.SETID = K_ED.SETID AND K.ITEM_TYPE = K_ED.ITEM_TYPE AND K_ED.EFFDT <= SYSDATE) AND J.BUSINESS_UNIT = K.SETID ) ORDER BY 2, 3, 4 |