eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.FND_XDF_UTIL_PKG

Source


1 package body fnd_xdf_util_pkg as
2 /* $Header: fndpxutb.pls 120.4.12010000.2 2009/10/10 02:04:19 smadhapp ship $ */
3 
4 /* The function receives the list of columns as the argument and returns an
5  * array of numbers containing hash values corresopnding to each of the
6  * columns.
7  */
8 function get_hashcode_table( p_tablename in varchar2 ,
9                              p_owner in varchar2,
10                              p_columns_list out NOCOPY FND_XDF_TABLE_OF_VARCHAR2_30,
11                              table_hash_val out NOCOPY number )
12     return FND_XDF_TABLE_OF_NUMBER is
13     l_hashVal number;
14     l_hashCode_List FND_XDF_TABLE_OF_NUMBER := FND_XDF_TABLE_OF_NUMBER();
15     l_column_List FND_XDF_TABLE_OF_VARCHAR2_30 := FND_XDF_TABLE_OF_VARCHAR2_30();
16     ind integer;
17     tmp_str varchar2(32000);
18 
19     begin
20         tmp_str := '';
21         ind := 0;
22              SELECT DURATION || NVL(TEMPORARY,'N') || NVL(PCT_FREE, 0)
23              || NVL(PCT_USED, 0) ||  NVL(INI_TRANS, 0) ||
24              NVL(MAX_TRANS, 0) || NVL(INITIAL_EXTENT, 0) ||
25              NVL(NEXT_EXTENT, 0) || NVL(MIN_EXTENTS, 0) ||
26              NVL(MAX_EXTENTS, 0) || NVL(PCT_INCREASE, 0) ||
27              NVL(PARTITIONED, 'NO') || NVL(FREELISTS, 0) ||
28              NVL(FREELIST_GROUPS, 0) ||
29              NVL(DECODE(LTRIM(DEGREE), 'DEFAULT', 65536, DEGREE) , 0) ||
30              LOGGING|| CACHE|| IOT_TYPE || ROW_MOVEMENT into tmp_str
31                      FROM   ALL_TABLES WHERE  TABLE_NAME=p_tablename AND OWNER=p_owner;
32 
33         for x in ( select COLUMN_NAME, DATA_TYPE, DATA_LENGTH, NULLABLE,
34             DATA_PRECISION, DATA_SCALE, DATA_DEFAULT from all_tab_columns where
35             table_name =p_tablename and owner = p_owner order by column_name)
36         loop
37 
38             l_hashVal := dbms_utility.get_hash_value(x.COLUMN_NAME || x.DATA_TYPE || x.DATA_LENGTH || x.NULLABLE || x.DATA_PRECISION || x.DATA_SCALE || x.DATA_DEFAULT, 1, 999999999);
39             ind := ind + 1;
40             l_hashCode_List.EXTEND;
41             l_hashCode_List(ind) := l_hashVal;
42 
43             l_column_List.EXTEND;
44             l_column_List(ind) := x.COLUMN_NAME;
45             tmp_str := tmp_str || l_hashVal;
46         end loop;
47 
48         for x in ( SELECT PARTITION_NAME, HIGH_VALUE, SUBPARTITION_COUNT
49                          FROM DBA_TAB_PARTITIONS
50                          WHERE TABLE_NAME = p_tablename
51                          and TABLE_OWNER = p_owner ORDER BY PARTITION_POSITION)
52         loop
53 
54                l_hashVal := dbms_utility.get_hash_value(x.PARTITION_NAME || x.HIGH_VALUE || x.SUBPARTITION_COUNT,1,999999999);
55                tmp_str := tmp_str || l_hashVal;
56         end loop;
57 
58         for x in ( SELECT COLUMN_NAME FROM DBA_PART_KEY_COLUMNS
59                        WHERE NAME = p_tablename AND OWNER =p_owner ORDER BY COLUMN_POSITION)
60         loop
61                l_hashVal := dbms_utility.get_hash_value(x.COLUMN_NAME,1,999999999);
62                tmp_str := tmp_str || l_hashVal;
63         end loop;
64 
65         table_hash_val := dbms_utility.get_hash_value(tmp_str , 1, 999999999);
66         p_columns_list := l_column_List;
67         return l_hashCode_List;
68     exception
69         WHEN NO_DATA_FOUND THEN
70         table_hash_val := 0;
71         return l_hashCode_List;
72 end;
73 
74 
75 function get_hashcode_qtable( p_qtablename in varchar2, p_owner in varchar2)
76     return number is
77       l_hashstr varchar2(32000);
78     begin
79         SELECT  TYPE || OBJECT_TYPE || SORT_ORDER || RECIPIENTS ||
80            MESSAGE_GROUPING || PRIMARY_INSTANCE || SECONDARY_INSTANCE ||
81            USER_COMMENT INTO l_hashstr
82         FROM   ALL_QUEUE_TABLES
83         WHERE QUEUE_TABLE=p_qtablename AND OWNER= p_owner;
84         return DBMS_UTILITY.GET_HASH_VALUE(l_hashstr,1,999999999);
85     exception
86         WHEN NO_DATA_FOUND THEN
87         return 0;
88     end;
89 
90 
91 
92 function get_hashcode_queue(p_queuename in varchar2, p_owner in varchar2)
93     return number is
94       l_hashstr varchar2(32000);
95     begin
96         SELECT name ||  queue_table || max_retries ||  enqueue_enabled ||
97             dequeue_enabled || retry_delay || retention || user_comment
98         INTO l_hashstr
99         FROM ALL_QUEUES
100         WHERE OWNER=p_owner AND NAME = p_queuename;
101         return DBMS_UTILITY.GET_HASH_VALUE(l_hashstr,1,999999999);
102     exception
103         WHEN NO_DATA_FOUND THEN
104         return 0;
105     end;
106 
107 /*
108  * Funtion to generate the hashcode for an index
109  */
110 function get_hashcode_index(p_indexname in varchar2, p_owner in varchar2)
111     return number is
112       l_hashstr varchar2(32000);
113       cursor col_cur is
114           SELECT COLUMN_NAME
115           FROM all_ind_columns
116           WHERE INDEX_NAME = p_indexname AND INDEX_OWNER = p_owner
117           ORDER BY COLUMN_POSITION;
118     begin
119         SELECT INDEX_TYPE || UNIQUENESS || NVL(INI_TRANS, 0) || NVL(MAX_TRANS, 0) ||
120           NVL(INITIAL_EXTENT, 0) || NVL(NEXT_EXTENT, 0 ) || NVL(MIN_EXTENTS, 0) || NVL(MAX_EXTENTS, 0) ||
121           NVL(PCT_INCREASE, 0) || NVL(FREELISTS, 0) || NVL(FREELIST_GROUPS, 0) || NVL(pct_free, 0) ||
122           NVL(DECODE(LTRIM(DEGREE), 'DEFAULT', 65536, DEGREE),0) || NVL(PARTITIONED, 'NO') ||
123           NVL(funcidx_status, 'DISABLED') || TABLE_NAME ||
124           TABLE_TYPE || NVL(ITYP_OWNER, '-1') || NVL(ITYP_NAME,'-1') || NVL(PARAMETERS, '-1') || NVL(COMPRESSION, 'DISABLED')
125         INTO l_hashstr
126         FROM ALL_INDEXES
127         WHERE index_name = p_indexname AND OWNER = p_owner;
128 
129         for c in col_cur loop
130             l_hashstr := l_hashstr || c.column_name;
131         end loop;
132         return DBMS_UTILITY.GET_HASH_VALUE(l_hashstr,1,999999999);
133     exception
134         WHEN NO_DATA_FOUND THEN
135         return 0;
136     end;
137 
138 /*
139  *  Function that generates the hashcode for an array of indexes.
140  */
141 /*
142 function get_hashcode_index(p_indexList in FND_XDF_TABLE_OF_VARCHAR2_30, p_owner in varchar2)
143   return FND_XDF_TABLE_OF_NUMBER is
144     indexHashcodes FND_XDF_TABLE_OF_NUMBER := FND_XDF_TABLE_OF_NUMBER();
145 
146     TYPE typIndxDetails IS REF CURSOR;
147     selQry varchar2(32000);
148 	indxDet typIndxDetails;
149 
150     cursor col_cur (p_indexname varchar2) is
151           SELECT COLUMN_NAME
152           FROM all_ind_columns
153           WHERE INDEX_NAME = p_indexname AND INDEX_OWNER = p_owner
154           ORDER BY COLUMN_POSITION;
155 
156     columnNameIndx varchar2(4000);
157 
158     l_hashstr varchar2(32000);
159     index_name varchar2(32000);
160     i number := 1;
161 	l_indexListStr varchar2(32000);
162 
163   begin
164   	  -- Generate the index names string from the table of index names.
165 	  for j in 1..p_indexList.count loop
166 	  	  l_indexListStr := l_indexListStr || '''' || p_indexList(j) || ''',';
167 	  end loop;
168 	  l_indexListStr := SUBSTR(l_indexListStr,1,LENGTH(l_indexListStr) - 1);
169 	  --DBMS_OUTPUT.PUT_LINE(l_indexListStr);
170 
171       selQry := 'SELECT INDEX_TYPE || UNIQUENESS || NVL(INI_TRANS, 0) || NVL(MAX_TRANS, 0) || '
172 	      ||'NVL(INITIAL_EXTENT, 0) || NVL(NEXT_EXTENT, 0 ) || NVL(MIN_EXTENTS, 0) || NVL(MAX_EXTENTS, 0) || '
173           ||'NVL(PCT_INCREASE, 0) || NVL(FREELISTS, 0) || NVL(FREELIST_GROUPS, 0) || NVL(pct_free, 0) || '
174           ||'NVL(DECODE(LTRIM(DEGREE), ''DEFAULT'', 65536, DEGREE),0) || NVL(PARTITIONED, ''NO'') || '
175           ||'NVL(funcidx_status, ''DISABLED'') || TABLE_NAME || NVL(tablespace_name, '' '') || TABLE_OWNER || '
176           ||'TABLE_TYPE || NVL(ITYP_OWNER, ''-1'') || NVL(ITYP_NAME,''-1'') || NVL(PARAMETERS, ''-1'') || NVL(COMPRESSION, ''DISABLED'') hashstr,'
177           ||'index_name '
178         ||'FROM ALL_INDEXES , TABLE(FND_XDF_TABLE_OF_VARCHAR2_30(' || l_indexListstr ||')) FND_TAB '
179         ||'WHERE index_name = FND_TAB.COLUMN_VALUE  AND OWNER = ''' || p_owner || ''''
180 		||'   UNION ALL '
181 		||'SELECT ''NULL'', C.COLUMN_VALUE FROM TABLE(FND_XDF_TABLE_OF_VARCHAR2_30('|| l_indexListstr || ')) C '
182 		||'WHERE C.COLUMN_VALUE NOT IN '
183 		||'(SELECT B.INDEX_NAME FROM ALL_INDEXES B WHERE OWNER = '''|| p_owner || ''' AND C.COLUMN_VALUE = B.INDEX_NAME) ';
184 
185       --DBMS_OUTPUT.PUT_LINE(l_indexListStr);
186       --DBMS_OUTPUT.PUT_LINE(selQry);
187 
188       open indxDet for selQry;
189       loop
190         fetch indxDet into l_hashstr,index_name;
191         exit when indxDet%NOTFOUND;
192 
193         --DBMS_OUTPUT.PUT_LINE(i);
194         --DBMS_OUTPUT.PUT_LINE(l_hashstr);
195 
196         open  col_cur(index_name);
197         loop
198             fetch col_cur into columnNameIndx;
199             exit when col_cur%NOTFOUND;
200             exit when l_hashstr = 'NULL';
201             l_hashstr := l_hashstr || columnNameIndx;
202         end loop;
203         close col_cur;
204 
205         --DBMS_OUTPUT.PUT_LINE(l_hashstr);
206 
207         indexHashcodes.EXTEND;
208         if l_hashstr <>'NULL' then
209            indexHashcodes(i) := DBMS_UTILITY.GET_HASH_VALUE(l_hashstr,1,999999999);
210         else
211            indexHashcodes(i) := 0;
212         end if;
213         i := i+1;
214       end loop;
215       close indxDet;
216       return indexHashcodes;
217   end;
218 */
219 
220 /*
221  *  Function that generates the hashcode for an array of indexes.
222  */
223  function get_hashcode_index(p_indexList in FND_XDF_TABLE_OF_VARCHAR2_30, p_owner in varchar2)
224  return FND_XDF_TABLE_OF_NUMBER is
225     indexHashcodes FND_XDF_TABLE_OF_NUMBER := FND_XDF_TABLE_OF_NUMBER();
226     selQry varchar2(32000);
227     cursor col_cur (p_indexname varchar2) is
228           SELECT COLUMN_NAME
229           FROM all_ind_columns
230           WHERE INDEX_NAME = p_indexname AND INDEX_OWNER = p_owner
231           ORDER BY COLUMN_POSITION;
232     columnNameIndx varchar2(4000);
233     l_hashstr varchar2(32000);
234     i number := 1;
235   begin
236 
237     selQry := 'SELECT  INDEX_TYPE || UNIQUENESS || NVL(INI_TRANS, 0) || NVL(MAX_TRANS, 0) || '
238 	      ||'NVL(INITIAL_EXTENT, 0) || NVL(NEXT_EXTENT, 0 ) || NVL(MIN_EXTENTS, 0) || NVL(MAX_EXTENTS, 0) ||'
239           ||'NVL(PCT_INCREASE, 0) || NVL(FREELISTS, 0) || NVL(FREELIST_GROUPS, 0) || NVL(pct_free, 0) ||'
240           ||'NVL(DECODE(LTRIM(DEGREE), ''DEFAULT'', 65536, DEGREE),0) || NVL(PARTITIONED, ''NO'') ||'
241           ||'NVL(funcidx_status, ''DISABLED'') || TABLE_NAME || '
242           ||'TABLE_TYPE || NVL(ITYP_OWNER, ''-1'') || NVL(ITYP_NAME,''-1'') || NVL(PARAMETERS, ''-1'') || NVL(COMPRESSION, ''DISABLED'') hashstr'
243 	   ||' FROM ALL_INDEXES '
244        ||' WHERE OWNER = '''|| p_owner ||''' AND INDEX_NAME = :p_indexname ';
245 
246     for j in 1..p_indexList.count loop
247         begin
248    	       execute immediate selQry into l_hashstr using p_indexList(j);
249         exception
250   	       when NO_DATA_FOUND then
251 	  	     l_hashstr := 'NULL';
252         end;
253 
254 	-- DBMS_OUTPUT.PUT_LINE(l_hashstr);
255 
256 
257         open  col_cur(p_indexList(j));
258         loop
259             fetch col_cur into columnNameIndx;
260             exit when col_cur%NOTFOUND;
261             exit when l_hashstr = 'NULL';
262             l_hashstr := l_hashstr || columnNameIndx;
263         end loop;
264         close col_cur;
265 
266 
267         indexHashcodes.EXTEND;
268         if l_hashstr <>'NULL' then
269            indexHashcodes(i) := DBMS_UTILITY.GET_HASH_VALUE(l_hashstr,1,999999999);
270         else
271            indexHashcodes(i) := 0;
272         end if;
273         i := i+1;
274     end loop;
275     return indexHashcodes;
276   end;
277 
278 /* Function to determine the referenced objects of type
279    'TABLE','TYPE','MATERIALIZED VIEW','VIEW','INDEX'
280    Input Parameters : 1. Object Name
281                       2. Object Type
282                       3. Owner of the Object
283 
284 */
285 function depends( p_name  in varchar2,
286                       p_type  in varchar2,
287                       p_owner in varchar2,
288                       p_lvl   in number default 1 ) return  fnd_xdf_deptype_tab_info
289     as
290         l_data fnd_xdf_deptype_tab_info := fnd_xdf_deptype_tab_info();
291 
292        procedure recurse( p_name  in varchar2,
293                           p_type  in varchar2,
294                           p_owner in varchar2,
295                           p_lvl   in number )
296        is
297        begin
298            if ( l_data.count > 1000 )
299            then
300                raise_application_error( -20001, 'probable connect by loop, aborting' );
301            end if;
302 
303            for x in ( select /*+ first_rows */ name,
304                                                owner,
305                                                type
306                          from dba_dependencies
307                        where referenced_owner = p_owner
308                            and referenced_type = p_type
309                            and type in ('TABLE','TYPE','MATERIALIZED VIEW','VIEW','INDEX')
310                            and referenced_name = p_name )
311            loop
312                l_data.extend;
313                l_data(l_data.count) :=
314                       fnd_xdf_deptype_info( p_lvl, x.name,
315                                     x.owner, x.type );
316                recurse( x.name, x.type,
317                        x.owner, p_lvl+1);
318            end loop;
319        end;
320    begin
321        l_data.extend;
322        l_data(l_data.count) := fnd_xdf_deptype_info( 1, p_name, p_owner, p_type );
323        recurse( p_name, p_type, p_owner, 2 );
324        return l_data;
325    end;
326 
327 end fnd_xdf_util_pkg;