This view is used to merge teaching responsibility history with the current teaching responsibility details enabling all details to be accessed over time, up till the current day.
Implementation/DBA Data:
Not implemented in this database
View Text
SELECT TRH1.UNIT_CD , TRH1.VERSION_NUMBER , TRH1.ORG_UNIT_CD , TRH1.OU_START_DT , TRH1.HIST_START_DT , TRH1.HIST_END_DT , TRH1.HIST_WHO , NVL( TRH1.PERCENTAGE , NVL( TO_NUMBER( IGS_AU_GEN_004.AUDP_GET_TRH_COL( TRH1.UNIT_CD , TRH1.VERSION_NUMBER , TRH1.ORG_UNIT_CD , TRH1.OU_START_DT , TRH1.HIST_END_DT)) , TR1.PERCENTAGE)) , TRH1.CREATED_BY , TRH1.CREATION_DATE , TRH1.LAST_UPDATED_BY , TRH1.LAST_UPDATE_DATE , TRH1.LAST_UPDATE_LOGIN FROM IGS_PS_TCH_RESP_HIST TRH1 , IGS_PS_TCH_RESP TR1 , IGS_PS_UNIT_VER UV WHERE UV.UNIT_CD = TRH1.UNIT_CD AND UV.VERSION_NUMBER = TRH1.VERSION_NUMBER AND TRH1.UNIT_CD = TR1.UNIT_CD (+) AND TRH1.VERSION_NUMBER = TR1.VERSION_NUMBER (+) AND TRH1.ORG_UNIT_CD = TR1.ORG_UNIT_CD (+) AND TRH1.OU_START_DT = TR1.OU_START_DT (+) UNION ALL SELECT TR2.UNIT_CD , TR2.VERSION_NUMBER , TR2.ORG_UNIT_CD , TR2.OU_START_DT , NVL( MAX(TRH2.HIST_END_DT) , TR2.LAST_UPDATE_DATE) , TO_DATE(NULL) , TR2.LAST_UPDATED_BY , TR2.PERCENTAGE , TR2.CREATED_BY , TR2.CREATION_DATE , TR2.LAST_UPDATED_BY , TR2.LAST_UPDATE_DATE , TR2.LAST_UPDATE_LOGIN FROM IGS_PS_TCH_RESP_HIST TRH2 , IGS_PS_TCH_RESP TR2 WHERE TR2.UNIT_CD = TRH2.UNIT_CD (+) AND TR2.VERSION_NUMBER = TRH2.VERSION_NUMBER (+) AND TR2.ORG_UNIT_CD = TRH2.ORG_UNIT_CD (+) AND TR2.OU_START_DT = TRH2.OU_START_DT (+) GROUP BY TR2.UNIT_CD , TR2.VERSION_NUMBER , TR2.ORG_UNIT_CD , TR2.OU_START_DT , TR2.PERCENTAGE , TR2.CREATED_BY , TR2.CREATION_DATE , TR2.LAST_UPDATED_BY , TR2.LAST_UPDATE_DATE , TR2.LAST_UPDATE_LOGIN