You have it configured to show % of case if you enter it like that @gerlandog so it will not show single units on inventory its going to show cases.
Multiplier needs to be set at 1 to show single units… You would purchase single unit quantities if you do it this way. So your quantity on the purchase would be 48 instead of 2. Your default cost would be same for unit and case if you use multiplier of 1.
Does your price the supplier charges you never change?
I don’t agree @Jesse.
It is my belief that you should not use a Multiplier of 1 unless your Base Unit and Transaction Unit are the same, which is sometimes the case with certain products. However, most of the time, your Base Unit and Transaction Unit should be different and your Multiplier therefore should not be set to 1.
I tend to consider the Transaction Unit to be the unit in which I Purchase from the Supplier.
You should be able to to Purchase in either Unit, and leave the Multiplier set to 24 to indicate there are 24 Base Units in 1 Transaction Unit (24 Bottles in 1 Case).
The Default Cost for each of the Units was a feature that I asked for, since supposedly, the price rarely changes, and it saves the need to enter costs all the time. You should be able to override it if it does change.
OK, first things first… @QMcKay, this is not my report… its sambapos inventory report @Jesse, no I have not configured it to show % of case, I changed it three months ago… remember, I had a hard work with stock after that… my base unit is bottles… in thsi case UN.
now…
my configuration is… as you can see in the first posts screen
BASE UNIT: UN
TRANSACTION UNIT: CAJA MULTIPLIER: 24
this means that…
if I buy in UN its ONE bottle… and that when I buy in CAJA it should be translated to 24 bottles…
The inventory report shows TRANSACTION UNIT, wich is NOT correct, it should show BASE UNIT which is the WORKING UNIT
I made a test…
initial sock 14 CAJA skol 600
I made a purchase of
2 UN skol 600
2 CAJA skol 600
new stock after closing transaction and period
18 CAJA skol 600
something here is not correct, and if everything is workng acording it should work, what I am missing here? what I am doing worng here?
Thanks!!!
G.
I remember that discussion, and I think you have your Inventory set properly.
Sorry, my bad, I only realized that after looking more closely. That said, you may want to create your own Report because…
That appears to be the case with this built-in Report.
Since you cannot dictate which unit the Report uses - it’s canned, and the decision was made to report on Transaction Unit, which IMO is not the wrong choice - it might be time to create your own, since it does appear to be calculating improperly.
P.S. I think your Inventory Item is properly set up, and you should be able to make a purchase in either unit.
EDIT: is the Inventory Purchase Report showing correct values? EDIT 2: I’m going to say mine in not reporting properly. I can’t say for sure though since I handle purchases and stock transfers through PHP, and I’ve never looked at these reports before.
I think I understand what is going on in this Inventory Purchase report now: it doesn’t distinguish between a Purchase Transaction and a Transfer Transaction…
That is fine by me, the problem is not the unit… is the calculation… if its shown in BASE or TRANSACTION unit, I really dont care… I have my own report… the problem started when as I corrected all my units i tried to enter a box of beer instead of the 24 bottles i used to enter…
And this all came up because I saw this issue: Decimal position in TRANSACTIONS and started to test things.
EDIT1: I was not using purchase inventory report until NOW I was using BASE unit as purchase unit too, just until I had all perfectly set up. but… inventory and inventory purchase reports seems to be wrong, at least for me…
THANKS!!!
G.
LOL, this report does not work for me so good… it can’t distinguish between a Purchase and a Transfer, and the Date Filter is a little funny, because it gives different results whether it is Today or Oct 17 (which is today).
This report appears to be ok, but it completely disregards the Date Filter…
Not sure what to say… from my last post, you can see they behave a bit strangely IMO (I agree with you), though I don’t know that they are wrong. I think they are old … so let’s build a new one in SQL!
Ok, you start…
And I’ll go next…
1 Like
I alreaady have two reports, one that shows your stock, and one that shows what items to buy.
The problem is that both work outside WORK PERIOD…
Stock:
SELECT [InventoryItemName],
CASE WHEN [BaseUnit] = [TransactionUnit] THEN
CONVERT(INT,ISNULL([PhysicalInventory],([InStock]+[Added]-[Removed]-[Consumption]))+
ISNULL((SELECT TOP 1 [Quantity] FROM [InventoryTransactions]
WHERE [InventoryItem_Id] = [PeriodicConsumptionItems].InventoryItemId AND [Date] >= CONVERT(varchar(10),GETDATE(),121)
ORDER BY [Id] DESC),0))
ELSE
CONVERT(INT,(CONVERT(numeric(6,2),ISNULL([PhysicalInventory],([InStock]+[Added]-[Removed]-[Consumption])))*[UnitMultiplier]))
END as Stockfisico,
[BaseUnit] as Unidad,
FORMAT(CONVERT(numeric(6,2),[Cost]),'C') as CostoUnit,
FORMAT(CONVERT(numeric(6,2),([Cost]*(ISNULL([PhysicalInventory],([InStock]+[Added]-[Removed]-[Consumption]))))
),'C') as CostoTOT
FROM [PeriodicConsumptionItems]
LEFT OUTER JOIN [InventoryItems] ON [InventoryItemId] = [InventoryItems].[Id]
WHERE [PeriodicConsumptionId] = (SELECT TOP 1 [Id] FROM [PeriodicConsumptions] ORDER BY [Id] DESC)
AND
CONVERT(numeric(6,2),ISNULL([PhysicalInventory],([InStock]+[Added]-[Removed]-[Consumption]))) >= 0.05
ORDER BY [InventoryItemName]
if you want (as I do) to have a estimate total money in stock:
SELECT FORMAT(SUM(CONVERT(numeric(6,2),CONVERT(numeric(6,2),([Cost]*(CONVERT(numeric(6,2),ISNULL([PhysicalInventory],([InStock]+[Added]-[Removed]-[Consumption])))))))),'C') as Costo
FROM [PeriodicConsumptionItems]
WHERE [PeriodicConsumptionId] = (SELECT TOP 1 [Id] FROM [PeriodicConsumptions] ORDER BY [Id] DESC)
AND
CONVERT(numeric(6,2),ISNULL([PhysicalInventory],([InStock]+[Added]-[Removed]-[Consumption]))) >= 0.05
Stock 0 to re-buy:
SELECT
[InventoryItems].[Name],
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,ISNULL([PhysicalInventory],ISNULL(([InStock]+[Added]-[Removed]-[Consumption])*[UnitMultiplier],0))) as StockFisicoUN
FROM [InventoryItems]
LEFT OUTER JOIN [MenuItems] on [MenuItems].[Name] = [InventoryItems].[Name]
LEFT OUTER JOIN [PeriodicConsumptionItems] on ([PeriodicConsumptionItems].[InventoryItemName]=[InventoryItems].[Name] AND [PeriodicConsumptionId] = (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]
for this one you need two product tags: StockMinimo and StockIdeal
Hope this helps!!!
G.
It can be configured either way. The multiplier is just that… a multiplier… if you set it to 1 it will track it per unit… if you set it to 24 it tracks it by 24s…
This does not have to be the case. It makes the most sense yes but if you want the system to track it per unit then multiplier needs to be set to 1. You can let the purchase transaction dictate what your actual Supplier Puchase qty is.
What I just said above is with understanding that your using the default reports… if your using custom reports then it does not matter as much and you should use Transaction Unit as you described.
EDIT: an example of when I use Multiplier of 1 in my business:
I track bottled water sales and I want it tracked per unit so when I look at the built in report it reflects how many bottles not % of cases is left. I set my multiplier to 1 and I fill out my purchases with total bottles not cases. I simply fill in the total price I paid my supplier and it fills in the per unit price.
1 Like
@gerlandog, Try this, and let me know how it works for you. It appears to be accurate for me, though it disregards my stock transfers from 1 warehouse to another. I think it should work for most people though.
This pulls Periodic Consumption from last CLOSED WorkPeriod and adds it with Inventory Purchase Transactions from the currently OPEN WorkPeriod (so you need to run this when you have a WorkPeriod OPEN for it to work properly).
Set your Warehouse Name at the Top where I have the word ‘SHOP’
use [SambaPOS4]
declare @Warehousename varchar(30) = 'SHOP'
SELECT
[Item]
,[BaseUnit]
,[Multiplier]
,[TransactionUnit]
--,[Unit]
,sum([Qtx]) as [Qtx]
,sum([Qbase]) as [Qbase]
FROM (
SELECT
-- itx.[Id]
--,[InventoryTransactionDocumentId]
--,[InventoryTransactionTypeId]
--,[SourceWarehouseId] as [WHsrcId]
--,[TargetWarehouseId] as [WHtgtId]
--,wh1.[Name] as [WHsrc]
--,wh2.[Name] as [WHtgt]
ii.[Name] as [Item]
--,[Date]
,[BaseUnit]
,[Multiplier]
,[TransactionUnit]
,[Unit]
,CASE
WHEN [Unit]=[TransactionUnit] THEN [Quantity]*1
WHEN [Unit]=[BaseUnit] THEN [Quantity]*[Multiplier]
END as [Qtx]
,[Quantity]*[Multiplier] as [Qbase]
--,[Price] as [UnitPrice]
--,[InventoryItem_Id]
--,([Quantity]*[Price]) as [TotalPrice]
FROM [InventoryTransactions] itx
left join [Warehouses] wh1 on wh1.[Id]= itx.[SourceWarehouseId]
left join [Warehouses] wh2 on wh2.[Id]= itx.[TargetWarehouseId]
left join [InventoryItems] ii on ii.[Id]= itx.[InventoryItem_Id]
WHERE 1=1
AND ([TargetWarehouseId]=(SELECT [Id] FROM [Warehouses] WHERE [Name]=@Warehousename)
AND [Date]>=(select [StartDate] FROM [WorkPeriods] WHERE [Id]=(select max([Id]) FROM [WorkPeriods] WHERE [StartDate]=[EndDate]))--'2014-10-17 10:39:24.827'
)
--ORDER BY wh1.[Name], ii.[Name]
UNION
SELECT
-- wp.[Id] as [WPID]
--, pci.[Id] as [pciID]
--, pc.[Id] as [pcID]
--, [WarehouseConsumptionId] as [wcID]
--, wc.[WarehouseId] as [whID]
--, wh.[Name] as [Warehouse]
--, ii.[GroupCode] as [ItemType]
ii.[Name] as [Item]
, ii.[BaseUnit]
, pci.[UnitMultiplier] as [Multiplier]
, ii.[TransactionUnit]
, pci.[UnitName] as [Unit]
, CONVERT(numeric(6,3),ISNULL([PhysicalInventory],([InStock]+[Added]-[Removed]-[Consumption]))) as [Qtx]
, (CONVERT(numeric(6,2),ISNULL([PhysicalInventory],([InStock]+[Added]-[Removed]-[Consumption])))*[UnitMultiplier]) as [Qbase]
FROM [PeriodicConsumptionItems] pci
left join [PeriodicConsumptions] pc on pc.[Id]=pci.[PeriodicConsumptionId]
left join [WarehouseConsumptions] wc on wc.[PeriodicConsumptionId]=pc.[Id]
left join [InventoryItems] ii on ii.[Id]=pci.[InventoryItemId]
left join [Warehouses] wh on wh.[Id]=wc.[WarehouseId]
left join [WorkPeriods] wp on wp.[Id]=pc.[WorkPeriodId]
WHERE 1=1
AND (wp.[Id] = (SELECT max([Id]) FROM [WorkPeriods] WHERE [StartDate]!=[EndDate])
AND pci.[WarehouseConsumptionId]=wc.[Id]
AND wh.[Name]=@Warehousename
)
--ORDER BY [Item]
) iMovement
GROUP BY
[Item]
,[BaseUnit]
,[Multiplier]
,[TransactionUnit]
--,[Unit]
ORDER BY [Item]
EDIT: I just updated it to show quantities in both units
1 Like
you are forgetting actual consumption from ORDERS table… i think… LOL… I just had a CAIPIRINHA and I am not thinkng straight…
G.
LOL, not that far yet. I know you want on-the-fly, but this should at least show what you consumed yesterday and includes Inventory Purchases made today.
For the previous Query, I mentioned that it disregards Stock Transfers from 1 Warehouse to another.
This is the same Query with a couple additional lines that will take into account those Transfers. You can also use it even if you only have 1 Warehouse and/or don’t do Transfers…
use [SambaPOS4]
declare @Warehousename1 varchar(30) = 'SHOP'
SELECT
[Item]
,[BaseUnit]
,[Multiplier]
,[TransactionUnit]
--,[Unit]
,sum([Qtx]) as [Qtx]
,sum([Qbase]) as [Qbase]
FROM (
SELECT
[Item]
,[BaseUnit]
,[Multiplier]
,[TransactionUnit]
,sum([Qtx]) as [Qtx]
,sum([Qbase]) as [Qbase]
FROM (
SELECT
-- itx.[Id]
--,[InventoryTransactionDocumentId]
--,[InventoryTransactionTypeId]
--,[SourceWarehouseId] as [WHsrcId]
--,[TargetWarehouseId] as [WHtgtId]
--,wh1.[Name] as [WHsrc]
--,wh2.[Name] as [WHtgt]
ii.[Name] as [Item]
--,[Date]
,[BaseUnit]
,[Multiplier]
,[TransactionUnit]
--,[Unit]
,CASE
WHEN [Unit]=[TransactionUnit] THEN
CASE WHEN [SourceWarehouseId]=(SELECT [Id] FROM [Warehouses] WHERE [Name]=@Warehousename1) THEN
[Quantity]*-1
ELSE [Quantity]
END * 1
WHEN [Unit]=[BaseUnit] THEN
CASE WHEN [SourceWarehouseId]=(SELECT [Id] FROM [Warehouses] WHERE [Name]=@Warehousename1) THEN
[Quantity]*-1
ELSE [Quantity]
END * [Multiplier]
END as [Qtx]
,CASE WHEN [SourceWarehouseId]=(SELECT [Id] FROM [Warehouses] WHERE [Name]=@Warehousename1) THEN
[Quantity]*-1
ELSE [Quantity]
END * [Multiplier] as [Qbase]
--,[Price] as [UnitPrice]
--,[InventoryItem_Id]
--,([Quantity]*[Price]) as [TotalPrice]
FROM [InventoryTransactions] itx
left join [Warehouses] wh1 on wh1.[Id]= itx.[SourceWarehouseId]
left join [Warehouses] wh2 on wh2.[Id]= itx.[TargetWarehouseId]
left join [InventoryItems] ii on ii.[Id]= itx.[InventoryItem_Id]
WHERE 1=1
AND ([TargetWarehouseId]=(SELECT [Id] FROM [Warehouses] WHERE [Name]=@Warehousename1) OR [SourceWarehouseId]=(SELECT [Id] FROM [Warehouses] WHERE [Name]=@Warehousename1))
AND [Date]>=(select [StartDate] FROM [WorkPeriods] WHERE [Id]=(select max([Id]) FROM [WorkPeriods] WHERE [StartDate]=[EndDate]))--'2014-10-17 10:39:24.827'
--ORDER BY wh1.[Name], ii.[Name]
) itx
GROUP BY
[Item]
,[BaseUnit]
,[Multiplier]
,[TransactionUnit]
UNION
SELECT
-- wp.[Id] as [WPID]
--, pci.[Id] as [pciID]
--, pc.[Id] as [pcID]
--, [WarehouseConsumptionId] as [wcID]
--, wc.[WarehouseId] as [whID]
--, wh.[Name] as [Warehouse]
--, ii.[GroupCode] as [ItemType]
ii.[Name] as [Item]
, ii.[BaseUnit]
, pci.[UnitMultiplier] as [Multiplier]
, ii.[TransactionUnit]
--, pci.[UnitName] as [Unit]
, CONVERT(numeric(6,3),ISNULL([PhysicalInventory],([InStock]+[Added]-[Removed]-[Consumption]))) as [Qtx]
, (CONVERT(numeric(6,2),ISNULL([PhysicalInventory],([InStock]+[Added]-[Removed]-[Consumption])))*[UnitMultiplier]) as [Qbase]
FROM [PeriodicConsumptionItems] pci
left join [PeriodicConsumptions] pc on pc.[Id]=pci.[PeriodicConsumptionId]
left join [WarehouseConsumptions] wc on wc.[PeriodicConsumptionId]=pc.[Id]
left join [InventoryItems] ii on ii.[Id]=pci.[InventoryItemId]
left join [Warehouses] wh on wh.[Id]=wc.[WarehouseId]
left join [WorkPeriods] wp on wp.[Id]=pc.[WorkPeriodId]
WHERE 1=1
AND (wp.[Id] = (SELECT max([Id]) FROM [WorkPeriods] WHERE [StartDate]!=[EndDate])
AND pci.[WarehouseConsumptionId]=wc.[Id]
AND wh.[Name]=@Warehousename1
)
--ORDER BY [Item]
) iMovement
GROUP BY
[Item]
,[BaseUnit]
,[Multiplier]
,[TransactionUnit]
--,[Unit]
ORDER BY [Item]
These results show the difference…
I still dont think that the problem is the report… today i will do a test to find out if the transaction is saving in the database the transaction unit qty * multiplier to transform it into base unit or not…
Maybe the report is showing correctly the info…
G.
Dear @Emre, just to fully understand all this…
Do you save fisical stock in a TRANSACTION UNIT basis?
If YES, all our reports MUST do the conversion with [UnitMultiplier], so I will have to do some changes. NO big beal.
Also, if YES, all what we were discussing here with my dear friend is NO relevant, since we are mixing units and multipliers…
So, bottom line…
IF ANSWER IS YES WE SHOUL ALLWAYS USE MULTIPLIER TO CALCULATE STOCK.
Correct me please if I am wrong!!!
THANKS!!!
G,
When you set a multiplier of more than 1 in the inventory report it will show cases not single bottles. If you want that specific report to show single bottles you have to set multiplier to 1 and purchase it by bottle…
When I say cases it can be whatever your purchase unit is… if its sold 24 to case or 24 to caja or 24 to sack… if you have multiplier set to more than 1 it will show how many caja, sack, or case… whatever you named the transaction unit.
You must set multiplier to 1 and purchase it in single units if you want that specific report to show bottles not cases.
Bottom line is that report shows quantity of Transaction Unit you have not base unit. So multiplier of 24 if you purchase 2 caja or 48 un… it will still show 2 in that report. If you set multiplier to 1 and purchase 24 un it will show 24 in that report.
To sum this all up it comes back to what I have been trying to tell you for months… if you want to track a case of beer by the bottle in the inventory and not by the case then your multiplier should be 1 and you should purchase it by the bottle. It does not matter if you normally buy it by case or not… nothing changes all that changes is how it reports it and how you enter it.
Maybe @emre can give us options in future to toggle how it calculates a specific item when its entered in as an inventory item… either per unit or per transaction unit but for now it only shows transaction units in that specific inventory report.
In some instances setting multiplier of 1 is an advantage expecially if items per transaction unit changes with each purchase…Sometimes a case of cokes is 12 sometimes they run a special and i get 14 per case instead of defining two different inventory products I can just purchase it per bottle and enter total price it will calculate unit price for me.
My personal primary reason I set multipliers to 1 on some of my products is simply because my base unit is my selling unit and I want it tracked per unit not per transaction unit. I think its great that @emre has allowed this.
Here is an example with screenshot:
I track my foam cups per unit each sale of a coke product uses a specific size cup. I can accurately track how many cups i sell because its always exactly 1 cup per soda sold… here is the setup to track my 8oz foam cups.
I can use simple custom report to convert it and show it as cases…
With this method I can see live on the fly stock. The inventory report will be accurate for any item that I sell this way. The inventory prediction will be my actual live inventory as I am selling it per item… the only reason it would not be accurate would be theft or damages.
This can be true… but it can also not be considered this way. It can simply be for reporting. Both methods work and both are accurate.
My actual live restaurant I use a mix of both methods… For items I want to track down to single items like my cups, candy bars, etc I use multiplier of 1 and I purchase them by base unit quantity in my reports… (I actually purchase by case from supplier) For my items like tomatos and pickles where I am never sure how many are exactly being sold I set my multiplier as how many per case or sack etc. This allows me to track some items down to item level as I demonstrated and it allows me to track some items per % of case both work together at same time.
OK… we all are correct… perhaps is a semantic issue here…
BASE UNIT for me means the basic unit in wich you sell… ergo the unit in wich I want to TRACK stock.
TRANS UNIT for means the unit I may be buying from a supllier… sometimes (most of the time) we buy in TRANS UNITS (like a whole pack of coke or a whole box of beer). sometimes we buy in units… like some special beers that we do not sell often and I buy them buy the unit, or in my case BASE UNIT. If not so, why in transaction screen we do have the option to choose the unit of purchase?
So… if this is the case… WE ALLWAYS SHOULD BE KEEPING BASE UNIT in the database… ando not trans unit…
maybe I am lost again…
Sorry.
G.