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_V10
(
    @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)
 
IF (SELECT top 1 S.bCostPerWarehouse FROM StDfTbl S) = 1
	BEGIN
			SELECT @UnitCostV2 = COALESCE((SELECT TOP 1 UnitCost = CASE WHEN S.iItemCostingMethod = 0 THEN st.AverageCost
                                                        WHEN S.iItemCostingMethod = 1 THEN st.LatestCost
                                                        WHEN S.iItemCostingMethod = 2 THEN st.LowestCost
                                                        WHEN S.iItemCostingMethod = 3 THEN st.HighestCost
                                                        WHEN S.iItemCostingMethod = 4 THEN st.ManualCost
                                                        ELSE st.AverageCost END
                        FROM StkItem S
                        LEFT JOIN _etblStockCosts st on StockLink = st.StockID
                        WHERE S.StockLink = @StockId AND st.WhseID = @WhId),0)
	END
	ELSE --not cost by wh:
	BEGIN
		SELECT @UnitCostV2 = COALESCE((SELECT TOP 1 UnitCost = CASE WHEN S.iItemCostingMethod = 0 THEN st.AverageCost
                                                        WHEN S.iItemCostingMethod = 1 THEN st.LatestCost
                                                        WHEN S.iItemCostingMethod = 2 THEN st.LowestCost
                                                        WHEN S.iItemCostingMethod = 3 THEN st.HighestCost
                                                        WHEN S.iItemCostingMethod = 4 THEN st.ManualCost
                                                        ELSE st.AverageCost END
                        FROM StkItem S
                        LEFT JOIN _etblStockCosts st on StockLink = st.StockID
                        WHERE S.StockLink = @StockId AND st.WhseID IN (0,-1)),0)
	END--IF (SELECT top 1 S.bCostPerWarehouse FROM StDfTbl S) = 1/ELSE

    -- Return the result of the function
    RETURN @UnitCostV2
 
END

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