Banner1 Banner1
 
SELECT `pinfo`.`sku`,`pinfo`.`vendor`, IF(`pinfo`.`multi_level_pricing_check`=1,(`mlrp`.`cost`/`mlrp`.`min_qty`),IF(CURDATE() >= `ds`.`expiration_date`, `ds`.`price`, `crp`.`contract_price`)) AS `price`, CONCAT(IF(`pinfo`.`vendor`=2,`desc_long`,`desc_short`),' ',`pinfo`.`sku`,' ',`category_1`,' ',`category_2`,' ',`category_3`,' ',`category_4`,' ',IF(`pinfo`.`vendor`=2,`sales_copy`,`desc_long`),' ',IF(`dealer_sku`!='',`dealer_sku`,''),' ',`manufacturer_name`,' ',`color`) AS `search_string` FROM `product_info` `pinfo` INNER JOIN `vendor_base_costs` `vbcc` ON `pinfo`.`sku`=`vbcc`.`sku` LEFT JOIN `contract_price_1` AS `crp` ON `crp`.`sku`=`pinfo`.`sku` LEFT JOIN `bestseller_priority_detail` AS `bpd` ON `pinfo`.`sku`=`bpd`.`sku` LEFT JOIN `bestseller_priority_master` AS `bpm` ON `bpm`.`list_id`=`bpd`.`list_id` LEFT JOIN `dealer_special` AS `ds` ON `ds`.`sku`=`pinfo`.`sku` AND CURDATE() BETWEEN `ds`.`start_date` AND `ds`.`expiration_date` LEFT JOIN `multi_level_retail_price` AS `mlrp` ON `mlrp`.`sku`=`pinfo`.`sku` AND `mlrp`.`preferred_vendor`=`pinfo`.`preferred_vendor` AND (`mlrp`.`cost`/`mlrp`.`min_qty`)>0 WHERE (`manufacturer_id` = "1528") AND `pinfo`.`status` IN ('active','discontinued','out_of_stock') AND `pinfo`.`desc_title` != '' AND `online_status`=1 AND `custom_sku_enable`=1 AND CASE WHEN `pinfo`.`multi_level_pricing_check`=1 THEN TRUE ELSE `crp`.`contract_price`>0 END AND `pinfo`.`sku` NOT IN (SELECT `alias_sku` FROM `internal_alias_detail`) GROUP BY `pinfo`.`sku` ORDER BY ISNULL(`bpm`.`list_priority`) ASC, `bpm`.`list_priority` ASC, ISNULL(`bpd`.`priority`) ASC, `bpd`.`priority` ASC, CASE WHEN `pinfo`.`sku` LIKE '%JMSMUCKERCO' THEN 1 ELSE 0 END DESC, CASE WHEN MATCH(`desc_short`) AGAINST ('JMSMUCKERCO' IN BOOLEAN MODE) THEN 1 ELSE 0 END DESC, CASE WHEN `pinfo`.`vendor`=2 THEN IF(MATCH(`desc_long`, `manufacturer_name`, `color`) AGAINST ('+JMSMUCKERCO' IN BOOLEAN MODE),4,0) ELSE CASE WHEN MATCH (`keywords`) AGAINST ('"JMSMUCKERCO"' IN BOOLEAN MODE) AND MATCH (`desc_title`) AGAINST ('"JMSMUCKERCO"' IN BOOLEAN MODE) THEN 4 ELSE IF(MATCH (`desc_title`) AGAINST ('"JMSMUCKERCO"' IN BOOLEAN MODE),3,IF(MATCH (`keywords`) AGAINST ('"JMSMUCKERCO"' IN BOOLEAN MODE),2,IF(MATCH(`desc_title`, `manufacturer_name`, `color`) AGAINST ('+JMSMUCKERCO' IN BOOLEAN MODE),1,0))) END END DESC,CASE WHEN IF(`pinfo`.`vendor`=2,`category_3` ='JMSMUCKERCO',`category_4` ='JMSMUCKERCO') THEN 1 ELSE 0 END DESC, CASE WHEN `desc_short` LIKE 'JMSMUCKERCO,%' THEN 1 ELSE 0 END DESC, SUBSTR(`pinfo`.`desc_title`, 1, LOCATE('JMSMUCKERCO', `pinfo`.`desc_title`)) DESC

Currently Filtered by

Manufacturer

J M SMUCKER CO

Refine Search

  GO
 
Category Filter

Coffee(71)

Condiments & Other Food(5)

Snacks(4)

Coffee Condiments(3)

 
Brand

Folgers(57)

Caf Bustelo(9)

Millstone(5)

Smucker's(5)

Jif To Go(4)

Stevia in the Raw(3)

 
Quantity

1 each(15)

6 per carton(12)

42 per carton(11)

40 per carton(6)

24 per carton(5)

18 per box(5)

12 per pack(2)

36 per carton(2)

30 per carton(1)

210 per pallet(1)

160 per carton(1)

200 per carton(1)

294 per pallet(1)

108 per carton(1)

60 per carton(1)

36 per Carton(1)

 
Packing Type

Fraction Pack(14)

Canister(12)

Bag(9)

Can(8)

Packet(7)

Filter Pack(6)

Pod(6)

Cup(4)

Jar(3)

Vacket Pack(3)

Pack(3)

Brick Pack(2)

Pouch(2)

Coffee Pod(1)

 
Flavor

Classic Roast(10)

Espresso(7)

Classic Roast Decaf(6)

Classic Roast Regular(4)

100% Colombian(4)

Country Roast(3)

Morning Caf(2)

Chocolate Silk(2)

Creamy Peanut Butter(2)

Decaf(2)

Regular(2)

Breakfast Blend(2)

Vanilla Biscotti(2)

Half Caff(2)

100% Colombian Decaf(2)

Special Roast(2)

Hazelnut(1)

Honey(1)

Peanut Butter(1)

Classic Roast Half-Caf(1)

Caf con Leche(1)

Apple Strawberry(1)

Apple Cinnamon(1)

Hazelnut Crme(1)

Colombian Supremo(1)

Morning Blend(1)

100% Colombian Regular(1)

French Vanilla(1)

100% Columbian(1)

Traditional Roast Decaf(1)

Regular Traditional Roast(1)

Caf con Leche Chocolate(1)

Traditional Roast(1)

Gourmet Supreme(1)

Black Silk(1)

Decaffeinated(1)

Simply Smooth(1)

Caramel Drizzle(1)

Assorted(1)

 
Beverage Type

Coffee(61)

Decaffeinated Coffee(5)

Coffee Drink(2)

 
 
 

© 2020-2021 Onestop, Inc., All Rights Reserved