VIEW: APPS.IGS_EN_SPL_PERM_H_V

Source

View Text - Preformatted

SELECT SPH1.ROWID, SPH1.SPL_PERM_REQUEST_H_ID, SPH1.SPL_PERM_REQUEST_ID, SPV1.STUDENT_PERSON_ID, SPV1.INSTRUCTOR_PERSON_ID, SPV1.UNIT_CD, SPV1.VERSION_NUMBER, SPV1.UNIT_CLASS, SPV1.CALL_NUMBER, SPV1.TITLE, SPV1.UOO_ID, SPH1.HIST_START_DT, SPH1.HIST_END_DT, SPH1.HIST_WHO, NVL(SPH1.DATE_SUBMISSION,NVL(IGS_GE_DATE.IGSDATE( IGS_AU_GEN_003.ENRP_GET_SPH_COL ('DATE_SUBMISSION',SPH1.SPL_PERM_REQUEST_H_ID,SPH1.HIST_END_DT)), SPV1.DATE_SUBMISSION)), SUBSTR(NVL(SPH1.AUDIT_THE_COURSE,NVL(IGS_AU_GEN_003.ENRP_GET_SPH_COL ('AUDIT_THE_COURSE',SPH1.SPL_PERM_REQUEST_H_ID,SPH1.HIST_END_DT), SPV1.AUDIT_THE_COURSE)),1,1), SUBSTR(NVL(SPH1.APPROVAL_STATUS,NVL(IGS_AU_GEN_003.ENRP_GET_SPH_COL ('APPROVAL_STATUS',SPH1.SPL_PERM_REQUEST_H_ID,SPH1.HIST_END_DT), SPV1.APPROVAL_STATUS)),1,1), SUBSTR(NVL(SPH1.REASON_FOR_REQUEST,NVL(IGS_AU_GEN_003.ENRP_GET_SPH_COL ('REASON_FOR_REQUEST',SPH1.SPL_PERM_REQUEST_H_ID,SPH1.HIST_END_DT), SPV1. REASON_FOR_REQUEST)),1,300), SUBSTR(NVL(SPH1.INSTRUCTOR_MORE_INFO,NVL(IGS_AU_GEN_003.ENRP_GET_SPH_COL('INSTRUCTOR_MORE_INFO',SPH1.SPL_PERM_REQUEST_H_ID,SPH1.HIST_END_DT), SPV1.INSTRUCTOR_MORE_INFO)),1,300), SUBSTR(NVL(SPH1.INSTRUCTOR_DENY_INFO,NVL(IGS_AU_GEN_003.ENRP_GET_SPH_COL('INSTRUCTOR_DENY',SPH1.SPL_PERM_REQUEST_H_ID,SPH1.HIST_END_DT), SPV1.INSTRUCTOR_DENY_INFO)),1,300), SUBSTR(NVL(SPH1.STUDENT_MORE_INFO,NVL(IGS_AU_GEN_003.ENRP_GET_SPH_COL('STUDENT_MORE_INFO', SPH1.SPL_PERM_REQUEST_H_ID,SPH1.HIST_END_DT), SPV1.STUDENT_MORE_INFO)),1,300), SUBSTR(NVL(SPH1.TRANSACTION_TYPE,NVL(IGS_AU_GEN_003.ENRP_GET_SPH_COL ('TRANSACTION_TYPE', SPH1.SPL_PERM_REQUEST_H_ID,SPH1.HIST_END_DT), SPV1.TRANSACTION_TYPE)),1,30), LKV.MEANING, SPV1.REQUEST_TYPE, SPH1.CREATED_BY, SPH1.CREATION_DATE, SPH1.LAST_UPDATED_BY, SPH1.LAST_UPDATE_DATE, SPH1.LAST_UPDATE_LOGIN FROM IGS_EN_SPL_PERM_H SPH1, IGS_EN_SPL_PERM_V SPV1, IGS_LOOKUP_VALUES LKV WHERE SPH1.SPL_PERM_REQUEST_ID = SPV1.SPL_PERM_REQUEST_ID AND SUBSTR(NVL(SPH1.TRANSACTION_TYPE,NVL(IGS_AU_GEN_003.ENRP_GET_SPH_COL ('TRANSACTION_TYPE', SPH1.SPL_PERM_REQUEST_H_ID,SPH1.HIST_END_DT), SPV1.TRANSACTION_TYPE)),1,30) = LKV.LOOKUP_CODE AND LKV.LOOKUP_TYPE = 'SPL_PERM_TRANSCTION_TYPE' UNION ALL SELECT SP2.ROW_ID, SPH2.SPL_PERM_REQUEST_H_ID, SP2.SPL_PERM_REQUEST_ID, SP2.STUDENT_PERSON_ID, SP2.INSTRUCTOR_PERSON_ID, SP2.UNIT_CD, SP2.VERSION_NUMBER, SP2.UNIT_CLASS, SP2.CALL_NUMBER, SP2.TITLE, SP2.UOO_ID, NVL(MAX(SPH2.HIST_END_DT), SP2.LAST_UPDATE_DATE), TO_DATE(NULL), SP2.LAST_UPDATED_BY, SP2.DATE_SUBMISSION, SP2.AUDIT_THE_COURSE, SP2.APPROVAL_STATUS, SP2.REASON_FOR_REQUEST, SP2.INSTRUCTOR_MORE_INFO, SP2.INSTRUCTOR_DENY_INFO, SP2.STUDENT_MORE_INFO, SP2.TRANSACTION_TYPE, SP2.TRANSACTION_TYPE_MEANING, SP2.REQUEST_TYPE, SP2.CREATED_BY, SP2.CREATION_DATE, SP2.LAST_UPDATED_BY, SP2.LAST_UPDATE_DATE, SP2.LAST_UPDATE_LOGIN FROM IGS_EN_SPL_PERM_H SPH2, IGS_EN_SPL_PERM_V SP2 WHERE SPH2.SPL_PERM_REQUEST_ID(+) = SP2.SPL_PERM_REQUEST_ID GROUP BY SP2.ROW_ID, SPH2.SPL_PERM_REQUEST_H_ID, SP2.SPL_PERM_REQUEST_ID, SP2.STUDENT_PERSON_ID, SP2.INSTRUCTOR_PERSON_ID, SP2.UNIT_CD, SP2.VERSION_NUMBER, SP2.UNIT_CLASS, SP2.CALL_NUMBER, SP2.TITLE, SP2.UOO_ID, sP2.LAST_UPDATED_BY, SP2.DATE_SUBMISSION, SP2.AUDIT_THE_COURSE, SP2.APPROVAL_STATUS, SP2.REASON_FOR_REQUEST, SP2.INSTRUCTOR_MORE_INFO, SP2.INSTRUCTOR_DENY_INFO, SP2.STUDENT_MORE_INFO, SP2.TRANSACTION_TYPE, SP2.TRANSACTION_TYPE_MEANING, SP2.REQUEST_TYPE, SP2.CREATED_BY, SP2.CREATION_DATE, SP2.LAST_UPDATED_BY, SP2.LAST_UPDATE_DATE, SP2.LAST_UPDATE_LOGIN

View Text - HTML Formatted

SELECT SPH1.ROWID
, SPH1.SPL_PERM_REQUEST_H_ID
, SPH1.SPL_PERM_REQUEST_ID
, SPV1.STUDENT_PERSON_ID
, SPV1.INSTRUCTOR_PERSON_ID
, SPV1.UNIT_CD
, SPV1.VERSION_NUMBER
, SPV1.UNIT_CLASS
, SPV1.CALL_NUMBER
, SPV1.TITLE
, SPV1.UOO_ID
, SPH1.HIST_START_DT
, SPH1.HIST_END_DT
, SPH1.HIST_WHO
, NVL(SPH1.DATE_SUBMISSION
, NVL(IGS_GE_DATE.IGSDATE( IGS_AU_GEN_003.ENRP_GET_SPH_COL ('DATE_SUBMISSION'
, SPH1.SPL_PERM_REQUEST_H_ID
, SPH1.HIST_END_DT))
, SPV1.DATE_SUBMISSION))
, SUBSTR(NVL(SPH1.AUDIT_THE_COURSE
, NVL(IGS_AU_GEN_003.ENRP_GET_SPH_COL ('AUDIT_THE_COURSE'
, SPH1.SPL_PERM_REQUEST_H_ID
, SPH1.HIST_END_DT)
, SPV1.AUDIT_THE_COURSE))
, 1
, 1)
, SUBSTR(NVL(SPH1.APPROVAL_STATUS
, NVL(IGS_AU_GEN_003.ENRP_GET_SPH_COL ('APPROVAL_STATUS'
, SPH1.SPL_PERM_REQUEST_H_ID
, SPH1.HIST_END_DT)
, SPV1.APPROVAL_STATUS))
, 1
, 1)
, SUBSTR(NVL(SPH1.REASON_FOR_REQUEST
, NVL(IGS_AU_GEN_003.ENRP_GET_SPH_COL ('REASON_FOR_REQUEST'
, SPH1.SPL_PERM_REQUEST_H_ID
, SPH1.HIST_END_DT)
, SPV1. REASON_FOR_REQUEST))
, 1
, 300)
, SUBSTR(NVL(SPH1.INSTRUCTOR_MORE_INFO
, NVL(IGS_AU_GEN_003.ENRP_GET_SPH_COL('INSTRUCTOR_MORE_INFO'
, SPH1.SPL_PERM_REQUEST_H_ID
, SPH1.HIST_END_DT)
, SPV1.INSTRUCTOR_MORE_INFO))
, 1
, 300)
, SUBSTR(NVL(SPH1.INSTRUCTOR_DENY_INFO
, NVL(IGS_AU_GEN_003.ENRP_GET_SPH_COL('INSTRUCTOR_DENY'
, SPH1.SPL_PERM_REQUEST_H_ID
, SPH1.HIST_END_DT)
, SPV1.INSTRUCTOR_DENY_INFO))
, 1
, 300)
, SUBSTR(NVL(SPH1.STUDENT_MORE_INFO
, NVL(IGS_AU_GEN_003.ENRP_GET_SPH_COL('STUDENT_MORE_INFO'
, SPH1.SPL_PERM_REQUEST_H_ID
, SPH1.HIST_END_DT)
, SPV1.STUDENT_MORE_INFO))
, 1
, 300)
, SUBSTR(NVL(SPH1.TRANSACTION_TYPE
, NVL(IGS_AU_GEN_003.ENRP_GET_SPH_COL ('TRANSACTION_TYPE'
, SPH1.SPL_PERM_REQUEST_H_ID
, SPH1.HIST_END_DT)
, SPV1.TRANSACTION_TYPE))
, 1
, 30)
, LKV.MEANING
, SPV1.REQUEST_TYPE
, SPH1.CREATED_BY
, SPH1.CREATION_DATE
, SPH1.LAST_UPDATED_BY
, SPH1.LAST_UPDATE_DATE
, SPH1.LAST_UPDATE_LOGIN
FROM IGS_EN_SPL_PERM_H SPH1
, IGS_EN_SPL_PERM_V SPV1
, IGS_LOOKUP_VALUES LKV
WHERE SPH1.SPL_PERM_REQUEST_ID = SPV1.SPL_PERM_REQUEST_ID
AND SUBSTR(NVL(SPH1.TRANSACTION_TYPE
, NVL(IGS_AU_GEN_003.ENRP_GET_SPH_COL ('TRANSACTION_TYPE'
, SPH1.SPL_PERM_REQUEST_H_ID
, SPH1.HIST_END_DT)
, SPV1.TRANSACTION_TYPE))
, 1
, 30) = LKV.LOOKUP_CODE
AND LKV.LOOKUP_TYPE = 'SPL_PERM_TRANSCTION_TYPE' UNION ALL SELECT SP2.ROW_ID
, SPH2.SPL_PERM_REQUEST_H_ID
, SP2.SPL_PERM_REQUEST_ID
, SP2.STUDENT_PERSON_ID
, SP2.INSTRUCTOR_PERSON_ID
, SP2.UNIT_CD
, SP2.VERSION_NUMBER
, SP2.UNIT_CLASS
, SP2.CALL_NUMBER
, SP2.TITLE
, SP2.UOO_ID
, NVL(MAX(SPH2.HIST_END_DT)
, SP2.LAST_UPDATE_DATE)
, TO_DATE(NULL)
, SP2.LAST_UPDATED_BY
, SP2.DATE_SUBMISSION
, SP2.AUDIT_THE_COURSE
, SP2.APPROVAL_STATUS
, SP2.REASON_FOR_REQUEST
, SP2.INSTRUCTOR_MORE_INFO
, SP2.INSTRUCTOR_DENY_INFO
, SP2.STUDENT_MORE_INFO
, SP2.TRANSACTION_TYPE
, SP2.TRANSACTION_TYPE_MEANING
, SP2.REQUEST_TYPE
, SP2.CREATED_BY
, SP2.CREATION_DATE
, SP2.LAST_UPDATED_BY
, SP2.LAST_UPDATE_DATE
, SP2.LAST_UPDATE_LOGIN
FROM IGS_EN_SPL_PERM_H SPH2
, IGS_EN_SPL_PERM_V SP2
WHERE SPH2.SPL_PERM_REQUEST_ID(+) = SP2.SPL_PERM_REQUEST_ID GROUP BY SP2.ROW_ID
, SPH2.SPL_PERM_REQUEST_H_ID
, SP2.SPL_PERM_REQUEST_ID
, SP2.STUDENT_PERSON_ID
, SP2.INSTRUCTOR_PERSON_ID
, SP2.UNIT_CD
, SP2.VERSION_NUMBER
, SP2.UNIT_CLASS
, SP2.CALL_NUMBER
, SP2.TITLE
, SP2.UOO_ID
, SP2.LAST_UPDATED_BY
, SP2.DATE_SUBMISSION
, SP2.AUDIT_THE_COURSE
, SP2.APPROVAL_STATUS
, SP2.REASON_FOR_REQUEST
, SP2.INSTRUCTOR_MORE_INFO
, SP2.INSTRUCTOR_DENY_INFO
, SP2.STUDENT_MORE_INFO
, SP2.TRANSACTION_TYPE
, SP2.TRANSACTION_TYPE_MEANING
, SP2.REQUEST_TYPE
, SP2.CREATED_BY
, SP2.CREATION_DATE
, SP2.LAST_UPDATED_BY
, SP2.LAST_UPDATE_DATE
, SP2.LAST_UPDATE_LOGIN