Query SQL:
SELECT D.LAST_NAME, D.FIRST_NAME, D.MIDDLE_NAME, G.EMPLID, G.ADMIT_TYPE, G.PROG_ACTION, D.PHONE, D.EMAIL_ADDR, E.ADDRESS1, E.ADDRESS2, E.CITY, E.STATE, E.POSTAL, B.EFFECTIVE_TERM, B.FIN_AID_ST_RES, A.ACAD_PLAN, H.CIP_CODE
FROM PS_SCC_CITIZEN_QVW C, PS_CU_BIO_VW D, PS_ADDRESSES4_VW E, PS_RESIDENCY_OFF B, PS_ADM_MC_VW G, PS_ADM_APPL_PLAN A, PS_ACAD_PLAN_TBL H
WHERE G.EFFDT =
(SELECT MAX(G_ED.EFFDT) FROM PS_ADM_MC_VW G_ED
WHERE G.EMPLID = G_ED.EMPLID
AND G.ACAD_CAREER = G_ED.ACAD_CAREER
AND G.STDNT_CAR_NBR = G_ED.STDNT_CAR_NBR
AND G.ADM_APPL_NBR = G_ED.ADM_APPL_NBR
AND G.APPL_PROG_NBR = G_ED.APPL_PROG_NBR
AND G_ED.EFFDT <= SYSDATE)
AND G.EFFSEQ =
(SELECT MAX(G_ES.EFFSEQ) FROM PS_ADM_MC_VW G_ES
WHERE G.EMPLID = G_ES.EMPLID
AND G.ACAD_CAREER = G_ES.ACAD_CAREER
AND G.STDNT_CAR_NBR = G_ES.STDNT_CAR_NBR
AND G.ADM_APPL_NBR = G_ES.ADM_APPL_NBR
AND G.APPL_PROG_NBR = G_ES.APPL_PROG_NBR
AND G.EFFDT = G_ES.EFFDT)
AND G.INSTITUTION = :1
AND G.ACAD_CAREER = 'UGRD'
AND G.ACAD_PROG = 'UGRD'
AND G.ADMIT_TERM BETWEEN :2 AND :3
AND G.PROG_ACTION NOT IN ('ADMT','DATA','DEFR','DENY','WAPP')
AND C.EMPLID = G.EMPLID
AND C.COUNTRY = 'USA'
AND C.CITIZENSHIP_STATUS IN ('1','2','5')
AND G.EMPLID = D.EMPLID
AND G.EMPLID = E.EMPLID (+)
AND 'HOME' = E.ADDRESS_TYPE (+)
AND G.EMPLID = B.EMPLID
AND G.ACAD_CAREER = B.ACAD_CAREER
AND B.INSTITUTION = G.INSTITUTION
AND B.EFFECTIVE_TERM = (SELECT MAX( F.EFFECTIVE_TERM)
FROM PS_RESIDENCY_OFF F
WHERE F.EMPLID = B.EMPLID
AND F.ACAD_CAREER = B.ACAD_CAREER
AND F.INSTITUTION = B.INSTITUTION)
AND B.FIN_AID_ST_RES IN ('NYC','IN')
AND ( G.ADMIT_TYPE = :4
OR :4 IS NULL)
AND G.EMPLID = A.EMPLID
AND G.ACAD_CAREER = A.ACAD_CAREER
AND G.STDNT_CAR_NBR = A.STDNT_CAR_NBR
AND G.ADM_APPL_NBR = A.ADM_APPL_NBR
AND G.APPL_PROG_NBR = A.APPL_PROG_NBR
AND A.EFFSEQ = G.EFFSEQ
AND G.EFFDT = A.EFFDT
AND G.INSTITUTION = H.INSTITUTION
AND A.ACAD_PLAN = H.ACAD_PLAN
AND H.EFFDT =
(SELECT MAX(H_ED.EFFDT) FROM PS_ACAD_PLAN_TBL H_ED
WHERE H.INSTITUTION = H_ED.INSTITUTION
AND H.ACAD_PLAN = H_ED.ACAD_PLAN
AND H_ED.EFFDT <= SYSDATE)
ORDER BY 1, 2, 3
FROM PS_SCC_CITIZEN_QVW C, PS_CU_BIO_VW D, PS_ADDRESSES4_VW E, PS_RESIDENCY_OFF B, PS_ADM_MC_VW G, PS_ADM_APPL_PLAN A, PS_ACAD_PLAN_TBL H
WHERE G.EFFDT =
(SELECT MAX(G_ED.EFFDT) FROM PS_ADM_MC_VW G_ED
WHERE G.EMPLID = G_ED.EMPLID
AND G.ACAD_CAREER = G_ED.ACAD_CAREER
AND G.STDNT_CAR_NBR = G_ED.STDNT_CAR_NBR
AND G.ADM_APPL_NBR = G_ED.ADM_APPL_NBR
AND G.APPL_PROG_NBR = G_ED.APPL_PROG_NBR
AND G_ED.EFFDT <= SYSDATE)
AND G.EFFSEQ =
(SELECT MAX(G_ES.EFFSEQ) FROM PS_ADM_MC_VW G_ES
WHERE G.EMPLID = G_ES.EMPLID
AND G.ACAD_CAREER = G_ES.ACAD_CAREER
AND G.STDNT_CAR_NBR = G_ES.STDNT_CAR_NBR
AND G.ADM_APPL_NBR = G_ES.ADM_APPL_NBR
AND G.APPL_PROG_NBR = G_ES.APPL_PROG_NBR
AND G.EFFDT = G_ES.EFFDT)
AND G.INSTITUTION = :1
AND G.ACAD_CAREER = 'UGRD'
AND G.ACAD_PROG = 'UGRD'
AND G.ADMIT_TERM BETWEEN :2 AND :3
AND G.PROG_ACTION NOT IN ('ADMT','DATA','DEFR','DENY','WAPP')
AND C.EMPLID = G.EMPLID
AND C.COUNTRY = 'USA'
AND C.CITIZENSHIP_STATUS IN ('1','2','5')
AND G.EMPLID = D.EMPLID
AND G.EMPLID = E.EMPLID (+)
AND 'HOME' = E.ADDRESS_TYPE (+)
AND G.EMPLID = B.EMPLID
AND G.ACAD_CAREER = B.ACAD_CAREER
AND B.INSTITUTION = G.INSTITUTION
AND B.EFFECTIVE_TERM = (SELECT MAX( F.EFFECTIVE_TERM)
FROM PS_RESIDENCY_OFF F
WHERE F.EMPLID = B.EMPLID
AND F.ACAD_CAREER = B.ACAD_CAREER
AND F.INSTITUTION = B.INSTITUTION)
AND B.FIN_AID_ST_RES IN ('NYC','IN')
AND ( G.ADMIT_TYPE = :4
OR :4 IS NULL)
AND G.EMPLID = A.EMPLID
AND G.ACAD_CAREER = A.ACAD_CAREER
AND G.STDNT_CAR_NBR = A.STDNT_CAR_NBR
AND G.ADM_APPL_NBR = A.ADM_APPL_NBR
AND G.APPL_PROG_NBR = A.APPL_PROG_NBR
AND A.EFFSEQ = G.EFFSEQ
AND G.EFFDT = A.EFFDT
AND G.INSTITUTION = H.INSTITUTION
AND A.ACAD_PLAN = H.ACAD_PLAN
AND H.EFFDT =
(SELECT MAX(H_ED.EFFDT) FROM PS_ACAD_PLAN_TBL H_ED
WHERE H.INSTITUTION = H_ED.INSTITUTION
AND H.ACAD_PLAN = H_ED.ACAD_PLAN
AND H_ED.EFFDT <= SYSDATE)
ORDER BY 1, 2, 3