General Journals Database & Business - Microsoft Dynamics 365 Vietnam

Microsoft Dynamics 365 Vietnam

Song Nghia - Microsoft Dynamics 365 Vietnam

Breaking

Friday, December 13, 2019

Home AX 2012 D365 Dynamics 365 FnO Dynamics 365 FO Dynamics 365 Retail X++ General Journals Database & Business

General Journals Database & Business

Song Nghia - Technical Consultant

1. 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 Ledger > Journals > General journal > [Button] Lines)
General journal
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.

2. 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

3. 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>

4. 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.

5. 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.
  • LedgerJournalTable _ledgerJournalTable - This is the LedgerJournalTable that you want to post.
  • NoYes _post - If you only want to validate without posting then set _post as No. It is possible to have several vouchers in the same journal. Sometimes one of the vouchers can have errors.
  • NoYes _transferErrors - If you set _transferErrors as Yes, then the journal will get posted and the erroneous vouchers will get transferred to a new journal. If you set _transferErrors as No and there are errors, then the posting will get cancelled and the journal still opened.
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:
Copied: https://axmasterclass.com/blog/mastering-general-journals/
if (!ledgerJournalCheckPost.numOfErrorsInList() && ledgerJournalCheckPost.numOfVouchersBooked())
    info('Success'); //Your success flow
else
    info('Failed'); //Your failed flow
Tags
Share This
Posted by on
Labels: , , , , ,