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