SELECT
			NOW() AS `cached`,
			`cre`.`id`,
			IF(`cnt`.`pcr_modified` IS NOT NULL, `cnt`.`pcr_modified`, `cre`.`pdc_modified`) AS `modified`,
			`cre`.`pdc_parent` AS `parent`,
			`cre`.`pdc_number` AS `number`,
			`cre`.`pdc_package_unit` AS `package_unit`,
			`cre`.`pdc_layout` AS `layout`,
			IF(`cre`.`pdc_layout` = 'voucher', 'V', 'P') AS `species`,
			`cre`.`pdc_gtin` AS `gtin`,
			`cre`.`pdc_pzn` AS `pzn`,
			`cre`.`pdc_ppn` AS `ppn`,
			`cre`.`pdc_type` AS `type`,
			`cre`.`pdc_vegan` AS `vegan`,
			`cre`.`pdc_spf` AS `spf`,
			`ccl`.`ptx_name_short` AS `ccl`,
			`cln`.`ptx_name_short` AS `cln`,
			`cre`.`pdc_color` AS `colorid`,
			`cre`.`pdc_color_code` AS `color_code`,
			@name:=IF(`ccl`.`ptx_name_short` IS NOT NULL, `ccl`.`ptx_name_short`, IF(`cln`.`ptx_name_short` IS NOT NULL, `cln`.`ptx_name_short`, `cre`.`pdc_name`)) AS `name`,
			IF(`ccl`.`ptx_teaser` IS NOT NULL, `ccl`.`ptx_teaser`, `cln`.`ptx_teaser`) AS `teaser`,
			IF(`ccl`.`ptx_caption` IS NOT NULL, `ccl`.`ptx_caption`, `cln`.`ptx_caption`) AS `caption`,
			IF(`ccl`.`ptx_description` IS NOT NULL, `ccl`.`ptx_description`, `cln`.`ptx_description`) AS `description`,
			IF(`ccl`.`ptx_features` IS NOT NULL, `ccl`.`ptx_features`, `cln`.`ptx_features`) AS `features`,
			IF(`ccl`.`ptx_application` IS NOT NULL, `ccl`.`ptx_application`, `cln`.`ptx_application`) AS `application`,
			IF(`ccl`.`ptx_color` IS NOT NULL, `ccl`.`ptx_color`, `cln`.`ptx_color`) AS `color`,
			IF(`ccl`.`ptx_ingredient` IS NOT NULL, `ccl`.`ptx_ingredient`, `cln`.`ptx_ingredient`) AS `ingredient`,
			IF(`ccl`.`ptx_info` IS NOT NULL, `ccl`.`ptx_info`, `cln`.`ptx_info`) AS `info`,

			IF(`ing1`.`ingredients` IS NULL, `ing2`.`ingredients`, `ing1`.`ingredients`) AS `ingredient`,
			IF(`ing1`.`mapping` IS NULL, `ing2`.`mapping`, `ing1`.`mapping`) AS `incimapping`,

			IF(`ccl`.`ptx_whatis` IS NOT NULL, `ccl`.`ptx_whatis`, `cln`.`ptx_whatis`) AS `whatis`,
			IF(`ccl`.`ptx_forwhat` IS NOT NULL, `ccl`.`ptx_forwhat`, `cln`.`ptx_forwhat`) AS `forwhat`,
			IF(`ccl`.`ptx_why` IS NOT NULL, `ccl`.`ptx_why`, `cln`.`ptx_why`) AS `why`,

			IF(`ccl`.`ptx_html` IS NOT NULL, `ccl`.`ptx_html`, `cln`.`ptx_html`) AS `html`,
			IF(`ccl`.`ptx_seo_content1` IS NOT NULL, `ccl`.`ptx_seo_content1`, `cln`.`ptx_seo_content1`) AS `seo_content1`,
			IF(`ccl`.`ptx_seo_content2` IS NOT NULL, `ccl`.`ptx_seo_content2`, `cln`.`ptx_seo_content2`) AS `seo_content2`,
			IF(`ccl`.`ptx_synonym_extended` IS NOT NULL, `ccl`.`ptx_synonym_extended`, `cln`.`ptx_synonym_extended`) AS `synonyms`,
			IF(`ccl`.`ptx_meta_title` IS NOT NULL, `ccl`.`ptx_meta_title`, `cln`.`ptx_meta_title`) AS `meta_title`,
			IF(`ccl`.`ptx_meta_keywords` IS NOT NULL, `ccl`.`ptx_meta_keywords`, `cln`.`ptx_meta_keywords`) AS `meta_keywords`,
			IF(`ccl`.`ptx_meta_description` IS NOT NULL, `ccl`.`ptx_meta_description`, `cln`.`ptx_meta_description`) AS `meta_description`,
			IF(`ccl`.`ptx_video` IS NOT NULL, `ccl`.`ptx_video`, `cln`.`ptx_video`) AS `video`,
			@available:=IF(`cnt`.`pcr_available` IS NOT NULL, `cnt`.`pcr_available`, `cre`.`pdc_available`) AS `available`,
			@status:=`cnt`.`pcr_available_status` AS `status`,
			@available_status:=`cnt`.`pcr_available_status` AS `available_status`,
			@available_date:=`cnt`.`pcr_available_date` AS `available_date`,
			IF(`cnt`.`pcr_available_status` BETWEEN 1 AND 2 AND `cnt`.`pcr_available` BETWEEN 1 AND 4, 1, 0) AS `visible`, 
			1 AS `b2c`,
			1 AS `b2b`, 
			1 AS `b2d`, 
			`cre`.`pdc_brand` AS `brand`,

			@release:=IF(`cnt`.`pcr_release` IS NOT NULL, `cnt`.`pcr_release`, `cre`.`pdc_release`) AS `release`,
			@expires:=IF(`cnt`.`pcr_expires` IS NOT NULL, `cnt`.`pcr_expires`, `cre`.`pdc_expires`) AS `expires`,
			@new:=IF(`cnt`.`pcr_new` IS NOT NULL, `cnt`.`pcr_new`, `cre`.`pdc_new`) AS `new`,
			@giftidea:=IF(`cnt`.`pcr_giftidea` IS NOT NULL, `cnt`.`pcr_giftidea`, `cre`.`pdc_giftidea`) AS `giftidea`,
			@is_new:=IF((@new >= NOW() AND @new IS NOT NULL), 1, 0) AS `is_new`,
			IF(`cnt`.`pcr_subscribable` IS NOT NULL, `cnt`.`pcr_subscribable`, 1) AS `subscribable`,
			IF(`cnt`.`pcr_condition_type` IS NOT NULL, `cnt`.`pcr_condition_type`, 'bpoints') AS `condition_type`,
			IF(`cnt`.`pcr_condition_value` IS NOT NULL, `cnt`.`pcr_condition_value`, 2) AS `condition_value`,

			IF(`cnt`.`pcr_bpoints_multiplier` IS NOT NULL, `cnt`.`pcr_bpoints_multiplier`, 1) AS `multiplier`,

			`cnt`.`pcr_vat` AS `vat`,

			@is_price_discountable:=IF(`cnt`.`pcr_discountable` IS NOT NULL, `cnt`.`pcr_discountable`, `cre`.`pdc_discountable`) AS `is_price_discountable`, 
				@price_regular:=CAST(`cnt`.`pcr_price_regular` AS DECIMAL(8,2)) AS `price_regular`,
				@price_value:=CAST(
					IF(`cnt`.`pcr_price_value` IS NULL, 0, `cnt`.`pcr_price_value`) AS DECIMAL(8,2)) AS `price_value`,
				@is_promotion:=IF(`cnt`.`pcr_price_regular` < IF(`cnt`.`pcr_price_value` IS NULL, 0, `cnt`.`pcr_price_value`), 1, 0) AS `is_promotion`, 
				@price_calculate:=CAST(
					IF(`cnt`.`pcr_price_value` > IF(`cnt`.`pcr_price_value` IS NULL, 0, `cnt`.`pcr_price_value`), `cnt`.`pcr_price_value`, IF(`cnt`.`pcr_price_value` IS NULL, 0, `cnt`.`pcr_price_value`)) AS DECIMAL(8,2)) AS `price_calculate`,
				@price_offer:=CAST(
					IF(`cnt`.`pcr_price_offer` IS NULL, 0, `cnt`.`pcr_price_offer`) AS DECIMAL(8,2)) AS `price_offer`,
				@price_unit:=`cnt`.`pcr_price_unit` AS `price_unit`,
			
				`cnt`.`pcr_price_offer_release` AS `price_offer_release`,
				`cnt`.`pcr_price_offer_expires` AS `price_offer_expires`,

				@is_price_offer:=IF((`cnt`.`pcr_price_offer_release` < NOW() AND `cnt`.`pcr_price_offer_expires` > NOW() AND `cnt`.`pcr_price_offer` > 0 AND `cnt`.`pcr_price_offer` < `cnt`.`pcr_price_regular`), 1, 0) AS `is_price_offer`,
				@offer:=IF(@is_price_offer = 1, `cnt`.`pcr_price_offer_expires`, IF(`cnt`.`pcr_offer` IS NOT NULL, `cnt`.`pcr_offer`, `cre`.`pdc_offer`)) AS `offer`,
				@is_offer:=IF(((@offer >= NOW() AND @offer IS NOT NULL) OR @is_price_offer = 1), 1, 0) AS `is_offer`,

				@price:=CAST(IF(@is_price_offer = 1, @price_offer, @price_regular) AS DECIMAL(8,2)) AS `price`,
				@sortprice:=CAST(IF(@is_price_offer = 1, @price_offer, @price_regular) AS DECIMAL(8,2) ) AS `sortprice`,

				@price_advantage:=IF(((@is_price_offer = 1) OR (CAST(IF(`cnt`.`pcr_price_value` IS NULL, 0, `cnt`.`pcr_price_value`) AS DECIMAL(8,2)) > CAST(`cnt`.`pcr_price_regular` AS DECIMAL(8,2)))), 1, 0) AS `price_advantage`,
				@price_saving_amount_order:=0 AS `price_saving_amount_order`,
				@price_saving_amount_product:=0 AS `price_saving_amount_product`,
				@price_saving_amount:=IF(@price_advantage = 1, IF(@is_price_offer = 1, (@price_regular - @price_offer), (@price_calculate - @price)), 0) AS `price_saving_amount`,
				IF(@price_advantage = 1, ROUND(@price_saving_amount * 100 / IF(@is_price_offer = 1, @price_regular, @price_calculate)), 0) AS `price_saving_percent`,
				IF(@is_price_offer = 1, 5, 0) AS `discounted`, 
				IF(@is_price_offer = 0 AND @is_price_discountable = 1, 1, 0) AS `discountable`, 
				
				0 AS `freegoods_limit`, 
				0 AS `freegoods_quantity`, 
				NULL AS `freegoods_product`, 
				
				IF(@is_promotion = 1, 'promotion', IF(@is_offer = 1, 'sale', IF(@is_new = 1, 'new', IF(IF(`cnt`.`pcr_flaged` IS NOT NULL, `cnt`.`pcr_flaged`, `cre`.`pdc_flaged`) >= NOW(), IF(`cnt`.`pcr_flaged_type` IS NOT NULL, `cnt`.`pcr_flaged_type`, `cre`.`pdc_flaged_type`), NULL)))) AS `flagged`,

			`cnt`.`pcr_popularity` AS `popularity`,
			@amount:=`cre`.`pdc_amount` AS `amount`,
			@amount AS `contents`,
			`cre`.`pdc_measurement` AS `measurement`,
			`cre`.`pdc_divided` AS `divided`,
			`cre`.`pdc_weight` AS `weight`,
			`cre`.`pdc_volume` AS `volume`,
			`cre`.`pdc_dimensions_length` AS `dimensions_length`,
			`cre`.`pdc_dimensions_width` AS `dimensions_width`,
			`cre`.`pdc_dimensions_height` AS `dimensions_height`,

			`cre`.`pdc_hazardous_goods` AS `hazardous_goods`,
			`cre`.`pdc_hazardous_goods_weight` AS `hazardous_goods_weight`,
			`cre`.`pdc_hazardous_goods_class_road` AS `hazardous_goods_class_road`,
			`cre`.`pdc_hazardous_goods_class_sea` AS `hazardous_goods_class_sea`,
			`cre`.`pdc_hazardous_goods_class_air` AS `hazardous_goods_class_air`,
			`cre`.`pdc_origin_country` AS `origin_country`,
			`cre`.`pdc_customs_tariff_number` AS `customs_tariff_number`,

			@price_per_100:=ROUND(@price / @amount * 100, 2) AS `price_per_100`,
			@price_per_100 AS `unitprice`,
			IF(`pcc1`.`primary` IS NOT NULL, `pcc1`.`primary`, `pc1`.`primary`) AS `line`,
			@catcaption1:=`cl1`.`ctc_caption` AS `catcaption1`,
			@catalias1:=`cl1`.`ctc_alias` AS `catalias1`,
			@catcaption2:=`cl2`.`ctc_caption` AS `catcaption2`,
			@catalias2:=`cl2`.`ctc_alias` AS `catalias2`,
			@catcaption3:=`cl3`.`ctc_caption` AS `catcaption3`,
			@catalias3:=`cl3`.`ctc_alias` AS `catalias3`,
			CONCAT_WS('/', @catalias1, @catalias2, @catalias3) AS `path`,
			`cnt`.`pcr_redirect` AS `redirect`,
			`cnt`.`pcr_garnish_blocked` AS `garnish_blocked`, 
			`cnt`.`pcr_promos_blocked` AS `promos_blocked`, 
			CONCAT(IF(@catcaption3 IS NOT NULL, @catcaption3, IF(@catcaption2 IS NOT NULL, @catcaption2, IF(@catcaption1 IS NOT NULL, @catcaption1, ''))), ' ', @name) AS `longname`,
			TRIM(CONCAT_WS(' ', @catcaption1, @catcaption2, @catcaption3, @name)) AS `fullname`,
			IF(`cnt`.`pcr_index_A` IS NOT NULL, `cnt`.`pcr_index_A`, `cre`.`pdc_index_A`) AS `index_A`,
			IF(`cnt`.`pcr_index_B` IS NOT NULL, `cnt`.`pcr_index_B`, `cre`.`pdc_index_B`) AS `index_B`,
			IF(`cnt`.`pcr_index_C` IS NOT NULL, `cnt`.`pcr_index_C`, `cre`.`pdc_index_C`) AS `index_C`,
			IF(`cnt`.`pcr_index_D` IS NOT NULL, `cnt`.`pcr_index_D`, `cre`.`pdc_index_D`) AS `index_D`,
			IF(`gva`.`id` IS NULL, 0, 1) AS `giveaways`,
			`cre`.`pdc_folder` AS `folder`
		FROM
			`babordb001`.`tbl_products_core` AS `cre`
		LEFT JOIN
			`babordb001`.`tbl_products_country_b2c` AS `cnt`
		ON (
			`cnt`.`id` = `cre`.`id` AND
			`cnt`.`country` = 'US' 
			)
		
		LEFT JOIN
			`babordb001`.`tbl_products_ingredients` AS `ing1`
		ON (
			`ing1`.`id` = `cre`.`id` AND
			`ing1`.`language` = 'EN'
			)
		LEFT JOIN
			`babordb001`.`tbl_products_ingredients` AS `ing2`
		ON (
			`ing2`.`id` = `cre`.`id` AND
			`ing2`.`language` = 'EN'
			)
		LEFT JOIN
			`babordb001`.`tbl_products_giveaways` AS `gva`
		ON (
			`gva`.`id` = `cre`.`id` AND
			`gva`.`country` = 'US'
			)

		LEFT JOIN
			`babordb001`.`tbl_products_language` AS `cln`
		ON (
			`cln`.`id` = `cre`.`id` AND
			`cln`.`language` = 'EN'
			)
		LEFT JOIN
			`babordb001`.`tbl_products_country_language` AS `ccl`
		ON (
			`ccl`.`id` = `cre`.`id` AND
			`ccl`.`country` = 'US' AND
			`ccl`.`language` = 'EN'
			)
			
		LEFT JOIN
			`babordb001`.`tbl_products_categorize` AS `pc1` 
		ON (
			`pc1`.`id` = `cre`.`id` AND
			`pc1`.`type` = 'srn' 
			)
		LEFT JOIN
			`babordb001`.`tbl_products_categorize_country` AS `pcc1` 
		ON (
			`pcc1`.`id` = `cre`.`id` AND
			`pcc1`.`type` = 'srn' AND 
			`pcc1`.`country` = 'US' 
			)
		LEFT JOIN 
			`babordb001`.`tbl_categorize_language` AS `cl1` 
		ON (
			`cl1`.`id` = IF(`pcc1`.`primary` IS NOT NULL, `pcc1`.`primary` ,`pc1`.`primary`) AND
			`cl1`.`country` = 'US' AND
			`cl1`.`language` = 'EN'
			)
		LEFT JOIN
			`babordb001`.`tbl_products_categorize` AS `pc2` 
		ON (
			`pc2`.`id` = `cre`.`id` AND
			`pc2`.`type` = 'srn' 
			)
		LEFT JOIN
			`babordb001`.`tbl_products_categorize_country` AS `pcc2` 
		ON (
			`pcc2`.`id` = `cre`.`id` AND
			`pcc2`.`type` = 'srn' AND 
			`pcc2`.`country` = 'US' 
			)
		LEFT JOIN 
			`babordb001`.`tbl_categorize_language` AS `cl2` 
		ON (
			`cl2`.`id` = IF(`pcc2`.`secondary` IS NOT NULL, `pcc2`.`secondary` ,`pc2`.`secondary`) AND
			`cl2`.`country` = 'US' AND
			`cl2`.`language` = 'EN'
			)
		LEFT JOIN
			`babordb001`.`tbl_products_categorize` AS `pc3` 
		ON (
			`pc3`.`id` = `cre`.`id` AND
			`pc3`.`type` = 'srn' 
			)
		LEFT JOIN
			`babordb001`.`tbl_products_categorize_country` AS `pcc3` 
		ON (
			`pcc3`.`id` = `cre`.`id` AND
			`pcc3`.`type` = 'srn' AND 
			`pcc3`.`country` = 'US' 
			)
		LEFT JOIN 
			`babordb001`.`tbl_categorize_language` AS `cl3` 
		ON (
			`cl3`.`id` =  IF(`pcc3`.`tertiary` IS NOT NULL, `pcc3`.`tertiary` ,`pc3`.`tertiary`) AND
			`cl3`.`country` = 'US' AND
			`cl3`.`language` = 'EN'
			)
		WHERE
			( `cre`.`id` = 58323 ) AND
			
			(IF(`cnt`.`pcr_release` IS NOT NULL, `cnt`.`pcr_release`, `cre`.`pdc_release`) <= NOW() OR IF(`cnt`.`pcr_release` IS NOT NULL, `cnt`.`pcr_release`, `cre`.`pdc_release`) IS NULL) AND
			(IF(`cnt`.`pcr_expires` IS NOT NULL, `cnt`.`pcr_expires`, `cre`.`pdc_expires`) >= NOW() OR IF(`cnt`.`pcr_expires` IS NOT NULL, `cnt`.`pcr_expires`, `cre`.`pdc_expires`) IS NULL) AND
			(IF(`cnt`.`pcr_available` IS NOT NULL, `cnt`.`pcr_available`, `cre`.`pdc_available`) BETWEEN 1 AND 4) AND
			(`cnt`.`pcr_available_status` BETWEEN 1 AND 2) 
		GROUP BY
			`cre`.`id`
		LIMIT 1;
Vitalizing Hair & Body Shampoo

BABOR MEN

Vitalizing Hair & Body Shampoo

Contents: 200 ml


Vitalizing shower gel for the skin and hair.

Available
No Animal Testing & Vegan

Vitalizing Hair & Body Shampoo is a refreshing, vitalizing shower gel for the hair and body.

Vitalizing Hair & Body Shampoo has a gentle cleansing action. It refreshes and invigorates, while conditioning the skin and the scalp. The perfect start to the day.

TAUREC – a powerful active complex helps vitalize the skin and strengthen its natural protective moisture barrier. Intensive-action birch extract helps firm the skin and reduce the appearance of fine lines and wrinkles. Wheat proteins and maize extract supply intensive moisture and smooth the skin.

Apply to wet skin and hair, work up to a lather, then rinse off under the shower.


This is what our customers say:

Ask Questions, Get Answers