1 PACKAGE ICX_POR_SCHEMA_UPLOAD AUTHID CURRENT_USER AS
2 /* $Header: ICXSULDS.pls 115.11 2003/09/08 10:58:34 pcreddy ship $*/
3
4 -- Number of seeded descriptors
5 NUM_SEEDED_DESCRIPTORS CONSTANT Number := 300;
6
7 -- Category type
8 GENUS_TYPE CONSTANT Number := 2;
9 NAVIGATION_TYPE CONSTANT Number := 1;
10
11 -- Schema Attribute Ids
12 ROOT_ATTRIB_ID CONSTANT Number := 0;
13 LOCAL_ATTRIB_ID CONSTANT Number := 1;
14
15 -- Descriptor type
16 TEXT_TYPE CONSTANT VARCHAR2(30) := '0';
17 NUMERIC_TYPE CONSTANT VARCHAR2(30) := '1';
18 TRANSLATABLE_TEXT_TYPE CONSTANT VARCHAR2(30) := '2';
19 DATE_TYPE CONSTANT VARCHAR2(30) := '3';
20 URL_TYPE CONSTANT VARCHAR2(30) := '4';
21 INTEGER_TYPE CONSTANT VARCHAR2(30) := '5';
22
23 -- YES and NO
24 YES CONSTANT VARCHAR2(1) := '1';
25 NO CONSTANT VARCHAR2(1) := '0';
26
27
28
29 -------------------------------------------------------------------------
30 -- ADD ACTION --
31 -------------------------------------------------------------------------
32 /**
33 ** Proc : add_child_category
34 ** Desc : Add a category as a child of another category.
35 ** If this child category is already a child of other
36 ** category, this relationship will be remained.
37 ** Also this parent category should be navigation type.
38 **/
39 /* Changes for userId, loginId Added two parameters p_user_id, p_login_id */
40 PROCEDURE add_child_category (p_parent_id IN NUMBER,
41 p_child_id IN NUMBER,
42 p_user_id IN NUMBER,
43 p_login_id IN NUMBER);
44
45 /**
46 ** Proc : create_category
47 ** Desc : Create a new category with the specified name + key.
48 ** If parent is specified, a new link will be created.
49 ** This method assumes all parameters are validated, and
50 ** it only creates one row for specified language, won't
51 ** create rows for each installed language.
52 **/
53 /* Changes for userId, loginId Added two parameters p_user_id, p_login_id */
54 PROCEDURE create_category (p_category_id OUT NOCOPY NUMBER,
55 p_key IN VARCHAR2,
56 p_name IN VARCHAR2,
57 p_description IN VARCHAR2,
58 p_type IN NUMBER,
59 p_language IN VARCHAR2,
60 p_parent_id IN NUMBER DEFAULT -1,
61 p_request_id IN NUMBER DEFAULT -1,
62 p_user_id IN NUMBER,
63 p_login_id IN NUMBER);
64
65 /**
66 ** Proc : create_category_table
67 ** Desc : Create dynamic table and view for genus category.
68 PROCEDURE create_category_table (p_category_id IN NUMBER);
69 **/
70
71 /**
72 ** Proc : create_descriptor
73 ** Desc : Create a new local descriptor within a category.
74 ** A dynamic category table will be created if it doesn't
75 ** exist, and a new column is added to this table.
76 ** This method assumes everything is validated before
77 ** calling. And it only creates one row for specified
78 ** language, won't create rows for each installed language.
79 **/
80 /* Changes for userId, loginId Added two parameters p_user_id, p_login_id */
81 PROCEDURE create_descriptor (p_descriptor_id OUT NOCOPY NUMBER,
82 p_key IN VARCHAR2,
83 p_name IN VARCHAR2 DEFAULT NULL,
84 p_description IN VARCHAR2 DEFAULT NULL,
85 p_type IN VARCHAR2 DEFAULT
86 TEXT_TYPE,
87 p_sequence IN NUMBER,
88 p_search_results_visible IN VARCHAR2 DEFAULT NO,
89 p_item_detail_visible IN VARCHAR2 DEFAULT NO,
90 p_searchable IN VARCHAR2 DEFAULT NO,
91 p_required IN VARCHAR2 DEFAULT NO,
92 p_refinable IN VARCHAR2 DEFAULT NO,
93 p_multivalue IN VARCHAR2 DEFAULT NO,
94 p_default_value IN VARCHAR2 DEFAULT NULL,
95 p_language IN VARCHAR2,
96 p_category_id IN NUMBER,
97 p_request_id IN NUMBER DEFAULT -1,
98 p_section_tag OUT NOCOPY NUMBER,
99 p_stored_in_table OUT NOCOPY VARCHAR2,
100 p_stored_in_column OUT NOCOPY VARCHAR2,
101 p_user_id IN NUMBER,
102 p_login_id IN NUMBER);
103
104
105 /**
106 ** Proc : create_descriptor_metadata
107 ** Desc : Insert a new local descriptor into ICX_POR_DESCRIPTORS_TL.
108 ** This method simply pulls out the metadata section of a descriptor
109 ** that gets inserted into ICX_DESCRIPTORS_TL. This is done to
110 ** separate the insertion of data, from the creation of a dynamic
111 ** table. And is called directly in online category creation.
112 ** This method assumes everything is validated before
113 ** calling. And it will create rows for each installed language.
114 **/
115 /* Changes for userId, loginId Added two parameters p_user_id, p_login_id */
116 PROCEDURE create_descriptor_metadata (p_descriptor_id IN NUMBER,
117 p_key IN VARCHAR2,
118 p_name IN VARCHAR2,
119 p_description IN VARCHAR2,
120 p_type IN VARCHAR2,
121 p_sequence IN NUMBER,
122 p_search_results_visible IN VARCHAR2,
123 p_item_detail_visible IN VARCHAR2,
124 p_searchable IN VARCHAR2,
125 p_required IN VARCHAR2,
126 p_refinable IN VARCHAR2,
127 p_multivalue IN VARCHAR2,
128 p_default_value IN VARCHAR2,
129 p_language IN VARCHAR2,
130 p_category_id IN NUMBER,
131 p_request_id IN NUMBER DEFAULT -1,
132 p_rebuild_flag IN VARCHAR2,
133 p_descriptor_id_out OUT NOCOPY NUMBER,
134 p_user_id IN NUMBER,
135 p_login_id IN NUMBER);
136
137
138 -------------------------------------------------------------------------
139 -- UPDATE ACTION --
140 -------------------------------------------------------------------------
141 /**
142 ** Proc : update_category
143 ** Desc : Update an existing category for a sepcified language.
144 ** If parent is specified, a new link will be created.
145 **/
146 /* Changes for userId, loginId Added two parameters p_user_id, p_login_id */
147 PROCEDURE update_category (p_category_id IN NUMBER,
148 p_language IN VARCHAR2,
149 p_name IN VARCHAR2 DEFAULT NULL,
150 p_description IN VARCHAR2 DEFAULT NULL,
151 p_type IN NUMBER DEFAULT -1,
152 p_parent_id IN NUMBER DEFAULT -1,
153 p_request_id IN NUMBER DEFAULT -1,
154 p_user_id IN NUMBER,
155 p_login_id IN NUMBER);
156
157 /**
158 ** Proc : update_descriptor
159 ** Desc : Update a existing local descriptor for a specified language
160 ** within a category.
161 **/
162 /* Changes for userId, loginId Added two parameters p_user_id, p_login_id */
163 PROCEDURE update_descriptor (p_descriptor_id IN NUMBER,
164 p_language IN VARCHAR2,
165 p_name IN VARCHAR2 DEFAULT NULL,
166 p_description IN VARCHAR2 DEFAULT NULL,
167 p_default_value IN VARCHAR2 DEFAULT NULL,
168 p_sequence IN VARCHAR2 DEFAULT NULL,
169 p_search_results_visible IN VARCHAR2 DEFAULT NULL,
170 p_item_detail_visible IN VARCHAR2 DEFAULT NULL,
171 p_searchable IN VARCHAR2 DEFAULT NULL,
172 p_required IN VARCHAR2 DEFAULT NULL,
173 p_refinable IN VARCHAR2 DEFAULT NULL,
174 p_multivalue IN VARCHAR2 DEFAULT NULL,
175 p_request_id IN NUMBER DEFAULT -1,
176 p_section_tag OUT NOCOPY NUMBER,
177 p_stored_in_table OUT NOCOPY VARCHAR2,
178 p_stored_in_column OUT NOCOPY VARCHAR2,
179 p_user_id IN NUMBER,
180 p_login_id IN NUMBER);
181
182
183 -------------------------------------------------------------------------
184 -- DELETE ACTION --
185 -------------------------------------------------------------------------
186 /**
187 ** Proc : delete_child_category
188 ** Desc : Delete a category as a child of another category.
189 ** Also this parent category should be navigation type.
190 **/
191
192 PROCEDURE delete_child_category (p_parent_id IN NUMBER,
193 p_child_id IN NUMBER);
194
195 /**
196 ** Proc : delete_category_tree
197 ** Desc : Navigate the subtree, delete the whole subtree and items
198 ** associated.
199 **/
200 PROCEDURE delete_category_tree (p_category_id IN NUMBER);
201 PROCEDURE delete_category (p_category_id IN NUMBER);
202
203 /**
204 ** Proc : delete_descriptor
205 ** Desc : Delete the local descriptor within a category.
206 ** If no local descriptors for this category, the
207 ** dynamic table and view will be dropped.
208 **/
209 --Bug#3027134 Added who columns in icx_cat_deleted_attributes
210 --as part of ECM OA Rewrite
211 --So add two parameters for user_id and login_id to delete_descriptors
212 --to populate the who columns in icx_cat_deleted_attributes.
213 PROCEDURE delete_descriptor (p_descriptor_id IN NUMBER,
214 p_request_id IN NUMBER DEFAULT -1,
215 p_user_id IN NUMBER,
216 p_login_id IN NUMBER);
217
218 -------------------------------------------------------------------------
219 -- Rebuild Index --
220 -------------------------------------------------------------------------
221 /*
222 ** Procedure : populate_ctx_desc_indexes
223 ** Synopsis : Update the ctx_<lang> columns for items belong to
224 ** those categories which own rebuild_flags or their
225 ** local descriptors' rebuild_flags are set to 'Y.'
226 **
227 ** Parameter: p_request_id - number of the job to rebuild
228 */
229
230 PROCEDURE populate_ctx_desc_indexes(p_request_id IN INTEGER := -1);
231
232 /*
233 ** Procedure : populate_ctx_desc_indexes
234 ** Synopsis : Overloaded version. Contains 2 extra out parameters
235 ** which are used by Concurrent program.
236 ** No other functional change
237 ** Update the ctx_<lang> columns for items belong to
238 ** those categories which own rebuild_flags or their
239 ** local descriptors' rebuild_flags are set to 'Y.'
240 **
241 ** Parameter: p_request_id - number of the job to rebuild
242 */
243
244 PROCEDURE populate_ctx_desc_indexes(errbuf OUT NOCOPY VARCHAR2,
245 retcode OUT NOCOPY VARCHAR2,
246 p_request_id IN INTEGER := -1);
247
248
249 -------------------------------------------------------------------------
250 -- Validation --
251 -------------------------------------------------------------------------
252 /**
253 ** Proc : validate_descriptor
254 ** Desc : validate whether the loaded parameters are valid
255 **/
256
257 PROCEDURE validate_descriptor(p_request_id IN OUT NOCOPY NUMBER,
258 p_line_number IN NUMBER,
259 p_user_action IN VARCHAR2,
260 p_system_action OUT NOCOPY VARCHAR2,
261 p_language IN VARCHAR2,
262 p_descriptor_id OUT NOCOPY NUMBER,
263 p_key IN VARCHAR2,
264 p_name IN VARCHAR2,
265 p_type IN VARCHAR2,
266 p_description IN VARCHAR2,
267 p_required IN VARCHAR2,
268 p_sequence IN VARCHAR2,
269 p_searchable IN VARCHAR2,
270 p_multivalue IN VARCHAR2,
271 p_itemdetailvisible IN VARCHAR2,
272 p_searchResultsVisible IN VARCHAR2,
273 p_owner_key IN VARCHAR2,
274 p_owner_name IN VARCHAR2,
275 p_owner_id OUT NOCOPY NUMBER,
276 p_is_valid OUT NOCOPY VARCHAR2);
277
278 /* validate descriptor updated online
279 PROCEDURE validate_update_desc_online(p_request_id IN OUT NOCOPY NUMBER,
280 p_line_number IN NUMBER,
281 p_session_key IN VARCHAR2,
282 p_owner_id IN NUMBER,
283 p_language IN VARCHAR2,
284 p_key IN VARCHAR2,
285 p_name IN VARCHAR2,
286 p_type IN VARCHAR2,
287 p_description IN VARCHAR2,
288 p_required IN VARCHAR2,
289 p_sequence IN VARCHAR2,
290 p_searchable IN VARCHAR2,
291 p_multivalue IN VARCHAR2,
292 p_itemdetailvisible IN VARCHAR2,
293 p_searchResultsVisible IN VARCHAR2,
294 p_descriptor_id IN NUMBER,
295 p_is_valid OUT NOCOPY VARCHAR2);
296
297 /* validate descriptor created online
298 PROCEDURE validate_add_desc_online(p_request_id IN OUT NOCOPY NUMBER,
299 p_line_number IN NUMBER,
300 p_session_key IN VARCHAR2,
301 p_owner_id IN NUMBER,
302 p_language IN VARCHAR2,
303 p_key IN VARCHAR2,
304 p_name IN VARCHAR2,
305 p_type IN VARCHAR2,
306 p_description IN VARCHAR2,
307 p_required IN VARCHAR2,
308 p_sequence IN VARCHAR2,
309 p_searchable IN VARCHAR2,
310 p_multivalue IN VARCHAR2,
314 */
311 p_itemdetailvisible IN VARCHAR2,
312 p_searchResultsVisible IN VARCHAR2,
313 p_is_valid OUT NOCOPY VARCHAR2);
315
316 /**
317 ** Proc : validate_category
318 ** Desc : check whether the passin parameters violate the rules
319 **/
320
321 PROCEDURE validate_category(p_request_id IN OUT NOCOPY NUMBER,
322 p_line_number IN NUMBER,
323 p_user_action IN VARCHAR2,
324 p_system_action OUT NOCOPY VARCHAR2,
325 p_language IN VARCHAR2,
326 p_category_id OUT NOCOPY NUMBER,
327 p_key IN VARCHAR2,
328 p_name IN VARCHAR2,
329 p_type IN VARCHAR2,
330 p_type_value OUT NOCOPY VARCHAR2,
331 p_owner_key IN VARCHAR2,
332 p_owner_name IN VARCHAR2,
333 p_owner_id OUT NOCOPY NUMBER,
334 p_is_valid OUT NOCOPY VARCHAR2);
335
336 /**
337 ** Proc : validate_hier_relationship
338 ** Desc : check the hierarchical relationship between categories
339 **/
340 PROCEDURE validate_hier_relationship(p_request_id IN OUT NOCOPY NUMBER,
341 p_line_number IN NUMBER,
342 p_user_action IN VARCHAR2,
343 p_system_action OUT NOCOPY VARCHAR2,
344 p_language IN VARCHAR2,
345 p_parent_key IN VARCHAR2,
346 p_parent_name IN VARCHAR2,
347 p_parent_id OUT NOCOPY NUMBER,
348 p_child_key IN VARCHAR2,
349 p_child_name IN VARCHAR2,
350 p_child_id OUT NOCOPY NUMBER,
351 p_is_valid OUT NOCOPY VARCHAR2);
352
353 PROCEDURE save_failed_category(p_request_id IN NUMBER,
354 p_line_number IN NUMBER,
355 p_action IN VARCHAR2,
356 p_key IN VARCHAR2,
357 p_name IN VARCHAR2,
358 p_type IN VARCHAR2,
359 p_description IN VARCHAR2,
360 p_owner_key IN VARCHAR2,
361 p_owner_name IN VARCHAR2);
362
363 PROCEDURE save_failed_descriptor(p_request_id IN NUMBER,
364 p_line_number IN NUMBER,
365 p_action IN VARCHAR2,
366 p_key IN VARCHAR2,
367 p_name IN VARCHAR2,
368 p_type IN VARCHAR2,
369 p_description IN VARCHAR2,
370 p_owner_key IN VARCHAR2,
371 p_owner_name IN VARCHAR2,
372 p_sequence IN VARCHAR2,
373 p_default_value IN VARCHAR2,
374 p_searchable IN VARCHAR2,
375 p_itemdetailvisible IN VARCHAR2,
376 p_searchresultsvisible IN VARCHAR2,
377 p_required IN VARCHAR2,
378 p_multivalue IN VARCHAR2,
379 p_errortype IN VARCHAR2);
380
381 PROCEDURE save_failed_hier_relationship(p_request_id IN NUMBER,
382 p_line_number IN NUMBER,
383 p_action IN VARCHAR2,
384 p_parent_key IN VARCHAR2,
385 p_parent_name IN VARCHAR2,
386 p_child_key IN VARCHAR2,
387 p_child_name IN VARCHAR2);
388
389
390 /**
391 ** Proc : InsertError
392 ** Desc : insert errors inti failed_line_messages table
393 **/
394
395 PROCEDURE InsertError(p_request_id in out NOCOPY number,
396 p_descriptor_key in varchar2,
397 p_message_name in varchar2,
398 p_line_number in number);
399
400 -------------------------------------------------------------------------
401 -- Manage Section Tags --
402 -------------------------------------------------------------------------
403 /**
404 ** Proc : release_section_tag
405 ** Desc : Called when a descriptor is to be deleted or made not searchable
406 ** SHOULD BE CALLED BEFORE THE DESCRIPTOR IS ACTUALLY DELETED
407 ** Before calling this the rows in icx_por_categories_tl with the
408 ** given rt_category_id should be locked thru a SELECT...FOR UPDATE
409 ** to avoid concurrent access to the SECTION_MAP column. The calling
410 ** code is responsible for committing the changes.
411 ** Parameters:
412 ** p_category_id - category to be modified
413 ** p_descriptor_id - descriptor to be modified
414 **/
415 PROCEDURE release_section_tag(p_category_id IN NUMBER,
416 p_descriptor_id IN NUMBER);
417
418 /**
419 ** Proc : assign_section_tag
420 ** Desc : Assigns a section tag to a given searchable descriptor. If the
421 ** descriptor is already assigned a tag then the assigned tag will
422 ** be returned.
423 ** Before calling this the rows in icx_por_categories_tl with the
427 ** Parameters:
424 ** given rt_category_id should be locked thru a SELECT...FOR UPDATE
425 ** to avoid concurrent access to the SECTION_MAP column. The calling
426 ** code is responsible for committing the changes.
428 ** p_category_id IN NUMBER - category to be modified
429 ** p_descriptor_id IN NUMBER - descriptor to be modified
430 ** p_section_tag OUT NUMBER - section tag assigned
431 **/
432 PROCEDURE assign_section_tag(p_category_id IN NUMBER,
433 p_descriptor_id IN NUMBER,
434 p_section_tag OUT NOCOPY NUMBER,
435 p_stored_in_table OUT NOCOPY VARCHAR2,
436 p_stored_in_column OUT NOCOPY VARCHAR2,
437 p_type IN VARCHAR2);
438
439 /**
440 ** Proc : assign_all_section_tags
441 ** Desc : Assigns section tags to all searchable descriptors of a given
442 ** category. This is intended to be called during the upgrade to 6.2
443 ** or when batch update of a category is needed
444 ** Before calling this the rows in icx_por_categories_tl with the
445 ** given rt_category_id should be locked thru a SELECT...FOR UPDATE
446 ** to avoid concurrent access to the SECTION_MAP column. The calling
447 ** code is responsible for committing the changes.
448 ** Parameters:
449 ** p_category_id - category to be modified
450 **/
451 PROCEDURE assign_all_section_tags(p_category_id IN NUMBER);
452
453 /* this is added for bug 2108372
454 the procedure itself will do nothing.
455 it will be called when starting a shema bulk load job.
456 */
457 PROCEDURE prepare_job;
458
459 PROCEDURE inc_schema_change_version(p_category_id IN NUMBER);
460
461 PROCEDURE fail_root_descriptor_section(p_request_id IN OUT NOCOPY NUMBER,
462 p_action IN VARCHAR2,
463 p_line_number IN NUMBER);
464
465 PROCEDURE sync_deleted_descriptors;
466
467 /**
468 ** Proc : get_stored_in_values
469 ** Desc : Formulates the stored_in_table, stored_in_column
470 ** Parameters:
471 ** p_descriptor_id - rt_descriptor_id
472 ** p_category_id - rt_category_id
473 ** type - type
477 p_category_id IN NUMBER,
474 ** p_section_tag - section_tag
475 **/
476 PROCEDURE get_stored_in_values(p_descriptor_id IN NUMBER,
478 p_type IN VARCHAR2,
479 p_section_tag IN NUMBER,
480 p_stored_in_table OUT NOCOPY VARCHAR2,
481 p_stored_in_column OUT NOCOPY VARCHAR2);
482
483 /**
484 ** Proc : update_items_for_category
485 ** Desc : Update primary_category_name in items_tlp with the category name for
486 a sepcified language.
487 **/
488 PROCEDURE update_items_for_category (
489 errbuf OUT NOCOPY VARCHAR2,
490 retcode OUT NOCOPY VARCHAR2,
491 p_category_name IN VARCHAR2,
492 p_category_id IN NUMBER,
493 p_language IN VARCHAR2,
494 p_request_id IN NUMBER DEFAULT -1);
495
496 /**
497 ** Proc : update_items_for_category
498 ** Desc : Update primary_category_name in items_tlp with the category name for a sepcified language.
499 **/
500 PROCEDURE update_items_for_category (p_category_name IN VARCHAR2,
501 p_category_id IN NUMBER,
502 p_language IN VARCHAR2,
503 p_request_id IN NUMBER DEFAULT -1);
504 /**
505 ** Proc : handle_delete_descriptors
506 ** Desc : Handles the plsql call required when a descritpor is deleted from ecmanager
507 **/
508 PROCEDURE handle_delete_descriptors (p_searchable IN NUMBER,
509 p_rename_category_done IN VARCHAR2,
510 p_category_name IN VARCHAR2,
511 p_rt_category_id IN NUMBER,
512 p_language IN VARCHAR2,
513 p_request_id IN NUMBER DEFAULT -1);
514
515 /**
516 ** Proc : handle_delete_descriptors
517 ** Desc : Overloaded version. No functional change except the 2 OUT parameters
518 ** Handles the plsql call required when a descritpor is deleted
519 ** from ecmanager
520 **/
521 PROCEDURE handle_delete_descriptors (
522 errbuf OUT NOCOPY VARCHAR2,
523 retcode OUT NOCOPY VARCHAR2,
524 p_searchable IN NUMBER,
525 p_rename_category_done IN VARCHAR2,
526 p_category_name IN VARCHAR2,
527 p_rt_category_id IN NUMBER,
528 p_language IN VARCHAR2,
529 p_request_id IN NUMBER DEFAULT -1);
530
531 END ICX_POR_SCHEMA_UPLOAD;
[Home] [Help]