Ribbon Bar / Launch HTML5 / Launch KPI
The following reports help monitor your Receiving processes. Each report has example scripts that you can use.
| Info |
|---|
If you don’t like reading, you can watch the video. |
| Panel | ||||||||
|---|---|---|---|---|---|---|---|---|
| ||||||||
None |
Try it!
Pallets and Age in Receiving Bin
<description>
<example report>This report shows the pallets in the Receiving Bin (refer to Bin column) and how long they have been there (refer to DaysOld column)
SQL Script
| Code Block |
|---|
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
<description>
<example report>This is a SYSPRO Query
SQL Script
| Code Block |
|---|
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
<description>
<example report>
SQL Script
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
| Code Block |
|---|
--- 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
<description>
<example report>Enter a stock code that has just come in, sales orders that are waiting on the stock code will display.
SQL Script
| Code Block |
|---|
Putaway per hour
<description>
<example report>
SQL Script
Putaway statistics
<description>
<example report>
SQL Script
select SD.SalesOrder, SM.Customer, AC.Name, SM.OrderStatus, SD.MStockCode as StockCode, SD.MStockDes, SD.MBackOrderQty as BackOrderQty, SM.EntrySystemDate, SM.ReqShipDate
from SysproCompanyTest.dbo.SorDetail SD with (nolock)
INNER JOIN SysproCompanyTest.dbo.SorMaster SM with (nolock) on SM.SalesOrder=SD.SalesOrder
INNER JOIN SysproCompanyTest.dbo.ArCustomer AC with (nolock) on SM.Customer=AC.Customer
Where SD.MBackOrderQty>'0' and SM.OrderStatus IN ('1','2','3','S')
AND SD.MStockCode =@StockCode
ORDER BY SM.EntrySystemDate |
Putaway per hour
Best on a graph in SSRS run each day to see when users are most productive.
SQL Script
| Code Block |
|---|
select DATEPART(HOUR,ActionDate) as HourofDay, count(PalletNumber) as PalletCount
from tblPalletAction PA with (nolock)
where PA.ActionName ='BIN_TRANSFER' AND PA.Reference LIKE 'RECV%'
AND PA.ActionDate<'2018-11-14' and PA.ActionDate>'2016-11-13'
GROUP BY DATEPART(HOUR,ActionDate) |
Putaway statistics
Putaway Report by user.
SQL Script
| Code Block |
|---|
select PA.Operator, LU.OperatorName, Count(PA.PalletNumber) as TotalPalletsPutAway--INTO #totalPA
from tblPalletAction PA with (nolock)
INNER JOIN Warehouse_Control.dbo.tblLicensedUsers LU with (nolock) on PA.Operator=LU.UserName
where PA.ActionName='BIN_TRANSFER' and PA.Reference LIKE 'RECV%' AND PA.ActionDate>='2018-01-01' and PA.ActionDate<'2018-12-01'
GROUP BY PA.Operator, LU.OperatorName |