View: IGSFV_UC_APPLICANTS

View Text

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

Columns