None

This script extracts SYSPRO data into Excel and how we typically work with the data to assign each SKU to a dedicated pick face in the new warehouse layout. It is used when planning to pick all products directly from the pick face. These types of warehouses typically pick and ship high volumes of small orders with many single units picking. When a full carton or two is required for an order, the pick face sizing holds sufficient stock to include full case picking from the pick face.

If you don’t like reading, you can watch the video.

None

premium_PICKFACE_SIZING_QUERY_ALL_THROUGH_PICKFACES_product_quickquide_QG - v0.1.pdf

Try it!

Pick face sizing queries

The steps below are per stock code:

Calculate total qty through pick face based on SYSPRO sales orders in a time frame. 1A: Assumes that the full order qty is taken from PF.
Calculate the total quantity per week.
Calculate max week Qty & max number of orders in a week.
Calculate the average weekly Qty.
Calculate the outlier limit for a weekly Qty.
Calculate the outlier limit for a weekly number of orders (i.e. how many orders per week is the statistical outlier limit)
Combine all the above into one report.

Drop table #ORDERS_LESS_FULLSKIDS
DROP TABLE #TEMP_RESULTS
DROP TABLE #TQ1
DROP TABLE #TQ3
DROP TABLE #TEMPQUART
DROP TABLE #TEMP_MAX_WEEK_QTY
DROP TABLE #TEMP_ORDER_AVG
DROP TABLE #Stats1

---Step 1a: Get all lines through PF (assumes all is coming through pick face)

declare @FromDate Date 
declare @ToDate	Date 
set @FromDate= '2016-01-01'
set @ToDate = '2018-09-01'


select SM.SalesOrder, SM.OrderDate, SD.MStockCode, SD.MOrderQty, SM.ReqShipDate, SD.MOrderQty AS  QtyFromPF, 
DATEPART(WEEK, SM.ReqShipDate) AS WeekNumReal, DATEPART(YEAR, SM.ReqShipDate) as [Year], cast(DATEPART(YEAR, SM.ReqShipDate) as varchar(max))+cast(DATEPART(WEEK, SM.ReqShipDate) as varchar(max)) as WeekNum
into #ORDERS_LESS_FULLSKIDS
from SysproCompanyTest.dbo.SorDetail SD with (nolock) 
INNER JOIN SysproCompanyTest.dbo.SorMaster SM with (nolock) on SD.SalesOrder=SM.SalesOrder
WHERE   SM.OrderStatus IN ('0','1','2','3','4','8','9')
	                    AND ((SM.ReqShipDate >= @FromDate) AND (SM.ReqShipDate < @ToDate))
	                    AND (SD.MWarehouse = 'FG') --- use IN to add multiple warehouses
	                    AND (SD.LineType <> 6) -- Exclude comment lines from Syspro
						AND SD.MOrderQty>'0' ---- exclude credit notes
and SD.MBomFlag <>'P' ---exclude kit parents
						ORDER BY SD.MStockCode, SM.ReqShipDate

---Step 2: For each stock code, calculate total qty per week through PF

select OLF.MStockCode, IM.Description, IM.PanSize, IM.Volume, IM.StockOnHold, OLF.WeekNum,  IM.ListPriceCode, IVP.PriceCode, IVP.SellingPrice,
SUM(OLF.QtyFromPF)as WeeklyPFQty, count(Distinct SalesOrder) as NumOfOrders 
INTO  #TEMP_RESULTS
 from #ORDERS_LESS_FULLSKIDS OLF 
  INNER JOIN SysproCompanyTest.dbo.InvMaster IM with(nolock) ON (IM.StockCode = OLF.MStockCode)
  inner join SysproCompanyTest.dbo.InvPrice IVP with (nolock) on IVP.StockCode=IM.StockCode and IVP.PriceCode=IM.ListPriceCode
			 GROUP BY
	                    OLF.MStockCode,
	                    IM.Description,
						OLF.WeekNum,
	                    IM.PanSize,
	                    IM.Volume,
						IM.StockOnHold,
						IM.ListPriceCode, IVP.PriceCode, IVP.SellingPrice

	          ORDER BY
	                    OLF.MStockCode,
	                    OLF.WeekNum

---Step 3: Per stock code, find the max week's qty and Max no. of orders in a week

----Step 4: Calculate the weekly average based on the number of weeks of sales

----Step 5: Take the weekly order quantities and calculate the quartiles and outlier limit

----Step 6: Take the weekly order count and calculate the quartiles and outlier limit for number of orders in a week

---- Step 7: Put all of the above together

Technical Guides

Pickface Allocation Process

Try it!

Pick face sizing queries