Overview

Your organization is not using currently the trigger of a queue within ResourceOne (R1), and this information is taking up a lot of storage, so you want to delete all the items that are listed in that queue.

Information

Deleting records in a specific queue involves database modification scripts that might damage your database and, in consequence, your data and the product.

If you want to proceed with the queries mentioned in the following section, please make sure you back up your entire database, and more precisely, the following tables which can be found in the BANK DB:

- Queue_Activities
- Activity_Contacts
- Activity_Detail
- Notes
- Activity

Once you execute the queries listed below, the data will be lost and will be unrecoverable. You can use the method described in the Backing Up Tables for Security KB Article for this purpose.

Input information

To get these records eliminated from the database, you must first reunite the following information:

• The name of the queue where all these records exist. (it will be called <NOTQ>)
  If you want to eliminate records only from the "DDA Queue" queue and the "Investments" queue, then provide both names.

Query to execute

Now that we have what we need, we can start our own research. To do so, let's check all the records that belong to the aforementioned queues:

select * 
from Queue 
where Queue_Name like '<NOTQ>';

We will use the Card Services queue (as Name of the Queue or <NOTQ>) for this example, so it would be in our case:

select * 
from Queue 
where Queue_Name like '%Card Services%'

It should show all the records in the queue(s). 

The following tables are involved with this Queue_ID, so we will have to back them up (as explained in the first part of this section).

• Queue_Activities
• Activity_Contacts
• Activity_Detail
• Notes
• Activity

To do so, we recommend using the following scripts, modifying the DATE tag with the current date of the backup.

select * into Queue_Activities_<NOTQ>_BK_<DATE>
from Queue_Activities where Queue_ID = '<Resultant Queue_ID>'

select * into Activity_Contacts_<NOTQ>_Queue_BK_<DATE>
from Activity_Contacts where Activity_ID in (select Activity_ID from Queue_Activities_<NOTQ>_BK_<DATE>)

select * into Activity_Detail_<NOTQ>_Queue_BK_<DATE>
from Activity_Detail where Activity_ID in (select Activity_ID from Queue_Activities_<NOTQ>_BK_<DATE>)

select * into Notes_<NOTQ>_Queue_BK_<DATE>
from Notes where Activity_ID in (select Activity_ID from Queue_Activities_<NOTQ>_BK_<DATE>)

select * into Activity_<NOTQ>_Queue_BK_<DATE>
from Activity where Activity_ID in (select Activity_ID from Queue_Activities_<NOTQ>_BK_<DATE>)

In our example, it would be:

select * into Queue_Activities_Card_Svcs_BK_20211203
from Queue_Activities where Queue_ID = 'EB56B7F5-4471-49AE-9760-DAFF5A160415'

select * into Activity_Contacts_Card_Svcs_Queue_BK_20211203
from Activity_Contacts where Activity_ID in (select Activity_ID from Queue_Activities_Card_Svcs_BK_20211203)

select * into Activity_Detail_Card_Svcs_Queue_BK_20211203
from Activity_Detail where Activity_ID in (select Activity_ID from Queue_Activities_Card_Svcs_BK_20211203)

select * into Notes_Card_Svcs_Queue_BK_20211203
from Notes where Activity_ID in (select Activity_ID from Queue_Activities_Card_Svcs_BK_20211203)

select * into Activity_Card_Svcs_Queue_BK_20211203
from Activity where Activity_ID in (select Activity_ID from Queue_Activities_Card_Svcs_BK_20211203)

That is enough to back up the tables that would be affected.

Now you can modify a bit the last query by adding the DELETE sentence, as shown in the following example:

Delete from Queue_Activities where Queue_ID = 'Resultant Queue_ID'

Delete from Activity_Contacts where Activity_ID in (select Activity_ID from Queue_Activities_<NOTQ>_BK_<DATE>)

Delete from Activity_Detail where Activity_ID in (select Activity_ID from Queue_Activities_<NOTQ>_BK_<DATE>)

Delete from Notes where Activity_ID in (select Activity_ID from Queue_Activities_<NOTQ>_BK_<DATE>)

Delete from Activity where Activity_ID in (select Activity_ID from Queue_Activities_<NOTQ>_BK_<DATE>)

In our example, it would be:

Delete from Queue_Activities where Queue_ID = 'EB56B7F5-4471-49AE-9760-DAFF5A160415'

Delete from Activity_Contacts where Activity_ID in (select Activity_ID from Queue_Activities_Card_Svcs_BK_20211203)

Delete from Activity_Detail where Activity_ID in (select Activity_ID from Queue_Activities_Card_Svcs_BK_20211203)

Delete from Notes where Activity_ID in (select Activity_ID from Queue_Activities_Card_Svcs_BK_20211203)

Delete from Activity where Activity_ID in (select Activity_ID from Queue_Activities_Card_Svcs_BK_20211203)

Based on the previous explanation, adapt this query to your needs to delete the list of records you want to delete.

Contacting Support

If you want support to execute this backend modification on your behalf, please contact the Support staff and provide them with the following information regarding the case:

• The name of the queue(s) where all these records exist.
• Any other relevant information related to the records you want to delete, which can be found in the queue table.

A customer support representative will contact you back with the result of the execution of the aforementioned query.