eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

[Home] [Help]

View: IGS_AS_PROGUGAI_ADI_V

Product:	IGS - Student System (Obsolete)
Description:	Assessment Progression/UnitGrade/AssessmentItem WebADI View
Implementation/DBA Data:	Not implemented in this database

View Text

SELECT HP.PARTY_NUMBER PERSON_NUMBER
, HP.PERSON_LAST_NAME || '
, ' || HP.PERSON_FIRST_NAME || ' ' || HP.PERSON_MIDDLE_NAME PERSON_NAME
, AIP.ANONYMOUS_ID ANONYMOUS_ID
, PV.COURSE_CD COURSE_CD
, PV.COURSE_TYPE COURSE_TYPE
, PV.RESPONSIBLE_ORG_UNIT_CD RESPONSIBLE_ORG_UNIT_CD
, SUSA.UNIT_SET_CD UNIT_SET_CD
, CI.ALTERNATE_CODE PRG_ALTERNATE_CD
, CI.CAL_TYPE PRG_CAL_TYPE
, CI.SEQUENCE_NUMBER PRG_CI_SEQUENCE_NUMBER
, NULL PROGRESSION_OUTCOME_TYPE
, HES.GRAD_SCH_GRADE YOP_GRADE
, HES.MARK YOP_MARK
, '-' UNIT_CD
, TO_NUMBER (NULL) SUA_VERSION_NUMBE
, NULL TEACH_CAL_TYPE
, TO_NUMBER (NULL) TEACH_CI_SEQUENCE_NUMBER
, TO_NUMBER (NULL) UOO_ID
, NULL LOCATION_CD
, '-' GRADING_PERIOD_CD
, NULL GRADING_SCHEMA_CD
, TO_NUMBER (NULL) GS_VERSION_NUMBER
, NULL UNIT_GRADE
, TO_NUMBER (NULL) UNIT_MARK
, NULL FINALISED_OUTCOME_INDICATOR
, TO_NUMBER (NULL) ASSESSMENT_ID
, NULL ASSESSMENT_TYPE
, NULL AI_GRADING_SCHEMA_CD
, TO_NUMBER (NULL) AI_GS_VERSION_NUMBER
, TO_NUMBER (NULL) AI_MARK
, NULL AI_GRADE
, 'PRG' RECORD_TYPE
, NULL UNIT_CLASS
, NULL REFERENCE
FROM IGS_EN_STDNT_PS_ATT_SV SPA
, IGS_PS_VER_ALL PV
, HZ_PARTIES HP
, IGS_AS_SU_SETATMPT SUSA
, IGS_CA_INST_ALL CI
, IGS_CA_TYPE CT
, IGS_HE_EN_SUSA HES
, IGS_AS_ANON_ID_PS AIP
WHERE HP.PARTY_ID = SPA.PERSON_ID
AND SPA.COURSE_CD = PV.COURSE_CD
AND SPA.VERSION_NUMBER = PV.VERSION_NUMBER
AND SPA.COURSE_ATTEMPT_STATUS IN ('ENROLLED'
, 'INACTIVE')
AND SUSA.PERSON_ID = SPA.PERSON_ID
AND SUSA.COURSE_CD = SPA.COURSE_CD
AND SUSA.END_DT IS NULL
AND SUSA.RQRMNTS_COMPLETE_DT IS NULL
AND SUSA.SELECTION_DT IS NOT NULL
AND SUSA.PERSON_ID = HES.PERSON_ID(+)
AND SUSA.COURSE_CD = HES.COURSE_CD(+)
AND SUSA.UNIT_SET_CD = HES.UNIT_SET_CD(+)
AND SUSA.SEQUENCE_NUMBER = HES.SEQUENCE_NUMBER(+)
AND AIP.PERSON_ID(+) = SPA.PERSON_ID
AND AIP.COURSE_CD(+) = SPA.COURSE_CD
AND CI.CAL_TYPE = CT.CAL_TYPE
AND CT.S_CAL_CAT = 'PROGRESS'
AND EXISTS ( SELECT 'X'
FROM IGS_EN_SU_ATTEMPT_ALL SUA
, IGS_CA_INST_REL CIR
WHERE SUA.PERSON_ID = SPA.PERSON_ID
AND SUA.COURSE_CD = SPA.COURSE_CD
AND SUA.CAL_TYPE = CIR.SUB_CAL_TYPE
AND SUA.CI_SEQUENCE_NUMBER = CIR.SUB_CI_SEQUENCE_NUMBER
AND CI.CAL_TYPE = CIR.SUP_CAL_TYPE
AND CI.SEQUENCE_NUMBER = CIR.SUP_CI_SEQUENCE_NUMBER
AND SUA.UNIT_ATTEMPT_STATUS IN ('ENROLLED'
, 'COMPLETED'))
AND NOT EXISTS ( SELECT 'X'
FROM IGS_PR_STDNT_PR_OU_ALL SPO
, IGS_PR_OU_TYPE_ALL POT
WHERE SPO.PERSON_ID = SPA.PERSON_ID
AND SPO.COURSE_CD = SPA.COURSE_CD
AND SPO.PRG_CAL_TYPE = CI.CAL_TYPE
AND SPO.PRG_CI_SEQUENCE_NUMBER = CI.SEQUENCE_NUMBER
AND SPO.PROGRESSION_OUTCOME_TYPE = POT.PROGRESSION_OUTCOME_TYPE
AND (POT.S_PROGRESSION_OUTCOME_TYPE IN ('NOPENALTY'
, 'ADVANCE'
, 'REPEATYR'
, 'MANUAL'
, 'EXCLUSION'
, 'EXPULSION' ) )) UNION ALL SELECT HP.PARTY_NUMBER PERSON_NUMBER
, HP.PERSON_LAST_NAME || '
, ' || HP.PERSON_FIRST_NAME || ' ' || HP.PERSON_MIDDLE_NAME PERSON_NAME
, AIP.ANONYMOUS_ID ANONYMOUS_ID
, PV.COURSE_CD COURSE_CD
, PV.COURSE_TYPE COURSE_TYPE
, PV.RESPONSIBLE_ORG_UNIT_CD RESPONSIBLE_ORG_UNIT_CD
, SUSA.UNIT_SET_CD UNIT_SET_CD
, CI.ALTERNATE_CODE PRG_ALTERNATE_CD
, CI.CAL_TYPE PRG_CAL_TYPE
, CI.SEQUENCE_NUMBER PRG_CI_SEQUENCE_NUMBER
, SPO.PROGRESSION_OUTCOME_TYPE PROGRESSION_OUTCOME_TYPE
, HES.GRAD_SCH_GRADE YOP_GRADE
, HES.MARK YOP_MARK
, '-' UNIT_CD
, TO_NUMBER (NULL) SUA_VERSION_NUMBE
, NULL TEACH_CAL_TYPE
, TO_NUMBER (NULL) TEACH_CI_SEQUENCE_NUMBER
, TO_NUMBER (NULL) UOO_ID
, NULL LOCATION_CD
, '-' GRADING_PERIOD_CD
, NULL GRADING_SCHEMA_CD
, TO_NUMBER (NULL) GS_VERSION_NUMBER
, NULL UNIT_GRADE
, TO_NUMBER (NULL) UNIT_MARK
, NULL FINALISED_OUTCOME_INDICATOR
, TO_NUMBER (NULL) ASSESSMENT_ID
, NULL ASSESSMENT_TYPE
, NULL AI_GRADING_SCHEMA_CD
, TO_NUMBER (NULL) AI_GS_VERSION_NUMBER
, TO_NUMBER (NULL) AI_MARK
, NULL AI_GRADE
, 'PRG' RECORD_TYPE
, NULL UNIT_CLASS
, NULL REFERENCE
FROM IGS_EN_STDNT_PS_ATT_SV SPA
, IGS_PS_VER_ALL PV
, HZ_PARTIES HP
, IGS_AS_SU_SETATMPT SUSA
, IGS_CA_INST_ALL CI
, IGS_PR_STDNT_PR_OU_ALL SPO
, IGS_PR_OU_TYPE_ALL POT
, IGS_HE_EN_SUSA HES
, IGS_AS_ANON_ID_PS AIP
WHERE HP.PARTY_ID = SPA.PERSON_ID
AND SPA.COURSE_CD = PV.COURSE_CD
AND SPA.VERSION_NUMBER = PV.VERSION_NUMBER
AND SPA.COURSE_ATTEMPT_STATUS IN ('ENROLLED'
, 'INACTIVE')
AND SUSA.PERSON_ID = SPA.PERSON_ID
AND SUSA.COURSE_CD = SPA.COURSE_CD
AND SUSA.END_DT IS NULL
AND SUSA.RQRMNTS_COMPLETE_DT IS NULL
AND SUSA.SELECTION_DT IS NOT NULL
AND AIP.PERSON_ID(+) = SPA.PERSON_ID
AND AIP.COURSE_CD(+) = SPA.COURSE_CD
AND SUSA.PERSON_ID = HES.PERSON_ID(+)
AND SUSA.COURSE_CD = HES.COURSE_CD(+)
AND SUSA.UNIT_SET_CD = HES.UNIT_SET_CD(+)
AND SUSA.SEQUENCE_NUMBER = HES.SEQUENCE_NUMBER(+)
AND SPO.PERSON_ID = SPA.PERSON_ID
AND SPO.COURSE_CD = SPA.COURSE_CD
AND SPO.PRG_CAL_TYPE = CI.CAL_TYPE
AND SPO.PRG_CI_SEQUENCE_NUMBER = CI.SEQUENCE_NUMBER
AND SPO.DECISION_STATUS <> 'REMOVED'
AND SPO.PROGRESSION_OUTCOME_TYPE = POT.PROGRESSION_OUTCOME_TYPE
AND POT.S_PROGRESSION_OUTCOME_TYPE IN ('NOPENALTY'
, 'ADVANCE'
, 'REPEATYR'
, 'MANUAL'
, 'EXCLUSION'
, 'EXPULSION') UNION ALL SELECT HP.PARTY_NUMBER PERSON_NUMBER
, HP.PERSON_LAST_NAME || '
, ' || HP.PERSON_FIRST_NAME || ' ' || HP.PERSON_MIDDLE_NAME PERSON_NAME
, AIP.ANONYMOUS_ID ANONYMOUS_ID
, PV.COURSE_CD COURSE_CD
, PV.COURSE_TYPE COURSE_TYPE
, PV.RESPONSIBLE_ORG_UNIT_CD RESPONSIBLE_ORG_UNIT_CD
, SUSA.UNIT_SET_CD UNIT_SET_CD
, NULL PRG_ALTERNATE_CD
, NULL PRG_CAL_TYPE
, TO_NUMBER (NULL) PRG_CI_SEQUENCE_NUMBER
, '-' PROGRESSION_OUTCOME_TYPE
, NULL YOP_GRADE
, TO_NUMBER (NULL) YOP_MARK
, SUA.UNIT_CD UNIT_CD
, SUA.VERSION_NUMBER SUA_VERSION_NUMBE
, SUA.CAL_TYPE TEACH_CAL_TYPE
, SUA.CI_SEQUENCE_NUMBER TEACH_CI_SEQUENCE_NUMBER
, SUA.UOO_ID UOO_ID
, SUA.LOCATION_CD LOCATION_CD
, SUAO.GRADING_PERIOD_CD GRADING_PERIOD_CD
, SUAO.GRADING_SCHEMA_CD UNIT_GRADING_SCHEMA_CD
, SUAO.VERSION_NUMBER UNIT_GS_VERSION_NUMBER
, SUAO.GRADE UNIT_GRADE
, SUAO.MARK UNIT_MARK
, SUAO.FINALISED_OUTCOME_IND FINALISED_OUTCOME_INDICATOR
, TO_NUMBER (NULL) ASSESSMENT_ID
, NULL ASSESSMENT_TYPE
, NULL AI_GRADING_SCHEMA_CD
, TO_NUMBER (NULL) AI_GS_VERSION_NUMBER
, TO_NUMBER (NULL) AI_MARK
, NULL AI_GRADE
, 'UNIT' RECORD_TYPE
, SUA.UNIT_CLASS UNIT_CLASS
, NULL REFERENCE
FROM IGS_EN_SU_ATTEMPT_SV SUA
, IGS_AS_SU_STMPTOUT_ALL SUAO
, IGS_EN_STDNT_PS_ATT_ALL SPA
, IGS_PS_VER_ALL PV
, HZ_PARTIES HP
, IGS_AS_SU_SETATMPT SUSA
, IGS_HE_EN_SUSA HES
, IGS_AS_ANON_ID_PS AIP
, IGS_EN_UNIT_SET US
, IGS_EN_UNIT_SET_CAT USC
WHERE HP.PARTY_ID = SPA.PERSON_ID
AND SPA.COURSE_CD = PV.COURSE_CD
AND SPA.VERSION_NUMBER = PV.VERSION_NUMBER
AND SPA.COURSE_ATTEMPT_STATUS IN ('ENROLLED'
, 'INACTIVE')
AND AIP.PERSON_ID(+) = SPA.PERSON_ID
AND AIP.COURSE_CD(+) = SPA.COURSE_CD
AND SUA.PERSON_ID = SPA.PERSON_ID
AND SUA.COURSE_CD = SPA.COURSE_CD
AND SPA.PERSON_ID = SUSA.PERSON_ID
AND SPA.COURSE_CD = SUSA.COURSE_CD
AND (IGS_EN_GEN_015.GET_EFFECTIVE_CENSUS_DATE ( NULL
, NULL
, SUA.CAL_TYPE
, SUA.CI_SEQUENCE_NUMBER ) BETWEEN SUSA.SELECTION_DT
AND NVL ( SUSA.RQRMNTS_COMPLETE_DT
, NVL ( SUSA.END_DT
, FND_DATE.CANONICAL_TO_DATE ('4000/12/31') ) ) )
AND SUSA.UNIT_SET_CD = US.UNIT_SET_CD
AND SUSA.US_VERSION_NUMBER = US.VERSION_NUMBER
AND US.UNIT_SET_CAT = USC.UNIT_SET_CAT
AND USC.S_UNIT_SET_CAT = 'PRENRL_YR'
AND SUA.PERSON_ID = SUAO.PERSON_ID(+)
AND SUA.COURSE_CD = SUAO.COURSE_CD(+)
AND SUA.UOO_ID = SUAO.UOO_ID(+)
AND SUAO.GRADING_PERIOD_CD(+) <> 'MIDTERM'
AND ( SUAO.OUTCOME_DT IS NULL OR SUAO.OUTCOME_DT IN (SELECT MAX (SUAO.OUTCOME_DT)
FROM IGS_AS_SU_STMPTOUT_ALL SUAO
WHERE SUAO.PERSON_ID = SUA.PERSON_ID
AND SUAO.COURSE_CD = SUA.COURSE_CD
AND SUAO.UOO_ID = SUA.UOO_ID
AND SUAO.GRADING_PERIOD_CD <> 'MIDTERM') )
AND SUSA.PERSON_ID = HES.PERSON_ID(+)
AND SUSA.COURSE_CD = HES.COURSE_CD(+)
AND SUSA.UNIT_SET_CD = HES.UNIT_SET_CD(+)
AND SUSA.SEQUENCE_NUMBER = HES.SEQUENCE_NUMBER(+)
AND ( SUA.UNIT_ATTEMPT_STATUS IN ('ENROLLED'
, 'COMPLETED') OR ( SUA.UNIT_ATTEMPT_STATUS = 'DISCONTIN'
AND EXISTS ( SELECT GSG.GRADING_SCHEMA_CD
, GSG.VERSION_NUMBER
, GSG.GRADE
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 SUSA.END_DT IS NULL
AND SUSA.RQRMNTS_COMPLETE_DT IS NULL
AND SUSA.SELECTION_DT IS NOT NULL UNION ALL SELECT HP.PARTY_NUMBER PERSON_NUMBER
, HP.PERSON_LAST_NAME || '
, ' || HP.PERSON_FIRST_NAME || ' ' || HP.PERSON_MIDDLE_NAME PERSON_NAME
, AIP.ANONYMOUS_ID ANONYMOUS_ID
, PV.COURSE_CD COURSE_CD
, PV.COURSE_TYPE COURSE_TYPE
, PV.RESPONSIBLE_ORG_UNIT_CD RESPONSIBLE_ORG_UNIT_CD
, SUSA.UNIT_SET_CD UNIT_SET_CD
, NULL PRG_ALTERNATE_CD
, NULL PRG_CAL_TYPE
, TO_NUMBER (NULL) PRG_CI_SEQUENCE_NUMBER
, '-' PROGRESSION_OUTCOME_TYPE
, NULL YOP_GRADE
, TO_NUMBER (NULL) YOP_MARK
, SUA.UNIT_CD UNIT_CD
, SUA.VERSION_NUMBER SUA_VERSION_NUMBE
, SUA.CAL_TYPE TEACH_CAL_TYPE
, SUA.CI_SEQUENCE_NUMBER TEACH_CI_SEQUENCE_NUMBER
, SUA.UOO_ID UOO_ID
, SUA.LOCATION_CD LOCATION_CD
, '-' GRADING_PERIOD_CD
, NULL UNIT_GRADING_SCHEMA_CD
, TO_NUMBER (NULL) UNIT_GS_VERSION_NUMBER
, NULL UNIT_GRADE
, TO_NUMBER (NULL) UNIT_MARK
, NULL FINALISED_OUTCOME_INDICATOR
, SAI.ASS_ID ASSESSMENT_ID
, AIA.ASSESSMENT_TYPE ASSESSMENT_TYPE
, SAI.GRADING_SCHEMA_CD AI_GRADING_SCHEMA_CD
, SAI.GS_VERSION_NUMBER AI_GS_VERSION_NUMBER
, SAI.MARK AI_MARK
, SAI.GRADE AI_GRADE
, 'AI' RECORD_TYPE
, SUA.UNIT_CLASS UNIT_CLASS
, IGS_AS_GEN_003.ASSP_GET_AI_REF ( SAI.UNIT_SECTION_ASS_ITEM_ID
, SAI.UNIT_ASS_ITEM_ID ) REFERENCE
FROM IGS_EN_SU_ATTEMPT_SV SUA
, IGS_AS_SU_SETATMPT SUSA
, IGS_EN_UNIT_SET US
, IGS_EN_UNIT_SET_CAT USC
, IGS_EN_STDNT_PS_ATT_ALL SPA
, IGS_PS_VER_ALL PV
, HZ_PARTIES HP
, IGS_HE_EN_SUSA HES
, IGS_AS_ANON_ID_PS AIP
, IGS_AS_SU_ATMPT_ITM SAI
, IGS_AS_ASSESSMNT_ITM_ALL AIA
WHERE HP.PARTY_ID = SPA.PERSON_ID
AND SPA.COURSE_CD = PV.COURSE_CD
AND SPA.VERSION_NUMBER = PV.VERSION_NUMBER
AND SPA.COURSE_ATTEMPT_STATUS IN ('ENROLLED'
, 'INACTIVE')
AND AIP.PERSON_ID(+) = SPA.PERSON_ID
AND AIP.COURSE_CD(+) = SPA.COURSE_CD
AND SUA.PERSON_ID = SPA.PERSON_ID
AND SUA.COURSE_CD = SPA.COURSE_CD
AND SPA.PERSON_ID = SUSA.PERSON_ID
AND SPA.COURSE_CD = SUSA.COURSE_CD
AND (IGS_EN_GEN_015.GET_EFFECTIVE_CENSUS_DATE ( NULL
, NULL
, SUA.CAL_TYPE
, SUA.CI_SEQUENCE_NUMBER ) BETWEEN SUSA.SELECTION_DT
AND NVL ( SUSA.RQRMNTS_COMPLETE_DT
, NVL ( SUSA.END_DT
, FND_DATE.CANONICAL_TO_DATE ('4000/12/31') ) ) )
AND SUSA.UNIT_SET_CD = US.UNIT_SET_CD
AND SUSA.US_VERSION_NUMBER = US.VERSION_NUMBER
AND US.UNIT_SET_CAT = USC.UNIT_SET_CAT
AND USC.S_UNIT_SET_CAT = 'PRENRL_YR'
AND SUA.UNIT_ATTEMPT_STATUS IN ('ENROLLED'
, 'COMPLETED')
AND SAI.PERSON_ID = SUA.PERSON_ID
AND SAI.UOO_ID = SUA.UOO_ID
AND SAI.COURSE_CD = SUA.COURSE_CD
AND SAI.ASS_ID = AIA.ASS_ID
AND SUSA.PERSON_ID = HES.PERSON_ID(+)
AND SUSA.COURSE_CD = HES.COURSE_CD(+)
AND SUSA.UNIT_SET_CD = HES.UNIT_SET_CD(+)
AND SUSA.SEQUENCE_NUMBER = HES.SEQUENCE_NUMBER(+)
AND SAI.LOGICAL_DELETE_DT IS NULL
AND SUSA.END_DT IS NULL
AND SUSA.RQRMNTS_COMPLETE_DT IS NULL
AND SUSA.SELECTION_DT IS NOT NULL

Columns

Name
PERSON_NUMBER
PERSON_NAME
ANONYMOUS_ID
COURSE_CD
COURSE_TYPE
RESPONSIBLE_ORG_UNIT_CD
UNIT_SET_CD
PRG_ALTERNATE_CD
PRG_CAL_TYPE
PRG_CI_SEQUENCE_NUMBER
PROGRESSION_OUTCOME_TYPE
YOP_GRADE
YOP_MARK
UNIT_CD
SUA_VERSION_NUMBE
TEACH_CAL_TYPE
TEACH_CI_SEQUENCE_NUMBER
UOO_ID
LOCATION_CD
GRADING_PERIOD_CD
GRADING_SCHEMA_CD
GS_VERSION_NUMBER
UNIT_GRADE
UNIT_MARK
FINALISED_OUTCOME_INDICATOR
ASSESSMENT_ID
ASSESSMENT_TYPE
AI_GRADING_SCHEMA_CD
AI_GS_VERSION_NUMBER
AI_MARK
AI_GRADE
RECORD_TYPE
UNIT_CLASS
REFERENCE