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
  • No labels