[Home] [Help]
PACKAGE BODY: APPS.IBY_EMAIL_REPORT
Source
1 PACKAGE BODY IBY_EMAIL_REPORT AS
2 /*$Header: ibyvmalb.pls 115.2 2002/11/18 22:22:17 jleybovi noship $*/
3
4 l_userinfo_tbl UserInfo_tbl_type := UserInfo_tbl_type();
5
6 -- Function that will return a string in parenthesis if it is
7 -- negative.
8 FUNCTION get_str( inputVal NUMBER
9 ) RETURN VARCHAR2 IS
10
11 returnStr VARCHAR2(200);
12 l_value NUMBER;
13 BEGIN
14
15 IF( inputVal is NULL) THEN
16 RETURN '0.00';
17 END IF;
18
19 l_value := inputVal;
20 IF( l_value < 0 ) THEN
21 l_value := l_value * -1;
22 returnStr := '(' || TO_CHAR(l_value) || ')';
23 ELSE
24 returnStr := TO_CHAR(l_value);
25 END IF;
26
27 RETURN returnStr;
28
29 END get_str;
30
31 --1. populate_userinfo
32
33 PROCEDURE populate_userinfo( email_users_str VARCHAR2
34 ) IS
35
36 c_delimiter CONSTANT VARCHAR2(1) := ',';
37 c_at CONSTANT VARCHAR2(1) := '@';
38
39 l_char_index NUMBER := 1;
40 l_loop_index NUMBER := 1;
41 pre_delimit_str VARCHAR2(200);
42 post_delimit_str VARCHAR2(200);
43
44 BEGIN
45
46 post_delimit_str := TRIM(email_users_str);
47
48 WHILE (l_char_index > 0) LOOP
49
50 -- Check whether the email conatins a ','.
51 l_char_index := INSTR( post_delimit_str, c_delimiter);
52
53 IF (l_char_index = 0) THEN
54 pre_delimit_str := post_delimit_str;
55 ELSE
56 pre_delimit_str := TRIM(SUBSTR(post_delimit_str, 1, l_char_index - 1));
57 post_delimit_str := TRIM(SUBSTR(post_delimit_str, l_char_index + 1));
58 END IF;
59
60 IF ( LENGTH(pre_delimit_str) <> 0) THEN
61
62 -- Extend the table by one index each time.
63 l_userinfo_tbl.EXTEND;
64
65 IF INSTR( pre_delimit_str, c_at) > 0 THEN
66 l_userinfo_tbl(l_loop_index).username := NULL;
67 l_userinfo_tbl(l_loop_index).emailaddr := pre_delimit_str;
68 l_userinfo_tbl(l_loop_index).usertype := C_USERTYPE_ADHOC;
69 ELSE
70 l_userinfo_tbl(l_loop_index).username := pre_delimit_str;
71 l_userinfo_tbl(l_loop_index).emailaddr := NULL;
72 l_userinfo_tbl(l_loop_index).usertype := C_USERTYPE_REGISTERED;
73 END IF;
74
75 l_loop_index := l_loop_index + 1;
76
77 END IF;
78
79 END LOOP;
80
81 END populate_userinfo;
82
83 --------------------------------------------------------------------------------------
84 -- 2. Send_Mail
85 -- Start of comments
86 -- API name : Send_Mail
87 -- Type : Public
88 -- Pre-reqs : None
89 -- Function : Sends an email report.
90 -- Parameters :
91 -- IN : p_item_key IN VARCHAR2
92 -- p_user_name IN VARCHAR2
93 --
94 -- OUT : x_return_status OUT VARCHAR2
95 -- x_msg_count OUT VARCHAR2
96 -- Version :
97 -- Current version 1.0
98 -- Previous version 1.0
99 -- Initial version 1.0
100 -- End of comments
101 --------------------------------------------------------------------------------------
102
103 --2. Send Mail
104
105 PROCEDURE Send_Mail ( p_item_key IN VARCHAR2,
106 p_user_name IN VARCHAR2,
107 x_return_status OUT NOCOPY VARCHAR2,
108 x_msg_count OUT NOCOPY NUMBER
109 ) IS
110
111 l_param VARCHAR2(20);
112 l_login_url VARCHAR2(200);
113 c_item_type CONSTANT VARCHAR2(10) := 'IBYPMAIL';
114 c_process CONSTANT VARCHAR2(15) := 'IBY_PUSH_MAIL';
115 c_period CONSTANT VARCHAR2(15) := 'DAILY';
116 summary_tbl IBY_DBCCARD_PVT.Summary_tbl_type;
117 trxnSum_tbl IBY_DBCCARD_PVT.TrxnSum_tbl_type;
118
119 BEGIN
120
121
122 -- Get the details first.
123 IBY_DBCCARD_PVT.Get_Trxn_Summary( NULL,
124 c_period,
125 summary_tbl,
126 trxnSum_tbl
127 );
128
129 -- Create the process first.
130 wf_engine.CreateProcess(
131 itemtype => c_item_type,
132 itemkey => p_item_key,
133 process => c_process
134 );
135
136 wf_engine.SetItemUserKey(
137 itemtype => c_item_type,
138 itemkey => p_item_key,
139 userkey => p_item_key
140 );
141
142 wf_engine.SetItemAttrText(
143 itemtype => c_item_type,
144 itemkey => p_item_key,
145 aname => 'TOTAL_TRXN',
146 avalue => get_str(summary_tbl(1).totalTrxn)
147 );
148
149 wf_engine.SetItemAttrText(
150 itemtype => c_item_type,
151 itemkey => p_item_key,
152 aname => 'TOTAL_TRXN_AMT',
153 avalue => get_str(summary_tbl(1).totalAmt)
154 );
155
156 wf_engine.SetItemAttrText(
157 itemtype => c_item_type,
158 itemkey => p_item_key,
159 aname => 'TOTAL_AUTH',
160 avalue => get_str(summary_tbl(2).totalTrxn)
161 );
162
163 wf_engine.SetItemAttrText(
164 itemtype => c_item_type,
165 itemkey => p_item_key,
166 aname => 'TOTAL_AUTH_AMT',
167 avalue => get_str(summary_tbl(2).totalAmt)
168 );
169
170 wf_engine.SetItemAttrText(
171 itemtype => c_item_type,
172 itemkey => p_item_key,
173 aname => 'TOTAL_CAPT',
174 avalue => get_str(summary_tbl(3).totalTrxn)
175 );
176
177 wf_engine.SetItemAttrText(
178 itemtype => c_item_type,
179 itemkey => p_item_key,
180 aname => 'TOTAL_CAPT_AMT',
181 avalue => get_str(summary_tbl(3).totalAmt)
182 );
183
184 wf_engine.SetItemAttrText(
185 itemtype => c_item_type,
186 itemkey => p_item_key,
187 aname => 'TOTAL_RC',
188 avalue => get_str(summary_tbl(4).totalTrxn)
189 );
190
191 wf_engine.SetItemAttrText(
192 itemtype => c_item_type,
193 itemkey => p_item_key,
194 aname => 'TOTAL_RC_AMT',
195 avalue => get_str(summary_tbl(4).totalAmt)
196 );
197
198 wf_engine.SetItemAttrText(
199 itemtype => c_item_type,
200 itemkey => p_item_key,
201 aname => 'TOTAL_AUTH_SET',
202 avalue => get_str(summary_tbl(5).totalTrxn)
203 );
204
205 wf_engine.SetItemAttrText(
206 itemtype => c_item_type,
207 itemkey => p_item_key,
208 aname => 'TOTAL_AUTH_SET_AMT',
209 avalue => get_str(summary_tbl(5).totalAmt)
210 );
211
212 wf_engine.SetItemAttrText(
213 itemtype => c_item_type,
214 itemkey => p_item_key,
215 aname => 'TOTAL_AUTH_OUT',
216 avalue => get_str(summary_tbl(6).totalTrxn)
217 );
218
219 wf_engine.SetItemAttrText(
220 itemtype => c_item_type,
221 itemkey => p_item_key,
222 aname => 'TOTAL_AUTH_OUT_AMT',
223 avalue => get_str(summary_tbl(6).totalAmt)
224 );
225
226 wf_engine.SetItemAttrText(
227 itemtype => c_item_type,
228 itemkey => p_item_key,
229 aname => 'TOTAL_CC_TRXN',
230 avalue => get_str(summary_tbl(7).totalTrxn)
231 );
232
233 wf_engine.SetItemAttrText(
234 itemtype => c_item_type,
235 itemkey => p_item_key,
236 aname => 'TOTAL_CC_TRXN_AMT',
237 avalue => get_str(summary_tbl(7).totalAmt)
238 );
239
240 wf_engine.SetItemAttrText(
241 itemtype => c_item_type,
242 itemkey => p_item_key,
243 aname => 'TOTAL_PC_TRXN',
244 avalue => get_str(summary_tbl(8).totalTrxn)
245 );
246
247 wf_engine.SetItemAttrText(
248 itemtype => c_item_type,
249 itemkey => p_item_key,
250 aname => 'TOTAL_PC_TRXN_AMT',
251 avalue => get_str(summary_tbl(8).totalAmt)
252 );
253
254 wf_engine.SetItemAttrText(
255 itemtype => c_item_type,
256 itemkey => p_item_key,
257 aname => 'SYSDATE',
258 avalue => TO_CHAR(SYSDATE, 'MON/DD/YYYY HH24:MI:SS')
259 );
260
261 l_login_url := FND_WEB_CONFIG.JSP_AGENT() || 'jtflogin.jsp';
262
263 wf_engine.SetItemAttrText(
264 itemtype => c_item_type,
265 itemkey => p_item_key,
266 aname => 'LOGIN_URL',
267 avalue => l_login_url
268 );
269
270 wf_engine.SetItemAttrText(
271 itemtype => c_item_type,
272 itemkey => p_item_key,
273 aname => 'RECIPIENT_USER',
274 avalue => p_user_name
275 );
276
277 wf_engine.SetItemOwner(
278 itemtype => c_item_type,
279 itemkey => p_item_key,
280 owner => 'SYSADMIN'
281 );
282
283 wf_engine.StartProcess(
284 itemtype => c_item_type,
285 itemkey => p_item_key
286 );
287
288 -- Commit the process trigger.
289 COMMIT;
290
291
292 Exception
293
294 When OTHERS Then
295 x_return_status := FND_API.g_ret_sts_error;
296 x_msg_count := 0;
297
298 END Send_Mail;
299
300 --------------------------------------------------------------------------------------
301 -- 3. Send_Report
302 -- Start of comments
303 -- API name : Send_Report
304 -- Type : Public
305 -- Pre-reqs : None
306 -- Function : Implements Concurrent Program.
307 -- Parameters :
308 -- IN : p_email_users IN VARCHAR2
309 --
310 -- OUT : ERRBUF OUT VARCHAR2
311 -- RETCODE OUT NUMBER
312 -- Version :
313 -- Current version 1.0
314 -- Previous version 1.0
315 -- Initial version 1.0
316 -- End of comments
317 --------------------------------------------------------------------------------------
318
319 -- 3. Send_Report
320
321 Procedure Send_Report(ERRBUF OUT NOCOPY VARCHAR2,
322 RETCODE OUT NOCOPY NUMBER,
323 p_email_users IN VARCHAR2
324 ) IS
325
326 c_notipref_html CONSTANT VARCHAR2(10) := 'MAILHTML';
327 c_itemkey_prefix CONSTANT VARCHAR2(10):= 'IBYPMAIL';
328 c_username_prefix CONSTANT VARCHAR2(10) := 'IBYPMAIL';
329
330 l_key_seq NUMBER;
331 l_return_status VARCHAR2(200);
332 l_msg_count NUMBER;
333 l_adhoc_name VARCHAR2(100);
334
335 BEGIN
336
337 IF(l_userinfo_tbl.COUNT > 0) THEN
338 l_userinfo_tbl.TRIM(l_userinfo_tbl.COUNT);
339 END IF;
340
341 -- Populate the instance table with the user details.
342 populate_userinfo(p_email_users);
343
344 FOR i IN 1..l_userinfo_tbl.COUNT LOOP
345
346 -- Get the next value in the sequence.
347 -- It will be appended to the userkey and username.
348 SELECT iby_pushmail_key_s.NEXTVAL
349 INTO l_key_seq
350 FROM DUAL;
351
352 -- Check whether we need to create adhoc user.
353 -- We need to create an adhoc user only if the user is not registered already.
354 -- Once created, then assign the username to the userinfo.
355 IF ( l_userinfo_tbl(i).usertype = C_USERTYPE_ADHOC) THEN
356
357 l_adhoc_name := c_username_prefix || l_key_seq;
358
359 wf_directory.CreateAdHocUser(
360 name => l_adhoc_name,
361 display_name => l_adhoc_name,
362 notification_preference => c_notipref_html,
363 email_address => l_userinfo_tbl(i).emailaddr,
364 expiration_date => SYSDATE + 1,
365 language => userenv('LANG')
366 );
367
368 l_userinfo_tbl(i).username := l_adhoc_name;
369
370 END IF;
371
372 Send_Mail( c_itemkey_prefix || l_key_seq,
373 l_userinfo_tbl(i).username,
374 l_return_status,
375 l_msg_count
376 );
377
378 -- Set the status to success.
379 RETCODE := 0;
380
381 END LOOP;
382 EXCEPTION
383 WHEN OTHERS THEN
384 ERRBUF := SQLERRM;
385 RETCODE := 2;
386
387 END Send_Report;
388
389 END IBY_EMAIL_REPORT;
390