[Home] [Help]
| Product: | IGS - Student System (Obsolete) |
|---|---|
| Description: | View for the creation of the Exchange Visitor dependent block in the XML file sent to SEVIS |
| Implementation/DBA Data: | Not implemented in this database |
SELECT PERS.BATCH_ID BATCH_ID
, PERS.PERSON_ID PERSON_ID
, PERS.RECORD_NUMBER RECORD_NUMBER
, SVDI.DEPDNT_SEVIS_ID DEPDNT_SEVIS_ID
, PERS.PDSO_SEVIS_ID PDSO_SEVIS_ID
, SVDI.DEPDNT_ID DEPDNT_ID
, LTRIM(TO_CHAR(SUBSTR(SVDI.DEPDNT_ID
, 1
, 10)
, '0000000000')) PERSON_NUMBER
, LTRIM(TO_CHAR(SUBSTR(SVDI.DEPDNT_ID
, 1
, 14)
, '00000000000000')) PERSON_ID_LONG
, PERS.PRINT_FORM PRINT_FORM
, PERS.SEVIS_USER_ID SEVIS_USER_ID
, LTRIM(TO_CHAR(SUBSTR(PERS.PERSON_ID
, 1
, 10)
, '0000000000')) SV_PERSON_NUMBER
, LTRIM(TO_CHAR(SUBSTR(PERS.PERSON_ID
, 1
, 14)
, '00000000000000')) SV_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
FROM IGS_SV_PERSONS PERS
, IGS_SV_DEPDNT_INFO SVDI
, IGS_SV_BTCH_SUMMARY DEPSUMM
WHERE PERS.BATCH_ID = SVDI.BATCH_ID
AND PERS.PERSON_ID = SVDI.PERSON_ID
AND PERS.RECORD_STATUS = 'C'
AND SVDI.PERSON_ID = DEPSUMM.PERSON_ID
AND SVDI.BATCH_ID = DEPSUMM.BATCH_ID
AND DEPSUMM.TAG_CODE = 'SV_DEPDNT'
AND DEPSUMM.ADM_ACTION_CODE = 'SEND'
AND SVDI.DEPDNT_ID = DEPSUMM.OWNER_TABLE_IDENTIFIER