Self Correcting Inventory System - UC DRC Home
Contents
1. Ch S Go N e 4 j K 28 Task E User Login Build Security Tables Design login Screen Coding Testing El Design Wavelink Screens Design Scan Zone Screen Design Scan Location Screen E Develop ERP functionality Validation of Zone Validation of location ERP Login Create ERP activity form Create Pallet move logic Put all the pieces together Implement Error handling El Populate Test Data Setup Site Create Products for testing Create necessary profiles Create production model Create Zones Create locations Produce product for testing Deploy databse in testing env Testing El Reports Create Inventory List Report Create Inventory Transaction Report Create Inventory Scorecard Report El Testing Unit testing Stress testing User acceptance testing Figure 21 Timeline Duration 5 days 1 day 1 day 3 days 1 day 3 days 2 days 1 day 18 days 0 5 days 0 5 days 0 5 days 5 days B days 2 days 2 days 14 days 2 days 2 days D days 2 days 1 day 2 days 1 day 1 day 3 days 6 days 2 days 2 days 2 days 5 days 1 day 1 day 3 days Start Thu 4 6 06 Thu 4 6 06 Fri 4 7 06 Mon 4 10 06 Thu 4 6 06 Wed 3 15 06 Wed 3 15 06 Fri 347 06 Mon 3 13 06 Mon 3 13 06 Tue 3A 4 06 Wed 3 15 06 Thu 3 16 06 Thu 3 23 06 Fri 3 31 06 Tue 4 4 06 Mon 4 10 06 Mon 4 10 06 Wed 4 2 06 Thu 4 3 06 Fri 4 4 06 Tue 48 06 Wed 4 19 06 Fri2. 49 The following screen shots are created for better understanding of the purpose of this application This will also help the user to follow the application flow There are two scenarios in this application and I will be discussing them via the following pictures Scenario I This scenario will describe the application flow when there is a discrepancy between the system inventory and the physical inventory and there are more pallets in the system and less in the physical location As the user counts the location the system inventory and physical inventory will be matched Physical Inventor System Inventory Pallet A Pallet B Pallet A Pallet B Pallet C Pallet C Pallet D Location XYZ Location XYZ No Pallets Handheld Location HH1 50 In the above case the system shows that there are four pallets in XYZ location Physically there are only three pallets As the user scans location XYZ all the pallets will be moved from XYZ location to the handheld HH1 location This move 15 described in the following screen shot Physical Inventory System Inventory Pallet A PalletB B No Pallets Pallet C Location XYZ Location XYZ Pallet A Pallet B Pallet C Pallet D Handheld Location HH1 51 As the user starts scanning the physical pallets A B C etc the pallets will be moved back to the original location ABC Physical Inventory System Inventory Pallet A Pallet B Pallet A Pallet C Location XYZ Loca
3. Sandip Singh W Sandip Singh Sandip Singh Sandip Singh ea Sandip Singh Sandip Singh Singh Sandip Singh Figure 22 Timeline 35 Cost Most of the hardware and software required for this project has already been purchased and installed by The Wornick Company Following is the list of equipment that the new application will utilize Task Name Presenttle prototype Create Pale tm ove 00 Create ERP actu ity fom Create prodictl mode Prodtce prodict for testhg Testhg Deshi Scar Zore Serei Settp Sie Codlig Desi Scat Locatbi Serei Pitallthe together Inpkmeit Error banding Create Products tor testhg Bald Sect rity Tab ks Deski bgli Screei Testlig Freeze the design aid code Create vecessaly Create Zoves Create beathis Dephy databse li testhg eru Valldatbi 012018 Valldatht of heathi ERP Logh Figure 23 Cost Fixed Cost 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 Fixed Cost Accrual P orate d P orate d P orate d P orate d P orate d Prorate d Prorate Prorate d Prorated Prorate P orate d P orate d P orate d P orate d P orate d P orate d P orate d P orate d P orate d P orate d Prorate d P orate d P orate d Total Cost 35 000 00 900 00 150 00 450 00 450 00 450 00 315 00 315 00 300
4. 3 Once a valid location has been scanned the system is ready to begin scanning individual inventory items for the current location Notice that the Scan Pallet screen displays the current location being scanned on the screen in Figure 10 Scan The Pallet aaa Loc A161A KF4 gt Loc Counted Figure 10 Scan Pallet Screen At this time all items found at the current location in the system are moved from the current location to the location of the scanning truck Inventory items remain there temporarily until they are physically scanned and counted If any items are not physically scanned back into the current location by the end of the location count they are moved to CCLost 4 As you scan the physical pallets from that location you will be asked to verify the quantity If the pallets is sealed full quantity you can just press the scanner and the system quantity will remain In the example shown in figure 11 the quantity would be 1620 EA and the location would be moved back to 101 20 Verify Quantity 1626 EA y If Correct N If Incorrect Figure 11 Verify Quantity Screen If the system quantity displayed is not correct press N on the scanner keypad The screen shown in figure 12 will be displayed At this point you can enter the correct quantity for the pallet Cycle Count Enter New Quantity EE Figure 12 Enter New Quantity Screen 5 Repeat steps 1 through 5 for each locatio
5. 39 Appendix This appendix describes all the Flex Process classes that are used to manipulate the system User Defined Activities and Profiles OLE Classes You enter user defined activities to record changes in inventory placement or status This may include any of the following e inventory movement such as a change in site area and location e inventory transition into from a cost center such as between a location and cost center or between cost centers e change of classification User defined activity requires one or more user defined activity profiles that determine rules for activity reporting and aspects such as field defaults layout inclusion and restrictions CUserDefinedActivityReference in Defined VOU GC USC E apes meg PI oca tent Activity Summarys SS SS T3 oe ce iG T e a T 1 CUserDefinedActivityProfileReference oe IPROTEAN IPROTEAN User User Defined Defined Activity Line IPROTEAN UserDefined Lineltern Characteristic IPROTEAN DelayedEvent Flex Process User Defined Activities and Profile Reference 40 1 Reference CERE The classes available for User Defined Activities and Profiles are rDefin ivi mmar IPROTEANUserDefinedActivitySummary IPROTEANUserDefActySummaryLineltems IPROTEAN rDefA in r risti IPROTEANUserDefinedLine IPROTEANUserDefinedLineltemC
6. Operator LONG UM CUMReference Updates long ValueTable CEditTableReference ValueType LONG 7 IPROTEANUserDefActySummaryDelayedEvents Interface Class Note This class is reserved for Internal Use Only by FlexProcess Development Create Using Property that creates this class UserDefActySummaryDelayedEvents Methods Method Name Comment Item Move Remove Properties Property Name Data Type Comment Count Integer 47 8 CUserDefinedActyProfileReference Interface Class Uniquely identifies a user defined activity profile User defined activities are used on the IPROTEANUserDefinedActivitySummary object Create Using PROTEAN UserDefinedActyProfileReference Methods Method Name Comment Copy IsNull Locate SetNull Properties Property Name Data Type Comment DisplayName BSTR InstanceT ype LONG ProfileName BSTR 9 CUserDefinedActivityReference Interface Class Uniquely identifies an IPROTEANUserDefinedActivitySummary object The CUserDefinedActivitySummarys object manages IPROTEANUserDefinedActivitySummary objects Create Using PROTEAN UserDefined A ctivityReference Methods Method Name Comment Copy IsNull Locate SetNull 48 Properties Property Name Data Type Comment DisplayName BSTR InstanceT ype LONG SystemReferenceNumber BSTR
7. 17 18 23 24 26 27 28 29 31 33 35 36 37 38 39 40 49 50 57 58 64 65 List of Figures Figure Name Figure 1 Figure 2 Figure 3 Figure 4 Figure 5 Figure 6 Figure 7 Figure 8 Figure 9 Figure 10 Figure 11 Figure 12 Figure 13 Figure 14 Figure 15 Figure 16 Figure 17 Figure 18 Figure 19 Figure 20 Use Case Model for a Material Handler Regular User Use Case Model for the Warehouse Managers Power User Functional Architecture Technical Architecture Login Screen Password Screen Scan Zone Screen Scan Location Screen Invalid Location Screen Scan Pallet Screen Verify Quantity Screen Enter New Quantity Uncounted Locations Screen Login User Maintenance Database Diagram Inventory Count Report Inventory Count Scorecard Report Inventory Count List Report Code Comments Page 14 15 16 17 19 19 19 20 20 20 21 21 21 22 23 28 29 30 31 32 Figure 21 22 Timeline 34 35 Figure 23 Cost 36 Acknowledgements My thanks and appreciation to Prof Russell McMahon for helping me as my advisor through out the time it took me to complete my Senior Design project I would also like to thank the entire faculty in my achievement of getting the Bachelor s degree from University of Cincinnati Abstract Self Correcting Inventory System By Sandip Kumar Singh Self correcting inventory system a system to resolve all the discrepancies in your inventory This product
8. 2 Select Zone Count and Application prompts user to hit Enter key enter the Zone 3 Enter 01 for zone If valid zone was entered application prompts user to 24 enter the Location that needs to be counted 4 Enter the Location and hit Enter key the pallets that were at that location will be moved to users handheld or the truck and will prompt the user for the pallet ID 5 Start scanning the pallets at that location Application will display either the confirmation message or an error message based on the transaction 6 Once the user is done counting that location a If any pallets were not counted but they exist at the specified location will be moved to CCLOST location b If the pallet was physically at the specified location but not in the system The information will be recorded and displayed on RF Zone Count report c Ifthe pallet was physically at the specified location but in Flex Process it was at another location Application will move it to the specified location and this transaction will show up on the RF Zone Count report 7 User will hit F4 key to go back to Scan Location screen Application will prompt the user to count another location 8 If user wants to continue 25 counting follow steps from 4 6 9 If user doesn t want to continue counting Hit F4 to complete the zone Any un
9. Inventory Pallet A Pallet B Pallet C Pallet D Location ABC No Pallets Location XYZ Tech Expo Presentation g LE THE WORNICK Self Correcting Inventory System Sandip Kumar Singh Tech Expo 2006 Presentation ET dim 2 THE VORNICK ff COMPANY Real time inventory counting Integrated with Enterprise Resource Planning System ERP Real time inventory correction Extensive reporting capability Can be used with handheld devices and desktops Latest technology 58 WORNICK Company Information Headquarters in Blue Ash OH Sister company in McAllen TX Leading provider of Meals Ready To Eat MRE s for United States Military Established in 1985 Employs around 500 permanent and 1000 temporary employees VORNICK COMPANY Area of Need Seeking automated system to count inventory in several warehouses Integration with the existing ERP system is a MUST Avoid manual inventory count at the end of year to satisfy audit requirements Simplified user interface on Flex Process ERP system Real time transactions Present meaningful data via crystal reports to the user 59 WORNICK The Wornick Company www wornick com 4701 Creek Rd Suite 200 Cincinnati OH 45242 Tel 513 552 7400 Fax 513 552 7600 UNIVERSITY OF ON Cincinnati College Of Applied Science WORNICK Self correcting inventory system eliminate
10. LOCA COUNTED INCORRECT LOCA COUNTED INCORRECT LOCA COUNTED INCORRECT LOCA COUNTED INCORRECT LOCA 29 From Loc CCLOST CCLOST CCLOST CCLOST CCLOST CCLOST CCLOST CCLOST CCLOST CCLOST Inventory Count Scorecard Report This report provides similar information as the inventory count report but also gives the ability to the Warehouse Supervisor to review the work and accuracy of the material handler and to find out the accuracy of the worker In the example below under the comments column we can find out whether the material handler counted the correct location or incorrect location For location A102A the accuracy was only 12 596 Based on this data supervisors are able to determine who is doing a poor job and identify the cause of it WOR 102 Location Resource 10 A10 A10 A10 A10 A10 10 A10 A10 10 10 A10 A10 A10 A10 A10 A10 A10 A10 A10 A10 A10 A10 A10 2 2 2 2 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 4810 4810 4810 4810 4810 4810 4810 4810 4810 4810 4810 4810 4810 4810 30 Description Ferroxide Micro Y ellc Ferroxide Micro Y ellc Ferroxide amp Micro Y ellc Ferroxide Micro Y ellc Ferroxide Micro Y ellc Ferroxide Micro Y ellc Ferroxide amp Micro Y ellc Ferroxide amp Micro Y ellc Ferroxide Micro Y ellc Ferroxide amp Micro Y ellc Ferroxide Micro Y ellc Ferroxide amp
11. Micro Y ellc Ferroxide amp Micro Y ellc Ferroxide amp Micro Y ellc Ferroxide amp Micro Y ellc Ferroxide Micro Y ellc Ferroxide Micro Y ellc Ferroxide amp Micro Y ellc Ferroxide Micro Y ellc Ferroxide Micro Y ellc Ferroxide Micro Y ellc Ferroxide Micro Y ellc Ferroxide Micro Y ellc Ferroxide amp Micro Y ellc 24 Unit SANDIP1 SANDIP2 SANDIP3 SANDIP4 SANDIP5 SANDIP6 SANDIP SANDIP SANDIP9 SANDIP10 SANDIP1 SANDIP2 SANDIP3 SANDIP4 SANDIP5 SANDIP6 SANDIP SANDIP SANDIP9 SANDIP10 SANDIP1 SANDIP1 SANDIP1 SANDIP1 Figure 18 Inventory Count Scorecard Report Qty UM Comments 0 CcOcOcocoococoocooooooooooooococo 12 50 BAG BAG BAG BAG BAG BAG BAG BAG BAG BAG BAG BAG BAG BAG BAG BAG BAG BAG BAG BAG BAG BAG BAG BAG INCORRECT INCORRECT INCORRECT INCORRECT INCORRECT INCORRECT INCORRECT INCORRECT INCORRECT INCORRECT INCORRECT INCORRECT INCORRECT INCORRECT INCORRECT INCORRECT INCORRECT INCORRECT INCORRECT INCORRECT INCORRECT CORRECT CORRECT CORRECT From Loc CCLOST CCLOST CCLOST CCLOST CCLOST CCLOST CCLOST CCLOST CCLOST CCLOST 30 Inventory Count List Report This report generates a list of locations that need to be counted on any given day The user can provide the number of days it has been since the location was counted and generate this report The report tells them exactly which site zone and
12. a standard template for functional testing and that same template has been used to get the user to sign off after the unit testing Following is the example of the testing form that will be created to test this application The following form will be created for every use case that is described above in the Use Case model AII of these forms must be signed by the user and the development leader for this application to be implemented in the live environment of The Wornick Company Wornick Unit Testing Form Component Developer Date of Test Self Correcting Inventory Sys Sandip Singh 02 14 2006 Defect ID s If any Applicable Version s 1 0 Workstation Tested On Database Test Production WSTCLIENTO1 Svexch2k Test Description of the Test This application will allow users to count the pallets that reside in a particular zone and location This application will also help The Wornick Company to correct the discrepancies between the physical inventory and the perpetual inventory Reports will be generated based on the data that this application collects to help the Warehouse Managers to make necessary adjustments to correct the physical inventory to avoid the total physical count at all the warehouses Test Step Expected Result Actual Result 1 Log into the RF system Default value screen shows up Make sure that the default site is WOR If not go to Default Values and change it
13. l There can be more pallets at the location according to the system than the actual inventory In this case the extra pallets will be moved to the location called cclost and are considered lost At the end of the day the Warehouse Supervisor will review it and instruct the forklift drivers to physically find the pallets and move them to the correct location There can also be more pallets physically in the location than what the system says When the user scans a pallet that is not on the user s truck the RF system finds that pallet in the entire inventory and moves it back to the scanned location Along with this transaction the system attaches a comment that this pallet should have been in this location but it wasn t This transaction will show up on the zone count report presented to the Warehouse Supervisor 11 User Profile There are two user profiles based on the specifications that were provided by The Wornick Company Warehouse Material Handlers Warehouse material handlers are responsible for using the Self Correcting Inventory System to count the product pallets in the warehouse Material handlers have no direct access to the Flex Process ERP System the transaction must be done via this application Warehouse Managers Warehouse managers will have full access to this application and also to the Flex Process ERP System Flex Process access is provided to them because they are responsible for other areas of the system a
14. the user to review Reports have been designed in Crystal Reports XI Inventory Count Report Inventory Count Report provides transactional information based on given date site location or Zone It includes every transaction that was performed based on above parameters The information is grouped by the activity form that was used to perform the following transactions Site Zone Julian D ate 06119 WOR 02 102 102 102 102 102 102 102 102 102 102 304810 304810 304810 304810 304810 304810 304810 304810 304810 304810 Resc Desc Acty Form Ferroxide Micro Yellow 20 Ferroxide Micro Yellow 20 Ferroxide Micro Yellow 20 Ferroxide Micro Yellow 20 Ferroxide Micro Yellow 20 Ferroxide Micro Yellow 20 Ferroxide Micro Yellow 20 Ferroxide Micro Yellow 20 Ferroxide Micro Yellow 20 Ferroxide Micro Yellow 20 Figure 17 Inventory Count Report Kg Kg Kg Kg Kg Kg Kg Kg Unit LOCCH 00067 SANDIP1 SANDIP2 SANDIP3 SANDIP4 SANDIPS SANDIP SANDIP SANDIPS SANDIPS SANDIP 10 Inventory Count Report IVORNICK COMPANY Last Cnt Dt 06119 06119 06119 06119 06119 06119 06119 06119 06119 06119 Comments COUNTED INCORRECT LOCA COUNTED INCORRECT LOCA COUNTED INCORRECT LOCA COUNTED INCORRECT LOCA COUNTED INCORRECT LOCA COUNTED INCORRECT
15. 00 300 00 300 00 300 00 300 00 150 00 150 00 150 00 150 00 150 00 150 00 150 00 150 00 1500 1500 1500 41 875 00 Baseline 35 000 00 150 00 150 00 450 00 450 00 450 00 300 00 300 00 300 00 300 00 300 00 300 00 300 00 150 00 150 00 150 00 150 00 150 00 150 00 150 00 150 00 1500 1500 1500 441 375 00 Variance 0 00 150 00 0 00 0 00 0 00 0 00 1500 1500 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 300 00 Actual 35 000 00 900 00 750 00 450 00 450 00 450 00 315 00 315 00 300 00 300 00 300 00 300 00 300 00 150 00 150 00 150 00 150 00 150 00 150 00 150 00 150 00 1500 1500 1500 441 75 00 36 Deliverables I will be providing a well designed and easy to use system data integrity is one of my main concerns because the system will affect many different functionalities of the Enterprise Resource Planning 1 10 11 Login functionality will be provided in the application Login screens will run on top of the custom designed SQL server tables Database design will be provided to the client Easy to navigate user interface will be provided All the UT s will be designed based on the client specifications Reports will be provided for the better visibility of the data This feature will also help the Warehouse Managers to
16. 02A Pallet s Found Site Zone Location Unit LastCntDt Date Quantity Mismatched Pallets Acty Form LOCCH 00068 Site LocZone WOR A02 Resource Description Location Unit ID System Quantity User Quantity 304810 Ferroxide Micro Yellow 20 Kg 102 SANDIPS 0 50 BAG 10 00 BAG COMPANY _ p User Login V Kk US metam Vv ON de S Scan the location 1 loca Validate the vocation y ease ante N pallets on the Move the pallet back m the pallet t Note original CCLOST iue ran Add the nono found Pallet pr VORNICK COMPANY Use Case Model for a Material Handler Regular User Use Cases Use case model for supervisors User Login A d Technical Architecture a 8 Gy Access Point 8 Access Point Access Point RF server Wavelink Software Database Server 1 Database Server 2 SQL Server 2000 Cluster 62 WORNICK COMPANY User Interface Wornick RF 5 n login UserID 1 Scan the Zone 1L THE WORNICK COMPANY Wornick RF System Login UserID SSINGH Password Scan the Locatio
17. 4 21 06 Mon 4 24 06 Tue 4 25 06 Mon 6 5 06 Mon 5 5 06 Wed 5 7 06 Fri 6 9 06 Fri 4 28 06 Fri 4 28 06 Mon 5 41 06 Tue 5 2 06 Finish Predecessors Wed 4 12 06 9 Thu 4 6 06 16 Fri 4 7 06 2 Wed 4 2 06 3 Thu 4 6 06 Fri 347 06 Thu 316 06 Fri 347 06 7 Wed 4 5 06 Mon 3 13 06 Tue 3A 4 06 Wed 3 15 06 Wed 3 22 06 Thu 3 30 06 13 Mon 4 3 06 14 Wed 4 5 06 15 Thu 4 27 06 16 Tue 4 11 06 Thu 4 13 06 18 Thu 4 13 06 19 Mon 4 17 05 20 Tue 4 18 06 21 Thu 4 20 06 22 Fri 4 21 06 23 Mon 41 24 06 24 Thu 4 27 06 25 6 12 06 Tue 6 6 06 Thu 5 8 06 28 Mon 6 12 06 28 Thu 5 4 06 Fri 4 28 06 26 Mon 5 1 06 32 Thu 5 4 06 33 34 Resource Mame Sandip Singh Sandip Singh Sandip Singh Sandip Singh Sandip Singh Sandip Singh Sandip Singh Sandip Singh Sandip Singh Sandip Singh Sandip Singh Sandip Singh Sandip Singh Sandip Singh Sandip Singh Sandip Singh Sandip Singh Sandip Singh Sandip Singh Sandip Singh Sandip Singh Sandip Singh Sandip Singh Sandip Singh Sandip Singh Sandip Singh Sandip Singh Sandip Singh Sandip Singh Sandip Singh Sandip Singh Sandip Singh Sandip Singh Sandip Singh Mar 12 106 Mar 13 06 25 16 Apr 2 06 Apr 9 06 Apr 16 06 Apr 23 06 Apr 30 06 May 7 Ot S M T WITIFIS S MIT WITIFTS S MIT WIT F S 1 M T 515 M T WITTF S S MIT WIT IF s S MIT WITIF S S MIT WITIF SIS MIT Sandip Singh Sandip Singh qy Sandip Singh Sandip Singh W Sandip Singh
18. CCLOST 2 304810 Ferroxide Micro Yellow 20 Kg SANDIPS 06119 COUNTED INCORRECT LOCA CCLOST A102A 304810 Ferroxide Micro Yellow 20 Kg SANDIPG 06119 COUNTED INCORRECT LOCA CCLOST A102A 304810 Ferroxide amp Micro Yellow 20 Kg SANDIPT 06119 COUNTED INCORRECT LOCA CCLOST 2 304810 Ferroxide Micro Yellow 20 Kg SANDIPS 06119 COUNTED INCORRECT LOCA CCLOST 2 304810 Micro Yellow 20 Kg SANDIPO 06119 COUNTED INCORRECT LOCA CCLOST A102A 304840 Ferroxide amp Micro Yellow 20 Kg SANDIP10 06119 COUNTED INCORRECT LOCA CCLOST VORNICK COMPANY Lost Pallets Site Location Resc RescDesc Unit LastCntDt Comments FromLoc WOR CCLOST 304810 Ferroxide amp Micro Yellow 20 Kg SANDIPS 06118 LOST PALLETS A102A CCLOST 304810 Ferroxide amp Micro Yellow 20 Kg SANDIP4 06119 LOST PALLETS A102A CCLOST 304840 Ferroxide amp Micro Yellow 20 Kg SANDIP7 06119 LOST PALLETS CCLOST 304840 Ferroxide amp Micro Yellow 20 Kg SANDIP 10 06119 LOST PALLETS A102A CCLOST 304810 Ferroxide amp Micro Yellow 20 Kg 06119 LOST PALLETS AT02A CCLOST 304810 Ferroxide amp Micro Yellow 20 Kg 06119 LOST PALLETS A102A CCLOST 304810 Ferroxide amp Micro Yellow 20 Kg SANDIP3 06119 LOST PALLETS A102A CCLOST 304810 Ferroxide amp Micro Yellow 20 Kg 2 06119 LOST PALLETS A102A CCLOST 304810 Ferroxide amp Micro Yellow 20 Kg SANDIPS 06119 LOST PALLETS A1
19. Self Correcting Inventory System By Sandip Kumar Singh Submitted to Prof Hazem Said the Faculty of the Information Engineering Technology Program in Partial Fulfillment of the Requirements for the Degree of Bachelor of Science in Information Engineering Technology University of Cincinnati College of Applied Science June 2006 Self Correcting Inventory System By Sandip Kumar Singh Submitted to Prof Hazem Said the Faculty of the Information Engineering Technology Program in Partial Fulfillment of the Requirements for the Degree of Bachelor of Science in Information Engineering Technology Copyright 2006 Sandip Kumar Singh The author grants to the Information Technology Program permission to reproduce and distribute copies of this document in whole or in part Sandip Kumar Singh Date Prof Russell McMahon Faculty Advisor Date Prof Patrick C Kumpf Ed D Interim Department Head Date Table of Contents Section 9 Project Description Statement of Problem Description of Solution User Profile Design Protocol Functional and Technical Architecture Proof of design User Interface Testing Database Diagram 10 Reports 11 12 13 Timeline Cost Deliverables 14 Conclusions and Recommendations 15 Flex Process database documentation Appendix A 16 Supporting documentation 17 Tech Expo slides 18 References Page 10 11 12 13 15 16
20. all the pallets on the handheld location Scan all the physical pallets back to the location Pallet exists on the handheld location Yes v Move the pallet back in the original location Y Search the entire inventory for the pallet and move it in the original location pallets moved back to the original location v Display confirmation message v Move rest of the pallets from the handheld to LOST location 16 Figure 4 Technical Architecture J 9 UO User User User User User Access Point Access Point Access Point Storage Area Network RF server Wavelink Software Database Server 1 Database Server 2 SQL Server 2000 Cluster 17 Proof of design User Interface Material handlers will log into the RF application using the handhelds This application is character based and has no mouse or touch screen functionality associated with it After the successful login the user will be able to start counting the material in the warehouse by using the Self Correcting Inventory System Graphics This system is completely character based and a graphical interface is not provided to the extent because of the system limitations Function keys will be used to navigate between the screens and they will be made visible and highlighted on the screen for the user User Manual The user will be provided with the use
21. cess errors Appropriate forms are created in the Flex Process depending on the kind of activity Statement of Problem When material handlers misplace product in the warehouse it cannot be found and an inventory adjustment is made This causes discrepancies between the perpetual and physical inventory The misplaced product is reported as lost in the system Due to this material handlers are not able to locate product in the warehouse Also there is no way to perform this function in real time Material handlers are forced to write the transactions and manually enter them in the system The Wornick Company would like to count products in all the locations within the warehouse and match the physical inventory with the system the locations will be counted every month and the application will generate a report of all the locations that are due for counting Description of Solution I have written a wireless application to count and correct the discrepancies at all five warehouses This application will build RF interface between the ERP system and symbol scanners used by warehouse coordinators Flex Process ERP system API s Application Program Interface will be used to enhance Flex Process s functionality by utilizing mobile computing devices These devices perform transactions in real time using barcode technology to automate the process and reduce the human error factor Flex Process ERP and company business rules will be enforced The app
22. correctly make sure that the CResourceReference SiteName Property for the reference for the ToResource is set to the correct To Site Create Using Methods on IPROTEANUserDefActySummaryLineltems Methods Method Name Comment ChangeAll 44 CopyLinelItem GenerateUnits GetAlternateFactor GetAlternateQuantity GetPrimaryQuantity GetQuantityCalcFlag RetrieveCharacteristics RetrieveDefaults SetAlternateFactor SetAlternateQuantity SetPrimaryQuantity SetQuantityCalcFlag Properties SuspendLineltem Property Name Data Type Comment CDateTime AgeDate2 CDateTime AgeDate3 CDateTime AgeDate4 CDateTime AgeDate5 CDateTime Classification CClassificationReference Comments BSTR ContainerlD BSTR CorrLinelD BSTR CostCenter CCostCenterReference CreateLooseQuantity CBoolean DateCode BSTR Errors CBoolean ETDDate CDateTime ExpirationDate CDateTime GrossQuantity CQuantity HasSpecialReqmts CBoolean InstructionType LONG LinelD BSTR Location CLocationReference Lot CLotReference ManufacturingDate CDateTime MiscField1 BSTR MiscField2 CDouble MiscField3 BSTR MiscField4 CDouble OriginalLinelD BSTR 45 PickingOverrideSequence CUnsignedlnteger ReasonCode CReas
23. counted locations under the specified zone will be displayed with the option to allow the user to count more or to end the application 10 User hits Y to count more Application will prompt the user to scan another location 11 User hits N Application ends Test Executed By Date Development Lead Date 26 Database Diagram The database design for this application is very complex The database architecture is provided by the Flex Process ERP system In some cases the database is extremely normalized and in others extremely de normalized I cannot make any changes to the database design and will have to study and understand the entire database structure necessary for this application The database design for this application consists of twelve tables These tables refer to several different objects in Flex Process ERP system including lot site inventory inquiry user defined activity form area location Most of these tables are very big and contain several million rows If the application experiences any performance degradation I have analyzed it and create the necessary indexes Following is the database design for the Self Correcting Inventory System and also for the administration application 27 ValueTatielnst Type ValusTatie ValueTabieO wn ValueTabieCwnOsID Updates Sec GrotObjeetID Figure 16 Database diagram 28 Reports This system provides three reports for
24. efActySummaryLineltems property on the IPROTEANUsSerDefinedActivitySummary object Create using the following class Property that creates this class UserDefActySummaryLineItems Methods Method Name Comment Add Item Remove Properties Property Name Data Type Count Integer 43 4 IPROTEANUserDefActyLineCharacteristics Interface Class Manages the IPROTEANUsSerDefinedLineItemCharacteristic object The IPROTEANUserDef ActyLineCharacteristics object is created by getting the UserDefLineItemCharacteristics property on the IPROTEANUsSerDefinedActivityLine object Create Using Property that creates this class UserDefActyLineCharacteristics Methods Method Name Comment Item Properties Property Name Data Type Comment Count Integer 5 IPROTEANUserDefinedLine Interface Class Corresponds to a line in an activity form for user defined activities IPROTEANUserDefined Line objects are managed by the IPROTEANUserDefActySummaryLineltems object Getting the UserDefActyLineCharacteristics property creates a manager for an IPROTEANUserDefinedLineltemCharacteristic object The From Site and To Site are based on the sites specified in the Resource and ToResource properties In order to set the From Site correctly make sure that the CResourceReference SiteName Property for the reference for the Resource is set to the correct From Site In order to set the To Site
25. haracteristic IPR ryDel n CUserDefinedActyProfileReference CUserDefinedActivityReference 1 CUserDefinedActivitySummary class has the following methods Method Name Comments Close CreateObjectReference CreateWith Execute IsAvailableForProcessing OpenForChange OpenForReview Remove RemoveByReference Save Validate CUserDefinedA ctivitySummary class 2 IPROTEANUserDefinedActivitySummary Interface Class Used to enter user defined activities to record changes in inventory placement or status including e inventory movement such as a change in site area and location 41 e inventory transition into from a cost center such as between a location and cost center or between cost centers e change of classification of the product Getting the UserDefActySummaryLineltems property creates an object that manages lines in the user defined activity form IPROTEANUsSerDefinedLine objects This object also requires a user defined activity profile which determines rules for activity reporting and aspects such as field defaults layout inclusion and restrictions The user defined profile is also not created with OLE automation Create using the following class Managing Class CUserDefinedActivitySummarys Properties Property Name Datatype Comments ActivityDate CDateTime AddOns CAddOns Adm
26. inistrativeSite CSiteReference AuditState LONG AutomationFailureFlag CBoolean CheckedOutDate CDateTime Comments BSTR Creator BSTR CreatorID BSTR CurrentEditor BSTR CurrentEditorID BSTR DateCreated CDateTime Description BSTR Description2 BSTR FiscalDate CDateTime FiscalPeriodData BSTR FiscalSubperiodData BSTR FiscalYearData BSTR LastActionIndex CUnsignedlnteger LastEditDate CDateTime LastEditor BSTR LastEditorlD BSTR Modified BOOL CUserDefinedActivity ObjectReference Reference OpenByDEP CBoolean OperatorID BSTR COrderAutomRefere OrderAutoDesign nce 42 OverrideAllUserEvents ToDelayed CBoolean RelatedToTriggeringObjectClassID CLong RelatedToTriggeringObjectString BSTR ReportingDate CDateTime ReportingShift CUnsignedlnteger ReportingSite CSiteReference SkipDEPNotify CBoolean TargetTriggeringObjectClassID CUnsignedLong TargetTriggeringObjectObjectlD CUnsignedLong CTransferListReferen TransferList ce Updates long UserCommentString BSTR UserDefActySummaryDelayedEvents IPROTEANUserDefActySummaryDelayed Events UserDefActySummaryLineltems IPROTEANUserDefActySummaryLineltem 5 CUserDefinedActyPr UserDefinedActivityProfile ofileReference UserReferenceNumber BSTR 3 IPROTEANUserDefActySummaryLineltems Interface Class Manges IPTOREANUserDefinedActivityLine objects The ProteanUserDefActySummar Lineltems object is created by getting the UserD
27. is being developed for The Wornick Company to address the inventory issues they are experiencing due to human errors When material handlers misplace product in the warehouse it cannot be found and an inventory adjustment is made This causes discrepancies between the perpetual and physical inventory The misplaced product is reported as lost in the system Due to this material handlers are not able to locate product in the warehouse Also there is no way to perform this function in real time Material handlers are forced to write the transactions and manually enter them in the system Self correcting inventory system is a wireless application and will allow the users to count the inventory in real time Multiple levels of validation are performed to minimize the errors Easy to navigate screens are implemented for users Reports will be generated for Warehouse Supervisors for review The Wornick Company uses the Flex Process ERP System to support their business Flex Process has modules to support production customer order management Finance human resources and the warehouse management system This product interfaces with Flex Process ERP Enterprise Resource Planning system and utilizes ERP API s Application Program Interface to populate the database Project Description The Wornick Company is a recognized innovator in the food industry providing meal solutions in manufacturing processing packaging assembly and new food product developme
28. let B Pallet C Pallet D Pallet C Location 123 Location XYZ No Pallets Pallet D Handheld Location HH1 Location XYZ In the above case pallet D is physically located in location 123 but in the system it resides at location XYZ As the user scans location 123 pallet A B and will be moved to handheld location This move is described in the following slide Physical Inventory System Inventory Pallet A Pallet B No Pallets Pallet C Pallet D Location 123 Location 123 Pallet A Pallet B Pallet D Handheld Location HH1 Location XYZ Pallet A B and C have been moved to handheld location by the application Pallet C User scanned pallets B and they were moved from the handheld location to its original location 123 Physical Inventory System Inventory Pallet A Pallet B Pallet A Pallet B Pallet C Pallet D Pallet C Location 123 Location 123 No Pallets Handheld Location HH1 Location XYZ As soon as the user scans Pallet D from the its physical location ABC the application will search the entire inventory in the system to try to find Pallet D The application will find that Pallet D is actually located in XYZ location but physically it is in ABC location Application will move Pallet D from XYZ to ABC and thus the physical inventory and system inventory 56 Physical Inventory Pallet A Pallet B Pallet C Pallet D Location ABC No Pallets Handheld Location HH1 System
29. lication will run on symbol handheld devices and will communicate with the server using Wavelink software The RF environment will allow the end user to scan type and move freely within the working area Non RF devices like wall mounted or Tablet PC s can also interact with the Wavelink software This solution will make real time inventory correction possible and minimize the loss of time and material The purpose of this system is to match the system inventory with the physical inventory A site will be broken into several small pieces called zones Locations come under zones and different areas of the warehouse are considered different zones The user will scan the zone in the RF The system will validate that the user scanned a valid zone The next step is to scan the location the location must belong to the zone or the system will generate an error message for the user Once the valid location is scanned all the pallets are moved onto the user s truck is not necessary that the system data and physical pallet match The only pallets that will be moved onto the user s truck are those that the system says are in that location The user will start scanning the physical pallets and during this process pallets will be moved back to that location one by one There are two different scenarios that can 10 occur during this process Note that the location has been scanned and all the pallets from that location have been moved to the user s truck
30. location they need to count today The report also displays the aged days since the location was counted Zone Count List Report bi Y ORNICK Site COMPANY Zone Age 120 Only Past Due False Site Zone Location Description Last Cnt Date Aged Days A01 TO 01 101 A101b 0 101 0 02 WOR A02 CCLOST Lost location 06123 116 HH1 Handheld1 0 102 102 08119 112 102 A102b 06119 112 Figure 19 Inventory Count List Report 3l Code Comments Module level and procedure level comments have been implemented in the application Complex code inside the procedure has also been properly documented For example Purpose Creates a user defined activity form in Flex ERP system Input Variables UserActivityProfile As String Output Return value ByVa ByRef ByVa ByVa ByVa ByVa ByVa ByRef ByRef ByRef ByRef ByRef ErrorChain As String ReportingDate As Date Site As String UserReference As String Shi ft As Integer OperatorID As String ActivityNumber As String oMy e oMy oMy oMy Variables NONE UserActyProfileRef As UserDefinedActyProfileReference UserActySumRef As UserDefinedActivityReference UserActySumMgr As CUserDefinedActivitySummarys UserActySum As PROTEANUserDefinedActivitySummary True if successful False in case an error occurs Remarks This procedure must succeed fo
31. make adjustments to the inventory UFs will have validations for all the critical input from the users to minimize This solution will make real time inventory correction possible and minimize the loss of time and material This application will match the system inventory with the physical inventory Reports will be created for the Warehouse Managers for review This application will eliminate the mandatory physical count for the entire warehouse at the end of the year User manual will be provided Code will be commented based on the client s standard Technical documentation will also be provided 37 12 User maintenance will be provided to the Warehouse Manager to maintain the users that are allowed to use the wireless application Application will be written in VB Net and SQL Server 2000 will host the database to provide the back end support 13 Application will be accessible from the handheld devices and the desktop computer via an emulator 14 the Enterprise Resource Planning business rules will be followed 38 Conclusions and Recommendations Self correction inventory system was developed to satisfy the current needs of The Wornick Company This application will help the company to resolve several issues with their inventory system It was very exiting project for me During the development of this application I learned a lot about project management and also extended my technical knowledge
32. n 4 7 1 User Interface Scan The Pallet LLL Loc A1BLA Enter New Quantity porem EA Quantity 1628 E Y If Correct W If Incorrect 63 VORNICK COMPANY User Interface RF User Maintenance Name User ID Initials Site I Password 1D UserName usem initials Password Site 145 alisha rogere aMisharog ARC bubal00 RFTRUCK 13 Amber Amber AKW bubal RFFIRS AnnN AWN buba3 RFFIRS 147 bara seck baraveck hello10 RFFIRS 42 BerthaF BFQA bubag9 RFTRUCK 120 BUM BMW buba74 RFFIRS 15 DrianD buba3 RFFIRS z Charles W Charles W cww bubal RFTRUCK 34 DaleC Dalec Dew bbuba2l RFFIRS 17 DarrylH DHW bubas RFFIRS 137 DebbieM cCormick debbiemc DMW hello4 RFTRUCK 140 Dexter Norman dexterno DNW hotlos RFTRUCK 64 DianaC DianaC taylor RFTRUCK 145 stoy galan sloygalan ROW hellol t RFFIRS 125 EHW bbuba77 RFFIRS Fricl Frick ELW buba33 RFFIRS ibrahim moussa ibrahtmmo 12 RFFIRS j Delete Add Clear Exit References 1 Hanco Enterprise specializes in RF development http www hanco ent com 2 SSA Global is the provider of the ERP system http www ssaglobal com 3 Symbol devices handhelds truck mounts and scanners will be used http www symbol com 4 Wavelink COM software will be used to make the communication pos
33. n in the zone that is being counted All locations in a zone should be scanned If any locations in a particular zone are not scanned the following screen in figure 13 will be displayed Uncounted Locations A161B A102A Figure 13 Uncounted Locations Screen Pressing Y will enable you to scan another location for the current zone If you do not wish to count any more locations in the current zone press N and exit zone count Any remaining items found in locations that have not been scanned will be moved to CCLost in the system until they are physically scanned at another point in time 21 User Maintenance This application will be created for the warehouse managers Warehouse managers are considered administrator of the entire Warehouse Management System The following login screen requires an account that has access in Flex Process ERP system Figure 14 Login 22 After a successful login warehouse managers will be able to change delete and add new users in the system to use the RF application Wornick RF User Maintenance alisha rogers bara seck BerthaF BrianD Charles W DaleC DarrylH DebbieMcCormick debbiemc Dexter Norman dextero DianaC DianaC eloy galan eloygalan EricH EricH EricL EricL ibrahim moussa i i Figure 15 User Maintenance 23 Testing The Wornick Company has
34. nd must have full control of the inventory control receiving and shipping module of the ERP system Warehouse managers will also have access to the maintenance application that will be used to add and delete users from the system 12 Design Protocols Use Case Diagram The use case is divided into two main sections based on the user profiles discussed above Self Correcting Inventory System will be access from several 20 30 handheld devices This system will have different access for the Material Handler and the Warehouse Managers The use case is explained in the following diagram for the material handlers 13 Figure 1 Use Case Model for a Material Handler Regular User Use Case Model for a Material Handler Regular User uses Move all the pallets on the truck extends Scan the pallet Move the pallet to extends CCLOST location extends Move the pallet back to the original location Add the pallet to Wornick found Pallet table 14 Figure 2 Use Case Model for the Warehouse Managers Power User Use Case Model for x4 User Login Warehouse Supervisors extends uses D N 15 Figure 3 Functional Architecture User login Success Yes Scan the zone ZEN NO Valid Zone pon eee Yes Scan the Location Valid Location Yes y Move
35. nt Domestic and international food marketers the U S government the Canadian government and other governments around the world look to help them test develop and introduce new products refine existing products and meet their ever changing needs The Wornick Company has two divisions The Prepared Food Division is located in Cincinnati OH and is responsible for making packing and shipping the food to the Right Away Division in McAllen TX The Right Away Division is a distribution center for the Wornick Company The Wornick Company has about 1 000 employees between Cincinnati and Texas The Wornick Company uses the Flex Process ERP Enterprise Resource Planning System to support their business Flex Process has modules to support production customer order management human resources and the warehouse management system Flex Process is also integrated with another system named Avantis Avantis is utilized by the purchasing department Flex Process doesn t provide any functionality for Radio Frequency RF out of the box Applications can be written using the application program interface provided by Flex Process The decision was made to design a RF system by the internal staff and add it on to the Flex Process system The RF system is built on Flex Process and it also enforces all the business rules of Flex Process One cannot do any transaction via RF that cannot be done in Flex Process the errors that occur on the RF are actually Flex Pro
36. onCodeReference Resource CResourceReference ResourceDescription BSTR RetestDate CDateTime RevisionLevel BSTR RotationDate BSTR SampleNumber BSTR ScaleAdjustmentQuantity CQuantity Status LONG TareQuantity CQuantity ToCostCenter CCostCenterReference ToLocation CLocationReference ToLot CLotReference ToResource CResourceReference ToResourceDescription BSTR ToUnitID BSTR ToUnitType CUnitTypeReference UnitID BSTR UnitType CUnitTypeReference UpdateGeneralLedger CBoolean Updates long UserDefActyLineCharacteristics IPROTEANUserDefActyLineCharacteristics UserDefinedActivityT ype CUserDefinedTransT ypeRef VehiclelD BSTR 6 IPROTEANUsSserDefinedLineltemCharacteristic Interface Class Holds information about a characteristic for this IPROTEANUserDefinedLine object The characteristic you assign to this object must already exist as CCharacteristicReference IPROTEANUserDefinedLineItemCharacteristic object is managed by the IPROTEANUserDefActyLineCharacteristics object Create Using Methods on IPROTEANUserDefActyLineCharacteristics Methods Method Name Comment None 46 Properties Property Name Data Type Comment AlphanumericValue BSTR BaseNumericValue CDouble BaseUM CUMReference BooleanValue CBoolean Characteristic CCharacteristicReference InventoryCharacteristic CBoolean LimitedTOValueTable CBoolean LimittoValueTable BOOL NumericValue CDouble
37. r manual paper printed to help them use this system because it is impossible to incorporate the help files with this system Following are the screen designs for the Self Correcting Inventory System the screens are designed using the Wavelink 3 7 software 18 Login In order to enter the system you must have a valid username and password This application requires a username and password for the following two reasons e Security Tracking Purposes 1 When you first come to your device you will see the login screen as shown in Figure 5 UPEI 1L Figure 5 Login Screen 2 Type in your username and then press the enter key Now you will be prompted for your password as shown in Figure 6 Wornick RF System Login UserID SSINGH Password Figure 6 Password Screen Self Correcting Inventory System 1 Next you will see the screen shown in Figure 7 Scan the zone barcode for the zone you are about to count Scan the Zone Figure 7 Scan Zone Screen 19 2 After you successfully scan the zone scan the first location that belongs to the current zone Scan the Location KF4 gt Zone Complete Figure 8 Scan Location Screen NOTE If you scan an invalid location the screen shown in figure 8 will appear This occurs if an invalid location is scanned for the current zone Invalid Location Scan the Location Figure 9 Invalid Location Screen
38. r the application to continue Figure 20 Code Comments 32 Timeline e Learn Current System This part of the project requires commitment from The Wornick Company stakeholders and Mr Sandip Kumar Singh During this task regular meetings will be scheduled to explain in detail the way this application needs to be designed e Preliminary Documentation The project proposal will be reviewed and signed by The Wornick Company After signing the proposal the project specifications will be developed and sign off will be required from The Wornick Company Database Design The Wornick Company will provide a logical database design of their system to help me understand the system and develop the solution Application Coding This will entail completion of all of the development that is required for this project In addition I have developed an application that interfaces with Flex Process ERP system using the API provided by SSA Global e Testing Quality assurance will be performed by the developer before the product is released to The Wornick Company Due to possible conflicts between testing environments it will be necessary for thorough testing to be performed on site after the product is released and before the implementation The Wornick Company employees will be engaged in the user acceptance testing and will be required to sign off on it 33 O oh w ui F2 p Cee e
39. s all the discrepancies in your inventory This product is being developed for The Wornick Company to address the inventory issues they are experiencing due to human errors When material handlers misplace product in the warehouse it cannot be found and an inventory adjustment is made This causes discrepancies between the perpetual and physical inventory The misplaced product is reported as lost in the system Material handlers are forced to write the transactions and manually enter them in the system Self correcting inventory system is a wireless application and will allow the users to count the inventory in real time Reports will be generated for Warehouse Supervisors for review This product interfaces with Flex Process ERP Enterprise Resource Planning system and utilizes ERP API s Application Program Interface to populate the database 60 VORNICI COMPANY Advanced reporting with Crystal Reports XI me Inventory Count Report TN ORNICK Julian Date 06119 COMPANY Site Zone Loc Resc Resc Desc Unit Last Cnt Dt Comments Erom Loc Acty Form LOCCH 00067 WOR A02 A102A 304810 Ferroxide Micro Yellow 20 Kg SANDIP1 06119 COUNTED INCORRECT LOCA CCLOST A102A 304810 Ferroxide amp Micro Yellow 20 Kg SANDIP2 06119 COUNTED INCORRECT LOCA CCLOST A102A 304840 Ferroxide amp Micro Yellow 20 Kg SANDIP3 06119 COUNTED INCORRECT LOCA CCLOST A102A 304810 Ferroxide Micro Yellow 20 SANDIP4 06119 COUNTED INCORRECT LOCA
40. sible between the handheld devices and the RF server http www wavelink com Prof Russ McMahon Mr Jason Watson Ms Kimberly Harmeyer Mr Manuel Cota UA 65
41. tion XYZ Pallet B Pallet C Pallet D Handheld Location User scanned pallets A and it was moved from the handheld location to its original location XYZ The user continues to scan the physical pallets and they keep moving back the location XYZ 52 At this point the application has moved B and back to the original location but we still have Pallet D sitting on the handheld Since pallet D is physically not at that location the user will not be able to scan it and is unaware that there is still a pallet residing at the handheld location The user will exit out of the system thinking that he she is done counting that location The application will move the pallet to fictitious location CCLOST All the pallets sitting in the lost location will be accounted for later by using this application or manually by the warehouse supervisor Physical Inventory System Inventory Pallet B Pallet A Pallet B Pallet C Pallet C Location XYZ Location XYZ Pallet A No Pallets Handheld Location HH1 Lost Pallet Location CCLOST This scenario will describe the application flow when there is discrepancy between the system inventory and the physical inventory and there are more pallets in the physical location and less in the system As the user counts the location the system inventory and physical inventory will be matched Physical Inventory System Inventory Pallet A Pallet B Pallet A Pal
Download Pdf Manuals
Related Search