VIEW: APPS.IGS_PS_FLD_STUDY_HIST_V

Source

View Text - Preformatted

SELECT cfosh1.course_cd, cfosh1.version_number, cfosh1.FIELD_OF_STUDY, cfosh1.hist_start_dt, cfosh1.hist_end_dt, cfosh1.hist_who, NVL( cfosh1.percentage, NVL( TO_NUMBER( IGS_AU_GEN_001.audp_get_cfosh_col( cfosh1.course_cd, cfosh1.version_number, cfosh1.FIELD_OF_STUDY, 'PERCENTAGE', cfosh1.hist_end_dt)), cfos1.percentage)), NVL( cfosh1.major_field_ind, NVL( SUBSTR( IGS_AU_GEN_001.audp_get_cfosh_col( cfosh1.course_cd, cfosh1.version_number, cfosh1.FIELD_OF_STUDY, 'MAJOR_FIELD_IND', cfosh1.hist_end_dt),1,1), cfos1.major_field_ind)), cfosh1.created_by, cfosh1.creation_date, cfosh1.last_updated_by, cfosh1.last_update_date, cfosh1.last_update_login FROM IGS_PS_FLD_STD_HIST cfosh1, IGS_PS_FIELD_STUDY cfos1, IGS_PS_VER cv1 WHERE cv1.course_cd = cfosh1.course_cd AND cv1.version_number = cfosh1.version_number AND cfosh1.course_cd = cfos1.course_cd (+) AND cfosh1.version_number = cfos1.version_number (+) AND cfosh1.FIELD_OF_STUDY = cfos1.FIELD_OF_STUDY (+) UNION ALL SELECT cfos2.course_cd, cfos2.version_number, cfos2.FIELD_OF_STUDY, NVL( MAX(cfosh2.hist_end_dt), cfos2.last_update_date), TO_DATE(NULL), cfos2.last_updated_by, cfos2.percentage, cfos2.major_field_ind, cfos2.created_by, cfos2.creation_date, cfos2.last_updated_by, cfos2.last_update_date, cfos2.last_update_login FROM IGS_PS_FLD_STD_HIST cfosh2, IGS_PS_FIELD_STUDY cfos2 WHERE cfos2.course_cd = cfosh2.course_cd (+) AND cfos2.version_number = cfosh2.version_number (+) AND cfos2.FIELD_OF_STUDY = cfosh2.FIELD_OF_STUDY (+) GROUP BY cfos2.course_cd, cfos2.version_number, cfos2.FIELD_OF_STUDY, cfos2.percentage, cfos2.major_field_ind, cfos2.created_by, cfos2.creation_date, cfos2.last_updated_by, cfos2.last_update_date, cfos2.last_update_login

View Text - HTML Formatted

SELECT CFOSH1.COURSE_CD
, CFOSH1.VERSION_NUMBER
, CFOSH1.FIELD_OF_STUDY
, CFOSH1.HIST_START_DT
, CFOSH1.HIST_END_DT
, CFOSH1.HIST_WHO
, NVL( CFOSH1.PERCENTAGE
, NVL( TO_NUMBER( IGS_AU_GEN_001.AUDP_GET_CFOSH_COL( CFOSH1.COURSE_CD
, CFOSH1.VERSION_NUMBER
, CFOSH1.FIELD_OF_STUDY
, 'PERCENTAGE'
, CFOSH1.HIST_END_DT))
, CFOS1.PERCENTAGE))
, NVL( CFOSH1.MAJOR_FIELD_IND
, NVL( SUBSTR( IGS_AU_GEN_001.AUDP_GET_CFOSH_COL( CFOSH1.COURSE_CD
, CFOSH1.VERSION_NUMBER
, CFOSH1.FIELD_OF_STUDY
, 'MAJOR_FIELD_IND'
, CFOSH1.HIST_END_DT)
, 1
, 1)
, CFOS1.MAJOR_FIELD_IND))
, CFOSH1.CREATED_BY
, CFOSH1.CREATION_DATE
, CFOSH1.LAST_UPDATED_BY
, CFOSH1.LAST_UPDATE_DATE
, CFOSH1.LAST_UPDATE_LOGIN
FROM IGS_PS_FLD_STD_HIST CFOSH1
, IGS_PS_FIELD_STUDY CFOS1
, IGS_PS_VER CV1
WHERE CV1.COURSE_CD = CFOSH1.COURSE_CD
AND CV1.VERSION_NUMBER = CFOSH1.VERSION_NUMBER
AND CFOSH1.COURSE_CD = CFOS1.COURSE_CD (+)
AND CFOSH1.VERSION_NUMBER = CFOS1.VERSION_NUMBER (+)
AND CFOSH1.FIELD_OF_STUDY = CFOS1.FIELD_OF_STUDY (+) UNION ALL SELECT CFOS2.COURSE_CD
, CFOS2.VERSION_NUMBER
, CFOS2.FIELD_OF_STUDY
, NVL( MAX(CFOSH2.HIST_END_DT)
, CFOS2.LAST_UPDATE_DATE)
, TO_DATE(NULL)
, CFOS2.LAST_UPDATED_BY
, CFOS2.PERCENTAGE
, CFOS2.MAJOR_FIELD_IND
, CFOS2.CREATED_BY
, CFOS2.CREATION_DATE
, CFOS2.LAST_UPDATED_BY
, CFOS2.LAST_UPDATE_DATE
, CFOS2.LAST_UPDATE_LOGIN
FROM IGS_PS_FLD_STD_HIST CFOSH2
, IGS_PS_FIELD_STUDY CFOS2
WHERE CFOS2.COURSE_CD = CFOSH2.COURSE_CD (+)
AND CFOS2.VERSION_NUMBER = CFOSH2.VERSION_NUMBER (+)
AND CFOS2.FIELD_OF_STUDY = CFOSH2.FIELD_OF_STUDY (+) GROUP BY CFOS2.COURSE_CD
, CFOS2.VERSION_NUMBER
, CFOS2.FIELD_OF_STUDY
, CFOS2.PERCENTAGE
, CFOS2.MAJOR_FIELD_IND
, CFOS2.CREATED_BY
, CFOS2.CREATION_DATE
, CFOS2.LAST_UPDATED_BY
, CFOS2.LAST_UPDATE_DATE
, CFOS2.LAST_UPDATE_LOGIN