DESIGN & DEVELOP WAREHOUSE AND INVENTORY SYSTEM

(1)

SIM UNIVERSITY

SCHOOL OF SCIENCE AND TECHNOLOGY

DESIGN & DEVELOP WAREHOUSE AND

INVENTORY SYSTEM

STUDENT

: CHAN KOK CHERN (Q0704653)

SUPERVISOR

: MR KOH KIM BOON

PROJECT CODE : JAN2011/ICT/0015

A project report submitted to SIM University

in partial fulfilment of the requirements for the degree of

BSc (Honours) Information Technology and Business

(2)

ABSTRACT

In today’s competitive market, it orders to stay competitive and survive, the warehousing company need to be efficient and effective in their business flow like reducing storage capacity, increasing inventory accuracy and customer service and decreasing labour cost and high inventory wastage in it. In order to do that, most companies tends to invest in technology to achieve this goal by buying third party commercial software application or requesting software companies to customise it to aid them in their daily operation.

The main function or component of a warehouse management system (WMS) is to track the location and movement of the goods for easy retrieving and locating, tracking of the stock number for each product, configuring of stock management (FIFO or LIFO), generating of each products details, generate and create orders and etc.

For this final year project, my project supervisor and I are designing and customizing a warehouse management system (WMS) that is specially cater for small and medium enterprise warehouse companies needs.

(3)

ACKNOWLEDGEMENTS

First and foremost I would like to specially thank my project supervisor Mr Koh Kim Boon for his valuable advices, suggestions and design for this warehouse management system (WMS). During the design and implementation of the project phrase, he has wasted much of his precious time to give directions, care, motivation, encouragement and guidance me which has been a great learning value for me.

Lastly, I would also like to thank my family members and working colleague for their continued support, encouragement and understanding during the work of this project and also my best friends for their words of encouragement.

(4)

1. Introduction

1.1. Background

In today world, the demand for network-based warehouse management system (WMS) is very huge as we are able to search that there are various type of Commercial WMS available in the market for potential buyer to choose from base on the company requirement in the internet. WMS main purpose is to maximize the effectiveness and efficiency of the organization business flow and most important things is to reduce the time and the manpower costs in the organization in order to stay competitive in this globalization market. For example, it is important for an organization want to keep track of its item shell life in its warehouse inventory as expired items bring losses to the organization. Without any WMS, the organization will have to increase its manpower to keep track of each items shell life in the inventory which can be very expensive in the long run.

The primary goal of a warehouse management system is to control the movement and storage of item within an operation and process the associated transactions. Other goals include direct picking of the location, replenishment, and direct put away are also the key operation to the system. After all, the overall objective of this project is to manage the inventory of the warehouse to ensure item or product are always stocked and business operation are efficient and effective.

Not only that, the other goal of a warehouse management system is also to store historical information or records about organization transactions for a timeframe, and then present it in a statistical view to allow the organization to analysis it and then make important decisions for the organization in terms of which item currently in demand in the market and which item is not. For example, organization will generate report on which items belong to the fast moving product or which items belong to the slow moving product and then the companies are able to determine which product needed to bring in more.

(6)

1.2. Project Objective

This project aims to develop a Java-Based Web Application that handles the process of the following few main functions in the Warehousing Management System (WMS), that are:

Login

 Access the system Inventory

 Tracks Item's Shelf Life

 Tracks Item’s Movement & Location

 Tracks Item’s Quantity

 Support FIFO and FILO distribution

 Support Item’s Statistical Selling Report Customer

 Tracks Customer’s Profile

 Tracks Customer’s Order and Transaction Records

 Tracks Item’s Selling Price to Customers

 Generate and Print Customer’s Invoice Supplier

 Tracks Supplier’s Profile

 Tracks Item’s Buying Price from Supplier

If time permits, as stated in Figure 1.1 week 40 and 41 (WMS enhancement and refinement), additional function will be included in the project.

Supplier

 Tracks Supplier’s Order and Transaction Records

(7)

Gantt chart

(8)

1.3. Projects Deliverables

In this WMS project, the system will be separate into a few main modules that will be developing for different user role usage.

The list of the following module for the warehouse management system is as followed:

Login Module

 Access the System

In this Project, the focus is mainly on the operational business flow of the system and not the security part. This means that the system only provide a little authentication which means whoever able to login is able use the application.

Inventory Management Module

 Search Item Quantity

 Search Item Location

 Search Empty Slot

 Pick-Up Order

 Add New Item

Inventory Management Module is the core of this system. In this module, there are operational functions like keeping track of the product information in terms of quantity, location and status. For example, item quantity, location in the warehouse, item shelf life and expiry date and etc. Update the product quantity when stock arrival to the warehouse, show the customer order and where to pick up the items from, view the entire warehouse empty slot or rack to determine how much capacity can be put into it and lastly create a new item or product to be in the warehouse.

Customer Account Module

 Create Account

 Update Account

(9)

 Create Customer Item Special Pricing

Customer Account Module is basically to maintain the client information like for example create or update client company address, credit terms, country location and item special pricing. This module also allows the sale team to look into the item information and the default selling price.

Supplier Account Module

 Create Account

 Update Account

 Create Product

Like customer account module, Supplier Account Module also maintains the supplier information except that this module can create new product like for example, supplier release a new product to the market which purchaser will have to key in the item into the system.

Order Management Module

 Create Order

 Update Order

Order Management Module is to create or update customer order.

Invoice Management Module

 Print Invoice

Invoice Management Module is to print the order that is going to deliver to the customer.

Generate Report Module

 Generate Sale Report

(10)

1.4. System Structure

In software engineering, multi-tier architecture (often referred to as n-tier architecture) is a client–server architecture in which the presentation, the application processing, and the data management are logically separate processes. For example, an application that uses middleware to service data requests between a user and a database employs multi-tier architecture. The most widespread use of multi-tier architecture is the three-tier architecture.

3-tier application architecture provides a model for developers to create a flexible and reusable application. By breaking up an application into 3 tiers, developers only have to modify or add a specific layer, rather than have to rewrite the entire application over. There should be a presentation tier which is the User Interface, a business or data access tier which is the Web Application, and a data tier which is the Database. [1]

Below is the diagram of Warehouse Management System (WMS) Implementation of the 3-tier Architecture:

3-tier System Architecture Figure 1.2

In this Warehouse Management System, Client or User will interact with the module’s function of the system via the Graphical User Interface (GUI) which then communicates with

(11)

will be via through the Application server to return to the user. For example, a Sale Person would want to create a customer account and he/she will enter the customer information via the GUI and click on the create button. Information of the customer will be process in the Application Server which will then insert into the database. The reason of using 3-tiers Architecture is to make sure that the system design can be scalability which means that customising to each companies needs would be much easier.

(12)

2. Background Study and Literature Review

In the old days, setting up a warehouse management system for a warehousing company especially for SME is not very popular due to the high cost factor like setting up the system environment, user training for familiarization and developing the application. Due to this reason, these companies tend to rely on huge manpower to maintain the business operation flow which many of the time prone to lots of mistake due to human error, miscommunication, company politic and etc.

Gradually, as technology has become more and more advances, things has started to change due to the fact that setting up a warehouse management system has become much cheaper compare to the old days. At the same time warehousing company owner has also begin to realise that in order to stay competitive in this business sector especially in the globalization era, the company business operation flow must be effective and efficiency and labour cost must be low in order to gain competitive edge. These companies realise that in order to achieve that, they will have to use the power of technology to assist them and so they started to invest in it.

As more and more warehousing companies are starting to invest on technology to assist in their business operation, warehouse management system has started to become very popular and begin to have a huge demand in the market. Because of this, many software companies took this opportunity to start developing the warehousing management systems to sell. As we search in the internet, there are many various types of warehouse management system available in the market. Although, there are so many types of

warehouse management system in the market, all the function of the system are about the same due to the fact that they design their software system (third party software) basically cater for general purpose.

This in term does not fully benefit the company that is using the third party software because individual company operation in individual ways and so each company may want their system to operate, function or react in different ways which a third party software may not able to do it.

(13)

Basically, most of the basic function for a warehouse management system should have is shown in Appendix F of Daifuku White Paper. For this project that is going to develop, the team is going develop a system that is much more customise which is specially cater for SME companies that has only a single warehouse compare to third party software. In additional to that, the system that is developing will be much more scalability in order to cater for future enhancement or creating additional module.

(14)

3. Proposed Approach and Method

Software Development Life Cycle (SDLC)

Figure 3.1

The Systems Development Life Cycle (SDLC), or Software Development Life Cycle in systems engineering, information systems and software engineering, is a process of creating or altering information systems, and the models and methodologies that people use to develop these systems. It is a process used by a systems analyst or developer to develop an

information system that includes system requirements, design, coding, validation and testing, and user acceptance test (UAT). Any SDLC should result in a high quality system that meets or exceeds customer expectations, reaches completion within time and cost estimates, works effectively and efficiently in the current and planned Information Technology infrastructure, and is inexpensive to maintain and cost-effective to enhance. [2] Figure 3.1 show an image of the waterfall model in the SDLC.

For this Warehouse Management System, we will be using the SDLC concept as taught in ICT 203 and 205 course to develop the system as this concept is widely used for software development. Basically there are 5 main stages for it which are [2]:

(15)

Planning or Requirements Phase

 The planning or requirements phase is to establish a high-level view of the intended project and determines its goals.

Analysis phase

 In this phase, the team will transform the project goals into defined functions and operation of the intended application by analyzing end-user information needs.

Design phase

 At the design phase, the team will produce a list of desired features and operations in detail, which include screen layouts, business rules, process diagrams, pseudo code and other documentation.

Coding and Testing phase

 At the coding phase, the team will base on the design produce in the design phase to code the system. Where else for the for the testing phase, it will brings all the written code together into a special testing environment, then checks for errors, bugs and

interoperability.

Maintenance phase

 In the Maintenance phase, it is maintain the rest of the system's life and during this phase, there will be change request, enhancement, moves to a different computing platform and etc.

3.1. Planning or Requirements Phase

Basically in the planning stage, there are lots of Literature Review and Research is to be done in order to give a good understanding and in-depth of how a warehouse management system purpose, process and operation works that can benefit to an organization. By understanding the warehouse

management system, it allows the developer or analyst easier to list down the function requirements needed for the project. This is done via weeks of research from Internet research and reading of warehouse management system books.

(16)

3.2. Analysis Phase

In the analysis stage, all the functional requirements that is gather or draft out during the planning phase is analysis and brainstorm base on the following criteria which are:

 The timeframe for the project

 The main scope for the project

 Feasibility of building the particular module on time

 Equipment availability

 Project commitment

Based on these criteria, the team will list down the list of the functional

requirement that will be doing for the system and then base on that to draft out a project schedule and milestone for the WMS project so that the team can able to track the project progress and hopefully able to accomplish all the tasks within the project submission deadline.

3.2.1. Use Case and Use Case Diagram

A use case and use case diagram in terms of software engineering and systems engineering, is a description of steps or actions between a user (actor) and a software system which leads the user towards something useful. The user or actor might be a person or something more abstract like for example an external software system or manual process (batch job).

Use cases and use case diagram are a type of software modeling technique which helps developers or programmers to determine which features to implement and how to gracefully to resolve errors. [3]

After the functional requirement has been finalised, the team started working on the use case and use case diagram for the warehouse management system as listed in Appendix A and B. In the Appendix B of the use-case diagram, there are basically 4 actors will be using the system who is the Sale Team, Purchase Team, Store Supervisor and lastly the Admin Team.

(17)

Sale Team

Sale Team role in the system basically involving in the following activities:

 Creating or updating of customer profile

 Creating or updating of orders

 Generating of invoice

 Generating of item report Purchase Team

Purchase Team role in the system basically involving in the following activities:

 Creating or updating of supplier profile

 Creating of new product

 Generating of item report Store Supervisor

Store Supervisor role in the system basically involving in the following activities:

 Maintaining the warehouse space availability

 Picking up the item of the order

 Maintaining the product stocks Admin Team

Admin Team role in the system basically involving in the following activity:

 Generating of invoice

3.2.2. Story Board or Prototyping

Software prototyping refers to the activity of creating prototypes of software applications which is an incomplete version of the software program being developed which an activity that can is done in software development. A prototype is to simulate a few aspects of the final solution, and may be completely different from the final product.

(18)

Prototyping has several benefits when developing a system like for example the business analyst or project manager can get valuable feedback from the users early stages of the project. The client or the user can compare if the software made matches the software specification, according to which the software program is built. It also allows the software engineer to have some insight into the accuracy of initial project estimates and whether the deadlines and

milestones proposed can be successfully met. [4]

After the use case and use case diagram has done, the team started building a prototype for the flow of each use case. From there the team will do some amendment on the prototype before implementing on the development system.

During the designing, the team will be using the Human Computer Interaction (HCI) Design Principle as learn in ICT 203 & 205 to design it. Human–computer interaction (HCI) is the study, planning and design of the interaction between people and computers which is often regarded as the intersection of computer science, behavioral sciences, design and several other fields of study.

Communication is often occur between users and computers via the user interface (or simply interface), which includes both software and hardware like for example, characters or objects displayed by software on a personal

computer's monitor and input received from users via hardware peripherals such as keyboards and mouse. [5]

When using this design principle, there are three main principles need to take note that are:

Affordance

 Is it clear how to use it Visibility

 Is it clear what it is for Feedback

(19)

WMS User Interface Figure 3.2

By implementing this element, it allows the team to build a user-friendly Interface in the Warehouse Management System which user can easily get hold of it without much problem. Figure 3.2 show the screen shot of the WMS Graphical User Interface.

(20)

3.2.3. Entity-Relationship Diagram

Warehouse Management System Database Design Figure 3.2

As for the designing of the system database, using the skill that was taught in ICT 321 and 322 to build on the schema. Basically what is important is when designing a database schema in a relational database management system (RDBMS) is the way how we do normalization. The team will be using Boyce– Codd normal form (BCNF) to do normalization for the Warehouse Management System database to prevent any type of anomalies occurs

Database normalization is the process of organizing data to minimize

redundancy.The goal of it is to decompose relations with anomalies in order to produce smaller, well-structured relations in the database. Normalization usually involves breaking larger tables into smaller ones and defining relationships between them. The objective is to isolate data so that additions, deletions, and

(21)

modifications of a field can be made in just one table and then propagated through the rest of the database via the defined relationships. [6]

Boyce–Codd normal form (BCNF) is a normal form used in database

normalization and was developed in 1974 by both Raymond F. Boyce and Edgar F. Codd to address certain types of anomaly not dealt with by 3NF as originally defined. The main reason of developing the normal form is to have more integrity between tables’ relationship which means that one table will have a superkey (Primary Key) and the other will have candidate key (Foreign Key). [7] In Figure 3.2, the picture describes the entire system database design for the Warehouse Management System project.

3.3. Design Phase

After the prototype, ERD, use-case and use-case diagram is more or less confirmed; the team begin to move on to the next stage of the SDLC which is the design phase.

In this phase, the team basically will compare and analysis which technology, framework, programming language and database which will be using for this project. The team need to also understand that building this warehouse management system is specially cater for the SME and so building the application must not have high overhead like the cost factor in it.

3.3.1. Application Server

An application server is a software framework that install in a server or computer. It provides an environment that a web application to host in order to allow user

accessing it.

Application Server is important for client-server architecture as it is going to host the Warehouse Management System application to allow the client to access it via Intranet. The team will need to do some research base on the performance of individual specification and feature to determine the choice of application server using for the project. As commercial Application Server such as BEA Weblogic, IBM Websphere and JBoss Enterprise is rather very expensive and furthermore, the system

(22)

does not need use the application server complex feature as of now, the team decided that using the free application server such as GlassFish, Apache Tomcat or Jetty will be good enough to host the system. Although the team decide to use Jetty as the choice of hosting the application, but during the development the team uses BEA Weblogic development version application server to development the system due to the easy deployment of the application to the server.

3.3.2. Database

Database is the storage of the information and data for the system. There many commercial and free database such as PostgreSQL, MySQL, MSSQL, DB2, Oracle and etc available in the market. The team decided to use free database for the project which come down to PostgreSQL and MySQL. Base on the comparison of

PostgreSQL vs. MySQL in Appendix C, The team decided to use MySQL database for this project as it is somehow much better than PostgreSQL. But the main reason is that MySQL has provided a Graphical User Interface (GUI) for accessing the table, data, constraint and etc to make us much easier to work with.

3.3.3. Programming Language and IDE

A programming language is computer language that is designed to interact it with a hardware device like for example a computer. There are many programming language available for developing the warehouse management system like for example Java, C#, VB, C++ and etc. Since the team is more expertise in using Java. The

development of the system will be using java and the choice of Integrated

Development Environment (IDE) will be eclipse as team is much more familiar with it.

3.3.4. Web Application Framework

A web application framework is a software framework that is design or develops to assist the development of dynamic web application or services. For development of

(23)

Hibernate framework to work on the backend side (server side) as these frameworks are much familiar than other framework like iBatis, Spring and etc.

3.3.5. Graphical User Interface (GUI)

Graphical User Interface is a user interface that allows the user to interact with a device like for example selecting music in windows media player. Initially, the team decided to use Extjs which is an extensive JavaScript to build the front end portion of the Warehouse Management System but later found out that Extjs4 which is the latest version of Extjs need licensing to it. This lead the team decided to fall back to use Java Server Page (JSP) together with Struts2-JQuery and Struts2 tag library.

3.4. Coding & Testing Phase

At this stage, the development of the Warehouse Management System

application will start here which means all the Function Requirement that has stated in the Analysis stage will be developed. Writing of source code,

debugging, testing and integrating into the system for each module will be base on the prototype and use-case to carry out.

3.4.1. Setting up of the System for Development

In order to start developing the warehouse management system application, the IDE in this case Eclipse will need to do some of the configuration and download of some library like for Struts2 package, Hibernate3 package, Struts2JQuery.jar for the system development use. Struts2 and Hibernate3 package are for the use of the Warehouse Management System server side coding while else Struts2JQuery.jar is use for the Graphical User Interface advance widget. The following package and jar file can be downloaded at the following website.

Struts2 - http://struts.apache.org/2.1.2/

Hibernate 3 - http://www.hibernate.org/downloads Struts2JQuery - http://code.google.com/p/struts2-jquery/

(24)

As for the configuration of the application server, Eclipse Indigo has already contained the weblogic plug-in in it and all the team need to do is to configure as what the referencing [9] has given.

Project Layout Figure 3.3

Figure 3.3 is the snapshot of the Warehouse Management System project setup layout and the application that is deploy to the development application server.

3.4.2. Generating of the Database table and relationship

Hibernate is an object-relational mapping (ORM) library that is used for Java development. It provides a software framework for mapping an object-oriented domain model to a traditional relational database and most importantly solves object-relational impedance mismatch problems by replacing direct persistence-related database accesses with high-level object handling functions. [10]

As the System backend is using hibernate framework, it will auto generate the tables and its relationship in the Warehouse Management System database if it is properly

(25)

configure in the hibernate.cfg.xml and its related java classes. It gives the developer the hassle-free of creating table and its relationship in MySQL.

Appendix D show a snapshot hibernate configuration file of how it establish a connection between the database server schema and the web application that is using hibernate framework.

3.4.3. Development of the User Interface

Basically, for the Graphical User Interface (GUI) of the Warehouse Management System, the team uses the prototype HTML to convert into Java Server Page (JSP). Widgets in the prototype are also change from html widget into struts2 tag library for as the Warehouse Management System is using Struts2 framework. For example, HTML text field <input type="text" name=" companyName " /> has change to Struts2 tag of this <s:textfield label="Company Name" name="companyName"

cols="40"/>

In Addition to that, Struts2JQuery are also use of the advance Widget like Grid and Tree and etc. As for the Cascading Style Sheet (CSS) for this project, basically it uses 3 types of CSS in this project which are the style.css, login.css and lastly Strut2Query library css. Login.css and Style.css are using a third-party css to modified for own system usage where else Struts2JQuery CSS come with the package. Appendix E consists of both style.css and login.css code.

3.4.4. Backend Coding

When developing or coding the backend for the Warehouse Management System, what the team need to take note when writing the code for the system as it is very important for the future enhancement and system scalability. In order to do that, it has to follow closely on the Modular Design Principle as learn in ICT 203 & 205.

Modular Design Principle is a software design technique that increases the extent to how the code is composed of separate and the interchangeable components which is

(26)

call module. It is the breaking down large program functions into smaller modules, each of which accomplishes one function and contains everything necessary to accomplish it. Modules are the representation of a separation of concerns, and improve maintainability by enforcing logical boundaries between components. [11]

Basically, the writing of the code for the system will be focusing on 3 main key areas which will be

Cohesion

 Meaningful unit (individual component) Coupling

 Relationship (among component) Encapsulation

 Interaction (among component)

This way, the codes will be neater and easier to debug for any error. Any changes in the logic are easier and faster to implement.

3.4.5. System Testing

After each module or functional requirement is completed, there is a need to do a overall check whether the module is working (reliable) or not and this has been a standard practice in the Information Technology (IT) sector when developing software, normally project manager (PM) will create a test case for each module for the tester/user to test it. Test case is also used in User Acceptance Test (UAT) to ensure user of verifying and knowledge it.

Test Case design is normally base on the use-case and use-case diagram to generate it out and for this Warehouse Management System project test case, it is no exception. The system Test Cases are generated base on the Use-Case and Use-Case Diagram found in Appendix A and B. A sample of a Test Case format for this project can be found in Appendix F.

(27)

3.5. Maintenance Phase

At this phase which is the week 40 and 41 in the project schedule, instead of maintaining the Warehouse Management System and doing change request (CR). For this Warehouse Management System project, it will mainly focus on the so call “touch up” and enhancement of the system.

The enhancement of this Warehouse Management System will basically focus on the Supplier portion while the “touch up” will focus on the review and recommendation of the existing function and at the same time check for the last time for any error and debug it.

The enhancement additional functions that will included are

 Tracks Supplier’s Order and Transaction Records

(28)

4. Criteria Review and Reflection

This project basically involved in the whole Software Development Life Cycle (SDLC) of a system and I would love to use this opportunity to gain more experience and knowledge in system designing and project planning. At the same time, I would also like to

implement new software framework for my technology exploration in my workplace. For this project, I have used the knowledge and skill set I have learnt in UniSIM courses for the past years to implement in the project like for example ICT 203 and 205 for the system modelling and 321 and 322 for the database design. However, as my knowledge in the requirement gathering, requirement analysis and project planning is limited cause by the inexperience, there are many changes, flaw amendment and requirement needed to add during the system development due to incomplete functional requirement and this has wasted lot of time in it.

In the early stage of the project, I spent some of the time on reading online forum and material regarding the technology software framework (Struts2 and Hibernate3) that I am planning to use for the system and at the same time doing the warehouse management system literature research. My project supervisor Mr Koh Kim Boon also gave me some guideline and advice regarding on what a warehouse management system should have and how a warehouse operation flow is like during our first few meetings to build a list of functional requirement for this project. He also gave me some ideas and tips on what to focus on at the early stage of doing this project. After I have gathered enough

information, I started to list down the functional requirement which will then discuss with my supervisor. During the discussion, we will the list down the functional requirement for the project base on the time frame and project commitment. The functional requirement that is doing will be the project objective for the FYP.

During the start of the development, I begin to face problem in setting up and configuring the whole environment for the warehouse management system and this took me some time to resolve it. And along the way, there have been lots of changes and problem surface in each module designing which I have resolve it by redesigning the database tables, amend or redesign some of the logic and flow of the use-case, add in additional functional requirement that have been miss out during the planning stage and coding

(29)

problem like for example how “json” works. This took me ample of time resolving it and in order to follow the tight schedule on time, sometime I will need to take leave from work to work on the project.

After completed the entire module for the system, I will do a final test on each module to check for any error or bugs and as expected, it worked according to each functional requirement required and this means that project development and implementation of a Warehouse Management System had successfully meet the objective.

This project has really given me a sense of achievement which I am very proud of cause I can finally present this piece of work which I am working at for the past one year to my audience. I would like to special thanks to my project supervisor Mr Koh Kim Boon for his patient, guidance and understanding that has guided me through the entire year building this warehouse management system start from the scratch. During the planning and development phase of this project, I have encounter lots of problem regarding to the system design and flow which my project supervisor is always there to assist me

resolving these problems. Without him, I will not be able to accomplish this project on time and present it on the 3rd December presentation. Finally, this Final Year Project (FYP) has really helped me to build up a strong foundation and also gain experience in IT project management which I can apply it to my work place in the near future.

(30)

5. Recommendation

Although all the functional requirement of the warehouse management system project has been completed on time, there are still room for many enhancements for this project which I am unable to do it due to the time constraint and project commitment. Below are lists of recommendation which can do to make the system much better.

5.1. Security Enhancement

Recommendation 1:

The system can be improve by creating accessing rights and privilege for each user role so that a user that have a sale role privilege for example will not able to use those purchaser function. This access rights and privilege can be done by controlling the display of the function on the tree panel of the warehouse management system upon logging in.

Recommendation 2:

Creating an audit log for the warehouse management system would be recommended as it can keep track of the user movement in the system as part of the user integrity and system check. This log can be use for many purposes like for example can keep track that created the order, changes the customer’s item selling price or supplier’s item buying price, packed and generate the invoice for the order and etc.

5.2. Function Enhancement

Recommendation 3:

Create an advance or better search for that retrieving the actual item for keying the item pricing or ordering of item as for the system at this stage can only determine the item by keying in the SKU number to add order item or add item pricing. For example, next developer can create three (Category, Brand and items) combo box and using Ajax to retrieve the item more precise and much easier and convenient to the user rather than remembering the entire SKU number.

(31)

Recommendation 4:

Expand the system scalability from Intranet to both Internet and Intranet. As for now, the Warehouse Management System is fully catered for the staff working within the organization. Customers who wanted to create an account or order may have to go through the call centre to do it. Create additional 2 modules or enhance the existing Customer and Order module to allow access to the Internet. Expanding the 2 modules for internet usage may need the developer to rethink and reconsider some of the factor like the Access Control List (ACL), reanalysis how the module should go about and those security points include in 4.1.

Recommendation 5:

As for now, the Warehouse Management System inserting of the item to the rack is automated. This means there is no control over for the store supervisor to physically select which the rack he wants to put next when the items quantity in the rack is excess the rack capacity. The developer may reconsider to enhance the logic by creating a dialog box in the system to physically allow store supervisor to select empty rack he wants for the items to put in when the existing rack is fully stack.

Recommendation 6:

Store supervisor for now may have difficulty tracking new orders coming in and may heavy rely on the sale or admin department to inform them that order has been created in order to start packing the items. Although the store

supervisor go in to the inventory module to search for any new orders, but it is quite silly of it for him to click on the search orders and search for any new orders every minute. Instead of depending on the sale and admin personnel and every minute searching for new orders, developer may reconsider integrating an alert to the warehouse management system which means the upon a order is created or updated, an dialog box will pop out to sound out and show the order number. This allows the store personnel to react to it much better.

(32)

5.3. Other Enhancement

Recommendation 7:

Backing up of database data is always a top priority in commercial industrial because anytime a database server is crashed or corrupted; there is always a database backup file to restore the information back. In this project, the team emphasis is only at the business workflow and not the security portion which means nothing is done on the backing up of database. It will be a good practice to backup the database schema in terms of daily or weekly. This can be

achieving by creating a standalone application or with database agent to backup the database schema.

(33)

6. Conclusion

Overall, the Warehouse Management System in term of the design and implementation for it was a huge success. Furthermore, the system that is developed is design to be very scalability which means that the system can pass to another person to enhance it according the organization or companies’ wants or needs without any huge revamp or recode. The new Warehouse Management System is also design to provide a user-friendly interface which allow user working with this system hassle free. After all, the Warehouse Management System has meet up with all the basic objectives to help SME warehousing companies to improve the existing business workflow by creating a system which is more efficient and effective way to handle the work compare to the traditional way. Basically, the system help different user role in the companies to work much more independent and also lower the chance of human error. With this system running, the time needed from creating an order till the generating of invoice already to send the order is cut by 30% or more compare to the traditional method. In additional to that, creating the customers and suppliers price list and information using the system will be more consistencies and also eliminate human error compare to traditional way like for example sale A personnel is selling item A at $1.00 while else sale B personnel is selling item A at $1.10 due to miscommunication which will anger the customers. Store supervisor are now able to process the order fast as the system able to pinpoint or locate the item location within seconds and even better is that the system also help the companies to perform it business operation better by allowing user to decide to the items to be operate in FIFO or LIFO. Useful information like generating of the items sale report for companies analysis can be done easily in the system.

Lastly, Login is also catering for this Warehouse Management System to provide a minimum amount of security to prevent any unauthorised personnel from misusing it.

(34)

Appendix A – Use Case

Customer Account Management

Use Case Create Account and add item special selling price

Initiator Sale Team

Objective Create new customer account and add each item special selling price if necessary to the particular customer

Pre-conditions Create a new customer account

Post-conditions Sale team will add each of the item selling price of the customer which will reflect the cost in the invoice

Assumptions, notes or constraints

Items without keying the special selling price will be using the default pricing

Use Case Update Account and add or update special selling price

Initiator Sale Team

Objective Search for the existing customer account to add, delete or update each item special selling price for the particular customer

Pre-conditions Search for the particular customer account using the customer company name or company id

Post-conditions Sale team will add, delete or update the item special selling price of the particular customer which will reflect the cost in the invoice and also the order

Items without keying the special selling price will be using the default pricing

Use Case Delete Account

Initiator Sale Team

Objective Search for the existing customer account and delete the customer account

Pre-conditions Search for the particular customer account using the customer company name or company id

Post-conditions Sale team will delete the customer account

N.A.

Use Case View Item information & default selling price

Initiator Sale Team

Objective Search for the item information and default selling price

Pre-conditions Item must be existed in the database

Post-conditions Sale team will delete the customer account

Searching for specific item information can be done through item sku or item name.

(35)

Supplier Account Management

Use Case Create Account and add each item purchasing price

Initiator Purchase Team

Objective Create new supplier account and add each item buying price

Pre-conditions Create a new supplier account

Post-conditions Purchase team will add each of the item buying price

Items without keying the price will be default to $0.00

Use Case Update Account and add, delete or update item purchasing price

Objective Search for the existing supplier account to add, delete or update the item purchasing price

Pre-conditions Search for the particular customer account using the supplier company name or company id

Post-conditions Purchase team will add or update the item buying price

N.A.

Use Case Delete Account

Objective Search for the existing supplier account and delete the supplier account

Pre-conditions Search for the particular supplier account using the supplier company name

Post-conditions Purchase team will delete the supplier account

N.A.

Use Case Create Product

Objective Add the new product information, default pricing and distribution method

Pre-conditions Have to click on the create product link under the Supplier Account tab

Post-conditions New item will be created

(36)

Invoice Management

Use Case Generate Invoice

Initiator Admin Team and Sale Team

Objective Generate an Invoice when customer order is ready to deliver

Pre-conditions Search for an order

Post-conditions An order invoice will be created

An order in the order management must be created

Use Case Update Invoice

Objective Search for an existing Invoice to add or edit the invoice

Pre-conditions Search for a particular invoice using the invoice number or company name

Post-conditions Admin or Sale Team will update the information of the particular invoice only

Admin or Sale Team able to print the updated invoice

N.A.

Use Case Delete Invoice

Initiator Admin/Sale Team

Objective Search for an existing Invoice to delete the invoice

Pre-conditions Search for the particular invoice using the invoice number or company name

Post-conditions Admin or Sale Team will delete the invoice

N.A

Use Case View Invoice

Initiator Admin/Sale Team

Objective Search for an existing Invoice to view and print it

Pre-conditions Search for a particular invoice using the invoice number or company name

Post-conditions Print the invoice

(37)

Order Management

Use Case Generate Order

Objective Generate an Order when customer has order items

Pre-conditions An order will be created once sale manage to get sale order from customer.

Post-conditions Order will be created.

Item pricing does not have special selling price will use default selling price

Use Case Update Order

Objective Search for an existing order to add, delete or update the order item

Pre-conditions Search for a particular order using the order number or company name

Post-conditions Particular order information will be updated

Item pricing does not have special selling price will use default selling price

Use Case Delete Order

Objective Search for an existing order to delete the order

Pre-conditions Search for the particular order using the order number or company name

Post-conditions Particular order will be delete

(38)

Inventory Management

Use Case Pickup Order

Initiator Store Supervisor

Objective Search for any new order to find and pack the items

Pre-conditions Search for a particular order using the order number or company name

Post-conditions Find the item indicate in the order list and pack the order

N.A.

Use Case Search Empty Rack

Objective Search for any new empty to rack available in the warehouse

Pre-conditions Go to the Inventory management tab to click on the Search Empty Rack

Post-conditions A list of empty rack slot that is available in the warehouse will be shown

N.A.

Use Case Search Item Location

Objective Search for the location of the items in the warehouse

Pre-conditions Search for a particular item using the SKU or item name

Post-conditions Retrieve a list of the item location, information, distribution method and rack type

Item must be assign to a location at the beginning

Use Case Add new Item Quantity and Location

Initiator Store Supervisor/Purchase Team

Objective Add a new item and its quantity to the system and then assign a location in the warehouse to it

Pre-conditions Add new item and update the item quantity

Post-conditions The new item quantity will be updated and the location of the item will be created

Upon the rack is used, empty rack available will change to false from true

(39)

Use Case Update item Quantity

Objective Search for an existing item to update its quantity or distribution method

Post-conditions Distribution method and item quantity will be updated.

N.A.

Use Case Delete item

Objective Search for an existing item to delete it

Post-conditions The assign item location will be remove

N.A.

Use Case Add item Distributing Rule

Objective Search for an item to view its information and then add a business logic (FIFO/LIFO) to the distributing method

Pre-conditions Search for a particular item using the SKU

Post-conditions Assign or add a business rule to the particular items

Use Case Update item Distributing Rule

Objective Search for an item to view its information and then update a business logic (FIFO/LIFO) to the distributing method

Pre-conditions Search for a particular item using the SKU

Post-conditions Update a business rule to the particular items

(40)

Report Management

Use Case Generate Purchase Report

Objective Generate the report to view base on a timeframe

Pre-conditions Key in the timeframe to generate a report

Post-conditions Generate a report for viewing or printing

To Date cannot be earlier than From Date

Use Case Generate Sale Report

Initiator Sale Team

Objective Generate the report to view base on a timeframe

Pre-conditions Key in the timeframe to generate a report

Post-conditions Generate a report for viewing or printing

(41)

(42)

(43)

(44)

Appendix C – PostgreSQL vs. MySQL

PostgreSQL MySQL

SQL standard COMPLIANCE

Postgres understands a good subset of SQL92/99 plus some object-oriented features to these subsets. Postgres is capable of handling complex routines and rules as declarative SQL queries, subqueries, views, multi-user support, transactions, query optimization, inheritance, and arrays. Does not support selecting data across different databases.

MySQL uses SQL92 as its foundation. Runs on countless platforms. Mysql can

construct queries that can join tables from different

databases. Supports both left and right outer joins using both ANSI and ODBC syntax. As of MySQL 4.1 from that release on, MySQL will handle subqueries. Views supported as of release 5. PLATFORMS Lacks binary distribution for all

the supported plataforms. One of the problems is that PostgreSQL doesn't run properly on NT as a service by default, you need something like firedaemon to start it. The PgAccess GUI is available on windows as well, but it lacks a few features that psql supports. Non-supported platforms:

Windows9x/Me, NextStep, Ultrix.

There are binary distribution for most of the supported plataforms. MySQL works better on Windows than PostgreSQL does. MySQL runs as a native Windows application (a service on NT/Win2000/WinXP), while PostgreSQL is run under the cygwin emulation.

SPEED Postgres slower on low-end but

has some options for improving. Postgres forks on every incoming connection - and the forking process and backend setup is a bit slow, but one can speed up

PostgreSQL by coding things as stored procedures.

MySQL is very fast on both simple and complex

SELECTs, but might require changing the database type from MyISAM to InnoDB for UPDATE intense

applications. MySQL handles connections very fast, thus making it suitable to use MySQL for Web - if you have hundreds of CGIs

connecting/disconnecting all the time you'd like to avoid long startup procedures. STABILITY PostgreSQL 6.x series and earlier

were much worse in this aspect. Random disconnects, core dumps and memory leaks are usual. PostgreSQL 7.x series was a big improvement. Expect PostgreSQL 8.x to continue this trend.

MySQL does very good job even on the busiest sites; it certainly has some problems handling hundreds of

connections per second, but these problems are resolvable. Random disconnects and core dumps are exceptionally rare.

(45)

user base than PostgreSQL, therefore the code is more tested and has historically been more stable than

PostgreSQL and more used in production environments. DATA INTEGRITY Postgres does very good job

supporting referential integrity, has transactions and rollbacks, foreign keys ON DELETE CASCADE and ON UPDATE CASCADE.

Mysql has some basic provisions for referential integrity and

transactions/rollbacks.

CHECK clause is allowed for compatibility only and has no effect on database operation. InnoDB tables have

FOREIGN KEYs for relational or multi-table delete, and support transaction processing. In MySAM tables FOREIGN KEY is for

compatibility only and has no effect on database operation. SPECIAL server-side

FEATURES

Postgres has rules, triggers, server-side functions that can be written in C, pgsql, python, perl and tcl languages.

INSTEAD OF rules can be used for updating data through views. PostgreSQL has schemas that allow users to create objects in separate namespaces, so two people or applications can have tables with the same name. There is also a public schema for shared tables. Table/index creation can be restricted by removing

permissions on the public schema.

MySQL has simple (and probably inconvenient) mechanism for server-side shared libraries with C functions. Rudimentary support for triggers was included beginning with MySQL 5.0.2. An external development implemented in perl can be used as stored procedures in Mysql. MySql has more powerful admin tools included in the distribution (mysqladmin allows you to watch processes and queries in-progress), including hot backup, a file corruption recovery tool and a of couple others. Command-line tools - you can see database and table structures using describe and show commands. Postgres'

commands are less obvious ( \d to show a list of tables for instance).

SECURITY Postgres has similar features, but a little less fine-grained. For example, if user can connect to a

MySQL has exceptionally good fine-grained access control. You can GRANT and

(46)

database, user can CREATE TABLE, thus running Denial-of-Service. On the other hand

Postgres can limit logins based on different criteria - network

segment, ident string, etc.

REVOKE whatever rights you want, based on user name, table name and client host name.

LOCKING and CONCURRENCY SUPPORT

PostgreSQL has a mechanism called MVCC (MultiVersion Concurrency Control),

comparable or superior to best commercial databases. It can do row-level locking, can lock rows for writing in one session but give these rows unaffected in another session. MVCC is considered better than row-level locking because a reader is never blocked by writer. Instead, Postgres keeps track of all transactions and is able to manage the records without waiting to become available.

MySQL can do table locking for ISAM/MyISAM and HEAP tables, page level locking for BDB tables. InnoDB has full row level locking support.

LARGE OBJECTS n Postgres, Large Objects are very special beasties. You need to create them using lo_create function and store the result of the function - OID - in a regular table. Later you can manipulate the LOB using the OID and other functions - lo_read/lo_write, etc. Large object support is broken in Postgres - pg_dump cannot dump LOBs; you need to develop your own backup mechanism. Tthe team is working on implementing large rows; this will replace current LOB support.

In MySQL, text and binary LOBs are just fields in the table. Nothing special - just INSERT, UPDATE, SELECT and DELETE it the way you like. There are some

limitations on indexing and applying functions to these fields.

ALTER TABLE Postgres supports ALTER

TABLE to some extent. You can ADD COLUMN, RENAME COLUMN and RENAME TABLE.

MySQL has all options in ALTER TABLE - you can ADD column, DROP it, RENAME or CHANGE its type on the fly - very good feature for busy servers, when you don't want to lock the entire database to dump it, change definition and reload it back.

(47)

Appendix D – Hibernate Configuration File

<?xmlversion='1.0'encoding='utf-8'?>

<!DOCTYPE hibernate-configuration PUBLIC "-//Hibernate/Hibernate Configuration DTD//EN"

"http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd"> <hibernate-configuration>

<session-factory>

<propertyname="connection.useUnicode">true</property>

<propertyname="connection.characterEncoding">UTF-8</property>

<propertyname="connection.driver_class">com.mysql.jdbc.Driver</property> <propertyname="connection.url">jdbc:mysql://localhost:3306/wms</property> <propertyname="connection.username">root</property>

<propertyname="connection.password">password123</property>

<propertyname="dialect">org.hibernate.dialect.MySQLDialect</property> <propertyname="current_session_context_class">thread</property>

<property

name="hibernate.transaction.factory_class">org.hibernate.transaction.JDBCTransactionFact ory</property>

<propertyname="hibernate.show_sql">true</property>

<propertyname="hibernate.hbm2ddl.auto">update</property>

<mappingresource="vo/Users.hbm.xml"/> <mappingresource="vo/Country.hbm.xml"/> <mappingresource="vo/CreditTerms.hbm.xml"/> <mappingresource="vo/Customers.hbm.xml"/> <mappingresource="vo/Suppliers.hbm.xml"/> <mappingresource="vo/Category.hbm.xml"/> <mappingresource="vo/Brand.hbm.xml"/> <mappingresource="vo/Item.hbm.xml"/>

<mappingresource="vo/CustomerItemRelation.hbm.xml"/> <mappingresource="vo/SupplierItemRelation.hbm.xml"/> <mappingresource="vo/Orders.hbm.xml"/>

<mappingresource="vo/OrderItem.hbm.xml"/> <mappingresource="vo/ItemDetail.hbm.xml"/> <mappingresource="vo/Rack.hbm.xml"/> <mappingresource="vo/Location.hbm.xml"/> <mappingresource="vo/ItemLocation.hbm.xml"/> </session-factory>

(48)

Creating table column and keys (Foreign and Primary)

<?xml version="1.0"?>

<!DOCTYPE hibernate-mapping PUBLIC

"-//Hibernate/Hibernate Mapping DTD 3.0//EN"

"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd"> <hibernate-mapping>

<class name="vo.Customers" table="Customers">

</id> <property name="companyName" type="string" column="companyName" length="45" /> <property name="companyAddress" type="string" column="companyAddress" length="45" /> <many-to-one name="companyLocation" class="vo.Country" column="companyLocationId" not-null="false" cascade="all" /> <many-to-one name="companyCreditTerms" class="vo.CreditTerms" column="creditTermsId" not-null="false" cascade="all" /> </class> </hibernate-mapping>

(49)

Appendix E – Cascading Style Sheet

Style.css

body {

background:#fbfdf6;

font-family:"Myriad Pro", Myriad, Helvetica, Arial, sans-serif;

font-size:90%; color:#787878; margin:20px auto; text-align:center; line-height:1.4em; } h1, h2, h3, h4, h5, h6 {

font-family:Helvetica, Arial, sans-serif;

font-size:1.5em; letter-spacing:-0.06em; text-transform:uppercase; font-weight:normal; } a:link, a:visited {

font-family:georgia, times, serif;

color:#787878; text-decoration:none; font-size:.8em; } a img { border:none; } header { display:block; position:relative; text-align:left; padding:10px 20px 20px; margin:5px; border:2px solid #666; -webkit-border-radius:4px; -moz-border-radius:6px; background:#fff repeat-x 0px -120px; } header h1 { font-size:2.3em; text-transform:none; letter-spacing:0em; text-align:center; } header h2 { margin:-10px 0px 20px; font-size:1.3em; text-align:center

(50)

} section { float:left; width: 200px; text-align:left; padding:10px 20px 20px; margin:5px; border:2px solid #666; -webkit-border-radius:4px; -moz-border-radius:6px; background:#fff repeat-x 0px -120px; } section h1 { color:#fff; padding:9px 10px 10px 10px; margin:13px -10px; } article { float:left; width: 95%; text-align:left; padding:10px 20px 20px; margin:5px; border:2px solid #666; -webkit-border-radius:4px; -moz-border-radius:6px; background:#fff repeat-x 0px -120px; } article h1 {

background:transparent url(sprites.png) no-repeat 0px 0px;

color:#787878;

padding:7px 10px 12px 30px;

margin-left:-30px; }

article h2 {

background:transparent no-repeat 0px -40px;

color:#fff;

padding:7px 10px 12px 30px;

margin-left:-30px; }

#createbutton{background:#999; color:#fff;cursor:pointer;padding:5px 10px; -moz-border-radius:6px;-webkit-border-radius:4px;width:150px;}

#updatebutton{background:#999; color:#fff;cursor:pointer;padding:5px 10px; -moz-border-radius:6px;-webkit-border-radius:4px;width:150px;}

#insertbutton{background:#999; color:#fff;cursor:pointer;padding:5px 10px; -moz-border-radius:6px;-webkit-border-radius:4px;width:150px;}

#addbutton{background:#999; color:#fff;cursor:pointer;padding:5px 10px; -moz-border-radius:6px;-webkit-border-radius:4px;width:150px;}

(51)

login.css

/* Last Modified 24-12-2009 by Menuka Walpitagamage */

body{

width:640px;

margin:0 auto;

text-align:center;

border:0px solid #666; font-family: Verdana, Arial, Helvetica, sans-serif; padding-top:100px;}

#content{padding:5px 0; min-width: 450px; max-width:500px;} #innerholder{margin:10px auto; width:90%;}

#innerholder h3 span{background:url(../images/logo.png) no-repeat; height:60px;

display:block;}

#innerholder .a {padding:4px;}

#innerholder .a, #innerholder .a .sb-inner {padding:30px; height:200px;} #loginbutton{background:#999; color:#fff;cursor:pointer;padding:5px 10px; -moz-border-radius:6px;-webkit-border-radius:4px;width:150px;}

input{border: none;background: #fff;width: 250px;font-family:arial, helvetica;

font-size:18px; font-weight:bold;}

.roundedfield{background: url(../images/rounded.gif) no-repeat left top;

height:50px;padding: 4px;text-align:left;} #label {padding-bottom:5px; text-align:left;}

DESIGN & DEVELOP WAREHOUSE AND INVENTORY SYSTEM

SIM UNIVERSITY

SCHOOL OF SCIENCE AND TECHNOLOGY