Modified version works like a charm :-):-):-)

USE [NetPortal]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Create date: <2010/08/09>

-- Modify Date: <2016/01/27>

-- =============================================

ALTER FUNCTION [dbo].[NetPortal_GetProductPrice]

(

@ProductCodevarchar(24),

@RateStructure varchar(24),--MEMBER or LIST

@Subsystem varchar(10) = null

)

RETURNS numeric(12,2)

AS

begin

declare@CurrentPricenumeric(12,2)

set @CurrentPrice = 0

if @Subsystem = 'PCK'

Begin

declare @bundlesum numeric(12,2)

select @bundlesum = SUM(gppv.PRICE)

from AMS.ppro.dbo.[AHIMA_GetProductPricingView] gppv

join AMS.ppro.dbo.PRODUCT_COMPONENT pc (nolock)

on gppv.product_id = pc.COMPONENT_PRODUCT_ID

join AMS.ppro.dbo.product p (nolock)

on p.product_id = pc.product_id

where p.product_code = @ProductCode

AND getdate() between gppv.PRICE_BEGIN_DATE and ISNULL(gppv.PRICE_END_DATE,getdate())

and gppv.RATE_STRUCTURE = isnull(@RateStructure,gppv.RATE_STRUCTURE)

and gppv.RATE_CODE = 'STD'

group by p.product_code

select @CurrentPrice= @bundlesum - @bundlesum*p2.PACKAGE_DISCOUNT_PCT/100

from AMS.ppro.dbo.[PRODUCT] p2 (nolock)

where p2.PRODUCT_CODE=@productcode

End

else

Begin

Select @CurrentPrice = PRICE

from AMS.ppro.dbo.[AHIMA_GetProductPricingView] gppv

Where gppv.Product_Code = @ProductCode

AND getdate() between gppv.PRICE_BEGIN_DATE and ISNULL(gppv.PRICE_END_DATE,getdate())

AND gppv.RATE_STRUCTURE = isnull(@RateStructure,RATE_STRUCTURE)

and gppv.RATE_CODE = 'STD'

order by PRICE_BEGIN_DATE

End

if @@rowcount = 0 and @RateStructure = 'MEMBER'

BEGIN

return dbo.DinfoPortal_GetProductPrice(@ProductCode,'LIST', null)

END

return @CurrentPrice

END