Featured Post

Dynamics GP 2018 - Upcoming Features

We are counting the days for Dynamics GP 2018 release which is promising to introduce a new set of features that have been highly recogniz...

Saturday, November 21, 2015

Fixed Assets - Account Group (Part 2) | SQL Macro Generator

In the previous post; Fixed Assets - Account Group (Part 1),general information on the FA account group is provided along with an SQL script to retrieve the account groups. As previously illustrated, this post is supposed to shed a light on how to migrate existing account group from one company to another. For this purpose, an SQL script will read the account groups and retrieve a Macro to be run on the destination company.

Fixed Asset - Account Group

Note !
The script can be run "as is" considering that both companies have a similar account format and structure, otherwise, it would not work unless required modifications are deployed. The script is built considers an account format of three segments:
  • Segment One - Three Characters
  • Segment Two - Four Characters
  • Segment Three - Two Characters

Additionally, there is a prerequisite for this Macro, which is the view provided on the Fixed Assets - Account Group (1). Download links will be provided in this post for all the required scripts. 

>> Download Links: You can download both; FA Account Group view SQL Script and SQL Macro Generator from this link

/*----------------------------------------------------------------------------
Creation Date: The 18th of November, 2015
Created by: Mahmoud M. AlSaadi
SQL Macro Generator to migrate FA account groups from one company to another. 
(Considering they both have the same account format and structure)

Revision History:
Revision No.            Revision Date    Description
1                       18/11/2015       Original Version
------------------------------------------------------------------------------ */

DECLARE @ACTSEG_1 NVARCHAR(MAX)
DECLARE @ACTSEG_2 NVARCHAR(MAX)
DECLARE @ACTSEG_3 NVARCHAR(MAX)


DECLARE @Number INT
DECLARE @AccountGroupID NVARCHAR(MAX)
DECLARE @AccountGroupDescription NVARCHAR(MAX)
DECLARE @FromCompany NVARCHAR(MAX)
DECLARE @ToCompany  NVARCHAR(MAX)

DECLARE @DepreciationExpense NVARCHAR(MAX),
              @DepreciationReserve NVARCHAR(MAX),
              @PriodYearDep NVARCHAR(MAX),
              @AssetCost NVARCHAR(MAX),
              @ProceedAccount NVARCHAR(MAX),          
              @Realized NVARCHAR(MAX),
              @Non_Realized NVARCHAR(MAX),
              @Clearing NVARCHAR(MAX)
             


 PRINT  '# DEXVERSION=14.00.0072.000 2 2'
 PRINT 'CheckActiveWin dictionary ''Fixed Assets''  form ''FA_Setup_Account_Group_Maintenance'' window ''FA_Setup_Account_Group_Maintenance'' '
                

DECLARE FA_CurSOR CURSOR LOCAL STATIC FORWARD_ONLY READ_ONLY
FOR
    SELECT  Number ,
            AccountGroupID ,
            AccountGroupDescription ,
            DepreciationExpense ,
            DepreciationReserve ,
            PriorYear ,
            AssetCost ,
            ProceedAccount ,
            NonRealizedGain ,
            Realized ,
            Clearing
    FROM    GPEssentials_FA_Original_AccountGroup
    ORDER BY 1

OPEN FA_CurSOR;

FETCH NEXT FROM FA_CurSOR
       INTO @Number, @AccountGroupID, @AccountGroupDescription,@DepreciationExpense,@DepreciationReserve,
       @PriodYearDep,@AssetCost,@ProceedAccount,@Realized,
       @Non_Realized,@Clearing


WHILE @@FETCH_STATUS = 0
    BEGIN

              PRINT 'MoveTo field ''Account Group ID'''
        PRINT 'TypeTo field ''Account Group ID'' , ' + '''' + RTRIM(LTRIM(@AccountGroupID)) + ''''
        PRINT 'MoveTo field ''Account Group Description'' '
        PRINT 'TypeTo field ''Account Group Description'' , ' + '''' + RTRIM(LTRIM(@AccountGroupDescription)) + ''''

              -- Fill out the Depreciation Expense Account
              PRINT  'MoveTo field ''Account Number Array'':''Account_Segment_Pool1''[1] '
              PRINT   'TypeTo field ''Account Number Array'':''Account_Segment_Pool1''[1] , '  + '''' + SUBSTRING(@DepreciationExpense,1,3)   + ''''

              PRINT  'MoveTo field ''Account Number Array'':''Account_Segment_Pool2''[1] '
              PRINT   'TypeTo field ''Account Number Array'':''Account_Segment_Pool2''[1] , '  + '''' + SUBSTRING(@DepreciationExpense,5,4) + ''''

              PRINT  'MoveTo field ''Account Number Array'':''Account_Segment_Pool3''[1] '
              PRINT   'TypeTo field ''Account Number Array'':''Account_Segment_Pool3''[1] , '  + '''' + RIGHT(LTRIM(RTRIM(@DepreciationExpense)),2) + ''''

              -- Fill out the Depreciation Reserve Account
              PRINT  'MoveTo field ''Account Number Array'':''Account_Segment_Pool1''[2] '
              PRINT   'TypeTo field ''Account Number Array'':''Account_Segment_Pool1''[2] , '  + '''' + SUBSTRING(@DepreciationReserve,1,3)  + ''''

              PRINT  'MoveTo field ''Account Number Array'':''Account_Segment_Pool2''[2] '
              PRINT   'TypeTo field ''Account Number Array'':''Account_Segment_Pool2''[2] , '  + '''' + SUBSTRING(@DepreciationReserve,5,4) + ''''

              PRINT  'MoveTo field ''Account Number Array'':''Account_Segment_Pool3''[2] '
              PRINT   'TypeTo field ''Account Number Array'':''Account_Segment_Pool3''[2] , '  + '''' + RIGHT(LTRIM(RTRIM(@DepreciationReserve)),2) + ''''

              -- Fill out the Prior Year Depreciation Account
              PRINT  'MoveTo field ''Account Number Array'':''Account_Segment_Pool1''[3] '
              PRINT   'TypeTo field ''Account Number Array'':''Account_Segment_Pool1''[3] , '  + '''' + SUBSTRING(@PriodYearDep,1,3)  + ''''

              PRINT  'MoveTo field ''Account Number Array'':''Account_Segment_Pool2''[3] '
              PRINT   'TypeTo field ''Account Number Array'':''Account_Segment_Pool2''[3] , '  + '''' + SUBSTRING(@PriodYearDep,5,4) + ''''

              PRINT  'MoveTo field ''Account Number Array'':''Account_Segment_Pool3''[3] '
              PRINT   'TypeTo field ''Account Number Array'':''Account_Segment_Pool3''[3] , '  + '''' + RIGHT(LTRIM(RTRIM(@PriodYearDep)),2) + ''''

              -- Fill out the Asset Cost Account
              PRINT  'MoveTo field ''Account Number Array'':''Account_Segment_Pool1''[4] '
              PRINT   'TypeTo field ''Account Number Array'':''Account_Segment_Pool1''[4] , '  + '''' + SUBSTRING(@AssetCost,1,3)   + ''''

              PRINT  'MoveTo field ''Account Number Array'':''Account_Segment_Pool2''[4] '
              PRINT   'TypeTo field ''Account Number Array'':''Account_Segment_Pool2''[4] , '  + '''' + SUBSTRING(@AssetCost,5,4) + ''''

              PRINT  'MoveTo field ''Account Number Array'':''Account_Segment_Pool3''[4] '
              PRINT   'TypeTo field ''Account Number Array'':''Account_Segment_Pool3''[4] , '  + '''' + RIGHT(LTRIM(RTRIM(@AssetCost)),2) + ''''

             
              -- Fill out the Proceed Account
              PRINT  'MoveTo field ''Account Number Array'':''Account_Segment_Pool1''[5] '
              PRINT   'TypeTo field ''Account Number Array'':''Account_Segment_Pool1''[5] , '  + '''' + SUBSTRING(@ProceedAccount,1,3)  + ''''

              PRINT  'MoveTo field ''Account Number Array'':''Account_Segment_Pool2''[5] '
              PRINT   'TypeTo field ''Account Number Array'':''Account_Segment_Pool2''[5] , '  + '''' + SUBSTRING(@ProceedAccount,5,4) + ''''

              PRINT  'MoveTo field ''Account Number Array'':''Account_Segment_Pool3''[5] '
              PRINT   'TypeTo field ''Account Number Array'':''Account_Segment_Pool3''[5] , '  + '''' + RIGHT(LTRIM(RTRIM(@ProceedAccount)),2) + ''''

           -- Fill out the Realized Account
              PRINT  'MoveTo field ''Account Number Array'':''Account_Segment_Pool1''[6] '
              PRINT   'TypeTo field ''Account Number Array'':''Account_Segment_Pool1''[6] , '  + '''' + SUBSTRING(@Realized,1,2)  + ''''

              PRINT  'MoveTo field ''Account Number Array'':''Account_Segment_Pool2''[6] '
              PRINT   'TypeTo field ''Account Number Array'':''Account_Segment_Pool2''[6] , '  + '''' + SUBSTRING(@Realized,5,4) + ''''

              PRINT  'MoveTo field ''Account Number Array'':''Account_Segment_Pool3''[6] '
              PRINT   'TypeTo field ''Account Number Array'':''Account_Segment_Pool3''[6] , '  + '''' + RIGHT(LTRIM(RTRIM(@Realized)),3) + ''''

         -- Fill out the Non-Realized Account
              PRINT  'MoveTo field ''Account Number Array'':''Account_Segment_Pool1''[7] '
              PRINT   'TypeTo field ''Account Number Array'':''Account_Segment_Pool1''[7] , '  + '''' + SUBSTRING(@Non_Realized,1,2)  + ''''

              PRINT  'MoveTo field ''Account Number Array'':''Account_Segment_Pool2''[7] '
              PRINT   'TypeTo field ''Account Number Array'':''Account_Segment_Pool2''[7] , '  + '''' + SUBSTRING(@Non_Realized,5,4) + ''''

              PRINT  'MoveTo field ''Account Number Array'':''Account_Segment_Pool3''[7] '
              PRINT   'TypeTo field ''Account Number Array'':''Account_Segment_Pool3''[7] , '  + '''' + RIGHT(LTRIM(RTRIM(@Non_Realized)),2) + ''''

        -- Fill out the Non-Realized Account
              PRINT  'MoveTo field ''Account Number Array'':''Account_Segment_Pool1''[8] '
              PRINT   'TypeTo field ''Account Number Array'':''Account_Segment_Pool1''[8] , '  + '''' +  SUBSTRING(@Clearing,1,2)   + ''''

              PRINT  'MoveTo field ''Account Number Array'':''Account_Segment_Pool2''[8] '
              PRINT   'TypeTo field ''Account Number Array'':''Account_Segment_Pool2''[8] , '  + '''' + SUBSTRING(@Clearing,5,4) + ''''

              PRINT  'MoveTo field ''Account Number Array'':''Account_Segment_Pool3''[8] '
              PRINT   'TypeTo field ''Account Number Array'':''Account_Segment_Pool3''[8] , '  + '''' + RIGHT(LTRIM(RTRIM(@Clearing)),2) + ''''

              PRINT    'MoveTo field ''Account Group Description'' '
              PRINT    'CommandExec dictionary ''Fixed Assets''  form ''FA_Setup_Account_Group_Maintenance'' command ''Save Button_w_FA_Setup_Account_Group_Maintenance_f_FA_Setup_Account_Group_Maintenance'
              PRINT    'NewActiveWin dictionary ''Fixed Assets''  form ''FA_Setup_Account_Group_Maintenance'' window ''FA_Setup_Account_Group_Maintenance'
              PRINT '
                     '
        FETCH NEXT FROM FA_CurSOR
                     INTO
                     @Number, @AccountGroupID, @AccountGroupDescription,@DepreciationExpense,@DepreciationReserve,
       @PriodYearDep,@AssetCost,@ProceedAccount,@Realized,
       @Non_Realized,@Clearing
    END

CLOSE FA_CurSOR;
DEALLOCATE FA_CurSOR;


Best Regards,
Mahmoud M. AlSaadi

No comments:

Post a Comment