UCCE to PCCE Migration Validation SQL Queries

Note - Errors below taken from the ICM Tomcat Error logs. the GUI was not displaying the errors correctly for me (due to a Java error).

You need to have all 9 ECC Variables configured (no need to enabled them all!).

SELECT COUNT(*) FROM Expanded_Call_Variable WHERE 
EnterpriseName='user.microapp.app_media_lib' OR 
EnterpriseName='user.microapp.error_code' OR 
EnterpriseName='user.microapp.FromExtVXML' OR 
EnterpriseName='user.microapp.input_type'OR 
EnterpriseName='user.microapp.metadata'OR 
EnterpriseName='user.microapp.play_data'OR 
EnterpriseName='user.microapp.sys_media_lib'OR 
EnterpriseName='user.microapp.ToExtVXML' OR 
EnterpriseName='user.microapp.UseVXMLParams'

Each CVP Dialed Number needs to be configured on both CVP Server (PIMS) - this SQL checks for this

SELECT COUNT (*) FROM Dialed_Number INNER JOIN Routing_Client ON Dialed_Number.RoutingClientID = Routing_Client.RoutingClientID WHERE ClientType = 13 AND Dialed_Number.Deleted = 'N' GROUP BY DialedNumberString, LogicalControllerID HAVING (COUNT(DialedNumberString ) != 2)

SQL: SELECT COUNT(1) FROM Enterprise_Route_Member

SELECT COUNT(1) FROM Enterprise_Route

• Skillgroup Route Names need to match the Skill group Name - Bulked exported and re-imported to correct
• Correlation ID needs to be 1001 - 9999
• All Dialed Numbers need to be associated with the Customer instance
• Remove Any Enterprise Skillgroups & Services & Device Targets (need to removed from Scripts first)
• you should have the 3 “VXML” VRU Scripts names as per PCCE - rename if necessary, i.e. VXML_Server, VXML_Server_Interruptible, VXML_Server_Noninterruptible
• Setup the MR PG as per a PCCE install

Note: the below SQL has commented out some valid SQL query for CCE 12 - but so you can run this on a CCE 11 machines - I have commented it out (as that is what I am tested against) - so remove the SQL comments to run on CCE 12.

SELECT CapacityCheckName = 'MAXIMUM_AGENTS', MaxCapacity= '24000', CurrentCount = COUNT(*) FROM Agent WHERE Deleted = 'N' UNION
SELECT CapacityCheckName = 'MAXIMUM_AGENTS_WITH_TRACE', MaxCapacity= '100', CurrentCount = COUNT(*) FROM Agent WHERE AgentStateTrace = 'Y' AND Deleted = 'N' UNION
SELECT CapacityCheckName = 'MAXIMUM_AGENTS_WITH_TRACE_AGENTSTATETRACE', MaxCapacity= '100', CurrentCount = COUNT(*) FROM Agent WHERE AgentStateTrace = 'Y' AND Deleted = 'N' UNION
SELECT CapacityCheckName = 'MAXIMUM_AGENT_DESK_SETTINGS' , MaxCapacity= '4000', CurrentCount = COUNT(*) FROM Agent_Desk_Settings UNION
SELECT CapacityCheckName = 'MAXIMUM_SUPERVISORS' , MaxCapacity= '2400', CurrentCount = COUNT(*) FROM Agent WHERE SupervisorAgent = 'Y' AND Deleted = 'N' UNION
SELECT CapacityCheckName = 'MAXIMUM_AGENT_TEAMS' , MaxCapacity= '2400', CurrentCount = COUNT(*) FROM Agent_Team  UNION
SELECT CapacityCheckName = 'MAXIMUM_ADMINISTRATORS' , MaxCapacity= '100', CurrentCount = COUNT(*) FROM User_Group WHERE UserGroupID != 1 AND UserGroupID NOT IN (SELECT UserGroupID FROM User_Supervisor_Map)  UNION
SELECT CapacityCheckName = 'MAXIMUM_ROLES' , MaxCapacity= '30', CurrentCount = COUNT(*) FROM Feature_Control_Set UNION
SELECT CapacityCheckName = 'MAXIMUM_DEPARTMENTS' , MaxCapacity= '20', CurrentCount = COUNT(*) FROM Department WHERE Deleted = 'N' UNION
SELECT CapacityCheckName = 'MAXIMUM_SKILL_GROUPS' , MaxCapacity= '16000', COUNT(*) FROM Skill_Group WHERE PeripheralID IN (SELECT PeripheralID FROM Peripheral WHERE ClientType = 30) AND Deleted = 'N' UNION
SELECT CapacityCheckName = 'MAXIMUM_MEDIA_ROUTING_DOMAINS' , MaxCapacity= '20', COUNT(*) FROM Media_Routing_Domain UNION
SELECT CapacityCheckName = 'MAXIMUM_PERSISTENT_EXPANDED_CALL_VARIABLES' , MaxCapacity= '5', COUNT(*) FROM Expanded_Call_Variable WHERE Deleted = 'N' AND Enabled = 'Y' AND Persistent = 'Y' AND ECCArray = 'N' UNION
SELECT CapacityCheckName = 'MAXIMUM_PERSISTENT_EXPANDED_CALL_ARRAYS' , MaxCapacity= '', COUNT(*) FROM Expanded_Call_Variable WHERE Deleted = 'N' AND Enabled = 'Y' AND Persistent = 'Y' AND ECCArray = 'Y' UNION
SELECT CapacityCheckName = 'MAXIMUM_BUCKET_INTERVALS' , MaxCapacity= '4000', COUNT(*) FROM Bucket_Intervals WHERE Deleted = 'N' UNION
SELECT CapacityCheckName = 'MAXIMUM_CALL_TYPES' , MaxCapacity= '8000', COUNT(*) FROM Call_Type WHERE Deleted = 'N' UNION
SELECT CapacityCheckName = 'MAXIMUM_NETWORK_VRU_SCRIPTS' , MaxCapacity= '4000', COUNT(*) FROM Network_Vru_Script UNION
SELECT CapacityCheckName = 'MAXIMUM_REASON_CODES' , MaxCapacity= '3821', COUNT(*) FROM Reason_Code WHERE Deleted = 'N' UNION
SELECT CapacityCheckName = 'MAXIMUM_TEAM_SPECIFIC_NOTREADY_REASON_CODES' , MaxCapacity= '1000', COUNT(*) FROM Reason_Code WHERE Deleted = 'N' /* AND ReasonType = 1 AND IsGlobal = 'N'  */ UNION
SELECT CapacityCheckName = 'MAXIMUM_GLOBAL_NOTREADY_REASON_CODES' , MaxCapacity= '100', COUNT(*) FROM Reason_Code WHERE Deleted = 'N' /* AND ReasonType = 1 AND IsGlobal = 'Y' */ UNION
SELECT CapacityCheckName = 'MAXIMUM_TEAM_SPECIFIC_SIGNOUT_REASON_CODES' , MaxCapacity= '1000', COUNT(*) FROM Reason_Code WHERE Deleted = 'N' /* AND ReasonType = 2 AND IsGlobal = 'N' */ UNION
SELECT CapacityCheckName = 'MAXIMUM_GLOBAL_SIGNOUT_REASON_CODES' , MaxCapacity= '100', COUNT(*) FROM Reason_Code WHERE Deleted = 'N' /* AND ReasonType = 2 AND IsGlobal = 'Y' */ UNION
SELECT CapacityCheckName = 'MAXIMUM_TEAM_SPECIFIC_WRAPUP_REASON_CODES' , MaxCapacity= '1500', COUNT(*) FROM Reason_Code WHERE Deleted = 'N' /*  AND ReasonType = 3 AND IsGlobal = 'N' */ UNION
SELECT CapacityCheckName = 'MAXIMUM_GLOBAL_WRAPUP_REASON_CODES' , MaxCapacity= '100', COUNT(*) FROM Reason_Code WHERE Deleted = 'N' /* AND ReasonType = 3 AND IsGlobal = 'Y' */ UNION
SELECT CapacityCheckName = 'MAXIMUM_BULK_JOBS' , MaxCapacity= '200', COUNT(*) FROM Bulk_Job UNION
SELECT CapacityCheckName = 'MAXIMUM_APPLICATION_GATEWAYS' , MaxCapacity= '20', COUNT(*) FROM Application_Gateway WHERE ApplicationGatewayType = 0 --UNION
--SELECT CapacityCheckName = 'MAXIMUM_BUSINESS_HOURS' , MaxCapacity= '1000', COUNT(*) FROM Business_Hours 

--CustomCapacityRule name="MAXIMUM_AGENTS_IN_TEAM" capacity="50" className="AgentsInTeamRule"/> 
SELECT AgentTeamID, COUNT(*) AS NumberOfAgentsInTeam FROM Agent_Team_Member GROUP BY AgentTeamID
 
--CapacityAttributesRule name="MAXIMUM_AGENTS_FOR_TEAM" capacity="50" beanType="AgentTeam" attributeName="agents"/>
SELECT SkillTargetID, COUNT(*) AS NumberOfTeamsPerAgent FROM Agent_Team_Member GROUP BY SkillTargetID
 
--CapacityAttributesRule name="MAXIMUM_AGENT_ATTRIBUTES" capacity="50" beanType="Agent" attributeName="attributes"/>
SELECT SkillTargetID, COUNT(*) AS NumberOfAttributes FROM Agent_Attribute GROUP BY SkillTargetID
 
--CapacityAttributesRule name="MAXIMUM_TEAMS_FOR_SUPERVISOR" capacity="20" beanType="Agent" attributeName="supervisorTeams"/> CustomCapacityRule name="MAXIMUM_TEAMS_PER_SUPERVISOR" capacity="20" className="SupervisorsPerTeamFromTeamRule"/>
SELECT SupervisorSkillTargetID, COUNT(*) AS NumberOfTeamsUnderSupervisor FROM Agent_Team_Supervisor GROUP BY SupervisorSkillTargetID
 
--CapacityAttributesRule name="MAXIMUM_SUPERVISORS_FOR_TEAM" capacity="20" beanType="AgentTeam" attributeName="supervisors"/> CustomCapacityRule name="MAXIMUM_SUPERVISORS_PER_TEAM" capacity="20" className="SupervisorsPerTeamFromAgentRule"/>
SELECT AgentTeamID, COUNT(*) AS NumberOfSupervisorsPerTeam FROM Agent_Team_Supervisor GROUP BY AgentTeamID 
 
--CustomCapacityRule name="MAXIMUM_DEPARTMENTS_PER_ADMINISTRATOR" capacity="10" className="DepartmentsPerAdministrator"/>
SELECT UserGroupID, COUNT(*) AS NumberOfDepartmentsPerAdministrator FROM Department_Member GROUP BY UserGroupID 
 
--CapacityAttributesRule name="MAXIMUM_DEPARTMENTS_PER_ADMINISTRATOR_IN_ADMINISTRATOR" capacity="10" beanType="Administrator" attributeName="departments"/>
SELECT DepartmentID, COUNT(*) AS NumberOfAdministratorsPerDepartment FROM Department_Member GROUP BY DepartmentID
 
--CapacityCountCheckerRule name="MAXIMUM_SKILL_GROUPS_PER_PERIPHERAL_SET" capacity="4000" beanType="skillgroup.SkillGroup" criteriaType="SkillGroupAndPqPerPeripheralSetCriteria"/>
SELECT PeripheralID, COUNT(*) AS NumberOfSkillGroupsPerPeripehral FROM Skill_Group WHERE PeripheralID IN (SELECT PeripheralID FROM Peripheral WHERE ClientType = 30) AND Deleted = 'N' GROUP BY PeripheralID
 
--CapacityCountCheckerRule name="MAXIMUM_DIALED_NUMBERS_EXTERNAL_PER_PERIPHERAL_SET" capacity="3500" beanType="DialedNumber" criteriaType="DialedNumberExternalPerPeripheralSetCriteria"/>
SELECT RoutingClientID, COUNT(*) AS NumberOfDialedNumbersPerCVPRoutingClient FROM Dialed_Number WHERE Deleted = 'N' AND RoutingClientID IN (SELECT RoutingClientID FROM Routing_Client WHERE ClientType = 13) /* AND PCSPattern IS NULL */GROUP BY RoutingClientID 
 
--CapacityCountCheckerRule name="MAXIMUM_DIALED_NUMBERS_INTERNAL_PER_PERIPHERAL_SET" capacity="2000" beanType="DialedNumber" criteriaType="DialedNumberInternalPerPeripheralSetCriteria"/>
SELECT RoutingClientID, COUNT(*) AS NumberOfDialedNumbersPerUCMRoutingClient FROM Dialed_Number WHERE Deleted = 'N' AND RoutingClientID IN (SELECT RoutingClientID FROM Routing_Client WHERE ClientType = 30) GROUP BY RoutingClientID
 
--CapacityCountCheckerRule name="MAXIMUM_DIALED_NUMBER_MEDIA_ROUTING_TYPE_PER_PERIPHERAL_SET" capacity="1000" beanType="DialedNumber" criteriaType="DialedNumberMediaRoutingTypePerPeripheralSetCriteria"/>
SELECT RoutingClientID, COUNT(*) AS NumberOfDialedNumbersPerMRRoutingClient FROM Dialed_Number WHERE Deleted = 'N' AND RoutingClientID IN (SELECT RoutingClientID FROM Routing_Client WHERE ClientType = 47) GROUP BY RoutingClientID
 
--CapacityCountCheckerRule name="MAXIMUM_DIALED_NUMBERS_PCS_PER_PERIPHERAL_SET" capacity="500" beanType="DialedNumber" criteriaType="DialedNumberPCSPerPeripheralSetCriteria"/>
SELECT RoutingClientID, COUNT(*) AS NumberOfPCSDialedNumbersPerCVPRoutingClient FROM Dialed_Number WHERE Deleted = 'N' AND RoutingClientID IN (SELECT RoutingClientID FROM Routing_Client WHERE ClientType = 13) /* AND PCSPattern IS NOT NULL */ GROUP BY RoutingClientID 

• Enterprise Skill Groups (ESG) cannot be deleted if referenced in a script.
• So we need to delete all scripts which reference an ESG.
• We cannot delete any script which happens to be referenced in another script (via a GOTO Script) or a scripts which is associated with a CallType - so these scripts need to be deleted first and any Calltype <> Scripts associated needs to be removed.

• Step 1 Delete All scripts which have a GOTO node to an ESG Scripts - Worksheet [1. Referenced ESG GOTO Scripts]
• Step 2 Delete all ESG Scripts - Worksheet [2. Script Referencing ESG]
• Step 3 Delete all ESG - should be very simple once above is done

SQL
ALL Scripts (including OLD scripts) which reference an Enterprise Skillgroup
 
SELECT 
 MasterScriptName = MS.EnterpriseName
,ESGName = ESG.EnterpriseName
 
 
  FROM [Ref_Script_Cross_Reference] SCR
  LEFT JOIN Ref_Script S ON S.ScriptID = SCR.ScriptID
  LEFT JOIN Ref_Master_Script MS ON MS.MasterScriptID = S.MasterScriptID
  LEFT JOIN Enterprise_Skill_Group ESG ON ESG.EnterpriseSkillGroupID = SCR.ForeignKey
 
  WHERE SCR.TargetType = '9' 
 
  GROUP BY MS.EnterpriseName, ESG.EnterpriseName
  ORDER BY MS.EnterpriseName, ESG.EnterpriseName

Get the list of Scripts from above SQL query - put into EXCEL and remove duplicates and use this List to create the next SQL query.

SELECT
OriginatingScript = MS1.EnterpriseName
,SCR.[ScriptID]
,S.MasterScriptID
,SCR.[LocalID]
,SCR.[TargetType]
,SCR.[ForeignKey]
,GotoScript = MS2.EnterpriseName
 
FROM [vhi_awdb].[dbo].[Script_Cross_Reference] SCR
LEFT JOIN Script S ON S.ScriptID = SCR.ScriptID
LEFT JOIN Master_Script MS1 ON MS1.MasterScriptID = S.MasterScriptID
LEFT JOIN Master_Script MS2 ON MS2.MasterScriptID = SCR.ForeignKey
WHERE SCR.TargetType = '20'
AND MS2.EnterpriseName IN 
(
'Script1',
'Script2',
)
 
ORDER BY MS2.EnterpriseName