/*
* Gets all Xml System Requests that have a status of "Queued".
*/
create procedure [dbo].[GetPendingXmlSystemRequests]
as

declare @queuedStatus int
declare @processingStatus int
select @queuedStatus = XmlSystemRequestStatusId
from XmlSystemRequestStatus
where [Name] = 'Queued'
select @processingStatus = XmlSystemRequestStatusId
from XmlSystemRequestStatus
where [Name] = 'Processing'

declare @queuedRequests table( XmlSystemRequestQueueId int )

update XmlSystemRequestQueue set
    XmlSystemRequestStatusFid = @processingStatus
output deleted.XmlSystemRequestQueueId
into @queuedRequests
where XmlSystemRequestStatusFid = @queuedStatus

select
    XmlSystemRequestQueue.XmlSystemRequestQueueId,
    dbo.XmlSystemDataTypeMap.[Name] as XmlSystemDataTypeMap,
    XmlSystemRequestStatus.[Name]    as XmlSystemRequestStatus,
    xmlSystemRequestType.[Name]    as XmlSystemRequestType,
    dbo.FormatFirstNameLastName( SysUser.FIRSTNAME, SysUser.LASTNAME ) as SysUserName,
    Orders.OrderNo as OrderNumber,
    Orders.PriKey as OrderId
from @queuedRequests as QueuedRequests
inner join XmlSystemRequestQueue on XmlSystemRequestQueue.XmlSystemRequestQueueId = QueuedRequests.XmlSystemRequestQueueId
inner join Sysuser on XmlSystemRequestQueue.SysUserFId = Sysuser.SysUserID
inner join dbo.Orders on XmlSystemRequestQueue.OrderFId = Orders.PriKey
inner join dbo.XmlSystemDataTypeMap on XmlSystemRequestQueue.XmlSystemDataTypeMapFId = XmlSystemDataTypeMap.XmlSystemDataTypeMapId
inner join dbo.XmlSystemRequestType on XmlSystemRequestQueue.XmlSystemRequestTypeFId = XmlSystemRequestType.XmlSystemRequestTypeId
inner join dbo.XmlSystemRequestStatus on XmlSystemRequestQueue.XmlSystemRequestStatusFId = XmlSystemRequestStatus.XmlSystemRequestStatusId
GO
GRANT EXECUTE ON  [dbo].[GetPendingXmlSystemRequests] TO [MssExec]
GO