CREATE procedure [dbo].[MssWebGetConversationListByUser]
    @inSysUserId int
as
begin
    set nocount on
    declare @subscribedOnly bit  = 1;
    select
        OrderId = Orders.PriKey,
        OrderNumber = coalesce(
        Orders.OrderNo,
        Orders.ArchivedOrderNo + ' [A]' ) ,
        CustomerName = dbo.FormatFirstNameLastName(Orders.FirstName ,Orders.LastName),
        ContactPriKey = SakariContact.SakariContactID,
        UpdateCount = ContactMessagesCount.UpdateCount ,
        ScheduledMessageCount = ContactMessagesCount.ScheduledMessageCount,
        LastUpdatedAt = SakariContact.LastUpdatedAt
    from  [dbo].udfGetUnreadAndScheduledMessageCount(@inSysUserId,@subscribedOnly) ContactMessagesCount
    inner join SakariContact on ContactMessagesCount.ContactID = SakariContact.SakariContactID
    inner join SakariContactOrderMap on ContactMessagesCount.ContactID = SakariContactOrderMap.SakariContactFID
    inner join Orders on SakariContactOrderMap.OrderFID = Orders.PriKey
    order by LastUpdatedAt desc, OrderNumber asc
end
GO
GRANT EXECUTE ON  [dbo].[MssWebGetConversationListByUser] TO [MssExec]
GO