VIEW: APPS.IGS_PR_SUA_INQ_V

Source

View Text - Preformatted

SELECT SUA.PERSON_ID, SUA.COURSE_CD, SUA.UNIT_CD, SUA.VERSION_NUMBER, SUA.UNIT_CD || '.' || TO_CHAR(SUA.VERSION_NUMBER), NVL(SUA.ALTERNATIVE_TITLE,UV.SHORT_TITLE), SUBSTR(IGS_EN_GEN_014.ENRS_GET_ACAD_ALT_CD( SUA.CAL_TYPE, SUA.CI_SEQUENCE_NUMBER),1,10) || '/' || SUBSTR(IGS_CA_GEN_001.CALP_GET_ALT_CD( SUA.CAL_TYPE, SUA.CI_SEQUENCE_NUMBER),1,10), SUA.CAL_TYPE, SUA.CI_SEQUENCE_NUMBER, SUBSTR(IGS_EN_GEN_014.ENRS_GET_ACAD_ALT_CD( SUA.CAL_TYPE, SUA.CI_SEQUENCE_NUMBER),1,10), SUBSTR(IGS_CA_GEN_001.CALP_GET_ALT_CD( SUA.CAL_TYPE, SUA.CI_SEQUENCE_NUMBER),1,10), SUA.LOCATION_CD, SUA.UNIT_CLASS, UC.UNIT_MODE, SUA.CI_START_DT, SUA.CI_END_DT, SUA.UOO_ID, SUA.ENROLLED_DT, SUA.UNIT_ATTEMPT_STATUS, SUA.ADMINISTRATIVE_UNIT_STATUS, SUA.DISCONTINUED_DT, SUA.RULE_WAIVED_DT, SUA.RULE_WAIVED_PERSON_ID, SUA.NO_ASSESSMENT_IND, SUA.SUP_UNIT_CD, SUA.SUP_VERSION_NUMBER, SUA.EXAM_LOCATION_CD, NVL(SUA.OVERRIDE_ENROLLED_CP, NVL(cps.enrolled_credit_points,UV.ENROLLED_CREDIT_POINTS)), NVL(SUA.OVERRIDE_EFTSU, IGS_EN_GEN_014.ENRS_CLC_SUA_EFTSUT( SUA.PERSON_ID, SUA.COURSE_CD, SCA.VERSION_NUMBER, SUA.UNIT_CD, SUA.VERSION_NUMBER, SUA.CAL_TYPE, SUA.CI_SEQUENCE_NUMBER, SUA.UOO_ID, SUA.OVERRIDE_ENROLLED_CP, SUA.OVERRIDE_EFTSU, NULL)), IGS_IN_GEN_001.INQP_GET_SUA_ACHVD(SUA.PERSON_ID,SUA.COURSE_CD,SUA.UNIT_CD, SUA.CAL_TYPE,SUA.CI_SEQUENCE_NUMBER,SUA.VERSION_NUMBER, SUA.CI_END_DT,SUA.UNIT_ATTEMPT_STATUS, SUA.OVERRIDE_ACHIEVABLE_CP, NULL,UV.REPEATABLE_IND, NVL(cps.achievable_credit_points,UV.ACHIEVABLE_CREDIT_POINTS), NVL(cps.enrolled_credit_points,UV.ENROLLED_CREDIT_POINTS), SUA.UOO_ID), SUA.OVERRIDE_OUTCOME_DUE_DT, SUA.CREATED_BY, SUA.CREATION_DATE, SUA.LAST_UPDATED_BY, SUA.LAST_UPDATE_DATE, SUA.LAST_UPDATE_LOGIN, SUA.OVERRIDE_CREDIT_REASON, sfov.mark, NVL(sfov.translated_grading_schema_cd, sfov.grading_schema_cd), NVL(sfov.translated_gs_version_number, sfov.gs_version_number), NVL(sfov.translated_grade, sfov.grade), gsg.s_result_type ,SUA.DEG_AUD_DETAIL_ID FROM IGS_EN_SU_ATTEMPT SUA, IGS_PS_UNIT_OFR_OPT UOO, IGS_PS_USEC_CPS CPS, IGS_EN_STDNT_PS_ATT SCA, IGS_PS_UNIT_VER_ALL UV, IGS_AS_UNIT_CLASS UC, IGS_PR_sua_final_outcome_v sfov, IGS_AS_GRD_SCH_GRADE gsg WHERE sua.unit_attempt_status in ('COMPLETED','DISCONTIN','DUPLICATE') AND UV.UNIT_CD = SUA.UNIT_CD AND UV.VERSION_NUMBER = SUA.VERSION_NUMBER AND SUA.uoo_id = uoo.uoo_id AND uoo.uoo_id = cps.uoo_id(+) AND UC.UNIT_CLASS = SUA.UNIT_CLASS AND SCA.PERSON_ID = SUA.PERSON_ID AND SCA.COURSE_CD = SUA.COURSE_CD and sfov.person_id = sua.person_id AND sfov.course_cd = sua.course_cd AND sfov.uoo_id = sua.uoo_id AND gsg.grading_schema_cd = NVL(sfov.translated_grading_schema_cd, sfov.grading_schema_cd) AND gsg.version_number = NVL(sfov.translated_gs_version_number, sfov.gs_version_number) AND gsg.grade = NVL(sfov.translated_grade, sfov.grade) UNION ALL SELECT SUA.PERSON_ID, SUA.COURSE_CD, SUA.UNIT_CD, SUA.VERSION_NUMBER, SUA.UNIT_CD || '.' || TO_CHAR(SUA.VERSION_NUMBER), NVL(SUA.ALTERNATIVE_TITLE,UV.SHORT_TITLE), SUBSTR(IGS_EN_GEN_014.ENRS_GET_ACAD_ALT_CD(SUA.CAL_TYPE,SUA.CI_SEQUENCE_NUMBER),1,10) || '/' || SUBSTR(IGS_CA_GEN_001.CALP_GET_ALT_CD(SUA.CAL_TYPE,SUA.CI_SEQUENCE_NUMBER),1,10), SUA.CAL_TYPE, SUA.CI_SEQUENCE_NUMBER, subSTR(IGS_EN_GEN_014.ENRS_GET_ACAD_ALT_CD(SUA.CAL_TYPE,SUA.CI_SEQUENCE_NUMBER),1,10), SUBSTR(IGS_CA_GEN_001.CALP_GET_ALT_CD(SUA.CAL_TYPE,SUA.CI_SEQUENCE_NUMBER),1,10), SUA.LOCATION_CD, SUA.UNIT_CLASS, UC.UNIT_MODE, SUA.CI_START_DT, SUA.CI_END_DT, SUA.UOO_ID, SUA.ENROLLED_DT, SUA.UNIT_ATTEMPT_STATUS, SUA.ADMINISTRATIVE_UNIT_STATUS, SUA.DISCONTINUED_DT, SUA.RULE_WAIVED_DT, SUA.RULE_WAIVED_PERSON_ID, SUA.NO_ASSESSMENT_IND, SUA.SUP_UNIT_CD, SUA.SUP_VERSION_NUMBER, SUA.EXAM_LOCATION_CD, NVL(SUA.OVERRIDE_ENROLLED_CP, NVL(cps.enrolled_credit_points,UV.ENROLLED_CREDIT_POINTS)), NVL(SUA.OVERRIDE_EFTSU, IGS_EN_GEN_014.ENRS_CLC_SUA_EFTSUT( SUA.PERSON_ID, SUA.COURSE_CD, SCA.VERSION_NUMBER, SUA.UNIT_CD, SUA.VERSION_NUMBER, SUA.CAL_TYPE, SUA.CI_SEQUENCE_NUMBER, SUA.UOO_ID, SUA.OVERRIDE_ENROLLED_CP, SUA.OVERRIDE_EFTSU, NULL)), IGS_IN_GEN_001.INQP_GET_SUA_ACHVD(SUA.PERSON_ID,SUA.COURSE_CD,SUA.UNIT_CD, SUA.CAL_TYPE,SUA.CI_SEQUENCE_NUMBER,SUA.VERSION_NUMBER, SUA.CI_END_DT,SUA.UNIT_ATTEMPT_STATUS,SUA.OVERRIDE_ACHIEVABLE_CP, NULL, UV.REPEATABLE_IND, NVL(cps.ACHIEVABLE_CREDIT_POINTS,UV.ACHIEVABLE_CREDIT_POINTS), NVL(cps.ENROLLED_CREDIT_POINTS, UV.ENROLLED_CREDIT_POINTS), SUA.UOO_ID), SUA.OVERRIDE_OUTCOME_DUE_DT, SUA.CREATED_BY, SUA.CREATION_DATE, SUA.LAST_UPDATED_BY, SUA.LAST_UPDATE_DATE, SUA.LAST_UPDATE_LOGIN, SUA.OVERRIDE_CREDIT_REASON, TO_NUMBER(NULL), NULL, TO_NUMBER(NULL), NULL, NULL ,sua.deg_aud_detail_id FROM IGS_EN_SU_ATTEMPT SUA, IGS_PS_UNIT_OFR_OPT UOO, IGS_PS_USEC_CPS CPS, IGS_EN_STDNT_PS_ATT SCA, IGS_PS_UNIT_VER_ALL UV, IGS_AS_UNIT_CLASS UC WHERE sua.unit_attempt_status in ('ENROLLED','UNCONFIRM') AND UV.UNIT_CD = SUA.UNIT_CD AND UV.VERSION_NUMBER = SUA.VERSION_NUMBER AND sua.uoo_id = uoo.uoo_id AND uoo.uoo_id = cps.uoo_id(+) AND UC.UNIT_CLASS = SUA.UNIT_CLASS AND SCA.PERSON_ID = SUA.PERSON_ID AND SCA.COURSE_CD = SUA.COURSE_CD

View Text - HTML Formatted

SELECT SUA.PERSON_ID
, SUA.COURSE_CD
, SUA.UNIT_CD
, SUA.VERSION_NUMBER
, SUA.UNIT_CD || '.' || TO_CHAR(SUA.VERSION_NUMBER)
, NVL(SUA.ALTERNATIVE_TITLE
, UV.SHORT_TITLE)
, SUBSTR(IGS_EN_GEN_014.ENRS_GET_ACAD_ALT_CD( SUA.CAL_TYPE
, SUA.CI_SEQUENCE_NUMBER)
, 1
, 10) || '/' || SUBSTR(IGS_CA_GEN_001.CALP_GET_ALT_CD( SUA.CAL_TYPE
, SUA.CI_SEQUENCE_NUMBER)
, 1
, 10)
, SUA.CAL_TYPE
, SUA.CI_SEQUENCE_NUMBER
, SUBSTR(IGS_EN_GEN_014.ENRS_GET_ACAD_ALT_CD( SUA.CAL_TYPE
, SUA.CI_SEQUENCE_NUMBER)
, 1
, 10)
, SUBSTR(IGS_CA_GEN_001.CALP_GET_ALT_CD( SUA.CAL_TYPE
, SUA.CI_SEQUENCE_NUMBER)
, 1
, 10)
, SUA.LOCATION_CD
, SUA.UNIT_CLASS
, UC.UNIT_MODE
, SUA.CI_START_DT
, SUA.CI_END_DT
, SUA.UOO_ID
, SUA.ENROLLED_DT
, SUA.UNIT_ATTEMPT_STATUS
, SUA.ADMINISTRATIVE_UNIT_STATUS
, SUA.DISCONTINUED_DT
, SUA.RULE_WAIVED_DT
, SUA.RULE_WAIVED_PERSON_ID
, SUA.NO_ASSESSMENT_IND
, SUA.SUP_UNIT_CD
, SUA.SUP_VERSION_NUMBER
, SUA.EXAM_LOCATION_CD
, NVL(SUA.OVERRIDE_ENROLLED_CP
, NVL(CPS.ENROLLED_CREDIT_POINTS
, UV.ENROLLED_CREDIT_POINTS))
, NVL(SUA.OVERRIDE_EFTSU
, IGS_EN_GEN_014.ENRS_CLC_SUA_EFTSUT( SUA.PERSON_ID
, SUA.COURSE_CD
, SCA.VERSION_NUMBER
, SUA.UNIT_CD
, SUA.VERSION_NUMBER
, SUA.CAL_TYPE
, SUA.CI_SEQUENCE_NUMBER
, SUA.UOO_ID
, SUA.OVERRIDE_ENROLLED_CP
, SUA.OVERRIDE_EFTSU
, NULL))
, IGS_IN_GEN_001.INQP_GET_SUA_ACHVD(SUA.PERSON_ID
, SUA.COURSE_CD
, SUA.UNIT_CD
, SUA.CAL_TYPE
, SUA.CI_SEQUENCE_NUMBER
, SUA.VERSION_NUMBER
, SUA.CI_END_DT
, SUA.UNIT_ATTEMPT_STATUS
, SUA.OVERRIDE_ACHIEVABLE_CP
, NULL
, UV.REPEATABLE_IND
, NVL(CPS.ACHIEVABLE_CREDIT_POINTS
, UV.ACHIEVABLE_CREDIT_POINTS)
, NVL(CPS.ENROLLED_CREDIT_POINTS
, UV.ENROLLED_CREDIT_POINTS)
, SUA.UOO_ID)
, SUA.OVERRIDE_OUTCOME_DUE_DT
, SUA.CREATED_BY
, SUA.CREATION_DATE
, SUA.LAST_UPDATED_BY
, SUA.LAST_UPDATE_DATE
, SUA.LAST_UPDATE_LOGIN
, SUA.OVERRIDE_CREDIT_REASON
, SFOV.MARK
, NVL(SFOV.TRANSLATED_GRADING_SCHEMA_CD
, SFOV.GRADING_SCHEMA_CD)
, NVL(SFOV.TRANSLATED_GS_VERSION_NUMBER
, SFOV.GS_VERSION_NUMBER)
, NVL(SFOV.TRANSLATED_GRADE
, SFOV.GRADE)
, GSG.S_RESULT_TYPE
, SUA.DEG_AUD_DETAIL_ID
FROM IGS_EN_SU_ATTEMPT SUA
, IGS_PS_UNIT_OFR_OPT UOO
, IGS_PS_USEC_CPS CPS
, IGS_EN_STDNT_PS_ATT SCA
, IGS_PS_UNIT_VER_ALL UV
, IGS_AS_UNIT_CLASS UC
, IGS_PR_SUA_FINAL_OUTCOME_V SFOV
, IGS_AS_GRD_SCH_GRADE GSG
WHERE SUA.UNIT_ATTEMPT_STATUS IN ('COMPLETED'
, 'DISCONTIN'
, 'DUPLICATE')
AND UV.UNIT_CD = SUA.UNIT_CD
AND UV.VERSION_NUMBER = SUA.VERSION_NUMBER
AND SUA.UOO_ID = UOO.UOO_ID
AND UOO.UOO_ID = CPS.UOO_ID(+)
AND UC.UNIT_CLASS = SUA.UNIT_CLASS
AND SCA.PERSON_ID = SUA.PERSON_ID
AND SCA.COURSE_CD = SUA.COURSE_CD
AND SFOV.PERSON_ID = SUA.PERSON_ID
AND SFOV.COURSE_CD = SUA.COURSE_CD
AND SFOV.UOO_ID = SUA.UOO_ID
AND GSG.GRADING_SCHEMA_CD = NVL(SFOV.TRANSLATED_GRADING_SCHEMA_CD
, SFOV.GRADING_SCHEMA_CD)
AND GSG.VERSION_NUMBER = NVL(SFOV.TRANSLATED_GS_VERSION_NUMBER
, SFOV.GS_VERSION_NUMBER)
AND GSG.GRADE = NVL(SFOV.TRANSLATED_GRADE
, SFOV.GRADE) UNION ALL SELECT SUA.PERSON_ID
, SUA.COURSE_CD
, SUA.UNIT_CD
, SUA.VERSION_NUMBER
, SUA.UNIT_CD || '.' || TO_CHAR(SUA.VERSION_NUMBER)
, NVL(SUA.ALTERNATIVE_TITLE
, UV.SHORT_TITLE)
, SUBSTR(IGS_EN_GEN_014.ENRS_GET_ACAD_ALT_CD(SUA.CAL_TYPE
, SUA.CI_SEQUENCE_NUMBER)
, 1
, 10) || '/' || SUBSTR(IGS_CA_GEN_001.CALP_GET_ALT_CD(SUA.CAL_TYPE
, SUA.CI_SEQUENCE_NUMBER)
, 1
, 10)
, SUA.CAL_TYPE
, SUA.CI_SEQUENCE_NUMBER
, SUBSTR(IGS_EN_GEN_014.ENRS_GET_ACAD_ALT_CD(SUA.CAL_TYPE
, SUA.CI_SEQUENCE_NUMBER)
, 1
, 10)
, SUBSTR(IGS_CA_GEN_001.CALP_GET_ALT_CD(SUA.CAL_TYPE
, SUA.CI_SEQUENCE_NUMBER)
, 1
, 10)
, SUA.LOCATION_CD
, SUA.UNIT_CLASS
, UC.UNIT_MODE
, SUA.CI_START_DT
, SUA.CI_END_DT
, SUA.UOO_ID
, SUA.ENROLLED_DT
, SUA.UNIT_ATTEMPT_STATUS
, SUA.ADMINISTRATIVE_UNIT_STATUS
, SUA.DISCONTINUED_DT
, SUA.RULE_WAIVED_DT
, SUA.RULE_WAIVED_PERSON_ID
, SUA.NO_ASSESSMENT_IND
, SUA.SUP_UNIT_CD
, SUA.SUP_VERSION_NUMBER
, SUA.EXAM_LOCATION_CD
, NVL(SUA.OVERRIDE_ENROLLED_CP
, NVL(CPS.ENROLLED_CREDIT_POINTS
, UV.ENROLLED_CREDIT_POINTS))
, NVL(SUA.OVERRIDE_EFTSU
, IGS_EN_GEN_014.ENRS_CLC_SUA_EFTSUT( SUA.PERSON_ID
, SUA.COURSE_CD
, SCA.VERSION_NUMBER
, SUA.UNIT_CD
, SUA.VERSION_NUMBER
, SUA.CAL_TYPE
, SUA.CI_SEQUENCE_NUMBER
, SUA.UOO_ID
, SUA.OVERRIDE_ENROLLED_CP
, SUA.OVERRIDE_EFTSU
, NULL))
, IGS_IN_GEN_001.INQP_GET_SUA_ACHVD(SUA.PERSON_ID
, SUA.COURSE_CD
, SUA.UNIT_CD
, SUA.CAL_TYPE
, SUA.CI_SEQUENCE_NUMBER
, SUA.VERSION_NUMBER
, SUA.CI_END_DT
, SUA.UNIT_ATTEMPT_STATUS
, SUA.OVERRIDE_ACHIEVABLE_CP
, NULL
, UV.REPEATABLE_IND
, NVL(CPS.ACHIEVABLE_CREDIT_POINTS
, UV.ACHIEVABLE_CREDIT_POINTS)
, NVL(CPS.ENROLLED_CREDIT_POINTS
, UV.ENROLLED_CREDIT_POINTS)
, SUA.UOO_ID)
, SUA.OVERRIDE_OUTCOME_DUE_DT
, SUA.CREATED_BY
, SUA.CREATION_DATE
, SUA.LAST_UPDATED_BY
, SUA.LAST_UPDATE_DATE
, SUA.LAST_UPDATE_LOGIN
, SUA.OVERRIDE_CREDIT_REASON
, TO_NUMBER(NULL)
, NULL
, TO_NUMBER(NULL)
, NULL
, NULL
, SUA.DEG_AUD_DETAIL_ID
FROM IGS_EN_SU_ATTEMPT SUA
, IGS_PS_UNIT_OFR_OPT UOO
, IGS_PS_USEC_CPS CPS
, IGS_EN_STDNT_PS_ATT SCA
, IGS_PS_UNIT_VER_ALL UV
, IGS_AS_UNIT_CLASS UC
WHERE SUA.UNIT_ATTEMPT_STATUS IN ('ENROLLED'
, 'UNCONFIRM')
AND UV.UNIT_CD = SUA.UNIT_CD
AND UV.VERSION_NUMBER = SUA.VERSION_NUMBER
AND SUA.UOO_ID = UOO.UOO_ID
AND UOO.UOO_ID = CPS.UOO_ID(+)
AND UC.UNIT_CLASS = SUA.UNIT_CLASS
AND SCA.PERSON_ID = SUA.PERSON_ID
AND SCA.COURSE_CD = SUA.COURSE_CD