inventory interface design with datbase
TRANSCRIPT
Contents1.INTRODUCTION........................................................................................................................................4
2. INTERFACE DESIGN..................................................................................................................................5
Figure 2.1 Login.......................................................................................................................................5
Figure 2.2 Create User.............................................................................................................................6
Figure 2.3 Modify User............................................................................................................................7
Figure 2.4 Home page..............................................................................................................................8
Figure 2.5 Order handling........................................................................................................................9
Figure 2.6 New Orders...........................................................................................................................10
Figure 2.7 Order Approval.....................................................................................................................11
Figure 2.8 Product Management...........................................................................................................12
Figure 2.9 Stocks (BIN Card)..................................................................................................................13
Figure 2.10 Purchase Order Generations...............................................................................................14
Figure 2.11 Purchase Order Approvals..................................................................................................15
Figure 2.12 Add New Purchase..............................................................................................................16
Figure 2.13 Sales return.........................................................................................................................17
Figure 2.14 Purchase return..................................................................................................................18
Figure 2.15 Add, Delete, and modify Product........................................................................................19
Figure 2.15 Customer............................................................................................................................20
Figure 2.16 Add delete Modify Customer..............................................................................................21
Figure 2.17 Customer Profile.................................................................................................................22
Figure 2.18 Customer Payments............................................................................................................23
Figure 2.19 Sales....................................................................................................................................24
Figure 2.20 Sales view...........................................................................................................................25
Figure 2.21 Supplier...............................................................................................................................26
Figure 2.22 Reports...............................................................................................................................27
Figure 2.23 Forecast..............................................................................................................................28
Figure 2.25 Sales Representative...........................................................................................................29
Figure 2.26 EOQ.....................................................................................................................................30
LD Private Limited-Interface design Page 1
3. DATABASE DESIGN.................................................................................................................................31
a) Normalized database Structure.........................................................................................................31
b) Preview of tables.............................................................................................................................35
4.REPORTS DESIGN....................................................................................................................................43
LD Private Limited-Interface design Page 2
1.INTRODUCTION
Leelasena Distributors Pvt Ltd. is one of the main Hardware-New products Distributor of Richard Pieris Distributors Pvt Ltd (Arpico) which is a Business to Business Company and does its distribution in the area of Wayamba Province. There are approximately 45 product items which are handled by this company. Leelasena Distributors Pvt Ltd. started the company in the year of 2004 as a sole proprietor company and registered as a Private Limited Company in the year of 2007. Leelasena Distributors Pvt Ltd has monthly Sales revenue around 7 to 8 Million.
Leelasena Distributors mainly distributes products such as;
Arpitec water pumps
Arpilight CFL bulbs.
PVC Doors
Plastic furniture.
Shovels and hoes.
Plastic garden hoses.
LD Private Limited-Interface design Page 3
Paint brushes.
Sand paper roles and sheets.
Scope of the system
Since Leelasena Distributors Pvt Ltd. does its distribution in the Wayamba province, they have to perform a high level of inventory management for a vast product line but since most of the procedures such as forecasting and demanding decisions are made by Delphi method, there are inefficiencies in the current method. Therefore using a proper method for taking decisions about future purchases was identified as a main problem. Main goal of this project is to identify the current problems which Leelasena Distributors Pvt Ltd. Faces in,
Inventory Management Demand Forecasting Order Management
And introduce an IT Based solution to overcome with the problems identified
2. INTERFACE DESIGN
LD Private Limited-Interface design Page 4
Figure 2.1 LoginUser can log in to the system by typing the user name and password. Depending on the User Name their will be different fields acctivated. Using Administrator user name Password User can log in and create/delete/modify a user and Using Manager user name and operator user name different people can log in with different fields ctivated.
LD Private Limited-Interface design Page 5
Figure 2.2 Create User
From this interface administrator can create a user.depending on the value selected in the Role differents rights will be activated.
LD Private Limited-Interface design Page 6
Figure 2.3 Modify User
From this interface administrator can Modify or delete a user .depending on the value selected in the Role differents rights will be activated.Have to select the user name from the combobox and other values will be then editable.
LD Private Limited-Interface design Page 7
Figure 2.4 Home page
This is the Home Page for the system.Using a menustrip the other links are connected above.Main parts are order handling,Product management,Customer,Supplier,Sales,Forecast,Reorts,EOQ,SalesRep
LD Private Limited-Interface design Page 8
Figure 2.5 Order handling
There are two sections in order handling.Approval of new Order should only be activated in Managers interface.
LD Private Limited-Interface design Page 9
Figure 2.6 New Orders
When a new order is received Operator will input the details of the order in the following interface. He has to select a due date for payments in future agreed day. This is normally 60 days. In one order there can be several products so by choosing the product Id and Quantity and by adding them in to the data grid. From save for approval these not approved orders get saved and only manager can view them and then approve or reject them.
LD Private Limited-Interface design Page 10
Figure 2.7 Order Approval
In this interface Manager get the details in the data grid and when he select a particular item its details appear on the below then manager can Approve the order or reject it further he can click on customer profile to view the particular customer details in the order. When Manager approves the order sales Invoices get printed and will be saved as a sale.
LD Private Limited-Interface design Page 11
Figure 2.8 Product Management
In the Product Management area there are five sub sections such as stock (BIN), Add/Delete product And Sales Return and purchase return. Purchase has another three sub parts such as purchase order, add new purchase and purchase order approval.
LD Private Limited-Interface design Page 12
Figure 2.9 Stocks (BIN Card)
In this interface user can view the current level of stock .user can view all the products to a selected product by choosing from the product ID.From purchase Order button use can straight away add products( which are selected at the time ) to the next interface by automatically filing the products requirement.
LD Private Limited-Interface design Page 13
Figure 2.10 Purchase Order Generations
Operator will use this interface to generate new Purchase order. Products and quantity are selected for the particular purchase Order No. and it will be saved in to be approved from the Manager.
LD Private Limited-Interface design Page 14
Figure 2.11 Purchase Order ApprovalsPurchase Order will be viewed by manager and it will be approved or rejected. If approved Purchase Order will be generated in the same time.
LD Private Limited-Interface design Page 15
Figure 2.12 Add New PurchaseWhen we receive goods from the supplier through the above interface operator will add new products to the stock. And save it. Then the stock (BIN Card) will be updated.
LD Private Limited-Interface design Page 16
Figure 2.13 Sales returnCustomers can return the products. When they return operator will enter the customer ID and specific products that the customer will return. It could be one or many products and would be in different quantities, after adding the products by saving the information stock would get added to the BIN card the general stock.
LD Private Limited-Interface design Page 17
Figure 2.14 Purchase returnBusiness can return the products. When we return operator will enter the Supplier ID and specific products that the business will return. It could be one or many products and would be in different quantities, after adding the products by saving the information stock would get reduced from the BIN card the general stock.
LD Private Limited-Interface design Page 18
Figure 2.15 Add, Delete, and modify ProductThis form will Add, Delete or modify the information of a product in to the main database. All the product related information is in this interface.
LD Private Limited-Interface design Page 19
Figure 2.15 CustomerCustomer will have three sub elements. Payments Profile and Add/Delete/Modify. By selecting each we could go to those forms.
LD Private Limited-Interface design Page 20
Figure 2.16 Add delete Modify CustomerAdd Delete Modify Customer will be Done in this interface. Customer ID, Name and contact no and Address will be insert or modify hear. By saving this can be saved in customer Database.
LD Private Limited-Interface design Page 21
Figure 2.17 Customer ProfileFrom this User can view the customer details ,Total outstanding and Transactions such as ( Sales and Returns) and also his payment hishory.
LD Private Limited-Interface design Page 22
Figure 2.18 Customer Payments
From this interface we can update customer payments. Adding the details will reduce the particular customer outstanding balance and the details of payments will be saved in payments database.
LD Private Limited-Interface design Page 23
Figure 2.19 Sales
LD Private Limited-Interface design Page 24
Figure 2.20 Sales view
From this form user can view the sales details for a particular selected period.
LD Private Limited-Interface design Page 25
Figure 2.21 Supplier
All the supplier details will be added and modified by this interface and will be saved in the database.
LD Private Limited-Interface design Page 26
Figure 2.22 Reports
From this interface any user can retrieve the above mentioned reports from the interface. After selecting the radio button user have to select the duration which the reports are needed. But in the reports of Stock, General Customer Details, Stock under reorder level and Customer payment outstanding reports will be without a time duration selection. So the date time picker will be disable for those.
LD Private Limited-Interface design Page 27
Figure 2.23 Forecast
From this interface user will be able to view the forecasted product requirement of a particular product or for all the products after selecting the time period. Then forecast will be generated and will be saved in the database under a forecast ID.
LD Private Limited-Interface design Page 28
Figure 2.25 Sales Representative
Sales Representative will be Added deleted and modified through this interface and will be saved in the database .
LD Private Limited-Interface design Page 29
Figure 2.26 EOQ
From this interface users can generate the EOQ( economic order quantity) of a particular product. This would be useful to purchase decisions.
LD Private Limited-Interface design Page 30
3. DATABASE DESIGN
a) Normalized database StructurePRODUCTS
PRODUCT_ID PRODUCT_NAME PRODUCT_TYPE DESCRIPTIONUNIT_PRICE AVAILABLE_QUANTITY REORDER_LEVEL MAINTAINENCE_COST INSUARANCE_COST H_OTHER_COST O_OTHER_COSTCALL_CHARGELABOUR_COST PAPER_COST
CUSTOMER
CUSTOMER_ID CFNAME CLNAME CHNO CLANE CCITY CUSTOMER_OUTSTANDING
SALES_REP
SALES_REP_ID RFNAME RLNAME RHNO RLANE RCITY TARGET
REP_TEL
SALES_REP_ID REP_TEL FOREIGN KEY SALES_REP_ID
CUSTOMER_PAYMENTS
PAYMENT_ID
LD Private Limited-Interface design Page 31
PAYMENT_AMOUNT CUSTOMER_ID PAYMENT_DATE FOREIGN KEY CUSTOMER_ID
SUPPLIER
SUPPLIER_ID SFNAMESLNAME SHNOSLANESCITY
CUSTOMER_TEL
CUSTOMER_ID CUSTOMER_TEL FOREIGN KEY CUSTOMER_ID
SUPPLIER_TEL
SUPPLIER_ID SUPPLIER_TEL FOREIGN KEY(SUPPLIER_ID)
CUSTOMER_ORDER
ORDER_ID ORDER_DATE CUSTOMER_ID SALES_REP_ID APPROVE_NOT_APPROVED FOREIGN KEY(CUSTOMER_ID) FOREIGN KEY(SALES_REP_ID)
SALES
SALES_ID SALES_DATE DUE_DATE CUSTOMER_ID SALES_REP_ID FOREIGN KEY(CUSTOMER_ID)FOREIGN KEY(SALES_REP_ID)
LD Private Limited-Interface design Page 32
SALES_RETURN
SALES_RETURN_ID SALES_RETURN_DATE CUSTOMER_ID FOREIGN KEY(CUSTOMER_ID)
SALES_RETURN_DETAILS
SALES_RETURN_ID PRODUCT_ID S_RETURN_QUANTITY FOREIGN KEY PRODUCT_IDFOREIGN KEY(SALES_RETURN_ID)
PURCHASE_ORDER
PURCHASE_ORDER_ID PURCHASE_APPROVE_NOT_APPROVED PURCHASE_ORDER_DATE
PURCHASE_ORDER_DETAILS
PURCHASE_ORDER_ID PRODUCT_ID PURCHASE_ORDER_QUANTITY FOREIGN KEY(PRODUCT_IDFOREIGN KEY(PURCHASE_ORDER_ID
PURCHASE_IN
PURCHASE_IN_ID PURCHASE_IN_DATE SUPPLIER_ID FOREIGN KEY(SUPPLIER_ID)
PURCHASE_IN_DETAILS
PURCHASE_IN_ID ) PRODUCT_ID ) PURCHASE_IN_QUANTITY FOREIGN KEY(PRODUCT_ID)FOREIGN KEY(PURCHASE_IN_ID)
LD Private Limited-Interface design Page 33
SALES_DETAILS
SALES_ID PRODUCT_ID SALES_QUANTITY OREIGN KEY(PRODUCT_ID),FOREIGN KEY(SALES_ID)
CUSTOMER_ORDER_DETAILS
ORDER_ID PRODUCT_ID CUSTOMER_ORDER_QUANTITY FOREIGN KEY(PRODUCT_ID) FOREIGN KEY(ORDER_ID)
PURCHASE_RETURN
PURCHASE_RETURN_ID PURCHASE_RETURN_DATE SUPPLIER_ID FOREIGN KEY(SUPPLIER_ID)
PURCHASE_RETURN_DETAILS
PURCHASE_RETURN_ID PRODUCT_ID PURCHASE_RETURN_QUNTITY FOREIGN KEY(PRODUCT_ID)FOREIGN KEY(PURCHASE_RETURN_ID))
LD Private Limited-Interface design Page 34
b) Preview of tables
Figure3.1 CUSTOMER
Figure 3.2 CUSTOMER_ORDER
LD Private Limited-Interface design Page 35
Figure 3.3 CUSTOMER_ORDER_DETAILS
Figure 3.4 CUSTOMER_PAYMENTS
Figure 3.5 CUSTOMER_TEL
LD Private Limited-Interface design Page 36
Figure 3.6 PRODUCTS
Figure 3.7 PURCHASE_IN
LD Private Limited-Interface design Page 37
Figure 3.8 PURCHASE_IN_DETAILS
Figure 3.9 PURCHASE_ORDER
Figure 3.10 PURCHASE_ORDER_DETAILS
LD Private Limited-Interface design Page 38
Figure 3.11 PURCHASE_RETURN
Figure 3.12 PURCHASE_RETURN_DETAILS
Figure 3.13 REP_TEL
LD Private Limited-Interface design Page 39
Figure 3.14 SALES
Figure 3.15 SALES_DETAILS
Figure 3.16 SALES_DETAILS
LD Private Limited-Interface design Page 40
Figure 3.17 SALES_DETAILS
Figure 3.18 SALES_RETURN_DETAILS
Figure 3.19 SUPPLIER
LD Private Limited-Interface design Page 41
Figure 3.20 SUPPLIER_TEL
LD Private Limited-Interface design Page 42
4.REPORTS DESIGN
LD Private Limited-Interface design Page 43