IF NOT EXISTS ( SELECT * 
            FROM   sysobjects 
            WHERE  id = object_id(N'[dbo].[GetAmountPaidToAssistantFee]') 
                   and OBJECTPROPERTY(id, N'IsScalarFunction') = 1 )
BEGIN
	
	DECLARE @query  AS NVARCHAR(MAX)
	set @query =	'CREATE FUNCTION [dbo].[GetAmountPaidToAssistantFee] 
		(
			@allocatedReference NVARCHAR(MAX)
		)
		RETURNS DECIMAL(18,4)
		AS
		BEGIN
			-- Declare the return variable here
			DECLARE @allocatedAmount DECIMAL(18,4);

			SELECT @allocatedAmount = SUM(amount)
			FROM	pal_Account P
			WHERE	P.recId IN (SELECT * FROM dbo.SplitString(LEFT(@allocatedReference,CASE WHEN CHARINDEX('':'',@allocatedReference) > 0 THEN CHARINDEX('':'',@allocatedReference)-1 ELSE 0 END),'',''))
					AND P.modifier1 IN (''0008'',''0009'')

			-- Return the result of the function
			RETURN @allocatedAmount

		END'

		exec sp_executesql @query;
END