[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
TYPE rule_select_rec is record
( enable_routine FND_DEBUG_OPTION_VALUES.enable_routine%TYPE,
disable_routine FND_DEBUG_OPTION_VALUES.disable_routine%TYPE,
debug_option_name FND_DEBUG_RULE_OPTIONS.debug_option_name%TYPE,
debug_option_value FND_DEBUG_RULE_OPTIONS.debug_option_value%TYPE,
debug_rule_id FND_DEBUG_RULES.debug_rule_id%TYPE,
repeation_counter FND_DEBUG_RULES.repeation_counter%TYPE,
start_time FND_DEBUG_RULES.start_time%TYPE,
end_time FND_DEBUG_RULES.end_time%TYPE,
user_id FND_DEBUG_RULES.user_id%TYPE,
responsibility_id FND_DEBUG_RULES.responsibility_id%TYPE,
resp_appl_id FND_DEBUG_RULES.resp_appl_id%TYPE,
component_type FND_DEBUG_RULES.component_type%TYPE,
component_name FND_DEBUG_RULES.component_name%TYPE,
component_id FND_DEBUG_RULES.component_id%TYPE,
component_appl_id FND_DEBUG_RULES.component_appl_id%TYPE,
trace_file_routine FND_DEBUG_OPTION_VALUES.trace_file_routine%TYPE,
trace_file_node FND_DEBUG_OPTION_VALUES.trace_file_node%TYPE,
comments FND_DEBUG_RULES.comments%TYPE,
reqid FND_DEBUG_RULES.request_id%TYPE
);
select enable_routine, disable_routine, DRO.debug_option_name,
DRO.debug_option_value, DR.debug_rule_id, repeation_counter, start_time,
end_time, user_id, responsibility_id, resp_appl_id, component_type,
component_name, component_id, component_appl_id,
trace_file_routine, trace_file_node, comments, nvl(request_id,0) reqid
from fnd_debug_options DO,
fnd_debug_rules DR,
fnd_debug_option_values DOV,
fnd_debug_rule_options DRO
where ( DR.user_id = userid
OR (DR.responsibility_id = respid
and DR.Resp_appl_id = respapplid )
OR (DR.user_id is null and DR.Responsibility_ID is null) )
AND (( sysdate >= DR.Start_time and sysdate <= DR.end_time)
or DR.repeation_counter > 0 )
AND ( DR.Component_Name = cname
or (DR.Component_id = cid
AND DR.component_appl_id = capplid ) )
AND DR.Component_type = ctype
AND DRO.debug_option_name = DOV.debug_option_name
AND DRO.debug_option_value = DOV.debug_option_value
AND DRO.debug_option_name = DO.debug_option_name
AND DO.type = 'D'
AND DO.enabled_flag = 'Y'
AND ((DR.request_id is not null AND reqid = DR.request_id) OR (DR.request_id is null) )
AND DR.debug_rule_id = DRO.debug_rule_id
AND DR.debug_rule_id=(SELECT min(debug_rule_id) FROM fnd_debug_rules IDR
WHERE (IDR.user_id = userid
OR (IDR.responsibility_id = respid
AND IDR.Resp_appl_id = respapplid )
OR (IDR.user_id IS NULL AND IDR.Responsibility_ID IS NULL) )
AND (( sysdate >= IDR.Start_time and sysdate <= IDR.end_time)
OR IDR.repeation_counter > 0 )
AND ( IDR.Component_Name = cname
OR (IDR.Component_id = cid
AND IDR.component_appl_id = capplid ) )
AND IDR.Component_type = ctype
AND ((IDR.request_id IS NOT NULL
AND reqid = IDR.request_id) OR (IDR.request_id IS NULL) )
) -- Fix for Bug 3960063,Earliest rule is selected
order by reqid desc, DR.creation_date, DR.debug_rule_id
/* sorted by oldest rule based on creation such that same rule id are contiguous*/;
select enable_routine, disable_routine, DRO.debug_option_name,
DRO.debug_option_value, DR.debug_rule_id, repeation_counter, start_time,
end_time, user_id, responsibility_id, resp_appl_id, component_type,
component_name, component_id, component_appl_id,
trace_file_routine, trace_file_node, comments, nvl(request_id,0) reqid
from fnd_debug_options DO,
fnd_debug_rules DR,
fnd_debug_option_values DOV,
fnd_debug_rule_options DRO
where ( DR.user_id = userid
OR (DR.responsibility_id = respid
and DR.Resp_appl_id = respapplid )
OR (DR.user_id is null and DR.Responsibility_ID is null) )
AND (( sysdate >= DR.Start_time and sysdate <= DR.end_time)
or DR.repeation_counter > 0 )
AND DR.Component_Name = cname
AND DR.Component_type = ctype
AND DRO.debug_option_name = DOV.debug_option_name
AND DRO.debug_option_value = DOV.debug_option_value
AND DRO.debug_option_name = DO.debug_option_name
AND DO.type = 'D'
AND DO.enabled_flag = 'Y'
AND DR.request_id is null
AND DR.debug_rule_id = DRO.debug_rule_id
AND DR.debug_rule_id=(SELECT min(debug_rule_id) FROM fnd_debug_rules IDR
WHERE (IDR.user_id = userid
OR (IDR.responsibility_id = respid
AND IDR.Resp_appl_id = respapplid )
OR (IDR.user_id IS NULL AND IDR.Responsibility_ID IS NULL) )
AND (( sysdate >= IDR.Start_time and sysdate <= IDR.end_time)
OR IDR.repeation_counter > 0 )
AND IDR.Component_Name = cname
AND IDR.Component_type = ctype
AND IDR.request_id IS NULL
) -- Fix for Bug 3960063,Earliest rule is selected
order by reqid desc, DR.creation_date , DR.debug_rule_id
/* sorted by oldest rule based on creation such that same rule id are contiguous */;
dr_rec rule_select_rec;
SELECT NVL(MAX(DRO.repeation_counter),0) INTO repetition_counter
FROM fnd_debug_rule_executions DRO,
fnd_debug_option_values DOV
WHERE DRO.debug_option_name=DOV.debug_option_name
AND DRO.debug_option_value=DOV.debug_option_value
AND DOV.debug_option_name=dr_rec.debug_option_name
AND DOV.debug_option_value=dr_rec.debug_option_value
AND DRO.rule_id=dr_rec.debug_rule_id;
insert into fnd_debug_rule_executions
(transaction_id, rule_id, component_type, component_name,
component_id, component_appl_id, start_time, end_time,
repeation_counter, debug_log_file, log_file_node_name,
user_id, responsibility_id, resp_appl_id,
debug_option_name, debug_option_value, creation_date,
created_by, last_update_date, last_updated_by,
last_update_login, comments, request_id)
values (trans_id, dr_rec.debug_rule_id, dr_rec.component_type,
dr_rec.component_name, dr_rec.component_id,
dr_rec.component_appl_id, dr_rec.start_time,
dr_rec.end_time, repetition_counter+1, log_file, -- fix for bug 3787995
node_name, dr_rec.user_Id, dr_rec.responsibility_id,
dr_rec.resp_appl_id, dr_rec.debug_option_name,
dr_rec.debug_option_value, sysdate, userid,
sysdate, userid, loginid,
dr_rec.comments, req_id); --Added for Bug 3788285.For showing request_id
UPDATE fnd_debug_rules
SET repeation_counter = repeation_counter -1,
last_update_date = sysdate
WHERE debug_rule_id = dr_rec.debug_rule_id;
UPDATE fnd_debug_rules
SET repeation_counter = 0,
last_update_date = sysdate
WHERE debug_rule_id = dr_rec.debug_rule_id;
DELETE FROM fnd_debug_rule_options WHERE debug_rule_id IN
(SELECT debug_rule_id FROM fnd_debug_rules
WHERE (start_time IS NOT NULL AND end_time < sysdate)
OR (repeation_counter = 0)
);
DELETE FROM fnd_debug_rules
WHERE (start_time IS NOT NULL AND end_time < sysdate)
OR (repeation_counter = 0);
select * from (
select DRO.debug_option_name, DRO.debug_option_value, separator,
trace_file_token, DR.debug_rule_id, repeation_counter, start_time,
end_time, user_id, responsibility_id, resp_appl_id,
component_type, component_name, component_id, component_appl_id,
trace_file_routine, trace_file_node, comments,
nvl(request_id,0) reqid
from fnd_debug_options DO,
fnd_debug_rules DR,
fnd_debug_option_values DOV,
fnd_debug_rule_options DRO
where ( DR.user_id = uid
OR (DR.responsibility_id = respid
and DR.Resp_appl_id = respapplid )
OR (DR.user_id is null and DR.Responsibility_ID is null))
AND (( sysdate >= DR.Start_time and sysdate <= DR.end_time )
or DR.repeation_counter > 0 )
AND ( DR.Component_Name = cname
or (DR.Component_id = cid
AND DR.component_appl_id = capplid ) )
AND DR.Component_type = ctype
AND DRO.debug_option_name = DOV.debug_option_name
AND DRO.debug_option_value = DOV.debug_option_value
AND DRO.debug_option_name = DO.debug_option_name
AND DO.type = 'O'
AND DO.enabled_flag = 'Y'
AND ((DR.request_id is not null AND reqid = DR.request_id) OR (DR.request_id is null) )
AND DR.debug_rule_id = DRO.debug_rule_id
order by reqid desc, DR.creation_date )
where rownum =1;
select debug_option_value
into t_ftoken
from fnd_debug_option_values
where debug_option_name = dr_rec.debug_option_name
and is_file_token = 'Y';
SELECT NVL(MAX(DRO.repeation_counter),0) INTO repetition_counter
FROM fnd_debug_rule_executions DRO,
fnd_debug_option_values DOV
WHERE DRO.debug_option_name=DOV.debug_option_name
AND DRO.debug_option_value=DOV.debug_option_value
AND DOV.debug_option_name=dr_rec.debug_option_name
AND DOV.debug_option_value=dr_rec.debug_option_value
AND DRO.rule_id=dr_rec.debug_rule_id;
insert into fnd_debug_rule_executions
(transaction_id, rule_id, component_type, component_name,
component_id, component_appl_id, start_time, end_time,
repeation_counter, debug_log_file, log_file_node_name,
user_id, responsibility_id, resp_appl_id,
debug_option_name, debug_option_value, creation_date,
created_by, last_update_date, last_updated_by,
last_update_login, comments, request_id)
values (trans_id, dr_rec.debug_rule_id, dr_rec.component_type,
dr_rec.component_name, dr_rec.component_id,
dr_rec.component_appl_id, dr_rec.start_time,
dr_rec.end_time, repetition_counter+1, log_file, -- fix for bug 3787995
node_name, dr_rec.user_Id, dr_rec.responsibility_id,
dr_rec.resp_appl_id, dr_rec.debug_option_name,
dr_rec.debug_option_value, sysdate, fnd_global.user_id,
sysdate, fnd_global.user_id, fnd_global.login_id,
dr_rec.comments, comp_inst_id); --Added for Bug 3788285.For showing request_id
SELECT 'T' INTO db_rule_enabled
FROM DUAL
WHERE EXISTS(
SELECT *
FROM fnd_debug_rule_options DRO,
fnd_debug_options DO
WHERE DRO.debug_option_name=DO.debug_option_name
AND DO.type='D'
AND dr_rec.debug_rule_id=DRO.debug_rule_id);
UPDATE fnd_debug_rules
SET repeation_counter = repeation_counter -1,
last_update_date = sysdate
WHERE debug_rule_id = dr_rec.debug_rule_id;
UPDATE fnd_debug_rules
SET repeation_counter = 0,
last_update_date = sysdate
WHERE debug_rule_id = dr_rec.debug_rule_id;
DELETE FROM fnd_debug_rule_options WHERE debug_rule_id IN
(SELECT debug_rule_id
FROM fnd_debug_rules
WHERE (start_time IS NOT NULL AND end_time < sysdate)
OR (repeation_counter = 0)
);
DELETE FROM fnd_debug_rules
WHERE (start_time IS NOT NULL AND end_time < sysdate)
OR (repeation_counter = 0);
update fnd_debug_rule_executions
set log_request_id = assign_request.request_id
where transaction_id = assign_request.transaction_id
and debug_option_value = 'PLSQL_PROFILER';