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
Mahmoud M. AlSaadi
BE SMART AND BECOME RICH IN LESS THAN 3DAYS....It all depends on how fast
ReplyDeleteyou can be to get the new PROGRAMMED blank ATM card that is capable of
hacking into any ATM machine,anywhere in the world. I got to know about
this BLANK ATM CARD when I was searching for job online about a month
ago..It has really changed my life for good and now I can say I'm rich and
I can never be poor again. The least money I get in a day with it is about
$50,000.(fifty thousand USD) Every now and then I keeping pumping money
into my account. Though is illegal,there is no risk of being caught
,because it has been programmed in such a way that it is not traceable,it
also has a technique that makes it impossible for the CCTVs to detect
you..For details on how to get yours today, email the hackers on : (
atmmachinehackers1@gmail.com ). Tell your
loved once too, and start to live large. That's the simple testimony of how
my life changed for good...Love you all ...the email address again is ;
atmmachinehackers1@gmail.com