/*Verified Members*/
-- DELETE FROM Verified.Members WHERE OlasId IS NOT NULL
INSERT INTO Verified.Members(SurnameOrCompanyName,FirstNames,IDTypeID,IDNumberOrBusinessRegistrationNo,PostalAddress,PostalAddressCode,PhysicalAddress,PhysicalAddressCode,TelPhone,TelCell,TelFax,Email,SARSIncomeTaxNumber,
	TradeName,MemberStatusID,OlasID,BusinessTypeID, RegionID)
SELECT sLastName,sFirstName,(SELECT IDTypeID FROM md.IDTypes WHERE OlasID = [NDoTNTR_2023].dbo.tblPerson.fkiIDType),REPLACE(tblPerson.sIDNum,' ',''),
	(SELECT sAddressLine1 + CHAR(13) + sAddressLine2 + CHAR(13) + sAddressLine3 FROM [NDoTNTR_2023].dbo.tblAddress INNER JOIN [NDoTNTR_2023].dbo.tluProvince ON tblAddress.fkiProvinceID = [tluProvince].pkiProvinceID WHERE pkiAddressId = [NDoTNTR_2023].dbo.tblPerson.fkiPOAddressId),
	(SELECT sPOCode FROM [NDoTNTR_2023].dbo.tblAddress INNER JOIN [NDoTNTR_2023].dbo.tluProvince ON tblAddress.fkiProvinceID = [tluProvince].pkiProvinceID WHERE pkiAddressId = [NDoTNTR_2023].dbo.tblPerson.fkiPOAddressId),
	(SELECT sAddressLine1 + CHAR(13) + sAddressLine2 + CHAR(13) + sAddressLine3 FROM [NDoTNTR_2023].dbo.tblAddress INNER JOIN [NDoTNTR_2023].dbo.tluProvince ON tblAddress.fkiProvinceID = [tluProvince].pkiProvinceID WHERE pkiAddressId = [NDoTNTR_2023].dbo.tblPerson.fkiPhysicalAddressId),
	(SELECT sPOCode FROM [NDoTNTR_2023].dbo.tblAddress INNER JOIN [NDoTNTR_2023].dbo.tluProvince ON tblAddress.fkiProvinceID = [tluProvince].pkiProvinceID WHERE pkiAddressId = [NDoTNTR_2023].dbo.tblPerson.fkiPhysicalAddressId),
	sTelephoneNum,sCellNum,sFaxNum,sEMailAddress,sIncomeTaxNumber,sTradeName, 1,
	tblPerson.pkiPersonId,(SELECT BusinessTypeID FROM md.BusinessTypes WHERE OlasID = [NDoTNTR_2023].dbo.tblPerson.fkiTypeOfBusinessId),
	ISNULL((SELECT TOP 1 RegionID FROM  Md.OfficeLocationSetup WHERE ProvinceID = (SELECT TOP 1 CASE WHEN fkiProvinceID = 11 THEN 10 ELSE fkiProvinceID END FROM [NDoTNTR_2023].dbo.tblAddress WHERE pkiAddressId = [NDoTNTR_2023].dbo.tblPerson.fkiPhysicalAddressId)),18)
FROM [NDoTNTR_2023].dbo.tblPerson INNER JOIN (
	SELECT MAX(tblPerson.pkiPersonId) pkiPersonId,ISNULL(REPLACE(sIDNum,' ',''),'') sIDNum 
	FROM [NDoTNTR_2023].dbo.tblPerson LEFT JOIN [NDoTNTR_2023].dbo.tblMember
	ON tblPerson.pkiPersonId = tblMember.fkiPersonId
	WHERE ISNULL(REPLACE(sIDNum,' ',''),'') != ''
	GROUP BY ISNULL(REPLACE(sIDNum,' ',''),'')) A
ON tblPerson.pkiPersonId = A.pkiPersonId
WHERE tblPerson.pkiPersonId NOT IN (SELECT ISNULL(OlasId,-1) FROM Verified.Members)

UPDATE Verified.Members
SET OlasID = NULL

UPDATE Verified.Members
SET OlasID = P.pkiPersonId
FROM [NDoTNTR_2023].dbo.tblLicence L INNER JOIN [NDoTNTR_2023].dbo.tblApplication AA 
ON AA.pkiApplicationId = L.fkiApplicationId INNER JOIN [NDoTNTR_2023].dbo.tlkHumanEntity HE
ON AA.fkiApplicantEntityId = HE.PkiEntityId INNER JOIN [NDoTNTR_2023].dbo.tblPerson P 
ON P.pkiPersonId = HE.mfkiRealEntityId 
WHERE ISNULL(REPLACE(sIDNum,' ',''),'') COLLATE SQL_Latin1_General_CP1_CI_AS = Verified.Members.IDNumberOrBusinessRegistrationNo

SELECT * FROM Verified.Members WHERE OlasID IS NOT NULL

SELECT MAX(tblPerson.pkiPersonId)
FROM [NDoTNTR_2023].dbo.tblPerson LEFT JOIN [NDoTNTR_2023].dbo.tblMember
ON tblPerson.pkiPersonId = tblMember.fkiPersonId
WHERE ISNULL(sIDNum,'') != ''
GROUP BY sIDNum
ORDER BY ISNULL(sIDNum,'') 

/*Applications*/
-- DELETE FROM App.ApplicationDetails WHERE OlasID IS NOT NULL
INSERT INTO App.ApplicationDetails(ApplicationNumber, ApplicationRequestTypeID, SourceApplicationID, ApplicationProvinceID, ApplicationRegionID,OfficeLocationID,OfficeLocationDivisionTypeID, 
		CreatedByID, CreatedDate, UpdatedByID, UpdatedDate, LicenceCount, IsCancelled,ApplicationStatusID,ApplicationSubStatusID, OlasId)	
SELECT sApplicationNum,CASE
			WHEN fkiApplicationTypeId IN (37 /*1 Minibus Permit to 1 Minibus OL*/,40 /*2 Minibus Permits to 1 Midibus OL*/,41 /*2 9-Seater Permits to 1 Minibus OL*/,42 /*4 9-Seater permits to 1 Midibus OL*/,72 /*4 9-Seater permits to 1 Midibus OL*/) THEN 5 /*Conversion (LTPS to legiti-mate)*/
			WHEN fkiApplicationTypeId = 43 /*New OL*/ THEN 1 /*New Operating Licence*/
			WHEN fkiApplicationTypeId = 44	/*Temporary OL*/ THEN 11 /*Temporary (Special) licence*/
			WHEN fkiApplicationTypeId = 45	/*Transfer of OL*/ THEN 6 /*Normal transfer*/						
			WHEN fkiApplicationTypeId = 48	/*Replacement of Vehicle*/ THEN 3 /*Replacements (COV)*/
			WHEN fkiApplicationTypeId = 49	/*Renewal of OL*/ THEN 8 /*Renewal*/			
			WHEN fkiApplicationTypeId = 57	/*Additional Authority*/ THEN 2 /*Add Authorisations/Routes*/
			WHEN fkiApplicationTypeId = 59	/*Duplicate OL*/ THEN 10 /*Duplicate Licence*/
			WHEN fkiApplicationTypeId = 61	/*Amendment of Route*/ THEN 2 /*Add Authorisations/Routes*/
			WHEN fkiApplicationTypeId = 56 /*OL Withdrawal*/ then 5 /*Conversion (LTPS to legiti-mate)*/
			WHEN fkiApplicationTypeId = 70 /*Temporary Suspension of OL*/ then 5 /*Conversion (LTPS to legiti-mate)*/
			WHEN fkiApplicationTypeId = 47 /*Change of Particulars*/ then 29 /*Personal Details Update*/
			WHEN fkiApplicationTypeId = 46 /*Additional Vehicle*/ then 5 /*Conversion (LTPS to legiti-mate)*/
			WHEN fkiApplicationTypeId = 72 /*Additional Vehicle*/ then 38 /*Conversion (LTPS to legiti-mate)*/
			ELSE 5 END,1,Provinces.ProvinceID,(SELECT TOP 1 RegionID FROM Md.OfficeLocationSetup WHERE ProvinceID = Provinces.ProvinceID),
		(SELECT TOP 1 OfficeLocationID FROM Md.OfficeLocationSetup WHERE ProvinceID = Provinces.ProvinceID),
		(SELECT TOP 1 OfficeLocationSetup.DivisionTypeID FROM Md.OfficeLocationSetup WHERE ProvinceID = Provinces.ProvinceID),
		1,dtApplicationDate,1,tblApplication.dtAudit,ISNULL(iNumVehicles,0),
		CASE WHEN ISNULL(sWithdrawalReason,'') = '' THEN 0 ELSE 1 END,
		CASE
			WHEN fkiApplicationStatusId = 1 THEN 2
			WHEN fkiApplicationStatusId = 2 THEN 3
			WHEN fkiApplicationStatusId = 3 THEN 5
			WHEN fkiApplicationStatusId = 4 THEN 6
			WHEN fkiApplicationStatusId = 5 THEN 7
			WHEN fkiApplicationStatusId = 6 THEN 8
			WHEN fkiApplicationStatusId = 9 THEN 9
		END,1,
		pkiApplicationId
FROM [NDoTNTR_2023].dbo.tblApplication INNER JOIN [NDoTNTR_2023].dbo.tblPermitBoard INNER JOIN Md.Provinces
ON CASE WHEN tblPermitBoard.fkiProvinceId = 11 THEN 10 ELSE tblPermitBoard.fkiProvinceId END = Provinces.ProvinceID
ON tblApplication.fkiBoardCapturedById = tblPermitBoard.pkiPermitBoardId 
WHERE tblApplication.pkiApplicationId NOT IN (SELECT ISNULL(OlasId,-1) FROM App.ApplicationDetails)

/*Verified Vehicles*/
-- DELETE FROM Verified.Vehicles WHERE OlasID IS NOT NULL
-- SELECT * FROM Verified.Vehicles
INSERT INTO Verified.Vehicles(RegistrationNumber,EngineNumber,VIN,SeatedCapacity,StandingCapacity,GrossMass,VehicleMakeID,VehicleTypeID,
	VehicleStatusID,OlasID,CarryingCapacityID,YearOfManufacture)
SELECT sRegistrationNum,sEngineNum,V.sChassisNum, iSeatingCapacity, 0,dGrossMass, 
	(SELECT TOP 1 VehicleMakeID FROM Md.VehicleMakes WHERE [Description] = Vm.sVehicleMakeDesc COLLATE SQL_Latin1_General_CP1_CI_AS), 
	CASE
		WHEN V.fkiVehicleTypeID = 1 THEN 1	/*Motorcar*/
		WHEN V.fkiVehicleTypeID = 2 THEN 2	/*Microbus/Minibus/Combi*/
		WHEN V.fkiVehicleTypeID = 3 THEN 3	/*Bus (Single deck)*/
		WHEN V.fkiVehicleTypeID = 4 THEN 3	/*Bus (Double deck)*/
		WHEN V.fkiVehicleTypeID = 5 THEN 11	/*Panel Wagon*/
		WHEN V.fkiVehicleTypeID = 6 THEN 11	/*Flat Deck Truck*/
		WHEN V.fkiVehicleTypeID = 7 THEN 11	/*Flat Deck Trailer*/
		WHEN V.fkiVehicleTypeID = 8 THEN 11	/*Type 8*/
		WHEN V.fkiVehicleTypeID = 9 THEN 11	/*Unknown*/
		WHEN V.fkiVehicleTypeID = 10 THEN 10	/*Midibus*/
	END, 1 /*todo - calculate vehicle status*/, V.pkiVehicleId, 
	CASE
		WHEN ISNULL(V.iSeatingCapacity,0) <= 8 THEN 1 /*4-8*/
		WHEN ISNULL(V.iSeatingCapacity,0) <= 16 THEN 2 /*9-16*/
		WHEN ISNULL(V.iSeatingCapacity,0) <= 35 THEN 3 /*17-35*/
		ELSE 4 /*35+*/
	END, 
	iManufactureYear /*year of manufature*/
FROM [NDoTNTR_2023].dbo.tblVehicle V INNER JOIN (SELECT MAX(v.pkiVehicleId) pkiVehicleId, V.sChassisNum
FROM [NDoTNTR_2023].dbo.tblVehicle V LEFT JOIN [NDoTNTR_2023].dbo.tluVehicleMake Vm 
ON Vm.pkiVehicleMakeId = V.fkiVehicleMakeId
WHERE ISNULL(REPLACE(V.sChassisNum,' ',''),'') != ''
GROUP BY V.sChassisNum) A 
ON v.pkiVehicleId = A.pkiVehicleId LEFT JOIN [NDoTNTR_2023].dbo.tluVehicleMake Vm 
ON Vm.pkiVehicleMakeId = V.fkiVehicleMakeId
WHERE V.pkiVehicleID NOT IN (SELECT ISNULL(OlasId,-1) FROM Verified.Vehicles) AND
	V.sChassisNum COLLATE SQL_Latin1_General_CP1_CI_AS NOT IN (SELECT VIN FROM Verified.Vehicles)

/*Verified Vehicles*/
--DELETE FROM Verified.LicenceExceptions
--GO
--DELETE FROM Verified.Licences WHERE OlasID IS NOT NULL
--GO
INSERT INTO App.ApplicationLicences(ApplicationDetailID,ApplicationLicenceReferenceNumber,OlasId)
SELECT (SELECT TOP 1 ApplicationDetails.ApplicationDetailID FROM App.ApplicationDetails WITH(NOLOCK) WHERE OlasId = tblLicence.fkiApplicationId),
	tblApplication.sApplicationNum + '-' + CAST(ROW_NUMBER() OVER(PARTITION BY tblLicence.fkiApplicationId ORDER BY tblLicence.fkiApplicationId DESC) AS VARCHAR(10)),
	tblLicence.pkiLicenceId
FROM [NDoTNTR_2023].dbo.tblLicence LEFT JOIN [NDoTNTR_2023].dbo.tblApplication
ON tblLicence.fkiApplicationId = tblApplication.pkiApplicationId
WHERE tblLicence.pkiLicenceId NOT IN (SELECT ISNULL(OlasId,-1) FROM App.ApplicationLicences)
GO

INSERT INTO Verified.Licences(ApplicationDetailID, ApplicationLicenceID, LicenceStatusID, MemberID, 
	VehicleID, LicenceVehicleTypeID, NameOnLicence, OperatingLicenceNumber, IssueNumber, CertificateNumber, DateOfIssue, 
	DateOfExpiry, Indefinite, IsLatestIssue, OlasId,DivisionTypeID,LicencingBoardID,ProvinceID,RegionID)
SELECT ApplicationDetails.ApplicationDetailID, 
	(SELECT TOP 1 ApplicationLicenceID FROM App.ApplicationLicences WHERE OlasId = tblLicence.pkiLicenceId),
	1,NULL,NULL,NULL,
	(SELECT TOP 1 Members.FullName FROM Verified.Members WHERE OlasID = (SELECT TOP 1 P.pkiPersonId FROM [NDoTNTR_2023].dbo.tblLicence L
			INNER JOIN [NDoTNTR_2023].dbo.tblApplication AA ON AA.pkiApplicationId = L.fkiApplicationId
			INNER JOIN [NDoTNTR_2023].dbo.tlkHumanEntity HE ON AA.fkiApplicantEntityId = HE.PkiEntityId 
			INNER JOIN [NDoTNTR_2023].dbo.tblPerson P ON P.pkiPersonId = HE.mfkiRealEntityId 
			INNER JOIN Verified.Members M ON P.pkiPersonId = M.OlasID 
		WHERE pkiLicenceId = tblLicence.pkiLicenceId)),sOLNum,iIssueNum,sCertificateNum,dtIssueDate,dtOLExpiryDate,bIndefiniteOL,1, pkiLicenceId,
	ApplicationDetails.OfficeLocationDivisionTypeID,
	(SELECT TOP 1 LicencingBoardID FROM Md.OfficeLocationSetup WHERE DivisionTypeID = ApplicationDetails.OfficeLocationDivisionTypeID AND 
		ProvinceID = ApplicationDetails.ApplicationProvinceID AND
		RegionID = ApplicationDetails.ApplicationRegionID),
	ApplicationDetails.ApplicationProvinceID,ApplicationDetails.ApplicationRegionID		
FROM [NDoTNTR_2023].dbo.tblLicence LEFT JOIN [NDoTNTR_2023].dbo.tblApplication
ON tblLicence.fkiApplicationId = tblApplication.pkiApplicationId LEFT JOIN App.ApplicationDetails
ON tblLicence.fkiApplicationId = ApplicationDetails.OlasId
WHERE pkiLicenceId NOT IN (SELECT ISNULL(OlasId,-1) FROM Verified.Licences)

UPDATE Verified.Licences
SET MemberID = A.MemberID, NameOnLicence = FullName
FROM (SELECT M.FullName,M.MemberID, L.pkiLicenceId FROM [NDoTNTR_2023].dbo.tblLicence L
			INNER JOIN [NDoTNTR_2023].dbo.tblApplication AA ON AA.pkiApplicationId = L.fkiApplicationId
			INNER JOIN [NDoTNTR_2023].dbo.tlkHumanEntity HE ON AA.fkiApplicantEntityId = HE.PkiEntityId 
			INNER JOIN [NDoTNTR_2023].dbo.tblPerson P ON P.pkiPersonId = HE.mfkiRealEntityId 
			INNER JOIN Verified.Members M ON P.pkiPersonId = M.OlasID) A
WHERE Verified.Licences.OlasId = A.pkiLicenceId

SELECT * FROM Verified.Licences WHERE MemberID = 1800590

UPDATE Verified.Licences
SET VehicleID = A.VehicleID, LicenceVehicleTypeID = A.VehicleTypeID  
FROM (SELECT L.pkiLicenceId, M.VehicleID, M.VehicleTypeID FROM [NDoTNTR_2023].dbo.tblLicence L
			INNER JOIN [NDoTNTR_2023].dbo.tblVehicle V ON L.fkiVehicleId = V.pkiVehicleId
			INNER JOIN Verified.Vehicles M ON V.sChassisNum COLLATE SQL_Latin1_General_CP1_CI_AS = M.VIN) A
WHERE Verified.Licences.OlasId = A.pkiLicenceId

-- Update Awaiting Issuing
UPDATE [Verified].[Licences]
SET LicenceStatusID = 1 

-- Issued
UPDATE [Verified].[Licences]
SET LicenceStatusID = 2, Indefinite = 1 
WHERE LicenceStatusID = 1 AND 
	CertificateNumber IS NOT NULL AND 
	DateOfIssue IS NOT NULL AND
	DateOfExpiry IS NULL

UPDATE [Verified].[Licences]
SET LicenceStatusID = 2, Indefinite = 0 
WHERE LicenceStatusID = 1 AND 
	CertificateNumber IS NOT NULL AND 
	DateOfIssue IS NOT NULL AND
	DateOfExpiry IS NOT NULL

UPDATE [Verified].[Licences]
SET LicenceStatusID = 3
WHERE LicenceStatusID = 2 AND
	DateOfExpiry BETWEEN GETDATE() AND DATEADD(D,60,GETDATE())

UPDATE [Verified].[Licences]
SET LicenceStatusID = 4
WHERE LicenceStatusID = 2 AND
	DateOfExpiry <= GETDATE()

UPDATE [Verified].[Licences]
SET LicenceStatusID = 5
WHERE LicenceStatusID = 1 AND
	DateOfExpiry <= GETDATE() AND DateOfIssue IS NOT NULL AND CertificateNumber IS NULL AND DateOfExpiry IS NOT NULL

UPDATE [Verified].[Licences]
SET LicenceStatusID = 6, Indefinite = 1
WHERE LicenceStatusID = 1 AND
	DateOfIssue IS NOT NULL AND CertificateNumber IS NULL AND DateOfExpiry IS NULL

UPDATE [Verified].[Licences]
SET LicenceStatusID = 7
WHERE LicenceStatusID = 1 AND
	DateOfIssue IS NULL AND CertificateNumber IS NULL AND DateOfExpiry IS NOT NULL

UPDATE [Verified].[Licences]
SET LicenceStatusID = 8
WHERE LicenceStatusID = 1 AND
	OperatingLicenceNumber IS NULL


UPDATE [Verified].[Licences]
SET CertificateNumber = REPLACE(CertificateNumber,' ','')

/*Verified Licence Service Details*/
--DELETE FROM Verified.LicenceServiceDetails
--GO
INSERT INTO Verified.LicenceServiceDetails(LicenceID,ServiceTypeID,ServiceTypeDescription)
SELECT Licences.LicenceID,CASE
		WHEN A.fkiServiceTypeId =  4 THEN 2	/*OLAS Minibus taxi-type service = LG Unscheduled service*/
		WHEN A.fkiServiceTypeId =  5 THEN 3	/*OLAS Charter service = LG Charter service*/
		WHEN A.fkiServiceTypeId =  6 THEN 10	/*OLAS Metered taxi = LG Metered taxi Service*/
		WHEN A.fkiServiceTypeId =  7 THEN 5	/*OLAS Staff service = LG Staff Service */
		WHEN A.fkiServiceTypeId =  8 THEN 1	/*OLAS Scheduled service = LG Scheduled Service*/
		WHEN A.fkiServiceTypeId =  10 THEN 2	/*OLAS Unscheduled service = LG Unscheduled service*/
		WHEN A.fkiServiceTypeId =  13 THEN 11	/*OLAS Long distance service = LG Long Distance Service*/
		WHEN A.fkiServiceTypeId =  26 THEN 4	/*OLAS Tourist service = LG Tourist service*/
		WHEN A.fkiServiceTypeId =  62 THEN 6	/*OLAS Educational Transport = LG Scholat service*/
		WHEN A.fkiServiceTypeId = 63 THEN 8	/*OLAS Organised GROUP = LG Other*/
		ELSE 8 /*other*/
	END,ISNULL(A.sServiceTypeDesc,'Organised group')
FROM Verified.Licences LEFT JOIN (SELECT Vehicles.VehicleID,tlkVehicleServiceType.fkiServiceTypeId,tluServiceType.sServiceTypeDesc FROM [NDoTNTR_2023].dbo.tlkVehicleServiceType INNER JOIN [NDoTNTR_2023].dbo.tluServiceType 
			ON tlkVehicleServiceType.fkiServiceTypeId = tluServiceType.pkiServiceTypeId INNER JOIN Verified.Vehicles
			ON tlkVehicleServiceType.fkiVehicleId = Vehicles.OlasId) A
ON Licences.VehicleID = A.VehicleID
WHERE Licences.LicenceID NOT IN (SELECT LicenceID FROM Verified.LicenceServiceDetails)


/*Verified Licence Routes*/
--DELETE FROM verified.LicenceRoutes
--GO
INSERT INTO Verified.LicenceRoutes(LicenceID,RouteID,ServiceTypeID)
SELECT VL.LicenceID, VR.RouteID,
	CASE
		WHEN VST.fkiServiceTypeId = 4 THEN 2	/*OLAS Minibus taxi-type service = LG Unscheduled service*/
		WHEN VST.fkiServiceTypeId = 5 THEN 3	/*OLAS Charter service = LG Charter service*/
		WHEN VST.fkiServiceTypeId = 6 THEN 10	/*OLAS Metered taxi = LG Metered taxi Service*/
		WHEN VST.fkiServiceTypeId = 7 THEN 5	/*OLAS Staff service = LG Staff Service */
		WHEN VST.fkiServiceTypeId = 8 THEN 1	/*OLAS Scheduled service = LG Scheduled Service*/
		WHEN VST.fkiServiceTypeId = 10 THEN 2	/*OLAS Unscheduled service = LG Unscheduled service*/
		WHEN VST.fkiServiceTypeId = 13 THEN 11	/*OLAS Long distance service = LG Long Distance Service*/
		WHEN VST.fkiServiceTypeId = 26 THEN 4	/*OLAS Tourist service = LG Tourist service*/
		WHEN VST.fkiServiceTypeId = 62 THEN 6	/*OLAS Educational Transport = LG Scholat service*/
		WHEN VST.fkiServiceTypeId = 63 THEN 8	/*OLAS Organised GROUP = LG Other*/
		ELSE 8 /*other*/
	END FROM NDoTNTR_2023.dbo.tblApplication A INNER JOIN NDoTNTR_2023.dbo.tblLicence L
ON A.pkiApplicationId = L.fkiApplicationId INNER JOIN NDoTNTR_2023.dbo.tluLicenceStatus LS
ON L.fkiLicenceStatusId = LS.pkiLicenceStatusId INNER JOIN NDoTNTR_2023.dbo.tblVehicle V
ON L.pkiLicenceId = V.fkiLicenceId INNER JOIN NDoTNTR_2023.dbo.tlkVehicleServiceType VST
ON VST.fkiVehicleId = V.pkiVehicleId INNER JOIN NDoTNTR_2023.dbo.tlkVehicleServiceTypeRoute VSTR 
ON VST.pkiVehicleServiceTypeId = VSTR.fkiVehicleServiceTypeId INNER JOIN NDoTNTR_2023.dbo.tblApprovedRoute AR 
ON AR.pkiApprovedRouteId = VSTR.fkiApprovedRouteId INNER JOIN NDoTNTR_2023.dbo.tlkVehicleServiceTypeCondition VSTC
ON VST.pkiVehicleServiceTypeId = VSTC.fkiVehicleServiceTypeId INNER JOIN NDoTNTR_2023.dbo.tluServiceCondition SC
ON SC.pkiServiceConditionId = VSTC.fkiServiceConditionId INNER JOIN Verified.Licences VL
ON L.pkiLicenceId = VL.OlasId INNER JOIN Verified.Routes VR
ON AR.pkiApprovedRouteId   = VR.OlasID LEFT JOIN Verified.LicenceRoutes LR
ON VL.LicenceID = LR.LicenceID AND  VR.RouteID = LR.RouteID
WHERE LR.LicenceRouteID IS NULL

