View: IGSFV_ST_UNIT_SET_ATT_HESA_DET

View Text

SELECT SUSA.HESA_EN_SUSA_ID HESA_EN_SUSA_ID
, SUSA.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
, SUSA.COURSE_CD PROGRAM_CODE
, PS.TITLE PROGRAM_TITLE
, PS.SHORT_TITLE PROGRAM_SHORT_TITLE
, PS.ABBREVIATION PROGRAM_ABBREVIATION
, SUSA.UNIT_SET_CD UNIT_SET_CODE
, US.TITLE UNIT_SET_TITLE
, US.SHORT_TITLE UNIT_SET_SHORT_TITLE
, US.ABBREVIATION UNIT_SET_ABBREVIATION
, SUSA.US_VERSION_NUMBER UNIT_SET_VERSION_NUMBER
, SUSA.SEQUENCE_NUMBER SEQUENCE_NUMBER
, SUSA.NEW_HE_ENTRANT_CD NEW_HE_ENTRANT_CODE
, (SELECT VALUE_DESCRIPTION
FROM IGS_HE_CODE_VALUES
WHERE CODE_TYPE = 'OSS_HEENT'
AND VALUE = SUSA.NEW_HE_ENTRANT_CD) NEW_HE_ENTRANT_DESC
, SUSA.TERM_TIME_ACCOM TERM_TIME_ACCOMMODATION
, (SELECT VALUE_DESCRIPTION
FROM IGS_HE_CODE_VALUES
WHERE CODE_TYPE = 'OSS_TTA'
AND VALUE = SUSA.TERM_TIME_ACCOM) TERM_TIME_ACCOMMODATION_DESC
, SUSA.DISABILITY_ALLOW DISABILITY_ALLOWANCE
, (SELECT VALUE_DESCRIPTION
FROM IGS_HE_CODE_VALUES
WHERE CODE_TYPE = 'OSS_DIS_ALLOW'
AND VALUE = SUSA.DISABILITY_ALLOW) DISABILITY_ALLOWANCE_DESC
, SUSA.ADDITIONAL_SUP_BAND ADDITIONAL_SUP_BAND
, (SELECT VALUE_DESCRIPTION
FROM IGS_HE_CODE_VALUES
WHERE CODE_TYPE = 'OSS_SUP_BAND'
AND VALUE = SUSA.ADDITIONAL_SUP_BAND) ADDITIONAL_SUP_BAND_DESC
, SUSA.SLDD_DISCRETE_PROV SLDD_DISCRETE_PROVISION
, (SELECT VALUE_DESCRIPTION
FROM IGS_HE_CODE_VALUES
WHERE CODE_TYPE = 'OSS_ST13'
AND VALUE = SUSA.SLDD_DISCRETE_PROV) SLDD_DISCRETE_PROVISION_DESC
, SUSA.STUDY_LOCATION LOCATION_OF_STUDY
, (SELECT VALUE_DESCRIPTION
FROM IGS_HE_CODE_VALUES
WHERE CODE_TYPE = 'OSS_LOCSDY'
AND VALUE = SUSA.STUDY_LOCATION) LOCATION_OF_STUDY_DESC
, SUSA.STUDY_MODE MODE_OF_STUDY
, (SELECT VALUE_DESCRIPTION
FROM IGS_HE_CODE_VALUES
WHERE CODE_TYPE = 'OSS_MODE_TYPE'
AND VALUE = SUSA.STUDY_MODE) MODE_OF_STUDY_DESC
, SUSA.COMPLETION_STATUS COMPLETION_STATUS
, (SELECT VALUE_DESCRIPTION
FROM IGS_HE_CODE_VALUES
WHERE CODE_TYPE = 'OSS_CSTAT'
AND VALUE = SUSA.COMPLETION_STATUS) COMPLETION_STATUS_DESC
, SUSA.GOOD_STAND_MARKER GOOD_STAND_MARKER
, (SELECT VALUE_DESCRIPTION
FROM IGS_HE_CODE_VALUES
WHERE CODE_TYPE = 'OSS_PROGRESS'
AND VALUE = SUSA.GOOD_STAND_MARKER) GOOD_STAND_MARKER_DESC
, SUSA.COMPLETE_PYR_STUDY_CD COMPLETE_PYR_STUDY_CODE
, (SELECT VALUE_DESCRIPTION
FROM IGS_HE_CODE_VALUES
WHERE CODE_TYPE = 'OSS_FUNDCOMP'
AND VALUE = SUSA.COMPLETE_PYR_STUDY_CD) COMPLETE_PYR_STUDY_DESC
, SUSA.FUNDABILITY_CODE FUNDABILITY_CODE
, (SELECT VALUE_DESCRIPTION
FROM IGS_HE_CODE_VALUES
WHERE CODE_TYPE = 'OSS_FUND_CODE'
AND VALUE = SUSA.FUNDABILITY_CODE) FUNDABILITY_DESC
, SUSA.FEE_BAND FEE_BAND
, (SELECT VALUE_DESCRIPTION
FROM IGS_HE_CODE_VALUES
WHERE CODE_TYPE = 'OSS_FEEBAND'
AND VALUE = SUSA.FEE_BAND) FEE_BAND_DESC
, SUSA.FEE_ELIGIBILITY FEE_ELIGIBILITY
, (SELECT VALUE_DESCRIPTION
FROM IGS_HE_CODE_VALUES
WHERE CODE_TYPE = 'OSS_FEEELIG'
AND VALUE = SUSA.FEE_ELIGIBILITY) FEE_ELIGIBILITY_DESC
, SUSA.STUDENT_FEE MAJOR_SOURCE_TUITION_FEE
, (SELECT VALUE_DESCRIPTION
FROM IGS_HE_CODE_VALUES
WHERE CODE_TYPE = 'OSS_MSTUFEE'
AND VALUE = SUSA.STUDENT_FEE) MAJOR_SOURCE_TUITION_FEE_DESC
, SUSA.NON_PAYMENT_REASON NON_PAYMENT_REASON
, (SELECT VALUE_DESCRIPTION
FROM IGS_HE_CODE_VALUES
WHERE CODE_TYPE = 'OSS_NONPAY'
AND VALUE = SUSA.NON_PAYMENT_REASON) NON_PAYMENT_REASON_DESC
, SUSA.FRANCHISING_ACTIVITY FRANCHISING_ACTIVITY
, (SELECT VALUE_DESCRIPTION
FROM IGS_HE_CODE_VALUES
WHERE CODE_TYPE = 'OSS_FRAN_ACT'
AND VALUE = SUSA.FRANCHISING_ACTIVITY) FRANCHISING_ACTIVITY_DESC
, SUSA.TYPE_OF_YEAR TYPE_PROGRAM_YEAR
, (SELECT VALUE_DESCRIPTION
FROM IGS_HE_CODE_VALUES
WHERE CODE_TYPE = 'OSS_TYPEYR'
AND VALUE = SUSA.TYPE_OF_YEAR) TYPE_PROGRAM_YEAR_DESC
, SUSA.CREDIT_VALUE_YOP1 YOP_CREDIT_VALUE_1
, SUSA.CREDIT_LEVEL1 LEVEL_CREDIT_1
, (SELECT DESCRIPTION
FROM IGS_PS_UNIT_LEVEL_ALL
WHERE UNIT_LEVEL = SUSA.CREDIT_LEVEL1) LEVEL_CREDIT_1_DESC
, SUSA.CREDIT_VALUE_YOP2 YOP_CREDIT_VALUE_2
, SUSA.CREDIT_LEVEL2 LEVEL_CREDIT_2
, (SELECT DESCRIPTION
FROM IGS_PS_UNIT_LEVEL_ALL
WHERE UNIT_LEVEL = SUSA.CREDIT_LEVEL2) LEVEL_CREDIT_2_DESC
, SUSA.CREDIT_PT_ACHIEVED1 CREDIT_PTS_ACHIEVED1
, SUSA.CREDIT_LEVEL_ACHIEVED1 CREDIT_LEVEL_ACHIEVED1
, (SELECT DESCRIPTION
FROM IGS_PS_UNIT_LEVEL_ALL
WHERE UNIT_LEVEL = SUSA.CREDIT_LEVEL_ACHIEVED1) CREDIT_LEVEL_ACHIEVED1_DESC
, SUSA.CREDIT_PT_ACHIEVED2 CREDIT_PTS_ACHIEVED2
, SUSA.CREDIT_LEVEL_ACHIEVED2 CREDIT_LEVEL_ACHIEVED2
, (SELECT DESCRIPTION
FROM IGS_PS_UNIT_LEVEL_ALL
WHERE UNIT_LEVEL = SUSA.CREDIT_LEVEL_ACHIEVED2) CREDIT_LEVEL_ACHIEVED2_DESC
, SUSA.TEACHING_INST1 OTHER_INST_PROV_TEACHING1
, (SELECT HP.PARTY_NAME
FROM HZ_PARTIES HP
, IGS_PE_HZ_PARTIES IHP
WHERE IHP.OSS_ORG_UNIT_CD = SUSA.TEACHING_INST1
AND IHP.PARTY_ID = HP.PARTY_ID) OTHER_INST_PROV_TEACHING1_DESC
, SUSA.TEACHING_INST2 OTHER_INST_PROV_TEACHING2
, (SELECT HP.PARTY_NAME
FROM HZ_PARTIES HP
, IGS_PE_HZ_PARTIES IHP
WHERE IHP.OSS_ORG_UNIT_CD = SUSA.TEACHING_INST2
AND IHP.PARTY_ID = HP.PARTY_ID) OTHER_INST_PROV_TEACHING2_DESC
, SUSA.PRO_NOT_TAUGHT PROP_NOT_TAUGHT_BY_THIS_INST
, SUSA.FTE_PERC_OVERRIDE FTE_PERC_OVERRIDE
, SUSA.FTE_INTENSITY FTE_INTENSITY
, SUSA.FTE_CALC_TYPE FTE_CALC_TYPE
, '_LA:SUSA.FTE_CALC_TYPE:IGS_LOOKUP_VALUES:IGS_HE_FTE_CALC_TYPE:MEANING' "_LA:FTE_CALC_TYPE_DESC"
, SUSA.CALCULATED_FTE CALCULATED_FTE
, SUSA.YEAR_STU YEAR_OF_STUDENT_ON_PROGRAM
, SUSA.CREDIT_VALUE_YOP3 YOP_CREDIT_VALUE_3
, SUSA.CREDIT_LEVEL3 LEVEL_CREDIT_3
, (SELECT DESCRIPTION
FROM IGS_PS_UNIT_LEVEL_ALL
WHERE UNIT_LEVEL = SUSA.CREDIT_LEVEL3) LEVEL_CREDIT_3_DESC
, SUSA.CREDIT_VALUE_YOP4 YOP_CREDIT_VALUE_4
, SUSA.CREDIT_LEVEL4 LEVEL_CREDIT_4
, (SELECT DESCRIPTION
FROM IGS_PS_UNIT_LEVEL_ALL
WHERE UNIT_LEVEL = SUSA.CREDIT_LEVEL4) LEVEL_CREDIT_4_DESC
, SUSA.CREDIT_PT_ACHIEVED3 CREDIT_PTS_ACHIEVED3
, SUSA.CREDIT_LEVEL_ACHIEVED3 CREDIT_LEVEL_ACHIEVED3
, (SELECT DESCRIPTION
FROM IGS_PS_UNIT_LEVEL_ALL
WHERE UNIT_LEVEL = SUSA.CREDIT_LEVEL_ACHIEVED3) CREDIT_LEVEL_ACHIEVED3_DESC
, SUSA.CREDIT_PT_ACHIEVED4 CREDIT_PTS_ACHIEVED4
, SUSA.CREDIT_LEVEL_ACHIEVED4 CREDIT_LEVEL_ACHIEVED4
, (SELECT DESCRIPTION
FROM IGS_PS_UNIT_LEVEL_ALL
WHERE UNIT_LEVEL = SUSA.CREDIT_LEVEL_ACHIEVED4) CREDIT_LEVEL_ACHIEVED4_DESC
, SUSA.ENH_FUND_ELIG_CD ENHANCED_FUNDING_ELIG_CODE
, (SELECT VALUE_DESCRIPTION
FROM IGS_HE_CODE_VALUES
WHERE CODE_TYPE = 'OSS_ELIGENFD'
AND VALUE = SUSA.ENH_FUND_ELIG_CD) ENHANCED_FUNDING_ELIG_DESC
, SUSA.ADDITIONAL_SUP_COST ADDITIONAL_SUPPORT_COST
, SUSA.DISADV_UPLIFT_FACTOR DISADVANTAGE_UPLIFT_FACTOR
, SUSA.CREATION_DATE CREATION_DATE
, SUSA.CREATED_BY CREATED_BY
, SUSA.LAST_UPDATE_DATE LAST_UPDATE_DATE
, SUSA.LAST_UPDATED_BY LAST_UPDATED_BY
FROM IGS_HE_EN_SUSA SUSA
, HZ_PARTIES PER
, HZ_PERSON_PROFILES PERP
, IGS_EN_STDNT_PS_ATT_ALL SPA
, IGS_PS_VER_ALL PS
, IGS_EN_UNIT_SET_ALL US
WHERE PER.PARTY_ID = SUSA.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 SPA.PERSON_ID = SUSA.PERSON_ID
AND SPA.COURSE_CD = SUSA.COURSE_CD
AND PS.COURSE_CD = SUSA.COURSE_CD
AND PS.VERSION_NUMBER = SPA.VERSION_NUMBER
AND US.UNIT_SET_CD = SUSA.UNIT_SET_CD
AND US.VERSION_NUMBER = SUSA.US_VERSION_NUMBER WITH READ ONLY

Columns