Integration Manager for Microsoft Dynamics GP 9.0 Fixed Asset Book integration error.

This post contains information about an error you receive when you use the Fixed Asset Book destination in Integration Manager for Microsoft Dynamics GP. 

Integration Manager for Microsoft Dynamics GP 9.0 Fixed Asset Book integration error:

DOC 1 ERROR: CORPORATE Book: If Depreciated to Date is later than Place in Service Date, LTD Depreciation must be entered.

This error is fixed in Integration Manager for Microsoft Dynamics GP 10.0 with the latest hotfix. Please refer to the link below:

Integration Manager 10.0 downloads for Microsoft Dynamics GP 10.0 

For version 9.0, here is a workaround:

1. Do not map the following fields in the integration:
   • LTD Depreciation
   • YTD Depreciation
   • Depreciate To Date
      
2. Create an After Document script to update these fields in the FA00200 table.

Here is the After Document script (The connection string is hard-coded because most users integrating with SQL Optimized Adapters do not want to start into Microsoft Dynamics GP.  See KB Article 874790  to use RetrieveGlobals9 instead of hard coding the connection string):

Integration Manager After Document Script

Dim LTDDepr
Dim YTDDepr
Dim DepToDate
Dim Asset
Dim Book

LTDDepr = SourceFields("MainFixedAssetCorpBook.LifeToDate Deprec")
YTDDepr = SourceFields("MainFixedAssetCorpBook.Current YTD Depr")
DepToDate = SourceFields("MainFixedAssetCorpBook.Deprec To Date")
Asset = SourceFields("MainFixedAssetCorpBook.AssetNo")
Book = SourceFields("MainFixedAssetCorpBook.BookID")

Set objRec = CreateObject("ADODB.Recordset")
Set objConn = CreateObject("ADODB.Connection")

objConn.ConnectionString = "Provider=MSDASQL;DSN=YOURGPDSN;Initial Catalog=ME;User Id=sa;Password=YOURPASSWORD"
objConn.Open

cmdString = "update a Set a.DEPRTODATE = '" & DepToDate & "' from FA00200 a,FA40200 b,FA00100 c where a.BOOKINDX = b.BOOKINDX and a.ASSETINDEX = c.ASSETINDEX and (b.BOOKID = '" & Book & "') and (c.ASSETID = '" & Asset & "')"
Set objRec = objConn.Execute(cmdString)

cmdString = "update a Set a.LTDDEPRAMT = '" & LTDDepr & "' from FA00200 a,FA40200 b,FA00100 c where a.BOOKINDX = b.BOOKINDX and a.ASSETINDEX = c.ASSETINDEX and (b.BOOKID = '" & Book & "') and (c.ASSETID = '" & Asset & "')"
Set objRec = objConn.Execute(cmdString)

cmdString = "update a Set a.YTDDEPRAMT = '" & YTDDepr & "' from FA00200 a,FA40200 b,FA00100 c where a.BOOKINDX = b.BOOKINDX and a.ASSETINDEX = c.ASSETINDEX and (b.BOOKID = '" & Book & "') and (c.ASSETID = '" & Asset & "')"
Set objRec = objConn.Execute(cmdString)

ObjConn.Close

The objConn.ConnectionString section must be your connection string. Replace the DSN (ODBC Data Source Name) with your DSN that you use to connect to Dynamics GP. You will also have to replace the password.

As soon as the asset is integrated, you have to reconcile Fixed Assets to update the YTD Depreciation Rate.

Create an Asset Group and then do a Mass Change.

1. Start Microsoft Dynamics GP and then click Transactions, click Fixed Assets, and select Mass Change.
    
2. Drill back on Asset Group ID.
    
3. Create the Group. 
    
4. Make sure all the assets that are in the integration and you want to recalculate the YTD Depreciation Rate are assigned to this group.
    
5. In the Fixed Assets Mass Change window, select the Group that you created and select the Book tab.
    
6. Select the book from the integration and select the following:
    
   Depreciation Method: Your depreciation method
   Averaging Convention: Your Averaging Convention
   Switchover: Your Switchover selection
    
   Mark Calc Remaining
    
7. Select Apply Changes.

Hope this helps.

Dawn

// Copyright © Microsoft Corporation. All Rights Reserved.
// This code released under the terms of the
// Microsoft Public License (MS-PL, https://opensource.org/licenses/ms-pl.html.)