[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select notification_id nid,
message_type msg_type,
message_name msg_name,
begin_date,
end_date,
recipient_role rec_role,
more_info_role more_role,
status stat,
mail_status m_stat,
callback cb,
context ctx,
responder resp,
subject subj
from wf_notifications
where notification_id = p_nid;
l_cells.DELETE;
l_cells.DELETE;
select NOTIFICATION_ID, RECIPIENT_ROLE, STATUS, MAIL_STATUS, BEGIN_DATE, END_DATE
from WF_NOTIFICATIONS
where GROUP_ID=p_nid and
GROUP_ID<>NOTIFICATION_ID;
l_cells.DELETE;
l_cells.DELETE;
SELECT wur.user_name
FROM wf_user_roles wur, wf_notifications wn
WHERE wur.role_name = wn.recipient_role
AND wn.notification_id = p_nid;
l_cells.DELETE;
l_cells.DELETE;
l_cells.DELETE;
SELECT recipient_role
INTO l_role
FROM wf_notifications
WHERE notification_id = p_nid;
l_cells.DELETE;
SELECT wur.user_name
FROM wf_user_roles wur
WHERE wur.role_name = p_role;
l_cells.DELETE;
l_cells.DELETE;
l_cells.DELETE;
l_cells.DELETE;
l_cells.DELETE;
SELECT more_info_role
INTO l_role
FROM wf_notifications
WHERE notification_id = p_nid;
l_cells.DELETE;
SELECT wr.action action,
wr.begin_date begin_date,
wr.end_date end_date,
wr.message_type msg_type,
wr.message_name msg_name,
wr.action_argument act_arg,
wra.name,
wra.type,
nvl(nvl(wra.text_value, to_char(wra.number_value)), to_char(wra.date_value)) value
FROM wf_routing_rules wr,
wf_routing_rule_attributes wra,
wf_notifications wn
WHERE wr.rule_id = wra.rule_id (+)
AND (wr.role = wn.recipient_role or wr.action_argument = wn.recipient_role)
AND wn.notification_id = p_nid;
l_cells.DELETE;
l_cells.DELETE;
select wma.name name,
wmat.display_name d_name,
wma.sequence seq,
wma.type type,
wma.subtype s_type,
wma.value_type v_type,
decode (wma.type,
'DATE', to_char(wma.date_default),
'NUMBER', to_char(wma.number_default),
wma.text_default) value,
wma.format format
from wf_message_attributes wma,
wf_message_attributes_tl wmat,
wf_notifications wn
where wma.message_name = wmat.message_name
and wma.message_type = wmat.message_type
and wma.name = wmat.name
and wmat.language = userenv('LANG')
and wma.message_type = wn.message_type
and wma.message_name = wn.message_name
and wn.notification_id = p_nid;
l_cells.DELETE;
l_cells.DELETE;
select name name,
number_value num_val,
date_value date_val,
text_value txt_val
from wf_notification_attributes
where notification_id = p_nid;
l_cells.DELETE;
l_cells.DELETE;
select lookup_type lookup_type,
lookup_code lookup_code,
meaning display_Value,
language lang_code
from WF_LOOKUPS_TL
where language = lang
AND lookup_type = type;
l_cells.DELETE;
select message_type, message_name, language
into l_msg_type, l_msg_name, l_lang_code
from wf_notifications
where notification_id = p_nid;
select format
into l_format
from wf_message_attributes
where message_type = l_msg_type
and message_name = l_msg_name
and name = 'RESULT' ;
l_cells.DELETE;
l_cells.DELETE;
select wc.from_role,
wc.from_user,
to_char(wc.comment_date, 'DD-MON-RRRR HH24:MI:SS') comm_date,
wc.action,
wc.user_comment
from wf_comments wc
where wc.notification_id = p_nid
order by comment_date;
l_cells.DELETE;
l_cells.DELETE;
l_cells.DELETE;
l_sql_str := 'select tab.msg_id msgid, '||
'tab.msg_state state, '||
'tab.consumer_name con_name, '||
'tab.queue queue_name, '||
'tab.exception_queue ex_queue_name, '||
'tab.retry_count, '||
'tab.user_data.event_name ev_name, '||
'tab.user_data.event_key ev_key, '||
'tab.enq_time, '||
'tab.deq_time, '||
'tab.user_data.error_message err_msg, '||
'tab.user_data.error_stack err_stack '||
'from '||g_qowner||'.aq$'||p_queue_name||' tab '||
'where tab.user_data.event_name like :p1 '||
'and tab.user_data.event_key like :p2';
l_cells.DELETE;
l_cells.DELETE;
open c_jmsq for 'select msg_id, corr_id, msg_state, consumer_name, queue, exception_queue, '||
' retry_count, enq_time, deq_time, user_data'||
' from '||g_qowner||'.aq$'||p_queue_name ||
' order by enq_time desc';
l_cells.DELETE;
SELECT installed_flag
INTO l_install
FROM wf_languages
WHERE nls_language = p_language
AND installed_flag = 'Y';
l_cells.DELETE;
select status, mail_status, message_type, message_name, recipient_role
into l_status, l_mstatus, l_msg_type, l_msg_name, l_recip_role
from wf_notifications
where notification_id = p_nid;
l_cells.DELETE;
select installed_flag
into l_installed
from wf_languages
where nls_language = l_nls_lang;
select subject, body, html_body
into l_subj, l_txt_body, l_htm_body
from wf_messages_vl
where name = l_tname
and type = l_ttype;
l_cells.DELETE;
select subject, body, html_body
into l_subj, l_txt_body, l_htm_body
from wf_messages_vl
where name = l_msg_name
and type = l_msg_type;
l_cells.DELETE;
l_cells.DELETE;
SELECT component_id
FROM FND_SVC_COMPONENTS
WHERE component_type = 'WF_MAILER'
order by DECODE(component_status, 'RUNNING', 1, 'NOT_CONFIGURED', 3, 2) ASC ;
SELECT a.parameter_value into l_summary_param
FROM fnd_svc_comp_param_vals a,
fnd_svc_components b,
fnd_svc_comp_params_vl c
WHERE b.component_id = a.component_id
AND b.component_type = c.component_type
AND c.parameter_id = a.parameter_id
AND c.encrypted_flag = 'N'
AND b.component_id = l_component_id
AND c.parameter_name in ('SUMHTML' );
SELECT a.parameter_value into l_summary_param
FROM fnd_svc_comp_param_vals a,
fnd_svc_components b,
fnd_svc_comp_params_vl c
WHERE b.component_id = a.component_id
AND b.component_type = c.component_type
AND c.parameter_id = a.parameter_id
AND c.encrypted_flag = 'N'
AND b.component_id = l_component_id
AND c.parameter_name in ('SUMMARY' );
select installed_flag
into l_installed
from wf_languages
where nls_language = l_user_lang;
select subject, body, html_body
into l_subj, l_txt_body, l_htm_body
from wf_messages_vl
where name = l_tname
and type = l_ttype;
l_cells.DELETE;
select subject, body, html_body
into l_subj, l_txt_body, l_htm_body
from wf_messages_vl
where name = l_tname
and type = l_ttype;
l_cells.DELETE;
l_cells.DELETE;
l_cells.DELETE;
select recipient_role, message_type
into l_role, l_msg_type
from wf_notifications
where notification_id = p_nid;
l_cells.DELETE;
l_cells.DELETE;
l_cells.DELETE;
select component_id,
component_name,
component_status,
startup_mode,
container_type,
inbound_agent_name,
outbound_agent_name,
correlation_id
from fnd_svc_components
where component_type = p_comp_type
and component_name like nvl(p_comp_name, '%');
select p.parameter_name,
v.parameter_value,
v.parameter_description,
v.default_parameter_value
from fnd_svc_comp_param_vals_v v,
fnd_svc_comp_params_b p
where p.encrypted_flag = 'N'
and v.component_id = p_comp_id
and v.parameter_id = p.parameter_id
order by p.parameter_name;
l_cells.DELETE;
l_cells.DELETE;
l_cells.DELETE;
l_cells.DELETE;
select component_id,
component_name,
component_status,
startup_mode,
container_type,
inbound_agent_name,
outbound_agent_name,
correlation_id
from fnd_svc_components
where component_type = p_comp_type
and component_name like nvl(p_comp_name, '%')
order by DECODE(component_status, 'RUNNING', 1, 'NOT_CONFIGURED', 3, 2) ASC ;
SELECT component_request_id,
job_id,
event_name,
event_params,
event_date,
event_frequency,
requested_by_user,
b.what,
b.last_Date,
b.last_sec
from fnd_svc_comp_requests a,
user_jobs b
WHERE a.component_id = p_comp_id
AND a.job_id = b.job;
l_cells.DELETE;
l_cells.DELETE;
l_cells.DELETE;
l_cells.DELETE;
select profile_option_name, profile_option_value
from fnd_profile_options a, fnd_profile_option_values b
where a.application_id = b.application_id and
a.profile_option_id = b.profile_option_id and
a.profile_option_name in ('APPS_FRAMEWORK_AGENT', 'WF_MAIL_WEB_AGENT',
'AMPOOL_ENABLED', 'ICX_LIMIT_TIME',
'ICX_LIMIT_CONNECT', 'ICX_SESSION_TIMEOUT',
'FRAMEWORK_URL_TIMEOUT')
and b.level_value = 0;
l_cells.DELETE;
l_cells.DELETE;
l_cells.DELETE;
select code,
override_email_charset
from wf_override_charsets;
l_cells.DELETE;
l_cells.DELETE;
l_cells.DELETE;
select name,
tag_id,
action,
pattern,
allow_reload
from wf_mailer_tags
order by name;
l_cells.DELETE;
l_cells.DELETE;
l_cells.DELETE;
l_cells.DELETE;
select nout.user_data.text_lob lob
from wf_notification_out nout
where instr(nout.user_data.get_string_property('BES_EVENT_KEY'), p_nid) > 0
and (p_corr_id is null or nout.corrid like p_corr_id)
order by ENQ_TIME;
l_cells.DELETE;
l_cells.DELETE;
l_sql_str := ' select tab.user_data.text_lob '||
' from '||g_qowner||'.aq$wf_notification_out tab' ||
' where instr(tab.user_data.get_string_property(''ROLE_NAME''), :p1) > 0 ' ||
' order by tab.enq_time desc';
select nin.user_data.text_lob lob
from wf_notification_in nin
where instr(nin.user_data.get_string_property('BES_EVENT_KEY'), p_nid) > 0
and (p_corr_id is null or upper(nin.corrid) like upper(p_corr_id))
order by ENQ_TIME;
l_cells.DELETE;
select wfn.notification_id nid, to_char(begin_date, 'DD-MON-YYYY HH:MI:SS') dt, subject ntf_sub
from wf_notifications wfn, wf_notification_attributes wfa, wf_notification_attributes wfna
where wfn.notification_id = wfa.notification_id and wfn.notification_id = wfna.notification_id
and wfn.message_type = 'WFERROR'
and wfn.message_name in ('DEFAULT_EVENT_ERROR' , 'DEFAULT_EVENT_EXT_ERROR')
and wfa.name = 'EVENT_NAME'
and wfa.text_value like p_event_name
and wfna.name = 'EVENT_KEY'
and wfna.text_value = p_event_key
order by 1;
select decode(name, 'ERROR_NAME', 'Error', 'ERROR_MESSAGE', 'Error Message',
'ERROR_STACK', 'Error Stack', name) name, text_value value
from wf_notification_attributes
where notification_id = p_nid
and name in ('ERROR_NAME', 'ERROR_MESSAGE', 'ERROR_STACK');
l_cells.DELETE;
SELECT wn.notification_id, to_char(wn.begin_date, 'DD-MON-YYYY HH:MI:SS'), subject,
error_message, error_name, error_stack
INTO l_nid, l_date, l_subject, l_err_msg, l_err_name, l_err_stack
FROM wf_notifications wn,
wf_item_activity_statuses wias
WHERE wn.notification_id = l_ntf_id
AND wias.notification_id = l_ntf_id
AND wn.message_type = wias.item_type
AND wn.item_key = wias.item_key;
l_cells.DELETE;
l_cells.DELETE;
select null
into l_dummy
from wf_notifications
where notification_id = p_nid;
l_cells.DELETE;
select count(1)
into l_group_cnt
from WF_NOTIFICATIONS
where GROUP_ID=p_nid;
select count(1) into job_count
from user_jobs
where upper(what) like '%WF_BES_CLEANUP.CLEANUP_SUBSCRIBERS%'
and broken = 'N';
execute immediate 'SELECT COUNT(1) FROM '||g_qowner||'.AQ$WF_CONTROL_S' INTO l_subscriber_number;
execute immediate 'SELECT COUNT(1) '||
'FROM WF_BES_SUBSCRIBER_PINGS wbsp, '||g_qowner||'.AQ$WF_CONTROL_S sub '||
'WHERE sub.name = wbsp.subscriber_name '||
'AND sub.queue = wbsp.queue_name '||
'AND wbsp.queue_name = ''WF_CONTROL'' '||
'AND wbsp.status IN (''REMOVE_FAILED'', ''PINGED'') '||
'AND wbsp.ping_time < SYSDATE - 1/48'
INTO l_dead_subscriber;
'SELECT wbsp.subscriber_name ' ||
'FROM WF_BES_SUBSCRIBER_PINGS wbsp, '||g_qowner||'.AQ$WF_CONTROL_S sub ' ||
'WHERE sub.name = wbsp.subscriber_name '||
'AND sub.queue = wbsp.queue_name '||
'AND wbsp.queue_name = ''WF_CONTROL'' '||
'AND wbsp.status IN (''REMOVE_FAILED'', ''PINGED'') '||
'AND wbsp.ping_time < SYSDATE - 1/48';
SELECT ' ' || a.NAME || ' ' agent_name ,
' ' || s.NAME || ' ' sys_name,
' ' || a.status ||'
SELECT e.name EVENT_NAME,
DECODE(sub.guid, NULL, 'Subscription Not Defined',
DECODE(sub.rule_function, NULL, 'Not Defined',
sub.rule_function || '@' || s.name)) RULE_FUNCTION,
DECODE(sub.guid, NULL, 'Subscription Not Defined',
DECODE(sub.out_agent_guid, NULL, 'Not Defined',
oa.name || '@' || oas.name)) OUT_AGENT,
sub.status STATUS
FROM WF_EVENTS e, WF_SYSTEMS s, WF_EVENT_SUBSCRIPTIONS sub, WF_AGENTS oa, WF_SYSTEMS oas
WHERE e.NAME IN ('oracle.apps.wf.notification.send.group',
'oracle.apps.fnd.cp.gsc.bes.control.group',
'oracle.apps.wf.notification.summary.send')
AND e.guid = sub.event_filter_guid(+)
AND sub.licensed_flag(+) = 'Y'
AND e.licensed_flag = 'Y'
AND sub.system_guid = s.guid(+)
AND oa.guid(+) = sub.out_agent_guid
AND oa.system_guid = oas.guid(+)
ORDER BY e.name;
SELECT WF_EVENT.LOCAL_SYSTEM_NAME INTO l_sys_name FROM dual;
SELECT uo.object_name name,
uo.object_type type,
uo.status status
FROM user_objects uo
WHERE (uo.object_name LIKE 'WF%'
OR uo.object_name LIKE 'ECX%'
OR uo.object_name LIKE 'FND_SVC_%')
AND uo.object_type IN ('PACKAGE', 'PACKAGE BODY')
AND uo.status <> 'VALID'
ORDER BY 1, 2;
l_cells.DELETE;
l_cells.DELETE;
l_cells.DELETE;
l_cells.DELETE;
SELECT status, owner
FROM all_objects
WHERE object_name = 'XMLDOM'
AND object_type = 'PACKAGE'
AND (owner in ('SYS', 'SYSTEM') OR owner = g_qowner)
UNION
SELECT status, user
FROM user_objects
WHERE object_name = 'XMLDOM'
AND object_type = 'PACKAGE';
SELECT object_name, status, owner
FROM all_objects
WHERE object_type = 'JAVA RESOURCE'
AND object_name like '%xmlparser%'
AND (owner in ('SYS', 'SYSTEM') OR owner = g_qowner)
UNION
SELECT object_name, status, user
FROM user_objects
WHERE object_type = 'JAVA RESOURCE'
AND object_name like '%xmlparser%';
l_cells.DELETE;
l_cells.DELETE;
l_cells.DELETE;
l_cells.DELETE;
SELECT name, queue_name, status
FROM wf_agents
WHERE (name like 'WF%'
OR name like 'ECX%');
SELECT aq.enqueue_enabled, aq.dequeue_enabled, db1.status queue_status, db2.status table_status
FROM all_queues aq, dba_objects db1, dba_objects db2
WHERE db1.object_name = l_queue_name
AND db1.owner = l_owner
AND db1.object_type = 'QUEUE'
AND aq.name = l_queue_name
AND aq.owner = l_owner
AND db2.object_name = aq.queue_table
AND db2.object_type = 'TABLE'
AND db2.owner = l_owner;
SELECT version
INTO l_dbver
FROM v$instance;
l_cells.DELETE;
l_cells.DELETE;
open c_subs for 'SELECT s.queue_name, s.name, s.address, s.protocol '||
'FROM '||l_owner||'.aq$_'||l_queue_name||'_s s';
l_cells.DELETE;
l_cells.DELETE;
open c_rules for 'SELECT name, rule '||
'FROM '||l_owner||'.aq$'||l_queue_name||'_r';
open c_rules for 'SELECT s.name name, rule_condition rule '||
'FROM '||l_owner||'.aq$_'||l_queue_name||'_s s, dba_rules r '||
'WHERE (bitand(s.subscriber_type, 1) = 1) '||
'AND s.rule_name = r.rule_name '||
'AND r.rule_owner = :1' using l_owner;
l_cells.DELETE;
l_cells.DELETE;
l_cells.DELETE;
select text into l_result from wf_resources where name='WF_SYSTEM_STATUS' and language='US';
select text into l_result from wf_resources where name='WF_SYSTEM_GUID' and language='US';
select name into l_result from wf_systems where guid=l_cells(2);
l_cells.DELETE;
l_cells.DELETE;
select type, status, owner_name, owner_tag,
generate_function gen_func,
java_generate_func jgen_func,
customization_level cust_level,
licensed_flag lic_flag
into l_type, l_status, l_own_name, l_own_tag,
l_gen_func, l_jgen_func, l_cust_level, l_lic_flag
from wf_events
where name = p_event_name;
l_cells.DELETE;
select display_name into l_agent_tmp from wf_agents where guid = l_subs_list(i).SOURCE_AGENT_GUID;
select display_name into l_agent_tmp from wf_agents where guid = l_subs_list(i).OUT_AGENT_GUID;
select display_name into l_agent_tmp from wf_agents where guid = l_subs_list(i).TO_AGENT_GUID;
SELECT subscription_source_type source_type,
ac.display_name source_agent,
subscription_phase phase,
subscription_rule_data rule_data,
aa.display_name out_agent,
ab.display_name to_agent,
subscription_rule_function rule_function,
wf_process_type wf_process_type,
wf_process_name wf_process_name,
subscription_parameters parameters,
subscription_on_error_type error_type
FROM wf_active_subscriptions_v wfact, wf_agents aa,
wf_agents ab, wf_agents ac
WHERE event_name = p_event_name
and wfact.subscription_out_agent_guid = aa.guid(+)
and wfact.subscription_to_agent_guid = ab.guid(+)
and wfact.subscription_source_agent_guid = ac.guid(+);
l_cells.DELETE;
select component_name comp_name, correlation_id corrid,
inbound_agent_name inbound_agent,
initcap(decode(FND_SVC_COMPONENT.Get_Component_Status(component_name),
'NOT_CONFIGURED', 'Not Configured',
'STOPPED_ERROR', 'Stopped with Error',
'DEACTIVATED_USER', 'User Deactivated',
FND_SVC_COMPONENT.Get_Component_Status(component_name))) status,
component_status_info info
FROM fnd_svc_components_v
WHERE component_type in ('WF_AGENT_LISTENER', 'WF_JAVA_AGENT_LISTENER');
l_cells.DELETE;
select null
into l_dummy
from wf_events
where name = p_event_name;
l_cells.DELETE;
l_cells.DELETE;
select user into l_user from dual;