Query SQL:

SELECT K.LAST_NAME, K.FIRST_NAME, K.MIDDLE_NAME, A.EMPLID, A.ACAD_CAREER, decode ( A.ACAD_LEVEL_BOT, '00', 'Not Set') ||
decode ( A.ACAD_LEVEL_BOT, '11', 'Lower Freshman') ||
decode ( A.ACAD_LEVEL_BOT, '15', 'Upper Freshman')||  
decode ( A.ACAD_LEVEL_BOT, '21', 'Lower Sophmore') ||
decode ( A.ACAD_LEVEL_BOT, '25', 'Upper Sophmore')||   decode ( A.ACAD_LEVEL_BOT, '31', 'Lower Junior') ||
decode ( A.ACAD_LEVEL_BOT, '35', 'Upper Junior') ||   decode ( A.ACAD_LEVEL_BOT, '41', 'Lower Senior') ||
decode ( A.ACAD_LEVEL_BOT, '45', 'Upper Senior') ||  
decode ( A.ACAD_LEVEL_BOT, '52', 'Second Degree') ||  
decode ( A.ACAD_LEVEL_BOT, 'GR', 'Graduate'), B.ACAD_PROG, B.ADMIT_TERM, D.EMAIL_ADDR, E.ACAD_PLAN, F.ACAD_SUB_PLAN, G.ACAD_PLAN, H.ACAD_SUB_PLAN, I.ACAD_PLAN, J.ACAD_SUB_PLAN
  FROM PS_STDNT_CAR_TERM A, PS_ACAD_PROG B, PS_SCC_EMAIL_QVW D, PS_ACAD_PLAN E, PS_ACAD_SUBPLAN F, PS_ACAD_PLAN G, PS_ACAD_SUBPLAN H, PS_ACAD_PLAN I, PS_ACAD_SUBPLAN J, PS_CU_BIO_VW K
  WHERE ( A.INSTITUTION = :1
     AND A.UNT_TAKEN_PRGRSS > 0
     AND A.EMPLID = B.EMPLID
     AND A.ACAD_CAREER = B.ACAD_CAREER
     AND A.INSTITUTION = B.INSTITUTION
     AND B.STDNT_CAR_NBR = A.STDNT_CAR_NBR
     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 <= SYSDATE)
    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 A.ACAD_CAREER = :2
     AND A.STRM = :3
     AND A.EMPLID =  D.EMPLID (+)
     AND 'CAMP' =  D.E_ADDR_TYPE (+)
     AND B.EMPLID =  E.EMPLID (+)
     AND B.ACAD_CAREER =  E.ACAD_CAREER (+)
     AND B.STDNT_CAR_NBR =  E.STDNT_CAR_NBR (+)
     AND B.EFFSEQ =  E.EFFSEQ (+)
     AND B.EFFDT =  E.EFFDT (+)
     AND 10 =  E.PLAN_SEQUENCE (+)
     AND E.EMPLID =  F.EMPLID (+)
     AND E.ACAD_CAREER =  F.ACAD_CAREER (+)
     AND E.STDNT_CAR_NBR =  F.STDNT_CAR_NBR (+)
     AND E.EFFSEQ =  F.EFFSEQ (+)
     AND E.ACAD_PLAN =  F.ACAD_PLAN (+)
     AND E.EFFDT =  F.EFFDT (+)
     AND B.EMPLID =  G.EMPLID (+)
     AND B.ACAD_CAREER =  G.ACAD_CAREER (+)
     AND B.STDNT_CAR_NBR =  G.STDNT_CAR_NBR (+)
     AND B.EFFSEQ =  G.EFFSEQ (+)
     AND B.EFFDT =  G.EFFDT (+)
     AND 20 =  G.PLAN_SEQUENCE (+)
     AND G.EMPLID =  H.EMPLID (+)
     AND G.ACAD_CAREER =  H.ACAD_CAREER (+)
     AND G.STDNT_CAR_NBR =  H.STDNT_CAR_NBR (+)
     AND G.EFFSEQ =  H.EFFSEQ (+)
     AND G.ACAD_PLAN =  H.ACAD_PLAN (+)
     AND G.EFFDT =  H.EFFDT (+)
     AND B.EMPLID =  I.EMPLID (+)
     AND B.ACAD_CAREER =  I.ACAD_CAREER (+)
     AND B.STDNT_CAR_NBR =  I.STDNT_CAR_NBR (+)
     AND B.EFFSEQ =  I.EFFSEQ (+)
     AND B.EFFDT =  I.EFFDT (+)
     AND 30 =  I.PLAN_SEQUENCE (+)
     AND I.EMPLID =  J.EMPLID (+)
     AND I.ACAD_CAREER =  J.ACAD_CAREER (+)
     AND I.STDNT_CAR_NBR =  J.STDNT_CAR_NBR (+)
     AND I.EFFSEQ =  J.EFFSEQ (+)
     AND I.ACAD_PLAN =  J.ACAD_PLAN (+)
     AND I.EFFDT =  J.EFFDT (+)
     AND A.EMPLID =  K.EMPLID (+) )
  ORDER BY 1, 2, 3
  • No labels