VIEW: APPS.IGS_PS_TYPE_HIST_V

Source

View Text - Preformatted

SELECT cth1.course_type, cth1.hist_start_dt, cth1.hist_end_dt, cth1.hist_who, NVL( cth1.description, NVL( SUBSTR( IGS_AU_GEN_001.audp_get_cth_col( 'DESCRIPTION', cth1.course_type, cth1.hist_end_dt), 1, 60), ct1.description)), NVL( cth1.GOVT_COURSE_TYPE, NVL( TO_NUMBER(IGS_AU_GEN_001.audp_get_cth_col( 'GOVT_COURSE_TYPE', cth1.course_type, cth1.hist_end_dt)), ct1.GOVT_COURSE_TYPE)), NVL( cth1.award_course_ind, NVL( SUBSTR( IGS_AU_GEN_001.audp_get_cth_col( 'AWARD_COURSE_IND', cth1.course_type, cth1.hist_end_dt), 1, 1), ct1.award_course_ind)), NVL( cth1.course_type_group_cd, NVL( SUBSTR( IGS_AU_GEN_001.audp_get_cth_col( 'COURSE_TYPE_GROUP_CD', cth1.course_type, cth1.hist_end_dt), 1, 10), ct1.course_type_group_cd)), NVL( cth1.tac_course_level, NVL( SUBSTR( IGS_AU_GEN_001.audp_get_cth_col( 'TAC_COURSE_LEVEL', cth1.course_type, cth1.hist_end_dt), 1, 4), ct1.tac_course_level)), NVL( cth1.research_type_ind, NVL( SUBSTR( IGS_AU_GEN_001.audp_get_cth_col( 'RESEARCH_TYPE_IND', cth1.course_type, cth1.hist_end_dt), 1, 1), ct1.research_type_ind)), NVL( cth1.closed_ind, NVL( SUBSTR( IGS_AU_GEN_001.audp_get_cth_col( 'CLOSED_IND', cth1.course_type, cth1.hist_end_dt), 1, 1), ct1.closed_ind)), NVL( cth1.primary_auto_select, NVL( SUBSTR( IGS_AU_GEN_001.audp_get_cth_col( 'PRIMARY_AUTO_SELECT', cth1.course_type, cth1.hist_end_dt), 1, 1), ct1.primary_auto_select)), cth1.created_by, cth1.creation_date, cth1.last_updated_by, cth1.last_update_date, cth1.last_update_login, NVL( cth1.FIN_AID_PROGRAM_TYPE, NVL( SUBSTR( IGS_AU_GEN_001.audp_get_cth_col( 'FIN_AID_PROGRAM_TYPE', cth1.course_type, cth1.hist_end_dt), 1, 1), ct1.FIN_AID_PROGRAM_TYPE)), lv1.MEANING FROM IGS_PS_TYPE_HIST cth1, IGS_PS_TYPE ct1, IGS_LOOKUP_VALUES lv1 WHERE ct1.course_type = cth1.course_type AND cth1.FIN_AID_PROGRAM_TYPE = lv1.lookup_code (+) AND lv1.lookup_type (+) = 'IGS_PS_FA_PROGRAM_TYPE' UNION ALL SELECT ct2.course_type, NVL( MAX(cth2.hist_end_dt), ct2.last_update_date), TO_DATE(NULL), ct2.last_updated_by, ct2.description, ct2.GOVT_COURSE_TYPE, ct2.award_course_ind, ct2.course_type_group_cd, ct2.tac_course_level, ct2.research_type_ind, ct2.closed_ind, ct2.primary_auto_select, ct2.created_by, ct2.creation_date, ct2.last_updated_by, ct2.last_update_date, ct2.last_update_login, ct2.FIN_AID_PROGRAM_TYPE, lv2.MEANING FROM IGS_PS_TYPE_HIST cth2, IGS_PS_TYPE ct2, IGS_LOOKUP_VALUES lv2 WHERE ct2.course_type = cth2.course_type (+) AND ct2.FIN_AID_PROGRAM_TYPE = lv2.lookup_code (+) AND lv2.lookup_type (+) = 'IGS_PS_FA_PROGRAM_TYPE' GROUP BY ct2.course_type, ct2.description, ct2.GOVT_COURSE_TYPE, ct2.award_course_ind, ct2.course_type_group_cd, ct2.tac_course_level, ct2.research_type_ind, ct2.closed_ind, ct2.primary_auto_select, ct2.created_by, ct2.creation_date, ct2.last_updated_by, ct2.last_update_date, ct2.last_update_login, ct2.FIN_AID_PROGRAM_TYPE, lv2.MEANING

View Text - HTML Formatted

SELECT CTH1.COURSE_TYPE
, CTH1.HIST_START_DT
, CTH1.HIST_END_DT
, CTH1.HIST_WHO
, NVL( CTH1.DESCRIPTION
, NVL( SUBSTR( IGS_AU_GEN_001.AUDP_GET_CTH_COL( 'DESCRIPTION'
, CTH1.COURSE_TYPE
, CTH1.HIST_END_DT)
, 1
, 60)
, CT1.DESCRIPTION))
, NVL( CTH1.GOVT_COURSE_TYPE
, NVL( TO_NUMBER(IGS_AU_GEN_001.AUDP_GET_CTH_COL( 'GOVT_COURSE_TYPE'
, CTH1.COURSE_TYPE
, CTH1.HIST_END_DT))
, CT1.GOVT_COURSE_TYPE))
, NVL( CTH1.AWARD_COURSE_IND
, NVL( SUBSTR( IGS_AU_GEN_001.AUDP_GET_CTH_COL( 'AWARD_COURSE_IND'
, CTH1.COURSE_TYPE
, CTH1.HIST_END_DT)
, 1
, 1)
, CT1.AWARD_COURSE_IND))
, NVL( CTH1.COURSE_TYPE_GROUP_CD
, NVL( SUBSTR( IGS_AU_GEN_001.AUDP_GET_CTH_COL( 'COURSE_TYPE_GROUP_CD'
, CTH1.COURSE_TYPE
, CTH1.HIST_END_DT)
, 1
, 10)
, CT1.COURSE_TYPE_GROUP_CD))
, NVL( CTH1.TAC_COURSE_LEVEL
, NVL( SUBSTR( IGS_AU_GEN_001.AUDP_GET_CTH_COL( 'TAC_COURSE_LEVEL'
, CTH1.COURSE_TYPE
, CTH1.HIST_END_DT)
, 1
, 4)
, CT1.TAC_COURSE_LEVEL))
, NVL( CTH1.RESEARCH_TYPE_IND
, NVL( SUBSTR( IGS_AU_GEN_001.AUDP_GET_CTH_COL( 'RESEARCH_TYPE_IND'
, CTH1.COURSE_TYPE
, CTH1.HIST_END_DT)
, 1
, 1)
, CT1.RESEARCH_TYPE_IND))
, NVL( CTH1.CLOSED_IND
, NVL( SUBSTR( IGS_AU_GEN_001.AUDP_GET_CTH_COL( 'CLOSED_IND'
, CTH1.COURSE_TYPE
, CTH1.HIST_END_DT)
, 1
, 1)
, CT1.CLOSED_IND))
, NVL( CTH1.PRIMARY_AUTO_SELECT
, NVL( SUBSTR( IGS_AU_GEN_001.AUDP_GET_CTH_COL( 'PRIMARY_AUTO_SELECT'
, CTH1.COURSE_TYPE
, CTH1.HIST_END_DT)
, 1
, 1)
, CT1.PRIMARY_AUTO_SELECT))
, CTH1.CREATED_BY
, CTH1.CREATION_DATE
, CTH1.LAST_UPDATED_BY
, CTH1.LAST_UPDATE_DATE
, CTH1.LAST_UPDATE_LOGIN
, NVL( CTH1.FIN_AID_PROGRAM_TYPE
, NVL( SUBSTR( IGS_AU_GEN_001.AUDP_GET_CTH_COL( 'FIN_AID_PROGRAM_TYPE'
, CTH1.COURSE_TYPE
, CTH1.HIST_END_DT)
, 1
, 1)
, CT1.FIN_AID_PROGRAM_TYPE))
, LV1.MEANING
FROM IGS_PS_TYPE_HIST CTH1
, IGS_PS_TYPE CT1
, IGS_LOOKUP_VALUES LV1
WHERE CT1.COURSE_TYPE = CTH1.COURSE_TYPE
AND CTH1.FIN_AID_PROGRAM_TYPE = LV1.LOOKUP_CODE (+)
AND LV1.LOOKUP_TYPE (+) = 'IGS_PS_FA_PROGRAM_TYPE' UNION ALL SELECT CT2.COURSE_TYPE
, NVL( MAX(CTH2.HIST_END_DT)
, CT2.LAST_UPDATE_DATE)
, TO_DATE(NULL)
, CT2.LAST_UPDATED_BY
, CT2.DESCRIPTION
, CT2.GOVT_COURSE_TYPE
, CT2.AWARD_COURSE_IND
, CT2.COURSE_TYPE_GROUP_CD
, CT2.TAC_COURSE_LEVEL
, CT2.RESEARCH_TYPE_IND
, CT2.CLOSED_IND
, CT2.PRIMARY_AUTO_SELECT
, CT2.CREATED_BY
, CT2.CREATION_DATE
, CT2.LAST_UPDATED_BY
, CT2.LAST_UPDATE_DATE
, CT2.LAST_UPDATE_LOGIN
, CT2.FIN_AID_PROGRAM_TYPE
, LV2.MEANING
FROM IGS_PS_TYPE_HIST CTH2
, IGS_PS_TYPE CT2
, IGS_LOOKUP_VALUES LV2
WHERE CT2.COURSE_TYPE = CTH2.COURSE_TYPE (+)
AND CT2.FIN_AID_PROGRAM_TYPE = LV2.LOOKUP_CODE (+)
AND LV2.LOOKUP_TYPE (+) = 'IGS_PS_FA_PROGRAM_TYPE' GROUP BY CT2.COURSE_TYPE
, CT2.DESCRIPTION
, CT2.GOVT_COURSE_TYPE
, CT2.AWARD_COURSE_IND
, CT2.COURSE_TYPE_GROUP_CD
, CT2.TAC_COURSE_LEVEL
, CT2.RESEARCH_TYPE_IND
, CT2.CLOSED_IND
, CT2.PRIMARY_AUTO_SELECT
, CT2.CREATED_BY
, CT2.CREATION_DATE
, CT2.LAST_UPDATED_BY
, CT2.LAST_UPDATE_DATE
, CT2.LAST_UPDATE_LOGIN
, CT2.FIN_AID_PROGRAM_TYPE
, LV2.MEANING