iVend Database Database : GetPriceListProductsForGrossMargin Stored Procedure

Creation Date

9/18/2015 10:23 AM

Encrypted

Ansi Nulls

Parameter

Direction

Description

Data Type

Size

@PriceListKey

In

VarWChar

50

@RETURN_VALUE

Return Value

Integer

4

CREATE PROCEDURE [dbo].[GetPriceListProductsForGrossMargin]

(

@PriceListKey nvarchar(50)

)

AS

BEGIN

SET NOCOUNT ON

CREATE TABLE #TempPriceListProductGrossMargin

(

 PriceListKey nvarchar(50),

 PriceMatrixKey nvarchar(50),

 UOMPriceMatrixKey nvarchar(50),

 ProductKey nvarchar(50),

 ProductId NVARCHAR(20),

 Description NVARCHAR(200),

 ProductGroupId NVARCHAR(50),

 ParentProductId NVARCHAR(50),

 CurrentCost DECIMAL(20,5),

 CurrentPrice DECIMAL(20,5),

 MarginPercentage DECIMAL(20,5),

 SuggestedPrice DECIMAL(20,5),

 UOMKey nvarchar(50),

 UOMId NVARCHAR(50),

 PrimaryAttribute NVARCHAR(50),

 SecondaryAttribute NVARCHAR(50)

)

INSERT INTO #TempPriceListProductGrossMargin(PriceListKey,PriceMatrixKey,UOMPriceMatrixKey,ProductKey,ProductId,

  Description,ProductGroupId,ParentProductId,CurrentCost,CurrentPrice,MarginPercentage,SuggestedPrice,

  UOMKey,UOMId,PrimaryAttribute,SecondaryAttribute)

SELECT DISTINCT PrcPriceList.PriceListKey,PrcPriceMatrix.PriceMatrixKey, '0' AS UOMPriceMatrixKey,PrcPriceMatrix.ProductKey,

    InvProduct.Id, InvProduct.Description,InvProductGroup.Id,ISNULL(ParentProduct.Id,''),0,PrcPriceMatrix.Price,

    0,PrcPriceMatrix.Price,ISNULL(InvUOM.UOMKey,0),ISNULL(InvUOM.Id,''),'',''

FROM PrcPriceList

INNER JOIN PrcPriceMatrix ON PrcPriceMatrix.PriceListKey = PrcPriceList.PriceListKey AND PrcPriceMatrix.IsDeleted = 0

INNER JOIN InvProduct ON InvProduct.ProductKey = PrcPriceMatrix.ProductKey AND InvProduct.IsDeleted = 0

INNER JOIN InvProductGroup ON InvProductGroup.ProductGroupKey = InvProduct.ProductGroupKey

LEFT OUTER JOIN InvUOMGroupDetail ON InvProduct.UOMGroupKey = InvUOMGroupDetail.UOMGroupKey AND InvUOMGroupDetail.IsDeleted = 0

  LEFT OUTER JOIN InvUOM ON InvUOMGroupDetail.BaseUOMKey = InvUOM.UOMKey

LEFT OUTER JOIN InvProduct AS ParentProduct ON ParentProduct.ProductKey = InvProduct.ParentMartixProductKey

WHERE PrcPriceList.PriceListKey = @PriceListKey AND InvProduct.IsGiftCertificate = 0 AND InvProduct.IsMatrixItem = 0

UPDATE A

SET A.CurrentPrice = B.Price

FROM #TempPriceListProductGrossMargin A

INNER JOIN PrcUOMPriceMatrix B ON A.ProductKey = B.ProductKey AND A.UOMKey = B.UOMKey AND B.PriceListKey = A.PriceListKey AND B.IsDeleted = 0

UPDATE A

SET A.PrimaryAttribute = ISNULL(InvAttributeDetail.Id,'')

FROM #TempPriceListProductGrossMargin A

INNER JOIN InvProductAttribute ON A.ProductKey = InvProductAttribute.ProductKey AND InvProductAttribute.IsPrimaryFilter = 1

INNER JOIN InvAttributeDetail ON InvAttributeDetail.AttributeDetailKey = InvProductAttribute.AttributeDetailKey

UPDATE A

SET A.SecondaryAttribute = ISNULL(InvAttributeDetail.Id,'')

FROM #TempPriceListProductGrossMargin A

INNER JOIN InvProductAttribute ON A.ProductKey = InvProductAttribute.ProductKey AND InvProductAttribute.IsSecondaryFilter = 1

INNER JOIN InvAttributeDetail ON InvAttributeDetail.AttributeDetailKey = InvProductAttribute.AttributeDetailKey

UPDATE A

SET A.CurrentCost = CASE B.CostingMethod WHEN 1 THEN ISNULL((

    SELECT TOP 1 AverageCost

    FROM InvInventoryItem WITH (NOLOCK)

    WHERE ProductKey = B.ProductKey

    --AND WarehouseKey = @WarehouseKey

And ISNULL(AverageCost,0) != 0 ORDER BY ISNULL(AverageCost,0) DESC

),0)

WHEN 2 THEN ISNULL((

    SELECT TOP 1 Cost

    FROM InvProductCost WITH (NOLOCK)

    WHERE ProductKey = B.ProductKey

    --AND WarehouseKey IN (@WarehouseKey, 0)

    AND IsDeleted = 0

    AND CONVERT(VARCHAR,EffectiveDate,112) <= dbo.GetCompanyDateTime()

    ORDER BY EffectiveDate DESC, Cost DESC

),0)

ELSE 0 END

FROM #TempPriceListProductGrossMargin A

INNER JOIN InvProduct B ON A.ProductKey = B.ProductKey

--LEFT OUTER JOIN InvWarehouse C ON C.PriceListKey = A.PriceListKey

SELECT * FROM #TempPriceListProductGrossMargin ORDER BY ProductId

END