None

This script is used when we plan to pick units from the pick face but all full cartons from a break-bulk or bulk location. Order picking sizes would typically involve a few full pallets, plenty of full carton picks, and some loose unit picking. These warehouses are normally laid out with bulk bins holding full pallets, a Break-bulk area holding “open” pallets (often with more than one SKU), and a range of dedicated pick bins with a single SKU assigned to each bin.

The Sales order reservation rule is normally configured to reserve full pallets where possible, then full cartons where possible, and then finally, the loose units directly from the pick face. In this sort of warehouse, the dedicated pick faces are normally on the floor with random storage break-bulk above the pick faces but are still accessible using a ladder or order picker/man-up, and full pallets are placed high in the racks. Another form of this warehouse can be a dedicated pick face area, separate break-bulk storage warehouse/area, and full pallets, possibly in an offsite warehouse. Pick slips can be split by picking area and given to different MHEs (Material Handling Equipment)

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

None

premium_SYSPRO_DATA_EXTRACTION_CARTONS_AND_UNITS_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. 1C: Script 1 C will subtract pan size from the order qty. This would be for a site where full boxes are picked from bulk.
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.

D 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 1c: Get all lines through PF (subtract multiples of 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

Cartons from Bulk units from pickface

Try it!

Pick face sizing queries