[Home] [Help]
PACKAGE BODY: APPS.FA_RX_PUBLISH
Source
1 package body fa_rx_publish as
2 /* $Header: FARXPBSB.pls 120.6 2005/10/25 10:46:10 rravunny ship $ */
3
4 ------------------------------------------------
5 -- CONSTANTS
6 ------------------------------------------------
7 NEWLINE constant varchar2(10) := fnd_global.local_chr(10);
8 -- convert(chr(10), 'US7ASCII');
9
10 ------------------------------------------------
11 -- Private types and private globals
12 ------------------------------------------------
13 g_print_debug boolean := fa_cache_pkg.fa_print_debug;
14
15 --
16 -- Report Level
17 -- GLCHEN
18 type reportrec is record (
19 request_id number,
20 multiformat boolean,
21 report_id number,
22 attribute_set varchar2(30),
23 output_format varchar2(30),
24 display_report_title varchar2(1),
25 display_set_of_books varchar2(1),
26 display_functional_currency varchar2(1),
27 display_submission_date varchar2(1),
28 display_current_page varchar2(1),
29 display_total_page varchar2(1),
30 report_title varchar2(240),
31 set_of_books_id number,
32 set_of_books_name varchar2(200),
33 functional_currency_prompt varchar2(100),
34 functional_currency varchar2(100),
35 real_submission_date date,
36 submission_date varchar2(100),
37 current_page_prompt varchar2(100),
38 total_page_prompt varchar2(100),
39 page_width number,
40 page_height number,
41 nls_end_of_report varchar2(200),
42 nls_no_data_found varchar2(100),
43 date_prompt VARCHAR2(100), /* bug #1401113, rravunny */
44 conc_appname VARCHAR2(50),
45 concurrent_program_name varchar2(30));
46 m_report reportrec;
47
48
49 --
50 -- Direct Select RX Arguments
51 --
52 type argumentrec is record (
53 where_clause varchar2(100),
54 value varchar2(240),
55 datatype varchar2(10));
56 type argumenttab is table of argumentrec index by binary_integer;
57 m_arguments argumenttab;
58 m_argument_count number;
59
60 --
61 -- Format Level
62 --
63 type formatrec is record (
64 report_id number,
65 request_id number,
66 attribute_set varchar2(30),
67 conc_appname VARCHAR2(240),
68 concurrent_program_name varchar2(30),
69 interface_table varchar2(80),
70 where_clause_api varchar2(80),
71 display_parameters varchar2(3),
72 display_page_break varchar2(3),
73 group_display_type varchar2(20),
74 complex_flag varchar2(1),
75 group_id number,
76 where_clause varchar2(400),
77 default_date_format varchar2(20),
78 default_date_time_format varchar2(30));
79 type formattab is table of formatrec index by binary_integer;
80 m_formats formattab;
81 m_format_count number;
82 s_current_format_idx number;
83
84
85 --
86 -- Parameters
87 --
88 type paramrec is record (
89 name varchar2(100),
90 value varchar2(240),
91 display_flag VARCHAR2(3),
92 format_type varchar2(30),
93 flex_value_set_id NUMBER,
94 flex_value_set_name VARCHAR2(60),
95 param_idx number,
96 column_name varchar2(30),
97 operator varchar2(30));
98 type paramtab is table of paramrec index by binary_integer;
99 m_params paramtab;
100 m_params_with_actual_value paramtab;
101 m_param_count number;
102 m_param_display_count NUMBER;
103 s_current_param_idx number;
104
105 --
106 -- Break Levels
107 --
108 type breakrec is record (
109 columncnt number,
110 summarycnt number
111 );
112 type breaktab is table of breakrec index by binary_integer;
113 m_breaks breaktab;
114 m_break_count number;
115 s_current_break_idx number;
116
117
118 --
119 -- Columns
120 --
121 type columnrec is record (
122 attribute_name varchar2(80),
123 column_name varchar2(30),
124 ordering varchar(30),
125 display_length number,
126 display_format varchar2(30),
127 display_status varchar2(30),
128 break varchar2(1),
129 break_group_level number,
130 currency_column varchar2(30),
131 currency_column_id number,
132 precision number,
133 minimum_accountable_unit number,
134 units number,
135 format_mask varchar2(50),
136 --bug 2848621 lgandhi
137 -- Column values
138 --
139 vvalue varchar2(240),
140 dvalue date,
141 nvalue number
142 );
143 type columntab is table of columnrec index by binary_integer;
144 m_columns columntab;
145 m_column_count number;
146 m_displayed_column_count number;
147 s_current_column_idx number;
148
149 --
150 -- Summary Columns
151 --
152 type summaryrec is record (
153 column_name varchar2(30),
154 source_column_id number,
155 function varchar2(15),
156 summary_prompt varchar2(80),
157 print_level number,
158 reset_level number,
159 compute_level number,
160 value number
161 );
162 type summarytab is table of summaryrec index by binary_integer;
163 m_summaries summarytab;
164 m_summary_count number;
165 s_current_summary_idx number;
166
167
168
169 --
170 --
171 --
172 TYPE bindtab IS TABLE OF VARCHAR2(240) INDEX BY BINARY_INTEGER;
173 m_binds bindtab;
174 m_bind_count NUMBER;
175 s_bind_idx NUMBER;
176
177 m_page_break_cnt number;
178 m_page_carry_forward_cnt number;
179 m_report_break_cnt number;
180
181 m_main_cursor integer;
182 m_row_count number;
183 m_max_rows number;
184
185
186
187
188 ----------------------------------------------------------------
189 -- PRIVATE Procedures/Functions
190 ----------------------------------------------------------------
191
192 function get_select_list return varchar2;
193 function get_from_clause return varchar2;
194 function get_where_clause return varchar2;
195 function get_order_by_clause return varchar2;
196
197 function is_multi_format_report(p_request_id in number) return boolean;
198 procedure Expand_Complex_Multiformat(p_formats in formattab, p_count in number);
199 procedure Validate_Report;
200 PROCEDURE bind_variables(c IN INTEGER);
201
202
203 ---------------------------------------------------------
204 -- Procedure populate_report
205 --
206 -- Populates the m_report structure
207 ---------------------------------------------------------
208 procedure populate_report(
209 p_request_id in number,
210 p_report_id in number,
211 p_attribute_set in varchar2,
212 p_output_format in varchar2)
213 is
214 appname varchar2(50);
215 begin
216 IF (g_print_debug) THEN
217 fa_rx_util_pkg.debug('is_multi_format_report: ' || 'populate_report()+');
218 END IF;
219
220 m_report.multiformat := is_multi_format_report(p_request_id);
221 if m_report.multiformat then
222 m_report.request_id := p_request_id;
223 m_report.output_format := p_output_format;
224
225 select sub_report_id, sub_attribute_set
226 into m_report.report_id, m_report.attribute_set
227 from fa_rx_multiformat_reps
228 where request_id = p_request_id
229 and seq_number = (
230 select min(seq_number) from fa_rx_multiformat_reps
231 where request_id = p_request_id)
232 and rownum=1;
233
234 else
235 m_report.request_id := p_request_id;
236 m_report.output_format := p_output_format;
237
238 m_report.report_id := p_report_id;
239 m_report.attribute_set := p_attribute_set;
240 end if;
241
242 IF (g_print_debug) THEN
243 fa_rx_util_pkg.debug('is_multi_format_report: ' ||
244 'Request ID = '||p_request_id||newline||
245 'Report ID = '||p_report_id||newline||
246 'Attribute Set = '||p_attribute_set||newline||
247 'Output Format = '||p_output_format);
248 fa_rx_util_pkg.debug('is_multi_format_report: ' || 'Getting attribute_set flags...');
249 END IF;
250 select
251 nvl(print_title, 'Y') print_title,
252 nvl(print_sob_flag, 'Y') print_sob_flag,
253 nvl(print_func_curr_flag, 'Y') print_func_curr_flag,
254 nvl(print_submission_date,'Y') print_submission_date,
255 nvl(print_current_page, 'Y') print_current_pages,
256 nvl(print_total_pages, 'N') print_total_pages,
257 report_title report_title,
258 nvl(page_width, 0) page_width,
259 nvl(page_height, 0) page_height
260 into
261 m_report.display_report_title,
262 m_report.display_set_of_books,
263 m_report.display_functional_currency,
264 m_report.display_submission_date,
265 m_report.display_current_page,
266 m_report.display_total_page,
267 m_report.report_title,
268 m_report.page_width, m_report.page_height
269 from
270 fa_rx_attrsets
271 where
272 report_id = m_report.report_id and
273 attribute_set = m_report.attribute_set;
274
275 IF (g_print_debug) THEN
276 fa_rx_util_pkg.debug('is_multi_format_report: ' || 'Get report title...');
277 END IF;
278 if m_report.report_title is null then
279 select distinct substrb (user_program_name, 1, 100)
280 into m_report.report_title
281 from fa_rx_reports_v
282 where report_id = m_report.report_id;
283 end if;
284
285 --
286 -- SOB and functional currency cannot be
287 -- initialized until the format level
288 --
289
290 IF (g_print_debug) THEN
291 fa_rx_util_pkg.debug('is_multi_format_report: ' || 'Get NLS flags...');
292 END IF;
293 select meaning into m_report.functional_currency_prompt
294 from fnd_lookups where lookup_type='FARX_NLS_PARAMS' and lookup_code = 'CURRENCY_PROMPT';
295
296 --* bug #1401113, rravunny
297 select meaning into m_report.date_prompt
298 from fnd_lookups where lookup_type='FARX_NLS_PARAMS' and lookup_code = 'DATE_PROMPT';
299
300 select meaning into m_report.current_page_prompt
301 from fnd_lookups where lookup_type='FARX_NLS_PARAMS' and lookup_code = 'CURRENT_PAGE';
302
303 select meaning into m_report.total_page_prompt
304 from fnd_lookups where lookup_type='FARX_NLS_PARAMS' and lookup_code = 'TOTAL_PAGE';
305
306 select meaning into m_report.nls_end_of_report
307 from fnd_lookups where lookup_type='FARX_NLS_PARAMS' and lookup_code = 'END_OF_REPORT';
308
309 select meaning into m_report.nls_no_data_found
310 from fnd_lookups where lookup_type='FARX_NLS_PARAMS' and lookup_code = 'NO_DATA_FOUND';
311
312 m_report.real_submission_date := sysdate;
313
314 get_report_name(
315 m_report.report_id,
316 m_report.conc_appname,
317 m_report.concurrent_program_name);
318
319 if m_report.output_format in ('CSV', 'HTML', 'TAB') then
320 m_report.page_width := 0;
321 m_report.page_height := 0;
322 end if;
323
324 IF m_report.page_height = 0 THEN
325 m_report.display_current_page := 'N';
326 m_report.display_total_page := 'N';
327 END IF;
328 end populate_report;
329
330
331 ---------------------------------------------------------
332 -- Procedure populate_sob
333 --
334 -- Populates the set of books info in the m_report structure
335 ---------------------------------------------------------
336 procedure populate_sob is
337 c number;
338 rows number;
339 where_clause varchar2(1000);
340 sqlstmt varchar2(1000);
341 begin
342 --
343 -- This routine looks for the column SET_OF_BOOKS_ID
344 -- from the interface table. It assumes that
345 -- that there will only be one distinct value
346 -- in this column
347 --
348 m_report.set_of_books_id := null;
349 --
350 -- Per instructions from bug 1082862, FIRST_ROWS hint has been taken out.
351 sqlstmt := 'SELECT ORGANIZATION_NAME, FUNCTIONAL_CURRENCY_CODE '||NEWLINE||
352 Get_From_Clause||NEWLINE||get_where_clause;
353 c := dbms_sql.open_cursor;
354 IF (g_print_debug) THEN
355 fa_rx_util_pkg.debug('is_multi_format_report: ' || sqlstmt);
356 END IF;
357 fa_rx_util_pkg.debug('Parsing statement : '||To_char(Sysdate, 'YY/MM/DD HH24:MI:SS'));
358 dbms_sql.parse(c, sqlstmt, dbms_sql.native);
359 IF (g_print_debug) THEN
360 fa_rx_util_pkg.debug('is_multi_format_report: ' || 'Binding statement');
361 END IF;
362 bind_variables(c);
363 fa_rx_util_pkg.debug('Defining columns : '||To_char(Sysdate, 'YY/MM/DD HH24:MI:SS'));
364 dbms_sql.define_column(c, 1, m_report.set_of_books_name, 100);
365 dbms_sql.define_column(c, 2, m_report.functional_currency, 100);
366 fa_rx_util_pkg.debug('Executing : '||To_char(Sysdate, 'YY/MM/DD HH24:MI:SS'));
367 rows := dbms_sql.execute(c);
368 fa_rx_util_pkg.debug('Fetching : '||To_char(Sysdate, 'YY/MM/DD HH24:MI:SS'));
369 rows := dbms_sql.fetch_rows(c);
370 fa_rx_util_pkg.debug('Got '||To_char(rows)||' row(s) : '||To_char(Sysdate, 'YY/MM/DD HH24:MI:SS'));
371 if rows = 0 then
372 --
373 -- The column did not exist in the database
374 -- Set SOB info to null;
375 --
376 fnd_message.set_name('OFA', 'FA_RX_NO_SOB_COLUMN');
377 IF (g_print_debug) THEN
378 fa_rx_util_pkg.debug('is_multi_format_report: ' || fnd_message.get);
379 END IF;
380 m_report.display_set_of_books := 'N';
381 m_report.display_functional_currency := 'N';
382 m_report.set_of_books_name := null;
383 m_report.functional_currency := null;
384
385 else
386
387 dbms_sql.column_value(c, 1, m_report.set_of_books_name);
388 dbms_sql.column_value(c, 2, m_report.functional_currency);
389
390 end if;
391 dbms_sql.close_cursor(c);
392
393 exception
394 when others then
395 -- no set of books
396 fnd_message.set_name('OFA', 'FA_RX_NO_SOB_COLUMN');
397 IF (g_print_debug) THEN
398 fa_rx_util_pkg.debug('is_multi_format_report: ' || fnd_message.get);
399 END IF;
400
401 m_report.display_set_of_books := 'N';
402 m_report.display_functional_currency := 'N';
403 m_report.set_of_books_name := null;
404 m_report.functional_currency := null;
405
406 if dbms_sql.is_open(c) then
407 dbms_sql.close_cursor(c);
408 end if;
409 return;
410 end populate_sob;
411
412
413 ---------------------------------------------------------
414 -- Procedure populate_arguments
415 --
416 -- Populates the m_arguments structure. For
417 -- Direct Select RX reports only.
418 ---------------------------------------------------------
419 procedure populate_arguments(
420 p_argument1 in varchar2 ,
421 p_argument2 in varchar2 ,
422 p_argument3 in varchar2 ,
423 p_argument4 in varchar2 ,
424 p_argument5 in varchar2 ,
425 p_argument6 in varchar2 ,
426 p_argument7 in varchar2 ,
427 p_argument8 in varchar2 ,
428 p_argument9 in varchar2 ,
429 p_argument10 in varchar2 ,
430 p_argument11 in varchar2 ,
431 p_argument12 in varchar2 ,
432 p_argument13 in varchar2 ,
433 p_argument14 in varchar2 ,
434 p_argument15 in varchar2 ,
435 p_argument16 in varchar2 ,
436 p_argument17 in varchar2 ,
437 p_argument18 in varchar2 ,
438 p_argument19 in varchar2 ,
439 p_argument20 in varchar2 ,
440 p_argument21 in varchar2 ,
441 p_argument22 in varchar2 ,
442 p_argument23 in varchar2 ,
443 p_argument24 in varchar2 ,
444 p_argument25 in varchar2 ,
445 p_argument26 in varchar2 ,
446 p_argument27 in varchar2 ,
447 p_argument28 in varchar2 ,
448 p_argument29 in varchar2 ,
449 p_argument30 in varchar2 ,
450 p_argument31 in varchar2 ,
451 p_argument32 in varchar2 ,
452 p_argument33 in varchar2 ,
453 p_argument34 in varchar2 ,
454 p_argument35 in varchar2 ,
455 p_argument36 in varchar2 ,
456 p_argument37 in varchar2 ,
457 p_argument38 in varchar2 ,
458 p_argument39 in varchar2 ,
459 p_argument40 in varchar2 ,
460 p_argument41 in varchar2 ,
461 p_argument42 in varchar2 ,
462 p_argument43 in varchar2 ,
463 p_argument44 in varchar2 ,
464 p_argument45 in varchar2 ,
465 p_argument46 in varchar2 ,
466 p_argument47 in varchar2 ,
467 p_argument48 in varchar2 ,
468 p_argument49 in varchar2 ,
469 p_argument50 in varchar2 ,
470 p_argument51 in varchar2 ,
471 p_argument52 in varchar2 ,
472 p_argument53 in varchar2 ,
473 p_argument54 in varchar2 ,
474 p_argument55 in varchar2 ,
475 p_argument56 in varchar2 ,
476 p_argument57 in varchar2 ,
477 p_argument58 in varchar2 ,
478 p_argument59 in varchar2 ,
479 p_argument60 in varchar2 ,
480 p_argument61 in varchar2 ,
481 p_argument62 in varchar2 ,
482 p_argument63 in varchar2 ,
483 p_argument64 in varchar2 ,
484 p_argument65 in varchar2 ,
485 p_argument66 in varchar2 ,
486 p_argument67 in varchar2 ,
487 p_argument68 in varchar2 ,
488 p_argument69 in varchar2 ,
489 p_argument70 in varchar2 ,
490 p_argument71 in varchar2 ,
491 p_argument72 in varchar2 ,
492 p_argument73 in varchar2 ,
493 p_argument74 in varchar2 ,
494 p_argument75 in varchar2 ,
495 p_argument76 in varchar2 ,
496 p_argument77 in varchar2 ,
497 p_argument78 in varchar2 ,
498 p_argument79 in varchar2 ,
499 p_argument80 in varchar2 ,
500 p_argument81 in varchar2 ,
501 p_argument82 in varchar2 ,
502 p_argument83 in varchar2 ,
503 p_argument84 in varchar2 ,
504 p_argument85 in varchar2 ,
505 p_argument86 in varchar2 ,
506 p_argument87 in varchar2 ,
507 p_argument88 in varchar2 ,
508 p_argument89 in varchar2 ,
509 p_argument90 in varchar2 ,
510 p_argument91 in varchar2 ,
511 p_argument92 in varchar2 ,
512 p_argument93 in varchar2 ,
513 p_argument94 in varchar2 ,
514 p_argument95 in varchar2 ,
515 p_argument96 in varchar2 ,
516 p_argument97 in varchar2 ,
517 p_argument98 in varchar2 ,
518 p_argument99 in varchar2 ,
519 p_argument100 in varchar2)
520 is
521 cursor cargs is
522 select f.end_user_column_name ,
523 decode(v.format_type,
524 'C', 'VARCHAR2',
525 'D', 'DATE',
526 'I', 'DATE',
527 'N', 'NUMBER',
528 'T', 'DATE',
529 'X', 'DATE',
530 'Y', 'DATE',
531 'VARCHAR2')
532 from
533 fnd_application a,
534 fnd_concurrent_programs p,
535 fnd_descr_flex_column_usages f,
536 fnd_flex_value_sets v
537 WHERE
538 a.application_short_name = m_report.conc_appname AND
539 a.application_id = p.application_id AND
540 p.concurrent_program_name = m_report.concurrent_program_name AND
541 f.descriptive_flexfield_name = '$SRS$.'||p.concurrent_program_name AND
542 f.enabled_flag = 'Y' AND
543 f.flex_value_set_id = v.flex_value_set_id
544 ORDER BY f.column_seq_num;
545
546 l_column_name fnd_descr_flex_column_usages.end_user_column_name%type;
547 l_data_type varchar2(10);
548 begin
549 IF (g_print_debug) THEN
550 fa_rx_util_pkg.debug('is_multi_format_report: ' || 'FA_RX_PUBLISH.populate_arguments()+');
551 END IF;
552
553 m_arguments(1).value := p_argument1;
554 m_arguments(2).value := p_argument2;
555 m_arguments(3).value := p_argument3;
556 m_arguments(4).value := p_argument4;
557 m_arguments(5).value := p_argument5;
558 m_arguments(6).value := p_argument6;
559 m_arguments(7).value := p_argument7;
560 m_arguments(8).value := p_argument8;
561 m_arguments(9).value := p_argument9;
562 m_arguments(10).value := p_argument10;
563 m_arguments(11).value := p_argument11;
564 m_arguments(12).value := p_argument12;
565 m_arguments(13).value := p_argument13;
566 m_arguments(14).value := p_argument14;
567 m_arguments(15).value := p_argument15;
568 m_arguments(16).value := p_argument16;
569 m_arguments(17).value := p_argument17;
570 m_arguments(18).value := p_argument18;
571 m_arguments(19).value := p_argument19;
572 m_arguments(20).value := p_argument20;
573 m_arguments(21).value := p_argument21;
574 m_arguments(22).value := p_argument22;
575 m_arguments(23).value := p_argument23;
576 m_arguments(24).value := p_argument24;
577 m_arguments(25).value := p_argument25;
578 m_arguments(26).value := p_argument26;
579 m_arguments(27).value := p_argument27;
580 m_arguments(28).value := p_argument28;
581 m_arguments(29).value := p_argument29;
582 m_arguments(30).value := p_argument30;
583 m_arguments(31).value := p_argument31;
584 m_arguments(32).value := p_argument32;
585 m_arguments(33).value := p_argument33;
586 m_arguments(34).value := p_argument34;
587 m_arguments(35).value := p_argument35;
588 m_arguments(36).value := p_argument36;
589 m_arguments(37).value := p_argument37;
590 m_arguments(38).value := p_argument38;
591 m_arguments(39).value := p_argument39;
592 m_arguments(40).value := p_argument40;
593 m_arguments(41).value := p_argument41;
594 m_arguments(42).value := p_argument42;
595 m_arguments(43).value := p_argument43;
596 m_arguments(44).value := p_argument44;
597 m_arguments(45).value := p_argument45;
598 m_arguments(46).value := p_argument46;
599 m_arguments(47).value := p_argument47;
600 m_arguments(48).value := p_argument48;
601 m_arguments(49).value := p_argument49;
602 m_arguments(50).value := p_argument50;
603 m_arguments(51).value := p_argument51;
604 m_arguments(52).value := p_argument52;
605 m_arguments(53).value := p_argument53;
606 m_arguments(54).value := p_argument54;
607 m_arguments(55).value := p_argument55;
608 m_arguments(56).value := p_argument56;
609 m_arguments(57).value := p_argument57;
610 m_arguments(58).value := p_argument58;
611 m_arguments(59).value := p_argument59;
612 m_arguments(60).value := p_argument60;
613 m_arguments(61).value := p_argument61;
614 m_arguments(62).value := p_argument62;
615 m_arguments(63).value := p_argument63;
616 m_arguments(64).value := p_argument64;
617 m_arguments(65).value := p_argument65;
618 m_arguments(66).value := p_argument66;
619 m_arguments(67).value := p_argument67;
620 m_arguments(68).value := p_argument68;
621 m_arguments(69).value := p_argument69;
622 m_arguments(70).value := p_argument70;
623 m_arguments(71).value := p_argument71;
624 m_arguments(72).value := p_argument72;
625 m_arguments(73).value := p_argument73;
626 m_arguments(74).value := p_argument74;
627 m_arguments(75).value := p_argument75;
628 m_arguments(76).value := p_argument76;
629 m_arguments(77).value := p_argument77;
630 m_arguments(78).value := p_argument78;
631 m_arguments(79).value := p_argument79;
632 m_arguments(80).value := p_argument80;
633 m_arguments(81).value := p_argument81;
634 m_arguments(82).value := p_argument82;
635 m_arguments(83).value := p_argument83;
636 m_arguments(84).value := p_argument84;
637 m_arguments(85).value := p_argument85;
638 m_arguments(86).value := p_argument86;
639 m_arguments(87).value := p_argument87;
640 m_arguments(88).value := p_argument88;
641 m_arguments(89).value := p_argument89;
642 m_arguments(90).value := p_argument90;
643 m_arguments(91).value := p_argument91;
644 m_arguments(92).value := p_argument92;
645 m_arguments(93).value := p_argument93;
646 m_arguments(94).value := p_argument94;
647 m_arguments(95).value := p_argument95;
648 m_arguments(96).value := p_argument96;
649 m_arguments(97).value := p_argument97;
650 m_arguments(98).value := p_argument98;
651 m_arguments(99).value := p_argument99;
652 m_arguments(100).value := p_argument100;
653
654 open cargs;
655 fetch cargs into l_column_name, l_data_type; -- DIRECT
656 fetch cargs into l_column_name, l_data_type; -- REPORT_ID
657 fetch cargs into l_column_name, l_data_type; -- ATTRIBUTE_SET
658 fetch cargs into l_column_name, l_data_type; -- FORMAT TYPE
659 m_argument_count := 0;
660 loop
661 fetch cargs into l_column_name, l_data_type;
662 exit when cargs%notfound;
663
664 m_argument_count := m_argument_count + 1;
665
666 if l_column_name like '[%]' THEN -- used for WHERE clause
667 m_arguments(m_argument_count).where_clause :=
668 substr(l_column_name, 2, length(l_column_name)-2); -- Strip [ and ]
669 m_arguments(m_argument_count).datatype := l_data_type;
670 else
671 m_arguments(m_argument_count).where_clause := null;
672 m_arguments(m_argument_count).datatype := l_data_type;
673 end if;
674 end loop;
675 close cargs;
676
677 IF (g_print_debug) THEN
678 fa_rx_util_pkg.debug('is_multi_format_report: ' || 'FA_RX_PUBLISH.populate_arguments()-');
679 END IF;
680 end populate_arguments;
681
682
683
684 procedure check_group_display_type(fidx in number)
685 is
686 cursor c is select Least(display_length, 255) display_length
687 from fa_rx_rep_columns
688 where report_id = m_formats(fidx).report_id
689 and attribute_set = m_formats(fidx).attribute_set
690 and break = 'Y'
691 and break_group_level >=
692 decode(m_formats(fidx).display_page_break, 'Y', 2, 1)
693 and display_status = 'YES'
694 order by break_group_level, attribute_counter;
695 crec c%rowtype;
696
697 current_pos number;
698 max_len number;
699
700 begin
701 if m_report.output_format in ('CSV', 'HTML', 'TAB') then
702 --
703 -- CSV and HTML and TAB always use GROUP LEFT
704 m_formats(fidx).group_display_type := 'GROUP LEFT';
705 return;
706 end if;
707
708 if m_formats(fidx).group_display_type = 'GROUP ABOVE' then
709 -- Nothing to check
710 return;
711 end if;
712
713 if m_report.page_width = 0 or m_report.page_width is null then
714 -- Nothing to check
715 return;
716 end if;
717 current_pos := -2; -- Adds 2 to take care of spaces except first item in line
718 for crec in c loop
719 if current_pos + crec.display_length + 2 > m_report.page_width then
720 current_pos := -2;
721 end if;
722
723 current_pos := current_pos + crec.display_length + 2;
724 end loop;
725
726 select max(Least(display_length, 255)) into max_len
727 from fa_rx_rep_columns
728 where report_id = m_formats(fidx).report_id
729 and attribute_set = m_formats(fidx).attribute_set
730 and nvl(break, 'N') = 'N'
731 and display_status = 'YES';
732 if current_pos + max_len + 2 > m_report.page_width then
733 m_formats(fidx).group_display_type := 'GROUP ABOVE';
734 end if;
735 end check_group_display_type;
736
737
738 ---------------------------------------------------------
739 -- Procedure populate_formats
740 --
741 -- Populates the m_formats structure
742 ---------------------------------------------------------
743 procedure populate_formats
744 is
745 cursor cformat is
746 select
747 m.sub_report_id,
748 m.sub_request_id,
749 m.sub_attribute_set,
750 m.group_id,
751 m.complex_flag,
752 m.seq_number
753 from fa_rx_multiformat_reps m
754 where m.request_id = m_report.request_id
755 order by m.group_id, m.seq_number;
756 rformat cformat%rowtype;
757
758 c integer;
759 rows number;
760 sqlstmt varchar2(2000);
761
762 appname varchar2(240);
763 l_formats formattab;
764 idx number;
765 begin
766 IF (g_print_debug) THEN
767 fa_rx_util_pkg.debug('is_multi_format_report: ' || 'populate_formats()+');
768 END IF;
769
770 m_format_count := 0;
771
772 --
773 -- First check to see if this is a multi-format report
774 idx := 1;
775 open cformat;
776 loop
777 fetch cformat into rformat;
778 exit when cformat%notfound;
779
780 l_formats(idx).report_id := rformat.sub_report_id;
781 l_formats(idx).request_id := rformat.sub_request_id;
782 l_formats(idx).attribute_set := rformat.sub_attribute_set;
783 l_formats(idx).group_id := rformat.group_id;
784 l_formats(idx).complex_flag := rformat.complex_flag;
785 l_formats(idx).where_clause := null;
786
787 select
788 nvl(print_page_break_cols, 'N') print_page_break_cols,
789 nvl(print_parameters, 'Y') print_parameters,
790 nvl(group_display_type, 'GROUP LEFT') group_display_type,
791 default_date_format, default_date_time_format
792 into
793 l_formats(idx).display_page_break,
794 l_formats(idx).display_parameters,
795 l_formats(idx).group_display_type,
796 l_formats(idx).default_date_format,
797 l_formats(idx).default_date_time_format
798 from fa_rx_attrsets
799 where report_id = rformat.sub_report_id and
800 attribute_set = rformat.sub_attribute_set;
801
802 select interface_table, where_clause_api
803 into l_formats(idx).interface_table,
804 l_formats(idx).where_clause_api
805 from fa_rx_reports
806 where report_id = rformat.sub_report_id;
807
808 get_report_name(l_formats(idx).report_id,
809 l_formats(idx).conc_appname,
810 l_formats(idx).concurrent_program_name);
811
812
813 if (idx > 1) then
814 if(l_formats(idx-1).complex_flag = 'Y' and
815 (l_formats(idx).complex_flag <> 'Y' or
816 l_formats(idx).group_id <> l_formats(idx-1).group_id)) then
817 Expand_Complex_Multiformat(l_formats, idx-1);
818 l_formats(1) := l_formats(idx);
819 idx := 1;
820 end if;
821 end if;
822
823 if l_formats(idx).complex_flag = 'Y' then
824 IF (g_print_debug) THEN
825 fa_rx_util_pkg.debug('is_multi_format_report: ' || 'Complex format found - '||to_char(l_formats(idx).group_id));
826 END IF;
827
828 l_formats(idx).display_page_break := 'Y';
829 idx := idx + 1;
830 else
831 IF (g_print_debug) THEN
832 fa_rx_util_pkg.debug('is_multi_format_report: ' || 'Regular format found - '||l_formats(idx).concurrent_program_name);
833 END IF;
834 m_format_count := m_format_count + 1;
835 m_formats(m_format_count) := l_formats(1);
836 idx := 1;
837 end if;
838
839 IF m_report.page_height = 0 THEN
840 l_formats(idx).display_page_break := 'N';
841 END IF;
842 end loop;
843
844 idx := idx - 1;
845 if (idx > 1) then
846 if (l_formats(idx).complex_flag = 'Y') then
847 Expand_Complex_Multiformat(l_formats, idx);
848 end if;
849 end if;
850
851 close cformat;
852
853 if m_format_count = 0 then
854 -- This was not a multi-format report.
855 -- Copy over some of the information from m_report
856
857 m_format_count := 1;
858 m_formats(1).report_id := m_report.report_id;
859 m_formats(1).request_id := m_report.request_id;
860 m_formats(1).attribute_set := m_report.attribute_set;
861 m_formats(1).group_id := 1;
862
863 select
864 nvl(print_page_break_cols, 'N') print_page_break_cols,
865 nvl(print_parameters, 'N') print_parameters,
866 nvl(group_display_type, 'GROUP LEFT') group_display_type,
867 default_date_format, default_date_time_format
868 into m_formats(1).display_page_break, m_formats(1).display_parameters,
869 m_formats(1).group_display_type,
870 m_formats(1).default_date_format,
871 m_formats(1).default_date_time_format
872 from fa_rx_attrsets
873 where report_id = m_report.report_id and
874 attribute_set = m_report.attribute_set;
875
876 select interface_table, where_clause_api
877 into m_formats(m_format_count).interface_table,
878 m_formats(m_format_count).where_clause_api
879 from fa_rx_reports
880 where report_id = m_report.report_id;
881
882
883 get_report_name(
884 m_formats(m_format_count).report_id,
885 m_formats(m_format_count).conc_appname,
886 m_formats(m_format_count).concurrent_program_name);
887
888 check_group_display_type(m_format_count);
889
890 IF m_report.page_height = 0 THEN
891 l_formats(1).display_page_break := 'N';
892 END IF;
893 end if;
894
895 s_current_format_idx := 1;
896
897 --
898 -- Initialize the first one
899 if (m_formats(s_current_format_idx).default_date_format is not null) then
900 fnd_date.initialize(m_formats(s_current_format_idx).default_date_format,
901 m_formats(s_current_format_idx).default_date_time_format);
902 end if;
903 m_report.submission_date := fnd_date.date_to_displaydt(m_report.real_submission_date);
904 end populate_formats;
905
906 ---------------------------------------------------------
907 -- Procedure populate_parameters
908 --
909 -- Populates the m_parameters structure
910 ---------------------------------------------------------
911 procedure populate_parameters(p_request_type IN VARCHAR2, p_request_id IN NUMBER)
912 is
913 cursor cparam(l_request_id IN NUMBER) IS
914 select
915 d.form_left_prompt,
916 v.format_type,
917 d.display_flag,
918 v.flex_value_set_id,
919 v.flex_value_set_name
920 from
921 fnd_descr_flex_col_usage_vl d,
922 fnd_flex_value_sets v,
923 fnd_concurrent_programs c,
924 fnd_concurrent_requests r
925 WHERE
926 r.request_id = l_request_id
927 AND c.application_id = r.program_application_id
928 AND c.concurrent_program_id = r.concurrent_program_id
929 and d.application_id = c.application_id
930 and d.descriptive_flexfield_name = '$SRS$.'||c.concurrent_program_name
931 and d.enabled_flag = 'Y'
932 -- and d.flex_value_set_application_id = v.application_id
933 and d.flex_value_set_id = v.flex_value_set_id
934 order by d.column_seq_num;
935
936 rparam cparam%rowtype;
937 flex_value_meaning VARCHAR2(240);
938 sqlstmt varchar2(2000);
939 sep varchar2(10);
940
941 c integer;
942 rows number;
943 len number;
944 dvalue date;
945 idx number;
946
947 max_param_idx number := 0;
948 begin
949 IF (g_print_debug) THEN
950 fa_rx_util_pkg.debug('is_multi_format_report: ' || 'Getting parameters for request - '||To_char(p_request_id));
951 END IF;
952
953 fa_rx_shared_pkg.clear_flex_val_cache;
954
955 m_param_count := 0;
956 m_param_display_count := 0;
957
958 --
959 -- Check the descriptive flexfield definition for the concurrent program parameters
960 -- (Descriptive flexfield name = $SRS$.<CONCURRENT_PROGRAM_NAME>
961 open cparam(p_request_id);
962 idx := 0;
963 loop
964 fetch cparam into rparam;
965 exit when cparam%notfound;
966 idx := idx + 1;
967
968 m_param_count := m_param_count + 1;
969 m_params(m_param_count).name := rparam.form_left_prompt;
970 m_params(m_param_count).format_type := rparam.format_type;
971 m_params(m_param_count).flex_value_set_id := rparam.flex_value_set_id;
972 m_params(m_param_count).flex_value_set_name := rparam.flex_value_set_name;
973 m_params(m_param_count).param_idx := idx;
974 m_params(m_param_count).display_flag := rparam.display_flag;
975
976 IF Upper(p_request_type) = 'DIRECT' AND idx <= 4 THEN
977 m_params(m_param_count).display_flag := 'N';
978 ELSIF Upper(p_request_type) = 'SUBMIT' AND idx <= 6 THEN
979 m_params(m_param_count).display_flag := 'N';
980 ELSIF rparam.display_flag = 'Y' then
981 m_param_display_count := m_param_display_count + 1;
982 end if;
983 end loop;
984 close cparam;
985 max_param_idx := idx;
986
987 IF idx = 0 then
988 IF (g_print_debug) THEN
989 fa_rx_util_pkg.debug('is_multi_format_report: ' || 'There were no parameters for this report');
990 END IF;
991 return;
992 ELSIF m_param_count = 0 then
993 IF (g_print_debug) THEN
994 fa_rx_util_pkg.debug('is_multi_format_report: ' || 'There were no displayed parameters for this report');
995 END IF;
996 return;
997 end if;
998 IF (g_print_debug) THEN
999 fa_rx_util_pkg.debug('is_multi_format_report: ' || 'Found '||to_char(m_param_count)||' parameter(s).');
1000 END IF;
1001
1002
1003 --
1004 -- Build the SELECT statement which will retrieve the parameter values
1005 -- from FND_CONCURRENT_REQUESTS (and FND_CONC_REQUEST_ARGUMENTS)
1006 sqlstmt := 'SELECT ';
1007 for idx in 1..m_param_count loop
1008 if m_params(idx).param_idx <= 25 then
1009 sqlstmt := sqlstmt||sep||'r.argument'||to_char(m_params(idx).param_idx);
1010 else
1011 sqlstmt := sqlstmt||sep||'rs.argument'||to_char(m_params(idx).param_idx);
1012 end if;
1013 sep := NEWLINE||',';
1014 end loop;
1015 sqlstmt := sqlstmt||NEWLINE||'from fnd_concurrent_requests r';
1016 if max_param_idx > 25 then
1017 sqlstmt := sqlstmt||', fnd_conc_request_arguments rs';
1018 end if;
1019
1020 sqlstmt := sqlstmt ||NEWLINE||'where r.request_id = to_char(:b_request_id)'; /* bug 2276534, rravunny */
1021 if m_param_count > 25 then
1022 sqlstmt := sqlstmt||NEWLINE||'and r.request_id = rs.request_id';
1023 end if;
1024
1025
1026 IF (g_print_debug) THEN
1027 fa_rx_util_pkg.debug('is_multi_format_report: ' || 'SQL Statement...'||NEWLINE||sqlstmt);
1028 END IF;
1029
1030 --
1031 -- Get the parameter values
1032 c := dbms_sql.open_cursor;
1033 dbms_sql.parse(c, sqlstmt, dbms_sql.native);
1034 dbms_sql.bind_variable(c, ':b_request_id', p_request_id); /* bug 2276534, rravunny */
1035 for idx in 1..m_param_count loop
1036 dbms_sql.define_column(c, idx, m_params(idx).value, 240);
1037 end loop;
1038 rows := dbms_sql.execute(c);
1039 rows := dbms_sql.fetch_rows(c);
1040 if rows = 0 then raise no_data_found;
1041 end if;
1042 for idx in 1..m_param_count loop
1043 dbms_sql.column_value(c, idx, m_params(idx).value);
1044 end loop;
1045 dbms_sql.close_cursor(c);
1046
1047 m_params_with_actual_value := m_params;
1048 --
1049 -- Try to reformat date values
1050 for idx in 1..m_param_count loop
1051 m_params(idx).value := substrb(fa_rx_shared_pkg.get_flex_val_meaning(m_params(idx).flex_value_set_id,
1052 m_params(idx).flex_value_set_name,
1053 m_params(idx).value), 1, 240);
1054
1055 if m_params(idx).format_type in ('D', 'I', 'T', 'X', 'Y', 'Z', 't') then
1056 begin
1057 dvalue := fnd_date.canonical_to_date(m_params(idx).value);
1058 exception
1059 when others then
1060 Null;
1061 --* len := length(m_params(idx).value);
1062 --* if len = 8 then
1063 --* dvalue := to_date(m_params(idx).value, 'YY/MM/DD');
1064 --* elsif len = 9 then
1065 --* dvalue := to_date(m_params(idx).value, 'DD-MON-YY');
1066 --* elsif len = 10 then
1067 --* dvalue := to_date(m_params(idx).value, 'YYYY/MM/DD');
1068 --* elsif len = 11 then
1069 --* dvalue := to_date(m_params(idx).value, 'DD-MON-YYYY');
1070 --* elsif len = 17 then
1071 --* dvalue := to_date(m_params(idx).value, 'YY/MM/DD HH24:MI:SS');
1072 --* elsif len = 18 then
1073 --* dvalue := to_date(m_params(idx).value, 'DD-MON-YY HH24:MI:SS');
1074 --* elsif len = 19 then
1075 --* dvalue := to_date(m_params(idx).value, 'YYYY/MM/DD HH24:MI:SS');
1076 --* elsif len = 20 then
1077 --* dvalue := to_date(m_params(idx).value, 'DD-MON-YYYY HH24:MI:SS');
1078 --* end if;
1079 end;
1080
1081 if m_params(idx).format_type in ('D', 'X') then
1082 m_params(idx).value := fnd_date.date_to_displaydate(dvalue);
1083 elsif m_params(idx).format_type in ('T', 'Y') then
1084 m_params(idx).value := fnd_date.date_to_displaydt(dvalue);
1085 elsif m_params(idx).format_type in ('I', 'Z', 't') then
1086 m_params(idx).value := to_char(dvalue, 'HH24:MI:SS');
1087 end if;
1088 end if;
1089
1090 IF (g_print_debug) THEN
1091 fa_rx_util_pkg.debug('is_multi_format_report: ' || '--> '||to_char(idx)||' = '||m_params(idx).value);
1092 END IF;
1093 end loop;
1094 end populate_parameters;
1095
1096
1097 ---------------------------------------------------------
1098 -- Function find_column
1099 --
1100 -- Returns the index of the column name from m_columns
1101 ---------------------------------------------------------
1102 function find_column(p_column_name in varchar2) return number
1103 is
1104 idx number;
1105 begin
1106 for idx in 1..m_column_count loop
1107 if m_columns(idx).column_name = p_column_name then
1108 return idx;
1109 end if;
1110 end loop;
1111
1112 return null;
1113 end find_column;
1114
1115 ---------------------------------------------------------
1116 -- Procedure populate_columns
1117 --
1118 -- Populates the m_columns structure
1119 ---------------------------------------------------------
1120 procedure populate_columns
1121 is
1122 cursor ccol is
1123 select
1124 attribute_name,
1125 column_name,
1126 ordering,
1127 display_length,
1128 display_format,
1129 nvl(display_status, 'NO') display_status,
1130 nvl(break, 'N') break,
1131 currency_column,
1132 precision,
1133 minimum_accountable_unit,
1134 units,
1135 format_mask,
1136 break_group_level
1137 from
1138 fa_rx_rep_columns
1139 where
1140 report_id = m_formats(s_current_format_idx).report_id
1141 and display_status = 'YES'
1142 and attribute_set = m_formats(s_current_format_idx).attribute_set
1143 order by decode(break, 'Y', 1, 2), break_group_level, attribute_counter;
1144 rcol ccol%rowtype;
1145
1146 l_displayed_columns number;
1147 l_current_break number;
1148 l_last_break number;
1149 begin
1150 IF (g_print_debug) THEN
1151 fa_rx_util_pkg.debug('is_multi_format_report: ' || 'FA_RX_PUBLISH.populate_columns()+'||NEWLINE||
1152 'Getting columns for...'||NEWLINE||
1153 'Format # = '||to_char(s_current_format_idx)||NEWLINE||
1154 'Report ID = '||to_char(m_formats(s_current_format_idx).report_id)||NEWLINE||
1155 'Attribute Set = '||m_formats(s_current_format_idx).attribute_set);
1156 END IF;
1157
1158
1159 m_column_count := 0;
1160 open ccol;
1161 loop
1162 fetch ccol into rcol;
1163 exit when ccol%notfound;
1164
1165 if (rcol.display_length = 0) then
1166 fnd_message.set_name('OFA', 'FA_RX_LENGTH_IS_ZERO');
1167 fnd_message.set_token('COLUMN_NAME', rcol.column_name);
1168 fa_rx_util_pkg.log(fnd_message.get);
1169 GOTO end_loop;
1170 end if;
1171
1172 IF (rcol.display_length > 255) THEN
1173 fnd_message.set_name('OFA', 'FA_RX_LENGTH_RESET');
1174 fnd_message.set_token('COLUMN_NAME', rcol.column_name);
1175 fa_rx_util_pkg.Log(fnd_message.get);
1176 rcol.display_length := 255;
1177 END IF;
1178
1179 m_column_count := m_column_count + 1;
1180 m_columns(m_column_count).attribute_name := rcol.attribute_name;
1181 m_columns(m_column_count).column_name := rcol.column_name;
1182 m_columns(m_column_count).ordering := rcol.ordering;
1183 m_columns(m_column_count).display_length := rcol.display_length;
1184 m_columns(m_column_count).display_status := rcol.display_status;
1185 m_columns(m_column_count).display_format := rcol.display_format;
1186 m_columns(m_column_count).break := rcol.break;
1187 m_columns(m_column_count).currency_column := rcol.currency_column;
1188 m_columns(m_column_count).precision := rcol.precision;
1189 m_columns(m_column_count).minimum_accountable_unit := rcol.minimum_accountable_unit;
1190 m_columns(m_column_count).units := rcol.units;
1191 m_columns(m_column_count).format_mask := rcol.format_mask;
1192 m_columns(m_column_count).break_group_level := rcol.break_group_level;
1193
1194 IF m_report.output_format = 'CSV' THEN
1195 m_columns(m_column_count).currency_column := NULL;
1196 IF m_columns(m_column_count).display_format = 'NUMBER' THEN
1197 m_columns(m_column_count).format_mask := REPLACE(m_columns(m_column_count).format_mask, ',', NULL);
1198 ELSIF m_columns(m_column_count).display_format = 'DATE' THEN
1199 m_columns(m_column_count).format_mask := 'YYYY/MM/DD';
1200 END IF;
1201 END IF;
1202
1203 <<end_loop>>
1204 NULL;
1205 end loop;
1206
1207 IF (g_print_debug) THEN
1208 fa_rx_util_pkg.debug('is_multi_format_report: ' || 'Retrieved '||to_char(m_column_count)||' column(s)');
1209 END IF;
1210 IF (m_column_count = 0) THEN
1211 fnd_message.set_name('OFA', 'FA_RX_NO_DISPLAYED_COLUMNS');
1212 -- fa_rx_util_pkg.Log(fnd_message.get);
1213 app_exception.raise_exception;
1214 END IF;
1215
1216 --
1217 -- Get currency columns
1218 --
1219 m_displayed_column_count := m_column_count;
1220 l_displayed_columns := m_column_count;
1221 for idx in 1..l_displayed_columns loop
1222 if m_columns(idx).currency_column is not null then
1223 m_columns(idx).currency_column_id := find_column(m_columns(idx).currency_column);
1224 if m_columns(idx).currency_column_id is null then
1225 m_column_count := m_column_count + 1;
1226
1227 m_columns(m_column_count).attribute_name := null;
1228 m_columns(m_column_count).column_name := m_columns(idx).currency_column;
1229 m_columns(m_column_count).ordering := null;
1230 m_columns(m_column_count).display_length := 0;
1231 m_columns(m_column_count).display_status := 'NO';
1232 m_columns(m_column_count).display_format := 'VARCHAR2';
1233 m_columns(m_column_count).break := 'N';
1234 m_columns(m_column_count).currency_column := null;
1235 m_columns(m_column_count).precision := null;
1236 m_columns(m_column_count).minimum_accountable_unit := null;
1237 m_columns(m_column_count).units := null;
1238 m_columns(m_column_count).format_mask := null;
1239 m_columns(m_column_count).break_group_level := null;
1240
1241 m_columns(idx).currency_column_id := m_column_count;
1242 end if;
1243 end if;
1244 end loop;
1245
1246 IF (g_print_debug) THEN
1247 fa_rx_util_pkg.debug('is_multi_format_report: ' || 'Retrieved '||to_char(m_column_count)||' column(s) with currency columns');
1248 END IF;
1249
1250 --
1251 -- Reorder break level
1252 --
1253 l_current_break := 0;
1254 m_break_count := 0;
1255 l_last_break := -999999;
1256 for idx in 1..l_displayed_columns loop
1257 if l_last_break is null and
1258 m_columns(idx).break_group_level is null then
1259 l_last_break := null;
1260 elsif l_last_break is not null and
1261 m_columns(idx).break_group_level is null then
1262 l_current_break := l_current_break + 1;
1263 m_break_count := m_break_count + 1;
1264 l_last_break := null;
1265 elsif l_last_break <> m_columns(idx).break_group_level then
1266 l_current_break := l_current_break + 1;
1267 m_break_count := m_break_count + 1;
1268 l_last_break := m_columns(idx).break_group_level;
1269 end if;
1270
1271 m_columns(idx).break_group_level := l_current_break;
1272 end loop;
1273
1274 IF (g_print_debug) THEN
1275 fa_rx_util_pkg.debug('is_multi_format_report: ' || 'FA_RX_PUBLISH.populate_columns()-');
1276 END IF;
1277 end populate_columns;
1278
1279 ---------------------------------------------------------
1280 -- Procedure populate_summaries
1281 --
1282 -- Populates the m_summaries structure
1283 ---------------------------------------------------------
1284 procedure populate_summaries
1285 is
1286 -- cursor csum is
1287 -- select
1288 -- c.summary_prompt,
1289 -- c.reset_level,
1290 -- c.compute_level,
1291 -- c.print_level,
1292 -- r.function,
1293 -- r.column_name
1294 -- from
1295 -- fa_rx_summary_rules r,
1296 -- fa_rx_summary_columns c
1297 -- where
1298 -- r.report_id = m_formats(s_current_format_idx).report_id
1299 -- and r.attribute_set = m_formats(s_current_format_idx).attribute_set
1300 -- and r.summary_rule_id = c.summary_rule_id
1301 -- order by print_level, compute_level, reset_level;
1302 cursor csum is
1303 select
1304 summary_prompt,
1305 reset_level,
1306 compute_level,
1307 print_level,
1308 summary_function function,
1309 column_name
1310 from
1311 fa_rx_summary s
1312 where
1313 report_id = m_formats(s_current_format_idx).report_id
1314 and attribute_set = m_formats(s_current_format_idx).attribute_set
1315 and display_status = 'Y'
1316 and column_name in
1317 (select column_name from fa_rx_rep_columns c
1318 where c.report_id=s.report_id and
1319 c.attribute_set=s.attribute_set and
1320 display_status = 'YES')
1321 order by print_level, compute_level, reset_level;
1322 rsum csum%rowtype;
1323 begin
1324 IF (g_print_debug) THEN
1325 fa_rx_util_pkg.debug('is_multi_format_report: ' || 'FA_RX_PUBLISH.populate_summaries()+
1326 report_id = '||to_char(m_formats(s_current_format_idx).report_id)||'
1327 attribute_set = '||m_formats(s_current_format_idx).attribute_set);
1328 END IF;
1329
1330 m_summary_count := 0;
1331 open csum;
1332 loop
1333 fetch csum into rsum;
1334 exit when csum%notfound;
1335
1336 m_summary_count := m_summary_count + 1;
1337 m_summaries(m_summary_count).summary_prompt := rsum.summary_prompt;
1338 m_summaries(m_summary_count).reset_level := rsum.reset_level;
1339 m_summaries(m_summary_count).compute_level := rsum.compute_level;
1340 m_summaries(m_summary_count).print_level := rsum.print_level;
1341 m_summaries(m_summary_count).function := rsum.function;
1342 m_summaries(m_summary_count).column_name := rsum.column_name;
1343
1344 m_summaries(m_summary_count).source_column_id :=
1345 find_column(rsum.column_name);
1346
1347 --
1348 -- Temporary bugfix until form is fixed.
1349 -- This should be taken out when the form-side is fixed.
1350 -- Compute Level, for the time being, is always the
1351 -- same as the break group level of the source column
1352 --
1353 m_summaries(m_summary_count).compute_level :=
1354 m_columns(m_summaries(m_summary_count).source_column_id).break_group_level;
1355 end loop;
1356 close csum;
1357
1358 IF (g_print_debug) THEN
1359 fa_rx_util_pkg.debug('is_multi_format_report: ' || 'FA_RX_PUBLISH.populate_summaries()-');
1360 END IF;
1361 end populate_summaries;
1362
1363 ---------------------------------------------------------
1364 -- function get_select_list
1365 --
1366 -- Returns the select list required
1367 ---------------------------------------------------------
1368 function get_select_list return varchar2
1369 is
1370 l_select varchar2(10000);
1371 sep varchar2(10);
1372 begin
1373 l_select := 'SELECT ';
1374 sep := NEWLINE||' ';
1375
1376 for idx in 1..m_column_count loop
1377 if m_columns(idx).display_format = 'NUMBER' and
1378 m_columns(idx).units is not null then
1379 l_select := l_select || sep || m_columns(idx).column_name
1380 ||'/'||to_char(m_columns(idx).units);
1381 elsif m_columns(idx).display_format = 'DATE' and
1382 m_columns(idx).format_mask is not null then
1383 l_select := l_select || sep ||
1384 'to_char('||m_columns(idx).column_name||', '''||m_columns(idx).format_mask||''')';
1385 elsif m_columns(idx).display_format = 'DATE' then
1386 l_select := l_select || sep ||
1387 'fnd_date.date_to_displaydate('||m_columns(idx).column_name||')';
1388 ELSIF m_report.output_format = 'CSV' AND m_columns(idx).display_format = 'VARCHAR2' THEN
1389 l_select := l_select || sep || '''"''||' || m_columns(idx).column_name || '||''"''';
1390 else
1391 l_select := l_select || sep || m_columns(idx).column_name;
1392 end if;
1393 sep := NEWLINE||' ,';
1394 end loop;
1395
1396 return l_select;
1397 end get_select_list;
1398
1399 ---------------------------------------------------------
1400 -- function get_from_clause
1401 --
1402 -- Returns the from clause
1403 ---------------------------------------------------------
1404 function get_from_clause return varchar2
1405 is
1406 l_from varchar2(100);
1407 begin
1408 l_from := 'FROM '||m_formats(s_current_format_idx).interface_table;
1409
1410 return l_from;
1411 end get_from_clause;
1412
1413 ---------------------------------------------------------
1414 -- function get_where_clause
1415 --
1416 -- returns the where clause
1417 ---------------------------------------------------------
1418 function get_where_clause return varchar2
1419 is
1420 l_where varchar2(10000);
1421 sep varchar2(10);
1422 begin
1423 s_bind_idx := 0;
1424 m_bind_count := 0;
1425
1426 if m_formats(s_current_format_idx).request_id is null then
1427 sep := 'WHERE '||NEWLINE||' ';
1428 l_where := NULL;
1429 for idx in 1..m_argument_count loop
1430 IF (g_print_debug) THEN
1431 fa_rx_util_pkg.debug('is_multi_format_report: ' || 'idx='||To_char(idx)||', where_clause='||
1432 m_arguments(idx).where_clause||
1433 ', value='||m_arguments(idx).value);
1434 END IF;
1435 if m_arguments(idx).where_clause is not null
1436 and m_arguments(idx).value is not null then
1437
1438 m_bind_count := m_bind_count + 1;
1439 m_binds(m_bind_count) := m_arguments(idx).value;
1440
1441 if m_arguments(idx).datatype = 'VARCHAR2' then
1442 l_where := l_where||sep||
1443 m_arguments(idx).where_clause ||' :b'||To_char(m_bind_count);
1444 --''''||m_arguments(idx).value||'''';
1445 elsif m_arguments(idx).datatype = 'NUMBER' then
1446 l_where := l_where||sep||
1447 m_arguments(idx).where_clause ||' fnd_number.canonical_to_number(:b'||To_char(m_bind_count)||')';
1448 -- 'fnd_number.canonical_to_number('''||m_arguments(idx).value||''')';
1449 else
1450 l_where := l_where||sep||
1451 m_arguments(idx).where_clause ||' fnd_date.canonical_to_date(:b'||To_char(m_bind_count)||')';
1452 --'fnd_date.canonical_to_date('''||m_arguments(idx).value||''')';
1453 end if;
1454 sep := NEWLINE||'AND ';
1455
1456 END IF; /* where clause is not null and value is not null */
1457 end loop;
1458 else
1459 l_where := 'WHERE REQUEST_ID=fnd_number.canonical_to_number(:b1)'
1460 ||m_formats(s_current_format_idx).where_clause;
1461
1462 m_binds(1) := fnd_number.number_to_canonical(m_formats(s_current_format_idx).request_id);
1463 m_bind_count := 1;
1464 end if;
1465
1466 return l_where;
1467 end get_where_clause;
1468
1469 ---------------------------------------------------------
1470 -- function get_order_by_clause
1471 --
1472 -- returns the order by clause
1473 ---------------------------------------------------------
1474 function get_order_by_clause return varchar2
1475 is
1476 l_order_by varchar2(10000);
1477 sep varchar2(10);
1478 begin
1479 sep := 'ORDER BY ';
1480 for idx in 1..m_column_count loop
1481 if m_columns(idx).ordering is not null and
1482 m_columns(idx).ordering <> 'NONE' then
1483 l_order_by := l_order_by||sep||
1484 m_columns(idx).column_name;
1485 if m_columns(idx).ordering = 'DESCENDING' then
1486 l_order_by := l_order_by ||' DESC';
1487 end if;
1488
1489 sep := ','|| NEWLINE;
1490 end if;
1491 end loop;
1492
1493 return l_order_by;
1494 end get_order_by_clause;
1495
1496
1497 ---------------------------------------------------------
1498 ---------------------------------------------------------
1499 ---------------------------------------------------------
1500 ---------------------------------------------------------
1501 ---------------------------------------------------------
1502
1503
1504 ------------------------------------------------------------------
1505 -- Get_Report_Name
1506 --
1507 -- Given a report_id, this routine returns
1508 -- the application short name and concurrent program name of the
1509 -- associated concurrent program.
1510 -- If this is a Direct Select RX, it will return a NULL value
1511 -- for p_appname and p_concname
1512 ------------------------------------------------------------------
1513 procedure get_report_name(
1514 p_report_id in number,
1515 p_appname out nocopy varchar2,
1516 p_concname out nocopy varchar2)
1517 is
1518 begin
1519 IF (g_print_debug) THEN
1520 fa_rx_util_pkg.debug('is_multi_format_report: ' || 'FA_RX_PUBLISH.get_report_name('||to_char(p_report_id)||')+');
1521 END IF;
1522
1523 select a.application_short_name, c.concurrent_program_name
1524 into p_appname, p_concname
1525 from
1526 fa_rx_reports rx,
1527 fnd_concurrent_programs c,
1528 fnd_application a
1529 where
1530 rx.application_id = c.application_id and
1531 rx.concurrent_program_id = c.concurrent_program_id and
1532 rx.application_id = a.application_id and
1533 rx.report_id = p_report_id;
1534
1535 IF (g_print_debug) THEN
1536 fa_rx_util_pkg.debug('is_multi_format_report: ' || 'FA_RX_PUBLISH.get_report_name('||p_appname||','||p_concname||')-');
1537 END IF;
1538 exception
1539 when no_data_found then
1540 p_appname := null;
1541 p_concname := null;
1542 IF (g_print_debug) THEN
1543 fa_rx_util_pkg.debug('is_multi_format_report: ' || 'FA_RX_PUBLISH.get_report_name('||p_appname||','||p_concname||')-');
1544 END IF;
1545 end get_report_name;
1546
1547
1548 ------------------------------------------------------------------
1549 -- Init_Request
1550 --
1551 -- Initializes this package with the given request ID, report ID,
1552 -- attribute set and output format.
1553 -- Should not call this for Direct Select RX.
1554 -- Should only be called once per session.
1555 ------------------------------------------------------------------
1556 procedure init_request(p_request_id in number,
1557 p_report_id in number,
1558 p_attribute_set in varchar2,
1559 p_output_format in VARCHAR2,
1560 p_request_type IN VARCHAR2 DEFAULT 'PUBLISH'
1561 )
1562 is
1563 l_request_id NUMBER;
1564 begin
1565 IF (g_print_debug) THEN
1566 fa_rx_util_pkg.debug('is_multi_format_report: ' || 'FA_RX_PUBLISH.init_request()+'||NEWLINE||
1567 'P_Request_ID = '||to_char(p_request_id)||NEWLINE||
1568 'P_Report_ID = '||to_char(p_report_id)||NEWLINE||
1569 'P_Attribute_Set = '||p_attribute_set||NEWLINE||
1570 'P_Output_Format = '||p_output_format);
1571 END IF;
1572
1573 populate_report(p_request_id,
1574 p_report_id,
1575 p_attribute_set,
1576 p_output_format);
1577 populate_formats;
1578 populate_sob;
1579
1580 IF (g_print_debug) THEN
1581 fa_rx_util_pkg.debug('is_multi_format_report: ' || '** Populate parameters');
1582 END IF;
1583 IF Upper(p_request_type) = 'PUBLISH' THEN
1584 populate_parameters('PUBLISH', p_request_id);
1585 ELSE
1586 fnd_profile.get('CONC_REQUEST_ID', l_request_id);
1587 IF l_request_id IS NOT NULL THEN
1588 -- If request ID is null then we just won't print the parameters.
1589 -- Include this form debuggin purposes
1590 populate_parameters('SUBMIT', l_request_id);
1591 END IF;
1592 END IF;
1593
1594
1595 Validate_Report;
1596
1597 IF (g_print_debug) THEN
1598 fa_rx_util_pkg.debug('is_multi_format_report: ' || 'FA_RX_PUBLISH.init_request()-');
1599 END IF;
1600 end init_request;
1601
1602 ------------------------------------------------------------------
1603 -- Init_Report
1604 --
1605 -- Initializes this package with the given report ID, attribute
1606 -- set, output format, and concurrent program arguments.
1607 -- This routine should only be called for Direct Select RX.
1608 -- This routine should only be called once per session.
1609 ------------------------------------------------------------------
1610 procedure init_report(
1611 p_report_id in number,
1612 p_attribute_set in varchar2,
1613 p_output_format in varchar2,
1614 p_argument1 in varchar2 ,
1615 p_argument2 in varchar2 ,
1616 p_argument3 in varchar2 ,
1617 p_argument4 in varchar2 ,
1618 p_argument5 in varchar2 ,
1619 p_argument6 in varchar2 ,
1620 p_argument7 in varchar2 ,
1621 p_argument8 in varchar2 ,
1622 p_argument9 in varchar2 ,
1623 p_argument10 in varchar2 ,
1624 p_argument11 in varchar2 ,
1625 p_argument12 in varchar2 ,
1626 p_argument13 in varchar2 ,
1627 p_argument14 in varchar2 ,
1628 p_argument15 in varchar2 ,
1629 p_argument16 in varchar2 ,
1630 p_argument17 in varchar2 ,
1631 p_argument18 in varchar2 ,
1632 p_argument19 in varchar2 ,
1633 p_argument20 in varchar2 ,
1634 p_argument21 in varchar2 ,
1635 p_argument22 in varchar2 ,
1636 p_argument23 in varchar2 ,
1637 p_argument24 in varchar2 ,
1638 p_argument25 in varchar2 ,
1639 p_argument26 in varchar2 ,
1640 p_argument27 in varchar2 ,
1641 p_argument28 in varchar2 ,
1642 p_argument29 in varchar2 ,
1643 p_argument30 in varchar2 ,
1644 p_argument31 in varchar2 ,
1645 p_argument32 in varchar2 ,
1646 p_argument33 in varchar2 ,
1647 p_argument34 in varchar2 ,
1648 p_argument35 in varchar2 ,
1649 p_argument36 in varchar2 ,
1650 p_argument37 in varchar2 ,
1651 p_argument38 in varchar2 ,
1652 p_argument39 in varchar2 ,
1653 p_argument40 in varchar2 ,
1654 p_argument41 in varchar2 ,
1655 p_argument42 in varchar2 ,
1656 p_argument43 in varchar2 ,
1657 p_argument44 in varchar2 ,
1658 p_argument45 in varchar2 ,
1659 p_argument46 in varchar2 ,
1660 p_argument47 in varchar2 ,
1661 p_argument48 in varchar2 ,
1662 p_argument49 in varchar2 ,
1663 p_argument50 in varchar2 ,
1664 p_argument51 in varchar2 ,
1665 p_argument52 in varchar2 ,
1666 p_argument53 in varchar2 ,
1667 p_argument54 in varchar2 ,
1668 p_argument55 in varchar2 ,
1669 p_argument56 in varchar2 ,
1670 p_argument57 in varchar2 ,
1671 p_argument58 in varchar2 ,
1672 p_argument59 in varchar2 ,
1673 p_argument60 in varchar2 ,
1674 p_argument61 in varchar2 ,
1675 p_argument62 in varchar2 ,
1676 p_argument63 in varchar2 ,
1677 p_argument64 in varchar2 ,
1678 p_argument65 in varchar2 ,
1679 p_argument66 in varchar2 ,
1680 p_argument67 in varchar2 ,
1681 p_argument68 in varchar2 ,
1682 p_argument69 in varchar2 ,
1683 p_argument70 in varchar2 ,
1684 p_argument71 in varchar2 ,
1685 p_argument72 in varchar2 ,
1686 p_argument73 in varchar2 ,
1687 p_argument74 in varchar2 ,
1688 p_argument75 in varchar2 ,
1689 p_argument76 in varchar2 ,
1690 p_argument77 in varchar2 ,
1691 p_argument78 in varchar2 ,
1692 p_argument79 in varchar2 ,
1693 p_argument80 in varchar2 ,
1694 p_argument81 in varchar2 ,
1695 p_argument82 in varchar2 ,
1696 p_argument83 in varchar2 ,
1697 p_argument84 in varchar2 ,
1698 p_argument85 in varchar2 ,
1699 p_argument86 in varchar2 ,
1700 p_argument87 in varchar2 ,
1701 p_argument88 in varchar2 ,
1702 p_argument89 in varchar2 ,
1703 p_argument90 in varchar2 ,
1704 p_argument91 in varchar2 ,
1705 p_argument92 in varchar2 ,
1706 p_argument93 in varchar2 ,
1707 p_argument94 in varchar2 ,
1708 p_argument95 in varchar2 ,
1709 p_argument96 in varchar2 ,
1710 p_argument97 in varchar2 ,
1711 p_argument98 in varchar2 ,
1712 p_argument99 in varchar2 ,
1713 p_argument100 in varchar2 )
1714 is
1715 t_conc_request_id varchar2(20);
1716 conc_request_id number;
1717 begin
1718 IF (g_print_debug) THEN
1719 fa_rx_util_pkg.debug('is_multi_format_report: ' || 'Init_report()+');
1720 END IF;
1721 populate_report(
1722 null, -- Request ID
1723 p_report_id,
1724 p_attribute_set,
1725 p_output_format);
1726
1727 fnd_profile.get('CONC_REQUEST_ID', t_conc_request_id);
1728 IF (g_print_debug) THEN
1729 fa_rx_util_pkg.debug('is_multi_format_report: ' || 'Concurrent request_id = '||t_conc_request_id);
1730 END IF;
1731
1732 if t_conc_request_id is null then
1733 m_report.conc_appname := NULL;
1734 m_report.concurrent_program_name := null; -- Debugging.
1735 else
1736 conc_request_id := to_number(t_conc_request_id);
1737 select
1738 p.concurrent_program_name,
1739 a.application_short_name
1740 INTO
1741 m_report.concurrent_program_name,
1742 m_report.conc_appname
1743 from
1744 fnd_concurrent_requests r,
1745 fnd_concurrent_programs p,
1746 fnd_application a
1747 where
1748 r.request_id = conc_request_id and
1749 r.program_application_id = p.application_id and
1750 r.concurrent_program_id = p.concurrent_program_id and
1751 p.application_id = a.application_id;
1752 end if;
1753
1754 populate_arguments(
1755 p_argument1,
1756 p_argument2,
1757 p_argument3,
1758 p_argument4,
1759 p_argument5,
1760 p_argument6,
1761 p_argument7,
1762 p_argument8,
1763 p_argument9,
1764 p_argument10,
1765 p_argument11,
1766 p_argument12,
1767 p_argument13,
1768 p_argument14,
1769 p_argument15,
1770 p_argument16,
1771 p_argument17,
1772 p_argument18,
1773 p_argument19,
1774 p_argument20,
1775 p_argument21,
1776 p_argument22,
1777 p_argument23,
1778 p_argument24,
1779 p_argument25,
1780 p_argument26,
1781 p_argument27,
1782 p_argument28,
1783 p_argument29,
1784 p_argument30,
1785 p_argument31,
1786 p_argument32,
1787 p_argument33,
1788 p_argument34,
1789 p_argument35,
1790 p_argument36,
1791 p_argument37,
1792 p_argument38,
1793 p_argument39,
1794 p_argument40,
1795 p_argument41,
1796 p_argument42,
1797 p_argument43,
1798 p_argument44,
1799 p_argument45,
1800 p_argument46,
1801 p_argument47,
1802 p_argument48,
1803 p_argument49,
1804 p_argument50,
1805 p_argument51,
1806 p_argument52,
1807 p_argument53,
1808 p_argument54,
1809 p_argument55,
1810 p_argument56,
1811 p_argument57,
1812 p_argument58,
1813 p_argument59,
1814 p_argument60,
1815 p_argument61,
1816 p_argument62,
1817 p_argument63,
1818 p_argument64,
1819 p_argument65,
1820 p_argument66,
1821 p_argument67,
1822 p_argument68,
1823 p_argument69,
1824 p_argument70,
1825 p_argument71,
1826 p_argument72,
1827 p_argument73,
1828 p_argument74,
1829 p_argument75,
1830 p_argument76,
1831 p_argument77,
1832 p_argument78,
1833 p_argument79,
1834 p_argument80,
1835 p_argument81,
1836 p_argument82,
1837 p_argument83,
1838 p_argument84,
1839 p_argument85,
1840 p_argument86,
1841 p_argument87,
1842 p_argument88,
1843 p_argument89,
1844 p_argument90,
1845 p_argument91,
1846 p_argument92,
1847 p_argument93,
1848 p_argument94,
1849 p_argument95,
1850 p_argument96,
1851 p_argument97,
1852 p_argument98,
1853 p_argument99,
1854 p_argument100);
1855 populate_formats;
1856 populate_sob;
1857
1858 IF (g_print_debug) THEN
1859 fa_rx_util_pkg.debug('is_multi_format_report: ' || '** Populate parameters');
1860 END IF;
1861 if t_conc_request_id is NOT null then
1862 -- If request ID is null then we just won't print the parameters.
1863 -- Include this form debuggin purposes
1864 populate_parameters('DIRECT', t_conc_request_id);
1865 END IF;
1866
1867 Validate_Report;
1868 IF (g_print_debug) THEN
1869 fa_rx_util_pkg.debug('is_multi_format_report: ' || 'Init_report()-');
1870 END IF;
1871 end init_report;
1872
1873 ------------------------------------------------------------------
1874 -- Get_Report_Info
1875 --
1876 -- This routine returns report level information.
1877 -- These are information that should not change between formats.
1878 ------------------------------------------------------------------
1879 procedure get_report_info(
1880 p_display_report_title out nocopy varchar2,
1881 p_display_set_of_books out nocopy varchar2,
1882 p_display_functional_currency out nocopy varchar2,
1883 p_display_submission_date out nocopy varchar2,
1884 p_display_current_page out nocopy varchar2,
1885 p_display_total_page out nocopy varchar2,
1886 p_report_title out nocopy varchar2,
1887 p_set_of_books_name out nocopy varchar2,
1888 p_function_currency_prompt out nocopy varchar2,
1889 p_function_currency out nocopy varchar2,
1890 p_submission_date out nocopy varchar2,
1891 p_current_page_prompt out nocopy varchar2,
1892 p_total_page_prompt out nocopy varchar2,
1893 p_page_width out nocopy number,
1894 p_page_height out nocopy number,
1895 p_output_format out nocopy varchar2,
1896 p_nls_end_of_report out nocopy varchar2,
1897 p_nls_no_data_found out nocopy varchar2)
1898 is
1899 begin
1900 p_display_report_title := m_report.display_report_title;
1901 p_display_set_of_books := m_report.display_set_of_books;
1902 p_display_functional_currency := m_report.display_functional_currency;
1903 p_display_submission_date := m_report.display_submission_date;
1904 p_display_current_page := m_report.display_current_page;
1905 p_display_total_page := m_report.display_total_page;
1906 p_report_title := m_report.report_title;
1907 p_set_of_books_name := m_report.set_of_books_name;
1908 p_function_currency_prompt := m_report.functional_currency_prompt;
1909 p_function_currency := m_report.functional_currency;
1910 --* 1401113, rravunny
1911 --* p_submission_date is not a date type it is varchar2
1912 --*
1913 p_submission_date := m_report.date_prompt||' '||m_report.submission_date;
1914 --* end of fix
1915 p_current_page_prompt := m_report.current_page_prompt;
1916 p_total_page_prompt := m_report.total_page_prompt;
1917 p_page_width := m_report.page_width;
1918 p_page_height := m_report.page_height;
1919 p_output_format := m_report.output_format;
1920 p_nls_end_of_report := m_report.nls_end_of_report;
1921 p_nls_no_data_found := m_report.nls_no_data_found;
1922 end get_report_info;
1923
1924 ------------------------------------------------------------------
1925 -- Format Available
1926 --
1927 -- This routine should be called after a format ends to determine
1928 -- if there are any more formats.
1929 ------------------------------------------------------------------
1930 function format_available return varchar2
1931 is
1932 begin
1933 s_current_format_idx := s_current_format_idx + 1;
1934 if s_current_format_idx > m_format_count then
1935 s_current_format_idx := null;
1936 return 'N';
1937 end if;
1938
1939 return 'Y';
1940 end format_available;
1941
1942 ------------------------------------------------------------------
1943 -- Get_Format_Info
1944 --
1945 -- This routine returns format level information.
1946 -- These are information that may change from format to format.
1947 ------------------------------------------------------------------
1948 procedure get_format_info(
1949 p_display_parameters out nocopy varchar2,
1950 p_display_page_break out nocopy varchar2,
1951 p_group_display_type out nocopy varchar2)
1952 is
1953 begin
1954 p_display_parameters := m_formats(s_current_format_idx).display_parameters;
1955 p_display_page_break := m_formats(s_current_format_idx).display_page_break;
1956 p_group_display_type := m_formats(s_current_format_idx).group_display_type;
1957 end get_format_info;
1958
1959 ------------------------------------------------------------------
1960 -- Get_Param_Count
1961 --
1962 -- Returns the number of parameters for the current format.
1963 ------------------------------------------------------------------
1964 function get_param_count return number
1965 is
1966 begin
1967 s_current_param_idx := 1;
1968 return m_param_display_count;
1969 end get_param_count;
1970
1971 ------------------------------------------------------------------
1972 -- Get_Parameter
1973 --
1974 -- Returns parameter information.
1975 -- Must be called exact number of times as is returned by
1976 -- Get_Param_Count.
1977 ------------------------------------------------------------------
1978 procedure get_parameter(
1979 p_param_id in number,
1980 p_param_name out nocopy varchar2,
1981 p_param_value out nocopy varchar2)
1982 is
1983 begin
1984 WHILE (m_params(s_current_param_idx).display_flag <> 'Y') LOOP
1985 s_current_param_idx := s_current_param_idx + 1;
1986 END LOOP;
1987 p_param_name := m_params(s_current_param_idx).name;
1988 p_param_value := m_params(s_current_param_idx).value;
1989 s_current_param_idx := s_current_param_idx + 1;
1990 end get_parameter;
1991
1992 ------------------------------------------------------------------
1993 -- Get_Break_Level
1994 --
1995 -- Returns the number of break levels in the current format.
1996 ------------------------------------------------------------------
1997 function get_break_level_count return number
1998 is
1999 begin
2000 s_current_column_idx := null;
2001 s_current_summary_idx := null;
2002
2003 return m_break_count;
2004 end get_break_level_count;
2005
2006 ------------------------------------------------------------------
2007 -- Get_Column_Count
2008 --
2009 -- Returns the number of columns in the given break level.
2010 -- Break level should be between 1 and the returned value of
2011 -- Get_Break_Level.
2012 ------------------------------------------------------------------
2013 function get_column_count(
2014 p_break_level in number) return number
2015 is
2016 cnt number;
2017 begin
2018 if s_current_column_idx is null then
2019 s_current_column_idx := 1;
2020 end if;
2021
2022 cnt := 0;
2023 for idx in s_current_column_idx..m_column_count loop
2024 if m_columns(idx).break_group_level <> p_break_level
2025 or m_columns(idx).display_status = 'NO' then
2026 return cnt;
2027 end if;
2028
2029 cnt := cnt + 1;
2030 end loop;
2031
2032 return cnt;
2033 end get_column_count;
2034
2035
2036 ------------------------------------------------------------------
2037 -- Get_Column_Info
2038 --
2039 -- Returns the column information for the given break level.
2040 -- This routine must be called as many times as is returned
2041 -- by Get_Column_Count.
2042 ------------------------------------------------------------------
2043 procedure get_column_info(
2044 p_break_level in number,
2045 p_column_id out nocopy number,
2046 p_column_name out nocopy varchar2,
2047 p_column_type out nocopy varchar2,
2048 p_attribute_name out nocopy varchar2,
2049 p_length out nocopy number,
2050 p_currency_column_id out nocopy number,
2051 p_precision out nocopy number,
2052 p_minimum_accountable_unit out nocopy number,
2053 p_break out nocopy varchar2)
2054 is
2055 begin
2056 p_column_id := s_current_column_idx;
2057 p_column_name := m_columns(s_current_column_idx).column_name;
2058 p_column_type := m_columns(s_current_column_idx).display_format;
2059 p_attribute_name := m_columns(s_current_column_idx).attribute_name;
2060 p_length := m_columns(s_current_column_idx).display_length;
2061 p_currency_column_id := m_columns(s_current_column_idx).currency_column_id;
2062 p_precision := m_columns(s_current_column_idx).precision;
2063 p_minimum_accountable_unit := m_columns(s_current_column_idx).minimum_accountable_unit;
2064 p_break := m_columns(s_current_column_idx).break;
2065
2066 s_current_column_idx := s_current_column_idx + 1;
2067 end get_column_info;
2068
2069
2070 ------------------------------------------------------------------
2071 -- Get_Summary_Column_Count
2072 --
2073 -- Returns the number of summary columns in the given break level.
2074 -- Break level should be between 1 and the returned value of
2075 -- Get_Break_Level.
2076 ------------------------------------------------------------------
2077 function get_summary_column_count(
2078 p_break_level in number) return number
2079 is
2080 cnt number;
2081 idx number;
2082 begin
2083 IF (g_print_debug) THEN
2084 fa_rx_util_pkg.debug('is_multi_format_report: ' || 'FA_RX_PUBLISH.get_summary_column_count('||to_char(p_break_level)||')+');
2085 END IF;
2086
2087 idx := 1;
2088 loop
2089 exit when idx > m_summary_count;
2090 exit when m_summaries(idx).print_level = p_break_level;
2091 idx := idx + 1;
2092 end loop;
2093
2094 cnt := 0;
2095 s_current_summary_idx := idx;
2096 loop
2097 if idx > m_summary_count then
2098 IF (g_print_debug) THEN
2099 fa_rx_util_pkg.debug('is_multi_format_report: ' || 'idx = '||to_char(idx)||', summary_count='||to_char(m_summary_count));
2100 END IF;
2101 exit;
2102 end if;
2103 IF (g_print_debug) THEN
2104 fa_rx_util_pkg.debug('is_multi_format_report: ' || 'Checking '||to_char(idx)||' with break level '||to_char(m_summaries(idx).print_level));
2105 END IF;
2106 exit when m_summaries(idx).print_level <> p_break_level;
2107
2108 idx := idx + 1;
2109 cnt := cnt + 1;
2110 end loop;
2111
2112 IF (g_print_debug) THEN
2113 fa_rx_util_pkg.debug('is_multi_format_report: ' || 'FA_RX_PUBLISH.get_summary_column_count('||to_char(cnt)||')-');
2114 END IF;
2115 return cnt;
2116 end get_summary_column_count;
2117
2118 ------------------------------------------------------------------
2119 -- Get_Summary_Column_Info
2120 --
2121 -- Returns the summary column information for the given break level.
2122 -- This routine must be called as many times as is returned
2123 -- by Get_Summary_Column_Count.
2124 ------------------------------------------------------------------
2125 procedure get_summary_column_info(
2126 p_break_level in number,
2127 p_summary_column_id out nocopy number,
2128 p_prompt out nocopy varchar2,
2129 p_source_column_id out nocopy number)
2130 is
2131 begin
2132 p_summary_column_id := s_current_summary_idx;
2133 p_prompt := m_summaries(s_current_summary_idx).summary_prompt;
2134 p_source_column_id := m_summaries(s_current_summary_idx).source_column_id;
2135
2136 s_current_summary_idx := s_current_summary_idx + 1;
2137 end get_summary_column_info;
2138
2139
2140 ------------------------------------------------------------------
2141 -- Start_Format
2142 --
2143 -- This routine should be called at the beginning of every format.
2144 ------------------------------------------------------------------
2145 procedure start_format
2146 is
2147 rows number;
2148 dummy varchar2(240);
2149 begin
2150 IF (g_print_debug) THEN
2151 fa_rx_util_pkg.debug('is_multi_format_report: ' || 'FA_RX_PUBLISH.START_FORMAT()+');
2152 END IF;
2153
2154 --
2155 -- Initialize the date/time formats
2156 -- NOTE: The very first one was also initialized during populate_formats
2157 if (m_formats(s_current_format_idx).default_date_format is not null) then
2158 fnd_date.initialize(m_formats(s_current_format_idx).default_date_format,
2159 m_formats(s_current_format_idx).default_date_time_format);
2160 end if;
2161 m_report.submission_date := fnd_date.date_to_displaydt(m_report.real_submission_date);
2162
2163 IF (g_print_debug) THEN
2164 fa_rx_util_pkg.debug('is_multi_format_report: ' || '-- Populate columns');
2165 END IF;
2166 populate_columns;
2167 IF (g_print_debug) THEN
2168 fa_rx_util_pkg.debug('is_multi_format_report: ' || '-- Populate summaries');
2169 END IF;
2170 populate_summaries;
2171
2172 IF (g_print_debug) THEN
2173 fa_rx_util_pkg.debug('is_multi_format_report: ' || 'FA_RX_PUBLISH.start_format()-');
2174 END IF;
2175 end start_format;
2176
2177 ------------------------------------------------------------------
2178 -- End_Format
2179 --
2180 -- This routine should be called at the end of every format.
2181 ------------------------------------------------------------------
2182 procedure end_format
2183 is
2184 begin
2185 null;
2186 end end_format;
2187
2188 ------------------------------------------------------------------
2189 -- Get_All_Column_Count
2190 --
2191 -- This routine returns the number of columns in the select
2192 -- statement returned by Get_Select_Stmt
2193 -- May return more than get_break_level_count * get_column_count
2194 -- since the select statement may include currency columns
2195 -- as well.
2196 ------------------------------------------------------------------
2197 function get_all_column_count return number
2198 is
2199 begin
2200 s_current_column_idx := 1;
2201 return m_column_count;
2202 end get_all_column_count;
2203
2204 function get_disp_column_count return number
2205 is
2206 begin
2207 s_current_column_idx := 1;
2208 return m_displayed_column_count;
2209 end get_disp_column_count;
2210
2211 ------------------------------------------------------------------
2212 -- Get_Format_Col_Info
2213 --
2214 -- This routine returns information required for formatting.
2215 -- It will return the columns in order of the column_id returned
2216 -- by Get_Column_Info. The p_currency_column_id returned here
2217 -- will also point to a valid column_id returned by this routine.
2218 ------------------------------------------------------------------
2219 procedure get_format_col_info(
2220 p_column_name out nocopy varchar2,
2221 p_display_format out nocopy varchar2,
2222 p_display_length out nocopy number,
2223 p_break_group_level out nocopy number,
2224 p_format_mask out nocopy varchar2,
2225 p_currency_column_id out nocopy number,
2226 p_precision out nocopy number,
2227 p_minunit out nocopy number)
2228 is
2229 begin
2230 if s_current_column_idx > m_column_count then
2231 raise no_data_found;
2232 end if;
2233
2234 p_column_name := m_columns(s_current_column_idx).column_name;
2235 p_display_format := m_columns(s_current_column_idx).display_format;
2236 p_display_length := m_columns(s_current_column_idx).display_length;
2237 p_break_group_level := m_columns(s_current_column_idx).break_group_level;
2238 p_format_mask := m_columns(s_current_column_idx).format_mask;
2239 p_currency_column_id := m_columns(s_current_column_idx).currency_column_id;
2240 p_precision := m_columns(s_current_column_idx).precision;
2241 p_minunit := m_columns(s_current_column_idx).minimum_accountable_unit;
2242
2243 s_current_column_idx := s_current_column_idx + 1;
2244 end get_format_col_info;
2245
2246 ------------------------------------------------------------------
2247 -- Get_All_Summary_Count
2248 --
2249 -- This routine returns the number of summary columns
2250 ------------------------------------------------------------------
2251 function get_all_summary_count return number
2252 is
2253 begin
2254 s_current_summary_idx := 1;
2255 return m_summary_count;
2256 end get_all_summary_count;
2257
2258 ------------------------------------------------------------------
2259 -- Get_Format_Sum_Info
2260 --
2261 -- This routine returns information about summary columns as required
2262 -- to format.
2263 -- p_source_column_id will point to a valid column_id returned
2264 -- by Get_Format_Column_Info.
2265 -- This routine will return the columns in order of p_summary_column_id
2266 -- returned by Get_Summary_Column_Info
2267 ------------------------------------------------------------------
2268 procedure get_format_sum_info(
2269 p_source_column_id out nocopy number,
2270 p_reset_level out nocopy number,
2271 p_compute_level out nocopy number,
2272 p_summary_function out nocopy varchar2)
2273 is
2274 begin
2275 if s_current_summary_idx > m_summary_count then
2276 p_source_column_id := 0;
2277 p_reset_level := -99;
2278 p_compute_level := -99;
2279 p_summary_function := -99;
2280 else
2281 p_source_column_id := m_summaries(s_current_summary_idx).source_column_id;
2282 p_reset_level := m_summaries(s_current_summary_idx).reset_level;
2283 p_compute_level := m_summaries(s_current_summary_idx).compute_level;
2284 p_summary_function := m_summaries(s_current_summary_idx).function;
2285 end if;
2286
2287 s_current_summary_idx := s_current_summary_idx + 1;
2288 end get_format_sum_info;
2289
2290
2291 ------------------------------------------------------------------
2292 -- Get_Select_Stmt
2293 --
2294 -- This routine returns the main select statment.
2295 -- There will be no bind variables here.
2296 ------------------------------------------------------------------
2297 function get_select_stmt return varchar2
2298 is
2299 sqlstmt varchar2(10000);
2300 add_where_clause varchar2(10000) := '';
2301 tmp_where_clause varchar2(10000);
2302 plsql_block varchar2(150);
2303 X_where_clause_api varchar2(80);
2304 l_cursor integer;
2305
2306 begin
2307
2308 tmp_where_clause := get_where_clause;
2309 X_where_clause_api := m_formats(s_current_format_idx).where_clause_api;
2310 IF X_where_clause_api IS NOT NULL THEN
2311 plsql_block := 'BEGIN :where_clause := ' || X_where_clause_api ||'(:id); end;';
2312 execute immediate plsql_block using out add_where_clause, in m_formats(s_current_format_idx).request_id;
2313 END IF;
2314
2315 IF add_where_clause IS NOT NULL THEN
2316 --
2317 IF tmp_where_clause IS NULL THEN
2318 tmp_where_clause := ' where '||add_where_clause;
2319 ELSE
2320 tmp_where_clause := tmp_where_clause||' AND '||add_where_clause;
2321 END IF;
2322 --
2323 END IF;
2324 sqlstmt := get_select_list||NEWLINE||
2325 get_from_clause||NEWLINE||
2326 tmp_where_clause||NEWLINE||
2327 get_order_by_clause;
2328 BEGIN
2329 l_cursor := dbms_sql.open_cursor;
2330
2331 IF (g_print_debug) THEN
2332 arp_util_tax.debug('BEGIN '||rtrim(ltrim(sqlstmt))||'; END;');
2333 END IF;
2334
2335 dbms_sql.parse(l_cursor,
2336 'BEGIN '||rtrim(ltrim(sqlstmt))||'; END;', dbms_sql.native);
2337 dbms_sql.close_cursor(l_cursor);
2338
2339 EXCEPTION
2340 WHEN OTHERS THEN
2341 IF (g_print_debug) THEN
2342 arp_util_tax.debug('get_select_stmt(-) wrong PL_SQL statement');
2343 END IF;
2344 END;
2345
2346 return sqlstmt;
2347 end get_select_stmt;
2348
2349 ------------------------------------------------------------------
2350 -- Is_Multi_Format_Report
2351 -- Returns true if the request is a multiple format report.
2352 ------------------------------------------------------------------
2353 function Is_Multi_Format_Report(p_request_id in number)
2354 return boolean
2355 is
2356 cnt number;
2357 begin
2358 select count(*) into cnt
2359 from fa_rx_multiformat_reps
2360 where request_id = p_request_id;
2361
2362 return (cnt > 0);
2363 end is_multi_format_report;
2364
2365 procedure Expand_Complex_Multiformat(p_formats in formattab, p_count in number)
2366 is
2367 cursor cols(p_report_id in number, p_attribute_set in varchar2) is select
2368 column_name, display_format
2369 from fa_rx_rep_columns
2370 where report_id = p_report_id
2371 and attribute_set = p_attribute_set
2372 and break = 'Y'
2373 and break_group_level = 1
2374 order by attribute_counter;
2375
2376 sqlstmt varchar2(2000);
2377 sep varchar2(30);
2378 numcols number;
2379 where_clause varchar2(1000);
2380 buf varchar2(400);
2381 colidx number;
2382
2383 c integer;
2384 rows integer;
2385
2386 invalid_result_columns exception;
2387 pragma exception_init(invalid_result_columns, -1789);
2388 begin
2389 IF (g_print_debug) THEN
2390 fa_rx_util_pkg.debug('Expand_Complex_Multiformat()+');
2391 END IF;
2392
2393 sqlstmt := null;
2394 for idx in 1..p_count loop
2395 if sqlstmt is null then
2396 sqlstmt := 'SELECT DISTINCT ';
2397 else
2398 sqlstmt := sqlstmt ||' UNION SELECT DISTINCT ';
2399 end if;
2400
2401 sep := null;
2402 numcols := 0;
2403 for crow in cols(p_formats(idx).report_id, p_formats(idx).attribute_set) loop
2404 if crow.display_format = 'VARCHAR2' then
2405 sqlstmt := sqlstmt || sep || crow.column_name;
2406 elsif crow.display_format = 'NUMBER' then
2407 sqlstmt := sqlstmt || sep || 'fnd_number.number_to_canonical('||crow.column_name||')';
2408 elsif crow.display_format = 'DATE' then
2409 sqlstmt := sqlstmt || sep || 'fnd_date.date_to_canonical('||crow.column_name||')';
2410 end if;
2411
2412 sep := ', ';
2413 numcols := numcols + 1;
2414 end loop;
2415
2416 if numcols = 0 then
2417 -- There were no break columns!
2418 app_exception.raise_exception;
2419 end if;
2420
2421 sqlstmt := sqlstmt || ' FROM '||p_formats(idx).interface_table
2422 || ' WHERE REQUEST_ID = '||to_char(p_formats(idx).request_id);
2423 end loop;
2424
2425
2426 IF (g_print_debug) THEN
2427 fa_rx_util_pkg.debug('Expand_Complex_Multiformat: ' || '
2428 '||sqlstmt||'
2429 ');
2430 END IF;
2431
2432 c := dbms_sql.open_cursor;
2433 begin
2434 dbms_sql.parse(c, sqlstmt, dbms_sql.native);
2435 exception
2436 when invalid_result_columns then
2437 IF (g_print_debug) THEN
2438 fa_rx_util_pkg.debug('Expand_Complex_Multiformat: ' || 'Cannot use complex mode for these sets of reports');
2439 END IF;
2440 fnd_message.set_name('OFA', 'FA_RX_INVALID_USE_OF_COMPLEX');
2441 app_exception.raise_exception;
2442 end;
2443 for idx in 1..numcols loop
2444 dbms_sql.define_column(c, idx, buf, 400);
2445 end loop;
2446 rows := dbms_sql.execute(c);
2447 loop
2448 rows := dbms_sql.fetch_rows(c);
2449 exit when rows = 0 ;
2450
2451 for idx in 1..p_count loop
2452 m_format_count := m_format_count + 1;
2453 where_clause := null;
2454 sep := ' AND ';
2455
2456 colidx := 0;
2457 for crow in cols(p_formats(idx).report_id, p_formats(idx).attribute_set) loop
2458 colidx := colidx + 1;
2459 dbms_sql.column_value(c, colidx, buf);
2460
2461 if crow.display_format = 'VARCHAR2' then
2462 where_clause := where_clause || sep ||
2463 crow.column_name ||' = '''||buf||'''';
2464 elsif crow.display_format = 'NUMBER' then
2465 where_clause := where_clause || sep ||
2466 'FND_NUMBER.NUMBER_TO_CANONICAL('||crow.column_name ||') = '||buf;
2467 elsif crow.display_format = 'DATE' then
2468 where_clause := where_clause || sep ||
2469 'FND_DATE.DATE_TO_CANONICAL('||crow.column_name ||') = '||buf;
2470 end if;
2471
2472 sep := ' AND ';
2473 end loop;
2474
2475 m_formats(m_format_count) := p_formats(idx);
2476 m_formats(m_format_count).where_clause := where_clause;
2477
2478 IF (g_print_debug) THEN
2479 fa_rx_util_pkg.debug('Expand_Complex_Multiformat: ' || where_clause);
2480 END IF;
2481 end loop;
2482 end loop;
2483
2484 dbms_sql.close_cursor(c);
2485 end expand_complex_multiformat;
2486
2487
2488
2489 -----------------------------------------------------------------
2490 -- Validate_Report
2491 -- Validate different portions of the report
2492 -----------------------------------------------------------------
2493 procedure Validate_Report
2494 is
2495 max_lines number;
2496 pos number;
2497 line number;
2498 last_pos number;
2499 last_line number;
2500
2501
2502 function calculate_pos(format_idx in number, break_flag in varchar2, max_width in number,
2503 last_pos out nocopy number, last_line out number) return boolean
2504 is
2505 min_level number;
2506 current_pos number;
2507 current_line number;
2508
2509 cursor col(min_level in NUMBER, break_flag IN varchar2) is select
2510 attribute_name,
2511 Least(display_length, 255) display_length
2512 from fa_rx_rep_columns
2513 where report_id = m_formats(format_idx).report_id and attribute_set = m_formats(format_idx).attribute_set
2514 and display_status = 'YES'
2515 and decode(break, 'Y', break_group_level, -999) >= min_level
2516 AND Nvl(break, 'N') = break_flag
2517 ORDER BY Nvl(break,'N') DESC, break_group_level, attribute_counter;
2518 begin
2519 if break_flag = 'Y' then
2520 select min(break_group_level) into min_level from fa_rx_rep_columns
2521 where report_id = m_formats(format_idx).report_id and attribute_set = m_formats(format_idx).attribute_set
2522 and display_status = 'YES';
2523
2524 if m_formats(format_idx).display_page_break = 'Y' then
2525 min_level := min_level + 1;
2526 end if;
2527 else
2528 min_level := -999;
2529 end if;
2530
2531 current_pos := -2;
2532 current_line := 1;
2533 for colrow in col(min_level, break_flag) loop
2534 if colrow.display_length > max_width then
2535 fnd_message.set_name('OFA', 'FA_RX_COLUMN_TOO_WIDE');
2536 fnd_message.set_token('COLUMN_NAME', colrow.attribute_name);
2537 return FALSE;
2538 end if;
2539
2540 if current_pos + 2 + colrow.display_length > max_width then
2541 current_pos := -2;
2542 current_line := current_line + 1;
2543 end if;
2544
2545 current_pos := current_pos + 2 + colrow.display_length;
2546 end loop;
2547
2548 last_pos := current_pos;
2549 last_line := current_line;
2550
2551 return TRUE;
2552 end calculate_pos;
2553
2554 begin
2555 max_lines := 0;
2556
2557 if m_report.page_width <> 0 then
2558 -- Check page widths
2559 for i in 1..m_format_count loop
2560 pos := 0;
2561 line := 0;
2562 if not calculate_pos(i, 'Y', m_report.page_width, last_pos, last_line) then
2563 -- fa_rx_util_pkg.Log(fnd_message.get);
2564 app_exception.raise_exception;
2565 end if;
2566 pos := last_pos;
2567 line := last_line;
2568
2569 if m_formats(i).group_display_type = 'GROUP LEFT' then
2570 if not calculate_pos(i, 'N', m_report.page_width - 2 - pos, last_pos, last_line) then
2571 fa_rx_util_pkg.debug(fnd_message.get);
2572 fnd_message.set_name('OFA', 'FA_RX_GROUP_ABOVE_USED');
2573 fa_rx_util_pkg.log(fnd_message.get);
2574
2575 m_formats(i).group_display_type := 'GROUP ABOVE';
2576 else
2577 line := line + last_line - 1;
2578 end if;
2579 end if;
2580
2581 if m_formats(i).group_display_type = 'GROUP ABOVE' then
2582 if not calculate_pos(i, 'N', m_report.page_width, last_pos, last_line) then
2583 -- fa_rx_util_pkg.Log(fnd_message.get);
2584 app_exception.raise_exception;
2585 else
2586 line := line + last_line;
2587 end if;
2588 end if;
2589
2590 if max_lines < line then max_lines := line; end if;
2591 end loop;
2592 else
2593 max_lines := 1;
2594 end if;
2595
2596 if m_report.page_height <> 0 then
2597 -- Check page height
2598 if max_lines*2 + 20 > m_report.page_height then
2599 fnd_message.set_name('OFA', 'FA_RX_PAGE_HEIGHT_TOO_SMALL');
2600 -- fa_rx_util_pkg.Log(fnd_message.get);
2601 app_exception.raise_exception;
2602 end if;
2603 end if;
2604
2605 end Validate_Report;
2606
2607
2608
2609 -----------------------------------------
2610 -- Handle Bind variables
2611 -----------------------------------------
2612 FUNCTION get_bind_count RETURN NUMBER
2613 IS
2614 BEGIN
2615 s_bind_idx := 0;
2616 RETURN m_bind_count;
2617 END get_bind_count;
2618
2619 FUNCTION get_bind_variable RETURN VARCHAR2
2620 IS
2621 BEGIN
2622 s_bind_idx := s_bind_idx + 1;
2623 IF s_bind_idx > m_bind_count THEN RETURN NULL;
2624 END IF;
2625 RETURN m_binds(s_bind_idx);
2626 END get_bind_variable;
2627
2628 PROCEDURE bind_variables(c IN INTEGER)
2629 IS
2630 v VARCHAR2(240);
2631 BEGIN
2632 FOR i IN 1..get_bind_count LOOP
2633 v := get_bind_variable;
2634 IF (g_print_debug) THEN
2635 fa_rx_util_pkg.debug('bind_variables: ' || 'Bind :b'||To_char(i)||' => '||v);
2636 END IF;
2637 dbms_sql.bind_variable(c, ':b'||To_char(i), v);
2638 END LOOP;
2639 END bind_variables;
2640
2641 PROCEDURE get_rows_purged(request_id IN VARCHAR2, l_report_id IN NUMBER,
2642 l_purge_api OUT NOCOPY VARCHAR2,row_num out NUMBER)
2643 is
2644 plsql_block varchar2(200);
2645
2646 begin
2647
2648 IF (g_print_debug) THEN
2649 arp_util_tax.debug('get_rows_purged()');
2650 arp_util_tax.debug('request_id: '||request_id);
2651 END IF;
2652
2653 select purge_api into l_purge_api
2654 from fa_rx_reports
2655 where report_id = l_report_id;
2656
2657 IF (g_print_debug) THEN
2658 arp_util_tax.debug('purge_api: '|| l_purge_api);
2659 END IF;
2660
2661 if l_purge_api is not null then
2662 plsql_block := 'BEGIN :num := ' || l_purge_api||'(:id); end;';
2663 execute immediate plsql_block using out row_num, in to_number(request_id);
2664
2665 IF (g_print_debug) THEN
2666 arp_util_tax.debug('rows purged : '||row_num);
2667 END IF;
2668 end if;
2669 end get_rows_purged;
2670
2671 Procedure Get_Moac_Message(xMoac_Message out NOCOPY Varchar2)
2672 Is
2673 l_ld_sp varchar2(1);
2674 l_Reporting_Level Varchar2(100);
2675 l_reporting_entity_id Varchar2(100);
2676 Begin
2677 xMoac_Message := Null;
2678
2679 for s_current_param_idx in m_params_with_actual_value.first..m_params_with_actual_value.last
2680 LOOP
2681 fa_rx_util_pkg.debug(' name = '||m_params_with_actual_value(s_current_param_idx).name );
2682 fa_rx_util_pkg.debug(' value = '||m_params_with_actual_value(s_current_param_idx).value );
2683 fa_rx_util_pkg.debug(' display_flag = '||m_params_with_actual_value(s_current_param_idx).display_flag );
2684 fa_rx_util_pkg.debug(' format_type = '||m_params_with_actual_value(s_current_param_idx).format_type );
2685 fa_rx_util_pkg.debug(' flex_value_set_id = '||m_params_with_actual_value(s_current_param_idx).flex_value_set_id );
2686 fa_rx_util_pkg.debug(' flex_value_set_name = '||m_params_with_actual_value(s_current_param_idx).flex_value_set_name );
2687 fa_rx_util_pkg.debug(' param_idx = '||m_params_with_actual_value(s_current_param_idx).param_idx );
2688 fa_rx_util_pkg.debug(' column_name = '||m_params_with_actual_value(s_current_param_idx).column_name );
2689 fa_rx_util_pkg.debug(' operator = '||m_params_with_actual_value(s_current_param_idx).operator );
2690
2691 If (m_params_with_actual_value(s_current_param_idx).flex_value_set_name = 'FND_MO_REPORTING_LEVEL') Then
2692 l_Reporting_Level := m_params_with_actual_value(s_current_param_idx).value;
2693 End If;
2694 If (m_params_with_actual_value(s_current_param_idx).flex_value_set_name = 'FND_MO_REPORTING_ENTITY') Then
2695 l_reporting_entity_id := m_params_with_actual_value(s_current_param_idx).value;
2696 End If;
2697 END LOOP;
2698
2699 fa_rx_util_pkg.debug(' l_Reporting_Level = '||l_Reporting_Level);
2700 fa_rx_util_pkg.debug(' l_reporting_entity_id = '||l_reporting_entity_id);
2701
2702 If to_number(l_Reporting_Level) = 1000 Then --* if 1000, then it means it is legal entity.
2703 l_ld_sp:= mo_utils.check_ledger_in_sp(TO_NUMBER(l_reporting_entity_id)); --* if 1000, then use the p_reporting_entity_id
2704 IF l_ld_sp = 'N' THEN
2705 FND_MESSAGE.SET_NAME('FND','FND_MO_RPT_PARTIAL_LEDGER');
2706 xMoac_Message :=FND_MESSAGE.get;
2707 END IF;
2708 END IF;
2709 fa_rx_util_pkg.debug(' xMoac_Message = '||xMoac_Message);
2710 Exception
2711 When Others Then
2712 xMoac_Message := Null;
2713 fa_rx_util_pkg.debug(' In exception xMoac_Message = '||xMoac_Message);
2714 End get_Moac_Message;
2715
2716 end fa_rx_publish;