Data Cleansing Essentials Series | Dummy 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:
-
Correct item site assignment ensures that no site will be picked incorrectly by an accountant or a warehouse keeper when entering a transactions
-
Inventory Reporting and inquires will show only the associated sites under the “site ID” field, which makes it an easier experience
-
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.
-
IV10200 | Inventory Purchase Receipt
-
IV10001 | Un posted Inventory Transactions
-
IV00102 | Item Site Assignment
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
*This post is locked for comments