SSR_BR_ADDR_VW

(SQL View)
 Index Back

SELECT ADDR.EMPLID ,CASE WHEN (ADDR.COUNTRY) = ('NLD') THEN (CASE WHEN (ADDR.POSTAL) IS NOT NULL THEN (%Substring(ADDR.ADDRESS3,1,24)) END )END AS SSR_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 SSR_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 SSR_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_ADDRESSES ADDR WHERE ADDR.ADDRESS_TYPE = ( SELECT A.ADDR_TYPE FROM PS_SSR_BR_BO_SETUP A ) 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 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
2 SSR_BR_STREET_NLD Character(24) VARCHAR2(24) NOT NULL Street
3 NUM1 Character(6) VARCHAR2(6) NOT NULL Number 1
4 NUM2 Character(6) VARCHAR2(6) NOT NULL Number 2
5 SSR_BR_LOC_DESCR Character(35) VARCHAR2(35) NOT NULL Location Description
6 CITY Character(30) VARCHAR2(30) NOT NULL City
7 SSR_BR_HOUSE_I_NBR Character(2) VARCHAR2(2) NOT NULL House Number Indication
8 SSR_BR_FR_ADDR_1 Character(35) VARCHAR2(35) NOT NULL Foreign Address Line 1
9 SSR_BR_FR_ADDR_2 Character(35) VARCHAR2(35) NOT NULL Foreign Address Line 2
10 SSR_BR_FR_ADDR_3 Character(35) VARCHAR2(35) NOT NULL Foreign Address Line 3
11 SSR_BR_NATIONALITY Character(4) VARCHAR2(4) NOT NULL Nationality Code 1
12 SSR_BR_NATION_CD_2 Character(4) VARCHAR2(4) NOT NULL Nationality Code 2