VIEW: APPS.IGS_SV_CRT_STDT_BLK_V

Source

View Text - Preformatted

SELECT pers.batch_id batch_id, pers.person_id, pers.print_form, NULL stdnt_last_name, NULL stdnt_first_name, NULL stdnt_middle_name, NULL stdnt_suffix, NULL stdnt_birth_date, NULL stdnt_gender, NULL stdnt_birth_cntry_code, NULL stdnt_citizen_cntry_code, NULL stdnt_commuter, NULL stdnt_visa_type, NULL dmission_number, NULL drivers_license, NULL drivers_license_state, NULL ssn, NULL tax_id, NULL fn_address_line1, NULL fn_address_line2, NULL fn_city, NULL fn_province, NULL fn_country_code, 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 education_level, NULL educ_lvl_remarks, NULL primary_major, NULL second_major, NULL minor, NULL length_of_study, NULL prg_start_date, NULL prg_end_date, NULL engl_required, NULL engl_requirements_met, NULL engl_not_reqd_reason, NULL academic_term, NULL tuition, NULL living_expense, NULL dependent_expense, NULL other_expense, NULL other_expense_desc, NULL personal_funds, NULL school_funds, NULL school_funds_desc, NULL other_funds, NULL other_funds_desc, NULL employment_funds, NULL funding_remarks, pers.record_number, pers.pdso_sevis_id pdso_sevis_id, pers.person_id stdnt_person_id, ltrim(to_char(substr(pers.person_id, 1, 10), '0000000000')) stdnt_person_number, NULL prog_remarks, ltrim(to_char(substr(pers.person_id, 1, 14), '00000000000000')) person_id_long, pers.remarks, pers.issuing_reason issue_reason FROM igs_sv_persons pers, IGS_SV_BTCH_SUMMARY bioSumm, IGS_SV_BTCH_SUMMARY faddSumm, IGS_SV_BTCH_SUMMARY prgmSumm, IGS_SV_BTCH_SUMMARY finSumm WHERE pers.record_status = 'N' AND pers.person_id = bioSumm.person_id and pers.batch_id = bioSumm.batch_id and bioSumm.TAG_CODE = 'SV_BIO' AND bioSumm.ADM_ACTION_CODE = 'SEND' AND pers.person_id = faddSumm.person_id and pers.batch_id = faddSumm.batch_id and faddSumm.TAG_CODE = 'SV_F_ADDR' AND faddSumm.ADM_ACTION_CODE = 'SEND' AND pers.person_id = prgmSumm.person_id and pers.batch_id = prgmSumm.batch_id and prgmSumm.TAG_CODE = 'SV_PRGMS' AND prgmSumm.ADM_ACTION_CODE = 'SEND' AND pers.person_id = finSumm.person_id and pers.batch_id = finSumm.batch_id and finSumm.TAG_CODE = 'SV_FINANCIAL' AND finSumm.ADM_ACTION_CODE = 'SEND'

View Text - HTML Formatted

SELECT PERS.BATCH_ID BATCH_ID
, PERS.PERSON_ID
, PERS.PRINT_FORM
, NULL STDNT_LAST_NAME
, NULL STDNT_FIRST_NAME
, NULL STDNT_MIDDLE_NAME
, NULL STDNT_SUFFIX
, NULL STDNT_BIRTH_DATE
, NULL STDNT_GENDER
, NULL STDNT_BIRTH_CNTRY_CODE
, NULL STDNT_CITIZEN_CNTRY_CODE
, NULL STDNT_COMMUTER
, NULL STDNT_VISA_TYPE
, NULL DMISSION_NUMBER
, NULL DRIVERS_LICENSE
, NULL DRIVERS_LICENSE_STATE
, NULL SSN
, NULL TAX_ID
, NULL FN_ADDRESS_LINE1
, NULL FN_ADDRESS_LINE2
, NULL FN_CITY
, NULL FN_PROVINCE
, NULL FN_COUNTRY_CODE
, 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 EDUCATION_LEVEL
, NULL EDUC_LVL_REMARKS
, NULL PRIMARY_MAJOR
, NULL SECOND_MAJOR
, NULL MINOR
, NULL LENGTH_OF_STUDY
, NULL PRG_START_DATE
, NULL PRG_END_DATE
, NULL ENGL_REQUIRED
, NULL ENGL_REQUIREMENTS_MET
, NULL ENGL_NOT_REQD_REASON
, NULL ACADEMIC_TERM
, NULL TUITION
, NULL LIVING_EXPENSE
, NULL DEPENDENT_EXPENSE
, NULL OTHER_EXPENSE
, NULL OTHER_EXPENSE_DESC
, NULL PERSONAL_FUNDS
, NULL SCHOOL_FUNDS
, NULL SCHOOL_FUNDS_DESC
, NULL OTHER_FUNDS
, NULL OTHER_FUNDS_DESC
, NULL EMPLOYMENT_FUNDS
, NULL FUNDING_REMARKS
, PERS.RECORD_NUMBER
, PERS.PDSO_SEVIS_ID PDSO_SEVIS_ID
, PERS.PERSON_ID STDNT_PERSON_ID
, LTRIM(TO_CHAR(SUBSTR(PERS.PERSON_ID
, 1
, 10)
, '0000000000')) STDNT_PERSON_NUMBER
, NULL PROG_REMARKS
, LTRIM(TO_CHAR(SUBSTR(PERS.PERSON_ID
, 1
, 14)
, '00000000000000')) PERSON_ID_LONG
, PERS.REMARKS
, PERS.ISSUING_REASON ISSUE_REASON
FROM IGS_SV_PERSONS PERS
, IGS_SV_BTCH_SUMMARY BIOSUMM
, IGS_SV_BTCH_SUMMARY FADDSUMM
, IGS_SV_BTCH_SUMMARY PRGMSUMM
, IGS_SV_BTCH_SUMMARY FINSUMM
WHERE PERS.RECORD_STATUS = 'N'
AND PERS.PERSON_ID = BIOSUMM.PERSON_ID
AND PERS.BATCH_ID = BIOSUMM.BATCH_ID
AND BIOSUMM.TAG_CODE = 'SV_BIO'
AND BIOSUMM.ADM_ACTION_CODE = 'SEND'
AND PERS.PERSON_ID = FADDSUMM.PERSON_ID
AND PERS.BATCH_ID = FADDSUMM.BATCH_ID
AND FADDSUMM.TAG_CODE = 'SV_F_ADDR'
AND FADDSUMM.ADM_ACTION_CODE = 'SEND'
AND PERS.PERSON_ID = PRGMSUMM.PERSON_ID
AND PERS.BATCH_ID = PRGMSUMM.BATCH_ID
AND PRGMSUMM.TAG_CODE = 'SV_PRGMS'
AND PRGMSUMM.ADM_ACTION_CODE = 'SEND'
AND PERS.PERSON_ID = FINSUMM.PERSON_ID
AND PERS.BATCH_ID = FINSUMM.BATCH_ID
AND FINSUMM.TAG_CODE = 'SV_FINANCIAL'
AND FINSUMM.ADM_ACTION_CODE = 'SEND'