FPSAPP.WIP_FLUSH_BASE

LOT

VARCHAR2(128)

N

A lot is a group of units that process together. Usually lot_id or lot_number in MES. All units in a lot are in the same carrier but there may be multiple lots in a carrier. (* from FPSINPUT.WIP_LOTS_STATIC)

FACILITY

VARCHAR2(6)

N

Facility is included in almost every join in the DWH so this represents a definitive split. A route must have all steps on tools in the same facility. A tool must process all lots in the same facility. If your site has multiple buildings where lots run on routes using tools in multiple buildings then everything should be one facility. For example, multiple Fab buildings. But if your site has independent facilities like Fab and Test and Assembly where lot may progress from one to the next but on different routes then these should be different facilities. Since this column is in virtually every table it is critical that the value here is exactly matches what is in the MES if the MES has facility. Use facility_display for the display friendly name displayed in applications. See site_name comment for client/site/facility example. (* from FPSINPUT.GEN_FACILITIES)

PRD

VARCHAR2(64)

N

Prd determines the route which is used to process the lot in the facility and what tools, recipes, durables, etc. can be used at each step. Prd also determines the next facility for the lot when it finishes its route. For detailed information on prd vs. planprd see table comments in RTG_PLANPRDS. (* from FPSINPUT.RTG_PRDS)

BANK

VARCHAR2(36)

N

Lots which are not on a route are considered in a bank and the bank name indicates why the lot is off route. Bank must be NA for lots which are on a route. Our standard filter for active lots is bank = NA. (* from FPSINPUT.RTG_BANKS)

BUNCH_ID

VARCHAR2(17)

Chain lots together for Goal Planner (* from FPSINPUT.WIP_LOTS_STATIC)

CARRIER

VARCHAR2(32)

RFID of the cassette or FOUP the lot is currently in. (* from FPSINPUT.MHS_CARRIERS)

CARR_CATEGORY_RQD

VARCHAR2(17)

Required carrier category which if set must match carr_category in MHS_CARRIERS (* from FPSINPUT.RTG_DURABLES)

CREATED_INST

DATE

Time when the lot was created, either lot start or split. (* from FPSINPUT.WIP_LOTS_STATIC)

CUM_DEMAND

NUMBER(14)

CUM_DEMAND_BY_WW

NUMBER(14)

CUM_PLANPRD_QTY_EOL

NUMBER(15,1)

Cumulative qty for the specified planprd of the specified main route or bank. This counts from the finish so if we have a several lots with 25 units then the closest to the finish would be 25 and the next closest would be 50 then 75 and so on. We match this with the demand for the prd to determine when each lot is needed to meet demand. (* from FPSBASE.WIP_FLUSH)

CURR_FACILITY

VARCHAR2(6)

Current facility of the lot. (* from FPSBASE.WIP_FLUSH)

CURR_FAC_BANK

VARCHAR2(36)

Current bank of the lot (NA if lot is currently on a route). (* from FPSBASE.WIP_FLUSH)

CURR_FAC_PRD

VARCHAR2(64)

Current product of the lot. (* from FPSBASE.WIP_FLUSH)

CURR_FAC_ROUTE

VARCHAR2(256)

Current main route of the lot (NA if lot is currently in a bank). (* from FPSBASE.WIP_FLUSH)

CURR_FAC_STEP

VARCHAR2(256)

Current step of the lot on its current main route (NA if lot is currently in a bank). (* from FPSBASE.WIP_FLUSH)

CURR_OFFROUTE

VARCHAR2(256)

If the current route of the lot is not the same as the main route then the lot is offroute and this is the current route. (* from FPSBASE.WIP_FLUSH)

CURR_OFFRT_STEP

VARCHAR2(256)

If the current route of the lot is not the same as the main route then the lot is offroute and this is the current step. (* from FPSBASE.WIP_FLUSH)

CURR_QTY

NUMBER(7)

Current qty of the lot using quantity unit of the current facility. (* from FPSBASE.WIP_FLUSH)

CURR_SEC_QTY

NUMBER(7)

Current secondary qty of the lot using secondary quantity unit of the current facility. (* from FPSBASE.WIP_FLUSH)

CUSTOMER

VARCHAR2(64)

Customer who will accept shipment of the lot. Currently this only used for grouping and filtering but in the future we might want to allow a customer to view a Dashboard only including their products. (* from FPSINPUT.GEN_CUSTOMERS)

DAYS_TO_DEMAND

NUMBER(8,4)

DEMAND_INST

DATE

Start of period when this qty of this planprd needs to finish. We automatically spread demand across each plan week in WIP_DEMAND_BY_WEEK which feeds WIP_FLUSH therefore this column can be the start of any time period less than or equal to plan week. It could be the demand for each shift or for each day or for each plan week but if the site has no preference then the easiest way to set this up is to write one row per plan week and let us spread across that week. It is important to note that we always spread across the plan week with our custom logic so even if this table is by day we might adjust it to another day within the same plan week. (* from FPSINPUT.WIP_DEMAND)

DEMAND_WW_QTY

NUMBER(14)

DUE_INST

DATE

To indicate the due date for this futuer start lot (* from FPSINPUT.WIP_STARTS)

ENGR_COMMENT

VARCHAR2(128)

Special comments made by engineering about this lot. These comments are about the lot in general and not about a specific step. (* from FPSINPUT.WIP_LOTS_STATIC)

EOL_INST_RAW_2D_WAVG

DATE

EOL_INST_RAW_7D_MED

DATE

EOL_INST_RAW_7D_WAVG

DATE

EOL_INST_RAW_COMMIT

DATE

EOL_INST_RAW_FIFO

DATE

EOL_INST_RAW_FULL_WAVG

DATE

EOL_INST_RAW_LONG_WAVG

DATE

EOL_INST_RAW_NOHOLD_FOR_WSF

DATE

EOL_INST_RAW_TARGET

DATE

EST_CURR_DIE_QTY

NUMBER(7)

Estimated die qty based on current qty and no yield loss. If the current die qty is unknown then the current wafer quantity is multiplied by gross die per wafer. (* from FPSBASE.WIP_FLUSH)

EST_DIE_QTY_AT_EOL

NUMBER(7)

The estimated die quantity when the lot finishes the route/bank listed. The calculation is based on line yield and sort yield from the current step to the end of the route/bank. If current die qty is unknown is calculated we estimate using gross_die_per_wfr. (* from FPSBASE.WIP_FLUSH)

EST_QTY_AT_EOL

NUMBER(8,1)

The estimated quantity when the lot finishes the route/bank listed in units of the facility listed. The calculation is based on line yield from the current step to the end of the route/bank listed. So if the route/bank is in the future then the upcoming routes/banks starting with the current one are included since we expect those wafers to be lost between now and the end of the future route/bank listed. This does not include sort yield. (* from FPSBASE.WIP_FLUSH)

FACILITY_OUT

VARCHAR2(6)

Facility of final flushed product. See detailed comments and examples in table overview. (* from FPSBASE.WIP_FLUSH)

FLUSH_SEQ_DESC

NUMBER(2)

Flush sequence descending is the order of main routes and banks for the lot from final flushed product back to the current. 1 is always the last main route or bank for the final flushed product. (* from FPSBASE.WIP_FLUSH)

FLUSH_SEQ_NUM

NUMBER(2)

Flush sequence number is the order of main routes and banks for the lot from current to the final flushed product. 1 is always the current main route or bank. (* from FPSBASE.WIP_FLUSH)

FLUSH_SEQ_OF_LAST_ROUTE_IN_FAC

NUMBER(2)

GBL_SORT

NUMBER(38)

Sorting used for dispatching or allocation systems to sort future start lots based on priority, critical ratio, etc. (* from FPSINPUT.WIP_STARTS)

GROSS_DIE_PER_WFR

NUMBER(6)

Expected gross die per wafer for the product. This is a critical field for planning die shipments. Note this is gross so we multiply by line yield and by sort yield for remaining steps of remaining products. (* from FPSINPUT.RTG_PRDS)

HOLD_NOTE

VARCHAR2(512)

Comment or note entered when the lot went on hold. Normally this should match the comment which was entered in the hold event. The logic to determine the hold note can get tricky when a subsequent comment event is logged to a lot already on hold because we do not know whether to update the hold note with the new comment or keep the comment from the original event that put the lot on hold. Our solution is to not update the hold_note by trigger with this comment event and leave the decision to our logic in WIP_LOTS_VERIFY. If you want to use the subsequent comment then populate it as the hold_note in WLV otherwise keep the original comment in WLV. Then our logic in CHK_WIP_LOTS_REALTIME is that if the hold_note written in WIP_LOTS_VERIFY differs from the hold_note populated by trigger while the hold_type and hold_for agree then we waive the normal 20 minute waiting period and update the hold_note immediately with a MISS-HOLDNOTE event. (* from FPSINPUT.WIP_LOTS_VERIFY)

HOLD_TYPE

VARCHAR2(24)

Generally hold_type will come directly from the MES. (* from FPSINPUT.WIP_HOLD_TYPES)

IS_CURR

CHAR(1)

Boolean flag where Y indicates this row is for the current main route or product. Same as flush_seq_num = 1. (* from FPSBASE.WIP_FLUSH)

IS_FUTURE_FACILITY

CHAR(1)

This flag is unnecessary and poorly named and will be dropped in a future release. (* from FPSBASE.WIP_FLUSH)

IS_HOLD

CHAR(1)

This field indicates the lot is on hold after the event is logged. In other words, this will always be Y if is_hold_start is Y and always N if is_hold_end is Y. (* from FPSINPUT.WIP_EVENT_HIST)

IS_MAIN_ROUTE_SIMILAR_TO_PRD

CHAR(1)

IS_REWORK

CHAR(1)

Y indicates the lot is on a rework route after the event is logged. If a lot is reworked then returns to the main route and repeats some steps, is_rework will be N but we will count those steps as repeats. (* from FPSINPUT.WIP_EVENT_HIST)

IS_START

CHAR(1)

Boolean flag where Y indicates the lot is an upcoming start and not an existing lot. (* from FPSBASE.WIP_FLUSH)

LAST_HOLD_INST

DATE

Time when the lot was last placed on hold. (* from FPSBASE.WIP_FLUSH)

LINE_SECTION

VARCHAR2(32)

Large grouping of the line, e.g. FEOL, BEOL, Cu, etc. There should be only a handful of values for the entire facility - or if the facility only has one section then we can leave this column blank. (* from FPSINPUT.RTG_LINE_SECTIONS)

LINE_YIELD_PCT

NUMBER(6,3)

Sort yield percentage from the current step to the finish of the specified route/bank. If the route/bank is not the current one then this included all steps. (* from FPSBASE.WIP_FLUSH)

LOT_ACTIVITY_INST

DATE

Date/time when the lot last had activity. (* from FPSBASE.WIP_FLUSH)

LOT_GROUP

VARCHAR2(8)

Lot_group is a grouping of lot_family and is the highest grouping in the lot type hierarchy. There should be only a few values for lot_group, i.e. Prod, Dev, TW. We group WIP and moves by lot_group on the dashboard and we group cycle time calculations by lot_group so this is an important field. (* from FPSINPUT.WIP_LOT_GROUPS)

LOT_TYPE

VARCHAR2(24)

Lot_type is the base of the hierarchy that determines lot_family then lot_group. Ideally lot_type will come straight from the MES with little modification. (* from FPSINPUT.WIP_LOT_TYPES)

MAIN_ROUTE_FAMILY

VARCHAR2(36)

Route family of the main route for the prd of the lot. (* from FPSINPUT.WIP_OVR_OPER_MOVES_HIST)

MAIN_ROUTE_GROUP

VARCHAR2(36)

Route group of main route. Often referred to as technology. (* from FPSBASE.WIP_FLUSH)

NUM_SIBLING_LOTS

NUMBER(2)

A count of other lots that were also split from the same parent. (* from FPSINPUT.WIP_LOTS_STATIC)

OFFROUTE_SEC

NUMBER(9)

If the current route of the lot is not the same as the main route then the lot is offroute and we record the estimated seconds to return to the main route here. (* from FPSBASE.WIP_FLUSH)

ORIG_DUE_INST

DATE

Original Due Date of this lot (* from FPSINPUT.WIP_STARTS)

ORIG_PLAN_OUT_INST

DATE

Original plan out date for this lot in the current facility which was set for the lot at its creation or when it entered this facility. (* from FPSINPUT.WIP_LOTS_STATIC)

PARENT_LOT

VARCHAR2(32)

The parent lot should ideally be the original parent but it could be the most recent parent if multiple splits have occurred. (* from FPSINPUT.WIP_LOTS_STATIC)

PHOTO_LAYER

VARCHAR2(12)

The photo layer the step belongs to. (* from FPSINPUT.RTG_ROUTE_STEPS)

PLANPRD

VARCHAR2(64)

N

Planning product used for all planning purposes. All lots with the same planprd are interchangeable to ship to the customer regardless of their prd, route, technology, wafer size, etc. For detailed information on prd vs. planprd see table comments in RTG_PLANPRDS. (* from FPSINPUT.RTG_PLANPRDS)

PLANPRD_OUT

VARCHAR2(64)

Planprd of final flushed product. See detailed comments and examples in table overview. (* from FPSBASE.WIP_FLUSH)

PLAN_OUT_INST

DATE

Current planned out date for this lot in the current facility. (* from FPSINPUT.WIP_LOTS_STATIC)

PLAN_PRIORITY

VARCHAR2(7)

N

Permanent priority of the lot set in the MES generally by planning. (* from FPSINPUT.WIP_EVENT_HIST)

PLAN_WEEK_DEMAND

VARCHAR2(7)

QTY_UNIT

VARCHAR2(12)

N

Defines the unit used to measure number of quantity. The key here is that this quantity must be known throughout the entire route through the facility. For example in a sort facility where lots enter in wafers and are broken into die but the number of wafers is still known when the lot completes the facility then the qty_unit must be wafer. It cannot be die because die is not known when the lot enters so die will be the sec_qty_unit. Similarly at a final test facility where the lots enter with both wafers and die known but only die are known when the lot completes the facility then the qty_unit must be die. It cannot be wafer because wafer is not known when the lot completes so wafer will be the sec_qty_unit. Please note the entire process from only wafers to only die must be split into at least two facilities because of this requirement. (* from FPSINPUT.GEN_FACILITIES)

REPORT_INST

DATE

Date/time when the WIP Flush last updated. Obviously this is the same for all rows. (* from FPSBASE.WIP_FLUSH)

ROUTE

VARCHAR2(256)

N

Route that has threading requirements (* from FPSINPUT.RTG_STEP_THREADING)

ROUTE_SECTION

VARCHAR2(32)

A large grouping of route steps used for line balance and should be set to contain at least a week of steps based on cycle time. Line balance uses this grouping instead of segment so that it can be configured independently from segment which is used in the Dashboard for display purposes, but they can also be set the same if desired. The section should be large in order to minimize the impact of short-term WIP distribution changes on step WIP targets because line balance calculates step WIP targets based on the WIP currently in each section. (* from FPSINPUT.RTG_ROUTE_STEPS)

ROUTE_SEGMENT

VARCHAR2(36)

Route_segment allows for clear hierarchical segment organization for Segment Summary and Line Viewer on Dashboard. This is often referred to as stage and typically will come from the MES (as opposed to facility_segment which we will typically have to define for our purposes). We recommend that all routes in the same route family have the same route segments in the same order so that the Line Viewer by route family will be consistent but if this is not the case then we approximate the order as best we can. (* from FPSINPUT.RTG_ROUTE_STEPS)

SEQ_NUM

NUMBER(7,2)

Sequence number of step in route (* from FPSINPUT.RTG_ROUTE_STEPS)

SHIP_QTY_THIS_WEEK

NUMBER(14)

SORT_YIELD_PCT

NUMBER(6,3)

Line yield percentage from the current step to the finish of the specified route/bank. If the route/bank is not the current one then this included all steps. (* from FPSBASE.WIP_FLUSH)

START_INST

DATE

Time when the lot started. (* from FPSINPUT.WIP_STARTS)

START_INST_ORIG_FAC

DATE

START_INST_RAW_2D_WAVG

DATE

START_INST_RAW_7D_MED

DATE

START_INST_RAW_7D_WAVG

DATE

START_INST_RAW_COMMIT

DATE

START_INST_RAW_FIFO

DATE

START_INST_RAW_FULL_WAVG

DATE

START_INST_RAW_LONG_WAVG

DATE

START_INST_RAW_NOHOLD_FOR_WSF

DATE

START_INST_RAW_TARGET

DATE

START_PLAN_WK_DEMAND

DATE

STEP

VARCHAR2(256)

N

A single processing step within a route representing a single tool visit. Step is often a very complex string and should rarely be displayed. Instead we should use process_display. (* from FPSINPUT.RTG_ROUTE_STEPS)

STEP_ENT_INST

DATE

Time when the lot entered the step. (* from FPSBASE.WIP_LOT_HIST)