1 PACKAGE OKS_MAIL AUTHID CURRENT_USER AS
2 /* $Header: OKSMAILS.pls 120.0 2005/05/25 18:02:36 appldev noship $ */
3 ----------------------- Customizable Section -----------------------
4
5 -- Customize the SMTP host, port and your domain name below.
6 smtp_host VARCHAR2(256) := FND_PROFILE.VALUE('OKS_SMTP_HOST');
7 smtp_port PLS_INTEGER := FND_PROFILE.VALUE('OKS_SMTP_PORT');
8 wallet_path VARCHAR2(256) := FND_PROFILE.VALUE('OKS_SMTP_DOMAIN');
9
10 -- smtp_host VARCHAR2(256) := 'gmsmtp02.oraclecorp.com';
11 -- smtp_port PLS_INTEGER := 25;
12 -- smtp_domain VARCHAR2(256) := 'oracle.com';
13
14 -- Customize signature that will appear in the email's MIME header.
15 -- Useful for versioning.
16 -- MAILER_ID CONSTANT VARCHAR2(256) := 'Mailer by Oracle 9i UTL_SMTP';
17 MAILER_ID CONSTANT VARCHAR2(256) := 'Oracle Contracts for Service';
18
19 -- A unique string that demarcates boundaries of parts in a multi-part
20 -- email. The string should not appear inside the body of any part of the
21 -- email. Customize this if needed or generate this randomly dynamically.
22 BOUNDARY CONSTANT VARCHAR2(256) := '-----7D81B75CCC90D2974F7A1CBD';
23
24 CRLF CONSTANT VARCHAR2(10) := FND_GLOBAL.LOCAL_CHR(13) ||
25 FND_GLOBAL.LOCAL_CHR(10);
26
27 --------------------- End Customizable Section ---------------------
28
29 FIRST_BOUNDARY CONSTANT VARCHAR2(256) := '--' || BOUNDARY || CRLF;
30 LAST_BOUNDARY CONSTANT VARCHAR2(256) := '--' || BOUNDARY || '--' || CRLF;
31
32 -- A MIME type that denotes multi-part email (MIME) messages.
33 MULTIPART_MIME_TYPE CONSTANT VARCHAR2(256) := 'multipart/mixed; boundary="'||
34 BOUNDARY || '"';
35 MAX_BASE64_LINE_WIDTH CONSTANT PLS_INTEGER := 76 / 4 * 3;
36 NORMAL_PRIORITY PLS_INTEGER := 3;
37
38 TYPE recipient_rec IS RECORD
39 (
40 mail_type VARCHAR2(10),
41 to_email_address VARCHAR2(3000)
42 );
43 TYPE recipient_rec_tbl IS TABLE OF recipient_rec INDEX BY BINARY_INTEGER;
44
45 FUNCTION get_address(mailbox IN VARCHAR2) RETURN VARCHAR2;
46
47 -- Mark a message-part boundary. Set <last> to TRUE for the last boundary.
48 PROCEDURE write_boundary(conn IN OUT NOCOPY utl_smtp.connection,
49 last IN BOOLEAN DEFAULT FALSE);
50
51 -- Write a MIME header
52 PROCEDURE write_mime_header(conn IN OUT NOCOPY utl_smtp.connection,
53 name IN VARCHAR2,
54 value IN VARCHAR2);
55
56 -- A simple email API for sending email in plain text in a single call.
57 PROCEDURE mail(sender IN VARCHAR2,
58 recipient_tbl IN recipient_rec_tbl,
59 subject IN VARCHAR2,
60 message IN VARCHAR2);
61
62 -- Extended email API to send email in HTML or plain text with no size limit.
63 -- First, begin the email by begin_mail(). Then, call write_text() repeatedly
64 -- to send email in ASCII piece-by-piece. Or, call write_mb_text() to send
65 -- email in non-ASCII or multi-byte character set. End the email with
66 -- end_mail().
67 FUNCTION begin_mail(sender IN VARCHAR2,
68 recipient_tbl IN recipient_rec_tbl,
69 subject IN VARCHAR2,
70 mime_type IN VARCHAR2 DEFAULT 'text/plain',
71 priority IN PLS_INTEGER DEFAULT NULL)
72 RETURN utl_smtp.connection;
73
74 -- Write email body in ASCII
75 PROCEDURE write_text(conn IN OUT NOCOPY utl_smtp.connection,
76 message IN VARCHAR2);
77
78 -- Write email body in non-ASCII (including multi-byte). The email body
79 -- will be sent in the database character set.
80 PROCEDURE write_mb_text(conn IN OUT NOCOPY utl_smtp.connection,
81 message IN VARCHAR2);
82
83 -- Write email body in binary
84 PROCEDURE write_raw(conn IN OUT NOCOPY utl_smtp.connection,
85 message IN RAW);
86
87 -- APIs to send email with attachments. Attachments are sent by sending
88 -- emails in "multipart/mixed" MIME format. Specify that MIME format when
89 -- beginning an email with begin_mail().
90
91 -- Send a single text attachment.
92 PROCEDURE attach_text(conn IN OUT NOCOPY utl_smtp.connection,
93 data IN VARCHAR2,
94 mime_type IN VARCHAR2 DEFAULT 'text/plain',
95 inline IN BOOLEAN DEFAULT TRUE,
96 filename IN VARCHAR2 DEFAULT NULL,
97 last IN BOOLEAN DEFAULT FALSE);
98
99 -- Send a binary attachment. The attachment will be encoded in Base-64
100 -- encoding format.
101 PROCEDURE attach_base64(conn IN OUT NOCOPY utl_smtp.connection,
102 data IN RAW,
103 mime_type IN VARCHAR2 DEFAULT 'application/pdf',
104 inline IN BOOLEAN DEFAULT TRUE,
105 filename IN VARCHAR2 DEFAULT NULL,
106 last IN BOOLEAN DEFAULT FALSE);
107
108 -- Send an attachment with no size limit. First, begin the attachment
109 -- with begin_attachment(). Then, call write_text repeatedly to send
110 -- the attachment piece-by-piece. If the attachment is text-based but
111 -- in non-ASCII or multi-byte character set, use write_mb_text() instead.
112 -- To send binary attachment, the binary content should first be
113 -- encoded in Base-64 encoding format using the demo package for 8i,
114 -- or the native one in 9i. End the attachment with end_attachment.
115 PROCEDURE begin_attachment(conn IN OUT NOCOPY utl_smtp.connection,
116 mime_type IN VARCHAR2 DEFAULT 'text/plain',
117 inline IN BOOLEAN DEFAULT TRUE,
118 filename IN VARCHAR2 DEFAULT NULL,
119 transfer_enc IN VARCHAR2 DEFAULT NULL);
120
121 -- End the attachment.
122 PROCEDURE end_attachment(conn IN OUT NOCOPY utl_smtp.connection,
123 last IN BOOLEAN DEFAULT FALSE);
124
125 -- End the email.
126 PROCEDURE end_mail(conn IN OUT NOCOPY utl_smtp.connection);
127
128 -- Extended email API to send multiple emails in a session for better
129 -- performance. First, begin an email session with begin_session.
130 -- Then, begin each email with a session by calling begin_mail_in_session
131 -- instead of begin_mail. End the email with end_mail_in_session instead
132 -- of end_mail. End the email session by end_session.
133 FUNCTION begin_session RETURN utl_smtp.connection;
134
135 -- Begin an email in a session.
136 PROCEDURE begin_mail_in_session(conn IN OUT NOCOPY utl_smtp.connection,
137 sender IN VARCHAR2,
138 recipient_tbl IN recipient_rec_tbl,
139 subject IN VARCHAR2,
140 mime_type IN VARCHAR2 DEFAULT 'text/plain',
141 priority IN PLS_INTEGER DEFAULT NULL);
142
143 -- End an email in a session.
144 PROCEDURE end_mail_in_session(conn IN OUT NOCOPY utl_smtp.connection);
145
146 -- End an email session.
147 PROCEDURE end_session(conn IN OUT NOCOPY utl_smtp.connection);
148
149 -- This is the main program. It will call the other procedures to send the
150 -- attachment over.
151 PROCEDURE send_binary_attachment(sender IN VARCHAR2,
152 recipient_tbl IN recipient_rec_tbl,
153 subject IN VARCHAR2,
154 mail_text IN VARCHAR2,
155 mime_type IN VARCHAR2 DEFAULT 'application/pdf',
156 priority IN PLS_INTEGER DEFAULT NORMAL_PRIORITY,
157 path_name IN VARCHAR2,
158 file_name IN VARCHAR2
159 );
160
161
162 ------------------------------------------------------------------------
163 -- This procedure takes a URL which yields a PDF document and sends the
164 -- retrieved document as an attachment to the email.
165 ------------------------------------------------------------------------
166 PROCEDURE send_attachment(sender IN VARCHAR2,
167 recipient_tbl IN recipient_rec_tbl,
168 subject IN VARCHAR2,
169 mail_text IN VARCHAR2,
170 mime_type IN VARCHAR2 DEFAULT 'application/pdf',
171 priority IN PLS_INTEGER DEFAULT NORMAL_PRIORITY,
172 url IN VARCHAR2,
173 file_name IN VARCHAR2
174 );
175
176 PROCEDURE send_attachment ( sender IN VARCHAR2,
177 recipient_tbl IN recipient_rec_tbl,
178 subject IN VARCHAR2,
179 mail_text IN VARCHAR2,
180 mime_type IN VARCHAR2 DEFAULT 'text/plain',
181 priority IN PLS_INTEGER DEFAULT NORMAL_PRIORITY,
182 document IN CLOB,
183 file_name IN VARCHAR2
184 );
185
186 PROCEDURE send_attachment ( sender IN VARCHAR2,
187 recipient_tbl IN recipient_rec_tbl,
188 subject IN VARCHAR2,
189 mail_text IN CLOB,
190 mime_type IN VARCHAR2 DEFAULT 'text/plain',
191 priority IN PLS_INTEGER DEFAULT NORMAL_PRIORITY,
192 document IN CLOB,
193 file_name IN VARCHAR2
194 );
195
196 PROCEDURE send_attachment ( sender IN VARCHAR2,
197 recipient_tbl IN recipient_rec_tbl,
198 subject IN VARCHAR2,
199 mail_text IN OKS_AUTO_REMINDER.message_rec_tbl,
200 mime_type IN VARCHAR2 DEFAULT 'text/plain',
201 priority IN PLS_INTEGER DEFAULT NORMAL_PRIORITY,
202 document IN OKS_AUTO_REMINDER.message_rec_tbl,
203 file_name IN VARCHAR2
204 );
205
206 PROCEDURE send_text_attachment ( sender IN VARCHAR2,
207 recipient_tbl IN recipient_rec_tbl,
208 subject IN VARCHAR2,
209 mail_text IN VARCHAR2,
210 mime_type IN VARCHAR2 DEFAULT 'text/plain',
211 priority IN PLS_INTEGER DEFAULT NORMAL_PRIORITY,
212 document IN VARCHAR2,
213 file_name IN VARCHAR2
214 );
215
216 PROCEDURE send_mail (
217 sender IN VARCHAR2,
218 recipient_tbl IN recipient_rec_tbl,
219 subject IN VARCHAR2,
220 mail_text IN VARCHAR2,
221 mime_type IN VARCHAR2 DEFAULT 'text/plain',
222 priority IN PLS_INTEGER DEFAULT NORMAL_PRIORITY
223 );
224
225 END OKS_MAIL;