[Home] [Help]
PACKAGE BODY: APPS.OKS_MAIL
Source
1 PACKAGE BODY OKS_MAIL AS
2 /* $Header: OKSMAILB.pls 120.0 2005/05/25 17:37:39 appldev noship $ */
3
4 -- Return the email address in the mailbox. The format of mailbox
5 -- may be in one of these formats:
6 -- someone@some-domain
7 -- "Someone" <someone@some-domain>
8 -- Someone <someone@some-domain>
9 FUNCTION get_address(mailbox IN VARCHAR2) RETURN VARCHAR2 AS
10 i PLS_INTEGER;
11 str VARCHAR2(256);
12 BEGIN
13 i := instr(mailbox, '<', -1);
14 IF (i > 0) THEN
15 str := substr(mailbox, i + 1);
16 RETURN substr(str, 1, instr(str, '>') - 1);
17 ELSE
18 RETURN mailbox;
19 END IF;
20
21 EXCEPTION
22 WHEN OTHERS THEN
23 FND_FILE.PUT_LINE(FND_FILE.LOG, SQLERRM );
24 END;
25
26 -- Write a MIME header
27 PROCEDURE write_mime_header(conn IN OUT NOCOPY utl_smtp.connection,
28 name IN VARCHAR2,
29 value IN VARCHAR2) IS
30 BEGIN
31 utl_smtp.write_data(conn, name || ': ' || value || CRLF);
32
33 EXCEPTION
34 WHEN OTHERS THEN
35 FND_FILE.PUT_LINE(FND_FILE.LOG, SQLERRM );
36 END;
37
38 -- Mark a message-part boundary. Set <last> to TRUE for the last boundary.
39 PROCEDURE write_boundary(conn IN OUT NOCOPY utl_smtp.connection,
40 last IN BOOLEAN DEFAULT FALSE) AS
41 BEGIN
42 IF (last) THEN
43 utl_smtp.write_data(conn, LAST_BOUNDARY);
44 ELSE
45 utl_smtp.write_data(conn, FIRST_BOUNDARY);
46 END IF;
47 EXCEPTION
48 WHEN OTHERS THEN
49 FND_FILE.PUT_LINE(FND_FILE.LOG, SQLERRM );
50 END;
51
52 ------------------------------------------------------------------------
53 PROCEDURE mail(sender IN VARCHAR2,
54 recipient_tbl IN recipient_rec_tbl,
55 subject IN VARCHAR2,
56 message IN VARCHAR2) IS
57 conn utl_smtp.connection;
58 BEGIN
59 conn := begin_mail(sender, recipient_tbl, subject);
60 write_text(conn, message);
61 end_mail(conn);
62 EXCEPTION
63 WHEN OTHERS THEN
64 FND_FILE.PUT_LINE(FND_FILE.LOG, SQLERRM );
65 END;
66
67 ------------------------------------------------------------------------
68 FUNCTION begin_mail(sender IN VARCHAR2,
69 recipient_tbl IN recipient_rec_tbl,
70 subject IN VARCHAR2,
71 mime_type IN VARCHAR2 DEFAULT 'text/plain',
72 priority IN PLS_INTEGER DEFAULT NULL)
73 RETURN utl_smtp.connection IS
74 conn utl_smtp.connection;
75 BEGIN
76 conn := begin_session;
77 begin_mail_in_session(conn, sender, recipient_tbl, subject, mime_type,
78 priority);
79 RETURN conn;
80
81 EXCEPTION
82 WHEN OTHERS THEN
83 FND_FILE.PUT_LINE(FND_FILE.LOG, SQLERRM );
84 END;
85
86 ------------------------------------------------------------------------
87 PROCEDURE write_text(conn IN OUT NOCOPY utl_smtp.connection,
88 message IN VARCHAR2) IS
89 BEGIN
90 utl_smtp.write_data(conn, message);
91 EXCEPTION
92 WHEN OTHERS THEN
93 FND_FILE.PUT_LINE(FND_FILE.LOG, SQLERRM );
94 END;
95
96 ------------------------------------------------------------------------
97 PROCEDURE write_mb_text(conn IN OUT NOCOPY utl_smtp.connection,
98 message IN VARCHAR2) IS
99 --l_message VARCHAR2(32000);
100 BEGIN
101 utl_smtp.write_raw_data(conn, utl_raw.cast_to_raw(message));
102 --l_message := oks_base64.encode(message);
103 --utl_smtp.write_data(conn, l_message);
104 EXCEPTION
105 WHEN OTHERS THEN
106 FND_FILE.PUT_LINE(FND_FILE.LOG, SQLERRM );
107 END;
108
109 ------------------------------------------------------------------------
110 PROCEDURE write_raw(conn IN OUT NOCOPY utl_smtp.connection,
111 message IN RAW) IS
112 --l_message VARCHAR2(32000);
113 BEGIN
114 utl_smtp.write_raw_data(conn, message);
115 --l_message := oks_base64.encode(message);
116 --utl_smtp.write_data(conn, l_message);
117 EXCEPTION
118 WHEN OTHERS THEN
119 FND_FILE.PUT_LINE(FND_FILE.LOG, SQLERRM );
120 END;
121
122 ------------------------------------------------------------------------
123 PROCEDURE attach_text(conn IN OUT NOCOPY utl_smtp.connection,
124 data IN VARCHAR2,
125 mime_type IN VARCHAR2 DEFAULT 'text/plain',
126 inline IN BOOLEAN DEFAULT TRUE,
127 filename IN VARCHAR2 DEFAULT NULL,
128 last IN BOOLEAN DEFAULT FALSE) IS
129 BEGIN
130 begin_attachment(conn, mime_type, inline, filename);
131 write_text(conn, data);
132 end_attachment(conn, last);
133 EXCEPTION
134 WHEN OTHERS THEN
135 FND_FILE.PUT_LINE(FND_FILE.LOG, SQLERRM );
136 END;
137
138 ------------------------------------------------------------------------
139 PROCEDURE attach_base64(conn IN OUT NOCOPY utl_smtp.connection,
140 data IN RAW,
141 mime_type IN VARCHAR2 DEFAULT 'application/pdf',
142 inline IN BOOLEAN DEFAULT TRUE,
143 filename IN VARCHAR2 DEFAULT NULL,
144 last IN BOOLEAN DEFAULT FALSE) IS
145 i PLS_INTEGER;
146 len PLS_INTEGER;
147 BEGIN
148
149 -- by MK begin_attachment(conn, mime_type, inline, filename, 'base64');
150
151 -- Split the Base64-encoded attachment into multiple lines
152 i := 1;
153 len := utl_raw.length(data);
154 WHILE (i < len) LOOP
155 IF (i + MAX_BASE64_LINE_WIDTH < len) THEN
156 -- @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
157 -- For Oracle 9i, replace oks_base64.encode with the native
158 -- utl_encode.base64_encode:
159 --
160 -- utl_smtp.write_raw_data(conn,
161 -- utl_encode.base64_encode(utl_raw.substr(data, i,
162 -- MAX_BASE64_LINE_WIDTH)));
163 --
164 -- @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
165 utl_smtp.write_data(conn,
166 oks_base64.encode(utl_raw.substr(data, i,
167 MAX_BASE64_LINE_WIDTH)));
168 -- utl_smtp.write_raw_data(conn, utl_raw.substr(data, i, MAX_BASE64_LINE_WIDTH)); -- By MK
169
170 ELSE
171 -- @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
172 -- For Oracle 9i, replace oks_base64.encode with the native
173 -- utl_encode.base64_encode:
174 --
175 -- utl_smtp.write_raw_data(conn,
176 -- utl_encode.base64_encode(utl_raw.substr(data, i)));
177 --
178 -- @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
179 utl_smtp.write_data(conn,
180 oks_base64.encode(utl_raw.substr(data, i)));
181 --utl_smtp.write_raw_data(conn, utl_raw.substr(data, i)); -- By MK
182 END IF;
183 utl_smtp.write_data(conn, CRLF);
184 i := i + MAX_BASE64_LINE_WIDTH;
185 END LOOP;
186
187 -- By MK -- end_attachment(conn, last);
188
189 EXCEPTION
190 WHEN OTHERS THEN
191 FND_FILE.PUT_LINE(FND_FILE.LOG, SQLERRM );
192 END;
193
194 ------------------------------------------------------------------------
195 PROCEDURE begin_attachment(conn IN OUT NOCOPY utl_smtp.connection,
196 mime_type IN VARCHAR2 DEFAULT 'text/plain',
197 inline IN BOOLEAN DEFAULT TRUE,
198 filename IN VARCHAR2 DEFAULT NULL,
199 transfer_enc IN VARCHAR2 DEFAULT NULL) IS
200 BEGIN
201 write_boundary(conn);
202 write_mime_header(conn, 'Content-Type', mime_type);
203
204 IF (filename IS NOT NULL) THEN
205 IF (inline) THEN
206 write_mime_header(conn, 'Content-Disposition',
207 'inline; filename="'||filename||'"');
208 ELSE
209 write_mime_header(conn, 'Content-Disposition',
210 'attachment; filename="'||filename||'"');
211 END IF;
212 END IF;
213
214 IF (transfer_enc IS NOT NULL) THEN
215 write_mime_header(conn, 'Content-Transfer-Encoding', transfer_enc);
216 END IF;
217
218 utl_smtp.write_data(conn, CRLF);
219
220 EXCEPTION
221 WHEN OTHERS THEN
222 FND_FILE.PUT_LINE(FND_FILE.LOG, SQLERRM );
223 END;
224
225 ------------------------------------------------------------------------
226 PROCEDURE end_attachment(conn IN OUT NOCOPY utl_smtp.connection,
227 last IN BOOLEAN DEFAULT FALSE) IS
228 BEGIN
229 utl_smtp.write_data(conn, CRLF);
230 IF (last) THEN
231 write_boundary(conn, last);
232 END IF;
233 EXCEPTION
234 WHEN OTHERS THEN
235 FND_FILE.PUT_LINE(FND_FILE.LOG, SQLERRM );
236 END;
237
238 ------------------------------------------------------------------------
239 PROCEDURE end_mail(conn IN OUT NOCOPY utl_smtp.connection) IS
240 BEGIN
241 end_mail_in_session(conn);
242 end_session(conn);
243 EXCEPTION
244 WHEN OTHERS THEN
245 FND_FILE.PUT_LINE(FND_FILE.LOG, SQLERRM );
246 END;
247
248 ------------------------------------------------------------------------
249 FUNCTION begin_session RETURN utl_smtp.connection IS
250 conn utl_smtp.connection;
251 status UTL_SMTP.REPLY;
252 BEGIN
253 -- open SMTP connection
254 status := utl_smtp.open_connection(smtp_host, smtp_port, conn);
255
256 -- Status code 220 - Service is ready
257 IF (status.code <> 220) THEN
258 FND_FILE.PUT_LINE(FND_FILE.LOG, status.code || ': ' || status.text );
259 END IF;
260
261 status := utl_smtp.helo(conn, smtp_host); -- @@ or EHLO?
262
263 -- Status code 250 - Requested mail action OKAY completed
264 -- Hand shaking working
265 IF (status.code <> 250) THEN
266 FND_FILE.PUT_LINE(FND_FILE.LOG, status.code || ': ' || status.text );
267 END IF;
268 RETURN conn;
269 EXCEPTION
270 WHEN OTHERS THEN
271 FND_FILE.PUT_LINE(FND_FILE.LOG, SQLERRM );
272 END;
273
274 ------------------------------------------------------------------------
275 PROCEDURE begin_mail_in_session(conn IN OUT NOCOPY utl_smtp.connection,
276 sender IN VARCHAR2,
277 recipient_tbl IN recipient_rec_tbl,
278 subject IN VARCHAR2,
279 mime_type IN VARCHAR2 DEFAULT 'text/plain',
280 priority IN PLS_INTEGER DEFAULT NULL) IS
281 l_count NUMBER := 1;
282 BEGIN
283
284 -- Specify sender's address (our server allows bogus address
285 -- as long as it is a full email address ([email protected]).
286 utl_smtp.mail(conn, get_address(sender));
287
288 -- Specify recipient(s) of the email.
289 While l_count <= recipient_tbl.count
290 Loop
291 utl_smtp.rcpt(conn, get_address(recipient_tbl(l_count).to_email_address) );
292 l_count := l_count + 1;
293 End Loop;
294
295 -- Start body of email
296 utl_smtp.open_data(conn);
297
298 -- Set "From" MIME header
299 write_mime_header(conn, 'From', sender);
300
301 -- Set "To" MIME header
302 l_count := 1;
303 While l_count <= recipient_tbl.count
304 Loop
305
306 IF UPPER(NVL(recipient_tbl(l_count).mail_type,'TO')) = 'TO' THEN
307 write_mime_header(conn, 'To', recipient_tbl(l_count).to_email_address);
308 ELSIF UPPER(recipient_tbl(l_count).mail_type) = 'CC' THEN
309 write_mime_header(conn, 'CC', recipient_tbl(l_count).to_email_address);
310 ELSIF UPPER(recipient_tbl(l_count).mail_type) = 'BCC' THEN
311 write_mime_header(conn, 'Bcc', recipient_tbl(l_count).to_email_address);
312 ELSIF UPPER(recipient_tbl(l_count).mail_type) = 'REPLY-TO' THEN
313 write_mime_header(conn, 'Reply-To', recipient_tbl(l_count).to_email_address);
314 END IF;
315
316 l_count := l_count + 1;
317 End Loop;
318
319 -- Set "Subject" MIME header
320 write_mime_header(conn, 'Subject', subject);
321
322 -- Set "Content-Type" MIME header
323 write_mime_header(conn, 'Content-Type', mime_type);
324
325 -- Set "X-Mailer" MIME header
326 write_mime_header(conn, 'X-Mailer', MAILER_ID);
327
328 -- Set priority:
329 -- High Normal Low
330 -- 1 2 3 4 5
331 IF (priority IS NOT NULL) THEN
332 write_mime_header(conn, 'X-Priority', priority);
333 END IF;
334
335 -- Send an empty line to denotes end of MIME headers and
336 -- beginning of message body.
337 utl_smtp.write_data(conn, CRLF);
338
339 IF (mime_type LIKE 'multipart/mixed%') THEN
340 write_text(conn, 'This is a multi-part message in MIME format.' ||
341 CRLF);
342 END IF;
343
344 EXCEPTION
345 WHEN OTHERS THEN
346 FND_FILE.PUT_LINE(FND_FILE.LOG, SQLERRM );
347 END;
348
349 ------------------------------------------------------------------------
350 PROCEDURE end_mail_in_session(conn IN OUT NOCOPY utl_smtp.connection) IS
351 BEGIN
352 utl_smtp.close_data(conn);
353 EXCEPTION
354 WHEN OTHERS THEN
355 FND_FILE.PUT_LINE(FND_FILE.LOG, SQLERRM );
356 END;
357
358 ------------------------------------------------------------------------
359 PROCEDURE end_session(conn IN OUT NOCOPY utl_smtp.connection) IS
360 BEGIN
361 utl_smtp.quit(conn);
362 EXCEPTION
363 WHEN OTHERS THEN
364 FND_FILE.PUT_LINE(FND_FILE.LOG, SQLERRM );
365 END;
366
367 PROCEDURE send_binary_attachment(sender IN VARCHAR2,
368 recipient_tbl IN recipient_rec_tbl,
369 subject IN VARCHAR2,
370 mail_text IN VARCHAR2,
371 mime_type IN VARCHAR2 DEFAULT 'application/pdf',
372 priority IN PLS_INTEGER DEFAULT NORMAL_PRIORITY,
373 path_name IN VARCHAR2,
374 file_name IN VARCHAR2
375 ) IS
376 conn utl_smtp.connection;
377 l_file_loc BFILE;
378 l_raw RAW(32000);
379 l_num INTEGER;
380 l_amount BINARY_INTEGER := 32000;
381 l_offset INTEGER := 1;
382 BEGIN
383 conn := begin_mail(sender => sender,
384 recipient_tbl => recipient_tbl,
385 subject => subject,
389 attach_text(conn, mail_text, 'text/html');
386 mime_type => oks_mail.MULTIPART_MIME_TYPE,
387 priority => priority);
388
390
391 begin_attachment(conn => conn,
392 mime_type => mime_type,
393 inline => TRUE,
394 filename => file_name,
395 transfer_enc => 'base64');
396 ------------ It will upload the physical file ----------------
397 l_file_loc := BFILENAME(path_name, file_name);
398
399 l_num := dbms_lob.getlength(l_file_loc);
400
401 dbms_lob.open(file_loc => l_file_loc,
402 open_mode => dbms_lob.file_readonly);
403
404 while l_offset < l_num Loop
405 dbms_lob.read(file_loc => l_file_loc,
406 amount => l_amount,
407 offset => l_offset,
408 buffer => l_raw);
409 attach_base64(conn, l_raw, mime_type, TRUE, file_name, FALSE);
410 l_offset := l_offset + l_amount;
411 IF (l_offset + l_amount) > l_num Then
412 l_amount := l_num - l_offset;
413 End If;
414
415 End Loop;
416 dbms_lob.fileclose(file_loc => l_file_loc);
417 --------------------------------------------------------------
418 end_attachment(conn);
419 end_mail(conn);
420
421 EXCEPTION
422 WHEN OTHERS THEN
423 FND_FILE.PUT_LINE(FND_FILE.LOG, SQLERRM );
424 END;
425
426
427 --------------------------------------------------------------
428 -- This Procedure takes a URL which yields a PDF document and
429 -- sends the retrieved document as an attachment to the email.
430 --------------------------------------------------------------
431
432 PROCEDURE send_attachment ( sender IN VARCHAR2,
433 recipient_tbl IN recipient_rec_tbl,
434 subject IN VARCHAR2,
435 mail_text IN VARCHAR2,
436 mime_type IN VARCHAR2 DEFAULT 'application/pdf',
437 priority IN PLS_INTEGER DEFAULT NORMAL_PRIORITY,
438 url IN VARCHAR2,
439 file_name IN VARCHAR2
440 ) IS
441
442 conn UTL_SMTP.CONNECTION;
443 buf RAW(32767);
444 pieces UTL_HTTP.HTML_PIECES;
445 wallet_pswd VARCHAR2(2000);
446 j pls_integer;
447
448 BEGIN
449
450 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Host name: ' || smtp_host);
451 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Port : ' || smtp_port);
452
453 conn := begin_mail(sender => sender,
454 recipient_tbl => recipient_tbl,
455 subject => subject,
456 mime_type => oks_mail.MULTIPART_MIME_TYPE,
457 priority => priority);
458
459 attach_text(conn, mail_text);
460
461 begin_attachment(conn => conn,
462 mime_type => mime_type,
463 inline => TRUE,
464 filename => file_name,
465 transfer_enc => 'base64');
466
467 IF (UPPER(SUBSTR(URL, 1,5)) = 'HTTPS') THEN
468
469 IF LENGTH(wallet_path) > 0 THEN
470
471 IF INSTR(wallet_path,'$$') > 0 THEN
472 wallet_pswd := SUBSTR(wallet_path, INSTR(wallet_path,'$$')+2);
473 wallet_path := SUBSTR(wallet_path, 0, INSTR(wallet_path,'$$')-1);
474 ELSE
475 wallet_pswd := NULL;
476 END IF;
477
478 pieces := UTL_HTTP.REQUEST_PIECES(
479 url => URL,
480 max_pieces => 32767,
481 proxy => NULL,
482 wallet_path => wallet_path,
483 wallet_password => wallet_pswd
484 );
485 ELSE
486 FND_MESSAGE.SET_NAME ('OKS', 'OKS_INVALID_WALLET_PATH');
487 FND_FILE.PUT_LINE(FND_FILE.LOG, FND_MESSAGE.GET );
488 END IF;
489
490 ELSE
491 pieces := UTL_HTTP.REQUEST_PIECES(
492 url => URL,
493 max_pieces => 32767,
494 proxy => NULL,
495 wallet_path => NULL,
496 wallet_password => NULL
497 );
498 END IF;
499
500 FOR i IN 1..pieces.count LOOP
501 buf := utl_raw.concat(buf, utl_raw.cast_to_raw(pieces(i)));
502 j := 1;
503 WHILE ((j + 57) < utl_raw.length(buf)) LOOP
504 utl_smtp.write_data(conn,
505 oks_base64.encode(utl_raw.substr(buf, j, 57)) || utl_tcp.crlf);
506 j := j + 57;
507 END LOOP;
508 buf := utl_raw.substr(buf, j);
509 END LOOP;
510
511 IF (buf IS NOT NULL) then
512 utl_smtp.write_data(conn, oks_base64.encode(buf) || utl_tcp.crlf);
513 END IF;
514
515 --------------------------------------------------------
516
517 end_attachment(conn);
521 WHEN OTHERS THEN
518 end_mail(conn);
519
520 EXCEPTION
522 FND_FILE.PUT_LINE(FND_FILE.LOG, SQLERRM );
523 END;
524
525 PROCEDURE send_text_attachment ( sender IN VARCHAR2,
526 recipient_tbl IN recipient_rec_tbl,
527 subject IN VARCHAR2,
528 mail_text IN VARCHAR2,
529 mime_type IN VARCHAR2 DEFAULT 'text/plain',
530 priority IN PLS_INTEGER DEFAULT NORMAL_PRIORITY,
531 document IN VARCHAR2,
532 file_name IN VARCHAR2
533 ) IS
534
535 conn UTL_SMTP.CONNECTION;
536 l_raw RAW(32767);
537
538 BEGIN
539
540 conn := begin_mail(sender => sender,
541 recipient_tbl => recipient_tbl,
542 subject => subject,
543 mime_type => oks_mail.MULTIPART_MIME_TYPE,
544 priority => priority);
545
546 attach_text(
547 conn => conn,
548 data => mail_text,
549 mime_type => mime_type
550 );
551
552 begin_attachment(conn => conn,
553 mime_type => mime_type,
554 inline => TRUE,
555 filename => file_name,
556 transfer_enc => 'base64');
557
558 l_raw := UTL_RAW.CAST_TO_RAW (document);
559 attach_base64(conn, l_raw, mime_type, TRUE, file_name, FALSE);
560
561 end_attachment(conn);
562 end_mail(conn);
563
564 EXCEPTION
565 WHEN OTHERS THEN
566 FND_FILE.PUT_LINE(FND_FILE.LOG, SQLERRM );
567 END;
568
569 PROCEDURE send_attachment ( sender IN VARCHAR2,
570 recipient_tbl IN recipient_rec_tbl,
571 subject IN VARCHAR2,
572 mail_text IN VARCHAR2,
573 mime_type IN VARCHAR2 DEFAULT 'text/plain',
574 priority IN PLS_INTEGER DEFAULT NORMAL_PRIORITY,
575 document IN CLOB,
576 file_name IN VARCHAR2
577 ) IS
578
579 conn UTL_SMTP.CONNECTION;
580 l_raw RAW(3000);
581 l_str VARCHAR2(3000);
582 clob_length INTEGER;
583 offset INTEGER;
584 amount INTEGER;
585
586 BEGIN
587
588 conn := begin_mail(sender => sender,
589 recipient_tbl => recipient_tbl,
590 subject => subject,
591 mime_type => oks_mail.MULTIPART_MIME_TYPE,
592 priority => priority);
593
594 attach_text(
595 conn => conn,
596 data => mail_text,
597 mime_type => mime_type
598 );
599
600 begin_attachment(conn => conn,
601 mime_type => mime_type,
602 inline => FALSE,
603 filename => file_name,
604 transfer_enc => 'base64');
605
606 ------ It will upload the physical file ----------------
607
608 clob_length := dbms_lob.getlength(document);
609 offset := 1;
610
611 WHILE clob_length > 0 LOOP
612
613 IF clob_length < 3000 THEN
614 amount := clob_length;
615 ELSE
616 amount := 3000;
617 END IF;
618
619 dbms_lob.read(document, amount, offset, l_str);
620
621 l_raw := UTL_RAW.CAST_TO_RAW (l_str);
622 attach_base64(conn, l_raw, mime_type, TRUE, file_name, FALSE);
623
624 clob_length := clob_length - 3000;
625 offset := offset + 3000;
626
627 END LOOP;
628
629 --------------------------------------------------------
630
631 end_attachment(conn);
632 end_mail(conn);
633
634 EXCEPTION
635 WHEN OTHERS THEN
636 FND_FILE.PUT_LINE(FND_FILE.LOG, SQLERRM );
637 END;
638
639 PROCEDURE send_attachment ( sender IN VARCHAR2,
640 recipient_tbl IN recipient_rec_tbl,
641 subject IN VARCHAR2,
642 mail_text IN CLOB,
643 mime_type IN VARCHAR2 DEFAULT 'text/plain',
644 priority IN PLS_INTEGER DEFAULT NORMAL_PRIORITY,
645 document IN CLOB,
646 file_name IN VARCHAR2
647 ) IS
648
649 conn UTL_SMTP.CONNECTION;
650 l_raw RAW(32767);
651 l_str VARCHAR2(4000);
652 clob_length INTEGER;
653 offset INTEGER;
654 amount INTEGER;
655
656 BEGIN
657
658 conn := begin_mail(sender => sender,
659 recipient_tbl => recipient_tbl,
660 subject => subject,
661 mime_type => oks_mail.MULTIPART_MIME_TYPE,
665 mime_type => mime_type);
662 priority => priority);
663
664 begin_attachment(conn => conn,
666
667 clob_length := dbms_lob.getlength(mail_text);
668 offset := 1;
669
670 WHILE clob_length > 0 LOOP
671
672 IF clob_length < 2000 THEN
673 amount := clob_length;
674 ELSE
675 amount := 2000;
676 END IF;
677
678 l_str := DBMS_LOB.SUBSTR(mail_text, amount, offset);
679 write_text(conn => conn,
680 message => l_str);
681
682 clob_length := clob_length - 2000;
683 offset := offset + 2000;
684
685 END LOOP;
686 end_attachment(conn);
687
688 begin_attachment(conn => conn,
689 mime_type => mime_type,
690 inline => FALSE,
691 filename => file_name);
692
693 clob_length := dbms_lob.getlength(document);
694 offset := 1;
695
696 WHILE clob_length > 0 LOOP
697
698 IF clob_length < 2000 THEN
699 amount := clob_length;
700 ELSE
701 amount := 2000;
702 END IF;
703
704 l_str := DBMS_LOB.SUBSTR(document, amount, offset);
705 write_text(conn => conn,
706 message => l_str );
707
708 clob_length := clob_length - 2000;
709 offset := offset + 2000;
710
711 END LOOP;
712
713 end_attachment(conn);
714 end_mail(conn);
715
716 EXCEPTION
717 WHEN OTHERS THEN
718 FND_FILE.PUT_LINE(FND_FILE.LOG, SQLERRM );
719 END;
720
721 PROCEDURE send_attachment ( sender IN VARCHAR2,
722 recipient_tbl IN recipient_rec_tbl,
723 subject IN VARCHAR2,
724 mail_text IN OKS_AUTO_REMINDER.message_rec_tbl,
725 mime_type IN VARCHAR2 DEFAULT 'text/plain',
726 priority IN PLS_INTEGER DEFAULT NORMAL_PRIORITY,
727 document IN OKS_AUTO_REMINDER.message_rec_tbl,
728 file_name IN VARCHAR2
729 ) IS
730
731 conn UTL_SMTP.CONNECTION;
732
733 BEGIN
734
735 conn := begin_mail(sender => sender,
736 recipient_tbl => recipient_tbl,
737 subject => subject,
738 mime_type => oks_mail.MULTIPART_MIME_TYPE,
739 priority => priority);
740
741 begin_attachment(
742 conn => conn,
743 mime_type => mime_type
744 );
745
746 FOR i IN 1 .. mail_text.count LOOP
747 IF mail_text(i).description IS NOT NULL THEN
748 write_text(
749 conn => conn,
750 message => trim(mail_text(i).description)
751 );
752 END IF;
753 END LOOP;
754 end_attachment(conn);
755
756 begin_attachment(
757 conn => conn,
758 mime_type => mime_type,
759 inline => FALSE,
760 filename => file_name
761 );
762
763 FOR i IN 1 .. document.count LOOP
764 IF document(i).description IS NOT NULL THEN
765 write_text(
766 conn => conn,
767 message => trim(document(i).description)
768 );
769 END IF;
770 END LOOP;
771
772 end_attachment(conn);
773 end_mail(conn);
774
775 EXCEPTION
776 WHEN OTHERS THEN
777 FND_FILE.PUT_LINE(FND_FILE.LOG, SQLERRM );
778 END;
779
780 PROCEDURE send_mail (
781 sender IN VARCHAR2,
782 recipient_tbl IN recipient_rec_tbl,
783 subject IN VARCHAR2,
784 mail_text IN VARCHAR2,
785 mime_type IN VARCHAR2 DEFAULT 'text/plain',
786 priority IN PLS_INTEGER DEFAULT NORMAL_PRIORITY
787 ) IS
788
789 conn UTL_SMTP.CONNECTION;
790
791 BEGIN
792
793 conn := begin_mail(sender => sender,
794 recipient_tbl => recipient_tbl,
795 subject => subject,
796 mime_type => oks_mail.MULTIPART_MIME_TYPE,
797 priority => priority);
798
799 attach_text(
800 conn => conn,
801 data => mail_text,
802 mime_type => mime_type
803 );
804
805 end_mail(conn);
806
807 EXCEPTION
808 WHEN OTHERS THEN
809 FND_FILE.PUT_LINE(FND_FILE.LOG, SQLERRM );
810 END;
811
812 END OKS_MAIL;