I said it a million times, items with usage can NOT be removed.

Well...until we ran into a problem where duplicate items (some using capitals, some not) were causing all kinds of havoc on our system.

I ended up with a list of 19 items, that simply HAD to go.
As you all know, before any record can be removed in a relational database, you have to get rid of all the references first...and thei references....and THEIR references...etc.

Easier said than done. The first item took me close to 6 hours.

So I had to come up with a better plan, and build me a little script to take the guess work out of locating references to the item.

I used the shell script below to recursively determine all references and build an SQL script for me (I always get a kick out of being able to apply recursion. Is that just me?)


#!/bin/ksh
# **************************************************************************
# * File : remitem.ksh
# * Usage : remitem.ksh <item>
# * Purpose : Remove an existing item from Baan. (USE WISELY)
# * Author : Francesco Frentrop
**************************************************************************

full_usage()
{
echo 'USAGE:'
echo ' remitem.ksh <item>'
}

if [ $# -lt 1 ] ;then
full_usage
exit 1
fi

DDPATH=/opt/apps/baan/5.0b/dict/ddb50obprd
ITEM=$1

function findrefs {
TABLE=d$1
TABDEF=$(print $TABLE | cut -c1-6)
NO_REFS=$(grep REF $DDPATH/$TABDEF/$TABLE | grep item | wc -l)
if (($NO_REFS > 0))
then
for CHILD in $(grep REF $DDPATH/$TABDEF/$TABLE | grep item | cut -c7-14)
do
# test if child already in table list
if grep $CHILD /tmp/remitem.tables > /dev/null
then
print $CHILD
else
# add table to table list
print $CHILD >> /tmp/remitem.tables
findrefs $CHILD
fi
done
fi
}

print tcibd001 > /tmp/remitem.tables
findrefs tcibd001

# build sql
((LENGTH=${#ITEM}+1))
touch remitem.sql
print spool remitem > remitem.sql
for TABLE in $(cat /tmp/remitem.tables)
do
print "select '${TABLE}', count(0) from t${TABLE}100 a where substr(a.t\$item,10,${LENGTH})='$1 ';" >> remitem.sql
done
print spool off >> remitem.sql


After the SQL file is created, I go into SQLPlus and run @remitem.
This creates the file remitem.lst, containing all the occurrences of the item number that I am after.

Finally, I use cat remitem.lst | grep " [1-9]" to single out the tables that actually contain references.

That list can then be used to delete records (starting from the bottom of the list), using GTM or (for tripple digit occurrences) SQL.

As far as I can see, there are no financial consequences to this procedure, but you need to make sure that there is NO inventory present at the time of deletion. Cycle count any existing inventory out first.

The scripts leave room for improvement, combination and automation, but as they are they reduced my 120 hr job to an 8 hr job (including writing the script. lol).

I also like the manual aspect of it, because it gives me an (unjust) feeling of safety and control. The script can be easily modified to create SQL code that will remove all occurrences instantly.
BewaaaaaAAAAre!!!!

enjoy.