Skip to end of metadata
Go to start of metadata

You are viewing an old version of this page. View the current version.

Compare with Current View Page History

« Previous Version 2 Current »

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)

image-20240416-103651.png

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

image-20240416-103832.png

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.

image-20240416-104033.png

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.

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.

image-20240416-104312.png

SQL Script

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.

image-20240416-104418.png

SQL Script

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
  • No labels