FPSBASE.WIP_REF_LOTS_COLS_W_DD_PRTN

>

> FPSBASE.WIP_WAFER_HIST_LOOP

View FPSBASE.WIP_REF_LOTS_COLS_W_DD_PRTN

This is the second refreshed table in our WIP_LOTS hierarchy. This view includes columns which either depend on data_date or which use a "partition by" clause. That means that when we refresh this table repeatedly in the RealTime job that nearly all records will be updated even those for lots which had no activity. Fortunately this table has a small number of columns so it should refresh relatively quickly. All columns which do not depend on data_date and which do not use a "partition by" clause are in WIP_LOTS_COLS_NO_DD_PRTN. Since nearly every record in this table will be different on each refresh, there is a question to use DBPR or DBPM. We found that DBPR is faster (13 sec to 18 sec) but also uses quite a bit more CPU according to ADM_TOP_SQL_SNAPSHOT. We generally use DBPR but either is acceptable, especially since we improved ADM_LOAD_EVENT_HIST_VIA_APD to call this with ADM_TABLE_MATCH_COMMIT in DWH-2755. The four tables WIP_LOTS_REALTIME, WIP_LOTS_STATIC, WIP_LOTS_COLS_NO_DD_PRTN, and WIP_LOTS_COLS_W_DD_PRTN are joined together in the WIP_LOTS_PLUS view which is used by nearly everything including DASH_P_WIP_LOTS. The initial logic in this view gives a rough estimate of when lots which have not started processing will complete their current step. This rough estimate only applies to lots which do not have an estimated completion from an external reservation nor from Scheduler. Obviously when we have either of those more accurate estimates we will use them. This rough estimate is not expected to perfect but the main goal is to smooth out WIP bubbles so that we do not estimate that all lots waiting at a step will arrive to the next step at the same time. Here is how this rough estimate logic works: 1) We find all lots which have estimated complete times. These include lots which are processing, which have an external estimate, or which have a Scheduler end time. 2) We find the latest of these estimated complete times for the process family and consider that time to be the starting point for lots without estimates. 3) We estimate the interval at which lots will move out of this process family. Note this has nothing to do with tools because we do not know the tool. In fact, this estimate move interval for the process family is approximately the estimated complete interval for each tool divided by the number of tools. Then we adjust this estimate specifically for each lot based on its throughput and on the current availability of tools in the process family. This is not perfect nor is it intended to be but it is reasonable. 4) We cumulate this estimated move interval for all lots in the process family sorted by status (dispatched first, hold last), priority, and age. 5) We add this cumulate after the last estimate from #1. EXAMPLE: Lot A1 is processing on tool A and estimated to complete at 1:00. Lot A2 is also processing on tool A estimated to complete at 2:00. Lot B1 is processing on tool B and estimate to complete at 1:30. Lot B2 is dispatched to tool B with no estimated complete. Lots 4 and 5 are waiting. The last estimated complete for the process family is 2:00. Estimated move interval for the process family is 25 minutes per lot so the cumulated interval is 25 for lot B2, 50 for lot 4, and 75 for lot 5. We add this cum to the last estimated complete of 2:00 so we get an estimated complete of 2:25 for lot B2, 2:50 for lot 4, and 3:15 for lot 5. Note how we do not care which tool lots B2, 4, and 5 will use. In fact, B2's estimate is based on the last estimated complete of 2:00 from tool A even though we know the lot is dispatched to B.