VIEW: APPS.IGSFV_PROGRAM_UK_STATISTICS

Source

View Text - Preformatted

SELECT prg.HESA_ST_PROG_ID HESA_STATS_PROGRAM_ID, prg.COURSE_CD PROGRAM_CODE, ps.TITLE PROGRAM_TITLE, ps.SHORT_TITLE PROGRAM_SHORT_TITLE, ps.ABBREVIATION PROGRAM_ABBREVIATION, prg.VERSION_NUMBER PROGRAM_VERSION_NUMBER, prg.FE_PROGRAM_MARKER FE_PROGRAM_MARKER, (SELECT VALUE_DESCRIPTION FROM IGS_HE_CODE_VALUES WHERE CODE_TYPE = 'OSS_FEPRMK' AND VALUE = prg.FE_PROGRAM_MARKER ) FE_PROGRAM_MARKER_DESC, prg.RETURN_TYPE HESA_RETURN_TYPE, '_LA:prg.RETURN_TYPE:IGS_LOOKUP_VALUES:IGS_HE_RED_RTN:MEANING' "_LA:HESA_RETURN_TYPE_DESC" , prg.LOCATION_OF_STUDY LOCATION_OF_STUDY, ( SELECT VALUE_DESCRIPTION FROM IGS_HE_CODE_VALUES WHERE CODE_TYPE = 'OSS_LOCSDY' AND VALUE = prg.LOCATION_OF_STUDY ) LOCATION_OF_STUDY_DESC, prg.CREDIT_TRANSFER_SCHEME CREDIT_TRANSFER_SCHEME, ( SELECT VALUE_DESCRIPTION FROM IGS_HE_CODE_VALUES WHERE CODE_TYPE = 'OSS_CRDTSCM' AND VALUE = prg.CREDIT_TRANSFER_SCHEME ) CREDIT_TRANSFER_SCHEME_DESC, prg.LEVEL_APPLICABLE_TO_FUNDING LEVEL_APP_TO_FUNDING_COUNCIL, ( SELECT VALUE_DESCRIPTION FROM IGS_HE_CODE_VALUES WHERE CODE_TYPE = 'OSS_FUNDLEV' AND VALUE = prg.LEVEL_APPLICABLE_TO_FUNDING ) LEVEL_APP_FUNDING_COUNCIL_DESC, prg.FUNDABILITY FUNDABILITY_CODE, ( SELECT VALUE_DESCRIPTION FROM IGS_HE_CODE_VALUES WHERE CODE_TYPE = 'OSS_FUND_CODE' AND VALUE = prg.FUNDABILITY ) FUNDABILITY_DESC, prg.FEE_BAND FEE_BAND, ( SELECT VALUE_DESCRIPTION FROM IGS_HE_CODE_VALUES WHERE CODE_TYPE = 'OSS_FEEBAND' AND VALUE = prg.FEE_BAND ) FEE_BAND_DESC, prg.NHS_FUNDING_SOURCE SOURCE_OF_DEPT_HEALTH_FUNDING, ( SELECT VALUE_DESCRIPTION FROM IGS_HE_CODE_VALUES WHERE CODE_TYPE = 'OSS_NHS_FUSRC' AND VALUE = prg.NHS_FUNDING_SOURCE ) SOURCE_DEPT_HEALTH_FUND_DESC, prg.FRANCHISING_ACTIVITY FRANCHISING_ACTIVITY, ( SELECT VALUE_DESCRIPTION FROM IGS_HE_CODE_VALUES WHERE CODE_TYPE = 'OSS_FRAN_ACT' AND VALUE = prg.FRANCHISING_ACTIVITY ) FRANCHISING_ACTIVITY_DESC, prg.OTHER_INST_PROV_TEACHING1 OTHER_INST_PROV_TEACHING1, ( SELECT HP.PARTY_NAME FROM HZ_PARTIES HP, IGS_PE_HZ_PARTIES IHP WHERE IHP.OSS_ORG_UNIT_CD = prg.OTHER_INST_PROV_TEACHING1 AND IHP.PARTY_ID = HP.PARTY_ID) OTHER_INST_PROV_TEACHING1_DESC, prg.OTHER_INST_PROV_TEACHING2 OTHER_INST_PROV_TEACHING2, ( SELECT HP.PARTY_NAME FROM HZ_PARTIES HP, IGS_PE_HZ_PARTIES IHP WHERE IHP.OSS_ORG_UNIT_CD = prg.OTHER_INST_PROV_TEACHING2 AND IHP.PARTY_ID = HP.PARTY_ID) OTHER_INST_PROV_TEACHING2_DESC, prg.PROP_NOT_TAUGHT PROP_NOT_TAUGHT_BY_THIS_INST, prg.PROP_TEACHING_IN_WELSH PROP_TAUGHT_IN_WELSH, prg.PROGRAM_CALC USE_PROGRAM_LEVEL_FOR_CALC, prg.FTE_INTENSITY FTE_INTENSITY, prg.TEACH_PERIOD_START_DT TEACHING_PERIOD_START_DATE, prg.TEACH_PERIOD_END_DT TEACHING_PERIOD_END_DATE, prg.TEACHER_TRAIN_PROG_ID TEACHER_TRAINING_PROGRAM_ID, ( SELECT VALUE_DESCRIPTION FROM IGS_HE_CODE_VALUES WHERE CODE_TYPE = 'OSS_TTCID' AND VALUE = prg.TEACHER_TRAIN_PROG_ID ) TEACHER_TRAINING_PROGRAM_DESC, prg.ITT_PHASE ITT_PHASE_SCOPE, ( SELECT VALUE_DESCRIPTION FROM IGS_HE_CODE_VALUES WHERE CODE_TYPE = 'OSS_ITTPHSC' AND VALUE = prg.ITT_PHASE ) ITT_PHASE_SCOPE_DESC, prg.BILINGUAL_ITT_MARKER BILINGUAL_ITT_MARKER, ( SELECT VALUE_DESCRIPTION FROM IGS_HE_CODE_VALUES WHERE CODE_TYPE = 'OSS_BITTM' AND VALUE = prg.BILINGUAL_ITT_MARKER) BILINGUAL_ITT_MARKER_DESC, prg.TEACHING_QUAL_SOUGHT_SECTOR TEACH_QUAL_SOUGHT_SECTOR, ( SELECT VALUE_DESCRIPTION FROM IGS_HE_CODE_VALUES WHERE CODE_TYPE = 'OSS_TQSEC' AND VALUE = prg.TEACHING_QUAL_SOUGHT_SECTOR ) TEACH_QUAL_SOUGHT_SECTOR_DESC, prg.TEACHING_QUAL_SOUGHT_SUBJ1 TEACH_QUAL_SOUGHT_SUBJ1, ( SELECT VALUE_DESCRIPTION FROM IGS_HE_CODE_VALUES WHERE CODE_TYPE = 'OSS_TQSUB123' AND VALUE = prg.TEACHING_QUAL_SOUGHT_SUBJ1 ) TEACH_QUAL_SOUGHT_SUBJ1_DESC, prg.TEACHING_QUAL_SOUGHT_SUBJ2 TEACH_QUAL_SOUGHT_SUBJ2, ( SELECT VALUE_DESCRIPTION FROM IGS_HE_CODE_VALUES WHERE CODE_TYPE = 'OSS_TQSUB123' AND VALUE = prg.TEACHING_QUAL_SOUGHT_SUBJ2 ) TEACH_QUAL_SOUGHT_SUBJ2_DESC, prg.TEACHING_QUAL_SOUGHT_SUBJ3 TEACH_QUAL_SOUGHT_SUBJ3, ( SELECT VALUE_DESCRIPTION FROM IGS_HE_CODE_VALUES WHERE CODE_TYPE = 'OSS_TQSUB123' AND VALUE = prg.TEACHING_QUAL_SOUGHT_SUBJ3 ) TEACH_QUAL_SOUGHT_SUBJ3_DESC, prg.IMPLIED_FUND_RATE IMPLIED_FUNDING_RATE, prg.GOV_INITIATIVES_CD GOVERNMENT_INITIATIVES_CODE, (SELECT VALUE_DESCRIPTION FROM IGS_HE_CODE_VALUES WHERE CODE_TYPE = 'OSS_GOVINIT' AND VALUE = prg.GOV_INITIATIVES_CD) GOVERNMENT_INITIATIVES_DESC, prg.UNITS_FOR_QUAL UNITS_TO_ACHIEVE_QUALIFICATION, prg.DISADV_UPLIFT_ELIG_CD DISADVANTAGE_UPLIFT_ELIG_CODE, (SELECT VALUE_DESCRIPTION FROM IGS_HE_CODE_VALUES WHERE CODE_TYPE = 'OSS_ELIDISUP' AND VALUE = prg.DISADV_UPLIFT_ELIG_CD) DISADVANTAGE_UPLIFT_ELIG_DESC, prg.FRANCH_PARTNER_CD FRANCHISE_PARTNER_CODE, (SELECT VALUE_DESCRIPTION FROM IGS_HE_CODE_VALUES WHERE CODE_TYPE = 'OSS_FRANPART' AND VALUE = prg.FRANCH_PARTNER_CD) FRANCHISE_PARTNER_DESC, prg.FRANCH_OUT_ARR_CD FRANCHISED_OUT_ARRANGMNTS_CODE, (SELECT VALUE_DESCRIPTION FROM IGS_HE_CODE_VALUES WHERE CODE_TYPE = 'OSS_FROUTARR' AND VALUE = prg.FRANCH_OUT_ARR_CD) FRANCHISED_OUT_ARRANGMNTS_DESC, prg.EXCLUDE_FLAG EXCLUDE_FLAG, prg.CREATED_BY CREATED_BY, prg.CREATION_DATE CREATION_DATE, prg.LAST_UPDATED_BY LAST_UPDATED_BY, prg.LAST_UPDATE_DATE LAST_UPDATE_DATE FROM IGS_HE_ST_PROG_ALL prg, IGS_PS_VER_ALL ps WHERE prg.COURSE_CD = ps.COURSE_CD AND prg.VERSION_NUMBER = ps.VERSION_NUMBER WITH READ ONLY

View Text - HTML Formatted

SELECT PRG.HESA_ST_PROG_ID HESA_STATS_PROGRAM_ID
, PRG.COURSE_CD PROGRAM_CODE
, PS.TITLE PROGRAM_TITLE
, PS.SHORT_TITLE PROGRAM_SHORT_TITLE
, PS.ABBREVIATION PROGRAM_ABBREVIATION
, PRG.VERSION_NUMBER PROGRAM_VERSION_NUMBER
, PRG.FE_PROGRAM_MARKER FE_PROGRAM_MARKER
, (SELECT VALUE_DESCRIPTION
FROM IGS_HE_CODE_VALUES
WHERE CODE_TYPE = 'OSS_FEPRMK'
AND VALUE = PRG.FE_PROGRAM_MARKER ) FE_PROGRAM_MARKER_DESC
, PRG.RETURN_TYPE HESA_RETURN_TYPE
, '_LA:PRG.RETURN_TYPE:IGS_LOOKUP_VALUES:IGS_HE_RED_RTN:MEANING' "_LA:HESA_RETURN_TYPE_DESC"
, PRG.LOCATION_OF_STUDY LOCATION_OF_STUDY
, ( SELECT VALUE_DESCRIPTION
FROM IGS_HE_CODE_VALUES
WHERE CODE_TYPE = 'OSS_LOCSDY'
AND VALUE = PRG.LOCATION_OF_STUDY ) LOCATION_OF_STUDY_DESC
, PRG.CREDIT_TRANSFER_SCHEME CREDIT_TRANSFER_SCHEME
, ( SELECT VALUE_DESCRIPTION
FROM IGS_HE_CODE_VALUES
WHERE CODE_TYPE = 'OSS_CRDTSCM'
AND VALUE = PRG.CREDIT_TRANSFER_SCHEME ) CREDIT_TRANSFER_SCHEME_DESC
, PRG.LEVEL_APPLICABLE_TO_FUNDING LEVEL_APP_TO_FUNDING_COUNCIL
, ( SELECT VALUE_DESCRIPTION
FROM IGS_HE_CODE_VALUES
WHERE CODE_TYPE = 'OSS_FUNDLEV'
AND VALUE = PRG.LEVEL_APPLICABLE_TO_FUNDING ) LEVEL_APP_FUNDING_COUNCIL_DESC
, PRG.FUNDABILITY FUNDABILITY_CODE
, ( SELECT VALUE_DESCRIPTION
FROM IGS_HE_CODE_VALUES
WHERE CODE_TYPE = 'OSS_FUND_CODE'
AND VALUE = PRG.FUNDABILITY ) FUNDABILITY_DESC
, PRG.FEE_BAND FEE_BAND
, ( SELECT VALUE_DESCRIPTION
FROM IGS_HE_CODE_VALUES
WHERE CODE_TYPE = 'OSS_FEEBAND'
AND VALUE = PRG.FEE_BAND ) FEE_BAND_DESC
, PRG.NHS_FUNDING_SOURCE SOURCE_OF_DEPT_HEALTH_FUNDING
, ( SELECT VALUE_DESCRIPTION
FROM IGS_HE_CODE_VALUES
WHERE CODE_TYPE = 'OSS_NHS_FUSRC'
AND VALUE = PRG.NHS_FUNDING_SOURCE ) SOURCE_DEPT_HEALTH_FUND_DESC
, PRG.FRANCHISING_ACTIVITY FRANCHISING_ACTIVITY
, ( SELECT VALUE_DESCRIPTION
FROM IGS_HE_CODE_VALUES
WHERE CODE_TYPE = 'OSS_FRAN_ACT'
AND VALUE = PRG.FRANCHISING_ACTIVITY ) FRANCHISING_ACTIVITY_DESC
, PRG.OTHER_INST_PROV_TEACHING1 OTHER_INST_PROV_TEACHING1
, ( SELECT HP.PARTY_NAME
FROM HZ_PARTIES HP
, IGS_PE_HZ_PARTIES IHP
WHERE IHP.OSS_ORG_UNIT_CD = PRG.OTHER_INST_PROV_TEACHING1
AND IHP.PARTY_ID = HP.PARTY_ID) OTHER_INST_PROV_TEACHING1_DESC
, PRG.OTHER_INST_PROV_TEACHING2 OTHER_INST_PROV_TEACHING2
, ( SELECT HP.PARTY_NAME
FROM HZ_PARTIES HP
, IGS_PE_HZ_PARTIES IHP
WHERE IHP.OSS_ORG_UNIT_CD = PRG.OTHER_INST_PROV_TEACHING2
AND IHP.PARTY_ID = HP.PARTY_ID) OTHER_INST_PROV_TEACHING2_DESC
, PRG.PROP_NOT_TAUGHT PROP_NOT_TAUGHT_BY_THIS_INST
, PRG.PROP_TEACHING_IN_WELSH PROP_TAUGHT_IN_WELSH
, PRG.PROGRAM_CALC USE_PROGRAM_LEVEL_FOR_CALC
, PRG.FTE_INTENSITY FTE_INTENSITY
, PRG.TEACH_PERIOD_START_DT TEACHING_PERIOD_START_DATE
, PRG.TEACH_PERIOD_END_DT TEACHING_PERIOD_END_DATE
, PRG.TEACHER_TRAIN_PROG_ID TEACHER_TRAINING_PROGRAM_ID
, ( SELECT VALUE_DESCRIPTION
FROM IGS_HE_CODE_VALUES
WHERE CODE_TYPE = 'OSS_TTCID'
AND VALUE = PRG.TEACHER_TRAIN_PROG_ID ) TEACHER_TRAINING_PROGRAM_DESC
, PRG.ITT_PHASE ITT_PHASE_SCOPE
, ( SELECT VALUE_DESCRIPTION
FROM IGS_HE_CODE_VALUES
WHERE CODE_TYPE = 'OSS_ITTPHSC'
AND VALUE = PRG.ITT_PHASE ) ITT_PHASE_SCOPE_DESC
, PRG.BILINGUAL_ITT_MARKER BILINGUAL_ITT_MARKER
, ( SELECT VALUE_DESCRIPTION
FROM IGS_HE_CODE_VALUES
WHERE CODE_TYPE = 'OSS_BITTM'
AND VALUE = PRG.BILINGUAL_ITT_MARKER) BILINGUAL_ITT_MARKER_DESC
, PRG.TEACHING_QUAL_SOUGHT_SECTOR TEACH_QUAL_SOUGHT_SECTOR
, ( SELECT VALUE_DESCRIPTION
FROM IGS_HE_CODE_VALUES
WHERE CODE_TYPE = 'OSS_TQSEC'
AND VALUE = PRG.TEACHING_QUAL_SOUGHT_SECTOR ) TEACH_QUAL_SOUGHT_SECTOR_DESC
, PRG.TEACHING_QUAL_SOUGHT_SUBJ1 TEACH_QUAL_SOUGHT_SUBJ1
, ( SELECT VALUE_DESCRIPTION
FROM IGS_HE_CODE_VALUES
WHERE CODE_TYPE = 'OSS_TQSUB123'
AND VALUE = PRG.TEACHING_QUAL_SOUGHT_SUBJ1 ) TEACH_QUAL_SOUGHT_SUBJ1_DESC
, PRG.TEACHING_QUAL_SOUGHT_SUBJ2 TEACH_QUAL_SOUGHT_SUBJ2
, ( SELECT VALUE_DESCRIPTION
FROM IGS_HE_CODE_VALUES
WHERE CODE_TYPE = 'OSS_TQSUB123'
AND VALUE = PRG.TEACHING_QUAL_SOUGHT_SUBJ2 ) TEACH_QUAL_SOUGHT_SUBJ2_DESC
, PRG.TEACHING_QUAL_SOUGHT_SUBJ3 TEACH_QUAL_SOUGHT_SUBJ3
, ( SELECT VALUE_DESCRIPTION
FROM IGS_HE_CODE_VALUES
WHERE CODE_TYPE = 'OSS_TQSUB123'
AND VALUE = PRG.TEACHING_QUAL_SOUGHT_SUBJ3 ) TEACH_QUAL_SOUGHT_SUBJ3_DESC
, PRG.IMPLIED_FUND_RATE IMPLIED_FUNDING_RATE
, PRG.GOV_INITIATIVES_CD GOVERNMENT_INITIATIVES_CODE
, (SELECT VALUE_DESCRIPTION
FROM IGS_HE_CODE_VALUES
WHERE CODE_TYPE = 'OSS_GOVINIT'
AND VALUE = PRG.GOV_INITIATIVES_CD) GOVERNMENT_INITIATIVES_DESC
, PRG.UNITS_FOR_QUAL UNITS_TO_ACHIEVE_QUALIFICATION
, PRG.DISADV_UPLIFT_ELIG_CD DISADVANTAGE_UPLIFT_ELIG_CODE
, (SELECT VALUE_DESCRIPTION
FROM IGS_HE_CODE_VALUES
WHERE CODE_TYPE = 'OSS_ELIDISUP'
AND VALUE = PRG.DISADV_UPLIFT_ELIG_CD) DISADVANTAGE_UPLIFT_ELIG_DESC
, PRG.FRANCH_PARTNER_CD FRANCHISE_PARTNER_CODE
, (SELECT VALUE_DESCRIPTION
FROM IGS_HE_CODE_VALUES
WHERE CODE_TYPE = 'OSS_FRANPART'
AND VALUE = PRG.FRANCH_PARTNER_CD) FRANCHISE_PARTNER_DESC
, PRG.FRANCH_OUT_ARR_CD FRANCHISED_OUT_ARRANGMNTS_CODE
, (SELECT VALUE_DESCRIPTION
FROM IGS_HE_CODE_VALUES
WHERE CODE_TYPE = 'OSS_FROUTARR'
AND VALUE = PRG.FRANCH_OUT_ARR_CD) FRANCHISED_OUT_ARRANGMNTS_DESC
, PRG.EXCLUDE_FLAG EXCLUDE_FLAG
, PRG.CREATED_BY CREATED_BY
, PRG.CREATION_DATE CREATION_DATE
, PRG.LAST_UPDATED_BY LAST_UPDATED_BY
, PRG.LAST_UPDATE_DATE LAST_UPDATE_DATE
FROM IGS_HE_ST_PROG_ALL PRG
, IGS_PS_VER_ALL PS
WHERE PRG.COURSE_CD = PS.COURSE_CD
AND PRG.VERSION_NUMBER = PS.VERSION_NUMBER WITH READ ONLY