[Home] [Help]
PACKAGE BODY: APPS.IEM_IM_WRAPPERS_PVT
Source
1 PACKAGE BODY IEM_IM_WRAPPERS_PVT as
2 /* $Header: iemvimwb.pls 115.6 2002/12/03 23:47:55 sboorela shipped $*/
3
4 G_PKG_NAME CONSTANT varchar2(30) :='IEM_IM_WRAPPERS_PVT ';
5
6 FUNCTION GetThemes(p_message_id IN INTEGER, p_part IN INTEGER,
7 p_flags IN INTEGER,p_link IN VARCHAR2,
8 p_themes OUT NOCOPY theme_table,
9 p_errtext OUT NOCOPY VARCHAR2) RETURN INTEGER
10 IS
11 p_theme_str VARCHAR2(32000);
12 l_theme_str VARCHAR2(32000);
13 l_work_str VARCHAR2(3000);
14 p_index NUMBER := 0;
15 l_count NUMBER := 1;
16 l_bool BOOLEAN := TRUE;
17 l_loc_t NUMBER;
18 l_loc_s NUMBER;
19 l_status NUMBER;
20
21 l_plsql_block VARCHAR2(2000) := 'BEGIN :x := IM_IMT_API.getthemesw'||p_link||'(:a,:b,:c,:d,:e,:f); END;';
22
23 BEGIN
24 EXECUTE IMMEDIATE l_plsql_block USING OUT l_status, IN p_message_id,IN p_part, IN p_flags,
25 OUT p_theme_str, OUT p_index, OUT p_errtext;
26
27 --DBMS_OUTPUT.PUT_LINE('NUMBER OF THEMES := '||p_index);
28 IF (p_index > 0) THEN
29 WHILE (l_bool) LOOP
30 SELECT INSTR(p_theme_str, '<T>', 1,1) INTO l_loc_t FROM DUAL;
31 SELECT SUBSTR(p_theme_str,1, l_loc_t-1) INTO l_work_str FROM DUAL;
32 SELECT INSTR(l_work_str, '<S>', 1, 1) INTO l_loc_s FROM DUAL;
33 SELECT SUBSTR(l_work_str,1,l_loc_s-1) INTO p_themes(l_count).THEME FROM DUAL;
34 SELECT SUBSTR(l_work_str,l_loc_s+3) INTO p_themes(l_count).WEIGHT FROM DUAL;
35 l_count := l_count+1;
36 SELECT SUBSTR(p_theme_str,l_loc_t+3) INTO l_theme_str FROM DUAL;
37 p_theme_str := l_theme_str;
38 p_index := p_index-1;
39
40 IF (p_index < 1) THEN
41 l_bool := FALSE;
42 END IF;
43 END LOOP;
44 END IF;
45
46 -- Appropriate err_text to be returned
47 -- "" error number to be returned
48
49 return l_status;
50
51 EXCEPTION
52 WHEN OTHERS THEN
53 p_errtext := SUBSTR(p_errtext ||' '||SQLERRM, 1, 254);
54 return l_status;
55 -- DBMS_OUTPUT.PUT_LINE(SQLERRM);
56 END getthemes;
57
58
59 FUNCTION gethighlight(p_message_id IN INTEGER, p_part IN INTEGER,
60 p_flags IN INTEGER, p_text_query IN VARCHAR2,
61 p_link IN VARCHAR2,
62 p_highlight_buf OUT NOCOPY highlight_table,
63 p_errtext OUT NOCOPY VARCHAR2) RETURN INTEGER
64 IS
65 p_highlight_str VARCHAR2(32000);
66 l_highlight_str VARCHAR2(32000);
67 l_work_str VARCHAR2(3000);
68 p_index NUMBER := 0;
69 l_count NUMBER := 1;
70 l_bool BOOLEAN := TRUE;
71 l_loc_t NUMBER;
72 l_loc_s NUMBER;
73 l_status NUMBER;
74 l_plsql_block VARCHAR2(2000) := 'BEGIN :x := IM_IMT_API.gethighlightw'||p_link||'(:a,:b,:c,:d,:e,:f,:g); END;';
75
76 BEGIN
77 EXECUTE IMMEDIATE l_plsql_block USING OUT l_status, IN p_message_id,IN p_part, IN p_flags,
78 IN p_text_query, OUT p_highlight_str, OUT p_index,
79 OUT p_errtext;
80
81 --DBMS_OUTPUT.PUT_LINE('NUMBER OF THEMES := '||p_index);
82 IF (p_index > 0) THEN
83 WHILE (l_bool) LOOP
84 SELECT INSTR(p_highlight_str, '<T>', 1,1) INTO l_loc_t FROM DUAL;
85 SELECT SUBSTR(p_highlight_str,1, l_loc_t-1) INTO l_work_str FROM DUAL;
86 SELECT INSTR(l_work_str, '<S>', 1, 1) INTO l_loc_s FROM DUAL;
87 SELECT SUBSTR(l_work_str,1,l_loc_s-1) INTO p_highlight_buf(l_count).offset FROM DUAL;
88 SELECT SUBSTR(l_work_str,l_loc_s+3) INTO p_highlight_buf(l_count).length FROM DUAL;
89 l_count := l_count+1;
90 SELECT SUBSTR(p_highlight_str,l_loc_t+3) INTO l_highlight_str FROM DUAL;
91 p_highlight_str := l_highlight_str;
92 p_index := p_index-1;
93
94 IF (p_index < 1) THEN
95 l_bool := FALSE;
96 END IF;
97 END LOOP;
98 END IF;
99
100 -- Appropriate err_text to be returned
101 -- "" error number to be returned
102
103 return l_status;
104
105 EXCEPTION
106 WHEN OTHERS THEN
107 p_errtext := SUBSTR(p_errtext ||' '||SQLERRM, 1, 254);
108 return l_status;
109 -- DBMS_OUTPUT.PUT_LINE(SQLERRM);
110 END gethighlight;
111
112
113 FUNCTION getPartlist(p_message_id IN INTEGER,
114 p_link IN VARCHAR2,
115 p_parts OUT NOCOPY att_table ) RETURN INTEGER
116 IS
117 p_part_str VARCHAR2(32000);
118 l_part_str VARCHAR2(32000);
119 l_work_str VARCHAR2(3000);
120 l_temp_str VARCHAR2(3000);
121 p_index NUMBER := 0;
122 l_count NUMBER := 1;
123 l_bool BOOLEAN := TRUE;
124 l_loc_t NUMBER;
125 l_loc_s NUMBER;
126 l_loc_f NUMBER;
127 l_loc_g NUMBER;
128 l_loc_h NUMBER;
129 l_status NUMBER;
130 l_plsql_block VARCHAR2(2000) := 'BEGIN :x := IM_API.getpartlistw'||p_link||'(:a,:b,:c); END;';
131
132 BEGIN
133 EXECUTE IMMEDIATE l_plsql_block USING OUT l_status, IN p_message_id,OUT
134 p_part_str, OUT p_index;
135 IF (p_index > 0) THEN
136 WHILE (l_bool) LOOP
137 SELECT INSTR(p_part_str, '<T>', 1,1) INTO l_loc_t FROM DUAL;
138 SELECT SUBSTR(p_part_str,1, l_loc_t-1) INTO l_work_str FROM DUAL;
139
140 SELECT SUBSTR(p_part_str,l_loc_t+3) INTO l_part_str FROM DUAL;
141 p_part_str := l_part_str;
142 SELECT INSTR(l_work_str, '<S>', 1, 1) INTO l_loc_s FROM DUAL;
143 SELECT SUBSTR(l_work_str,1,l_loc_s-1) INTO p_parts(l_count).part_number FROM DUAL;
144
145 SELECT SUBSTR(l_work_str,l_loc_s+3) INTO l_temp_str FROM DUAL;
146
147 SELECT INSTR(l_temp_str, '<F>', 1, 1) INTO l_loc_f FROM DUAL;
148 SELECT SUBSTR(l_temp_str,1,l_loc_f-1) INTO p_parts(l_count).content_type FROM DUAL;
149
150 SELECT SUBSTR(l_temp_str,l_loc_f+3) INTO l_work_str FROM DUAL;
151
152
153 SELECT INSTR(l_work_str, '<G>', 1, 1) INTO l_loc_g FROM DUAL;
154 SELECT SUBSTR(l_work_str,1, l_loc_g-1) INTO p_parts(l_count).is_binary FROM DUAL;
155
156
157 SELECT SUBSTR(l_work_str,l_loc_g+3) INTO l_temp_str FROM DUAL;
158
159 SELECT INSTR(l_temp_str, '<H>', 1, 1) INTO l_loc_h FROM DUAL;
160 SELECT SUBSTR(l_temp_str,1, l_loc_h-1) INTO p_parts(l_count).att_size FROM DUAL;
161
162 SELECT SUBSTR(l_temp_str,l_loc_h+3) INTO p_parts(l_count).att_name FROM DUAL;
163
164 l_count := l_count+1;
165 p_index := p_index-1;
166
167 IF (p_index < 1) THEN
168 l_bool := FALSE;
169 END IF;
170 END LOOP;
171 END IF;
172
173 -- Appropriate err_text to be returned
174 -- "" error number to be returned
175 return l_status;
176
177 EXCEPTION
178 WHEN OTHERS THEN
179 return l_status;
180 END getpartlist;
181
182
183 FUNCTION getextendedhdrs(p_message_id IN INTEGER,
184 p_link IN VARCHAR2,
185 p_headers OUT NOCOPY header_table ) RETURN INTEGER
186 IS
187 p_header_str VARCHAR2(32000);
188 l_header_str VARCHAR2(32000);
189 l_work_str VARCHAR2(3000);
190 p_index NUMBER := 0;
191 l_count NUMBER := 1;
192 l_bool BOOLEAN := TRUE;
193 l_loc_t NUMBER;
194 l_loc_s NUMBER;
195 l_status NUMBER;
196
197 l_plsql_block VARCHAR2(2000) := 'BEGIN :x := IM_API.getextendedhdrsw'||p_link||'(:a,:b,:c); END;';
198
199 BEGIN
200 EXECUTE IMMEDIATE l_plsql_block USING OUT l_status, IN p_message_id,
201 OUT p_header_str, OUT p_index;
202
203 IF (p_index > 0) THEN
204 WHILE (l_bool) LOOP
205 SELECT INSTR(p_header_str, '<T>', 1,1) INTO l_loc_t FROM DUAL;
206 SELECT SUBSTR(p_header_str,1, l_loc_t-1) INTO l_work_str FROM DUAL;
207 SELECT INSTR(l_work_str, '<S>', 1, 1) INTO l_loc_s FROM DUAL;
208 SELECT SUBSTR(l_work_str,1,l_loc_s-1) INTO p_headers(l_count).hdr_name FROM DUAL;
209 SELECT SUBSTR(l_work_str,l_loc_s+3) INTO p_headers(l_count).hdr_value FROM DUAL;
210 l_count := l_count+1;
211 SELECT SUBSTR(p_header_str,l_loc_t+3) INTO l_header_str FROM DUAL;
212 p_header_str := l_header_str;
213 p_index := p_index-1;
214
215 IF (p_index < 1) THEN
216 l_bool := FALSE;
217 END IF;
218 END LOOP;
219 END IF;
220
221 -- Appropriate err_text to be returned
222 -- "" error number to be returned
223
224 return l_status;
225
226 EXCEPTION
227 WHEN OTHERS THEN
228 return l_status;
229 -- DBMS_OUTPUT.PUT_LINE(SQLERRM);
230 END getextendedhdrs;
231
232 FUNCTION openfolder(p_folder IN VARCHAR2,
233 p_link IN VARCHAR2,
234 p_messages OUT NOCOPY msg_table) RETURN INTEGER
235 IS
236 p_id_str VARCHAR2(32000);
237 l_id_str VARCHAR2(32000);
238 p_index NUMBER := 0;
239 l_count NUMBER := 1;
240 l_bool BOOLEAN := TRUE;
241 l_loc_t NUMBER;
242 l_status NUMBER;
243 l_flag number:=1;
244 l_batchsize number:=300;
245
246 l_plsql_block VARCHAR2(2000) := 'BEGIN :x := IM_API.openfolderw'||p_link||'(:a,:b,:c,:d,:e); END;';
247
248 BEGIN
249 LOOP
250 EXECUTE IMMEDIATE l_plsql_block USING OUT l_status, IN p_folder,
251 OUT p_id_str, OUT p_index,IN l_flag,IN l_batchsize;
252 IF (p_index > 0) THEN
253 WHILE (l_bool) LOOP
254 SELECT INSTR(p_id_str, '<T>', 1,1) INTO l_loc_t FROM DUAL;
255 SELECT SUBSTR(p_id_str,1, l_loc_t-1) INTO p_messages(l_count) FROM DUAL;
256 l_count := l_count+1;
257 SELECT SUBSTR(p_id_str,l_loc_t+3) INTO l_id_str FROM DUAL;
258 p_id_str := l_id_str;
259 p_index := p_index-1;
260 IF (p_index < 1) THEN
261 l_bool := FALSE;
262 END IF;
263 END LOOP;
264 END IF;
265 IF l_flag=1 THEN
266 l_flag:=2;
267 END IF;
268 l_bool:=TRUE;
269 EXIT WHEN l_status=2 or l_status=3;
270 END LOOP;
271 -- Appropriate err_text to be returned
272 -- "" error number to be returned
273 If l_status =2 then
274 l_status:=0;
275 end if;
276 return l_status;
277
278 EXCEPTION
279 WHEN OTHERS THEN
280 return l_status;
281 END openfolder;
282
283 FUNCTION openfoldernew(folder IN VARCHAR2,
284 p_link varchar2,
285 message_records OUT NOCOPY msg_record_table,
286 include_sub IN INTEGER default 1,
287 top_n IN INTEGER DEFAULT 0,
288 top_option IN INTEGER DEFAULT 1) RETURN INTEGER is
289
290 l_status number;
291 l_index number;
292 l_str1 varchar2(32767);
293 l_str2 varchar2(32767);
294 l_str3 varchar2(32767);
295 l_str4 varchar2(32767);
296 l_str5 varchar2(32767);
297 l_str6 varchar2(32767);
298 l_str7 varchar2(32767);
299 l_str8 varchar2(32767);
300 l_str9 varchar2(32767);
301 l_str10 varchar2(32767);
302 l_loc number;
303 l_current_row varchar2(2000);
304 l_rest_rows varchar2(32767);
305 i integer;
306 l_rest_columns varchar2(2000);
307 current_count integer;
308 incr_counter integer;
309 l_plsql_block VARCHAR2(2000) := 'BEGIN :x := IM_API.openfolderneww'||p_link||'(:a,:b,:c,:d,:e,:f,:g,:h,:i,:j,:k,:l,:m,:n,:o); END;';
310
311
312 begin
313 -- l_status := openfolderneww(folder, include_sub, top_n, top_option,
314 --l_str1, l_str2, l_str3, l_str4, l_str5, l_str6, l_str7, l_str8,
315 --l_str9, l_str10, l_index);
316 -- dbms_output.put_line('before calling im');
317
318 EXECUTE IMMEDIATE l_plsql_block USING OUT l_status, IN folder, IN include_sub,
319 IN top_n, in top_option, out l_str1, out l_str2, out l_str3, out l_str4,out l_str5,
320 out l_str6, out l_str7, out l_str8, out l_str9, out l_str10, out l_index;
321
322
323 current_count := 1;
324 incr_counter := 0;
325 i:=1;
326
327 while (i <= l_index) loop
328 if (current_count = 1) then
329 l_loc := instr(l_str1, '<R>', 1,1);
330 if (l_loc = 0)
331 then
332 incr_counter :=1;
333 i:=i-1;
334 else
335 l_current_row :=substr(l_str1,1,l_loc-1);
336 if (l_loc+3 < length(l_str1)) then
337 l_rest_rows :=substr(l_str1,l_loc+3);
338
339 l_str1 := l_rest_rows;
340
341 else l_str1 := 'EOL';
342 -- dbms_output.put_line('str1 is empty now..');
343
344 end if;
345 end if;
346 end if;
347 if (current_count = 2) then
348 l_loc := instr(l_str2, '<R>', 1,1);
349 if (l_loc = 0)
350 then
351 incr_counter :=1;
352 i:=i-1;
353 else
354 l_current_row :=substr(l_str2,1,l_loc-1);
355 if (l_loc+3 < length(l_str2)) then
356 l_rest_rows :=substr(l_str2,l_loc+3);
357
358 l_str2 := l_rest_rows;
359
360 else l_str2 := 'EOL';
361 -- dbms_output.put_line('str2 is empty now..');
362
363 end if;
364 end if;
365 end if;
366 if (current_count = 3) then
367 l_loc := instr(l_str3, '<R>', 1,1);
368 if (l_loc = 0)
369 then
370 incr_counter :=1;
371 i:=i-1;
372 else
373 l_current_row :=substr(l_str3,1,l_loc-1);
374 if (l_loc+3 < length(l_str3)) then
375 l_rest_rows :=substr(l_str3,l_loc+3);
376
377 l_str3 := l_rest_rows;
378
379 else l_str3 := 'EOL';
380 --dbms_output.put_line('str3 is empty now..');
381
382 end if;
383 end if;
384 end if;
385 if (current_count = 4) then
386 l_loc := instr(l_str4, '<R>', 1,1);
387 if (l_loc = 0)
388 then
389 incr_counter :=1;
390 i:=i-1;
391 else
392 l_current_row :=substr(l_str4,1,l_loc-1);
393 if (l_loc+3 < length(l_str4)) then
394 l_rest_rows :=substr(l_str4,l_loc+3);
395
396 l_str4 := l_rest_rows;
397
398 else l_str4 := 'EOL';
399 -- dbms_output.put_line('str4 is empty now..');
400
401 end if;
402 end if;
403 end if;
404 if (current_count = 5) then
405 l_loc := instr(l_str5, '<R>', 1,1);
406 if (l_loc = 0)
407 then
411 l_current_row :=substr(l_str5,1,l_loc-1);
408 incr_counter :=1;
409 i:=i-1;
410 else
412 if (l_loc+3 < length(l_str5)) then
413 l_rest_rows :=substr(l_str5,l_loc+3);
414
415 l_str5 := l_rest_rows;
416
417 else l_str5 := 'EOL';
418 -- dbms_output.put_line('str5 is empty now..');
419
420 end if;
421 end if;
422 end if;
423 if (current_count = 6) then
424 l_loc := instr(l_str6, '<R>', 1,1);
425 if (l_loc = 0)
426 then
427 incr_counter :=1;
428 i:=i-1;
429 else
430 l_current_row :=substr(l_str6,1,l_loc-1);
431 if (l_loc+3 < length(l_str6)) then
432 l_rest_rows :=substr(l_str6,l_loc+3);
433
434 l_str6 := l_rest_rows;
435
436 else l_str6 := 'EOL';
437 -- dbms_output.put_line('str6 is empty now..');
438
439 end if;
440 end if;
441 end if;
442 if (current_count = 7) then
443 l_loc := instr(l_str7, '<R>', 1,1);
444 if (l_loc = 0)
445 then
446 incr_counter :=1;
447 i:=i-1;
448 else
449 l_current_row :=substr(l_str7,1,l_loc-1);
450 if (l_loc+3 < length(l_str7)) then
451 l_rest_rows :=substr(l_str7,l_loc+3);
452
453 l_str7 := l_rest_rows;
454
455 else l_str7 := 'EOL';
456 -- dbms_output.put_line('str7 is empty now..');
457
458 end if;
459 end if;
460 end if;
461 if (current_count = 8) then
462 l_loc := instr(l_str8, '<R>', 1,1);
463 if (l_loc = 0)
464 then
465 incr_counter :=1;
466 i:=i-1;
467 else
468 l_current_row :=substr(l_str8,1,l_loc-1);
469 if (l_loc+3 < length(l_str8)) then
470 l_rest_rows :=substr(l_str8,l_loc+3);
471
472 l_str8 := l_rest_rows;
473
474 else l_str8 := 'EOL';
475 -- dbms_output.put_line('str8 is empty now..');
476
477 end if;
478 end if;
479 end if;
480 if (current_count = 9) then
481 l_loc := instr(l_str9, '<R>', 1,1);
482 if (l_loc = 0)
483 then
484 incr_counter :=1;
485 i:=i-1;
486 else
487 l_current_row :=substr(l_str9,1,l_loc-1);
488 if (l_loc+3 < length(l_str9)) then
489 l_rest_rows :=substr(l_str9,l_loc+3);
490
491 l_str9 := l_rest_rows;
492
493 else l_str9 := 'EOL';
494 -- dbms_output.put_line('str9 is empty now..');
495
496 end if;
497 end if;
498 end if;
499 if (current_count = 10) then
500 l_loc := instr(l_str10, '<R>', 1,1);
501 if (l_loc > 0) then
502 l_current_row :=substr(l_str10,1,l_loc-1);
503 l_rest_rows :=substr(l_str10,l_loc+3);
504 l_str10 := l_rest_rows;
505 end if;
506 end if;
507
508 if (incr_counter = 1)
509 then
510 current_count := current_count +1;
511 -- dbms_output.put_line('advance to the next string..');
512
513 incr_counter := 0;
514 else
515
516 l_loc:=instr(l_current_row,'<C>', 1,1);
517 message_records(i).msg_id:=substr(l_current_row,1,l_loc-1);
518 l_rest_columns:=substr(l_current_row, l_loc+3);
519 l_current_row := l_rest_columns;
520
521 l_loc:=instr(l_current_row,'<C>', 1,1);
522 message_records(i).smtp_msg_id:=substr(l_current_row,1,l_loc-1);
523 l_rest_columns:=substr(l_current_row, l_loc+3);
524 l_current_row := l_rest_columns;
525
526
527 l_loc:=instr(l_current_row,'<C>', 1,1);
528 message_records(i).sender_name:=substr(l_current_row,1,l_loc-1);
529 l_rest_columns:=substr(l_current_row, l_loc+3);
530 l_current_row := l_rest_columns;
531
532 l_loc:=instr(l_current_row,'<C>', 1,1);
533 message_records(i).received_date:=substr(l_current_row,1,l_loc-1);
534 l_rest_columns:=substr(l_current_row, l_loc+3);
535 l_current_row := l_rest_columns;
536
537 l_loc:=instr(l_current_row,'<C>', 1,1);
538 message_records(i).from_str:=substr(l_current_row,1,l_loc-1);
539 l_rest_columns:=substr(l_current_row, l_loc+3);
540 l_current_row := l_rest_columns;
541
542 l_loc:=instr(l_current_row,'<C>', 1,1);
543 message_records(i).to_str:=substr(l_current_row,1,l_loc-1);
544 l_rest_columns:=substr(l_current_row, l_loc+3);
545 l_current_row := l_rest_columns;
546
547 l_loc:=instr(l_current_row,'<C>', 1,1);
548 message_records(i).priority:=substr(l_current_row,1,l_loc-1);
549 l_rest_columns:=substr(l_current_row, l_loc+3);
550 l_current_row := l_rest_columns;
551
552 l_loc:=instr(l_current_row,'<C>', 1,1);
553 message_records(i).replyto:=substr(l_current_row,1,l_loc-1);
554 l_rest_columns:=substr(l_current_row, l_loc+3);
555 l_current_row := l_rest_columns;
556
557 l_loc:=instr(l_current_row,'<C>', 1,1);
558 message_records(i).folder_path:=substr(l_current_row,1,l_loc-1);
559 message_records(i).subject:=substr(l_current_row, l_loc+3);
560 end if;
561
562
563 i:= i+1;
564 end loop;
565 return l_status;
566
567 end;
568 END IEM_IM_WRAPPERS_PVT;