Micros 3700 - Item Sales SQL Query 1

[bufftrek]

I'm trying to programmatically pull sales info from our Micros 3700 POS for inventory purposes and am running into issues. While I can determine usage from individual line items (Jack Daniels, Grey Goose, etc), I'm having difficulties when it comes to modifiers such as Doubles and Rocks. Is it possible to link 1 modifier to it's parent item? Essentially I get the following if I run a consolidated sales report:
[ul]
[li]Jack Daniels: 4 sold[/li]
[li]Grey Goose 3 sold[/li]
[li]$ Rocks: 1 sold[/li]
[li]$ Double: 3 sold[/li]
[/ul]

I'm trying to get the following:
[ul]
[li]Jack Daniels: 4 sold[/li]
[li]Rocks [Jack Daniels]: 1 sold[/li]
[li]Double [Jack Daniels]: 2 sold[/li]
[li]Grey Goose: 3 sold[/li]
[li]Double [Grey Goose]: 1 sold[/li]
[/ul]

 

[bufftrek]

Would anybody have an idea of other websites where I may be able to find assistance with this topic? Thank you!

 

[FreeSoldier]

Bufftrek,

The best solution is to create menu items called Rocks, Double etc that have required condiments. So when you click on Double it will prompt you for a spirit selection.
Note that the required condiments have to be different for Rocks and Double unless your pouring size is the same.

Good luck
FreeSoldier

 

[bufftrek]

I'm actually trying to determine how to pull this information via a SQL query. I realize now that I wasn't clear in my original post. Thank you for the advice!

 

[JVPzoom]

You're going to want to look at mi_dtl, dtl, and trans_dtl. Those 3 tables have the combination to provide you with main items, modifiers (via parent_dtl_seq), and the ability to order them correctly (via trans_seq for multiple condiments under one parent item).

As an example, here is something I've used to pull all checks and their line by line details (including special instructions) for the previous business date. It doesn't account for voided items as we utilize a separate query for the detail of those. Hope it helps

		SELECT DISTINCT
			trans_dtl.business_date,
			trans_dtl.trans_seq,
			trans_dtl.chk_seq,
			dtl.dtl_seq,
			mi_dtl.parent_dtl_seq,
			mi_dtl.mi_seq,
			mi_def.obj_num,
			mi_def.name_1,
			ref_dtl.ref,
			dtl.rpt_cnt,
			maj_grp_def.name AS major_category,
			fam_grp_def.name AS minor_category,
			dtl.rpt_ttl AS item_sales
		FROM micros.mi_dtl mi_dtl
		JOIN micros.dtl dtl ON dtl.trans_seq = mi_dtl.trans_seq AND dtl.dtl_seq = mi_dtl.dtl_seq AND dtl.dtl_type = 'M' AND dtl.record_type != 'R' AND dtl.ob_dtl05_void_flag != 'T'
		JOIN micros.mi_def mi_def ON mi_def.mi_seq = mi_dtl.mi_seq
		JOIN micros.maj_grp_def maj_grp_def ON maj_grp_def.maj_grp_seq = mi_def.maj_grp_seq
		JOIN micros.fam_grp_def fam_grp_def ON fam_grp_def.fam_grp_seq = mi_def.fam_grp_seq
		JOIN micros.trans_dtl trans_dtl ON trans_dtl.trans_seq = mi_dtl.trans_seq
		LEFT JOIN micros.ref_dtl ref_dtl ON ref_dtl.parent_trans_seq = mi_dtl.trans_seq AND ref_dtl.parent_dtl_seq = mi_dtl.dtl_seq
		WHERE trans_dtl.business_date = DATE(NOW()-1)
		ORDER BY trans_dtl.chk_seq ASC, trans_dtl.trans_seq ASC, dtl.dtl_seq ASC, mi_dtl.parent_dtl_seq ASC;

 

[FROZZA]

try to use PM stored procedures for sales extraction