This blog provides detailed explaination for integrating Web2Expense (W2E) Expense Management with Microsoft Dynamics GP.
Let’s start with GL code structure:
Example: 99-9546
99=Division
95=Department
46=Expense Account
User:
Name: Joe.Smith
Department:
Name: Information Technology
Department Code: 99.95 (see GL code structure above)
Cost Center*:
(This will be used for the Employee’s “Vendor Code” in GP)
Name: JSMITH
*If you have or can create a standard for how Vendor Codes are setup in GP, i.e LastName, FirstName, then you don’t have to use this and you can have Integration Manager link and/or create the new Vendor Codes if they aren’t created in GP already. If you don’t have a standard for Vendor Codes, then you have to look up the Employee’s Vendor Code in GP, create it in the W2E Cost Center and then assign the Employee the Cost Center code in W2E.
You are much better off if you can standardize and not have to mess with this field at all.
Project*:
Project Name: Human Resources
Project Code: 99.38
*This can be used so that you can allocate expenses to different Divisions/Department other than just the default Department that the Employee is assigned to. For example, if an employee is mainly in Sales, but occasionally has expenses that should be charged to R&D, you could assign them a Project, which you could then assign to an Expense Report when needed.
Custom Expense Type Mapping*:
Expense Type Category: Airfare Expenses
Expense Type: Airfare
Custom Expense Type Category: Travel – Air
Custom Expense Type: 46 (see GL Code Structure above)
*This will complete the last piece of the GL Code. Our expense categories are the same across Divisions/Departments. If we didn’t have consistent expense categories across Divisions/Departments, we would have to have SQL table or Excel file that contained the W2E and GP accounts and would perform the translations in Integration Manager.
The Custom Expense Type Category can be used as a description line in GP.
Integration Manager
Setup a new integration using the ‘Payables Transaction’ as the destination.
You will need two data sources:
1) A query of all the data in the export from W2E,
2) A query of the ‘summary header’ information, i.e., report date, report total amount
For the top level ‘Payables Transaction’:
Payables Transaction
For the line item “Distributions” destination:
Distributions Destination
A script is shown for the ‘Distribution Account’. This script determines if there is a ‘Project’ assigned to the Expense report and, if so, uses the ‘Project Code’ instead of the ‘Department Code’. The script also pulls together the “Department Code” or “Project Code” and “Expense Type” from W2E to form the GL code that Dynamics GP expects.
Feel free to post your comments into the blog or contact us for more information.
