[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT cct_wf_process_id_s.nextval
FROM dual;
SELECT agent_ID
FROM CCT_ROUTING_RESULTS
WHERE call_ID = p_call_ID
ORDER BY sort_num;
DELETE from CCT_ROUTING_RESULTS
WHERE call_ID = p_call_ID;
DELETE from CCT_ROUTING_RESULTS
WHERE call_ID = p_call_ID;
procedure Selector (
itemtype in varchar2
, itemkey in varchar2
, actid in number
, funmode in varchar2
, resultout in out nocopy varchar2
) IS
l_select_process VARCHAR2(30) := 'SELECTOR';
select WIA.TEXT_DEFAULT
into resultout
from WF_ITEM_ATTRIBUTES WIA
where WIA.ITEM_TYPE = itemtype
and WIA.NAME = l_select_process;
end Selector;
l_dynamic_select VARCHAR2(4000);
l_select_csr INTEGER;
l_default_select VARCHAR2(200);
select FILTER_TYPE from CCT_TEMPAGENTS
where call_id = l_call_id
and agent_id = '-1' ;
l_default_select :=
'Select distinct(A.agent_id) from cct_tempagents a '||
'where a.call_id ='||l_apos||l_call_ID||l_apos||
' and a.agent_id <> '||l_apos||-1||l_apos;
l_dynamic_select := l_default_select;
l_dynamic_select := l_dynamic_select ||
' AND A.agent_ID IN (SELECT agent_ID from CCT_TEMPAGENTS ' ||
' WHERE CALL_ID = ' || l_apos || l_call_ID || l_apos ||
' AND FILTER_TYPE = ' || l_apos || l_filter.FILTER_TYPE || l_apos || ')';
l_select_csr := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(l_select_csr, l_dynamic_select, DBMS_SQL.native);
DBMS_SQL.DEFINE_COLUMN(l_select_csr, 1, l_agent_ID, 32);
l_dummy := DBMS_SQL.EXECUTE(l_select_csr);
if DBMS_SQL.FETCH_ROWS(l_select_csr) = 0 then
EXIT;
DBMS_SQL.COLUMN_VALUE(l_select_csr, 1, l_agent_ID);
l_dynamic_select := l_default_select;
DBMS_SQL.PARSE(l_select_csr, l_dynamic_select, DBMS_SQL.NATIVE);
DBMS_SQL.DEFINE_COLUMN(l_select_csr, 1, l_agent_ID, 32);
l_dummy := DBMS_SQL.EXECUTE(l_select_csr);
if DBMS_SQL.FETCH_ROWS(l_select_csr) = 0 then
EXIT;
DBMS_SQL.COLUMN_VALUE(l_select_csr, 1, l_agent_ID);
DBMS_SQL.CLOSE_CURSOR(l_select_csr);
DELETE from CCT_TEMPAGENTS
WHERE CALL_ID = l_call_ID;
-- randomization needed insert all into CCT_ROUTING_RESULTS
Randomize_Agents(l_agents_tbl, l_sort_num);
INSERT INTO CCT_ROUTING_RESULTS
(call_id,itemkey,agent_id,sort_num,
routing_Result_id,last_update_date,last_updated_by,
creation_Date,created_by)
VALUES (l_call_ID, itemkey,l_agents_tbl(counter), counter,
1001,sysdate,1,sysdate,1);
INSERT INTO CCT_ROUTING_RESULTS
(call_id,itemkey,agent_id,sort_num,
routing_Result_id,last_update_date,last_updated_by,
creation_Date,created_by)
VALUES (l_call_ID, itemkey,l_agents_tbl(counter), counter,
1001,sysdate,1,sysdate,1);
DBMS_SQL.CLOSE_CURSOR (l_select_csr);
param_name.delete;
param_val.delete;
param_type.delete;
paramHash.delete;
SELECT cct_wf_process_id_s.nextval
FROM dual;
SELECT agent_ID
FROM CCT_ROUTING_RESULTS
-- WHERE call_ID = p_call_ID
WHERE call_ID = p_call_ID
ORDER BY sort_num;
DELETE from CCT_ROUTING_RESULTS
WHERE call_ID = p_call_ID;
DELETE from CCT_ROUTING_RESULTS
WHERE call_ID = p_call_ID;
-- If SELECTOR is sent as part of string set the global G_PROCESS_NAME
-- this process name will be used to start the wf process
IF param_name(ind) = 'SELECTOR' THEN
G_PROCESS_NAME := paramHash(p_ind+1);
l_dynamic_select VARCHAR2(4000);
l_select_csr INTEGER;
l_default_select VARCHAR2(200);
select FILTER_TYPE from CCT_TEMPAGENTS
where call_id = l_call_id
and agent_id = '-1' ;
l_default_select :=
'Select distinct(A.agent_id) from cct_tempagents a '||
'where a.call_id ='||l_apos||l_call_ID||l_apos||
' and a.agent_id <> '||l_apos||-1||l_apos;
l_dynamic_select := l_default_select;
l_dynamic_select := l_dynamic_select ||
' AND A.agent_ID IN (SELECT agent_ID from CCT_TEMPAGENTS ' ||
' WHERE CALL_ID = ' || l_apos || l_call_ID || l_apos ||
' AND FILTER_TYPE = ' || l_apos || l_filter.FILTER_TYPE || l_apos || ')';
l_select_csr := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(l_select_csr, l_dynamic_select, DBMS_SQL.native);
DBMS_SQL.DEFINE_COLUMN(l_select_csr, 1, l_agent_ID, 32);
l_dummy := DBMS_SQL.EXECUTE(l_select_csr);
if DBMS_SQL.FETCH_ROWS(l_select_csr) = 0 then
EXIT;
DBMS_SQL.COLUMN_VALUE(l_select_csr, 1, l_agent_ID);
l_dynamic_select := l_default_select;
DBMS_SQL.PARSE(l_select_csr, l_dynamic_select, DBMS_SQL.NATIVE);
DBMS_SQL.DEFINE_COLUMN(l_select_csr, 1, l_agent_ID, 32);
l_dummy := DBMS_SQL.EXECUTE(l_select_csr);
if DBMS_SQL.FETCH_ROWS(l_select_csr) = 0 then
IF l_reroute is NULL THEN
-- If the call is already rerouted once don't reroute again
-- even if no agents found
-- Set Rerouted to Y as this call is about to be rerouted
WF_ENGINE.SetItemAttrText (
itemtype
, itemkey
, 'REROUTED'
, 'Y');
DBMS_SQL.COLUMN_VALUE(l_select_csr, 1, l_agent_ID);
DBMS_SQL.CLOSE_CURSOR(l_select_csr);
DELETE from CCT_TEMPAGENTS
WHERE CALL_ID = l_call_ID;
-- randomization needed insert all into CCT_ROUTING_RESULTS
Randomize_Agents(l_agents_tbl, l_sort_num);
INSERT INTO CCT_ROUTING_RESULTS
(call_id,itemkey,agent_id,sort_num,
routing_Result_id,last_update_date,last_updated_by,
creation_Date,created_by)
VALUES (l_call_ID, itemkey,l_agents_tbl(counter), counter,
1001,sysdate,1,sysdate,1);
INSERT INTO CCT_ROUTING_RESULTS
(call_id,itemkey,agent_id,sort_num,
routing_Result_id,last_update_date,last_updated_by,
creation_Date,created_by)
VALUES (l_call_ID, itemkey,l_agents_tbl(counter), counter,
1001,sysdate,1,sysdate,1);
DBMS_SQL.CLOSE_CURSOR (l_select_csr);