Stretching Your Dollars: Adding to MFG/PRO without Subtracting From Your Wallet

1. Stretching Your Dollars: Adding to MFG/PRO without Subtracting From YourWallet Dave Ruetz MIS Manager Driv-Lok Inc.

2. Anticipated Outcome: • The attendee will gain insight into how to best gain value from the standard QAD offering. • You will be shown examples of how the IT department of a small to midsize company can easily access the data in MFG Pro, manipulate it and make it readily available to management. • Learn how to get at the data and solve your business headaches.

3. “How To’s” • How to use ODBC drivers to interface between off the shelf programs and QAD data. • How to use a side database and a data extract progress program to act as a source of data for custom data and shipping labels. • How to use off the shelf windows programs to send extracted data to recipients on a regular basis. • How to use extracted data to send to a public information display to keep company employees informed of current information.

4. Driv-Lok Established 1939 • Driv-Lok is a well-integrated manufacturer of metal fabrication equipment and high volume component production. Driv-Lok strives to be the preferred source of innovative, cost-effective solutions for our customers who require press fit fastening, joining, and special component applications. Driv-Lok also strives to provide the highest quality, superior customer service, shortest lead times, and unequaled technical assistance through a team of dedicated professionals. • Driv-Lok’s operations consist of the manufacture and sale of industrial fasteners to the automotive, electrical, hardware, industrial, medical, and sport industries throughout the world.

5. MIS Statistics • 120 employees • 120 PC's • 1 AIX RS6000 (QAD) • Exchange Server (Windows 2000) • File Server (Windows 2000) • Fax Server (Windows 2000) • ADP Server (Windows 2000) • Intranet Web Server (Windows 2000) • Windows Update Server (Windows 2000)

6. QAD and Related Offerings • QAD Core modules EB2, SP9, Customer/Supplier Schedules • QAD Customer since 1988 • Eagle Shop Floor • Cyberquery Reporting Solution • WPA EDI Solution

7. What you will see here: • A label application using ODBC and a side database to make easier and “more” error-proof labels. • A public information display for our employees • The use of project manager to display scheduling data • Exception reporting for sales and operations.

8. Problem: Increased labeling requirements driving us “NUTS!”. Error Proofing a NECESSITY!.We have 180 custom label formats and 1 default format. Some customers require 10 or more fields.

9. Blank Customer Label

10. Manual Shipping Label

11. Label Input With Interface

12. Goal: To more quickly and more accurately generate our shipping labels. • Most information is currently stored in QAD. • We wanted one repository of the information (QAD) • Labels should be as error proof as possible • Solution should be easily changeable.

13. Possible Solutions • QAD Partner for labeling. Very nice and well integrated, but too expensive for us. We wanted a lower cost solution. • Hire outside programmer (may work) • Integrate existing label software • Investigated found to be ODBC compatible • Is flexible enough to fulfill our needs • Is a very good value and our people already understand it.

14. Considerations • Some information stored in QAD (so_mstr,sod_det, pt_mstr, etc) • Others, no fields designated for customers individual needs. • Information given on customer PO or on EDI release data. • Tharo Easy Label program to connect via ODBC for live information.

15. Considerations • Use the pt_mstr:pt_chr fields to store static information given by customer • Key to the data is ship to code and finished goods part number. • Store the information that rarely changes but needed for the labels. Info may differ for the same customer but different ship-to’s.

16. Field Maint. Screen on QAD

17. By entering the sales order and line number, most information needed can be retrieved directly from QAD. • For flexibility a side database was implemented with an extract program that runs every 5 minutes to gather data from the so_mstr, so_det, pt_mstr, ad_mstr, etc. • Label program connects ODBC to this side database as well as standard QAD tables.

18. Side Table Fields

19. ODBC Setup • Install progress on PC that will connect via ODBC • Install the Merrant Driver • Configure Connection in ODBC Manager

20. ODBC

21. ODBC

22. Label Input With Interface

23. Label ODBC Connection

24. Label ODBC Connection

25. Label ODBC Connection

26. Side Benefits: • By doing a check of the lot number entered to the lot number in the wo file we can make sure that the correct lot has been selected. • Can keep from shipping very similar parts. • Error proof against transposed digits in the lot number field.

27. Interfaced Label with Lot Checking

28. Goal: Disseminate More Information • Employees are asking more questions about the state of the business and management wants to keep them informed. • How did we do yesterday? • What are we expected to ship this month? • What is happening today? (Message of the day)

29. Solution: Extract Information and Display on a TV Screen

34. How? • Progress & Cyberquery programs used to generate text files with desired information. • Text files are ftp’d in the background every 2 minutes from the AIX host. • Display server software and hardware formats the information and outputs to TV Cable format. • All information updates as changes are made.

35. FTP Script Files On Display Server

36. Goal: To easily visualize the amount of work on the shop floor • Dispatch Lists work well in SOME situations • Managers wanted to be able to look at visual summary of WIP

38. Solution: Import Dispatch List Extract into Project Manager • Cyberquery reporting solution creates Excel file every 2 minutes • Excel file is ftp’d into project directory • Project Manager imports Excel file using pre-defined maps

39. Goal: Operations to manage by exception. • With fewer doing more, management by exception is EXTREMELY important • Need to be able to look at concise exception reports to make sure “nothing is falling through the cracks”. • Need for reports to be delivered automatically so they are waiting upon arrival at work.

40. Solution: A series of exception reports from various sources.

43. Goal: Sales needs better “tailored” tools. • Similar to operations in that “need to do more with fewer” • Need to be able to respond quickly to customer requests (preferably while on the phone). • Need to deal with less paper. • Need to make sure “nothing slips through the cracks”

44. Solution: A series of exception reports from various sources.

46. Sales Order Line Pricing Differs

47. Customer Master with No Invoice

48. Non-Confirmed Sales Orders

49. Goal: How to easily email off-site on a scheduled. • Must be simple • Must be able to handle unlimited amounts of email • Must be able to be scheduled

50. Solution: Off the shelf email Program • Our solution was $129.00 • Installs on Exchange server (or elsewhere) • Create batch file where program is called with command line parameters (recipient, sender, subject, etc) • Batch file can be scheduled with Windows Scheduler