[Home] [Help]
PACKAGE BODY: APPS.CS_KB_SOLN_CATEGORIES_PVT
Source
1 PACKAGE BODY CS_KB_SOLN_CATEGORIES_PVT AS
2 /* $Header: csvcatb.pls 120.1 2005/09/13 14:11:10 alawang noship $ */
3
4 soln_category_link_obj_code varchar2(30) := 'CS_KB_SOLN_CATEGORY_LINK';
5
6 procedure getStdParams
7 (
8 x_current_date OUT NOCOPY date,
9 x_current_user_id OUT NOCOPY number,
10 x_current_login_id OUT NOCOPY number
11 )
12 is
13 begin
14 x_current_date := sysdate;
15 x_current_user_id := fnd_global.user_id;
16 x_current_login_id := fnd_global.login_id;
17 end getStdParams;
18
19 -- this API is used by JTT, obsoleted
20 procedure createCategory
21 (
22 p_api_version in number,
23 p_init_msg_list in varchar2 := FND_API.G_FALSE,
24 p_commit in varchar2 := FND_API.G_FALSE,
25 p_validation_level in number := FND_API.G_VALID_LEVEL_FULL,
26 x_return_status OUT NOCOPY varchar2,
27 x_msg_count OUT NOCOPY number,
28 x_msg_data OUT NOCOPY varchar2,
29 p_parent_category_id in number,
30 p_name in varchar2,
31 p_description in varchar2,
32 x_category_id OUT NOCOPY number
33 )
34 is
35 begin
36 createCategory( null ,
37 p_api_version ,
38 p_init_msg_list ,
39 p_commit ,
40 p_validation_level ,
41 x_return_status ,
42 x_msg_count ,
43 x_msg_data ,
44 p_parent_category_id,
45 p_name ,
46 p_description ,
47 x_category_id,
48 3 ); -- default to external visibility, if called from JTT
49
50 end;
51
52 -- this new API is called from OA, core should use this one instead
53 procedure createCategory
54 (
55 p_category_id in number,
56 p_api_version in number,
57 p_init_msg_list in varchar2 := FND_API.G_FALSE,
58 p_commit in varchar2 := FND_API.G_FALSE,
59 p_validation_level in number := FND_API.G_VALID_LEVEL_FULL,
60 x_return_status OUT NOCOPY varchar2,
61 x_msg_count OUT NOCOPY number,
62 x_msg_data OUT NOCOPY varchar2,
63 p_parent_category_id in number,
64 p_name in varchar2,
65 p_description in varchar2,
66 x_category_id OUT NOCOPY number,
67 p_visibility_id in number
68 )
69 is
70 l_current_date date;
71 l_current_user_id number;
72 l_current_login_id number;
73 l_rowid varchar2(50);
74 l_category_id number;
75 l_property_id number;
76 begin
77 getStdParams(l_current_date, l_current_user_id, l_current_login_id);
78 l_category_id := p_category_id;
79
80 /* Validations */
81
82 /* Insert the category */
83 cs_kb_soln_categories_pkg.insert_row
84 (
85 X_ROWID => l_rowid,
86 X_CATEGORY_ID => l_category_id,
87 X_PARENT_CATEGORY_ID => p_parent_category_id,
88 X_NAME => p_name,
89 X_DESCRIPTION => p_description,
90 X_CREATION_DATE => l_current_date,
91 X_CREATED_BY => l_current_user_id,
92 X_LAST_UPDATE_DATE => l_current_date,
93 X_LAST_UPDATED_BY => l_current_user_id,
94 X_LAST_UPDATE_LOGIN => l_current_login_id,
95 X_VISIBILITY_ID => p_visibility_id
96 );
97 x_category_id := l_category_id;
98
99 cs_kb_security_pvt.ADD_CATEGORY_TO_DENORM (
100 P_CATEGORY_ID => x_category_id,
101 P_PARENT_CATEGORY_ID => p_parent_category_id,
102 P_VISIBILITY_ID => p_visibility_id,
103 X_RETURN_STATUS => x_return_status,
104 X_MSG_DATA => x_msg_data,
105 X_MSG_COUNT => x_msg_count
106 );
107
108 if fnd_api.to_boolean( p_commit ) then
109 commit;
110 end if;
111 end createCategory;
112
113 procedure removeCategory
114 (
115 p_api_version in number,
116 p_init_msg_list in varchar2 := FND_API.G_FALSE,
117 p_commit in varchar2 := FND_API.G_FALSE,
118 p_validation_level in number := FND_API.G_VALID_LEVEL_FULL,
119 x_return_status OUT NOCOPY varchar2,
120 x_msg_count OUT NOCOPY number,
121 x_msg_data OUT NOCOPY varchar2,
122 p_category_id in number
123 )
124 is
125 n_child_solutions number;
126 n_subcatgories number;
127 l_delete_status number;
128
129 /* cursor removeLinksCsr
130 ( category_id number, link_obj_code varchar2 )
131 is
132 select link_id
133 from cs_kb_set_links
134 where object_code = link_obj_code
135 and other_id = category_id;
136 */
137 begin
138
139 select /*+ index(sl) */ count( * ) into n_child_solutions
140 from cs_kb_set_categories sl, cs_kb_sets_b b
141 where sl.category_id = p_category_id
142 and b.set_id = sl.set_id
143 --and b.status = 'PUB';
144 and (b.status = 'PUB' or (b.status <> 'OBS' and b.latest_version_flag = 'Y'));
145
146 select count( * ) into n_subcatgories
147 from cs_kb_soln_categories_b
148 where parent_category_id = p_category_id;
149
150 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
151 -- check if the category is deletable
152 -- i.e. it does not contain sub-categories nor PUBlished child solutions
153 if( n_child_solutions <> 0 OR n_subcatgories <> 0 ) then
154 FND_MSG_PUB.initialize;
155 FND_MESSAGE.set_name('CS', 'CS_KB_C_CAT_DELETE_FAILED');
156 FND_MSG_PUB.ADD;
157 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
158 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE ,
159 p_count => X_MSG_COUNT,
160 p_data => X_MSG_DATA);
161
162 ELSE
163 -- Delete all set category links (which should not be PUBlished versions)
164 delete /*+ index(sl) */ from cs_kb_set_categories sl
165 where sl.category_id = p_category_id;
166 /* for linkIdRec in removeLinksCsr
167 ( p_category_id, soln_category_link_obj_code )
168 loop
169 l_delete_status :=
170 cs_kb_set_links_pkg.delete_set_link
171 (
172 p_link_id => linkIdRec.link_id
173 );
174 end loop;
175 */
176 -- Delete this leaf category
177 cs_kb_soln_categories_pkg.delete_row( p_category_id );
178
179 cs_kb_security_pvt.REMOVE_CATEGORY_FROM_CAT_GROUP(
180 P_CATEGORY_ID => p_category_id,
181 X_RETURN_STATUS => x_return_status,
182 X_MSG_DATA => x_msg_data,
183 X_MSG_COUNT => x_msg_count
184 );
185 if fnd_api.to_boolean( p_commit ) then
186 commit;
187 end if;
188 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
189
190 end if;
191
192 end removeCategory;
193
194 -- Start of comments
195 -- API name : removeCategoryCascade
196 -- Type : Private
197 -- Function : This removes a category and its descendents recursively, once
198 -- all of them contain no solutions.
199 -- Pre-reqs : None.
200 -- Parameters :
201 -- IN : P_CATEGORY_ID NUMBER Required
202 --
203 -- OUT : X_RETURN_STATUS VARCHAR2
204 -- X_MSG_DATA VARCHAR2
205 -- X_MSG_COUNT NUMBER
206 --
207 -- History:
208 -- 10-Aug-04 alawang created.
209 --
210 --
211 --
212 -- Notes :
213 -- 1) This method will query all decedents of a given category and delete them
214 -- one by one by calling removeCategory from bottom up. It relies on
215 -- removeCategory to check the emptiness.
216 --
217 -- End of comments
218 procedure removeCategoryCascade
219 (
220 p_api_version in number,
221 p_category_id in number,
222 p_init_msg_list in varchar2 := FND_API.G_FALSE,
223 p_commit in varchar2 := FND_API.G_FALSE,
224 p_validation_level in number := FND_API.G_VALID_LEVEL_FULL,
225 x_return_status OUT NOCOPY varchar2,
226 x_msg_count OUT NOCOPY number,
227 x_msg_data OUT NOCOPY varchar2
228 )
229 is
230 l_category_id number;
231 l_category_fullpath_name varchar2(1000);
232 cursor Get_Descendent_Categories
233 ( cp_category_id number)
234 is
235 SELECT SolnCategoryEO.CATEGORY_ID
236 FROM cs_kb_soln_categories_b SolnCategoryEO
237 start with SolnCategoryEO.CATEGORY_ID = cp_category_id
238 connect by prior CATEGORY_ID = PARENT_CATEGORY_ID
239 order by level desc;
240 cursor Get_Category_Fullpath_Name
241 ( cp_category_id number)
242 is
246 savepoint removeCategoryCascade_PVT;
243 SELECT cs_kb_soln_categories_pvt.admin_cat_fullpath_names( cp_category_id, ' > ' )
244 FROM dual;
245 begin
247 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
248
249 -- Loop through descendent categories and delete them one by one.
250 -- The query return descendent categories from bottome up.
251 OPEN Get_Descendent_Categories(p_category_id);
252 LOOP
253 FETCH Get_Descendent_Categories INTO l_category_id;
254 EXIT WHEN Get_Descendent_Categories%NOTFOUND;
255 removeCategory(p_api_version => 1.0,
256 p_category_id => l_category_id,
257 x_return_status => x_return_status,
258 x_msg_data => x_msg_data,
259 x_msg_count => x_msg_count);
260 -- If any of these descendent categories failed to be deleted. Rollback
261 -- and return.
262 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
263 ROLLBACK TO removeCategoryCascade_PVT;
264 --Prepare error message
265 OPEN Get_Category_Fullpath_Name(p_category_id);
266 FETCH Get_Category_Fullpath_Name INTO l_category_fullpath_name;
267 CLOSE Get_Category_Fullpath_Name;
268
269 FND_MSG_PUB.initialize;
270 FND_MESSAGE.set_name('CS', 'CS_KB_C_CAT_DELETE_CAS_FAILED');
271 FND_MESSAGE.SET_TOKEN(TOKEN => 'CATEGORY_FULLPATH_NAME',
272 VALUE => l_category_fullpath_name,
273 TRANSLATE => true);
274 FND_MSG_PUB.ADD;
275 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE ,
276 p_count => X_MSG_COUNT,
277 p_data => X_MSG_DATA);
278 EXIT;
279 END IF;
280 END LOOP;
281 CLOSE Get_Descendent_Categories;
282
283 -- If everything is okay and specified to commit, then commit the transaction.
284 IF X_RETURN_STATUS = FND_API.G_RET_STS_SUCCESS AND
285 fnd_api.to_boolean( p_commit ) THEN
286 COMMIT;
287 END IF;
288 end removeCategoryCascade;
289
290 -- this API is used by JTT, obsoleted
291 procedure updateCategory
292 (
293 p_api_version in number,
294 p_init_msg_list in varchar2 := FND_API.G_FALSE,
295 p_commit in varchar2 := FND_API.G_FALSE,
296 p_validation_level in number := FND_API.G_VALID_LEVEL_FULL,
297 x_return_status OUT NOCOPY varchar2,
298 x_msg_count OUT NOCOPY number,
299 x_msg_data OUT NOCOPY varchar2,
300 p_category_id in number,
301 p_parent_category_id in number,
302 p_name in varchar2,
303 p_description in varchar2
304 )
305 is
306 begin
307 updateCategory( p_api_version ,
308 p_init_msg_list ,
309 p_commit ,
310 p_validation_level ,
311 x_return_status ,
312 x_msg_count ,
313 x_msg_data ,
314 p_category_id ,
315 p_parent_category_id,
316 p_name ,
317 p_description ,
318 3 ); -- default to external visibility, if called from JTT
319 end;
320
321 -- this new API is called from OA, core should use this one instead
322 procedure updateCategory
323 (
324 p_api_version in number,
325 p_init_msg_list in varchar2 := FND_API.G_FALSE,
326 p_commit in varchar2 := FND_API.G_FALSE,
327 p_validation_level in number := FND_API.G_VALID_LEVEL_FULL,
328 x_return_status OUT NOCOPY varchar2,
329 x_msg_count OUT NOCOPY number,
330 x_msg_data OUT NOCOPY varchar2,
331 p_category_id in number,
332 p_parent_category_id in number,
333 p_name in varchar2,
334 p_description in varchar2,
335 p_visibility_id in number
336 )
337 is
338 l_current_date date;
339 l_current_user_id number;
340 l_current_login_id number;
341
342 l_original_visibility_id NUMBER;
343 l_original_parent_category_id NUMBER;
344 l_request_id NUMBER;
348 l_category_id number;
345 l_return_status VARCHAR2(1);
346 l_found_cat_group_diff VARCHAR2(1);
347 l_cat_group_id number;
349
350 CURSOR GET_ORIGINAL_CAT_VISIBILITY IS
351 SELECT Visibility_Id
352 FROM CS_KB_SOLN_CATEGORIES_B
353 WHERE Category_Id = p_category_id;
354
355 CURSOR GET_ORIGINAL_PARENT_CAT IS
356 SELECT parent_category_Id
357 FROM CS_KB_SOLN_CATEGORIES_B
361 SELECT c.category_id
358 WHERE Category_Id = p_category_id;
359
360 CURSOR Get_Moved_Categories IS
362 FROM CS_KB_SOLN_CATEGORIES_B c
363 START WITH c.category_id = P_CATEGORY_ID
364 CONNECT BY PRIOR c.category_id = c.parent_category_id
365 ORDER BY level asc;
366
367 CURSOR GET_DUP_CG_MEMBERSHIP(cp_category_id number) IS
368 SELECT Distinct Category_Group_Id
369 FROM CS_KB_CAT_GROUP_DENORM
370 WHERE CHILD_CATEGORY_ID = P_PARENT_CATEGORY_ID
371 intersect
372 select distinct m.category_group_id
373 FROM CS_KB_CAT_GROUP_MEMBERS m
374 WHERE Category_Id = cp_category_id;
375
376
377 begin
378 savepoint updateCategory_PVT;
379
380 OPEN GET_ORIGINAL_CAT_VISIBILITY;
381 FETCH GET_ORIGINAL_CAT_VISIBILITY INTO l_original_visibility_id;
382 CLOSE GET_ORIGINAL_CAT_VISIBILITY;
383
384 OPEN GET_ORIGINAL_PARENT_CAT;
385 FETCH GET_ORIGINAL_PARENT_CAT INTO l_original_parent_category_id;
386 CLOSE GET_ORIGINAL_PARENT_CAT;
387
388 getStdParams(l_current_date, l_current_user_id, l_current_login_id);
389
390 /* Validations */
391
392 /* Update the category */
393 cs_kb_soln_categories_pkg.update_row
394 (
395 X_CATEGORY_ID => p_category_id,
396 X_PARENT_CATEGORY_ID => p_parent_category_id,
397 X_NAME => p_name,
398 X_DESCRIPTION => p_description,
399 X_LAST_UPDATE_DATE => l_current_date,
400 X_LAST_UPDATED_BY => l_current_user_id,
401 X_LAST_UPDATE_LOGIN => l_current_login_id,
402 X_VISIBILITY_ID => p_visibility_id
403 );
404
405 cs_kb_security_pvt.UPDATE_CATEGORY_TO_DENORM (
406 P_CATEGORY_ID => p_category_id,
407 P_VISIBILITY_ID => p_visibility_id,
408 X_RETURN_STATUS => x_return_status,
409 X_MSG_DATA => x_msg_data,
410 X_MSG_COUNT => x_msg_count
411 );
412
413 IF l_original_visibility_id <> p_visibility_id THEN
414
415 CS_KB_SYNC_INDEX_PKG.request_mark_idx_on_sec_change
416 ( 'CHANGE_CAT_VIS',
417 p_category_id,
418 l_original_visibility_id,
419 l_request_id,
420 l_return_status );
421
422 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
423 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
424 ELSE
425 RAISE INDEX_SYNC_FAILED;
426 END IF;
427
428 END IF;
429
430 IF l_original_parent_category_id <> p_parent_category_id THEN
431 for x in Get_Moved_Categories loop
432 for y in GET_DUP_CG_MEMBERSHIP(x.category_id) loop
433 CS_KB_SECURITY_PVT.delete_category_group_member(
434 p_category_group_id => y.category_group_id,
435 p_category_id => x.category_id,
436 X_RETURN_STATUS => x_return_status,
437 X_MSG_DATA => x_msg_data,
438 X_MSG_COUNT => x_msg_count
439 );
440 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
441 RAISE CG_MEMBER_DEL_FAILED;
442 END IF;
443 end loop;
444 end loop;
445
446
447 -- Update denorm table.
448 cs_kb_security_pvt.MOVE_CATEGORY_IN_DENORM (
449 P_CATEGORY_ID => p_category_id,
450 X_RETURN_STATUS => x_return_status,
451 X_MSG_DATA => x_msg_data,
452 X_MSG_COUNT => x_msg_count
453 );
454 -- Update index.
455 CS_KB_SYNC_INDEX_PKG.request_mark_idx_on_sec_change
456 ( 'CHANGE_PARENT_CAT',
457 p_category_id,
458 l_original_parent_category_id,
459 l_request_id,
460 l_return_status );
461
462 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
463 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
464 ELSE
465 RAISE INDEX_SYNC_FAILED;
466 END IF;
467
468 END IF;
469
470 if fnd_api.to_boolean( p_commit ) then
471 commit;
472 end if;
473
474 EXCEPTION
475 WHEN CG_MEMBER_DEL_FAILED THEN
476 ROLLBACK TO updateCategory_PVT;
477 FND_MSG_PUB.initialize;
478 FND_MESSAGE.set_name('CS', 'CS_KB_C_DELETE_ERR');
479 FND_MSG_PUB.ADD;
480 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
481 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE ,
482 p_count => X_MSG_COUNT,
483 p_data => X_MSG_DATA);
484 WHEN INDEX_SYNC_FAILED THEN
485 ROLLBACK TO updateCategory_PVT;
486 FND_MSG_PUB.initialize;
487 FND_MESSAGE.set_name('CS', 'CS_KB_SYNC_REQ_FAILED');
488 FND_MSG_PUB.ADD;
489 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
490 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE ,
491 p_count => X_MSG_COUNT,
492 p_data => X_MSG_DATA);
493
494 end updateCategory;
495
496 -- 11510 - This procedure has been changed to use cs_kb_set_categories
497 -- The x_soln_category_link_id out parameter is no longer
498 -- used, so it will always return 0.
499 procedure addSolutionToCategory
500 (
504 p_validation_level in number := FND_API.G_VALID_LEVEL_FULL,
501 p_api_version in number,
502 p_init_msg_list in varchar2 := FND_API.G_FALSE,
503 p_commit in varchar2 := FND_API.G_FALSE,
505 x_return_status OUT NOCOPY varchar2,
506 x_msg_count OUT NOCOPY number,
507 x_msg_data OUT NOCOPY varchar2,
508 p_solution_id in number,
509 p_category_id in number,
510 x_soln_category_link_id OUT NOCOPY number
511 )
515 l_user number := fnd_global.user_id;
512 is
513 l_date date := sysdate;
514 l_login number := fnd_global.login_id;
516 begin
517 x_soln_category_link_id := 0;
518
519 -- Validation is done here
520
521 SAVEPOINT linkSolutionToCategoryTran;
522
523 -- There is no PL/SQL table handler for the cs_kb_set_categories
524 -- linking table yet. This code will directly perform insert.
525 insert into CS_KB_SET_CATEGORIES (
526 SET_ID,
527 CATEGORY_ID,
528 CREATION_DATE,
529 CREATED_BY,
530 LAST_UPDATE_DATE,
531 LAST_UPDATED_BY,
532 LAST_UPDATE_LOGIN,
533 ATTRIBUTE_CATEGORY,
534 ATTRIBUTE1,
535 ATTRIBUTE2,
536 ATTRIBUTE3,
537 ATTRIBUTE4,
538 ATTRIBUTE5,
539 ATTRIBUTE6,
540 ATTRIBUTE7,
541 ATTRIBUTE8,
542 ATTRIBUTE9,
543 ATTRIBUTE10,
544 ATTRIBUTE11,
545 ATTRIBUTE12,
546 ATTRIBUTE13,
547 ATTRIBUTE14,
548 ATTRIBUTE15
549 ) values (
550 p_solution_id,
551 p_category_id,
552 l_date,
553 l_user,
554 l_date,
555 l_user,
556 l_login,
557 null,
558 null,
559 null,
560 null,
561 null,
562 null,
563 null,
564 null,
565 null,
566 null,
567 null,
568 null,
569 null,
570 null,
571 null,
572 null
573 );
574
575 x_return_status := fnd_api.g_ret_sts_success;
576 x_msg_count := 0;
577 x_msg_data := null;
578
579 if fnd_api.to_boolean( p_commit ) then
580 commit;
581 end if;
582 exception
583 when DUP_VAL_ON_INDEX then
584 rollback to linkSolutionToCategoryTran; -- undo changes
585 x_return_status := fnd_api.g_ret_sts_success;
586 x_msg_count := 0;
587 x_msg_data := null;
588 if fnd_api.to_boolean( p_commit ) then
589 commit;
590 end if;
591 when others then
592 x_return_status := fnd_api.g_ret_sts_error;
593 x_msg_count := 0;
594 x_msg_data := null;
595 end addSolutionToCategory;
596
597 -- 11.5.10 dev - This procedure has been changed to use cs_kb_set_categories.
598 -- Need to handle error better.
599 procedure removeSolutionFromCategory
600 (
601 p_api_version in number,
602 p_init_msg_list in varchar2 := FND_API.G_FALSE,
603 p_commit in varchar2 := FND_API.G_FALSE,
604 p_validation_level in number := FND_API.G_VALID_LEVEL_FULL,
605 x_return_status OUT NOCOPY varchar2,
606 x_msg_count OUT NOCOPY number,
607 x_msg_data OUT NOCOPY varchar2,
608 p_solution_id in number,
609 p_category_id in number
610 )
611 is
612 begin
613 -- Validation here
614
615 -- Remove the links
619
616 delete from cs_kb_set_categories
617 where set_id = p_solution_id
618 and category_id = p_category_id;
620 x_return_status := fnd_api.g_ret_sts_success;
621 x_msg_count := 0;
622 x_msg_data := null;
623
624 if fnd_api.to_boolean( p_commit ) then
625 commit;
626 end if;
627
628 exception
629 when others then
630 x_return_status := fnd_api.g_ret_sts_error;
631 x_msg_count := 0;
632 x_msg_data := null;
633 end removeSolutionFromCategory;
634
635 function secure_cat_fullpath_names( category_id number, separator varchar2 )
636 return varchar2 is
637 cursor c_fullpath_names( p_cat_id number,
638 p_category_group_id number,
639 p_soln_visibility_position number) is
640 select tl.name--, b.category_id, b.lev
641 from ( SELECT category_id, level lev
642 FROM cs_kb_soln_categories_b
643 START WITH category_id = p_cat_id
644 CONNECT BY prior parent_category_id = category_id
645 ) b, cs_kb_soln_categories_tl tl, cs_kb_cat_group_denorm mv
646 where
647 b.category_id = tl.category_id
648 and tl.language = userenv( 'LANG' )
649 and tl.category_id = mv.child_category_id
650 and mv.category_group_id = p_category_group_id -- 2
651 and mv.visibility_position >= p_soln_visibility_position -- 1000
652 order by b.lev desc;
653
654 i number (15);
655 l_category_group_id number;
656 l_soln_visibility_position number;
657 type t_varchar_arr is table of varchar2( 80 );
658 l_catnames_arr t_varchar_arr;
659 fullpath_name VARCHAR2( 2000 );
660 begin
661 l_category_group_id := cs_kb_security_pvt.Get_Category_Group_Id;
662 l_soln_visibility_position := cs_kb_security_pvt.Get_Soln_Visibility_Position;
663
664 open c_fullpath_names( category_id,
665 l_category_group_id,
666 l_soln_visibility_position );
667 fetch c_fullpath_names bulk collect into l_catnames_arr;
668 close c_fullpath_names;
669
670 if( l_catnames_arr.count >= 1 ) then
671 fullpath_name := fnd_message.GET_STRING('CS','CS_KB_BROWSE_ROOT_LABEL');
672
673 for i in 1..l_catnames_arr.count loop
674 fullpath_name := fullpath_name || separator || l_catnames_arr(i);
675 end loop;
676 end if;
677 return fullpath_name;
678 end;
679
680 function admin_cat_fullpath_names( category_id number, separator varchar2 )
681 return varchar2 is
682 cursor c_fullpath_names( cat_id number ) is
683 select tl.name--, b.category_id, b.lev
684 from ( SELECT category_id, level lev
685 FROM cs_kb_soln_categories_b
686 START WITH category_id = cat_id
687 CONNECT BY prior parent_category_id = category_id
688 ) b, cs_kb_soln_categories_tl tl
689 where
690 b.category_id = tl.category_id
691 and tl.language = userenv( 'LANG' )
692 order by b.lev desc;
693
694 i number (15);
695 type t_varchar_arr is table of varchar2( 80 );
696 l_catnames_arr t_varchar_arr;
697
698 fullpath_name VARCHAR2( 2000 );
699 begin
700 open c_fullpath_names( category_id );
701 fetch c_fullpath_names bulk collect into l_catnames_arr;
702 close c_fullpath_names;
703
704 if( l_catnames_arr.count >= 1 ) then
705 fullpath_name := l_catnames_arr(1);
706 for i in 2..l_catnames_arr.count loop
707 fullpath_name := fullpath_name || separator || l_catnames_arr(i);
711 end;
708 end loop;
709 end if;
710 return fullpath_name;
712
713 function admin_cat_fullpath_ids( category_id number )
714 return varchar2 is
715 cursor c_fullpath_ids( cat_id number ) is
716 SELECT category_id
717 FROM cs_kb_soln_categories_b
718 START WITH category_id = cat_id
719 CONNECT BY prior parent_category_id = category_id
720 order by level desc;
721
722 i number (15);
723 type t_varchar_arr is table of varchar2( 80 );
724 l_catids_arr t_varchar_arr;
725 separator VARCHAR2( 1 ) := ':';
726
727 fullpath_ids VARCHAR2( 2000 );
728 begin
729 open c_fullpath_ids( category_id );
730 fetch c_fullpath_ids bulk collect into l_catids_arr;
731 close c_fullpath_ids;
732
733 if( l_catids_arr.count >= 1 ) then
734 fullpath_ids := l_catids_arr(1);
735 for i in 2..l_catids_arr.count loop
736 fullpath_ids := fullpath_ids || separator || l_catids_arr(i);
737 end loop;
738 end if;
739 return fullpath_ids;
740 end;
741
742 function has_pub_wip_descendents( category_id number )
743 return varchar2 is
744 cursor get_descendents(cp_category_id number)
745 is
746 SELECT CATEGORY_ID
747 FROM cs_kb_soln_categories_b
748 start with CATEGORY_ID = cp_category_id
749 connect by prior CATEGORY_ID = PARENT_CATEGORY_ID;
750
751 cursor get_pub_inprog(cp_category_id number)
752 is
753 select /*+ index(sl) */ b.set_id
754 from cs_kb_set_categories sl, cs_kb_sets_b b
755 where sl.category_id = cp_category_id
756 and b.set_id = sl.set_id
757 and (b.status = 'PUB' or (b.status <> 'OBS' and b.latest_version_flag = 'Y'));
758
759 l_found varchar2(1) := 'N';
760
761 begin
762
763 <<descendent_loop>>
764 for x in get_descendents(category_id) loop
765 for y in get_pub_inprog(x.category_id) loop
766 l_found := 'Y';
767 exit descendent_loop;
768 end loop;
769 end loop;
770 return l_found;
771 end;
772
773 END CS_KB_SOLN_CATEGORIES_PVT;