View: IGSFV_ST_PROG_ATT_HESA_DETAILS

View Text

SELECT SPA.HESA_ST_SPA_ID HESA_STATS_SPA_ID
, SPA.PERSON_ID PERSON_ID
, PER.PARTY_NUMBER PERSON_NUMBER
, PER.PERSON_LAST_NAME PERSON_SURNAME
, PER.PERSON_FIRST_NAME PERSON_GIVEN_NAMES
, PERP.PERSON_NAME PERSON_FULL_NAME
, SPA.COURSE_CD PROGRAM_CODE
, PS.TITLE PROGRAM_TITLE
, PS.SHORT_TITLE PROGRAM_SHORT_TITLE
, PS.ABBREVIATION PROGRAM_ABBREVIATION
, SPA.VERSION_NUMBER PROGRAM_VERSION_NUMBER
, (SELECT API_PERSON_ID
FROM IGS_PE_ALT_PERS_ID
WHERE PERSON_ID_TYPE = 'HUSID'
AND PE_PERSON_ID = SPA.PERSON_ID
AND TRUNC(SYSDATE) BETWEEN START_DT
AND NVL(END_DT
, TRUNC(SYSDATE))
AND (END_DT IS NULL OR START_DT <> END_DT)) HUSID
, SPA.STUDENT_INST_NUMBER STUDENT_INSTANCE_NUMBER
, SPA.RETURN_TYPE HESA_RETURN_TYPE
, '_LA:SPA.RETURN_TYPE:IGS_LOOKUP_VALUES:IGS_HE_RED_RTN:MEANING' "_LA:HESA_RETURN_TYPE_DESC"
, SPA.FE_STUDENT_MARKER FE_STUDENT_MARKER
, (SELECT VALUE_DESCRIPTION
FROM IGS_HE_CODE_VALUES
WHERE CODE_TYPE = 'OSS_FESTUMK'
AND VALUE = SPA.FE_STUDENT_MARKER) FE_STUDENT_MARKER_DESC
, SPA.HIGHEST_QUAL_ON_ENTRY HIGHEST_QUAL_ON_ENTRY
, (SELECT FULL_GRADE_NAME
FROM IGS_AS_GRD_SCH_GRADE GSG
WHERE GSG.GRADE = SPA.HIGHEST_QUAL_ON_ENTRY
AND GSG.GRADING_SCHEMA_CD IN (SELECT VALUE
FROM IGS_HE_CODE_VALUES
WHERE CODE_TYPE = 'HESA_HIGH_QUAL_ON_ENT')
AND GSG.VERSION_NUMBER = (SELECT MAX(VERSION_NUMBER)
FROM IGS_AS_GRD_SCH_GRADE GSG2
WHERE GSG2.GRADING_SCHEMA_CD = GSG.GRADING_SCHEMA_CD
AND GSG2.GRADE = GSG.GRADE)) HIGHEST_QUAL_ON_ENTRY_DESC
, SPA.DATE_QUAL_ON_ENTRY_CALC UCAS_TARIFF_CALC_LAST_RUN_DATE
, SPA.TOTAL_UCAS_TARIFF TOTAL_UCAS_TARIFF
, SPA.COMMENCEMENT_DT COMMENCEMENT_DATE
, SPA.SPECIAL_STUDENT SPECIAL_STUDENT_CODE
, (SELECT VALUE_DESCRIPTION
FROM IGS_HE_CODE_VALUES
WHERE CODE_TYPE = 'OSS_SPEC_STUD'
AND VALUE = SPA.SPECIAL_STUDENT) SPECIAL_STUDENT_DESC
, SPA.STUDENT_QUAL_AIM STUDENT_QUAL_AIM_AWD
, (SELECT AWARD_TITLE
FROM IGS_PS_AWD
WHERE AWARD_CD = SPA.STUDENT_QUAL_AIM) STUDENT_QUAL_AIM_AWD_TITLE
, SPA.QUAL_AIM_SUBJ1 QUAL_AIM_SUBJ1
, (SELECT DESCRIPTION
FROM IGS_PS_FLD_OF_STUDY_ALL
WHERE FIELD_OF_STUDY = SPA.QUAL_AIM_SUBJ1) QUAL_AIM_SUBJ1_DESC
, SPA.QUAL_AIM_SUBJ2 QUAL_AIM_SUBJ2
, (SELECT DESCRIPTION
FROM IGS_PS_FLD_OF_STUDY_ALL
WHERE FIELD_OF_STUDY = SPA.QUAL_AIM_SUBJ2) QUAL_AIM_SUBJ2_DESC
, SPA.QUAL_AIM_SUBJ3 QUAL_AIM_SUBJ3
, (SELECT DESCRIPTION
FROM IGS_PS_FLD_OF_STUDY_ALL
WHERE FIELD_OF_STUDY = SPA.QUAL_AIM_SUBJ3) QUAL_AIM_SUBJ3_DESC
, SPA.QUAL_AIM_PROPORTION QUAL_AIM_PROPORTION
, (SELECT VALUE_DESCRIPTION
FROM IGS_HE_CODE_VALUES
WHERE CODE_TYPE = 'OSS_PROPORTION'
AND VALUE = SPA.QUAL_AIM_PROPORTION) QUAL_AIM_PROPORTION_DESC
, SPA.STUDENT_FE_QUAL_AIM STUDENT_FE_QUAL_AIM_AWD
, (SELECT AWARD_TITLE
FROM IGS_PS_AWD
WHERE AWARD_CD = SPA.STUDENT_FE_QUAL_AIM) STUDENT_FE_QUAL_AIM_AWD_TITLE
, SPA.UFI_PLACE UFI_PLACE
, (SELECT VALUE_DESCRIPTION
FROM IGS_HE_CODE_VALUES
WHERE CODE_TYPE = 'OSS_UFI_PLACE'
AND VALUE = SPA.UFI_PLACE) UFI_PLACE_DESC
, SPA.POSTCODE POSTCODE
, SPA.SOCIAL_CLASS_IND SOCIAL_CLASS_CODE
, (SELECT VALUE_DESCRIPTION
FROM IGS_HE_CODE_VALUES
WHERE CODE_TYPE = 'OSS_SOC'
AND VALUE = SPA.SOCIAL_CLASS_IND) SOCIAL_CLASS_DESC
, SPA.OCCCODE PRE_2000_PARENTAL_OCCCODE
, SPA.OCCUPATION_CODE OCCUPATION_CODE
, (SELECT VALUE_DESCRIPTION
FROM IGS_HE_CODE_VALUES
WHERE CODE_TYPE = 'OSS_OCC'
AND VALUE = SPA.OCCUPATION_CODE) OCCUPATION_DESC
, SPA.DOMICILE_CD DOMICILE_CODE
, (SELECT VALUE_DESCRIPTION
FROM IGS_HE_CODE_VALUES
WHERE CODE_TYPE = 'OSS_DOM'
AND VALUE = SPA.DOMICILE_CD) DOMICILE_DESC
, SPA.DESTINATION DESTINATION
, (SELECT VALUE_DESCRIPTION
FROM IGS_HE_CODE_VALUES
WHERE CODE_TYPE = 'OSS_DEST'
AND VALUE = SPA.DESTINATION) DESTINATION_DESC
, SPA.NHS_FUNDING_SOURCE SOURCE_OF_DH_FUNDING
, (SELECT VALUE_DESCRIPTION
FROM IGS_HE_CODE_VALUES
WHERE CODE_TYPE = 'OSS_NHS_FUSRC'
AND VALUE = SPA.NHS_FUNDING_SOURCE) SOURCE_OF_DH_FUNDING_DESC
, SPA.NHS_EMPLOYER NHS_EMPLOYER
, (SELECT VALUE_DESCRIPTION
FROM IGS_HE_CODE_VALUES
WHERE CODE_TYPE = 'OSS_NHS_EMPLOYER'
AND VALUE = SPA.NHS_EMPLOYER) NHS_EMPLOYER_DESC
, SPA.ASSOCIATE_UCAS_NUMBER ASSOCIATE_UCAS_NUMBER
, (SELECT API_PERSON_ID
FROM IGS_PE_ALT_PERS_ID
WHERE PERSON_ID_TYPE = 'UCASID'
AND PE_PERSON_ID = SPA.PERSON_ID
AND TRUNC(SYSDATE) BETWEEN START_DT
AND NVL(END_DT
, TRUNC(SYSDATE))
AND (END_DT IS NULL OR START_DT <> END_DT)) UCAS_NUMBER
, (SELECT API_PERSON_ID
FROM IGS_PE_ALT_PERS_ID
WHERE PERSON_ID_TYPE = 'GTTRID'
AND PE_PERSON_ID = SPA.PERSON_ID
AND TRUNC(SYSDATE)BETWEEN START_DT
AND NVL(END_DT
, TRUNC(SYSDATE))
AND (END_DT IS NULL OR START_DT <> END_DT)) GTTR_NUMBER
, (SELECT API_PERSON_ID
FROM IGS_PE_ALT_PERS_ID
WHERE PERSON_ID_TYPE = 'SWASID'
AND PE_PERSON_ID = SPA.PERSON_ID
AND TRUNC(SYSDATE) BETWEEN START_DT
AND NVL(END_DT
, TRUNC(SYSDATE))
AND (END_DT IS NULL OR START_DT <> END_DT)) SWAS_NUMBER
, (SELECT API_PERSON_ID
FROM IGS_PE_ALT_PERS_ID
WHERE PERSON_ID_TYPE = 'NMASID'
AND PE_PERSON_ID = SPA.PERSON_ID
AND TRUNC(SYSDATE)BETWEEN START_DT
AND NVL(END_DT
, TRUNC(SYSDATE))
AND (END_DT IS NULL OR START_DT <> END_DT)) NMAS_NUMBER
, SPA.ASSOCIATE_SCOTT_CAND ASSOCIATE_SCOTT_CAND_NUMBER
, (SELECT API_PERSON_ID
FROM IGS_PE_ALT_PERS_ID
WHERE PERSON_ID_TYPE = 'UCASREGNO'
AND PE_PERSON_ID = SPA.PERSON_ID
AND TRUNC(SYSDATE) BETWEEN START_DT
AND NVL(END_DT
, TRUNC(SYSDATE))
AND (END_DT IS NULL OR START_DT <> END_DT)) SCOTT_CAND_NUMBER
, SPA.ASSOCIATE_TEACH_REF_NUM ASSOCIATE_TEACHER_REF_NUMBER
, (SELECT API_PERSON_ID
FROM IGS_PE_ALT_PERS_ID
WHERE PERSON_ID_TYPE = 'TEACH REF'
AND PE_PERSON_ID = SPA.PERSON_ID
AND TRUNC(SYSDATE) BETWEEN START_DT
AND NVL(END_DT
, TRUNC(SYSDATE))
AND (END_DT IS NULL OR START_DT <> END_DT)) TEACHER_TRAINING_REF_NUMBER
, SPA.ASSOCIATE_NHS_REG_NUM ASSOCIATE_REG_BODY_REF_NUMBER
, (SELECT API_PERSON_ID
FROM IGS_PE_ALT_PERS_ID
WHERE PERSON_ID_TYPE = 'DH REG REF'
AND PE_PERSON_ID = SPA.PERSON_ID
AND TRUNC(SYSDATE) BETWEEN START_DT
AND NVL(END_DT
, TRUNC(SYSDATE))
AND (END_DT IS NULL OR START_DT <> END_DT)) REG_BODY_REF_NUMBER
, SPA.TEACHER_TRAIN_PROG_ID TEACHER_TRAINING_COURSE_ID
, (SELECT VALUE_DESCRIPTION
FROM IGS_HE_CODE_VALUES
WHERE CODE_TYPE = 'OSS_TTCID'
AND VALUE = SPA.TEACHER_TRAIN_PROG_ID) TEACHER_TRAINING_COURSE_DESC
, SPA.ITT_PHASE ITT_PHASE_SCOPE
, (SELECT VALUE_DESCRIPTION
FROM IGS_HE_CODE_VALUES
WHERE CODE_TYPE = 'OSS_ITTPHSC'
AND VALUE = SPA.ITT_PHASE) ITT_PHASE_SCOPE_DESC
, SPA.BILINGUAL_ITT_MARKER BILINGUAL_ITT_MARKER
, (SELECT VALUE_DESCRIPTION
FROM IGS_HE_CODE_VALUES
WHERE CODE_TYPE = 'OSS_BITTM'
AND VALUE = SPA.BILINGUAL_ITT_MARKER) BILINGUAL_ITT_MARKER_DESC
, SPA.TEACHING_QUAL_GAIN_SECTOR TEACHING_QUAL_GAIN_SECTOR
, (SELECT VALUE_DESCRIPTION
FROM IGS_HE_CODE_VALUES
WHERE CODE_TYPE = 'OSS_TQSEC'
AND VALUE = SPA.TEACHING_QUAL_GAIN_SECTOR) TEACHING_QUAL_GAIN_SECTOR_DESC
, SPA.TEACHING_QUAL_GAIN_SUBJ1 TEACHING_QUAL_GAIN_SUBJ1
, (SELECT VALUE_DESCRIPTION
FROM IGS_HE_CODE_VALUES
WHERE CODE_TYPE = 'OSS_TQSUB123'
AND VALUE = SPA.TEACHING_QUAL_GAIN_SUBJ1) TEACHING_QUAL_GAIN_SUBJ1_DESC
, SPA.TEACHING_QUAL_GAIN_SUBJ2 TEACHING_QUAL_GAIN_SUBJ2
, (SELECT VALUE_DESCRIPTION
FROM IGS_HE_CODE_VALUES
WHERE CODE_TYPE = 'OSS_TQSUB123'
AND VALUE = SPA.TEACHING_QUAL_GAIN_SUBJ2) TEACHING_QUAL_GAIN_SUBJ2_DESC
, SPA.TEACHING_QUAL_GAIN_SUBJ3 TEACHING_QUAL_GAIN_SUBJ3
, (SELECT VALUE_DESCRIPTION
FROM IGS_HE_CODE_VALUES
WHERE CODE_TYPE = 'OSS_TQSUB123'
AND VALUE = SPA.TEACHING_QUAL_GAIN_SUBJ3) TEACHING_QUAL_GAIN_SUBJ3_DESC
, SPA.ITT_PROG_OUTCOME ITT_PROG_OUTCOME
, (SELECT VALUE_DESCRIPTION
FROM IGS_HE_CODE_VALUES
WHERE CODE_TYPE = 'OSS_OUTCOME'
AND VALUE = SPA.ITT_PROG_OUTCOME) ITT_PROG_OUTCOME_DESC
, SPA.HESA_SUBMISSION_NAME HESA_SUBMISSION_NAME
, (SELECT DESCRIPTION
FROM IGS_HE_SUBMSN_HEADER
WHERE SUBMISSION_NAME = SPA.HESA_SUBMISSION_NAME) HESA_SUBMISSION_DESC
, SPA.HESA_RETURN_NAME HESA_RETURN_NAME
, (SELECT USER_RETURN_SUBCLASS
FROM IGS_HE_EXT_RUN_DTLS
WHERE EXTRACT_RUN_ID = SPA.HESA_RETURN_ID) HESA_USER_RETURN_SUBCLASS
, (SELECT URC.DESCRIPTION
FROM IGS_HE_USR_RTN_CLAS URC
, IGS_HE_EXT_RUN_DTLS ERD
WHERE URC.USER_RETURN_SUBCLASS = ERD.USER_RETURN_SUBCLASS
AND EXTRACT_RUN_ID = SPA.HESA_RETURN_ID) HESA_USER_RETURN_SUBCLASS_DESC
, SPA.HESA_RETURN_ID HESA_RETURN_ID
, SPA.DEPENDANTS_CD DEPENDANTS_CODE
, (SELECT VALUE_DESCRIPTION
FROM IGS_HE_CODE_VALUES
WHERE CODE_TYPE = 'OSS_DEPEND'
AND VALUE = SPA.DEPENDANTS_CD) DEPENDANTS_DESC
, SPA.IMPLIED_FUND_RATE IMPLIED_FUNDING_RATE
, SPA.GOV_INITIATIVES_CD GOVERNMENT_INITIATIVES_CODE
, (SELECT VALUE_DESCRIPTION
FROM IGS_HE_CODE_VALUES
WHERE CODE_TYPE = 'OSS_GOVINIT'
AND VALUE = SPA.GOV_INITIATIVES_CD) GOVERNMENT_INITIATIVES_DESC
, SPA.UNITS_FOR_QUAL UNITS_TO_ACHIEVE_QUALIFICATION
, SPA.DISADV_UPLIFT_ELIG_CD DISADVANTAGE_UPLIFT_ELIG_CODE
, (SELECT VALUE_DESCRIPTION
FROM IGS_HE_CODE_VALUES
WHERE CODE_TYPE = 'OSS_ELIDISUP'
AND VALUE = SPA.DISADV_UPLIFT_ELIG_CD) DISADVANTAGE_UPLIFT_ELIG_DESC
, SPA.FRANCH_PARTNER_CD FRANCHISE_PARTNER_CODE
, (SELECT VALUE_DESCRIPTION
FROM IGS_HE_CODE_VALUES
WHERE CODE_TYPE = 'OSS_FRANPART'
AND VALUE = SPA.FRANCH_PARTNER_CD) FRANCHISE_PARTNER_DESC
, SPA.UNITS_COMPLETED UNITS_COMPLETED
, SPA.FRANCH_OUT_ARR_CD FRANCHISED_OUT_ARRANGMNTS_CODE
, (SELECT VALUE_DESCRIPTION
FROM IGS_HE_CODE_VALUES
WHERE CODE_TYPE = 'OSS_FROUTARR'
AND VALUE = SPA.FRANCH_OUT_ARR_CD) FRANCHISED_OUT_ARRANGMNTS_DESC
, SPA.EMPLOYER_ROLE_CD EMPLOYER_ROLE_CODE
, (SELECT VALUE_DESCRIPTION
FROM IGS_HE_CODE_VALUES
WHERE CODE_TYPE = 'OSS_EMPROLE'
AND VALUE = SPA.EMPLOYER_ROLE_CD) EMPLOYER_ROLE_DESC
, SPA.DISADV_UPLIFT_FACTOR DISADVANTAGE_UPLIFT_FACTOR
, SPA.ENH_FUND_ELIG_CD ENHANCED_FUNDING_ELIG_CODE
, (SELECT VALUE_DESCRIPTION
FROM IGS_HE_CODE_VALUES
WHERE CODE_TYPE = 'OSS_ELIGENFD'
AND VALUE = SPA.ENH_FUND_ELIG_CD) ENHANCED_FUNDING_ELIG_DESC
, SPA.EXCLUDE_FLAG EXCLUDE_FLAG
, SPA.CREATION_DATE CREATION_DATE
, SPA.CREATED_BY CREATED_BY
, SPA.LAST_UPDATE_DATE LAST_UPDATE_DATE
, SPA.LAST_UPDATED_BY LAST_UPDATED_BY
FROM IGS_HE_ST_SPA_ALL SPA
, HZ_PARTIES PER
, HZ_PERSON_PROFILES PERP
, IGS_PS_VER_ALL PS
WHERE PER.PARTY_ID = SPA.PERSON_ID
AND PER.PARTY_ID = PERP.PARTY_ID
AND PERP.CONTENT_SOURCE_TYPE = 'USER_ENTERED'
AND SYSDATE BETWEEN PERP.EFFECTIVE_START_DATE
AND NVL(PERP.EFFECTIVE_END_DATE
, SYSDATE)
AND PS.COURSE_CD = SPA.COURSE_CD
AND PS.VERSION_NUMBER = SPA.VERSION_NUMBER WITH READ ONLY

Columns