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
