View: IGS_PS_OWN_HIST_V

Product:	IGS - Student System
Description:	This view is used to merge program ownership history with the current program ownership 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:	APPS.IGS_PS_OWN_HIST_V

View Text

SELECT COH1.COURSE_CD
, COH1.VERSION_NUMBER
, COH1.ORG_UNIT_CD
, COH1.OU_START_DT
, COH1.HIST_START_DT
, COH1.HIST_END_DT
, COH1.HIST_WHO
, NVL( COH1.PERCENTAGE
, NVL( TO_NUMBER( IGS_AU_GEN_001.AUDP_GET_COH_COL( COH1.COURSE_CD
, COH1.VERSION_NUMBER
, COH1.ORG_UNIT_CD
, COH1.OU_START_DT
, COH1.HIST_END_DT))
, COW1.PERCENTAGE))
, COH1.CREATED_BY
, COH1.CREATION_DATE
, COH1.LAST_UPDATED_BY
, COH1.LAST_UPDATE_DATE
, COH1.LAST_UPDATE_LOGIN FROM IGS_PS_OWN_HIST COH1
, IGS_PS_OWN COW1
, IGS_PS_VER CV1
WHERE CV1.COURSE_CD = COH1.COURSE_CD
AND CV1.VERSION_NUMBER = COH1.VERSION_NUMBER
AND COH1.COURSE_CD = COW1.COURSE_CD (+)
AND COH1.VERSION_NUMBER = COW1.VERSION_NUMBER (+)
AND COH1.ORG_UNIT_CD = COW1.ORG_UNIT_CD (+)
AND COH1.OU_START_DT = COW1.OU_START_DT (+) UNION ALL SELECT COW2.COURSE_CD
, COW2.VERSION_NUMBER
, COW2.ORG_UNIT_CD
, COW2.OU_START_DT
, NVL( MAX(COH2.HIST_END_DT)
, COW2.LAST_UPDATE_DATE)
, TO_DATE(NULL)
, COW2.LAST_UPDATED_BY
, COW2.PERCENTAGE
, COW2.CREATED_BY
, COW2.CREATION_DATE
, COW2.LAST_UPDATED_BY
, COW2.LAST_UPDATE_DATE
, COW2.LAST_UPDATE_LOGIN FROM IGS_PS_OWN_HIST COH2
, IGS_PS_OWN COW2 WHERE COW2.COURSE_CD = COH2.COURSE_CD (+)
AND COW2.VERSION_NUMBER = COH2.VERSION_NUMBER (+)
AND COW2.ORG_UNIT_CD = COH2.ORG_UNIT_CD (+)
AND COW2.OU_START_DT = COH2.OU_START_DT (+) GROUP BY COW2.COURSE_CD
, COW2.VERSION_NUMBER
, COW2.ORG_UNIT_CD
, COW2.OU_START_DT
, COW2.PERCENTAGE
, COW2.CREATED_BY
, COW2.CREATION_DATE
, COW2.LAST_UPDATED_BY
, COW2.LAST_UPDATE_DATE
, COW2.LAST_UPDATE_LOGIN

Columns