Data Cleansing Essentials Series | Dummy Item-Site assignment

In the previous article of the data essentials series, a script was provided in order to determine the status of the Chart of Accounts and make sure that no “un-used” accounts exist. In this post, another script is provided to handle another data quality issue related to the inventory module. It is primarily about the incorrect item-site assignment .

Inventory items should only be assigned to the actual sites on which there are transactions. Although, it is a very common case to find items assigned to site that they don’t actually have transactions on. The reasons are quite too many, either due to the lack of correct master files at the implementation phases, granting access to the item-site assignment window to several users without any segregation of duty or control, or many possible other reasons.

There are many reasons why the item-site assignment topic should be considered by clients, some of the reasons are:

1. Correct item site assignment ensures that no site will be picked incorrectly by an accountant or a warehouse keeper when entering a transactions
2. Inventory Reporting and inquires will show only the associated sites under the “site ID” field, which makes it an easier experience
3. The cost of correcting invalid item-site assignment after transactions are posted could cause data quality issues. Taking into consideration that deleting item-site assignment will remove all inventory cost layers.

A very simple example below shows that a “raw material” item is assigned to the Finished Goods warehouse, which is totally incorrect. Deleting this assignment will ensure that no incorrect transactions for the raw material item will be posted to the FG warehouse.

How to detect the problem

The SQL script below checks the posted and un-posted transactions to find actual current assignments. Then look for the master assignment as retrieved from the IV00102 table. In case there is an assignment on which there is no actual (saved or posted) transactions, this assignment will be deleted.

Note !

• Do Not run on live environment unless you have done a previous test of the result. The script below “Deletes” records from the database as derived form the logic described above.
• Taking a back up of your database beforehand is a must.

Tables Included:

• IV10200 | Inventory Purchase Receipt
• IV10001 | Un posted Inventory Transactions
• IV00102 | Item Site Assignment

DELETE  FROM dbo.IV00102
WHERE   DEX_ROW_ID IN (
                        SELECT DISTINCT
                        DEX_ROW_ID
                        FROM (
                             SELECT DISTINCT
                             DEX_ROW_ID ,
                             RTRIM(ITEMNMBR) + '-' + RTRIM(LOCNCODE) AS ITEM_INDEX
                             FROM      dbo.IV00102
                             WHERE     RCRDTYPE = 2
                             AND RTRIM(ITEMNMBR) + '-' + RTRIM(LOCNCODE)
                               NOT IN (
                               SELECT DISTINCT
                               RTRIM(ITEMNMBR) + '-' + RTRIM(TRXLOCTN) AS ITEM_INDEX
                               FROM    dbo.IV10200
                               UNION ALL
                               SELECT DISTINCT
                               RTRIM(ITEMNMBR) + '-' + RTRIM(TRXLOCTN) AS ITEM_INDEX
                               FROM    dbo.IV10001 )
                         ) IncorrectAssignemtn )

 

Best Regards,
Mahmoud M. AlSaadi

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