Micros Database help (need a Micros Guru) 3

[azrobert]

I have been working on a program to pull timecard / sales data from the Micros database and pass it to a payroll program. So far I have been looking at the views rather than the tables. I am able to compile the data amd kind of match up shifts. it appears that a time card entry is usually between shift times. is there a table I am missing ? I can't seem to find anything that ties the shifts and timecard entries together (no common field)

what I really need to do is get (or combine and creat my own) table with clock in / out times, sales, tips etc....

I am pulling the information from Micros and then working on it in my own database for the program so I don;t need to change the micros db, just figure out how to tie everything together.


any help will be greatly appreciated.

 

[azrobert]

I'm pulling over a good back up from one of the stores to try all this aganst live data rather than my test set up here. should be about the same, I have a few clock in's on the test set up though..

 

[pmegan]

Good idea. If you're running transactions in demo mode the time clock and shift detail times will be real, but the business date will be 09/08/2015 so you'll never get a match.

 

[azrobert]

Looks great so far with the live data....
I will have to change my program to read this view and see how everything looks.......

I don't know why Micros has such a clunky time keeping set up. the pos system is first rate, we looked at every system out there and even partnered in the development of the system we used for 20 years and none of them even come close to Micros. the timekeeping and editing leave a lot to be desired though. What I would really like to be able to do down the road is write a program to allow me to edit times outside of Micros in an easier format. But I guess writing to the database is something that Micros does not like.

 

[azrobert]

This is getting really close. I noticed that with the live data I am not getting any records for bussers or dishwashers or cooks.... any ideas ?

 

[pmegan]

They probably didn't expect the 3700 to take off like it did, so the infrastructure is kind of weak in some spots. The Labor Management module was initially going to be more robust than it is, so maybe they had planned on putting it all together there. Personally I think Micros hit the breaks so they didn't take the chance of being liable for payroll errors for all the employees in tens of thousands of sites; but that's just a guess.

It's still easier than the 8700 or 9700 before v3 where you had to dump everything to a flat files and use perl scripts to parse through and tie the data from different files together.

As far as editing outside of Micros, they don't let us modify their totals because it would make the system unsupportable. Just create your own tables with the custom database user and copy in the data you need from the Micros tables. I've got my own views, stored procedures and tables running inall of our stores; from creating RVC reporting groups to sales and historical total tables and their respective posting and purging procedures.

Now that v4.0 is on the .Net 1.1 framework you can make some nice GUI's pretty quickly.

 

[azrobert]

THat is a good idea, have my program read sort of a copy table and use that information for payroll processing. and have Micros update it every so often ?

still looking in to why I can't see any times for bussers, or dishwashers or cooks yet. there shifts are incrementing at clock in so there should be distinct shifts relating to timecard just like all the servers etc...


Robert

 

[pmegan]

I hadn't even thought about BOH employees. You can probably get it done by splitting the export into to parts, pushed together by a UNION statement.

Use a query similar to the one we've been working on but without the view. Inner join the time card, shift detail and employee def tables, put the whole mess in parenthesis and left outer join the shift employee totals table. This will pull everything in the time card tables and the left outer join will cause the employees without tips to show up with nulls instead of being filtered out.

Skip the people who are exported in the first part by using this in the where clause
time_card_dtl.job_seq not in (select distinct job_seq from custom.v_R_emp_shift_job_dtl
If that doesn't work you have to explicitly filter the job_seq in each part of the export.

Make sure you have the same number of columns with the same data tyeps in both parts, and put a UNION statement between them. You'll probably want to use isNull(fieldname,0) for the sales and tip fields so your cooks export zeros instead of nulls.

Let me know if that made sense. I'm in vb.net code my predecessor wrote, trying to bubble his error messages that were getting lost up through two layers of threads to the main application, and my speaking skills slow down in vb-land.

 

[azrobert]

Yea, we are definatley missing some timeclock entries with this. i don;t see any entries for the Cooks, Hosts Bussers or Dishwashers and they all have entries in the timeclock table. at first i thought all non tipped employees were being missed but it is picking up manager shifts.

 

[azrobert]

I will play with this..... getting way past what I am used to in sql but I do need to learn, Going to get my Brother to help me with this one a little beyond me at the moment.

again, thanks so much !!!!!!!!

 

[pmegan]

Ha! Your managers are editing/closing checks under their own number instead of having the employee sign in and authorizing privileged functions. We have the same problem.

 

[azrobert]

yep,

That kind of makes sence, would just picking up a check and editing it cause them to appear with the way we are looking at the data now ?

I am pretty sure that the managers are not actually closing any checks and they really shouldn;tbe starting them, once in a while they will start a check and then transfer it to a server.

 

[azrobert]

Got a "what if " question for ya....

what if a busser forgets to clock in on Monday, we then clock him in on Thursday and adjust the date and hours to reflect the right clock in. I am assuming that there is no way that the clock in is going to match up with any shift data....
if that is true I think I will have to pull a weekly tips amt. claimed for the bussers bassed on a date range of shifts and they can add or subtract thier tips to get the right total claimed for the week and not worry about the daily totals.(from a payroll standpoint I just need to be sure that their tips + wages keep them over minimum wage for the week ... I think)



the servers / bartenders will never be a problem since they have to clock in to create checks.

same is true for all other hourly employees. who cares about the sifts for them... there are no sales or tips associated with their pay.


Am I correct on any of this ?

 

[pmegan]

Your should probably pull a weekly tip report anyway as something to use as a cross reference.

If the bussers are declaring tips they have to be clocked in. When they forget to clock in, the procedure should be for them to clock in for the missing shift, declare tips and clock out. Management can adjust the time card to teh correct hours at this point. You'll have a shift start time that's pretty close to the clock in time. I haven't used this in years, but there's a table called time_card_adjust_dtl that you may be able to outer join in for adjustments.

It may be easier to do it the way you were thinking of, whatever works for you. I really don't like support calls so tend to go the extra mile to make my stuff self sufficient.

It just occured to me that since you were originally a GM you may not have had any real training on the database side of Micros. Do you know about the schema & domain reports and how to log into sybase central?

 

[azrobert]

I have been exploring sybase central.....

what kind of training classes does Micros offer to people like me that just need to get in and mess up stuff (just kidding)

Another thought is what is Micros's view on people writing 3 rd party things like this ?

 

[pmegan]

They don't offer very much anymore. There were classes on SQL and SIM scripting way back, but they were done before I started with Micros in 1998. Everybody doing this now is self taught except for the real old-timers and the folks in Maryland.

You might be able to find a large Micros distributor giving classes every once in a while. I haven't checked in a while, but Wade Holt in Colorado and Gene Archuletta in San Francisco used to run classes sometimes. Never made it to one, but those guys really know their stuff.

Micros doesn't really mind 3rd party apps, that's what the custom user was made for. As long as you use your own tables they can support their side of the product. Make sure you keep installation scripts and unload your data to a flat file on a regular basis. If somebody in support decides to rebuild the db or reinstall the software there's a good chance the 3rd party stuff will get blown out.

I'm probably not going to be checking on here for a couple of weeks. If you have problems with the export you can get me through website support at

http://www.brguestrestaurants.com/contact/index.php

Pat