[Home] [Help]
PACKAGE BODY: APPS.ETRM_SEARCH
Source
1 package body etrm_search 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 -----------------------------------------------------------------------------
9 -- Private Procedures
10 -----------------------------------------------------------------------------
11
12
13 procedure draw_buttons
14 is
15 begin
16 uiutil.a_href_gen(c_link => 'etrm_search.search'
17 , c_display => 'Home');
18 -- uiutil.a_href_gen(c_link => 'https://login-stage.oracle.com/pls/orasso/orasso.wwsso_app_admin.ls_logout?p_done_url=https://etrm-test.us.oracle.com'
19 -- , c_display => 'Sign out');
20 uiutil.a_javascript_gen(c_link => 'etrm_search.help'
21 , c_display => 'Help');
22 end draw_buttons;
23
24 procedure draw_tabs
25 is
26 v_cookie owa_cookie.cookie;
27 v_pnav_url varchar2(1024);
28 v_fndnav_url varchar2(1024);
29 begin
30
31 v_cookie := owa_cookie.get('etrm_pnav_context');
32 if (v_cookie.num_vals >0) THEN
33 v_pnav_url := 'etrm_pnav.ls_object?'||utl_url.escape(v_cookie.vals(1));
34 else
35 v_pnav_url := 'etrm_pnav.ls_apps';
36 end if;
37
38 v_cookie := owa_cookie.get('etrm_fndnav_context');
39 if (v_cookie.num_vals >0) THEN
40 v_fndnav_url := 'etrm_fndnav.ls_object?'||utl_url.escape(v_cookie.vals(1));
41 else
42 v_fndnav_url := 'etrm_fndnav.ls_apps';
43 end if;
44
45
46
47
48 -- uiutil.render_tab(c_url => 'etrm_search.search'
49 -- , c_title => 'Search'
50 -- , c_position => 'LEFT'
51 -- , c_state => 'SELECTED');
52
53 uiutil.render_tab(c_url => v_fndnav_url
54 , c_title => 'FND'
55 , c_position => 'LEFT'
56 , c_state => 'ENABLED'
57 , b_prior_selected => TRUE);
58
59 uiutil.render_tab(c_url => v_pnav_url
60 , c_title => 'DBA'
61 , c_position => 'RIGHT'
62 , c_state => 'ENABLED');
63
64 end draw_tabs;
65
66 procedure quick_list(c_search in varchar2 := NULL)
67 is
68 cursor cur_version is
69 select release_name
70 from fnd_product_groups
71 order by release_name;
72
73 v_version fnd_product_groups.release_name%type;
74 begin
75 v_version := '11i';
76 for v_rec in cur_version loop
77 v_version := v_rec.release_name;
78 end loop;
79
80 htp.p('<h1>eTRM Version '||v_version||'</h1>');
81
82
83 end quick_list;
84
85 procedure draw_form(c_search in varchar2 := NULL)
86 is
87
88 cursor cur_apps
89 is
90 select app.application_id
91 , app.application_short_name || ' - '|| tl.application_name product_name
92 from APPLSYS.FND_APPLICATION_TL tl
93 , APPLSYS.FND_APPLICATION app
94 where app.application_id = tl.application_id
95 order by app.application_short_name;
96
97 begin
98 htp.p('<FORM ACTION="etrm_search.search" METHOD="POST">');
99
100 if c_search is null then
101 htp.p('<label for="search"><p>Enter a search condition then press ''Search''</p>');
102 else
103 htp.p('<label for="search"><p>Search results for:<b> '||FND_CSS_PKG.Encode(c_search)||'</b></p>');
104 end if;
105
106
107 htp.p('<TABLE cellpadding="0" cellspacing="0" >');
108
109 htp.p('<tr>');
110 htp.p('<td>
111 <INPUT id="search" TYPE="text" VALUE="'
112 ||FND_CSS_PKG.Encode(c_search)
113 ||'" NAME="c_search" SIZE="50" maxlength="180"></label></td>');
114
115 htp.p('<td><INPUT TYPE="submit" VALUE="Search"></td>');
116 htp.p('</tr>');
117 htp.p('</table>');
118 htp.p('</form>');
119
120
121 end draw_form;
122
123
124 function find_table(c_search in varchar2) return number is
125
126 cursor cur_table(n_appid in fnd_tables.application_id%type
127 , n_tabid in fnd_tables.table_id%type) is
128 select table_name
129 , description
130 from fnd_tables
131 where application_id = n_appid
132 and table_id = n_tabid;
133
134 cursor cur_view(n_appid in fnd_tables.application_id%type
135 , n_tabid in fnd_tables.table_id%type) is
136 select view_name
137 , description
138 from fnd_views
139 where application_id = n_appid
140 and view_id = n_tabid;
141
142
143 cursor cur_search is
144 select app_id, obj_id, obj_type, count(*) hits
145 from fnd_etrm_search_tmp
146 group by app_id, obj_id, obj_type
147 order by count(*) desc;
148
149
150
151
152 type va_table_id is table of fnd_etrm_search_tmp.obj_id%type;
153 type va_application_id is table of fnd_etrm_search_tmp.app_id%type;
154 type va_obj_type is table of fnd_etrm_search_tmp.obj_type%type;
155 type va_col_count is table of number(8);
156
157
158 vl_table_id va_table_id;
159 vl_application_id va_application_id;
160 vl_obj_type va_obj_type;
161 vl_col_count va_col_count;
162
163
164 v_table_name fnd_tables.table_name%type;
165 v_column_name fnd_columns.column_name%type;
166 v_description fnd_tables.description%type;
167
168 v_display_count number(3) := 150;
169 v_return_value number(8);
170 v_display_text varchar2(256);
171 v_type varchar2(32);
172
173 v_stmt varchar2(32000);
174
175 begin
176 v_stmt :=
177 'insert into fnd_etrm_search_tmp (app_id, obj_id, obj_type)
178 select application_id, table_id, ''COL''
179 from fnd_columns c1
180 where catsearch(c1.description, :search, null) > 0';
181
182 execute immediate v_stmt using c_search;
183
184 v_stmt :=
185 'insert into fnd_etrm_search_tmp (app_id, obj_id, obj_type)
186 select application_id, table_id, ''TAB''
187 from fnd_tables c1
188 where catsearch(c1.description, :search, null) > 0';
189
190 execute immediate v_stmt using c_search;
191
192 v_stmt :=
193 'insert into fnd_etrm_search_tmp (app_id, obj_id, obj_type)
194 select application_id, view_id, ''VW''
195 from fnd_views c1
196 where catsearch(c1.description, :search, null) > 0';
197
198 execute immediate v_stmt using c_search;
199
200
201
202 open cur_search;
203 fetch cur_search bulk collect into vl_application_id, vl_table_id, vl_obj_type, vl_col_count;
204 close cur_search;
205
206 if vl_application_id.COUNT < v_display_count
207 then v_display_count := vl_application_id.COUNT;
208 end if;
209
210 v_return_value := vl_application_id.COUNT;
211
212 for i in 1 .. v_display_count loop
213
214 if vl_obj_type(i) = 'VW' then
215 open cur_view(n_appid => vl_application_id(i)
216 , n_tabid => vl_table_id(i));
217 fetch cur_view into v_table_name, v_description;
218 close cur_view;
219 v_display_text := v_table_name;
220 v_type := 'VIEW';
221 else
222 open cur_table(n_appid => vl_application_id(i)
223 , n_tabid => vl_table_id(i));
224 fetch cur_table into v_table_name, v_description;
225 close cur_table;
226 v_display_text := v_table_name;
227 v_type := 'TABLE';
228 end if;
229
230 htp.p('<dt>');
231 uiutil.a_javascript_gen(c_type => v_type
232 , c_link => 'etrm_fndnav.show_object?n_appid=' ||vl_application_id(i)
233 ||'&n_tabid=' ||vl_table_id(i)
234 ||'&c_type='||v_type
235 , c_display => v_display_text);
236
237 htp.p('</dt>');
238 if v_description is not null then
239 htp.p('<dd>');
240 htp.p(v_description);
241 htp.p('</dd>');
242 end if;
243
244 end loop;
245 rollback;
246 return v_return_value;
247 end find_table;
248
249
250 procedure find_help_text(c_search in varchar2) is
251
252 cursor cur_find(c_search in varchar2) is
253 select file_id
254 , application
255 , title
256 from fnd_help_documents
257 where catsearch(title, c_search, null) > 0
258 order by application, title;
259
260 begin
261 htp.p('<h3>Documents</h3>');
262 for t_rec in cur_find(c_search) loop
263 htp.p('<dt>');
264 uiutil.a_javascript_gen(c_type => 'APP'
265 , c_link => 'etrm_fndnav.show_file?n_file_id='
266 ||t_rec.file_id
267 , c_display => '<b>'||t_rec.application||':</b> '|| t_rec.title);
268 htp.p('</dt>');
269
270 end loop;
271 end find_help_text;
272
273 -----------------------------------------------------------------------------
274 -- Public Procedures
275 -----------------------------------------------------------------------------
276 PROCEDURE show_file(n_file_id IN fnd_lobs.file_id%type)
277 is
278 cursor cur_get_lob(n_file_id IN fnd_lobs.file_id%type)
279 is
280 select file_data
281 , file_name
282 , file_content_type
283 from fnd_lobs
284 where file_id = n_file_id;
285
286 buffer raw(32767);
287 amount binary_integer := 32767;
288 position integer := 1;
289 chunksize integer;
290 lob_bytes_remaining number(10);
291
292 begin
293 for lob_rec in cur_get_lob(n_file_id) loop
294 lob_bytes_remaining := dbms_lob.getlength(lob_rec.file_data);
295 htp.p('Content-type: '||lob_rec.file_content_type);
296 htp.p('Content-Disposition: attachement; filename='||lob_rec.file_name);
297 htp.p('Content-Transfer-Encoding: base64');
298 -- htp.p('Content-Disposition: inline; filename='||lob_rec.file_name);
299 htp.p('');
300
301 chunksize := dbms_lob.getchunksize(lob_rec.file_data);
302 if (chunksize < 32767) then
303 amount := (32767 / chunksize) * chunksize;
304 end if;
305 dbms_lob.open(lob_rec.file_data, DBMS_LOB.LOB_READONLY);
306
307 while (lob_bytes_remaining > 0) loop
308 if (lob_bytes_remaining < amount) then
309 amount := lob_bytes_remaining;
310 end if;
311 dbms_lob.read(lob_rec.file_data, amount, position, buffer);
312 htp.prn(utl_raw.cast_to_varchar2(buffer));
313 position := position + amount;
314 lob_bytes_remaining := lob_bytes_remaining - amount;
315 end loop;
316 dbms_lob.close(lob_rec.file_data);
317 end loop;
318 end show_file;
319
320
321
322
323 procedure search(c_search in varchar2 := NULL)
324 is
325
326 cursor cur_object(c_search in varchar2) is
327 select object_name
328 , object_type
329 , owner
330 , object_id
331 from dba_objects
332 where upper(object_name) = c_search
333 order by object_id;
334
335
336 cursor cur_app(c_search in varchar2)
337 is
338 select app.application_id
339 , tl.application_name product_name
340 from APPLSYS.FND_APPLICATION_TL tl
341 , APPLSYS.FND_APPLICATION app
342 where app.application_id = tl.application_id
343 and upper(c_search) = decode(app.application_short_name
344 , 'SQLAP', 'AP'
345 , 'SQLGL', 'GL'
346 , app.application_short_name)
347 and tl.language = userenv('LANG');
348
349 vsearch_name varchar2(80);
350 vapps_installation boolean := TRUE;
351 fnd_name varchar2(50);
352 v_items_found number(8);
353 v_search varchar2(512);
354 v_pieces utl_http.html_pieces;
355 v_menu varchar2(2000);
356
357 begin
358
359 v_search := replace(c_search, '&', ' ');
360 v_search := replace(v_search, ' ', '%20');
361 -- Write context information to cookie
362 -- This wraps the following packages in an HTTP header
363 owa_util.mime_header('text/html', FALSE);
364
365 -- Send some value to the target cookie for the next browser call
366 owa_cookie.send('etrm_search_context', v_search);
367
368 owa_util.http_header_close;
369 uiutil.cabo1;
370 uiutil.cabo2a;
371 draw_buttons;
372 uiutil.cabo2b;
373 draw_tabs;
374 uiutil.cabo3;
375
376
377 v_pieces := utl_http.request_pieces(etrm_static.index_url);
378 for i in 1 .. v_pieces.count loop
379 v_menu := v_pieces(i);
380 htp.prn(v_menu);
381 end loop;
382
383
384 uiutil.cabo4;
385 quick_list;
386 uiutil.cabo5;
387 htp.p(etrm_static.etrm_home1);
388 -- draw_form(c_search);
389 if c_search is NULL then
390 htp.p(etrm_static.etrm_home2);
391 else
392 htp.p('<dl>');
393 v_search := upper(c_search);
394 v_items_found := 0;
395
396 for o_rec in cur_object(replace(v_search, ' ', '')) loop
397 v_items_found := v_items_found + 1;
398 htp.p('<dt>');
399 uiutil.a_javascript_gen(c_type => o_rec.object_type
400 , c_link => 'etrm_pnav.show_object?c_name=' ||o_rec.object_name
401 ||'&c_owner=' ||o_rec.owner
402 ||'&c_type=' ||o_rec.object_type
403 , c_display => o_rec.object_name);
404
405 htp.p('</dt>');
406 htp.p('<dd>');
407 htp.p(o_rec.object_type || ' in the '|| o_rec.owner || ' schema.');
408 htp.p('</dd>');
409 end loop;
410
411 for a_rec in cur_app(replace(v_search, ' ', '')) loop
412 v_items_found := v_items_found + 1;
413 htp.p('<dt>');
414 -- uiutil.a_href_gen(c_type => 'ROOT'
415 -- , c_link => 'etrm_fndnav.ls_object?n_appid=' ||a_rec.application_id
416 -- , c_display => a_rec.product_name);
417
418 htp.p('</dt>');
419 htp.p('<dd>');
420 htp.p('Oracle Applications product.');
421 htp.p('</dd>');
422 end loop;
423
424 v_search := c_search;
425 v_search := replace(v_search, '&', ' '); -- catsearch does not like '&' characters
426 v_search := ltrim(v_search);
427 v_search := rtrim(v_search);
428
429 v_search := replace(v_search, '|', '{|}'); -- escape '|' (OR) characters to prevent http 404
430 v_items_found := v_items_found + find_table(v_search);
431 htp.p('</dl>');
432 if v_items_found < 20 then
433 v_items_found := v_items_found + find_table(replace(v_search, chr(32), '{|}'));
434 end if;
435 end if;
436 if v_items_found = 0 then
437 htp.p('Your search: <b>'||FND_CSS_PKG.Encode(c_search)||'</b> did not match any table, view or
438 column description in this database.');
439 end if;
440 uiutil.cabo6;
441 end search;
442
443 procedure help is
444 begin
445 uiutil.cabo1(c_title => 'eTRM Help');
446 htp.p('<body bgcolor="ffffff" onload="javascript:window.focus();">');
447 htp.p('<img src="/images/oraclelogo.gif" alt="Oracle" border="0">
448 <hr size="2" width="100%" noshade align="left">');
449 htp.p(etrm_static.help_text);
450 uiutil.prn_copyright;
451 htp.p('</body>');
452 htp.p('</html>');
453 end help;
454
455 end etrm_search;