1 PACKAGE BODY amv_search_grp AS
2 /* $Header: amvgserb.pls 120.1 2005/06/21 17:48:49 appldev ship $ */
3 --
4 -- NAME
5 -- AMV_SEARCH_GRP
6 -- PURPOSE
7 --
8 -- HISTORY
9 -- 12/07/1999 SLKRISHN CREATED
10 --
11 --
12 --
13 G_PKG_NAME CONSTANT VARCHAR2(30) := 'AMV_SEARCH_GRP';
14
15 G_ITEM CONSTANT VARCHAR2(30) := AMV_UTILITY_PVT.G_ITEM;
16 G_AUTHOR CONSTANT VARCHAR2(30) := 'AUTHOR';
17 G_KEYWORD CONSTANT VARCHAR2(30) := 'KEYWORD';
18 G_TITLE_DESC CONSTANT VARCHAR2(30) := 'TITLE_DESC';
19 --
20 -- This package contains the following procedures
21 --------------------------------------------------------------------------------
22 --------------------------------------------------------------------------------
23 -- Start of comments
24 -- API name : find_repositories
25 -- Type : Group or Public
26 -- Pre-reqs : Total number of repository names retrieved at a time will
27 -- not need to exceed amv_utility_pub.g_max_array_size. By
28 -- not needing to exceed this limit, the parameters to
29 -- control a "sliding window" of retrieved values is not
30 -- needed, thus simplifying this API's signature.
31 -- Function : Retrieves all repository names participating with
32 -- MES Search that match the input parameters specified.
33 -- Typically, only the status parameter will be set to
34 -- retrieve only active Repositories.
35 --
36 -- Marketing Encyclopedia (MES) will employ this procedure
37 -- within its Search API and screens to retrieve
38 -- repositories participating with MES search.
39 --
40 --
41 -- Parameters (Standard parameters not mentioned):
42 -- IN : p_repository_id IN NUMBER Optional
43 -- Repository ID of the Repository to retrieve
44 -- information for. Corresponds to the column
45 -- amv_d_entities_b.entity_id
46 -- where amv_d_entities_b.usage_indicator = 'ASRN'
47 --
48 -- : p_repository_code IN VARCHAR2(255) Optional
49 -- Repository Code of the Repository to retrieve
50 -- information for. Corresponds to the column
51 -- amv_d_entities_b.table_name
52 -- where amv_d_entities_b.usage_indicator = 'ASRN'
53 --
54 -- : p_repository_name IN VARCHAR2(80) Optional
55 -- Description of the Repository that should appear
56 -- on the Advanced Repository Area Search page.
57 -- Corresponds to the column
58 -- amv_d_entities_tl.entity_name.
59 --
60 -- : p_status IN VARCHAR2 Optional
61 -- Status condition to be queried.
62 -- (A= active, I=inactive).
63 --
64 -- : p_object_version_number IN NUMBER Optional
65 -- Used as a means of detecting updates to a row.
66 --
67 -- OUT NOCOPY NOCOPY : x_searchrep_array OUT NOCOPY NOCOPY ARRAY_TYPE
68 -- Varying Array of Object amv_searchrep_obj_type that
69 -- holds the resulting search matches.
70 --
71 -- repository_id OUT NOCOPY NUMBER
72 -- Repository ID that met the search criteria
73 -- provided.
74 --
75 -- repository_code OUT NOCOPY VARCHAR2(255)
76 -- Repository code that met the search criteria
77 -- provided.
78 --
79 -- repository_name OUT NOCOPY VARCHAR2(80)
80 -- Name of the Repository that met the
81 -- search criteria provided. Value will be
82 -- what is displayed on the Advanced Repository Area
83 -- Search page.
84 --
85 -- status OUT NOCOPY VARCHAR2(30)
86 -- Status of the record.
87 --
88 -- object_version_number OUT NOCOPY NUMBER
89 -- Version number stamp of the record.
90 --
91 -- Version : Current version 1.0
92 -- {add comments here}
93 -- Previous version 1.0
94 -- Initial version 1.0
95 -- End of comments
96 --
97 PROCEDURE find_repositories
98 (p_api_version IN NUMBER,
99 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
100 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
101 x_return_status OUT NOCOPY VARCHAR2,
102 x_msg_count OUT NOCOPY NUMBER,
103 x_msg_data OUT NOCOPY VARCHAR2,
104 p_check_login_user IN VARCHAR2 := FND_API.G_TRUE,
105 p_object_version_number IN NUMBER := FND_API.G_MISS_NUM,
106 p_repository_id IN NUMBER := FND_API.G_MISS_NUM,
107 p_repository_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
108 p_repository_name IN VARCHAR2 := FND_API.G_MISS_CHAR,
109 p_status IN VARCHAR2 := FND_API.G_MISS_CHAR,
110 x_searchrep_array OUT NOCOPY AMV_SEARCH_PVT.amv_searchrep_varray_type)
111
112 IS
113 l_api_name varchar2(30) := 'find_repositories';
114 BEGIN
115
116 amv_search_pvt.find_repositories
117 (p_api_version => p_api_version,
118 p_init_msg_list => p_init_msg_list,
119 p_validation_level => p_validation_level,
120 x_return_status => x_return_status,
121 x_msg_count => x_msg_count,
122 x_msg_data => x_msg_data,
123 p_check_login_user => p_check_login_user,
124 p_object_version_number => p_object_version_number,
125 p_repository_id => p_repository_id,
126 p_repository_code => p_repository_code,
127 p_repository_name => p_repository_name,
128 p_status => p_status,
129 x_searchrep_array => x_searchrep_array);
130
131 EXCEPTION
132 WHEN OTHERS THEN
133 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
134 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
135 THEN
136 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
137 END IF;
138 -- Standard call to get message count and if count=1, get the message
139 FND_MSG_PUB.Count_And_Get (
140 p_encoded => FND_API.G_FALSE,
141 p_count => x_msg_count,
142 p_data => x_msg_data
143 );
144 END find_repositories;
145 --
146 --------------------------------------------------------------------------------
147 -- Start of comments
148 -- API name : find_repository_areas
149 -- Type : Group or Public
150 -- Pre-reqs : Total number of repository areas retrieved at a time will
151 -- not need to exceed amv_utility_pub.g_max_array_size. By
152 -- not needing to exceed this limit, the parameters to
153 -- control a "sliding window" of retrieved values is not
154 -- needed, thus simplifying this API's signature.
155 -- Function : Retrieves all repository areas for a given repository
156 -- that is participating with MES Search which matches the
157 -- input parameters specified.
158 -- Typically, the repository code will be provided to
159 -- restrict the return to include only areas for that
160 -- repository; The status parameter will usually be set to
161 -- retrieve only active Repository Areas.
162 --
163 -- Marketing Encyclopedia (MES) will employ this procedure
164 -- within its Search API and screens to retrieve Repository
165 -- Areas valid for an MES search with the specified Repository.
166 --
167 -- Parameters (Standard parameters not mentioned):
168 -- IN : p_repository_id IN NUMBER Optional
169 -- Repository identifier of the Repository Code to
170 -- retrieve information for. Corresponds to the column
171 -- amv_d_entities_b.entity_id
172 -- where amv_d_entities_b.usage_indicator = 'ASRN'
173 --
174 -- : p_repository_code IN VARCHAR2(255) Optional
175 -- Repository Code of the Repository to retrieve
176 -- information for. Corresponds to the column
177 -- amv_d_entities_b.table_name
178 -- where amv_d_entities_b.usage_indicator = 'ASRN'
179 --
180 -- : p_area_id IN NUMBER Optional
181 -- Repository Area identifier of the Repository Area to
182 -- retrieve information for. Corresponds to the column
183 -- amv_d_ent_attributes_b.attribute_id
184 -- where amv_d_ent_attributes_b.usage_indicator = 'ASRA'
185 --
186 -- : p_area_code IN VARCHAR2(255) Optional
187 -- Area Repository Code of the Repository to retrieve
188 -- information for. Corresponds to the column
189 -- amv_d_ent_attributes_b.column_name
190 -- where amv_d_ent_attributes_b.usage_indicator = 'ASRA'
191 --
192 -- : p_area_name IN VARCHAR2(80) Optional
193 -- Description of the Repository that should appear
194 -- on the Advanced Repository Area Search page.
195 -- Corresponds to the column
196 -- amv_d_ent_attributes_tl.attribute_name.
197 --
198 -- : p_status IN VARCHAR2 Optional
199 -- Status condition to be queried.
200 -- (A= active, I=inactive).
201 --
202 -- : p_object_version_number IN NUMBER Optional
203 -- Used as a means of detecting updates to a row.
204 --
205 -- OUT NOCOPY : x_searcharea_array OUT NOCOPY ARRAY_TYPE
206 -- Varying Array of Object amv_searchrep_obj_type that
207 -- holds the resulting search matches.
208 --
209 -- repository_id OUT NOCOPY NUMBER
210 -- Repository ID that met the search criteria
211 -- provided.
212 --
213 -- repository_code OUT NOCOPY VARCHAR2(255)
214 -- Repository code that met the search criteria
215 -- provided.
216 --
217 -- area_id OUT NOCOPY NUMBER
218 -- Area ID that met the search criteria
219 -- provided.
220 --
221 -- area_code OUT NOCOPY VARCHAR2(80)
222 -- Area code that met the search criteria
223 -- provided.
224 --
225 -- area_name OUT NOCOPY VARCHAR2(80)
226 -- Name of the Repository Area that met the
227 -- search criteria provided. Value will be
228 -- what is displayed on the Advanced Repository Area
229 -- Search page.
230 --
231 -- status OUT NOCOPY VARCHAR2(30)
232 -- Status of the record.
233 --
234 -- object_version_number OUT NOCOPY NUMBER
235 -- Version number stamp of the record.
236 --
237 -- Version : Current version 1.0
238 -- {add comments here}
239 -- Previous version 1.0
240 -- Initial version 1.0
241 -- End of comments
242 --
243 PROCEDURE find_repository_areas
244 (p_api_version IN NUMBER,
245 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
246 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
247 x_return_status OUT NOCOPY VARCHAR2,
248 x_msg_count OUT NOCOPY NUMBER,
249 x_msg_data OUT NOCOPY VARCHAR2,
250 p_check_login_user IN VARCHAR2 := FND_API.G_TRUE,
251 p_searcharea_obj IN AMV_SEARCH_PVT.amv_searchara_obj_type,
252 x_searcharea_array OUT NOCOPY AMV_SEARCH_PVT.amv_searchara_varray_type)
253
254 IS
255 l_api_name varchar2(30) := 'find_repository_areas';
256 BEGIN
257
258 amv_search_pvt.find_repository_areas
259 (p_api_version => p_api_version,
260 p_init_msg_list => p_init_msg_list,
261 p_validation_level => p_validation_level,
262 x_return_status => x_return_status,
263 x_msg_count => x_msg_count,
264 x_msg_data => x_msg_data,
265 p_check_login_user => p_check_login_user,
266 p_searcharea_obj => p_searcharea_obj,
267 x_searcharea_array => x_searcharea_array);
268
269 EXCEPTION
270 WHEN OTHERS THEN
271 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
272 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
273 THEN
274 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
275 END IF;
276 -- Standard call to get message count and if count=1, get the message
277 FND_MSG_PUB.Count_And_Get (
278 p_encoded => FND_API.G_FALSE,
279 p_count => x_msg_count,
280 p_data => x_msg_data
281 );
282 END find_repository_areas;
283 --
284 --------------------------------------------------------------------------------
285 -- Start of comments
286 -- API name : Content_Search
287 -- Type : Group or Public
288 -- Pre-reqs : None
289 -- Function : Accept a search string of a standardized syntax for the
290 -- searching of one or more repositories for which
291 -- this repository API supports. The API name will be
292 -- registered within the tables amv_d_entities_%,
293 -- amv_d_ent_attributes_tl, and amv_d_ent_attributes_b
294 -- with the later tables column, FUNCTION_CALL set to
295 -- {user-defined name of [package.]procedure} (i.e. the
296 -- name of this API).
297 -- As the value stored within the column FUNCTION_CALL will be
298 -- concatenated along with a pre-determined procedure
299 -- specification for participation in a dynamic PL/SQL call,
300 -- it is imperative that this value conforms to a valid
301 -- Oracle PL/SQL [package.]procedure name.
302 --
303 -- Marketing Encyclopedia (MES) will employ this procedure
304 -- within its Search API and screens to retrieve and filter
305 -- another repository's data that meets the specified
306 -- search criteria passed in.
307 --
308 -- Parameters (Standard parameters not mentioned):
309 -- IN : p_imt_search_string IN VARCHAR2(400) Required
310 -- Search string defining what to search in interMedia
311 -- Text syntax. The intent is for this API to accept
312 -- the string as-is, and drop the string into a
313 -- Dynamic SQL statement containing the iMT CONTAINS()
314 -- clause for immediate execution.
315 --
316 -- Note, this string will NOT include the iMT keyword
317 -- CONTAINS along with it's parentheses, just a valid
318 -- string that can be dropped as-is into the CONTAINS
319 -- clause.
320 --
321 -- : p_search_param_array IN amv_searchpar_array_type Required
322 -- Array of object amv_searchpar_obj_type listing values
323 -- to search against database columns that are not
324 -- interMedia Text enabled.
325 --
326 -- The attributes of the object follow:
327 --
328 -- : operator IN VARCHAR2(30) Required
329 --
333 -- : string_value IN VARCHAR2(400) Required
330 -- Oracle operators consisting values in
331 -- {=,!=,IN,NOT IN,LIKE,NOT LIKE}.
332 --
334 --
335 -- Value portion of the search in string form.
336 --
337 -- The format of the two columns of this object type
338 -- is such that the API will be able to concatenate
339 -- these values with appropriate white space and the
340 -- search source column name; This would form a
341 -- syntactically valid SQL predicate for construction
342 -- of a Dynamic SQL Statement.
343 --
344 -- Example:
345 --
346 -- col_name||two single quotes||operator||two single quotes||string_value
347 --
348 -- The string_value will conform to the proper SQL
349 -- syntax for its corresponding operator. (e.g. the
350 -- string_value will be enclosed in parentheses for
351 -- the IN operator). As there could be multiple
352 -- string_values, this API must be able to build a
353 -- Dynamic SQL statement using all cells of this array.
354 --
355 -- : p_area_array IN amv_area_array_type Optional
356 -- Array structure that lists a subset of all areas
357 -- of the repository for which this API is based. If the
358 -- array is NULL (by default), then all areas are to be
359 -- searched. Areas listed within this array must, for
360 -- validation purposes, be registered under the MES tables
361 -- amv_d_entities_%, amv_d_ent_attributes_% and
362 -- amv_d_ attrib_operators. Valid areas will be
363 -- identified in the column
364 -- amv_d_ent_attributes_b.column_name.
365 --
366 -- The main AMV Search API will only recognize areas
367 -- defined within this table. The API will also refer to
368 -- the status column of this table to ignore areas
369 -- where this column's value is set to "disabled".
370 --
371 -- : p_user_id IN NUMBER Required
372 -- Identifier from FND that declares the end-user. This
373 -- API may required the ID to filter privileged items.
374 --
375 -- : p_request_array IN amv_request_array_type Required
376 -- Object structure that specifies and controls a sliding
377 -- window to the retrieved LOV results set (i.e. restricts
378 -- the subset of rows returned, and controls its starting
379 -- and ending record position of the complete set of rows
380 -- that could potentially be retrieved). See package
381 -- amv_utility_pub for further specifications to the
382 -- object's structure. The attributes of the object and
383 -- their description follow:
384 --
385 -- records_requested IN NUMBER
386 -- Specifies the maximum number of records to return
387 -- in the varray results subset (Defaults to
388 -- (amv_utility_pub.g_amv_max_varray_size).
389 --
390 -- start_record_position IN NUMBER
391 -- Specifies a subscript into the varray results
392 -- set for the first record to be returned in the
393 -- retrieval subset. Usually used in conjunction
394 -- with p_request_obj.next_record_position
395 -- (Default 1 ).
396 --
397 -- return_total_count_flag IN VARCHAR2
398 -- Flag consisting of the values {fnd_api.g_true,
399 -- fnd_api.g_false} to specify whether
400 -- p_request_obj.total_record_count is
401 -- derived, albeit at a possible cost to resources
402 -- (Default fnd_api.g_false).
403 --
404 -- OUT NOCOPY : x_return_obj OUT NOCOPY OBJ_TYPE
405 -- Object structure that reports information abOUT NOCOPY the
406 -- retrieved results set defined by p_request_obj.
407 -- See package amv_utility_pub for further
408 -- specifications to the object's structure.
409 -- Object structure of:
410 --
411 -- returned_record_count OUT NOCOPY NUMBER
412 -- Indicates the total number of records returned
413 -- for the retrieved subset. This value will not
414 -- exceed p_request_obj.records_requested.
415 --
416 -- next_record_position OUT NOCOPY NUMBER
417 -- Indicates the subscript to the varray that is the
418 -- starting point to the next subset of records in
419 -- the set (base 1; that is, the first record of the
420 -- set is one, NOT zero). Will return 0 if there are
421 -- no more rows.
422 --
423 -- total_record_count OUT NOCOPY NUMBER
427 -- to fnd_api.g_true; Otherwise undefined.
424 -- Indicates the total record count in the complete
425 -- varray retrieval set only if
426 -- p_request_obj.return_total_count is set
428 --
429 -- : x_searchres_array OUT NOCOPY ARRAY_TYPE
430 -- Varying Array of Object amv_searchres_obj_type that
431 -- holds the resulting search matches.
432 --
433 -- title IN VARCHAR2(80)
434 -- Title of the item that met the search criteria
435 -- provided.
436 --
437 -- url_string IN VARCHAR2(2000)
438 -- URL of the item that met the search. If this item
439 -- is a file, then it will conform to MIME types.
440 -- If the item has it's body of a table column, then
441 -- the URL will point to an appropriate viewer with
442 -- the table column provided as a parameter into the
443 -- viewer call.
444 --
445 -- description IN VARCHAR2(200)
446 -- Abbreviated description of the item that met the
447 -- search criteria provided.
448 --
449 -- score IN NUMBER
450 -- Weighted score of the item that met the search.
451 -- The determination of the score is derived by
452 -- interMedia Text ranged 0 to 100 with 100 being
453 -- the best score. Exact matches against table
454 -- columns which are not interMedia Text enabled will
455 -- automatically score 100.
456 --
457 -- area_id IN VARCHAR2(30)
458 -- The area identifier of the area code.
459 -- Corresponds to the column
460 -- amv_d_ent_attributes_b.column_name where
461 -- amv_d_ent_attributes_b.usage_indicator = 'ASRA'
462 --
463 -- area_code IN VARCHAR2(30)
464 -- The area code of the repository for which this API
465 -- supports. Valid values will be found within the
466 -- column amv_d_ent_attributes_b.column_name where
467 -- amv_d_ent_attributes_b.usage_indicator = 'ASRA'
468 --
469 -- user1 - user3 IN VARCHAR2(255)
470 -- Unused columns that exist for customized needs.
471 --
472 --
473 -- Version : Current version 1.0
474 -- {add comments here}
475 -- Previous version 1.0
476 -- Initial version 1.0
477 -- End of comments
478 --
479 PROCEDURE Content_Search
480 (p_api_version IN NUMBER,
481 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
482 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
483 x_return_status OUT NOCOPY VARCHAR2,
484 x_msg_count OUT NOCOPY NUMBER,
485 x_msg_data OUT NOCOPY VARCHAR2,
486 p_check_login_user IN VARCHAR2 := FND_API.G_TRUE,
487 p_application_id IN NUMBER,
488 p_area_array IN AMV_SEARCH_PVT.amv_char_varray_type,
489 p_content_array IN AMV_SEARCH_PVT.amv_char_varray_type,
490 p_param_array IN AMV_SEARCH_PVT.amv_searchpar_varray_type,
491 p_imt_string IN VARCHAR2 := FND_API.G_MISS_CHAR,
492 p_days IN NUMBER := FND_API.G_MISS_NUM,
493 p_user_id IN NUMBER := FND_API.G_MISS_NUM,
494 p_category_id IN AMV_SEARCH_PVT.amv_number_varray_type,
495 p_include_subcats IN VARCHAR2 := FND_API.G_FALSE,
496 p_external_contents IN VARCHAR2 := FND_API.G_FALSE,
497 p_request_obj IN AMV_SEARCH_PVT.amv_request_obj_type,
498 x_return_obj OUT NOCOPY AMV_SEARCH_PVT.amv_return_obj_type,
499 x_searchres_array OUT NOCOPY AMV_SEARCH_PVT.amv_searchres_varray_type)
500
501 IS
502 l_api_name varchar2(30) := 'content_search';
503 BEGIN
504 amv_search_pvt.Content_Search
505 (p_api_version => p_api_version,
506 p_init_msg_list => p_init_msg_list,
507 p_validation_level => p_validation_level,
508 x_return_status => x_return_status,
509 x_msg_count => x_msg_count,
510 x_msg_data => x_msg_data,
511 p_check_login_user => p_check_login_user,
512 p_application_id => p_application_id,
513 p_area_array => p_area_array,
514 p_content_array => p_content_array,
515 p_param_array => p_param_array,
516 p_imt_string => p_imt_string,
517 p_days => p_days,
518 p_user_id => p_user_id,
519 p_category_id => p_category_id,
520 p_include_subcats => p_include_subcats,
521 p_external_contents => p_external_contents,
522 p_request_obj => p_request_obj,
523 x_return_obj => x_return_obj,
524 x_searchres_array => x_searchres_array);
525
526 EXCEPTION
527 WHEN OTHERS THEN
528 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
529 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
530 THEN
531 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
532 END IF;
533 -- Standard call to get message count and if count=1, get the message
534 FND_MSG_PUB.Count_And_Get (
535 p_encoded => FND_API.G_FALSE,
536 p_count => x_msg_count,
537 p_data => x_msg_data
538 );
539 END content_search;
540 --------------------------------------------------------------------------------
541 END amv_search_grp;
[Home] [Help]