create function [dbo].[GetOrderThirdPartyErrors](@inOrderId int)
returns @xmlimportErrorsDetail table(
    Id int,
    ErrorDate datetime,
    DataDescription varchar(max),
    Identifier varchar (111),
    XmlSystemDataTypeMapId int,
    AppDataTypeId int,
    ErrorCodeId int,
    OrderId int,
    OrderBranchId int,
    HasXmlData bit,
    IsVanLineOrderError bit )
as
begin

    declare @OrderNo varchar (23)= null;
    declare @EstimateNo varchar (32)= null;
    declare @OrderSeg varchar (16)= null;
    declare @OrderSegPOS varchar (max)= null;
    -- getting order related detail(if orderId provided).
    --This detail will be used to filter out errors(if any xml error identifier match with OrderNo, EstimateNo,OrderSeg,OrderSegPOS )
    select
        @OrderNo = Orders.OrderNo,
        @EstimateNo = Orders.EstimateNo,
        @OrderSeg = Orders.OrderSeg,
        @OrderSegPOS = isnull(Orders.OrderSeg,'')+'-'+isnull(Orders.PSeg,' ')+'-'+isnull(Orders.OSeg,' ')+'-' + isnull(Orders.SSeg, ' ')  from Orders
    where Orders.PriKey = @inOrderId;

    insert into @xmlimportErrorsDetail
        select
        Distinct
        Id = XmlImportError.XmlImportErrorId,
        ErrorDate = XmlImportError.DateDownloaded,
        DataDescription = XmlImportError.DataDescription,
        Identifier = XmlImportError.XmlID,
        XmlSystemDataTypeMapId = XmlImportError.XmlSystemDataTypeMapFID,
        AppDataTypeId = XmlImportError.XmlSystemDataTypeMapFID,
        ErrorCodeId = XmlImportError.ErrorCodeFID,
        OrderId = XmlImportError.OrderFID,
        OrderBranchId = Orders.BranchPriKey,
        HasXmlData = convert( bit, case when XmlImportError.XMLData is null then 0 else 1 end ),
        IsVanLineOrderError = XmlImportError.IsVanLineOrderErrors
        from XmlImportError
        left outer join Orders on Orders.PriKey = XmlImportError.OrderFID
        where     (@inOrderId is null or
            (XmlImportError.OrderFID = @inOrderId or
                (XmlImportError.XmlID  = @OrderNo or
                XmlImportError.XmlID  = @EstimateNo or
                XmlImportError.XMLID =  @OrderSeg or
                XmlImportError.XMLID =  @OrderSegPOS )
    ))
    return;
end
GO
GRANT SELECT ON  [dbo].[GetOrderThirdPartyErrors] TO [MssExec]
GO