Go Back > Forum > Baan SIGs > Code & Utilities

User login

Frontpage Sponsor


Where is your Baan/LN hardware located?
On Premise
Hosted Data Center
Cloud (Azure/AWS - Not Infor Cloudsuite)
Infor Cloudsuite
Total votes: 5

Baanboard at LinkedIn

Reference Content

Thread Tools Display Modes
Old 17th January 2003, 19:42
Francesco's Avatar
Francesco Francesco is offline
Join Date: Aug 2001
Location: Antwerp, BE
Posts: 727
Francesco is on a distinguished road
Baan: 5b - DB: Oracle - OS: Solaris
Script: removing unwanted items from Baan

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?)

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

        echo 'USAGE:'
        echo '  remitem.ksh <item>'

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


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

print tcibd001 > /tmp/remitem.tables
findrefs tcibd001

# build sql
touch remitem.sql
print spool remitem > remitem.sql
for TABLE in $(cat /tmp/remitem.tables)
        print "select '${TABLE}', count(0) from t${TABLE}100 a where substr(a.t\$item,10,${LENGTH})='$1 ';" >> remitem.sql
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.



Admiral Business Solutions | My World | Baan Board | IT Happens!

"If everyone is thinking alike, then somebody isn't thinking" -- George Patton
"It's easy to cry 'bug' when the truth is that you've got a complex system and sometimes it takes a while to get all the components to co-exist peacefully." -- Doug Vargas
Reply With Quote
Sponsored Links

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are Off
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
problem with items import in Baan CRM trchandra Baan CRM & Front-Office 0 7th June 2002 01:06
Baan World Users conference in Orlando chadwickh General Discussion & Chat 7 3rd November 2001 21:55
Availability of Web-Based DEM (Baan Press Release) Pierre B. General Discussion & Chat 0 5th September 2001 05:59
BSP-US (LLC) added as a Baan Education Alliance svandenh General Discussion & Chat 1 20th August 2001 19:44

All times are GMT +2. The time now is 17:20.

©2001-2018 - -