SAD_BR_M305_NLD

(SQL View)
 Index Back

SELECT DISTINCT ES.INSTITUTION , ES.SCC_BRINCD_NLD , ES.SAD_BR_EDU_SEC_NLD , ES.EMPLID , CASE WHEN ( SELECT STUDENT.SAD_GBA_B_YR_NLD FROM PS_SSR_STUDENT_NLD STUDENT WHERE STUDENT.EMPLID = ES.EMPLID) = (' ') OR ( SELECT STUDENT.SAD_GBA_B_YR_NLD FROM PS_SSR_STUDENT_NLD STUDENT WHERE STUDENT.EMPLID = ES.EMPLID) IS NULL THEN PERS1.BIRTHDATE END, ( SELECT PERS2.SEX FROM PS_PERS_DATA_EFFDT PERS2 WHERE ES.EMPLID = PERS2.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 = ES.EMPLID AND PERS_NID1.NATIONAL_ID_TYPE = ( SELECT A.SAD_BR_NID_TYP_NLD FROM PS_SAD_BR_INST_NLD A WHERE A.INSTITUTION = ES.INSTITUTION AND A.SCC_BRINCD_NLD = ES.SCC_BRINCD_NLD)), CASE WHEN ( SELECT PERS_NID1.NATIONAL_ID FROM PS_PERS_NID PERS_NID1 WHERE PERS_NID1.COUNTRY = 'NLD' AND PERS_NID1.EMPLID = ES.EMPLID AND PERS_NID1.NATIONAL_ID_TYPE = ( SELECT A.SAD_BR_NID_TYP_NLD FROM PS_SAD_BR_INST_NLD A WHERE A.INSTITUTION = ES.INSTITUTION AND A.SCC_BRINCD_NLD = ES.SCC_BRINCD_NLD)) >' ' THEN ' ' ELSE ( SELECT %Substring(PERS_NID.NATIONAL_ID, 1, 9) FROM PS_PERS_NID PERS_NID WHERE PERS_NID.EMPLID = ES.EMPLID AND PERS_NID.COUNTRY = 'NLD' AND PERS_NID.NATIONAL_ID_TYPE = ( SELECT A.SAD_BR_EDU_NBR_TYP FROM PS_SAD_BR_INST_NLD A WHERE A.INSTITUTION = ES.INSTITUTION AND A.SCC_BRINCD_NLD = ES.SCC_BRINCD_NLD)) END, CASE WHEN ( SELECT PERS3.COUNTRY FROM PS_ADDRESSES PERS3 WHERE PERS3.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 PERS3.EMPLID = ES.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_SAD_BR_INST_NLD A WHERE A.INSTITUTION = ES.INSTITUTION AND A.SCC_BRINCD_NLD = ES.SCC_BRINCD_NLD) AND PERS3.EMPLID = ES.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_SAD_BR_INST_NLD A WHERE A.INSTITUTION = ES.INSTITUTION AND A.SCC_BRINCD_NLD = ES.SCC_BRINCD_NLD) AND PERS3.EMPLID = ES.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_SAD_BR_INST_NLD A WHERE A.INSTITUTION = ES.INSTITUTION AND A.SCC_BRINCD_NLD = ES.SCC_BRINCD_NLD) AND PERS3.EMPLID = ES.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_SAD_BR_INST_NLD A WHERE A.INSTITUTION = ES.INSTITUTION AND A.SCC_BRINCD_NLD = ES.SCC_BRINCD_NLD) AND PERS3.EMPLID = ES.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_SAD_BR_INST_NLD A WHERE A.INSTITUTION = ES.INSTITUTION AND A.SCC_BRINCD_NLD = ES.SCC_BRINCD_NLD) AND PERS3.EMPLID = ES.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, ( SELECT STUDENT.SAD_GBA_B_YR_NLD FROM PS_SSR_STUDENT_NLD STUDENT WHERE STUDENT.EMPLID = ES.EMPLID), ( SELECT STUDENT.SAD_GBA_B_MN_NLD FROM PS_SSR_STUDENT_NLD STUDENT WHERE STUDENT.EMPLID = ES.EMPLID) FROM PS_SAD_BR_ENRSQNLD ES JOIN PS_PERSON PERS1 ON ES.EMPLID = PERS1.EMPLID

# 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 BIRTHDATE Date(10) DATE Date of Birth
6 SAD_BR_SEX_NLD Character(1) VARCHAR2(1) NOT NULL BRON Sex
M=Male
O=Unknown
V=Female
7 SAD_SL_SOFI_NBR Character(9) VARCHAR2(9) NOT NULL Social Fiscal Number
8 SAD_BR_NAT_ID_NLD Character(20) VARCHAR2(20) NOT NULL Educational Number
9 SAD_BR_COUNTRY_NLD Character(4) VARCHAR2(4) NOT NULL Country
10 SAD_BR_POSTAL_NLD Character(7) VARCHAR2(7) NOT NULL Postal Code
11 SAD_GBA_B_YR_NLD Character(4) VARCHAR2(4) NOT NULL GBA Year of Birth
12 SAD_GBA_B_MN_NLD Character(2) VARCHAR2(2) NOT NULL GBA Month of Birth