None

This script exports the SYSPRO sales data history and then runs through every sales order line and removes quantities. Here, the warehouse would have picked a full pallet quantity – as set in the Pallet Size filed on SYSPRO’s InvWarehouse table.

The result is that any order line quantity less than a full pallet would be expected to be picked from the pick face bin.

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

None

premium_SYSPRO_DATA_EXTRACTION_FULL PALLETS_THEN_PICK_FACE_product_quickquide_QG - v0.1.pdf

Try it!

Pick face sizing queries

The steps below are per stock code.

Calculate total qty through pickface based on SYSPRO sales orders in a time frame.
1B: If full pallets are coming from bulk, these must be subtracted from pickface demand using script Step 1b suggestions. This does not have to be the pallet qty in SYSPRO but could be a Custom form field.
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 1b: Get all lines through PF (subtract full pallet qty from SYSPRO InvWarehouse default pallet qty)

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,  IW.PalletQty,
CASE WHEN (ISNULL(IW.PalletQty,0)) > SD.MOrderQty ---if order qty is less than pallet size then take from PF
THEN SD.MOrderQty 
ELSE  
 SD.MOrderQty - (floor(SD.MOrderQty/ISNULL(NULLIF(IW.PalletQty,0),1))*IW.PalletQty)
end
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
inner join SysproCompanyTest.dbo.InvWarehouse IW with (nolock) on IW.StockCode=SD.MStockCode and IW.Warehouse=SD.MWarehouse
WHERE   SM.OrderStatus IN ('0','1','2','3','4','8','9')
	                    AND ((SM.ReqShipDate >= @FromDate) AND (SM.ReqShipDate < @ToDate))
	                    AND (SD.MWarehouse = 'SA') --- 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


---OPTIONAL instead of 1a or 1b: Step 1c: Get all lines through PF (subtract pan size)

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, 
CASE WHEN (ISNULL(IM.PanSize,0)) > SD.MOrderQty ---if order qty is less than pan size then take from PF
THEN SD.MOrderQty 
ELSE  
 SD.MOrderQty - (floor(SD.MOrderQty/ISNULL(NULLIF(IM.PanSize,0),1))*IM.PanSize) ---if order qty is greater than or equal to pan, take the remainder
end
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
inner join SysproCompanyTest.dbo.InvMaster IM with (nolock) on IM.StockCode=SD.MStockCode
WHERE   SM.OrderStatus IN ('0','1','2','3','4','8','9')
	                    AND ((SM.ReqShipDate >= @FromDate) AND (SM.ReqShipDate < @ToDate))
	                    AND (SD.MWarehouse = 'SA') --- 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

Pallets from Bulk Rest from Pick face

Try it!

Pick face sizing queries