1 PACKAGE BODY JTF_DIAGNOSTIC_COREAPI AS
2 /* $Header: jtfdiagcoreapi_b.pls 120.11.12000000.3 2007/04/05 09:33:32 rudas ship $ */
3
4
5 -----------------------------------------------------
6 --- HTML output APIs
7 -----------------------------------------------------
8
9 procedure line_out_html (text varchar2) is
10 l_ptext varchar2(32767);
11 l_hold_num number;
12 tempClob clob;
13 begin
14 l_hold_num := mod(g_curr_loc, 32767);
15 if l_hold_num + length(text) > 32759 then
16 l_ptext := '<!--' || rpad('*', 32761-l_hold_num,'*') || '-->';
17 --dbms_lob.write(g_hold_output, length(l_ptext), g_curr_loc, l_ptext);
18 --dbms_lob.write(tempClob, length(l_ptext), g_curr_loc, l_ptext);
19 --JTF_DIAGNOSTIC_ADAPTUTIL.setReportClob(tempClob);
20 JTF_DIAGNOSTIC_ADAPTUTIL.addStringToReport(l_ptext);
21 g_curr_loc := g_curr_loc + length(l_ptext);
22 end if;
23 --dbms_lob.write(g_hold_output, length(text)+1, g_curr_loc, text || l_newline);
24 JTF_DIAGNOSTIC_ADAPTUTIL.addStringToReport(text||l_newline);
25 --dbms_lob.write(tempClob, length(text)+1, g_curr_loc, text || l_newline);
26 g_curr_loc := g_curr_loc + length(text)+1;
27 end line_out_html;
28
29
30 -- Procedure Name: Insert_Style_Sheet
31 --
32 -- Usage:
33 -- Insert_Style_Sheet;
34 --
35 -- Output:
36 -- Inserts a Style Sheet into the output
37 --
38 -- Comments:
39 -- This is not normally needed as the style sheet is automatically
40 -- inserted with the header.
41 --
42 procedure Insert_Style_Sheet_html is
43 styleSheet varchar2(5000);
44 begin
45 /*line_out('<style type="text/css">');
46 line_out('<!--');
47 line_out('.tab0 {font-size: 9pt; font-weight: normal}');
48 line_out('.tab1 {text-indent: .25in; font-size: 9pt; font-weight: normal}');
49 line_out('.tab2 {text-indent: .5in; font-size: 9pt; font-weight: normal}');
50 line_out('.tab3 {text-indent: .75in; font-size: 9pt; font-weight: normal}');
51 line_out('.error {color: #cc0000; font-size: 9pt; font-weight: normal}');
52 line_out('.errorbold {font-weight: bold; color: #cc0000; font-size: 9pt}');
53 line_out('.warning {font-weight: normal; color: #3c3c3c; font-size: 9pt}');
54 line_out('.warningbold {font-weight: bold; color: #3c3c3c; font-size: 9pt}');
55 line_out('.section {font-weight: normal; font-size: 9pt}');
56 line_out('.sectionbold {font-weight: bold; font-size: 9pt}');
57 line_out('.BigPrint {font-weight: bold; font-size: 12pt}');
58 line_out('.SmallPrint {font-weight: normal; font-size: 8pt}');
59 line_out('.BigError {color: #cc0000; font-size: 12pt; font-weight: bold}');
60 line_out('body.report {background-color: white; font: normal 12pt Ariel;}');
61 line_out('table.report {background-color: #f2f2f5 color:#000000; font-size: 9pt; font-weight: bold; line-height:1.5; padding:2px; text-align:left}');
62 line_out('h1.report, h2.report, h3.report, h4.report {color: #00000}');
63 line_out('h3.report {font-size: 16pt}');
64 line_out('td.report {background-color: #eaeff5; color: #000000; font-weight: normal; font-size: 9pt; border-style: solid; border-width: 1; border-color: #c9cdb3; white-space: nowrap}');
65 line_out('tr.report {background-color: #f2f2f5; color: #c3c3c3; font-weight: normal; font-size: 9pt; white-space: nowrap}');
66 line_out('th.report {background-color: #cfe0f1; color: #000000; height: 20; border-style: solid; border-width: 1; border-left-color: #c9cdb3; border-right-color: #c9cdb3; border-top-width: 0; border-bottom-width: 0; white-space: nowrap}');
67 line_out('th.rowh {background-color: #cfe0f1; color: #000000; height: 20; border-style: solid; border-width: 1; border-top-color: #c9cdb3; border-bottom-color: #c9cdb3; border-left-width: 0; border-right-width: 0; white-space: nowrap}');
68 line_out('-->');
69 line_out('</style>'); */
70 styleSheet := '<style type="text/css">';
71 styleSheet := concat(styleSheet, '<!--');
72 styleSheet := concat(styleSheet, '.tab0 {font-size: 10pt; font-weight: normal}');
73 styleSheet := concat(styleSheet, '.tab1 {text-indent: .25in; font-size: 10pt; font-weight: normal}');
74 styleSheet := concat(styleSheet, '.tab2 {text-indent: .5in; font-size: 10pt; font-weight: normal}');
75 styleSheet := concat(styleSheet, '.tab2 {text-indent: .5in; font-size: 10pt; font-weight: normal}');
76 styleSheet := concat(styleSheet, '.tab3 {text-indent: .75in; font-size: 10pt; font-weight: normal}');
77 styleSheet := concat(styleSheet, '.error {color: #cc0000; font-size: 10pt; font-weight: normal}');
78 styleSheet := concat(styleSheet, '.errorbold {font-weight: bold; color: #cc0000; font-size: 10pt}');
79 styleSheet := concat(styleSheet, '.warning {font-weight: normal; color: #336699; font-size: 10pt}');
80 styleSheet := concat(styleSheet, '.warningbold {font-weight: bold; color: #336699; font-size: 10pt}');
81 styleSheet := concat(styleSheet, '.section {font-weight: normal; font-size: 10pt}');
82 styleSheet := concat(styleSheet, '.sectionbold {font-weight: bold; font-size: 10pt}');
83 styleSheet := concat(styleSheet, '.BigPrint {font-weight: bold; font-size: 12pt}');
84 styleSheet := concat(styleSheet, '.SmallPrint {font-weight: normal; font-size: 8pt}');
85 styleSheet := concat(styleSheet, '.BigError {color: #cc0000; font-size: 12pt; font-weight: bold}');
86 styleSheet := concat(styleSheet, 'body.report {background-color: white; font: normal 12pt Ariel;}');
87 styleSheet := concat(styleSheet, 'table.report {background-color: #000000 color:#000000; font-size: 10pt; font-weight: bold; line-height:1.5; padding:2px; text-align:left}');
88 styleSheet := concat(styleSheet, 'h1.report, h2.report, h3.report, h4.report {color: #00000}');
89 styleSheet := concat(styleSheet, 'h3.report {font-size: 16pt}');
90 styleSheet := concat(styleSheet, 'td.report {background-color: #f7f7e7; color: #000000; font-weight: normal; font-size: 9pt; border-style: solid; border-width: 1; border-color: #CCCC99; white-space: nowrap}');
91 styleSheet := concat(styleSheet, 'tr.report {background-color: #f7f7e7; color: #000000; font-weight: normal; font-size: 9pt; white-space: nowrap}');
92 styleSheet :=
93 concat(styleSheet, 'th.report {background-color: #CCCC99; color: #336699; height: 20; border-style: solid; border-width: 1; border-left-color: #f7f7e7; border-right-color: #f7f7e7; border-top-width: 0; border-bottom-width: 0; white-space: nowrap}');
94 styleSheet :=
95 concat(styleSheet, 'th.rowh {background-color: #CCCC99; color: #336699; height: 20; border-style: solid; border-width: 1; border-top-color: #f7f7e7; border-bottom-color: #f7f7e7; border-left-width: 0; border-right-width: 0; white-space: nowrap}');
96 styleSheet := concat(styleSheet, '-->');
97 styleSheet := concat(styleSheet, '</style>');
98
99 line_out(styleSheet);
100
101 end;
102
103 -- Procedure Name: Insert_HTML
104 --
105 -- Usage:
106 -- Insert_HMTL('String');
107 --
108 -- Parameters:
109 -- String - Any text string
110 --
111 -- Output:
112 -- Displays the text string
113 --
114 -- Examples:
115 -- begin
116 -- Insert_HTML('<em>This can be any text you want.</em>');
117 -- end;
118 --
119 -- Notes:
120 -- Usage of this procedure may make the script not compatible with
121 -- standards or 508. Please avoid if possible.
122 --
123 procedure Insert_HTML_html(p_text varchar2) is
124 begin
125 line_out(p_text);
126 end Insert_HTML_html;
127
128 -- Procedure Name: ActionErrorPrint
129 --
130 -- Usage:
131 -- ActionErrorPrint('String');
132 --
133 -- Parameters:
134 -- String - Any text string
135 --
136 -- Output:
137 -- Displays the text string with the word ACTION - prior to the string
138 --
139 -- Examples:
140 -- begin
141 -- ActionErrorPrint('Run Gather Schema Statistics');
142 -- end;
143 --
144 procedure ActionErrorPrint_html(p_text varchar2) is
145 begin
146 line_out('<span class="errorbold">ACTION - </span><span class="error">' || p_text || '</span><br/>');
147 end ActionErrorPrint_html;
148
149 -- Procedure Name: ActionPrint
150 --
151 -- Usage:
152 -- ActionPrint('String');
153 --
154 -- Parameters:
155 -- String - Any text string
156 --
157 -- Output:
158 -- Displays the text string
159 --
160 -- Examples:
161 -- begin
162 -- ActionPrint('Run Gather Schema Statistics');
163 -- end;
164 --
165 procedure ActionPrint_html(p_text varchar2) is
166 begin
167 line_out(p_text || '<br/>');
168 end ActionPrint_html;
169
170 -- Procedure Name: ActionWarningPrint
171 --
172 -- Usage:
173 -- ActionWarningPrint('String');
174 --
175 -- Parameters:
176 -- String - Any text string
177 --
178 -- Output:
179 -- Displays the text string in warning format
180 --
181 -- Examples:
182 -- begin
183 -- ActionWarningPrint('Run Gather Schema Statistics');
184 -- end;
185 --
186 procedure ActionWarningPrint_html(p_text varchar2) is
187 begin
188 line_out('<span class="warningbold">ACTION - </span><span class="warning">' || p_text || '</span><br/>');
189 end ActionWarningPrint_html;
190
191 -- Procedure Name: WarningPrint
192 --
193 -- Usage:
194 -- WarningPrint('String');
195 --
196 -- Parameters:
197 -- String - Any text string
198 --
199 -- Output:
200 -- Displays the text string in warning format
201 --
202 -- Examples:
203 -- begin
204 -- WarningPrint('Statistics are not up to date');
205 -- end;
206 --
207 procedure WarningPrint_html(p_text varchar2) is
208 begin
209 line_out('<span class="warningbold">WARNING - </span><span class="warning">' || p_text || '</span><br/>');
210 end WarningPrint_html;
211
212 -- Procedure Name: ActionErrorLink
213 --
214 -- Usage:
215 -- ActionErrorLink('Pre_String','Note_Number','Post_String');
216 -- ActionErrorLink('Pre_String','URL','Link_Text', 'Post_String');
217 --
218 -- Parameters:
219 -- Pre_String - Text to appear prior to the link
220 -- Note_Number - Number of a metalink note to link to
221 -- URL - Any valid URL
222 -- Link_Text - Text for the link to URL
223 -- Post_String - Text to appear after the link
224 --
225 -- Output:
226 -- Displays the pre-link string, the link (as specified either by the
227 -- note number or by the URL and link text), and the post-link string
228 -- all in the format of an Error Action
229 --
230 -- Examples:
231 -- begin
232 -- ActionErrorLink('For clarification see note', 112233.1,
233 -- 'which provides more information on the subject');
234 -- ActionErrorLink('For clarification see the',
235 -- 'http://someurl.us.com/somepage.html','Development Homepage',
236 -- 'which provides more information on the subject');
237 -- end;
238 --
239 procedure ActionErrorLink_html(p_txt1 varchar2
240 , p_note varchar2
241 , p_txt2 varchar2) is
242 begin
243 line_out('<span class="errorbold">ACTION - </span><span class="error">'
244 || p_txt1
245 || ' <a href="http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOT&p_id='
246 || p_note
247 || '">'
248 || p_note
249 || '</a> '
250 || p_txt2
251 || '</span><br/>');
252 end ActionErrorLink_html;
253
254 procedure ActionErrorLink_html(p_txt1 varchar2
255 , p_url varchar2
256 , p_link_txt varchar2
257 , p_txt2 varchar2) is
258 begin
259 line_out('<span class="errorbold">ACTION - </span><span class="error">'
260 || p_txt1
261 || ' <a href="'
262 || p_url
263 || '">'
264 || p_link_txt
265 || '</a> '
266 || p_txt2
267 || '</span><br/>');
268 end ActionErrorLink_html;
269
270 -- Procedure Name: ActionWarningLink
271 --
272 -- Usage:
273 -- ActionWarningLink('Pre_String','Note_Number','Post_String');
274 -- ActionWarningLink('Pre_String','URL','Link_Text', 'Post_String');
275 --
276 -- Parameters:
277 -- Pre_String - Text to appear prior to the link
278 -- Note_Number - Number of a metalink note to link to
279 -- URL - Any valid URL
280 -- Link_Text - Text for the link to URL
281 -- Post_String - Text to appear after the link
282 --
283 -- Output:
284 -- Displays the pre-link string, the link (as specified either by the
285 -- note number or by the URL and link text), and the post-link string
286 -- all in the format of a Warning Action
287 --
288 -- Examples:
289 -- begin
290 -- ActionWarningLink('For clarification see note', 112233.1,
291 -- 'which provides more information on the subject');
292 -- ActionWarningLink('For clarification see the',
293 -- 'http://someurl.us.com/somepage.html','Development Homepage',
294 -- 'which provides more information on the subject');
295 -- end;
296 --
297
298 procedure ActionWarningLink_html(p_txt1 varchar2
299 , p_note varchar2
300 , p_txt2 varchar2) is
301 begin
302 line_out('<span class="warningbold">ACTION - </span><span class="warning">'
303 || p_txt1
304 || ' <a href="http://metalink.oracle.com/metalink/plsql/'
305 || 'ml2_documents.showDocument?p_database_id=NOT&p_id='
306 || p_note
307 || '">'
308 || p_note
309 || '</a> '
310 || p_txt2
311 || '</span><br/>');
312 end ActionWarningLink_html;
313
314 procedure ActionWarningLink_html(p_txt1 varchar2
315 , p_url varchar2
316 , p_link_txt varchar2
317 , p_txt2 varchar2) is
318 begin
319 line_out('<span class="warningbold">ACTION - </span><spanclass="warning">'
320 || p_txt1
321 || ' <a href="'
322 || p_url
323 || '">'
324 || p_link_txt
325 || '</a> '
326 || p_txt2
327 || '</span><br/>');
328 end ActionWarningLink_html;
329
330 -- Procedure Name: ErrorPrint
331 --
332 -- Usage:
333 -- ErrorPrint('String');
334 --
335 -- Parameters:
336 -- String - Any text string
337 --
338 -- Output:
339 -- Displays the text string
340 --
341 -- Examples:
342 -- begin
343 -- ErrorPrint('Statistics have not been run');
344 -- end;
345 --
346 procedure ErrorPrint_html(p_text varchar2) is
347 begin
348 line_out('<span class="errorbold">ERROR - </span><span class="error">' || p_text || '</span><br/>');
349 end ErrorPrint_html;
350
351 -- Procedure Name: SectionPrint
352 --
353 -- Usage:
354 -- SectionPrint('String');
355 --
356 -- Parameters:
357 -- String - Any text string
358 --
359 -- Output:
360 -- Displays the text string in bold print
361 --
362 -- Examples:
363 -- begin
364 -- SectionPrint('Checking OE Parameters');
365 -- end;
366 --
367 procedure SectionPrint_html (p_text varchar2) is
368 begin
369 line_out('<br/><span class="sectionbold">' || p_text || '</span><br/>');
370 end SectionPrint_html;
371
372 -- Procedure Name: Tab0Print
373 --
374 -- Usage:
375 -- Tab0Print('String');
376 --
377 -- Parameters:
378 -- String - Any text string
379 --
380 -- Output:
381 -- Displays the text string with no indentation
382 --
383 -- Examples:
384 -- begin
385 -- Tab0Print('Layer 0');
386 -- end;
387 --
388 procedure Tab0Print_html (p_text varchar2) is
389 begin
390 line_out('<div class="tab0">' || p_text || '</div>');
391 end Tab0Print_html;
392
393 -- Procedure Name: Tab1Print
394 --
395 -- Usage:
396 -- Tab1Print('String');
397 --
398 -- Parameters:
399 -- String - Any text string
400 --
401 -- Output:
402 -- Displays the text string indented .25 inch
403 --
404 -- Examples:
405 -- begin
406 -- Tab1Print('Layer 1');
407 -- end;
408 --
409 procedure Tab1Print_html (p_text varchar2) is
410 begin
411 line_out('<div class="tab1">' || p_text || '</div>');
412 end Tab1Print_html;
413
414 -- Procedure Name: Tab2Print
415 --
416 -- Usage:
417 -- Tab2Print('String');
418 --
419 -- Parameters:
420 -- String - Any text string
421 --
422 -- Output:
423 -- Displays the text string indented .50 inch
424 --
425 -- Examples:
426 -- begin
427 -- Tab2Print('Layer 2');
428 -- end;
429 --
430 procedure Tab2Print_html (p_text varchar2) is
431 begin
432 line_out('<div class="tab2">' || p_text || '</div>');
433 end Tab2Print_html;
434
435 -- Procedure Name: Tab3Print
436 --
437 -- Usage:
438 -- Tab3Print('String');
439 --
440 -- Parameters:
441 -- String - Any text string
442 --
443 -- Output:
444 -- Displays the text string indented .75 inch
445 --
446 -- Examples:
447 -- begin
448 -- Tab3Print('Layer 3');
449 -- end;
450 --
451 procedure Tab3Print_html (p_text varchar2) is
452 begin
453 line_out('<div class="tab3">' || p_text || '</div>');
454 end Tab3Print_html;
455
456 -- Procedure Name: BRPrint
457 --
458 -- Usage:
459 -- BRPrint;
460 --
461 -- Output:
462 -- Displays a blank Line
463 --
464 -- Examples:
465 -- begin
466 -- Tab3Print('Layer 3');
467 -- BRPrint;
468 -- Tab3Print('Layer 4');
469 -- end;
470 --
471 procedure BRPrint_html is
472 begin
473 line_out('<br/>');
474 end BRPrint_html;
475
476 -- Procedure Name: CheckFinPeriod
477 --
478 -- Usage:
479 -- CheckFinPeriod('Set of Books ID','Application ID');
480 --
481 -- Paramteters:
482 -- Set of Books ID - ID for the set of books
483 -- Application ID - ID of the application whose periods are being checked
484 --
485 -- Output:
486 -- List the number of defined and open periods. Indicate the latest
487 -- open period. Produce warnings if no periods are open or if the
488 -- current date is not in an open period.
489 --
490 -- Examples:
491 -- CheckFinPeriod(62, 222); -- Check open periods for AR SOB 62
492 -- CheckFinPeriod(202, 201); -- Check open periods for PO SOB 202
493 --
494 -- BVS-START <- Starting ignoring this section
495
496 procedure checkFinPeriod_html (p_sobid NUMBER, p_appid NUMBER ) IS
497 l_appname VARCHAR2(50) :=NULL;
498 l_period_name VARCHAR2(50);
499 l_user_period_type VARCHAR2(50);
500 l_start_date DATE;
501 l_end_date DATE;
502 l_sysdate DATE;
503 l_sysopen VARCHAR2(1);
504
505 CURSOR C1 IS
506 select a.name sobname,
507 count(b.period_name) total_periods,
508 count(decode(b.closing_status,'O',b.PERIOD_NAME,null)) open_periods,
509 a.accounted_period_type period_type
510 from gl_sets_of_books a,
511 gl_period_statuses b
512 where a.set_of_books_id = b.set_of_books_id (+)
513 and b.application_id = p_appId
514 and a.set_of_books_id = p_sobId
515 and b.period_type = a.accounted_period_type
516 group by a.name, a.accounted_period_type;
517
518 c1_rec c1%rowtype;
519 no_rows exception;
520
521 BEGIN
522
523 select application_name
524 into l_appname
525 from fnd_application_vl
526 where application_id = p_appid ;
527
528 open c1;
529 fetch c1 into c1_rec;
530 IF c1%notfound THEN
531 raise no_rows;
532 END IF;
533
534 select user_period_type into l_user_period_type
535 from gl_period_types
536 where period_type = c1_rec.period_type;
537
538 Tab1Print('Set of books '|| c1_rec.sobname ||' for application '
539 || l_appname || ' has ' || to_char(c1_rec.total_periods)
540 || ' periods defined and '|| to_char(c1_rec.open_periods)
541 || ' periods open for period type '|| l_user_period_type);
542 IF c1_rec.total_periods = 0 THEN
543 WarningPrint('There are no periods defined for this Set of books');
544 ActionWarningPrint('There must be periods defined for this set of books');
545 END IF;
546 IF c1_rec.open_periods = 0 THEN
547 WarningPrint('There are no open periods defined for this Set of books');
548 ActionWArningprint('Please consider opening a period for this '||
549 'application and set of books');
550 ELSE
551 BEGIN
552 SELECT period_name, start_date, end_date, sysdate
553 INTO l_period_name, l_start_date, l_end_date, l_sysdate
554 FROM gl_period_statuses
555 WHERE adjustment_period_flag = 'N'
556 AND period_type = c1_rec.period_type
557 AND start_date = (
558 SELECT MAX(start_date)
559 FROM gl_period_statuses
560 WHERE closing_status = 'O'
561 AND adjustment_period_flag = 'N'
562 AND period_type = c1_rec.period_type
563 AND application_id = p_appId
564 AND set_of_books_id = p_sobId )
565 AND closing_status = 'O'
566 AND application_id = p_appId
567 AND set_of_books_id = p_sobId;
568
569 /* check if sysdate is in the latest open period*/
570 l_sysopen := 'N';
571 IF l_sysdate >= l_start_date AND l_sysdate <= l_end_date THEN
572 l_sysOpen := 'Y';
573 END IF;
574 Tab1Print('Latest open period is '|| l_period_name
575 || ' with a start date of '|| to_char(l_start_date)
576 || ' and an end date of ' || to_char(l_end_date) );
577 IF l_sysopen = 'Y' THEN
578 Tab2Print('Current date '|| to_char(l_sysdate)
579 || ' is in the latest open period');
580 ELSE
581 BEGIN
582 SELECT period_name, start_date, end_date, sysdate
583 INTO l_period_name, l_start_date, l_end_date, l_sysdate
584 FROM gl_period_statuses
585 WHERE adjustment_period_flag = 'N'
586 AND period_type = c1_rec.period_type
587 AND sysdate between start_date and end_date
588 AND closing_status = 'O'
589 AND application_id = p_appId
590 AND set_of_books_id = p_sobId;
591
592 Tab2Print('Current date '|| to_char(sysDate)
593 || ' is in the open period ' || l_period_name
594 || ' with a start date of ' || to_char(l_start_date)
595 || ' and an end date of ' || to_char(l_end_date) );
596
597 EXCEPTION WHEN NO_DATA_FOUND THEN
598 WarningPrint('Current date '|| to_char(l_sysdate)
599 || ' is not in an open period');
600 ActionwarningPrint('Please consider opening the current period');
601 END;
602 END IF;
603 EXCEPTION WHEN NO_DATA_FOUND THEN
604 /* not really possible to fall in this exception as we already
605 checked that there were open periods */
606 WarningPrint('There are no open periods defined for this Set of books');
607 ActionWArningprint('Please consider opening a period for this '||
608 'application and set of books');
609 END;
610 END IF;
611 EXCEPTION
612 WHEN NO_ROWS THEN
613 WarningPrint('There are no accounting periods defined in '||
614 'gl_period_statuses for this application and set of books');
615 ActionWArningprint('If required, define the accounting calendar for this '||
616 'application and set of books');
617 WHEN NO_DATA_FOUND THEN
618 ErrorPrint('Invalid Application id passed to checkFinPeriod');
619 ActionErrorPrint('Application id ' || to_char(p_appid)
620 || ' is not valid on this system');
621 WHEN OTHERS THEN
622 ErrorPrint(sqlerrm||' occurred in CheckFinPeriod');
623 ActionErrorPrint('Report this error to your support representative');
624 END checkFinPeriod_html;
625
626 -- BVS-STOP <- Stop ignoring this section (restart scanning)
627
628
629 -- Function Name: CheckKeyFlexfield
630 -- Procedure Name: CheckKeyFlexfield
631 --
632 -- Usage:
633 -- CheckKeyFlexfield('Key Flex Code','Flex Structure ID','Print Header');
634 --
635 -- Parameters:
636 -- Key Flex Code - The code of the Key Flexfield to be displayed. For
637 -- example, for the Accounting Flexfield use 'GL#'.
638 -- Flex Structure ID - The id_flex_num of the specific structure
639 -- of the key flexfield whose details are to be
640 -- displayed. If null, print details of all
641 -- structures. (default NULL)
642 -- Print Header - A boolean (true or false) indicating whether the output
643 -- should print a heading before outputting the details
644 -- of the key flexfield. (default TRUE)
645 -- Returns:
646 -- If value has been provided for the Flex Structure ID, the function
647 -- will returns an array of character strings with the following structure
648 -- 1 name of the flexfield
649 -- 2 enabled flag
650 -- 3 frozen flag
651 -- 4 dynamic instert flag
652 -- 5 cross validation allowed flag
653 -- 6 number of enabled segments defined
654 -- 7 number of enabled segments with value sets
655 -- 8 Y if any segment has security otherwise N
656 -- If no value is passed to the parameter the function will return an
657 -- array will null values.:w
658 --
659 -- Output:
660 -- Displays key information about the flexfield, its structure, and the
661 -- individual flexfield segments that make it up.
662 --
663 -- Examples:
664 -- declare
665 -- flexarray V2T;
666 -- begin
667 -- CheckKeyFlexfield('GL#', 50577, true);
668 -- CheckKeyFlexfield('MSTK', null, false);
669 -- flexarray := CheckKeyFlexfield('GL#', 12345, false);
670 -- end;
671 --
672 -- BVS-START <- Starting ignoring this section
673
674 Function CheckKeyFlexfield_html(p_flex_code in varchar2
675 , p_flex_num in number default null
676 , p_print_heading in boolean default true)
677 return V2T is
678
679 l_ret_array V2T := V2T(null,null,null,null,null,null,null,null);
680 l_no_value_sets integer := 0;
681 l_any_sec_enabled varchar2(1) := 'N';
682 l_sec_enabled varchar2(1) := 'N';
683 l_flex_name fnd_id_flexs.id_flex_name%type;
684 l_counter integer := 0;
685 l_counter2 integer := 0;
686 l_num_segs integer := 0;
687 l_num_segs_vs integer := 0;
688 l_rule_count integer := 0;
689 l_rule_assign_count integer := 0;
690 l_value_set_str varchar2(400);
691 leave_api exception;
692
693 cursor get_structs (p_f_code varchar2, p_f_num number) is
694 select id_flex_num flex_str_num,
695 id_flex_structure_name flex_str_name,
696 to_char(last_update_date,'MM-DD-YYYY HH24:MI:SS') last_updated,
697 cross_segment_validation_flag cross_val,
698 dynamic_inserts_allowed_flag dyn_insert,
699 enabled_flag enabled,
700 freeze_flex_definition_flag frozen
701 from fnd_id_flex_structures_vl
702 where id_flex_code = p_f_code
703 and enabled_flag ='Y'
704 and id_flex_num = nvl(p_f_num,id_flex_num);
705
706 cursor get_segments (p_f_code varchar2, p_f_num number) is
707 select s.application_column_name col_name,
708 s.segment_name seg_name,
709 s.segment_num seg_num,
710 s.enabled_flag enabled,
711 s.required_flag required,
712 s.display_flag displayed,
713 s.flex_value_set_id value_set_id,
714 vs.flex_value_set_name value_set_name,
715 DECODE(vs.validation_type,
716 'I', 'Independent', 'N', 'None', 'D', 'Dependent',
717 'U', 'Special', 'P', 'Pair', 'F', 'Table',
718 'X', 'Translatable Independent', 'Y', 'Translatable Dependent',
719 vs.validation_type) validation_type,
720 s.security_enabled_flag seg_security,
721 nvl(vs.security_enabled_flag,'N') value_set_security
722 from fnd_id_flex_segments_vl s, fnd_flex_value_sets vs
723 where s.flex_value_set_id = vs.flex_value_set_id (+)
724 and s.id_flex_code = p_f_code
725 and s.id_flex_num = p_f_num
726 order by s.segment_num ;
727
728 cursor get_qualifiers(p_f_code varchar2, p_f_num number, p_col_name varchar2) is
729 select segment_prompt
730 from fnd_segment_attribute_values sav,
731 fnd_segment_attribute_types sat
732 where sav.attribute_value = 'Y'
733 and sav.segment_attribute_type <> 'GL_GLOBAL'
734 and sav.application_id = sat.application_id
735 and sav.id_flex_code = sat.id_flex_code
736 and sav.segment_attribute_type = sat.segment_attribute_type
737 and sav.id_flex_code = p_f_code
738 and sav.id_flex_num = p_f_num
739 and sav.application_column_name = p_col_name;
740
741 begin
742 begin
743 select id_flex_name into l_flex_name
744 from fnd_id_flexs
745 where id_flex_code = p_flex_code;
746 exception when no_data_found then
747 WarningPrint('ID Flex Code passed '||p_flex_code||' is not valid on this '||
748 'system');
749 ActionWarningPrint('ID Flex Code '||p_flex_code||' will not be tested');
750 end;
751
752 BRPrint;
753 if p_flex_num is null then
754 if (p_print_heading) then
755 SectionPrint('Details of Key flexfield: '||l_flex_name);
756 else
757 Tab0Print('Key flexfield: '||l_flex_name);
758 end if;
759 else
760 l_ret_array(1) := l_flex_name;
761 if (p_print_heading) then
762 SectionPrint('Details of Key flexfield: '||l_flex_name
763 ||' with id_flex_num '||to_char(p_flex_num));
764 else
765 Tab0Print('Key flexfield: '||l_flex_name||' with id_flex_num '
766 || to_char(p_flex_num));
767 end if;
768 end if;
769
770 l_counter := 0;
771 for str in get_structs(p_flex_code, p_flex_num) loop
772 l_counter := l_counter + 1;
773 if p_flex_num is not null then
774 l_ret_array(2) := str.enabled;
775 l_ret_array(3) := str.frozen;
776 l_ret_array(4) := str.dyn_insert;
777 l_ret_array(5) := str.cross_val;
778 end if;
779 BRPrint;
780 Tab1Print('Structure '||str.flex_str_name||' (ID='||
781 to_char(str.flex_str_num) ||')');
782 Tab1Print('Enabled Flag = '||str.enabled||', Frozen = '||str.frozen
783 ||', Dynamic Inserts = '||str.dyn_insert||', Cross Validation Allowed = '
784 ||str.cross_val||', Last Updated '||str.last_updated);
785
786
787 l_counter2 := 0;
788 l_num_segs := 0;
789 l_num_segs_vs := 0;
790 for seg in get_segments(p_flex_code, str.flex_str_num) loop
791 if l_counter2 = 0 then
792 Tab1Print('Segment Details for '||str.flex_str_name);
793 BRPrint;
794 end if;
795 l_counter2 := l_counter2 + 1;
796
797 if (p_flex_num is not null) then
798 if seg.enabled = 'Y' then
799 l_num_segs := l_num_segs + 1;
800 if (seg.value_set_id is not null) then
801 l_num_segs_vs := l_num_segs_vs + 1;
802 end if;
803 end if;
804 end if;
805 if (seg.seg_security = 'Y' and seg.value_set_security in ('Y','H')) then
806 l_any_sec_enabled := 'Y';
807 l_sec_enabled := 'Y';
808 end if;
809 if (seg.value_set_id is not null) then
810 l_value_set_str := ', Value Set = '||seg.value_set_name||
811 ', Value Set Type = '||seg.validation_type;
812 else
813 l_value_set_str := ' with no value set assigned';
814 end if;
815 Tab2Print('Segment Name = '||seg.seg_name);
816 Tab2Print('Enabled = '||seg.enabled||', Displayed = '||
817 seg.displayed||l_value_set_str);
818
819 for qual in get_qualifiers(p_flex_code,str.flex_str_num,seg.col_name) loop
820 Tab3Print('Qualifier '||qual.segment_prompt||' is assigned to '||
821 'segment '|| seg.seg_name);
822 end loop;
823
824 if l_sec_enabled = 'Y' then
825 select count(*) into l_rule_count
826 from fnd_flex_value_rules_vl
827 where flex_value_set_id = seg.value_set_id;
828
829 select count(*) into l_rule_assign_count
830 from fnd_flex_value_rules_vl r,
831 fnd_flex_value_rule_usages ru
832 where r.flex_value_rule_id = ru.flex_value_rule_id
833 and r.flex_value_set_id = seg.value_set_id;
834
835 Tab3Print('Security is enabled for this segment and value set with '||
836 to_char(l_rule_count)||' rules defined and '||
837 to_char(l_rule_assign_count)||' rule assignments');
838 end if;
839 end loop;
840 if (p_flex_num is not null) then
841 l_ret_array(6) := to_char(l_num_segs);
842 l_ret_array(7) := to_char(l_num_segs_vs);
843 l_ret_array(8) := l_any_sec_enabled;
844 end if;
845 if l_counter2 = 0 then
846 ErrorPrint('There are no segments defined for this structure');
847 ActionErrorPrint('Please enable or define at least one segment for '||
848 str.flex_str_name);
849 end if;
850 end loop;
851 if l_counter = 0 then
852 if p_flex_num is null then
853 ErrorPrint('There are no Key Flexfields enabled for ' || p_flex_code);
854 ActionErrorPrint('Please enable or define a Key Flexfield for ' ||
855 p_flex_code);
856 else
857 ErrorPrint('The requested flexfield structure (ID_FLEX_NUM='||
858 to_char(p_flex_num)||') is inactive or does not exist');
859 ActionErrorPrint('Verify that the flexfield structure is defined '||
860 'and enabled for Key Flexfield '||p_flex_code);
861 end if;
862 end if;
863 return l_ret_array;
864 exception
865 when leave_api then
866 return l_ret_array;
867 end CheckKeyFlexfield_html;
868 -- BVS-STOP <- Stop ignoring this section (restart scanning)
869
870
871 procedure CheckKeyFlexfield_html(p_flex_code in varchar2
872 , p_flex_num in number default null
873 , p_print_heading in boolean default true) is
874 dummy_v2t V2T;
875 begin
876 dummy_v2t := CheckKeyFlexfield(p_flex_code, p_flex_num, p_print_heading);
877 end CheckKeyFlexfield_html;
878
879 -- Function Name: CheckProfile
880 -- Procedure Name: CheckProfile
881 --
882 -- Usage:
883 -- CheckProfile('Profile Name', UserID, ResponsibilityID,
884 -- ApplicationID, 'Default Value', Indent Level);
885 --
886 -- Parameters:
887 -- Profile Name - System name of the profile option being checked
888 -- UserID - The identifier of that applications user for which the
889 -- profile option is to be checked.
890 -- ResponsibilityID - The identifier of the responsibility for which
891 -- the profile option is to be checked
892 -- ApplicationID - The identifier of the application for which the profile
893 -- option is to be checked
894 -- Default Value - The value that will be used as a default if the profile
895 -- option is not set by the users (Default=NULL)
896 -- Indent Level - Number of tabs (0,1,2,3) that output should be indented
897 -- (Default=0)
898 --
899 -- Returns:
900 -- If called as a function the return value will be either:
901 -- 1 the value of the profile option if set
902 -- 2 'DOESNOTEXIST' if the profile option does not exist
903 -- 3 'DISABLED' if the profile option has been end dated
904 -- 4 null if the profile option is not set
905 --
906 -- Output:
907 -- If the profile is set, displays its current setting. If not set and
908 -- a default value exists, displays a warning indicating that the default
909 -- value will be used and indicating the value of the default. If not set
910 -- and no default value is supplied, displays an error indicating that
911 -- the profile option should be set. Output will be indented according
912 -- to the Indent Level parameter supplied.
913 --
914 -- If the profile option does not exist or is disabled there is no
915 -- output.
916 --
917 -- Examples:
918 -- declare
919 -- profile_val fnd_profile_option_values.profile_option_value%type;
920 -- begin
921 -- profile_val := CheckProfile('PA_SELECTIVE_FLEX_SEG',g_user_id,
922 -- g_resp_id, g_appl_id, null, 1);
923 --
924 -- CheckProfile('PA_DEBUG_MODE',g_user_id, g_resp_id, g_appl_id);
925 -- CheckProfile('PA_DEBUG_MODE',g_user_id, g_resp_id, g_appl_id,'Y',2);
926 -- end;
927 --
928 -- BVS-START <- Starting ignoring this section
929
930 function CheckProfile_html(p_prof_name in varchar2
931 , p_user_id in number
932 , p_resp_id in number
933 , p_appl_id in number
934 , p_default in varchar2 default null
935 , p_indent in integer default 0)
936 return varchar2 is
937 l_user_prof_name fnd_profile_options_tl.user_profile_option_name%type;
938 l_prof_value fnd_profile_option_values.profile_option_value%type;
939 l_start_date date;
940 l_end_date date;
941 l_opt_defined boolean;
942 l_output_txt varchar2(500);
943 begin
944 begin
945 select user_profile_option_name,
946 nvl(start_date_active,sysdate-1),
947 nvl(end_date_active,sysdate+1)
948 into l_user_prof_name, l_start_date, l_end_date
949 from fnd_profile_options_vl
950 where profile_option_name = p_prof_name;
951 exception
952 when no_data_found then
953 l_prof_value := 'DOESNOTEXIST';
954 return(l_prof_value);
955 when others then
956 ErrorPrint(sqlerrm||' occured while getting profile option '||
957 'information');
958 ActionErrorPrint('Report the above information to your support '||
959 'representative');
960 return(null);
961 end;
962 if ((sysdate < l_start_date) or (sysdate > l_end_date)) then
963 l_prof_value := 'DISABLED';
964 return(l_prof_value);
965 end if;
966 fnd_profile.get_specific(p_prof_name, p_user_id, p_resp_id, p_appl_id,
967 l_prof_value, l_opt_defined);
968 if not l_opt_defined then
969 l_prof_value := null;
970 end if;
971 if l_prof_value is null then
972 if p_default is null then
973 ErrorPrint(l_user_prof_name || ' profile option is not set');
974 ActionErrorPrint('Please set the profile option according to '||
975 'the user manual');
976 return(l_prof_value);
977 else
978 WarningPrint(l_user_prof_name || ' profile option is not set '||
979 'and will default to ' || p_default);
980 ActionWarningPrint('Please set the profile option according to '||
981 'the user manual if you do not want to use the default');
982 return(l_prof_value);
983 end if;
984 else
985 l_output_txt := l_user_prof_name || ' profile option is set to -- ' ||
986 l_prof_value;
987 if p_indent = 1 then
988 Tab1Print(l_output_txt);
989 elsif p_indent = 2 then
990 Tab2Print(l_output_txt);
991 elsif p_indent = 3 then
992 Tab3Print(l_output_txt);
993 else
994 Tab0Print(l_output_txt);
995 end if;
996 return(l_prof_value);
997 end if;
998 exception when others then
999 ErrorPrint(sqlerrm||' occured in CheckProfile');
1000 ActionErrorPrint('Please report this error to your support representative');
1001 end CheckProfile_html;
1002
1003 procedure CheckProfile_html(p_prof_name in varchar2
1004 , p_user_id in number
1005 , p_resp_id in number
1006 , p_appl_id in number
1007 , p_default in varchar2 default null
1008 , p_indent in integer default 0) is
1009 l_dummy_prof_value fnd_profile_option_values.profile_option_value%type;
1010 begin
1011 l_dummy_prof_value := CheckProfile(p_prof_name, p_user_id, p_resp_id,
1012 p_appl_id, p_default, p_indent);
1013 end CheckProfile_html;
1014 -- BVS-STOP <- Stop ignoring this section (restart scanning)
1015
1016
1017 -- Function Name: Column_Exists
1018 --
1019 -- Usage:
1020 -- Column_Exists('Table Name','Column Name');
1021 --
1022 -- Paramteters:
1023 -- Table Name - Table in which to check for the column
1024 -- Column Name - Column to check
1025 --
1026 -- Returns:
1027 -- 'Y' if the column exists in the table, 'N' if not.
1028 --
1029 -- Example:
1030 -- declare
1031 -- sqltxt varchar2(1000);
1032 -- begin
1033 -- if Column_Exists('PA_IMPLEMENTATIONS_ALL','UTIL_SUM_FLAG') = 'Y' then ;
1034 -- sqltxt := sqltxt||' and i.util_sum_flag is not null';
1035 -- end if;
1036 -- end;
1037 --
1038 function Column_Exists_html(p_tab in varchar, p_col in varchar, p_owner in varchar) return varchar2 is
1039 l_counter integer:=0;
1040 begin
1041
1042 -- UNSURE!! SHOULD WE SEEK OWNER AS PARAMETER
1043 -- DECIDED TO DO SO
1044
1045 select count(*) into l_counter
1046 from all_tab_columns z
1047 where z.table_name = upper(p_tab)
1048 and z.column_name = upper(p_col)
1049 and upper(z.owner) = upper(p_owner);
1050
1051 if l_counter > 0 then
1052 return('Y');
1053 else
1054 return('N');
1055 end if;
1056 exception when others then
1057 ErrorPrint(sqlerrm||' occured in Column_Exists');
1058 ActionErrorPrint('Report this information to your support analyst');
1059 raise;
1060 end Column_Exists_html;
1061
1062 -- Procedure Name: Begin_Pre
1063 --
1064 -- Usage:
1065 -- Begin_Pre;
1066 --
1067 -- Output:
1068 -- Allows the following output to be preformatted
1069 --
1070 -- Examples:
1071 -- begin
1072 -- Begin_Pre;
1073 -- end;
1074 --
1075 procedure Begin_Pre_html is
1076 begin
1077 line_out('<pre>');
1078 end Begin_Pre_html;
1079
1080 -- Procedure Name: End_Pre
1081 --
1082 -- Usage:
1083 -- End_Pre;
1084 --
1085 -- Output:
1086 -- Closes the Begin_Pre procedure
1087 --
1088 -- Examples:
1089 -- begin
1090 -- End_Pre;
1091 -- end;
1092 --
1093 procedure End_Pre_html is
1094 begin
1095 line_out('</pre>');
1096 end End_Pre_html;
1097
1098 procedure Show_Table_html(p_type varchar2, p_values V2T, p_caption varchar2 default null, p_options V2T default null) is
1099 l_hold_option varchar2(500);
1100 temp varchar2(500);
1101 begin
1102
1103 -- if table, then add viewInExcel attr to help Excel reporting
1104 if upper(p_type) in ('START','TABLE') then
1105 if p_caption is null then
1106 line_out('<table class="report" cellspacing="0" viewInExcel="true">');
1107 else
1108 temp := replace(p_caption, '&', '&');
1109 temp := replace(temp, '"', '"');
1110 temp := replace(replace(temp,'<','<'),'>','>');
1111 line_out('<br/><table class="report" cellspacing="0" summary="' || temp || '" viewInExcel="true">');
1112 end if;
1113 end if;
1114 if upper(p_type) in ('TABLE','ROW', 'HEADER') then
1115 line_out('<tr class="report">');
1116 for i in 1..p_values.COUNT loop
1117 if p_options is not null then
1118 l_hold_option := ' ' || p_options(i);
1119 end if;
1120 if p_values(i) = ' '
1121 or p_values(i) is null then
1122 if upper(p_type) = 'HEADER' then
1123 line_out('<th class="report" id="' || i || '"> </th>');
1124 else
1125 line_out('<td class="report" headers="' || i || '"> </td>');
1126 end if;
1127 else
1128 if upper(p_type) = 'HEADER' then
1129 line_out('<th class="report" ' || l_hold_option || ' id="' || i || '">' || p_values(i) || '</th>');
1130 else
1131 line_out('<td class="report" ' || l_hold_option || ' headers="' || i || '">' || p_values(i) || '</td>');
1132 end if;
1133 end if;
1134 end loop;
1135 line_out('</tr>');
1136 end if;
1137 if upper(p_type) in ('TABLE','END') then
1138 line_out('</table>');
1139 --line_out('</TABLE>');
1140 end if;
1141 end Show_Table_html;
1142
1143 procedure Show_Table_html(p_values V2T) is
1144 begin
1145 Show_Table('TABLE',p_values);
1146 end Show_Table_html;
1147
1148 procedure Show_Table_html(p_type varchar2) is
1149 begin
1150 Show_Table(p_type,null);
1151 end Show_Table_html;
1152
1153 procedure Show_Table_Row_html(p_values V2T, p_options V2T default null) is
1154 begin
1155 Show_Table('ROW', p_values, null, p_options);
1156 end Show_Table_Row_html;
1157
1158 procedure Show_Table_Header_html(p_values V2T, p_options V2T default null) is
1159 begin
1160 Show_Table('HEADER', p_values, null, p_options);
1161 end Show_Table_Header_html;
1162
1163 procedure Start_Table_html (p_caption varchar2 default null) is
1164 begin
1165 Show_Table('START',null, p_caption);
1166 end Start_Table_html;
1167
1168 procedure End_Table_html is
1169 begin
1170 Show_Table('END',null);
1171 end End_Table_html;
1172
1173 -- Function Name: Display_SQL
1174 --
1175 -- Usage:
1176 -- a_number := Display_SQL('SQL statement','Name for Header','Long Flag',
1177 -- 'Feedback', 'Max Rows');
1178 --
1179 -- Parameters:
1180 -- SQL Statement - Any valid SQL Select Statement
1181 -- Name for Header - Text String to for heading the output
1182 -- Long Flag - Y or N - If set to N then this will not output
1183 -- any LONG columns (default = Y)
1184 -- Feedback - Y or N indicates whether to indicate the number of rows
1185 -- selected automatically in the output (default = Y)
1186 -- Max Rows - Limits the number of rows output to this number. NULL or
1187 -- ZERO value indicates unlimited. (Default = NULL)
1188 --
1189 -- Returns:
1190 -- The function returns the # of rows selected.
1191 -- If there is an error then the function returns -1.
1192 --
1193 -- Output:
1194 -- Displays the output of the SQL statement as an HTML table.
1195 --
1196 -- Examples:
1197 -- declare
1198 -- num_rows number;
1199 -- begin
1200 -- num_rows := Display_SQL('select * from ar_system_parameters_all',
1201 -- 'AR Parameters', 'Y', 'N',null);
1202 -- num_rows := Display_SQL('select * from pa_implementations_all',
1203 -- 'PA Implementation Options');
1204 -- end;
1205 --
1206
1207 function Display_SQL_html (p_sql_statement varchar2
1208 , table_alias varchar2
1209 , hideHeader Boolean
1210 , display_longs varchar2 default 'Y'
1211 , p_feedback varchar2 default 'Y'
1212 , p_max_rows number default null
1213 , p_current_exec number default 0) return number is
1214
1215 error_position number;
1216 error_position_end number;
1217 row_counter number;
1218 hold_exclude_cols boolean;
1219 hold_sql_needed varchar2(3);
1220 hold_string varchar2(32767) default null;
1221 hold_option varchar2(32767) default null;
1222 hold_sql varchar2(32767) default null;
1223 hold_sql_remain varchar2(32767) default null;
1224 hold_element varchar2(32767) default null;
1225 hold_long long;
1226 hold_clob clob;
1227 hold_length varchar2(40);
1228 hold_bgcolor varchar2(40);
1229 hold_color varchar2(40);
1230 hold_open_paren number;
1231 hold_curr_loc number;
1232 hold_end_pos number;
1233 column_counter binary_integer default 1;
1234 value_counter binary_integer default 1;
1235
1236 column_high binary_integer default 1;
1237 value_high binary_integer default 1;
1238 v_cursor_id number;
1239 v_dummy integer;
1240 l_hold_length varchar2(20);
1241 l_hold_date_format varchar2(40);
1242 l_hold_type varchar2(40);
1243 l_max_rows integer;
1244 l_feedback_txt varchar2(200);
1245
1246 v_values V2T;
1247 v_options V2T;
1248 v_describe dbms_sql.desc_tab;
1249
1250 T_VARCHAR2 constant integer := 1;
1251 T_NUMBER constant integer := 2;
1252 T_LONG constant integer := 8;
1253 T_ROWID constant integer := 11;
1254 T_DATE constant integer := 12;
1255 T_RAW constant integer := 23;
1256 T_CHAR constant integer := 96;
1257 T_TYPE constant integer := 109;
1258 T_CLOB constant integer := 112;
1259 T_BLOB constant integer := 113;
1260 T_BFILE constant integer := 114;
1261 temp varchar2(500);
1262
1263 begin
1264 -- line_out('<table>');
1265 if nvl(p_max_rows,0) = 0 then
1266 l_max_rows := null;
1267 else
1268 l_max_rows := p_max_rows;
1269 end if;
1270
1271 if p_current_exec = 0 then
1272 select value into l_hold_date_format
1273 from nls_session_parameters where parameter = 'NLS_DATE_FORMAT';
1274 execute immediate 'alter session set nls_date_format =
1275 ''MM-DD-YYYY HH24:MI''';
1276 end if;
1277 begin
1278 v_cursor_id := DBMS_SQL.OPEN_CURSOR;
1279 DBMS_SQL.PARSE(v_cursor_id, p_sql_statement, DBMS_SQL.V7);
1280 DBMS_SQL.DESCRIBE_COLUMNS(v_cursor_id, column_high, v_describe);
1281 hold_sql := 'select ';
1282 hold_sql_needed := null;
1283 hold_exclude_cols := false;
1284 hold_sql_remain := ltrim(substr(replace(p_sql_statement,l_newline,' '), 7));
1285 for value_counter in 1..column_high loop
1286 if v_describe(value_counter).col_type = T_LONG then
1287 hold_length := 25000;
1288 else
1289 hold_length := to_number(v_describe(value_counter).col_max_len);
1290 end if;
1291 if v_describe(value_counter).col_type in (T_DATE, T_VARCHAR2,
1292 T_NUMBER, T_CHAR, T_ROWID) then
1293 DBMS_SQL.DEFINE_COLUMN(v_cursor_id, value_counter,
1294 hold_string, greatest(TO_NUMBER(hold_length),30));
1295 elsif v_describe(value_counter).col_type = T_CLOB then
1296 DBMS_SQL.DEFINE_COLUMN(v_cursor_id, value_counter, hold_clob);
1297 else
1298 null;
1299 end if;
1300 hold_string := v_describe(value_counter).col_name;
1301 if value_counter = 1 then
1302 v_values := V2T(replace(initcap(hold_string),'|','<br/>'));
1303 else
1304 v_values.EXTEND;
1305 v_values(value_counter) := replace(initcap(hold_string),'|','<br/>');
1306 end if;
1307 if substr(hold_sql_remain,1,1) <> '*' then
1308 hold_end_pos := 1;
1309 hold_open_paren := 0;
1310 loop
1311 if substr(hold_sql_remain,hold_end_pos,1) = '(' then
1312 hold_open_paren := hold_open_paren + 1;
1313 elsif substr(hold_sql_remain,hold_end_pos,1) = ')' then
1314 hold_open_paren := hold_open_paren - 1;
1315 elsif substr(hold_sql_remain,hold_end_pos,1) = ',' or
1316 lower(substr(hold_sql_remain, hold_end_pos, 4)) = ' from ' then
1317 if hold_open_paren = 0 then
1318 exit;
1319 end if;
1320 end if;
1321 hold_end_pos := hold_end_pos + 1;
1322 if hold_end_pos > length(p_sql_statement) then
1323 exit;
1324 end if;
1325 end loop;
1326 hold_element := substr(hold_sql_remain, 1, hold_end_pos);
1327 hold_sql_remain := ltrim(substr(hold_sql_remain, hold_end_pos + 1));
1328 else
1329 hold_element := v_describe(value_counter).col_name;
1330 end if;
1331 if v_describe(value_counter).col_type in
1332 (T_VARCHAR2, T_CHAR, T_NUMBER, T_DATE, T_LONG, T_CLOB, T_ROWID) then
1333 hold_sql := hold_sql || hold_sql_needed || hold_element;
1334 else
1335 hold_exclude_cols := true;
1336 end if;
1337 hold_sql_needed := ', ';
1338 end loop;
1339 if hold_exclude_cols then
1340 DBMS_SQL.CLOSE_CURSOR(v_cursor_id);
1341 hold_sql := hold_sql || ' ' ||
1342 substr(p_sql_statement,instr(lower(p_sql_statement),' from '));
1343 row_counter := Display_SQL (hold_sql, table_alias, display_longs,
1344 p_feedback, p_max_rows, p_current_exec + 1) + 1;
1345 else
1346 if table_alias is not null then
1347 temp := replace(table_alias, '&', '&');
1348 temp := replace(replace(temp,'<','<'),'>','>');
1349 end if;
1350 if hideHeader = FALSE then
1351 --Always print the header if user doesnot want to hide headers
1352 line_out('<br/><span class="BigPrint">' || temp || '</span>');
1353 end if;
1354
1355 v_dummy := DBMS_SQL.EXECUTE(v_cursor_id);
1356
1357 row_counter := 1;
1358 loop
1359 if DBMS_SQL.FETCH_ROWS(v_cursor_id) = 0 then
1360 if row_counter > 1 then
1361 Show_Table('END');
1362 end if;
1363 exit;
1364 end if;
1365 if row_counter = 1 then
1366 /*This will print the header if the user doesnot want to print
1367 header if there is no rows.This line will be reached only if there is
1368 atleast one row and it will pirnt header only if rows are selected
1369 */
1370 if hideHeader = TRUE then
1371 line_out('<br/><span class="BigPrint">' || temp || '</span>');
1372 end if;
1373 Start_Table(table_alias);
1374 Show_Table_Header(v_values);
1375 end if;
1376 for value_counter in 1..column_high loop
1377 if v_describe(value_counter).col_type in
1378 (T_DATE, T_VARCHAR2, T_NUMBER, T_CHAR, T_ROWID) then
1379 DBMS_SQL.COLUMN_VALUE(v_cursor_id,value_counter,hold_string);
1380 else
1381 DBMS_SQL.COLUMN_VALUE(v_cursor_id,value_counter,hold_clob);
1382 hold_string := 'CLOB';
1383 end if;
1384 hold_string := nvl(hold_string,' ');
1385 hold_option := null;
1386 if v_describe(value_counter).col_type = T_DATE then
1387 --hold_string := replace(hold_string,' ',' ');
1388 hold_option := 'nowrap="nowrap" align="right"';
1389 elsif v_describe(value_counter).col_type = T_VARCHAR2 then
1390 hold_string := replace(hold_string, '&', '&');
1391 hold_string := replace(replace(hold_string,'<','<'),
1392 '>','>');
1393 if hold_string <> rtrim(hold_string) then
1394 hold_option := 'nowrap="nowrap" bgcolor="yellow"';
1395 else
1396 hold_option := 'nowrap="nowrap"';
1397 end if;
1398 elsif v_describe(value_counter).col_type = T_NUMBER then
1399 hold_option := 'nowrap="nowrap" align="right"';
1400 else
1401 null;
1402 end if;
1403 if value_counter = 1 then
1404 v_values := V2T(hold_string);
1405 v_options := V2T(hold_option);
1406 else
1407 v_values.EXTEND;
1408 v_values(value_counter) := hold_string;
1409 v_options.EXTEND;
1410 v_options(value_counter) := hold_option;
1411 end if;
1412 end loop;
1413 Show_Table_Row(v_values, v_options);
1414 row_counter := row_counter + 1;
1415 if row_counter > nvl(l_max_rows,row_counter) then
1416 Show_Table('END');
1417 exit;
1418 end if;
1419 end loop;
1420 DBMS_SQL.CLOSE_CURSOR(v_cursor_id);
1421 end if;
1422 if p_current_exec = 0 and p_feedback = 'Y' then
1423 if row_counter = 1 then
1424 if hideHeader = FALSE then
1425 l_feedback_txt := '<BR/><span class="SmallPrint">'||
1426 '0 Rows Selected</span><br/>';
1427 end if;
1428 elsif row_counter = 2 then
1429 l_feedback_txt := '<span class="SmallPrint">'||
1430 '1 Row Selected</span><br/>';
1431 else
1432 l_feedback_txt := '<span class="SmallPrint">'||
1433 ltrim(to_char(row_counter - 1,'9999999')) ||
1434 ' Rows Selected</span><br/>';
1435 end if;
1436 line_out(l_feedback_txt);
1437 execute immediate 'alter session set nls_date_format = ''' ||
1438 l_hold_date_format || '''';
1439 end if;
1440 if p_current_exec = 0 and row_counter = 1 then
1441 line_out('<BR/>');
1442 end if;
1443 return row_counter-1;
1444 exception
1445 when others then
1446 line_out('</table><br/>');
1447 error_position := DBMS_SQL.LAST_ERROR_POSITION;
1448 ErrorPrint(sqlerrm || ' occurred in Display_SQL');
1449 ActionErrorPrint('Please report the error below to your support '||
1450 'representative');
1451 line_out('Position: ' || error_position || ' of ' ||
1452 length(p_sql_statement) || '<br/>');
1453 line_out(replace(substr(p_sql_statement,1,error_position),l_newline,
1454 '<br/>'));
1455 error_position_end := instr(p_sql_statement,' ',error_position+1) -
1456 error_position;
1457 line_out('<span class="error">' ||
1458 replace(substr(p_sql_statement,error_position+1,
1459 error_position_end),l_newline,'<br/>') || '</span>');
1460 line_out(replace(substr(p_sql_statement,error_position+
1461 error_position_end+1),l_newline,'<br/>') || '<br/>');
1462 DBMS_SQL.CLOSE_CURSOR(v_cursor_id);
1463 if p_current_exec = 0 then
1464 execute immediate 'alter session set nls_date_format = ''' ||
1465 l_hold_date_format || '''';
1466 end if;
1467 return -1;
1468 end;
1469 end Display_SQL_html;
1470
1471 -- Function Name: Run_SQL
1472 --
1473 -- Usage:
1474 -- a_number := Run_SQL('Heading', 'SQL statement');
1475 -- a_number := Run_SQL('Heading', 'SQL statement', 'Feedback');
1476 -- a_number := Run_SQL('Heading', 'SQL statement', 'Max Rows');
1477 -- a_number := Run_SQL('Heading', 'SQL statement', 'Feedback', 'Max Rows');
1478 --
1479 -- Parameters:
1480 -- Heading - Text String to for heading the output
1481 -- SQL Statement - Any valid SQL Select Statement
1482 -- Feedback - Y or N to indicate whether to automatically print the
1483 -- number of rows returned (default 'Y')
1484 -- Max Rows - Limit the output to this many rows. NULL or ZERO values
1485 -- indicate unlimited rows (default NULL)
1486 --
1487 -- Returns:
1488 -- The function returns the # of rows selected.
1489 -- If there is an error then the function returns -1.
1490 --
1491 -- Output:
1492 -- Displays the output of the SQL statement as an HTML table.
1493 --
1494 -- Examples:
1495 -- declare
1496 -- num_rows number;
1497 -- begin
1498 -- num_rows := Run_SQL('AR Parameters', 'select * from ar_system_parameters_all');
1499 -- end;
1500 --
1501 function Run_SQL_html(p_title varchar2, p_sql_statement varchar2) return number is
1502 begin
1503 return(Display_SQL(p_sql_statement , p_title ,'Y','Y',null));
1504 end Run_SQL_html;
1505
1506 function Run_SQL_html(p_title varchar2
1507 , p_sql_statement varchar2
1508 , p_feedback varchar2) return number is
1509 begin
1510 return(Display_SQL(p_sql_statement , p_title ,'Y',p_feedback,null));
1511 end Run_SQL_html;
1512
1513 function Run_SQL_html(p_title varchar2
1514 , p_sql_statement varchar2
1515 , p_max_rows number) return number is
1516 begin
1517 return(Display_SQL(p_sql_statement , p_title ,'Y','Y',p_max_rows));
1518 end Run_SQL_html;
1519
1520 function Run_SQL_html(p_title varchar2
1521 , p_sql_statement varchar2
1522 , p_feedback varchar2
1523 , p_max_rows number) return number is
1524 begin
1525 return(Display_SQL(p_sql_statement , p_title ,'Y',p_feedback,p_max_rows));
1526 end Run_SQL_html;
1527
1528 -- Procedure Name: Run_SQL
1529 --
1530 -- Usage:
1531 -- Run_SQL('Heading', 'SQL statement');
1532 -- Run_SQL('Heading', 'SQL statement', 'Feedback');
1533 -- Run_SQL('Heading', 'SQL statement', 'Max Rows');
1534 -- Run_SQL('Heading', 'SQL statement', 'Feedback', 'Max Rows');
1535 --
1536 -- Parameters:
1537 -- SQL Statement - Any valid SQL Select Statement
1538 -- Heading - Text String to for heading the output
1539 --
1540 -- Output:
1541 -- Displays the output of the SQL statement as an HTML table.
1542 --
1543 -- Examples:
1544 -- begin
1545 -- Run_SQL('AR Parameters', 'select * from ar_system_parameters_all');
1546 -- end;
1547 --
1548 procedure Run_SQL_html(p_title varchar2, p_sql_statement varchar2) is
1549 dummy number;
1550 begin
1551 dummy := Display_SQL (p_sql_statement , p_title , 'Y','Y',null);
1552 end Run_SQL_html;
1553
1554 procedure Run_SQL_html(p_title varchar2
1555 , p_sql_statement varchar2
1556 , p_feedback varchar2) is
1557 dummy number;
1558 begin
1559 dummy := Display_SQL (p_sql_statement , p_title , 'Y', p_feedback, null);
1560 end Run_SQL_html;
1561
1562 procedure Run_SQL_html(p_title varchar2
1563 , p_sql_statement varchar2
1564 , p_max_rows number) is
1565 dummy number;
1566 begin
1567 dummy := Display_SQL (p_sql_statement , p_title , 'Y', 'Y', p_max_rows);
1568 end Run_SQL_html;
1569
1570 procedure Run_SQL_html(p_title varchar2
1571 , p_sql_statement varchar2
1572 , p_feedback varchar2
1573 , p_max_rows number) is
1574 dummy number;
1575 begin
1576 dummy := Display_SQL (p_sql_statement , p_title , 'Y',
1577 p_feedback, p_max_rows);
1578 end Run_SQL_html;
1579
1580 -- Procedure Name: Display_Table
1581 --
1582 -- Usage:
1583 -- Display_Table('Table Name', 'Heading', 'Where Clause', 'Order By', 'Long Flag');
1584 --
1585 -- Parameters:
1586 -- Table Name - Any Valid Table or View
1587 -- Heading - Text String to for heading the output
1588 -- Where Clause - Where clause to apply to the table dump
1589 -- Order By - Order By clause to apply to the table dump
1590 -- Long Flag - 'Y' or 'N' - If set to 'N' then this will not output any LONG columns
1591 --
1592 -- Output:
1593 -- Displays the output of the 'select * from table' as an HTML table.
1594 --
1595 -- Examples:
1596 -- begin
1597 -- Display_Table('AR_SYSTEM_PARAMETERS_ALL', 'AR Parameters', 'Where Org_id <> -3113'
1598 -- , 'order by org_id, set_of_books_id', 'N');
1599 -- end;
1600 --
1601 -- BVS-START <- Starting ignoring this section
1602
1603 procedure Display_Table_html (p_table_name varchar2,
1604 p_table_alias varchar2,
1605 p_where_clause varchar2,
1606 p_order_by_clause varchar2 default null,
1607 p_display_longs varchar2 default 'Y') is
1608 dummy number;
1609 hold_char varchar(1) := null;
1610 begin
1611 if p_where_clause is not null then
1612 hold_char := l_newline;
1613 end if;
1614 dummy := Display_SQL ('select * from ' ||
1615 replace(upper(p_table_name),'V_$','V$') || l_newline || p_where_clause ||
1616 hold_char || nvl(p_order_by_clause,'order by 1')
1617 , nvl(p_table_alias, p_table_name)
1618 , p_display_longs);
1619 end Display_Table_html;
1620
1621 -- BVS-STOP <- Stop ignoring this section (restart scanning)
1622
1623
1624 -- Function Name: Display_Table
1625 --
1626 -- Usage:
1627 -- a_number := Display_Table('Table Name', 'Heading', 'Where Clause', 'Order By', 'Long Flag');
1628 --
1629 -- Parameters:
1630 -- Table Name - Any Valid Table or View
1631 -- Heading - Text String to for heading the output
1632 -- Where Clause - Where clause to apply to the table dump
1633 -- Order By - Order By clause to apply to the table dump
1634 -- Long Flag - 'Y' or 'N' - If set to 'N' then this will not output any LONG columns
1635 --
1636 -- Output:
1637 -- Displays the output of the 'select * from table' as an HTML table.
1638 --
1639 -- Returns:
1640 -- Number of rows displayed.
1641 --
1642 -- Examples:
1643 -- declare
1644 -- num_rows number;
1645 -- begin
1646 -- num_rows := Display_Table('AR_SYSTEM_PARAMETERS_ALL', 'AR Parameters', 'Where Org_id <> -3113'
1647 -- , 'order by org_id, set_of_books_id', 'N');
1648 -- end;
1649 --
1650 function Display_Table_html (p_table_name varchar2,
1651 p_table_alias varchar2,
1652 p_where_clause varchar2,
1653 p_order_by_clause varchar2 default null,
1654 p_display_longs varchar2 default 'Y') return number is
1655 begin
1656 return(Display_SQL ('select * from ' ||
1657 replace(upper(p_table_name),'V_$','V$') || l_newline || p_where_clause ||
1658 l_newline || nvl(p_order_by_clause,'order by 1')
1659 , nvl(p_table_alias, p_table_name)
1660 , p_display_longs));
1661 end Display_Table_html;
1662
1663 -- Function Name: Get_DB_Apps_Version
1664 --
1665 -- Usage:
1666 -- a_varchar := Get_DB_Apps_Version;
1667 --
1668 -- Returns:
1669 -- The version of applications from fnd_product_groups
1670 -- Also sets the variable g_appl_version to '10.7','11.0', or '11.5'
1671 -- as appropriate.
1672 --
1673 -- Examples:
1674 -- declare
1675 -- apps_ver varchar2(20);
1676 -- begin
1677 -- apps_ver := Get_DB_Apps_Version;
1678 -- end;
1679 --
1680 function Get_DB_Apps_Version_html return varchar2 is
1681 l_appsver fnd_product_groups.release_name%type := null;
1682 begin
1683 select release_name into l_appsver from fnd_product_groups;
1684 g_appl_version := substr(l_appsver,1,4);
1685 return(l_appsver);
1686 end;
1687
1688 -- Procedure Name: Show_Header
1689 --
1690 -- Usage:
1691 -- Show_Header('Note Number', 'Title');
1692 --
1693 -- Parameters:
1694 -- Note Number - Any Valid Metalink Note Number
1695 -- Title - Text string to go beside the note link
1696 --
1697 -- Output:
1698 -- Displays Standard Header Information
1699 --
1700 -- Examples:
1701 -- begin
1702 -- Show_Header('139684.1', 'Oracle Applications Current Patchsets Comparison to applptch.txt');
1703 -- end;
1704 --
1705 procedure Show_Header_html(p_note varchar2, p_title varchar2) is
1706 l_instance_name varchar2(16) := null;
1707 l_host_name varchar2(64) := null;
1708 l_version varchar2(17) := null;
1709 begin
1710 DBMS_LOB.CREATETEMPORARY(g_hold_output,TRUE,DBMS_LOB.SESSION);
1711 select instance_name
1712 , host_name
1713 , version
1714 into l_instance_name
1715 , l_host_name
1716 , l_version
1717 from v$instance;
1718 Insert_Style_Sheet;
1719 line_out('<table class="report" cellspacing="0" summary="Header Table to start off Script"><tr class="report">');
1720 line_out('<th class="rowh" align="left" id="note" nowrap="nowrap">Note</th>');
1721 line_out('<td class="report" align="left" headers="note" nowrap="nowrap">');
1722 line_out('<a href="http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOT&p_id=' || p_note || '" target="new">' );
1723 line_out(p_note||'</a>');
1724 line_out('<a href="http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOT&p_id=' || p_note || '" target="new"></a>' );
1725 line_out(p_title || '</td>');
1726 line_out('</tr><tr class="report">');
1727 line_out('<th class="rowh" align="left" id="machine" nowrap="nowrap">Machine</th>');
1728 line_out('<td class="report" align="left" headers="machine" nowrap="nowrap">' || l_host_name || '</td>');
1729 line_out('</tr><tr class="report">');
1730 line_out('<th class="rowh" align="left" id="date" nowrap="nowrap">Date Run</th>');
1731 line_out('<td class="report" align="left" headers="date" nowrap="nowrap">' || to_char(sysdate,'MM-DD-YYYY HH24:MI') || '</td>');
1732 line_out('</tr><tr class="report">');
1733 line_out('<th class="rowh" align="left" id="info" nowrap="nowrap">Oracle Info</th>');
1734 line_out('<td class="report" align="left" headers="info" nowrap="nowrap">SID: ' || l_instance_name || ' Version: ' || l_version || '</td>');
1735 line_out('</tr><tr class="report">');
1736 line_out('<th class="rowh" align="left" id="appver" nowrap="nowrap">Apps Version</th>');
1737 line_out('<td class="report" align="left" headers="appver" nowrap="nowrap">' || Get_DB_Apps_Version || '</td>');
1738 line_out('</tr></table><br/>' );
1739 end Show_Header_html;
1740
1741 -- Procedure Name: Show_Footer
1742 --
1743 -- Usage:
1744 -- Show_Footer('Script Name','Header');
1745 --
1746 -- Output:
1747 -- Displays Standard Footer
1748 --
1749 -- Examples:
1750 -- begin
1751 -- Show_Footer('AR Setup Script', '$Header: jtfdiagcoreapi_b.pls 120.11.12000000.3 2007/04/05 09:33:32 rudas ship $';
1752 -- end;
1753 --
1754 procedure Show_Footer_html(p_script_name varchar2, p_header varchar2) is
1755 begin
1756 line_out('<br/><br/>Please provide ');
1757 line_out('<a href="mailto:[email protected]?Subject=Diagnostic Framework feedback for ' || p_script_name || ' - ' || p_header || '">');
1758 line_out('feedback</a> regarding the usefulness of this test and/or');
1759 line_out('<br/>tool.We appreciate your feedback, however, there will be no replies to');
1760 line_out('<br/>feedback emails. For support issues, please log an iTar (Service Request).');
1761 end Show_Footer_html;
1762
1763 -- Procedure Name: Show_Link
1764 --
1765 -- Usage:
1766 -- Show_Link('Note #');
1767 --
1768 -- Output:
1769 -- Displays A link to a Metalink Note
1770 --
1771 -- Examples:
1772 -- begin
1773 -- Show_Link('139684.1');
1774 -- end;
1775 --
1776 procedure Show_Link_html(p_note varchar2) is
1777 begin
1778 line_out('Click to see Note: <a href="http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOT&p_id=' || p_note || '">' || p_note || '</a>');
1779 end Show_Link_html;
1780
1781 -- Procedure Name: Show_Link
1782 --
1783 -- Usage:
1784 -- Show_Link('URL', 'Name');
1785 --
1786 -- Output:
1787 -- Displays A link to a URL using the Name Parameter
1788 --
1789 -- Examples:
1790 -- begin
1791 -- Show_Link('http://metalink.us.oracle.com', 'Metalink');
1792 -- end;
1793 --
1794 procedure Show_Link_html(p_link varchar2, p_link_name varchar2 ) is
1795 begin
1796 line_out('<a href="' || p_link || '">' || p_link_name || '</a>');
1797 end Show_Link_html;
1798
1799
1800 -- Procedure Name: Send_Email
1801 --
1802 -- Usage:
1803 -- Send_Email('Sender', 'Recipient', 'Subject', 'Message', 'SMTP Host');
1804 --
1805 -- Output:
1806 -- Sends E-mail - No screen output.
1807 --
1808 -- Examples:
1809 -- begin
1810 -- Send_Email ('[email protected]','[email protected]','this is a subject', 'this is a body','gmsmtp01.oraclecorp.com');
1811 -- end;
1812 --
1813 procedure Send_Email_html ( p_sender varchar2
1814 , p_recipient varchar2
1815 , p_subject varchar2
1816 , p_message varchar2
1817 , p_mailhost varchar2) is
1818
1819 l_mail_conn utl_smtp.connection;
1820 begin
1821 l_mail_conn := utl_smtp.open_connection(p_mailhost, 25);
1822 utl_smtp.helo(l_mail_conn, p_mailhost);
1823 utl_smtp.mail(l_mail_conn, p_sender);
1824 utl_smtp.rcpt(l_mail_conn, p_recipient);
1825 utl_smtp.open_data(l_mail_conn);
1826 if p_subject is not null then
1827 utl_smtp.write_data(l_mail_conn, 'Subject: ' || p_subject || utl_tcp.CRLF);
1828 end if;
1829 utl_smtp.write_data(l_mail_conn, utl_tcp.CRLF || p_mailhost);
1830 utl_smtp.close_data(l_mail_conn);
1831 utl_smtp.quit(l_mail_conn);
1832 exception
1833 when others then
1834 utl_smtp.quit(l_mail_conn);
1835 ErrorPrint('<br/>Error in Sending Mail' || sqlerrm);
1836 end Send_Email_html;
1837
1838 -- Function Name: Get_Package_Version
1839 --
1840 -- Usage:
1841 -- a_varchar := Get_Package_Version ('Object Type', 'Schema', 'Package Name');
1842 --
1843 -- Returns:
1844 -- The version of the package or spec
1845 --
1846 -- Examples:
1847 -- declare
1848 -- spec_ver varchar2(20);
1849 -- body_ver varchar2(20);
1850 -- begin
1851 -- spec_ver := Get_Package_Version('PACKAGE','APPS','ARH_ADDR_PKG');
1852 -- body_ver := Get_Package_Version('PACKAGE BODY','APPS','ARH_ADDR_PKG');
1853 -- end;
1854 --
1855 function Get_Package_Version_html (p_type varchar2, p_schema varchar2, p_package varchar2) return varchar2 is
1856 hold_version varchar2(50);
1857 begin
1858 select substr(z.text, instr(z.text,'$Header')+10, 40)
1859 into hold_version
1860 from all_source z
1861 where z.name = p_package
1862 and z.type = p_type
1863 and z.owner = p_schema
1864 and z.text like '%$Header%';
1865 hold_version := substr(hold_version, instr(hold_version,' ')+1, 50);
1866 hold_version := substr(hold_version, 1, instr(hold_version,' ')-1);
1867 return (hold_version);
1868 end Get_Package_Version_html;
1869
1870 -- Function Name: Get_Package_Spec
1871 --
1872 -- Usage:
1873 -- a_varchar := Get_Package_Spec('Package Name');
1874 --
1875 -- Returns:
1876 -- The version of the package specification in the APPS schema
1877 --
1878 -- Examples:
1879 -- declare
1880 -- spec_ver varchar2(20);
1881 -- begin
1882 -- spec_ver := Get_Package_Spec('ARH_ADDR_PKG');
1883 -- end;
1884 --
1885 function Get_Package_Spec_html(p_package varchar2) return varchar2 is
1886 begin
1887 return Get_Package_Version('PACKAGE','APPS',p_package);
1888 end Get_Package_Spec_html;
1889
1890 -- Function Name: Get_Package_Body
1891 --
1892 -- Usage:
1893 -- a_varchar := Get_Package_Body('Package Name');
1894 --
1895 -- Returns:
1896 -- The version of the package body in the APPS schema
1897 --
1898 -- Examples:
1899 -- declare
1900 -- body_ver varchar2(20);
1901 -- begin
1902 -- body_ver := Get_Package_Body('ARH_ADDR_PKG');
1903 -- end;
1904 --
1905 function Get_Package_Body_html(p_package varchar2) return varchar2 is
1906 begin
1907 return Get_Package_Version('PACKAGE BODY','APPS',p_package);
1908 end Get_Package_Body_html;
1909
1910 -- BVS-START <- Starting ignoring this section
1911 -- Procedure Name: Display_Profiles
1912 --
1913 -- Usage:
1914 -- Display_Profiles(application id, 'profile short name');
1915 --
1916 -- Output:
1917 -- Displays all Profile settings for the application or profile
1918 -- in an HTML table
1919 --
1920 -- Examples:
1921 -- begin
1922 -- Display_Profiles(222,null);
1923 -- Display_Profiles(null, 'AR_ALLOW_OVERAPPLICATION_IN_LOCKBOX');
1924 -- end;
1925 --
1926 procedure Display_Profiles_html (p_application_id varchar2
1927 , p_short_name varchar2 default null) is
1928 begin
1929 Run_SQL('Profile Options',
1930 'select b.USER_PROFILE_OPTION_NAME "Long<br/>Name"'
1931 || ' , a.profile_option_name "Short<br/>Name"'
1932 || ' , decode(to_char(c.level_id),''10001'',''Site'''
1933 || ' ,''10002'',''Application'''
1934 || ' ,''10003'',''Responsibility'''
1935 || ' ,''10004'',''User'''
1936 || ' ,''Unknown'') "Level"'
1937 || ' , decode(to_char(c.level_id),''10001'',''Site'''
1938 || ' ,''10002'',nvl(h.application_short_name,to_char(c.level_value))'
1939 || ' ,''10003'',nvl(g.responsibility_name,to_char(c.level_value))'
1940 || ' ,''10004'',nvl(e.user_name,to_char(c.level_value))'
1941 || ' ,''Unknown'') "Level<br/>Value"'
1942 || ' , c.PROFILE_OPTION_VALUE "Profile<br/>Value"'
1943 || ' , c.profile_option_id "Profile<br/>ID"'
1944 || ' , to_char(c.LAST_UPDATE_DATE,''MM-DD-YYYY HH24:MI'') '
1945 || ' "Updated<br/>Date"'
1946 || ' , nvl(d.user_name,to_char(c.last_updated_by)) "Updated<br/>By"'
1947 || ' from fnd_profile_options a'
1948 || ' , FND_PROFILE_OPTIONS_TL b'
1949 || ' , FND_PROFILE_OPTION_VALUES c'
1950 || ' , FND_USER d'
1951 || ' , FND_USER e'
1952 || ' , FND_RESPONSIBILITY_TL g'
1953 || ' , FND_APPLICATION h'
1954 || ' where a.application_id = nvl(' || nvl(p_application_id,'null')
1955 || ' , a.application_id)'
1956 || ' and a.profile_option_name = nvl(''' || p_short_name
1957 || ''' , a.profile_option_name)'
1958 || ' and a.profile_option_name = b.profile_option_name'
1959 || ' and a.profile_option_id = c.profile_option_id'
1960 || ' and a.application_id = c.application_id'
1961 || ' and c.last_updated_by = d.user_id (+)'
1962 || ' and c.level_value = e.user_id (+)'
1963 || ' and c.level_value = g.responsibility_id (+)'
1964 || ' and c.level_value = h.application_id (+)'
1965 || ' and b.language = ''US'''
1966 || ' order by 1, 4, 5');
1967 end;
1968 -- BVS-STOP <- Stop ignoring this section (restart scanning)
1969
1970
1971 -- Procedure Name: Get_Profile_Option
1972 --
1973 -- Usage:
1974 -- a_varchar := Get_Profile_Option('Short Name');
1975 --
1976 -- Parameters:
1977 -- Short Name - The Short Name of the Profile Option
1978 --
1979 -- Returns:
1980 -- The value of the profile option based on the user.
1981 -- If Set_Client has not been run successfully then
1982 -- it will return the site level.
1983 --
1984 -- Output:
1985 -- None
1986 --
1987 -- Examples:
1988 -- declare
1989 -- prof_value varchar2(150);
1990 -- begin
1991 -- prof_value := Get_Profile_Option('AR_ALLOW_OVERAPPLICATION_IN_LOCKBOX')
1992 -- end;
1993 --
1994 function Get_Profile_Option_html (p_profile_option varchar2) return varchar2 is
1995 begin
1996 return FND_PROFILE.VALUE(p_profile_option);
1997 end;
1998
1999 -- Procedure Name: Set_Org
2000 --
2001 -- Usage:
2002 -- Set_Org(org_id);
2003 --
2004 -- Parameters:
2005 -- Org_ID - The id of the organization to set.
2006 --
2007 -- Output:
2008 -- None
2009 --
2010 -- Examples:
2011 -- begin
2012 -- Set_Org(204);
2013 -- end;
2014 --
2015 procedure Set_Org_html (p_org_id number) is
2016 begin
2017 fnd_client_info.set_org_context(p_org_id);
2018 end Set_Org_html;
2019
2020 -- Procedure Name: Set_Client
2021 --
2022 -- Description:
2023 -- Validates user_name, responsibility_id, and application_id parameters
2024 -- If valid it initializes the session (which results in the operating
2025 -- unit being set for the session as well. Also sets the global variables
2026 -- g_user_id, g_resp_id, g_appl_id, and g_org_id which can then be used
2027 -- throughout the script.
2028 --
2029 -- Usage:
2030 -- Set_Client(UserName, Responsibility_ID);
2031 -- Set_Client(UserName, Responsibility_ID, Application_ID);
2032 -- Set_Client(UserName, Responsibility_ID, Application_ID, SecurityGrp_ID);
2033 --
2034 -- Parameters:
2035 -- UserName - The Name of the Applications User
2036 -- Responsibility_ID - Any Valid Responsibility ID
2037 -- Application_ID - Any Valid Application ID (275=PA) If no value
2038 -- provided, attempt to obtain from responsibility_id
2039 -- SecurityGrp_ID - A valid security_group_id
2040 --
2041 -- Examples:
2042 -- begin
2043 -- Set_Client('JOEUSER',50719, 222);
2044 -- end;
2045 -- BVS-START <- Starting ignoring this section
2046 procedure Set_Client_html(p_user_name varchar2, p_resp_id number,
2047 p_app_id number, p_sec_grp_id number) is
2048 l_cursor integer;
2049 l_num_rows integer;
2050 l_user_name fnd_user.user_name%type;
2051 l_user_id number;
2052 l_app_id number;
2053 l_counter integer;
2054 l_appl_vers fnd_product_groups.release_name%type;
2055 sqltxt varchar2(2000);
2056 inv_user exception;
2057 inv_resp exception;
2058 inv_app exception;
2059 no_app exception;
2060 begin
2061 l_user_name := upper(p_user_name);
2062 begin
2063 select user_id into l_user_id
2064 from fnd_user where user_name = l_user_name;
2065 exception
2066 when others then
2067 raise inv_user;
2068 end;
2069 l_appl_vers := get_db_apps_version; -- sets g_appl_version
2070 if g_appl_version = '11.0' or g_appl_version = '10.7' then
2071 sqltxt := 'select rg.application_id '||
2072 'from fnd_user_responsibility rg '||
2073 'where rg.responsibility_id = '||to_char(p_resp_id)||' '||
2074 'and rg.user_id = '||to_char(l_user_id);
2075 elsif g_appl_version = '11.5' then
2076 sqltxt := 'select rg.responsibility_application_id '||
2077 'from fnd_user_resp_groups rg '||
2078 'where rg.responsibility_id = '||to_char(p_resp_id)||' '||
2079 'and rg.user_id = '||to_char(l_user_id);
2080 end if;
2081 begin
2082 l_cursor := dbms_sql.open_cursor;
2083 dbms_sql.parse(l_cursor, sqltxt, dbms_sql.native);
2084 dbms_sql.define_column(l_cursor, 1, l_app_id);
2085 l_num_rows := dbms_sql.execute_and_fetch(l_cursor, TRUE);
2086 dbms_sql.column_value(l_cursor, 1, l_app_id);
2087 dbms_sql.close_cursor(l_cursor);
2088
2089 exception
2090 when no_data_found then
2091 raise inv_resp;
2092 when too_many_rows then
2093 if p_app_id is null then
2094 raise no_app;
2095 else
2096 dbms_sql.close_cursor(l_cursor);
2097 l_cursor := dbms_sql.open_cursor;
2098 dbms_sql.parse(l_cursor, sqltxt, dbms_sql.native);
2099 dbms_sql.define_column(l_cursor, 1, l_app_id);
2100 l_num_rows := dbms_sql.execute(l_cursor);
2101 while dbms_sql.fetch_rows(l_cursor) > 0 loop
2102 dbms_sql.column_value(l_cursor, 1, l_app_id);
2103 if l_app_id = p_app_id then
2104 exit;
2105 end if;
2106 end loop;
2107 dbms_sql.close_cursor(l_cursor);
2108 if l_app_id <> p_app_id then
2109 raise inv_app;
2110 end if;
2111 end if;
2112 end;
2113 l_cursor := dbms_sql.open_cursor;
2114 if g_appl_version = '11.5' then
2115 sqltxt := 'begin '||
2116 'fnd_global.apps_initialize(:user, :resp, '||
2117 ':appl, :secg); '||
2118 'end; ';
2119 dbms_sql.parse(l_cursor, sqltxt, dbms_sql.native);
2120 dbms_sql.bind_variable(l_cursor,':user',l_user_id);
2121 dbms_sql.bind_variable(l_cursor,':resp',p_resp_id);
2122 dbms_sql.bind_variable(l_cursor,':appl',l_app_id);
2123 dbms_sql.bind_variable(l_cursor,':secg',p_sec_grp_id);
2124 else
2125 sqltxt := 'begin '||
2126 'fnd_global.apps_initialize(:user,:resp,:appl); '||
2127 'end; ';
2128 dbms_sql.parse(l_cursor, sqltxt, dbms_sql.native);
2129 dbms_sql.bind_variable(l_cursor,':user',l_user_id);
2130 dbms_sql.bind_variable(l_cursor,':resp',p_resp_id);
2131 dbms_sql.bind_variable(l_cursor,':appl',l_app_id);
2132 end if;
2133 l_num_rows := dbms_sql.execute(l_cursor);
2134 g_user_id := l_user_id;
2135 g_resp_id := p_resp_id;
2136 g_appl_id := l_app_id;
2137 g_org_id := Get_Profile_Option('ORG_ID');
2138 exception
2139 when inv_user then
2140 ErrorPrint('Unable to initialize client due to invalid username: '||
2141 l_user_name);
2142 ActionErrorPrint('Set_Client has been passed an invalid username '||
2143 'parameter. Please correct this parameter if possible, and if not, '||
2144 'inform your support representative.');
2145 raise;
2146 when inv_resp then
2147 ErrorPrint('Unable to initialize client due to invalid responsibility '||
2148 'ID: '||to_char(p_resp_id));
2149 ActionErrorPrint('Set_Client has been passed an invalid responsibility '||
2150 'ID parameter. This responsibility_id either does not exist or has not '||
2151 'been assigned to the user ('||l_user_name||'). Please correct these '||
2152 'parameter values if possible, and if not inform your support '||
2153 'representative.');
2154 raise;
2155 when inv_app then
2156 ErrorPrint('Unable to initialize client due to invalid application ID: '||
2157 to_char(p_app_id));
2158 ActionErrorPrint('Set_Client has been passed an invalid application ID '||
2159 'parameter. This application either does not exist or is not '||
2160 'associated with the responsibility id ('||to_char(p_resp_id)||'). '||
2161 'Please correct this parameter value if possible, and if not inform '||
2162 'your support representative.');
2163 raise;
2164 when no_app then
2165 ErrorPrint('Set_Client was unable to obtain an application ID to '||
2166 'initialize client settings');
2167 ActionErrorPrint('No application_id was supplied and Set_Client was '||
2168 'unable to determine this from the responsibility because multiple '||
2169 'responsibilities with the same responsibility_id have been assigned '||
2170 'to this user ('||l_user_name||').');
2171 raise;
2172 when others then
2173 ErrorPrint(sqlerrm||' occured in Set_Client');
2174 ActionErrorPrint('Please inform your support representative');
2175 raise;
2176 end Set_Client_html;
2177 -- BVS-STOP <- Stop ignoring this section (restart scanning)
2178
2179 procedure Set_Client_html(p_user_name varchar2, p_resp_id number) is
2180 begin
2181 Set_Client(p_user_name, p_resp_id, null, null);
2182 end Set_Client_html;
2183
2184 procedure Set_Client_html(p_user_name varchar2, p_resp_id number,
2185 p_app_id number ) is
2186 begin
2187 Set_Client(p_user_name, p_resp_id, p_app_id, null);
2188 end Set_Client_html;
2189
2190 /*
2191 -- OBSOLETED API SINCE THIS IS NOT COMPLIANT WITH GSCC STANDARD
2192 -- FILE.SQL.6 WHICH DOES NOT PERMIT USING OF SCHEMA
2193 -- NAMES WITHIN PLSQL CODE
2194 --
2195 -- Procedure Name: Get_DB_Patch_List
2196 --
2197 -- Usage:
2198 -- a_string := Get_DB_Patch_List('heading', 'short name', 'bug number', 'start date');
2199 --
2200 -- Parameters:
2201 -- Heading = Title to go at the top of TABLE or TEXT outputs
2202 -- Short Name = Limits to Bugs that match this expression for the Applications Production Short Name (LIKE)
2203 -- Bug Number = Limits to bugs that match this expression (LIKE)
2204 -- Start Date = Limits to Bugs created after this date
2205 --
2206 -- Output:
2207 -- An HTML table of patches applied for the application since the date
2208 -- indicated is displayed.
2209 --
2210 -- Examples:
2211 -- begin
2212 -- Get_DB_Patch_List(null, 'AD','%', '03-MAR-2002', 'SILENT');
2213 -- end;
2214 --
2215
2216 -- BVS-START <- Starting ignoring this section
2217 procedure Get_DB_Patch_List_html (p_heading varchar2 default 'AD_BUGS'
2218 , p_app_short_name varchar2 default '%'
2219 , p_bug_number varchar2 default '%'
2220 , p_start_date date default to_date(olddate,'MM-DD-YYYY')
2221 , p_output_option varchar2 default 'TABLE') is
2222 l_cursor integer;
2223 l_sqltxt varchar2(5000);
2224 l_list_out varchar2(32767);
2225 l_hold_comma varchar2(2);
2226 l_counter integer;
2227 l_app_short_name varchar2(50);
2228 l_bug_number varchar2(30);
2229 l_creation_date date;
2230
2231 begin
2232
2233 select count(*) into l_counter
2234 from all_tables z
2235 where z.table_name = 'AD_BUGS'
2236 and upper(z.owner) = 'APPLSYS';
2237
2238 if l_counter > 0 then
2239 l_sqltxt := 'select application_short_name'
2240 || ' , bug_number'
2241 || ' , creation_date'
2242 || ' from ad_bugs'
2243 || ' where upper(application_short_name) like '''
2244 || upper(p_app_short_name)
2245 || ''' and creation_date >= '''
2246 || nvl(to_char(p_start_date,'MM-DD-YYYY'),olddate)
2247 || ''' and bug_number like '''||p_bug_number||'''';
2248 Run_SQL(p_heading, l_sqltxt);
2249 else
2250 WarningPrint('Table AD_BUGS does not exist');
2251 ActionWarningPrint('Unable to retrieve a patch list from the database as this feature is not available on this version of the applications');
2252 end if;
2253 end Get_DB_Patch_List_html;
2254 -- BVS-STOP <- Stop ignoring this section (restart scanning)
2255 */
2256
2257 -- Function Name: Get_RDBMS_Header
2258 --
2259 -- Usage:
2260 -- Get_RDBMS_Header;
2261 --
2262 -- Returns:
2263 -- Version of the Database from v$version
2264 --
2265 -- Examples:
2266 -- declare
2267 -- RDBMS_Ver := v$version.banner%type;
2268 -- begin
2269 -- RDBMS_Ver := Get_RDBMS_Header;
2270 -- end;
2271 --
2272 Function Get_RDBMS_Header_html return varchar2 is
2273 l_hold_name v$database.name%type;
2274 l_DB_Ver v$version.banner%type;
2275 begin
2276 begin
2277 select name
2278 into l_hold_name
2279 from v$database;
2280 exception
2281 when others then
2282 l_hold_name := 'Not Available';
2283 end;
2284 begin
2285 select banner
2286 into l_DB_Ver
2287 from v$version
2288 where banner like 'Oracle%';
2289 exception
2290 when others then
2291 l_DB_Ver := 'Not Available';
2292 end;
2293 return(l_hold_name || ' - ' || l_DB_Ver);
2294 end Get_RDBMS_Header_html;
2295
2296 /*
2297 -- OBSOLETED API SINCE THIS IS NOT COMPLIANT WITH GSCC STANDARD
2298 -- FILE.SQL.6 WHICH DOES NOT PERMIT USING OF SCHEMA
2299 -- NAMES WITHIN PLSQL CODE
2300 --
2301 -- Procedure Name: Show_Invalids
2302 --
2303 -- Usage:
2304 -- Show_Invalids('start string', 'include errors', 'heading');
2305 --
2306 -- Parameters:
2307 -- start string - An string indicating the beginning of object names to
2308 -- be included. The underscore '_' character will be
2309 -- escaped in this string so that it does not act as a
2310 -- wild card character. For example, 'PA_' will not match
2311 -- 'PAY' even though it normally would in SQL*Plus.
2312 -- include errors - Y or N to indicate whether to search on and report
2313 -- the errors from ALL_ERRORS for each of the invalid
2314 -- objects found. (DEFAULT = N)
2315 -- heading - An optional heading for the table. If null the heading will
2316 -- be "Invalid Objects (Starting with 'XXX')" where XXX is
2317 -- the start string parameter.
2318 --
2319 -- Output:
2320 -- A listing of invalid objects whose name starts with the 'start string'.
2321 -- For packages, procedures, and functions, file versions will be included,
2322 -- and when requested, error messages associated with the object will
2323 -- be reported.
2324 --
2325 -- Examples:
2326 -- Show_Invalids('PA_','Y');
2327 -- Show_Invalids('GL_');
2328 --
2329 Procedure Show_Invalids_html (p_start_string varchar2
2330 , p_include_errors varchar2 default 'N'
2331 , p_heading varchar2 default null) is
2332 l_start_string varchar2(60);
2333 l_errors varchar2(32767);
2334 l_file_version varchar2(100);
2335 l_heading varchar2(500);
2336 l_first_row boolean := true;
2337 l_table_row V2T;
2338 l_row_options V2T;
2339
2340
2341 -- OWNER CHANGE
2342 cursor get_invalids(c_start_string varchar2) is
2343 select o.object_name, o.object_type, o.owner
2344 from all_objects o
2345 where o.status = 'INVALID'
2346 and o.object_name like c_start_string escape '~'
2347 and upper(o.owner) in ('APPS', 'JTF', 'APPLSYS')
2348 order by o.object_name;
2349
2350 cursor get_file_version(
2351 c_obj_name varchar2
2352 , c_obj_type varchar2
2353 , c_obj_owner varchar2) is
2354 select substr(substr(s.text,instr(s.text,'$Header')+9),1,
2355 instr(substr(s.text,instr(s.text,'$Header')+9),' ',1,2)-1) file_vers
2356 from all_source s
2357 where s.name = c_obj_name
2358 and s.type = c_obj_type
2359 and s.owner = c_obj_owner
2360 and s.text like '%$Header%';
2361
2362 cursor get_errors (
2363 c_obj_name varchar2
2364 , c_obj_type varchar2
2365 , c_obj_owner varchar2) is
2366 select to_char(sequence)||') LINE: '||to_char(line)||' CHR: '||
2367 to_char(position)||' '||text error_row
2368 from all_errors z
2369 where z.name = c_obj_name
2370 and z.type = c_obj_type
2371 and z.owner = c_obj_owner;
2372
2373 begin
2374 l_start_string := upper(replace(p_start_string,'_','~_')) || '%';
2375 if p_heading is null then
2376 l_heading := 'Invalid Objects (Starting with '''||p_start_string||''')';
2377 else
2378 l_heading := p_heading;
2379 end if;
2380 line_out('<br/><span class="BigPrint">' || l_heading || '</span>');
2381 for inv_rec in get_invalids(l_start_string) loop
2382 if l_first_row then
2383 Start_Table('Invalid Objects');
2384 if p_include_errors = 'Y' then
2385 Show_Table_Header(V2T('Object Name','Object Type', 'Owner',
2386 'File Version', 'Errors'));
2387 else
2388 Show_Table_Header(V2T('Object Name', 'Object Type', 'Owner',
2389 'File Version'));
2390 end if;
2391 l_first_row := false;
2392 end if;
2393
2394 if inv_rec.object_type like 'PACKAGE%' or
2395 inv_rec.object_type in ('PROCEDURE','FUNCTION') then
2396 open get_file_version(inv_rec.object_name, inv_rec.object_type,
2397 inv_rec.owner);
2398 fetch get_file_version into l_file_version;
2399 if get_file_version%notfound then
2400 l_file_version := null;
2401 end if;
2402 close get_file_version;
2403 else
2404 l_file_version := null;
2405 end if;
2406
2407 if p_include_errors = 'Y' then
2408 for err_rec in get_errors(inv_rec.object_name, inv_rec.object_type,
2409 inv_rec.owner) loop
2410 l_errors := l_errors||err_rec.error_row||'<br/>';
2411 end loop;
2412 l_table_row := V2T(inv_rec.object_name, inv_rec.object_type,
2413 inv_rec.owner, l_file_version, l_errors);
2414 l_row_options := V2T(null,'nowrap',null,'nowrap','nowrap');
2415 Show_Table_Row(l_table_row,l_row_options);
2416 else
2417 l_table_row := V2T(inv_rec.object_name, inv_rec.object_type,
2418 inv_rec.owner, l_file_version);
2419 Show_Table_Row(l_table_row);
2420 end if;
2421 end loop;
2422 End_Table;
2423 if l_first_row then
2424 Insert_HTML('<br/><span class="SmallPrint">No Rows Selected</span><br/>');
2425 end if;
2426 exception when others then
2427 ErrorPrint(sqlerrm||' occured in Show_Invalids');
2428 ActionErrorPrint('Use the feedback link to report the above error to '||
2429 'support');
2430 end Show_Invalids_html;
2431 */
2432
2433 ---------------------------------------------------------------
2434 -- Text Output APIs
2435 ---------------------------------------------------------------
2436
2437
2438 -- Procedure Name: Line_Out
2439 -- Description:
2440 -- Outputs plain text - same as Tab0Print
2441 -- Usage:
2442 -- Line_Out('String');
2443 -- Parameters:
2444 -- String - Any text string
2445 -- Output:
2446 -- Writes the text to the reportClob object
2447 -- Examples:
2448 -- begin
2449 -- Line_Out('Run Gather Schema Statistics');
2450 -- end;
2451
2452 procedure line_out_text(text varchar2) is
2453 begin
2454 --dbms_lob.write(JTF_DIAGNOSTIC_ADAPTUTIL.reportClob, length(text)+1, g_curr_loc, text || l_newline);
2455 --JTF_DIAGNOSTIC_ADAPTUTIL.addStringToReport(text || l_newline);
2456 JTF_DIAGNOSTIC_ADAPTUTIL.addStringToReport(text||FND_GLOBAL.Local_Chr(10));
2457 g_curr_loc := g_curr_loc + length(text)+1;
2458 end line_out_text;
2459
2460 -- Procedure Name: ActionPrint
2461 -- Usage:
2462 -- ActionPrint('String');
2463 -- Parameters:
2464 -- String - Any text string
2465 -- Output:
2466 -- Displays the text string with no indention preceded by 'ACTION - '
2467 -- Examples:
2468 -- begin
2469 -- ActionPrint('Run Gather Schema Statistics');
2470 -- end;
2471
2472 procedure ActionPrint_text(p_text varchar2) is
2473 begin
2474 line_out('ACTION - '||p_text);
2475 end ActionPrint_text;
2476
2477 -- Procedure Name: ActionErrorPrint
2478 -- Usage:
2479 -- ActionErrorPrint('String');
2480 -- Parameters:
2481 -- String - Any text string
2482 -- Output:
2483 -- Displays the text string with the word ACTION - prior to the string
2484 -- Same as ActionPrint
2485 -- Examples:
2486 -- begin
2487 -- ActionErrorPrint('Run Gather Schema Statistics');
2488 -- end;
2489
2490 procedure ActionErrorPrint_text(p_text varchar2) is
2491 begin
2492 ActionPrint(p_text);
2493 end ActionErrorPrint_text;
2494
2495 -- Procedure Name: ActionWarningPrint
2496 -- Usage:
2497 -- ActionWarningPrint('String');
2498 -- Parameters:
2499 -- String - Any text string
2500 -- Output:
2501 -- Displays the text string in warning format
2502 -- Same as ActionPrint
2503 -- Examples:
2504 -- begin
2505 -- ActionWarningPrint('Run Gather Schema Statistics');
2506 -- end;
2507
2508 procedure ActionWarningPrint_text(p_text varchar2) is
2509 begin
2510 ActionPrint(p_text);
2511 end ActionWarningPrint_text;
2512
2513 -- Procedure Name: WarningPrint
2514 -- Usage:
2515 -- WarningPrint('String');
2516 -- Parameters:
2517 -- String - Any text string
2518 -- Output:
2519 -- Displays the text string with no indentation preceded by 'WARNING - '
2520 -- Examples:
2521 -- begin
2522 -- WarningPrint('Statistics are not up to date');
2523 -- end;
2524
2525 procedure WarningPrint_text(p_text varchar2) is
2526 begin
2527 line_out('WARNING - ' ||p_text);
2528 end WarningPrint_text;
2529
2530 -- Procedure Name: ErrorPrint
2531 -- Usage:
2532 -- ErrorPrint('String');
2533 -- Parameters:
2534 -- String - Any text string
2535 -- Output:
2536 -- Displays the text string with no indentation preceded by 'ERROR - '
2537 -- Examples:
2538 -- begin
2539 -- ErrorPrint('Statistics have not been run');
2540 -- end;
2541
2542 procedure ErrorPrint_text(p_text varchar2) is
2543 begin
2544 line_out('ERROR - '||p_text);
2545 end ErrorPrint_text;
2546
2547 -- Procedure Name: SectionPrint
2548 -- Usage:
2549 -- SectionPrint('String');
2550 -- Parameters:
2551 -- String - Any text string
2552 -- Output:
2553 -- Displays the text underlined with two preceding carriage returns
2554 -- Examples:
2555 -- begin
2556 -- SectionPrint('Checking OE Parameters');
2557 -- end;
2558
2559 procedure SectionPrint_text (p_text varchar2) is
2560 ultxt varchar2(1000) := '-';
2561 begin
2562 line_out(l_newline||l_newline||p_text);
2563 ultxt := rpad(ultxt, length(p_text),'-');
2564 line_out(ultxt);
2565 end SectionPrint_text;
2566
2567 -- Procedure Name: BRPrint
2568 -- Usage:
2569 -- BRPrint;
2570 -- Output:
2571 -- Inserts a blank line.
2572 -- Examples:
2573 -- begin
2574 -- BRPrint;
2575 -- end;
2576 procedure BRPrint_text is
2577 begin
2578 line_out(l_tab);
2579 end BRPrint_text;
2580
2581
2582
2583 -- Function Name: Column_Exists
2584 -- Usage:
2585 -- Column_Exists('Table Name','Column Name');
2586 -- Paramteters:
2587 -- Table Name - Table in which to check for the column
2588 -- Column Name - Column to check
2589 -- Returns:
2590 -- 'Y' if the column exists in the table, 'N' if not.
2591 -- Examples:
2592 -- declare
2593 -- sqltxt varchar2(1000);
2594 -- begin
2595 -- if Column_Exists('PA_IMPLEMENTATIONS_ALL','UTIL_SUM_FLAG') = 'Y' then ;
2596 -- sqltxt := sqltxt||' and i.util_sum_flag is not null';
2597 -- end if;
2598 -- end;
2599
2600 function Column_Exists_text(p_tab in varchar, p_col in varchar, p_owner in varchar) return varchar2 is
2601 l_counter integer:=0;
2602
2603 begin
2604 -- UNSURE!! SHOULD WE SEEK OWNER AS PARAMETER
2605 -- DECIDED TO DO SO
2606
2607 select count(*) into l_counter
2608 from all_tab_columns z
2609 where z.table_name = upper(p_tab)
2610 and z.column_name = upper(p_col)
2611 and upper(z.owner) = upper(p_owner);
2612
2613 if l_counter > 0 then
2614 return('Y');
2615 else
2616 return('N');
2617 end if;
2618 exception when others then
2619 ErrorPrint(sqlerrm||' occured in Column_Exists');
2620 ActionErrorPrint('Report this information to your support analyst');
2621 raise;
2622 end Column_Exists_text;
2623
2624 -- Procedure Name: Tab0Print
2625 -- Usage:
2626 -- Tab0Print('String');
2627 -- Parameters:
2628 -- String - Any text string
2629 -- Output:
2630 -- Displays the text string unindented. (Same as Line_Out())
2631 -- Examples:
2632 -- begin
2633 -- Tab0Print('Layer 0');
2634 -- end;
2635
2636 procedure Tab0Print_text (p_text varchar2) is
2637 begin
2638 line_out(p_text);
2639 end Tab0Print_text;
2640
2641 -- Procedure Name: Tab1Print
2642 -- Usage:
2643 -- Tab1Print('String');
2644 -- Parameters:
2645 -- String - Any text string
2646 -- Output:
2647 -- Displays the text string indented by 1 tab character
2648 -- Examples:
2649 -- begin
2650 -- Tab1Print('Layer 1');
2651 -- end;
2652
2653 procedure Tab1Print_text (p_text varchar2) is
2654 begin
2655 line_out(l_tab||p_text);
2656 end Tab1Print_text;
2657
2658 -- Procedure Name: Tab2Print
2659 -- Usage:
2660 -- Tab2Print('String');
2661 -- Parameters:
2662 -- String - Any text string
2663 -- Output:
2664 -- Displays the text string indented two tab characters
2665 -- Examples:
2666 -- begin
2667 -- Tab2Print('Layer 2');
2668 -- end;
2669
2670 procedure Tab2Print_text (p_text varchar2) is
2671 begin
2672 line_out(l_tab||l_tab||p_text);
2673 end Tab2Print_text;
2674
2675 -- Procedure Name: Tab3Print
2676 -- Usage:
2677 -- Tab3Print('String');
2678 -- Parameters:
2679 -- String - Any text string
2680 -- Output:
2681 -- Displays the text string indented 3 tab characters
2682 -- Examples:
2683 -- begin
2684 -- Tab3Print('Layer 3');
2685 -- end;
2686
2687 procedure Tab3Print_text (p_text varchar2) is
2688 begin
2689 line_out(l_tab||l_tab||l_tab||p_text);
2690 end Tab3Print_text;
2691
2692 -- Procedure Name: CheckFinPeriod
2693 -- Usage:
2694 -- CheckFinPeriod('Set of Books ID','Application ID');
2695 -- Paramteters:
2696 -- Set of Books ID - ID for the set of books
2697 -- Application ID - ID of the application whose periods are being checked
2698 -- Output:
2699 -- List the number of defined and open periods. Indicate the latest
2700 -- open period. Produce warnings if no periods are open or if the
2701 -- current date is not in an open period.
2702 -- Examples:
2703 -- CheckFinPeriod(62, 222); -- Check open periods for AR SOB 62
2704 -- CheckFinPeriod(202, 201); -- Check open periods for PO SOB 202
2705 -- BVS-START <- Starting ignoring this section
2706
2707 procedure checkFinPeriod_text (p_sobid NUMBER, p_appid NUMBER ) IS
2708 l_appname VARCHAR2(50) :=NULL;
2709 l_period_name VARCHAR2(50);
2710 l_user_period_type VARCHAR2(50);
2711 l_start_date DATE;
2712 l_end_date DATE;
2713 l_sysdate DATE;
2714 l_sysopen VARCHAR2(1);
2715
2716 CURSOR C1 IS
2717 select a.name sobname,
2718 count(b.period_name) total_periods,
2719 count(decode(b.closing_status,'O',b.PERIOD_NAME,null)) open_periods,
2720 a.accounted_period_type period_type
2721 from gl_sets_of_books a,
2722 gl_period_statuses b
2723 where a.set_of_books_id = b.set_of_books_id (+)
2724 and b.application_id = p_appId
2725 and a.set_of_books_id = p_sobId
2726 and b.period_type = a.accounted_period_type
2727 group by a.name, a.accounted_period_type;
2728
2729 c1_rec c1%rowtype;
2730 no_rows exception;
2731
2732 BEGIN
2733
2734 select application_name
2735 into l_appname
2736 from fnd_application_vl
2737 where application_id = p_appid ;
2738
2739 open c1;
2740 fetch c1 into c1_rec;
2741 IF c1%notfound THEN
2742 raise no_rows;
2743 END IF;
2744 select user_period_type into l_user_period_type
2745 from gl_period_types
2746 where period_type = c1_rec.period_type;
2747 Tab0Print('Application = '||l_appname);
2748 Tab0Print('Set of books = '|| c1_rec.sobname ||'(ID='||to_char(p_sobid)||')');
2749 Tab0Print('Period type = '||l_user_period_type);
2750 Tab1Print('Periods Defined = ' || to_char(c1_rec.total_periods));
2751 Tab1Print('Periods Open = ' || to_char(c1_rec.open_periods));
2752 IF c1_rec.total_periods = 0 THEN
2753 WarningPrint('There are no periods defined for this Set of books');
2754 ActionWarningPrint('There must be periods defined for this set of books');
2755 END IF;
2756 IF c1_rec.open_periods = 0 THEN
2757 WarningPrint('There are no open periods defined for this Set of books');
2758 ActionWArningprint('Please consider opening a period for this '||
2759 'application and set of books');
2760 ELSE
2761 BEGIN
2762 SELECT period_name, start_date, end_date, sysdate
2763 INTO l_period_name, l_start_date, l_end_date, l_sysdate
2764 FROM gl_period_statuses
2765 WHERE adjustment_period_flag = 'N'
2766 AND period_type = c1_rec.period_type
2767 AND start_date = (
2768 SELECT MAX(start_date)
2769 FROM gl_period_statuses
2770 WHERE closing_status = 'O'
2771 AND adjustment_period_flag = 'N'
2772 AND period_type = c1_rec.period_type
2773 AND application_id = p_appId
2774 AND set_of_books_id = p_sobId )
2775 AND closing_status = 'O'
2776 AND application_id = p_appId
2777 AND set_of_books_id = p_sobId;
2778
2779 /* check if sysdate is in the latest open period */
2780
2781 l_sysopen := 'N';
2782 IF l_sysdate >= l_start_date AND l_sysdate <= l_end_date THEN
2783 l_sysOpen := 'Y';
2784 END IF;
2785 Tab0Print('Latest open period is '|| l_period_name);
2786 Tab1Print('Start date = '|| to_char(l_start_date)||' End date = '
2787 || to_char(l_end_date));
2788 IF l_sysopen = 'Y' THEN
2789 Tab0Print('Current date '|| to_char(l_sysdate)
2790 || ' is in the latest open period');
2791 ELSE
2792 BEGIN
2793 SELECT period_name, start_date, end_date, sysdate
2794 INTO l_period_name, l_start_date, l_end_date, l_sysdate
2795 FROM gl_period_statuses
2796 WHERE adjustment_period_flag = 'N'
2797 AND period_type = c1_rec.period_type
2798 AND sysdate between start_date and end_date
2799 AND closing_status = 'O'
2800 AND application_id = p_appId
2801 AND set_of_books_id = p_sobId;
2802
2803 Tab0Print('Current date '|| to_char(sysDate)
2804 || ' is in the open period ' || l_period_name);
2805 Tab1Print('Start date = ' || to_char(l_start_date)||' End date = '
2806 || to_char(l_end_date));
2807
2808 EXCEPTION WHEN NO_DATA_FOUND THEN
2809 WarningPrint('Current date '|| to_char(l_sysdate)
2810 || ' is not in an open period');
2811 ActionwarningPrint('Please consider opening the current period');
2812 END;
2813 END IF;
2814 END;
2815 END IF;
2816 EXCEPTION
2817 WHEN NO_ROWS THEN
2818 WarningPrint('There are no accounting periods defined in '||
2819 'gl_period_statuses');
2820 ActionWArningprint('If required, define the accounting calendar for this '||
2821 'application and set of books');
2822 WHEN NO_DATA_FOUND THEN
2823 ErrorPrint('Invalid Application id passed to checkFinPeriod');
2824 ActionErrorPrint('Application id ' || to_char(p_appid)
2825 || ' is not valid on this system');
2826 WHEN OTHERS THEN
2827 ErrorPrint(sqlerrm||' occurred in CheckFinPeriod');
2828 ActionErrorPrint('Report this error to your support representative');
2829 END checkFinPeriod_text;
2830 -- BVS-STOP <- Stop ignoring this section (restart scanning)
2831
2832
2833 -- Function Name: CheckKeyFlexfield
2834 -- Procedure Name: CheckKeyFlexfield
2835 --
2836 -- Usage:
2837 -- CheckKeyFlexfield('Key Flex Code','Flex Structure ID','Print Header');
2838 --
2839 -- Parameters:
2840 -- Key Flex Code - The code of the Key Flexfield to be displayed. For
2841 -- example, for the Accounting Flexfield use 'GL#'.
2842 -- Flex Structure ID - The id_flex_num of the specific structure
2843 -- of the key flexfield whose details are to be
2844 -- displayed. If null, print details of all
2845 -- structures. (default NULL)
2846 -- Print Header - A boolean (true or false) indicating whether the output
2847 -- should print a heading before outputting the details
2848 -- of the key flexfield. (default TRUE)
2849 -- Returns:
2850 -- If value has been provided for the Flex Structure ID, the function
2851 -- will returns an array of character strings with the following structure
2852 -- 1 name of the flexfield
2853 -- 2 enabled flag
2854 -- 3 frozen flag
2855 -- 4 dynamic instert flag
2856 -- 5 cross validation allowed flag
2857 -- 6 number of enabled segments defined
2858 -- 7 number of enabled segments with value sets
2859 -- 8 Y if any segment has security otherwise N
2860 -- If no value is passed to the parameter the function will return an
2861 -- array will null values.:w
2862 --
2863 -- Output:
2864 -- Displays key information about the flexfield, its structure, and the
2865 -- individual flexfield segments that make it up.
2866 --
2867 -- Examples:
2868 -- declare
2869 -- flexarray V2T;
2870 -- begin
2871 -- CheckKeyFlexfield('GL#', 50577, true);
2872 -- CheckKeyFlexfield('MSTK', null, false);
2873 -- flexarray := CheckKeyFlexfield('GL#', 12345, false);
2874 -- end;
2875 --
2876 -- BVS-START <- Starting ignoring this section
2877
2878 Function CheckKeyFlexfield_text(p_flex_code in varchar2
2879 , p_flex_num in number default null
2880 , p_print_heading in boolean default true)
2881 return V2T is
2882
2883 l_ret_array V2T := V2T(null,null,null,null,null,null,null,null);
2884 l_no_value_sets integer := 0;
2885 l_any_sec_enabled varchar2(1) := 'N';
2886 l_sec_enabled varchar2(1) := 'N';
2887 l_flex_name fnd_id_flexs.id_flex_name%type;
2888 l_counter integer := 0;
2889 l_counter2 integer := 0;
2890 l_num_segs integer := 0;
2891 l_num_segs_vs integer := 0;
2892 l_rule_count integer := 0;
2893 l_rule_assign_count integer := 0;
2894 l_value_set_str varchar2(400);
2895 leave_api exception;
2896
2897 cursor get_structs (p_f_code varchar2, p_f_num number) is
2898 select id_flex_num flex_str_num,
2899 id_flex_structure_name flex_str_name,
2900 to_char(last_update_date,'MM-DD-YYYY HH24:MI:SS') last_updated,
2901 cross_segment_validation_flag cross_val,
2902 dynamic_inserts_allowed_flag dyn_insert,
2903 enabled_flag enabled,
2904 freeze_flex_definition_flag frozen
2905 from fnd_id_flex_structures_vl
2906 where id_flex_code = p_f_code
2907 and enabled_flag ='Y'
2908 and id_flex_num = nvl(p_f_num,id_flex_num);
2909
2910 cursor get_segments (p_f_code varchar2, p_f_num number) is
2911 select s.application_column_name col_name,
2912 s.segment_name seg_name,
2913 s.segment_num seg_num,
2914 s.enabled_flag enabled,
2915 s.required_flag required,
2916 s.display_flag displayed,
2917 s.flex_value_set_id value_set_id,
2918 vs.flex_value_set_name value_set_name,
2919 DECODE(vs.validation_type,
2920 'I', 'Independent', 'N', 'None', 'D', 'Dependent',
2921 'U', 'Special', 'P', 'Pair', 'F', 'Table',
2922 'X', 'Translatable Independent', 'Y', 'Translatable Dependent',
2923 vs.validation_type) validation_type,
2924 s.security_enabled_flag seg_security,
2925 nvl(vs.security_enabled_flag,'N') value_set_security
2926 from fnd_id_flex_segments_vl s, fnd_flex_value_sets vs
2927 where s.flex_value_set_id = vs.flex_value_set_id (+)
2928 and s.id_flex_code = p_f_code
2929 and s.id_flex_num = p_f_num
2930 order by s.segment_num ;
2931
2932 cursor get_qualifiers(p_f_code varchar2, p_f_num number, p_col_name varchar2) is
2933 select segment_prompt
2934 from fnd_segment_attribute_values sav,
2935 fnd_segment_attribute_types sat
2936 where sav.attribute_value = 'Y'
2937 and sav.segment_attribute_type <> 'GL_GLOBAL'
2938 and sav.application_id = sat.application_id
2939 and sav.id_flex_code = sat.id_flex_code
2940 and sav.segment_attribute_type = sat.segment_attribute_type
2941 and sav.id_flex_code = p_f_code
2942 and sav.id_flex_num = p_f_num
2943 and sav.application_column_name = p_col_name;
2944
2945 begin
2946 begin
2947 select id_flex_name into l_flex_name
2948 from fnd_id_flexs
2949 where id_flex_code = p_flex_code;
2950 exception when no_data_found then
2951 WarningPrint('ID Flex Code passed '||p_flex_code||' is not valid on this '||
2952 'system');
2953 ActionWarningPrint('ID Flex Code '||p_flex_code||' will not be tested');
2954 end;
2955
2956 BRPrint;
2957 if p_flex_num is null then
2958 if (p_print_heading) then
2959 SectionPrint('Details of Key flexfield: '||l_flex_name);
2960 else
2961 Tab0Print('Key flexfield: '||l_flex_name);
2962 end if;
2963 else
2964 l_ret_array(1) := l_flex_name;
2965 if (p_print_heading) then
2966 SectionPrint('Details of Key flexfield: '||l_flex_name
2967 ||' with id_flex_num '||to_char(p_flex_num));
2968 else
2969 Tab0Print('Key flexfield: '||l_flex_name||' with id_flex_num '
2970 || to_char(p_flex_num));
2971 end if;
2972 end if;
2973
2974 l_counter := 0;
2975 for str in get_structs(p_flex_code, p_flex_num) loop
2976 l_counter := l_counter + 1;
2977 if p_flex_num is not null then
2978 l_ret_array(2) := str.enabled;
2979 l_ret_array(3) := str.frozen;
2980 l_ret_array(4) := str.dyn_insert;
2981 l_ret_array(5) := str.cross_val;
2982 end if;
2983 BRPrint;
2984 Tab0Print('Structure '||str.flex_str_name||' (ID='||
2985 to_char(str.flex_str_num) ||')');
2986 Tab1Print('Enabled Flag = '||str.enabled);
2987 Tab1Print('Frozen = '||str.frozen);
2988 Tab1Print('Dynamic Inserts = '||str.dyn_insert);
2989 Tab1Print('Cross Validation Allowed = '||str.cross_val);
2990 Tab1Print('Last Updated Date = '||str.last_updated);
2991
2992
2993 l_counter2 := 0;
2994 l_num_segs := 0;
2995 l_num_segs_vs := 0;
2996 for seg in get_segments(p_flex_code, str.flex_str_num) loop
2997 if l_counter2 = 0 then
2998 BRPrint;
2999 Tab0Print('Segment Details for '||str.flex_str_name);
3000 end if;
3001 l_counter2 := l_counter2 + 1;
3002
3003 if (p_flex_num is not null) then
3004 if seg.enabled = 'Y' then
3005 l_num_segs := l_num_segs + 1;
3006 if (seg.value_set_id is not null) then
3007 l_num_segs_vs := l_num_segs_vs + 1;
3008 end if;
3009 end if;
3010 end if;
3011 if (seg.seg_security = 'Y' and seg.value_set_security in ('Y','H')) then
3012 l_any_sec_enabled := 'Y';
3013 l_sec_enabled := 'Y';
3014 end if;
3015
3016 if (seg.value_set_id is not null) then
3017 l_value_set_str := ', Value Set = '||seg.value_set_name||
3018 ', Value Set Type = '||seg.validation_type;
3019 else
3020 l_value_set_str := ' with no value set assigned';
3021 end if;
3022
3023 Tab1Print('Segment Name = '||seg.seg_name);
3024 Tab2Print('Enabled = '||seg.enabled);
3025 Tab2Print('Displayed = '||seg.displayed);
3026 if seg.value_set_id is not null then
3027 Tab2Print('Value Set = '||seg.value_set_name);
3028 Tab2Print('Value Set Type = '||seg.validation_type);
3029 else
3030 Tab2Print('Value Set = None assigned');
3031 end if;
3032
3033 for qual in get_qualifiers(p_flex_code,str.flex_str_num,seg.col_name) loop
3034 Tab2Print('Qualifier '||qual.segment_prompt||' is assigned');
3035 end loop;
3036
3037 if l_sec_enabled = 'Y' then
3038 select count(*) into l_rule_count
3039 from fnd_flex_value_rules_vl
3040 where flex_value_set_id = seg.value_set_id;
3041
3042 select count(*) into l_rule_assign_count
3043 from fnd_flex_value_rules_vl r,
3044 fnd_flex_value_rule_usages ru
3045 where r.flex_value_rule_id = ru.flex_value_rule_id
3046 and r.flex_value_set_id = seg.value_set_id;
3047
3048 Tab2Print('Security is enabled for this segment and value set');
3049 Tab3Print(to_char(l_rule_count)||' rules are defined');
3050 Tab3Print(to_char(l_rule_assign_count)||' rule assignments exist');
3051 end if;
3052 end loop;
3053 if (p_flex_num is not null) then
3054 l_ret_array(6) := to_char(l_num_segs);
3055 l_ret_array(7) := to_char(l_num_segs_vs);
3056 l_ret_array(8) := l_any_sec_enabled;
3057 end if;
3058 if l_counter2 = 0 then
3059 ErrorPrint('There are no segments defined for this structure');
3060 ActionErrorPrint('Please enable or define at least one segment for '||
3061 str.flex_str_name);
3062 end if;
3063 end loop;
3064 if l_counter = 0 then
3065 if p_flex_num is null then
3066 ErrorPrint('There are no Key Flexfields enabled for ' || p_flex_code);
3067 ActionErrorPrint('Please enable or define a Key Flexfield for ' ||
3068 p_flex_code);
3069 else
3070 ErrorPrint('The requested flexfield structure (ID_FLEX_NUM='||
3071 to_char(p_flex_num)||') is inactive or does not exist');
3072 ActionErrorPrint('Verify that the flexfield structure is defined '||
3073 'and enabled for Key Flexfield '||p_flex_code);
3074 end if;
3075 end if;
3076 return l_ret_array;
3077 exception
3078 when leave_api then
3079 return l_ret_array;
3080 end;
3081
3082 procedure CheckKeyFlexfield_text(p_flex_code in varchar2
3083 , p_flex_num in number default null
3084 , p_print_heading in boolean default true) is
3085 dummy_v2t V2T;
3086 begin
3087 dummy_v2t := CheckKeyFlexfield(p_flex_code, p_flex_num, p_print_heading);
3088 end CheckKeyFlexfield_text;
3089
3090 -- Function Name: CheckProfile
3091 -- Procedure Name: CheckProfile
3092 -- Usage:
3093 -- CheckProfile('Profile Name', UserID, ResponsibilityID,
3094 -- ApplicationID, 'Default Value', Indent Level);
3095 -- Parameters:
3096 -- Profile Name - System name of the profile option being checked
3097 -- UserID - The identifier of that applications user for which the
3098 -- profile option is to be checked.
3099 -- ResponsibilityID - The identifier of the responsibility for which
3100 -- the profile option is to be checked
3101 -- ApplicationID - The identifier of the application for which the profile
3102 -- option is to be checked
3103 -- Default Value - The value that will be used as a default if the profile
3104 -- option is not set by the users (Default=NULL)
3105 -- Indent Level - Number of tabs (0,1,2,3) that output should be indented
3106 -- (Default=0)
3107 -- Returns:
3108 -- If called as a function the return value will be either:
3109 -- 1 the value of the profile option if set
3110 -- 2 'DOESNOTEXIST' if the profile option does not exist
3111 -- 3 'DISABLED' if the profile option has been end dated
3112 -- 4 null if the profile option is not set
3113 -- Output:
3114 -- If the profile is set, displays its current setting. If not set and
3115 -- a default value exists, displays a warning indicating that the default
3116 -- value will be used and indicating the value of the default. If not set
3117 -- and no default value is supplied, displays an error indicating that
3118 -- the profile option should be set. Output will be indented according
3119 -- to the Indent Level parameter supplied.
3120 --
3121 -- If the profile option does not exist or is disabled there is no
3122 -- output.
3123 -- Examples:
3124 -- declare
3125 -- profile_val fnd_profile_option_values.profile_option_value%type;
3126 -- begin
3127 -- profile_val := CheckProfile('PA_SELECTIVE_FLEX_SEG',g_user_id,
3128 -- g_resp_id, g_appl_id, null, 1);
3129 --
3130 -- CheckProfile('PA_DEBUG_MODE',g_user_id, g_resp_id, g_appl_id);
3131 -- CheckProfile('PA_DEBUG_MODE',g_user_id, g_resp_id, g_appl_id,'Y',2);
3132 -- end;
3133
3134 function CheckProfile_text(p_prof_name in varchar2
3135 , p_user_id in number
3136 , p_resp_id in number
3137 , p_appl_id in number
3138 , p_default in varchar2 default null
3139 , p_indent in integer default 0)
3140 return varchar2 is
3141 l_user_prof_name fnd_profile_options_tl.user_profile_option_name%type;
3142 l_prof_value fnd_profile_option_values.profile_option_value%type;
3143 l_start_date date;
3144 l_end_date date;
3145 l_opt_defined boolean;
3146 l_output_txt varchar2(500);
3147 begin
3148 begin
3149 select user_profile_option_name,
3150 nvl(start_date_active,sysdate-1),
3151 nvl(end_date_active,sysdate+1)
3152 into l_user_prof_name, l_start_date, l_end_date
3153 from fnd_profile_options_vl
3154 where profile_option_name = p_prof_name;
3155 exception
3156 when no_data_found then
3157 l_prof_value := 'DOESNOTEXIST';
3158 return(l_prof_value);
3159 when others then
3160 ErrorPrint(sqlerrm||' occured while getting profile option '||
3161 'information');
3162 ActionErrorPrint('Report the above information to your support '||
3163 'representative');
3164 return(null);
3165 end;
3166 if ((sysdate < l_start_date) or (sysdate > l_end_date)) then
3167 l_prof_value := 'DISABLED';
3168 return(l_prof_value);
3169 end if;
3170 fnd_profile.get_specific(p_prof_name, p_user_id, p_resp_id, p_appl_id,
3171 l_prof_value, l_opt_defined);
3172 if not l_opt_defined then
3173 l_prof_value := null;
3174 end if;
3175 if l_prof_value is null then
3176 if p_default is null then
3177 ErrorPrint(l_user_prof_name || ' profile option is not set');
3178 ActionErrorPrint('Please set the profile option according to '||
3179 'the user manual');
3180 return(l_prof_value);
3181 else
3182 WarningPrint(l_user_prof_name || ' profile option is not set '||
3183 'and will default to ' || p_default);
3184 ActionWarningPrint('Please set the profile option according to '||
3185 'the user manual if you do not want to use the default');
3186 return(l_prof_value);
3187 end if;
3188 else
3189 l_output_txt := l_user_prof_name || ' profile option is set to -- ' ||
3190 l_prof_value;
3191 if p_indent = 1 then
3192 Tab1Print(l_output_txt);
3193 elsif p_indent = 2 then
3194 Tab2Print(l_output_txt);
3195 elsif p_indent = 3 then
3196 Tab3Print(l_output_txt);
3197 else
3198 Tab0Print(l_output_txt);
3199 end if;
3200 return(l_prof_value);
3201 end if;
3202 exception when others then
3203 ErrorPrint(sqlerrm||' occured in CheckProfile');
3204 ActionErrorPrint('Please report this error to your support representative');
3205 end CheckProfile_text;
3206
3207 procedure CheckProfile_text(p_prof_name in varchar2
3208 , p_user_id in number
3209 , p_resp_id in number
3210 , p_appl_id in number
3211 , p_default in varchar2 default null
3212 , p_indent in integer default 0) is
3213 l_dummy_prof_value fnd_profile_option_values.profile_option_value%type;
3214 begin
3215 l_dummy_prof_value := CheckProfile(p_prof_name, p_user_id, p_resp_id,
3216 p_appl_id, p_default, p_indent);
3217 end CheckProfile_text;
3218
3219 -- Procedure Name: Show_Table_Header
3220 -- Description:
3221 -- Private procedure used by Display_SQL to display the headers
3222 -- Usage: N/A
3223 -- Examples: N/A
3224
3225 procedure Show_Table_Header_text(p_headers in headers,
3226 p_lengths in out NOCOPY lengths) is
3227 hdr_str varchar2(5000);
3228 ul_str varchar2(5000);
3229 begin
3230 p_lengths := p_lengths;
3231 for i in 1..p_headers.count loop
3232 if p_lengths(i) is null or p_lengths(i) < length(p_headers(i)) then
3233 p_lengths(i) := length(p_headers(i));
3234 end if;
3235 if i = 1 then
3236 hdr_str := l_return||rpad(p_headers(i),p_lengths(i),' ');
3237 ul_str := rpad('-',p_lengths(i),'-');
3238 else
3239 hdr_str := hdr_str||' '||rpad(p_headers(i),p_lengths(i),' ');
3240 ul_str := ul_str||' '||rpad('-',p_lengths(i),'-');
3241 end if;
3242 end loop;
3243 line_out(hdr_str);
3244 line_out(ul_str);
3245 end Show_Table_Header_text;
3246
3247 -- Function Name: Display_SQL
3248 -- Procedure Name: Display_SQL
3249 -- Usage:
3250 -- Function:
3251 -- a_number := Display_SQL('SQL statement', 'disp_lengths_tbl',
3252 -- 'headers_tbl', 'feedback', 'max rows');
3253 -- Procedure:
3254 -- Display_SQL('SQL statement', 'disp_lengths_tbl',
3255 -- 'headers_tbl', 'feedback', 'max rows');
3256 -- Parameters:
3257 -- SQL Statement - Any valid SQL select statement text which selects only
3258 -- columns of type Number, Date, or Varchar2.
3259 -- disp_lengths_tbl - a table of type 'lengths' indicating the display
3260 -- length for each of the columns in the select.
3261 -- A value must be supplied for each column. If the
3262 -- value is null, the length of the header will be used.
3263 -- headers_tbl - a table of type 'headers' indicating the column heading
3264 -- for each of the columns in the select. If an individual
3265 -- element of this parameter is null, or if this parameter
3266 -- is not provided (it is not required) the heading will be
3267 -- 1) the column alias
3268 -- 2) the column name
3269 -- feedback - Y or N to indicate whether a count of rows should automaticall
3270 -- be printed at the end of the output. (Default = Y)
3271 -- max rows - Maximum number of rows to output. NULL or ZERO indicates
3272 -- unlimited. (Default = NULL)
3273 -- Returns:
3274 -- The function returns the number of rows selected.
3275 -- If there is an error then the function returns null.
3276 -- Output:
3277 -- Displays the output of the SQL statement as text.
3278 -- Examples:
3279 -- declare
3280 -- num_rows number;
3281 -- sqltxt varchar2(2000);
3282 -- disp_lengths lengths;
3283 -- col_headers headers;
3284 -- begin
3285 -- sqltxt := 'Select segment1, project_type, project_id '||
3286 -- 'from pa_projects_all'
3287 -- disp_lengths := lengths(20,15,8);
3288 -- col_headers := headers('Project Number', 'Project Type', null);
3289 --
3290 -- num_rows := Display_SQL(sqltxt, disp_lengths);
3291 -- /* or */
3292 -- num_rows := Display_SQL(sqltxt, disp_lengths, col_headers);
3293 -- /* or */
3294 -- num_rows := Display_SQL(sqltxt, disp_lengths, col_headers,'N');
3295 -- tab0Print(to_char(num_rows)||' rows selected');
3296 -- /* or */
3297 -- num_rows := Display_SQL(sqltxt, disp_lengths, col_headers,'N',5);
3298 -- tab0Print(to_char(num_rows)||' rows selected');
3299 -- /* or */
3300 -- num_rows := Display_SQL(sqltxt, disp_lengths,null,'Y',5);
3301 -- /* or */
3302 -- Display_SQL(sqltxt, disp_lengths);
3303 -- /* or */
3304 -- Display_SQL(sqltxt, disp_lengths, col_headers);
3305 -- /* or */
3306 -- Display_SQL(sqltxt, disp_lengths, col_headers,'N');
3307 -- /* or */
3308 -- Display_SQL(sqltxt, disp_lengths, col_headers,'N',5);
3309 -- end;
3310
3311 function Display_SQL_text (
3312 p_sql_statement varchar2
3313 , p_disp_lengths lengths
3314 , p_headers headers default null
3315 , p_feedback varchar2 default 'Y'
3316 , p_max_rows number default null)
3317 return number is
3318 error_position number;
3319 error_position_end number;
3320 i binary_integer := 1;
3321 l_row_counter number;
3322 l_row_str varchar2(32767) := null;
3323 l_column_high binary_integer := 1;
3324 l_cursor number;
3325 l_cols dbms_sql.desc_tab;
3326 l_number_value number;
3327 l_date_value date;
3328 l_varchar_value varchar2(32767);
3329 l_dummy integer;
3330 l_headers headers;
3331 l_disp_lengths lengths;
3332 l_max_rows number;
3333
3334 T_VARCHAR2 constant integer := 1;
3335 T_NUMBER constant integer := 2;
3336 T_LONG constant integer := 8;
3337 T_DATE constant integer := 12;
3338 T_RAW constant integer := 23;
3339 T_CHAR constant integer := 96;
3340 T_TYPE constant integer := 109;
3341 T_CLOB constant integer := 112;
3342 T_BLOB constant integer := 113;
3343 T_BFILE constant integer := 114;
3344
3345 param_error exception;
3346
3347 begin
3348 if p_max_rows = 0 then
3349 l_max_rows := null;
3350 else
3351 l_max_rows := p_max_rows;
3352 end if;
3353 l_headers := p_headers;
3354 l_disp_lengths := p_disp_lengths;
3355 l_cursor := DBMS_SQL.OPEN_CURSOR;
3356 begin
3357 DBMS_SQL.PARSE(l_cursor, p_sql_statement, DBMS_SQL.V7);
3358 exception when others then
3359 ErrorPrint('Unable to parse the statement passed to Display_SQL: '||
3360 sqlerrm);
3361 ActionErrorPrint('Review the SQL statement below for errors and provide '||
3362 'the information to your support representative:'||l_newline||
3363 p_sql_statement);
3364 raise param_error;
3365 end;
3366
3367 DBMS_SQL.DESCRIBE_COLUMNS(l_cursor, l_column_high, l_cols);
3368 if l_column_high <> l_disp_lengths.count then
3369 ErrorPrint('The column length is not specified for the correct number '||
3370 'of columns in call to Display_SQL');
3371 ActionErrorPrint('You must spefify the display length for each and every '||
3372 'column of the SQL select statement');
3373 raise param_error;
3374 end if;
3375 if l_headers is null then
3376 l_headers := headers();
3377 end if;
3378 if l_headers.count <> 0 and l_headers.count <> l_disp_lengths.count then
3379 ErrorPrint('Incorrect number of headers passed to Display_SQL');
3380 ActionErrorPrint('Either no headers must be passed, or a header value '||
3381 '(which can be null) must be passed for every column of the select');
3382 raise param_error;
3383 end if;
3384 for i in 1..l_column_high loop
3385 if l_cols(i).col_type not in (T_VARCHAR2,T_NUMBER,T_DATE) then
3386 ErrorPrint('Invalid column datatype');
3387 ActionErrorPrint('The Display_SQL api does not support queries on '||
3388 'columns of type '||l_cols(i).col_type);
3389 raise param_error;
3390 end if;
3391 if l_cols(i).col_type = T_NUMBER then
3392 DBMS_SQL.DEFINE_COLUMN(l_cursor, i, l_number_value);
3393 elsif l_cols(i).col_type = T_DATE then
3394 DBMS_SQL.DEFINE_COLUMN(l_cursor, i, l_date_value);
3395 l_disp_lengths(i) := greatest(l_disp_lengths(i),11);
3396 elsif l_cols(i).col_type = T_VARCHAR2 then
3397 DBMS_SQL.DEFINE_COLUMN(l_cursor, i, l_varchar_value,
3398 l_cols(i).col_max_len);
3399 else
3400 null;
3401 end if;
3402 if l_headers.count < i then -- no header supplied
3403 while l_headers.count < i loop
3404 l_headers.extend;
3405 end loop;
3406 l_headers(i) := l_cols(i).col_name;
3407 elsif l_headers(i) is null then -- header supplied is null
3408 l_headers(i) := l_cols(i).col_name;
3409 end if;
3410 if l_cols(i).col_type = T_NUMBER then
3411 l_disp_lengths(i) := nvl(l_disp_lengths(i),length(l_headers(i)));
3412 l_headers(i) := lpad(l_headers(i),l_disp_lengths(i),' ');
3413 end if;
3414 end loop;
3415 l_dummy := dbms_sql.execute(l_cursor);
3416 l_row_counter := 0;
3417 while dbms_sql.fetch_rows(l_cursor) <> 0 loop
3418 if l_row_counter = 0 then
3419 Show_Table_Header(l_headers, l_disp_lengths);
3420 end if;
3421 l_row_counter := l_row_counter + 1;
3422 if l_row_counter > nvl(l_max_rows,l_row_counter) then
3423 l_row_counter := l_row_counter - 1;
3424 exit;
3425 end if;
3426 for i in 1..l_column_high loop
3427 if l_cols(i).col_type = T_NUMBER then
3428 dbms_sql.column_value(l_cursor, i, l_number_value);
3429 if length(to_char(l_number_value)) > l_disp_lengths(i) then
3430 l_varchar_value := rpad('*',l_disp_lengths(i),'*');
3431 else
3432 if i = 1 then
3433 if l_number_value is null then
3434 l_varchar_value := rpad(l_return,l_disp_lengths(i)+1,' ');
3435 else
3436 l_varchar_value := l_return||lpad(to_char(l_number_value),
3437 l_disp_lengths(i),' ');
3438 end if;
3439 else
3440 l_varchar_value := lpad(nvl(to_char(l_number_value),' '),
3441 l_disp_lengths(i),' ');
3442 end if;
3443 end if;
3444 elsif l_cols(i).col_type = T_DATE then
3445 dbms_sql.column_value(l_cursor, i, l_date_value);
3446 if i = 1 and l_date_value is null then
3447 l_varchar_value := rpad(l_return,l_disp_lengths(i)+1, ' ');
3448 else
3449 l_varchar_value:=rpad(nvl(to_char(l_date_value,'MM-DD-YYYY'),' '),
3450 l_disp_lengths(i), ' ');
3451 end if;
3452 elsif l_cols(i).col_type = T_VARCHAR2 then
3453 dbms_sql.column_value(l_cursor, i, l_varchar_value);
3454 if i = 1 and l_varchar_value is null then
3455 l_varchar_value := rpad(l_return,l_disp_lengths(i)+1,' ');
3456 else
3457 l_varchar_value := rpad(substr(nvl(l_varchar_value,' '),1,
3458 l_disp_lengths(i)),l_disp_lengths(i), ' ');
3459 end if;
3460 end if;
3461 if i = 1 then
3462 l_row_str := l_varchar_value;
3463 else
3464 l_row_str := l_row_str||' '||l_varchar_value;
3465 end if;
3466 end loop;
3467 l_row_str := rtrim(l_row_str);
3468 line_out(l_row_str);
3469 end loop;
3470 if p_feedback = 'Y' then
3471 BRPrint;
3472 line_out(to_char(l_row_counter)||' rows selected');
3473 BRPrint;
3474 end if;
3475 return(l_row_counter);
3476 exception
3477 when param_error then
3478 return(null);
3479 when others then
3480 ErrorPrint(sqlerrm||' occured in Display_SQL');
3481 ActionErrorPrint('Report this information to your support representative.');
3482 return(sqlcode);
3483 end Display_SQL_text;
3484
3485
3486 procedure Display_SQL_text (
3487 p_sql_statement varchar2
3488 , p_disp_lengths lengths) is
3489 l_dummy number;
3490 begin
3491 l_dummy := Display_SQL(p_sql_statement, p_disp_lengths);
3492 end Display_SQL_text;
3493
3494 procedure Display_SQL_text (
3495 p_sql_statement varchar2
3496 , p_disp_lengths lengths
3497 , p_headers headers) is
3498 l_dummy number;
3499 begin
3500 l_dummy := Display_SQL(p_sql_statement, p_disp_lengths, p_headers);
3501 end Display_SQL_text;
3502
3503 procedure Display_SQL_text (
3504 p_sql_statement varchar2
3505 , p_disp_lengths lengths
3506 , p_headers headers
3507 , p_feedback varchar2) is
3508 l_dummy number;
3509 begin
3510 l_dummy := Display_SQL(p_sql_statement, p_disp_lengths, p_headers,
3511 p_feedback);
3512 end Display_SQL_text;
3513
3514 procedure Display_SQL_text (
3515 p_sql_statement varchar2
3516 , p_disp_lengths lengths
3517 , p_headers headers
3518 , p_feedback varchar2
3519 , p_max_rows number) is
3520 l_dummy number;
3521 begin
3522 l_dummy := Display_SQL(p_sql_statement, p_disp_lengths, p_headers,
3523 p_feedback, p_max_rows);
3524 end Display_SQL_text;
3525
3526 -- Function Name: Run_SQL
3527 -- Procedure Name: Run_SQL
3528 -- Usage:
3529 -- Function:
3530 -- a_number := Run_SQL('Header', 'SQL statement','disp_lengths_tbl',
3531 -- 'col_headers_tbl');
3532 -- a_number := Run_SQL('Header', 'SQL statement','disp_lengths_tbl',
3533 -- 'col_headers_tbl','feedback');
3534 -- a_number := Run_SQL('Header', 'SQL statement','disp_lengths_tbl',
3535 -- 'col_headers_tbl','max rows');
3536 -- a_number := Run_SQL('Header', 'SQL statement','disp_lengths_tbl',
3537 -- 'col_headers_tbl','feedback', 'max rows');
3538 -- Procedure:
3539 -- Run_SQL('Header', 'SQL statement', 'disp_lengths_tbl',
3540 -- 'col_headers_tbl');
3541 -- Run_SQL('Header', 'SQL statement', 'disp_lengths_tbl',
3542 -- 'col_headers_tbl','feedback');
3543 -- Run_SQL('Header', 'SQL statement', 'disp_lengths_tbl',
3544 -- 'col_headers_tbl','max rows');
3545 -- Run_SQL('Header', 'SQL statement', 'disp_lengths_tbl',
3546 -- 'col_headers_tbl','feedback','max rows');
3547 -- Parameters:
3548 -- Header - Text String to for heading the output
3549 -- SQL Statement - Any valid SQL Select Statement
3550 -- disp_lengths_tbl - a table of type 'lengths' indicating the display
3551 -- length for each of the columns in the select.
3552 -- A value must be supplied for each column even if
3553 -- that value is null. If the value is null,
3554 -- the length of the header will be used.
3555 -- headers_tbl - a table of type 'headers' indicating the column heading
3556 -- for each of the columns in the select. If an individual
3557 -- element of this parameter is null, or if this parameter
3558 -- is not provided (it is not required) the heading will be
3559 -- 1) the column alias
3560 -- 2) the column name
3561 -- feedback - Y or N to indicate whether a count of rows should automaticall
3562 -- be printed at the end of the output. (Default = Y)
3563 -- max rows - Maximum number of rows to output. NULL or ZERO indicates
3564 -- unlimited. (Default = NULL)
3565 -- Returns:
3566 -- The function returns the number of rows selected.
3567 -- If there is an error then the function returns null.
3568 -- Output:
3569 -- Displays the output of the SQL statement as text. The only difference
3570 -- between this and display SQL is that this will print a Title or
3571 -- heading statement prior to the actual sql output.
3572 -- Examples:
3573 -- declare
3574 -- num_rows number;
3575 -- sqltxt varchar2(2000);
3576 -- disp_lengths lengths;
3577 -- col_headers headers;
3578 -- begin
3579 -- sqltxt := 'Select segment1, project_type, project_id '||
3580 -- 'from pa_projects_all'
3581 -- disp_lengths := lengths(20,15,8);
3582 -- col_headers := headers('Project Number', 'Project Type', null);
3583 --
3584 -- num_rows := Run_SQL('All Projects', sqltxt, disp_lengths, col_headers);
3585 -- tab0Print(to_char(num_rows)||' rows selected');
3586 -- /* or */
3587 -- num_rows := Run_SQL('All Projects', sqltxt, disp_lengths);
3588 -- /* or */
3589 -- Run_SQL('All Projects', sqltxt, disp_lengths, col_headers);
3590 -- /* or */
3591 -- Run_SQL('All Projects', sqltxt, disp_lengths);
3592 -- end;
3593
3594 function Run_SQL_text(p_title varchar2,
3595 p_sql_statement varchar2,
3596 p_disp_lengths lengths)
3597 return number is
3598 begin
3599 SectionPrint(p_title);
3600 BRPrint;
3601 return(Display_SQL(p_sql_statement , p_disp_lengths));
3602 end Run_SQL_text;
3603
3604 function Run_SQL_text(p_title varchar2,
3605 p_sql_statement varchar2,
3606 p_disp_lengths lengths,
3607 p_headers headers)
3608 return number is
3609 begin
3610 SectionPrint(p_title);
3611 BRPrint;
3612 return(Display_SQL(p_sql_statement , p_disp_lengths, p_headers));
3613 end Run_SQL_text;
3614
3615 function Run_SQL_text(p_title varchar2,
3616 p_sql_statement varchar2,
3617 p_disp_lengths lengths,
3618 p_headers headers,
3619 p_feedback varchar2)
3620 return number is
3621 begin
3622 SectionPrint(p_title);
3623 BRPrint;
3624 return(Display_SQL(p_sql_statement, p_disp_lengths, p_headers, p_feedback));
3625 end Run_SQL_text;
3626
3627 function Run_SQL_text(p_title varchar2,
3628 p_sql_statement varchar2,
3629 p_disp_lengths lengths,
3630 p_headers headers,
3631 p_max_rows number)
3632 return number is
3633 begin
3634 SectionPrint(p_title);
3635 BRPrint;
3636 return(Display_SQL(p_sql_statement , p_disp_lengths, p_headers,
3637 'Y',p_max_rows));
3638 end Run_SQL_text;
3639
3640 function Run_SQL_text(p_title varchar2,
3641 p_sql_statement varchar2,
3642 p_disp_lengths lengths,
3643 p_headers headers,
3644 p_feedback varchar2,
3645 p_max_rows number)
3646 return number is
3647 begin
3648 SectionPrint(p_title);
3649 BRPrint;
3650 return(Display_SQL(p_sql_statement , p_disp_lengths, p_headers,
3651 p_feedback,p_max_rows));
3652 end Run_SQL_text;
3653
3654 procedure Run_SQL_text(p_title varchar2,
3655 p_sql_statement varchar2,
3656 p_disp_lengths lengths) is
3657 dummy number;
3658 begin
3659 SectionPrint(p_title);
3660 BRPrint;
3661 dummy := Display_SQL(p_sql_statement , p_disp_lengths);
3662 end Run_SQL_text;
3663
3664 procedure Run_SQL_text(p_title varchar2,
3665 p_sql_statement varchar2,
3666 p_disp_lengths lengths,
3667 p_headers headers) is
3668 dummy number;
3669 begin
3670 SectionPrint(p_title);
3671 BRPrint;
3672 dummy := Display_SQL(p_sql_statement , p_disp_lengths, p_headers);
3673 end Run_SQL_text;
3674
3675 procedure Run_SQL_text(p_title varchar2,
3676 p_sql_statement varchar2,
3677 p_disp_lengths lengths,
3678 p_headers headers,
3679 p_feedback varchar2) is
3680 dummy number;
3681 begin
3682 SectionPrint(p_title);
3683 BRPrint;
3684 dummy := Display_SQL(p_sql_statement , p_disp_lengths, p_headers,
3685 p_feedback);
3686 end Run_SQL_text;
3687
3688 procedure Run_SQL_text(p_title varchar2,
3689 p_sql_statement varchar2,
3690 p_disp_lengths lengths,
3691 p_headers headers,
3692 p_max_rows number) is
3693 dummy number;
3694 begin
3695 SectionPrint(p_title);
3696 BRPrint;
3697 dummy := Display_SQL(p_sql_statement , p_disp_lengths, p_headers,
3698 'Y', p_max_rows);
3699 end Run_SQL_text;
3700
3701 procedure Run_SQL_text(p_title varchar2,
3702 p_sql_statement varchar2,
3703 p_disp_lengths lengths,
3704 p_headers headers,
3705 p_feedback varchar2,
3706 p_max_rows number) is
3707 dummy number;
3708 begin
3709 SectionPrint(p_title);
3710 BRPrint;
3711 dummy := Display_SQL(p_sql_statement , p_disp_lengths, p_headers,
3712 p_feedback, p_max_rows);
3713 end Run_SQL_text;
3714
3715 /*
3716 Procedure Name: Display_Table
3717 Usage:
3718 Display_Table('Table Name', 'Heading', 'Where Clause',
3719 'Order By', 'Long Flag');
3720 Parameters:
3721 Table Name - Any Valid Table or View
3722 Heading - Text String to for heading the output
3723 Where Clause - Where clause to apply to the table dump
3724 Order By - Order By clause to apply to the table dump
3725 Long Flag - 'Y' or 'N' - If set to 'N' then this will not
3726 output any LONG columns
3727 Output:
3728 Displays the output of the 'select * from table' as an HTML table.
3729 Examples:
3730 begin
3731 Display_Table('AR_SYSTEM_PARAMETERS_ALL', 'AR Parameters',
3732 'Where Org_id <> -3113'
3733 , 'order by org_id, set_of_books_id', 'N');
3734 end;
3735
3736 procedure Display_Table_text (p_table_name varchar2,
3737 p_table_alias varchar2,
3738 p_where_clause varchar2,
3739 p_order_by_clause varchar2 default null,
3740 p_display_longs varchar2 default 'Y') is
3741 dummy number;
3742 hold_char varchar(1) := null;
3743 begin
3744 if p_where_clause is not null then
3745 hold_char := l_newline;
3746 end if;
3747 dummy := Display_SQL ('select * from ' || replace(upper(p_table_name),'V_$','V$') || l_newline || p_where_clause || hold_char || nvl(p_order_by_clause,'order by 1')
3748 , nvl(p_table_alias, p_table_name)
3749 , p_display_longs);
3750 end Display_Table_text;
3751
3752 Function Name: Display_Table
3753 Usage:
3754 a_number := Display_Table('Table Name', 'Heading', 'Where Clause',
3755 'Order By', 'Long Flag');
3756 Parameters:
3757 Table Name - Any Valid Table or View
3758 Heading - Text String to for heading the output
3759 Where Clause - Where clause to apply to the table dump
3760 Order By - Order By clause to apply to the table dump
3761 Long Flag - 'Y' or 'N' - If set to 'N' then this will not output
3762 any LONG columns
3763 Output:
3764 Displays the output of the 'select * from table' as an HTML table.
3765 Returns:
3766 Number of rows displayed.
3767 Examples:
3768 declare
3769 num_rows number;
3770 begin
3771 num_rows := Display_Table('AR_SYSTEM_PARAMETERS_ALL',
3772 'AR Parameters', 'Where Org_id <> -3113',
3773 'order by org_id, set_of_books_id', 'N');
3774 end;
3775
3776 function Display_Table_text (p_table_name varchar2,
3777 p_table_alias varchar2,
3778 p_where_clause varchar2,
3779 p_order_by_clause varchar2 default null,
3780 p_display_longs varchar2 default 'Y') return number is
3781 begin
3782 return(Display_SQL ('select * from ' || replace(upper(p_table_name),'V_$','V$') || l_newline || p_where_clause || l_newline || nvl(p_order_by_clause,'order by 1')
3783 , nvl(p_table_alias, p_table_name)
3784 , p_display_longs));
3785 end Display_Table_text;
3786
3787 */
3788
3789 -- Function Name: Get_DB_Apps_Version
3790 -- Description:
3791 -- Finds the applications version and sets the global variable
3792 -- g_appl_version to the string value 10.7, 11.0, or 11.5 for
3793 -- use throughout the script where branching due to applications
3794 -- release may be necessary.
3795 -- Usage:
3796 -- a_string := Get_DB_Apps_Version;
3797 -- Parameters:
3798 -- None
3799 -- Output:
3800 -- None
3801 -- Examples:
3802 -- begin
3803 -- Tab0Print('Applications Version: '||Get_DB_Apps_Version);
3804 -- end;
3805
3806 function Get_DB_Apps_Version_text return varchar2 is
3807 l_appsver fnd_product_groups.release_name%type := null;
3808 begin
3809 select release_name into l_appsver from fnd_product_groups;
3810 g_appl_version := substr(l_appsver,1,4);
3811 return(l_appsver);
3812 end;
3813
3814
3815
3816 -- Procedure Name: Show_Header
3817 -- Usage:
3818 -- Show_Header('Note Number', 'Title');
3819 -- Parameters:
3820 -- Note Number - Any Valid Metalink Note Number
3821 -- Title - Text string to go beside the note link
3822 -- Output:
3823 -- Displays Standard Header Information
3824 -- Examples:
3825 -- begin
3826 -- Show_Header('139684.1',
3827 -- 'Oracle Applications Current Patchsets Comparison to applptch.txt');
3828 -- end;
3829
3830 procedure Show_Header_text(p_note varchar2, p_title varchar2) is
3831 l_instance_name varchar2(16) := null;
3832 l_host_name varchar2(64) := null;
3833 l_language varchar2(512):= null;
3834 l_version varchar2(17) := null;
3835 l_org_name hr_all_organization_units.name%type;
3836 begin
3837 select instance_name, host_name, version
3838 into l_instance_name, l_host_name, l_version
3839 from v$instance;
3840
3841 begin
3842 select upper(value) into l_language
3843 from v$parameter
3844 where name = 'nls_language';
3845 exception when others then
3846 l_language := null;
3847 end;
3848 SectionPrint('System Information');
3849 line_out('Machine: '|| l_host_name);
3850 line_out('Date Run: '|| to_char(sysdate,'MM-DD-YYYY HH24:MI:SS'));
3851 line_out('DB Info: SID: '||l_instance_name||' Version: '||l_version);
3852 line_out('DB Language: '||l_language);
3853 line_out('Apps Version: '|| Get_DB_Apps_Version);
3854 if g_org_id is not null then
3855 begin
3856 select name into l_org_name
3857 from hr_all_organization_units
3858 where organization_id = g_org_id;
3859 exception when others then
3860 l_org_name := null;
3861 end;
3862 if l_org_name is not null then
3863 line_out('Operating Unit: '||l_org_name||' (ID='||to_char(g_org_id)||')');
3864 end if;
3865 end if;
3866 BRPrint;
3867 if p_note is not null then
3868 if p_title is not null then
3869 line_out('Note: '||p_note||' - '||p_title);
3870 else
3871 line_out('Note: '||p_note);
3872 end if;
3873 else
3874 if p_title is not null then
3875 line_out('Title: '||p_title);
3876 end if;
3877 end if;
3878 end Show_Header_text;
3879
3880 -- Procedure Name: Show_Footer
3881 -- Usage:
3882 -- Show_Footer;
3883 -- Show_Footer('Script Description','Script Header');
3884 -- Parameters:
3885 -- Script Description - Description of the script (not used)
3886 -- Script Header - Header/version information for the script (not used)
3887 -- These parameters are allowed so that calls for the HTML API will
3888 -- work in the text API as well. They are not used for anything.
3889 -- Output:
3890 -- Displays Standard Footer
3891 -- Examples:
3892 -- begin
3893 -- Show_Footer;
3894 -- Show_Footer('My Script','$Header: jtfdiagcoreapi_b.pls 120.11.12000000.3 2007/04/05 09:33:32 rudas ship $');
3895 -- end;
3896
3897 procedure Show_Footer_text is
3898 begin
3899 line_out('Please provide feedback regarding the usefulness of this test '||
3900 'and/or tool');
3901 line_out('to [email protected]. We appreciate your '||
3902 'feedback, however,');
3903 line_out('there will be no replies to feedback emails. For '||
3904 'support issues, please log');
3905 line_out('an iTar (Service Request).');
3906 end Show_Footer_text;
3907
3908 procedure Show_Footer_text(p_dummy1 varchar2, p_dummy2 varchar2) is
3909 begin
3910 Show_Footer;
3911 end Show_Footer_text;
3912
3913 -- Procedure Name: Show_Link
3914 -- Usage:
3915 -- Show_Link('Note #');
3916 -- Output:
3917 -- Displays A link to a Metalink Note
3918 -- Examples:
3919 -- begin
3920 -- Show_Link('139684.1');
3921 -- end;
3922
3923 procedure Show_Link_text(p_note varchar2) is
3924 begin
3925 line_out('See Note: '||p_note);
3926 end Show_Link_text;
3927
3928 -- Procedure Name: Show_Link
3929 -- Usage:
3930 -- Show_Link('URL', 'Name');
3931 -- Output:
3932 -- Displays A link to a URL using the Name Parameter
3933 -- Examples:
3934 -- begin
3935 -- Show_Link('http://metalink.us.oracle.com', 'Metalink');
3936 -- end;
3937
3938 procedure Show_Link_text(p_link varchar2, p_link_name varchar2 ) is
3939 begin
3940 line_out('See '||p_link_name||' at '||p_link);
3941 end Show_Link_text;
3942
3943 -- Function Name: Get_Package_Version
3944 -- Usage:
3945 -- a_varchar := Get_Package_Version ('Object Type', 'Schema', 'Package Name');
3946 -- Returns:
3947 -- The version of the package or spec
3948 -- Examples:
3949 -- declare
3950 -- spec_ver varchar2(20);
3951 -- body_ver varchar2(20);
3952 -- begin
3953 -- spec_ver := Get_Package_Version('PACKAGE','APPS','ARH_ADDR_PKG');
3954 -- body_ver := Get_Package_Version('PACKAGE BODY','APPS','ARH_ADDR_PKG');
3955 -- end;
3956
3957 function Get_Package_Version_text (p_type varchar2, p_schema varchar2,
3958 p_package varchar2)
3959 return varchar2 is
3960 hold_version varchar2(50);
3961 begin
3962 select substr(z.text, instr(z.text,'$Header')+10, 40)
3963 into hold_version
3964 from all_source z
3965 where z.name = p_package
3966 and z.type = p_type
3967 and z.owner = p_schema
3968 and z.text like '%$Header%';
3969 hold_version := substr(hold_version, instr(hold_version,' ')+1, 50);
3970 hold_version := substr(hold_version, 1, instr(hold_version,' ')-1);
3971 return (hold_version);
3972 exception
3973 when no_data_found then
3974 ErrorPrint(p_type||' '||p_package||' owned by '||p_schema||
3975 ' does not exist');
3976 ActionPrint('Verify that this object is valid for your version of '||
3977 'applications and that the owner indicated is correct');
3978 return(null);
3979 when others then
3980 ErrorPrint(sqlerrm||' occured in Get_Package_Version');
3981 ActionPrint('Please provide this information to your support '||
3982 'representative');
3983 raise;
3984 end Get_Package_Version_text;
3985
3986 -- Function Name: Get_Package_Spec
3987 -- Usage:
3988 -- a_varchar := Get_Package_Spec('Package Name');
3989 -- Returns:
3990 -- The version of the package specification in the APPS schema
3991 -- Examples:
3992 -- declare
3993 -- spec_ver varchar2(20);
3994 -- begin
3995 -- spec_ver := Get_Package_Spec('ARH_ADDR_PKG');
3996 -- end;
3997
3998 function Get_Package_Spec_text(p_package varchar2) return varchar2 is
3999 begin
4000 return Get_Package_Version('PACKAGE','APPS',p_package);
4001 end Get_Package_Spec_text;
4002
4003 -- Function Name: Get_Package_Body
4004 -- Usage:
4005 -- a_varchar := Get_Package_Body('Package Name');
4006 -- Returns:
4007 -- The version of the package body in the APPS schema
4008 -- Examples:
4009 -- declare
4010 -- body_ver varchar2(20);
4011 -- begin
4012 -- body_ver := Get_Package_Body('ARH_ADDR_PKG');
4013 -- end;
4014
4015 function Get_Package_Body_text(p_package varchar2) return varchar2 is
4016 begin
4017 return Get_Package_Version('PACKAGE BODY','APPS',p_package);
4018 end Get_Package_Body_text;
4019
4020 -- Procedure Name: Display_Profiles
4021 -- Usage:
4022 -- Display_Profiles(application_id, 'profile short name');
4023 -- Parameters:
4024 -- application_id - if provided will limit output to profile options
4025 -- associated with this application_id
4026 -- profile_short_name - system name of the profile option. Will limit output
4027 -- to the settings for this specific profile option
4028 -- Output:
4029 -- Displays all Profile settings for the application or specific profile
4030 -- option at all levels for which it is set.
4031 -- Examples:
4032 -- begin
4033 -- Display_Profiles(275,null);
4034 -- Display_Profiles(null, 'PA_DEBUG_MODE');
4035 -- end;
4036
4037
4038 procedure Display_Profiles_text (p_application_id varchar2, p_short_name varchar2) is
4039 cursor get_profile_options is
4040 select substr(ot.user_profile_option_name,1,45) user_profile_option_name,
4041 substr(o.profile_option_name,1,30) profile_option_name,
4042 decode(v.level_id,10001, 'Site', 10002, 'Appl',
4043 10003, 'Resp',
4044 10004, 'User') lev,
4045 substr(decode(v.level_id,
4046 10001, ' ',
4047 10002, a.application_name,
4048 10003, r.responsibility_name,
4049 10004, u.user_name),1,20) lev_value,
4050 v.profile_option_value opt_value
4051 from fnd_profile_option_values v,
4052 fnd_profile_options o,
4053 fnd_profile_options_tl ot,
4054 fnd_application_tl a,
4055 fnd_responsibility_tl r,
4056 fnd_user u
4057 where o.application_id = nvl(p_application_id, o.application_id)
4058 and o.profile_option_name = nvl(p_short_name, o.profile_option_name)
4059 and v.LEVEL_VALUE =
4060 decode(level_id, 10004, u.user_id, 10003, r.responsibility_id,
4061 10002, a.application_id,10001,0)
4062 and v.profile_option_id = o.profile_option_id
4063 and v.application_id = o.application_id
4064 and a.application_id (+) = v.level_value
4065 and r.responsibility_id (+) = v.level_value
4066 and u.user_id (+) = v.level_value
4067 and ot.profile_option_name = o.profile_option_name
4068 and nvl(ot.language,'US') = nvl(USERENV('LANG'),'US')
4069 and sysdate between nvl(o.start_date_active, sysdate) and
4070 nvl(o.end_date_active,sysdate)
4071 and v.profile_option_value is not null
4072 order by ot.user_profile_option_name, v.level_id,
4073 decode(level_id,10001, 'Site', 10002, a.application_name, 10003,
4074 r.responsibility_name, 10004, u.user_name);
4075 max_user_opt integer;
4076 max_opt integer;
4077 max_lev integer;
4078 max_lev_value integer;
4079 max_opt_value integer;
4080 begin
4081 select max(length(substr(ot.user_profile_option_name,1,45))) max_u_length,
4082 max(length(substr(o.profile_option_name,1,30))) max_length,
4083 max(length(decode(v.level_id,10001, 'Site', 10002, 'Appl',
4084 10003, 'Resp',
4085 10004, 'User'))),
4086 max(length(substr(decode(v.level_id,
4087 10001, ' ',
4088 10002, a.application_name,
4089 10003, r.responsibility_name,
4090 10004, u.user_name),1,20))),
4091 max(length(v.profile_option_value))
4092 into max_user_opt, max_opt, max_lev, max_lev_value, max_opt_value
4093 from fnd_profile_option_values v,
4094 fnd_profile_options o,
4095 fnd_profile_options_tl ot,
4096 fnd_application_tl a,
4097 fnd_responsibility_tl r,
4098 fnd_user u
4099 where o.application_id = nvl(p_application_id, o.application_id)
4100 and o.profile_option_name = nvl(p_short_name, o.profile_option_name)
4101 and v.LEVEL_VALUE =
4102 decode(level_id, 10004, u.user_id, 10003, r.responsibility_id,
4103 10002, a.application_id,10001,0)
4104 and v.profile_option_id = o.profile_option_id
4105 and v.application_id = o.application_id
4106 and a.application_id (+) = v.level_value
4107 and r.responsibility_id (+) = v.level_value
4108 and u.user_id (+) = v.level_value
4109 and ot.profile_option_name = o.profile_option_name
4110 and nvl(ot.language,'US') = nvl(USERENV('LANG'),'US')
4111 and sysdate between nvl(o.start_date_active, sysdate) and
4112 nvl(o.end_date_active,sysdate)
4113 and v.profile_option_value is not null;
4114
4115 line_out(rpad('User Profile Name',greatest(max_user_opt,17),' ')||' '||
4116 rpad('System Name',greatest(max_opt,11),' ')||' '||
4117 rpad('Level',greatest(max_lev,5),' ')||' '||
4118 rpad('Level Value',greatest(max_lev_value,11),' ')||' '||
4119 rpad('Opt Value',greatest(max_opt_value,9),' '));
4120 line_out(rpad('-',greatest(max_user_opt,17),'-')||' '||
4121 rpad('-',greatest(max_opt,11),'-')||' '||
4122 rpad('-',greatest(max_lev,5),'-')||' '||
4123 rpad('-',greatest(max_lev_value,11),'-')||' '||
4124 rpad('-',greatest(max_opt_value,9),'-'));
4125 for opt in get_profile_options loop
4126 line_out(rpad(opt.user_profile_option_name,
4127 greatest(max_user_opt,17),' ')||' '||
4128 rpad(opt.profile_option_name,greatest(max_opt,11),' ')||' '||
4129 rpad(opt.lev,greatest(max_lev,5),' ')||' '||
4130 rpad(opt.lev_value,greatest(max_lev_value,11),' ')||' '||
4131 rpad(opt.opt_value,greatest(max_opt_value,9),' '));
4132 end loop;
4133 exception when others then
4134 ErrorPrint('Unexpected error: '||sqlerrm||' occured in Display_Profiles');
4135 ActionErrorPrint('Report the above error message to your support '||
4136 'representative');
4137 end Display_Profiles_text;
4138
4139 -- Procedure Name: Get_Profile_Option
4140 -- Usage:
4141 -- a_varchar := Get_Profile_Option('Short Name');
4142 -- Parameters:
4143 -- Short Name - The Short Name of the Profile Option
4144 -- Returns:
4145 -- The value of the profile option based on the user, responsibility,
4146 -- and application context.
4147 -- If Set_Client has not been run successfully then
4148 -- it will return the site level setting.
4149 -- Output:
4150 -- None
4151 -- Examples:
4152 -- declare
4153 -- prof_value varchar2(150);
4154 -- begin
4155 -- prof_value := Get_Profile_Option('AR_ALLOW_OVERAPPLICATION_IN_LOCKBOX')
4156 -- end;
4157
4158 function Get_Profile_Option_text (p_profile_option varchar2) return varchar2 is
4159 begin
4160 return FND_PROFILE.VALUE(p_profile_option);
4161 end;
4162
4163 -- Procedure Name: Set_Org
4164 -- Usage:
4165 -- Set_Org(org_id);
4166 -- Parameters:
4167 -- Org_ID - Character string containing the id of the organization to set.
4168 -- Output:
4169 -- None
4170 -- Examples:
4171 -- begin
4172 -- Set_Org('204');
4173 -- end;
4174
4175 procedure Set_Org_text (p_org_id Varchar2) is
4176 begin
4177 fnd_client_info.set_org_context(p_org_id);
4178 end Set_Org_text;
4179
4180 procedure Set_Org_text (p_org_id number) is
4181 l_org_id varchar2(10);
4182 begin
4183 l_org_id := to_char(p_org_id);
4184 fnd_client_info.set_org_context(l_org_id);
4185 end Set_Org_text;
4186
4187 -- Procedure Name: Set_Client
4188 -- Description:
4189 -- Validates user_name, responsibility_id, and application_id parameters
4190 -- If valid it initializes the session (which results in the operating
4191 -- unit being set for the session as well. Also sets the global variables
4192 -- g_user_id, g_resp_id, g_appl_id, and g_org_id which can then be used
4193 -- throughout the script.
4194 -- Usage:
4195 -- Set_Client(UserName, Responsibility_ID);
4196 -- Set_Client(UserName, Responsibility_ID, Application_ID);
4197 -- Set_Client(UserName, Responsibility_ID, Application_ID, SecurityGrp_ID);
4198 -- Parameters:
4199 -- UserName - The Name of the Applications User
4200 -- Responsibility_ID - Any Valid Responsibility ID
4201 -- Application_ID - Any Valid Application ID (275=PA) If no value
4202 -- provided, attempt to obtain from responsibility_id
4203 -- SecurityGrp_ID - A valid security_group_id
4204 -- Examples:
4205 -- begin
4206 -- Set_Client('JOEUSER',50719, 222);
4207 -- end;
4208
4209 procedure Set_Client_text(p_user_name varchar2, p_resp_id number,
4210 p_app_id number, p_sec_grp_id number) is
4211 l_cursor integer;
4212 l_num_rows integer;
4213 l_user_name fnd_user.user_name%type;
4214 l_user_id number;
4215 l_app_id number;
4216 l_counter integer;
4217 l_appl_vers fnd_product_groups.release_name%type;
4218 sqltxt varchar2(2000);
4219 inv_user exception;
4220 inv_resp exception;
4221 inv_app exception;
4222 no_app exception;
4223 begin
4224 l_user_name := upper(p_user_name);
4225 begin
4226 select user_id into l_user_id
4227 from fnd_user where user_name = l_user_name;
4228 exception
4229 when others then
4230 raise inv_user;
4231 end;
4232 l_appl_vers := get_db_apps_version; -- sets g_appl_version
4233 if g_appl_version = '11.0' or g_appl_version = '10.7' then
4234 sqltxt := 'select rg.application_id '||
4235 'from fnd_user_responsibility rg '||
4236 'where rg.responsibility_id = '||to_char(p_resp_id)||' '||
4237 'and rg.user_id = '||to_char(l_user_id);
4238 elsif g_appl_version = '11.5' then
4239 sqltxt := 'select rg.responsibility_application_id '||
4240 'from fnd_user_resp_groups rg '||
4241 'where rg.responsibility_id = '||to_char(p_resp_id)||' '||
4242 'and rg.user_id = '||to_char(l_user_id);
4243 end if;
4244 begin
4245 l_cursor := dbms_sql.open_cursor;
4246 dbms_sql.parse(l_cursor, sqltxt, dbms_sql.native);
4247 dbms_sql.define_column(l_cursor, 1, l_app_id);
4248 l_num_rows := dbms_sql.execute_and_fetch(l_cursor, TRUE);
4249 dbms_sql.column_value(l_cursor, 1, l_app_id);
4250 dbms_sql.close_cursor(l_cursor);
4251
4252 exception
4253 when no_data_found then
4254 raise inv_resp;
4255 when too_many_rows then
4256 if p_app_id is null then
4257 raise no_app;
4258 else
4259 dbms_sql.close_cursor(l_cursor);
4260 l_cursor := dbms_sql.open_cursor;
4261 dbms_sql.parse(l_cursor, sqltxt, dbms_sql.native);
4262 dbms_sql.define_column(l_cursor, 1, l_app_id);
4263 l_num_rows := dbms_sql.execute(l_cursor);
4264 while dbms_sql.fetch_rows(l_cursor) > 0 loop
4265 dbms_sql.column_value(l_cursor, 1, l_app_id);
4266 if l_app_id = p_app_id then
4267 exit;
4268 end if;
4269 end loop;
4270 dbms_sql.close_cursor(l_cursor);
4271 if l_app_id <> p_app_id then
4272 raise inv_app;
4273 end if;
4274 end if;
4275 end;
4276 l_cursor := dbms_sql.open_cursor;
4277 if g_appl_version = '11.5' then
4278 sqltxt := 'begin '||
4279 'fnd_global.apps_initialize(:user, :resp, '||
4280 ':appl, :secg); '||
4281 'end; ';
4282 dbms_sql.parse(l_cursor, sqltxt, dbms_sql.native);
4283 dbms_sql.bind_variable(l_cursor,':user',l_user_id);
4284 dbms_sql.bind_variable(l_cursor,':resp',p_resp_id);
4285 dbms_sql.bind_variable(l_cursor,':appl',l_app_id);
4286 dbms_sql.bind_variable(l_cursor,':secg',p_sec_grp_id);
4287 else
4288 sqltxt := 'begin '||
4289 'fnd_global.apps_initialize(:user,:resp,:appl); '||
4290 'end; ';
4291 dbms_sql.parse(l_cursor, sqltxt, dbms_sql.native);
4292 dbms_sql.bind_variable(l_cursor,':user',l_user_id);
4293 dbms_sql.bind_variable(l_cursor,':resp',p_resp_id);
4294 dbms_sql.bind_variable(l_cursor,':appl',l_app_id);
4295 end if;
4296 l_num_rows := dbms_sql.execute(l_cursor);
4297 g_user_id := l_user_id;
4298 g_resp_id := p_resp_id;
4299 g_appl_id := l_app_id;
4300 g_org_id := Get_Profile_Option('ORG_ID');
4301 exception
4302 when inv_user then
4303 ErrorPrint('Unable to initialize client due to invalid username: '||
4304 l_user_name);
4305 ActionErrorPrint('Set_Client has been passed an invalid username '||
4306 'parameter. Please correct this parameter if possible, and if not, '||
4307 'inform your support representative.');
4308 raise;
4309 when inv_resp then
4310 ErrorPrint('Unable to initialize client due to invalid responsibility '||
4311 'ID: '||to_char(p_resp_id));
4312 ActionErrorPrint('Set_Client has been passed an invalid responsibility '||
4313 'ID parameter. This responsibility_id either does not exist or has not '||
4314 'been assigned to the user ('||l_user_name||'). Please correct these '||
4315 'parameter values if possible, and if not inform your support '||
4316 'representative.');
4317 raise;
4318 when inv_app then
4319 ErrorPrint('Unable to initialize client due to invalid application ID: '||
4320 to_char(p_app_id));
4321 ActionErrorPrint('Set_Client has been passed an invalid application ID '||
4322 'parameter. This application either does not exist or is not '||
4323 'associated with the responsibility id ('||to_char(p_resp_id)||'). '||
4324 'Please correct this parameter value if possible, and if not inform '||
4325 'your support representative.');
4326 raise;
4327 when no_app then
4328 ErrorPrint('Set_Client was unable to obtain an application ID to '||
4329 'initialize client settings');
4330 ActionErrorPrint('No application_id was supplied and Set_Client was '||
4331 'unable to determine this from the responsibility because multiple '||
4332 'responsibilities with the same responsibility_id have been assigned '||
4333 'to this user ('||l_user_name||').');
4334 raise;
4335 when others then
4336 ErrorPrint(sqlerrm||' occured in Set_Client');
4337 ActionErrorPrint('Please inform your support representative');
4338 raise;
4339 end Set_Client_text;
4340
4341 procedure Set_Client_text(p_user_name varchar2, p_resp_id number) is
4342 begin
4343 Set_Client(p_user_name, p_resp_id, null, null);
4344 end Set_Client_text;
4345
4346 procedure Set_Client_text(p_user_name varchar2, p_resp_id number,
4347 p_app_id number ) is
4348 begin
4349 Set_Client(p_user_name, p_resp_id, p_app_id, null);
4350 end Set_Client_text;
4351
4352 /*
4353 -- OBSOLETED API SINCE THIS IS NOT COMPLIANT WITH GSCC STANDARD
4354 -- FILE.SQL.6 WHICH DOES NOT PERMIT USING OF SCHEMA
4355 -- NAMES WITHIN PLSQL CODE
4356
4357 -- Procedure Name: Get_DB_Patch_List
4358 -- Usage:
4359 -- Get_DB_Patch_List('heading', 'short name', 'bug number', 'start date');
4360 -- Parameters:
4361 -- Heading = Title to go at the top of TABLE or TEXT outputs
4362 -- Short Name = Limits to Bugs that match this expression for the
4363 -- Applications Production Short Name (LIKE)
4364 -- Bug Number = Limits to bugs that match this expression (LIKE)
4365 -- Start Date = Limits to Bugs applied after this date
4366 -- Output:
4367 -- FORMATTED TEXT listing of patches applied is displayed
4368 -- Examples:
4369 -- begin
4370 -- Get_DB_Patch_List('AD Patches Applied Since 03-MAR-2002',
4371 -- 'AD','%', '03-MAR-2002');
4372 -- end;
4373
4374 procedure Get_DB_Patch_List_text (
4375 p_heading varchar2 default 'AD_BUGS'
4376 , p_app_short_name varchar2 default '%'
4377 , p_bug_number varchar2 default '%'
4378 , p_start_date date default to_date(olddate,'MM-DD-YYYY')) is
4379 l_appl_version fnd_product_groups.release_name%type;
4380 l_disp_lengths lengths;
4381 l_headers headers;
4382 l_counter integer;
4383 sqltxt varchar2(32767);
4384 begin
4385 if g_appl_version is null then
4386 l_appl_version := get_db_apps_version; -- sets g_appl_version
4387 end if;
4388 SectionPrint(p_heading);
4389 if g_appl_version = '11.5' then
4390
4391 select count(*) into l_counter from all_tables z
4392 where z.table_name = 'AD_BUGS'
4393 and z.owner = 'APPLSYS';
4394
4395 if l_counter = 0 then
4396 WarningPrint('The function Get_DB_Patch_List is not available');
4397 ActionPrint('If the table AD_BUGS does not exist in the database you '||
4398 'must review the applptch.txt file in the APPL_TOP directory '||
4399 'for patch application information. This functionality is available '||
4400 'with release 11.5.5 and above or 11.5 with AD patchset E or higher.');
4401 else
4402 BRPrint;
4403 l_headers := headers('Patch Number','Creation Date', 'Appl Short Name');
4404 l_disp_lengths := lengths(9, 11, 5);
4405 sqltxt := 'select bug_number, creation_date, application_short_name ' ||
4406 'from ad_bugs '||
4407 'where upper(application_short_name) like '''||
4408 p_app_short_name||''''||
4409 'and bug_number like '''||p_bug_number||''''||
4410 'and creation_date >= nvl(to_date('''||
4411 to_char(p_start_date,'MM-DD-YYYY')||
4412 ''',''MM-DD-YYYY''),creation_date)';
4413 display_sql(sqltxt,l_disp_lengths, l_headers);
4414 BRPrint;
4415 end if;
4416 else
4417 WarningPrint('The Get_DB_Patch_List function is only available on '||
4418 'applications 11.5');
4419 ActionPrint('For release 11.0 and 10.7 review the file applptch.txt '||
4420 'in your APPL_TOP directory');
4421 end if;
4422 end Get_DB_Patch_List_text;
4423 */
4424
4425 -- Function Name: Get_RDBMS_Header
4426 -- Usage:
4427 -- Get_RDBMS_Header;
4428 -- Returns:
4429 -- Version of the Database from v$version
4430 -- Examples:
4431 -- declare
4432 -- RDBMS_Ver := v$version.banner%type;
4433 -- begin
4434 -- RDBMS_Ver := Get_RDBMS_Header;
4435 -- end;
4436
4437 Function Get_RDBMS_Header_text return varchar2 is
4438 l_hold_name v$database.name%type;
4439 l_DB_Ver v$version.banner%type;
4440 begin
4441 begin
4442 select name
4443 into l_hold_name
4444 from v$database;
4445 exception
4446 when others then
4447 l_hold_name := 'Not Available';
4448 end;
4449 begin
4450 select banner
4451 into l_DB_Ver
4452 from v$version
4453 where banner like 'Oracle%';
4454 exception
4455 when others then
4456 l_DB_Ver := 'Not Available';
4457 end;
4458 return(l_hold_name || ' - ' || l_DB_Ver);
4459 end Get_RDBMS_Header_text;
4460
4461 -- Function Name: Compare_Pkg_Version
4462 -- Usage:
4463 -- Compare_Pkg_Version('package_name','obj_type','obj_owner', 'outversvar',
4464 -- 'reference_version');
4465 -- Compare_Pkg_Version('package_name','obj_type', 'outversvar',
4466 -- 'reference_version');
4467 -- Parameters:
4468 -- package_name - Name of the package whose version is being checked
4469 -- obj_type - Either BODY or SPEC to determine which piece to check
4470 -- obj_owner - The owner of the package being checked. If null or
4471 -- not supplied the default is APPS.
4472 -- outversvar - A text out variable to hold the actual package version
4473 -- of the package as returned from the database
4474 -- reference_version - A string containing the version to which the
4475 -- package version should be compared (in format ###.##,
4476 -- ie, in a format convertible to a number. As opposed
4477 -- to, for example, 11.5.119, use 115.119.
4478 -- Returns:
4479 -- 'greater' if the version of the object is greater than the reference
4480 -- 'less' if the version of the object is less than the reference
4481 -- 'equal' if the version of the object is equal to the reference
4482 -- 'null' if either the reference or db version is null
4483 -- Examples:
4484 -- declare
4485 -- Comparison_Var varchar2(8);
4486 -- Package_Version varchar2(10);
4487 -- begin
4488 -- Comparison_Var := Compare_Pkg_Version('PA_UTILS2','BODY','APPS',
4489 -- Package_Version, '115.13');
4490 -- Comparison_Var := Compare_Pkg_Version('PA_UTILS2','BODY',
4491 -- Package_Version, '115.13');
4492 -- end;
4493
4494 Function Compare_Pkg_Version_text(
4495 package_name in varchar2,
4496 object_type in varchar2,
4497 object_owner in varchar2,
4498 version_str in out NOCOPY varchar2,
4499 compare_version in varchar2)
4500 return varchar2 is
4501 vers_line varchar2(1000);
4502 l_object_owner varchar2(250);
4503 db_vers_key number;
4504 in_vers_key number;
4505 begin
4506 l_object_owner := object_owner;
4507 if l_object_owner is null then
4508 l_object_owner := 'APPS';
4509 end if;
4510 in_vers_key :=
4511 to_number(substr(compare_version,instr(compare_version,'.')+1));
4512 if upper(object_type) = 'BODY' then
4513 select z.text into vers_line
4514 from dba_source z
4515 where z.name = package_name
4516 and z.owner = l_object_owner
4517 and z.text like '%$Header%'
4518 and z.type = 'PACKAGE BODY';
4519 else
4520 select z.text into vers_line
4521 from dba_source z
4522 where z.name = package_name
4523 and z.owner = l_object_owner
4524 and z.text like '%$Header%'
4525 and z.type = 'PACKAGE';
4526 end if;
4527 vers_line := substr(vers_line,instr(vers_line,'$Header:')+9);
4528 vers_line := ltrim(vers_line);
4529 vers_line := substr(vers_line,1,instr(vers_line,' ',1,2)-1);
4530 vers_line := substr(vers_line,instr(vers_line,' ')+1);
4531 version_str := vers_line;
4532 db_vers_key :=
4533 to_number(substr(vers_line,instr(vers_line,'.')+1));
4534 if db_vers_key < in_vers_key then
4535 return('less');
4536 elsif db_vers_key > in_vers_key then
4537 return('greater');
4538 elsif db_vers_key = in_vers_key then
4539 return('equal');
4540 elsif db_vers_key is null or in_vers_key is null then
4541 return('null');
4542 end if;
4543 exception when others then
4544 ErrorPrint('Unable to verify package version for '||package_name||' ('||
4545 object_type||') -- '||sqlerrm||' occured in Compare_Pkg_Version');
4546 ActionErrorPrint('Contact your support representative and supply the '||
4547 'above error information');
4548 return('null');
4549 end Compare_Pkg_Version_text;
4550
4551 Function Compare_Pkg_Version_text(
4552 package_name in varchar2,
4553 object_type in varchar2,
4554 version_str in out NOCOPY varchar2,
4555 compare_version in varchar2 default null)
4556 return varchar2 is
4557 begin
4558 return(compare_pkg_version(
4559 package_name, object_type, null, version_str,compare_version));
4560 end Compare_Pkg_Version_text;
4561
4562 /*
4563
4564 -- OBSOLETED API SINCE THIS IS NOT COMPLIANT WITH GSCC STANDARD
4565 -- FILE.SQL.6 WHICH DOES NOT PERMIT USING OF SCHEMA
4566 -- NAMES WITHIN PLSQL CODE
4567
4568
4569 -- Procedure Name: Show_Invalids
4570 -- Usage:
4571 -- Show_Invalids('start string','include errors','heading');
4572 -- Parameters:
4573 -- start string = Only return objects beginning with this string (case
4574 -- insensitive)
4575 -- include errors - Y or N to indicate whether to search on and report
4576 -- the errors from ALL_ERRORS for each of the invalid
4577 -- objects found. (DEFAULT = N)
4578 -- heading - An optional heading for the table. If null the heading will
4579 -- be "Invalid Objects (Starting with 'XXX')" where XXX is
4580 -- the start string parameter.
4581 -- Ouput:
4582 -- Will output a list of invalid objects. For PL/SQL program units the
4583 -- file name and version will be displayed. If the 'include errors'
4584 -- parameter is 'Y' a listing of errors associated with the object will
4585 -- be printed.
4586 -- Examples:
4587 -- begin
4588 -- Show_Invalids('PA_');
4589 -- Show_Invalids('GL','N','General Ledger Invalid Objects');
4590 -- end;
4591 Procedure Show_Invalids_text (p_start_string varchar2 default null
4592 , p_include_errors varchar2 default 'N'
4593 , p_heading varchar2 default null) is
4594 l_start_string varchar2(60);
4595 l_file_version varchar2(100);
4596 l_heading varchar2(500);
4597 l_sqltxt varchar2(32767);
4598 l_first_row boolean := true;
4599 l_table_row varchar2(32767);
4600 l_lengths lengths;
4601 l_hdrs headers;
4602 l_rows integer := 0;
4603 l_counter integer := 0;
4604
4605 -- OWNER CHANGE
4606 cursor get_invalids(c_start_string varchar2) is
4607 select o.object_name, o.object_type, o.owner
4608 from all_objects o
4609 where o.status = 'INVALID'
4610 and o.object_name like c_start_string escape '~'
4611 and upper(o.owner) in ('APPS', 'JTF', 'APPLSYS')
4612 order by o.object_name;
4613
4614 cursor get_file_version(
4615 c_obj_name varchar2
4616 , c_obj_type varchar2
4617 , c_obj_owner varchar2) is
4618 select substr(substr(s.text,instr(s.text,'$Header')+9),1,
4619 instr(substr(s.text,instr(s.text,'$Header')+9),' ',1,2)-1) file_vers
4620 from all_source s
4621 where name = c_obj_name
4622 and type = c_obj_type
4623 and owner = c_obj_owner
4624 and text like '%$Header%';
4625
4626 cursor get_errors (
4627 c_obj_name varchar2
4628 , c_obj_type varchar2
4629 , c_obj_owner varchar2) is
4630 select to_char(z.sequence)||') LINE: '||to_char(z.line)||' CHR: '||
4631 to_char(z.position)||' '||text error_row
4632 from all_errors z
4633 where z.name = c_obj_name
4634 and z.type = c_obj_type
4635 and z.owner = c_obj_owner;
4636
4637 begin
4638 l_start_string := upper(replace(p_start_string,'_','~_')) || '%';
4639 if p_heading is null then
4640 l_heading := 'Invalid Objects (Starting with '''||p_start_string||''')';
4641 else
4642 l_heading := p_heading;
4643 end if;
4644 SectionPrint(l_heading);
4645 l_lengths := lengths(35,13,9,20);
4646 l_hdrs := headers('Object Name','Object Type','Owner','Version');
4647
4648 for inv_rec in get_invalids(l_start_string) loop
4649 if l_first_row then
4650 Show_Table_Header(l_hdrs, l_lengths);
4651 l_first_row := false;
4652 end if;
4653 l_table_row :=
4654 rpad(nvl(substr(inv_rec.object_name,1,35),' '),35,' ')||' '||
4655 rpad(nvl(substr(inv_rec.object_type,1,13),' '),13,' ')||' '||
4656 rpad(nvl(substr(inv_rec.owner,1,9),' '),9,' ')||' ';
4657 if inv_rec.object_type like 'PACKAGE%' or
4658 inv_rec.object_type in ('PROCEDURE','FUNCTION') then
4659 open get_file_version(inv_rec.object_name, inv_rec.object_type,
4660 inv_rec.owner);
4661 fetch get_file_version into l_file_version;
4662 if get_file_version%notfound then
4663 l_file_version := null;
4664 end if;
4665 close get_file_version;
4666 else
4667 l_file_version := null;
4668 end if;
4669 l_file_version := rpad(nvl(substr(l_file_version,1,20),' '),20,' ');
4670 l_table_row := l_table_row||l_file_version;
4671 Tab0Print(l_table_row);
4672 l_rows := l_rows + 1;
4673
4674 if p_include_errors = 'Y' then
4675 l_counter := 0;
4676 for err_rec in get_errors(inv_rec.object_name, inv_rec.object_type,
4677 inv_rec.owner) loop
4678 if l_counter = 0 then
4679 Tab1Print('Object Errors:');
4680 end if;
4681 l_counter := l_counter + 1;
4682 Tab1Print(err_rec.error_row);
4683 end loop;
4684 if l_counter > 0 then
4685 BRPrint;
4686 end if;
4687 end if;
4688 end loop
4689 BRPrint;
4690 Tab0Print(to_char(l_rows)||' rows selected');
4691 BRPrint;
4692 exception when others then
4693 ErrorPrint(sqlerrm||' occurred in Show_Invalids');
4694 ActionErrorPrint('Please report this information to your support '||
4695 'representative');
4696 end Show_Invalids_text;
4697 */
4698
4699 ------------------------------------------------------------------------
4700 -- External APIs
4701 -----------------------------------------------------------------------
4702
4703 procedure line_out (text varchar2) IS
4704 BEGIN
4705 IF (JTF_DIAGNOSTIC_ADAPTUTIL.b_html_on) THEN
4706 line_out_html (text);
4707 ELSE
4708 line_out_text (text );
4709 END IF;
4710 END;
4711
4712
4713 procedure Insert_Style_Sheet IS
4714 BEGIN
4715 IF (JTF_DIAGNOSTIC_ADAPTUTIL.b_html_on) THEN
4716 Insert_Style_Sheet_html;
4717 ELSE
4718 -- API currently not implemented for text
4719 null;
4720 END IF;
4721 END;
4722
4723
4724 procedure Insert_HTML(p_text varchar2) IS
4725 BEGIN
4726 IF (JTF_DIAGNOSTIC_ADAPTUTIL.b_html_on) THEN
4727 Insert_HTML_html(p_text);
4728 ELSE
4729 -- API currently not implemented for text
4730 null;
4731 END IF;
4732 END;
4733
4734
4735 procedure ActionErrorPrint(p_text varchar2) IS
4736 BEGIN
4737 IF (JTF_DIAGNOSTIC_ADAPTUTIL.b_html_on) THEN
4738 ActionErrorPrint_html(p_text);
4739 ELSE
4740 ActionErrorPrint_text(p_text);
4741 END IF;
4742 END;
4743
4744
4745
4746
4747 procedure ActionPrint(p_text varchar2) IS
4748 BEGIN
4749 IF (JTF_DIAGNOSTIC_ADAPTUTIL.b_html_on) THEN
4750 ActionPrint_html(p_text);
4751 ELSE
4752 ActionPrint_text(p_text);
4753 END IF;
4754 END;
4755
4756
4757 procedure ActionWarningPrint(p_text varchar2) IS
4758 BEGIN
4759 IF (JTF_DIAGNOSTIC_ADAPTUTIL.b_html_on) THEN
4760 ActionWarningPrint_html(p_text);
4761 ELSE
4762 ActionWarningPrint_text(p_text);
4763 END IF;
4764 END;
4765
4766
4767
4768 procedure WarningPrint(p_text varchar2) IS
4769 BEGIN
4770 IF (JTF_DIAGNOSTIC_ADAPTUTIL.b_html_on) THEN
4771 WarningPrint_html(p_text);
4772 ELSE
4773 WarningPrint_text(p_text);
4774 END IF;
4775 END;
4776
4777
4778 procedure ActionErrorLink(p_txt1 varchar2
4779 , p_note varchar2
4780 , p_txt2 varchar2) IS
4781 BEGIN
4782 IF (JTF_DIAGNOSTIC_ADAPTUTIL.b_html_on) THEN
4783 ActionErrorLink_html(p_txt1, p_note, p_txt2);
4784 ELSE
4785 -- API currently not implemented for text
4786 null;
4787 END IF;
4788 END;
4789
4790
4791 procedure ActionErrorLink(p_txt1 varchar2
4792 , p_url varchar2
4793 , p_link_txt varchar2
4794 , p_txt2 varchar2) IS
4795 BEGIN
4796 IF (JTF_DIAGNOSTIC_ADAPTUTIL.b_html_on) THEN
4797 ActionErrorLink_html(p_txt1,p_url,p_link_txt,p_txt2);
4798 ELSE
4799 -- API currently not implemented for text
4800 null;
4801 END IF;
4802 END;
4803
4804
4805 procedure ActionWarningLink(p_txt1 varchar2
4806 , p_note varchar2
4807 , p_txt2 varchar2) IS
4808 BEGIN
4809 IF (JTF_DIAGNOSTIC_ADAPTUTIL.b_html_on) THEN
4810 ActionWarningLink_html(p_txt1, p_note, p_txt2);
4811 ELSE
4812 -- API currently not implemented for text
4813 null;
4814 END IF;
4815 END;
4816
4817
4818 procedure ActionWarningLink(p_txt1 varchar2
4819 , p_url varchar2
4820 , p_link_txt varchar2
4821 , p_txt2 varchar2) IS
4822 BEGIN
4823 IF (JTF_DIAGNOSTIC_ADAPTUTIL.b_html_on) THEN
4824 ActionWarningLink_html(p_txt1,p_url ,p_link_txt,p_txt2);
4825 ELSE
4826 -- API currently not implemented for text
4827 null;
4828 END IF;
4829 END;
4830
4831
4832 procedure ErrorPrint(p_text varchar2) IS
4833 BEGIN
4834 IF (JTF_DIAGNOSTIC_ADAPTUTIL.b_html_on) THEN
4835 ErrorPrint_html(p_text);
4836 ELSE
4837 ErrorPrint_text(p_text);
4838 END IF;
4839 END;
4840
4841
4842 procedure SectionPrint(p_text varchar2) IS
4843 BEGIN
4844 IF (JTF_DIAGNOSTIC_ADAPTUTIL.b_html_on) THEN
4845 SectionPrint_html(p_text);
4846 ELSE
4847 SectionPrint_text(p_text);
4848 END IF;
4849 END;
4850
4851
4852 procedure Tab0Print (p_text varchar2) IS
4853 BEGIN
4854 IF (JTF_DIAGNOSTIC_ADAPTUTIL.b_html_on) THEN
4855 Tab0Print_html(p_text);
4856 ELSE
4857 Tab0Print_text(p_text);
4858 END IF;
4859 END;
4860
4861
4862 procedure Tab1Print (p_text varchar2) IS
4863 BEGIN
4864 IF (JTF_DIAGNOSTIC_ADAPTUTIL.b_html_on) THEN
4865 Tab1Print_html(p_text);
4866 ELSE
4867 Tab1Print_text(p_text);
4868 END IF;
4869 END;
4870
4871
4872
4873 procedure Tab2Print (p_text varchar2) IS
4874 BEGIN
4875 IF (JTF_DIAGNOSTIC_ADAPTUTIL.b_html_on) THEN
4876 Tab2Print_html(p_text);
4877 ELSE
4878 Tab2Print_text(p_text);
4879 END IF;
4880 END;
4881
4882 procedure Tab3Print (p_text varchar2) IS
4883 BEGIN
4884 IF (JTF_DIAGNOSTIC_ADAPTUTIL.b_html_on) THEN
4885 Tab3Print_html(p_text);
4886 ELSE
4887 Tab3Print_text(p_text);
4888 END IF;
4889 END;
4890
4891 procedure BRPrint IS
4892 BEGIN
4893 IF (JTF_DIAGNOSTIC_ADAPTUTIL.b_html_on) THEN
4894 BRPrint_html;
4895 ELSE
4896 BRPrint_text;
4897 END IF;
4898 END;
4899
4900 procedure checkFinPeriod (p_sobid NUMBER, p_appid NUMBER ) IS
4901 BEGIN
4902 IF (JTF_DIAGNOSTIC_ADAPTUTIL.b_html_on) THEN
4903 checkFinPeriod_html(p_sobid, p_appid);
4904 ELSE
4905 checkFinPeriod_text(p_sobid, p_appid );
4906 END IF;
4907 END;
4908
4909
4910 procedure CheckKeyFlexfield(p_flex_code in varchar2
4911 , p_flex_num in number default null
4912 , p_print_heading in boolean default true) IS
4913 BEGIN
4914 IF (JTF_DIAGNOSTIC_ADAPTUTIL.b_html_on) THEN
4915 CheckKeyFlexfield_html(p_flex_code,p_flex_num,p_print_heading);
4916 ELSE
4917 CheckKeyFlexfield_text(p_flex_code,p_flex_num,p_print_heading);
4918 END IF;
4919 END;
4920
4921
4922 procedure CheckProfile(p_prof_name in varchar2
4923 , p_user_id in number
4924 , p_resp_id in number
4925 , p_appl_id in number
4926 , p_default in varchar2 default null
4927 , p_indent in integer default 0) IS
4928 BEGIN
4929 IF (JTF_DIAGNOSTIC_ADAPTUTIL.b_html_on) THEN
4930 CheckProfile_html(p_prof_name, p_user_id, p_resp_id,p_appl_id,p_default,p_indent);
4931 ELSE
4932 CheckProfile_text(p_prof_name, p_user_id, p_resp_id,p_appl_id,p_default,p_indent);
4933 END IF;
4934 END;
4935
4936
4937 procedure Begin_Pre IS
4938 BEGIN
4939 IF (JTF_DIAGNOSTIC_ADAPTUTIL.b_html_on) THEN
4940 Begin_Pre_html;
4941 ELSE
4942 -- API currently not implemented for text
4943 null;
4944 END IF;
4945 END;
4946
4947
4948 procedure End_Pre IS
4949 BEGIN
4950 IF (JTF_DIAGNOSTIC_ADAPTUTIL.b_html_on) THEN
4951 End_Pre_html;
4952 ELSE
4953 -- API currently not implemented for text
4954 null;
4955 END IF;
4956 END;
4957
4958
4959 procedure Show_Table(p_type varchar2, p_values V2T, p_caption varchar2 default null, p_options V2T default null) IS
4960 BEGIN
4961 IF (JTF_DIAGNOSTIC_ADAPTUTIL.b_html_on) THEN
4962 Show_Table_html(p_type, p_values, p_caption, p_options);
4963 ELSE
4964 -- API currently not implemented for text
4965 null;
4966 END IF;
4967 END;
4968
4969
4970 procedure Show_Table(p_values V2T) IS
4971 BEGIN
4972 IF (JTF_DIAGNOSTIC_ADAPTUTIL.b_html_on) THEN
4973 Show_Table_html(p_values);
4974 ELSE
4975 -- API currently not implemented for text
4976 null;
4977 END IF;
4978 END;
4979
4980
4981 procedure Show_Table(p_type varchar2) IS
4982 BEGIN
4983 IF (JTF_DIAGNOSTIC_ADAPTUTIL.b_html_on) THEN
4984 Show_Table_html(p_type);
4985 ELSE
4986 -- API currently not implemented for text
4987 null;
4988 END IF;
4989 END;
4990
4991
4992 procedure Show_Table_Row(p_values V2T, p_options V2T default null) IS
4993 BEGIN
4994 IF (JTF_DIAGNOSTIC_ADAPTUTIL.b_html_on) THEN
4995 Show_Table_Row_html(p_values, p_options);
4996 ELSE
4997 -- API currently not implemented for text
4998 null;
4999 END IF;
5000 END;
5001
5002
5003 procedure Show_Table_Header(p_values V2T, p_options V2T default null) IS
5004 BEGIN
5005 IF (JTF_DIAGNOSTIC_ADAPTUTIL.b_html_on) THEN
5006 Show_Table_Header_html(p_values, p_options);
5007 ELSE
5008 -- API currently not implemented for text
5009 null;
5010 END IF;
5011 END;
5012
5013 procedure Show_Table_Header(p_headers in headers, p_lengths in out NOCOPY lengths) IS
5014 BEGIN
5015 IF (JTF_DIAGNOSTIC_ADAPTUTIL.b_html_on) THEN
5016 Show_Table_Header_text(p_headers,p_lengths);
5017 ELSE
5018 -- API currently not implemented for text
5019 null;
5020 END IF;
5021 END;
5022
5023
5024 procedure Start_Table(p_caption varchar2 default null) IS
5025 BEGIN
5026 IF (JTF_DIAGNOSTIC_ADAPTUTIL.b_html_on) THEN
5027 Start_Table_html(p_caption);
5028 ELSE
5029 -- API currently not implemented for text
5030 null;
5031 END IF;
5032 END;
5033
5034
5035 procedure End_Table IS
5036 BEGIN
5037 IF (JTF_DIAGNOSTIC_ADAPTUTIL.b_html_on) THEN
5038 End_Table_html;
5039 ELSE
5040 -- API currently not implemented for text
5041 null;
5042 END IF;
5043 END;
5044
5045
5046 function Run_SQL(p_title varchar2, p_sql_statement varchar2) return number is
5047 BEGIN
5048 IF (JTF_DIAGNOSTIC_ADAPTUTIL.b_html_on) THEN
5049 return Run_SQL_html(p_title,p_sql_statement);
5050 ELSE
5051 -- API currently not implemented for text
5052 null;
5053 END IF;
5054 END;
5055
5056 function Run_SQL(p_title varchar2
5057 , p_sql_statement varchar2
5058 , p_feedback varchar2) return number is
5059 BEGIN
5060 IF (JTF_DIAGNOSTIC_ADAPTUTIL.b_html_on) THEN
5061 return Run_SQL_html(p_title,p_sql_statement,p_feedback);
5062 ELSE
5063 -- API currently not implemented for text
5064 null;
5065 END IF;
5066 END;
5067
5068
5069 function Run_SQL(p_title varchar2
5070 , p_sql_statement varchar2
5071 , p_max_rows number) return number is
5072 BEGIN
5073 IF (JTF_DIAGNOSTIC_ADAPTUTIL.b_html_on) THEN
5074 return Run_SQL_html(p_title,p_sql_statement,p_max_rows);
5075 ELSE
5076 -- API currently not implemented for text
5077 null;
5078 END IF;
5079 END;
5080
5081
5082
5083 function Run_SQL(p_title varchar2
5084 , p_sql_statement varchar2
5085 , p_feedback varchar2
5086 , p_max_rows number) return number is
5087 BEGIN
5088 IF (JTF_DIAGNOSTIC_ADAPTUTIL.b_html_on) THEN
5089 return Run_SQL_html(p_title,p_sql_statement,p_feedback ,p_max_rows);
5090 ELSE
5091 -- API currently not implemented for text
5092 null;
5093 END IF;
5094 END;
5095
5096
5097 procedure Run_SQL(p_title varchar2, p_sql_statement varchar2) is
5098 BEGIN
5099 IF (JTF_DIAGNOSTIC_ADAPTUTIL.b_html_on) THEN
5100 Run_SQL_html(p_title,p_sql_statement);
5101 ELSE
5102 -- API currently not implemented for text
5103 null;
5104 END IF;
5105 END;
5106
5107
5108 procedure Run_SQL(p_title varchar2
5109 , p_sql_statement varchar2
5110 , p_feedback varchar2) is
5111 BEGIN
5112 IF (JTF_DIAGNOSTIC_ADAPTUTIL.b_html_on) THEN
5113 Run_SQL_html(p_title,p_sql_statement,p_feedback);
5114 ELSE
5115 -- API currently not implemented for text
5116 null;
5117 END IF;
5118 END;
5119
5120
5121 procedure Run_SQL(p_title varchar2
5122 , p_sql_statement varchar2
5123 , p_max_rows number) is
5124 BEGIN
5125 IF (JTF_DIAGNOSTIC_ADAPTUTIL.b_html_on) THEN
5126 Run_SQL_html(p_title,p_sql_statement,p_max_rows);
5127 ELSE
5128 -- API currently not implemented for text
5129 null;
5130 END IF;
5131 END;
5132
5133 procedure Run_SQL(p_title varchar2
5134 , p_sql_statement varchar2
5135 , p_feedback varchar2
5136 , p_max_rows number) IS
5137 BEGIN
5138 IF (JTF_DIAGNOSTIC_ADAPTUTIL.b_html_on) THEN
5139 Run_SQL_html(p_title,p_sql_statement,p_feedback ,p_max_rows);
5140 ELSE
5141 -- API currently not implemented for text
5142 null;
5143 END IF;
5144 END;
5145
5146
5147 function Run_SQL(p_title varchar2,
5148 p_sql_statement varchar2,
5149 p_disp_lengths lengths) return number is
5150 BEGIN
5151 IF (JTF_DIAGNOSTIC_ADAPTUTIL.b_html_on) THEN
5152 -- API currently not implemented for html
5153 null;
5154 ELSE
5155 return Run_SQL_text(p_title,p_sql_statement,p_disp_lengths);
5156 END IF;
5157 END;
5158
5159
5160
5161 function Run_SQL(p_title varchar2,
5162 p_sql_statement varchar2,
5163 p_disp_lengths lengths,
5164 p_headers headers) return number is
5165 BEGIN
5166 IF (JTF_DIAGNOSTIC_ADAPTUTIL.b_html_on) THEN
5167 -- API currently not implemented for html
5168 null;
5169 ELSE
5170 return Run_SQL_text(p_title,p_sql_statement,p_disp_lengths,p_headers);
5171 END IF;
5172 END;
5173
5174 function Run_SQL(p_title varchar2,
5175 p_sql_statement varchar2,
5176 p_disp_lengths lengths,
5177 p_headers headers,
5178 p_feedback varchar2) return number is
5179 BEGIN
5180 IF (JTF_DIAGNOSTIC_ADAPTUTIL.b_html_on) THEN
5181 -- API currently not implemented for html
5182 null;
5183 ELSE
5184 return Run_SQL_text(p_title,p_sql_statement,p_disp_lengths,p_headers,p_feedback);
5185 END IF;
5186 END;
5187
5188
5189 function Run_SQL(p_title varchar2,
5190 p_sql_statement varchar2,
5191 p_disp_lengths lengths,
5192 p_headers headers,
5193 p_max_rows number) return number is
5194 BEGIN
5195 IF (JTF_DIAGNOSTIC_ADAPTUTIL.b_html_on) THEN
5196 -- API currently not implemented for html
5197 null;
5198 ELSE
5199 return Run_SQL_text(p_title,p_sql_statement,p_disp_lengths,p_headers,p_max_rows);
5200 END IF;
5201 END;
5202
5203
5204 function Run_SQL(p_title varchar2,
5205 p_sql_statement varchar2,
5206 p_disp_lengths lengths,
5207 p_headers headers,
5208 p_feedback varchar2,
5209 p_max_rows number) return number is
5210 BEGIN
5211 IF (JTF_DIAGNOSTIC_ADAPTUTIL.b_html_on) THEN
5212 -- API currently not implemented for html
5213 null;
5214 ELSE
5215 return Run_SQL_text(p_title,p_sql_statement,p_disp_lengths,p_headers,p_feedback,p_max_rows);
5216 END IF;
5217 END;
5218
5219
5220
5221 procedure Run_SQL(p_title varchar2,
5222 p_sql_statement varchar2,
5223 p_disp_lengths lengths) IS
5224 BEGIN
5225 IF (JTF_DIAGNOSTIC_ADAPTUTIL.b_html_on) THEN
5226 -- API currently not implemented for html
5227 null;
5228 ELSE
5229 Run_SQL_text(p_title,p_sql_statement,p_disp_lengths);
5230 END IF;
5231
5232 END;
5233
5234
5235 procedure Run_SQL(p_title varchar2,
5236 p_sql_statement varchar2,
5237 p_disp_lengths lengths,
5238 p_headers headers) is
5239 BEGIN
5240 IF (JTF_DIAGNOSTIC_ADAPTUTIL.b_html_on) THEN
5241 -- API currently not implemented for html
5242 null;
5243 ELSE
5244 Run_SQL_text(p_title,p_sql_statement,p_disp_lengths,p_headers);
5245 END IF;
5246 END;
5247
5248
5249
5250 procedure Display_Table (p_table_name varchar2,
5251 p_table_alias varchar2,
5252 p_where_clause varchar2,
5253 p_order_by_clause varchar2 default null,
5254 p_display_longs varchar2 default 'Y') IS
5255 BEGIN
5256 IF (JTF_DIAGNOSTIC_ADAPTUTIL.b_html_on) THEN
5257 Display_Table_html(p_table_name,p_table_alias ,p_where_clause,p_order_by_clause,p_display_longs);
5258 ELSE
5259 -- API currently not implemented for text
5260 null;
5261 END IF;
5262 END;
5263
5264
5265
5266 procedure Show_Header(p_note varchar2, p_title varchar2) IS
5267 BEGIN
5268 IF (JTF_DIAGNOSTIC_ADAPTUTIL.b_html_on) THEN
5269 Show_Header_html(p_note, p_title);
5270 ELSE
5271 Show_Header_text(p_note, p_title);
5272 END IF;
5273 END;
5274
5275
5276
5277
5278 procedure Show_Footer(p_script_name varchar2, p_header varchar2) IS
5279 BEGIN
5280 IF (JTF_DIAGNOSTIC_ADAPTUTIL.b_html_on) THEN
5281 Show_Footer_html(p_script_name,p_header );
5282 ELSE
5283 Show_Footer_text;
5284 END IF;
5285 END;
5286
5287
5288 procedure Show_Footer IS
5289 BEGIN
5290 IF (JTF_DIAGNOSTIC_ADAPTUTIL.b_html_on) THEN
5291 -- API currently not implemented for html
5292 null;
5293 ELSE
5294 Show_Footer_text;
5295 END IF;
5296 END;
5297
5298
5299 procedure Show_Link(p_note varchar2) IS
5300 BEGIN
5301 IF (JTF_DIAGNOSTIC_ADAPTUTIL.b_html_on) THEN
5302 Show_Link_html(p_note);
5303 ELSE
5304 Show_Link_text(p_note);
5305 END IF;
5306 END;
5307
5308 procedure Show_Link(p_link varchar2, p_link_name varchar2 ) IS
5309 BEGIN
5310 IF (JTF_DIAGNOSTIC_ADAPTUTIL.b_html_on) THEN
5311 Show_Link_html(p_link, p_link_name);
5312 ELSE
5313 Show_Link_text(p_link, p_link_name);
5314 END IF;
5315 END;
5316
5317
5318 procedure Send_Email ( p_sender varchar2
5319 , p_recipient varchar2
5320 , p_subject varchar2
5321 , p_message varchar2
5322 , p_mailhost varchar2) IS
5323 BEGIN
5324 IF (JTF_DIAGNOSTIC_ADAPTUTIL.b_html_on) THEN
5325 Send_Email_html( p_sender, p_recipient, p_subject , p_message , p_mailhost);
5326 ELSE
5327 -- API currently not implemented for text
5328 null;
5329 END IF;
5330 END;
5331
5332
5333 procedure Display_Profiles (p_application_id varchar2
5334 , p_short_name varchar2 default null) IS
5335 BEGIN
5336 IF (JTF_DIAGNOSTIC_ADAPTUTIL.b_html_on) THEN
5337 Display_Profiles_html(p_application_id, p_short_name);
5338 ELSE
5339 Display_Profiles_text(p_application_id, p_short_name);
5340 END IF;
5341 END;
5342
5343
5344 procedure Set_Org (p_org_id number) IS
5345 BEGIN
5346 IF (JTF_DIAGNOSTIC_ADAPTUTIL.b_html_on) THEN
5347 Set_Org_html(p_org_id);
5348 ELSE
5349 Set_Org_text(p_org_id);
5350 END IF;
5351 END;
5352
5353 procedure Set_Client(p_user_name varchar2, p_resp_id number,
5354 p_app_id number, p_sec_grp_id number) IS
5355 BEGIN
5356 IF (JTF_DIAGNOSTIC_ADAPTUTIL.b_html_on) THEN
5357 Set_Client_html(p_user_name, p_resp_id,p_app_id, p_sec_grp_id);
5358 ELSE
5359 Set_Client_text(p_user_name, p_resp_id,p_app_id, p_sec_grp_id);
5360 END IF;
5361 END;
5362
5363 procedure Set_Client(p_user_name varchar2, p_resp_id number) IS
5364 BEGIN
5365 IF (JTF_DIAGNOSTIC_ADAPTUTIL.b_html_on) THEN
5366 Set_Client_html(p_user_name, p_resp_id);
5367 ELSE
5368 Set_Client_text(p_user_name, p_resp_id);
5369 END IF;
5370 END;
5371
5372 procedure Set_Client(p_user_name varchar2, p_resp_id number,
5373 p_app_id number ) IS
5374 BEGIN
5375 IF (JTF_DIAGNOSTIC_ADAPTUTIL.b_html_on) THEN
5376 Set_Client_html(p_user_name, p_resp_id,p_app_id);
5377 ELSE
5378 Set_Client_text(p_user_name, p_resp_id,p_app_id);
5379 END IF;
5380 END;
5381
5382
5383 /*
5384
5385 -- OBSOLETED API SINCE THIS IS NOT COMPLIANT WITH GSCC STANDARD
5386 -- FILE.SQL.6 WHICH DOES NOT PERMIT USING OF SCHEMA
5387 -- NAMES WITHIN PLSQL CODE
5388
5389 procedure Get_DB_Patch_List (p_heading varchar2 default 'AD_BUGS'
5390 , p_app_short_name varchar2 default '%'
5391 , p_bug_number varchar2 default '%'
5392 , p_start_date date default to_date(olddate,'MM-DD-YYYY')
5393 , p_output_option varchar2 default 'TABLE') IS
5394 BEGIN
5395 IF (JTF_DIAGNOSTIC_ADAPTUTIL.b_html_on) THEN
5396 Get_DB_Patch_List_html (p_heading, p_app_short_name, p_bug_number, p_start_date, p_output_option);
5397 ELSE
5398 -- API currently not implemented for text
5399 null;
5400 END IF;
5401 END;
5402 */
5403
5404
5405 /*
5406
5407 -- OBSOLETED API SINCE THIS IS NOT COMPLIANT WITH GSCC STANDARD
5408 -- FILE.SQL.6 WHICH DOES NOT PERMIT USING OF SCHEMA
5409 -- NAMES WITHIN PLSQL CODE
5410
5411 procedure Get_DB_Patch_List (
5412 p_heading varchar2 default 'AD_BUGS'
5413 , p_app_short_name varchar2 default '%'
5414 , p_bug_number varchar2 default '%'
5415 , p_start_date date default to_date(olddate,'MM-DD-YYYY')) is
5416 BEGIN
5417 IF (JTF_DIAGNOSTIC_ADAPTUTIL.b_html_on) THEN
5418 -- API currently not implemented for html
5419 null;
5420 ELSE
5421 Get_DB_Patch_List_text(p_heading, p_app_short_name, p_bug_number, p_start_date);
5422 END IF;
5423 END;
5424
5425 */
5426
5427 /*
5428
5429 -- OBSOLETED API SINCE THIS IS NOT COMPLIANT WITH GSCC STANDARD
5430 -- FILE.SQL.6 WHICH DOES NOT PERMIT USING OF SCHEMA
5431 -- NAMES WITHIN PLSQL CODE
5432
5433
5434 Procedure Show_Invalids (p_start_string varchar2
5435 , p_include_errors varchar2 default 'N'
5436 , p_heading varchar2 default null) IS
5437 BEGIN
5438 IF (JTF_DIAGNOSTIC_ADAPTUTIL.b_html_on) THEN
5439 Show_Invalids_html(p_start_string,p_include_errors,p_heading);
5440 ELSE
5441 Show_Invalids_text(p_start_string,p_include_errors,p_heading);
5442 END IF;
5443 END;
5444 */
5445
5446 Function CheckKeyFlexfield(p_flex_code in varchar2
5447 , p_flex_num in number default null
5448 , p_print_heading in boolean default true) return V2T IS
5449 BEGIN
5450 IF (JTF_DIAGNOSTIC_ADAPTUTIL.b_html_on) THEN
5451 return CheckKeyFlexfield_html(p_flex_code,p_flex_num,p_print_heading);
5452 ELSE
5453 return CheckKeyFlexfield_text(p_flex_code,p_flex_num,p_print_heading);
5454 END IF;
5455 END;
5456
5457
5458 function CheckProfile(p_prof_name in varchar2
5459 , p_user_id in number
5460 , p_resp_id in number
5461 , p_appl_id in number
5462 , p_default in varchar2 default null
5463 , p_indent in integer default 0) return varchar2 IS
5464 BEGIN
5465 IF (JTF_DIAGNOSTIC_ADAPTUTIL.b_html_on) THEN
5466 return CheckProfile_html(p_prof_name, p_user_id, p_resp_id, p_appl_id, p_default, p_indent);
5467 ELSE
5468 return CheckProfile_text(p_prof_name, p_user_id, p_resp_id, p_appl_id, p_default, p_indent);
5469 END IF;
5470 END;
5471
5472
5473
5474 function Column_Exists(p_tab in varchar, p_col in varchar, p_owner in varchar) return varchar2 IS
5475 BEGIN
5476 IF (JTF_DIAGNOSTIC_ADAPTUTIL.b_html_on) THEN
5477 return Column_Exists_html(p_tab, p_col, p_owner);
5478 ELSE
5479 return Column_Exists_text(p_tab, p_col, p_owner);
5480 END IF;
5481 END;
5482
5483
5484 function Display_SQL (p_sql_statement varchar2
5485 , table_alias varchar2
5486 , display_longs varchar2 default 'Y'
5487 , p_feedback varchar2 default 'Y'
5488 , p_max_rows number default null
5489 , p_current_exec number default 0) return number IS
5490 BEGIN
5491 IF (JTF_DIAGNOSTIC_ADAPTUTIL.b_html_on) THEN
5492 return Display_SQL_html(p_sql_statement, table_alias, FALSE, display_longs, p_feedback, p_max_rows, p_current_exec);
5493 ELSE
5494 -- API currently not implemented for text
5495 null;
5496 END IF;
5497 END;
5498
5499 function Display_SQL (p_sql_statement varchar2
5500 , table_alias varchar2
5501 , hideHeader Boolean
5502 , display_longs varchar2 default 'Y'
5503 , p_feedback varchar2 default 'Y'
5504 , p_max_rows number default null
5505 , p_current_exec number default 0) return number IS
5506 BEGIN
5507 IF (JTF_DIAGNOSTIC_ADAPTUTIL.b_html_on) THEN
5508 return Display_SQL_html(p_sql_statement, table_alias, hideHeader, display_longs, p_feedback, p_max_rows, p_current_exec);
5509 ELSE
5510 -- API currently not implemented for text
5511 null;
5512 END IF;
5513 END;
5514
5515 Function Compare_Pkg_Version(
5516 package_name in varchar2,
5517 object_type in varchar2,
5518 object_owner in varchar2,
5519 version_str in out NOCOPY varchar2,
5520 compare_version in varchar2)
5521 return varchar2 is
5522 BEGIN
5523 IF (JTF_DIAGNOSTIC_ADAPTUTIL.b_html_on) THEN
5524 -- API currently not implemented for html
5525 null;
5526 ELSE
5527 return Compare_Pkg_Version_text(package_name,object_type,object_owner,version_str,compare_version);
5528 END IF;
5529 END;
5530
5531
5532
5533 function Display_Table (p_table_name varchar2,
5534 p_table_alias varchar2,
5535 p_where_clause varchar2,
5536 p_order_by_clause varchar2 default null,
5537 p_display_longs varchar2 default 'Y') return number IS
5538 BEGIN
5539 IF (JTF_DIAGNOSTIC_ADAPTUTIL.b_html_on) THEN
5540 return Display_Table_html (p_table_name,p_table_alias, p_where_clause, p_order_by_clause, p_display_longs);
5541 ELSE
5542 -- API currently not implemented for text
5543 null;
5544 END IF;
5545 END;
5546
5547
5548
5549 function Get_DB_Apps_Version return varchar2 IS
5550 BEGIN
5551 IF (JTF_DIAGNOSTIC_ADAPTUTIL.b_html_on) THEN
5552 return Get_DB_Apps_Version_html;
5553 ELSE
5554 return Get_DB_Apps_Version_text;
5555 END IF;
5556 END;
5557
5558 function Get_Package_Version (p_type varchar2, p_schema varchar2, p_package varchar2) return varchar2 IS
5559 BEGIN
5560 IF (JTF_DIAGNOSTIC_ADAPTUTIL.b_html_on) THEN
5561 return Get_Package_Version_html(p_type, p_schema,p_package);
5562 ELSE
5563 return Get_Package_Version_text(p_type, p_schema,p_package);
5564 END IF;
5565 END;
5566
5567 function Get_Package_Spec(p_package varchar2) return varchar2 IS
5568 BEGIN
5569 IF (JTF_DIAGNOSTIC_ADAPTUTIL.b_html_on) THEN
5570 return Get_Package_Spec_html(p_package);
5571 ELSE
5572 return Get_Package_Spec_text(p_package);
5573 END IF;
5574 END;
5575
5576 function Get_Package_Body(p_package varchar2) return varchar2 IS
5577 BEGIN
5578 IF (JTF_DIAGNOSTIC_ADAPTUTIL.b_html_on) THEN
5579 return Get_Package_Body_html(p_package);
5580 ELSE
5581 return Get_Package_Body_text(p_package);
5582 END IF;
5583 END;
5584
5585
5586 function Get_Profile_Option (p_profile_option varchar2) return varchar2 IS
5587 BEGIN
5588 IF (JTF_DIAGNOSTIC_ADAPTUTIL.b_html_on) THEN
5589 return Get_Profile_Option_html(p_profile_option);
5590 ELSE
5591 return Get_Profile_Option_text(p_profile_option);
5592 END IF;
5593 END;
5594
5595
5596 Function Get_RDBMS_Header return varchar2 IS
5597 BEGIN
5598 IF (JTF_DIAGNOSTIC_ADAPTUTIL.b_html_on) THEN
5599 return Get_RDBMS_Header_html;
5600 ELSE
5601 return Get_RDBMS_Header_text;
5602 END IF;
5603 END;
5604
5605
5606
5607 function Display_SQL (
5608 p_sql_statement varchar2
5609 , p_disp_lengths lengths
5610 , p_headers headers default null
5611 , p_feedback varchar2 default 'Y'
5612 , p_max_rows number default null)
5613 return number is
5614 BEGIN
5615 IF (JTF_DIAGNOSTIC_ADAPTUTIL.b_html_on) THEN
5616 -- API currently not implemented for html
5617 null;
5618 ELSE
5619 return Display_SQL_text(p_sql_statement,p_disp_lengths,p_headers,p_feedback, p_max_rows);
5620 END IF;
5621 END;
5622
5623
5624 procedure Display_SQL (
5625 p_sql_statement varchar2
5626 , p_disp_lengths lengths) is
5627 BEGIN
5628 IF (JTF_DIAGNOSTIC_ADAPTUTIL.b_html_on) THEN
5629 -- API currently not implemented for html
5630 null;
5631 ELSE
5632 Display_SQL_text(p_sql_statement,p_disp_lengths);
5633 END IF;
5634 END;
5635
5636 procedure Display_SQL (
5637 p_sql_statement varchar2
5638 , p_disp_lengths lengths
5639 , p_headers headers) is
5640 BEGIN
5641 IF (JTF_DIAGNOSTIC_ADAPTUTIL.b_html_on) THEN
5642 -- API currently not implemented for html
5643 null;
5644 ELSE
5645 Display_SQL_text(p_sql_statement,p_disp_lengths,p_headers);
5646 END IF;
5647 END;
5648
5649 procedure Display_SQL (
5650 p_sql_statement varchar2
5651 , p_disp_lengths lengths
5652 , p_headers headers
5653 , p_feedback varchar2) is
5654 BEGIN
5655 IF (JTF_DIAGNOSTIC_ADAPTUTIL.b_html_on) THEN
5656 -- API currently not implemented for html
5657 null;
5658 ELSE
5659 Display_SQL_text(p_sql_statement,p_disp_lengths,p_headers,p_feedback);
5660 END IF;
5661 END;
5662 -- BVS-STOP <- Stop ignoring this section (restart scanning)
5663
5664
5665 procedure Display_SQL(
5666 p_sql_statement varchar2
5667 , p_disp_lengths lengths
5668 , p_headers headers
5669 , p_feedback varchar2
5670 , p_max_rows number) is
5671 BEGIN
5672 IF (JTF_DIAGNOSTIC_ADAPTUTIL.b_html_on) THEN
5673 -- API currently not implemented for html
5674 null;
5675 ELSE
5676 Display_SQL_text(p_sql_statement,p_disp_lengths,p_headers,p_feedback, p_max_rows);
5677 END IF;
5678 END;
5679
5680
5681 END JTF_DIAGNOSTIC_COREAPI;
5682
[Home] [Help]