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_2` 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 (`pinfo`.`sku` IN ('ONSGSFS4','GRR60000202','GRR60000203','GN1WXDKZ0005BX','GN1WXDKZ0007BX','GN1WXDKZ0007ES','GN1WXDKZ0005ES','GN124446905','GN124444923PK','GN1KN955','GN1KN95ESPK','GN1KN95ES','SBX77141EA','SBX77141CT','GN1H17011BX','SBX77140CT','GN1E236SAN','GN1CBC1GALSAN','XER008R08112','XER008R08111','SBX77172BX','GN1430LEA','GN112SAN24EA','GN112SAN24','GN1IT0808EA','SJMTHDG986','GN112SAN24EA','DVOCBD540557','SUAHGASSY16','SUAHGFSHLD32','NMDUVB60892','NMDPUS60883','DEFPBCTA2724P','DEFPBCTPC2724P','DEFPBCTA2724F','DEFPBCTPC2724F','DEFPBCTA3136P','DEFPBCTPC3136P','DEFPBCTA3136F','DEFPBCTPC3136F','DEFPBCTA3123B','DEFPBCTPC3123B','DEFPBCHPC3624','DEFPBCHA3624','DEFPBCHPC2436','DEFPBCHA2424','DEFPBCHA2436','DEFPBCHPC2424','DEFPBCMA2331F','DEFPBCMPC2331F','DEFPBCMA2331S','DEFPBCMPC3138FB','DEFPBCMPC3138P','DEFPBCMA3138P','DEFPSDD20SIXFT6','DEFPSDD20SIX50','DEFPSDD20FPSS6','DEFPSDD20FPSS50','DEFPSDD20UWS6','DEFPSDD20UWS50','APH3984528772X3','APH3984528782X3','APH3984528792X3','APH3984528802X3','APH3984528812X3','APH3984528822X3','APH3984528832X3','APH3984528842X3','GN1MFS345ESP','GN1MFS347ESP','GN1MFS380ESP','GN1MFS382ESP','GN1MFS384ESP','GN1MFS386ESP','GN1MFS388ESP','GN1MFS390ESP','GN1MFS422ESP','GN1MFS423ESP','GN1MFS424ESP','GN1MFS425ESP','GN1MFS420ESP','GN1MFS421ESP','GN1MFS426ESP','GN1MFS427ESP','GN1MFS429ESP','GN1MFS428ESP','GN1MFS465ESP','GN1MFS467ESP','GN1MFS464ESP','GN1MFS466ESP','GN1MGNF540VPESP','GN1MGNF542VPESP','GN1MGNF544VPESP','GN1MGNF546VPESP','GN1MGNF547VPESP','GN1MGNF549VPESP','GN1MGNG901VPESP','GN1MGNG905VPESP','GN1MGNG902VPESP','GN1MGNG906VPESP','GN1MGNG903VPESP','GN1MGNG907VPESP','GN1MGNG908VPESP','GN1MGNG909VPESP','HONFTPULL15P8V','HONFTPULL15P6P','HONFTPULL5P8V','HONFTPULL5P6P','ONSGS1Q','ONSGSTS','RST626001','SMP01128EA','SMP01128','SMP01032EA','SMP01032','CLO41773EA','CLO41773CT','PGC22570EA','PGC58773EA','SEV44755EA','SEV44755CT','SEV44752CT','RAC75055CT','SMP30501CT','SMP01001','DBL476801','DBL476803','DBL476823','DBL476901','DBL400423','DBL472101','DBL472123','DBL477101','DBL4772130','DBL4772131','DBL4772132','DBL400023','DBL400123','DBL477323','DUC1265013','DUC1265015','DUC1265014','DUC1304966','DUC1265016','DUC1265018','DUC1265019','VCT11003A','VCT900','SMD10500','SMD10503','SMD10504','SMD10510','SMD10515','SMD10540','SMD14045','SMD70863','SMD70866','SMD70876','SMD71500','SMD71503','SMD73500','SMD85751','SMD89614','MMMWR309LE','MMMMW209MB','MMF28904','FEL98915','AOP6060MS','AOP6040MS','AOP6070MS','AOP6080MS','SMD10338','VCT11803A','BOSB310HDS','ALL42179','ALL42339','AOPLT412MS','DUC1078566','FEL9892901','CLI62137','BOS00540','BOSB326BLK','ACM14643','ALEAP101W','ALEAPFILTER','IVR51516','FAO25001','FAO7006','FAO12001','FAOAN337','FAO90101','FAO90001','FAOH307','FAOFAE7021','FAOFAE6011','FAO12700','FAOFAE4004','FAO21008','ACM90091','ACM90087','ACM90032','FAO40800','ACM90306','LIL97477','PGC01443BX','CRD32200','CRD32215','CRD32220','CRD32230','CRD32100','CRD32120','CRD32130','CRD32140','CRD32150','SJMT1300TBK','BWK6252','CRWOG110AF','CRWCL110','CRWCL010','CRWCL112','CRWBK110BX','MMMM1LANEMARK','MMM471IWRED','MMM471IWYLW','MMM471ORG','')) 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 '%ResourceForReopening' THEN 1 ELSE 0 END DESC, CASE WHEN MATCH(`desc_short`) AGAINST ('ResourceForReopening' IN BOOLEAN MODE) THEN 1 ELSE 0 END DESC, CASE WHEN `pinfo`.`vendor`=2 THEN IF(MATCH(`desc_long`, `manufacturer_name`, `color`) AGAINST ('+ResourceForReopening' IN BOOLEAN MODE),4,0) ELSE CASE WHEN MATCH (`keywords`) AGAINST ('"ResourceForReopening"' IN BOOLEAN MODE) AND MATCH (`desc_title`) AGAINST ('"ResourceForReopening"' IN BOOLEAN MODE) THEN 4 ELSE IF(MATCH (`desc_title`) AGAINST ('"ResourceForReopening"' IN BOOLEAN MODE),3,IF(MATCH (`keywords`) AGAINST ('"ResourceForReopening"' IN BOOLEAN MODE),2,IF(MATCH(`desc_title`, `manufacturer_name`, `color`) AGAINST ('+ResourceForReopening' IN BOOLEAN MODE),1,0))) END END DESC,CASE WHEN IF(`pinfo`.`vendor`=2,`category_3` ='ResourceForReopening',`category_4` ='ResourceForReopening') THEN 1 ELSE 0 END DESC, CASE WHEN `desc_short` LIKE 'ResourceForReopening,%' THEN 1 ELSE 0 END DESC, SUBSTR(`pinfo`.`desc_title`, 1, LOCATE('ResourceForReopening', `pinfo`.`desc_title`)) DESC
Orders less than $75 will incur a $9.99 shipping charge.
For items that are unable to ship via UPS, such as furniture, shipping charges will be quoted on a per shipment basis. You will be contacted if these fees apply to your order.
