/**
* Don't use this stored proc directly but instead use GenerateAccountingBatches_Synonym.
* GenerateAccountingBatches_Synonym will either point to this view or another view named like GenerateAccountingBatches_*
*
* 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_Bc]
    @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
    -- 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 BcTransactionBatch
    (
        [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 into BcTransactionBatchItem
    (


        BcTransactionBatchFid,
        AcctTransactionFid,
        ProjectCode, -- can revisit length
        ProjectDimensionValueBcId,
        NominalAccountBcId,
        OpposingAccountBcId,
        CustomerBcId,
        VendorBcId,
        MoveTypeDimensionValueBcId,
        NominalBranchDimensionValueBcId,
        OpposingBranchDimensionValueBcId,
        NominalDivisionDimensionValueBcId,
        OpposingDivisionDimensionValueBcId,
        ArDriverSalesDimensionValueBcId,
        RevenueDriverSalesDimensionValueBcId
    )
    select
        BcTransactionBatchFid = @outXLedgerJournalEntryBatchId,
        AcctTransactionFid = BATProcessAcctTransactionMap.AcctTransactionsID,
        ProjectCode = ProjectCode.Code,
        ProjectDimensionValueBcId = ProjectDimensionValue.BcId, -- join in dimensions based on the code
        NominalAccountBcId = NominalBcAccount.BcId,
        OpposingAccountBcId = OpposingBcAccount.BcId,
        CustomerBcId = BcCustomer.BcId,
        VendorBcId = BcVendor.BcId,
        MoveTypeDimensionValueBcId = MoveTypeBcDimensionValue.BcId,
        NominalBranchDimensionValueBcId = NominalBranchDimensionValue.BcId,
        OpposingBranchDimensionValueBcId = OpposingBranchDimensionValue.BcId,
        NominalDivisionDimensionValueBcId = NominalDivisionBcDimensionValue.BcId,
        OpposingDivisionDimensionValueBcId = OpposingDivisionBcDimensionValue.BcId,
        ArDriverSalesDimensionValueBcId = SalespersonDimensionValue.BcId,
        RevenueDriverSalesDimensionValueBcId = SysUserRevenueDimensionValue.BcId
    from @inBatProcessAcctTransctionsMap as BATProcessAcctTransactionMap
    inner join AcctTransactions on BATProcessAcctTransactionMap.AcctTransactionsID = AcctTransactions.ATPriKey
    inner join BATProcess on BATProcess.BATProcessPriKey = BATProcessAcctTransactionMap.BATProcessID
    left outer join Orders on AcctTransactions.OrdPriKey = Orders.PriKey
    cross apply (
        select Code = isnull( Orders.OrderNo, 'NONE' ) --BCTD: OrderSeg or order number?
    ) as ProjectCode
    cross join BcDimensionSetup
    left outer join BcDimensionValue ProjectDimensionValue
        on ProjectCode.Code = ProjectDimensionValue.Code
        and BcDimensionSetup.ProjectBcDimensionFid = ProjectDimensionValue.BcDimensionFid
    inner join BcBranchDimensionValueMap NominalBcBranchDimensionValueMap
        on BATProcess.NominalBranchFid = NominalBcBranchDimensionValueMap.BranchFid
    inner join BcDimensionValue NominalBranchDimensionValue
        on NominalBcBranchDimensionValueMap.BcDimensionValueFid = NominalBranchDimensionValue.BcDimensionValueId
    inner join BcBranchDimensionValueMap OpposingBcBranchDimensionValueMap
        on BATProcess.OpposingBranchFid = OpposingBcBranchDimensionValueMap.BranchFid
    inner join BcDimensionValue OpposingBranchDimensionValue
        on OpposingBcBranchDimensionValueMap.BcDimensionValueFid = OpposingBranchDimensionValue.BcDimensionValueId
    left outer join BcDivisionDimensionValueMap NominalBcDivisionDimensionValueMap
        on BATProcess.NominalDivisionFid = NominalBcDivisionDimensionValueMap.DivisionFid
    left outer join BcDimensionValue NominalDivisionBcDimensionValue
        on NominalBcDivisionDimensionValueMap.BcDimensionValueFid = NominalDivisionBcDimensionValue.BcDimensionValueId
    left outer join BcDivisionDimensionValueMap OpposingBcDivisionDimensionValueMap
        on BATProcess.OpposingDivisionFid = OpposingBcDivisionDimensionValueMap.DivisionFid
    left outer join BcDimensionValue OpposingDivisionBcDimensionValue
        on OpposingBcDivisionDimensionValueMap.BcDimensionValueFid = OpposingDivisionBcDimensionValue.BcDimensionValueId
    left outer join MoveType on Orders.MoveType = MoveType.PriKey
    left outer join BcMoveTypeGroupDimensionValueMap
        on MoveType.MTGroupPriKey = BcMoveTypeGroupDimensionValueMap.MoveTypeGroupFid
    left outer join BcDimensionValue MoveTypeBcDimensionValue
        on BcMoveTypeGroupDimensionValueMap.BcDimensionValueFid = MoveTypeBcDimensionValue.BcDimensionValueId
    inner join AccountingAccount NominalAccount on BATProcess.NominalAccountingAccountFid = NominalAccount.AccountingAccountId
    inner join BcAccount NominalBcAccount on NominalAccount.AccountingAccountId = NominalBcAccount.AccountingAccountFid
    inner join AccountingAccount OpposingAccount on BATProcess.OpposingAccountingAccountFid = OpposingAccount.AccountingAccountId
    inner join BcAccount OpposingBcAccount on OpposingAccount.AccountingAccountId = OpposingBcAccount.AccountingAccountFid
    left outer join BcCustomer on BATProcess.AccountingCustomerFid = BcCustomer.AccountingCustomerFid
    left outer join BcVendor on BATProcess.AccountingVendorFid = BcVendor.AccountingVendorFid
    left outer join BcSysUserDimensionValueMap SalespersonDimensionValueMap
        on Orders.SalesPerson = SalespersonDimensionValueMap.SysUserFid
    left outer join BcDimensionValue SalespersonDimensionValue
        on SalespersonDimensionValueMap.BcDimensionValueFid = SalespersonDimensionValue.BcDimensionValueId
    outer apply dbo.BcDetermineSysUserRevenueDimensionValue( AcctTransactions.ATPriKey ) SysUserRevenueDimensionValue

    if( @inSendToAccountingSystem = 1 )
    begin
        -- Queue this batch up for uploading and attempted posting in BC.
        -- One queue per batch
        insert into BcTransactionBatchQueue
        (
            BcTransactionBatchFid
        )
        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 = 'BcTransactionUploadRequested'
    end
end
GO
GRANT EXECUTE ON  [dbo].[GenerateAccountingBatches_Bc] TO [MssExec]
GO