|
SELECT DISTINCT AD.EMPLID
, HC.SCC_BRINCD_NLD || ':' ||AD.ADM_APPL_NBR AS SSR_EA_APL_ID_NLD
, CASE WHEN IDBO.SAD_SL_SOFI_NBR IS NULL THEN IDBO.SSR_BR_NAT_ID_NLD ELSE NULL END AS SSR_BR_NAT_ID_NLD
, IDBO.SAD_SL_SOFI_NBR
,(%Sql(SSR_EA_ADDR_POSTAL_NLD,AD.EMPLID)) AS SSR_BR_POSTAL_NLD
,(%Sql(SSR_EA_ADDR_NUM1_NLD,AD.EMPLID)) AS NUM1
,CASE WHEN ZEVO.ENROLLMENT_DT > ZEVA.ENROLLMENT_DT
OR ZEVA.ENROLLMENT_DT IS NULL THEN ZEVO.SCC_BRINCD_NLD ELSE ZEVA.SCC_BRINCD_NLD END AS SSR_EA_BRN_LV_NLD
,' ' AS SSR_EA_EPR_LV_NLD
,HC.SCC_BRINCD_NLD AS SCC_BRINCD_NLD
,(%Sql(SSR_EA_RIO_EPV_ACADPROG_NLD, HC.ACAD_ORG)) AS SSR_EA_EPR_RC_NLD
, CASE WHEN AP.PROG_ACTION IS NOT NULL THEN (%Sql(SSR_EA_GET_STATUS_NLD, HC.INSTITUTION, HC.SCC_BRINCD_NLD, AP.PROG_ACTION, AP.PROG_REASON, AP.EFFDT)) ELSE CASE WHEN (%Sql(SSR_EA_OWO_PROG_PRINT_CHK_NLD,
AD.INSTITUTION, AD.ACAD_CAREER,AD.ADM_APPL_NBR,AD.APPL_PROG_NBR,AD.EMPLID)) IS NOT NULL
OR (%Sql(SSR_EA_OWO_APPL_PRINT_CHK_NLD, AD.INSTITUTION, AD.ACAD_CAREER,AD.ADM_APPL_NBR,AD.APPL_PROG_NBR,AD.EMPLID)) IS NOT NULL THEN 'OA' ELSE (%Sql(SSR_EA_GET_STATUS_NLD, HC.INSTITUTION, HC.SCC_BRINCD_NLD,
AD.PROG_ACTION, AD.PROG_REASON,AD.EFFDT)) END END AS SSR_EA_STATUS_NLD
,CASE WHEN ZEVO.ENROLLMENT_DT > ZEVA.ENROLLMENT_DT
OR ZEVA.ENROLLMENT_DT IS NULL THEN ZEVO.STUDY_FIELD ELSE ZEVA.STUDY_FIELD END AS STUDY_FIELD
,ZGNI.SSR_BR_STRT_QF_NLD AS SSR_BR_STRT_QF_NLD
,IDBO.BIRTHDATE AS BIRTHDATE
,CASE WHEN SETUP.SSR_EA_HEGISLD_NLD = 'Y' THEN (%Sql(SSR_EA_GET_HEGIS_CODE_NLD, AD.INSTITUTION, AD.ACAD_PROG, AD.EFFDT)) END AS HEGIS_CODE
, CASE WHEN SETUP.SSR_EA_HEGISLD_NLD = 'Y' THEN CASE WHEN STDAP.SSR_FORM_STUDY_NLD = 'E' THEN 'EX' ELSE (%Sql(SSR_BR_LOOKUP_CAR_LD_SQL, HC.INSTITUTION, HC.SCC_BRINCD_NLD, AD.ACAD_LOAD_APPR, AD.EFFDT)) END
END AS SSR_BR_CAREER_NLD
,AD.INSTITUTION
,AD.ADM_APPL_NBR
,AD.APPL_PROG_NBR
,AD.ACAD_CAREER
,CASE WHEN AP.EMPLID IS NOT NULL THEN AP.STDNT_CAR_NBR ELSE AD.STDNT_CAR_NBR END
,APLCAF.SCC_CAF_ATTR_TVAL AS SSR_EA_CA_INDEX
,%NumToChar(T.TERM_BEGIN_DT,'YYYY') || '-' || %NumToChar(T.TERM_END_DT,'YYYY') AS SSR_EA_ADMIT_YR
,CASE WHEN AD.PROG_ACTION = 'MATR' THEN AD.EFFDT END AS SSR_EA_START_DT
FROM ((((((((PS_ADM_APPL_PROG AD JOIN PS_TERM_TBL T ON T.INSTITUTION = AD.INSTITUTION
AND T.ACAD_CAREER = AD.ACAD_CAREER
AND T.STRM = AD.ADMIT_TERM) LEFT OUTER JOIN (PS_ACAD_PROG AP LEFT OUTER JOIN PS_TERM_TBL TERM ON TERM.INSTITUTION = AP.INSTITUTION
AND TERM.ACAD_CAREER = AP.ACAD_CAREER
AND TERM.STRM = AP.ADMIT_TERM) ON AD.EMPLID = AP.EMPLID
AND AD.ACAD_CAREER = AP.ACAD_CAREER
AND AD.ADM_APPL_NBR = AP.ADM_APPL_NBR
AND AD.APPL_PROG_NBR = AP.APPL_PROG_NBR
AND AP.PROG_ACTION IN ('WADM'
,'DISC')
AND AP.EFFDT < %DateIn(%NumToChar(TERM.TERM_BEGIN_DT,'YYYY') %Concat '-10-01')) LEFT OUTER JOIN PS_SSR_BP_ZGNI_NLD ZGNI ON AD.EMPLID = ZGNI.EMPLID) LEFT OUTER JOIN PS_SSR_BP_ZEVO_NLD ZEVO ON ZEVO.EMPLID =
AD.EMPLID
AND ZEVO.ENROLLMENT_DT <= T.TERM_BEGIN_DT
AND 1 >= (CASE WHEN ZEVO.SSR_BR_ENR_END_DT IS NOT NULL THEN %DateDiff(ZEVO.SSR_BR_ENR_END_DT, T.TERM_BEGIN_DT)/365 ELSE 1 END)) LEFT OUTER JOIN PS_SSR_BP_ZEVA_NLD ZEVA ON ZEVA.EMPLID = AD.EMPLID
AND ZEVA.ENROLLMENT_DT <= T.TERM_BEGIN_DT
AND 1 >= (CASE WHEN ZEVA.SSR_BR_ENR_END_DT IS NOT NULL THEN %DateDiff(ZEVA.SSR_BR_ENR_END_DT, T.TERM_BEGIN_DT)/365 ELSE 1 END)) LEFT OUTER JOIN PS_SAD_STD_APP_NLD STDAP ON AD.EMPLID = STDAP.EMPLID
AND AD.ACAD_CAREER = STDAP.ACAD_CAREER
AND AD.STDNT_CAR_NBR = STDAP.STDNT_CAR_NBR
AND AD.ADM_APPL_NBR = STDAP.ADM_APPL_NBR
AND AD.APPL_PROG_NBR = STDAP.APPL_PROG_NBR
AND AD.EFFDT = STDAP.EFFDT
AND AD.EFFSEQ = STDAP.EFFSEQ ) LEFT OUTER JOIN PS_SAD_CA_CFG CA ON AD.INSTITUTION = CA.INSTITUTION) LEFT OUTER JOIN PS_SAD_APPL_CAF APLCAF ON AD.EMPLID = APLCAF.EMPLID
AND AD.ACAD_CAREER = APLCAF.ACAD_CAREER
AND AD.STDNT_CAR_NBR = APLCAF.STDNT_CAR_NBR
AND AD.ADM_APPL_NBR = APLCAF.ADM_APPL_NBR
AND APLCAF.SCC_CAF_ATTRIB_NM = CA.SAD_CA_CAF_CHAINID )
,PS_SSR_BR_IDVW_NLD IDBO
,PS_SCC_HM_CAMP_NLD HC
,PS_SSR_EA_ADMS_NLD SETUP
WHERE AD.EMPLID = IDBO.EMPLID
AND AD.INSTITUTION = HC.INSTITUTION
AND AD.ACAD_PROG = HC.ACAD_PROG
AND AD.CAMPUS = HC.CAMPUS
AND %EffdtCheck(SCC_HM_CAMP_NLD HC_ED, HC, AD.EFFDT)
AND AD.ACAD_CAREER IN (
SELECT DISTINCT XLSE.ACAD_CAREER
FROM PS_SSR_BR_XLSE_NLD XLSE
WHERE XLSE.INSTITUTION = AD.INSTITUTION
AND XLSE.SCC_BRINCD_NLD = HC.SCC_BRINCD_NLD
AND %EffdtCheck(SSR_BR_XLSE_NLD XLSE_ED, XLSE, AD.EFFDT) )
AND AD.EFFDT = (
SELECT MAX(AD_ED.EFFDT)
FROM PS_ADM_APPL_PROG AD_ED
WHERE AD.EMPLID = AD_ED.EMPLID
AND AD.ACAD_CAREER = AD_ED.ACAD_CAREER
AND AD.STDNT_CAR_NBR = AD_ED.STDNT_CAR_NBR
AND AD.ADM_APPL_NBR =AD_ED.ADM_APPL_NBR
AND AD.APPL_PROG_NBR = AD_ED.APPL_PROG_NBR )
AND AD.EFFSEQ= (
SELECT MAX(AD_ES.EFFSEQ)
FROM PS_ADM_APPL_PROG AD_ES
WHERE AD.EMPLID = AD_ES.EMPLID
AND AD.ACAD_CAREER = AD_ES.ACAD_CAREER
AND AD.STDNT_CAR_NBR = AD_ES.STDNT_CAR_NBR
AND AD.ADM_APPL_NBR =AD_ES.ADM_APPL_NBR
AND AD.APPL_PROG_NBR = AD_ES.APPL_PROG_NBR
AND AD.EFFDT = AD_ES.EFFDT)
AND AD.ADMIT_TERM >= SETUP.ADMIT_TERM
AND (AP.EFFDT = (
SELECT MAX(AP_ED.EFFDT)
FROM PS_ACAD_PROG AP_ED
WHERE AP.EMPLID = AP_ED.EMPLID
AND AP.ACAD_CAREER = AP_ED.ACAD_CAREER
AND AP.STDNT_CAR_NBR = AP_ED.STDNT_CAR_NBR
AND AP.EFFDT < %DateIn(%NumToChar((SELECT TO_CHAR(TRM.TERM_BEGIN_DT ,'YYYY') FROM PS_TERM_TBL TRM WHERE TRM.INSTITUTION = AP.INSTITUTION AND TRM.ACAD_CAREER = AP.ACAD_CAREER AND TRM.STRM = AP.ADMIT_TERM))
%Concat '-10-01'))
OR AP.EFFDT IS NULL)
AND (AP.EFFSEQ = (
SELECT MAX(AP_ES.EFFSEQ)
FROM PS_ACAD_PROG AP_ES
WHERE AP.EMPLID = AP_ES.EMPLID
AND AP.ACAD_CAREER = AP_ES.ACAD_CAREER
AND AP.STDNT_CAR_NBR = AP_ES.STDNT_CAR_NBR
AND AP.EFFDT = AP_ES.EFFDT)
OR AP.EFFSEQ IS NULL)
AND ( NOT EXISTS (
SELECT 'X'
FROM PS_SSR_BP_ZEVO_NLD ZEVO1
WHERE ZEVO1.EMPLID = ZEVO.EMPLID
AND ZEVO1.ENROLLMENT_DT <= T.TERM_BEGIN_DT
AND (ZEVO1.ENROLLMENT_DT > ZEVO.ENROLLMENT_DT
OR (ZEVO1.ENROLLMENT_DT = ZEVO.ENROLLMENT_DT
AND ZEVO1.STUDY_FIELD < ZEVO.STUDY_FIELD)))
OR ZEVO.ENROLLMENT_DT IS NULL )
AND ( NOT EXISTS (
SELECT 'X'
FROM PS_SSR_BP_ZEVA_NLD ZEVA1
WHERE ZEVA1.EMPLID = ZEVA.EMPLID
AND ZEVA1.ENROLLMENT_DT <= T.TERM_BEGIN_DT
AND (ZEVA1.ENROLLMENT_DT > ZEVA.ENROLLMENT_DT
OR (ZEVA1.ENROLLMENT_DT = ZEVA.ENROLLMENT_DT
AND ZEVA1.STUDY_FIELD < ZEVA.STUDY_FIELD)))
OR ZEVA.ENROLLMENT_DT IS NULL )
AND AD.ACAD_LOAD_APPR NOT IN (
SELECT DISTINCT ACAD_LOAD_APPR
FROM PS_SSR_BR_N_LD_NLD LDEX
WHERE LDEX.INSTITUTION = AD.INSTITUTION
AND LDEX.SCC_BRINCD_NLD = HC.SCC_BRINCD_NLD
AND LDEX.EFFDT = (
SELECT MAX(LDEX_ED.EFFDT)
FROM PS_SSR_BR_N_LD_NLD LDEX_ED
WHERE LDEX.INSTITUTION = LDEX_ED.INSTITUTION
AND LDEX.SCC_BRINCD_NLD = LDEX_ED.SCC_BRINCD_NLD
AND LDEX.ACAD_LOAD_APPR = LDEX_ED.ACAD_LOAD_APPR
AND LDEX_ED.EFFDT <= AD.EFFDT ) )
AND (STDAP.SSR_FORM_STUDY_NLD NOT IN (
SELECT DISTINCT FS.SSR_FORM_STUDY_NLD
FROM PS_SSR_EA_EXFS_NLD FS
WHERE FS.INSTITUTION = AD.INSTITUTION
AND FS.SCC_BRINCD_NLD = HC.SCC_BRINCD_NLD
AND FS.EFFDT=(
SELECT MAX(FS_ED.EFFDT)
FROM PS_SSR_EA_EXFS_NLD FS_ED
WHERE FS_ED.INSTITUTION=FS.INSTITUTION
AND FS_ED.SCC_BRINCD_NLD=FS.SCC_BRINCD_NLD
AND FS_ED.SSR_FORM_STUDY_NLD =FS.SSR_FORM_STUDY_NLD
AND FS_ED.EFFDT <= AD.EFFDT))
OR STDAP.SSR_FORM_STUDY_NLD IS NULL)
AND EXISTS (
SELECT 'X'
FROM PS_SSR_EA_STAT_NLD ST
WHERE ST.INSTITUTION = AD.INSTITUTION
AND ST.SCC_BRINCD_NLD = HC.SCC_BRINCD_NLD
AND ST.PROG_ACTION = AD.PROG_ACTION
AND (ST.PROG_REASON = AD.PROG_REASON
OR ST.SSR_EA_ALLRSN_NLD = 'Y')
AND ST.EFFDT=(
SELECT MAX(ST_ED.EFFDT)
FROM PS_SSR_EA_STAT_NLD ST_ED
WHERE ST_ED.INSTITUTION=ST.INSTITUTION
AND ST_ED.SCC_BRINCD_NLD=ST.SCC_BRINCD_NLD
AND ST_ED.EFFDT <= AD.EFFDT))
AND SETUP.INSTITUTION = AD.INSTITUTION
AND SETUP.SCC_BRINCD_NLD = HC.SCC_BRINCD_NLD
AND %EffdtCheck(SSR_EA_ADMS_NLD SETUP_ED, SETUP, AD.EFFDT)
AND T.INSTITUTION = AD.INSTITUTION
AND T.ACAD_CAREER = AD.ACAD_CAREER
AND T.STRM = AD.ADMIT_TERM
AND (%Sql(SSR_EA_IDOK_CHK_APPL_NLD, AD.EMPLID, AD.ADM_APPL_NBR)) = 'Y'
|
EMPLID