-- Make sure to Remove Duplicates in Excel because certain items may have
-- inconsistent stock quantities and throw in multiple references for ordering.
-- DECLARE @StartDate VARCHAR(16), @EndDate VARCHAR(16), @LeadTimeInDays DECIMAL, @SalesWindowInDays DECIMAL, @MinimumOrderCount INT, @PONumber VARCHAR(16)
SET @StartDate = '2024-04-01';
SET @EndDate = '2024-09-30';
SET @LeadTimeInDays = 3;
SET @SalesWindowInDays = 90;
SET @MinimumOrderCount = 5;
SET @PONumber = '12032024';
SELECT
client_map.PIN AS Inventory_Location,
MAX(client_map.ProductCode) AS ProductCode,
MAX(pins.Name) AS ProductName,
IFNULL(_VPW.Stock,0) AS Inv_QtyOnHand,
-- This CASE statement selects 0 for the total unshipped if there were none in the query.
CASE WHEN MAX(UnappliedStock.Total_Unshipped) IS NULL THEN 0 ELSE MAX(UnappliedStock.Total_Unshipped) END AS Unapplied_Stock,
'0' AS Bosch_On_Order,
'0' AS Dist2_On_Order,
-- This CASE statement selects the estimated number of stock which will be used in the given period
-- unless there are fewer than that in stock. In that case, the lower number (the inventory count)
-- will be used instead. This will hopefully prevent unnecessary over-buying.
CASE WHEN ROUND((SUM(orderdetails.Quantity) * (@LeadTimeInDays/@SalesWindowInDays)),0) > IFNULL(_VPW.Stock,0) THEN IFNULL(_VPW.Stock,0) ELSE 
ROUND((SUM(orderdetails.Quantity) * (@LeadTimeInDays/@SalesWindowInDays)),0) END AS Stock_Attrition,
SUM(orderdetails.Quantity) AS Period_Sales,
-- Average cost per item in the given period.
ROUND((SUM(orderdetails.Quantity * orderdetails.Vendor_Price)/SUM(orderdetails.Quantity)),2) AS Avg_Cost,
MAX(pins.Cost) AS Bosch_Cost,
COUNT(orderdetails.Quantity) AS times_sold,
ROUND((CAST(SUM(orderdetails.Quantity) AS Decimal)/CAST(COUNT(orderdetails.Quantity) AS DECIMAL)),2) AS Items_Per_Line,
-- This is the order quantity recommendation algorithim.
-- Begin by taking the current available inventory level (on shelf minus unapplied inventory)
-- and subtracting the expected number of item which will sell in the given period.
-- This is the total inventory which is expected to be there once the given order is placed.
-- Subtract this from the total sold in the given period, and that should leave you with
-- a reasonable number of items to order.
-- For more detailed reasoning on the CASE statements, read above to see their analogues.
'750321' AS Account,
@PONumber AS PONumber,
''AS SuppliersManf,
RIGHT(MAX(client_map.ProductCode), LENGTH(MAX(orderdetails.ProductCode))-4) AS SuppliersSKU,
' ' AS ItemName,
SUM(orderdetails.Quantity) - (
IFNULL(_VPW.Stock,0)
- CASE WHEN MAX(UnappliedStock.Total_Unshipped) IS NULL THEN 0 ELSE MAX(UnappliedStock.Total_Unshipped) END
- CASE WHEN ROUND((SUM(orderdetails.Quantity) * (@LeadTimeInDays/@SalesWindowInDays)),0) > IFNULL(_VPW.Stock,0) THEN IFNULL(_VPW.Stock,0) ELSE ROUND((SUM(orderdetails.Quantity) * (@LeadTimeInDays/@SalesWindowInDays)),0) END
)
AS Ordered
FROM products.client_map
JOIN products.pins
	ON client_map.PIN = pins.PIN
LEFT JOIN products._VPW
	ON client_map.PIN = _VPW.MFG_SKU
LEFT OUTER JOIN (
SELECT client_map.PIN, JustApplied.Total_Unshipped
FROM custopayted.orders
JOIN custopayted.orderdetails
ON orders.OrderID = orderdetails.OrderID
JOIN products.client_map
ON orderdetails.ProductCode = client_map.ProductCode
JOIN (
SELECT client_map.PIN AS Location, SUM(orderdetails.Quantity) AS Total_Unshipped
FROM custopayted.orderdetails
JOIN products.client_map
ON orderdetails.ProductCode = client_map.ProductCode
WHERE orderdetails.ProductNote LIKE '%STOCK%'
AND orderdetails.Quantity > 0
AND orderdetails.QtyShipped = 0
GROUP BY client_map.PIN
) AS JustApplied
ON client_map.PIN = JustApplied.Location
WHERE JustApplied.Total_Unshipped > 0
GROUP BY client_map.PIN, JustApplied.Total_Unshipped
) AS UnappliedStock
ON client_map.PIN = UnappliedStock.PIN
JOIN orderdetails
ON client_map.ProductCode = orderdetails.ProductCode
JOIN orders
ON orderdetails.OrderID = orders.OrderID
WHERE DATEDIFF(@StartDate , orders.OrderDate) <= 0 AND DATEDIFF(@EndDate , orders.OrderDate) >= 0 
AND orders.OrderStatus <> 'Cancelled'
AND pins.Manufacturer NOT IN ('')
AND orderdetails.Quantity != 0
AND orderdetails.Vendor_Price != 0
AND client_map.Productcode LIKE 'BSH-%'
AND pins.Vertical = 'tool'
GROUP BY client_map.PIN, IFNULL(_VPW.Stock,0)
HAVING COUNT(orderdetails.Quantity) >= @MinimumOrderCount
ORDER BY client_map.PIN ASC