|
SELECT DISTINCT A.INSTITUTION ,A.SSR_RI_EDU_GOV_NLD ,B.ACAD_ORG ,B.EFFDT ,B.DESCRFORMAL ,CASE WHEN D.LANGUAGE_CD = 'ENG' THEN D.DESCRFORMAL END ,C.SCC_CAF_ATTR_VAL FROM %Table(SSR_RI_EPS_NLD) A JOIN %Table(ACAD_ORG_TBL) B ON A.INSTITUTION = B.INSTITUTION JOIN %Table(SSR_RI_EPWL_NLD) EPW ON B.INSTITUTION = EPW.INSTITUTION AND B.ACAD_ORG = EPW.ACAD_ORG LEFT OUTER JOIN %Table(ACAD_ORG_CAF) C ON A.SCC_CAF_ATTRIB_NM = C.SCC_CAF_ATTRIB_NM AND B.ACAD_ORG = C.ACAD_ORG AND B.EFFDT = C.EFFDT LEFT OUTER JOIN %Table(ACAD_ORG_LANG) D ON B.ACAD_ORG = D.ACAD_ORG AND B.EFFDT = D.EFFDT AND D.LANGUAGE_CD = 'ENG' WHERE %EffdtCheck(SSR_RI_EPS_NLD A_ED,A,%CURRENTDATEIN) AND A.EFFDT = EPW.EFFDT AND B.EFFDT >= ( SELECT MIN(B_ED.EFFDT) FROM PS_ACAD_ORG_TBL B_ED WHERE B_ED.ACAD_ORG = B.ACAD_ORG AND B_ED.EFF_STATUS = 'A') AND B.EFFDT <= ( SELECT MAX(B_ED.EFFDT) FROM PS_ACAD_ORG_TBL B_ED WHERE B_ED.ACAD_ORG = B.ACAD_ORG AND B_ED.effdt <= %CurrentDateIn) AND ( B.EFFDT NOT IN ( SELECT B_IED.EFFDT FROM PS_ACAD_ORG_TBL B_IED WHERE B_IED.ACAD_ORG = B.ACAD_ORG AND B_IED.EFFDT = ( SELECT MAX(B_IED1.EFFDT) FROM PS_ACAD_ORG_TBL B_IED1 WHERE B_IED1.ACAD_ORG = B_IED.ACAD_ORG AND B_IED1.EFFDT > ( SELECT MAX(B_EDX.EFFDT) FROM PS_ACAD_ORG_TBL B_EDX WHERE B_EDX.ACAD_ORG = B.ACAD_ORG AND B_EDX.EFF_STATUS = 'A' AND B_EDX.EFFDT <= %CurrentDateIn ) AND B_IED1.EFF_STATUS = 'I' AND B_IED1.EFFDT <= %CurrentDateIn) ) )
|
INSTITUTION