IF EXISTS
  (SELECT *
     FROM SYS.[objects] SO
    WHERE SO.[name] = 'KAAS_FN_CheckCodeExists'
      AND SO.[type] = 'FN')
BEGIN
  DROP FUNCTION [dbo].[KAAS_FN_CheckCodeExists]  
END
GO


CREATE FUNCTION
       [dbo].[KAAS_FN_CheckCodeExists]
      (
	  @Mode varchar(10),
	  @TableName varchar(50),
	  @CodeVal varchar(10),
	  @Description varchar(200),
	  @NewCodeVal varchar(20)
	
	  )
RETURNS VARCHAR(MAX)
AS
/********************************************************************************
 *																				*
 * Returns selected line number from multi-line Address							*
 * [dbo].[KAAS_FN_CheckCodeExists] 'INSERT','UndertakingStatus', '','PENDING'	*
 * Stored Procedure : KAAS_FN_CheckCodeExists									*
 * Maintenance History															*
 * 2021-06-15  Natarajan S             Created									*																			*
 * 2023-08-28	Ghayathri.S.V		   Inserted the Document CLass and Type to 
									   check if the code exists		            *
 * 2023-08-29	Ghayathri.S.V		   Inserted Marital status, Tax type, Tax Head
									   module to check if the code exists		*
 *******************************************************************************/

BEGIN

DECLARE   @Result varchar(10)

DECLARE @Qry Nvarchar(max)

DECLARE @ColCode varchar(20)

IF(@TableName='UndertakingStatus')
	BEGIN
		SET @ColCode = 'StatusDesc'
		IF(@Mode = 'INSERT')
			SET @Qry = 'select '+@ColCode+' FROM ' +@TableName +' WHERE ' +@ColCode+'=''' +@NewCodeVal +''''
		ELSE
			SET @Qry = 'select '+@ColCode+' FROM ' +@TableName +' WHERE ' +@ColCode+'=''' +@NewCodeVal +''''+ ' AND RecordId <>''' +@CodeVal +''''
	END


 
ELSE IF(@TableName='ClientType')
	BEGIN
		SET @ColCode = 'Description'
		IF(@Mode = 'INSERT')
			SET @Qry = 'select '+@ColCode+' FROM ' +@TableName +' WHERE ' +@ColCode+'=''' +@Description +''''
		ELSE
			SET @Qry = 'select '+@ColCode+' FROM ' +@TableName +' WHERE ' +@ColCode+'=''' +@Description +''''+ ' AND PK_ClientType <>''' +@CodeVal +''''
	END 




ELSE IF(@TableName='UDFCategory')
	BEGIN
		SET @ColCode = 'UDFCategory'
		IF(@Mode = 'INSERT')
			SET @Qry = 'select '+@ColCode+' FROM ' +@TableName +' WHERE ' +@ColCode+'=''' +@Description +''''
		ELSE
			SET @Qry = 'select '+@ColCode+' FROM ' +@TableName +' WHERE ' +@ColCode+'=''' +@Description +''''+ ' AND ID <>''' +@CodeVal +''''
	END 


ELSE IF(@TableName='Maritals')
	BEGIN
		SET @ColCode = 'CDECODE'
		IF(@Mode = 'INSERT')
			SET @Qry = 'select '+@ColCode+' FROM MiscCodes ' +' WHERE CDETYPE = ''MRS'' AND ' +@ColCode+'=''' +@CodeVal +''''
		ELSE
			SET @Qry = 'select '+@ColCode+' FROM MiscCodes ' +' WHERE CDETYPE = ''MRS'' AND ' +@ColCode+'=''' +@NewCodeVal +''''+ 'AND '+ @ColCode +'<>''' +@CodeVal +''''
	END 
 

ELSE
	BEGIN 
		IF(@TableName='CategoryType')
			BEGIN
				SET @ColCode = 'CategoryType'
			END 
		ELSE IF(@TableName='BillDescriptions')
			BEGIN
				SET @ColCode = 'SEARCHFIELD'
			END 
		ELSE IF(@TableName='DocumentClasses')
			BEGIN
				SET @ColCode = 'CLASSCODE'
			END
		ELSE IF(@TableName='filetypes')
			BEGIN
				SET @ColCode = 'Type'
			END
		ELSE IF(@TableName='TaxType')
			BEGIN
				SET @ColCode = 'TaxType'
			END
		ELSE IF(@TableName='TaxHead')
			BEGIN
				SET @ColCode = 'TAXHEAD'
			END
		ELSE
			BEGIN
				SET @ColCode = (	SELECT 
									C.COLUMN_NAME 
					FROM  
						INFORMATION_SCHEMA.TABLE_CONSTRAINTS T  
					JOIN 
						INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE C  
					  ON 
						C.CONSTRAINT_NAME=T.CONSTRAINT_NAME  
					WHERE  
						C.TABLE_NAME=@TableName  
					  AND
						T.CONSTRAINT_TYPE='PRIMARY KEY'
				)
			END


			IF(@Mode = 'INSERT')
				BEGIN
					SET @Qry = 'select '+@ColCode+' FROM ' +@TableName +' WHERE ' +@ColCode+'=''' +@CodeVal +''''
				END
			ELSE
				BEGIN
					SET @Qry = 'select '+@ColCode+' FROM ' +@TableName +' WHERE ' +@ColCode+'=''' +@NewCodeVal +''''+ 'AND '+ @ColCode +'<>''' +@CodeVal +''''
				END
	END


exec sp_executesql  @Qry

return @result 

end

GO