CREATE OR ALTER VIEW [Verified].[Vw_OperatingLicenceDetails]
AS
    SELECT L.LicenceID ,
		L.ApplicationDetailID ,
		ISNULL(UPPER(A.AssociationName),'-') AS AssociationName ,
		ISNULL(UPPER(Acc.AccreditationNumber),'-') AS AccreditationNumber,
		UPPER(L.OperatingLicenceNumber) AS OperatingLicenceNumber ,
		UPPER(ARD.ApplicationNumber) AS ApplicationNumber ,
		UPPER(M.IDNumberOrBusinessRegistrationNo) AS IDNumberOrBusinessRegistrationNo ,
		UPPER(L.NameOnLicence) AS NameOnLicence ,
		UPPER(REPLACE(M.PostalAddress, CHAR(10),',') + ',' + M.PostalAddressCode) AS PostalAddress ,
		UPPER(V.RegistrationNumber) AS RegistrationNumber ,
		V.GrossMass ,
		UPPER(V.EngineNumber) AS EngineNumber ,
		UPPER(V.VIN) AS VIN ,
		UPPER(VM.Description) AS VehicleMake ,
		CAST(V.SeatedCapacity AS VARCHAR(10)) + ' Seated' AS SeatedCapacity,
		CASE 
			WHEN V.StandingCapacity > 0 THEN CAST(V.StandingCapacity AS VARCHAR(10)) + ' Standing' 
			ELSE ''
		END AS StandingCapacity,
		VT.Description AS VehicleType ,
		UPPER(L.CertificateNumber) AS CertificateNumber ,
		CONVERT(VARCHAR, L.DateOfIssue, 106) AS DateOfIssue ,
		CONVERT(VARCHAR, L.DateOfExpiry, 106) AS DateOfExpiry ,
		'NO' AS CrossBorder ,
		CASE 
			WHEN L.DivisionTypeID = 2 THEN 'National Public Transport Regulator'
			WHEN P.Description IS NOT NULL THEN P.Description + ' Provincial Regulatory Entity'
			ELSE 'Provincial Regulatory Entity'
		END AS LicensingBoard,
		UPPER(V.YearOfManufacture) AS YearOfFirstReg ,
		ARD.ApplicationRequestTypeID ,
		Verified.Fn_GetLicenceExceptions(LicenceID) AS LicenceExceptions ,
		Ls.Title AS LicenceStatus ,
		L.VehicleID,
		A.AssociationID,
		Ls.LicenceStatusID AS LicenceStatusID,
		L.MemberID,
		L.DivisionTypeID
    FROM Verified.Licences AS L INNER JOIN Verified.LicenceStatus Ls 
	ON L.LicenceStatusID = Ls.LicenceStatusID LEFT JOIN Verified.Members AS M 
	ON L.MemberID = M.MemberID LEFT JOIN App.Vw_ApplicationDetails AS ARD 
	ON L.ApplicationDetailID = ARD.ApplicationDetailID LEFT JOIN App.ApplicationApplicantDetails AS AAD 
	ON L.ApplicationDetailID = AAD.ApplicationDetailID LEFT JOIN Verified.Associations AS A 
	ON ARD.VerifiedAssociationID = A.AssociationID LEFT JOIN Verified.Vehicles AS V 
	ON L.VehicleID = V.VehicleID LEFT JOIN Md.VehicleMakes AS VM 
	ON V.VehicleMakeID = VM.VehicleMakeID LEFT JOIN Md.VehicleTypes AS VT 
	ON V.VehicleTypeID = VT.VehicleTypeID LEFT JOIN Verified.Accreditations Acc 
	ON L.AccreditationID = Acc.AccreditationID LEFT JOIN Md.Provinces AS P
	ON L.ProvinceID = P.ProvinceID
GO