Cisco Support Community
cancel
Showing results for 
Search instead for 
Did you mean: 
Announcements

Welcome to Cisco Support Community. We would love to have your feedback.

For an introduction to the new site, click here. If you'd prefer to explore, try our test area to get started. And see here for current known issues.

EIM 4.3.2 - Query All Activities Due In X Days

We are using the alarm to generate notices when a an actiivty (or case) is due with X amount of Days.

The problem I had was that we would receive an email with just a bunch of case or activity ID's that we would then go and have to look up. I wrote the query below to be called from my alarm workflow, then used the custom object in the work flow to automatically take the results from the query below and put them in an email for me.

Here is a query which will tell you when an Activity due within the amount of days you specify... i.e. 5 days, or -2 days over.


This can be easily modified to use Case due, or due in hours.

The query below is messy, as I quickly found out that using a data query link can only be so many lines long (or characters - not sure)... and yes that is not documented. So, I had to shrink the query as much as I could before I could get it to run.

This query will require an input parameter. When setting up your link usage, leave it as an input parameter, so from your alarms you can pass in how many days you want something open so that you don't have to keep copying this.

Where it says 'XXXX' below, put in your department name as it is in EIM.

select     egpl_casemgmt_case.case_id,
    egpl_casemgmt_case.due_date as Case_DueDate,
    DateDiff(day,getdate(),egpl_casemgmt_case.due_date) as Case_Due_In_Days,
    case when egpl_user.user_name is NULL then 'Not Assigned'
    else egpl_user.user_name
    end as Assigned_TO,
    egpl_casemgmt_activity.activity_id,
    egpl_casemgmt_cpoint_email.email_address as Cust_Email,
    egpl_casemgmt_activity.subject as Email_Subject,
    egpl_casemgmt_activity.due_date as Activity_DueDate,
    DateDiff(day,getdate(),egpl_casemgmt_activity.due_date) as Activity_Due_In_Days,
    egpl_routing_queue.queue_name as Email_Queue,
    egpl_casemgmt_activity.Pinned,
    egpl_casemgmt_activity.Locked,
    case when egpl_casemgmt_activity.activity_status = 1000 then 'New'
     when egpl_casemgmt_activity.activity_status = 3000 then 'Workflow'
     when egpl_casemgmt_activity.activity_status = 4000 then 'Waiting In Queue'
     when egpl_casemgmt_activity.activity_status = 5000 then 'Assigned'
     when egpl_casemgmt_activity.activity_status = 7000 then 'Precompletion'
    end as Activity_Status,
    case when egpl_casemgmt_activity.last_action_reason is NULL then 'Not Assigned Yet'
    else egpl_casemgmt_activity.last_action_reason
    end as Queue_Reason
from egpl_casemgmt_case,
     egpl_casemgmt_activity
    left outer join egpl_user on egpl_casemgmt_activity.assigned_to = egpl_user.user_id
    left outer join egpl_routing_queue on egpl_casemgmt_activity.queue_id = egpl_routing_queue.queue_id
    left outer join egpl_casemgmt_cpoint_email on egpl_casemgmt_activity.contact_point_id = egpl_casemgmt_cpoint_email.contact_point_id,
     egpl_department
where    egpl_casemgmt_activity.case_id = egpl_casemgmt_case.case_id AND
    egpl_department.department_id = egpl_casemgmt_activity.department_id and
    egpl_casemgmt_case.case_status = 0 and
    egpl_department.department_name = 'XXXX' and
    egpl_casemgmt_activity.activity_status <> 9000 and
    DateDiff(day,getdate(),egpl_casemgmt_activity.due_date) <= <%days%>

Thanks

Barry

  • Contact Center
2 REPLIES
Green

Re: EIM 4.3.2 - Query All Activities Due In X Days

Barry,

You are doing a JOIN

left outer join egpl_casemgmt_cpoint_email on egpl_casemgmt_activity.contact_point_id = egpl_casemgmt_cpoint_email.contact_point_id,

that you don't need, in my opinion. You are doing that to get the customer email address

egpl_casemgmt_cpoint_email.email_address as Cust_Email

but I always use contact_point_data from egpl_casemgmt_activity which holds the customer email address.

Regards,

Geoff

Re: EIM 4.3.2 - Query All Activities Due In X Days

Thanks Geoff,

One less join could have meant I could point fit one more column to display data since this query is as large as the Link would allow.

Did you ever manage to find the 4.3.2 schema?

Barry

274
Views
0
Helpful
2
Replies