View: IGS_AS_UGAI_ADI_V

View Text

SELECT SUA.PERSON_ID PERSON_ID
, DECODE (IGS_AS_ANON_GRD_PKG.CHK_ANON_GRADED (SUA.UOO_ID
, NULL)
, 'Y'
, NULL
, P.PARTY_NUMBER) PERSON_NUMBER
, DECODE (IGS_AS_ANON_GRD_PKG.CHK_ANON_GRADED (SUA.UOO_ID
, NULL)
, 'Y'
, NULL
, P.PERSON_LAST_NAME || '
, ' || P.PERSON_FIRST_NAME || ' ' || P.PERSON_MIDDLE_NAME ) PERSON_NAME
, SUA.COURSE_CD COURSE_CD
, SUA.UNIT_CD UNIT_CD
, SUA.VERSION_NUMBER SUA_VERSION_NUMBER
, SUA.CAL_TYPE CAL_TYPE
, SUA.CI_SEQUENCE_NUMBER CI_SEQUENCE_NUMBER
, SUA.CI_START_DT CI_START_DT
, SUA.CI_END_DT CI_END_DT
, SUA.UOO_ID UOO_ID
, SUAO.OUTCOME_DT OUTCOME_DT
, SUAO.GRADING_SCHEMA_CD GRADING_SCHEMA_CD
, SUAO.VERSION_NUMBER SUAO_VERSION_NUMBER
, SUAO.GRADE GRADE
, SUAO.S_GRADE_CREATION_METHOD_TYPE S_GRADE_CREATION_METHOD_TYPE
, SUAO.FINALISED_OUTCOME_IND FINALISED_OUTCOME_INDICATOR
, SUAO.MARK MARK
, SUAO.NUMBER_TIMES_KEYED NUMBER_TIMES_KEYED
, SUAO.TRANSLATED_GRADING_SCHEMA_CD TRANSLATED_GRADING_SCHEMA_CD
, SUAO.TRANSLATED_VERSION_NUMBER TRANSLATED_VERSION_NUMBER
, SUAO.TRANSLATED_GRADE TRANSLATED_GRADE
, SUAO.TRANSLATED_DT TRANSLATED_DT
, SUA.ORG_ID ORG_ID
, SUAO.INCOMP_DEADLINE_DATE INCOMP_DEADLINE_DATE
, SUAO.INCOMP_GRADING_SCHEMA_CD INCOMP_GRADING_SCHEMA_CD
, SUAO.INCOMP_VERSION_NUMBER INCOMP_VERSION_NUMBER
, SUAO.INCOMP_DEFAULT_MARK INCOMP_DEFAULT_MARK
, SUAO.INCOMP_DEFAULT_GRADE INCOMP_DEFAULT_GRADE
, SUAO.COMMENTS COMMENTS
, SUAO.GRADING_PERIOD_CD GRADING_PERIOD_CD
, NULL ASSESSMENT_TYPE
, NULL EXAM_LOCATION_CD
, TO_NUMBER(NULL) ASS_ID
, SUA.LOCATION_CD LOCATION_CD
, SUA.UNIT_CLASS UNIT_CLASS
, NULL CHK_ANON_GRADED
, SUAO.MARK_CAPPED_FLAG MARK_CAPPED_FLAG
, SUAO.SHOW_ON_ACADEMIC_HISTRY_FLAG SHOW_ON_ACADEMIC_HISTRY_FLAG
, SUAO.RELEASE_DATE RELEASE_DATE
, SUAO.MANUAL_OVERRIDE_FLAG MANUAL_OVERRIDE_FLAG
, IGS_AS_ANON_GRD_PKG.GET_ANON_ID ( SUA.PERSON_ID
, SUA.COURSE_CD
, SUA.UNIT_CD
, SUA.CAL_TYPE
, SUA.CI_SEQUENCE_NUMBER
, SUA.UOO_ID
, NULL
, 'Y' ) ANONYMOUS_ID
, NULL REFERENCE
FROM IGS_EN_SU_ATTEMPT_SV SUA
, IGS_AS_SU_STMPTOUT_ALL SUAO
, HZ_PARTIES P
WHERE SUA.UNIT_CD = SUAO.UNIT_CD (+)
AND SUA.PERSON_ID = SUAO.PERSON_ID (+)
AND SUA.COURSE_CD = SUAO.COURSE_CD (+)
AND SUA.CAL_TYPE = SUAO.CAL_TYPE (+)
AND SUA.CI_SEQUENCE_NUMBER = SUAO.CI_SEQUENCE_NUMBER (+)
AND SUA.PERSON_ID = P.PARTY_ID
AND (SUA.UNIT_ATTEMPT_STATUS IN ('ENROLLED'
, 'COMPLETED') OR (SUA.UNIT_ATTEMPT_STATUS = 'DISCONTIN'
AND EXISTS (SELECT 1
FROM IGS_AS_GRD_SCH_GRADE GSG
WHERE SUAO.GRADING_SCHEMA_CD = GSG.GRADING_SCHEMA_CD
AND SUAO.VERSION_NUMBER = GSG.VERSION_NUMBER
AND SUAO.GRADE = GSG.GRADE
AND GSG.S_RESULT_TYPE = 'FAIL')))
AND ( SUAO.PERSON_ID IS NULL OR SUAO.OUTCOME_DT = (SELECT MAX (OUTCOME_DT)
FROM IGS_AS_SU_STMPTOUT_ALL
WHERE PERSON_ID = SUAO.PERSON_ID
AND COURSE_CD = SUAO.COURSE_CD
AND UOO_ID = SUAO.UOO_ID
AND GRADING_PERIOD_CD = SUAO.GRADING_PERIOD_CD) ) UNION SELECT HZ.PARTY_ID PERSON_ID
, DECODE (IGS_AS_ANON_GRD_PKG.CHK_ANON_GRADED ( UOO.UOO_ID
, SAI.ASS_ID)
, 'Y'
, NULL
, HZ.PARTY_NUMBER ) PERSON_NUMBER
, DECODE (IGS_AS_ANON_GRD_PKG.CHK_ANON_GRADED (UOO.UOO_ID
, SAI.ASS_ID)
, 'Y'
, NULL
, HZ.PERSON_LAST_NAME || '
, ' || HZ.PERSON_FIRST_NAME || ' ' || HZ.PERSON_MIDDLE_NAME ) PERSON_NAME
, SAI.COURSE_CD COURSE_CD
, SUA.UNIT_CD UNIT_CD
, SUA.VERSION_NUMBER SUA_VERSION_NUMBER
, SUA.CAL_TYPE CAL_TYPE
, SUA.CI_SEQUENCE_NUMBER CI_SEQUENCE_NUMBER
, TO_DATE(NULL
, 'YYYY-MM-DD') CI_START_DT
, TO_DATE(NULL
, 'YYYY-MM-DD') CI_END_DT
, UOO.UOO_ID UOO_ID
, SAI.OUTCOME_DT OUTCOME_DT
, SAI.GRADING_SCHEMA_CD GRADING_SCHEMA_CD
, TO_NUMBER(NULL) SUAO_VERSION_NUMBER
, SAI.GRADE GRADE
, NULL S_GRADE_CREATION_METHOD_TYPE
, NULL FINALISED_OUTCOME_IND
, SAI.MARK MARK
, TO_NUMBER(NULL) NUMBER_TIMES_KEYED
, NULL TRANSLATED_GRADING_SCHEMA_CD
, TO_NUMBER(NULL) TRANSLATED_VERSION_NUMBER
, NULL TRANSLATED_GRADE
, TO_DATE(NULL
, 'YYYY-MM-DD') TRANSLATED_DT
, TO_NUMBER(NULL) ORG_ID
, TO_DATE(NULL
, 'YYYY-MM-DD') INCOMP_DEADLINE_DT
, NULL INCOMP_GRADING_SCHEMA_CD
, TO_NUMBER(NULL) INCOMP_VERSION_NUMBER
, TO_NUMBER(NULL) INCOMP_DEFAULT_MARK
, NULL INCOMP_DEFAULT_GRADE
, AIA.COMMENTS COMMENTS
, '-' GRADING_PERIOD_CD
, AIA.ASSESSMENT_TYPE ASSESSMENT_TYPE
, SUA.EXAM_LOCATION_CD EXAM_LOCATION_CD
, SAI.ASS_ID ASS_ID
, UOO.LOCATION_CD LOCATION_CD
, UOO.UNIT_CLASS UNIT_CLASS
, IGS_AS_ANON_GRD_PKG.CHK_ANON_GRADED(UOO.UOO_ID
, SAI.ASS_ID) CHK_ANON_GRADED
, NULL MARK_CAPPED_FLAG
, NULL SHOW_ON_ACADEMIC_HISTRY_FLAG
, TO_DATE(NULL
, 'DD-MON-RRRR') RELEASE_DATE
, NULL MANUAL_OVERRIDE_FLAG
, IGS_AS_ANON_GRD_PKG.GET_ANON_ID ( SUA.PERSON_ID
, SUA.COURSE_CD
, SUA.UNIT_CD
, SUA.CAL_TYPE
, SUA.CI_SEQUENCE_NUMBER
, SUA.UOO_ID
, SAI.ASS_ID
, 'N' ) ANONYMOUS_ID
, IGS_AS_GEN_003.ASSP_GET_AI_REF(SAI.UNIT_SECTION_ASS_ITEM_ID
, SAI.UNIT_ASS_ITEM_ID ) REFERENCE
FROM HZ_PARTIES HZ
, IGS_EN_SU_ATTEMPT_SV SUA
, IGS_AS_SU_ATMPT_ITM SAI
, IGS_PS_UNIT_OFR_OPT_ALL UOO
, IGS_AS_ASSESSMNT_ITM_ALL AIA
WHERE HZ.PARTY_ID = SAI.PERSON_ID
AND SAI.PERSON_ID = SUA.PERSON_ID
AND SAI.UNIT_CD = SUA.UNIT_CD
AND SAI.COURSE_CD = SUA.COURSE_CD
AND SAI.CAL_TYPE = SUA.CAL_TYPE
AND SAI.CI_SEQUENCE_NUMBER = SUA.CI_SEQUENCE_NUMBER
AND UOO.UOO_ID = SUA.UOO_ID
AND SAI.ASS_ID = AIA.ASS_ID
AND SAI.LOGICAL_DELETE_DT IS NULL
AND ( SUA.UNIT_ATTEMPT_STATUS IN ('ENROLLED'
, 'COMPLETED') OR ( SUA.UNIT_ATTEMPT_STATUS = 'DISCONTIN'
AND EXISTS ( SELECT 1
FROM IGS_AS_GRD_SCH_GRADE GSG
WHERE SAI.GRADING_SCHEMA_CD = GSG.GRADING_SCHEMA_CD
AND SAI.GS_VERSION_NUMBER = GSG.VERSION_NUMBER
AND SAI.GRADE = GSG.GRADE
AND GSG.S_RESULT_TYPE = 'FAIL') ) )

Columns