Overview
The following tutorial focuses on importing/ uploading of data from the Batch PDT mode of bar code scanners in a WIP component consumption scenario.
Background: Collecting the data for inventory transfers (PO receipts, WO picks/receipts, WIP part consumption etc) can often be done more productively using a barcode scanner. Barcode scanners come in 3 basic modes:
Wedge
RF Portable Terminal
Batch Portable Terminal.
The easiest, quickest, and most cost effective is to install a keyboard wedge device. This is a bar code reader that has a Y-cable, where you unplug your keyboard, plug the Y-cable into the keyboard port and the keyboard into the Y-cable. All scans made with the bar code scanner appear like keyboard entry to the application. The wedge barcode reader requires a PC at the data collection workstation.
Since wedges are very straightforward (i.e. plug them in and scan rather than type), they will not be discussed further in this tutorial.
A more complex system, let's say an RF system with several PDTs (Portable Data Terminals, small hand held devices with integrated laser scanners) with antennas attached to a network. An RF system is usually beyond most users to install themselves and thus, will not be discussed in this tutorial.
Batch systems use a Portable Data Terminal (PDT) which has it's own program and stores the data for periodic uploading to the host. Batch PDT's are easy to implement and can be less expensive than a wedge because they require no PC at the data collection workstation and are substantially less expensive than RF systems. In addition, shop floor procedures and related training are straightforward,thus improving quality and productivity.
ERPlite has automated utilities for importing the data from most PDT's:
Admin > Barcode > follow on screen instructions
If these utilities are not sufficient for your needs, then the Microsoft Access import untilities can be used to handle almost any data. Read the rest of this tutorial to learn how:
Do it yourself import instructions
The rest of this tutorial will demonstrate the importing (uploading) of data from a Batch PDT. Since there are many opportunities to scan data in, we will demonstrate one very common scenario. Adaptation of this demo to other scan scenarios can be easily done by a person with basic Microsoft Access skills.
The demo is "manual" in the sense that the user must perform a few steps to complete the scanner data import. These steps include some Access Queries. The manual method is more than adequate for many companies. However, the manual method makes a perfect rapid prototype and can be easily fully automated by someone with basic Visual Basic programming skills.
WIP Consumable Component Consumption Scenario
The WIP component consumption scenario will be used throughout the tutorial. Components in WIP inventory can be consumed via the WO Receipts or Inventory Transfer forms. WO Receipts is very efficient if a WO was issued for a multilevel BOM. The WO picklist transfers the components from Stores to WIP. The WO Receipt then backflushs the components out of WIP when the finished good is transferred to Stores.
However, when consumables (i.e. items that are stored in bins on the shop floor , say screws, resistors etc) are used on the Production floor, they are transferred from WIP to a virtual inventory location (like Backflush) in order to downcount the WIP inventory. Backflush is considered virtual because no real inventory exists, and the virtual counts do not have inventory value. This is usually done using the Inventory Transfer Form. If large quantities are involved or if it is not desirable to have a PC on the shop floor, then a Batch barcode PDT can be a very attractive way to transfer the inventory out of WIP.
Sample Batch PDT file
Following is typical output file from a Batch PDT, stored as a text file called BarCodeSampleData1.txt:
Employee Work Order Item Scan Time Scan Date
This sample delimited ASCII text file has been installed in the C:\DbM directory as a result of your installing a version of ERPlite containing the barcode data import demo.
Importing Process Overview
Importing the data is accomplished using the following steps:
Capture data via the barcode scanner(PDT).
Export the data from the PDT to a text file and store it on your PC.
Import the text file into an ERPlite table.
Use Append Queries to copy the data into the Sales Order and Inventory Transactions tables, including additional business rules data required by ERPLite
Visually verify the data, looking for invalid data such as blanks where text or numbers are required
Fix the invalid data.
Check the Inventory counts to verify that WIP has been downcounted accurately. Microsoft Access Importing Tools
Microsoft Access Importing Tools
The "typical" installation of Microsoft Access includes only a few importing tools, and does not include the text importing tools. These text importing tools (along with several others for proprietary databases) can be installed from the Office 97 ValuPack.
Barcode demo
Sample data from a batch barcode scanner is stored in C:\DbM\BarcodeSampleData1.txt. Note, that this file is installed with and ERPlite version that contains the demo code.
Load DbM_fe.mde with Microsoft Access.
Click on: File > Get External Data >Import.
On the Import Screen, select .txt in the Files of Type box.
Navigate to find and select the text file, click Import.(C:\DbM\BarcodeSampleData1.txt).
Since this is a delimited text file, click Next.
Since the data looks Ok, click Next.
Then, Select "In Existing table" = BarCodeSampleData , click Next and then Finish on the next screen.
The existing table BarCodeSampleData has been populated in Access. If any data could not be imported properly, an error table will be generated. Inspect the error table and take appropriate action.
Append Barcode data as an Inventory Transfer Transaction
Print the WIP inventory valuation report so you can check for correct inventory downcounting in the next step.
Open the BarCode Data Import Form (ERPlite Switchboard -> Main Menu -> Manufacturing Orders - > Barcode data import.
Click the button. Note that this button causes queries to be run which append data records to the Sales Order and Inventory Transactions table.
Queries run in series: BarcodeAppendSoTable, BarCodeAppend1InvTransTable, BarCodeAppend2InvTranstable.
These queries include creating the necessary business rule data in the records appended. Inspect the queries to understand the necessary business rules. The Append1 query creates the transaction that is visible in the Inventory Transfer form. Append2 downcounts WIP and upcounts Backflush.
A new tracking number is computed by concatenating "IT" and "WO12587" = ITWO12587, thus preserving the internal work order number captured by the scanner. The IT header is needed by the Inventory Transactions form.
Verify that the WIP downcounts were done accurately by printing the WIP inventory valuation report and comparing the quantities to those in step 7. As well, open the Inventory Transfer Form and navigate to ITWO12587
Of course, the data could have been manually typed into the Inventory Transfer form, but with large quantities of data the Batch Barcode import method is a lot faster, eliminates the need for a PC on the shop floor and simplifies procedures and skill training.