eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.IRC_NOTIFICATION_WORKFLOW_PKG

Source


1 PACKAGE BODY IRC_NOTIFICATION_WORKFLOW_PKG as
2   /* $Header: irntfwfl.pkb 120.35.12020000.12 2013/03/19 05:12:55 pshreera ship $ */
3 --+
4 --+ loadWorkflowAttributes
5 --+
6   procedure loadWorkflowAttributes ( p_eventData in varchar2
7                                    , p_itemType  in varchar2
8                                    , p_itemKey   in varchar2 )is
9     l_temp  varchar2(32767);
10     l_key   varchar2(200);
11     l_value varchar2(2000);
12     l_proc  constant varchar2(50) := 'loadWorkflowAttributes';
13     begin
14       hr_utility.set_location('Entering:'|| g_package||'.'||l_proc, 10);
15       l_temp := p_eventData;
16       while ( length(l_temp) <> 0) loop
17         l_key   := substr(l_temp, 1, instr(l_temp, ':') - 1);
18         l_temp  := substr(l_temp, instr(l_temp, ':') + 1);
19         l_value := substr(l_temp, 1, instr(l_temp, ';') - 1);
20         l_temp  := substr(l_temp, instr(l_temp, ';') + 1);
21         begin
22         wf_engine.setItemAttrText( itemtype => p_itemType
23                                  , itemkey  => p_itemKey
24                                  , aname    => l_key
25                                  , avalue   => l_value);
26         exception
27           when others then
28             hr_utility.set_location('Error Message : ' ||sqlerrm,50);
29         end;
30       end loop;
31       hr_utility.set_location('Success - Exiting:'|| g_package||'.'||l_proc, 100);
32     exception
33       when others then
34         hr_utility.set_location('Error - Exiting:' || g_package||'.'||l_proc, 110);
35         hr_utility.set_location('Error Message : ' || sqlerrm,130);
36     end loadWorkflowAttributes;
37 --+
38 --+ launchNotificationsWorkflow
39 --+
40   function launchNotificationsWorkflow ( p_subscriptionGuid in raw
41                                        , p_event             in out nocopy WF_EVENT_T ) return varchar2 is
42     l_eventData                   varchar2(32767);
43     l_assignmentId                number;
44     l_vacancyId                   number;
45     l_candidateId                 number;
46     l_managerId                   number;
47     l_recruiterId                 number;
48     l_referrerId                  number;
49     l_effectiveDate               date;
50     l_assignmentStatusCode        number;
51     l_assignmentOldStatusCode     number;
52     l_topicId                     number;
53     l_communicationObjectType     varchar2(30);
54     l_messageId                   number;
55     l_interviewId                 number;
56     l_interviewStatusCode         varchar2(50);
57     l_eventName                   varchar2(50);
58     l_eventKey                    number;
59     l_actionPerformerId           number;
60     l_itemType                    varchar2(50);
61     l_itemKey                     varchar2(50);
62     l_proc                        constant varchar2(50) := 'launchNotificationsWorkflow';
63     begin
64       hr_utility.set_location('Entering:'|| g_package||'.'||l_proc, 10);
65       l_eventData := p_event.event_data;
66       l_assignmentId := IRC_NOTIFICATION_DATA_PKG.getParamValue
67                              ( p_param     => 'IRC_ASSIGNMENT_ID'
68                              , p_eventData => l_eventData);
69       hr_utility.set_location('Assignment Id : ' || l_assignmentId, 20);
70       l_eventName := IRC_NOTIFICATION_DATA_PKG.getParamValue
71                              ( p_param     => 'IRC_EVENT_NAME'
72                              , p_eventData => l_eventData);
73       hr_utility.set_location('Event Name : ' || l_eventName, 30);
74       l_effectiveDate := to_date( IRC_NOTIFICATION_DATA_PKG.getParamValue
75                                      ( p_param     => 'IRC_EFFECTIVE_DATE'
76                                      , p_eventData => l_eventData)
77                                 , 'DD-MM-RRRR');
78       if l_effectiveDate is null then
79         l_effectiveDate := sysdate;
80       end if;
81       hr_utility.set_location('Effective Date : ' || l_effectiveDate, 40);
82       l_vacancyId := IRC_NOTIFICATION_DATA_PKG.getParamValue
83                               ( p_param     => 'IRC_VACANCY_ID'
84                               , p_eventData => l_eventData);
85       if l_vacancyId is null then
86         l_vacancyId := IRC_NOTIFICATION_DATA_PKG.getVacancyId
87                               ( p_assignmentId  => l_assignmentId
88                               , p_effectiveDate => l_effectiveDate);
89           l_eventData := l_eventData
90                          || 'IRC_VACANCY_ID:'
91                          || l_vacancyId
92                          ||';';
93       end if;
94       hr_utility.set_location('Vacancy Id : ' || l_vacancyId, 50);
95       if l_vacancyId is not null then
96       l_eventData := l_eventData
97                      || IRC_NOTIFICATION_DATA_PKG.getVacancyDetails
98                                   ( p_vacancyId     => l_vacancyId
99                                   , p_effectiveDate => l_effectiveDate);
100       end if;
101       l_candidateId := IRC_NOTIFICATION_DATA_PKG.getParamValue
102                               ( p_param     => 'IRC_CAND_PER_ID'
103                               , p_eventData => l_eventData);
104       if l_candidateId is null then
105         l_candidateId := IRC_NOTIFICATION_DATA_PKG.getCandidatePersonId
106                               ( p_assignmentId  => l_assignmentId
107                               , p_effectiveDate => l_effectiveDate
108                               , p_event_name    => l_eventName);
109         l_candidateId := irc_utilities_pkg.GET_RECRUITMENT_PERSON_ID
110                               ( p_person_id      => l_candidateId
111                               , p_effective_date => l_effectiveDate);
112       end if;
113       hr_utility.set_location('Candidate Id : ' || l_candidateId, 60);
114       if l_candidateId is not null then
115         l_eventData := l_eventData
116                        || 'IRC_CAND_PER_ID:'
117                        || l_candidateId
118                        ||';';
119         l_eventData := l_eventData
120                        || IRC_NOTIFICATION_DATA_PKG.getPersonDetails
121                             ( p_personId      => l_candidateId
122                             , p_role          => 'CAND'
123                             , p_effectiveDate => l_effectiveDate);
124         l_eventData := l_eventData
125                        || IRC_NOTIFICATION_DATA_PKG.getCandidateAgencyId
126                             (p_candidateId    => l_candidateId
127                             , p_effectiveDate => l_effectiveDate);
128       end if;
129       l_managerId := IRC_NOTIFICATION_DATA_PKG.getManagerPersonId
130                             ( p_vacancyId     => l_vacancyId
131                             , p_effectiveDate => l_effectiveDate);
132       hr_utility.set_location('Manager Id : ' || l_managerId, 70);
133       if l_managerId IS NOT null then
134         l_eventData := l_eventData
135                        || 'IRC_MGR_PER_ID:'
136                        || l_managerId
137                        ||';';
138         l_eventData := l_eventData
139                        || IRC_NOTIFICATION_DATA_PKG.getPersonDetails
140                               ( p_personId      => l_managerId
141                               , p_role          => 'MGR'
142                               , p_effectiveDate => l_effectiveDate);
143       end if;
144       l_recruiterId := IRC_NOTIFICATION_DATA_PKG.getRecruiterPersonId
145                               ( p_assignmentId     => l_assignmentId
146                               , p_effectiveDate => l_effectiveDate);
147       hr_utility.set_location('Recruiter Id : ' || l_recruiterId, 80);
148       if l_recruiterId IS NOT null then
149         l_eventData := l_eventData
150                        || 'IRC_REC_PER_ID:'
151                        || l_recruiterId
152                        ||';';
153         l_eventData := l_eventData
154                        || IRC_NOTIFICATION_DATA_PKG.getPersonDetails
155                               ( p_personId      => l_recruiterId
156                               , p_role          => 'REC'
157                               , p_effectiveDate => l_effectiveDate);
158       end if;
159       l_referrerId := IRC_NOTIFICATION_DATA_PKG.getParamValue
160                               ( p_param     => 'IRC_REFR_PER_ID'
161                               , p_eventData => l_eventData);
162       hr_utility.set_location('Referrer Id : ' || l_referrerId, 90);
163       if l_referrerId IS NOT null then
164         l_eventData := l_eventData
165                        || IRC_NOTIFICATION_DATA_PKG.getPersonDetails
166                               ( p_personId      => l_referrerId
167                               , p_role          => 'REFR'
168                               , p_effectiveDate => l_effectiveDate);
169       end if;
170       l_actionPerformerId := IRC_NOTIFICATION_DATA_PKG.getParamValue
171                                     ( p_param     => 'IRC_ACT_PERF_PER_ID'
172                                     , p_eventData => l_eventData);
173       hr_utility.set_location('Action Performer Id : ' || l_actionPerformerId, 100);
174       if l_actionPerformerId is not null then
175         l_eventData := l_eventData
176                        || IRC_NOTIFICATION_DATA_PKG.getPersonDetails
177                                ( p_personId      => l_actionPerformerId
178                                , p_role          => 'ACT_PERF'
179                                , p_effectiveDate => l_effectiveDate);
180       end if;
181       if l_eventName = 'APLSTACHG' then
182         l_assignmentStatusCode := IRC_NOTIFICATION_DATA_PKG.getparamvalue
183                                     ( p_param     => 'IRC_JOB_APPL_NEW_STATUS_CODE'
184                                     , p_eventdata => l_eventData);
185         l_eventData := l_eventData
186                        || IRC_NOTIFICATION_DATA_PKG.getApplicationStatus
187                                     ( p_assignmentStatusCode => l_assignmentStatusCode);
188 
189     l_eventData := l_eventData
190                        || IRC_NOTIFICATION_DATA_PKG.getApplicationExtStatus
191                                     ( p_assignmentStatusCode => l_assignmentStatusCode);
192 
193 
194     l_assignmentOldStatusCode := IRC_NOTIFICATION_DATA_PKG.getparamvalue
195                                     ( p_param     => 'IRC_JOB_APPL_OLD_STATUS_CODE'
196                                     , p_eventdata => l_eventData);
197 
198     l_eventData := l_eventData
199                        || IRC_NOTIFICATION_DATA_PKG.getApplicationOldExtStatus
200                                     ( p_assignmentOldStatusCode => l_assignmentOldStatusCode);
201 
202 
203       end if;
204       if l_eventName = 'COMTOPCRE' or l_eventName = 'COMTOPUPD' then
205         l_communicationObjectType := IRC_NOTIFICATION_DATA_PKG.getParamValue
206                               ( p_param     => 'IRC_COMM_OBJ_TYPE'
207                               , p_eventData => l_eventData);
208         if(l_communicationObjectType is null) then
209            l_communicationObjectType := 'TOPIC';
210         end if;
211         if(l_communicationObjectType = 'TOPIC') then
212           l_topicId := IRC_NOTIFICATION_DATA_PKG.getParamValue
213                               ( p_param     => 'IRC_COMM_OBJ_ID'
214                               , p_eventData => l_eventData);
215           l_messageId := IRC_NOTIFICATION_DATA_PKG.getParamValue
216                               ( p_param     => 'IRC_COMM_MSG_ID'
217                               , p_eventData => l_eventData);
218           l_eventData := l_eventData
219                          || IRC_NOTIFICATION_DATA_PKG.getCommunicationTopicDetails
220                                    ( p_topicId   => l_topicId
221                                    , p_messageId => l_messageId);
222         end if;
223       end if;
224       if l_eventName = 'INTVCRE' or l_eventName = 'INTVUPD' then
225           l_interviewId := IRC_NOTIFICATION_DATA_PKG.getParamValue
226                               ( p_param     => 'IRC_INTVW_ID'
227                               , p_eventData => l_eventData);
228           l_eventData := l_eventData
229                          || IRC_NOTIFICATION_DATA_PKG.getInterviewDetails
230                                    ( p_interviewId   => l_interviewId
231                                    , p_effectiveDate => l_effectiveDate);
232           l_interviewStatusCode := IRC_NOTIFICATION_DATA_PKG.getParamValue
233                                        ( p_param     => 'IRC_INTVW_NEW_STATUS_CODE'
234                                        , p_eventData => l_eventData);
235           l_eventData := l_eventData
236                          || IRC_NOTIFICATION_DATA_PKG.getinterviewstatusmeaning
237                               ( p_interviewstatuscode => l_interviewStatusCode
238                               , p_attributename => 'IRC_INTVW_NEW_STATUS');
239           l_interviewStatusCode := IRC_NOTIFICATION_DATA_PKG.getParamValue
240                                        ( p_param     => 'IRC_INTVW_OLD_STATUS_CODE'
241                                        , p_eventData => l_eventData);
242           l_eventData := l_eventData
243                          || IRC_NOTIFICATION_DATA_PKG.getinterviewstatusmeaning
244                               ( p_interviewstatuscode => l_interviewStatusCode
245                               , p_attributename => 'IRC_INTVW_OLD_STATUS');
246           l_eventData := l_eventData
247                          || IRC_NOTIFICATION_DATA_PKG.getinterviersnameshtml
248                               ( p_interviewid   => l_interviewId
249                               , p_effectivedate => l_effectiveDate);
250       end if;
251       hr_utility.set_location('Populated all data:Create Workflow', 110);
252       hr_utility.set_location('Event Data : '||l_eventData, 120);
253       l_itemType := fnd_profile.value('IRC_NTF_WF_ITEM_TYPE');
254       hr_utility.set_location('Item Type : '||l_itemType, 130);
255       if l_itemType is not NULL then
256         l_itemKey := p_event.event_key;
257         wf_engine.CreateProcess(l_itemType
258                                ,l_itemKey
259                                ,'IRC_NOTIFICATION_PRC');
260         loadWorkflowAttributes ( p_eventData => l_eventData
261                                , p_itemType  => l_itemType
262                                , p_itemKey   => l_itemKey );
263         wf_engine.startprocess( l_itemType
264                               , l_itemKey);
265       else
266         hr_utility.set_location('Workflow Item Type not set', 160);
267       end if;
268       hr_utility.set_location('Success - Exiting:'|| g_package||'.'||l_proc, 180);
269       return 'SUCCESS';
270     exception
271      when others then
272        hr_utility.set_location('Error - Exiting:'|| g_package||'.'||l_proc, 200);
273        hr_utility.set_location('Error Message: ' || sqlerrm, 210);
274        WF_CORE.CONTEXT('IRC_NOTIFICATIONS_WORKFLOW_PKG','launchNotificationsWorkflow',p_event.getEventName( ), p_subscriptionGuid);
275        WF_EVENT.setErrorInfo(p_event, 'ERROR');
276        return 'ERROR';
277   end launchNotificationsWorkflow;
278 --+
279 --+constructURL
280 --+
281 procedure constructURL ( p_itemType in varchar2
282                        , p_itemKey  in varchar2
283                        , p_urlAttribute in varchar2
284                        , p_recipientPersonId in varchar2
285                        , p_personType in varchar2) is
286     l_eventName            varchar2(100);
287     l_url                  varchar2(4000) := null;
288     l_apps_fwk_agent       varchar2(2000);
289     l_assignmentId         varchar2(4000);
290     l_isInternalPerson     varchar2(10);
291     l_candidatePersonId    varchar2(4000);
292     l_managerId            varchar2(100);
293     l_recruiterId          varchar2(100);
294     l_proc                 constant varchar2(50) := 'constructURL';
295     l_params               varchar2(32767);
296     l_func                 varchar2(200);
297     l_funcId               number;
298 
299  cursor c_func(p_function_name varchar2) is
300           select function_id from fnd_form_functions
301                   where function_name = p_function_name;
302     begin
303       hr_utility.set_location('Entering:'|| g_package||'.'||l_proc, 10);
304       l_eventName := wf_engine.getItemAttrText ( itemtype => p_itemType
305                                               , itemkey  => p_itemKey
306                                               , aname => 'IRC_EVENT_NAME');
307       hr_utility.set_location('Event name : '||l_eventName,20);
308       l_isInternalPerson := irc_utilities_pkg.is_internal_person
309                              (p_person_id=> p_recipientPersonId,
310                               p_eff_date => trunc(sysdate)
311                              );
312       if (p_personType <> 'CAND' OR l_isInternalPerson ='TRUE') then
313         l_apps_fwk_agent := rtrim(fnd_profile.value_specific('APPS_FRAMEWORK_AGENT')
314                             || fnd_profile.value('ICX_PREFIX'),'/');
315       else
316         l_apps_fwk_agent := rtrim(nvl(fnd_profile.value('IRC_FRAMEWORK_AGENT'),
317                                       fnd_profile.value('APPS_FRAMEWORK_AGENT'))
318                             ||fnd_profile.value('ICX_PREFIX'),'/');
319       end if;
320 
321       l_apps_fwk_agent := l_apps_fwk_agent ||'/OA_HTML';
322 
323       l_assignmentId := wf_engine.getItemAttrText ( itemtype => p_itemType
324                                                   , itemkey  => p_itemKey
325                                                   , aname => 'IRC_ASSIGNMENT_ID');
326       l_candidatePersonId := wf_engine.getItemAttrText ( itemtype => p_itemType
327                                                        , itemkey  => p_itemKey
328                                                        , aname => 'IRC_CAND_PER_ID');
329 
330       hr_utility.set_location('Assignment Id : '||l_assignmentId,30);
331       hr_utility.set_location('Candidate Person Id : '||l_candidatePersonId,40);
332 
333       if p_personType = 'CAND' then
334         if l_isInternalPerson = 'TRUE' then
335           l_func := 'IRC_EMP_VIS_APPLY_LOGIN_PAGE';
336         else
337           l_func := 'IRC_VIS_APPLY_LOGIN_PAGE';
338         end if;
339 
340         if l_eventName in ('APLSTACHG','APLFORJOB','UPDREF','COMTOPCRE',
341                         'COMTOPUPD','REVTERACK','INTVCRE','INTVUPD') then
342           l_params :=  'p_aplid=' || l_assignmentId;
343           if(l_eventName in ('COMTOPCRE','COMTOPUPD')) then
344             l_params := l_params|| '&TabAction=CommunicationDetails';
345           end if;
346         elsif l_eventName = 'UPDCANDREF' then
347           l_params :=  'AccountDetails=Y';
348         end if;
349       else
350         l_func := 'IRC_RELAUNCH_PG';
351 
352         if l_eventName in ('APLSTACHG','APLFORJOB','REQREVTER','WTDRAPL','UPDREF') then
353           l_params := 'IrcAction=ApplicationDetails'
354                 || '&p_aplid=' || l_assignmentId
355                 || '&p_sprty=' || l_candidatePersonId;
356         elsif l_eventName in ('CANDREG','UPDCANDREF') then
357           l_params := 'IrcAction=CandidateDetails'
358                 || '&p_sprty=' || l_candidatePersonId;
359         elsif l_eventName like 'COMTOP%' then
360           l_params := 'IrcAction=CommunicationDetails'
361                 || '&p_aplid=' || l_assignmentId
362                 || '&p_sprty=' || l_candidatePersonId;
363         elsif l_eventName like 'INTV%' then
364           l_managerId := wf_engine.getItemAttrText ( itemtype => p_itemType
365                                                    , itemkey  => p_itemKey
366                                                    , aname => 'IRC_MGR_PER_ID');
367           l_recruiterId := wf_engine.getItemAttrText ( itemtype => p_itemType
368                                                    , itemkey  => p_itemKey
369                                                    , aname => 'IRC_REC_PER_ID');
370           l_params := 'IrcAction=InterviewDetails'
371                 || '&p_aplid=' || l_assignmentId
372                 || '&p_sprty=' || l_candidatePersonId;
373           if p_recipientPersonId <> nvl(l_managerId,-1) and
374              p_recipientPersonId <> nvl(l_recruiterId,-1) then
375             l_params := l_params
376                      || '&Interviewer=Y';
377           end if;
378         end if;
379       end if;
380 
381  hr_utility.set_location('l_params:'||l_params,50);
382  open c_func(l_func);
383  fetch c_func into l_funcId;
384  close c_func;
385 
386       l_url:=   fnd_run_function.get_run_function_url ( p_function_id =>l_funcId,
387                                 p_resp_appl_id =>-1,
388                                 p_resp_id =>-1,
389                                 p_security_group_id =>0,
390                                 p_override_agent=>l_apps_fwk_agent,
391                                 p_parameters =>l_params,
392                                 p_encryptParameters =>true ) ;
393 
394       hr_utility.set_location('Generated URL : '||l_url,60);
395       wf_engine.setItemAttrText( itemtype => p_itemType
396                                , itemkey  => p_itemKey
397                                , aname    => p_urlAttribute
398                                , avalue   => l_url);
399       hr_utility.set_location('Success - Exiting:'|| g_package||'.'||l_proc, 70);
400     exception
401       when others then
402         hr_utility.set_location('Error - Exiting:'|| g_package||'.'||l_proc, 200);
403         hr_utility.set_location('Error Message : ' || sqlerrm, 210);
404 end constructURL;
405 --+
406 --+ parseAndReplaceFNDMessage
407 --+
408 function parseAndReplaceFNDMessage ( p_itemType in varchar2
409                                    , p_itemKey  in varchar2
410                                    , p_message  in varchar2
411                                    , p_personId in varchar2 default null
412                                    , p_personType in varchar2 default null) return varchar2 as
413     l_leftIndex       number := 1;
414     l_rightIndex      number ;
415     l_returnMessage   varchar2(32767) := p_message;
416     l_token           varchar2(1000);
417     l_value           varchar2(10000):= null;
418     l_proc            constant varchar2(50) := 'parseAndReplaceFNDMessage';
419     begin
420       hr_utility.set_location('Entering:'|| g_package||'.'||l_proc, 10);
421       while (l_leftIndex <> 0) loop
422         l_value := null;
423         l_leftIndex := instr(l_returnMessage, '&' || 'IRC');
424         if (l_leftIndex = 0) then
425           exit;
426         end if;
427         l_rightIndex := length(l_returnMessage);
428         l_token := substr(l_returnMessage,l_leftIndex + 1, l_rightIndex-l_leftIndex);
429   			/*	Remove space from token  */
430         if(instr(l_token,' ') <> 0) then
431           l_token := substr(l_token,0, instr(l_token,' ')-1);
432         end if;
433         /*	Remove comma(,) from token  */
434         if(instr(l_token,',') <> 0) then
435           l_token := substr(l_token,0, instr(l_token,',')-1);
436         end if;
437         /*	Remove dot(.) from token  */
438         if(instr(l_token,'.') <> 0) then
439           l_token := substr(l_token,0, instr(l_token,'.')-1);
440         end if;
441         /*	Remove semicolon(;) from token  */
442         if(instr(l_token,';') <> 0) then
443           l_token := substr(l_token,0, instr(l_token,';')-1);
444         end if;
445         /*	Remove closed paranthsis()) from token  */
446         if(instr(l_token,')') <> 0) then
447           l_token := substr(l_token,0, instr(l_token,')')-1);
448         end if;
449         /*	Remove open paranthsis(() from token  */
450         if(instr(l_token,'(') <> 0) then
451           l_token := substr(l_token,0, instr(l_token,'(')-1);
452         end if;
453         /*	Remove open single quotes('') from token  */
454   		  if(instr(l_token,'''') <> 0) then
455           l_token := substr(l_token,0, instr(l_token,'''')-1);
456         end if;
457         /*	Remove colon(:) from token  */
458               if(instr(l_token,':') <> 0) then
459         l_token := substr(l_token,0, instr(l_token,':')-1);
460         end if;
461         if l_token like 'IRC%HYPERLINK%' then
462           hr_utility.set_location('Token for hyperlink',20);
463           constructURL ( p_itemType => p_itemType
464                            , p_itemKey  => p_itemKey
465                            , p_urlAttribute => l_token
466                            , p_recipientPersonId =>p_personId
467                            , p_personType => p_personType);
468         end if;
469         begin
470           l_value := wf_engine.GetItemAttrText( itemtype => p_itemType
471                                               , itemkey  => p_itemKey
472                                               , aname => l_token);
473         exception
474           when others then
475             hr_utility.set_location('Error in getting Workflow attribute : ' || sqlerrm, 50);
476         end;
477         l_rightIndex := l_leftIndex + lengthb(l_token)+1;
478           l_returnMessage := substr(l_returnMessage, 1, l_leftIndex -1 )
479                              || l_value
480                              || substr(l_returnMessage, l_rightIndex);
481         end loop;
482       hr_utility.set_location('Success - Exiting:'|| g_package||'.'||l_proc, 100);
483       return l_returnMessage;
484     exception
485       when others then
486         hr_utility.set_location('Error - Exiting:'|| g_package||'.'||l_proc, 120);
487         hr_utility.set_location('Error Message : ' || sqlerrm, 130);
488   end parseAndReplaceFNDMessage;
489 --+
490 --+ attatchDoc
491 --+
492 procedure attatchDoc(   document_id   IN VARCHAR2
493                        ,display_type  IN VARCHAR2
494                        ,document      IN OUT nocopy blob
495                        ,document_type IN OUT nocopy VARCHAR2)
496 
497 
498 is
499   l_blob        blob;
500   l_mimetype    varchar2(150);
501   l_file_name   varchar2(240);
502   p_document_id varchar2(100);
503   cursor csr_doc is
504      select binary_doc,
505             mime_type,
506             file_name
507         from irc_documents
508        where document_id = p_document_id;
509 begin
510   p_document_id := document_id;
511   open csr_doc;
512   fetch csr_doc into l_blob, l_mimetype,l_file_name;
513   close csr_doc;
514   document_type := l_mimetype||'; name='||l_file_name;
515   dbms_lob.copy(document,l_blob,dbms_lob.getlength(l_blob));
516 end attatchDoc;
517 --+
518 --attach document
519 --+
520 procedure attachDocument(p_notificationIdIn in number,p_personIdIn in varchar2
521                          ,p_eventName in varchar2,p_itemKey in varchar2
522                          ,p_roleType in varchar2) is
523    l_doc_ids         ame_util.idList;
524    l_proc            varchar2(50);
525    l_person_id       number;
526    l_count           number;
527    l_doc_type        varchar2(40);
528    l_intw_status     varchar2(100);
529    l_intw_id         number;
530    cursor chkResumeExts(c_person_id in number ) is
531       select count(*)
532        from irc_documents
533       where type = 'RESUME'
534         and party_id =
535            (select party_id
536               from per_all_people_f
537             where person_id = c_person_id
538               and trunc(sysdate) between effective_start_date and effective_end_Date)
539         and end_date is null;
540    cursor getDocIdList(c_personIdIn in number,c_doc_type in varchar2) is
541       select document_id
542         from irc_documents
543        where type  = c_doc_type
544          and party_id =
545            (select party_id
546               from per_all_people_f
547             where person_id = c_personIdIn
548               and trunc(sysdate) between effective_start_date and effective_end_Date)
549          and end_Date is null;
550   cursor getInterViewStatus(c_interviewId in number) is
551     select status
552      from irc_interview_details
553     where event_id = c_interviewId
554       and sysdate between start_date and nvl(end_Date,sysdate);
555 
556 begin
557   l_proc := 'attatchDocument';
558   open chkResumeExts(p_personIdIn);
559   fetch chkResumeExts into l_count;
560   close chkResumeExts;
561   if l_count > 0 then
562     l_doc_type := 'RESUME';
563   else
564     l_doc_type := 'AUTO_RESUME';
565   end if;
566   hr_utility.set_location('Entering attatchDocument:'|| g_package||'.'||l_proc, 10);
567   hr_utility.set_location('attatchDocument:p_personIdIn:'||p_personIdIn, 30);
568   hr_utility.set_location('attatchDocument:p_eventName:'||p_eventName, 40);
569   if p_personIdIn is not null then
570     l_person_id := to_number(p_personIdIn);
571   else
572     return;
573   end if;
574   if p_eventName is not null and p_eventName in ('INTVCRE','INTVUPD','APLFORJOB') then
575     if p_eventName in ('INTVCRE','INTVUPD') then
576       begin
577         l_intw_id := to_number(IRC_NOTIFICATION_WORKFLOW_PKG.getWFAttrValue(p_itemKey,'IRC_INTVW_ID'));
578         if l_intw_id is not null then
579           open getInterViewStatus(l_intw_id);
580           fetch getInterViewStatus into l_intw_status;
581           close getInterViewStatus;
582         end if;
583         if l_intw_status is not null and l_intw_status not in ('PLANNED','CONFIRMED','RESCHEDULED') then
584           return;
585         end if;
586       exception
587        when others then
588          hr_utility.set_location('error:'||sqlerrm, 30);
589       end;
590       if l_intw_status is not null and l_intw_status  in ('CONFIRMED','RESCHEDULED') then
591         wf_notification.setAttrText(p_notificationIdIn,
592                                      'IRC_CAL_ATTACHMENT',
593                                      'plsqlblob:irc_notification_workflow_pkg.attatchICDoc/'||p_itemKey||':'||p_notificationIdIn);
594       end if;
595     end if;
596     if p_roleType <> 'CAND' then
597       open getDocIdList(l_person_id,l_doc_type);
598       fetch getDocIdList bulk collect into l_doc_ids;
599       close getDocIdList;
600       for i in 1..l_doc_ids.count loop
601          wf_notification.setAttrText(p_notificationIdIn,
602                                      'IRC_ATTACHMENT_'||to_char(i),
603                                      'plsqlblob:irc_notification_workflow_pkg.attatchDoc/'||l_doc_ids(i));
604       end loop;
605     end if;
606   end if;
607   hr_utility.set_location('Exiting attatchDocument:'|| g_package||'.'||l_proc, 60);
608   exception
609     when others then
610       hr_utility.set_location('Error occurred in :'|| g_package||'.'||l_proc, 70);
611       hr_utility.set_location('Error Message:'|| SQLERRM, 80);
612 end attachDocument;
613 --+
614 --+ getNextRecipient
615 --+
616 procedure getNextRecipient ( p_itemType   in varchar2
617                             , p_itemKey    in varchar2
618                             , p_activityId in number
619                             , funmode      in varchar2
620                             , result       out nocopy varchar2 ) is
621      l_ameTransactionType           varchar2(50);
622      l_nextApprovers                ame_util.approversTable2;
623      l_approvalProcessCompleteYNOut varchar2(1);
624      l_notificationId               number;
625      l_messageSubjectName           varchar2(30);
626      l_messageSubject               varchar2(5000);
627      l_htmlMessageName              varchar2(30);
628      l_htmlMessageBody              varchar2(30000);
629      l_eventName                    varchar2(50);
630      l_itemIndexesOut               ame_util.idList;
631      l_itemClassesOut               ame_util.stringList;
632      l_itemIdsOut                   ame_util.stringList;
633      l_itemSourcesOut               ame_util.longStringList;
634      l_productionIndexesOut         ame_util.idList;
635      l_variableNamesOut             ame_util.stringList;
636      l_variableValuesOut            ame_util.stringList;
637      l_actionPerformerId            number;
638      e_ameException                 exception;
639      e_messageNameIsNull            exception;
640      l_approverRole                 varchar2(50);
641      l_message_type                 varchar2(100);
642      l_proc                         constant varchar2(50) := 'getNextRecipient';
643      l_candidatePersonId            varchar2(50);
644      l_person_type                  varchar2(50);
645      l_ntf_message_type             varchar2(50);
646      l_oldExtStatus                 varchar2(50);
647      l_newExtStatus                 varchar2(50);
648      l_dft_lang                     varchar2(20);
649      l_user_lang                    varchar2(20);
650      l_itemType                     varchar2(50);
651 
652      l_textMessageName              varchar2(30);
653      l_textMessageBody              varchar2(30000);
654      l_returned_messageName         varchar2(30);
655 
656       cursor get_nls_lang (p_dft_lang varchar2) is
657        select NLS_LANGUAGE
658          from fnd_languages_vl
659         where language_code = p_dft_lang;
660       cursor get_user_lang(p_user_name varchar2) is
661         select FND_PROFILE.value_specific('ICX_LANGUAGE',usr.user_id)
662           from fnd_user usr
663           where user_name = p_user_name;
664       cursor get_message_name( c_messageName in varchar2 , c_language in varchar2) is
665         select message_name
666         from fnd_new_messages
667         where message_name = c_messageName
668         and language_code = ( select language_code
669                               from fnd_languages_vl
670                               where nls_language = c_language );
671      BEGIN
672       hr_utility.set_location('Entering:'|| g_package||'.'||l_proc, 10);
673       l_itemType := fnd_profile.value('IRC_NTF_WF_ITEM_TYPE');
674       l_oldExtStatus := wf_engine.getItemAttrText ( itemtype => l_itemType, itemkey  => p_itemKey,aname => 'IRC_JOB_APPL_OLD_STATUS');
675       l_newExtStatus := wf_engine.getItemAttrText ( itemtype => l_itemType, itemkey  => p_itemKey,aname => 'IRC_APPL_NEW_EXTERNAL_STATUS');
676 
677       l_ameTransactionType := fnd_profile.value('IRC_NTF_AME_TX_TYPE');
678       l_person_type := null;
679       if l_ameTransactionType is not NULL then
680         hr_utility.set_location('AME Transaction Type : ' || l_ameTransactionType, 20);
681         begin
682           ame_api2.getNextApprovers2(applicationIdIn               => 800
683                                   , transactionTypeIn            => l_ameTransactionType
684                                   , transactionIdIn              => p_itemKey
685                                   , approvalProcessCompleteYNOut => l_approvalprocesscompleteynout
686                                   , nextApproversOut             => l_nextApprovers
687                                   , itemIndexesOut               => l_itemIndexesOut
688                                   , itemClassesOut               => l_itemClassesOut
689                                   , itemIdsOut                   => l_itemIdsOut
690                                   , itemSourcesOut               => l_itemSourcesOut
691                                   , productionIndexesOut         => l_productionIndexesOut
692                                   , variableNamesOut             => l_variableNamesOut
693                                   , variableValuesOut            => l_variableValuesOut);
694         exception
695           when others then
696             raise e_ameException;
697         end;
698         hr_utility.set_location('AME Transaction Complete : ' || l_approvalprocesscompleteynout,30);
699         if l_approvalProcessCompleteYNOut = 'N' then
700             hr_utility.set_location('Recipient : ' || l_nextApprovers(1).name,60);
701 
702             wf_engine.setItemAttrText ( itemtype => p_itemType
703                                       , itemkey  => p_itemKey
704                                       , aname    => 'IRC_APPROVER'
705                                       , avalue   => l_nextApprovers(1).name );
706             l_eventName := wf_engine.GetItemAttrText( itemtype => p_itemType
707                                                     , itemkey  => p_itemKey
708                                                     , aname => 'IRC_EVENT_NAME');
709             l_messageSubjectName := null;
710              l_htmlMessageName := null;
711 
712             for i in 1..l_variableNamesOut.count loop
713               if l_variableNamesOut(i) = 'IRECRUITMENT NOTIFICATION MSG SUBJECT' then
714                 l_messageSubjectName := l_variableValuesOut(i);
715               elsif l_variableNamesOut(i) = 'IRECRUITMENT NOTIFICATION MSG BODY' then
716                 l_htmlMessageName := l_variableValuesOut(i);
717               elsif  l_variableNamesOut(i) = 'IRC_ROLE' then
718                 if l_person_type is null or l_person_type <> 'CAND' then
719                   l_person_type := l_variableValuesOut(i);
720                 end if;
721               end if;
722             end loop;
723 
724             hr_utility.set_location('FND Message Name for Subject : ' || l_messageSubjectName,80);
725             hr_utility.set_location('FND Message Name for Body : ' || l_htmlMessageName,90);
726             if l_messageSubjectName is null or l_htmlMessageName is null then
727               raise e_messageNameIsNull;
728             else
729               open get_nls_lang(userenv('LANG'));
730               fetch get_nls_lang into l_dft_lang;
731               close get_nls_lang;
732 
733               open get_user_lang(l_nextApprovers(1).name);
734               fetch get_user_lang into l_user_lang;
735               close get_user_lang;
736               if l_dft_lang <> l_user_lang then
737                 DBMS_SESSION.SET_NLS('NLS_LANGUAGE',''''||l_user_lang||'''');
738               end if;
739 
740              l_textMessageName := CONCAT(SUBSTR(l_htmlMessageName, 1, LENGTH(l_htmlMessageName)-4), 'BTXT');
741              open get_message_name(l_textMessageName, l_user_lang);
742              fetch get_message_name into l_returned_messageName;
743              close get_message_name;
744              if l_returned_messageName is null then
745                l_textMessageName := l_htmlMessageName;
746              end if;
747              hr_utility.set_location('l_textMessageName: '||l_textMessageName,100);
748 
749             fnd_message.set_name('PER',l_messageSubjectName);
750             l_messageSubject :=   parseAndReplaceFNDMessage( p_itemType => p_itemType
751                                                            , p_itemKey  => p_itemKey
752                                                            , p_message  => fnd_message.get);
753               hr_utility.set_location('Message Subject sent to Recipient: '||l_messageSubject,120);
754             wf_engine.setItemAttrText ( itemtype => p_itemType
755                                       , itemkey  => p_itemKey
756                                       , aname    => 'IRC_MESSAGE_SUBJECT'
757                                       , avalue   => l_messageSubject);
758 
759              fnd_message.set_name('PER', l_textMessageName);
760              l_textMessageBody := parseAndReplaceFNDMessage( p_itemType => p_itemType
761                                                            , p_itemKey  => p_itemKey
762                                                            , p_message  => fnd_message.get
763                                                            , p_personId => l_nextApprovers(1).orig_system_id
764                                                            , p_personType => l_person_type);
765 
766              if l_eventName = 'INTVCRE' or l_eventName = 'INTVUPD' then
767                 l_textMessageBody := replace(l_textMessageBody,'<UL>',null);
768                 l_textMessageBody := replace(l_textMessageBody,'<LI>','
769 ');
770                 l_textMessageBody := replace(l_textMessageBody,'</LI>',null);
771                 l_textMessageBody := replace(l_textMessageBody,'</UL>','
772 ');
773              end if;
774 
775              hr_utility.set_location('Message Text Body sent to Recipient: '|| l_textMessageBody, 130);
776 
777              fnd_message.set_name('PER', l_htmlMessageName);
778              l_htmlMessageBody :=  parseAndReplaceFNDMessage( p_itemType => p_itemType
779                                                             , p_itemKey  => p_itemKey
780                                                             , p_message  => fnd_message.get
781                                                             , p_personId => l_nextApprovers(1).orig_system_id
782                                                             , p_personType => l_person_type);
783                hr_utility.set_location('Message HTML Body sent to recipient: '|| l_htmlMessageBody, 150);
784                wf_engine.setitemattrtext( itemtype => p_itemType
785                                            , itemkey  => p_itemKey
786                                            , aname    => 'IRC_MESSAGE_BODY'
787                                            , avalue   => l_htmlMessageBody);
788               l_message_type :=fnd_profile.value('IRC_NTF_WF_ITEM_TYPE');
789               if l_eventName in ('INTVCRE','INTVUPD') and l_person_type <> 'DLINT' then
790                 l_ntf_message_type := 'IRC_MESSAGE_WITH_ATTACHMENT';
791               elsif l_person_type <> 'CAND'  and l_eventName = 'APLFORJOB' then
792                 l_ntf_message_type := 'IRC_MESSAGE_WITH_ATTACHMENT';
793               else
794                 l_ntf_message_type := 'IRC_MESSAGE';
795               end if;
796 
797            if ((l_person_type = 'CAND') and (l_eventName = 'APLSTACHG') and (l_oldExtStatus = l_newExtStatus)) then
798                    null; --no operation
799                else
800                    l_notificationId:=wf_notification.send(  l_nextApprovers(1).name
801                                    ,  l_message_type
802                                    ,  l_ntf_message_type
803                                    );
804                end if;
805 
806 
807               hr_utility.set_location('Notification ID : ' || l_notificationId,180);
808               wf_notification.setAttrText (l_notificationId, '#FROM_ROLE', 'SYSADMIN');
809               wf_notification.setAttrText(l_notificationId, 'SUBJECT', l_messageSubject);
810               wf_notification.setAttrText(l_notificationId, 'TEXT_BODY', l_textMessageBody);
811               wf_notification.setAttrText(l_notificationId, 'HTML_BODY', l_htmlMessageBody);
812               if  l_ntf_message_type = 'IRC_MESSAGE_WITH_ATTACHMENT' then
813                 l_candidatePersonId := wf_engine.GetItemAttrText( itemtype => p_itemType
814                                                     , itemkey  => p_itemKey
815                                                     , aname => 'IRC_CAND_PER_ID');
816                 attachDocument(l_notificationId,l_candidatePersonId,l_eventName,p_itemKey,l_person_type);
817               end if;
818               wf_notification.denormalize_notification(l_notificationId);
819               if l_dft_lang <> l_user_lang then
820                  DBMS_SESSION.SET_NLS('NLS_LANGUAGE',l_dft_lang);
821               end if;
822             result := 'COMPLETE:IRC_E';
823             hr_utility.set_location('Recipients Exist - Exiting:'|| g_package||'.'||l_proc, 200);
824           end if;
825         else
826           hr_utility.set_location('No more Recipients - Exiting:'|| g_package||'.'||l_proc, 220);
827           result := 'COMPLETE:IRC_NE';
828         end if;
829       else
830         hr_utility.set_location('Profile value for AME Transaction Type not set - Exiting :'|| g_package||'.'||l_proc, 240);
831         result := 'COMPLETE:IRC_NE';
832       end if;
833     exception
834       when e_ameException then
835         hr_utility.set_location('AME Error - Exiting:'|| g_package||'.'||l_proc, 260);
836         hr_utility.set_location('Error Message:'|| SQLERRM, 270);
837         result := 'COMPLETE:IRC_NE';
838       when e_messageNameIsNull then
839         hr_utility.set_location('Error - Skipping'|| g_package||'.'||l_proc, 300);
840         hr_utility.set_location('Error Message:'|| 'FND message not defined for this recipient', 310);
841         result := 'COMPLETE:IRC_E';
842       when others then
843         hr_utility.set_location('Error - Skipping:'|| g_package||'.'||l_proc, 350);
844         hr_utility.set_location('Error Message:'|| SQLERRM, 360);
845         DBMS_SESSION.SET_NLS('NLS_LANGUAGE',l_dft_lang);
846         result := 'COMPLETE:IRC_E';
847   end getNextRecipient;
848 --+
849 --+ getWFAttrValue
850 --+
851   function getWFAttrValue ( p_itemKey in varchar2
852                           , p_WFAttr  in varchar2 ) return varchar2 is
853     l_value varchar2(1000);
854     l_proc constant varchar2(50) := 'getWFAttrValue';
855     begin
856       hr_utility.set_location('Entering:'|| g_package||'.'||l_proc, 10);
857       g_WFItemType := fnd_profile.value('IRC_NTF_WF_ITEM_TYPE');
858       hr_utility.set_location('Workflow Attribute : '|| p_WFAttr, 20);
859       l_value := wf_engine.getitemattrtext( itemtype => g_WFItemType
860                                           , itemkey  => p_itemKey
861                                           , aname    => p_WFAttr);
862       hr_utility.set_location('Success - Exiting:'|| g_package||'.'||l_proc, 60);
863       return l_value;
864     exception
865       when others then
866         hr_utility.set_location('Error - Exiting:'|| g_package||'.'||l_proc, 100);
867         hr_utility.set_location('Error Message : ' || sqlerrm, 110);
868         return null;
869   end getWFAttrValue;
870 --+
871 --+ isValidRecipient
872 --+
873   function isValidRecipient (p_recipient in VARCHAR2) return varchar2
874   is
875     cursor csrRoleExists (c_orig_system in VARCHAR2, c_orig_system_id in number) is
876       select count(*)
877         from wf_roles
878        where c_orig_system = orig_system
879          and c_orig_system_id = orig_system_id
880          and status = 'ACTIVE';
881     l_roleExists number;
882     l_orig_system varchar2(50);
883     l_orig_system_id number;
884     l_pos number;
885     l_proc constant varchar2(50) := 'isValidRecipient';
886     begin
887       hr_utility.set_location('Entering:'|| g_package||'.'||l_proc, 10);
888       l_pos:=instr(p_recipient,':');
889       l_orig_system:=substr(p_recipient,1,l_pos-1);
890       l_orig_system_id:=substr(p_recipient,l_pos+1,length(p_recipient));
891       open csrRoleExists(l_orig_system,l_orig_system_id);
892       fetch csrRoleExists into l_roleExists;
893       close csrRoleExists;
894       if l_roleExists > 0 then
895         return 'true';
896       else
897         return 'false';
898       end if;
899     exception
900       when others then
901         hr_utility.set_location('Error - Exiting:'|| g_package||'.'||l_proc, 50);
902         hr_utility.set_location('Error Message : ' || sqlerrm, 60);
903   end isValidRecipient;
904 
905   function checkIfIntvwCandidateIncluded ( p_modifiedItemsString varchar2
906                                          , p_eventName varchar2)
907     return varchar2 is
908     cursor csrCheckModifiedItems(c_modifiedItemsString varchar2) is
909       select 'true'
910       from dual
911       where instr(c_modifiedItemsString,',LOCATIONID,')>0
912             OR instr(c_modifiedItemsString,',DATESTART,')>0
913             OR instr(c_modifiedItemsString,',DATEEND,')>0
914             OR instr(c_modifiedItemsString,',TIMEEND,')>0
915             OR instr(c_modifiedItemsString,',TIMESTART,')>0
916             OR instr(c_modifiedItemsString,',CONTACTTELEPHONENUMBER,')>0
917             OR instr(c_modifiedItemsString,',INTERNALCONTACTPERSONID,')>0
918             OR instr(c_modifiedItemsString,',EXTERNALCONTACT,')>0
919             OR instr(c_modifiedItemsString,',NOTESTOCANDIDATE,')>0
920             OR instr(c_modifiedItemsString,',CATEGORY,')>0
921             OR instr(c_modifiedItemsString,',STATUS,')>0;
922     l_includeCandidate varchar2(10);
923     l_proc constant varchar2(50) := 'checkIfIntvwCandidateIncluded';
924     begin
925       hr_utility.set_location('Entering:'|| g_package||'.'||l_proc, 10);
926       if p_eventName =  'INTVCRE' then
927         l_includeCandidate := 'true';
928       else
929       open csrCheckModifiedItems(',' || p_modifiedItemsString || ',');
930       fetch csrCheckModifiedItems into l_includeCandidate;
931       if csrCheckModifiedItems%NOTFOUND then
932         l_includeCandidate := 'false';
933       end if;
934       close csrCheckModifiedItems;
935       end if;
936       return l_includeCandidate;
937     exception
938       when others then
939         hr_utility.set_location('Error - Exiting:'|| g_package||'.'||l_proc, 50);
940         hr_utility.set_location('Error Message : ' || sqlerrm, 60);
941     end checkIfIntvwCandidateIncluded;
942 
943   procedure getDocument (p_documentId   in varchar2
944                         ,p_displayType  in varchar2
945                         ,p_document in  out nocopy varchar2
946                         ,p_documentType in out nocopy varchar2) is
947   begin
948     p_document := p_documentId;
949   end getDocument;
950 --+
951   procedure attatchICDoc( document_id   IN VARCHAR2
952                        ,display_type  IN VARCHAR2
953                        ,document      IN OUT nocopy blob
954                        ,document_type IN OUT nocopy VARCHAR2) is
955     l_position number;
956     l_item_key varchar2(100);
957     l_notification_id varchar2(100);
958     l_start_date date;
959     l_end_date date;
960     l_start_time varchar2(10);
961     l_end_time varchar2(10);
962     l_time_zone varchar2(100);
963     l_subject varchar2(400);
964     l_description varchar2(500);
965     l_data varchar2(32000);
966     l_ignore_time_zone boolean := false;
967     l_time_zone_code fnd_timezones_vl.timezone_code%type;
968     l_loc_description varchar2(240);
969     l_proc varchar2(20) := 'attatchICDoc';
970     l_itemType varchar2(50);
971     cursor getTimezoneCode(c_timezoneNameIn in varchar2) is
972       select timezone_code
973         from fnd_timezones_vl
974        where name = c_timezoneNameIn
975         and rownum < 2;
976   begin
977     hr_utility.set_location('Entering:'|| g_package||'.'||l_proc, 10);
978     l_itemType := fnd_profile.value('IRC_NTF_WF_ITEM_TYPE');
979     l_position := instrb(document_id,':',1,1);
980   -- heredocumentid represent the item key and notification_id, which is used to get the required values
981     if l_position is null then
982       return;
983     else
984       l_item_key := substr(document_id,1,l_position-1);
985       l_notification_id := to_number(substr(document_id,l_position+1,length(document_id)));
986     end if;
987     hr_utility.set_location('getting the interview details', 20);
988     l_start_date := wf_engine.GetItemAttrDate ( itemtype => l_itemType, itemkey  => l_item_key,aname => 'IRC_INTVW_DATE_START');
989     l_end_date := wf_engine.GetItemAttrDate ( itemtype => l_itemType, itemkey  => l_item_key,aname => 'IRC_INTVW_DATE_END');
990     l_start_time := wf_engine.getItemAttrText ( itemtype => l_itemType, itemkey  => l_item_key,aname => 'IRC_INTVW_TIME_START',ignore_notfound=>true);
991     l_end_time := wf_engine.getItemAttrText ( itemtype => l_itemType, itemkey  => l_item_key,aname => 'IRC_INTVW_TIME_END',ignore_notfound=>true);
992     l_time_zone := wf_engine.getItemAttrText ( itemtype => l_itemType, itemkey  => l_item_key,aname => 'IRC_INTVW_TIME_ZONE',ignore_notfound=>true);
993     l_loc_description := wf_engine.getItemAttrText ( itemtype => l_itemType, itemkey  => l_item_key,aname => 'IRC_INTVW_LOC_DETAILS',ignore_notfound=>true);
994     if l_time_zone is null then
995       l_ignore_time_zone := true;
996     else
997       open getTimezoneCode(l_time_zone);
998       fetch getTimezoneCode into l_time_zone_code;
999       close getTimezoneCode;
1000       if l_time_zone_code is null then
1001         l_ignore_time_zone := true;
1002       end if;
1003     end if;
1004     l_subject := wf_notification.GetAttrText (nid => l_notification_id, aname => 'SUBJECT',ignore_notfound => true);
1005     --l_description := wf_notification.GetAttrText (nid => l_notification_id, aname => 'TEXT_BODY',ignore_notfound => true);
1006     hr_utility.set_location('Contructing the ics file', 20);
1007     per_calendar_util.calendar_generate_ical
1008       (DTSTARTDATE	=> l_start_date
1009       ,DTENDDATE 	=> l_end_date
1010       ,DTSTARTTIME 	=> l_start_time
1011       ,DTENDTIME	=> l_end_time
1012       ,DTTIMEFORMAT     => 'HH24:MI'
1013       ,TIMEZONE         => l_time_zone_code
1014       ,SUBJECT          => l_subject
1015       ,LOCATION         => l_loc_description
1016       ,IGNORE_TIME_ZONE => l_ignore_time_zone
1017       ,ICAL	        => l_data
1018       );
1019     document := to_blob(UTL_RAW.CAST_TO_RAW(l_data));
1020     document_type := 'text/calendar' || ';name=event.ics';
1021     hr_utility.set_location('completed:'|| g_package||'.'||l_proc, 30);
1022     exception
1023       when others then
1024         hr_utility.set_location('Error:'|| g_package||'.'||l_proc, 50);
1025         hr_utility.set_location('Error:'|| sqlerrm, 50);
1026   end attatchICDoc;
1027 --+
1028 end IRC_NOTIFICATION_WORKFLOW_PKG;