Discussions Linnworks.net Forums General Discussion and Queries
Start a new topic

Custom Script Help Needed

Hi,


I am trying to create a custom script in order to pull back a quick list of inventory products which have stock levels lower than the minimums we have set in Linnworks.net (i.e. a low inventory report). We have over 50 suppliers so this allow us to keep inventory 'in stock' more efficiently. I am struggling getting the report to show the 'Supplier' and 'Supplier Code' titles however from the inventory. Here is what I have got so far, could anyone suggest a tweak??



SELECT

si.ItemNumber, si.ItemTitle, sLoc.Location, sl.Quantity, sl.InOrderBook, sl.OnOrder, (sl.Quantity+sl.InOrderBook+sl.OnOrder) as TotalQty, sl.MinimumLevel, (sl.MinimumLevel-sl.Quantity+sl.InOrderBook+sl.OnOrder) as QtyToOrder, si.CategoryId, il.BinRackNumber

FROM

StockItem si

LEFT OUTER JOIN StockLevel sl on si.pkStockItemId = sl.fkStockItemId

LEFT OUTER JOIN StockLocation sLoc on sl.fkStockLocationid = sLoc.pkStockLocationId

LEFT OUTER JOIN ItemLocation il on il.fkStockItemId = si.pkStockItemID AND il.fkLocationId = sLoc.pkStockLocationId

WHERE

si.bLogicalDelete = 0

AND

sl.Quantity + sl.InOrderBook + sl.OnOrder <= sl.MinimumLevel

AND

sl.MinimumLevel > -1

ORDER BY

si.CategoryId, si.ItemNumber, sLoc.Location



1 person likes this idea

  Try this?

SELECT
si.ItemNumber, si.ItemTitle, sLoc.Location, sl.Quantity, sl.InOrderBook, sl.OnOrder, su.SupplierName, (sl.Quantity+sl.InOrderBook+sl.OnOrder) as TotalQty, sl.MinimumLevel, (sl.MinimumLevel-sl.Quantity+sl.InOrderBook+sl.OnOrder) as QtyToOrder, si.CategoryId ,il.BinRackNumber

FROM
StockItem si
LEFT OUTER JOIN StockLevel sl on si.pkStockItemId = sl.fkStockItemId
LEFT OUTER JOIN StockLocation sLoc on sl.fkStockLocationid = sLoc.pkStockLocationId
LEFT OUTER JOIN ItemLocation il on il.fkStockItemId = si.pkStockItemID AND il.fkLocationId = sLoc.pkStockLocationId
LEFT OUTER JOIN ItemSupplier pp on si.pkStockItemId = pp.fkStockItemId
LEFT OUTER JOIN Supplier su on pp.fkSupplierId = su.pkSupplierID

WHERE
si.bLogicalDelete = 0

AND
sl.Quantity + sl.InOrderBook + sl.OnOrder <= sl.MinimumLevel

AND
sl.MinimumLevel > -1

ORDER BY
si.CategoryId, si.ItemNumber, sLoc.Location 

  

Hello,


Thank you for your question.


You can try this query as well:



SELECT

    si.ItemNumber, 

    si.ItemTitle, 

    sLoc.Location, 

    'Bin Locn' = ISNULL(il.BinRackNumber,''),

    'In stock' = sl.Quantity, 

    'In Open Orders' = sl.InOrderBook, 

    'Due' = sl.OnOrder, 

    sl.MinimumLevel, 

    'Min Required' = (sl.MinimumLevel- (sl.Quantity-sl.InOrderBook) -sl.OnOrder), 

    c.CategoryName, 

    'Supplier' = ISNULL(sup.SupplierName,''), 

    'Supplier code' = ISNULL(iSup.SupplierCode,''), 

    'Supplier Barcode' = ISNULL(iSup.SupplierBarcode,''), 

    'Purchase Price ' = ISNULL(iSup.KnownPurchasePrice,''), 

    'Min Order Qty' = ISNULL(iSup.SupplierMinOrderQty,''), 

    'Pack Size'= ISNULL(iSup.SupplierPackSize,'')

FROM

    StockItem si WITH (NOLOCK)

    INNER JOIN ProductCategories c WITH (NOLOCK) ON c.CategoryId = si.CategoryId

    LEFT OUTER JOIN StockLevel sl WITH (NOLOCK) ON si.pkStockItemId = sl.fkStockItemId

    LEFT OUTER JOIN StockLocation sLoc WITH (NOLOCK) ON sl.fkStockLocationid = sLoc.pkStockLocationId

    LEFT OUTER JOIN ItemLocation il  WITH (NOLOCK) ON il.fkStockItemId = si.pkStockItemID AND il.fkLocationId = sLoc.pkStockLocationId

    LEFT OUTER JOIN ItemSupplier iSup WITH (NOLOCK) ON iSup.fkStockItemId = si.pkStockItemID AND iSup.IsDefault = 1

    LEFT OUTER JOIN Supplier sup WITH (NOLOCK) ON sup.pkSupplierID = iSup.fkSupplierID AND sup.bLogicalDelete = 0

WHERE

    si.bLogicalDelete = 0

    AND (sl.MinimumLevel - (sl.Quantity-sl.InOrderBook) - sl.OnOrder) >= sl.MinimumLevel    

    AND sl.MinimumLevel > 0

ORDER BY

    c.CategoryName, si.ItemNumber, sLoc.Location



Hope it helps!


Kind regards,

Julietta


Technical Support


Thank you both of you for the answers. Helps a lot!!

Login to post a comment