Many businesses that use Microsoft Dynamics AX 2012 use third party vendors for their payroll processing and have a requirement on a weekly, bi-weekly, or monthly basis to import the General Ledger entries related to Payroll into AX 2012. A simple solution for importing the Payroll ledger data is to use Microsoft’s Excel Add-in tool to import the payroll general ledger journal entries.
In order to import General ledger entries using Excel Add-In, the General Ledger Service must first be enabled using the following steps:
*Note: Steps shown below assume that “Office Add-ins” component was installed during setup of Microsoft Dynamics AX 2012 client and that the ‘Dynamics AX’ add-in tab appears in locally installed version of Excel. Also during initial setup of AX 2012 the “Set up of Application Integration Framework” was performed.
1. Navigate to System administration>Setup>Services and Application Integration Framework>Inbound ports, open form.
2. Click ‘New’ button. Enter Port name (I.e. ‘GLServices’) and click the ‘Service operations’ button on the ‘Service contract customizations’ fast tab.
3. Select the all the service operations that start with ‘LegerGeneralJournalService’ and click the < to add them to the selected service operations.
4. Expand the ‘Troubleshooting’ fast tab. Select Logging mode ‘All document versions’ and select ‘Include exceptions in fault’.
5. Before activating service, navigate to Organization administration>Setup>Document management>Document data sources.
6. Click ‘New’ and a record with the following values:
a. Module – select General ledger from list of values.
b. Data source type = Service (defaults)
c. Select ‘LedgerGeneralJournalService’ from list of values.
d. Click ‘Activate’ button in menu bar to activate data source
7. Navigate back to System administration>Setup> Services and Application Integration Framework>Inbound ports, open form. Select Port created in step 2 (I.e. ‘GLServices’) and click ‘Activate’ in menu bar.
Once service is activated, next step is to create a General Ledger Journal import template. To create the template complete the following steps:
1. Open Excel on the same computer where Dynamics AX 2012 client is installed locally (I.e. your PC, Terminal server, Remote App etc.)
2. Create a blank workbook.
3. Click on Dynamics AX tab in the ribbon, Click Options in configure group. Select Legal entity to upload entries and select applicable ‘Default account Structure’). Verify Server and Port for environment you are importing entries.*
*Speak to your system administrator for guidance on values.
4. Click on Dynamics AX tab in the ribbon, then click the Add Data button in the design group and select ‘Add Data’.
5. In the ‘Add data’ form select the General ledger entries service and click <OK>.
6. The field chooser will open in a separate left pane of the worksheet. Use the field chooser to add fields to two worksheets one for “header” and one for “lines”. To add fields to worksheet select field in pane then double click.
7. Header should have the following fields in the order shown.
*Note Key index field on shown with a key beside field name and required fields are shown with a red ‘*’ next to it.
a. Journal batch number
8. Lines worksheet should have the following fields:
*Note upload will be using two line entries and not using ‘offset’ account field.
a. Parent.Journal batch number
b. Line number. Account type
c. Rec id (added when Line number. Account type added)
d. Ledger Dimension.MainAccount (Main account)
e. Ledger Dimension.(Dimension) – Dimensions add a field for each one
*Tip – Add Main account and dimension in same order as reflected in applicable Account structure)
j. Line number.Date
k. Company accounts (code for legal entity where importing entries)
9. After all fields are added click the Fields button in Ribbon ‘Design’ group to close the Field chooser pane. Worksheets for header and lines will look as shown. Use File>Save as and save the template as it is while it is blank.
10. Manually enter the following values in header.
a. Journal batch number – Enter Journal batch number as AX (*Note: number sequence in AX will generate batch number)
b. Name – Enter Journal name of journal importing (I.e. GenJrn).
c. Description – Enter description for journal batch being imported
11. On the lines tab paste in journal line information that has been preformatted in a separate excel sheet. Your source spreadsheet must have the same columns of data as your import template. For Recid just enter 1 for the first row, 2 for second, 3 for third and so on, when imported AX 2012 will generate the Record Ids.
* Tip: In your source file format all fields and columns as text, except for data column. Next, highlight your data in source file use Ctrl + C to copy and in import template place cursor in B2 and use Ctrl + V to paste.
12. After all data successfully pasted into import template, in the Ribbon on the Dynamic AX tab, click the ‘Publish’ button in the Update group.
13. A Dynamics AX Status tab will be systematically added to the workbook with the status showing Total records 1 and Records Published 1 if import was successful.
14. To review imported journal in AX 2012 navigate to General ledger>Journals>General journal open form select Journal with description entered in step 10 c. and click the ‘Lines’ button.