SAD_BR_M305_VA(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_ENRSQ_VA 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 |