FPSINPUT.DOC_MODIFY_CAL_TABLES

>

> FPSINPUT.WIP_WAFER_HIST_LOOP

View FPSADMIN.DOC_MODIFY_CAL_TABLES

Updating the CAL tables for times in the past is one of our least favorite things to do therefore we try extremely hard to get the CAL tables correct the first time. But sometimes it is necessary. Each update is different but this is a good example of how to do it. This script will update the start of various work months. In this example, the quarters and years do not change but we changed from 5-4-4 to 4-4-5 format. --Run this first, it should return no rows meaning the current calendars have integrity select * from chk_calendars; --Disable unique constraints exec adm_disable_cons('FPSINPUT.CAL_WORK_MONTHS_U'); exec adm_disable_cons('FPSINPUT.CAL_WORK_MONTHS_DISPLAY_U'); --Insert "new" months (i.e. you will have a second record for March 2021 with a different start date) insert into cal_work_months (start_work_month, work_month, end_work_month, start_work_quarter, work_month_display) select distinct start_work_month, work_month, end_work_month, start_work_quarter, work_month_display from load_cal_shifts where work_year >= 2021 and start_work_month not in (select start_work_month from cal_work_months); --Update months to "new" months merge into fpsinput.cal_work_weeks a using (select distinct start_week, start_work_month from load_cal_shifts where work_year >= 2021) b on (a.start_week = b.start_week) when matched then update set a.start_work_month = b.start_work_month where a.start_work_month != b.start_work_month; --Update end_work_month of previous months merge into fpsinput.cal_work_months a using (select distinct start_work_month, end_work_month from load_cal_shifts where work_year >= 2021) b on (a.start_work_month = b.start_work_month) when matched then update set a.end_work_month = b.end_work_month where a.end_work_month != b.end_work_month; --Update corresponding_start_work_month merge into cal_plan_months a using ( select * from ( select distinct start_plan_month, corresponding_start_work_month, first_value(start_work_month) over (partition by start_plan_month order by abs(start_work_month - start_plan_month)) as new_cswm from cal_plan_months cross join cal_work_months ) where corresponding_start_work_month != new_cswm ) b on (a.start_plan_month = b.start_plan_month) when matched then update set a.corresponding_start_work_month = b.new_cswm; --Delete all records from PERF table delete from fpsapp.perf_p_h_comp_wip_month where start_month >= (select min(start_work_month) from cal_work_months where start_work_month >= to_date('2021', 'YYYY') and start_work_month not in (select start_work_month from load_cal_shifts)); --This one is slow, took almost 10 minutes on D merge into fpsapp.perf_p_h_otd_shift a using (select * from load_cal_shifts where work_year >= 2021) b on (a.start_shift = b.start_shift) when matched then update set a.start_month = b.start_work_month, a.month = b.work_month where a.start_month != b.start_work_month; --Finally delete the "old" months delete from cal_work_months where start_work_month >= to_date('01/01/2021', 'MM/DD/YYYY') and start_work_month not in (select distinct start_work_month from load_cal_shifts where work_year >= 2021); --Enable the constraints exec adm_enable_cons('FPSINPUT.CAL_WORK_MONTHS_U'); exec adm_enable_cons('FPSINPUT.CAL_WORK_MONTHS_DISPLAY_U'); --In case you deleted any months then reload them exec adm_load_object('PERF_P_H_COMP_WIP_MONTH'); --Run this last, it should return no rows meaning the new calendars have integrity select * from chk_calendars;