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