4.0 to 5.0 Offers Migration

Data migration for BLC_OFFERS (Enterprise)

-- BLC_OFFER_TEMPLATE
INSERT INTO BLC_OFFER_TEMPLATE (OFFER_TEMPLATE_ID, NAME, SEQUENCE) VALUES (-14110, 'Order Discount', 8.000000);
INSERT INTO BLC_OFFER_TEMPLATE (OFFER_TEMPLATE_ID, NAME, SEQUENCE) VALUES (-14109, 'Item Discount', 7.000000);
INSERT INTO BLC_OFFER_TEMPLATE (OFFER_TEMPLATE_ID, NAME, SEQUENCE) VALUES (-14108, 'Free Shipping', 6.000000);
INSERT INTO BLC_OFFER_TEMPLATE (OFFER_TEMPLATE_ID, NAME, SEQUENCE) VALUES (-14107, 'Amount Off Order', 4.000000);
INSERT INTO BLC_OFFER_TEMPLATE (OFFER_TEMPLATE_ID, NAME, SEQUENCE) VALUES (-14106, 'Percent Off Order', 3.000000);
INSERT INTO BLC_OFFER_TEMPLATE (OFFER_TEMPLATE_ID, NAME, SEQUENCE) VALUES (-14105, 'Buy x Get y (BOGO)', 5.000000);
INSERT INTO BLC_OFFER_TEMPLATE (OFFER_TEMPLATE_ID, NAME, SEQUENCE) VALUES (-14103, 'Shipping Discount', 9.000000);
INSERT INTO BLC_OFFER_TEMPLATE (OFFER_TEMPLATE_ID, NAME, SEQUENCE) VALUES (-14102, 'Amount Off Item(s)', 2.000000);
INSERT INTO BLC_OFFER_TEMPLATE (OFFER_TEMPLATE_ID, NAME, SEQUENCE) VALUES (-14101, 'Percent Off Item(s)', 1.000000);
INSERT INTO BLC_OFFER_TEMPLATE (OFFER_TEMPLATE_ID, NAME, SEQUENCE) VALUES (-14000, 'Custom Offer', 9999.000000);

Update Offer Template in BLC_OFFERS (MySQL Enterprise)

-- UPDATE BLC_OFFER TEMPLATE_ID IN BLC_OFFER_TEMPLATE
UPDATE BLC_OFFER SET OFFER_TEMPLATE_ID = (select OFFER_TEMPLATE_ID FROM BLC_OFFER_TEMPLATE WHERE NAME = 'Buy x Get y (BOGO)') AND OFFER_TYPE = 'ORDER_ITEM' AND OFFER_ID IN (SELECT BLC_OFFER2.OFFER_ID FROM (SELECT OFFER_ID, ARCHIVED FROM BLC_OFFER) AS BLC_OFFER2, BLC_QUAL_CRIT_OFFER_XREF WHERE BLC_OFFER2.OFFER_ID = BLC_QUAL_CRIT_OFFER_XREF.OFFER_ID AND (BLC_OFFER2.ARCHIVED = 'N' OR BLC_OFFER2.ARCHIVED IS NULL));
UPDATE BLC_OFFER SET OFFER_TEMPLATE_ID = (select OFFER_TEMPLATE_ID FROM BLC_OFFER_TEMPLATE WHERE NAME = 'Percent Off Item(s)') WHERE OFFER_TYPE = 'ORDER_ITEM' AND OFFER_DISCOUNT_TYPE = 'PERCENT_OFF' AND OFFER_ID NOT IN (SELECT BLC_OFFER2.OFFER_ID FROM (SELECT OFFER_ID, ARCHIVED FROM BLC_OFFER) AS BLC_OFFER2, BLC_QUAL_CRIT_OFFER_XREF WHERE BLC_OFFER2.OFFER_ID = BLC_QUAL_CRIT_OFFER_XREF.OFFER_ID AND (BLC_OFFER2.ARCHIVED = 'N' OR BLC_OFFER2.ARCHIVED IS NULL));
UPDATE BLC_OFFER SET OFFER_TEMPLATE_ID = (select OFFER_TEMPLATE_ID FROM BLC_OFFER_TEMPLATE WHERE NAME = 'Amount Off Item(s)') WHERE OFFER_TYPE = 'ORDER_ITEM' AND OFFER_DISCOUNT_TYPE = 'AMOUNT_OFF' AND OFFER_ID NOT IN (SELECT BLC_OFFER2.OFFER_ID FROM (SELECT OFFER_ID, ARCHIVED FROM BLC_OFFER) AS BLC_OFFER2, BLC_QUAL_CRIT_OFFER_XREF WHERE BLC_OFFER2.OFFER_ID = BLC_QUAL_CRIT_OFFER_XREF.OFFER_ID AND (BLC_OFFER2.ARCHIVED = 'N' OR BLC_OFFER2.ARCHIVED IS NULL));
UPDATE BLC_OFFER SET OFFER_TEMPLATE_ID = (select OFFER_TEMPLATE_ID FROM BLC_OFFER_TEMPLATE WHERE NAME = 'Amount Off Order') WHERE OFFER_TYPE = 'ORDER' AND OFFER_DISCOUNT_TYPE = 'AMOUNT_OFF' AND OFFER_ID NOT IN (SELECT BLC_OFFER2.OFFER_ID FROM (SELECT OFFER_ID, ARCHIVED FROM BLC_OFFER) AS BLC_OFFER2, BLC_QUAL_CRIT_OFFER_XREF WHERE BLC_OFFER2.OFFER_ID = BLC_QUAL_CRIT_OFFER_XREF.OFFER_ID AND (BLC_OFFER2.ARCHIVED = 'N' OR BLC_OFFER2.ARCHIVED IS NULL));

Update Offer Template in BLC_OFFERS (Oracle Enterprise)

merge into BLC_OFFER offer using (select OFFER_TEMPLATE_ID FROM BLC_OFFER_TEMPLATE WHERE NAME = 'Buy x Get y (BOGO)') sq on (OFFER_TYPE = 'ORDER_ITEM' AND OFFER_ID IN (SELECT BLC_OFFER2.OFFER_ID FROM (SELECT OFFER_ID, ARCHIVED FROM BLC_OFFER) BLC_OFFER2, BLC_QUAL_CRIT_OFFER_XREF WHERE BLC_OFFER2.OFFER_ID = BLC_QUAL_CRIT_OFFER_XREF.OFFER_ID AND (BLC_OFFER2.ARCHIVED = 'N' OR BLC_OFFER2.ARCHIVED IS NULL))) when matched then update set offer.OFFER_TEMPLATE_ID = sq.OFFER_TEMPLATE_ID;
merge into BLC_OFFER offer using (select OFFER_TEMPLATE_ID FROM BLC_OFFER_TEMPLATE WHERE NAME = 'Percent Off Item(s)') sq on (OFFER_TYPE = 'ORDER_ITEM' AND OFFER_DISCOUNT_TYPE = 'PERCENT_OFF' AND OFFER_ID NOT IN (SELECT BLC_OFFER2.OFFER_ID FROM (SELECT OFFER_ID, ARCHIVED FROM BLC_OFFER) BLC_OFFER2, BLC_QUAL_CRIT_OFFER_XREF WHERE BLC_OFFER2.OFFER_ID = BLC_QUAL_CRIT_OFFER_XREF.OFFER_ID AND (BLC_OFFER2.ARCHIVED = 'N' OR BLC_OFFER2.ARCHIVED IS NULL))) when matched then update set offer.OFFER_TEMPLATE_ID = sq.OFFER_TEMPLATE_ID;
merge into BLC_OFFER offer using (select OFFER_TEMPLATE_ID FROM BLC_OFFER_TEMPLATE WHERE NAME = 'Amount Off Item(s)') sq on (OFFER_TYPE = 'ORDER_ITEM' AND OFFER_DISCOUNT_TYPE = 'AMOUNT_OFF' AND OFFER_ID NOT IN (SELECT BLC_OFFER2.OFFER_ID FROM (SELECT OFFER_ID, ARCHIVED FROM BLC_OFFER) BLC_OFFER2, BLC_QUAL_CRIT_OFFER_XREF WHERE BLC_OFFER2.OFFER_ID = BLC_QUAL_CRIT_OFFER_XREF.OFFER_ID AND (BLC_OFFER2.ARCHIVED = 'N' OR BLC_OFFER2.ARCHIVED IS NULL))) when matched then update set offer.OFFER_TEMPLATE_ID = sq.OFFER_TEMPLATE_ID;
merge into BLC_OFFER offer using (select OFFER_TEMPLATE_ID FROM BLC_OFFER_TEMPLATE WHERE NAME = 'Amount Off Order') sq on (OFFER_TYPE = 'ORDER_ITEM' AND OFFER_DISCOUNT_TYPE = 'AMOUNT_OFF' AND OFFER_ID NOT IN (SELECT BLC_OFFER2.OFFER_ID FROM (SELECT OFFER_ID, ARCHIVED FROM BLC_OFFER) BLC_OFFER2, BLC_QUAL_CRIT_OFFER_XREF WHERE BLC_OFFER2.OFFER_ID = BLC_QUAL_CRIT_OFFER_XREF.OFFER_ID AND (BLC_OFFER2.ARCHIVED = 'N' OR BLC_OFFER2.ARCHIVED IS NULL))) when matched then update set offer.OFFER_TEMPLATE_ID = sq.OFFER_TEMPLATE_ID;