[Home] [Help]
PACKAGE BODY: APPS.IRC_NOTIFICATION_WORKFLOW_PKG
Source
1 PACKAGE BODY IRC_NOTIFICATION_WORKFLOW_PKG as
2 /* $Header: irntfwfl.pkb 120.14.12010000.4 2009/01/19 13:48:31 prasashe 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(500);
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_topicId number;
52 l_communicationObjectType varchar2(30);
53 l_messageId number;
54 l_interviewId number;
55 l_interviewStatusCode varchar2(50);
56 l_eventName varchar2(50);
57 l_eventKey number;
58 l_actionPerformerId number;
59 l_itemType varchar2(50);
60 l_itemKey varchar2(50);
61 l_proc constant varchar2(50) := 'launchNotificationsWorkflow';
62 begin
63 hr_utility.set_location('Entering:'|| g_package||'.'||l_proc, 10);
64 l_eventData := p_event.event_data;
65 l_assignmentId := IRC_NOTIFICATION_DATA_PKG.getParamValue
66 ( p_param => 'IRC_ASSIGNMENT_ID'
67 , p_eventData => l_eventData);
68 hr_utility.set_location('Assignment Id : ' || l_assignmentId, 20);
69 l_eventName := IRC_NOTIFICATION_DATA_PKG.getParamValue
70 ( p_param => 'IRC_EVENT_NAME'
71 , p_eventData => l_eventData);
72 hr_utility.set_location('Event Name : ' || l_eventName, 30);
73 l_effectiveDate := to_date( IRC_NOTIFICATION_DATA_PKG.getParamValue
74 ( p_param => 'IRC_EFFECTIVE_DATE'
75 , p_eventData => l_eventData)
76 , 'DD-MM-RRRR');
77 if l_effectiveDate is null then
78 l_effectiveDate := sysdate;
79 end if;
80 hr_utility.set_location('Effective Date : ' || l_effectiveDate, 40);
81 l_vacancyId := IRC_NOTIFICATION_DATA_PKG.getParamValue
82 ( p_param => 'IRC_VACANCY_ID'
83 , p_eventData => l_eventData);
84 if l_vacancyId is null then
85 l_vacancyId := IRC_NOTIFICATION_DATA_PKG.getVacancyId
86 ( p_assignmentId => l_assignmentId
87 , p_effectiveDate => l_effectiveDate);
88 l_eventData := l_eventData
89 || 'IRC_VACANCY_ID:'
90 || l_vacancyId
91 ||';';
92 end if;
93 hr_utility.set_location('Vacancy Id : ' || l_vacancyId, 50);
94 if l_vacancyId is not null then
95 l_eventData := l_eventData
96 || IRC_NOTIFICATION_DATA_PKG.getVacancyDetails
97 ( p_vacancyId => l_vacancyId
98 , p_effectiveDate => l_effectiveDate);
99 end if;
100 l_candidateId := IRC_NOTIFICATION_DATA_PKG.getParamValue
101 ( p_param => 'IRC_CAND_PER_ID'
102 , p_eventData => l_eventData);
103 if l_candidateId is null then
104 l_candidateId := IRC_NOTIFICATION_DATA_PKG.getCandidatePersonId
105 ( p_assignmentId => l_assignmentId
106 , p_effectiveDate => l_effectiveDate);
107 l_candidateId := irc_utilities_pkg.GET_RECRUITMENT_PERSON_ID
108 ( p_person_id => l_candidateId
109 , p_effective_date => l_effectiveDate);
110 end if;
111 hr_utility.set_location('Candidate Id : ' || l_candidateId, 60);
112 if l_candidateId is not null then
113 l_eventData := l_eventData
114 || 'IRC_CAND_PER_ID:'
115 || l_candidateId
116 ||';';
117 l_eventData := l_eventData
118 || IRC_NOTIFICATION_DATA_PKG.getPersonDetails
119 ( p_personId => l_candidateId
120 , p_role => 'CAND'
121 , p_effectiveDate => l_effectiveDate);
122 l_eventData := l_eventData
123 || IRC_NOTIFICATION_DATA_PKG.getCandidateAgencyId
124 (p_candidateId => l_candidateId
125 , p_effectiveDate => l_effectiveDate);
126 end if;
127 l_managerId := IRC_NOTIFICATION_DATA_PKG.getManagerPersonId
128 ( p_vacancyId => l_vacancyId
129 , p_effectiveDate => l_effectiveDate);
130 hr_utility.set_location('Manager Id : ' || l_managerId, 70);
131 if l_managerId IS NOT null then
132 l_eventData := l_eventData
133 || 'IRC_MGR_PER_ID:'
134 || l_managerId
135 ||';';
136 l_eventData := l_eventData
137 || IRC_NOTIFICATION_DATA_PKG.getPersonDetails
138 ( p_personId => l_managerId
139 , p_role => 'MGR'
140 , p_effectiveDate => l_effectiveDate);
141 end if;
142 l_recruiterId := IRC_NOTIFICATION_DATA_PKG.getRecruiterPersonId
143 ( p_assignmentId => l_assignmentId
144 , p_effectiveDate => l_effectiveDate);
145 hr_utility.set_location('Recruiter Id : ' || l_recruiterId, 80);
146 if l_recruiterId IS NOT null then
147 l_eventData := l_eventData
148 || 'IRC_REC_PER_ID:'
149 || l_recruiterId
150 ||';';
151 l_eventData := l_eventData
152 || IRC_NOTIFICATION_DATA_PKG.getPersonDetails
153 ( p_personId => l_recruiterId
154 , p_role => 'REC'
155 , p_effectiveDate => l_effectiveDate);
156 end if;
157 l_referrerId := IRC_NOTIFICATION_DATA_PKG.getParamValue
158 ( p_param => 'IRC_REFR_PER_ID'
159 , p_eventData => l_eventData);
160 hr_utility.set_location('Referrer Id : ' || l_referrerId, 90);
161 if l_referrerId IS NOT null then
162 l_eventData := l_eventData
163 || IRC_NOTIFICATION_DATA_PKG.getPersonDetails
164 ( p_personId => l_referrerId
165 , p_role => 'REFR'
166 , p_effectiveDate => l_effectiveDate);
167 end if;
168 l_actionPerformerId := IRC_NOTIFICATION_DATA_PKG.getParamValue
169 ( p_param => 'IRC_ACT_PERF_PER_ID'
170 , p_eventData => l_eventData);
171 hr_utility.set_location('Action Performer Id : ' || l_actionPerformerId, 100);
172 if l_actionPerformerId is not null then
173 l_eventData := l_eventData
174 || IRC_NOTIFICATION_DATA_PKG.getPersonDetails
175 ( p_personId => l_actionPerformerId
176 , p_role => 'ACT_PERF'
177 , p_effectiveDate => l_effectiveDate);
178 end if;
179 if l_eventName = 'APLSTACHG' then
180 l_assignmentStatusCode := IRC_NOTIFICATION_DATA_PKG.getparamvalue
181 ( p_param => 'IRC_JOB_APPL_NEW_STATUS_CODE'
182 , p_eventdata => l_eventData);
183 l_eventData := l_eventData
184 || IRC_NOTIFICATION_DATA_PKG.getApplicationStatus
185 ( p_assignmentStatusCode => l_assignmentStatusCode);
186 end if;
187 if l_eventName = 'COMTOPCRE' or l_eventName = 'COMTOPUPD' then
188 l_communicationObjectType := IRC_NOTIFICATION_DATA_PKG.getParamValue
189 ( p_param => 'IRC_COMM_OBJ_TYPE'
190 , p_eventData => l_eventData);
191 if(l_communicationObjectType is null) then
192 l_communicationObjectType := 'TOPIC';
193 end if;
194 if(l_communicationObjectType = 'TOPIC') then
195 l_topicId := IRC_NOTIFICATION_DATA_PKG.getParamValue
196 ( p_param => 'IRC_COMM_OBJ_ID'
197 , p_eventData => l_eventData);
198 l_messageId := IRC_NOTIFICATION_DATA_PKG.getParamValue
199 ( p_param => 'IRC_COMM_MSG_ID'
200 , p_eventData => l_eventData);
201 l_eventData := l_eventData
202 || IRC_NOTIFICATION_DATA_PKG.getCommunicationTopicDetails
203 ( p_topicId => l_topicId
204 , p_messageId => l_messageId);
205 end if;
206 end if;
207 if l_eventName = 'INTVCRE' or l_eventName = 'INTVUPD' then
208 l_interviewId := IRC_NOTIFICATION_DATA_PKG.getParamValue
209 ( p_param => 'IRC_INTVW_ID'
210 , p_eventData => l_eventData);
211 l_eventData := l_eventData
212 || IRC_NOTIFICATION_DATA_PKG.getInterviewDetails
213 ( p_interviewId => l_interviewId
214 , p_effectiveDate => l_effectiveDate);
215 l_interviewStatusCode := IRC_NOTIFICATION_DATA_PKG.getParamValue
216 ( p_param => 'IRC_INTVW_NEW_STATUS_CODE'
217 , p_eventData => l_eventData);
218 l_eventData := l_eventData
219 || IRC_NOTIFICATION_DATA_PKG.getinterviewstatusmeaning
220 ( p_interviewstatuscode => l_interviewStatusCode
221 , p_attributename => 'IRC_INTVW_NEW_STATUS');
222 l_interviewStatusCode := IRC_NOTIFICATION_DATA_PKG.getParamValue
223 ( p_param => 'IRC_INTVW_OLD_STATUS_CODE'
224 , p_eventData => l_eventData);
225 l_eventData := l_eventData
226 || IRC_NOTIFICATION_DATA_PKG.getinterviewstatusmeaning
227 ( p_interviewstatuscode => l_interviewStatusCode
228 , p_attributename => 'IRC_INTVW_OLD_STATUS');
229 l_eventData := l_eventData
230 || IRC_NOTIFICATION_DATA_PKG.getinterviersnameshtml
231 ( p_interviewid => l_interviewId
232 , p_effectivedate => l_effectiveDate);
233 end if;
234 hr_utility.set_location('Populated all data:Create Workflow', 110);
235 hr_utility.set_location('Event Data : '||l_eventData, 120);
236 l_itemType := fnd_profile.value('IRC_NTF_WF_ITEM_TYPE');
237 hr_utility.set_location('Item Type : '||l_itemType, 130);
238 if l_itemType is not NULL then
239 l_itemKey := p_event.event_key;
240 wf_engine.CreateProcess(l_itemType
241 ,l_itemKey
242 ,'IRC_NOTIFICATION_PRC');
243 loadWorkflowAttributes ( p_eventData => l_eventData
244 , p_itemType => l_itemType
245 , p_itemKey => l_itemKey );
246 wf_engine.startprocess( l_itemType
247 , l_itemKey);
248 else
249 hr_utility.set_location('Workflow Item Type not set', 160);
250 end if;
251 hr_utility.set_location('Success - Exiting:'|| g_package||'.'||l_proc, 180);
252 return 'SUCCESS';
253 exception
254 when others then
255 hr_utility.set_location('Error - Exiting:'|| g_package||'.'||l_proc, 200);
256 hr_utility.set_location('Error Message: ' || sqlerrm, 210);
257 WF_CORE.CONTEXT('IRC_NOTIFICATIONS_WORKFLOW_PKG','launchNotificationsWorkflow',p_event.getEventName( ), p_subscriptionGuid);
258 WF_EVENT.setErrorInfo(p_event, 'ERROR');
259 return 'ERROR';
260 end launchNotificationsWorkflow;
261 --+
262 --+ parseAndReplaceFNDMessage
263 --+
264 function parseAndReplaceFNDMessage ( p_itemType in varchar2
265 , p_itemKey in varchar2
266 , p_message in varchar2 ) return varchar2 as
267 l_leftIndex number := 1;
268 l_rightIndex number ;
269 l_returnMessage varchar2(10000) := p_message;
270 l_token varchar2(100);
271 l_value varchar2(100):= null;
272 l_proc constant varchar2(50) := 'parseAndReplaceFNDMessage';
273 begin
274 hr_utility.set_location('Entering:'|| g_package||'.'||l_proc, 10);
275 while (l_leftIndex <> 0) loop
276 l_value := null;
277 l_leftIndex := instr(l_returnMessage, '&' || 'IRC');
278 if (l_leftIndex = 0) then
279 exit;
280 end if;
281 l_rightIndex := instr(l_returnMessage,' ', l_leftIndex);
282 if (l_rightIndex = 0) then
283 l_rightIndex := length(l_returnMessage); --This is to cater for token at last
284 end if;
285 l_token := substr(l_returnMessage,l_leftIndex + 1, l_rightIndex-l_leftIndex-1);
286 begin
287 l_value := wf_engine.GetItemAttrText( itemtype => p_itemType
288 , itemkey => p_itemKey
289 , aname => l_token);
290 exception
291 when others then
292 hr_utility.set_location('Error in getting Workflow attribute : ' || sqlerrm, 50);
293 end;
294 l_returnMessage := substr(l_returnMessage, 1, l_leftIndex -1 )
295 || l_value
296 || substr(l_returnMessage, l_rightIndex);
297 end loop;
298 hr_utility.set_location('Success - Exiting:'|| g_package||'.'||l_proc, 100);
299 return l_returnMessage;
300 exception
301 when others then
302 hr_utility.set_location('Error - Exiting:'|| g_package||'.'||l_proc, 120);
303 hr_utility.set_location('Error Message : ' || sqlerrm, 130);
304 end parseAndReplaceFNDMessage;
305 --+
306 --+ attatchDoc
307 --+
308 procedure attatchDoc( document_id IN VARCHAR2
309 ,display_type IN VARCHAR2
310 ,document IN OUT nocopy blob
314 is
311 ,document_type IN OUT nocopy VARCHAR2)
312
313
315 l_blob blob;
316 l_mimetype varchar2(30);
317 l_file_name varchar2(240);
318 p_document_id varchar2(100);
319 cursor csr_doc is
320 select binary_doc,
321 mime_type,
322 file_name
323 from irc_documents
324 where document_id = p_document_id;
325 begin
326 p_document_id := document_id;
327 open csr_doc;
328 fetch csr_doc into l_blob, l_mimetype,l_file_name;
329 close csr_doc;
330 document_type := l_mimetype||'; name='||l_file_name;
331 dbms_lob.copy(document,l_blob,dbms_lob.getlength(l_blob));
332 end attatchDoc;
333 --+
334 --attach document
335 --+
336 procedure attachDocument(p_notificationIdIn in number,p_personIdIn in varchar2
337 ,p_eventName in varchar2,p_itemKey in varchar2) is
338 l_doc_ids ame_util.idList;
339 l_proc varchar2(50);
340 l_person_id number;
341 l_count number;
342 l_doc_type varchar2(40);
343 l_intw_status varchar2(100);
344 l_intw_id number;
345 cursor chkResumeExts(c_person_id in number ) is
346 select count(*)
347 from irc_documents
348 where type = 'RESUME'
349 and party_id =
350 (select party_id
351 from per_all_people_f
352 where person_id = c_person_id
353 and trunc(sysdate) between effective_start_date and effective_end_Date)
354 and end_date is null;
355 cursor getDocIdList(c_personIdIn in number,c_doc_type in varchar2) is
356 select document_id
357 from irc_documents
358 where type = c_doc_type
359 and party_id =
360 (select party_id
361 from per_all_people_f
362 where person_id = c_personIdIn
363 and trunc(sysdate) between effective_start_date and effective_end_Date)
364 and end_Date is null;
365 cursor getInterViewStatus(c_interviewId in number) is
366 select status
367 from irc_interview_details
368 where event_id = c_interviewId
369 and sysdate between start_date and nvl(end_Date,sysdate);
370
371 begin
372 l_proc := 'attatchDocument';
373 open chkResumeExts(p_personIdIn);
374 fetch chkResumeExts into l_count;
375 close chkResumeExts;
376 if l_count > 0 then
377 l_doc_type := 'RESUME';
378 else
379 l_doc_type := 'AUTO_RESUME';
380 end if;
381 hr_utility.set_location('Entering attatchDocument:'|| g_package||'.'||l_proc, 10);
382 hr_utility.set_location('attatchDocument:p_personIdIn:'||p_personIdIn, 30);
383 hr_utility.set_location('attatchDocument:p_eventName:'||p_eventName, 40);
384 if p_personIdIn is not null then
385 l_person_id := to_number(p_personIdIn);
386 else
387 return;
388 end if;
389 if p_eventName is not null and p_eventName in ('INTVCRE','INTVUPD','APLFORJOB') then
390 if p_eventName in ('INTVCRE','INTVUPD') then
391 begin
392 l_intw_id := to_number(IRC_NOTIFICATION_WORKFLOW_PKG.getWFAttrValue(p_itemKey,'IRC_INTVW_ID'));
393 if l_intw_id is not null then
394 open getInterViewStatus(l_intw_id);
395 fetch getInterViewStatus into l_intw_status;
396 close getInterViewStatus;
397 end if;
398 if l_intw_status is not null and l_intw_status not in ('PLANNED','CONFIRMED','RESCHEDULED') then
399 return;
400 end if;
401 exception
402 when others then
403 hr_utility.set_location('error:'||sqlerrm, 30);
404 end;
405 end if;
406 open getDocIdList(l_person_id,l_doc_type);
407 fetch getDocIdList bulk collect into l_doc_ids;
408 close getDocIdList;
409 for i in 1..l_doc_ids.count loop
410 wf_notification.setAttrText(p_notificationIdIn,
411 'IRC_ATTACHMENT_'||to_char(i),
412 'plsqlblob:irc_notification_workflow_pkg.attatchDoc/'||l_doc_ids(i));
413 end loop;
414 end if;
415 hr_utility.set_location('Exiting attatchDocument:'|| g_package||'.'||l_proc, 60);
416 exception
417 when others then
418 hr_utility.set_location('Error occurred in :'|| g_package||'.'||l_proc, 70);
419 hr_utility.set_location('Error Message:'|| SQLERRM, 80);
420 end attachDocument;
421 --+
422 --+ getNextRecipient
423 --+
424 procedure getNextRecipient ( p_itemType in varchar2
425 , p_itemKey in varchar2
426 , p_activityId in number
427 , funmode in varchar2
428 , result out nocopy varchar2 ) is
429 l_ameTransactionType varchar2(50);
430 l_nextApprovers ame_util.approversTable2;
431 l_approvalProcessCompleteYNOut varchar2(1);
432 l_notificationId number;
433 l_messageSubjectName varchar2(30);
434 l_messageSubject varchar2(5000);
435 l_messageBodyName varchar2(30);
436 l_messageBody varchar2(30000);
437 l_eventName varchar2(50);
438 l_itemIndexesOut ame_util.idList;
439 l_itemClassesOut ame_util.stringList;
440 l_itemIdsOut ame_util.stringList;
444 l_variableValuesOut ame_util.stringList;
441 l_itemSourcesOut ame_util.longStringList;
442 l_productionIndexesOut ame_util.idList;
443 l_variableNamesOut ame_util.stringList;
445 l_actionPerformerId number;
446 e_ameException exception;
447 e_messageNameIsNull exception;
448 l_approverRole varchar2(50);
449 l_message_type varchar2(100);
450 l_proc constant varchar2(50) := 'getNextRecipient';
451 l_candidatePersonId varchar2(50);
452 l_person_type varchar2(50);
453 l_ntf_message_type varchar2(50);
454 BEGIN
455 hr_utility.set_location('Entering:'|| g_package||'.'||l_proc, 10);
456 l_ameTransactionType := fnd_profile.value('IRC_NTF_AME_TX_TYPE');
457 l_person_type := null;
458 if l_ameTransactionType is not NULL then
459 hr_utility.set_location('AME Transaction Type : ' || l_ameTransactionType, 20);
460 begin
461 ame_api2.getNextApprovers2(applicationIdIn => 800
462 , transactionTypeIn => l_ameTransactionType
463 , transactionIdIn => p_itemKey
464 , approvalProcessCompleteYNOut => l_approvalprocesscompleteynout
465 , nextApproversOut => l_nextApprovers
466 , itemIndexesOut => l_itemIndexesOut
467 , itemClassesOut => l_itemClassesOut
468 , itemIdsOut => l_itemIdsOut
469 , itemSourcesOut => l_itemSourcesOut
470 , productionIndexesOut => l_productionIndexesOut
471 , variableNamesOut => l_variableNamesOut
472 , variableValuesOut => l_variableValuesOut);
473 exception
474 when others then
475 raise e_ameException;
476 end;
477 hr_utility.set_location('AME Transaction Complete : ' || l_approvalprocesscompleteynout,30);
478 if l_approvalProcessCompleteYNOut = 'N' then
479 hr_utility.set_location('Recipient : ' || l_nextApprovers(1).name,60);
480
481 wf_engine.setItemAttrText ( itemtype => p_itemType
482 , itemkey => p_itemKey
483 , aname => 'IRC_APPROVER'
484 , avalue => l_nextApprovers(1).name );
485 l_eventName := wf_engine.GetItemAttrText( itemtype => p_itemType
486 , itemkey => p_itemKey
487 , aname => 'IRC_EVENT_NAME');
488 l_messageSubjectName := null;
489 l_messageBodyName := null;
490
491 for i in 1..l_variableNamesOut.count loop
492 if l_variableNamesOut(i) = 'IRECRUITMENT NOTIFICATION MSG SUBJECT' then
493 l_messageSubjectName := l_variableValuesOut(i);
494 elsif l_variableNamesOut(i) = 'IRECRUITMENT NOTIFICATION MSG BODY' then
495 l_messageBodyName := l_variableValuesOut(i);
496 elsif l_variableNamesOut(i) = 'IRC_ROLE' then
497 if l_person_type is null or l_person_type <> 'CAND' then
498 l_person_type := l_variableValuesOut(i);
499 end if;
500 end if;
501 end loop;
502
503 hr_utility.set_location('FND Message Name for Subject : ' || l_messageSubjectName,80);
504 hr_utility.set_location('FND Message Name for Body : ' || l_messageBodyName,90);
505 if l_messageSubjectName is null or l_messageBodyName is null then
506 raise e_messageNameIsNull;
507 else
508 fnd_message.set_name('PER',l_messageSubjectName);
509 l_messageSubject := parseAndReplaceFNDMessage( p_itemType => p_itemType
510 , p_itemKey => p_itemKey
511 , p_message => fnd_message.get);
512 hr_utility.set_location('Message Subject sent to Recipient: '||l_messageSubject,120);
513 wf_engine.setItemAttrText ( itemtype => p_itemType
514 , itemkey => p_itemKey
515 , aname => 'IRC_MESSAGE_SUBJECT'
516 , avalue => l_messageSubject);
517 fnd_message.set_name('PER',l_messageBodyName);
518 l_messageBody := parseAndReplaceFNDMessage( p_itemType => p_itemType
519 , p_itemKey => p_itemKey
520 , p_message => fnd_message.get);
521 hr_utility.set_location('Message Body sent to recipient: '||l_messageBody,150);
522 wf_engine.setitemattrtext( itemtype => p_itemType
523 , itemkey => p_itemKey
524 , aname => 'IRC_MESSAGE_BODY'
525 , avalue => l_messageBody);
526 l_message_type :=fnd_profile.value('IRC_NTF_WF_ITEM_TYPE');
527 if l_person_type <> 'CAND' and l_eventName in ('INTVCRE','INTVUPD','APLFORJOB') then
528 l_ntf_message_type := 'IRC_MESSAGE_WITH_ATTACHMENT';
529 else
530 l_ntf_message_type := 'IRC_MESSAGE';
531 end if;
532 l_notificationId:=wf_notification.send( l_nextApprovers(1).name
533 , l_message_type
534 , l_ntf_message_type
535 );
536 hr_utility.set_location('Notification ID : ' || l_notificationId,180);
537 wf_notification.setAttrText (l_notificationId, '#FROM_ROLE', 'SYSADMIN');
538 wf_notification.setAttrText(l_notificationId, 'SUBJECT', l_messageSubject);
539 wf_notification.setAttrText(l_notificationId, 'TEXT_BODY', l_messageBody);
540 wf_notification.setAttrText(l_notificationId, 'HTML_BODY', l_messageBody);
541 if l_ntf_message_type = 'IRC_MESSAGE_WITH_ATTACHMENT' then
542 l_candidatePersonId := wf_engine.GetItemAttrText( itemtype => p_itemType
543 , itemkey => p_itemKey
544 , aname => 'IRC_CAND_PER_ID');
545 attachDocument(l_notificationId,l_candidatePersonId,l_eventName,p_itemKey);
546 end if;
547 wf_notification.denormalize_notification(l_notificationId);
548 result := 'COMPLETE:IRC_E';
549 hr_utility.set_location('Recipients Exist - Exiting:'|| g_package||'.'||l_proc, 200);
550 end if;
551 else
552 hr_utility.set_location('No more Recipients - Exiting:'|| g_package||'.'||l_proc, 220);
553 result := 'COMPLETE:IRC_NE';
554 end if;
555 else
556 hr_utility.set_location('Profile value for AME Transaction Type not set - Exiting :'|| g_package||'.'||l_proc, 240);
557 result := 'COMPLETE:IRC_NE';
558 end if;
559 exception
560 when e_ameException then
561 hr_utility.set_location('AME Error - Exiting:'|| g_package||'.'||l_proc, 260);
562 hr_utility.set_location('Error Message:'|| SQLERRM, 270);
563 result := 'COMPLETE:IRC_NE';
564 when e_messageNameIsNull then
565 hr_utility.set_location('Error - Skipping'|| g_package||'.'||l_proc, 300);
566 hr_utility.set_location('Error Message:'|| 'FND message not defined for this recipient', 310);
567 result := 'COMPLETE:IRC_E';
568 when others then
569 hr_utility.set_location('Error - Skipping:'|| g_package||'.'||l_proc, 350);
570 hr_utility.set_location('Error Message:'|| SQLERRM, 360);
571 result := 'COMPLETE:IRC_E';
572 end getNextRecipient;
573 --+
574 --+ getWFAttrValue
575 --+
576 function getWFAttrValue ( p_itemKey in varchar2
577 , p_WFAttr in varchar2 ) return varchar2 is
578 l_value varchar2(1000);
579 l_proc constant varchar2(50) := 'getWFAttrValue';
580 begin
581 hr_utility.set_location('Entering:'|| g_package||'.'||l_proc, 10);
582 g_WFItemType := fnd_profile.value('IRC_NTF_WF_ITEM_TYPE');
583 hr_utility.set_location('Workflow Attribute : '|| p_WFAttr, 20);
584 l_value := wf_engine.getitemattrtext( itemtype => g_WFItemType
585 , itemkey => p_itemKey
586 , aname => p_WFAttr);
587 hr_utility.set_location('Success - Exiting:'|| g_package||'.'||l_proc, 60);
588 return l_value;
589 exception
590 when others then
591 hr_utility.set_location('Error - Exiting:'|| g_package||'.'||l_proc, 100);
592 hr_utility.set_location('Error Message : ' || sqlerrm, 110);
593 return null;
594 end getWFAttrValue;
595 --+
596 --+ isValidRecipient
597 --+
598 function isValidRecipient (p_recipient in VARCHAR2) return varchar2
599 is
600 cursor csrRoleExists (c_recipient in VARCHAR2) is
601 select count(*)
602 from wf_roles
603 where c_recipient = orig_system || ':' || orig_system_id
604 and status = 'ACTIVE';
605 l_roleExists number;
606 l_proc constant varchar2(50) := 'isValidRecipient';
607 begin
608 hr_utility.set_location('Entering:'|| g_package||'.'||l_proc, 10);
609 open csrRoleExists(p_recipient);
610 fetch csrRoleExists into l_roleExists;
611 close csrRoleExists;
612 if l_roleExists > 0 then
613 return 'true';
614 else
615 return 'false';
616 end if;
617 exception
618 when others then
619 hr_utility.set_location('Error - Exiting:'|| g_package||'.'||l_proc, 50);
620 hr_utility.set_location('Error Message : ' || sqlerrm, 60);
621 end isValidRecipient;
622
623 function checkIfIntvwCandidateIncluded ( p_modifiedItemsString varchar2
624 , p_eventName varchar2)
625 return varchar2 is
626 cursor csrCheckModifiedItems(c_modifiedItemsString varchar2) is
627 select 'true'
628 from dual
629 where instr(c_modifiedItemsString,',LOCATIONID,')>0
630 OR instr(c_modifiedItemsString,',DATESTART,')>0
631 OR instr(c_modifiedItemsString,',DATEEND,')>0
632 OR instr(c_modifiedItemsString,',TIMEEND,')>0
633 OR instr(c_modifiedItemsString,',TIMESTART,')>0
634 OR instr(c_modifiedItemsString,',CONTACTTELEPHONENUMBER,')>0
635 OR instr(c_modifiedItemsString,',INTERNALCONTACTPERSONID,')>0
636 OR instr(c_modifiedItemsString,',EXTERNALCONTACT,')>0
637 OR instr(c_modifiedItemsString,',NOTESTOCANDIDATE,')>0
638 OR instr(c_modifiedItemsString,',CATEGORY,')>0
639 OR instr(c_modifiedItemsString,',STATUS,')>0;
640 l_includeCandidate varchar2(10);
641 l_proc constant varchar2(50) := 'checkIfIntvwCandidateIncluded';
642 begin
643 hr_utility.set_location('Entering:'|| g_package||'.'||l_proc, 10);
644 if p_eventName = 'INTVCRE' then
645 l_includeCandidate := 'true';
646 else
647 open csrCheckModifiedItems(',' || p_modifiedItemsString || ',');
648 fetch csrCheckModifiedItems into l_includeCandidate;
649 if csrCheckModifiedItems%NOTFOUND then
650 l_includeCandidate := 'false';
651 end if;
652 close csrCheckModifiedItems;
653 end if;
654 return l_includeCandidate;
655 exception
656 when others then
657 hr_utility.set_location('Error - Exiting:'|| g_package||'.'||l_proc, 50);
658 hr_utility.set_location('Error Message : ' || sqlerrm, 60);
659 end checkIfIntvwCandidateIncluded;
660
661 procedure getDocument (p_documentId in varchar2
662 ,p_displayType in varchar2
663 ,p_document in out nocopy varchar2
664 ,p_documentType in out nocopy varchar2) is
665 begin
666 p_document := p_documentId;
667 end getDocument;
668 end IRC_NOTIFICATION_WORKFLOW_PKG;