AnsweredAssumed Answered

Manual Delete Investments via SQL

Question asked by SalukiJim on Apr 13, 2012
Latest reply on Apr 17, 2012 by Michael
Greetings,

We are having issues getting the Delete Investments job to run. It keeps failing, and several conversations with CA help have not yet yielded anything.

My question, has anyone explored a straight SQL delete of INV_INVESTMENT data and related tables?
I was tinkering in our DEV sandbox area, and am thinking of the following (sorry for the formatting).
Has anyone done anything like this, and do you see potential pitfalls with this approach?

create table #ids_to_delete
(ID int)

insert into #ids_to_delete
select id
from INV_INVESTMENTS i
where i.PURGE_FLAG = 1
and i.ID in (5000193,5000194,5000195,5000196,5000209,5000217,5000225,5000233,5000241,5000249)

print 'deleting inv_hierarchies'
delete
from INV_HIERARCHIES
where PARENT_ID in (select ID from #ids_to_delete )

print 'deleting inv_flat_hierarchies'
delete
from INV_flat_HIERARCHIES
where PARENT_ID in (select ID from #ids_to_delete )

print 'deleting odf_ca_inv'
delete
from odf_ca_inv
where id in (select ID from #ids_to_delete )

print 'deleting inv_investment_allocs_flat'
delete
from INV_INVESTMENT_ALLOCS_FLAT
where FROM_INV_ID in (select ID from #ids_to_delete )

print 'deleting odf_object_instance_mapping'
delete
from ODF_OBJECT_INSTANCE_MAPPING
where PRIMARY_OBJECT_INSTANCE_ID in (select ID from #ids_to_delete )

print 'deleting pac_mnt_projects'
delete
from PAC_MNT_PROJECTS
where PROJECT_CODE in (select code from INV_INVESTMENTS i where ID in (select ID from #ids_to_delete ))

print 'deleting prj_baselines'
delete
from PRJ_BASELINES
where PROJECT_ID in (select ID from #ids_to_delete )

print 'deleting prj_baseline_details'
delete
from PRJ_BASELINE_DETAILS
where OBJECT_TYPE = 'project'
and OBJECT_ID in (select ID from #ids_to_delete )

print 'deleting prassignment'
delete
from PRASSIGNMENT
where PRTASKID in (select pt.PRID from PRTASK pt where pt.PRPROJECTID in (select ID from #ids_to_delete ))

print 'deleting prtask'
delete
from PRTASK
where PRPROJECTID in (select ID from #ids_to_delete )

print 'deleting inv_hierarchies'
delete
from INV_HIERARCHIES
where PARENT_ID in (select id from #ids_to_delete )

print 'deleting prj_ev_history'
delete
from PRJ_EV_HISTORY
where PROJECT_ID in (select ID from #ids_to_delete )

print 'deleting prj_project_depends'
delete
from PRJ_PROJECT_DEPENDS
where PRINCIPAL_ID in (select PRID from INV_PROJECTS ip where ip.PRID in (select i.IDEA_ID from INV_INVESTMENTS i where ID in (select ID from #ids_to_delete )))

print 'deleting inv_projects'
delete
from INV_PROJECTS
where PRID in (select i.IDEA_ID from INV_INVESTMENTS i where ID in (select ID from #ids_to_delete ))

print 'deleting inv_investments'
delete
from INV_INVESTMENTS
where PURGE_FLAG = 1
and ID in (select ID from #ids_to_delete )

Outcomes