Ribbon Bar / Launch HTML5 / Launch KPI

The following reports help monitor your Receiving processes. Each report has example scripts that you can use.

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

None

Try it!

Pallets and Age in Receiving Bin

This report shows the pallets in the Receiving Bin (refer to Bin column) and how long they have been there (refer to DaysOld column)

Example Report

SQL Script

select B.Bin, P.PalletNumber, PIT.StockCode, PIT.Qty, P.DateActivated, P.Status, DATEPART(dd,(GETDATE()-P.DateActivated)) as DaysOld, ISNULL(PIT.PurchaseOrder,'') as PurchaseOrder, ISNULL(PIT.ShipmentReference,'') as ShipRef
from WarehouseCompanyTest.dbo.tblPalletItem PIT with (nolock)
INNER JOIN WarehouseCompanyTest.dbo.tblPallet P with (nolock) on P.PalletNumber=PIT.PalletNumber
INNER JOIN WarehouseCompanyTest.dbo.tblBin B with (nolock) on P.Bin=B.Bin
where B.Bin In ('RECV01','RECV0B') and PIT.Qty>'0' and P.Status<>'INACTIVE'
ORDER BY B.Bin, P.DateActivated, P.PalletNumber, PIT.StockCode

Containers arriving based on estimated arrival date

This is a SYSPRO Query

SQL Script

SELECT   LCE.Vessel,LCH.VesselName,  LCE.ShipmentReference, LCE.DepartureDate,  LCH.EstArrivalDate, LCH.ActArrivalDate, LCH.[Route], LCE.Supplier
FROM         SysproCompanyTest.dbo.LctCostEleDet LCE INNER JOIN
                     SysproCompanyTest.dbo.LctShipmentHdr LCH with (nolock) ON LCE.Vessel = LCH.Vessel AND LCE.DepartureDate = LCH.DepartureDate
                     where LCH.ActArrivalDate IS NULL AND LCH.EstArrivalDate>'2017-01-01'
                     GROUP BY LCE.Vessel,LCH.VesselName,  LCE.ShipmentReference, LCE.DepartureDate,  LCH.EstArrivalDate, LCH.ActArrivalDate, LCH.[Route], LCE.Supplier
                     ORDER BY LCH.EstArrivalDate

Job received destined for orders

This report looks for jobs that have been received, stock is still on job receipt pallet, and matches to outstanding sales orders that do not have pick slips out already for those lines. I.e. indicates what could be released now.

SQL Script

--- sales order to pallet reserved view
Select PR.StockCode, PR.Line, sum(PR.QtyOrdered) as Qtyres, PSS.SourceNumber into #SourceRes
from tblPalletReserved PR with (nolock)
InneR JOIN tblPickingSlipSource PSS with (nolock) ON PR.PickingSlipNumber=PSS.PickingSlipNumber
INneR JOIN SysproCompanyTest.dbo.SorDetail SD with (nolock) on SD.SalesOrder=PSS.SourceNumber
where PR.Status='OPEN'
group by PSS.SourceNumber, PR.StockCode, PR.Line 


select PA.PalletNumber, PA.ActionDate as JobRecDate, PA.Module, PIT.JobNumber, PA.StockCode, PA.Quantity as JobQty, PIT.Qty as QtyonPallet, SM.SalesOrder,SM.ReqShipDate, SM.EntrySystemDate, SD.MBackOrderQty, #SourceRes.Line, #SourceRes.Qtyres
from tblPalletAction PA with (nolock)
inneR JOIN tblPalletItem PIT with (nolock) on PIT.PalletNumber=PA.PalletNumber
Inner join SysproCompanyTest.dbo.SorDetail SD with (nolock) on SD.MStockCode=PA.StockCode
inner join SysproCompanyTest.dbo.SorMaster SM with (nolock) on SM.SalesOrder=SD.SalesOrder
left join #SourceRes on SD.SalesOrder=#SourceRes.SourceNumber and SD.SalesOrderLine=#SourceRes.Line
where PA.Module LIke 'JOB RECEIPT' and SD.MBackOrderQty>'0' and SM.OrderStatus IN ('1','2','3','S','8') and PIT.Qty>'0' and #SourceRes.Qtyres is NULL
ORDER BY SM.ReqShipDate

Orders waiting on a stock code

Enter a stock code that has just come in, sales orders that are waiting on the stock code will display.

Technical Guides

Receiving KPI Reports

Try it!

Pallets and Age in Receiving Bin

SQL Script

Containers arriving based on estimated arrival date

SQL Script

Job received destined for orders

SQL Script

Orders waiting on a stock code

SQL Script

Putaway per hour

SQL Script

Putaway statistics

SQL Script