The client is a manufacturer of custom hollow metal doors and frames operating out of 100,000 square feet of manufacturing facility in the West Coast, USA. With more than 30 years of experience in the industry, the company supplies order-to-manufacture doors and frames across the country.
- 200+ sales order data was received every day that needed to be managed, distributed, worked upon, and order details such as name, sales details, etc. added to spreadsheets. This was adversely impacting engineers’ productivity.
- To eliminate this non-productive task, the team needed an automation solution that would not just address productivity issues but also to maintain data accuracy of orders.
- With more than 20 engineers working on a shared spreadsheet having 30+ column heads, filtering and identifying similar type of jobs was difficult.
- Typographical errors in manual population of spreadsheets resulted in inaccurate data. It caused hassle in identifying each sales order and assigning it to the respective project engineer.
- Project engineers studied the internal flow of tasks starting with work received from client until it was shipped back to the client.
- The findings suggested that the processes followed at the client’s end to generate sales quotations were done using the details available in the ERP.
- The project team suggested leveraging this same data to fill up their spreadsheet and assign work automatically on local SQL database by using an SQL connector.
Solution & Results.
The solution was designed by focusing on automating project management tasks, assigning work, and identifying the same type of jobs for better resource allocation. It aimed to achieve:
- Elimination of non-productive tasks of entering job details in spreadsheets
- Extracting data from client’s ERP
- Save time in data management and utilize it for process excellence
- Create additional work reports by using data generated at Hitech engineers’ end.
The project development team scripted an automation tool using VB scripts to integrate ERP systems with the SQL database and import pending work-related information to the local database. The tool was equipped with Start and Stop functionality to capture modeling and QC time for every specialist working on the project.
A VPN tunnel was set up between Hitech’s server and the client’s server to achieve the automation setup and maintain the productivity report of all the jobs assigned to engineers. Real-time visibility for all jobs in progress was made available by setting the synchronization time of five minutes between Hitech’s SQL and client’s SQL.
Development of project studio functionality elevated project engineers’ productivity and offered:
- Secure data retrieval from customer’s SQL through VPN tunnel
- On-time availability of records with top-down planning based on the shipping date
- Capturing time for modeling and QC process with regular productivity reports