FPSINPUT 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.

This view fakes two upcoming maintenance events for every entity. It created some PMs and some Quals. It does some tools time-based and other tools counted-based. So this just gives some reasonable data to show on a demo Dashboard for sites where you cannot get upcoming maintenance data immediately. To use this, create two views selecting from this view. First create EQP_REF_MNT_FUTURE selecting all columns except for counter_curr. Then create the EQP_APD_MNT_COUNTER_HIST view selecting only facility, event_id, operator, counter_value, data_date as inst. Finally if it does not already exist you will need to create the EQP_MNT_COUNTER_HIST_INSERT procedure which you can either copy from most other sites or build from BLD_TABLE_INSERT_PROCEDURE.

This is fake view to populate some simulated starts based on randomizing recently started WIP and should be used when we do not have real starts data but we need something to demo the Capacity Model. Just make this WIP_REF_STARTS and then this should be replaced with the real starts data if and when we start to use Capacity Model.

This view generates calendar information from shift and day all the way up to year. It is not normalized. This is admittedly a bit of circular logic because the output of view should look exactly like the output of the FPSBASE views CAL_SHIFTS_PLUS and CAL_PLAN_DAYS_PLUS. We normally copy this view as FPSINPUT.LOAD_CAL_BASE with the parameters in the first section set appropriately and then use our FPS standard set of CAL_APD views stored in STD_CAL_APD_VIEWS to populate the individual normalized FPSINPUT CAL tables so that we can enforce referential integrity. Then we use the CAL_SHIFTS_PLUS and CAL_PLAN_DAYS_PLUS views to combine them again. Since some sites have completely custom calendars with different shift lengths and different shifts each day, we must leave this as an FPSINPUT.STD view to allow for the possibility of fully customized ETL but for most facilities this standard logic will work quite well and is very easy to configure and use. At the end of STD_CAL_APD_VIEWS we have a script to load and check the CAL tables.

This view is a template for a standard way to build RTG_COMMON_STEPS. It sorts common steps based on weighted cycle time across routes and then divides them into 10 facility segments based on cycle time. This view may be used as is or slightly modified for the site or not used at all if the site has its own logic. If you use this logic as is then just make a wrapper view so that any future changes to the STD logic will be automatically used: CREATE VIEW FPSINPUT.RTG_REF_COMMON_STEPS AS select (slash)* We use the standard logic for RTG_COMMON_STEPS so we do not include any history comment here. *(slash) facility, common_step, common_step_sort, facility_segment from fpsinput.std_rtg_ref_common_steps; In addition, if you use this view, you must disable RTG_ROUTE_STEPS_FK_COMMON_STEP like this: insert into fpsadmin.cfg_disabled (object_name, why_disabled, is_permanent) values ('RTG_ROUTE_STEPS_FK_COMMON_STEP', 'Since we build RTG_COMMON_STEPS based on RTG_ROUTE_STEPS here we must disable this foreign key since we will have the new common_step in RTG_ROUTE_STEPS before it will be in RTG_COMMON_STEPS.', 'Y'); alter table fpsinput.rtg_route_steps disable constraint RTG_ROUTE_STEPS_FK_COMMON_STEP;

The WIP_LOTS_FAST_FROM_ETL view must return the six basic fields of lot_type, plan_priority, facility, prd, planprd, and qty which may be left blank in the actual event but are required to be populated in WIP_EVENT_HIST by the trigger. This STD version is a template for how to write this view. It should exist at all sites where our ETL is by database link because it prevents incorrect information in events for new lots. It is neither necessary nor possible to create this at sites where ETL uses APF. This view must be fast when filtered for a single lot as that is how it is used in the trigger. It is extremely likely that WIP_LOTS_FAST_FROM_ETL should also be your base for both WIP_REF_LOTS_STATIC and WIP_REF_LOTS_VERIFY. At most sites you might have another view like LOAD_LOT_BASE which uses WIP_LOTS_FAST_FROM_ETL and then add some information which takes longer to obtain but is required for the WIP_REF_LOTS view. Again it is important to emphasize that WIP_LOTS_FAST_FROM_ETL must be lightning fast when filtered for one lot.

This view converts their due_inst from WIP_LOTS_STATIC into a weekly demand. We can use this if their due_inst values are relatively accurate but not reordered properly.

There are so many ways that you can set goals that we just let each site write their own logic. But here is some very simple logic to get started at a new site. This logic just sets goals based on what happened over the last seven days. It is verified to generate goals for completes which are 5% higher than your average completes per shift over the last seven days. If nothing else this makes the Dashboard fillup bars look reasonable immediately. We expect that you will replace this with your own goals logic, however, there are sections in this STD view which will likely be useful, including: --Converting completes to moves using smp_rate --Converting moves to WIP using cycle time --Dividing lot_group and prty_ctm_group into lot_type and plan_priority If you really want to use this exactly as is for startup then you can do this: CREATE VIEW FPSINPUT.WIP_REF_GOALS_PER_SHIFT AS SELECT (slash+asterisk) Using STD view for startup History: No history needed when using STD view (asterisk+slash) START_SHIFT, FACILITY, ROUTE, STEP, PRD, PLANPRD, LOT_TYPE, PLAN_PRIORITY, IS_NONSTD, GOAL_MOVES, GOAL_COMPLETES, GOAL_WIP, GOAL_OPER_MOVES FROM STD_WIP_REF_GOALS_PER_SHIFT;