Featured Post

Dynamics GP 2016 R2 is Now Available .. and #MSDynGP will Always be Avilable

The Microsoft Dynamics GP team announced today that Microsoft Dynamics GP 2016 R2 has released . Dynamics GP 2016 R2 continues the grea...

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

No comments:

Post a Comment