1 package body fa_rx_util_pkg as
2 /* $Header: FARXUTLB.pls 120.10.12010000.1 2008/07/28 13:14:06 appldev ship $ */
3
4
5 ------------------------------------------------------------
6 -- Package types and global variables
7 ------------------------------------------------------------
8
9 --
10 -- Values for <<WHO Columns>>
11 --
12 Request_Id number;
13 User_Id number;
14 Login_Id number;
15 Today date;
16
17 CURSOR_COLUMN_VALUES integer; --* bug#3266462, rravunny
18 --
19 Initialization_Required boolean := true;
20 Run_Report_At_Proc varchar2(70) := null;
21
22 -- Debug flag
23 m_debug_flag boolean := false;
24 m_dbms_output boolean := false;
25 m_output_cursor integer := null;
26
27 g_print_debug boolean := fa_cache_pkg.fa_print_debug;
28
29 ------------------------------------
30 -- Private Functions
31 ------------------------------------
32 -----------------------------------------------------------
33 --
34 -- FUNCTION build_select
35 --
36 -- Parameters
37 -- None
38 --
39 -- Returns
40 -- Varchar2 Select statement
41 --
42 -- Description
43 -- Builds the select statement from
44 -- Rep_Columns as well as From_Clause, Where_Clause,
45 -- Group_By_Clause, Having_Clause, and Order_By_Clause.
46 --
47 -- Modificaiton History
48 -- KMIZUTA 12-MAR-99 Created.
49 --
50 -----------------------------------------------------------
51 function build_select return varchar2
52 is
53 buffer varchar2(10000);
54 idx number;
55 begin
56 IF (g_print_debug) THEN
57 fa_rx_util_pkg.debug('fa_rx_util_pkg.build_select('||to_char(Num_Columns)||')+');
58 END IF;
59
60 idx := 1;
61 while idx <= Num_Columns and Rep_Columns(idx).select_column_name is null loop
62 idx := idx + 1;
63 end loop;
64 buffer := 'SELECT '||Hint_Clause||'
65 '||Rep_Columns(idx).select_column_name;
66 loop
67 idx := idx + 1;
68 exit when idx > Num_Columns;
69
70 if Rep_Columns(idx).select_column_name is not null then
71 buffer := buffer ||',
72 '||Rep_Columns(idx).select_column_name;
73 end if;
74 end loop;
75
76 buffer := buffer || '
77 FROM '||From_Clause;
78 if Where_Clause is not null then buffer := buffer ||'
79 WHERE '||Where_Clause;
80 end if;
81 if Group_By_Clause is not null then buffer := buffer ||'
82 GROUP BY '||Group_By_Clause;
83 end if;
84 if Having_Clause is not null then buffer := buffer ||'
85 HAVING '||Having_Clause;
86 end if;
87 if Order_By_Clause is not null then buffer := buffer ||'
88 ORDER BY '||Order_By_Clause;
89 end if;
90
91 IF (g_print_debug) THEN
92 fa_rx_util_pkg.debug('SELECT Statement = ');
93 fa_rx_util_pkg.debug(buffer);
94 fa_rx_util_pkg.debug('fa_rx_util_pkg.build_select()-');
95 END IF;
96
97 return buffer;
98
99 exception
100 when no_data_found then
101 IF (g_print_debug) THEN
102 fa_rx_util_pkg.debug('Missing item at '||to_char(idx));
103 fa_rx_util_pkg.debug('Check your calls to assign_column() and make sure that you have that index');
104 END IF;
105
106 raise;
107 end build_select;
108
109 -----------------------------------------------------------
110 --
111 -- FUNCTION build_insert
112 --
113 -- Parameters
114 -- None
115 --
116 -- Returns
117 -- Varchar2 Insert statement
118 --
119 -- Description
120 -- Builds the insert statement from Rep_columns.
121
122 -- Modificaiton History
123 -- KMIZUTA 12-MAR-99 Created.
124 --
125 -----------------------------------------------------------
126 function build_insert return varchar2
127 is
128 idx number;
129 buf1 varchar2(10000);
130 buf2 varchar2(10000);
131 begin
132
133 IF (g_print_debug) THEN
134 fa_rx_util_pkg.debug('fa_rx_util_pkg.build_insert()+');
135 END IF;
136
137 idx := 1;
138 while idx <= Num_Columns and Rep_Columns(idx).insert_column_name is null loop
139 idx := idx + 1;
140 end loop;
141 buf1 := 'INSERT INTO '||Interface_Table||' (
142 '||Rep_Columns(idx).insert_column_name;
143 buf2 := ') VALUES (
144 :b'||to_char(idx);
145 loop
146 idx := idx + 1;
147 exit when idx > Num_Columns;
148
149 if Rep_Columns(idx).insert_column_name is not null then
150 buf1 := buf1 ||',
151 '||Rep_Columns(idx).insert_column_name;
152 buf2 := buf2 ||',
153 :b'||to_char(idx);
154 end if;
155 end loop;
156 buf1 := buf1||', request_id, created_by, creation_date, last_updated_by, last_update_date, last_update_login';
157 buf2 := buf2 ||', :b_request_id, :b_user_id, :b_today, :b_user_id, :b_today, :b_login_id)';
158
159 IF (g_print_debug) THEN
160 fa_rx_util_pkg.debug('INSERT Statement = ');
161 fa_rx_util_pkg.debug(buf1||buf2);
162 fa_rx_util_pkg.debug('fa_rx_util_pkg.build_insert()-');
163 END IF;
164
165 return(buf1||buf2);
166 end build_insert;
167
168
169 -----------------------------------------------------------
170 --
171 -- PROCEDURE bind_insert
172 --
173 -- Parameters
174 -- p_cursor Cursor for the parsed insert statement
175 --
176 -- Description
177 -- The insert statement which is returned by the above
178 -- function (build_insert) returns an INSERT statement
179 -- with bind variables for the placeholder variables.
180 -- This function actually binds the values.
181 --
182 -- Modificaiton History
183 -- KMIZUTA 12-MAR-99 Created.
184 --
185 -----------------------------------------------------------
186
187 procedure bind_insert(p_cursor in number)
188 is
189 buffer varchar2(30000);
190 idx number;
191 c integer;
192 rows number;
193 begin
194 IF (g_print_debug) THEN
195 fa_rx_util_pkg.debug('fa_rx_util_pkg.bind_insert()+');
196 END IF;
197
198 buffer := 'BEGIN ';
199 for idx in 1..Num_Columns loop
200 if Rep_Columns(idx).insert_column_name is not null then
201 buffer := buffer ||'dbms_sql.bind_variable('||to_char(p_cursor)||
202 ', '':b'||to_char(idx)||
203 ''', '||Rep_Columns(idx).placeholder_name||');
204 ';
205 end if;
206 end loop;
207 buffer := buffer ||' END;';
208
209 IF (g_print_debug) THEN
210 fa_rx_util_pkg.debug('bind_insert: ' || buffer);
211 END IF;
212
213 c := dbms_sql.open_cursor;
214 dbms_sql.parse(c, buffer, dbms_sql.native);
215 rows := dbms_sql.execute(c);
216 dbms_sql.close_cursor(c);
217
218 dbms_sql.bind_variable(p_cursor, 'b_request_id', Request_Id);
219 dbms_sql.bind_variable(p_cursor, 'b_user_id', User_Id);
220 dbms_sql.bind_variable(p_cursor, 'b_login_id', Login_Id);
221 dbms_sql.bind_variable(p_cursor, 'b_today', Today);
222
223 IF (g_print_debug) THEN
224 fa_rx_util_pkg.debug('fa_rx_util_pkg.bind_insert()-');
225 END IF;
226 end bind_insert;
227
228 -----------------------------------------------------------
229 --
230 -- PROCEDURE define_columns
231 --
232 -- Parameters
233 -- p_cursor Cursor for the parsed select statement
234 --
235 -- Description
236 -- When using dynamic SQL with a SELECT statement,
237 -- the select list must be described via a call to
238 -- dbms_sql.define_column.
239 -- This procedure does this.
240 --
241 -- Modificaiton History
242 -- KMIZUTA 12-MAR-99 Created.
243 --
244 -----------------------------------------------------------
245 procedure define_columns(p_cursor in number)
246 is
247 sel_idx number;
248 idx number;
249 c number;
250 rows number;
251
252 buffer varchar2(10000);
253
254 l_varchar varchar2(10000);
255 l_number number;
256 l_date date;
257
258 begin
259
260 IF (g_print_debug) THEN
261 fa_rx_util_pkg.debug('fa_rx_util_pkg.define_columns()+');
262 END IF;
263
264 sel_idx := 1;
265
266 -- Fix for Bug #3742493. Replace dynamic sql method which creates a
267 -- string to define_column with straight define_column statements. This
268 -- removes the literals issue which could drag performance.
269 for idx in 1..Num_Columns loop
270 if Rep_Columns(idx).select_column_name is not null then
271
272 if Rep_Columns(idx).column_type = 'VARCHAR2' then
273
274 dbms_sql.define_column(
275 c => p_cursor,
276 position => sel_idx,
277 column => l_varchar,
278 column_size => to_char(Rep_Columns(idx).column_length));
279
280 elsif Rep_Columns(idx).column_type = 'NUMBER' then
281
282 dbms_sql.define_column(
283 c => p_cursor,
284 position => sel_idx,
285 column => l_number);
286
287 elsif Rep_Columns(idx).column_type = 'DATE' then
288
289 dbms_sql.define_column(
290 c => p_cursor,
291 position => sel_idx,
292 column => l_date);
293 else
294
295 dbms_sql.define_column(
296 c => p_cursor,
297 position => sel_idx,
298 column => l_number);
299 end if;
300
301 sel_idx := sel_idx + 1;
302 end if;
303 end loop;
304
305 /*
306 buffer := 'BEGIN ';
307 for idx in 1..Num_Columns loop
308 if Rep_Columns(idx).select_column_name is not null then
309 buffer := buffer ||'dbms_sql.define_column('||
310 to_char(p_cursor)||', '||
311 to_char(sel_idx)||', '||
312 Rep_Columns(idx).placeholder_name;
313 if Rep_Columns(idx).column_type = 'VARCHAR2' then
314 buffer := buffer ||', '||to_char(Rep_Columns(idx).column_length);
315 end if;
316 buffer := buffer || ');
317 ';
318 sel_idx := sel_idx + 1;
319 end if;
320 end loop;
321 buffer := buffer || 'END;';
322
323 IF (g_print_debug) THEN
324 fa_rx_util_pkg.debug('define_columns: ' || buffer);
325 END IF;
326
327 c := dbms_sql.open_cursor;
328 dbms_sql.parse(c, buffer, dbms_sql.native);
329 rows := dbms_sql.execute(c);
330 dbms_sql.close_cursor(c);
331 */
332
333 IF (g_print_debug) THEN
334 fa_rx_util_pkg.debug('fa_rx_util_pkg.define_columns()-');
335 END IF;
336 end define_columns;
337
338
339 -----------------------------------------------------------
340 --
341 -- PROCEDURE column_values
342 --
343 -- Parameters
344 -- p_cursor Cursor for the parsed select statement
345 --
346 -- Description
347 -- When using dynamic SQL with a SELECT statement,
348 -- the values from the select list must be retrieved using
349 -- dbms_sql.column_value.
350 -- This procedure does this.
351 --
352 -- Modificaiton History
353 -- KMIZUTA 12-MAR-99 Created.
354 --
355 -----------------------------------------------------------
356 procedure column_values(p_cursor in number)
357 is
358 sel_idx number;
359 idx number;
360 c integer default cursor_column_values; --* bug#3266462, rravunny
361 rows number;
362
363 buffer varchar2(10000);
364 begin
365 IF (g_print_debug) THEN
366 fa_rx_util_pkg.debug('fa_rx_util_pkg.column_values()+');
367 END IF;
368
369 sel_idx := 1;
370 buffer := 'BEGIN ';
371 for idx in 1..Num_Columns loop
372 if Rep_Columns(idx).select_column_name is not null then
373 buffer := buffer ||'dbms_sql.column_value('||
374 to_char(p_cursor)||','||
375 to_char(sel_idx)||','||
376 Rep_Columns(idx).placeholder_name||');
377 ';
378 sel_idx := sel_idx + 1;
379 end if;
380 end loop;
381 buffer := buffer || 'END;';
382
383 IF (g_print_debug) THEN
384 fa_rx_util_pkg.debug('column_values: ' || buffer);
385 END IF;
386
387 --* bug#3266462, rravunny
388 If c is null then
389 fa_rx_util_pkg.debug('cursor cursor_column_values/ c is defined for first time');
390 c := dbms_sql.open_cursor;
391 dbms_sql.parse(c, buffer, dbms_sql.native);
392 else
393 fa_rx_util_pkg.debug('cursor cursor_column_values/ c is already defined');
394 End If;
395 rows := dbms_sql.execute(c);
396 --* bug#3266462, rravunny dbms_sql.close_cursor(c);
397
398 IF (g_print_debug) THEN
399 fa_rx_util_pkg.debug('fa_rx_util_pkg.column_values()-');
400 END IF;
401 cursor_column_values := c; --* bug#3266462, rravunny
402 end column_values;
403
404 ------------------------------------
405 -- Public Functions/Procedures
406 ------------------------------------
407
408 -------------------------------------------------------------------------
409 --
410 -- PROCEDURE init_request
411 --
412 -- Parameters
413 -- p_request_id Request ID of this concurrent request.
414 --
415 -- Description
416 -- This function initializes some of the parameters needed by RX.
417 -- These include:
418 -- User_ID
419 -- Login_ID
420 -- Today's Date <-- All three used in <WHO Columns>
421 -- Interface Table name <-- retrieved using p_request_id
422 -- NOTES
423 -- If this function is called with a request id of 0, it will assume
424 -- that you are trying to debug this code from SQL*Plus and skip
425 -- this routine. It assumes that your testing script has already
426 -- called init_debug (below) to initialize these routines.
427 --
428 -- Modification History
429 -- KMIZUTA 12-MAR-99 Created.
430 --
431 -------------------------------------------------------------------------
432 procedure init_request(p_calling_proc in varchar2, p_request_id in number,
433 p_interface_table in varchar2 default null)
434 is
435 begin
436 IF (g_print_debug) THEN
437 fa_rx_util_pkg.debug('fa_rx_util_pkg.init_request('||to_char(p_request_id)||')+');
438 END IF;
439
440 --
441 -- If Initialization has already occurred, then exit
442 if not Initialization_Required then return;
443 end if;
444 Initialization_Required := false;
445
446 --
447 -- This is the procedure that will actually run the report.
448 Run_Report_At_Proc := p_calling_proc;
449 Num_Sections := 0;
450 Num_Columns := 0;
451 From_Clause := null;
452 Where_Clause := null;
453 Group_By_Clause := null;
454 Having_Clause := null;
455 Order_By_Clause := null;
456 User_Id := fnd_global.user_id;
457 Login_Id := fnd_global.login_id;
458 Today := sysdate;
459
460 if p_request_id = 0 then
461 --
462 -- This is a debugging request
463 IF (g_print_debug) THEN
464 fa_rx_util_pkg.debug('init_request: ' || 'Running from SQL*Plus');
465 fa_rx_util_pkg.debug('fa_rx_util_pkg.init_request(DEBUG)-');
466 END IF;
467 elsif p_interface_table is not null then
468 Interface_Table := p_interface_table;
469 else
470 --
471 -- Get the interface table name
472 select
473 rx.interface_table
474 into
475 Interface_Table
476 from
477 fnd_concurrent_requests r,
478 fa_rx_reports rx
479 where
480 r.request_id = p_request_id and
481 r.program_application_id = rx.application_id and
482 r.concurrent_program_id = rx.concurrent_program_id;
483 end if;
484
485 Request_Id := p_request_id;
486
487 --
488 -- Delete rows with the same request id in the interface table
489 -- NOTE: There should not be any rows here to delete except during debugging when
490 -- request id 0 is used multiple times.
491 declare
492 c integer;
493 rows integer;
494 begin
495 c := dbms_sql.open_cursor;
496 --* bug#3207863, rravunny dbms_sql.parse(c, 'delete from '||Interface_Table||' where request_id = '||to_char(p_request_id), dbms_sql.native);
497 dbms_sql.parse(c, 'delete from '||Interface_Table||' where request_id = :request_id', dbms_sql.native); --* bug#3207863, rravunny
498 DBMS_SQL.BIND_VARIABLE(c,':request_id',p_request_id); --* bug#3207863, rravunny
499 rows := dbms_sql.execute(c);
500
501 IF (g_print_debug) THEN
502 fa_rx_util_pkg.debug('init_request: ' || to_char(dbms_sql.last_row_count)||' row(s) deleted from table '||Interface_Table);
503 END IF;
504 dbms_sql.close_cursor(c);
505 end;
506
507 IF (g_print_debug) THEN
508 fa_rx_util_pkg.debug('init_request: ' || 'Using RX Interface Table = '||Interface_Table);
509 fa_rx_util_pkg.debug('fa_rx_util_pkg.init_request()-');
510 END IF;
511 exception
512 when no_data_found then
513 IF (g_print_debug) THEN
514 fa_rx_util_pkg.debug('init_request: ' || 'No RX request with request id = '||to_char(p_request_id)||' found!');
515 fa_rx_util_pkg.debug('fa_rx_util_pkg.init_request(NO_DATA_FOUND)-');
516 END IF;
517 end init_request;
518
519
520
521 -------------------------------------------------------------------------
522 --
523 -- PROCEDURE init_debug
524 --
525 -- Parameters
526 -- p_interface_table Interface table for the RX Report
527 --
528 -- Description
529 -- Does pretty much the same as init_request() except it initializes
530 -- the Interface Table name from the parameter. When debugging
531 -- using SQL*Plus, there will be no request_id from which you could
532 -- find out the interface table name.
533 -- You should prepare a test script whenever testing RX reports which
534 -- 1) Calls fnd_global.initialize
535 -- This initializes Oracle Applications.
536 -- 2) Calls fa_rx_util_pkg.enable_debug
537 -- Enable debugging for these routines
538 -- 3) Calls fa_rx_util_pkg.init_debug
539 -- Initialize these routines
540 -- 4) Calls your RX report
541 --
542 -- Modification History
543 -- KMIZUTA 12-MAR-99 Created.
544 --
545 -------------------------------------------------------------------------
546 procedure init_debug(p_interface_table in varchar2)
547 is
548 begin
549 IF (g_print_debug) THEN
550 fa_rx_util_pkg.debug('fa_rx_util_pkg.init_debug()+');
551 fa_rx_util_pkg.debug('init_debug: ' || 'This initialization routine should only be called during debugging sessions from SQL*Plus!');
552 END IF;
553
554 Interface_Table := p_interface_table;
555 Request_Id := 0;
556
557 IF (g_print_debug) THEN
558 fa_rx_util_pkg.debug('init_debug: ' || 'Using RX Interface Table = '||Interface_Table);
559 fa_rx_util_pkg.debug('fa_rx_util_pkg.init_debug()-');
560 END IF;
561 end init_debug;
562
563
564
565 -------------------------------------------------------------------------
566 --
567 -- PROCEDURE assign_column
568 --
569 -- Parameters
570 -- p_index Position in report column table.
571 -- This is sort of like the primary key. Plug-ins
572 -- will be able to override what you specify
573 -- here using this index number.
574 -- p_select_column_name
575 -- This is the name of the column that you will
576 -- be selecting from. Make sure to fully qualify
577 -- the column name (i.e., make sure the table
578 -- alias is included as in cc.code_combination_id).
579 -- p_insert_column_name
580 -- This is the name of the column in the interface
581 -- table where this value will be stored.
582 -- p_placeholder_name
583 -- This is the name of the package variable into
584 -- which this value is temporarily stored.
585 -- Make sure to fully qualify your variable name
586 -- with the package name (i.e., package_foo.struct_bar.var_name)
587 -- p_column_type Data type. Must be either VARCHAR2, NUMBER, DATE
588 -- p_column_length Needed only if the column type is VARCHAR2.
589 -- The length of the PLACEHOLDER variable.
590 --
591 -- Description
592 -- This procedure assigns this column to the report. These values
593 -- will be used to construct the SELECT statement as well as the
594 -- INSERT statement.
595 --
596 -- NOTES:
597 -- You may want to SELECT from a column which will be used in your
598 -- after fetch trigger. If you do not want to insert this value into
599 -- the interface table, simply keep p_insert_column_name NULL.
600 -- On the other hand, you may have a value which you calculate in
601 -- the after fetch trigger, but there is no source column from
602 -- the select statement. In this case, simply leave p_select_column_name
603 -- NULL.
604 -- In either case, you must specify p_placeholder_name
605 --
606 -- Modification History
607 -- KMIZUTA 12-MAR-99 Created.
608 --
609 -------------------------------------------------------------------------
610 procedure assign_column(p_key in varchar2,
611 p_select_column_name in varchar2,
612 p_insert_column_name in varchar2,
613 p_placeholder_name in varchar2,
614 p_column_type in varchar2,
615 p_column_length in number default null)
616 is
617 l_index number;
618
619 function Find_Column_Index(pk in varchar2) return number
620 is
621 found_idx number;
622 begin
623 found_idx := null;
624 for idx in 1..Num_Columns loop
625 if Rep_Columns(idx).primary_key = pk then
626 found_idx := idx;
627 exit;
628 end if;
629 end loop;
630
631 if found_idx is null then
632 Num_Columns := Num_Columns + 1;
633 found_idx := Num_Columns;
634 Rep_Columns(found_idx).primary_key := pk;
635 end if;
636
637 return found_idx;
638 end Find_Column_Index;
639
640 begin
641 IF (g_print_debug) THEN
642 fa_rx_util_pkg.debug('fa_rx_util_pkg.assign_column('||
643 p_key||','||
644 p_select_column_name||','||
645 p_insert_column_name||','||
646 p_placeholder_name||','||
647 p_column_type||'('||to_char(p_column_length)||'))+');
648 END IF;
649
650 if p_placeholder_name is null then
651 log('Placeholder name must not be NULL.');
652 app_exception.raise_exception;
653 end if;
654 if p_column_type not in ('VARCHAR2', 'DATE', 'NUMBER') then
655 log('Unknown column type = '||p_column_type);
656 app_exception.raise_exception;
657 end if;
658 if p_column_type = 'VARCHAR2' and p_column_length is null then
659 log('Length must be specified for columns of type VARCHAR2');
660 app_exception.raise_exception;
661 end if;
662
663 l_index := Find_Column_Index(p_key);
664
665 Rep_Columns(l_index).select_column_name := p_select_column_name;
666 Rep_Columns(l_index).insert_column_name := p_insert_column_name;
667 Rep_Columns(l_index).placeholder_name := p_placeholder_name;
668 Rep_Columns(l_index).column_type := p_column_type;
669 Rep_Columns(l_index).column_length := p_column_length;
670
671 IF (g_print_debug) THEN
672 fa_rx_util_pkg.debug('fa_rx_util_pkg.assign_column()-');
673 END IF;
674 end assign_column;
675
676
677
678 -------------------------------------------------------------------------
679 --
680 -- PROCEDURE assign_report
681 --
682 -- Parameters
683 -- p_section_number Your report may have multiple sections
684 -- where each section has a different
685 -- SELECT statement. You can have this model
686 -- run through each of your SELECT statements
687 -- in separate sections. Specify your section
688 -- number here.
689 -- p_before_report
690 -- p_bind
691 -- p_after_fetch
692 -- p_after_report These are the event triggers. Specify the
693 -- name of your procedure that you would
694 -- like to have called in each event. Make
695 -- sure to fully specify the procedure name
696 -- (i.e., add package name).
697 --
698 -- p_before_report_process_level
699 -- p_bind_process_level
700 -- p_after_fetch_process_level
701 -- p_after_report_process_level
702 -- This tells this function how to process
703 -- the new event block. Should your block
704 -- run before/after/replace the current
705 -- block assigned to that event.
706 --
707 -- Description
708 -- This procedure assigns the different event blocks for a given
709 -- section.
710 --
711 -- NOTES
712 -- The before report, after fetch, and after report event blocks
713 -- should not use any host variables.
714 -- The bind event must pass the host variable :CURSOR_SELECT.
715 -- This variable should be of type number or integer and you
716 -- should use this value as the cursor you pass to
717 -- dbms_sql.bind_variable().
718 --
719 -- Logic Flow
720 -- Blocks marked with a (*) are the events that are being set
721 -- by this procedure.
722 --
723 -- ------------------
724 -- | Before Report* |<----|
725 -- ------------------ |
726 -- | |
727 -- v |
728 -- ------------------ |
729 -- | Build Select | |
730 -- ------------------ |
731 -- | |
732 -- v |
733 -- ------------------ |
734 -- | Bind Select* | |
735 -- ------------------ |
736 -- | |
737 -- v |
738 -- ------------------ |
739 -- | Fetch Row |<-| |
740 -- ------------------ | |
741 -- | | |
742 -- v | |
743 -- ------------------ | |
744 -- | After Fetch* | | |
745 -- ------------------ | |
746 -- | | |
747 -- v | |
748 -- ------------------ | |
749 -- | Insert Row | | |
750 -- ------------------ | |
751 -- | | |
752 -- v | |
753 -- ------------------ | |
754 -- | Get Next Row |--| |
755 -- ------------------ |
756 -- | |
757 -- no more |
758 -- | |
759 -- v |
760 -- ------------------ |
761 -- | Next Section |-----|
762 -- ------------------
763 -- |
764 -- no more
765 -- |
766 -- v
767 -- ------------------
768 -- | After Report* |
769 -- ------------------
770 -- |
771 -- v
772 --
773 -- Before Report
774 -- Before report should call assign_column and set values to
775 -- From_Clause, Where_Clause, Group_By_Clause, Having_Clause,
776 -- Order_By_Clause. This is basically building up the SELECT
777 -- statement and the insert statement.
778 -- You may also do any preprocessing that may be required such
779 -- as leaving an audit of your run.
780 --
781 -- Bind
782 -- The bind event is called once after the select statement is
783 -- built and it has been parsed. You will be passed the value
784 -- of the cursor for the SELECT statement in a host variable
785 -- by the name of :CURSOR_SELECT. You will need to call
786 -- dbms_sql.bind_variabe to bind any variables that you may
787 -- have included in your Where_Clause, Group_By_Clause,
788 -- Having_Clause, or Order_By_Clause.
789 --
790 -- After Fetch
791 -- This event is called after each row is fetched. You can
792 -- assume that the placeholder variables that you specified
793 -- in your calls to assign_column are holding the value
794 -- for the current row. Do any processing that needs to
795 -- be done before the insert.
796 --
797 -- After Report
798 -- This may not really be necessary in most cases, but it is
799 -- here for you to do any cleaning up before exiting from the report.
800 --
801 -- Modification History
802 -- KMIZUTA 12-MAR-99 Created.
803 --
804 -------------------------------------------------------------------------
805 procedure assign_report(p_section_name in varchar2,
806 p_enabled in boolean,
807 p_before_report in varchar2,
808 p_bind in varchar2,
809 p_after_fetch in varchar2,
810 p_after_report in varchar2)
811 is
812 l_section_number number;
813
814 function Find_Report_Index(pk in varchar2) return number
815 is
816 l_index number;
817 begin
818 l_index := null;
819 for idx in 1..nvl(Num_Sections,0) loop
820 if Report(idx).section_name = pk then
821 l_index := idx;
822 exit;
823 end if;
824 end loop;
825
826 if l_index is null then
827 l_index := nvl(Num_Sections,0)+1;
828 Report(l_index).section_name := pk;
829 Report(l_index).enabled := false;
830 Report(l_index).before_report := null;
831 Report(l_index).bind := null;
832 Report(l_index).after_fetch := null;
833 Report(l_index).after_report := null;
834 end if;
835
836 Num_Sections := greatest(nvl(Num_Sections,0), l_index);
837
838 return l_index;
839 end Find_Report_Index;
840
841 begin
842 IF (g_print_debug) THEN
843 fa_rx_util_pkg.debug('fa_rx_util_pkg.assign_report()+');
844 END IF;
845
846 l_section_number := Find_Report_Index(p_section_name);
847
848 Report(l_section_number).enabled := p_enabled;
849
850 Report(l_section_number).before_report :=
851 Report(l_section_number).before_report || p_before_report;
852 Report(l_section_number).bind :=
853 Report(l_section_number).bind || p_bind;
854 Report(l_section_number).after_fetch :=
855 Report(l_section_number).after_fetch || p_after_fetch;
856 Report(l_section_number).after_report :=
857 Report(l_section_number).after_report || p_after_report;
858
859
860 IF (g_print_debug) THEN
861 fa_rx_util_pkg.debug('fa_rx_util_pkg.assign_report('||to_char(l_section_number)||')-');
862 END IF;
863 end assign_report;
864
865
866
867 -------------------------------------------------------------------------
868 --
869 -- PROCEDURE run_report
870 --
871 -- Parameters
872 -- None
873 --
874 -- Description
875 -- This is the procedure which actually runs your report. The logic
876 -- flow that is described above (in assign_report), is actually
877 -- performed by this procedure. Make sure to call this procedure
878 -- only after you have called init_request (or init_debug) and
879 -- assign_report.
880 --
881 -- Modification History
882 -- KMIZUTA 12-MAR-99 Created.
883 --
884 -------------------------------------------------------------------------
885 procedure run_report(p_calling_proc in varchar2, retcode out nocopy number, errbuf out varchar2)
886 is
887 l_user_id number;
888 l_login_id number;
889 l_today date;
890
891 idx number;
892 rows number;
893
894 cursor_select integer;
895 cursor_insert integer;
896 cursor_before_report integer;
897 cursor_bind integer;
898 cursor_after_fetch integer;
899 cursor_after_report integer;
900
901 begin
902 IF (g_print_debug) THEN
903 fa_rx_util_pkg.debug('fa_rx_util_pkg.run_report()+');
904 END IF;
905
906 retcode := 0;
907 errbuf := null;
908
909 if not p_calling_proc = Run_Report_At_Proc then
910 return; -- Don't run the report yet.
911 end if;
912 Initialization_Required := true;
913
914 for idx in 1..Num_Sections loop
915 Current_Section := Report(idx).section_name;
916 IF (g_print_debug) THEN
917 fa_rx_util_pkg.debug('run_report: ' || 'Current section = '||current_Section);
918 END IF;
919
920 if not Report(idx).enabled then
921 goto next_section;
922 end if;
923
924 -- Before calling the before report trigger
925 -- reset the select/insert list. This will be initialized within
926 -- the before report trigger itself.
927 Num_Columns := 0;
928 From_Clause := null;
929 Where_Clause := null;
930 Group_By_Clause := null;
931 Having_Clause := null;
932 Order_By_Clause := null;
933
934 -- Before Report Trigger
935 IF (g_print_debug) THEN
936 fa_rx_util_pkg.debug('run_report: ' || 'Before Report = '''||Report(idx).before_report||'''');
937 END IF;
938 if Report(idx).before_report is not null then
939 cursor_before_report := dbms_sql.open_cursor;
940 dbms_sql.parse(cursor_before_report,
941 'BEGIN '||Report(idx).before_report||' END;',
942 dbms_sql.native);
943 rows := dbms_sql.execute(cursor_before_report);
944 dbms_sql.close_cursor(cursor_before_report);
945 end if;
946
947
948 -- Fetch
949 cursor_select := dbms_sql.open_cursor;
950 dbms_sql.parse(cursor_select,
951 build_select,
952 dbms_sql.native);
953 if Report(idx).bind is not null then
954 -- Bind variables in the select statement
955 IF (g_print_debug) THEN
956 fa_rx_util_pkg.debug('run_report: ' || ' Bind...');
957 fa_rx_util_pkg.debug('run_report: ' || Report(idx).bind);
958 END IF;
959 cursor_bind := dbms_sql.open_cursor;
960 dbms_sql.parse(cursor_bind,
961 'BEGIN '||Report(idx).bind||' END;',
962 dbms_sql.native);
963 dbms_sql.bind_variable(cursor_bind,
964 ':CURSOR_SELECT',
965 cursor_select);
966 rows := dbms_sql.execute(cursor_bind);
967 dbms_sql.close_cursor(cursor_bind);
968 end if; -- of Bind
969 define_columns(cursor_select);
970
971 IF (g_print_debug) THEN
972 fa_rx_util_pkg.debug('run_report: ' || 'Executing SELECT statement');
973 END IF;
974 rows := dbms_sql.execute(cursor_select);
975
976 IF (g_print_debug) THEN
977 fa_rx_util_pkg.debug('run_report: ' || 'Building INSERT');
978 END IF;
979 cursor_insert := dbms_sql.open_cursor;
980 dbms_sql.parse(cursor_insert, build_insert, dbms_sql.native);
981
982 IF (g_print_debug) THEN
983 fa_rx_util_pkg.debug('run_report: ' || 'After Fetch Initialization');
984 END IF;
985 if Report(idx).after_fetch is not null then
986 cursor_after_fetch := dbms_sql.open_cursor;
987 dbms_sql.parse(cursor_after_fetch,
988 'BEGIN '||Report(idx).after_fetch||' END;',
989 dbms_sql.native);
990 else
991 cursor_after_fetch := null;
992 end if;
993
994 loop
995 rows := dbms_sql.fetch_rows(cursor_select);
996 exit when rows < 1;
997
998 IF (g_print_debug) THEN
999 fa_rx_util_pkg.debug('run_report: ' || 'Get values ');
1000 END IF;
1001 column_values(cursor_select);
1002
1003 IF (g_print_debug) THEN
1004 fa_rx_util_pkg.debug('run_report: ' || 'After Fetch');
1005 END IF;
1006 if cursor_after_fetch is not null then
1007 rows := dbms_sql.execute(cursor_after_fetch);
1008 end if;
1009
1010 IF (g_print_debug) THEN
1011 fa_rx_util_pkg.debug('run_report: ' || 'Insert Row');
1012 END IF;
1013 bind_insert(cursor_insert);
1014 rows := dbms_sql.execute(cursor_insert);
1015 end loop; -- Fetch
1016 if cursor_after_fetch is not null then
1017 dbms_sql.close_cursor(cursor_after_fetch);
1018 end if;
1019 dbms_sql.close_cursor(cursor_insert);
1020 dbms_sql.close_cursor(cursor_select);
1021 if cursor_column_values is not null then
1022 dbms_sql.close_cursor(cursor_column_values);
1023 end if;
1024
1025 -- After Report Trigger
1026 IF (g_print_debug) THEN
1027 fa_rx_util_pkg.debug('run_report: ' || ' After Report Trigger, Pass #'||to_char(idx));
1028 END IF;
1029 if Report(idx).after_report is not null then
1030 cursor_after_report := dbms_sql.open_cursor;
1031 dbms_sql.parse(cursor_after_report,
1032 'BEGIN '||Report(idx).after_report||' END;',
1033 dbms_sql.native);
1034 rows := dbms_sql.execute(cursor_after_report);
1035 dbms_sql.close_cursor(cursor_after_report);
1036 end if;
1037
1038 <<next_section>>
1039 null;
1040 end loop;
1041
1042 IF (g_print_debug) THEN
1043 fa_rx_util_pkg.debug('fa_rx_util_pkg.run_report()-');
1044 END IF;
1045 exception
1046 when others then
1047 IF (g_print_debug) THEN
1048 fa_rx_util_pkg.debug('run_report: ' || sqlerrm);
1049 fa_rx_util_pkg.debug('fa_rx_util_pkg.run_report(EXCEPTION)-');
1050 END IF;
1051
1052 retcode := 2; -- Error
1053 errbuf := sqlerrm;
1054 end run_report;
1055
1056
1057 ------------------------------------
1058 -- Debuggin Routines
1059 ------------------------------------
1060
1061 -------------------------------------------------------------------------
1062 -- PROCEDURE enable_debug
1063 --
1064 -- Parameters
1065 -- None
1066 -- OR
1067 -- debug_dir Directory where the debug file will reside
1068 -- debug_file Filename of debug file.
1069 --
1070 -- Description
1071 -- If you are calling this routine from a PL/SQL concurrent program
1072 -- then you should call the version with no parameters. This will
1073 -- automatically route all of your debug statements to the log file.
1074 -- If you are calling this routine from a PL/SQL package called from
1075 -- within SQL*Plus, you must specify the directory and file name
1076 -- of the debug file. NOTE: the database must have write permission.
1077 -- (Such as /sqlcom/log).
1078 --
1079 -- Modification History
1080 -- KMIZUTA 12-MAR-99 Created.
1081 --
1082 -------------------------------------------------------------------------
1083 procedure enable_debug
1084 is
1085 begin
1086 m_debug_flag := true;
1087 fa_rx_util_pkg.debug('Enabling debug...');
1088 end enable_debug;
1089
1090 procedure enable_debug(debug_dir in varchar2, debug_file in varchar2)
1091 is
1092 begin
1093 fnd_file.put_names(debug_file||'.log', debug_file||'.out', debug_dir);
1094 enable_debug;
1095 end enable_debug;
1096
1097 procedure enable_debug(bufsize in number)
1098 is
1099 sqlstmt varchar2(100);
1100 rows number;
1101 begin
1102 enable_debug;
1103 if not m_dbms_output then
1104 m_dbms_output := true;
1105 m_output_cursor := dbms_sql.open_cursor;
1106 sqlstmt := 'begin dbms_'||'output.enable(:b_size); end;';
1107 dbms_sql.parse(m_output_cursor, sqlstmt, dbms_sql.native);
1108 dbms_sql.bind_variable(m_output_cursor, 'b_size', bufsize);
1109 rows := dbms_sql.execute(m_output_cursor);
1110 dbms_sql.close_cursor(m_output_cursor);
1111
1112 m_output_cursor := dbms_sql.open_cursor;
1113 sqlstmt := 'begin dbms_'||'output.put_line(:b_msg); end;';
1114 dbms_sql.parse(m_output_cursor, sqlstmt, dbms_sql.native);
1115
1116 -- dbms_output.enable(bufsize);
1117 end if;
1118 end enable_debug;
1119
1120 -------------------------------------------------------------------------
1121 -- PROCEDURE disable_debug
1122 --
1123 -- Parameters
1124 -- None
1125 --
1126 -- Description
1127 -- Stop debugging
1128 --
1129 -- Modification History
1130 -- KMIZUTA 12-MAR-99 Created.
1131 --
1132 -------------------------------------------------------------------------
1133 procedure disable_debug
1134 is
1135 begin
1136 fa_rx_util_pkg.debug('Disabling debug...');
1137 if Request_Id = 0 then
1138 fnd_file.close;
1139 end if;
1140 m_debug_flag := false;
1141 if m_dbms_output then
1142 m_dbms_output := false;
1143 dbms_sql.close_cursor(m_output_cursor);
1144 end if;
1145 end disable_debug;
1146
1147 -------------------------------------------------------------------------
1148 -- PROCEDURE debug_enabled
1149 --
1150 -- Parameters
1151 -- None
1152 --
1153 -- Returns
1154 -- Boolean Returns true if debugging is enabled.
1155 --
1156 -- Description
1157 -- Checks to see if debugging is enabled. Call this function
1158 -- if you want to have a block of code run only if debugging
1159 -- is enabled.
1160 -- If you have a whole bunch offa_rx_util_pkg.debug() calls in succession,
1161 -- then it is preferable if you first check to see if debuggin
1162 -- is enabled.
1163 --
1164 -- Modification History
1165 -- KMIZUTA 12-MAR-99 Created.
1166 --
1167 -------------------------------------------------------------------------
1168 function debug_enabled return boolean
1169 is
1170 begin
1171 return m_debug_flag;
1172 end debug_enabled;
1173
1174 PROCEDURE dbms_log(msg IN VARCHAR2)
1175 IS
1176 maxidx NUMBER;
1177 rows NUMBER;
1178 BEGIN
1179 maxidx := trunc(lengthb(msg)/255);
1180 for idx in 0..maxidx loop
1181
1182 dbms_sql.bind_variable(m_output_cursor, 'b_msg', substrb(msg,idx*255+1,255));
1183 rows := dbms_sql.execute(m_output_cursor);
1184 -- dbms_output.put_line(substrb(msg,idx*255+1,255));
1185 end loop;
1186 END dbms_log;
1187
1188 -------------------------------------------------------------------------
1189 -- PROCEDURE debug
1190 --
1191 -- Parameters
1192 -- msg String to be logged to the debug file
1193 --
1194 -- Description
1195 -- Send debug message to the debug (log) file.
1196 --
1197 -- Modification History
1198 -- KMIZUTA 12-MAR-99 Created.
1199 --
1200 -------------------------------------------------------------------------
1201 procedure debug(msg in varchar2)
1202 is
1203 idx number;
1204 maxidx number;
1205 rows number;
1206 begin
1207 if not m_debug_flag then return;
1208 end if;
1209
1210 if not m_dbms_output then
1211 fnd_file.put_line(fnd_file.log,msg);
1212 else
1213 dbms_log(msg);
1214 end if;
1215
1216 exception
1217 when others then
1218 begin
1219 fnd_file.put_line(fnd_file.log, '**** Exception occurred while outputing to file...');
1220 exception when others then null;
1221 end;
1222 end debug;
1223
1224
1225 -------------------------------------------------------------------------
1226 --
1227 -- PROCEDURE log
1228 -- PROCEDURE out
1229 --
1230 -- Parameters
1231 -- msg String to be logged/outputted
1232 --
1233 -- Description
1234 -- These files are wrappers to other routines. It basically handles
1235 -- your output to log and output files. This was added here so that
1236 -- if you ever need to quickly make it so that all output goes
1237 -- through dbms_output, you can just modify these functions.
1238 --
1239 -- Modification History
1240 -- KMIZUTA 12-MAR-99 Created.
1241 --
1242 -------------------------------------------------------------------------
1243 procedure log(msg in varchar2)
1244 is
1245 begin
1246 if not m_dbms_output then
1247 fnd_file.put_line(fnd_file.log,msg);
1248 else
1249 dbms_log(msg);
1250 end if;
1251
1252 exception
1253 when others then
1254 begin
1255 fnd_file.put_line(fnd_file.log, '**** Exception occurred while outputing to file...');
1256 exception when others then null;
1257 end;
1258 end log;
1259
1260 procedure out(msg in varchar2)
1261 is
1262 begin
1263 if not m_dbms_output then
1264 fnd_file.put_line(fnd_file.output,msg);
1265 else
1266 dbms_log(msg);
1267 end if;
1268
1269 exception
1270 when others then
1271 begin
1272 fnd_file.put_line(fnd_file.log, '**** Exception occurred while outputing to file...');
1273 exception when others then null;
1274 end;
1275 end out;
1276
1277
1278 -------------------------------------------------------------------------
1279 -- PROCEDURE enable_trace, disable_trace
1280 --
1281 -- Parameters
1282 -- None
1283 --
1284 -- Description
1285 -- Enable and disable SQL Tracing
1286 --
1287 -- Modification History
1288 -- KMIZUTA 12-MAR-99 Created.
1289 --
1290 -------------------------------------------------------------------------
1291 procedure enable_trace
1292 is
1293 begin
1294 --* bug#3344455, rravunny dbms_session.set_sql_trace(true);
1295 Null;
1296 end enable_trace;
1297
1298
1299 procedure disable_trace
1300 is
1301 begin
1302 --* bug#3344455, rravunny dbms_session.set_sql_trace(false);
1303 Null;
1304 end disable_trace;
1305
1306 end fa_rx_util_pkg;
[Home] [Help]