Question regarding Inventory UNITS and DEFAULT COST

gerlandog · October 17, 2014, 2:24pm

Dear guys, I am trying to fully understand this so I do not make more mystakes regarding inventory,
I saw that we have two units, two costs. In my case, both Default Cost are empty.

In this example, I have Skol 600 that is a 600ml bottle of beer that comes in a 24 package. So my Default cost per unit would be 4.375, and my default cost per pack would be 105.

Is this all correct?
the screen here shows everything correctly?
If I go to transactions and buy 12 of them, I would enter 12 UN in the transaction, is that correct?
if I go to transactions and buy 48 of them, Could I enter 2 PACK in the transaction?

THANKS!!!

G.

QMcKay · October 17, 2014, 2:42pm

@gerlandog, that looks correct. That is the way I have mine set up as well.

Yes
Yes

gerlandog · October 17, 2014, 3:07pm

Well, its not working for me…
I have defined the units as we discussed… and I do have 14 bottles of Skol 600

after that I do a transaction, I buy 2 PACKS (48 bottles)…

So I should have 14 bottles that I had + 48 bottles (2 CAJA) that I bought = 62 bottles…

Could I be doing something wrong??

Thanks!!!

G.

QMcKay · October 17, 2014, 3:48pm

I don’t get it. It shows you went from 14 CAJA to 16 CAJA, which is correct.

Your report is for CAJA, not UN. So if you really have 14 bottles + 2 cases, then I would suggest your Report is not computing correctly, since it before purchase, it originally shows 14 CAJA, not 14 UN.

What does your End of Day Record show as CAJA and UN quantities?

Jesse · October 17, 2014, 6:24pm

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.

Jesse · October 17, 2014, 6:26pm

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?

QMcKay · October 17, 2014, 6:43pm

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.

gerlandog · October 17, 2014, 6:45pm

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.

QMcKay · October 17, 2014, 6:54pm

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…

gerlandog · October 17, 2014, 7:26pm

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.

QMcKay · October 17, 2014, 8:18pm

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…

QMcKay · October 17, 2014, 8:22pm

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…

gerlandog · October 17, 2014, 8:37pm

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.

Jesse · October 17, 2014, 9:03pm

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.

QMcKay · October 17, 2014, 9:22pm

@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

gerlandog · October 17, 2014, 9:26pm

you are forgetting actual consumption from ORDERS table… i think… LOL… I just had a CAIPIRINHA and I am not thinkng straight…

G.

QMcKay · October 17, 2014, 9:50pm

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.

QMcKay · October 17, 2014, 10:29pm

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…

gerlandog · October 18, 2014, 12:06pm

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.

gerlandog · October 18, 2014, 5:58pm

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,