Micros Guest Check Detail Query

[GreekBeak]

I've been trying like crazy to recreate what's printed on a guest check with a SQL query and I'm not finding the correct tables.

My test SQL statement is listed here with a bunch of test columns but this isn't the final check, it looks like what is sent to the kitchen.

ANY help would be greatly appreciated!!

   select 
   tdtl.chk_emp_seq as Check_Emp, 
   tdtl.chk_seq as Check_Seq, 
   tdtl.cshr_ttl_seq as Cashier_Total_Seq, 
   tdtl.end_date_tm as End_date, 
   tdtl.trans_emp_seq as Transaction, 
   tdtl.trans_seq as Transaction_Seq, 
   d.chk_cnt as Check_Count, 
   d.chk_ttl as Check_Total, 
   d.date_time as Date_Time, 
   d.dtl_index as Detail_Index, 
   d.dtl_seq as Detail_Seq, 
   d.dtl_type as Detail_Type, 
   d.record_type as Record_type, 
   d.rpt_cnt as Report_Count, 
   d.rpt_ttl as Report_Total, 
   d.seat as Seat, 
   mdef.name_1 as MenuName1, 
   mdef.name_2 as MenuName2, 
   ckdtl.amt_due_ttl as TotalBill, 
   ckdtl.auto_svc_ttl as SvcChargeTtl, 
   ckdtl.chk_num as CheckNum, 
   ckdtl.chk_seq as CheckSeq, 
   ckdtl.cov_cnt as CoverCnt, 
   ckdtl.emp_seq as EmployeeSeq, 
   ckdtl.num_dtl as DetailNum, 
   ckdtl.order_type_seq as OrderType, 
   ckdtl.pg_num as PageNum, 
   ckdtl.pymnt_ttl as PaymentTot, 
   ckdtl.sub_ttl as SubTotal, 
   ckdtl.tax_ttl as TaxTotal, 
   ckdtl.tbl_open_date_time as TableOpnDate 
   from  micros.trans_dtl as tdtl join  
   micros.dtl as d on    
   tdtl.trans_seq = d.trans_seq join  
   micros.mi_dtl as mdtl on     
   d.trans_seq = mdtl.trans_seq and    
   d.dtl_seq = mdtl.dtl_seq join  
   micros.mi_def as mdef on    
   mdtl.mi_seq = mdef.mi_seq  join     
   micros.chk_dtl as ckdtl on       
   tdtl.chk_seq = ckdtl.chk_seq   
   where ckdtl.chk_num = 348;
output to 'D:\micros\database\data\chkdt348.txt'

Thanks,
George

 

[DrZogg]

This will be done by stored procedure. Have you looked there?

 

[GreekBeak]

If I go into Touchscreen Designer and find the "Edit Closd Check" button I can see that the Category is "Function : Check Access" and the Function is "Edit Closed Check"

I thought like you said that this would be a procedure but I could relate this information from Touchscreen Designer into any stored procedures. I don't remember exactly what happens when you use this button but of course it starts by asking for a check number and then I think it asks if you want to edit. It does show what was printed on the check so it grabs the information I want.

Do have any help information on how I can find the stored procedure?

 

[GreekBeak]

I've been using a program called RazorSQL which works pretty good for seeing what's inside the database and performing queries and other database operations as well as some hooks into other programming tools. It looks like a pretty useful tool so if haven't heard of it you might want to take a look.

I still haven't been able to find the function but another problem I have is that both custom/custom and support/support don't allow me access to view procedures so it make it difficult to see what they are doing. Do you have another way to look under the hood?

While I'm in the question asking mode I've got another. I've got a VM up and running that I'm using to test and debug. Of course I don't have the security dongle or workstations setup but if I go into the MICROS Control Panel shouldn't something be running with a green check mark? Right now all I see is a red X next to restaurant but on the actual server it lists

Restaurant
MICROS1
Diskless Boot Server
SQL Database Server
....

Since I don't have the ket does that lock out everything else or should I see SQL Database Server with a green check mark? I know the server is running because I can perform queries so maybe I'm starting the server differently than Micros expects?

 

[MikeRose]

What exactly are you trying to do... your query above will give you all the detail rows, but i am assuming you are trying to get the summarized numbers on the guest check?

 

[GreekBeak]

I'm trying to get everything on the check and this query isn't getting the payments and discounts applied. What I find odd is that the chk_dtl table doesn't have this info and all the columns are blank except for the check sequence field.

I'm not at my desk right now to check but I seem to recall that this query is actually pulling the check as it's going to the kitchen and not the closed check presented to the customer.

 

[GreekBeak]

With a little more poking around the following query seems to contain all of the information (and more) that are printed on the check but I'll need to dig through the documentation and learn what some of this field definitions are. I stumbled into this query from this older thread:

http://www.tek-tips.com/viewthread.cfm?qid=1568424

select * from micros.vta_BaseDtl where C_chk_num=3244

But as Robert stated in that thread he was also looking for just what was printed on the guest check and not everything.

As I mentioned earlier in this thread there's a function called "Edit Closed Check" which does what I want but I haven't been able to dig into the database and find this UDF.

 

[GreekBeak]

I can't seem to find documentation on vta_BaseDtl in the SQL2603700.pdf document. Is it hidden somewhere else?

 

[pmegan]

vta_BaseDtl is a view that's used by Transaction Analyzer. Micros hasn't put much effort into keeping up to date documentation available to their customers and that SQL guide you have was probably released before Transaction Analyzer.

If you're working on a Micros lab system why don't you just use the dbisql and sybase central programs that get installed with res? You can see get the definitions for all the views and sprocs from there so it's a huge help.

 

[GreekBeak]

Thanks for the kick in the but. I have been using RazorSQL to browse through the DB and it wasn't giving me much about the view so I thought it was perhaps the custom/custom and support/support didn't have rights. I see now when I connect to the DB in Sybase Central I can see the full details of the views.

It looks like I can limit which columns to select with this view and re-create the Guest Check information that I want to extract. Is this the route you took or did you switch to using journals or something else?

My plan is to pull this data out and dump it into a MySQL database I've got running so we can merge this data with our web site and customer relations development.

 

[pmegan]

I use SQL for any data extraction projects. Parsing through the journals is too labor intensive and unreliable for me. The custom user has resource privileges, so you can make your own views or sp's and pull your data. I'm not a big fan of plain text passwords in scripts so I mostly use stored procedures to dump flat files.

If you go that route make sure to grant execute, (or select for views), privileges to the Reports group, and add a line to the micros.stored_proc_def table so you can call it from an autosequence step.

 

[GreekBeak]

Thanks for your tips. I wasn't looking forward to parsing the journal but I thought it might be a quick and easy way to get data out automatically while I figured out what could be done inside Micros. I was thinking of the stored procedure route and adding it into an autosequence so I'm glad to hear that's a good way to go.

So it sounds like you rely on Micros to push data out rather than pull data from some outside program? Do you do this on a transaction basis or wait and get daily dumps when the restaurant is closed?

Do I need to grant privileges to the Reports group because I may end up relying on them for some of the queries or is there another reason?

 

[pmegan]

Most of our exports are daily and run from the posted totals tables, but a few run hourly so have to be called from the transaction tables. Either way works, it's just a little more work keeping track of the last exported transaction. Take a look at some of the posting procedures and the hist_ttl_def table and they'll give you some good ideas.

Yes, everything is pushed from micros rather than pulled. Back in the day it was sometimes tough to connect to the db from an outside system so pushing was the safest way to go.

The reports and autosequences connect to the database as the reports users. They don't have access to objects owned by the custom user by default, so if you don't explicitly grant permissions you just get some very ugly, very unhelpful errors in the d log.

 

[GreekBeak]

In Touchscreen Designer there is button assigned to the "Edit Closed Check" Function but when I use Sybase Central to browse the micros DB and navigate to the "Procedures & Functions" folder I don't see a function named "Edit Closed Check" or anything with check in it. The only functions I can see are:

ConvertQty
ConvertPrice
GetQtyOnOrder
GetQtyOnHand
CheckRounding

So what am I missing this time?

 

[GreekBeak]

Still looking for help from someone if they know where the "Edit Closed Check" function defined.