1 PACKAGE BODY JTF_DIAGNOSTIC_COREAPI AS
2 /* $Header: jtfdiagcoreapi_b.pls 120.15 2011/04/28 11:14:31 kbnair 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.15 2011/04/28 11:14:31 kbnair 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 apps_schema_name varchar2(30);
1887 begin
1888 apps_schema_name := Get_Apps_Schema_Name();
1889 return Get_Package_Version('PACKAGE',apps_schema_name,p_package);
1890 end Get_Package_Spec_html;
1891
1892 -- Function Name: Get_Package_Body
1893 --
1894 -- Usage:
1895 -- a_varchar := Get_Package_Body('Package Name');
1896 --
1897 -- Returns:
1898 -- The version of the package body in the APPS schema
1899 --
1900 -- Examples:
1901 -- declare
1902 -- body_ver varchar2(20);
1903 -- begin
1904 -- body_ver := Get_Package_Body('ARH_ADDR_PKG');
1905 -- end;
1906 --
1907 function Get_Package_Body_html(p_package varchar2) return varchar2 is
1908 apps_schema_name varchar2(30);
1909 begin
1910 apps_schema_name := Get_Apps_Schema_Name();
1911 return Get_Package_Version('PACKAGE BODY',apps_schema_name,p_package);
1912 end Get_Package_Body_html;
1913
1914 -- BVS-START <- Starting ignoring this section
1915 -- Procedure Name: Display_Profiles
1916 --
1917 -- Usage:
1918 -- Display_Profiles(application id, 'profile short name');
1919 --
1920 -- Output:
1921 -- Displays all Profile settings for the application or profile
1922 -- in an HTML table
1923 --
1924 -- Examples:
1925 -- begin
1926 -- Display_Profiles(222,null);
1927 -- Display_Profiles(null, 'AR_ALLOW_OVERAPPLICATION_IN_LOCKBOX');
1928 -- end;
1929 --
1930 procedure Display_Profiles_html (p_application_id varchar2
1931 , p_short_name varchar2 default null) is
1932 begin
1933 Run_SQL('Profile Options',
1934 'select b.USER_PROFILE_OPTION_NAME "Long<br/>Name"'
1935 || ' , a.profile_option_name "Short<br/>Name"'
1936 || ' , decode(to_char(c.level_id),''10001'',''Site'''
1937 || ' ,''10002'',''Application'''
1938 || ' ,''10003'',''Responsibility'''
1939 || ' ,''10004'',''User'''
1940 || ' ,''Unknown'') "Level"'
1941 || ' , decode(to_char(c.level_id),''10001'',''Site'''
1942 || ' ,''10002'',nvl(h.application_short_name,to_char(c.level_value))'
1943 || ' ,''10003'',nvl(g.responsibility_name,to_char(c.level_value))'
1944 || ' ,''10004'',nvl(e.user_name,to_char(c.level_value))'
1945 || ' ,''Unknown'') "Level<br/>Value"'
1946 || ' , c.PROFILE_OPTION_VALUE "Profile<br/>Value"'
1947 || ' , c.profile_option_id "Profile<br/>ID"'
1948 || ' , to_char(c.LAST_UPDATE_DATE,''MM-DD-YYYY HH24:MI'') '
1949 || ' "Updated<br/>Date"'
1950 || ' , nvl(d.user_name,to_char(c.last_updated_by)) "Updated<br/>By"'
1951 || ' from fnd_profile_options a'
1952 || ' , FND_PROFILE_OPTIONS_TL b'
1953 || ' , FND_PROFILE_OPTION_VALUES c'
1954 || ' , FND_USER d'
1955 || ' , FND_USER e'
1956 || ' , FND_RESPONSIBILITY_TL g'
1957 || ' , FND_APPLICATION h'
1958 || ' where a.application_id = nvl(' || nvl(p_application_id,'null')
1959 || ' , a.application_id)'
1960 || ' and a.profile_option_name = nvl(''' || p_short_name
1961 || ''' , a.profile_option_name)'
1962 || ' and a.profile_option_name = b.profile_option_name'
1963 || ' and a.profile_option_id = c.profile_option_id'
1964 || ' and a.application_id = c.application_id'
1965 || ' and c.last_updated_by = d.user_id (+)'
1966 || ' and c.level_value = e.user_id (+)'
1967 || ' and c.level_value = g.responsibility_id (+)'
1968 || ' and c.level_value = h.application_id (+)'
1969 || ' and b.language = ''US'''
1970 || ' order by 1, 4, 5');
1971 end;
1972 -- BVS-STOP <- Stop ignoring this section (restart scanning)
1973
1974
1975 -- Procedure Name: Get_Profile_Option
1976 --
1977 -- Usage:
1978 -- a_varchar := Get_Profile_Option('Short Name');
1979 --
1980 -- Parameters:
1981 -- Short Name - The Short Name of the Profile Option
1982 --
1983 -- Returns:
1984 -- The value of the profile option based on the user.
1985 -- If Set_Client has not been run successfully then
1986 -- it will return the site level.
1987 --
1988 -- Output:
1989 -- None
1990 --
1991 -- Examples:
1992 -- declare
1993 -- prof_value varchar2(150);
1994 -- begin
1995 -- prof_value := Get_Profile_Option('AR_ALLOW_OVERAPPLICATION_IN_LOCKBOX')
1996 -- end;
1997 --
1998 function Get_Profile_Option_html (p_profile_option varchar2) return varchar2 is
1999 begin
2000 return FND_PROFILE.VALUE(p_profile_option);
2001 end;
2002
2003 -- Procedure Name: Set_Org
2004 --
2005 -- Usage:
2006 -- Set_Org(org_id);
2007 --
2008 -- Parameters:
2009 -- Org_ID - The id of the organization to set.
2010 --
2011 -- Output:
2012 -- None
2013 --
2014 -- Examples:
2015 -- begin
2016 -- Set_Org(204);
2017 -- end;
2018 --
2019 procedure Set_Org_html (p_org_id number) is
2020 begin
2021 fnd_client_info.set_org_context(p_org_id);
2022 end Set_Org_html;
2023
2024 -- Procedure Name: Set_Client
2025 --
2026 -- Description:
2027 -- Validates user_name, responsibility_id, and application_id parameters
2028 -- If valid it initializes the session (which results in the operating
2029 -- unit being set for the session as well. Also sets the global variables
2030 -- g_user_id, g_resp_id, g_appl_id, and g_org_id which can then be used
2031 -- throughout the script.
2032 --
2033 -- Usage:
2034 -- Set_Client(UserName, Responsibility_ID);
2035 -- Set_Client(UserName, Responsibility_ID, Application_ID);
2036 -- Set_Client(UserName, Responsibility_ID, Application_ID, SecurityGrp_ID);
2037 --
2038 -- Parameters:
2039 -- UserName - The Name of the Applications User
2040 -- Responsibility_ID - Any Valid Responsibility ID
2041 -- Application_ID - Any Valid Application ID (275=PA) If no value
2042 -- provided, attempt to obtain from responsibility_id
2043 -- SecurityGrp_ID - A valid security_group_id
2044 --
2045 -- Examples:
2046 -- begin
2047 -- Set_Client('JOEUSER',50719, 222);
2048 -- end;
2049 -- BVS-START <- Starting ignoring this section
2050 procedure Set_Client_html(p_user_name varchar2, p_resp_id number,
2051 p_app_id number, p_sec_grp_id number) is
2052 l_cursor integer;
2053 l_num_rows integer;
2054 l_user_name fnd_user.user_name%type;
2055 l_user_id number;
2056 l_app_id number;
2057 l_counter integer;
2058 l_appl_vers fnd_product_groups.release_name%type;
2059 sqltxt varchar2(2000);
2060 inv_user exception;
2061 inv_resp exception;
2062 inv_app exception;
2063 no_app exception;
2064 begin
2065 l_user_name := upper(p_user_name);
2066 begin
2067 select user_id into l_user_id
2068 from fnd_user where user_name = l_user_name;
2069 exception
2070 when others then
2071 raise inv_user;
2072 end;
2073 l_appl_vers := get_db_apps_version; -- sets g_appl_version
2074 if g_appl_version = '11.0' or g_appl_version = '10.7' then
2075 sqltxt := 'select rg.application_id '||
2076 'from fnd_user_responsibility rg '||
2077 'where rg.responsibility_id = '||to_char(p_resp_id)||' '||
2078 'and rg.user_id = '||to_char(l_user_id);
2079 elsif g_appl_version = '11.5' then
2080 sqltxt := 'select rg.responsibility_application_id '||
2081 'from fnd_user_resp_groups rg '||
2082 'where rg.responsibility_id = '||to_char(p_resp_id)||' '||
2083 'and rg.user_id = '||to_char(l_user_id);
2084 end if;
2085 begin
2086 l_cursor := dbms_sql.open_cursor;
2087 dbms_sql.parse(l_cursor, sqltxt, dbms_sql.native);
2088 dbms_sql.define_column(l_cursor, 1, l_app_id);
2089 l_num_rows := dbms_sql.execute_and_fetch(l_cursor, TRUE);
2090 dbms_sql.column_value(l_cursor, 1, l_app_id);
2091 dbms_sql.close_cursor(l_cursor);
2092
2093 exception
2094 when no_data_found then
2095 raise inv_resp;
2096 when too_many_rows then
2097 if p_app_id is null then
2098 raise no_app;
2099 else
2100 dbms_sql.close_cursor(l_cursor);
2101 l_cursor := dbms_sql.open_cursor;
2102 dbms_sql.parse(l_cursor, sqltxt, dbms_sql.native);
2103 dbms_sql.define_column(l_cursor, 1, l_app_id);
2104 l_num_rows := dbms_sql.execute(l_cursor);
2105 while dbms_sql.fetch_rows(l_cursor) > 0 loop
2106 dbms_sql.column_value(l_cursor, 1, l_app_id);
2107 if l_app_id = p_app_id then
2108 exit;
2109 end if;
2110 end loop;
2111 dbms_sql.close_cursor(l_cursor);
2112 if l_app_id <> p_app_id then
2113 raise inv_app;
2114 end if;
2115 end if;
2116 end;
2117 l_cursor := dbms_sql.open_cursor;
2118 if g_appl_version = '11.5' then
2119 sqltxt := 'begin '||
2120 'fnd_global.apps_initialize(:user, :resp, '||
2121 ':appl, :secg); '||
2122 'end; ';
2123 dbms_sql.parse(l_cursor, sqltxt, dbms_sql.native);
2124 dbms_sql.bind_variable(l_cursor,':user',l_user_id);
2125 dbms_sql.bind_variable(l_cursor,':resp',p_resp_id);
2126 dbms_sql.bind_variable(l_cursor,':appl',l_app_id);
2127 dbms_sql.bind_variable(l_cursor,':secg',p_sec_grp_id);
2128 else
2129 sqltxt := 'begin '||
2130 'fnd_global.apps_initialize(:user,:resp,:appl); '||
2131 'end; ';
2132 dbms_sql.parse(l_cursor, sqltxt, dbms_sql.native);
2133 dbms_sql.bind_variable(l_cursor,':user',l_user_id);
2134 dbms_sql.bind_variable(l_cursor,':resp',p_resp_id);
2135 dbms_sql.bind_variable(l_cursor,':appl',l_app_id);
2136 end if;
2137 l_num_rows := dbms_sql.execute(l_cursor);
2138 g_user_id := l_user_id;
2139 g_resp_id := p_resp_id;
2140 g_appl_id := l_app_id;
2141 g_org_id := Get_Profile_Option('ORG_ID');
2142 exception
2143 when inv_user then
2144 ErrorPrint('Unable to initialize client due to invalid username: '||
2145 l_user_name);
2146 ActionErrorPrint('Set_Client has been passed an invalid username '||
2147 'parameter. Please correct this parameter if possible, and if not, '||
2148 'inform your support representative.');
2149 raise;
2150 when inv_resp then
2151 ErrorPrint('Unable to initialize client due to invalid responsibility '||
2152 'ID: '||to_char(p_resp_id));
2153 ActionErrorPrint('Set_Client has been passed an invalid responsibility '||
2154 'ID parameter. This responsibility_id either does not exist or has not '||
2155 'been assigned to the user ('||l_user_name||'). Please correct these '||
2156 'parameter values if possible, and if not inform your support '||
2157 'representative.');
2158 raise;
2159 when inv_app then
2160 ErrorPrint('Unable to initialize client due to invalid application ID: '||
2161 to_char(p_app_id));
2162 ActionErrorPrint('Set_Client has been passed an invalid application ID '||
2163 'parameter. This application either does not exist or is not '||
2164 'associated with the responsibility id ('||to_char(p_resp_id)||'). '||
2165 'Please correct this parameter value if possible, and if not inform '||
2166 'your support representative.');
2167 raise;
2168 when no_app then
2169 ErrorPrint('Set_Client was unable to obtain an application ID to '||
2170 'initialize client settings');
2171 ActionErrorPrint('No application_id was supplied and Set_Client was '||
2172 'unable to determine this from the responsibility because multiple '||
2173 'responsibilities with the same responsibility_id have been assigned '||
2174 'to this user ('||l_user_name||').');
2175 raise;
2176 when others then
2177 ErrorPrint(sqlerrm||' occured in Set_Client');
2178 ActionErrorPrint('Please inform your support representative');
2179 raise;
2180 end Set_Client_html;
2181 -- BVS-STOP <- Stop ignoring this section (restart scanning)
2182
2183 procedure Set_Client_html(p_user_name varchar2, p_resp_id number) is
2184 begin
2185 Set_Client(p_user_name, p_resp_id, null, null);
2186 end Set_Client_html;
2187
2188 procedure Set_Client_html(p_user_name varchar2, p_resp_id number,
2189 p_app_id number ) is
2190 begin
2191 Set_Client(p_user_name, p_resp_id, p_app_id, null);
2192 end Set_Client_html;
2193
2194 /*
2195 -- OBSOLETED API SINCE THIS IS NOT COMPLIANT WITH GSCC STANDARD
2196 -- FILE.SQL.6 WHICH DOES NOT PERMIT USING OF SCHEMA
2197 -- NAMES WITHIN PLSQL CODE
2198 --
2199 -- Procedure Name: Get_DB_Patch_List
2200 --
2201 -- Usage:
2202 -- a_string := Get_DB_Patch_List('heading', 'short name', 'bug number', 'start date');
2203 --
2204 -- Parameters:
2205 -- Heading = Title to go at the top of TABLE or TEXT outputs
2206 -- Short Name = Limits to Bugs that match this expression for the Applications Production Short Name (LIKE)
2207 -- Bug Number = Limits to bugs that match this expression (LIKE)
2208 -- Start Date = Limits to Bugs created after this date
2209 --
2210 -- Output:
2211 -- An HTML table of patches applied for the application since the date
2212 -- indicated is displayed.
2213 --
2214 -- Examples:
2215 -- begin
2216 -- Get_DB_Patch_List(null, 'AD','%', '03-MAR-2002', 'SILENT');
2217 -- end;
2218 --
2219
2220 -- BVS-START <- Starting ignoring this section
2221 procedure Get_DB_Patch_List_html (p_heading varchar2 default 'AD_BUGS'
2222 , p_app_short_name varchar2 default '%'
2223 , p_bug_number varchar2 default '%'
2224 , p_start_date date default to_date(olddate,'MM-DD-YYYY')
2225 , p_output_option varchar2 default 'TABLE') is
2226 l_cursor integer;
2227 l_sqltxt varchar2(5000);
2228 l_list_out varchar2(32767);
2229 l_hold_comma varchar2(2);
2230 l_counter integer;
2231 l_app_short_name varchar2(50);
2232 l_bug_number varchar2(30);
2233 l_creation_date date;
2234
2235 begin
2236
2237 select count(*) into l_counter
2238 from all_tables z
2239 where z.table_name = 'AD_BUGS'
2240 and upper(z.owner) = 'APPLSYS';
2241
2242 if l_counter > 0 then
2243 l_sqltxt := 'select application_short_name'
2244 || ' , bug_number'
2245 || ' , creation_date'
2246 || ' from ad_bugs'
2247 || ' where upper(application_short_name) like '''
2248 || upper(p_app_short_name)
2249 || ''' and creation_date >= '''
2250 || nvl(to_char(p_start_date,'MM-DD-YYYY'),olddate)
2251 || ''' and bug_number like '''||p_bug_number||'''';
2252 Run_SQL(p_heading, l_sqltxt);
2253 else
2254 WarningPrint('Table AD_BUGS does not exist');
2255 ActionWarningPrint('Unable to retrieve a patch list from the database as this feature is not available on this version of the applications');
2256 end if;
2257 end Get_DB_Patch_List_html;
2258 -- BVS-STOP <- Stop ignoring this section (restart scanning)
2259 */
2260
2261 -- Function Name: Get_RDBMS_Header
2262 --
2263 -- Usage:
2264 -- Get_RDBMS_Header;
2265 --
2266 -- Returns:
2267 -- Version of the Database from v$version
2268 --
2269 -- Examples:
2270 -- declare
2271 -- RDBMS_Ver := v$version.banner%type;
2272 -- begin
2273 -- RDBMS_Ver := Get_RDBMS_Header;
2274 -- end;
2275 --
2276 Function Get_RDBMS_Header_html return varchar2 is
2277 l_hold_name v$database.name%type;
2278 l_DB_Ver v$version.banner%type;
2279 begin
2280 begin
2281 select name
2282 into l_hold_name
2283 from v$database;
2284 exception
2285 when others then
2286 l_hold_name := 'Not Available';
2287 end;
2288 begin
2289 select banner
2290 into l_DB_Ver
2291 from v$version
2292 where banner like 'Oracle%';
2293 exception
2294 when others then
2295 l_DB_Ver := 'Not Available';
2296 end;
2297 return(l_hold_name || ' - ' || l_DB_Ver);
2298 end Get_RDBMS_Header_html;
2299
2300 /*
2301 -- OBSOLETED API SINCE THIS IS NOT COMPLIANT WITH GSCC STANDARD
2302 -- FILE.SQL.6 WHICH DOES NOT PERMIT USING OF SCHEMA
2303 -- NAMES WITHIN PLSQL CODE
2304 --
2305 -- Procedure Name: Show_Invalids
2306 --
2307 -- Usage:
2308 -- Show_Invalids('start string', 'include errors', 'heading');
2309 --
2310 -- Parameters:
2311 -- start string - An string indicating the beginning of object names to
2312 -- be included. The underscore '_' character will be
2313 -- escaped in this string so that it does not act as a
2314 -- wild card character. For example, 'PA_' will not match
2315 -- 'PAY' even though it normally would in SQL*Plus.
2316 -- include errors - Y or N to indicate whether to search on and report
2317 -- the errors from ALL_ERRORS for each of the invalid
2318 -- objects found. (DEFAULT = N)
2319 -- heading - An optional heading for the table. If null the heading will
2320 -- be "Invalid Objects (Starting with 'XXX')" where XXX is
2321 -- the start string parameter.
2322 --
2323 -- Output:
2324 -- A listing of invalid objects whose name starts with the 'start string'.
2325 -- For packages, procedures, and functions, file versions will be included,
2326 -- and when requested, error messages associated with the object will
2327 -- be reported.
2328 --
2329 -- Examples:
2330 -- Show_Invalids('PA_','Y');
2331 -- Show_Invalids('GL_');
2332 --
2333 Procedure Show_Invalids_html (p_start_string varchar2
2334 , p_include_errors varchar2 default 'N'
2335 , p_heading varchar2 default null) is
2336 l_start_string varchar2(60);
2337 l_errors varchar2(32767);
2338 l_file_version varchar2(100);
2339 l_heading varchar2(500);
2340 l_first_row boolean := true;
2341 l_table_row V2T;
2342 l_row_options V2T;
2343
2344
2345 -- OWNER CHANGE
2346 cursor get_invalids(c_start_string varchar2) is
2347 select o.object_name, o.object_type, o.owner
2348 from all_objects o
2349 where o.status = 'INVALID'
2350 and o.object_name like c_start_string escape '~'
2351 and upper(o.owner) in ('APPS', 'JTF', 'APPLSYS')
2352 order by o.object_name;
2353
2354 cursor get_file_version(
2355 c_obj_name varchar2
2356 , c_obj_type varchar2
2357 , c_obj_owner varchar2) is
2358 select substr(substr(s.text,instr(s.text,'$Header')+9),1,
2359 instr(substr(s.text,instr(s.text,'$Header')+9),' ',1,2)-1) file_vers
2360 from all_source s
2361 where s.name = c_obj_name
2362 and s.type = c_obj_type
2363 and s.owner = c_obj_owner
2364 and s.text like '%$Header%';
2365
2366 cursor get_errors (
2367 c_obj_name varchar2
2368 , c_obj_type varchar2
2369 , c_obj_owner varchar2) is
2370 select to_char(sequence)||') LINE: '||to_char(line)||' CHR: '||
2371 to_char(position)||' '||text error_row
2372 from all_errors z
2373 where z.name = c_obj_name
2374 and z.type = c_obj_type
2375 and z.owner = c_obj_owner;
2376
2377 begin
2378 l_start_string := upper(replace(p_start_string,'_','~_')) || '%';
2379 if p_heading is null then
2380 l_heading := 'Invalid Objects (Starting with '''||p_start_string||''')';
2381 else
2382 l_heading := p_heading;
2383 end if;
2384 line_out('<br/><span class="BigPrint">' || l_heading || '</span>');
2385 for inv_rec in get_invalids(l_start_string) loop
2386 if l_first_row then
2387 Start_Table('Invalid Objects');
2388 if p_include_errors = 'Y' then
2389 Show_Table_Header(V2T('Object Name','Object Type', 'Owner',
2390 'File Version', 'Errors'));
2391 else
2392 Show_Table_Header(V2T('Object Name', 'Object Type', 'Owner',
2393 'File Version'));
2394 end if;
2395 l_first_row := false;
2396 end if;
2397
2398 if inv_rec.object_type like 'PACKAGE%' or
2399 inv_rec.object_type in ('PROCEDURE','FUNCTION') then
2400 open get_file_version(inv_rec.object_name, inv_rec.object_type,
2401 inv_rec.owner);
2402 fetch get_file_version into l_file_version;
2403 if get_file_version%notfound then
2404 l_file_version := null;
2405 end if;
2406 close get_file_version;
2407 else
2408 l_file_version := null;
2409 end if;
2410
2411 if p_include_errors = 'Y' then
2412 for err_rec in get_errors(inv_rec.object_name, inv_rec.object_type,
2413 inv_rec.owner) loop
2414 l_errors := l_errors||err_rec.error_row||'<br/>';
2415 end loop;
2416 l_table_row := V2T(inv_rec.object_name, inv_rec.object_type,
2417 inv_rec.owner, l_file_version, l_errors);
2418 l_row_options := V2T(null,'nowrap',null,'nowrap','nowrap');
2419 Show_Table_Row(l_table_row,l_row_options);
2420 else
2421 l_table_row := V2T(inv_rec.object_name, inv_rec.object_type,
2422 inv_rec.owner, l_file_version);
2423 Show_Table_Row(l_table_row);
2424 end if;
2425 end loop;
2426 End_Table;
2427 if l_first_row then
2428 Insert_HTML('<br/><span class="SmallPrint">No Rows Selected</span><br/>');
2429 end if;
2430 exception when others then
2431 ErrorPrint(sqlerrm||' occured in Show_Invalids');
2432 ActionErrorPrint('Use the feedback link to report the above error to '||
2433 'support');
2434 end Show_Invalids_html;
2435 */
2436
2437 ---------------------------------------------------------------
2438 -- Text Output APIs
2439 ---------------------------------------------------------------
2440
2441
2442 -- Procedure Name: Line_Out
2443 -- Description:
2444 -- Outputs plain text - same as Tab0Print
2445 -- Usage:
2446 -- Line_Out('String');
2447 -- Parameters:
2448 -- String - Any text string
2449 -- Output:
2450 -- Writes the text to the reportClob object
2451 -- Examples:
2452 -- begin
2453 -- Line_Out('Run Gather Schema Statistics');
2454 -- end;
2455
2456 procedure line_out_text(text varchar2) is
2457 begin
2458 --dbms_lob.write(JTF_DIAGNOSTIC_ADAPTUTIL.reportClob, length(text)+1, g_curr_loc, text || l_newline);
2459 --JTF_DIAGNOSTIC_ADAPTUTIL.addStringToReport(text || l_newline);
2460 JTF_DIAGNOSTIC_ADAPTUTIL.addStringToReport(text||FND_GLOBAL.Local_Chr(10));
2461 g_curr_loc := g_curr_loc + length(text)+1;
2462 end line_out_text;
2463
2464 -- Procedure Name: ActionPrint
2465 -- Usage:
2466 -- ActionPrint('String');
2467 -- Parameters:
2468 -- String - Any text string
2469 -- Output:
2470 -- Displays the text string with no indention preceded by 'ACTION - '
2471 -- Examples:
2472 -- begin
2473 -- ActionPrint('Run Gather Schema Statistics');
2474 -- end;
2475
2476 procedure ActionPrint_text(p_text varchar2) is
2477 begin
2478 line_out('ACTION - '||p_text);
2479 end ActionPrint_text;
2480
2481 -- Procedure Name: ActionErrorPrint
2482 -- Usage:
2483 -- ActionErrorPrint('String');
2484 -- Parameters:
2485 -- String - Any text string
2486 -- Output:
2487 -- Displays the text string with the word ACTION - prior to the string
2488 -- Same as ActionPrint
2489 -- Examples:
2490 -- begin
2491 -- ActionErrorPrint('Run Gather Schema Statistics');
2492 -- end;
2493
2494 procedure ActionErrorPrint_text(p_text varchar2) is
2495 begin
2496 ActionPrint(p_text);
2497 end ActionErrorPrint_text;
2498
2499 -- Procedure Name: ActionWarningPrint
2500 -- Usage:
2501 -- ActionWarningPrint('String');
2502 -- Parameters:
2503 -- String - Any text string
2504 -- Output:
2505 -- Displays the text string in warning format
2506 -- Same as ActionPrint
2507 -- Examples:
2508 -- begin
2509 -- ActionWarningPrint('Run Gather Schema Statistics');
2510 -- end;
2511
2512 procedure ActionWarningPrint_text(p_text varchar2) is
2513 begin
2514 ActionPrint(p_text);
2515 end ActionWarningPrint_text;
2516
2517 -- Procedure Name: WarningPrint
2518 -- Usage:
2519 -- WarningPrint('String');
2520 -- Parameters:
2521 -- String - Any text string
2522 -- Output:
2523 -- Displays the text string with no indentation preceded by 'WARNING - '
2524 -- Examples:
2525 -- begin
2526 -- WarningPrint('Statistics are not up to date');
2527 -- end;
2528
2529 procedure WarningPrint_text(p_text varchar2) is
2530 begin
2531 line_out('WARNING - ' ||p_text);
2532 end WarningPrint_text;
2533
2534 -- Procedure Name: ErrorPrint
2535 -- Usage:
2536 -- ErrorPrint('String');
2537 -- Parameters:
2538 -- String - Any text string
2539 -- Output:
2540 -- Displays the text string with no indentation preceded by 'ERROR - '
2541 -- Examples:
2542 -- begin
2543 -- ErrorPrint('Statistics have not been run');
2544 -- end;
2545
2546 procedure ErrorPrint_text(p_text varchar2) is
2547 begin
2548 line_out('ERROR - '||p_text);
2549 end ErrorPrint_text;
2550
2551 -- Procedure Name: SectionPrint
2552 -- Usage:
2553 -- SectionPrint('String');
2554 -- Parameters:
2555 -- String - Any text string
2556 -- Output:
2557 -- Displays the text underlined with two preceding carriage returns
2558 -- Examples:
2559 -- begin
2560 -- SectionPrint('Checking OE Parameters');
2561 -- end;
2562
2563 procedure SectionPrint_text (p_text varchar2) is
2564 ultxt varchar2(1000) := '-';
2565 begin
2566 line_out(l_newline||l_newline||p_text);
2567 ultxt := rpad(ultxt, length(p_text),'-');
2568 line_out(ultxt);
2569 end SectionPrint_text;
2570
2571 -- Procedure Name: BRPrint
2572 -- Usage:
2573 -- BRPrint;
2574 -- Output:
2575 -- Inserts a blank line.
2576 -- Examples:
2577 -- begin
2578 -- BRPrint;
2579 -- end;
2580 procedure BRPrint_text is
2581 begin
2582 line_out(l_tab);
2583 end BRPrint_text;
2584
2585
2586
2587 -- Function Name: Column_Exists
2588 -- Usage:
2589 -- Column_Exists('Table Name','Column Name');
2590 -- Paramteters:
2591 -- Table Name - Table in which to check for the column
2592 -- Column Name - Column to check
2593 -- Returns:
2594 -- 'Y' if the column exists in the table, 'N' if not.
2595 -- Examples:
2596 -- declare
2597 -- sqltxt varchar2(1000);
2598 -- begin
2599 -- if Column_Exists('PA_IMPLEMENTATIONS_ALL','UTIL_SUM_FLAG') = 'Y' then ;
2600 -- sqltxt := sqltxt||' and i.util_sum_flag is not null';
2601 -- end if;
2602 -- end;
2603
2604 function Column_Exists_text(p_tab in varchar, p_col in varchar, p_owner in varchar) return varchar2 is
2605 l_counter integer:=0;
2606
2607 begin
2608 -- UNSURE!! SHOULD WE SEEK OWNER AS PARAMETER
2609 -- DECIDED TO DO SO
2610
2611 select count(*) into l_counter
2612 from all_tab_columns z
2613 where z.table_name = upper(p_tab)
2614 and z.column_name = upper(p_col)
2615 and upper(z.owner) = upper(p_owner);
2616
2617 if l_counter > 0 then
2618 return('Y');
2619 else
2620 return('N');
2621 end if;
2622 exception when others then
2623 ErrorPrint(sqlerrm||' occured in Column_Exists');
2624 ActionErrorPrint('Report this information to your support analyst');
2625 raise;
2626 end Column_Exists_text;
2627
2628 -- Procedure Name: Tab0Print
2629 -- Usage:
2630 -- Tab0Print('String');
2631 -- Parameters:
2632 -- String - Any text string
2633 -- Output:
2634 -- Displays the text string unindented. (Same as Line_Out())
2635 -- Examples:
2636 -- begin
2637 -- Tab0Print('Layer 0');
2638 -- end;
2639
2640 procedure Tab0Print_text (p_text varchar2) is
2641 begin
2642 line_out(p_text);
2643 end Tab0Print_text;
2644
2645 -- Procedure Name: Tab1Print
2646 -- Usage:
2647 -- Tab1Print('String');
2648 -- Parameters:
2649 -- String - Any text string
2650 -- Output:
2651 -- Displays the text string indented by 1 tab character
2652 -- Examples:
2653 -- begin
2654 -- Tab1Print('Layer 1');
2655 -- end;
2656
2657 procedure Tab1Print_text (p_text varchar2) is
2658 begin
2659 line_out(l_tab||p_text);
2660 end Tab1Print_text;
2661
2662 -- Procedure Name: Tab2Print
2663 -- Usage:
2664 -- Tab2Print('String');
2665 -- Parameters:
2666 -- String - Any text string
2667 -- Output:
2668 -- Displays the text string indented two tab characters
2669 -- Examples:
2670 -- begin
2671 -- Tab2Print('Layer 2');
2672 -- end;
2673
2674 procedure Tab2Print_text (p_text varchar2) is
2675 begin
2676 line_out(l_tab||l_tab||p_text);
2677 end Tab2Print_text;
2678
2679 -- Procedure Name: Tab3Print
2680 -- Usage:
2681 -- Tab3Print('String');
2682 -- Parameters:
2683 -- String - Any text string
2684 -- Output:
2685 -- Displays the text string indented 3 tab characters
2686 -- Examples:
2687 -- begin
2688 -- Tab3Print('Layer 3');
2689 -- end;
2690
2691 procedure Tab3Print_text (p_text varchar2) is
2692 begin
2693 line_out(l_tab||l_tab||l_tab||p_text);
2694 end Tab3Print_text;
2695
2696 -- Procedure Name: CheckFinPeriod
2697 -- Usage:
2698 -- CheckFinPeriod('Set of Books ID','Application ID');
2699 -- Paramteters:
2700 -- Set of Books ID - ID for the set of books
2701 -- Application ID - ID of the application whose periods are being checked
2702 -- Output:
2703 -- List the number of defined and open periods. Indicate the latest
2704 -- open period. Produce warnings if no periods are open or if the
2705 -- current date is not in an open period.
2706 -- Examples:
2707 -- CheckFinPeriod(62, 222); -- Check open periods for AR SOB 62
2708 -- CheckFinPeriod(202, 201); -- Check open periods for PO SOB 202
2709 -- BVS-START <- Starting ignoring this section
2710
2711 procedure checkFinPeriod_text (p_sobid NUMBER, p_appid NUMBER ) IS
2712 l_appname VARCHAR2(50) :=NULL;
2713 l_period_name VARCHAR2(50);
2714 l_user_period_type VARCHAR2(50);
2715 l_start_date DATE;
2716 l_end_date DATE;
2717 l_sysdate DATE;
2718 l_sysopen VARCHAR2(1);
2719
2720 CURSOR C1 IS
2721 select a.name sobname,
2722 count(b.period_name) total_periods,
2723 count(decode(b.closing_status,'O',b.PERIOD_NAME,null)) open_periods,
2724 a.accounted_period_type period_type
2725 from gl_sets_of_books a,
2726 gl_period_statuses b
2727 where a.set_of_books_id = b.set_of_books_id (+)
2728 and b.application_id = p_appId
2729 and a.set_of_books_id = p_sobId
2730 and b.period_type = a.accounted_period_type
2731 group by a.name, a.accounted_period_type;
2732
2733 c1_rec c1%rowtype;
2734 no_rows exception;
2735
2736 BEGIN
2737
2738 select application_name
2739 into l_appname
2740 from fnd_application_vl
2741 where application_id = p_appid ;
2742
2743 open c1;
2744 fetch c1 into c1_rec;
2745 IF c1%notfound THEN
2746 raise no_rows;
2747 END IF;
2748 select user_period_type into l_user_period_type
2749 from gl_period_types
2750 where period_type = c1_rec.period_type;
2751 Tab0Print('Application = '||l_appname);
2752 Tab0Print('Set of books = '|| c1_rec.sobname ||'(ID='||to_char(p_sobid)||')');
2753 Tab0Print('Period type = '||l_user_period_type);
2754 Tab1Print('Periods Defined = ' || to_char(c1_rec.total_periods));
2755 Tab1Print('Periods Open = ' || to_char(c1_rec.open_periods));
2756 IF c1_rec.total_periods = 0 THEN
2757 WarningPrint('There are no periods defined for this Set of books');
2758 ActionWarningPrint('There must be periods defined for this set of books');
2759 END IF;
2760 IF c1_rec.open_periods = 0 THEN
2761 WarningPrint('There are no open periods defined for this Set of books');
2762 ActionWArningprint('Please consider opening a period for this '||
2763 'application and set of books');
2764 ELSE
2765 BEGIN
2766 SELECT period_name, start_date, end_date, sysdate
2767 INTO l_period_name, l_start_date, l_end_date, l_sysdate
2768 FROM gl_period_statuses
2769 WHERE adjustment_period_flag = 'N'
2770 AND period_type = c1_rec.period_type
2771 AND start_date = (
2772 SELECT MAX(start_date)
2773 FROM gl_period_statuses
2774 WHERE closing_status = 'O'
2775 AND adjustment_period_flag = 'N'
2776 AND period_type = c1_rec.period_type
2777 AND application_id = p_appId
2778 AND set_of_books_id = p_sobId )
2779 AND closing_status = 'O'
2780 AND application_id = p_appId
2781 AND set_of_books_id = p_sobId;
2782
2783 /* check if sysdate is in the latest open period */
2784
2785 l_sysopen := 'N';
2786 IF l_sysdate >= l_start_date AND l_sysdate <= l_end_date THEN
2787 l_sysOpen := 'Y';
2788 END IF;
2789 Tab0Print('Latest open period is '|| l_period_name);
2790 Tab1Print('Start date = '|| to_char(l_start_date)||' End date = '
2791 || to_char(l_end_date));
2792 IF l_sysopen = 'Y' THEN
2793 Tab0Print('Current date '|| to_char(l_sysdate)
2794 || ' is in the latest open period');
2795 ELSE
2796 BEGIN
2797 SELECT period_name, start_date, end_date, sysdate
2798 INTO l_period_name, l_start_date, l_end_date, l_sysdate
2799 FROM gl_period_statuses
2800 WHERE adjustment_period_flag = 'N'
2801 AND period_type = c1_rec.period_type
2802 AND sysdate between start_date and end_date
2803 AND closing_status = 'O'
2804 AND application_id = p_appId
2805 AND set_of_books_id = p_sobId;
2806
2807 Tab0Print('Current date '|| to_char(sysDate)
2808 || ' is in the open period ' || l_period_name);
2809 Tab1Print('Start date = ' || to_char(l_start_date)||' End date = '
2810 || to_char(l_end_date));
2811
2812 EXCEPTION WHEN NO_DATA_FOUND THEN
2813 WarningPrint('Current date '|| to_char(l_sysdate)
2814 || ' is not in an open period');
2815 ActionwarningPrint('Please consider opening the current period');
2816 END;
2817 END IF;
2818 END;
2819 END IF;
2820 EXCEPTION
2821 WHEN NO_ROWS THEN
2822 WarningPrint('There are no accounting periods defined in '||
2823 'gl_period_statuses');
2824 ActionWArningprint('If required, define the accounting calendar for this '||
2825 'application and set of books');
2826 WHEN NO_DATA_FOUND THEN
2827 ErrorPrint('Invalid Application id passed to checkFinPeriod');
2828 ActionErrorPrint('Application id ' || to_char(p_appid)
2829 || ' is not valid on this system');
2830 WHEN OTHERS THEN
2831 ErrorPrint(sqlerrm||' occurred in CheckFinPeriod');
2832 ActionErrorPrint('Report this error to your support representative');
2833 END checkFinPeriod_text;
2834 -- BVS-STOP <- Stop ignoring this section (restart scanning)
2835
2836
2837 -- Function Name: CheckKeyFlexfield
2838 -- Procedure Name: CheckKeyFlexfield
2839 --
2840 -- Usage:
2841 -- CheckKeyFlexfield('Key Flex Code','Flex Structure ID','Print Header');
2842 --
2843 -- Parameters:
2844 -- Key Flex Code - The code of the Key Flexfield to be displayed. For
2845 -- example, for the Accounting Flexfield use 'GL#'.
2846 -- Flex Structure ID - The id_flex_num of the specific structure
2847 -- of the key flexfield whose details are to be
2848 -- displayed. If null, print details of all
2849 -- structures. (default NULL)
2850 -- Print Header - A boolean (true or false) indicating whether the output
2851 -- should print a heading before outputting the details
2852 -- of the key flexfield. (default TRUE)
2853 -- Returns:
2854 -- If value has been provided for the Flex Structure ID, the function
2855 -- will returns an array of character strings with the following structure
2856 -- 1 name of the flexfield
2857 -- 2 enabled flag
2858 -- 3 frozen flag
2859 -- 4 dynamic instert flag
2860 -- 5 cross validation allowed flag
2861 -- 6 number of enabled segments defined
2862 -- 7 number of enabled segments with value sets
2863 -- 8 Y if any segment has security otherwise N
2864 -- If no value is passed to the parameter the function will return an
2865 -- array will null values.:w
2866 --
2867 -- Output:
2868 -- Displays key information about the flexfield, its structure, and the
2869 -- individual flexfield segments that make it up.
2870 --
2871 -- Examples:
2872 -- declare
2873 -- flexarray V2T;
2874 -- begin
2875 -- CheckKeyFlexfield('GL#', 50577, true);
2876 -- CheckKeyFlexfield('MSTK', null, false);
2877 -- flexarray := CheckKeyFlexfield('GL#', 12345, false);
2878 -- end;
2879 --
2880 -- BVS-START <- Starting ignoring this section
2881
2882 Function CheckKeyFlexfield_text(p_flex_code in varchar2
2883 , p_flex_num in number default null
2884 , p_print_heading in boolean default true)
2885 return V2T is
2886
2887 l_ret_array V2T := V2T(null,null,null,null,null,null,null,null);
2888 l_no_value_sets integer := 0;
2889 l_any_sec_enabled varchar2(1) := 'N';
2890 l_sec_enabled varchar2(1) := 'N';
2891 l_flex_name fnd_id_flexs.id_flex_name%type;
2892 l_counter integer := 0;
2893 l_counter2 integer := 0;
2894 l_num_segs integer := 0;
2895 l_num_segs_vs integer := 0;
2896 l_rule_count integer := 0;
2897 l_rule_assign_count integer := 0;
2898 l_value_set_str varchar2(400);
2899 leave_api exception;
2900
2901 cursor get_structs (p_f_code varchar2, p_f_num number) is
2902 select id_flex_num flex_str_num,
2903 id_flex_structure_name flex_str_name,
2904 to_char(last_update_date,'MM-DD-YYYY HH24:MI:SS') last_updated,
2905 cross_segment_validation_flag cross_val,
2906 dynamic_inserts_allowed_flag dyn_insert,
2907 enabled_flag enabled,
2908 freeze_flex_definition_flag frozen
2909 from fnd_id_flex_structures_vl
2910 where id_flex_code = p_f_code
2911 and enabled_flag ='Y'
2912 and id_flex_num = nvl(p_f_num,id_flex_num);
2913
2914 cursor get_segments (p_f_code varchar2, p_f_num number) is
2915 select s.application_column_name col_name,
2916 s.segment_name seg_name,
2917 s.segment_num seg_num,
2918 s.enabled_flag enabled,
2919 s.required_flag required,
2920 s.display_flag displayed,
2921 s.flex_value_set_id value_set_id,
2922 vs.flex_value_set_name value_set_name,
2923 DECODE(vs.validation_type,
2924 'I', 'Independent', 'N', 'None', 'D', 'Dependent',
2925 'U', 'Special', 'P', 'Pair', 'F', 'Table',
2926 'X', 'Translatable Independent', 'Y', 'Translatable Dependent',
2927 vs.validation_type) validation_type,
2928 s.security_enabled_flag seg_security,
2929 nvl(vs.security_enabled_flag,'N') value_set_security
2930 from fnd_id_flex_segments_vl s, fnd_flex_value_sets vs
2931 where s.flex_value_set_id = vs.flex_value_set_id (+)
2932 and s.id_flex_code = p_f_code
2933 and s.id_flex_num = p_f_num
2934 order by s.segment_num ;
2935
2936 cursor get_qualifiers(p_f_code varchar2, p_f_num number, p_col_name varchar2) is
2937 select segment_prompt
2938 from fnd_segment_attribute_values sav,
2939 fnd_segment_attribute_types sat
2940 where sav.attribute_value = 'Y'
2941 and sav.segment_attribute_type <> 'GL_GLOBAL'
2942 and sav.application_id = sat.application_id
2943 and sav.id_flex_code = sat.id_flex_code
2944 and sav.segment_attribute_type = sat.segment_attribute_type
2945 and sav.id_flex_code = p_f_code
2946 and sav.id_flex_num = p_f_num
2947 and sav.application_column_name = p_col_name;
2948
2949 begin
2950 begin
2951 select id_flex_name into l_flex_name
2952 from fnd_id_flexs
2953 where id_flex_code = p_flex_code;
2954 exception when no_data_found then
2955 WarningPrint('ID Flex Code passed '||p_flex_code||' is not valid on this '||
2956 'system');
2957 ActionWarningPrint('ID Flex Code '||p_flex_code||' will not be tested');
2958 end;
2959
2960 BRPrint;
2961 if p_flex_num is null then
2962 if (p_print_heading) then
2963 SectionPrint('Details of Key flexfield: '||l_flex_name);
2964 else
2965 Tab0Print('Key flexfield: '||l_flex_name);
2966 end if;
2967 else
2968 l_ret_array(1) := l_flex_name;
2969 if (p_print_heading) then
2970 SectionPrint('Details of Key flexfield: '||l_flex_name
2971 ||' with id_flex_num '||to_char(p_flex_num));
2972 else
2973 Tab0Print('Key flexfield: '||l_flex_name||' with id_flex_num '
2974 || to_char(p_flex_num));
2975 end if;
2976 end if;
2977
2978 l_counter := 0;
2979 for str in get_structs(p_flex_code, p_flex_num) loop
2980 l_counter := l_counter + 1;
2981 if p_flex_num is not null then
2982 l_ret_array(2) := str.enabled;
2983 l_ret_array(3) := str.frozen;
2984 l_ret_array(4) := str.dyn_insert;
2985 l_ret_array(5) := str.cross_val;
2986 end if;
2987 BRPrint;
2988 Tab0Print('Structure '||str.flex_str_name||' (ID='||
2989 to_char(str.flex_str_num) ||')');
2990 Tab1Print('Enabled Flag = '||str.enabled);
2991 Tab1Print('Frozen = '||str.frozen);
2992 Tab1Print('Dynamic Inserts = '||str.dyn_insert);
2993 Tab1Print('Cross Validation Allowed = '||str.cross_val);
2994 Tab1Print('Last Updated Date = '||str.last_updated);
2995
2996
2997 l_counter2 := 0;
2998 l_num_segs := 0;
2999 l_num_segs_vs := 0;
3000 for seg in get_segments(p_flex_code, str.flex_str_num) loop
3001 if l_counter2 = 0 then
3002 BRPrint;
3003 Tab0Print('Segment Details for '||str.flex_str_name);
3004 end if;
3005 l_counter2 := l_counter2 + 1;
3006
3007 if (p_flex_num is not null) then
3008 if seg.enabled = 'Y' then
3009 l_num_segs := l_num_segs + 1;
3010 if (seg.value_set_id is not null) then
3011 l_num_segs_vs := l_num_segs_vs + 1;
3012 end if;
3013 end if;
3014 end if;
3015 if (seg.seg_security = 'Y' and seg.value_set_security in ('Y','H')) then
3016 l_any_sec_enabled := 'Y';
3017 l_sec_enabled := 'Y';
3018 end if;
3019
3020 if (seg.value_set_id is not null) then
3021 l_value_set_str := ', Value Set = '||seg.value_set_name||
3022 ', Value Set Type = '||seg.validation_type;
3023 else
3024 l_value_set_str := ' with no value set assigned';
3025 end if;
3026
3027 Tab1Print('Segment Name = '||seg.seg_name);
3028 Tab2Print('Enabled = '||seg.enabled);
3029 Tab2Print('Displayed = '||seg.displayed);
3030 if seg.value_set_id is not null then
3031 Tab2Print('Value Set = '||seg.value_set_name);
3032 Tab2Print('Value Set Type = '||seg.validation_type);
3033 else
3034 Tab2Print('Value Set = None assigned');
3035 end if;
3036
3037 for qual in get_qualifiers(p_flex_code,str.flex_str_num,seg.col_name) loop
3038 Tab2Print('Qualifier '||qual.segment_prompt||' is assigned');
3039 end loop;
3040
3041 if l_sec_enabled = 'Y' then
3042 select count(*) into l_rule_count
3043 from fnd_flex_value_rules_vl
3044 where flex_value_set_id = seg.value_set_id;
3045
3046 select count(*) into l_rule_assign_count
3047 from fnd_flex_value_rules_vl r,
3048 fnd_flex_value_rule_usages ru
3049 where r.flex_value_rule_id = ru.flex_value_rule_id
3050 and r.flex_value_set_id = seg.value_set_id;
3051
3052 Tab2Print('Security is enabled for this segment and value set');
3053 Tab3Print(to_char(l_rule_count)||' rules are defined');
3054 Tab3Print(to_char(l_rule_assign_count)||' rule assignments exist');
3055 end if;
3056 end loop;
3057 if (p_flex_num is not null) then
3058 l_ret_array(6) := to_char(l_num_segs);
3059 l_ret_array(7) := to_char(l_num_segs_vs);
3060 l_ret_array(8) := l_any_sec_enabled;
3061 end if;
3062 if l_counter2 = 0 then
3063 ErrorPrint('There are no segments defined for this structure');
3064 ActionErrorPrint('Please enable or define at least one segment for '||
3065 str.flex_str_name);
3066 end if;
3067 end loop;
3068 if l_counter = 0 then
3069 if p_flex_num is null then
3070 ErrorPrint('There are no Key Flexfields enabled for ' || p_flex_code);
3071 ActionErrorPrint('Please enable or define a Key Flexfield for ' ||
3072 p_flex_code);
3073 else
3074 ErrorPrint('The requested flexfield structure (ID_FLEX_NUM='||
3075 to_char(p_flex_num)||') is inactive or does not exist');
3076 ActionErrorPrint('Verify that the flexfield structure is defined '||
3077 'and enabled for Key Flexfield '||p_flex_code);
3078 end if;
3079 end if;
3080 return l_ret_array;
3081 exception
3082 when leave_api then
3083 return l_ret_array;
3084 end;
3085
3086 procedure CheckKeyFlexfield_text(p_flex_code in varchar2
3087 , p_flex_num in number default null
3088 , p_print_heading in boolean default true) is
3089 dummy_v2t V2T;
3090 begin
3091 dummy_v2t := CheckKeyFlexfield(p_flex_code, p_flex_num, p_print_heading);
3092 end CheckKeyFlexfield_text;
3093
3094 -- Function Name: CheckProfile
3095 -- Procedure Name: CheckProfile
3096 -- Usage:
3097 -- CheckProfile('Profile Name', UserID, ResponsibilityID,
3098 -- ApplicationID, 'Default Value', Indent Level);
3099 -- Parameters:
3100 -- Profile Name - System name of the profile option being checked
3101 -- UserID - The identifier of that applications user for which the
3102 -- profile option is to be checked.
3103 -- ResponsibilityID - The identifier of the responsibility for which
3104 -- the profile option is to be checked
3105 -- ApplicationID - The identifier of the application for which the profile
3106 -- option is to be checked
3107 -- Default Value - The value that will be used as a default if the profile
3108 -- option is not set by the users (Default=NULL)
3109 -- Indent Level - Number of tabs (0,1,2,3) that output should be indented
3110 -- (Default=0)
3111 -- Returns:
3112 -- If called as a function the return value will be either:
3113 -- 1 the value of the profile option if set
3114 -- 2 'DOESNOTEXIST' if the profile option does not exist
3115 -- 3 'DISABLED' if the profile option has been end dated
3116 -- 4 null if the profile option is not set
3117 -- Output:
3118 -- If the profile is set, displays its current setting. If not set and
3119 -- a default value exists, displays a warning indicating that the default
3120 -- value will be used and indicating the value of the default. If not set
3121 -- and no default value is supplied, displays an error indicating that
3122 -- the profile option should be set. Output will be indented according
3123 -- to the Indent Level parameter supplied.
3124 --
3125 -- If the profile option does not exist or is disabled there is no
3126 -- output.
3127 -- Examples:
3128 -- declare
3129 -- profile_val fnd_profile_option_values.profile_option_value%type;
3130 -- begin
3131 -- profile_val := CheckProfile('PA_SELECTIVE_FLEX_SEG',g_user_id,
3132 -- g_resp_id, g_appl_id, null, 1);
3133 --
3134 -- CheckProfile('PA_DEBUG_MODE',g_user_id, g_resp_id, g_appl_id);
3135 -- CheckProfile('PA_DEBUG_MODE',g_user_id, g_resp_id, g_appl_id,'Y',2);
3136 -- end;
3137
3138 function CheckProfile_text(p_prof_name in varchar2
3139 , p_user_id in number
3140 , p_resp_id in number
3141 , p_appl_id in number
3142 , p_default in varchar2 default null
3143 , p_indent in integer default 0)
3144 return varchar2 is
3145 l_user_prof_name fnd_profile_options_tl.user_profile_option_name%type;
3146 l_prof_value fnd_profile_option_values.profile_option_value%type;
3147 l_start_date date;
3148 l_end_date date;
3149 l_opt_defined boolean;
3150 l_output_txt varchar2(500);
3151 begin
3152 begin
3153 select user_profile_option_name,
3154 nvl(start_date_active,sysdate-1),
3155 nvl(end_date_active,sysdate+1)
3156 into l_user_prof_name, l_start_date, l_end_date
3157 from fnd_profile_options_vl
3158 where profile_option_name = p_prof_name;
3159 exception
3160 when no_data_found then
3161 l_prof_value := 'DOESNOTEXIST';
3162 return(l_prof_value);
3163 when others then
3164 ErrorPrint(sqlerrm||' occured while getting profile option '||
3165 'information');
3166 ActionErrorPrint('Report the above information to your support '||
3167 'representative');
3168 return(null);
3169 end;
3170 if ((sysdate < l_start_date) or (sysdate > l_end_date)) then
3171 l_prof_value := 'DISABLED';
3172 return(l_prof_value);
3173 end if;
3174 fnd_profile.get_specific(p_prof_name, p_user_id, p_resp_id, p_appl_id,
3175 l_prof_value, l_opt_defined);
3176 if not l_opt_defined then
3177 l_prof_value := null;
3178 end if;
3179 if l_prof_value is null then
3180 if p_default is null then
3181 ErrorPrint(l_user_prof_name || ' profile option is not set');
3182 ActionErrorPrint('Please set the profile option according to '||
3183 'the user manual');
3184 return(l_prof_value);
3185 else
3186 WarningPrint(l_user_prof_name || ' profile option is not set '||
3187 'and will default to ' || p_default);
3188 ActionWarningPrint('Please set the profile option according to '||
3189 'the user manual if you do not want to use the default');
3190 return(l_prof_value);
3191 end if;
3192 else
3193 l_output_txt := l_user_prof_name || ' profile option is set to -- ' ||
3194 l_prof_value;
3195 if p_indent = 1 then
3196 Tab1Print(l_output_txt);
3197 elsif p_indent = 2 then
3198 Tab2Print(l_output_txt);
3199 elsif p_indent = 3 then
3200 Tab3Print(l_output_txt);
3201 else
3202 Tab0Print(l_output_txt);
3203 end if;
3204 return(l_prof_value);
3205 end if;
3206 exception when others then
3207 ErrorPrint(sqlerrm||' occured in CheckProfile');
3208 ActionErrorPrint('Please report this error to your support representative');
3209 end CheckProfile_text;
3210
3211 procedure CheckProfile_text(p_prof_name in varchar2
3212 , p_user_id in number
3213 , p_resp_id in number
3214 , p_appl_id in number
3215 , p_default in varchar2 default null
3216 , p_indent in integer default 0) is
3217 l_dummy_prof_value fnd_profile_option_values.profile_option_value%type;
3218 begin
3219 l_dummy_prof_value := CheckProfile(p_prof_name, p_user_id, p_resp_id,
3220 p_appl_id, p_default, p_indent);
3221 end CheckProfile_text;
3222
3223 -- Procedure Name: Show_Table_Header
3224 -- Description:
3225 -- Private procedure used by Display_SQL to display the headers
3226 -- Usage: N/A
3227 -- Examples: N/A
3228
3229 procedure Show_Table_Header_text(p_headers in headers,
3230 p_lengths in out NOCOPY lengths) is
3231 hdr_str varchar2(5000);
3232 ul_str varchar2(5000);
3233 begin
3234 p_lengths := p_lengths;
3235 for i in 1..p_headers.count loop
3236 if p_lengths(i) is null or p_lengths(i) < length(p_headers(i)) then
3237 p_lengths(i) := length(p_headers(i));
3238 end if;
3239 if i = 1 then
3240 hdr_str := l_return||rpad(p_headers(i),p_lengths(i),' ');
3241 ul_str := rpad('-',p_lengths(i),'-');
3242 else
3243 hdr_str := hdr_str||' '||rpad(p_headers(i),p_lengths(i),' ');
3244 ul_str := ul_str||' '||rpad('-',p_lengths(i),'-');
3245 end if;
3246 end loop;
3247 line_out(hdr_str);
3248 line_out(ul_str);
3249 end Show_Table_Header_text;
3250
3251 -- Function Name: Display_SQL
3252 -- Procedure Name: Display_SQL
3253 -- Usage:
3254 -- Function:
3255 -- a_number := Display_SQL('SQL statement', 'disp_lengths_tbl',
3256 -- 'headers_tbl', 'feedback', 'max rows');
3257 -- Procedure:
3258 -- Display_SQL('SQL statement', 'disp_lengths_tbl',
3259 -- 'headers_tbl', 'feedback', 'max rows');
3260 -- Parameters:
3261 -- SQL Statement - Any valid SQL select statement text which selects only
3262 -- columns of type Number, Date, or Varchar2.
3263 -- disp_lengths_tbl - a table of type 'lengths' indicating the display
3264 -- length for each of the columns in the select.
3265 -- A value must be supplied for each column. If the
3266 -- value is null, the length of the header will be used.
3267 -- headers_tbl - a table of type 'headers' indicating the column heading
3268 -- for each of the columns in the select. If an individual
3269 -- element of this parameter is null, or if this parameter
3270 -- is not provided (it is not required) the heading will be
3271 -- 1) the column alias
3272 -- 2) the column name
3273 -- feedback - Y or N to indicate whether a count of rows should automaticall
3274 -- be printed at the end of the output. (Default = Y)
3275 -- max rows - Maximum number of rows to output. NULL or ZERO indicates
3276 -- unlimited. (Default = NULL)
3277 -- Returns:
3278 -- The function returns the number of rows selected.
3279 -- If there is an error then the function returns null.
3280 -- Output:
3281 -- Displays the output of the SQL statement as text.
3282 -- Examples:
3283 -- declare
3284 -- num_rows number;
3285 -- sqltxt varchar2(2000);
3286 -- disp_lengths lengths;
3287 -- col_headers headers;
3288 -- begin
3289 -- sqltxt := 'Select segment1, project_type, project_id '||
3290 -- 'from pa_projects_all'
3291 -- disp_lengths := lengths(20,15,8);
3292 -- col_headers := headers('Project Number', 'Project Type', null);
3293 --
3294 -- num_rows := Display_SQL(sqltxt, disp_lengths);
3295 -- /* or */
3296 -- num_rows := Display_SQL(sqltxt, disp_lengths, col_headers);
3297 -- /* or */
3298 -- num_rows := Display_SQL(sqltxt, disp_lengths, col_headers,'N');
3299 -- tab0Print(to_char(num_rows)||' rows selected');
3300 -- /* or */
3301 -- num_rows := Display_SQL(sqltxt, disp_lengths, col_headers,'N',5);
3302 -- tab0Print(to_char(num_rows)||' rows selected');
3303 -- /* or */
3304 -- num_rows := Display_SQL(sqltxt, disp_lengths,null,'Y',5);
3305 -- /* or */
3306 -- Display_SQL(sqltxt, disp_lengths);
3307 -- /* or */
3308 -- Display_SQL(sqltxt, disp_lengths, col_headers);
3309 -- /* or */
3310 -- Display_SQL(sqltxt, disp_lengths, col_headers,'N');
3311 -- /* or */
3312 -- Display_SQL(sqltxt, disp_lengths, col_headers,'N',5);
3313 -- end;
3314
3315 function Display_SQL_text (
3316 p_sql_statement varchar2
3317 , p_disp_lengths lengths
3318 , p_headers headers default null
3319 , p_feedback varchar2 default 'Y'
3320 , p_max_rows number default null)
3321 return number is
3322 error_position number;
3323 error_position_end number;
3324 i binary_integer := 1;
3325 l_row_counter number;
3326 l_row_str varchar2(32767) := null;
3327 l_column_high binary_integer := 1;
3328 l_cursor number;
3329 l_cols dbms_sql.desc_tab;
3330 l_number_value number;
3331 l_date_value date;
3332 l_varchar_value varchar2(32767);
3333 l_dummy integer;
3334 l_headers headers;
3335 l_disp_lengths lengths;
3336 l_max_rows number;
3337
3338 T_VARCHAR2 constant integer := 1;
3339 T_NUMBER constant integer := 2;
3340 T_LONG constant integer := 8;
3341 T_DATE constant integer := 12;
3342 T_RAW constant integer := 23;
3343 T_CHAR constant integer := 96;
3344 T_TYPE constant integer := 109;
3345 T_CLOB constant integer := 112;
3346 T_BLOB constant integer := 113;
3347 T_BFILE constant integer := 114;
3348
3349 param_error exception;
3350
3351 begin
3352 if p_max_rows = 0 then
3353 l_max_rows := null;
3354 else
3355 l_max_rows := p_max_rows;
3356 end if;
3357 l_headers := p_headers;
3358 l_disp_lengths := p_disp_lengths;
3359 l_cursor := DBMS_SQL.OPEN_CURSOR;
3360 begin
3361 DBMS_SQL.PARSE(l_cursor, p_sql_statement, DBMS_SQL.V7);
3362 exception when others then
3363 ErrorPrint('Unable to parse the statement passed to Display_SQL: '||
3364 sqlerrm);
3365 ActionErrorPrint('Review the SQL statement below for errors and provide '||
3366 'the information to your support representative:'||l_newline||
3367 p_sql_statement);
3368 raise param_error;
3369 end;
3370
3371 DBMS_SQL.DESCRIBE_COLUMNS(l_cursor, l_column_high, l_cols);
3372 if l_column_high <> l_disp_lengths.count then
3373 ErrorPrint('The column length is not specified for the correct number '||
3374 'of columns in call to Display_SQL');
3375 ActionErrorPrint('You must spefify the display length for each and every '||
3376 'column of the SQL select statement');
3377 raise param_error;
3378 end if;
3379 if l_headers is null then
3380 l_headers := headers();
3381 end if;
3382 if l_headers.count <> 0 and l_headers.count <> l_disp_lengths.count then
3383 ErrorPrint('Incorrect number of headers passed to Display_SQL');
3384 ActionErrorPrint('Either no headers must be passed, or a header value '||
3385 '(which can be null) must be passed for every column of the select');
3386 raise param_error;
3387 end if;
3388 for i in 1..l_column_high loop
3389 if l_cols(i).col_type not in (T_VARCHAR2,T_NUMBER,T_DATE) then
3390 ErrorPrint('Invalid column datatype');
3391 ActionErrorPrint('The Display_SQL api does not support queries on '||
3392 'columns of type '||l_cols(i).col_type);
3393 raise param_error;
3394 end if;
3395 if l_cols(i).col_type = T_NUMBER then
3396 DBMS_SQL.DEFINE_COLUMN(l_cursor, i, l_number_value);
3397 elsif l_cols(i).col_type = T_DATE then
3398 DBMS_SQL.DEFINE_COLUMN(l_cursor, i, l_date_value);
3399 l_disp_lengths(i) := greatest(l_disp_lengths(i),11);
3400 elsif l_cols(i).col_type = T_VARCHAR2 then
3401 DBMS_SQL.DEFINE_COLUMN(l_cursor, i, l_varchar_value,
3402 l_cols(i).col_max_len);
3403 else
3404 null;
3405 end if;
3406 if l_headers.count < i then -- no header supplied
3407 while l_headers.count < i loop
3408 l_headers.extend;
3409 end loop;
3410 l_headers(i) := l_cols(i).col_name;
3411 elsif l_headers(i) is null then -- header supplied is null
3412 l_headers(i) := l_cols(i).col_name;
3413 end if;
3414 if l_cols(i).col_type = T_NUMBER then
3415 l_disp_lengths(i) := nvl(l_disp_lengths(i),length(l_headers(i)));
3416 l_headers(i) := lpad(l_headers(i),l_disp_lengths(i),' ');
3417 end if;
3418 end loop;
3419 l_dummy := dbms_sql.execute(l_cursor);
3420 l_row_counter := 0;
3421 while dbms_sql.fetch_rows(l_cursor) <> 0 loop
3422 if l_row_counter = 0 then
3423 Show_Table_Header(l_headers, l_disp_lengths);
3424 end if;
3425 l_row_counter := l_row_counter + 1;
3426 if l_row_counter > nvl(l_max_rows,l_row_counter) then
3427 l_row_counter := l_row_counter - 1;
3428 exit;
3429 end if;
3430 for i in 1..l_column_high loop
3431 if l_cols(i).col_type = T_NUMBER then
3432 dbms_sql.column_value(l_cursor, i, l_number_value);
3433 if length(to_char(l_number_value)) > l_disp_lengths(i) then
3434 l_varchar_value := rpad('*',l_disp_lengths(i),'*');
3435 else
3436 if i = 1 then
3437 if l_number_value is null then
3438 l_varchar_value := rpad(l_return,l_disp_lengths(i)+1,' ');
3439 else
3440 l_varchar_value := l_return||lpad(to_char(l_number_value),
3441 l_disp_lengths(i),' ');
3442 end if;
3443 else
3444 l_varchar_value := lpad(nvl(to_char(l_number_value),' '),
3445 l_disp_lengths(i),' ');
3446 end if;
3447 end if;
3448 elsif l_cols(i).col_type = T_DATE then
3449 dbms_sql.column_value(l_cursor, i, l_date_value);
3450 if i = 1 and l_date_value is null then
3451 l_varchar_value := rpad(l_return,l_disp_lengths(i)+1, ' ');
3452 else
3453 l_varchar_value:=rpad(nvl(to_char(l_date_value,'MM-DD-YYYY'),' '),
3454 l_disp_lengths(i), ' ');
3455 end if;
3456 elsif l_cols(i).col_type = T_VARCHAR2 then
3457 dbms_sql.column_value(l_cursor, i, l_varchar_value);
3458 if i = 1 and l_varchar_value is null then
3459 l_varchar_value := rpad(l_return,l_disp_lengths(i)+1,' ');
3460 else
3461 l_varchar_value := rpad(substr(nvl(l_varchar_value,' '),1,
3462 l_disp_lengths(i)),l_disp_lengths(i), ' ');
3463 end if;
3464 end if;
3465 if i = 1 then
3466 l_row_str := l_varchar_value;
3467 else
3468 l_row_str := l_row_str||' '||l_varchar_value;
3469 end if;
3470 end loop;
3471 l_row_str := rtrim(l_row_str);
3472 line_out(l_row_str);
3473 end loop;
3474 if p_feedback = 'Y' then
3475 BRPrint;
3476 line_out(to_char(l_row_counter)||' rows selected');
3477 BRPrint;
3478 end if;
3479 return(l_row_counter);
3480 exception
3481 when param_error then
3482 return(null);
3483 when others then
3484 ErrorPrint(sqlerrm||' occured in Display_SQL');
3485 ActionErrorPrint('Report this information to your support representative.');
3486 return(sqlcode);
3487 end Display_SQL_text;
3488
3489
3490 procedure Display_SQL_text (
3491 p_sql_statement varchar2
3492 , p_disp_lengths lengths) is
3493 l_dummy number;
3494 begin
3495 l_dummy := Display_SQL(p_sql_statement, p_disp_lengths);
3496 end Display_SQL_text;
3497
3498 procedure Display_SQL_text (
3499 p_sql_statement varchar2
3500 , p_disp_lengths lengths
3501 , p_headers headers) is
3502 l_dummy number;
3503 begin
3504 l_dummy := Display_SQL(p_sql_statement, p_disp_lengths, p_headers);
3505 end Display_SQL_text;
3506
3507 procedure Display_SQL_text (
3508 p_sql_statement varchar2
3509 , p_disp_lengths lengths
3510 , p_headers headers
3511 , p_feedback varchar2) is
3512 l_dummy number;
3513 begin
3514 l_dummy := Display_SQL(p_sql_statement, p_disp_lengths, p_headers,
3515 p_feedback);
3516 end Display_SQL_text;
3517
3518 procedure Display_SQL_text (
3519 p_sql_statement varchar2
3520 , p_disp_lengths lengths
3521 , p_headers headers
3522 , p_feedback varchar2
3523 , p_max_rows number) is
3524 l_dummy number;
3525 begin
3526 l_dummy := Display_SQL(p_sql_statement, p_disp_lengths, p_headers,
3527 p_feedback, p_max_rows);
3528 end Display_SQL_text;
3529
3530 -- Function Name: Run_SQL
3531 -- Procedure Name: Run_SQL
3532 -- Usage:
3533 -- Function:
3534 -- a_number := Run_SQL('Header', 'SQL statement','disp_lengths_tbl',
3535 -- 'col_headers_tbl');
3536 -- a_number := Run_SQL('Header', 'SQL statement','disp_lengths_tbl',
3537 -- 'col_headers_tbl','feedback');
3538 -- a_number := Run_SQL('Header', 'SQL statement','disp_lengths_tbl',
3539 -- 'col_headers_tbl','max rows');
3540 -- a_number := Run_SQL('Header', 'SQL statement','disp_lengths_tbl',
3541 -- 'col_headers_tbl','feedback', 'max rows');
3542 -- Procedure:
3543 -- Run_SQL('Header', 'SQL statement', 'disp_lengths_tbl',
3544 -- 'col_headers_tbl');
3545 -- Run_SQL('Header', 'SQL statement', 'disp_lengths_tbl',
3546 -- 'col_headers_tbl','feedback');
3547 -- Run_SQL('Header', 'SQL statement', 'disp_lengths_tbl',
3548 -- 'col_headers_tbl','max rows');
3549 -- Run_SQL('Header', 'SQL statement', 'disp_lengths_tbl',
3550 -- 'col_headers_tbl','feedback','max rows');
3551 -- Parameters:
3552 -- Header - Text String to for heading the output
3553 -- SQL Statement - Any valid SQL Select Statement
3554 -- disp_lengths_tbl - a table of type 'lengths' indicating the display
3555 -- length for each of the columns in the select.
3556 -- A value must be supplied for each column even if
3557 -- that value is null. If the value is null,
3558 -- the length of the header will be used.
3559 -- headers_tbl - a table of type 'headers' indicating the column heading
3560 -- for each of the columns in the select. If an individual
3561 -- element of this parameter is null, or if this parameter
3562 -- is not provided (it is not required) the heading will be
3563 -- 1) the column alias
3564 -- 2) the column name
3565 -- feedback - Y or N to indicate whether a count of rows should automaticall
3566 -- be printed at the end of the output. (Default = Y)
3567 -- max rows - Maximum number of rows to output. NULL or ZERO indicates
3568 -- unlimited. (Default = NULL)
3569 -- Returns:
3570 -- The function returns the number of rows selected.
3571 -- If there is an error then the function returns null.
3572 -- Output:
3573 -- Displays the output of the SQL statement as text. The only difference
3574 -- between this and display SQL is that this will print a Title or
3575 -- heading statement prior to the actual sql output.
3576 -- Examples:
3577 -- declare
3578 -- num_rows number;
3579 -- sqltxt varchar2(2000);
3580 -- disp_lengths lengths;
3581 -- col_headers headers;
3582 -- begin
3583 -- sqltxt := 'Select segment1, project_type, project_id '||
3584 -- 'from pa_projects_all'
3585 -- disp_lengths := lengths(20,15,8);
3586 -- col_headers := headers('Project Number', 'Project Type', null);
3587 --
3588 -- num_rows := Run_SQL('All Projects', sqltxt, disp_lengths, col_headers);
3589 -- tab0Print(to_char(num_rows)||' rows selected');
3590 -- /* or */
3591 -- num_rows := Run_SQL('All Projects', sqltxt, disp_lengths);
3592 -- /* or */
3593 -- Run_SQL('All Projects', sqltxt, disp_lengths, col_headers);
3594 -- /* or */
3595 -- Run_SQL('All Projects', sqltxt, disp_lengths);
3596 -- end;
3597
3598 function Run_SQL_text(p_title varchar2,
3599 p_sql_statement varchar2,
3600 p_disp_lengths lengths)
3601 return number is
3602 begin
3603 SectionPrint(p_title);
3604 BRPrint;
3605 return(Display_SQL(p_sql_statement , p_disp_lengths));
3606 end Run_SQL_text;
3607
3608 function Run_SQL_text(p_title varchar2,
3609 p_sql_statement varchar2,
3610 p_disp_lengths lengths,
3611 p_headers headers)
3612 return number is
3613 begin
3614 SectionPrint(p_title);
3615 BRPrint;
3616 return(Display_SQL(p_sql_statement , p_disp_lengths, p_headers));
3617 end Run_SQL_text;
3618
3619 function Run_SQL_text(p_title varchar2,
3620 p_sql_statement varchar2,
3621 p_disp_lengths lengths,
3622 p_headers headers,
3623 p_feedback varchar2)
3624 return number is
3625 begin
3626 SectionPrint(p_title);
3627 BRPrint;
3628 return(Display_SQL(p_sql_statement, p_disp_lengths, p_headers, p_feedback));
3629 end Run_SQL_text;
3630
3631 function Run_SQL_text(p_title varchar2,
3632 p_sql_statement varchar2,
3633 p_disp_lengths lengths,
3634 p_headers headers,
3635 p_max_rows number)
3636 return number is
3637 begin
3638 SectionPrint(p_title);
3639 BRPrint;
3640 return(Display_SQL(p_sql_statement , p_disp_lengths, p_headers,
3641 'Y',p_max_rows));
3642 end Run_SQL_text;
3643
3644 function Run_SQL_text(p_title varchar2,
3645 p_sql_statement varchar2,
3646 p_disp_lengths lengths,
3647 p_headers headers,
3648 p_feedback varchar2,
3649 p_max_rows number)
3650 return number is
3651 begin
3652 SectionPrint(p_title);
3653 BRPrint;
3654 return(Display_SQL(p_sql_statement , p_disp_lengths, p_headers,
3655 p_feedback,p_max_rows));
3656 end Run_SQL_text;
3657
3658 procedure Run_SQL_text(p_title varchar2,
3659 p_sql_statement varchar2,
3660 p_disp_lengths lengths) is
3661 dummy number;
3662 begin
3663 SectionPrint(p_title);
3664 BRPrint;
3665 dummy := Display_SQL(p_sql_statement , p_disp_lengths);
3666 end Run_SQL_text;
3667
3668 procedure Run_SQL_text(p_title varchar2,
3669 p_sql_statement varchar2,
3670 p_disp_lengths lengths,
3671 p_headers headers) is
3672 dummy number;
3673 begin
3674 SectionPrint(p_title);
3675 BRPrint;
3676 dummy := Display_SQL(p_sql_statement , p_disp_lengths, p_headers);
3677 end Run_SQL_text;
3678
3679 procedure Run_SQL_text(p_title varchar2,
3680 p_sql_statement varchar2,
3681 p_disp_lengths lengths,
3682 p_headers headers,
3683 p_feedback varchar2) is
3684 dummy number;
3685 begin
3686 SectionPrint(p_title);
3687 BRPrint;
3688 dummy := Display_SQL(p_sql_statement , p_disp_lengths, p_headers,
3689 p_feedback);
3690 end Run_SQL_text;
3691
3692 procedure Run_SQL_text(p_title varchar2,
3693 p_sql_statement varchar2,
3694 p_disp_lengths lengths,
3695 p_headers headers,
3696 p_max_rows number) is
3697 dummy number;
3698 begin
3699 SectionPrint(p_title);
3700 BRPrint;
3701 dummy := Display_SQL(p_sql_statement , p_disp_lengths, p_headers,
3702 'Y', p_max_rows);
3703 end Run_SQL_text;
3704
3705 procedure Run_SQL_text(p_title varchar2,
3706 p_sql_statement varchar2,
3707 p_disp_lengths lengths,
3708 p_headers headers,
3709 p_feedback varchar2,
3710 p_max_rows number) is
3711 dummy number;
3712 begin
3713 SectionPrint(p_title);
3714 BRPrint;
3715 dummy := Display_SQL(p_sql_statement , p_disp_lengths, p_headers,
3716 p_feedback, p_max_rows);
3717 end Run_SQL_text;
3718
3719 /*
3720 Procedure Name: Display_Table
3721 Usage:
3722 Display_Table('Table Name', 'Heading', 'Where Clause',
3723 'Order By', 'Long Flag');
3724 Parameters:
3725 Table Name - Any Valid Table or View
3726 Heading - Text String to for heading the output
3727 Where Clause - Where clause to apply to the table dump
3728 Order By - Order By clause to apply to the table dump
3729 Long Flag - 'Y' or 'N' - If set to 'N' then this will not
3730 output any LONG columns
3731 Output:
3732 Displays the output of the 'select * from table' as an HTML table.
3733 Examples:
3734 begin
3735 Display_Table('AR_SYSTEM_PARAMETERS_ALL', 'AR Parameters',
3736 'Where Org_id <> -3113'
3737 , 'order by org_id, set_of_books_id', 'N');
3738 end;
3739
3740 procedure Display_Table_text (p_table_name varchar2,
3741 p_table_alias varchar2,
3742 p_where_clause varchar2,
3743 p_order_by_clause varchar2 default null,
3744 p_display_longs varchar2 default 'Y') is
3745 dummy number;
3746 hold_char varchar(1) := null;
3747 begin
3748 if p_where_clause is not null then
3749 hold_char := l_newline;
3750 end if;
3751 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')
3752 , nvl(p_table_alias, p_table_name)
3753 , p_display_longs);
3754 end Display_Table_text;
3755
3756 Function Name: Display_Table
3757 Usage:
3758 a_number := Display_Table('Table Name', 'Heading', 'Where Clause',
3759 'Order By', 'Long Flag');
3760 Parameters:
3761 Table Name - Any Valid Table or View
3762 Heading - Text String to for heading the output
3763 Where Clause - Where clause to apply to the table dump
3764 Order By - Order By clause to apply to the table dump
3765 Long Flag - 'Y' or 'N' - If set to 'N' then this will not output
3766 any LONG columns
3767 Output:
3768 Displays the output of the 'select * from table' as an HTML table.
3769 Returns:
3770 Number of rows displayed.
3771 Examples:
3772 declare
3773 num_rows number;
3774 begin
3775 num_rows := Display_Table('AR_SYSTEM_PARAMETERS_ALL',
3776 'AR Parameters', 'Where Org_id <> -3113',
3777 'order by org_id, set_of_books_id', 'N');
3778 end;
3779
3780 function Display_Table_text (p_table_name varchar2,
3781 p_table_alias varchar2,
3782 p_where_clause varchar2,
3783 p_order_by_clause varchar2 default null,
3784 p_display_longs varchar2 default 'Y') return number is
3785 begin
3786 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')
3787 , nvl(p_table_alias, p_table_name)
3788 , p_display_longs));
3789 end Display_Table_text;
3790
3791 */
3792
3793 -- Function Name: Get_DB_Apps_Version
3794 -- Description:
3795 -- Finds the applications version and sets the global variable
3796 -- g_appl_version to the string value 10.7, 11.0, or 11.5 for
3797 -- use throughout the script where branching due to applications
3798 -- release may be necessary.
3799 -- Usage:
3800 -- a_string := Get_DB_Apps_Version;
3801 -- Parameters:
3802 -- None
3803 -- Output:
3804 -- None
3805 -- Examples:
3806 -- begin
3807 -- Tab0Print('Applications Version: '||Get_DB_Apps_Version);
3808 -- end;
3809
3810 function Get_DB_Apps_Version_text return varchar2 is
3811 l_appsver fnd_product_groups.release_name%type := null;
3812 begin
3813 select release_name into l_appsver from fnd_product_groups;
3814 g_appl_version := substr(l_appsver,1,4);
3815 return(l_appsver);
3816 end;
3817
3818
3819
3820 -- Procedure Name: Show_Header
3821 -- Usage:
3822 -- Show_Header('Note Number', 'Title');
3823 -- Parameters:
3824 -- Note Number - Any Valid Metalink Note Number
3825 -- Title - Text string to go beside the note link
3826 -- Output:
3827 -- Displays Standard Header Information
3828 -- Examples:
3829 -- begin
3830 -- Show_Header('139684.1',
3831 -- 'Oracle Applications Current Patchsets Comparison to applptch.txt');
3832 -- end;
3833
3834 procedure Show_Header_text(p_note varchar2, p_title varchar2) is
3835 l_instance_name varchar2(16) := null;
3836 l_host_name varchar2(64) := null;
3837 l_language varchar2(512):= null;
3838 l_version varchar2(17) := null;
3839 l_org_name hr_all_organization_units.name%type;
3840 begin
3841 select instance_name, host_name, version
3842 into l_instance_name, l_host_name, l_version
3843 from v$instance;
3844
3845 begin
3846 select upper(value) into l_language
3847 from v$parameter
3848 where name = 'nls_language';
3849 exception when others then
3850 l_language := null;
3851 end;
3852 SectionPrint('System Information');
3853 line_out('Machine: '|| l_host_name);
3854 line_out('Date Run: '|| to_char(sysdate,'MM-DD-YYYY HH24:MI:SS'));
3855 line_out('DB Info: SID: '||l_instance_name||' Version: '||l_version);
3856 line_out('DB Language: '||l_language);
3857 line_out('Apps Version: '|| Get_DB_Apps_Version);
3858 if g_org_id is not null then
3859 begin
3860 select name into l_org_name
3861 from hr_all_organization_units
3862 where organization_id = g_org_id;
3863 exception when others then
3864 l_org_name := null;
3865 end;
3866 if l_org_name is not null then
3867 line_out('Operating Unit: '||l_org_name||' (ID='||to_char(g_org_id)||')');
3868 end if;
3869 end if;
3870 BRPrint;
3871 if p_note is not null then
3872 if p_title is not null then
3873 line_out('Note: '||p_note||' - '||p_title);
3874 else
3875 line_out('Note: '||p_note);
3876 end if;
3877 else
3878 if p_title is not null then
3879 line_out('Title: '||p_title);
3880 end if;
3881 end if;
3882 end Show_Header_text;
3883
3884 -- Procedure Name: Show_Footer
3885 -- Usage:
3886 -- Show_Footer;
3887 -- Show_Footer('Script Description','Script Header');
3888 -- Parameters:
3889 -- Script Description - Description of the script (not used)
3890 -- Script Header - Header/version information for the script (not used)
3891 -- These parameters are allowed so that calls for the HTML API will
3892 -- work in the text API as well. They are not used for anything.
3893 -- Output:
3894 -- Displays Standard Footer
3895 -- Examples:
3896 -- begin
3897 -- Show_Footer;
3898 -- Show_Footer('My Script','$Header: jtfdiagcoreapi_b.pls 120.15 2011/04/28 11:14:31 kbnair ship $');
3899 -- end;
3900
3901 procedure Show_Footer_text is
3902 begin
3903 line_out('Please provide feedback regarding the usefulness of this test '||
3904 'and/or tool');
3905 line_out('to [email protected]. We appreciate your '||
3906 'feedback, however,');
3907 line_out('there will be no replies to feedback emails. For '||
3908 'support issues, please log');
3909 line_out('an iTar (Service Request).');
3910 end Show_Footer_text;
3911
3912 procedure Show_Footer_text(p_dummy1 varchar2, p_dummy2 varchar2) is
3913 begin
3914 Show_Footer;
3915 end Show_Footer_text;
3916
3917 -- Procedure Name: Show_Link
3918 -- Usage:
3919 -- Show_Link('Note #');
3920 -- Output:
3921 -- Displays A link to a Metalink Note
3922 -- Examples:
3923 -- begin
3924 -- Show_Link('139684.1');
3925 -- end;
3926
3927 procedure Show_Link_text(p_note varchar2) is
3928 begin
3929 line_out('See Note: '||p_note);
3930 end Show_Link_text;
3931
3932 -- Procedure Name: Show_Link
3933 -- Usage:
3934 -- Show_Link('URL', 'Name');
3935 -- Output:
3936 -- Displays A link to a URL using the Name Parameter
3937 -- Examples:
3938 -- begin
3939 -- Show_Link('http://metalink.us.oracle.com', 'Metalink');
3940 -- end;
3941
3942 procedure Show_Link_text(p_link varchar2, p_link_name varchar2 ) is
3943 begin
3944 line_out('See '||p_link_name||' at '||p_link);
3945 end Show_Link_text;
3946
3947 -- Function Name: Get_Package_Version
3948 -- Usage:
3949 -- a_varchar := Get_Package_Version ('Object Type', 'Schema', 'Package Name');
3950 -- Returns:
3951 -- The version of the package or spec
3952 -- Examples:
3953 -- declare
3954 -- spec_ver varchar2(20);
3955 -- body_ver varchar2(20);
3956 -- begin
3957 -- spec_ver := Get_Package_Version('PACKAGE','APPS','ARH_ADDR_PKG');
3958 -- body_ver := Get_Package_Version('PACKAGE BODY','APPS','ARH_ADDR_PKG');
3959 -- end;
3960
3961 function Get_Package_Version_text (p_type varchar2, p_schema varchar2,
3962 p_package varchar2)
3963 return varchar2 is
3964 hold_version varchar2(50);
3965 begin
3966 select substr(z.text, instr(z.text,'$Header')+10, 40)
3967 into hold_version
3968 from all_source z
3969 where z.name = p_package
3970 and z.type = p_type
3971 and z.owner = p_schema
3972 and z.text like '%$Header%';
3973 hold_version := substr(hold_version, instr(hold_version,' ')+1, 50);
3974 hold_version := substr(hold_version, 1, instr(hold_version,' ')-1);
3975 return (hold_version);
3976 exception
3977 when no_data_found then
3978 ErrorPrint(p_type||' '||p_package||' owned by '||p_schema||
3979 ' does not exist');
3980 ActionPrint('Verify that this object is valid for your version of '||
3981 'applications and that the owner indicated is correct');
3982 return(null);
3983 when others then
3984 ErrorPrint(sqlerrm||' occured in Get_Package_Version');
3985 ActionPrint('Please provide this information to your support '||
3986 'representative');
3987 raise;
3988 end Get_Package_Version_text;
3989
3990 -- Function Name: Get_Package_Spec
3991 -- Usage:
3992 -- a_varchar := Get_Package_Spec('Package Name');
3993 -- Returns:
3994 -- The version of the package specification in the APPS schema
3995 -- Examples:
3996 -- declare
3997 -- spec_ver varchar2(20);
3998 -- begin
3999 -- spec_ver := Get_Package_Spec('ARH_ADDR_PKG');
4000 -- end;
4001
4002 function Get_Package_Spec_text(p_package varchar2) return varchar2 is
4003 apps_schema_name varchar2(30);
4004 begin
4005 apps_schema_name := Get_Apps_Schema_Name();
4006 return Get_Package_Version('PACKAGE',apps_schema_name,p_package);
4007 end Get_Package_Spec_text;
4008
4009 -- Function Name: Get_Package_Body
4010 -- Usage:
4011 -- a_varchar := Get_Package_Body('Package Name');
4012 -- Returns:
4013 -- The version of the package body in the APPS schema
4014 -- Examples:
4015 -- declare
4016 -- body_ver varchar2(20);
4017 -- begin
4018 -- body_ver := Get_Package_Body('ARH_ADDR_PKG');
4019 -- end;
4020
4021 function Get_Package_Body_text(p_package varchar2) return varchar2 is
4022 apps_schema_name varchar2(30);
4023 begin
4024 apps_schema_name := Get_Apps_Schema_Name();
4025 return Get_Package_Version('PACKAGE BODY',apps_schema_name,p_package);
4026 end Get_Package_Body_text;
4027
4028 -- Procedure Name: Display_Profiles
4029 -- Usage:
4030 -- Display_Profiles(application_id, 'profile short name');
4031 -- Parameters:
4032 -- application_id - if provided will limit output to profile options
4033 -- associated with this application_id
4034 -- profile_short_name - system name of the profile option. Will limit output
4035 -- to the settings for this specific profile option
4036 -- Output:
4037 -- Displays all Profile settings for the application or specific profile
4038 -- option at all levels for which it is set.
4039 -- Examples:
4040 -- begin
4041 -- Display_Profiles(275,null);
4042 -- Display_Profiles(null, 'PA_DEBUG_MODE');
4043 -- end;
4044
4045
4046 procedure Display_Profiles_text (p_application_id varchar2, p_short_name varchar2) is
4047 cursor get_profile_options is
4048 select substr(ot.user_profile_option_name,1,45) user_profile_option_name,
4049 substr(o.profile_option_name,1,30) profile_option_name,
4050 decode(v.level_id,10001, 'Site', 10002, 'Appl',
4051 10003, 'Resp',
4052 10004, 'User') lev,
4053 substr(decode(v.level_id,
4054 10001, ' ',
4055 10002, a.application_name,
4056 10003, r.responsibility_name,
4057 10004, u.user_name),1,20) lev_value,
4058 v.profile_option_value opt_value
4059 from fnd_profile_option_values v,
4060 fnd_profile_options o,
4061 fnd_profile_options_tl ot,
4062 fnd_application_tl a,
4063 fnd_responsibility_tl r,
4064 fnd_user u
4065 where o.application_id = nvl(p_application_id, o.application_id)
4066 and o.profile_option_name = nvl(p_short_name, o.profile_option_name)
4067 and v.LEVEL_VALUE =
4068 decode(level_id, 10004, u.user_id, 10003, r.responsibility_id,
4069 10002, a.application_id,10001,0)
4070 and v.profile_option_id = o.profile_option_id
4071 and v.application_id = o.application_id
4072 and a.application_id (+) = v.level_value
4073 and r.responsibility_id (+) = v.level_value
4074 and u.user_id (+) = v.level_value
4075 and ot.profile_option_name = o.profile_option_name
4076 and nvl(ot.language,'US') = nvl(USERENV('LANG'),'US')
4077 and sysdate between nvl(o.start_date_active, sysdate) and
4078 nvl(o.end_date_active,sysdate)
4079 and v.profile_option_value is not null
4080 order by ot.user_profile_option_name, v.level_id,
4081 decode(level_id,10001, 'Site', 10002, a.application_name, 10003,
4082 r.responsibility_name, 10004, u.user_name);
4083 max_user_opt integer;
4084 max_opt integer;
4085 max_lev integer;
4086 max_lev_value integer;
4087 max_opt_value integer;
4088 begin
4089 select max(length(substr(ot.user_profile_option_name,1,45))) max_u_length,
4090 max(length(substr(o.profile_option_name,1,30))) max_length,
4091 max(length(decode(v.level_id,10001, 'Site', 10002, 'Appl',
4092 10003, 'Resp',
4093 10004, 'User'))),
4094 max(length(substr(decode(v.level_id,
4095 10001, ' ',
4096 10002, a.application_name,
4097 10003, r.responsibility_name,
4098 10004, u.user_name),1,20))),
4099 max(length(v.profile_option_value))
4100 into max_user_opt, max_opt, max_lev, max_lev_value, max_opt_value
4101 from fnd_profile_option_values v,
4102 fnd_profile_options o,
4103 fnd_profile_options_tl ot,
4104 fnd_application_tl a,
4105 fnd_responsibility_tl r,
4106 fnd_user u
4107 where o.application_id = nvl(p_application_id, o.application_id)
4108 and o.profile_option_name = nvl(p_short_name, o.profile_option_name)
4109 and v.LEVEL_VALUE =
4110 decode(level_id, 10004, u.user_id, 10003, r.responsibility_id,
4111 10002, a.application_id,10001,0)
4112 and v.profile_option_id = o.profile_option_id
4113 and v.application_id = o.application_id
4114 and a.application_id (+) = v.level_value
4115 and r.responsibility_id (+) = v.level_value
4116 and u.user_id (+) = v.level_value
4117 and ot.profile_option_name = o.profile_option_name
4118 and nvl(ot.language,'US') = nvl(USERENV('LANG'),'US')
4119 and sysdate between nvl(o.start_date_active, sysdate) and
4120 nvl(o.end_date_active,sysdate)
4121 and v.profile_option_value is not null;
4122
4123 line_out(rpad('User Profile Name',greatest(max_user_opt,17),' ')||' '||
4124 rpad('System Name',greatest(max_opt,11),' ')||' '||
4125 rpad('Level',greatest(max_lev,5),' ')||' '||
4126 rpad('Level Value',greatest(max_lev_value,11),' ')||' '||
4127 rpad('Opt Value',greatest(max_opt_value,9),' '));
4128 line_out(rpad('-',greatest(max_user_opt,17),'-')||' '||
4129 rpad('-',greatest(max_opt,11),'-')||' '||
4130 rpad('-',greatest(max_lev,5),'-')||' '||
4131 rpad('-',greatest(max_lev_value,11),'-')||' '||
4132 rpad('-',greatest(max_opt_value,9),'-'));
4133 for opt in get_profile_options loop
4134 line_out(rpad(opt.user_profile_option_name,
4135 greatest(max_user_opt,17),' ')||' '||
4136 rpad(opt.profile_option_name,greatest(max_opt,11),' ')||' '||
4137 rpad(opt.lev,greatest(max_lev,5),' ')||' '||
4138 rpad(opt.lev_value,greatest(max_lev_value,11),' ')||' '||
4139 rpad(opt.opt_value,greatest(max_opt_value,9),' '));
4140 end loop;
4141 exception when others then
4142 ErrorPrint('Unexpected error: '||sqlerrm||' occured in Display_Profiles');
4143 ActionErrorPrint('Report the above error message to your support '||
4144 'representative');
4145 end Display_Profiles_text;
4146
4147 -- Procedure Name: Get_Profile_Option
4148 -- Usage:
4149 -- a_varchar := Get_Profile_Option('Short Name');
4150 -- Parameters:
4151 -- Short Name - The Short Name of the Profile Option
4152 -- Returns:
4153 -- The value of the profile option based on the user, responsibility,
4154 -- and application context.
4155 -- If Set_Client has not been run successfully then
4156 -- it will return the site level setting.
4157 -- Output:
4158 -- None
4159 -- Examples:
4160 -- declare
4161 -- prof_value varchar2(150);
4162 -- begin
4163 -- prof_value := Get_Profile_Option('AR_ALLOW_OVERAPPLICATION_IN_LOCKBOX')
4164 -- end;
4165
4166 function Get_Profile_Option_text (p_profile_option varchar2) return varchar2 is
4167 begin
4168 return FND_PROFILE.VALUE(p_profile_option);
4169 end;
4170
4171 -- Procedure Name: Set_Org
4172 -- Usage:
4173 -- Set_Org(org_id);
4174 -- Parameters:
4175 -- Org_ID - Character string containing the id of the organization to set.
4176 -- Output:
4177 -- None
4178 -- Examples:
4179 -- begin
4180 -- Set_Org('204');
4181 -- end;
4182
4183 procedure Set_Org_text (p_org_id Varchar2) is
4184 begin
4185 fnd_client_info.set_org_context(p_org_id);
4186 end Set_Org_text;
4187
4188 procedure Set_Org_text (p_org_id number) is
4189 l_org_id varchar2(10);
4190 begin
4191 l_org_id := to_char(p_org_id);
4192 fnd_client_info.set_org_context(l_org_id);
4193 end Set_Org_text;
4194
4195 -- Procedure Name: Set_Client
4196 -- Description:
4197 -- Validates user_name, responsibility_id, and application_id parameters
4198 -- If valid it initializes the session (which results in the operating
4199 -- unit being set for the session as well. Also sets the global variables
4200 -- g_user_id, g_resp_id, g_appl_id, and g_org_id which can then be used
4201 -- throughout the script.
4202 -- Usage:
4203 -- Set_Client(UserName, Responsibility_ID);
4204 -- Set_Client(UserName, Responsibility_ID, Application_ID);
4205 -- Set_Client(UserName, Responsibility_ID, Application_ID, SecurityGrp_ID);
4206 -- Parameters:
4207 -- UserName - The Name of the Applications User
4208 -- Responsibility_ID - Any Valid Responsibility ID
4209 -- Application_ID - Any Valid Application ID (275=PA) If no value
4210 -- provided, attempt to obtain from responsibility_id
4211 -- SecurityGrp_ID - A valid security_group_id
4212 -- Examples:
4213 -- begin
4214 -- Set_Client('JOEUSER',50719, 222);
4215 -- end;
4216
4217 procedure Set_Client_text(p_user_name varchar2, p_resp_id number,
4218 p_app_id number, p_sec_grp_id number) is
4219 l_cursor integer;
4220 l_num_rows integer;
4221 l_user_name fnd_user.user_name%type;
4222 l_user_id number;
4223 l_app_id number;
4224 l_counter integer;
4225 l_appl_vers fnd_product_groups.release_name%type;
4226 sqltxt varchar2(2000);
4227 inv_user exception;
4228 inv_resp exception;
4229 inv_app exception;
4230 no_app exception;
4231 begin
4232 l_user_name := upper(p_user_name);
4233 begin
4234 select user_id into l_user_id
4235 from fnd_user where user_name = l_user_name;
4236 exception
4237 when others then
4238 raise inv_user;
4239 end;
4240 l_appl_vers := get_db_apps_version; -- sets g_appl_version
4241 if g_appl_version = '11.0' or g_appl_version = '10.7' then
4242 sqltxt := 'select rg.application_id '||
4243 'from fnd_user_responsibility rg '||
4244 'where rg.responsibility_id = '||to_char(p_resp_id)||' '||
4245 'and rg.user_id = '||to_char(l_user_id);
4246 elsif g_appl_version = '11.5' then
4247 sqltxt := 'select rg.responsibility_application_id '||
4248 'from fnd_user_resp_groups rg '||
4249 'where rg.responsibility_id = '||to_char(p_resp_id)||' '||
4250 'and rg.user_id = '||to_char(l_user_id);
4251 end if;
4252 begin
4253 l_cursor := dbms_sql.open_cursor;
4254 dbms_sql.parse(l_cursor, sqltxt, dbms_sql.native);
4255 dbms_sql.define_column(l_cursor, 1, l_app_id);
4256 l_num_rows := dbms_sql.execute_and_fetch(l_cursor, TRUE);
4257 dbms_sql.column_value(l_cursor, 1, l_app_id);
4258 dbms_sql.close_cursor(l_cursor);
4259
4260 exception
4261 when no_data_found then
4262 raise inv_resp;
4263 when too_many_rows then
4264 if p_app_id is null then
4265 raise no_app;
4266 else
4267 dbms_sql.close_cursor(l_cursor);
4268 l_cursor := dbms_sql.open_cursor;
4269 dbms_sql.parse(l_cursor, sqltxt, dbms_sql.native);
4270 dbms_sql.define_column(l_cursor, 1, l_app_id);
4271 l_num_rows := dbms_sql.execute(l_cursor);
4272 while dbms_sql.fetch_rows(l_cursor) > 0 loop
4273 dbms_sql.column_value(l_cursor, 1, l_app_id);
4274 if l_app_id = p_app_id then
4275 exit;
4276 end if;
4277 end loop;
4278 dbms_sql.close_cursor(l_cursor);
4279 if l_app_id <> p_app_id then
4280 raise inv_app;
4281 end if;
4282 end if;
4283 end;
4284 l_cursor := dbms_sql.open_cursor;
4285 if g_appl_version = '11.5' then
4286 sqltxt := 'begin '||
4287 'fnd_global.apps_initialize(:user, :resp, '||
4288 ':appl, :secg); '||
4289 'end; ';
4290 dbms_sql.parse(l_cursor, sqltxt, dbms_sql.native);
4291 dbms_sql.bind_variable(l_cursor,':user',l_user_id);
4292 dbms_sql.bind_variable(l_cursor,':resp',p_resp_id);
4293 dbms_sql.bind_variable(l_cursor,':appl',l_app_id);
4294 dbms_sql.bind_variable(l_cursor,':secg',p_sec_grp_id);
4295 else
4296 sqltxt := 'begin '||
4297 'fnd_global.apps_initialize(:user,:resp,:appl); '||
4298 'end; ';
4299 dbms_sql.parse(l_cursor, sqltxt, dbms_sql.native);
4300 dbms_sql.bind_variable(l_cursor,':user',l_user_id);
4301 dbms_sql.bind_variable(l_cursor,':resp',p_resp_id);
4302 dbms_sql.bind_variable(l_cursor,':appl',l_app_id);
4303 end if;
4304 l_num_rows := dbms_sql.execute(l_cursor);
4305 g_user_id := l_user_id;
4306 g_resp_id := p_resp_id;
4307 g_appl_id := l_app_id;
4308 g_org_id := Get_Profile_Option('ORG_ID');
4309 exception
4310 when inv_user then
4311 ErrorPrint('Unable to initialize client due to invalid username: '||
4312 l_user_name);
4313 ActionErrorPrint('Set_Client has been passed an invalid username '||
4314 'parameter. Please correct this parameter if possible, and if not, '||
4315 'inform your support representative.');
4316 raise;
4317 when inv_resp then
4318 ErrorPrint('Unable to initialize client due to invalid responsibility '||
4319 'ID: '||to_char(p_resp_id));
4320 ActionErrorPrint('Set_Client has been passed an invalid responsibility '||
4321 'ID parameter. This responsibility_id either does not exist or has not '||
4322 'been assigned to the user ('||l_user_name||'). Please correct these '||
4323 'parameter values if possible, and if not inform your support '||
4324 'representative.');
4325 raise;
4326 when inv_app then
4327 ErrorPrint('Unable to initialize client due to invalid application ID: '||
4328 to_char(p_app_id));
4329 ActionErrorPrint('Set_Client has been passed an invalid application ID '||
4330 'parameter. This application either does not exist or is not '||
4331 'associated with the responsibility id ('||to_char(p_resp_id)||'). '||
4332 'Please correct this parameter value if possible, and if not inform '||
4333 'your support representative.');
4334 raise;
4335 when no_app then
4336 ErrorPrint('Set_Client was unable to obtain an application ID to '||
4337 'initialize client settings');
4338 ActionErrorPrint('No application_id was supplied and Set_Client was '||
4339 'unable to determine this from the responsibility because multiple '||
4340 'responsibilities with the same responsibility_id have been assigned '||
4341 'to this user ('||l_user_name||').');
4342 raise;
4343 when others then
4344 ErrorPrint(sqlerrm||' occured in Set_Client');
4345 ActionErrorPrint('Please inform your support representative');
4346 raise;
4347 end Set_Client_text;
4348
4349 procedure Set_Client_text(p_user_name varchar2, p_resp_id number) is
4350 begin
4351 Set_Client(p_user_name, p_resp_id, null, null);
4352 end Set_Client_text;
4353
4354 procedure Set_Client_text(p_user_name varchar2, p_resp_id number,
4355 p_app_id number ) is
4356 begin
4357 Set_Client(p_user_name, p_resp_id, p_app_id, null);
4358 end Set_Client_text;
4359
4360 /*
4361 -- OBSOLETED API SINCE THIS IS NOT COMPLIANT WITH GSCC STANDARD
4362 -- FILE.SQL.6 WHICH DOES NOT PERMIT USING OF SCHEMA
4363 -- NAMES WITHIN PLSQL CODE
4364
4365 -- Procedure Name: Get_DB_Patch_List
4366 -- Usage:
4367 -- Get_DB_Patch_List('heading', 'short name', 'bug number', 'start date');
4368 -- Parameters:
4369 -- Heading = Title to go at the top of TABLE or TEXT outputs
4370 -- Short Name = Limits to Bugs that match this expression for the
4371 -- Applications Production Short Name (LIKE)
4372 -- Bug Number = Limits to bugs that match this expression (LIKE)
4373 -- Start Date = Limits to Bugs applied after this date
4374 -- Output:
4375 -- FORMATTED TEXT listing of patches applied is displayed
4376 -- Examples:
4377 -- begin
4378 -- Get_DB_Patch_List('AD Patches Applied Since 03-MAR-2002',
4379 -- 'AD','%', '03-MAR-2002');
4380 -- end;
4381
4382 procedure Get_DB_Patch_List_text (
4383 p_heading varchar2 default 'AD_BUGS'
4384 , p_app_short_name varchar2 default '%'
4385 , p_bug_number varchar2 default '%'
4386 , p_start_date date default to_date(olddate,'MM-DD-YYYY')) is
4387 l_appl_version fnd_product_groups.release_name%type;
4388 l_disp_lengths lengths;
4389 l_headers headers;
4390 l_counter integer;
4391 sqltxt varchar2(32767);
4392 begin
4393 if g_appl_version is null then
4394 l_appl_version := get_db_apps_version; -- sets g_appl_version
4395 end if;
4396 SectionPrint(p_heading);
4397 if g_appl_version = '11.5' then
4398
4399 select count(*) into l_counter from all_tables z
4400 where z.table_name = 'AD_BUGS'
4401 and z.owner = 'APPLSYS';
4402
4403 if l_counter = 0 then
4404 WarningPrint('The function Get_DB_Patch_List is not available');
4405 ActionPrint('If the table AD_BUGS does not exist in the database you '||
4406 'must review the applptch.txt file in the APPL_TOP directory '||
4407 'for patch application information. This functionality is available '||
4408 'with release 11.5.5 and above or 11.5 with AD patchset E or higher.');
4409 else
4410 BRPrint;
4411 l_headers := headers('Patch Number','Creation Date', 'Appl Short Name');
4412 l_disp_lengths := lengths(9, 11, 5);
4413 sqltxt := 'select bug_number, creation_date, application_short_name ' ||
4414 'from ad_bugs '||
4415 'where upper(application_short_name) like '''||
4416 p_app_short_name||''''||
4417 'and bug_number like '''||p_bug_number||''''||
4418 'and creation_date >= nvl(to_date('''||
4419 to_char(p_start_date,'MM-DD-YYYY')||
4420 ''',''MM-DD-YYYY''),creation_date)';
4421 display_sql(sqltxt,l_disp_lengths, l_headers);
4422 BRPrint;
4423 end if;
4424 else
4425 WarningPrint('The Get_DB_Patch_List function is only available on '||
4426 'applications 11.5');
4427 ActionPrint('For release 11.0 and 10.7 review the file applptch.txt '||
4428 'in your APPL_TOP directory');
4429 end if;
4430 end Get_DB_Patch_List_text;
4431 */
4432
4433 -- Function Name: Get_RDBMS_Header
4434 -- Usage:
4435 -- Get_RDBMS_Header;
4436 -- Returns:
4437 -- Version of the Database from v$version
4438 -- Examples:
4439 -- declare
4440 -- RDBMS_Ver := v$version.banner%type;
4441 -- begin
4442 -- RDBMS_Ver := Get_RDBMS_Header;
4443 -- end;
4444
4445 Function Get_RDBMS_Header_text return varchar2 is
4446 l_hold_name v$database.name%type;
4447 l_DB_Ver v$version.banner%type;
4448 begin
4449 begin
4450 select name
4451 into l_hold_name
4452 from v$database;
4453 exception
4454 when others then
4455 l_hold_name := 'Not Available';
4456 end;
4457 begin
4458 select banner
4459 into l_DB_Ver
4460 from v$version
4461 where banner like 'Oracle%';
4462 exception
4463 when others then
4464 l_DB_Ver := 'Not Available';
4465 end;
4466 return(l_hold_name || ' - ' || l_DB_Ver);
4467 end Get_RDBMS_Header_text;
4468
4469 -- Function Name: Compare_Pkg_Version
4470 -- Usage:
4471 -- Compare_Pkg_Version('package_name','obj_type','obj_owner', 'outversvar',
4472 -- 'reference_version');
4473 -- Compare_Pkg_Version('package_name','obj_type', 'outversvar',
4474 -- 'reference_version');
4475 -- Parameters:
4476 -- package_name - Name of the package whose version is being checked
4477 -- obj_type - Either BODY or SPEC to determine which piece to check
4478 -- obj_owner - The owner of the package being checked. If null or
4479 -- not supplied the default is APPS.
4480 -- outversvar - A text out variable to hold the actual package version
4481 -- of the package as returned from the database
4482 -- reference_version - A string containing the version to which the
4483 -- package version should be compared (in format ###.##,
4484 -- ie, in a format convertible to a number. As opposed
4485 -- to, for example, 11.5.119, use 115.119.
4486 -- Returns:
4487 -- 'greater' if the version of the object is greater than the reference
4488 -- 'less' if the version of the object is less than the reference
4489 -- 'equal' if the version of the object is equal to the reference
4490 -- 'null' if either the reference or db version is null
4491 -- Examples:
4492 -- declare
4493 -- Comparison_Var varchar2(8);
4494 -- Package_Version varchar2(10);
4495 -- begin
4496 -- Comparison_Var := Compare_Pkg_Version('PA_UTILS2','BODY','APPS',
4497 -- Package_Version, '115.13');
4498 -- Comparison_Var := Compare_Pkg_Version('PA_UTILS2','BODY',
4499 -- Package_Version, '115.13');
4500 -- end;
4501
4502 Function Compare_Pkg_Version_text(
4503 package_name in varchar2,
4504 object_type in varchar2,
4505 object_owner in varchar2,
4506 version_str in out NOCOPY varchar2,
4507 compare_version in varchar2)
4508 return varchar2 is
4509 vers_line varchar2(1000);
4510 l_object_owner varchar2(250);
4511 db_vers_key number;
4512 in_vers_key number;
4513 begin
4514 l_object_owner := object_owner;
4515 if l_object_owner is null then
4516 l_object_owner := Get_Apps_Schema_Name();
4517 end if;
4518 in_vers_key :=
4519 to_number(substr(compare_version,instr(compare_version,'.')+1));
4520 if upper(object_type) = 'BODY' then
4521 select z.text into vers_line
4522 from dba_source z
4523 where z.name = package_name
4524 and z.owner = l_object_owner
4525 and z.text like '%$Header%'
4526 and z.type = 'PACKAGE BODY';
4527 else
4528 select z.text into vers_line
4529 from dba_source z
4530 where z.name = package_name
4531 and z.owner = l_object_owner
4532 and z.text like '%$Header%'
4533 and z.type = 'PACKAGE';
4534 end if;
4535 vers_line := substr(vers_line,instr(vers_line,'$Header:')+9);
4536 vers_line := ltrim(vers_line);
4537 vers_line := substr(vers_line,1,instr(vers_line,' ',1,2)-1);
4538 vers_line := substr(vers_line,instr(vers_line,' ')+1);
4539 version_str := vers_line;
4540 db_vers_key :=
4541 to_number(substr(vers_line,instr(vers_line,'.')+1));
4542 if db_vers_key < in_vers_key then
4543 return('less');
4544 elsif db_vers_key > in_vers_key then
4545 return('greater');
4546 elsif db_vers_key = in_vers_key then
4547 return('equal');
4548 elsif db_vers_key is null or in_vers_key is null then
4549 return('null');
4550 end if;
4551 exception when others then
4552 ErrorPrint('Unable to verify package version for '||package_name||' ('||
4553 object_type||') -- '||sqlerrm||' occured in Compare_Pkg_Version');
4554 ActionErrorPrint('Contact your support representative and supply the '||
4555 'above error information');
4556 return('null');
4557 end Compare_Pkg_Version_text;
4558
4559 Function Compare_Pkg_Version_text(
4560 package_name in varchar2,
4561 object_type in varchar2,
4562 version_str in out NOCOPY varchar2,
4563 compare_version in varchar2 default null)
4564 return varchar2 is
4565 begin
4566 return(compare_pkg_version(
4567 package_name, object_type, null, version_str,compare_version));
4568 end Compare_Pkg_Version_text;
4569
4570 /*
4571
4572 -- OBSOLETED API SINCE THIS IS NOT COMPLIANT WITH GSCC STANDARD
4573 -- FILE.SQL.6 WHICH DOES NOT PERMIT USING OF SCHEMA
4574 -- NAMES WITHIN PLSQL CODE
4575
4576
4577 -- Procedure Name: Show_Invalids
4578 -- Usage:
4579 -- Show_Invalids('start string','include errors','heading');
4580 -- Parameters:
4581 -- start string = Only return objects beginning with this string (case
4582 -- insensitive)
4583 -- include errors - Y or N to indicate whether to search on and report
4584 -- the errors from ALL_ERRORS for each of the invalid
4585 -- objects found. (DEFAULT = N)
4586 -- heading - An optional heading for the table. If null the heading will
4587 -- be "Invalid Objects (Starting with 'XXX')" where XXX is
4588 -- the start string parameter.
4589 -- Ouput:
4590 -- Will output a list of invalid objects. For PL/SQL program units the
4591 -- file name and version will be displayed. If the 'include errors'
4592 -- parameter is 'Y' a listing of errors associated with the object will
4593 -- be printed.
4594 -- Examples:
4595 -- begin
4596 -- Show_Invalids('PA_');
4597 -- Show_Invalids('GL','N','General Ledger Invalid Objects');
4598 -- end;
4599 Procedure Show_Invalids_text (p_start_string varchar2 default null
4600 , p_include_errors varchar2 default 'N'
4601 , p_heading varchar2 default null) is
4602 l_start_string varchar2(60);
4603 l_file_version varchar2(100);
4604 l_heading varchar2(500);
4605 l_sqltxt varchar2(32767);
4606 l_first_row boolean := true;
4607 l_table_row varchar2(32767);
4608 l_lengths lengths;
4609 l_hdrs headers;
4610 l_rows integer := 0;
4611 l_counter integer := 0;
4612
4613 -- OWNER CHANGE
4614 cursor get_invalids(c_start_string varchar2) is
4615 select o.object_name, o.object_type, o.owner
4616 from all_objects o
4617 where o.status = 'INVALID'
4618 and o.object_name like c_start_string escape '~'
4619 and upper(o.owner) in ('APPS', 'JTF', 'APPLSYS')
4620 order by o.object_name;
4621
4622 cursor get_file_version(
4623 c_obj_name varchar2
4624 , c_obj_type varchar2
4625 , c_obj_owner varchar2) is
4626 select substr(substr(s.text,instr(s.text,'$Header')+9),1,
4627 instr(substr(s.text,instr(s.text,'$Header')+9),' ',1,2)-1) file_vers
4628 from all_source s
4629 where name = c_obj_name
4630 and type = c_obj_type
4631 and owner = c_obj_owner
4632 and text like '%$Header%';
4633
4634 cursor get_errors (
4635 c_obj_name varchar2
4636 , c_obj_type varchar2
4637 , c_obj_owner varchar2) is
4638 select to_char(z.sequence)||') LINE: '||to_char(z.line)||' CHR: '||
4639 to_char(z.position)||' '||text error_row
4640 from all_errors z
4641 where z.name = c_obj_name
4642 and z.type = c_obj_type
4643 and z.owner = c_obj_owner;
4644
4645 begin
4646 l_start_string := upper(replace(p_start_string,'_','~_')) || '%';
4647 if p_heading is null then
4648 l_heading := 'Invalid Objects (Starting with '''||p_start_string||''')';
4649 else
4650 l_heading := p_heading;
4651 end if;
4652 SectionPrint(l_heading);
4653 l_lengths := lengths(35,13,9,20);
4654 l_hdrs := headers('Object Name','Object Type','Owner','Version');
4655
4656 for inv_rec in get_invalids(l_start_string) loop
4657 if l_first_row then
4658 Show_Table_Header(l_hdrs, l_lengths);
4659 l_first_row := false;
4660 end if;
4661 l_table_row :=
4662 rpad(nvl(substr(inv_rec.object_name,1,35),' '),35,' ')||' '||
4663 rpad(nvl(substr(inv_rec.object_type,1,13),' '),13,' ')||' '||
4664 rpad(nvl(substr(inv_rec.owner,1,9),' '),9,' ')||' ';
4665 if inv_rec.object_type like 'PACKAGE%' or
4666 inv_rec.object_type in ('PROCEDURE','FUNCTION') then
4667 open get_file_version(inv_rec.object_name, inv_rec.object_type,
4668 inv_rec.owner);
4669 fetch get_file_version into l_file_version;
4670 if get_file_version%notfound then
4671 l_file_version := null;
4672 end if;
4673 close get_file_version;
4674 else
4675 l_file_version := null;
4676 end if;
4677 l_file_version := rpad(nvl(substr(l_file_version,1,20),' '),20,' ');
4678 l_table_row := l_table_row||l_file_version;
4679 Tab0Print(l_table_row);
4680 l_rows := l_rows + 1;
4681
4682 if p_include_errors = 'Y' then
4683 l_counter := 0;
4684 for err_rec in get_errors(inv_rec.object_name, inv_rec.object_type,
4685 inv_rec.owner) loop
4686 if l_counter = 0 then
4687 Tab1Print('Object Errors:');
4688 end if;
4689 l_counter := l_counter + 1;
4690 Tab1Print(err_rec.error_row);
4691 end loop;
4692 if l_counter > 0 then
4693 BRPrint;
4694 end if;
4695 end if;
4696 end loop
4697 BRPrint;
4698 Tab0Print(to_char(l_rows)||' rows selected');
4699 BRPrint;
4700 exception when others then
4701 ErrorPrint(sqlerrm||' occurred in Show_Invalids');
4702 ActionErrorPrint('Please report this information to your support '||
4703 'representative');
4704 end Show_Invalids_text;
4705 */
4706
4707 ------------------------------------------------------------------------
4708 -- External APIs
4709 -----------------------------------------------------------------------
4710
4711 procedure line_out (text varchar2) IS
4712 BEGIN
4713 IF (JTF_DIAGNOSTIC_ADAPTUTIL.b_html_on) THEN
4714 line_out_html (text);
4715 ELSE
4716 line_out_text (text );
4717 END IF;
4718 END;
4719
4720
4721 procedure Insert_Style_Sheet IS
4722 BEGIN
4723 IF (JTF_DIAGNOSTIC_ADAPTUTIL.b_html_on) THEN
4724 Insert_Style_Sheet_html;
4725 ELSE
4726 -- API currently not implemented for text
4727 null;
4728 END IF;
4729 END;
4730
4731
4732 procedure Insert_HTML(p_text varchar2) IS
4733 BEGIN
4734 IF (JTF_DIAGNOSTIC_ADAPTUTIL.b_html_on) THEN
4735 Insert_HTML_html(p_text);
4736 ELSE
4737 -- API currently not implemented for text
4738 null;
4739 END IF;
4740 END;
4741
4742
4743 procedure ActionErrorPrint(p_text varchar2) IS
4744 BEGIN
4745 IF (JTF_DIAGNOSTIC_ADAPTUTIL.b_html_on) THEN
4746 ActionErrorPrint_html(p_text);
4747 ELSE
4748 ActionErrorPrint_text(p_text);
4749 END IF;
4750 END;
4751
4752
4753
4754
4755 procedure ActionPrint(p_text varchar2) IS
4756 BEGIN
4757 IF (JTF_DIAGNOSTIC_ADAPTUTIL.b_html_on) THEN
4758 ActionPrint_html(p_text);
4759 ELSE
4760 ActionPrint_text(p_text);
4761 END IF;
4762 END;
4763
4764
4765 procedure ActionWarningPrint(p_text varchar2) IS
4766 BEGIN
4767 IF (JTF_DIAGNOSTIC_ADAPTUTIL.b_html_on) THEN
4768 ActionWarningPrint_html(p_text);
4769 ELSE
4770 ActionWarningPrint_text(p_text);
4771 END IF;
4772 END;
4773
4774
4775
4776 procedure WarningPrint(p_text varchar2) IS
4777 BEGIN
4778 IF (JTF_DIAGNOSTIC_ADAPTUTIL.b_html_on) THEN
4779 WarningPrint_html(p_text);
4780 ELSE
4781 WarningPrint_text(p_text);
4782 END IF;
4783 END;
4784
4785
4786 procedure ActionErrorLink(p_txt1 varchar2
4787 , p_note varchar2
4788 , p_txt2 varchar2) IS
4789 BEGIN
4790 IF (JTF_DIAGNOSTIC_ADAPTUTIL.b_html_on) THEN
4791 ActionErrorLink_html(p_txt1, p_note, p_txt2);
4792 ELSE
4793 -- API currently not implemented for text
4794 null;
4795 END IF;
4796 END;
4797
4798
4799 procedure ActionErrorLink(p_txt1 varchar2
4800 , p_url varchar2
4801 , p_link_txt varchar2
4802 , p_txt2 varchar2) IS
4803 BEGIN
4804 IF (JTF_DIAGNOSTIC_ADAPTUTIL.b_html_on) THEN
4805 ActionErrorLink_html(p_txt1,p_url,p_link_txt,p_txt2);
4806 ELSE
4807 -- API currently not implemented for text
4808 null;
4809 END IF;
4810 END;
4811
4812
4813 procedure ActionWarningLink(p_txt1 varchar2
4814 , p_note varchar2
4815 , p_txt2 varchar2) IS
4816 BEGIN
4817 IF (JTF_DIAGNOSTIC_ADAPTUTIL.b_html_on) THEN
4818 ActionWarningLink_html(p_txt1, p_note, p_txt2);
4819 ELSE
4820 -- API currently not implemented for text
4821 null;
4822 END IF;
4823 END;
4824
4825
4826 procedure ActionWarningLink(p_txt1 varchar2
4827 , p_url varchar2
4828 , p_link_txt varchar2
4829 , p_txt2 varchar2) IS
4830 BEGIN
4831 IF (JTF_DIAGNOSTIC_ADAPTUTIL.b_html_on) THEN
4832 ActionWarningLink_html(p_txt1,p_url ,p_link_txt,p_txt2);
4833 ELSE
4834 -- API currently not implemented for text
4835 null;
4836 END IF;
4837 END;
4838
4839
4840 procedure ErrorPrint(p_text varchar2) IS
4841 BEGIN
4842 IF (JTF_DIAGNOSTIC_ADAPTUTIL.b_html_on) THEN
4843 ErrorPrint_html(p_text);
4844 ELSE
4845 ErrorPrint_text(p_text);
4846 END IF;
4847 END;
4848
4849
4850 procedure SectionPrint(p_text varchar2) IS
4851 BEGIN
4852 IF (JTF_DIAGNOSTIC_ADAPTUTIL.b_html_on) THEN
4853 SectionPrint_html(p_text);
4854 ELSE
4855 SectionPrint_text(p_text);
4856 END IF;
4857 END;
4858
4859
4860 procedure Tab0Print (p_text varchar2) IS
4861 BEGIN
4862 IF (JTF_DIAGNOSTIC_ADAPTUTIL.b_html_on) THEN
4863 Tab0Print_html(p_text);
4864 ELSE
4865 Tab0Print_text(p_text);
4866 END IF;
4867 END;
4868
4869
4870 procedure Tab1Print (p_text varchar2) IS
4871 BEGIN
4872 IF (JTF_DIAGNOSTIC_ADAPTUTIL.b_html_on) THEN
4873 Tab1Print_html(p_text);
4874 ELSE
4875 Tab1Print_text(p_text);
4876 END IF;
4877 END;
4878
4879
4880
4881 procedure Tab2Print (p_text varchar2) IS
4882 BEGIN
4883 IF (JTF_DIAGNOSTIC_ADAPTUTIL.b_html_on) THEN
4884 Tab2Print_html(p_text);
4885 ELSE
4886 Tab2Print_text(p_text);
4887 END IF;
4888 END;
4889
4890 procedure Tab3Print (p_text varchar2) IS
4891 BEGIN
4892 IF (JTF_DIAGNOSTIC_ADAPTUTIL.b_html_on) THEN
4893 Tab3Print_html(p_text);
4894 ELSE
4895 Tab3Print_text(p_text);
4896 END IF;
4897 END;
4898
4899 procedure BRPrint IS
4900 BEGIN
4901 IF (JTF_DIAGNOSTIC_ADAPTUTIL.b_html_on) THEN
4902 BRPrint_html;
4903 ELSE
4904 BRPrint_text;
4905 END IF;
4906 END;
4907
4908 procedure checkFinPeriod (p_sobid NUMBER, p_appid NUMBER ) IS
4909 BEGIN
4910 IF (JTF_DIAGNOSTIC_ADAPTUTIL.b_html_on) THEN
4911 checkFinPeriod_html(p_sobid, p_appid);
4912 ELSE
4913 checkFinPeriod_text(p_sobid, p_appid );
4914 END IF;
4915 END;
4916
4917
4918 procedure CheckKeyFlexfield(p_flex_code in varchar2
4919 , p_flex_num in number default null
4920 , p_print_heading in boolean default true) IS
4921 BEGIN
4922 IF (JTF_DIAGNOSTIC_ADAPTUTIL.b_html_on) THEN
4923 CheckKeyFlexfield_html(p_flex_code,p_flex_num,p_print_heading);
4924 ELSE
4925 CheckKeyFlexfield_text(p_flex_code,p_flex_num,p_print_heading);
4926 END IF;
4927 END;
4928
4929
4930 procedure CheckProfile(p_prof_name in varchar2
4931 , p_user_id in number
4932 , p_resp_id in number
4933 , p_appl_id in number
4934 , p_default in varchar2 default null
4935 , p_indent in integer default 0) IS
4936 BEGIN
4937 IF (JTF_DIAGNOSTIC_ADAPTUTIL.b_html_on) THEN
4938 CheckProfile_html(p_prof_name, p_user_id, p_resp_id,p_appl_id,p_default,p_indent);
4939 ELSE
4940 CheckProfile_text(p_prof_name, p_user_id, p_resp_id,p_appl_id,p_default,p_indent);
4941 END IF;
4942 END;
4943
4944
4945 procedure Begin_Pre IS
4946 BEGIN
4947 IF (JTF_DIAGNOSTIC_ADAPTUTIL.b_html_on) THEN
4948 Begin_Pre_html;
4949 ELSE
4950 -- API currently not implemented for text
4951 null;
4952 END IF;
4953 END;
4954
4955
4956 procedure End_Pre IS
4957 BEGIN
4958 IF (JTF_DIAGNOSTIC_ADAPTUTIL.b_html_on) THEN
4959 End_Pre_html;
4960 ELSE
4961 -- API currently not implemented for text
4962 null;
4963 END IF;
4964 END;
4965
4966
4967 procedure Show_Table(p_type varchar2, p_values V2T, p_caption varchar2 default null, p_options V2T default null) IS
4968 BEGIN
4969 IF (JTF_DIAGNOSTIC_ADAPTUTIL.b_html_on) THEN
4970 Show_Table_html(p_type, p_values, p_caption, p_options);
4971 ELSE
4972 -- API currently not implemented for text
4973 null;
4974 END IF;
4975 END;
4976
4977
4978 procedure Show_Table(p_values V2T) IS
4979 BEGIN
4980 IF (JTF_DIAGNOSTIC_ADAPTUTIL.b_html_on) THEN
4981 Show_Table_html(p_values);
4982 ELSE
4983 -- API currently not implemented for text
4984 null;
4985 END IF;
4986 END;
4987
4988
4989 procedure Show_Table(p_type varchar2) IS
4990 BEGIN
4991 IF (JTF_DIAGNOSTIC_ADAPTUTIL.b_html_on) THEN
4992 Show_Table_html(p_type);
4993 ELSE
4994 -- API currently not implemented for text
4995 null;
4996 END IF;
4997 END;
4998
4999
5000 procedure Show_Table_Row(p_values V2T, p_options V2T default null) IS
5001 BEGIN
5002 IF (JTF_DIAGNOSTIC_ADAPTUTIL.b_html_on) THEN
5003 Show_Table_Row_html(p_values, p_options);
5004 ELSE
5005 -- API currently not implemented for text
5006 null;
5007 END IF;
5008 END;
5009
5010
5011 procedure Show_Table_Header(p_values V2T, p_options V2T default null) IS
5012 BEGIN
5013 IF (JTF_DIAGNOSTIC_ADAPTUTIL.b_html_on) THEN
5014 Show_Table_Header_html(p_values, p_options);
5015 ELSE
5016 -- API currently not implemented for text
5017 null;
5018 END IF;
5019 END;
5020
5021 procedure Show_Table_Header(p_headers in headers, p_lengths in out NOCOPY lengths) IS
5022 BEGIN
5023 IF (JTF_DIAGNOSTIC_ADAPTUTIL.b_html_on) THEN
5024 Show_Table_Header_text(p_headers,p_lengths);
5025 ELSE
5026 -- API currently not implemented for text
5027 null;
5028 END IF;
5029 END;
5030
5031
5032 procedure Start_Table(p_caption varchar2 default null) IS
5033 BEGIN
5034 IF (JTF_DIAGNOSTIC_ADAPTUTIL.b_html_on) THEN
5035 Start_Table_html(p_caption);
5036 ELSE
5037 -- API currently not implemented for text
5038 null;
5039 END IF;
5040 END;
5041
5042
5043 procedure End_Table IS
5044 BEGIN
5045 IF (JTF_DIAGNOSTIC_ADAPTUTIL.b_html_on) THEN
5046 End_Table_html;
5047 ELSE
5048 -- API currently not implemented for text
5049 null;
5050 END IF;
5051 END;
5052
5053
5054 function Run_SQL(p_title varchar2, p_sql_statement varchar2) return number is
5055 BEGIN
5056 IF (JTF_DIAGNOSTIC_ADAPTUTIL.b_html_on) THEN
5057 return Run_SQL_html(p_title,p_sql_statement);
5058 ELSE
5059 -- API currently not implemented for text
5060 null;
5061 END IF;
5062 END;
5063
5064 function Run_SQL(p_title varchar2
5065 , p_sql_statement varchar2
5066 , p_feedback varchar2) return number is
5067 BEGIN
5068 IF (JTF_DIAGNOSTIC_ADAPTUTIL.b_html_on) THEN
5069 return Run_SQL_html(p_title,p_sql_statement,p_feedback);
5070 ELSE
5071 -- API currently not implemented for text
5072 null;
5073 END IF;
5074 END;
5075
5076
5077 function Run_SQL(p_title varchar2
5078 , p_sql_statement varchar2
5079 , p_max_rows number) return number is
5080 BEGIN
5081 IF (JTF_DIAGNOSTIC_ADAPTUTIL.b_html_on) THEN
5082 return Run_SQL_html(p_title,p_sql_statement,p_max_rows);
5083 ELSE
5084 -- API currently not implemented for text
5085 null;
5086 END IF;
5087 END;
5088
5089
5090
5091 function Run_SQL(p_title varchar2
5092 , p_sql_statement varchar2
5093 , p_feedback varchar2
5094 , p_max_rows number) return number is
5095 BEGIN
5096 IF (JTF_DIAGNOSTIC_ADAPTUTIL.b_html_on) THEN
5097 return Run_SQL_html(p_title,p_sql_statement,p_feedback ,p_max_rows);
5098 ELSE
5099 -- API currently not implemented for text
5100 null;
5101 END IF;
5102 END;
5103
5104
5105 procedure Run_SQL(p_title varchar2, p_sql_statement varchar2) is
5106 BEGIN
5107 IF (JTF_DIAGNOSTIC_ADAPTUTIL.b_html_on) THEN
5108 Run_SQL_html(p_title,p_sql_statement);
5109 ELSE
5110 -- API currently not implemented for text
5111 null;
5112 END IF;
5113 END;
5114
5115
5116 procedure Run_SQL(p_title varchar2
5117 , p_sql_statement varchar2
5118 , p_feedback varchar2) is
5119 BEGIN
5120 IF (JTF_DIAGNOSTIC_ADAPTUTIL.b_html_on) THEN
5121 Run_SQL_html(p_title,p_sql_statement,p_feedback);
5122 ELSE
5123 -- API currently not implemented for text
5124 null;
5125 END IF;
5126 END;
5127
5128
5129 procedure Run_SQL(p_title varchar2
5130 , p_sql_statement varchar2
5131 , p_max_rows number) is
5132 BEGIN
5133 IF (JTF_DIAGNOSTIC_ADAPTUTIL.b_html_on) THEN
5134 Run_SQL_html(p_title,p_sql_statement,p_max_rows);
5135 ELSE
5136 -- API currently not implemented for text
5137 null;
5138 END IF;
5139 END;
5140
5141 procedure Run_SQL(p_title varchar2
5142 , p_sql_statement varchar2
5143 , p_feedback varchar2
5144 , p_max_rows number) IS
5145 BEGIN
5146 IF (JTF_DIAGNOSTIC_ADAPTUTIL.b_html_on) THEN
5147 Run_SQL_html(p_title,p_sql_statement,p_feedback ,p_max_rows);
5148 ELSE
5149 -- API currently not implemented for text
5150 null;
5151 END IF;
5152 END;
5153
5154
5155 function Run_SQL(p_title varchar2,
5156 p_sql_statement varchar2,
5157 p_disp_lengths lengths) return number is
5158 BEGIN
5159 IF (JTF_DIAGNOSTIC_ADAPTUTIL.b_html_on) THEN
5160 -- API currently not implemented for html
5161 null;
5162 ELSE
5163 return Run_SQL_text(p_title,p_sql_statement,p_disp_lengths);
5164 END IF;
5165 END;
5166
5167
5168
5169 function Run_SQL(p_title varchar2,
5170 p_sql_statement varchar2,
5171 p_disp_lengths lengths,
5172 p_headers headers) return number is
5173 BEGIN
5174 IF (JTF_DIAGNOSTIC_ADAPTUTIL.b_html_on) THEN
5175 -- API currently not implemented for html
5176 null;
5177 ELSE
5178 return Run_SQL_text(p_title,p_sql_statement,p_disp_lengths,p_headers);
5179 END IF;
5180 END;
5181
5182 function Run_SQL(p_title varchar2,
5183 p_sql_statement varchar2,
5184 p_disp_lengths lengths,
5185 p_headers headers,
5186 p_feedback varchar2) return number is
5187 BEGIN
5188 IF (JTF_DIAGNOSTIC_ADAPTUTIL.b_html_on) THEN
5189 -- API currently not implemented for html
5190 null;
5191 ELSE
5192 return Run_SQL_text(p_title,p_sql_statement,p_disp_lengths,p_headers,p_feedback);
5193 END IF;
5194 END;
5195
5196
5197 function Run_SQL(p_title varchar2,
5198 p_sql_statement varchar2,
5199 p_disp_lengths lengths,
5200 p_headers headers,
5201 p_max_rows number) return number is
5202 BEGIN
5203 IF (JTF_DIAGNOSTIC_ADAPTUTIL.b_html_on) THEN
5204 -- API currently not implemented for html
5205 null;
5206 ELSE
5207 return Run_SQL_text(p_title,p_sql_statement,p_disp_lengths,p_headers,p_max_rows);
5208 END IF;
5209 END;
5210
5211
5212 function Run_SQL(p_title varchar2,
5213 p_sql_statement varchar2,
5214 p_disp_lengths lengths,
5215 p_headers headers,
5216 p_feedback varchar2,
5217 p_max_rows number) return number is
5218 BEGIN
5219 IF (JTF_DIAGNOSTIC_ADAPTUTIL.b_html_on) THEN
5220 -- API currently not implemented for html
5221 null;
5222 ELSE
5223 return Run_SQL_text(p_title,p_sql_statement,p_disp_lengths,p_headers,p_feedback,p_max_rows);
5224 END IF;
5225 END;
5226
5227
5228
5229 procedure Run_SQL(p_title varchar2,
5230 p_sql_statement varchar2,
5231 p_disp_lengths lengths) IS
5232 BEGIN
5233 IF (JTF_DIAGNOSTIC_ADAPTUTIL.b_html_on) THEN
5234 -- API currently not implemented for html
5235 null;
5236 ELSE
5237 Run_SQL_text(p_title,p_sql_statement,p_disp_lengths);
5238 END IF;
5239
5240 END;
5241
5242
5243 procedure Run_SQL(p_title varchar2,
5244 p_sql_statement varchar2,
5245 p_disp_lengths lengths,
5246 p_headers headers) is
5247 BEGIN
5248 IF (JTF_DIAGNOSTIC_ADAPTUTIL.b_html_on) THEN
5249 -- API currently not implemented for html
5250 null;
5251 ELSE
5252 Run_SQL_text(p_title,p_sql_statement,p_disp_lengths,p_headers);
5253 END IF;
5254 END;
5255
5256
5257
5258 procedure Display_Table (p_table_name varchar2,
5259 p_table_alias varchar2,
5260 p_where_clause varchar2,
5261 p_order_by_clause varchar2 default null,
5262 p_display_longs varchar2 default 'Y') IS
5263 BEGIN
5264 IF (JTF_DIAGNOSTIC_ADAPTUTIL.b_html_on) THEN
5265 Display_Table_html(p_table_name,p_table_alias ,p_where_clause,p_order_by_clause,p_display_longs);
5266 ELSE
5267 -- API currently not implemented for text
5268 null;
5269 END IF;
5270 END;
5271
5272
5273
5274 procedure Show_Header(p_note varchar2, p_title varchar2) IS
5275 BEGIN
5276 IF (JTF_DIAGNOSTIC_ADAPTUTIL.b_html_on) THEN
5277 Show_Header_html(p_note, p_title);
5278 ELSE
5279 Show_Header_text(p_note, p_title);
5280 END IF;
5281 END;
5282
5283
5284
5285
5286 procedure Show_Footer(p_script_name varchar2, p_header varchar2) IS
5287 BEGIN
5288 IF (JTF_DIAGNOSTIC_ADAPTUTIL.b_html_on) THEN
5289 Show_Footer_html(p_script_name,p_header );
5290 ELSE
5291 Show_Footer_text;
5292 END IF;
5293 END;
5294
5295
5296 procedure Show_Footer IS
5297 BEGIN
5298 IF (JTF_DIAGNOSTIC_ADAPTUTIL.b_html_on) THEN
5299 -- API currently not implemented for html
5300 null;
5301 ELSE
5302 Show_Footer_text;
5303 END IF;
5304 END;
5305
5306
5307 procedure Show_Link(p_note varchar2) IS
5308 BEGIN
5309 IF (JTF_DIAGNOSTIC_ADAPTUTIL.b_html_on) THEN
5310 Show_Link_html(p_note);
5311 ELSE
5312 Show_Link_text(p_note);
5313 END IF;
5314 END;
5315
5316 procedure Show_Link(p_link varchar2, p_link_name varchar2 ) IS
5317 BEGIN
5318 IF (JTF_DIAGNOSTIC_ADAPTUTIL.b_html_on) THEN
5319 Show_Link_html(p_link, p_link_name);
5320 ELSE
5321 Show_Link_text(p_link, p_link_name);
5322 END IF;
5323 END;
5324
5325
5326 procedure Send_Email ( p_sender varchar2
5327 , p_recipient varchar2
5328 , p_subject varchar2
5329 , p_message varchar2
5330 , p_mailhost varchar2) IS
5331 BEGIN
5332 IF (JTF_DIAGNOSTIC_ADAPTUTIL.b_html_on) THEN
5333 Send_Email_html( p_sender, p_recipient, p_subject , p_message , p_mailhost);
5334 ELSE
5335 -- API currently not implemented for text
5336 null;
5337 END IF;
5338 END;
5339
5340
5341 procedure Display_Profiles (p_application_id varchar2
5342 , p_short_name varchar2 default null) IS
5343 BEGIN
5344 IF (JTF_DIAGNOSTIC_ADAPTUTIL.b_html_on) THEN
5345 Display_Profiles_html(p_application_id, p_short_name);
5346 ELSE
5347 Display_Profiles_text(p_application_id, p_short_name);
5348 END IF;
5349 END;
5350
5351
5352 procedure Set_Org (p_org_id number) IS
5353 BEGIN
5354 IF (JTF_DIAGNOSTIC_ADAPTUTIL.b_html_on) THEN
5355 Set_Org_html(p_org_id);
5356 ELSE
5357 Set_Org_text(p_org_id);
5358 END IF;
5359 END;
5360
5361 procedure Set_Client(p_user_name varchar2, p_resp_id number,
5362 p_app_id number, p_sec_grp_id number) IS
5363 BEGIN
5364 IF (JTF_DIAGNOSTIC_ADAPTUTIL.b_html_on) THEN
5365 Set_Client_html(p_user_name, p_resp_id,p_app_id, p_sec_grp_id);
5366 ELSE
5367 Set_Client_text(p_user_name, p_resp_id,p_app_id, p_sec_grp_id);
5368 END IF;
5369 END;
5370
5371 procedure Set_Client(p_user_name varchar2, p_resp_id number) IS
5372 BEGIN
5373 IF (JTF_DIAGNOSTIC_ADAPTUTIL.b_html_on) THEN
5374 Set_Client_html(p_user_name, p_resp_id);
5375 ELSE
5376 Set_Client_text(p_user_name, p_resp_id);
5377 END IF;
5378 END;
5379
5380 procedure Set_Client(p_user_name varchar2, p_resp_id number,
5381 p_app_id number ) IS
5382 BEGIN
5383 IF (JTF_DIAGNOSTIC_ADAPTUTIL.b_html_on) THEN
5384 Set_Client_html(p_user_name, p_resp_id,p_app_id);
5385 ELSE
5386 Set_Client_text(p_user_name, p_resp_id,p_app_id);
5387 END IF;
5388 END;
5389
5390
5391 /*
5392
5393 -- OBSOLETED API SINCE THIS IS NOT COMPLIANT WITH GSCC STANDARD
5394 -- FILE.SQL.6 WHICH DOES NOT PERMIT USING OF SCHEMA
5395 -- NAMES WITHIN PLSQL CODE
5396
5397 procedure Get_DB_Patch_List (p_heading varchar2 default 'AD_BUGS'
5398 , p_app_short_name varchar2 default '%'
5399 , p_bug_number varchar2 default '%'
5400 , p_start_date date default to_date(olddate,'MM-DD-YYYY')
5401 , p_output_option varchar2 default 'TABLE') IS
5402 BEGIN
5403 IF (JTF_DIAGNOSTIC_ADAPTUTIL.b_html_on) THEN
5404 Get_DB_Patch_List_html (p_heading, p_app_short_name, p_bug_number, p_start_date, p_output_option);
5405 ELSE
5406 -- API currently not implemented for text
5407 null;
5408 END IF;
5409 END;
5410 */
5411
5412
5413 /*
5414
5415 -- OBSOLETED API SINCE THIS IS NOT COMPLIANT WITH GSCC STANDARD
5416 -- FILE.SQL.6 WHICH DOES NOT PERMIT USING OF SCHEMA
5417 -- NAMES WITHIN PLSQL CODE
5418
5419 procedure Get_DB_Patch_List (
5420 p_heading varchar2 default 'AD_BUGS'
5421 , p_app_short_name varchar2 default '%'
5422 , p_bug_number varchar2 default '%'
5423 , p_start_date date default to_date(olddate,'MM-DD-YYYY')) is
5424 BEGIN
5425 IF (JTF_DIAGNOSTIC_ADAPTUTIL.b_html_on) THEN
5426 -- API currently not implemented for html
5427 null;
5428 ELSE
5429 Get_DB_Patch_List_text(p_heading, p_app_short_name, p_bug_number, p_start_date);
5430 END IF;
5431 END;
5432
5433 */
5434
5435 /*
5436
5437 -- OBSOLETED API SINCE THIS IS NOT COMPLIANT WITH GSCC STANDARD
5438 -- FILE.SQL.6 WHICH DOES NOT PERMIT USING OF SCHEMA
5439 -- NAMES WITHIN PLSQL CODE
5440
5441
5442 Procedure Show_Invalids (p_start_string varchar2
5443 , p_include_errors varchar2 default 'N'
5444 , p_heading varchar2 default null) IS
5445 BEGIN
5446 IF (JTF_DIAGNOSTIC_ADAPTUTIL.b_html_on) THEN
5447 Show_Invalids_html(p_start_string,p_include_errors,p_heading);
5448 ELSE
5449 Show_Invalids_text(p_start_string,p_include_errors,p_heading);
5450 END IF;
5451 END;
5452 */
5453
5454 Function CheckKeyFlexfield(p_flex_code in varchar2
5455 , p_flex_num in number default null
5456 , p_print_heading in boolean default true) return V2T IS
5457 BEGIN
5458 IF (JTF_DIAGNOSTIC_ADAPTUTIL.b_html_on) THEN
5459 return CheckKeyFlexfield_html(p_flex_code,p_flex_num,p_print_heading);
5460 ELSE
5461 return CheckKeyFlexfield_text(p_flex_code,p_flex_num,p_print_heading);
5462 END IF;
5463 END;
5464
5465
5466 function CheckProfile(p_prof_name in varchar2
5467 , p_user_id in number
5468 , p_resp_id in number
5469 , p_appl_id in number
5470 , p_default in varchar2 default null
5471 , p_indent in integer default 0) return varchar2 IS
5472 BEGIN
5473 IF (JTF_DIAGNOSTIC_ADAPTUTIL.b_html_on) THEN
5474 return CheckProfile_html(p_prof_name, p_user_id, p_resp_id, p_appl_id, p_default, p_indent);
5475 ELSE
5476 return CheckProfile_text(p_prof_name, p_user_id, p_resp_id, p_appl_id, p_default, p_indent);
5477 END IF;
5478 END;
5479
5480
5481
5482 function Column_Exists(p_tab in varchar, p_col in varchar, p_owner in varchar) return varchar2 IS
5483 BEGIN
5484 IF (JTF_DIAGNOSTIC_ADAPTUTIL.b_html_on) THEN
5485 return Column_Exists_html(p_tab, p_col, p_owner);
5486 ELSE
5487 return Column_Exists_text(p_tab, p_col, p_owner);
5488 END IF;
5489 END;
5490
5491
5492 function Display_SQL (p_sql_statement varchar2
5493 , table_alias varchar2
5494 , display_longs varchar2 default 'Y'
5495 , p_feedback varchar2 default 'Y'
5496 , p_max_rows number default null
5497 , p_current_exec number default 0) return number IS
5498 BEGIN
5499 IF (JTF_DIAGNOSTIC_ADAPTUTIL.b_html_on) THEN
5500 return Display_SQL_html(p_sql_statement, table_alias, FALSE, display_longs, p_feedback, p_max_rows, p_current_exec);
5501 ELSE
5502 -- API currently not implemented for text
5503 null;
5504 END IF;
5505 END;
5506
5507 function Display_SQL (p_sql_statement varchar2
5508 , table_alias varchar2
5509 , hideHeader Boolean
5510 , display_longs varchar2 default 'Y'
5511 , p_feedback varchar2 default 'Y'
5512 , p_max_rows number default null
5513 , p_current_exec number default 0) return number IS
5514 BEGIN
5515 IF (JTF_DIAGNOSTIC_ADAPTUTIL.b_html_on) THEN
5516 return Display_SQL_html(p_sql_statement, table_alias, hideHeader, display_longs, p_feedback, p_max_rows, p_current_exec);
5517 ELSE
5518 -- API currently not implemented for text
5519 null;
5520 END IF;
5521 END;
5522
5523 Function Compare_Pkg_Version(
5524 package_name in varchar2,
5525 object_type in varchar2,
5526 object_owner in varchar2,
5527 version_str in out NOCOPY varchar2,
5528 compare_version in varchar2)
5529 return varchar2 is
5530 BEGIN
5531 IF (JTF_DIAGNOSTIC_ADAPTUTIL.b_html_on) THEN
5532 -- API currently not implemented for html
5533 null;
5534 ELSE
5535 return Compare_Pkg_Version_text(package_name,object_type,object_owner,version_str,compare_version);
5536 END IF;
5537 END;
5538
5539
5540
5541 function Display_Table (p_table_name varchar2,
5542 p_table_alias varchar2,
5543 p_where_clause varchar2,
5544 p_order_by_clause varchar2 default null,
5545 p_display_longs varchar2 default 'Y') return number IS
5546 BEGIN
5547 IF (JTF_DIAGNOSTIC_ADAPTUTIL.b_html_on) THEN
5548 return Display_Table_html (p_table_name,p_table_alias, p_where_clause, p_order_by_clause, p_display_longs);
5549 ELSE
5550 -- API currently not implemented for text
5551 null;
5552 END IF;
5553 END;
5554
5555
5556
5557 function Get_DB_Apps_Version return varchar2 IS
5558 BEGIN
5559 IF (JTF_DIAGNOSTIC_ADAPTUTIL.b_html_on) THEN
5560 return Get_DB_Apps_Version_html;
5561 ELSE
5562 return Get_DB_Apps_Version_text;
5563 END IF;
5564 END;
5565
5566 function Get_Package_Version (p_type varchar2, p_schema varchar2, p_package varchar2) return varchar2 IS
5567 BEGIN
5568 IF (JTF_DIAGNOSTIC_ADAPTUTIL.b_html_on) THEN
5569 return Get_Package_Version_html(p_type, p_schema,p_package);
5570 ELSE
5571 return Get_Package_Version_text(p_type, p_schema,p_package);
5572 END IF;
5573 END;
5574
5575 function Get_Package_Spec(p_package varchar2) return varchar2 IS
5576 BEGIN
5577 IF (JTF_DIAGNOSTIC_ADAPTUTIL.b_html_on) THEN
5578 return Get_Package_Spec_html(p_package);
5579 ELSE
5580 return Get_Package_Spec_text(p_package);
5581 END IF;
5582 END;
5583
5584 function Get_Package_Body(p_package varchar2) return varchar2 IS
5585 BEGIN
5586 IF (JTF_DIAGNOSTIC_ADAPTUTIL.b_html_on) THEN
5587 return Get_Package_Body_html(p_package);
5588 ELSE
5589 return Get_Package_Body_text(p_package);
5590 END IF;
5591 END;
5592
5593
5594 function Get_Profile_Option (p_profile_option varchar2) return varchar2 IS
5595 BEGIN
5596 IF (JTF_DIAGNOSTIC_ADAPTUTIL.b_html_on) THEN
5597 return Get_Profile_Option_html(p_profile_option);
5598 ELSE
5599 return Get_Profile_Option_text(p_profile_option);
5600 END IF;
5601 END;
5602
5603
5604 Function Get_RDBMS_Header return varchar2 IS
5605 BEGIN
5606 IF (JTF_DIAGNOSTIC_ADAPTUTIL.b_html_on) THEN
5607 return Get_RDBMS_Header_html;
5608 ELSE
5609 return Get_RDBMS_Header_text;
5610 END IF;
5611 END;
5612
5613 FUNCTION Get_Apps_Schema_Name RETURN VARCHAR2 IS
5614 apps_schema_name varchar2(30);
5615 BEGIN
5616 select oracle_username into apps_schema_name from fnd_oracle_userid where read_only_flag='U';
5617 return apps_schema_name;
5618 END;
5619
5620
5621
5622 function Display_SQL (
5623 p_sql_statement varchar2
5624 , p_disp_lengths lengths
5625 , p_headers headers default null
5626 , p_feedback varchar2 default 'Y'
5627 , p_max_rows number default null)
5628 return number is
5629 BEGIN
5630 IF (JTF_DIAGNOSTIC_ADAPTUTIL.b_html_on) THEN
5631 -- API currently not implemented for html
5632 null;
5633 ELSE
5634 return Display_SQL_text(p_sql_statement,p_disp_lengths,p_headers,p_feedback, p_max_rows);
5635 END IF;
5636 END;
5637
5638
5639 procedure Display_SQL (
5640 p_sql_statement varchar2
5641 , p_disp_lengths lengths) is
5642 BEGIN
5643 IF (JTF_DIAGNOSTIC_ADAPTUTIL.b_html_on) THEN
5644 -- API currently not implemented for html
5645 null;
5646 ELSE
5647 Display_SQL_text(p_sql_statement,p_disp_lengths);
5648 END IF;
5649 END;
5650
5651 procedure Display_SQL (
5652 p_sql_statement varchar2
5653 , p_disp_lengths lengths
5654 , p_headers headers) is
5655 BEGIN
5656 IF (JTF_DIAGNOSTIC_ADAPTUTIL.b_html_on) THEN
5657 -- API currently not implemented for html
5658 null;
5659 ELSE
5660 Display_SQL_text(p_sql_statement,p_disp_lengths,p_headers);
5661 END IF;
5662 END;
5663
5664 procedure Display_SQL (
5665 p_sql_statement varchar2
5666 , p_disp_lengths lengths
5667 , p_headers headers
5668 , p_feedback varchar2) is
5669 BEGIN
5670 IF (JTF_DIAGNOSTIC_ADAPTUTIL.b_html_on) THEN
5671 -- API currently not implemented for html
5672 null;
5673 ELSE
5674 Display_SQL_text(p_sql_statement,p_disp_lengths,p_headers,p_feedback);
5675 END IF;
5676 END;
5677 -- BVS-STOP <- Stop ignoring this section (restart scanning)
5678
5679
5680 procedure Display_SQL(
5681 p_sql_statement varchar2
5682 , p_disp_lengths lengths
5683 , p_headers headers
5684 , p_feedback varchar2
5685 , p_max_rows number) is
5686 BEGIN
5687 IF (JTF_DIAGNOSTIC_ADAPTUTIL.b_html_on) THEN
5688 -- API currently not implemented for html
5689 null;
5690 ELSE
5691 Display_SQL_text(p_sql_statement,p_disp_lengths,p_headers,p_feedback, p_max_rows);
5692 END IF;
5693 END;
5694
5695
5696 END JTF_DIAGNOSTIC_COREAPI;
5697
[Home] [Help]