Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Duplicate a menu item in Micros RES 3700 using SQL

Status
Not open for further replies.
OracleSucks

OracleSucks

Programmer
Joined
Sep 25, 2015
Messages
8
Location
CA
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!
 
Sort by date Sort by votes
What do you mean by duplicating a menu item? Just copying an existing item with a new object number?
 
Upvote 0 Downvote
Yes. I would like to mimic the functionality of the "Copy Record" button in the POS Configurator.
 
Upvote 0 Downvote
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.

Code: 
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
 
Upvote 0 Downvote
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:

Code: 
        [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:

Code: 
        [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.
 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

GeminiBoy
  • Locked
  • Question Question
Micros 3700 RES 5.7.7 Invalid Version Response
Replies
1
Views
819
DatFatMan
DatFatMan
B
  • Locked
  • Question Question
MICROS RES 3700 - Desperate!
Replies
4
Views
2K
bdavidson024
B
azrobert
  • Question Question
Micros Res3700 Report / SQL
Replies
1
Views
270
goodbody
goodbody
RickysRestaurants
  • Locked
  • Question Question
Micros 3700 Res 4.11
Replies
2
Views
801
RickysRestaurants
RickysRestaurants
Kurosakis
  • Locked
  • Question Question
Micros 3700 backoffice pc in different networks
Replies
2
Views
670
myu0516
myu0516

Part and Inventory Search

Sponsor

Back
Top