/**
* 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.
*/
CREATE PROCEDURE [dbo].[GenerateAccountingBatches_XLedger]
    @inBatchDescription nvarchar(64),
    @inBatProcessAcctTransctionsMap BATProcessAcctTransactions readonly,
    @inSubmittedBySysUserId int,
    @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 it 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
    )
    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
    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

    -- 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
GO
GRANT EXECUTE ON  [dbo].[GenerateAccountingBatches_XLedger] TO [MssExec]
GO