SQL Query To Check For Inventory Items With Incorrect User Defined Category Field

Dynamics GP has a small "bug" in the inventory module related to user defined fields. GP allows you to configure 5 user defined fields at the item setup level that are drop down boxes. Go to Setup > Inventory > Inventory Control and you can configure the "user category" fields. However, if you change one of these setups, the change does NOT automatically update the value in any item that might be assigned to the value you just changed. So you end up with an item that has a user category in it that is no longer a valid category in the user category setup.

To check for items with a bad user category, run this sql query:

select itemnmbr, itemdesc, '4' as Category, USCATVLS_4 as Value from IV00101

where USCATVLS_4 not in (select uscatval from IV40600 where USCATNUM=4)

and ITEMTYPE=1

union all

select itemnmbr, itemdesc, '5' , USCATVLS_5 from IV00101

where USCATVLS_5 not in (select uscatval from IV40600 where USCATNUM=5)

and ITEMTYPE=1

order by itemnmbr