FPSAPP Views

Simple P view wrapper for APP_G table.

the list of roles for all applications

the list of roles for all applications

the list user

the list user

the list of users' roles for all applications

the list of users' roles for all applications

This view simply lists all roles for each facility since the roles are global but the web application which reads this filters for facility.

This view translates demand scenarios to starts scenarios by using fpsbase.rtg_prd_facility_reverse. Since the capacity model needs to see starts for each product even if one product will become another, all products are included.

This view gets additional information scenario starts since only route is inputted

This view gets additional information scenario starts since only route is inputted *** This is the PRD-as-ROUTE version. ***

This view allows next n weeks to be used in queries in code. Don't want to query directly from fpsbase.

This view returns capacity entity information from the FPSBASE schema to the Capacity Model application.

This view gathers cap_entity information for all uses in the CM including setup page display, pulldowns, and Excel.

Basic info on facilities.

This view provides menu items to the capacity model

*** This is the PRD-as-ROUTE version. *** (To get CM to do starts by PRD, rather than ROUTE.)

This view calculates equipment information for production and scenarios.

This view will replicate CM_C_SCENARIO_INFO_COLS

This view combines the starts scenario and prod starts data. Converting from a table to a view.

This view combines the starts scenarios (uploaded by users) and PRODuction starts data (normally loaded via ETL). *** This is the PRD-as-ROUTE version (CM-80). ***

This view gets work weeks for capacity model pages

We cannot use DASH_P_WIP_LOTS for CM only because we need the step_sec_long_wavg_to_eol column and do not want to go to the effort now of adding to WIP_LOTS_REALTIME and the whole chain.

This view calculates starts and information on starts the production scenario which we label PROD. This is a CM_B (base) object since it is not used by the web application but only by the two views CM_P_STARTS and CM_P_STARTS_BY_PRD which are both used by the web application..

This view creates the table of (rorp,step)--(eqp_type) data for Capacity Model calculations.

This view determines all capacity information necessary for route, step for calculation. The key point here is that we are now getting assignments by process rather than route/step but we already group by eqp_type. It is true that different route/steps using the same process will have different tool assignments but they will be within the same eqp_type in all but the strangest case. So I am quite confident that this logic which says to use the eqp_type assigned to the process is sufficient. Since this is now just a simply join of a few tables it is rather fast and we should be able to just use this as a view but some of the other refreshed tables using this were slow so I refreshed it for now.

This view is the base view for all Cycle Time Analyzer weekly data.

CTA includes three virtual process states. Is_proc_state is used for size of bar as proc states are larger on webpage.

This view gets all selection options for the DCTA

This view gets all fillin reasons for the DCTA

Query to use for drilldown to individual lot-step records. CTA has three drill downs: 1) From lot to all steps for lot with filter on lot. 2) From process to all lot-steps for process with filter on facility and process. 3) From bank to all lots for bank with filter on facility and bank This view is extremely sensitive to speed for both #2 and #3 so we did extensive testing to determine that this syntax was the fastest. Please be very careful making any changes to this view.

View used for showing last update for DCTA

This view is used for the DCTA legend.

This view gets the list of ctm groups for the selections in the CTA

This view refreshes the base table for all non-trend Cycle Time Analyzer data. Please note that the CTA queries must filter module, process family, and process at the very end in order to weight properly by route. This means that there is no reason to have indexes on any of these columns.

CTR logic adds a fake facility for Die Bank and this stores the values easier than a single row table since these are used in multiple views.

This view filters only valid lots, gets some information for the prd and facility, and unions to split die bank time into a separate virtual facility. This view should be used both for the chart data and for the drilldown to guarantee that the drilldown matches the charts.

This view is used to pick a *single* start/original PRD to display for each finished PRD in CTR. It replaces the ORIG_FACILITY, ORIG_PRD columns that used to be in FPSINPUT.RTG_PRDS. It picks the alphabetically highest START_PRD from the facility with the lowest SORT_ORDER. (The rationale is that, when a PRD is replaced by a newer version, its suffix is typically incremented, as in XYZ_01 to XYZ_02.) This is only an approximation, because a PRD in a multi-facility flow can clearly have multiple starting PRDs.

This view exists only as an example of the query used in the website to get data for the all facilities view. It is both for reference and to ensure it does not become invalid. This query uses a different view but is otherwise identical to the single facility query except this omits the facility filter.

This view exists only as an example of the query used in the website to get data for the single facility view when grouped by process state and filtered by criteria including process family or module or bank. It is both for reference and to ensure it does not become invalid.

This view exists only as an example of the query used in the website to get data for the single facility view when grouped by either process family or module. It applies to filter criteria.

This view exists only as an example of the query used in the website to get data for the single facility view when grouped by process state and filtered by criteria not including process family nor module nor bank. It is both for reference and to ensure it does not become invalid. This query is similar to the all facilities query except it uses a different view, has a facility filter, and treats commit/target a bit differently since they are totals across the series (which is process state). IMPORTANT NOTE: The total lots in this view is NOT the total number of finished lots for the relevant filtering when we have a ctm_series with multiple ctm_series_seq_in_fac values. When we have these ordered series, only the last seq actually finishes the facility while the first seq are merged into the prds in the last seq. To account for this, we just take the minimum of the num_lots from all seq in the series as the num_lots of the series.

View needed only because we do not allow websites to query from C tables.

This view gets the data needed for the bar chart in Finished Lot Cycle Time.

This view gets the data needed for the single facility view in Finished Lot Cycle Time. Columns are identical to CTR_P_DATA_ALL_FACILITIES except this has a facility column so the query from the website can be the same for both views except this one must have a filter for the single facility.

This view creates drop down selections needed for the Cycle Time Report

View to get information about each finished period in the queries.

This view gets the data needed for the lot charts

This view filters only valid lots and gets some information for the prd and facility. It is used both as a base for other CTR views that provide the chart data as well as individual for the drilldown to guarantee that the drilldown matches the charts. This view is quite similar to CTR_P_H_VALID_FINISHED_PF_BANK and any changes should likely be made to both. This view was originally CTR_B but it is used by the website for drilldown and other views so we renamed.

This view filters only valid lots and gets some information for the prd and facility. It is used both as a base for other CTR views that provide the chart data as well as individual for the drilldown to guarantee that the drilldown matches the charts. This view is quite similar to CTR_P_H_VALID_FINISHED_LOTS and any changes should likely be made to both.

This view normalizes the data from CTR_P_H_VALID_FINISHED_PF_BANK by process state.

This view gets the list of lot groups for the selections

This view creates drop down selections needed for the Cycle Time Report

This view creates a list of products and related information needed for the Cycle Time Report

This view is used for the dropdowns only and includes facility from RTG_PRDS. For the queries we use CTR_P_PRD_CTM_SERIES_ALL_FAC which does not have a facility column.

This view is used for the queries only and does not have a facility column. For the dropdowns, we use CTR_P_PRD_CTM_SERIES which includes facility from RTG_PRDS.

View for route-step information. Based off of DASH_P_ROUTE_STEPS

This view will populate with the input forecasted target days data for the FLCT chart

Information for all lots

View needed for process state colors

This view gets the data needed for Finished Lot Cycle Time lot drilldown and is fast when queried with a filter on lot. However we also query this for the drilldown to get multiple lots in a period using finished_period or start_period. This query takes about 30 seconds and it is essential that all of the columns which are available for filtering from the page are in CTM_FINISHED_LOT_HIST and specified from that table (c) in this view. Please note that this was improved in 6.17 to include the full history not only the finished lot but also for its parent lot prior to the split. This query is sensitive for speed.

This view filters only valid lots, gets some information for the prd and facility, and unions to split die bank time into a separate virtual facility. This view should be used both for the chart data and for the drilldown to guarantee that the drilldown matches the charts.

This view generates a table which is used in CTR_P_WIP_STEP_HIST when the prd is a plus-delimited list from a same_fac_seq. Since CTM_FINISHED_LOT_HIST records the main route of the prd and the main route of the lot, we want to join WIP_STEP_HIST by lot+step and not by main_route. If we added main_route to the join then we would get no data when the main_route of the prd was different than of the lot. However it is normal for a few steps to appear on both routes in the sequence, usually logistics or staging or similar. So we compromise by joining on route_segment which should be different for the two routes in the sequence but should be similar for both the main_route of the prd and of the lot. However occasionally the same combination of route_segment+step occurs on both routes in the same sequence. We would prefer to avoid that and the solution is to modify your ETL for route_segment to add some type of a prefix indicating the type of route in the facility sequence. This will be generic like FOL/EOL or FAB/PROBE or AL/CU or similar so it would also be useful to have this prefix which viewing route_segment. Having said that, if we have duplicates then we pick one here to avoid failing to load. If you make a change to CTM_SUMMARY and want it to reflect here immediately then it takes awhile: exec adm_load_object('CTM_SUMMARY'); exec adm_load_object('CTM_FOR_ROUTE_STEPS'); exec adm_load_object('RTG_ROUTE_STEPS_PLUS'); exec adm_load_object('CTR_B_RRS_FOR_SAME_FAC_SEQ');

This is the base table for all historical plan day data for the dashboard except scrap. This is used for the accordion on every spotlight so for speed reasons it is essential that this table is purged to keep only 14 days worth of data otherwise the dashboard is too slow. This view is nearly identical to DASH_APD_B_H_SHIFT_SUMMARY. Please note the index is on end_plan_day rather than start_plan_day because we do not want to use the index for DASH queries (that query by start_plan_day) but rather only for our purge logic.

This is the base table for all historical shift data for the dashboard except scrap. This is used for the accordion on every spotlight so for speed reasons it is essential that this table is purged to keep only 14 days worth of data otherwise the dashboard is too slow. Please note the index is on end_shift rather than start_shift because we do not want to use the index for DASH queries (that query by start_shift) but rather only for our purge logic.

This view will load ending WIP qty data grouped by plan_day. It is a copy of the DASH_APD_ECT_WIP_SHIFT_HIST view but is grouped by plan_day

This append view will load a snap shot of the WIP by ect state for the Performance reports

This view adds the arrv_in_hours and arrv_cutoff_inst for This Shift dynamically. This is necessarily since DASH_P_COMING_LOTS_COLS is a refreshed table for speed. For periods less than 24 hours we show all WIP arriving from now until the end of the period and for periods greater than or equal to 24 hours we show WIP arriving between the end of the previous period and the next period.

Information on process_subfamily tools with one record for each process_subfamily and tool, including current shift goal and completes. This information provides the input data necessary for the use percent optimizer to determine what percentage of each process_subfamily goal remaining should be done by each tool. This in turn is used to calculate a process_subfamily bottleneck score.

This view calculates average process_subfamily uph to use as fill-in when route step values are not available.

In 6.15 we moved sort_order from DASH_C_CATEGORY_TABLE_COLS to DASH_C_COLUMNS to standardize the column order across all tables. This complex logic attempts to determine that standard column sort order across the site. We kept ovr_sort_order in DASH_C_C_T_C for all tables where the standard order of displayed columns would be different from the current order so we keep this as view in case you want to modify the ovr_sort_order and get a new standard order.

This query is incredible useful to build the statements to call the DASH_CFG_UTIL package to add new columns to DASH_C_CATEGORY_TABLE_COLS and DASH_C_CATEGORY_TABLES. Basically just insert the rows manually (which we have likely already done on FPSDEV3) and then run this query with the appropriate filters to get all of the new columns. Then just paste the results into your upgrade script.

This view checks that all required columns are in all DASH_P objects that should have all required columns. This is used in CHK_COLUMNS which is part of CHK_ALL. Therefore if we add a required column and add it to some DASH_P views this will check if we missed any.

This view does two checks for duplicate similar records set to should_display in DASH_C_CATEGORY_TABLE_COLS. This is used in CHK_COLUMNS which is part of CHK_ALL. The two checks are: 1) Check that column XXX and column XXX_BAR are not both displayed in the same table. We would only want to display one or the other but not both. For example, SHIFT_COMPLETES and SHIFT_COMPLETES_BAR. 2) Check that two columns with the same value for column_header_display are not both displayed in the same table. Similarly in most cases we would only want to display one of these columns although it is possible that we would want to show both and change the column_header_display for one of them.

This merge statement copies a perfect configuration for the Shift view (web_version WEB) to the Day view (web_version DAY). merge into dash_c_category_table_cols day using ( select c.*, case when column_id like 'NEXT7%S' then column_id else replace(replace(replace(replace(replace(column_id, 'LAST_SHIFT', 'YESTERDAX'), 'SHIFT', 'DAX'), 'YESTERDAY', 'LAST_SHIFT'), 'DAY', 'SHIFT'), 'DAX', 'DAY') end as day_column_id from dash_c_category_table_cols c where web_version = 'WEB' and category not in ('LOTS','POPUP') ) web on (day.web_version = 'DAY' and day.facility = web.facility and day.category = web.category and day.table_id = web.table_id and day.column_id = web.day_column_id) when matched then update set day.column_entity_max_num = web.column_entity_max_num, day.column_width_pct = web.column_width_pct, day.ovr_sort_order = web.ovr_sort_order, day.should_auto_hide = web.should_auto_hide, day.should_display = web.should_display, day.should_display_exp = web.should_display_exp, day.should_export = web.should_export, day.should_total = web.should_total where ( day.column_entity_max_num != web.column_entity_max_num or day.column_width_pct != web.column_width_pct or day.ovr_sort_order != web.ovr_sort_order or day.should_auto_hide != web.should_auto_hide or day.should_display != web.should_display or day.should_display_exp != web.should_display_exp or day.should_export != web.should_export or day.should_total != web.should_total ) when not matched then insert ( web_version, facility, category, table_id, column_id, column_entity_max_num, column_width_pct, ovr_sort_order, should_auto_hide, should_display, should_display_exp, should_export, should_total ) values ( 'DAY', web.facility, web.category, web.table_id, web.day_column_id, web.column_entity_max_num, web.column_width_pct, web.ovr_sort_order, web.should_auto_hide, web.should_display, web.should_display_exp, web.should_export, web.should_total )

This is a copy of the complex spotlight history query used in the C# function. Please note this view is not used by the dashboard or anything else but is saved in this query for reference and to test speed. This is from Dashboard 3.9. Variables are filled in so the view compiles. Search for colon or curly brackets.

This is a copy of the complex query to build the Line Viewer chart in the Dashboard. It is saved here for testing purposes.

This is a copy of the logic used to calculate MTTR and MTBF in the Dashboard. It is saved here for testing purposes. {dataFilter} can be the following: - null - and mnt_module=:maintenanceModuleId - and mnt_family=:maintenanceFamilyId - and tool=:toolId

In 6.15 we moved sort_order from DASH_C_CATEGORY_TABLE_COLS to DASH_C_COLUMNS to standardize the column order across all tables. We kept ovr_sort_order in DASH_C_C_T_C for all tables where the standard order of displayed columns would be different from the current order. This view shows any tables where the ovr_sort_order does not change the order of displayed columns from what it would be if we cleared ovr_sort_order and used the standard sort_order. If any records exist in this view, we recommend clearing them: update dash_c_category_table_cols set ovr_sort_order = null where ovr_sort_order is not null and (facility, web_version, category, table_id) in (select distinct facility, web_version, category, table_id from dash_e_remove_ovr_sort_order);

Most clients use the expanded view of each table to see what columns are available to be displayed on that table in the main page. To this end, we would generally like to show all of the available columns that make sense. The logic in this view suggests to show all columns except the following that we decided do not make sense although they are still technically available: * Shift columns on Day version * Day columns on Shift version * "Bar" columns if corresponding "Non-Bar" column is displayed * "Non-Bar" columns if corresponding "Bar" column is displayed * "Moves" columns since Completes and Oper Moves are preferred * "IO2 Oper Moves" columns since IO2 Completes are preferred * "Oper Mvins" columns since Mvins or Oper Moves are preferred

This is a copy of the complex spotlight query used in the C# function in 3.9. Please note this view is not used by the dashboard or anything else but is saved in this query for reference and in case we want to make this a function in the future. Variables are filled in so the view compiles. Search for colon or curly brackets. Some advantages to make this an Oracle function rather than a C# function include: 1) We can easily see and update the complex query in the database rather than the website. 2) Database experts (i.e. Cabe) can maintain the function in addition to programmers. 3) We can change the basic spotlight hover messages without a web site release. Disadvantages include: 1) It's a bit complicated how we have to make a type to return the values. 2) There are more complex inputs in the web function than are included here. Cabe and Tyler agreed not to do this now mostly because of #2. But if we did this, the inputs would be: 1) i_facility (i.e. RRCWF2) 2) i_category (must match DASH_C_SPOTLIGHTS value i.e. MODULE) 3) i_category_filter (i.e. and wip_module = 'THIN FILM') - could be null for facility - always appended to i_facility filter so needs to start with and - currently :moduleId is a parameter but we could move to i_category_filter 4) i_data_filter (i.e. and wafer_size = '6') - will often be null if no filters selected then set to 1=1 - always appended to i_facility filter and i_category_filter so needs to start with and 5) i_only_movable_turns (boolean) - would need to be changed since web uses True/False and datbase uses Y/N The select query in the web code for the example would be: select * from table(dash_p_spotlights('MFN', 'MODULE', 'wip_module = ''DIFF''', 'wafer_size = ''6''', 'N') order by shift_index desc, sort_order;

This view is not actually used for anything but is a reference to copy and paste into any DASH views that is used with standard filtering. See also DASH_E_STANDARD_GROUPING which has much more detail than this as well as the joins required to get these columns. Note that Dashboard still uses lot_priority rather than curr_priority in many places so we often need to keep it until we change to use curr_priority. But all non-history objects should have curr_priority to allow us to fully switch over. Curr_priority does not apply to history objects.

This view is not actually used for anything but is a reference to copy and paste into any DASH views that is used with standard filtering/grouping. This view includes the standard fields which should be in all of these views. Please note that our testing showed it was faster to include all columns in cached tables as well rather than cache the base data and do the joins in a view.

This is a copy of the logic used to calculate turns in the Dashboard. It is saved here for testing purposes. IMPORTANT: The actual query used to calculate turns has changed dramatically in newer versions of the Dashboard, however, this query is still reasonably relevant to calculate turns even though simpler than what is actually used now.

In 6.15 we moved sort_order from DASH_C_CATEGORY_TABLE_COLS to DASH_C_COLUMNS to standardize the column order across all tables. We kept ovr_sort_order in DASH_C_C_T_C for all tables where the standard order of displayed columns would be different from the current order. This view shows those tables where we still use ovr_sort_order and therefore show the columns in a different order than the standard order. If when reviewing this view you decide that you like the standard order better then just set ovr_sort_order to null for all records of this table.

This view is used to determine when an object was last loaded for caching purposes. Since Dashboard is an application and not administration it is important for it to be limited to only the absolutely necessary information from an ADM table. In the future, we might want show a summary of load status on Admin Console at which time we would create a separate view for Admin Console.

Basic information about banks

This view returns the wip between a specific start and end time for a process family. It is quite sensitive to speed so we must do appropriate speed testing on any future change even the simplest one. Here is the normal query from the Dashboard to this view and a sample version to modify for testing: select * from dash_p_block_wip_hist where facility = :facility and process_family = :process_family and start_inst <= :endInst and end_inst >= :startInst; select * from dash_p_block_wip_hist where facility = 'FAC' and process_family = 'PF' and start_inst <= data_date and end_inst >= data_date - 2;

Summarize plan day info needed for dashboard.

Summarize shift info needed for dashboard.

Summarize work day info needed for dashboard.

Summarize work week info needed for Dashboard. Also used by the Throughput Tracker history charts.

History of carrier location. This provides the carrier location, user who updated the location, and a comment describing the transcation. The comment is auto generated by this view.

View needed because we do not use DASH_C tables in web pages.

View needed because we do not use DASH_C tables in web pages.

This view will allow dashboard to fetch the chamber alarm history for tools and chambers.

This view will allow dashboard to fetch the chamber health history for tools and chambers.

View used for color dropdown lists in different applications. Initially used for the Rule Group edit pane in MSOUI.

Get matrix of coming lots. For the WIP/Moves Summary on the main page we include the flag INCLUDE_IN_SUMMARY. We discussed whether we should count once per lot for Dashboard coming totals and/or whether we should multiply by smp_rate and we decided on the latter. The coming_qty here is the current qty multiplied by any expected yield loss from the current step to the upcoming step multiplied by the smp_rate. Please note that if future sampling decision is already known then the smp_rate is 0 or 1.

Quick fix to explain differences between completes on wip_module vs. eqp_module

Quick fix to explain differences between completes on process_family by process vs. by tool.

Cycle time summary information for use by the dashboard

Cycle time summary information for use by the dashboard

Current availability duration for all tools

This view is a wrapper for the FPSBASE.WIP_GOALS_CURR_PLAN_DAY table to use in Dashboard. Please note that this view is also used for work day goals since they are the same. To refresh all goal logic after making any input changes, see instructions in WIP_REF_GOAL_EST_SHIFT_BASE header.

This view is a wrapper for the FPSBASE.WIP_GOALS_CURR_SHIFT table to use in Dashboard. To refresh all goal logic after making any input changes, see instructions in WIP_REF_GOAL_EST_SHIFT_BASE header.

Moves for the current plan day. This view exists for backwards capability but will no longer be used in 5.4 where we introduce the dual dashboard functionality. The new query for the shift view (replaces this view) should be this: select cols from dash_p_curr_sh_d_moves where is_move = 'Y' and is_curr_shift = 'Y'; And for the plan day view: select cols from dash_p_curr_sh_d_moves where is_move = 'Y' and is_curr_plan_day = 'Y';

DROPPABLE: This view is no longer used in Dashboard version 5.4 and later, however, it is used in Mobile Dashboard through 5.7. If Mobile Dashboard is not used or if it is version 5.8 or higher then you can drop this view.

Basic information about customers

This P view shows the contents of the C table plus any global FPS reports which are not already listed in the C table. This means that Reports tab on the Dashboard will always include all global FPS reports regardless of whether they are in DASH_C_CUSTOM_REPORTS or not. If DASH_C_CUSTOM_REPORTS is completely blank then Reports tab will show just these reports in the default format. Please note that the ~ in the URL means the current application. So if you are viewing the Reports page at http://fpsweb.yourco.com/DashboardUAT/Reports then the link for the Lot List will go to http://fpsweb.yourco.com/DashboardUAT/Operations/Lots.

This P view shows the contents of the DASH_P_CUSTOM_RPT_SECTIONS table plus any sections assigned to any global FPS reports which are not included in the DASH_C_CUSTOM_REPORTS table. Since this view only shows section with reports assigned, it will not show records in DASH_P_CUSTOM_RPT_SECTIONS which have no reports assigned in DASH_C_CUSTOM_REPORTS.

Shows the last updated date in the upper right corner of DASH and triggers the refresh of the cache. Even though this view is fast, it must be refreshed into a table because it is critical that it updates only after the DASH tables are refreshed We get the time-weighted average of refresh intervals for the 3-minute load over the past 12 hours as the update_interval_sec. Weighting is done so that more recent observations are counted more heavily.

Gets all data that is constant for the database and does not need to be refreshed with the data update.

This view will provide us information about particular durables

This view provides the ECT state information for the dashboard.

This view will load the end wip qty by month which reads from the dash_p_ect_wip_day_hist table joining where end_plan_day = end_plan_month

This view will load the end wip qty by week which reads from the dash_p_ect_wip_day_hist table joining where end_plan_day = end_plan_week

This view provides entity status information to the dashboard

This view provides E10 history to the dashboard

This view provides E10 history to the dashboard

This view provides E10 history to the dashboard

This view provides E10 history to the dashboard

DROPPABLE: Dashboard 5.8.2 using the NOW view so this can be drooped. This view provides EQP entity state numbers by shift to the dashboard

This view provides EQP entity state numbers by week to the dashboard

This view provides EQP entity state numbers by shift to the dashboard

This view provides the state information for the dashboard based on equipment states defined by the client (EQP)

This view provides ETP tool state numbers by plan day to the dashboard

This view provides E10 history to the dashboard

This view provides E10 history to the dashboard

This view provides E10 history to the dashboard

This view provides E10 history to the dashboard

DROPPABLE: Dashboard 5.8.2 using the NOW view so this can be drooped. This view provides EQP tool state numbers by shift to the dashboard

This view provides EQP tool state numbers by shift to the dashboard

This view provides EQP tool state numbers by week to the dashboard

This view gets the estimated machine recipes for each eqp_type.

This view provides E10 history to the dashboard

This view provides E10 history to the dashboard

This view provides E10 history to the dashboard

This view provides E10 history to the dashboard

DROPPABLE: Dashboard 5.8.2 using the NOW view so this can be drooped. This view provides ETP entity state numbers by shift to the dashboard

This view provides ETP entity state numbers by week to the dashboard

This view provides ETP entity state numbers by shift to the dashboard

This view provides tool state history information to the Dashboard. It is critical for speed that the query to this view uses an index hint either +index(etp_hist etp_hist_eqp_to_inst) for EQP or +index(etp_hist etp_hist_eqp_to_inst) for ETP. These hints were added to the Dashboard in 6.4.2 so please make sure that you have this version or higher.

This view provides the state information for the dashboard based on Enhanced Tool Performance (ETP)

This view provides current tool state information to the dashboard

This view provides ETP tool state numbers by plan day to the dashboard

This view provides E10 history to the dashboard

This view provides E10 history to the dashboard

This view provides E10 history to the dashboard

This view provides E10 history to the dashboard

DROPPABLE: Dashboard 5.8.2 using the NOW view so this can be drooped. This view provides ETP tool state numbers by shift to the dashboard

This view provides ETP tool state numbers by shift to the dashboard

This view provides ETP tool state numbers by week to the dashboard

Basic info on facilities.

This view provides facility segments to the dashboard. Sort is based on common step sort and the FPSBASE.CHK_FACILITY_SEGMENTS view ensures this sort is consistent. NOTE: In previous versions of this view using RRSP we had to cast sort_order as number(4) which made no sense because fss is a number(4) in RRSP but without it the segment chart failed at some sites. Now we find the same problem using row_number. Something strange is going on in the Dashboard code but this cast seems to fix it so it stays.

This view gets all shutdown periods not including normally scheduled shutdowns to show on the Dashboard Lot page progress chart. The tricky logic here is to compare adjacent shutdowns into a single period of shutdown for the Dashboard. For example, if the table has a record from 01/01 06:00 to 01/02 06:00 and has another record from 01/02 06:00 to 01/03 06:00 then this logic will combine those two records into a single row in this view from 01/01 06:00 to 01/03 06:00.

This view provides finished lot history to the Dashboard and other apps. This enables us to display information about lots that have finished and are no longer in DASH_P_WIP_LOTS. This query should include all columns that we want to show for a finished lot. Note that the routing information returned by this view comes from history so it will match the groupings at the time the lot finishes. This is different than in Finished Lot CT where we use the current groupings to maintain consistency for filtering and data rollup.

This view includes entity state info and adds lot info if available which we include in the hover message. Note that this view uses either eqp_state or etp_state depending on the setting of use_etp_for_web in GEN_GLOBAL. Example query like: select * from fpsapp.dash_p_gantt_entity_state where facility = 'F' and tool = 'T' and to_inst > data_date - 1;

Includes all of the information needed for lot Gantt chart with one row for each lot/step. This view is designed to be filtered for a single lot as it is used in the Dashboard. This view is similar to DASH_REF_P_GANTT_PRIORITY_LOTS with two important distinctions. First we can write this view more easily to take advantage of the required single lot filter and still be fast. Second this view uses ECT_HIST rather than WIP_STEP_HIST to provide more detailed history. If we make changes to this view, we should review DASH_REF_P_GANTT_PRIORITY_LOTS to see if the same change is necessary.

This view is designed specifically for the Tool Lot Gantt Chart. This query is extremely sensitive to speed so be very careful when making even minor changes. This must be queried exactly like this: select * from dash_p_gantt_tool_lot where tool = :toolId and facility = :facilityId and comp_inst > :startInst and step_ent_inst <= :endInst order by lot_beg_inst_for_sort desc, lot, from_inst; Please note that process_state column is used to determine if the bar is wide (PROC) or narrow (all others).

One row for each tool and each time with information for chambers of the tool in the hover message. There are three identical versions of this view which just use a different version of DASH_P_GANTT_ENTITY_STATE.

Build the list of categories by which we can filter and group WIP

List of hold types for each facility. Currently WIP_HOLD_TYPES does not have a facility column so we cross join on GEN_FACILITIES so that each hold_type is listed for each facility. In the future we might choose to restrict this list somehow by facility. Please note that this view is also used with distinct to get the list of hold groups for each facility.

This view provides real-time ETP (or EQP) history to the dashboard

This view is used to get 3 day+curr, 7 day+curr, and current week metrics for the Line Viewer. This query to this view will be filtered on start_plan_day to get the appropriate period and grouped by the appropriate fields. Therefore we have no filters nor grouping in this view. We use full outer join rather than union for speed even though we never have the same record in both WPDH and DPCHDM since the former is past days and the latter is current day. The qty_xxx fields in DASH_B_H_PLAN_DAY_SUMMARY are filled in with zeroes so the logic of nvl works as desired.

This is the base view for all historical plan_day data for the dashboard except scrap. This is used for the accordion on every spotlight so for speed reasons it is essential that the B_H table is purged to keep only 14 days worth of data otherwise the dashboard is too slow. We use joins so we get all information for facility even in some route/steps are not in RTG_ROUTE_STEPS_PLUS. This view is nearly identical to DASH_P_H_SHIFT_SUMMARY.

This is the base view for historical day data for tool completes. The structure is identical to DASH_P_H_PLAN_DAY_SUMMARY for speed reasons.

Scrap events for the entire range of the spotlight. This view will eventually be used for all scrap queries in the Dashboard but for now we have other views using this for backwards capability.

This is the base view for all historical shift data for the dashboard except scrap. This is used for the accordion on every spotlight so for speed reasons it is essential that the B_H table is purged to keep only 14 days worth of data otherwise the dashboard is too slow. We use left joins so we get all information for facility even in some route/steps are not in RTG_ROUTE_STEPS_PLUS.

This is the base view for historical shift data for tool completes. The structure is identical to DASH_P_H_SHIFT_SUMMARY for speed reasons.

Ship history for Dashboard with no filter for time so Dashboard query must filter. The underlying table has an index on ship_inst so please filter on ship_inst rather than start_plan_week. Something like this would get the last four weeks plus the current week: select * from dash_p_h_ships where ship_inst > data_date - get_start_week_curr - 28

Process state level breakdown for each lot and step. This has one row per process state and only includes process states that are > 0 seconds. This will be used to display the step cycle time for each lot. We assume that this data will be grouped appropriately in the Dashboard so we do not group here. In other words, we could have multiple rows for the same process_state during the same step if a lot was in the same process_state more than once during the step.

This view is similar to DASH_P_H_SHIFT_SUMMARY and DASH_P_H_PLAN_DAY_SUMMARY but, unlike those two views which get data from FPSBASE.WIP_xxx_HIST_PLUS, this view is just a simple grouping of SHIFT into WORK_DAY.

This is the base view for historical work day data for tool completes. The structure is identical to DASH_P_H_WORK_DAY_SUMMARY for speed reasons.

Build all legends for the dashboard.

If there are 9 or fewer values in this view for a given facility then the Dashboard options menu will show check boxes. If there are 10 or more values then it will show a scrollable list.

This view is designed to show all of the assignments for a single lot at the current step and future steps. It should only be queried with a filter on lot. It is fast even for all steps for the lot and even faster with a filter on num_steps_away = 0 for the current step. If we need assignments for a tool or process_group then we use the appropriate NMV or SCH view.

Summary of lot_groups with colors from lot_family.

List only columns used by Lot Step Summary query. This view must be query with a filter on lot. The default sort should be on expected_comp_inst which is unique because we filter out steps that took (or are expected to take) no time.

View used to get bays information for web pages.

List of buildings for web pages

View used to get carrier information for web pages.

List of locations for web pages

List of stations for web pages

This view provides mnt_certifications to the dashboard.

This view provides mnt_cert_assignments to the dashboard.

This view grabs everything from EQP_MNT_COUNTER_HIST to be used in FPSWEB

Information about each entity - in other words tools and chambers.

This view gets all of the information needed to display maintenance episodes on the dashboard.

This view gets all of the information needed to display individual events on the maintenance dashboard.

This view provides mnt_event_part_assignments to the dashboard.

All information for MaintenanceFamilies within MaintenanceFamilyRepository

Two notes about this view: 1) The nested distinct is necessary because upcoming PM's for main entity have a row for each chamber in EQP_MNT_FUTURE and we only want one row per upcoming per tool. 2) Mnt_group is used twice in this view. The mnt_group of the future event is the event_type. The current mnt_group of the tool is used to not show upcoming PM's for tools which are currently NSC.

This view will allow dashboard to fetch the current maintenance indicator status for tools and chambers.

This view will allow dashboard to fetch the maintenance indicator status history for tools and chambers.

This view gets all of the information needed to display all instances on the maintenance dashboard. This view is intended to eventually replace most MNT views including MNT_B_BASE, MNT_P_LEFT, MNT_P_RIGHT, MNT_P_DATA_GRID, and MNT_P_EXCEL_EXPORT but there is no hurry to this. When we make changes to the maintenance dashboard we will start to use this view.

View used to get list of mnt_module values for web pages. This is necessary because not all modules are mnt_modules. As of Jan 2022 this view is used in core only. In DWH 6.20.4/7.5.5 and higher we could change the core query to DASH_P_MODULES with the filter on is_mnt_module = Y and then drop this view. But admittedly it is not really hurting anything to have this as a separate view.

This view provides mnt_parts to the dashboard.

This view is for a summary matrix at the bottom of the page

This view provides mnt_technicians to the dashboard.

This view provides mnt_tech_cert_assignments to the dashboard.

This view provides mnt_tech_schedules to the dashboard.

This view provides mnt_techn_sched_assignments to the dashboard.

This view provides mnt_tech_cert_assignments to the dashboard.

This view provides mnt_tech_schedules to the dashboard.

This view provides mnt_techn_sched_assignments to the dashboard.

This P view is simply to show contents of C table.

All information for tools with maintenance events needed for Dashboard and other websites

Basic information about modules

Gets the move types to display on the dashboard

This view provides the dashboard with port history

DROPPABLE: This view can be dropped after Dashboard 6.2 is active. In 6.2 we get yesterday completes directly from DASH_P_CURR_SH_D_MOVES. Note that we include completes on an offroute but since we left join this with the WIP chart these completes will only be displayed on the page if they are at a process/segment which is on the main_route.

IMPORTANT: This view uses "on overflow truncate" with listagg which requires Oracle Database 12c Release 2 (12.2). If your database is older, you will get the not-so-intuitive "ORA-00907: missing right parenthesis" error when attempting to create this view. The solution is to simply remove all six uses of "on overflow truncate" preceded by a space with nothing. The preceding space prevents the find/replace from replacing this comment. Then hope that you never exceed the 4000 character limit at your site. This view groups relevant information from RTG_ROUTE_STEPS_PLUS by process. It includes all processes in RTG_PROCESSES but adds the is_active flag indicating if the process currently has WIP, goals, or moves. The view DASH_P_PROCESSES filters this view for only the active processes to keep the list smaller for the Dashboard. TODO: Once the Dashboard has been updated to query DASH_P_PROCESSES where IS_ACTIVE = 'Y' then we will rename this view to DASH_REF_P_PROCESSES and we can eliminate this ALL view.

Originally this was a REF view that included the Bottleneck Score logic but we moved all logic to RTG_PROCESS_FAMILIES_PLUS so this is now just a simple wrapper view.

Build the list of process state including hiding block if appropriate

Summary view of queue timer history information for completed timers. One row for each facility, route, start step, and end step combination, along with relavent timer information. It is possible for a lot to restart and be brought back to the beginning of a timer loop. This would show as a separate record with the same lot and timer id. You will only see one instance for each route, start step, end step, timer id combination. Sometimes a route will be updated or a step renamed. The timer id will get deleted and a new timer id will be generated in the ETL

Summary table of queue timer history information for steps in a completed lot timer. One row for each lot and step combination, along with relavent step history information.

Summary view of queue timer information for WIP One row per lot per timer that is active or will be active at the current step for the lot. The TIMER_ORDER column is used to identify the most important timer for a lot when multiple timers are associated with the lot. Summary views should filter for TIMER_ORDER = 1.

Rack data for Dashboard. We need to join on MHS_RACKS only because cleanup_if_free_less_than is not in MHS_STATIONS.

This view will be used primarily for the rank order when we display the tools allowed on dashboard.

List of route_groups used for filtering and grouping

List of route sections for each facility. Currently this is a simple query to RTG_ROUTE_STEPS but if this is too slow we can refresh to a table. We decided to omit route_section_sort from this view since it is not currently relevant to the Dashboard and is actually a rather tricky column to use.

DROPPABLE: This view can be dropped after Dashboard queries are changed to use DASH_P_ROUTE_STEPS. That will likely be 6.14 but this could get pushed out so we must check when we think it might be safe. This view is for the WIP Segments and Line Viewer pages of the dashboard. Please note that it appears that the Line Viewer page gets route_segment_sort from DASH_P_ROUTE_STEPS so we must keep the route_segment_sort as a property of the route_family+route_segment combination. In other words, we cannot make route_segment_sort in DASH_P_ROUTE_STEPS a property of the route+route_segment and then have a route_family_segment_sort to use here with logic by route_family.

View for Line Viewer to get route-step information.

List of routes for web pages

Used in search. View needed because we do not use DASH_C tables in web pages.

This view is an wrapper on FPSBASE.WIP_SPLIT_MERGE_HIST and it is critical that this view be used with a filter on this_lot like this: select * from dash_p_split_merge_hist where this_lot = 'LOT1' order by inst desc;

View needed because we do not use DASH_C tables in web pages.

Future starts for Dashboard with no filter for time so Dashboard query must filter on start_inst. Since they have the same columns, this view can be easily unioned with DASH_P_STARTS_HIST to get history and future starts together.

Historical starts for Dashboard with no filter for time so Dashboard query must filter on start_inst. Since they have the same columns, this view can be easily unioned with DASH_P_STARTS_FUTURE to get history and future starts together.

This view for the maintenance spotlights to get the availability and utilization percentages

Stocker data for Dashboard

All information for tools needed for all FPS web applications including Dashboard. Because this view is used in all applications, setting the hide_in_dash flag to Y will hide the tool in all applications.

Performance charts work for both tools and stockers therefore this is a useful view that is used repeatedly in many performance queries.

Information about each entity - in other words tools and chambers.

Get matrix of coming lots by assigned tool. This view absolutely must be filtered on facility and tool but even then it takes 5-10 seconds so the development team should decide if they even want to use this for the tool page. The output is identical to DASH_P_COMING_LOTS so we can use the same webcode to built the same table in the hover message just with the filter on tool rather than process_family or module or whatever. Remember that lots are listed multiple times for each allowed tool so we cannot group this to get process_family. I do not see any reasonable way to make this faster and refreshing this into a table would take about 5 minutes and the table would be humongous so this view seems like the best possible solution.

This view originally included both WIP and EQP events for each tool but now it has only events from EQP_EVENT_HIST and is used in the Maintenance Dashboard. For the Recent Events table on the Operations Tool page which has both WIP and EQP events we use DASH_P_TOOL_LAST_FIFTY.

This questionably named view gets the most recent x events for each tool regardless of the time. Originally x was hardcoded to 50 therefore the view name but now x is configurable in GEN_SITE using the NUM_EVENTS_KEEP_DASH_TOOL_HIST column. To do this fast we union EQP_LAST_EV_OLDER_1HR to get events older than an hour but we do not know exactly when this table was refreshed. To make this as fast as possible we just take all events newer than the max from the history tables. Unfortunately Oracle can be stupid so there is significant Sailboat Racing in older to make this view fast even when queried with a filter on inst. The query from the Dashboard to this query to use for testing is like this select * from dash_p_tool_last_fifty where tool = 'ABC' and facility = 'XYZ' and inst between data_date - 3 and data_date - 2 order by inst desc, seq_within_sec desc;

This view calculates the maximum number of completes for each tool by finding the max in the recent shifts. It is very fast for a single tool using the primary key on facility+tool and reasonably fast for all tools using the index on start_shift assuming that shifts_for_tool_max_comp is not too large.

This view overlays the carrier and its process state onto EQP_TOOL_PORTS. Note that port_mode will only reflect the WIP processing state when the underlying port_mode has IS_UP=Y set in EQP_PORT_MODES. In other words, down port modes will be displayed as is. If your site does not want to overlay the process state information then you can disable this in GEN_FACILITIES.

This view for the maintenance spotlights to get the availability and utilization percentages

List of all process subfamilies for each tool. Unlike process family where each tool must be in just one for Dashboard purposes, tools can be in multiple process subfamilies. Ultimately we probably need to include which chambers are disallowed for a tool which is otherwise part of a process subfamily. For example, ET01 is allowed for process subfamily METAL but on only chambers A and B while C cannot do METAL. Once the programming team starts working on this they should instruct the DWH team how they would like this information to be provided. For now I included an empty column named ch_not_part_of_family.

This view gives WIP by tool for the current step. Lots are repeated when allowed on multiple tools so this view is intended to also include a filter on tool.

Information for users which can be joined on operator, hold_for, and other columns by username

Vehicle data for Dashboard. We need to use MHS_VEHICLES because is_auto_slot_assignment is not in MHS_STATIONS.

List of wafer sizes used for the dashboard

This view is basically the same as PERF_APD_P_H_COMP_WIP_SHIFT, but with Process column added to allow for that grouping in Dashboard Performance. This view does not populate a table because the table would become too large. Also, no grouping is applied within this view since that will be handled within the application.

Basic query to WIP_LOT_HIST for display on lot page and Excel export.

WIP_HOLD_HIST is a fast table so this view can be used by Dashboard to get hold history for a particular lot or time range very quickly. Filtering by any other criteria should be acceptable as long as it is within a reasonable time range.

This view is a combination of DASH_P_WIP_END_SHIFT, PERF_APD_P_H_COMP_WIP_SHIFT, and WIP_END_SHIFT_HIST_CUSTOM. Like the first object, this is a view with the Process column added to allow for that grouping on the Dashboard Performance page. Like the second object, this view has grouping because we must group by hour but process is included in the grouping. Like the third object, we get the data from WIP_STEP_HIST so we use similar case logic.

Information for all lots used nearly everywhere on the Dashboard.

Information for all lots was previously split into separate refreshed tables by active_code for speed reasons. Now we keep the ACTIVE, BANK, TW versions only for backwards capability as DASH_P_WIP_LOTS also uses WIP_LOTS_PLUS.

Information for all lots was previously split into separate refreshed tables by active_code for speed reasons. Now we keep the ACTIVE, BANK, TW versions only for backwards capability as DASH_P_WIP_LOTS also uses WIP_LOTS_PLUS.

This view displays each lots expected ship date and can be expanded to display the relevant CTM data. It exists so that Dashboard can pull from this view rather than using the complicated and likely to change GET_CTM_VALUES function directly in the application.

Information for hold lots page.

Information for priority lots. This requires a separate view because of the tricky logic to put a lot which is both a temporary and permanent priority in both categories. History:

Information for all lots was previously split into separate refreshed tables by active_code for speed reasons. Now we keep the ACTIVE, BANK, TW versions only for backwards capability as DASH_P_WIP_LOTS also uses WIP_LOTS_PLUS.

DROPPABLE: No longer used in Dashboard 5.8 and later View used to get lot families for web pages.

View used to get lot types for web pages.

Basic query to WIP_STEP_HIST with filters for only non-TW moves. This is used both for calculating moves by hour or other historical periods.

Provides a pace adjustment ratio based on the time of shift or day and last 7 days of history of moves by hour compared to final period moves. This adjustment is applied to the linear pace calculation on the Dashboard to create the PACE_ADJ calculation.

This view is basically the same as PERF_APD_P_H_COMP_WIP_DAY, but with Process column added to allow for that grouping in Dashboard Performance. This view does not populate a table because the table would become too large. Also, no grouping is applied within this view since that will be handled within the application.

This view is the same as the PERF_APD_P_H_COMP_WIP_MONTH but with process added to support Dashboard 6.8. The speed of this view can be slow depending on how big your site's wip_end_shift_hist table is but will only be used when the user charts by process in the performance reports which should not be very frequent

This view is the same as the PERF_APD_P_H_COMP_WIP_WEEK but with process added to support Dashboard 6.8. The speed of this view can be slow depending on how big your site's wip_end_shift_hist table is but will only be used when the user charts by process in the performance reports which should not be very frequent

Basic information about priorities

This view adds all Scheduler and NextMove information from WIP_STEP_FUTURE_SCHED for use in Dashboard lot views when necessary. This information must be filtered or joined on lot+facility+route+step like this: select * from dash_p_wip_lots w left join dash_p_wip_step_future_sched s on s.lot = w.lot and s.route = w.route and s.step = w.step and s.facility = w.facility; Please note that this view breaks the normal rule of only including columns necessary for the Dashboard and instead includes all columns from WIP_STEP_FUTURE_SCHED except for process, tool_for_tts, and job_id which are already in DASH_P_WIP_LOTS and three long details columns. However please only query the columns which are currently needed in the Dashboard query so that we can drop columns which are not used in the future as necessary.

Basic query to WIP_STEP_HIST with filters for only non-TW moves. This is used both to append to DASH_P_CURR_SHIFT_MOVES and for calculating moves by hour or other historical periods.

Query to WIP_WAFER_HIST used to build the wafer level Gantt chart on the Tool page. Example usage: SELECT actual_durable_used as ActualDurableUsed, actual_machine_recipe as ActualMachineRecipe, event as Event, event_type as EventType, facility as FacilityId, inst as Inst, logged_entity as LoggedEntity, lot as LotId, operator as Operator, tool as ToolId, wafer as WaferId FROM dash_p_wip_wafer_hist WHERE facility = :facility AND tool = :tool AND inst BETWEEN to_date(:startDate, 'MM/DD/YYYY HH24:MI:SS') AND to_date(:endDate, 'MM/DD/YYYY HH24:MI:SS'); We filter out events which we copy into WIP_EVENT_HIST. In case you were wondering why we do not just insert these events into WIP_EVENT_HIST, this is a two part answer. The first part is that lot-based begin, end, and abort events which come directly from the tool are often logged to an independent database outside of the MES. In this case, we want the ETL flexibility to log all events from the MES into WIP_EVENT_HIST and all events from the tool database into WIP_WAFER_HIST. This flexibility makes the ETL much easier to write and manage. The second part is that Oracle does not allow an insert to be rejected without an error. While we can copy the event to WIP_EVENT_HIST in the trigger, we cannot reject the insert in WIP_WAFER_HIST without an error. If we rejected with an error, this would cause the insert to WEH to fail also unless we did some crazy tricks with autonomous transactions that we do not want to do. So the best option is to end up with the event in both tables and this logic is in the WIP_WAFER_HIST_INSERT_BEF trigger.

This view is basically the same as PERF_P_H_COMP_WIP_WORK_DAY, but with Process column added to allow for that grouping in Dashboard Performance. This view does not populate a table because the table would become too large. Grouping is applied in this view only to group shifts into work days.

Get columns for matrix of coming lots. Dashboard needs to query rows and columns first and then left join data. This view is extremely fast but it basically crashed the dashboard when using in a more complex query so we are refreshing it.

Get rows for matrix of coming lots. Dashboard needs to query rows and columns first and then left join data.

Coefficient of Variation of Availability for tools on the mnt facility, mnt modules, and mnt families. NOTE: This view is exactly the same as DASH_REF_P_CV_AVAIL_ENTITY except that it has the grouping of facility+tool rather than just facility+tool+entity.

Coefficient of Variation of Availability for entities on the mnt facility, mnt modules, and mnt families. NOTE: This view is exactly the same as DASH_REF_P_CV_AVAIL except that it has the grouping of facility+tool+entity rather than just facility+tool.

This view gets all of the information for the priority lot Gantt chart. This view is designed to be filtered for all priority lots using the is_in_prty_gantt index on WIP_STEP_FUTURE. This view is similar to DASH_P_GANTT_LOT_STEPS with two important distinctions. First DASH_P_GANTT_LOT_STEPS has a single lot filter where this view filters for all priority lots making this view far more sensitive for speed. Second this view uses WIP_STEP_HIST which results in a single history bar for each step as that is more appropriate for the priority lot Gantt history while the other view uses ECT_HIST to provide more detailed history. If we make changes to this view, we should review DASH_P_GANTT_LOT_STEPS to see if the same change is necessary.

List of lots both current and future affected by line holds.

View used to get operations for web pages. We get operation module from rtg_route_steps_plus

Group relevant information from RTG_ROUTE_STEPS_PLUS by process. This view only includes processes with WIP, goals, or moves.

Information on process_subfamilies plus the Bottleneck Score. Bottleneck Score is the percentage of the goal expected to be achieved by the end of the shift assuming that all tools in the family which are currently up remain up and tools which are currently down remain down. A score under 100% means we do not expect to meet the goal while a score over 100% means that we expect to exceed the goal. Since a tool can be in more than one process_submfamily, we use the use percent optimizer linear program to determine how much of each process_subfamily goal remaining should be done by each tool. Note that we group by process_subfamily rather than by the full GP grouping so this will work even with est goals.

Base logic for NextMove Bay Performance pages. Metrics inlude; - Tool and Bay Curr WIP - Tool and Bay Completes - Tool and Bay Completes and wip targets - Tool and Bay Completes Pace - Tool and Bay Compliance Current WIP is sum of; -Any wip that has sched state ended, processing, reserved ... will be assigned to that tool -Any other WIP will be split between the allowable tools (rank P or A) in fab

This view calculates the last 30 days of historical WIP by process and then compares that to the coming WIP for the next 30 days and provides a determination as to whether the coming WIP will be low, medium, or high by shift relative to last 30 days. Please note that DASH_B_H_SHIFT_SUMMARY may be configured to purge less than 30 days so if this metric is important to your site then you should increase your purge_days to 30. This will slow down the Dashboard spotlights for the Shift web version so it is important to test if this slow down is significant at your site and manage accordingly. The coming WIP is aggregated by tool and shift using tool assignments and the WIP forecast levels of burndown, high, medium, and low are calcualted dynamically based on the coming WIP data. Burndown is a special level specified if the current WIP is high enough that it's expected to take multiple shifts to return to a normal level.

This view combines WIP_STEP_FUTURE with other tables to get all of the relevant information needed for goal planning and scheduling, The current step is included along with all upcoming stepf. This view is dependent on the current time.

Do calculations to get amount of labor needed per tool

All information on columns needed for documentation web pages. This view has one row for each column name which has a comment which generally means it is in an FPSINPUT table but might also be

All information on tables needed for documentation web pages.

All information on table columns needed for documentation web pages.

All information on views needed for documentation web pages. We must use the extract_view_comment function because the view text is stored as LONG datatype.

All information on view columns needed for documentation web pages.

This table is the base for Cycle Time Analyzer weekly trend data using the ECT logic. CTA_P_BASE_WK has similar columns using our standard CT logic from WIP_STEP_HIST via WIP_END_SHIFT_HIST. We might move a subset of this logic to FPSBASE.ECT_APD_PRCST_WEEK_HIST as this logic is similar to the FPSBASE.ECT_REF_SUMMARY_Px views.

NOTE: This was our original and likely future ECTA_REF_P_BASE view but we decided to use CTM_SUMMARY -> CTA_P_BASE for all of our fill-in logic in the first round of ECT. This view is how we would build everything from scratch if we want to completely replace CTM_SUMMARY with the logic coming from ECT_HIST. This current version is also quite slow at some sites so it will have to be sped up to use in production. We will also need to improve the fill-in logic as well as add the commit/target logic similar to CTM_SUMMARY. This table stores the base table for all non-trend Cycle Time Analyzer data using the ECT logic. Here we get all of the steps for routes where we have data for at least one step and use some rudimentary fill-in logic. This logic does not use CTM_SUMMARY (except for a few random columns) but it does not replace CTM_SUMMARY, at least not yet. This logic only does the minimum necessary to feed the CTA. If we are going to continue down the path of replacing CTM_SUMMARY with ECT logic there are a bunch of things that we must do but first and foremost would be better fill-in logic. The fill-in logic in this view is terrible but it gives some result and the first place we want to focus is the high runners and this allows us to do that. Please note that the ECTA queries must filter module, process family, and process at the very end in order to weight properly by route. This means that there is no reason to have indexes on any of these columns.

This view gets all selection options for the ECTA

This view gets all fillin reasons for the ECTA

Query to use for drilldown to individual lot-step records. CTA has three drill downs: 1) From lot to all steps for lot with filter on lot. 2) From process to all lot-steps for process with filter on facility and process. 3) From bank to all lots for bank with filter on facility and bank This view is extremely sensitive to speed for both #2 and #3 so we did extensive testing to determine that this syntax was the fastest. Please be very careful making any changes to this view.

View used for showing last update for ECTA

Despite its name, this view is used to get process states by ECTA. ECTA includes three virtual process states. Is_proc_state is used for size of bar as proc states are larger on webpage.

This view gets the list of ctm groups for the selections in the ECTA

This view is the base for all non-trend Cycle Time Analyzer queries using ECT. Please note that the ECTA queries must filter module, process family, and process at the very end in order to weight properly by route. This means that there is no reason to have indexes on any of these columns.

This view filters only valid lots, gets some information for the prd and facility, and unions to split die bank time into a separate virtual facility. This view should be used both for the chart data and for the drilldown to guarantee that the drilldown matches the charts.

View needed only because we do not allow websites to query from C tables.

This view gets the data needed for the bar chart in Finished Lot Cycle Time.

This view gets the data needed for the single facility view in Finished Lot Cycle Time. Columns are identical to ECTFL_P_DATA_ALL_FACILITIES except this has a facility column so the query from the website can be the same for both views except this one must have a filter for the single facility.

This view creates drop down selections needed for the Cycle Time Report

View to get information about each finished period in the queries.

This view filters only valid lots and gets some information for the prd and facility. It is used both as a base for other CTR views that provide the chart data as well as individual for the drilldown to guarantee that the drilldown matches the charts. This view is quite similar to ECTFL_P_H_FINISHED_PF_B and any changes should likely be made to both.

This view filters only valid lots and gets some information for the prd and facility. It is used both as a base for other ECTFL views that provide the chart data as well as individual for the drilldown to guarantee that the drilldown matches the charts. This view is quite similar to ECTFL_P_H_VALID_FINISHED_LOTS and any changes should likely be made to both.

This view gets the data needed for the lot charts

This view normalizes the data from ECTFL_P_H_FINISHED_PF_B by process state.

This view gets the list of lot groups for the selections

This view creates drop down selections needed for the Cycle Time Report

This view creates a list of products and related information needed for the Cycle Time Report

This view is used for the dropdowns only and includes facility from RTG_PRDS. For the queries we use ECTFL_P_PRD_CTM_SERIES_ALL_FAC which does not have a facility column.

This view is used for the queries only and does not have a facility column. For the dropdowns, we use ECTFL_P_PRD_CTM_SERIES which includes facility from RTG_PRDS.

View for route-step information based off of DASH_P_ROUTE_STEPS

This view will populate with the input forecasted target days data for the FLCT chart

Information for all lots

View needed for process state colors

See comments in CTR_P_WIP_STEP_HIST

This view filters only valid lots, gets some information for the prd and facility, and unions to split die bank time into a separate virtual facility. This view should be used both for the chart data and for the drilldown to guarantee that the drilldown matches the charts.

Pulls issue issue types for Feedbacks

Pulls statuses for Feedbacks

The goal of this view is to join the locked column from LN FR_W_WORKFLOW_TASK table with FR_W_MNT_SESSION_TASKS for active tasks

The goal of this view is to categorize maintenance event sessions that are no longer active using FR_W_MNT_SESSION_COMMENTS

The goal of this view is to provide an aggregated, comma-separated list of identifiers and users for a maintenance event session

This view determines drum rate per shift, day, or rolling period and target WIP levels by route and step. The drum rate is calculated based on target cycle time and current WIP within each route-route_section. Target WIP is calculated based on step process time and crurent WIP within each route-route_section, then adjusted based on historical sampling rates, target WIP multipliers, and directly provided target WIP values that are specified in the Goal Planner configuration. These multipliers and values are usually set to buffer more WIP in front of bottleneck toolsets. There are separate views for each level of detail which was deemed easier to manage than using one larger query with many more partitions and joins at the different levels of detail. Changes made in one view should likely be made in the others.

This view calculates drum priority based on the summarized data from GP_P_ROUTE_STEPS. The drum priority is normalized for use in the Scheduler based on the largest drum value in the facility.

This view calculates line balance priority based on the summarized data from GP_P_ROUTE_STEPS. The line balance priority is normalized for use in the Scheduler based on the absolute value of the largest effectice delta to target WIP or the largest WIP target in the facility, whichever is greater.

This view joins base route/step information on itself and filters for upcoming steps that are within the designated horizon days or num steps away. It uses the current and target WIP information to determine current and future target WIP deltas and then weighted averages the future target WIP deltas (weighting nearer steps more heavily) to determine a single future target WIP delta. The current step's current to target WIP delta minus the future target WIP delta is the line balance qyt delta. There are separate views for each level of detail which was deemed easier to manage than using one larger query with many more partitions and joins at the different levels of detail. Changes made in one view section should likely be made in the others.

Gives the maximum wafers at risk by rule and tool for each week to be used by the Sampling Dashboard. This logic keeps the max at risk lot for each tool / rule combination regardless if there was metrology done or not.

This view keeps the max at risk lot for each tool / rule combination for the last 7 days regardless if there was metrology done or not. Used for FPS Sampling Dashboard.

This view provides MSO Capacity Used Information to the Sampling Dashboard.

View of MSO curr counters

View of MSO decision history

This view provides decision parameters to the MSO UI.

View that return the default values for minimum consecutive skips, maximum consecutive skips and lot hist cutoff for a given rule type and it's condition.

View of MSO detection metrics

View of MSO detection at peaks. Combines lot/wafer and ttd. Only used for Max / Avg so nulls intentional

View of MSO detection at risk peaks

View of MSO detection at time_to_detect peaks

View of MSO detection at time_to_detect peaks. Adds last 7 days for charting on dashboard.

View of MSO detection at time_to_detect peaks. Adds last 7 days for charting on dashboard.

View provides a list of the rules that have been disassociated from a tag conditions

All information for tools and chambers needed for Sampling Dashboard.

This view provides external rule tag conditions to the MSO UI. These rule and tag condition associations can not be edited through the UI and will be read only

This view provides external tag conditions to the MSO UI. These tag conditions can not be edited through the UI and will be read only

This view provides external tag condition context to the MSO UI. These tag condition contexts can not be edited through the UI and will be read only

This view provides filters to the MSO UI.

This view provides filter parameter values to the MSO UI.

This view provides lot mso groups to the MSO UI.

This view provides MSO Event information to the MSO UI.

This view provides MSO Process information to the MSO UI.

This view provides product and corresponding prd_mso_group to the MSO UI.

List of the prd grouping for MSO

This view provides prd mso groups to the MSO UI.

Full list of associations between mso processes and modules, process families, tool mso groups for the filter dropdowns on the rules page of the MSO UI

Provide list of sampling rule process links to the MSO UI

Provide list of sampling rule process substitutions to the MSO UI

List of the route grouping used by MSO

This view provides route mso groups to the MSO UI.

This view provides route step information to the MSO UI.

View maps rules and links to modules and process families.

This view provides is used by the MSO UI to determine what process families, tool groups, and modules are linked to a given route and step. Whenever querying this view a filter must be applied by facility and one or more of the following (process_family, tool_mso_group, tool_mso_module)

This view provides rules to the MSO UI.

This view provides possible rule class values to the MSO UI.

Provide list of rule decision linkage - dependent processes

This view provides events linked to rules for event type rules.

View used by the MSO UI and Dashboard to display defined rule groups

View contains each sampling rule and it's process links / process subs used in sampling dashboard.

This view provides rule tag conditions to the MSO UI.

View contains the temporary skip count waivers created by engineering to temporarily override the rule default settings. The waiver is set by rule and tool

View displays tagged MSO lots and MSO attributes necessary for the dashboard

This view provides tag conditions to the MSO UI.

This view provides tag condition context to the MSO UI.

List of the route grouping used by MSO

Time-to-Start (TTS) data by tool for NextMove for all bays. If at least one lot is loaded on the tool then TTS is the time when the next lot needs to start after the most recently loaded lot in order to maintain the cascade. If no lots are loaded then TTS is data_date.

Bay to bay transit times for NextMove.

Carrier data for NextMove

View to display badges for each carrier

Enable NextMove to read from mhs_carrier_location_hist

NMV_P_CARRIER_SUMMARY is the view we use to get current-step information about carriers (basically everything that does not require wip_step_future). It is used in the loaded lots section on the tool page where we will want to display setup name and color so we can make it clear to the user which setup is currently running or needed for the loaded lots. Reserved carriers that are at the current step but are in a different bay than the reserved tool need to be displayed on the NextMove moves list_reserved. Please note there is some duplication here with NMV_P_RESERVED_SCHED_xxx because this view has similar logic but does not query WIP_STEP_FUT_ASSIGNMENTS.

All possible locations for NextMove.

Current assignment information by carrier for each tool. This view should be filtered on facility and tool. In earlier DWH versions, we had to also filter by process_group due to the exclusive logic but this is no longer required after the restructuring of WIP_STEP_FUT_ASGN views in 2016. We include lots that are already at the step plus more depending on the setting of is_in_nmv_pick_list. For each of these lot-steps, NextMove needs the estimated machine recipe, batch criteria, required setup, global sort order, the number of steps away, the estimated arrival time, processing time, and a list of other tools in the group where the lot can run. Only lots which have not been reserved are included. Lots scheduled to the tool are not included because they will be in the scheduled list but lots scheduled to other tools but not reserved are included. Lots that are excluded via use_in_sched (usually perm_rank of I) are also not shown. Example query: select * from fpsapp.nmv_p_pick_list_carriers where facility = 'FFF' and tool = 'TTT'; DROPPABLE: The PROCESS_GROUP column is no longer used in NMV Version 5.0 Later. If you are using any version prior the pick list will filter by the tool process group with the routing process group which is incorrect. If you need that corrected you'll need to upgrade to the NextMove 5.0 or later

Summary info needed for racks and stockers pages on NextMove. For some reason, putting NMV_P_CARRIER_SUMMARY inside a with statement at the beginning caused this query to crash so we query it twice.

This very fast view shows the complete information for all carriers either reserved or scheduled to a tool. This one view will be used to built the combined reserved and scheduled lists in NextMove. This view takes advantage of the WIP_STEP_FUTURE index on tool_for_tts which is the reserved or scheduled tool. This view should be queried like this; select * from fpsapp.nmv_p_reserved_sched_carriers where facility = 'FFF' and tool = 'TTT' order by manl_reserve_order nulls last, weh_reserve_inst nulls last, sched_start nulls last;

This view shows the complete information for all lots either reserved or scheduled to a tool. This view is the base for NMV_P_RESERVED_SCHED_CARRIERS which is used for display but the reservation and scheduling actually happens at the lot level so we need to know which specific lots are reserved. This view takes advantage of the WIP_STEP_FUTURE index on tool_for_tts which is the reserved or scheduled tool. This view should be queried like this: select * from fpsapp.nmv_p_reserved_sched_lots where facility = 'FFF' and tool = 'TTT' order by manl_reserve_order nulls last, weh_reserve_inst nulls last, sched_start nulls last;

Scheduled durable information for production lots.

upcoming setup changes data for NextMove

Staffing groups for NextMove.

Stockers and racks data for NextMove.

This view provides NextMove with station alternates

Station assignments data for NextMove.

A list of tools with their display configuration from EQP_BADGES.

This view gets qual summary information for tools to display on bay view.

This view gets qual summary information for tools to display on bay view.

Time-to-Start (TTS) data by tool for NextMove for all tools. If at least one lot is loaded on the tool then TTS is the time when the next lot needs to start after the most recently loaded lot in order to maintain the cascade. If no lots are loaded then TTS is data_date.

Vehicle data for NextMove

View to display list of active vehicle sub route information

Vehicle routes data for NextMove

Summary information for vehicles to be displayed on NextMove UI

This view list the number of completes by day, lot group, module, technology, line_section, and average wip. Used for FPS Performance Report.

This view list the number of completes by Month, lot group, module, technology, line_section, and average wip. Used for FPS Performance Report.

This view list the number of completes by shift, lot group, module, technology, line_section, and average wip. Used for FPS Performance Report.

This view list the number of completes by Week, lot group, module, technology, line_section and average wip. Used for FPS Performance Report.

This view snapshots the data to calculate on line balance. Used for FPS Performance Report.

This view snapshots the WIP_FLUSH table to calculate on time delivery used in the Performance tab of the Dashboard. This should be run at the start of each shift so we use CAL_SHIFT_LAST to assign the snapshot to the previous shift.

This view lists tool completes by plan day in the standard format and grouping used by the Historical Performance application.

IMPORTANT: We are not actually using this view in the Dashboard Performance code because it is easier to get this information from the cached version of DASH_P_CURR_SH_D_MOVES. However we still provide this view for testing and debugging. It provides the hourly completes by tool.

This view lists tool completes by plan month in the standard format and grouping used by the Historical Performance application.

This view lists tool completes by shift in the standard format and grouping used by the Historical Performance application.

This view lists tool completes by plan day in the standard format and grouping used by the Historical Performance application.

This view lists tool completes by work day in the standard format and grouping used by the Historical Performance application.

This view list the number of completes by day, lot group, module, technology, line_section, and average wip. Used for FPS Performance Report.

This view filters PERF_P_H_LINEBAL_SHIFT to provide per row per line balance grouping level based on how line balance is configured to be calculated. This allows Performance to show a calculation of line balance KPI's the way they are configured in the system as opposed to doing the calculation for each route/step which is how the data is stored. For example, PERF_P_H_LINEBAL_SHIFT may have 3 rows for 3 different route/steps all assigned to the same common_step. If line balance is configured to be calculated by common_step, then one row should be returned per common_step with the data aggregated accordingly.

Tool History by day.

Tool History by Month. TO DO: probably need to convert this to refreshed table since reading from shift data - need to test vs non-demo data

Tool History by shift.

Tool History by week.

Basic info on bays.

the carriers, here it only binds the carriers with the carrier assignments, for those not dedicated the carriers, it is fine not to bring to the scheduler.

the carrier family assignments

the list of lots - base information

IRL Durable info

report for the durables is required for the current lot and future coming lots

the counter for the durable counter

The list of durable states that will be used in Reticle Management

the list of entities

This view used to store information we want to display to the user during upgrades or at other times Only pulls rows with valid scheduled dates

the list of facilities

the relation of location - station - bay - building

the list of lots - base information

Basic info on process_groups.

report for the durables is currently on the tool but there is not pod

the current durable stockers

the durable assignments view based on the lot, route, step originally it was part of sch_b_durable_asgn_std but now we remove the logic of binding rtg_durables This is an important view sicne it is going to pass the data to - sch_p_curr_durable_families This is the view for loading durable family. Durable_family and Durable_group will be used to determine if we have 1 or 2 or more durable families required for this process - sch_p_curr_durable_assignments This is use to determine what is the required durable family for this process - sch_p_curr_durables This is the view to list all of durables that should be scheduled; however, we don't really want to bind too much data, we only need the durables we need

the counter for the durable

the list of process groups from rtg_route_steps_plus

the summary of counter for the process counter table from input schema

the list of process groups from rtg_process_groups

the tools sched event base view

the base view for the future block logic if there is any lot might fall into this scenario, then it might need to downgrade this lot priority

the list of lots to dispatch - base information

This view combines is based off of WIP_STEP_FUTURE_SCHED but only contains data necessary to calculate the dispatch inst (track-in time)

the list of lots to dispatch - base information

Description: Shows the batch criteria of the processing batched lots for current jobs

Description: Shows the batch criteria of the processing batched lots

- check the lots currently running or dispatched to tools which are batched together for more than 1 unique job id. The results of this view should show the lot data in the batch, the tool and the batch criteria

- check the lots currently running or dispatched to tools which are batched together for more than 1 unique job id. Shows count of the unique batch criteria in the same job There should only be one unique batch criteria per job. If this view returns anything Then we need to check.

Check if lots in batch is greater than batch size

this script is used to help to check if there is any potential errors on DWH objects

This is to check what could tbe the potential reasons the scheduler won't run it will check 1. ON/OFF of sched_group 2. if SCH_C_FACILITIES is set properly 3. if it has data to schedule, lots, tools and tool assignments 4. if it has good configuration on SCH_C_CFG_XXX tables 5. if it has good SERVICE_HOST and SERVICE_WEB

the list of lots - base information

App Information

Basic info on app settings

the list of lots - base information

the tools sched event. this base view will feed the data to - sch_p_curr_tool_events for scheduler to use - sch_p_tool_events for user to display the list of events

Basic info on bays.

get the list of calendar to be used to generate the reports

this is the list to show currently the lots or durables in transit

This view is to display the current batch configurations by process group

the list of current active config for the the tool

Basic info on tool settings

the list of current active config for the the process group

Basic info on eqp types settings

the list of current active config for the the process group

the list of current process group evaluations settings used in the scheduler calculation

Basic info on process group settings

the list of current active config for the the sched group

the list of sched group evaluations settings used in the scheduler calculation

the list of KPI items that will be used in calculation

the list jobs scheduled to run

View was originally created for conformance. It was too slow but I believe it's still being used by Matt.

the carriers, here it only binds the carriers with the carrier assignments, for those not dedicated the carriers, it is fine not to bring to the scheduler.

the carrier family assignments

Chamber path assignment based on FPSBASE paths. This view compiles data from both the FPSINPUT path assignment tables, so should work at any site.

the current drum rate

the current durable status

this is view for the durable asgns combo. when there is any lot/route/steps populated in RTG_DURABLE_ASGNS_COMBO, the scheduler then will ignore the data from rtg_durable_assignment for example, if on rtg_durable_assignment it has data to allow the lot X can run either tool S or T, but it has data for this lot X only for tool S with durable R, then this means the tool Y will not be an option as well

the durable assignment for the lot/route/step

the counter for the durable counter

the durables' sched events

the list of durable families it was part of durable_assignments, but try not to save same data again and again, so, split it out from durable_family

the current durable stockers

the current tool status

the list of process changes to ignore on the tool level

this view reads the pattern history given from the tool if available.

the view of all the patterns defined by eqp_type or tool

the view of the process-limit for every eqp-type

the view of the max process-usages for every eqp-type/tool

the view of the recipe for every eqp-type

the view of the recipe_family and recipe_group for every eqp-type

the list of lots - base information

the list of lots - base information

the list of lots - queued

the lots' queue stop data

the list of lots - to schedule

the list of lots - the list of the eligible lots that can be scheduled based on the sched-group and lot-group

the summarized counter information, including the data from durable counter and the fpsinput.rtg_process_counter_asgns

the summarized counter information, including the data from durable counter and the fpsinput.rtg_process_counters

the list of process groups for each sched group

the lots' queue stop data

the lots' queue sequence data

the list of sched groups

the list of rqd_setups for each sched group

the current step queue timer information

this view is used to indicate the possible threading tools that the scheduler should consider. 1. for the lot already entered into the threading, the scheduler should only schedule the future_thread_step to the allowed thread_group 2. for the lot that at not yet entered into the threading, then the scheduelr should schedule the start step first and according to the tool it is scheduled, then the the scheduler should only schedule the future_thread_step to the allowed thread_group

the list of lot assignments with process and tool

List the ports which are used to load cassettes (not reticle ports) except for those ignored for Scheduler.

the tools sched event. see comments on fpsapp.sch_p_base_tool_events

the ports for pod

the primary station/rack for the tool

the storage, including capacity for the the tool to store durable this view might not be right now, but this is the first step

the current tool type list

populate manual WIP_STEP_HIST data into scheduler snapshot

denote where to refresh the data

List of all dispatched lots (aka WEH_RESERVE) with all relevant information for Scheduler. Most important is est_end_inst_tool and est_next_lot_beg_inst which will only be populated for lots which have started processing. For dispatched lots which have not started yet Scheduler can reorder start time therefore Scheduler must estimate the end and next_lot_beg using standard THP logic.

the carrier dispatch list

the carrier dispatch list

No comment provided

dispatched lots with track in time

the list of process groups from rtg_route_steps_plus

Basic info on facilities.

the list of sched groups

the list web/service hosts for each sched group

the list of lot priority for each sched group

the list of lot gbl_sort for each sched group

the list of lot types for each sched group

the view of the sched weights for each sched group and perm rank

the list of pre-batch

the list of pre-batch

the list of process groups for each sched group

job type list

legend list

the list of link-steps-sched

the relation of location - station - bay - building

the list of menu items for each facility

denote where to refresh the data

the carriers, here it only binds the carriers with the carrier assignments, for those not dedicated the carriers, it is fine not to bring to the scheduler.

the list of lots - base information

report for the durables is currently on the tool but there is not pod

report for the durables is currently on the tool but aren't scheduled to run in the next 13 hours

report for the durables is required for the current lot and future coming lots

This view gets all durables that needs attention on inspection

This view gets all durables not currently available that have wip needing them in next X hours

report for the durables that are currently on the tool but aren't scheduled to run in the next 13 hours

the counter for the durable counter

the current tool status

the current durable stockers

the list of lots - base information

the list of lots that is currently in the timers report

this view will show the status since last successful run for each sched-group

statistics for sched group run

List and determine if it needs to recycle the IIS App Pool on sched-group level

sched state

statistics for sched group run

the list of durable required for setup changes

Basic info on station->bay->building->facility.

the list of current evaluation templates defined in this PROCESS_GROUP

the list of template that is current process group evaluations settings used in the scheduler calculation

the list of current evaluation templates defined in this SCHED_GROUP

the list of current evaluation templates with items defined in this SCHED_GROUP

the tools sched event. see comments on fpsapp.sch_p_base_tool_events

transit info for durable

transit info for cassettes

This view gets all durables that either unavailable now or might become unavailable in the next 12 hours but needed by lots

it includes, the planned out date for the lot, the expected out date for the lot, the expected out date for the step

In Photo, there are either internal reticle library (IRL) tools where individual reticles can be loaded or unloaded independently of other reticles or multi-reticle pod tools where up to six reticles are loaded or unloaded together and are tied to each other via the pod they are stored in. There are some tools, including ASML TwinScans, that have both, which means that reticles can be loaded and unloaded independently but we want to batch loads and unloads using the multi-reticle pods to avoid making repeated trips back and forth. This means we need take into account the current reticles in the IRL and pod and the capacity of both when deciding what to unload and load between them. The scheduler manages the lot and reticle usage on the tool but currently does not handle batching the moves for the IRL via pods. The following group of database objects aim to do that and are documented together for easier reference. Database objects and their purpose: SCH_REF_P_DISPATCH_DURABLES - View that reads Scheduler output and determines what reticles should be moved to and from a tool's IRL. SCH_P_DISPATCH_DURABLES - Table that stores the output from the SCH_REF_P_DISPATCH_DURABLES view so that applications accessing the data will receive a much quicker response than reading from the view directly which takes ~30 seconds to run. SCH_P_TRK_DURABLES_IRL - View that provides the actual reticle management plan for a pod that will feed an IRL tool. Important note, this view determines what reticles to load and unload from reticle storage and the pod, not the tool IRL since that will be handled directly between the tool EI and FPS DWH and not presented on the Reticle Management page. NMV_IRL_NEXT_DESTINATION - Procedure to be called by tool automation to provide reticle load and unload commands for the IRL. After a pod is kitted (reticles loaded and unloaded as needed) using the info from SCH_P_TRK_DURABLES_IRL above, we are now ready to load and unload reticles from the tool IRL. If managed automatically this procedure will be called to provide these instructions. NMV_IRL_NEXT_DESTINATION_CHK - Wrapper procedure to call NMV_IRL_NEXT_DESTINATION and print the results for checking. NMV_W_IRL_MOVES_OVR - Table that allows external system moves to be provided or which can be manually edited for testing purposes. To initiate moves from this table via NMV_IRL_NEXT_DESTINATION instead of the Scheduler, set i_mode = 2 in NMV_IRL_NEXT_DESTINATION. NMV_W_IRL_NEXT_DEST_LOG - Log table of calls to and returns from NMV_IRL_NEXT_DESTINATION.

the list of lots - queued for sample data

the list of lots - to schedule for sample data

the list of lots - from wip_step_hist

This view returns all effective roles configured in the security application

DROPPABLE: This view is for backwards capability with the Dashboard after we renamed this table to SEC_G_APPLICATIONS. It can be dropped after the Dashboard is fully converted to reading the new table which is likely version 5.10.

View copies over WIP_RESERVE_HIST for Dispatch events to track conformance to scheduler, nextmove or external reservations.

This view is the basic configuration for the System Monitor application replacing the SysMonitor.config XML file.

view to get CPU usage

This view is used by the SysMonitor className of CheckDataDate.

View to check curr locks based on this link: http://docs.oracle.com/cd/E11882_01/server.112/e41084/ap_locks001.htm#SQLRF55502

This view is used by the SysMonitor className of CheckLoadFailure. Sample output after formatting tweaks explained below looks like this: ORA-12899: value too large for column FPSBASE.RTG_ROUTE_STEPS.PROCESS_MODULE (actual: 13, maximum: 12) FPSADMIN.ADM_TABLE_REFRESH, line 65 FPSADMIN.ADM_LOAD_OBJECT, line 142

NOTE: We will drop this view when SYSMNTR application no longer uses it view to get admin.chk_load

View to check load deadlocks

This view is used by the SysMonitor className of CheckWipStepFuture. It finds missing lots between WIP_STEP_FUTURE and WIP_LOTS_REALTIME.

View that checks sites over all MSO health.

Logic was once in SYSMNTR_P_CHK_DATA_DATE and this view still queries this view but we separated so that we can eventually use just this view and configure CHK_DATA_DATE and CHK_NEXTMOVE as separate alerts.

View to check QM speed

View to check Scheduler run failures and send alert

This view is used by the SysMonitor className of CheckSlow.

View used by Sys Monitor Reports to show fab conformance to scheduler.

view to get the current DWH version

Simple view to return the runtimes for the ETL jobs

View to monitor job performance

View that checks for new MSO waivers, too many skipps, and tools interdicted and can be used to alert the client.

View used by Sys Monitor to generate filter list of all equipment types

View used by Sys Monitor to generate filter list of modules that have scheduler enabled

View used by Sys Monitor to generate Scheduler Load/Save/Run times along with Number of scheduled and non scheduled lots during the run.

View to monitor WIP_EVENT_HIST performance

This view retrieves the weighted average Cv of our unit interval THP data by eqp_type for the top 10 processes run in the last N days, where N is defined by THP_WINDOW_WEEKS from GEN_FACILITIES. The eqp_types are sorted in order of worst to best MPU Cv (and hence worst to best statistical quality of THP data in our DWH). Note: we attempt to exclude TW-only processes in the calculation from THP_SUMMARY.

This view retrieves the average Cv of all processes run by an equipment type in the last N days, where N is defined by THP_WINDOW_WEEKS from GEN_FACILITIES. Three coefficient of variations are calculated: 1) First Unit 2) Unit Int 3) MPU Additionally, if external or manual values exists for the eqp_type / process, the percentage by which our values differ is calculated as well. The processes are sorted in order of highest to lowest MPU Cv (and hence worst to best statistical quality of MPU THP data in our DWH). Though it is fast regardless, this view should normally be queried with a filter on facility and eqp_type.

This view retrieves the weighted average Cv of our unit interval THP data by tool for the top 10 processes run in the last N days, where N is defined by THP_WINDOW_WEEKS from gen_facilities. The tools are sorted in order of worst to best MPU Cv (and hence worst to best statistical quality of THP data in our DWH). Note: we attempt to exclude TW-only processes in the calculation from THP_SUMMARY_VALID_TOOL.

This view retrieves the average Cv of all processes run by a tool in the last N days, where N is defined by THP_WINDOW_WEEKS from gen_facilities. Three coefficient of variations are calculated: 1) MPU 2) Unit Int 3) First Unit Additionally, if external or manual values exists for the eqp_type / process, the percentage by which our values differ is calculated as well. The processes are sorted in order of highest to lowest MPU Cv (and hence worst to best statistical quality of MPU THP data in our DWH). Though it is fast regardless, this view should normally be queried with a filter on facility and eqp_type.

This view is adds columns to THP_EQPTYPE_WEEK_HIST that do not need to be stored in the history table. NOTE: This view is identical to THPT_P_H_TOOL_WEEK!

This view is adds columns to THP_TOOL_WEEK_HIST that do not need to be stored in the history table. NOTE: This view is identical to THPT_P_H_EQPTYPE_WEEK!

Simple wrapper view for FPSAPP.THPT.

Simple wrapper view for FPSAPP.THPT.

This view finds validation errors in the UPO tables

This is a query to the WIP_FLUSH table which demonstrates its demand planning capability for planprds where demand is populated in the WIP_DEMAND table. Currently we only use this as a reference for clients to use in their own custom reports but we might add something to our WIP Flush application in the future. It filters for a particular facility+planprd+lot_group+bank because this is how we group the lots for demand planning. Lot_group is usually something like Prod or PROD and bank should nearly always be NA for this query. It selects the appropriate columns use for demand planning and sorts appropriately by cum_planprd_qty_eol.

This view was originally on CTM_SUMMARY so it filtered on seq_num = 1 to get the total cycle time for the route or bank. Now we have CTM_SUMMARY_TOTALS which is much faster and has more consistent logic. CTM_SUMMARY_TOTALS_BY_PRD_BANK view includes logic to filter for main route and production lot groups as appropriate so no filtering is needed. But we need to set default values to 1 and 0 to allow for backwards compatibility. NOTE: Drop this view after WFM application is modified to use new WFM_P_CTM_TOTALS_BY_PRD_BANK view.

This view allowed WFM application to use CTM_SUMMARY_TOTALS_BY_PRD_BANK view. NOTE: Drop WFM_P_CTM_SUMMARY_PLUS view after WFM application is modified to use this new view.

View feeds the dropdowns for multi facility wipflush

This view allows FPSINPUT.WIP_DEMAND to be used in WFM.

UPDATED NOTE: This version is not completely correct! However the previous version was not only incorrect but very slow. Since this is a simple view on WIP_FLUSH there is no reason to keep as REF. To make this completely correct, we need a perfect version of RTG_PRD_FACILITY_REVERSE that gets all of the "starting" planprds for a given planprd by tracing back through each branch of RTG_PRD_FACILITY_NEXT until it ends. Then we know that a given planprd requires those "starting" planprds. Then we do the same reverse lookup for each current lot to find out all of the "starting" planprds which is represents. Then we can make a plot for each of the "starting" planprds of the given planprd which is the desired result. DWH-2865 is the JIRA task to do this. ORIGINAL COMMENT FROM REF VIEW: Multi-facility, multi-path WIP flush view. This view is used to build the "outs graph" for multi-facility WIP flush, which plots the expected number of dies (est_dies_to_dest) reaching EOL for a given end-product vs. date (days_to_dest). Because each end-product (in RTG_END_PRDS) may depend on multiple starting or intermediate products (from RTG_START_PRDS or RTG_PATH_PRDS), the outs graph may include multiple traces--one for each input product. The intent is for each input product's trace to estimate the number of expected dies out *if* the availability of that input product were the one limiting the total outs of the finished product. For example, if end-product X requires input-products A and B in equal quantity, then the graph for A shows the number of outs assuming infinite availability of B, and vice versa. Important: in multi-facility WIP flush, each product can go to multiple destinations. (RTG_PRD_SPLIT_30DAY contains the recent split ratios.) In order to forecast the number of outs based on recent split pattern, EST_DIES_TO_DEST from this view should be multiplied by PROB_TO_DEST. These columns are kept separate here to facilitate speculative "what if?" adjustments of the PROB_TO_DEST.

This view will replicate rtg_prd_end_banks

This view will replicate fpsinput.rtg_prd_facility_next

This view will replicate RTG_PRD_FAC_NEXT_W_BANK

This view will replicate RTG_PRD_SPLIT_30DAY

This view allows FPSINPUT.WIP_STARTS to be used in WFM.

This view snapshots the data from WIP Flush. Used in WIP Flush Application. This should be run after WIP_FLUSH which is normally in the middle of the shift or day so we get one snapshot per shift or day.

This view is used to provide planprds info to WIP Flush.

This view is used to provide colors to WIP Flush.

This view is used to get facility information for the WIP Flush Application.

This is the base view that feeds the wip flush application

This view is used to provide lot types to WIP Flush.

This view is used to provide planprds info to WIP Flush.

This view is used to provide prds to WIP Flush.

This view is used to provide priorities to WIP Flush.

WF look up view for colors.

This view is used to provide route info to WIP Flush.

This view is used to provide route families info to WIP Flush.

This view is used to provide route groups info to WIP Flush.

View is used to know the shifts for history. The WF_P_H_FLUSH table is just a snapshot of the WIP_FLUSH table which is refreshed every shift at some sites and every day at others. Because it just takes a snapshot, it cannot fill in old data like WIP_END_SHIFT_HIST or ETP_TOOL_SHIFT_HIST that is compiling from another history table. Therefore WF_P_H_FLUSH will not include duplicate records for the same shift (and PK of WIP_FLUSH) but it is also not guaranteed to have every shift. The labels say shift throughout the code and UI but only show shifts that have data. Therefore WF_P_SHIFT_CNTS must only include shifts in WF_P_H_FLUSH to guarantee that only valid shifts are in the dropdown. We were somewhat concerned about the speed of this view but it seems to take less than half a second.