VIEW: APPS.IGS_AS_SU_STMPTOUT_V

Source

View Text - Preformatted

SELECT SS.ROWID ROW_ID, PDV.PERSON_NUMBER PERSON_NUMBER, SS.PERSON_ID PERSON_ID, SS.COURSE_CD COURSE_CD, SS.UNIT_CD UNIT_CD, SS.CAL_TYPE CAL_TYPE, SS.CI_SEQUENCE_NUMBER CI_SEQUENCE_NUMBER, SS.CI_START_DT CI_START_DT, SS.CI_END_DT CI_END_DT, SS.OUTCOME_DT OUTCOME_DT, SS.GRADING_SCHEMA_CD GRADING_SCHEMA_CD, SS.VERSION_NUMBER VERSION_NUMBER, SS.GRADE GRADE, SS.S_GRADE_CREATION_METHOD_TYPE S_GRADE_CREATION_METHOD_TYPE, SS.FINALISED_OUTCOME_IND FINALISED_OUTCOME_IND, SS.MARK MARK, SS.NUMBER_TIMES_KEYED NUMBER_TIMES_KEYED, SS.TRANSLATED_GRADING_SCHEMA_CD TRANSLATED_GRADING_SCHEMA_CD, SS.TRANSLATED_VERSION_NUMBER TRANSLATED_VERSION_NUMBER, SS.TRANSLATED_GRADE TRANSLATED_GRADE, SS.TRANSLATED_DT TRANSLATED_DT, SCA.VERSION_NUMBER DSP_VERSION_NUMBER, CRV.TITLE DSP_TITLE, PDV.FULL_NAME DSP_CONTEXT_BLOCK_NAME, ILV.MEANING DSP_SEX, PDV.BIRTH_DATE DSP_BIRTH_DT, API.API_PERSON_ID API_PERSON_ID, CI.ALTERNATE_CODE DSP_ALTERNATE_CODE, SUA.VERSION_NUMBER DSP_VERSION_NUMBER2, UV.TITLE DSP_TITLE2, SS.CREATED_BY CREATED_BY, SS.CREATION_DATE CREATION_DATE, SS.LAST_UPDATED_BY LAST_UPDATED_BY, SS.LAST_UPDATE_DATE LAST_UPDATE_DATE, SS.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN, SS.REQUEST_ID REQUEST_ID, SS.PROGRAM_APPLICATION_ID PROGRAM_APPLICATION_ID, SS.PROGRAM_ID PROGRAM_ID, SS.PROGRAM_UPDATE_DATE PROGRAM_UPDATE_DATE ,SUA.DEG_AUD_DETAIL_ID DEG_AUD_DETAIL_ID ,SS.UOO_ID ,SUA.LOCATION_CD ,SUA.UNIT_CLASS , ss.mark_capped_flag, ss.SHOW_ON_ACADEMIC_HISTRY_FLAG, ss.release_date, ss.manual_override_flag, (SELECT MAX(sub.submitted_date) FROM igs_as_gaa_sub_hist sub WHERE sub.uoo_id = ss.uoo_id AND sub.grading_period_cd = ss.grading_period_cd AND sub.submission_type = 'GRADE') submitted_date FROM igs_as_su_stmptout ss, igs_pe_person_base_v pdv, igs_pe_person_id_typ pit, igs_pe_alt_pers_id api, igs_en_stdnt_ps_att_ALL sca, igs_ps_ver crv, Fnd_lookup_values ilv, Igs_ca_inst ci, igs_en_su_attempt_sv sua, igs_ps_unit_ver uv WHERE SS.PERSON_ID = PDV.PERSON_ID AND SS.PERSON_ID = API.PE_PERSON_ID(+) AND API.PERSON_ID_TYPE = PIT.PERSON_ID_TYPE(+) AND PIT.PREFERRED_IND(+) = 'Y' AND SYSDATE BETWEEN API.START_DT(+) AND NVL(API.END_DT(+), SYSDATE) AND (SS.PERSON_ID = SCA.PERSON_ID OR (SCA.PERSON_ID IS NULL AND SS.PERSON_ID IS NULL)) AND SS.COURSE_CD = SCA.COURSE_CD AND CRV.COURSE_CD = SCA.COURSE_CD AND CRV.VERSION_NUMBER = SCA.VERSION_NUMBER AND PDV.GENDER = ILV.LOOKUP_CODE AND ILV.LOOKUP_TYPE = 'HZ_GENDER' AND ilv.language = userenv('LANG') AND ilv.view_application_id = 222 AND ilv.security_group_id = 0 AND SS.CAL_TYPE=CI.CAL_TYPE AND SS.CI_SEQUENCE_NUMBER = CI.SEQUENCE_NUMBER AND SS.CI_START_DT=CI.START_DT AND SS.CI_END_DT=CI.END_DT AND SS.PERSON_ID = SUA.PERSON_ID AND SS.COURSE_CD = SUA.COURSE_CD AND SS.UOO_ID = SUA.UOO_ID AND UV.UNIT_CD = SUA.UNIT_CD AND UV.VERSION_NUMBER = SUA.VERSION_NUMBER AND SS.GRADING_PERIOD_CD <> 'MIDTERM'

View Text - HTML Formatted

SELECT SS.ROWID ROW_ID
, PDV.PERSON_NUMBER PERSON_NUMBER
, SS.PERSON_ID PERSON_ID
, SS.COURSE_CD COURSE_CD
, SS.UNIT_CD UNIT_CD
, SS.CAL_TYPE CAL_TYPE
, SS.CI_SEQUENCE_NUMBER CI_SEQUENCE_NUMBER
, SS.CI_START_DT CI_START_DT
, SS.CI_END_DT CI_END_DT
, SS.OUTCOME_DT OUTCOME_DT
, SS.GRADING_SCHEMA_CD GRADING_SCHEMA_CD
, SS.VERSION_NUMBER VERSION_NUMBER
, SS.GRADE GRADE
, SS.S_GRADE_CREATION_METHOD_TYPE S_GRADE_CREATION_METHOD_TYPE
, SS.FINALISED_OUTCOME_IND FINALISED_OUTCOME_IND
, SS.MARK MARK
, SS.NUMBER_TIMES_KEYED NUMBER_TIMES_KEYED
, SS.TRANSLATED_GRADING_SCHEMA_CD TRANSLATED_GRADING_SCHEMA_CD
, SS.TRANSLATED_VERSION_NUMBER TRANSLATED_VERSION_NUMBER
, SS.TRANSLATED_GRADE TRANSLATED_GRADE
, SS.TRANSLATED_DT TRANSLATED_DT
, SCA.VERSION_NUMBER DSP_VERSION_NUMBER
, CRV.TITLE DSP_TITLE
, PDV.FULL_NAME DSP_CONTEXT_BLOCK_NAME
, ILV.MEANING DSP_SEX
, PDV.BIRTH_DATE DSP_BIRTH_DT
, API.API_PERSON_ID API_PERSON_ID
, CI.ALTERNATE_CODE DSP_ALTERNATE_CODE
, SUA.VERSION_NUMBER DSP_VERSION_NUMBER2
, UV.TITLE DSP_TITLE2
, SS.CREATED_BY CREATED_BY
, SS.CREATION_DATE CREATION_DATE
, SS.LAST_UPDATED_BY LAST_UPDATED_BY
, SS.LAST_UPDATE_DATE LAST_UPDATE_DATE
, SS.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN
, SS.REQUEST_ID REQUEST_ID
, SS.PROGRAM_APPLICATION_ID PROGRAM_APPLICATION_ID
, SS.PROGRAM_ID PROGRAM_ID
, SS.PROGRAM_UPDATE_DATE PROGRAM_UPDATE_DATE
, SUA.DEG_AUD_DETAIL_ID DEG_AUD_DETAIL_ID
, SS.UOO_ID
, SUA.LOCATION_CD
, SUA.UNIT_CLASS
, SS.MARK_CAPPED_FLAG
, SS.SHOW_ON_ACADEMIC_HISTRY_FLAG
, SS.RELEASE_DATE
, SS.MANUAL_OVERRIDE_FLAG
, (SELECT MAX(SUB.SUBMITTED_DATE)
FROM IGS_AS_GAA_SUB_HIST SUB
WHERE SUB.UOO_ID = SS.UOO_ID
AND SUB.GRADING_PERIOD_CD = SS.GRADING_PERIOD_CD
AND SUB.SUBMISSION_TYPE = 'GRADE') SUBMITTED_DATE
FROM IGS_AS_SU_STMPTOUT SS
, IGS_PE_PERSON_BASE_V PDV
, IGS_PE_PERSON_ID_TYP PIT
, IGS_PE_ALT_PERS_ID API
, IGS_EN_STDNT_PS_ATT_ALL SCA
, IGS_PS_VER CRV
, FND_LOOKUP_VALUES ILV
, IGS_CA_INST CI
, IGS_EN_SU_ATTEMPT_SV SUA
, IGS_PS_UNIT_VER UV
WHERE SS.PERSON_ID = PDV.PERSON_ID
AND SS.PERSON_ID = API.PE_PERSON_ID(+)
AND API.PERSON_ID_TYPE = PIT.PERSON_ID_TYPE(+)
AND PIT.PREFERRED_IND(+) = 'Y'
AND SYSDATE BETWEEN API.START_DT(+)
AND NVL(API.END_DT(+)
, SYSDATE)
AND (SS.PERSON_ID = SCA.PERSON_ID OR (SCA.PERSON_ID IS NULL
AND SS.PERSON_ID IS NULL))
AND SS.COURSE_CD = SCA.COURSE_CD
AND CRV.COURSE_CD = SCA.COURSE_CD
AND CRV.VERSION_NUMBER = SCA.VERSION_NUMBER
AND PDV.GENDER = ILV.LOOKUP_CODE
AND ILV.LOOKUP_TYPE = 'HZ_GENDER'
AND ILV.LANGUAGE = USERENV('LANG')
AND ILV.VIEW_APPLICATION_ID = 222
AND ILV.SECURITY_GROUP_ID = 0
AND SS.CAL_TYPE=CI.CAL_TYPE
AND SS.CI_SEQUENCE_NUMBER = CI.SEQUENCE_NUMBER
AND SS.CI_START_DT=CI.START_DT
AND SS.CI_END_DT=CI.END_DT
AND SS.PERSON_ID = SUA.PERSON_ID
AND SS.COURSE_CD = SUA.COURSE_CD
AND SS.UOO_ID = SUA.UOO_ID
AND UV.UNIT_CD = SUA.UNIT_CD
AND UV.VERSION_NUMBER = SUA.VERSION_NUMBER
AND SS.GRADING_PERIOD_CD <> 'MIDTERM'