[Home] [Help]
PACKAGE BODY: APPS.OKI_ALERT_UTIL_PVT
Source
1 PACKAGE BODY oki_alert_util_pvt AS
2 /* $Header: OKIRAUTB.pls 115.12 2002/04/30 16:37:01 pkm ship $*/
3 /*---------------------------------------------------------------------------+
4 | |
5 | PACKAGE: OKI_ALERT_UTIL |
6 | |
7 | |
8 | FILE : OKIRAUTB.pls |
9 | |
10 *-------------------------------------------------------------------------- */
11 --------------------------------------------------------------------------------
12 --
13 -- HISTORY:
14 -- 03-DEC-2001 brrao created
15 -- 29-JAN-2001 mezra Added get_gv_prev_x_qtr_end_date and dflt_gv_qed for
16 -- functions for the oki_expiration_graph graph component
17 -- for the bins.
18 -- 04-FEB-2002 mezra Change get_gv_prev_x_qtr_end_date and dflt_gv_qed
19 -- function to remove hard coded 'DD-MON-YY format
20 -- mask.
21 -- 30-APR-2002 mezra Added dbdrv command and correct header syntax.
22 --
23 --------------------------------------------------------------------------------
24
25
26
27 PROCEDURE Send_email (ERRBUF OUT VARCHAR2
28 ,RETCODE OUT NUMBER
29 ,subject IN VARCHAR2
30 ,body IN VARCHAR2
31 ,email_list IN VARCHAR2 )
32 IS
33 return_status VARCHAR2(1) ;
34 x_msg_count number ;
35 x_msg_data varchar2(2000);
36
37 BEGIN
38
39 OKI_ALERT_UTIL_PVT.Send_Email (
40 p_api_version => 1,
41 p_commit => FND_API.g_false,
42 p_init_msg_list => FND_API.g_false,
43 email_list => email_list,
44 subject => subject,
45 body => body,
46 return_status => return_status,
47 x_msg_count => x_msg_count,
48 x_msg_data => x_msg_data );
49 --IF return_status ='S'then
50 -- dbms_output.put_line('its success');
51 --END IF;
52 END SEND_EMAIL ;
53
54 PROCEDURE Send_Email (
55 p_api_version IN NUMBER,
56 p_commit IN VARCHAR2 := FND_API.g_false,
57 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
58 email_list IN VARCHAR2,
59 subject IN VARCHAR2,
60 body IN VARCHAR2,
61 return_status OUT VARCHAR2,
62 x_msg_count OUT NUMBER,
63 x_msg_data OUT VARCHAR2
64 )
65 IS
66
67 wf_itemkey_seq INTEGER;
68 wf_itemkey VARCHAR2(30);
69 role_name VARCHAR2(30);
70 role_display_name VARCHAR2(30);
71 BEGIN
72 fnd_file.put_line( which => fnd_file.log
73 , buff => 'Alert Subject :'||subject);
74 fnd_file.put_line( which => fnd_file.log
75 , buff => 'Alert Body :'||body);
76 fnd_file.put_line( which => fnd_file.log
77 , buff => 'Email List :'||email_list);
78
79 return_status := FND_API.g_ret_sts_success;
80
81
82 select OKI_ALERT_WF_S1.NEXTVAL into wf_itemkey_seq from dual;
83 wf_itemkey := 'OKI_MAIL_' || wf_itemkey_seq;
84
85 role_name := 'OKI_EMAIL_LIST_' || wf_itemkey_seq ;
86 role_display_name := 'OKI_EMAIL_LIST_' || wf_itemkey_seq ;
87
88 wf_directory.CreateAdHocUser(
89 name => role_name,
90 display_name => role_display_name,
91 notification_preference => 'MAILTEXT',
92 email_address => email_list,
93 expiration_date => sysdate + 1
94 );
95
96 wf_engine.CreateProcess (
97 itemtype => 'OKI_MAIL',
98 itemkey => wf_itemkey,
99 process => 'OKI_SEND_EMAIL'
100 );
101 wf_engine.SetItemUserKey (
102 itemtype => 'OKI_MAIL',
103 itemkey => wf_itemkey,
104 userkey => 'OKI Alert Notification' || wf_itemkey_seq
105 );
106 wf_engine.SetItemAttrText (
107 itemtype => 'OKI_MAIL',
108 itemkey => wf_itemkey,
109 aname => 'ROLE_TO_NOTIFY',
110 avalue => role_name
111 );
112 wf_engine.SetItemAttrText (
113 itemtype => 'OKI_MAIL',
114 itemkey => wf_itemkey,
115 aname => 'NOTIFICATION_SUBJECT',
116 avalue => subject
117 );
118 wf_engine.SetItemAttrText (
119 itemtype => 'OKI_MAIL',
120 itemkey => wf_itemkey,
121 aname => 'NOTIFICATION_BODY',
122 avalue => body
123 );
124 wf_engine.SetItemOwner (
125 itemtype => 'OKI_MAIL',
126 itemkey => wf_itemkey,
127 owner => role_name
128 );
129 wf_engine.StartProcess (
130 itemtype => 'OKI_MAIL',
131 itemkey => wf_itemkey
132 );
133
134 EXCEPTION
135 When others then
136
137 return_status := FND_API.g_ret_sts_error;
138 x_msg_count := 0;
139
140 wf_core.context('OKI_ALERT_WF',
141 'Send_Email',
142 email_list,
143 subject,
144 body
145 );
146 raise;
147 END Send_Email;
148
149
150
151 PROCEDURE myprint(p_str IN VARCHAR2)
152 IS
153 BEGIN
154 -- UTL_FILE.PUT_LINE(g_output_stream,p_str);
155 fnd_file.put_line( which => fnd_file.output
156 , buff => p_str);
157
158 END; -- myprint
159
160
161 procedure create_page( p_title IN varchar2) IS
162 BEGIN
163 myprint('<HTML>');
164 myprint('<HEAD>');
165 myprint('<TITLE>');
166 myprint(p_title);
167 myprint('</TITLE>');
168 myprint('<META http-equiv="Expires" content="0">'); -- for no caching of page..
169 myprint('<link rel="stylesheet" href="'||g_oki_parent_url||'/jtfucss.css">');
170 myprint('</HEAD>');
171 myprint('<BODY text=#000000 bgColor=#ffffff>');
172 END;
173
174 procedure create_mainheader( p_title IN varchar2,p_run_date IN DATE) IS
175 BEGIN
176 myprint('<TABLE width="100%">');
177 myprint('<TBODY><tr>');
178 myprint('<TD bgColor=#336699 colSpan=2>
179 <FONT face="arial, helvetica, sans-serif";>
180 <FONT color=#ffffff><FONT size=+1 ;>');
181 myprint('<A name="report_top">' ||p_title || ' as of '||
182 to_char(p_run_date));
183 myprint('</a></FONT></FONT></FONT></TD></TR></TBODY></TABLE><BR>');
184 END;
185
186
187 procedure reportHeaderCell(p_str IN VARCHAR2, p_ref in VARCHAR2) IS
188 BEGIN
189 myprint('<TABLE cellSpacing=0 cellPadding=0 width="100%" border=0><TBODY>');
190 myprint('<TR><TD width="70%" bgColor=#f7f7e7><b>
191 <font face="arial, helvetica, sans-serif" color="#999966" size="+0">
192 <a name="'||p_ref||'">'||p_str||'</a></font></b></TD></TR>');
193 myprint('<TR><TD colSpan=2 height=25>
194 <FONT face="arial, helvetica, sans-serif" color=#336699 size=-1>&'||'nbsp'||';
195 </FONT></TD></TR></TBODY></TABLE>');
196 END;
197
198 procedure start_table( p_align IN varchar2 default 'L',
199 p_cellpadding IN NUMBER default 0,
200 p_bdr in NUMBER default 0) IS
201 BEGIN
202
203 IF p_align = 'C'
204 THEN
205 myprint('<TABLE BORDER="'||to_char(p_bdr)||'" cellspacing="1"
206 ALIGN="CENTER" width="100%" CELLPADDING="' ||
207 to_char(p_cellpadding)||'">');
208 ELSE
209 myprint('<TABLE BORDER="'||to_char(p_bdr)||'" cellspacing="1"
210 width="100%" CELLPADDING="'|| to_char(p_cellpadding) || '">');
211 END IF;
212
213 END;
214
215
216 procedure start_row IS
217 BEGIN
218 myprint('<TR BGCOLOR="#f7f7e7">');
219 END;
220
221
222 procedure end_row IS
223 BEGIN
224 myprint('</TR>');
225 END;
226
227 procedure create_crumb( p_title IN varchar2,
228 p_link IN VARCHAR2,
229 flag in VARCHAR2) IS
230 BEGIN
231 IF flag IS NULL THEN
232 myprint('<TABLE width="100%">');
233 myprint('<TBODY><tr>');
234 myprint('<td align="left"><table><tr>');
235 myprint(' <td align="left" style="font-size:10pt">');
236 myprint('<a href="'||p_link||'">'||p_title||'</a> </td>');
237 ELSIF(p_title IS NULL AND flag = 'END') THEN
238 myprint('</td></TABLE><br>');
239 ELSE
240 myprint('<td align="left" style="font-size:10pt">');
241 myprint('> <a href="'||p_link||'">'||p_title||'</a> </td>');
242 END IF;
243 END;
244
245
246 procedure populateCell(p_str IN VARCHAR2,
247 p_align IN VARCHAR2,
248 p_link IN VARCHAR2,
249 p_class in VARCHAR2,
250 p_width in VARCHAR2) IS
251 l_class VARCHAR2(100);
252 BEGIN
253 myprint('<td align="'||p_align||'" nowrap ');
254
255 IF(p_str = 'ERROR')
256 THEN
257 l_class := 'errorMessage';
258 ELSE
259 l_class := p_class;
260 END IF;
261
262 IF p_class IS NOT NULL THEN
263 myprint(' class="'||l_class||'"');
264 END IF;
265 IF p_width IS NOT NULL THEN
266 myprint(' width="'||p_width||'%">');
267 ELSE
268 myprint('>');
269 END IF;
270 IF p_str IS NULL
271 THEN
272 myprint('&'||'nbsp'||';');
273 ELSE
274 IF p_link IS NULL THEN
275 myprint(p_str);
276 ELSE
277 myprint('<A HREF="'||g_oki_alert_url||'/'||p_link||'">');
278 myprint(p_str);
279 myprint('</A>');
280 END IF;
281 END IF;
282 myprint('</TD>');
283 END;
284
285 procedure spaceCell(p_space in VARCHAR2,p_str IN VARCHAR2,
286 p_align IN VARCHAR2, p_link IN VARCHAR2,
287 p_class in VARCHAR2, p_width in VARCHAR2) IS
288
289 BEGIN
290 myprint('<td align="'||p_align||'" nowrap ');
291 IF p_class IS NOT NULL THEN
292 myprint(' class="'||p_class||'"');
293 END IF;
294 IF p_width IS NOT NULL THEN
295 myprint(' width="'||p_width||'%">');
296 ELSE
297 myprint('>');
298 END IF;
299 IF p_str IS NULL
300 THEN
301 myprint('&'||'nbsp'||';');
302 ELSE
303 IF p_link IS NULL THEN
304 myprint(p_str);
305 ELSE
306 myprint(p_space);
307 myprint('<A HREF="'||g_oki_alert_url||'/'||p_link||'">');
308 myprint(p_str);
309 myprint('</A>');
310 END IF;
311 END IF;
312 myprint('</TD>');
313 END;
314
315
316 procedure end_table(p_run_date IN DATE) IS
317 BEGIN
318 myprint('<TR><TD colSpan=4><FONT face="arial, helvetica, sans-serif"
319 size=-2>last refreshed on '|| to_char(p_run_date)||'</FONT>
320 </TD></TR>');
324 myprint('</TABLE>');
321 myprint('</FONT></TD></TR></TBODY>');
322 myprint('</TABLE>');
323 myprint('</p>');
325 END;
326
327
328
329 FUNCTION set_output_stream(p_file_name IN VARCHAR2)
330 RETURN BOOLEAN
331 IS
332 BEGIN
333 -- g_output_stream := UTL_FILE.FOPEN(g_utl_file_dest,
334 -- p_file_name,'W');
335 fnd_file.put_line( which => fnd_file.output
336 , buff => 'FILEOKI: '||p_file_name);
337
338 RETURN TRUE;
339 EXCEPTION
340 WHEN UTL_FILE.INVALID_PATH THEN
341 RAISE_APPLICATION_ERROR(-20100,'Invalid Path');
342 RETURN FALSE;
343
344 WHEN UTL_FILE.INVALID_MODE THEN
345 RAISE_APPLICATION_ERROR(-20101,'Invalid Mode');
346 RETURN FALSE;
347
348 WHEN UTL_FILE.INVALID_FILEHANDLE THEN
349 RAISE_APPLICATION_ERROR(-20102,'Invalid Filehandle');
350 RETURN FALSE;
351
352 WHEN UTL_FILE.INVALID_OPERATION THEN
353 RAISE_APPLICATION_ERROR(-20103,'Invalid Operation -- May signal a file locked by the OS');
354 RETURN FALSE;
355
356 WHEN UTL_FILE.WRITE_ERROR THEN
357 RAISE_APPLICATION_ERROR(-20105,'Write Error');
358 RETURN FALSE;
359
360 WHEN UTL_FILE.INTERNAL_ERROR THEN
361 RAISE_APPLICATION_ERROR(-20106,'Internal Error');
362 RETURN FALSE;
363
364 WHEN OTHERS THEN
365 RAISE_APPLICATION_ERROR(-20109,'Unknown UTL_FILE Error');
366 RETURN FALSE;
367 END;
368 -- 1. Success
369 -- 0. Failure
370 PROCEDURE end_output_stream
371 IS
372 BEGIN
373 -- UTL_FILE.FCLOSE(g_output_stream);
374 NULL;
375 END;
376
377 PROCEDURE print_error(p_string IN VARCHAR2)
378 IS
379 BEGIN
380 myprint('<FONT COLOR="#CC0000">');
381 myprint(p_string);
382 myprint('</FONT>');
383 END;
384
385
386 -- This function returns the quarter end date. It takes the quarter and year
387 -- parameter as the starting date and uses the number of quarters parameter
388 -- to determine the number of quarters to go back to determine the "true"
389 -- quarter start date.
390 FUNCTION get_gv_prev_x_qtr_end_date
391 ( p_qtr_end_date IN DATE DEFAULT NULL
392 , p_number_of_qtrs IN NUMBER DEFAULT NULL
393 ) RETURN DATE IS
394
395 l_end_date DATE := NULL ;
396 -- The message id when an error occurs
397 l_message_id VARCHAR2(40) := NULL ;
398
399 BEGIN
400 l_end_date := TO_CHAR(ADD_MONTHS(p_qtr_end_date, ((3 * p_number_of_qtrs) * -1)),
401 fnd_profile.value('ICX_DATE_FORMAT_MASK'));
402
403 RETURN l_end_date ;
404
405 EXCEPTION
406 WHEN OTHERS THEN
407 -- return the error number to the calling program;
408 l_message_id := substr(sqlerrm, 1, (instr(sqlerrm, ':') - 1) ) ;
409 return l_message_id ;
410 END get_gv_prev_x_qtr_end_date ;
411
412 /* Commented by Ravi 02-11-2001
413 -- This function defaults the current quarter start date.
414 FUNCTION dflt_gv_qed
415 ( p_name IN VARCHAR2 DEFAULT NULL
416 ) RETURN VARCHAR2 IS
417
418 -- Holds the sysdate
419 l_curr_date DATE := NULL ;
420 -- Holds the quarter end date of the sysdate
421 l_qtr_end_date DATE := NULL ;
422 -- The message id when an error occurs
423 l_message_id VARCHAR2(40) := null ;
424 -- Holds the organization id
425 l_authoring_org_id NUMBER := NULL ;
426
427 -- Cursor to get the quarter end date for the given date
428 CURSOR get_qtr_end_date_csr
429 ( p_curr_date IN DATE
430 , p_authoring_org_id IN NUMBER
431 ) IS
432 SELECT qtr_end_date
433 FROM oki_graph_values
434 WHERE p_curr_date between qtr_start_date and qtr_end_date
435 AND graph_code = 'OKI_SEQ_GRW'
436 AND authoring_org_id = p_authoring_org_id
437 AND ROWNUM < 2
438 ;
439 rec_get_qtr_end_date_csr get_qtr_end_date_csr%ROWTYPE ;
440
441 BEGIN
442 l_authoring_org_id := jtfb_dcf.get_parameter_value(p_name,'P_AUTHORING_ORG_ID');
443
444 l_curr_date := TRUNC(SYSDATE) ;
445 OPEN get_qtr_end_date_csr ( l_curr_date, l_authoring_org_id );
446 FETCH get_qtr_end_date_csr INTO rec_get_qtr_end_date_csr ;
447 l_qtr_end_date := rec_get_qtr_end_date_csr.qtr_end_date ;
448 CLOSE get_qtr_end_date_csr ;
449
450 RETURN TO_CHAR(l_qtr_end_date, fnd_profile.value('ICX_DATE_FORMAT_MASK')) ;
451
452 EXCEPTION
453 WHEN OTHERS THEN
454 -- return the error number to the calling program;
455 l_message_id := substr(sqlerrm, 1, (instr(sqlerrm, ':') - 1) ) ;
456 return l_message_id ;
457 END dflt_gv_qed ;
458 */
459 BEGIN
460 -- Initialization Block
461 g_alert_dist_list := fnd_profile.value('OKI_ALERT_DIST_LIST');
462 g_alert_publish_dir := fnd_profile.value('OKI_ALERT_PUBLISH_DIR');
463 g_utl_file_dest := fnd_profile.value('OKI_UTL_FILE_DEST');
464 g_oki_parent_url := fnd_profile.value('OKI_ALERT_URL');
465
466 fnd_file.put_line( which => fnd_file.log
467 , buff => 'Alert Dist List :'||g_alert_dist_list);
468 fnd_file.put_line( which => fnd_file.log
469 , buff => 'Alert Publish Dir :'||g_alert_publish_dir);
470 fnd_file.put_line( which => fnd_file.log
471 , buff => 'Utl File Dest :'||g_utl_file_dest);
472 fnd_file.put_line( which => fnd_file.log
473 , buff => 'Alert URL :'||g_oki_parent_url);
475 END; -- Package Body OKI_ALERT_UTIL_PVT
474