[Home] [Help]
PACKAGE BODY: APPS.CS_SR_CTX_PKG
Source
1 package body cs_sr_ctx_pkg as
2 /* $Header: csctxsrb.pls 120.0 2005/06/01 10:01:55 appldev noship $ */
3
4
5 -- ********************************
6 -- Public Procedure Implementations
7 -- ********************************
8
9 Procedure Build_SR_Text
10 (p_rowid IN ROWID, p_clob IN OUT NOCOPY CLOB)
11 is
12 l_incident_id NUMBER;
13 l_lang varchar2(5);
14 l_inventory_item_id NUMBER;
15 l_summary varchar2(241);
16
17 l_type_id NUMBER;
18
19 l_temp_clob CLOB;
20 l_newline varchar2(4);
21
22 CURSOR GET_SR_CONTENT(c_rowid rowid) IS
23 select tl.incident_id, tl.language, b.incident_type_id,
24 b.inventory_item_id, tl.summary
25 from cs_incidents_all_tl tl, cs_incidents_all_b b
26 where tl.rowid = c_rowid
27 and tl.incident_id = b.incident_id;
28
29 CURSOR GET_SR_NOTS (c_obj_id NUMBER, c_lang VARCHAR2) IS
30 select tl.notes, b.note_status, b.created_by
31 ,tl.notes_detail
32 from jtf_notes_tl tl, jtf_notes_b b
33 where tl.jtf_note_id = b.jtf_note_id
34 and b.source_object_code = 'SR'
35 and b.source_object_id = c_obj_id
36 and b.note_status in ('E', 'I')
37 and tl.language = c_lang;
38
39 l_data VARCHAR2(32000);
40 l_amt NUMBER;
41 l_note VARCHAR2(2000);
42
43 l_clob_len NUMBER;
44 p_clob_len NUMBER;
45 l_notes_detail CLOB;
46 Begin
47 -- Initialize parameters
48 l_amt := 0;
49 l_data := '';
50 l_note := null;
51 l_newline := fnd_global.newline;
52 l_temp_clob := null;
53 l_notes_detail := null;
54
55
56 -- Clear out the output CLOB buffer
57 dbms_lob.trim(p_clob, 0);
58
59 Open GET_SR_CONTENT(p_rowid);
60 Fetch GET_SR_CONTENT Into l_incident_id,
61 l_lang,
62 l_type_id,
63 l_inventory_item_id,
64 l_summary;
65 Close GET_SR_CONTENT;
66
67 -- Synthesize the text content
68 l_data := '<SUMMARY>'||Remove_Tags(l_summary)||'</SUMMARY>'||l_newline;
69
70 -- Add sections
71 -- 1. Add SRTYPE
72 l_data := l_data || l_newline ||'<SRTYPE>'||to_char(l_type_id)
73 ||'</SRTYPE>';
74
75 -- 2. Add LANG
76 l_data := l_data||l_newline||'<LANG>a'||l_lang||'a</LANG>';
77
78 -- 3. Add ITEM
79 l_data := l_data||l_newline||'<ITEM>'||to_char(l_inventory_item_id)
80 ||'</ITEM>';
81
82 l_amt := length(l_data);
83
84 dbms_lob.writeappend(p_clob, l_amt, l_data);
85
86 -- 4. Append all SR notes to the NOTES section
87 l_data := l_newline||'<NOTES>';
88 For srnote in GET_SR_NOTS(l_incident_id, l_lang) Loop
89
90 l_data := l_data ||' '||Remove_Tags(srnote.notes)||l_newline;
91 l_amt := length(l_data);
92 /*
93 If l_amt > 29990 Then
94 dbms_lob.writeappend(p_clob, l_amt, l_data);
95 l_data := '';
96 End If;
97 */
98 dbms_lob.writeappend(p_clob, l_amt, l_data);
99 l_data := '';
100
101 If (srnote.notes_detail is not null
102 and dbms_lob.getlength(srnote.notes_detail) > 0)
103 Then
104 dbms_lob.createtemporary(l_temp_clob, TRUE, dbms_lob.call);
105
106 l_notes_detail := Remove_Tags_Clob(srnote.notes_detail, l_temp_clob);
107 l_clob_len := dbms_lob.getlength(l_notes_detail);
108 p_clob_len := dbms_lob.getlength(p_clob);
109 dbms_lob.copy(p_clob,
110 l_notes_detail,
111 l_clob_len,
112 p_clob_len+1, 1);
113
114 dbms_lob.freetemporary(l_temp_clob);
115 End if;
116 End Loop;
117
118 l_data := l_data||'</NOTES>';
119
120 l_amt := length(l_data);
121 dbms_lob.writeappend(p_clob, l_amt, l_data);
122 End Build_SR_Text;
123
124 /*
125 Remove_Tags:
126 - replaces all occurrences of '<' with '!'
127 p_text: the original varchar
128 returns: the modified varchar
129 */
130 function Remove_Tags
131 ( p_text IN VARCHAR2)
132 return VARCHAR2
133 is
134 begin
135 return replace(p_text, '<', '!');
136 end Remove_Tags;
137
138 /*
139 * Remove_Tags_Clob: replaces all occurrences of '<' with '!'
140 * p_clob: the original data
141 * p_temp_clob: if necessary, modified data is stored here
142 * returns: pointer to either p_clob or p_temp_clob
143 */
144 function Remove_Tags_Clob
145 ( p_clob IN CLOB,
146 p_temp_clob IN OUT NOCOPY CLOB
147 )
148 RETURN CLOB
149 is
150 l_len number;
151 l_idx number;
152 begin
153 --can't use, 8.1.7 does not support CLOB replace
154 --p_clob := replace(p_clob, '<', '!');
155
156 l_idx := dbms_lob.instr(p_clob, '<', 1);
157 if(l_idx is not null and l_idx > 0) then
158 -- '<' found, so need to copy original into temp clob
159 -- Clear out the temp clob buffer
160 dbms_lob.trim(p_temp_clob, 0);
161 -- Copy original data into temporary clob
162 l_len := dbms_lob.getlength(p_clob);
163 dbms_lob.copy(p_temp_clob, p_clob, l_len, 1, 1);
164 else
165 -- no '<' found, so just return the original
166 return p_clob;
167 end if;
168
169 --assert: there is at least one '<' in p_clob,
170 --assert: l_idx contains the position of the first '<'
171 --assert: p_temp_clob is a copy of p_clob.
172
173 --Now replace all '<' with '!' in p_temp_clob
174 --and return p_temp_clob
175
176 while(l_idx is not null and l_idx > 0) loop
177 dbms_lob.write(p_temp_clob, 1, l_idx, '!');
178 l_idx := dbms_lob.instr(p_temp_clob, '<', l_idx);
179 end loop;
180
181 return p_temp_clob;
182
183 end Remove_Tags_Clob;
184
185
186 end cs_sr_ctx_pkg;