Micros 3700 Menu Level Pricing to Ticket Menu Item 1

[HKLord]

I'm having some problems joining the Ticket Information back to the Menu Item and ultimately back to the mlvl_type to determine the volume of a pour. The rvc_mlvl_temp has two categories "M" and "S" for the mlvl_type. That difference is preventing me from joining the menu item to its mlvl (Regular, Up, Rocks, Double) which would allow me to calculate the actual amount of an item used for a given drink...

Any ideas of how to join the ticket detail all the way through the menu item to its RVC Transaction Sub Level Name...

 

[pmegan]

Try the price_lvl field in the mi_dtl table.

 

[HKLord]

Thanks Megan, I figured that I had it...
I included mi_dtl.price_lvl and chk_dtl.rvc_seq the other key seemed to be that I needed to join out on the dtl_type and transaction type

The system was set up so that the rvc_mlvl_temp table has the pricing for Regular, Up, Rocks, and Double (from the RVC Transactions Menu Levels), so the only consistant tie to volume of drinks poured id the rvc_mlvl_temp.name... We know that Regular is 1.25 oz,
Rocks and Up are 1.5 oz, and Double is 2.5 oz.

Do you know of any other way to get a volume of pour for a menu item? Maybe a recipe or something?

Thanks!

micros.rvc_mlvl_temp

rvc_seq mlvl_type mlvl name prefix
1 'M' 1 'Mon&Reg'
2 'M' 1 'Mon&Reg'
1 'M' 2 'Tuesday'
2 'M' 2 'Tuesday'
1 'M' 3 'Wed'
2 'M' 3 'Wed'
1 'M' 4 'Thursday'
2 'M' 4 'Thursday'
[highlight #FCE94F]1 'S' 1 'Regular'
2 'S' 1 'Regular'
1 'S' 2 'Up' 'Up'
2 'S' 2 'Up' 'Up'
1 'S' 3 'Rocks' 'Rk'
2 'S' 3 'Rocks' 'Rk'
1 'S' 4 'Double' 'Db'
2 'S' 4 'Double' 'Db'[/highlight]

 

[pmegan]

I don't know of an easy way to get that info, especially at the transaction level. You have to use the mi_type_class_def.ob_mi20_sub_lvl_for_prices field to see if the items use main or sub-levels for pricing and translate the T/F into S/M. This might do the trick. I only returned the columns necessary for testing it out. The virtual table, (v), can be changed to return any field you need from the mi_def or mi_type_class_def tables.

select 
    t.rvc_seq, d.rpt_cnt, d.rpt_ttl, m.mi_seq, v.name_1, m.price_lvl, v.mlvl_type, r.[name], r.prefix
from 
    micros.trans_dtl t
    join micros.dtl d
        on t.trans_seq = d.trans_seq
    join micros.mi_dtl m
        on m.trans_seq = d.trans_seq
        and m.dtl_seq = d.dtl_seq
    join (
        select 
            m.mi_seq
            , m.name_1
            , (case c.ob_mi20_sub_lvl_for_prices when 'T' then 'S' else 'M' end) [mlvl_type]
        from 
            micros.mi_def m 
            join micros.mi_type_class_def c
                on m.mi_type_seq = c.mi_type_seq
        ) as v
            on m.mi_seq = v.mi_seq
    join micros.rvc_mlvl_temp r
        on t.rvc_seq = r.rvc_seq
        and v.mlvl_type = r.mlvl_type
        and m.price_lvl = r.mlvl
where t.business_date = dateadd(day, -1, today(*))

 

[Wildbar]

Is this for a one off location? Or some sort of interface? Just asking as there is no standard link between menu item level and pour size for Micros. You would need to actually pull the recipe info from the system (if recipes have been set up) that links to the menu item at the level it was rung in at.

 

[HKLord]

This is basically the solution that I came up with as well. The missing piece for me was the lack of the trans_dtl.type. Once I had that, I could link like you were showing. Thanks for verifying my analysis!

 

[HKLord]

Wildbar,
Thanks for the input as well. No, it is a standard issue that all restaurants should be facing if they are pouring alcohol. In our case, it is for multiple brand name concepts and all of them have the same basic configuration. The system was configured by the experts at Micros, and works great, however the micros.rvc_mlvl_temp table currently contains the only reference to the pour sizes, which in turn allow us to determine the volume of alcohol used which significantly changes the inventory numbers for the units. The sales data is passed to Restaurant Magic, where an error is induced without the additional data regarding the pour size. Since I can now link to the pour size (description/cost) I can create additional menue items in Restaurant Magic with more precise inventory usage and pricing.
I hope that helps you to understand the issue as well.

Thanks All
Hk