Excel Import and Journal Processing in D365 F&O

Part Two: Creating and Posting the Journal Entries

Introduction

Having successfully read the imported Excel file and passed the values as arguments to the helper class, we will now use these values to create a journal entry, create journal lines, and automatically post the journal.

Create the Journal

In this step, we initialize and create a new journal using the provided journal name and description. The createJournal method sets up the ledger journal table, assigns a journal type, name, and description, and inserts into LedgerjournalTable table.

public static Name createJournal(Name journalName, Name journalDesc)
{
    LedgerJournalTable lTable;
    Name jName;

    ttsbegin;
    lTable.clear();
    lTable.JournalType = LedgerJournalName::find(journalName).JournalType;
    lTable.JournalName = journalName;
    lTable.Name = journalDesc;
    lTable.initFromLedgerJournalName();
    lTable.JournalNum = JournalTableData::newTable(lTable).nextJournalId();
    lTable.insert();
    ttscommit;

    jName = lTable.JournalNum;
    return jName;
}

Create Journal Lines

This step involves adding lines to the journal. The createLines method takes various parameters, including the journal number, payment modes, account numbers, dimensions, description, and amount. It generates vouchers, sets ledger and offset accounts, and inserts the journal lines into LedgerJournalTrans table.


public static void createLines(Name JournNo, str paymentModes,
    MainAccountNum mainAccountNum, DimensionValue dimensionValue1, DimensionValue dimensionValue2,
    Name _desc, LedgerJournalACType accType, Int64 _ledger,
    LedgerJournalACType offSetAccType, int64 _offsetLedger, AmountMST amount,
    CurrencyCode curCode, Name journalName, Name cashAdvNum = "", Name expNum = "")
{
    NumberSequenceTable numSeqtable;
    NumberSeq num;
    ttsbegin;
    LedgerJournalTrans ltrans;

    numSeqtable = NumberSequenceTable::find(LedgerJournalName::find(journalName).NumberSequenceTable);

    ltrans.clear();
    ltrans.JournalNum = JournNo;
    num = NumberSeq::newGetVoucherFromCode(numSeqtable.NumberSequence, NumberSeqScopeFactory::createDataAreaScope(), true);
    ltrans.voucher = num.voucher();
    num.used();
    ltrans.CurrencyCode = CompanyInfoHelper::standardCurrency();
    ltrans.TransDate = today();
    ltrans.Txt = _desc;
    ltrans.AccountType = accType;
    ltrans.AmountCurDebit = amount;
    ltrans.CurrencyCode = curCode;

    DimensionDefault defaultDimension = BankChargesJournalCreation::createDefaultDimension(mainAccountNum, dimensionValue1, dimensionValue2);

    LedgerDimensionAccount defaultAccount = LedgerDefaultAccountHelper::getDefaultAccountFromMainAccountId(mainAccountNum);
    if (!defaultAccount)
    {
        throw error(strFmt("Cannot find main account %1", mainAccountNum));
    }
    ltrans.LedgerDimension = LedgerDimensionFacade::serviceCreateLedgerDimension(defaultAccount, defaultDimension);
    if (!ltrans.LedgerDimension)
    {
        throw error("Invalid ledger dimension");
    }

    ltrans.OffsetAccountType = offSetAccType;
    ltrans.PaymReference = JournNo;
    ltrans.ExchRate = 100;

    DimensionDefault offsetDefaultDimension = BankChargesJournalCreation::createDefaultDimension(mainAccountNum, dimensionValue1, dimensionValue2);
    ltrans.OffsetDefaultDimension = offsetDefaultDimension;
    ltrans.OffsetLedgerDimension = _offsetLedger;

    ltrans.PaymMode = paymentModes;
    ltrans.insert();

    ttscommit;
}

Get Cost Centre and Source of Fund

The helper methods getCostCentre and getSourceOfFund are used to retrieve the names of the cost center and source of fund dimensions.

static protected Name getCostCentre()
{
    return 'CostCenter';
}

static protected Name getSourceOfFund()
{
    return 'SourceOfFund';
}

Create Default Dimension

The createDefaultDimension method helps in setting up the default dimension for the ledger accounts.

public static DimensionDefault createDefaultDimension(MainAccountNum mainAccountNum, DimensionValue dimensionValue1, DimensionValue dimensionValue2)
{
    DimensionDefault defaultDimension;

    defaultDimension = BankChargesJournalCreation::setValueToDefaultDimension(0, BankChargesJournalCreation::getCostCentre(), dimensionValue1);
    defaultDimension = BankChargesJournalCreation::setValueToDefaultDimension(defaultDimension, BankChargesJournalCreation::getSourceOfFund(), dimensionValue2);

    return defaultDimension;
}

Set Value to Default Dimension

This step involves assigning specific values to the default dimension using the setValueToDefaultDimension method.

public static DimensionDefault setValueToDefaultDimension(DimensionDefault _dimensionDefault, Name _dimensionName, DimensionValue _newDimensionValue)
{
    return BankChargesJournalCreation::setDefaultDimensionRecId(_dimensionDefault, DimensionAttribute::findByName(_dimensionName).RecId, _newDimensionValue);
}

public static DimensionDefault setDefaultDimensionRecId(DimensionDefault _dimensionDefault, RefRecId _dimensionAttributeRecId, DimensionValue _newDimensionValue)
{
    DimensionAttributeValueSetStorage dimStorage;
    DimensionAttributeValue dimensionAttributeValue;

    if (_dimensionAttributeRecId)
    {
        dimStorage = DimensionAttributeValueSetStorage::find(_dimensionDefault);
        if (_newDimensionValue)
        {
            dimensionAttributeValue = DimensionAttributeValue::findByDimensionAttributeAndValue(DimensionAttribute::find(_dimensionAttributeRecId), _newDimensionValue, false, true);
            dimStorage.addItem(dimensionAttributeValue);
        }
        else
        {
            dimStorage.removeDimensionAttribute(_dimensionAttributeRecId);
        }
        _dimensionDefault = dimStorage.save();
    }
    return _dimensionDefault;
}

Post the Journal

Once the journal and its lines are created, the postLedgerJournal method is used to post the journal.

public static void postLedgerJournal(Name journalNum)
{
    LedgerJournalTable ledgerJournalTable;
    select firstOnly RecId from ledgerJournalTable where ledgerJournalTable.JournalNum == journalNum;

    if (ledgerJournalTable.RecId)
    {
        LedgerJournalCheckPost ledgerJournalCheckPost = LedgerJournalCheckPost::newLedgerJournalTable(ledgerJournalTable, NoYes::Yes);
        ledgerJournalCheckPost.run();
    }
}

Conclusion

In conclusion, by following both Part One and Part Two of this article, you will be able to upload an Excel file, create a journal and its lines, and post the journal. You can then view the created and posted journal in the Customer Payment Journal.

https://learn.microsoft.com/en-us/dynamics365/finance/general-ledger/Default-dimensions

https://dynamicspedia.com/2020/12/tips-on-ledger-default-dimension-development-on-forms/

Excel Import and Journal Processing in D365 F&O - Part Two

Table of contents