|
SELECT A.INSTITUTION , A.SSR_RI_EUN_COD_NLD , ' ' AS ACAD_PROG , A2.EFFDT , CASE WHEN A.EFF_STATUS = 'I' THEN A.EFFDT END , A.SSR_RI_DOCTP_NLD , A.SSR_BR_LVL_NLD , A.SSR_RI_EQF_NLD , A.SSR_RI_NLQF_NLD , A.SSR_RI_CAT_NLD FROM %Table(ssr_ri_edunt_nl) A JOIN %Table(ssr_ri_edunt_nl) A2 ON A.INSTITUTION = A2.INSTITUTION AND A.SSR_RI_EUN_COD_NLD = A2.SSR_RI_EUN_COD_NLD WHERE A.EFFDT = ( SELECT MAX(A_ED.EFFDT) FROM %Table(ssr_ri_edunt_nl) A_ED WHERE A.SSR_RI_EUN_COD_NLD = A_ED.SSR_RI_EUN_COD_NLD AND A_ED.EFFDT <= %CurrentDateIn) AND A2.EFFDT = ( SELECT MIN(A2_ED.EFFDT) FROM %Table(ssr_ri_edunt_nl) A2_ED WHERE A.SSR_RI_EUN_COD_NLD = A2_ED.SSR_RI_EUN_COD_NLD AND A2_ED.EFFDT <= %CurrentDateIn AND A2_ED.EFF_STATUS = 'A' ) UNION SELECT B2.INSTITUTION , %Coalesce(A.SSR_RI_EUN_COD_NLD ,' ') , B2.ACAD_PROG , B2.EFFDT , CASE WHEN B.EFF_STATUS = 'I' THEN B.EFFDT END , ' ' , ' ' , ' ' , ' ' , ' ' FROM %Table(SSR_PRG_NRM_NLD) A JOIN %Table(ACAD_PROG_TBL) B ON A.INSTITUTION = B.INSTITUTION AND A.ACAD_PROG = B.ACAD_PROG AND A.EFFDT = B.EFFDT JOIN %Table(ACAD_PROG_TBL) B2 ON B.ACAD_PROG = B2.ACAD_PROG JOIN %Table(ACAD_PROG_CAF) B2CF ON B2CF.INSTITUTION = B2.INSTITUTION AND B2CF.ACAD_PROG = B2.ACAD_PROG AND B2CF.EFFDT = B2.EFFDT JOIN %Table(SCC_CMPLOAD_NLD) CL ON B2.ACAD_PROG = CL.ACAD_PROG AND B2.EFFDT = CL.EFFDT JOIN %Table(SSR_RI_OPCF_NLD) CF ON B.INSTITUTION = CF.INSTITUTION AND CF.SCC_CAF_ATTRIB_NM = B2CF.SCC_CAF_ATTRIB_NM AND CF.SSR_RI_AT_NAME_NLD = 'toestemmingDeelnameSTAP' JOIN %Table(SSR_RI_XMLV_NLD) CF1 ON CF.INSTITUTION = CF1.INSTITUTION AND CF1.EFFDT = CF.EFFDT AND CF.RECNAME = CF1.RECNAME AND CF1.SCC_CAF_ATTRIB_NM = CF.SCC_CAF_ATTRIB_NM AND CF1.SSR_RI_AT_NAME_NLD = CF.SSR_RI_AT_NAME_NLD WHERE B.EFFDT = ( SELECT MAX(B_ED.EFFDT) FROM %Table(ACAD_PROG_TBL) B_ED WHERE B.ACAD_PROG = B_ED.ACAD_PROG AND B_ED.EFFDT <= %CurrentDateIn) AND B2.EFFDT = ( SELECT MIN(B2_ED.EFFDT) FROM %Table(ACAD_PROG_TBL) B2_ED WHERE B.ACAD_PROG = B2_ED.ACAD_PROG AND B2_ED.EFFDT <= %CurrentDateIn AND B2_ED.EFF_STATUS = 'A' ) AND B.HEGIS_CODE = ' ' AND B2.HEGIS_CODE = ' ' AND CL.SSR_RIO_YN_NLD = 'Y' AND %Cast(%SUBSTRING(CF1.SSR_BR_XMLVL_NLD,1,1000),long,Character) = 'TOESTEMMING_VERLEEND' AND CF1.SSR_BR_FIELDVL_NLD = B2CF.SCC_CAF_ATTR_VAL AND A.SSR_RI_EUN_COD_NLD NOT IN ( SELECT SSR_RI_EUN_COD_NLD FROM %Table(ssr_ri_edunt_nl)) AND B.ACAD_CAREER IN ( SELECT DISTINCT XLSE.ACAD_CAREER FROM PS_SSR_BR_XLSE_NLD XLSE WHERE XLSE.INSTITUTION = A.INSTITUTION AND XLSE.EFFDT=( SELECT MAX(EFFDT) FROM PS_SSR_BR_XLSE_NLD XLSE_ED WHERE XLSE_ED.INSTITUTION=XLSE.INSTITUTION AND XLSE_ED.SCC_BRINCD_NLD=XLSE.SCC_BRINCD_NLD AND XLSE_ED.ACAD_CAREER=XLSE.ACAD_CAREER AND XLSE_ED.EFFDT<=B.EFFDT) ) AND %EffdtCheck(SSR_RI_OPCF_NLD CF_ED, CF, %CURRENTDATEIN)
|
INSTITUTION