create procedure [dbo].[GetScheduledMessagesForUser]
    @inSysUserId int
as
begin
set nocount on

    select
        SakariScheduledMessage.SakariScheduledMessageID,
        SakariContact.SakariContactID,
        SakariConversation.SakariConversationID,
        SakariContact.ContactName,
        SakariScheduledMessage.TextMessage,
        SakariContact.PhoneNumber,
        OrderIdentifier =
        coalesce(
            Orders.OrderNo,
            Orders.ArchivedOrderNo + ' [A]',
            dbo.FormatFirstNameLastName(Orders.LastName, Orders.FirstName ) ),
        SakariScheduledMessage.ToBeSentAt,
        SakariScheduledMessage.ErrorMessage
    from SakariScheduledMessage
        inner join SakariContact on SakariScheduledMessage.SakariContactFID = SakariContact.SakariContactID
        inner join SakariAccount on SakariContact.SakariAccountFID = SakariAccount.SakariAccountID
        inner join SakariContactSysuserMap on SakariContact.SakariContactID = SakariContactSysuserMap.SakariContactFID
        left outer join SakariConversation on SakariContact.SakariContactID = SakariConversation.SakariContactFID
        left outer join SakariContactOrderMap on SakariContact.SakariContactID = SakariContactOrderMap.SakariContactFID
        left outer join Orders on SakariContactOrderMap.OrderFID = Orders.PriKey
        left outer join SakariOutgoingMessage on SakariScheduledMessage.SakariScheduledMessageID = SakariOutgoingMessage.SakariScheduledMessageFID
    where
        SakariContactSysuserMap.SysuserFID = @inSysuserId and
        SakariContactSysuserMap.Subscribed = 1 and
        SakariOutgoingMessage.SakariOutgoingMessageID is null
end
GO
GRANT EXECUTE ON  [dbo].[GetScheduledMessagesForUser] TO [MssExec]
GO