Querying a MICROS 9700 Database

[wrob]

I'm being asked to produce some numbers that appear on the "Cashier Financial" report out of a MICROS 9700 v3.0 system.

I've found that most figures I can reproduce accurately using CHECKS, CHECK_DETAIL, TENDER_MEDIA_DETAIL and a few others, but "Tips Paid" is stumping me.

I notice that there is a tender (TENDER_MEDIA.tendermediaid = 5) named "Tips Paid", but there are no detail records using it. There are aggregate tables in the Netvue database that join to this record (TENDER_MEDIA_DAILY_TOTAL and EMPLOYEE_DAILY_TOTAL) and if I only needed the total "Tips Paid" by revenue center I could use them, but I need it by cashier which apparently I can only get in the MICROS schema.

There's another figure on the report called "Tips Charged". This is different from "Tips Paid" - the two will frequently be the same, but not always. I know to get "Tips Charged" from TENDER_MEDIA_DETAIL.chargetip.

I'm assured that there is a cash register operation that corresponds to the action of reimbursing a cashier for tips charged. In fact, I've seen a piece of register tape that records such an event. I've so far failed to locate any corresponding data points, and so I can't reproduce "Tips Paid" from the "Cashier Financial" report.

Any shared insight would be greatly appreciated!

-Rob

 

[wrob]

OK, I figured it out.

The data appears to be in the TOTALS table, on rows where ttltype = 4. When I constrain on transactiondate (adjusting by 8 hours for some reason) the sum of amount1 ties to the amount on the reports.