VIEW: APPS.IGSFV_UC_APPLICANTS

Source

View Text - Preformatted

SELECT AP.APP_ID APPLICANT_ID, AP.PERSONAL_ID PERSONAL_ID, AP.APP_NO UCAS_APPLICANT_NUMBER, AP.CHECK_DIGIT UCAS_APPLICANT_CHECK_DIGIT, AP.ENQUIRY_NO ENQUIRY_NO, AP.OSS_PERSON_ID OSS_PERSON_ID, AP.APPLICATION_SOURCE APPLICATION_SOURCE, AP.NAME_CHANGE_DATE UCAS_NAME_CHANGE_DATE, AP.STUDENT_SUPPORT STUDENT_SUPPORT_ARRANGEMENT, AP.ADDRESS_AREA ADDRESS_AREA, AP.APPLICATION_DATE APPLICATION_DATE, AP.APPLICATION_SENT_DATE APPLICATION_SENT_DATE, AP.APPLICATION_SENT_RUN APPLICATION_SENT_RUN, AP.FEE_PAYER_CODE FEE_PAYER_CODE, AP.FEE_TEXT FEE_TEXT, AP.DOMICILE_APR DOMICILE_CODE, AP.CODE_CHANGED_DATE CODE_CHANGED_DATE, AP.SCHOOL SCHOOL, AP.WITHDRAWN WITHDRAWN, AP.WITHDRAWN_DATE WITHDRAWN_DATE, AP.REL_TO_CLEAR_REASON RELEASE_TO_CLEARING_REASON, AP.ROUTE_B ROUTE_B_FLAG, AP.EXAM_CHANGE_DATE EXAM_CHANGE_DATE, AP.WINTER QUAL_NUM_WINTER_A_LEVELS, AP.PREVIOUS QUAL_NUM_PREVIOUS_QUALS, AP.BTEC QUAL_BTEC_FLAG, AP.ILC QUAL_ILC_FLAG, AP.AILC QUAL_AILC_FLAG, AP.IB QUAL_IB_FLAG, AP.MANUAL MANUAL_QUAL_CHECK, AP.REG_NUM BTEC_REGISTRATION_NUMBER, AP.OEQ OTHER_EXAMINATION_QUAL_FLAG, AP.EAS ELECTRONIC_APPL_SYSTEM_FLAG, AP.ROA RECORD_OF_ACHEIVEMENT_FLAG, AP.STATUS APPLICANT_STATUS, AP.FIRM_NOW FIRM_CHOICE_NUMBER, AP.FIRM_REPLY FIRM_REPLY, AP.INSURANCE_REPLY INSURANCE_REPLY, AP.CONF_HIST_FIRM_REPLY CONFIRMATION_HIST_FIRM_REPLY, AP.CONF_HIST_INS_REPLY CONFIRMATION_HIST_INSU_REPLY, AP.RESIDENTIAL_CATEGORY RESIDENTIAL_CATEGORY, AP.PERSONAL_STATEMENT PERSONAL_STATEMENT, AP.MATCH_PREV MATCH_PREVIOUS_FLAG, AP.MATCH_PREV_DATE MATCH_PREVIOUS_DATE, AP.MATCH_WINTER MATCH_WINTER_FLAG, AP.MATCH_SUMMER MATCH_SUMMER_FLAG, AP.IB_DATE IB_MOVED_DATE, AP.ILC_DATE ILC_MOVED_DATE, AP.AILC_DATE AILC_MOVED_DATE, AP.GCSEQA_DATE GCSEQA_MOVED_DATE, AP.UK_ENTRY_DATE UK_ENTRY_DATE, AP.PREV_SURNAME PREVIOUS_SURNAME, AP.CRIMINAL_CONVICTIONS CRIMINAL_CONVICTIONS_FLAG, AP.SENT_TO_HESA SENT_TO_HESA_FLAG, AP.BATCH_IDENTIFIER BATCH_IDENTIFIER, AP.GCE QUAL_NUM_GCE, AP.VCE QUAL_NUM_VCE, AP.SQA QUAL_NUM_SQA, AP.PREVIOUSAS QUAL_NUM_PREVIOUS_AS, AP.KEYSKILLS KEYSKILLS_FLAG, AP.VOCATIONAL VOCATIONAL_FLAG, AP.SCN SCOTTISH_CANDIDATE_NUMBER, AP.PREVOEQ PREVIOUS_OTHER_EXAM_QUAL_FLAG, AP.CHOICES_TRANSPARENT_IND CHOICES_TRANSPARENT_IND, AP.EXTRA_STATUS EXTRA_STATUS, AP.EXTRA_PASSPORT_NO EXTRA_PASSPORT_NO, AP.REQUEST_APP_DETS_IND REQUEST_APPL_DETAILS_IND, AP.REQUEST_COPY_APP_FRM_IND REQUEST_COPY_APPL_FORM_IND, AP.CEF_NO CLEARING_ENTRY_FORM_NUMBER, AP.SYSTEM_CODE UCAS_SYSTEM_CODE, AP.GCSE_ENG GTTR_GCSE_ENGLISH_FLAG, AP.GCSE_MATH GTTR_GCSE_MATHS_FLAG, AP.DEGREE_SUBJECT GTTR_DEGREE_SUBJECT, AP.DEGREE_STATUS GTTR_DEGREE_STATUS, AP.DEGREE_CLASS GTTR_DEGREE_CLASS, AP.GCSE_SCI GTTR_GCSE_SCIENCE_FLAG, AP.WELSHSPEAKER SWAS_WELSHSPEAKER, AP.NI_NUMBER NMAS_NATIONAL_INSURANCE_NUMBER, AP.EARLIEST_START NMAS_EARLIEST_START, AP.NEAR_INST NMAS_NEAREST_INSTITUTION, AP.PREF_REG NMAS_PREFRRED_REGION, AP.QUAL_ENG NMAS_QUAL_ENGLISH_FLAG, AP.QUAL_MATH NMAS_QUAL_MATHS_FLAG, AP.QUAL_SCI NMAS_QUAL_SCIENCE_FLAG, AP.MAIN_QUAL NMAS_MAIN_QUALIFICATION, AP.QUAL_5 NMAS_QUAL_5_FLAG, AP.FUTURE_SERV SWAS_FUTURE_SERVICE, AP.FUTURE_SET SWAS_FUTURE_SETTING, AP.PRESENT_SERV SWAS_PRESENT_SERVICE, AP.PRESENT_SET SWAS_PRESENT_SETTING, AP.CURR_EMPLOYMENT SWAS_CURRENT_EMPLOYMENT, AP.EDU_QUALIFICATION SWAS_EDUCATION_QUALIFICATION, HZ.PARTY_NUMBER OSS_PERSON_NUMBER, HZ.PARTY_NAME OSS_PERSON_NAME, DE.NAME UCAS_SYSTEM_NAME, DE.DESCRIPTION UCAS_SYSTEM_DESCRIPTION, CO.SCHOOL_NAME SCHOOL_NAME, RE.DOM_TEXT DOMICILE_CODE_DESCRIPTION, DECODE (AP.RESIDENTIAL_CATEGORY, NULL, TO_CHAR(NULL),(SELECT CODE_TEXT FROM IGS_UC_REF_CODES WHERE CODE = AP.RESIDENTIAL_CATEGORY AND CODE_TYPE ='RC')) RESIDENTIAL_CATEGORY_NAME, DECODE (AP.DEGREE_SUBJECT, NULL, TO_CHAR(NULL),(SELECT CODE_TEXT FROM IGS_UC_REF_CODES WHERE CODE = AP.DEGREE_SUBJECT AND CODE_TYPE = 'DS')) GTTR_DEGREE_SUBJECT_DESC, AP.CREATED_BY CREATED_BY, AP.CREATION_DATE CREATION_DATE, AP.LAST_UPDATED_BY LAST_UPDATED_BY, AP.LAST_UPDATE_DATE LAST_UPDATE_DATE FROM IGS_UC_APPLICANTS AP, IGS_UC_DEFAULTS DE, HZ_PARTIES HZ, IGS_UC_COM_SCH CO, IGS_UC_REF_APR RE WHERE AP.SYSTEM_CODE = DE.SYSTEM_CODE AND AP.OSS_PERSON_ID = HZ.PARTY_ID (+) AND AP.SCHOOL = CO.SCHOOL (+) AND AP.DOMICILE_APR = RE.DOM (+) WITH READ ONLY

View Text - HTML Formatted

SELECT AP.APP_ID APPLICANT_ID
, AP.PERSONAL_ID PERSONAL_ID
, AP.APP_NO UCAS_APPLICANT_NUMBER
, AP.CHECK_DIGIT UCAS_APPLICANT_CHECK_DIGIT
, AP.ENQUIRY_NO ENQUIRY_NO
, AP.OSS_PERSON_ID OSS_PERSON_ID
, AP.APPLICATION_SOURCE APPLICATION_SOURCE
, AP.NAME_CHANGE_DATE UCAS_NAME_CHANGE_DATE
, AP.STUDENT_SUPPORT STUDENT_SUPPORT_ARRANGEMENT
, AP.ADDRESS_AREA ADDRESS_AREA
, AP.APPLICATION_DATE APPLICATION_DATE
, AP.APPLICATION_SENT_DATE APPLICATION_SENT_DATE
, AP.APPLICATION_SENT_RUN APPLICATION_SENT_RUN
, AP.FEE_PAYER_CODE FEE_PAYER_CODE
, AP.FEE_TEXT FEE_TEXT
, AP.DOMICILE_APR DOMICILE_CODE
, AP.CODE_CHANGED_DATE CODE_CHANGED_DATE
, AP.SCHOOL SCHOOL
, AP.WITHDRAWN WITHDRAWN
, AP.WITHDRAWN_DATE WITHDRAWN_DATE
, AP.REL_TO_CLEAR_REASON RELEASE_TO_CLEARING_REASON
, AP.ROUTE_B ROUTE_B_FLAG
, AP.EXAM_CHANGE_DATE EXAM_CHANGE_DATE
, AP.WINTER QUAL_NUM_WINTER_A_LEVELS
, AP.PREVIOUS QUAL_NUM_PREVIOUS_QUALS
, AP.BTEC QUAL_BTEC_FLAG
, AP.ILC QUAL_ILC_FLAG
, AP.AILC QUAL_AILC_FLAG
, AP.IB QUAL_IB_FLAG
, AP.MANUAL MANUAL_QUAL_CHECK
, AP.REG_NUM BTEC_REGISTRATION_NUMBER
, AP.OEQ OTHER_EXAMINATION_QUAL_FLAG
, AP.EAS ELECTRONIC_APPL_SYSTEM_FLAG
, AP.ROA RECORD_OF_ACHEIVEMENT_FLAG
, AP.STATUS APPLICANT_STATUS
, AP.FIRM_NOW FIRM_CHOICE_NUMBER
, AP.FIRM_REPLY FIRM_REPLY
, AP.INSURANCE_REPLY INSURANCE_REPLY
, AP.CONF_HIST_FIRM_REPLY CONFIRMATION_HIST_FIRM_REPLY
, AP.CONF_HIST_INS_REPLY CONFIRMATION_HIST_INSU_REPLY
, AP.RESIDENTIAL_CATEGORY RESIDENTIAL_CATEGORY
, AP.PERSONAL_STATEMENT PERSONAL_STATEMENT
, AP.MATCH_PREV MATCH_PREVIOUS_FLAG
, AP.MATCH_PREV_DATE MATCH_PREVIOUS_DATE
, AP.MATCH_WINTER MATCH_WINTER_FLAG
, AP.MATCH_SUMMER MATCH_SUMMER_FLAG
, AP.IB_DATE IB_MOVED_DATE
, AP.ILC_DATE ILC_MOVED_DATE
, AP.AILC_DATE AILC_MOVED_DATE
, AP.GCSEQA_DATE GCSEQA_MOVED_DATE
, AP.UK_ENTRY_DATE UK_ENTRY_DATE
, AP.PREV_SURNAME PREVIOUS_SURNAME
, AP.CRIMINAL_CONVICTIONS CRIMINAL_CONVICTIONS_FLAG
, AP.SENT_TO_HESA SENT_TO_HESA_FLAG
, AP.BATCH_IDENTIFIER BATCH_IDENTIFIER
, AP.GCE QUAL_NUM_GCE
, AP.VCE QUAL_NUM_VCE
, AP.SQA QUAL_NUM_SQA
, AP.PREVIOUSAS QUAL_NUM_PREVIOUS_AS
, AP.KEYSKILLS KEYSKILLS_FLAG
, AP.VOCATIONAL VOCATIONAL_FLAG
, AP.SCN SCOTTISH_CANDIDATE_NUMBER
, AP.PREVOEQ PREVIOUS_OTHER_EXAM_QUAL_FLAG
, AP.CHOICES_TRANSPARENT_IND CHOICES_TRANSPARENT_IND
, AP.EXTRA_STATUS EXTRA_STATUS
, AP.EXTRA_PASSPORT_NO EXTRA_PASSPORT_NO
, AP.REQUEST_APP_DETS_IND REQUEST_APPL_DETAILS_IND
, AP.REQUEST_COPY_APP_FRM_IND REQUEST_COPY_APPL_FORM_IND
, AP.CEF_NO CLEARING_ENTRY_FORM_NUMBER
, AP.SYSTEM_CODE UCAS_SYSTEM_CODE
, AP.GCSE_ENG GTTR_GCSE_ENGLISH_FLAG
, AP.GCSE_MATH GTTR_GCSE_MATHS_FLAG
, AP.DEGREE_SUBJECT GTTR_DEGREE_SUBJECT
, AP.DEGREE_STATUS GTTR_DEGREE_STATUS
, AP.DEGREE_CLASS GTTR_DEGREE_CLASS
, AP.GCSE_SCI GTTR_GCSE_SCIENCE_FLAG
, AP.WELSHSPEAKER SWAS_WELSHSPEAKER
, AP.NI_NUMBER NMAS_NATIONAL_INSURANCE_NUMBER
, AP.EARLIEST_START NMAS_EARLIEST_START
, AP.NEAR_INST NMAS_NEAREST_INSTITUTION
, AP.PREF_REG NMAS_PREFRRED_REGION
, AP.QUAL_ENG NMAS_QUAL_ENGLISH_FLAG
, AP.QUAL_MATH NMAS_QUAL_MATHS_FLAG
, AP.QUAL_SCI NMAS_QUAL_SCIENCE_FLAG
, AP.MAIN_QUAL NMAS_MAIN_QUALIFICATION
, AP.QUAL_5 NMAS_QUAL_5_FLAG
, AP.FUTURE_SERV SWAS_FUTURE_SERVICE
, AP.FUTURE_SET SWAS_FUTURE_SETTING
, AP.PRESENT_SERV SWAS_PRESENT_SERVICE
, AP.PRESENT_SET SWAS_PRESENT_SETTING
, AP.CURR_EMPLOYMENT SWAS_CURRENT_EMPLOYMENT
, AP.EDU_QUALIFICATION SWAS_EDUCATION_QUALIFICATION
, HZ.PARTY_NUMBER OSS_PERSON_NUMBER
, HZ.PARTY_NAME OSS_PERSON_NAME
, DE.NAME UCAS_SYSTEM_NAME
, DE.DESCRIPTION UCAS_SYSTEM_DESCRIPTION
, CO.SCHOOL_NAME SCHOOL_NAME
, RE.DOM_TEXT DOMICILE_CODE_DESCRIPTION
, DECODE (AP.RESIDENTIAL_CATEGORY
, NULL
, TO_CHAR(NULL)
, (SELECT CODE_TEXT
FROM IGS_UC_REF_CODES
WHERE CODE = AP.RESIDENTIAL_CATEGORY
AND CODE_TYPE ='RC')) RESIDENTIAL_CATEGORY_NAME
, DECODE (AP.DEGREE_SUBJECT
, NULL
, TO_CHAR(NULL)
, (SELECT CODE_TEXT
FROM IGS_UC_REF_CODES
WHERE CODE = AP.DEGREE_SUBJECT
AND CODE_TYPE = 'DS')) GTTR_DEGREE_SUBJECT_DESC
, AP.CREATED_BY CREATED_BY
, AP.CREATION_DATE CREATION_DATE
, AP.LAST_UPDATED_BY LAST_UPDATED_BY
, AP.LAST_UPDATE_DATE LAST_UPDATE_DATE
FROM IGS_UC_APPLICANTS AP
, IGS_UC_DEFAULTS DE
, HZ_PARTIES HZ
, IGS_UC_COM_SCH CO
, IGS_UC_REF_APR RE
WHERE AP.SYSTEM_CODE = DE.SYSTEM_CODE
AND AP.OSS_PERSON_ID = HZ.PARTY_ID (+)
AND AP.SCHOOL = CO.SCHOOL (+)
AND AP.DOMICILE_APR = RE.DOM (+) WITH READ ONLY