Steps to set up a GL Transaction Integration with Analytical Accounting in Microsoft Dynamics GP

 

Introduction

This document will walk you through the necessary steps to setup a GL Transaction integration that includes Analytical Accounting mappings using Integration Manager for Microsoft Dynamics GP. This article will cover two common setup scenarios for importing Analytical Accounting information with a GL Transaction.

Background Information

Starting with Microsoft Dynamics GP/Integration Manager 10.0, Integration Manager now utilizes eConnect for select destination adapters (formerly the ‘SQL Optimized’ destination adapters). When using the eConnect destination adapters, it is now possible to import the following transaction types with Analytical Accounting information:

  • GL Transactions
  • Sales Transactions
  • Inventory Transactions
  • POP Receivings Transactions

In this article, we will look at two common scenarios for integrating GL Transactions that include AA information.

Step By Step

Activating Analytical Accounting

  1. In Dynamics GP, click on Tools under Microsoft Dynamics GP, point to Setup, point to Company, point to Analytical Accounting and click Setup. This will open the Analytical Accounting Setup Wizard window.
  2. Ensure that Create Default Record is checked and click Next, then click Finish
  3. Go back to the Analytical Accounting Setup Wizard window again. (See step 1.)  The Create Default Record option should now be grayed out.
  4. Now check Activate Analytical Accounting option and click Next, then Finish.
  5. Click Yes to proceed with the activation.
  6. Click OK when it is finished.

Setting up sample AA Dimensions and Dimension Codes

  1. First, create three new test accounts that we will use for AA:  
    1. In Microsoft Dynamcis GP, click on Cards point to Financial and click Account.
    2. Create the following accounts:
      Account Description Category
      999-9999-01 AA Test Account 1 Cash
      999-9999-02 AA Test Account 2 Cash
      999-9999-03 AA Test Account 3 Cash
    3. Accept all other defaults.
  2. Create the AA Transactions Dimension:  
    1. In Microsoft Dynamcis GP, click on Cards, point to Financial, point to Analytical Accounting and click Transaction Dimension.
    2. Create the Trx Dimension by entering ELECTRONICS for the Trx Dimension.
    3. Select for the Data Type Alphanumeric.
    4. Enter Electronics for the Description 1 field.
      2688111
    5. Click Save and close the Transaction Dimension Maintenance window.
  3. Create the AA Transaction Dimension Codes: 
    1. In Microsoft Dynamics GP, click on Cards, point to Financial, point to Analytical Accounting, and click Transaction Dimension.
    2. Choose the Dimension ELECTRONICS.
    3. Enter XBOX for the Trx Dimension Code.
    4. Enter XBOX Hardware/Accessories for the Description.
    5. Select the Lookup and choose ELECTRONICS for the Linked to Node.
      2688559
    6. Click Save.
    7. Keeping the window open, now enter TV for the Trx Dimension Code.
    8. Enter Televisions for Description 1. 
    9. Select the Lookup and choose ELECTRONICS for the Linked to Node.
      2688560
    10. Click Save.
    11. Enter STEREO for the Trx Dimension Code.
    12. Enter Stereo Equipment for Description 1.
    13. Select the Lookup and choose ELECTRONICS for the Linked to Node.
      2688561
  4. Now grant access to the Dimension Codes:  
    1. In Microsoft Dynamics GP, click on Tools, point to Setup, point to Company, point to Analytical Accounting and click User Access.
    2. For Trx Dimension, choose the Dimension ELECTRONICS.
    3. Select the Trx Dimension Code XBOX, then click the Distribute and Adjust check boxes at the bottom of the window to enable the code for all users.
      2689170
    4. Click Save.
    5. Repeat steps ‘B’ and ‘C’ for the STEREO and TV” Dimension Codes.
  5. Create the Accounting Class to link our test accounts we created to the AA Dimensions:  
    1. In Microsoft Dynamics GP, click on Cards, point to Financial, point to Analytical Accounting and click Accounting Class.
    2. Enter the Class ID ELECTR_CLASS
    3. Enter Electronics Account Class for the Description 1 field.
    4. In the Grid you should see the ELECTRONICS Trx Dimension. Change the Analysis Type to Optional.
      2689171
    5. Click the Accounts in the bottom right corner.
    6. You may be prompted to Save changes to the Accounting class. Click Save.
    7. Scroll down in the list of accounts and find the three accounts you created in step 1.  
    8. Mark the Link checkbox next to each account 999-9999-01, 999-9999-02 and 999-9999-03
      2689172

      • You may be prompted with a message saying “This change will affect un-posted transactions and adjustments, if any. Do you want to continue?” 
      • Click Continue.
    9. Close the Account Class Link window to return back to the Accounting Class Maintenance window.
    10. Click Save and close the window.

Building the Integrations

GL Transaction (with Sequence required) – Example Scenario:
One of the Analytical Accounting enabled accounts appears more than once in the distributions. Also, the distribution amount is being split out to multiple Dimension Codes. Because the same distribution account appears more than once, we must map the Line Item Sequence value on the GL Distributions so the AA information can be linked to the correct distribution line/amount.  

Furthermore, since the distribution amount for a single line is being split out into multiple Dimension Codes, we must also map the “Assign ID” field for the Analytics Dimension.

  1. To support these requirements, create a Tab Delimited text file for the header and distributions source queries with the following data (include the column headings as shown):  
    JournalNum TrxDate Account Amount Sequence
    10000 4/12/2017 000-1100-00 500 500
    10000 4/12/2017 000-1200-00 -500 1000
    10001 4/12/2017 999-9999-01 16200 500
    10001 4/12/2017 000-1100-00 -16200 1000
    10001 4/12/2017 999-9999-01 2000 1500
    10001 4/12/2017 000-1200-00 -2000 2000
  2. Save the file as a Tab Delimited text file (*.TXT). For this example, name the file “AA GLTrx-with Seq.txt”.
  3. Create a second Tab Delimited source file that contains the AA Distribution information (include the column headings as shown):  
    JournalNum Account Amount Dimension DimensionCode CodeAmount AAAsignID Sequence
    10001 999-9999-01 16200 ELECTRONICS XBOX 8000 1 500
    10001 999-9999-01 16200 ELECTRONICS TV 7000 2 500
    10001 999-9999-01 16200 ELECTRONICS STEREO 1200 3 500
    10001 999-9999-01 2000 ELECTRONICS XBOX 1100 1 1500
    10001 999-9999-01 2000 ELECTRONICS TV 600 2 1500
    10001 999-9999-01 2000 ELECTRONICS STEREO 300 3 1500

    *NOTE: The AAssignID value will be used since we are splitting the distribution amount across multiple Dimension codes. The Sequence value is required because the distribution account 999-9999-01 appears on more than one distribution line, therefore the Sequence value is required to link the AA information to the correct distribution line.

  4. Save the file as a Tab Delimited text file (*.TXT). For this example, name the file AA GLTrx-with Seq AA Dimension.txt.
  5. Open Integration Manager and click on New Integration. Give it the name GL Transaction – AA with Sequence”.

    Create the Header Source Query

  6. Click on Add Source, click on Text, then click on Define New Text.
  7. Give the source query the name GL Header and browse out to the first file you created named AA GLTrx-with Seq.txt.
  8. Choose Tab as the delimiter and check the box First Row Contains Column Names.
    2689280
  9. Since we are using a single source file for the Header and Distribution lines, we need to setup the Grouping on the header fields. Click on the Sorting tab.
  10. Add JournalNum and TrxDate to the Group By fields list, then click OK to save the query. Click OK again on Warning stating there are fields named as Show that are not in the Group By list. This is expected.
  11. Right-click on the GL Header query and make sure you can preview the data as follows:
    2689281

    Create the Distribution Source Query

  12. Click on Add Source, click on Text, then click on Define New Text.
  13. Give the source query the name GL Distributions and again browse out to the first file you created named AA GLTrx-with Seq.txt.
  14. Choose Tab as the delimiter and check the box First Row Contains Column Names.
    2689302
  15. Click OK to save the query. You will receive a message stating that you must setup Query Relationship. Click OK to dismiss the dialog. We will set that up later once all the queries are created.
  16. Right-click on the GL Distributions query and make sure you can preview the data as follows:
    2689303

    Create the AA Dimension Source Query

  17. Click on Add Source, click on Text, then click on Define New Text.
  18. Give the source query the name GL AA Dimension” and browse out to the second file you created for the AA information named AA GLTrx-with Seq AA Dimension.txt.
  19. Choose Tab as the delimiter and check the box First Row Contains Column Names.
    2689305
  20. Click OK to save the query.
  21. Right-click on the GL AA Dimension query and make sure you can preview the data as follows:
    2689306

    Create the AA Dimension Code Source Query

  22. Click on Add Source, click on Text, then click on Define New Text.
  23. Give the source query the name GL AA Dimension Code and browse out to the second file you created for the AA information named AA GLTrx-with Seq AA Dimension.txt.
  24. Choose Tab as the delimiter and check the box First Row Contains Column Names.
    2689330
  25. Click OK to save the query.
  26. Right-click on the GL AA Dimension Code query and make sure you can preview the data as follows:
    2689342

    Setting up the Query Relationships

  27. Double-click on Query Relationships.
    1. Since the Journal Number is the key field that differentiates one transaction from the next, this key field should go all the way through the Query relationship chain.
      2689343
    2. To link the AA information, since the AA Dimension information is linked per distribution line, next we need to establish a link between the GL Distributions query and the “L AA Dimension query. As stated in the overview, a given GL account may appear more than once in a single transaction, there we must us the Sequence field to link the AA Dimension to the correct distribution line.
      2689344
    3. Continuing down the relationship, the AA Dimension Codes are linked back to a given AA Dimension. In this example, we need to continue linking with the Sequence field and also add the DimensionCode field. At this point, all the appropriate relationships have been established so your Query Relationship window should look like the following:
      2689345

    Add the Destination Adapter

  28. Click on Add Dest., expand Microsoft Dynamics GP eConnect, click on Financial and select GL Transaction.
  29. In the Server Name field, enter in the name of your SQL Server instance (not a DSN name) and the company database name.
    2689352

    Define the field mappings

  30. Double-click on Destination Mapping to open the field mapping window.
  31. For GL Transactions with AA information, the Journal Entry # is REQUIRED. This field cannot be defaulted in! Some advanced users will use a VBScript on the Journal Entry# field to get the next number, but it does have to be supplied in the mapping otherwise the AA information cannot be linked to the transaction correctly.
  32. Provide a constant for the Batch ID and Reference fields.
    2689363
  33. Click on the Entries folder. Map the Account Number, Debit/Credit Amount and Sequence Line to the fields from the GL Distributions query.
    2689364

    *NOTE: The Sequence Line field is required in this case since the GL Account may appear more than once within the same GL Transacion. Without this field mapped, the AA information cannot be linked to the correct distribution.

  34. Click on the Analytics folder. Map the Assign ID and Amount field from the GL AA Dimension source query as follows:
    2689365

    *NOTE: The “Assign ID” field is required in this case since we are splitting out the distribution amount to more than one AA Dimension Code.

    *Optional: If the percentages are known instead of the Amounts, you can map the “Assigned Percent” field instead of the Amount field. You do not need to map both!

  35. Click on the Dimensions folder. In this example, we are only using Alphanumeric Dimension Codes, so we will map the Transaction Dimension and Transaction Dimension AlphaNumeric” from the GL AA Dimension Code source query.
  36. Map the “Transaction Dimension” to the Dimension field from the source query and “Transaction Dimension AlphaNumeric” to the DimensionCode field from the source query:
    2689366
  37. The field mappings are now complete! Save the Integration.
  38. Go ahead and Run the integration now to bring in the two Journal Entries.

    Verify the Transaction Integrated as expected

  39. The first Journal Entry does not have any AA information, so we will look at the second transaction, Journal Number 10001.
    2689841
  40. To inspect the AA distributions, click on the first distribution line, then click the Analytical Accounting button. We should see the distribution amount of $16,200 has been split across 3 different AA Dimension Codes as follows:
    2689843
    2689845
    2689849
  41. You can use the VCR buttons at the bottom of the window to navigate to the second Analytics distribution where you will see that the distribution amount of $2000 has also been split out across the 3 AA Dimension Codes.

GL Transaction (No Sequence required) – Example Scenario:
The Analytical Accounting enabled accounts do NOT appear more than once in the distributions. The AA information represents a one-to-one relationship in the source data. This means that a single distribution line links to only one AA Dimension which has only one AA Dimension Code. Because it’s one-to-one, we do not need a Sequence Number or the Assign ID fields. For this example scenario, we can use a single source file.

  1. To support these requirements, create a Tab Delimited text file (*.TXT) with the following data:
    JournalNum TrxDate Account Amount Dimension DimensionCode
    10003 4/12/2017 000-1100-00 500    
    10003 4/12/2017 000-1200-00 -500    
    10004 4/12/2017 999-9999-01 8000 ELECTRONICS XBOX
    10004 4/12/2017 999-9999-02 7000 ELECTRONICS TV
    10004 4/12/2017 999-9999-03 7000 ELECTRONICS STEREO
    10004 4/12/2017 000-1100-00 -16200    
    10005 4/12/2017 999-9999-01 1100 ELECTRONICS XBOX
    10005 4/12/2017 999-9999-02 600 ELECTRONICS TV
    10005 4/12/2017 999-9999-03 300 ELECTRONICS STEREO
    10005 4/12/2017 000-1100-00 -2000    
  2. Save the file as a Tab Delimited text file (*.TXT). For this example, name the file “AA GLTrx-No Seq.txt”
  3. Open Integration Manager and click on New Integration. Give it the name “GL Transaction – AA without Sequence”.

    Create the Header Source Query

  4. Click on Add Source, click on Text, then click on Define New Text.
  5. Give the source query the name GL Header – No Seq and browse out to the source file you created named AA GLTrx-No Seq.txt.
  6. Choose Tab as the delimiter and check the box First Row Contains Column Names.
    2689897
  7. Since we are using a single source file, we need to setup the Grouping on the header fields. Click on the Sorting tab.
  8. Add JournalNum and TrxDate to the Group By fields list, then click OK to save the query. Click OK again on Warning stating there are fields named as Show that are not in the Group By list. This is expected.
  9. Right-click on the GL Header – No Seq query and make sure you can preview the data as follows:
    2689898

    Create the Distribution Source Query

  10. Click on Add Source, click on Text, then click on Define New Text.
  11. Give the source query the name GL Distributions – No Seq and again browse out to the source file you created named AA GLTrx-No Seq.txt.
  12. Choose Tab as the delimiter and check the box First Row Contains Column Names.
    2689904
  13. Click OK to save the query. You will receive a message stating that you must setup Query Relationship. Click OK to dismiss the dialog. We will set that up later once all the queries are created.
  14. Right-click on the GL Distributions – No Seq query and make sure you can preview the data as follows:
    2689905

    Create the AA Dimension Source Query

  15. Click on Add Source, click on Text, then click on Define New Text.
  16. Give the source query the name GL AA Dimension – No Seq and browse out to the source file you created named AA GLTrx-No Seq.txt.
  17. Choose Tab as the delimiter and check the box First Row Contains Column Names.
    2689906

    1. Click on the Filter tab and add a restriction with the following settings:
      Column: Dimension
      Operator: <>
      Value: 0
    2. Click “And Into Criteria”
      2689907
  18. Click OK to save the query.
  19. Right-click on the GL AA Dimension – No Seq query and make sure you can preview the data as follows:
    2689909

    Create the AA Dimension Code Source Query

  20. This query will be setup identical to the Dimension query we just created since this example is a one-to-one sample for the distribution/dimension/dimension code relationship. Click on Add Source, click on Text, then click on Define New Text.  
  21. Give the source query the name GL AA Dimension Code – No Seqand browse out to the source file you created named AA GLTrx-No Seq.txt.  
  22. Choose Tab as the delimiter and check the box First Row Contains Column Names.  
    2689910

    1. Click on the Filter tab and add a restriction with the following settings:
      Column: Dimension
      Operator: <>
      Value: 0
    2. Click “And Into Criteria”
      2689931
  23. Click OK to save the query.
  24. Right-click on the GL AA Dimension Code – No Seq query and make sure you can preview the data as follows:
    2689932

    Setting up the Query Relationships

  25. Double-click on Query Relationships.
    1. Since the Journal Number is the key field that differentiates one transaction from the next, this key field should go all the way through the Query relationship chain.
      2689933
    2. To link the AA information, since this example demonstrates a one-to-one relationship between the Distribution/Dimension/Dimension Code, the relationship setup is fairly straightforward. All we need to add is a link to the Account field since the AA information pertains to a particular account number.
      2689934
    3. Continuing down the relationship, the AA Dimension Codes are linked back to a given AA Dimension. In this example, we need to continue linking with the Sequence field and also add the DimensionCode field. At this point, all the appropriate relationships have been established so your Query Relationship window should look like the following:
      2689935

    Add the Destination Adapter

  26. Click on Add Dest., expand Microsoft Dynamics GP eConnect, click on Financial and select GL Transaction.  
  27. In the Server Name field, enter in the name of your SQL Server instance (not a DSN name) and the company database name.  
    2689352

    Define the field mappings

  28. Double-click on Destination Mappings to open the field mapping window.
  29. For GL Transactions with AA information, the Journal Entry # is REQUIRED. This field cannot be defaulted in! Some advanced users with use a VBScript on the Journal Entry# field to get the next number, but it does have to be supplied in the mapping otherwise the AA information cannot be linked to the transaction correctly.
  30. Provide a constant for the Batch ID and Reference fields.
    2689936
  31. Click on the Entries folder. Map the Account Number and Debit/Credit Amount to the fields from the GL Distributions – No Seq query.
    2689937
  32. Click on the Analytics folder. Map the Amount field from the GL AA Dimension – No Seq source query as follows:
    2689955

    *Optional: If the percentages are known instead of the Amounts, you can map the “Assigned Percent” field instead of the Amount field. You do not need to map both!

  33. Click on the Dimensions folder. In this example, we are only using Alphanumeric Dimension Codes, so we will map the Transaction Dimension and Transaction Dimension AlphaNumeric from the GL AA Dimension Code – No Seq source query.
  34. Map the “Transaction Dimension” to the Dimension field from the source query and “Transaction Dimension AlphaNumeric” to the DimensionCode field from the source query:
    2689956
  35. The field mappings are now complete! Save the Integration.
  36. Go ahead and Run the integration now to bring in the two Journal Entries.

    Define the field mappings

  37. The first Journal Entry does not have any AA information, so we will look at the second transaction, Journal Number 10004.
    2689957
  38. To inspect the AA distributions, click on the first distribution line, then click the Analytical Accounting button. We should see the distribution amount of $8000 assigned to the XBOX dimension code as follows:
    2689958
  39. You can use the VCR buttons at the bottom of the window to navigate to the second Analytics distribution where you will see that the distribution amount of $7000 has been assigned to the TV Dimension Code.
    2689959
  40. Lastly, the 3rd AA distribution has the amount of $1200 assigned to the STEREO dimension code.
    2689960
  41. You can also view Journal Entry 10005 in the same way to verify that the distributions have been assigned to the AA Dimension Codes as expected.
Advertisements

About nijamkhan

I am simple Hardworking,and i would like improve the Knowledge time by time
This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s