Micros 3700 SQL Handling Quantity Modifiers

[bufftrek]

I am using the following query to pull sales from Micros 3700:

SELECT micros.trans_dtl.chk_seq as CheckNo, micros.mi_def.obj_num AS PLU, micros.mi_def.name_1 AS ItemName, (micros.dtl.rpt_cnt) AS Qty, micros.dtl.date_time AS DateTimeOrdered, micros.lan_node_def.name AS Terminal,micros.dtl.chk_ttl AS RetailPrice, micros.trans_dtl.Trans_Seq as ID,'false' as IsCurrent FROM .....

The problem I am running into is that when a quantity modifier is used then I am getting a larger price per item sold than what is reported.

On the quick bar screen there is a list of numerical 'modifiers' that allow quantities to be rang faster. With the noted issue, I will get 5 Crown Royal sold at $30 each as opposed to 5 Crown Royals sold at $6 each (it is spitting out the revenue for that line item as opposed to the price per item). Should I be using something other than "micros.dtl.chk_ttl AS RetailPrice" to pull the correct retail value of each item sold when this item is rang with said quantity modifiers?

 

[hosehead78]

Are you pulling information from multiple Revenue Centers (RVC's)? If so then it might be a thought to put a Where/And clause for RVC_NUM # = XX.
If you sale the same items at different RVC's, this could be a cause as to why it is so high, when you look at another RVC's Menu item sales.

 

[bufftrek]

This only happens when an item's count being sold is modified with the numberical keys. All other items come through as qty sold of 1 or -1.

 

[pmegan]

I'm not quite getting this quantity modifier. Is it an actual modifier, or are you just hitting the number 5 and then Crown Royal instead of hitting the Crown Royal button 5 times?

 

[bufftrek]

I'm sorry, it is not a modifier.
You are right in that the Quantity button is being pressed before the item is selected.

 

[pmegan]

Ok, that's where your problem is then. The detail lines that look overstated are for multiples of the same item, not the proce per item. They're correct the way they are, but if you still need the price per item, add a column using (micros.dtl.rpt_ttl / micros.dtl.rpt_cnt).

Just as a note here, if this is really what your query looks like:

SELECT micros.trans_dtl.chk_seq as CheckNo, micros.mi_def.obj_num AS PLU, micros.mi_def.name_1 AS ItemName, (micros.dtl.rpt_cnt) AS Qty, micros.dtl.date_time AS DateTimeOrdered, micros.lan_node_def.name AS Terminal,micros.dtl.chk_ttl AS RetailPrice, micros.trans_dtl.Trans_Seq as ID,'false' as IsCurrent FROM...

You may want to look into using aliases and formatting. It'll make troubleshooting a whole lot easier if you're looking at this:

[pre]
select
TR.chk_seq [CheckNo]
,MDEF.obj_num [PLU]
,MDEF.name_1 [ItemName]
,DTL.rpt_cnt [Qty]
,DTL.date_time [DateTimeOrdered]
,LAN."name" [Terminal]
,DTL.chk_ttl [RetailPrice]
,TR.trans_seq [ID]
,'false' [IsCurrent]
from
micros.trans_dtl [TR]
join micros.dtl [DTL]
on TR.trans_seq = DTL.trans_seq
join micros.mi_dtl [MDTL]
on DTL.trans_seq = MDTL.trans_seq
and DTL.dtl_seq = MDTL.dtl_seq
join micros.mi_def MDEF
on MDTL.mi_seq = MDEF.mi_seq
join micros.uws_def [UWS]
on TR.uws_seq = UWS.uws_seq
join micros.dev_def [DEV]
on UWS.uws_dev_seq = DEV.dvc_tbl_seq
join micros.lan_node_def [LAN]
on DEV.lan_node_seq = LAN.lan_node_seq

[/pre]
instead of this:

[pre]
select micros.trans_micros.dtl.chk_seq as CheckNo ,micros.mi_def.obj_num as PLU ,micros.mi_def.name_1 as ItemName ,micros.dtl.rpt_cnt as Qty ,micros.dtl.date_time as DateTimeOrdered ,micros.lan_node_def."name" as Terminal ,micros.dtl.chk_ttl as RetailPrice ,micros.trans_micros.dtl.trans_seq as ID ,'false' as IsCurrent from micros.trans_dtl join micros.dtl on micros.trans_micros.dtl.trans_seq = micros.dtl.trans_seq join micros.mi_dtl on micros.dtl.trans_seq = Mmicros.dtl.trans_seq and micros.dtl.dtl_seq = micros.dtl.dtl_seq join micros.mi_def on micros.dtl.mi_seq = micros.mi_def.mi_seq join micros.uws_def on micros.trans_micros.dtl.uws_seq = micros.uws_def.uws_seq join micros.dev_def on micros.uws_def.uws_dev_seq = micros.dev_def.dvc_tbl_seq join micros.lan_node_def on micros.dev_def.lan_node_seq = micros.lan_node_def.lan_node_seq
[/pre]