/*
*    Description: Fetches the most recent XLedgerOnDemandSyncHistory records and displays the
*    time for these events in the user's preferred time zone.
*/
CREATE PROCEDURE [dbo].[GetXLedgerOnDemandSyncHistory]
    @topN int,
    @userIdForBestTimeZone int,
    @onlyRetrieveUnprocessed bit
as
set nocount on

declare @outBestTimeZone varchar(64);
exec GetBestUserTimeZoneName
    @inSysUserId = @userIdForBestTimeZone,
    @inGetServerTimeZoneIfNeeded = 1,
    @outBestTimeZone = @outBestTimeZone output

select top( @topN )
    XLedgerOnDemandSyncHistory.XLedgerOnDemandSyncHistoryId,
    XLedgerOnDemandSyncHistory.InvokingSysUserFid,
    XLedgerOnDemandSyncHistory.XLedgerOnDemandSyncTypeFid,
    EnqueuedAt = dbo.ConvertDateTimeOffsetToBestTimeZone( XLedgerOnDemandSyncHistory.EnqueuedAt, @outBestTimeZone ),
    ProcessedState = case
        when XLedgerOnDemandSyncHistory.StartedAt is null then 'Pending'
        when XLedgerOnDemandSyncHistory.CompletedAt is null then 'Processing'
        when XLedgerOnDemandSyncHistory.FailureMessage is not null then 'Unsuccessful'
        else 'Completed'
    end,
    StartedAt = dbo.ConvertDateTimeOffsetToBestTimeZone( XLedgerOnDemandSyncHistory.StartedAt, @outBestTimeZone ),
    CompletedAt = dbo.ConvertDateTimeOffsetToBestTimeZone( XLedgerOnDemandSyncHistory.CompletedAt, @outBestTimeZone ),
    XLedgerOnDemandSyncHistory.FailureMessage,
    InvokingSysUserName = dbo.FormatFirstNameLastName( Sysuser.FIRSTNAME, SysUser.LASTNAME ),
    SyncTypeDescription = XLedgerOnDemandSyncType.[Description]
from XLedgerOnDemandSyncHistory
inner join SysUser on SysUser.SysUserID = XLedgerOnDemandSyncHistory.InvokingSysUserFid
inner join XLedgerOnDemandSyncType on XLedgerOnDemandSyncType.XLedgerOnDemandSyncTypeId = XLedgerOnDemandSyncHistory.XLedgerOnDemandSyncTypeFid
where XLedgerOnDemandSyncHistory.StartedAt is null or @onlyRetrieveUnprocessed = 0
order by XLedgerOnDemandSyncHistory.XLedgerOnDemandSyncHistoryId desc;
GO
GRANT EXECUTE ON  [dbo].[GetXLedgerOnDemandSyncHistory] TO [MssExec]
GO