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