Minimum STOCK Report V2.00

Version 4 V4 Custom Reports
1

Dear everybody… I noticed that if in the last work period I did either not buy or have stock 0 and no consumption (We have some negatives like Ham or Cheese) of an invertory item, Samba automatically discarded it from the PeriodicConsumptionItems table. So my Custom Report Minimun Stock would not work properly, becuase those maybe you want to know wich items were left out or maybe you want to rebuy something that you stoped buying (seasonal prodcts). So I took a different approach, and instead of reporting from PeriodicConsumptionItems, now I am reporting from Inventory Items.

For those who had your Report working, just COPY and PASTE the new SQL and it will still be working, but better now…

SQL

SELECT
  [InventoryItems].[Name],
  CONVERT(Int,ISNULL([PhysicalInventory],ISNULL(([InStock]+[Added]-[Removed]-[Consumption])*[UnitMultiplier],0))) as StockFisicoUN,
  CONVERT(Int,
	        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,
	        CASE WHEN CharIndex('{"TN":"StockIdeal","TV":"',cast([CustomTags] as nvarchar(4000))) <> 0
	        THEN
	             CONVERT(Integer,substring(cast([CustomTags] as nvarchar(4000)),CharIndex('{"TN":"StockIdeal","TV":"',
				 cast([CustomTags] as nvarchar(4000)))+25,CharIndex('"',cast([CustomTags] as nvarchar(4000)),
                 CharIndex('{"TN":"StockIdeal","TV":"',cast([CustomTags] as nvarchar(4000)))+25)-
				 CharIndex('{"TN":"StockIdeal","TV":"',cast([CustomTags] as nvarchar(4000)))-25))
			ELSE
			     '0'
		    END) as [StockIdeal],
   CONVERT(Int,
	        CASE WHEN CharIndex('{"TN":"StockIdeal","TV":"',cast([CustomTags] as nvarchar(4000))) <> 0
	        THEN
	             CONVERT(Integer,substring(cast([CustomTags] as nvarchar(4000)),CharIndex('{"TN":"StockIdeal","TV":"',
				 cast([CustomTags] as nvarchar(4000)))+25,CharIndex('"',cast([CustomTags] as nvarchar(4000)),
                 CharIndex('{"TN":"StockIdeal","TV":"',cast([CustomTags] as nvarchar(4000)))+25)-
				 CharIndex('{"TN":"StockIdeal","TV":"',cast([CustomTags] as nvarchar(4000)))-25))
			ELSE
			     '0'
		    END) - CONVERT(Int,ISNULL([PhysicalInventory],ISNULL(([InStock]+[Added]-[Removed]-[Consumption])*[UnitMultiplier],0))) as Diferencia


FROM [InventoryItems]

LEFT OUTER JOIN [MenuItems] on [MenuItems].[Name] = [InventoryItems].[Name]
LEFT OUTER JOIN [PeriodicConsumptionItems] on ([PeriodicConsumptionItems].[InventoryItemName]=[InventoryItems].[Name] AND [WarehouseConsumptionId] = (SELECT TOP 1 [Id] FROM [PeriodicConsumptions] ORDER BY [Id] DESC))

WHERE ISNULL([PhysicalInventory],ISNULL(([InStock]+[Added]-[Removed]-[Consumption])*[UnitMultiplier],0)) <= 
      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 [InventoryItems].[Name]
2 Likes
2

I think we need to state we should configure a StockMinimo product custom tag.

3

Dear @Emre, that´s why I wrote:

This is the original post:

Thanks!!!

G.

4

I like that idea emre StockMinimo will be awesome

5

What @emre was saying is for this report to work correctly you need to define a StockMinimo product custom tag. This report was built upon a StockMinimo product custom tag.

6

Dear gerlandog,

I have a strange problem with your code and can´t find the error.
In my Custom Report the Physical Stock is always 8 Items higher than the real Stock.

Can you help me to solve this?

BR

Kevin

7

maybe the last changes that made @Emre make the report behave strangly…

here is the code I use:

this sql was wrongly postd here, I am sorry... look down into posts and you will find the correct one

Try this one and tell me how it goes.

G.

8

Dear G.

The code dosen´t work.
In SQL view it tells me there is an error in converting numeric into numeric-datatype.

BR

Kevin

9

I just copy pasted his exact code into a SQL query and it works just fine. There must be something your missing or doing and we are not aware of it.

99855bcedaa265cd.png1669×989 184 KB

10

@gerlandog, I’m confused why you continue to use this criteria. It is more expensive than something like:

WHERE [PeriodicConsumptionId] IN (SELECT max([Id]) FROM [PeriodicConsumptions])
11

I get this in SQL query

ef0b26967db4eb7e.JPG945×469 69.9 KB

12

Be sure your running it in the right database. It does not define it.

13

Wich one should I use?

14

You need to either tell it which one in the SQL or you need to click on your database on the left in object explorer and then run the query.

15

I got only one database running

fc5d1d9c3e0c0d80.JPG1272×613 113 KB

I selected the database and then run query then i get the error.

Strange?!?!?!

16

Any other idea where i can look or change to use the code from the first post.
I really like to use it.
I don´t need to see the cost or total cost.

17

I’m getting the same error (overflow). Change this line:

CONVERT(numeric(6,2),ROUND([Cost]*ISNULL([PhysicalInventory],([InStock]+[Added]-[Removed]-[Consumption])),2)) as CostoTOT

to this:

CONVERT(numeric(7,2),ROUND([Cost]*ISNULL([PhysicalInventory],([InStock]+[Added]-[Removed]-[Consumption])),2)) as CostoTOT

The change is small… numeric(6,2) has been changed to numeric(7,2)

1 Like
18

numeric(6,2) means a number that have 6 digits including decimal places so it can be at most 9999.99. we can try increasing digits to a larger number (eg 10) and decimals to 3.

Can you please try replacing numeric(6,2) parts to numeric(10,3) ?

2 Likes
19

Ah so that explains why mine worked. It was test and did not use up all the numbers.

20

code is working now but i still get wrong physical numbers in stock.