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]

Just a side note as to why I don't like the way I am currently matching time card entries to a shift with times from the views.

if an employee (busser) forgets to clock in and we clock them in and out at a later date and adjust the times and date to match the day they forgot to clock in, I am pretty sure that my method of matching records will miss the shift data for that clock in (miss the tips declared) becasue the shift that matches that time card entry will be on a different date.

 

[jdirt2]

Hi. We are looking to do a similar application, however we have run into a single problem. When we do an ODBC connection to the micros.db it prompts for a password. This is not an application level password, but rather a database password. Does anyone know what the default ODBC username/password is? Any help would be greatly appreciated.

Thanks.

Rick

 

[azrobert]

I have been using custom as a user and password, I believe that support will also work. Neither password will allow you to alter data in the tables but I have given up on being able to edit data and am happy with reading it for now.

What I really need to know is how to tie the shiftstable / view and the time card table / view together.


Robert

 

[pmegan]

Pulling together shift, business date and time card data is one of the major pains about custom work in the 3700. Another is that the canned views return object numbers instead of sequence numbers, making it impossible to join them together.

I put together a view to tie shift and job data together and use it in almost all the labor related reports and exports that I write. It's a good cross reference link between the shift and business date tables. I modified it a bit here to add in the time card detail table. It isn't tested but should at least be close.

create view "custom"."v_R_timecard_shift_dtl"(business_date,
  emp_seq,
  shift_seq,
  time_card_seq,
  clock_in_datetime,
  clock_out_datetime,
  labor_date,
  labor_week,
  job_seq,
  job_name,
  job_cat)
  as select distinct t.business_date,
    t.trans_emp_seq,
    t.trans_emp_shift_seq,
    c.tm_card_seq,
    c.clk_in_date_tm,
    c.clk_out_date_tm,
    c.labor_date,
    c.labor_week,
    t.job_seq,
    j.name,
    j.job_cat_seq from
    micros.trans_dtl as t join
    micros.job_def as j on
       t.job_seq = j.job_seq join
    micros.time_card_dtl c on
       t.trans_emp_seq = c.emp_seq and
       t.job_seq = c.job_seq and
       t.business_date = c.labor_date

The downside to this is that it pulls from the trans_dtl table which purges after 14 days. If you have to hold info longer than that, just create a table with the same columns as the new view, post into it every night using the view and use it in your export. Just make sure you remember to purge it on a regular basis.

There are a few pitfalls with this.
1) If you have employees working multiple shifts in one day you may have to join in the shift_emp_dtl table and put in a time range filter between the clock in time and shift start times.

2) If the employees are working in multiple RVC's per shift you'll have to do the above for both the shift start and end times. RVC assignment is done at clock-in, so if an employee switches RVC's they're clocked out & in behind the scenes creating multiple clock in/out entries in a single shift.

Hope that helps

Pat

 

[azrobert]

Pat,

thanks for your help... I am very new to views and until now have been pulling everything from the database with straight querys. when I use your code, I execute the script and it creates the view but it appears to be empty when i view data. Am I missing part of it ?

this is what I have:


alter view custom.v_R_timecard_shift_dtl(business_date,
emp_seq,
shift_seq,
time_card_seq,
clock_in_datetime,
clock_out_datetime,
labor_date,
labor_week,
job_seq,
job_name,
job_cat)
as select distinct t.business_date,
t.trans_emp_seq,
t.trans_emp_shift_seq,
c.tm_card_seq,
c.clk_in_date_tm,
c.clk_out_date_tm,
c.labor_date,
c.labor_week,
t.job_seq,
j.name,
j.job_cat_seq from
micros.trans_dtl as t join
micros.job_def as j on
t.job_seq = j.job_seq join
micros.time_card_dtl as c on
t.trans_emp_seq = c.emp_seq and
t.job_seq = c.job_seq and
t.business_date = c.labor_date


when I view data on this I see no data at all, just the column headers.

I apologize in advance for my ignorance of views......


I also don't seem to see any of the sales, tips data that I am looking to see (might be just a issue of adding it in to the view ?)

 

[pmegan]

Sorry it took a while to get back to you. We're opening a huge 400+ seat, 22 workstation restaurant in a couple of weeks and I just got the final device layouts.

The problem is most likely with the link to the time card table. I didn't test it so there may be something wrong in there.

Try this one out just to make sure you have the basic data we're looking for. This is the view I install in all my databases so I know it works.

CREATE VIEW custom.v_R_emp_shift_job_dtl(business_date,
  emp_seq,
  shift_seq,
  job_seq,
  job_name,
  job_cat)
  as select distinct t.business_date,
    t.trans_emp_seq,
    t.trans_emp_shift_seq,
    t.job_seq,
    j.name,
    j.job_cat_seq from
    micros.trans_dtl as t join
    micros.job_def as j on
    t.job_seq = j.job_seq

Now that I look at it we may be better off joining in the shift detail table. (This is a note for myself. I'm a bit frazzled at the moment and will absolutely forget if it isn't written down).

Run the script above to create the view and then run this command:

   select * from custom.v_R_emp_shift_job_dtl

You should get a screen full of data that doesn't really show anything. This is just a cross reference.
If you got some results run this command:

   select e.obj_num, e.last_name, e.first_name, v.*, s.*
   from 
      custom.v_R_emp_shift_job_dtl as v join
      micros.shift_emp_ttl as s on
         v.emp_seq = s.emp_seq and
         v.shift_seq = s.shift_seq join
      micros.emp_def e on 
         s.emp_seq = e.emp_seq

This should give you the employee's number and name, and a line for each shift over the last 14 days with the business date, shift #, job they worked, and a bunch of sales & tip data. Let me know this goes. If you get this stuff out of the database it'll just be a matter of joining in the time card table.

Pat

 

[azrobert]

Pmegan,


Thank you so much for taking the time to help with this...
we just switched 6 full service 250+ seat restaurants over to Micros from a 20 year old pos system and are opening a new restaurant this month. I know the feeling of frazzled !!!!!!!! I am doing this to pass the payroll information to a custom payroll processing system and we need a step in the middle at corp office to edit times so the adp export won't work for me. I got tasked with writing the interface to pull the times, sales etc. data and send it to Corp. , create the edit and export routines to pass to payroll program and then the check writing program......



I will give this a try right now !!!!!!!!!


Talk soon........

Your help is invaluable....

 

[azrobert]

That appears to be getting really close to what I am looking for !!!!!!!!!!

what do I need to do to pull clock in/out times with that ?


Robert

 

[pmegan]

This is where it may get tricky. I'm always leary about joining tables on datetime and string fields. The best place to start is by adding the shift_emp_dtl to the view.

ALTER VIEW custom.v_R_emp_shift_job_dtl(business_date,
  emp_seq,
  shift_seq,
  shift_start_time,
  job_seq,
  job_name,
  job_cat)
  as select distinct t.business_date,
    [COLOR=green]t.trans_emp_seq,
    t.trans_emp_shift_seq[/color],
    d.shift_start_time,
    t.job_seq,
    j.name,
    j.job_cat_seq from
    micros.trans_dtl as t join
    micros.shift_emp_dtl d on
       [COLOR=green]t.trans_emp_seq[/color], = t.emp_seq and
       [COLOR=green]t.trans_emp_seq[/color], = d.shift_seq join
    micros.job_def as j on
       t.job_seq = j.job_seq

Run the last query we tried and make sure you still get valid results. If it looks good, add the time card detail to it.

   select distinct e.obj_num, e.last_name, e.first_name, t*, v.*, s.*
   from 
      custom.v_R_emp_shift_job_dtl as v join
      micros.shift_emp_ttl as s on
         v.emp_seq = s.emp_seq and
         v.shift_seq = s.shift_seq join
      micros.time_card_dtl t on
         t.emp_seq = v.emp_seq and
         t.job_seq = v.job_seq 
         t.labor_date = v.business_date
      micros.emp_def e on 
         s.emp_seq = e.emp_seq 
   [COLOR=red]where
      abs(datediff(minute,v.shift_start_time,t,clk_in_date_time)) < 5[/color]

If this returns data then trim it down to select just the fields you're interested in and compare it against the reports to make sure the numbers match.

Micros can post sales/tips to either the check or transaction employee. This is set to run by transaction employee. If the numbers are way off try changing the green lines in the top code to chk_emp_seq.

The red lines in the bottom code may or may not be needed. If you have employees workin in multiple RVC's, or if they clock in and out for breaks you'll need it to avoid generating a full record for each clock in. I used 5 minutes between clock in and shift start at random. You'll probably want to tweak this a bit if you leave the line in.


Are you using Res4.0 in your stores? Our new site will be my first 4.0 installation so I don't know what issues to expect. I'm also going to use the server as an image for a 16 store upgrade rollout starting in January so really want to make the current one solid.

Pat

 

[azrobert]

Our new store will be 4.0. Not sure what to expect either, our rep says it will be a big improvement but you are never sure till it "hits the fan" I will give this a try and see where it goes....

Thank You so much again !!

 

[azrobert]

when I run this:

ALTER VIEW custom.v_R_emp_shift_job_dtl(business_date,
emp_seq,
shift_seq,
shift_start_time,
job_seq,
job_name,
job_cat)
as select distinct t.business_date,
t.trans_emp_seq,
t.trans_emp_shift_seq,
d.shift_start_time,
t.job_seq,
j.name,
j.job_cat_seq from
micros.trans_dtl as t join
micros.shift_emp_dtl d on
t.trans_emp_seq, = t.emp_seq and
t.trans_emp_seq, = d.shift_seq join
micros.job_def as j on
t.job_seq = j.job_seq

I get a syntax error on the line: t.trans_emp_seq, = t.emp_seq and The comma is the problem also on the line below. If I take the commas out I get am invalid type for field reference error....

 

[pmegan]

Sorry, that was a typo. Try this.

ALTER VIEW custom.v_R_emp_shift_job_dtl(business_date,
  emp_seq,
  shift_seq,
  shift_start_time,
  job_seq,
  job_name,
  job_cat)
  as select distinct t.business_date,
    t.trans_emp_seq,
    t.trans_emp_shift_seq,
    d.shift_start_time,
    t.job_seq,
    j.name,
    j.job_cat_seq from
    micros.trans_dtl as t join
    micros.shift_emp_dtl d on
       t.trans_emp_seq = d.emp_seq and
       t.trans_emp_shift_seq = d.shift_seq join
    micros.job_def as j on
       t.job_seq = j.job_seq

 

[azrobert]

When I run the last "alter View" that works and I can return some data.

when I run this command against it I get a syntax error.

select distinct e.obj_num, e.last_name, e.first_name, t*, v.*, s.*
from
custom.v_R_emp_shift_job_dtl as v join
micros.shift_emp_ttl as s on
v.emp_seq = s.emp_seq and
v.shift_seq = s.shift_seq join
micros.time_card_dtl t on
t.emp_seq = v.emp_seq and
t.job_seq = v.job_seq
t.labor_date = v.business_date
micros.emp_def e on
s.emp_seq = e.emp_seq
where
abs(datediff(minute,v.shift_start_time,t,clk_in_date_time)) < 5


I am pretty sure I will need to include the last part we do have employees that clock in and out (double shift, sometimes different jobs) If it makes any difference I have increment shifts on clock in set on, and I am autoclocking out at 4:00 AM. so when i get the final data I mark all 4 am entries as autoclock outs that need to be looked at.

 

[azrobert]

If only the Micros techs I work with were this knowledgeable or helpful!

Just out of curiousity what region are you in ?

 

[azrobert]

i do think i see a . missing after the t here

but i still get a syntax error line 10 near t

t.labor_date = v.business_date


wish I understood views a little more, getting there with your help !

 

[azrobert]

oops should have been
i think i see a . missing here:
select distinct e.obj_num, e.last_name, e.first_name, t*, v.*, s.*

 

[pmegan]

Jeez, I shouldn't be allowed to type that early in the morning. This should do it.

   select distinct e.obj_num, e.last_name, e.first_name, t.*, v.*, s.*
   from 
      custom.v_R_emp_shift_job_dtl as v join
      micros.shift_emp_ttl as s on
         v.emp_seq = s.emp_seq and
         v.shift_seq = s.shift_seq join
      micros.time_card_dtl t on
         t.emp_seq = v.emp_seq and
         t.job_seq = v.job_seq and
         t.labor_date = v.business_date join
      micros.emp_def e on 
         s.emp_seq = e.emp_seq 
   where
      abs(datediff(minute,v.shift_start_time,t.clk_in_date_tm)) < 5

I'm in large restaurant group in Manhattan. Been at this job for 4 1/2 years and was a 3700 programmer/tech support for the local Micros dealer for about 4 years beforehand.

Where in AZ are you? I lived in Tempe for 3 years a while back.

Pat

 

[azrobert]

I'm in north Phx, with a 9 store mexican food rest chain. was a GM at one of the stores but switched to writing our back of the house / accounting and catering software.

 

[azrobert]

that runs without error but I get no data.