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