CREATE OR ALTER PROCEDURE [App].[Pr_ApplicationCreate]
	@ApplicationPrecheckDetailID BIGINT,
	@UserID BIGINT
AS
-- EXEC [App].[Pr_ApplicationCreate] 1199229, 1001
BEGIN	
	SET NOCOUNT ON;
	
	/********************************************************************************************
	Get user details and perform security checks
	*********************************************************************************************/	
	DECLARE @EntityIDToCheck INT = 1024 /*ApplicationPrecheckDetails*/
	DECLARE @ItemIDToCheck BIGINT = @ApplicationPrecheckDetailID
	DECLARE @Action VARCHAR(255) = '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	

	IF EXISTS(SELECT * FROM App.ApplicationDetails WHERE ApplicationPrecheckDetailID = @ApplicationPrecheckDetailID)
	BEGIN
		RAISERROR('#EXPOSE_ERROR#Precheck already used', 16, 1)
		RETURN
	END

	DECLARE @ApplicationRequestTypeID SMALLINT,
		@SourceApplicationID SMALLINT = 3,
		@ApplicantName VARCHAR(510),
		@TelCell VARCHAR(14),
		@IdTypeID SMALLINT,
		@IDTypeDescription VARCHAR(255),
		@IdOrBusinessRegistrationNumber	VARCHAR(20),	
		@VehicleTypeID SMALLINT,
		@ServiceTypeIDs VARCHAR(255),
		@NumberOfLicencesRequired BIGINT,
		@ApplicationProvinceID SMALLINT,
		@ApplicationRegionID SMALLINT,
		@ApplicationRequestType VARCHAR(255),
		@AssociationID BIGINT

	SELECT @ApplicationRequestTypeID = ApplicationRequestTypeID,
		@ApplicantName  = ApplicantName ,
		@TelCell = TelCell,
		@IdTypeID = IdTypeID,
		@IdOrBusinessRegistrationNumber	 = IdOrBusinessRegistrationNumber,
		@VehicleTypeID = VehicleTypeID,
		@ServiceTypeIDs  = ServiceTypeIDs,
		@NumberOfLicencesRequired = NumberOfLicencesRequired,
		@ApplicationProvinceID = ApplicationProvinceID,
		@ApplicationRegionID = ApplicationRegionID,
		@DivisionTypeID = OfficeLocationDivisionTypeID,
		@ProvinceID = ApplicationProvinceID,
		@AssociationID = AssociationID
	FROM App.ApplicationPrecheckDetails
	WHERE ApplicationPrecheckDetailID = @ApplicationPrecheckDetailID
	
	SET @IDTypeDescription = (SELECT TOP 1 Description FROM Md.IDTypes WHERE IdTypeID = @IdTypeID)
	SET @ApplicationRegionID = ISNULL(@ApplicationRegionID, (SELECT TOP 1 RegionID FROM Verified.Associations WHERE AssociationID = @AssociationID))

	SELECT @ApplicationRequestType = Title
	FROM Md.ApplicationRequestTypes
	WHERE ApplicationRequestTypeID = @ApplicationRequestTypeID

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

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

	/********************************************************************************************
	Create application
	*********************************************************************************************/	
	DECLARE @ApplicationDetailID BIGINT
	DECLARE @IsBulkApplication BIT = CASE WHEN @NumberOfLicencesRequired > 1 THEN 1 ELSE 0 END
	DECLARE @FirstNames VARCHAR(255)
	DECLARE @SurnameOrCompanyName VARCHAR(255)
	DECLARE @GazetteTypeID SMALLINT = App.Fn_GetGazetteType(@ApplicationRequestTypeID, @DivisionTypeID, @ProvinceID)		


	IF EXISTS (SELECT TOP 1 1 FROM Md.IDTypes WHERE IDTypeID = @IdTypeID AND IsForIndividual = 1)
	BEGIN
		SET @FirstNames = LEFT(@ApplicantName, System.Fn_RcharIndex(' ', @ApplicantName, 0))
		SET @SurnameOrCompanyName = TRIM(SUBSTRING(@ApplicantName, System.Fn_RcharIndex(' ', @ApplicantName, 0), 510))	
	END
	ELSE 
	BEGIN
		SET @SurnameOrCompanyName = @ApplicantName
	END

	/********************************************************************************************
	Get Precheck Details
	*********************************************************************************************/
	DECLARE @SourceApplicationXML XML
	SET @SourceApplicationXML = (SELECT TOP 1 SourceApplicationXML FROM App.ApplicationPrecheckLicenceDetails WHERE ApplicationPrecheckDetailID = @ApplicationPrecheckDetailID AND SourceApplicationXML IS NOT NULL)

	/*************************************************
    Check if this is an existing member or not
	*************************************************/
	DECLARE @VerifiedMemberID BIGINT
	DECLARE @VerifiedAccreditationID BIGINT

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

	/*************************************************
    Create the base application record
	*************************************************/
	INSERT INTO App.ApplicationDetails(AccreditationID, ApplicationRequestTypeID, SourceApplicationID, OfficeLocationID, OfficeLocationDivisionTypeID, ApplicationProvinceID, ApplicationRegionID, CreatedByID, CreatedDate, UpdatedByID, UpdatedDate, IsBulkApplication, LicenceCount, AdjudicationOfficeLocationID, ApplicationStatusID, ApplicationSubStatusID, GazetteTypeID, ApplicationPrecheckDetailID, ForceGazetting, VerifiedAssociationID)
	VALUES(@VerifiedAccreditationID, @ApplicationRequestTypeID, @SourceApplicationID, @OfficeLocationID, @DivisionTypeID,  @ApplicationProvinceID, @ApplicationRegionID, @UserID, GETDATE(), @UserID, GETDATE(), @IsBulkApplication, @NumberOfLicencesRequired, @OfficeLocationID, 2 /*Data Capture*/, 2 /*Awaiting Data Capture*/, @GazetteTypeID, @ApplicationPrecheckDetailID, 1, @AssociationID)

	SET @ApplicationDetailID = SCOPE_IDENTITY()

	/*************************************************
    Update the application number
	*************************************************/
	DECLARE @ApplicationNumberTable TABLE(ApplicationNumber VARCHAR(20))
	DECLARE @ApplicationNumber VARCHAR(20)

	;WITH Cte AS
	(
		SELECT TOP(1) ReferenceNumber, IsUsed
		FROM System.ReferenceNumbers WITH (ROWLOCK, READPAST)
		WHERE 
			DivisionTypeID = @DivisionTypeID 
			AND ProvinceID = @ApplicationProvinceID 
			AND EntityID = 1000 /*ApplicationDetails*/
			AND IsUsed = 0 
		ORDER BY ReferenceNumberID
	)
	UPDATE Cte SET IsUsed = 1
	OUTPUT Inserted.ReferenceNumber INTO @ApplicationNumberTable;

	SELECT @ApplicationNumber = ApplicationNumber
	FROM @ApplicationNumberTable

	UPDATE App.ApplicationDetails
	SET	
		ApplicationNumber = @ApplicationNumber
	WHERE ApplicationDetailID = @ApplicationDetailID

	/*************************************************
    Create Bypass Business Rules Record
	*************************************************/
	INSERT INTO App.ApplicationBypassBusinessRules(ApplicationDetailID)
	SELECT @ApplicationDetailID

	/*************************************************
    Create the applicant record 
	*************************************************/
	IF ISNULL(@VerifiedMemberID,-1) <> -1
	BEGIN
		INSERT INTO App.ApplicationApplicantDetails(ApplicationDetailID, SurnameOrCompanyName, FirstNames, IDTypeID, IDTypeDescription, IDNumberOrBusinessRegistrationNo, 
			PostalAddress, PostalAddressCode, PhysicalAddress, PhysicalAddressCode, TelPhone, TelCell, TelFax, Email, 
			SARSIncomeTaxNumber, TradeName, BusinessTypeID, VerifiedMemberID,IsApplicantIndividual)
		SELECT @ApplicationDetailID, SurnameOrCompanyName, FirstNames, ISNULL(IDTypeID,@IdTypeID), IDTypeDescription, IDNumberOrBusinessRegistrationNo, 
			System.Fn_TrimString(PostalAddress), PostalAddressCode, System.Fn_TrimString(PhysicalAddress), PhysicalAddressCode, TelPhone, TelCell, TelFax, Email, 
			SARSIncomeTaxNumber, TradeName, BusinessTypeID, MemberID,CASE WHEN ISNULL(IDTypeID,@IdTypeID) IN (1, 2, 4, 5) THEN 1 ELSE 0 END
		FROM Verified.Members
		WHERE MemberID = @VerifiedMemberID		
	END
	ELSE IF (@SourceApplicationXML IS NOT NULL AND @IncludeDeceasedTransferType = 0 AND @IncludeNormalTransferType = 0)
	BEGIN
		INSERT INTO App.ApplicationApplicantDetails(ApplicationDetailID, SurnameOrCompanyName, FirstNames, IDTypeID, IDTypeDescription, IDNumberOrBusinessRegistrationNo, PostalAddress, PostalAddressCode, PhysicalAddress, PhysicalAddressCode, TelPhone, TelCell, TelFax, Email, SARSIncomeTaxNumber, TradeName, BusinessTypeID, VerifiedMemberID,IsApplicantIndividual)
		SELECT @ApplicationDetailID, SurnameOrCompanyName,FirstNames, ISNULL(IdTypeID,@IdTypeID), ISNULL(IDTypeDescription,@IDTypeDescription), IDNumberOrBusinessRegistrationNo, System.Fn_TrimString(PostalAddress), PostalAddressCode, System.Fn_TrimString(PhysicalAddress), PhysicalAddressCode, TelPhone, TelCell, TelFax, Email, SARSIncomeTaxNumber, TradeName, BusinessTypeID, VerifiedMemberID,CASE WHEN ISNULL(IDTypeID,@IdTypeID) IN (1, 2, 4, 5) THEN 1 ELSE 0 END
		FROM Imported.Fn_SourceApplicationXmlMemberGet(@SourceApplicationXML, @SourceApplicationID)
	END
	ELSE IF (@ApplicationRequestTypeID IN (36, 37) /*Add New Authorisations/Routes, Amend Authorisations/Routes*/)
	BEGIN
		INSERT INTO App.ApplicationApplicantDetails(ApplicationDetailID, SurnameOrCompanyName, FirstNames, IDTypeID, IDTypeDescription, IDNumberOrBusinessRegistrationNo, PostalAddress, PostalAddressCode, PhysicalAddress, PhysicalAddressCode, TelPhone, TelCell, TelFax, Email, SARSIncomeTaxNumber, TradeName, BusinessTypeID, VerifiedMemberID,IsApplicantIndividual)
		SELECT @ApplicationDetailID,AssociationName, '', 11 /*Taxi Association Registration Number*/, 'Taxi Association Registration Number', ReferenceNumber, System.Fn_TrimString(PostalAddress), PostalAddressCode, System.Fn_TrimString(PhysicalAddress), PhysicalAddressCode, TelNumber, CellNumber, FaxNumber, EmailAddress, '', AssociationName, NULL, null,CASE WHEN 11 IN (1, 2, 4, 5) THEN 1 ELSE 0 END
		FROM Verified.Associations
		WHERE AssociationID = @AssociationID
	END
	ELSE
	BEGIN
		INSERT INTO App.ApplicationApplicantDetails(ApplicationDetailID, SurnameOrCompanyName, FirstNames, IDTypeID, IDNumberOrBusinessRegistrationNo, TelCell,IsApplicantIndividual)
		VALUES(@ApplicationDetailID, @SurnameOrCompanyName, @FirstNames, @IDTypeID, @IdOrBusinessRegistrationNumber, @TelCell,CASE WHEN @IDTypeID IN (1, 2, 4, 5) THEN 1 ELSE 0 END)
	END
    
	/*************************************************
    Create the contract record
	*************************************************/ 
	INSERT INTO App.ApplicationContractDetails(ApplicationDetailID)
	VALUES(@ApplicationDetailID)

	/*************************************************
    Create the TAT record
	*************************************************/ 
	INSERT INTO App.ApplicationTATDetails(ApplicationDetailID)
	VALUES(@ApplicationDetailID)

	/*************************************************
    Create the previous licence holder record
		(record will be created later for tranfer types based on the member linked to the existing licence)
	*************************************************/ 
	IF @IncludeDeceasedTransferType = 1 OR @IncludeNormalTransferType = 1
	BEGIN		
		INSERT INTO App.ApplicationPreviousLicenceHolder(ApplicationDetailID, SurnameOrCompanyName, FirstNames, IDTypeID, IDTypeDescription, IDNumberOrBusinessRegistrationNo, PostalAddress, PostalAddressCode, PhysicalAddress, PhysicalAddressCode, TelPhone, TelCell, TelFax, Email, SARSIncomeTaxNumber, TradeName)
		SELECT @ApplicationDetailID, SurnameOrCompanyName,FirstNames, ISNULL(IdTypeID,@IdTypeID), ISNULL(IDTypeDescription,@IDTypeDescription), IDNumberOrBusinessRegistrationNo, PostalAddress, PostalAddressCode, PhysicalAddress, PhysicalAddressCode, TelPhone, TelCell, TelFax, Email, SARSIncomeTaxNumber, TradeName
		FROM Imported.Fn_SourceApplicationXmlMemberGet(@SourceApplicationXML, @SourceApplicationID)
	END
	ELSE BEGIN
		INSERT INTO App.ApplicationPreviousLicenceHolder(ApplicationDetailID)
		VALUES(@ApplicationDetailID)
	END

	/*************************************************
    Create the NPTR record
	*************************************************/ 
	INSERT INTO App.ApplicationNPTRDetails(ApplicationDetailID)
	VALUES(@ApplicationDetailID)

	/*************************************************
    Create the inspection record
	*************************************************/ 
	INSERT INTO App.ApplicationInspectionDetails(ApplicationDetailID, InspectionStatusID)
	VALUES(@ApplicationDetailID, 1 /*Awaiting inspection date*/)

	/*************************************************
	Create the tourism authority referral
	*************************************************/ 
	IF @DivisionTypeID = 2
	BEGIN
		INSERT INTO App.ApplicationReferrals(ApplicationDetailID, ReferralTypeID)
		VALUES(@ApplicationDetailID, 1 /*Tourism Authority*/)
	END
	ELSE
	BEGIN
		/*************************************************
		Create the planning authority referral
		*************************************************/ 
		INSERT INTO App.ApplicationReferrals(ApplicationDetailID, ReferralTypeID)
		VALUES(@ApplicationDetailID, 2 /*Planning Authority*/)
	END

	/*************************************************
    Create the application status record
	*************************************************/ 
	INSERT INTO App.ApplicationStatusHistory(ApplicationDetailID,ApplicationStatusID,UpdatedByID,UpdatedDate)
	VALUES(@ApplicationDetailID,2/*Data Capture*/,@UserID,GETDATE())

	/*************************************************
    Create the application sub status record
	*************************************************/ 
	INSERT INTO App.ApplicationSubStatusHistory
	(
	    ApplicationDetailID,
	    ApplicationSubStatusID,
	    UpdatedByID,
	    UpdatedDate
	)
	VALUES(@ApplicationDetailID,2/*Awaiting Data Capture*/,@UserID,GETDATE())
	
	/*************************************************
    Create the required licence record(s)
	*************************************************/ 
	DECLARE @ApplicationLicenceID BIGINT
	DECLARE @OperatingLicenceNumber VARCHAR(100)
	DECLARE @SourceApplicationLicenceXML XML
	DECLARE @ApplicationLicenceRowNumber SMALLINT 
	DECLARE @ApplicationLicenceReferenceNumber VARCHAR(20)
	DECLARE @LicenceDescription VARCHAR(500)
	DECLARE @LicenceVehicleTypeID SMALLINT
	DECLARE @LicenceCount INT = 0

	IF @ApplicationRequestTypeID IN (1 /*new ol*/, 31 /*new accreditation*/, 38 /*new accreditation without Operating Licence*/)
	BEGIN
		WHILE @LicenceCount < @NumberOfLicencesRequired
		BEGIN
			SET @LicenceDescription = NULL
			SET @ApplicationLicenceRowNumber = @LicenceCount + 1
			SET @ApplicationLicenceReferenceNumber = @ApplicationNumber + '-' + CAST(@ApplicationLicenceRowNumber AS VARCHAR(20))
			SET @ApplicationLicenceID = NULL
			SET @OperatingLicenceNumber = 'NEW OPERATING LICENCE ' + System.Fn_PadLeft('0', 3, @LicenceCount + 1)
			SET @LicenceDescription = @ApplicationRequestType + ' for member ' + @ApplicantName
		
			INSERT INTO App.ApplicationLicences(ApplicationDetailID, OperatingLicenceNumber, VehicleTypeID, ApplicationLicenceReferenceNumber, CurrentVerifiedLicenceID, IsExistingLicence, ApplicationLicenceRequestTypeID, SplitIntoNewApplication, ApplicationLicenceDescription, IsCancelled)
			VALUES(@ApplicationDetailID, @OperatingLicenceNumber, @VehicleTypeID, @ApplicationLicenceReferenceNumber,NULL, 0, 1 /*new ol*/, 0, @LicenceDescription, 0)
		
			SELECT @ApplicationLicenceID = SCOPE_IDENTITY()		

			/*************************************************
			Create the service details 
			*************************************************/ 
			IF @ApplicationRequestTypeID = 31 /*application for accreditation*/ OR @ApplicationRequestTypeID = 38 /*application for accreditation without Operating Licence*/
			BEGIN
				INSERT INTO app.ApplicationLicenceServiceDetails(ApplicationLicenceID,ServiceTypeID,ServiceTypeDescription)
				SELECT @ApplicationLicenceID, 4 /*tourist service*/, NULL								
			END
			ELSE
            BEGIN
				INSERT INTO app.ApplicationLicenceServiceDetails(ApplicationLicenceID,ServiceTypeID,ServiceTypeDescription)
				SELECT @ApplicationLicenceID, Value, NULL
				FROM System.Fn_GenSplitCSV(@ServiceTypeIDs, ',')
				WHERE Value IS NOT NULL
			END

			/*************************************************
			Create the vehicle details record
			*************************************************/ 
			IF 
				@IncludeCovType = 0 
				AND @IncludeIncreaseInCapacityType = 0
				AND @IncludeNewOperatingLicenceType = 0
			BEGIN
				/*************************************************
				Populate vehicle details from xml
				*************************************************/ 
				INSERT INTO App.ApplicationLicenceVehicleDetails(ApplicationLicenceID, IsStillToBeAcquired, VehicleMakeID, VehicleMakeDescription, VehicleTypeID, VIN, EngineNumber, YearOfManufacture, VehicleRegistrationNumber, SeatedCapacity, StandingCapacity, GrossMass, VerifiedVehicleID, CarryingCapacityID, CurrentOdometerReading, Tare, RoadworthyCertificateDocID, ProfessionalDriversPermitDocID, RankPermitDocID, CertificateOfRegistrationDocID, VehicleModelID)
				SELECT @ApplicationLicenceID, 0, VehicleMakeID, VehicleMakeDescription, VehicleTypeID, VIN, EngineNumber, YearOfManufacture, VehicleRegistrationNumber, SeatedCapacity, StandingCapacity, GrossMass, VerifiedVehicleID, CarryingCapacityID, CurrentOdometerReading, Tare, RoadworthyCertificateDocID, ProfessionalDriversPermitDocID, RankPermitDocID, CertificateOfRegistrationDocID, VehicleModelID
				FROM Imported.Fn_SourceApplicationXmlVehicleGet(@SourceApplicationLicenceXML, @SourceApplicationID)

				IF NOT EXISTS(SELECT * FROM App.ApplicationLicenceVehicleDetails WHERE ApplicationLicenceID = @ApplicationLicenceID)
				BEGIN
					INSERT INTO App.ApplicationLicenceVehicleDetails(ApplicationLicenceID, IsStillToBeAcquired)
					VALUES(@ApplicationLicenceID, 0)
				END
				
				/*************************************************
				Create a blank previous vehicle details record
				*************************************************/ 
				INSERT INTO App.ApplicationLicencePreviousVehicleDetails(ApplicationLicenceID)
				VALUES(@ApplicationLicenceID)				
			END
			ELSE
            BEGIN
				/*************************************************
				Create a blank vehicle details record
				*************************************************/ 
				INSERT INTO App.ApplicationLicenceVehicleDetails(ApplicationLicenceID, IsStillToBeAcquired)
				VALUES(@ApplicationLicenceID, 0)

				/*************************************************
				Populate vehicle details from xml
				*************************************************/ 
				INSERT INTO App.ApplicationLicencePreviousVehicleDetails(ApplicationLicenceID, VehicleMakeID, VehicleMakeDescription, VehicleTypeID, VIN, EngineNumber, YearOfManufacture, VehicleRegistrationNumber, SeatedCapacity, StandingCapacity, GrossMass, VerifiedVehicleID, CarryingCapacityID, CurrentOdometerReading, Tare, RoadworthyCertificateDocID, ProfessionalDriversPermitDocID, RankPermitDocID, CertificateOfRegistrationDocID, VehicleModelID)
				SELECT @ApplicationLicenceID, VehicleMakeID, VehicleMakeDescription, VehicleTypeID, VIN, EngineNumber, YearOfManufacture, VehicleRegistrationNumber, SeatedCapacity, StandingCapacity, GrossMass, VerifiedVehicleID, CarryingCapacityID, CurrentOdometerReading, Tare, RoadworthyCertificateDocID, ProfessionalDriversPermitDocID, RankPermitDocID, CertificateOfRegistrationDocID, VehicleModelID
				FROM Imported.Fn_SourceApplicationXmlVehicleGet(@SourceApplicationLicenceXML, @SourceApplicationID)

				IF NOT EXISTS(SELECT * FROM App.ApplicationLicencePreviousVehicleDetails WHERE ApplicationLicenceID = @ApplicationLicenceID)
				BEGIN
					INSERT INTO App.ApplicationLicencePreviousVehicleDetails(ApplicationLicenceID)
					VALUES(@ApplicationLicenceID)
				END
			END 
			
			SET @LicenceCount = @LicenceCount + 1
		END
	END
	ELSE IF @ApplicationRequestTypeID IN (36 /*Add New Authorisations/Routes*/)
	BEGIN
		WHILE @LicenceCount < @NumberOfLicencesRequired
		BEGIN
			SET @LicenceDescription = NULL
			SET @ApplicationLicenceRowNumber = @LicenceCount + 1
			SET @ApplicationLicenceReferenceNumber = @ApplicationNumber + '-' + CAST(@ApplicationLicenceRowNumber AS VARCHAR(20))

			SET @ApplicationLicenceID = NULL
			SET @OperatingLicenceNumber = 'NEW AUTHORITY ' + System.Fn_PadLeft('0', 3, @LicenceCount + 1)
			SET @LicenceDescription = @ApplicationRequestType + ' for association  ' + @ApplicantName
		
			INSERT INTO App.ApplicationLicences(ApplicationDetailID, OperatingLicenceNumber, VehicleTypeID, ApplicationLicenceReferenceNumber, CurrentVerifiedLicenceID, IsExistingLicence, ApplicationLicenceRequestTypeID, SplitIntoNewApplication, ApplicationLicenceDescription, IsCancelled)
			VALUES(@ApplicationDetailID, @OperatingLicenceNumber, @VehicleTypeID, @ApplicationLicenceReferenceNumber,NULL, 0, @ApplicationRequestTypeID /*new ol*/, 0, @LicenceDescription, 0)
		
			SELECT @ApplicationLicenceID = SCOPE_IDENTITY()
			
			/*************************************************
			Create the service details 
			*************************************************/ 
			INSERT INTO app.ApplicationLicenceServiceDetails(ApplicationLicenceID,ServiceTypeID,ServiceTypeDescription)
			SELECT @ApplicationLicenceID, 2 /*Unscheduled service*/, NULL	

			/*************************************************
			Create a licence route record
			*************************************************/ 
			INSERT INTO App.ApplicationLicenceRouteDetails(ApplicationLicenceID, ServiceTypeID,ApplicationRouteDetailStatusID,VerifiedRouteID,NewRouteRequestDetailID,RouteOrigin,RouteDestination,RouteDescription,NationalRouteNumber,BoardRouteNumber,RouteLength,TimeTableDocumentID,FareScheduleDocumentID,RouteNumber,RouteChangeTypeID,IsExistingLtpsOrOlasRoute)
			SELECT @ApplicationLicenceID, 2 /*Unscheduled service*/, 1 /*New*/, NULL, NULL, '', '', '', null, null, null, null, null, 1, 6 /*New authority/association route*/, 0
	
			/*************************************************
			Create a blank vehicle details record
			*************************************************/ 
			INSERT INTO App.ApplicationLicenceVehicleDetails(ApplicationLicenceID, IsStillToBeAcquired)
			VALUES(@ApplicationLicenceID, 1)

			INSERT INTO App.ApplicationLicencePreviousVehicleDetails(ApplicationLicenceID)
			VALUES(@ApplicationLicenceID)			
			
			SET @LicenceCount = @LicenceCount + 1
		END
	END
	ELSE IF @ApplicationRequestTypeID IN (37 /*Amend Authorisations/Routes*/)
	BEGIN
		/* declare variables */
		DECLARE @VerifiedRouteID BIGINT
		
		DECLARE Amend_RouteChanges CURSOR FAST_FORWARD READ_ONLY FOR 
		SELECT VerifiedRouteID
		FROM App.ApplicationPrecheckRouteDetails
		WHERE ApplicationPrecheckDetailID = @ApplicationPrecheckDetailID
		
		OPEN Amend_RouteChanges
		
		FETCH NEXT FROM Amend_RouteChanges INTO @VerifiedRouteID
		
		WHILE @@FETCH_STATUS = 0
		BEGIN
		    SET @LicenceDescription = NULL
			SET @ApplicationLicenceRowNumber = @LicenceCount + 1
			SET @ApplicationLicenceReferenceNumber = @ApplicationNumber + '-' + CAST(@ApplicationLicenceRowNumber AS VARCHAR(20))

			SET @ApplicationLicenceID = NULL
			SET @OperatingLicenceNumber = 'AMEND AUTHORITY ' + System.Fn_PadLeft('0', 3, @LicenceCount + 1)
			SET @LicenceDescription = @ApplicationRequestType + ' for association  ' + @ApplicantName
		
			INSERT INTO App.ApplicationLicences(ApplicationDetailID, OperatingLicenceNumber, VehicleTypeID, ApplicationLicenceReferenceNumber, CurrentVerifiedLicenceID, IsExistingLicence, ApplicationLicenceRequestTypeID, SplitIntoNewApplication, ApplicationLicenceDescription, IsCancelled)
			VALUES(@ApplicationDetailID, @OperatingLicenceNumber, @VehicleTypeID, @ApplicationLicenceReferenceNumber,NULL, 0, @ApplicationRequestTypeID /*new ol*/, 0, @LicenceDescription, 0)
		
			SELECT @ApplicationLicenceID = SCOPE_IDENTITY()
			
			/*************************************************
			Create the service details 
			*************************************************/ 
			INSERT INTO app.ApplicationLicenceServiceDetails(ApplicationLicenceID,ServiceTypeID,ServiceTypeDescription)
			SELECT @ApplicationLicenceID, 2 /*Unscheduled service*/, NULL	

			/*************************************************
			Create a licence route record
			*************************************************/ 
			INSERT INTO App.ApplicationLicenceRouteDetails(ApplicationLicenceID, ServiceTypeID,ApplicationRouteDetailStatusID,VerifiedRouteID,NewRouteRequestDetailID,RouteOrigin,RouteDestination,RouteDescription,NationalRouteNumber,BoardRouteNumber,RouteLength,TimeTableDocumentID,FareScheduleDocumentID,RouteNumber,RouteChangeTypeID,IsExistingLtpsOrOlasRoute)
			SELECT @ApplicationLicenceID, 2 /*Unscheduled service*/, 1 /*New*/, RouteID, NULL, CAST(OriginPlaceID AS VARCHAR(30)), CAST(R.DestinationPlaceID AS VARCHAR(30)), R.Description, r.NationalRouteNumber, R.BoardRouteNumber, R.RouteLength, null, null, 1, 7 /*Amend authority/association route*/, 0
			FROM Verified.Routes R
			LEFT JOIN Verified.Places OP ON OP.PlaceID = R.OriginPlaceID
			LEFT JOIN Verified.Places DP ON DP.PlaceID = R.DestinationPlaceID
			WHERE RouteID = @VerifiedRouteID
	
			/*************************************************
			Create a blank vehicle details record
			*************************************************/ 
			INSERT INTO App.ApplicationLicenceVehicleDetails(ApplicationLicenceID, IsStillToBeAcquired)
			VALUES(@ApplicationLicenceID, 1)

			INSERT INTO App.ApplicationLicencePreviousVehicleDetails(ApplicationLicenceID)
			VALUES(@ApplicationLicenceID)				
			
			SET @LicenceCount = @LicenceCount + 1;
			
		    FETCH NEXT FROM Amend_RouteChanges INTO @VerifiedRouteID
		END
		
		CLOSE Amend_RouteChanges
		DEALLOCATE Amend_RouteChanges
		
	END
	ELSE
	BEGIN
		SET @LicenceCount = 0
		DECLARE newLicenceCursor CURSOR FAST_FORWARD READ_ONLY FOR 
			SELECT OperatingLicenceOrPermitNumber, SourceApplicationXML
			FROM App.ApplicationPrecheckLicenceDetails
			WHERE ApplicationPrecheckDetailID = @ApplicationPrecheckDetailID
		OPEN newLicenceCursor
		
		FETCH NEXT FROM newLicenceCursor INTO @OperatingLicenceNumber, @SourceApplicationLicenceXML
		
		WHILE @@FETCH_STATUS = 0
		BEGIN		    
			SET @LicenceVehicleTypeID = NULL
			SET @ApplicationLicenceRowNumber = @LicenceCount + 1
			SET @ApplicationLicenceReferenceNumber = @ApplicationNumber + '-' + CAST(@ApplicationLicenceRowNumber AS VARCHAR(20))
			SET @ApplicationLicenceID = NULL		
			SET @OperatingLicenceNumber = App.Fn_StripVersionNumber(@OperatingLicenceNumber,'/')

			IF @SourceApplicationLicenceXML IS NOT NULL
			BEGIN				
				SET @LicenceDescription = NULL
				IF @ApplicationRequestTypeID = 1 --NEW
				BEGIN
					SET @LicenceDescription = @ApplicationRequestType + ' for member ' + @ApplicantName
				END
				ELSE
				BEGIN
					SET @LicenceDescription = @ApplicationRequestType + ' for licence ' + @OperatingLicenceNumber
				END
				

				INSERT INTO App.ApplicationLicences(ApplicationDetailID , VehicleTypeID, CurrentVehicleTypeID , OperatingLicenceNumber, LicencingBoardID, OperatingLicenceIssueDate, OperatingLicenceExpiryDate, CurrentVerifiedVehicleID, ApplicationLicenceReferenceNumber, CurrentVerifiedLicenceID,	IsExistingLicence,	ApplicationLicenceRequestTypeID,	SplitIntoNewApplication, ApplicationLicenceDescription,	IsCancelled)
				SELECT @ApplicationDetailID, VehicleTypeID, CASE WHEN @IncludeIncreaseInCapacityType = 1 THEN VehicleTypeID ELSE NULL END, OperatingLicenceNumber, LicencingBoardID, OperatingLicenceIssueDate, OperatingLicenceExpiryDate, CurrentVerifiedVehicleID, @ApplicationLicenceReferenceNumber,VerifiedLicenceID,			1,					@ApplicationRequestTypeID,			0, @LicenceDescription,							0
				FROM Imported.Fn_SourceApplicationXmlLicenceGet(@SourceApplicationLicenceXML, @SourceApplicationID)
		
				SELECT @ApplicationLicenceID = SCOPE_IDENTITY()
				SET @LicenceCount = @LicenceCount + 1				
				
				/*************************************************
				Create the route details 
				*************************************************/ 
				IF @ApplicationRequestTypeID <> 11 /*Temporary (Special) licence*/
				BEGIN
					DECLARE @Route_ServiceTypeID SMALLINT
					DECLARE @Route_VerifiedRouteID BIGINT
					DECLARE @Route_RouteName VARCHAR(100)
					DECLARE @Route_Description VARCHAR(MAX)
					DECLARE @Route_OriginPlaceID BIGINT
					DECLARE @Route_OriginPlace VARCHAR(100)
					DECLARE @Route_OriginPlaceXml XML
					DECLARE @Route_DestinationPlaceID BIGINT
					DECLARE @Route_DestinationPlace VARCHAR(100)
					DECLARE @Route_DestinationPlaceXml XML
					DECLARE @Route_NationalRouteNumber VARCHAR(100)
					DECLARE @Route_BoardRouteNumber VARCHAR(100)
					DECLARE @Route_RouteLength FLOAT
					DECLARE @Route_RouteID BIGINT
					DECLARE @Route_OlasID BIGINT
					DECLARE @Route_LtpsID BIGINT
					DECLARE @Route_NeoID BIGINT
					
					DECLARE Cursor_Route CURSOR FAST_FORWARD READ_ONLY FOR 
					SELECT ServiceTypeID, VerifiedRouteID , RouteName, RouteDescription, RouteOriginID, RouteOriginXml, RouteDestinationID, RouteDestinationXml, NationalRouteNumber, BoardRouteNumber, RouteLength, RouteID, RouteOrigin, RouteDestination
					FROM Imported.Fn_SourceApplicationXmlRoutesGet(@SourceApplicationXML, @SourceApplicationID)						
					OPEN Cursor_Route					
					FETCH NEXT FROM Cursor_Route INTO @Route_ServiceTypeID, @Route_VerifiedRouteID, @Route_RouteName, @Route_Description, @Route_OriginPlaceID, @Route_OriginPlaceXml, @Route_DestinationPlaceID, @Route_DestinationPlaceXml, @Route_NationalRouteNumber, @Route_BoardRouteNumber, @Route_RouteLength, @Route_RouteID, @Route_OriginPlace, @Route_DestinationPlace					
					WHILE @@FETCH_STATUS = 0
					BEGIN
					    /***********If route description is a condition******************************************/
						/***********Save condition to License***************************************************/
						/***********Else Created Verified Route and App.License Routes**************************/
					    IF(ISNULL(@Route_RouteName,'') LIKE '%Conditions%' OR ISNULL(@Route_Description,'') LIKE '%Conditions%') 
						BEGIN
							UPDATE App.ApplicationLicences
							SET LicenseSpecificConditions = @Route_Description
							WHERE ApplicationLicenceID = @ApplicationLicenceID
						END
						ELSE BEGIN
							/*Search for existing Verified Route*/
							IF EXISTS(SELECT * FROM Verified.Routes WHERE NationalRouteNumber = @Route_NationalRouteNumber)
							BEGIN
								 SELECT @Route_VerifiedRouteID = RouteID
								 FROM Verified.Routes WHERE NationalRouteNumber = @Route_NationalRouteNumber
							END
							ELSE IF(@Route_VerifiedRouteID IS NULL) 
							BEGIN
								/*If route cannot be found, create the verified route*/
								/*********************************************/
								/*********Determine Route Source Origan*******/
								/*********************************************/

								SET @Route_OlasID = NULL
								SET @Route_LtpsID = NULL
								SET @Route_NeoID = @Route_RouteID


								/************************************************/
								/*********Create/Calculate Verified Places*******/
								/************************************************/
								DECLARE @ImportPlaceID BIGINT = NULL							
								DECLARE @PlaceDescription VARCHAR(255) = NULL
								DECLARE @PlacetTypeID BIGINT = NULL

					
								/************************************************/
								/******************Create Route******************/
								/************************************************/
								INSERT INTO Verified.Routes(RouteTypeID, RouteName, Description, OriginPlaceID, DestinationPlaceID, NationalRouteNumber, BoardRouteNumber, RouteLength, RouteStatusID, OlasID, LtpsID, NeoID,ProvinceID, TimeTableDocumentID, FareScheduleDocumentID, IsChecked, IsPotentialIncorrectRouteChange, IsPotentialIncorrectRouteChangeProbability)
								VALUES(1 /*STANDARD ROUTE*/,@Route_RouteName, @Route_Description, @Route_OriginPlaceID, @Route_DestinationPlaceID, @Route_NationalRouteNumber, @Route_BoardRouteNumber, @Route_RouteLength, 1 /*Active*/, @Route_OlasID, @Route_LtpsID, @Route_NeoID, @ProvinceID, NULL, NULL, 1, 0, 0 )

								SELECT @Route_VerifiedRouteID = SCOPE_IDENTITY()
							END

							IF (@Route_VerifiedRouteID IS NOT NULL)
							BEGIN
								INSERT INTO App.ApplicationLicenceRouteDetails(ApplicationLicenceID, ServiceTypeID,ApplicationRouteDetailStatusID,VerifiedRouteID,NewRouteRequestDetailID,RouteOrigin,RouteDestination,RouteDescription,NationalRouteNumber,BoardRouteNumber,RouteLength,TimeTableDocumentID,FareScheduleDocumentID,RouteNumber,RouteChangeTypeID,IsExistingLtpsOrOlasRoute)
								SELECT @ApplicationLicenceID, @Route_ServiceTypeID, 3 /*verified*/, R.RouteID, NULL, ISNULL(Origin.Description,'N/A'), ISNULL(Destination.Description, 'N/A'), R.Description, NationalRouteNumber, BoardRouteNumber, RouteLength, TimeTableDocumentID, FareScheduleDocumentID, 1, 1 /*Existing route on licence*/, 1
								FROM Verified.Routes R								
									LEFT JOIN Verified.Places Origin ON R.OriginPlaceID = Origin.PlaceID
									LEFT JOIN Verified.Places Destination ON R.DestinationPlaceID = Destination.PlaceID
								WHERE R.RouteID = @Route_VerifiedRouteID
							END
						END
					
					    FETCH NEXT FROM Cursor_Route INTO @Route_ServiceTypeID, @Route_VerifiedRouteID, @Route_RouteName, @Route_Description, @Route_OriginPlaceID, @Route_OriginPlaceXml, @Route_DestinationPlaceID, @Route_DestinationPlaceXml, @Route_NationalRouteNumber, @Route_BoardRouteNumber, @Route_RouteLength, @Route_RouteID, @Route_OriginPlace, @Route_DestinationPlace
					END
					
					CLOSE Cursor_Route
					DEALLOCATE Cursor_Route					
				END	
				ELSE IF @ApplicationRequestTypeID = 11 /*Temporary (Special) licence*/
				BEGIN
						INSERT INTO App.ApplicationLicenceRouteDetails(ApplicationLicenceID, ServiceTypeID,ApplicationRouteDetailStatusID,VerifiedRouteID,NewRouteRequestDetailID,RouteOrigin,RouteDestination,RouteDescription,NationalRouteNumber,BoardRouteNumber,RouteLength,TimeTableDocumentID,FareScheduleDocumentID,RouteNumber,RouteChangeTypeID,IsExistingLtpsOrOlasRoute)
						SELECT @ApplicationLicenceID, 2 /*Unscheduled service*/, 1 /*New*/, NULL, NULL, '', '', '', NULL, NULL, NULL, NULL, NULL, NULL, NULL , 0						
                END
                
				/*************************************************
				Create the service details 
				*************************************************/ 
				IF @ApplicationRequestTypeID <> 11 /*Temporary (Special) licence*/
				BEGIN
					INSERT INTO App.ApplicationLicenceServiceDetails(ApplicationLicenceID,ServiceTypeID,ServiceTypeDescription)
					SELECT @ApplicationLicenceID, ServiceTypeID, ServiceTypeDescription
					FROM Imported.Fn_SourceApplicationXmlServiceGet(@SourceApplicationLicenceXML, @SourceApplicationID)
					WHERE ServiceTypeID IS NOT NULL                    
				END
				ELSE BEGIN
					INSERT INTO App.ApplicationLicenceServiceDetails(ApplicationLicenceID,ServiceTypeID,ServiceTypeDescription)
					SELECT @ApplicationLicenceID, 12, 'Temporary service'
				END
                								
				/*************************************************
				Create the vehicle details record
				*************************************************/ 
				IF 
					@IncludeCovType = 0 
					AND @IncludeIncreaseInCapacityType = 0
					AND @IncludeNewOperatingLicenceType = 0
				BEGIN
					INSERT INTO App.ApplicationLicenceVehicleDetails(ApplicationLicenceID, IsStillToBeAcquired, VehicleMakeID, VehicleMakeDescription, VehicleTypeID, VIN, EngineNumber, YearOfManufacture, VehicleRegistrationNumber, SeatedCapacity, StandingCapacity, GrossMass, VerifiedVehicleID, CarryingCapacityID, CurrentOdometerReading, Tare, RoadworthyCertificateDocID, ProfessionalDriversPermitDocID, RankPermitDocID, CertificateOfRegistrationDocID, VehicleModelID)
					SELECT @ApplicationLicenceID, 0, VehicleMakeID, VehicleMakeDescription, VehicleTypeID, VIN, EngineNumber, YearOfManufacture, VehicleRegistrationNumber, SeatedCapacity, StandingCapacity, GrossMass, VerifiedVehicleID, CarryingCapacityID, CurrentOdometerReading, Tare, RoadworthyCertificateDocID, ProfessionalDriversPermitDocID, RankPermitDocID, CertificateOfRegistrationDocID, VehicleModelID
					FROM Imported.Fn_SourceApplicationXmlVehicleGet(@SourceApplicationLicenceXML, @SourceApplicationID)

					IF NOT EXISTS(SELECT * FROM App.ApplicationLicenceVehicleDetails WHERE ApplicationLicenceID = @ApplicationLicenceID)
					BEGIN
						INSERT INTO App.ApplicationLicenceVehicleDetails(ApplicationLicenceID, IsStillToBeAcquired)
						VALUES(@ApplicationLicenceID, 0)
					END

					INSERT INTO App.ApplicationLicencePreviousVehicleDetails(ApplicationLicenceID)
					VALUES(@ApplicationLicenceID)

				END
				ELSE IF @ApplicationRequestTypeID = 9 /*New Operating Licence (pre-populate with expired licence data)*/
				BEGIN
					INSERT INTO App.ApplicationLicenceVehicleDetails(ApplicationLicenceID, IsStillToBeAcquired, VehicleMakeID, VehicleMakeDescription, VehicleTypeID, VIN, EngineNumber, YearOfManufacture, VehicleRegistrationNumber, SeatedCapacity, StandingCapacity, GrossMass, VerifiedVehicleID, CarryingCapacityID, CurrentOdometerReading, Tare, RoadworthyCertificateDocID, ProfessionalDriversPermitDocID, RankPermitDocID, CertificateOfRegistrationDocID, VehicleModelID)
					SELECT @ApplicationLicenceID, 0, VehicleMakeID, VehicleMakeDescription, VehicleTypeID, VIN, EngineNumber, YearOfManufacture, VehicleRegistrationNumber, SeatedCapacity, StandingCapacity, GrossMass, VerifiedVehicleID, CarryingCapacityID, CurrentOdometerReading, Tare, RoadworthyCertificateDocID, ProfessionalDriversPermitDocID, RankPermitDocID, CertificateOfRegistrationDocID, VehicleModelID
					FROM Imported.Fn_SourceApplicationXmlVehicleGet(@SourceApplicationLicenceXML, @SourceApplicationID)

					IF NOT EXISTS(SELECT * FROM App.ApplicationLicenceVehicleDetails WHERE ApplicationLicenceID = @ApplicationLicenceID)
					BEGIN
						INSERT INTO App.ApplicationLicenceVehicleDetails(ApplicationLicenceID, IsStillToBeAcquired)
						VALUES(@ApplicationLicenceID, 0)
					END

					INSERT INTO App.ApplicationLicencePreviousVehicleDetails(ApplicationLicenceID, VehicleMakeID, VehicleMakeDescription, VehicleTypeID, VIN, EngineNumber, YearOfManufacture, VehicleRegistrationNumber, SeatedCapacity, StandingCapacity, GrossMass, VerifiedVehicleID, CarryingCapacityID, CurrentOdometerReading, Tare, RoadworthyCertificateDocID, ProfessionalDriversPermitDocID, RankPermitDocID, CertificateOfRegistrationDocID, VehicleModelID)
					SELECT @ApplicationLicenceID, VehicleMakeID, VehicleMakeDescription, VehicleTypeID, VIN, EngineNumber, YearOfManufacture, VehicleRegistrationNumber, SeatedCapacity, StandingCapacity, GrossMass, VerifiedVehicleID, CarryingCapacityID, CurrentOdometerReading, Tare, RoadworthyCertificateDocID, ProfessionalDriversPermitDocID, RankPermitDocID, CertificateOfRegistrationDocID, VehicleModelID
					FROM Imported.Fn_SourceApplicationXmlVehicleGet(@SourceApplicationLicenceXML, @SourceApplicationID)

					IF NOT EXISTS(SELECT * FROM App.ApplicationLicencePreviousVehicleDetails WHERE ApplicationLicenceID = @ApplicationLicenceID)
					BEGIN
						INSERT INTO App.ApplicationLicencePreviousVehicleDetails(ApplicationLicenceID)
						VALUES(@ApplicationLicenceID)
					END
                END
                ELSE BEGIN
					/*************************************************
					Create a blank vehicle details record
					*************************************************/ 
					INSERT INTO App.ApplicationLicenceVehicleDetails(ApplicationLicenceID, IsStillToBeAcquired)
					VALUES(@ApplicationLicenceID, 0)

					INSERT INTO App.ApplicationLicencePreviousVehicleDetails(ApplicationLicenceID, VehicleMakeID, VehicleMakeDescription, VehicleTypeID, VIN, EngineNumber, YearOfManufacture, VehicleRegistrationNumber, SeatedCapacity, StandingCapacity, GrossMass, VerifiedVehicleID, CarryingCapacityID, CurrentOdometerReading, Tare, RoadworthyCertificateDocID, ProfessionalDriversPermitDocID, RankPermitDocID, CertificateOfRegistrationDocID, VehicleModelID)
					SELECT @ApplicationLicenceID, VehicleMakeID, VehicleMakeDescription, VehicleTypeID, VIN, EngineNumber, YearOfManufacture, VehicleRegistrationNumber, SeatedCapacity, StandingCapacity, GrossMass, VerifiedVehicleID, CarryingCapacityID, CurrentOdometerReading, Tare, RoadworthyCertificateDocID, ProfessionalDriversPermitDocID, RankPermitDocID, CertificateOfRegistrationDocID, VehicleModelID
					FROM Imported.Fn_SourceApplicationXmlVehicleGet(@SourceApplicationLicenceXML, @SourceApplicationID)

					IF NOT EXISTS(SELECT * FROM App.ApplicationLicencePreviousVehicleDetails WHERE ApplicationLicenceID = @ApplicationLicenceID)
					BEGIN
						INSERT INTO App.ApplicationLicencePreviousVehicleDetails(ApplicationLicenceID)
						VALUES(@ApplicationLicenceID)
					END
				END 
				
				/*****************************************************************/
				/**********************Set ApplicationLicence Vehicle Type Value**/
				/*****************************************************************/
				SET @LicenceVehicleTypeID = ISNULL(
													(
														SELECT TOP 1 VehicleTypeID FROM App.ApplicationLicenceVehicleDetails WHERE ApplicationLicenceID = @ApplicationLicenceID
													),
													(
														SELECT TOP 1 VehicleTypeID FROM App.ApplicationLicencePreviousVehicleDetails WHERE ApplicationLicenceID = @ApplicationLicenceID
													)
											      )

				UPDATE App.ApplicationLicences
				SET VehicleTypeID = @LicenceVehicleTypeID
				WHERE ApplicationLicenceID	= @ApplicationLicenceID
				AND ApplicationDetailID = @ApplicationDetailID
				AND VehicleTypeID IS NULL
			END
			            					
		    FETCH NEXT FROM newLicenceCursor INTO @OperatingLicenceNumber, @SourceApplicationLicenceXML
		END
		
		CLOSE newLicenceCursor
		DEALLOCATE newLicenceCursor		
	END

	/*************************************************
    Create the proxy record
	*************************************************/ 
	INSERT INTO App.ApplicationProxyDetails(ApplicationDetailID, RequiresProxy)
	VALUES(@ApplicationDetailID,CASE WHEN @IdTypeID IN (1, 2, 4, 5) THEN 0 ELSE 1 END)

	/*************************************************
    Set the IsBusAndContract value
	*************************************************/ 
	UPDATE App.ApplicationDetails
	SET 
		IsBusAndContract = App.Fn_CheckIfApplicationIsBusAndContractServiceType(@ApplicationDetailID)
	WHERE ApplicationDetailID  = @ApplicationDetailID 	

	/*************************************************
    Start the actual workflow
	*************************************************/ 
	DECLARE @ErrorMessage VARCHAR(MAX)
	DECLARE @Username VARCHAR(255) = Security.Fn_GetUserNameByID(@UserID)
	EXEC Wf.Pr_WorkflowInstanceStart 1 /*application workflow*/, 1000 /*ApplicationDetails*/, @ApplicationDetailID, NULL, @Username, @ApplicationNumber, @ErrorMessage OUTPUT

	/**********************************
	Populate previous documents
	**********************************/
	IF ISNULL(@VerifiedMemberID,-1) <> -1
	BEGIN
		DECLARE @WorkflowInstanceID BIGINT

		SELECT @WorkflowInstanceID = WorkflowInstanceID
		FROM Wf.WorkflowInstances
		WHERE EntityID = 1000 /*ApplicationDetails*/
			AND ItemID = @ApplicationDetailID

		EXEC Wf.Pr_WorkflowInstanceDocumentsUpdate @WorkflowInstanceID, @UserID
				
		UPDATE Wf.WorkflowInstanceDocuments
		SET	DocumentID = (SELECT DocumentID FROM Doc.Documents D WHERE D.DocumentTypeID = Wid.DocumentTypeID AND D.EntityID = 1009 /*members*/ AND D.ItemID = @VerifiedMemberID)
        FROM Wf.WorkflowInstanceDocuments Wid
		INNER JOIN Doc.DocumentTypes DT ON DT.DocumentTypeID = Wid.DocumentTypeID
		WHERE WorkflowInstanceID = @WorkflowInstanceID
			AND DT.IsUniqueEntityDocument = 1 /*TRUE*/
	END
    
	/**********************************
	Record Performance Measure
	**********************************/
	EXEC Performance.Pr_PerformanceHistoryCreate 1000/*Application created*/, @UserID, 1000 /*ApplicationDetails*/, @ApplicationDetailID, 1 /*AllowDuplicate*/, @UserID

	EXEC [App].[Pr_ApplicationRequiredRulesCalculate] @ApplicationDetailID, @UserID

	/*************************************************
    Return application details
	*************************************************/ 
	SELECT *
	FROM App.Vw_ApplicationDetails_Light
	WHERE ApplicationDetailID = @ApplicationDetailID
	
END
GO

CREATE OR ALTER PROCEDURE [App].[Pr_ApplicationSplitFromParentApp]
	@ParentApplicationDetailID BIGINT,
	@ParentApplicationLicenceID BIGINT,
	@ReasonForSplit VARCHAR(2000),
	@MoveToAdjudicationQueue BIT,
	@RelatedTATApplicationRequestID 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) = '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	
	
	DECLARE @ApplicationDetailID BIGINT
	DECLARE @ApplicationRequestTypeID SMALLINT
	DECLARE @AuditComment VARCHAR(2000) = 'Splitting application from parent: ' + @ReasonForSplit
	DECLARE @SystemEmail VARCHAR(100) = System.Fn_GetSettingValue('SystemEmail')
    DECLARE @SystemUserID BIGINT = CAST(System.Fn_GetSettingValue('SystemUserID') AS BIGINT)

	/*************************************************
    Get the application number i.e. The original licence app number 
	i.e. APP000000-1 is now used as the primary app number as well
	*************************************************/
	DECLARE @ApplicationNumber VARCHAR(20)

	SELECT @ApplicationNumber = Al.ApplicationLicenceReferenceNumber
	FROM App.ApplicationDetails Ad
		INNER JOIN App.ApplicationLicences Al ON Al.ApplicationDetailID = Ad.ApplicationDetailID
	WHERE Al.ApplicationLicenceID = @ParentApplicationLicenceID

	/*************************************************
    Create the base application record
	*************************************************/
	SELECT @ApplicationRequestTypeID = ApplicationLicenceRequestTypeID
	FROM App.ApplicationLicences INNER JOIN App.ApplicationDetails
	ON ApplicationLicences.ApplicationDetailID = ApplicationDetails.ApplicationDetailID
	WHERE ApplicationLicenceID = @ParentApplicationLicenceID

	INSERT INTO App.ApplicationDetails(ApplicationNumber, AccreditationID, ApplicationRequestTypeID, SourceApplicationID, OfficeLocationID, OfficeLocationDivisionTypeID, ApplicationProvinceID, ApplicationRegionID, CreatedByID, CreatedDate, UpdatedByID, UpdatedDate, IsBulkApplication, LicenceCount, AdjudicationOfficeLocationID, ApplicationStatusID, ApplicationSubStatusID, GazetteTypeID, CopiedFromApplicationDetailID, CopiedFromApplicationLicenceID, RelatedTATApplicationRequestID)
	SELECT @ApplicationNumber, AccreditationID, @ApplicationRequestTypeID, SourceApplicationID, OfficeLocationID, OfficeLocationDivisionTypeID, ApplicationProvinceID, ApplicationRegionID, CreatedByID, CreatedDate, UpdatedByID, UpdatedDate, 0, 1, AdjudicationOfficeLocationID, ApplicationStatusID, ApplicationSubStatusID, GazetteTypeID, @ParentApplicationDetailID, @ParentApplicationLicenceID, @RelatedTATApplicationRequestID
	FROM App.ApplicationDetails
	WHERE ApplicationDetailID = @ParentApplicationDetailID

	SET @ApplicationDetailID = SCOPE_IDENTITY()
	
	/*************************************************
    Update the parent application records
	*************************************************/
	UPDATE App.ApplicationLicences
	SET
		SplitIntoNewApplication = 1,
		NewApplicationDetailID = @ApplicationDetailID
	WHERE ApplicationLicenceID = @ParentApplicationLicenceID

	UPDATE App.ApplicationDetails
	SET
		LicenceCount = (SELECT COUNT(*) FROM App.ApplicationLicences WHERE ApplicationDetailID = @ParentApplicationDetailID AND SplitIntoNewApplication = 0)
	WHERE ApplicationDetailID = @ParentApplicationDetailID

	/*************************************************
    Create the applicant record 
	*************************************************/
	INSERT INTO App.ApplicationApplicantDetails(ApplicationDetailID, SurnameOrCompanyName, FirstNames, IDTypeID, IDTypeDescription, IDNumberOrBusinessRegistrationNo, PostalAddress, PostalAddressCode, PhysicalAddress, PhysicalAddressCode, TelPhone, TelCell, TelFax, Email, SARSIncomeTaxNumber, TradeName, TypeOfBusiness, BusinessTypeID, VerifiedMemberID)
	SELECT @ApplicationDetailID, SurnameOrCompanyName, FirstNames, IDTypeID, IDTypeDescription, IDNumberOrBusinessRegistrationNo, PostalAddress, PostalAddressCode, PhysicalAddress, PhysicalAddressCode, TelPhone, TelCell, TelFax, Email, SARSIncomeTaxNumber, TradeName, TypeOfBusiness, BusinessTypeID, VerifiedMemberID
	FROM App.ApplicationApplicantDetails
	WHERE ApplicationDetailID = @ParentApplicationDetailID
    
	/*************************************************
    Create the contract record
	*************************************************/ 
	INSERT INTO App.ApplicationContractDetails(ApplicationDetailID, ContractTypeID, ContractReferenceNumber, NameOfParties1, AddressOfParties1, AddressCodeOfParties1, NameOfParties2, AddressOfParties2, AddressCodeOfParties2, NameOfSubContractor, AddressOfSubContractor, AddressCodeOfSubContractor, ContractFromDate, ContractToDate)
	SELECT @ApplicationDetailID, ContractTypeID, ContractReferenceNumber, NameOfParties1, AddressOfParties1, AddressCodeOfParties1, NameOfParties2, AddressOfParties2, AddressCodeOfParties2, NameOfSubContractor, AddressOfSubContractor, AddressCodeOfSubContractor, ContractFromDate, ContractToDate
	FROM App.ApplicationContractDetails
	WHERE ApplicationDetailID = @ParentApplicationDetailID

	/*************************************************
    Create the previous licence holder record
	*************************************************/ 
	INSERT INTO App.ApplicationPreviousLicenceHolder(ApplicationDetailID, SurnameOrCompanyName, FirstNames, IDTypeID, IDTypeDescription, IDNumberOrBusinessRegistrationNo, PostalAddress, PostalAddressCode, PhysicalAddress, PhysicalAddressCode, TelPhone, TelCell, TelFax, Email, SARSIncomeTaxNumber, TradeName, TypeOfBusiness)
	SELECT @ApplicationDetailID, SurnameOrCompanyName, FirstNames, IDTypeID, IDTypeDescription, IDNumberOrBusinessRegistrationNo, PostalAddress, PostalAddressCode, PhysicalAddress, PhysicalAddressCode, TelPhone, TelCell, TelFax, Email, SARSIncomeTaxNumber, TradeName, TypeOfBusiness
	FROM App.ApplicationPreviousLicenceHolder
	WHERE ApplicationDetailID = @ParentApplicationDetailID

	/*************************************************
    Create the NPTR record
	*************************************************/ 
	INSERT INTO App.ApplicationNPTRDetails(ApplicationDetailID, TotalMotorcar, MotorcarCapacity, TotalMinibus, MinibusCapacity, TotalMidibus, MidibusCapacity, TotalBus, BusCapacity, TotalOther, OtherCapacity, TotalVehicles, TotalVehiclesToBePurchased, IsMaintenanceProgrammeInPlace, MaintenanceProgrammeDetail, IsVehiclesServicedAtGarage, GarageName, ServiceAddress, LiveryDetails, LiveryDocumentID)
	SELECT @ApplicationDetailID, TotalMotorcar, MotorcarCapacity, TotalMinibus, MinibusCapacity, TotalMidibus, MidibusCapacity, TotalBus, BusCapacity, TotalOther, OtherCapacity, TotalVehicles, TotalVehiclesToBePurchased, IsMaintenanceProgrammeInPlace, MaintenanceProgrammeDetail, IsVehiclesServicedAtGarage, GarageName, ServiceAddress, LiveryDetails, LiveryDocumentID
	FROM App.ApplicationNPTRDetails
	WHERE ApplicationDetailID = @ParentApplicationDetailID

	/*************************************************
    Create the inspection record
	*************************************************/ 
	INSERT INTO App.ApplicationInspectionDetails(ApplicationDetailID, InspectionStatusID, InspectorID, InspectionDateFrom, InspectionDateTo, InspectionAddress, InspectionPassed, InspectionResultDocID, InspectionBackToOfficeDocID)
	SELECT @ApplicationDetailID, InspectionStatusID, InspectorID, InspectionDateFrom, InspectionDateTo, InspectionAddress, InspectionPassed, InspectionResultDocID, InspectionBackToOfficeDocID
	FROM App.ApplicationInspectionDetails
	WHERE ApplicationDetailID = @ParentApplicationDetailID

	/*************************************************
    Create the referral records
	*************************************************/ 
	INSERT INTO App.ApplicationReferrals(ApplicationDetailID, ReferralTypeID, DateReferralSent, DateFeedbackReceived, FeedbackDocID, IsCancelled, ReferralSentTo, ReferralAddress)
	SELECT @ApplicationDetailID, ReferralTypeID, DateReferralSent, DateFeedbackReceived, FeedbackDocID, IsCancelled, ReferralSentTo, ReferralAddress
	FROM App.ApplicationReferrals
	WHERE ApplicationDetailID = @ParentApplicationDetailID

	/*************************************************
    Create the TAT record
	*************************************************/ 
	INSERT INTO App.ApplicationTATDetails(ApplicationDetailID)
	SELECT @ApplicationDetailID

	/*************************************************
    Create the required licence record
	*************************************************/ 
	DECLARE @ApplicationLicenceID BIGINT

	INSERT INTO App.ApplicationLicences(ApplicationDetailID, ApplicationLicenceRequestTypeID, ApplicationLicenceReferenceNumber, OperatingLicenceNumber, LicencingBoardID, OperatingLicenceIssueDate, OperatingLicenceExpiryDate, ReasonForAmendment, SpecialLicenceDetails, SpecialLicenceDateFrom, SpecialLicenceDateTo, LongDistanceReason, ApplicationFormDocID, OperatingLicenceDocID, VehicleTypeID, CurrentVerifiedLicenceID, IsExistingLicence, CurrentVehicleTypeID, CurrentVerifiedVehicleID, IsAdjudicationFinalised, ApplicationAdjudicationID)
	SELECT @ApplicationDetailID, ApplicationLicenceRequestTypeID, ApplicationLicenceReferenceNumber, OperatingLicenceNumber, LicencingBoardID, OperatingLicenceIssueDate, OperatingLicenceExpiryDate, ReasonForAmendment, SpecialLicenceDetails, SpecialLicenceDateFrom, SpecialLicenceDateTo, LongDistanceReason, ApplicationFormDocID, OperatingLicenceDocID, VehicleTypeID, CurrentVerifiedLicenceID, IsExistingLicence, CurrentVehicleTypeID, CurrentVerifiedVehicleID, IsAdjudicationFinalised, ApplicationAdjudicationID
	FROM App.ApplicationLicences
	WHERE ApplicationLicenceID = @ParentApplicationLicenceID

	SELECT @ApplicationLicenceID = SCOPE_IDENTITY()
		
	/*************************************************
    Create the licence previous vehicle details
	*************************************************/ 
	INSERT INTO App.ApplicationLicencePreviousVehicleDetails(ApplicationLicenceID, VehicleMakeID, VehicleMakeDescription, VehicleTypeID, VIN, EngineNumber, YearOfManufacture, VehicleRegistrationNumber, SeatedCapacity, StandingCapacity, RoadworthyCertificateDocID, GrossMass, VerifiedVehicleID, CarryingCapacityID)
	SELECT @ApplicationLicenceID, VehicleMakeID, VehicleMakeDescription, VehicleTypeID, VIN, EngineNumber, YearOfManufacture, VehicleRegistrationNumber, SeatedCapacity, StandingCapacity, RoadworthyCertificateDocID, GrossMass, VerifiedVehicleID, CarryingCapacityID
	FROM App.ApplicationLicencePreviousVehicleDetails
	WHERE ApplicationLicenceID = @ParentApplicationLicenceID

	/*************************************************
    Create the licence route details
	*************************************************/ 
	INSERT INTO App.ApplicationLicenceRouteDetails(ApplicationLicenceID, ApplicationRouteDetailStatusID, ServiceTypeID, VerifiedRouteID, NewRouteRequestDetailID, RouteOrigin, RouteDestination, RouteDescription, NationalRouteNumber, BoardRouteNumber, RouteLength, TimeTableDocumentID, FareScheduleDocumentID, RouteNumber, RouteChangeTypeID, IsExistingLtpsOrOlasRoute, IsOriginSameAsPhysicalAddress, IsDestinationArea, RelatedApplicationLicenceRouteDetailID)
	SELECT @ApplicationLicenceID, ApplicationRouteDetailStatusID, ServiceTypeID, VerifiedRouteID, NewRouteRequestDetailID, RouteOrigin, RouteDestination, RouteDescription, NationalRouteNumber, BoardRouteNumber, RouteLength, TimeTableDocumentID, FareScheduleDocumentID, RouteNumber, RouteChangeTypeID, IsExistingLtpsOrOlasRoute, IsOriginSameAsPhysicalAddress, IsDestinationArea, ApplicationLicenceRouteDetailID
	FROM App.ApplicationLicenceRouteDetails
	WHERE ApplicationLicenceID = @ParentApplicationLicenceID

	INSERT INTO App.ApplicationLicenceRouteDetailPlaces(ApplicationLicenceRouteDetailID, PlaceID)
	SELECT 
		(
			SELECT ApplicationLicenceRouteDetailID 
			FROM App.ApplicationLicenceRouteDetails NewRoutes 
			WHERE NewRoutes.ApplicationLicenceID = @ApplicationLicenceID 
				AND NewRoutes.RelatedApplicationLicenceRouteDetailID = OldPlaces.ApplicationLicenceRouteDetailID
		), OldPlaces.PlaceID
	FROM App.ApplicationLicenceRouteDetailPlaces OldPlaces
	WHERE OldPlaces.ApplicationLicenceRouteDetailID IN
	(
		SELECT ApplicationLicenceRouteDetailID
		FROM App.ApplicationLicenceRouteDetails OldRoutes
		WHERE ApplicationLicenceID = @ParentApplicationLicenceID
	)
	
	UPDATE App.ApplicationLicenceRouteDetails
	SET
		RelatedApplicationLicenceRouteDetailID = NULL
	WHERE ApplicationLicenceID = @ApplicationLicenceID

	/*************************************************
    Create the service details 
	*************************************************/ 
	INSERT INTO App.ApplicationLicenceServiceDetails(ApplicationLicenceID, ServiceTypeID, ServiceTypeDescription)
	SELECT @ApplicationLicenceID, ServiceTypeID, ServiceTypeDescription
	FROM App.ApplicationLicenceServiceDetails
	WHERE ApplicationLicenceID = @ParentApplicationLicenceID

	/*************************************************
    Create the vehicle details
	*************************************************/ 
	INSERT INTO App.ApplicationLicenceVehicleDetails(ApplicationLicenceID,IsStillToBeAcquired,VehicleMakeID,VehicleMakeDescription,VehicleTypeID,CarryingCapacityID,VIN,EngineNumber,YearOfManufacture,VehicleRegistrationNumber,SeatedCapacity,StandingCapacity,GrossMass,Tare,VerifiedVehicleID,CurrentOdometerReading,RoadworthyCertificateDocID,ProfessionalDriversPermitDocID, RankPermitDocID,CertificateOfRegistrationDocID,VehicleModelID)
	SELECT @ApplicationLicenceID, IsStillToBeAcquired, VehicleMakeID, VehicleMakeDescription, VehicleTypeID, CarryingCapacityID, VIN, EngineNumber, YearOfManufacture, VehicleRegistrationNumber, SeatedCapacity, StandingCapacity, GrossMass, Tare, VerifiedVehicleID, CurrentOdometerReading, RoadworthyCertificateDocID, ProfessionalDriversPermitDocID, RankPermitDocID, CertificateOfRegistrationDocID, VehicleModelID
	FROM App.ApplicationLicenceVehicleDetails
	WHERE ApplicationLicenceID = @ParentApplicationLicenceID

	/*************************************************
    Create the proxy record
	*************************************************/ 
	INSERT INTO App.ApplicationProxyDetails(ApplicationDetailID, Surname, FirstNames, IDTypeID, IDTypeDescription, TelPhone, TelCell, TelFax, Email, IDNumberOrBusinessRegistrationNo)
	SELECT @ApplicationDetailID, Surname, FirstNames, IDTypeID, IDTypeDescription, TelPhone, TelCell, TelFax, Email, IDNumberOrBusinessRegistrationNo
	FROM App.ApplicationProxyDetails
	WHERE ApplicationDetailID = @ParentApplicationDetailID

	/*************************************************
    Create the application objections
	*************************************************/ 
	INSERT INTO App.ApplicationObjections(ApplicationDetailID , Objector, Objection, ObjectionDate, PostalAddress, PostalAddressCode, TelPhone, TelCell, TelFax, Email, CreatedByID, CreatedDate, ObjectionDocumentID, ApplicationLicenceID, IsCancelled)
	SELECT								  @ApplicationDetailID, Objector, Objection, ObjectionDate, PostalAddress, PostalAddressCode, TelPhone, TelCell, TelFax, Email, CreatedByID, CreatedDate, ObjectionDocumentID, @ApplicationLicenceID, IsCancelled
	FROM App.ApplicationObjections
	WHERE ApplicationDetailID = @ParentApplicationDetailID
	AND ApplicationLicenceID = @ParentApplicationLicenceID
	AND IsCancelled = 0

	/*************************************************
    Recreate the workflow
		Copy all tasks, comments etc. to keep history in tact
	*************************************************/ 
	DECLARE @ParentWorkflowInstanceID BIGINT

	SELECT @ParentWorkflowInstanceID = WorkflowInstanceID
	FROM App.ApplicationDetails
	WHERE ApplicationDetailID = @ParentApplicationDetailID

	/*************************************************
    Create the main workflow instance record
	*************************************************/ 
	DECLARE @WorkflowInstanceID BIGINT 
	--DECLARE @ReferenceNumber VARCHAR(15)
	
	--SELECT @ReferenceNumber = FolderPrefix + CAST(@ApplicationDetailID AS VARCHAR(20))
	--FROM System.Entities
	--WHERE EntityID = 1000 /*application detail*/

	INSERT INTO Wf.WorkflowInstances(WorkflowInstanceStatusID, WorkflowTemplateID, WorkflowTemplateStepID, WorkflowInstanceStateID, OfficeLocationID, EntityID, ItemID, ReferenceNumber, StartDate, WarningDate, DueDate, EndDate, RequiredSteps, CreatedByID, CreatedDate, UpdatedByID, UpdatedDate, WorkflowInstanceAge, Description)
	SELECT 1, WorkflowTemplateID, WorkflowTemplateStepID, WorkflowInstanceStateID, OfficeLocationID, EntityID, @ApplicationDetailID, @ApplicationNumber, StartDate, WarningDate, DueDate, EndDate, RequiredSteps, CreatedByID, CreatedDate, UpdatedByID, UpdatedDate, WorkflowInstanceAge, NULL
	FROM wf.WorkflowInstances
	WHERE WorkflowInstanceID = @ParentWorkflowInstanceID

	SET @WorkflowInstanceID = SCOPE_IDENTITY()

	UPDATE App.ApplicationDetails
	SET
		WorkflowInstanceID = @WorkflowInstanceID
	WHERE ApplicationDetailID = @ApplicationDetailID

	/*************************************************
    Create the workflow tasks
	*************************************************/ 
	INSERT INTO Wf.WorkflowInstanceTasks(WorkflowInstanceID, WorkflowInstanceTaskStatusID, WorkflowTemplateStepID, DateCompleted, AssignedToID, CompletedByID, Description, Comments, CreatedByID, CreatedDate, UpdatedByID, UpdatedDate, EscalatedFromID)
	SELECT @WorkflowInstanceID, WorkflowInstanceTaskStatusID, WorkflowTemplateStepID, DateCompleted, AssignedToID, CompletedByID, Description, Comments, CreatedByID, CreatedDate, UpdatedByID, UpdatedDate, EscalatedFromID
	FROM Wf.WorkflowInstanceTasks
	WHERE WorkflowInstanceID = @ParentWorkflowInstanceID

	/*************************************************
    Move the adjudication
	*************************************************/
	IF (@MoveToAdjudicationQueue = 1)
	BEGIN
		UPDATE Wf.WorkflowInstanceTasks
		SET CompletedByID = @SystemUserID
		, WorkflowInstanceTaskStatusID = 12 /*Override*/
		, DateCompleted = GETDATE()
		, UpdatedByID = 1
		, UpdatedDate = GETDATE()
		WHERE WorkflowInstanceID = @WorkflowInstanceID

		INSERT INTO Wf.WorkflowInstanceTasks(WorkflowInstanceID, WorkflowInstanceTaskStatusID, WorkflowTemplateStepID, DateCompleted, AssignedToID, CompletedByID, Description, Comments, CreatedByID, CreatedDate, UpdatedByID, UpdatedDate, EscalatedFromID)
		SELECT @WorkflowInstanceID, 1, WorkflowTemplateStepID/*Adjudication Queue (Add application to adjudication)*/, NULL, @SystemUserID, NULL, StepDescription, NULL, @SystemUserID, GETDATE(), @SystemUserID, GETDATE(), NULL
		FROM Wf.WorkflowTemplateSteps
		WHERE WorkflowTemplateStepID = 1012

		UPDATE Wf.WorkflowInstances
		SET WorkflowTemplateStepID = 1012
		WHERE WorkflowInstanceID = @WorkflowInstanceID

	END

	/*************************************************
    Create the workflow documents
	*************************************************/ 		
	EXEC Wf.Pr_WorkflowInstanceDocumentsCopyToNewInstance @ParentWorkflowInstanceID, @WorkflowInstanceID, @AuditComment, @UserID

	/*************************************************
    Create the workflow participants
	*************************************************/ 
	INSERT INTO Wf.WorkflowInstanceParticipants(WorkflowInstanceID, WorkflowTemplateParticipantID, UserID)
	SELECT @WorkflowInstanceID, WorkflowTemplateParticipantID, UserID
	FROM Wf.WorkflowInstanceParticipants
	WHERE WorkflowInstanceID = @ParentWorkflowInstanceID

	/*************************************************
    Create the workflow status history records
	*************************************************/ 
	INSERT INTO Wf.WorkflowInstanceStatusHistory(WorkflowInstanceID, WorkflowInstanceStatusID, UserID, [Date])
	SELECT @WorkflowInstanceID, WorkflowInstanceStatusID, UserID, [Date]
	FROM Wf.WorkflowInstanceStatusHistory
	WHERE WorkflowInstanceID = @ParentWorkflowInstanceID
	
	/*************************************************
    Create the workflow step records
	*************************************************/ 
	INSERT INTO Wf.WorkflowInstanceSteps(WorkflowInstanceID, WorkflowTemplateStepID, IsRequired)
	SELECT @WorkflowInstanceID, WorkflowTemplateStepID, IsRequired
	FROM Wf.WorkflowInstanceSteps
	WHERE WorkflowInstanceID = @ParentWorkflowInstanceID

	/*************************************************
    Perform post saves
	*************************************************/ 
	EXEC App.Pr_ApplicationPostSaveProcess @ApplicationDetailID, @UserID 
	EXEC App.Pr_ApplicationStatusCalculate @ApplicationDetailID, @UserID 
	EXEC Wf.Pr_WorkflowInstancePostFormSave @WorkflowInstanceID, @UserID
  	
END
GO

CREATE OR ALTER PROCEDURE [App].[Pr_TAT_ApplicationCreate]
	@ApplicationPrecheckDetailID 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) = '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	

	IF EXISTS(SELECT * FROM App.ApplicationDetails WHERE ApplicationPrecheckDetailID = @ApplicationPrecheckDetailID)
	BEGIN
		RAISERROR('Precheck already used', 16, 1)
		RETURN
	END

	DECLARE @ApplicationRequestTypeID SMALLINT,
		@SourceApplicationID SMALLINT = 3,
		@ApplicantName VARCHAR(510),
		@TelCell VARCHAR(14),
		@IdTypeID SMALLINT,
		@IdOrBusinessRegistrationNumber	VARCHAR(20),	
		@ApplicationProvinceID SMALLINT,
		@ApplicationRegionID SMALLINT,
		@ParentApplicationDetailID BIGINT,
		@ParentApplicationLicenceID BIGINT


	SELECT 
		@ApplicationRequestTypeID = ApplicationRequestTypeID,
		@ApplicantName  = ApplicantName ,
		@TelCell = TelCell,
		@IdTypeID = IdTypeID,
		@IdOrBusinessRegistrationNumber	 = IdOrBusinessRegistrationNumber,
		@ApplicationProvinceID = ApplicationProvinceID,
		@ApplicationRegionID = ApplicationRegionID,
		@DivisionTypeID = OfficeLocationDivisionTypeID,
		@ProvinceID = ApplicationProvinceID,
		@ParentApplicationDetailID = ParentApplicationDetailID,
		@ParentApplicationLicenceID = ParentApplicationLicenceID
	FROM App.ApplicationPrecheckDetails
	WHERE ApplicationPrecheckDetailID = @ApplicationPrecheckDetailID
	

	/********************************************************************************************
	Create application
	*********************************************************************************************/	
	DECLARE @ApplicationDetailID BIGINT
	DECLARE @GazetteTypeID SMALLINT = App.Fn_GetGazetteType(@ApplicationRequestTypeID, @DivisionTypeID, @ProvinceID)		

	/*************************************************
    Create the base application record
	*************************************************/
	INSERT INTO App.ApplicationDetails(AccreditationID, ApplicationRequestTypeID, SourceApplicationID, OfficeLocationID, OfficeLocationDivisionTypeID, ApplicationProvinceID, ApplicationRegionID, CreatedByID, CreatedDate, UpdatedByID, UpdatedDate, IsBulkApplication, LicenceCount, AdjudicationOfficeLocationID, ApplicationStatusID, ApplicationSubStatusID, GazetteTypeID, ApplicationPrecheckDetailID, ForceGazetting, CopiedFromApplicationDetailID, CopiedFromApplicationLicenceID)
	VALUES(NULL, @ApplicationRequestTypeID, @SourceApplicationID, @OfficeLocationID, @DivisionTypeID,  @ApplicationProvinceID, @ApplicationRegionID, @UserID, GETDATE(), @UserID, GETDATE(), 0, 0, @OfficeLocationID, 2 /*Data Capture*/, 2 /*Awaiting Data Capture*/, @GazetteTypeID, @ApplicationPrecheckDetailID, 1, @ParentApplicationDetailID, @ParentApplicationLicenceID)
	
	SET @ApplicationDetailID = SCOPE_IDENTITY()
	
	/*************************************************
    Update the application number
	*************************************************/
	DECLARE @ApplicationNumberTable TABLE(ApplicationNumber VARCHAR(20))
	DECLARE @ApplicationNumber VARCHAR(20)
	DECLARE @OriginalApplicationNumber VARCHAR(20)

	SELECT @OriginalApplicationNumber = ApplicationLicenceReferenceNumber
	FROM App.ApplicationLicences
	WHERE ApplicationDetailID = @ParentApplicationDetailID
	AND ApplicationLicenceID = @ParentApplicationLicenceID

	;WITH Cte AS
	(
		SELECT TOP(1) ReferenceNumber, IsUsed
		FROM System.ReferenceNumbers WITH (ROWLOCK, READPAST)
		WHERE 
			DivisionTypeID = @DivisionTypeID 
			AND ProvinceID = @ApplicationProvinceID 
			AND EntityID = 1033 /*ApplicationDetails*/
			AND IsUsed = 0 
		ORDER BY ReferenceNumberID
	)
	UPDATE Cte SET IsUsed = 1
	OUTPUT Inserted.ReferenceNumber INTO @ApplicationNumberTable;

	SELECT @ApplicationNumber = ApplicationNumber
	FROM @ApplicationNumberTable

	UPDATE App.ApplicationDetails
	SET	
		ApplicationNumber = @ApplicationNumber
	WHERE ApplicationDetailID = @ApplicationDetailID

	/*************************************************
    Create TAT Record
	*************************************************/
	DECLARE @DateAdjudicated DATETIME
	DECLARE @ApplicationAdjudicationID BIGINT
	DECLARE @AdjudicationDecisionID BIGINT

	SELECT @DateAdjudicated = FinalAdjudicationDecisionDate
	, @ApplicationAdjudicationID = ApplicationAdjudicationID
	, @AdjudicationDecisionID = FinalAdjudicationDecisionID
	FROM App.ApplicationAdjudications 
	WHERE IsActiveAdjudication = 1 AND ApplicationDetailID = @ParentApplicationDetailID
	AND ApplicationLicenceID = @ParentApplicationLicenceID

	INSERT INTO App.ApplicationTATDetails(ApplicationDetailID, OriginalApplicationNumber, DateAdjudicated, ApplicationAdjudicationID, TatRequestByName, TatRequestByIDTypeID, TatRequestByIDNumber, AdjudicationDecisionID, TatRequestByTelCell)
	VALUES(@ApplicationDetailID, @OriginalApplicationNumber, @DateAdjudicated, @ApplicationAdjudicationID, @ApplicantName, @IdTypeID, @IdOrBusinessRegistrationNumber, @AdjudicationDecisionID, @TelCell)

	/*************************************************
    Create Application Licence Record
	*************************************************/	
	INSERT INTO App.ApplicationLicences(ApplicationDetailID, OperatingLicenceNumber, VehicleTypeID, ApplicationLicenceReferenceNumber, CurrentVerifiedLicenceID, IsExistingLicence, ApplicationLicenceRequestTypeID, SplitIntoNewApplication, ApplicationLicenceDescription, IsCancelled)
	VALUES(@ApplicationDetailID, '', null, @ApplicationNumber,NULL, null, @ApplicationRequestTypeID, 0, null, 0)

	/*************************************************
    Create Bypass Business Rules Record
	*************************************************/
	INSERT INTO App.ApplicationBypassBusinessRules(ApplicationDetailID)
	SELECT @ApplicationDetailID

	/*************************************************
    Create the applicant record 
	*************************************************/
	INSERT INTO App.ApplicationApplicantDetails(ApplicationDetailID, IDTypeID)
	VALUES(@ApplicationDetailID, 1)
    
	/*************************************************
    Create the contract record
	*************************************************/ 
	INSERT INTO App.ApplicationContractDetails(ApplicationDetailID)
	VALUES(@ApplicationDetailID)

	/*************************************************
    Create the previous licence holder record

	*************************************************/ 
	INSERT INTO App.ApplicationPreviousLicenceHolder(ApplicationDetailID)
	VALUES(@ApplicationDetailID)

	/*************************************************
    Create the NPTR record
	*************************************************/ 
	INSERT INTO App.ApplicationNPTRDetails(ApplicationDetailID)
	VALUES(@ApplicationDetailID)

	/*************************************************
    Create the inspection record
	*************************************************/ 
	INSERT INTO App.ApplicationInspectionDetails(ApplicationDetailID, InspectionStatusID)
	VALUES(@ApplicationDetailID, 1 /*Awaiting inspection date*/)

	/*************************************************
	Create the tourism authority referral
	*************************************************/ 
	IF @DivisionTypeID = 2
	BEGIN
		INSERT INTO App.ApplicationReferrals(ApplicationDetailID, ReferralTypeID)
		VALUES(@ApplicationDetailID, 1 /*Tourism Authority*/)
	END
	ELSE
	BEGIN
		/*************************************************
		Create the planning authority referral
		*************************************************/ 
		INSERT INTO App.ApplicationReferrals(ApplicationDetailID, ReferralTypeID)
		VALUES(@ApplicationDetailID, 2 /*Planning Authority*/)
	END

	/*************************************************
    Create the planning authority referral
	*************************************************/ 
	INSERT INTO App.ApplicationReferrals(ApplicationDetailID, ReferralTypeID)
	VALUES(@ApplicationDetailID, 2 /*Planning Authority*/)

	/*************************************************
    Create the application status record
	*************************************************/ 
	INSERT INTO App.ApplicationStatusHistory(ApplicationDetailID,ApplicationStatusID,UpdatedByID,UpdatedDate)
	VALUES(@ApplicationDetailID,2/*Data Capture*/,@UserID,GETDATE())

	/*************************************************
    Create the application sub status record
	*************************************************/ 
	INSERT INTO App.ApplicationSubStatusHistory
	(
	    ApplicationDetailID,
	    ApplicationSubStatusID,
	    UpdatedByID,
	    UpdatedDate
	)
	VALUES(@ApplicationDetailID,2/*Awaiting Data Capture*/,@UserID,GETDATE())	

	/*************************************************
    Create the proxy record
	*************************************************/ 
	INSERT INTO App.ApplicationProxyDetails(ApplicationDetailID)
	VALUES(@ApplicationDetailID)

	
	/*************************************************
    Start the actual workflow
	*************************************************/ 
	DECLARE @ErrorMessage VARCHAR(MAX)
	DECLARE @Username VARCHAR(255) = Security.Fn_GetUserNameByID(@UserID)
	EXEC Wf.Pr_WorkflowInstanceStart 1 /*application workflow*/, 1000 /*ApplicationDetails*/, @ApplicationDetailID, NULL, @Username, @ApplicationNumber, @ErrorMessage OUTPUT
	
    
	/**********************************
	Record Performance Measure
	**********************************/

	EXEC Performance.Pr_PerformanceHistoryCreate 1000/*Application created*/, @UserID, 1000 /*ApplicationDetails*/, @ApplicationDetailID, 1, @UserID

	EXEC [App].[Pr_ApplicationRequiredRulesCalculate] @ApplicationDetailID, @UserID

	/*************************************************
    Return application details
	*************************************************/ 
	SELECT *
	FROM App.Vw_ApplicationDetails_Light
	WHERE ApplicationDetailID = @ApplicationDetailID
END
GO