Hi,
My client is running the production floor 24 hours a day, seven days a week.
Unless I’m missing something, the laborhed and labordtl records only have a clock in date and no clock out date which makes it impossible to determine how long someone has been logged into the company if they have been on site across midnight.
Has anyone found a way around this?
Thanks,
Andrew.
It is not something you need. If someone starts at 20:00 and clocks out at 4:00 that is your indicator that it went into the next day. If you ever tried to develop a Payroll System, dealing with dates and timezones is a nightmare. So what Epicor does behind the scenes it uses the ClockInDate and ClockInTime as a starting point and then when the ClockOutTime happens it calculates the PayHours.
The same goes with LunchOut and LunchIn, there is no “date” a User could Start at 23:00 and go to lunch at 1:00.
You can solve that easily in a Dashboard / BAQ or even add an additional UI Field and calculate the date.
DATEADD(second, Erp.LaborHed.LunchOutTime * 3600, CAST(Erp.LaborHed.ClockInDate as DateTime)) AS LunchOutDateTime,
IIF(Erp.LaborHed.ClockInTime > Erp.LaborHed.ClockOutTime, Erp.LaborHed.ClockOutTime + 24, Erp.LaborHed.ClockOutTime) AS ClockOutTime,
DATEADD(second, IIF(Erp.LaborHed.ClockInTime > Erp.LaborHed.ClockOutTime, Erp.LaborHed.ClockOutTime + 24, Erp.LaborHed.ClockOutTime) * 3600, CAST(Erp.LaborHed.ClockInDate as DateTime)) AS ClockOutDateTime
Some Notes I wrote back in the days:
Since I add + 24 to Epicors ##.## if the ClockInTime is less than ClockOutTime (indicator that it reset to 0). I get a result such as “ClockOutTime 26.18” then I can times that by seconds and convert to an actual DateTime. That should cover up to a 23.9hr shift (which is not something normal people do).
Thanks for the detailed respone and SQL.
