+1Hi,
I’d like to implement an atomatic escalation (or popup or email or whatever ...) when we have already 5 or more “Emergency” service orders (call type = “Emergency”) booked for the same equipment (serial number) in last 30 days.
Do you know how I could do that ?
Thanks in advance.
Best regards
Padcal
Best answer by Phil Seifert
Hi Pascal,
While I am not going to do all the development for you, perhaps the below can be used as a guideline for what you want to do:
select item_id, serial_no, callt_id, sum(list1)
from (
select item_id, serial_no, callt_id, count(item_id) list1
from service_call with(nolock)
where callt_id = 'Support'
group by item_id, serial_no, callt_id
union all
select item_id, serial_no, callt_id, count(item_id) list1
from c_service_call with(nolock)
where callt_id = 'Support'
group by item_id, serial_no, callt_id
) FinalList
group by item_id, serial_no, callt_id
having sum(list1) > 5
The trick is to do a union all join between two queries; one for active and one for closed tickets and then sum the respective counts to qualify if over the threshold.
I did not do anything to check specific date ranges or other criteria. Please note, I have not tried this within a process flow itself but just pure SQL and it returned the expected results I wanted for my criteria.
There may be and probably are more elegant ways to do this. If this is not sufficient, I recommend you ask for consulting services to help you develop exactly what you need.
+24This is for Alliance and not IFS Cloud or other IFS products?
I think your question is whether when creating a new service order, you can have a popup appear indicating there were multiple occurrences in the last 30 days on this serial number for emergency calls (presumably using priority or some other field).
Alliance does have the possibility to use Process Flows to notify alerts upon certain actions such as this using SQL queries as the source validation or not. Please take a look at this functionality.
Or you could have a scheduled job for the Process flow which will send out emails, etc. scheduled daily.
Please review the document Process Flow User Guide.pdf for more information on creating and using Process Flows.
+1Hi Phil,
Many thanks. I was thinking to Process Flow as well.
My difficulty would be to count calls still in activity (service_order) and ones already closed (c_service_order) for same equipment.
Not sure I can create a procedure instead of a single SQL query in “Process Flow Data Query” in order to build a complex query.
Do you have any experience on that ?
Thanks a lot.
Regards
Pascal
+24Hi Pascal,
While I am not going to do all the development for you, perhaps the below can be used as a guideline for what you want to do:
select item_id, serial_no, callt_id, sum(list1)
from (
select item_id, serial_no, callt_id, count(item_id) list1
from service_call with(nolock)
where callt_id = 'Support'
group by item_id, serial_no, callt_id
union all
select item_id, serial_no, callt_id, count(item_id) list1
from c_service_call with(nolock)
where callt_id = 'Support'
group by item_id, serial_no, callt_id
) FinalList
group by item_id, serial_no, callt_id
having sum(list1) > 5
The trick is to do a union all join between two queries; one for active and one for closed tickets and then sum the respective counts to qualify if over the threshold.
I did not do anything to check specific date ranges or other criteria. Please note, I have not tried this within a process flow itself but just pure SQL and it returned the expected results I wanted for my criteria.
There may be and probably are more elegant ways to do this. If this is not sufficient, I recommend you ask for consulting services to help you develop exactly what you need.
+1Hi Phil,
Many thanks for your help.
You turned me the light !
I know now what to do.
Your quesry was very helpful.
Regards
pascal
+24You are welcome, Pascal.
Please note, the change to the original query as I removed the check for qty of 5 for the 'sub-queries’ relying only on the sum used in the main query but I am sure you get the idea.
Good luck!
Phil
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.