SELECT Item AS Item, Each_Barcode AS "Each Barcode", Description AS "Description", Each_Length AS "Each Length", Each_Length_CM AS "Each Length CM", Each_Width AS "Each Width", Each_Width_CM AS "Each Width CM", Each_Height AS "Each Height", Each_Height_CM AS "Each Height CM", Each_Weight AS "Each Weight", Each_Weight_Grams AS "Each Weight Grams", Top30Rank AS "Top30Rank", Specification AS "Specification", UN3091 AS "UN3091", UN3481 AS "UN3481", UN1170 AS "UN1170", Prop65 AS "Prop65", CountryOfOrigin AS "CountryOfOrigin", FormalName AS "FormalName", BatteriesIncluded AS "BatteriesIncluded", BatteryLife AS "BatteryLife", BatteryTypeUsed AS "BatteryTypeUsed", ExtraBatteriesIncluded AS "ExtraBatteriesIncluded", BulletPoint1 AS "BulletPoint1", BulletPoint2 AS "BulletPoint2", BulletPoint3 AS "BulletPoint3", BulletPoint4 AS "BulletPoint4", BulletPoint5 AS "BulletPoint5", ColorsAvailable AS "ColorsAvailable", FeelingOrSensation AS "FeelingOrSensation", Functions AS "Functions", Latexphthalatefree AS "Latexphthalatefree", Material AS "Material", OneSizeFitsMost AS "OneSizeFitsMost", ProductDescriptionShort AS "ProductDescriptionShort", CUSTOMRECORD_MM_PRODUCT_SPEC.custrecord_mm_prod_desc_medium as "ProductDescriptionMedium", ProductDescriptionLong AS "ProductDescriptionLong", ProductType AS "ProductType", RetailUnitPackType AS "RetailUnitPackType", Reusable AS "Reusable", Texture AS "Texture", VideoLink1 AS "VideoLink1", VideoLink2 AS "VideoLink2", VideoLink3 AS "VideoLink3", inner_barcode AS "inner barcode", BX_Alias_Unit AS "BX Alias Unit", BX_Alias_Description AS "BX Alias Description", BX_Inner_Quantity AS "BX Inner Quantity", BX_Alias_Length AS "BX Alias Length", BX_Alias_Length_CM AS "BX Alias Length CM", BX_Alias_Width AS "BX Alias Width", BX_Alias_Width_CM AS "BX Alias Width CM", BX_Alias_Height AS "BX Alias Height", BX_Alias_Height_CM AS "BX Alias Height CM", BX_Alias_Weight AS "BX Alias Weight", BX_Alias_Weight_Grams AS "BX Alias Weight Grams", master_barcode AS "master barcode", MC_Alias_Unit AS "MC Alias Unit", MC_Alias_Description AS "MC Alias Description", Master_Quantity AS "Master Quantity", Inners_Per_MC AS "Inners Per MC", MC_Alias_Length AS "MC Alias Length", MC_Alias_Length_CM AS "MC Alias Length CM", MC_Alias_Width AS "MC Alias Width", MC_Alias_Width_CM AS "MC Alias Width CM", MC_Alias_Height AS "MC Alias Height", MC_Alias_Height_CM AS "MC Alias Height CM", MC_Alias_Weight AS "MC Alias Weight", MC_Alias_Weight_Grams AS "MC Alias Weight Grams" FROM ( SELECT item.itemid AS Item, item.upccode AS Each_Barcode, item.description AS Description, MAX(item.custitem_length) AS Each_Length, ROUND(MAX(item.custitem_length * 2.54), 2) AS Each_Length_CM, MAX(item.custitem_width) AS Each_Width, MAX(item.custitem_width * 2.54) AS Each_Width_CM, MAX(item.custitem_height) AS Each_Height, MAX(item.custitem_height * 2.54) AS Each_Height_CM, MAX(item.custitem_weight) AS Each_Weight, ROUND(MAX(item.custitem_weight * 453.59237),2) AS Each_Weight_Grams, item.custitem_top30rank AS Top30Rank, item.custitem_mm_specifcation AS Specification, item.custitem_mm_un3091 AS UN3091, item.custitem_mm_un3481 AS UN3481, item.custitem_mm_un1170 AS UN1170, item.custitem_mm_prop_65 AS Prop65, CUSTOMRECORD_MM_PRODUCT_SPEC.id AS itemspecId, CUSTOMRECORD_MM_PRODUCT_SPEC.custrecord_mm_country_of_origin AS CountryOfOrigin, CUSTOMRECORD_MM_PRODUCT_SPEC.custrecord_mm_formal_name AS FormalName, CUSTOMRECORD_MM_PRODUCT_SPEC.custrecord_mm_batteries_included AS BatteriesIncluded, CUSTOMRECORD_MM_PRODUCT_SPEC.custrecord_mm_battery_life AS BatteryLife, CUSTOMRECORD_MM_PRODUCT_SPEC.custrecord_mm_battery_type_used AS BatteryTypeUsed, CUSTOMRECORD_MM_PRODUCT_SPEC.custrecord_mm_extra_batteries_included AS ExtraBatteriesIncluded, CUSTOMRECORD_MM_PRODUCT_SPEC.custrecord_mm_bullet_point_1 AS BulletPoint1, CUSTOMRECORD_MM_PRODUCT_SPEC.custrecord_mm_bullet_point_2 AS BulletPoint2, CUSTOMRECORD_MM_PRODUCT_SPEC.custrecord_mm_bullet_point_3 AS BulletPoint3, CUSTOMRECORD_MM_PRODUCT_SPEC.custrecord_mm_bullet_point_4 AS BulletPoint4, CUSTOMRECORD_MM_PRODUCT_SPEC.custrecord_mm_bullet_point_5 AS BulletPoint5, CUSTOMRECORD_MM_PRODUCT_SPEC.custrecord_mm_spec_colors_available AS ColorsAvailable, CUSTOMRECORD_MM_PRODUCT_SPEC.custrecord_mm_feeling_sensation AS FeelingOrSensation, CUSTOMRECORD_MM_PRODUCT_SPEC.custrecord_mm_functions AS Functions, CUSTOMRECORD_MM_PRODUCT_SPEC.custrecord_mm_latex_pht_free AS Latexphthalatefree, CUSTOMRECORD_MM_PRODUCT_SPEC.custrecord_mm_material AS Material, CUSTOMRECORD_MM_PRODUCT_SPEC.custrecord_mm_one_size_fits_most AS OneSizeFitsMost, CUSTOMRECORD_MM_PRODUCT_SPEC.custrecord_mm_product_desc_short AS ProductDescriptionShort, CUSTOMRECORD_MM_PRODUCT_SPEC.custrecord_mm_prod_desc_long AS ProductDescriptionLong, CUSTOMRECORD_MM_PRODUCT_SPEC.custrecord_mm_product_type AS ProductType, CUSTOMRECORD_MM_PRODUCT_SPEC.custrecord_mm_retail_pack_type AS RetailUnitPackType, CUSTOMRECORD_MM_PRODUCT_SPEC.custrecord_mm_reusable AS Reusable, CUSTOMRECORD_MM_PRODUCT_SPEC.custrecord_mm_texture AS Texture, CUSTOMRECORD_MM_PRODUCT_SPEC.custrecord_mm_video_link_1 AS VideoLink1, CUSTOMRECORD_MM_PRODUCT_SPEC.custrecord_mm_video_link_2 AS VideoLink2, CUSTOMRECORD_MM_PRODUCT_SPEC.custrecord_mm_video_link_3 AS VideoLink3, MAX(CASE WHEN CUSTOMRECORD_WMSSE_SKU_ALIAS.custrecord_wmsse_alias_desc LIKE '%BX%' THEN CUSTOMRECORD_WMSSE_SKU_ALIAS.name END) AS inner_barcode, MAX(CASE WHEN CUSTOMRECORD_WMSSE_SKU_ALIAS.custrecord_wmsse_alias_desc LIKE '%BX%' THEN BUILTIN.DF( CUSTOMRECORD_WMSSE_SKU_ALIAS.custrecord_wms_alias_unit ) END ) AS BX_Alias_Unit, MAX(CASE WHEN CUSTOMRECORD_WMSSE_SKU_ALIAS.custrecord_wmsse_alias_desc LIKE '%BX%' THEN CUSTOMRECORD_WMSSE_SKU_ALIAS.custrecord_wmsse_alias_desc END ) AS BX_Alias_Description, MAX(CASE WHEN CUSTOMRECORD_WMSSE_SKU_ALIAS.custrecord_wmsse_alias_desc LIKE '%BX%' THEN REPLACE(REGEXP_SUBSTR(BUILTIN.DF(CUSTOMRECORD_WMSSE_SKU_ALIAS.custrecord_wms_alias_unit), '-\s*([0-9]+)', 1, 1, 'i'), '-','') END) AS BX_Inner_Quantity, MAX(CASE WHEN CUSTOMRECORD_WMSSE_SKU_ALIAS.custrecord_wmsse_alias_desc LIKE '%BX%' THEN CUSTOMRECORD_WMSSE_SKU_ALIAS.custrecord_alias_length END) AS BX_Alias_Length, MAX(CASE WHEN CUSTOMRECORD_WMSSE_SKU_ALIAS.custrecord_wmsse_alias_desc LIKE '%BX%' THEN CUSTOMRECORD_WMSSE_SKU_ALIAS.custrecord_alias_length * 2.54 END) AS BX_Alias_Length_CM, MAX(CASE WHEN CUSTOMRECORD_WMSSE_SKU_ALIAS.custrecord_wmsse_alias_desc LIKE '%BX%' THEN CUSTOMRECORD_WMSSE_SKU_ALIAS.custrecord_alias_width END) AS BX_Alias_Width, MAX(CASE WHEN CUSTOMRECORD_WMSSE_SKU_ALIAS.custrecord_wmsse_alias_desc LIKE '%BX%' THEN CUSTOMRECORD_WMSSE_SKU_ALIAS.custrecord_alias_width * 2.54 END) AS BX_Alias_Width_CM, MAX(CASE WHEN CUSTOMRECORD_WMSSE_SKU_ALIAS.custrecord_wmsse_alias_desc LIKE '%BX%' THEN CUSTOMRECORD_WMSSE_SKU_ALIAS.custrecord_alias_height END) AS BX_Alias_Height, MAX(CASE WHEN CUSTOMRECORD_WMSSE_SKU_ALIAS.custrecord_wmsse_alias_desc LIKE '%BX%' THEN CUSTOMRECORD_WMSSE_SKU_ALIAS.custrecord_alias_height * 2.54 END) AS BX_Alias_Height_CM, MAX(CASE WHEN CUSTOMRECORD_WMSSE_SKU_ALIAS.custrecord_wmsse_alias_desc LIKE '%BX%' THEN CUSTOMRECORD_WMSSE_SKU_ALIAS.custrecord_alias_weight END) AS BX_Alias_Weight, ROUND(MAX(CASE WHEN CUSTOMRECORD_WMSSE_SKU_ALIAS.custrecord_wmsse_alias_desc LIKE '%BX%' THEN CUSTOMRECORD_WMSSE_SKU_ALIAS.custrecord_alias_weight * 453.59237 END), 2) AS BX_Alias_Weight_Grams, MAX(CASE WHEN CUSTOMRECORD_WMSSE_SKU_ALIAS.custrecord_wmsse_alias_desc LIKE '%MC%' THEN CUSTOMRECORD_WMSSE_SKU_ALIAS.name END) AS master_barcode, MAX(CASE WHEN CUSTOMRECORD_WMSSE_SKU_ALIAS.custrecord_wmsse_alias_desc LIKE '%MC%' THEN BUILTIN.DF( CUSTOMRECORD_WMSSE_SKU_ALIAS.custrecord_wms_alias_unit ) END ) AS MC_Alias_Unit, MAX(CASE WHEN CUSTOMRECORD_WMSSE_SKU_ALIAS.custrecord_wmsse_alias_desc LIKE '%MC%' THEN CUSTOMRECORD_WMSSE_SKU_ALIAS.custrecord_wmsse_alias_desc END ) AS MC_Alias_Description, MAX(CASE WHEN CUSTOMRECORD_WMSSE_SKU_ALIAS.custrecord_wmsse_alias_desc LIKE '%MC%' THEN REPLACE(REGEXP_SUBSTR(BUILTIN.DF(CUSTOMRECORD_WMSSE_SKU_ALIAS.custrecord_wms_alias_unit), '-\s*([0-9]+)', 1, 1, 'i'), '-','') END) AS Master_Quantity, (MAX(CASE WHEN CUSTOMRECORD_WMSSE_SKU_ALIAS.custrecord_wmsse_alias_desc LIKE '%MC%' THEN REPLACE(REGEXP_SUBSTR(BUILTIN.DF(CUSTOMRECORD_WMSSE_SKU_ALIAS.custrecord_wms_alias_unit), '-\s*([0-9]+)', 1, 1, 'i'), '-','') END) / MAX(CASE WHEN CUSTOMRECORD_WMSSE_SKU_ALIAS.custrecord_wmsse_alias_desc LIKE '%BX%' THEN REPLACE(REGEXP_SUBSTR(BUILTIN.DF(CUSTOMRECORD_WMSSE_SKU_ALIAS.custrecord_wms_alias_unit), '-\s*([0-9]+)', 1, 1, 'i'), '-','') END)) AS Inners_Per_MC, MAX(CASE WHEN CUSTOMRECORD_WMSSE_SKU_ALIAS.custrecord_wmsse_alias_desc LIKE '%MC%' THEN CUSTOMRECORD_WMSSE_SKU_ALIAS.custrecord_alias_length END) AS MC_Alias_Length, MAX(CASE WHEN CUSTOMRECORD_WMSSE_SKU_ALIAS.custrecord_wmsse_alias_desc LIKE '%MC%' THEN CUSTOMRECORD_WMSSE_SKU_ALIAS.custrecord_alias_length * 2.54 END) AS MC_Alias_Length_CM, MAX(CASE WHEN CUSTOMRECORD_WMSSE_SKU_ALIAS.custrecord_wmsse_alias_desc LIKE '%MC%' THEN CUSTOMRECORD_WMSSE_SKU_ALIAS.custrecord_alias_width END) AS MC_Alias_Width, MAX(CASE WHEN CUSTOMRECORD_WMSSE_SKU_ALIAS.custrecord_wmsse_alias_desc LIKE '%MC%' THEN CUSTOMRECORD_WMSSE_SKU_ALIAS.custrecord_alias_width * 2.54 END) AS MC_Alias_Width_CM, MAX(CASE WHEN CUSTOMRECORD_WMSSE_SKU_ALIAS.custrecord_wmsse_alias_desc LIKE '%MC%' THEN CUSTOMRECORD_WMSSE_SKU_ALIAS.custrecord_alias_height END) AS MC_Alias_Height, MAX(CASE WHEN CUSTOMRECORD_WMSSE_SKU_ALIAS.custrecord_wmsse_alias_desc LIKE '%MC%' THEN CUSTOMRECORD_WMSSE_SKU_ALIAS.custrecord_alias_height * 2.54 END) AS MC_Alias_Height_CM, MAX(CASE WHEN CUSTOMRECORD_WMSSE_SKU_ALIAS.custrecord_wmsse_alias_desc LIKE '%MC%' THEN CUSTOMRECORD_WMSSE_SKU_ALIAS.custrecord_alias_weight END) AS MC_Alias_Weight, ROUND(MAX(CASE WHEN CUSTOMRECORD_WMSSE_SKU_ALIAS.custrecord_wmsse_alias_desc LIKE '%MC%' THEN CUSTOMRECORD_WMSSE_SKU_ALIAS.custrecord_alias_weight * 453.59237 END), 2) AS MC_Alias_Weight_Grams, FROM item INNER JOIN CUSTOMRECORD_MM_PRODUCT_SPEC ON item.custitem_mm_specifcation = CUSTOMRECORD_MM_PRODUCT_SPEC.id INNER JOIN CUSTOMRECORD_WMSSE_SKU_ALIAS ON item.id = CUSTOMRECORD_WMSSE_SKU_ALIAS.custrecord_wmsse_alias_item WHERE item.custitem_mm_saleable_item = 'T' AND item.isinactive = 'F' AND item.itemid NOT LIKE 'W%' GROUP BY item.itemid, item.upccode, item.description, item.custitem_top30rank, item.custitem_mm_specifcation, item.custitem_mm_un3091, item.custitem_mm_un3481, item.custitem_mm_un1170, item.custitem_mm_prop_65, CUSTOMRECORD_MM_PRODUCT_SPEC.id, CUSTOMRECORD_MM_PRODUCT_SPEC.custrecord_mm_country_of_origin, CUSTOMRECORD_MM_PRODUCT_SPEC.custrecord_mm_formal_name, CUSTOMRECORD_MM_PRODUCT_SPEC.custrecord_mm_batteries_included, CUSTOMRECORD_MM_PRODUCT_SPEC.custrecord_mm_battery_life, CUSTOMRECORD_MM_PRODUCT_SPEC.custrecord_mm_battery_type_used, CUSTOMRECORD_MM_PRODUCT_SPEC.custrecord_mm_extra_batteries_included, CUSTOMRECORD_MM_PRODUCT_SPEC.custrecord_mm_bullet_point_1, CUSTOMRECORD_MM_PRODUCT_SPEC.custrecord_mm_bullet_point_2, CUSTOMRECORD_MM_PRODUCT_SPEC.custrecord_mm_bullet_point_3, CUSTOMRECORD_MM_PRODUCT_SPEC.custrecord_mm_bullet_point_4, CUSTOMRECORD_MM_PRODUCT_SPEC.custrecord_mm_bullet_point_5, CUSTOMRECORD_MM_PRODUCT_SPEC.custrecord_mm_spec_colors_available, CUSTOMRECORD_MM_PRODUCT_SPEC.custrecord_mm_feeling_sensation, CUSTOMRECORD_MM_PRODUCT_SPEC.custrecord_mm_functions, CUSTOMRECORD_MM_PRODUCT_SPEC.custrecord_mm_latex_pht_free, CUSTOMRECORD_MM_PRODUCT_SPEC.custrecord_mm_material, CUSTOMRECORD_MM_PRODUCT_SPEC.custrecord_mm_one_size_fits_most, CUSTOMRECORD_MM_PRODUCT_SPEC.custrecord_mm_product_desc_short, CUSTOMRECORD_MM_PRODUCT_SPEC.custrecord_mm_prod_desc_long, CUSTOMRECORD_MM_PRODUCT_SPEC.custrecord_mm_product_type, CUSTOMRECORD_MM_PRODUCT_SPEC.custrecord_mm_retail_pack_type, CUSTOMRECORD_MM_PRODUCT_SPEC.custrecord_mm_reusable, CUSTOMRECORD_MM_PRODUCT_SPEC.custrecord_mm_texture, CUSTOMRECORD_MM_PRODUCT_SPEC.custrecord_mm_video_link_1, CUSTOMRECORD_MM_PRODUCT_SPEC.custrecord_mm_video_link_2, CUSTOMRECORD_MM_PRODUCT_SPEC.custrecord_mm_video_link_3 ORDER BY item.itemid ) INNER JOIN CUSTOMRECORD_MM_PRODUCT_SPEC ON itemspecId = CUSTOMRECORD_MM_PRODUCT_SPEC.id