SSR_RI_OLOV_NLD

(SQL View)
 Index Back

Current View (base location)


SELECT DISTINCT A.INSTITUTION , A.SSR_RI_EDU_GOV_NLD , B.LOCATION , CASE WHEN LENGTH(B.ESTABID) = 7 THEN B.ESTABID END , B2.EFFDT , CASE WHEN B.EFF_STATUS = 'I' THEN B.EFFDT END , CASE WHEN B.COUNTRY = 'NLD' THEN B.POSTAL END , CASE WHEN B.COUNTRY = 'NLD' THEN B.NUM1 END , CASE WHEN B.COUNTRY = 'NLD' THEN B.NUM2 END , ' ' , COALESCE(D.SCC_LATITUDE , NULL) , COALESCE(D.SCC_LONGITUDE , NULL) FROM %Table(SSR_RI_EPS_NLD) A JOIN %Table(CAMPUS_TBL) C ON A.INSTITUTION = C.INSTITUTION JOIN %Table(LOCATION_TBL) B ON C.LOCATION = B.LOCATION JOIN %Table(SSR_RI_OLWL_NLD) OLW ON A.INSTITUTION = OLW.INSTITUTION AND B.LOCATION = OLW.LOCATION LEFT OUTER JOIN %Table(BLDG_TBL) D ON B.BUILDING = D.BLDG_CD JOIN %Table(LOCATION_TBL) B2 ON B.SETID = B2.SETID AND B.LOCATION = B2.LOCATION JOIN %Table(SET_CNTRL_REC) E ON A.INSTITUTION = E.SETCNTRLVALUE AND E.RECNAME = 'LOCATION_TBL' AND E.SETID = B.SETID WHERE %EffdtCheck(CAMPUS_TBL C1, C, %currentdatein) AND B.EFFDT = ( SELECT MAX(B_ED.EFFDT) FROM %Table(LOCATION_TBL) B_ED WHERE B.SETID = B_ED.SETID AND B.LOCATION = B_ED.LOCATION AND B_ED.EFFDT <= %CurrentDateIn ) AND B2.EFFDT = ( SELECT MIN(B2_ED.EFFDT) FROM %Table(LOCATION_TBL) B2_ED WHERE B.SETID = B2_ED.SETID AND B.LOCATION = B2_ED.LOCATION AND B2_ED.EFF_STATUS = 'A' ) AND %EffdtCheck(SSR_RI_EPS_NLD A_ED,A,%CURRENTDATEIN) AND A.EFFDT = OLW.EFFDT

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 INSTITUTION Character(5) VARCHAR2(5) NOT NULL Academic Institution
2 SSR_RI_EDU_GOV_NLD Character(20) VARCHAR2(20) NOT NULL Educational Board Code
3 LOCATION Character(10) VARCHAR2(10) NOT NULL Location Code
4 SSR_RI_OLO_COD_NLD Character(12) VARCHAR2(12) NOT NULL RIO Establishment ID
5 BEGIN_DT Date(10) DATE NOT NULL Begin Date
6 END_DT Date(10) DATE end date
7 SSR_RI_POSTAL_NLD Character(7) VARCHAR2(7) NOT NULL Postal
8 SSR_RI_NUM1_NLD Character(6) VARCHAR2(6) NOT NULL Number 1
9 SSR_RI_NUM2_NLD Character(6) VARCHAR2(6) NOT NULL Number 2
10 SSR_RI_BAGID_NLD Character(50) VARCHAR2(50) NOT NULL BagID
11 SCC_LATITUDE Signed Number(12,7) DECIMAL(10,7) NOT NULL Latitude
12 SCC_LONGITUDE Signed Number(12,7) DECIMAL(10,7) NOT NULL Longitude