Work In Process reporting and inquiry tools begin with the selection of the source of the data and provides the ability to sort and filter data by key fields, which makes WIP reporting intuitive from within Dynamics GP.
That being said, it is not uncommon for users to desire very specific report designs to meet users' needs. Additionally, SQL Server Reporting Services, Analysis Cubes and other tools are provided expressly because, not every constituent needs nor has access to Dynamic GP.
Consequently, when generating tools-based reports on WIP labor detail, it is important to know which tool is being used to collect the data, and which tables contain the associated records.
The Data Collection window is a common way to enter labor (direct and indirect) as well as machine costs for work orders. With a simple wedge scanner and some well-placed barcode font, entering data in this window is fast and accurate. This approach is commonly used when there is a central location in the shop, where work order data is collected. The Data Collection window can be accessed using the following navigation:
(Manufacturing > Transactions > WIP > Data Collection).
NOTE: The Labor Code Master table contains the Shop Rate for the Labor Code, which will be used if the Employee does not have a specific value assigned for the Labor Code in Payroll Setup. The query below assumes the shop rate is used.
-- Labor Detail when entered in Data Collection Window
when 1 then 'Direct_Labor'
when 2 then 'Machine_Cost'
when 3 then 'Indirect_Labor'
,RTRIM(RTRIM(EMP.LASTNAME)+', '+RTRIM(EMP.FRSTNAME)+' '+RTRIM(EMP.MIDLNAME)) Employee_Name
from SF010115 DCT
left join LC010014 LCD on DCT.LABORCODE_I = LCD.LABORCODE_I
Left join UPR00100 EMP on DCT.EMPLOYID = EMP.EMPLOYID
Where DCT.DATAENTRYTYPE_I in (1,3)
Alternatively, the Time Card Entry window can be used to enter Detailed Labor data into Dynamics GP. The Time Card Entry window is an employee based entry system, and is more frequently used when each work-center/employee has access to the window, and updates their time data throughout the day. The Time Card Entry window can be accessed using the following navigation:
(Manufacturing > Transactions > WIP > Time Card Entry)
Time Card Entry data is stored in the SF010600 - DC_Labor_Batch_Entry_Hdr and SF010601 - DC_Labor_Batch_Entry_Dtl (Data Collection Labor Batch Entry Header and Detail) tables. These tables contain a wealth of data and therefore do not require links to additional tables.
-- Labor Detail when entered in Time Entry Window
select * from SF010600 TEH
left join SF010601 TED on TEH.DCHDRNUM_I = TED.DCHDRNUM_I
Feel free to reach out to me, should you have questions about Detailed Labor Reporting for WIP in Dynamics GP Manufacturing.