USE [LegitimateNational]
GO
/****** Object:  StoredProcedure [App].[Pr_ApplicationPrecheckCreate]    Script Date: 2026/05/11 10:44:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER   PROCEDURE [App].[Pr_ApplicationPrecheckCreate]
	@ApplicationRequestTypeID SMALLINT,
	@SourceApplicationID SMALLINT = 3,
	@ApplicantName VARCHAR(510),
	@TelCell VARCHAR(14),
	@IdTypeID SMALLINT,
	@IdOrBusinessRegistrationNumber	VARCHAR(20),	
	@OriginatingLicenceNumbers VARCHAR(2000),
	@OriginatingAccreditationNumber VARCHAR(25),
	@VehicleTypeID SMALLINT,
	@ServiceTypeIDs VARCHAR(255),
	@NumberOfLicencesRequired BIGINT,
	@ApplicationProvinceID SMALLINT,
	@ApplicationRegionID SMALLINT,
	@ApplicationAssociationID BIGINT,
	@ApplicationAllowedNewLicenceIDs VARCHAR(2000),
	@SelectedRouteIDs VARCHAR(2000),
	@ParentApplicationDetailID BIGINT,
	@ParentApplicationLicenceID BIGINT,
	@UserID BIGINT
AS
BEGIN	
	SET NOCOUNT ON;
	
	/********************************************************************************************
	Get user details and perform security checks
	*********************************************************************************************/	
	DECLARE @EntityIDToCheck INT = 1000 /*Application*/
	DECLARE @ItemIDToCheck BIGINT = NULL
	DECLARE @Action VARCHAR(255) = 'Precheck Create'
	DECLARE @OfficeLocationID BIGINT, @DivisionTypeID SMALLINT, @ProvinceID SMALLINT, @RegionID SMALLINT, @HasAccess BIT
	
	EXEC App.Pr_GetUserLocationAndSecurity @UserID, @EntityIDToCheck, @ItemIDToCheck, @Action, @OfficeLocationID OUTPUT, @DivisionTypeID OUTPUT, @ProvinceID OUTPUT, @RegionID OUTPUT, @HasAccess OUTPUT

	IF @HasAccess = 0
	BEGIN
		RAISERROR('User location and security error', 16, 1)
		RETURN
	END	

	/********************************************************************************************
	Create precheck application
	*********************************************************************************************/
	DECLARE @ApplicationPrecheckDetailID BIGINT
	DECLARE @VerifiedMemberID BIGINT	
	DECLARE @VerifiedAccreditationID BIGINT	
	
	INSERT INTO App.ApplicationPrecheckDetails(ApplicationRequestTypeID,SourceApplicationID, ApplicantName, TelCell, IdTypeID, IdOrBusinessRegistrationNumber, OriginatingLicenceNumbers, OriginatingAccreditationNumber, VehicleTypeID, ServiceTypeIDs, NumberOfLicencesRequired, ApplicationProvinceID, ApplicationRegionID, OfficeLocationDivisionTypeID, CreateByID, CreateDate, UpdateByID, UpdateDate, ParentApplicationDetailID, ParentApplicationLicenceID, AssociationID)
	VALUES(@ApplicationRequestTypeID,@SourceApplicationID, @ApplicantName, @TelCell, @IdTypeID, @IdOrBusinessRegistrationNumber, @OriginatingLicenceNumbers, @OriginatingAccreditationNumber, @VehicleTypeID, @ServiceTypeIDs, @NumberOfLicencesRequired, @ApplicationProvinceID, @ApplicationRegionID, @DivisionTypeID, @UserID, GETDATE(), @UserID, GETDATE(), @ParentApplicationDetailID, @ParentApplicationLicenceID, @ApplicationAssociationID)

	SET @ApplicationPrecheckDetailID = SCOPE_IDENTITY()

	SELECT @VerifiedMemberID = MemberID
	FROM Verified.Members WITH(NOLOCK)
	WHERE IDNumberOrBusinessRegistrationNo LIKE @IdOrBusinessRegistrationNumber

	IF ISNULL(@VerifiedMemberID, -1) <> -1
	BEGIN
		SELECT @VerifiedAccreditationID = AccreditationID
		FROM Verified.Accreditations WITH(NOLOCK)
		WHERE MemberID = @VerifiedMemberID
			AND IsLatestIssue = 1
	END

	IF @ApplicationRequestTypeID = 32 /*Renewal of accreditation*/
	BEGIN
		SET @OriginatingLicenceNumbers = NULL

		SELECT @OriginatingLicenceNumbers = COALESCE(@OriginatingLicenceNumbers + ', ', '') + L.OperatingLicenceNumber
		FROM Verified.Licences L WITH(NOLOCK)
		WHERE L.AccreditationID = @VerifiedAccreditationID
			AND L.IsLatestIssue = 1
	END

	/********************************************************************************************
	Create the precheck route record(s)
	*********************************************************************************************/	
	INSERT INTO App.ApplicationPrecheckRouteDetails(ApplicationPrecheckDetailID, VerifiedRouteID)
	SELECT @ApplicationPrecheckDetailID, Value
	FROM System.Fn_GenSplitCSV(@SelectedRouteIDs,',')

	/********************************************************************************************
	Create the empty precheck licence record(s)
	*********************************************************************************************/	
	INSERT INTO App.ApplicationPrecheckLicenceDetails(ApplicationPrecheckDetailID, SourceApplicationID, OperatingLicenceOrPermitNumber, VerifiedLicenceID, SourceApplicationXML)
	SELECT @ApplicationPrecheckDetailID, @SourceApplicationID, Value, NULL,NULL
	FROM System.Fn_GenSplitCSV(@OriginatingLicenceNumbers,',')

	/********************************************************************************************
	Populate the precheck licence records with source data
	*********************************************************************************************/	
	DECLARE @ApplicationPrecheckLicenceDetailID INT
	DECLARE @OperatingLicenceOrPermitNumber VARCHAR(30)
	DECLARE @OperatingLicenceOrPermitNumberWithVersionNumber VARCHAR(30)
		
	DECLARE licenceSourceXml CURSOR FAST_FORWARD READ_ONLY FOR 
	SELECT ApplicationPrecheckLicenceDetailID, App.Fn_StripVersionNumber(OperatingLicenceOrPermitNumber,'/'), OperatingLicenceOrPermitNumber
	FROM App.ApplicationPrecheckLicenceDetails WITH(NOLOCK)
	WHERE ApplicationPrecheckDetailID = @ApplicationPrecheckDetailID
		AND ISNULL(OperatingLicenceOrPermitNumber, '') <> ''
	
	OPEN licenceSourceXml	
	
	FETCH NEXT FROM licenceSourceXml INTO @ApplicationPrecheckLicenceDetailID, @OperatingLicenceOrPermitNumber, @OperatingLicenceOrPermitNumberWithVersionNumber	
	
	WHILE @@FETCH_STATUS = 0
	BEGIN	
		
		DECLARE @VerifiedLicenceID BIGINT 
		DECLARE @SourceLicenceToUseXML XML = App.Fn_GetLicenceToUseFromSourceSystem(@OperatingLicenceOrPermitNumberWithVersionNumber, @SourceApplicationID)   

		IF NOT EXISTS(SELECT t.p.query('.') FROM @SourceLicenceToUseXML.nodes('(/response/application)') t(p))
		BEGIN			
			INSERT INTO App.ApplicationPrecheckOutcomes(ApplicationPrecheckDetailID, Title, IsSuccessful, OutcomeDetails, CreateByID, CreateDate, IsCancelled)
			VALUES(@ApplicationPrecheckDetailID, 'Check if licence can be found in source system', 0, @OperatingLicenceOrPermitNumber, @UserID, GETDATE(), 0)	
		END
		ELSE        
		BEGIN			
			IF @SourceLicenceToUseXML IS NOT NULL
			BEGIN
				SELECT @VerifiedLicenceID =  t.p.value('(licenceid)[1]','bigint') 
				FROM @SourceLicenceToUseXML.nodes('(/response/application/licences/licence)') t(p)			
			END
									
			UPDATE App.ApplicationPrecheckLicenceDetails
			SET SourceApplicationXML = (SELECT t.p.query('.') FROM @SourceLicenceToUseXML.nodes('(/response/application)') t(p)),
				VerifiedLicenceID = @VerifiedLicenceID,
				SourceApplicationID = @SourceApplicationID
			WHERE ApplicationPrecheckLicenceDetailID = @ApplicationPrecheckLicenceDetailID
		END									
	    FETCH NEXT FROM licenceSourceXml INTO @ApplicationPrecheckLicenceDetailID, @OperatingLicenceOrPermitNumber, @OperatingLicenceOrPermitNumberWithVersionNumber
	END	

	CLOSE licenceSourceXml
	DEALLOCATE licenceSourceXml

	/********************************************************************************************
	C39 - Check if ID Number Or Business Registration No is valid
	*********************************************************************************************/	
	IF (SELECT [System].[Fn_IsValidIDNumberOrBusinessRegistrationNo](@IdOrBusinessRegistrationNumber, @IdTypeID)) = 0
	BEGIN
		INSERT INTO App.ApplicationPrecheckOutcomes(ApplicationPrecheckDetailID, Title, IsSuccessful, OutcomeDetails, CreateByID, CreateDate, IsCancelled)
		SELECT @ApplicationPrecheckDetailID, 'ID Number/Business Registration No is invalid', 0, '', @UserID, GETDATE(), 0
	END	
	
	/********************************************************************************************
	C1 - Block transactions based on temporary licenses
	*********************************************************************************************/	
	IF EXISTS(SELECT TOP 1 1  FROM System.Fn_GenSplitCSV(@OriginatingLicenceNumbers,',') WHERE  UPPER(LEFT(ISNULL(VALUE,''),2)) = 'LT')
	BEGIN
		INSERT INTO App.ApplicationPrecheckOutcomes(ApplicationPrecheckDetailID, Title, IsSuccessful, OutcomeDetails, CreateByID, CreateDate, IsCancelled)
		SELECT @ApplicationPrecheckDetailID, 'Applications can''t be started against a temporary licence', 0, VALUE, @UserID, GETDATE(), 0
		FROM System.Fn_GenSplitCSV(@OriginatingLicenceNumbers,',') WHERE  UPPER(LEFT(ISNULL(VALUE,''),2)) = 'LT'
	END	
	
	/********************************************************************************************
	C2 - Check if the OL is not on the blocked OL list
	*********************************************************************************************/	
	IF NOT EXISTS(SELECT TOP 1 1 FROM Purification.ApplicationBlockedLicences WITH(NOLOCK) WHERE ProvinceID = @ProvinceID AND DivisionTypeID = @DivisionTypeID AND App.Fn_StripVersionNumber(OperatingLicenceNumber, '/') IN (SELECT App.Fn_StripVersionNumber(VALUE, '/') FROM System.Fn_GenSplitCSV(@OriginatingLicenceNumbers,',')) AND IsCancelled = 0)
	BEGIN
		INSERT INTO App.ApplicationPrecheckOutcomes(ApplicationPrecheckDetailID, Title, IsSuccessful, OutcomeDetails, CreateByID, CreateDate, IsCancelled)
		SELECT @ApplicationPrecheckDetailID, 'Is the licence on the blocked list', 1, '', @UserID, GETDATE(), 0
	END
	ELSE BEGIN
		DECLARE @BlockedLicense VARCHAR(MAX) = ''
	
		SELECT @BlockedLicense = @BlockedLicense + ''+OperatingLicenceNumber + ', Blocked Reason: ' + ISNULL(Reason,'-') +'' 
		FROM Purification.ApplicationBlockedLicences WITH(NOLOCK) 
		WHERE ProvinceID = @ProvinceID AND DivisionTypeID = @DivisionTypeID AND 
			@OperatingLicenceOrPermitNumber IN (SELECT App.Fn_StripVersionNumber(VALUE, '/') FROM System.Fn_GenSplitCSV(@OriginatingLicenceNumbers,',')) AND IsCancelled = 0

		INSERT INTO App.ApplicationPrecheckOutcomes(ApplicationPrecheckDetailID, Title, IsSuccessful, OutcomeDetails, CreateByID, CreateDate, IsCancelled)
		SELECT @ApplicationPrecheckDetailID, 'Is the licence on the blocked list', 0, @BlockedLicense, @UserID, GETDATE(), 0
	END

	/********************************************************************************************
	C3 - Check if the OL is not on the blocked ID list
	*********************************************************************************************/	
	IF NOT EXISTS(SELECT TOP 1 1 FROM Purification.ApplicationBlockedIDNumbers WITH(NOLOCK) WHERE ProvinceID = @ProvinceID AND DivisionTypeID = @DivisionTypeID AND IDNumber = @IdOrBusinessRegistrationNumber AND IsCancelled = 0)
	BEGIN
		INSERT INTO App.ApplicationPrecheckOutcomes(ApplicationPrecheckDetailID, Title, IsSuccessful, OutcomeDetails, CreateByID, CreateDate, IsCancelled)
		SELECT @ApplicationPrecheckDetailID, 'Is the applicant ID number on the blocked list', 1, '', @UserID, GETDATE(), 0
	END
	ELSE BEGIN
		DECLARE @BlockIDReason VARCHAR(255) = (SELECT TOP 1 Reason FROM Purification.ApplicationBlockedIDNumbers WITH(NOLOCK) WHERE ProvinceID = @ProvinceID AND DivisionTypeID = @DivisionTypeID AND IDNumber = @IdOrBusinessRegistrationNumber AND IsCancelled = 0)
		INSERT INTO App.ApplicationPrecheckOutcomes(ApplicationPrecheckDetailID, Title, IsSuccessful, OutcomeDetails, CreateByID, CreateDate, IsCancelled)
		SELECT @ApplicationPrecheckDetailID, 'Is the applicant ID number on the blocked list', 0, 'ID Number '+@IdOrBusinessRegistrationNumber + ', Blocked ID number: ' + ISNULL(@BlockIDReason,'-'), @UserID, GETDATE(), 0
	END

	/********************************************************************************************
	C4 - Make sure the service type is valid for the selected Vehicle Type
	*********************************************************************************************/	
	IF @ApplicationRequestTypeID = 1 AND @ApplicationRequestTypeID = 31 /*new app*/ AND @VehicleTypeID IS NOT NULL
	BEGIN
		IF NOT EXISTS (SELECT TOP 1 1 FROM Md.v_ServiceTypesByDivision WITH(NOLOCK)
			WHERE ServiceTypeID IN (SELECT VALUE FROM System.Fn_GenSplitCSV(@ServiceTypeIDs,',')) AND
				VehicleTypeID = @VehicleTypeID AND
				ProvinceID = @ProvinceID AND
				DivisionTypeID = @DivisionTypeID
		)
		BEGIN
			INSERT INTO App.ApplicationPrecheckOutcomes(ApplicationPrecheckDetailID, Title, IsSuccessful, OutcomeDetails, CreateByID, CreateDate, IsCancelled)
			SELECT @ApplicationPrecheckDetailID, 'The service type selected is invalid for the selected vehicle type.', 0, '', @UserID, GETDATE(), 0
		END
	END

	/********************************************************************************************
	C5 - Make sure we have an originating licence if requiried
	*********************************************************************************************/	
	IF (@ApplicationRequestTypeID NOT IN (1, 31, 32, 36, 37, 38, 63) /*New Application,New accreditation,Add New Authorisations/Routes,Amend Authorisations/Routes, 63=Conversion of Metered Taxi to E-hailing*/) 
	BEGIN
		IF EXISTS(SELECT TOP 1 1 FROM App.ApplicationPrecheckLicenceDetails WITH(NOLOCK) WHERE ApplicationPrecheckDetailID = @ApplicationPrecheckDetailID AND ISNULL(OperatingLicenceOrPermitNumber,'') = '') OR NOT EXISTS (SELECT TOP 1 1 FROM App.ApplicationPrecheckLicenceDetails WITH(NOLOCK) WHERE ApplicationPrecheckDetailID = @ApplicationPrecheckDetailID)
		BEGIN
			INSERT INTO App.ApplicationPrecheckOutcomes(ApplicationPrecheckDetailID, Title, IsSuccessful, OutcomeDetails, CreateByID, CreateDate, IsCancelled)
			SELECT @ApplicationPrecheckDetailID, 'Valid originating licence number(s) specified', 0, 'No originating licence', @UserID, GETDATE(), 0
		END
	END
	
	/********************************************************************************************
	C6 - If the LG licence is in an expired state the application type must be New Operating Licence (pre-populate with expired licence data) or New Operating Licence (pre-populate with expired deceased transfer licence data)
	*********************************************************************************************/	
	IF EXISTS(SELECT TOP 1 1 FROM Verified.Licences WITH(NOLOCK) WHERE CleanOperatingLicenceNumber = (SELECT App.Fn_StripVersionNumber(OperatingLicenceOrPermitNumber,'/') FROM App.ApplicationPrecheckLicenceDetails WITH(NOLOCK) WHERE ApplicationPrecheckDetailID = @ApplicationPrecheckDetailID) AND LicenceStatusID = 4 /*4=expired*/)
	AND NOT EXISTS(SELECT TOP 1 1 FROM Verified.Licences WITH(NOLOCK) WHERE CleanOperatingLicenceNumber = (SELECT App.Fn_StripVersionNumber(OperatingLicenceOrPermitNumber,'/') FROM App.ApplicationPrecheckLicenceDetails WITH(NOLOCK) WHERE ApplicationPrecheckDetailID = @ApplicationPrecheckDetailID) AND LicenceStatusID IN (1 /*Awaiting Issuing*/,2 /*Issued*/,3 /*Expiring Within 60 Days*/) AND ISNULL(IsLatestIssue, 1) = 1)
	AND (@ApplicationRequestTypeID NOT IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 60, 61, 62, 63))
	BEGIN			
		DECLARE @ExpiredLicenceMessage VARCHAR(MAX) = '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.'

		SELECT @ExpiredLicenceMessage = @ExpiredLicenceMessage  + ', ' +OperatingLicenceNumber + ''
		FROM Verified.Licences WITH(NOLOCK) 
		WHERE CleanOperatingLicenceNumber IN (SELECT App.Fn_StripVersionNumber(OperatingLicenceOrPermitNumber,'/') FROM App.ApplicationPrecheckLicenceDetails WITH(NOLOCK) WHERE ApplicationPrecheckDetailID = @ApplicationPrecheckDetailID) AND LicenceStatusID = 4 /*4=expired*/ 

		INSERT INTO App.ApplicationPrecheckOutcomes(ApplicationPrecheckDetailID, Title, IsSuccessful, OutcomeDetails, CreateByID, CreateDate, IsCancelled)
		SELECT @ApplicationPrecheckDetailID, 'Is the licence(s) expired', 0, @ExpiredLicenceMessage, @UserID, GETDATE(), 0
	END
	ELSE BEGIN
		INSERT INTO App.ApplicationPrecheckOutcomes(ApplicationPrecheckDetailID, Title, IsSuccessful, OutcomeDetails, CreateByID, CreateDate, IsCancelled)
		SELECT @ApplicationPrecheckDetailID, 'Is the licence(s) expired', 1, '', @UserID, GETDATE(), 0
	END


	-- REWORK
	/********************************************************************************************
	C7 - if the source is 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 EXISTS(SELECT TOP 1 1 FROM App.ApplicationDetails AD WITH(NOLOCK)
	--						INNER JOIN App.ApplicationLicences AL WITH(NOLOCK) ON AD.ApplicationDetailID = AL.ApplicationDetailID
	--						INNER JOIN Wf.WorkflowInstances WI WITH(NOLOCK) ON AD.WorkflowInstanceID = WI.WorkflowInstanceID
	--						WHERE Wi.WorkflowInstanceStatusID <> 4 /*cancelled*/
	--						AND AD.ApplicationRequestTypeID NOT IN (11,27) /*Temporary (Special) licence, Temporary Replacement*/
	--						AND UPPER(AL.CleanOperatingLicenceNumber) IN (SELECT UPPER([App].[Fn_StripVersionNumber](OperatingLicenceOrPermitNumber,'/')) FROM App.ApplicationPrecheckLicenceDetails WITH(NOLOCK) WHERE ApplicationPrecheckDetailID = @ApplicationPrecheckDetailID)
	--						)
	--BEGIN
	--	IF @ApplicationRequestTypeID IN (3,8,14) /*3=Replacements (COV), 8=Renewal, 14=Renewal and COV*/
	--		AND EXISTS(
	--			SELECT TOP 1 1
	--			FROM App.ApplicationDetails AS AD WITH(NOLOCK)
	--				INNER JOIN App.ApplicationLicences AS AL WITH(NOLOCK) ON AD.ApplicationDetailID = AL.ApplicationDetailID
	--				INNER JOIN Wf.WorkflowInstances AS WI WITH(NOLOCK) ON AD.WorkflowInstanceID = WI.WorkflowInstanceID
	--			WHERE WorkflowInstanceStatusID = 1 -- IN Progress
	--				AND AD.IsCancelled = 0
	--				AND AD.ApplicationRequestTypeID = 2	/*2 = Add Existing Authorisations/Routes*/
	--				AND UPPER(AL.CleanOperatingLicenceNumber) IN (SELECT UPPER([App].[Fn_StripVersionNumber](OperatingLicenceOrPermitNumber,'/')) FROM App.ApplicationPrecheckLicenceDetails WITH(NOLOCK) WHERE ApplicationPrecheckDetailID = @ApplicationPrecheckDetailID)
	--		)
	--	BEGIN
	--		INSERT INTO App.ApplicationPrecheckOutcomes(ApplicationPrecheckDetailID, Title, IsSuccessful, OutcomeDetails, CreateByID, CreateDate, IsCancelled)
	--		SELECT @ApplicationPrecheckDetailID, 'Has the permit/operating licence has already been used on another application', 1, '', @UserID, GETDATE(), 0
	--	END
	--	ELSE
	--	BEGIN
	--		DECLARE @LicenseHasBeenUsedMessage VARCHAR(MAX) = 'The permit/operating licence has already been used on another application and can not be used again.'

	--		SELECT @LicenseHasBeenUsedMessage  = @LicenseHasBeenUsedMessage + ', ' + al.OperatingLicenceNumber + ''
	--			FROM App.ApplicationDetails AS AD WITH(NOLOCK)
	--				INNER JOIN App.ApplicationLicences AS AL WITH(NOLOCK) ON AD.ApplicationDetailID = AL.ApplicationDetailID
	--				INNER JOIN Wf.WorkflowInstances AS WI WITH(NOLOCK) ON AD.WorkflowInstanceID = WI.WorkflowInstanceID
	--			WHERE WorkflowInstanceStatusID = 1 -- IN Progress
	--				AND AD.IsCancelled = 0
	--				AND AD.ApplicationRequestTypeID = 2	/*2 = Add Authorisations  Routes*/
	--				AND UPPER(AL.CleanOperatingLicenceNumber) IN (SELECT UPPER([App].[Fn_StripVersionNumber](OperatingLicenceOrPermitNumber,'/')) FROM App.ApplicationPrecheckLicenceDetails WITH(NOLOCK) WHERE ApplicationPrecheckDetailID = @ApplicationPrecheckDetailID)
				
	--		SET @LicenseHasBeenUsedMessage = @LicenseHasBeenUsedMessage + ''

	--		INSERT INTO App.ApplicationPrecheckOutcomes(ApplicationPrecheckDetailID, Title, IsSuccessful, OutcomeDetails, CreateByID, CreateDate, IsCancelled)
	--		SELECT @ApplicationPrecheckDetailID, 'Has the permit/operating licence has already been used on another application', 0, @LicenseHasBeenUsedMessage, @UserID, GETDATE(), 0
	--	END	
	--END

	/********************************************************************************************
	C8 - Check if source is valid
	*********************************************************************************************/	
	DECLARE @OLAS SMALLINT = 1
	DECLARE @LTPS SMALLINT = 2
	DECLARE @LG SMALLINT = 3
	DECLARE @SourceIsValid BIT = 0

	IF @ApplicationRequestTypeID = 1 /*New Operating Licence*/
	BEGIN
		IF ISNULL(@OperatingLicenceOrPermitNumber,'') = '' /*all other new OLs is not from a source*/
		BEGIN 
			SET @SourceIsValid = 1
		END			
	END

	IF @ApplicationRequestTypeID in (36, 37) /*Add New Authorisations/Routes,Amend Authorisations/Routes*/
	BEGIN
		SET @SourceIsValid = 1		
	END

	SET @SourceIsValid = 1

	IF(@SourceIsValid = 0)
	BEGIN
		INSERT INTO App.ApplicationPrecheckOutcomes(ApplicationPrecheckDetailID, Title, IsSuccessful, OutcomeDetails, CreateByID, CreateDate, IsCancelled)
		SELECT @ApplicationPrecheckDetailID, 'Invalid application request type selected', 0, '', @UserID, GETDATE(), 0
	END

	/********************************************************************************************
	C9 - Check if the member is linked to the captured association
	*********************************************************************************************/	
	/*Check if the member is linked to the association*/		
	IF EXISTS(SELECT TOP 1 1 FROM System.Fn_GenSplitCSV(@ServiceTypeIDs, ',') WHERE VALUE = 2 /*2=Unscheduled*/) AND (@VehicleTypeID IN(2,10) /*2=MINIBUS, 10=MIDIBUS*/)
	BEGIN
		DECLARE @ValidInVerifiedMembers BIT = 0
		DECLARE @ValidInImportTable BIT = 0
		DECLARE @ValidInAllowedNewOLs BIT = 0
		DECLARE @ValidApplicationRequestTypeID BIT = 0

		SELECT @VerifiedMemberID = MemberID
		FROM Verified.Members WITH(NOLOCK) 
		WHERE IDNumberOrBusinessRegistrationNo LIKE @IdOrBusinessRegistrationNumber

		/********************************************************************************************
		C10 - Check if applicant ID number can be found in verified members and member's association matches the application's association
		*********************************************************************************************/	
		IF @VerifiedMemberID <> 0
		BEGIN
			IF NOT EXISTS(
				SELECT 1 FROM Verified.AssociationMembers WITH(NOLOCK) WHERE AssociationID = @ApplicationAssociationID AND MemberID = @VerifiedMemberID
				UNION 
				SELECT 1 FROM Verified.Licences WITH(NOLOCK) WHERE AssociationID = @ApplicationAssociationID AND MemberID = @VerifiedMemberID AND ISNULL(IsLatestIssue, 1) = 1
			)
			BEGIN
				SET @ValidInVerifiedMembers = 0
			END
			ELSE
			BEGIN
				SET @ValidInVerifiedMembers = 1
			END
		END

		/********************************************************************************************
		C12 - Check if applicant ID number can be found in Purification - Allowed new license list and association matches the application's association
		*********************************************************************************************/	
		IF @ValidInVerifiedMembers = 0 AND @ValidInImportTable = 0
		BEGIN
			IF NOT EXISTS(SELECT TOP 1 1 FROM Purification.ApplicationAllowedNewLicences WITH(NOLOCK) WHERE IdNumberOrBusinessRegistrationNumber = @IdOrBusinessRegistrationNumber AND AssociationID = @ApplicationAssociationID)
			BEGIN
				SET @ValidInAllowedNewOLs = 0
			END
			ELSE
			BEGIN
				SET @ValidInAllowedNewOLs = 1
			END
		END

		/********************************************************************************************
		C42 - Check if request type is add/amend Authorisations/Routes
		*********************************************************************************************/	
		IF @ApplicationRequestTypeID IN (36, 37) /*Add New Authorisations/Routes,Amend Authorisations/Routes*/
		BEGIN
			SET @ValidApplicationRequestTypeID = 1
		END

		--IF @ValidInVerifiedMembers = 0 AND @ValidInVerifiedMembers = 0 AND @ValidInAllowedNewOLs = 0 AND @ValidApplicationRequestTypeID = 0
		--BEGIN
		--	INSERT INTO App.ApplicationPrecheckOutcomes(ApplicationPrecheckDetailID, Title, IsSuccessful, OutcomeDetails, CreateByID, CreateDate, IsCancelled)
		--	SELECT @ApplicationPrecheckDetailID, 'The specified member can not be verified for the association.', 0, '', @UserID, GETDATE(), 0
		--END			
	END

	/********************************************************************************************
			Check Licence Records
	*********************************************************************************************/
	DECLARE @IntegrationXml XML,
		@QueryString VARCHAR(MAX),
		@DataFound BIT,
		@InlcudeActiveOnly BIT,
		@DataAssociationID BIGINT,
		@DataAssociationName VARCHAR(2000),
		@DataIDNumberOrBusinessRegistrationNo VARCHAR(20),
		@DataLicenceExpiryDate DATETIME,
		@DataLicenceStatus VARCHAR(255),
		@DataLicenceActive BIT,				
		@DataServiceTypeXML XML,
		@RoutesXML XML
					
	DECLARE @VerifiedAssociationID BIGINT,
		@RenewalGracePeriod INT

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

	SELECT @VerifiedAssociationID = AssociationID
	FROM Verified.Associations WITH(NOLOCK)
	WHERE AssociationID = @ApplicationAssociationID

	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, YearOfManufacture VARCHAR(20), IntegrationXML XML)
	/* declare variables */
	DECLARE @OlPermitNumber VARCHAR(255)
	DECLARE @OLIssueNumber VARCHAR(255)
	DECLARE @YearOfManufacture VARCHAR(20)
	
	DECLARE Application_Cursor CURSOR FAST_FORWARD READ_ONLY FOR 
	SELECT VALUE, App.Fn_GetVersionNumber(VALUE,'/')
	FROM System.Fn_GenSplitCSV(@OriginatingLicenceNumbers,',')	
	
	OPEN Application_Cursor
	
	FETCH NEXT FROM Application_Cursor INTO @OlPermitNumber, @OLIssueNumber
	
	WHILE @@FETCH_STATUS = 0
	BEGIN
		DELETE FROM @LicenceTable

	    IF ISNULL(@OlPermitNumber,'') <> ''
		BEGIN
			SET @InlcudeActiveOnly = CASE WHEN @ApplicationRequestTypeID = 34 /* Upliftment Extension */ THEN 0 ELSE 1 /*1 will ensure only active, or bypassed expired licence is returned*/ END

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

			/********************************************************************************************
			C13 - if new ol and licence can be found, flag as failed
			*********************************************************************************************/
			IF(@ApplicationRequestTypeID = 1 AND @DataFound = 1)
			BEGIN
				INSERT INTO App.ApplicationPrecheckOutcomes(ApplicationPrecheckDetailID, Title, IsSuccessful, OutcomeDetails, CreateByID, CreateDate, IsCancelled)
				SELECT @ApplicationPrecheckDetailID, 'The specified licence can be found in the source system. Start a New OL (From Source) application instead.', 0, 'Licence Number: ' + @OlPermitNumber , @UserID, GETDATE(), 0
			END

			/********************************************************************************************
			C14 - 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 TOP 1 1
					FROM App.ApplicationDetails AS ARD WITH(NOLOCK)
					INNER JOIN App.ApplicationLicences AS AEL WITH(NOLOCK) ON ARD.ApplicationDetailID = AEL.ApplicationDetailID
					INNER JOIN Wf.WorkflowInstances AS WI WITH(NOLOCK) ON ARD.WorkflowInstanceID = WI.WorkflowInstanceID
					WHERE WorkflowInstanceStatusID <> 4 --cancelled
						AND ARD.IsCancelled = 0
						AND AEL.CleanOperatingLicenceNumber = [App].[Fn_StripVersionNumber](@OlPermitNumber,'/')
					)
				BEGIN
					INSERT INTO App.ApplicationPrecheckOutcomes(ApplicationPrecheckDetailID, Title, IsSuccessful, OutcomeDetails, CreateByID, CreateDate, IsCancelled)
					SELECT @ApplicationPrecheckDetailID,'The specified licence has already been used in the system.', 0, 'Licence Number: ' + @OlPermitNumber , @UserID, GETDATE(), 0
				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
			*********************************************************************************************/
			
			/********************************************************************************************
			C17 - Check if Licence can be found in LG source
			*********************************************************************************************/
			ELSE IF(@DataFound = 0 AND @ApplicationRequestTypeID <> 1)
			BEGIN
				INSERT INTO App.ApplicationPrecheckOutcomes(ApplicationPrecheckDetailID, Title, IsSuccessful, OutcomeDetails, CreateByID, CreateDate, IsCancelled)
				SELECT @ApplicationPrecheckDetailID, 'The source licence can not be found in source NLTIS, please recheck all given information. ', 0, 'Licence Number: ' + @OlPermitNumber , @UserID, GETDATE(), 0
			END

			/********************************************************************************************
			C18 - block any applications if an issue workflow is in progress for the same licence
			*********************************************************************************************/
			IF (@DataFound = 1)
			BEGIN
				IF EXISTS(SELECT TOP 1 1 
					FROM App.LicenceIssueDetails Lid WITH(NOLOCK) INNER JOIN Verified.Licences L WITH(NOLOCK) 
					ON L.LicenceID = Lid.VerifiedLicenceID INNER JOIN Wf.WorkflowInstances Wi WITH(NOLOCK) ON Lid.WorkflowInstanceID = Wi.WorkflowInstanceID
					WHERE L.CleanOperatingLicenceNumber = App.Fn_StripVersionNumber(@OlPermitNumber,'/')
						AND Wi.WorkflowInstanceStatusID = 1 /*in progress*/
					) AND @ApplicationRequestTypeID <> 34 /* Upliftment Extension */
				BEGIN
					INSERT INTO App.ApplicationPrecheckOutcomes(ApplicationPrecheckDetailID, Title, IsSuccessful, OutcomeDetails, CreateByID, CreateDate, IsCancelled)
					SELECT @ApplicationPrecheckDetailID, '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.', 0, 'Licence Number: ' + @OlPermitNumber , @UserID, GETDATE(), 0
				END
			END

			/********************************************************************************************
			More Checks
			*********************************************************************************************/
			DECLARE @CheckFailedMessage VARCHAR(MAX)
			IF @DataFound = 1 AND @ApplicationRequestTypeID <> 1 
			BEGIN
				/********************************************************************************************
				C19 - Check if application ID number matches application source ID number
				*********************************************************************************************/				
				IF (ISNULL(@IdOrBusinessRegistrationNumber, '') <> ISNULL(@DataIDNumberOrBusinessRegistrationNo, '')) 
					-- Do Not for Transfers
					AND @ApplicationRequestTypeID NOT IN (6,7,16,17,18,23,24,26)
				BEGIN
					INSERT INTO App.ApplicationPrecheckOutcomes(ApplicationPrecheckDetailID, Title, IsSuccessful, OutcomeDetails, CreateByID, CreateDate, IsCancelled)
					SELECT @ApplicationPrecheckDetailID, 'The licence you have specified is registered under a different id number than the one you have specified (Registered ID Number: ' + ISNULL(@DataIDNumberOrBusinessRegistrationNo,'') + ').', 0, 'Licence Number: ' + @OlPermitNumber , @UserID, GETDATE(), 0
				END

				/********************************************************************************************
				C20-- Kick out any OL/Permit number that is active on a workflow
				*********************************************************************************************/				
				IF EXISTS (
					SELECT TOP 1 1
					FROM App.ApplicationDetails AS ARD WITH(NOLOCK)
					INNER JOIN App.ApplicationLicences AS AEL WITH(NOLOCK) ON ARD.ApplicationDetailID = AEL.ApplicationDetailID
					INNER JOIN Wf.WorkflowInstances AS WI WITH(NOLOCK) ON ARD.WorkflowInstanceID = WI.WorkflowInstanceID
					WHERE WorkflowInstanceStatusID = 1 -- IN Progress
						AND ARD.IsCancelled = 0
						AND AEL.CleanOperatingLicenceNumber = [App].[Fn_StripVersionNumber](@OlPermitNumber,'/')						
				)
				BEGIN
					/********************************************************************************************
					C21 --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 /*Temporary (Special) licence*/
					BEGIN
						/********************************************************************************************
						C22 -- Check for no in-progres Application Request workflow of type Temporary (Special) licence with matching licence numbers
						*********************************************************************************************/	
						IF EXISTS(SELECT TOP 1 1
							FROM App.ApplicationDetails AS ARD WITH(NOLOCK)
								INNER JOIN App.ApplicationLicences AS AEL WITH(NOLOCK) ON ARD.ApplicationDetailID = AEL.ApplicationDetailID
								INNER JOIN Wf.WorkflowInstances AS WI WITH(NOLOCK) ON ARD.WorkflowInstanceID = WI.WorkflowInstanceID
								WHERE WorkflowInstanceStatusID = 1 -- IN Progress
									AND ARD.IsCancelled = 0
									AND AEL.CleanOperatingLicenceNumber = [App].[Fn_StripVersionNumber](@OlPermitNumber,'/')						
									AND Ard.ApplicationRequestTypeID = 11 --Temporary (Special) licence
									)
						BEGIN
							SELECT @CheckFailedMessage = 'The licence you have specified is currently locked by another active special licence workflow (' + ARD.ApplicationNumber + ').'
							FROM App.Vw_ApplicationDetails AS ARD WITH(NOLOCK)
							INNER JOIN App.ApplicationLicences AS AEL WITH(NOLOCK) ON ARD.ApplicationDetailID = AEL.ApplicationDetailID
							INNER JOIN Wf.WorkflowInstances AS WI WITH(NOLOCK) ON ARD.WorkflowInstanceID = WI.WorkflowInstanceID
							WHERE WorkflowInstanceStatusID = 1 -- IN Progress
								AND AEL.CleanOperatingLicenceNumber = [App].[Fn_StripVersionNumber](@OlPermitNumber,'/')												

							INSERT INTO App.ApplicationPrecheckOutcomes(ApplicationPrecheckDetailID, Title, IsSuccessful, OutcomeDetails, CreateByID, CreateDate, IsCancelled)
							SELECT @ApplicationPrecheckDetailID, @CheckFailedMessage, 0, '', @UserID, GETDATE(), 0
						END
					END
					ELSE
					BEGIN
						/********************************************************************************************
						C23--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) /*3=Replacements (COV), 8=Renewal, 14=Renewal and COV*/
							AND EXISTS(
								SELECT TOP 1 1
								FROM App.ApplicationDetails AS ARD WITH(NOLOCK)
									INNER JOIN App.ApplicationLicences AS AEL WITH(NOLOCK) ON ARD.ApplicationDetailID = AEL.ApplicationDetailID
									INNER JOIN Wf.WorkflowInstances AS WI WITH(NOLOCK) ON ARD.WorkflowInstanceID = WI.WorkflowInstanceID
								WHERE WorkflowInstanceStatusID = 1 -- IN Progress
									AND ARD.IsCancelled = 0
									AND Ard.ApplicationRequestTypeID = 2 /*2 = Add Authorisations  Routes*/
									AND AEL.CleanOperatingLicenceNumber = [App].[Fn_StripVersionNumber](@OlPermitNumber,'/')							
							)
						BEGIN
							PRINT 'Allowing COV or renewal to be processed'
						END
						ELSE
						BEGIN
							SELECT @CheckFailedMessage = 'The licence you have specified is currently locked by an active workflow -' + WIS.StepTitle + ' (' + ApplicationNumber + ').'
							FROM App.Vw_ApplicationDetails AS ARD WITH(NOLOCK)
							INNER JOIN App.ApplicationLicences AS AEL WITH(NOLOCK) ON ARD.ApplicationDetailID = AEL.ApplicationDetailID
							INNER JOIN Wf.WorkflowInstances AS WI WITH(NOLOCK) ON ARD.WorkflowInstanceID = WI.WorkflowInstanceID
							INNER JOIN Wf.WorkflowTemplateSteps AS WIS WITH(NOLOCK) ON WI.WorkflowTemplateStepID = WIS.WorkflowTemplateStepID
							WHERE WorkflowInstanceStatusID = 1 -- IN Progress
								AND AEL.CleanOperatingLicenceNumber = [App].[Fn_StripVersionNumber](@OlPermitNumber,'/')												

							INSERT INTO App.ApplicationPrecheckOutcomes(ApplicationPrecheckDetailID, Title, IsSuccessful, OutcomeDetails, CreateByID, CreateDate, IsCancelled)
							SELECT @ApplicationPrecheckDetailID, @CheckFailedMessage, 0, '', @UserID, GETDATE(), 0		
						END						
					END
				END

				/*********************************************************************************************
				C24--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 TOP 1 1
					FROM Verified.Licences Vl WITH(NOLOCK)
						INNER JOIN App.ApplicationDetails Ard WITH(NOLOCK) ON Vl.ApplicationDetailID = Ard.ApplicationDetailID
						INNER JOIN App.ApplicationLicences El WITH(NOLOCK) ON Ard.ApplicationDetailID = El.ApplicationDetailID
					WHERE Vl.CleanOperatingLicenceNumber = [App].[Fn_StripVersionNumber](@OlPermitNumber,'/')
						AND Ard.SourceApplicationID IN (1,2) /*1 = OLAS, 2 = LTPS*/
						AND Vl.LicenceStatusID <> 7 --7=cancelled
						AND Ard.ApplicationRequestTypeID NOT IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 60, 61, 62, 63)
				)
				BEGIN
					SELECT TOP 1 @ExistingLegitimateNumber = Vl.OperatingLicenceNumber
					FROM Verified.Licences Vl WITH(NOLOCK)
						INNER JOIN App.ApplicationDetails Ard WITH(NOLOCK) ON Vl.ApplicationDetailID = Ard.ApplicationDetailID
						INNER JOIN App.ApplicationLicences El WITH(NOLOCK) ON Ard.ApplicationDetailID = El.ApplicationDetailID
					WHERE Vl.CleanOperatingLicenceNumber = [App].[Fn_StripVersionNumber](@OlPermitNumber,'/')
						AND Ard.SourceApplicationID IN (1,2)  /*1 = OLAS, 2 = LTPS*/
						AND Vl.LicenceStatusID <> 7 --7=cancelled
						AND Ard.ApplicationRequestTypeID NOT IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 60, 61, 62, 63)

					SET @CheckFailedMessage = 'The licence you have specified has already been migrated (' + ISNULL(@ExistingLegitimateNumber,'') + ').'
					INSERT INTO App.ApplicationPrecheckOutcomes(ApplicationPrecheckDetailID, Title, IsSuccessful, OutcomeDetails, CreateByID, CreateDate, IsCancelled)
					SELECT @ApplicationPrecheckDetailID, @CheckFailedMessage, 0, 'Licence Number: ' + @OlPermitNumber , @UserID, GETDATE(), 0
				END

				/*********************************************************************************************
				C38--Check if the OLAS OL or LTPS Permit number that has been for Temporary (Special) licence
				*********************************************************************************************/				
				IF EXISTS(
					SELECT TOP 1 1
					FROM App.ApplicationDetails Ard WITH(NOLOCK)						
						INNER JOIN App.ApplicationLicences El WITH(NOLOCK) ON Ard.ApplicationDetailID = El.ApplicationDetailID
						INNER JOIN Wf.WorkflowInstances Wi WITH(NOLOCK) ON Wi.WorkflowInstanceID = Ard.WorkflowInstanceID
					WHERE El.CleanOperatingLicenceNumber = [App].[Fn_StripVersionNumber](@OlPermitNumber,'/')
						AND Ard.SourceApplicationID IN (1,2) /*1 = OLAS, 2 = LTPS*/
						AND Wi.WorkflowInstanceStatusID <> 4 --74cancelled
						AND Ard.ApplicationRequestTypeID = 11 --Temporary (Special) licence
				) AND @ApplicationRequestTypeID = 11 --Temporary (Special) licence
				BEGIN
					SELECT TOP 1 @ExistingLegitimateNumber = Ard.ApplicationNumber
					FROM App.ApplicationDetails Ard WITH(NOLOCK)						
						INNER JOIN App.ApplicationLicences El WITH(NOLOCK) ON Ard.ApplicationDetailID = El.ApplicationDetailID
						INNER JOIN Wf.WorkflowInstances Wi WITH(NOLOCK) ON Wi.WorkflowInstanceID = Ard.WorkflowInstanceID
					WHERE El.CleanOperatingLicenceNumber = [App].[Fn_StripVersionNumber](@OlPermitNumber,'/')
						AND Ard.SourceApplicationID IN (1,2) /*1 = OLAS, 2 = LTPS*/
						AND Wi.WorkflowInstanceStatusID <> 4 --74cancelled
						AND Ard.ApplicationRequestTypeID = 11 --Temporary (Special) licence
			
					SET @CheckFailedMessage = 'The licence has already been used to create a Temporary (Special) licence (' + ISNULL(@ExistingLegitimateNumber,'') + ').'
					INSERT INTO App.ApplicationPrecheckOutcomes(ApplicationPrecheckDetailID, Title, IsSuccessful, OutcomeDetails, CreateByID, CreateDate, IsCancelled)
					SELECT @ApplicationPrecheckDetailID, @CheckFailedMessage, 0, 'Licence Number: ' + @OlPermitNumber , @UserID, GETDATE(), 0
				END	
			END

			/********************************************************************************************
			More Checks
			*********************************************************************************************/
			IF(@DataFound = 1 AND @ApplicationRequestTypeID NOT IN (1 /*New Operating Licence*/,9 /*New Operating Licence (From Source)*/))
			BEGIN
					/********************************************************************************************
					C26 - Check Expirey Date
					*********************************************************************************************/		
					IF (DATEDIFF(DAY, GETDATE(), @DataLicenceExpiryDate) < @RenewalGracePeriod) 
						AND @ApplicationRequestTypeID NOT IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 60, 61, 62, 63)
					BEGIN						
						INSERT INTO App.ApplicationPrecheckOutcomes(ApplicationPrecheckDetailID, Title, IsSuccessful, OutcomeDetails, CreateByID, CreateDate, IsCancelled)
						SELECT @ApplicationPrecheckDetailID, 'This licence is expiring soon. Start the related linked applicaton type instead.', 0, 'Licence Number: ' + @OlPermitNumber , @UserID, GETDATE(), 0
					END

					/********************************************************************************************
					C27 - Check if the licence expired today or over the weekend, and add it to the Purification.ApplicationAllowedExpiredLicences table so it can still be processed.
					*********************************************************************************************/
					DECLARE @ExpiredTodayOrOverTheWeekend BIT = 0
					IF NOT EXISTS(SELECT TOP 1 1 FROM @LicenceTable WHERE IsInBypassedExpiryList=1)
					BEGIN
						IF EXISTS(SELECT TOP 1 1 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 TOP 1 1 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 TOP 1 1 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 Purification.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

					/********************************************************************************************
					C28 -- 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 TOP 1 1 FROM @LicenceTable WHERE IsInBypassedExpiryList = 1) AND @ExpiredTodayOrOverTheWeekend = 0 AND @ApplicationRequestTypeID NOT IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 60, 61, 62, 63)

					BEGIN
						IF (App.Fn_IsLicenceActive(@DataLicenceStatus,@SourceApplicationID) = 0) OR @DataLicenceActive = 0
						BEGIN						
							SET @CheckFailedMessage = '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.'
							INSERT INTO App.ApplicationPrecheckOutcomes(ApplicationPrecheckDetailID, Title, IsSuccessful, OutcomeDetails, CreateByID, CreateDate, IsCancelled)
							SELECT @ApplicationPrecheckDetailID, @CheckFailedMessage, 0, 'Licence Number: ' + @OlPermitNumber , @UserID, GETDATE(), 0
						END
					END
				END
			
			/********************************************************************************************
			C29 -- 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(@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 TOP 1 1 FROM @LicenceTable WHERE IsInBypassedExpiryList=1)
				BEGIN
					INSERT INTO Purification.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

			/********************************************************************************************
			C30 - OLAS specific validation	
			********************************************************************************************/
			IF(@DataFound = 1 AND @ApplicationRequestTypeID <> 1/*New Application*/ AND @ApplicationAssociationID IS NOT NULL)
			BEGIN
				-- kick out an application where the assocations are not the same
				IF (ISNULL(@VerifiedAssociationID, 0) <> ISNULL(@DataAssociationID, 0))
				BEGIN
					DECLARE @OlasAssociationName VARCHAR(100)

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

					SET @CheckFailedMessage = 'The licence you have specified is registered under a different association than the one you have specified (NLTIS Association: ' + ISNULL(@OlasAssociationName,'') + ').'

					INSERT INTO App.ApplicationPrecheckOutcomes(ApplicationPrecheckDetailID, Title, IsSuccessful, OutcomeDetails, CreateByID, CreateDate, IsCancelled)
					SELECT @ApplicationPrecheckDetailID, @CheckFailedMessage, 0, 'Licence Number: ' + @OlPermitNumber , @UserID, GETDATE(), 0
				END												
			END

			/********************************************************************************************
			C31 - Legitimate specific validaton
			********************************************************************************************/
			IF(@DataFound = 1 AND @ApplicationRequestTypeID <> 1 /*New OL*/) AND ISNULL(@ServiceTypeIDs,'') <> ''
			BEGIN
				--kick out any application where the service type is not the same
				IF @DataServiceTypeXML IS NOT NULL
				BEGIN			
						/********************************************************************************************
						C31 - Legitimate specific validaton
						********************************************************************************************/
					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') IN (SELECT VALUE FROM System.Fn_GenSplitCSV(@ServiceTypeIDs,','))
						)
					BEGIN
						SET @CheckFailedMessage = 'The specified service type does not match the existing licence.'
						INSERT INTO App.ApplicationPrecheckOutcomes(ApplicationPrecheckDetailID, Title, IsSuccessful, OutcomeDetails, CreateByID, CreateDate, IsCancelled)
						SELECT @ApplicationPrecheckDetailID, @CheckFailedMessage, 0, 'Licence Number: ' + @OlPermitNumber , @UserID, GETDATE(), 0
					END
				END

				
				/********************************************************************************************
				C32 -- block an application where the assocations are not the same
				********************************************************************************************/
				IF (ISNULL(@DataAssociationID, 0) <> 0 AND (ISNULL(@VerifiedAssociationID, 0) <> ISNULL(@DataAssociationID, 0)) AND @ApplicationAssociationID IS NOT NULL)
				BEGIN
					DECLARE @VerifiedAssociationName VARCHAR(100)

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

					SET @CheckFailedMessage = 'The licence you have specified is registered under a different association than the one you have specified (Verified Association: ' + ISNULL(@VerifiedAssociationName,'') + ').'
					INSERT INTO App.ApplicationPrecheckOutcomes(ApplicationPrecheckDetailID, Title, IsSuccessful, OutcomeDetails, CreateByID, CreateDate, IsCancelled)
					SELECT @ApplicationPrecheckDetailID, @CheckFailedMessage, 0, 'Licence Number: ' + @OlPermitNumber , @UserID, GETDATE(), 0
				END																																
			END

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

					INSERT INTO @Routes(NationalRouteNumberInXml, BoardRouteNumberInXml, RouteName, RouteDescription, ServiceTypeID)
					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,
						t.p.value('(servicetypeid)[1]','VARCHAR(MAX)') AS ServiceTypeID
					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 WITH(NOLOCK) WHERE (NationalRouteNumber LIKE R.NationalRouteNumberInXml OR BoardRouteNumber LIKE R.BoardRouteNumberInXml) AND RouteStatusID = 1 /*Active*/)					
					FROM @Routes R

					/********************************************************************************************
					C33 - Validate Route Details
					********************************************************************************************/
					IF EXISTS(SELECT TOP 1 1 FROM @Routes WHERE VerifiedRouteID IS NULL AND ServiceTypeID IN (1, 2, 5, 6, 11 /*Scheduled service, Unscheduled service, Staff service, Staff service, Long distance service*/))
					BEGIN
						SELECT @RouteNumbersWithProblems = COALESCE(@RouteNumbersWithProblems + ',', '  ') + CAST(NationalRouteNumberInXml AS VARCHAR(20))  
						FROM @Routes 
						WHERE VerifiedRouteID IS NULL

						SET @CheckFailedMessage = 'The following routes does not exists currently and will have to be added before the application can be started: ' + ISNULL(@RouteNumbersWithProblems,'')
					
						INSERT INTO App.ApplicationPrecheckOutcomes(ApplicationPrecheckDetailID, Title, IsSuccessful, OutcomeDetails, CreateByID, CreateDate, IsCancelled)
						SELECT @ApplicationPrecheckDetailID, @CheckFailedMessage, 0, 'Licence Number: ' + @OlPermitNumber , @UserID, GETDATE(), 0
					END

					/********************************************************************************************
					C34 - Check all routes linked to License are also linked to the Association captured
					********************************************************************************************/
					IF ISNULL(@ApplicationAssociationID, -1) <> -1
					BEGIN
						IF EXISTS(
							SELECT TOP 1 1 
							FROM @Routes R
								LEFT JOIN Verified.AssociationRoutes Ar WITH(NOLOCK) ON Ar.AssociationID = @ApplicationAssociationID 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 WITH(NOLOCK) ON Ar.AssociationID = @ApplicationAssociationID AND R.VerifiedRouteID = Ar.RouteID
							WHERE Ar.RouteID IS NULL

							SET @CheckFailedMessage = 'The following routes exists but are not linked to the association in question: ' + ISNULL(@RouteNumbersWithProblems,'')
					
							INSERT INTO App.ApplicationPrecheckOutcomes(ApplicationPrecheckDetailID, Title, IsSuccessful, OutcomeDetails, CreateByID, CreateDate, IsCancelled)
							SELECT @ApplicationPrecheckDetailID, @CheckFailedMessage, 0, 'Licence Number: ' + @OlPermitNumber , @UserID, GETDATE(), 0
						END
					END
				END
			END
					
			/********************************************************************************************
			C35 - Normal and Deceased Transferes must be within the  same Association
			********************************************************************************************/
			IF EXISTS(SELECT TOP 1 1 FROM Md.ApplicationRequestTypes WITH(NOLOCK) WHERE ApplicationRequestTypeID = @ApplicationRequestTypeID AND (IncludeDeceasedTransferType = 1 OR IncludeNormalTransferType = 1))
			BEGIN
				-- kick out an application where the assocations are not the same
				IF (ISNULL(@ApplicationAssociationID, 0) <> ISNULL(@DataAssociationID, 0))
				BEGIN
					DECLARE @AssociationName VARCHAR(100)

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

					SET @CheckFailedMessage = 'The licence you have specified is registered under a different association than the one you have specified for transafer (Association: ' + ISNULL(@AssociationName,'') + ').'

					INSERT INTO App.ApplicationPrecheckOutcomes(ApplicationPrecheckDetailID, Title, IsSuccessful, OutcomeDetails, CreateByID, CreateDate, IsCancelled)
					SELECT @ApplicationPrecheckDetailID, @CheckFailedMessage, 0, 'Licence Number: ' + @OlPermitNumber , @UserID, GETDATE(), 0
				END
			END

			/********************************************************************************************
			C36 - Vehicle Year of Manaufacture: Cannot be greater than current year
			********************************************************************************************/
			IF (@DataFound = 1 AND ISNULL(@YearOfManufacture,'') <> '')
			BEGIN
				IF (CAST(YEAR(GETDATE()) AS BIGINT) < CAST(@YearOfManufacture AS BIGINT))
				BEGIN
					INSERT INTO App.ApplicationPrecheckOutcomes(ApplicationPrecheckDetailID, Title, IsSuccessful, OutcomeDetails, CreateByID, CreateDate, IsCancelled)
					SELECT @ApplicationPrecheckDetailID, 'Vehicle Year of manaufacture is greater than current year', 0, 'Licence Number: ' + @OlPermitNumber , @UserID, GETDATE(), 0
				END
			END

			/********************************************************************************************
			C37 - Check if an their are any in progress request with matching source licence ID, exclude Add Existing Authorisations/Routes
			********************************************************************************************/
			IF (@DataFound = 1 AND @ApplicationRequestTypeID <> 2 /*Add Existing Authorisations/Routes*/)
			AND EXISTS
			(
	
				SELECT TOP 1 1
				FROM App.ApplicationLicences AL WITH(NOLOCK)
				INNER JOIN App.ApplicationDetails AD WITH(NOLOCK) ON AD.ApplicationDetailID = AL.ApplicationDetailID
				INNER JOIN Wf.WorkflowInstances Wi WITH(NOLOCK) ON Wi.WorkflowInstanceID = AD.WorkflowInstanceID
				WHERE AL.CleanOperatingLicenceNumber = App.Fn_StripVersionNumber(@OlPermitNumber,'/')
				AND AL.ApplicationLicenceRequestTypeID <> 2 /*Add Existing Authorisations/Routes*/
				AND Wi.WorkflowInstanceStatusID = 1 /*Inprogress*/
			)
			BEGIN
				IF (CAST(YEAR(GETDATE()) AS BIGINT) < CAST(@YearOfManufacture AS BIGINT))
				BEGIN
					INSERT INTO App.ApplicationPrecheckOutcomes(ApplicationPrecheckDetailID, Title, IsSuccessful, OutcomeDetails, CreateByID, CreateDate, IsCancelled)
					SELECT @ApplicationPrecheckDetailID, 'Their is a in-progress request with a matching licence number', 0, 'Licence Number: ' + @OlPermitNumber , @UserID, GETDATE(), 0
				END
			END
		END           		
	
	    FETCH NEXT FROM Application_Cursor INTO @OlPermitNumber, @OLIssueNumber
	END
	
	CLOSE Application_Cursor
	DEALLOCATE Application_Cursor

	-- This check is not valid anymore
	/********************************************************************************************
	C38 - Check if source is valid for Personal Details Update request
	*********************************************************************************************/	
	--IF @ApplicationRequestTypeID = 29 /*Personal Details Update*/
	--BEGIN
	--	INSERT INTO App.ApplicationPrecheckOutcomes(ApplicationPrecheckDetailID, Title, IsSuccessful, OutcomeDetails, CreateByID, CreateDate, IsCancelled)
	--	SELECT @ApplicationPrecheckDetailID, 'Personal Details Update Request must be linked to license', 0, '', @UserID, GETDATE(), 0		
	--END		

	/********************************************************************************************
	Add dummy precheck if none found
	*********************************************************************************************/	
	IF NOT EXISTS(SELECT TOP 1 1 FROM App.ApplicationPrecheckOutcomes WITH(NOLOCK) WHERE ApplicationPrecheckDetailID = @ApplicationPrecheckDetailID)
	BEGIN
		INSERT INTO App.ApplicationPrecheckOutcomes(ApplicationPrecheckDetailID, Title, IsSuccessful, OutcomeDetails, CreateByID, CreateDate, IsCancelled)
		SELECT @ApplicationPrecheckDetailID, 'Temporary pre-check', 1, '', @UserID, GETDATE(), 0
	END

	/********************************************************************************************
	Return outcomes to interface
	*********************************************************************************************/	
	SELECT ApplicationPrecheckOutcomeID
		, ApplicationPrecheckDetailID
		, Title
		, IsSuccessful
		, OutcomeDetails
		, CreateByID
		, CreateDate
		, IsCancelled
	FROM App.ApplicationPrecheckOutcomes WITH(NOLOCK)
	WHERE ApplicationPrecheckDetailID = @ApplicationPrecheckDetailID
END