Creating SQL queries, escalations, and notifications for work records

1. In the Work View application, select Advanced Search > Where Clause and copy one of the code samples to the Current Query area.
2. Name the query and make it public.
3. Save the record.

Sample queries

SQL WHERE clause to list Priority 1 work view records that are open and do not have log data entered in the previous sixty minutes

((((exists (select recordkey from worklog where workview.recordkey = worklog.recordkey and workview.class=worklog.class and (worklog.createdate < sysdate - 60/1440)) or not exists (select recordkey from worklog where workview.recordkey = worklog.recordkey and workview.class=worklog.class )and ((status != 'CLOSE')))) and ((status != 'RESOLVED'))) and ((status != 'COMP'))) and ((priority = 1))

SQL WHERE clause to list Priority 2 work view records that are open and do not have log data entered in the previous four hours

((((exists (select recordkey from worklog where workview.recordkey = worklog.recordkey and workview.class=worklog.class and (worklog.createdate < sysdate - 240/1440)) or not exists (select recordkey from worklog where workview.recordkey = worklog.recordkey and workview.class=worklog.class )and ((status != 'CLOSE')))) and ((status != 'RESOLVED'))) and ((status != 'COMP'))) and ((priority = 2))

How to reset user's Start Centers

First of all you have to identify the set of start centers to be reset. In this example I want to reset all the start centers of the user's belonging to MYGROUP security group. Run this select and check the results.

SELECT * FROM scconfig WHERE groupname='MYGROUP';

Now replace the star (*) character with scconfigid and you will have something like this.

SELECT scconfigid FROM scconfig WHERE groupname='MYGROUP'

Now replace [SCLIST] in the following SQL delete statements with the previous select and execute them.

DELETE FROM rsconfig WHERE layoutid IN (SELECT layoutid FROM layout WHERE scconfigid IN ([SCLIST]));

DELETE FROM faconfig WHERE layoutid IN (SELECT layoutid FROM layout WHERE scconfigid IN ([SCLIST]));

DELETE FROM inbxconfig WHERE layoutid IN (SELECT layoutid FROM layout WHERE scconfigid IN ([SCLIST]));

DELETE FROM kpilconfig WHERE layoutid IN (SELECT layoutid FROM layout WHERE scconfigid IN ([SCLIST]));

DELETE FROM kpigconfig WHERE layoutid IN (SELECT layoutid FROM layout WHERE scconfigid IN ([SCLIST]));

DELETE FROM actionscfg WHERE layoutid IN (SELECT layoutid FROM layout WHERE scconfigid IN ([SCLIST]));

If everything is fine you can run the last two deletes.

DELETE FROM layout WHERE layoutid IN (SELECT layoutid FROM layout WHERE scconfigid IN ([SCLIST]));

DELETE FROM scconfig WHERE scconfigid IN ([SCLIST]);