VIEW: APPS.IGS_SV_STDNT_REG_BLK_V

Source

View Text - Preformatted

SELECT prs.batch_id, PRS.pdso_sevis_id pdso_sevis_id, prs.person_id, prs.print_form, prs.curr_session_end_date current_session_end_date, prs.next_session_start_date next_session_start_date, bio.commuter commuter, NULL us_address_line1, NULL us_address_line2, NULL us_city, NULL us_state, NULL us_postal_code, NULL us_postal_routing_code, NULL fn_address_line1, NULL fn_address_line2, NULL fn_city, NULL fn_cntry_code, NULL fn_province, NULL fn_postal_code, NULL pspt_number, NULL pspt_issuing_cntry, NULL pspt_expiry_date, NULL visa_number, NULL visa_issuing_cntry, NULL visa_expiry_date, NULL admission_number, NULL port_of_entry, NULL date_of_entry, NULL remarks, prs.last_session_flag, prs.sevis_user_id sevis_user_id, prs.record_number record_number, ltrim(to_char(substr(prs.person_id, 1, 10), '0000000000')) person_number, ltrim(to_char(substr(prs.person_id, 1, 14), '00000000000000')) person_id_long, decode(prs.REPRINT_RSN_CODE,'11',null, prs.REPRINT_RSN_CODE) reprint_reason, prs.reprint_remarks FROM igs_sv_persons prs, igs_sv_bio_info bio WHERE Prs.person_id = bio.person_id and Bio.batch_id = (select max(binfo.batch_id) from igs_sv_bio_info binfo,igs_sv_batches btch where binfo.person_id = prs.person_id and binfo.batch_id = btch.batch_id and btch.batch_status <> 'E') and prs.record_status = 'C' AND ( ( prs.curr_session_end_date IS NOT NULL AND prs.next_session_start_date IS NOT NULL ) OR EXISTS ( SELECT 1 FROM igs_sv_btch_summary summ WHERE summ.person_id = prs.person_id AND summ.batch_id = prs.batch_id AND summ.TAG_CODE IN ('SV_BIO','SV_US_ADDR','SV_F_ADDR','SV_LEGAL') AND summ.ADM_ACTION_CODE = 'SEND' ) )

View Text - HTML Formatted

SELECT PRS.BATCH_ID
, PRS.PDSO_SEVIS_ID PDSO_SEVIS_ID
, PRS.PERSON_ID
, PRS.PRINT_FORM
, PRS.CURR_SESSION_END_DATE CURRENT_SESSION_END_DATE
, PRS.NEXT_SESSION_START_DATE NEXT_SESSION_START_DATE
, BIO.COMMUTER COMMUTER
, NULL US_ADDRESS_LINE1
, NULL US_ADDRESS_LINE2
, NULL US_CITY
, NULL US_STATE
, NULL US_POSTAL_CODE
, NULL US_POSTAL_ROUTING_CODE
, NULL FN_ADDRESS_LINE1
, NULL FN_ADDRESS_LINE2
, NULL FN_CITY
, NULL FN_CNTRY_CODE
, NULL FN_PROVINCE
, NULL FN_POSTAL_CODE
, NULL PSPT_NUMBER
, NULL PSPT_ISSUING_CNTRY
, NULL PSPT_EXPIRY_DATE
, NULL VISA_NUMBER
, NULL VISA_ISSUING_CNTRY
, NULL VISA_EXPIRY_DATE
, NULL ADMISSION_NUMBER
, NULL PORT_OF_ENTRY
, NULL DATE_OF_ENTRY
, NULL REMARKS
, PRS.LAST_SESSION_FLAG
, PRS.SEVIS_USER_ID SEVIS_USER_ID
, PRS.RECORD_NUMBER RECORD_NUMBER
, LTRIM(TO_CHAR(SUBSTR(PRS.PERSON_ID
, 1
, 10)
, '0000000000')) PERSON_NUMBER
, LTRIM(TO_CHAR(SUBSTR(PRS.PERSON_ID
, 1
, 14)
, '00000000000000')) PERSON_ID_LONG
, DECODE(PRS.REPRINT_RSN_CODE
, '11'
, NULL
, PRS.REPRINT_RSN_CODE) REPRINT_REASON
, PRS.REPRINT_REMARKS
FROM IGS_SV_PERSONS PRS
, IGS_SV_BIO_INFO BIO
WHERE PRS.PERSON_ID = BIO.PERSON_ID
AND BIO.BATCH_ID = (SELECT MAX(BINFO.BATCH_ID)
FROM IGS_SV_BIO_INFO BINFO
, IGS_SV_BATCHES BTCH
WHERE BINFO.PERSON_ID = PRS.PERSON_ID
AND BINFO.BATCH_ID = BTCH.BATCH_ID
AND BTCH.BATCH_STATUS <> 'E')
AND PRS.RECORD_STATUS = 'C'
AND ( ( PRS.CURR_SESSION_END_DATE IS NOT NULL
AND PRS.NEXT_SESSION_START_DATE IS NOT NULL ) OR EXISTS ( SELECT 1
FROM IGS_SV_BTCH_SUMMARY SUMM
WHERE SUMM.PERSON_ID = PRS.PERSON_ID
AND SUMM.BATCH_ID = PRS.BATCH_ID
AND SUMM.TAG_CODE IN ('SV_BIO'
, 'SV_US_ADDR'
, 'SV_F_ADDR'
, 'SV_LEGAL')
AND SUMM.ADM_ACTION_CODE = 'SEND' ) )