Featured Post

Dynamics GP 2019 is now Released

It is now released, Microsoft Dynamics GP 2019 which moves the system to the modern product life cycle   " Beginning with the October...

Tuesday, February 3, 2015

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 .


Data Cleansing Essentials Series 

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.

Item Site Assignment

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

3 comments:

  1. Hello

    Is it possible that the script can be run for a specific site ID and delete all the item assignments related to that site only?

    We have around 30 sites in our system but have never been used at all, but all items are assigned to those sites (our policy is to always assign all items to all sites).

    Thanks

    Mark

    ReplyDelete
    Replies
    1. It can be ... The script needs further modifications.

      Is it urgent ? I can get it scheduled.


      Best Regards,
      Mahmoud M. AlSaadi

      Delete
  2. BE SMART AND BECOME RICH IN LESS THAN 3DAYS....It all depends on how fast 
    you can be to get the new PROGRAMMED blank ATM card that is capable of
    hacking into any ATM machine,anywhere in the world. I got to know about 
    this BLANK ATM CARD when I was searching for job online about a month 
    ago..It has really changed my life for good and now I can say I'm rich and 
    I can never be poor again. The least money I get in a day with it is about 
    $50,000.(fifty thousand USD) Every now and then I keeping pumping money 
    into my account. Though is illegal,there is no risk of being caught 
    ,because it has been programmed in such a way that it is not traceable,it 
    also has a technique that makes it impossible for the CCTVs to detect 
    you..For details on how to get yours today, email the hackers on : (
    atmmachinehackers1@gmail.com ). Tell your 
    loved once too, and start to live large. That's the simple testimony of how 
    my life changed for good...Love you all ...the email address again is ;
    atmmachinehackers1@gmail.com

    ReplyDelete