View: IGS_AS_ASSITEM_ADI_V

Product:	IGS - Student System
Description:	Assessment Item WebADI View
Implementation/DBA Data:	APPS.IGS_AS_ASSITEM_ADI_V

View Text

SELECT HZ.PERSON_LAST_NAME || '
, ' || HZ.PERSON_FIRST_NAME || ' ' || HZ.PERSON_MIDDLE_NAME PARTY_NAME
, HZ.PARTY_NUMBER PARTY_NUMBER
, NULL ANONYMOUS_ID
, AIA.ASSESSMENT_TYPE ASSESSMENT_TYPE
, SAI.GRADING_SCHEMA_CD GRADING_SCHEMA_CD
, SAI.GS_VERSION_NUMBER GS_VERSION_NUMBER
, SAI.OUTCOME_DT OUTCOME_DT
, SAI.MARK MARK
, SAI.GRADE GRADE
, DECODE(SAI.OUTCOME_COMMENT_CODE
, NULL
, ''
, IGS_GE_GEN_004.GENP_GET_LOOKUP('MARKS_GRADE_CHANGE_COMMENT'
, SAI.OUTCOME_COMMENT_CODE )) COMMENTS
, SUA.EXAM_LOCATION_CD EXAM_LOCATION_CD
, SAI.PERSON_ID PERSON_ID
, SAI.COURSE_CD COURSE_CD
, SAI.UNIT_CD UNIT_CD
, UV.TITLE TITLE
, SAI.CAL_TYPE CAL_TYPE
, SAI.CI_SEQUENCE_NUMBER CI_SEQUENCE_NUMBER
, SAI.ASS_ID ASSESSMENT_ID
, UOO.LOCATION_CD LOCATION_CD
, UOO.UOO_ID UOO_ID
, IGS_AS_ANON_GRD_PKG.CHK_ANON_GRADED(UOO.UOO_ID
, SAI.ASS_ID) CHK_ANON_GRADED
, DECODE (SAI.UNIT_SECTION_ASS_ITEM_ID
, NULL
, (SELECT UAI.REFERENCE
FROM IGS_AS_UNITASS_ITEM_ALL UAI
WHERE UAI.UNIT_ASS_ITEM_ID = SAI.UNIT_ASS_ITEM_ID)
, (SELECT USAI.REFERENCE
FROM IGS_PS_UNITASS_ITEM USAI
WHERE USAI.UNIT_SECTION_ASS_ITEM_ID = SAI.UNIT_SECTION_ASS_ITEM_ID)) REFERENCE
, DECODE (SAI.UNIT_SECTION_ASS_ITEM_ID
, NULL
, (SELECT UAI.DUE_DT
FROM IGS_AS_UNITASS_ITEM_ALL UAI
WHERE UAI.UNIT_ASS_ITEM_ID = SAI.UNIT_ASS_ITEM_ID)
, (SELECT USAI.DUE_DT
FROM IGS_PS_UNITASS_ITEM USAI
WHERE USAI.UNIT_SECTION_ASS_ITEM_ID = SAI.UNIT_SECTION_ASS_ITEM_ID)) DUE_DT
, SAI.WAIVED_FLAG WAIVED_FLAG
, SAI.OVERRIDE_DUE_DT OVERRIDE_DUE_DT
, SAI.SUBMITTED_DATE SUBMITTED_DATE
, SAI.PENALTY_APPLIED_FLAG PENALTY_APPLIED_FLAG
, UOO.UNIT_CLASS UNIT_CLASS
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
, IGS_PS_UNIT_VER_ALL UV
WHERE HZ.PARTY_ID = SAI.PERSON_ID
AND SAI.PERSON_ID = SUA.PERSON_ID
AND SAI.UNIT_CD = SUA.UNIT_CD
AND SUA.UNIT_CD = UV.UNIT_CD
AND SUA.VERSION_NUMBER = UV.VERSION_NUMBER
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 IGS_AS_ANON_GRD_PKG.CHK_ANON_GRADED(UOO.UOO_ID
, SAI.ASS_ID) = 'N'
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') ) ) UNION SELECT NULL PARTY_NAME
, NULL PARTY_NUMBER
, NVL(IGS_AS_ANON_GRD_PKG.GET_ANON_ID(SAI.PERSON_ID
, SAI.COURSE_CD
, SAI.UNIT_CD
, SAI.CAL_TYPE
, SAI.CI_SEQUENCE_NUMBER
, UOO.UOO_ID
, SAI.ASS_ID
, 'N')
, 'ERROR NO ANONYMOUS ID') ANONYMOUS_ID
, AIA.ASSESSMENT_TYPE ASSESSMENT_TYPE
, SAI.GRADING_SCHEMA_CD GRADING_SCHEMA_CD
, SAI.GS_VERSION_NUMBER GS_VERSION_NUMBER
, SAI.OUTCOME_DT OUTCOME_DT
, SAI.MARK MARK
, SAI.GRADE GRADE
, DECODE(SAI.OUTCOME_COMMENT_CODE
, NULL
, ''
, IGS_GE_GEN_004.GENP_GET_LOOKUP('MARKS_GRADE_CHANGE_COMMENT'
, SAI.OUTCOME_COMMENT_CODE )) COMMENTS
, SUA.EXAM_LOCATION_CD EXAM_LOCATION_CD
, SAI.PERSON_ID PERSON_ID
, SAI.COURSE_CD COURSE_CD
, SUA.UNIT_CD UNIT_CD
, UV.TITLE TITLE
, SUA.CAL_TYPE CAL_TYPE
, SUA.CI_SEQUENCE_NUMBER CI_SEQUENCE_NUMBER
, SAI.ASS_ID ASSESSMENT_ID
, UOO.LOCATION_CD LOCATION_CD
, UOO.UOO_ID UOO_ID
, IGS_AS_ANON_GRD_PKG.CHK_ANON_GRADED(UOO.UOO_ID
, SAI.ASS_ID) CHK_ANON_GRADED
, DECODE (SAI.UNIT_SECTION_ASS_ITEM_ID
, NULL
, (SELECT UAI.REFERENCE
FROM IGS_AS_UNITASS_ITEM_ALL UAI
WHERE UAI.UNIT_ASS_ITEM_ID = SAI.UNIT_ASS_ITEM_ID)
, (SELECT USAI.REFERENCE
FROM IGS_PS_UNITASS_ITEM USAI
WHERE USAI.UNIT_SECTION_ASS_ITEM_ID = SAI.UNIT_SECTION_ASS_ITEM_ID)) REFERENCE
, DECODE (SAI.UNIT_SECTION_ASS_ITEM_ID
, NULL
, (SELECT UAI.DUE_DT
FROM IGS_AS_UNITASS_ITEM_ALL UAI
WHERE UAI.UNIT_ASS_ITEM_ID = SAI.UNIT_ASS_ITEM_ID)
, (SELECT USAI.DUE_DT
FROM IGS_PS_UNITASS_ITEM USAI
WHERE USAI.UNIT_SECTION_ASS_ITEM_ID = SAI.UNIT_SECTION_ASS_ITEM_ID)) DUE_DT
, SAI.WAIVED_FLAG WAIVED_FLAG
, SAI.OVERRIDE_DUE_DT OVERRIDE_DUE_DT
, SAI.SUBMITTED_DATE SUBMITTED_DATE
, SAI.PENALTY_APPLIED_FLAG PENALTY_APPLIED_FLAG
, UOO.UNIT_CLASS UNIT_CLASS
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
, IGS_PS_UNIT_VER_ALL UV
WHERE HZ.PARTY_ID = SAI.PERSON_ID
AND SAI.PERSON_ID = SUA.PERSON_ID
AND SAI.UNIT_CD = SUA.UNIT_CD
AND SUA.UNIT_CD = UV.UNIT_CD
AND SUA.VERSION_NUMBER = UV.VERSION_NUMBER
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 (IGS_AS_ANON_GRD_PKG.CHK_ANON_GRADED(UOO.UOO_ID
, SAI.ASS_ID) = 'Y')
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