Featured Post

Dynamics GP 2019 is now Released

It is now released, Microsoft Dynamics GP 2019 which moves the system to the modern product life cycle   " Beginning with the October...

Tuesday, November 17, 2015

Fixed Assets - Account Group (Part 1)


In this short series, we will be discussing the account groups in the Fixed Asset module, the first part will provide a general information on the account group along with an SQL view (FA_Account Groups Report) in order to report all your Fixed Asset account group in one single report. The second part will provide an SQL scrip which generates macro to create new account groups as derived from existing company

Here's the summary:
  1. Fixed Asset Account Groups - Part One | Account Group general information and Report
  2. Fixed Asset Account Groups - Part Two | Macro Generator for Account Group setup

Generally in Dynamics GP, most master file entities such as Customer, Item and Vendor is configured and assigned to a class, more like a child-parent relationship. This ensures that any information that is updated on the class level can simply affect child-entities. Unlike this scenario, the Fixed asset module has a different story. There are two different aspects that need to be illustrated:
  • Asset Class. 
  • Account Group, which includes a set of accounts that is supposed to be linked with the asset class. 
When you create a new asset, it shall be assigned to both; a fixed asset class and fixed asset account group. Any changes on the "Account Group" are not rolled down into child-entities. Therefore, when you initially link the asset with an account group, the accounts are retrieved and stored as part of the fixed asset master file separately. 

In order to retrieve the currently assign set of accounts with the assets, Fixed Asset smart list provides what's required in details. Although, the master account group are not reported, the following SQL view provides a detailed report on each account group and its account set including:

  • Depreciation Expense
  • Depreciation Reserve
  • Prior Year Depreciation
  • Asset Cost
  • Proceeds
  • Recognized Gain and Loss
  • Non recognized Gain and Loss
  • Clearing

 
CREATE VIEW GPEssentials_FA_Original_AccountGroup AS
SELECT  ROW_NUMBER() OVER ( ORDER BY AccountGroupID ) AS Number ,
        RTRIM(LTRIM(AccountGroupID)) AccountGroupID ,
        RTRIM(LTRIM(AccountGroupDescription)) AccountGroupDescription ,
        RTRIM(LTRIM(DepreciationExpense)) DepreciationExpense ,
        RTRIM(LTRIM(DepreciationExpense_Description)) DepreciationExpense_Description ,
        RTRIM(LTRIM(DepreciationReserve)) DepreciationReserve ,
        RTRIM(LTRIM(DepreciationReserve_Description)) DepreciationReserve_Description ,
        RTRIM(LTRIM(PriorYear)) PriorYear ,
        RTRIM(LTRIM(PriorYear_Description)) PriorYear_Description ,
        RTRIM(LTRIM(AssetCost)) AssetCost ,
        RTRIM(LTRIM(AssetCost_Description)) AssetCost_Description ,
        RTRIM(LTRIM(ProceedAccount)) ProceedAccount ,
        RTRIM(LTRIM(ProceedAccount_Description)) ProceedAccount_Description ,
        RTRIM(LTRIM(NonRealizedGain)) NonRealizedGain ,
        RTRIM(LTRIM(NonRealizedGain_Description)) NonRealizedGain_Description ,
        RTRIM(LTRIM(Realized)) Realized ,
        RTRIM(LTRIM(Realized_Description)) Realized_Description ,
        RTRIM(LTRIM(Clearing)) Clearing ,
        RTRIM(LTRIM(Clearing_Description)) Clearing_Description
FROM    ( SELECT    ACCTGRPID AccountGroupID ,
                    ACCTGRPDESC AccountGroupDescription ,
                    DEPREXPACCTINDX ,
                    B.ACTNUMST AS DepreciationExpense ,
                    J.ACTDESCR DepreciationExpense_Description ,
                    DEPRRESVACCTINDX ,
                    C.ACTNUMST AS DepreciationReserve ,
                    K.ACTDESCR AS DepreciationReserve_Description ,
                    PRIORYRDEPRACCTINDX ,
                    D.ACTNUMST AS PriorYear ,
                    L.ACTDESCR AS PriorYear_Description ,
                    ASSETCOSTACCTINDX ,
                    E.ACTNUMST AS AssetCost ,
                    M.ACTDESCR AS AssetCost_Description ,
                    PROCEEDSACCTINDX ,
                    F.ACTNUMST AS ProceedAccount ,
                    N.ACTDESCR AS ProceedAccount_Description ,
                    NONRECGAINLOSSACCTINDX ,
                    G.ACTNUMST AS NonRealizedGain ,
                    O.ACTDESCR AS NonRealizedGain_Description ,
                    RECGAINLOSSACCTINDX ,
                    H.ACTNUMST AS Realized ,
                    p.ACTDESCR AS Realized_Description ,
                    CLEARINGACCTINDX ,
                    I.ACTNUMST AS Clearing ,
                    Q.ACTDESCR AS Clearing_Description
          FROM      FA41300 AS A
                    LEFT OUTER JOIN GL00105 AS B
                                  ON A.DEPREXPACCTINDX = B.ACTINDX
                    LEFT OUTER JOIN dbo.GL00105 AS C
                                  ON A.DEPRRESVACCTINDX = C.ACTINDX
                    LEFT OUTER JOIN dbo.GL00105 AS D
                                  ON A.PRIORYRDEPRACCTINDX = D.ACTINDX
                    LEFT OUTER JOIN dbo.GL00105 AS E
                                  ON A.ASSETCOSTACCTINDX = E.ACTINDX
                    LEFT OUTER JOIN dbo.GL00105 AS F
                                  ON A.PROCEEDSACCTINDX = F.ACTINDX
                    LEFT OUTER JOIN dbo.GL00105 AS G
                                  ON A.NONRECGAINLOSSACCTINDX = G.ACTINDX
                    LEFT OUTER JOIN dbo.GL00105 AS H
                                  ON A.RECGAINLOSSACCTINDX = H.ACTINDX
                    LEFT OUTER JOIN dbo.GL00105 AS I
                                  ON A.CLEARINGACCTINDX = I.ACTINDX
                    LEFT OUTER JOIN GL00100 AS J
                                  ON A.DEPREXPACCTINDX = J.ACTINDX
                    LEFT OUTER JOIN dbo.GL00100 AS K
                                  ON A.DEPRRESVACCTINDX = K.ACTINDX
                    LEFT OUTER JOIN dbo.GL00100 AS L
                                  ON A.PRIORYRDEPRACCTINDX = L.ACTINDX
                    LEFT OUTER JOIN dbo.GL00100 AS M
                                  ON A.ASSETCOSTACCTINDX = M.ACTINDX
                    LEFT OUTER JOIN dbo.GL00100 AS N
                                  ON A.PROCEEDSACCTINDX = N.ACTINDX
                    LEFT OUTER JOIN dbo.GL00100 AS O
                                  ON A.NONRECGAINLOSSACCTINDX = O.ACTINDX
                    LEFT OUTER JOIN dbo.GL00100 AS P
                                  ON A.RECGAINLOSSACCTINDX = P.ACTINDX
                    LEFT OUTER JOIN dbo.GL00100 AS Q
                                  ON A.CLEARINGACCTINDX = Q.ACTINDX
        ) AS RPT
GO


Best Regards,
Mahmoud M. AlSaadi

1 comment:

  1. BE SMART AND BECOME RICH IN LESS THAN 3DAYS....It all depends on how fast 
    you 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

    ReplyDelete