FPSADMIN Tables

OWNER, VIEW_NAME

Summary of all CHK views populated by procedure.

OWNER, VIEW_NAME

In this configuration table, we set the minimum number of rows required for each CHK_xxx views in order for it to be included in the alert message. We can also disable the view from being included in the alert. For example, if set 50 as the minimum for CHK_EQP_HIST then the ADM_CHK_SUMMARY email will include a row for CHK_EQP_HIST only if the view has more than 50 rows. In general, we will most of the FPSADMIN CHK views to 0 and the FPSBASE views to a higher minimum.

OWNER, OBJECT_NAME, LINE

Stores text length of views, constraints, and PL/SQL source including nonwhitespace length for comparison purposes. The table is needed to convert the text column in ALL_VIEWS from the Oracle long datatype to the clob datatype. Long and clob are both extended length string types and Oracle has weird requirements that the only way to convert is to write to a table. If length_from_all_views is not equal to length_from_text the view likely contains illegal characters

ONE_ROW

Stores critical values that are used to determine if the ETL is not updating fast enough. One row for the table and it contains the refresh time of GEN_DATA_DATE and the last commit for RTG_PROCESS_FAMILIES_PLUS.

INSERTED_TIME, SID, SERIAL#

Log table that stores the results of ADM_SESSIONS_PROBLEM where SHOULD_KILL = Y. If a session is allowed to be killed based on IS_AUTO_KILL_ALLOWED, then the session was marked for kill when this record was logged.

DAY, OBJECT_NAME, OBJ_OWNER, OBJ_TYPE, APPLICATION, COMPONENT

See comments in ADM_APD_LOAD_DAILY_HIST view.

Detailed history of all ADM_LOAD transactions for all of the different load types. All applications which load data insert directly into this table and then a trigger on this table populates ADM_LOAD_STATUS.

OBJECT_NAME

Current status of all objects loaded by ADM_LOAD. This table is populated by a trigger on ADM_LOAD_HIST and is never updated directly.

This is a snapshot every minute of queries which have locked a table longer than one minute. It shows the temp space usage, undo space usage, progress, and other metrics to help us track our database stress over time.

DAY, SPEED_CLASS, SPEED_GROUP, FACILITY, TOOL_LOT_INST, VALUE_DEFINITIONS

See comments in ADM_APD_SPEED_DAILY_HIST view.

A table to store results of speed tests written by the ADM_SPEED_HIST_INSERT procedure.

OWNER, TABLE_NAME, PK_COLUMN1_VALUE, PK_COLUMN2_VALUE

This table stored the values used in the loop of ADM_TABLE_LOOP since dynamic queries are not allowed within a loop.

INSTALL_INST

Installation history of all versions of the FPS DWH and the database objects for each application. Please note this it is allowed to install the same version repeatedly which often happens during development and testing of a new version.

OBJECT_NAME, GRANTEE, PRIVILEGE

All grants in the FPS DWH are set automatically based on the logic in BLD_PRIVILEGES but occasionally a site will need to add grants, particularly to select from FPSAPP objects in FPSRPT. This is generic so we can add any site specific privilege.

TABLE_NAME, COLUMN_NAME

List of individual columns which should be ignored by the ETL in tables which are otherwise updated automatically. For tables updated by ADM_TABLE_xxx procedures, these columns will be ignored automatically. For tables updated by APF, the BLD_TABLE_QUERIES view will ignore these columns but we must copy/paste from this view into the APF report so this is not automatic.

OBJECT_NAME

Configurable list of everything in the database which is temporarily or permanently disabled including objects to load, load jobs, all FPSAPP objects to load with a certain prefix, triggers, and constraints. If the is_permanent flag is set to Y then this will not show in SYSMNTR_DISABLED as it is permanently disabled at this client for some good reason.

Configurable info on files to update using ADM_LOAD.

List of jobs for ADM_LOAD. No foreign key because ADM_LOAD_CFG_SCHED has ADMA and IF versions but this is used in CHK_LOAD.

OBJECT_NAME

This table allows members of the DWH team to properly inform everyone on the site team about known DWH errors. As soon as anyone on the DWH team starts to investigate an error they should insert a record in this table with stop_alert_until_inst blank. This will add their name and the note to the SYSMNTR email so that everyone knows it is being investigated. Once they fix the error then they should delete this record. Of course it will be stored in the CHGLOG table for historical tracking purposes. If for some reason the error cannot be fixed right away -- for example an MES change is required -- they should populate stop_alert_until_inst which will remove errors on this object from our SYSMNTR email. The errors will still be in the SYSMNTR_ERRORS view. If the errors persists past the stop_alert_until_inst then we will start emailing again. Finally if the object is no longer failing but is still in this table then we will include a message in SYSMNTR_ERRORS that the record should be removed (but we will not email about this).

EVENT

Occasionally there are corner cases to the ETL that we simply cannot handle properly in WIP_EVENT_HIST. One example is lots going on hold twice then released and unsure whether still on hold. Another is hold type not correctly populated with the event. This table allows us to record acceptable miss events so they are not reported by SYSMNTR_HEALTH.

OBJECT_NAME

This table allows members of the DWH team to hide known out-of-control and repeated errors from SYSMNTR_OOC_AND_REPEATED. This can be permanent or temporary.

List of procedures and scripts to run using ADM_LOAD.

PROCEDURE_NAME

List of procedures to run using ADM_LOAD.

Provided with the FPS DWH is a robust loading system that can either be driven via scheduler jobs within the Oracle database itself via cron on a UNIX/Linux system. <C2><A0>Standard load types are done via APF/RTD and PL/SQL. <C2><A0>C# loaders can also be provided/written if necessary or we could potentially use any other ETL tool that is available. This particular table is where the schedule is maintained so all other ADM_LOAD tables support the schedule specified here.

USERNAME_OR_ANY, OSUSER_OR_ANY, MODULE_OR_ANY

Configuration settings that determine what sessions are considered hung based on username, osuser, and module from v$session. At least one of these must be specified and the others must be listed as ANY. The order of precedence is module, then osuser, then username. Settings do not overlap so whichever rows is deemed the most relevant will its settings applied and no others.

Configurable info on tables to update using ADM_LOAD.

WHY_CODE

Explanations of the different cycle time fill-in groups that can be used when estimating cycle time for levels where not enough valid data points exist. The reason codes are referenced in the CTM_SUMMARY "WHY" columns.

PERIOD_TYPE

List of the valid period types which can be used in Finished Lot Cycle Time and Cycle Time Forecast tables. The FLCT application includes a filtering option for period type with these values.

Database Checker table to store comparison of data between this instance and the referenced linked instance.

OWNER, TABLE_NAME, GRANTEE, GRANTOR, PRIVILEGE, GRANTABLE

Database Checker table to store grants which changed between this instance and the referenced linked instance.

THIS_OWNER, THIS_OBJECT_NAME

Database Checker table to store objects which are renamed between this instance and the referenced linked instance.

Database Checker table to store procedures which changed between this instance and the referenced linked instance.

SEQUENCE_OWNER, SEQUENCE_NAME

Database Checker table to store sequences which changed between this instance and the referenced linked instance.

OWNER, SYNONYM_NAME, TABLE_OWNER

Database Checker table to store synonyms which changed between this instance and the referenced linked instance.

OWNER, TABLE_NAME, TABLESPACE_NAME

Database Checker table to store tables which changed between this instance and the referenced linked instance.

OWNER, VIEW_NAME

Database Checker table to store views which changed between this instance and the referenced linked instance.

See comments in DBF_REF_ARGUMENTS view.

SOURCE_OBJECT_NAME, OWNER, TABLE_NAME, COLUMN_NAME

See comments in DBF_REF_COLUMNS_FROM_SOURCE view.

OWNER, TABLE_NAME, COLUMN_NAME

See comments in DBF_REF_COL_COMMENTS view.

See comments in DBF_REF_CONSTRAINTS view.

OWNER, CONSTRAINT_NAME, COLUMN_NAME

See comments in DBF_REF_CONS_COLUMNS view.

See comments in DBF_REF_DEPENDENCIES view.

See comments in DBF_REF_DEV_ONLY_OBJECTS view.

OWNER, NAME, TYPE, SEQUENCE

See comments in DBF_REF_ERRORS view.

OWNER, INDEX_NAME

See comments in DBF_REF_INDEXES view.

INDEX_OWNER, INDEX_NAME, COLUMN_NAME

See comments in DBF_REF_IND_COLUMNS view.

JOB

See comments in DBF_REF_JOBS view.

See comments in DBF_REF_OBJECTS view.

See comments in DBF_REF_PROCEDURES view.

OWNER, JOB_NAME

See comments in DBF_REF_SCHEDULER_JOBS view.

SEQUENCE_OWNER, SEQUENCE_NAME

See comments in DBF_REF_SEQUENCES view.

See comments in DBF_REF_SESSIONS view.

See comments in DBF_REF_SESSION_COUNT view.

OWNER, NAME, TYPE, LINE

See comments in DBF_REF_SOURCE_FOR_CHK view.

OWNER, SYNONYM_NAME

See comments in DBF_REF_SYNONYMS view.

OWNER, TABLE_NAME

See comments in DBF_REF_TABLES view.

TABLESPACE_NAME

See comments in DBF_REF_TABLESPACES view.

OWNER, TABLE_NAME, COLUMN_NAME

See comments in DBF_REF_TAB_COLUMNS view.

OWNER, TABLE_NAME, COLUMN_NAME

See comments in DBF_TAB_COL_DEFAULTS_CUSTOM procedure.

OWNER, TABLE_NAME

See comments in DBF_REF_TAB_COMMENTS view.

TABLE_OWNER, TABLE_NAME, PARTITION_NAME

See comments in DBF_REF_TAB_PARTITIONS view.

OWNER, TRIGGER_NAME

See comments in DBF_REF_TRIGGERS view.

OWNER, TYPE_NAME

See comments in DBF_REF_TYPES view.

OWNER, TYPE_NAME, ATTR_NAME

See comments in DBF_REF_TYPE_ATTRS view.

OWNER, VIEW_NAME

See comments in DBF_REF_VIEWS view.

CHANGE_TYPE

Default change types to be shown in NextMove

MNT_SCHED_STATUS

List of FPS universal schedule statuses for maintenance events.

OPERATIONAL_STATE

Industry standard level 1 operational states. This is the top of the hierarchy so these do not have parent states.

AVAILABILITY

Industry standard level 2 availability. This is also known as equipment availability as Engineering time is included.

E10_STATE

Industry standard level 3 E10 states.

OPPORTUNITY_STATE

Level 4 is FPS Enhanced Tool Performance opportunity states. This level is critical because opportunity state is the default grouping on all Tool Performance charts and determines the color on all views including Dashboard and Gantt charts. This table is slightly different from other FPSADMIN tables in that we do allow sites to make minor modifications to this table, for example adding a new opportunity state SBY-WIPFAR and assigned transition_state Standby With WIP Faraway to this opportunity state so we can give it a different color. Both check and referential constraints are in place to ensure that any changes do not break anything.

Level 5 is FPS Enhanced Tool Performance families. This table is for grouping purposes between L4 and L6.

SBY_OR_CSC_STATE_SUFFIX

Most standby states are set used the best rank of available WIP and that information is all in RTG_AUTO_RANKS and RTG_PERM_RANKS but these states are set later in ETP_SBY_STATE view so we need an additional table.

ETP_STATE

Level 5 is FPS Enhanced Tool Performance states. ETP only modifies the eqp_state when it is PRD or SBY so we only include the etp_states to which will modify. ENG, SDT, UDT, and NST states are not included because we keep the eqp_state.

ETP_STATE

This table is a list of ETP states which we plan to add in the future. This table is not used but exists only for reference.

ETP_STATE

This table stores old ETP states which are no longer used but may be in ETP_HIST and the various ETP_TOOL/ENTITY_SHIFT/WEEK_HIST tables.

List of the 140 standard HTML CSS colors. We use only these 140 standard colors in FPS applications.

TABLE_NAME, COLUMN_NAME

Lists columns in FPSINPUT tables which are updated by triggers. This is GEN rather than CFG because this is not configurable. Our APF load reports and ADM_AUTO_TABLE_REFRESH procedures will ignore these columns.

IGNORE_CODE

Reference table listing all options for ignore code in CFG_COLUMNS_TO_IGNORE with full information.

ICON_NAME

List of icon sets that can be used by web applications and by referencing html class values.

METHOD

List of allowable METHOD values used in ADM_LOAD_CFG tables.

MSG_CODE

List of allowable MSG_CODE values used in ADM_LOAD_STATUS and ADM_LOAD_HIST.

PREFIX

List of allowed prefixes in FPS database.

CARRIER_CLASS

List of FPS defined values allowed for the carrier class. Restricting carrier_class to a value defined in this table allows our applications to make specific references to these values.

CARRIER_STATE

List of FPS defined values allowed for the carrier state. Restricting carrier_state to a value defined in this table allows our applications to make specific references to these values.

STATION_TYPE

List of FPS defined values allowed for the station type. These are defined in MHS_REF_LOCATIONS so it should not be possible to have a value nont in this table unless we made a global DWH change and do not add a record here.

TRANSPORT_MODE

List of allowed transport modes that are used by various applications. Client transport modes are mapped to these modes in FPSINPUT.

VEHICLE_STATE

List of FPS defined values allowed for the vehicle state. Restricting vehicle_state to a value defined in this table allows our applications to make specific references to these values.

SEQUENCE_OWNER, SEQUENCE_NAME

This table lists the MSO sequence objects and what table and column they populate via table triggers. The purpose of this table is for the ADM_MSO_UTILITIES package to be able to recreate sequences when rules are copied from another database to ensure the copy procedures can reset the sequence value based on the current value or highest value in the table.

Configuration table that contains the list of MSO tables used by the admin utility package (ADM_MSO_UTILITIES) used to copy rules, copy data and replaying and validating data.

RANK

List of FPS-defined ranks which we dynamically set in WIP_STEP_FUTURE_ASSIGNMENTS

EXTL_ID_STATE

List of FPS external id states. This is sometimes referred to as external model. See the description column for details about the meaning of each of the allowed states.

RANK

Defines list of possible permanent ranks for tool and durable assignments set by ETL in the RTG_TOOL_ASSIGNMENTS family of tables.

PROCESS_CLASS

See comments on FPSINPUT.EQP_TOOLS for detailed information about EQP hierarchy.

TABLE_NAME, COLUMN_NAME

List of columns which must be populated during the DWH startup phase even though a null value is acceptable. Normally we want to make required columns not null but these are cases where that is not feasible.

TABLE_NAME

List of tables which must have data for basic functionality of the DWH, Dashboard, NextMove, and Scheduler.

THP_IGNORE_REASON

Reasons why we would not use a particular job in throughput calculations.

DELTA_TYPE

List of FPS universal delta types. Please note that if delta_type is null in WIP_STEP_HIST that the qty did not change

ECT_STATE

List of FPS universal ECT states used to build ECT_STATE_DIAGRAM. This table is rarely directed used since nearly all uses will point to ECT_STATE_DIAGRAM.

EVENT_TYPE

FPS standard lot event types to use with FK to ensure event_type column in all tables has values from our standard list.

MHS_STATE

List of FPS universal MHS states used in ECT logic.

MOVE_TYPE

List of FPS universal move types.

PROCESS_STATE

List of FPS universal process states.