USE [LegitimateKZN_QA_SO]
GO
/****** Object:  StoredProcedure [App].[Pr_PrecheckDetailsUpsert]    Script Date: 2025/08/26 09:56:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [App].[Pr_PrecheckDetailsUpsert]
(
	@PrecheckDetailID BIGINT,
	@ApplicationRequestTypeID SMALLINT, 
	@SourceApplicationID SMALLINT,
	@ApplicantName VARCHAR(400), 
	@OlPermitNumber VARCHAR(50), 
	@IDNumber VARCHAR(20), 
	@ChassisNumber VARCHAR(50), 
	@VehicleRegistrationNumber VARCHAR(50), 
	@AssociationID BIGINT,
	@CheckData BIT,
	@PrecheckDetailIncompleteReasonID SMALLINT,
	@ContactEmail VARCHAR(255),
	@ContactCell VARCHAR(20),
	@NumberOfVehiclesRequired BIGINT,
	@UserID BIGINT,
	@ModeID SMALLINT,
	@ServiceTypeID SMALLINT,
	@CalledFromSave BIT
)
AS
BEGIN
	BEGIN TRANSACTION
	BEGIN TRY
		
		DECLARE @OLIssueNumber SMALLINT = App.Fn_GetVersionNumber(@OlPermitNumber,'/')
		SET @OlPermitNumber = [App].[Fn_StripVersionNumber](@OlPermitNumber,'/')
		DECLARE @IsLtpsConversion BIT = CASE WHEN @SourceApplicationID=2 /*ltps*/ AND @ApplicationRequestTypeID <> 11 /*Special License*/ AND @ApplicationRequestTypeID <> 27 /*Temporary replacement*/ THEN 1 ELSE 0 END

		/*First check if the OL is not on the blocked OL list*/
		IF EXISTS(SELECT * FROM App.ApplicationBlockedLicences WHERE [App].[Fn_StripVersionNumber](OperatingLicenceNumber, '/') LIKE @OlPermitNumber AND IsCancelled=0)
		BEGIN
			DECLARE @BlockedReason VARCHAR(255)

			SELECT TOP 1 @BlockedReason = 'Blocked licence: ' + ISNULL(Reason,'')
			FROM App.ApplicationBlockedLicences 
			WHERE [App].[Fn_StripVersionNumber](OperatingLicenceNumber, '/') LIKE @OlPermitNumber

			RAISERROR(@BlockedReason, 16, 1)
			RETURN
		END

		/*check if the ID number is not on the blocked OL list*/
		IF EXISTS(SELECT * FROM App.ApplicationBlockedIdNumbers WHERE IDNumber LIKE @IDNumber AND IsCancelled=0)
		BEGIN
			DECLARE @BlockedReasonIDNumber VARCHAR(255)

			SELECT TOP 1 @BlockedReasonIDNumber = 'Blocked ID number: ' + ISNULL(Reason,'')
			FROM App.ApplicationBlockedIdNumbers 
			WHERE IDNumber LIKE @IDNumber

			RAISERROR(@BlockedReasonIDNumber, 16, 1)
			RETURN
		END

		/*Block transactions based on temporary licenses */
		IF UPPER(LEFT(ISNULL(@OlPermitNumber,''),2)) = 'LT'
		BEGIN
			RAISERROR('Applications can''t be started against a temporary licence', 16, 1)
			RETURN
		END

		/*Make sure the servcie type is valid for the selected mode */
		IF @ApplicationRequestTypeID = 1 /*new app*/ OR @SourceApplicationID = 2 /*LTPS*/
		BEGIN
			IF NOT EXISTS (
				SELECT *
				FROM App.Fn_AllowedServiceTypesForMode(@ModeID)
				WHERE ServiceTypeID = @ServiceTypeID
			)
			BEGIN
				RAISERROR('The service type is invalid for the selected mode.', 16, 1)
				RETURN
			END
		END

		/*Make sure we have an originating licence if requiried*/
		IF @ApplicationRequestTypeID <> 1 /*New Operating Licence*/
		BEGIN
			IF ISNULL(@OlPermitNumber, '') = ''
			BEGIN
				RAISERROR('No originating licence number specified', 16, 1)
				RETURN
			END            
		END

		/*Check if the application is allowed if it is a new OL*/
		IF @ApplicationRequestTypeID = 1 /*new ol*/ AND @CalledFromSave = 0
		BEGIN			
			IF @ServiceTypeID = 2 /*unscheduled service*/ AND (@ModeID = 2 OR @ModeID = 3)
			BEGIN				
				DECLARE @ErrorReason VARCHAR(2000)

				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
						RAISERROR('The association/member/vehicle registration number has not been enabled for new operating licence applications', 16, 1)
						RETURN                    
					END

					IF EXISTS(SELECT * FROM App.ApplicationAllowedNewLicences WHERE AssociationID = @AssociationID AND IdNumberOrBusinessRegistrationNumber = @IDNumber AND UPPER(REPLACE(RegistrationNumber, ' ','')) LIKE @VehicleRegistrationNumber AND IsUsed = 1)
					BEGIN					
						DECLARE @UsedOn VARCHAR(1000) 

						SELECT @UsedOn = COALESCE(@UsedOn + ',', '') + Wi.ReferenceNumber
						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 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*/,2 /*Completed*/)

						SET @ErrorReason = 'The association/member/vehicle registration number has been enabled for new operating licence applications, but has already been flagged as used.'
						SET @ErrorReason = @ErrorReason + 'The application(s) linked to the id/registration is ' + ISNULL(@UsedOn,'')

						RAISERROR(@ErrorReason, 16, 1)
						RETURN                    
					END	
				END
			END
		END

		/*If the LG licence is in an expired state the application type must be New Operating Licence (From Source)*/		
		IF @SourceApplicationID = 3 /*LG*/
		BEGIN
			IF EXISTS(SELECT * FROM Verified.Licences WHERE [App].[Fn_StripVersionNumber](OperatingLicenceNumber,'/') LIKE @OlPermitNumber AND LicenceStatusID = 4 /*4=expired*/)
			BEGIN
				IF @ApplicationRequestTypeID <> 9 /*New Operating Licence (From Source)*/ AND @ApplicationRequestTypeID <> 25 /*Deceased Transfer and New Operating Licence*/
				BEGIN
					RAISERROR('The licence is in an expired state. Please start a New Operating Licence (From Source) instead.  The licence will have to be added to exception list first.', 16, 1)
					RETURN
				END				 
			END
		END		
	    
		/*
			if the source is not LG, check if the OL or permit number has already been used in the system and block it if it has
			unless it was used for a special permit, in which case further checks below will block it if it should be blocked
		*/
		IF @SourceApplicationID <> 3 /*LG*/
		BEGIN
			IF EXISTS(
				SELECT Ard.ApplicationRequestDetailID 
				FROM App.Applicationrequestdetails Ard
					INNER JOIN Wf.WorkflowInstances Wi ON Ard.WorkflowInstanceID = Wi.WorkflowInstanceID
					INNER JOIN App.ApplicationExistingLicences Ael ON Ael.ApplicationRequestDetailID = Ard.ApplicationRequestDetailID
				WHERE Wi.WorkflowInstanceStatusID <> 4 /*cancelled*/
					AND Ard.ApplicationRequestTypeID <> 11 /*Temporary (Special) licence*/ 
					AND Ard.ApplicationRequestTypeID <> 27 /*Temporary Replacements*/ 
					AND [App].[Fn_StripVersionNumber](Ael.OperatingLicenceNumber,'/') LIKE @OlPermitNumber
				)
			BEGIN
				IF @ApplicationRequestTypeID IN (3,8,14) /*3=Replacements (COV), 8=Renewal, 14=Renewal and COV*/
							AND EXISTS(
								SELECT *
								FROM App.ApplicationRequestDetails AS ARD
									INNER JOIN App.ApplicationExistingLicences AS AEL ON ARD.ApplicationRequestDetailID = AEL.ApplicationRequestDetailID
									INNER JOIN Wf.WorkflowInstances AS WI ON ARD.WorkflowInstanceID = WI.WorkflowInstanceID
								WHERE WorkflowInstanceStatusID = 1 -- IN Progress
									AND ARD.IsCancelled = 0
									AND Ard.ApplicationRequestTypeID = 2	/*2 = Add Authorisations  Routes*/
									AND [App].[Fn_StripVersionNumber](OperatingLicenceNumber,'/') LIKE [App].[Fn_StripVersionNumber](@OlPermitNumber,'/')							
							)
						BEGIN
							PRINT 'Allowing COV or renewal to be processed'
						END
						ELSE
						BEGIN
							RAISERROR('The permit/operating licence has already been used on another application and can not be used again.', 16, 1)
							RETURN
						END
			END            
		END

		/*Check if the source is allowed for the specified type*/
		DECLARE @LKN SMALLINT = 1
		DECLARE @LTPS SMALLINT = 2
		DECLARE @LG SMALLINT = 3
		DECLARE @SourceIsValid BIT = 0

		IF @ApplicationRequestTypeID = 1 /*New Operating Licence*/
		BEGIN
			IF ISNULL(@OlPermitNumber,'') = '' /*all other new OLs is not from a source*/
			BEGIN 
				SET @SourceIsValid = 1
			END			
		END
		ELSE IF @ApplicationRequestTypeID = 5 /*Conversion (LTPS to legiti-mate)*/
        BEGIN
			IF @SourceApplicationID = @LTPS
			BEGIN
				SET @SourceIsValid = 1 
			END
		END
        ELSE IF @ApplicationRequestTypeID = 9 /*New Operating Licence (From Source)*/
        BEGIN
			IF @SourceApplicationID=@LTPS OR @SourceApplicationID = @LKN OR @SourceApplicationID = @LG /*used for late renewals from OLAS, LTPS and expired legiti-mate applications*/
			BEGIN
				SET @SourceIsValid = 1 
			END
		END
		ELSE IF @ApplicationRequestTypeID = 10 /*Duplicate Licence*/
        BEGIN
			IF @SourceApplicationID = @LG
			BEGIN
				SET @SourceIsValid = 1 
			END
		END
		ELSE IF @ApplicationRequestTypeID = 11 /*Temp Licence*/
        BEGIN
			IF @SourceApplicationID = @LG OR @SourceApplicationID = @LTPS OR @SourceApplicationID = @LKN
			BEGIN
				SET @SourceIsValid = 1 
			END
		END
		ELSE IF @ApplicationRequestTypeID = 13 /*Migration/Conversion (OLAS to Legiti-mate)*/
        BEGIN
			IF @SourceApplicationID = @LKN
			BEGIN
				SET @SourceIsValid = 1 
			END
		END
		ELSE IF @ApplicationRequestTypeID = 27 /*Temporary Replacement*/
        BEGIN
			IF @SourceApplicationID = @LG OR @SourceApplicationID = @LTPS OR @SourceApplicationID = @LKN
			BEGIN
				SET @SourceIsValid = 1 
			END
		END
		ELSE IF @ApplicationRequestTypeID = 28 /*Renewal and increase of carrying capacity*/
        BEGIN
			IF @SourceApplicationID = @LG OR @SourceApplicationID = @LTPS OR @SourceApplicationID = @LKN
			BEGIN
				SET @SourceIsValid = 1 
			END
		END
		ELSE IF @ApplicationRequestTypeID = 29 /*Personal details update*/
        BEGIN
			IF @SourceApplicationID = @LG 
			BEGIN
				SET @SourceIsValid = 1 
			END
		END
		ELSE
		BEGIN
			IF @SourceApplicationID=@LTPS OR @SourceApplicationID = @LKN OR @SourceApplicationID = @LG
			BEGIN
				SET @SourceIsValid = 1 
			END
		END

		IF @SourceIsValid = 0
		BEGIN
			RAISERROR('Invalid originating licence number.', 16, 1)
			RETURN
		END

		IF @CalledFromSave = 0
		BEGIN        
			IF(@PrecheckDetailID = -1)
			BEGIN
				INSERT INTO App.PrecheckDetails(ApplicationRequestTypeID, SourceApplicationID, ApplicantName, OlPermitNumber, IDNumber, ChassisNumber, VehicleRegistrationNumber, AssociationID, IsComplete, PrecheckDetailIncompleteReasonID, ContactEmail, ContactCell, CreatedByID, CreatedDate, UpdatedByID, UpdatedDate, ModeID, IsLtpsConversion, NumberOfVehiclesRequired, ServiceTypeID)
				SELECT @ApplicationRequestTypeID,@SourceApplicationID,@ApplicantName,@OlPermitNumber,@IDNumber,@ChassisNumber,@VehicleRegistrationNumber,@AssociationID,0,@PrecheckDetailIncompleteReasonID, @ContactEmail, @ContactCell,@UserID,GETDATE(),@UserID,GETDATE(),@ModeID,@IsLtpsConversion,@NumberOfVehiclesRequired, @ServiceTypeID
			
				SELECT @PrecheckDetailID = SCOPE_IDENTITY()
			END
			ELSE
			BEGIN
				UPDATE App.PrecheckDetails
					SET IsComplete = 0
				WHERE PrecheckDetailID = @PrecheckDetailID
			END	
		END
        
		DECLARE @IntegrationXml XML,
				@QueryString VARCHAR(MAX),
				@DataFound BIT,
				@DataAssociationID BIGINT,
				@DataAssociationName VARCHAR(2000),
				@DataIDNumberOrBusinessRegistrationNo VARCHAR(20),
				@DataLicenceExpiryDate DATETIME,
				@DataLicenceStatus VARCHAR(255),
				@DataLicenceActive BIT,				
				@DataIsInBypassedExpiryList BIT,
				@DataIsWithinForcedRenewalPeriod BIT,
				@DataServiceTypeXML XML,
				@RoutesXML XML,
				@DataModeID SMALLINT

		DECLARE @VerifiedAssociationID BIGINT,
				@VerifiedAssociationOlasID BIGINT,
				@RenewalGracePeriod INT

		SET @RenewalGracePeriod = CAST(System.Fn_GetSettingValue('RenewalGracePeriod') AS INT)

		SELECT @VerifiedAssociationOlasID = OlasID, @VerifiedAssociationID = AssociationID
		FROM Verified.Associations
		WHERE AssociationID = @AssociationID

		DECLARE @LicenceTable AS TABLE(ID INT IDENTITY(1, 1) primary key, ApplicationNumber VARCHAR(50), AssociationID BIGINT, AssociationName VARCHAR(255), IDNumber VARCHAR(255), LicenceNumber VARCHAR(255), LicenceStartDate DATETIME, LicenceEndDate DATETIME, LicenceIndefinite INT, LicenceStatus VARCHAR(100), LicenceActive BIT, RoutesXml XML, ServiceTypes XML, ModeID SMALLINT, IsInBypassedExpiryList BIT, IsWithinForcedRenewalPeriod BIT, IntegrationXML XML)

		IF ISNULL(@OlPermitNumber,'') <> ''
		BEGIN
			INSERT INTO @LicenceTable(ApplicationNumber, AssociationID,AssociationName,IDNumber,LicenceNumber,LicenceStartDate,LicenceEndDate,LicenceIndefinite,LicenceStatus,LicenceActive,RoutesXml,ServiceTypes,ModeID, IsInBypassedExpiryList, IsWithinForcedRenewalPeriod, IntegrationXML)
			SELECT ApplicationNumber, AssociationID,AssociationName,IDNumber,LicenceNumber,LicenceStartDate,LicenceEndDate,LicenceIndefinite,LicenceStatus,LicenceActive,RoutesXml,ServiceTypesXml, ModeID, IsInBypassedExpiryList, IsWithinForcedRenewalPeriod, FullIntegrationXML
			FROM App.Fn_GetSourceApplicationRecordsFromSourceSystem(@OlPermitNumber, @SourceApplicationID, @ModeID, 1 /*1 will ensure only active, or bypassed expired licence is returned*/, 1 /*will ensure expired record is returned if no active or bypassed licence found*/)	
        
			IF EXISTS(SELECT * FROM @LicenceTable)
			BEGIN
				SET @DataFound = 1
			
				SELECT @DataAssociationID = AssociationID, @DataAssociationName = AssociationName, @DataIDNumberOrBusinessRegistrationNo = IDNumber, @DataLicenceExpiryDate = LicenceEndDate, @DataLicenceStatus = LicenceStatus, @DataLicenceActive = LicenceActive, @DataServiceTypeXML = ServiceTypes, @RoutesXML = RoutesXml, @DataModeID = ModeID, @DataIsInBypassedExpiryList = IsInBypassedExpiryList, @DataIsWithinForcedRenewalPeriod = IsWithinForcedRenewalPeriod, @IntegrationXml = IntegrationXML
				FROM @LicenceTable
			END
			ELSE
			BEGIN
				SET @DataFound = 0
			END

			UPDATE App.PrecheckDetails
				SET NltisXml = @IntegrationXml,
					NltisXmlDataFound = @DataFound
			WHERE PrecheckDetailID = @PrecheckDetailID
		END

		IF(@CheckData = 1)
		BEGIN
			--if new ol and licence can be found in the source system throw an error
			IF(@ApplicationRequestTypeID = 1 AND @DataFound = 1)
			BEGIN
				EXEC System.Pr_SysErrorThrow 'The specified licence can be found in the source system. Start a New OL (From Source) application instead.'
			END

			--if new ol and licence specified, but licence can not be found in the source system, check for duplicates
			IF @ApplicationRequestTypeID = 1 AND ISNULL(@OlPermitNumber,'') <> '' AND @DataFound = 0
			BEGIN				
				IF EXISTS(SELECT *
					FROM App.ApplicationRequestDetails AS ARD
					INNER JOIN App.ApplicationExistingLicences AS AEL ON ARD.ApplicationRequestDetailID = AEL.ApplicationRequestDetailID
					INNER JOIN Wf.WorkflowInstances AS WI ON ARD.WorkflowInstanceID = WI.WorkflowInstanceID
					WHERE WorkflowInstanceStatusID <> 4 --cancelled
						AND ARD.IsCancelled = 0
						AND [App].[Fn_StripVersionNumber](OperatingLicenceNumber,'/') LIKE [App].[Fn_StripVersionNumber](@OlPermitNumber,'/')
					)
				BEGIN
					EXEC System.Pr_SysErrorThrow 'The specified licence has already been used in the system.'
				END				
			END

			-- if not data found (@DataFound) and source is nltis (@SourceApplicationID = 1) and it is not a new application (@ApplicationRequestTypeID <> 1) > throw an error
			IF(@DataFound = 0 AND @SourceApplicationID = 1 AND @ApplicationRequestTypeID <> 1)
			BEGIN
				EXEC System.Pr_SysErrorThrow 'The source licence can not be found in source NLTIS, please recheck all given information. '
			END
			
			IF(@DataFound = 0 AND @SourceApplicationID = 2 AND @ApplicationRequestTypeID <> 1)
			BEGIN
				EXEC System.Pr_SysErrorThrow 'The source licence can not be found in source LTPS, please recheck all given information. '
			END

			IF(@DataFound = 0 AND @SourceApplicationID = 3 AND @ApplicationRequestTypeID <> 1)
			BEGIN
				EXEC System.Pr_SysErrorThrow 'The source licence can not be found in source Legitimate, please recheck all given information. '
			END

			--block any applications if an issue workflow is in progress for the same licence
			IF (@DataFound = 1 AND @SourceApplicationID = 3 /*legiti-mate*/)
			BEGIN
				IF EXISTS(
					SELECT * 
					FROM App.LicenceIssueDetails Lid
						INNER JOIN Verified.Licences L ON L.LicenceID = Lid.VerifiedLicenceID
						INNER JOIN Wf.WorkflowInstances Wi ON Lid.WorkflowInstanceID = Wi.WorkflowInstanceID
					WHERE App.Fn_StripVersionNumber(OperatingLicenceNumber,'/') LIKE App.Fn_StripVersionNumber(@OlPermitNumber,'/')
						AND Wi.WorkflowInstanceStatusID = 1 /*in progress*/
					)
				BEGIN
					EXEC System.Pr_SysErrorThrow 'A licence issue request workflow is currently in progress for this originating licence and will have to be completed before a new application is started.'
				END
			END
			
			DECLARE @Error VARCHAR(1000)

			IF @DataFound = 1 AND @ApplicationRequestTypeID <> 1 
			BEGIN								                
				-- kick out an application where the ids are not the same
				IF (ISNULL(@IDNumber, '') <> ISNULL(@DataIDNumberOrBusinessRegistrationNo, ''))
				BEGIN
					SET @Error = 'The licence you have specified is registered under a different id number than the one you have specified (Registered ID Number: ' + ISNULL(@DataIDNumberOrBusinessRegistrationNo,'') + ').'
					EXEC System.Pr_SysErrorThrow @Error
				END

				-- Kick out any OL/Permit number that is active on a workflow
				IF EXISTS (
					SELECT *
					FROM App.ApplicationRequestDetails AS ARD
					INNER JOIN App.ApplicationExistingLicences AS AEL ON ARD.ApplicationRequestDetailID = AEL.ApplicationRequestDetailID
					INNER JOIN Wf.WorkflowInstances AS WI ON ARD.WorkflowInstanceID = WI.WorkflowInstanceID
					WHERE WorkflowInstanceStatusID = 1 -- IN Progress
						AND ARD.IsCancelled = 0
						AND [App].[Fn_StripVersionNumber](OperatingLicenceNumber,'/') LIKE [App].[Fn_StripVersionNumber](@OlPermitNumber,'/')						
				)
				BEGIN
					--allow special licences even if another application is in progress as long as the other app is not a special one as well
					IF @ApplicationRequestTypeID = 11 
					BEGIN
						IF EXISTS(SELECT * 
							FROM App.ApplicationRequestDetails AS ARD
								INNER JOIN App.ApplicationExistingLicences AS AEL ON ARD.ApplicationRequestDetailID = AEL.ApplicationRequestDetailID
								INNER JOIN Wf.WorkflowInstances AS WI ON ARD.WorkflowInstanceID = WI.WorkflowInstanceID
								WHERE WorkflowInstanceStatusID = 1 -- IN Progress
									AND ARD.IsCancelled = 0
									AND [App].[Fn_StripVersionNumber](OperatingLicenceNumber,'/') LIKE [App].[Fn_StripVersionNumber](@OlPermitNumber,'/')						
									AND Ard.ApplicationRequestTypeID = 11 --special
									)
						BEGIN
							SELECT @Error = 'The licence you have specified is currently locked by another active special licence workflow (' + ApplicationNumber + ').'
							FROM App.Vw_ApplicationRequestDetails AS ARD
							INNER JOIN App.ApplicationExistingLicences AS AEL ON ARD.ApplicationRequestDetailID = AEL.ApplicationRequestDetailID
							INNER JOIN Wf.WorkflowInstances AS WI ON ARD.WorkflowInstanceID = WI.WorkflowInstanceID
							WHERE WorkflowInstanceStatusID = 1 -- IN Progress
								AND [App].[Fn_StripVersionNumber](OperatingLicenceNumber,'/') LIKE [App].[Fn_StripVersionNumber](@OlPermitNumber,'/')												

							EXEC System.Pr_SysErrorThrow @Error		
						END
					END
					ELSE
					BEGIN
						--if new type is "Replacements (COV)" or "Renewal" or "Renewal and COV" and the other app in progress is an "Add Authorisations/Routes", allow the COV or Renewal to be processed
						--the add auth will be linked to this application on issue of the COV or Renewal
						IF @ApplicationRequestTypeID IN (3,8,14,4,10,11,19,22) /*3=Replacements (COV), 8=Renewal, 14=Renewal and COV*/
							AND EXISTS(
								SELECT *
								FROM App.ApplicationRequestDetails AS ARD
									INNER JOIN App.ApplicationExistingLicences AS AEL ON ARD.ApplicationRequestDetailID = AEL.ApplicationRequestDetailID
									INNER JOIN Wf.WorkflowInstances AS WI ON ARD.WorkflowInstanceID = WI.WorkflowInstanceID
								WHERE WorkflowInstanceStatusID = 1 -- IN Progress
									AND ARD.IsCancelled = 0
									AND Ard.ApplicationRequestTypeID = 2	/*2 = Add Authorisations  Routes*/
									AND [App].[Fn_StripVersionNumber](OperatingLicenceNumber,'/') LIKE [App].[Fn_StripVersionNumber](@OlPermitNumber,'/')							
							)
						BEGIN
							PRINT 'Allowing COV or renewal to be processed'
						END
						ELSE
						BEGIN
							SELECT @Error = 'The licence you have specified is currently locked by an active workflow (' + ApplicationNumber + ').'
							FROM App.Vw_ApplicationRequestDetails AS ARD
							INNER JOIN App.ApplicationExistingLicences AS AEL ON ARD.ApplicationRequestDetailID = AEL.ApplicationRequestDetailID
							INNER JOIN Wf.WorkflowInstances AS WI ON ARD.WorkflowInstanceID = WI.WorkflowInstanceID
							WHERE WorkflowInstanceStatusID = 1 -- IN Progress
								AND [App].[Fn_StripVersionNumber](OperatingLicenceNumber,'/') LIKE [App].[Fn_StripVersionNumber](@OlPermitNumber,'/')												

							EXEC System.Pr_SysErrorThrow @Error			
						END						
					END
				END

				--Check if the OLAS OL or LTPS Permit number that has already been used to uplift a legitimate licence
				DECLARE @ExistingLegitimateNumber VARCHAR(100) = ''
				
				IF EXISTS(
					SELECT *
					FROM Verified.Licences Vl
						INNER JOIN App.ApplicationRequestDetails Ard ON Vl.ApplicationRequestDetailID = Ard.ApplicationRequestDetailID
						INNER JOIN App.ApplicationExistingLicences El ON Ard.ApplicationRequestDetailID = El.ApplicationRequestDetailID
					WHERE [App].[Fn_StripVersionNumber](El.OperatingLicenceNumber,'/') LIKE [App].[Fn_StripVersionNumber](@OlPermitNumber,'/')
						AND Ard.SourceApplicationID IN (1,2)
						AND Vl.LicenceStatusID <> 7 --7=cancelled
						AND Ard.ApplicationRequestTypeID <> 11 --Temporary (Special) licence
				)
				BEGIN
					SELECT TOP 1 @ExistingLegitimateNumber = Vl.OperatingLicenceNumber
					FROM Verified.Licences Vl
						INNER JOIN App.ApplicationRequestDetails Ard ON Vl.ApplicationRequestDetailID = Ard.ApplicationRequestDetailID
						INNER JOIN App.ApplicationExistingLicences El ON Ard.ApplicationRequestDetailID = El.ApplicationRequestDetailID
					WHERE [App].[Fn_StripVersionNumber](El.OperatingLicenceNumber,'/') LIKE [App].[Fn_StripVersionNumber](@OlPermitNumber,'/')
						AND Ard.SourceApplicationID IN (1,2)
						AND Vl.LicenceStatusID <> 7 --7=cancelled
						AND Ard.ApplicationRequestTypeID <> 11 --Temporary (Special) licence

					SELECT @Error = 'The licence you have specified has already been migrated to Legiti-mate (' + ISNULL(@ExistingLegitimateNumber,'') + ').'
					EXEC System.Pr_SysErrorThrow @Error
				END		
				
				--if the source is not LTPS (multiple LTPS special licences are allowed as per PCR172)
				--check if the OL has been used for a special licence, in which case the user is allowed to use the licence again, 
				--provided that the new application request type is not a special licence (only one special licence is allowed against a non-legitimate source)
				IF @SourceApplicationID <> 2 /*LTPS*/				
				BEGIN
					IF EXISTS(
						SELECT *
						FROM Verified.Licences Vl
							INNER JOIN App.ApplicationRequestDetails Ard ON Vl.ApplicationRequestDetailID = Ard.ApplicationRequestDetailID
							INNER JOIN App.ApplicationExistingLicences El ON Ard.ApplicationRequestDetailID = El.ApplicationRequestDetailID
						WHERE [App].[Fn_StripVersionNumber](El.OperatingLicenceNumber,'/') LIKE [App].[Fn_StripVersionNumber](@OlPermitNumber,'/')
							AND Ard.SourceApplicationID IN (1,2)
							AND Vl.LicenceStatusID <> 7 --7=cancelled
							AND Ard.ApplicationRequestTypeID = 11 --Temporary (Special) licence
					)
					BEGIN
						IF @ApplicationRequestTypeID = 11
						BEGIN
							SELECT TOP 1 @ExistingLegitimateNumber = Vl.OperatingLicenceNumber
							FROM Verified.Licences Vl
								INNER JOIN App.ApplicationRequestDetails Ard ON Vl.ApplicationRequestDetailID = Ard.ApplicationRequestDetailID
								INNER JOIN App.ApplicationExistingLicences El ON Ard.ApplicationRequestDetailID = El.ApplicationRequestDetailID
							WHERE [App].[Fn_StripVersionNumber](El.OperatingLicenceNumber,'/') LIKE [App].[Fn_StripVersionNumber](@OlPermitNumber,'/')
								AND Ard.SourceApplicationID IN (1,2)
								AND Vl.LicenceStatusID <> 7 --7=cancelled
								AND Ard.ApplicationRequestTypeID = 11 --Temporary (Special) licence

							SELECT @Error = 'You are not allowed to start another Special licence request against this source application as the operator has already done one before (' + ISNULL(@ExistingLegitimateNumber,'') + ').  The licence must be migrated to legiti-mate first before continuing.'
							EXEC System.Pr_SysErrorThrow @Error
						END							
					END
				END	
			END
			
			IF @CalledFromSave = 0
			BEGIN
				IF(@DataFound = 1 AND @ApplicationRequestTypeID NOT IN (1 /*New Operating Licence*/,9 /*New Operating Licence (From Source)*/))
				BEGIN				
					
					--Check if a renewal application should be started, in addition to the application type specified	
					--disabled 20190709. forcing linked transaction instead
					/*
					IF (DATEDIFF(DAY, GETDATE(), @DataLicenceExpiryDate) < @RenewalGracePeriod) 
						AND @ApplicationRequestTypeID <> 11 --Temporary Licence
						AND @ApplicationRequestTypeID <> 9 --new OL from source
						AND @ApplicationRequestTypeID <> 25 --Deceased Transfer and New Operating Licence
						AND @ApplicationRequestTypeID <> 27 --Temporary Replacements
					BEGIN						
						UPDATE App.PrecheckDetails
							SET IsRenewal = 1,							
								IsComplete = 0
						WHERE PrecheckDetailID = @PrecheckDetailID
					END
					*/

					IF (DATEDIFF(DAY, GETDATE(), @DataLicenceExpiryDate) < @RenewalGracePeriod) 
						AND @ApplicationRequestTypeID <> 11 --Temporary Licence
						AND @ApplicationRequestTypeID <> 9 --new OL from source
						AND @ApplicationRequestTypeID <> 25 --Deceased Transfer and New Operating Licence
						AND @ApplicationRequestTypeID <> 27 --Temporary Replacements						
						AND App.Fn_IsRenewalType(@ApplicationRequestTypeID) = 0
					BEGIN						
						SELECT @Error = 'This licence is expiring soon. Start the related linked applicaton type instead.'
						EXEC System.Pr_SysErrorThrow @Error
						RETURN
					END
					
					--Check if the licence expired today or over the weekend, and add it to the App.ApplicationAllowedExpiredLicences table so it can still be processed.
					DECLARE @ExpiredTodayOrOverTheWeekend BIT = 0
					IF NOT EXISTS(SELECT * FROM @LicenceTable WHERE IsInBypassedExpiryList=1)
					BEGIN
						IF EXISTS(SELECT * FROM @LicenceTable WHERE LicenceNumber = @OlPermitNumber AND LicenceStatus= 'Expired' AND DAY(LicenceEndDate) = DAY(GETDATE()) AND MONTH(LicenceEndDate) = MONTH(GETDATE()) AND YEAR(LicenceEndDate) = YEAR(GETDATE()))
							OR 
							(
								DATENAME(dw,GETDATE()) = 'Monday' --check weekend if it is currently Monday
								AND 
								(
									EXISTS(SELECT * FROM @LicenceTable WHERE LicenceNumber = @OlPermitNumber AND LicenceStatus= 'Expired' AND DAY(LicenceEndDate) = DAY( DATEADD(d,-1,GETDATE())) AND MONTH(LicenceEndDate) = MONTH( DATEADD(d,-1,GETDATE())) AND YEAR(LicenceEndDate) = YEAR( DATEADD(d,-1,GETDATE())))
									OR
									EXISTS(SELECT * FROM @LicenceTable WHERE LicenceNumber = @OlPermitNumber AND LicenceStatus= 'Expired' AND DAY(LicenceEndDate) = DAY( DATEADD(d,-2,GETDATE())) AND MONTH(LicenceEndDate) = MONTH( DATEADD(d,-2,GETDATE())) AND YEAR(LicenceEndDate) = YEAR( DATEADD(d,-2,GETDATE())))
								)
							)
						BEGIN
							SET @ExpiredTodayOrOverTheWeekend = 1

							INSERT INTO App.ApplicationAllowedExpiredLicences(OperatingLicenceNumber,IssueNumber, ApplicationNumber, ApplicationSourceID, CreatedByID, CreatedDate, CreatedReason)
							SELECT LicenceNumber, @OLIssueNumber, ApplicationNumber, @SourceApplicationID, 1 /*system user*/, GETDATE(), 'Auto-adding to bypass list due to expiry over weekend or on the day of application'
							FROM @LicenceTable
						END
					END

					--make sure the source licence is active, unless it is the bypass list (for applications expiring over a weekend or on the same day)
					IF NOT EXISTS(SELECT * FROM @LicenceTable WHERE IsInBypassedExpiryList=1) AND @ExpiredTodayOrOverTheWeekend=0
					BEGIN
						IF (App.Fn_IsLicenceActive(@DataLicenceStatus,@SourceApplicationID) = 0) OR @DataLicenceActive = 0
						BEGIN						
							SET @Error = 'The given licence (' + @OlPermitNumber + ') is not active. Please start a New Operating Licence (From Source) instead.  The licence will have to be added to exception list first.'
							EXEC System.Pr_SysErrorThrow @Error						
						END
					END
				END
			
				IF(@DataFound = 1 AND @ApplicationRequestTypeID  = 9 /*New Operating Licence (From Source)*/)
				BEGIN												
					--Add the licence to the App.ApplicationAllowedExpiredLicences table so it can still be processed.
					IF NOT EXISTS(SELECT * FROM @LicenceTable WHERE IsInBypassedExpiryList=1)
					BEGIN
						INSERT INTO App.ApplicationAllowedExpiredLicences(OperatingLicenceNumber,IssueNumber, ApplicationNumber, ApplicationSourceID, CreatedByID, CreatedDate, CreatedReason)
						SELECT LicenceNumber, @OLIssueNumber, ApplicationNumber, @SourceApplicationID, 1 /*system user*/, GETDATE(), 'Auto-adding to bypass list for a New Operating Licence (From Source) application type'
						FROM @LicenceTable				
					END
				END
			END

			--OLAS specific validation			
			IF(@DataFound = 1 AND @SourceApplicationID = 1 AND @ApplicationRequestTypeID <> 1)
			BEGIN				
				-- kick out an application where the assocations are not the same
				IF (ISNULL(@VerifiedAssociationOlasID, 0) <> ISNULL(@DataAssociationID, 0))
				BEGIN
					DECLARE @OlasAssociationName VARCHAR(100)

					SELECT @OlasAssociationName = ISNULL(sAssocName,'') + ' (ID=' + CAST(@DataAssociationID AS VARCHAR(20)) + ')'
					FROM LegitimateKZN_OLAS.dbo.Associations
					WHERE pkiAssocId = @DataAssociationID

					SET @Error = 'The licence you have specified is registered under a different association than the one you have specified (OLAS Association: ' + ISNULL(@OlasAssociationName,'') + ').'
					EXEC System.Pr_SysErrorThrow @Error
				END												
			END
			
			--Legitimate specific validaton
			IF(@DataFound = 1 AND @SourceApplicationID = 3 AND @ApplicationRequestTypeID <> 1)
			BEGIN		
				-- kick out an application where the mode is not the same
				IF @ModeID <> @DataModeID
				BEGIN
					SET @Error = 'The specified mode does not match the existing licence mode.'
					EXEC System.Pr_SysErrorThrow @Error
				END
                
				--kick out any application where the service type is not the same
				IF @DataServiceTypeXML IS NOT NULL
				BEGIN			
						
					IF NOT EXISTS
						(
						SELECT t.p.value('(servicetype/licenceservicetypeid)[1]', 'SMALLINT')
						FROM @DataServiceTypeXML.nodes('(/servicetypes)') t(p)
						WHERE t.p.value('(servicetype/licenceservicetypeid)[1]', 'SMALLINT') = @ServiceTypeID
						)
					BEGIN
						SET @Error = 'The specified service type does not match the existing licence.'
						EXEC System.Pr_SysErrorThrow @Error
					END
				END

				-- kick out an application where the assocations are not the same
				IF (ISNULL(@DataAssociationID, 0) <> 0 AND (ISNULL(@VerifiedAssociationID, 0) <> ISNULL(@DataAssociationID, 0)))
				BEGIN
					DECLARE @VerifiedAssociationName VARCHAR(100)

					SELECT @VerifiedAssociationName = ISNULL(AssociationName,'') + ' (ID=' + CAST(@DataAssociationID AS VARCHAR(20)) + ')'
					FROM Verified.Associations
					WHERE AssociationID = @DataAssociationID

					SET @Error = 'The licence you have specified is registered under a different association than the one you have specified (Verified Association: ' + ISNULL(@VerifiedAssociationName,'') + ').'
					EXEC System.Pr_SysErrorThrow @Error
				END																																
			END

			--Validate the route information
			IF @DataFound = 1 AND @ApplicationRequestTypeID <> 1 AND @CalledFromSave = 0
			BEGIN								                
				--check all the routes and make sure they exist for the specified association
				IF @RoutesXML IS NOT NULL
				BEGIN
					DECLARE @RouteNumbersWithProblems VARCHAR(1000)
					DECLARE @Routes TABLE(NationalRouteNumberInXml VARCHAR(255), BoardRouteNumberInXml VARCHAR(255), RouteName VARCHAR(1000), RouteDescription VARCHAR(MAX), VerifiedRouteID BIGINT NULL)
					DELETE FROM @Routes 

					INSERT INTO @Routes(NationalRouteNumberInXml, BoardRouteNumberInXml, RouteName, RouteDescription)
					SELECT 
						t.p.value('(nationalroutenumber)[1]','VARCHAR(255)') AS NationalRouteNumber,
						t.p.value('(boardroutenumber)[1]','VARCHAR(255)') AS BoardRouteNumber,						
						t.p.value('(routename)[1]','VARCHAR(1000)') AS RouteName,
						t.p.value('(routedescription)[1]','VARCHAR(MAX)') AS RouteDescription
					FROM @RoutesXML.nodes('(/routes/route)') t(p)

					UPDATE @Routes
					SET RouteDescription = REPLACE(RouteDescription, ' ','')

					DELETE FROM @Routes
					WHERE ISNULL(RouteName,'') = '' OR
						ISNULL(RouteDescription,'') = '' OR
						LOWER(RouteDescription) LIKE '%conditions%' OR
						LOWER(RouteName) LIKE '%N/A%' OR 
						LOWER(RouteDescription) LIKE '%OOOOOOO%'						

					UPDATE @Routes
					SET VerifiedRouteID = (SELECT TOP 1 RouteID FROM Verified.Routes WHERE (NationalRouteNumber LIKE R.NationalRouteNumberInXml OR BoardRouteNumber LIKE R.BoardRouteNumberInXml) AND RouteStatusID = 1 /*Active*/)					
					FROM @Routes R

					IF EXISTS(SELECT * FROM @Routes WHERE VerifiedRouteID IS NULL)
					BEGIN
						SELECT @RouteNumbersWithProblems = COALESCE(@RouteNumbersWithProblems + ',', '  ') + CAST(NationalRouteNumberInXml AS VARCHAR(20))  
						FROM @Routes 
						WHERE VerifiedRouteID IS NULL

						SET @Error = 'The following routes does not exists currently and will have to be added before the application can be started: ' + ISNULL(@RouteNumbersWithProblems,'')
					
						EXEC System.Pr_SysErrorThrow @Error
					END

					IF ISNULL(@AssociationID, -1) <> -1
					BEGIN
						IF EXISTS(
							SELECT * 
							FROM @Routes R
								LEFT JOIN Verified.AssociationRoutes Ar ON Ar.AssociationID = @AssociationID AND R.VerifiedRouteID = Ar.RouteID
							WHERE Ar.RouteID IS NULL
						)
						BEGIN
							SELECT @RouteNumbersWithProblems = COALESCE(@RouteNumbersWithProblems + ',', '  ') + CAST(NationalRouteNumberInXml AS VARCHAR(20))  
							FROM @Routes R
								LEFT JOIN Verified.AssociationRoutes Ar ON Ar.AssociationID = @AssociationID AND R.VerifiedRouteID = Ar.RouteID
							WHERE Ar.RouteID IS NULL

							SET @Error = 'The following routes exists but are not linked to the association in question: ' + ISNULL(@RouteNumbersWithProblems,'')
					
							EXEC System.Pr_SysErrorThrow @Error
						END
					END
				END
			END
		END

		/*Check if the member is linked to the association*/		
		IF @ServiceTypeID = 2 /*2=Unscheduled*/ AND (@ModeID = 2 OR @ModeID = 3)
		BEGIN			
			DECLARE @VerifiedMemberID BIGINT
			DECLARE @ValidInVerifiedMembers BIT = 0
			DECLARE @ValidInNeoTable BIT = 0
			DECLARE @ValidInAllowedNewOLs BIT = 0

			SELECT @VerifiedMemberID = MemberID
			FROM Verified.Members 
			WHERE IDNumberOrBusinessRegistrationNo LIKE @IDNumber

			IF @VerifiedMemberID <> 0
			BEGIN
				IF NOT EXISTS(SELECT * FROM Verified.AssociationMembers WHERE AssociationID = @AssociationID AND MemberID = @VerifiedMemberID)
				BEGIN
					SET @ValidInVerifiedMembers = 0
				END
				ELSE
				BEGIN
					SET @ValidInVerifiedMembers = 1
				END
			END

			IF @ValidInVerifiedMembers = 0
			BEGIN
				DECLARE @NeoAssociationID BIGINT

				SELECT @NeoAssociationID = ISNULL(A.NeoID,0)
				FROM Verified.Associations A 
				WHERE A.AssociationID = @AssociationID

				IF NOT EXISTS(SELECT * FROM LegitimateKZN_Neo.dbo.AssociationMemberData WHERE [Member ID Number] = @IdNumber AND AssociationID = @NeoAssociationID)
				BEGIN
					SET @ValidInNeoTable = 0
				END
				ELSE
				BEGIN
					SET @ValidInNeoTable = 1
				END
			END	

			IF @ValidInVerifiedMembers = 0 AND @ValidInNeoTable = 0
			BEGIN
				IF NOT EXISTS(SELECT * FROM App.ApplicationAllowedNewLicences WHERE IdNumberOrBusinessRegistrationNumber = @IdNumber AND AssociationID = @AssociationID)
				BEGIN
					SET @ValidInAllowedNewOLs = 0
				END
				ELSE
				BEGIN
					SET @ValidInAllowedNewOLs = 1
				END
			END

			IF @ValidInVerifiedMembers = 0 AND @ValidInNeoTable = 0 AND @ValidInAllowedNewOLs = 0
			BEGIN
				RAISERROR('The specified member can not be verified for the association.', 16, 1)
				RETURN
			END			
		END

		/*
		Set renewal flag for all linked renewal types as well
		*/
		IF App.Fn_IsRenewalType(@ApplicationRequestTypeID) = 1 AND @CalledFromSave = 0
		BEGIN
			UPDATE App.PrecheckDetails
				SET IsRenewal = 1
			WHERE PrecheckDetailID = @PrecheckDetailID
		END
		
		COMMIT TRANSACTION 
	END TRY
	BEGIN CATCH
		IF XACT_STATE() <> 0
		BEGIN
			ROLLBACK TRANSACTION;

			SET @PrecheckDetailID = -1
		END
		EXEC [System].Pr_SysErrorRethrow
	END CATCH

	SELECT @PrecheckDetailID
END