Simple Minimum Stock Report

[gerlandog]

I was in the need of something simple, that would allow me to add a field for the minimal point of stock of items and just a report to show me that… so here I go:

1. ADD a Product TAG
   

   

2. ADD some values to check that I made things good… LOL
   

   

3. ADD this script:

   @@StockMinimo:

   SELECT [InventoryItemName],
   CONVERT(Integer,
   CASE WHEN CharIndex(‘{“TN”:“StockMinimo”,“TV”:"’,cast([CustomTags] as nvarchar(4000))) <> 0
   THEN
   CONVERT(Integer,substring(cast([CustomTags] as nvarchar(4000)),CharIndex(‘{“TN”:“StockMinimo”,“TV”:"’,
   cast([CustomTags] as nvarchar(4000)))+26,CharIndex(‘"’,cast([CustomTags] as nvarchar(4000)),
   CharIndex(‘{“TN”:“StockMinimo”,“TV”:"’,cast([CustomTags] as nvarchar(4000)))+26)-
   CharIndex(‘{“TN”:“StockMinimo”,“TV”:"’,cast([CustomTags] as nvarchar(4000)))-26))
   ELSE
   ‘0’
   END) as [StockMinimo],
   CONVERT(INT,([InStock]+[Added]-[Removed]-[Consumption])*[UnitMultiplier]) as StockFisicoUN

   FROM [PeriodicConsumptionItems]

   LEFT OUTER JOIN [MenuItems] on [MenuItems].[Name] =[PeriodicConsumptionItems].[InventoryItemName]

   WHERE [WarehouseConsumptionId] = (SELECT TOP 1 [Id] FROM [PeriodicConsumptions] ORDER BY [Id] DESC)
   AND
   CONVERT(INT,([InStock]+[Added]-[Removed]-[Consumption])*[UnitMultiplier]) <=
   CONVERT(Integer,
   CASE WHEN CharIndex(‘{“TN”:“StockMinimo”,“TV”:"’,cast([CustomTags] as nvarchar(4000))) <> 0
   THEN
   CONVERT(Integer,substring(cast([CustomTags] as nvarchar(4000)),CharIndex(‘{“TN”:“StockMinimo”,“TV”:"’,
   cast([CustomTags] as nvarchar(4000)))+26,CharIndex(‘"’,cast([CustomTags] as nvarchar(4000)),
   CharIndex(‘{“TN”:“StockMinimo”,“TV”:"’,cast([CustomTags] as nvarchar(4000)))+26)-
   CharIndex(‘{“TN”:“StockMinimo”,“TV”:"’,cast([CustomTags] as nvarchar(4000)))-26))
   ELSE
   ‘0’
   END)

   ORDER BY [InventoryItemId]

4. ADD this Report:

   [Stock a Reponer:2, 1, 1]

   Item|Stock Minimo|Stock Actual
   @@StockMinimo

There you go… simple… fast… nothing fancy.

Hope it works!!!

G.

[emre]

So that works fine for inventory items that matches to the name of the product. Nice idea. Thanks.

[gerlandog]

Yes, I tought that was natural, since I had it this way since day 0. As a rule we used the same names just to be on the safe side.

G.

[Jesse]

Gotta love it. You were basically told its not possible, yet you found a way. Another example of just how great Samba really is! I like this btw I may use it.

[gerlandog]

I tought you were going to say “Another example of just how clever you are”, but no…

G.

[Jesse]

Haha you are clever.