Product: | IGS - Student System (Obsolete) |
---|---|
Description: | This view is used to merge discipline history with the current discipline details enabling all details to be accessed over time, up till the current day. Each entry represents all column values over defined time periods. |
Implementation/DBA Data: | Not implemented in this database |
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