Mastering general journals

April 8 · 2015 In this article we will look into how to create general journals from code and then on how to post it. We will look at the data model so that we get a deeper understanding of what is happening. DIXF, integration, dimensions and a lot lot more.

In this article we will look into how to create general journals from code and then on how to post it. We will look at the data model so that we get a deeper understanding of what is happening.

For a lot of installations, migration of financial data is an issue. DIXF is a great tool for this if we know how to customize it.

We will also follow up the article about financial dimensions from last week. How are the ledger dimensions and default dimensions transferred to vouchers? Let's find out.

Business perspective

General journal is the most common way to insert financial transaction into AX. So it is important that you understand the basics of using it.

General journal (General Ledger > Journals > General journal > [Button] Lines)

A journal in AX is like a temporary book where you write things down. When you are done, you validate the data and then you post it. Once it is posted, it is like you have sealed it with ink and can not change it. Well, not legally anyway.

If you haven't posted a journal in AX 2012 for a long time, I suggest that you play with it a bit more before going technical.

###Dimensions As we talked about last week, there are Accounts (ledger dimension) and Financial dimensions (default dimension). Financial dimensions are only used only if the account type is not Ledger. Let's look in an example:

In this case you can see that the account is a bank and we complemented with the Business Unit 004 "IT consulting practice". When we post this journal it will become the following voucher: You will notice that the bank account GBSI OPER is translated to main account 110150 and the financial transaction is merged together into the Ledger account field. In the second and third line, which are of account type Ledger, are just posted as they were.

This is how the dimensions are used in general journal and voucher transactions. All other account types (customer, vendor, asset, project) will follow the same pattern as the bank example.

If you wonder how the magic happened for translating bank account to a main account the setting can be found in the form Bank accounts.

Management Reporter

There are many ways to see posted transactions. The easiest way is through Voucher transactions (General Ledger > Inquiries > Voucher transactions) as we seen in the previous example. This is the most detailed way of viewing individual transactions.

New in AX 2012 is the Management Reporter, which is the recommended way to view aggregated voucher data. The strength with MR is that it is highly customizable. Due to the complex data model in finance it requires more effort to customize reports in SSRS. MR on the other hand has a lot of predefined column and row definitions, which make it easy even for non-developers to create their own specialized reports. Management Reporter is a great tool and deserves its own article, which will come soon.

Data Model

We will separate the data model into two entities: general journal and voucher transactions. The first is before posting and the latter is after posting.

###General journal The general journal data model is very simple and consists basically of two three tables: LedgerJournalName, LedgerJournalTable and LedgerJournalTrans. There are of course a lot of reference data like dimensions, exchange rates, tax, parties and so on, but we will leave those in the perifery while focusing on the core.

Table LedgerJournalName LedgerJournalTable LedgerJournalTrans
Label Journal names General journal Journal voucher
Form General ledger > Setup > Journals > Journal names General ledger > Journals > General journals General ledger > Journals > General journals > [button] lines
Function LedgerJournalName keeps the settings for the journal type. Typical settings are voucher number sequence, journal type, journal control, posting restictions, approval and default financial dimensions. Click on the image above to see more. LedgerJournalTable holds the information for each journal. Each record has to have a journal name. Many of the settings e.g. financial dimension will inherit the values from the chosen LedgerJournalName. LedgerJournalTrans is where the journal vouchers gets stored. Each journal can hold unlimited of journal vouchers (unless it is limit through Lines limit on the journal).
Relationship LegerJournalTable. JournalName == LedgerJournalName. JournalName LedgerJournalTrans. JournalNum == LedgerJournalTable. JournalNum

Voucher transactions

After the journal gets posted the voucher transaction creates. Here we will explain three central tables.

Table LedgerEntryJournal GeneralJournalEntry GeneralJournalAccountEntry
Function Keeps relation between LedgerJournalTable and GeneralJournalEntry Holds information for each voucher. Holds information for each transaction with account
Relationship LedgerEntryJournal. JournalNumber == LedgerJournalTable. JournalNum && LedgerEntryJournal. LedgerJournalTableDataArea == LedgerJournalTable.DataAreaId GeneralJournalEntry. LedgerEntryJournal == LedgerEntryJournal.RecId GeneralJournalAccountEntry. GeneralJournalEntry == GeneralJournalEntry.RecId
Class that generates records LedgerVoucher LedgerVoucherObject LedgerVoucherTransObject

Create journals with AIF

The service for creating journals is called LedgerGeneralJournalService. As all other service classes we can use it directly through X++. Here is an example for that.

static void createLedgerJournal(Args _args)
{
    LedgerGeneralJournalService             service;
    LedgerGeneralJournal                    ledgerGeneralJournal;
    LedgerGeneralJournal_LedgerJournalTable ledgerJournalTable;
    LedgerGeneralJournal_LedgerJournalTrans ledgerJournalTrans;

    AfStronglyTypedDataContainerList    list;
    AifMultiTypeAccount                 ledgerDimension;
    AifDimensionAttributeValueSet       defaultDimension;
    AfStronglyTypedDataContainerList    values;
    AifDimensionAttributeValue          value;
    AifEntityKeyList                    keyList;
    ;
    //LedgerGeneralJournal
    ledgerGeneralJournal = new LedgerGeneralJournal();

    //LedgerJournalTable
    ledgerJournalTable = ledgerGeneralJournal.createLedgerJournalTable().addNew();
    ledgerJournalTable.parmJournalName('GenJrn');
    list = ledgerJournalTable.createLedgerJournalTrans();

    //LedgerJournalTrans 1
    ledgerJournalTrans = new LedgerGeneralJournal_LedgerJournalTrans();
    ledgerJournalTrans.parmCompany(curext());
    ledgerJournalTrans.parmAccountType(LedgerJournalACType::Ledger);
    ledgerJournalTrans.parmAmountCurDebit(100);
    list.add(ledgerJournalTrans);

    //LedgerDimension
    ledgerDimension = ledgerJournalTrans.createLedgerDimension();
    ledgerDimension.parmAccount('605150');
    ledgerDimension.parmDisplayValue('605150-004-025--');
    values = ledgerDimension.createValues();
    value = values.addNew();
    value.parmName('BusinessUnit');
    value.parmValue('004');
    value = values.addNew();
    value.parmName('Department');
    value.parmValue('025');

    //LedgerJournalTrans 2
    ledgerJournalTrans = new LedgerGeneralJournal_LedgerJournalTrans();
    ledgerJournalTrans.parmCompany(curext());
    ledgerJournalTrans.parmAccountType(LedgerJournalACType::Bank);
    ledgerJournalTrans.parmAmountCurCredit(100);
    list.add(ledgerJournalTrans);

    //LedgerDimension
    ledgerDimension = ledgerJournalTrans.createLedgerDimension();
    ledgerDimension.parmAccount('GBSI OPER');
    ledgerDimension.parmDisplayValue('GBSI OPER');
    values = ledgerDimension.createValues();
    value = values.addNew();
    value.parmName('Account');
    value.parmValue('GBSI OPER');

    //DefaultDimension
    defaultDimension = ledgerJournalTrans.createDefaultDimension();
    values = defaultDimension.createValues();
    value = values.addNew();
    value.parmName('BusinessUnit');
    value.parmValue('004');

    //Service
    service = LedgerGeneralJournalService::construct();
    keyList = service.create(ledgerGeneralJournal);
    info(strFmt('Created RecId: %1', keyList.getEntityKey(1).parmRecId()));
}

The C# code for AIF Web Services looks very similar to the X++ code so I'll skip that. There are plenty of articles with sample code if you Bing it. In short: activate Web Service in AX, connect the web reference in Visual Studio and then let IntelliSense do the magic. What we don't see that often is the AIF file integration, so I'll add the XML here. Notice the XML namespace for the dimensions are from the shared types namespace.

<?xml version="1.0" encoding="utf-8"?>
<Envelope xmlns="http://schemas.microsoft.com/dynamics/2011/01/documents/Message">
    <Header>
        <MessageId>d23c9a6b-5b06-4b6c-bd32-000000000001</MessageId>
        <Action>http://schemas.microsoft.com/dynamics/2008/01/services/GeneralJournalService/create</Action>
    </Header>
    <Body>
        <MessageParts>
            <LedgerGeneralJournal xmlns="http://schemas.microsoft.com/dynamics/2008/01/documents/LedgerGeneralJournal">
                <LedgerJournalTable class="entity">
                    <JournalName>GenJrn</JournalName>
                    <LedgerJournalTrans class="entity">
                        <AccountType>Ledger</AccountType>
                        <AmountCurDebit>100</AmountCurDebit>
                        <Company>GBSI</Company>
                        <LedgerDimension>
                            <DisplayValue xmlns="http://schemas.microsoft.com/dynamics/2008/01/sharedtypes">605150-004-025--</DisplayValue>
                            <Account xmlns="http://schemas.microsoft.com/dynamics/2008/01/sharedtypes">605150</Account>
                            <Values xmlns="http://schemas.microsoft.com/dynamics/2008/01/sharedtypes">
                                <Value>
                                    <Name>BusinessUnit</Name>
                                    <Value>004</Value>
                                </Value>
                                <Value>
                                    <Name>Department</Name>
                                    <Value>025</Value>
                                </Value>
                            </Values>
                        </LedgerDimension>
                    </LedgerJournalTrans>
                    <LedgerJournalTrans class="entity">
                        <AccountType>Bank</AccountType>
                        <AmountCurCredit>100</AmountCurCredit>
                        <Company>GBSI</Company>
                        <DefaultDimension>
                            <Values xmlns="http://schemas.microsoft.com/dynamics/2008/01/sharedtypes">
                                <Value>
                                    <Name>BusinessUnit</Name>
                                    <Value>004</Value>
                                </Value>
                            </Values>
                        </DefaultDimension>
                        <LedgerDimension>
                            <DisplayValue xmlns="http://schemas.microsoft.com/dynamics/2008/01/sharedtypes">GBSI OPER</DisplayValue>
                            <Account xmlns="http://schemas.microsoft.com/dynamics/2008/01/sharedtypes">GBSI OPER</Account>
                        </LedgerDimension>
                    </LedgerJournalTrans>
                </LedgerJournalTable>
            </LedgerGeneralJournal>
        </MessageParts>
    </Body>
</Envelope>

Migrate journals with DIXF

AIF is great because it can handle multiple formats from Web Services, MSMQ, files etc. And you can get synchronous error messages. But sometimes you want a one-time import of a large batch of data and be able to validate the data before importing to the real table. In that case, DIXF is great. You can also expand the code behind the entity so that you process the data in a certain way, for instance transform Microsoft Dynamics AX 2009 dimensions to AX 2012 dimensions.

In this example we will use the predefined entity Opening balance to generate journal lines. The entity Opening balances is supposed to be used for exporting/importing balances. But it works fine for this example. The steps are:

  1. Create a new Processing group (Data import export framework > Common > Processing framework).
  2. Click the Entities button, create a new entity and select Opening balance. In this sample we use a CSV as the Source data format.
  3. Create a CSV file with the content from below and save it as OpeningBalanceImport.csv. Change the data that seems fit. In this case I used the GBSI company in the AX 2012 R3 Demo VPC.

    CurrencyCode,JournalNum,LineNum,Voucher,AmountCurCredit,AmountCurDebit,AccountType,LedgerDimension,DefaultDimension,TransDate
    GBP,00000339,1.0000000000000000,TEST0001,0.00,100.00,Ledger,605150-004-025,,2015-04-01 00:00:00
    GBP,00000339,2.0000000000000000,TEST0001,100.00,0.00,Bank,GBSI OPER,,2015-04-01 00:00:00
    
  4. Set the Sample file path as your new file.

  5. Click Generate source mapping and then Validate.
  6. If everything went well then go back to the Processing group form and click Get staging data and then Copy data to target.
  7. If everything went well then you should be able to see open your journal lines in the journal.

Posting journals

It is possible to post journals by code using the class LedgerJournalCheckPost. One of the use-cases for posting journals by code could be to validate all journals that comes in from integration or migration. Another use-case is that you can create an AIF Web Service, so that the integrating subsystem can post the journal after creating it.

public static void main(Args _args)
{
    LedgerJournalCheckPost ledgerJournalCheckPost; 
    LedgerJournalTable ledgerJournalTable;
    ;
    ledgerJournalTable = ledgerJournalTable::find('00000336');
    ledgerJournalCheckPost = LedgerJournalCheckPost::newLedgerJournalTable(
        ledgerJournalTable, NoYes::Yes, NoYes::Yes);
    ledgerJournalCheckPost.run();
}

This will result in an info message: Number of vouchers posted to the journal: 1.

As you can see in the example LedgerJournalCheckPost::newLedgerJournalTable(...) takes three arguments.

The example code is a very simple version, in reality you should add more control to the flow. For instance try-catch-statement and also add some follow up flow like:

if (!ledgerJournalCheckPost.numOfErrorsInList() && ledgerJournalCheckPost.numOfVouchersBooked())
    info('Success'); //Your success flow
else
    info('Failed'); //Your failed flow

Final words

In this article we have seen how to create general journals in different ways and how to post them. In next article we will continue to explore the reporting side that I briefly touched, Management Reporter. If you have any questions or suggestions please add a comment or email me. Cheers!