Sunday, June 7, 2015

Manufacturing Detail Labor Reporting

Dynamics GP has multiple mechanisms to record labor and machine transactions to calculate costs during manufacturing production. Time and Machine transactions can be entered through either the Time Card Entry or Data Collection Window. The tables populated by these two windows are different tables altogether.

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).


Data entered in the Data Collection window is stored in the SF010014 - Data Collection Master and SF010115 - Data Collection Line Items tables (most of the relevant data is stored in the SF010115 table). When joined to the UPR00100 - Employee Master and LC010014 - Labor Code Master tables, a complete picture of detailed labor transactions is achieved.  Here is a basic query, which returns all labor related transactions from the SF010115 table.

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
Select
DCT.MANUFACTUREORDER_I
,DCT.RTSEQNUM_I
,CASE DCT.DATAENTRYTYPE_I
      when 1 then 'Direct_Labor'
      when 2 then 'Machine_Cost'
      when 3 then 'Indirect_Labor'
ELSE 'ERROR'
End Entry_Type   
,DCT.EMPLOYID Employee_ID
,RTRIM(RTRIM(EMP.LASTNAME)+', '+RTRIM(EMP.FRSTNAME)+' '+RTRIM(EMP.MIDLNAME)) Employee_Name
,LCD.COST_I ShopRate
,DCT.ELAPSEDTIME_I Elapsed_Time
,DCT.SEQUENCECOST_I Labor_Cost
,DCT.FIXOVERMARK_I Fixed_Overhead
,DCT.Variable_Overhead_Amount Variable_Overhead
,DCT.PIECES_I Pieces
,DCT.REJECTS_I Rejects
,DCT.PIECECOST_I Piece_Cost
,CONVERT(varchar(10),DCT.ACTUALFINISHDATE_I,101) Finish_Date
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.

1 comment: