[Home] [Help]
PACKAGE BODY: APPS.CS_KB_CTX_PKG
Source
1 PACKAGE BODY cs_kb_ctx_pkg AS
2 /* $Header: cskbdstb.pls 120.0 2005/08/16 13:33 alawang noship $ */
3
4 -- *********************************
5 -- Private Procedure Declarations
6 -- *********************************
7
8
9 PROCEDURE Single_Synthesize_Set_Content
10 (p_solution_id IN NUMBER,
11 p_lang IN VARCHAR2,
12 p_soln_number IN VARCHAR2,
13 p_set_type_id IN NUMBER,
14 p_clob IN OUT NOCOPY CLOB
15
16 );
17
18
19 PROCEDURE Write_Soln_Header_Cont_Hlp
20 ( p_solution_id IN NUMBER,
21 p_lang IN VARCHAR2,
22 p_clob IN OUT NOCOPY CLOB,
23 p_temp_clob IN OUT NOCOPY CLOB);
24
25
26 PROCEDURE Write_Soln_Header_Sect_Hlp
27 ( p_solution_id IN NUMBER,
28 p_lang IN VARCHAR2,
29 p_clob IN OUT NOCOPY CLOB);
30
31
32 PROCEDURE Write_Soln_Category_Sect_Hlp
33 ( p_solution_id IN NUMBER,
34 p_clob IN OUT NOCOPY CLOB);
35
36
37 PROCEDURE Write_Soln_Product_Cont_Hlp
38 ( p_solution_id IN NUMBER,
39 p_lang IN VARCHAR2,
40 p_clob IN OUT NOCOPY CLOB,
41 p_temp_clob IN OUT NOCOPY CLOB);
42
43
44 PROCEDURE Write_Soln_Product_Sect_Hlp
45 ( p_solution_id IN NUMBER,
46 p_clob IN OUT NOCOPY CLOB);
47
48
49 PROCEDURE Write_Soln_Platform_Cont_Hlp
50 ( p_solution_id IN NUMBER,
51 p_lang IN VARCHAR2,
52 p_clob IN OUT NOCOPY CLOB,
53 p_temp_clob IN OUT NOCOPY CLOB);
54
55 PROCEDURE Write_Soln_Platform_Sect_Hlp
56 ( p_solution_id IN NUMBER,
57 p_clob IN OUT NOCOPY CLOB);
58
59 PROCEDURE Write_Soln_CatGrp_Sect_Hlp
60 ( p_solution_id IN NUMBER,
61 p_clob IN OUT NOCOPY CLOB);
62
63
64 PROCEDURE Write_Soln_Statement_Cont_Hlp
65 ( p_solution_id IN NUMBER,
66 p_lang IN VARCHAR2,
67 p_clob IN OUT NOCOPY CLOB,
68 p_temp_clob IN OUT NOCOPY CLOB);
69
70 PROCEDURE Write_Soln_Statement_Sect_Hlp
71 ( p_solution_id IN NUMBER,
72 p_clob IN OUT NOCOPY CLOB);
73
74 PROCEDURE Write_Stmt_Header_Cont_Hlp
75 ( p_statement_id IN NUMBER,
76 p_lang IN VARCHAR2,
77 p_clob IN OUT NOCOPY CLOB,
78 p_temp_clob IN OUT NOCOPY CLOB,
79 p_statement_number IN VARCHAR2,
80 p_name IN VARCHAR2,
81 p_description IN CLOB);
82
83 PROCEDURE Write_Stmt_Header_Sect_Hlp
84 ( p_statement_id IN NUMBER,
85 p_lang IN VARCHAR2,
86 p_clob IN OUT NOCOPY CLOB,
87 p_statement_number IN VARCHAR2,
88 p_type_id IN NUMBER,
89 p_access_level IN NUMBER);
90
91 PROCEDURE Write_Stmt_CatGrp_Sect_Hlp
92 ( p_statement_id IN NUMBER,
93 p_clob IN OUT NOCOPY CLOB);
94
95 -- 3341248
96 PROCEDURE Write_Related_Stmt_Sect_Hlp
97 ( p_statement_id IN NUMBER,
98 p_clob IN OUT NOCOPY CLOB);
99
100
101
102 -- ********************************
103 -- Public Procedure Implementations
104 -- ********************************
105
106 PROCEDURE Get_Composite_Elements
107 ( p_rowid IN ROWID, p_clob IN OUT NOCOPY CLOB )
108 IS
109 l_solnid NUMBER;
110 l_lang VARCHAR2(4);
111 l_status VARCHAR2(30);
112 l_clob CLOB := NULL;
113 l_clob_len NUMBER;
114
115 --(perf11510)
116 CURSOR set_content_csr(p_rowid IN ROWID) IS
117 SELECT tl.set_id,
118 tl.LANGUAGE,
119 b.status,
120 tl.content_cache,
121 b.set_number,
122 b.set_type_id
123 FROM CS_KB_SETS_TL tl, CS_KB_SETS_B b
124 WHERE tl.ROWID = p_rowid
125 AND b.set_id = tl.set_id;
126
127 l_set_number VARCHAR2(30);
128 l_set_type_id NUMBER;
129 BEGIN
130
131 --(perf11510)
132 /*
133 -- Get the solution id and language, based on the rowid
134 select tl.set_id, tl.language, b.status, tl.content_cache
135 into l_solnid, l_lang, l_status, l_clob
136 from CS_KB_SETS_TL tl, CS_KB_SETS_B b
137 where tl.rowid = p_rowid
138 and b.set_id = tl.set_id;
139 */
140 OPEN set_content_csr(p_rowid);
141 FETCH set_content_csr
142 INTO l_solnid
143 ,l_lang
144 ,l_status
145 ,l_clob
146 ,l_set_number
147 ,l_set_type_id;
148
149 CLOSE set_content_csr;
150 -- end (perf11510)
151
152
153 -- Index only Published Solutions.
154 IF ( l_status = 'PUB' )
155 THEN
156 -- If the solution content cache is populated, then use it
157 -- for the indexed content.
158 IF (l_clob IS NOT NULL AND dbms_lob.getlength(l_clob) > 0)
159 THEN
160 -- Clear out the output CLOB buffer
161 dbms_lob.trim(p_clob, 0);
162
163 -- Copy content cache into output CLOB buffer for indexing
164 l_clob_len := dbms_lob.getlength(l_clob);
165 dbms_lob.copy(p_clob, l_clob, l_clob_len, 1, 1);
166 ELSE
167 -- Call procedure to return synthesized solution content
168 -- for indexing. CLOB is passed by reference and sub
169 -- procedures will change the CLOB directly.
170 -- Synthesize_Solution_Content( l_solnid, l_lang, p_clob );
171 Single_Synthesize_Set_Content( l_solnid
172 ,l_lang
173 ,l_set_number
174 ,l_set_type_id
175 ,p_clob);
176
177 END IF;
178
179 -- Append the solution security information to the indexable
180 -- content at index time.
181 -- Note: neither the content cache, nor the call to Synthesize_
182 -- Solution_Content() includes the security section.
183 Write_Soln_CatGrp_Sect_Hlp( l_solnid, p_clob );
184
185 END IF;
186 END Get_Composite_Elements;
187
188 --(perf11510)
189 PROCEDURE Single_Synthesize_Set_Content
190 ( p_solution_id IN NUMBER,
191 p_lang IN VARCHAR2,
192 p_soln_number IN VARCHAR2,
193 p_set_type_id IN NUMBER,
194 p_clob IN OUT NOCOPY CLOB)
195 IS
196 l_temp_clob CLOB;
197 l_amt BINARY_INTEGER;
198 l_newline VARCHAR2(4) := fnd_global.newline;
199 l_data VARCHAR2(32000);
200
201 CURSOR set_title_csr(p_set_id NUMBER, p_lang VARCHAR2) IS
202 SELECT name
203 FROM Cs_Kb_Sets_tl
204 WHERE set_id = p_set_id
205 AND LANGUAGE = p_lang;
206 l_soln_title VARCHAR2(2000);
207
208 l_sections VARCHAR2(32000);
209 l_content VARCHAR2(32000);
210
211 CURSOR c1(c_setid IN NUMBER, c_lang IN VARCHAR2) IS
212 SELECT et.name, et.description, eb.element_id, eb.element_number
213 FROM CS_KB_ELEMENTS_B eb,
214 CS_KB_ELEMENTS_TL et,
215 CS_KB_SET_ELES se
216 WHERE se.set_id = c_setid
217 AND eb.element_id = se.element_id
218 AND eb.element_id = et.element_id
219 AND eb.status = 'PUBLISHED' --- added 03/16/2004
220 AND et.LANGUAGE = c_lang;
221
222 rec1 c1%ROWTYPE;
223
224 CURSOR c6(c_set_id IN NUMBER, c_lang IN VARCHAR2) IS
225 SELECT b.segment1 name,t.description, s.product_id
226 FROM mtl_system_items_b b, mtl_system_items_tl t, cs_kb_set_products s
227 WHERE t.inventory_item_id = s.product_id
228 AND t.organization_id = s.product_org_id
229 AND b.inventory_item_id = s.product_id
230 AND b.organization_id = s.product_org_id
231 AND t.LANGUAGE = c_lang
232 AND s.set_id = c_set_id;
233
234 CURSOR c7(c_set_id IN NUMBER, c_lang IN VARCHAR2) IS
235 SELECT b.segment1 name,t.description, s.platform_id
236 FROM mtl_system_items_b b, mtl_system_items_tl t, cs_kb_set_platforms s
237 WHERE t.inventory_item_id = s.platform_id
238 AND t.organization_id = s.platform_org_id
239 AND b.inventory_item_id = s.platform_id
240 AND b.organization_id = s.platform_org_id
241 AND t.LANGUAGE = c_lang
242 AND s.set_id = c_set_id;
243
244 CURSOR c5(c_set_id IN NUMBER) IS
245 SELECT DISTINCT category_id
246 FROM CS_KB_SOLN_CATEGORIES_B
247 START WITH category_id IN
248 (
249 SELECT category_id
250 FROM cs_kb_set_categories
251 WHERE set_id = c_set_id
252 )
253 CONNECT BY PRIOR parent_category_id = category_id;
254
255 l_clob CLOB := NULL;
256 l_clob_len NUMBER;
257 p_clob_len NUMBER;
258
259 l_stmt_name VARCHAR2(32000) := '';
260 empty_flag BOOLEAN := TRUE;
261 BEGIN
262 -- temp clob lives for at most the duration of call.
263 dbms_lob.createtemporary(l_temp_clob, TRUE, dbms_lob.call);
264
265 DBMS_LOB.OPEN(l_temp_clob,DBMS_LOB.LOB_READWRITE);
266
267 -- Clear out the output CLOB buffer
268 dbms_lob.trim(p_clob, 0);
269
270 -- write the solution number to clob
271 l_data := l_newline||p_soln_number;
272
273 -- write solution title to clob
274 OPEN set_title_csr(p_solution_id, p_lang);
275 FETCH set_title_csr INTO l_soln_title;
276 CLOSE set_title_csr;
277
278 l_data := l_data||' '||l_soln_title||l_newline;
279 l_data := Remove_Tags(l_data);
280 l_amt := LENGTH(l_data);
281
282 dbms_lob.writeappend(p_clob, l_amt, l_data);
283
284 -- Construct the header sections
285 -- write solution type id
286 l_sections := l_newline||'<TYPE>a' || TO_CHAR(p_set_type_id) || 'a</TYPE> ';
287
288 -- write language code
289 l_sections := l_sections||l_newline||'<LANG>a'|| p_lang ||'a</LANG>';
290
291 -- write solution number
292 l_sections := l_sections|| l_newline||'<NUMBER>a' || p_soln_number
293 || 'a</NUMBER>';
294
295 -- For each Statement linked to the solution header,
296 -- write all of the Statement content (summary, description)
297 -- into the clob.
298 l_sections := l_sections || l_newline||'<STATEMENTS>';
299
300 l_stmt_name := '';
301
302 FOR rec1 IN c1(p_solution_id, p_lang) LOOP
303
304 -- Write the statement summary to clob
305 l_stmt_name := l_stmt_name||rec1.name||l_newline;
306 l_amt := LENGTH(l_stmt_name);
307 IF l_amt >= 31000 THEN
308 -- flush l_stmt_name to the p_clob
309 l_stmt_name := Remove_Tags(l_stmt_name);
310 dbms_lob.writeappend(p_clob, l_amt, l_stmt_name);
311 l_stmt_name := l_newline;
312 END IF;
313
314 -- Write the statement description to clob
315 l_clob := rec1.description;
316 IF (l_clob IS NOT NULL AND dbms_lob.getlength(l_clob) > 0)
317 THEN
318 l_clob := Remove_Tags_Clob(l_clob, l_temp_clob);
319 l_clob_len := dbms_lob.getlength(l_clob);
320 p_clob_len := dbms_lob.getlength(p_clob);
321 dbms_lob.copy(p_clob, l_clob, l_clob_len, p_clob_len+1, 1);
322 END IF;
323
324 -- Repeat each statement id for 10 times.
325 -- Need revisit in 115.x. See issue #1309
326 FOR i IN 1..10 LOOP
327 l_sections := l_sections||' a'||rec1.element_id||'a ';
328 END LOOP;
329 END LOOP;
330
331 l_amt := LENGTH(l_stmt_name);
332 IF l_amt > 0 THEN
333 l_stmt_name := Remove_Tags(l_stmt_name);
334 dbms_lob.writeappend(p_clob, l_amt, l_stmt_name);
335 END IF;
336
337 l_sections := l_sections || '</STATEMENTS>';
338
339 -- write category section
340 l_sections := l_sections|| l_newline||'<CATEGORIES>';
341 FOR rec5 IN c5(p_solution_id) LOOP
342 l_sections := l_sections || ' a' || TO_CHAR(rec5.category_id) || 'a ';
343 END LOOP;
344
345 l_sections := l_sections||'</CATEGORIES>' ;
346
347 --write product name and description to clob
348 l_sections := l_sections || l_newline||'<PRODUCTS>';
349
350 l_data := '';
351 l_amt := 0;
352
353 -- reset empty_flag
354 empty_flag := TRUE;
355 FOR rec6 IN c6(p_solution_id, p_lang) LOOP
356 l_data := l_data||l_newline||rec6.name||' '||rec6.description;
357 l_sections := l_sections||l_newline||' a'||TO_CHAR(rec6.product_id)||'a ';
358 empty_flag := FALSE;
359 END LOOP;
360
361 IF empty_flag THEN
362 -- write generice platforms
363 l_sections := l_sections || 'a000a';
364 END IF;
365 l_sections := l_sections || '</PRODUCTS>';
366
367 l_sections := l_sections || l_newline||'<PLATFORMS>';
368
369 -- reset empty_flag
370 empty_flag := TRUE;
371 FOR rec7 IN c7(p_solution_id, p_lang) LOOP
372 l_data := l_data||l_newline||rec7.name||' '||rec7.description;
373 l_sections := l_sections||' a'||TO_CHAR(rec7.platform_id) ||'a ';
374 empty_flag := FALSE;
375 END LOOP;
376
377 IF empty_flag THEN
378 -- write generice platforms
379 l_sections := l_sections || 'a000a';
380 END IF;
381
382 l_sections := l_sections || '</PLATFORMS>';
383
384 l_data := Remove_Tags(l_data);
385
386 l_amt := LENGTH(l_data);
387 IF ( l_amt > 0 )
388 THEN
389 dbms_lob.writeappend(p_clob, l_amt, l_data);
390 END IF;
391
392 l_amt := LENGTH(l_sections);
393 IF ( l_amt > 0 )
394 THEN
395 dbms_lob.writeappend(p_clob, l_amt, l_sections);
396 END IF;
397
398 DBMS_LOB.CLOSE(l_temp_clob);
399 dbms_lob.freetemporary(l_temp_clob);
400
401 END Single_Synthesize_Set_Content;
402 -- end (perf11510)
403
404 PROCEDURE Build_Elements
405 (p_rowid IN ROWID, p_clob IN OUT NOCOPY CLOB)
406 IS
407 l_statement_id NUMBER;
408 l_lang VARCHAR2(5);
409 l_status VARCHAR2(30);
410
414 l_access_level NUMBER;
411 l_statement_number VARCHAR2(30);
412 l_name VARCHAR2(2000);
413 l_description CLOB := NULL;
415 l_type_id NUMBER;
416
417 l_temp_clob CLOB;
418
419 CURSOR GET_STMT_CONTENT IS
420 SELECT tl.element_id, tl.LANGUAGE, b.status,
421 b.element_number, tl.name, tl.description,
422 b.access_level, b.element_type_id
423 FROM CS_KB_ELEMENTS_TL tl, CS_KB_ELEMENTS_B b
424 WHERE tl.ROWID = p_rowid
425 AND tl.element_id = b.element_id;
426 BEGIN
427 -- Fetch statement id, language, and status based on rowid
428 -- Bug 3455203 - Perf Changes: Select all info in one cursor
429 -- and pass down to other apis
430
431 OPEN GET_STMT_CONTENT;
432 FETCH GET_STMT_CONTENT INTO l_statement_id, l_lang, l_status,
433 l_statement_number, l_name, l_description,
434 l_access_level, l_type_id;
435 CLOSE GET_STMT_CONTENT;
436
437
438 -- Index only Published statements
439 IF ( l_status = 'PUBLISHED' )
440 THEN
441 -- Call procedure to return synthesized statement content
442 -- for indexing. CLOB is passed by reference and sub
443 -- procedures will change the CLOB directly.
444 --Synthesize_Statement_Content( l_statement_id, l_lang, p_clob );
445 -- Bug 3455203 - Perf Changes:
446 -- Consolidated code from Synthesize_Statement_Content to here:
447
448 -- temp clob lives for at most the duration of call.
449 dbms_lob.createtemporary(l_temp_clob, TRUE, dbms_lob.call);
450 -- Clear out the output CLOB buffer
451 dbms_lob.trim(p_clob, 0);
452
453 -- Write out the statement text content
454 Write_Stmt_Header_Cont_Hlp(l_statement_id, l_lang, p_clob, l_temp_clob,
455 l_statement_number, l_name, l_description);
456 -- Write out metadata sections
457 Write_Stmt_Header_Sect_Hlp(l_statement_id, l_lang, p_clob,
458 l_statement_number, l_type_id, l_access_level);
459 -- explicitly free the clob
460 dbms_lob.freetemporary(l_temp_clob);
461 --
462
463 -- Append the statement security information into the index
464 -- at index time.
465 Write_Stmt_CatGrp_Sect_Hlp(l_statement_id, p_clob);
466
467 -- 3341248: Append the related statements information
468 Write_Related_Stmt_Sect_Hlp(l_statement_id, p_clob);
469
470 END IF;
471 END Build_Elements;
472
473
474 PROCEDURE Synthesize_Solution_Content
475 ( p_solution_id IN NUMBER,
476 p_lang IN VARCHAR2,
477 p_clob IN OUT NOCOPY CLOB)
478 IS
479 l_temp_clob CLOB;
480
481 BEGIN
482
483 -- temp clob lives for at most the duration of call.
484 dbms_lob.createtemporary(l_temp_clob, TRUE, dbms_lob.call);
485
486 -- Clear out the output CLOB buffer
487 dbms_lob.trim(p_clob, 0);
488
489 -- Write out the solution text content
490 Write_Soln_Header_Cont_Hlp( p_solution_id, p_lang, p_clob, l_temp_clob );
491 Write_Soln_Statement_Cont_Hlp( p_solution_id, p_lang, p_clob, l_temp_clob );
492 Write_Soln_Product_Cont_Hlp( p_solution_id, p_lang, p_clob, l_temp_clob );
493 Write_Soln_Platform_Cont_Hlp( p_solution_id, p_lang, p_clob, l_temp_clob );
494
495 -- Write out metadata sections
496 Write_Soln_Header_Sect_Hlp( p_solution_id, p_lang, p_clob );
497 Write_Soln_Statement_Sect_Hlp( p_solution_id, p_clob );
498 Write_Soln_Category_Sect_Hlp( p_solution_id, p_clob );
499 Write_Soln_Product_Sect_Hlp( p_solution_id, p_clob );
500 Write_Soln_Platform_Sect_Hlp( p_solution_id, p_clob );
501
502 -- explicitly free the clob
503 dbms_lob.freetemporary(l_temp_clob);
504 END Synthesize_Solution_Content;
505
506 -- procedure Synthesize_Statement_Content
507 -- ( p_statement_id IN NUMBER,
508 -- p_lang IN VARCHAR2,
509 -- p_clob IN OUT NOCOPY CLOB)
510 -- is
511 -- l_temp_clob CLOB;
512 -- begin
513 --
514 -- -- temp clob lives for at most the duration of call.
515 -- dbms_lob.createtemporary(l_temp_clob, TRUE, dbms_lob.call);
516 --
517 -- -- Clear out the output CLOB buffer
518 -- dbms_lob.trim(p_clob, 0);
519 --
520 -- -- Write out the statement text content
521 -- Write_Stmt_Header_Cont_Hlp(p_statement_id, p_lang, p_clob, l_temp_clob);
522 --
523 -- -- Write out metadata sections
524 -- Write_Stmt_Header_Sect_Hlp(p_statement_id, p_lang, p_clob);
525 --
526 -- -- explicitly free the clob
527 -- dbms_lob.freetemporary(l_temp_clob);
528 --
529 -- end Synthesize_Statement_Content;
530
531
532
533 -- *********************************
534 -- Private Procedure Implementations
535 -- *********************************
536
537 PROCEDURE Write_Soln_Header_Cont_Hlp
538 ( p_solution_id IN NUMBER,
539 p_lang IN VARCHAR2,
540 p_clob IN OUT NOCOPY CLOB,
541 p_temp_clob IN OUT NOCOPY CLOB)
542 IS
543 CURSOR c2(c_set_id IN NUMBER, c_lang IN VARCHAR2) IS
547 AND tl.set_id = b.set_id
544 SELECT b.set_number, tl.name
545 FROM CS_KB_SETS_B b, CS_KB_SETS_TL tl
546 WHERE b.set_id = c_set_id
548 AND tl.LANGUAGE = c_lang;
549
550 l_soln_title VARCHAR2(2000);
551 l_soln_number VARCHAR2(30);
552 l_data VARCHAR2(2000);
553 l_amt BINARY_INTEGER;
554 l_newline VARCHAR2(4) := fnd_global.newline;
555 BEGIN
556 -- Fetch the solution header
557 OPEN c2(p_solution_id, p_lang);
558 FETCH c2 INTO l_soln_number, l_soln_title;
559 CLOSE c2;
560
561 -- write the solution number to clob
562 l_data := l_newline||l_soln_number;
563 l_amt := LENGTH(l_data);
564 dbms_lob.writeappend(p_clob, l_amt, l_data);
565
566 -- write solution title to clob
567 l_data := ' '||l_soln_title;
568 l_data := Remove_Tags(l_data); --, p_temp_clob);
569 l_amt := LENGTH(l_data);
570 dbms_lob.writeappend(p_clob, l_amt, l_data);
571
572 END Write_Soln_Header_Cont_Hlp;
573
574
575 PROCEDURE Write_Soln_Header_Sect_Hlp
576 ( p_solution_id IN NUMBER,
577 p_lang IN VARCHAR2,
578 p_clob IN OUT NOCOPY CLOB)
579 IS
580 CURSOR c2(c_set_id IN NUMBER) IS
581 SELECT set_type_id, set_number
582 FROM CS_KB_SETS_B
583 WHERE set_id = c_set_id;
584
585 l_soln_number VARCHAR2(30);
586 l_type_id NUMBER;
587 l_data VARCHAR2(2000);
588 l_amt BINARY_INTEGER;
589 l_newline VARCHAR2(4) := fnd_global.newline;
590 BEGIN
591
592 OPEN c2(p_solution_id);
593 FETCH c2 INTO l_type_id, l_soln_number;
594 CLOSE c2;
595
596 -- write solution type id
597 l_data := l_newline||'<TYPE>a' || TO_CHAR(l_type_id) || 'a</TYPE> ';
598 l_amt := LENGTH(l_data);
599 dbms_lob.writeappend(p_clob, l_amt, l_data);
600
601 -- write language code
602 l_data := l_newline||'<LANG>a'|| p_lang ||'a</LANG>';
603 l_amt := LENGTH(l_data);
604 dbms_lob.writeappend(p_clob, l_amt, l_data);
605
606 -- write solution number
607 l_data := l_newline||'<NUMBER>a' || l_soln_number || 'a</NUMBER>';
608 l_amt := LENGTH(l_data);
609 dbms_lob.writeappend(p_clob, l_amt, l_data);
610
611 END Write_Soln_Header_Sect_Hlp;
612
613
614 PROCEDURE Write_Soln_Category_Sect_Hlp
615 ( p_solution_id IN NUMBER,
616 p_clob IN OUT NOCOPY CLOB)
617 IS
618 CURSOR c5(c_set_id IN NUMBER) IS
619 SELECT DISTINCT category_id
620 FROM CS_KB_SOLN_CATEGORIES_B
621 START WITH category_id IN
622 (
623 SELECT category_id
624 FROM cs_kb_set_categories
625 WHERE set_id = c_set_id
626 )
627 CONNECT BY PRIOR parent_category_id = category_id;
628
629 l_data VARCHAR2(32000);
630 l_amt BINARY_INTEGER;
631 l_category_id NUMBER;
632 l_newline VARCHAR2(4) := fnd_global.newline;
633 BEGIN
634 -- write category section
635
636 l_data := l_newline||'<CATEGORIES>';
637 l_amt := LENGTH(l_data);
638 dbms_lob.writeappend(p_clob, l_amt, l_data);
639
640 l_data := '';
641 l_amt := 0;
642
643 FOR rec5 IN c5(p_solution_id) LOOP
644 l_category_id := rec5.category_id;
645 l_data := l_data || l_newline || ' a' || TO_CHAR(l_category_id) || 'a ';
646 END LOOP;
647
648 l_amt := LENGTH(l_data);
649 IF ( l_amt > 0 )
650 THEN
651 dbms_lob.writeappend(p_clob, l_amt, l_data);
652 END IF;
653
654 l_data := '</CATEGORIES>';
655 l_amt := LENGTH(l_data);
656 dbms_lob.writeappend(p_clob, l_amt, l_data);
657 END Write_Soln_Category_Sect_Hlp;
658
659
660
661 PROCEDURE Write_Soln_Product_Cont_Hlp
662 ( p_solution_id IN NUMBER,
663 p_lang IN VARCHAR2,
664 p_clob IN OUT NOCOPY CLOB,
665 p_temp_clob IN OUT NOCOPY CLOB)
666 IS
667 CURSOR c6(c_set_id IN NUMBER, c_lang IN VARCHAR2) IS
668 SELECT b.segment1 name,t.description
669 FROM mtl_system_items_b b, mtl_system_items_tl t, cs_kb_set_products s
670 WHERE t.inventory_item_id = s.product_id
671 AND t.organization_id = s.product_org_id
672 AND b.inventory_item_id = s.product_id
673 AND b.organization_id = s.product_org_id
674 AND t.LANGUAGE = c_lang
675 AND s.set_id = c_set_id;
676 l_data VARCHAR2(32000);
677 l_amt BINARY_INTEGER;
678 l_newline VARCHAR2(4) := fnd_global.newline;
679 BEGIN
680 --write product name and description to clob
684 FOR rec6 IN c6(p_solution_id, p_lang) LOOP
681 l_data := '';
682 l_amt := 0;
683
685 l_data := l_data||l_newline||rec6.name||' '||rec6.description;
686 END LOOP;
687
688 l_data := Remove_Tags(l_data); --, p_temp_clob);
689
690 l_amt := LENGTH(l_data);
691 IF ( l_amt > 0 )
692 THEN
693 dbms_lob.writeappend(p_clob, l_amt, l_data);
694 END IF;
695
696 END Write_Soln_Product_Cont_Hlp;
697
698
699 PROCEDURE Write_Soln_Product_Sect_Hlp
700 ( p_solution_id IN NUMBER,
701 p_clob IN OUT NOCOPY CLOB)
702 IS
703 CURSOR c3(c_set_id IN NUMBER) IS
704 SELECT product_id, product_org_id
705 FROM CS_KB_SET_PRODUCTS
706 WHERE set_id = c_set_id;
707 l_data VARCHAR2(32000);
708 l_amt BINARY_INTEGER;
709 l_setid NUMBER;
710 l_product_id NUMBER;
711 l_product_org_id NUMBER;
712 rec3 c3%ROWTYPE;
713 l_newline VARCHAR2(4) := fnd_global.newline;
714 BEGIN
715 -- write the start product section info
716
717 l_data := l_newline||'<PRODUCTS>';
718 l_amt := LENGTH(l_data);
719 dbms_lob.writeappend(p_clob, l_amt, l_data);
720
721 l_data := '';
722 l_amt := 0;
723 FOR rec3 IN c3(p_solution_id) LOOP
724 l_product_id := rec3.product_id;
725 l_product_org_id := rec3.product_org_id;
726
727 l_data := l_data||l_newline||' a'||TO_CHAR(l_product_id)||'a ';
728 END LOOP;
729
730 l_amt := LENGTH(l_data);
731 IF ( l_amt > 0 )
732 THEN
733 dbms_lob.writeappend(p_clob, l_amt, l_data);
734 ELSE
735 l_data := 'a000a';
736 dbms_lob.writeappend(p_clob, 5, l_data);
737 END IF;
738
739 l_data := '</PRODUCTS>';
740 l_amt := LENGTH(l_data);
741 dbms_lob.writeappend(p_clob, l_amt, l_data);
742 END Write_Soln_Product_Sect_Hlp;
743
744
745
746 PROCEDURE Write_Soln_Platform_Cont_Hlp
747 ( p_solution_id IN NUMBER,
748 p_lang IN VARCHAR2,
749 p_clob IN OUT NOCOPY CLOB,
750 p_temp_clob IN OUT NOCOPY CLOB)
751 IS
752 CURSOR c7(c_set_id IN NUMBER, c_lang IN VARCHAR2) IS
753 SELECT b.segment1 name,t.description
754 FROM mtl_system_items_b b, mtl_system_items_tl t, cs_kb_set_platforms s
755 WHERE t.inventory_item_id = s.platform_id
756 AND t.organization_id = s.platform_org_id
757 AND b.inventory_item_id = s.platform_id
758 AND b.organization_id = s.platform_org_id
759 AND t.LANGUAGE = c_lang
760 AND s.set_id = c_set_id;
761 l_data VARCHAR2(32000);
762 l_amt BINARY_INTEGER;
763 l_newline VARCHAR2(4) := fnd_global.newline;
764 BEGIN
765 --write platform name and description to clob
766 l_data := '';
767 l_amt := 0;
768 FOR rec7 IN c7(p_solution_id, p_lang) LOOP
769 l_data := l_data||l_newline||rec7.name||' '||rec7.description;
770 END LOOP;
771
772 l_data := Remove_Tags(l_data); --, p_temp_clob);
773
774 l_amt := LENGTH(l_data);
775 IF ( l_amt > 0 )
776 THEN
780
777 dbms_lob.writeappend(p_clob, l_amt, l_data);
778 END IF;
779 END Write_Soln_Platform_Cont_Hlp;
781
782 PROCEDURE Write_Soln_Platform_Sect_Hlp
783 ( p_solution_id IN NUMBER,
784 p_clob IN OUT NOCOPY CLOB)
785 IS
786 CURSOR c4(c_set_id IN NUMBER) IS
787 SELECT platform_id, platform_org_id
788 FROM CS_KB_SET_PLATFORMS
789 WHERE set_id = c_set_id;
790 l_data VARCHAR2(32000);
791 l_amt BINARY_INTEGER;
792 l_platform_id NUMBER;
793 l_platform_org_id NUMBER;
794 rec4 c4%ROWTYPE;
795 l_newline VARCHAR2(4) := fnd_global.newline;
796 BEGIN
797 -- write the start platform section info
798
799 l_data := l_newline||'<PLATFORMS>';
800 l_amt := LENGTH(l_data);
801 dbms_lob.writeappend(p_clob, l_amt, l_data);
802
803 l_data := '';
804 l_amt := 0;
805
806 FOR rec4 IN c4(p_solution_id) LOOP
807 l_platform_id := rec4.platform_id;
808 l_platform_org_id := rec4.platform_org_id;
809
810 l_data := l_data||' a'||TO_CHAR(l_platform_id) ||'a ';
811 END LOOP;
812
813 l_amt := LENGTH(l_data);
814 IF ( l_amt > 0 )
815 THEN
816 dbms_lob.writeappend(p_clob, l_amt, l_data);
817 ELSE
818 l_data := 'a000a';
819 dbms_lob.writeappend(p_clob, 5, l_data);
820 END IF;
821
822 l_data := '</PLATFORMS>';
823 l_amt := LENGTH(l_data);
824 dbms_lob.writeappend(p_clob, l_amt, l_data);
825 END Write_Soln_Platform_Sect_Hlp;
826
827
828 PROCEDURE Write_Soln_CatGrp_Sect_Hlp
829 ( p_solution_id IN NUMBER,
830 p_clob IN OUT NOCOPY CLOB)
831 IS
832 CURSOR c4(c_set_id IN NUMBER) IS
833 SELECT UNIQUE b.category_group_id
834 FROM cs_kb_set_categories a, CS_KB_CAT_GROUP_DENORM b
835 WHERE a.category_id = b.child_category_id
836 AND a.set_id = c_set_id;
837
838 CURSOR c5(c_position IN NUMBER) IS
839 SELECT visibility_id FROM cs_kb_visibilities_b
840 -- (secure) klou
841 WHERE position <= c_position
842 ORDER BY visibility_id;
843
844 l_data VARCHAR2(32000);
845 l_amt BINARY_INTEGER;
846 l_max_cat_vis NUMBER;
847 l_soln_vis NUMBER;
848 l_vis NUMBER;
849 rec4 c4%ROWTYPE;
850 rec5 c5%ROWTYPE;
851 l_newline VARCHAR2(4) := fnd_global.newline;
852 BEGIN
853 -- write the start category group section info
854
855 l_data := l_newline||'<CATEGORYGROUPS>';
856 l_amt := LENGTH(l_data);
857 dbms_lob.writeappend(p_clob, l_amt, l_data);
858
859 l_data := '';
860 l_amt := 0;
861
862 FOR rec4 IN c4(p_solution_id) LOOP
863 SELECT MAX(b.visibility_position)
864 INTO l_max_cat_vis
865 FROM cs_kb_set_categories a, CS_KB_CAT_GROUP_DENORM b
866 WHERE a.category_id = b.child_category_id
867 AND b.category_group_id = rec4.category_group_id
868 AND a.set_id = p_solution_id;
869
870 SELECT b.position
871 INTO l_soln_vis
872 FROM cs_kb_sets_b a, cs_kb_visibilities_b b
873 WHERE a.visibility_id = b.visibility_id
874 AND a.set_id = p_solution_id;
875
876 IF l_soln_vis < l_max_cat_vis THEN
877 l_vis := l_soln_vis;
878 ELSE
879 l_vis := l_max_cat_vis;
880 END IF;
881
882 FOR rec5 IN c5(l_vis) LOOP
883 l_data := l_data||' '||TO_CHAR(rec4.category_group_id)||'a' ||TO_CHAR(rec5.visibility_id)||' ';
884 END LOOP;
885 END LOOP;
886
887 l_amt := LENGTH(l_data);
888 IF ( l_amt > 0 )
889 THEN
890 dbms_lob.writeappend(p_clob, l_amt, l_data);
891 END IF;
892
893 l_data := '</CATEGORYGROUPS>';
894 l_amt := LENGTH(l_data);
895 dbms_lob.writeappend(p_clob, l_amt, l_data);
896 END Write_Soln_CatGrp_Sect_Hlp;
897
898
899 PROCEDURE Write_Soln_Statement_Cont_Hlp
900 ( p_solution_id IN NUMBER,
901 p_lang IN VARCHAR2,
902 p_clob IN OUT NOCOPY CLOB,
903 p_temp_clob IN OUT NOCOPY CLOB)
904 IS
905 CURSOR c1(c_setid IN NUMBER, c_lang IN VARCHAR2) IS
906 SELECT et.name, et.description, eb.element_id, eb.element_number
907 FROM CS_KB_ELEMENTS_B eb,
908 CS_KB_ELEMENTS_TL et,
909 CS_KB_SET_ELES se
910 WHERE se.set_id = c_setid
911 AND eb.element_id = se.element_id
912 AND eb.element_id = et.element_id
913 AND eb.status = 'PUBLISHED' --- added 03/16/2004
914 AND et.LANGUAGE = c_lang;
915 rec1 c1%ROWTYPE;
916 l_data VARCHAR2(32000);
917 l_amt BINARY_INTEGER;
921 l_newline VARCHAR2(4) := fnd_global.newline;
918 l_clob CLOB := NULL;
919 l_clob_len NUMBER;
920 p_clob_len NUMBER;
922 BEGIN
923 -- For each Statement linked to the solution header,
924 -- write all of the Statement content (summary, description)
925 -- into the clob.
926 FOR rec1 IN c1(p_solution_id, p_lang) LOOP
927
928 -- Write the statement summary to clob
929 l_data := l_newline||rec1.name||l_newline;
930 l_data := Remove_Tags(l_data); --, p_temp_clob);
931 l_amt := LENGTH(l_data);
932
933 IF(l_amt>0) THEN
934 dbms_lob.writeappend(p_clob, l_amt, l_data);
935 END IF;
936
937 -- Write the statement description to clob
938 l_clob := rec1.description;
939
940 IF (l_clob IS NOT NULL AND dbms_lob.getlength(l_clob) > 0)
941 THEN
942 l_clob := Remove_Tags_Clob(l_clob, p_temp_clob);
943 l_clob_len := dbms_lob.getlength(l_clob);
944 p_clob_len := dbms_lob.getlength(p_clob);
945 dbms_lob.copy(p_clob, l_clob, l_clob_len, p_clob_len+1, 1);
946 END IF;
947 END LOOP;
948 END Write_Soln_Statement_Cont_Hlp;
949
950 PROCEDURE Write_Soln_Statement_Sect_Hlp
951 ( p_solution_id IN NUMBER,
952 p_clob IN OUT NOCOPY CLOB)
953 IS
954 CURSOR c1(c_setid IN NUMBER) IS
955 /* (perf11510): functional fix
956 select se.element_id
957 from CS_KB_SET_ELES se
958 where se.set_id = c_setid;
959 */
960 SELECT se.element_id
961 FROM CS_KB_SET_ELES se,
962 cs_kb_elements_b sb
963 WHERE se.set_id = c_setid
964 AND sb.element_id = se.element_id
965 AND sb.status = 'PUBLISHED';
966 l_data VARCHAR2(32000);
967 l_amt BINARY_INTEGER;
968 l_setid NUMBER;
969 l_lang VARCHAR2(4);
970 rec1 c1%ROWTYPE;
971 l_newline VARCHAR2(4) := fnd_global.newline;
972 BEGIN
973 -- Write out statement Section to statements section
974 l_data := l_newline||'<STATEMENTS>';
975 l_amt := LENGTH(l_data);
976 dbms_lob.writeappend(p_clob, l_amt, l_data);
977
978 l_data := '';
979 l_amt := 0;
980 FOR rec1 IN c1(p_solution_id) LOOP
981 FOR i IN 1..10 LOOP
982 l_data := l_data || ' a' || rec1.element_id || 'a ';
983 END LOOP;
984 END LOOP;
985
986 l_amt := LENGTH(l_data);
987 IF ( l_amt > 0 )
988 THEN
989 dbms_lob.writeappend(p_clob, l_amt, l_data);
990 END IF;
991
992 l_data := '</STATEMENTS>';
993 l_amt := LENGTH(l_data);
994 dbms_lob.writeappend(p_clob, l_amt, l_data);
995 END Write_Soln_Statement_Sect_Hlp;
996
997 PROCEDURE Write_Stmt_Header_Cont_Hlp
998 ( p_statement_id IN NUMBER,
999 p_lang IN VARCHAR2,
1000 p_clob IN OUT NOCOPY CLOB,
1001 p_temp_clob IN OUT NOCOPY CLOB,
1002 p_statement_number IN VARCHAR2,
1003 p_name IN VARCHAR2,
1004 p_description IN CLOB)
1005
1006 IS
1007 -- Bug 3455203 - Perf Changes:
1008 -- Name and Desc now passed as params to api
1009 -- cursor c1(c_element_id in number, c_lang in varchar2) is
1010 -- select b.element_number, tl.name, tl.description
1011 -- from cs_kb_elements_tl tl, cs_kb_elements_b b
1012 -- where tl.element_id = c_element_id
1013 -- and tl.language = c_lang
1014 -- and tl.element_id = b.element_id;
1015 -- rec1 c1%ROWTYPE;
1016 l_data VARCHAR2(2000);
1017 l_amt BINARY_INTEGER;
1018 l_newline VARCHAR2(4) := fnd_global.newline;
1019 -- l_statement_number varchar2(30);
1020 -- l_name varchar2(2000);
1021 l_description CLOB := NULL;
1022 l_clob_len NUMBER;
1023 p_clob_len NUMBER;
1024 BEGIN
1025 -- Fetch Statement summary and description
1026 -- open c1( p_statement_id, p_lang);
1027 -- fetch c1 into l_statement_number, l_name, l_description;
1028 -- close c1;
1029
1030 -- Write the statement number and summary to clob
1031 l_data := l_newline||p_statement_number||' '||p_name||l_newline;
1032 l_data := Remove_Tags(l_data); --, p_temp_clob);
1033 l_amt := LENGTH(l_data);
1034 dbms_lob.writeappend(p_clob, l_amt, l_data);
1035
1036 -- write statement description into clob
1037 l_description := p_description;
1038 IF (l_description IS NOT NULL AND dbms_lob.getlength(l_description) > 0)
1039 THEN
1040 l_description := Remove_Tags_Clob(l_description, p_temp_clob);
1041 l_clob_len := dbms_lob.getlength(l_description);
1042 p_clob_len := dbms_lob.getlength(p_clob);
1043 dbms_lob.copy(p_clob, l_description, l_clob_len, p_clob_len+1, 1);
1044 END IF;
1045 END Write_Stmt_Header_Cont_Hlp;
1046
1047
1048 PROCEDURE Write_Stmt_Header_Sect_Hlp
1049 ( p_statement_id IN NUMBER,
1050 p_lang IN VARCHAR2,
1051 p_clob IN OUT NOCOPY CLOB,
1052 p_statement_number IN VARCHAR2,
1053 p_type_id IN NUMBER,
1054 p_access_level IN NUMBER)
1055 IS
1056 -- Bug 3455203 - Perf Changes:
1057 -- Type and Number now passed as params to api
1058 -- cursor c1(c_element_id in number) is
1059 -- select element_type_id, element_number
1063 l_data VARCHAR2(2000);
1060 -- from cs_kb_elements_b
1061 -- where element_id = c_element_id;
1062 --rec1 c1%ROWTYPE;
1064 l_amt BINARY_INTEGER;
1065 --l_type_id number;
1066 l_newline VARCHAR2(4) := fnd_global.newline;
1067 --l_statement_number varchar(30);
1068
1069 -- Add access level section
1070 CURSOR access_levels_csr IS --(p_element_id in NUMBER) is
1071 SELECT lookup_code
1072 FROM cs_lookups
1073 WHERE lookup_type = 'CS_KB_ACCESS_LEVEL'
1074 AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(start_date_active, SYSDATE))
1075 AND TRUNC(NVL(end_date_active, SYSDATE))
1076 AND TO_NUMBER(lookup_code) <= p_access_level;
1077
1078 l_access_level access_levels_csr%ROWTYPE;
1079
1080 BEGIN
1081 -- Fetch Statement type info
1082 -- open c1( p_statement_id );
1083 -- fetch c1 into l_type_id, l_statement_number;
1084 -- close c1;
1085
1086 --write statement type and language section data into clob.
1087 l_data := l_newline||'<NUMBER>a' || p_statement_number || 'a</NUMBER>'||
1088 l_newline||'<TYPE>a'||TO_CHAR(p_type_id)||'a</TYPE>'||
1089 l_newline||'<LANG>a'||p_lang||'a</LANG>'
1090 --3341248
1091 ||l_newline||'<STATEMENTID>a'||p_statement_id||'a</STATEMENTID>'
1092 ;
1093 --end 3341248
1094
1095 -- Add access level
1096 l_data := l_data || l_newline||'<ACCESS> ';
1097 FOR l_access_level IN access_levels_csr --(p_statement_id)
1098 LOOP
1099 l_data := l_data||'a'||l_access_level.lookup_code||'a'||' ';
1100 END LOOP;
1101 l_data := l_data ||'</ACCESS>';
1102
1103
1104 l_amt := LENGTH(l_data);
1105 IF(l_amt>0) THEN
1106 dbms_lob.writeappend(p_clob, l_amt, l_data);
1107 END IF;
1108 END Write_Stmt_Header_Sect_Hlp;
1109
1110 PROCEDURE Write_Stmt_CatGrp_Sect_Hlp
1111 ( p_statement_id IN NUMBER,
1112 p_clob IN OUT NOCOPY CLOB)
1113 IS
1114
1115 -- Bug 3455203 - Perf Changes:
1116 -- Restrict on PUB
1117
1118 CURSOR c4(c_element_id IN NUMBER) IS
1119 SELECT UNIQUE b.category_group_id
1120 FROM cs_kb_set_categories a,
1121 CS_KB_CAT_GROUP_DENORM b,
1122 cs_kb_set_eles c ,
1123 cs_kb_sets_b d
1124 WHERE a.category_id = b.child_category_id
1125 AND a.set_id = c.set_id
1126 AND d.status = 'PUB'
1127 AND c.set_id = d.set_id
1128 AND c.element_id = c_element_id;
1129
1130 CURSOR c5(c_position IN NUMBER) IS
1131 SELECT visibility_id FROM cs_kb_visibilities_b
1132 -- (secure) klou
1133 WHERE position <= c_position
1134 ORDER BY visibility_id;
1135
1136 -- Bug 3455203 - Perf Changes:
1137 -- Restrict on PUB
1138 CURSOR c6(c_element_id IN NUMBER) IS
1139 SELECT s.set_id, v.position
1140 FROM cs_kb_set_eles se,
1141 cs_kb_Sets_B s,
1142 cs_kb_visibilities_b v
1143 WHERE se.element_id = c_element_id
1144 AND se.set_id = s.set_id
1145 AND s.status = 'PUB'
1146 AND s.visibility_id = v.visibility_id;
1147
1148
1149 l_data VARCHAR2(32000);
1150 l_amt BINARY_INTEGER;
1151 l_max_cat_vis NUMBER;
1152 l_soln_vis NUMBER;
1153 l_vis NUMBER;
1154 l_max_vis NUMBER;
1155 rec4 c4%ROWTYPE;
1156 rec5 c5%ROWTYPE;
1157 rec6 c6%ROWTYPE;
1158 l_newline VARCHAR2(4) := fnd_global.newline;
1159
1160 -- perf11510
1161 CURSOR max_vis_pos_csr (p_statement_id NUMBER,
1162 p_set_id NUMBER,
1163 p_cat_group_id NUMBER)
1164 IS
1165 SELECT NVL(MAX(b.visibility_position), -1)
1166 FROM cs_kb_set_categories a,
1167 CS_KB_CAT_GROUP_DENORM b,
1168 cs_kb_set_eles c
1169 WHERE a.category_id = b.child_category_id
1170 AND a.set_id = c.set_id
1171 AND a.set_id = p_set_id --rec6.set_id
1172 AND b.category_group_id = p_cat_group_id --rec4.category_group_id
1173 AND c.element_id = p_statement_id;
1174
1175 /* 336469: For 8.1.7 compatibility
1176 Type t_set_pos_tbl Is Table Of c6%ROWTYPE
1177 Index By Binary_Integer;
1178
1179 l_set_pos t_set_pos_tbl;
1180 */
1181 TYPE list_num IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
1182 l_set_pos list_num;
1183 l_set_ids list_num;
1184
1185 BEGIN
1186 -- write the start category group section info
1187
1188 l_data := l_newline||'<CATEGORYGROUPS>';
1189 l_amt := LENGTH(l_data);
1190 dbms_lob.writeappend(p_clob, l_amt, l_data);
1191
1192 l_data := '';
1193 l_amt := 0;
1194
1195 l_vis := -1;
1196 l_max_vis := -1;
1197
1198 -- perf11510
1199 OPEN c6(p_statement_id);
1200 --336469: For 8.1.7 compatibility
1201 --Fetch c6 BULK COLLECT Into l_set_pos;
1202 FETCH c6 BULK COLLECT INTO l_set_ids, l_set_pos;
1203 CLOSE c6;
1204 -- end perf11510
1205
1206 FOR rec4 IN c4(p_statement_id) LOOP
1207
1211
1208 -- perf11510
1209 -- for rec6 in c6(p_statement_id) loop
1210 FOR i IN l_set_pos.FIRST..l_set_pos.LAST LOOP
1212
1213 OPEN max_vis_pos_csr(p_statement_id,
1214 --336469 l_set_pos(i).set_id,
1215 l_set_ids(i),
1216 rec4.category_group_id);
1217 FETCH max_vis_pos_csr INTO l_max_cat_vis;
1218 CLOSE max_vis_pos_csr;
1219
1220 -- Bug 3455203 - Perf Changes:
1221 -- Posn now retrieved via c6 cursor
1222 -- select b.position
1223 -- into l_soln_vis
1224 -- from cs_kb_sets_b a, cs_kb_visibilities_b b
1225 -- where a.visibility_id = b.visibility_id
1226 -- and a.set_id = rec6.set_id;
1227
1228 -- perf11510
1229 IF l_max_cat_vis > 0 THEN
1230 l_soln_vis := l_set_pos(i); --336469 l_set_pos(i).position;
1231
1235 l_vis := l_max_cat_vis;
1232 IF l_soln_vis < l_max_cat_vis THEN
1233 l_vis := l_soln_vis;
1234 ELSE
1236 END IF;
1237
1238 IF l_max_vis < l_vis THEN
1239 l_max_vis := l_vis;
1240 END IF;
1241 END IF; -- end l_max_cat_vis check
1242 END LOOP; -- end l_set_pos loop
1243
1244 FOR rec5 IN c5(l_max_vis) LOOP
1245 l_data := l_data||' '||TO_CHAR(rec4.category_group_id)||'a' ||TO_CHAR(rec5.visibility_id)||' ';
1246 END LOOP;
1247
1248 -- reset for each cg in loop
1249 l_max_vis := -1;
1250
1251 END LOOP;
1252
1253 l_amt := LENGTH(l_data);
1254 IF ( l_amt > 0 )
1255 THEN
1256 dbms_lob.writeappend(p_clob, l_amt, l_data);
1257 END IF;
1258
1259 l_data := '</CATEGORYGROUPS>';
1260 l_amt := LENGTH(l_data);
1261 dbms_lob.writeappend(p_clob, l_amt, l_data);
1262 END Write_Stmt_CatGrp_Sect_Hlp;
1263
1264 /*
1265 Remove_Tags:
1266 - replaces all occurrences of '<' with '!'
1267 p_text: the original varchar
1268 returns: the modified varchar
1269 */
1270 FUNCTION Remove_Tags
1271 ( p_text IN VARCHAR2)
1272 RETURN VARCHAR2
1273 IS
1274 BEGIN
1275 RETURN REPLACE(p_text, '<', '!');
1276 END Remove_Tags;
1277
1278 /*
1279 Remove_Tags_Clob:
1280 - replaces all occurrences of '<' with '!'
1281 p_clob: the original data
1282 p_temp_clob: if necessary, modified data is stored here
1283 returns: pointer to either p_clob or p_temp_clob
1284 */
1285 FUNCTION Remove_Tags_Clob
1286 ( p_clob IN CLOB,
1287 p_temp_clob IN OUT NOCOPY CLOB
1288 )
1289 RETURN CLOB
1290 IS
1291 l_len NUMBER;
1292 l_idx NUMBER;
1293 BEGIN
1294 --can't use, 8.1.7 does not support CLOB replace
1295 --p_clob := replace(p_clob, '<', '!');
1296
1297 l_idx := dbms_lob.INSTR(p_clob, '<', 1);
1298 IF(l_idx IS NOT NULL AND l_idx > 0) THEN
1299 -- '<' found, so need to copy original into temp clob
1300 -- Clear out the temp clob buffer
1301 dbms_lob.trim(p_temp_clob, 0);
1302 -- Copy original data into temporary clob
1303 l_len := dbms_lob.getlength(p_clob);
1304 dbms_lob.copy(p_temp_clob, p_clob, l_len, 1, 1);
1305 ELSE
1306 -- no '<' found, so just return the original
1307 RETURN p_clob;
1308 END IF;
1309
1310 --assert: there is at least one '<' in p_clob,
1311 --assert: l_idx contains the position of the first '<'
1312 --assert: p_temp_clob is a copy of p_clob.
1313
1314 --Now replace all '<' with '!' in p_temp_clob
1315 --and return p_temp_clob
1316
1317 WHILE(l_idx IS NOT NULL AND l_idx > 0) LOOP
1318 dbms_lob.WRITE(p_temp_clob, 1, l_idx, '!');
1319 l_idx := dbms_lob.INSTR(p_temp_clob, '<', l_idx);
1320 END LOOP;
1321
1322 RETURN p_temp_clob;
1323
1324 END Remove_Tags_Clob;
1325
1326 -- 3341248
1327 PROCEDURE Write_Related_Stmt_Sect_Hlp
1328 ( p_statement_id IN NUMBER,
1329 p_clob IN OUT NOCOPY CLOB)
1330 IS
1331 CURSOR get_all_stmts(p_element_id IN NUMBER) IS
1332 SELECT a.element_id
1333 FROM cs_kb_set_eles a
1334 WHERE a.element_id <> p_element_id --:b1
1335 AND a.set_id IN (
1336 SELECT s.set_id
1337 FROM cs_kb_set_eles se,
1338 cs_kb_sets_b s
1339 WHERE se.element_id = p_element_id
1340 AND se.set_id = s.set_id
1341 AND s.status = 'PUB');
1342 -- select a.element_id
1343 -- from cs_kb_set_eles a, cs_kb_set_eles b, cs_kb_sets_b c
1344 -- where b.set_id = a.set_id
1345 -- and a.set_id = c.set_id
1346 -- and c.status = 'PUB'
1347 -- and b.element_id = p_element_id
1348 -- and a.element_id <> p_element_id;
1349
1350
1351 l_data VARCHAR2(32000);
1352 l_amt BINARY_INTEGER;
1353
1354 l_newline VARCHAR2(4) := fnd_global.newline;
1355
1356 ROWS NATURAL := 30000;
1357 TYPE list_ids IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
1358 l_ele_ids list_ids;
1359
1360 l_cnt NUMBER := 0;
1361 BEGIN
1362
1363 l_data := l_newline||'<RELATEDSTMTS>';
1364 l_amt := LENGTH(l_data);
1365 dbms_lob.writeappend(p_clob, l_amt, l_data);
1366 l_data := '';
1367
1368 OPEN get_all_stmts(p_statement_id);
1369 LOOP
1370 FETCH get_all_stmts BULK COLLECT INTO l_ele_ids LIMIT ROWS;
1371 EXIT WHEN get_all_stmts%NOTFOUND;
1372
1373 FOR i IN l_ele_ids.FIRST..l_ele_ids.LAST
1374 LOOP
1375 l_data := l_data||' '||TO_CHAR(l_ele_ids(i));
1376
1377 l_cnt := l_cnt + 1;
1378
1379 -- Assume that each element_id is 15 digits, we need
1380 -- to flush the l_data every 2000 elements.
1381 -- The 15 digits assumption is very conservative, most
1382 -- of the time it uses only up to 7 digits.
1383 IF l_cnt >= 2000 THEN
1384 l_cnt := 0;
1385 l_amt := LENGTH(l_data);
1386 dbms_lob.writeappend(p_clob, l_amt, l_data);
1387 l_data := '';
1388 END IF;
1389 END LOOP;
1390
1391 END LOOP;
1392 CLOSE get_all_stmts;
1393
1394 -- Process the last batch.
1395 -- Why is this needed? This is because when the number of
1396 -- rows in the cursor is less than the LIMIT rows. Oracle
1397 -- does a bulk collect and then set the cursor%notfound
1398 -- to true. That's why the last batch will not be processed
1399 -- inside the loop.
1400 l_cnt := 0;
1404 l_data := l_data||' '||TO_CHAR(l_ele_ids(i));
1401 IF l_ele_ids.COUNT > 0 THEN
1402 FOR i IN l_ele_ids.FIRST..l_ele_ids.LAST
1403 LOOP
1405 l_cnt := l_cnt + 1;
1406 -- Assume that each element_id is 15 digits, we need
1407 -- to flush the l_data every 2000 elements.
1408 -- The 15 digits assumption is very conservative, most
1409 -- of the time it uses only up to 7 digits.
1410 IF l_cnt >= 2000 THEN
1411 l_cnt := 0;
1412 l_amt := LENGTH(l_data);
1413 dbms_lob.writeappend(p_clob, l_amt, l_data);
1414 l_data := '';
1415 END IF;
1416 END LOOP;
1417 END IF;
1418
1419 l_data := l_data||'</RELATEDSTMTS>';
1420 l_amt := LENGTH(l_data);
1421 dbms_lob.writeappend(p_clob, l_amt, l_data);
1422 END Write_Related_Stmt_Sect_Hlp;
1423
1424
1425 END cs_kb_ctx_pkg;