CREATE PROCEDURE [dbo].[XLedgerGetEnqueuedAccountTransactionApplyTos]
    @queueId int
AS
BEGIN
    set nocount on

    select
        XLedgerJournalEntryBatchItemId = XLedgerJournalEntryBatchItem.XLedgerJournalEntryBatchItemId,
        ApplyToJournalEntryBatchItemId = ApplyToAccountTransaction.ApplyToJournalEntryBatchItemId,
        Id = AcctTransactionsApplyToInfo.AcctTransactionsApplyToInfoID,
        MatchIdentifier = ApplyToAccountTransaction.MatchIdentifier,
        Amount = dbo.AcctTransactionsApplyToInfo.ApplyAmount,
        AccountExternalDbId = ApplyToAccountTransaction.AccountExternalDbId,
        BranchObjectValueExternalDbId = ApplyToAccountTransaction.BranchObjectValueXLedgerDbId,
        BranchCompanyExternalDbId = ApplyToAccountTransaction.BranchCompanyExternalDbId,
        XLedgerSubledgerExternalDbId = ApplyToAccountTransaction.XLedgerSubledgerExternalDbId,
        XglExternalDbId = ApplyToAccountTransaction.XglExternalDbId,
        [Description] = ApplyToAccountTransaction.[Description]

    from XLedgerJournalEntryBatchQueue
    inner join XLedgerJournalEntryBatchItem on
         XLedgerJournalEntryBatchQueue.XLedgerJournalEntryBatchFid = XLedgerJournalEntryBatchItem.XLedgerJournalEntryBatchFid
    inner join AcctTransactions on XLedgerJournalEntryBatchItem.AcctTransactionFid = AcctTransactions.ATPriKey
    inner join AcctTransactionsApplyToInfo on AcctTransactions.ATPriKey = AcctTransactionsApplyToInfo.AcctTransactionsFID
    cross apply (

        --There's no schema guarantee, so we're doing a cross-apply to ensure we don't expand a single AcctTransactionsApplyToInfo into multiple records on accident.
        select top 1
            MatchIdentifier = XLedgerTransactionHeader.ArApMatchIdentifier,
            AccountExternalDbId = XLedgerAccount.ExternalDbId,
            BranchObjectValueXLedgerDbId = ApplyToJournalBatchItem.OpposingBranchObjectValueXLedgerDbId,
            BranchCompanyExternalDbId = ApplyToJournalBatchItem.OpposingXLedgerCompanyXLedgerDbId,
            XLedgerSubledgerExternalDbId = ApplyToJournalBatchItem.XLedgerCustomerXLedgerDbId,
            XglExternalDbId = Xgl.XLedgerDbId,
            ApplyToJournalEntryBatchItemId = ApplyToJournalBatchItem.XLedgerJournalEntryBatchItemId,

            -- Typically (at time of writing) this is something like "Cash Receipt - FYMF-12345"
            [Description] = AcctTransactions.ItemDescription
        from AcctTransactions ApplyToAccountTransaction
        inner join dbo.XLedgerJournalEntryBatchItem ApplyToJournalBatchItem on
            ApplyToAccountTransaction.ATPriKey = ApplyToJournalBatchItem.AcctTransactionFid
        left outer join XLedgerTransactionHeader on
            ApplyToJournalBatchItem.XLedgerTransactionHeaderFid = XLedgerTransactionHeader.XLedgerTransactionHeaderId and
            XLedgerTransactionHeader.ArApMatchIdentifier is not null -- safety precaution
        inner join XLedgerAccount on ApplyToJournalBatchItem.OpposingAccountingAccountFid = XLedgerAccount.AccountingAccountFid
        inner join Xgl on ApplyToJournalBatchItem.XglFid = Xgl.XglId

        --Must be either part of this same batch OR was previously successfully uploaded
        where
        ApplyToAccountTransaction.DocumentNumber = AcctTransactionsApplyToInfo.ApplyToDocument and
            ( ApplyToJournalBatchItem.XLedgerJournalEntryBatchFid = XLedgerJournalEntryBatchItem.XLedgerJournalEntryBatchFid or
              XLedgerTransactionHeader.ArApMatchIdentifier is not null )
        order by ApplyToAccountTransaction.ATPriKey desc -- if for some reason there's more than one account transaction (probably an invalid state) then use the latest.
    ) ApplyToAccountTransaction
    where XLedgerJournalEntryBatchQueue.XLedgerJournalEntryBatchQueueId = @queueId

end
GO
GRANT EXECUTE ON  [dbo].[XLedgerGetEnqueuedAccountTransactionApplyTos] TO [MssExec]
GO