Many businesses that use Microsoft Dynamics AX 2012 R2, R3 (AX 2012) for their Financials have external time sheet systems that record and track employee hours. However, the business would like to process payroll in AX 2012. AX 2012 R2, R3 has a standard Application Integration Framework (AIF) document service that can be setup and used to import time sheet data from external systems using AX 2012 Excel Add-In. Once imported the earnings data can then be used for payroll processing in AX 2012.
To import Earnings statements from excel first the Earnings statement Service must be enabled using the following steps:
*Note: Steps shown below assume that “Office Addins” components 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. In addition, Human resource setups related to Employee and all Payroll Setups required to process Payroll in AX 2012 have been completed.
1. Navigate to System administration>Setup>Services and Application Integration Framework>Inbound ports, open form.
2. Click ‘New’ button. Enter Port name (I.e. ‘EarningsImport’) and click the ‘Service operations’ button on the ‘Service contract customizations’ fast tab.
3. Select the service operation ‘PayrollEarningsImportService.create’ and click the < to service 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:
- Module – select Human resources from list of values.
- Data source type = Service (defaults)
- Select ‘PayrollEarningsImportService’ from list of values.
- 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 Earnings import created in step 2 and click ‘Activate’ in menu bar.
After service is activated, next step is to create Earnings statement import template. To create the template in Excel by completing 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 Payroll Earnings import service and click
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. Worksheet 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 ‘*’ beside field.
a. Earning Statements
– Earnings statement number – value should be ELS, ELS1, a different combination for each worker record, same value will be populated in Parent.Earnings statement number
– Worker.PartyID – (Party ID from Global address book)
– Worker.Personnel number
– Pay period.Pay cycle
– Pay period.Period start date
– Pay period.Period end date
Skip two columns leaving them blank and add the following
b. Earnings statement lines
– Parent.Earnings statement number
– Earnings date – equal to first date of pay period being paid.
– RecId – Add systematically when Earnings date added.
– Tax region.Location ID
– Tax region.Personnel number
– Earning code
– Earning code.Description
8. 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.
9. Manually enter the values for fields or paste from a source spreadsheet all required values. Below are example of data to be entered and where to find information in AX 2012.
10. After all earnings statement data successfully pasted or entered in the Ribbon on the Dynamic AX tab, click the ‘Publish’ button in the Update group.
11. 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.
12. To view Earnings statements imported, navigate In AX 2012 to Payroll>Earnings statements>Earnings statements not processed.
13. Earnings statement can now be used to start Payroll processing inside of AX 2012.