/**
* Don't use this stored proc directly but instead use GenerateAccountingBatches_Synonym.
* GenerateAccountingBatches_Synonym will either point to this view or to
* GenerateAccountingBatches_Legacy otherwise.
*
* Please review the ReEnqueueFailedTransactions stored proc should you need to make
* changes here that may also need to be made there.  ReEnqueueFailedTransactions basically
* creates new batches and new BatchItems that point to the AcctTransactions that failed
* to upload to XLedger.
*/
CREATE PROCEDURE [dbo].[GenerateAccountingBatches_XLedger]
    @inBatchDescription nvarchar(64),
    @inBatProcessAcctTransctionsMap BATProcessAcctTransactions readonly,
    @inSubmittedBySysUserId int,
    @inSendToAccountingSystem bit = 1,
    @outErrorCode int output,
    @outXLedgerJournalEntryBatchId int output
as
begin
    set nocount on

    set @outErrorCode = 0

    -- Insert our XLedger batch header.
    -- =================================================================================
    -- While @inSubmittedBySysUserId should always be valid, it's possible someone could
    -- modify a user between when the user logged in and when this code executes.  The
    -- SubmittedBySysUserFid column allows nulls.  We do need to make sure that we always
    -- insert a row into XLedgerJournalEntryBatch and that the SysUser is only inserted
    -- if it is valid.
    ;with SubmittedBySysUser as
    (
        select
            SysUserId = @inSubmittedBySysUserId
    )
    insert into XLedgerJournalEntryBatch
    (
        [Description],
        SubmittedBySysUserFid,
        CreatedOn
    )
    select
        [Description] = @inBatchDescription,
        -- It's possible that the submitting SysUserId is invalid since it is looked up when the user logs
        -- into MoversSuite and an admin could merge it to some other Id or just delete it (we think this
        -- would be very rare).  If it does happen, this insert will still work but we wouldn't know who
        -- generated this and we wouldn't know to whom we should report any errors to (if that occurs).
        SubmittedBySysUserFid = SysUser.SysUserId,
        CreatedOn = sysdatetimeoffset()
    from SubmittedBySysUser
    left outer join SysUser on SysUser.SysUserId = SubmittedBySysUser.SysUserId

    set @outXLedgerJournalEntryBatchId = scope_identity()

    -- Insert our XLedger batch details.
    -- One detail per AcctTransaction.
    insert into XLedgerJournalEntryBatchItem
    (
        XLedgerJournalEntryBatchFid,
        AcctTransactionFid,
        XglFid,
        NominalXLedgerCompanyXLedgerDbId,
        OpposingXLedgerCompanyXLedgerDbId,
        NominalBranchObjectValueXLedgerDbId,
        OpposingBranchObjectValueXLedgerDbId,
        -- XLTD: These will likely be the appropriate XLedgerDivisionObjectValue.XLedgerDbId
        --NominalDivisionFid,
        --OpposingDivisionFid,
        NominalAccountingAccountFid,
        OpposingAccountingAccountFid,
        XLedgerCustomerXLedgerDbId,
        XLedgerSupplierXLedgerDbId,
        RequestGroupIdentifier,
        XLedgerBankAccountFid
    )
    select
        XLedgerJournalEntryBatchFid = @outXLedgerJournalEntryBatchId,
        AcctTransactionFid = BATProcessAcctTransactionMap.AcctTransactionsID,
        XglFid = BATProcess.XglFid,
        NominalXLedgerCompanyXLedgerDbId = NominalXLedgerCompany.XLedgerDbId,
        OpposingXLedgerCompanyXLedgerDbId = OpposingXLedgerCompany.XLedgerDbId,
        NominalBranchObjectValueXLedgerDbId = NominalXLedgerBranchObjectValue.XLedgerDbId,
        OpposingBranchObjectValueXLedgerDbId = OpposingXLedgerBranchObjectValue.XLedgerDbId,
        --NominalDivisionFid = BATProcess.NominalDivisionFid,
        --OpposingDivisionFid = BATProcess.OpposingDivisionFid,
        NominalAccountingAccountFid = BATProcess.NominalAccountingAccountFid,
        OpposingAccountingAccountFid = BATProcess.OpposingAccountingAccountFid,
        XLedgerCustomerXLedgerDbId = XLedgerCustomer.ExternalDbId,
        XLedgerSupplierXLedgerDbId = XLedgerSupplier.ExternalDbId,
        RequestGroupIdentifier = BATProcess.RequestGroupIdentifier,
        XLedgerBankAccountFid = XLedgerBankAccount.XLedgerBankAccountId
    from @inBatProcessAcctTransctionsMap as BATProcessAcctTransactionMap
    inner join BATProcess on BATProcess.BATProcessPriKey = BATProcessAcctTransactionMap.BATProcessID
    inner join XLedgerBranchObjectValue as NominalXLedgerBranchObjectValue on
        NominalXLedgerBranchObjectValue.BranchFid = BATProcess.NominalBranchFid
    inner join XLedgerCompany as NominalXLedgerCompany on
        NominalXLedgerCompany.XLedgerCompanyId = NominalXLedgerBranchObjectValue.XLedgerCompanyFid
    inner join XLedgerBranchObjectValue as OpposingXLedgerBranchObjectValue on
        OpposingXLedgerBranchObjectValue.BranchFid = BATProcess.OpposingBranchFid
    inner join XLedgerCompany as OpposingXLedgerCompany on
        OpposingXLedgerCompany.XLedgerCompanyId = OpposingXLedgerBranchObjectValue.XLedgerCompanyFid
    left outer join XLedgerCustomer on XLedgerCustomer.AccountingCustomerFid = BATProcess.AccountingCustomerFid
    left outer join XLedgerSupplier on XLedgerSupplier.AccountingVendorFid = BATProcess.AccountingVendorFid
    left outer join XLedgerBankAccount on XLedgerBankAccount.AccountingBankAccountFid = BATProcess.AccountingBankAccountFid

    if( @inSendToAccountingSystem = 1 )
    begin
        -- All done!  Queue this batch up for processing to XLedger.
        -- One queue per XLedgerJournalEntryBatch.
        insert into XLedgerJournalEntryBatchQueue
        (
            XLedgerJournalEntryBatchFid
        )
        select
            XLedgerJournalEntryBatchFid = @outXLedgerJournalEntryBatchId

        -- And lastly tell MssWeb that there is work to be done.
        insert into MssWebEvent( MssWebEventTypeFid )
        select MssWebEventType.MssWebEventTypeId
        from MssWebEventType
        where MssWebEventType.EventTypeName = 'XLedgerTransactionUploadRequested'
    end
end
GO
GRANT EXECUTE ON  [dbo].[GenerateAccountingBatches_XLedger] TO [MssExec]
GO