[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
LAST_UPDATED_BY_LIST NUMBER_TAB;
LAST_UPDATE_DATE_LIST DATE_TAB;
LAST_UPDATE_LOGIN_LIST NUMBER_TAB;
BIND_LAST_UPDATED_BY_LIST NUMBER_TAB;
BIND_LAST_UPDATE_DATE_LIST DATE_TAB;
BIND_LAST_UPDATE_LOGIN_LIST NUMBER_TAB;
SELECT DISTINCT
ptable.CLI_PLUGIN_CLASS
INTO
l_class
FROM
IEU_UWQ_CLI_MED_PLUGINS ptable
WHERE
(ptable.MEDIA_TYPE_ID = l_media_types(i).media_type_id);
SELECT
DISTINCT
ptable.CLI_PLUGIN_CLASS
INTO
l_class
FROM
IEU_UWQ_CLI_MED_PLUGINS ptable
WHERE
( (ptable.MEDIA_TYPE_ID = IEU_CONSTS_PUB.G_MTID_INBOUND_TELEPHONY)
OR
(ptable.MEDIA_TYPE_ID = IEU_CONSTS_PUB.G_MTID_OUTBOUND_TELEPHONY)
OR
(ptable.MEDIA_TYPE_ID = IEU_CONSTS_PUB.G_MTID_ADV_OUTB_TELEPHONY))
AND
(ROWNUM <= 1)
ORDER BY
ptable.CLI_PLUGIN_ID;
SELECT
DISTINCT
ptable.CLI_PLUGIN_CLASS
INTO
l_class
FROM
IEU_UWQ_CLI_MED_PLUGINS ptable
WHERE
( (ptable.MEDIA_TYPE_ID = IEU_CONSTS_PUB.G_MTID_INBOUND_EMAIL) OR
(ptable.MEDIA_TYPE_ID = IEU_CONSTS_PUB.G_MTID_DIRECT_EMAIL) ) AND
(ROWNUM <= 1)
ORDER BY
ptable.CLI_PLUGIN_ID;
select a.tel_reqd_flag, decode(b.login_rule_type, 'FUNC', b.login_rule, null),
decode(b.login_rule_type, 'MUUID', b.login_rule, null)
into l_tel_reqd_flag, l_svr_connect_rule, l_tel_media_type
from ieu_uwq_media_types_b a, ieu_uwq_login_rules_b b
where a.media_type_uuid = l_media_type_uuid
and a.svr_login_rule_id = b.svr_login_rule_id(+);
select instr(str, '|') into pos from dual;
select instr(str, '|') into pos from dual;
select a.media_type_id, a.media_type_uuid, a.tel_reqd_flag, decode(b.login_rule_type, 'FUNC', b.login_rule, null),
decode(b.login_rule_type, 'MUUID', b.login_rule, null)
into l_media_type_id_1, l_media_type_uuid_1, l_tel_reqd_flag, l_svr_connect_rule, l_tel_media_type
from ieu_uwq_media_types_b a, ieu_uwq_login_rules_b b
where a.media_type_uuid = l_media_type_uuid_1
and a.svr_login_rule_id = b.svr_login_rule_id(+);
SELECT
DISTINCT
mttab.MEDIA_TYPE_ID,
mttab.MEDIA_TYPE_UUID
FROM
-- sjm got rid of server side checks altogether for Client Provider enh.
-- this is simpler and more flexible for 3rd party providers
-- now just check for all media types that have media provider plugins defined
/* JTF_RS_RESOURCE_EXTNS restab,
IEO_SVR_SERVERS svrtab,
IEO_SVR_SERVERS svrtab2,
IEU_UWQ_SVR_MPS_MMAPS mmptab,
IEU_UWQ_MEDIA_TYPES_B mttab
WHERE
(restab.RESOURCE_ID = p_resource_id) AND
(restab.SERVER_GROUP_ID IS NOT NULL) AND
( (svrtab.MEMBER_SVR_GROUP_ID IS NOT NULL) AND
(
(restab.SERVER_GROUP_ID = svrtab.MEMBER_SVR_GROUP_ID) OR
(
(svrtab.USING_SVR_GROUP_ID IS NOT NULL) AND
(svrtab.SERVER_ID = svrtab2.SERVER_ID) AND
(svrtab.USING_SVR_GROUP_ID = svrtab2.MEMBER_SVR_GROUP_ID) AND
(svrtab2.MEMBER_SVR_GROUP_ID = restab.SERVER_GROUP_ID)
)
)
) AND
(svrtab.TYPE_ID = mmptab.SVR_TYPE_ID) AND
(mmptab.MEDIA_TYPE_ID = mttab.MEDIA_TYPE_ID);
(SELECT MEDIA_TYPE_ID
FROM IEU_UWQ_SVR_MPS_MMAPS
UNION -- 10/5/04 changed to union #3926849
SELECT subclimap.MEDIA_TYPE_ID
FROM IEU_CLI_PROV_PLUGIN_MED_MAPS subclimap,
IEU_CLI_PROV_PLUGINS cliplugins
WHERE
subclimap.PLUGIN_ID = cliplugins.PLUGIN_ID
AND (cliplugins.IS_ACTIVE_FLAG is NULL
OR upper(cliplugins.IS_ACTIVE_FLAG) = 'Y')
);
SELECT WORK_Q_ENABLE_PROFILE_OPTION
INTO L_WORK_Q_ENABLE_PROFILE_OPTION
FROM IEU_UWQ_SEL_ENUMERATORS
WHERE media_type_id = c_rec.MEDIA_TYPE_ID
AND NVL(not_valid_flag, 'N') = 'N';
SELECT
PROFILE_OPTION_ID
INTO
l_profile_id
FROM
FND_PROFILE_OPTIONS
WHERE
PROFILE_OPTION_NAME = L_WORK_Q_ENABLE_PROFILE_OPTION;
SELECT
e.SEL_ENUM_ID sel_enum_id,
e.ENUM_PROC enum_proc,
nvl(IEU_UWQ_UTIL_PUB.to_number_noerr(fnd_profile.value(e.work_q_order_profile_option)), e.work_q_order_system_default) display_order,
e.work_q_register_type,
e.media_type_id
FROM
IEU_UWQ_SEL_ENUMERATORS e
WHERE EXISTS (select 'x' from FND_PROFILE_OPTIONS b
where b.PROFILE_OPTION_NAME = upper(e.work_q_enable_profile_option)
and (b.end_date_active is null -- Niraj, bug 4738501, Added
or trunc(b.end_date_active) > trunc(sysdate))) -- Niraj, Bug 5031721, Added
AND ((e.NOT_VALID_FLAG is NULL) OR (e.NOT_VALID_FLAG = 'N')) AND
(nvl(fnd_profile.value(e.work_q_enable_profile_option),'Y') = 'Y')
ORDER BY
display_order;
UPDATE IEU_UWQ_SEL_RT_NODES
SET not_valid = 'Y'
WHERE resource_id = P_RESOURCE_ID;
UPDATE IEU_UWQ_RTNODE_BIND_VALS
SET not_valid_flag = 'Y'
WHERE resource_id = P_RESOURCE_ID;
select
rownum
into
l_media_count
from
IEU_UWQ_SEL_RT_NODES
where
(resource_id = p_resource_id) and
(not_valid = 'N') and
(media_type_id IS NOT NULL) and
(rownum = 1);
Select
meaning
into
l_node_label
from
fnd_lookup_values_vl
where
(lookup_type = 'IEU_NODE_LABELS') and
(view_application_id = 696) and
(lookup_code = 'IEU_MEDIA_LBL');
SELECT
rt_nodes.sel_rt_node_id,
rt_nodes.node_id,
rt_nodes.node_pid,
rt_nodes.view_name,
rt_nodes.where_clause,
rt_nodes.media_type_id,
rt_nodes.sel_enum_id,
rt_nodes.refresh_view_name,
rt_nodes.refresh_view_sum_col,
rt_nodes.res_cat_enum_flag,
rt_nodes.node_depth
FROM
ieu_uwq_sel_rt_nodes rt_nodes
WHERE
(rt_nodes.resource_id = p_resource_id) AND
(rt_nodes.node_id > 0) AND
/* (rt_nodes.node_id <> IEU_CONSTS_PUB.G_SNID_MEDIA) and */
(rt_nodes.not_valid = 'N');
SELECT
rt_nodes.sel_rt_node_id,
rt_nodes.node_id,
rt_nodes.node_pid,
rt_nodes.where_clause,
rt_nodes.sel_enum_id,
rt_nodes.refresh_view_name,
rt_nodes.refresh_view_sum_col,
rt_nodes.res_cat_enum_flag,
rt_nodes.view_name
FROM
ieu_uwq_sel_rt_nodes rt_nodes
WHERE
(rt_nodes.resource_id = p_resource_id) AND
(rt_nodes.node_id = IEU_CONSTS_PUB.G_SNID_MEDIA) and
(rt_nodes.not_valid = 'N');
SELECT
rt_nodes_bind_val.SEL_RT_NODE_ID,
rt_nodes_bind_val.node_id,
rt_nodes_bind_val.BIND_VAR_NAME,
rt_nodes_bind_val.bind_var_value
FROM
ieu_uwq_rtnode_bind_vals rt_nodes_bind_val
WHERE
(rt_nodes_bind_val.resource_id = p_resource_id) AND
(rt_nodes_bind_val.node_id > 0) AND
(rt_nodes_bind_val.not_valid_flag = 'N');
IEU_PVT.SEL_RT_NODE_ID_REF_LIST.DELETE;
IEU_PVT.REF_COUNT_LIST.DELETE;
UPDATE IEU_UWQ_SEL_RT_NODES
SET COUNT = IEU_PVT.REF_COUNT_LIST(x)
WHERE SEL_RT_NODE_ID = IEU_PVT.SEL_RT_NODE_ID_REF_LIST(x)
AND RESOURCE_ID = P_RESOURCE_ID;
IEU_PVT.SEL_RT_NODE_ID_REF_LIST.delete;
IEU_PVT.REF_COUNT_LIST.delete;
IEU_PVT.SEL_RT_NODE_ID_REF_LIST.delete;
IEU_PVT.REF_COUNT_LIST.delete;
UPDATE IEU_UWQ_SEL_RT_NODES
SET COUNT = l_bulk_count
WHERE SEL_RT_NODE_ID = l_sel_rt_node_id
AND RESOURCE_ID = P_RESOURCE_ID;
UPDATE IEU_UWQ_SEL_RT_NODES
SET COUNT = l_bulk_count
WHERE SEL_RT_NODE_ID = l_tsk_sel_rt_node_id
AND RESOURCE_ID = P_RESOURCE_ID;
select
where_clause
into
l_res_cat_where_clause
from
ieu_uwq_res_cats_b
where
res_cat_id = 10001;
select
decode(
(instr(l_res_cat_where_clause, ':resource_id', 1, 1)), 0, 'F','T' )
into
l_enum_bind_var_flag
from
dual;
select
refresh_proc
into
l_refresh_proc
from
ieu_uwq_sel_enumerators
where
sel_enum_id = p_sel_enum_id;
'Select sum(' || p_REFRESH_VIEW_SUM_COL || ') from ' ||
p_REFRESH_view_name || ' where ' || l_where_clause ||
' and ieu_param_pk_value is not null';
so, the above select would not work for 'ANY' */
if p_node_pid = 4000 then
l_media_sql_stmt :=
'begin select count(*) into :l_node_count from '||
p_refresh_view_name||' where resource_id = '||':p_resource_id'||'; end;';
'begin select ieu_param_pk_value into :l_param_pk_value from '||
p_refresh_view_name||' where resource_id = '||':p_resource_id'||'; end;';
'Select sum(' || p_REFRESH_VIEW_SUM_COL || ') from ' ||
p_REFRESH_view_name || ' where ' || l_where_clause;
l_sql_stmt := 'select /*+ index(v.tasks JTF_TASKS_B_U1) */ count(resource_id) from ' || p_refresh_view_name || ' where ' || l_where_clause;
'select count(resource_id) from ' || p_refresh_view_name ||
' where ' || l_where_clause;
'select count(resource_id) from ' || p_view_name ||
' where ' || l_where_clause;
select
decode((instr(l_where_clause, ':resource_id', 1, 1)), 0, 'F','T' )
into
l_resource_id_flag
from
dual;
/* UPDATE
IEU_UWQ_SEL_RT_NODES nodes
SET
nodes.count = l_count
WHERE
(nodes.sel_rt_node_id = p_sel_rt_node_id) AND
(nodes.resource_id = p_resource_id);
l_last_update_secs PLS_INTEGER;
SELECT
DISTINCT
SERVER_GROUP_ID
INTO
l_server_group_id
FROM
JTF_RS_RESOURCE_EXTNS
WHERE
(RESOURCE_ID = P_RESOURCE_ID) AND
(ROWNUM <= 1);
SELECT
binds.SERVER_ID
INTO
l_server_id
FROM
IEU_UWQ_AGENT_BINDINGS binds,
IEO_SVR_SERVERS srvrs,
IEO_SVR_RT_INFO rti
WHERE
(binds.RESOURCE_ID = P_RESOURCE_ID) AND
(binds.SERVER_ID = srvrs.SERVER_ID) AND
(nvl(binds.NOT_VALID,'N') = 'N') AND
(srvrs.TYPE_ID = IEU_CONSTS_PUB.G_STID_UWQ) AND
(srvrs.SERVER_ID = rti.SERVER_ID) AND
(nvl(rti.STATUS,0) > 0) AND
(ROWNUM <= 1);
SELECT
DISTINCT
to_number(to_char(rti.LAST_UPDATE_DATE,'SSSSS'))
INTO
l_last_update_secs
FROM
IEO_SVR_RT_INFO rti
WHERE
(rti.SERVER_ID = l_server_id) AND
(ROWNUM <= 1);
SELECT
DISTINCT
((stype.RT_REFRESH_RATE * 60) + 60)
INTO
l_threshold_secs
FROM
IEO_SVR_SERVERS srvrs,
IEO_SVR_TYPES_B stype
WHERE
(srvrs.SERVER_ID = l_server_id) AND
(srvrs.TYPE_ID = stype.TYPE_ID) AND
(ROWNUM <= 1);
IF ( ABS(l_curr_time_secs - l_last_update_secs) > l_threshold_secs )
THEN
l_server_id := null;
SELECT
DISTINCT
comp_table.COMP_NAME
INTO
X_COMP_NAME
FROM
IEO_SVR_SERVERS svr_table,
IEO_SVR_COMP_DEFS cdef_table,
IEO_SVR_COMPS comp_table,
IEO_SVR_PROTOCOL_MAP prot_table
WHERE
(svr_table.SERVER_ID = comp_table.SERVER_ID) AND
(svr_table.SERVER_ID = l_server_id) AND
(comp_table.COMP_DEF_ID = cdef_table.COMP_DEF_ID) AND
(prot_table.COMP_ID = comp_table.COMP_ID) AND
(prot_table.WIRE_PROTOCOL = P_WIRE_PROTOCOL) AND
(cdef_table.COMP_DEF_NAME = P_COMP_DEF_NAME) AND
(cdef_table.COMP_DEF_VERSION = P_COMP_DEF_VERSION) AND
(cdef_table.IMPLEMENTATION = P_COMP_DEF_IMPL) AND
(ROWNUM <= 1);
SELECT
DISTINCT
svr_table.USER_ADDRESS,
svr_table.DNS_NAME,
svr_table.IP_ADDRESS,
prot_table.PORT
INTO
X_SVR_USER_ADDRESS,
X_SVR_DNS_NAME,
X_SVR_IP_ADDRESS,
X_SVR_PORT
FROM
IEO_SVR_SERVERS svr_table,
IEO_SVR_COMP_DEFS cdef_table,
IEO_SVR_COMPS comp_table,
IEO_SVR_PROTOCOL_MAP prot_table
WHERE
(svr_table.SERVER_ID = l_server_id) AND
(svr_table.SERVER_ID = comp_table.SERVER_ID) AND
(comp_table.COMP_DEF_ID = cdef_table.COMP_DEF_ID) AND
(prot_table.COMP_ID = comp_table.COMP_ID) AND
(prot_table.WIRE_PROTOCOL = P_WIRE_PROTOCOL) AND
(cdef_table.COMP_DEF_NAME = P_COMP_DEF_NAME) AND
(cdef_table.COMP_DEF_VERSION = P_COMP_DEF_VERSION) AND
(cdef_table.IMPLEMENTATION = P_COMP_DEF_IMPL) AND
(comp_table.COMP_NAME = X_COMP_NAME) AND
(ROWNUM <= 1);
SELECT
ab.SERVER_ID
FROM
IEU_UWQ_AGENT_BINDINGS ab,
IEO_SVR_SERVERS srvrs
WHERE
ab.RESOURCE_ID = P_RESOURCE_ID AND
ab.SERVER_ID = srvrs.SERVER_ID AND
srvrs.TYPE_ID = svr_type_id;
SELECT
TYPE_ID
INTO
l_svr_type_id
FROM
IEO_SVR_SERVERS
WHERE
SERVER_ID = P_SERVER_ID;
UPDATE
IEU_UWQ_AGENT_BINDINGS ab
SET
ab.LAST_UPDATE_DATE = SYSDATE,
ab.NOT_VALID = 'Y'
WHERE
ab.RESOURCE_ID = P_RESOURCE_ID AND
ab.SERVER_ID = cur.SERVER_ID;
UPDATE IEU_UWQ_AGENT_BINDINGS ab
SET
ab.LAST_UPDATE_DATE = SYSDATE,
ab.NOT_VALID = NULL
WHERE
ab.RESOURCE_ID = P_RESOURCE_ID AND
ab.SERVER_ID = P_SERVER_ID;
INSERT INTO IEU_UWQ_AGENT_BINDINGS
( RESOURCE_ID,
SERVER_ID,
LAST_UPDATE_DATE,
NOT_VALID )
VALUES (
P_RESOURCE_ID,
P_SERVER_ID,
SYSDATE,
NULL );
UPDATE IEU_UWQ_AGENT_BINDINGS ab
SET
ab.LAST_UPDATE_DATE = SYSDATE,
ab.NOT_VALID = 'Y'
WHERE
ab.RESOURCE_ID = P_RESOURCE_ID AND
ab.SERVER_ID = P_SERVER_ID;
UPDATE IEU_UWQ_AGENT_BINDINGS ab
SET
ab.LAST_UPDATE_DATE = SYSDATE,
ab.NOT_VALID = 'Y'
WHERE
ab.RESOURCE_ID = P_RESOURCE_ID;
/* update rt info */
/*server status is always 4 when agent logs in*/
IEO_SVR_UTIL_PVT.UPDATE_RT_INFO( P_SERVER_ID, 4,
P_MAJOR_LOAD_FACTOR,
P_MINOR_LOAD_FACTOR, ' ' );
PROCEDURE UPDATE_SERVER_STARTUP_INFO( P_SERVER_ID IN NUMBER
,P_IP_ADDRESS IN VARCHAR2
,P_DNS_NAME IN VARCHAR2
,P_USER_ADDRESS IN VARCHAR2 )
AS
BEGIN
/*clear all old server bindings*/
IEO_SVR_UTIL_PVT.CLEAR_SERVER_BINDINGS( P_SERVER_ID );
/*update server information*/
UPDATE IEO_SVR_SERVERS
SET DNS_NAME = P_DNS_NAME,
IP_ADDRESS = P_IP_ADDRESS,
USER_ADDRESS = P_USER_ADDRESS
WHERE SERVER_ID = P_SERVER_ID;
END UPDATE_SERVER_STARTUP_INFO;
PROCEDURE BIND_AGENT_AND_UPDATE_LOAD( P_RESOURCE_ID IN NUMBER
,P_SERVER_ID IN NUMBER
,P_MAJOR_LOAD_FACTOR IN NUMBER
,P_MINOR_LOAD_FACTOR IN NUMBER
,X_EXISTING_BINDINGS OUT NOCOPY BINDING_CURSOR )
AS
l_binding_cursor BINDING_CURSOR;
/* update rt info */
/*server status is always 4 when agent logs in*/
IEO_SVR_UTIL_PVT.UPDATE_RT_INFO( P_SERVER_ID, 4,
P_MAJOR_LOAD_FACTOR,
P_MINOR_LOAD_FACTOR, ' ' );
l_binding_statement := 'SELECT bindings.server_id, svr_types.type_id FROM ' ||
' ieu_uwq_agent_bindings bindings, ' ||
' ieo_svr_types_b svr_types, ' ||
' ieo_svr_servers svrs ' ||
' WHERE bindings.resource_id = :1 ' ||
' AND bindings.server_id=svrs.server_id ' ||
' AND svr_types.type_id = svrs.type_id ' ||
' AND bindings.NOT_VALID IS NULL';
END BIND_AGENT_AND_UPDATE_LOAD;
PROCEDURE FORCE_UPDATE_MRT_DATA
(P_RESOURCE_ID IN IEU_UWQ_SEL_MRT_DATA.RESOURCE_ID%TYPE
,P_SERVER_TYPE_ID IN IEU_UWQ_SEL_MRT_DATA.SVR_TYPE_ID%TYPE
,P_MEDIA_TYPE_ID IN IEU_UWQ_SEL_MRT_DATA.MEDIA_TYPE_ID%TYPE
,P_QUEUE_LIST IN SYSTEM.IEU_UWQ_SEL_MRT_QUEUES_NST
)
AS
BEGIN
IF ( (P_RESOURCE_ID IS NULL) OR
(P_SERVER_TYPE_ID IS NULL) OR
(P_MEDIA_TYPE_ID IS NULL) )
THEN
raise_application_error
(-20000
,'P_RESOURCE_ID, P_SERVER_TYPE_ID, and P_MEDIA_TYPE_ID cannot be NULL.' ||
'(P_RESOURCE_ID = ' || P_RESOURCE_ID ||
') (P_SERVER_TYPE_ID = ' || P_SERVER_TYPE_ID ||
') (P_MEDIA_TYPE_ID = ' || P_MEDIA_TYPE_ID || ')'
,TRUE
);
SAVEPOINT start_update;
UPDATE IEU_UWQ_SEL_MRT_DATA
SET
NOT_VALID = 'Y',
LAST_UPDATE_DATE = SYSDATE
WHERE
(RESOURCE_ID = P_RESOURCE_ID) AND
(SVR_TYPE_ID = P_SERVER_TYPE_ID) AND
(MEDIA_TYPE_ID = P_MEDIA_TYPE_ID);
UPDATE IEU_UWQ_SEL_MRT_DATA
SET
NOT_VALID = NULL,
LAST_UPDATE_DATE = SYSDATE,
QUEUE_COUNT = P_QUEUE_LIST(i).QUEUE_COUNT,
PROVIDER_REF = p_QUEUE_LIST(i).PROVIDER_REF
WHERE
(RESOURCE_ID = P_RESOURCE_ID) AND
(SVR_TYPE_ID = P_SERVER_TYPE_ID) AND
(MEDIA_TYPE_ID = P_MEDIA_TYPE_ID) AND
( ( (QUEUE_NAME IS NULL) AND
(P_QUEUE_LIST(i).QUEUE_NAME IS NULL ) ) OR
(QUEUE_NAME = P_QUEUE_LIST(i).QUEUE_NAME) ) AND
(ROWNUM <= 1);
INSERT INTO IEU_UWQ_SEL_MRT_DATA
( SEL_MRT_ID,
RESOURCE_ID,
SVR_TYPE_ID,
MEDIA_TYPE_ID,
LAST_UPDATE_DATE,
NOT_VALID,
QUEUE_NAME,
QUEUE_COUNT,
PROVIDER_REF )
VALUES (
IEU_UWQ_SEL_MRT_DATA_S1.NEXTVAL,
P_RESOURCE_ID,
P_SERVER_TYPE_ID,
P_MEDIA_TYPE_ID,
SYSDATE,
NULL,
P_QUEUE_LIST(i).QUEUE_NAME,
P_QUEUE_LIST(i).QUEUE_COUNT,
P_QUEUE_LIST(i).PROVIDER_REF );
ROLLBACK TO start_update;
END FORCE_UPDATE_MRT_DATA;
select
(max(node_id)+1)
into
l_curr_node_id
from
ieu_uwq_sel_rt_nodes
where
(resource_id = p_resource_id) and
(not_valid = 'N');
UPDATE IEU_UWQ_SEL_RT_NODES SET
SEL_ENUM_ID = IEU_PVT.SEL_ENUM_ID_LIST(i),
NODE_TYPE = IEU_PVT.NODE_TYPE_LIST(i),
NODE_PID = IEU_PVT.NODE_PID_LIST(i),
NODE_WEIGHT = IEU_PVT.NODE_WEIGHT_LIST(i),
NODE_DEPTH = IEU_PVT.NODE_DEPTH_LIST(i),
SEL_ENUM_PID = IEU_PVT.SEL_ENUM_PID_LIST(i),
MEDIA_TYPE_ID = IEU_PVT.MEDIA_TYPE_ID_LIST(i),
COUNT = IEU_PVT.COUNT_LIST(i),
DATA_SOURCE = IEU_PVT.DATA_SOURCE_LIST(i),
VIEW_NAME = IEU_PVT.VIEW_NAME_LIST(i),
WHERE_CLAUSE = IEU_PVT.WHERE_CLAUSE_LIST(i),
HIDE_IF_EMPTY = IEU_PVT.HIDE_IF_EMPTY_LIST(i),
REFRESH_VIEW_NAME = IEU_PVT.REFRESH_VIEW_NAME_LIST(i),
REFRESH_VIEW_SUM_COL = IEU_PVT.REFRESH_VIEW_SUM_COL_LIST(i),
RES_CAT_ENUM_FLAG = IEU_PVT.RES_CAT_ENUM_FLAG_LIST(i),
NOT_VALID = IEU_PVT.NOT_VALID_LIST(i),
NODE_LABEL = IEU_PVT.NODE_LABEL_LIST(i),
LAST_UPDATE_DATE = IEU_PVT.LAST_UPDATE_DATE_LIST(i),
LAST_UPDATED_BY = IEU_PVT.LAST_UPDATED_BY_LIST(i),
LAST_UPDATE_LOGIN = IEU_PVT.LAST_UPDATE_LOGIN_LIST(i)
WHERE RESOURCE_ID = IEU_PVT.RESOURCE_ID_LIST(i)
AND NODE_ID = IEU_PVT.NODE_ID_LIST(i);
IEU_PVT.LAST_UPDATED_BY_LIST.delete;
IEU_PVT.LAST_UPDATE_DATE_LIST.delete;
IEU_PVT.LAST_UPDATE_LOGIN_LIST.delete;
IEU_PVT.RESOURCE_ID_LIST.delete;
IEU_PVT.SEL_ENUM_ID_LIST.delete;
IEU_PVT.NODE_ID_LIST.delete;
IEU_PVT.NODE_TYPE_LIST.delete;
IEU_PVT.NODE_LABEL_LIST.delete;
IEU_PVT.COUNT_LIST.delete;
IEU_PVT.DATA_SOURCE_LIST.delete;
IEU_PVT.VIEW_NAME_LIST.delete;
IEU_PVT.MEDIA_TYPE_ID_LIST.delete;
IEU_PVT.SEL_ENUM_PID_LIST.delete;
IEU_PVT.NODE_PID_LIST.delete;
IEU_PVT.NODE_WEIGHT_LIST.delete;
IEU_PVT.WHERE_CLAUSE_LIST.delete;
IEU_PVT.HIDE_IF_EMPTY_LIST.delete;
IEU_PVT.NOT_VALID_LIST.delete;
IEU_PVT.REFRESH_VIEW_NAME_LIST.delete;
IEU_PVT.RES_CAT_ENUM_FLAG_LIST.delete;
IEU_PVT.REFRESH_VIEW_SUM_COL_LIST.delete;
IEU_PVT.NODE_DEPTH_LIST.delete;
insert into IEU_UWQ_SEL_RT_NODES values IEU_PVT.IEU_UWQ_SEL_RT_NODES_LIST(i);
IEU_PVT.IEU_UWQ_SEL_RT_NODES_LIST.delete;
UPDATE IEU_UWQ_RTNODE_BIND_VALS SET
LAST_UPDATED_BY = IEU_PVT.BIND_LAST_UPDATED_BY_LIST(i),
LAST_UPDATE_DATE = IEU_PVT.BIND_LAST_UPDATE_DATE_LIST(i),
LAST_UPDATE_LOGIN = IEU_PVT.BIND_LAST_UPDATE_LOGIN_LIST(i),
BIND_VAR_VALUE = IEU_PVT.BIND_VAR_VALUE_LIST(i),
BIND_VAR_DATATYPE = IEU_PVT.BIND_VAR_DATATYPE_LIST(i),
NOT_VALID_FLAG = IEU_PVT.NOT_VALID_FLAG_LIST(i),
SEL_RT_NODE_ID = IEU_PVT.BIND_SEL_RT_NODE_ID_LIST(i),
OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1
WHERE RESOURCE_ID = IEU_PVT.BIND_RESOURCE_ID_LIST(i)
AND NODE_ID = IEU_PVT.BIND_NODE_ID_LIST(i)
AND BIND_VAR_NAME = IEU_PVT.BIND_VAR_NAME_LIST(i);
IEU_PVT.BIND_LAST_UPDATED_BY_LIST.delete;
IEU_PVT.BIND_LAST_UPDATE_DATE_LIST.delete;
IEU_PVT.BIND_LAST_UPDATE_LOGIN_LIST.delete;
IEU_PVT.BIND_SEL_RT_NODE_ID_LIST.delete;
IEU_PVT.BIND_RESOURCE_ID_LIST.delete;
IEU_PVT.BIND_NODE_ID_LIST.delete;
IEU_PVT.BIND_VAR_NAME_LIST.delete;
IEU_PVT.BIND_VAR_VALUE_LIST.delete;
IEU_PVT.BIND_VAR_DATATYPE_LIST.delete;
IEU_PVT.NOT_VALID_FLAG_LIST.delete;
insert into IEU_UWQ_RTNODE_BIND_VALS values IEU_PVT.IEU_UWQ_RTNODE_BIND_VALS_LIST(i);
IEU_PVT.IEU_UWQ_RTNODE_BIND_VALS_LIST.delete;
IEU_PVT.LAST_UPDATED_BY_LIST.delete;
IEU_PVT.LAST_UPDATE_DATE_LIST.delete;
IEU_PVT.LAST_UPDATE_LOGIN_LIST.delete;
IEU_PVT.RESOURCE_ID_LIST.delete;
IEU_PVT.SEL_ENUM_ID_LIST.delete;
IEU_PVT.NODE_ID_LIST.delete;
IEU_PVT.NODE_TYPE_LIST.delete;
IEU_PVT.NODE_LABEL_LIST.delete;
IEU_PVT.COUNT_LIST.delete;
IEU_PVT.DATA_SOURCE_LIST.delete;
IEU_PVT.VIEW_NAME_LIST.delete;
IEU_PVT.MEDIA_TYPE_ID_LIST.delete;
IEU_PVT.SEL_ENUM_PID_LIST.delete;
IEU_PVT.NODE_PID_LIST.delete;
IEU_PVT.NODE_WEIGHT_LIST.delete;
IEU_PVT.WHERE_CLAUSE_LIST.delete;
IEU_PVT.HIDE_IF_EMPTY_LIST.delete;
IEU_PVT.NOT_VALID_LIST.delete;
IEU_PVT.REFRESH_VIEW_NAME_LIST.delete;
IEU_PVT.RES_CAT_ENUM_FLAG_LIST.delete;
IEU_PVT.REFRESH_VIEW_SUM_COL_LIST.delete;
IEU_PVT.NODE_DEPTH_LIST.delete;
IEU_PVT.IEU_UWQ_SEL_RT_NODES_LIST.delete;
IEU_PVT.BIND_LAST_UPDATED_BY_LIST.delete;
IEU_PVT.BIND_LAST_UPDATE_DATE_LIST.delete;
IEU_PVT.BIND_LAST_UPDATE_LOGIN_LIST.delete;
IEU_PVT.BIND_SEL_RT_NODE_ID_LIST.delete;
IEU_PVT.BIND_RESOURCE_ID_LIST.delete;
IEU_PVT.BIND_NODE_ID_LIST.delete;
IEU_PVT.BIND_VAR_NAME_LIST.delete;
IEU_PVT.BIND_VAR_VALUE_LIST.delete;
IEU_PVT.BIND_VAR_DATATYPE_LIST.delete;
IEU_PVT.NOT_VALID_FLAG_LIST.delete;
IEU_PVT.IEU_UWQ_RTNODE_BIND_VALS_LIST.delete;
select
nvl (
IEU_UWQ_UTIL_PUB.to_number_noerr (
fnd_profile.value(e.work_q_order_profile_option) ) ,
e.work_q_order_system_default
)
into
l_node_weight
from
ieu_uwq_sel_enumerators e
where
e.sel_enum_id = p_sel_enum_id;
update
ieu_uwq_sel_rt_nodes
set
node_weight = l_node_weight
where
resource_id = p_resource_id and
node_id = l_curr_node_id;
L_RTNODE_INSERT_FLAG VARCHAR2(1);
L_BINDVALS_INSERT_FLAG VARCHAR2(1);
L_RTNODE_INSERT_FLAG := '';
select sel_rt_node_id
into l_sel_rt_node_id
from ieu_uwq_sel_rt_nodes
where RESOURCE_ID = P_RESOURCE_ID
and NODE_ID = l_curr_node_id;
L_RTNODE_INSERT_FLAG := 'N';
SELECT IEU_UWQ_SEL_RT_NODES_S1.NEXTVAL INTO L_SEL_RT_NODE_ID FROM DUAL;
L_RTNODE_INSERT_FLAG := 'Y';
IF NVL(L_RTNODE_INSERT_FLAG, 'X') = 'N' THEN
IEU_PVT.LAST_UPDATED_BY_LIST(i) := FND_GLOBAL.USER_ID;
IEU_PVT.LAST_UPDATE_DATE_LIST(i) := sysdate;
IEU_PVT.LAST_UPDATE_LOGIN_LIST(i) := FND_GLOBAL.LOGIN_ID;
IEU_PVT.IEU_UWQ_SEL_RT_NODES_LIST(IEU_PVT.L_RT_NODES_ITR).LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
IEU_PVT.IEU_UWQ_SEL_RT_NODES_LIST(IEU_PVT.L_RT_NODES_ITR).LAST_UPDATE_DATE := sysdate;
IEU_PVT.IEU_UWQ_SEL_RT_NODES_LIST(IEU_PVT.L_RT_NODES_ITR).LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
select
sel_rt_node_id
into
l_sel_rt_node_id
from
ieu_uwq_sel_rt_nodes
where
RESOURCE_ID = P_RESOURCE_ID and
NODE_ID = l_curr_node_id;
L_BINDVALS_INSERT_FLAG := '';
select rtnode_bind_var_id
into l_rtnode_bind_var_id
from ieu_uwq_rtnode_bind_vals
where RESOURCE_ID = P_RESOURCE_ID
and NODE_ID = l_curr_node_id
and BIND_VAR_NAME = L_BIND_VAR_NAME;
L_BINDVALS_INSERT_FLAG := 'N';
SELECT IEU_UWQ_RTNODE_BIND_VALS_S1.NEXTVAL INTO L_RTNODE_BIND_VAR_ID FROM DUAL;
L_BINDVALS_INSERT_FLAG := 'Y';
IF NVL(L_BINDVALS_INSERT_FLAG, 'X') = 'N' THEN
IEU_PVT.BIND_LAST_UPDATED_BY_LIST(IEU_PVT.L_IND_LIST_ITR) := FND_GLOBAL.USER_ID;
IEU_PVT.BIND_LAST_UPDATE_DATE_LIST(IEU_PVT.L_IND_LIST_ITR) := SYSDATE;
IEU_PVT.BIND_LAST_UPDATE_LOGIN_LIST(IEU_PVT.L_IND_LIST_ITR) := FND_GLOBAL.LOGIN_ID;
IEU_PVT.IEU_UWQ_RTNODE_BIND_VALS_LIST(IEU_PVT.L_BIND_VALS_ITR).LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
IEU_PVT.IEU_UWQ_RTNODE_BIND_VALS_LIST(IEU_PVT.L_BIND_VALS_ITR).LAST_UPDATE_DATE := sysdate;
IEU_PVT.IEU_UWQ_RTNODE_BIND_VALS_LIST(IEU_PVT.L_BIND_VALS_ITR).LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
l_select_id JTF_OBJECTS_B.SELECT_ID%TYPE;
l_select_name JTF_OBJECTS_B.SELECT_NAME%TYPE;
l_select_details JTF_OBJECTS_B.SELECT_DETAILS%TYPE;
SELECT select_id, select_name, select_details, from_table,where_clause
INTO l_select_id,l_select_name, l_select_details, l_from_table, l_where_clause
FROM jtf_objects_b
WHERE object_code = 'AOMANMODE';
select count(*) into l_count1 from ast_grp_campaigns a, IEC_G_EXECUTING_LISTS_V b, -- for bug 6982201
JTF_RS_GROUP_MEMBERS c, JTF_RS_GROUPS_DENORM d
where c.group_id = d.group_id
and a.group_id = d.parent_group_id
and c.resource_id = l_resource_id
and a.campaign_id = b.schedule_id
and b.DIALING_METHOD <> 'MAN';
l_sql_stmt := 'BEGIN SELECT '|| l_select_id || ' INTO :l_count ';
SELECT
sel_rt_node_id,
node_id,
node_pid,
node_depth,
view_name,
where_clause,
sel_enum_id,
refresh_view_name,
refresh_view_sum_col,
res_cat_enum_flag
from
( SELECT
rt_nodes.sel_rt_node_id,
rt_nodes.node_id,
rt_nodes.node_pid,
rt_nodes.node_depth,
rt_nodes.view_name,
rt_nodes.where_clause,
rt_nodes.sel_enum_id,
rt_nodes.refresh_view_name,
rt_nodes.refresh_view_sum_col,
rt_nodes.res_cat_enum_flag
FROM
ieu_uwq_sel_rt_nodes rt_nodes
WHERE
(rt_nodes.resource_id = p_resource_id) AND
(rt_nodes.sel_enum_id = p_sel_enum_id) AND
(rt_nodes.not_valid = 'N')
) ieu_uwq_sel_enumerators
-- connect by node_pid = node_id; -- Niraj: Bug 4352211, 06-May-2005: Commented this and added below 2 statements,
SELECT
rt_nodes.sel_rt_node_id,
rt_nodes.node_id,
rt_nodes.node_pid,
rt_nodes.node_depth,
rt_nodes.view_name,
rt_nodes.where_clause,
rt_nodes.sel_enum_id,
rt_nodes.refresh_view_name,
rt_nodes.refresh_view_sum_col,
rt_nodes.res_cat_enum_flag
FROM
ieu_uwq_sel_rt_nodes rt_nodes
WHERE
(rt_nodes.resource_id = p_resource_id) AND
(rt_nodes.sel_enum_id = p_sel_enum_id) AND
(rt_nodes.not_valid = 'N');
SELECT
rt_nodes.sel_enum_id, rt_nodes.node_depth
FROM
ieu_uwq_sel_rt_nodes rt_nodes
WHERE
(rt_nodes.resource_id = p_resource_id) AND
(rt_nodes.node_pid = 4000) AND
(rt_nodes.not_valid = 'N');
SELECT
rt_nodes_bind_val.SEL_RT_NODE_ID,
rt_nodes_bind_val.node_id,
rt_nodes_bind_val.BIND_VAR_NAME,
rt_nodes_bind_val.bind_var_value
FROM
ieu_uwq_rtnode_bind_vals rt_nodes_bind_val
WHERE
(rt_nodes_bind_val.resource_id = p_resource_id) AND
(rt_nodes_bind_val.node_id > 0) AND
(rt_nodes_bind_val.not_valid_flag = 'N');
IEU_PVT.SEL_RT_NODE_ID_REF_LIST.DELETE;
IEU_PVT.REF_COUNT_LIST.DELETE;
SELECT
rt_nodes.sel_rt_node_id,
rt_nodes.node_id,
rt_nodes.node_pid,
rt_nodes.node_depth,
rt_nodes.view_name,
rt_nodes.where_clause,
rt_nodes.sel_enum_id,
rt_nodes.refresh_view_name,
rt_nodes.refresh_view_sum_col,
rt_nodes.res_cat_enum_flag
INTO
l_sel_rt_node_id,
l_node_id,
l_node_pid,
l_node_depth,
l_view_name,
l_where_clause,
l_sel_enum_id,
l_refresh_view_name,
l_refresh_view_sum_col,
l_res_cat_enum_flag
FROM
ieu_uwq_sel_rt_nodes rt_nodes
WHERE
(rt_nodes.resource_id = p_resource_id) AND
(rt_nodes.node_id = p_node_id) AND
(rt_nodes.not_valid = 'N');
UPDATE IEU_UWQ_SEL_RT_NODES
SET COUNT = IEU_PVT.REF_COUNT_LIST(x)
WHERE SEL_RT_NODE_ID = IEU_PVT.SEL_RT_NODE_ID_REF_LIST(x)
AND RESOURCE_ID = P_RESOURCE_ID;
IEU_PVT.SEL_RT_NODE_ID_REF_LIST.delete;
IEU_PVT.REF_COUNT_LIST.delete;
IEU_PVT.SEL_RT_NODE_ID_REF_LIST.delete;
IEU_PVT.REF_COUNT_LIST.delete;
UPDATE IEU_UWQ_SEL_RT_NODES
SET COUNT = l_count
WHERE SEL_RT_NODE_ID = l_sel_rt_node_id
AND RESOURCE_ID = P_RESOURCE_ID;
UPDATE IEU_UWQ_SEL_RT_NODES
SET COUNT = IEU_PVT.REF_COUNT_LIST(x)
WHERE SEL_RT_NODE_ID = IEU_PVT.SEL_RT_NODE_ID_REF_LIST(x)
AND RESOURCE_ID = P_RESOURCE_ID;
IEU_PVT.SEL_RT_NODE_ID_REF_LIST.delete;
IEU_PVT.REF_COUNT_LIST.delete;
IEU_PVT.SEL_RT_NODE_ID_REF_LIST.delete;
IEU_PVT.REF_COUNT_LIST.delete;
UPDATE IEU_UWQ_SEL_RT_NODES
SET COUNT = l_count
WHERE SEL_RT_NODE_ID = l_sel_rt_node_id
AND RESOURCE_ID = P_RESOURCE_ID;
REFRESH_SELECTIVE_NODE_COUNTS(p_resource_id, p_node_id_string, x_node_id_list);
PROCEDURE REFRESH_SELECTIVE_NODE_COUNTS(p_resource_id in number, p_node_id_string in varchar2, x_node_id_list OUT NOCOPY varchar2 )
IS
current_node_id number;