FPSBASE.ECT_FINISHED_LOT_HIST

Data Dictionary

FPSBASE Tables

> FPSBASE.ECT_FINISHED_LOT_HIST

Table FPSBASE.ECT_FINISHED_LOT_HIST"

A record for each finished lot showing the total cycle time spent in the facility broken down by queue, hold, bank, and other categories. The primary key of this table should be lot+facility but it is actually lot+facility+inst and there are two reasons for this. The first reason is that some sites allow a lot to be shipped and then unshipped and then shipped again. When this happens only the first record will be valid so this will not impact CTR views. The second reason is that some sites reuse the same lot_id values in their MES for new lots. So one particular physical lot can only finish once per facility. However two different physical lots with the same lot_id value can finish at different times. This will impact CTR views but it is extremely rare so we accept that CTR will not work properly if a site does this with production lots.

Schema: FPSBASE
Tablespace: FPSDATAHIST
Primary key: INST, FACILITY, LOT, IS_IN_SAME_FAC_SEQ

Column	Type	Nullable	Comment
INST	DATE	N	Time when the event occurred. (* from FPSINPUT.WIP_EVENT_HIST)
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)
LOT	VARCHAR2(32)	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)
IS_IN_SAME_FAC_SEQ	CHAR(1)	N
COMBINED_LOT_FOR_SAME_FAC_SEQ	VARCHAR2(32)
COMMIT_CT_DAYS	NUMBER(5,1)		Number of days of cycle time committed to the customer for the prd or bank. This commit is published externally. This should be greater than or equal to the target. (* from FPSINPUT.RTG_BANKS)
DIE_BANK_SEC	NUMBER(9)	N
FIFTH_BANK	VARCHAR2(36)
FIFTH_BANK_SEC	NUMBER(9)
FINISHED_INST	DATE
FINISHED_PERIOD	VARCHAR2(7)	N
FIRST_BANK	VARCHAR2(36)
FIRST_BANK_SEC	NUMBER(9)
FIRST_OPER_ENT_INST	DATE
FIRST_OPER_FOR_LOT	VARCHAR2(50)
FIRST_PRD_IF_SAME_FAC_SEQ	VARCHAR2(64)
FIRST_STEP_FOR_LOT	VARCHAR2(256)	N
FIRST_STEP_ON_ROUTE	VARCHAR2(256)
FOURTH_BANK	VARCHAR2(36)
FOURTH_BANK_SEC	NUMBER(9)
INSERTED_TIME	TIMESTAMP(6)		Timestamp field set by trigger storing SYSTIMESTAMP when record was inserted in the table. (* from FPSINPUT.WIP_EVENT_HIST)
IS_NONSTD	CHAR(1)		Our column to indicate non-standard processing at the step. For example, an experiment or a chain that is only active at a particular step or set of steps and then goes back to being normal for the route. If an experiment is severe enough that it affects the final product and has to be planned separately then it will be its own prd or planprd. (* from FPSBASE.WIP_LOT_HIST)
IS_START_FROM_CSLH	CHAR(1)
IS_VALID	CHAR(1)	N	We consider a lot valid for our finished lot calculations when the lot moved through the first step and the last step on the main route for its product. There are some variations such as the lot moved through the first step on an older version of the route or finished with an event specifically marked as a ship event or the route did not have any steps.
LAST_OPER_FOR_LOT	VARCHAR2(50)
LAST_OPER_MOVE_INST	DATE
LAST_PRD_IF_SAME_FAC_SEQ	VARCHAR2(64)
LAST_STEP_FOR_LOT	VARCHAR2(256)	N
LAST_STEP_ON_ROUTE	VARCHAR2(256)
LOT_GROUP	VARCHAR2(8)	N	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)	N	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_OF_PRD	VARCHAR2(256)	N
MERGE_TO_LOT_ON_FINISH	VARCHAR2(512)
MIN_BANK_ENT_INST	DATE
MIN_OVERALL_ENT_INST	DATE	N
MIN_STEP_ENT_INST	DATE
NEXT_FACILITY	VARCHAR2(6)		Event moved the lot to this new facility (* from FPSINPUT.WIP_EVENT_HIST)
NEXT_PRD	VARCHAR2(64)		Event moved the lot to this new prd (* from FPSINPUT.WIP_EVENT_HIST)
NUM_PHOTO_LAYERS	NUMBER(3)
NUM_STEPS_COMP	NUMBER(4)
ORDER_ID	VARCHAR2(64)		The customer order to which this lot belongs. (* from FPSINPUT.WIP_LOTS_STATIC)
OVERLAPPING_BANK_SEC	NUMBER(9)
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)
PLAN_PRIORITY	VARCHAR2(7)	N	Permanent priority of the lot set in the MES generally by planning. (* from FPSINPUT.WIP_EVENT_HIST)
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)
QTY	NUMBER(7)	N	Quantity of units in the lot according the qty_unit defined for the facility. It is required for all lots in each facility to have their qty defined in the same units therefore the change in the unit is critical to defining the facility. For example, a pretest facility might have a sort step in the middle where we learn the qty of die. Prior to this step we know only the wafer qty but after this step we know both wafer and die. Since wafer is the only qty we know throughput the flow then wafer must be defined as the qty unit for this facility. Die can then be populated as sec_qty when it is known. Similarly the wafer saw facility might have a step in the middle where we cut the wafers into die. After this step we no longer know the number of wafers which means that die must be the qty unit for this facility and wafers can be the sec_qty prior to the saw step. Please note that a lot with qty of 0 is allowed but only if the sec_qty is greater than 0. This is unusual but one case is where we know the wafers will be scrapped but cannot be scrapped quite yet. (* from FPSINPUT.WIP_EVENT_HIST)
QTY_AFTER	NUMBER(7)	N
ROUTE_AT_FIRST_STEP	VARCHAR2(256)
ROUTE_AT_LAST_STEP	VARCHAR2(256)
ROUTE_AT_SHIP_EVENT	VARCHAR2(256)
ROUTE_FAMILY	VARCHAR2(36)		Route_family indicates that all routes within the family have similar or even identical steps and have the same segments. At facilities where various prds share the same route it is likely that the route will be the route_family. This is sometimes referred to as the main process flow. It is used on Segment Summary and Line Viewer to group similar routes. (* from FPSINPUT.RTG_ROUTE_FAMILIES)
ROUTE_GROUP	VARCHAR2(36)		Route_group is the parent of route_family. Route_group is used on the Dashboard and other applications as a large grouping for filtering. At many sites this is referred to as technology. (* from FPSINPUT.RTG_ROUTE_GROUPS)
SECOND_BANK	VARCHAR2(36)
SECOND_BANK_SEC	NUMBER(9)
SHIP_INST	DATE
SPLIT_FROM_LOT	VARCHAR2(32)
SPLIT_FROM_LOT2	VARCHAR2(32)
SPLIT_FROM_LOT3	VARCHAR2(32)
SPLIT_FROM_LOT4	VARCHAR2(32)
SPLIT_INST	DATE
SPLIT_INST2	DATE
SPLIT_INST3	DATE
SPLIT_INST4	DATE
START_INST	DATE		Time when the lot started. (* from FPSINPUT.WIP_STARTS)
START_PLAN_DAY	DATE		Plan days must be 24 hours in length and the start of the plan day must be the start of one of the shifts. Contrast this to plan day which is independent of shift. (* from FPSINPUT.CAL_PLAN_DAYS)
START_SHIFT	DATE		Shifts can be of any length and it is important that all queries get the shift length based on start_shift and end_shift rather than assuming a set number of hours. (* from FPSINPUT.CAL_SHIFTS)
STEP_ABORT_SEC	NUMBER(9)	N
STEP_BLOCK_SEC	NUMBER(9)	N
STEP_DISP_SEC	NUMBER(9)	N
STEP_DOWN_SEC	NUMBER(9)	N
STEP_ENDED_SEC	NUMBER(9)	N
STEP_HOLD_SEC	NUMBER(9)	N
STEP_LONGH_SEC	NUMBER(9)	N
STEP_OFFRT_SEC	NUMBER(9)	N
STEP_PROC_SEC	NUMBER(9)	N
STEP_RESRV_SEC	NUMBER(9)	N
STEP_SHUTD_SEC	NUMBER(9)	N
STEP_STG_SEC	NUMBER(9)	N
STEP_TCT_SEC	NUMBER(9)	N
STEP_TOTAL_SEC	NUMBER(9)	N
STEP_WAIT1_SEC	NUMBER(9)	N
STEP_WAIT2_SEC	NUMBER(9)	N
STEP_WAIT3_SEC	NUMBER(9)	N
TARGET_CT_DAYS	NUMBER(5,1)		Number of days of cycle time targeted for the prd or bank. This target is only published internally which often means it is not even published to corporate planning. This should be less than or equal to the commit. (* from FPSINPUT.RTG_BANKS)
THIRD_BANK	VARCHAR2(36)
THIRD_BANK_SEC	NUMBER(9)
TOTAL_BANK_SEC	NUMBER(9)	N
USE_IN_CTR	CHAR(1)	N
WAFER_SIZE	VARCHAR2(8)		If the facility only has one wafer size then this column should be null in both RTG_PRDS and EQP_TYPES. If the facility has more than one wafer size then a value of null in EQP_TYPES means that those tools can run all wafer sizes. See column IS_ANY_WAFER_SIZE for details. (* from FPSINPUT.GEN_WAFER_SIZES)