Wednesday, September 18, 2013

List entities of an ICM script



USE ccc_awdb
SELECT scr.*,
CASE(scr.TargetType)
WHEN 1 THEN 'Service'
WHEN 2 THEN 'Skill_Group'
WHEN 3 THEN 'Agent'
WHEN 4 THEN 'Translation_Route'
WHEN 7 THEN 'Call_Type'
WHEN 8 THEN 'Enterprise_Service'
WHEN 14 THEN 'Peripheral'
WHEN 16 THEN 'Trunk_Group'
WHEN 17 THEN 'Route'
WHEN 20 THEN 'Master_Script'
WHEN 33 THEN 'Network_Vru_Script'
WHEN 37 THEN 'Expanded_Call_Variable'

END AS Type,
CASE(scr.TargetType)
WHEN 1 THEN (SELECT EnterpriseName FROM Service WHERE SkillTargetID = scr.ForeignKey)
WHEN 2 THEN (SELECT EnterpriseName FROM Skill_Group WHERE SkillTargetID = scr.ForeignKey)
WHEN 3 THEN (SELECT EnterpriseName FROM Agent WHERE SkillTargetID = scr.ForeignKey)
WHEN 4 THEN (SELECT EnterpriseName FROM Translation_Route WHERE SkillTargetID = scr.ForeignKey)
WHEN 7 THEN (SELECT EnterpriseName FROM Call_Type WHERE CallTypeID = scr.ForeignKey)
WHEN 8 THEN (SELECT EnterpriseName FROM Enterprise_Service WHERE EnterpriseServiceID = scr.ForeignKey)
WHEN 14 THEN (SELECT EnterpriseName FROM Peripheral WHERE PeripheralID = scr.ForeignKey)
WHEN 16 THEN (SELECT EnterpriseName FROM Trunk_Group WHERE TrunkGroupID = scr.ForeignKey)
WHEN 17 THEN (SELECT EnterpriseName FROM Route WHERE RouteID = scr.ForeignKey)
WHEN 20 THEN (SELECT EnterpriseName FROM Master_Script WHERE MasterScriptID = scr.ForeignKey)
WHEN 33 THEN (SELECT EnterpriseName FROM Network_Vru_Script WHERE NetworkVruScriptID = scr.ForeignKey)
WHEN 37 THEN (SELECT EnterpriseName FROM Expanded_Call_Variable WHERE ExpandedCallVariableID = scr.ForeignKey)
END AS EnterpriseName
FROM Master_Script ms
INNER JOIN Script s ON s.MasterScriptID = ms.MasterScriptID AND s.Version = ms.CurrentVersion
INNER JOIN Script_Cross_Reference scr ON scr.ScriptID = s.ScriptID
WHERE ms.EnterpriseName = 'NM_AkviziceEL'