View: IGSFV_UK_PRG_OFR_OPT_UNIT_SETS

View Text

SELECT POO.HESA_POOUS_ID HESA_POOUS_ID
, POO.COURSE_CD PROGRAM_CODE
, PS.TITLE PROGRAM_TITLE
, PS.SHORT_TITLE PROGRAM_SHORT_TITLE
, PS.ABBREVIATION PROGRAM_ABBREVIATION
, POO.CRV_VERSION_NUMBER PROGRAM_VERSION_NUMBER
, POO.CAL_TYPE CALENDAR_TYPE
, CA.DESCRIPTION CALENDAR_DESC
, CA.ABBREVIATION CALENDAR_ABBREVIATION
, POO.LOCATION_CD LOCATION_CODE
, ( SELECT DESCRIPTION
FROM IGS_AD_LOCATION_ALL
WHERE LOCATION_CD = POO.LOCATION_CD) LOCATION_DESC
, POO.ATTENDANCE_MODE ATTENDANCE_MODE
, ( SELECT DESCRIPTION
FROM IGS_EN_ATD_MODE_ALL
WHERE ATTENDANCE_MODE = POO.ATTENDANCE_MODE ) ATTENDANCE_MODE_DESC
, POO.ATTENDANCE_TYPE ATTENDANCE_TYPE
, ( SELECT DESCRIPTION
FROM IGS_EN_ATD_TYPE_ALL
WHERE ATTENDANCE_TYPE = POO.ATTENDANCE_TYPE ) ATTENDANCE_TYPE_DESC
, POO.UNIT_SET_CD UNIT_SET_CODE
, EN.TITLE UNIT_SET_TITLE
, EN.SHORT_TITLE UNIT_SET_SHORT_TITLE
, EN.ABBREVIATION UNIT_SET_ABBREVIATION
, POO.US_VERSION_NUMBER UNIT_SET_VERSION_NUMBER
, POO.LOCATION_OF_STUDY LOCATION_OF_STUDY
, ( SELECT VALUE_DESCRIPTION
FROM IGS_HE_CODE_VALUES
WHERE CODE_TYPE = 'OSS_LOCSDY'
AND VALUE = POO.LOCATION_OF_STUDY ) LOCATION_OF_STUDY_DESC
, POO.LEVEL_APPLICABLE_TO_FUNDING LEVEL_APP_TO_FUNDING_COUNCIL
, ( SELECT VALUE_DESCRIPTION
FROM IGS_HE_CODE_VALUES
WHERE CODE_TYPE = 'OSS_FUNDLEV'
AND VALUE = POO.LEVEL_APPLICABLE_TO_FUNDING ) LEVEL_APP_FUNDING_COUNCIL_DESC
, POO.FUNDABILITY_CD FUNDABILITY_CODE
, ( SELECT VALUE_DESCRIPTION
FROM IGS_HE_CODE_VALUES
WHERE CODE_TYPE = 'OSS_FUND_CODE'
AND VALUE = POO.FUNDABILITY_CD ) FUNDABILITY_DESC
, POO.FEE_BAND FEE_BAND
, ( SELECT VALUE_DESCRIPTION
FROM IGS_HE_CODE_VALUES
WHERE CODE_TYPE = 'OSS_FEEBAND'
AND VALUE = POO.FEE_BAND ) FEE_BAND_DESC
, POO.FUNDING_SOURCE MAJOR_SOURCE_OF_FUNDING
, ( SELECT DESCRIPTION
FROM IGS_FI_FUND_SRC_ALL
WHERE FUNDING_SOURCE = POO.FUNDING_SOURCE) MAJOR_SOURCE_OF_FUNDING_DESC
, POO.MODE_OF_STUDY MODE_OF_STUDY
, ( SELECT VALUE_DESCRIPTION
FROM IGS_HE_CODE_VALUES
WHERE CODE_TYPE = 'OSS_MODE_TYPE'
AND VALUE = POO.MODE_OF_STUDY ) MODE_OF_STUDY_DESC
, POO.FRANCHISING_ACTIVITY FRANCHISING_ACTIVITY
, ( SELECT VALUE_DESCRIPTION
FROM IGS_HE_CODE_VALUES
WHERE CODE_TYPE = 'OSS_FRAN_ACT'
AND VALUE = POO.FRANCHISING_ACTIVITY ) FRANCHISING_ACTIVITY_DESC
, POO.TYPE_OF_YEAR TYPE_PROGRAM_YEAR
, ( SELECT VALUE_DESCRIPTION
FROM IGS_HE_CODE_VALUES
WHERE CODE_TYPE = 'OSS_TYPEYR'
AND VALUE = POO.TYPE_OF_YEAR ) TYPE_PROGRAM_YEAR_DESC
, POO.LENG_CURRENT_YEAR LENGTH_PROGRAM_YEAR
, POO.GRADING_SCHEMA_CD GRADING_SCHEMA_CODE
, POO.GS_VERSION_NUMBER GRADING_SCHEMA_VERSION
, ( SELECT DESCRIPTION
FROM IGS_AS_GRD_SCHEMA
WHERE GRADING_SCHEMA_CD = POO.GRADING_SCHEMA_CD
AND VERSION_NUMBER = POO.GS_VERSION_NUMBER ) GRADING_SCHEMA_DESC
, POO.CREDIT_VALUE_YOP1 YOP_CREDIT_VALUE_1
, POO.LEVEL_CREDIT1 LEVEL_CREDIT_1
, ( SELECT DESCRIPTION
FROM IGS_PS_UNIT_LEVEL_ALL
WHERE UNIT_LEVEL = POO.LEVEL_CREDIT1 ) LEVEL_CREDIT_1_DESC
, POO.CREDIT_VALUE_YOP2 YOP_CREDIT_VALUE_2
, POO.LEVEL_CREDIT2 LEVEL_CREDIT_2
, ( SELECT DESCRIPTION
FROM IGS_PS_UNIT_LEVEL_ALL
WHERE UNIT_LEVEL = POO.LEVEL_CREDIT2 ) LEVEL_CREDIT_2_DESC
, POO.CREDIT_VALUE_YOP3 YOP_CREDIT_VALUE_3
, POO.LEVEL_CREDIT3 LEVEL_CREDIT_3
, ( SELECT DESCRIPTION
FROM IGS_PS_UNIT_LEVEL_ALL
WHERE UNIT_LEVEL = POO.LEVEL_CREDIT3 ) LEVEL_CREDIT_3_DESC
, POO.CREDIT_VALUE_YOP4 YOP_CREDIT_VALUE_4
, POO.LEVEL_CREDIT4 LEVEL_CREDIT_4
, ( SELECT DESCRIPTION
FROM IGS_PS_UNIT_LEVEL_ALL
WHERE UNIT_LEVEL = POO.LEVEL_CREDIT4 ) LEVEL_CREDIT_4_DESC
, POO.OTHER_INSTIT_TEACH1 OTHER_INST_PROV_TEACHING1
, (SELECT HP.PARTY_NAME
FROM HZ_PARTIES HP
, IGS_PE_HZ_PARTIES IHP
WHERE IHP.OSS_ORG_UNIT_CD = POO.OTHER_INSTIT_TEACH1
AND IHP.PARTY_ID = HP.PARTY_ID) OTHER_INST_PROV_TEACHING1_DESC
, POO.OTHER_INSTIT_TEACH2 OTHER_INST_PROV_TEACHING2
, (SELECT HP.PARTY_NAME
FROM HZ_PARTIES HP
, IGS_PE_HZ_PARTIES IHP
WHERE IHP.OSS_ORG_UNIT_CD = POO.OTHER_INSTIT_TEACH2
AND IHP.PARTY_ID = HP.PARTY_ID) OTHER_INST_PROV_TEACHING2_DESC
, POO.PROP_NOT_TAUGHT PROP_NOT_TAUGHT_BY_THIS_INST
, POO.UFI_PLACE UFI_PLACE
, ( SELECT VALUE_DESCRIPTION
FROM IGS_HE_CODE_VALUES
WHERE CODE_TYPE = 'OSS_UFI_PLACE'
AND VALUE = POO.UFI_PLACE ) UFI_PLACE_DESC
, POO.FTE_INTENSITY FTE_INTENSITY
, POO.FTE_CALC_TYPE FTE_CALC_TYPE
, '_LA:POO.FTE_CALC_TYPE:IGS_LOOKUP_VALUES:IGS_HE_FTE_CALC_TYPE:MEANING' "_LA:FTE_CALC_TYPE_DESC"
, POO.TEACH_PERIOD_START_DT TEACHING_PERIOD_START_DATE
, POO.TEACH_PERIOD_END_DT TEACHING_PERIOD_END_DATE
, POO.CREATED_BY CREATED_BY
, POO.CREATION_DATE CREATION_DATE
, POO.LAST_UPDATED_BY LAST_UPDATED_BY
, POO.LAST_UPDATE_DATE LAST_UPDATE_DATE
FROM IGS_HE_POOUS_ALL POO
, IGS_PS_VER_ALL PS
, IGS_EN_UNIT_SET_ALL EN
, IGS_CA_TYPE CA
WHERE POO.COURSE_CD = PS.COURSE_CD
AND POO.CRV_VERSION_NUMBER = PS.VERSION_NUMBER
AND POO.UNIT_SET_CD = EN.UNIT_SET_CD
AND POO.US_VERSION_NUMBER = EN.VERSION_NUMBER
AND POO.CAL_TYPE = CA.CAL_TYPE WITH READ ONLY

Columns