USM Material Requirement Planning System

ISYE582 Engineering Information Systems
Course Project Report 2
Spring 2013

Executive Summary

US Materials, Inc. (USM) is an American semiconductors manufacturing corporation, serving the semiconductor, liquid crystal display (LCD) devices, Glass and solar (crystalline and thin film) manufacturing industries. US Materials (USM) is the global leader in providing innovative equipment, services and software to the semiconductor, flat panel display and solar photovoltaic industries. USM has over a half million unique spare parts (each part has a unique part number) to be purchased from its vendors and stocked in inventories in order to carry out its manufacturing processes and support its customer requirements. USM has three main Central Distribution Centers (CDC), America, Europe and Asia. There are many depots and sub-depots under these distribution centers. CDCs are generally sourcing locations, because parts are procured at CDCs from external/internal vendors and then shipped to depots and then to sub depots. A supply chain management software called the i2 planning tool is used for demand planning and material requirement planning of USM spare parts. i2 planning is customized, maintained and owned by a third party company ABC.

ABC collects logics, rules, theories and data used by USM for planning purposes and includes them into i2 software. The supply chain planning team from USM will be coordinating and guiding the ABC team in building the logics into i2 software. The customization of i2 software for USM was done ten years ago. ABC refreshes the USM databases using i2 software on a weekly basis. i2 is linked to all USM oracle databases for static data and SAP tables for dynamic data. The outputs from the i2 refresh are various reports and database tables. Some important tables are the location-wise forecast and the shortage report for each spare part in the USM network. These reports and tables are used by USM in generating many other tables using MS-Access and Brio. These tables/reports are then used for various decision making activities like forecasting and inventory management of spare parts.

For effective material requirement planning, USM management has decided to streamline the data used for planning purposes. The project team came forward to take this task of building a new streamlined database for the procurement purpose of USM.

As a part of needs analysis, project team conducted interviews, gathered information from questionnaires. Project team also performed gap analysis between current and expected system. Based on the requirements, a process model was developed using DFDs, and a data model is developed using entity-relation diagrams. Obligatory and non-obligatory analysis was conducted and a final database was designed using MS-Access. Tables, forms, reports and queries were built in access.

Once final database is developed, it was thoroughly tested using test procedures for all the tables, forms, reports, queries and navigation menus. After continuous testing till no more errors were found, database was implemented.

1. Introduction
Material requirement planning (MRP) is intended to ensure materials are readily available to support production thereby ensuring on-time delivery of products to customers. MRP is simple for small and medium manufacturing companies, where product varieties manufactured by each company are fewer. However, there are many complications for a large scale manufacturing company with large product variety and with multiple manufacturing locations spread across the globe. The system is complicated even more when multiple vendors exist for a single product and customers are globally distributed. For such companies, the MRP system should collect demand and supply data for all the products from all the locations, calculate current inventory levels at all the