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