[Home] [Help]
PACKAGE BODY: APPS.IBC_CONTENT_CTX_PKG
Source
1 package body ibc_content_ctx_pkg as
2 /* $Header: ibcintxb.pls 120.7 2011/05/10 10:19:22 snsarava ship $ */
3 -- *********************************
4 -- Private Procedure Declarations
5 -- *********************************
6
7 PROCEDURE Synthesize_Content_Attachments
8 ( p_file_id IN NUMBER,
9 p_clob IN OUT NOCOPY CLOB);
10
11
12 PROCEDURE Synthesize_Attribute_Bundles
13 ( p_attribute_bundle_id IN NUMBER,
14 p_clob IN OUT NOCOPY CLOB);
15
16
17 PROCEDURE Synthesize_Content_Renditions
18 ( p_citem_version_id IN NUMBER,
19 p_clob IN OUT NOCOPY CLOB);
20
21
22 PROCEDURE Synthesize_Content_Keywords
23 ( p_citem_version_id IN NUMBER,
24 p_clob IN OUT NOCOPY CLOB);
25
26
27 -- PROCEDURE Synthesize_Content_metadata
28 -- ( p_solution_id IN NUMBER,
29 -- p_clob IN OUT NOCOPY CLOB);
30
31 l_newline CONSTANT VARCHAR2(4) := fnd_global.newline;
32
33 FUNCTION isValidForFilter(p_file_content_type IN VARCHAR2)
34 RETURN BOOLEAN IS
35 BEGIN
36
37 IF instr(UPPER(p_file_content_type),'IMAGE')=1 THEN
38 RETURN FALSE;
39 ELSIF instr(UPPER(p_file_content_type),'VIDEO')=1 THEN
40 RETURN FALSE;
41
42 ELSE
43 RETURN TRUE;
44 END IF;
45
46 END isValidForFilter;
47
48 -- ********************************
49 -- Public Procedure Implementations
50 -- ********************************
51
52
53
54
55 Procedure Build_Content_Document
56 (p_rowid IN ROWID, p_clob IN OUT NOCOPY CLOB)
57 is
58
59 l_citem_version_id NUMBER;
60 l_language VARCHAR2(30);
61 l_content_item_name VARCHAR2(240);
62 l_description VARCHAR2(240);
63 l_attachment_file_name VARCHAR2(240);
64 l_attachment_file_id NUMBER;
65 l_attribute_bundle_id NUMBER;
66 l_data VARCHAR2(32000);
67 l_amt INTEGER;
68 CURSOR cur_name_desc(p_rowid ROWID) IS
69 SELECT
70 ctl.citem_version_id,
71 language,
72 content_item_name,
73 description,
74 attachment_file_name,
75 attachment_file_id,
76 attribute_file_id
77 FROM IBC_CITEM_VERSIONS_TL ctl
78 where ctl.ROWID=p_rowid;
79
80 BEGIN
81
82 -- Clear out the output CLOB buffer
83 dbms_lob.trim(p_clob, 0);
84
85 Open cur_name_desc(p_rowid);
86 Fetch cur_name_desc Into l_citem_version_id,
87 l_language,
88 l_content_item_name,
89 l_description,
90 l_attachment_file_name,
91 l_attachment_file_id,
92 l_attribute_bundle_id;
93 Close cur_name_desc;
94
95 -- Add sections
96 -- 1. Add NAME
97 l_data := '<CONTENT_NAME>'||l_newline|| l_content_item_name||l_newline||'</CONTENT_NAME>';
98
99 -- 2. Add LANG
100 l_data := l_data||l_newline||'<LANG>a'||l_language||'a</LANG>';
101
102 -- 3. Add DESCRIPTION
103 l_data := l_data||l_newline||'<CONTENT_DESCRIPTION>'||l_description||'</CONTENT_DESCRIPTION>';
104
105 -- 3. Add Attachment filename
106 l_data := l_data||l_newline||'<ATTACHMENT_FILENAME>'||l_attachment_file_name||'</ATTACHMENT_FILENAME>';
107
108 l_amt := length(l_data);
109
110 dbms_lob.writeappend(p_clob, l_amt, l_data);
111
112 Synthesize_Content_Attachments(l_attachment_file_id, p_clob);
113 Synthesize_Attribute_Bundles(l_attribute_bundle_id,p_clob);
114 Synthesize_Content_Keywords(l_citem_version_id,p_clob);
115 Synthesize_Content_Renditions(l_citem_version_id,p_clob);
116
117 END Build_Content_Document;
118
119 -- *********************************
120 -- Private Procedure Implementations
121 -- *********************************
122
123 PROCEDURE Synthesize_Content_Attachments
124 ( p_file_id IN NUMBER,
125 p_clob IN OUT NOCOPY CLOB) IS
126
127 CURSOR cur_attachment_file_data(p_file_id IN NUMBER) IS
128 SELECT
129 file_data attachment_file_data,
130 file_content_type
131 FROM
132 fnd_lobs flob
133 WHERE
134 flob.file_id = p_file_id;
135
136 restab CTX_DOC.highlight_tab;
137 l_file_data BLOB;
138 l_data VARCHAR2(32000);
139 l_amt INTEGER;
140 l_document CLOB;
141 l_file_content_type VARCHAR2(256);
142 BEGIN
143
144
145 Open cur_attachment_file_data(p_file_id);
146 Fetch cur_attachment_file_data Into l_file_data,l_file_content_type;
147 Close cur_attachment_file_data;
148
149 IF (l_file_data is NOT NULL) AND (isValidForFilter(l_file_content_type)) THEN
150
151 l_data := l_data || l_newline||'<CONTENT_ATTACHMENT>';
152 l_amt := LENGTH(l_data);
153 dbms_lob.writeappend(p_clob, l_amt, l_data);
154
155 -- Binary files get filtered as text file
156 -- msword,pdf etc is converted to text format
157 ctx_doc.policy_filter(policy_name =>'IBC_Binary2Text_Filter'
158 ,document =>l_file_data -- Binary in from FND_LOBS
159 ,restab =>l_document -- Text out from Filter
160 ,plaintext =>TRUE);
161
162 p_clob := p_clob || l_document;
163
164 l_data := '</CONTENT_ATTACHMENT>';
165 l_amt := LENGTH(l_data);
166 dbms_lob.writeappend(p_clob, l_amt, l_data);
167
168 END IF;
169
170 END Synthesize_Content_Attachments;
171
172
173 PROCEDURE Synthesize_Attribute_Bundles
174 ( p_attribute_bundle_id IN NUMBER,
175 p_clob IN OUT NOCOPY CLOB) IS
176
177 CURSOR cur_attribute_bundle(p_attribute_bundle_id IN NUMBER) IS
178 SELECT
179 attribute_bundle_data
180 FROM
181 IBC_ATTRIBUTE_BUNDLES att
182 WHERE
183 att.attribute_bundle_id = p_attribute_bundle_id;
184
185 l_attribute_data CLOB;
186 l_data VARCHAR2(32000);
187 l_amt INTEGER;
188 l_clean_xml_doc CLOB;
189 l_clean_html_doc CLOB;
190
191 BEGIN
192
193 Open cur_attribute_bundle(p_attribute_bundle_id);
194 Fetch cur_attribute_bundle Into l_attribute_data;
195 Close cur_attribute_bundle;
196
197 IF l_attribute_data is NOT NULL THEN
198
199 l_data := l_data || l_newline||'<CONTENT_ATTRIBUTE_BUNDLE>';
200 l_amt := LENGTH(l_data);
201 dbms_lob.writeappend(p_clob, l_amt, l_data);
202
203 ctx_doc.policy_filter(policy_name =>'IBC_XML_Policy'
204 ,document =>l_attribute_data --l_file_data varvhar2,clob,blob
205 ,restab =>l_clean_xml_doc
206 ,plaintext =>TRUE);
207
208 l_clean_xml_doc := '<html>' || l_clean_xml_doc;
209
210 ctx_doc.policy_filter(policy_name =>'IBC_HTML_Policy'
211 ,document =>l_clean_xml_doc --l_file_data varvhar2,clob,blob
212 ,restab =>l_clean_html_doc
213 ,plaintext =>TRUE);
214
215 p_clob := p_clob || l_clean_html_doc;
216
217 l_data := l_newline|| '</CONTENT_ATTRIBUTE_BUNDLE>';
218 l_amt := LENGTH(l_data);
219 dbms_lob.writeappend(p_clob, l_amt, l_data);
220
221 END IF;
222
223 END Synthesize_Attribute_Bundles;
224
225 PROCEDURE Synthesize_Content_Keywords
226 ( p_citem_version_id IN NUMBER,
227 p_clob IN OUT NOCOPY CLOB) IS
228
229 CURSOR cur_content_keywords(p_citem_version_id IN NUMBER) IS
230 SELECT
231 keyword
232 FROM
233 IBC_CITEM_VERSIONS_B cb,
234 IBC_CITEM_KEYWORDS k
235 WHERE
236 cb.citem_version_id = p_citem_version_id
237 AND k.content_item_id = cb.content_item_id;
238
239 l_data VARCHAR2(32000);
240 l_amt INTEGER;
241
242 BEGIN
243
244 FOR cur_content_keywords_rec IN cur_content_keywords(p_citem_version_id)
245 LOOP
246 l_data := l_data || l_newline || cur_content_keywords_rec.keyword;
247 l_amt := LENGTH(l_data);
248 IF l_amt >= 31000 THEN
249 -- flush l_data to the p_clob
250 dbms_lob.writeappend(p_clob, l_amt, l_data);
251 l_data := l_newline;
252 END IF;
253 END LOOP;
254
255 IF l_data is NOT NULL THEN
256 l_data := l_newline||'<CONTENT_KEYWORDS>'|| l_data || '</CONTENT_KEYWORDS>';
257 dbms_lob.writeappend(p_clob, LENGTH(l_data), l_data);
258 END IF;
259
260 END Synthesize_Content_Keywords;
261
262 PROCEDURE Synthesize_Content_Renditions
263
264 ( p_citem_version_id IN NUMBER,
265
266 p_clob IN OUT NOCOPY CLOB) IS
267
268
269
270 CURSOR cur_rend_data(p_citem_version_id IN NUMBER) IS
271 SELECT
272 ren.citem_version_id,
273 flob.file_data rendition_file_data,
274 flob.file_name rendition_file_name,
275 flob.file_content_type file_content_type
276 FROM
277 IBC_RENDiTIONS ren,
278 fnd_lobs flob
279 WHERE
280 ren.citem_version_id = p_citem_version_id
281 AND ren.file_id = flob.file_id
282 AND ren.LANGUAGE = USERENV('LANG');
283
284 l_data VARCHAR2(32000);
285 l_amt INTEGER;
286 l_rendition_file_name VARCHAR2(32000);
287 l_document CLOB;
288
289 BEGIN
290
291 l_rendition_file_name := NULL;
292
293 l_data := l_data || l_newline||'<CONTENT_RENDITION>';
294 l_amt := LENGTH(l_data);
295 dbms_lob.writeappend(p_clob, l_amt, l_data);
296
297 FOR cur_rend_data_rec IN cur_rend_data(p_citem_version_id)
298 LOOP
299
300 IF (cur_rend_data_rec.rendition_file_data is NOT NULL) AND (isValidForFilter(cur_rend_data_rec.file_content_type)) THEN
301
302 -- Binary files get filtered as text file
303 -- msword,pdf etc is converted to text format
304 ctx_doc.policy_filter(policy_name =>'IBC_Binary2Text_Filter'
305 ,document =>cur_rend_data_rec.rendition_file_data -- Binary in from FND_LOBS
306 ,restab =>l_document -- Text out from Filter
307 ,plaintext =>TRUE);
308
309 p_clob := p_clob || l_document;
310
311 END IF;
312
313 l_rendition_file_name := l_rendition_file_name || l_newline || cur_rend_data_rec.rendition_file_name;
314 l_amt := LENGTH(l_rendition_file_name);
315 IF l_amt >= 31000 THEN
316 -- flush l_rendition_file_name to the p_clob
317 dbms_lob.writeappend(p_clob, l_amt, l_rendition_file_name);
318 l_rendition_file_name := l_newline;
319 END IF;
320 END LOOP;
321
322 IF p_clob IS NOT NULL THEN
323 l_data := '</CONTENT_RENDITION>' || l_newline;
324 l_amt := LENGTH(l_data);
325 dbms_lob.writeappend(p_clob, l_amt, l_data);
326 END IF;
327
328 If l_rendition_file_name IS NOT NULL THEN
329 l_rendition_file_name := '<RENDITION_FILE_NAME>' || l_newline||l_rendition_file_name ||'</RENDITION_FILE_NAME>';
330 dbms_lob.writeappend(p_clob, LENGTH(l_rendition_file_name), l_rendition_file_name);
331 END IF;
332
333 END Synthesize_Content_Renditions;
334
335
336 end ibc_content_ctx_pkg;