1 package body FND_DEBUG as
2 /* $Header: AFCPDWBB.pls 120.2 2005/09/30 10:42:13 rckalyan ship $ */
3
4 --
5 -- PUBLIC VARIABLES
6 --
7 TYPE rules_rec_type is record
8 (debug_option varchar2(30),
9 -- option_value varchar2(80),
10 disable_routine varchar2(500));
11
12 TYPE rules_tab_type is table of rules_rec_type
13 index by binary_integer;
14
15 P_RULES rules_tab_type;
16 RULEC number := 0;
17
18 TYPE rule_select_rec is record
19 ( enable_routine FND_DEBUG_OPTION_VALUES.enable_routine%TYPE,
20 disable_routine FND_DEBUG_OPTION_VALUES.disable_routine%TYPE,
21 debug_option_name FND_DEBUG_RULE_OPTIONS.debug_option_name%TYPE,
22 debug_option_value FND_DEBUG_RULE_OPTIONS.debug_option_value%TYPE,
23 debug_rule_id FND_DEBUG_RULES.debug_rule_id%TYPE,
24 repeation_counter FND_DEBUG_RULES.repeation_counter%TYPE,
25 start_time FND_DEBUG_RULES.start_time%TYPE,
26 end_time FND_DEBUG_RULES.end_time%TYPE,
27 user_id FND_DEBUG_RULES.user_id%TYPE,
28 responsibility_id FND_DEBUG_RULES.responsibility_id%TYPE,
29 resp_appl_id FND_DEBUG_RULES.resp_appl_id%TYPE,
30 component_type FND_DEBUG_RULES.component_type%TYPE,
31 component_name FND_DEBUG_RULES.component_name%TYPE,
32 component_id FND_DEBUG_RULES.component_id%TYPE,
33 component_appl_id FND_DEBUG_RULES.component_appl_id%TYPE,
34 trace_file_routine FND_DEBUG_OPTION_VALUES.trace_file_routine%TYPE,
35 trace_file_node FND_DEBUG_OPTION_VALUES.trace_file_node%TYPE,
36 comments FND_DEBUG_RULES.comments%TYPE,
37 reqid FND_DEBUG_RULES.request_id%TYPE
38 );
39
40 -- Exceptions
41
42 -- Exception Pragmas
43
44 --
45 -- PUBLIC FUNCTIONS
46 --
47
48 --
49 -- Name
50 -- enable_db_rules
51 -- Purpose
52 -- Based on debug rules currently active for the user / responsibility
53 -- it will execute the matching rules accordingly
54 -- return string which will contain the debug string for the
55 -- component instance to use.
56 --
57 -- return true if atleast one rule is executed in this call
58 -- other wise returns false (if no rule is executed)
59 /*
60 some examples to call this API
61 1. To execute all rules associated with this component having different debug options
62 enable_db_rules(FND_DEBUG.FORM, 'FNDRSRUN');
63
64 2. To execute all rules associated with this component id and component application id
65 having different debug options (one of the component name or comp id and comp appl id is required)
66 enable_db_rules(FND_DEBUG.REPORT, null, 1,2);
67
68 3. To execute rules specific to a request id having different debug options
69 enable_db_rules(FND_DEBUG.REPORT, 'FNDSCURS', null, null, 12345);
70
71 */
72 FUNCTION enable_db_rules (comp_type in varchar2,
73 comp_name in varchar2,
74 comp_appl_id in number default null,
75 comp_id in number default null,
76 req_id in number default null
77 ) RETURN BOOLEAN IS
78
79 PRAGMA AUTONOMOUS_TRANSACTION;
80 CURSOR DRC_REQ (ctype varchar2, cname varchar2, capplid number,
81 cid number, userid number, respid number,
82 respapplid number, reqid number) is
83 select enable_routine, disable_routine, DRO.debug_option_name,
84 DRO.debug_option_value, DR.debug_rule_id, repeation_counter, start_time,
85 end_time, user_id, responsibility_id, resp_appl_id, component_type,
86 component_name, component_id, component_appl_id,
87 trace_file_routine, trace_file_node, comments, nvl(request_id,0) reqid
88 from fnd_debug_options DO,
89 fnd_debug_rules DR,
90 fnd_debug_option_values DOV,
91 fnd_debug_rule_options DRO
92 where ( DR.user_id = userid
93 OR (DR.responsibility_id = respid
94 and DR.Resp_appl_id = respapplid )
95 OR (DR.user_id is null and DR.Responsibility_ID is null) )
96 AND (( sysdate >= DR.Start_time and sysdate <= DR.end_time)
97 or DR.repeation_counter > 0 )
98 AND ( DR.Component_Name = cname
99 or (DR.Component_id = cid
100 AND DR.component_appl_id = capplid ) )
101 AND DR.Component_type = ctype
102 AND DRO.debug_option_name = DOV.debug_option_name
103 AND DRO.debug_option_value = DOV.debug_option_value
104 AND DRO.debug_option_name = DO.debug_option_name
105 AND DO.type = 'D'
106 AND DO.enabled_flag = 'Y'
107 AND ((DR.request_id is not null AND reqid = DR.request_id) OR (DR.request_id is null) )
108 AND DR.debug_rule_id = DRO.debug_rule_id
109 AND DR.debug_rule_id=(SELECT min(debug_rule_id) FROM fnd_debug_rules IDR
110 WHERE (IDR.user_id = userid
111 OR (IDR.responsibility_id = respid
112 AND IDR.Resp_appl_id = respapplid )
113 OR (IDR.user_id IS NULL AND IDR.Responsibility_ID IS NULL) )
114 AND (( sysdate >= IDR.Start_time and sysdate <= IDR.end_time)
115 OR IDR.repeation_counter > 0 )
116 AND ( IDR.Component_Name = cname
117 OR (IDR.Component_id = cid
118 AND IDR.component_appl_id = capplid ) )
119 AND IDR.Component_type = ctype
120 AND ((IDR.request_id IS NOT NULL
121 AND reqid = IDR.request_id) OR (IDR.request_id IS NULL) )
122
123
124 ) -- Fix for Bug 3960063,Earliest rule is selected
125 order by reqid desc, DR.creation_date, DR.debug_rule_id
126 /* sorted by oldest rule based on creation such that same rule id are contiguous*/;
127
128 CURSOR DRC_NON_REQ (ctype varchar2, cname varchar2, capplid number,
129 cid number, userid number, respid number,
130 respapplid number) is
131 select enable_routine, disable_routine, DRO.debug_option_name,
132 DRO.debug_option_value, DR.debug_rule_id, repeation_counter, start_time,
133 end_time, user_id, responsibility_id, resp_appl_id, component_type,
134 component_name, component_id, component_appl_id,
135 trace_file_routine, trace_file_node, comments, nvl(request_id,0) reqid
136 from fnd_debug_options DO,
137 fnd_debug_rules DR,
138 fnd_debug_option_values DOV,
139 fnd_debug_rule_options DRO
140 where ( DR.user_id = userid
141 OR (DR.responsibility_id = respid
142 and DR.Resp_appl_id = respapplid )
143 OR (DR.user_id is null and DR.Responsibility_ID is null) )
144 AND (( sysdate >= DR.Start_time and sysdate <= DR.end_time)
145 or DR.repeation_counter > 0 )
146 AND DR.Component_Name = cname
147 AND DR.Component_type = ctype
148 AND DRO.debug_option_name = DOV.debug_option_name
149 AND DRO.debug_option_value = DOV.debug_option_value
150 AND DRO.debug_option_name = DO.debug_option_name
151 AND DO.type = 'D'
152 AND DO.enabled_flag = 'Y'
153 AND DR.request_id is null
154 AND DR.debug_rule_id = DRO.debug_rule_id
155 AND DR.debug_rule_id=(SELECT min(debug_rule_id) FROM fnd_debug_rules IDR
156 WHERE (IDR.user_id = userid
157 OR (IDR.responsibility_id = respid
158 AND IDR.Resp_appl_id = respapplid )
159 OR (IDR.user_id IS NULL AND IDR.Responsibility_ID IS NULL) )
160 AND (( sysdate >= IDR.Start_time and sysdate <= IDR.end_time)
161 OR IDR.repeation_counter > 0 )
162 AND IDR.Component_Name = cname
163 AND IDR.Component_type = ctype
164 AND IDR.request_id IS NULL
165
166 ) -- Fix for Bug 3960063,Earliest rule is selected
167 order by reqid desc, DR.creation_date , DR.debug_rule_id
168 /* sorted by oldest rule based on creation such that same rule id are contiguous */;
169
170
171 i number;
172 -- duplicate boolean := FALSE;
173 empty_drules rules_tab_type;
174 sql_str varchar2(512);
175 trans_id number;
176 log_file varchar2(512);
177 node_name varchar2(512);
178 left_iterations number;
179 is_rule_processed boolean := FALSE; -- turned on if any rule is executed
180 userid number;
181 respid number;
182 respapplid number;
183 loginid number;
184 dr_rec rule_select_rec;
185 last_rule_id number;
186 repetition_counter FND_DEBUG_RULES.repeation_counter%TYPE := 0; -- repetition_counter is the number of actual occurances for a rule
187 begin
188
189 RULEC := 0;
190 P_RULES := empty_drules;
191 userid := FND_GLOBAL.user_id;
192 respid := FND_GLOBAL.resp_id;
193 respapplid := FND_GLOBAL.resp_appl_id;
194 loginid := FND_GLOBAL.login_id;
195 last_rule_id := 0;
196
197 -- find any debug rules available for this component instance.
198 -- if request_id is passed then use DRC_REQ else DRC_NON_REQ cursor.
199 if ( req_id is null ) then
200 OPEN DRC_NON_REQ(comp_type, comp_name, comp_appl_id, comp_id, userid,
201 respid, respapplid);
202 else
203 OPEN DRC_REQ(comp_type, comp_name, comp_appl_id, comp_id, userid,
204 respid, respapplid, req_id);
205 end if;
206
207 LOOP
208
209 if ( req_id is null ) then
210 FETCH DRC_NON_REQ INTO dr_rec;
211 EXIT when DRC_NON_REQ%NOTFOUND;
212 else
213 FETCH DRC_REQ INTO dr_rec;
214 EXIT when DRC_REQ%NOTFOUND;
215 end if;
216
217 -- check if this call to enable_db_rule is request id based
218 -- and also the fetched rule has the same request id
219 -- If fnd_debug_rule has a request_id that means rule is associated
220 -- to a specific request_id. If we find even one request_id based
221 -- rule for the current request then, we will execute that one only.
222 -- Not other non-request_id based rules.
223
224 if ( req_id is not null and dr_rec.reqid <> req_id
225 and dr_rec.reqid <> 0 ) then
226 goto end_loop;
227 end if;
228
229 /*
230 -- check this debug option with value already processed or not.
231 -- this condition is to avoid executing the same debug optiontwice.
232 -- if yes then ignore it.
233
234 ** no need to check the duplicate as we will be processing only one
235 debug rule with all of its debug options **
236 for i in 1..RULEC loop
237 if ( dr_rec.debug_option_name = P_RULES(i).debug_option ) then
238 duplicate := TRUE;
239 end if;
240 end loop;
241 */
242 -- execute all the rules with same rule id.. for the first time
243 -- last_rule_id will be 0 and be initialized to the first rule id
244 if ((dr_rec.enable_routine is not null) AND
245 (last_rule_id = 0 OR last_rule_id = dr_rec.debug_rule_id) ) then
246
247 -- last_rule_id := dr_rec.debug_rule_id;
248 is_rule_processed := TRUE;
249 -- Store this debug option with value in global to use in
250 -- disable_db_rules.
251 RULEC := RULEC + 1;
252 P_RULES(RULEC).debug_option := dr_rec.debug_option_name;
253 -- P_RULES(RULEC).option_value := dr_rec.debug_option_value;
254 P_RULES(RULEC).disable_routine := dr_rec.disable_routine;
255
256 -- Run the enable routine to enable the debugging.
257 sql_str := 'begin ' || dr_rec.enable_routine || '; end;';
258
259 execute immediate sql_str;
260
261 -- insert row about execution in fnd_debug_rule_executions
262 trans_id := get_transaction_id(FALSE);
263
264 log_file := get_ret_value(dr_rec.trace_file_routine);
265
266 node_name := get_ret_value(dr_rec.trace_file_node);
267
268 -- added repetition_counter as a fix for bug 3787995
269 -- repetition_counter is the number of actual occurances for this rule
270 SELECT NVL(MAX(DRO.repeation_counter),0) INTO repetition_counter
271 FROM fnd_debug_rule_executions DRO,
272 fnd_debug_option_values DOV
273 WHERE DRO.debug_option_name=DOV.debug_option_name
274 AND DRO.debug_option_value=DOV.debug_option_value
275 AND DOV.debug_option_name=dr_rec.debug_option_name
276 AND DOV.debug_option_value=dr_rec.debug_option_value
277 AND DRO.rule_id=dr_rec.debug_rule_id;
278
279 insert into fnd_debug_rule_executions
280 (transaction_id, rule_id, component_type, component_name,
281 component_id, component_appl_id, start_time, end_time,
282 repeation_counter, debug_log_file, log_file_node_name,
283 user_id, responsibility_id, resp_appl_id,
284 debug_option_name, debug_option_value, creation_date,
285 created_by, last_update_date, last_updated_by,
286 last_update_login, comments, request_id)
287 values (trans_id, dr_rec.debug_rule_id, dr_rec.component_type,
288 dr_rec.component_name, dr_rec.component_id,
289 dr_rec.component_appl_id, dr_rec.start_time,
290 dr_rec.end_time, repetition_counter+1, log_file, -- fix for bug 3787995
291 node_name, dr_rec.user_Id, dr_rec.responsibility_id,
292 dr_rec.resp_appl_id, dr_rec.debug_option_name,
293 dr_rec.debug_option_value, sysdate, userid,
294 sysdate, userid, loginid,
295 dr_rec.comments, req_id); --Added for Bug 3788285.For showing request_id
296
297
298 -- decrement repeation_counter if there are some more to run
299 -- else delete the row from fnd_debug_rules
300 -- If it is time based then repeation_counter will be null.
301 IF (last_rule_id <> dr_rec.debug_rule_id) THEN
302 BEGIN
303 left_iterations := NVL(dr_rec.repeation_counter, 0) - 1;
304 IF ( left_iterations >= 1 ) THEN
305 UPDATE fnd_debug_rules
306 SET repeation_counter = repeation_counter -1,
307 last_update_date = sysdate
308 WHERE debug_rule_id = dr_rec.debug_rule_id;
309 ELSIF (left_iterations = 0 ) THEN
310 BEGIN
311 UPDATE fnd_debug_rules
312 SET repeation_counter = 0,
313 last_update_date = sysdate
314 WHERE debug_rule_id = dr_rec.debug_rule_id;
315 END;
316 END IF;
317 END;
318 END IF;
319
320 last_rule_id := dr_rec.debug_rule_id;
321 end if;
322 <<end_loop>>
323 null;
324 END LOOP;
325
326 if ( req_id is null ) then
327 CLOSE DRC_NON_REQ;
328 else
332 -- delete any old rules as a fix for bug 3787995
329 CLOSE DRC_REQ;
330 end if;
331
333 DELETE FROM fnd_debug_rule_options WHERE debug_rule_id IN
334 (SELECT debug_rule_id FROM fnd_debug_rules
335 WHERE (start_time IS NOT NULL AND end_time < sysdate)
336 OR (repeation_counter = 0)
337 );
338
339 DELETE FROM fnd_debug_rules
340 WHERE (start_time IS NOT NULL AND end_time < sysdate)
341 OR (repeation_counter = 0);
342
343 commit;
344
345 return(is_rule_processed);
346
347 exception
348 when others then
349 fnd_message.set_name ('FND', 'SQL-Generic error');
350 fnd_message.set_token ('ERRNO', sqlcode, FALSE);
351 fnd_message.set_token ('REASON', sqlerrm, FALSE);
352 fnd_message.set_token ('ROUTINE', 'FND_DEBUG.ENABLE_DB_RULES', FALSE);
353 IF (fnd_log.LEVEL_EXCEPTION >= fnd_log.g_current_runtime_level) THEN
354 fnd_log.message(FND_LOG.LEVEL_EXCEPTION,
355 'fnd.plsql.FND_DEBUG.ENABLE_DB_RULES.others',
356 FALSE);
357 END IF;
358 if ( req_id is null ) then
359 CLOSE DRC_NON_REQ;
360 else
361 CLOSE DRC_REQ;
362 end if;
363
364 commit;
365 return (FALSE);
366
367 end;
368
369 --
370 -- Name
371 -- disable_db_rules
372 -- Purpose
373 -- Based on all debug rules currently active for the user / responsibility
374 -- it will disable the rules in the database session.
375 --
376 -- return true if atleast one rule is disabled
377 -- other wise returns false (if no rule is disabled)
378 function disable_db_rules return boolean is
379 sql_str varchar2(512);
380 is_rule_processed boolean := FALSE;
381 begin
382
383 -- database disable rules always executed in the same session
384 -- where ever they enabled.
385 for i in 1..RULEC loop
386 if (P_RULES(i).disable_routine is not null) then
387 is_rule_processed := TRUE;
388 -- Run the diable routine to disable debugging.
389 sql_str := 'begin ' || P_RULES(i).disable_routine || '; end;';
390
391 execute immediate sql_str;
392 end if;
393 end loop;
394
395 return is_rule_processed;
396
397 exception
398 when others then
399 fnd_message.set_name ('FND', 'SQL-Generic error');
400 fnd_message.set_token ('ERRNO', sqlcode, FALSE);
401 fnd_message.set_token ('REASON', sqlerrm, FALSE);
402 fnd_message.set_token ('ROUTINE', 'FND_DEBUG.DISABLE_DB_RULES', FALSE);
403 IF (fnd_log.LEVEL_EXCEPTION >= fnd_log.g_current_runtime_level) THEN
404 fnd_log.message(FND_LOG.LEVEL_EXCEPTION,
405 'fnd.plsql.FND_DEBUG.DISABLE_DB_RULES.others',
406 FALSE);
407 END IF;
408 return (FALSE);
409
410 end;
411
412
413 --
414 -- Name
415 -- get_os_rules
416 -- Purpose
417 -- Based on debug rules currently active for the user / responsibility
418 -- it will return debug string which contains debug string for the component
419 -- to use before running the component.
420 -- this will execute one and only one rule at a call
421 -- in case multiple rules are matching, the oldest rule will be picked
422 --
423 -- return string containing debug string for matched rule
424 /*
425 some examples to call this API
426 1. To execute rule associated with this component for a specific user
427 get_os_rules(FND_DEBUG.REPORT, 'FNDSCURS', null, null, 12345, 0, 20450,1);
428
429
430 */
431 function get_os_rules ( comp_type in varchar2,
432 comp_name in varchar2,
433 comp_appl_id in number default null,
434 comp_id in number default null,
435 comp_inst_id in number default null, /* request id */
436 user_id in number,
437 resp_appl_id in number,
438 resp_id in number
439 ) RETURN VARCHAR2 IS
440 PRAGMA AUTONOMOUS_TRANSACTION;
441
442 -- avoid using the same debug option twice, will take old one.
443 CURSOR DRC (ctype varchar2, cname varchar2,
444 capplid number, cid number,
445 uid number, respapplid number, respid number,
446 reqid number) is
447 select * from (
448 select DRO.debug_option_name, DRO.debug_option_value, separator,
449 trace_file_token, DR.debug_rule_id, repeation_counter, start_time,
450 end_time, user_id, responsibility_id, resp_appl_id,
451 component_type, component_name, component_id, component_appl_id,
452 trace_file_routine, trace_file_node, comments,
453 nvl(request_id,0) reqid
454 from fnd_debug_options DO,
455 fnd_debug_rules DR,
456 fnd_debug_option_values DOV,
457 fnd_debug_rule_options DRO
461 OR (DR.user_id is null and DR.Responsibility_ID is null))
458 where ( DR.user_id = uid
459 OR (DR.responsibility_id = respid
460 and DR.Resp_appl_id = respapplid )
462 AND (( sysdate >= DR.Start_time and sysdate <= DR.end_time )
463 or DR.repeation_counter > 0 )
464 AND ( DR.Component_Name = cname
465 or (DR.Component_id = cid
466 AND DR.component_appl_id = capplid ) )
467 AND DR.Component_type = ctype
468 AND DRO.debug_option_name = DOV.debug_option_name
469 AND DRO.debug_option_value = DOV.debug_option_value
470 AND DRO.debug_option_name = DO.debug_option_name
471 AND DO.type = 'O'
472 AND DO.enabled_flag = 'Y'
473 AND ((DR.request_id is not null AND reqid = DR.request_id) OR (DR.request_id is null) )
474 AND DR.debug_rule_id = DRO.debug_rule_id
475 order by reqid desc, DR.creation_date )
476 where rownum =1;
477
478 t_ftoken varchar(80);
479 debug_str varchar2(500);
480 trace_file varchar2(250);
481 trace_file_str varchar2(250);
482 trc_option_nmval varchar2(250);
483 left_iterations number;
484 trans_id number;
485 log_file varchar2(250);
486 node_name varchar2(250);
487 db_rule_enabled varchar2(1);
488 repetition_counter FND_DEBUG_RULES.repeation_counter%TYPE := 0;
489
490 begin
491 debug_str := '';
492 -- find any debug rules available for this component instance.
493 FOR dr_rec in drc(comp_type, comp_name, comp_appl_id, comp_id,
494 user_id, resp_appl_id, resp_id, comp_inst_id) LOOP
495
496 -- construct debug string
497 if ( dr_rec.trace_file_token is not null ) then
498
499 -- get trace file name;
500 trace_file := get_ret_value(dr_rec.trace_file_routine);
501 trace_file_str := NVL(dr_rec.separator,' ') ||
502 dr_rec.trace_file_token || '=' || trace_file;
503 else
504 -- check this debug option might have any trace file name
505 -- at option values.
506 begin
507 select debug_option_value
508 into t_ftoken
509 from fnd_debug_option_values
510 where debug_option_name = dr_rec.debug_option_name
511 and is_file_token = 'Y';
512
513 trace_file := get_ret_value(dr_rec.trace_file_routine);
514 trace_file_str := NVL(dr_rec.separator, ' ') || t_ftoken ||
515 '=' || trace_file ;
516
517 exception
518 when no_data_found then
519 null;
520 end;
521 end if;
522
523 trc_option_nmval := ' ';
524
525 if ( dr_rec.debug_option_value = 'BLANK' ) then
526 trc_option_nmval := dr_rec.debug_option_name;
527 else
528 trc_option_nmval := dr_rec.debug_option_name || '=' ||
529 dr_rec.debug_option_value;
530 end if;
531
532 debug_str := debug_str || ' ' || trc_option_nmval ||
533 NVL(dr_rec.separator,' ') || trace_file_str || ' ';
534
535 -- insert row about execution in fnd_debug_rule_executions
536 trans_id := get_transaction_id(TRUE, comp_type, comp_inst_id,
537 comp_appl_id, user_id, resp_id, resp_appl_id);
538 log_file := trace_file;
539 node_name := get_ret_value(dr_rec.trace_file_node);
540
541 -- added repetition_counter as a fix for bug 3787995
542 -- repetition_counter is the number of actual occurances for this rule
543 SELECT NVL(MAX(DRO.repeation_counter),0) INTO repetition_counter
544 FROM fnd_debug_rule_executions DRO,
545 fnd_debug_option_values DOV
546 WHERE DRO.debug_option_name=DOV.debug_option_name
547 AND DRO.debug_option_value=DOV.debug_option_value
548 AND DOV.debug_option_name=dr_rec.debug_option_name
549 AND DOV.debug_option_value=dr_rec.debug_option_value
550 AND DRO.rule_id=dr_rec.debug_rule_id;
551
552 insert into fnd_debug_rule_executions
553 (transaction_id, rule_id, component_type, component_name,
554 component_id, component_appl_id, start_time, end_time,
555 repeation_counter, debug_log_file, log_file_node_name,
556 user_id, responsibility_id, resp_appl_id,
557 debug_option_name, debug_option_value, creation_date,
558 created_by, last_update_date, last_updated_by,
559 last_update_login, comments, request_id)
560 values (trans_id, dr_rec.debug_rule_id, dr_rec.component_type,
561 dr_rec.component_name, dr_rec.component_id,
562 dr_rec.component_appl_id, dr_rec.start_time,
563 dr_rec.end_time, repetition_counter+1, log_file, -- fix for bug 3787995
564 node_name, dr_rec.user_Id, dr_rec.responsibility_id,
565 dr_rec.resp_appl_id, dr_rec.debug_option_name,
566 dr_rec.debug_option_value, sysdate, fnd_global.user_id,
567 sysdate, fnd_global.user_id, fnd_global.login_id,
568 dr_rec.comments, comp_inst_id); --Added for Bug 3788285.For showing request_id
569
570
571 -- added db_rule_enabled as a fix for bug 3787995
572 BEGIN --check whether any of the db rule is enabled or not
573 SELECT 'T' INTO db_rule_enabled
574 FROM DUAL
575 WHERE EXISTS(
576 SELECT *
577 FROM fnd_debug_rule_options DRO,
578 fnd_debug_options DO
579 WHERE DRO.debug_option_name=DO.debug_option_name
580 AND DO.type='D'
581 AND dr_rec.debug_rule_id=DRO.debug_rule_id);
582 EXCEPTION
583 WHEN NO_DATA_FOUND THEN
584 db_rule_enabled:='F';
585 END; --check whether any of the db rule is enabled or not
586
587 -- decrement repeation_counter if no db rules are enabled and
588 -- if there are some more to run
589 -- else delete the row from fnd_debug_rules
590 -- If it is time based then repeation_counter will be null.
591 -- added the check as a fix for bug 3787995
592 IF (db_rule_enabled='F') THEN --if none of db rules are enabled
593 BEGIN
594 left_iterations := nvl(dr_rec.repeation_counter,0) - 1;
598 last_update_date = sysdate
595 IF ( left_iterations >= 1 ) THEN
596 UPDATE fnd_debug_rules
597 SET repeation_counter = repeation_counter -1,
599 WHERE debug_rule_id = dr_rec.debug_rule_id;
600 ELSIF (left_iterations = 0 ) THEN
601 BEGIN
602 UPDATE fnd_debug_rules
603 SET repeation_counter = 0,
604 last_update_date = sysdate
605 WHERE debug_rule_id = dr_rec.debug_rule_id;
606 END;
607 END IF;
608 END;
609 END IF; --if none of db rules are enabled
610
611
612 END LOOP;
613
614
615 -- delete any old rules as a fix for bug 3787995
616 DELETE FROM fnd_debug_rule_options WHERE debug_rule_id IN
617 (SELECT debug_rule_id
618 FROM fnd_debug_rules
619 WHERE (start_time IS NOT NULL AND end_time < sysdate)
620 OR (repeation_counter = 0)
621 );
622
623 DELETE FROM fnd_debug_rules
624 WHERE (start_time IS NOT NULL AND end_time < sysdate)
625 OR (repeation_counter = 0);
626
627
628
629 commit;
630
631 return ltrim(rtrim(debug_str));
632
633 exception
634 when others then
635 fnd_message.set_name ('FND', 'SQL-Generic error');
636 fnd_message.set_token ('ERRNO', sqlcode, FALSE);
637 fnd_message.set_token ('REASON', sqlerrm, FALSE);
638 fnd_message.set_token (
639 'ROUTINE', 'FND_DEBUG.GET_OS_RULES', FALSE);
640 IF (fnd_log.LEVEL_EXCEPTION >= fnd_log.g_current_runtime_level) THEN
641 fnd_log.message(FND_LOG.LEVEL_EXCEPTION,
642 'fnd.plsql.FND_DEBUG.GET_OS_RULES.others', FALSE);
643 END IF;
644 commit;
645 return ltrim(rtrim(debug_str));
646 end;
647
648 --
649 -- Name
650 -- get_transaction_id
651 -- Purpose
652 -- Returns the transaction context id by calling
653 -- fnd_log_repository.init_trans_int_with_context api.
654
655 function get_transaction_id(force boolean default FALSE,
656 comp_type varchar2 default null,
657 comp_inst_id number default null,
658 comp_inst_appl_id number default null,
659 user_id number default null,
660 resp_id number default null,
661 resp_appl_id number default null
662 ) RETURN NUMBER IS
663 transaction_id number;
664 conc_request_id number;
665 form_id number;
666 form_appl_id number;
667 conc_process_id number;
668 conc_queue_id number;
669 icx_session_id number;
670
671 begin
672 -- if force is true then it will always gets the new transaction context
673 -- information, otherwise checks context already exitsts or not if
674 -- exists then use that one else create new one.
675 -- force is used in spawned concurrent request case.
676 if ( force ) then
677 if (comp_type in (FND_DEBUG.SQLPLUS_CP, FND_DEBUG.PLSQL_CP,
678 FND_DEBUG.JAVA_CP, FND_DEBUG.REPORTS ) ) then
679 conc_request_id := comp_inst_id;
680 end if;
681
682 transaction_id := fnd_log_repository.init_trans_int_with_context
683 (conc_request_id,
684 form_id,
685 form_appl_id,
686 conc_process_id,
687 conc_queue_id,
688 icx_session_id,
689 user_id,
690 resp_appl_id,
691 resp_id,
692 fnd_global.security_group_id
693 );
694
695 else
696 if ( fnd_log.g_transaction_context_id is null ) then
697 transaction_id := fnd_log_repository.init_trans_int_with_context
698 (fnd_global.conc_request_id,
699 fnd_global.form_id,
700 fnd_global.form_appl_id,
701 fnd_global.conc_process_id,
702 fnd_global.conc_queue_id,
703 fnd_global.queue_appl_id,
704 icx_sec.g_session_id,
705 fnd_global.user_id,
706 fnd_global.resp_appl_id,
707 fnd_global.resp_id,
708 fnd_global.security_group_id
709 );
710 else
711 transaction_id := fnd_log.g_transaction_context_id;
712 end if;
713 end if;
714
715 return transaction_id;
716
717 end;
718
719
720 --
721 -- Name
722 -- get_ret_value
723 -- Purpose
724 -- A utility function to execute the passed routine as string
725 --
726 -- returns string containing the result of execution of passes string.
727 function get_ret_value(t_routine varchar2) return varchar2 is
728 ret_val varchar2(500);
729 sql_str varchar2(512);
730 begin
731 if ( t_routine is null ) then
732 ret_val := '';
733 else
734 sql_str := 'begin :1 := ' || t_routine || '; end;';
735
736 execute immediate sql_str using out ret_val;
737
738 end if;
739
740 return ret_val;
741 end;
742
743 --
744 -- Name
745 -- assign_request
746 -- Purpose
747 -- It will assign specified request_id to the debug_rule_execution.
748 -- In case of PL SQL Profiling we have to submit a request to get the
749 -- output of trace information.
750 -- Arguments:
751 -- Transaction_id : transaction_id for which we need to assign the
752 -- request_id
753 -- request_id : Request_id value we need to assign.
754 procedure assign_request(transaction_id IN number,
755 request_id IN number) is
756 PRAGMA AUTONOMOUS_TRANSACTION;
757 begin
758 update fnd_debug_rule_executions
759 set log_request_id = assign_request.request_id
760 where transaction_id = assign_request.transaction_id
761 and debug_option_value = 'PLSQL_PROFILER';
762
763 commit;
764
765 exception
766 when others then
767 fnd_message.set_name ('FND', 'SQL-Generic error');
768 fnd_message.set_token ('ERRNO', sqlcode, FALSE);
769 fnd_message.set_token ('REASON', sqlerrm, FALSE);
770 fnd_message.set_token (
771 'ROUTINE', 'FND_DEBUG.ASSIGN_REQUEST', FALSE);
772 IF (fnd_log.LEVEL_EXCEPTION >= fnd_log.g_current_runtime_level) THEN
773 fnd_log.message(FND_LOG.LEVEL_EXCEPTION,
774 'fnd.plsql.FND_DEBUG.ASSIGN_REQUEST.others', FALSE);
775 END IF;
776 commit;
777
778
779 end;
780 end FND_DEBUG;
[Home] [Help]