How to get rid of work items which have not been completed.

Products

• CA Identity Manager
• CA Identity Governance
• CA Identity Portal
• CA Risk Analytics
• CA Secure Cloud SaaS - Arcot A-OK (WebFort)
• CLOUDMINDER ADVANCED AUTHENTICATION
• CA Secure Cloud SaaS - Advanced Authentication
• CA Secure Cloud SaaS - Identity Management
• CA Secure Cloud SaaS - Single Sign On

Issue/Introduction

This question was asked in a context when Client started with a new cleaned Task Persistence Database and did no more want the remaining work items in the lists.

Note: SQL statements are given for MS SQL server.

How to delete work list items which have not been completed and dealing with the referential constraints?

Environment

Release:
Component: IDMGR

Resolution

Possible foreign keys can prevent us to simply delete WP_WORK_ITEM rows. 

The referential path is the following: 

WP_WORK_ITEM <--(fk1_wp_wi_alert) <-- WP_WI_ALERT <-- (fk1_wp_wi_alert_act) <-- WP_WI_ALERT_ACT 

 

To avoid this referential constraint conflict run the 3 following statements in this order: 

 

DELETE child2 FROM [WP_WI_ALERT_ACT] AS child2 

INNER JOIN [WP_WI_ALERT] AS child1 ON child2.WI_ALERT_ID = child1.WI_ALERT_ID AND 

child2.WI_ALERT_DB=child1.WI_ALERT_DB 

INNER JOIN [WP_WORK_ITEM] AS parent ON child1.ACTI_ID = parent.ACTI_ID AND 

child1.ACTI_DB = parent.ACTI_DB AND 

child1.WI_ITERATION=parent.WI_ITERATION 

WHERE parent.COMPLETE_DATE is null; 

 

DELETE child FROM [WP_WI_ALERT] AS child 

INNER JOIN [WP_WORK_ITEM] AS parent ON child.ACTI_ID = parent.ACTI_ID AND 

child.ACTI_DB = parent.ACTI_DB AND 

child.WI_ITERATION=parent.WI_ITERATION 

WHERE parent.COMPLETE_DATE is null; 

 

DELETE FROM [WP_WORK_ITEM] WHERE COMPLETE_DATE is null;