CREATE OR ALTER PROCEDURE [App].[Pr_ApplicationMoveToVerified]
(
	@ApplicationDetailID BIGINT,	
	@ApplicationRequestTypeID SMALLINT,
	@SourceApplicationID SMALLINT = 3,
	@ApplicationLicenceID BIGINT,
	@UserID BIGINT
)
-- EXEC App.Pr_ApplicationMoveToVerified 1583438, 1, 3, 1453314, 1001146    
AS
BEGIN
	/********************************************************************************************
	Get user details and perform security checks
	*********************************************************************************************/	
	DECLARE @EntityIDToCheck INT = 1000 /*Application*/
	DECLARE @ItemIDToCheck BIGINT = @ApplicationDetailID
	DECLARE @Action VARCHAR(255) = 'Move to verified'
	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	
	

	/********************************************************************************************
	Get the apps division and province
	*********************************************************************************************/	
	SELECT @DivisionTypeID = OfficeLocationDivisionTypeID, @ProvinceID = ApplicationProvinceID
	FROM App.ApplicationDetails
	WHERE ApplicationDetailID = @ApplicationDetailID

	/********************************************************************************************
	Read the linked application details 
	*********************************************************************************************/	
	DECLARE @IncludeRenewalType BIT
	DECLARE @IncludeCovType BIT
	DECLARE @IncludeIncreaseInCapacityType BIT
	DECLARE @IncludeDeceasedTransferType BIT
	DECLARE @IncludeNormalTransferType BIT
	DECLARE @IncludeNewOperatingLicenceType BIT

	SELECT 
		@IncludeRenewalType = IncludeRenewalType,
		@IncludeCovType = IncludeCovType, 
		@IncludeIncreaseInCapacityType = IncludeIncreaseInCapacityType, 
		@IncludeDeceasedTransferType = IncludeDeceasedTransferType, 
		@IncludeNormalTransferType = IncludeNormalTransferType, 
		@IncludeNewOperatingLicenceType = IncludeNewOperatingLicenceType
	FROM Md.ApplicationRequestTypes
	WHERE ApplicationRequestTypeID = @ApplicationRequestTypeID

	/********************************************************************************************
	Move to verified
	*********************************************************************************************/	
	----IF NOT EXISTS(SELECT * FROM App.ApplicationRouteDetails WHERE ApplicationDetailID = @ApplicationDetailID AND RouteChangeTypeID <> 4 /*Remove route from licence*/)
	----BEGIN
	----	RAISERROR('No routes linked to this application.  The application can not be moved to issuing', 16, 1)
	----	RETURN
	----END
	
	--VEHICLE------------------------------------------------------------------------
	DECLARE @VehicleID BIGINT
	DECLARE @RegistrationNumber VARCHAR(50)
	DECLARE @EngineNumber VARCHAR(50) 
	DECLARE @VIN VARCHAR(50)
	DECLARE @SeatedCapacity INT
	DECLARE @StandingCapacity INT
	DECLARE @GrossMass BIGINT
	DECLARE @VehicleMakeID SMALLINT		
	DECLARE @VehicleModelID SMALLINT	
	DECLARE @VehicleTypeID SMALLINT	
	DECLARE @CarryingCapacityID SMALLINT
	DECLARE @YearOfManufacture VARCHAR(4)
	DECLARE @Tare BIGINT
	DECLARE @CurrentOdometerReading BIGINT
	DECLARE @RoadworthyCertificateDocID BIGINT
	DECLARE @ProfessionalDriversPermitDocID BIGINT
	DECLARE @RankPermitDocID BIGINT
	DECLARE @CertificateOfRegistrationDocID BIGINT
	DECLARE @IsStillToBeAcquired BIT
	DECLARE @AuditDescription VARCHAR(100) = ''
	DECLARE @ForceUpdateIfExists BIT = 1
	DECLARE @CertifiedServiceRecordsDocID BIGINT
	DECLARE @CertifiedPublicPassangerLiabilityDocID BIGINT

	SELECT @EngineNumber = EngineNumber,
		@RegistrationNumber = VehicleRegistrationNumber,
		@VIN = Vin,
		@SeatedCapacity = SeatedCapacity,
		@StandingCapacity = StandingCapacity,
		@GrossMass = GrossMass, 
		@VehicleMakeID = VehicleMakeID,
		@VehicleModelID = VehicleModelID,
		@VehicleTypeID = VehicleTypeID,
		@IsStillToBeAcquired = IsStillToBeAcquired,
		@CarryingCapacityID = CarryingCapacityID,
		@YearOfManufacture = YearOfManufacture,
		@Tare = Tare,
		@CurrentOdometerReading = CurrentOdometerReading,
		@RoadworthyCertificateDocID = RoadworthyCertificateDocID,
		@ProfessionalDriversPermitDocID = ProfessionalDriversPermitDocID,
		@RankPermitDocID = RankPermitDocID,
		@CertificateOfRegistrationDocID = CertificateOfRegistrationDocID,
		@CertifiedServiceRecordsDocID = CertifiedServiceRecordsDocID,
		@CertifiedPublicPassangerLiabilityDocID = CertifiedPublicPassangerLiabilityDocID
	FROM App.ApplicationLicenceVehicleDetails
	WHERE ApplicationLicenceID = @ApplicationLicenceID 

	IF @IsStillToBeAcquired = 0 AND ISNULL(@VIN,'') <> '' --check for blank VIN to cater for corrupt transfer data from OLAS (in certain instances)
	BEGIN				
		--check if the vehicle exists and if it is linked to a different licence i.e. not the originating one
		--set to null if it is so that a new vehicle can be specified at upliftment
		DECLARE @ExistingVehicleID BIGINT
		DECLARE @OriginatingLicence VARCHAR(20)
		DECLARE @LinkedLicenceCount SMALLINT = 0

		SELECT @ExistingVehicleID = VehicleID
		FROM Verified.Vehicles
		WHERE Vin LIKE	@VIN
		
		SET @ExistingVehicleID = ISNULL(@ExistingVehicleID,-1)

		IF @ExistingVehicleID <> -1
		BEGIN			
			IF @ApplicationRequestTypeID <> 1 --new application
			BEGIN
				SELECT @OriginatingLicence = App.Fn_StripVersionNumber(OperatingLicenceNumber, '/')
				FROM App.ApplicationLicences
				WHERE ApplicationLicenceID = @ApplicationLicenceID

				SELECT @LinkedLicenceCount = COUNT(1)
				FROM Verified.Licences
				WHERE VehicleID = @ExistingVehicleID
					AND LicenceStatusID IN (1 /*Awaiting Issuing*/,2 /*Issued*/,3 /*Expiring Within 60 Days*/,4 /*Expired*/)
					AND OperatingLicenceNumber NOT LIKE 'LT%'
					AND  App.Fn_StripVersionNumber(OperatingLicenceNumber, '/') <> @OriginatingLicence					
			END
			ELSE
			BEGIN
				SELECT @LinkedLicenceCount = COUNT(1)
				FROM Verified.Licences
				WHERE VehicleID = @ExistingVehicleID
					AND LicenceStatusID IN (1 /*Awaiting Issuing*/,2 /*Issued*/,3 /*Expiring Within 60 Days*/,4 /*Expired*/)		
			END
		END

		SET @LinkedLicenceCount = ISNULL(@LinkedLicenceCount,0)

		IF @LinkedLicenceCount = 0
		BEGIN			
			EXEC @VehicleID = [Verified].[Pr_VehicleUpsert] 
				@ExistingVehicleID,
				@RegistrationNumber,
				@EngineNumber,
				@VIN,
				@SeatedCapacity,
				@StandingCapacity,
				@GrossMass,
				@VehicleMakeID,
				@VehicleModelID,
				@VehicleTypeID,
				@CarryingCapacityID,
				@YearOfManufacture,
				@Tare,
				@CurrentOdometerReading,
				@RoadworthyCertificateDocID,
				@ProfessionalDriversPermitDocID,
				@RankPermitDocID,
				@CertificateOfRegistrationDocID,
				@CertifiedServiceRecordsDocID,
				@CertifiedPublicPassangerLiabilityDocID,
				NULL,
				NULL,
				@UserID,
				@AuditDescription,
				@ForceUpdateIfExists
		END
		ELSE
		BEGIN
			SET @VehicleID = NULL
		END
	END
	ELSE
	BEGIN
		SET @VehicleID = NULL
	END

	--MEMBER------------------------------------------------------------------------
	DECLARE @MemberID BIGINT 
	DECLARE @IdentityDocumentDocID BIGINT 
	DECLARE @TaxClearanceCertificateDocID BIGINT 
	DECLARE @SurnameOrCompanyName VARCHAR(255)
	DECLARE @FirstNames VARCHAR(255)
	DECLARE @IDTypeID SMALLINT
	DECLARE @IDTypeDescription VARCHAR(50) 
	DECLARE @IDNumberOrBusinessRegistrationNo VARCHAR(20) 
	DECLARE @PostalAddress VARCHAR(255)
	DECLARE @PostalAddressCode VARCHAR(4) 
	DECLARE @PhysicalAddress VARCHAR(255) 
	DECLARE @PhysicalAddressCode VARCHAR(4) 
	DECLARE @TelPhone VARCHAR(14)
	DECLARE @TelCell VARCHAR(14) 
	DECLARE @TelFax VARCHAR(14) 
	DECLARE @Email VARCHAR(150) 
	DECLARE @SARSIncomeTaxNumber VARCHAR(20) 
	DECLARE @TradeName VARCHAR(255)
	DECLARE @AssociationID BIGINT
	DECLARE @BusinessTypeID BIGINT

	SELECT 
		@MemberID = ISNULL(VerifiedMemberID, -1),
		@IdentityDocumentDocID = NULL,
		@TaxClearanceCertificateDocID = NULL,
		@SurnameOrCompanyName = SurnameOrCompanyName,
		@FirstNames = FirstNames,
		@IDTypeID = IDTypeID,
		@IDTypeDescription = IDTypeDescription,
		@IDNumberOrBusinessRegistrationNo = IDNumberOrBusinessRegistrationNo, 
		@PostalAddress = PostalAddress,
		@PostalAddressCode = PostalAddressCode,
		@PhysicalAddress= PhysicalAddress,
		@PhysicalAddressCode = PhysicalAddressCode,
		@TelPhone = TelPhone,
		@TelCell = TelCell, 
		@TelFax = TelFax,
		@Email = Email,
		@SARSIncomeTaxNumber = SARSIncomeTaxNumber,
		@TradeName = TradeName,
		@AssociationID = VerifiedAssociationID,
		@BusinessTypeID = BusinessTypeID									
	FROM App.ApplicationApplicantDetails Aad
		INNER JOIN App.ApplicationDetails Ad ON Ad.ApplicationDetailID = Aad.ApplicationDetailID
	WHERE Aad.ApplicationDetailID = @ApplicationDetailID 

	EXEC @MemberID = [Verified].[Pr_MemberUpsert]
		@MemberID,
		@IdentityDocumentDocID,
		@TaxClearanceCertificateDocID,
		@SurnameOrCompanyName,
		@FirstNames,
		@IDTypeID,
		@IDTypeDescription,
		@IDNumberOrBusinessRegistrationNo,
		@PostalAddress,
		@PostalAddressCode,
		@PhysicalAddress,
		@PhysicalAddressCode,
		@TelPhone,
		@TelCell,
		@TelFax,
		@Email,
		@SARSIncomeTaxNumber,
		@TradeName,
		NULL,
		NULL,
		@BusinessTypeID,
		@UserID,
		@AuditDescription,
		@ForceUpdateIfExists

	--LICENCE------------------------------------------------------------------------
	DECLARE @LicenceID BIGINT = -1
	DECLARE @ModeID SMALLINT
	DECLARE @NameOnLicence VARCHAR(100)
	DECLARE @Today DATETIME = GETDATE()
	DECLARE @IssueDate DATETIME
	DECLARE @ExpiryDate DATETIME
	DECLARE @IssueNumber BIGINT
	DECLARE @ExistingLicenceNumber VARCHAR(20)
	DECLARE @IsRenewal BIT						
	DECLARE @IsLtpsConversion BIT
	DECLARE @LicenceVehicleTypeID SMALLINT
	DECLARE @LicenceCarryingCapacityID SMALLINT
	DECLARE @LicencingBoardID SMALLINT
	DECLARE @ApplicationProvinceID SMALLINT
	DECLARE @AccreditationID BIGINT
	DECLARE @WorkflowInstanceID BIGINT
						
	SELECT 
		@LicenceVehicleTypeID  = VehicleTypeID,
		@LicenceCarryingCapacityID = NULL,		
		@LicencingBoardID = Ols.LicencingBoardID, 
		@ApplicationProvinceID = Ad.ApplicationProvinceID 
	FROM App.ApplicationLicences Al
		INNER JOIN App.ApplicationDetails Ad ON Al.ApplicationDetailID = Ad.ApplicationDetailID
		INNER JOIN Md.OfficeLocationSetup Ols ON Ad.OfficeLocationID = Ols.OfficeLocationID
	WHERE ApplicationLicenceID = @ApplicationLicenceID

	SELECT @IsRenewal = IsRenewal, @IsLtpsConversion = IsLtpsConversion, @AccreditationID = AccreditationID, @WorkflowInstanceID = WorkflowInstanceID
	FROM App.ApplicationDetails
	WHERE ApplicationDetailID = @ApplicationDetailID
	
	SELECT @NameOnLicence = FullName
	FROM Verified.Members
	WHERE MemberID = @MemberID

	--Get new issue number	
	IF @ApplicationRequestTypeID <> 11 --Temporary licence
	BEGIN
		SELECT @ExistingLicenceNumber = ISNULL(OperatingLicenceNumber,'')
		FROM App.ApplicationLicences
		WHERE ApplicationLicenceID = @ApplicationLicenceID

		IF ISNULL(@ExistingLicenceNumber,'') <> ''
		BEGIN
			SELECT @IssueNumber = MAX(L.IssueNumber) + 1
			FROM Verified.Licences L
			WHERE App.Fn_StripVersionNumber(OperatingLicenceNumber,'/') LIKE App.Fn_StripVersionNumber(@ExistingLicenceNumber,'/')

			SET @IssueNumber = ISNULL(@IssueNumber, 1)
		END
		ELSE
		BEGIN
			SET @IssueNumber = 1
		END
	END
	ELSE
	BEGIN
		DECLARE @LicencePrefix VARCHAR(20) = 'TODO' --LGNPTR' --set to LGTST on test site
		DECLARE @TempLicencePrefix VARCHAR(20) = 'TODO' --LGNPTR' --set to LGTST on test site

		IF @DivisionTypeID = 2 /*NPTR*/
		BEGIN
			SET @LicencePrefix = 'LNPTR'
			SET @TempLicencePrefix = 'LNPTRT'
		END
		ELSE IF @DivisionTypeID = 1 /*PRE*/ AND @ProvinceID = 1 /*GP*/
		BEGIN
			SET @LicencePrefix = 'LGPPRE'
			SET @TempLicencePrefix = 'LGPPRET'
		END
		ELSE IF @DivisionTypeID = 1 /*PRE*/ AND @ProvinceID = 2 /*kzn*/
		BEGIN
			SET @LicencePrefix = 'LGKZN'
			SET @TempLicencePrefix = 'LGKZNT'
		END
		ELSE IF @DivisionTypeID = 1 /*PRE*/ AND @ProvinceID = 3 /*EC*/
		BEGIN
			SET @LicencePrefix = 'LECPRE'
			SET @TempLicencePrefix = 'LECPRET'
		END
		ELSE IF @DivisionTypeID = 1 /*PRE*/ AND @ProvinceID = 4 /*W-Cape*/
		BEGIN
			SET @LicencePrefix = 'LWCPRE'
			SET @TempLicencePrefix = 'LWCPRET'
		END
		ELSE IF @DivisionTypeID = 1 /*PRE*/ AND @ProvinceID = 5 /*Limpopo*/
		BEGIN
			SET @LicencePrefix = 'LLPPRE'
			SET @TempLicencePrefix = 'LLPPRET'
		END
		ELSE IF @DivisionTypeID = 1 /*PRE*/ AND @ProvinceID = 6 /*NorthWest*/
		BEGIN
			SET @LicencePrefix = 'LNWPRE'
			SET @TempLicencePrefix = 'LNWPRET'
		END
		ELSE IF @DivisionTypeID = 1 /*PRE*/ AND @ProvinceID = 7 /*FreeState*/
		BEGIN
			SET @LicencePrefix = 'LFSOLB'
			SET @TempLicencePrefix = 'LFSOLBT'
		END
		ELSE IF @DivisionTypeID = 1 /*PRE*/ AND @ProvinceID = 8 /*Mpumalanga*/
		BEGIN
			SET @LicencePrefix = 'LMRPRE'
			SET @TempLicencePrefix = 'LMRPRET'
		END
		ELSE IF @DivisionTypeID = 1 /*PRE*/ AND @ProvinceID = 9 /*N-Cape*/
		BEGIN
			SET @LicencePrefix = 'LNCPRE'
			SET @TempLicencePrefix = 'LNCPRET'
		END

		SELECT @ExistingLicenceNumber = ISNULL(OperatingLicenceNumber,'')
		FROM App.ApplicationLicences
		WHERE ApplicationDetailID = @ApplicationDetailID
						
		IF ISNULL(@ExistingLicenceNumber,'') <> ''
		BEGIN
			SET @ExistingLicenceNumber = @TempLicencePrefix + REPLACE(TRANSLATE(@ExistingLicenceNumber, 'abcdefghijklmnopqrstuvwxyz+()- ,#+', '@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@'), '@', '') 

			SELECT @IssueNumber = MAX(L.IssueNumber) + 1
			FROM Verified.Licences L
			WHERE App.Fn_StripVersionNumber(OperatingLicenceNumber,'/') LIKE App.Fn_StripVersionNumber(@ExistingLicenceNumber,'/')

			SET @IssueNumber = ISNULL(@IssueNumber, 1)
		END
		ELSE
		BEGIN
			SET @IssueNumber = 1
		END
	END

	--Issue date
	IF ISNULL(@AccreditationID,-1) <> -1
	BEGIN
		SELECT @IssueDate = DateOfIssue
		FROM Verified.Accreditations
		WHERE AccreditationID = @AccreditationID
	END
	ELSE
	BEGIN
		IF @ApplicationRequestTypeID <> 11 --temporary
		BEGIN
			SET @IssueDate = @Today
		END
		ELSE
		BEGIN
			SELECT @IssueDate = Ael.SpecialLicenceDateFrom
			FROM App.ApplicationLicences Ael
			WHERE ApplicationDetailID = @ApplicationDetailID		
		END
	END
	

	--Expiry date
	IF ISNULL(@AccreditationID,-1) <> -1
	BEGIN
		SELECT @ExpiryDate = DateOfExpiry
		FROM Verified.Accreditations
		WHERE AccreditationID = @AccreditationID
	END
	ELSE
	BEGIN
		IF @ApplicationRequestTypeID <> 11 --temporary
		BEGIN
			--calculate expiry date.  5 Years form now if one of the below, else it keeps the original date
			-- 1 New Operating Licence	
			-- 9 New from source
			-- Transfers and Renewals
			-- @IsRenewal = An application type such as COV started within the renewal period
			IF @ApplicationRequestTypeID = 1 OR @ApplicationRequestTypeID = 9 OR @IncludeDeceasedTransferType = 1 OR @IncludeRenewalType = 1 OR @IsRenewal = 1
			BEGIN
				DECLARE @LicenceDurationMonths INT
				DECLARE @LicenceExpiryDate DATE

				SELECT @LicenceDurationMonths = Adj.FinalAdjudicationLicenceDurationMonths, @LicenceExpiryDate = Adj.FinalAdjudicationLicenceExpiryDate
				FROM App.ApplicationAdjudications Adj
				WHERE ApplicationDetailID = @ApplicationDetailID

				IF ISNULL(@LicenceDurationMonths,0) <> 0
				BEGIN
					SET @ExpiryDate = DATEADD(MONTH, @LicenceDurationMonths, @Today)
				END
				ELSE IF @LicenceExpiryDate IS NOT NULL
				BEGIN
					SET @ExpiryDate = @LicenceExpiryDate
				END
				ELSE
				BEGIN
					SET @ExpiryDate = DATEADD(yyyy, 5, @Today)
				END
			END
			ELSE IF @ApplicationrequestTypeID = 5 OR @ApplicationrequestTypeID = 13 OR @IsLtpsConversion=1 --5=Conversion (LTPS to legiti-mate), 13=Conversion (OLAS to Legiti-mate)
			BEGIN 
				SELECT @ExpiryDate = OperatingLicenceExpiryDate
				FROM App.ApplicationLicences
				WHERE ApplicationLicenceID = @ApplicationLicenceID

				--Check if the expiry date is the same as the application start date. The system assigns it this way if an indefinite licence is received
				IF EXISTS(SELECT TOP 1 1 FROM App.ApplicationDetails Ard WHERE ApplicationDetailID = @ApplicationDetailID AND DAY(Ard.CreatedDate) = DAY(@ExpiryDate) AND 
					MONTH(Ard.CreatedDate) = MONTH(@ExpiryDate) AND YEAR(Ard.CreatedDate) = YEAR(@ExpiryDate)) OR @ExpiryDate IS NULL OR @ExpiryDate < @Today
				BEGIN
					--Rule Change 15 Nov 2014. Conversion applications without an expiry date will now only be valid for a week as per email from Mark.
					SET @ExpiryDate = DATEADD(DAY, 7, @Today)
				END		
			END
			ELSE
			BEGIN
				SELECT @ExpiryDate = OperatingLicenceExpiryDate
				FROM App.ApplicationLicences
				WHERE ApplicationDetailID = @ApplicationDetailID 
		
				--Check if the expiry date is the same as the application start date. The system assigns it this way if an indefinite licence is received
				IF EXISTS(SELECT TOP 1 1 FROM App.ApplicationDetails Ard WHERE ApplicationDetailID = @ApplicationDetailID AND DAY(Ard.CreatedDate) = DAY(@ExpiryDate) AND 
					MONTH(Ard.CreatedDate) = MONTH(@ExpiryDate) AND YEAR(Ard.CreatedDate) = YEAR(@ExpiryDate)) OR @ExpiryDate IS NULL --Some LTPS conversions are coming through without expiry dates
				BEGIN
					--Rule Change 4 April 2014. Indefinite licences will now be valid for 5 years instead of until 8 Dec 2016 as per email from Mark Lehrer
					--SET @ExpiryDate = '8 December 2016' --Expiry date for indefinite licences
					SET @ExpiryDate = DATEADD(yyyy, 5, @Today)
				END		
			END
		END
		ELSE
		BEGIN
			SELECT @ExpiryDate = Ael.SpecialLicenceDateTo
			FROM App.ApplicationLicences Ael
			WHERE ApplicationLicenceID = @ApplicationLicenceID		
		END
	END

	DECLARE @IsTemporaryLicence BIT = CASE WHEN @ApplicationRequestTypeID = 11 /*temp licence*/ THEN 1 ELSE 0 END
	
	EXEC @LicenceID = Verified.Pr_LicenceCreate @LicenceID, @ApplicationDetailID, @ApplicationLicenceID, @AccreditationID, @MemberID, @VehicleID, @LicenceVehicleTypeID, @LicenceCarryingCapacityID, @DivisionTypeID, @LicencingBoardID, @ApplicationProvinceID, @NameOnLicence, @IssueNumber, @IssueDate, @ExpiryDate, @IsTemporaryLicence, @UserID, @AuditDescription

	--Map the member to the association if he does not exist
	IF @MemberID IS NOT NULL AND @AssociationID IS NOT NULL
	BEGIN
		IF NOT EXISTS(SELECT TOP 1 1 FROM Verified.AssociationMembers WHERE MemberID = @MemberID AND AssociationID = @AssociationID)
		BEGIN
			INSERT INTO Verified.AssociationMembers(AssociationID, MemberID)
			VALUES(@AssociationID, @MemberID)			
		END
	END

	--Update any other in-progress applications (such as additional auth) with new licence details
	IF ISNULL(@ExistingLicenceNumber,'') <> '' 
	BEGIN
		DECLARE @NewOperatingLicenceNumber VARCHAR(255)
		DECLARE @NewOperatingLicenceIssueDate DATETIME
		DECLARE @NewOperatingLicenceExpiryDate DATETIME

		SELECT @NewOperatingLicenceNumber = OperatingLicenceNumber, @NewOperatingLicenceIssueDate = DateOfIssue, @NewOperatingLicenceExpiryDate = DateOfExpiry
		FROM Verified.Licences 
		WHERE LicenceID = @LicenceID

		IF @NewOperatingLicenceNumber NOT LIKE 'LT%' --only update running workflows if the current app is not a temporary licence
		BEGIN
			DECLARE @ApplicationsInProgressTable TABLE(ApplicationDetailID BIGINT, ApplicationLicenceID BIGINT)
			DELETE FROM @ApplicationsInProgressTable

			INSERT INTO @ApplicationsInProgressTable(ApplicationDetailID, ApplicationLicenceID)
			SELECT Ard.ApplicationDetailID, AEL.ApplicationLicenceID
			FROM App.ApplicationDetails AS ARD
				INNER JOIN App.ApplicationLicences AS AEL ON ARD.ApplicationDetailID = AEL.ApplicationDetailID
				INNER JOIN Wf.WorkflowInstances AS WI ON ARD.WorkflowInstanceID = WI.WorkflowInstanceID
			WHERE Ard.ApplicationDetailID <> @ApplicationDetailID
				AND WorkflowInstanceStatusID = 1 -- IN Progress
				AND ARD.IsCancelled = 0
				AND [App].[Fn_StripVersionNumber](Ael.OperatingLicenceNumber,'/') LIKE [App].[Fn_StripVersionNumber](@ExistingLicenceNumber,'/')
		
			IF EXISTS(SELECT TOP 1 1 FROM @ApplicationsInProgressTable)
			BEGIN
				UPDATE App.ApplicationLicenceVehicleDetails
				SET
					VerifiedVehicleID = @VehicleID,
					EngineNumber = @EngineNumber,
					VehicleRegistrationNumber = @RegistrationNumber,
					Vin = @VIN,
					SeatedCapacity = @SeatedCapacity,
					StandingCapacity = @StandingCapacity,
					GrossMass = @GrossMass, 
					VehicleMakeID = @VehicleMakeID,
					VehicleTypeID = @VehicleTypeID,
					IsStillToBeAcquired = @IsStillToBeAcquired,
					CarryingCapacityID = @CarryingCapacityID,
					YearOfManufacture = @YearOfManufacture			
				WHERE ApplicationLicenceID IN (SELECT ApplicationLicenceID FROM @ApplicationsInProgressTable)

				UPDATE App.ApplicationApplicantDetails
				SET
					VerifiedMemberID = @MemberID,						
					SurnameOrCompanyName = @SurnameOrCompanyName,
					FirstNames = @FirstNames,
					IDTypeID = @IDTypeID,
					IDTypeDescription = @IDTypeDescription,
					IDNumberOrBusinessRegistrationNo = @IDNumberOrBusinessRegistrationNo, 
					PostalAddress = @PostalAddress,
					PostalAddressCode = @PostalAddressCode,
					PhysicalAddress = @PhysicalAddress,
					PhysicalAddressCode = @PhysicalAddressCode,
					TelPhone = @TelPhone,
					TelCell = @TelCell, 
					TelFax = @TelFax,
					Email = @Email,
					SARSIncomeTaxNumber = @SARSIncomeTaxNumber,
					TradeName = @TradeName,
					BusinesstypeID = @BusinessTypeID
				WHERE ApplicationDetailID IN (SELECT ApplicationDetailID FROM @ApplicationsInProgressTable)

				UPDATE App.ApplicationLicences
				SET
					OperatingLicenceNumber = @NewOperatingLicenceNumber,
					OperatingLicenceIssueDate = @NewOperatingLicenceIssueDate, 
					OperatingLicenceExpiryDate = @NewOperatingLicenceExpiryDate
				WHERE ApplicationLicenceID IN (SELECT ApplicationLicenceID FROM @ApplicationsInProgressTable)

				UPDATE App.ApplicationDetails
				SET
					SourceApplicationID = 3			
				WHERE ApplicationDetailID IN (SELECT ApplicationDetailID FROM @ApplicationsInProgressTable)
			END
		END        		
	END

	--LICENCE SERVICE TYPES------------------------------------------------------------------------		
	INSERT INTO Verified.LicenceServiceDetails(LicenceID, ServiceTypeID)
	SELECT @LicenceID, ServiceTypeID
	FROM App.ApplicationLicenceServiceDetails
	WHERE ApplicationLicenceID = @ApplicationLicenceID

	--LICENCE ROUTES------------------------------------------------------------------------		
	DECLARE @SqlScript VARCHAR(MAX)
	DECLARE @VerifiedRouteID BIGINT			
	DECLARE @RouteTypeID SMALLINT
	DECLARE @OriginPlaceID BIGINT
	DECLARE @DestinationPlaceID BIGINT
	DECLARE @SystemUserID BIGINT
	SELECT @SystemUserID = CAST(System.Fn_GetSettingValue('SystemUserID') AS BIGINT)

	IF @ApplicationRequestTypeID <> 11 /*11=temp licence*/ AND @DivisionTypeID <> 2 /*NPTR routes are created at this point as well*/
	BEGIN
		IF EXISTS(SELECT TOP 1 1 FROM App.ApplicationLicenceRouteDetails WHERE ApplicationLicenceID = @ApplicationLicenceID AND VerifiedRouteID IS NOT NULL)
		BEGIN
			SELECT @SqlScript = COALESCE(@SqlScript + ';', '') + 'EXEC Verified.Pr_LicenceRouteAdd ' + CAST(@LicenceID AS VARCHAR(10)) + ',' + CAST(ISNULL((SELECT TOP 1  RouteID FROM Verified.Routes SR WHERE SR.IsLatest = 1 AND SR.ParentRouteID = R.RouteID),VerifiedRouteID) AS VARCHAR(10)) + ',' + CAST(ServiceTypeID AS VARCHAR(10))  + ',' + CAST(@UserID AS VARCHAR(20))
			FROM App.ApplicationLicenceRouteDetails
			INNER JOIN Verified.Routes R ON R.RouteID = VerifiedRouteID
			WHERE ApplicationLicenceID = @ApplicationLicenceID
				AND RouteChangeTypeID <> 4 /*Remove route from licence*/
				AND VerifiedRouteID IS NOT NULL
				AND ServiceTypeID IN (
					SELECT ServiceTypeID
					FROM App.ApplicationLicenceServiceDetails
					WHERE ApplicationLicenceID = @ApplicationLicenceID
				)

				EXEC(@SqlScript);
				SET @SqlScript = '';
		END

		IF EXISTS(SELECT TOP 1 1 FROM App.ApplicationLicenceRouteDetails WHERE ApplicationLicenceID = @ApplicationLicenceID AND VerifiedRouteID IS NULL)
		BEGIN
			/*Create any required temporary routes*/						
			DECLARE @RouteOrigin VARCHAR(255)
			DECLARE @RouteDestination VARCHAR(255)
			DECLARE @RouteDescription VARCHAR(MAX)
			DECLARE @ServiceTypeID SMALLINT
			DECLARE @RouteLength FLOAT

			DECLARE tempRouteCursor CURSOR FAST_FORWARD READ_ONLY FOR 
				SELECT RouteOrigin, RouteDestination, RouteDescription, Alrd.ServiceTypeID, ST.RouteTypeID, Alrd.RouteLength
				FROM App.ApplicationLicenceRouteDetails Alrd
					INNER JOIN Md.ServiceTypes ST ON ST.ServiceTypeID = Alrd.ServiceTypeID
				WHERE ApplicationLicenceID = @ApplicationLicenceID
					AND VerifiedRouteID IS NULL	
					AND ST.ServiceTypeID IN (
						SELECT ServiceTypeID
						FROM App.ApplicationLicenceServiceDetails
						WHERE ApplicationLicenceID = @ApplicationLicenceID
					)
			OPEN tempRouteCursor
			
			FETCH NEXT FROM tempRouteCursor INTO @RouteOrigin, @RouteDestination, @RouteDescription, @ServiceTypeID, @RouteTypeID, @RouteLength
			
			WHILE @@FETCH_STATUS = 0
			BEGIN				
				SET @VerifiedRouteID = NULL				

				/*Create new origin place id if required*/
				SET @OriginPlaceID = NULL
				SELECT @OriginPlaceID = PlaceID
				FROM Verified.Places
				WHERE Description LIKE @RouteOrigin
					AND IsCancelled = 0
				
				IF ISNULL(@OriginPlaceID,-1) = -1
				BEGIN
					INSERT INTO Verified.Places(Description,PlaceTypeID,OlasID,LtpsID,NeoID)
					VALUES (@RouteOrigin, 6 /*TEMPORARY PLACE*/, NULL,NULL,NULL)

					SET @OriginPlaceID = SCOPE_IDENTITY()
				END
                
				/*Create new destination place id if required*/
				SET @DestinationPlaceID = NULL
				SELECT @DestinationPlaceID = PlaceID
				FROM Verified.Places
				WHERE Description LIKE @RouteDestination
					AND IsCancelled = 0
				
				IF ISNULL(@DestinationPlaceID,-1) = -1
				BEGIN
					INSERT INTO Verified.Places(Description,PlaceTypeID,OlasID,LtpsID,NeoID)
					VALUES (@RouteDestination,6,NULL,NULL,NULL)

					SET @DestinationPlaceID = SCOPE_IDENTITY()
				END

				INSERT INTO Verified.Routes(RouteTypeID,RouteName,Description,OriginPlaceID,DestinationPlaceID,NationalRouteNumber,BoardRouteNumber,RouteLength,RouteStatusID,OlasID,LtpsID,NeoID,ProvinceID,TimeTableDocumentID,FareScheduleDocumentID)				
				VALUES( @RouteTypeID, ISNULL(@RouteOrigin,'') + ' - ' + ISNULL(@RouteDestination,''), @RouteDescription, @OriginPlaceID,@DestinationPlaceID,NULL,NULL,@RouteLength,1 /*active*/, NULL,NULL,NULL,NULL,NULL,NULL)

				SET @VerifiedRouteID = SCOPE_IDENTITY()

				INSERT INTO Verified.LicenceRoutes(LicenceID, RouteID, ServiceTypeID)
				SELECT @LicenceID, @VerifiedRouteID, @ServiceTypeID
				
				IF ISNULL(@AssociationID,-1) <> -1
				BEGIN
					IF NOT EXISTS(SELECT TOP 1 1 FROM Verified.AssociationRoutes WHERE AssociationID = @AssociationID AND RouteID = @VerifiedRouteID)
					BEGIN

						EXEC Verified.Pr_AssociationRouteAdd @AssociationID, @VerifiedRouteID, @SystemUserID				
					END
				END				
                
			    FETCH NEXT FROM tempRouteCursor INTO @RouteOrigin, @RouteDestination, @RouteDescription, @ServiceTypeID, @RouteTypeID, @RouteLength
			END
			
			CLOSE tempRouteCursor
			DEALLOCATE tempRouteCursor
				
		END		
	END
	ELSE
	BEGIN
		IF EXISTS(SELECT TOP 1 1 FROM App.ApplicationLicenceRouteDetails WHERE ApplicationLicenceID = @ApplicationLicenceID AND VerifiedRouteID IS NOT NULL)
		BEGIN
			/*Link the existing verified routes*/
			SELECT @SqlScript = COALESCE(@SqlScript + ';', '') + 'EXEC Verified.Pr_LicenceRouteAdd ' + CAST(@LicenceID AS VARCHAR(10)) + ',' + CAST(ISNULL((SELECT TOP 1  RouteID FROM Verified.Routes SR WHERE SR.IsLatest = 1 AND SR.ParentRouteID = R.RouteID),VerifiedRouteID) AS VARCHAR(10)) + ',' + CAST(ServiceTypeID AS VARCHAR(10)) + ',' + CAST(@UserID AS VARCHAR(20))
			FROM App.ApplicationLicenceRouteDetails
			INNER JOIN Verified.Routes R ON R.RouteID = VerifiedRouteID
			WHERE ApplicationLicenceID = @ApplicationLicenceID
				AND VerifiedRouteID IS NOT NULL
				AND RouteChangeTypeID <> 4 /*Remove route from licence*/
				AND ServiceTypeID IN (
					SELECT ServiceTypeID
					FROM App.ApplicationLicenceServiceDetails
					WHERE ApplicationLicenceID = @ApplicationLicenceID
				)

			EXEC(@SqlScript);
		END
        
		IF EXISTS(SELECT TOP 1 1 FROM App.ApplicationLicenceRouteDetails WHERE ApplicationLicenceID = @ApplicationLicenceID AND VerifiedRouteID IS NULL)
		BEGIN	
			/*Create any required temporary routes*/						
			DECLARE @TempRouteOrigin VARCHAR(255)
			DECLARE @TempRouteDestination VARCHAR(255)
			DECLARE @TempRouteDescription VARCHAR(MAX)
			DECLARE @TempServiceTypeID SMALLINT

			DECLARE tempRouteCursor CURSOR FAST_FORWARD READ_ONLY FOR 
				SELECT RouteOrigin, RouteDestination, RouteDescription, ServiceTypeID
				FROM App.ApplicationLicenceRouteDetails
				WHERE ApplicationLicenceID = @ApplicationLicenceID
					AND VerifiedRouteID IS NULL	
					AND ServiceTypeID IN (
						SELECT ServiceTypeID
						FROM App.ApplicationLicenceServiceDetails
						WHERE ApplicationLicenceID = @ApplicationLicenceID
					)
			OPEN tempRouteCursor
			
			FETCH NEXT FROM tempRouteCursor INTO @TempRouteOrigin, @TempRouteDestination, @TempRouteDescription, @TempServiceTypeID
			
			WHILE @@FETCH_STATUS = 0
			BEGIN				
				SET @VerifiedRouteID = NULL				
				SET @RouteTypeID  = NULL

				IF @DivisionTypeID = 2 /*NPTR - does not need origina/destinations*/
				BEGIN
					SET @RouteTypeID = 3 /*AREA OF OPERATION*/
				END
				ELSE
				BEGIN
					SET @RouteTypeID = 4 /*TEMPORARY ROUTE*/					

					/*Create new origin place id if required*/
					SET @OriginPlaceID = NULL
					SELECT @OriginPlaceID = PlaceID
					FROM Verified.Places
					WHERE Description LIKE @TempRouteOrigin
						AND IsCancelled = 0
				
					IF ISNULL(@OriginPlaceID,-1) = -1
					BEGIN
						INSERT INTO Verified.Places(Description,PlaceTypeID,OlasID,LtpsID,NeoID)
						VALUES (@TempRouteOrigin, 6 /*TEMPORARY PLACE*/, NULL,NULL,NULL)

						SET @OriginPlaceID = SCOPE_IDENTITY()
					END
                
					/*Create new destination place id if required*/
					SET @DestinationPlaceID = NULL
					SELECT @DestinationPlaceID = PlaceID
					FROM Verified.Places
					WHERE Description LIKE @TempRouteDestination
						AND IsCancelled = 0
				
					IF ISNULL(@DestinationPlaceID,-1) = -1
					BEGIN
						INSERT INTO Verified.Places(Description,PlaceTypeID,OlasID,LtpsID,NeoID)
						VALUES (@TempRouteDestination,6,NULL,NULL,NULL)

						SET @DestinationPlaceID = SCOPE_IDENTITY()
					END
				END

				INSERT INTO Verified.Routes(RouteTypeID,RouteName,Description,OriginPlaceID,DestinationPlaceID,NationalRouteNumber,BoardRouteNumber,RouteLength,RouteStatusID,OlasID,LtpsID,NeoID,ProvinceID,TimeTableDocumentID,FareScheduleDocumentID)				
				VALUES( @RouteTypeID, ISNULL(@TempRouteOrigin,'') + ' - ' + ISNULL(@TempRouteDestination,''), @TempRouteDescription, @OriginPlaceID,@DestinationPlaceID,NULL,NULL,NULL,1 /*active*/, NULL,NULL,NULL,NULL,NULL,NULL)

				SET @VerifiedRouteID = SCOPE_IDENTITY()

				INSERT INTO Verified.LicenceRoutes(LicenceID, RouteID, ServiceTypeID)
				SELECT @LicenceID, @VerifiedRouteID, @TempServiceTypeID
				
				IF ISNULL(@AssociationID,-1) <> -1
				BEGIN
					IF NOT EXISTS(SELECT TOP 1 1 FROM Verified.AssociationRoutes WHERE AssociationID = @AssociationID AND RouteID = @VerifiedRouteID)
					BEGIN						

						EXEC Verified.Pr_AssociationRouteAdd @AssociationID, @VerifiedRouteID, @SystemUserID				
					END
				END				
                
			    FETCH NEXT FROM tempRouteCursor INTO @TempRouteOrigin, @TempRouteDestination, @TempRouteDescription, @TempServiceTypeID
			END
			
			CLOSE tempRouteCursor
			DEALLOCATE tempRouteCursor
				
		END
	END
	
	--COPY MEMBER DOCS------------------------------------------------------------------------
	EXEC Wf.Pr_WorkflowInstanceDocumentsCopyToEntity @WorkflowInstanceID, 1009 /*Verified.Members*/, @MemberID, '', @UserID

	EXEC [Verified].[Pr_LicenceExceptionsUpdate] @LicenceID

	EXEC App.Pr_LicenceIssueRequestStartNewWorkflow @ApplicationDetailID, @ApplicationLicenceID, @LicenceID, @VehicleID, @UserID
END
GO