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