1 package body BIS_GNRL_SEARCH_ENGINE_PVT AS
2 /* $Header: BISVGSEB.pls 115.3 99/08/05 10:43:15 porting ship $ */
3 -- **********************************************************
4 -- Procedure to build the InterMedia query
5 -- *********************************************************
6 Procedure build_query (
7 p_api_version in pls_integer
8 ,p_eul in varchar2
9 ,p_keywords_tbl in BIS_GNRL_SEARCH_ENGINE_PVT.keywords_tbl_typ
10 ,x_results_tbl out BIS_GNRL_SEARCH_ENGINE_PVT.results_tbl_typ
11 ,x_return_status out varchar2
12 ,x_error_tbl out BIS_UTILITIES_PUB.Error_Tbl_Type
13 )
14 IS
15 v_eul varchar2(256);
16 l_sql varchar2(32000);
17 l_wordlist varchar2(32000);
18 l_dummy pls_integer;
19 l_count pls_integer;
20 l_cursor pls_integer;
21 l_score1 pls_integer;
22 l_score2 pls_integer;
23 l_score3 pls_integer;
24 l_folder_id pls_integer;
25 l_folder_name varchar2(400);
26 l_folder_description varchar2(32000);
27 l_results_tbl BIS_GNRL_SEARCH_ENGINE_PVT.results_tbl_typ;
28
29 BEGIN
30 x_return_status := FND_API.G_RET_STS_SUCCESS;
31
32 -- To make sure the Intermedia query does not break becuase of lack of schema name
33 IF (p_eul IS NOT NULL) THEN
34 v_eul := p_eul ||'.';
35 ELSE
36 v_eul := p_eul;
37 END IF;
38
39 -- Construct the search words list from the plsql
40 -- table containing the words and add the appropriate InterMedia OPERATORS
41 FOR i in 1 .. p_keywords_tbl.COUNT LOOP
42 IF i = p_keywords_tbl.COUNT THEN
43 l_wordlist := l_wordlist || c_stem_optr ||'{'|| p_keywords_tbl(i) ||'}';
44 EXIT;
45 END IF;
46 l_wordlist := l_wordlist || c_stem_optr ||'{'||p_keywords_tbl(i)||'}'|| c_accum_optr;
47 END LOOP;
48
49 -- Add the appropriate number of ' to the ends of the word list to be embedded in the
50 -- Intermedia query
51 l_wordlist := concat_string(l_wordlist);
52
53 -- ******************************************************************************
54 -- CONSTRUCT THE INTERMEDIA SQL QUERY
55 -- The '$' STEM operator creates a linguistic root of the word supplied
56 -- and returns all row hits that contain words which could be probably
57 -- generated by this root word.
58 -- The ',' ACCUM operator accumulates the list of words supplied and returns
59 -- the highest score for hits containing all of the words and then corresponding
60 -- lower scores for subsequent row hits containing one or more words.
61 -- The '{ }' ESCAPE SEQUENCE operators escape a group of reserved characters if exists.
62 -- Example 1 : For word1 and word2 supplied, the query 'Contains(colname, 'word1,word2',2)
63 -- will give the highest score for row hits containing both the words, and then
64 -- a lower score for row hits for occurences of word1 OR word2.
65 -- Example 2 : If the word contains 'function-layout', it will be read as {function-layout}
66 -- as one word inclusive of the '-', where the '-' is escaped.
67 -- QUERY FORMULA
68 -- The following query will return rows that got the best hits according to
69 -- the following priority order...
70 -- 1) All of the search words exist in eul_objs.obj_name column (Folder name)
71 -- 2) One or more search words exist in eul_objs.obj_name column (Folder name)
72 -- 3) All of the search words exist in eul_objs.obj_description col (Fldr desc.)
73 -- 4) One or more search words exist in eul_objs.obj_description col (Fldr desc)
74 -- 5) Now the detail table (eul_expressions.exp_name) is searched. This contains
75 -- the cols that make up the above Folder/business view. These cols are searched
76 -- for every folder_id (eul_objs.obj_id) as one singe 'document set', so that
77 -- only one row is returned for hits on any of the folder columns. This part
78 -- is taken care by the appropriate index 'BIS_BV_ITEM_NAMES_||'LANG''
79 -- ****************************************************************************
80
81 l_sql := ' select score(10), score(20), score(30), ' ||
82 ' obj_id, obj_name, obj_description ' ||
83 ' from ' || v_eul || 'eul_objs ' ||
84 ' where contains (obj_name, ' || l_wordlist || ', 10) > 0 ' ||
85 ' or contains (obj_description, ' || l_wordlist || ', 20) > 0 ' ||
86 ' or contains (obj_type, ' || l_wordlist || ', 30) > 0 ' ||
87 ' order by score(10) DESC ,score(20) DESC ,score(30) DESC';
88
89 -- *************************************************
90 -- ************* For DEBUGGING ****************
91 -- dbms_output.put_line('l_wordlist '||l_wordlist);
92 -- dbms_output.put_line('l_sql '||l_sql);
93 -- dbms_output.put_line('l_sqlcount '||l_sqlcount);
94 /*************************************************
95 htp.p('ENTERED BUILD_QUERY ');
96 htp.p('l_wordlist '||l_wordlist||'<BR>');
97 htp.p('l_sql '||l_sql||'<BR>');
98 *************************************************/
99
100 -- Now prepare and run the sql query
101 l_cursor := DBMS_SQL.OPEN_CURSOR;
102 DBMS_SQL.PARSE(l_cursor, l_sql, DBMS_SQL.NATIVE);
103 DBMS_SQL.DEFINE_COLUMN(l_cursor,1, l_score1);
104 DBMS_SQL.DEFINE_COLUMN(l_cursor,2, l_score2);
105 DBMS_SQL.DEFINE_COLUMN(l_cursor,3, l_score3);
106 DBMS_SQL.DEFINE_COLUMN(l_cursor,4, l_folder_id);
107 DBMS_SQL.DEFINE_COLUMN(l_cursor,5, l_folder_name,400);
108 DBMS_SQL.DEFINE_COLUMN(l_cursor,6, l_folder_description,32000);
109 l_dummy := DBMS_SQL.EXECUTE(l_cursor);
110
111 -- Collect the results - folder_id, folder_name and folder_description into
112 -- the plsql table to send it back to BIS_BUSINESS_VIEWS_CATALOG packkage
113 l_count := 1;
114 LOOP
115 l_dummy := DBMS_SQL.FETCH_ROWS(l_cursor);
116 IF (l_dummy = 0) THEN EXIT; END IF;
117 IF (l_count > C_MAX_HITS) THEN EXIT; END IF;
118
119 DBMS_SQL.COLUMN_VALUE(l_cursor, 1, l_score1);
120 DBMS_SQL.COLUMN_VALUE(l_cursor, 2, l_score2);
121 DBMS_SQL.COLUMN_VALUE(l_cursor, 3, l_score3);
122 DBMS_SQL.COLUMN_VALUE(l_cursor, 4, l_folder_id);
123 DBMS_SQL.COLUMN_VALUE(l_cursor, 5, l_folder_name);
124 DBMS_SQL.COLUMN_VALUE(l_cursor, 6, l_folder_description);
125
126 -- Transfer the fetched values from the buffer into the plsql table
127 l_results_tbl(l_count).folder_id := l_folder_id;
128 l_results_tbl(l_count).folder_name := l_folder_name;
129 l_results_tbl(l_count).folder_description := l_folder_description;
130 l_count := l_count + 1;
131 END LOOP;
132
133 -- Close the cursor
134 DBMS_SQL.CLOSE_CURSOR(l_cursor);
135 COMMIT;
136
137 -- Send this table of results back
138 x_results_tbl := l_results_tbl;
139
140
141 EXCEPTION
142 WHEN NO_DATA_FOUND THEN
143 x_return_status := FND_API.G_RET_STS_ERROR;
144 WHEN OTHERS THEN
145 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
146 BIS_UTILITIES_PVT.Add_Error_Message
147 ( p_error_table => x_error_Tbl
148 , p_error_msg_id => SQLCODE
149 , p_error_description => SQLERRM
150 , x_error_table => x_error_Tbl
151 );
152 --htp.p('Error in BIS_GNRL_SEARCH_ENGINE_PVT.build_query'||SQLERRM);
153 RAISE;
154
155 END build_query;
156
157 -- *************************************************************
158 -- Get the intermedia domain index owner
159 -- *************************************************************
160 Function get_a_index_owner( p_index in varchar2 )
161 return varchar2
162 IS
163 CURSOR cur_idx_owner IS
164 SELECT owner
165 FROM ALL_INDEXES
166 WHERE index_name like p_index;
167
168 l_owner varchar2(200);
169 BEGIN
170
171 OPEN cur_idx_owner;
172 BEGIN
173 FETCH cur_idx_owner INTO l_owner;
174 EXCEPTION
175 WHEN OTHERS THEN CLOSE cur_idx_owner;
176 RAISE;
177 END;
178 CLOSE cur_idx_owner;
179 return l_owner;
180
181 EXCEPTION
182 WHEN OTHERS THEN
183 --htp.p('Error in BIS_GNRL_SEARCH_ENGINE_PVT.get_a_index_owner');
184 RAISE;
185 END get_a_index_owner;
186
187 -- ****************************************************
188 -- Function to return a string with attachments on
189 -- both sides
190 -- ****************************************************
191 function concat_string (p_str in varchar2)
192 return
193 varchar2 is
194 v_local_str varchar2(3200);
195
196 begin
197 v_local_str := ''''||p_str||'''';
198
199 return v_local_str;
200 end concat_string;
201
202 -- *****************************************************
203
204 -- ***************************************************************
205 -- ***************************************************************
206 END BIS_GNRL_SEARCH_ENGINE_PVT;
[Home] [Help]