Data Cleansing Essentials Series | Chart of Account Analysis “Unused Accounts”

Data cleansing is a wide topic that extends over a variety of issues related to master files specifically, it’s due to the fact that data is prone to “corruption or deformation” especially when the process of configuring master files is not properly managed. As derived from the above, Dynamics GP Essentials blog will start an extended series related to “Data Cleansing techniques” that could be useful either to diagnose, detect or resolve data cleansing issues. The series will provide theory, scripts, excel templates and any valuable information or tools that can be of an added value in this topic.

The starting point will be the General Ledger module with the Chart of Accounts (COA) which could possibly have a number of unused accounts. The script below is valuable as related to:

- Detecting which accounts that have never been used
- Detecting the last time the account was used in (years, months and days)

The difference between the current data and the last date at which the account was used is calculated and provided separately either in three different calculations (No of years, No of months and No of Years). Lets check the following scenarios:

Scenario One

An account is configured on (01-01-2012), and never been used. The time at which the report is generated is (01-11-2014)

• Never Used = Yes
• Not used since (In years) = 2 years
• Not used since (In Month) = 34 Months
• Not used since (In Days) = 1035 days

Scenario Two

An account is configured on (01-01-2012), and was last used (31-12-2012). The time at which the report is generated is (01-11-2014)

• Never Used = No
• Last used = 31-12-2012
• Not used since (In years) = 2 years
• Not used since (In Month) = 23 Months
• Not used since (In Days) = 670 days

The data provided above can play a crucial role in determining which accounts to keep and which account to remove. The business should keep precise parameters which are considered as the main driver of the correction process.

-- Tables Included:

• GL00100 | Account Master
• GL00105 | Account Index Master
• GL20000 | Year-to-Date Transaction Open
• GL30000 | Account Transaction History

 

SELECT  B.ACTNUMST 'Account Number' ,
        A.ACTDESCR 'Account Description' ,
        A.CREATDDT 'Created Date' ,
        CASE
           WHEN ISNULL(D.TRX_Date, 0) < A.CREATDDT
           THEN 'Yes'
           ELSE ''
        END AS NeverUsed ,
        ISNULL(D.TRX_Date, 0) AS 'Last Used' ,
        DATEDIFF(YY, ISNULL(D.TRX_Date, 0), GETDATE())
            'Not Used Since (In Years)' ,
        DATEDIFF(MM, ISNULL(D.TRX_Date, 0), GETDATE())
            'Not Used Since (In Months)' ,
        DATEDIFF(DD, ISNULL(D.TRX_Date, 0), GETDATE())
            'Not Used Since (In Days)'
        FROM    dbo.GL00100 AS A
            LEFT OUTER JOIN dbo.GL00105 AS B ON A.ACTINDX = B.ACTINDX
            LEFT OUTER JOIN ( SELECT ACTINDX ,
                                     MAX(TRX_Date) TRX_Date
                                     FROM ( SELECT    ACTINDX ,
                                            MAX(TRXDATE) AS TRX_Date
                                            FROM      dbo.GL20000
                                            GROUP BY  ACTINDX
                                            UNION ALL
                                            SELECT    ACTINDX ,
                                            MAX(TRXDATE) AS TRX_Date
                                            FROM      dbo.GL30000
                                            GROUP BY  ACTINDX
                                           ) AS C
                                     GROUP BY  C.ACTINDX
                            ) AS D ON B.ACTINDX = D.ACTINDX

Best Regards,
Mahmoud M. AlSaadi