[Home] [Help]
PACKAGE BODY: APPS.OKC_WORD_DOWNLOAD_UPLOAD
Source
1 PACKAGE BODY okc_word_download_upload AS
2 /* $Header: OKCWDUPB.pls 120.2.12020000.6 2012/12/12 10:37:09 skavutha ship $ */
3 ------------------------------------------------------------------------------
4 -- GLOBAL CONSTANTS
5 ------------------------------------------------------------------------------
6 G_PKG_NAME CONSTANT VARCHAR2(200) := 'OKC_WORD_DOWNLOAD_UPLOAD';
7 G_APP_NAME CONSTANT VARCHAR2(3) := OKC_API.G_APP_NAME;
8
9 G_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
10 G_MODULE CONSTANT VARCHAR2(250) := 'okc.plsql.'||G_PKG_NAME||'.';
11 G_APPLICATION_ID CONSTANT NUMBER :=510; -- OKC Application
12
13 G_FALSE CONSTANT VARCHAR2(1) := FND_API.G_FALSE;
14 G_TRUE CONSTANT VARCHAR2(1) := FND_API.G_TRUE;
15
16 G_RET_STS_SUCCESS CONSTANT VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
17 G_RET_STS_ERROR CONSTANT VARCHAR2(1) := FND_API.G_RET_STS_ERROR;
18 G_RET_STS_UNEXP_ERROR CONSTANT VARCHAR2(1) := FND_API.G_RET_STS_UNEXP_ERROR;
19
20 G_UNEXPECTED_ERROR CONSTANT VARCHAR2(200) := 'OKC_UNEXPECTED_ERROR';
21 G_SQLERRM_TOKEN CONSTANT VARCHAR2(200) := 'ERROR_MESSAGE';
22 G_SQLCODE_TOKEN CONSTANT VARCHAR2(200) := 'ERROR_CODE';
23 G_UNABLE_TO_RESERVE_REC CONSTANT VARCHAR2(200) := OKC_API.G_UNABLE_TO_RESERVE_REC;
24
25
26 --=========================================================================================
27 --============================DOWNLOAD PROCEDURES BEGINS===================================
28 --=========================================================================================
29 FUNCTION variable_custom_replace(p_source CLOB,l_search_string VARCHAR2,l_replacing_string VARCHAR) RETURN VARCHAR2 AS
30 l_source CLOB;
31 l_var_occurance NUMBER := 1;
32 l_var_start_position NUMBER := -1;
33 l_var_end_position NUMBER := -1;
34 l_wt_occurance NUMBER := 1;
35 l_wt_start_position NUMBER := -1;
36 l_wt_end_position NUMBER := -1;
37 l_process_string VARCHAR2(4000);
38 l_process_string_temp VARCHAR2(4000);
39 l_var_name VARCHAR2(1000);
40 BEGIN
41 l_source := p_source;
42 l_var_start_position := InStr(l_source,'agsfddfsga1',1,l_var_occurance);
43
44 WHILE(l_var_start_position <> 0)
45 LOOP
46 l_var_end_position := InStr(l_source,'agsfddfsga2',1,l_var_occurance);
47 l_process_string_temp := SubStr(l_source,l_var_start_position+11,l_var_end_position-l_var_start_position-11); -- Length(agsfddfsga1) = 11
48 l_process_string := '<w:t>' || l_process_string_temp || '</w:t>';
49
50 --forming the actual variable name
51 -- start
52 l_wt_occurance := 1;
53 l_var_name := '';
54 l_wt_start_position := InStr(l_process_string,'<w:t>',1,l_wt_occurance);
55
56 WHILE(l_wt_start_position <> 0)
57 LOOP
58 l_wt_end_position := InStr(l_process_string,'</w:t>',1,l_wt_occurance);
59 l_var_name := l_var_name || SubStr(l_process_string,l_wt_start_position+5,l_wt_end_position-l_wt_start_position-5); -- Length('<w:t>') = 5
60 l_wt_occurance := l_wt_occurance + 1;
61 l_wt_start_position := InStr(l_process_string,'<w:t>',1,l_wt_occurance);
62 END LOOP;
63 l_source := regexp_replace(l_source,l_process_string_temp,l_var_name);
64 -- end
65
66 l_var_occurance := l_var_occurance + 1;
67 l_var_start_position := InStr(l_source,'agsfddfsga1',1,l_var_occurance);
68 END LOOP;
69 RETURN regexp_replace(l_source,l_search_string,l_replacing_string);
70 END variable_custom_replace;
71
72 /*PROCEDURE save_data ()
73 IS
74 PRAGMA autonomous_transaction
75
76 BEGIN
77 INSERT INTO OKC_WORD_SYNC_T(
78 )
79 VALUES ();
80 END save_data; */
81
82 PROCEDURE DOWNLOAD_PRE_PROCESSOR (
83 p_doc_id NUMBER,
84 p_doc_type VARCHAR2,
85 x_return_status OUT NOCOPY VARCHAR2,
86 x_msg_data OUT NOCOPY VARCHAR2
87 ) AS
88 l_api_name VARCHAR2(30) := 'DOWNLOAD_PRE_PROCESSOR';
89 p_document_xml CLOB;
90 l_doc_clob CLOB;
91 l_doc_XML XMLType;
92 l_art_XML XMLType;
93 l_xpath_clause_elem VARCHAR2(1000);
94 l_xpath_temp VARCHAR2(1000);
95
96 l_i NUMBER := 1;
97
98 l_progress VARCHAR2(3) := '000';
99
100 BEGIN
101
102 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
103 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT ,
104 G_MODULE||l_api_name,
105 '100: Entered '||G_PKG_NAME ||'.'||'DOWNLOAD_PRE_PROCESSOR');
106 END IF;
107
108 -------------------------------------
109 -- Get the XML from the table.
110 -------------------------------------
111 select blob_to_clob(blob_data)
112 into p_document_xml
113 from OKC_WORD_SYNC_T
114 WHERE id = 1
115 AND doc_id = p_doc_id
116 AND doc_type = p_doc_type;
117
118 l_progress := 010;
119
120 p_document_xml := regexp_replace(p_document_xml,'&','~');
121 l_doc_xml := xmltype(p_document_xml);
122
123 --Initialize the xpaths
124 l_xpath_clause_elem := '//SectionsArticlesToPrintVORow['||to_char(l_i)||']';
125
126 while ((l_doc_xml.existsnode(l_xpath_clause_elem) > 0)) LOOP
127
128 l_xpath_temp := l_xpath_clause_elem||'/ArticleText[1]//*[name()="var"]';
129
130 if (l_doc_xml.extract(l_xpath_temp) IS NOT NULL) then
131 l_art_XML := l_doc_xml.extract(l_xpath_temp);
132 l_xpath_temp := l_xpath_clause_elem||'/ArticleText[1]';
133 l_art_xml := xmltype('<ArticleText>'||l_art_xml.getClobVal()||'</ArticleText>');
134 select updateXML(l_doc_xml,l_xpath_temp,l_art_xml) into l_doc_xml FROM dual;
135 /*ELSE
136 l_xpath_temp := l_xpath_clause_elem||'/ArticleText[1]';
137 select updateXML(l_doc_xml,l_xpath_temp,'<ArticleText><p>##ARTICLEWML##</p></ArticleText>') into l_doc_xml FROM dual;
138 */
139 end if;
140 l_i := l_i+1;
141 l_xpath_clause_elem := '//SectionsArticlesToPrintVORow['||to_char(l_i)||']';
142 end loop;
143
144 l_progress := 020;
145
146 SELECT updateXML( l_doc_xml
147 ,'//ArticleText[not(.//var)]','<ArticleText><p>##ARTICLEWML##</p></ArticleText>'
148 ).getClobVal()
149
150 INTO l_doc_clob
151 FROM dual;
152
153 l_doc_clob := regexp_replace(l_doc_clob,'~','&');
154
155 l_progress := 030;
156
157 UPDATE OKC_WORD_SYNC_T
158 SET blob_data = clob_to_blob(l_doc_clob)
159 WHERE id = 1
160 AND doc_id = p_doc_id
161 AND doc_type = p_doc_type;
162
163
164 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
165 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT ,
166 G_MODULE||l_api_name,
167 '100: Leaving '||G_PKG_NAME ||'.'||'DOWNLOAD_PRE_PROCESSOR');
168 END IF;
169 x_return_status := G_RET_STS_SUCCESS;
170 x_msg_data := NULL;
171 EXCEPTION WHEN OTHERS THEN
172 x_return_status := G_RET_STS_ERROR;
173 x_msg_data := SQLCODE||' -ERROR- '||SQLERRM ;
174 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
175 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT ,
176 G_MODULE||l_api_name,
177 '100: Exception in '||G_PKG_NAME ||'.'||'DOWNLOAD_PRE_PROCESSOR');
178
179 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT ,
180 G_MODULE||l_api_name,
181 '100: Exception - at ' || l_progress );
182
183 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT ,
184 G_MODULE||l_api_name,
185 '100: Exception ' || SQLERRM );
186 END IF;
187 END DOWNLOAD_PRE_PROCESSOR;
188
189
190
191
192 PROCEDURE DOWNLOAD_POST_PROCESSOR(
193 p_doc_id NUMBER,
194 p_doc_type VARCHAR2,
195 x_return_status OUT NOCOPY VARCHAR2,
196 x_msg_data OUT NOCOPY VARCHAR2) AS
197
198 l_api_name VARCHAR2(30) := 'DOWNLOAD_POST_PROCESSOR';
199 p_document_xml CLOB;
200 l_doc_clob CLOB;
201 l_doc_XML XMLType;
202 l_art_XML XMLType;
203 l_var_XML XMLType;--Contains all variables in doc xml
204 l_xpath_clause_elem VARCHAR2(1000);
205 l_xpath_temp VARCHAR2(1000);
206 l_clob_temp CLOB;
207 l_i NUMBER := 1;
208
209 l_article_id NUMBER ;
210 l_list_prefix NUMBER;
211
212 l_list_clob CLOB; --Added for Download Perf Improvement Fix.
213
214 l_doc_listdef_clob CLOB ;
215 l_doc_list_clob CLOB ;
216
217 TYPE l_art_body_tbl_type IS TABLE OF CLOB INDEX BY BINARY_INTEGER;
218 l_art_body_tbl l_art_body_tbl_type;
219
220 l_listdefCount number;
221 l_listilfocount number;
222
223 l_doc_xml_upd XMLType;
224
225 l_clause_temp CLOB;
226
227 n number;
228 l_result clob ;
229 l_result2 CLOB;
230
231 -- parameters for fix for insert by reference clauses
232 l_insert_by_reference VARCHAR2(1);
233 l_reference_text VARCHAR2(2000);
234 l_article_text_in_word BLOB;
235
236 -- Added for Code Hook
237 l_return_status VARCHAR2(1);
238 l_msg_count NUMBER;
239 l_msg_data VARCHAR2(2000);
240
241 l_progress VARCHAR2(3) := '000';
242
243 BEGIN
244
245 l_progress := '010';
246
247 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
248 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT ,
249 G_MODULE||l_api_name,
250 '100: Entered '||G_PKG_NAME ||'.'||'DOWNLOAD_POST_PROCESSOR');
251 END IF;
252
253 -- Get the Document XML
254 select blob_to_clob(blob_data) into p_document_xml from OKC_WORD_SYNC_T WHERE id = 1 and doc_id = p_doc_id AND doc_type = p_doc_type;
255
256 l_progress := '020';
257
258 -- Replace w:list with wlist
259 l_doc_clob := regexp_replace(p_document_xml,'w:list','wlist');
260
261 -- Convert the clob into XML for XML Processing
262 l_doc_xml := XMLType(l_doc_clob);
263
264 --Initialize the xpaths
265 l_xpath_clause_elem := '//ClauseTag['||to_char(l_i)||']';
266
267 l_progress := '030';
268 while ((l_doc_xml.existsnode(l_xpath_clause_elem) > 0))
269 LOOP
270
271 -- Get the Cat ID (OKC_K_ARTICLES_B.ID)
272 l_article_id := l_doc_xml.extract(l_xpath_clause_elem||'/@CatId').getNumberVal();
273
274 --Get Article sub elements
275 l_var_XML := Xmltype('<DUMMYTAG>'||l_doc_xml.extract(l_xpath_clause_elem||'/ArticleText[1]/*').getClobVal()||'</DUMMYTAG>');
276
277 l_progress := '040';
278 --Get the clause text from individual clauses Word doc.
279 select VER.INSERT_BY_REFERENCE,VER.REFERENCE_TEXT,VER.article_text_in_word
280 into l_insert_by_reference,l_reference_text,l_article_text_in_word
281 from okc_article_versions VER,okc_k_articles_b ART
282 where ART.id = l_article_id
283 and VER.article_id = ART.sav_sae_id
284 and VER.article_version_id = ART.article_version_id;
285 l_clob_temp := okc_word_download_upload.get_article_body(l_article_text_in_word);
286 IF l_insert_by_reference = 'Y' then
287 l_clob_temp := to_clob('<SOMETAG><wbody><w:p xmlns:w="http://schemas.microsoft.com/office/word/2003/wordml"><w:r><w:t>' || l_reference_text || '</w:t></w:r></w:p></wbody></SOMETAG>');
288 ELSE
289 l_clob_temp := okc_word_download_upload.get_article_body(l_article_text_in_word);
290 END IF;
291
292 --make the list related attributes as unique by appending the position no
293 --w:listDefId="[[:digit:]]{1}" ,w:ilfo="[[:digit:]]{1}",w:ilst w:val="[[:digit:]]{1}" & w:ilfo w:val="[[:digit:]]{1}"
294 if (xmltype(l_clob_temp).existsnode('/SOMETAG/wlists') > 0) then
295 l_list_prefix := l_i+1000;
296 --Remove previously added prefix.
297 /*l_clob_temp := regexp_replace(l_clob_temp,'w:listDefId="([[:digit:]]{4})','w:listDefId="');
298 l_clob_temp := regexp_replace(l_clob_temp,'w:ilfo="([[:digit:]]{4})','w:ilfo="');
299 l_clob_temp := regexp_replace(l_clob_temp,'w:ilst w:val="([[:digit:]]{4})','w:ilst w:val="');
300 l_clob_temp := regexp_replace(l_clob_temp,'w:ilfo w:val="([[:digit:]]{4})','w:ilfo w:val="');*/
301
302 l_clob_temp := regexp_replace(l_clob_temp,'wlistDefId="([[:digit:]]{1})','wlistDefId="'||to_char(l_list_prefix)||'\1');
303 l_clob_temp := regexp_replace(l_clob_temp,'w:ilfo="([[:digit:]]{1})','w:ilfo="'||to_char(l_list_prefix)||'\1');
304 l_clob_temp := regexp_replace(l_clob_temp,'w:ilst w:val="([[:digit:]]{1})','w:ilst w:val="'||to_char(l_list_prefix)||'\1');
305 l_clob_temp := regexp_replace(l_clob_temp,'w:ilfo w:val="([[:digit:]]{1})','w:ilfo w:val="'||to_char(l_list_prefix)||'\1');
306 end if;
307
308 -- Get the article Body text
309 l_art_XML := xmltype(l_clob_temp).extract('/SOMETAG/wbody/*');
310
311 l_progress := '050';
312 -- Resolve the Variables
313 l_art_XML := OKC_WORD_DOWNLOAD_UPLOAD.resolve_variables_download(l_art_XML,l_var_XML);
314 -- Store the resolved clause in a pl/sql table.
315 l_art_body_tbl(l_i) := '<ArticleText>'||l_art_XML.getClobVal()||'</ArticleText>';
316
317
318 if (xmltype(l_clob_temp).existsnode('//wlists') > 0) then
319
320 select deleteXML(XMLType(l_clob_temp),'//wlistDef//wlsid').getClobVal() into l_clob_temp from dual;
321 l_doc_listdef_clob := l_doc_listdef_clob || xmltype(l_clob_temp).extract('//wlistDef').getClobVal();
322 l_doc_list_clob := l_doc_list_clob || xmltype(l_clob_temp).extract('//wlist').getClobVal();
323
324 end if;
325
326 l_i := l_i+1;
327 l_xpath_clause_elem := '//ClauseTag['||to_char(l_i)||']';
328 end loop;
329
330 l_progress := '060';
331 SELECT deleteXML( l_doc_xml
332 ,'//ClauseTag/*'
333 )
334 INTO l_doc_xml
335 FROM dual;
336
337
338 l_progress := '070';
339 -- Clause Processing --serukull changes
340 FOR i IN 1..l_i-1
341 LOOP
342 l_doc_xml := XMLtype.appendChildXML(l_doc_xml,'//ClauseTag['||to_char(i)||']',XMLTYPe(l_art_body_tbl(i)));
343 END LOOP;
344 l_doc_clob := l_doc_xml.getClobVal();
345
346 l_progress := '80';
347
348 l_doc_clob := regexp_replace(l_doc_clob,'</w:styles>','<w:style w:type="table" w:styleId="TableGrid"><w:name w:val="Table Grid"/><w:rsid w:val="00DF2472"/><w:rPr><wx:font wx:val="Times"/><w:lang w:val="EN-US" w:fareast="EN-US" w:bidi="AR-SA"/>
349 </w:rPr><w:tblPr><w:tblInd w:w="0" w:type="dxa"/><w:tblBorders><w:top w:val="single" w:sz="4" wx:bdrwidth="10" w:space="0" w:color="auto"/><w:left w:val="single" w:sz="4" wx:bdrwidth="10" w:space="0" w:color="auto"/><w:bottom w:val="single"
350 w:sz="4" wx:bdrwidth="10" w:space="0" w:color="auto"/><w:right w:val="single" w:sz="4" wx:bdrwidth="10" w:space="0" w:color="auto"/><w:insideH w:val="single" w:sz="4" wx:bdrwidth="10" w:space="0" w:color="auto"/><w:insideV w:val="single"
351 w:sz="4" wx:bdrwidth="10" w:space="0" w:color="auto"/></w:tblBorders><w:tblCellMar><w:top w:w="0" w:type="dxa"/><w:left w:w="108" w:type="dxa"/><w:bottom w:w="0" w:type="dxa"/><w:right w:w="108" w:type="dxa"/></w:tblCellMar></w:tblPr></w:style>
352 </w:styles>');
353
354 l_progress := '090';
355 l_listdefCount := l_doc_xml.extract('//wlistDef[position()=last()]/@wlistDefId').getNumberVal();
356 l_listilfocount := l_doc_xml.extract('//wlist[position()=last()]/@w:ilfo','xmlns:w="http://schemas.microsoft.com/office/word/2003/wordml"').getNumberVal();
357
358 l_progress := '100';
359 -- List processing start --serukull changes
360 n := dbms_lob.instr( l_doc_clob,'</wlistDef>',1,(l_listdefCount+1));
361 n := n+12;
362
363 if ( nvl(n,0) > 12 AND Nvl(dbms_lob.getlength(l_doc_listdef_clob),0) > 0 )
364 THEN
365 dbms_lob.createtemporary(l_result, false, dbms_lob.call);
366 dbms_lob.copy(l_result, l_doc_clob, n - 1, 1, 1);
367 dbms_lob.copy(l_result,
368 l_doc_listdef_clob,
369 dbms_lob.getlength(l_doc_listdef_clob) ,
370 dbms_lob.getlength(l_result) + 1,
371 1 );
372 dbms_lob.copy(l_result,
373 l_doc_clob,
374 dbms_lob.getlength(l_doc_clob) - (n + length('</wlistDef>')) + 1 ,
375 dbms_lob.getlength(l_result) + 1,
376 n + length('</wlistDef>') );
377
378 l_doc_clob := l_result;
379 DBMS_LOB.FREETEMPORARY(l_result);
380 end if;
381 -- ListDef added
382
383 l_progress := '110';
384
385 -- Add wlists --serukull changes Perf Fix
386 n := dbms_lob.instr( l_doc_clob,'</wlist>',1,l_listilfoCount);
387 n := n+8;
388 if ( nvl(n,0) > 8 AND Nvl(dbms_lob.getlength(l_doc_list_clob),0) > 0 )
389 THEN
390 dbms_lob.createtemporary(l_result2, false, dbms_lob.call);
391 dbms_lob.copy(l_result2, l_doc_clob, n - 1, 1, 1);
392 dbms_lob.copy(l_result2,
393 l_doc_list_clob,
394 dbms_lob.getlength(l_doc_list_clob) ,
395 dbms_lob.getlength(l_result2) + 1,
396 1 );
397 dbms_lob.copy(l_result2,
398 l_doc_clob,
399 dbms_lob.getlength(l_doc_clob) - (n + length('</wlist>')) + 1 ,
400 dbms_lob.getlength(l_result2) + 1,
401 n + length('</wlist>') );
402 l_doc_clob := l_result2;
403 DBMS_LOB.FREETEMPORARY(l_result2);
404 end if;
405
406
407 l_doc_clob := regexp_replace(regexp_replace(regexp_replace(regexp_replace(l_doc_clob,'wbody','w:body'),'',
408 ' '),'wlist','w:list'),'<w:t/>','<w:t> </w:t>');
409
410 l_progress := '120';
411 -- Encoding Fix
412 l_doc_clob := change_encoding(l_doc_clob);
413
414 l_progress := '130';
415 -- CALL to Code hook for further processing
416 OKC_WORD_SYNC_HOOK.DOWNLOAD_CONTRACT_EXT(
417 p_doc_type => p_doc_type,
418 p_doc_id => p_doc_id,
419 p_init_msg_list => FND_API.G_FALSE,
420 x_contract_xml => l_doc_clob,
421 x_return_status => l_return_status,
422 x_msg_count => l_msg_count,
423 x_msg_data => l_msg_data
424 );
425 IF l_return_status <> G_RET_STS_SUCCESS THEN
426 raise_application_error(-20101, 'Error in OKC_WORD_SYNC_HOOK.DOWNLOAD_CONTRACT_EXT',TRUE);
427 END IF;
428 -- code to remove custom tags - start
429 select updateXML(xmltype(l_doc_clob),
430 '//SectionTag/@*','',
431 '//ClauseTag/@*','',
432 '//var/@*',''
433 ).getClobVal() into l_doc_clob from dual;
434 l_doc_clob := regexp_replace(l_doc_clob,'<(/)*((SectionsArticlesToPrintVORow)|(TocTag)|(PageHeading)|(SignatureTag)|(LabelTitleTag)|(ArticleText)|(var)|(ClauseTag)|((SectionTag)))[^>.]*>','');
435
436 -- code to remove custom tags - end
437
438
439 l_progress := '140';
440 UPDATE OKC_WORD_SYNC_T
441 SET blob_data = clob_to_blob(l_doc_clob)
442 WHERE id = 1
443 and doc_id = p_doc_id
444 AND doc_type = p_doc_type;
445
446
447 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
448 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT ,
449 G_MODULE||l_api_name,
450 '100: Leaving '||G_PKG_NAME ||'.'||'DOWNLOAD_POST_PROCESSOR');
451 END IF;
455 EXCEPTION WHEN OTHERS THEN
452 x_return_status := G_RET_STS_SUCCESS;
453 x_msg_data := NULL;
454
456
457 x_return_status := G_RET_STS_ERROR;
458 x_msg_data := SQLCODE||' -ERROR- '||SQLERRM ;
459 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
460 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT ,
461 G_MODULE||l_api_name,
462 '100: Exception in '||G_PKG_NAME ||'.'||'DOWNLOAD_POST_PROCESSOR');
463
464 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT ,
465 G_MODULE||l_api_name,
466 ' Exception - at' || l_progress);
467
468 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT ,
469 G_MODULE||l_api_name,
470 ' Exception - ' || SQLERRM);
471 END IF;
472 END DOWNLOAD_POST_PROCESSOR;
473
474
475
476 FUNCTION get_article_body(p_text_in_word IN BLOB) RETURN CLOB IS
477 l_api_name VARCHAR2(30) := 'get_article_body';
478 v_clob CLOB; --Terms
479 v_varchar VARCHAR2(32767);
480 v_start PLS_INTEGER := 1;
481 v_buffer PLS_INTEGER := 32767;
482 l_xpath VARCHAR2(1000);
483
484 BEGIN
485
486 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
487 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT ,
488 G_MODULE||l_api_name,
489 '100: Entered '||G_PKG_NAME ||'.'||'get_article_body');
490 END IF;
491
492 DBMS_LOB.CREATETEMPORARY(v_clob, TRUE);
493
494 FOR i IN 1..CEIL(DBMS_LOB.GETLENGTH(p_text_in_word) / v_buffer) LOOP
495 v_varchar := UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(p_text_in_word, v_buffer, v_start));
496 DBMS_LOB.WRITEAPPEND(v_clob, LENGTH(v_varchar), v_varchar);
497 v_start := v_start + v_buffer;
498 END LOOP;
499
500
501 v_clob := regexp_replace(regexp_replace(regexp_replace(v_clob,'w:body','wbody'),'w:list','wlist'),'w:lvlPicBulletId','wlvlPicBulletId');
502 v_clob := '<SOMETAG>'||xmltype(v_clob).extract('//wbody|//wlists').getClobVal()||'</SOMETAG>';
503 select deleteXML(XMLType(v_clob),'//wlvlPicBulletId').getClobVal() into v_clob from dual;
504
505 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
506 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT ,
507 G_MODULE||l_api_name,
508 '100: Leaving '||G_PKG_NAME ||'.'||'get_article_body');
509 END IF;
510
511 RETURN v_clob;
512
513 EXCEPTION WHEN OTHERS THEN
514 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
515 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT ,
516 G_MODULE||l_api_name,
517 '100: Exception in '||G_PKG_NAME ||'.'||'get_article_body');
518 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT ,
519 G_MODULE||l_api_name,
520 '100: Exception - ' || SQLERRM);
521 END IF;
522
523 END get_article_body;
524
525
526
527 --=========================================================================================
528 --==============================UPLOAD PROCEDURES BEGINS===================================
529 --=========================================================================================
530
531
532 PROCEDURE UPLOAD_PRE_PROCESSOR(p_doc_id NUMBER,p_doc_type VARCHAR2, p_cust_tag_exists IN VARCHAR2 DEFAULT 'Y',
533 x_return_status OUT NOCOPY VARCHAR2,
534 x_msg_data OUT NOCOPY VARCHAR2
535
536 ) IS
537 l_api_name VARCHAR2(30) := 'UPLOAD_PRE_PROCESSOR';
538 l_doc_clob CLOB;
539 l_doc_clob1 CLOB;
540 l_doc_blob BLOB;
541 l_doc_xml XMLType;
542 l_doc_xml_temp XMLType;
543 l_doc_placeholder_xml XMLType;
544 l_dummy_article_text XMLType;
545
546 TYPE id_tbl_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
547 cat_id_tbl id_tbl_type;
548 cat_id1_tbl id_tbl_type;
549 id_tbl id_tbl_type;
550
551 TYPE CLOB_TBL_TYPE IS TABLE OF CLOB INDEX BY BINARY_INTEGER;
552 ART_CLOB_TBL CLOB_TBL_TYPE;
553
554 l_art_CLOB CLOB;
555
556 l_temp_clob CLOB;
557 l_start_string NUMBER;
558 l_end_string NUMBER;
559 l_encoding VARCHAR2(100);
560
561 CURSOR c_mod_encoding IS
562 SELECT id,blob_data
563 FROM okc_word_sync_t
564 WHERE doc_id = p_doc_id
565 AND doc_type = p_doc_type
566 AND cat_id IS NOT NULL;
567
568 l_i NUMBER := 1;
569
570 l_progress VARCHAR2(3) := '000';
571
572 BEGIN
573
574 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
575 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT ,
576 G_MODULE||l_api_name,
577 '100: Entered '||G_PKG_NAME ||'.'||'UPLOAD_PRE_PROCESSOR');
578 END IF;
579
580 -- Get the document data from okc_word_sync_t
581 l_progress := '010';
582 select blob_data into l_doc_blob from okc_word_sync_t where id = 1 and doc_id = p_doc_id and doc_type = p_doc_type;
583
584 -- convert the document to clob
585 l_progress := '020';
586 l_doc_clob := okc_word_download_upload.blob_to_clob(l_doc_blob);
587
588 -- change_encoding
589 l_progress := '030';
590 l_doc_clob := change_encoding(l_doc_clob);
591
592 -- convert the document to XMLType
593 l_progress := '040';
594 l_doc_xml := xmltype(regexp_replace(l_doc_clob,'w:body','wbody'));
595
596 -- Get the place holder xml which is used for adding clause text
597 l_progress := '050';
598 INSERT INTO okc_word_sync_t (id,doc_id,doc_type,CLOB_data)
599 select 10,p_doc_id,p_doc_type, deleteXML(l_doc_xml,'//wbody/*').getclobVal()
600 from dual;
601
602
603 l_doc_xml_temp := XmlType('<wbody>'||l_doc_xml.extract('//wbody[1]/*').getClobVal()||'</wbody>');
604
605
606 -- Build Article CLOBs From the Uploaded Document
607 l_progress := '060';
608 WHILE l_doc_xml.Existsnode('//ClauseTag['||To_Char(l_i)||']') > 0
609 LOOP
610 ID_TBL(l_i) := l_i+100;
611 CAT_ID_TBL(l_i) := l_doc_xml_temp.Extract('//ClauseTag['||To_Char(l_i)||']/@CatId').getNumberVal();
612 ART_CLOB_TBL(l_i) := '<wbody>'||l_doc_xml_temp.Extract('//ClauseTag['||To_Char(l_i)||']/ArticleText[1]/*').getClobVal()||'</wbody>';
613 --ART_CLOB_TBL(l_i) := XMLType.appendChildXML(l_doc_placeholder_xml,'//wbody', l_doc_xml.Extract('//ClauseTag['||To_Char(l_i)||']/ArticleText[1]/*')).GetClobVal();
614
615 IF (XMLType(ART_CLOB_TBL(l_i)).existsnode('//var')>0) THEN
616 l_progress := '070';
617 ART_CLOB_TBL(l_i) := OKC_WORD_DOWNLOAD_UPLOAD.resolve_variables_upload(ART_CLOB_TBL(l_i));
618 END IF;
619 ART_CLOB_TBL(l_i) := regexp_replace(regexp_replace(ART_CLOB_TBL(l_i),'wbody','w:body'),'<w:t/>','<w:t> </w:t>');
620 l_i := l_i+1;
621 END LOOP;
622
623
624 -- Insert the Articles into okc_word_sync_t
625 l_progress := '080';
626 FORALL i IN CAT_ID_TBL.first..CAT_ID_TBL.last
627 INSERT INTO okc_word_sync_t (id,doc_id,doc_type,cat_id,action,CLOB_data)
628 VALUES (id_TBL(i),p_doc_id,p_doc_type,CAT_ID_TBL(i),'NONE',ART_CLOB_TBL(i));
629
630 --Mark new clauses...
631 l_progress := '090';
632 UPDATE OKC_WORD_SYNC_T t0
633 SET ACTION = 'ADDED'
634 --cat_id = NULL
635 WHERE t0.doc_id = p_doc_id
636 AND t0.doc_type = p_doc_type
637 AND ( cat_id = 0 OR
638 EXISTS (select 1 from OKC_WORD_SYNC_T t1
639 where t1.doc_id = p_doc_id
640 and t1.doc_type = p_doc_type
641 and t0.cat_id = t1.cat_id
642 and t1.id < t0.id)
643 );
644
645 -- Compare clauses..
646 l_progress := '100';
647
648 SELECT art.id
649 BULK COLLECT INTO cat_id1_tbl
650 From okc_article_versions VER,okc_k_articles_b ART,okc_word_sync_t st
651 WHERE st.doc_id = p_doc_id
652 AND st.doc_type = p_doc_type
653 AND st.action <> 'ADDED'
654 AND ART.id = st.cat_id
655 and VER.article_id = ART.sav_sae_id
656 and VER.article_version_id = ART.article_version_id
657 AND Dbms_Lob.compare(okc_word_download_upload.get_articleWML_Text(VER.article_text_in_word),okc_word_download_upload.get_articleWML_Text(st.CLOB_data)) <> 0;
658
659 -- Mark the Clauses as 'UPDATED'
660 l_progress := '110';
661 IF cat_id1_tbl.Count>0 THEN
662 FORALL i IN cat_id1_tbl.first..cat_id1_tbl.last
663 UPDATE okc_word_sync_t
664 SET action = 'UPDATED'
665 WHERE doc_id = p_doc_id
666 AND doc_type = p_doc_type
667 AND action <> 'ADDED'
668 AND cat_id=cat_id1_tbl(i);
669 END IF;
670 -- Replace the Article Text with Dummy ARTWML so that the Java Layer Processing becomes lighter.
671 l_progress := '120';
672 l_dummy_article_text := XMLType('<ArticleText><agsfddfsgap><agsfddfsgar><agsfddfsgat>##ARTICLEWML##</agsfddfsgat></agsfddfsgar></agsfddfsgap></ArticleText>');
673 SELECT UpdateXML(l_doc_xml,
674 '//ArticleText', l_dummy_article_text
675 )INTO l_doc_xml FROM dual;
676 l_doc_clob:= regexp_replace(regexp_replace(l_doc_xml.getClobVal(),'agsfddfsga','w:'),'wbody','w:body');
677
678 -- Insert the document into okc_word_sync_t
679 l_progress := '130';
680 insert into okc_word_sync_t(id,doc_id,doc_type,blob_data) values(2,p_doc_id,p_doc_type,okc_word_download_upload.clob_to_blob(l_doc_clob));
681 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
682 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT ,
683 G_MODULE||l_api_name,
684 '100: Leaving '||G_PKG_NAME ||'.'||'UPLOAD_PRE_PROCESSOR');
685 END IF;
686
687 x_return_status := G_RET_STS_SUCCESS;
688 x_msg_data := NULL;
689
690 EXCEPTION WHEN OTHERS THEN
691
692 x_return_status := G_RET_STS_ERROR;
693 x_msg_data := SQLCODE||' -ERROR- '||SQLERRM ;
694
695 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
696 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT ,
697 G_MODULE||l_api_name,
698 '100: Exception in '||G_PKG_NAME ||'.'||'UPLOAD_PRE_PROCESSOR');
699
700 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT ,
701 G_MODULE||l_api_name,
702 '100: Exception at - '||l_progress);
703 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT ,
704 G_MODULE||l_api_name,
705 '100: Exception '|| SQLERRM);
706
707 END IF;
708 END UPLOAD_PRE_PROCESSOR;
709
710 /****
711 * THIS FUNCTION IS NOT BEING CALLED FROM ANY WHERE
712 */
713 FUNCTION UPLOAD_POST_PROCESSOR(p_doc_id NUMBER,p_doc_type VARCHAR2,p_cat_id NUMBER) return BLOB AS
714 l_api_name VARCHAR2(30) := 'UPLOAD_POST_PROCESSOR';
715 l_doc_clob CLOB;
716 l_art_clob CLOB;
717 l_clob_temp CLOB;
718 l_art_XML XMLType;
719 l_art_blob BLOB;
720 l_i NUMBER := 1;
721 l_i_var NUMBER := 1;
722 l_xpath VARCHAR2(100);
723 l_xpath1 VARCHAR2(100);
724 l_xpath2 VARCHAR2(100);
725
726 l_clause_id NUMBER;
727 l_wml_to_html XMLType;
728 t_xml XMLType;
729
730
731 CURSOR get_doc_data is
732 SELECT OKC_WORD_DOWNLOAD_UPLOAD.blob_to_clob(FILE_DATA)
733 FROM OKC_REVIEW_UPLD_HEADER
734 WHERE (DOCUMENT_TYPE,DOCUMENT_ID) IN (SELECT DOCUMENT_TYPE,DOCUMENT_ID
735 FROM OKC_K_ARTICLES_B
736 WHERE ID = p_cat_id);
737
738
739 BEGIN
740
741 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
742 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT ,
743 G_MODULE||l_api_name,
747
744 '100: Entered '||G_PKG_NAME ||'.'||'UPLOAD_POST_PROCESSOR');
745 END IF;
746
748 l_art_clob := '##ARTICLEWML##';
749
750 DBMS_LOB.CREATETEMPORARY(l_doc_clob, TRUE);
751
752 BEGIN
753
754 OPEN get_doc_data;
755 FETCH get_doc_data INTO l_doc_clob;
756 IF get_doc_data%NOTFOUND THEN
757 SELECT OKC_WORD_DOWNLOAD_UPLOAD.blob_to_clob(blob_data) INTO l_doc_clob FROM OKC_WORD_SYNC_T WHERE id = 1 AND doc_id = p_doc_id AND doc_type = p_doc_type;
758 END IF;
759 CLOSE get_doc_data;
760
761 l_doc_clob := regexp_replace(l_doc_clob,'w:body','wbody');
762 l_xpath := '//ClauseTag['||to_char(l_i)||']';
763 l_xpath1 := '//ClauseTag['||to_char(l_i)||']/ArticleText[1]/*';
764 l_xpath2 := '//ClauseTag['||to_char(l_i)||']/@CatId';
765
766 select deleteXML(xmltype(l_doc_clob),'//wbody/*').getClobVal() into l_clob_temp from dual;
767
768 while ((xmltype(l_doc_clob).existsnode(l_xpath) > 0)) LOOP
769
770 IF (xmltype(l_doc_clob).extract(l_xpath2).getNumberVal() = p_cat_id) THEN
771 l_art_XML := xmltype(l_doc_clob).extract(l_xpath1);
772 l_art_CLOB := xmltype.appendChildXML(xmltype(l_clob_temp),'//wbody',l_art_XML).getClobVal();
773 EXIT;
774 END IF;
775
776 l_i := l_i+1;
777 l_xpath := '//ClauseTag['||to_char(l_i)||']';
778 l_xpath1 := '//ClauseTag['||to_char(l_i)||']/ArticleText[1]/*';
779 l_xpath2 := '//ClauseTag['||to_char(l_i)||']/@CatId';
780
781 END LOOP;
782
783 --Replace Variable Tags..
784 --while ((xmltype(l_art_CLOB).existsnode('//var[1]') > 0)) LOOP
785 --l_clob_temp :='<DUMMY>[@'||xmltype(l_art_CLOB).extract('//var[1]/@meaning').getClobVal()||'@]</DUMMY>';
786 --select updateXML(xmltype(l_art_CLOB),'//var[1]',xmltype(l_clob_temp).extract('/DUMMY/*')).getClobVal() into l_art_CLOB from dual;
787 -- select updateXML(xmltype(l_art_CLOB),'//var','<w#r><w#t>[@State of Jurisdiction@]</w#t></w#r>').getClobVal() into l_art_CLOB from dual;
788 --l_art_clob := regexp_replace(l_art_clob,'w#','w:');
789 --END LOOP;
790 --l_art_CLOB := regexp_replace(regexp_replace(l_art_CLOB,'<var name="[0-9,a-z,A-Z,_,$]*" type="[A-Z]*" meaning="','[@'),'"/>','@]');
791
792
793 l_art_CLOB := resolve_variables_upload(l_art_CLOB);
794
795
796 l_art_CLOB := regexp_replace(regexp_replace(l_art_CLOB,'wbody','w:body'),'<w:t/>','<w:t> </w:t>');
797
798
799 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
800 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT ,
801 G_MODULE||l_api_name,
802 '100: Leaving '||G_PKG_NAME ||'.'||'UPLOAD_POST_PROCESSOR');
803 END IF;
804
805 return OKC_WORD_DOWNLOAD_UPLOAD.clob_to_blob(l_art_clob);
806
807 END;
808
809 EXCEPTION WHEN OTHERS THEN
810
811 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
812 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT ,
813 G_MODULE||l_api_name,
814 '100: Exception in '||G_PKG_NAME ||'.'||'UPLOAD_POST_PROCESSOR');
815 END IF;
816
817 return OKC_WORD_DOWNLOAD_UPLOAD.clob_to_blob(l_art_clob);
818
819 END UPLOAD_POST_PROCESSOR;
820
821
822 --=========================================================================================
823 --==============================UTILITY PROCEDURES BEGINS===================================
824 --=========================================================================================
825
826
827 FUNCTION BLOB_TO_CLOB(p_text_in_word IN BLOB) RETURN CLOB
828 IS
829
830 l_api_name VARCHAR2(30) := 'BLOB_TO_CLOB';
831 v_clob CLOB; --Terms
832 v_varchar VARCHAR2(32767);
833 v_start PLS_INTEGER := 1;
834 v_buffer PLS_INTEGER := 32767;
835 l_xpath VARCHAR2(1000);
836
837 BEGIN
838
839 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
840 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT ,
841 G_MODULE||l_api_name,
842 '100: Entered '||G_PKG_NAME ||'.'||'BLOB_TO_CLOB');
843 END IF;
844
845 DBMS_LOB.CREATETEMPORARY(v_clob, TRUE);
846
847 FOR i IN 1..CEIL(DBMS_LOB.GETLENGTH(p_text_in_word) / v_buffer) LOOP
848 v_varchar := UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(p_text_in_word, v_buffer, v_start));
849 DBMS_LOB.WRITEAPPEND(v_clob, LENGTH(v_varchar), v_varchar);
850 v_start := v_start + v_buffer;
851 END LOOP;
852
853 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
854 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT ,
855 G_MODULE||l_api_name,
856 '100: Leaving '||G_PKG_NAME ||'.'||'BLOB_TO_CLOB');
857 END IF;
858
859
860 RETURN v_clob;
861 EXCEPTION WHEN OTHERS THEN
862 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
863 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT ,
864 G_MODULE||l_api_name,
865 '100: Exception in '||G_PKG_NAME ||'.'||'BLOB_TO_CLOB');
866 END IF;
867 END BLOB_TO_CLOB;
868
869
870
871
872
873 FUNCTION Clob_to_blob(p_clob IN CLOB) return BLOB AS
874 l_api_name VARCHAR2(30) := 'Clob_to_blob';
875 l_art_blob BLOB;
876 v_in Pls_Integer := 1;
877 v_out Pls_Integer := 1;
878 v_lang Pls_Integer := 0;
879 v_warning Pls_Integer := 0;
880 BEGIN
881
882 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
883 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT ,
884 G_MODULE||l_api_name,
885 '100: Entered '||G_PKG_NAME ||'.'||'Clob_to_blob');
886 END IF;
887
888 DBMS_LOB.CREATETEMPORARY(l_art_blob, FALSE);
892
889
890 DBMS_LOB.convertToBlob(l_art_blob,p_clob,DBMS_lob.getlength(p_clob),
891 v_in,v_out,DBMS_LOB.default_csid,v_lang,v_warning);
893 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
894 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT ,
895 G_MODULE||l_api_name,
896 '100: Leaving '||G_PKG_NAME ||'.'||'Clob_to_blob');
897 END IF;
898
899 return l_art_blob;
900 EXCEPTION WHEN OTHERS THEN
901 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
902 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT ,
903 G_MODULE||l_api_name,
904 '100: Exception in '||G_PKG_NAME ||'.'||'Clob_to_blob');
905 END IF;
906
907 END Clob_to_blob;
908
909
910 FUNCTION GET_WORD_SYNC_PROFILE RETURN VARCHAR2 IS
911 PROF_VALUE VARCHAR2(1);
912 BEGIN
913 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
914 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT ,
915 G_MODULE||'GET_WORD_SYNC_PROFILE',
916 '100: Entered '||G_PKG_NAME ||'.'||'GET_WORD_SYNC_PROFILE');
917 END IF;
918 SELECT FND_PROFILE.VALUE('OKC_WORD_SYNC_ART_EDIT') INTO PROF_VALUE FROM DUAL;
919 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
920 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT ,
921 G_MODULE||'GET_WORD_SYNC_PROFILE',
922 '100: Leaving '||G_PKG_NAME ||'.'||'GET_WORD_SYNC_PROFILE');
923 END IF;
924 RETURN PROF_VALUE;
925 END GET_WORD_SYNC_PROFILE;
926
927
928
929 PROCEDURE INSERT_WML_TEXT(p_article_version_id NUMBER, p_article_text_in_word BLOB) IS
930 BEGIN
931 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
932 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT ,
933 G_MODULE||'INSERT_WML_TEXT',
934 '100: Entered '||G_PKG_NAME ||'.'||'INSERT_WML_TEXT');
935 END IF;
936 UPDATE okc_article_versions
937 SET article_text_in_word = p_article_text_in_word,
938 edited_in_word = 'Y'
939 WHERE article_version_id = p_article_version_id;
940 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
941 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT ,
942 G_MODULE||'INSERT_WML_TEXT',
943 '100: Leaving '||G_PKG_NAME ||'.'||'INSERT_WML_TEXT');
944 END IF;
945 END INSERT_WML_TEXT;
946
947
948
949 FUNCTION resolve_variables_download(p_art_XML XMLType,p_var_XML XMLType) RETURN XMLType IS
950 l_art_XML XMLType;
951 l_art_clob CLOB;
952 l_xpath VARCHAR2(1000);
953 l_repl_str VARCHAR2(2000);
954 l_var_str VARCHAR2(2000);
955 l_var_clob clob;
956 l_i NUMBER := 1;
957
958 l_xpath1 VARCHAR2(1000);
959 l_var_type VARCHAR2(1);
960 BEGIN
961 l_art_CLOB := p_art_XML.getClobVal();
962 l_art_CLOB := regexp_replace(l_art_CLOB,'[[]','agsfddfsga1');
963 l_art_CLOB := regexp_replace(l_art_CLOB,'[]]','agsfddfsga2');
964 --l_art_CLOB := regexp_replace(regexp_replace(l_art_CLOB,'agsfddfsga1@','</w:t></w:r><var name="'),'@agsfddfsga2','" /><w:r><w:t>');
965
966 l_xpath := '//var['||to_char(l_i)||']';
967
968 while (p_var_XML.existsnode(l_xpath) > 0) LOOP
969
970 l_xpath := '//var['||to_char(l_i)||']/@meaning';
971 l_repl_str := 'agsfddfsga1@'||p_var_XML.extract(l_xpath).getStringVal()||'@agsfddfsga2';
972 l_xpath := '//var['||to_char(l_i)||']';
973 l_xpath1 := '//var['||to_char(l_i)||']/@type';
974 l_var_type := p_var_XML.extract(l_xpath1).getStringVal();
975 IF l_var_type = 'D' THEN
976 -- l_var_clob := p_var_XML.extract(l_xpath).getClobVal();
977 --l_var_clob := '</w:t></w:r></w:p>'||p_var_XML.extract(l_xpath).getClobVal()||'<w:p><w:r><w:t>';
978 -- l_art_CLOB := regexp_replace(l_art_CLOB,l_repl_str,l_var_clob);
979 l_art_clob := p_var_XML.extract(l_xpath).getClobVal();
980 ELSE
981 l_var_str := '</w:t></w:r>'||p_var_XML.extract(l_xpath).getStringVal()||'<w:r><w:t>';
982 l_art_CLOB := variable_custom_replace(l_art_CLOB,l_repl_str,l_var_str);
983 END IF;
984
985 l_i := l_i +1;
986 l_xpath := '//var['||to_char(l_i)||']';
987 end loop;
988 l_art_clob := '<DUMMY>'||l_art_CLOB||'</DUMMY>';
989 l_art_clob := regexp_replace(l_art_clob,'agsfddfsga1','[');
990 l_art_clob := regexp_replace(l_art_clob,'agsfddfsga2',']');
991 l_art_XML := XMLType(l_art_clob).extract('/DUMMY/*');
992 return l_art_XML;
993
994 END resolve_variables_download;
995
996
997 FUNCTION resolve_variables_upload(p_art_CLOB CLOB) RETURN CLOB IS
998 l_art_XML XMLType;
999 l_var_XML XMLType;
1000 l_art_clob CLOB := p_art_CLOB;
1001 l_xpath VARCHAR2(1000);
1002 l_repl_str VARCHAR2(2000);
1003 l_var_str VARCHAR2(2000);
1004 l_i NUMBER := 1;
1005
1006 l_xpath1 VARCHAR2(1000);
1007 l_var_type VARCHAR2(1);
1008 BEGIN
1009
1010 IF (XMLType(l_art_clob).existsnode('//var')>0) THEN
1011 l_art_XML := XMLType(l_art_clob);
1012 l_art_clob := '<DUMMY>'||l_art_XML.extract('//var').getClobVal()||'</DUMMY>';
1013 l_var_XML := XMLType(l_art_clob);
1014
1015 l_xpath := '//var['||to_char(l_i)||']';
1016 l_xpath1 := '//var['||to_char(l_i)||']/@type';
1017
1018 while (l_var_XML.existsnode(l_xpath) > 0) LOOP
1019 l_xpath := '//var['||to_char(l_i)||']/@meaning';
1020 l_var_str := l_var_XML.extract(l_xpath).getStringVal();
1021 l_xpath := '//var[@meaning="'||l_var_str||'"]';
1022 l_var_type := l_var_XML.extract(l_xpath1).getStringVal();
1023 IF l_var_type = 'D' THEN
1024 l_var_str := '<DUMMY><agsfddfsgap><agsfddfsgar><agsfddfsgat>[@'||l_var_str||'@]</agsfddfsgat></agsfddfsgar></agsfddfsgap></DUMMY>';
1025 ELSE
1029 l_i := l_i +1;
1026 l_var_str := '<DUMMY><agsfddfsgar><agsfddfsgat>[@'||l_var_str||'@]</agsfddfsgat></agsfddfsgar></DUMMY>';
1027 END IF;
1028 SELECT updateXML( l_art_XML,l_xpath,xmltype(l_var_str).extract('/DUMMY/*')) INTO l_art_XML FROM dual;
1030 l_xpath := '//var['||to_char(l_i)||']';
1031 end loop;
1032 l_art_clob:= regexp_replace(l_art_XML.getClobVal(),'agsfddfsga','w:');
1033 END IF;
1034 return l_art_clob;
1035 END resolve_variables_upload;
1036
1037
1038
1039 procedure strip_tags (p_doc_id number, p_doc_type varchar2) is
1040 pos1 number;
1041 pos2 number;
1042 len number;
1043 html_clob clob;
1044 stripped_html clob;
1045
1046 begin
1047
1048 select blob_to_clob(blob_data) into html_clob from okc_word_sync_t where id = 3 and doc_id = p_doc_id and doc_type = p_doc_type;
1049
1050 pos1 := regexp_instr(html_clob,'<ClauseText>');
1051 pos2 := regexp_instr(html_clob,'</ClauseText>');
1052
1053 pos1 := pos1+12; --length of the clausetext tag is 12
1054 pos2 := pos2;
1055 len := pos2-pos1;
1056
1057 stripped_html := substr(html_clob,pos1,len);
1058 /*stripped_html := regexp_replace(stripped_html,'''','"'); -- After the Import XSL is applied miscellaneous and <br forWord tags have ' instead of "
1059 stripped_html := regexp_replace(stripped_html,' >','>');
1060 stripped_html := regexp_replace(stripped_html,' />','/>');*/
1061
1062 update okc_word_sync_t
1063 set blob_data = clob_to_blob(stripped_html)
1064 where id = 3
1065 and doc_id = p_doc_id
1066 and doc_type = p_doc_type;
1067
1068 end strip_tags;
1069
1070 -------------------------------------------
1071 -- This procedure is not required
1072 -------------------------------------------
1073 FUNCTION GET_ARTICLE_WML(p_art_clob XMLtype,p_doc_clob CLOB) return BLOB AS
1074 l_api_name VARCHAR2(30) := 'GET_ARTICLE_WML';
1075 l_art_XML XMLType;
1076 l_art_clob CLOB;
1077
1078
1079 BEGIN
1080 SELECT deleteXML(xmltype(p_doc_clob),'//wbody/*').getClobVal() INTO l_art_clob FROM dual;
1081 l_art_XML := p_art_clob.extract('//ArticleText[1]/*');
1082 select appendChildXML(XMLTYPE(l_art_clob),'//wbody',l_art_XML).getClobVal()
1083 into l_art_CLOB from dual;
1084 l_art_CLOB := OKC_WORD_DOWNLOAD_UPLOAD.resolve_variables_upload(l_art_CLOB);
1085 l_art_CLOB := regexp_replace(regexp_replace(l_art_CLOB,'wbody','w:body'),'<w:t/>','<w:t> </w:t>');
1086
1087 return OKC_WORD_DOWNLOAD_UPLOAD.clob_to_blob(l_art_clob);
1088
1089 END GET_ARTICLE_WML;
1090
1091
1092 FUNCTION get_articleWML_Text(p_art_blob BLOB) return CLOB AS
1093 l_api_name VARCHAR2(30) := 'get_articleWML_Text';
1094 l_art_clob CLOB;
1095
1096
1097 BEGIN
1098 l_art_clob := blob_to_clob(p_art_blob);
1099 l_art_clob := regexp_replace(regexp_replace(l_art_clob,'w:body','wbody'),'w:t','wt');
1100 l_art_clob := xmltype(l_art_clob).extract('//wt/text()').getClobVal();
1101 l_art_clob := regexp_replace(l_art_clob,' ','');
1102
1103 return l_art_clob;
1104
1105 END get_articleWML_Text;
1106
1107 FUNCTION get_articleWML_Text(p_art_blob CLOB) return CLOB AS
1108 l_api_name VARCHAR2(30) := 'get_articleWML_Text';
1109 l_art_clob CLOB:=p_art_blob;
1110 BEGIN
1111 --l_art_clob := blob_to_clob(p_art_blob);
1112 l_art_clob := regexp_replace(regexp_replace(l_art_clob,'w:body','wbody'),'w:t','wt');
1113 l_art_clob := xmltype(l_art_clob).extract('//wt/text()').getClobVal();
1114 l_art_clob := regexp_replace(l_art_clob,' ','');
1115 return l_art_clob;
1116 END get_articleWML_Text;
1117
1118
1119
1120 PROCEDURE get_latest_wml (
1121 p_doc_id IN NUMBER,
1122 p_doc_type IN VARCHAR2,
1123 p_cat_id IN NUMBER DEFAULT NULL,
1124 x_action OUT NOCOPY VARCHAR2,
1125 x_wml_blob OUT NOCOPY BLOB,
1126 x_return_status OUT NOCOPY VARCHAR2,
1127 x_msg_data OUT NOCOPY VARCHAR2) AS
1128 t_action VARCHAR2(30);
1129 t_blob_data BLOB;
1130 t_clob_data CLOB;
1131 t_id NUMBER;
1132
1133 CURSOR get_updated_csr IS
1134 SELECT action,clob_data
1135 FROM okc_word_sync_t
1136 WHERE doc_id = p_doc_id
1137 AND doc_type = p_doc_type
1138 AND cat_id = p_cat_id
1139 AND action = 'UPDATED';
1140
1141 /* p_cat_id will be null for the clauses added via 2010 and they will be sent as null
1142 and in okc_word_sync_t they will be loaded as 0. so the nvl check is added
1143 */
1144 CURSOR get_added_csr IS
1145 SELECT id,action,clob_data
1146 FROM okc_word_sync_t
1147 WHERE doc_id = p_doc_id
1148 AND doc_type = p_doc_type
1149 AND cat_id = Nvl(p_cat_id,0)
1150 AND action = 'ADDED'
1151 AND id = (SELECT Min(id) FROM okc_word_sync_t WHERE doc_id = p_doc_id AND doc_type = p_doc_type AND cat_id = Nvl(p_cat_id,0) AND action = 'ADDED');
1152
1153 CURSOR get_none_csr IS
1154 SELECT action
1155 FROM okc_word_sync_t
1156 WHERE doc_id = p_doc_id
1157 AND doc_type = p_doc_type
1158 AND cat_id = p_cat_id
1159 AND action = 'NONE';
1160
1161 CURSOR get_placeholder_xml
1162 IS
1163 SELECT clob_data
1164 FROM okc_word_sync_t
1165 WHERE doc_id = p_doc_id
1166 AND doc_type = p_doc_type
1167 AND id=10;
1168
1169 l_placeholder_xml CLOB;
1170
1171 l_progress VARCHAR2(3);
1172
1173 BEGIN
1174
1175 l_progress := '010';
1176
1177 OPEN get_updated_csr;
1178 FETCH get_updated_csr INTO t_action,t_clob_data;
1179 IF get_updated_csr%FOUND THEN
1180
1181 OPEN get_placeholder_xml;
1182 FETCH get_placeholder_xml INTO l_placeholder_xml;
1183 CLOSE get_placeholder_xml;
1184
1185 l_progress := '020';
1186 t_clob_data := regexp_replace(t_clob_data,'w:body', 'wbody');
1187
1188 l_progress := '030';
1192
1189 l_placeholder_xml := XmlType.appendchildXML(XmlType(l_placeholder_xml),'//wbody',xmltype(t_clob_data).extract('wbody/*')).getClobVal();
1190 l_placeholder_xml := regexp_replace(l_placeholder_xml,'wbody','w:body');
1191
1193 x_action := t_action;
1194 x_wml_blob := clob_to_blob(l_placeholder_xml);
1195
1196 UPDATE okc_word_sync_t
1197 SET action = 'UPDATEDASSIGNED' , clob_data = l_placeholder_xml
1198 WHERE doc_id = p_doc_id
1199 AND doc_type = p_doc_type
1200 AND cat_id = Nvl(p_cat_id,0)
1201 AND action = 'UPDATED';
1202
1203 CLOSE get_updated_csr;
1204
1205 ELSE
1206 l_progress := '040';
1207 OPEN get_none_csr;
1208 FETCH get_none_csr INTO t_action;
1209
1210 IF get_none_csr%FOUND THEN
1211
1212 SELECT article_text INTO t_clob_data FROM okc_article_versions WHERE article_version_id = (SELECT article_version_id FROM okc_k_articles_b WHERE id = p_cat_id);
1213 t_blob_data := clob_to_blob(t_clob_data);
1214
1215 x_action := t_action;
1216 x_wml_blob := t_blob_data;
1217
1218 UPDATE okc_word_sync_t
1219 SET action = 'NONEASSIGNED'
1220 WHERE doc_id = p_doc_id
1221 AND doc_type = p_doc_type
1222 AND cat_id = p_cat_id
1223 AND action = 'NONE';
1224
1225 CLOSE get_none_csr;
1226
1227 ELSE
1228 l_progress := '040';
1229 OPEN get_added_csr;
1230 FETCH get_added_csr INTO t_id,t_action,t_clob_data;
1231 IF get_added_csr%FOUND THEN
1232
1233 OPEN get_placeholder_xml;
1234 FETCH get_placeholder_xml INTO l_placeholder_xml;
1235 CLOSE get_placeholder_xml;
1236
1237 t_clob_data := regexp_replace(t_clob_data,'w:body', 'wbody');
1238 l_placeholder_xml := XmlType.appendchildXML(XmlType(l_placeholder_xml),'//wbody',xmltype(t_clob_data).extract('wbody/*')).GetClobVal();
1239 l_placeholder_xml := regexp_replace(l_placeholder_xml,'wbody','w:body');
1240 x_action := t_action;
1241 x_wml_blob := clob_to_blob(l_placeholder_xml);
1242
1243
1244 UPDATE okc_word_sync_t
1245 SET action = 'ADDEDASSIGNED' , clob_data = l_placeholder_xml
1246 WHERE doc_id = p_doc_id
1247 AND doc_type = p_doc_type
1248 AND cat_id = Nvl(p_cat_id,0)
1249 AND action = 'ADDED'
1250 AND id = t_id;
1251
1252 CLOSE get_added_csr;
1253
1254 END IF;
1255 END IF;
1256 END IF;
1257
1258 x_return_status := G_RET_STS_SUCCESS;
1259 x_msg_data := NULL;
1260
1261 EXCEPTION
1262 WHEN OTHERS THEN
1263
1264 x_return_status := G_RET_STS_ERROR;
1265 x_msg_data := SQLCODE||' -ERROR- '||SQLERRM ;
1266
1267 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1268 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT ,
1269 G_MODULE||'GET_LATEST_WML',
1270 '100: Exception '||G_PKG_NAME ||'.'||'GET_LATEST_WML');
1271 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT ,
1272 G_MODULE||'GET_LATEST_WML',
1273 '100: Exception - '|| SQLERRM );
1274 END IF;
1275 RAISE;
1276 END get_latest_wml;
1277
1278
1279 FUNCTION get_latest_wmlblob (p_doc_id IN NUMBER, p_doc_type IN VARCHAR2, p_cat_id IN NUMBER DEFAULT NULL) RETURN BLOB AS
1280 t_action VARCHAR2(30);
1281 t_blob_data BLOB;
1282 l_id NUMBER;
1283 BEGIN
1284 IF p_cat_id IS NOT NULL THEN
1285 BEGIN
1286 SELECT action,clob_to_blob(clob_data) INTO t_action,t_blob_data FROM okc_word_sync_t
1287 WHERE doc_id = p_doc_id
1288 AND doc_type = p_doc_type
1289 AND cat_id = p_cat_id;
1290
1291 IF t_action = 'NONEASSIGNED' THEN
1292 SELECT article_text_in_word INTO t_blob_data FROM okc_article_versions WHERE article_version_id = (SELECT article_version_id FROM okc_k_articles_b WHERE id = p_cat_id);
1293 END IF;
1294 EXCEPTION
1295 WHEN No_Data_Found THEN
1296 RETURN NULL;
1297 END;
1298
1299
1300 ELSE
1301
1302
1303 BEGIN
1304 SELECT clob_to_blob(clob_data),id INTO t_blob_data,l_id FROM okc_word_sync_t
1305 WHERE doc_id = p_doc_id
1306 AND doc_type = p_doc_type
1307 AND action = 'ADDEDASSIGNED'
1308 AND id = (SELECT Min(id) FROM okc_word_sync_t WHERE doc_id = p_doc_id AND doc_type = p_doc_type AND action = 'ADDEDASSIGNED');
1309
1310 UPDATE okc_word_sync_t
1311 SET action = 'ADDEDDONE'
1312 WHERE id = l_id;
1313 EXCEPTION
1314 WHEN No_Data_Found THEN
1315 RETURN NULL;
1316 END;
1317
1318 END IF;
1319
1320 RETURN t_blob_data;
1321
1322 END get_latest_wmlblob;
1323
1324
1325 procedure get_article_html_for_comp(p_art_ver_id IN NUMBER,p_review_upld_terms_id IN NUMBER,x_art_html OUT NOCOPY BLOB,x_success OUT NOCOPY VARCHAR2) AS
1326 x_art_wml CLOB;
1327
1328 BEGIN
1329
1330 IF p_review_upld_terms_id IS NOT NULL THEN
1331 select wt.clob_data into x_art_wml
1332 from okc_review_upld_terms UPLD,OKC_WORD_SYNC_T WT
1333 where UPLD.REVIEW_UPLD_TERMS_ID = p_review_upld_terms_id
1334 and WT.doc_id = UPLD.document_id
1335 and WT.doc_type = UPLD.DOCUMENT_TYPE
1336 and WT.cat_id = UPLD.object_id
1337 AND wt.action = 'UPDATEDASSIGNED';
1338
1339 ELSE
1340 select okc_word_download_upload.blob_to_clob(article_text_in_word) into x_art_wml
1341 from okc_article_versions VER
1342 where VER.article_version_id = p_art_ver_id
1343 and ver.edited_in_word = 'Y';
1344
1345 END IF;
1346
1347 x_art_wml := convert_wml_to_html_1(x_art_wml);
1348 --x_art_wml := regexp_replace(x_art_wml,'<td/>','<td></td>');
1349 x_art_wml := regexp_replace(x_art_wml,' ','');
1350 x_art_html := okc_word_download_upload.Clob_to_blob(x_art_wml);
1351 x_success := FND_API.G_TRUE;
1352 EXCEPTION
1353 when others then x_success := FND_API.G_FALSE;
1354
1355 END get_article_html_for_comp;
1356
1357 function convert_wml_to_html_1(p_art_wml CLOB) return CLOB as
1358 l_art_html CLOB;
1359 l_tbl CLOB;
1360 begin
1361 l_art_html := regexp_replace(regexp_replace(regexp_replace(regexp_replace(p_art_wml,'w:body','wbody'),'w:p','wp'),'w:t','wt'),'wx:s','wxs');
1362
1363 --Get body
1364 l_art_html := xmltype(l_art_html).extract('//wbody').getClobVal();
1365 --remove section tags seen in docs created using MS Word 2003
1366 while (xmltype(l_art_html).existsnode('//wxsect')>0) loop
1367
1368 l_tbl := '<div>'||xmltype(l_art_html).extract('//wxsect[1]/*').getClobVal()||'</div>';
1369
1370 select updateXML(xmltype(l_art_html),'//wxsect[1]',XMLType(l_tbl).extract('/div/*')).getClobVal() into l_art_html from dual;
1371 end loop;
1372 --Remove subsections
1373 while (xmltype(l_art_html).existsnode('//wxsub-section')>0) loop
1374
1375 l_tbl := '<div>'||xmltype(l_art_html).extract('//wxsub-section[1]/*').getClobVal()||'</div>';
1376
1377 select updateXML(xmltype(l_art_html),'//wxsub-section[1]',XMLType(l_tbl).extract('/div/*')).getClobVal() into l_art_html from dual;
1378 end loop;
1379 --Replace wml tables with html tables
1380 while (xmltype(l_art_html).existsnode('//wtbl')>0) loop
1381 l_tbl := '<TABLE border="2" cellSpacing="0">'||xmltype(l_art_html).extract('//wtbl[1]//wtr').getClobVal()||'</TABLE>';
1382 l_tbl := convert_rows_to_html(l_tbl);
1383 select updateXML(xmltype(l_art_html),'//wtbl[1]',XMLType(l_tbl)).getClobVal() into l_art_html from dual;
1384 end loop;
1385 --Replace paras in subsections
1386 /*while (xmltype(l_art_html).existsnode('//wxsub-section/wp')>0) loop
1387
1388 begin
1389 l_tbl := '<div>'||xmltype(l_art_html).extract('//wxsub-section[1]//wp').getClobVal()||'</div>';
1390 l_tbl := convert_para_to_html(l_tbl);
1391 exception
1392 when others then
1393 l_tbl := '<div><p/></div>';
1394 end;
1395 select updateXML(xmltype(l_art_html),'//wxsub-section[1]',XMLType(l_tbl).extract('/div/*')).getClobVal() into l_art_html from dual;
1396 end loop;
1397 */
1398
1399
1400 --Replace paras
1401
1402 while (xmltype(l_art_html).existsnode('//wp')>0) loop
1403 begin
1404 l_tbl := '<p>'||xmltype(l_art_html).extract('//wp[1]//wt/text()').getClobVal()||'</p>';
1405 exception
1406 when others then
1407 l_tbl := '<p/>';
1408 end;
1409 select updateXML(xmltype(l_art_html),'//wp[1]',XMLType(l_tbl)).getClobVal() into l_art_html from dual;
1410 end loop;
1411
1412 l_art_html := xmltype(l_art_html).extract('//p|//TABLE').getClobVal();
1413 -- l_art_html := regexp_replace(l_art_html,'tr>','p>');
1414 -- l_art_html := regexp_replace(regexp_replace(l_art_html,'<td>',' '),'</td>',' ');
1415 return l_art_html;
1416 end convert_wml_to_html_1;
1417
1418 function convert_rows_to_html(p_table CLOB) return CLOB as
1419 l_table CLOB;
1420 l_row CLOB;
1421 begin
1422 l_table := p_table;
1423 while (xmltype(l_table).existsnode('//wtr')>0) loop
1424 l_row := '<tr>'||xmltype(l_table).extract('//wtr[1]//wtc').getClobVal()||'</tr>';
1425 l_row := convert_cells_to_html(l_row);
1426 select updateXML(xmltype(l_table),'//wtr[1]',XMLType(l_row)).getClobVal() into l_table from dual;
1427 end loop;
1428 return l_table;
1429 end convert_rows_to_html;
1430
1431
1432 function convert_cells_to_html(p_row CLOB) return CLOB as
1433 l_cell CLOB;
1434 l_row CLOB;
1435 begin
1436 l_row := p_row;
1437 while (xmltype(l_row).existsnode('//wtc')>0) loop
1438 begin
1439 l_cell := '<td>'||xmltype(l_row).extract('//wtc[1]//text()').getClobVal()||'</td>';
1440 exception
1441 when others then
1442 l_cell:='<td/>';
1443 end;
1444 select updateXML(xmltype(l_row),'//wtc[1]',XMLType(l_cell)).getClobVal() into l_row from dual;
1445 end loop;
1446 return l_row;
1447 end convert_cells_to_html;
1448
1449 FUNCTION clean_html_diff(p_html_diff CLOB) RETURN CLOB AS
1450 l_html_diff CLOB;
1451 l_tr CLOB;
1452 l_i NUMBER := 1;
1453 l_xpath VARCHAR2(100);
1454 BEGIN
1455 --Ignore span tags around table element..
1456 l_html_diff := p_html_diff;
1457
1458 --l_html_diff := '<DUMMY>'||l_html_diff||'</DUMMY>';
1459 --if (xmltype(l_html_diff).existsnode('//TABLE')>0) THEN
1460 l_html_diff := regexp_replace(regexp_replace(regexp_replace(l_html_diff,'<TABLE border="2" cellSpacing="0">','<TABLESTART/>'),'<TR>','<TRSTART/>'),'</TR>','<TREND/>');
1461 l_html_diff := regexp_replace(l_html_diff,'</TABLE>','<TABLEEND/>');
1462 l_html_diff := '<DUMMY>'||l_html_diff||'</DUMMY>';
1463 l_html_diff := XMLType(l_html_diff).extract('//P|//TABLESTART|//TABLEEND|//TRSTART|//TREND|//TD').getClobVal();
1464 --l_html_diff := '<DUMMY1>'||l_html_diff||'</DUMMY1>';
1465 --l_html_diff := XMLType(l_html_diff).extract('//P|//TABLE').getClobVal();
1466 l_html_diff := '<DUMMY2>'||l_html_diff||'</DUMMY2>';
1467 select deleteXML(XMLType(l_html_diff),'//br').getClobVal() into l_html_diff from dual;
1468 l_html_diff := regexp_replace(regexp_replace(regexp_replace(l_html_diff,'<TABLESTART/>','<TABLE border="1" cellSpacing="0" width="650">'),'<TRSTART/>','<TR>'),'<TREND/>','</TR>');
1469 l_html_diff := regexp_replace(l_html_diff,'<TABLEEND/>','</TABLE>');
1470 l_html_diff := XMLType(l_html_diff).extract('/DUMMY2/*').getClobVal();
1471 l_html_diff := regexp_replace(l_html_diff,'<span style="text-decoration:underline"/>','');
1472 l_html_diff := regexp_replace(l_html_diff,'<span style="text-decoration:line-through"/>','');
1473 /*else
1474 l_html_diff := p_html_diff;
1475 end if;
1476 l_html_diff := '<DUMMY2>'||l_html_diff||'</DUMMY2>';
1477 select deleteXML(XMLType(l_html_diff),'//br').getClobVal() into l_html_diff from dual;
1478 l_html_diff := XMLType(l_html_diff).extract('/DUMMY2/*').getClobVal();*/
1479 return l_html_diff;
1480 END clean_html_diff;
1481
1482 FUNCTION change_encoding (p_clob CLOB) RETURN CLOB is
1483 l_encoding_st NUMBER;
1484 l_encoding_end NUMBER;
1485 l_encoding VARCHAR2(100);
1486 l_clob CLOB;
1487 BEGIN
1488 l_clob := p_clob;
1489 l_encoding_st:=Dbms_Lob.InStr(l_clob,'encoding="',1)+length('encoding="');
1490 l_encoding_end:=Dbms_Lob.InStr(l_clob,'"',l_encoding_st);
1491 l_encoding:=Dbms_Lob.SubStr(l_clob,l_encoding_end-l_encoding_st,l_encoding_st);
1492 IF l_encoding <> 'UTF-8' THEN
1493 l_clob:=REGEXP_REPLACE(l_clob, l_encoding, 'UTF-8',1,1);
1494 END IF;
1495 RETURN l_clob;
1496 END;
1497
1498 END OKC_WORD_DOWNLOAD_UPLOAD;