[Home] [Help]
PACKAGE BODY: APPS.BIS_BUSINESS_VIEWS_CATALOG
Source
1 PACKAGE BODY bis_business_views_catalog AS
2 /* $Header: BISPBVCB.pls 115.5 2000/08/14 12:12:32 pkm ship $ */
3
4 -- *******************************************************
5 -- This displays the plug on the Personal Home Page
6 -- *******************************************************
7 PROCEDURE enter_query_page_plug
8 ( p_session_id IN pls_integer
9 , p_plug_id IN pls_integer
10 , p_display_name IN VARCHAR2 DEFAULT NULL
11 , p_delete IN VARCHAR2 DEFAULT 'N'
12 )
13
14 IS
15 l_lang varchar2(100);
16 l_submit varchar2(240);
17 l_title varchar2(100);
18 l_prompts ICX_UTIL.g_prompts_table;
19 l_user_id pls_integer;
20
21 BEGIN
22
23 IF upper( p_delete ) <> 'Y' -- if p_delete is set to yes, STOP
24 AND ICX_SEC.validatePlugSession( p_plug_id, p_session_id ) THEN
25
26 -- Get the Language CODE and USERID from the ICX environment
27 l_user_id := ICX_SEC.getID(ICX_SEC.pv_user_id, '', p_session_id);
28 l_lang := ICX_SEC.getID(icx_sec.PV_LANGUAGE_CODE);
29
30
31 -- Begin painting the table structure for the plug
32 htp.p('<table border=0 cellspacing=0 cellpadding=0 width=100%>');
33
34 htp.p('<tr> ');
35 htp.p('<td> ');
36 ICX_PLUG_UTILITIES.plugbanner
37 ( p_text => NVL(p_display_name, c_title)
38 , p_icon => 'FNDBVCAT.gif'
39 );
40 htp.p('</td> ');
41 htp.p('</tr> ');
42
43 htp.p('<tr> ');
44 htp.p('<td> ');
45 htp.p('<font size=-2> ');
46 htp.p('<br> ');
47 htp.p('</font> ');
48 htp.p('</td> ');
49 htp.p('</tr> ');
50
51 htp.p('<tr> ');
52 htp.p('<td colspan=1 align=left>');
53 htp.p('<table border=0 cellspacing=0 cellpadding=0 width=10%>');
54 htp.p('<tr> ');
55 -- ********************** OPEN FORM ************************
56 htp.formOpen(icx_plug_utilities.getplsqlagent||'BIS_BUSINESS_VIEWS_CATALOG.query'
57 ,'POST','','','NAME="BVC"');
58 htp.formHidden('p_lang',l_lang);
59
60 htp.p('<td align=right> ');
61 htp.p('<input type="text" size=40 name="p_keywords"> ');
62 htp.p('</td> ');
63
64 insert_blank_cell;
65
66 htp.p('<td align=left> ');
67 icx_plug_utilities.buttonboth (c_submit, 'javascript:document.BVC.submit()');
68 htp.p('</td> ');
69
70 htp.formClose;
71 -- ************************ CLOSE FORM ***********************
72 htp.p('</tr> ');
73 htp.tableClose;
74
75 htp.p('</td> ');
76 htp.p('</tr> ');
77
78 htp.p('<tr> ');
79 htp.p('<td> ');
80 htp.p('<br> ');
81 htp.p('</td> ');
82 htp.p('</tr> ');
83
84 htp.tableClose;
85
86 END IF;
87
88 EXCEPTION
89 WHEN OTHERS THEN
90 htp.p( SQLERRM );
91
92 END enter_query_page_plug;
93
94 -- ************************************************************
95 -- Function to get a business area
96 -- A folder may belong to many business areas. So, pick the
97 -- first Business Area I get.
98 -- ***********************************************************
99 FUNCTION get_a_business_area
100 ( p_folder_id IN PLS_INTEGER
101 , p_eul IN VARCHAR2
102 )
103 RETURN VARCHAR2
104 IS
105
106 l_sql varchar2(1000);
107 l_ba_name varchar2(256);
108 l_csr pls_integer;
109 l_ignore pls_integer;
110 l_dummy pls_integer;
111
112 BEGIN
113
114 l_csr := DBMS_SQL.open_cursor;
115 BEGIN
116 l_sql:= 'select ba_name from ' || p_eul ||'.eul_business_areas ba, ' ||
117 p_eul ||'.eul_ba_obj_links bol ' ||
118 'where ' || p_folder_id || ' = bol.bol_obj_id ' ||
119 'and bol.bol_ba_id = ba.ba_id ';
120 DBMS_SQL.parse( l_csr, l_sql, dbms_sql.native );
121 DBMS_SQL.define_column( l_csr, 1, l_ba_name, 256 );
122 l_ignore := DBMS_SQL.execute( l_csr );
123
124 l_dummy := DBMS_SQL.fetch_rows( l_csr );
125 IF l_dummy > 0 THEN
126 DBMS_SQL.column_value( l_csr, 1, l_ba_name );
127 ELSE
128 l_ba_name := ' ';
129 END IF;
130 EXCEPTION
131 WHEN OTHERS THEN
132 dbms_sql.close_cursor( l_csr );
133 RAISE;
134 END;
135 dbms_sql.close_cursor( l_csr );
136
137 RETURN l_ba_name;
138
139 END get_a_business_area;
140
141 -- *********************************************************
142 -- Paint the Heading cell
143 -- *********************************************************
144 procedure insert_heading_cell( p_text in varchar2 )
145 is
146 begin
147
148 htp.p('<th align=left bgcolor=' || icx_plug_utilities.headingcolor || '>');
149 htp.p(' <table border=0 cellspacing=0 cellpadding=1 >');
150 htp.p(' <tr> ');
151 htp.p(' <th align=left>');
152 htp.p(' <font face="Arial" size=2> ');
153 htp.p( p_text );
154 htp.p(' </th>');
155 htp.p(' </tr> ');
156 htp.p(' </table> ');
157 htp.p('</th> ');
158
159 end insert_heading_cell;
160
161 -- ************************************************************
162 -- Paint a blank heading cell
163 -- ***********************************************************
164 procedure insert_blank_heading_cell
165 is
166 begin
167
168 htp.p('<th bgcolor=' || icx_plug_utilities.headingcolor || '>');
169 htp.p(' <table border=0 cellspacing=0 cellpadding=1 >');
170 htp.p(' <tr> ');
171 htp.p(' <td>');
172 htp.p(' <br> ');
173 htp.p(' </td>');
174 htp.p(' </tr> ');
175 htp.p(' </table> ');
176 htp.p('</th> ');
177
178 end insert_blank_heading_cell;
179
180 -- ************************************************************
181 -- Paint a blank cell
182 -- ************************************************************
183 procedure insert_blank_cell
184 is
185 begin
186
187 htp.p('<TD>'||'&'||'nbsp</TD>');
188
189 end insert_blank_cell;
190
191
192 -- *************************************************************
193 -- Paint the results table
194 -- *************************************************************
195 PROCEDURE results_page
196 ( p_results_tbl IN BIS_GNRL_SEARCH_ENGINE_PVT.results_tbl_typ
197 , p_lang IN VARCHAR2
198 )
199
200 IS
201
202 l_prompts ICX_UTIL.g_prompts_table;
203 l_region_name varchar2(256);
204 l_eul VARCHAR2(256);
205 i PLS_INTEGER;
206 l_ba varchar2(240);
207
208 BEGIN
209
210 --ICX_UTIL.getPrompts(191, 'BIS_BVC_PROMPTS, l_title, l_prompts);
211
212 htp.p('<body bgcolor="'||icx_plug_utilities.bgcolor||'">');
213
214 IF (p_results_tbl.COUNT = 0) THEN NULL;
215
216 ELSE
217 -- htp.p('Total Hits = '||p_results_tbl.COUNT );
218
219 -- Begin painting the table containing the results
220 -- ********* Paint the Headers first **************
221 htp.p('<table border=0 cellspacing=0 cellpadding=0 width=100% >');
222 htp.p(' <tr> ');
223 insert_blank_heading_cell;
224 insert_heading_cell(c_folder);
225
226 insert_blank_heading_cell;
227
228 htp.p('<th align=left nowrap bgcolor='||icx_plug_utilities.headingcolor||'>');
229 htp.p('<font face="Arial" size=2> ');
230 htp.p(c_busarea);
231 htp.p('</th> ');
232
233 insert_blank_heading_cell;
234
235 htp.p('<th align=left nowrap bgcolor='||icx_plug_utilities.headingcolor||'>');
236 htp.p('<font face="Arial" size=2> ');
237 htp.p(c_desc);
238 htp.p('</th> ');
239 htp.p('</tr> ');
240 -- ******************** Headers end **********************
241
242 l_eul := BIS_GNRL_SEARCH_ENGINE_PVT.get_a_index_owner
243 ( 'BIS_BV_FOLDER_NAMES_' || p_lang );
244
245 IF (l_eul IS NULL) THEN RAISE BIS_GNRL_SEARCH_ENGINE_PVT.e_noIndexDefined;
246 END IF;
247
248 -- **************************************************
249 -- Begin painting the query results
250 -- ***************************************************
251 FOR i in 1 ..p_results_tbl.COUNT LOOP
252
253 --Get a Business Area that this folder belongs to
254 l_ba := get_a_business_area( p_folder_id => p_results_tbl(i).folder_id
255 ,p_eul => l_eul );
256
257 -- Start the table rows
258 htp.p('<tr>');
259 htp.p('<td nowrap>');
260 htp.p('<br>');
261 htp.p('</td>');
262 htp.p('<td nowrap>');
263 htp.p(p_results_tbl(i).folder_name);
264 htp.p('</td>');
265 htp.p('<td nowrap>');
266 htp.p('<br>');
267 htp.p('</td>');
268 htp.p('<td nowrap>');
269 htp.p( l_ba );
270 htp.p('</td> ');
271 htp.p('<td nowrap> ');
272 htp.p('<br>');
273 htp.p('</td>');
274 htp.p('<td>');
275 htp.p( p_results_tbl(i).folder_description );
276 htp.p('</td>');
277 htp.p('</tr>');
278
279 END LOOP; -- End of the p_results_tbl.COUNT loop
280 htp.p('</table> ');
281
282 END IF; -- if p_results_tbl.COUNT is ZERO
283
284 htp.bodyClose;
285 htp.htmlClose;
286 EXCEPTION
287 WHEN BIS_GNRL_SEARCH_ENGINE_PVT.e_noIndexDefined THEN RAISE;
288 WHEN OTHERS THEN htp.p(SQLERRM);
289
290 END results_page;
291
292 -- *********************************************************
293 -- Procedure for the second page to enter keywords
294 -- ********************************************************
295
296 PROCEDURE enter_query_page
297 ( p_keywords in varchar2
298 , p_lang in varchar2
299 )
300
301 IS
302 l_ba varchar2(240);
303 l_folder_description varchar2(240);
304 l_prompts ICX_UTIL.g_prompts_table;
305
306 BEGIN
307
308 --ICX_UTIL.getPrompts(191, 'BIS_BVC_PROMPTS, l_title, l_prompts);
309 --l_submit := l_prompts(5);
310
311 htp.htmlopen;
312 htp.headopen;
313 htp.title( c_title );
314 htp.headclose;
315 htp.p('<body bgcolor="'||icx_plug_utilities.bgcolor||'">');
316
317 htp.p('<table border=0 cellspacing=0 cellpadding=0 width=100%>');
318 htp.p(' <tr> ');
319 ICX_PLUG_UTILITIES.toolbar (
320 p_text => c_title
321 , p_disp_mainmenu => 'Y'
322 , p_disp_menu => 'N'
323 );
324 htp.p('</tr>');
325 htp.p('</table> ');
326
327 htp.p('<tr> ');
328 htp.p('<td> ');
329 htp.p('<br> ');
330 htp.p('</td> ');
331 htp.p('</tr> ');
332
333 htp.p('<table border=0 cellspacing=0 cellpadding=0 width=100%>');
334
335 -- ************** OPEN FORM *******************************
336 htp.formOpen('BIS_BUSINESS_VIEWS_CATALOG.query'
337 ,'POST','','','NAME="BVC"');
338
339 htp.formHidden('p_lang',p_lang);
340
341 htp.p('<tr> ');
342 htp.p('<td align=center> ');
343 htp.p('<table border=0 cellspacing=0 cellpadding=0 width=10%>');
344
345 htp.p('<tr> ');
346 htp.p('<td align=right> ');
347 htp.p('<input type="text" size=40 name="p_keywords" value="' || p_keywords ||'" >' );
348 htp.p('</td> ');
349
350 insert_blank_cell;
351
352 htp.p('<td align=left> ');
353 icx_plug_utilities.buttonboth ( c_submit , 'javascript:document.BVC.submit()' );
354 htp.p('</td> ');
355 htp.p('</tr> ');
356 htp.p('</table> ');
357
358 htp.p('</td> ');
359 htp.p('</tr> ');
360
361 htp.formClose;
362 -- ****************** CLOSE FORM ******************************
363
364 htp.p('<tr> ');
365 htp.p('<td> ');
366 htp.p('<br> ');
367 htp.p('</td> ');
368 htp.p('</tr> ');
369
370 htp.p('<tr> ');
371 htp.p('<td> ');
372 htp.p('<br> ');
373 htp.p('</td> ');
374 htp.p('</tr> ');
375
376 htp.p('</table> ');
377
378
379 EXCEPTION
380 WHEN OTHERS THEN
381 htp.p( SQLERRM );
382
383 END enter_query_page;
384
385 -- **********************************************************
386 -- Procedure query is called from the html form as set up by
387 -- the procedures 'enter_query_page_plug' and 'enter_query_page' .
388 -- It validates the search words and then transfers them into
389 -- a plsql table to be sent to the
390 -- BIS_GENERAL_SEARCH_ENGINE_PVT.build_query procedure.
391 -- *************************************************************
392 PROCEDURE query
393 ( p_keywords IN varchar2
394 , p_lang IN varchar2
395 )
396 IS
397 i pls_integer;
398 l_plug_id pls_integer;
399 l_user_id pls_integer;
400 l_return_status VARCHAR2(100);
401 l_eul VARCHAR2(256);
402 l_lang VARCHAR2(100);
403 l_length pls_integer;
404 l_startpoint pls_integer;
405 l_separator pls_integer;
406 l_keywords_tbl BIS_GNRL_SEARCH_ENGINE_PVT.keywords_tbl_typ;
407 l_results_tbl BIS_GNRL_SEARCH_ENGINE_PVT.results_tbl_typ;
408 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
409 l_prompts ICX_UTIL.g_prompts_table;
410 v_keywords varchar2(32000);
411
412
413 BEGIN
414 IF (ICX_SEC.validateSession) THEN
415 l_user_id := ICX_SEC.getID(icx_sec.PV_USER_ID);
416
417 -- ****************************************************************
418 -- Transfer the individual words from the input box into
419 -- a plsql table after cleaning and validating the entries
420 l_length := LENGTH( p_keywords );
421 -- ONLY if there are any words entered then do the processing
422 -- else just paint the word entry screen again
423 IF (l_length > 0) THEN
424 -- replace any commas with a space
425 v_keywords := REPLACE(p_keywords, ',', ' ');
426 i := 1;
427 l_startpoint := 1;
428
429 -- ********* Begin wordlist PARSER *************
430 WHILE (l_startpoint < l_length) LOOP
431 l_separator := INSTR(v_keywords,' ',l_startpoint,1);
432
433 IF (l_separator > 0) THEN -- If there is atleast one space or comma
434 l_keywords_tbl(i) := LTRIM(RTRIM(SUBSTR(v_keywords,l_startpoint,l_separator - l_startpoint)));
435 l_startpoint := l_separator + 1;
436 i := i + 1;
437
438 IF (l_startpoint = INSTR(v_keywords,' ',l_startpoint,1)) THEN
439 -- In case the next char is also a space or a comma the startpoint is
440 -- incremented once more
441 l_startpoint := l_startpoint + 1;
442 END IF; -- endif for checking if the next char is the same as this one
443 END IF;
444
445 IF ((l_separator = 0) AND (l_startpoint = 1)) THEN
446 -- just one word in inputbox so do not loop anymore
447 l_keywords_tbl(i) := LTRIM(RTRIM(v_keywords));
451 -- or if this is the last word in the word list
448 EXIT;
449 ELSIF
450 ((l_separator = 0) AND (l_startpoint > 1)) THEN
452 l_keywords_tbl(i) := SUBSTR(v_keywords,l_startpoint);
453 EXIT;
454 END IF; -- end if to see where we are in string parsing
455
456 END LOOP; -- end of while loop
457 -- ********************************************************************
458 -- ************* End of wordlist PARSER *****************************
459
460 -- Clip the ends of the language code to remove spurious spaces
461 l_lang := ltrim(rtrim( p_lang ));
462
463
464 -- if Business Views InterMedia Indexes have been installed in
465 -- several schemas, some schema is chosen, arbitrarily.
466
467 l_eul := BIS_GNRL_SEARCH_ENGINE_PVT.get_a_index_owner( 'BIS_BV_FOLDER_NAMES_' || l_lang );
468
469 IF (l_eul IS NULL) THEN RAISE BIS_GNRL_SEARCH_ENGINE_PVT.e_noIndexDefined;
470 END IF;
471
472 /************* FOR DEBUGGING *************************
473 htp.p('11111111111111111111111111111111111111111'||'<BR>');
474 htp.p('p_lang :'||p_lang||'<BR>');
475 htp.p('p_keywords :'||p_keywords||'<BR>');
476 htp.p('v_keywords :'||v_keywords||'<BR>');
477 htp.p('l_length :'||l_length||'<BR>');
478 htp.p('l_eul :'||l_eul||'<BR>');
479 htp.p('22222222222222222222222222222222222222222222'||'<BR>');
480 htp.p('l_keywords_tbl COUNT :'||l_keywords_tbl.COUNT||'<BR>');
481 for i in 1 .. l_keywords_tbl.COUNT loop
482 htp.p('l_keywords_tbl_'||i||' :'||l_keywords_tbl(i)||'<BR>');
483 end loop;
484 htp.p('33333333333333333333333333333333333333333'||'<BR>');
485 ************* FOR DEBUGGING *************************/
486
487 BEGIN
488 -- Now call the package to build and run the InterMedia
489 -- query and obtain the best row hits
490 BIS_GNRL_SEARCH_ENGINE_PVT.build_query
491 ( p_api_version => 1.0
492 ,p_eul => l_eul
493 ,p_keywords_tbl => l_keywords_tbl
494 ,x_results_tbl => l_results_tbl
495 ,x_return_status => l_return_status
496 ,x_error_tbl => l_error_tbl
497 );
498
499 EXCEPTION
500 WHEN OTHERS THEN RAISE;
501
502 END; -- end of begin-end block of call to procedure which will
503 -- build and run the INterMedia query
504
505 IF l_return_status = fnd_api.G_RET_STS_SUCCESS THEN
506 -- First print out the keywords entry box again for future tries
507 enter_query_page( p_keywords => p_keywords
508 , p_lang => p_lang );
509 -- Then print out the result set
510 results_page( p_results_tbl => l_results_tbl
511 , p_lang => p_lang );
512 END IF;
513
514 -- *********************************************************
515 ELSE -- If no keywords entered in the box<l_length(p_keywords) = 0>
516 -- Print out the keywords entry box again for future tries
517 enter_query_page( p_keywords, p_lang );
518 htp.bodyClose;
519 htp.htmlClose;
520
521 END IF; -- endif for l_length of the keywords entered not equal to zero
522 -- *********************************************************
523
524
525 END IF; -- ICX_SEC.validatesession()
526
527 EXCEPTION
528 WHEN BIS_GNRL_SEARCH_ENGINE_PVT.e_noIndexDefined THEN
529 -- The following message need not be translated since this is a propagated error
530 -- due to undefined InterMedia domain Index. The Preferences and Indexes must
531 -- be created first, for the Business Views Catalog Search region to work !!
532 htp.p('<BR><BR>');
533 htp.p('ERROR : InterMedia Index Not Created in Language - '||p_lang||'<BR>');
534 htp.p('Please run BISPBVI.sql in the Discoverer eul schema with appropriate Language code as parameter.'||'<BR>');
535 WHEN OTHERS THEN
536 htp.p( SQLERRM );
537 END query;
538
539 -- ************************************************
540 -- ************************************************
541 END bis_business_views_catalog;