SQL - Evo - GetUnitCost

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Asamco B.V. - Alex
-- Create date: 22/08/2018
-- Description:	Get Unit Cost of Stock item, incl per WH costing and different costing methods.
-- =============================================
CREATE FUNCTION _as_GetUnitCost
(
	@StockLink as int
	,@WarehouseID as int = 0
)
RETURNS float
AS
BEGIN
	-- Declare the return variable here
	DECLARE @UnitCostV2 float
	DECLARE @CostingMethod int


	SELECT @CostingMethod = COALESCE((SELECT iItemCostingMethod FROM StkItem WHERE StockLink = @StockLink),0)

	-- Add the T-SQL statements to compute the return value here
IF ((SELECT top 1 S.bCostPerWarehouse FROM StDfTbl S) = 1 AND (SELECT WhseItem FROM StkItem S WHERE S.StockLink = @StockLink) = 1)
            BEGIN
            SELECT @UnitCostV2 = COALESCE((SELECT UnitCost = CASE WHEN @CostingMethod = 0 THEN W.fAverageCost
                                                                  WHEN @CostingMethod = 1 THEN W.fLatestCost
                                                                  WHEN @CostingMethod = 2 THEN W.fLowestCost
                                                                  WHEN @CostingMethod = 3 THEN W.fHighestCost
                                                                  WHEN @CostingMethod = 4 THEN W.fManualCost
                                                                  ELSE W.fAverageCost END
                                    FROM WhseStk W WHERE W.WHStockLink = @StockLink AND WHWhseID = @WarehouseID),0)
            END
        ELSE
            BEGIN --cost per stkItem (not by wh)
                SELECT @UnitCostV2 = COALESCE((SELECT UnitCost = CASE WHEN @CostingMethod = 0 THEN S.AveUCst
                                                                      WHEN @CostingMethod = 1 THEN S.LatUCst
                                                                      WHEN @CostingMethod = 2 THEN S.LowUCst
                                                                      WHEN @CostingMethod = 3 THEN S.HigUCst
                                                                      WHEN @CostingMethod = 4 THEN S.StdUCst
                                                                      ELSE S.AveUCst END
                                        FROM StkItem S WHERE S.StockLink = @StockLink),0)
            END
	-- Return the result of the function
	RETURN @UnitCostV2

END
GO