SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
GO

ALTER   FUNCTION [Verified].[Fn_GetLicenceExceptions]
(
	@LicenceID BIGINT
)
RETURNS VARCHAR(2000)
AS
BEGIN

DECLARE @LicenceExceptions VARCHAR(2000) = ''
DECLARE @ApplicationrequestDetailID BIGINT
DECLARE @ApplicationRequestTypeID BIGINT
DECLARE @LicenceStatusID SMALLINT
DECLARE @OperatingLicenceNumber VARCHAR(20)
DECLARE @MemberID BIGINT
DECLARE @VehicleID BIGINT
DECLARE @CertficateNumber VARCHAR(20)
DECLARE @OriginatingLicenceNumber VARCHAR(20)
DECLARE @ModeID BIGINT
DECLARE @OriginalOLASNumber VARCHAR(20)
DECLARE @OriginalLTPSPermitNumber VARCHAR(20)
DECLARE @AssociationID BIGINT
DECLARE @ApplicationWorkflowInstanceID BIGINT
DECLARE @IdNumber VARCHAR(20)
DECLARE @ApplicantIDNumber VARCHAR(20)
DECLARE @SourceApplicationID SMALLINT

SELECT @ApplicationrequestDetailID = ApplicationrequestDetailID,@MemberID = MemberID, @VehicleID = VehicleID, @OperatingLicenceNumber = App.Fn_StripVersionNumber(OperatingLicenceNumber,'/'), @CertficateNumber = CertificateNumber, @LicenceStatusID = LicenceStatusID, @OriginalOLASNumber = OriginalOLASNumber, @OriginalLTPSPermitNumber = OriginalLTPSPermitNumber
FROM Verified.Licences
WHERE LicenceID = @LicenceID

SELECT @OriginatingLicenceNumber = App.Fn_StripVersionNumber(OperatingLicenceNumber, '/'), @ApplicationRequestTypeID = Ard.ApplicationRequestTypeID, @ApplicationWorkflowInstanceID = Ard.WorkflowInstanceID, @SourceApplicationID = Ard.SourceApplicationID
FROM App.ApplicationExistingLicences Ael
	INNER JOIN App.ApplicationRequestDetails Ard ON Ard.ApplicationRequestDetailID = Ael.ApplicationRequestDetailID
WHERE Ael.ApplicationrequestDetailID = @ApplicationrequestDetailID

SELECT @ApplicantIDNumber = IDNumberOrBusinessRegistrationNo
FROM App.ApplicationApplicantDetails
WHERE ApplicationRequestDetailID = @ApplicationrequestDetailID

SELECT @ModeID = ModeID
FROM Verified.Licences
WHERE LicenceID = @LicenceID

SELECT @AssociationID = VerifiedAssociationID
FROM app.ApplicationApplicantDetails
WHERE ApplicationRequestDetailID = @ApplicationrequestDetailID

IF @LicenceStatusID = 7 /*cancelled*/
BEGIN
	RETURN @LicenceExceptions --RETURN a blank string as no exceptions have  to be calculated FOR cancelled licences
END

IF EXISTS(SELECT * FROM App.ApplicationRequestDetails WHERE ApplicationRequestDetailID = @ApplicationrequestDetailID)
BEGIN
	/*
	Make sure related application is in a completed state
	*/
	IF NOT EXISTS(SELECT * FROM App.ApplicationRequestDetails INNER JOIN wf.WorkflowInstances ON WorkflowInstances.WorkflowInstanceID = ApplicationRequestDetails.WorkflowInstanceID  WHERE ApplicationRequestDetailID = @ApplicationrequestDetailID AND WorkflowInstanceStatusID = 2) --2=completed 
	BEGIN
		SET @LicenceExceptions = @LicenceExceptions + 'Related application is not in a completed status,'
	END
	ELSE 
	BEGIN
		/*
		Make sure all the required steps was completed
		*/
		IF EXISTS(
			SELECT *
			FROM Wf.WorkflowInstanceSteps Wis
				INNER JOIN Wf.WorkflowTemplateSteps Wts ON Wts.WorkflowTemplateStepID = Wis.WorkflowTemplateStepID
			WHERE WorkflowInstanceID = @ApplicationWorkflowInstanceID
				AND Wts.WorkflowTemplateStepTypeID <> 3 /*branch*/
				AND IsRequired = 1
				AND Wis.WorkflowTemplateStepID NOT IN
				(
					SELECT WorkflowTemplateStepID
					FROM Wf.WorkflowInstanceTasks
					WHERE WorkflowInstanceID = @ApplicationWorkflowInstanceID
						AND WorkflowInstanceTaskStatusID IN (2 /*approved*/, 4 /*Completed*/, 12 /*Override*/)
				)
			)
		BEGIN
			SET @LicenceExceptions = @LicenceExceptions + 'Not all the required steps on the application was completed successfully,'
		END
	END	
END
ELSE
BEGIN
	SET @LicenceExceptions = @LicenceExceptions + 'Related application not found,'
END

IF EXISTS(SELECT * FROM Verified.Members WHERE MemberID = @MemberID)
BEGIN		
	SELECT @IdNumber = IDNumberOrBusinessRegistrationNo
	FROM Verified.Members
	WHERE MemberID = @MemberID

	IF ISNULL(@IdNumber,'') <> ''
	BEGIN
		/*
		Make sure the member id is unique in the verified members database
		*/
		IF EXISTS(SELECT * FROM Verified.Members WHERE IDNumberOrBusinessRegistrationNo LIKE @IdNumber AND MemberID <> @MemberID)
		BEGIN
			SET @LicenceExceptions = @LicenceExceptions + 'No id number can also be found on another member,'
		END
	END
	ELSE
	BEGIN
		SET @LicenceExceptions = @LicenceExceptions + 'No id number specified for member,'
	END
END
ELSE
BEGIN
	SET @LicenceExceptions = @LicenceExceptions + 'No member linked to the licence,'
END

DECLARE @VIN VARCHAR(150)
DECLARE @EngineNumber VARCHAR(150)
DECLARE @IgnoreVinException BIT = 0
DECLARE @VehicleRegistrationNumber VARCHAR(100)

IF ISNULL(@VehicleID,0) = 0
BEGIN
	SET @LicenceExceptions = @LicenceExceptions + 'No vehicle linked to the licence,'
END

IF @OperatingLicenceNumber NOT LIKE 'LT%' /*don't validate the vehicle for temporary licences*/
BEGIN	
	IF ISNULL(@VehicleID,0) <> 0 /*vehicle specified*/
	BEGIN			
		SELECT @VIN = VIN, @EngineNumber = EngineNumber, @VehicleRegistrationNumber = RegistrationNumber, @IgnoreVinException = ISNULL(IgnoreVinException,0)
		FROM Verified.Vehicles
		WHERE VehicleID = @VehicleID

		IF ISNULL(@VIN,'') <> '' AND @VIN NOT LIKE '%_delinked%'
		BEGIN						
			IF System.Fn_IsValidVin(@VIN) = 0 AND @IgnoreVinException = 0
			BEGIN						
				SET @LicenceExceptions = @LicenceExceptions + 'The vehicle VIN appears to be incorrect,'
			END
		END	
	END
	
	IF @LicenceStatusID <> 1 /*not awaiting issuing*/ OR ISNULL(@VehicleID,0) <> 0 /*vehicle specified*/
	BEGIN	
		IF EXISTS(SELECT * FROM Verified.Vehicles WHERE VehicleID = @VehicleID)
		BEGIN				
			SELECT @VIN = VIN, @EngineNumber = EngineNumber, @VehicleRegistrationNumber = RegistrationNumber, @IgnoreVinException = IgnoreVinException
			FROM Verified.Vehicles
			WHERE VehicleID = @VehicleID
		
			IF ISNULL(@VIN,'') <> '' AND ISNULL(@EngineNumber,'') <> ''
			BEGIN								
				IF @VIN LIKE '%_delinked%'
				BEGIN
					SET @LicenceExceptions = @LicenceExceptions + 'The vehicle VIN is in a delinked status,'
				END
				ELSE
				BEGIN															
					/*
					Make sure the vehicle is not currently linke to a different, active licence
					*/
					IF EXISTS(SELECT * FROM Verified.Licences WHERE LicenceID <> @LicenceID AND VehicleID = @VehicleID AND LicenceStatusID IN (1,2,3,4,5) AND App.Fn_StripVersionNumber(OperatingLicenceNumber,'/') NOT LIKE @OperatingLicenceNumber AND OperatingLicenceNumber NOT LIKE 'LT%')
					BEGIN
						SET @LicenceExceptions = @LicenceExceptions + 'The vehicle has also been linked to another licence,'
					END

					/*
					Make sure the VIN is unique in the verified vehicles database
					*/
					IF EXISTS(SELECT * FROM Verified.Vehicles WHERE VIN LIKE @VIN AND VehicleID <> @VehicleID AND VehicleID IN (SELECT VehicleID FROM Verified.Licences WHERE VehicleID = @VehicleID AND LicenceStatusID IN (1,2,3,4,5)))
					BEGIN
						SET @LicenceExceptions = @LicenceExceptions + 'The VIN number has also been linked to another vehicle,'
					END

					/*
					Make sure the Engine number is unique in the verified vehicles database
					*/
					IF EXISTS(SELECT * FROM Verified.Vehicles WHERE EngineNumber LIKE @EngineNumber AND VehicleID <> @VehicleID AND VehicleID IN (SELECT VehicleID FROM Verified.Licences WHERE VehicleID = @VehicleID AND LicenceStatusID IN (1,2,3,4,5)))
					BEGIN
						SET @LicenceExceptions = @LicenceExceptions + 'The Engine number has also been linked to another vehicle,'
					END
				END			
			END
			ELSE
			BEGIN
				SET @LicenceExceptions = @LicenceExceptions + 'No VIN or engine number specified for vehicle,'
			END

			/*
			Make sure all the requied vehicle details have been captured
			*/
			IF EXISTS(SELECT * FROM Verified.Vehicles WHERE VehicleID = @VehicleID AND 
				(
					ISNULL(RegistrationNumber,'') = '' OR
					ISNULL(SeatedCapacity, 0) = 0 OR
					ISNULL(GrossMass, 0) = 0 OR
					ISNULL(VehicleMakeID, 0) = 0 OR
					ISNULL(VehicleTypeID, 0) = 0 OR
					ISNULL(CarryingCapacityID, 0) = 0 OR
					ISNULL(YearOfManufacture, '') = ''
				)
			)
			BEGIN
				SET @LicenceExceptions = @LicenceExceptions + 'Not all the vehicle details have been specified,'
			END
		END
		ELSE
		BEGIN
			SET @LicenceExceptions = @LicenceExceptions + 'No vehicle linked to the licence,'
		END
	END
END

/*Check if the application is allowed if it is a new OL*/
IF @ApplicationRequestTypeID = 1 /*new ol*/ AND @ModeID IN (2,3)
BEGIN	
	IF EXISTS (SELECT * FROM App.ApplicationServiceDetails WHERE ApplicationRequestDetailID = @ApplicationrequestDetailID AND ServiceTypeID = 2 /*Unscheduled*/)			
	BEGIN
		SET @VehicleRegistrationNumber = UPPER(REPLACE(@VehicleRegistrationNumber, ' ',''))

		-- Only validate vehicle registration number if one is provided (making it optional for unscheduled vehicles)
		IF ISNULL(@VehicleRegistrationNumber,'') <> ''
		BEGIN
			IF NOT EXISTS(SELECT * FROM App.ApplicationAllowedNewLicences WHERE AssociationID = @AssociationID AND IdNumberOrBusinessRegistrationNumber = @IDNumber AND UPPER(REPLACE(RegistrationNumber, ' ','')) LIKE @VehicleRegistrationNumber)
			BEGIN
				SET @LicenceExceptions = @LicenceExceptions + 'The association/member/vehicle registration number has not been enabled for new operating licence applications,'
			END

			IF EXISTS(
				SELECT *
				FROM App.ApplicationRequestDetails Ard
					INNER JOIN App.ApplicationApplicantDetails Aad ON Aad.ApplicationRequestDetailID = Ard.ApplicationRequestDetailID
					INNER JOIN App.ApplicationVehicleDetails Avd ON Avd.ApplicationRequestDetailID = Ard.ApplicationRequestDetailID
					INNER JOIN Wf.WorkflowInstances Wi ON Wi.WorkflowInstanceID = Ard.WorkflowInstanceID
				WHERE Aad.ApplicationRequestDetailID <> @ApplicationrequestDetailID
					AND Ard.ApplicationRequestTypeID = 1 /*New operating licence*/
					AND  REPLACE(Aad.IDNumberOrBusinessRegistrationNo, ' ','') LIKE REPLACE(@IdNumber,' ','')
					AND REPLACE(Avd.VehicleRegistrationNumber,' ','') LIKE REPLACE(@VehicleRegistrationNumber,' ','')
					AND Wi.WorkflowInstanceStatusID IN (1 /*In Progress*/)
				)
				BEGIN
					SET @LicenceExceptions = @LicenceExceptions + 'The association/member/vehicle registration number is linked to another in-progress application,'
				END 

			IF EXISTS(
				SELECT *
				FROM Verified.Vw_OperatingLicenceDetails
				WHERE ApplicationRequestDetailID <> @ApplicationrequestDetailID
					AND ApplicationRequestTypeID = 1 /*New operating licence*/
					AND  REPLACE(IDNumberOrBusinessRegistrationNo, ' ','') LIKE REPLACE(@IdNumber,' ','')
					AND REPLACE(RegistrationNumber,' ','') LIKE REPLACE(@VehicleRegistrationNumber,' ','')
					AND LicenceStatus <> 'Cancelled'
				)
				BEGIN
					SET @LicenceExceptions = @LicenceExceptions + 'The association/member/vehicle registration number is linked to another in-progress application,'
				END 			               				
		END        		
	END	
END

/*
Check for duplcate certificate numbers
*/
IF ISNULL(@CertficateNumber,'') <> ''
BEGIN
	IF EXISTS(SELECT * FROM Verified.Licences WHERE CertificateNumber LIKE @CertficateNumber AND LicenceID <> @LicenceID AND LicenceStatusID IN (1,2,3,4,5))
	BEGIN
		SET @LicenceExceptions = @LicenceExceptions + 'The certificate number has been used on another licence,'		
	END
END

/*
Check that the issue and expire dates are present is after the issue date, and after today
*/
IF EXISTS(SELECT * FROM Verified.Licences WHERE LicenceID = @LicenceID AND (DateOfIssue IS NULL OR DateOfExpiry IS NULL) AND LicenceStatusID IN (1,2,3,4,5))
BEGIN
	SET @LicenceExceptions = @LicenceExceptions + 'The issued or expire date is missing,'
END
ELSE
BEGIN
	IF EXISTS(SELECT * FROM Verified.Licences WHERE LicenceID = @LicenceID AND (DateOfExpiry < DateOfIssue) AND LicenceStatusID IN (1,2,3,4,5))
	BEGIN
		SET @LicenceExceptions = @LicenceExceptions + 'The expiry date is after the issue date,'
	END
END


/*
Check that the originating licence has not been used before
*/
IF LEN(@OriginatingLicenceNumber) > 0 AND @OriginatingLicenceNumber NOT LIKE 'LG%'
BEGIN
	IF @ApplicationRequestTypeID <> 11
	BEGIN
		IF EXISTS(
			SELECT * 
			FROM App.ApplicationExistingLicences  Ael
				INNER JOIN App.ApplicationRequestDetails Ard ON Ael.ApplicationRequestDetailID = Ard.ApplicationRequestDetailID
				INNER JOIN Wf.WorkflowInstances Wi ON Ard.WorkflowInstanceID = Wi.WorkflowInstanceID
			WHERE Ael.ApplicationRequestDetailID <> @ApplicationrequestDetailID
				AND Wi.WorkflowInstanceStatusID NOT IN (3,4)
					AND Ard.ApplicationRequestTypeID <> 11 --Temporary (Special) licence
					AND Ard.ApplicationRequestTypeID <> 27 --Temporary Replacements
				AND OperatingLicenceNumber  NOT LIKE 'LG%'
				AND App.Fn_StripVersionNumber(OperatingLicenceNumber,'/') LIKE @OriginatingLicenceNumber			
				)
		BEGIN
			SET @LicenceExceptions = @LicenceExceptions + 'The originating licence has also been used on another application,'
		END
	END
END

/*
Check that the OriginalOLASNumber has not been used on any other licences
*/
IF ISNULL(@OriginalOLASNumber,'') <> ''
BEGIN
	IF EXISTS(SELECT * FROM Verified.Licences WHERE LicenceID <> @LicenceID AND OperatingLicenceNumber NOT LIKE 'LT%' AND LicenceStatusID <> 6 /*archived*/ AND LicenceStatusID <> 7 /*cancelled*/ AND App.Fn_StripVersionNumber(OriginalOLASNumber,'/') LIKE App.Fn_StripVersionNumber(@OriginalOLASNumber,'/'))
	BEGIN
		SET @LicenceExceptions = @LicenceExceptions + 'The originating OL number has been used on another licence,'		
	END
END

/*
Check that the OriginalPermitNumber has not been used on any other licences
*/
IF ISNULL(@OriginalLTPSPermitNumber,'') <> ''
BEGIN
	IF EXISTS(SELECT * FROM Verified.Licences WHERE LicenceID <> @LicenceID AND OperatingLicenceNumber NOT LIKE 'LT%' AND App.Fn_StripVersionNumber(OriginalLTPSPermitNumber,'/') LIKE App.Fn_StripVersionNumber(@OriginalLTPSPermitNumber,'/') AND LicenceStatusID <> 7 /*cancelled*/)
	BEGIN
		SET @LicenceExceptions = @LicenceExceptions + 'The originating permit number has been used on another licence,'		
	END
END

/*
Check if we have more than one issue workflow in progress for the same licence
*/
IF @OriginatingLicenceNumber LIKE 'LG%'
BEGIN
	DECLARE @IssueCount INT = 0

	SELECT @IssueCount = COUNT(*)
	FROM Verified.Licences L
	WHERE LicenceStatusID = 1 /*awaiting issue*/
		AND App.Fn_StripVersionNumber(OperatingLicenceNumber,'/') LIKE App.Fn_StripVersionNumber(@OriginatingLicenceNumber,'/')
	GROUP BY  App.Fn_StripVersionNumber(L.OperatingLicenceNumber,'/')

	IF @IssueCount > 1
	BEGIN
		SET @LicenceExceptions = @LicenceExceptions + 'The licence is linked to more than one in-progress issue workflow,'		
	END
END

/*
Check that the originating licence is not on the blocked list
*/
IF LEN(@OriginatingLicenceNumber) > 0
BEGIN
	IF EXISTS(SELECT * FROM App.ApplicationBlockedLicences WHERE [App].[Fn_StripVersionNumber](OperatingLicenceNumber, '/') LIKE [App].[Fn_StripVersionNumber](@OriginatingLicenceNumber, '/') AND IsCancelled = 0)
	BEGIN
		SET @LicenceExceptions = @LicenceExceptions + 'The originating licence is currently blocked,'
	END
END

/*
Everything in blocked list
*/
IF EXISTS(SELECT * FROM App.ApplicationBlockedLicences WHERE [App].[Fn_StripVersionNumber](OperatingLicenceNumber, '/') LIKE [App].[Fn_StripVersionNumber](@OperatingLicenceNumber, '/') AND IsCancelled = 0)
BEGIN
	SET @LicenceExceptions = @LicenceExceptions + 'The operating licence is currently blocked,'
END

/*
Check that the ID number is not on the blocked list
*/
IF LEN(@ApplicantIDNumber) > 0
BEGIN
	IF EXISTS(SELECT * FROM App.ApplicationBlockedIdNumbers WHERE IDNumber LIKE @ApplicantIDNumber AND IsCancelled = 0)
	BEGIN
		SET @LicenceExceptions = @LicenceExceptions + 'The ID number is currently blocked,'
	END
END

/*
Check if we have at least one route linked to the licence
*/
IF NOT EXISTS(SELECT * FROM Verified.LicenceRoutes Lr INNER JOIN Verified.Routes R ON R.RouteID = Lr.RouteID  WHERE Lr.LicenceID = @LicenceID AND R.RouteStatusID <> 4 /*cancelled*/)
BEGIN
	SET @LicenceExceptions = @LicenceExceptions + 'No active routes linked to the licence,'
END

/*
Check if we have at least one service type linked to the licence
*/
IF @ApplicationRequestTypeID <> 11 OR (@ApplicationRequestTypeID = 11 AND @SourceApplicationID = 3 /*LG*/) --temp/special licence
BEGIN
	IF NOT EXISTS(SELECT * FROM Verified.LicenceServiceDetails WHERE LicenceID = @LicenceID)
	BEGIN
		SET @LicenceExceptions = @LicenceExceptions + 'No service types linked to the licence,'
	END
END

/*
Check to make sure the mode and service types are correct
*/
IF EXISTS(	
	SELECT * 
	FROM Verified.LicenceServiceDetails
	WHERE LicenceID = @LicenceID
		AND ServiceTypeID NOT IN 
		(
			SELECT ServiceTypeID FROM App.Fn_AllowedServiceTypesForMode(@ModeID)
		)
)
BEGIN
	SET @LicenceExceptions = @LicenceExceptions + 'The service types are not valid for the specified mode,'
END

/*
Check to make sure:
	the vehicle type is valid for the mode
	the carrying capacity is valid for the mode
	the seated is valid for the mode
*/
IF ISNULL(@VehicleID,-1) <> -1
BEGIN
	DECLARE @VehicleTypeID SMALLINT
	DECLARE @CarryingCapacityID SMALLINT
	DECLARE @SeatedCapacity INT

	SELECT 
		@VehicleTypeID = VehicleTypeID,
		@CarryingCapacityID = CarryingCapacityID,
		@SeatedCapacity = SeatedCapacity
	FROM Verified.Vehicles 
	WHERE VehicleID = @VehicleID

	/*vehicle type check*/
	IF ISNULL(@VehicleTypeID,-1) <> -1
	BEGIN
		IF 
			(@ModeID = 1 /*Bus*/ AND @VehicleTypeID <> 3 /*BUS*/) OR
			(@ModeID = 2 /*Midibus*/ AND @VehicleTypeID <> 10 /*MIDIBUS*/) OR
			(@ModeID = 3 /*Minibus Taxi*/ AND @VehicleTypeID <> 2 /*MINIBUS*/) OR
			(@ModeID = 4 /*Metered Taxi*/ AND (@VehicleTypeID NOT IN (1 /*MOTORCAR*/, 11 /*OTHER*/))) OR
			(@ModeID = 5 /*Other*/ AND @VehicleTypeID <> 11 /*OTHER*/) 
		BEGIN
			SET @LicenceExceptions = @LicenceExceptions + 'The vehicle type is not valid for the specified mode,'
		END
	END

	/*carrying capacity check*/
	IF ISNULL(@CarryingCapacityID,-1) <> -1
	BEGIN
		IF 
			(@ModeID = 1 /*Bus*/ AND @CarryingCapacityID <> 4 /*35+*/) OR
			(@ModeID = 2 /*Midibus*/ AND @CarryingCapacityID <> 3 /*17-35*/) OR
			(@ModeID = 3 /*Minibus Taxi*/ AND @CarryingCapacityID <> 2 /*9-16*/) OR
			(@ModeID = 4 /*Metered Taxi*/ AND @CarryingCapacityID <> 1 /*4-8*/)			
		BEGIN
			SET @LicenceExceptions = @LicenceExceptions + 'The carrying capacity is not valid for the specified mode,'
		END
	END

	/*seated capacity check*/
	IF ISNULL(@SeatedCapacity,-1) <> -1
	BEGIN
		IF 			
			(@ModeID = 2 /*Midibus*/ AND @SeatedCapacity > 34) OR
			(@ModeID = 3 /*Minibus Taxi*/ AND @SeatedCapacity > 16) OR
			(@ModeID = 4 /*Metered Taxi*/ AND @SeatedCapacity > 7)
		BEGIN
			SET @LicenceExceptions = @LicenceExceptions + 'The seated capacity is not valid for the specified mode,'
		END
	END
	ELSE
    BEGIN
		SET @LicenceExceptions = @LicenceExceptions + 'No seated capacity specified,'
	END
END

/*
Check if the applicant and existing licence holder is different if it is a transfer application
*/
IF App.Fn_IsDeceasedTransferType(@ApplicationrequestTypeID) = 1 OR App.Fn_IsNormalTransferType(@ApplicationrequestTypeID) = 1
BEGIN
	DECLARE @ApplicantHolderID VARCHAR(255)
	DECLARE @ExistingLicenceHolderID VARCHAR(255)

	SELECT @ApplicantHolderID = IDNumberOrBusinessRegistrationNo FROM App.ApplicationApplicantDetails WHERE ApplicationRequestDetailID = @ApplicationrequestDetailID
	SELECT @ExistingLicenceHolderID = IDNumberOrBusinessRegistrationNo FROM App.ApplicationExistingLicenceHolders WHERE ApplicationRequestDetailID = @ApplicationrequestDetailID

	IF @ApplicantHolderID = @ExistingLicenceHolderID
	BEGIN
		SET @LicenceExceptions = @LicenceExceptions + 'No applicant change on transfer application,'
	END	
END


IF LEN(ISNULL(@LicenceExceptions,'')) <> 0
BEGIN
	SET @LicenceExceptions = SUBSTRING(@LicenceExceptions, 1, LEN(@LicenceExceptions)-1)
END

RETURN @LicenceExceptions

END



GO

