create procedure [dbo].[MssWebGetThirdPartyErrors]
    @inSysUserId int,
    @inOrderId int,
    @inFromDate dateTime,
    @inToDate dateTime,
    @inQuery varchar(256)
as
begin
    set nocount on;
    ;with OrderInfoSecurityModule as
    (
        select SecModules.ModulePriKey
        from SecModules where SecModules.Description = 'Order Information' and
        SecModules.Active = 1
    ),
    UserBranchesWithMinAccessLevel as (
        select
            SecProfileDetail.BranchPriKey,
            MinAccessLevel = min(
                case when SecProfileDetail.SecProfilePriKey is null then 0
                when SecProfileDetail.ManagerFlag = 1 then 3
                when SecAccessTypes.Description = 'Read Only' then 1
                else 2
                end
            )
        from UserAccess
        cross join OrderInfoSecurityModule
        inner join SecProfileDetail on
            UserAccess.SecProfilePriKey = SecProfileDetail.SecProfilePriKey and
            OrderInfoSecurityModule.ModulePriKey = SecProfileDetail.ModulePriKey
        inner join SecAccessTypes on SecAccessTypes.AccessPriKey = SecProfileDetail.AccessPriKey
        where UserAccess.SysUserID = @inSysUserId
        group by SecProfileDetail.BranchPriKey
    )
    select top 50
        Id = ThirdPartyErrors.Id,
        ErrorDate = ThirdPartyErrors.ErrorDate,
        Identifier = ThirdPartyErrors.Identifier,
        AppDataTypeId = ThirdPartyErrors.AppDataTypeId,
        AppDataTypeName = XmlSystemDataTypeMap.Name,
        DataTypeName = XmlSystemDataType.Name,
        VendorName = XmlInterface.VendorName,
        ErrorCodeId = ThirdPartyErrors.ErrorCodeId,
        ErrorMessage = case when ErrorCode.[Description] is null then
            isnull(ThirdPartyErrors.DataDescription,'') else
            isnull(ErrorCode.[Description],'') + ' ' + isnull(ThirdPartyErrors.DataDescription,'')
            end,
        LevelDescription = upper( LogLevel.LevelDescription ),
        OrderId = ThirdPartyErrors.OrderId,
        HasXmlData = ThirdPartyErrors.HasXmlData,
        IsVanLineOrderError = ThirdPartyErrors.IsVanLineOrderError,
        OrderMinAccessLevel = isnull(UserBranchesWithMinAccessLevel.MinAccessLevel,0)
    from GetOrderThirdPartyErrors(@inOrderId) as ThirdPartyErrors
    left outer join UserBranchesWithMinAccessLevel on ThirdPartyErrors.OrderBranchId = UserBranchesWithMinAccessLevel.BranchPriKey
    left outer join ErrorCode on ( ErrorCode.ECPriKey = ThirdPartyErrors.ErrorCodeId )
    left outer join LogLevel on ( LogLevel.LogLevelID = ErrorCode.LogLevelFID )
    left outer join XmlSystemDataTypeMap on XmlSystemDataTypeMap.XmlSystemDataTypeMapID = ThirdPartyErrors.XmlSystemDataTypeMapId
    left outer join XmlSystemDataType on XmlSystemDataType.XmlSystemDataTypeID = XmlSystemDataTypeMap.XmlSystemDataTypeFID
    left outer join XmlSystem on ( XmlSystem.XmlSystemID = XmlSystemDataTypeMap.XmlSystemFID )
    left outer join XmlInterface on ( XmlInterface.XmlInterfaceID = XmlSystem.XmlInterfaceFID )
    where (@inFromDate is null or ThirdPartyErrors.ErrorDate >= @inFromDate) and
    (@inToDate is null or ThirdPartyErrors.ErrorDate <= @inToDate) and
    (@inQuery is null or (ThirdPartyErrors.Identifier like '%'+ @inQuery +'%') or
        (XmlSystemDataTypeMap.Name like '%'+ @inQuery +'%') or
        ( ThirdPartyErrors.ErrorCodeId like '%'+ @inQuery  +'%') or
        (ErrorCode.[Description] like '%'+ @inQuery +'%') or
        (ThirdPartyErrors.DataDescription like '%'+ @inQuery +'%') )
    order by ThirdPartyErrors.ErrorDate desc
end
GO
GRANT EXECUTE ON  [dbo].[MssWebGetThirdPartyErrors] TO [MssExec]
GO