[Home] [Help]
| Product: | IGS - Student System (Obsolete) |
|---|---|
| Description: | View for obtaining information for the XML block describing the students registration information. |
| Implementation/DBA Data: | Not implemented in this database |
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' ) )