Micros 3700 - Auto clock out employee after 8 hours 1

[Serenitee]

Hi,

I am trying to come up with a way to have our 3700 automatically clock out certain employees (salaried employees) 8 hours after they clock in. If I can get it working it will allow us to use a budgeting component of our above store reporting system (which would make my boss very happy). I've bumped my head against it a few times and feel like I am missing something obvious.

Any advice/guidance would be most appreciated.

 

[Moregelen]

Instead of doing that, you can actually have the system record salary hours, and you can set the money they are making. Under time and attendance you can set salary to post to labor, and then under their salary jobs you can assign what their hourly, or weekly, or monthly, etc wages are. Seems a much more reliable approach.

 

[Serenitee]

Thanks Moregelen,

Unfortunately our above store reporting system can not pull from Micros' Time and Attendance but it does grab clock ins/outs, which is why we are hoping to implement the 8hr auto clock out for our salaried employees. I'm sure there must be a way to do it.. I had thought maybe I could devise an autosequence to clock them out after 8 hrs, but haven't had any luck.

 

[Moregelen]

Can your above store reporting not just manually calculate in the hours?

There is a stored procedure that does a clock out and clock in of employees, so you could use the SQL contained within that to make some kind of application/batch file that processes it for you, and then run it via task scheduler.

[COLOR=#0000FF]ALTER[/color] [COLOR=#0000FF]PROCEDURE[/color] "micros"[COLOR=#808080].[/color]"sp_ClockOutInEmployee"[COLOR=#808080](IN[/color] @employee_num OBJ_NUM[COLOR=#808080])[/color]
[COLOR=#0000FF]BEGIN[/color]
  [COLOR=#0000FF]DECLARE[/color] @emp_seq              SEQ_NUM[COLOR=#808080];[/color]
  [COLOR=#0000FF]DECLARE[/color] @job_seq              SEQ_NUM[COLOR=#808080];[/color]
  [COLOR=#0000FF]DECLARE[/color] @tm_clk_status        TIME_CLOCK_STATUS[COLOR=#808080];[/color]
  [COLOR=#0000FF]DECLARE[/color] @tm_card_num          [COLOR=#0000FF]SMALLINT[/color][COLOR=#808080];[/color]
  [COLOR=#0000FF]DECLARE[/color] @tm_card_pg_num       [COLOR=#0000FF]SMALLINT[/color][COLOR=#808080];[/color]
  [COLOR=#0000FF]DECLARE[/color] @tm_card_line_find_line_num [COLOR=#0000FF]SMALLINT[/color][COLOR=#808080];[/color]
  [COLOR=#0000FF]DECLARE[/color] @num_clk_in_outs      [COLOR=#0000FF]SMALLINT[/color][COLOR=#808080];[/color]
  [COLOR=#0000FF]DECLARE[/color] @tm_card_seq          SEQ_NUM[COLOR=#808080];[/color]
  [COLOR=#0000FF]DECLARE[/color] @tm_clk_sched_seq     SEQ_NUM[COLOR=#808080];[/color]
  [COLOR=#0000FF]DECLARE[/color] @rvc_seq              SEQ_NUM[COLOR=#808080];[/color]
  [COLOR=#0000FF]DECLARE[/color] @clk_in_status        [COLOR=#0000FF]CHAR[/color][COLOR=#808080]([/color]1[COLOR=#808080]);[/color]
  [COLOR=#0000FF]DECLARE[/color] @NOW                  [COLOR=#0000FF]TIMESTAMP[/color][COLOR=#808080];[/color]
  [COLOR=#0000FF]DECLARE[/color] @NOWINUCT             [COLOR=#0000FF]TIMESTAMP[/color][COLOR=#808080];[/color]
  [COLOR=#0000FF]DECLARE[/color] @original_nowinuct    [COLOR=#0000FF]TIMESTAMP[/color][COLOR=#808080];[/color]
 
  [COLOR=#808080]//[/color][COLOR=#008000]---------------------------------------------------------------------------------[/color]
  
   [COLOR=#808080]//[/color][COLOR=#0000FF]get[/color] info [COLOR=#0000FF]from[/color] emp_status  
   [COLOR=#0000FF]SELECT[/color] stat[COLOR=#808080].[/color]emp_seq[COLOR=#808080],[/color]
          stat[COLOR=#808080].[/color]tm_clk_status[COLOR=#808080],[/color]
          stat[COLOR=#808080].[/color]tm_card_line_find_line_num[COLOR=#808080],[/color]
          stat[COLOR=#808080].[/color]num_clk_in_outs
     [COLOR=#0000FF]INTO[/color] @emp_seq[COLOR=#808080],[/color]
          @tm_clk_status[COLOR=#808080],[/color]
          @tm_card_line_find_line_num[COLOR=#808080],[/color]
          @num_clk_in_outs
     [COLOR=#0000FF]FROM[/color] micros[COLOR=#808080].[/color]emp_status stat[COLOR=#808080],[/color]
          micros[COLOR=#808080].[/color]emp_def def
    [COLOR=#0000FF]WHERE[/color] def[COLOR=#808080].[/color]obj_num [COLOR=#808080]=[/color] @employee_num
      [COLOR=#808080]AND[/color] def[COLOR=#808080].[/color]emp_seq [COLOR=#808080]=[/color] stat[COLOR=#808080].[/color]emp_seq[COLOR=#808080];[/color]
      
   [COLOR=#808080]//[/color]SCR 21658 Do [COLOR=#808080]not[/color] clock [COLOR=#0000FF]out[/color] [COLOR=#808080]/[/color] [COLOR=#808080]in[/color] emps [COLOR=#0000FF]on[/color] [COLOR=#0000FF]break[/color]   
   [COLOR=#0000FF]IF[/color] @tm_clk_status [COLOR=#808080]IN[/color][COLOR=#0000FF] [/color][COLOR=#808080]([/color][COLOR=#FF0000]'P'[/color][COLOR=#808080],[/color][COLOR=#FF0000]'B'[/color][COLOR=#808080],[/color][COLOR=#FF0000]'O'[/color][COLOR=#808080])[/color] [COLOR=#0000FF]THEN[/color]
       
       [COLOR=#0000FF]MESSAGE[/color] [COLOR=#FF0000]'Employee '[/color] [COLOR=#808080]||[/color] @employee_num [COLOR=#808080]||[/color] [COLOR=#FF0000]' is already clocked out!'[/color][COLOR=#808080];[/color]
       [COLOR=#0000FF]RETURN[/color][COLOR=#808080];[/color]
       
   [COLOR=#0000FF]END[/color] [COLOR=#0000FF]IF[/color][COLOR=#808080];[/color]
  
   SAVEPOINT s_Savepoint[COLOR=#808080];[/color]
 
   [COLOR=#808080]//[/color]Clock [COLOR=#0000FF]out[/color] employee
   [COLOR=#0000FF]CALL[/color] micros[COLOR=#808080].[/color]sp_ClockOutEmployee[COLOR=#808080]([/color]@employee_num[COLOR=#808080]);[/color]  
  
   [COLOR=#808080]//[/color]Clock back [COLOR=#808080]IN[/color]
   [COLOR=#808080]//[/color][COLOR=#0000FF]get[/color] employee [COLOR=#0000FF]time[/color] [COLOR=#0000FF]card[/color] info
   [COLOR=#0000FF]SELECT[/color] t1[COLOR=#808080].[/color]tm_card_seq[COLOR=#808080],[/color]
          t1[COLOR=#808080].[/color]rvc_seq[COLOR=#808080],[/color]
          t1[COLOR=#808080].[/color]job_seq[COLOR=#808080],[/color]
          t1[COLOR=#808080].[/color]tm_clk_sched_seq
     [COLOR=#0000FF]INTO[/color] @tm_card_seq[COLOR=#808080],[/color]
          @rvc_seq[COLOR=#808080],[/color]
          @job_seq[COLOR=#808080],[/color]
          @tm_clk_sched_seq
     [COLOR=#0000FF]FROM[/color] micros[COLOR=#808080].[/color]time_card_dtl t1
    [COLOR=#0000FF]WHERE[/color] t1[COLOR=#808080].[/color]emp_seq [COLOR=#808080]=[/color] @emp_seq
      [COLOR=#808080]AND[/color] t1[COLOR=#808080].[/color]tm_card_seq [COLOR=#808080]=[/color][COLOR=#0000FF] [/color][COLOR=#808080]([/color][COLOR=#0000FF]SELECT[/color] [COLOR=#FF00FF]MAX[/color][COLOR=#808080]([/color]t2[COLOR=#808080].[/color]tm_card_seq[COLOR=#808080])[/color]
                              [COLOR=#0000FF]FROM[/color] micros[COLOR=#808080].[/color]time_card_dtl t2
                             [COLOR=#0000FF]WHERE[/color] t1[COLOR=#808080].[/color]emp_seq [COLOR=#808080]=[/color] t2[COLOR=#808080].[/color]emp_seq[COLOR=#808080]);[/color]
 
   [COLOR=#0000FF]SET[/color] @NOWINUCT [COLOR=#808080]=[/color] NowInUCT[COLOR=#808080]();[/color]
   [COLOR=#0000FF]SET[/color] @NOWINUCT [COLOR=#808080]=[/color] seconds[COLOR=#808080]([/color][COLOR=#0000FF]date[/color][COLOR=#808080]([/color]NowInUCT[COLOR=#808080]()),(([/color][COLOR=#FF00FF]hour[/color][COLOR=#808080]([/color]NowInUCT[COLOR=#808080]())*[/color]3600[COLOR=#808080]+[/color][COLOR=#FF00FF]minute[/color][COLOR=#808080]([/color]NowInUCT[COLOR=#808080]())*[/color]60[COLOR=#808080]+[/color][COLOR=#FF00FF]second[/color][COLOR=#808080]([/color]NowInUCT[COLOR=#808080]()))/[/color]36[COLOR=#808080])*[/color]36[COLOR=#808080]);[/color]
   [COLOR=#0000FF]SET[/color] @NOW [COLOR=#808080]=[/color] UCTtoLocal[COLOR=#808080]([/color]@NOWINUCT[COLOR=#808080]);[/color]
  
   [COLOR=#0000FF]INSERT[/color] [COLOR=#0000FF]INTO[/color] micros[COLOR=#808080].[/color]time_card_dtl[COLOR=#0000FF] [/color][COLOR=#808080]([/color]
          emp_seq[COLOR=#808080],[/color]
          tm_card_seq[COLOR=#808080],[/color]
          rvc_seq[COLOR=#808080],[/color]
          tm_clk_sched_seq[COLOR=#808080],[/color]
          job_seq[COLOR=#808080],[/color]
          clk_in_date_tm[COLOR=#808080],[/color]
          clk_in_gmt_date_tm[COLOR=#808080],[/color]
          clk_in_status[COLOR=#808080])[/color]
     [COLOR=#0000FF]VALUES [/color][COLOR=#808080]([/color]
          @emp_seq[COLOR=#808080],[/color]
          @tm_card_seq [COLOR=#808080]+[/color] 1[COLOR=#808080],[/color]
          @rvc_seq[COLOR=#808080],[/color]
          @tm_clk_sched_seq[COLOR=#808080],[/color]
          @job_seq[COLOR=#808080],[/color]
          @NOW[COLOR=#808080],[/color]
          @NOWINUCT[COLOR=#808080],[/color]
          [COLOR=#FF0000]'M'[/color][COLOR=#808080]);[/color]
 
   [COLOR=#808080]//[/color]calc [COLOR=#0000FF]next[/color] nums [COLOR=#0000FF]for[/color] emp_status [COLOR=#0000FF]table[/color]
   [COLOR=#0000FF]IF[/color] @tm_card_line_find_line_num [COLOR=#808080]<[/color] 98 [COLOR=#0000FF]THEN[/color]
     [COLOR=#0000FF]SET[/color] @tm_card_line_find_line_num [COLOR=#808080]=[/color] @tm_card_line_find_line_num [COLOR=#808080]+[/color] 2[COLOR=#808080];[/color]
   [COLOR=#0000FF]END[/color] [COLOR=#0000FF]IF[/color][COLOR=#808080];[/color]
 
   [COLOR=#0000FF]IF[/color] @num_clk_in_outs [COLOR=#808080]<[/color] 999 [COLOR=#0000FF]THEN[/color]
     [COLOR=#0000FF]SET[/color] @num_clk_in_outs [COLOR=#808080]=[/color] @num_clk_in_outs [COLOR=#808080]+[/color] 1[COLOR=#808080];[/color]
   [COLOR=#0000FF]END[/color] [COLOR=#0000FF]IF[/color][COLOR=#808080];[/color]
   
   [COLOR=#808080]//[/color][COLOR=#FF00FF]Update[/color] the employee [COLOR=#0000FF]status[/color] [COLOR=#0000FF]table[/color][COLOR=#808080].[/color]
   [COLOR=#FF00FF]UPDATE[/color] micros[COLOR=#808080].[/color]emp_status
      [COLOR=#0000FF]SET[/color] tm_card_line_find_line_num [COLOR=#808080]=[/color] @tm_card_line_find_line_num[COLOR=#808080],[/color]
          num_clk_in_outs [COLOR=#808080]=[/color] @num_clk_in_outs[COLOR=#808080],[/color]
          tm_clk_status [COLOR=#808080]=[/color] [COLOR=#FF0000]'I'[/color]
    [COLOR=#0000FF]WHERE[/color] emp_seq [COLOR=#808080]=[/color] @emp_seq[COLOR=#808080];[/color]
 
[COLOR=#808080]//[/color][COLOR=#008000]---------------------------------------------------------------------------------[/color]
 
 
 EXCEPTION
   [COLOR=#0000FF]WHEN[/color] OTHERS [COLOR=#0000FF]THEN[/color]
     [COLOR=#0000FF]ROLLBACK[/color] [COLOR=#0000FF]TO[/color] SAVEPOINT s_Savepoint[COLOR=#808080];[/color]
     RESIGNAL[COLOR=#808080];[/color]
[COLOR=#0000FF]END[/color]

 

[Serenitee]

We really just need to get the dates that the employee works up to the above store, the 8hr auto log out just keeps our payroll system (separate from above store) from incorrectly trying to give salaried employees over-time pay. Long story short there are a bunch of moving parts and it is across 9 locations, so lots of balls to juggle.

Thanks so much for posting the procedure! I hadn't even thought of repeatedly running something (like an scheduled bat) to check status/clock in time through-out the day and then clocking someone out if need be. I had been fixated on trying to start a 'count' when employee clocked in. I probably wont get much time to dig into this today (already thinking I might need to query for employee class or maybe job too so I don't clock out hourly folks), but I'm very grateful for pointing me in a good direction. Cheers!

 

[Moregelen]

The only warning I'd give is that if you're requiring that salaried employees clock in and out, if they are working more than eight hours and you clock them out, they will not be able to use the system until you clock them back in.

I don't know how familiar you are with C#, but something like this set to run on a schedule could potentially work:

[COLOR=#0000FF]using[/color] ([COLOR=#2B91AF]MicrosDatabase[/color] md = [COLOR=#2B91AF]MicrosDatabase[/color].OdbcDbConnection())
{
    md.Connect();
 
    [COLOR=#0000FF]string[/color] query = [COLOR=#800000]@"select obj_num from micros.emp_def where emp_seq in ([/color]
[COLOR=#800000]                        select emp_seq from micros.time_card_dtl where clk_out_date_tm is null and job_seq in ([/color]
[COLOR=#800000]                            select job_seq from micros.job_def where job_type = 'S'[/color]
[COLOR=#800000]                        ) and datediff(hh, clk_in_date_tm, now()) >= 8[/color]
[COLOR=#800000]                     )"[/color];
    [COLOR=#2B91AF]DataTable[/color] toClockout = md.Query(query, 30);
    [COLOR=#0000FF]if[/color] (toClockout == [COLOR=#0000FF]null[/color])
        [COLOR=#0000FF]return[/color];
    [COLOR=#0000FF]foreach[/color] ([COLOR=#2B91AF]DataRow[/color] emp [COLOR=#0000FF]in[/color] toClockout.Rows)
    {
        md.Execute([COLOR=#0000FF]string[/color].Format([COLOR=#A31515]"CALL micros.sp_ClockOutEmployee({0});"[/color], emp[[COLOR=#A31515]"obj_num"[/color]]), 30);
    }
}

 

[Jackba]

This sounds interesting its seems you can do more with micros with a little programming knowledge

 

[PosGuyUS]

I would be concerned with salaried employees that are eligible for OT. After 12/1, if a salaried employee is making less than about $47k they are eligible for OT. If you are making up punch outs for them, then you may be breaking the law.

 

[Moregelen]

Yes, I will caution that in a lot of states (mine for example) its actually illegal to automatically clock out employees. I'm no lawyer, so use your (or your adviser/lawyer's) judgement.

 

[Moregelen]

Also, to respond.

@Jackba - Yes, the reason, honestly, that Micros gained as much ground as they did is that it is actually very easy to produce 'extensions' for. Between using an SQL database, ISL, support for importing of DLL's in ISL, the POS Transaction API (which operates over COM or SOAP), and PMS, there are very few things you can find a legit need for that you wouldn't be able to do.

For example, one of my projects was integrating wine dispensers into Micros, so that you could activate an RFID for use in the machine, and then pull the transactions back from those machines and insert them into the check the card is associated with. We went with a SIM paired with a PMS interface which then pushes the transactions to the SIM, which actually rings them in, but we also could have used the transaction API to directly insert the information into the checks.

Another project was the need to 'checkout' tablets to our employees for accountability. With a simple sub fifty line SIM we setup a system where only the employee who 'owns' the tablet can actually use it, and it has to be checked out by a manager. Once checked out, the responsibility for the tablet is on that employee until they check it back in. Makes it a lot easier to keep track of who lost/broke the device when we can point at a report that says they were the only one who could possibly have been using it.

 

[Serenitee]

Really appreciate the feedback and I will be (finally) able to sit down with micros server and start using the suggestions. Luckily we have a closed location which has a 3700 I can use to test. As far as I can tell, what the above store labor forecasting is really 'looking for' is the employee job (with rate/hourly equivalent of his/her salary) and the hours that he/she has worked in the past (what their 'average' hours are). So by having salaried employees showing up (with hourly rate) as normally clocking in for 8 hours it will allow us to get close to where my bosses want to be.

We don't/won't use micros to track overtime/payroll for salaried employees so we should have no troubles on the legal side (but thanks for looking out).

Thanks again for the responses.

 

[Serenitee]

I suspect the answer to this will be.. "I really wouldn't write to that table", but...

Couldn't I create a "Clock in Salaried" button which would clock in/out an employee at the same time by recording "clk_out_date_tm" and "clk_out_gmt_date_tm" in time_card_dtl as NOW (clock in time) + 8hrs? This way I could avoid repeatedly running a scheduled task all day.

Note: I understand that a clocked out employee can't actually use the system, supposedly these BoH employees will only need to use system for this clockin/out procedure and nothing else (I'm going to verify that before moving forward).

Thank you.

 

[Moregelen]

You could, but that isn't likely to work correctly. It might, but I can't say for sure. Technically, that is about all Payroll Preprocessing is doing, so you might be able to just insert a record. Micros typically works by doing running totals though, meaning it posts data every X. So you might not get everything adding up correctly. I don't know because I've never tried it. There are A LOT of moving parts in the Micros database, and simply inserting data doesn't typically work as intended. Give it a try and see what happens?

 

[Serenitee]

Unfortunately I don't seem to be able to get write level access to *time card detail* table (stuck using u:custom p:custom), no matter what hoops I jump through. So I guess it is back to the stored procedure route, too bad I was really interested to see if it would work (first time I've had access to a test/development Micros box).

 

[Moregelen]

The check detail table..? You shouldn't need to write to that table for time cards. Its time_card_dtl.

Do you know the DBA password for your database? Custom, Custom is a limited user.

It is also definitely not advisable to be writing to that table (chk_dtl). It can cause major issues. The only thing we really ever write to that table for is to force close a corrupted check or to forcibly transfer a check to another cashier when a manager terminates an employee with open checks (I have no idea how that happened despite the fact that it required me to swipe my card to override the warning! -Manager).

 

[Serenitee]

Sorry I was just discussing how our above store grabs check detail with coworker before I wrote that email (also coffee hasn't fully kicked in yet). I did mean time card detail, I am staying as far away from that chk_dtl as possible. I'll edit the post.

I tried pwd of 'micros3700' for dba but no luck. Also, tried to find the 'tool' micros uses to recover dba passwords but no luck there either. We have a couple systems which are upgrades that might allow dba access with the old password, but that still leaves me stuck on newer systems (like my test box).

 

[Moregelen]

Was your system installed by a dealer, or Micros?

 

[pmegan]

I know this is a little different than what's been going on, but might work for the goal:

We really just need to get the dates that the employee works up to the above store, the 8hr auto log out just keeps our payroll system (separate from above store) from incorrectly trying to give salaried employees over-time pay.

Can't you just use the job code options?

Set up an overtime level for salaried employees that never hits overtime.
Or set up a "salaried" job category for salaried employees and link it to the job codes.

When you export the time card data you use something like this:

select
   [i]<all other fields>[/i]
   ,(case
       when (reg_hrs + ovt_hrs) > 8 and job_seq in ([i]<list salaried job seqs here>[/i]) then 8 
       else reg_hrs
    end) [reg_hrs]
   ,(case
       when job_seq in ([i]<list salaried job seqs here>[/i]) then 0 
       else ovt_hrs
    end) [ovt_hrs]
from
   micros.time_card_dtl

You could use something similar in the crystal report templates as well.

 

[Serenitee]

Systems were installed by Micros.

 

[Serenitee]

Unfortunately PMegan the queries our above store (CTuit) use are out of our hands (although they are willing to do development work for $).