VIEW: APPS.IGSFV_ST_PROG_ATT_HESA_DETAILS

Source

View Text - Preformatted

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

View Text - HTML Formatted

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