VIEW: APPS.IGS_SV_EDT_PERS_BLK_V

Source

View Text - Preformatted

SELECT pers.batch_id, PERS.pdso_sevis_id pdso_sevis_id, pers.person_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, NULL print_form, bio_info.first_name, bio_info.last_name, bio_info.middle_name, bio_info.birth_date, bio_info.gender, bio_info.birth_cntry_code, bio_info.ctznship_cntry_code, NULL fn_address_line1, NULL fn_address_line2, NULL fn_city, NULL fn_cntry_code, NULL fn_province, NULL fn_postal_code, NULL us_address_line1, NULL us_address_line2, NULL us_city, NULL us_state, NULL us_postal_code, NULL us_postal_routing_code, NULL drivers_license, NULL drivers_license_state, NULL ssn, NULL tax_id, NULL admission_number, bio_info.commuter, bio_info.suffix FROM igs_sv_persons pers , ( SELECT bio.batch_id, bio.person_id, bio.first_name, bio.last_name, bio.middle_name, bio.birth_date, bio.gender, bio.birth_cntry_code, bio.CITIZEN_CNTRY_CODE ctznship_cntry_code, bio.COMMUTER, bio.SUFFIX 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 bioSumm WHERE bioSumm.person_id = pers.person_id AND bioSumm.batch_id = pers.batch_id AND bioSumm.TAG_CODE IN ('SV_BIO','SV_US_ADDR','SV_F_ADDR','SV_LEGAL','SV_OTHER') AND bioSumm.ADM_ACTION_CODE = 'SEND' ) AND pers.person_id = bio_info.person_id (+) AND pers.batch_id = bio_info.batch_id (+)

View Text - HTML Formatted

SELECT PERS.BATCH_ID
, PERS.PDSO_SEVIS_ID PDSO_SEVIS_ID
, PERS.PERSON_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
, NULL PRINT_FORM
, BIO_INFO.FIRST_NAME
, BIO_INFO.LAST_NAME
, BIO_INFO.MIDDLE_NAME
, BIO_INFO.BIRTH_DATE
, BIO_INFO.GENDER
, BIO_INFO.BIRTH_CNTRY_CODE
, BIO_INFO.CTZNSHIP_CNTRY_CODE
, NULL FN_ADDRESS_LINE1
, NULL FN_ADDRESS_LINE2
, NULL FN_CITY
, NULL FN_CNTRY_CODE
, NULL FN_PROVINCE
, NULL FN_POSTAL_CODE
, NULL US_ADDRESS_LINE1
, NULL US_ADDRESS_LINE2
, NULL US_CITY
, NULL US_STATE
, NULL US_POSTAL_CODE
, NULL US_POSTAL_ROUTING_CODE
, NULL DRIVERS_LICENSE
, NULL DRIVERS_LICENSE_STATE
, NULL SSN
, NULL TAX_ID
, NULL ADMISSION_NUMBER
, BIO_INFO.COMMUTER
, BIO_INFO.SUFFIX
FROM IGS_SV_PERSONS PERS
, ( SELECT BIO.BATCH_ID
, BIO.PERSON_ID
, BIO.FIRST_NAME
, BIO.LAST_NAME
, BIO.MIDDLE_NAME
, BIO.BIRTH_DATE
, BIO.GENDER
, BIO.BIRTH_CNTRY_CODE
, BIO.CITIZEN_CNTRY_CODE CTZNSHIP_CNTRY_CODE
, BIO.COMMUTER
, BIO.SUFFIX
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 BIOSUMM
WHERE BIOSUMM.PERSON_ID = PERS.PERSON_ID
AND BIOSUMM.BATCH_ID = PERS.BATCH_ID
AND BIOSUMM.TAG_CODE IN ('SV_BIO'
, 'SV_US_ADDR'
, 'SV_F_ADDR'
, 'SV_LEGAL'
, 'SV_OTHER')
AND BIOSUMM.ADM_ACTION_CODE = 'SEND' )
AND PERS.PERSON_ID = BIO_INFO.PERSON_ID (+)
AND PERS.BATCH_ID = BIO_INFO.BATCH_ID (+)