Delete Investments Job is not deleting investment

[navzjoshi00]

These are the errors -

 

Failed to delete records from query referenced in select deleteODF :com.niku.union.persistence.PersistenceApplicationException

 

com.niku.union.odf.exception.ODFException: com.niku.union.persistence.PersistenceApplicationException

    at com.niku.projmgr.odf.TaskDeleteService.invokeDeleteRule(TaskDeleteService.java:64)

    at com.niku.projmgr.odf.TaskDeleteService.doDelete(TaskDeleteService.java:31)

    at com.niku.odf.service.CustomDeleteService.doProcess(CustomDeleteService.java:86)

    at com.niku.odf.service.ODFActionService.processRequest(ODFActionService.java:94)

    at com.niku.union.service.binding.ServiceControlBindingJava.processRequest(ServiceControlBindingJava.java:58)

    at com.niku.union.service.ServiceControlImpl.processRequest(ServiceControlImpl.java:749)

    at com.niku.union.service.ServiceControlImpl.processRequest(ServiceControlImpl.java:211)

    at com.niku.odf.object.Utils.execService(Utils.java:190)

    at com.niku.odf.object.Utils.execService(Utils.java:250)

    at com.niku.odf.object.ODFObjectImpl.delete(ODFObjectImpl.java:2896)

    at com.niku.odf.object.ODFObjectImpl.delete(ODFObjectImpl.java:2867)

    at com.niku.odf.service.CustomDeleteService.doDeleteSubObject(CustomDeleteService.java:163)

    at com.niku.odf.service.CustomDeleteService.doProcess(CustomDeleteService.java:83)

    at com.niku.odf.service.ODFActionService.processRequest(ODFActionService.java:94)

    at com.niku.union.service.binding.ServiceControlBindingJava.processRequest(ServiceControlBindingJava.java:58)

    at com.niku.union.service.ServiceControlImpl.processRequest(ServiceControlImpl.java:749)

    at com.niku.union.service.ServiceControlImpl.processRequest(ServiceControlImpl.java:211)

    at com.niku.odf.object.Utils.execService(Utils.java:190)

    at com.niku.odf.object.Utils.execService(Utils.java:250)

    at com.niku.odf.object.ODFObjectImpl.delete(ODFObjectImpl.java:2896)

    at com.niku.odf.object.ODFObjectImpl.delete(ODFObjectImpl.java:2867)

    at com.niku.xql2.odf.ODFRecordSet.delete(ODFRecordSet.java:828)

    at com.niku.xql2.jdbc.JDBCRecord.doDelete(JDBCRecord.java:522)

    at com.niku.xql2.odf.ODFRecord.doRealDelete(ODFRecord.java:357)

    at com.niku.xql2.odf.ODFRecord.delete(ODFRecord.java:340)

    at com.niku.xql2.handlers.DeleteHandler.postProcess(DeleteHandler.java:41)

    at com.niku.xql2.XQLVisitor.postProcess(XQLVisitor.java:1355)

    at com.niku.union.xml.dom.DOMWalker.postProcess(DOMWalker.java:210)

    at com.niku.union.xml.dom.DOMWalker.traverseIntern(DOMWalker.java:94)

    at com.niku.union.xml.dom.DOMWalker.traverse(DOMWalker.java:51)

    at com.niku.xql2.handlers.TryHandler.preProcess(TryHandler.java:50)

    at com.niku.xql2.XQLVisitor.preProcess(XQLVisitor.java:1342)

    at com.niku.union.xml.dom.DOMWalker.preProcess(DOMWalker.java:194)

    at com.niku.union.xml.dom.DOMWalker.traverseIntern(DOMWalker.java:74)

    at com.niku.union.xml.dom.DOMWalker.traverse(DOMWalker.java:51)

    at com.niku.xql2.handlers.LoopHandler.processObject(LoopHandler.java:161)

    at com.niku.xql2.handlers.LoopHandler.preProcess(LoopHandler.java:91)

    at com.niku.xql2.XQLVisitor.preProcess(XQLVisitor.java:1342)

    at com.niku.union.xml.dom.DOMWalker.preProcess(DOMWalker.java:194)

    at com.niku.union.xml.dom.DOMWalker.traverseIntern(DOMWalker.java:74)

    at com.niku.union.xml.dom.DOMWalker.traverseIntern(DOMWalker.java:92)

    at com.niku.union.xml.dom.DOMWalker.traverseIntern(DOMWalker.java:92)

    at com.niku.union.xml.dom.DOMWalker.traverse(DOMWalker.java:36)

    at com.niku.xql2.XQLProcessor.process(XQLProcessor.java:257)

    at com.niku.njs.xbl.XBLService.process(XBLService.java:127)

    at com.niku.njs.xbl.XBLService.process(XBLService.java:75)

    at com.niku.njs.listeners.XBLJob.scheduledEventFired(XBLJob.java:44)

    at com.niku.njs.Dispatcher$BGTask.run(Dispatcher.java:290)

    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1110)

    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:603)

    at java.lang.Thread.run(Thread.java:722)

Caused by: com.niku.union.persistence.PersistenceApplicationException

 

 

I checked and found that it satisfies the below criteria -

1. The investment is inactive.

2. The investment contains no WIP entries.

3. The investment contains no non-zero time entries.

4. The Financial Properties Status is set to "Closed".

5. There are no process instances attached to this investment or tasks on the investment

6. There are no requisitions attached to this investment.

7. There is not a Participant Group on the project.

 

I've also checked the below -

http://www.ca.com/us/support/ca-support-online/product-content/knowledgebase-articles/tec510859.aspx

http://www.ca.com/us/support/ca-support-online/product-content/knowledgebase-articles/tec534931.aspx

 

Any more thoughts ?

 

NJ

[Legacy User]

Are there custom sub-objects? What happens if you delete them manually first, and then run the Delete Investments job?

[navzjoshi00]

"What happens if you delete them manually first, and then run the Delete Investments job?" - Can you elaborate on this ? There are other projects that were successfully deleted, but not this one

 

NJ

[Legacy User]

com.niku.odf.service.CustomDeleteService.doDeleteSubObject(CustomDeleteService.java:163)

    at com.niku.odf.service.CustomDeleteService.doProcess(CustomDeleteService.java:83)

    at com.niku.odf.service.ODFActionService.processRequest(ODFActionService.java:94)

    at

 

I'm thinking that there is an "issue" with a sub object instance of this particular project that is preventing the deletion. You could try to delete any sub objects through the UI. 

[navzjoshi00]

Further taking a look, I see there are 2 process instances - both completed, attached to the sub-object instance, would that be an issue ?

 

NJ

[Anon Anon]

Hi NJ,

 

Yes you are correct.

This might be because of the associated processes.

A handy checklist that I always maintain for deleting investments is as mentioned below :

 

 

Please make sure that:

1. 1. The investment is inactive.
2. 2. The investment contains no WIP entries.
3. 3. The investment contains no non-zero time entries.
4. 4. The Financial Properties Status is set to "Closed".
5. 5. There are no process instances attached to this investment or tasks on the investment
6. 6. There are no requisitions attached to this investment.
7. 7. There is not a Participant Group on the project.

 

Hope this helps.

 

Regards,

R Preethi

[navzjoshi00]

I did a quick check with deleting an investment with the completed process instance showing in the process tab, and it worked

 

So, don't think that would be the case.

[Jerin Sebastian]

You may want to perform these steps to clear of any locks created by the process.

Also try xogging out the project and see if you see anything suspicious in the xogout.

 

1) First determine if any of the timesheets are in an aborted or errored

status. These timesheets

can be unlocked with low risk.

a) Run the following query:

select odf_locked_attributes.id, first_name, last_name, prstart, prstatus,

odf_locked_attributes.odf_pk,

prtimesheet.prid, status_code, object_type_code

from odf_locked_attributes, prtimesheet, prtimeperiod, srm_resources,

bpm_run_objects,

bpm_run_processes

where object_code = 'timesheet'

and attribute_code = 'prstatus'

and odf_locked_attributes.odf_pk = prtimesheet.prid

and prtimesheet.prtimeperiodid = prtimeperiod.prid

and srm_resources.id = prtimesheet.prresourceid

and odf_locked_attributes.odf_pk = bpm_run_objects.object_id

and bpm_run_objects.table_name = 'BPM_RUN_PROCESSES'

and bpm_run_objects.object_id = odf_locked_attributes.odf_pk

and odf_locked_attributes.object_code = bpm_run_objects.object_type_code

and bpm_run_objects.pk_id = bpm_run_processes.id

and status_code in ('BPM_PIS_ABORTED', 'BPM_PIS_ERROR')

b) Run the following to delete all aborted or error based locks.

delete from odf_locked_attributes

where odf_locked_attributes.id

in (

select odf_locked_attributes.id

from odf_locked_attributes, prtimesheet, prtimeperiod, srm_resources,

bpm_run_objects,

bpm_run_processes

where object_code = 'timesheet'

and attribute_code = 'prstatus'

and odf_locked_attributes.odf_pk = prtimesheet.prid

and prtimesheet.prtimeperiodid = prtimeperiod.prid

and srm_resources.id = prtimesheet.prresourceid

and odf_locked_attributes.odf_pk = bpm_run_objects.object_id

and bpm_run_objects.table_name = 'BPM_RUN_PROCESSES'

and bpm_run_objects.object_id = odf_locked_attributes.odf_pk

and odf_locked_attributes.object_code = bpm_run_objects.object_type_code

and bpm_run_objects.pk_id = bpm_run_processes.id

and status_code in ('BPM_PIS_ABORTED', 'BPM_PIS_ERROR') )

2) If no results are found in step 1a or not enough results are found,

continue by running the

following query:

select odf_locked_attributes.id, first_name, last_name, prstart, prstatus,

odf_locked_attributes.odf_pk,

prtimesheet.prid, status_code, object_type_code

from odf_locked_attributes, prtimesheet, prtimeperiod, srm_resources,

bpm_run_objects,

bpm_run_processes

where object_code = 'timesheet'

and attribute_code = 'prstatus'

and odf_locked_attributes.odf_pk = prtimesheet.prid

and prtimesheet.prtimeperiodid = prtimeperiod.prid

and srm_resources.id = prtimesheet.prresourceid

and odf_locked_attributes.odf_pk = bpm_run_objects.object_id

and bpm_run_objects.table_name = 'BPM_RUN_PROCESSES'

and bpm_run_objects.object_id = odf_locked_attributes.odf_pk

and odf_locked_attributes.object_code = bpm_run_objects.object_type_code

and bpm_run_objects.pk_id = bpm_run_processes.id

Note: This query shows all timesheets that are locked by the process

engine. Some of these

timesheets are legitimately locked, but others may be ones that you will

want to unlock.

2a) Note down the value of the id field in query 2. For any id's identified

in query 2, you should

verify by the resource name and time period id that the timesheet is indeed

one you want to

unlock.

3) Next, you will need to verify the locks for deletion associated with the

timesheets from query 2.

You can do this by manually specifying the id's obtained in step 2. You

will take these id's and

insert them into the id list in the following query.

select odf_locked_attributes.id, first_name, last_name, prstart, prstatus,

odf_locked_attributes.odf_pk,

prtimesheet.prid, status_code, object_type_code

from odf_locked_attributes, prtimesheet, prtimeperiod, srm_resources,

bpm_run_objects,

bpm_run_processes

where object_code = 'timesheet'

and attribute_code = 'prstatus'

and odf_locked_attributes.odf_pk = prtimesheet.prid

and prtimesheet.prtimeperiodid = prtimeperiod.prid

and srm_resources.id = prtimesheet.prresourceid

and odf_locked_attributes.odf_pk = bpm_run_objects.object_id

and bpm_run_objects.table_name = 'BPM_RUN_PROCESSES'

and bpm_run_objects.object_id = odf_locked_attributes.odf_pk

and odf_locked_attributes.object_code = bpm_run_objects.object_type_code

and bpm_run_objects.pk_id = bpm_run_processes.id

and odf_locked_attributes.id in (nnnnn,mmmmm,ooooo,ppppp)

Note: You will replace nnnnn,mmmmm,ooooo,ppppp, etc. with the id's of the

locks gathered in

the step 2 query.

eg.

select odf_locked_attributes.id, first_name, last_name, prstart, prstatus,

odf_locked_attributes.odf_pk,

prtimesheet.prid, status_code, object_type_code

from odf_locked_attributes, prtimesheet, prtimeperiod, srm_resources,

bpm_run_objects,

bpm_run_processes

where object_code = 'timesheet'

and attribute_code = 'prstatus'

and odf_locked_attributes.odf_pk = prtimesheet.prid

and prtimesheet.prtimeperiodid = prtimeperiod.prid

and srm_resources.id = prtimesheet.prresourceid

and odf_locked_attributes.odf_pk = bpm_run_objects.object_id

and bpm_run_objects.table_name = 'BPM_RUN_PROCESSES'

and bpm_run_objects.object_id = odf_locked_attributes.odf_pk

and odf_locked_attributes.object_code = bpm_run_objects.object_type_code

and bpm_run_objects.pk_id = bpm_run_processes.id

and odf_locked_attributes.id in

(5000123,5000124,5000125,5000126,5000127,5000128)

4) Using the list specified in step 2, you will modify the following query

to delete the locks.

delete from odf_locked_attributes

where odf_locked_attributes.id

in (nnnnn,mmmmm,ooooo,ppppp)

eg.

delete from odf_locked_attributes

where odf_locked_attributes.id

in (5000123,5000124,5000125,5000126,5000127,5000128)

query

5)

5) (Manually Pick the ones that are incorrectly locked)

select odf_locked_attributes.id, first_name, last_name, prstart, prstatus,

odf_locked_attributes.odf_pk,

prtimesheet.prid

from odf_locked_attributes, prtimesheet, prtimeperiod, srm_resources

where object_code = 'timesheet'

and attribute_code = 'prstatus'

and odf_locked_attributes.odf_pk = prtimesheet.prid

and prtimesheet.prtimeperiodid = prtimeperiod.prid

and srm_resources.id = prtimesheet.prresourceid;

query

6)

delete from odf_locked_attributes

where odf_locked_attributes.id

in (nnnnn,mmmmm,ooooo,ppppp)

nnnnn, etc. will come from query 5 above

 

Jerin

[navzjoshi00]

Thanks Jerin, I will check and get back

 

NJ

[Jerin Sebastian]

How did this go?    

[navzjoshi00]

Apologies, I haven't checked on this lately. I will get back with the info.

 

NJ

[Nick Darlington]

Hi NJ,

 

Please can you share the full exception log (it has terminated early without extra code following the final 'caused by' nested/thrown exception)?  If it actually does appear to end there, please check the prior/subsequent log entries to see if they are related follow-ons to this problem, and share that too.

 

Also for (some) stack traces to be checked further, it is important to share the exact version of Clarity and any patch level applied; so that we can match the line numbers being reported to the correct code that was being executed.

 

Thanks.

-Nick

[navzjoshi00]

Today, I looked at this discussion -

https://communities.ca.com/message/101955042#101955042

 

In one of the threads in the above discussion, the user who created this thread mentioned this -

"I checked the Purge Financial Tables Job, but I cannot find the project which is causing issues.

I can select out of two projects which are not marked for deletion. I also took a look at the other Purge Jobs, but also there I can not find my project..."

 

Take a look at the first statement he makes -

"I checked the Purge Financial Tables Job, but I cannot find the project which is causing issues."

 

Taking this statement into account, I checked the investments appearing in the "Purge Financial Tables" job, and it completed fine.

 

When I again checked the project in the Investment Select list for the ""Purge Financial Tables" job", I found that project again

 

Does that mean that the financials have not been purged successfully ?

 

NJ

[navzjoshi00]

I think I know the issue now.

 

There are a few tasks with a custom process instance in error.

 

Is there quick query to identify such process instance for the tasks in a project ?

 

NJ

[Anon Anon]

Hi NJ,

 

did you get your issue resolve?

We are facing similar problem now whereby investments (mainly IDEAs) marked for deletion are not being removed after the "Delete Investment" job run.

 

We do have Completed Process Instance and Team allocation with 0 actual. However, we are not able to see the Idea Financial as there was an error : "ErrorPMA-0021: You do not have sufficient rights to view this idea. "

One point to note, we do not run the "Purge Financial Tables" job. Will it affect?

[navzjoshi00]

Do you have the right to delete the Ideas ?

 

NJ

[navzjoshi00]

Also, if there are any actuals then the investment can only be deactivated

 

NJ

[Anon Anon]

Yes. Having Admin rights.

[Anon Anon]

0 Actual. Process was aborted half way and marked for deletion.

[Anon Anon]

Worked with CA Support on this and confirmed defect found..

Defect # CLRT-78057 found in v13.3 patch 6. (happening in IDEA only)

You can replicate this issue in the following steps:

1 - Go to an idea with an instance process running

2 - Set status idea = 'Inactive'

3 - Mark it for delete it

Actual Result = You can mark it for be deleted. Although the delete investment job is able to detect it and not let it be deleted, the idea should not have allow the deletion in the first place.

Expected = You should not be able for do it if have process instance running

[Nick Darlington]

Although a potential defect was logged, there are a few things to bear in mind, and I am sharing this for awareness and your feedback:

 

1) The documentation** only says investments need to be marked inactive (for NPIOs, and a few other verifications for Projects) in order to be marked for deletion

2) The job itself when it runs takes care of performing further checks to see if an item can be deleted or not (situations can change between marking and the job running)

3) All NPIOs behave this way, not just Ideas

4) It's actually therefore only Projects that behave inconsistently by performing this check and preventing the action, contrary to the documentation

 

As a result, the corrective action likely to be taken in future is that Projects will no longer check for active process instances at the time the project is marked for deletion, making all investment types act consistently and inline with the documented behaviour.

 

 

 

** References:

 

Close, Deactivate, Delete, and Cancel Projects - CA Clarity™ PPM - 14.2 (On Premise) - CA Wiki

Delete a Project

Use these steps to delete a project:

1. Verify that the project contains no posted transactions.
2. Verify that the project contains no time entries with a value greater than zero.
3. Financially close the project.
4. Deactivate the project.
5. Mark the project for deletion.
6. If necessary, cancel the deletion process before running the job.
7. Schedule the Delete Projects job to run. Your CA Clarity™ PPM administrator schedules and runs the job on a regular basis.

 

https://wiki.ca.com/display/CCPPMOP142/How%20to%20Manage%20Ideas#HowtoManageIdeas-DeleteanIdea

Delete an Idea

You can delete an idea, including an idea that has been converted to an investment. Deleting a converted idea removes the link to the investment. Deleting an idea does not delete the converted investment.

Follow these steps:

1. On the Ideas list page, select the check box next to an idea.
2. Set the Active field to No and click Save. You cannot delete an active idea.
3. Click Mark for Deletion.
   The idea is deleted and no longer appears in the list.

[navzjoshi00]

Thanks for sharing this useful info, Nick

 

Regards

NJ

[Lowell Wetzel]

What is the query for ​deleteODF?

I have our Delete Investment job running and it appears to be permanently running.

How do we check the status of the Delete Investments job?

Thanks,
Lowell