Duplicate a menu item in Micros RES 3700 using SQL

[OracleSucks]

Hello,

I'm trying to figure out how to duplicate a menu item in RES 3700 using SQL. Now that they've moved over to Oracle they're pretty tight lipped when asked these kinds of questions (they used to at least point me in the right direction). Can anyone tell me which stored procs to use, or offer up some SQL queries?

Thanks in advance!

 

[pmegan]

What do you mean by duplicating a menu item? Just copying an existing item with a new object number?

 

[OracleSucks]

Yes. I would like to mimic the functionality of the "Copy Record" button in the POS Configurator.

 

[pmegan]

This should give you a starting point for it. The test server I did this on is running Res v4.11 and I just copied all the fields, so you may have to add or remove some of them depending on the version you have. All you have to do is enter the object number for the item you want to copy as @CopyFrom, and the object number for the new item as @CopyTo.

declare @CopyFrom int
declare @CopyTo int

set @CopyFrom =  /* object number of item to copy */
set @CopyTo =  /* object number to insert */

/* don't change anything below here */

declare @OrigSeq seq_num
declare @NewSeq seq_num

select mi_seq into @OrigSeq
from micros.mi_def
where obj_num = @CopyFrom

insert into micros.mi_def(
	obj_num
	,name_1
	,name_2
	,maj_grp_seq
	,fam_grp_seq
	,mi_grp_seq
	,mi_slu_seq
	,price_grp_seq
	,slu_priority
	,nlu_grp
	,nlu_num
	,key_num
	,icon_id
	,ob_mi31_chk_mi_avail
	,ob_mi44_no_edit_in_mgr_proc
	,ob_item_is_the_no_modifier
	,ob_lite_mi_dirty
	,mi_type_seq
	,cond_grp_mem_seq
	,cond_req
	,cond_allowed
	,crs_mem_seq
	,crs_sel_seq
	,mlvl_class_seq
	,prn_def_class_seq
	,product_seq_1
	,product_seq_2
	,product_seq_3
	,product_seq_4
	,comm_amt
	,comm_pcnt
	,cross_ref1
	,cross_ref2
	,last_updated_by
	,last_updated_date
	,ob_workstation_only
	,mi_slu2_seq
	,ob_flags
	,multi_user_access_seq
	,topping_type_seq
	,prep_time
	,external_type
	,topping_modifier_seq
	,build_screen_style_seq
	,hht_build_screen_style_seq
	,prefix_override_count
	,mi_slu3_seq
	,mi_slu4_seq
	,mi_slu5_seq
	,mi_slu6_seq
	,mi_slu7_seq
	,mi_slu8_seq
)
select
	@CopyTo
	,name_1
	,name_2
	,maj_grp_seq
	,fam_grp_seq
	,mi_grp_seq
	,mi_slu_seq
	,price_grp_seq
	,slu_priority
	,nlu_grp
	,nlu_num
	,key_num
	,icon_id
	,ob_mi31_chk_mi_avail
	,ob_mi44_no_edit_in_mgr_proc
	,ob_item_is_the_no_modifier
	,ob_lite_mi_dirty
	,mi_type_seq
	,cond_grp_mem_seq
	,cond_req
	,cond_allowed
	,crs_mem_seq
	,crs_sel_seq
	,mlvl_class_seq
	,prn_def_class_seq
	,product_seq_1
	,product_seq_2
	,product_seq_3
	,product_seq_4
	,comm_amt
	,comm_pcnt
	,cross_ref1
	,cross_ref2
	,last_updated_by
	,last_updated_date
	,ob_workstation_only
	,mi_slu2_seq
	,ob_flags
	,multi_user_access_seq
	,topping_type_seq
	,prep_time
	,external_type
	,topping_modifier_seq
	,build_screen_style_seq
	,hht_build_screen_style_seq
	,prefix_override_count
	,mi_slu3_seq
	,mi_slu4_seq
	,mi_slu5_seq
	,mi_slu6_seq
	,mi_slu7_seq
	,mi_slu8_seq

from micros.mi_def where obj_num = @CopyFrom

set @NewSeq = @@identity

update micros.mi_price_def NP
set 
    NP.preset_amt_1 = OP.preset_amt_1
    ,NP.preset_amt_2 = OP.preset_amt_2
    ,NP.preset_amt_3 = OP.preset_amt_3
    ,NP.preset_amt_4 = OP.preset_amt_4
    ,NP.preset_amt_5 = OP.preset_amt_5
    ,NP.preset_amt_6 = OP.preset_amt_6
    ,NP.preset_amt_7 = OP.preset_amt_7
    ,NP.preset_amt_8 = OP.preset_amt_8
    ,NP.preset_amt_9 = OP.preset_amt_9
    ,NP.preset_amt_10 = OP.preset_amt_10
from
    micros.mi_price_def OP
where
    NP.mi_seq = @NewSeq
    and OP.mi_seq = @OrigSeq

 

[Moregelen]

I'd be wary doing it that way simply because of the risk of changes to the database structure.

Here is what I'm doing..

This code to get the next available object number from the one I'm copying:

        [COLOR=#808080]///[/color][COLOR=#008000] [/color][COLOR=#808080]<summary>[/color]
        [COLOR=#808080]///[/color][COLOR=#008000] Get the next object number available after the specified menu item[/color]
        [COLOR=#808080]///[/color][COLOR=#008000] [/color][COLOR=#808080]</summary>[/color]
        [COLOR=#808080]///[/color][COLOR=#008000] [/color][COLOR=#808080]<param name="[/color]mi_seq[COLOR=#808080]">[/color][COLOR=#008000]sequence of menu items to find free row after[/color][COLOR=#808080]</param>[/color]
        [COLOR=#808080]///[/color][COLOR=#008000] [/color][COLOR=#808080]<returns></returns>[/color]
        [COLOR=#0000FF]private[/color] [COLOR=#0000FF]int[/color] NextObjectNumber([COLOR=#0000FF]int[/color] mi_seq)
        {
            [COLOR=#0000FF]string[/color] sql_cmd = [COLOR=#0000FF]string[/color].Format(
                [COLOR=#800000]@"SELECT FIRST o.obj_num + 1[/color]
[COLOR=#800000]                    FROM MICROS.mi_def as o[/color]
[COLOR=#800000]                    WHERE o.obj_num >= (SELECT obj_num FROM MICROS.mi_def WHERE mi_seq = {0}) AND[/color]
[COLOR=#800000]                    ([/color]
[COLOR=#800000]                        SELECT i.obj_num[/color]
[COLOR=#800000]                        FROM MICROS.mi_def as i[/color]
[COLOR=#800000]                        WHERE i.obj_num = o.obj_num +1[/color]
[COLOR=#800000]                    ) IS NULL[/color]
[COLOR=#800000]                    ORDER BY obj_num"[/color], mi_seq
            );
            [COLOR=#2B91AF]DataTable[/color] tempTable = _MicrosDB.Query(sql_cmd);
            [COLOR=#0000FF]return[/color] tempTable.Rows.Count > 0 ? ([COLOR=#0000FF]int[/color])tempTable.Rows[0][0] : 1;
        }

And this code to actually copy paste the item:

        [COLOR=#808080]///[/color][COLOR=#008000] [/color][COLOR=#808080]<summary>[/color]
        [COLOR=#808080]///[/color][COLOR=#008000] Copies and Pastes a menu item and returns the new items mi_seq[/color]
        [COLOR=#808080]///[/color][COLOR=#008000] [/color][COLOR=#808080]</summary>[/color]
        [COLOR=#808080]///[/color][COLOR=#008000] [/color][COLOR=#808080]<param name="[/color]mi_seq[COLOR=#808080]">[/color][COLOR=#008000]The sequence number of the item to copy and paste[/color][COLOR=#808080]</param>[/color]
        [COLOR=#808080]///[/color][COLOR=#008000] [/color][COLOR=#808080]<returns>[/color][COLOR=#008000]The new menu item's sequence number[/color][COLOR=#808080]</returns>[/color]
        [COLOR=#0000FF]public[/color] [COLOR=#0000FF]int[/color] CopyPaste([COLOR=#0000FF]int[/color] mi_seq)
        {
            [COLOR=#0000FF]int[/color] new_obj_num = NextObjectNumber(mi_seq);
            [COLOR=#0000FF]string[/color] sql_cmd = [COLOR=#0000FF]string[/color].Format([COLOR=#800000]@"[/color]
[COLOR=#800000]                SELECT * INTO #temp_mi_def FROM MICROS.mi_def WHERE mi_seq = {0};[/color]
 
[COLOR=#800000]                UPDATE #temp_mi_def SET obj_num = {1}, mi_seq = (SELECT MAX(mi_seq)+1 FROM MICROS.mi_def);[/color]
 
[COLOR=#800000]                INSERT INTO MICROS.mi_def SELECT * FROM #temp_mi_def;[/color]
 
[COLOR=#800000]                DROP TABLE #temp_mi_def;[/color]
 
[COLOR=#800000]                SELECT mi_seq FROM MICROS.mi_def WHERE obj_num = {1};"[/color],
                mi_seq, new_obj_num
            );
            [COLOR=#2B91AF]DataTable[/color] result = _MicrosDB.Query(sql_cmd);
            [COLOR=#0000FF]return[/color] (result != [COLOR=#0000FF]null[/color] && result.Rows.Count > 0) ? ([COLOR=#0000FF]int[/color])result.Rows[0][[COLOR=#A31515]"mi_seq"[/color]] : -1;
        }

I have another two methods that set the price records for menu items by copying them from another, but once you do this in a script there is a trigger that automatically creates the $0 price records for you, so at that point all you have to do is copy the existing record in.

 

[pmegan]

It doesn't change the structure, you actually can't change the structure logged in as a non-dba. This scriptlet just inserts a record using all the setting from another except the obj_num. Once it's created I'm using @@identity to store the mi_seq for the newly added item, and updating the trigger generated mi_price_def record using that mi_seq.