The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT count(item_key) into activeCount FROM
(select /*+ PARALLEL(wf_item_activity_statuses) */
distinct item_type, item_key from wf_item_activity_statuses
WHERE activity_status in ('ACTIVE','NOTIFIED','WAITING'));
SELECT count(*) into deferredCount FROM wf_item_activity_statuses WHERE activity_status = 'DEFERRED';
SELECT count(item_key) into erroredCount FROM
(select /*+ PARALLEL(wf_item_activity_statuses) */
distinct item_type, item_key from wf_item_activity_statuses
WHERE activity_status = 'ERROR');
SELECT count(*) into suspendedCount FROM wf_item_activity_statuses WHERE activity_status = 'SUSPEND';
update FND_USER_PREFERENCES set preference_value = to_char(activeCount) where preference_name = 'NUM_ACTIVE' and user_name='-WF_DEFAULT-' and module_name='WFManagerWFItemsGraph';
update FND_USER_PREFERENCES set preference_value = to_char(deferredCount) where preference_name = 'NUM_DEFER' and user_name='-WF_DEFAULT-' and module_name='WFManagerWFItemsGraph';
update FND_USER_PREFERENCES set preference_value = to_char(erroredCount) where preference_name = 'NUM_ERROR' and user_name='-WF_DEFAULT-' and module_name='WFManagerWFItemsGraph';
update FND_USER_PREFERENCES set preference_value = to_char(suspendedCount) where preference_name = 'NUM_SUSPEND' and user_name='-WF_DEFAULT-' and module_name='WFManagerWFItemsGraph';
update fnd_user_preferences
set preference_value = to_char(sysdate,'dd/MM/YYYY HH24:MI:SS')
where preference_name = 'LAST_UPDATE_TIME' and
module_name = 'WFManagerWFItemsGraph' and
user_name = '-WF_DEFAULT-';
SELECT /*+ PARALLEL(wf_item_activity_statuses) */
item_type, count(distinct(item_key)) cnt
FROM wf_item_activity_statuses
WHERE activity_status in ('ACTIVE','NOTIFIED','WAITING')
GROUP BY item_type ORDER BY item_type;
update wf_item_types set NUM_ACTIVE = 0;
update wf_item_types set NUM_ACTIVE = l_cnt(i)
where name = l_item_type(i);
update fnd_user_preferences
set preference_value = to_char(sysdate,'dd/MM/YYYY HH24:MI:SS')
where preference_name = 'LAST_UPDATE_TIME' and
module_name = 'WFManagerActiveWFItems' and
user_name = '-WF_DEFAULT-';
SELECT item_type, count(distinct(item_key)) cnt
FROM wf_item_activity_statuses
WHERE activity_status = 'ERROR'
GROUP BY item_type ORDER BY item_type;
update wf_item_types set NUM_ERROR = 0;
update wf_item_types set NUM_ERROR = l_cnt(i)
where name = l_item_type(i);
update fnd_user_preferences
set preference_value = to_char(sysdate,'dd/MM/YYYY HH24:MI:SS')
where preference_name = 'LAST_UPDATE_TIME' and
module_name = 'WFManagerErrorWFItems' and
user_name = '-WF_DEFAULT-';
SELECT item_type, count(distinct(item_key)) cnt
FROM wf_item_activity_statuses
WHERE activity_status = 'DEFERRED'
GROUP BY item_type ORDER BY item_type;
update wf_item_types set NUM_DEFER = 0;
update wf_item_types set NUM_DEFER = l_cnt(i)
where name = l_item_type(i);
update fnd_user_preferences
set preference_value = to_char(sysdate,'dd/MM/YYYY HH24:MI:SS')
where preference_name = 'LAST_UPDATE_TIME' and
module_name = 'WFManagerDeferWFItems' and
user_name = '-WF_DEFAULT-';
SELECT item_type, count(distinct(item_key)) cnt
FROM wf_item_activity_statuses
WHERE activity_status = 'SUSPEND'
GROUP BY item_type ORDER BY item_type;
update wf_item_types set NUM_SUSPEND = 0;
update wf_item_types set NUM_SUSPEND = l_cnt(i)
where name = l_item_type(i);
update fnd_user_preferences
set preference_value = to_char(sysdate,'dd/MM/YYYY HH24:MI:SS')
where preference_name = 'LAST_UPDATE_TIME' and
module_name = 'WFManagerSuspendWFItems' and
user_name = '-WF_DEFAULT-';
SELECT /*+ PARALLEL(wi) */ wi.item_type, count(wi.item_key) cnt
FROM wf_items wi
WHERE wi.end_date IS NOT NULL
GROUP BY wi.item_type
order by wi.item_type;
update wf_item_types set NUM_COMPLETE = 0, NUM_PURGEABLE = 0;
update wf_item_types set NUM_COMPLETE = l_cnt(i),
NUM_PURGEABLE = l_purgeCnt(i)
where name = l_item_type(i);
update fnd_user_preferences
set preference_value = to_char(sysdate,'dd/MM/YYYY HH24:MI:SS')
where preference_name = 'LAST_UPDATE_TIME' and
module_name = 'WFManagerCompleteWFItems' and
user_name = '-WF_DEFAULT-';
update FND_USER_PREFERENCES set preference_value = to_char(readyCount) where preference_name = 'NUM_READY' and user_name='-WF_DEFAULT-' and module_name='WFManagerWFAgentsGraph';
update FND_USER_PREFERENCES set preference_value = to_char(waitingCount) where preference_name = 'NUM_WAITING' and user_name='-WF_DEFAULT-' and module_name='WFManagerWFAgentsGraph';
update FND_USER_PREFERENCES set preference_value = to_char(processedCount) where preference_name = 'NUM_PROCESSED' and user_name='-WF_DEFAULT-' and module_name='WFManagerWFAgentsGraph';
update FND_USER_PREFERENCES set preference_value = to_char(expiredCount) where preference_name = 'NUM_EXPIRED' and user_name='-WF_DEFAULT-' and module_name='WFManagerWFAgentsGraph';
update FND_USER_PREFERENCES set preference_value = to_char(undeliverableCount) where preference_name = 'NUM_UNDELIV' and user_name='-WF_DEFAULT-' and module_name='WFManagerWFAgentsGraph';
update FND_USER_PREFERENCES set preference_value = to_char(erroredCount) where preference_name = 'NUM_ERROR' and user_name='-WF_DEFAULT-' and module_name='WFManagerWFAgentsGraph';
update fnd_user_preferences
set preference_value = to_char(sysdate,'dd/MM/YYYY HH24:MI:SS')
where preference_name = 'LAST_UPDATE_TIME' and
module_name = 'WFManagerWFAgentsGraph' and
user_name = '-WF_DEFAULT-';
SELECT distinct(queue_name) as queue_name FROM wf_agents
WHERE queue_name is not null
AND queue_name not like '%WF_SMTP_O_1_QUEUE'
AND system_guid = hextoraw(wf_core.translate('WF_SYSTEM_GUID'));
UPDATE wf_agents
SET NUM_READY = 0, NUM_WAITING = 0, NUM_EXPIRED = 0, NUM_UNDELIV = 0,
NUM_ERROR = 0, NUM_PROCESS = 0;
update wf_agents
SET NUM_READY = readyCount, NUM_WAITING = waitingCount,
NUM_EXPIRED = expiredCount, NUM_UNDELIV = undeliverableCount,
NUM_ERROR = erroredCount, NUM_PROCESS = processedCount
WHERE queue_name = agent_row.queue_name and
system_guid = hextoraw(wf_core.translate('WF_SYSTEM_GUID'));
update fnd_user_preferences
set preference_value = to_char(sysdate,'dd/MM/YYYY HH24:MI:SS')
where preference_name = 'LAST_UPDATE_TIME' and
module_name = 'WFManagerWFAgents' and
user_name = '-WF_DEFAULT-';
SELECT count(*) into processedCount FROM wf_notifications
WHERE mail_status = 'SENT' AND status = 'OPEN';
SELECT count(*) into waitingCount FROM wf_notifications
WHERE mail_status = 'MAIL';
UPDATE FND_USER_PREFERENCES
SET preference_value = to_char(processedCount)
WHERE preference_name = 'NUM_PROCESSED' and
user_name='-WF_DEFAULT-' and module_name='WFManagerWFNtfsGraph';
UPDATE FND_USER_PREFERENCES SET preference_value = to_char(waitingCount) WHERE preference_name = 'NUM_WAITING' and
user_name='-WF_DEFAULT-' and module_name='WFManagerWFNtfsGraph';
UPDATE fnd_user_preferences
set preference_value = to_char(sysdate,'dd/MM/YYYY HH24:MI:SS')
where preference_name = 'LAST_UPDATE_TIME' and
module_name = 'WFManagerWFNtfsGraph' and
user_name = '-WF_DEFAULT-';