[Home] [Help]
PACKAGE BODY: APPS.ETRM_RPT
Source
1 package body etrm_rpt as
2 ----------------------------------------------------------------------------
3 -- Copyright � 2001, 2014, Oracle and/or its affiliates. All rights reserved.
4 -- eTRM: Oracle Applications repository browser and dependency report
5 -- Author: Peter Goldthorp 9 October 2001
6 -----------------------------------------------------------------------------
7 -----------------------------------------------------------------------------
8 -- Private Procedures
9 -----------------------------------------------------------------------------
10 function get_appid(c_app in varchar2) return number
11 is
12
13 cursor cur_app(c_app in varchar2)
14 is
15 select application_id
16 from fnd_application
17 where application_short_name = c_app;
18
19 vreturn_value number(16) := NULL;
20 begin
21 for a_rec in cur_app(c_app) loop
22 vreturn_value := a_rec.application_id;
23 end loop;
24 return vreturn_value;
25 end get_appid;
26
27
28 FUNCTION get_object_name(n_object_id in dba_objects.object_id%type
29 , c_mode in varchar2 := 'BOTH')
30 return varchar2
31 is
32
33 cursor cur_raw(n_object_id in dba_objects.object_id%type) is
34 select name
35 from sys.obj$
36 where obj# = n_object_id;
37
38 cursor cur_cons(n_object_id in dba_objects.object_id%type) is
39 select name
40 from sys.con$
41 where con# = n_object_id;
42
43
44
45
46
47 cursor cur_object(n_object_id in dba_objects.object_id%type) is
48 select object_name
49 , object_type
50 , owner
51 from dba_objects
52 where object_id = n_object_id;
53
54 v_return_value varchar2(512);
55
56 begin
57 if (upper(c_mode) = 'RAW'
58 or upper(c_mode) = 'ITEM') then
59 for o_rec in cur_raw(n_object_id) loop
60 v_return_value := o_rec.name;
61 end loop;
62 elsif upper(c_mode) = 'CONS' then
63 for o_rec in cur_cons(n_object_id) loop
64 v_return_value := o_rec.name;
65 end loop;
66 else
67 for o_rec in cur_object(n_object_id) loop
68 if upper(c_mode) = 'USER' then
69 v_return_value := o_rec.owner;
70 elsif upper(c_mode) = 'TYPE' then
71 v_return_value := o_rec.object_type;
72 else
73 v_return_value := o_rec.owner||'.'||o_rec.object_name;
74 end if;
75 end loop;
76 end if;
77 return v_return_value;
78 end get_object_name;
79
80 function get_tablename(n_appid in number
81 , n_tabid in number) return varchar2
82 is
83
84 cursor cur_name(n_appid in number
85 , n_tabid in number)
86 is
87 select table_name
88 from applsys.fnd_tables
89 where application_id = n_appid
90 and table_id = n_tabid;
91
92 vreturn_value varchar2(512) := '%';
93 begin
94 for a_rec in cur_name(n_appid, n_tabid) loop
95 vreturn_value := a_rec.table_name;
96 end loop;
97 return vreturn_value;
98 end get_tablename;
99
100
101 PROCEDURE a_href_gen(n_object_id in dba_objects.object_id%type)
102 is
103 cursor cur_object(n_object_id in dba_objects.object_id%type) is
104 select object_name
105 , object_type
106 , owner
107 from dba_objects
108 where object_id = n_object_id;
109 begin
110 for o_rec in cur_object(n_object_id) loop
111 uiutil.a_href_gen(c_type => o_rec.object_type
112 , c_link => 'etrm_pnav.show_object?c_name='
113 ||o_rec.object_name
114 ||'&c_owner='
115 ||o_rec.owner
116 ||'&c_type='
117 ||o_rec.object_type
118 , c_display => o_rec.object_name);
119 end loop;
120 end a_href_gen;
121
122
123
124 function dba_object_info(c_name in dba_objects.object_name%type
125 , c_owner in dba_objects.owner%type
126 , c_type in dba_objects.object_type%type)
127 return dba_objects.object_id%type is
128
129 cursor cur_object(c_name in dba_objects.object_name%type
130 , c_owner in dba_objects.owner%type
131 , c_type in dba_objects.object_type%type) is
132 select OWNER
133 , OBJECT_NAME
134 , SUBOBJECT_NAME
135 , OBJECT_ID
136 , DATA_OBJECT_ID
137 , OBJECT_TYPE
138 , CREATED
139 , LAST_DDL_TIME
140 , TIMESTAMP
141 , STATUS
142 , TEMPORARY
143 , GENERATED
144 , SECONDARY
145 from dba_objects
146 where object_name = c_name
147 and object_type = c_type
148 and owner = owner;
149
150 cursor cur_table(c_name in dba_objects.object_name%type)
151 is
152 select a.application_short_name
153 , a.application_id
154 , t.table_id
155 from fnd_tables t
156 , fnd_application a
157 where table_name = c_name
158 and t.application_id = a.application_id
159 order by a.application_short_name;
160
161
162 cursor cur_view(c_name in dba_objects.object_name%type)
163 is
164 select a.application_short_name
165 , a.application_id
166 , t.view_id
167 from fnd_views t
168 , fnd_application a
169 where view_name = c_name
170 and t.application_id = a.application_id
171 order by a.application_short_name;
172
173 v_return_val dba_objects.object_id%type := 0;
174 v_first BOOLEAN;
175
176 begin
177 for o_rec in cur_object(c_name, c_owner, c_type) loop
178 v_return_val := o_rec.object_id;
179 end loop;
180 return v_return_val;
181 end dba_object_info;
182
183
184 procedure ls_table(c_name dba_tables.table_name%type
185 , c_owner dba_tab_columns.owner%type) is
186
187 CURSOR cur_table_dets(c_name in dba_tables.table_name%type
188 , c_owner in dba_objects.owner%type)
189 -- List table details
190 IS select tab.table_name
191 , tab.tablespace_name
192 , tab.pct_free
193 , tab.pct_used
194 from dba_tables tab
195 where table_name = c_name
196 order by tab.table_name;
197 --
198
199 CURSOR cur_comment(c_name in dba_tables.table_name%type
200 , c_owner in dba_objects.owner%type)
201 is select comments
202 from dba_tab_comments
203 where owner = c_owner
204 and table_name = c_name
205 and (table_type = 'TABLE'
206 OR table_type = 'VIEW');
207
208 cursor cur_index(cv_tab_name in dba_indexes.table_name%type
209 , c_owner in dba_objects.owner%type)
210 -- List Indexes
211 is select ind.index_name
212 , ind.uniqueness
213 , ind.tablespace_name
214 , ind.pct_increase
215 , ind.pct_free
216 , ind.owner
217 from dba_indexes ind
218 where ind.table_name = cv_tab_name
219 and ind.table_owner = c_owner
220 order by ind.uniqueness desc, ind.index_name;
221 --
222 cursor cur_index_col(cv_index_name in dba_ind_columns.index_name%type
223 , c_owner in dba_objects.owner%type)
224 -- List index columns
225 is select ic.column_name
226 , ic.column_length
227 from dba_ind_columns ic
228 where ic.index_name = cv_index_name
229 and ic.index_owner = c_owner
230 order by ic.column_position;
231 --
232 cursor cur_primary_key(cv_table_name in dba_constraints.table_name%type
233 , c_owner in dba_objects.owner%type)
234 -- List the primary key
235 is select uc.constraint_name
236 from dba_constraints uc
237 where uc.table_name = cv_table_name
238 and uc.owner = c_owner
239 and uc.constraint_type = 'P'
240 order by uc.constraint_name;
241 --
242 cursor cur_unique_keys(cv_table_name in dba_constraints.table_name%type
243 , c_owner in dba_objects.owner%type)
244 -- List the unique key constraints
245 is select uc.constraint_name
246 from dba_constraints uc
247 where uc.table_name = cv_table_name
248 and uc.constraint_type = 'U'
249 and uc.owner = c_owner
250 order by uc.constraint_name;
251 --
252
253
254 --
255 cursor cur_list_cons_columns(cv_cons_name in dba_cons_columns.constraint_name%type
256 , c_owner in dba_objects.owner%type)
257 -- list the columns for a given constraint
258 is select cc.column_name
259 from dba_cons_columns cc
260 where cc.constraint_name = cv_cons_name
261 and cc.owner = c_owner
262 order by cc.position;
263
264 --
265 -- Main Body
266 --
267
268 v_first BOOLEAN := TRUE;
269 v_first2 BOOLEAN := TRUE;
270 unexpected_error EXCEPTION;
271 loop_counter INTEGER;
272 v_datetime varchar2(100);
273 v_user varchar2(30);
274 v_pk_name dba_constraints.constraint_name%type;
275 --
276 BEGIN
277 --
278
279 v_first := TRUE;
280 for c_rec in cur_comment(c_name, c_owner) loop
281 if v_first = TRUE then
282 v_first:= FALSE;
283 htp.p('<h5>Comments</h5>');
284 end if;
285 htp.p(c_rec.comments);
286 end loop;
287
288
289
290
291 for key_rec in cur_primary_key(c_name, c_owner) loop
292 htp.p('<h5>Primary Key: '|| key_rec.constraint_name || '</h5>');
293 htp.p('<ol>');
294 v_pk_name := key_rec.constraint_name;
295 for key_col in cur_list_cons_columns(key_rec.constraint_name, c_owner) loop
296 htp.p('<li> '
297 ||key_col.column_name
298 ||'</li>');
299 end loop;
300 htp.p('</ol>');
301 end loop;
302 --
303 for key_rec in cur_unique_keys(c_name, c_owner) loop
304 htp.p('<h5>Unique Key: '|| key_rec.constraint_name || '</h5>');
305 htp.p('<ol>');
306 for key_col in cur_list_cons_columns(key_rec.constraint_name, c_owner) loop
307 htp.p('<li> '
308 ||key_col.column_name
309 ||'</li>');
310 end loop;
311 htp.p('</ol>');
312 end loop;
313 --
314 v_first := TRUE;
315 for ind_rec in cur_index(c_name, c_owner) loop
316 if v_first = TRUE then
317 v_first := FALSE;
318 htp.p('<h5>Indexes</h5>');
319 htp.p('<table cellpadding="1" cellspacing="0" border="1"
320 summary="Indexes in this table">
321 <tr class="OraTableColumnHeader" >
322 <th class="OraTableColumnHeader" id="name">Index</th>
323 <th class="OraTableColumnHeader" id="type">Type</th>
324 <th class="OraTableColumnHeader" id="ts">Tablespace</th>
325 <th class="OraTableColumnHeader" id="col">Column</th></tr>');
326 end if;
327
328 htp.p('<tr >');
329 htp.p('<td headers="name">');
330 htp.p(ind_rec.index_name);
331 htp.p('</td>');
332
333
334 htp.p('<td headers="type">');
335 htp.p(ind_rec.uniqueness);
336 htp.p('</td>');
337
338
339 htp.p('<td headers="ts">');
340 htp.p(ind_rec.tablespace_name);
341 htp.p('</td>');
342
343
344
345 htp.p('<td headers="col">');
346
347 v_first2 := TRUE;
348 for ind_col_rec in cur_index_col(ind_rec.index_name, ind_rec.owner) loop
349 IF v_first2 Then
350 v_first2 := FALSE;
351 else
352 htp.p('<br>');
353 end if;
354 htp.p(ind_col_rec.column_name);
355 end loop;
356 htp.p('</td></tr>');
357 end loop;
358
359 IF v_first Then
360 null;
361 else
362 htp.p('</table>');
363 end if;
364
365
366 --
367 EXCEPTION
368 WHEN unexpected_error THEN
369 RAISE_APPLICATION_ERROR(-20003,
370 'An unexpected error has occured in the script. This is an internal error - not normally issued'
371 );
372 END ls_table;
373
374 procedure ls_fnd_table(n_tabid in number
375 , n_appid in number) is
376
377 CURSOR cur_table_dets(n_tabid in number
378 , n_appid in number)
379 -- List table details
380 IS select table_name
381 , INITIAL_EXTENT
382 , NEXT_EXTENT
383 , MIN_EXTENTS
384 , MAX_EXTENTS
385 , PCT_INCREASE
386 , INI_TRANS
387 , MAX_TRANS
388 , PCT_FREE
389 , PCT_USED
390 , description
391 from applsys.fnd_tables
392 where application_id = n_appid
393 and table_id = n_tabid
394 order by table_name;
395 --
396
397 cursor cur_primary_key(n_tabid in number
398 , n_appid in number)
399 -- List the primary key
400 is SELECT PRIMARY_KEY_ID
401 , PRIMARY_KEY_NAME
402 , PRIMARY_KEY_TYPE
403 , ENABLED_FLAG
404 FROM APPLSYS.FND_PRIMARY_KEYS
405 where table_id = n_tabid
406 and application_id = n_appid
407 order by primary_key_name;
408
409 cursor cur_pk_columns(n_tabid in number
410 , n_appid in number
411 , n_keyid in number) is
412 SELECT c.column_name
413 from applsys.fnd_columns c
414 , applsys.fnd_primary_key_columns k
415 where k.table_id = c.table_id
416 and k.application_id = c.application_id
417 and k.column_id = c.column_id
418 and k.table_id = n_tabid
419 and k.application_id = n_appid
420 and k.primary_key_id = n_keyid
421 order by primary_key_sequence;
422
423
424 --
425 cursor cur_foreign_key(n_tabid in number
426 , n_appid in number)
427 -- List the primary key
428 is SELECT PRIMARY_KEY_APPLICATION_ID
429 , PRIMARY_KEY_TABLE_ID
430 , PRIMARY_KEY_ID
431 , foreign_key_id
432 , foreign_key_name
433 FROM APPLSYS.FND_FOREIGN_KEYS
434 where table_id = n_tabid
435 and application_id = n_appid
436 order by foreign_key_name;
437
438 cursor cur_fk_refs(n_tabid in number
439 , n_appid in number)
440 -- List the primary key
441 is SELECT PRIMARY_KEY_ID
442 , table_id
443 , application_id
444 , foreign_key_id
445 , foreign_key_name
446 FROM APPLSYS.FND_FOREIGN_KEYS
447 where primary_key_table_id = n_tabid
448 and primary_key_application_id = n_appid
449 order by foreign_key_name;
450
451 cursor cur_fk_columns(n_tabid in number
452 , n_appid in number
453 , n_keyid in number) is
454 SELECT c.column_name
455 from applsys.fnd_columns c
456 , applsys.fnd_foreign_key_columns k
457 where k.table_id = c.table_id
458 and k.application_id = c.application_id
459 and k.column_id = c.column_id
460 and k.table_id = n_tabid
461 and k.application_id = n_appid
465
462 and k.foreign_key_id = n_keyid
463 order by foreign_key_sequence;
464 --
466
467
468 --
469 -- Main Body
470 --
471
472 v_first BOOLEAN := TRUE;
473 v_first2 BOOLEAN := TRUE;
474 unexpected_error EXCEPTION;
475 loop_counter INTEGER;
476 v_datetime varchar2(100);
477 v_user varchar2(30);
478 v_tablename varchar2(128);
479 --
480 BEGIN
481 --
482
483 v_first := TRUE;
484 FOR tab_rec IN cur_table_dets(n_appid=>n_appid, n_tabid=>n_tabid) LOOP
485 htp.p('<h3>Table: '||tab_rec.table_name ||'</h3>');
486 v_tablename := tab_rec.table_name;
487 htp.p('<TABLE cellpadding="0" cellspacing="3"
488 summary="location information">');
489
490 htp.p('<tr><th scope="row" align="left"><b>Description: </b></th><td>'
491 || tab_rec.description||'</td></tr>');
492
493
494
495 htp.p('</table>');
496
497 end loop;
498 --
499
500
501 for key_rec in cur_primary_key(n_appid => n_appid, n_tabid => n_tabid) loop
502 htp.p('<h5>Primary Key: '|| key_rec.primary_key_name || '</h5>');
503 htp.p('<ol>');
504 for key_col in cur_pk_columns(n_appid => n_appid
505 , n_tabid => n_tabid
506 , n_keyid => key_rec.primary_key_id) loop
507 htp.p('<li>'
508 ||key_col.column_name
509 ||'</li>');
510 end loop;
511 htp.p('</ol>');
512 end loop;
513
514 v_first := TRUE;
515
516
517 for key_rec in cur_foreign_key(n_appid => n_appid
518 , n_tabid => n_tabid) loop
519 if v_first = TRUE then
520 v_first := FALSE;
521 htp.p('<h5>Foreign Keys from this table:</h5>');
522 htp.p('<table cellpadding="1" cellspacing="0" border="1"
523 summary="Foreign keys from/to this table">
524 <tr class="OraTableColumnHeader" >
525 <th class="OraTableColumnHeader" id="col">Foreign Key Column</th>
526 <th class="OraTableColumnHeader" id="ftab">Foreign Table</th></tr>');
527
528 end if;
529
530
531
532 htp.p('<tr >');
533 v_first2 := TRUE;
534 htp.p('<td headers="col">');
535 for key_col in cur_fk_columns(n_appid => n_appid
536 , n_tabid => n_tabid
537 , n_keyid => key_rec.foreign_key_id) loop
538 if v_first2 = TRUE
539 then v_first2 := FALSE;
540 else
541 htp.p('<br>');
542 end if;
543 htp.p(get_tablename(n_appid, n_tabid)
544 ||'.<b>'
545 ||key_col.column_name
546 ||'</b>');
547 end loop;
548 htp.p('</td>');
549
550 htp.p('<td headers="ftab">');
551 htp.p(get_tablename(n_appid => key_rec.primary_key_application_id
552 , n_tabid => key_rec.primary_key_table_id));
553 htp.p('</td></tr>');
554
555 end loop;
556 --
557 if v_first
558 then null;
559 else
560 htp.p('</table>');
561 end if;
562
563 v_first := TRUE;
564 for key_rec in cur_fk_refs(n_appid => n_appid
565 , n_tabid => n_tabid) loop
566 if v_first = TRUE then
567 v_first := FALSE;
568 htp.p('<h5>Foreign Keys to this table:</h5>');
569 htp.p('<table cellpadding="1" cellspacing="0" border="1"
570 summary="Foreign keys from/to this table">
571 <tr class="OraTableColumnHeader" >
572 <th class="OraTableColumnHeader" id="col">Foreign Key Column</th>
573 <th class="OraTableColumnHeader" id="ftab">Foreign Table</th></tr>');
574
575 end if;
576
577 htp.p('<tr >');
578 v_first2 := TRUE;
579 htp.p('<td headers="col">');
580 for key_col in cur_fk_columns(n_appid => key_rec.application_id
581 , n_tabid => key_rec.table_id
582 , n_keyid => key_rec.foreign_key_id) loop
583 if v_first2 = TRUE
584 then v_first2 := FALSE;
585 else
586 htp.p('<br>');
587 end if;
588
589 htp.p(get_tablename(key_rec.application_id, key_rec.table_id)
590 ||'<b>.'
591 ||key_col.column_name
592 ||'</b>');
593 end loop;
594 htp.p('</td>');
595 htp.p('<td headers="ftab">');
596 htp.p(v_tablename||'</td></tr>');
597
598 end loop;
599
600 if v_first
601 then null;
602 else
603 htp.p('</table>');
604 end if;
605
606 --
607
608 --
609 EXCEPTION
610 WHEN unexpected_error THEN
611 RAISE_APPLICATION_ERROR(-20003,
615
612 'An unexpected error has occured in the script. This is an internal error - not normally issued'
613 );
614 END ls_fnd_table;
616
617 procedure ls_columns(c_name dba_tab_columns.table_name%type
618 , c_owner dba_tab_columns.owner%type) is
619
620 cursor cur_columns(c_name dba_tab_columns.table_name%type
621 , c_owner dba_tab_columns.owner%type)
622 is select col.column_name
623 , col.data_type
624 , col.data_length
625 , nvl(to_char(col.data_precision), '<br>') data_precision
626 , nvl(decode(col.nullable, 'N', 'Y'), '<br>') nullable
627 from dba_tab_columns col
628 where col.table_name = c_name
629 and col.owner = c_owner
630 order by col.column_id;
631
632
633 cursor cur_col_comments(c_tname dba_tab_columns.table_name%type
634 , c_cname dba_tab_columns.column_name%type
635 , c_owner dba_tab_columns.owner%type)
636 is select comments
637 from dba_col_comments
638 where owner = c_owner
639 and table_name = c_tname
640 and column_name = c_cname;
641
642 v_first boolean;
643 v_first2 boolean;
644
645 begin
646 v_first := TRUE;
647
648 for col_rec in cur_columns(c_name, c_owner) loop
649 IF v_first Then
650
651 htp.p('<h5> Columns</h5>');
652 htp.p('<table cellpadding=1 cellspacing=0 border=1
653 summary="Column details for this table">');
654 htp.p('<TR>');
655 htp.p('<TH class="OraTableColumnHeader" id="name">Name</TH>');
656 htp.p('<TH class="OraTableColumnHeader" id="datatype"> Datatype</TH>');
657 htp.p('<TH class="OraTableColumnHeader" id="length">Length</TH>');
658 -- htp.p('<TH class="OraTableColumnHeader" id="prcn">Prcn</TH>');
659 htp.p('<TH class="OraTableColumnHeader" id="null">Mandatory</TH>');
660 htp.p('<TH class="OraTableColumnHeader" id="text">Comments</TH></TR>');
661 v_first := FALSE;
662 end if;
663
664 htp.prn('<TR> <TD headers="name">');
665 htp.prn(col_rec.column_name);
666 htp.p('</TD>');
667 htp.prn('<TD headers="datatype">');
668 htp.prn(col_rec.data_type);
669 htp.p('</TD>');
670 htp.prn('<TD headers="length">');
671 if col_rec.data_type = 'DATE'
672 then htp.prn('<br>');
673 elsif (col_rec.data_type = 'NUMBER'
674 AND col_rec.data_precision = '<br>')
675 then htp.prn('<br>');
676 elsif (col_rec.data_type = 'NUMBER'
677 AND col_rec.data_precision != '<br>')
678 then htp.prn('('||col_rec.data_precision||')');
679 elsif col_rec.data_length is null
680 then htp.prn('<br>');
681 else
682 htp.prn('('||col_rec.data_length||')');
683 end if;
684 htp.p('</TD>');
685 -- htp.prn('<TD headers="prcn">');
686 -- htp.prn(col_rec.data_precision);
687 -- htp.p('</TD>');
688 htp.prn('<TD headers="null">');
689 htp.prn(col_rec.nullable);
690 htp.p('</TD>');
691 htp.prn('<TD headers="text">');
692 v_first2 := TRUE;
693 for t_rec in cur_col_comments(c_tname => c_name
694 , c_cname => col_rec.column_name
695 , c_owner => c_owner) loop
696 v_first2 := FALSE;
697 if t_rec.comments like '%- Retrofitted%' then
698 htp.prn('<br>');
699 else
700 htp.prn(t_rec.comments);
701 end if;
702 end loop;
703 if v_first2 then
704 htp.prn('<br>');
705 end if;
706 htp.p('</TD></TR>');
707
708 end loop;
709 IF v_first Then
710 htp.p(FND_CSS_PKG.Encode(c_name) || ' has no columns<p>');
711 else
712 htp.p('</table>');
713
714 end if;
715
716 end ls_columns;
717
718 procedure ls_dependencies(n_object_id IN dba_objects.object_id%type) is
719
720
721 cursor cur_depend(n_object_id IN dba_objects.object_id%type)
722 is select d_obj# object_id
723 , etrm_pnav.get_object_name(d_obj#, 'USER') owner
724 , etrm_pnav.get_object_name(d_obj#, 'ITEM') item
725 from sys.dependency$
726 where p_obj# = n_object_id
727 order by etrm_pnav.get_object_name(d_obj#);
728 --
729 cursor cur_depend2(n_object_id IN dba_objects.object_id%type)
730 is select p_obj# object_id
731 , etrm_pnav.get_object_name(p_obj#, 'USER') owner
732 , etrm_pnav.get_object_name(p_obj#, 'ITEM') item
733 from sys.dependency$
734 where d_obj# = n_object_id
735 order by etrm_pnav.get_object_name(p_obj#);
736
737 v_first boolean;
738 v_owner dba_objects.owner%type;
739
740 begin
741 v_first := TRUE;
742 v_owner := 'foRce_mE_to_diff';
746 for a_rec in cur_depend2(n_object_id) loop
743 htp.p('<a name="dependencies"></a>
744 <p><a href="#top-of-page">[top of page]</a></p>');
745
747 IF v_first Then
748 htp.p('<b>'||etrm_pnav.get_object_name(n_object_id)
749 || ' references the following: </b><p>');
750 htp.p('<dl>');
751 v_first := FALSE;
752 end if;
753
754 if a_rec.owner = v_owner then
755 null;
756 else
757 v_owner := a_rec.owner;
758 htp.p('<dt>');
759 if a_rec.owner is not null then
760 htp.p('<b>'||a_rec.owner||'</b>');
761 end if;
762 htp.p('</dt>');
763 end if;
764
765
766 htp.p('<dd>');
767 htp.p(get_object_name(a_rec.object_id, 'TYPE')
768 || ' - <b>' ||get_object_name(a_rec.object_id, 'ITEM')||'</b>');
769
770
771 htp.p('</dd>');
772 end loop;
773
774
775 IF v_first Then
776 htp.p('<p>'||etrm_pnav.get_object_name(n_object_id)|| ' does not reference any database object<p>');
777 else
778 htp.p('</dl>');
779 v_first := TRUE;
780 end if;
781
782
783 v_first := TRUE;
784 v_owner := 'foRce_mE_to_diff';
785
786 for a_rec in cur_depend(n_object_id) loop
787 IF v_first Then
788 htp.p('<b>'||etrm_pnav.get_object_name(n_object_id)
789 || ' is referenced by following: </b><p>');
790 htp.p('<dl>');
791 v_first := FALSE;
792 end if;
793
794 if a_rec.owner = v_owner then
795 null;
796 else
797 v_owner := a_rec.owner;
798 htp.p('<dt>');
799 if a_rec.owner is not null then
800 htp.p('<b>'||a_rec.owner||'</b>');
801 end if;
802 htp.p('</dt>');
803 end if;
804
805 htp.p('<dd>');
806 htp.p(get_object_name(a_rec.object_id, 'TYPE') || ' - <b>'
807 ||get_object_name(a_rec.object_id, 'ITEM')||'</b>');
808
809
810 htp.p('</dd>');
811 end loop;
812
813
814 IF v_first Then
815 htp.p('<p>'||etrm_pnav.get_object_name(n_object_id)|| ' is not referenced by any database object<p>');
816 else
817 htp.p('</dl>');
818 v_first := TRUE;
819 end if;
820
821 end ls_dependencies;
822
823
824 procedure ls_source(c_name in dba_objects.object_name%type
825 , c_type in dba_objects.object_type%type
826 , c_owner in dba_objects.owner%type) is
827
828 cursor cur_source(c_name in dba_objects.object_name%type
829 , c_type in dba_objects.object_type%type
830 , c_owner in dba_objects.owner%type)
831 is select text
832 from dba_source
833 where name = c_name
834 and type = c_type
835 and owner = c_owner;
836
837 cursor cur_vw_source(c_name in dba_objects.object_name%type
838 , c_owner in dba_objects.owner%type)
839 is select text
840 from dba_views
841 where view_name = c_name
842 and owner = c_owner;
843
844 v_viewtext varchar2(32000);
845 begin
846 if c_type = 'VIEW' then
847 for t_rec in cur_vw_source(c_name, c_owner) loop
848 htp.p('<h5>View Text - Preformatted</h5>');
849 htp.p('<pre>');
850 htp.p(t_rec.text);
851 htp.p('</pre>');
852
853 v_viewtext := upper(t_rec.text);
854 v_viewtext := replace(v_viewtext , 'SELECT ', '<b>SELECT </b>');
855 v_viewtext := replace(v_viewtext , 'UNION ', '<b>UNION </b>');
856 v_viewtext := replace(v_viewtext, ' FROM ', '<br><b> FROM </b>');
857 v_viewtext := replace(v_viewtext , ' WHERE ', '<br><b> WHERE </b>');
858 v_viewtext := replace(v_viewtext , ',', '<br>, ');
859 v_viewtext := replace(v_viewtext , ' AND ', '<br> AND ');
860
861 htp.p('<h5>View Text - HTML Formatted</h5>');
862 htp.p('<p>');
863 htp.p(v_viewtext);
864 htp.p('</p>');
865
866 end loop;
867
868 else
869 htp.p('<pre>');
870 for t_rec in cur_source(c_name, c_type, c_owner) loop
871 uiutil.txt_p(t_rec.text);
872 end loop;
873 htp.p('</pre>');
874 end if;
875
876 end ls_source;
877
878
879 -----------------------------------------------------------------------------
880 -- Public Procedures
881 -----------------------------------------------------------------------------
882
883 procedure cr_file(c_name in dba_objects.object_name%type
884 , c_owner in dba_objects.owner%type
885 , c_type in dba_objects.object_type%type
886 , c_rpt_type in varchar2 := 'FULL')
887
888 is
889
890 v_object_id dba_objects.object_id%type;
891
892 begin
893
894 v_object_id := dba_object_info(c_name
898 ls_table(c_name, c_owner);
895 , c_owner
896 , c_type );
897 if c_rpt_type = 'FULL' then
899 ls_columns(c_name, c_owner);
900 end if;
901 ls_dependencies(v_object_id);
902
903 end cr_file;
904
905 procedure cr_file(c_owner in dba_objects.owner%type
906 , c_rpt_type in varchar2 := 'FULL')
907 is
908
909 cursor cur_items(c_owner in dba_objects.owner%type)
910 is
911
912 select d.object_name
913 , d.object_type
914 , d.owner
915 , a.application_id
916 , t.table_id
917 from dba_objects d
918 , fnd_tables t
919 , fnd_application a
920 where a.application_short_name = c_owner
921 and a.application_id = t.application_id
922 and t.table_name = d.object_name
923 and d.object_type = 'TABLE'
924 UNION
925 select d.object_name
926 , d.object_type
927 , d.owner
928 , a.application_id
929 , t.view_id table_id
930 from dba_objects d
931 , fnd_views t
932 , fnd_application a
933 where a.application_short_name = c_owner
934 and a.application_id = t.application_id
935 and t.view_name = d.object_name
936 and d.object_type = 'VIEW'
937 order by 2, 1;
938
939 v_owner dba_objects.owner%type;
940
941 begin
942
943 uiutil.file_header(c_filename => c_owner||'.html');
944 uiutil.cabo1;
945 htp.p(etrm_static.legal_text);
946 htp.p('<a name="top-of-page"></a>');
947 htp.p('<h1>eTRM - '||FND_CSS_PKG.Encode(c_owner) || ' Tables and Views</h1>');
948
949 htp.p('<dl>');
950 for i_rec in cur_items(c_owner) loop
951
952 htp.p('<dt>');
953
954 htp.p(i_rec.object_type||' - '
955 ||'<a href="#'||i_rec.object_type||i_rec.object_name||'">'
956 ||i_rec.owner||'.'||i_rec.object_name||'</a>');
957
958
959
960 htp.p('</dt>');
961 end loop;
962 htp.p('</dl>');
963
964 for i_rec in cur_items(c_owner) loop
965 htp.p('<a name="'||i_rec.object_type||i_rec.object_name||'"></a>');
966
967 if i_rec.object_type = 'TABLE' then
968 ls_fnd_table(n_appid => i_rec.application_id, n_tabid => i_rec.table_id);
969 elsif i_rec.object_type = 'VIEW' then
970 htp.p('<h3>View: '||i_rec.object_name||'</h3>');
971 end if;
972 v_owner := i_rec.owner;
973 cr_file(c_name => i_rec.object_name
974 , c_owner => v_owner
975 , c_type => i_rec.object_type
976 , c_rpt_type => c_rpt_type);
977 htp.p('<p><a href="#top-of-page">[top of page]</a></p>');
978 end loop;
979 uiutil.prn_copyright;
980 end cr_file;
981
982
983 procedure product_dependencies(c_product in varchar2) is
984 cursor cur_items(c_owner in dba_objects.owner%type)
985 is
986
987 select d.object_name
988 , d.object_type
989 , d.object_id
990 , d.owner
991 from dba_objects d
992 , fnd_tables t
993 , fnd_application a
994 where a.application_short_name = c_owner
995 and a.application_id = t.application_id
996 and t.table_name = d.object_name
997 and d.object_type = 'TABLE'
998 UNION
999 select d.object_name
1000 , d.object_type
1001 , d.object_id
1002 , d.owner
1003 from dba_objects d
1004 , fnd_views t
1005 , fnd_application a
1006 where a.application_short_name = c_owner
1007 and a.application_id = t.application_id
1008 and t.view_name = d.object_name
1009 and d.object_type = 'VIEW'
1010 order by 2, 1;
1011
1012 cursor cur_depend(n_object_id in dba_objects.object_id%type) is
1013 select etrm_fndnav.get_owner(etrm_pnav.get_object_name(d_obj#, 'ITEM')
1014 , etrm_pnav.get_object_name(d_obj#, 'TYPE')) owner
1015 , etrm_pnav.get_object_name(d_obj#, 'ITEM') d_item
1016 , etrm_pnav.get_object_name(d_obj#, 'TYPE') d_item_type
1017 , etrm_pnav.get_object_name(d_obj#, 'USER') d_schema
1018 from sys.dependency$
1019 where p_obj# = n_object_id
1020 order by etrm_fndnav.get_owner(etrm_pnav.get_object_name(d_obj#, 'ITEM')
1021 , etrm_pnav.get_object_name(d_obj#, 'TYPE'))
1022 , etrm_pnav.get_object_name(d_obj#, 'USER')
1023 , etrm_pnav.get_object_name(d_obj#, 'ITEM');
1024
1025
1026 cursor cur_depend2(n_object_id in dba_objects.object_id%type) is
1027 select etrm_fndnav.get_owner(etrm_pnav.get_object_name(p_obj#, 'ITEM')
1028 , etrm_pnav.get_object_name(p_obj#, 'TYPE')) owner
1029 , etrm_pnav.get_object_name(p_obj#, 'ITEM') d_item
1030 , etrm_pnav.get_object_name(p_obj#, 'TYPE') d_item_type
1031 , etrm_pnav.get_object_name(p_obj#, 'USER') d_schema
1032 from sys.dependency$
1033 where d_obj# = n_object_id
1034 order by etrm_fndnav.get_owner(etrm_pnav.get_object_name(p_obj#, 'ITEM')
1035 , etrm_pnav.get_object_name(p_obj#, 'TYPE'))
1036 , etrm_pnav.get_object_name(p_obj#, 'USER')
1040 is
1037 , etrm_pnav.get_object_name(p_obj#, 'ITEM');
1038
1039 cursor cur_appname
1041 select a.application_id
1042 , a.application_short_name
1043 , t.application_name
1044 , t.description
1045 from fnd_application a
1046 , fnd_application_tl t
1047 where a.application_id = t.application_id
1048 order by a.application_short_name;
1049
1050 cursor cur_appid
1051 is
1052 select application_id
1053 from fnd_application
1054 order by application_id;
1055
1056
1057
1058 v_item_appid fnd_application.application_id%type;
1059 v_depend_appid fnd_application.application_id%type;
1060 v_item_rowid number(16);
1061 v_loop_start number(16);
1062 v_loop_end number(16);
1063 v_item_name dba_objects.object_name%type;
1064 v_product varchar2(256);
1065 v_first boolean;
1066
1067
1068 type dep_type is
1069 record (uses boolean
1070 , used_by boolean
1071 , id_base number(16)
1072 , rowcount number(16));
1073
1074 type dep_table_t is table of dep_type index by binary_integer;
1075 dep_table dep_table_t;
1076
1077 type dep_item_type is
1078 record (product_item dba_objects.object_name%type
1079 , product_item_type dba_objects.object_type%type
1080 , product_item_schema dba_objects.owner%type
1081 , dep_type varchar2(8)
1082 , dep_item dba_objects.object_name%type
1083 , dep_item_type dba_objects.object_type%type
1084 , dep_item_schema dba_objects.owner%type);
1085
1086 type dep_item_table_t is table of dep_item_type index by binary_integer;
1087 dep_item_table dep_item_table_t;
1088
1089
1090
1091
1092 begin
1093
1094 uiutil.file_header(c_filename => c_product||'.html');
1095 uiutil.cabo1;
1096 htp.p(etrm_static.legal_text);
1097 htp.p('<a name="top-of-page"></a>');
1098 htp.p('<h1>eTRM - '||FND_CSS_PKG.Encode(c_product) || ' External Dependencies</h1>');
1099 htp.p(etrm_static.product_dependency_text);
1100
1101 v_item_appid := get_appid(c_product);
1102 for a_rec in cur_appid loop
1103 dep_table(a_rec.application_id).uses := FALSE;
1104 dep_table(a_rec.application_id).used_by := FALSE;
1105 dep_table(a_rec.application_id).rowcount := 0;
1106 dep_table(a_rec.application_id).id_base := a_rec.application_id * 10000;
1107 end loop;
1108
1109 for i_rec in cur_items(c_product) loop
1110 for d_rec in cur_depend(i_rec.object_id) loop
1111 v_depend_appid := get_appid(d_rec.owner);
1112 if (v_depend_appid is NULL
1113 OR v_item_appid = v_depend_appid)
1114 then null;
1115 else
1116 dep_table(v_depend_appid).rowcount := dep_table(v_depend_appid).rowcount + 1;
1117 -- dep_item_table has a compound key app_id||rowcount:
1118 v_item_rowid := dep_table(v_depend_appid).id_base + dep_table(v_depend_appid).rowcount;
1119
1120 if dep_table(v_depend_appid).used_by = FALSE
1121 then dep_table(v_depend_appid).used_by := TRUE;
1122 end if;
1123 dep_item_table(v_item_rowid).product_item := i_rec.object_name;
1124 dep_item_table(v_item_rowid).product_item_type := i_rec.object_type;
1125 dep_item_table(v_item_rowid).product_item_schema := i_rec.owner;
1126 dep_item_table(v_item_rowid).dep_type := 'used by';
1127 dep_item_table(v_item_rowid).dep_item := d_rec.d_item;
1128 dep_item_table(v_item_rowid).dep_item_type := d_rec.d_item_type; dep_item_table(v_item_rowid).dep_item_schema := d_rec.d_schema;
1129 end if;
1130 end loop;
1131
1132 for d_rec in cur_depend2(i_rec.object_id) loop
1133 v_depend_appid := get_appid(d_rec.owner);
1134 if (v_depend_appid is NULL
1135 OR v_item_appid = v_depend_appid)
1136 then null;
1137 else
1138 dep_table(v_depend_appid).rowcount := dep_table(v_depend_appid).rowcount + 1;
1139 -- dep_item_table has a compound key app_id||rowcount:
1140 v_item_rowid := dep_table(v_depend_appid).id_base + dep_table(v_depend_appid).rowcount;
1141
1142 if dep_table(v_depend_appid).uses = FALSE
1143 then dep_table(v_depend_appid).uses := TRUE;
1144 end if;
1145 dep_item_table(v_item_rowid).product_item := i_rec.object_name;
1146 dep_item_table(v_item_rowid).product_item_type := i_rec.object_type;
1147 dep_item_table(v_item_rowid).product_item_schema := i_rec.owner; dep_item_table(v_item_rowid).dep_type := 'uses';
1148 dep_item_table(v_item_rowid).dep_item := d_rec.d_item;
1149 dep_item_table(v_item_rowid).dep_item_type := d_rec.d_item_type;
1150 dep_item_table(v_item_rowid).dep_item_schema := d_rec.d_schema;
1151 end if;
1152 end loop;
1153
1154 end loop;
1155
1156
1157 v_first := TRUE;
1158 for ap_rec in cur_appname loop
1159 if v_first then
1160 v_first := FALSE;
1161 htp.p('<table cellpadding="1" cellspacing="0" border="1"
1162 summary="Product Dependencies">
1163 <tr><th class="OraTableColumnHeader" id="p1">Product</th>
1164 <th class="OraTableColumnHeader" id="d">Dependency</th>
1165 <th class="OraTableColumnHeader" id="p2">Product</th></tr>');
1166 v_product := c_product;
1167 end if;
1168
1169 if ((dep_table(ap_rec.application_id).uses = FALSE)
1170 AND (dep_table(ap_rec.application_id).used_by = FALSE))
1171 then null;
1172 elsif (dep_table(ap_rec.application_id).uses = TRUE
1176 ||'<a href="#'||ap_rec.application_short_name||'">'
1173 AND dep_table(ap_rec.application_id).used_by = FALSE)
1174 then htp.p('<tr><td headers="p1">'
1175 ||v_product||'</td><td headers="d">uses</td><td headers="p2">'
1177 ||ap_rec.application_short_name||' - '||ap_rec.application_name||'</a>');
1178 htp.p('</td></tr>');
1179
1180 v_product := '<br>'; -- print product name once only.
1181 elsif (dep_table(ap_rec.application_id).uses = FALSE
1182 AND dep_table(ap_rec.application_id).used_by = TRUE)
1183 then htp.p('<tr><td headers="p1">'
1184 ||v_product||'</td><td headers="d">used by</td><td headers="p2">'
1185 ||'<a href="#'||ap_rec.application_short_name||'">'
1186 ||ap_rec.application_short_name||' - '||ap_rec.application_name||'</a>');
1187 htp.p('</td></tr>');
1188 v_product := '<br>'; -- print product name once only.
1189 else
1190 htp.p('<tr><td headers="p1">'
1191 ||v_product||'</td><td headers="d">used by/uses</td><td headers="p2">'
1192 ||'<a href="#'||ap_rec.application_short_name||'">'
1193 ||ap_rec.application_short_name||' - '||ap_rec.application_name||'</a>');
1194 htp.p('</td></tr>');
1195 v_product := '<br>'; -- print product name once only.
1196 end if;
1197
1198 end loop;
1199
1200 if v_first then
1201 htp.p('<p>No external dependencies were found for this product.</p>');
1202 else
1203 htp.p('</table>');
1204
1208 then null;
1205 for ap_rec in cur_appname loop
1206
1207 if dep_table(ap_rec.application_id).rowcount = 0
1209 else
1210 htp.p('<a name="'||ap_rec.application_short_name||'"></a>');
1211 htp.p('<h3>'||ap_rec.application_short_name||' - '
1212 ||ap_rec.application_name||'</h3>');
1213
1214 htp.p('<table cellpadding="1" cellspacing="0" border="1"
1215 summary="'|| ap_rec.application_short_name||' Product Dependencies">
1216 <tr><th class="OraTableColumnHeader" id="p1">'||c_product || ' Item</th>
1217 <th class="OraTableColumnHeader" id="d">Dependency</th>
1218 <th class="OraTableColumnHeader" id="p2">'||ap_rec.application_short_name
1219 ||' Item</th></tr>');
1220
1221 v_loop_start := dep_table(ap_rec.application_id).id_base + 1;
1222 v_loop_end := dep_table(ap_rec.application_id).id_base + dep_table(ap_rec.application_id).rowcount;
1223
1224 v_item_name := 'forCe_Me_to_diff';
1225 for loop_index in v_loop_start .. v_loop_end loop
1226 htp.p('<tr><td headers="p1">');
1227
1228 if v_item_name = dep_item_table(loop_index).product_item
1229 then htp.prn('<br>');
1230 else
1231 htp.prn(initcap(dep_item_table(loop_index).product_item_type) || ' '
1232 || dep_item_table(loop_index).product_item_schema ||'.'
1233 || '<b>'||dep_item_table(loop_index).product_item || '</b>');
1234 v_item_name := dep_item_table(loop_index).product_item;
1235 end if;
1236
1237 htp.p('</td><td headers="d">');
1238 htp.p(dep_item_table(loop_index).dep_type);
1239 htp.p('</td><td headers="p2">');
1240 htp.p(initcap(dep_item_table(loop_index).dep_item_type) || ' '
1241 || dep_item_table(loop_index).dep_item_schema ||'.'
1242 || '<b>'||dep_item_table(loop_index).dep_item||'</b>');
1243 htp.p('</td></tr>');
1244 end loop;
1245 htp.p('</table>');
1246 htp.p('<p>'||dep_table(ap_rec.application_id).rowcount || ' dependencies</p>');
1247 htp.p('<p><a href="#top-of-page">[top of page]</a></p>');
1248 end if;
1249 end loop;
1250 end if;
1251
1252 uiutil.prn_copyright;
1253 end product_dependencies;
1254
1255
1256 end etrm_rpt;