MSC_DEPARTMENT_RESOURCES
This table stores the resources available for planning
Details
-
Schema: FUSION
-
Object owner: MSC
-
Object type: TABLE
-
Tablespace: TRANSACTION_TABLES
Primary Key
| Name | Columns |
|---|---|
|
MSC_DEPARTMENT_RESOURCES_PK |
PLAN_ID, RESOURCE_ID, NEW_PLAN_ID, SIMULATION_SET_ID |
Columns
| Name | Datatype | Length | Precision | Not-null | Comments | Status |
|---|---|---|---|---|---|---|
| PLAN_ID | NUMBER | 18 | Yes | This value indicates the plan identifier for the record. | ||
| CURRENCY_CODE | VARCHAR2 | 1000 | CURRENCY CODE FOR RESOURCE COST | |||
| RES_AVL_START_DATE | DATE | Start date of the resource availability for the department resource. | ||||
| RES_AVL_END_DATE | DATE | End date of the resource availability for the department resource. | ||||
| APPLIED | NUMBER | 1 | Flag to indicate whether this row contains changed row (this col is not collected). | |||
| ORGANIZATION_ID | NUMBER | 18 | Yes | This column indicates the unique identifier of a given Organization. | ||
| SR_INSTANCE_ID | NUMBER | 18 | Yes | Source application instance identifier. | Obsolete | |
| RESOURCE_ID | NUMBER | 18 | Yes | Source application resource identifier | ||
| DEPARTMENT_ID | NUMBER | 18 | Yes | Source application department identifier or line identifier | ||
| RESOURCE_CODE | VARCHAR2 | 40 | The value indicates resource code collected from source system. | |||
| RESOURCE_DESCRIPTION | VARCHAR2 | 240 | This value indicates the description of the resource. | |||
| DEPARTMENT_CODE | VARCHAR2 | 120 | Department code, also holds line code | |||
| DEPARTMENT_NAME | VARCHAR2 | 80 | Name of the Work Center / Department | |||
| RESOURCE_NAME | VARCHAR2 | 80 | This value indicates the Name of the Resource. | |||
| WORK_AREA_NAME | VARCHAR2 | 80 | Work area name for the work centre or a resource department. | |||
| WORK_AREA_CODE | VARCHAR2 | 120 | Work area code for the work centre or a resource department. | |||
| WORK_AREA_DESCRIPTION | VARCHAR2 | 240 | Work area code for the work centre or a resource department. | |||
| WORK_AREA_ID | NUMBER | 18 | Work Area identifier on the source side | |||
| DEPARTMENT_DESCRIPTION | VARCHAR2 | 240 | Department description, also hold line description | |||
| DEPARTMENT_CLASS | VARCHAR2 | 10 | Department class to which the resource is attached. | |||
| LINE_FLAG | NUMBER | Yes | Flag to indicate whether or not this resource is a line | |||
| AGGREGATE_RESOURCE_ID | NUMBER | 18 | Reference to aggregate resource, if aggregated | |||
| AGGREGATE_RESOURCE_FLAG | NUMBER | Yes/No flag to indicate whether or not this is an aggregated resource | ||||
| RESOURCE_GROUP_NAME | VARCHAR2 | 30 | This value indicates the Name of the group of resource. | |||
| RESOURCE_GROUP_CODE | VARCHAR2 | 10 | This value indicates the Resource group code. | |||
| RESOURCE_SHORTAGE_TYPE | NUMBER | This value indicates the Resource shortage type. | ||||
| RESOURCE_BALANCE_FLAG | NUMBER | Flag to indicate if the resource needs to load balanced | ||||
| RESOURCE_EXCESS_TYPE | NUMBER | This value indicates the Resource excess type. | ||||
| RESOURCE_INCLUDE_FLAG | NUMBER | Flag to indicae if we need to consider this resource when planning | ||||
| CRITICAL_RESOURCE_FLAG | NUMBER | Flag to enforce capacity constraints only for critical bottleneck resources. Valid values are 1(Yes) and 2(No). | ||||
| OVERUTILIZED_PERCENT | NUMBER | Overutilization tolerance percentage of the department resource. | ||||
| BOTTLENECK_FLAG | NUMBER | Flag to indicate if the resource is a known bottleneck | ||||
| UNDERUTILIZED_PERCENT | NUMBER | Underutilization Tolerance of the resource. | ||||
| OWNING_DEPARTMENT_ID | NUMBER | 18 | Owning department identifier for share resources | |||
| CAPACITY_UNITS | NUMBER | Number of units available during the time interval | ||||
| RUN_RATE | NUMBER | Resource run rate of the department resource. | ||||
| UTILIZATION | NUMBER | Resource utilization collected from the source system. | ||||
| EFFICIENCY | NUMBER | This value indicates the resource efficiency. | ||||
| USER_TIME_FENCE | NUMBER | Number of days within which planning will generate an exception message | ||||
| RESOURCE_TYPE | NUMBER | This value indicates the Resource type. | ||||
| DISABLE_DATE | DATE | Date on which resource/line was disabled | ||||
| AVAILABLE_24_HOURS_FLAG | NUMBER | Yes | Resource is available 24 hours or by shifts | |||
| MIN_RATE | NUMBER | Hourly minimum rate of production line | ||||
| MAX_RATE | NUMBER | Hourly maximum rate of production line | ||||
| LINE_DISABLE_DATE | DATE | Line disable date specified for the department resource. | ||||
| START_TIME | NUMBER | Start time of the process. This is used in calculating the total time consumed by the process. | ||||
| STOP_TIME | NUMBER | Stop time of the process. This is used in calculating the total time consumed by the process. | ||||
| CTP_FLAG | NUMBER | Flag indicating whether the department resource is used for ATP or not | ||||
| LAST_UPDATE_DATE | TIMESTAMP | Yes | Who column: indicates the date and time of the last update of the row. | |||
| LAST_UPDATED_BY | VARCHAR2 | 64 | Yes | Who column: indicates the user who last updated the row. | ||
| CREATION_DATE | TIMESTAMP | Yes | Who column: indicates the date and time of the creation of the row. | |||
| CREATED_BY | VARCHAR2 | 64 | Yes | Who column: indicates the user who created the row. | ||
| LAST_UPDATE_LOGIN | VARCHAR2 | 32 | Who column: indicates the session login associated to the user who last updated the row. | |||
| ATTRIBUTE_CATEGORY | VARCHAR2 | 30 | Descriptive Flexfield: structure definition of the user descriptive flexfield. | |||
| ATTRIBUTE1 | VARCHAR2 | 150 | Descriptive Flexfield: segment of the user descriptive flexfield. | |||
| ATTRIBUTE2 | VARCHAR2 | 150 | Descriptive Flexfield: segment of the user descriptive flexfield. | |||
| ATTRIBUTE3 | VARCHAR2 | 150 | Descriptive Flexfield: segment of the user descriptive flexfield. | |||
| ATTRIBUTE4 | VARCHAR2 | 150 | Descriptive Flexfield: segment of the user descriptive flexfield. | |||
| ATTRIBUTE5 | VARCHAR2 | 150 | Descriptive Flexfield: segment of the user descriptive flexfield. | |||
| ATTRIBUTE6 | VARCHAR2 | 150 | Descriptive Flexfield: segment of the user descriptive flexfield. | |||
| ATTRIBUTE7 | VARCHAR2 | 150 | Descriptive Flexfield: segment of the user descriptive flexfield. | |||
| ATTRIBUTE8 | VARCHAR2 | 150 | Descriptive Flexfield: segment of the user descriptive flexfield. | |||
| ATTRIBUTE9 | VARCHAR2 | 150 | Descriptive Flexfield: segment of the user descriptive flexfield. | |||
| ATTRIBUTE10 | VARCHAR2 | 150 | Descriptive Flexfield: segment of the user descriptive flexfield. | |||
| ATTRIBUTE11 | VARCHAR2 | 150 | Descriptive Flexfield: segment of the user descriptive flexfield. | |||
| ATTRIBUTE12 | VARCHAR2 | 150 | Descriptive Flexfield: segment of the user descriptive flexfield. | |||
| ATTRIBUTE13 | VARCHAR2 | 150 | Descriptive Flexfield: segment of the user descriptive flexfield. | |||
| ATTRIBUTE14 | VARCHAR2 | 150 | Descriptive Flexfield: segment of the user descriptive flexfield. | |||
| ATTRIBUTE15 | VARCHAR2 | 150 | Descriptive Flexfield: segment of the user descriptive flexfield. | |||
| REFRESH_NUMBER | NUMBER | Refresh identifier populated by the collection program. | ||||
| RESOURCE_COST | NUMBER | The value indicates resource code collected from source system. | ||||
| RESOURCE_OVER_UTIL_COST | NUMBER | This value indicates the Resource overutilization cost. | ||||
| DEPT_OVERHEAD_COST | NUMBER | Department overhead cost of the resource. | ||||
| PLANNING_EXCEPTION_SET | VARCHAR2 | 10 | Planning Exception Set defined for the department resource. | |||
| BATCHABLE_FLAG | NUMBER | Flag indicates whether the resource can be added to a batch. 1-Yes, 2-No. | ||||
| BATCHING_WINDOW | NUMBER | The number of days a given resource batch spans across. | ||||
| MIN_CAPACITY | NUMBER | This value indicates the minimum capacity for a specific resource. | ||||
| MAX_CAPACITY | NUMBER | This value indicates the maximum capacity for a specific resource. | ||||
| UNIT_OF_MEASURE | VARCHAR2 | 3 | Unit of Measure of the department resource record. | |||
| UOM_CLASS_TYPE | VARCHAR2 | 10 | Unit of measure class type of the resource. | |||
| ATP_RULE_ID | NUMBER | 18 | Identifier of the available to promise rule used. | |||
| AVAIL_FACTOR | NUMBER | This value indicates the availability factor of the resource in a department. | ||||
| CAPACITY_TOLERANCE | NUMBER | The value indicates change in resource capacity tolerance in percentage(OPM). | ||||
| CHARGEABLE_FLAG | NUMBER | If checked then resource is chargeable (OPM)1 Yes, 2 No | ||||
| LAST_KNOWN_SETUP | NUMBER | Last know setup associated with this resource. | ||||
| SCHEDULE_TO_INSTANCE | NUMBER | Resource to be scheduled for instances. 1 Yes 2 =no | ||||
| SDS_SCHEDULING_WINDOW | NUMBER | Scheduling window for sds resource | ||||
| IDLE_TIME_TOLERANCE | NUMBER | For sds resource, what is the idle time ( in hours) resource can stay idle | ||||
| BATCHING_PENALTY | NUMBER | Batching Penalty = 0, if the activity is added on to a currently scheduled batch Batching Penalty = pb, if the activity is scheduled as a new batch | ||||
| SETUP_TIME_PERCENT | NUMBER | Threshold percentage for Resource performing excessive setup | ||||
| UTILIZATION_CHANGE_PERCENT | NUMBER | Threshold percentage for Resource utilization change exceeds threshold | ||||
| SETUP_TIME_TYPE | NUMBER | Exception horizon for Resource performing excessive setup | ||||
| UTILIZATION_CHANGE_TYPE | NUMBER | Exception horizon for Resource utilization change exceeds Threshold | ||||
| OBJECT_VERSION_NUMBER | NUMBER | 9 | Yes | Used to implement optimistic locking. This number is incremented every time that the row is updated. The number is compared at the start and end of a transaction to detect whether another session has updated the row since it was queried. | ||
| NEW_PLAN_ID | NUMBER | Yes | This indicates the new plan identifier if the base plan exists for this record. | |||
| NEW_PLAN_LIST | VARCHAR2 | 256 | List indicates the new plan identifiers for the record. | |||
| SIMULATION_SET_ID | NUMBER | Yes | Simulation set to which the current record is added. | |||
| UNDER_UTILIZED_PERCENT | NUMBER | Underutilization Tolerance of the resource. | Obsolete | |||
| OVER_UTILIZED_PERCENT | NUMBER | Overutilization tolerance of the department resource. | Obsolete | |||
| SIM_EDIT_FLAGS | NUMBER | 19 | Indicates which columns are edited in simulation set records |
Foreign Keys
| Table | Foreign Table | Foreign Key Column |
|---|---|---|
| msc_net_resource_avail | msc_department_resources | PLAN_ID, RESOURCE_ID, NEW_PLAN_ID, SIMULATION_SET_ID |
| msc_resource_shifts | msc_department_resources | PLAN_ID, RESOURCE_ID, NEW_PLAN_ID, SIMULATION_SET_ID |
| msc_resource_changes | msc_department_resources | PLAN_ID, RESOURCE_ID, NEW_PLAN_ID, SIMULATION_SET_ID |
Indexes
| Index | Uniqueness | Tablespace | Columns | Status |
|---|---|---|---|---|
| MSC_DEPARTMENT_RESOURCES_N1 | Non Unique | Default | LINE_FLAG, PLAN_ID, ORGANIZATION_ID | |
| MSC_DEPARTMENT_RESOURCES_N2 | Non Unique | Default | PLAN_ID, ORGANIZATION_ID, RESOURCE_ID | |
| MSC_DEPARTMENT_RESOURCES_N3 | Non Unique | Default | PLAN_ID, RESOURCE_GROUP_NAME, RESOURCE_ID, DEPARTMENT_ID | |
| MSC_DEPARTMENT_RESOURCES_N4 | Non Unique | Default | PLAN_ID, RESOURCE_ID, CTP_FLAG | |
| MSC_DEPARTMENT_RESOURCES_U1 | Unique | Default | PLAN_ID, RESOURCE_ID, NEW_PLAN_ID, SIMULATION_SET_ID | |
| MSC_DEPARTMENT_RESOURCES_U2 | Non Unique | Default | PLAN_ID, RESOURCE_ID, DEPARTMENT_ID | Obsolete |
| MSC_DEPARTMENT_RESOURCES_U3 | Unique | Default | PLAN_ID, ORGANIZATION_ID, DEPARTMENT_CODE, RESOURCE_CODE, NEW_PLAN_ID, SIMULATION_SET_ID |