CREATE OR ALTER PROCEDURE [Verified].[Pr_LicenceRoutesGet]
	@LicenceID BIGINT,
	@UserID BIGINT
AS
BEGIN
	SET NOCOUNT ON;

	/********************************************************************************************
	Get user details and perform security checks
	*********************************************************************************************/	
	DECLARE @EntityIDToCheck INT = 1011 /*OperatingLicences*/
	DECLARE @ItemIDToCheck BIGINT = NULL
	DECLARE @Action VARCHAR(255) = 'Read'
	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	
	
	/********************************************************************************************
	Perform update
	*********************************************************************************************/
	SELECT Vr.*, Rt.Description AS RouteType, Op.Description OriginDescription,Dp.Description DestinationDescription 
	FROM Verified.Routes Vr
		INNER JOIN Verified.LicenceRoutes Lr ON Vr.RouteID = Lr.RouteID
		INNER JOIN Md.RouteTypes Rt ON Vr.RouteTypeID = Rt.RouteTypeID
		LEFT JOIN Verified.Places Op ON Vr.OriginPlaceID = Op.PlaceID
		LEFT JOIN Verified.Places Dp ON Vr.DestinationPlaceID = Dp.PlaceID
	WHERE Lr.LicenceID = @LicenceID
		AND Vr.RouteStatusID <> 4 /*cancelled*/

END
GO