DBA Data[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;