How to implement Price list feature in IS for kit Items?

Aspdotnetstorefron uses different Store Procedures for getting Items detail. We have modified the Store procedure according to our need.
We have taken in consideration that in kit some item may not have the Price List settings. If item don’t have Price list then we take default price (inventoryitempricing detail) as the total price.

In all Stored Procedure query is added for getting the Pricing level and method of customer.

SELECT @customerPricingLevel=PricingLevel,
@customerPricingMethode=PricingMethod
from customer where CustomerCode = @CustomerCode;

If customer pricing method is not set then default query will get called in all stored procedures.

Following are the store procedure used for getting details

1) aspdnsf_GetKitItems – This store procedure is used on showproduct.aspx page. In this store Procedure we
have create a temp table.

select II.* , ipl.pricelevelcode into #InventoryKitDetailDescription_temp from InventoryKitDetailDescription ii,InventoryPriceLevel ipl

This query will create a temp table which has one to many relationships between InventoryKitDetailDescription and InventoryPricingLevel.


We have replaced InventoryKitDetailDescription table with the new temp table. We add a left outer join on InventoryPricingLevel with the temp table


set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
-- =============================================
-- Author:
-- Create date: <25 April 2009>
-- Description:
-- =============================================
ALTER PROCEDURE [dbo].[aspdnsf_GetKitItems]
-- Add the parameters for the stored procedure here
(
@ItemKitcode NVARCHAR(50),
@currencyCode NVARCHAR(50),
@languageCode NVARCHAR(50),
@CustomerCode NVARCHAR(30),
@CartID UNIQUEIDENTIFIER,
@WebsiteCode NVARCHAR(30),
@IsAnonymous BIT,
@AnonymousCustomerCode NVARCHAR(30)
)
AS
BEGIN
select II.* , ipl.pricelevelcode into #InventoryKitDetailDescription_temp from InventoryKitDetailDescription ii,InventoryPriceLevel ipl
declare @customerPriceWithPricingLevel nvarchar(100);
-- this Section will get the price methode and pricing type of customer
declare @customerPricingLevel nvarchar(100);
declare @customerPricingMethode nvarchar(100);
-- declare @kitPricingType nvarchar(100);

SELECT @customerPricingLevel=PricingLevel,
@customerPricingMethode=PricingMethod
from customer where CustomerCode = @CustomerCode;
--
if (@customerPricingMethode is null OR @customerPricingMethode='None')
Begin
SELECT i.Counter,
i.ItemCode,
i.ItemName,
ikd.ItemCode,
ikd.ItemCode as itemdetailcode,
it.ItemName,
it.ItemType,
ikd.UnitMeasureCode,
ikdd.ItemDescription,
ikd.IsDefault,
ikpd.TotalRate,
ikd.GroupCode,
ikogd.Description AS GroupDescription,
ikogd.HTMLDescription AS GroupHTMLDescription,
ikog.GroupType,
ikog.SelectionControl,
i.Counter + ikd.Counter AS ItemId, -- to avoid id collisions
ikog.Counter AS GroupId,
CAST(
(
CASE
WHEN wkc.ItemCode IS NOT NULL THEN 1
WHEN ( wkc.ItemCode IS NULL AND ikd.IsDefault = 1 ) THEN 1
ELSE 0
END
) AS BIT) AS [Select],
av.FreeStock
FROM InventoryKitDetail ikd WITH (NOLOCK)
INNER JOIN InventoryKitDetailDescription ikdd WITH (NOLOCK) ON ( ikdd.ItemKitCode = ikd.ItemKitCode AND ikdd.ItemCode = ikd.ItemCode AND ikdd.GroupCode = ikd.GroupCode )
INNER JOIN InventoryKitOptionGroup ikog WITH (NOLOCK) ON ( ikog.ItemKitCode = ikd.ItemKitCode AND ikog.GroupCode = ikd.GroupCode )
INNER JOIN InventoryKitOptionGroupDescription ikogd WITH (NOLOCK) ON ( ikog.ItemKitCode = ikogd.ItemKitCode AND ikog.GroupCode = ikogd.GroupCode AND ikogd.LanguageCode = @LanguageCode)
INNER JOIN InventoryItem i WITH (NOLOCK) ON i.ItemCode = ikd.ItemKitCode
INNER JOIN InventoryItem it WITH (NOLOCK) ON (it.ItemCode = ikd.ItemCode)
left outer JOIN inventoryitem_properties_C iipc WITH (NOLOCK) ON (iipc.ItemCode_C = ikd.ItemCode)
INNER JOIN InventoryKitPricingDetail ikpd WITH (NOLOCK) ON
( ikd.ItemCode = ikpd.ItemCode AND
ikpd.ItemKitCode = ikd.ItemKitCode AND
ikpd.GroupCode = ikd.GroupCode
)
INNER JOIN InventoryItemDescription iid WITH (NOLOCK) ON iid.ItemCode = ikd.ItemCode
LEFT OUTER JOIN WebKitCart wkc WITH (NOLOCK) ON ( wkc.ItemKitCode = ikd.ItemKitCode AND wkc.ItemCode = ikd.ItemCode AND wkc.GroupCode = ikd.GroupCode AND wkc.CustomerCode = @CustomerCode AND wkc.CartID = @CartID )
INNER JOIN Customer c WITH (NOLOCK) ON (@IsAnonymous = 1 AND c.CustomerCode = @AnonymousCustomerCode) OR (c.CustomerCode = @CustomerCode)
INNER JOIN CustomerShipTo cs WITH (NOLOCK) ON cs.CustomerCode = c.CustomerCode AND cs.ShipToCode = c.DefaultShipToCode
LEFT OUTER JOIN AvailabilityView av ON av.ItemCode = ikd.ItemCode AND av.WarehouseCode = cs.WarehouseCode AND av.UM = ikd.UnitMeasureCode
WHERE i.ItemCode = @ItemKitCode AND
ikd.ItemKitCode = @ItemKitcode AND
ikpd.CurrencyCode = @CurrencyCode AND
ikdd.LanguageCode = @LanguageCode AND
iid.LanguageCode = @LanguageCode
ORDER BY ikog.SortOrder,iipc.length_C,iipc.width_C, ikd.ItemCode ASC
END
ELSE
BEGIN
SELECT i.Counter,
i.ItemCode,
i.ItemName,
ikd.ItemCode,
ikd.ItemCode as itemdetailcode,
it.ItemName,
it.ItemType,
ikd.UnitMeasureCode,
ikdd.ItemDescription,
ikd.IsDefault,
CASE
WHEN ipl.SalesPrice=0 THEN ikpd.TotalRate
WHEN ipl.SalesPrice is null THEN ikpd.TotalRate ELSE ipl.SalesPrice
END 'TotalRate'
,
ikd.GroupCode,
ikogd.Description AS GroupDescription,
ikogd.HTMLDescription AS GroupHTMLDescription,
ikog.GroupType,
ikog.SelectionControl,
i.Counter + ikd.Counter AS ItemId, -- to avoid id collisions
ikog.Counter AS GroupId,
CAST(
(
CASE
WHEN wkc.ItemCode IS NOT NULL THEN 1
WHEN ( wkc.ItemCode IS NULL AND ikd.IsDefault = 1 ) THEN 1
ELSE 0
END
) AS BIT) AS [Select],
av.FreeStock
FROM InventoryKitDetail ikd WITH (NOLOCK)
INNER JOIN #InventoryKitDetailDescription_temp ikdd WITH (NOLOCK) ON ( ikdd.ItemKitCode = ikd.ItemKitCode AND ikdd.ItemCode = ikd.ItemCode AND ikdd.GroupCode = ikd.GroupCode )
INNER JOIN InventoryKitOptionGroup ikog WITH (NOLOCK) ON ( ikog.ItemKitCode = ikd.ItemKitCode AND ikog.GroupCode = ikd.GroupCode )
INNER JOIN InventoryKitOptionGroupDescription ikogd WITH (NOLOCK) ON ( ikog.ItemKitCode = ikogd.ItemKitCode AND ikog.GroupCode = ikogd.GroupCode AND ikogd.LanguageCode = @LanguageCode)
INNER JOIN InventoryItem i WITH (NOLOCK) ON i.ItemCode = ikd.ItemKitCode
INNER JOIN InventoryItem it WITH (NOLOCK) ON (it.ItemCode = ikd.ItemCode)
left outer JOIN inventoryitem_properties_C iipc WITH (NOLOCK) ON (iipc.ItemCode_C = ikd.ItemCode)
INNER JOIN InventoryKitPricingDetail ikpd WITH (NOLOCK) ON ( ikd.ItemCode = ikpd.ItemCode AND ikpd.ItemKitCode = ikd.ItemKitCode AND ikpd.GroupCode = ikd.GroupCode)
INNER JOIN InventoryItemDescription iid WITH (NOLOCK) ON iid.ItemCode = ikd.ItemCode
LEFT OUTER JOIN WebKitCart wkc WITH (NOLOCK) ON ( wkc.ItemKitCode = ikd.ItemKitCode AND wkc.ItemCode = ikd.ItemCode AND wkc.GroupCode = ikd.GroupCode AND wkc.CustomerCode = @CustomerCode AND wkc.CartID = @CartID )
INNER JOIN Customer c WITH (NOLOCK) ON (@IsAnonymous = 1 AND c.CustomerCode = @AnonymousCustomerCode) OR (c.CustomerCode = @CustomerCode)
INNER JOIN CustomerShipTo cs WITH (NOLOCK) ON cs.CustomerCode = c.CustomerCode AND cs.ShipToCode = c.DefaultShipToCode
LEFT OUTER JOIN AvailabilityView av ON av.ItemCode = ikd.ItemCode AND av.WarehouseCode = cs.WarehouseCode AND av.UM = ikd.UnitMeasureCode
LEFT OUTER JOIN InventoryPricingLevel ipl on ikdd.ItemCode=ipl.ItemCode
WHERE i.ItemCode = @ItemKitCode AND
ikd.ItemKitCode = @ItemKitcode AND
ikpd.CurrencyCode = @CurrencyCode AND
ikdd.LanguageCode = @LanguageCode AND
iid.LanguageCode = @LanguageCode and
(
(ikdd.PriceLevelcode = (Select pricingLevel from Customer where CustomerCode=@CustomerCode))
AND
(ipl.pricinglevel= (Select pricingLevel from Customer where CustomerCode=@CustomerCode)
or ipl.pricinglevel is null)
)
ORDER BY ikog.SortOrder,iipc.length_C,iipc.width_C, ikd.ItemCode ASC
END
DROP TABLE #InventoryKitDetailDescription_temp
END

The Codes in Maroon are added for price list implementation.

2) aspdnsf_GetKitCartComposition – This store procedure is used on Shopingcart.aspx page.

In this store procedure we have created temp table #InventoryKitDetail_temp

select II.* , ipl.pricelevelcode into #InventoryKitDetail_temp from InventoryKitDetail ii,InventoryPriceLevel ipl

The InventoryKitDetail is repalaced with the new temp table in query.


set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go



ALTER PROCEDURE [dbo].[aspdnsf_GetKitCartComposition](
@CartID UNIQUEIDENTIFIER,
@CartType INT,
@CustomerCode NVARCHAR(30),
@CurrencyCode NVARCHAR(30)
)
AS
BEGIN
-- this Section will get the price methode and pricing type of customer
declare @customerPricingLevel nvarchar(100);
declare @customerPricingMethode nvarchar(100);

SELECT @customerPricingLevel=PricingLevel,
@customerPricingMethode=PricingMethod
from customer where CustomerCode = @CustomerCode;

select II.* , ipl.pricelevelcode into #InventoryKitDetail_temp from InventoryKitDetail ii,InventoryPriceLevel ipl
-- This Section is for customer having price list
if (@customerPricingMethode is not null AND @customerPricingMethode!='None')
BEGIN
SELECT wkc.CartID,
wkc.ItemKItCode,
wkc.ItemCode,
wkc.CustomerCode,
wkc.GroupCode,
wsc.KitPricingType,
ium.UnitMeasureCode,
ium.UnitMeasureQty AS UnitMeasureQuantity,
ikp.Total,
--ikp.TotalRate
CASE
WHEN ipl.SalesPrice=0 THEN ikp.TotalRate
WHEN ipl.SalesPrice is null THEN ikp.TotalRate ELSE ipl.SalesPrice
END 'TotalRate'
FROM WebKitCart wkc WITH (NOLOCK)
INNER JOIN #InventoryKitDetail_temp ikd WITH (NOLOCK) ON wkc.ItemKitCode = ikd.ItemKitCode AND wkc.GroupCode = ikd.GroupCode AND wkc.ItemCode =ikd.ItemCode
INNER JOIN InventoryUnitMeasure ium WITH (NOLOCK) ON ikd.UnitMeasureCode = ium.UnitMeasureCode AND ikd.ItemCode = ium.ItemCode
INNER JOIN WebShoppingCart wsc WITH (NOLOCK) ON wkc.CartId=wsc.ShoppingCartRecGUID AND wsc.CustomerCode = @CustomerCode
INNER JOIN InventoryKitPricingDetail ikp WITH (NOLOCK) ON ikd.ItemKitCode = ikp.ItemKitCode AND ikd.GroupCode = ikp.GroupCode AND ikd.ItemCode =ikp.ItemCode AND ikp.CurrencyCode = @CurrencyCode
LEFT OUTER JOIN InventoryPricingLevel ipl WITH (NOLOCK) ON ikd.ItemCode = ipl.ItemCode
WHERE wkc.CustomerCode = @CustomerCode AND
wsc.CartType= @CartType AND
wsc.ShoppingCartRecGuid = @CartID
AND
(
(ikd.PriceLevelcode = (Select pricingLevel from Customer where CustomerCode=@CustomerCode))
And
(ipl.pricinglevel= (Select pricingLevel from Customer where CustomerCode=@CustomerCode)
or ipl.pricinglevel is null)
)
END
ELSE
BEGIN
SELECT wkc.CartID,
wkc.ItemKItCode,
wkc.ItemCode,
wkc.CustomerCode,
wkc.GroupCode,
wsc.KitPricingType,
ium.UnitMeasureCode,
ium.UnitMeasureQty AS UnitMeasureQuantity,
ikp.Total,
ikp.TotalRate
FROM WebKitCart wkc WITH (NOLOCK)
INNER JOIN InventoryKitDetail ikd WITH (NOLOCK) ON wkc.ItemKitCode = ikd.ItemKitCode AND wkc.GroupCode = ikd.GroupCode AND wkc.ItemCode =ikd.ItemCode
INNER JOIN InventoryUnitMeasure ium WITH (NOLOCK) ON ikd.UnitMeasureCode = ium.UnitMeasureCode AND ikd.ItemCode = ium.ItemCode
INNER JOIN WebShoppingCart wsc WITH (NOLOCK) ON wkc.CartId=wsc.ShoppingCartRecGUID AND wsc.CustomerCode = @CustomerCode
INNER JOIN InventoryKitPricingDetail ikp WITH (NOLOCK) ON ikd.ItemKitCode = ikp.ItemKitCode AND ikd.GroupCode = ikp.GroupCode AND ikd.ItemCode =ikp.ItemCode AND ikp.CurrencyCode = @CurrencyCode
WHERE wkc.CustomerCode = @CustomerCode AND
wsc.CartType= @CartType AND
wsc.ShoppingCartRecGuid = @CartID
END
DROP TABLE #InventoryKitDetail_temp
END

3) aspdnsf_GetWebKitCartDetail – This Stored Procedure is used for showing data on all pages after shopingcart.aspx

It has same working as aspdnsf_GetKitItems store procedure.

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go



ALTER PROCEDURE [dbo].[aspdnsf_GetWebKitCartDetail](
@ItemKitCode NVARCHAR(30),
@CurrencyCode NVARCHAR(30),
@LanguageCode NVARCHAR(30),
@CustomerCode NVARCHAR(30),
@CartID UNIQUEIDENTIFIER
)
AS
BEGIN

-- this Section will get the price methode and pricing type of customer
declare @customerPricingLevel nvarchar(100);
declare @customerPricingMethode nvarchar(100);

SELECT @customerPricingLevel=PricingLevel,
@customerPricingMethode=PricingMethod
from customer where CustomerCode = @CustomerCode;

select II.* , ipl.pricelevelcode into #InventoryKitDetailDescription_temp from InventoryKitDetailDescription ii,InventoryPriceLevel ipl
-- Get Language Code
IF( @LanguageCode is NULL or @LanguageCode = '' )
SELECT @LanguageCode = CompanyLanguage FROM SystemCompanyInformation WITH (NOLOCK)
ELSE
SELECT @LanguageCode = LanguageCode FROM SystemLanguage WITH (NOLOCK) WHERE ShortString = @LanguageCode

if (@customerPricingMethode is not null AND @customerPricingMethode!='None')
BEGIN
SELECT
KD.Counter,
KD.ItemKitCode,
KD.GroupCode,
KP.CurrencyCode,
KD.ItemCode,
i.ItemName,
i.ItemType,
KDESC.ItemDescription,
KDESC.ExtendedDescription,
KDESC.PlainTextDescription,
KDESC.HTMLDescription,
KD.IsDefault,
KD.Percentage,
KD.UnitMeasureCode,
(
SELECT UnitMeasureQty
FROM InventoryUnitMeasure WITH (NOLOCK)
WHERE KD.ItemCode = ItemCode AND UnitMeasureCode = KD.UnitMeasureCode
) UnitMeasureQuantity,
KD.Quantity,
KP.Discount,
--KP.SalesPrice,
CASE
WHEN ipl.SalesPrice=0 THEN KP.TotalRate
WHEN ipl.SalesPrice is null THEN KP.TotalRate ELSE ipl.SalesPrice
END 'SalesPrice',
--KP.SalesPriceRate,
CASE
WHEN ipl.SalesPrice=0 THEN KP.TotalRate
WHEN ipl.SalesPrice is null THEN KP.TotalRate ELSE ipl.SalesPrice
END 'SalesPrice',
KP.RetailPrice,
KP.RetailPriceRate,
KP.StandardCost,
KP.StandardCostRate,
--KP.Total,
CASE
WHEN ipl.SalesPrice=0 THEN KP.TotalRate
WHEN ipl.SalesPrice is null THEN KP.TotalRate ELSE ipl.SalesPrice
END 'Total',
--KP.TotalRate,
CASE
WHEN ipl.SalesPrice=0 THEN KP.TotalRate
WHEN ipl.SalesPrice is null THEN KP.TotalRate ELSE ipl.SalesPrice
END 'TotalRate',
KD.UserCreated,
KD.DateCreated,
KD.UserModified,
KD.DateModified,
ikog.GroupType,
wkc.KitOptionName_C,
wkc.KitOptionValue_C --Added by Mohan
FROM InventoryKitDetail KD WITH (NOLOCK)
INNER JOIN WebKitCart wkc WITH (NOLOCK) ON ( wkc.ItemKitCode = kd.ItemKitCode AND wkc.ItemCode = kd.ItemCode AND wkc.GroupCode = kd.GroupCode AND wkc.CustomerCode = @CustomerCode AND wkc.CartID = @CartID )
INNER JOIN #InventoryKitDetailDescription_temp KDESC WITH (NOLOCK) ON ( KD.ItemKitCode = KDESC.ItemKitCode AND KD.ItemCode = KDESC.ItemCode AND KD.GroupCode = KDESC.GroupCode )
INNER JOIN InventoryKitPricingDetail KP WITH (NOLOCK) ON KD.ItemKitCode = KP.ItemKitCode AND KD.GroupCode = KP.GroupCode AND KD.ItemCode =KP.ItemCode
INNER JOIN InventoryKitOptionGroup ikog WITH (NOLOCK) ON ikog.ItemKitCode = wkc.ItemKitCode AND ikog.GroupCode = ikog.GroupCode AND kd.GroupCode = ikog.GroupCode
INNER JOIN InventoryItem i WITH (NOLOCK) ON i.ItemCode = kd.ItemCode
LEFT OUTER JOIN InventoryPricingLevel ipl on KDESC.ItemCode=ipl.ItemCode
WHERE KD.ItemKitCode = @ItemKitCode
AND KDESC.LanguageCode =@LanguageCode
AND KP.CurrencyCode =@CurrencyCode
AND
(
(KDESC.PriceLevelcode = (Select pricingLevel from Customer where CustomerCode=@CustomerCode))
AND
(ipl.pricinglevel= (Select pricingLevel from Customer where CustomerCode=@CustomerCode)
or ipl.pricinglevel is null)
)
END
ELSE
BEGIN
SELECT
KD.Counter,
KD.ItemKitCode,
KD.GroupCode,
KP.CurrencyCode,
KD.ItemCode,
i.ItemName,
i.ItemType,
KDESC.ItemDescription,
KDESC.ExtendedDescription,
KDESC.PlainTextDescription,
KDESC.HTMLDescription,
KD.IsDefault,
KD.Percentage,
KD.UnitMeasureCode,
(
SELECT UnitMeasureQty
FROM InventoryUnitMeasure WITH (NOLOCK)
WHERE KD.ItemCode = ItemCode AND UnitMeasureCode = KD.UnitMeasureCode
) UnitMeasureQuantity,
KD.Quantity,
KP.Discount,
KP.SalesPrice,
KP.SalesPriceRate,
KP.RetailPrice,
KP.RetailPriceRate,
KP.StandardCost,
KP.StandardCostRate,
KP.Total,
KP.TotalRate,
KD.UserCreated,
KD.DateCreated,
KD.UserModified,
KD.DateModified,
ikog.GroupType,
wkc.KitOptionName_C,
wkc.KitOptionValue_C --Added by Mohan
FROM InventoryKitDetail KD WITH (NOLOCK)
INNER JOIN WebKitCart wkc WITH (NOLOCK) ON ( wkc.ItemKitCode = kd.ItemKitCode AND wkc.ItemCode = kd.ItemCode AND wkc.GroupCode = kd.GroupCode AND wkc.CustomerCode = @CustomerCode AND wkc.CartID = @CartID )
INNER JOIN InventoryKitDetailDescription KDESC WITH (NOLOCK) ON ( KD.ItemKitCode = KDESC.ItemKitCode AND KD.ItemCode = KDESC.ItemCode AND KD.GroupCode = KDESC.GroupCode )
INNER JOIN InventoryKitPricingDetail KP WITH (NOLOCK) ON KD.ItemKitCode = KP.ItemKitCode AND KD.GroupCode = KP.GroupCode AND KD.ItemCode =KP.ItemCode
INNER JOIN InventoryKitOptionGroup ikog WITH (NOLOCK) ON ikog.ItemKitCode = wkc.ItemKitCode AND ikog.GroupCode = ikog.GroupCode AND kd.GroupCode = ikog.GroupCode
INNER JOIN InventoryItem i WITH (NOLOCK) ON i.ItemCode = kd.ItemCode
WHERE KD.ItemKitCode = ISNULL(@ItemKitCode,KD.ItemKitCode) AND LanguageCode =@LanguageCode AND CurrencyCode =ISNULL(@CurrencyCode,CurrencyCode)
END
DROP TABLE #InventoryKitDetailDescription_temp
END

Hope this will help people to implement Price list for kit items who are using Enterprise suit.
If any body have any query or problem please mail me.