1 package body BIS_GNRL_SEARCH_ENGINE_PVT_OA AS
2 /* $Header: BISSRCQB.pls 120.1 2006/08/21 12:41:54 akoduri noship $ */
3
4
5 -- **********************************************************
6 -- Procedure to build the InterMedia query
7 -- *********************************************************
8 Procedure build_query (
9 p_api_version in pls_integer
10 ,p_eul in varchar2
11 ,p_keywords_tbl in BIS_GNRL_SEARCH_ENGINE_PVT_OA.keywords_tbl_typ
12 ,x_results_tbl out NOCOPY BIS_GNRL_SEARCH_ENGINE_PVT_OA.results_tbl_typ
13 ,x_return_status out NOCOPY varchar2
14 ,x_error_tbl out NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
15 )
16 IS
17 v_eul varchar2(256);
18 l_sql varchar2(32000);
19 l_wordlist varchar2(32000);
20 l_dummy pls_integer;
21 l_count pls_integer;
22 l_cursor pls_integer;
23 l_score1 pls_integer;
24 l_score2 pls_integer;
25 l_score3 pls_integer;
26 l_folder_id pls_integer;
27 l_folder_name varchar2(400);
28 l_folder_description varchar2(32000);
29 l_results_tbl BIS_GNRL_SEARCH_ENGINE_PVT_OA.results_tbl_typ;
30
31
32 BEGIN
33
34
35
36 x_return_status := FND_API.G_RET_STS_SUCCESS;
37 IF(p_keywords_tbl.COUNT = 0) THEN
38 RETURN;
39 END IF;
40 -- To make sure the Intermedia query does not break becuase of lack of schema name
41 IF (p_eul IS NOT NULL) THEN
42 v_eul := p_eul ||'.';
43 ELSE
44 v_eul := p_eul;
45 END IF;
46
47 -- Construct the search words list from the plsql
48 -- table containing the words and add the appropriate InterMedia OPERATORS
49 FOR i in 1 .. p_keywords_tbl.COUNT LOOP
50 IF i = p_keywords_tbl.COUNT THEN
51 l_wordlist := l_wordlist || c_stem_optr ||'{'|| p_keywords_tbl(i) ||'}';
52 EXIT;
53 END IF;
54 l_wordlist := l_wordlist || c_stem_optr ||'{'||p_keywords_tbl(i)||'}'|| c_accum_optr;
55 END LOOP;
56
57 -- Add the appropriate number of ' to the ends of the word list to be embedded in the
58 -- Intermedia query
59 l_wordlist := concat_string(l_wordlist);
60
61
62 -- ******************************************************************************
63 -- CONSTRUCT THE INTERMEDIA SQL QUERY
64 -- The '$' STEM operator creates a linguistic root of the word supplied
65 -- and returns all row hits that contain words which could be probably
66 -- generated by this root word.
67 -- The ',' ACCUM operator accumulates the list of words supplied and returns
68 -- the highest score for hits containing all of the words and then corresponding
69 -- lower scores for subsequent row hits containing one or more words.
70 -- The '{ }' ESCAPE SEQUENCE operators escape a group of reserved characters if exists.
71 -- Example 1 : For word1 and word2 supplied, the query 'Contains(colname, 'word1,word2',2)
72 -- will give the highest score for row hits containing both the words, and then
73 -- a lower score for row hits for occurences of word1 OR word2.
74 -- Example 2 : If the word contains 'function-layout', it will be read as {function-layout}
75 -- as one word inclusive of the '-', where the '-' is escaped.
76 -- QUERY FORMULA
77 -- The following query will return rows that got the best hits according to
78 -- the following priority order...
79 -- 1) All of the search words exist in eul_objs.obj_name column (Folder name)
80 -- 2) One or more search words exist in eul_objs.obj_name column (Folder name)
81 -- 3) All of the search words exist in eul_objs.obj_description col (Fldr desc.)
82 -- 4) One or more search words exist in eul_objs.obj_description col (Fldr desc)
83 -- 5) Now the detail table (eul_expressions.exp_name) is searched. This contains
84 -- the cols that make up the above Folder/business view. These cols are searched
85 -- for every folder_id (eul_objs.obj_id) as one singe 'document set', so that
86 -- only one row is returned for hits on any of the folder columns. This part
87 -- is taken care by the appropriate index 'BIS_BV_ITEM_NAMES_||'LANG''
88 -- ****************************************************************************
89
90 l_sql := ' select score(10), score(20), ' ||
91 ' obj_id, obj_name, obj_description ' ||
92 ' from ' || v_eul || get_eul_table_version ||'_objs ' ||
93 ' where contains (obj_name, ' || l_wordlist || ', 10) > 0 ' ||
94 ' or contains (obj_description, ' || l_wordlist || ', 20) > 0 ' ||
95 ' order by score(10) DESC ,score(20) DESC';
96
97 -- *************************************************
98 -- ************* For DEBUGGING ****************
99 /*************************************************
100 htp.p('ENTERED BUILD_QUERY ');
101 htp.p('l_wordlist '||l_wordlist||'<BR>');
102 htp.p('l_sql '||l_sql||'<BR>');
103 *************************************************/
104
105 -- Now prepare and run the sql query
106 l_cursor := DBMS_SQL.OPEN_CURSOR;
107 DBMS_SQL.PARSE(l_cursor, l_sql, DBMS_SQL.NATIVE);
108 DBMS_SQL.DEFINE_COLUMN(l_cursor,1, l_score1);
109 DBMS_SQL.DEFINE_COLUMN(l_cursor,2, l_score2);
110 DBMS_SQL.DEFINE_COLUMN(l_cursor,3, l_folder_id);
111 DBMS_SQL.DEFINE_COLUMN(l_cursor,4, l_folder_name,400);
112 DBMS_SQL.DEFINE_COLUMN(l_cursor,5, l_folder_description,32000);
113
114 l_dummy := DBMS_SQL.EXECUTE(l_cursor);
115
116 -- Collect the results - folder_id, folder_name and folder_description into
117 -- the plsql table to send it back to BIS_BUSINESS_VIEWS_CATALOG packkage
118 l_count := 1;
119 LOOP
120 l_dummy := DBMS_SQL.FETCH_ROWS(l_cursor);
121 IF (l_dummy = 0) THEN EXIT; END IF;
122 IF (l_count > C_MAX_HITS) THEN EXIT; END IF;
123
124 DBMS_SQL.COLUMN_VALUE(l_cursor, 1, l_score1);
125 DBMS_SQL.COLUMN_VALUE(l_cursor, 2, l_score2);
126 DBMS_SQL.COLUMN_VALUE(l_cursor, 3, l_folder_id);
127 DBMS_SQL.COLUMN_VALUE(l_cursor, 4, l_folder_name);
128 DBMS_SQL.COLUMN_VALUE(l_cursor, 5, l_folder_description);
129
130
131 -- Transfer the fetched values from the buffer into the plsql table
132 l_results_tbl(l_count).folder_id := l_folder_id;
133 l_results_tbl(l_count).folder_name := l_folder_name;
134 l_results_tbl(l_count).folder_description := l_folder_description;
135 l_results_tbl(l_count).folder_eul := p_eul;
136 l_count := l_count + 1;
137 END LOOP;
138
139 -- Close the cursor
140 DBMS_SQL.CLOSE_CURSOR(l_cursor);
141 COMMIT;
142
143 -- Send this table of results back
144 x_results_tbl := l_results_tbl;
145
146
147
148 EXCEPTION
149 WHEN NO_DATA_FOUND THEN
150 x_return_status := FND_API.G_RET_STS_ERROR;
151 WHEN OTHERS THEN
152 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
153 BIS_UTILITIES_PVT.Add_Error_Message
154 ( p_error_table => x_error_Tbl
155 , p_error_msg_id => SQLCODE
156 , p_error_description => SQLERRM
157 , x_error_table => x_error_Tbl
158 );
159 --htp.p('Error in BIS_GNRL_SEARCH_ENGINE_PVT.build_query'||SQLERRM);
160 RAISE;
161
162 END build_query;
163
164 -- *************************************************************
165 -- Get the intermedia domain index owner
166 -- *************************************************************
167 Function get_a_index_owner( p_index in varchar2 )
168 return eul_results
169 IS
170
171 TYPE Recdc_eul IS REF CURSOR;
172 dc_eul Recdc_eul;
173
174 dc_query varchar2(500);
175 eul varchar2(30);
176
177 pv_cntr number;
178 pv_eul_cnt number;
179
180 --l_owner varchar2(200);
181 l_temp eul_results;
182 l_owner eul_results;
183
184 BEGIN
185
186 dc_query := 'select distinct ' ||
187 ' b.profile_option_value || ''_'' || c.language ' ||
188 ' from ' ||
189 ' fnd_profile_options a ' ||
190 ' ,fnd_profile_option_values b' ||
191 ' ,fnd_profile_options_tl c ' ||
192 ' where ' ||
193 ' a.profile_option_name = ''ICX_DEFAULT_EUL'' and ' ||
194 ' a.profile_option_id = b.profile_option_id and ' ||
195 ' a.profile_option_name = c.profile_option_name ';
196
197
198 pv_cntr := 0;
199 open dc_eul for dc_query;
200 loop
201 fetch dc_eul into l_temp(pv_cntr + 1).eul_schema;
202 exit when dc_eul%NOTFOUND;
203 pv_cntr := pv_cntr + 1;
204 end loop;
205 close dc_eul;
206
207 dc_query := 'select distinct count(*) ' ||
208 ' from all_objects ' ||
209 ' where owner = :e ' ||
210 ' and object_name = ''' || get_eul_table_version || '_BAS''' ||
211 ' and object_type = ''TABLE''';
212
213 pv_cntr := 1;
214 for i in 1..l_temp.count loop
215 open dc_eul for dc_query using l_temp(i).eul_schema;
216 fetch dc_eul into pv_eul_cnt;
217 if pv_eul_cnt > 0 then
218 l_owner(pv_cntr).eul_schema := l_temp(i).eul_schema;
219 pv_cntr := pv_cntr + 1;
220 end if;
221 close dc_eul;
222 end loop;
223
224
225 return l_owner;
226
227 EXCEPTION
228 WHEN OTHERS THEN
229 rollback;
230 --htp.p('Error in BIS_GNRL_SEARCH_ENGINE_PVT.get_a_index_owner');
231 RAISE;
232 END get_a_index_owner;
233
234 -- ****************************************************
235 -- Function to return a string with attachments on
236 -- both sides
237 -- ****************************************************
238 function concat_string (p_str in varchar2)
239 return
240 varchar2 is
241 v_local_str varchar2(3200);
242
243 begin
244 v_local_str := ''''||p_str||'''';
245
246 return v_local_str;
247 end concat_string;
248
249 -- *****************************************************
250
251 -- *************************************************************
252 -- Get the Oracle Discover Version
253 -- *************************************************************
254 FUNCTION get_disco_release
255 RETURN VARCHAR2
256 IS
257 l_disco_release fnd_profile_option_values.profile_option_value%TYPE;
258 x_release_name VARCHAR2(50);
259 x_other_release_info VARCHAR2(50);
260 l_status BOOLEAN;
261 l_major_version VARCHAR2(50);
262 BEGIN
263 l_status := fnd_release.get_release(
264 release_name => x_release_name,
265 other_release_info => x_other_release_info);
266 IF (l_status = TRUE AND x_release_name IS NOT NULL) THEN
267 l_major_version := SUBSTR(x_release_name,1,INSTR(x_release_name,'.',1,1)-1);
268 IF (l_major_version = '12') THEN
269 RETURN '10';-- In R12 the default value is 10
270 END IF;
271 END IF;
272 --The following code will be executed only in 11i
273 l_disco_release := fnd_profile.value('ICX_DISCOVERER_RELEASE');
274 IF (l_disco_release IS NULL) THEN
275 RETURN '4';
276 END IF;
277
278 RETURN l_disco_release;
279 EXCEPTION
280 WHEN OTHERS THEN
281 RETURN '4';
282 END get_disco_release;
283 -- ***************************************************************
284 -- Get the Oracle EUL TABLE VERSION
285 -- *************************************************************
286 FUNCTION get_eul_table_version
287 RETURN varchar2
288 IS
289 l_eul_file_version varchar2(10);
290 BEGIN
291 IF(TO_NUMBER(get_disco_release) <= 4) THEN
292 l_eul_file_version := 'EUL4';
293 ELSE
294 l_eul_file_version := 'EUL5';
295 END IF;
296 return l_eul_file_version;
297 EXCEPTION
298 WHEN OTHERS THEN
299 RETURN 'EUL4';
300 END get_eul_table_version;
301 -- ***************************************************************
302
303 END BIS_GNRL_SEARCH_ENGINE_PVT_OA;
[Home] [Help]