[Home] [Help]
PACKAGE BODY: APPS.CS_KB_CTX_PKG
Source
1 PACKAGE BODY cs_kb_ctx_pkg AS
2 /* $Header: cskbdstb.pls 120.5.12020000.2 2012/07/05 05:36:44 gasankar ship $ */
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 --Start 12.1.3
19 PROCEDURE Single_Sync_Set_Attach_Content
20 (p_solution_id IN NUMBER,
21 p_lang IN VARCHAR2,
22 p_soln_number IN VARCHAR2,
23 p_set_type_id IN NUMBER,
24 p_clob IN OUT NOCOPY CLOB
25
26 );
27 --End 12.1.3
28 PROCEDURE Write_Soln_Header_Cont_Hlp
29 ( p_solution_id IN NUMBER,
30 p_lang IN VARCHAR2,
31 p_clob IN OUT NOCOPY CLOB,
32 p_temp_clob IN OUT NOCOPY CLOB);
33
34
35 PROCEDURE Write_Soln_Header_Sect_Hlp
36 ( p_solution_id IN NUMBER,
37 p_lang IN VARCHAR2,
38 p_clob IN OUT NOCOPY CLOB);
39
40
41 PROCEDURE Write_Soln_Category_Sect_Hlp
42 ( p_solution_id IN NUMBER,
43 p_clob IN OUT NOCOPY CLOB);
44
45
46 PROCEDURE Write_Soln_Product_Cont_Hlp
47 ( p_solution_id IN NUMBER,
48 p_lang IN VARCHAR2,
49 p_clob IN OUT NOCOPY CLOB,
50 p_temp_clob IN OUT NOCOPY CLOB);
51
52
53 PROCEDURE Write_Soln_Product_Sect_Hlp
54 ( p_solution_id IN NUMBER,
55 p_clob IN OUT NOCOPY CLOB);
56
57
58 PROCEDURE Write_Soln_Platform_Cont_Hlp
59 ( p_solution_id IN NUMBER,
60 p_lang IN VARCHAR2,
61 p_clob IN OUT NOCOPY CLOB,
62 p_temp_clob IN OUT NOCOPY CLOB);
63
64 PROCEDURE Write_Soln_Platform_Sect_Hlp
65 ( p_solution_id IN NUMBER,
66 p_clob IN OUT NOCOPY CLOB);
67
68 PROCEDURE Write_Soln_CatGrp_Sect_Hlp
69 ( p_solution_id IN NUMBER,
70 p_clob IN OUT NOCOPY CLOB);
71
72
73 PROCEDURE Write_Soln_Statement_Cont_Hlp
74 ( p_solution_id IN NUMBER,
75 p_lang IN VARCHAR2,
76 p_clob IN OUT NOCOPY CLOB,
77 p_temp_clob IN OUT NOCOPY CLOB);
78
79 PROCEDURE Write_Soln_Statement_Sect_Hlp
80 ( p_solution_id IN NUMBER,
81 p_clob IN OUT NOCOPY CLOB);
82
83 PROCEDURE Write_Stmt_Header_Cont_Hlp
84 ( p_statement_id IN NUMBER,
85 p_lang IN VARCHAR2,
86 p_clob IN OUT NOCOPY CLOB,
87 p_temp_clob IN OUT NOCOPY CLOB,
88 p_statement_number IN VARCHAR2,
89 p_name IN VARCHAR2,
90 p_description IN CLOB);
91
92 PROCEDURE Write_Stmt_Header_Sect_Hlp
93 ( p_statement_id IN NUMBER,
94 p_lang IN VARCHAR2,
95 p_clob IN OUT NOCOPY CLOB,
96 p_statement_number IN VARCHAR2,
97 p_type_id IN NUMBER,
98 p_access_level IN NUMBER);
99
100 PROCEDURE Write_Stmt_CatGrp_Sect_Hlp
101 ( p_statement_id IN NUMBER,
102 p_clob IN OUT NOCOPY CLOB);
103
104 -- 3341248
105 PROCEDURE Write_Related_Stmt_Sect_Hlp
106 ( p_statement_id IN NUMBER,
107 p_clob IN OUT NOCOPY CLOB);
108
109 --Start 12.1.3
110 PROCEDURE Write_Soln_Attach_Cont_Hlp
111 ( p_solution_id IN NUMBER,
112 p_lang IN VARCHAR2,
113 p_clob IN OUT NOCOPY CLOB,
114 p_temp_clob IN OUT NOCOPY CLOB);
115
116 PROCEDURE Write_Soln_Attach_Sect_Hlp
117 ( p_solution_id IN NUMBER,
118 p_clob IN OUT NOCOPY CLOB);
119 --End 12.1.3
120
121
122
123 -- ********************************
124 -- Public Procedure Implementations
125 -- ********************************
126
127 PROCEDURE Get_Composite_Elements
128 ( p_rowid IN ROWID, p_clob IN OUT NOCOPY CLOB )
129 IS
130 l_solnid NUMBER;
131 l_lang VARCHAR2(4);
132 l_status VARCHAR2(30);
133 l_clob CLOB := NULL;
134 l_clob_len NUMBER;
135
136 --(perf11510)
137 CURSOR set_content_csr(p_rowid IN ROWID) IS
138 SELECT tl.set_id,
139 tl.LANGUAGE,
140 b.status,
141 tl.content_cache,
142 b.set_number,
143 b.set_type_id
144 FROM CS_KB_SETS_TL tl, CS_KB_SETS_B b
145 WHERE tl.ROWID = p_rowid
146 AND b.set_id = tl.set_id;
147
148 l_set_number VARCHAR2(30);
149 l_set_type_id NUMBER;
150 BEGIN
151
152 --(perf11510)
153 /*
154 -- Get the solution id and language, based on the rowid
155 select tl.set_id, tl.language, b.status, tl.content_cache
156 into l_solnid, l_lang, l_status, l_clob
157 from CS_KB_SETS_TL tl, CS_KB_SETS_B b
158 where tl.rowid = p_rowid
159 and b.set_id = tl.set_id;
160 */
161 OPEN set_content_csr(p_rowid);
162 FETCH set_content_csr
163 INTO l_solnid
164 ,l_lang
165 ,l_status
166 ,l_clob
167 ,l_set_number
168 ,l_set_type_id;
169
170 CLOSE set_content_csr;
171 -- end (perf11510)
172
173
174 -- Index only Published Solutions.
175 IF ( l_status = 'PUB' )
176 THEN
177 -- If the solution content cache is populated, then use it
178 -- for the indexed content.
179 IF (l_clob IS NOT NULL AND dbms_lob.getlength(l_clob) > 0)
180 THEN
181 -- Clear out the output CLOB buffer
182 dbms_lob.trim(p_clob, 0);
183
184 -- Copy content cache into output CLOB buffer for indexing
185 l_clob_len := dbms_lob.getlength(l_clob);
186 dbms_lob.copy(p_clob, l_clob, l_clob_len, 1, 1);
187 ELSE
188 -- Call procedure to return synthesized solution content
189 -- for indexing. CLOB is passed by reference and sub
190 -- procedures will change the CLOB directly.
191 -- Synthesize_Solution_Content( l_solnid, l_lang, p_clob );
192 Single_Synthesize_Set_Content( l_solnid
193 ,l_lang
194 ,l_set_number
195 ,l_set_type_id
196 ,p_clob);
197
198 END IF;
199
200 -- Append the solution security information to the indexable
201 -- content at index time.
202 -- Note: neither the content cache, nor the call to Synthesize_
203 -- Solution_Content() includes the security section.
204 Write_Soln_CatGrp_Sect_Hlp( l_solnid, p_clob );
205
206 END IF;
207 END Get_Composite_Elements;
208
209 --(perf11510)
210 PROCEDURE Single_Synthesize_Set_Content
211 ( p_solution_id IN NUMBER,
212 p_lang IN VARCHAR2,
213 p_soln_number IN VARCHAR2,
214 p_set_type_id IN NUMBER,
215 p_clob IN OUT NOCOPY CLOB)
216 IS
217 l_temp_clob CLOB;
218 l_amt BINARY_INTEGER;
219 l_newline VARCHAR2(4) := fnd_global.newline;
220 l_data VARCHAR2(32000);
221
222 CURSOR set_title_csr(p_set_id NUMBER, p_lang VARCHAR2) IS
223 SELECT name
224 FROM Cs_Kb_Sets_tl
225 WHERE set_id = p_set_id
226 AND LANGUAGE = p_lang;
230 l_content VARCHAR2(32000);
227 l_soln_title VARCHAR2(2000);
228
229 l_sections VARCHAR2(32000);
231
232 CURSOR c1(c_setid IN NUMBER, c_lang IN VARCHAR2) IS
233 SELECT et.name, et.description, eb.element_id, eb.element_number
234 FROM CS_KB_ELEMENTS_B eb,
235 CS_KB_ELEMENTS_TL et,
236 CS_KB_SET_ELES se
237 WHERE se.set_id = c_setid
238 AND eb.element_id = se.element_id
239 AND eb.element_id = et.element_id
240 AND eb.status = 'PUBLISHED' --- added 03/16/2004
241 AND et.LANGUAGE = c_lang;
242
243 rec1 c1%ROWTYPE;
244
245 CURSOR c6(c_set_id IN NUMBER, c_lang IN VARCHAR2) IS
246 SELECT b.segment1 name,t.description, s.product_id
247 FROM mtl_system_items_b b, mtl_system_items_tl t, cs_kb_set_products s
248 WHERE t.inventory_item_id = s.product_id
249 AND t.organization_id = s.product_org_id
250 AND b.inventory_item_id = s.product_id
251 AND b.organization_id = s.product_org_id
252 AND t.LANGUAGE = c_lang
253 AND s.set_id = c_set_id;
254
255 CURSOR c7(c_set_id IN NUMBER, c_lang IN VARCHAR2) IS
256 SELECT b.segment1 name,t.description, s.platform_id
257 FROM mtl_system_items_b b, mtl_system_items_tl t, cs_kb_set_platforms s
258 WHERE t.inventory_item_id = s.platform_id
259 AND t.organization_id = s.platform_org_id
260 AND b.inventory_item_id = s.platform_id
261 AND b.organization_id = s.platform_org_id
262 AND t.LANGUAGE = c_lang
263 AND s.set_id = c_set_id;
264
265 CURSOR c5(c_set_id IN NUMBER) IS
266 SELECT DISTINCT category_id
267 FROM CS_KB_SOLN_CATEGORIES_B
268 START WITH category_id IN
269 (
270 SELECT category_id
271 FROM cs_kb_set_categories
272 WHERE set_id = c_set_id
273 )
274 CONNECT BY PRIOR parent_category_id = category_id;
275
276 l_clob CLOB := NULL;
277 l_clob_len NUMBER;
278 p_clob_len NUMBER;
279
280 l_stmt_name VARCHAR2(32000) := '';
281 empty_flag BOOLEAN := TRUE;
282 BEGIN
283 -- temp clob lives for at most the duration of call.
284 dbms_lob.createtemporary(l_temp_clob, TRUE, dbms_lob.call);
285
286 DBMS_LOB.OPEN(l_temp_clob,DBMS_LOB.LOB_READWRITE);
287
288 -- Clear out the output CLOB buffer
289 dbms_lob.trim(p_clob, 0);
290
291 -- write the solution number to clob
292 l_data := l_newline||p_soln_number;
293
294 -- write solution title to clob
295 OPEN set_title_csr(p_solution_id, p_lang);
296 FETCH set_title_csr INTO l_soln_title;
297 CLOSE set_title_csr;
298
299 l_data := l_data||' '||l_soln_title||l_newline;
300 l_data := Remove_Tags(l_data);
301 l_amt := LENGTH(l_data);
302
303 dbms_lob.writeappend(p_clob, l_amt, l_data);
304
305 -- Construct the header sections
306 -- write solution type id
307 l_sections := l_newline||'<TYPE>a' || TO_CHAR(p_set_type_id) || 'a</TYPE> ';
308
309 -- write language code
310 l_sections := l_sections||l_newline||'<LANG>a'|| p_lang ||'a</LANG>';
311
312 -- write solution number
313 l_sections := l_sections|| l_newline||'<NUMBER>a' || p_soln_number
314 || 'a</NUMBER>';
315
316 -- For each Statement linked to the solution header,
317 -- write all of the Statement content (summary, description)
318 -- into the clob.
319 l_sections := l_sections || l_newline||'<STATEMENTS>';
320
321 l_stmt_name := '';
322
323 FOR rec1 IN c1(p_solution_id, p_lang) LOOP
324
325 -- Write the statement summary to clob
326 l_stmt_name := l_stmt_name||rec1.name||l_newline;
327 l_amt := LENGTH(l_stmt_name);
328 IF l_amt >= 31000 THEN
329 -- flush l_stmt_name to the p_clob
330 l_stmt_name := Remove_Tags(l_stmt_name);
331 dbms_lob.writeappend(p_clob, l_amt, l_stmt_name);
332 l_stmt_name := l_newline;
333 END IF;
334
335 -- Write the statement description to clob
336 l_clob := rec1.description;
337 IF (l_clob IS NOT NULL AND dbms_lob.getlength(l_clob) > 0)
338 THEN
339 l_clob := Remove_Tags_Clob(l_clob, l_temp_clob);
340 l_clob_len := dbms_lob.getlength(l_clob);
341 p_clob_len := dbms_lob.getlength(p_clob);
342 dbms_lob.copy(p_clob, l_clob, l_clob_len, p_clob_len+1, 1);
343 END IF;
344
345 -- Repeat each statement id for 10 times.
346 -- Need revisit in 115.x. See issue #1309
347 FOR i IN 1..10 LOOP
348 l_sections := l_sections||' a'||rec1.element_id||'a ';
349 END LOOP;
350 END LOOP;
351
352 l_amt := LENGTH(l_stmt_name);
353 IF l_amt > 0 THEN
354 l_stmt_name := Remove_Tags(l_stmt_name);
355 dbms_lob.writeappend(p_clob, l_amt, l_stmt_name);
356 END IF;
357
358 l_sections := l_sections || '</STATEMENTS>';
359
360 -- write category section
361 l_sections := l_sections|| l_newline||'<CATEGORIES>';
362 FOR rec5 IN c5(p_solution_id) LOOP
363 l_sections := l_sections || ' a' || TO_CHAR(rec5.category_id) || 'a ';
364 END LOOP;
365
366 l_sections := l_sections||'</CATEGORIES>' ;
367
368 --write product name and description to clob
369 l_sections := l_sections || l_newline||'<PRODUCTS>';
370
371 l_data := '';
372 l_amt := 0;
373
374 -- reset empty_flag
375 empty_flag := TRUE;
376 FOR rec6 IN c6(p_solution_id, p_lang) LOOP
377 l_data := l_data||l_newline||rec6.name||' '||rec6.description;
378 l_sections := l_sections||l_newline||' a'||TO_CHAR(rec6.product_id)||'a ';
379 empty_flag := FALSE;
380 END LOOP;
381
382 IF empty_flag THEN
383 -- write generice platforms
384 l_sections := l_sections || 'a000a';
385 END IF;
386 l_sections := l_sections || '</PRODUCTS>';
387
388 l_sections := l_sections || l_newline||'<PLATFORMS>';
389
390 -- reset empty_flag
391 empty_flag := TRUE;
392 FOR rec7 IN c7(p_solution_id, p_lang) LOOP
393 l_data := l_data||l_newline||rec7.name||' '||rec7.description;
394 l_sections := l_sections||' a'||TO_CHAR(rec7.platform_id) ||'a ';
395 empty_flag := FALSE;
396 END LOOP;
397
398 IF empty_flag THEN
399 -- write generice platforms
400 l_sections := l_sections || 'a000a';
401 END IF;
402
403 l_sections := l_sections || '</PLATFORMS>';
404
405 l_data := Remove_Tags(l_data);
406
407 l_amt := LENGTH(l_data);
408 IF ( l_amt > 0 )
409 THEN
410 dbms_lob.writeappend(p_clob, l_amt, l_data);
411 END IF;
412
413 l_amt := LENGTH(l_sections);
414 IF ( l_amt > 0 )
415 THEN
416 dbms_lob.writeappend(p_clob, l_amt, l_sections);
417 END IF;
418
419 DBMS_LOB.CLOSE(l_temp_clob);
420 dbms_lob.freetemporary(l_temp_clob);
421
422 END Single_Synthesize_Set_Content;
423 -- end (perf11510)
424 --Start 12.1.3
425 PROCEDURE Get_Composite_Attach_Elements
426 ( p_rowid IN ROWID, p_clob IN OUT NOCOPY CLOB )
427 IS
428 l_solnid NUMBER;
429 l_lang VARCHAR2(4);
430 l_status VARCHAR2(30);
431 l_clob CLOB := NULL;
432 l_clob_len NUMBER;
433
434 --(perf11510)
435 CURSOR set_content_csr(p_rowid IN ROWID) IS
436 SELECT tl.set_id,
437 tl.LANGUAGE,
438 b.status,
439 tl.attachment_content_cache,
440 b.set_number,
441 b.set_type_id
442 FROM CS_KB_SETS_TL tl, CS_KB_SETS_B b
443 WHERE tl.ROWID = p_rowid
444 AND b.set_id = tl.set_id;
445
446 l_set_number VARCHAR2(30);
447 l_set_type_id NUMBER;
448 BEGIN
449
450 --(perf11510)
451 /*
452 -- Get the solution id and language, based on the rowid
453 select tl.set_id, tl.language, b.status, tl.content_cache
454 into l_solnid, l_lang, l_status, l_clob
455 from CS_KB_SETS_TL tl, CS_KB_SETS_B b
456 where tl.rowid = p_rowid
457 and b.set_id = tl.set_id;
458 */
459 OPEN set_content_csr(p_rowid);
460 FETCH set_content_csr
461 INTO l_solnid
462 ,l_lang
463 ,l_status
464 ,l_clob
465 ,l_set_number
466 ,l_set_type_id;
467
468 CLOSE set_content_csr;
469 -- end (perf11510)
470
471
472 -- Index only Published Solutions.
473 IF ( l_status = 'PUB' )
474 THEN
475 -- If the solution content cache is populated, then use it
476 -- for the indexed content.
477 IF (l_clob IS NOT NULL AND dbms_lob.getlength(l_clob) > 0)
478 THEN
479 -- Clear out the output CLOB buffer
480 dbms_lob.trim(p_clob, 0);
481
482 -- Copy content cache into output CLOB buffer for indexing
483 l_clob_len := dbms_lob.getlength(l_clob);
484 dbms_lob.copy(p_clob, l_clob, l_clob_len, 1, 1);
485 ELSE
486 -- Call procedure to return synthesized solution content
487 -- for indexing. CLOB is passed by reference and sub
488 -- procedures will change the CLOB directly.
489 -- Synthesize_Solution_Content( l_solnid, l_lang, p_clob );
490 Single_Sync_Set_Attach_Content( l_solnid
491 ,l_lang
492 ,l_set_number
493 ,l_set_type_id
494 ,p_clob);
495
496 END IF;
497
498 -- Append the solution security information to the indexable
499 -- content at index time.
500 -- Note: neither the content cache, nor the call to Synthesize_
501 -- Solution_Content() includes the security section.
502 Write_Soln_CatGrp_Sect_Hlp( l_solnid, p_clob );
503
504 END IF;
505 END Get_Composite_Attach_Elements;
506
507 PROCEDURE Single_Sync_Set_Attach_Content
508 ( p_solution_id IN NUMBER,
509 p_lang IN VARCHAR2,
510 p_soln_number IN VARCHAR2,
511 p_set_type_id IN NUMBER,
512 p_clob IN OUT NOCOPY CLOB)
513 IS
514 l_temp_clob CLOB;
515 l_amt BINARY_INTEGER;
516 l_newline VARCHAR2(4) := fnd_global.newline;
517 l_data VARCHAR2(32000);
518
519 CURSOR set_title_csr(p_set_id NUMBER, p_lang VARCHAR2) IS
520 SELECT name
521 FROM Cs_Kb_Sets_tl
522 WHERE set_id = p_set_id
523 AND LANGUAGE = p_lang;
524 l_soln_title VARCHAR2(2000);
525
526 l_sections VARCHAR2(32000);
527 l_content VARCHAR2(32000);
528
529 CURSOR c1(c_setid IN NUMBER, c_lang IN VARCHAR2) IS
530 SELECT et.name, et.description, eb.element_id, eb.element_number
531 FROM CS_KB_ELEMENTS_B eb,
532 CS_KB_ELEMENTS_TL et,
533 CS_KB_SET_ELES se
534 WHERE se.set_id = c_setid
535 AND eb.element_id = se.element_id
536 AND eb.element_id = et.element_id
537 AND eb.status = 'PUBLISHED' --- added 03/16/2004
538 AND et.LANGUAGE = c_lang;
539
540 rec1 c1%ROWTYPE;
541
542 CURSOR c6(c_set_id IN NUMBER, c_lang IN VARCHAR2) IS
543 SELECT b.segment1 name,t.description, s.product_id
544 FROM mtl_system_items_b b, mtl_system_items_tl t, cs_kb_set_products s
545 WHERE t.inventory_item_id = s.product_id
546 AND t.organization_id = s.product_org_id
547 AND b.inventory_item_id = s.product_id
548 AND b.organization_id = s.product_org_id
549 AND t.LANGUAGE = c_lang
550 AND s.set_id = c_set_id;
551
552 CURSOR c7(c_set_id IN NUMBER, c_lang IN VARCHAR2) IS
553 SELECT b.segment1 name,t.description, s.platform_id
554 FROM mtl_system_items_b b, mtl_system_items_tl t, cs_kb_set_platforms s
555 WHERE t.inventory_item_id = s.platform_id
556 AND t.organization_id = s.platform_org_id
557 AND b.inventory_item_id = s.platform_id
558 AND b.organization_id = s.platform_org_id
559 AND t.LANGUAGE = c_lang
560 AND s.set_id = c_set_id;
561
562 CURSOR c5(c_set_id IN NUMBER) IS
563 SELECT DISTINCT category_id
564 FROM CS_KB_SOLN_CATEGORIES_B
565 START WITH category_id IN
566 (
567 SELECT category_id
568 FROM cs_kb_set_categories
569 WHERE set_id = c_set_id
570 )
571 CONNECT BY PRIOR parent_category_id = category_id;
572 -- Changed the cursor to fix 11739796
573 CURSOR c8(c_setid IN NUMBER, c_lang IN VARCHAR2) IS
574 SELECT fdtl.title, fdtl.description, fl.file_name, fad.document_id
575 FROM fnd_attached_documents fad,
576 fnd_documents fd,
577 fnd_documents_tl fdtl,
578 fnd_lobs fl
579 WHERE fad.document_id = fd.document_id
580 AND fd.document_id = fdtl.document_id
581 AND fl.file_id = fd.media_id
582 AND fdtl.language = c_lang
583 AND fad.entity_name = 'CS_KB_SETS_B'
584 AND fad.pk1_value = c_setid;
585
586 l_clob CLOB := NULL;
587 l_clob_len NUMBER;
588 p_clob_len NUMBER;
589 --12.1.3
590 src_blob BLOB := null;
591 amount INTEGER := dbms_lob.lobmaxsize;
592 dest_offset INTEGER :=1;
593 -- src_offset INTEGER :=1;
594 blob_csid NUMBER := dbms_lob.default_csid;
595 lang_context INTEGER := dbms_lob.default_lang_ctx;
596 warning INTEGER;
597 --12.1.3
598
599 l_stmt_name VARCHAR2(32000) := '';
600 empty_flag BOOLEAN := TRUE;
601 BEGIN
602 -- temp clob lives for at most the duration of call.
603 dbms_lob.createtemporary(l_temp_clob, TRUE, dbms_lob.call);
604
605 DBMS_LOB.OPEN(l_temp_clob,DBMS_LOB.LOB_READWRITE);
606
607 -- Clear out the output CLOB buffer
608 dbms_lob.trim(p_clob, 0);
609
610 -- write the solution number to clob
611 l_data := l_newline||p_soln_number;
612
613 -- write solution title to clob
614 OPEN set_title_csr(p_solution_id, p_lang);
615 FETCH set_title_csr INTO l_soln_title;
616 CLOSE set_title_csr;
617
618 l_data := l_data||' '||l_soln_title||l_newline;
619 l_data := Remove_Tags(l_data);
620 l_amt := LENGTH(l_data);
621
622 dbms_lob.writeappend(p_clob, l_amt, l_data);
623
624 -- Construct the header sections
625 -- write solution type id
626 l_sections := l_newline||'<TYPE>a' || TO_CHAR(p_set_type_id) || 'a</TYPE> ';
627
628 -- write language code
629 l_sections := l_sections||l_newline||'<LANG>a'|| p_lang ||'a</LANG>';
630
631 -- write solution number
632 l_sections := l_sections|| l_newline||'<NUMBER>a' || p_soln_number
633 || 'a</NUMBER>';
634
635 -- For each Statement linked to the solution header,
636 -- write all of the Statement content (summary, description)
637 -- into the clob.
638 l_sections := l_sections || l_newline||'<STATEMENTS>';
639
640 l_stmt_name := '';
641
642 FOR rec1 IN c1(p_solution_id, p_lang) LOOP
643
644 -- Write the statement summary to clob
645 l_stmt_name := l_stmt_name||rec1.name||l_newline;
646 l_amt := LENGTH(l_stmt_name);
647 IF l_amt >= 31000 THEN
648 -- flush l_stmt_name to the p_clob
649 l_stmt_name := Remove_Tags(l_stmt_name);
650 dbms_lob.writeappend(p_clob, l_amt, l_stmt_name);
651 l_stmt_name := l_newline;
652 END IF;
653
654 -- Write the statement description to clob
655 l_clob := rec1.description;
656 IF (l_clob IS NOT NULL AND dbms_lob.getlength(l_clob) > 0)
657 THEN
658 l_clob := Remove_Tags_Clob(l_clob, l_temp_clob);
659 l_clob_len := dbms_lob.getlength(l_clob);
660 p_clob_len := dbms_lob.getlength(p_clob);
661 dbms_lob.copy(p_clob, l_clob, l_clob_len, p_clob_len+1, 1);
662 END IF;
663
664 -- Repeat each statement id for 10 times.
665 -- Need revisit in 115.x. See issue #1309
666 FOR i IN 1..10 LOOP
667 l_sections := l_sections||' a'||rec1.element_id||'a ';
668 END LOOP;
669 END LOOP;
670
671 l_amt := LENGTH(l_stmt_name);
672 IF l_amt > 0 THEN
673 l_stmt_name := Remove_Tags(l_stmt_name);
674 dbms_lob.writeappend(p_clob, l_amt, l_stmt_name);
675 END IF;
676
677 l_sections := l_sections || '</STATEMENTS>';
678
679 -- write category section
680 l_sections := l_sections|| l_newline||'<CATEGORIES>';
681 FOR rec5 IN c5(p_solution_id) LOOP
682 l_sections := l_sections || ' a' || TO_CHAR(rec5.category_id) || 'a ';
683 END LOOP;
684
685 l_sections := l_sections||'</CATEGORIES>' ;
686
687 --write product name and description to clob
688 l_sections := l_sections || l_newline||'<PRODUCTS>';
689
690 l_data := '';
691 l_amt := 0;
692
693 -- reset empty_flag
694 empty_flag := TRUE;
695 FOR rec6 IN c6(p_solution_id, p_lang) LOOP
696 l_data := l_data||l_newline||rec6.name||' '||rec6.description;
697 l_sections := l_sections||l_newline||' a'||TO_CHAR(rec6.product_id)||'a ';
698 empty_flag := FALSE;
699 END LOOP;
700
701 IF empty_flag THEN
702 -- write generice platforms
703 l_sections := l_sections || 'a000a';
704 END IF;
705 l_sections := l_sections || '</PRODUCTS>';
706
707 l_sections := l_sections || l_newline||'<PLATFORMS>';
708
709 -- reset empty_flag
710 empty_flag := TRUE;
711 FOR rec7 IN c7(p_solution_id, p_lang) LOOP
712 l_data := l_data||l_newline||rec7.name||' '||rec7.description;
713 l_sections := l_sections||' a'||TO_CHAR(rec7.platform_id) ||'a ';
714 empty_flag := FALSE;
715 END LOOP;
716
717 IF empty_flag THEN
718 -- write generice platforms
719 l_sections := l_sections || 'a000a';
720 END IF;
721
722 l_sections := l_sections || '</PLATFORMS>';
723
724 -- Start 12.1.3
725 l_sections := l_sections || l_newline||'<ATTACHMENTS>';
726
727 -- reset empty_flag
728 empty_flag := TRUE;
729 FOR rec8 IN c8(p_solution_id, p_lang) LOOP
730 l_data := l_newline||rec8.title||l_newline||rec8.description||l_newline||rec8.file_name||l_newline;
731 l_data := Remove_Tags(l_data); --, p_temp_clob);
732 l_sections := l_sections||' a'||TO_CHAR(rec8.document_id) ||'a ';
733 l_amt := LENGTH(l_data);
734
735 IF(l_amt>0) THEN
736 dbms_lob.writeappend(p_clob, l_amt, l_data);
737 END IF;
738
739 -- Write the statement description to clob
740 --Commented to fix 11739796
741 /* src_blob := rec8.file_data;
742 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
743 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'cs.plsql.cskbdstb.pls',
744 'Before dbms_lob.converttoclob- src_blob : || src_blob');
745 END IF;
746 dbms_lob.converttoclob(l_clob ,src_blob, amount, dest_offset, src_offset, blob_csid,lang_context, warning);
747 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
748 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'cs.plsql.cskbdstb.pls',
749 'After dbms_lob.converttoclob- l_clob : || l_clob' );
750 END IF;
751
752 IF (l_clob IS NOT NULL AND dbms_lob.getlength(l_clob) > 0)
753 THEN
754 l_clob := Remove_Tags_Clob(l_clob, l_temp_clob);
755 l_clob_len := dbms_lob.getlength(l_clob);
756 p_clob_len := dbms_lob.getlength(p_clob);
757 dbms_lob.copy(p_clob, l_clob, l_clob_len, p_clob_len+1, 1);
758 END IF;*/
759
760 empty_flag := FALSE;
761 IF empty_flag THEN
762 -- write generice platforms
763 l_sections := l_sections || 'a000a';
764 END IF;
765 END LOOP;
766 l_sections := l_sections || '</ATTACHMENTS>';
767
768 --End 12.1.3
769
770 l_data := Remove_Tags(l_data);
771
772 l_amt := LENGTH(l_data);
773 IF ( l_amt > 0 )
774 THEN
775 dbms_lob.writeappend(p_clob, l_amt, l_data);
776 END IF;
777
778 l_amt := LENGTH(l_sections);
779 IF ( l_amt > 0 )
780 THEN
781 dbms_lob.writeappend(p_clob, l_amt, l_sections);
782 END IF;
783
784 DBMS_LOB.CLOSE(l_temp_clob);
785 dbms_lob.freetemporary(l_temp_clob);
786
787 END Single_Sync_Set_Attach_Content;
788 -- end (perf11510)
789
790 --End 12.1.3
791
792 PROCEDURE Build_Elements
793 (p_rowid IN ROWID, p_clob IN OUT NOCOPY CLOB)
794 IS
795 l_statement_id NUMBER;
796 l_lang VARCHAR2(5);
797 l_status VARCHAR2(30);
798
799 l_statement_number VARCHAR2(30);
800 l_name VARCHAR2(2000);
801 l_description CLOB := NULL;
802 l_access_level NUMBER;
803 l_type_id NUMBER;
804
805 l_temp_clob CLOB;
806
807 CURSOR GET_STMT_CONTENT IS
808 SELECT tl.element_id, tl.LANGUAGE, b.status,
809 b.element_number, tl.name, tl.description,
810 b.access_level, b.element_type_id
811 FROM CS_KB_ELEMENTS_TL tl, CS_KB_ELEMENTS_B b
812 WHERE tl.ROWID = p_rowid
813 AND tl.element_id = b.element_id;
814 BEGIN
815 -- Fetch statement id, language, and status based on rowid
816 -- Bug 3455203 - Perf Changes: Select all info in one cursor
817 -- and pass down to other apis
818
819 OPEN GET_STMT_CONTENT;
820 FETCH GET_STMT_CONTENT INTO l_statement_id, l_lang, l_status,
821 l_statement_number, l_name, l_description,
822 l_access_level, l_type_id;
823 CLOSE GET_STMT_CONTENT;
824
825
826 -- Index only Published statements
827 IF ( l_status = 'PUBLISHED' )
828 THEN
829 -- Call procedure to return synthesized statement content
830 -- for indexing. CLOB is passed by reference and sub
831 -- procedures will change the CLOB directly.
832 --Synthesize_Statement_Content( l_statement_id, l_lang, p_clob );
833 -- Bug 3455203 - Perf Changes:
834 -- Consolidated code from Synthesize_Statement_Content to here:
835
836 -- temp clob lives for at most the duration of call.
837 dbms_lob.createtemporary(l_temp_clob, TRUE, dbms_lob.call);
838 -- Clear out the output CLOB buffer
839 dbms_lob.trim(p_clob, 0);
840
841 -- Write out the statement text content
842 Write_Stmt_Header_Cont_Hlp(l_statement_id, l_lang, p_clob, l_temp_clob,
843 l_statement_number, l_name, l_description);
844 -- Write out metadata sections
845 Write_Stmt_Header_Sect_Hlp(l_statement_id, l_lang, p_clob,
846 l_statement_number, l_type_id, l_access_level);
847 -- explicitly free the clob
848 dbms_lob.freetemporary(l_temp_clob);
849 --
850
851 -- Append the statement security information into the index
852 -- at index time.
853 Write_Stmt_CatGrp_Sect_Hlp(l_statement_id, p_clob);
854
855 -- 3341248: Append the related statements information
856 Write_Related_Stmt_Sect_Hlp(l_statement_id, p_clob);
857
858 END IF;
859 END Build_Elements;
860
861
862 PROCEDURE Synthesize_Solution_Content
863 ( p_solution_id IN NUMBER,
864 p_lang IN VARCHAR2,
865 p_clob IN OUT NOCOPY CLOB)
866 IS
867 l_temp_clob CLOB;
868
869 BEGIN
870
871 -- temp clob lives for at most the duration of call.
872 dbms_lob.createtemporary(l_temp_clob, TRUE, dbms_lob.call);
873
874 -- Clear out the output CLOB buffer
875 dbms_lob.trim(p_clob, 0);
876
877 -- Write out the solution text content
878 Write_Soln_Header_Cont_Hlp( p_solution_id, p_lang, p_clob, l_temp_clob );
879 Write_Soln_Statement_Cont_Hlp( p_solution_id, p_lang, p_clob, l_temp_clob );
880 Write_Soln_Product_Cont_Hlp( p_solution_id, p_lang, p_clob, l_temp_clob );
881 Write_Soln_Platform_Cont_Hlp( p_solution_id, p_lang, p_clob, l_temp_clob );
882
883 -- Write out metadata sections
884 Write_Soln_Header_Sect_Hlp( p_solution_id, p_lang, p_clob );
885 Write_Soln_Statement_Sect_Hlp( p_solution_id, p_clob );
886 Write_Soln_Category_Sect_Hlp( p_solution_id, p_clob );
887 Write_Soln_Product_Sect_Hlp( p_solution_id, p_clob );
888 Write_Soln_Platform_Sect_Hlp( p_solution_id, p_clob );
889
890 -- explicitly free the clob
891 dbms_lob.freetemporary(l_temp_clob);
892 END Synthesize_Solution_Content;
896 PROCEDURE Synthesize_Sol_Attach_Content
893
894
895 --Start 12.1.3
897 ( p_solution_id IN NUMBER,
898 p_lang IN VARCHAR2,
899 p_clob IN OUT NOCOPY CLOB)
900 IS
901 l_temp_clob CLOB;
902
903 BEGIN
904
905 -- temp clob lives for at most the duration of call.
906 dbms_lob.createtemporary(l_temp_clob, TRUE, dbms_lob.call);
907
908 -- Clear out the output CLOB buffer
909 dbms_lob.trim(p_clob, 0);
910
911 -- Write out the solution text content
912 Write_Soln_Header_Cont_Hlp( p_solution_id, p_lang, p_clob, l_temp_clob );
913 Write_Soln_Statement_Cont_Hlp( p_solution_id, p_lang, p_clob, l_temp_clob );
914 Write_Soln_Product_Cont_Hlp( p_solution_id, p_lang, p_clob, l_temp_clob );
915 Write_Soln_Platform_Cont_Hlp( p_solution_id, p_lang, p_clob, l_temp_clob );
916 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
917 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'cs.plsql.cskbdstb.pls',
918 'Before Write_Soln_Attach_Cont_Hlp- ');
919 END IF;
920 Write_Soln_Attach_Cont_Hlp( p_solution_id, p_lang, p_clob, l_temp_clob );
921 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
922 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'cs.plsql.cskbdstb.pls',
923 'After Write_Soln_Attach_Cont_Hlp- ');
924 END IF;
925 -- Write out metadata sections
926 Write_Soln_Header_Sect_Hlp( p_solution_id, p_lang, p_clob );
927 Write_Soln_Statement_Sect_Hlp( p_solution_id, p_clob );
928 Write_Soln_Category_Sect_Hlp( p_solution_id, p_clob );
929 Write_Soln_Product_Sect_Hlp( p_solution_id, p_clob );
930 Write_Soln_Platform_Sect_Hlp( p_solution_id, p_clob );
931 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
932 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'cs.plsql.cskbdstb.pls',
933 'Before Write_Soln_Attach_Sect_Hlp- ');
934 END IF;
935 Write_Soln_Attach_Sect_Hlp( p_solution_id, p_clob );
936 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
937 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'cs.plsql.cskbdstb.pls',
938 'After Write_Soln_Attach_Sect_Hlp- ');
939 END IF;
940 -- explicitly free the clob
941 dbms_lob.freetemporary(l_temp_clob);
942 END Synthesize_Sol_Attach_Content;
943 --End 12.1.3
944
945 -- procedure Synthesize_Statement_Content
946 -- ( p_statement_id IN NUMBER,
947 -- p_lang IN VARCHAR2,
948 -- p_clob IN OUT NOCOPY CLOB)
949 -- is
950 -- l_temp_clob CLOB;
951 -- begin
952 --
953 -- -- temp clob lives for at most the duration of call.
954 -- dbms_lob.createtemporary(l_temp_clob, TRUE, dbms_lob.call);
955 --
956 -- -- Clear out the output CLOB buffer
957 -- dbms_lob.trim(p_clob, 0);
958 --
959 -- -- Write out the statement text content
960 -- Write_Stmt_Header_Cont_Hlp(p_statement_id, p_lang, p_clob, l_temp_clob);
961 --
962 -- -- Write out metadata sections
963 -- Write_Stmt_Header_Sect_Hlp(p_statement_id, p_lang, p_clob);
964 --
965 -- -- explicitly free the clob
966 -- dbms_lob.freetemporary(l_temp_clob);
967 --
968 -- end Synthesize_Statement_Content;
969
970
971
972 -- *********************************
973 -- Private Procedure Implementations
974 -- *********************************
975
976 PROCEDURE Write_Soln_Header_Cont_Hlp
977 ( p_solution_id IN NUMBER,
978 p_lang IN VARCHAR2,
979 p_clob IN OUT NOCOPY CLOB,
980 p_temp_clob IN OUT NOCOPY CLOB)
981 IS
982 CURSOR c2(c_set_id IN NUMBER, c_lang IN VARCHAR2) IS
983 SELECT b.set_number, tl.name
984 FROM CS_KB_SETS_B b, CS_KB_SETS_TL tl
985 WHERE b.set_id = c_set_id
986 AND tl.set_id = b.set_id
987 AND tl.LANGUAGE = c_lang;
988
989 l_soln_title VARCHAR2(2000);
990 l_soln_number VARCHAR2(30);
991 l_data VARCHAR2(2000);
992 l_amt BINARY_INTEGER;
993 l_newline VARCHAR2(4) := fnd_global.newline;
994 BEGIN
995 -- Fetch the solution header
996 OPEN c2(p_solution_id, p_lang);
997 FETCH c2 INTO l_soln_number, l_soln_title;
998 CLOSE c2;
999
1000 -- write the solution number to clob
1001 l_data := l_newline||l_soln_number;
1002 l_amt := LENGTH(l_data);
1003 dbms_lob.writeappend(p_clob, l_amt, l_data);
1004
1005 -- write solution title to clob
1006 l_data := ' '||l_soln_title;
1007 l_data := Remove_Tags(l_data); --, p_temp_clob);
1008 l_amt := LENGTH(l_data);
1009 dbms_lob.writeappend(p_clob, l_amt, l_data);
1010
1011 END Write_Soln_Header_Cont_Hlp;
1012
1013
1014 PROCEDURE Write_Soln_Header_Sect_Hlp
1015 ( p_solution_id IN NUMBER,
1016 p_lang IN VARCHAR2,
1017 p_clob IN OUT NOCOPY CLOB)
1018 IS
1019 CURSOR c2(c_set_id IN NUMBER) IS
1020 SELECT set_type_id, set_number
1021 FROM CS_KB_SETS_B
1022 WHERE set_id = c_set_id;
1023
1024 l_soln_number VARCHAR2(30);
1025 l_type_id NUMBER;
1026 l_data VARCHAR2(2000);
1027 l_amt BINARY_INTEGER;
1028 l_newline VARCHAR2(4) := fnd_global.newline;
1029 BEGIN
1030
1031 OPEN c2(p_solution_id);
1032 FETCH c2 INTO l_type_id, l_soln_number;
1033 CLOSE c2;
1034
1035 -- write solution type id
1036 l_data := l_newline||'<TYPE>a' || TO_CHAR(l_type_id) || 'a</TYPE> ';
1037 l_amt := LENGTH(l_data);
1038 dbms_lob.writeappend(p_clob, l_amt, l_data);
1039
1040 -- write language code
1044
1041 l_data := l_newline||'<LANG>a'|| p_lang ||'a</LANG>';
1042 l_amt := LENGTH(l_data);
1043 dbms_lob.writeappend(p_clob, l_amt, l_data);
1045 -- write solution number
1046 l_data := l_newline||'<NUMBER>a' || l_soln_number || 'a</NUMBER>';
1047 l_amt := LENGTH(l_data);
1048 dbms_lob.writeappend(p_clob, l_amt, l_data);
1049
1050 END Write_Soln_Header_Sect_Hlp;
1051
1052 --Start 12.1.3
1053 PROCEDURE Write_Soln_Attach_Cont_Hlp
1054 ( p_solution_id IN NUMBER,
1055 p_lang IN VARCHAR2,
1056 p_clob IN OUT NOCOPY CLOB,
1057 p_temp_clob IN OUT NOCOPY CLOB)
1058 IS
1059 /* CURSOR c1(c_setid IN NUMBER, c_lang IN VARCHAR2) IS
1060 SELECT fdtl.title, fdtl.description, fl.file_name, fl.file_data
1061 FROM fnd_attached_documents fad,
1062 fnd_documents fd,
1063 fnd_documents_tl fdtl,
1064 fnd_lobs fl
1065 WHERE fad.document_id = fd.document_id
1066 AND fd.document_id = fdtl.document_id
1067 AND fl.file_id = fd.media_id
1068 AND fdtl.language = c_lang
1069 AND fad.entity_name = 'CS_KB_SETS_B'
1070 AND fad.pk1_value = c_setid; */
1071 -- Changed the cursor for bug 8815880,11739796
1072 CURSOR c1(c_setid IN NUMBER, c_lang IN VARCHAR2) IS
1073 SELECT fdtl.title, fdtl.description, fl.file_name, fd.url, fst.short_text
1074 FROM fnd_attached_documents fad,
1075 fnd_documents fd,
1076 fnd_documents_tl fdtl,
1077 fnd_lobs fl,
1078 fnd_documents_short_text fst
1079 WHERE fad.document_id = fd.document_id
1080 AND fd.document_id = fdtl.document_id
1081 AND fl.file_id(+) = fd.media_id
1082 AND fst.media_id(+) = fd.media_id
1083 AND fdtl.LANGUAGE = c_lang
1084 AND fad.entity_name = 'CS_KB_SETS_B'
1085 AND fad.pk1_value = c_setid;
1086
1087 rec1 c1%ROWTYPE;
1088 l_data VARCHAR2(32000);
1089 l_amt BINARY_INTEGER;
1090 l_clob CLOB := NULL;
1091 l_clob_len NUMBER;
1092 p_clob_len NUMBER;
1093 src_blob BLOB := null;
1094 amount INTEGER := dbms_lob.lobmaxsize;
1095 dest_offset INTEGER :=1;
1096 -- src_offset INTEGER :=1;
1097 blob_csid NUMBER := dbms_lob.default_csid;
1098 lang_context INTEGER := dbms_lob.default_lang_ctx;
1099 warning INTEGER;
1100 -- blob_length INTEGER;
1101 l_newline VARCHAR2(4) := fnd_global.newline;
1102
1103 BEGIN
1104 -- For each Statement linked to the solution header,
1105 -- write all of the Statement content (summary, description)
1106 -- into the clob.
1107 dbms_lob.createtemporary(l_clob, TRUE, dbms_lob.call);
1108
1109 -- Clear out the output CLOB buffer
1110 dbms_lob.trim(l_clob, 0);
1111
1112
1113 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1114 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'cs.plsql.cskbdstb.pls',
1115 'Inside Write_Soln_Attach_Cont_Hlp- ');
1116 END IF;
1117 FOR rec1 IN c1(p_solution_id, p_lang) LOOP
1118
1119 -- Write the statement summary to clob
1120 l_data := l_newline||rec1.title||l_newline||rec1.description||l_newline||rec1.file_name||l_newline||rec1.url||l_newline||rec1.short_text||l_newline;
1121 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1122 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'cs.plsql.cskbdstb.pls',
1123 'Before Remove_Tags-l_data: || l_data');
1124 END IF;
1125 l_data := Remove_Tags(l_data); --, p_temp_clob);
1126 l_amt := LENGTH(l_data);
1127
1128 IF(l_amt>0) THEN
1129 dbms_lob.writeappend(p_clob, l_amt, l_data);
1130 END IF;
1131
1132 -- Write the statement description to clob
1133 /* commented for the bug fix 11739796
1134 blob_length := DBMS_LOB.GETLENGTH(rec1.file_data); --Bug 8815880
1135 If blob_length is not null then
1136 src_blob := rec1.file_data;
1137 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1138 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'cs.plsql.cskbdstb.pls',
1139 'Before dbms_lob.converttoclob- src_blob : || src_blob');
1140 END IF;
1141 dbms_lob.converttoclob(l_clob ,src_blob, amount, dest_offset, src_offset, blob_csid,lang_context, warning);
1142 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1143 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'cs.plsql.cskbdstb.pls',
1144 'After dbms_lob.converttoclob- l_clob : || l_clob' );
1145 END IF;
1146
1147 IF (l_clob IS NOT NULL AND dbms_lob.getlength(l_clob) > 0)
1148 THEN
1149 l_clob := Remove_Tags_Clob(l_clob, p_temp_clob);
1150 l_clob_len := dbms_lob.getlength(l_clob);
1151 p_clob_len := dbms_lob.getlength(p_clob);
1152 dbms_lob.copy(p_clob, l_clob, l_clob_len, p_clob_len+1, 1);
1153 END IF;
1154 dbms_lob.trim(l_clob, 0);
1155 src_offset := 1;
1156 End If;*/
1157 END LOOP;
1158 END Write_Soln_Attach_Cont_Hlp;
1159
1160 PROCEDURE Write_Soln_Attach_Sect_Hlp
1161 ( p_solution_id IN NUMBER,
1162 p_clob IN OUT NOCOPY CLOB)
1163 IS
1164 CURSOR c1(c_setid IN NUMBER) IS
1165 /* (perf11510): functional fix
1166 select se.element_id
1167 from CS_KB_SET_ELES se
1168 where se.set_id = c_setid;
1169 */
1170 SELECT fad.document_id
1171 FROM fnd_attached_documents fad,
1172 fnd_documents fd
1173 WHERE fad.document_id = fd.document_id
1174 AND fad.entity_name = 'CS_KB_SETS_B'
1175 AND fad.pk1_value = c_setid;
1176 l_data VARCHAR2(32000);
1177 l_amt BINARY_INTEGER;
1178 l_setid NUMBER;
1179 l_lang VARCHAR2(4);
1180 rec1 c1%ROWTYPE;
1181 l_newline VARCHAR2(4) := fnd_global.newline;
1182 BEGIN
1183 -- Write out statement Section to statements section
1184 l_data := l_newline||'<ATTACHMENTS>';
1185 l_amt := LENGTH(l_data);
1186 dbms_lob.writeappend(p_clob, l_amt, l_data);
1187
1188 l_data := '';
1189 l_amt := 0;
1190 FOR rec1 IN c1(p_solution_id) LOOP
1191 l_data := l_data || ' a' || rec1.document_id || 'a ';
1192 END LOOP;
1193
1194 l_amt := LENGTH(l_data);
1195 IF ( l_amt > 0 )
1196 THEN
1197 dbms_lob.writeappend(p_clob, l_amt, l_data);
1198 END IF;
1199
1200 l_data := '</ATTACHMENTS>';
1201 l_amt := LENGTH(l_data);
1202 dbms_lob.writeappend(p_clob, l_amt, l_data);
1203 END Write_Soln_Attach_Sect_Hlp;
1204
1205 --End 12.1.3
1206
1207 PROCEDURE Write_Soln_Category_Sect_Hlp
1208 ( p_solution_id IN NUMBER,
1209 p_clob IN OUT NOCOPY CLOB)
1210 IS
1211 CURSOR c5(c_set_id IN NUMBER) IS
1212 SELECT DISTINCT category_id
1213 FROM CS_KB_SOLN_CATEGORIES_B
1214 START WITH category_id IN
1215 (
1216 SELECT category_id
1217 FROM cs_kb_set_categories
1218 WHERE set_id = c_set_id
1219 )
1220 CONNECT BY PRIOR parent_category_id = category_id;
1221
1222 l_data VARCHAR2(32000);
1223 l_amt BINARY_INTEGER;
1224 l_category_id NUMBER;
1225 l_newline VARCHAR2(4) := fnd_global.newline;
1226 BEGIN
1227 -- write category section
1228
1229 l_data := l_newline||'<CATEGORIES>';
1230 l_amt := LENGTH(l_data);
1231 dbms_lob.writeappend(p_clob, l_amt, l_data);
1232
1233 l_data := '';
1234 l_amt := 0;
1235
1236 FOR rec5 IN c5(p_solution_id) LOOP
1237 l_category_id := rec5.category_id;
1238 l_data := l_data || l_newline || ' a' || TO_CHAR(l_category_id) || 'a ';
1239 END LOOP;
1240
1241 l_amt := LENGTH(l_data);
1242 IF ( l_amt > 0 )
1243 THEN
1244 dbms_lob.writeappend(p_clob, l_amt, l_data);
1245 END IF;
1246
1247 l_data := '</CATEGORIES>';
1248 l_amt := LENGTH(l_data);
1249 dbms_lob.writeappend(p_clob, l_amt, l_data);
1250 END Write_Soln_Category_Sect_Hlp;
1251
1252
1253
1254 PROCEDURE Write_Soln_Product_Cont_Hlp
1255 ( p_solution_id IN NUMBER,
1256 p_lang IN VARCHAR2,
1257 p_clob IN OUT NOCOPY CLOB,
1258 p_temp_clob IN OUT NOCOPY CLOB)
1259 IS
1260 CURSOR c6(c_set_id IN NUMBER, c_lang IN VARCHAR2) IS
1261 SELECT b.segment1 name,t.description
1262 FROM mtl_system_items_b b, mtl_system_items_tl t, cs_kb_set_products s
1263 WHERE t.inventory_item_id = s.product_id
1264 AND t.organization_id = s.product_org_id
1265 AND b.inventory_item_id = s.product_id
1266 AND b.organization_id = s.product_org_id
1267 AND t.LANGUAGE = c_lang
1268 AND s.set_id = c_set_id;
1269 l_data VARCHAR2(32000);
1270 l_amt BINARY_INTEGER;
1271 l_newline VARCHAR2(4) := fnd_global.newline;
1272 BEGIN
1273 --write product name and description to clob
1274 l_data := '';
1275 l_amt := 0;
1276
1277 FOR rec6 IN c6(p_solution_id, p_lang) LOOP
1278 l_data := l_data||l_newline||rec6.name||' '||rec6.description;
1279 END LOOP;
1280
1281 l_data := Remove_Tags(l_data); --, p_temp_clob);
1282
1283 l_amt := LENGTH(l_data);
1284 IF ( l_amt > 0 )
1285 THEN
1286 dbms_lob.writeappend(p_clob, l_amt, l_data);
1287 END IF;
1288
1289 END Write_Soln_Product_Cont_Hlp;
1290
1291
1292 PROCEDURE Write_Soln_Product_Sect_Hlp
1293 ( p_solution_id IN NUMBER,
1294 p_clob IN OUT NOCOPY CLOB)
1295 IS
1296 CURSOR c3(c_set_id IN NUMBER) IS
1297 SELECT product_id, product_org_id
1298 FROM CS_KB_SET_PRODUCTS
1299 WHERE set_id = c_set_id;
1300 l_data VARCHAR2(32000);
1301 l_amt BINARY_INTEGER;
1302 l_setid NUMBER;
1303 l_product_id NUMBER;
1304 l_product_org_id NUMBER;
1305 rec3 c3%ROWTYPE;
1306 l_newline VARCHAR2(4) := fnd_global.newline;
1307 BEGIN
1308 -- write the start product section info
1309
1310 l_data := l_newline||'<PRODUCTS>';
1311 l_amt := LENGTH(l_data);
1312 dbms_lob.writeappend(p_clob, l_amt, l_data);
1313
1314 l_data := '';
1315 l_amt := 0;
1316 FOR rec3 IN c3(p_solution_id) LOOP
1317 l_product_id := rec3.product_id;
1318 l_product_org_id := rec3.product_org_id;
1319
1320 l_data := l_data||l_newline||' a'||TO_CHAR(l_product_id)||'a ';
1321 END LOOP;
1322
1323 l_amt := LENGTH(l_data);
1324 IF ( l_amt > 0 )
1325 THEN
1326 dbms_lob.writeappend(p_clob, l_amt, l_data);
1327 ELSE
1328 l_data := 'a000a';
1329 dbms_lob.writeappend(p_clob, 5, l_data);
1330 END IF;
1331
1332 l_data := '</PRODUCTS>';
1333 l_amt := LENGTH(l_data);
1334 dbms_lob.writeappend(p_clob, l_amt, l_data);
1335 END Write_Soln_Product_Sect_Hlp;
1336
1337
1338
1339 PROCEDURE Write_Soln_Platform_Cont_Hlp
1340 ( p_solution_id IN NUMBER,
1341 p_lang IN VARCHAR2,
1342 p_clob IN OUT NOCOPY CLOB,
1343 p_temp_clob IN OUT NOCOPY CLOB)
1344 IS
1345 CURSOR c7(c_set_id IN NUMBER, c_lang IN VARCHAR2) IS
1346 SELECT b.segment1 name,t.description
1347 FROM mtl_system_items_b b, mtl_system_items_tl t, cs_kb_set_platforms s
1348 WHERE t.inventory_item_id = s.platform_id
1349 AND t.organization_id = s.platform_org_id
1350 AND b.inventory_item_id = s.platform_id
1351 AND b.organization_id = s.platform_org_id
1352 AND t.LANGUAGE = c_lang
1353 AND s.set_id = c_set_id;
1354 l_data VARCHAR2(32000);
1355 l_amt BINARY_INTEGER;
1356 l_newline VARCHAR2(4) := fnd_global.newline;
1357 BEGIN
1358 --write platform name and description to clob
1359 l_data := '';
1360 l_amt := 0;
1361 FOR rec7 IN c7(p_solution_id, p_lang) LOOP
1362 l_data := l_data||l_newline||rec7.name||' '||rec7.description;
1363 END LOOP;
1364
1365 l_data := Remove_Tags(l_data); --, p_temp_clob);
1366
1367 l_amt := LENGTH(l_data);
1368 IF ( l_amt > 0 )
1369 THEN
1370 dbms_lob.writeappend(p_clob, l_amt, l_data);
1371 END IF;
1372 END Write_Soln_Platform_Cont_Hlp;
1373
1374
1375 PROCEDURE Write_Soln_Platform_Sect_Hlp
1376 ( p_solution_id IN NUMBER,
1377 p_clob IN OUT NOCOPY CLOB)
1378 IS
1379 CURSOR c4(c_set_id IN NUMBER) IS
1380 SELECT platform_id, platform_org_id
1381 FROM CS_KB_SET_PLATFORMS
1382 WHERE set_id = c_set_id;
1383 l_data VARCHAR2(32000);
1384 l_amt BINARY_INTEGER;
1385 l_platform_id NUMBER;
1386 l_platform_org_id NUMBER;
1390 -- write the start platform section info
1387 rec4 c4%ROWTYPE;
1388 l_newline VARCHAR2(4) := fnd_global.newline;
1389 BEGIN
1391
1392 l_data := l_newline||'<PLATFORMS>';
1393 l_amt := LENGTH(l_data);
1394 dbms_lob.writeappend(p_clob, l_amt, l_data);
1395
1396 l_data := '';
1397 l_amt := 0;
1398
1399 FOR rec4 IN c4(p_solution_id) LOOP
1400 l_platform_id := rec4.platform_id;
1401 l_platform_org_id := rec4.platform_org_id;
1402
1403 l_data := l_data||' a'||TO_CHAR(l_platform_id) ||'a ';
1404 END LOOP;
1405
1406 l_amt := LENGTH(l_data);
1407 IF ( l_amt > 0 )
1408 THEN
1409 dbms_lob.writeappend(p_clob, l_amt, l_data);
1410 ELSE
1411 l_data := 'a000a';
1412 dbms_lob.writeappend(p_clob, 5, l_data);
1413 END IF;
1414
1415 l_data := '</PLATFORMS>';
1416 l_amt := LENGTH(l_data);
1417 dbms_lob.writeappend(p_clob, l_amt, l_data);
1418 END Write_Soln_Platform_Sect_Hlp;
1419
1420
1421 PROCEDURE Write_Soln_CatGrp_Sect_Hlp
1422 ( p_solution_id IN NUMBER,
1423 p_clob IN OUT NOCOPY CLOB)
1424 IS
1425 CURSOR c4(c_set_id IN NUMBER) IS
1426 SELECT UNIQUE b.category_group_id
1427 FROM cs_kb_set_categories a, CS_KB_CAT_GROUP_DENORM b
1428 WHERE a.category_id = b.child_category_id
1429 AND a.set_id = c_set_id;
1430
1431 CURSOR c5(c_position IN NUMBER) IS
1432 SELECT visibility_id FROM cs_kb_visibilities_b
1433 -- (secure) klou
1434 WHERE position <= c_position
1435 ORDER BY visibility_id;
1436
1437 l_data VARCHAR2(32000);
1438 l_amt BINARY_INTEGER;
1439 l_max_cat_vis NUMBER;
1440 l_soln_vis NUMBER;
1441 l_vis NUMBER;
1442 rec4 c4%ROWTYPE;
1443 rec5 c5%ROWTYPE;
1444 l_newline VARCHAR2(4) := fnd_global.newline;
1445 BEGIN
1446 -- write the start category group section info
1447
1448 l_data := l_newline||'<CATEGORYGROUPS>';
1449 l_amt := LENGTH(l_data);
1450 dbms_lob.writeappend(p_clob, l_amt, l_data);
1451
1452 l_data := '';
1453 l_amt := 0;
1454
1455 FOR rec4 IN c4(p_solution_id) LOOP
1456 SELECT MAX(b.visibility_position)
1457 INTO l_max_cat_vis
1458 FROM cs_kb_set_categories a, CS_KB_CAT_GROUP_DENORM b
1459 WHERE a.category_id = b.child_category_id
1460 AND b.category_group_id = rec4.category_group_id
1461 AND a.set_id = p_solution_id;
1462
1463 SELECT b.position
1464 INTO l_soln_vis
1465 FROM cs_kb_sets_b a, cs_kb_visibilities_b b
1466 WHERE a.visibility_id = b.visibility_id
1467 AND a.set_id = p_solution_id;
1468
1469 IF l_soln_vis < l_max_cat_vis THEN
1470 l_vis := l_soln_vis;
1471 ELSE
1472 l_vis := l_max_cat_vis;
1473 END IF;
1474
1475 FOR rec5 IN c5(l_vis) LOOP
1476 l_data := l_data||' '||TO_CHAR(rec4.category_group_id)||'a' ||TO_CHAR(rec5.visibility_id)||' ';
1477 END LOOP;
1478 END LOOP;
1479
1480 l_amt := LENGTH(l_data);
1481 IF ( l_amt > 0 )
1482 THEN
1483 dbms_lob.writeappend(p_clob, l_amt, l_data);
1484 END IF;
1485
1486 l_data := '</CATEGORYGROUPS>';
1487 l_amt := LENGTH(l_data);
1488 dbms_lob.writeappend(p_clob, l_amt, l_data);
1489 END Write_Soln_CatGrp_Sect_Hlp;
1490
1491
1492 PROCEDURE Write_Soln_Statement_Cont_Hlp
1493 ( p_solution_id IN NUMBER,
1494 p_lang IN VARCHAR2,
1495 p_clob IN OUT NOCOPY CLOB,
1496 p_temp_clob IN OUT NOCOPY CLOB)
1497 IS
1498 CURSOR c1(c_setid IN NUMBER, c_lang IN VARCHAR2) IS
1499 SELECT et.name, et.description, eb.element_id, eb.element_number
1500 FROM CS_KB_ELEMENTS_B eb,
1501 CS_KB_ELEMENTS_TL et,
1502 CS_KB_SET_ELES se
1503 WHERE se.set_id = c_setid
1504 AND eb.element_id = se.element_id
1505 AND eb.element_id = et.element_id
1506 AND eb.status = 'PUBLISHED' --- added 03/16/2004
1507 AND et.LANGUAGE = c_lang;
1508 rec1 c1%ROWTYPE;
1509 l_data VARCHAR2(32000);
1510 l_amt BINARY_INTEGER;
1511 l_clob CLOB := NULL;
1512 l_clob_len NUMBER;
1513 p_clob_len NUMBER;
1514 l_newline VARCHAR2(4) := fnd_global.newline;
1515 BEGIN
1516 -- For each Statement linked to the solution header,
1517 -- write all of the Statement content (summary, description)
1518 -- into the clob.
1519 FOR rec1 IN c1(p_solution_id, p_lang) LOOP
1520
1521 -- Write the statement summary to clob
1522 l_data := l_newline||rec1.name||l_newline;
1523 l_data := Remove_Tags(l_data); --, p_temp_clob);
1524 l_amt := LENGTH(l_data);
1525
1526 IF(l_amt>0) THEN
1527 dbms_lob.writeappend(p_clob, l_amt, l_data);
1528 END IF;
1529
1530 -- Write the statement description to clob
1531 l_clob := rec1.description;
1532
1533 IF (l_clob IS NOT NULL AND dbms_lob.getlength(l_clob) > 0)
1534 THEN
1535 l_clob := Remove_Tags_Clob(l_clob, p_temp_clob);
1536 l_clob_len := dbms_lob.getlength(l_clob);
1537 p_clob_len := dbms_lob.getlength(p_clob);
1538 dbms_lob.copy(p_clob, l_clob, l_clob_len, p_clob_len+1, 1);
1539 END IF;
1540 END LOOP;
1541 END Write_Soln_Statement_Cont_Hlp;
1542
1543 PROCEDURE Write_Soln_Statement_Sect_Hlp
1544 ( p_solution_id IN NUMBER,
1545 p_clob IN OUT NOCOPY CLOB)
1546 IS
1547 CURSOR c1(c_setid IN NUMBER) IS
1551 where se.set_id = c_setid;
1548 /* (perf11510): functional fix
1549 select se.element_id
1550 from CS_KB_SET_ELES se
1552 */
1553 SELECT se.element_id
1554 FROM CS_KB_SET_ELES se,
1555 cs_kb_elements_b sb
1556 WHERE se.set_id = c_setid
1557 AND sb.element_id = se.element_id
1558 AND sb.status = 'PUBLISHED';
1559 l_data VARCHAR2(32000);
1560 l_amt BINARY_INTEGER;
1561 l_setid NUMBER;
1562 l_lang VARCHAR2(4);
1563 rec1 c1%ROWTYPE;
1564 l_newline VARCHAR2(4) := fnd_global.newline;
1565 BEGIN
1566 -- Write out statement Section to statements section
1567 l_data := l_newline||'<STATEMENTS>';
1568 l_amt := LENGTH(l_data);
1569 dbms_lob.writeappend(p_clob, l_amt, l_data);
1570
1571 l_data := '';
1572 l_amt := 0;
1573 FOR rec1 IN c1(p_solution_id) LOOP
1574 FOR i IN 1..10 LOOP
1575 l_data := l_data || ' a' || rec1.element_id || 'a ';
1576 END LOOP;
1577 END LOOP;
1578
1579 l_amt := LENGTH(l_data);
1580 IF ( l_amt > 0 )
1581 THEN
1582 dbms_lob.writeappend(p_clob, l_amt, l_data);
1583 END IF;
1584
1585 l_data := '</STATEMENTS>';
1586 l_amt := LENGTH(l_data);
1587 dbms_lob.writeappend(p_clob, l_amt, l_data);
1588 END Write_Soln_Statement_Sect_Hlp;
1589
1590 PROCEDURE Write_Stmt_Header_Cont_Hlp
1591 ( p_statement_id IN NUMBER,
1592 p_lang IN VARCHAR2,
1593 p_clob IN OUT NOCOPY CLOB,
1594 p_temp_clob IN OUT NOCOPY CLOB,
1595 p_statement_number IN VARCHAR2,
1596 p_name IN VARCHAR2,
1597 p_description IN CLOB)
1598
1599 IS
1600 -- Bug 3455203 - Perf Changes:
1601 -- Name and Desc now passed as params to api
1602 -- cursor c1(c_element_id in number, c_lang in varchar2) is
1603 -- select b.element_number, tl.name, tl.description
1604 -- from cs_kb_elements_tl tl, cs_kb_elements_b b
1605 -- where tl.element_id = c_element_id
1606 -- and tl.language = c_lang
1607 -- and tl.element_id = b.element_id;
1608 -- rec1 c1%ROWTYPE;
1609 l_data VARCHAR2(2000);
1610 l_amt BINARY_INTEGER;
1611 l_newline VARCHAR2(4) := fnd_global.newline;
1612 -- l_statement_number varchar2(30);
1613 -- l_name varchar2(2000);
1614 l_description CLOB := NULL;
1615 l_clob_len NUMBER;
1616 p_clob_len NUMBER;
1617 BEGIN
1618 -- Fetch Statement summary and description
1619 -- open c1( p_statement_id, p_lang);
1620 -- fetch c1 into l_statement_number, l_name, l_description;
1621 -- close c1;
1622
1623 -- Write the statement number and summary to clob
1624 l_data := l_newline||p_statement_number||' '||p_name||l_newline;
1625 l_data := Remove_Tags(l_data); --, p_temp_clob);
1626 l_amt := LENGTH(l_data);
1627 dbms_lob.writeappend(p_clob, l_amt, l_data);
1628
1629 -- write statement description into clob
1630 l_description := p_description;
1631 IF (l_description IS NOT NULL AND dbms_lob.getlength(l_description) > 0)
1632 THEN
1633 l_description := Remove_Tags_Clob(l_description, p_temp_clob);
1634 l_clob_len := dbms_lob.getlength(l_description);
1635 p_clob_len := dbms_lob.getlength(p_clob);
1636 dbms_lob.copy(p_clob, l_description, l_clob_len, p_clob_len+1, 1);
1637 END IF;
1638 END Write_Stmt_Header_Cont_Hlp;
1639
1640
1641 PROCEDURE Write_Stmt_Header_Sect_Hlp
1642 ( p_statement_id IN NUMBER,
1643 p_lang IN VARCHAR2,
1644 p_clob IN OUT NOCOPY CLOB,
1645 p_statement_number IN VARCHAR2,
1646 p_type_id IN NUMBER,
1647 p_access_level IN NUMBER)
1648 IS
1649 -- Bug 3455203 - Perf Changes:
1650 -- Type and Number now passed as params to api
1651 -- cursor c1(c_element_id in number) is
1652 -- select element_type_id, element_number
1653 -- from cs_kb_elements_b
1654 -- where element_id = c_element_id;
1655 --rec1 c1%ROWTYPE;
1656 l_data VARCHAR2(2000);
1657 l_amt BINARY_INTEGER;
1658 --l_type_id number;
1659 l_newline VARCHAR2(4) := fnd_global.newline;
1660 --l_statement_number varchar(30);
1661
1662 -- Add access level section
1663 CURSOR access_levels_csr IS --(p_element_id in NUMBER) is
1664 SELECT lookup_code
1665 FROM cs_lookups
1666 WHERE lookup_type = 'CS_KB_ACCESS_LEVEL'
1667 AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(start_date_active, SYSDATE))
1668 AND TRUNC(NVL(end_date_active, SYSDATE))
1669 AND TO_NUMBER(lookup_code) <= p_access_level;
1670
1671 l_access_level access_levels_csr%ROWTYPE;
1672
1673 BEGIN
1674 -- Fetch Statement type info
1675 -- open c1( p_statement_id );
1676 -- fetch c1 into l_type_id, l_statement_number;
1677 -- close c1;
1678
1679 --write statement type and language section data into clob.
1680 l_data := l_newline||'<NUMBER>a' || p_statement_number || 'a</NUMBER>'||
1681 l_newline||'<TYPE>a'||TO_CHAR(p_type_id)||'a</TYPE>'||
1682 l_newline||'<LANG>a'||p_lang||'a</LANG>'
1683 --3341248
1684 ||l_newline||'<STATEMENTID>a'||p_statement_id||'a</STATEMENTID>'
1685 ;
1686 --end 3341248
1687
1688 -- Add access level
1689 l_data := l_data || l_newline||'<ACCESS> ';
1690 FOR l_access_level IN access_levels_csr --(p_statement_id)
1691 LOOP
1692 l_data := l_data||'a'||l_access_level.lookup_code||'a'||' ';
1693 END LOOP;
1694 l_data := l_data ||'</ACCESS>';
1695
1696
1700 END IF;
1697 l_amt := LENGTH(l_data);
1698 IF(l_amt>0) THEN
1699 dbms_lob.writeappend(p_clob, l_amt, l_data);
1701 END Write_Stmt_Header_Sect_Hlp;
1702
1703 PROCEDURE Write_Stmt_CatGrp_Sect_Hlp
1704 ( p_statement_id IN NUMBER,
1705 p_clob IN OUT NOCOPY CLOB)
1706 IS
1707
1708 -- Bug 3455203 - Perf Changes:
1709 -- Restrict on PUB
1710
1711 CURSOR c4(c_element_id IN NUMBER) IS
1712 SELECT UNIQUE b.category_group_id
1713 FROM cs_kb_set_categories a,
1714 CS_KB_CAT_GROUP_DENORM b,
1715 cs_kb_set_eles c ,
1716 cs_kb_sets_b d
1717 WHERE a.category_id = b.child_category_id
1718 AND a.set_id = c.set_id
1719 AND d.status = 'PUB'
1720 AND c.set_id = d.set_id
1721 AND c.element_id = c_element_id;
1722
1723 CURSOR c5(c_position IN NUMBER) IS
1724 SELECT visibility_id FROM cs_kb_visibilities_b
1725 -- (secure) klou
1726 WHERE position <= c_position
1727 ORDER BY visibility_id;
1728
1729 -- Bug 3455203 - Perf Changes:
1730 -- Restrict on PUB
1731 CURSOR c6(c_element_id IN NUMBER) IS
1732 SELECT s.set_id, v.position
1733 FROM cs_kb_set_eles se,
1734 cs_kb_Sets_B s,
1735 cs_kb_visibilities_b v
1736 WHERE se.element_id = c_element_id
1737 AND se.set_id = s.set_id
1738 AND s.status = 'PUB'
1739 AND s.visibility_id = v.visibility_id;
1740
1741
1742 l_data VARCHAR2(32000);
1743 l_amt BINARY_INTEGER;
1744 l_max_cat_vis NUMBER;
1745 l_soln_vis NUMBER;
1746 l_vis NUMBER;
1747 l_max_vis NUMBER;
1748 rec4 c4%ROWTYPE;
1749 rec5 c5%ROWTYPE;
1750 rec6 c6%ROWTYPE;
1751 l_newline VARCHAR2(4) := fnd_global.newline;
1752
1753 -- perf11510
1754 CURSOR max_vis_pos_csr (p_statement_id NUMBER,
1755 p_set_id NUMBER,
1756 p_cat_group_id NUMBER)
1757 IS
1758 SELECT NVL(MAX(b.visibility_position), -1)
1759 FROM cs_kb_set_categories a,
1760 CS_KB_CAT_GROUP_DENORM b,
1761 cs_kb_set_eles c
1762 WHERE a.category_id = b.child_category_id
1763 AND a.set_id = c.set_id
1764 AND a.set_id = p_set_id --rec6.set_id
1765 AND b.category_group_id = p_cat_group_id --rec4.category_group_id
1766 AND c.element_id = p_statement_id;
1767
1768 /* 336469: For 8.1.7 compatibility
1769 Type t_set_pos_tbl Is Table Of c6%ROWTYPE
1770 Index By Binary_Integer;
1771
1772 l_set_pos t_set_pos_tbl;
1773 */
1774 TYPE list_num IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
1775 l_set_pos list_num;
1776 l_set_ids list_num;
1777
1778 BEGIN
1779 -- write the start category group section info
1780
1781 l_data := l_newline||'<CATEGORYGROUPS>';
1782 l_amt := LENGTH(l_data);
1783 dbms_lob.writeappend(p_clob, l_amt, l_data);
1784
1785 l_data := '';
1786 l_amt := 0;
1787
1788 l_vis := -1;
1789 l_max_vis := -1;
1790
1791 -- perf11510
1792 OPEN c6(p_statement_id);
1793 --336469: For 8.1.7 compatibility
1794 --Fetch c6 BULK COLLECT Into l_set_pos;
1795 FETCH c6 BULK COLLECT INTO l_set_ids, l_set_pos;
1796 CLOSE c6;
1797 -- end perf11510
1798
1799 FOR rec4 IN c4(p_statement_id) LOOP
1800
1801 -- perf11510
1802 -- for rec6 in c6(p_statement_id) loop
1803 FOR i IN l_set_pos.FIRST..l_set_pos.LAST LOOP
1804
1805
1806 OPEN max_vis_pos_csr(p_statement_id,
1807 --336469 l_set_pos(i).set_id,
1808 l_set_ids(i),
1809 rec4.category_group_id);
1810 FETCH max_vis_pos_csr INTO l_max_cat_vis;
1811 CLOSE max_vis_pos_csr;
1812
1813 -- Bug 3455203 - Perf Changes:
1814 -- Posn now retrieved via c6 cursor
1815 -- select b.position
1816 -- into l_soln_vis
1817 -- from cs_kb_sets_b a, cs_kb_visibilities_b b
1818 -- where a.visibility_id = b.visibility_id
1819 -- and a.set_id = rec6.set_id;
1820
1821 -- perf11510
1822 IF l_max_cat_vis > 0 THEN
1823 l_soln_vis := l_set_pos(i); --336469 l_set_pos(i).position;
1824
1825 IF l_soln_vis < l_max_cat_vis THEN
1826 l_vis := l_soln_vis;
1827 ELSE
1828 l_vis := l_max_cat_vis;
1829 END IF;
1830
1831 IF l_max_vis < l_vis THEN
1832 l_max_vis := l_vis;
1833 END IF;
1834 END IF; -- end l_max_cat_vis check
1835 END LOOP; -- end l_set_pos loop
1836
1837 FOR rec5 IN c5(l_max_vis) LOOP
1838 l_data := l_data||' '||TO_CHAR(rec4.category_group_id)||'a' ||TO_CHAR(rec5.visibility_id)||' ';
1839 END LOOP;
1840
1841 -- reset for each cg in loop
1842 l_max_vis := -1;
1843
1844 END LOOP;
1845
1846 l_amt := LENGTH(l_data);
1847 IF ( l_amt > 0 )
1848 THEN
1849 dbms_lob.writeappend(p_clob, l_amt, l_data);
1850 END IF;
1851
1852 l_data := '</CATEGORYGROUPS>';
1853 l_amt := LENGTH(l_data);
1854 dbms_lob.writeappend(p_clob, l_amt, l_data);
1855 END Write_Stmt_CatGrp_Sect_Hlp;
1856
1857 /*
1858 Remove_Tags:
1859 - replaces all occurrences of '<' with '!'
1863 FUNCTION Remove_Tags
1860 p_text: the original varchar
1861 returns: the modified varchar
1862 */
1864 ( p_text IN VARCHAR2)
1865 RETURN VARCHAR2
1866 IS
1867 BEGIN
1868 RETURN REPLACE(p_text, '<', '!');
1869 END Remove_Tags;
1870
1871 /*
1872 Remove_Tags_Clob:
1873 - replaces all occurrences of '<' with '!'
1874 p_clob: the original data
1875 p_temp_clob: if necessary, modified data is stored here
1876 returns: pointer to either p_clob or p_temp_clob
1877 */
1878 FUNCTION Remove_Tags_Clob
1879 ( p_clob IN CLOB,
1880 p_temp_clob IN OUT NOCOPY CLOB
1881 )
1882 RETURN CLOB
1883 IS
1884 l_len NUMBER;
1885 l_idx NUMBER;
1886 BEGIN
1887 --can't use, 8.1.7 does not support CLOB replace
1888 --p_clob := replace(p_clob, '<', '!');
1889
1890 l_idx := dbms_lob.INSTR(p_clob, '<', 1);
1891 IF(l_idx IS NOT NULL AND l_idx > 0) THEN
1892 -- '<' found, so need to copy original into temp clob
1893 -- Clear out the temp clob buffer
1894 dbms_lob.trim(p_temp_clob, 0);
1895 -- Copy original data into temporary clob
1896 l_len := dbms_lob.getlength(p_clob);
1897 dbms_lob.copy(p_temp_clob, p_clob, l_len, 1, 1);
1898 ELSE
1899 -- no '<' found, so just return the original
1900 RETURN p_clob;
1901 END IF;
1902
1903 --assert: there is at least one '<' in p_clob,
1904 --assert: l_idx contains the position of the first '<'
1905 --assert: p_temp_clob is a copy of p_clob.
1906
1907 --Now replace all '<' with '!' in p_temp_clob
1908 --and return p_temp_clob
1909
1910 WHILE(l_idx IS NOT NULL AND l_idx > 0) LOOP
1911 dbms_lob.WRITE(p_temp_clob, 1, l_idx, '!');
1912 l_idx := dbms_lob.INSTR(p_temp_clob, '<', l_idx);
1913 END LOOP;
1914
1915 RETURN p_temp_clob;
1916
1917 END Remove_Tags_Clob;
1918
1919 -- 3341248
1920 PROCEDURE Write_Related_Stmt_Sect_Hlp
1921 ( p_statement_id IN NUMBER,
1922 p_clob IN OUT NOCOPY CLOB)
1923 IS
1924 CURSOR get_all_stmts(p_element_id IN NUMBER) IS
1925 SELECT a.element_id
1926 FROM cs_kb_set_eles a
1927 WHERE a.element_id <> p_element_id --:b1
1928 AND a.set_id IN (
1929 SELECT s.set_id
1930 FROM cs_kb_set_eles se,
1931 cs_kb_sets_b s
1932 WHERE se.element_id = p_element_id
1933 AND se.set_id = s.set_id
1934 AND s.status = 'PUB');
1935 -- select a.element_id
1936 -- from cs_kb_set_eles a, cs_kb_set_eles b, cs_kb_sets_b c
1937 -- where b.set_id = a.set_id
1938 -- and a.set_id = c.set_id
1939 -- and c.status = 'PUB'
1940 -- and b.element_id = p_element_id
1941 -- and a.element_id <> p_element_id;
1942
1943
1944 l_data VARCHAR2(32000);
1945 l_amt BINARY_INTEGER;
1946
1947 l_newline VARCHAR2(4) := fnd_global.newline;
1948
1949 ROWS NATURAL := 30000;
1950 TYPE list_ids IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
1951 l_ele_ids list_ids;
1952
1953 l_cnt NUMBER := 0;
1954 BEGIN
1955
1956 l_data := l_newline||'<RELATEDSTMTS>';
1957 l_amt := LENGTH(l_data);
1958 dbms_lob.writeappend(p_clob, l_amt, l_data);
1959 l_data := '';
1960
1961 OPEN get_all_stmts(p_statement_id);
1962 LOOP
1963 FETCH get_all_stmts BULK COLLECT INTO l_ele_ids LIMIT ROWS;
1964 EXIT WHEN get_all_stmts%NOTFOUND;
1965
1966 FOR i IN l_ele_ids.FIRST..l_ele_ids.LAST
1967 LOOP
1968 l_data := l_data||' '||TO_CHAR(l_ele_ids(i));
1969
1970 l_cnt := l_cnt + 1;
1971
1972 -- Assume that each element_id is 15 digits, we need
1973 -- to flush the l_data every 2000 elements.
1974 -- The 15 digits assumption is very conservative, most
1975 -- of the time it uses only up to 7 digits.
1976 IF l_cnt >= 2000 THEN
1977 l_cnt := 0;
1978 l_amt := LENGTH(l_data);
1979 dbms_lob.writeappend(p_clob, l_amt, l_data);
1980 l_data := '';
1981 END IF;
1982 END LOOP;
1983
1984 END LOOP;
1985 CLOSE get_all_stmts;
1986
1987 -- Process the last batch.
1988 -- Why is this needed? This is because when the number of
1989 -- rows in the cursor is less than the LIMIT rows. Oracle
1990 -- does a bulk collect and then set the cursor%notfound
1991 -- to true. That's why the last batch will not be processed
1992 -- inside the loop.
1993 l_cnt := 0;
1994 IF l_ele_ids.COUNT > 0 THEN
1995 FOR i IN l_ele_ids.FIRST..l_ele_ids.LAST
1996 LOOP
1997 l_data := l_data||' '||TO_CHAR(l_ele_ids(i));
1998 l_cnt := l_cnt + 1;
1999 -- Assume that each element_id is 15 digits, we need
2000 -- to flush the l_data every 2000 elements.
2001 -- The 15 digits assumption is very conservative, most
2002 -- of the time it uses only up to 7 digits.
2003 IF l_cnt >= 2000 THEN
2004 l_cnt := 0;
2005 l_amt := LENGTH(l_data);
2006 dbms_lob.writeappend(p_clob, l_amt, l_data);
2007 l_data := '';
2008 END IF;
2009 END LOOP;
2010 END IF;
2011
2012 l_data := l_data||'</RELATEDSTMTS>';
2013 l_amt := LENGTH(l_data);
2014 dbms_lob.writeappend(p_clob, l_amt, l_data);
2015 END Write_Related_Stmt_Sect_Hlp;
2016
2017
2018 END cs_kb_ctx_pkg;