Try Microsoft Edge
A fast and secure browser that's designed for Windows 10
Hi Manufacturing users,
We get a few questions around Data collection and what tables it stores the data in, especially if there is a hiccup and something is stuck that needs to be looked at. I put together a quick document that talks about the 3 options for Data Collection and what main tables the data reside.
There are 3 locations to manually entering Data Collection. These windows are used when you are NOT back flushing Labor on your Routing.
Time Card Entry
Auto Data Collection (ADC)
Time Card Entry and Time Card Batch windows are used together. Once you have populated the time card information in the Time Card Entry window, you use the Time Card Batch window to post the data. This window is used to enter Direct Labor.
Data Collection window can also be used to enter Direct labor, but you can also enter Indirect labor, as well as Machine Costs too.
Auto Data Collection is typically used with integrations that push the labor information into the tables and the system then automatically posts the data when there is a “match”. But if there is a hiccup you can also manually enter the data using this window to finish the process. There will be a start time record for a specific Employee, MO number, Sequence etc. When the system recognizes the ending time record or “match record” for the same, MO number/Sequence, the system will automatically post the document to the main SF010014 and SF010115 tables. The starting record will sit in the SF010501 until that match is found. If there is a hiccup and you need to enter the ending record, you can manually enter the “ending” record and the system will then post the document. NOTE: If the values don’t leave the SF010501 table, check the status column (ADCSTATUS_I). Many times, you will see a 3 status. Some reasons for Status of 3 and for records sitting in the table and not clearing are:
Here's a list of causes of status 3:
The MO is locked. The MO does not have a status of Released (3), Partially Received (7) or Complete (6). The MO is marked as Done. (All routing sequences are complete, or manually marked) The routing sequence is not part of the MO. The data collection type is Direct Labor, but the sequence is marked to backflush labor. The data collection type is Machine, but the sequence is marked to backflush machine. The MO does not exist. (in the MOP Order MSTR table) The employee (for a direct labor data collection) does not exist in the employee master table. The machine (for a machine data collection) does not exist in the machine master table.
Below is some information on what tables get updated during our data collection processes. Each column represents the type of document you entered from Start to finish (top to bottom). As you will notice, they ALL end up in the same main tables (SF010014 and SF010115).
DATA COLLECTION TABLES
SF010600 (Summary) SF010601 (Detail)
Posts to the following tables and clears out of the ones above
SF010500 (Starts here for a second)
SF010501 Immediately after saving.
On record for Start
One record for End
When Match is found it removes from the SF010501 and ends up below
SF010014 (Totals- Summary)
SF010200 when DC Transaction Posts
Note: All 3 options have the same resting place when completed. SF010014 and SF010115
All three options once processed will sit in DC Transactions window waiting for you to post to Payroll (Manufacturing >> Transactions >> WIP >> DC Transactions)
More information on ADC – SDK information (related to all supported versions:
You can import records directly to the SF010500 table if labor is collected by some other process. Here is a chart showing the fields in this table and related field values:
Data Entry directly to table through automated device.
Table Name: ADC_Input_Records (SF010500)
Validations: The validations that must be performed on each field prior to inserting data into the table are listed along with a description of the fields below.
Table Field Descriptions:
Physical (SQL) Field Name
System defaults (if empty)
Data Entry Type ADC
1 for Direct Labor
2 for Machine
1 (Direct Labor)
Manufacturing Order Number
None but required
Sequence of the MO routing
0 if sequence is not done
1 if this sequence is done.
None for a Finish Transaction; 0 for a Start Transaction
Employee ID number
None; required for Direct Labor trans
Machine ID IC
Machine ID number
None; required for Machine trans
0 for Start transaction
1 for Finish transaction
Enter transaction date
Enter transaction time
0 if transaction not labor setup
1 if transaction is labor setup
0 (NOT labor setup)
Enter pieces completed
Enter pieces rejected
ADC Device ID
Enter ADC Device ID
Pay Code Rate 1
Enter Pay Code associated with time being recorded
Enter payroll Labor Code associated with Pay Code
Manufacturing Order must exist in the system. Check this from table MOP_Order_MSTR (physical name WO010032) using key 1 (Manufacture Order).
Manufacturing Order must have status of Released or Partially Received. Check this from table MOP_Order_MSTR (physical name WO010032). Check field 'Manufacture Order Status' of file MOP_Order_MSTR – if its value is not 3 or 7, the MO is not Released or Partially Received.
Manufacturing Order is not done. Check this from table MOP_Routing_Line (physical name WR010130). For the ‘Manufacture Order’, go through all the sequences (field rtseqnum) and check the field done_cb. If all the sequences for the MO are done, then the MO is done.
The sequence must be part of the Manufacturing Order. Check this from table MOP_Routing_Line (physical name WR010130). The sequence must exist in this table for the Manufacturing Order. Use fields ‘Manufacture Order’ and ‘rtseqnum’ to check this.
The sequence is not set to be backflushed. Check this from table MOP_Routing_Line (physical name WR010130). If the field ‘Auto Backflush Labor’ or the field ‘Auto Backflush Machine’ are false, then the sequence is not set to be backflushed.
Employee ID must exist in the system. Check this by using the ‘Employee ID’ field of file UPR_MSTR (physical name UPR00100).
Machine ID must exist in the system. Check this by using the ‘Machine ID’ field of file Machine_MSTR (physical name MM010032).
The above listed validations are the only ones required to be done on the data entered into the table through an automated device. Other fields will be filled by the system—for example, the labor code, pay code, and job number. Also, if the date and time fields are empty, the system will fill these with system date and time.
The Automated Data Collection window can be accessed manually and populated. This window provides a user interface and captures the same information as may be pushed into the SF010500 table as listed above.
Hope this information is helpful when looking at data collection process and table information.
Angela Ebensteiner | Sr. Technical Advisor | Microsoft Dynamics GP