Micros Schedule DB issue with Hot Schedules

[dwittrup]

We have Micros 3700 ver 4.7 in 6 restaurants. We use Hot Schedules to do employee communication and scheduling. Hot Schedules will import the employee schedules into our Micros DB. This allows time and attendance to work. Here is my problem: The schedule data never goes away. This becomes an issue when an employee is termed and we would like to reuse the number. I am looking for a script that would purge the schedule data older than 12 months. I am not sure which table needs to be purged.

Can anybody help?

Thanks.

 

[pmegan]

I never reuse employee lines, Micros just leaves too much lingering nonsense behind. The procedure for terminating employees in our restaurants is to remove the login ID, check the Account Disabled box and add 1,000,000 to the employee number to push terms to the bottom of the list. I can then remote in every few months to do some cleanup work.

Micros doesn't link employee records on the number you see in the POS Configurator, there's a behind-the-scenes sequence number used for that. So if you terminate emp 999, renumber it to 1,000,099 and then create a new employee 999 your old schedules will stay linked to 1,000,099.

 

[dwittrup]

Thanks for your help. How do you do the cleanup work? Wouldn't you still need to remove the schedule?

 

[Netvoid]

In POS Configurator | Time & Attendance | Number of days to store schedules ... That is one thing.

If you want to purge them quicker for termed employees.. It's a pretty simple procedure, if you would like me to post a sample please let me know.

 

[dwittrup]

If you could post a sample script for me. That would be great.

 

[Netvoid]

Sorry for delayed response ...

This is just a sample but it does work, it will delete all schedule / break schedule records for employees that are terminated.

The first couple lines will add an entry to Micros so that you can use this stored procedure as an autosequence "stored proc" step... The remainder is the procedure itself.

You may have conditions that make this not exactly the fit you need but no matter it's a good starting point.

INSERT INTO micros.stored_proc_def(obj_num,name,proc_id,ob_user_visible,product_mask)
VALUES (51000,'Purge Term Emp Schedules','custom.sp_PurgeTermEmpSched','T','3700')

CREATE PROCEDURE custom.sp_PurgeTermEmpSched()
BEGIN

DELETE FROM micros.time_clock_sched_brk_def
WHERE emp_seq IN (SELECT emp_seq FROM micros.emp_def WHERE (termination_date < getDate()));

DELETE FROM micros.time_clock_sched_def
WHERE emp_seq IN (SELECT emp_seq FROM micros.emp_def WHERE (termination_date < getDate()))

END

 

[dwittrup]

Thanks

 

[Netvoid]

Almost forgot ...

You need to grant permissions for this to be executable from Micros as an autosequence step.... As I recall the last one is the most important to Micros these days but no problem to do this to all of the groups.

grant execute on custom.sp_PurgeTermEmpSched to CAEDC_GROUP;
grant execute on custom.sp_PurgeTermEmpSched to DEFINITION_GROUP;
grant execute on custom.sp_PurgeTermEmpSched to ESD_GROUP;
grant execute on custom.sp_PurgeTermEmpSched to REPORTS_GROUP;
grant execute on custom.sp_PurgeTermEmpSched to TRANSACTION_GROUP;
grant execute on custom.sp_PurgeTermEmpSched to MICROS_GROUP

Good luck