CCMP "Awaiting Deletion"

[Jeremy Janusz]

We are using Contact Center Management Portal 7.5, and are having an issue where we deleted some agents, but they are still showing in CCMP as "Awaiting Deletion", for both the agent and for the person.  This is a problem because we need to re-use the person login ID, and it will not allow us (even if we try to create the person and agent from ICM Configuration Manager instead).  Neither the person nor the agent appears in ICM anywhere (in ICM Configuration Manager, deleted items, or even in the awdb).  In the CCMP Portal DB, the agent shows up in the TB_DIM_AGENT table, and the person shows up in the TB_DIM_PERSON table.  Can I delete the persons and agents that are "Awaiting Deletion" from these tables, or are there tie ins that would cause problems?  Thank you for any assistance you can give me.

PS:  I have a TAC case open for this issue, but they are taking an extremely long time to come up with anything.

[jonathan.charlton · Accepted solution]

Hi Jeremy,

Usually when items are stuck in a Pending status within CCMP the route cause is connectivity with the ICM. Are you sure that the connection to ICM is active and are you able to create new item or perform updates on existing items?

The safest way to resolve this issue will be to mark the item back into Ready state then the next time the Data Import Server runs it will identify that the item no longer existing on the ICM and it will be flagged as deleted within CCMP. This means that the items and all of its audit information remains intact.

To perform this proceedure locate the AGENT_URN and PERSON_URN of the Agent and Person in question from VW_DIM_AGENT and VW_DIM_PERSON. Once located execute the following queries:

UPDATE TB_DIM_ITEM SET STATUS = 'R', DELETED = 0 WHERE ITEM_URN =
UPDATE TB_DIM_MEMBER SET STATUS = 'R', DELETED = 0 WHERE PARENT_ITEM_URN = AND STATUS = 'P'
UPDATE TB_DIM_MEMBER SET STATUS = 'R', DELETED = 0 WHERE CHILD_ITEM_URN = AND STATUS = 'P'

UPDATE TB_DIM_ITEM SET STATUS = 'R', DELETED = 0 WHERE ITEM_URN =
UPDATE TB_DIM_MEMBER SET STATUS = 'R', DELETED = 0 WHERE PARENT_ITEM_URN = AND STATUS = 'P'
UPDATE TB_DIM_MEMBER SET STATUS = 'R', DELETED = 0 WHERE CHILD_ITEM_URN = AND STATUS = 'P'

It is possible to purge the items completely from the database but this is a little more involved because of the referencial integrity between the various tables. The following script may be used to remove items completely from the CCMP database. Please ensure that all CCMP Services are stopped when the script is being executed and are re-started once the purge command has been completed.

To delete Agents...

DECLARE @ident AS INT
SET @ident =

DELETE FROM TB_FCT_AUDIT WHERE ITEM_URN = @ident OR RELATED_ITEM_URN = @ident
DELETE FROM TB_DIM_MEMBER_PKEY_MAP WHERE MEMBER_URN
IN (SELECT MEMBER_URN FROM TB_DIM_MEMBER WHERE PARENT_ITEM_URN = @ident OR CHILD_ITEM_URN = @ident)
DELETE FROM TB_DIM_AGENT_AGENT_DESKTOP_MEMBER WHERE AGENT_AGENT_DESKTOP_MEMBER_URN
IN (SELECT MEMBER_URN FROM TB_DIM_MEMBER WHERE PARENT_ITEM_URN = @ident OR CHILD_ITEM_URN = @ident)
DELETE FROM TB_DIM_AGENT_SKILLGROUP_MEMBER WHERE AGENT_SKILLGROUP_MEMBER_URN
IN (SELECT MEMBER_URN FROM TB_DIM_MEMBER WHERE PARENT_ITEM_URN = @ident OR CHILD_ITEM_URN = @ident)
DELETE FROM TB_DIM_AGENT_PERSON_MEMBER WHERE AGENT_PERSON_MEMBER_URN
IN (SELECT MEMBER_URN FROM TB_DIM_MEMBER WHERE PARENT_ITEM_URN = @ident OR CHILD_ITEM_URN = @ident)
DELETE FROM TB_DIM_AGENT_PERIPHERAL_MEMBER WHERE AGENT_PERIPHERAL_MEMBER_URN
IN (SELECT MEMBER_URN FROM TB_DIM_MEMBER WHERE PARENT_ITEM_URN = @ident OR CHILD_ITEM_URN = @ident)
DELETE FROM TB_DIM_AGENT_AGENT_TEAM_MEMBER WHERE AGENT_AGENT_TEAM_MEMBER_URN
IN (SELECT MEMBER_URN FROM TB_DIM_MEMBER WHERE PARENT_ITEM_URN = @ident OR CHILD_ITEM_URN = @ident)

DELETE FROM TB_DIM_MEMBER WHERE PARENT_ITEM_URN = @ident OR CHILD_ITEM_URN = @ident
DELETE FROM TB_DIM_ITEM_PKEY_MAP WHERE ITEM_URN = @ident
DELETE FROM TB_DIM_AGENT WHERE AGENT_URN = @ident

DELETE FROM TB_DIM_ITEM WHERE ITEM_URN = @ident

To delete Persons.....

DECLARE @ident AS INT
SET @ident =

DELETE FROM TB_FCT_AUDIT WHERE ITEM_URN = @ident OR RELATED_ITEM_URN = @ident
DELETE FROM TB_DIM_MEMBER_PKEY_MAP WHERE MEMBER_URN
IN (SELECT MEMBER_URN FROM TB_DIM_MEMBER WHERE PARENT_ITEM_URN = @ident OR CHILD_ITEM_URN = @ident)
DELETE FROM TB_DIM_AGENT_PERSON_MEMBER WHERE AGENT_PERSON_MEMBER_URN
IN (SELECT MEMBER_URN FROM TB_DIM_MEMBER WHERE PARENT_ITEM_URN = @ident OR CHILD_ITEM_URN = @ident)

DELETE FROM TB_DIM_MEMBER WHERE PARENT_ITEM_URN = @ident OR CHILD_ITEM_URN = @ident
DELETE FROM TB_DIM_ITEM_PKEY_MAP WHERE ITEM_URN = @ident
DELETE FROM TB_DIM_PERSON WHERE PERSON_URN = @ident
DELETE FROM TB_DIM_ITEM WHERE ITEM_URN = @ident

Hope that helps

Jonathan

[Jeremy Janusz]

I finally got a different TAC engineer, and here is their solution (looks like it is basically the same as Jonathan's solution):

After conferring with the development team, it looks like our best course of action is going to be to correct the status of these records in the Portal database.

1.       We have to first identify the items to be changed in the

VW_DIM_AGENT and VW_DIM_PERSON tables.  This will give us the URN values for each of the records.

Ex: select * from

2.       Then we can update TB_DIM_ITEM table for each of the records to

State = 'D', Effective_to = getdate(), and Deleted = 1.  This will remove them from the UI like any other deleted object.

EX: Update TB_DIM_ITEM set State='D', Effective_to=getdate(), Deleted=1 where UCN=XXXX