Micros 3700 How to programmatically retrieve Guest check details.

[jmercado27]

I've been trying to retrieve what's printed on a guest check with a SQL query, stored procedure, or find a view that does this. I seem to be getting additional information with just a query.

select
ckdtl.chk_num,
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 1 = 1
order by Date_Time desc

Steps to get the additional information:
A check is created with one item. (Using the query there is just the one row that can be seen for the new check at the top)
This check is split. (Using the query there are now 2 rows that can be seen for the split items)
The split items are combined to one check (Using the query there are now 4 rows that can be seen for the split items. 3 rows for one check which includes the original item and the splits and 1 row for the old split that has been combined to the other check)


How do I get just the split items for the one check correct check? Is there something I should be filtering on. There is nothing that jumps out at me as something that makes sense. Please help!!!

Data included below:


chk_num,Check_Emp,Check_Seq,Cashier_Total_Seq,End_date,Transaction,Transaction_Seq,Check_Count,Check_Total,Date_Time,Detail_Index,Detail_Seq,Detail_Type,Record_type,Report_Count,Report_Total,Seat,MenuName1,MenuName2,TotalBill,SvcChargeTtl,CheckNum,CheckSeq,CoverCnt,EmployeeSeq,DetailNum,OrderType,PageNum,PaymentTot,SubTotal,TaxTotal,TableOpnDate
1079,1253,2747,,'2014-09-16 13:33:18.000',1253,7626,0,2.00,'2014-09-16 13:31:54.000',-1,1,'M','A',0,0.00,1,'Budweiser',,2.00,0.00,1079,2747,1,1253,17,1,0,0.00,2.00,0.00,'2014-09-16 13:31:27.000'
1078,1253,2746,,'2014-09-16 13:31:54.000',1253,7625,1,4.00,'2014-09-16 13:31:54.000',-1,1,'M','A',1,4.00,1,'Budweiser',,4.00,0.00,1078,2746,2,1253,18,1,0,0.00,4.00,0.00,'2014-09-16 13:31:27.000'
1078,1253,2746,,'2014-09-16 13:34:46.000',1253,7630,0,2.00,'2014-09-16 13:31:54.000',17,1,'M','I',0,0.00,2,'Budweiser',,4.00,0.00,1078,2746,2,1253,18,1,0,0.00,4.00,0.00,'2014-09-16 13:31:27.000'
1078,1253,2746,,'2014-09-16 13:33:19.000',1253,7628,1,2.00,'2014-09-16 13:31:54.000',16,2,'M','I',0,0.00,1,'Budweiser',,4.00,0.00,1078,2746,2,1253,18,1,0,0.00,4.00,0.00,'2014-09-16 13:31:27.000'

 

[nomed]

The query you're trying may be really tricky.

You should focus on:

trans_dtl
chk_dtlI
dtl

May i ask you what's the goal ?

I recently released an open source web application:

http://www.zeroisp.com/lilliput-0.1.0.html#.VCGsWt-bEfk

that does more or less what you're trying.

You can even see an example of exported check detail.

I'd be very happy to improve it with feedbacks of ppl that may find it useful.

Best Regards

 

[pmegan]

His query should work, it's pretty straightforward for pulling menu item transaction details. There are fields in the results that aren't available in the three tables you mentioned so mi_dtl and mi_def are needed.

I'm having the same problem though. Queries like this work until a check is split. I can see the transactions where the check is split, but not the actual menu items that are transferred.

 

[nomed]

I couldn't test that query on a db but maybe you should filter record_type:

where 1 = 1 and d.record_type <> 'R'
and d.dtl_type <> 'N'

 

[pmegan]

Why would you use 1=1? That's just a condition that will always be true. Tricks like that were needed in old programming languages to make sure that there was always a result, but here it's kind of redundant. The R and N detail types will be filtered out by linking to mi_dtl, which will only return M detail lines.

The problem that jmercado and I are both having is with menu items being transferred between checks. When that happens you get an X transaction type, but no entries in the dtl or mi_dtl tables. In my case I've been asked to create a report that shows major group sales by tender. It's not really hard until you get to split checks. Say you have a check with $100 in food and $100 in wine. If that check is paid to Visa, then those amounts are recorded as being paid to Visa. However, if the food is split onto a second check it becomes a problem. The original check, with $100 wine, will be closed to Visa. The split check, with $100 food, will be paid to cash. When you sum up the totals all the menu items are shown as still belonging to the first check, so you get Visa with $100 food and $100 wine, and Cash with $0 food and $0 wine. There are no entries linking the mi_seq for those items to the split chk_seq.

 

[nomed]

hehe well i just copy pasted the "where 1=1" to show where to add the conditions.

My approach is to split checks in :

header ( one for each check_num bringing daily_ops infos )

and generic rows:

service charge
items ( item discounts )
sbt discounts
tenders

indeed i'd not see tenders and sales mix as same fact.

By the way i'll debug further the case you suggested to see results.

 

[jmercado27]

pmegan
The 1 = 1 , is just there for testing and readability. This allows you to comment out some of the filters
easily w/o having to rewrite the code. Like so:
where 1 = 1
--and d.record_type <> 'R'
and d.dtl_type <> 'N'

But as you state beautifully " There are no entries linking the mi_seq for those items to the split chk_seq. " And that leaves us back at square one..
Has anyone else solved this yet? Or knows how to get the check detail items from a split check that has been put back to the original check, with out getting the original item as well?

So the scenario is: 1) A check is created with one bottle of wine. And sent in.
2) This check is split in two. And sent in.
3) Food Items are put on both checks separately. And sent in.
4) The checks are combined back onto the original check. And sent in.

This scenario will give me 3 bottles of wine (the original and the 2 split) and 2 food items. What I should see is 2 split bottles of wine and 2 food items.

Thank you anyone who can answer this!! May your code always compile with no exceptions!!

 

[nomed]

may you export the micros.trans_dtl.type as column too ?

wandering if all rows have the same as for d.record_type and d.dtl_type

 

[pmegan]

[ol 1]
[li]A check is created with one bottle of wine. And sent in.[/li]
[li]This check is split in two. And sent in. [I'm assuming you split the wine equally, so at this point you'll have 2 checks with 1/2 bottle on each][/li]
[li]Food Items are put on both checks separately. And sent in.[/li]
[li]The checks are combined back onto the original check. And sent in.[/li]
[/ol]

What you'll end up with is this.
In POS operations:

check 1 will have one bottle of wine and all the food items​

check 2 will have no items and a $0 balance​

In the database:

check 1 will have one bottle of wine and the food that was added while the checks were still split​

check 2 will have no wine and the food items that were added to it while the checks were still split​

there will be no trace of wine ever being on check 2
the food items that were merged back onto check 1 will still be linked to check 2​

 

[pmegan]

wandering if all rows have the same as for d.record_type and d.dtl_type

No, they're different
dtl_type

D - Discount
T - Tender/Media
C - Credit Auth
V - Voucher
M - Menu Item
R - Reference
N - Null​

record_type

D - Delete
R - Replace
A - Append
I - Insert
S - Skip​

There are 2 report templates in \micros\res\pos\reports that aren't linked to anything. Take a look at them, they're a big help, even though they're a throwback and haven't really been updated in years.
Domain.rpt - this defines the custom data types
Schema.rpt - this lists all the tables in the database and the data types assigned to each column


I'm really glad you asked that question. Looking at the record type gave me a thought and I think it may be right. It looks like records are appended and replaced when a check is split. The original totals held in the dtl.rpt_ttl field don't change, but the totals in the dtl.chk_ttl field are updated to reflect the split item amounts. It seems I have a new path to follow for this.

 

[jmercado27]

A check is created with one bottle of wine. And sent in.
This check is split in two. And sent in. [I'm assuming you split the wine equally, so at this point you'll have 2 checks with 1/2 bottle on each]
Food Items are put on both checks separately. And sent in.
The checks are combined back onto the original check. And sent in.
What you'll end up with is this.
In POS operations:
check 1 will have one bottle of wine and all the food items
Not the case. The original check will have 2 1/2 bottles of wine, and 2 food items.
check 2 will have no items and a $0 balance This check does not exist any more.
In the database:
check 1 will have one bottle of wine and the food that was added while the checks were still split
check 2 will have no wine and the food items that were added to it while the checks were still split
there will be no trace of wine ever being on check 2
the food items that were merged back onto check 1 will still be linked to check 2.

I am not sure about the record_type being the key. Here is the data for the scenario that I described:
1016 1253 2684 2014-09-09 11:03:31.0001253 7319 18.95 2014-09-09 11:03:12.00019 2 MI0 0.00 2 Fettucine Alfrdo 0.00 0.00 1016 2684 1 1253 21 1 0 47.45 44.95 2.50 2014-09-09 11:02:04.000
1016 1253 2684 2014-09-09 11:02:53.0001253 7315 116.00 2014-09-09 11:02:53.000-1 1 MA1 16.00 1 Margherita 0.00 0.00 1016 2684 1 1253 21 1 0 47.45 44.95 2.50 2014-09-09 11:02:04.000
1016 1253 2684 2014-09-09 11:02:24.0001253 7311 120.00 2014-09-09 11:02:24.000-1 1 MA1 20.00 1 Chardonnay 0.00 0.00 1016 2684 1 1253 21 1 0 47.45 44.95 2.50 2014-09-09 11:02:04.000
1016 1253 2684 2014-09-09 11:03:31.0001253 7319 010.00 2014-09-09 11:02:24.00018 1 MI0 0.00 2 Chardonnay 0.00 0.00 1016 2684 1 1253 21 1 0 47.45 44.95 2.50 2014-09-09 11:02:04.000
1016 1253 2684 2014-09-09 11:02:39.0001253 7314 110.00 2014-09-09 11:02:24.00016 2 MI0 0.00 1 Chardonnay 0.00 0.00 1016 2684 1 1253 21 1 0 47.45 44.95 2.50 2014-09-09 11:02:04.000

Here the $20 Chardonnay is an A type. As well as the Margherita. But only the $20 Chardonnay should not be there. The rest are I types. Hmm.. well let me know if you find the answer. Thanks again

 

[nomed]

Hi ,

what 's the value of micros.trans_dtl.type ?

 

[jmercado27]

All M

 

[nomed]

Well ... M can be micros.dtl.dtl_type what i 'm asking is micros.trans_dtl.type

 

[jmercado27]

Correction.... I mean
micros.trans_dtl.type = S for all the rows

 

[RitwickGupta]

I am looking at a similar issue. This is a very informative thread

 

[jmercado27]

So was anyone able to get this sorted?

 

[pmegan]

I haven't. I actually bailed on it and used sales itemizers posted to a custom table by a final_tender SIM event instead.

 

[hosehead78]

Can't say that I have, I do not have an active 3700 db to work with.

 

[nomed]

Hi all,

i could reproduce the tricky checks.

What i found is that the sales items cnt is 0.

The real issue from my micros demo install cames from service charge.


jmercado27 wrote in his ex that he'll receive 3 bottles of wines.

That's not really correct , i get trans cnt 0 and 0 for both and 1 for the re-merged check, indeed that's correct.

While with service charge , instead of 2 covers ( tot 2 £ )§ i receive 3 covers ( tot 3£ )

I'll investigate more and i'll post here any news

Best regards