------------------------------------------------------------------------
-- USER DATA INITIALIZATION
------------------------------------------------------------------------

CREATE TABLE tt_change_product_category_2018
(
    old_gender_name character varying(100),
    old_category_name character varying(100),
    old_type_name character varying(100),
    new_level0 character varying(100),
    new_level1 character varying(100),
    new_level2 character varying(100),
    new_level3 character varying(100)
) WITH (
    OIDS=FALSE
);

INSERT INTO tt_change_product_category_2018 (old_gender_name, old_category_name, old_type_name, new_level0, new_level1, new_level2, new_level3) VALUES
('LADIES APPAREL','CASUAL LADIES','BLOUSE CASUAL LADIES','A','A01','A0101','A010104'),
('LADIES APPAREL','CASUAL LADIES','BLOUSE FORMAL LADIES','A','A01','A0101','A010104'),
('LADIES APPAREL','CASUAL LADIES','DRESS CASUAL LADIES','A','A01','A0104','A010401'),
('LADIES APPAREL','CASUAL LADIES','JEANS CASUAL LADIES','A','A01','A0103','A010304'),
('LADIES APPAREL','CASUAL LADIES','JEGGING CASUAL LADIES','A','A01','A0103','A010305'),
('LADIES APPAREL','CASUAL LADIES','JUMPSUIT','A','A01','A0104','A010403'),
('LADIES APPAREL','CASUAL LADIES','LADIES CARDIGAN','A','A01','A0108','A010802'),
('LADIES APPAREL','CASUAL LADIES','LADIES TSHIRT / TOP KNIT','A','A01','A0101','A010107'),
('LADIES APPAREL','CASUAL LADIES','LONG PANTS CASUAL LADIES','A','A01','A0103','A010305'),
('LADIES APPAREL','CASUAL LADIES','SKIRT CASUAL LADIES','A','A01','A0102','A010201'),
('LADIES APPAREL','CASUAL TEEN / MISSY','BLOUSE / TOP WOVEN','D','D01','D0106','D010604'),
('LADIES APPAREL','CASUAL TEEN / MISSY','DRESS','D','D01','D0108','D010801'),
('LADIES APPAREL','CASUAL TEEN / MISSY','LADIES TSHIRT / TOP KNIT','D','D01','D0106','D010607'),
('LADIES APPAREL','FORMAL TEEN & LADIES','BLOUSE CASUAL LADIES','D','D01','D0106','D010604'),
('LADIES APPAREL','FORMAL TEEN & LADIES','BLOUSE FORMAL LADIES','D','D01','D0106','D010604'),
('LADIES APPAREL','FORMAL TEEN & LADIES','CASUAL BLAZER','D','D01','D0106','D010606'),
('LADIES APPAREL','FORMAL TEEN & LADIES','DRESS FORMAL LADIES','D','D01','D0108','D010801'),
('LADIES APPAREL','FORMAL TEEN & LADIES','JEANS CASUAL LADIES','D','D01','D0107','D010704'),
('LADIES APPAREL','FORMAL TEEN & LADIES','SET TOP & BOTTOM','D','D01','D0108','D010805'),
('LADIES APPAREL','FORMAL TEEN & LADIES','TUNIK MOSLEM LADIES','C','C01','C0108','C010802'),
('LADIES APPAREL','GIRL MOSLEM','GIRLS MOSLEM TUNIC','C','C01','C0108','C010802'),
('LADIES APPAREL','LADIES BATIK','BLOUSE LADIES BATIK','A','A01','A0105','A010506'),
('LADIES APPAREL','LADIES BIG SIZE & MATERNITY','BIG SIZE TOP LADIES','A','A11','A0110','A011001'),
('LADIES APPAREL','LADIES MOSLEM','GAMIS MOSLEM LADIES','C','C01','C0104','C010403'),
('LADIES APPAREL','LADIES MOSLEM','LONG PANTS','C','C01','C0103','C010302'),
('LADIES APPAREL','LADIES MOSLEM','MAXI DRESS','C','C01','C0104','C010403'),
('LADIES APPAREL','LADIES MOSLEM','MOSLEM BLOUSE','C','C01','C0101','C010103'),
('LADIES APPAREL','LADIES MOSLEM','OUTER','C','C01','C0106','C010601'),
('LADIES APPAREL','LADIES MOSLEM','SET MOSLEM GAMIS','C','C01','C0105','C010501'),
('LADIES APPAREL','LADIES MOSLEM','TUNIK MOSLEM LADIES','C','C01','C0101','C010102'),
('LADIES APPAREL','PAKAIAN (APPAREL)','LADIES TSHIRT / TOP KNIT','A','A01','A0101','A010107'),
('LADIES APPAREL','PAKAIAN (APPAREL)','LONG PANTS CASUAL LADIES','A','A01','A0103','A010305'),
('LADIES APPAREL','PAKAIAN (APPAREL)','PAKAIAN HAMIL WANITA (MATERNITY)','A','A11','A0110','A011001'),
('LADIES APPAREL','PAKAIAN (APPAREL)','TERUSAN WANITA (DRESS)','A','A01','A0104','A010401'),
('LADIES APPAREL','PAKAIAN (APPAREL)','TUNIK MOSLEM LADIES','C','C01','C0101','C010102'),
('ACCESSORIES','ACCESSORIES','LADIES FASHION ACCESSORIES','A','A01','A0501','A050101'),
('ACCESSORIES','BEAUTY TOOLS','BEAUTY TOOLS','F','F11','F0115','F011501'),
('ACCESSORIES','BELT','BOY ACCESSORIES','D','D01','D0401','D040101'),
('ACCESSORIES','BELT','LADIES FASHION BELT','A','A01','A0505',''),
('ACCESSORIES','BELT','MENS FORMAL BELT','B','B01','B0504',''),
('ACCESSORIES','BOY WATCHES','BOYS WATCH','D','D01','D0401','D040103'),
('ACCESSORIES','GIRL ACCESSORIES','GIRL ACCESORIES','D','D01','D0401','D040101'),
('ACCESSORIES','JEWELRY','LADIES FASHION ACCESSORIES','A','A01','A0503','A050301'),
('ACCESSORIES','JEWELRY','NECKLACE','A','A01','A0503','A050301'),
('ACCESSORIES','LADIES ACCESSORIES','BEAUTY TOOLS','F','F11','F0115','F011501'),
('ACCESSORIES','LADIES ACCESSORIES','BRACELET / BANGLE','A','A01','A0503','A050302'),
('ACCESSORIES','LADIES ACCESSORIES','BROS','A','A01','A0503','A050305'),
('ACCESSORIES','LADIES ACCESSORIES','EARRING','A','A01','A0503','A050304'),
('ACCESSORIES','LADIES ACCESSORIES','JEWELRY SET','A','A01','A0503','A050306'),
('ACCESSORIES','LADIES ACCESSORIES','LADIES PASHMINA','A','A01','A0502',''),
('ACCESSORIES','LADIES ACCESSORIES','NECKLACE','A','A01','A0503','A050301'),
('ACCESSORIES','LADIES ACCESSORIES','RING','A','A01','A0503','A050303'),
('ACCESSORIES','LADIES SUNGLASSES','LADIES FASHION SUNGLASS','A','A01','A0501','A050101'),
('ACCESSORIES','LADIES WATCHES','LADIES FASHION WATCH','A','A01','A0401',''),
('ACCESSORIES','LADIES WATCHES','LADIES FORMAL WATCH','A','A01','A0401',''),
('ACCESSORIES','MENS SUNGLASSES','MENS FASHION SUNGLASS','B','B01','B0501',''),
('ACCESSORIES','MENS WATCHES','MENS FASHION WATCH','B','B01','B0401',''),
('ACCESSORIES','MENS WATCHES','MENS FORMAL WATCH','B','B01','B0401',''),
('ACCESSORIES','SOUVENIR','KITCHEN LINEN & PLACEMAT','H','H01','H0303','H030301'),
('ACCESSORIES','SOUVENIR','MENS CAP','B','B01','B0505',''),
('ACCESSORIES','SUN GLASSES','LADIES','A','A01','A0501','A050101'),
('BAG','KIDS BAG','SCHOOL BAG GIRL','D','D01','D0301','D030101'),
('BAG','LADIES BAG','CASUAL BAG','A','A01','A0301',''),
('BAG','LADIES BAG','FASHION BAG','A','A01','A0301',''),
('BAG','LADIES BAG','LADIES FASHION BAGS','A','A01','A0301',''),
('BAG','LADIES BAGS','BACKPACK','A','A01','A0305',''),
('BAG','LADIES BAGS','BAG SET','A','A01','A0301',''),
('BAG','LADIES BAGS','CASUAL BAG','A','A01','A0301',''),
('BAG','LADIES BAGS','COSMETIC BAG','A','A01','A0307',''),
('BAG','LADIES BAGS','LADIES FASHION BAGS','A','A01','A0301',''),
('BAG','LADIES BAGS','LADIES FASHION WALLET','A','A01','A0306',''),
('BAG','LADIES BAGS','LADIES PARTY BAGS','A','A01','A0304',''),
('BAG','LADIES BAGS','SHOPPING/GIFT BAG','A','A11','A0310',''),
('BAG','LADIES WALLET','COSMETIC BAG','A','A01','A0307',''),
('BAG','LADIES WALLET','WALLET','A','A01','A0306',''),
('BAG','MEN BAG','CASUAL BAG','B','B01','B0301',''),
('BAG','MENS BAGS','BACKPACK','B','B01','B0301',''),
('BAG','MENS BAGS','CASUAL BAG','B','B01','B0301',''),
('BAG','MENS WALLET','WALLET','B','B01','B0307','B030701'),
('SHOES','KIDS FOOTWEAR','BOY CASUAL SHOES','D','D01','D0201','D020101'),
('SHOES','KIDS FOOTWEAR','CASUAL SANDAL','D','D01','D0201','D020103'),
('SHOES','KIDS FOOTWEAR','CASUAL SHOES','D','D01','D0201','D020101'),
('SHOES','KIDS FOOTWEAR','GIRL CASUAL SHOES','D','D01','D0201','D020101'),
('SHOES','LADIES FOOTWEAR','CASUAL SANDAL','A','A01','A0202',''),
('SHOES','LADIES FOOTWEAR','CASUAL SHOES','A','A01','A0201',''),
('SHOES','LADIES FOOTWEAR','FORMAL SHOES','A','A01','A0201',''),
('SHOES','LADIES FOOTWEAR','LADIES SHOES DAILY','A','A01','A0201',''),
('SHOES','LADIES FOOTWEAR','LADIES SHOES FORMAL','A','A01','A0201',''),
('SHOES','LADIES FOOTWEAR','PARTY SHOES','A','A01','A0201',''),
('SHOES','LADIES SHOES','BRA','A','A01','A0201',''),
('SHOES','MEN FOOTWEAR','CASUAL SANDAL','B','B01','B0202',''),
('SHOES','MEN FOOTWEAR','CASUAL SHOES','B','B01','B0201',''),
('SHOES','MEN FOOTWEAR','FORMAL SHOES','B','B01','B0207',''),
('SHOES','MEN FOOTWEAR','SPORT SHOES','P','P01','P0401',''),
('KOMPUTER & LAPTOP','LAPTOP','LAPTOP UMUM','J','J01','J0101',''),
('COSMETIC & HEALTH','COSMETIC','BEAUTY TOOLS','F','F11','F0115','F011501'),
('COSMETIC & HEALTH','COSMETIC','BLUSH ON','F','F01','F0101','F010104'),
('COSMETIC & HEALTH','COSMETIC','BODY LOTION','F','F01','F0107','F010706'),
('COSMETIC & HEALTH','COSMETIC','EYES','F','F01','F0101','F010101'),
('COSMETIC & HEALTH','COSMETIC','FACIAL FOAM','F','F11','F0110',''),
('COSMETIC & HEALTH','COSMETIC','FRAGRANCE','F','F11','F0111','F011101'),
('COSMETIC & HEALTH','COSMETIC','LADIES HAIR CARE COSMETIC','F','F11','F0113','F011301'),
('COSMETIC & HEALTH','COSMETIC','LADIES SKIN CARE COSMETIC','F','F01','F0107','F010701'),
('COSMETIC & HEALTH','COSMETIC','LIP','F','F01','F0109',''),
('COSMETIC & HEALTH','COSMETIC','PAKET KOSMETIK','F','F01','F0102',''),
('COSMETIC & HEALTH','COSMETIC','TALCUM POWDER','F','F01','F0107','F010701'),
('COSMETIC & HEALTH','HEALTH TOOLS','HEALTH EQUIPMENT','G','G01','G0101',''),
('COSMETIC & HEALTH','KOSMETIK','LADIES SKIN CARE COSMETIC','F','F01','F0107','F010701'),
('COSMETIC & HEALTH','SUPLEMENT & HEALTH','HEALTH EQUIPMENT','G','G01','G0101',''),
('COSMETIC & HEALTH','SUPLEMENT & HEALTH','SUPLEMENT','G','G01','G0102',''),
('MEN APPAREL','MEN BATIK','MEN BATIK SHIRT L/S','B','B01','B0103','B010302'),
('MEN APPAREL','MEN BATIK','MEN BATIK SHIRT S/S','B','B01','B0103','B010302'),
('MEN APPAREL','MEN CASUAL & COTEMP','MEN CASUAL DENIM','B','B01','B0102','B010202'),
('MEN APPAREL','MEN CASUAL & COTEMP','MEN CASUAL JACKET & OUTWEAR','B','B01','B0106','B010603'),
('MEN APPAREL','MEN CASUAL & COTEMP','MEN CASUAL POLO SHIRT','B','B01','B0101','B010105'),
('MEN APPAREL','MEN CASUAL & COTEMP','MEN CASUAL SHIRT','B','B01','B0101','B010104'),
('MEN APPAREL','MEN CASUAL & COTEMP','MEN CASUAL SHORT PANTS/BERMUDA','B','B01','B0102','B010201'),
('MEN APPAREL','MEN CASUAL & COTEMP','MEN CASUAL T-SHIRT','B','B01','B0101','B010104'),
('MEN APPAREL','MEN FOOTWEAR','CASUAL SHOES','B','B01','B0201',''),
('MEN APPAREL','MEN FORMAL','MEN FORMAL TROUSER/PANTS','B','B01','B0102','B010203'),
('MEN APPAREL','MEN MOSLEM','MEN MOSLEM KOKO','C','C01','C0101','C010104'),
('MEN APPAREL','MEN UNDERWEAR','MEN CASUAL POLO SHIRT','B','B01','B0101','B010105'),
('MEN APPAREL','PAKAIAN','MEN MOSLEM KOKO','C','C01','C0101','C010104'),
('HANDPHONE AND TABLET','AKSESORIS HANDPHONE & TABLET','ASSESORIES ELEKTRONIK','I','I01','I0201',''),
('HANDPHONE AND TABLET','HANDPHONE','HANDPHONE','I','I01','I0101',''),
('LADIES INTIMATE & SPORTSWEAR','LADIES LINGERIE & SLEEPWEAR','BRA - LADIES LINGERIE','A','A01','A0106','A010601'),
('LADIES INTIMATE & SPORTSWEAR','LADIES LINGERIE & SLEEPWEAR','KORSET','A','A01','A0106','A010603'),
('LADIES INTIMATE & SPORTSWEAR','LADIES LINGERIE & SLEEPWEAR','SINGLET /TANK TOP- LADIES LINGERIE','A','A01','A0106','A010601'),
('LADIES INTIMATE & SPORTSWEAR','LADIES SPORTWEAR','LADIES AEROBIC & TRAINING','P','P01','P0101','P010101'),
('LADIES INTIMATE & SPORTSWEAR','PAKAIAN (APPAREL)','PAKAIAN DALAM WANITA (INTIMATE)','A','A01','A0106','A010601'),
('CHILDREN','BOY CASUAL & FORMAL','BOY CASUAL JACKET','D','D01','D0101','D010104'),
('CHILDREN','BOY CASUAL & FORMAL','BOY CASUAL & T.SHIRT ROUND','D','D01','D0101','D010103'),
('CHILDREN','BOY CASUAL & FORMAL','BOY CASUAL / T-SHIRT POLO','D','D01','D0101','D010103'),
('CHILDREN','BOY CASUAL & FORMAL','BOY CASUAL SWEATER','D','D01','D0101','D010104'),
('CHILDREN','BOY CASUAL & FORMAL','MEN UNDERWEAR SHORT','D','D01','D0104','D010401'),
('CHILDREN','GIRL CASUAL & GIRL FORMAL','GIRL CASUAL & GIRL FORMAL BLOUSE','D','D01','D0106','D010602'),
('CHILDREN','GIRL CASUAL & GIRL FORMAL','GIRL CASUAL & GIRL FORMAL DRESS CASUAL','D','D01','D0108','D010801'),
('CHILDREN','GIRL CASUAL & GIRL FORMAL','GIRL CASUAL & GIRL FORMAL PARTY DRESS','D','D01','D0108','D010801'),
('CHILDREN','GIRL CASUAL & GIRL FORMAL','GIRL CASUAL & GIRL FORMAL T.SHIRT','D','D01','D0106','D010607'),
('CHILDREN','GIRL CASUAL & GIRL FORMAL','LEGGING','D','D01','D0107','D010704'),
('CHILDREN','GIRL CASUAL & GIRL FORMAL','SET TAS+GENDONGAN+BLANKET','D','D05','D0513',''),
('BABY','BABY PRODUCT','BABY ACCSESSORIES','D','D05','D0501',''),
('BABY','BABY PRODUCT','BABY CARRIER & SLINGS','D','D05','D0513',''),
('BABY','BABY PRODUCT','GENDONGAN BABY','D','D05','D0513',''),
('BABY','BABY PRODUCT','NURSERY','D','D05','D0501',''),
('BABY','INFANT/ TODLER APPAREL','DRESS','D','D01','D0112','D011206'),
('BABY','INFANT/ TODLER APPAREL','LAYYETTE','D','D01','D0112','D011201'),
('BABY','INFANT/ TODLER APPAREL','PANTS INFANT','D','D01','D0112','D011202'),
('BABY','INFANT/ TODLER APPAREL','SET T-SHIRT','D','D01','D0112','D011203'),
('BABY','INFANT/ TODLER APPAREL','T-SHIRT INFANT','D','D01','D0112','D011201'),
('ACCESSORIES MUSLIM','KERUDUNG','BASIC','C','C01','C0105','C010501'),
('ACCESSORIES MUSLIM','KERUDUNG','FASHION','C','C01','C0105','C010501'),
('ACCESSORIES MUSLIM','KERUDUNG','HIJAB','C','C01','C0105','C010501'),
('ACCESSORIES MUSLIM','KERUDUNG','KERUDUNG','C','C01','C0105','C010501'),
('ACCESSORIES MUSLIM','MEN ACCESSORIES','MENS SOCKS','C','C02','C0202',''),
('ACCESSORIES MUSLIM','SOCKS & MANSET','LADIES','C','C02','C0202',''),
('TOYS/GAMES & SPORT','TOYS','TOYS','E','E01','E0101',''),
('EDUTAINMENT,BOOKS,STATIONARY','EDUTAINMENT','SCHOOL STATIONARY','E','E04','E0401',''),
('EDUTAINMENT,BOOKS,STATIONARY','EDUTAINMENT','TOYS','E','E01','E0101',''),
('AUTOMOTIVE','AKSESORIS MOBIL','CAR DAN MOTOR ACCESSORIES','K','K02','K0201','');

-- Cek apakah ada new_category yang diberikan oleh user yang tidak ada di m_product_ctgr_tree
SELECT * FROM tt_change_product_category_2018 A
WHERE NOT EXISTS (
    SELECT 1 FROM m_product_ctgr_tree B
    WHERE CASE WHEN
        A.new_level3 = '' THEN A.new_level2 = B.product_ctgr_tree_code
        ELSE A.new_level3 = B.product_ctgr_tree_code
        END
)

------------------------------------------------------------------------
-- DATA GATHERING
------------------------------------------------------------------------

-- Mapping master product dengan new_ctgr yang diberikan user berdasarkan Gender, Category dan Type
-- Jika tidak ada, maka kolom new_level3 akan berisi null
CREATE TABLE tt_product_new_ctgr_2018 AS
SELECT A.product_id, A.product_code, A.product_name,
    UPPER(B.value) AS type, UPPER(C.value) AS gender, UPPER(D.product_ctgr_tree_code) AS category_code,
    UPPER(D.product_ctgr_tree_name) AS category_name,
    CASE WHEN E.new_level3 = '' THEN E.new_level2 ELSE E.new_level3 END AS new_level3
from m_product A -- 77090
INNER JOIN m_product_custom_field B ON A.product_id = B.product_id AND B.product_attr_code = 'TYPE' AND B.active = 'Y' -- 76838
INNER JOIN m_product_custom_field C ON A.product_id = C.product_id AND C.product_attr_code = 'GENDER' AND C.active = 'Y' -- 75338
INNER JOIN m_product_ctgr_tree D ON A.sub_ctgr_product_id = D.product_ctgr_tree_id -- 75338
LEFT JOIN tt_change_product_category_2018 E ON UPPER(B.value) = UPPER(E.old_type_name) AND UPPER(C.value) = UPPER(E.old_gender_name) AND UPPER(D.product_ctgr_tree_name) = UPPER(E.old_category_name)
--ORDER BY A.product_code

-- Cari apakah ada kombinasi Gender, Category dan Type yang tidak ada di m_product
CREATE TABLE tt_not_found_product_category_2018 AS
SELECT * FROM tt_change_product_category_2018 E
WHERE NOT EXISTS (SELECT 1 FROM m_product A -- 77090
INNER JOIN m_product_custom_field B ON A.product_id = B.product_id AND B.product_attr_code = 'TYPE' AND B.active = 'Y' -- 76838
INNER JOIN m_product_custom_field C ON A.product_id = C.product_id AND C.product_attr_code = 'GENDER' AND C.active = 'Y' -- 75338
INNER JOIN m_product_ctgr_tree D ON A.sub_ctgr_product_id = D.product_ctgr_tree_id -- 75338
WHERE UPPER(B.value) = UPPER(E.old_type_name) AND UPPER(C.value) = UPPER(E.old_gender_name) AND UPPER(D.product_ctgr_tree_name) = UPPER(E.old_category_name))

-- Mendapatkan daftar kombinasi Gender, Category dan Type yang tidak ada di vw_product_catalog_cogs
SELECT * FROM tt_change_product_category_2018 A
WHERE NOT EXISTS (SELECT 1 FROM vw_product_catalog_cogs B WHERE UPPER(A.old_gender_name) = UPPER(B.gender) AND UPPER(A.old_category_name) = UPPER(B.product_ctgr_tree_name) AND UPPER(A.old_type_name) = UPPER(B.type))

------------------------------------------------------------------------
-- BACKUP SCRIPT
------------------------------------------------------------------------
CREATE TABLE backup_m_product_20180703 AS
SELECT * FROM m_product A
WHERE EXISTS (SELECT 1 FROM tt_product_new_ctgr_2018 B WHERE B.new_level3 IS NOT NULL AND A.product_id = B.product_id);

CREATE TABLE backup_m_product_custom_20180703 AS
SELECT * FROM m_product_custom A
WHERE EXISTS (SELECT 1 FROM tt_product_new_ctgr_2018 B WHERE B.new_level3 IS NOT NULL AND A.product_id = B.product_id);

CREATE TABLE backup_m_product_field_20180703 AS
SELECT * FROM m_product_custom_field A
WHERE A.product_attr_code IN ('GENDER', 'TYPE')
AND EXISTS (SELECT 1 FROM tt_product_new_ctgr_2018 B WHERE B.new_level3 IS NOT NULL AND A.product_id = B.product_id);

------------------------------------------------------------------------
-- UPDATE SCRIPT
------------------------------------------------------------------------
-- UPDATE gender_code m_product
UPDATE m_product
  SET
    ctgr_product_id = C.product_ctgr_tree_id,
    sub_ctgr_product_id =  C.product_ctgr_tree_id
FROM tt_product_new_ctgr_2018 B
INNER JOIN m_product_ctgr_tree C ON B.new_level3 = C.product_ctgr_tree_code AND C.active = 'Y'
WHERE A.product_id = B.product_id
AND EXISTS (SELECT 1 FROM backup_m_product_20180703 D WHERE A.product_id = D.product_id);

-- UPDATE gender_code m_product_custom
UPDATE m_product_custom A
  SET A.gender_code = C.product_ctgr_tree_code
FROM tt_product_new_ctgr_2018 B
INNER JOIN m_product_ctgr_tree C ON SUBSTR(B.new_level3,1,1) = C.product_ctgr_tree_code AND C.active = 'Y'
WHERE A.product_id = B.product_id
AND EXISTS (SELECT 1 FROM backup_m_product_custom_20180703 D WHERE A.product_id = D.product_id);

-- UPDATE GENDER product_custom_field
UPDATE m_product_custom_field A
  SET A.value = C.product_ctgr_tree_name
FROM tt_product_new_ctgr_2018 B
INNER JOIN m_product_ctgr_tree C ON SUBSTR(B.new_level3,1,1) = C.product_ctgr_tree_code AND C.active = 'Y'
WHERE A.product_attr_code = 'GENDER'
AND A.product_id = B.product_id
AND EXISTS (SELECT 1 FROM backup_m_product_field_20180703 D WHERE A.product_custom_field_id = D.product_custom_field_id AND A.product_attr_code = D.product_attr_code AND A.product_id = D.product_id);

-- UPDATE TYPE product_custom_field
UPDATE m_product_custom_field A
  SET A.value = C.product_ctgr_tree_name
FROM tt_product_new_ctgr_2018 B
INNER JOIN m_product_ctgr_tree C ON SUBSTR(B.new_level3,1,5) = C.product_ctgr_tree_code AND C.active = 'Y'
WHERE A.product_attr_code = 'TYPE'
AND A.product_id = B.product_id
AND EXISTS (SELECT 1 FROM backup_m_product_field_20180703 D WHERE A.product_custom_field_id = D.product_custom_field_id AND A.product_attr_code = D.product_attr_code AND A.product_id = D.product_id);

----------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------