[Home] [Help]
| Product: | IGS - Student System (Obsolete) |
|---|---|
| Description: | Edit Exchange Visitor Block View. |
| Implementation/DBA Data: | Not implemented in this database |
SELECT PERS.BATCH_ID BATCH_ID
, PERS.PERSON_ID PERSON_ID
, BIO_INFO.LAST_NAME
, BIO_INFO.FIRST_NAME
, BIO_INFO.MIDDLE_NAME
, BIO_INFO.SUFFIX
, BIO_INFO.BIRTH_DATE
, BIO_INFO.GENDER
, BIO_INFO.BIRTH_CITY
, BIO_INFO.BIRTH_CNTRY_CODE
, BIO_INFO.CITIZEN_CNTRY_CODE
, BIO_INFO.PERM_RES_CNTRY_CODE
, NULL US_ADDRESS_LINE1
, NULL US_ADDRESS_LINE2
, NULL US_CITY
, NULL US_STATE
, NULL US_POSTAL_CODE
, NULL US_POSTAL_ROUTING_CODE
, BIO_INFO.POSITION_CODE
, BIO_INFO.REMARKS
, BIO_INFO.BIRTH_CNTRY_RESN_CODE
, PERS.PRINT_FORM
, PERS.PDSO_SEVIS_ID
, PERS.SEVIS_USER_ID SEVIS_USER_ID
, PERS.RECORD_NUMBER RECORD_NUMBER
, LTRIM(TO_CHAR(SUBSTR(PERS.PERSON_ID
, 1
, 10)
, '0000000000')) PERSON_NUMBER
, LTRIM(TO_CHAR(SUBSTR(PERS.PERSON_ID
, 1
, 14)
, '00000000000000')) PERSON_ID_LONG
, PERS.REPRINT_RSN_CODE REPRINT_REASON
, DECODE(PERS.REPRINT_RSN_CODE
, '05'
, NULL
, PERS.REPRINT_REMARKS) REPRINT_REMARKS
, DECODE(PERS.REPRINT_RSN_CODE
, '05'
, PERS.REPRINT_REMARKS
, NULL) OTHER_REMARKS
FROM IGS_SV_PERSONS PERS
, ( SELECT BIO.PERSON_ID
, BIO.BATCH_ID
, BIO.LAST_NAME
, BIO.FIRST_NAME
, BIO.MIDDLE_NAME
, BIO.SUFFIX
, BIO.BIRTH_DATE
, BIO.GENDER
, BIO.BIRTH_CITY
, BIO.BIRTH_CNTRY_CODE
, BIO.CITIZEN_CNTRY_CODE
, BIO.LEGAL_RES_CNTRY_CODE PERM_RES_CNTRY_CODE
, BIO.POSITION_CODE
, BIO.BIRTH_CNTRY_RESN_CODE
, BIO.REMARKS
FROM IGS_SV_BIO_INFO BIO
, IGS_SV_BTCH_SUMMARY BIOSUMM
, IGS_SV_PERSONS PRS
WHERE PRS.RECORD_STATUS = 'C'
AND PRS.BATCH_ID = BIO.BATCH_ID
AND PRS.PERSON_ID = BIO.PERSON_ID
AND BIO.BATCH_ID = BIOSUMM.BATCH_ID
AND BIO.PERSON_ID = BIOSUMM.PERSON_ID
AND BIOSUMM.TAG_CODE = 'SV_BIO'
AND BIOSUMM.ADM_ACTION_CODE = 'SEND' ) BIO_INFO
WHERE PERS.RECORD_STATUS = 'C'
AND EXISTS ( SELECT 1
FROM IGS_SV_BTCH_SUMMARY SUMM
WHERE SUMM.PERSON_ID = PERS.PERSON_ID
AND SUMM.BATCH_ID = PERS.BATCH_ID
AND SUMM.TAG_CODE IN ('SV_BIO'
, 'SV_US_ADDR')
AND SUMM.ADM_ACTION_CODE = 'SEND' )
AND PERS.PERSON_ID = BIO_INFO.PERSON_ID (+)
AND PERS.BATCH_ID = BIO_INFO.BATCH_ID (+)