Creating an Integration Manager to Process Intercompany Payables (PM) Transactions in Microsoft Dynamics GP - Step-by-Step Instructions

Sometimes a company is associated with other companies. For instance, there could be a parent company that has multiple subsidiary companies under its umbrella. The parent, or originating, company would want to be reimbursed for expenses being incurred by the subsidiary, or destination, company. In order to do this in Microsoft Dynamics GP, an intercompany Payables (PM) transaction is needed.

If these transactions occur frequently, it is beneficial to create an Integration Manager that can automatically create these intercompany transactions, minimizing manual work and maximizing efficiency. Integration Manager provides you with an easy way to integrate data between business applications. You can integrate data from external business databases, e-commerce solutions, or other data file types into the Microsoft Dynamics GP application.

In order to do Intercompany PM transactions, you must have more than one GP company created in your database. The examples presented here will use the standard GP test company, Fabrikam (database name TWO), for our destination company (the subsidiary) and another test company we have set up called Sample WH (database name TWOWH) for our originating company.

Let's Get Started...

There are some preparations that must be made within Dynamics GP to enable the intercompany transaction capability.

In both the originating and destination companies, an account category needs to be set up for the due to/due from accounts. Go to Financial > Setup > Category, scroll to the bottom of the list of categories and simply add the new category:

A Due To/Due From account needs to be set up in both companies as well (Financial > Cards > Account). For our illustration, they were set up as follows:

In the originating company (select whatever account number desired):

In the destination company (select whatever account number desired):

Next, the Dynamics GP companies will need to be set up to accept intercompany transactions. Go to Administration > Setup > System > Intercompany and set up as follows using TWOWH as the Originating Company ID and Fabrikam as the Destination Company Name. Using the same account for the Due To and Due From accounts is perfectly acceptable.

The companies will now be able to accept intercompany transactions.

We will be importing the data from a comma-delimited text file (csv). Other possibilities would be to import from a database source. Our csv file looks like this:

Notice, the values in the Entity column, TWO & TWOWH. These will be used to direct our intercompany PM transactions to the correct company.

Now, let’s create a new Integration Manager to handle these transactions. In Dynamics GP, go to Microsoft Dynamics GP > Tools > Integrate > Integration Manager.

This will open the Integration Manager:

Integrations are saved in a database. You may use a database that has already been created, the Integration Manager Samples database or you can create a new database which we will do here.

In the Tools menu, select Create Database:

This opens the Create New Database window. Click on the Folder Dialogue button to the right of the text box to create the database in the folder you select:

Select your folder, type in the name of your database and click Save – the full path of the database is added to the Create New Database dialogue window. Once you click on the Create button, the (for now) empty database is created. At the top of the Integration Manager, the database you just created is displayed (normally this would be the sample database that comes with Dynamics GP unless it has already been changed).

To open the newly created database, go to File > Open Database and select the database you just created. To make this new database your default database, go to Tools > Options and select the path of the new database in the Default Integration Manager Database textbox and click on OK.

This will ensure that, whenever you open the Integration Manager, you will be in the correct database where the integrations you create are stored:

Now click on “New Integration”:

This creates a new integration and opens the properties window for the new integration. It defaults to Integration1, but we will change the name to IMIntercompanyPMTransactionBlog. When you create an integration, you’ll specify sources, destinations, and destination mappings that will indicate where the data is coming from and where it will be integrated into the Dynamics GP database.

You will see selections for the maximum number of errors and warnings. These can be set to limit the number of errors/warnings before the integration fails. You will also see a tab to specify where and how the logs are saved. We will specify that the logs will be saved as files and where the files will be located.

You will need to select and set up your sources next. For a PM transaction, you will need to set up a PM Header source and a PM Distribution source. You can use the same file for both or you can use separate files; there needs to be common fields to link the datasources together.

Right-click on Source and select Add Source or simply select Add Source from the menu at the top of the page. Since we are importing from a text file, in the Adapters column, select the folder called Text and in the Sources column, select Define New Text, then click on Open:

We are creating the header query first so we will name this query, PM Header and give it a description. Select the file you want to use, specify that it is comma delimited and that the first row contains column names:

If you have done this correctly, clicking on the Columns tab will show all the columns in the file:

Since this query is for the header information, we will specify which columns will be used in the Sorting tab. In this case the only fields that are related to the PM header are the batch date and the vendor:

Once you click on OK, a message will appear indicating that there are fields marked as “Show” in the Columns tab that are not in the Group By list. Simply click on OK and your PM Header source is created. Right click on it and select Preview PM Header to see that you have the data you expect to see. In our case, since all of the data in this file is related to one transaction, you will only see one line of data:

To create the PM Distribution source, follow the same steps as you did to create the PM Header – add source, select the Text adapter and Define New Text. Name the query, PM Distribution, and select the same csv file, specify comma delimited and that the first row contains the column names.

On the Columns tab, leave all the fields selected. On the Sorting tab, we will order by Entity, then by Account String but will not group by any fields. These rows will be the distribution details of the transaction and all are needed.

Next, a relationship between the two queries needs to be established. Double-click on Query Relationships (or select Relationships from the menu at the top) and the Relationships window opens:

Click on Batch Date in PM Header and drag across to Batch Date in PM Distribution. This will create a one-to-one relationship between the two files using Batch Date as the join. Do the same with Vendor.

You can select other types of relationships (outer joins, etc.) by right-clicking on the arrow between the two queries and making a different selection.

Once the relationship has been established, a destination must be added. Double-click Destination (or select Add Destination in the menu at the top) to open the Add Destination window where we will select Microsoft Dynamics GP. Since the Integration we are creating will process PM transactions, select Payables Management under the Microsoft Dynamics GP Adapter and Payable Transaction in the Destinations:

Once the destination adapter has been selected, it is time to do the Destination Mapping to indicate where the data will be integrated. Double-click on Destination Mapping (or select Mapping in the menu at the top) and the mapping dialogue window opens:

First, do the mapping for the PM Header. Since this is going to be an intercompany transaction, for the Intercompany field, in the Rule column, select Use Constant and in the Source column select “True” from the dropdown:

Notice that when you select a field, you will see fields that you can change in a smaller box in the lower left of the window. In most cases, values can be changed in the main window or in this smaller one; in some cases there are properties that can be changed in the smaller box like the ability to truncate a field’s value if it exceeds the max length allowed.

For the Batch ID, we will be using a constant value, however, you could also specify a field from your file if this data was included. Select Use Constant and in the Source column, type in your batch id. We will use TESTIMIC001. One thing to note is that the batch ID will not automatically be created unless you specify a rule for it. Select the Options tab and for the Missing Batch option, select the Add New Batch rule. At this point, you may also select the Override rule for the Over Invoice Limit option.

Return to the Fields tab.

For the Vendor ID, we will be using a field from the source text file. In the Rule column, select Use Source Field and then select the field either by clicking on the button in the Source column or in the Source Field Value column in the smaller box. This will open the Source Object window where you can select the query you will use for the info and the field name of the info which, in this case, is Vendor:

Highlight the desired field and press Select and the field is added to the Source column:

Scripts may also be used to provide values to fields. For the PM transactions, Document Number is a required field and does not have a default. As it is part of the PM Header, there will only be one document number for each header record. To create the script, set the Rule to “Use Script”. This changes the properties window in the left hand side of the window to display the Script Text property name.

Click on the button in the Value column to open the script window. In this window, we will enter our VB script to set the document number. There are some very useful sample scripts that can be accessed by clicking on Script in the top menu of this window and then selecting Script Library. Here is the script we are using to create the document number for this header:

Click on File > Save and Close and the script is saved and ready for use.

For the PM Distributions mapping, click on the Distributions folder. Since this is for an intercompany transaction, we will specify the Intercompany ID from the text file. Select “Use Source Field” from the Rule column and then click on the button that will appear in the value field for Source Field in the Properties Window. Change the Source Object selection to PM Distribution and select the field holding the intercompany ID, Entity.

The distribution accounts in our text file include dashes in the account number, 100-6520-00 for instance. When processing intercompany PM transactions, dashes in the account number are not allowed. Luckily, in our Script Library, there was a sample script for removing the dashes and we will use that script to format our account numbers. For the Distribution Account, select “Use Script” in the Rules field and click on the button in the Value field in the properties window.

Select Script > Script Library in the Script Editor window. Expand the Microsoft Dynamics GP Scripts list and then expand the listing for Removing Dashes from an Account Number. Click on Account Number field script and the script is displayed:

Click on Add Script and close the Script Library window. You will see the script has been added to the Script Editor window. We will edit the script to use our query and field name:

File > Save and Close and the script has been added.

In our text file, the distribution amounts (debit=positive amounts and credit=negative amounts) are all shown in one column; there are not separate columns for debit and credit amounts. This is easily handled within our Destination Mapping by changing the rules for Debit Amount and Credit Amount.

For Debit Amount, change the rule to “Use Positive Source Field” and specify the source field for the amounts from our PM Distribution query, Journal Amount.

For Credit Amount, change the rule to “Use Negative Source Field” and set the source field to the same field, Journal Amount.

At this point, we are ready to save and run our integration.

Click on Run from the menu and a Progress window will open, showing the progress as the integration runs. If there are any errors, they will show here.

In our example, our integration ran successfully. If a log file location was specified when creating the integration, the details of the process can be viewed in the file.

If we try to run the transaction a second time, the document number we set up will duplicate since we only scripted it to be “IMICDOC” & DATE(); this will cause an error since the document number has already been used and must be unique. Here is what you’ll see when the integration is not successful:

And the log will show the complete information:

Once any errors are resolved, in this case, the issue with the document number, the integration can be saved and re-run successfully.

Once the integration has run successfully, you will be able to confirm and post the transactions; posting does not happen as part of the Integration Manager.

In Dynamics GP, go to Purchasing > Inquiry > Transaction by Vendor. Select the vendor used in the transaction, Redisplay and select our document number from the list.

Click on Document Number and the Payables Transaction Entry Zoom window is displayed:

Click on Distributions and you will be able to see and confirm all the transactions that were in the text document:

Notice that the debits and credits were handled properly from the rules set in the integration.

To post the batch, go to Purchasing > Transactions > Batches and select the Batch ID by clicking on the spy glass or typing in the batch ID.

From this screen, you can also confirm the transactions and their distributions if needed. Since we have already done this, we will click on Post. A series of reports can be created either on the screen, printer or in files or, if you don’t need the reports, these report screens can be cancelled. These reports can be useful in confirming the distributions and due from/due to transactions.

Now that the purchasing batch has been posted, the transactions will need to be posted to the General Ledger to create the transactions in the destination company as well as being posted in the General Ledger in the destination company.

Hopefully, you have now seen that creating an Integration Manager for Intercompany PM Transactions is not nearly as complicated as you thought, and you should now be able to take advantage of this great tool!

Feel free to give me a ring or shoot me a message should you have any questions or need assistance with Dynamics GP.

Written by:
Debi Johnson, BI Developer, FMT Consultants

Posted by: Jakob Bechgaard