Export/Import Menu Items for price changes 3

[mxap]

Greetings,
I am trying to export all my CURRENT menu items to excel or CSV file so that I can change the prices and Import back into micros 3700.
My micros is RES 3700 5.4 MR1.
I am using this command in "dbisql"
SELECT
def.mi_seq,
def.obj_num,
def.name_1,
prices.preset_amt_1,
prices.preset_amt_2,
prices.preset_amt_3,
prices.preset_amt_4,
prices.preset_amt_5,
prices.preset_amt_6,
prices.preset_amt_7,
prices.preset_amt_8,
prices.preset_amt_9,
prices.preset_amt_10
FROM
MICROS.mi_def def JOIN
MICROS.mi_price_def prices
ON def.mi_seq = prices.mi_seq
ORDER BY def.obj_num ASC;

OUTPUT TO 'C:\temp\Menu_Item_Prices.txt';

But the exported file has all old prices. Please see attached.the prices are not current. (see Attached screenshot form excel).
How do I export the menu items with the latest prices?
Second, how do I import back into SQL after changing the prices?
thank you, everyone.
mxap

 

[BootstrapperXx]

Take a look at the table with the SQL utility. Is there a field for active or a date for the latest?

Import would be similar with UPDATE INTO commands.

 

[mxap]

How and where do I check that table? Sorry, new with this SQL.
thanks

 

[Moregelen]

Menu items can have multiple price records. You will need to change your query; something like this should work:

WITH prices AS (
    SELECT *,
    ROW_NUMBER() OVER(PARTITION BY mi_seq ORDER BY effective_from DESC) AS rk
    FROM micros.mi_price_def WHERE effective_from <= NOW() AND (effective_to > NOW() OR effective_to IS NULL))
SELECT
    p.mi_seq,
    d.obj_num,
    d.name_1,
    p.preset_amt_1,
    p.preset_amt_2,
    p.preset_amt_3,
    p.preset_amt_4,
    p.preset_amt_5,
    p.preset_amt_6,
    p.preset_amt_7,
    p.preset_amt_8,
    p.preset_amt_9,
    p.preset_amt_10
FROM
    prices p
    JOIN micros.mi_def d ON p.mi_seq = d.mi_seq
WHERE p.rk = 1

 

[Moregelen]

To import it back in, you don't really need to care about updating inactive records. Just do something like:

DROP TABLE IF EXISTS #mi_price_def;

CREATE TABLE #mi_price_def (
    mi_seq SEQ_NUM,
    obj_num OBJ_NUM,
    name_1 NAME16,
    preset_amt_1 MONEY12,
    preset_amt_2 MONEY12,
    preset_amt_3 MONEY12,
    preset_amt_4 MONEY12,
    preset_amt_5 MONEY12,
    preset_amt_6 MONEY12,
    preset_amt_7 MONEY12,
    preset_amt_8 MONEY12,
    preset_amt_9 MONEY12,
    preset_amt_10 MONEY12
);

INPUT INTO #mi_price_def
FROM C:\temp\Menu_Item_Prices.txt
FORMAT TEXT;

UPDATE MICROS.mi_price_def SET
    target.preset_amt_1 = source.preset_amt_1,
    target.preset_amt_2 = source.preset_amt_2,
    target.preset_amt_3 = source.preset_amt_3,
    target.preset_amt_4 = source.preset_amt_4,
    target.preset_amt_5 = source.preset_amt_5,
    target.preset_amt_6 = source.preset_amt_6,
    target.preset_amt_7 = source.preset_amt_7,
    target.preset_amt_8 = source.preset_amt_8,
    target.preset_amt_9 = source.preset_amt_9,
    target.preset_amt_10 = source.preset_amt_10
FROM MICROS.mi_price_def target 
LEFT OUTER JOIN #mi_price_def source
ON target.mi_seq = source.mi_seq
WHERE target.mi_seq IN (SELECT mi_seq FROM #mi_price_def);


UPDATE MICROS.mi_def SET
    target.name_1 = source.name_1,
    target.obj_num = source.obj_num
FROM MICROS.mi_def target
LEFT OUTER JOIN #mi_price_def source
ON target.mi_seq = source.mi_seq
WHERE target.mi_seq IN (SELECT mi_seq FROM #mi_price_def);

 

[Moregelen]

I included importing changed names and object numbers, but that can be pretty dangerous. I recommend leaving off this piece:

UPDATE MICROS.mi_def SET
    target.name_1 = source.name_1,
    target.obj_num = source.obj_num
FROM MICROS.mi_def target
LEFT OUTER JOIN #mi_price_def source
ON target.mi_seq = source.mi_seq
WHERE target.mi_seq IN (SELECT mi_seq FROM #mi_price_def);

Also lol.. now that I look at what you posted originally, I realize that is my original export/import script you are using. Interesting to see it is still floating around. Using timed menu item price records is unusual so I didn't account for it in the original script.

 

[mxap]

Thanks, Moregelen.
I will give a go to your script this evening and see if this will help me.
thanks again.
Best

 

[mxap]

Hi Moregelen.
I was able to export to CSV file and changed three prices just to test.
Then, I Imported as per your script. but gave me en error (Attached)"Data on Line 1 Could not be Imported. Cannot covert 'mi_seq'to a integer. SQLCODE=-157, ODBC 3 State="07006".
Do you want to continue?
I clicked on continue.
went through but none of the raw got updated.
Here what I did: I opened the CSV file in Excel changed about 7 prices and saved the file as Tab delimited TXT and used that for import.
Where did I go wrong?
Thanks, again for all your help.
MXAP

 

[mxap]

My Dear Moregelen,
Please ignore the last post. I figured it out. I cannot make changes in Excel and save it as TXT. I saved it as CSV and it worked just fine.
Now see if I could create a batch file to run both export and import.
Again, thanks, a million. You saved me a lot of boring time at night.
MXAP

 

[mxap]

Sorry, Moregelen,
What is the command for output to C:\temp\menu_item.csv?
thanks
mxap

 

[mxap]

Sorry, again.
I figured out the output command.
Now need help in creating a batch file for both Export and Import.
thanks
MXAP

 

[JVPzoom]

Moregelen,
Thanks for providing that script - our challenge was exactly what this solves... EM stores with multiple price_defs for any given mi_seq. We were looking to get an idea of what was active and SLU'd in the system without having to go through and hit each button in each location. Correcting 0.00, deleting local promotions, 86'd items from 3 years past, etc. Our stores/we have struggled with menu cleanup and having upwards of 50-60 buttons under a given SLU when they should only need 10-20.

For anyone facing a similar challenge, here is my altered version with credit to Moregelen. We use menu levels for happy hour and those items are the only ones that use prices 2-10 (managed by menu item class), so I tweaked it not to show any pricing unless the item was actually on our happy hour. Also accounts for 86 status and any secondary SLU if an item has multiple ones.

WITH prices AS (
	SELECT *,
	ROW_NUMBER()
	OVER(PARTITION BY mi_seq ORDER BY effective_from DESC) AS rk
FROM micros.mi_price_def 
WHERE effective_from <= NOW() AND (effective_to > NOW() OR effective_to IS NULL))
SELECT
p.mi_seq,
d.obj_num AS item_id,
d.name_1 AS name,
s.name AS active_slu,
(SELECT z.name FROM micros.mi_slu_def z WHERE z.mi_slu_seq = d.mi_slu2_seq) AS '2nd_slu',
p.preset_amt_1 AS reg_price,
(CASE WHEN (d.mi_type_seq IN (SELECT mi_type_seq FROM micros.mi_type_class_def WHERE ob_mi19_enable_multi_prices='T')) THEN p.preset_amt_2 ELSE NULL END) AS HH_price,
CASE WHEN e.ob_mi32_out_of_mi='F' THEN NULL ELSE '86d' END AS '86_status',
p.effective_to
FROM
prices p
JOIN micros.mi_def d ON p.mi_seq = d.mi_seq
JOIN micros.mi_status e ON p.mi_seq = e.mi_seq
JOIN micros.mi_slu_def s ON d.mi_slu_seq = s.mi_slu_seq
WHERE p.rk = 1 AND d.mi_slu_seq IS NOT NULL
ORDER BY s.name ASC, d.name_1 ASC;