x

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.

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

  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

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.

more ▼

asked Aug 27, 2014 at 09:35 AM in Default

avatar image

GoranJ
11 1 1 2

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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.

more ▼

answered Dec 29, 2015 at 06:51 AM

avatar image

Khaled_Afifi
0

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x407
x14

asked: Aug 27, 2014 at 09:35 AM

Seen: 1490 times

Last Updated: Dec 29, 2015 at 06:51 AM

Copyright 2016 Redgate Software. Privacy Policy