Hi.

I need to delete all ad-hoc groups for Accounts and Contacts.
I was thinking on this:


delete from sysdba.ADHOCGROUP
where ENTITYID in (select CONTACTID from sysdba.CONTACT)
or ENTITYID in (select ACCOUNTID from sysdba.ACCOUNT);

delete from sysdba.PLUGIN
where TYPE = 8
and (FAMILY like 'Contact' or FAMILY like 'Account')
and PLUGINID in (select GROUPID from sysdba.ADHOCGROUP);



The strange thing (at least to me) is that when I get the count for both statements, the numbers are very different:

select COUNT(*)
from sysdba.PLUGIN
where TYPE = 8
and (FAMILY like 'Contact' or FAMILY like 'Account')
and PLUGINID in (select GROUPID from sysdba.ADHOCGROUP);
==> Result: 132

select COUNT(*)
from sysdba.ADHOCGROUP
where ENTITYID in (select CONTACTID from sysdba.CONTACT)
or ENTITYID in (select ACCOUNTID from sysdba.ACCOUNT);
==> Result: 13688


Do you think the two delete sentences above will correctly delete the ad-hoc groups for Accounts and Contacts?

Regards,

   Marcos

sysdba.PLUGIN contains one record for each adhocgroup 

sysdba,adhacgroup contains one record per member per adhac group. If I create a group with 5 members there will be one record in sysdba.plugin and 5 in sysdba.adhacgroup. so the numbers you are seeing are accurate. 

If you are going to use these 2 statements just make sure to run 

delete from sysdba.PLUGIN 
where TYPE = 8
and (FAMILY like 'Contact' or FAMILY like 'Account')
and PLUGINID in (select GROUPID from sysdba.ADHOCGROUP);

Before you run the other one else you will not remove any plug-ins.  

Excellent Mr. Welther, that's how we've been doing it.....better safe than sorry....

The other item to key off is DATACODE.

Otherwise you won't remove Releases of these groups.

The best way to determine ADHOCGROUPS (not just the ones with Associated Data):

SELECT * FROM PLUGIN WHERE TYPE = 8 AND DATACODE IS NOT NULL AND DATACODE <> ''

When an Adhoc Group is created, it sets the DATACODE to the PLUGINID.

When releases are created, the releases set the BASEDON to match the PLUGINID of the Original Group (this is the same for all PLUGINS), but in addition, on ADHOC groups the DATACODE is set to the PLUGINID of the Original group.

Also, I do recall that when you Copy ADHOC groups, the BASEDON may not be set (as this is a new group path), but if it is an ADHOC group, the DATACODE field gets populated (again, with the original PLUGINID).

Any entities added to the Groups get added to the ADHOC group table with the DATACODE as the GROUPID.

Thanks RJ and Raul

While we are on the subject of extra groups you can run the 2 scripts below to remove all the unreleased groups from retired users. Then remove all the adhacgroup records that no longer match a group. This is a good thing to do every once in a while as eventually these 2 tables will acquire a lot of unused data. (the SLX provider will parse these)

Delete from sysdba.PLUGIN
Where PLUGINID in (
Select PLUGINID from sysdba.PLUGIN p
INNER JOIN sysdba.USERSECURITY us on p.USERID = us.USERID
where p.TYPE = 8
and us.TYPE = 'R')
-- you can add “and PLUGINID in (select GROUPID from sysdba.ADHOCGROUP)” if you only want to remove the adhac groups

Delete from sysdba.ADHOCGROUP
Where GROUPID not in
(select GROUPID from sysdba.ADHOCGROUP
Where GROUPID not in (select PLUGINID
from sysdba.PLUGIN p));