SAD_BR_ADRVA_VW

(SQL View)
 Index Back

SELECT ES.INSTITUTION , ES.SCC_BRINCD_NLD , ES.SAD_BR_EDU_SEC_NLD , ES.EMPLID ,CASE WHEN (ADDR.COUNTRY) = ('NLD') THEN (CASE WHEN (ADDR.POSTAL) IS NOT NULL THEN (%Substring(ADDR.ADDRESS3,1,24)) END )END AS SAD_BR_STREET_NLD ,CASE WHEN (ADDR.COUNTRY) = ('NLD') THEN (CASE WHEN (ADDR.POSTAL) IS NOT NULL THEN (ADDR.NUM1) END )END AS NUM1 , CASE WHEN (ADDR.COUNTRY) = ('NLD') THEN (CASE WHEN (ADDR.POSTAL) IS NOT NULL THEN (ADDR.NUM2) END )END AS NUM2 , CASE WHEN (ADDR.COUNTRY) = ('NLD') THEN (CASE WHEN (ADDR.POSTAL) IS NOT NULL THEN (ADDR.HOUSE_TYPE) END )END AS SAD_BR_LOC_DESCR , CASE WHEN (ADDR.COUNTRY) = ('NLD') THEN (CASE WHEN (ADDR.POSTAL) IS NOT NULL THEN (ADDR.CITY) END )END AS CITY , CASE WHEN (ADDR.COUNTRY ) = ('NLD') THEN (CASE WHEN (ADDR.POSTAL) IS NOT NULL THEN (ADDR.HOUSE_TYPE) END )END AS SAD_BR_HOUSE_I_NBR ,CASE WHEN (ADDR.COUNTRY) <> ('NLD') THEN %Substring(CASE WHEN ADDR.ADDRESS1 IS NOT NULL THEN (ADDR.ADDRESS1) ELSE ' ' END %concat ' ' %concat CASE WHEN ADDR.ADDRESS2 IS NOT NULL THEN (ADDR.ADDRESS2) ELSE ' ' END %concat ' ' %concat CASE WHEN ADDR.ADDRESS3 IS NOT NULL THEN (ADDR.ADDRESS3) ELSE ' ' END %concat ' ' %concat CASE WHEN ADDR.ADDRESS4 IS NOT NULL THEN (ADDR.ADDRESS4) ELSE ' ' END %concat ' ' %concat CASE WHEN ADDR.CITY IS NOT NULL THEN (ADDR.CITY) ELSE ' ' END %concat ' ' %concat CASE WHEN ADDR.NUM1 IS NOT NULL THEN (ADDR.NUM1) ELSE ' ' END %concat ' ' %concat CASE WHEN ADDR.NUM2 IS NOT NULL THEN (ADDR.NUM2) ELSE ' ' END %concat ' ' %concat CASE WHEN ADDR.HOUSE_TYPE IS NOT NULL THEN (ADDR.HOUSE_TYPE) ELSE ' ' END %concat ' ' %concat CASE WHEN ADDR.ADDR_FIELD1 IS NOT NULL THEN (ADDR.ADDR_FIELD1) ELSE ' ' END %concat ' ' %concat CASE WHEN ADDR.ADDR_FIELD2 IS NOT NULL THEN (ADDR.ADDR_FIELD2) ELSE ' ' END %concat ' ' %concat CASE WHEN ADDR.ADDR_FIELD3 IS NOT NULL THEN (ADDR.ADDR_FIELD3) ELSE ' ' END %concat ' ' %concat CASE WHEN ADDR.COUNTY IS NOT NULL THEN (ADDR.COUNTY) ELSE ' ' END %concat ' ' %concat CASE WHEN ADDR.STATE IS NOT NULL THEN (ADDR.STATE) ELSE ' ' END %concat ' ' %concat CASE WHEN ADDR.POSTAL IS NOT NULL THEN (ADDR.POSTAL) ELSE ' ' END %concat ' ' %concat CASE WHEN ADDR.GEO_CODE IS NOT NULL THEN (ADDR.GEO_CODE) ELSE ' ' END %concat ' ' %concat CASE WHEN ADDR.IN_CITY_LIMIT IS NOT NULL THEN (ADDR.IN_CITY_LIMIT) ELSE ' ' END ,1,35) END ,CASE WHEN (ADDR.COUNTRY) <> ('NLD') THEN %Substring(CASE WHEN ADDR.ADDRESS1 IS NOT NULL THEN (ADDR.ADDRESS1) ELSE ' ' END %concat ' ' %concat CASE WHEN ADDR.ADDRESS2 IS NOT NULL THEN (ADDR.ADDRESS2) ELSE ' ' END %concat ' ' %concat CASE WHEN ADDR.ADDRESS3 IS NOT NULL THEN (ADDR.ADDRESS3) ELSE ' ' END %concat ' ' %concat CASE WHEN ADDR.ADDRESS4 IS NOT NULL THEN (ADDR.ADDRESS4) ELSE ' ' END %concat ' ' %concat CASE WHEN ADDR.CITY IS NOT NULL THEN (ADDR.CITY) ELSE ' ' END %concat ' ' %concat CASE WHEN ADDR.NUM1 IS NOT NULL THEN (ADDR.NUM1) ELSE ' ' END %concat ' ' %concat CASE WHEN ADDR.NUM2 IS NOT NULL THEN (ADDR.NUM2) ELSE ' ' END %concat ' ' %concat CASE WHEN ADDR.HOUSE_TYPE IS NOT NULL THEN (ADDR.HOUSE_TYPE) ELSE ' ' END %concat ' ' %concat CASE WHEN ADDR.ADDR_FIELD1 IS NOT NULL THEN (ADDR.ADDR_FIELD1) ELSE ' ' END %concat ' ' %concat CASE WHEN ADDR.ADDR_FIELD2 IS NOT NULL THEN (ADDR.ADDR_FIELD2) ELSE ' ' END %concat ' ' %concat CASE WHEN ADDR.ADDR_FIELD3 IS NOT NULL THEN (ADDR.ADDR_FIELD3) ELSE ' ' END %concat ' ' %concat CASE WHEN ADDR.COUNTY IS NOT NULL THEN (ADDR.COUNTY) ELSE ' ' END %concat ' ' %concat CASE WHEN ADDR.STATE IS NOT NULL THEN (ADDR.STATE) ELSE ' ' END %concat ' ' %concat CASE WHEN ADDR.POSTAL IS NOT NULL THEN (ADDR.POSTAL) ELSE ' ' END %concat ' ' %concat CASE WHEN ADDR.GEO_CODE IS NOT NULL THEN (ADDR.GEO_CODE) ELSE ' ' END %concat ' ' %concat CASE WHEN ADDR.IN_CITY_LIMIT IS NOT NULL THEN (ADDR.IN_CITY_LIMIT) ELSE ' ' END ,36,35) END ,CASE WHEN (ADDR.COUNTRY) <> ('NLD') THEN %Substring(CASE WHEN ADDR.ADDRESS1 IS NOT NULL THEN (ADDR.ADDRESS1) ELSE ' ' END %concat ' ' %concat CASE WHEN ADDR.ADDRESS2 IS NOT NULL THEN (ADDR.ADDRESS2) ELSE ' ' END %concat ' ' %concat CASE WHEN ADDR.ADDRESS3 IS NOT NULL THEN (ADDR.ADDRESS3) ELSE ' ' END %concat ' ' %concat CASE WHEN ADDR.ADDRESS4 IS NOT NULL THEN (ADDR.ADDRESS4) ELSE ' ' END %concat ' ' %concat CASE WHEN ADDR.CITY IS NOT NULL THEN (ADDR.CITY) ELSE ' ' END %concat ' ' %concat CASE WHEN ADDR.NUM1 IS NOT NULL THEN (ADDR.NUM1) ELSE ' ' END %concat ' ' %concat CASE WHEN ADDR.NUM2 IS NOT NULL THEN (ADDR.NUM2) ELSE ' ' END %concat ' ' %concat CASE WHEN ADDR.HOUSE_TYPE IS NOT NULL THEN (ADDR.HOUSE_TYPE) ELSE ' ' END %concat ' ' %concat CASE WHEN ADDR.ADDR_FIELD1 IS NOT NULL THEN (ADDR.ADDR_FIELD1) ELSE ' ' END %concat ' ' %concat CASE WHEN ADDR.ADDR_FIELD2 IS NOT NULL THEN (ADDR.ADDR_FIELD2) ELSE ' ' END %concat ' ' %concat CASE WHEN ADDR.ADDR_FIELD3 IS NOT NULL THEN (ADDR.ADDR_FIELD3) ELSE ' ' END %concat ' ' %concat CASE WHEN ADDR.COUNTY IS NOT NULL THEN (ADDR.COUNTY) ELSE ' ' END %concat ' ' %concat CASE WHEN ADDR.STATE IS NOT NULL THEN (ADDR.STATE) ELSE ' ' END %concat ' ' %concat CASE WHEN ADDR.POSTAL IS NOT NULL THEN (ADDR.POSTAL) ELSE ' ' END %concat ' ' %concat CASE WHEN ADDR.GEO_CODE IS NOT NULL THEN (ADDR.GEO_CODE) ELSE ' ' END %concat ' ' %concat CASE WHEN ADDR.IN_CITY_LIMIT IS NOT NULL THEN (ADDR.IN_CITY_LIMIT) ELSE ' ' END ,71,35) END ,' ' ,' ' FROM PS_SAD_BR_ENRSQ_VA ES , PS_ADDRESSES ADDR WHERE ADDR.EMPLID = ES.EMPLID AND ADDR.ADDRESS_TYPE = ( SELECT A.ADDR_TYPE FROM PS_SAD_BR_INST_NLD A WHERE A.INSTITUTION = ES.INSTITUTION AND A.SCC_BRINCD_NLD = ES.SCC_BRINCD_NLD ) AND ADDR.EFFDT = ( SELECT MAX(ADDR1.EFFDT) FROM PS_ADDRESSES ADDR1 WHERE ADDR.EMPLID = ADDR1.EMPLID AND ADDR.ADDRESS_TYPE = ADDR1.ADDRESS_TYPE AND ADDR1.EFFDT <= %CurrentDateIn AND ADDR1.EFF_STATUS = 'A')

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 INSTITUTION Character(5) VARCHAR2(5) NOT NULL Academic Institution
2 SCC_BRINCD_NLD Character(4) VARCHAR2(4) NOT NULL BRINcode
3 SAD_BR_EDU_SEC_NLD Character(2) VARCHAR2(2) NOT NULL Educational Sector
BO=Professional Education
ED=Education
VA=VAVO
4 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
5 SAD_BR_STREET_NLD Character(24) VARCHAR2(24) NOT NULL Street
6 NUM1 Character(6) VARCHAR2(6) NOT NULL Number 1
7 NUM2 Character(6) VARCHAR2(6) NOT NULL Number 2
8 SAD_BR_LOC_DESCR Character(35) VARCHAR2(35) NOT NULL Location Description
9 CITY Character(30) VARCHAR2(30) NOT NULL City
10 SAD_BR_HOUSE_I_NBR Character(2) VARCHAR2(2) NOT NULL House Number Indication
11 SAD_BR_FR_ADDR_1 Character(35) VARCHAR2(35) NOT NULL Foreign Address Line 1
12 SAD_BR_FR_ADDR_2 Character(35) VARCHAR2(35) NOT NULL Foreign Address Line 2
13 SAD_BR_FR_ADDR_3 Character(35) VARCHAR2(35) NOT NULL Foreign Address Line 3
14 SAD_BR_NATIONALITY Character(4) VARCHAR2(4) NOT NULL Nationality Code 1
15 SAD_BR_NATION_CD_2 Character(4) VARCHAR2(4) NOT NULL Nationality Code 2