VIEW: APPS.IGS_SV_CRT_STDT_OTHER_V

Source

View Text - Preformatted

SELECT pers.batch_id, pers.person_id, legl.i94_number admission_number, othr.drivers_license, othr.drivers_license_state, othr.ssn, othr.tax_id FROM igs_sv_persons pers, (SELECT othr.drivers_license, othr.drivers_license_state, othr.ssn, othr.tax_id, otherSumm.batch_id, otherSumm.person_id FROM IGS_SV_BTCH_SUMMARY otherSumm, igs_sv_oth_info othr WHERE otherSumm.TAG_CODE = 'SV_OTHER' AND otherSumm.ADM_ACTION_CODE = 'SEND' AND othr.person_id = otherSumm.person_id and othr.batch_id = otherSumm.batch_id) othr, (SELECT legl.i94_number, leglSumm.batch_id, leglSumm.person_id FROM IGS_SV_BTCH_SUMMARY leglSumm, igs_sv_legal_info legl WHERE leglSumm.TAG_CODE = 'SV_LEGAL' AND leglSumm.ADM_ACTION_CODE = 'SEND' AND legl.person_id = leglSumm.person_id and legl.batch_id = leglSumm.batch_id) legl WHERE legl.batch_id (+) = pers.batch_id AND legl.person_id (+) = pers.person_id AND othr.batch_id (+) = pers.batch_id AND othr.person_id (+) = pers.person_id 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_LEGAL' , 'SV_OTHER') AND summ.ADM_ACTION_CODE = 'SEND' )

View Text - HTML Formatted

SELECT PERS.BATCH_ID
, PERS.PERSON_ID
, LEGL.I94_NUMBER ADMISSION_NUMBER
, OTHR.DRIVERS_LICENSE
, OTHR.DRIVERS_LICENSE_STATE
, OTHR.SSN
, OTHR.TAX_ID
FROM IGS_SV_PERSONS PERS
, (SELECT OTHR.DRIVERS_LICENSE
, OTHR.DRIVERS_LICENSE_STATE
, OTHR.SSN
, OTHR.TAX_ID
, OTHERSUMM.BATCH_ID
, OTHERSUMM.PERSON_ID
FROM IGS_SV_BTCH_SUMMARY OTHERSUMM
, IGS_SV_OTH_INFO OTHR
WHERE OTHERSUMM.TAG_CODE = 'SV_OTHER'
AND OTHERSUMM.ADM_ACTION_CODE = 'SEND'
AND OTHR.PERSON_ID = OTHERSUMM.PERSON_ID
AND OTHR.BATCH_ID = OTHERSUMM.BATCH_ID) OTHR
, (SELECT LEGL.I94_NUMBER
, LEGLSUMM.BATCH_ID
, LEGLSUMM.PERSON_ID
FROM IGS_SV_BTCH_SUMMARY LEGLSUMM
, IGS_SV_LEGAL_INFO LEGL
WHERE LEGLSUMM.TAG_CODE = 'SV_LEGAL'
AND LEGLSUMM.ADM_ACTION_CODE = 'SEND'
AND LEGL.PERSON_ID = LEGLSUMM.PERSON_ID
AND LEGL.BATCH_ID = LEGLSUMM.BATCH_ID) LEGL
WHERE LEGL.BATCH_ID (+) = PERS.BATCH_ID
AND LEGL.PERSON_ID (+) = PERS.PERSON_ID
AND OTHR.BATCH_ID (+) = PERS.BATCH_ID
AND OTHR.PERSON_ID (+) = PERS.PERSON_ID
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_LEGAL'
, 'SV_OTHER')
AND SUMM.ADM_ACTION_CODE = 'SEND' )