[Home] [Help]
PACKAGE BODY: APPS.QP_DEBUG_UTIL
Source
1 PACKAGE BODY QP_DEBUG_UTIL AS
2 /* $Header: QPXDUTLB.pls 120.3.12020000.2 2013/01/31 14:03:24 jputta ship $ */
3
4 g_PkgStack t_CallStack;
5
6 g_VersionStack t_CallStack;
7
8 g_Level NUMBER;
9
10 g_indent VARCHAR2(500);
11 g_curr_parent NUMBER := 0;
12 g_granularity_level NUMBER := 0;
13
14 g_csv_count NUMBER;
15
16 TYPE file_id_list_tbl IS TABLE OF number INDEX BY VARCHAR2(500);
17 g_file_ids_tbl file_id_list_tbl;
18
19
20 PROCEDURE write_output(x_Line IN VARCHAR2)
21 IS
22 l_debug_msg VARCHAR2(2000);
23 v_MesgSize NUMBER;
24 v_Mesg VARCHAR2(32767);
25 BEGIN
26 v_MesgSize := LENGTHB(x_Line);
27
28 IF v_MesgSize > 500 THEN
29 v_Mesg := x_Line;
30 WHILE v_MesgSize > 500 loop
31 l_debug_msg := SUBSTRB(v_Mesg,1,500);
32 IF g_qp_Debug NOT IN ('P','S','M') THEN
33 l_debug_msg := TRIM(l_debug_msg);
34 ELSE
35 v_Mesg := g_indent || SUBSTRB(v_Mesg,501);
36 v_MesgSize := LENGTHB(v_Mesg);
37 END IF;
38 oe_debug_pub.add(l_debug_msg,1);
39 END LOOP;
40 IF v_MesgSize BETWEEN 1 AND 500 THEN
41 IF g_qp_Debug NOT IN ('P','S','M') THEN
42 v_Mesg := TRIM(v_Mesg);
43 END IF;
44 oe_debug_pub.add(v_Mesg,1);
45 END IF;
46 ELSE
47
48 l_debug_msg := x_line;
49 IF g_qp_Debug NOT IN ('P','S','M') THEN
50 l_debug_msg := TRIM(l_debug_msg);
51 END IF;
52 oe_debug_pub.add(l_debug_msg,1);
53 END IF;
54
55 EXCEPTION
56 WHEN OTHERS THEN
57 write_output('write_output-MSG-'||SQLERRM);
58 g_qp_Debug := 'X';
59 END write_output;
60
61 PROCEDURE tstart(x_Marker IN VARCHAR2,
62 x_Desc IN VARCHAR2 := NULL,
63 x_Accumalation IN BOOLEAN := true,
64 x_PutLine IN BOOLEAN := false)
65 IS
66 v_Position NUMBER := 0;
67 BEGIN
68
69 G_qp_debug := FND_PROFILE.VALUE('QP_DEBUG');
70 G_DEBUG := OE_DEBUG_PUB.G_DEBUG;
71 G_Debug_Level := FND_PROFILE.VALUE('ONT_DEBUG_LEVEL');
72
73 IF ISQPDebugON AND G_QP_debug = 'M' THEN
74
75 IF x_Accumalation THEN
76 FOR v_Count IN 1..g_TimeStack.COUNT LOOP
77 IF g_TimeStack(v_Count).Marker = x_Marker
78 AND g_TimeStack(v_Count).ParentId = g_curr_parent
79 THEN
80 v_Position := v_Count;
81 EXIT;
82 END IF;
83 END LOOP;
84 END IF;
85
86 IF v_Position = 0
87 --OR g_TimeStack(v_Position).ParentId <> g_curr_parent
88 OR g_TimeStack(v_Position).Deleted
89 THEN
90 v_Position := g_TimeStack.COUNT + 1;
91 g_TimeStack(v_Position).Marker := x_Marker;
92 g_TimeStack(v_Position).Description := x_Desc;
93 g_TimeStack(v_Position).TotalTime := 0;
94 g_TimeStack(v_Position).CallCount := 0;
95 g_TimeStack(v_Position).ParentId := g_curr_parent;
96 g_TimeStack(v_Position).Deleted := false;
97
98 g_curr_parent := v_Position;
99
100 END IF;
101 g_TimeStack(v_Position).Time := dbms_utility.get_time;
102 g_TimeStack(v_Position).CallCount := g_TimeStack(v_Position).CallCount + 1;
103 g_TimeStack(v_Position).IsRunning := true;
104 g_TimeStack(v_Position).putLine := x_PutLine;
105
106 END IF;
107
108 EXCEPTION
109 WHEN OTHERS THEN
110 write_output('tstart-MSG-'||SQLERRM);
111 g_qp_Debug := 'X';
112
113 END tstart;
114
115 --Summary Time Log changes (Bug# 8933551).
116 PROCEDURE tstop(x_Marker IN VARCHAR2, x_Total_Time OUT NOCOPY NUMBER)
117 IS
118 v_position NUMBER := 0;
119 -- Summary Time Log Changes
120 retValue NUMBER := 0;
121 running BOOLEAN;
122 BEGIN
123
124 IF ISQPDebugON AND G_QP_debug = 'M' THEN
125 FOR v_Count IN 1..g_TimeStack.COUNT LOOP
126 IF g_TimeStack(v_Count).Marker = x_Marker THEN
127 IF g_TimeStack(v_Count).IsRunning THEN
128 v_position := v_Count;
129 g_TimeStack(v_Count).TotalTime := g_TimeStack(v_Count).TotalTime +
130 ((( dbms_utility.get_time -
131 g_TimeStack(v_Count).Time)*10));
132 g_TimeStack(v_Count).IsRunning := false;
133 g_curr_parent := g_TimeStack(v_Count).ParentId;
134
135 retValue := g_TimeStack(v_Count).TotalTime;
136
137 IF g_curr_parent <> 0
138 AND NOT g_TimeStack(g_curr_parent).IsRunning
139 THEN
140 write_output('ERROR ** Wrongly stopped marker '||x_Marker
141 || '. Its parent marker '|| g_TimeStack(g_curr_parent).Marker
142 ||' is already stopped.');
143 END IF;
144
145 running := true;
146 EXIT;
147 ELSE
148 running := false;
149 --write_output('ERROR ** Failed to stop marker '||g_TimeStack(v_Count).Marker || '. Not in running status.');
150 END IF;
151 --EXIT;
152 END IF;
153 END LOOP;
154
155 IF NOT running THEN
156 write_output('ERROR ** Failed to stop marker '||x_Marker || '. Not in running status.');
157 END IF;
158
159 END IF;
160
161 -- Summary Time Log Changes
162 x_Total_Time := retValue;
163
164 EXCEPTION
165 WHEN OTHERS THEN
166 write_output('tstop-MSG-'||SQLERRM);
167 g_qp_Debug := 'X';
168
169 -- Summary Time Log Changes
170 x_Total_Time := 0;
171 END tstop;
172
173 PROCEDURE tstop(x_Marker IN VARCHAR2)
174 IS
175 v_position NUMBER := 0;
176 running BOOLEAN;
177 BEGIN
178
179 IF ISQPDebugON AND G_QP_debug = 'M' THEN
180 FOR v_Count IN 1..g_TimeStack.COUNT LOOP
181 IF g_TimeStack(v_Count).Marker = x_Marker THEN
182 IF g_TimeStack(v_Count).IsRunning THEN
183 v_position := v_Count;
184 g_TimeStack(v_Count).TotalTime := g_TimeStack(v_Count).TotalTime +
185 ((( dbms_utility.get_time -
186 g_TimeStack(v_Count).Time)*10));
187 g_TimeStack(v_Count).IsRunning := false;
188 g_curr_parent := g_TimeStack(v_Count).ParentId;
189
190 IF g_curr_parent <> 0
191 AND NOT g_TimeStack(g_curr_parent).IsRunning
192 THEN
193 write_output('ERROR ** Wrongly stopped marker '||x_Marker
194 || '. Its parent marker '|| g_TimeStack(g_curr_parent).Marker
195 ||' is already stopped.');
196 END IF;
197
198 running := true;
199 EXIT;
200 ELSE
201 running := false;
202 --write_output('ERROR ** Failed to stop marker '||g_TimeStack(v_Count).Marker || '. Not in running status.');
203 END IF;
204 --EXIT;
205 END IF;
206 END LOOP;
207
208 IF NOT running THEN
209 write_output('ERROR ** Failed to stop marker '||x_Marker || '. Not in running status.');
210 END IF;
211
212 END IF;
213
214 EXCEPTION
215 WHEN OTHERS THEN
216 write_output('tstop-MSG-'||SQLERRM);
217 g_qp_Debug := 'X';
218 END tstop;
219
220
221 PROCEDURE tdump
222 IS
223 tempParentId NUMBER := 0;
224 msg VARCHAR2(2000);
225 BEGIN
226
227
228 IF ISQPDebugON AND G_QP_debug = 'M' THEN
229
230 IF g_curr_parent <> 0 and g_TimeStack(g_curr_parent).IsRunning THEN
231 write_output(' **** Error : Forcefully stopping marker '
232 ||g_TimeStack(g_curr_parent).Marker
233 ||' : It may have wrong values.');
234 tstop(g_TimeStack(g_curr_parent).Marker);
235 END IF;
236
237 IF g_granularity_level = 0 THEN
238 write_output(' ');
239 write_output(' **** Dumping Time Log Information Started ****');
240 write_output(' ');
241 END IF;
242
243 --FOR v_Count IN 1..g_TimeStack.COUNT LOOP
244 IF g_curr_parent <> 0 THEN
245 IF NOT g_TimeStack(g_curr_parent).Deleted THEN
246
247 IF g_TimeStack(g_curr_parent).Description IS NULL
248 THEN
249 msg := g_TimeStack(g_curr_parent).Marker;
250 ELSE
251 msg := g_TimeStack(g_curr_parent).Description;
252 END IF;
253
254 IF g_TimeStack(g_curr_parent).putLine THEN
255 write_output(' ');
256 END IF;
257
258 msg := msg || ' : ' ||
259 g_TimeStack(g_curr_parent).CallCount||' calls : '||
260 g_TimeStack(g_curr_parent).TotalTime||' ms';
261
262 write_output(LPAD(msg,LENGTH(msg)+2*g_granularity_level,' '));
263 END IF;
264 END IF;
265 --END LOOP;
266
267 IF g_curr_parent = 0 OR NOT g_TimeStack(g_curr_parent).Deleted THEN
268 FOR v_Count IN 1..g_TimeStack.COUNT LOOP
269
270 IF g_TimeStack(v_count).ParentId = g_curr_parent
271 AND NOT g_TimeStack(v_count).Deleted
272 THEN
273 tempParentId := g_curr_parent;
274 g_curr_parent := v_count;
275 g_granularity_level := g_granularity_level + 1;
276 tdump;
277 g_granularity_level := g_granularity_level - 1;
278 g_curr_parent := tempParentId;
279 END IF;
280 END LOOP;
281 END IF;
282
283 IF g_curr_parent <> 0 THEN
284 tempParentId := g_TimeStack(g_curr_parent).ParentId;
285 g_TimeStack(g_curr_parent).Deleted := true;
286 g_curr_parent := tempParentId;
287 END IF;
288
289 IF g_granularity_level = 0 THEN
290 write_output(' ');
291 write_output(' **** Dumping Time Log Information Ended ****');
292 write_output(' ');
293 END IF;
294 END IF;
295 EXCEPTION
296 WHEN OTHERS THEN
297 write_output('tdump-MSG-'||SQLERRM);
298 g_qp_Debug := 'X';
299 END tdump;
300
301
302 PROCEDURE tflush
303 IS
304 BEGIN
305 g_TimeStack.DELETE;
306 END tflush;
307
308
309 Function ISQPDebugOn
310 Return Boolean IS
311 BEGIN
312
313 if G_DEBUG = FND_API.G_TRUE AND G_DEBUG_LEVEL = 1 AND G_qp_debug IN ('P','S','M') then
314 RETURN(TRUE);
315 else
316 RETURN(FALSE);
317 end if;
318 End ISQPDebugOn;
319
320 Function IsTimeLogDebugOn
321 Return Boolean IS
322 BEGIN
323
324 G_qp_debug := FND_PROFILE.VALUE('QP_DEBUG');
325 G_DEBUG := OE_DEBUG_PUB.G_DEBUG;
326 G_Debug_Level := FND_PROFILE.VALUE('ONT_DEBUG_LEVEL');
327
328 if G_DEBUG = FND_API.G_TRUE AND G_DEBUG_LEVEL = 1 AND G_QP_debug = 'M' then
329 RETURN(TRUE);
330 else
331 RETURN(FALSE);
332 end if;
333 End IsTimeLogDebugOn;
334
335
336 /*
337 Description :
338 This procedure will print the data returned by cursor p_cursor in a CSV file.
339 Input Parameters :
340 p_output_file - file handle of the csv file.
341 p_cursor - Cursor id whose data is to be printed.
342 Output Parameters :
343 None
344 */
345
346 PROCEDURE print_cursor_data_csv_pvt(
347 p_output_file IN utl_file.file_type,
348 p_cursor in INTEGER
349 )
350 IS
351 l_routine VARCHAR2(240):='Routine : QP_DEBUG_UTIL.PRINT_CURSOR_DATA_CSV_PVT';
352 l_cols_desc dbms_sql.desc_tab;
353 l_col_cnt number;
354 l_status number;
355 l_col_val varchar2(32767);
356 l_hdr_string VARCHAR2(32767);
357 l_row_string VARCHAR2(32767);
358
359 l_output_file utl_file.file_type;
360 filePath varchar2(1000);
361 fileName varchar2(1000);
362
363 BEGIN
364
365 l_output_file := p_output_file;
366
367 --getting column details in l_cols_desc
368 dbms_sql.describe_columns( p_cursor, l_col_cnt, l_cols_desc);
369
370 --defining holding variables for columns
371 l_hdr_string := '';
372
373 for i in 1 .. l_cols_desc.count LOOP
374
375 dbms_sql.define_column( p_cursor, i, l_col_val, 32765);
376
377 l_hdr_string := l_hdr_string || l_cols_desc(i).col_name;
378
379 IF i <> l_cols_desc.COUNT THEN
380 l_hdr_string := l_hdr_string || ',';
381 END IF;
382
383 end loop;
384
385 utl_file.put_line(l_output_file,l_hdr_string);
386
387 --printing rows
388
389 l_status := dbms_sql.execute(p_cursor);
390
391 LOOP
392
393 EXIT WHEN (dbms_sql.fetch_rows(p_cursor) <= 0);
394
395 l_row_string := '';
396
397 for i in 1 .. l_cols_desc.count LOOP
398
399 dbms_sql.column_value(p_cursor, i, l_col_val );
400
401 l_row_string := l_row_string || l_col_val;
402
403 IF i <> l_cols_desc.COUNT THEN
404 l_row_string := l_row_string || ',';
405 END IF;
406
407 end loop;
408
409 utl_file.put_line(l_output_file,l_row_string);
410
411 END LOOP;
412
413 EXCEPTION
414 WHEN OTHERS THEN
415
416 IF qp_preq_grp.G_DEBUG_ENGINE = FND_API.G_TRUE THEN
417 qp_preq_grp.engine_debug('Exception occured - '||l_routine);
418 qp_preq_grp.engine_debug('Error Message - '||SQLERRM);
419 END IF;
420
421 END print_cursor_data_csv_pvt;
422
423 /*
424 Description :
425 This procedure will print the data returned by cursor p_cursor in a CSV file.
426 Naming convention for csv file is -
427 <unique sequence number (g_csv_count) for a session>_<event>_<p_file_id>_<OE Debug file name>.csv
428 Input Parameters :
429 p_file_id - CSV file will contain this id in it's name
430 p_cursor - Cursor id whose data is to be printed.
431 Output Parameters :
432 None
433 */
434
435 PROCEDURE print_cursor_data_csv_pvt(
436 p_file_id IN VARCHAR2,
437 p_cursor in INTEGER,
438 p_append IN BOOLEAN := FALSE,
439 p_prefix_event IN BOOLEAN := TRUE
440 )
441 IS
442 l_routine VARCHAR2(240):='Routine : QP_DEBUG_UTIL.PRINT_CURSOR_DATA_CSV_PVT';
443 l_cols_desc dbms_sql.desc_tab;
444 l_col_cnt number;
445 l_status number;
446 l_col_val varchar2(32767);
447 l_hdr_string VARCHAR2(32767);
448 l_row_string VARCHAR2(32767);
449 l_init_row_string VARCHAR2(50);
450 l_first_time BOOLEAN := TRUE;
451
452 --l_prcng_event VARCHAR2(30);
453
454 l_output_file utl_file.file_type;
455 filePath varchar2(1000);
456 fileName varchar2(1000);
457
458 l_file_ids_tbl file_id_list_tbl;
459
460 l_csv_count NUMBER := 0;
461
462 l_mode VARCHAR2(1);
463
464 BEGIN
465
466 select fnd_profile.value('OE_DEBUG_LOG_DIRECTORY')
467 into filePath
468 from dual;
469
470 IF INSTR(G_CURR_PRICE_EVENT,',') = LENGTH(G_CURR_PRICE_EVENT)
471 THEN
472 l_init_row_string := REPLACE(G_CURR_PRICE_EVENT,',','-');
473 ELSE
474 l_init_row_string := REPLACE(G_CURR_PRICE_EVENT,',','-') ||',';
475 END IF;
476
477 IF p_append THEN
478
479 IF g_file_ids_tbl.EXISTS(p_file_id) THEN
480 qp_preq_grp.engine_debug('file id'||p_file_id||' exist');
481 l_first_time := FALSE;
482 l_csv_count := g_file_ids_tbl(p_file_id);
483 ELSE
484 qp_preq_grp.engine_debug('file id '||p_file_id||' does not exist');
485 g_csv_count := g_csv_count+1;
486 l_csv_count := g_csv_count;
487 g_file_ids_tbl(p_file_id) := l_csv_count;
488 END IF;
489
490 l_mode := 'a';
491
492 ELSE
493
494 g_csv_count := g_csv_count+1;
495 l_csv_count := g_csv_count;
496
497 l_mode := 'w';
498
499 END IF;
500
501 qp_preq_grp.engine_debug('l_csv_count-'||l_csv_count);
502 qp_preq_grp.engine_debug('l_mode-'||l_mode);
503
504 fileName := l_csv_count;
505
506 IF p_append = FALSE
507 AND G_CURR_PRICE_EVENT IS NOT NULL THEN
508 fileName := fileName || '_' || G_CURR_PRICE_EVENT;
509 END IF;
510
511 fileName := fileName|| '_' || p_file_id || '_' ||
512 SUBSTR(OE_DEBUG_PUB.G_FILE,1,INSTR(OE_DEBUG_PUB.G_FILE,'.dbg')-1);
513
514 l_output_file := utl_file.fopen( filePath, fileName||'.csv', l_mode ,32000 );
515
516 qp_preq_grp.engine_debug('Dumping data in file - ' || filePath || '/' ||fileName||'.csv');
517
518
519
520 --getting column details in l_cols_desc
521 dbms_sql.describe_columns( p_cursor, l_col_cnt, l_cols_desc);
522
523 --defining holding variables for columns
524
525 IF G_CURR_PRICE_EVENT IS NOT NULL
526 AND p_prefix_event THEN
527 l_hdr_string := 'PRICING_EVENT,';
528 ELSE
529 l_hdr_string := '';
530 END IF;
531
532 for i in 1 .. l_cols_desc.count LOOP
533
534 dbms_sql.define_column( p_cursor, i, l_col_val, 32765);
535
536 l_hdr_string := l_hdr_string || l_cols_desc(i).col_name;
537
538 IF i <> l_cols_desc.COUNT THEN
539 l_hdr_string := l_hdr_string || ',';
540 END IF;
541
542 end loop;
543
544 IF l_first_time THEN
545 utl_file.put_line(l_output_file,l_hdr_string);
546 END IF;
547
548 --printing rows
549
550
551 l_status := dbms_sql.execute(p_cursor);
552
553 LOOP
554
555 EXIT WHEN (dbms_sql.fetch_rows(p_cursor) <= 0);
556
557 IF G_CURR_PRICE_EVENT IS NOT NULL
558 AND p_prefix_event THEN
559 l_row_string := l_init_row_string;
560 ELSE
561 l_row_string := '';
562 END IF;
563
564 for i in 1 .. l_cols_desc.count LOOP
565
566 dbms_sql.column_value(p_cursor, i, l_col_val );
567
568 l_row_string := l_row_string || l_col_val;
569
570 IF i <> l_cols_desc.COUNT THEN
571 l_row_string := l_row_string || ',';
572 END IF;
573
574 end loop;
575
576 utl_file.put_line(l_output_file,l_row_string);
577
578 END LOOP;
579
580 utl_file.fclose( l_output_file );
581
582 EXCEPTION
583 WHEN OTHERS THEN
584
585 IF utl_file.is_open(l_output_file) THEN
586 utl_file.fclose( l_output_file );
587 END IF;
588
589 IF qp_preq_grp.G_DEBUG_ENGINE = FND_API.G_TRUE THEN
590 qp_preq_grp.engine_debug('Exception occured - '||l_routine);
591 qp_preq_grp.engine_debug('Error Message - '||SQLERRM);
592 END IF;
593
594 END print_cursor_data_csv_pvt;
595
596 /*
597 Description :
598 This procedure will print data of table p_table_name in a csv file.
599 Input Parameters :
600 p_table_name - Name of table to be printed.
601 p_file_id - CSV file will contain this id in it's name
602 Output Parameters :
603 None
604 */
605
606 Procedure print_table_data_csv (
607 p_table_name IN VARCHAR2,
608 p_file_id IN VARCHAR2,
609 p_where_clause IN VARCHAR2 := NULL,
610 p_append IN BOOLEAN := FALSE,
611 p_prefix_event IN BOOLEAN := TRUE
612 )
613 IS
614 l_routine VARCHAR2(240):='Routine : QP_DEBUG_UTIL.PRINT_TABLE_DATA_CSV';
615 l_cursor number := dbms_sql.open_cursor;
616 l_query VARCHAR2(15000) := '';
617
618 BEGIN
619
620 G_DEBUG := OE_DEBUG_PUB.G_DEBUG;
621 G_Debug_Level := FND_PROFILE.VALUE('ONT_DEBUG_LEVEL');
622
623 IF G_DEBUG = FND_API.G_TRUE AND G_DEBUG_LEVEL = 5 THEN
624
625 l_query := 'select * from ' || p_table_name;
626
627 IF p_where_clause IS NOT NULL
628 THEN
629 l_query := l_query || ' where ' || p_where_clause;
630 END IF;
631
632 qp_preq_grp.engine_debug(' p_file_id - ' || p_file_id);
633 qp_preq_grp.engine_debug(' l_query - ' || l_query);
634
635
636 dbms_sql.parse( l_cursor,
637 l_query
638 ,dbms_sql.native );
639
640 print_cursor_data_csv_pvt(
641 p_file_id => p_file_id ,
642 p_cursor => l_cursor,
643 p_append => p_append,
644 p_prefix_event => p_prefix_event);
645
646 end if;
647 dbms_sql.close_cursor( l_cursor );
648
649 EXCEPTION
650
651 WHEN OTHERS THEN
652
653 IF DBMS_SQL.IS_OPEN (l_cursor) THEN
654 dbms_sql.close_cursor( l_cursor );
655 END IF;
656
657 IF qp_preq_grp.G_DEBUG_ENGINE = FND_API.G_TRUE THEN
658 qp_preq_grp.engine_debug('Exception occured - '||l_routine);
659 qp_preq_grp.engine_debug('Error Message - '||SQLERRM);
660 END IF;
661 END print_table_data_csv;
662
663 Procedure print_query_data_csv (
664 p_query IN VARCHAR2,
665 p_file_id IN VARCHAR2,
666 p_append IN BOOLEAN := FALSE,
667 p_prefix_event IN BOOLEAN := TRUE
668 )
669 IS
670 l_routine VARCHAR2(240):='Routine : QP_DEBUG_UTIL.PRINT_QUERY_DATA_CSV';
671 l_cursor number := dbms_sql.open_cursor;
672
673 BEGIN
674
675 G_DEBUG := OE_DEBUG_PUB.G_DEBUG;
676 G_Debug_Level := FND_PROFILE.VALUE('ONT_DEBUG_LEVEL');
677
678 IF G_DEBUG = FND_API.G_TRUE AND G_DEBUG_LEVEL = 5 THEN
679
680 dbms_sql.parse( l_cursor,
681 p_query,
682 dbms_sql.native );
683
684 print_cursor_data_csv_pvt(
685 p_file_id => p_file_id ,
686 p_cursor => l_cursor,
687 p_append => p_append,
688 p_prefix_event => p_prefix_event);
689
690 END IF;
691
692 dbms_sql.close_cursor( l_cursor );
693
694
695 EXCEPTION
696 WHEN OTHERS THEN
697
698 IF DBMS_SQL.IS_OPEN (l_cursor) THEN
699 dbms_sql.close_cursor( l_cursor );
700 END IF;
701
702 IF qp_preq_grp.G_DEBUG_ENGINE = FND_API.G_TRUE THEN
703 qp_preq_grp.engine_debug('Exception occured - '||l_routine);
704 qp_preq_grp.engine_debug('Error Message - '||SQLERRM);
705 END IF;
706
707 END print_query_data_csv;
708
709 /*
710 Description :
711 This procedure will print data of cursor p_cursor_id in a csv file.
712 Input Parameters :
713 p_cursor_id - cursor whose data is to be printed.
714 p_file_id - CSV file will contain this id in it's name
715 Output Parameters :
716 None
717 */
718
719 Procedure print_cursor_data_csv (
720 p_cursor_id IN number,
721 p_file_id IN VARCHAR2
722 )
723 IS
724 l_routine VARCHAR2(240):='Routine : QP_DEBUG_UTIL.PRINT_CURSOR_DATA_CSV';
725
726 BEGIN
727
728 G_DEBUG := OE_DEBUG_PUB.G_DEBUG;
729 G_Debug_Level := FND_PROFILE.VALUE('ONT_DEBUG_LEVEL');
730
731 IF G_DEBUG = FND_API.G_TRUE AND G_DEBUG_LEVEL = 5 THEN
732
733 print_cursor_data_csv_pvt(
734 p_file_id => p_file_id ,
735 p_cursor => p_cursor_id);
736
737 END IF;
738
739 EXCEPTION
740 WHEN OTHERS THEN
741 IF qp_preq_grp.G_DEBUG_ENGINE = FND_API.G_TRUE THEN
742 qp_preq_grp.engine_debug('Exception occured - '||l_routine);
743 qp_preq_grp.engine_debug('Error Message - '||SQLERRM);
744 END IF;
745
746 END print_cursor_data_csv;
747
748 /*
749 Description :
750 This procedure will print blank rows in the specified csv file.
751 Input Parameters :
752 p_outputfile_id - file id in which blank rows are required.
753 p_row_count - number of blank rows to be printed
754 Output Parameters :
755 None
756 */
757
758 PROCEDURE print_blank_rows_csv (
759 p_outputfile_id utl_file.file_type,
760 p_row_count number
761 )
762 IS
763 l_routine VARCHAR2(240):='Routine : QP_DEBUG_UTIL.PRINT_BLANK_ROWS_CSV';
764 BEGIN
765 FOR i IN 1..p_row_count
766 LOOP
767 utl_file.put_line(p_outputfile_id,' ');
768 END LOOP;
769
770 EXCEPTION
771 WHEN OTHERS THEN
772
773 IF qp_preq_grp.G_DEBUG_ENGINE = FND_API.G_TRUE THEN
774 qp_preq_grp.engine_debug('Exception occured - '||l_routine);
775 qp_preq_grp.engine_debug('Error Message - '||SQLERRM);
776 END IF;
777
778 END print_blank_rows_csv;
779
780 /*
781 Description :
782 This procedure will print results of queries listed in p_query_list
783 in a csv file. Naming convention for csv file is -
784 <unique sequence number (g_csv_count) for a session>_<event>_<p_file_id>_<OE Debug file name>.csv
785 Input Parameters :
786 p_query_list - list of queries.
787 p_file_id - CSV file will contain this id in it's name.
788 Output Parameters :
789 None
790 */
791
792 PROCEDURE print_querylist_data_csv (
793 p_query_list IN query_list,
794 p_file_id IN VARCHAR2 )
795 IS
796 l_output_file utl_file.file_type;
797 filePath varchar2(1000);
798 fileName varchar2(1000);
799
800 l_routine VARCHAR2(240):='Routine : QP_DEBUG_UTIL.PRINT_QUERYLIST_DATA_CSV';
801
802 l_cursor number;
803
804 BEGIN
805
806 G_DEBUG := OE_DEBUG_PUB.G_DEBUG;
807 G_Debug_Level := FND_PROFILE.VALUE('ONT_DEBUG_LEVEL');
808
809 IF G_DEBUG = FND_API.G_TRUE AND G_DEBUG_LEVEL = 5 THEN
810 g_csv_count := g_csv_count+1;
811
812 fileName := g_csv_count;
813
814 IF G_CURR_PRICE_EVENT IS NOT NULL THEN
815 fileName := fileName||'_' || G_CURR_PRICE_EVENT;
816 END IF;
817
818 fileName := fileName|| '_' || p_file_id || '_' ||
819 SUBSTR(OE_DEBUG_PUB.G_FILE,1,INSTR(OE_DEBUG_PUB.G_FILE,'.dbg')-1);
820
821 SELECT fnd_profile.value('OE_DEBUG_LOG_DIRECTORY')
822 INTO filePath
823 FROM dual;
824
825 l_output_file := utl_file.fopen( filePath, fileName||'.csv', 'w',32000 );
826
827 qp_preq_grp.engine_debug('Dumping data in file - ' || filePath || '/' ||fileName||'.csv');
828
829 FOR i IN p_query_list.first..p_query_list.last
830 LOOP
831
832 l_cursor := dbms_sql.open_cursor;
833
834 dbms_sql.parse( l_cursor,
835 p_query_list(i),
836 dbms_sql.native );
837
838 print_cursor_data_csv_pvt(
839 p_output_file => l_output_file ,
840 p_cursor => l_cursor);
841
842 print_blank_rows_csv(l_output_file,5);
843
844 dbms_sql.close_cursor( l_cursor );
845
846 END LOOP;
847
848 utl_file.fclose( l_output_file );
849
850 END IF;
851
852 EXCEPTION
853 WHEN OTHERS THEN
854
855 IF utl_file.is_open(l_output_file) THEN
856 utl_file.fclose( l_output_file );
857 END IF;
858
859 IF DBMS_SQL.IS_OPEN (l_cursor) THEN
860 dbms_sql.close_cursor( l_cursor );
861 END IF;
862
863 IF qp_preq_grp.G_DEBUG_ENGINE = FND_API.G_TRUE THEN
864 qp_preq_grp.engine_debug('Exception occured - '||l_routine);
865 qp_preq_grp.engine_debug('Error Message - '||SQLERRM);
866 END IF;
867
868
869 END print_querylist_data_csv;
870
871 /*
872 Description :
873 This procedure will print development log in csv files.
874 It will dump data from table qp_npreq_line_patrns_tmp,
875 QP_PREQ_PATRN_QUAL_TMP and qp_npreq_line_attrs_tmp. Total three
876 csv files will be generated (for the session) one for each table.
877 Data from different requestes within that session and of multiple
878 phases within that request will be dumped in the same file.
879 Input Parameters :
880 None
881 Output Parameters :
882 None
883 */
884
885 PROCEDURE print_development_csv AS
886
887 l_sql_stmt VARCHAR2(10000);
888 l_routine VARCHAR2(240):='Routine : QP_DEBUG_UTIL.print_development_csv';
889 BEGIN
890 G_Debug_Level := FND_PROFILE.VALUE('ONT_DEBUG_LEVEL');
891 IF G_DEBUG_LEVEL = 5 THEN
892 -- Printing table qp_npreq_line_patrns_tmp
893 l_sql_stmt := 'SELECT REQUEST_ID,'''
894 || REPLACE(G_CURR_PRICE_EVENT,',','-')
895 || ''' PRICING_EVENT, PATTERN_ID, LINE_INDEX, HASH_KEY'
896 || ' FROM qp_npreq_line_patrns_tmp';
897
898 qp_debug_util.print_query_data_csv (p_query => l_sql_stmt,
899 p_file_id => 'LINE_PATRNS_TMP',
900 p_append => TRUE,
901 p_prefix_event => FALSE
902 );
903
904 -- Printing table QP_PREQ_PATRN_QUAL_TMP
905 l_sql_stmt := ''
906 ||'SELECT request_id , '
907 || '''' || REPLACE(G_CURR_PRICE_EVENT,',','-') || ''' PRICING_EVENT,'
908 ||' line_index , '
909 ||' pricing_phase_id , '
910 ||' list_header_id , '
911 ||' list_line_id , '
912 ||' stage , '
913 ||' pricing_status_code , '
914 ||' matched_pattrn_id , '
915 ||' matched_hash_key , '
916 ||' grouping_no , '
917 ||' source_system_code , '
918 ||' header_quals_exist_flag , '
919 ||' eq_flag , '
920 ||' other_oprt_count , '
921 ||' null_other_oprt_count , '
922 ||' list_type_code , '
923 ||' pricing_effective_date , '
924 ||' modifier_level_code , '
925 ||' currency_detail_id , '
926 ||' currency_header_id , '
927 ||' qualifier_precedence , '
928 ||' validated_flag , '
929 ||' ask_for_flag , '
930 ||' header_limit_exists , '
931 ||' line_limit_exists , '
932 ||' selling_rounding_factor , '
933 ||' order_currency , '
934 ||' base_currency_code , '
935 ||' break_uom_code , '
936 ||' break_uom_context , '
937 ||' break_uom_attribute , '
938 ||' pricing_status_text '
939 ||'FROM QP_PREQ_PATRN_QUAL_TMP';
940
941 qp_debug_util.print_query_data_csv (p_query => l_sql_stmt,
942 p_file_id => 'PATRN_QUAL_TMP',
943 p_append => TRUE,
944 p_prefix_event => FALSE);
945
946 -- Printing table qp_npreq_line_attrs_tmp
947 l_sql_stmt := ''
948 ||' SELECT request_id , '
949 || '''' || REPLACE(G_CURR_PRICE_EVENT,',','-') || ''' PRICING_EVENT,'
950 ||' pricing_phase_id , '
951 ||' line_index , '
952 ||' line_detail_index , '
953 ||' line_detail_type_code , '
954 ||' list_header_id , '
955 ||' list_line_id , '
956 ||' attribute_level , '
957 ||' attribute_type , '
958 ||' context , '
959 ||' attribute , '
960 ||' value_from , '
961 ||' setup_value_from , '
962 ||' value_to , '
963 ||' setup_value_to , '
964 ||' grouping_number , '
965 ||' comparison_operator_type_code, '
966 ||' datatype , '
967 ||' product_uom_code , '
968 ||' group_quantity , '
969 ||' group_amount , '
970 ||' incompatability_grp_code , '
971 ||' modifier_level_code , '
972 ||' primary_uom_flag , '
973 ||' no_qualifiers_in_grp , '
974 ||' validated_flag , '
975 ||' applied_flag , '
976 ||' pricing_status_code , '
977 ||' pricing_status_text , '
978 ||' qualifier_precedence , '
979 ||' pricing_attr_flag , '
980 ||' qualifier_type , '
981 ||' processed_code , '
982 ||' excluder_flag , '
983 ||' distinct_qualifier_flag , '
984 ||' segment_id '
985 ||' FROM qp_npreq_line_attrs_tmp ';
986 /*||' WHERE ( '
987 ||' LINE_DETAIL_INDEX IS NULL AND '
988 || 'COMPARISON_OPERATOR_TYPE_CODE IN ('''
989 ||QP_PREQ_GRP.G_OPERATOR_BETWEEN ||''','''
990 ||G_OPERATOR_NOT_EQL ||''')) ';*/
991
992 qp_debug_util.print_query_data_csv (p_query => l_sql_stmt,
993 p_file_id => 'LINE_ATTRS_TMP',
994 p_append => TRUE,
995 p_prefix_event => FALSE
996 );
997 ELSE
998 qp_preq_grp.engine_debug('Profile OM:debug level should be set to 5 to print csv files');
999 END IF;
1000 EXCEPTION
1001 WHEN OTHERS THEN
1002 qp_preq_grp.engine_debug('Exception occured - '||l_routine);
1003 qp_preq_grp.engine_debug('Error Message - '||SQLERRM);
1004 END print_development_csv;
1005
1006 /*
1007 Description :
1008 This procedure will print support log in csv files.
1009 Data from different requestes within that session and of multiple
1010 phases within that request will be dumped in the same file.
1011 Input Parameters :
1012 None
1013 Output Parameters :
1014 None
1015 */
1016
1017 procedure print_support_csv(pos varchar2) as
1018
1019 --strings used for support log
1020 l_lines_stmt VARCHAR2(20000);
1021 l_ldets_stmt VARCHAR2(20000);
1022 l_attrs_stmt VARCHAR2(20000);
1023
1024 l_routine VARCHAR2(240):='Routine : QP_DEBUG_UTIL.print_support_csv';
1025
1026 begin
1027 G_Debug_Level := FND_PROFILE.VALUE('ONT_DEBUG_LEVEL');
1028 IF G_DEBUG_LEVEL = 5 THEN
1029 l_lines_stmt := 'select ' ||
1030 ' nlines.REQUEST_ID , ' ||
1031 '''' || REPLACE(G_CURR_PRICE_EVENT,',','-') || ''' PRICING_EVENT, ' ||
1032 ' nlines.LINE_INDEX , ' ||
1033 ' nlines.REQUEST_TYPE_CODE , ' ||
1034 ' nlines.LINE_ID , ' ||
1035 ' nlines.LINE_TYPE_CODE , ' ||
1036 ' nlines.PRICING_EFFECTIVE_DATE , ' ||
1037 ' nlines.LINE_QUANTITY , ' ||
1038 ' nlines.LINE_UOM_CODE , ' ||
1039 ' nlines.LINE_UNIT_PRICE , ' ||
1040 ' nlines.ORDER_UOM_SELLING_PRICE , ' ||
1041 ' nlines.PRICED_QUANTITY , ' ||
1042 ' nlines.PRICED_UOM_CODE , ' ||
1043 ' nlines.UNIT_PRICE , ' ||
1044 ' nlines.ADJUSTED_UNIT_PRICE , ' ||
1045 ' nlines.PRICE_LIST_HEADER_ID , ' ||
1046 ' list.NAME PRICE_LIST_NAME , ' ||
1047 ' nlines.UOM_QUANTITY , ' ||
1048 ' nlines.CURRENCY_CODE , ' ||
1049 ' nlines.PERCENT_PRICE , ' ||
1050 ' nlines.PARENT_PRICE , ' ||
1051 ' nlines.PARENT_QUANTITY , ' ||
1052 ' nlines.PARENT_UOM_CODE , ' ||
1053 ' nlines.PROCESSING_ORDER , ' ||
1054 ' nlines.PROCESSED_FLAG , ' ||
1055 ' nlines.PROCESSED_CODE , ' ||
1056 ' nlines.PRICE_FLAG , ' ||
1057 ' nlines.PRICING_STATUS_CODE , ' ||
1058 ' nlines.PRICING_STATUS_TEXT , ' ||
1059 ' nlines.START_DATE_ACTIVE_FIRST , ' ||
1060 ' nlines.ACTIVE_DATE_FIRST_TYPE , ' ||
1061 ' nlines.START_DATE_ACTIVE_SECOND , ' ||
1062 ' nlines.ACTIVE_DATE_SECOND_TYPE , ' ||
1063 ' nlines.GROUP_QUANTITY , ' ||
1064 ' nlines.GROUP_AMOUNT , ' ||
1065 ' nlines.LINE_AMOUNT , ' ||
1066 ' nlines.ROUNDING_FLAG , ' ||
1067 ' nlines.ROUNDING_FACTOR , ' ||
1068 ' nlines.UPDATED_ADJUSTED_UNIT_PRICE , ' ||
1069 ' nlines.PRICE_REQUEST_CODE , ' ||
1070 ' nlines.HOLD_CODE , ' ||
1071 ' nlines.HOLD_TEXT , ' ||
1072 ' nlines.VALIDATED_FLAG , ' ||
1073 ' nlines.QUALIFIERS_EXIST_FLAG , ' ||
1074 ' nlines.PRICING_ATTRS_EXIST_FLAG , ' ||
1075 ' nlines.PRIMARY_QUALIFIERS_MATCH_FLAG , ' ||
1076 ' nlines.USAGE_PRICING_TYPE , ' ||
1077 ' nlines.LINE_CATEGORY , ' ||
1078 ' nlines.CONTRACT_START_DATE , ' ||
1079 ' nlines.CONTRACT_END_DATE , ' ||
1080 ' nlines.PROCESS_STATUS , ' ||
1081 ' nlines.EXTENDED_PRICE , ' ||
1082 ' nlines.CATCHWEIGHT_QTY , ' ||
1083 ' nlines.ACTUAL_ORDER_QUANTITY , ' ||
1084 ' nlines.HEADER_ID , ' ||
1085 ' nlines.LIST_PRICE_OVERRIDE_FLAG , ' ||
1086 ' nlines.SERVICE_DURATION , ' ||
1087 ' nlines.SERVICE_PERIOD ' ||
1088 ' FROM QP_NPREQ_LINES_TMP nlines, qp_list_headers_vl list ' ||
1089 ' where list.list_header_id (+) = nlines.PRICE_LIST_HEADER_ID ' ||
1090 ' order by nlines.line_index';
1091
1092 l_ldets_stmt := 'select ' ||
1093 ' nldets.REQUEST_ID , ' ||
1094 '''' || REPLACE(G_CURR_PRICE_EVENT,',','-') || ''' PRICING_EVENT, ' ||
1095 ' nldets.LINE_INDEX , ' ||
1096 ' nldets.LINE_DETAIL_INDEX , ' ||
1097 ' nldets.CREATED_FROM_LIST_HEADER_ID , ' ||
1098 ' list.NAME LIST_NAME , ' ||
1099 ' nldets.CREATED_FROM_LIST_LINE_ID , ' ||
1100 ' nldets.CREATED_FROM_LIST_TYPE_CODE , ' ||
1101 ' nldets.CREATED_FROM_LIST_LINE_TYPE , ' ||
1102 ' nldets.MODIFIER_LEVEL_CODE , ' ||
1103 ' nldets.PRICING_PHASE_ID , ' ||
1104 ' nldets.APPLIED_FLAG , ' ||
1105 ' nldets.AUTOMATIC_FLAG , ' ||
1106 ' nldets.OPERAND_CALCULATION_CODE , ' ||
1107 ' nldets.OPERAND_VALUE , ' ||
1108 ' nldets.ADJUSTMENT_AMOUNT , ' ||
1109 ' nldets.LINE_DETAIL_TYPE_CODE , ' ||
1110 ' nldets.LINE_DETAIL_PBH_TYPE , ' ||
1111 ' nldets.PRICE_BREAK_TYPE_CODE , ' ||
1112 ' nldets.CREATED_FROM_SQL , ' ||
1113 ' nldets.PRICING_GROUP_SEQUENCE , ' ||
1114 ' nldets.LINE_QUANTITY , ' ||
1115 ' nldets.SUBSTITUTION_TYPE_CODE , ' ||
1116 ' nldets.SUBSTITUTION_VALUE_FROM , ' ||
1117 ' nldets.SUBSTITUTION_VALUE_TO , ' ||
1118 ' nldets.ASK_FOR_FLAG , ' ||
1119 ' nldets.PRICE_FORMULA_ID , ' ||
1120 ' nldets.PROCESSED_FLAG , ' ||
1121 ' nldets.PRICING_STATUS_CODE , ' ||
1122 ' nldets.PRICING_STATUS_TEXT , ' ||
1123 ' nldets.PRODUCT_PRECEDENCE , ' ||
1124 ' nldets.INCOMPATABILITY_GRP_CODE , ' ||
1125 ' nldets.BEST_PERCENT , ' ||
1126 ' nldets.OVERRIDE_FLAG , ' ||
1127 ' nldets.PRINT_ON_INVOICE_FLAG , ' ||
1128 ' nldets.PRIMARY_UOM_FLAG , ' ||
1129 ' nldets.BENEFIT_QTY , ' ||
1130 ' nldets.BENEFIT_UOM_CODE , ' ||
1131 ' nldets.LIST_LINE_NO , ' ||
1132 ' nldets.ACCRUAL_FLAG , ' ||
1133 ' nldets.ACCRUAL_CONVERSION_RATE , ' ||
1134 ' nldets.ESTIM_ACCRUAL_RATE , ' ||
1135 ' nldets.RECURRING_FLAG , ' ||
1136 ' nldets.SELECTED_VOLUME_ATTR , ' ||
1137 ' nldets.ROUNDING_FACTOR , ' ||
1138 ' nldets.SECONDARY_PRICELIST_IND , ' ||
1139 ' nldets.GROUP_QUANTITY , ' ||
1140 ' nldets.GROUP_AMOUNT , ' ||
1141 ' nldets.PROCESS_CODE , ' ||
1142 ' nldets.UPDATED_FLAG , ' ||
1143 ' nldets.CHARGE_TYPE_CODE , ' ||
1144 ' nldets.CHARGE_SUBTYPE_CODE , ' ||
1145 ' nldets.LIMIT_CODE , ' ||
1146 ' nldets.LIMIT_TEXT , ' ||
1147 ' nldets.HEADER_LIMIT_EXISTS , ' ||
1148 ' nldets.LINE_LIMIT_EXISTS , ' ||
1149 ' nldets.CALCULATION_CODE , ' ||
1150 ' nldets.CURRENCY_HEADER_ID , ' ||
1151 ' nldets.PRICING_EFFECTIVE_DATE , ' ||
1152 ' nldets.BASE_CURRENCY_CODE , ' ||
1153 ' nldets.ORDER_CURRENCY , ' ||
1154 ' nldets.CURRENCY_DETAIL_ID , ' ||
1155 ' nldets.SELLING_ROUNDING_FACTOR , ' ||
1156 ' nldets.CHANGE_REASON_CODE , ' ||
1157 ' nldets.CHANGE_REASON_TEXT , ' ||
1158 ' nldets.REQUEST_ID , ' ||
1159 ' nldets.PRICE_ADJUSTMENT_ID , ' ||
1160 ' nldets.RECURRING_VALUE , ' ||
1161 ' nldets.NET_AMOUNT_FLAG , ' ||
1162 ' nldets.ORDER_QTY_OPERAND , ' ||
1163 ' nldets.ORDER_QTY_ADJ_AMT , ' ||
1164 ' nldets.ACCUM_CONTEXT , ' ||
1165 ' nldets.ACCUM_ATTRIBUTE , ' ||
1166 ' nldets.ACCUM_ATTR_RUN_SRC_FLAG , ' ||
1167 ' nldets.BREAK_UOM_CODE , ' ||
1168 ' nldets.BREAK_UOM_CONTEXT , ' ||
1169 ' nldets.BREAK_UOM_ATTRIBUTE , ' ||
1170 ' nldets.SERVICE_DURATION , ' ||
1171 ' nldets.SERVICE_PERIOD ' ||
1172 ' FROM QP_NPREQ_LDETS_TMP nldets, qp_list_headers_vl list ' ||
1173 ' where list.list_header_id = nldets.CREATED_FROM_LIST_HEADER_ID ' ||
1174 ' order by nldets.line_index,nldets.LINE_DETAIL_INDEX';
1175
1176 l_attrs_stmt := 'select ' ||
1177 ' lattrs.REQUEST_ID , ' ||
1178 '''' || REPLACE(G_CURR_PRICE_EVENT,',','-') || ''' PRICING_EVENT, ' ||
1179 ' lattrs.LINE_INDEX , ' ||
1180 ' lattrs.LINE_DETAIL_INDEX , ' ||
1181 ' lattrs.ATTRIBUTE_LEVEL , ' ||
1182 ' lattrs.ATTRIBUTE_TYPE , ' ||
1183 ' lattrs.CONTEXT , ' ||
1184 ' qpc.USER_PRC_CONTEXT_NAME , ' ||
1185 ' lattrs.ATTRIBUTE , ' ||
1186 ' qps.USER_SEGMENT_NAME , ' ||
1187 ' lattrs.COMPARISON_OPERATOR_TYPE_CODE , ' ||
1188 ' lattrs.VALUE_FROM , ' ||
1189 ' lattrs.SETUP_VALUE_FROM , ' ||
1190 ' lattrs.VALUE_TO , ' ||
1191 ' lattrs.SETUP_VALUE_TO , ' ||
1192 ' lattrs.LIST_HEADER_ID , ' ||
1193 ' lattrs.LIST_LINE_ID , ' ||
1194 ' lattrs.GROUPING_NUMBER , ' ||
1195 ' lattrs.NO_QUALIFIERS_IN_GRP , ' ||
1196 ' lattrs.VALIDATED_FLAG , ' ||
1197 ' lattrs.APPLIED_FLAG , ' ||
1198 ' lattrs.PRICING_STATUS_CODE , ' ||
1199 ' lattrs.PRICING_STATUS_TEXT , ' ||
1200 ' lattrs.QUALIFIER_PRECEDENCE , ' ||
1201 ' lattrs.PRICING_ATTR_FLAG , ' ||
1202 ' lattrs.QUALIFIER_TYPE , ' ||
1203 ' lattrs.DATATYPE , ' ||
1204 ' lattrs.PRODUCT_UOM_CODE , ' ||
1205 ' lattrs.PROCESSED_CODE , ' ||
1206 ' lattrs.EXCLUDER_FLAG , ' ||
1207 ' lattrs.GROUP_QUANTITY , ' ||
1208 ' lattrs.GROUP_AMOUNT , ' ||
1209 ' lattrs.DISTINCT_QUALIFIER_FLAG , ' ||
1210 ' lattrs.PRICING_PHASE_ID , ' ||
1211 ' lattrs.INCOMPATABILITY_GRP_CODE , ' ||
1212 ' lattrs.LINE_DETAIL_TYPE_CODE , ' ||
1213 ' lattrs.MODIFIER_LEVEL_CODE , ' ||
1214 ' lattrs.PRIMARY_UOM_FLAG ' ||
1215 ' FROM QP_NPREQ_LINE_ATTRS_TMP lattrs, qp_prc_contexts_v qpc, qp_segments_v qps ' ||
1216 ' WHERE qpc.prc_context_code = lattrs.CONTEXT ' ||
1217 ' AND decode(QPC.prc_context_type,''PRICING_ATTRIBUTE'',''PRICING'',QPC.prc_context_type) = lattrs.ATTRIBUTE_TYPE ' ||
1218 ' AND qps.prc_context_id = qpc.prc_context_id' ||
1219 ' AND qps.segment_mapping_column = lattrs.ATTRIBUTE ';
1220
1221 if pos = 'START' then
1222 print_query_data_csv(l_lines_stmt,'PUB_QP_LINES_START',TRUE,FALSE);
1223 print_query_data_csv(l_ldets_stmt,'PUB_QP_LDETS_START',TRUE,FALSE);
1224 print_query_data_csv(l_attrs_stmt,'PUB_QP_ATTRS_START',TRUE,FALSE);
1225 else
1226 print_query_data_csv(l_lines_stmt,'PUB_QP_LINES_END',TRUE,FALSE);
1227 print_query_data_csv(l_ldets_stmt,'PUB_QP_LDETS_END',TRUE,FALSE);
1228 end if;
1229
1230 ELSE
1231 qp_preq_grp.engine_debug('Profile OM:debug level should be set to 5 to print csv files');
1232 END IF;
1233
1234 EXCEPTION
1235 WHEN OTHERS THEN
1236 qp_preq_grp.engine_debug('Exception occured - '||l_routine);
1237 qp_preq_grp.engine_debug('Error Message - '||SQLERRM);
1238 END print_support_csv;
1239
1240 /*
1241 Description :
1242 Set current pricing event in G_CURR_PRICE_EVENT.
1243 Input Parameters :
1244 currEvent : Current Pricing Event
1245 Output Parameters :
1246 None
1247 */
1248
1249 PROCEDURE setCurrentEvent(currEvent varchar2)
1250 AS
1251 BEGIN
1252 G_CURR_PRICE_EVENT := currEvent;
1253 END setCurrentEvent;
1254
1255 -- Summary Time Log Changes (Bug# 8933551)
1256 /*
1257 Description :
1258 Add given log message to the summary log.
1259 Input Parameters :
1260 logMessage : Log Message
1261 paddingTop : Number of blank lines to be inserted before the message
1262 paddingLeft : Left indentation (number of tabs)
1263 paddingBottom : Number of blank lines to be inserted after the message
1264 Output Parameters :
1265 None
1266 */
1267
1268 PROCEDURE addSummaryTimeLog(logMessage varchar2,
1269 paddingTop NUMBER := 0,
1270 paddingLeft NUMBER := 0,
1271 paddingBottom NUMBER := 0)
1272 AS
1273 v_posi NUMBER := 0;
1274 BEGIN
1275 IF IsTimeLogDebugOn THEN
1276 v_posi := g_summaryLog.COUNT + 1;
1277 g_summaryLog(v_posi).logMesg := logMessage;
1278 g_summaryLog(v_posi).paddingTop := paddingTop;
1279 g_summaryLog(v_posi).paddingLeft := paddingLeft;
1280 g_summaryLog(v_posi).paddingBottom := paddingBottom;
1281 END IF;
1282 END addSummaryTimeLog;
1283
1284 /*
1285 Description :
1286 Dump summary log in the debug file.
1287 Input Parameters :
1288 None.
1289 Output Parameters :
1290 None
1291 */
1292
1293 PROCEDURE dumpSummaryTimeLog
1294 AS
1295 BEGIN
1296
1297 IF IsTimeLogDebugOn THEN
1298
1299 write_output(' ');
1300 write_output(' **** Dumping Time Log (Summary) Information Started ****');
1301 write_output(' ');
1302
1303 FOR i IN 1..g_summaryLog.COUNT LOOP
1304
1305 --Number of blank line at top
1306 FOR j IN 1..g_summaryLog(i).paddingTop LOOP
1307 write_output(' ');
1308 END LOOP;
1309
1310 --final message
1311 write_output(LPAD(g_summaryLog(i).logMesg,LENGTH(g_summaryLog(i).logMesg)+8*g_summaryLog(i).paddingLeft,' '));
1312
1313 --Number of blank line at bottom
1314 FOR j IN 1..g_summaryLog(i).paddingBottom LOOP
1315 write_output(' ');
1316 END LOOP;
1317
1318 END LOOP;
1319
1320 write_output(' ');
1321 write_output(' **** Dumping Time Log (Summary) Information Ended ****');
1322 write_output(' ');
1323
1324 g_summaryLog.DELETE;
1325
1326 END IF;
1327
1328 EXCEPTION
1329 WHEN OTHERS THEN
1330 write_output('tdump-MSG-'||SQLERRM);
1331 g_qp_Debug := 'X';
1332 g_summaryLog.DELETE;
1333 END dumpSummaryTimeLog;
1334
1335 /*
1336 Description :
1337 Set attribute which can be accessed anywhere within the same session with the given key.
1338 Input Parameters :
1339 pKey : Key
1340 pValue : Value
1341 Output Parameters :
1342 None
1343 */
1344
1345 PROCEDURE setAttribute(pKey varchar2, pValue varchar2)
1346 AS
1347 BEGIN
1348 g_comm_attribs(pKey) := pValue;
1349 EXCEPTION
1350 WHEN OTHERS THEN
1351 write_output('setAttribute-MSG-'||SQLERRM);
1352 END setAttribute;
1353
1354 /*
1355 Description :
1356 Return value of the attribute for given key.
1357 Input Parameters :
1358 pKey : Key
1359 Output Parameters :
1360 Value
1361 */
1362
1363 FUNCTION getAttribute(pKey varchar2)
1364 RETURN VARCHAR2
1365 AS
1366 BEGIN
1367 IF g_comm_attribs.exists(pKey) THEN
1368 RETURN g_comm_attribs(pKey);
1369 ELSE
1370 RETURN null;
1371 END IF;
1372 EXCEPTION
1373 WHEN OTHERS THEN
1374 write_output('getAttribute-MSG-'||SQLERRM);
1375 RETURN null;
1376 END getAttribute;
1377
1378 begin
1379 g_csv_count := 0;
1380
1381 END QP_DEBUG_UTIL;