SSR_BR_IDVVWNLD

(SQL View)
 Index Back

Identification Current Vw VAVO

Identification Current View for VAVO

SELECT PERS1.EMPLID , CASE WHEN ( SELECT STUDENT.SAD_GBA_B_YR_NLD FROM PS_SSR_STUDENT_NLD STUDENT WHERE STUDENT.EMPLID = PERS1.EMPLID) = (' ') OR ( SELECT STUDENT.SAD_GBA_B_YR_NLD FROM PS_SSR_STUDENT_NLD STUDENT WHERE STUDENT.EMPLID = PERS1.EMPLID) IS NULL THEN PERS1.BIRTHDATE END , ( SELECT PERS2.SEX FROM PS_PERS_DATA_EFFDT PERS2 WHERE PERS2.EMPLID = PERS1.EMPLID AND PERS2.EFFDT = ( SELECT MAX(PERS2A.EFFDT) FROM PS_PERS_DATA_EFFDT PERS2A WHERE PERS2.EMPLID = PERS2A.EMPLID)) , ( SELECT %Substring(PERS_NID1.NATIONAL_ID, 1, 9) FROM PS_PERS_NID PERS_NID1 WHERE PERS_NID1.COUNTRY = 'NLD' AND PERS_NID1.EMPLID = PERS1.EMPLID AND PERS_NID1.NATIONAL_ID_TYPE = ( SELECT A.SSR_BR_NID_TYP_NLD FROM PS_SSR_BR_BO_SETUP A)) , ( SELECT %Substring(PERS_NID.NATIONAL_ID, 1, 9) FROM PS_PERS_NID PERS_NID WHERE PERS_NID.EMPLID = PERS1.EMPLID AND PERS_NID.COUNTRY = 'NLD' AND PERS_NID.NATIONAL_ID_TYPE = ( SELECT A.SSR_BR_EDU_NBR_TYP FROM PS_SSR_BR_BO_SETUP A)) , CASE WHEN ( SELECT PERS3.COUNTRY FROM PS_ADDRESSES PERS3 WHERE PERS3.ADDRESS_TYPE = ( SELECT A.ADDR_TYPE FROM PS_SSR_BR_BO_SETUP A ) AND PERS3.EMPLID = PERS1.EMPLID AND PERS3.EFFDT = ( SELECT MAX(PERS3A.EFFDT) FROM PS_ADDRESSES PERS3A WHERE PERS3.EMPLID = PERS3A.EMPLID AND PERS3.ADDRESS_TYPE = PERS3A.ADDRESS_TYPE AND PERS3A.EFFDT <= %CurrentDateIn)) = ('NLD') THEN CASE WHEN ( SELECT %Substring(PERS3.POSTAL, 1, 7) FROM PS_ADDRESSES PERS3 WHERE PERS3.ADDRESS_TYPE = ( SELECT A.ADDR_TYPE FROM PS_SSR_BR_BO_SETUP A ) AND PERS3.EMPLID = PERS1.EMPLID AND PERS3.EFFDT = ( SELECT MAX(PERS3A.EFFDT) FROM PS_ADDRESSES PERS3A WHERE PERS3.EMPLID = PERS3A.EMPLID AND PERS3.ADDRESS_TYPE = PERS3A.ADDRESS_TYPE AND PERS3A.EFFDT <= %CurrentDateIn)) IS NULL THEN ( SELECT CBAP.SSR_COUNTRY_NLD FROM PS_SSR_COUNTRY_NLD CBAP WHERE CBAP.COUNTRY = ( SELECT PERS3.COUNTRY FROM PS_ADDRESSES PERS3 WHERE PERS3.ADDRESS_TYPE = ( SELECT A.ADDR_TYPE FROM PS_SSR_BR_BO_SETUP A ) AND PERS3.EMPLID = PERS1.EMPLID AND PERS3.EFFDT = ( SELECT MAX(PERS3A.EFFDT) FROM PS_ADDRESSES PERS3A WHERE PERS3.EMPLID = PERS3A.EMPLID AND PERS3.ADDRESS_TYPE = PERS3A.ADDRESS_TYPE AND PERS3A.EFFDT <= %CurrentDateIn)) AND CBAP.EFFDT = ( SELECT MAX(CBAP1.EFFDT) FROM PS_SSR_COUNTRY_NLD CBAP1 WHERE CBAP.COUNTRY = CBAP1.COUNTRY AND CBAP.SSR_COUNTRY_NLD = CBAP1.SSR_COUNTRY_NLD AND CBAP1.EFF_STATUS = 'A')) ELSE ' ' END ELSE ( SELECT CBAP.SSR_COUNTRY_NLD FROM PS_SSR_COUNTRY_NLD CBAP WHERE CBAP.COUNTRY = ( SELECT PERS3.COUNTRY FROM PS_ADDRESSES PERS3 WHERE PERS3.ADDRESS_TYPE = ( SELECT A.ADDR_TYPE FROM PS_SSR_BR_BO_SETUP A ) AND PERS3.EMPLID = PERS1.EMPLID AND PERS3.EFFDT = ( SELECT MAX(PERS3A.EFFDT) FROM PS_ADDRESSES PERS3A WHERE PERS3.EMPLID = PERS3A.EMPLID AND PERS3.ADDRESS_TYPE = PERS3A.ADDRESS_TYPE AND PERS3A.EFFDT <= %CurrentDateIn)) AND CBAP.EFFDT = ( SELECT MAX(CBAP1.EFFDT) FROM PS_SSR_COUNTRY_NLD CBAP1 WHERE CBAP.COUNTRY = CBAP1.COUNTRY AND CBAP.SSR_COUNTRY_NLD = CBAP1.SSR_COUNTRY_NLD AND CBAP1.EFF_STATUS = 'A')) END , CASE WHEN ( SELECT PERS3.COUNTRY FROM PS_ADDRESSES PERS3 WHERE PERS3.ADDRESS_TYPE = ( SELECT A.ADDR_TYPE FROM PS_SSR_BR_BO_SETUP A ) AND PERS3.EMPLID = PERS1.EMPLID AND PERS3.EFFDT = ( SELECT MAX(PERS3A.EFFDT) FROM PS_ADDRESSES PERS3A WHERE PERS3.EMPLID = PERS3A.EMPLID AND PERS3.ADDRESS_TYPE = PERS3A.ADDRESS_TYPE AND PERS3A.EFFDT <= %CurrentDateIn)) <> ('NLD') THEN ' ' ELSE ( SELECT %Substring(PERS3.POSTAL, 1, 7) FROM PS_ADDRESSES PERS3 WHERE PERS3.ADDRESS_TYPE = ( SELECT A.ADDR_TYPE FROM PS_SSR_BR_BO_SETUP A ) AND PERS3.EMPLID =PERS1.EMPLID AND PERS3.EFFDT = ( SELECT MAX(PERS3A.EFFDT) FROM PS_ADDRESSES PERS3A WHERE PERS3.EMPLID = PERS3A.EMPLID AND PERS3.ADDRESS_TYPE = PERS3A.ADDRESS_TYPE AND PERS3A.EFFDT <= %CurrentDateIn)) END ,NAMES1.LAST_NAME ,NAMES1.NAME_ROYAL_PREFIX , NAMES1.SSR_BR_FST_NMS_NLD ,ADDR.SSR_BR_STREET_NLD ,ADDR.NUM1 , ADDR.NUM2 , ADDR.SSR_BR_LOC_DESCR , ADDR.CITY , ADDR.SSR_BR_HOUSE_I_NBR ,ADDR.SSR_BR_FR_ADDR_1 ,ADDR.SSR_BR_FR_ADDR_2 ,ADDR.SSR_BR_FR_ADDR_3 ,' ' ,' ',( SELECT STUDENT.SAD_GBA_B_YR_NLD FROM PS_SSR_STUDENT_NLD STUDENT WHERE STUDENT.EMPLID = PERS1.EMPLID), ( SELECT STUDENT.SAD_GBA_B_MN_NLD FROM PS_SSR_STUDENT_NLD STUDENT WHERE STUDENT.EMPLID = PERS1.EMPLID) FROM PS_PERSON PERS1, PS_SSR_BR_NAME_VW NAMES1, PS_SSR_BR_ADDR_VW ADDR WHERE PERS1.EMPLID = NAMES1.EMPLID AND PERS1.EMPLID = ADDR.EMPLID

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
2 BIRTHDATE Date(10) DATE Date of Birth
3 SSR_BR_SEX_NLD Character(1) VARCHAR2(1) NOT NULL Gender
F=Female
M=Male
U=Unknown
4 SAD_SL_SOFI_NBR Character(9) VARCHAR2(9) NOT NULL Social Fiscal Number
5 SSR_BR_NAT_ID_NLD Character(20) VARCHAR2(20) NOT NULL Educational Number
6 SSR_BR_COUNTRY_NLD Character(4) VARCHAR2(4) NOT NULL Country
7 SSR_BR_POSTAL_NLD Character(7) VARCHAR2(7) NOT NULL Postal Code
8 LAST_NAME Character(30) VARCHAR2(30) NOT NULL Last Name
9 NAME_ROYAL_PREFIX Character(15) VARCHAR2(15) NOT NULL Name Royal Prefix
10 SSR_BR_FST_NMS_NLD Character(200) VARCHAR2(200) NOT NULL Firstnames
11 SSR_BR_STREET_NLD Character(24) VARCHAR2(24) NOT NULL Street
12 NUM1 Character(6) VARCHAR2(6) NOT NULL Number 1
13 NUM2 Character(6) VARCHAR2(6) NOT NULL Number 2
14 SSR_BR_LOC_DESCR Character(35) VARCHAR2(35) NOT NULL Location Description
15 CITY Character(30) VARCHAR2(30) NOT NULL City
16 SSR_BR_HOUSE_I_NBR Character(2) VARCHAR2(2) NOT NULL House Number Indication
17 SSR_BR_FR_ADDR_1 Character(35) VARCHAR2(35) NOT NULL Foreign Address Line 1
18 SSR_BR_FR_ADDR_2 Character(35) VARCHAR2(35) NOT NULL Foreign Address Line 2
19 SSR_BR_FR_ADDR_3 Character(35) VARCHAR2(35) NOT NULL Foreign Address Line 3
20 SSR_BR_NATIONALITY Character(4) VARCHAR2(4) NOT NULL Nationality Code 1
21 SSR_BR_NATION_CD_2 Character(4) VARCHAR2(4) NOT NULL Nationality Code 2
22 SAD_GBA_B_YR_NLD Character(4) VARCHAR2(4) NOT NULL GBA Year of Birth
23 SAD_GBA_B_MN_NLD Character(2) VARCHAR2(2) NOT NULL GBA Month of Birth