Results

 

 
SELECT D.LAST_NAME, D.FIRST_NAME, D.MIDDLE_NAME, B.EMPLID, B.ACAD_CAREER, B.ACAD_PROG, B.PROG_ACTION, C.ACAD_PLAN, TO_CHAR(B.ACTION_DT,'YYYY-MM-DD'), B.ADMIT_TERM, A.ADMIT_TYPE, A.LAST_SCH_ATTEND, D.PHONE, D.EMAIL_ADDR, E.ADDRESS1, E.ADDRESS2, E.CITY, E.STATE, E.POSTAL, E.COUNTRY, F.EFFECTIVE_TERM, F.RESIDENCY, F.TUITION_RES, G.COUNTRY, G.VISA_PERMIT_TYPE, G.VISA_WRKPMT_STATUS, TO_CHAR(G.STATUS_DT,'YYYY-MM-DD'), TO_CHAR(G.DT_ISSUED,'YYYY-MM-DD'), TO_CHAR(G.EXPIRATN_DT,'YYYY-MM-DD'), TO_CHAR(SYSDATE,'YYYY-MM-DD')
FROM (SELECT TA.ADMIT_TYPE, TA.LAST_SCH_ATTEND, TA.INSTITUTION, TA.ACAD_CAREER, TA.EMPLID, TA.STDNT_CAR_NBR, TA.ADM_APPL_NBR
FROM PS_ADM_APPL_DATA TA,PS_ADM_APPL_SCTY A1 WHERE TA.EMPLID = A1.EMPLID AND TA.ACAD_CAREER = A1.ACAD_CAREER AND TA.ADM_APPL_NBR = A1.ADM_APPL_NBR AND A1.OPRCLASS = 'CUHCPPBAR01' AND A1.OPRID = '11002650' ) A, PS_ADM_APPL_PROG B, PS_ADM_MAINT_SCTY B1, PS_ADM_APPL_PLAN C, PS_CU_BIO_VW D, PS_ADDRESSES4_VW E, PS_RESIDENCY_OFF F, PS_SCC_VISA_P_QVW G
WHERE B.EMPLID = B1.EMPLID
AND B.ACAD_CAREER = B1.ACAD_CAREER
AND B.STDNT_CAR_NBR = B1.STDNT_CAR_NBR
AND B.ADM_APPL_NBR = B1.ADM_APPL_NBR
AND B.APPL_PROG_NBR = B1.APPL_PROG_NBR
AND B1.OPRCLASS = 'CUHCPPBAR01'
AND B1.OPRID = '11002650'
AND ( A.INSTITUTION = :1
AND A.ACAD_CAREER = :2
AND A.EMPLID = B.EMPLID
AND A.ACAD_CAREER = B.ACAD_CAREER
AND A.STDNT_CAR_NBR = B.STDNT_CAR_NBR
AND A.ADM_APPL_NBR = B.ADM_APPL_NBR
AND B.EFFDT =
(SELECT MAX(B_ED.EFFDT) FROM PS_ADM_APPL_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.ADM_APPL_NBR = B_ED.ADM_APPL_NBR
AND B.APPL_PROG_NBR = B_ED.APPL_PROG_NBR
AND B_ED.EFFDT <= SYSDATE)
AND B.EFFSEQ =
(SELECT MAX(B_ES.EFFSEQ) FROM PS_ADM_APPL_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.ADM_APPL_NBR = B_ES.ADM_APPL_NBR
AND B.APPL_PROG_NBR = B_ES.APPL_PROG_NBR
AND B.EFFDT = B_ES.EFFDT)
AND B.ADMIT_TERM BETWEEN :3 AND :4
AND ( B.PROG_ACTION = :5
OR :5 IS NULL)
AND ( A.ADMIT_TYPE = :6
OR :6 IS NULL)
AND B.EMPLID = C.EMPLID
AND B.ACAD_CAREER = C.ACAD_CAREER
AND B.STDNT_CAR_NBR = C.STDNT_CAR_NBR
AND B.ADM_APPL_NBR = C.ADM_APPL_NBR
AND B.APPL_PROG_NBR = C.APPL_PROG_NBR
AND B.EFFSEQ = C.EFFSEQ
AND B.EFFDT = C.EFFDT
AND A.EMPLID = D.EMPLID
AND A.EMPLID = E.EMPLID (+)
AND 'HOME' = E.ADDRESS_TYPE (+)
AND A.EMPLID = F.EMPLID (+)
AND A.ACAD_CAREER = F.ACAD_CAREER (+)
AND A.INSTITUTION = F.INSTITUTION (+)
AND A.EMPLID = G.EMPLID (+) )
ORDER BY 1, 2, 3
  • No labels