SELECT DISTINCT C.LAST_NAME, C.FIRST_NAME, C.MIDDLE_NAME, A.EMPLID, A.ACAD_CAREER, A.ACAD_PROG, B.ACAD_PLAN, D.SEX, C.EMAIL_ADDR, F.DESCR, G.VISA_PERMIT_TYPE, I.DESCR,F.COUNTRY,F.CITIZENSHIP_STATUS,I.EXT_ORG_ID,TO_CHAR(I.EFFDT,'YYYY-MM-DD')
FROM PS_ACAD_PROG A, PS_ACAD_PLAN B, PS_CU_BIO_VW C, PS_SF_STDNT_PRS_VW D, PS_SCC_CITIZEN_QVW E, PS_CITIZEN_STS_TBL F, (SELECT TG.VISA_PERMIT_TYPE, TG.EMPLID, TG.COUNTRY
FROM PS_VISA_PMT_DATA TG,PS_PERALL_SEC_QRY G1 WHERE TG.EMPLID = G1.EMPLID AND G1.OPRID = '11002650' ) G, (SELECT TH.LAST_SCH_ATTEND, TH.EMPLID, TH.ACAD_CAREER, TH.ADM_APPL_NBR
FROM PS_ADM_APPL_DATA TH,PS_ADM_APPL_SCTY H1 WHERE TH.EMPLID = H1.EMPLID AND TH.ACAD_CAREER = H1.ACAD_CAREER AND TH.ADM_APPL_NBR = H1.ADM_APPL_NBR AND H1.OPRCLASS = 'CUHCPPBAR01' AND H1.OPRID = '11002650' ) H, PS_EXT_ORG_VW I
WHERE ( A.EFFDT =
(SELECT MAX(A_ED.EFFDT) FROM PS_ACAD_PROG A_ED
WHERE A.EMPLID = A_ED.EMPLID
AND A.ACAD_CAREER = A_ED.ACAD_CAREER
AND A.STDNT_CAR_NBR = A_ED.STDNT_CAR_NBR
AND A_ED.EFFDT <= SYSDATE)
AND A.EFFSEQ =
(SELECT MAX(A_ES.EFFSEQ) FROM PS_ACAD_PROG A_ES
WHERE A.EMPLID = A_ES.EMPLID
AND A.ACAD_CAREER = A_ES.ACAD_CAREER
AND A.STDNT_CAR_NBR = A_ES.STDNT_CAR_NBR
AND A.EFFDT = A_ES.EFFDT)
AND A.EMPLID = B.EMPLID
AND A.ACAD_CAREER = B.ACAD_CAREER
AND A.STDNT_CAR_NBR = B.STDNT_CAR_NBR
AND A.EFFSEQ = B.EFFSEQ
AND A.EFFDT = B.EFFDT
AND A.INSTITUTION = 'BAR01'
AND A.PROG_STATUS = 'AC'
AND ( A.ACAD_PROG = 'MBA'
OR B.ACAD_PLAN IN ('FIN-MS','ENTPR-MS','IS-MS','MKT-MS','OPRR-MS','RESR-MS','STAT-MS','TAX-MS','ACC-MS','FSIORMR-MS','ILR-MS'))
AND A.EMPLID = C.EMPLID
AND A.ADMIT_TERM BETWEEN :1 AND :2
AND A.EMPLID = D.EMPLID (+)
AND A.EMPLID = E.EMPLID (+)
AND 'USA' = E.COUNTRY (+)
AND E.COUNTRY = F.COUNTRY (+)
AND E.CITIZENSHIP_STATUS = F.CITIZENSHIP_STATUS (+)
AND A.EMPLID = G.EMPLID (+)
AND 'USA' = G.COUNTRY (+)
AND A.EMPLID = H.EMPLID (+)
AND A.ACAD_CAREER = H.ACAD_CAREER (+)
AND A.ADM_APPL_NBR = H.ADM_APPL_NBR (+)
AND H.LAST_SCH_ATTEND = I.EXT_ORG_ID (+) )
ORDER BY 1, 2, 3
  • No labels