SELECT DISTINCT fpcm.StockCode, k8s.WebDescription, k8s.WebCategoryToJoin AS PriceGroup, k8s.RetailPrice, k8s.BasePrice, k8s.SalePrice, p.CustomerAccount, k8s.K8LeadTime, fpcm.displayorder,
sm1.MessageText AS StockMessage1,
sm1.AllowOrder AS AllowOrder1,
sm2.MessageText AS StockMessage2,
sm2.AllowOrder AS AllowOrder2,
sm3.MessageText AS StockMessage3,
sm3.AllowOrder AS AllowOrder3,
sm4.MessageText AS StockMessage4,
sm4.AllowOrder AS AllowOrder4,
CASE p.PriceBand WHEN 'R' THEN k8s.RetailPrice*(1-(p.Discount1/100))
WHEN 'B' THEN k8s.BasePrice*(1-(p.Discount1/100))
ELSE k8s.RetailPrice
END AS CustomerPrice,
CASE WHEN sp.Price > 0 THEN sp.Price
WHEN sp.Discount1 > 0 AND sp.PriceBand = 'R' THEN k8s.RetailPrice*(1-(sp.Discount1/100))
WHEN sp.Discount1 > 0 AND sp.PriceBand = 'B' THEN k8s.BasePrice*(1-(sp.Discount1/100))
ELSE k8s.RetailPrice
END AS CustomerSpecialPricePerProduct,
CASE WHEN sl.Price > 0 THEN sl.Price
WHEN sl.Discount1 > 0 AND sl.PriceBand = 'R' THEN k8s.RetailPrice*(1-(sl.Discount1/100))
WHEN sl.Discount1 > 0 AND sl.PriceBand = 'B' THEN k8s.BasePrice*(1-(sl.Discount1/100))
ELSE k8s.RetailPrice
END AS CustomerSpecialPricePerPriceGroup, k8s.FreeStockBedford2011, k8s.FreeStockBirmingham2301, k8s.FreeStockWakefield2057, p.Discount1, p.Discount2, sp.Discount1, sp.Discount2, sl.Discount1, sl.Discount2, k8s.SourceK8orEsprit, k8s.StockistBranch, k8s.Photo1, k8s.RecommendedSalesMultiple, k8s.EspritStockRank, k8sa.Value AS Brand,
CASE WHEN k8s.websortorder IS NULL THEN 1 ELSE 0 END, k8s.websortorder,
COALESCE ( (
SELECT CASE WHEN Count(1) > 0 Then 1 else 0 End As [value] FROM FP_OFFERS FPO
JOIN FP_OFFERSPRODUCTS FPOP ON FPO.OfferID = FPOP.OfferID
INNER JOIN FP_OFFERGROUPS FPOG ON FPO.GroupID = FPOG.OfferGroupID
WHERE FPOP.ProductCode = fpcm.StockCode
AND (SYSDATETIME() AT TIME ZONE 'UTC' AT TIME ZONE 'GMT Standard Time' BETWEEN CONVERT(smalldatetime, FPOG.ActiveFromDate + ' ' + FPOG.ActiveFromTime) AND CONVERT(smalldatetime, FPOG.ActiveUntilDate + ' ' + FPOG.ActiveUntilTime))
) ,0 ) AS OfferFound
FROM FP_CATALOGUE_PRODUCT_MAPPING fpcm
JOIN K8_STOCK k8s ON k8s.StockCode = fpcm.StockCode
LEFT OUTER JOIN K8_CUSTOMERPRICING p ON k8s.WebCategoryToJoin = p.PriceGroup AND p.CustomerAccount = ''
LEFT OUTER JOIN K8_SPECIALPRICING sp ON k8s.StockCode = sp.ProductCode AND sp.CustomerAccount = ''
LEFT OUTER JOIN K8_SPECIALPRICING sl ON k8s.WebCategoryToJoin = sl.PriceGroup AND sl.CustomerAccount = ''
LEFT OUTER JOIN PFP_STOCK_MESSAGES sm1 ON k8s.EspritStockRank = sm1.StockRank AND sm1.MessageTypeID = '1'
LEFT OUTER JOIN PFP_STOCK_MESSAGES sm2 ON k8s.EspritStockRank = sm2.StockRank AND sm2.MessageTypeID = '2'
LEFT OUTER JOIN PFP_STOCK_MESSAGES sm3 ON k8s.EspritStockRank = sm3.StockRank AND sm3.MessageTypeID = '3'
LEFT OUTER JOIN PFP_STOCK_MESSAGES sm4 ON k8s.EspritStockRank = sm4.StockRank AND sm4.MessageTypeID = '4'
LEFT JOIN K8_STOCK_ATTRIBUTES k8sa ON k8sa.StockCode = fpcm.StockCode
WHERE k8s.Active = 'Y' AND k8s.SourceK8orEsprit = 'Primaflow'
AND k8sa.AttributeID = '21'
AND k8sa.Active = 'Y'
AND fpcm.Active = 'Y'
AND fpcm.CatalogueItemID = '7'
AND k8s.StockCode IN (SELECT fq.StockCode FROM (SELECT DISTINCT p2.StockCode, a2.Value, a2.AttributeID FROM K8_STOCK_ATTRIBUTES a2
INNER JOIN FP_CATALOGUE_PRODUCT_MAPPING p2 on a2.StockCode = p2.StockCode AND p2.Active = 'Y'
AND p2.CatalogueItemID = 7INNER JOIN K8_STOCK s2 on a2.StockCode = s2.StockCode AND s2.Active = 'Y'
WHERE a2.Active = 'Y'
AND (
(a2.AttributeID = 21 AND a2.Value IN ('Pro'))
OR
(a2.AttributeID = 92 AND a2.Value IN ('Vertical'))
)) fq
GROUP BY fq.StockCode HAVING COUNT(fq.StockCode) = 2
)
UNION ALL
SELECT DISTINCT fpcm.StockCode, k8s.WebDescription, k8s.WebCategoryToJoin AS PriceGroup, k8s.RetailPrice, k8s.BasePrice, k8s.SalePrice, p.CustomerAccount, k8s.K8LeadTime, fpcm.displayorder,
sm1.MessageText AS StockMessage1,
sm1.AllowOrder AS AllowOrder1,
sm2.MessageText AS StockMessage2,
sm2.AllowOrder AS AllowOrder2,
sm3.MessageText AS StockMessage3,
sm3.AllowOrder AS AllowOrder3,
sm4.MessageText AS StockMessage4,
sm4.AllowOrder AS AllowOrder4,
CASE p.PriceBand WHEN 'R' THEN k8s.RetailPrice*(1-(p.Discount1/100))
WHEN 'B' THEN k8s.BasePrice*(1-(p.Discount1/100))
ELSE k8s.RetailPrice
END AS CustomerPrice,
CASE WHEN sp.Price > 0 THEN sp.Price
WHEN sp.Discount1 > 0 AND sp.PriceBand = 'R' THEN k8s.RetailPrice*(1-(sp.Discount1/100))
WHEN sp.Discount1 > 0 AND sp.PriceBand = 'B' THEN k8s.BasePrice*(1-(sp.Discount1/100))
ELSE k8s.RetailPrice
END AS CustomerSpecialPricePerProduct,
CASE WHEN sl.Price > 0 THEN sl.Price
WHEN sl.Discount1 > 0 AND sl.PriceBand = 'R' THEN k8s.RetailPrice*(1-(sl.Discount1/100))
WHEN sl.Discount1 > 0 AND sl.PriceBand = 'B' THEN k8s.BasePrice*(1-(sl.Discount1/100))
ELSE k8s.RetailPrice
END AS CustomerSpecialPricePerPriceGroup, k8s.FreeStockBedford2011, k8s.FreeStockBirmingham2301, k8s.FreeStockWakefield2057, p.Discount1, p.Discount2, sp.Discount1, sp.Discount2, sl.Discount1, sl.Discount2, k8s.SourceK8orEsprit, k8s.StockistBranch, k8s.Photo1, k8s.RecommendedSalesMultiple, k8s.EspritStockRank, k8sa.Value AS Brand,
CASE WHEN k8s.websortorder IS NULL THEN 1 ELSE 0 END, k8s.websortorder,
COALESCE ( (
SELECT CASE WHEN Count(1) > 0 Then 1 else 0 End As [value] FROM FP_OFFERS FPO
JOIN FP_OFFERSPRODUCTS FPOP ON FPO.OfferID = FPOP.OfferID
INNER JOIN FP_OFFERGROUPS FPOG ON FPO.GroupID = FPOG.OfferGroupID
WHERE FPOP.ProductCode = fpcm.StockCode
AND (SYSDATETIME() AT TIME ZONE 'UTC' AT TIME ZONE 'GMT Standard Time' BETWEEN CONVERT(smalldatetime, FPOG.ActiveFromDate + ' ' + FPOG.ActiveFromTime) AND CONVERT(smalldatetime, FPOG.ActiveUntilDate + ' ' + FPOG.ActiveUntilTime))
) ,0 ) AS OfferFound
FROM FP_CATALOGUE_PRODUCT_MAPPING fpcm
JOIN K8_STOCK k8s ON k8s.StockCode = fpcm.StockCode
LEFT OUTER JOIN K8_CUSTOMERPRICING p ON k8s.WebCategoryToJoin = p.PriceGroup AND p.CustomerAccount = ''
LEFT OUTER JOIN K8_SPECIALPRICING sp ON k8s.StockCode = sp.ProductCode AND sp.CustomerAccount = ''
LEFT OUTER JOIN K8_SPECIALPRICING sl ON k8s.WebCategoryToJoin = sl.PriceGroup AND sl.CustomerAccount = ''
LEFT OUTER JOIN PFP_STOCK_MESSAGES sm1 ON k8s.EspritStockRank = sm1.StockRank AND sm1.MessageTypeID = '1'
LEFT OUTER JOIN PFP_STOCK_MESSAGES sm2 ON k8s.EspritStockRank = sm2.StockRank AND sm2.MessageTypeID = '2'
LEFT OUTER JOIN PFP_STOCK_MESSAGES sm3 ON k8s.EspritStockRank = sm3.StockRank AND sm3.MessageTypeID = '3'
LEFT OUTER JOIN PFP_STOCK_MESSAGES sm4 ON k8s.EspritStockRank = sm4.StockRank AND sm4.MessageTypeID = '4'
LEFT JOIN K8_STOCK_ATTRIBUTES k8sa ON k8sa.StockCode = fpcm.StockCode
WHERE k8s.Active = 'Y' AND k8s.SourceK8orEsprit = 'FP'
AND k8sa.AttributeID = '21'
AND k8sa.Active = 'Y'
AND fpcm.Active = 'Y'
AND fpcm.CatalogueItemID = '7'
AND k8s.StockCode IN (SELECT fq.StockCode FROM (SELECT DISTINCT p2.StockCode, a2.Value, a2.AttributeID FROM K8_STOCK_ATTRIBUTES a2
INNER JOIN FP_CATALOGUE_PRODUCT_MAPPING p2 on a2.StockCode = p2.StockCode AND p2.Active = 'Y'
AND p2.CatalogueItemID = 7INNER JOIN K8_STOCK s2 on a2.StockCode = s2.StockCode AND s2.Active = 'Y'
WHERE a2.Active = 'Y'
AND (
(a2.AttributeID = 21 AND a2.Value IN ('Pro'))
OR
(a2.AttributeID = 92 AND a2.Value IN ('Vertical'))
)) fq
GROUP BY fq.StockCode HAVING COUNT(fq.StockCode) = 2
)
ORDER BY fpcm.DisplayOrder, CASE WHEN k8s.WebSortOrder IS NULL THEN 1 ELSE 0 END, k8s.WebSortOrder
OFFSET (0) ROWS FETCH NEXT (90) ROWS ONLY
Primaflow F&P
Please note that as this order is requesting a different address to the one we have on this account, we will be in touch to verify your details in order to help prevent fraudulent activities. Delivery changes may also occur in additional carriage charges. This will all be confirmed by the sales representative.
Select Account
Please note: If you have an order in progress in your basket, this will be lost.
Ordering Information
This product is currently unavailable to order via our website.
Please contact your local branch who will be happy to help with placing an order.
Your Privacy Settings
This site uses cookies to personalise content and services and to provide social media features.
Some of the cookies are essential to make the site work and some help us analyse how the site is used. We also share information about your use of our site with social media and advertising partners. By using this site you agree to our use of cookies.
