Web2Expense Integration with Microsoft Dynamics GP

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.