FPSAPP.STD_CAL_APD_VIEWS

>

> FPSAPP.WIP_WAFER_HIST_LOOP

View FPSINPUT.STD_CAL_APD_VIEWS

WHENEVER SQLERROR EXIT FAILURE; --Keep above line if running in SQL Developer but comment out if running in Toad CREATE OR REPLACE FORCE VIEW FPSINPUT.CAL_APD_SHIFTS AS WITH (slash+asterisk) View to append to CAL table from the non-normalizated view LOAD_CAL_BASE. Unlike the other 10 views in this STD_CAL_APD_VIEWS template which can be copied as is for most sites, this one requires some custom logic to get accurate logic for work_group which is why the column is not included in the standard view STD_LOAD_CAL_BASE. Work_group is "who" and is usually A/B/C/D shifts with different values each time based on a rotation. This template generates A/B/C/D shifts for a site where A is day shift for the first part of the week, B is night shift for the first part of the week, C is day for second half, D is night for second half, and the schedule rotates every two weeks. If you can easily modify this logic to be accurate then do it. However for startup, it is acceptable to use work_period for work_group as the work_group can be easily changed later. History: Using standard version of this view from STD_CAL_APD_VIEWS (asterisk+slash) SHIFTS AS ( SELECT DISTINCT SHIFT, START_SHIFT, END_SHIFT, START_WORK_DAY, WORK_PERIOD FROM LOAD_CAL_BASE WHERE INCLUDE_IN_WORK_CALENDAR = 'Y' AND START_SHIFT > (SELECT NVL(MAX(START_SHIFT), TO_DATE('2000', 'YYYY')) FROM CAL_SHIFTS) ) ,GET_COUNT AS ( SELECT S.*, --This is the fixed time which is the start of the first work_week on the calendar that starts the --every two week rotation. MOD(2 * (START_SHIFT-TO_DATE('01/01/2022 07:00:00','MM/DD/YYYY HH24:MI:SS')), 28) AS SHIFT_COUNT FROM SHIFTS S ) ,GET_SECTION AS ( SELECT S.*, --This logic calculates front end of week or back end of week rotating from a fixed time CASE WHEN SHIFT_COUNT < 5 THEN 'A/B' WHEN SHIFT_COUNT < 13 THEN 'C/D' WHEN SHIFT_COUNT < 19 THEN 'A/B' ELSE 'C/D' END AS WORK_WEEK_SECTION FROM GET_COUNT S ) SELECT SHIFT, START_SHIFT, END_SHIFT, START_WORK_DAY, WORK_PERIOD, SUBSTR(WORK_WEEK_SECTION, CASE WHEN SUBSTR(WORK_PERIOD, 1, 1) = 'D' THEN 1 ELSE 3 END, 1) AS WORK_GROUP FROM GET_SECTION ORDER BY START_SHIFT; CREATE OR REPLACE FORCE VIEW FPSINPUT.CAL_APD_WORK_DAYS AS SELECT (slash+asterisk) View to append to CAL table from the non-normalizated view LOAD_CAL_BASE. History: Using standard version of this view from STD_CAL_APD_VIEWS (asterisk+slash) DISTINCT WORK_DAY, START_WORK_DAY, END_WORK_DAY, START_WORK_WEEK AS START_WEEK, WORK_DAY_OF_WEEK AS DAY_OF_WEEK, WORK_CALENDAR_DAY AS CALENDAR_DAY FROM LOAD_CAL_BASE WHERE INCLUDE_IN_WORK_CALENDAR = 'Y' AND START_WORK_DAY > (SELECT NVL(MAX(START_WORK_DAY), TO_DATE('2000', 'YYYY')) FROM CAL_WORK_DAYS); CREATE OR REPLACE FORCE VIEW FPSINPUT.CAL_APD_WORK_WEEKS AS SELECT (slash+asterisk) View to append to CAL table from the non-normalizated view LOAD_CAL_BASE. History: Using standard version of this view from STD_CAL_APD_VIEWS (asterisk+slash) DISTINCT WORK_WEEK, START_WORK_WEEK AS START_WEEK, END_WORK_WEEK AS END_WEEK, WW_SHORT, START_WORK_MONTH FROM LOAD_CAL_BASE WHERE INCLUDE_IN_WORK_CALENDAR = 'Y' AND START_WORK_WEEK > (SELECT NVL(MAX(START_WEEK), TO_DATE('2000', 'YYYY')) FROM CAL_WORK_WEEKS); CREATE OR REPLACE FORCE VIEW FPSINPUT.CAL_APD_WORK_MONTHS AS SELECT (slash+asterisk) View to append to CAL table from the non-normalizated view LOAD_CAL_BASE. History: Using standard version of this view from STD_CAL_APD_VIEWS (asterisk+slash) DISTINCT WORK_MONTH, START_WORK_MONTH, END_WORK_MONTH, START_WORK_QUARTER, WORK_MONTH_DISPLAY FROM LOAD_CAL_BASE WHERE INCLUDE_IN_WORK_CALENDAR = 'Y' AND START_WORK_MONTH > (SELECT NVL(MAX(START_WORK_MONTH), TO_DATE('2000', 'YYYY')) FROM CAL_WORK_MONTHS); CREATE OR REPLACE FORCE VIEW FPSINPUT.CAL_APD_WORK_QUARTERS AS SELECT (slash+asterisk) View to append to CAL table from the non-normalizated view LOAD_CAL_BASE. History: Using standard version of this view from STD_CAL_APD_VIEWS (asterisk+slash) DISTINCT WORK_QUARTER, START_WORK_QUARTER, END_WORK_QUARTER, START_WORK_YEAR, WORK_QUARTER_DISPLAY FROM LOAD_CAL_BASE WHERE INCLUDE_IN_WORK_CALENDAR = 'Y' AND START_WORK_QUARTER > (SELECT NVL(MAX(START_WORK_QUARTER), TO_DATE('2000', 'YYYY')) FROM CAL_WORK_QUARTERS); CREATE OR REPLACE FORCE VIEW FPSINPUT.CAL_APD_WORK_YEARS AS SELECT (slash+asterisk) View to append to CAL table from the non-normalizated view LOAD_CAL_BASE. History: Using standard version of this view from STD_CAL_APD_VIEWS (asterisk+slash) DISTINCT WORK_YEAR, START_WORK_YEAR, END_WORK_YEAR, WORK_YEAR_DISPLAY FROM LOAD_CAL_BASE WHERE INCLUDE_IN_WORK_CALENDAR = 'Y' AND START_WORK_YEAR > (SELECT NVL(MAX(START_WORK_YEAR), TO_DATE('2000', 'YYYY')) FROM CAL_WORK_YEARS); CREATE OR REPLACE FORCE VIEW FPSINPUT.CAL_APD_PLAN_DAYS AS SELECT (slash+asterisk) View to append to CAL table from the non-normalizated view LOAD_CAL_BASE. Please note that in order for this to load without errors you must load in the order of years then quarters then months then weeks then days. History: Using standard version of this view from STD_CAL_APD_VIEWS (asterisk+slash) DISTINCT PLAN_DAY, START_PLAN_DAY, END_PLAN_DAY, PLAN_DAY_OF_WEEK AS DAY_OF_WEEK, PLAN_CALENDAR_DAY AS CALENDAR_DAY, START_PLAN_WEEK, START_PLAN_MONTH FROM LOAD_CAL_BASE WHERE INCLUDE_IN_PLAN_CALENDAR = 'Y' AND START_PLAN_DAY > (SELECT NVL(MAX(START_PLAN_DAY), TO_DATE('2000', 'YYYY')) FROM CAL_PLAN_DAYS); CREATE OR REPLACE FORCE VIEW FPSINPUT.CAL_APD_PLAN_WEEKS AS SELECT (slash+asterisk) View to append to CAL table from the non-normalizated view LOAD_CAL_BASE. Please note that in order for this to load without errors you must load in the order of years then quarters then months then weeks then days. History: Using standard version of this view from STD_CAL_APD_VIEWS (asterisk+slash) DISTINCT PLAN_WEEK, START_PLAN_WEEK, END_PLAN_WEEK, PLAN_WK_SHORT, START_WORK_WEEK AS CORRESPONDING_START_WORK_WEEK FROM LOAD_CAL_BASE WHERE INCLUDE_IN_PLAN_CALENDAR = 'Y' AND START_PLAN_WEEK > (SELECT NVL(MAX(START_PLAN_WEEK), TO_DATE('2000', 'YYYY')) FROM CAL_PLAN_WEEKS); CREATE OR REPLACE FORCE VIEW FPSINPUT.CAL_APD_PLAN_MONTHS AS SELECT (slash+asterisk) View to append to CAL table from the non-normalizated view LOAD_CAL_BASE. Please note that in order for this to load without errors you must load in the order of years then quarters then months then weeks then days. History: Using standard version of this view from STD_CAL_APD_VIEWS (asterisk+slash) DISTINCT PLAN_MONTH, START_PLAN_MONTH, END_PLAN_MONTH, START_PLAN_QUARTER, PLAN_MONTH_DISPLAY, START_WORK_MONTH AS CORRESPONDING_START_WORK_MONTH FROM LOAD_CAL_BASE WHERE INCLUDE_IN_PLAN_CALENDAR = 'Y' AND START_PLAN_MONTH > (SELECT NVL(MAX(START_PLAN_MONTH), TO_DATE('2000', 'YYYY')) FROM CAL_PLAN_MONTHS); CREATE OR REPLACE FORCE VIEW FPSINPUT.CAL_APD_PLAN_QUARTERS AS SELECT (slash+asterisk) View to append to CAL table from the non-normalizated view LOAD_CAL_BASE. Please note that in order for this to load without errors you must load in the order of years then quarters then months then weeks then days. History: Using standard version of this view from STD_CAL_APD_VIEWS (asterisk+slash) DISTINCT PLAN_QUARTER, START_PLAN_QUARTER, END_PLAN_QUARTER, START_PLAN_YEAR, PLAN_QUARTER_DISPLAY FROM LOAD_CAL_BASE WHERE INCLUDE_IN_PLAN_CALENDAR = 'Y' AND START_PLAN_QUARTER > (SELECT NVL(MAX(START_PLAN_QUARTER), TO_DATE('2000', 'YYYY')) FROM CAL_PLAN_QUARTERS); CREATE OR REPLACE FORCE VIEW FPSINPUT.CAL_APD_PLAN_YEARS AS SELECT (slash+asterisk) View to append to CAL table from the non-normalizated view LOAD_CAL_BASE. Please note that in order for this to load without errors you must load in the order of years then quarters then months then weeks then days.