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 |