[Home] [Help]
| Product: | IGS - Student System (Obsolete) |
|---|---|
| Description: | Create Exchange Visitor Block View. |
| Implementation/DBA Data: | Not implemented in this database |
SELECT PERS.BATCH_ID BATCH_ID
, PERS.PERSON_ID PERSON_ID
, PERS.PRINT_FORM PRINT_FORM
, PERS.SEVIS_USER_ID SEVIS_USER_ID
, PERS.RECORD_NUMBER RECORD_NUMBER
, NULL EV_LAST_NAME
, NULL EV_FIRST_NAME
, NULL EV_MIDDLE_NAME
, NULL EV_SUFFIX
, NULL EV_BIRTH_DATE
, NULL EV_GENDER
, NULL EV_BIRTH_CITY
, NULL EV_BIRTH_CNTRY_CODE
, NULL EV_CITIZEN_CNTRY_CODE
, NULL EV_PERM_RES_CNTRY_CODE
, NULL POSITION_CODE
, NULL EV_VISA_TYPE
, NULL US_ADDRESS_LINE1
, NULL US_ADDRESS_LINE2
, NULL US_CITY
, NULL US_STATE
, NULL US_POSTAL_CODE
, NULL US_POSTAL_ROUTING_CODE
, PERS.EV_CREATE_REASON CREATE_REASON_CODE
, PERS.INIT_PRGM_START_DATE INITIAL_PRGM_DATE
, PERS.EV_FORM_NUMBER FORM_NUMBER
, PERS.NEXT_SESSION_START_DATE PRGM_START_DATE
, PERS.CURR_SESSION_END_DATE PRGM_END_DATE
, NULL RECVD_US_GOV_FUNDS
, NULL PRGM_SPONSOR_FUNDS
, NULL US_GOVT_ORG1_CODE
, NULL US_GOVT_ORG1_FUNDS
, NULL US_GOVT_ORG2_CODE
, NULL US_GOVT_ORG2_FUNDS
, NULL INTL_GOVT_ORG1_CODE
, NULL INTL_GOVT_ORG1_FUNDS
, NULL INTL_GOVT_ORG2_CODE
, NULL INTL_GOVT_ORG2_FUNDS
, NULL EV_GOVT_FUNDS
, NULL BI_NATIONAL_COMM_FUNDS
, NULL OTHER_ORG_FUNDS
, NULL PERSONAL_FUNDS
, NULL FUND_REMARKS
, NULL CATEGORY_CODE
, NULL SUBJECT_FIELD_CODE
, NULL SUBJECT_REMARKS
, PERS.PDSO_SEVIS_ID PDSO_SEVIS_ID
, PERS.PERSON_ID STDNT_PERSON_ID
, LTRIM(TO_CHAR(SUBSTR(PERS.PERSON_ID
, 1
, 10)
, '0000000000')) STDNT_PERSON_NUMBER
, LTRIM(TO_CHAR(SUBSTR(PERS.PERSON_ID
, 1
, 14)
, '00000000000000')) PERSON_ID_LONG
FROM IGS_SV_PERSONS PERS
, IGS_SV_BTCH_SUMMARY BIOSUMM
, IGS_SV_BTCH_SUMMARY UADDSUMM
, IGS_SV_BTCH_SUMMARY PRGMUMM
, IGS_SV_BTCH_SUMMARY FINSUMM
WHERE PERS.RECORD_STATUS = 'N'
AND PERS.PERSON_ID = BIOSUMM.PERSON_ID
AND PERS.BATCH_ID = BIOSUMM.BATCH_ID
AND BIOSUMM.TAG_CODE = 'SV_BIO'
AND BIOSUMM.ADM_ACTION_CODE = 'SEND'
AND PERS.PERSON_ID = UADDSUMM.PERSON_ID
AND PERS.BATCH_ID = UADDSUMM.BATCH_ID
AND UADDSUMM.TAG_CODE = 'SV_US_ADDR'
AND UADDSUMM.ADM_ACTION_CODE = 'SEND'
AND PERS.PERSON_ID = PRGMUMM.PERSON_ID
AND PERS.BATCH_ID = PRGMUMM.BATCH_ID
AND PRGMUMM.TAG_CODE = 'SV_PRGMS'
AND PRGMUMM.ADM_ACTION_CODE = 'SEND'
AND PERS.PERSON_ID = FINSUMM.PERSON_ID
AND PERS.BATCH_ID = FINSUMM.BATCH_ID
AND FINSUMM.TAG_CODE = 'SV_FINANCIAL'
AND FINSUMM.ADM_ACTION_CODE = 'SEND'
AND EXISTS ( SELECT 1
FROM IGS_SV_BTCH_SUMMARY SOASUMM
WHERE PERS.PERSON_ID = SOASUMM.PERSON_ID
AND PERS.BATCH_ID = SOASUMM.BATCH_ID
AND SOASUMM.TAG_CODE = 'SV_SOA'
AND SOASUMM.ADM_ACTION_CODE = 'SEND' )