[Home] [Help]
PACKAGE BODY: APPS.IRC_NOTIFICATION_HELPER_PKG
Source
1 PACKAGE BODY irc_notification_helper_pkg AS
2 /* $Header: irnothlp.pkb 120.7 2008/05/05 09:51:24 kvenukop noship $ */
3 -- -- --------------------------------------------------------------------- *
4 -- Name : get_job_seekers_role
5 -- Purpose : function to obtain the wf role based on a person_id.
6 -- --------------------------------------------------------------------- *
7 FUNCTION get_job_seekers_role
8 (p_person_id per_all_people_f.person_id%type
9 ) RETURN varchar2 IS
10 l_role_name fnd_user.user_name%type;
11 --
12 cursor get_usr is
13 SELECT usr.user_name
14 FROM fnd_user usr
15 WHERE usr.employee_id = p_person_id;
16 --
17 BEGIN
18 open get_usr;
19 fetch get_usr into l_role_name;
20 close get_usr;
21 --
22 RETURN l_role_name;
23 EXCEPTION
24 WHEN OTHERS THEN
25 if (get_usr%isopen) then
26 close get_usr;
27 end if;
28 raise;
29 END;
30 --
31 -- --------------------------------------------------------------------- *
32 -- Name : set_v2_attributes
33 -- Purpose: The wf text attributes values can only be 1950chars in length.
34 -- This procedure converts the possible 15600 plsql varchar2 value
35 -- to multiple 1950 char sql value chunks that can be held
36 -- as attributes in the database.
37 -- (internal)
38 -- --------------------------------------------------------------------- *
39 PROCEDURE set_v2_attributes
40 (p_wf_attribute_value VARCHAR2
41 ,p_wf_attribute_name VARCHAR2
42 ,p_nid NUMBER
43 )
44 IS
45 l_attribute_length NUMBER DEFAULT length(p_wf_attribute_value);
46 l_counter NUMBER DEFAULT 0;
47 l_max_no_of_attribs NUMBER DEFAULT 8;
48 l_next_attrib NUMBER;
49 BEGIN
50 IF l_attribute_length > 1950 THEN
51 -- Loop through p_wf_attribute_value and grab chunks of 1950 bytes of it
52 -- (up to the max number catered for by the workflow) setting the
53 -- appropiate wf attribute.
54 FOR x IN 1 .. least
55 (trunc
56 (l_attribute_length/1950), l_max_no_of_attribs)
57 LOOP
58 l_counter := l_counter + 1;
59 wf_notification.setAttrText ( p_nid
60 , p_wf_attribute_name|| '_' ||x
61 , substr( p_wf_attribute_value
62 , ((x * 1950) - 1949)
63 , 1950
64 )
65 );
66 END LOOP;
67 -- The previous loop took as many 1950 byte chunks as possible. If
68 -- there is still a workflow attribute available (there are
69 -- l_max_no_of_attributes available), use the next one in line to
70 -- hold the remainder to the value.
71 IF ((l_counter < l_max_no_of_attribs)
72 AND mod(l_attribute_length,1950)<> 0) THEN
73 l_next_attrib := l_counter + 1;
74 wf_notification.setAttrText ( p_nid
75 , p_wf_attribute_name|| '_' ||l_next_attrib
76 , substr( p_wf_attribute_value
77 , l_attribute_length
78 - (mod (l_attribute_length,1950)- 1)
79 , l_attribute_length
80 )
81 );
82 END IF;
83 ELSE
84 -- There are less than 1950 chars in the value, so it can be stored
85 -- whole in the first workflow attribute.
86 wf_notification.setAttrText ( p_nid
87 , p_wf_attribute_name|| '_' ||'1'
88 , p_wf_attribute_value
89 );
90 END IF;
91 END;
92 -- --------------------------------------------------------------------- *
93 -- Name : send_text_notification
94 -- Purpose: Send Notification in Text Format
95 -- --------------------------------------------------------------------- *
96 FUNCTION send_text_notification
97 ( p_user_name IN varchar2
98 , p_subject IN varchar2
99 , p_text_body IN varchar2 DEFAULT null
100 , p_from_role IN varchar2 DEFAULT null
101 )
102 RETURN number
103 IS
104 PRAGMA autonomous_transaction;
105 l_message_type wf_messages.type%type;
106 l_message_name wf_messages.name%type := 'IRC_TEXT_MSG';
107 l_nid number;
108 BEGIN
109 --
110 l_message_type :=fnd_profile.value('IRC_WORKFLOW_ITEM_TYPE');
111 l_nid:=wf_notification.send( upper(p_user_name)
112 , l_message_type
113 , l_message_name
114 );
115 --
116 -- p_from_role contains the name of the person who is sending this
117 -- Notification
118 --
119 if(p_from_role is not null)
120 then
121 wf_notification.setAttrText ( l_nid , '#FROM_ROLE' , p_from_role);
122 end if;
123 --
124 wf_notification.setAttrText ( l_nid , 'SUBJECT' , p_subject);
125 set_v2_attributes
126 (p_wf_attribute_value => p_text_body
127 ,p_wf_attribute_name => 'TEXT_BODY'
128 ,p_nid => l_nid);
129 wf_notification.denormalize_notification(l_nid);
130 commit;
131 RETURN l_nid;
132 END send_text_notification;
133 --
134 -- --------------------------------------------------------------------- *
135 -- Name : send_html_text_notification
136 -- Purpose: Send Notification in Text and Html Format
137 -- --------------------------------------------------------------------- *
138 FUNCTION send_html_text_notification
139 ( p_user_name IN varchar2
140 , p_subject IN varchar2
141 , p_html_body IN varchar2 DEFAULT null
142 , p_text_body IN varchar2 DEFAULT null
143 , p_from_role IN varchar2 DEFAULT null
144 )
145 RETURN number
146 IS
147 PRAGMA autonomous_transaction;
148 l_message_type wf_messages.type%type;
149 l_message_name wf_messages.name%type := 'IRC_TEXT_HTML_MSG';
150 l_nid number;
151 BEGIN
152 --
153 l_message_type :=fnd_profile.value('IRC_WORKFLOW_ITEM_TYPE');
154 l_nid:=wf_notification.send( upper(p_user_name)
155 , l_message_type
156 , l_message_name
157 );
158 --
159 -- p_from_role contains the name of the person who is sending this
160 -- Notification
161 --
162 if(p_from_role is not null)
163 then
164 wf_notification.setAttrText ( l_nid , '#FROM_ROLE' , p_from_role);
165 end if;
166 --
167 wf_notification.setAttrText ( l_nid , 'SUBJECT' , p_subject);
168 set_v2_attributes
169 (p_wf_attribute_value => p_html_body
170 ,p_wf_attribute_name => 'HTML_BODY'
171 ,p_nid => l_nid);
172 set_v2_attributes
173 (p_wf_attribute_value => p_text_body
174 ,p_wf_attribute_name => 'TEXT_BODY'
175 ,p_nid => l_nid);
176 wf_notification.denormalize_notification(l_nid);
177 commit;
178 RETURN l_nid;
179 END send_html_text_notification;
180 -- --------------------------------------------------------------------- *
181
182 --
183 -- --------------------------------------------------------------------- *
184 -- Name : Create_AdHoc_User
185 -- Purpose: To create Adhoc User
186 -- --------------------------------------------------------------------- *
187 FUNCTION Create_AdHoc_User
188 (p_email_address IN VARCHAR2)
189 RETURN varchar2
190 IS
191 PRAGMA autonomous_transaction;
192 l_user_name wf_users.name%TYPE ;
193 l_user_display_name wf_users.display_name%TYPE ;
194 BEGIN
195 -- Create an ad-hoc user
196 wf_directory.CreateAdHocUser
197 ( name => l_user_name
198 , display_name => l_user_display_name
199 , email_address => p_email_address
200 , notification_preference => 'MAILHTML'
201 );
202 --
203 commit;
204 RETURN l_user_name;
205 END Create_AdHoc_User;
206 -- --------------------------------------------------------------------- *
207 -- Name : send_notification (overloaded function)
208 -- Purpose: See header
209 -- --------------------------------------------------------------------- *
210 FUNCTION send_notification
211 ( p_email_address IN varchar2
212 , p_subject IN varchar2
213 , p_html_body IN varchar2 DEFAULT null
214 , p_text_body IN varchar2 DEFAULT null
215 , p_from_role IN varchar2 DEFAULT null
216 )
217 RETURN number
218 IS
219 l_user_name wf_users.name%TYPE ;
220 l_nid number;
221 cursor get_user_name is
222 select user_name
223 from fnd_user
224 where upper(email_address)=upper(p_email_address);
225
226 BEGIN
227
228 open get_user_name;
229 fetch get_user_name into l_user_name;
230 if get_user_name%notfound then
231 close get_user_name;
232 -- Create an ad-hoc user
233 l_user_name := Create_AdHoc_User
234 ( p_email_address => p_email_address );
235 else
236 close get_user_name;
237 end if;
238 --
239 l_nid := send_notification
240 ( p_user_name => l_user_name
241 , p_subject => p_subject
242 , p_html_body => p_html_body
243 , p_text_body => p_text_body
244 , p_from_role => p_from_role);
245 --
246 RETURN l_nid;
247 END;
248 -- --------------------------------------------------------------------- *
249 -- Name : send_notification (overloaded function)
250 -- Purpose: See header
251 -- --------------------------------------------------------------------- *
252 FUNCTION send_notification
253 ( p_person_id IN number
254 , p_subject IN varchar2
255 , p_html_body IN varchar2 DEFAULT null
256 , p_text_body IN varchar2 DEFAULT null
257 , p_from_role IN varchar2 DEFAULT null
258 )
259 RETURN number
260 IS
261 l_seeker_role wf_roles.name%type;
262 l_nid number;
263 BEGIN
264 -- Get the job seekers role.
265 l_seeker_role := get_job_seekers_role ( p_person_id );
266 --
267 IF l_seeker_role IS NOT NULL THEN
268 l_nid := send_notification
269 ( p_user_name => l_seeker_role
270 , p_subject => p_subject
271 , p_html_body => p_html_body
272 , p_text_body => p_text_body
273 , p_from_role => p_from_role);
274 ELSE
275 fnd_message.set_name('PER','IRC_412059_NO_ROLE_4_PARTY');
276 fnd_message.raise_error();
277 END IF;
278 RETURN l_nid;
279 END;
280 -- --------------------------------------------------------------------- *
281 -- Name : send_notification (overloaded function)
282 -- Purpose: See header
283 -- --------------------------------------------------------------------- *
284 FUNCTION send_notification
285 ( p_user_name IN varchar2
286 , p_subject IN varchar2
287 , p_html_body IN varchar2 DEFAULT null
288 , p_text_body IN varchar2 DEFAULT null
289 , p_from_role IN varchar2 DEFAULT null
290 )
291 RETURN number
292 IS
293 l_nid number;
294 BEGIN
295 --
296 if p_html_body is not null
297 then
298 l_nid := send_html_text_notification
299 ( p_user_name => p_user_name
300 , p_subject => p_subject
301 , p_html_body => p_html_body
302 , p_text_body => p_text_body
303 , p_from_role => p_from_role
304 );
305 else
306 l_nid := send_text_notification
307 ( p_user_name => p_user_name
308 , p_subject => p_subject
309 , p_text_body => p_text_body
310 , p_from_role => p_from_role
311 );
312 end if;
313 RETURN l_nid;
314 END send_notification;
315
316 --
317 -- --------------------------------------------------------------------- *
318 -- Name : send_attach_resume_notify
319 -- Purpose: The wf attribute corresponding to the resumes should be set .
320 -- this procedure will decide which message to be used depending
321 -- upon the number of resumes to be attached
322 -- (internal)
323 -- --------------------------------------------------------------------- *
324 function send_attach_resume_notify
325 (p_user_name in varchar2
326 ,p_subject in varchar2
327 ,p_text_body in varchar2 default null
328 ,p_from_role in varchar2 default null
329 ,p_doc_ids in g_document_ids)
330 return number is
331 --
332 pragma autonomous_transaction;
333 l_message_type wf_messages.type%Type;
334 l_message_name wf_messages.name%Type;
335 l_nid number;
336 l_doc_text varchar2(2000);
337 --
338 begin
339 --
340 l_message_type :=fnd_profile.value('IRC_WORKFLOW_ITEM_TYPE');
341 --
342 if p_doc_ids.count > 0 then
343 --
344 l_message_name := 'IRC_TEXT_RESUME_MSG_'||to_char(p_doc_ids.count);
345 --
346 else
347 l_message_name := 'IRC_TEXT_MSG';
348 end if;
349 --
350 l_nid:=wf_notification.send(upper(p_user_name)
351 ,l_message_type
352 ,l_message_name);
353 --
354 -- p_from_role contains the name of the person who is sending this
355 -- Notification
356 --
357 if(p_from_role is not null) then
358 --
359 wf_notification.setAttrText(l_nid, '#FROM_ROLE', p_from_role);
360 --
361 end if;
362 --
363 wf_notification.setAttrText(l_nid, 'SUBJECT', p_subject);
364 set_v2_attributes(p_wf_attribute_value => p_text_body
365 ,p_wf_attribute_name => 'TEXT_BODY'
366 ,p_nid => l_nid);
367 --
368 if p_doc_ids.count > 0 then
369 --
370 for i in p_doc_ids.first..p_doc_ids.count loop
371 --
372 l_doc_text := wf_notification.getAttrText(l_nid,'IRC_RESUME_'||to_char(i));
373 l_doc_text := l_doc_text||'/'||p_doc_ids(i);
374 wf_notification.setAttrText(l_nid,
375 'IRC_RESUME_'||to_char(i),
376 l_doc_text);
377 --
378 end loop;
379 --
380 end if;
381 --
382 wf_notification.denormalize_notification(l_nid);
383 commit;
384 return l_nid;
385 --
386 end send_attach_resume_notify;
387 --
388 -- --------------------------------------------------------------------- *
389 -- Name : attach_resumes_notification
390 -- Purpose: See header
391 -- --------------------------------------------------------------------- *
392 function attach_resumes_notification(
393 p_user_name in varchar2
394 ,p_subject in varchar2
395 ,p_text_body in varchar2 default null
396 ,p_from_role in varchar2 default null
397 ,p_person_ids in varchar2 default null
398 )
399 return number is
400 --
401 l_nid number;
402 l_rank number := 1;
403 l_resume varchar2(20) := 'RESUME';
404 l_auto_resume varchar2(20) := 'AUTO_RESUME';
405 l_count number := 1;
406 l_doc_ids g_document_ids;
407 Type doc_id_csr is ref cursor;
408 csr_doc doc_id_csr;
409 l_query_str varchar2(3000):=
410 'select document_id from (
411 select document_id, person_id, rank () over
412 (partition by person_id order by last_update_date desc) rank
413 from irc_documents doc
414 where type in (:p_resume,:p_auto_resume)
415 and person_id in ('||p_person_ids||'))
416 where rank = :p_rank';
417 --
418 begin
419 --
420 open csr_doc for l_query_str using l_resume, l_auto_resume, l_rank;
421 fetch csr_doc into l_doc_ids(l_count);
422 while csr_doc%found loop
423 l_count := l_count + 1;
424 fetch csr_doc into l_doc_ids(l_count);
425 end loop;
426 --
427 close csr_doc;
428 --
429 l_nid := send_attach_resume_notify(
430 p_user_name => p_user_name
431 ,p_subject => p_subject
432 ,p_text_body => p_text_body
433 ,p_from_role => p_from_role
434 ,p_doc_ids => l_doc_ids);
435 --
436 return l_nid;
437 --
438 end attach_resumes_notification;
439 --
440 -- --------------------------------------------------------------------- *
441 -- Name : get_doc
442 -- Purpose: See header
443 -- --------------------------------------------------------------------- *
444 procedure get_doc (document_id in varchar2
445 ,display_type in varchar2
446 ,document in out nocopy varchar2
447 ,document_type in out nocopy varchar2) is
448 begin
449 document:=document_id;
450 end get_doc;
451 --
452
453 --
454 -- --------------------------------------------------------------------- *
455 -- Name : show_resume
456 -- Purpose: See header
457 -- --------------------------------------------------------------------- *
458 procedure show_resume (document_id in varchar2
459 ,display_type in varchar2
460 ,document in out nocopy blob
461 ,document_type in out nocopy varchar2
462 )is
463 l_blob blob;
464 l_mimetype varchar2(30);
465 l_file_name varchar2(240);
466 p_document_id varchar2(100);
467 cursor csr_doc is select binary_doc,
468 mime_type,
469 file_name
470 from irc_documents
471 where document_id = p_document_id;
472 begin
473 p_document_id := document_id;
474 open csr_doc;
475 fetch csr_doc into l_blob, l_mimetype,l_file_name;
476 close csr_doc;
477 document_type := l_mimetype||'; name='||l_file_name;
478 dbms_lob.copy(document,l_blob,dbms_lob.getlength(l_blob));
479 --
480 end show_resume;
481 --
482 -- --------------------------------------------------------------------- *
483 -- Name : raiseNotifyEvent
484 -- Purpose: To raise notifications sending event
485 -- --------------------------------------------------------------------- *
486 procedure raiseNotifyEvent( p_eventName in varchar2
487 , p_assignmentId in number
488 , p_personId in number
489 , params in clob) is
490 l_eventData clob;
491 l_eventKey number;
492 l_proc varchar2(100) := 'raiseNotifyEvent';
493 l_assignmentId number;
494 l_personId number;
495 l_eventName varchar2(30);
496 PRAGMA AUTONOMOUS_TRANSACTION;
497 begin
498 hr_utility.set_location('Entering:'|| g_package||'.'||l_proc, 10);
499 l_assignmentId := p_assignmentId;
500 l_personId := p_personId;
501 l_eventName := p_eventName;
502 l_eventData := params;
503 if l_assignmentId is null
504 then
505 l_assignmentId := IRC_NOTIFICATION_DATA_PKG.getParamValue
506 ( p_param => 'IRC_ASSIGNMENT_ID'
507 , p_eventData => l_eventData) ;
508 else
509 l_eventData := l_eventData
510 || 'IRC_ASSIGNMENT_ID:'
511 || l_assignmentId
512 || ';';
513 end if;
514 if l_personId is null
515 then
516 l_personId := IRC_NOTIFICATION_DATA_PKG.getParamValue
517 ( p_param => 'IRC_CAND_PER_ID'
518 , p_eventData => l_eventData) ;
519 else
520 l_eventData := l_eventData
521 || 'IRC_CAND_PER_ID:'
522 || l_personId
523 || ';';
524 end if;
525 if l_eventName is null
526 then
527 l_eventName := IRC_NOTIFICATION_DATA_PKG.getParamValue
528 ( p_param => 'IRC_EVENT_NAME'
529 , p_eventData => l_eventData) ;
530 else
531 l_eventData := l_eventData
532 || 'IRC_EVENT_NAME:'
533 || l_eventName
534 || ';';
535 end if;
536 if(l_eventName is null or
537 (l_eventName = 'CANDREG' and l_personId is null) or
538 (l_eventName = 'UPDCANDREF' and l_personId is null) or
539 (l_eventName <> 'CANDREG' and l_eventName <> 'UPDCANDREF' and l_assignmentId is null) )
540 then
541 hr_utility.set_location('All mandatory values not available', 20);
542 else
543 hr_utility.set_location('Raise event here', 40);
544 select IRC_NOTIFICATION_EVENT_KEY_S.nextval into l_eventKey from dual;
545 hr_utility.set_location('Event Key : '|| l_eventKey, 50);
546 wf_event.raise( p_event_name => 'oracle.apps.per.irc.common.notifications'
547 , p_event_key => l_eventKey
548 , p_event_data => l_eventData);
549 commit;
550 end if;
551 hr_utility.set_location('Success - Exiting:'|| g_package||'.'||l_proc, 80);
552 exception
553 when others then
554 hr_utility.set_location('Error - Exiting:'|| g_package||'.'||l_proc, 100);
555 end raiseNotifyEvent;
556 --
557 END irc_notification_helper_pkg;