Has anyone built a dashboard or otherwise to display live labor efficiency? (Actual hours over Estimated Hours)
I’m finding it quite difficult to account for instances where an employee might be working on multiple jobs at once and calculating applied hours. This is really only a problem for active transactions, as once the labor transaction is closed Epicor calculates this all by itself. The following would work if users never clocked into multiple jobs, but they do, so this way overstates applied hours.
select
jobhead.jobnum,
labordtl.OprSeq,
sum(
case
WHEN labordtl.ActiveTrans = 1
THEN
datediff(
mi,
timefromparts(floor(clockintime),(clockintime % 1) * 60,0,0,0),
convert(time, sysdatetime())
)
ELSE ClockOutMinute - ClockInMinute
END
) as ttime
from erp.labordtl
inner join erp.jobhead on labordtl.jobnum = jobhead.jobnum
where
jobhead.JobClosed = 0
group by jobhead.jobnum, labordtl.OprSeq
Jeff,
I feel like you are trying to solve the wrong problem here. If a person is punched into multiple jobs they are double billing. Which for a resource is impossible when you get to the point of accounting and costing. Is there a reason that a person might be on multiple jobs?
Because of the way our Epicor was setup 10 years ago, every job in the plant has a quantity of 1. We then use the JobNum as the parts’ serial number. Right now we have about 1600 active jobs in WIP. It’s not uncommon for operators to be working on 10 jobs at a time if they’re in a line.
Hi Jeff, Can you create a subquery with laborDtl add a criteria for active labor, group by employee number, then count the open number of jobs? Pass that number back to your main query and divide the active time by number of active jobs.
select
[LaborDtl].[EmployeeNum] as [LaborDtl_EmployeeNum],
(Count(LaborDtl.JobNum)) as [Calculated_activeJobs]
from Erp.LaborDtl as LaborDtl
where (LaborDtl.ActiveTrans = 1)
group by [LaborDtl].[EmployeeNum]
That’d get close, but if an employee clocked into Job A at 7:00AM and Job B at 8AM it wouldn’t distribute the labor correctly.
I’ve been noodling on this a while, and Epicor does this calculation correctly, bot only after the labordtl has been updated/closed. I think I may have to just settle with only pulling Job Ops that have been completed, and let Epicor do the heavy lifting.
Yikes. In that case, the correct answer is to start reforming your processes. Just because it was set up that way does not mean it can’t be changed, especially since it’s just at the job level which means you simply need to set the new process and declare all jobs created from that day forward will follow it and let the old mess cycle out.
Have you considered using a subquery to sum the employee’s active labor time across all jobs, and then in the main query discount the active time by the ratio of the active time on the particular job to the employees total active time to get the applied labor time?
Epicor labor efficiency calculated when employee end his transaction, to get the qty and/or setup percentage, unless that you have an external live data to link and read these figures when transactions are a live you wont be able to do that at least to this definition of labor efficiency, if you only means comparing between actual time spent on operation VS estimated time then yes, you can do that by adding the difference between the current time and clock in time per op for the live one + all previous operation time for the same Op/Job, then create a calculated field to highlight this live operation when this calculated time exceeded the estimated time
with regard to this issue, you can create a BPM to stop employees to log on more than one transaction at one time, then your calculation will be right, if your work environment allowed such behavior then either put the assumption of splitting the time evenly between the employee active operations or create a BPM to prompt a form when this happen to ask this employee to enter this split labor percentage between the active transactions to be able to use it in your calculation.
We are very similar to you in our use of Epicor. We make buildings, so it makes sense for us to have a quantity of one. Each building has a different part design. We also, at times, clock to more than one job at a time, in our case, when you are mixing concrete for 10 jobs, it’s hard to clock into one and then the others. We use Kronos for labor. Kronos allows you to divide your time among many jobs by standard hours, so it works out. We don’t use dashboards for efficiencies, but create reports.
