question

GoranJ avatar image
GoranJ asked

FIFO cost of sale - multi warehouses and transfers - set based solution?

Hi, I have a FIFO system based on cursor solution, and I'd like to switch to set based solution. I read great article "FIFO cost of sale" [link text][1]. It's very close to something I need (Mr. Usman Butt's answer), but there are some problems I can't solve it (I don't know how). Here is short description: 1. I have more than one warehouses. 2. I move (transfer) goods from one warehouse to other. These transactions have only quantity without price. Also, article can be moved more than once to other warehouses. 3. Article (product) can be purchased and put to any of the warehouses. 4. Article can be sold from any of the warehouses. This leads to the next test scenario: ![Article movment through warehouses][2] 1. Purchase 10 items of ArticleID 1000 at price 241$ and put them to the WarehouseID 10. All below steps are for the same ArticleID. 2. Move (transfer) 5 items from the WarehouseID 10 to the WarehouseID 11. It left 5 items on the WarehouseID 10. Value on WarehouseID 10 is 5 x 241$. Value on the WarehouseID 11 is 5 x 241$. 3. Move 3 items from the WarehouseID 11 to the WarehouseID 12. It left 2 items on the WarehouseID 11. 4. Move 2 items from the WarehouseID 12 to the WarehouseID 13. It left 1 item on the WarehouseID 12. 5. Sell 1 item from the WarehouseID 13. Cost of sold article is 1 x 241$ What I have done: 1. As in the original "FIFO cost of sale" article I take Stock table and add few columns: - WarehouseID int - To keep track of the original document I've added DocType, DocID and LineID columns. 2. DocType can be: - PUR for the purchase - SAL for the sale - TRN for the transfer from one warehouse to other. 3. Transactions (purchasing, sales, transfers) go to the Stock table. - PURchase: ArticleID, number of Items, purchasing Price, etc. - SALes: ArticleID, number of Items, Price (cost of sale - will be calculated), etc. - TRN (transfer) got two entries in the Stock table. First entry is OUT direction from the shipping-from warehouse (tranCode=TOU) and second entry is the IN direction to the receiving warehouse (tranCode=TIN). These transactions lack of price, so OUT direction should calculate "FIFO cost of sale" price (the same way as calculated in case of regular sale). That OUT Price should be purchasing price for the IN part of that transfer transaction. And this is the problem. **The problem.** Calculate "cost of sale" price for the OUT direction transactions (these with tranCode in (OUT, TOU)) in case when the corresponding IN transaction(s) (tranCode=IN,TIN) has no price (these come from the transfers from warehouse to warehouse). Though I have calculated "cost of sale" price for the TOU (transfer out) tranCode I can't find way how to "send" that result to the IN part of the same transaction so it could be reused in the following transactions. I have created an view with almost the same definition as main code. Then in the CTE StockInWithRollingBalance when selecting Price I've add handling for the "TIN" case. There I select from the view "TOU" part of the same transaction (based on the docType, docID and lineID). This works for the depth of one transaction, though it could be very bad idea from the point of performance. So, it's possible to sell from WarehouseID 11 and I'll get FIFO cost of sold goods. It's one step away from "real" purchase (with the price). But, when try to do sell from the warehouseID 12 or 13 I can't get FIFO price. In test scenario it's 2 or 3 "steps" away from the original purchase. I hope the solution could be recursion, but I can't figure out how to do it or is it possible at all? If I enter prices to transfer documents (to IN part of the transaction) then there is no problems (then price "comes" from Stock table not from temporary CTE views). This is result I get: ![alt text][3] **Here is the Stock table definition and test data:** use [tempdb] go IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE id=OBJECT_ID('tempdb.dbo.Stock')) DROP TABLE dbo.Stock --if there are objects that depends on Stock table it thorw error go CREATE TABLE dbo.Stock ( StockID INT NOT NULL, ArticleID SMALLINT NOT NULL, WarehouseID INT NOT NULL, TranDate DATETIME NOT NULL, TranCode VARCHAR(3) NOT NULL, DocType VARCHAR(3) NOT NULL, DocID INT NOT NULL, LineID INT NOT NULL, Items INT NOT NULL, Price MONEY NULL, CONSTRAINT [PK_Stock] PRIMARY KEY CLUSTERED ( StockID ASC ) ) go DELETE FROM STOCK INSERT dbo.[Stock] ( [StockID],[ArticleID],[WarehouseID],[TranDate],[TranCode],[docType],[docID],[lineID],[Items],[Price] ) --1: Purchase 10pcs * 241 SELECT 111300,10000,10,'21:00:54','IN','PUR',7,7,10,241.00 UNION ALL --2,3: Transfer 5pcs from WarehouseID=10 to 11 SELECT 111310,10000,10,'21:01:54','TOU','TRN',4,4,5,null UNION ALL SELECT 111320,10000,11,'21:01:55','TIN','TRN',4,4,5,null UNION ALL --4,5: Transfer 3pcs from 11 to 12 SELECT 111330,10000,11,'21:02:54','TOU','TRN',5,5,3,null UNION ALL SELECT 111340,10000,12,'21:02:55','TIN','TRN',5,5,3,null UNION ALL --6,7: Transfer 2pcs from 12 to 13 SELECT 111350,10000,12,'21:03:54','TOU','TRN',6,6,2,null UNION ALL SELECT 111360,10000,13,'21:03:55','TIN','TRN',6,6,2,null UNION ALL --8: Sell 1pcs from warehouseID=13 SELECT 111370,10000,13,'21:04:13','OUT','SAL',12,12,1,NULL go CREATE NONCLUSTERED INDEX IX_Input ON dbo.Stock (ArticleID, WarehouseID, [TranDate]) INCLUDE ([TranCode], Items, Price) -- Remove comment for SQL Server 2005 and later --WHERE TranCode IN ('IN', 'RET') -- Remove comment for SQL Server 2008 GO CREATE NONCLUSTERED INDEX IX_Output ON dbo.Stock (ArticleID, WarehouseID, [TranDate]) INCLUDE ([TranCode], Items) -- Remove comment for SQL Server 2005 and later --WHERE TranCode = 'OUT' -- Remove comment for SQL Server 2008 GO CREATE NONCLUSTERED INDEX IX_Dave_Price ON dbo.Stock (ArticleID, WarehouseID, [TranDate]) INCLUDE ([TranCode], Items, Price) -- Remove comment for SQL Server 2005 and later --WHERE TranCode = 'IN'-- Remove comment for SQL Server 2008 --WITH (DROP_EXISTING = ON)-- Remove comment for SQL Server 2008 GO **Here is the vwFIFO view definition:** USE [tempdb] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE VIEW [dbo].[vwFIFO] AS WITH StockInWithRollingBalance AS ( SELECT [S].[ArticleID] , [S].[WarehouseID] , [S].[TranDate] , [S].[Items] , CASE WHEN [TranCode] = 'RET' -- THEN ( /*==== GET PRICE OF THE IMMEDIATE PREVIOUS IN ======*/ SELECT TOP 1 Price RetPrice FROM [dbo].[Stock] AS S2 WHERE [S].[ArticleID] = [S2].[ArticleID] AND [S].[WarehouseID] = [S2].[WarehouseID] AND [S2].[TranCode] = 'IN' AND [S2].[TranDate] < S.[TranDate] ORDER BY [TranDate] DESC ) WHEN [TranCode] = 'TIN' -- THEN S.Price ELSE [S].[Price] END AS Price , [TotalIn] ,DocType ,DocID ,LineID ,TranCode FROM [dbo].[Stock] AS S CROSS APPLY ( /*===== GET THE ROLLING BALANCE =============*/ SELECT SUM([Items]) TotalIn FROM [dbo].[Stock] S1 WHERE [S].[ArticleID] = [S1].[ArticleID] AND [S].[WarehouseID] = [S1].[WarehouseID] AND [S1].[TranDate] <= s.[TranDate] AND [S1].[TranCode] IN ( 'IN','RET','TIN' ) ) A WHERE S.[TranCode] IN ( 'IN','RET','TIN' ) ) , StockOutWithBreakdown AS ( SELECT [C].[ArticleID] , [C].[WarehouseID] , [C].[TranDate] , [C].[Items] , D.Items [PurchasedQuantity] , D.Price StockInPrice , D.TotalIn + ISNULL([TotalOut], 0) [RunningTotalQuantity] , [C].[DocType] , [C].[DocID] , [C].[LineID] , [C].[TranCode] FROM [dbo].[Stock] C --WITH ( INDEX ( IX_Output ) ) CROSS APPLY ( /*====== GET THE TOTAL OF STOCK OUT JUST BEFORE THIS STOCK OUT======*/ SELECT SUM(0 - [Items]) TotalOut FROM [dbo].[Stock] AS S1 -- WITH ( INDEX ( IX_Output ) ) WHERE [S1].[TranCode] IN ('OUT','TOU') AND [S1].[ArticleID] = [C].[ArticleID] AND [S1].[WarehouseID] = [C].[WarehouseID] AND [S1].[TranDate] < C.[TranDate] ) B CROSS APPLY ( /*======= GET ALL THE STOCK IN BEFORE THIS PARTICULAR STOCK OUT ====*/ SELECT Items , CASE WHEN TranCode = 'TIN' THEN ( SELECT NULL ) ELSE Price END AS Price , TotalIn FROM StockInWithRollingBalance AS S WHERE [S].[ArticleID] = [C].[ArticleID] AND [S].[WarehouseID] = [C].[WarehouseID] --AND S.[TranDate] < C.TranDate -- take only older AND ((B.[TotalOut] <= S.TotalIn) OR (S.[TranDate] < C.TranDate)) -- can take from the "future" ) D WHERE C.[TranCode] IN ('OUT','TOU') ) SELECT top 100 percent [TranDate] , [ArticleID] , [WarehouseID] , docType , docID , lineID , tranCode , SUM(StockInPrice * CASE WHEN [RunningTotalQuantity] <= [Items] THEN CASE WHEN [RunningTotalQuantity] <= [PurchasedQuantity] THEN [RunningTotalQuantity] ELSE [PurchasedQuantity] END ELSE CASE WHEN [RunningTotalQuantity] > [Items] THEN CASE WHEN [RunningTotalQuantity] - [PurchasedQuantity] >= items THEN CONVERT(INT, 0) WHEN [RunningTotalQuantity] <= [PurchasedQuantity] THEN [Items] ELSE [Items] - ( [RunningTotalQuantity] - [PurchasedQuantity] ) END END END) CostofSale , SUM(CASE WHEN [RunningTotalQuantity] <= [Items] THEN CASE WHEN [RunningTotalQuantity] <= [PurchasedQuantity] THEN [RunningTotalQuantity] ELSE [PurchasedQuantity] END ELSE CASE WHEN [RunningTotalQuantity] > [Items] THEN CASE WHEN [RunningTotalQuantity] - [PurchasedQuantity] >= items THEN CONVERT(INT, 0) WHEN [RunningTotalQuantity] <= [PurchasedQuantity] --prodaja je cijela pokrivena lagerom THEN [Items] ELSE [Items] - ( [RunningTotalQuantity] - [PurchasedQuantity] ) END END END) ItemsSold , [items] ItemsRequested --, SUM([RunningTotalQuantity]) PreviousItems FROM StockOutWithBreakdown WHERE [RunningTotalQuantity] > 0 AND [RunningTotalQuantity] - [PurchasedQuantity] <= items GROUP BY [TranDate] , [ArticleID] , [WarehouseID] , [Items] , docType , docID , lineID , tranCode --, [RunningTotalQuantity] ORDER BY [ArticleID] , [TranDate] **Here is the main code:** use [tempdb] go --select * from stock ORDER BY TRANDATE SET NOCOUNT ON; declare @startTime datetime declare @endTime datetime set @startTime = getdate() ;WITH StockInWithRollingBalance AS ( SELECT [S].[ArticleID] , [S].[WarehouseID] , [S].[TranDate] , [S].[Items] , CASE WHEN [TranCode] = 'RET' -- THEN ( /*==== GET PRICE OF THE IMMEDIATE PREVIOUS IN ======*/ SELECT TOP 1 Price RetPrice FROM [dbo].[Stock] AS S2 WHERE [S].[ArticleID] = [S2].[ArticleID] AND [S].[WarehouseID] = [S2].[WarehouseID] AND [S2].[TranCode] = 'IN' AND [S2].[TranDate] < S.[TranDate] ORDER BY [TranDate] DESC ) WHEN [TranCode] = 'TIN' THEN (--todo: select top 1 costOfSale/ItemsSold from vwfifo S2 where S2.docType=S.docType and S2.docID=S.docID and S2.lineID = S.lineID and S2.tranCode='TOU' ) ELSE [S].[Price] END AS Price , [TotalIn] ,DocType ,DocID ,LineID ,TranCode FROM [dbo].[Stock] AS S CROSS APPLY ( /*===== GET THE ROLLING BALANCE =============*/ SELECT SUM([Items]) TotalIn FROM [dbo].[Stock] S1 WHERE [S].[ArticleID] = [S1].[ArticleID] AND [S].[WarehouseID] = [S1].[WarehouseID] AND [S1].[TranDate] <= s.[TranDate] AND [S1].[TranCode] IN ( 'IN','RET','TIN' ) ) A WHERE S.[TranCode] IN ( 'IN','RET','TIN' ) ) , StockOutWithBreakdown ([ArticleID],[WarehouseID],[TranDate],[Items],[PurchasedQuantity],StockInPrice,[RunningTotalQuantity], [DocType],[DocID],[LineID],[TranCode]) AS ( SELECT [C].[ArticleID] , [C].[WarehouseID] , [C].[TranDate] , [C].[Items] , D.Items [PurchasedQuantity] , D.Price StockInPrice , D.TotalIn + ISNULL([TotalOut], 0) [RunningTotalQuantity] , [C].[DocType] , [C].[DocID] , [C].[LineID] , [C].[TranCode] -- , [D].TotalIn FROM [dbo].[Stock] C WITH ( INDEX ( IX_Output ) ) CROSS APPLY ( /*====== GET THE TOTAL OF STOCK OUT JUST BEFORE THIS STOCK OUT======*/ SELECT SUM(0 - [Items]) TotalOut FROM [dbo].[Stock] AS S1 WITH ( INDEX ( IX_Output ) ) WHERE [S1].[TranCode] IN ('OUT','TOU') AND [S1].[ArticleID] = [C].[ArticleID] AND [S1].[WarehouseID] = [C].[WarehouseID] AND [S1].[TranDate] < C.[TranDate] ) B CROSS APPLY ( /*======= GET ALL THE STOCK IN BEFORE THIS PARTICULAR STOCK OUT ====*/ SELECT Items , Price , TotalIn FROM StockInWithRollingBalance AS S WHERE [S].[ArticleID] = [C].[ArticleID] AND [S].[WarehouseID] = [C].[WarehouseID] --AND S.[TranDate] < C.TranDate --take older stock AND ((S.[TranDate] < C.TranDate) or (B.[TotalOut] <= S.TotalIn)) -- take from future too ) D WHERE C.[TranCode] IN ('OUT','TOU')--('OUT','TOU','IN','TIN') ) SELECT [TranDate] , [ArticleID] , [WarehouseID] , docType , docID , lineID , tranCode , SUM(StockInPrice * CASE WHEN [RunningTotalQuantity] <= [Items] THEN CASE WHEN [RunningTotalQuantity] <= [PurchasedQuantity] THEN [RunningTotalQuantity] ELSE [PurchasedQuantity] END ELSE CASE WHEN [RunningTotalQuantity] > [Items] THEN CASE WHEN [RunningTotalQuantity] - [PurchasedQuantity] >= items THEN CONVERT(INT, 0) WHEN [RunningTotalQuantity] <= [PurchasedQuantity] THEN [Items] ELSE [Items] - ( [RunningTotalQuantity] - [PurchasedQuantity] ) END END END) CostofSale , SUM(CASE WHEN [RunningTotalQuantity] <= [Items] THEN CASE WHEN [RunningTotalQuantity] <= [PurchasedQuantity] THEN [RunningTotalQuantity] ELSE [PurchasedQuantity] END ELSE CASE WHEN [RunningTotalQuantity] > [Items] THEN CASE WHEN [RunningTotalQuantity] - [PurchasedQuantity] >= items THEN CONVERT(INT, 0) WHEN [RunningTotalQuantity] <= [PurchasedQuantity] -- THEN [Items] ELSE [Items] - ( [RunningTotalQuantity] - [PurchasedQuantity] ) END END END) ItemsSold , [items] ItemsRequested --, SUM([RunningTotalQuantity]) PreviousItems FROM StockOutWithBreakdown WHERE [RunningTotalQuantity] > 0 AND [RunningTotalQuantity] - [PurchasedQuantity] <= items GROUP BY [TranDate] , [ArticleID] , [WarehouseID] , [Items] , docType , docID , lineID , tranCode --,totalin --, [RunningTotalQuantity] -- ORDER BY [ArticleID] , [TranDate] set @endTime = getdate() select datediff(ms,@starttime,@endtime) as timeElapsed Looking forward to your responses. Thanks. [1]: http://ask.sqlservercentral.com/questions/95996/fifo-cost-of-sale.html [2]: /storage/temp/1671-fifo+cost+of+sale+-+article+movment+scheme.png [3]: /storage/temp/1672-fifo+result.png
tsqlset-based
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Khaled_Afifi avatar image
Khaled_Afifi answered
The Cost doesn't change while moving between warehouses. so no need to include transfer transactions when you calculate the cost. keep the cost attached to the item only. so my solution is: 1.Create The FIFO table to include TransactionId, ItemId, TranDate, Qty, Cost, CurrentStock, CurrentValue. don't include Warehouse, now you have stock and value for Each item in the company. 2. Create another table to hold stock for each item cross warehouse. 3. Create view to join both tables to get the value for each item in warehouse.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.