employee stuck in Clock IN Status

[bjsbas]

I'm new to Micros and need help resolving this issue: There is an employee entry who is no longer working in the restaurant stuck in the Employee Clock In Status. The employee was clocked out by the manger in May and still appears on report in August. I've run database validation and rebuilds but these did not correct the problem. Is there a way to delete the entry from the "Employeee Clock IN Status"?

 

[pmegan]

Try using manager procedures to adjust that employee's time card. Rebuilds and validations don't correct data, just structure and constraints.

Unless you're a 24 hour business, you can also add a step to your overnight autosequences to clock out all employees. I use this along with an employee class option to increment shift on clock-in to ensure that sales & tip totals don't combine from one day to the next, even if somebody forgets to clock out.

 

[bjsbas]

Thanks for your reply. As I stated, the Manager procedure was used to adjust the time card in May and again last week. I also updated the employee by removing the termination date and the adjusting the time card and item remains on the Clock In Status.

 

[Netvoid]

Call micros if you are under support...

Or, run DBISQL,

Replace the 9999 with the employee number of the employee you are having trouble with sticking on the clock.

SELECT 
 tm_clk_status 
FROM micros.emp_def eDef 
JOIN micros.emp_status eStatus
ON eDef.emp_seq = eStatus.emp_seq
WHERE
 eDef.obj_num = 9999;

If the result of this is not "O" then ...

may need to use your DBA login. (Replace the 9999 with the employee number )

UPDATE micros.emp_status
SET tm_clk_status = 'O'
WHERE emp_seq = (SELECT emp_seq FROM micros.emp_def WHERE eDef.obj_num = 9999);

If the code is already 'O' chime back in here still other options... This may be a good starting point.

 

[bjsbas]

Thanks Netvoid,
The results of query is tm_clk_ status is equal to 'O'. What's next?

 

[Netvoid]

Pretty much the same thing in the time card detail table,

SELECT * FROM micros.time_card_dtl 
WHERE emp_seq = (SELECT emp_seq FROM micros.emp_def WHERE eDef.obj_num = 9999)
ORDER BY clk_in_date_tm;

Shouldn't be anything here that is sensitive data, if you post it, it would be a lot easier to help. You should be looking for a null clk_out_date_tm or in this table the very last clock out record for this person should have a clk_out_status of 'N', it is probably 'B'...

Anyhow give me some kind of idea what the data looks like for this employee and I should be able to help more.

 

[bjsbas]

Good Morning Netvoid,

Attached is a pdf containing the out from the last query. There was a error so I added "eDef" after "From micros.emp_def eDef". If this is incorrect please advise. Pages 2 thru 7 of the pdf are screenshots of the output from the query. I did not know how to out the query to a file so it took 6 screen shot to show all the fields in the results.

Thanks

 

[pmegan]

Ok, there's something wrong. The nulls in the clock_out_status & clk_out_date_tm fields, and the 2,187.34 regular hours, show that this employee isn't clocked out. Also, the M in the clock_in_status means "manager clock out", indicating that the clock-in time was adjusted in manager procedures; Micros doesn't assign that code to clock-ins automatically, it has to be selected from the manager procedure reason box.

There are a lot of things that are linked together, and a lot of table updates made through POS Operations and backoffice applications, so making status changes through SQL isn't a good idea unless you know all the ramifications and are sure that the data is incorrect, which doesn't happen often. Updating an employee's clock-in status doesn't clock the employee out, it just sets a flag - in this case incorrectly. I just messed around with this on my test system and it did a fairly good job of banging things up by telling the system that the employee was clocked out but not setting a date & time for the clock out. This left the clock out time in manager procedures blank, but it also couldn't be adjusted. The good thing here is that the employee doesn't have any clock-ins since this one so we can still adjust the last shift.

Here's what I'd recommend:

1) run this code in SQL

UPDATE micros.emp_status
SET tm_clk_status = 'I'
WHERE emp_seq = 916;

commit;

2) Go into manager procedures time card adjustments and select this employee. You should get a prompt asking if you want to clock the employee out, answer Yes. Set the clock-out date & time back to some time on 5/8/2014 after 4:00am. Save and close.

3) Run this in SQL

call micros.sp_purgehistory();

 

[pmegan]

I think there's a GUI export option under the Command menu bar item. It looks like the Micros system you're using is pretty old and I haven't worked with that version of SQL in a while, but I think that's where it used to be. If not, you can use the OUTPUT statement.

SELECT * FROM micros.time_card_dtl 
WHERE emp_seq = (SELECT emp_seq FROM micros.emp_def WHERE eDef.obj_num = 9999)
ORDER BY clk_in_date_tm;

OUTPUT TO 'D:\\SomeFolder\\SomeSubFolder\\Filename.csv'
FORMAT ascii;

Change the path and filename to whatever you want, just use double backslashes in the path.
Use extension xls and format excel to export to an excel 2.1 worksheet instead of a comma delimited file.

 

[bjsbas]

Thank you, Pmegan and Netvoid,

Pmegan's recommendation corrected the problem. I'm not sure what threw things out of sink but I will monitor things closely.

Thanks again

 

[Netvoid]

Thanks for finishing him out Pmegan, I got heavily distracted.

Regards,

 

[pmegan]

No worries. Sometimes you need a tag team in this business.