[Home] [Help]
PACKAGE BODY: APPS.BIS_BUSINESS_VIEWS_CATALOG_OA
Source
1 PACKAGE BODY bis_business_views_catalog_oa AS
2 /* $Header: BISEULQB.pls 120.1 2005/10/21 07:03:57 ppandey noship $ */
3
4 gv_user_id number;
5
6 -- ************************************************************
7 -- Function to get a business area
8 -- A folder may belong to many business areas. So, pick the
9 -- first Business Area I get.
10 -- ***********************************************************
11 FUNCTION get_a_business_area
12 ( p_folder_id IN PLS_INTEGER
13 , p_eul IN VARCHAR2
14 )
15 return bis_gnrl_search_engine_pvt_oa.results_tbl_typ
16 --RETURN VARCHAR2
17 IS
18
19 l_sql varchar2(1000);
20 --l_ba_name varchar2(256);
21 l_ba_name bis_gnrl_search_engine_pvt_oa.results_tbl_typ;
22
23 TYPE Recdc_ba IS REF CURSOR;
24 dc_ba Recdc_ba;
25
26 --l_csr pls_integer;
27 --l_ignore pls_integer;
28 --l_dummy pls_integer;
29 pv_dc_query varchar2(1000);
30 pv_cntr number;
31 l_disco_table_version varchar2(10);
32 BEGIN
33
34 l_disco_table_version := BIS_GNRL_SEARCH_ENGINE_PVT_OA.get_eul_table_version;
35 pv_dc_query := 'select distinct ' ||
36 ' ba_name ' ||
37 ' ,ba_id ' ||
38 ' from ' ||
39 p_eul || '.' || l_disco_table_version ||'_bas ba, ' ||
40 p_eul || '.' || l_disco_table_version ||'_ba_obj_links bol ' ||
41 ' where ' ||
42 ' bol.bol_obj_id = :1' ||
43 ' and bol.bol_ba_id = ba.ba_id ';
44
45 pv_cntr := 0;
46 open dc_ba for pv_dc_query USING p_folder_id;
47 loop
48 fetch dc_ba into l_ba_name(pv_cntr + 1).folder_name,
49 l_ba_name(pv_cntr + 1).folder_id;
50 exit when dc_ba%NOTFOUND;
51 pv_cntr := pv_cntr + 1;
52 end loop;
53 close dc_ba;
54
55 /*
56 l_csr := DBMS_SQL.open_cursor;
57 BEGIN
58 l_sql:= 'select ba_name, ba_id from ' || p_eul ||'.'|| l_disco_table_version || '_bas ba, ' ||
59 p_eul ||'.' || l_disco_table_version || '_ba_obj_links bol ' ||
60 'where ' || p_folder_id || ' = bol.bol_obj_id ' ||
61 'and bol.bol_ba_id = ba.ba_id ';
62 DBMS_SQL.parse( l_csr, l_sql, dbms_sql.native );
63 DBMS_SQL.define_column( l_csr, 1, l_ba_name, 256 );
64 l_ignore := DBMS_SQL.execute( l_csr );
65
66 l_dummy := DBMS_SQL.fetch_rows( l_csr );
67 IF l_dummy > 0 THEN
68 DBMS_SQL.column_value( l_csr, 1, l_ba_name );
69 ELSE
70 l_ba_name := ' ';
71 END IF;
72 EXCEPTION
73 WHEN OTHERS THEN
74 dbms_sql.close_cursor( l_csr );
75 RAISE;
76 END;
77 dbms_sql.close_cursor( l_csr );
78 */
79
80 RETURN l_ba_name;
81
82 END get_a_business_area;
83
84
85 -- *************************************************************
86 -- Paint the results table
87 -- *************************************************************
88 PROCEDURE results_page
89 ( p_results_tbl IN BIS_GNRL_SEARCH_ENGINE_PVT_OA.results_tbl_typ
90 , p_lang IN VARCHAR2
91 )
92
93 IS
94
95 l_prompts ICX_UTIL.g_prompts_table;
96 l_region_name varchar2(256);
97 l_eul VARCHAR2(256);
98 JAIRl_eul BIS_GNRL_SEARCH_ENGINE_PVT_OA.EUL_results;
99 i PLS_INTEGER;
100 --l_ba varchar2(240);
101 l_ba bis_gnrl_search_engine_pvt_oa.results_tbl_typ;
102
103 pv_is_accessible varchar2(10);
104
105 BEGIN
106
107 --ICX_UTIL.getPrompts(191, 'BIS_BVC_PROMPTS, l_title, l_prompts);
108
109
110 IF (p_results_tbl.COUNT = 0) THEN NULL;
111
112 ELSE
113
114 JAIRl_eul := BIS_GNRL_SEARCH_ENGINE_PVT_OA.get_a_index_owner
115 ( 'BIS_BV_FOLDER_NAMES_' || p_lang );
116
117 IF (JAIRl_eul IS NULL) THEN RAISE BIS_GNRL_SEARCH_ENGINE_PVT_OA.e_noIndexDefined;
118 END IF;
119
120 -- **************************************************
121 -- Begin painting the query results
122 -- ***************************************************
123 for i in 1 ..p_results_tbl.count loop
124
125 --Get a Business Area that this folder belongs to
126 l_ba := get_a_business_area( p_folder_id => p_results_tbl(i).folder_id
127 ,p_eul => p_results_tbl(i).folder_eul );
128
129 --insert values into table.
130 for j in 1..l_ba.count loop
131 pv_is_accessible := Is_Business_Area_Accessible( l_ba(j).folder_id
132 ,gv_user_id
133 ,p_results_tbl(i).folder_eul);
134
135 insert into bis_search_results ( eul
136 ,eul_id
137 ,folder_name
138 ,ba_id
139 ,business_area
140 ,folder_description
141 ,eul_access
142 ,user_id)
143 values( p_results_tbl(i).folder_eul
144 ,p_results_tbl(i).folder_id
145 ,p_results_tbl(i).folder_name
146 ,l_ba(j).folder_id
147 ,l_ba(j).folder_name
148 ,p_results_tbl(i).folder_description
149 ,pv_is_accessible
150 ,gv_user_id);
151
152 end loop; -- End of j loop (l_ba)
153 commit;
154
155 end loop; -- End of the p_results_tbl.count loop
156
157 end if; -- if p_results_tbl.count is zero
158
159 EXCEPTION
160 WHEN BIS_GNRL_SEARCH_ENGINE_PVT_OA.e_noIndexDefined THEN RAISE;
161 WHEN OTHERS THEN htp.p(SQLERRM);
162
163 END results_page;
164
165 -- *********************************************************
166 -- Procedure for the second page to enter keywords
167 -- ********************************************************
168
169 PROCEDURE enter_query_page
170 ( p_keywords in varchar2
171 , p_lang in varchar2
172 )
173
174 IS
175 l_ba varchar2(240);
176 l_folder_description varchar2(240);
177 l_prompts ICX_UTIL.g_prompts_table;
178
179 BEGIN
180
181 --ICX_UTIL.getPrompts(191, 'BIS_BVC_PROMPTS, l_title, l_prompts);
182 --l_submit := l_prompts(5);
183
184 htp.htmlopen;
185 htp.headopen;
186 htp.title( c_title );
187 htp.headclose;
188 htp.p('<body bgcolor="'||icx_plug_utilities.bgcolor||'">');
189
190 htp.p('<table border=0 cellspacing=0 cellpadding=0 width=100%>');
191 htp.p(' <tr> ');
192 ICX_PLUG_UTILITIES.toolbar (
193 p_text => c_title
194 , p_disp_mainmenu => 'Y'
195 , p_disp_menu => 'N'
196 );
197 htp.p('</tr>');
198 htp.p('</table> ');
199
200 htp.p('<tr> ');
201 htp.p('<td> ');
202 htp.p('<br> ');
203 htp.p('</td> ');
204 htp.p('</tr> ');
205
206 htp.p('<table border=0 cellspacing=0 cellpadding=0 width=100%>');
207
208 -- ************** OPEN FORM *******************************
209 htp.formOpen(icx_plug_utilities.getplsqlagent||'BIS_BUSINESS_VIEWS_CATALOG.query'
210 ,'POST','','','NAME="BVC"');
211
212 htp.formHidden('p_lang',p_lang);
213
214 htp.p('<tr> ');
215 htp.p('<td align=center> ');
216 htp.p('<table border=0 cellspacing=0 cellpadding=0 width=10%>');
217
218 htp.p('<tr> ');
219 htp.p('<td align=right> ');
220 htp.p('<input type="text" size=40 name="p_keywords" value="' || p_keywords ||'" >' );
221 htp.p('</td> ');
222
223 -- insert_blank_cell;
224
225
226 htp.p('<td align=left> ');
227 icx_plug_utilities.buttonboth ( c_submit , 'javascript:document.BVC.submit()' );
228 htp.p('</td> ');
229 htp.p('</tr> ');
230 htp.p('</table> ');
231
232 htp.p('</td> ');
233 htp.p('</tr> ');
234
235 htp.formClose;
236 -- ****************** CLOSE FORM ******************************
237
238 htp.p('<tr> ');
239 htp.p('<td> ');
240 htp.p('<br> ');
241 htp.p('</td> ');
242 htp.p('</tr> ');
243
244 htp.p('<tr> ');
245 htp.p('<td> ');
246 htp.p('<br> ');
247 htp.p('</td> ');
248 htp.p('</tr> ');
249
250 htp.p('</table> ');
251
252
253 EXCEPTION
254 WHEN OTHERS THEN
255 htp.p( SQLERRM );
256
257 END enter_query_page;
258
259 -- **********************************************************
260 -- Procedure query is called from the html form as set up by
261 -- the procedures 'enter_query_page_plug' and 'enter_query_page' .
262 -- It validates the search words and then transfers them into
263 -- a plsql table to be sent to the
264 -- BIS_GENERAL_SEARCH_ENGINE_PVT_OA.build_query procedure.
265 -- *************************************************************
266 PROCEDURE query
267 ( p_keywords IN varchar2
268 , p_lang IN varchar2
269 )
270 IS
271 i pls_integer;
272 l_plug_id pls_integer;
273 l_user_id pls_integer;
274 l_return_status VARCHAR2(100);
275 l_eul VARCHAR2(256);
276 JAIRl_eul BIS_GNRL_SEARCH_ENGINE_PVT_OA.eul_results;
277 l_lang VARCHAR2(100);
278 l_length pls_integer;
279 l_startpoint pls_integer;
280 l_separator pls_integer;
281 l_keywords_tbl BIS_GNRL_SEARCH_ENGINE_PVT_OA.keywords_tbl_typ;
282 l_results_tbl BIS_GNRL_SEARCH_ENGINE_PVT_OA.results_tbl_typ;
283 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
284 l_prompts ICX_UTIL.g_prompts_table;
285 l_current_key_word varchar2(2000); -- to hold the current key word
286 v_keywords varchar2(32000);
287
288 p_jair_results_tbl BIS_GNRL_SEARCH_ENGINE_PVT_OA.results_tbl_typ;
289 pv_cnt number := 0;
290
291
292 BEGIN
293
294 -- Delete all the previous results from the table.
295 -- Changed from Truncate to Delete as truncate will not work on synonym.
296 EXECUTE IMMEDIATE 'DELETE FROM bis_search_results';
297
298 -- Will ensure that the previous results from the search table will be deleted permanatly
299 COMMIT;
300
301
302 -- IF (ICX_SEC.validateSession) THEN
303 l_user_id := ICX_SEC.getID(icx_sec.PV_USER_ID);
304
305 -- select USERENV('SESSIONID') into pv_session_id from dual;
306
307 select FND_GLOBAL.user_id into gv_user_id from dual;
308
309
310 -- ****************************************************************
311 -- Transfer the individual words from the input box into
312 -- a plsql table after cleaning and validating the entries
313
314 l_length := LENGTH( p_keywords );
315 -- ONLY if there are any words entered then do the processing
316 -- else just paint the word entry screen again
317
318 IF (l_length > 0) THEN
319 -- replace any commas with a space
320 v_keywords := REPLACE(p_keywords, ',', ' ');
321 v_keywords := REPLACE(v_keywords, '''', '''''');
322 v_keywords := REPLACE(v_keywords, '\', '\\');
323 v_keywords := REPLACE(v_keywords, '}', '\}');
324 i := 1;
325 l_startpoint := 1;
326 -- ********* Begin wordlist PARSER *************
327 WHILE (l_startpoint <= l_length) LOOP
328 l_separator := INSTR(v_keywords,' ',l_startpoint,1);
329 IF (l_separator > 0) THEN -- If there is atleast one space or comma
330 l_current_key_word := LTRIM(RTRIM(SUBSTR(v_keywords,l_startpoint,l_separator - l_startpoint)));
331 IF(l_current_key_word IS NOT NULL) THEN --Add a keyword to the list only if it is not null
332 l_keywords_tbl(i) := l_current_key_word;
333 i := i + 1;
334 END IF;
335 l_startpoint := l_separator + 1;
336
337 IF (l_startpoint = INSTR(v_keywords,' ',l_startpoint,1)) THEN
338 -- In case the next char is also a space or a comma the startpoint is
339 -- incremented once more
340 l_startpoint := l_startpoint + 1;
341 END IF; -- endif for checking if the next char is the same as this one
342 END IF;
346 IF ( LTRIM(RTRIM(v_keywords)) IS NOT NULL) THEN
343
344 IF ((l_separator = 0) AND (l_startpoint = 1)) THEN
345 -- just one word in inputbox so do not loop anymore
347 l_keywords_tbl(i) := LTRIM(RTRIM(v_keywords));
348 END IF;
349 EXIT;
350 ELSIF
351 ((l_separator = 0) AND (l_startpoint > 1)) THEN
352 -- or if this is the last word in the word list
353 IF( SUBSTR(v_keywords,l_startpoint) IS NOT NULL) THEN
354 l_keywords_tbl(i) := SUBSTR(v_keywords,l_startpoint);
355 END IF;
356 EXIT;
357 END IF; -- end if to see where we are in string parsing
358
359 END LOOP; -- end of while loop
360 -- ********************************************************************
361 -- ************* End of wordlist PARSER *****************************
362
363 -- Clip the ends of the language code to remove spurious spaces
364 l_lang := ltrim(rtrim( p_lang ));
365
366
367 -- if Business Views InterMedia Indexes have been installed in
368 -- several schemas, some schema is chosen, arbitrarily.
369 JAIRl_eul := BIS_GNRL_SEARCH_ENGINE_PVT_OA.get_a_index_owner( 'BIS_BV_FOLDER_NAMES_' || l_lang );
370
371
372 IF (JAIRl_eul IS NULL) THEN RAISE BIS_GNRL_SEARCH_ENGINE_PVT_OA.e_noIndexDefined;
373 END IF;
374
375 for i in 1..JAIRl_eul.count loop
376 BEGIN
377 -- Now call the package to build and run the InterMedia
378 -- query and obtain the best row hits
379 BIS_GNRL_SEARCH_ENGINE_PVT_OA.build_query
380 ( p_api_version => 1.0
381 ,p_eul => JAIRl_eul(i).eul_schema
382 ,p_keywords_tbl => l_keywords_tbl
383 ,x_results_tbl => l_results_tbl
384 ,x_return_status => l_return_status
385 ,x_error_tbl => l_error_tbl
386 );
387
388 --update the number of hits, this includes all hits for all EULS.
389 for j in 1..l_results_tbl.count loop
390 p_jair_results_tbl(pv_cnt + j).folder_id := l_results_tbl(j).folder_id;
391 p_jair_results_tbl(pv_cnt + j).folder_name := l_results_tbl(j).folder_name;
392 p_jair_results_tbl(pv_cnt + j).folder_description := l_results_tbl(j).folder_description;
393 p_jair_results_tbl(pv_cnt + j).folder_eul := l_results_tbl(j).folder_eul;
394 end loop;
395
396 pv_cnt := p_jair_results_tbl.count;
397
398 EXCEPTION
399 WHEN OTHERS THEN RAISE;
400
401 END; -- end of begin-end block of call to procedure which will
402 -- build and run the INterMedia query
403
404 end loop;
405
406
407 for k in 1..p_jair_results_tbl.count loop
408 l_results_tbl(k).folder_id := p_jair_results_tbl(k).folder_id;
409 l_results_tbl(k).folder_name := p_jair_results_tbl(k).folder_name;
410 l_results_tbl(k).folder_description := p_jair_results_tbl(k).folder_description;
411 l_results_tbl(k).folder_eul := p_jair_results_tbl(k).folder_eul;
412 end loop;
413
414
415 IF l_return_status = fnd_api.G_RET_STS_SUCCESS THEN
416 -- First print out the keywords entry box again for future tries
417 enter_query_page( p_keywords => p_keywords
418 , p_lang => p_lang);
419 -- Then print out the result set
420 results_page( p_results_tbl => p_jair_results_tbl
421 , p_lang => p_lang);
422
423 END IF;
424
425 -- *********************************************************
426 ELSE -- If no keywords entered in the box<l_length(p_keywords) = 0>
427 -- Print out the keywords entry box again for future tries
428 enter_query_page( p_keywords, p_lang );
429 htp.bodyClose;
430 htp.htmlClose;
431
432 END IF; -- endif for l_length of the keywords entered not equal to zero
433 -- *********************************************************
434
435
436 -- END IF; -- ICX_SEC.validatesession()
437
438
439 EXCEPTION
440 WHEN BIS_GNRL_SEARCH_ENGINE_PVT.e_noIndexDefined THEN
441 -- The following message need not be translated since this is a propagated error
442 -- due to undefined InterMedia domain Index. The Preferences and Indexes must
443 -- be created first, for the Business Views Catalog Search region to work !!
444 htp.p('<BR><BR>');
445 htp.p('ERROR : InterMedia Index Not Created in Language - '||p_lang||'<BR>');
446 htp.p('Please run BISPBVI.sql in the Discoverer eul schema with appropriate Language code as parameter.'||'<BR>');
447 WHEN OTHERS THEN
448 rollback;
449 raise;
450 END query;
451
452
453 -- **********************************************************
454 -- Procedure container
455 --
456 --
457 --
458 --
459 -- *************************************************************
460
461 PROCEDURE Container(
462 p_keywords in varchar2
463 ,p_lang in varchar2
464 ,p_results_tbl IN BIS_GNRL_SEARCH_ENGINE_PVT.results_tbl_typ
465 ) is
466
467 begin
468
469 /*
470 -- First print out the keywords entry box again for future tries
471 enter_query_page( p_keywords => p_keywords
472 , p_lang => p_lang);
476 */
473 -- Then print out the result set
474 results_page( p_results_tbl => p_results_tbl
475 ,p_lang => p_lang);
477
478 htp.headopen;
479 htp.p('<SCRIPT>');
480 icx_admin_sig.help_win_script('ASKORA', null, 'FND');
481 htp.p('</SCRIPT>');
482 htp.headclose;
483
484 htp.p('<html>');
485 htp.p('<head>');
486 htp.p('<title>BV Catalog</title>');
487 htp.p('</head>');
488 htp.p('<body bgcolor="#CCCCCC">');
489 htp.p('<FORM TARGET="_top" METHOD=POST>');
490 -- enter_query_page( p_keywords => p_keywords
491 -- , p_lang => p_lang);
492 htp.p('<H3>Hello there</H3>');
493
494 htp.p('</FORM>');
495 htp.p('</body>');
496 htp.p('</html>');
497
498
499
500 end Container;
501
502 /*************************************************************************************
503 *************************************************************************************/
504
505 function Is_Business_Area_Accessible(
506 x_ba_id number,
507 x_apps_user_id number,
508 x_eul varchar2
509 ) return varchar2 is
510
511 pv_ba_total number;
512
513 pv_accessible varchar2(10);
514 pv_dc_query varchar2(2000);
515 l_disco_table_version varchar2(10);
516 begin
517
518 l_disco_table_version := BIS_GNRL_SEARCH_ENGINE_PVT_OA.get_eul_table_version;
519 pv_dc_query := 'select ' ||
520 ' count (distinct ap.gba_ba_id) ' ||
521 ' from ' ||
522 x_eul || '.' || l_disco_table_version ||'_bas ba, ' ||
523 x_eul || '.' || l_disco_table_version ||'_access_privs ap, ' ||
524 x_eul || '.' || l_disco_table_version ||'_eul_users eu, ' ||
525 ' fnd_responsibility_vl r, ' ||
526 ' fnd_application a, ' ||
527 ' fnd_user_resp_groups ur, ' ||
528 ' fnd_user u, ' ||
529 ' fnd_security_groups_vl s, ' ||
530 ' fnd_data_group_units dgu, ' ||
531 ' fnd_oracle_userid ou ' ||
532 ' where ' ||
533 ' ba.ba_id = ap.gba_ba_id and ' ||
534 ' ba.ba_id = :1 and ' ||
535 ' ap.ap_eu_id = eu.eu_id and ' ||
536 ' eu.eu_role_flag = 1 and ' ||
537 ' ap.ap_type = ''GBA'' and ' ||
538 ' ur.responsibility_id = TO_NUMBER(SUBSTR(eu.eu_username,2,(INSTR(eu.eu_username,''#'',2)-2))) and ' ||
539 ' ur.responsibility_application_id = TO_NUMBER(SUBSTR(eu.eu_username,INSTR(eu.eu_username,''#'',2)+1)) and ' ||
540 ' u.user_id = :2 and ' ||
541 ' u.user_id = ur.user_id and ' ||
542 ' (sysdate BETWEEN ur.start_date AND NVL(ur.end_date, sysdate)) and ' ||
543 ' r.application_id = ur.responsibility_application_id and ' ||
544 ' r.responsibility_id = ur.responsibility_id and ' ||
545 ' (sysdate BETWEEN r.start_date AND NVL(r.end_date, sysdate)) and ' ||
546 ' r.application_id = a.application_id and ' ||
547 ' ur.security_group_id IN (-1, s.security_group_id) and ' ||
548 ' s.security_group_id >= 0 and ' ||
549 ' r.data_group_id = dgu.data_group_id and ' ||
550 ' r.data_group_application_id = dgu.application_id and ' ||
551 ' dgu.oracle_id = ou.oracle_id';
552
553 EXECUTE IMMEDIATE pv_dc_query INTO pv_ba_total USING x_ba_id, x_apps_user_id;
554
555 if pv_ba_total > 0 then
556 pv_accessible := 'Yes';
557 else
558 pv_accessible := 'No';
559 end if;
560
561 return pv_accessible;
562
563
564 EXCEPTION
565 WHEN OTHERS then
566 return 'No';
567 rollback;
568
569 end Is_Business_Area_Accessible;
570
571 -- ************************************************
572 -- ************************************************
573 END bis_business_views_catalog_oa;
574
575
576
577