View: IGS_SV_CRT_STDT_OTHER_V

View Text

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

Columns