VIEW: APPS.IGS_SV_EV_CRT_EV_BLK_V

Source

View Text - Preformatted

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' )

View Text - HTML Formatted

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' )