create function [dbo].[GetGen2AccountingInfoForTransaction]
(
    @acctTransactionId int
)
returns table as return
(
    select top 1
        NominalAccountingAccountId = XLedgerJournalEntryBatchItem.NominalAccountingAccountFid,
        OpposingAccountingAccountId = XLedgerJournalEntryBatchItem.OpposingAccountingAccountFid,
        XglId = XLedgerJournalEntryBatchItem.XglFid,
        AccountingCustomerId = XLedgerCustomer.AccountingCustomerFid,
        BatchCreatedAt = XLedgerJournalEntryBatch.CreatedOn,
        BatchCompletedAt = XLedgerJournalEntryBatch.CompletedOn
    from XLedgerJournalEntryBatchItem
    left outer join XLedgerCustomer on  XLedgerJournalEntryBatchItem.XLedgerCustomerXLedgerDbId = XLedgerCustomer.ExternalDbId
    inner join XLedgerJournalEntryBatch on XLedgerJournalEntryBatchItem.XLedgerJournalEntryBatchFid = XLedgerJournalEntryBatch.XLedgerJournalEntryBatchId
    where
        XLedgerJournalEntryBatchItem.AcctTransactionFid = @acctTransactionId
        and XLedgerJournalEntryBatchItem.XLedgerTransactionHeaderFid is not null
    order by XLedgerJournalEntryBatchItem.XLedgerJournalEntryBatchItemId desc
    union all
    select
        NominalAccountingAccountId = NominalBcAccount.AccountingAccountFid,
        OpposingAccountingAccountId = OpposingBcAccount.AccountingAccountFid,
        XglId = null,
        AccountingCustomerId = BcCustomer.AccountingCustomerFid,
        BatchCreatedAt = BcTransactionBatch.CreatedOn,
        BatchCompletedAt = BcTransactionBatch.CompletedOn
    from BcTransactionBatchItem
    inner join BcAccount NominalBcAccount on BcTransactionBatchItem.NominalAccountBcId = NominalBcAccount.BcId
    inner join BcAccount OpposingBcAccount on BcTransactionBatchItem.OpposingAccountBcId = OpposingBcAccount.BcId
    left outer join BcCustomer on BcTransactionBatchItem.CustomerBcId = BcCustomer.BcId
    inner join BcTransactionBatch on BcTransactionBatchItem.BcTransactionBatchFid = BcTransactionBatch.BcTransactionBatchId
    where BcTransactionBatchItem.AcctTransactionFid = @acctTransactionId
)
GO
GRANT SELECT ON  [dbo].[GetGen2AccountingInfoForTransaction] TO [MssExec]
GO