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:
Not implemented in this database
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
[Home] [Help]