MICROS 3700 Sales and Transaction Data 6

[froehlje]

I am looking to export the sales and transaction data from our Micros 3700 system to another machine for reporting. Does anyone know the tables that are used to store transaction and sales data?

Thanks,

Jim

 

[regilobo]

The online docs contain a comprehensive discussion of the reports and where the data is pulled from, down to the table and column used for each report.
There is also a document for SQL/Database Access.

By default they are installed in:

/micros/documentation/res/pos/reports/3700posrpts_man.pdf
/micros/documentation/res/common/sql/3700sql_man.pdf

But you can access them using the documentation link from the Micros menu on the Programs menu.

 

[pmegan]

Micros.trans_dtl is the main transaction table that will link transactions to checks, employees and RVC's.

Micros.sale_dtl holds order type and itemizer sales by transaction. Join it to micros.trans_dtl on the common field trans_seq.

Drilling in further needs an extra table link. Micros.dtl holds the details for each entry within a transaction. This also joins to micros.trans_dtl on the common field trans_seq.

You can join micros.dtl to the menu item (mi_dtl), discount/service charge, (dsvc_dtl) and payment, (tmed_dtl) details on the common fields trans_seq and dtl_seq. This will give you strictly numbers and codes. If you need the item names you'll have to also join the definition tables.

So, to get menu item details you would use something like this.

select * 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

Extracting details for multiple item types can get a bit tricky. Using inner joins to get both menu item and payment totals for instance will return nothing. The trans_seq/dtl_seq combos will have cancelled each other out. Use either individual queries or union a bunch together. The detail queries can get pretty labor intensive, so using one monster query with outer joins will work but it can bog down the server.

Last, remember that micros details are only held for 14 days. I don't usually trust the data from the earliest day in these tables. The data purge is hardcoded at 14 days, so if it's run mid shift you'll only have partial data for the earliest detail date.

Hope this helps,
Pat

 

[froehlje]

Thanks for the information.

Do you know the different transaction type that are found in the trans_dtl table?

Another project that I am looking at involves finding out when cashiers are loging in and out. I know most other POS systems that I have worked with treat these as transactions. Is this type of activity recorded in this table?

Thanks.

 

[pmegan]

These are the transaction types, (micros.trans_dtl.type):
B - balance correction
C - cancel
N - no sale
P - pickup loan
S - sale
T - tips declared
X - check transfer

These are the detail types, (micros.dtl.dtl_type):
D - discount
M - menu item
T - tender/media
R - reference
C - credit auth
N - null
V - voucher

Micros uses "cashiers" differently than other systems and they're frequently confused with employees. Everybody who runs a transaction is an employee, even if their only function is to pick up checks and tender them. Micros "cashiers" are just buckets to store totals. A cashier can be assigned by employee(s), workstation(s) or cash drawer(s). Every transaction rung with a cashier link will land in that cashier's totals. This being the case, cashiers don't actually log in/out.

You can find when transactions with cashier links were run in the micros.trans_dtl table. The fields you'll probably want for this are cshr_ttl_seq, cshr_ttl_shift_seq, start_date_tm and end_date_tm.

If you're looking for employees whose role is a cashier you can add in the fields trans_emp_seq/trans_emp_shift_seq to get the employee who ran the transaction and chk_emp_seq/chk_emp_shift_seq to get the employee who owned the check.

You can also track the start/end times in micros.shift_cshr_dtl for cashiers and micros.shift_emp_dtl for employees if that's what you're looking for.

The one thing you won't be easily able to do with the standard set of database objects is generate employee/cashier reports by business date. Micros runs on shifts which run independant of dates, so an employee or cashier can have multiple shifts in one day or a shift that spans multiple days.