[Home] [Help]
PACKAGE BODY: APPS.ETRM_FNDNAV
Source
1 PACKAGE BODY "ETRM_FNDNAV" as
2
3 ----------------------------------------------------------------------------
4 -- Copyright ?? 2001, 2014, Oracle and/or its affiliates. All rights reserved.
5 -- eTRM: Oracle Applications repository browser and dependency report
6 -- Author: Peter Goldthorp 9 October 2001
7 -----------------------------------------------------------------------------
8
9 -----------------------------------------------------------------------------
10 -- Private Procedures
11 -----------------------------------------------------------------------------
12 function get_appname(n_appid in number) return varchar2
13 is
14
15 cursor cur_app(n_appid in number)
16 is
17 select decode(app.application_short_name, 'SQLAP', 'AP'
18 , 'SQLGL', 'GL'
19 , app.application_short_name)
20 || ' - '|| tl.application_name product_name
21 from APPLSYS.FND_APPLICATION_TL tl
22 , APPLSYS.FND_APPLICATION app
23 where app.application_id = tl.application_id
24 and app.application_id = n_appid
25 and tl.language = userenv('LANG');
26
27 vreturn_value varchar2(512) := '%';
28 begin
29 for a_rec in cur_app(n_appid) loop
30 vreturn_value := a_rec.product_name;
31 end loop;
32 return vreturn_value;
33 end get_appname;
34
35 function get_tablename(n_appid in number
36 , n_tabid in number) return varchar2
37 is
38
39 cursor cur_name(n_appid in number
40 , n_tabid in number)
41 is
42 select table_name
43 from applsys.fnd_tables
44 where application_id = n_appid
45 and table_id = n_tabid;
46
47 vreturn_value varchar2(512) := '%';
48 begin
49 for a_rec in cur_name(n_appid, n_tabid) loop
50 vreturn_value := a_rec.table_name;
51 end loop;
52 return vreturn_value;
53 end get_tablename;
54
55 function get_lookup_meaning(c_lookup_type in varchar2
56 , c_lookup_code in varchar2)
57 return fnd_lookup_values.meaning%type is
58
59 cursor cur_lookup_values(c_lookup_type in varchar2
60 , c_lookup_code in varchar2) is
61 select t.meaning
62 from fnd_lookup_values t
63 , fnd_lookup_types l
64 where l.lookup_type = c_lookup_type
65 and t.lookup_code = c_lookup_code
66 and l.lookup_type = t.lookup_type
67 and l.security_group_id = t.security_group_id
68 and l.view_application_id = t.view_application_id
69 and t.language = userenv('LANG');
70
71 v_return_value fnd_lookup_values.meaning%type := NULL;
72
73 begin
74 for l_rec in cur_lookup_values (c_lookup_type => c_lookup_type
75 , c_lookup_code => c_lookup_code) loop
76 v_return_value := l_rec.meaning;
77 end loop;
78 return v_return_value;
79
80 end get_lookup_meaning;
81
82 procedure draw_buttons
83 is
84 begin
85 uiutil.a_href_gen(c_link => 'etrm_search.search'
86 , c_display => 'Home');
87 -- uiutil.a_href_gen(c_link => 'https://login.oracle.com/pls/orasso/orasso.wwsso_app_admin.ls_logout?p_done_url=http://etrm.oracle.com/'
88 -- , c_display => 'Sign out');
89 uiutil.a_javascript_gen(c_link => 'etrm_search.help'
90 , c_display => 'Help');
91 end draw_buttons;
92
93 procedure draw_tabs
94 is
95 v_cookie owa_cookie.cookie;
96 v_pnav_url varchar2(1024);
97 v_search_url varchar2(1024);
98 begin
99
100 v_cookie := owa_cookie.get('etrm_pnav_context');
101 if (v_cookie.num_vals >0) THEN
102 v_pnav_url := 'etrm_pnav.ls_object?'||utl_url.escape(v_cookie.vals(1));
103 else
104 v_pnav_url := 'etrm_pnav.ls_apps';
105 end if;
106
107 v_cookie := owa_cookie.get('etrm_search_context');
108 if (v_cookie.num_vals >0) THEN
109 v_search_url := 'etrm_search.search?c_search='||utl_url.escape(v_cookie.vals(1));
110 else
111 v_search_url := 'etrm_search.search';
112 end if;
113
114
115 -- uiutil.render_tab(c_url => v_search_url
116 -- , c_title => 'Search'
117 -- , c_position => 'LEFT'
118 -- , c_state => 'ENABLED');
119
120 uiutil.render_tab(c_url => 'etrm_fndnav.ls_apps'
121 , c_title => 'FND'
122 , c_position => 'LEFT'
123 , c_state => 'SELECTED');
124
125 uiutil.render_tab(c_url => v_pnav_url
126 , c_title => 'DBA'
127 , c_position => 'RIGHT'
128 , c_state => 'ENABLED'
129 , b_prior_selected => TRUE);
130
131 end draw_tabs;
132
133 procedure quick_list(c_search in varchar2 := ' ')
134 is
135 cursor cur_version is
136 select release_name
137 from fnd_product_groups
138 order by release_name;
139
140 v_version fnd_product_groups.release_name%type;
141 begin
142 v_version := '11i';
143 for v_rec in cur_version loop
144 v_version := v_rec.release_name;
145 end loop;
146
147 htp.p('<h1>'||v_version||' FND Design Data</h1>');
148
149 end quick_list;
150
151 procedure draw_form(n_appid in number := NULL
152 , c_name in dba_objects.object_name%type := '%'
153 , c_type in dba_objects.object_type%type := '%')
154 is
155
156 cursor cur_apps
157 is
158 select app.application_id
159 , decode(app.application_short_name, 'SQLAP', 'AP'
160 , 'SQLGL', 'GL'
161 , app.application_short_name)
162 || ' - '|| tl.application_name product_name
163 from FND_APPLICATION_TL tl
164 , FND_APPLICATION app
165 where app.application_id = tl.application_id
166 and tl.language = userenv('LANG')
167 order by decode(app.application_short_name, 'SQLAP', 'AP'
168 , 'SQLGL', 'GL'
169 , app.application_short_name);
170
171 begin
172
173 if (n_appid is null
174 and c_name = '%'
175 and c_type = '%') then
176 htp.p('<p>Browse the FND Tables in an Applications database</p>');
177 else
178 null;
179 -- htp.p('<p><a HREF="etrm_fndnav.ls_object?c_name=*&c_type=*"><img src="/images/repository.gif" border=0 alt="Home">FND Navigator Home</a>');
180 end if;
181
182
183
184
185
186 htp.p('<FORM ACTION="etrm_fndnav.ls_object" METHOD="POST">');
187 htp.p('<TABLE cellpadding="0" cellspacing="0" summary="">');
188
189 htp.p('<tr><td><label for="product"> Product:</td> ');
190 htp.p('<td><SELECT id="product" NAME="n_appid" SIZE="1">');
191 htp.p(' <OPTION selected VALUE="'||n_appid||'">'||get_appname(n_appid));
192 if n_appid is null
193 then null;
194 else
195 htp.p(' <OPTION VALUE="">%');
196 end if;
197 for a_rec in cur_apps loop
198 htp.p(' <OPTION VALUE="'||a_rec.application_id||'">'||a_rec.product_name);
199 end loop;
200 htp.p('</select>');
201 htp.p('</label></td></tr>');
202
203 htp.p('<tr><td><label for="name"> Object Name <i>like</i>:</td> ');
204 htp.p('<td><INPUT TYPE="text" id="name" NAME="c_name" SIZE="30" maxlength="80" value="'
205 ||FND_CSS_PKG.Encode(c_name)
206 ||'"></label></td></tr>');
207 htp.p('<tr><td><label for="type"> Object Type:</td> ');
208 htp.p('<td><SELECT id="type" NAME="c_type" SIZE="1">');
209 if(FND_CSS_PKG.Encode(c_type) in ('TABLE', 'VIEW', 'CONCURRENT', 'LOOKUP', 'FILE', 'EVENT', '%')) then
210 htp.p(' <OPTION selected VALUE="'||FND_CSS_PKG.Encode(c_type)||'">'||FND_CSS_PKG.Encode(c_type));
211 end if;
212 if c_type = '%'
213 then null;
214 else
215 htp.p(' <OPTION VALUE="%">%');
216 end if;
217 htp.p(' <OPTION VALUE="TABLE">TABLE');
218 htp.p(' <OPTION VALUE="VIEW">VIEW');
219 htp.p(' <OPTION VALUE="CONCURRENT">CONCURRENT');
220 htp.p(' <OPTION VALUE="LOOKUP">LOOKUP');
221 htp.p(' <OPTION VALUE="FILE">FILE');
222 htp.p(' <OPTION VALUE="EVENT">EVENT');
223 htp.p('</select>');
224 htp.p('</label></td></tr>');
225
226
227 htp.p('</TABLE>');
228 htp.p('<INPUT TYPE="submit" VALUE="Submit">');
229 htp.p('</FORM>');
230
231
232 end draw_form;
233
234
235 procedure ls_app_itemcount(n_appid in number := NULL
236 , c_name in dba_objects.object_name%type := '%'
237 , c_type in dba_objects.object_type%type := '%') is
238
239
240 cursor cur_tables(c_name in dba_objects.object_name%type := '%'
241 , n_appid in number) is
242 select count(t.table_id) id_count
243 from fnd_tables t
244 where t.application_id = n_appid
245 and t.table_name like c_name ESCAPE '\';
246
247
248 cursor cur_files(c_name in dba_objects.object_name%type := '%'
249 , n_appid in number) is
250 select count(f.file_id) id_count
251 from fnd_etrm_files f
252 where f.application_id = n_appid
253 and upper(f.file_name) like c_name ESCAPE '\';
254
255 cursor cur_views(c_name in dba_objects.object_name%type := '%'
256 , n_appid in number) is
257 select count(v.view_id) id_count
258 from fnd_views v
259 where v.application_id = n_appid
260 and v.view_name like c_name ESCAPE '\';
261
262 cursor cur_concurrent(c_name in dba_objects.object_name%type := '%'
263 , n_appid in number) is
264 select count(c.application_id) id_count
265 from fnd_concurrent_programs c
266 where c.application_id = n_appid
267 and c.concurrent_program_name like c_name ESCAPE '\';
268
269 cursor cur_lookup(c_name in dba_objects.object_name%type := '%'
270 , n_appid in number) is
271 select count(c.application_id) id_count
272 from fnd_lookup_types c
273 where c.application_id = n_appid
274 and c.lookup_type like c_name ESCAPE '\';
275
276 cursor cur_event(c_name in dba_objects.object_name%type := '%'
277 , n_appid in number) is
278 select count(e.name) id_count
279 from wf_events e
280 , fnd_application a
281 where a.application_id = n_appid
282 and e.owner_tag = a.application_short_name
283 and e.name like c_name ESCAPE '\';
284
285 vview_count integer := 0;
286 vtable_count integer := 0;
287 vconcurrent_count integer := 0;
288 vlookup_count integer := 0;
289 vfile_count integer := 0;
290 vevent_count integer := 0;
291 v_counter integer := 0;
292
293 begin
294
295 for c_rec in cur_tables(n_appid => n_appid, c_name=>c_name) loop
296 vtable_count := c_rec.id_count;
297 v_counter := v_counter + c_rec.id_count;
298 end loop;
299 for c_rec in cur_views(n_appid => n_appid, c_name=>c_name) loop
300 vview_count := c_rec.id_count;
301 v_counter := v_counter + c_rec.id_count;
302 end loop;
303 for c_rec in cur_concurrent(n_appid => n_appid, c_name=>c_name) loop
304 vconcurrent_count := c_rec.id_count;
305 v_counter := v_counter + c_rec.id_count;
306 end loop;
307 for c_rec in cur_lookup(n_appid => n_appid, c_name=>c_name) loop
308 vlookup_count := c_rec.id_count;
309 v_counter := v_counter + c_rec.id_count;
310 end loop;
311 for c_rec in cur_files(n_appid => n_appid, c_name=>c_name) loop
312 vfile_count := c_rec.id_count;
313 v_counter := v_counter + c_rec.id_count;
314 end loop;
315 for c_rec in cur_event(n_appid => n_appid, c_name=>c_name) loop
316 vevent_count := c_rec.id_count;
317 v_counter := v_counter + c_rec.id_count;
318 end loop;
319
320
321
322 if (v_counter >0) then
323
324 htp.p('<dt>');
325 uiutil.a_href_gen(c_type => 'APP'
326 , c_link => 'etrm_fndnav.ls_object?c_name='
327 ||replace(c_name, '%', '*')
328 ||'&n_appid='||n_appid
329 ||'&c_type=' ||replace(nvl(c_type, '*'), '%', '*')
330 , c_display => get_appname(n_appid));
331 htp.p('</dt>');
332 -- else
333 -- htp.p('<dt>'||uiutil.el_image('APP')
334 -- ||get_appname(n_appid)
335 -- ||'</dt>');
336 -- htp.p('<dd>No documented items</dd>');
337 end if;
338
339 if vtable_count > 0 then
340 htp.p('<dd>');
341 uiutil.a_href_gen(c_type => 'TABLE'
342 , c_link => 'etrm_fndnav.ls_object?c_name='
343 ||replace(c_name, '%', '*')
344 ||'&n_appid='
345 ||n_appid
346 ||'&c_type=TABLE'
347 , c_display => vtable_count||' Tables');
348 htp.p('</dd>');
349 end if;
350
351 if vview_count > 0 then
352 htp.p('<dd>');
353 uiutil.a_href_gen(c_type => 'VIEW'
354 , c_link => 'etrm_fndnav.ls_object?c_name='
355 ||replace(c_name, '%', '*')
356 ||'&n_appid='
357 ||n_appid
358 ||'&c_type=VIEW'
359 , c_display => vview_count||' Views');
360 htp.p('</dd>');
361 end if;
362
363 if vconcurrent_count > 0 then
364 htp.p('<dd>');
365 uiutil.a_href_gen(c_type => 'CONCURRENT'
366 , c_link => 'etrm_fndnav.ls_object?c_name='
367 ||replace(c_name, '%', '*')
368 ||'&n_appid='
369 ||n_appid
370 ||'&c_type=CONCURRENT'
371 , c_display => vconcurrent_count||' Concurrent Programs');
372 htp.p('</dd>');
373 end if;
374
375 if vlookup_count > 0 then
376 htp.p('<dd>');
377 uiutil.a_href_gen(c_type => 'LOOKUP'
378 , c_link => 'etrm_fndnav.ls_object?c_name='
379 ||replace(c_name, '%', '*')
380 ||'&n_appid='
381 ||n_appid
385 end if;
382 ||'&c_type=LOOKUP'
383 , c_display => vlookup_count||' Lookup Types');
384 htp.p('</dd>');
386
387 if vfile_count > 0 then
388 htp.p('<dd>');
389 uiutil.a_href_gen(c_type => 'FILE'
390 , c_link => 'etrm_fndnav.ls_object?c_name='
391 ||replace(c_name, '%', '*')
392 ||'&n_appid='
393 ||n_appid
394 ||'&c_type=FILE'
395 , c_display => vfile_count||' Diagrams & Reports');
396 htp.p('</dd>');
397 end if;
398
399 if vevent_count > 0 then
400 htp.p('<dd>');
401 uiutil.a_href_gen(c_type => 'EVENT'
402 , c_link => 'etrm_fndnav.ls_object?c_name='
403 ||replace(c_name, '%', '*')
404 ||'&n_appid='
405 ||n_appid
406 ||'&c_type=EVENT'
407 , c_display => vevent_count||' Workflow Events');
408 htp.p('</dd>');
409 end if;
410
411 if v_counter > 0 then
412 htp.p('<dt>'||v_counter ||' objects</dt>');
413 end if;
414
415 end ls_app_itemcount;
416
417 procedure ls_count(n_appid in number := NULL
418 , c_name in dba_objects.object_name%type := '%'
419 , c_type in dba_objects.object_type%type := '%') is
420
421 cursor cur_apps is
422 select application_id
423 from fnd_application
424 order by decode(application_short_name, 'SQLAP', 'AP'
425 , 'SQLGL', 'GL'
426 , application_short_name);
427
428
429
430
431
432 v_name varchar2(80);
433 v_owner dba_objects.owner%type;
434 v_type dba_objects.object_type%type;
435 l_method varchar2(100) := 'ls_count';
436
437
438 begin
439 uiutil.cabo1;
440 uiutil.cabo2a;
441 draw_buttons;
442 uiutil.cabo2b;
443 draw_tabs;
444 uiutil.cabo3;
445
446 v_name := replace(c_name, '*', '%'); -- http drops trailing %
447 v_type := replace(c_type, '*', '%');
448 htp.p('<dl>');
449 htp.p('<dt><A HREF="etrm_fndnav.ls_object">'
450 ||uiutil.el_image('ROOT')
451 ||'</a></dt>');
452
453 if n_appid is NULL then
454 for a_rec in cur_apps loop
455 ls_app_itemcount(n_appid => a_rec.application_id
456 , c_name => v_name
457 , c_type => v_type);
458 end loop;
459 else
460 ls_app_itemcount(n_appid => n_appid
461 , c_name => v_name
462 , c_type => v_type);
463 end if;
464
465
466 htp.p('</dl>');
467
468 uiutil.cabo4;
469 quick_list;
470 uiutil.cabo5;
471 -- draw_form(n_appid => n_appid, c_name => c_name, c_type => c_type);
472 uiutil.cabo6;
473 end ls_count;
474
475 procedure ls_product(n_appid in number := NULL
476 , c_name in dba_objects.object_name%type := '%'
477 , c_type in dba_objects.object_type%type := '%') is
478
479 cursor cur_tables(c_name in dba_objects.object_name%type := '%'
480 , n_appid in number) is
481 select t.table_id id
482 , t.table_name name
483 from fnd_tables t
484 where t.application_id = n_appid
485 and t.table_name like c_name ESCAPE '\'
486 order by table_name;
487
488
489 cursor cur_views(c_name in dba_objects.object_name%type := '%'
490 , n_appid in number) is
491 select v.view_id id
492 , v.view_name name
493 from fnd_views v
494 where v.application_id = n_appid
495 and v.view_name like c_name ESCAPE '\'
496 order by view_name;
497
498 cursor cur_files(c_name in dba_objects.object_name%type := '%'
499 , n_appid in number) is
500 select f.file_id id
501 , f.file_name name
502 from fnd_etrm_files f
503 where f.application_id = n_appid
504 and upper(f.file_name) like c_name ESCAPE '\'
505 order by file_name;
506
507 cursor cur_concurrent(c_name in dba_objects.object_name%type := '%'
508 , n_appid in number) is
509 select c.concurrent_program_id id
510 , c.concurrent_program_name name
511 from fnd_concurrent_programs c
512 where c.application_id = n_appid
513 and c.concurrent_program_name like c_name ESCAPE '\'
514 order by c.concurrent_program_name;
515
516 cursor cur_lookup(c_name in dba_objects.object_name%type := '%'
517 , n_appid in number) is
518 select v.lookup_type id
519 , v.lookup_type name
520 from fnd_lookup_types v
521 where v.application_id = n_appid
522 and v.lookup_type like c_name ESCAPE '\'
523 order by lookup_type;
524
525 cursor cur_event(c_name in dba_objects.object_name%type := '%'
526 , n_appid in number) is
527 select e.name
528 , e.guid id
529 from wf_events e
533 and e.name like c_name ESCAPE '\';
530 , fnd_application a
531 where a.application_id = n_appid
532 and e.owner_tag = a.application_short_name
534
535
536 cursor cur_tables_and_views(c_name in dba_objects.object_name%type := '%'
537 , n_appid in number) is
538 select t.table_id id
539 , t.table_name name
540 , 'TABLE' o_type
541 from applsys.fnd_tables t
542 where t.application_id = n_appid
543 and t.table_name like c_name ESCAPE '\'
544 UNION
545 select v.view_id id
546 , v.view_name name
547 , 'VIEW' o_type
548 from applsys.fnd_views v
549 where v.application_id = n_appid
550 and v.view_name like c_name ESCAPE '\'
551 order by 2;
552
553 v_name varchar2(80);
554 v_counter integer := 0;
555
556
557 begin
558 uiutil.cabo1;
559 uiutil.cabo2a;
560 draw_buttons;
561 uiutil.cabo2b;
562 draw_tabs;
563 uiutil.cabo3;
564
565 v_name := replace(c_name, '*', '%'); -- http drops trailing %
566 htp.p('<dl>');
567 htp.p('<dt><A HREF="etrm_fndnav.ls_object">'
568 ||uiutil.el_image('ROOT')
569 ||'</a>');
570 -- htp.p(uiutil.el_image('APP')
571 -- ||'<b>'
572 -- ||get_appname(n_appid)
573 -- ||'</b></dt>');
574
575 uiutil.a_href_gen(c_type => 'APP'
576 , c_link => 'etrm_fndnav.ls_object?n_appid='||n_appid
577 || '&c_name=*&c_type=*'
578 , c_display => get_appname(n_appid));
579 htp.p('</dt>');
580
581
582 if c_type = 'TABLE' then
583 for a_rec in cur_tables(v_name, n_appid) loop
584 htp.p('<dt>');
585 uiutil.a_javascript_gen(c_type => 'TABLE'
586 , c_link => 'etrm_fndnav.show_object?n_appid='
587 || n_appid
588 ||'&n_tabid='
589 ||a_rec.id
590 ||'&c_type=TABLE'
591 , c_display => a_rec.name);
592 htp.p('</dt>');
593 v_counter := v_counter + 1;
594 end loop;
595 elsif c_type = 'VIEW' then
596 for a_rec in cur_views(v_name, n_appid) loop
597 htp.p('<dt>');
598 uiutil.a_javascript_gen(c_type => 'VIEW'
599 , c_link => 'etrm_fndnav.show_object?n_appid='
600 || n_appid
601 ||'&n_tabid='
602 ||a_rec.id
603 ||'&c_type=VIEW'
604 , c_display => a_rec.name);
605 htp.p('</dt>');
606 v_counter := v_counter + 1;
607 end loop;
608 elsif c_type = 'CONCURRENT' then
609 for a_rec in cur_concurrent(v_name, n_appid) loop
610 htp.p('<dt>');
611 uiutil.a_javascript_gen(c_type => 'CONCURRENT'
612 , c_link => 'etrm_fndnav.show_object?n_appid='
613 || n_appid
614 ||'&n_tabid='
615 ||a_rec.id
616 ||'&c_type=CONCURRENT'
617 , c_display => a_rec.name);
618 htp.p('</dt>');
619 v_counter := v_counter + 1;
620 end loop;
621 elsif c_type = 'LOOKUP' then
622 for a_rec in cur_lookup(v_name, n_appid) loop
623 htp.p('<dt>');
624 uiutil.a_javascript_gen(c_type => 'LOOKUP'
625 , c_link => 'etrm_fndnav.show_object?n_appid='
626 || n_appid
627 ||'&n_tabid='
628 ||a_rec.id
629 ||'&c_type=LOOKUP'
630 , c_display => a_rec.name);
631 htp.p('</dt>');
632 v_counter := v_counter + 1;
633 end loop;
634
635 elsif c_type = 'FILE' then
636 for a_rec in cur_files(v_name, n_appid) loop
637 htp.p('<dt>');
638 uiutil.a_javascript_gen(c_type => 'FILE'
639 , c_link => 'etrm_fndnav.show_object?n_appid='
640 || n_appid
641 ||'&n_tabid='
642 ||a_rec.id
643 ||'&c_type=FILE'
644 , c_display => a_rec.name);
645 htp.p('</dt>');
646 v_counter := v_counter + 1;
647 end loop;
648
649 elsif c_type = 'EVENT' then
650 for a_rec in cur_event(v_name, n_appid) loop
651 htp.p('<dt>');
652 uiutil.a_javascript_gen(c_type => 'EVENT'
653 , c_link => 'etrm_fndnav.show_object?n_appid='
654 || n_appid
655 ||'&n_tabid='
656 ||a_rec.id
657 ||'&c_type=EVENT'
658 , c_display => a_rec.name);
659 htp.p('</dt>');
660 v_counter := v_counter + 1;
661 end loop;
662
663 elsif c_type in ('%', '*') then
664 for a_rec in cur_tables_and_views(v_name, n_appid) loop
665 htp.p('<dt>');
666 uiutil.a_javascript_gen(c_type => rtrim(a_rec.o_type)
667 , c_link => 'etrm_fndnav.show_object?n_appid='
668 || n_appid
669 ||'&n_tabid='
670 ||a_rec.id
671 ||'&c_type='
672 ||rtrim(a_rec.o_type)
673 , c_display => a_rec.name);
674 htp.p('</dt>');
675 v_counter := v_counter + 1;
676 end loop;
677 else
678 htp.p('<dt>Application does not support objects of given type</dt>');
679 end if;
680
681
682 htp.p('<dt>'||v_counter ||' objects</dt>');
683 htp.p('</dl>');
684
685 uiutil.cabo4;
686 quick_list;
687 uiutil.cabo5;
688 -- draw_form(n_appid => n_appid, c_name=> c_name, c_type => c_type);
689 htp.p('<br>');
690 uiutil.cabo6;
691 end ls_product;
692
693
694
695 procedure ls_table(n_tabid in number
696 , n_appid in number) is
697
698 CURSOR cur_table_dets(n_tabid in number
699 , n_appid in number)
700 -- List table details
701 IS select table_name
702 , INITIAL_EXTENT
703 , NEXT_EXTENT
704 , MIN_EXTENTS
705 , MAX_EXTENTS
706 , PCT_INCREASE
707 , INI_TRANS
708 , MAX_TRANS
709 , PCT_FREE
710 , PCT_USED
711 , description
712 from applsys.fnd_tables
713 where application_id = n_appid
714 and table_id = n_tabid
715 order by table_name;
716 --
717
718 cursor cur_index(n_appid in fnd_indexes.application_id%type
719 , n_tabid in fnd_indexes.table_id%type)
720 -- List Indexes
721 is select ind.index_name
722 , ind.index_id
723 , decode(ind.uniqueness, 'N', 'Non Unique'
724 , 'Y', 'Unique') uniqueness
725 , ind.description
726 from fnd_indexes ind
727 where ind.application_id = n_appid
728 and ind.table_id = n_tabid
729 order by ind.uniqueness desc, ind.index_name;
730 --
731 cursor cur_index_col(n_appid in fnd_indexes.application_id%type
732 , n_tabid in fnd_indexes.table_id%type
733 , n_index in fnd_indexes.index_id%type)
734 -- List index columns
735 is select c.column_name
736 from fnd_index_columns i
737 , fnd_columns c
738 where i.application_id = n_appid
739 and i.table_id = n_tabid
740 and i.index_id = n_index
741 and c.application_id = i.application_id
742 and c.table_id = i.table_id
743 and c.column_id = i.column_id
744 order by i.column_sequence;
745 --
746 cursor cur_primary_key(n_tabid in number
747 , n_appid in number)
748 -- List the primary key
749 is SELECT PRIMARY_KEY_ID
750 , PRIMARY_KEY_NAME
751 , PRIMARY_KEY_TYPE
752 , ENABLED_FLAG
753 FROM APPLSYS.FND_PRIMARY_KEYS
754 where table_id = n_tabid
755 and application_id = n_appid
756 order by primary_key_name;
757
758 cursor cur_pk_columns(n_tabid in number
759 , n_appid in number
760 , n_keyid in number) is
761 SELECT c.column_name
762 from applsys.fnd_columns c
763 , applsys.fnd_primary_key_columns k
764 where k.table_id = c.table_id
765 and k.application_id = c.application_id
766 and k.column_id = c.column_id
767 and k.table_id = n_tabid
768 and k.application_id = n_appid
769 and k.primary_key_id = n_keyid
770 order by primary_key_sequence;
771
772
773 --
774 cursor cur_foreign_key(n_tabid in number
775 , n_appid in number)
776 -- List the primary key
777 is SELECT PRIMARY_KEY_APPLICATION_ID
778 , PRIMARY_KEY_TABLE_ID
779 , PRIMARY_KEY_ID
780 , foreign_key_id
781 , foreign_key_name
782 FROM APPLSYS.FND_FOREIGN_KEYS
783 where table_id = n_tabid
784 and application_id = n_appid
785 order by foreign_key_name;
786
787 cursor cur_fk_refs(n_tabid in number
788 , n_appid in number)
789 -- List the primary key
790 is SELECT PRIMARY_KEY_ID
791 , table_id
792 , application_id
793 , foreign_key_id
794 , foreign_key_name
795 FROM APPLSYS.FND_FOREIGN_KEYS
796 where primary_key_table_id = n_tabid
797 and primary_key_application_id = n_appid
798 order by foreign_key_name;
799
800 cursor cur_fk_columns(n_tabid in number
801 , n_appid in number
802 , n_keyid in number) is
803 SELECT c.column_name
804 from applsys.fnd_columns c
805 , applsys.fnd_foreign_key_columns k
806 where k.table_id = c.table_id
807 and k.application_id = c.application_id
808 and k.column_id = c.column_id
809 and k.table_id = n_tabid
810 and k.application_id = n_appid
811 and k.foreign_key_id = n_keyid
812 order by foreign_key_sequence;
813 --
814
815 cursor cur_columns(n_tabid in number
816 , n_appid in number)
817 is select col.column_name
818 , decode(col.column_type, 'N', 'Number'
819 , 'D', 'Date'
820 , 'V', 'Varchar2'
821 , 'L', 'Long'
822 , 'R', 'Raw'
823 , 'X', 'Long Raw'
824 , 'I', 'RowID'
825 , 'A', 'N-Clob'
826 , 'B', 'BLOB'
827 , 'E', 'CLOB'
828 , 'F', 'B-File'
829 , 'W', 'N-Char'
830 , 'M', 'MLS-Label'
831 , 'Z', 'Raw MLS-Label'
832 , 'U', 'Unknown', 'Unknown') data_type
833 , col.width data_length
834 , nvl(to_char(col.precision), '<br>') data_precision
835 , nvl(decode(col.null_allowed_flag, 'N', 'NOT_NULL'
836 , 'Y', '<br>'), '<br>') nullable
837 , nvl(description, '<br>') description
838 from applsys.fnd_columns col
839 where col.table_id = n_tabid
840 and col.application_id = n_appid
841 order by col.column_sequence;
842
843
844
845
846 --
847 -- Main Body
848 --
849
850 v_first BOOLEAN := TRUE;
851 v_first2 BOOLEAN := TRUE;
852 unexpected_error EXCEPTION;
853 loop_counter INTEGER;
854 v_datetime varchar2(100);
855 v_user varchar2(30);
856 v_tablename varchar2(128);
857 --
858 BEGIN
859 --
860
861 v_first := TRUE;
862 FOR tab_rec IN cur_table_dets(n_appid=>n_appid, n_tabid=>n_tabid) LOOP
863 htp.p('<h1>Table: '||tab_rec.table_name ||'</h1>');
864 v_tablename := tab_rec.table_name;
865 htp.p('<TABLE cellpadding="0" cellspacing="3"
866 summary="location information">');
867
868 htp.p('<tr><th scope="row" align="left"><b>Product: </b></th><td>'
869 || get_appname(n_appid)||'</td></tr>');
870 htp.p('<tr><th scope="row" align="left"><b>Description: </b></th><td>'
871 || tab_rec.description||'</td></tr>');
872
873 htp.p('<tr><th scope="row" align="left"><b>Implementation/DBA Data: </b></th><td>');
874 etrm_pnav.prn_object_type(c_name => tab_rec.table_name
875 , c_owner => '%'
876 , c_type => 'TABLE'
877 , c_status => '%');
878 htp.p('</td></tr>');
879
880 htp.p('</table>');
881 /*
882 htp.p('<h5>Storage Details</h5>');
883 htp.p('<TABLE cellpadding="0" cellspacing="3"
884 summary="dba information">');
885
886 htp.p('<tr><th scope="row" align="left"><b>Initial Extent: </b></th><td>'
887 || tab_rec.initial_extent||'</td></tr>');
888 htp.p('<tr><th scope="row" align="left"><b>Next Extent: </b></th><td>'
889 || tab_rec.next_extent||'</td></tr>');
890 htp.p('<tr><th scope="row" align="left"><b>Min Extents: </b></th><td>'
891 || tab_rec.min_extents||'</td></tr>');
892 htp.p('<tr><th scope="row" align="left"><b>Max Extents: </b></th><td>'
893 || tab_rec.max_extents||'</td></tr>');
894
895 htp.p('<tr><th scope="row" align="left"><b>% Increase: </b></th><td>'
896 || tab_rec.pct_increase||'</td></tr>');
897 htp.p('<tr><th scope="row" align="left"><b>Init Trans: </b></th><td>'
898 || tab_rec.ini_trans||'</td></tr>');
899 htp.p('<tr><th scope="row" align="left"><b>Max Trans: </b></th><td>'
900 || tab_rec.max_trans||'</td></tr>');
901 htp.p('<tr><th scope="row" align="left"><b>% Free: </b></th><td>'
902 ||tab_rec.pct_free||'</td></tr>');
903 htp.p('<tr><th scope="row" align="left"><b>% Used: </b></th><td>'
904 || tab_rec.pct_used||'</td></tr>');
905 htp.p('</table>');
906 */
907
908
909
910 end loop;
911 --
912
913
914 for key_rec in cur_primary_key(n_appid => n_appid, n_tabid => n_tabid) loop
915 htp.p('<h5>Primary Key: '|| key_rec.primary_key_name || '</h5>');
916 htp.p('<ol>');
917 for key_col in cur_pk_columns(n_appid => n_appid
918 , n_tabid => n_tabid
919 , n_keyid => key_rec.primary_key_id) loop
920 htp.p('<li>'
921 || uiutil.el_image('COL')
922 ||key_col.column_name
923 ||'</li>');
924 end loop;
925 htp.p('</ol>');
926 end loop;
927
928 v_first := TRUE;
929
930
934 v_first := FALSE;
931 for key_rec in cur_foreign_key(n_appid => n_appid
932 , n_tabid => n_tabid) loop
933 if v_first = TRUE then
935 htp.p('<h5>Foreign Keys</h5>');
936 htp.p('<table cellpadding="1" cellspacing="0" border="1"
937 summary="Foreign keys from/to this table">
938 <tr class="OraTableColumnHeader" >
939 <th class="OraTableColumnHeader" id="tab">Table</th>
940 <th class="OraTableColumnHeader" id="join"> <br></th>
941 <th class="OraTableColumnHeader" id="ftab">Foreign Table</th>
942 <th class="OraTableColumnHeader" id="col">Foreign Key Column</th></tr>');
943 end if;
944
945
946
947 htp.p('<tr class="OraTableCellText">');
948 htp.p('<td class="OraTableCellText" headers="tab">'
949 ||v_tablename
950 ||'</td>');
951 htp.p('<td class="OraTableCellText" headers="join">'
952 ||uiutil.el_image('FK')
953 || '</td>');
954 htp.p('<td class="OraTableCellText" headers="ftab">');
955 uiutil.a_href_gen(c_type => 'TABLE'
956 , c_link => 'etrm_fndnav.show_object?n_appid='
957 ||key_rec.primary_key_application_id
958 ||'&n_tabid='
959 ||key_rec.primary_key_table_id
960 ||'&c_type=TABLE'
961 , c_display => get_tablename(n_appid => key_rec.primary_key_application_id
962 , n_tabid => key_rec.primary_key_table_id));
963 htp.p('</td>');
964
965 v_first2 := TRUE;
966 htp.p('<td class="OraTableCellText" headers="col">');
967 for key_col in cur_fk_columns(n_appid => n_appid
968 , n_tabid => n_tabid
969 , n_keyid => key_rec.foreign_key_id) loop
970 if v_first2 = TRUE
971 then v_first2 := FALSE;
972 else
973 htp.p('<br>');
974 end if;
975 htp.p(get_tablename(n_appid, n_tabid)
976 ||'.<b>'
977 ||key_col.column_name
978 ||'</b>');
979 end loop;
980 htp.p('</td></tr>');
981 end loop;
982 --
983
984 for key_rec in cur_fk_refs(n_appid => n_appid
985 , n_tabid => n_tabid) loop
986 if v_first = TRUE then
987 v_first := FALSE;
988 htp.p('<h5>Foreign Keys</h5>');
989 htp.p('<table cellpadding="1" cellspacing="0" border="1"
990 summary="Foreign keys from/to this table">
991 <tr class="OraTableColumnHeader" >
992 <th class="OraTableColumnHeader" id="tab">Table</th>
993 <th class="OraTableColumnHeader" id="join"> <br></th>
994 <th class="OraTableColumnHeader" id="ftab">Foreign Table</th>
995 <th class="OraTableColumnHeader" id="col">Foreign Key Column</th></tr>');
996 end if;
997
998 htp.p('<tr class="OraTableCellText">');
999 htp.p('<td class="OraTableCellText" headers="tab">');
1000 uiutil.a_href_gen(c_type => 'TABLE'
1001 , c_link => 'etrm_fndnav.show_object?n_appid='
1002 ||key_rec.application_id
1003 ||'&n_tabid='
1004 ||key_rec.table_id
1005 ||'&c_type=TABLE'
1006 , c_display => get_tablename(n_appid => key_rec.application_id
1007 , n_tabid => key_rec.table_id));
1008 htp.p('</td>');
1009 htp.p('<td class="OraTableCellText" headers="join">'
1010 ||uiutil.el_image('FK')
1011 ||'</td>');
1012 htp.p('<td class="OraTableCellText" headers="ftab">');
1013 htp.p(v_tablename||'</td>');
1014
1015 v_first2 := TRUE;
1016 htp.p('<td class="OraTableCellText" headers="col">');
1017 for key_col in cur_fk_columns(n_appid => key_rec.application_id
1018 , n_tabid => key_rec.table_id
1019 , n_keyid => key_rec.foreign_key_id) loop
1020 if v_first2 = TRUE
1021 then v_first2 := FALSE;
1022 else
1023 htp.p('<br>');
1024 end if;
1025
1026 htp.p(get_tablename(key_rec.application_id, key_rec.table_id)
1027 ||'<b>.'
1028 ||key_col.column_name
1029 ||'</b>');
1030 end loop;
1031 htp.p('</td></tr>');
1032
1033 end loop;
1034
1035 if v_first
1036 then null;
1037 else
1038 htp.p('</table>');
1039 end if;
1040
1041 --
1042
1043 /*
1044
1045 for ind_rec in cur_index(n_appid, n_tabid) loop
1046 htp.p('<h5>'
1047 ||ind_rec.uniqueness
1048 ||' Index: '
1049 ||ind_rec.index_name
1050 ||'</h5>');
1051
1052 v_first := TRUE;
1053
1054 for ind_col_rec in cur_index_col(n_appid, n_tabid, ind_rec.index_id) loop
1055 IF v_first Then
1056 htp.p('<ol>');
1057 v_first := FALSE;
1061 ||ind_col_rec.column_name
1058 end if;
1059 htp.p('<li>'
1060 ||uiutil.el_image('COL')
1062 ||'</li>');
1063 end loop;
1064
1065 IF v_first Then
1066 htp.p('<p>'||ind_rec.index_name|| ' has no columns<p>');
1067 else
1068 htp.p('</ol>');
1069 v_first := TRUE;
1070 end if;
1071
1072 end loop;
1073
1074
1075 v_first := TRUE;
1076 for col_rec in cur_columns(n_tabid, n_appid) loop
1077 IF v_first Then
1078
1079 htp.p('<h5> Columns</h5>');
1080 htp.p('<table cellpadding=1 cellspacing=0 border=1
1081 summary="Column details for this table">');
1082 htp.p('<TR>');
1083 htp.p('<TH class="OraTableColumnHeader" id="name">Name</TH>');
1084 htp.p('<TH class="OraTableColumnHeader" id="datatype"> Datatype</TH>');
1085 htp.p('<TH class="OraTableColumnHeader" id="length">Length</TH>');
1086 htp.p('<TH class="OraTableColumnHeader" id="prcn">Prcn</TH>');
1087 htp.p('<TH class="OraTableColumnHeader" id="null">Null</TH>');
1088 htp.p('<TH class="OraTableColumnHeader" id="description">Description</TH></TR>');
1089 v_first := FALSE;
1090 end if;
1091
1092 htp.prn('<TR> <TD Class="OraTableCellText" headers="name">');
1093 htp.prn(col_rec.column_name);
1094 htp.p('</TD>');
1095 htp.prn('<TD Class="OraTableCellText" headers="datatype">');
1096 htp.prn(col_rec.data_type);
1097 htp.p('</TD>');
1098 htp.prn('<TD Class="OraTableCellText" headers="length">');
1099 htp.prn(col_rec.data_length);
1100 htp.p('</TD>');
1101 htp.prn('<TD Class="OraTableCellText" headers="prcn">');
1102 htp.prn(col_rec.data_precision);
1103 htp.p('</TD>');
1104 htp.prn('<TD Class="OraTableCellText" headers="null">');
1105 htp.prn(col_rec.nullable);
1106 htp.prn('<TD Class="OraTableCellText" headers="description">');
1107 htp.prn(col_rec.description);
1108 htp.p('</TD></TR>');
1109 end loop;
1110 if v_first then
1111 null;
1112 else
1113 htp.p('</table>');
1114 end if;
1115 */
1116 --
1117 EXCEPTION
1118 WHEN unexpected_error THEN
1119 RAISE_APPLICATION_ERROR(-20003,
1120 'An unexpected error has occured in the script. This is an internal error - not normally issued'
1121 );
1122 END ls_table;
1123
1124
1125 procedure ls_view(n_tabid in number
1126 , n_appid in number) is
1127
1128 CURSOR cur_view_dets(n_tabid in number
1129 , n_appid in number)
1130 -- List view details
1131 IS select view_name
1132 , description
1133 , text
1134 from applsys.fnd_views
1135 where view_id = n_tabid
1136 and application_id = n_appid
1137 order by view_name;
1138 --
1139
1140 cursor cur_columns(n_tabid in number
1141 , n_appid in number)
1142 is select col.column_name
1143 from applsys.fnd_view_columns col
1144 where col.view_id = n_tabid
1145 and col.application_id = n_appid
1146 order by col.column_sequence;
1147
1148
1149
1150
1151 --
1152 -- Main Body
1153 --
1154
1155 v_first BOOLEAN := TRUE;
1156 unexpected_error EXCEPTION;
1157 loop_counter INTEGER;
1158 v_datetime varchar2(100);
1159 v_user varchar2(30);
1160 v_tablename varchar2(128);
1161 v_viewtext varchar2(32767);
1162 --
1163 BEGIN
1164 --
1165
1166 v_first := TRUE;
1167 FOR tab_rec IN cur_view_dets(n_appid=>n_appid, n_tabid=>n_tabid) LOOP
1168 htp.p('<h1>View: '||tab_rec.view_name ||'</h1>');
1169 v_tablename := tab_rec.view_name;
1170 htp.p('<TABLE cellpadding="0" cellspacing="3"
1171 summary="location information">');
1172
1173 htp.p('<tr><th scope="row" align="left"><b>Product: </b></th><td>'
1174 || get_appname(n_appid)||'</td></tr>');
1175 htp.p('<tr><th scope="row" align="left"><b>Description: </b></th><td>'
1176 || tab_rec.description||'</td></tr>');
1177 htp.p('<tr><th scope="row" align="left"><b>Implementation/DBA Data: </b></th><td>');
1178 etrm_pnav.prn_object_type(c_name => tab_rec.view_name
1179 , c_owner => '%'
1180 , c_type => 'VIEW'
1181 , c_status => '%');
1182 htp.p('</td></tr>');
1183 htp.p('</table>');
1184
1185 v_viewtext := upper(tab_rec.text);
1186 v_viewtext := replace(v_viewtext , 'SELECT ', '<b>SELECT </b>');
1187 v_viewtext := replace(v_viewtext, ' FROM ', '<br><b> FROM </b>');
1188 v_viewtext := replace(v_viewtext , ' WHERE ', '<br><b> WHERE </b>');
1189 v_viewtext := replace(v_viewtext , ',', '<br>, ');
1190 v_viewtext := replace(v_viewtext , ' AND ', '<br> AND ');
1191
1192 htp.p('<h5>View Text</h5>');
1193 htp.p('<p>');
1197 --
1194 htp.p(v_viewtext);
1195 htp.p('</p>');
1196 end loop;
1198
1199 v_first := TRUE;
1200 for col_rec in cur_columns(n_tabid, n_appid) loop
1201 IF v_first Then
1202
1203 htp.p('<h5> Columns</h5>');
1204 htp.p('<table cellpadding=1 cellspacing=0 border=1
1205 summary="Column details for this table">');
1206 htp.p('<TR>');
1207 htp.p('<TH class="OraTableColumnHeader" id="name">Name</TH>');
1208
1209 v_first := FALSE;
1210 end if;
1211
1212 htp.prn('<TR> <TD Class="OraTableCellText" headers="name">');
1213 htp.prn(col_rec.column_name);
1214 htp.p('</TD>');
1215 htp.p('</TD></TR>');
1216 end loop;
1217
1218 --
1219 EXCEPTION
1220 WHEN unexpected_error THEN
1221 RAISE_APPLICATION_ERROR(-20003,
1222 'An unexpected error has occured in the script. This is an internal error - not normally issued'
1223 );
1224 END ls_view;
1225
1226 procedure ls_concurrent(n_tabid in number
1227 , n_appid in number) is
1228
1229 cursor cur_concurrent(n_tabid in number
1230 , n_appid in number) is
1231 select t.user_concurrent_program_name long_name
1232 , t.description
1233 , c.concurrent_program_name name
1234 , c.execution_method_code
1235 , c.argument_method_code
1236 , c.queue_control_flag
1237 , c.queue_method_code
1238 , c.request_set_flag
1239 , c.enabled_flag
1240 , c.print_flag
1241 , c.run_alone_flag
1242 , c.srs_flag
1243 , c.save_output_flag
1244 , c.required_style
1245 from fnd_concurrent_programs_tl t
1246 , fnd_concurrent_programs c
1247 where c.application_id = n_appid
1248 and c.concurrent_program_id = n_tabid
1249 and c.concurrent_program_id = t.concurrent_program_id
1250 and c.application_id = t.application_id
1251 and t.language = userenv('LANG');
1252
1253 cursor cur_executable(n_tabid in number
1254 , n_appid in number) is
1255 select e.executable_name name
1256 , e.execution_method_code
1257 , e.execution_file_name
1258 , e.subroutine_name
1259 , t.user_executable_name
1260 , t.description
1261 from fnd_executables e
1262 , fnd_concurrent_programs c
1263 , fnd_executables_tl t
1264 where e.application_id = n_appid
1265 and c.concurrent_program_id = n_tabid
1266 and c.executable_id = e.executable_id
1267 and e.application_id = t.application_id
1268 and e.executable_id = t.executable_id
1269 and t.language = userenv('LANG');
1270
1271 cursor cur_args(n_progid in number
1272 , n_appid in number) is
1273 SELECT a.DESCRIPTIVE_FLEX_APPL_ID
1274 , a.DESCRIPTIVE_FLEXFIELD_NAME
1275 , a.APPLICATION_COLUMN_NAME
1276 , a.DISPLAY_FLAG
1277 , a.INSERT_FLAG
1278 , a.UPDATE_FLAG
1279 , a.DEFAULT_TYPE
1280 , a.DEFAULT_VALUE
1281 , a.SHARED_PARAMETER_NAME
1282 FROM APPLSYS.FND_REQUEST_SET_PROGRAMS p
1283 , APPLSYS.FND_REQUEST_SET_PROGRAM_ARGS a
1284 where p.PROGRAM_APPLICATION_ID = n_appid
1285 and p.CONCURRENT_PROGRAM_ID = n_progid
1286 and a.APPLICATION_ID = p.SET_APPLICATION_ID
1287 and a.REQUEST_SET_ID = p.REQUEST_SET_ID
1288 and a.REQUEST_SET_PROGRAM_ID = p.REQUEST_SET_PROGRAM_ID
1289 order by application_column_name;
1290
1291 v_first boolean;
1292
1293 begin
1294
1295
1296
1297 FOR c_rec IN cur_concurrent(n_appid=>n_appid, n_tabid=>n_tabid) LOOP
1298 htp.p('<h1>Concurrent Program: '||c_rec.name ||'</h1>');
1299
1300 htp.p('<TABLE cellpadding="0" cellspacing="3"
1301 summary="location information">');
1302
1303 htp.p('<tr><th scope="row" align="left"><b>Product: </b></th><td>'
1304 || get_appname(n_appid)||'</td></tr>');
1305
1306 htp.p('<tr><th scope="row" align="left"><b>User Name: </b></th><td>'
1307 || c_rec.long_name||'</td></tr>');
1308
1309 htp.p('<tr><th scope="row" align="left"><b>Description: </b></th><td>'
1310 || c_rec.description||'</td></tr>');
1311
1312 htp.p('<tr><th scope="row" align="left"><b>Enabled: </b></th><td>'
1313 || get_lookup_meaning(c_lookup_type => 'YES_NO'
1314 , c_lookup_code =>c_rec.enabled_flag)
1315 ||'</td></tr>');
1316
1317 htp.p('<tr><th scope="row" align="left"><b>Execution Method: </b></th><td>'
1318 || get_lookup_meaning(c_lookup_type => 'CP_EXECUTION_METHOD_CODE'
1319 , c_lookup_code =>c_rec.execution_method_code)
1320 ||'</td></tr>');
1321
1322 htp.p('<tr><th scope="row" align="left"><b>Argument Method: </b></th><td>'
1323 || get_lookup_meaning(c_lookup_type => 'CP_ARGUMENT_METHOD_CODE'
1324 , c_lookup_code =>c_rec.argument_method_code)
1325 ||'</td></tr>');
1326 htp.p('</table>');
1327 END LOOP;
1328
1329
1333 summary="location information">');
1330 for e_rec in cur_executable(n_appid=>n_appid, n_tabid=>n_tabid) loop
1331 htp.p('<h3>Executable: '||e_rec.name ||'</h3>');
1332 htp.p('<TABLE cellpadding="0" cellspacing="3"
1334
1335 htp.p('<tr><th scope="row" align="left"><b>User Name: </b></th><td>'
1336 || e_rec.user_executable_name||'</td></tr>');
1337
1338 htp.p('<tr><th scope="row" align="left"><b>Description: </b></th><td>'
1339 || e_rec.description||'</td></tr>');
1340
1341 htp.p('<tr><th scope="row" align="left"><b>Execution Filename: </b></th><td>'
1342 || e_rec.execution_file_name||'</td></tr>');
1343
1344 htp.p('<tr><th scope="row" align="left"><b>Subroutine Name: </b></th><td>'
1345 || e_rec.subroutine_name||'</td></tr>');
1346
1347 htp.p('</table>');
1348
1349 end loop;
1350
1351
1352 v_first := TRUE;
1353 for a_rec in cur_args(n_progid => n_tabid, n_appid => n_appid) loop
1354 IF v_first Then
1355
1356 htp.p('<h5>Request Set Arguments</h5>');
1357 htp.p('<table cellpadding=1 cellspacing=0 border=1
1358 summary="Concurrent program arguments">');
1359 htp.p('<TR>');
1360 htp.p('<TH class="OraTableColumnHeader" id="name">Name</TH>');
1361 htp.p('<TH class="OraTableColumnHeader" id="df"> Descriptive Flex</TH>');
1362 htp.p('<TH class="OraTableColumnHeader" id="disp">Display</TH>');
1363 htp.p('<TH class="OraTableColumnHeader" id="ins">Insert</TH>');
1364 htp.p('<TH class="OraTableColumnHeader" id="upd">Update</TH>');
1365 htp.p('<TH class="OraTableColumnHeader" id="type">Default Type</TH>');
1366 htp.p('<TH class="OraTableColumnHeader" id="val">Default Value</TH>');
1367 htp.p('<TH class="OraTableColumnHeader" id="sname">Shared Name</TH>');
1368 v_first := FALSE;
1369 end if;
1370
1371 htp.prn('<TR> <TD Class="OraTableCellText" headers="name">');
1372 htp.prn(a_rec.application_column_name);
1373 htp.p('</TD>');
1374 htp.prn('<TD Class="OraTableCellText" headers="df">');
1375 htp.prn(a_rec.descriptive_flexfield_name);
1376 htp.p('</TD>');
1377 htp.prn('<TD Class="OraTableCellText" headers="disp">');
1378 htp.prn(a_rec.display_flag);
1379 htp.p('</TD>');
1380 htp.prn('<TD Class="OraTableCellText" headers="ins">');
1381 htp.prn(a_rec.insert_flag);
1382 htp.p('</TD>');
1383 htp.prn('<TD Class="OraTableCellText" headers="upd">');
1384 htp.prn(a_rec.update_flag);
1385 htp.prn('<TD Class="OraTableCellText" headers="type">');
1386 htp.prn(a_rec.default_type);
1387 htp.prn('<TD Class="OraTableCellText" headers="val">');
1388 htp.prn(a_rec.default_value);
1389 htp.prn('<TD Class="OraTableCellText" headers="sname">');
1390 htp.prn(a_rec.shared_parameter_name);
1391 htp.p('</TD></TR>');
1392 end loop;
1393 if v_first then
1394 null;
1395 else
1396 htp.p('</table>');
1397 end if;
1398
1399
1400
1401 end ls_concurrent;
1402
1403
1404 procedure ls_lookup(c_lookup_type in varchar2
1405 , n_appid in number) is
1406
1407 cursor cur_lookup_tl(c_lookup_type in varchar2
1408 , n_appid in number) is
1409 select t.meaning
1410 , t.description
1411 from fnd_lookup_types_tl t
1412 , fnd_lookup_types l
1413 where l.application_id = n_appid
1414 and l.lookup_type = c_lookup_type
1415 and l.lookup_type = t.lookup_type
1416 and t.language = userenv('LANG');
1417
1418
1419 cursor cur_lookup_values(c_lookup_type in varchar2
1420 , n_appid in number) is
1421 select t.lookup_code
1422 , t.meaning
1423 , t.description
1424 from fnd_lookup_values t
1425 , fnd_lookup_types l
1426 where l.application_id = n_appid
1427 and l.lookup_type = c_lookup_type
1428 and l.lookup_type = t.lookup_type
1429 and l.security_group_id = t.security_group_id
1430 and l.view_application_id = t.view_application_id
1431 and t.language = userenv('LANG')
1432 order by t.lookup_code;
1433
1434 v_first boolean;
1435
1436 begin
1437
1438 htp.p('<h1>Lookup Type: '|| FND_CSS_PKG.Encode(c_lookup_type) ||'</h1>');
1439 FOR c_rec IN cur_lookup_tl(n_appid=>n_appid, c_lookup_type => c_lookup_type) LOOP
1440
1441
1442 htp.p('<TABLE cellpadding="0" cellspacing="3"
1443 summary="location information">');
1444
1445 htp.p('<tr><th scope="row" align="left"><b>Product: </b></th><td>'
1446 || get_appname(n_appid)||'</td></tr>');
1447
1448 htp.p('<tr><th scope="row" align="left"><b>Meaning: </b></th><td>'
1449 || c_rec.meaning||'</td></tr>');
1450
1451 htp.p('<tr><th scope="row" align="left"><b>Description: </b></th><td>'
1452 || c_rec.description||'</td></tr>');
1453 htp.p('</table>');
1454 END LOOP;
1458 IF v_first Then
1455 v_first := TRUE;
1456 for v_rec in cur_lookup_values(n_appid => n_appid
1457 , c_lookup_type => c_lookup_type) loop
1459
1460 htp.p('<h5>Quick Codes</h5>');
1461 htp.p('<table cellpadding=1 cellspacing=0 border=1
1462 summary="Quick Code details">');
1463 htp.p('<TR>');
1464 htp.p('<TH class="OraTableColumnHeader" id="code">Code</TH>');
1465 htp.p('<TH class="OraTableColumnHeader" id="meaning">Meaning</TH>');
1466 htp.p('<TH class="OraTableColumnHeader" id="description">Description</TH></TR>');
1467 v_first := FALSE;
1468 end if;
1469
1470 htp.prn('<TR> <TD Class="OraTableCellText" headers="code">');
1471 htp.prn(v_rec.lookup_code);
1472 htp.p('</TD>');
1473 htp.prn('<TD Class="OraTableCellText" headers="meaning">');
1474 htp.prn(v_rec.meaning);
1475 htp.p('</TD>');
1476 htp.prn('<TD Class="OraTableCellText" headers="description">');
1477 htp.prn(v_rec.description);
1478 htp.p('</TD></TR>');
1479 end loop;
1480 if v_first then
1481 null;
1482 else
1483 htp.p('</table>');
1484 end if;
1485
1486 end ls_lookup;
1487
1488 procedure ls_event(c_guid in varchar2) is
1489
1490 cursor cur_event(c_guid in varchar2) is
1491 SELECT NAME
1492 , TYPE
1493 , STATUS
1494 , GENERATE_FUNCTION
1495 , OWNER_NAME
1496 , OWNER_TAG
1497 FROM WF_EVENTS
1498 where GUID = c_guid;
1499
1500 cursor cur_subscriptions(c_guid in varchar2) is
1501 select s.wf_process_name
1502 , s.wf_process_type
1503 , s.rule_function
1504 , s.source_type
1505 , s.description
1506 from wf_event_subscriptions s
1507 where s.event_filter_guid = c_guid
1508 order by source_type;
1509
1510 v_first boolean := true;
1511
1512 begin
1513 for e_rec in cur_event(c_guid) loop
1514 htp.p('<h1>Event: '||e_rec.name ||'</h1>');
1515
1516 htp.p('<TABLE cellpadding="0" cellspacing="3"
1517 summary="Workflow Event information">');
1518
1519 htp.p('<tr><th scope="row" align="left"><b>Product: </b></th><td>'
1520 || e_rec.owner_tag||' - '||e_rec.owner_name||'</td></tr>');
1521 htp.p('<tr><th scope="row" align="left"><b>Type: </b></th><td>'
1522 || e_rec.type||'</td></tr>');
1523 htp.p('<tr><th scope="row" align="left"><b>Status: </b></th><td>'
1524 || e_rec.status||'</td></tr>');
1525 htp.p('<tr><th scope="row" align="left"><b>Generate Function: </b></th><td>'
1526 || e_rec.generate_function||'</td></tr>');
1527 htp.p('</table>');
1528
1529 end loop;
1530
1531
1532 for s_rec in cur_subscriptions(c_guid) loop
1533 IF v_first Then
1534
1535 htp.p('<h5>Subscriptions:</h5>');
1536 htp.p('<table cellpadding=1 cellspacing=0 border=1
1537 summary="Subscription details">');
1538 htp.p('<TR>');
1539 htp.p('<TH class="OraTableColumnHeader" id="source_type">Source Type</TH>');
1540 htp.p('<TH class="OraTableColumnHeader" id="rule">Rule Function</TH>');
1541 htp.p('<TH class="OraTableColumnHeader" id="process_type">WF Process Type</TH>');
1542 htp.p('<TH class="OraTableColumnHeader" id="process">WF Process Name</TH>');
1543 htp.p('<TH class="OraTableColumnHeader" id="description">Description</TH></TR>');
1544 v_first := FALSE;
1545 end if;
1546
1547 htp.prn('<TR> <TD Class="OraTableCellText" headers="source_type">');
1548 htp.prn(s_rec.source_type);
1549 htp.p('</TD>');
1550 htp.prn('<TD Class="OraTableCellText" headers="rule">');
1551 htp.prn(s_rec.rule_function);
1552 htp.p('</TD>');
1553
1554 htp.prn('<TD Class="OraTableCellText" headers="process_type">');
1555 htp.prn(s_rec.wf_process_type);
1556 htp.p('</TD>');
1557 htp.prn('<TD Class="OraTableCellText" headers="process">');
1558 htp.prn(s_rec.wf_process_name);
1559 htp.p('</TD>');
1560
1561 htp.prn('<TD Class="OraTableCellText" headers="description">');
1562 htp.prn(s_rec.description);
1563 htp.p('</TD></TR>');
1564 end loop;
1565 if v_first then
1566 null;
1567 else
1568 htp.p('</table>');
1569 end if;
1570
1571 end ls_event;
1572
1573 procedure ls_file(n_fileid in number) is
1574
1575 cursor cur_file(n_fileid in number) is
1576 select APPLICATION_ID
1577 , FILE_NAME
1578 , FILE_TYPE
1579 , DESCRIPTION
1580 , dbms_lob.getlength(FILE_CONTENT) file_length
1581 from fnd_etrm_files
1582 where file_id = n_fileid;
1583
1584
1585
1586 v_first boolean;
1587
1588 begin
1589
1590
1591 FOR c_rec IN cur_file(n_fileid=>n_fileid) LOOP
1592
1593 htp.p('<h1>File: '||c_rec.file_name ||'</h1>');
1594
1595 htp.p('<TABLE cellpadding="0" cellspacing="3"
1599 || get_appname(c_rec.application_id)
1596 summary="location information">');
1597
1598 htp.p('<tr><th scope="row" align="left"><b>Product: </b></th><td>'
1600 ||'</td></tr>');
1601
1602 htp.p('<tr><th scope="row" align="left"><b>File Type: </b></th><td>'
1603 || c_rec.file_type||'</td></tr>');
1604
1605 htp.p('<tr><th scope="row" align="left"><b>Size: </b></th><td>'
1606 || uiutil.byte_format(c_rec.file_length)
1607 ||'</td></tr>');
1608
1609
1610 if (c_rec.description is null
1611 or c_rec.description = c_rec.file_name)
1612 then null;
1613 else
1614 htp.p('<tr><th scope="row" align="left"><b>Description: </b></th><td>'
1615 || c_rec.description||'</td></tr>');
1616 end if;
1617
1618 htp.p('</table>');
1619
1620 END LOOP;
1621
1622 htp.p('<dl>');
1623 htp.p('<dt><b>Action:</b></dt>');
1624 htp.p('<dd>');
1625 uiutil.a_href_gen(c_type => 'FILE'
1626 , c_link => 'etrm_fndnav.show_file?n_file_id='
1627 ||n_fileid
1628 ||'&c_mode=INLINE'
1629 , c_display => 'View file');
1630 htp.p(' in current window');
1631 htp.p('</dd>');
1632 htp.p('</dl>');
1633
1634
1635 end ls_file;
1636
1637 -----------------------------------------------------------------------------
1638 -- Public Procedures
1639 -----------------------------------------------------------------------------
1640 PROCEDURE show_file(n_file_id IN fnd_etrm_files.file_id%type
1641 , c_mode in varchar2 := 'ATTACH')
1642 is
1643 cursor cur_get_lob(n_file_id IN fnd_etrm_files.file_id%type)
1644 is
1645 select file_content
1646 , file_name
1647 , file_content_type
1648 from fnd_etrm_files
1649 where file_id = n_file_id;
1650
1651 buffer raw(32767);
1652 amount binary_integer := 32767;
1653 position integer := 1;
1654 chunksize integer;
1655 lob_bytes_remaining number(10);
1656
1657 v_disposition varchar2(32) := 'attachment';
1658
1659 begin
1660 for lob_rec in cur_get_lob(n_file_id) loop
1661 lob_bytes_remaining := dbms_lob.getlength(lob_rec.file_content);
1662
1663 if upper(c_mode) = 'INLINE'
1664 then v_disposition := 'inline';
1665 else
1666 v_disposition := 'attachment';
1667 end if;
1668
1669 if upper(c_mode) = 'NOHEADER'
1670 then null;
1671 else
1672 htp.p('Content-type: '||lob_rec.file_content_type);
1673 htp.p('Content-Disposition: '|| v_disposition ||'; filename='||lob_rec.file_name);
1674 htp.p('Content-Transfer-Encoding: base64');
1675 htp.p('');
1676 end if;
1677
1678 chunksize := dbms_lob.getchunksize(lob_rec.file_content);
1679 if (chunksize < 32767) then
1680 amount := (32767 / chunksize) * chunksize;
1681 end if;
1682 dbms_lob.open(lob_rec.file_content, DBMS_LOB.LOB_READONLY);
1683
1684 while (lob_bytes_remaining > 0) loop
1685 if (lob_bytes_remaining < amount) then
1686 amount := lob_bytes_remaining;
1687 end if;
1688 dbms_lob.read(lob_rec.file_content, amount, position, buffer);
1689 htp.prn(utl_raw.cast_to_varchar2(buffer));
1690 position := position + amount;
1691 lob_bytes_remaining := lob_bytes_remaining - amount;
1692 end loop;
1693 dbms_lob.close(lob_rec.file_content);
1694 end loop;
1695 end show_file;
1696
1697
1698
1699 procedure ls_apps is
1700
1701 cursor cur_apps
1702 is
1703 select app.application_id
1704 , decode(app.application_short_name, 'SQLAP', 'AP'
1705 , 'SQLGL', 'GL'
1706 , app.application_short_name)
1707 || ' - '|| tl.application_name product_name
1708 from APPLSYS.FND_APPLICATION_TL tl
1709 , APPLSYS.FND_APPLICATION app
1710 where app.application_id = tl.application_id
1711 and tl.language = userenv('LANG')
1712 order by decode(app.application_short_name, 'SQLAP', 'AP'
1713 , 'SQLGL', 'GL'
1714 , app.application_short_name);
1715
1716
1717
1718 vsearch_name varchar2(80);
1719 vapps_installation boolean := TRUE;
1720 fnd_name varchar2(50);
1721
1722
1723
1724 begin
1725
1726
1727 uiutil.cabo1;
1728 uiutil.cabo2a;
1729 draw_buttons;
1730 uiutil.cabo2b;
1731 draw_tabs;
1732 uiutil.cabo3;
1733 htp.p('<h3>Products</h3>');
1734 htp.p('<dl>');
1735 for s_rec in cur_apps loop
1736
1737 htp.p('<dt>');
1738 uiutil.a_href_gen(c_type => 'APP'
1739 , c_link => 'etrm_fndnav.ls_object?c_name=*'
1740 ||'&n_appid='
1741 ||s_rec.application_id
1742 , c_display => s_rec.product_name);
1743
1747 uiutil.cabo4;
1744 htp.p('</dt>');
1745 end loop;
1746 htp.p('</dl>');
1748 quick_list;
1749 uiutil.cabo5;
1750 -- draw_form;
1751 uiutil.cabo6;
1752 end ls_apps;
1753 procedure ls_object(n_appid in number := NULL
1754 , c_name in dba_objects.object_name%type := '%'
1755 , c_type in dba_objects.object_type%type := '%') is
1756
1757 v_context varchar2(1024);
1758 v_name dba_objects.object_name%type;
1759 v_type dba_objects.object_type%type;
1760 l_method varchar2(100) := 'ls_object';
1761 begin
1762
1763 v_context := 'n_appid=' ||n_appid||
1764 '&c_name=' ||replace(c_name, '%', '*')||
1765 '&c_type=' ||replace(c_type, '%', '*'); -- http drops trailing %
1766 v_name := replace(upper(c_name), '*', '%');
1767 v_type := replace(c_type, '*', '%');
1768
1769 -- Write context information to cookie
1770 -- This wraps the following packages in an HTTP header
1771 owa_util.mime_header('text/html', FALSE);
1772
1773 -- Send some value to the target cookie for the next browser call
1774 owa_cookie.send('etrm_fndnav_context', v_context);
1775
1776 -- Note: The following would cause the target cookie to expire
1777 -- owa_cookie.remove('etrm_fndnav_context');
1778
1779 owa_util.http_header_close;
1780
1781 if (c_type not in ('TABLE', 'VIEW', 'CONCURRENT', 'LOOKUP', 'FILE', 'EVENT', '%', '*')) then
1782 uiutil.cabo1;
1783 uiutil.cabo2a;
1784 draw_buttons;
1785 uiutil.cabo2b;
1786 draw_tabs;
1787 uiutil.cabo3;
1788 htp.p('<dt>Application does not support objects of given type</dt>');
1789 uiutil.cabo4;
1790 quick_list;
1791 uiutil.cabo5;
1792 -- draw_form(n_appid => n_appid, c_name => c_name, c_type => c_type);
1793 uiutil.cabo6;
1794 elsif (v_name = '%'
1795 and n_appid is NULL)
1796 then ls_apps;
1797 elsif (v_name != '%'
1798 and n_appid IS NULL)
1799 then
1800 ls_count(n_appid => n_appid
1801 , c_name => v_name
1802 , c_type => v_type);
1803 elsif (v_name = '%'
1804 and v_type = '%')
1805 then
1806 ls_count(n_appid => n_appid
1807 , c_name => v_name
1808 , c_type => v_type);
1809
1810 else
1811 ls_product(n_appid => n_appid
1812 , c_name => v_name
1813 , c_type => v_type);
1814 end if;
1815 end ls_object;
1816
1817 procedure show_object(n_tabid in varchar2
1818 , n_appid in number
1819 , c_type in dba_objects.object_type%type := '%')
1820 -- n_tabid datatype is varchar because lookup types do not have a numeric id
1821 is
1822 begin
1823 uiutil.cabo1(c_title=>'FND Design Data');
1824 htp.p('<body bgcolor="#ffffff" onload="javascript:window.focus();">');
1828 if c_type = 'TABLE' then
1825 htp.p('<img src="/images/fnd_header.gif" alt="FND Design Data">
1826 <hr size="2" width="100%" noshade align="left">');
1827
1829 ls_table(n_tabid => to_number(n_tabid), n_appid => n_appid);
1830 elsif c_type = 'VIEW' then
1831 ls_view(n_tabid => to_number(n_tabid), n_appid => n_appid);
1832 elsif c_type = 'CONCURRENT' then
1833 ls_concurrent(n_tabid => to_number(n_tabid), n_appid => n_appid);
1834 elsif c_type = 'LOOKUP' then
1835 ls_lookup(c_lookup_type => n_tabid, n_appid => n_appid);
1836 elsif c_type = 'FILE' then
1837 ls_file(n_fileid => to_number(n_tabid));
1838 elsif c_type = 'EVENT' then
1839 ls_event(c_guid => n_tabid);
1840
1841 else
1842 htp.p(FND_CSS_PKG.Encode(c_type) ||' objects are not currently supported');
1843 end if;
1844
1845 htp.p('</p>');
1846 uiutil.prn_copyright;
1847 htp.p('</body>');
1848 end show_object;
1849
1850 function get_owner(c_name in dba_objects.object_name%type
1851 , c_type in dba_objects.object_type%type)
1852 return varchar2 is
1853
1854 cursor cur_table(c_name in dba_objects.object_name%type)
1855 is
1856 select decode(app.application_short_name, 'SQLAP', 'AP'
1857 , 'SQLGL', 'GL'
1858 , app.application_short_name) application_short_name
1859 from fnd_tables t
1860 , fnd_application app
1861 where table_name = c_name
1862 and t.application_id = app.application_id;
1863
1864
1865 cursor cur_view(c_name in dba_objects.object_name%type)
1866 is
1867 select decode(app.application_short_name, 'SQLAP', 'AP'
1868 , 'SQLGL', 'GL'
1869 , app.application_short_name) application_short_name
1870 from fnd_views t
1871 , fnd_application app
1872 where view_name = c_name
1873 and t.application_id = app.application_id;
1874
1875
1876 v_return_value varchar2(80) := 'NOT FOUND';
1877 v_first boolean := TRUE;
1878
1879
1880 begin
1881
1882 if c_type = 'TABLE' then
1883 for t_rec in cur_table(c_name) loop
1884 if v_first = TRUE then
1885 v_first := FALSE;
1886 v_return_value := t_rec.application_short_name;
1887 else
1888 v_return_value := 'MULTIPLE';
1889 end if;
1890 end loop;
1891 elsif c_type = 'VIEW' then
1892 for t_rec in cur_view(c_name) loop
1893 if v_first = TRUE then
1894 v_first := FALSE;
1895 v_return_value := t_rec.application_short_name;
1896 else
1897 v_return_value := 'MULTIPLE';
1898 end if;
1899 end loop;
1900 else v_return_value := 'UNKNOWN';
1901 end if;
1902 return v_return_value;
1903 end get_owner;
1904
1905 end etrm_fndnav;