VIEW: APPS.IGS_PS_DSCP_HIST_V

Source

View Text - Preformatted

SELECT dh1.discipline_group_cd, dh1.hist_start_dt, dh1.hist_end_dt, dh1.hist_who, NVL( dh1.description, NVL( SUBSTR( IGS_AU_GEN_002.audp_get_dh_col( 'DESCRIPTION', dh1.discipline_group_cd, dh1.hist_end_dt), 1, 60), di1.description)), NVL( dh1.funding_index_1, NVL( IGS_AU_GEN_002.audp_get_dh_col( 'FUNDING_INDEX_1', dh1.discipline_group_cd, dh1.hist_end_dt), di1.funding_index_1)), NVL( dh1.funding_index_2, NVL( IGS_AU_GEN_002.audp_get_dh_col( 'FUNDING_INDEX_2', dh1.discipline_group_cd, dh1.hist_end_dt), di1.funding_index_2)), NVL( dh1.funding_index_3, NVL( IGS_AU_GEN_002.audp_get_dh_col( 'FUNDING_INDEX_3', dh1.discipline_group_cd, dh1.hist_end_dt), di1.funding_index_3)), NVL( dh1.govt_discipline_group_cd, NVL( SUBSTR( IGS_AU_GEN_002.audp_get_dh_col( 'GOVT_DISCIPLINE_GROUP_CD', dh1.discipline_group_cd, dh1.hist_end_dt), 1, 4), di1.govt_discipline_group_cd)), NVL( dh1.closed_ind, NVL( SUBSTR( IGS_AU_GEN_002.audp_get_dh_col( 'CLOSED_IND', dh1.discipline_group_cd, dh1.hist_end_dt), 1, 1), di1.closed_ind)), dh1.created_by, dh1.creation_date, dh1.last_updated_by, dh1.last_update_date, dh1.last_update_login FROM IGS_PS_DSCP di1, IGS_PS_DSCP_HIST dh1 WHERE di1.discipline_group_cd = dh1.discipline_group_cd UNION ALL SELECT di2.discipline_group_cd, NVL( MAX(dh2.hist_end_dt), di2.last_update_date), TO_DATE(NULL), di2.last_updated_by, di2.description, di2.funding_index_1, di2.funding_index_2, di2.funding_index_3, di2.govt_discipline_group_cd, di2.closed_ind, di2.created_by, di2.creation_date, di2.last_updated_by, di2.last_update_date, di2.last_update_login FROM IGS_PS_DSCP_HIST dh2, IGS_PS_DSCP di2 WHERE di2.discipline_group_cd = dh2.discipline_group_cd (+) GROUP BY di2.discipline_group_cd, di2.description, di2.funding_index_1, di2.funding_index_2, di2.funding_index_3, di2.govt_discipline_group_cd, di2.closed_ind, di2.created_by, di2.creation_date, di2.last_updated_by, di2.last_update_date, di2.last_update_login

View Text - HTML Formatted

SELECT DH1.DISCIPLINE_GROUP_CD
, DH1.HIST_START_DT
, DH1.HIST_END_DT
, DH1.HIST_WHO
, NVL( DH1.DESCRIPTION
, NVL( SUBSTR( IGS_AU_GEN_002.AUDP_GET_DH_COL( 'DESCRIPTION'
, DH1.DISCIPLINE_GROUP_CD
, DH1.HIST_END_DT)
, 1
, 60)
, DI1.DESCRIPTION))
, NVL( DH1.FUNDING_INDEX_1
, NVL( IGS_AU_GEN_002.AUDP_GET_DH_COL( 'FUNDING_INDEX_1'
, DH1.DISCIPLINE_GROUP_CD
, DH1.HIST_END_DT)
, DI1.FUNDING_INDEX_1))
, NVL( DH1.FUNDING_INDEX_2
, NVL( IGS_AU_GEN_002.AUDP_GET_DH_COL( 'FUNDING_INDEX_2'
, DH1.DISCIPLINE_GROUP_CD
, DH1.HIST_END_DT)
, DI1.FUNDING_INDEX_2))
, NVL( DH1.FUNDING_INDEX_3
, NVL( IGS_AU_GEN_002.AUDP_GET_DH_COL( 'FUNDING_INDEX_3'
, DH1.DISCIPLINE_GROUP_CD
, DH1.HIST_END_DT)
, DI1.FUNDING_INDEX_3))
, NVL( DH1.GOVT_DISCIPLINE_GROUP_CD
, NVL( SUBSTR( IGS_AU_GEN_002.AUDP_GET_DH_COL( 'GOVT_DISCIPLINE_GROUP_CD'
, DH1.DISCIPLINE_GROUP_CD
, DH1.HIST_END_DT)
, 1
, 4)
, DI1.GOVT_DISCIPLINE_GROUP_CD))
, NVL( DH1.CLOSED_IND
, NVL( SUBSTR( IGS_AU_GEN_002.AUDP_GET_DH_COL( 'CLOSED_IND'
, DH1.DISCIPLINE_GROUP_CD
, DH1.HIST_END_DT)
, 1
, 1)
, DI1.CLOSED_IND))
, DH1.CREATED_BY
, DH1.CREATION_DATE
, DH1.LAST_UPDATED_BY
, DH1.LAST_UPDATE_DATE
, DH1.LAST_UPDATE_LOGIN
FROM IGS_PS_DSCP DI1
, IGS_PS_DSCP_HIST DH1
WHERE DI1.DISCIPLINE_GROUP_CD = DH1.DISCIPLINE_GROUP_CD UNION ALL SELECT DI2.DISCIPLINE_GROUP_CD
, NVL( MAX(DH2.HIST_END_DT)
, DI2.LAST_UPDATE_DATE)
, TO_DATE(NULL)
, DI2.LAST_UPDATED_BY
, DI2.DESCRIPTION
, DI2.FUNDING_INDEX_1
, DI2.FUNDING_INDEX_2
, DI2.FUNDING_INDEX_3
, DI2.GOVT_DISCIPLINE_GROUP_CD
, DI2.CLOSED_IND
, DI2.CREATED_BY
, DI2.CREATION_DATE
, DI2.LAST_UPDATED_BY
, DI2.LAST_UPDATE_DATE
, DI2.LAST_UPDATE_LOGIN
FROM IGS_PS_DSCP_HIST DH2
, IGS_PS_DSCP DI2
WHERE DI2.DISCIPLINE_GROUP_CD = DH2.DISCIPLINE_GROUP_CD (+) GROUP BY DI2.DISCIPLINE_GROUP_CD
, DI2.DESCRIPTION
, DI2.FUNDING_INDEX_1
, DI2.FUNDING_INDEX_2
, DI2.FUNDING_INDEX_3
, DI2.GOVT_DISCIPLINE_GROUP_CD
, DI2.CLOSED_IND
, DI2.CREATED_BY
, DI2.CREATION_DATE
, DI2.LAST_UPDATED_BY
, DI2.LAST_UPDATE_DATE
, DI2.LAST_UPDATE_LOGIN