USE [CSSAndiCoSpareParts]
GO
/****** Object:  UserDefinedFunction [dbo].[ProductReplacement]    Script Date: 27/02/2017 6:53:02 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER FUNCTION [dbo].[ProductReplacement] (
	@ProductCode VARCHAR(25)
)

RETURNS VARCHAR(250)
AS
BEGIN


DECLARE @FinalReplacement VARCHAR(250) = ''
DECLARE @Replacement VARCHAR(250) = ''

IF(DBO.ProductObsolete(@ProductCode) = 0)
BEGIN
	SET @FinalReplacement = @ProductCode
END
ELSE

DECLARE @ReplacementFound INT = 0
SET @Replacement = (SELECT APN FROM vwProductAPN WHERE APN <> 'ZZDELETED'
									AND ProductCode = @ProductCode)

IF(ISNULL(@Replacement,'') = '')
BEGIN
	SET @FinalReplacement = @ProductCode
END
ELSE
BEGIN
	WHILE @ReplacementFound = 0
	BEGIN

		IF(ISNULL(@Replacement,'') = '')
		BEGIN
			SET @FinalReplacement = @Replacement
			SET @ReplacementFound = 1
		END

		IF(DBO.ProductObsolete(@Replacement) = 1)
		BEGIN
			SET @Replacement = (SELECT APN FROM vwProductAPN WHERE APN <> 'ZZDELETED'
										AND ProductCode = @Replacement)

		END
		ELSE
		BEGIN
			SET @FinalReplacement = @Replacement
			SET @ReplacementFound = 1

		END

	END
END

RETURN @FinalReplacement




END