[Home] [Help]
PACKAGE BODY: APPS.IRC_NOTIFICATION_DATA_PKG
Source
1 PACKAGE BODY IRC_NOTIFICATION_DATA_PKG as
2 /* $Header: irntfdat.pkb 120.17.12020000.7 2013/03/07 08:09:47 pshreera ship $ */
3 --+
4 --+ getParamValue
5 --+
6 function getParamValue ( p_param in varchar2
7 , p_eventData in varchar2) return varchar2 is
8 l_index number;
9 l_leftIndex number;
10 l_rightIndex number;
11 l_proc varchar2(50) := 'getParamValue';
12 begin
13 l_index := instr(p_eventData, p_param||':');
14 if( l_index = 0) then
15 return null;
16 end if;
17 l_leftIndex := instr(p_eventData, ':', l_index);
18 l_rightIndex := instr(p_eventData, ';', l_index);
19 return substr(p_eventData
20 ,l_leftIndex + 1
21 ,l_rightIndex-1-l_leftIndex);
22 exception
23 when others then
24 hr_utility.set_location('Error : ' || g_package||'.'|| l_proc ,10);
25 hr_utility.set_location('Error Message: ' || sqlerrm, 20);
26 return null;
27 end getParamValue;
28 --+
29 --+ getVacancyId
30 --+
31 function getVacancyId ( p_assignmentId in number
32 , p_effectiveDate in date) return number as
33 cursor csrVacancyId(c_assignmentId in number
34 ,c_effectiveDate in date) is
35 select vacancy_id
36 from per_all_assignments_f
37 where assignment_id = c_assignmentId
38 and trunc(c_effectiveDate)
39 between trunc(effective_start_date) and trunc(effective_end_date);
40 l_vacancyId number;
41 l_func varchar2(50) := 'getVacancyId';
42 begin
43 open csrVacancyId(p_assignmentId, p_effectiveDate);
44 fetch csrVacancyId into l_vacancyId;
45 close csrVacancyId;
46 return l_vacancyId;
47 exception
48 when no_data_found then
49 hr_utility.set_location('No Data Found Error : ' || g_package||'.'|| l_func ,10);
50 return null;
51 when others then
52 hr_utility.set_location('Error : ' || g_package||'.'|| l_func ,20);
53 hr_utility.set_location('Error Message: ' || sqlerrm, 30);
54 return null;
55 end getVacancyId;
56 --+
57 --+ getCandidatePersonId
58 --+
59 function getCandidatePersonId ( p_assignmentId in number
60 , p_effectiveDate in date
61 , p_event_name in varchar2 default null) return number as
62 cursor csrCandidateId(c_assignmentId in number
63 ,c_effectiveDate in date) is
64 select person_id
65 from per_all_assignments_f
66 where assignment_id = c_assignmentId
67 and trunc(c_effectiveDate)
68 between trunc(effective_start_date) and trunc(effective_end_date);
69 cursor csrTermntdCandidateId(c_assignmentId in number) is
70 select person_id
71 from per_all_assignments_f
72 where assignment_id = c_assignmentId
73 and rownum<2;
74
75 l_candidateId number;
76 l_func varchar2(50) := 'getCandidatePersonId';
77 begin
78 open csrCandidateId(p_assignmentId, p_effectiveDate);
79 fetch csrCandidateId into l_candidateId;
80 if(csrCandidateId%NOTFOUND and (p_event_name = 'COMTOPCRE' or p_event_name = 'COMTOPUPD')) then
81 open csrTermntdCandidateId(p_assignmentId);
82 fetch csrTermntdCandidateId into l_candidateId;
83 close csrTermntdCandidateId;
84 end if;
85 close csrCandidateId;
86 return l_candidateId;
87 exception
88 when no_data_found then
89 hr_utility.set_location('No Data Found Error : ' || g_package||'.'|| l_func ,10);
90 return null;
91 when others then
92 hr_utility.set_location('Error : ' || g_package||'.'|| l_func ,20);
93 hr_utility.set_location('Error Message: ' || sqlerrm, 30);
94 return null;
95 end getCandidatePersonId;
96 --+
97 --+
98 --+
99 function getCandidateAgencyId(p_candidateId in number
100 ,p_effectiveDate in date)
101 return varchar2 is
102 cursor csrCandidateAgencyId (c_candidateId in number
103 ,c_effectiveDate in date) is
104 select 'IRC_CAND_AGENCY_ID:'
105 || inp.agency_id
106 || ';'
107 from irc_notification_preferences inp
108 ,per_all_people_f ppf
109 where inp.person_id = c_candidateId
110 and ppf.person_id = c_candidateId
111 and c_effectiveDate between ppf.effective_start_date
112 and ppf.effective_end_date;
113 l_candidateAgencyId varchar2(100);
114 l_func varchar2(50) := 'getCandidateAgencyId';
115 begin
116 open csrCandidateAgencyId(p_candidateId, p_effectiveDate);
117 fetch csrCandidateAgencyId into l_candidateAgencyId;
118 close csrCandidateAgencyId;
119 return l_candidateAgencyId;
120 exception
121 when no_data_found then
122 hr_utility.set_location('No Data Found Error : ' || g_package||'.'|| l_func ,10);
123 return null;
124 when others then
125 hr_utility.set_location('Error : ' || g_package||'.'|| l_func ,20);
126 hr_utility.set_location('Error Message: ' || sqlerrm, 30);
127 return null;
128 end getCandidateAgencyId;
129 --+
130 --+ getManagerPersonId
131 --+
132 function getManagerPersonId ( p_vacancyId in number
133 , p_effectiveDate in date) return number as
134 cursor csrManagerId(c_vacancyId in number
135 ,c_effectiveDate in date) is
136 select manager_id
137 from per_all_vacancies
138 where vacancy_id = c_vacancyId
139 and trunc(c_effectiveDate)
140 between trunc(date_from) and nvl(trunc(date_to),to_date('31-12-4712','DD-MM-RRRR'));
141 l_managerId number;
142 l_func varchar2(50) := 'getManagerPersonId';
143 begin
144 open csrManagerId(p_vacancyId, p_effectiveDate);
145 fetch csrManagerId into l_managerId;
146 close csrManagerId;
147 return l_managerId;
148 exception
149 when no_data_found then
150 hr_utility.set_location('No Data Found Error : ' || g_package||'.'|| l_func ,10);
151 return null;
152 when others then
153 hr_utility.set_location('Error : ' || g_package||'.'|| l_func ,20);
154 hr_utility.set_location('Error Message: ' || sqlerrm, 30);
155 return null;
156 end getManagerPersonId;
157 --+
158 --+ getRecruiterPersonId
159 --+
160 function getRecruiterPersonId ( p_assignmentId in number
161 , p_effectiveDate in date) return number as
162 cursor csrRecruiterId(c_assignmentId in number
163 ,c_effectiveDate in date) is
164 select recruiter_id
165 from per_all_assignments_f
166 where assignment_id = c_assignmentId
167 and trunc(c_effectiveDate)
168 between trunc(effective_start_date) and trunc(effective_end_date);
169 l_recruiterId number;
170 l_func varchar2(50) := 'getRecruiterPersonId';
171 begin
172 open csrRecruiterId(p_assignmentId, p_effectiveDate);
173 fetch csrRecruiterId into l_recruiterId;
174 close csrRecruiterId;
175 return l_recruiterId;
176 exception
177 when no_data_found then
178 hr_utility.set_location('No Data Found Error : ' || g_package||'.'|| l_func ,10);
179 return null;
180 when others then
181 hr_utility.set_location('Error : ' || g_package||'.'|| l_func ,20);
182 hr_utility.set_location('Error Message: ' || sqlerrm, 30);
183 return null;
184 end getRecruiterPersonId;
185 --+
186 --+ getVacancyDetails
187 --+
188 function getVacancyDetails ( p_vacancyId in number
189 , p_effectiveDate in date) return varchar2 as
190 cursor csrVacancyDetails(c_vacancyId in number
191 ,c_effectiveDate in date) is
192 select 'IRC_VACANCY_NAME:'
193 || pav.name
194 || ';IRC_VAC_BG_ID:'
195 || pav.business_group_id
196 || ';IRC_VAC_CATEGORY:'
197 || pav.vacancy_category
198 || ';IRC_VAC_JOB_TITLE:'
199 || pj.name
200 || ';IRC_VAC_POSITION_TITLE:'
201 || pp.name
202 || ';IRC_POSTING_ID:'
203 || pav.primary_posting_id
204 || ';IRC_JOB_POSTING_TITLE:'
205 || ipc.name
206 || ';IRC_RECRUITING_SITE_ID:'
207 || pra.recruiting_site_id
208 ||';'
209 from per_all_vacancies pav,
210 per_jobs pj,
211 hr_all_positions_f pp,
212 per_recruitment_activities pra,
213 irc_posting_contents_vl ipc
214 where vacancy_id = c_vacancyId
215 and pav.job_id = pj.job_id(+)
216 and pav.position_id = pp.position_id(+)
217 and trunc(c_effectiveDate) between trunc(pav.date_from)
218 and nvl(trunc(pav.date_to),to_date('31-12-4712','DD-MM-RRRR'))
219 and trunc(c_effectiveDate)
220 between trunc(pp.effective_start_date(+)) and trunc(pp.effective_end_date(+))
221 and pra.posting_content_id(+) = pav.primary_posting_id
222 and ipc.posting_content_id(+) = pav.primary_posting_id
223 and rownum = 1;
224 l_vacancyDetails varchar2(500);
225 l_func varchar2(50) := 'getVacancyDetails';
226 begin
227 open csrVacancyDetails(p_vacancyId, p_effectiveDate);
228 fetch csrVacancyDetails into l_vacancyDetails;
229 close csrVacancyDetails;
230 return l_vacancyDetails;
231 exception
232 when no_data_found then
233 hr_utility.set_location('No Data Found Error : ' || g_package||'.'|| l_func ,10);
234 return null;
235 when others then
236 hr_utility.set_location('Error : ' || g_package||'.'|| l_func ,20);
237 hr_utility.set_location('Error Message: ' || sqlerrm, 30);
238 return null;
239 end getVacancyDetails;
240 --+
241 --+ getCommTopicDetailsFromTopicId
242 --+
243 function getCommunicationTopicDetails ( p_topicId in number
244 , p_messageId in number) return varchar2 as
245 cursor csrCommunicationTopicDetails (c_topicId in number
246 ,c_messageId in number) is
247 select 'IRC_COMM_TOPIC_SUBJECT:'
248 || ict.SUBJECT
249 || ';IRC_COMM_MSG_SENDER_TYPE:'
250 || icm.SENDER_TYPE
251 || ';IRC_COMM_MSG_SENDER_ID:'
252 || icm.SENDER_ID
253 || ';IRC_COMM_MSG_SUBJECT:'
254 || icm.MESSAGE_SUBJECT
255 || ';IRC_COMM_MSG_BODY:'
256 || substr(icm.MESSAGE_BODY,1,1000)
257 from IRC_COMM_MESSAGES icm
258 ,IRC_COMM_TOPICS ict
259 where ict.COMMUNICATION_TOPIC_ID = c_topicId
260 and icm.COMMUNICATION_TOPIC_ID = c_topicId
261 and icm.COMMUNICATION_MESSAGE_ID = c_messageId;
262
263 cursor csrMessageBodyContent (c_topicId in number
264 ,c_messageId in number) is
265 select length(icm.MESSAGE_BODY)
266 from IRC_COMM_MESSAGES icm
267 where icm.COMMUNICATION_TOPIC_ID = c_topicId
268 and icm.COMMUNICATION_MESSAGE_ID = c_messageId;
269
270 l_messageBodyContent number;
271 l_communicationTopicDetails varchar2(10000);
272 l_func varchar2(50) := 'getCommunicationTopicDetails';
273 l_new_line varchar2(10) := '<BR>';
274 begin
275 open csrCommunicationTopicDetails(p_topicId, p_messageId);
276 fetch csrCommunicationTopicDetails into l_communicationTopicDetails;
277 close csrCommunicationTopicDetails;
278
279 open csrMessageBodyContent(p_topicId, p_messageId);
280 fetch csrMessageBodyContent into l_messageBodyContent;
281 close csrMessageBodyContent;
282
283 if l_messageBodyContent > 1000 then
284 return l_communicationTopicDetails||'...'||l_new_line||fnd_message.get_string('PER','IRC_403036_COMM_LONG_MSG')||';';
285 else
286 return l_communicationTopicDetails||';';
287 end if;
288
289 exception
290 when no_data_found then
291 hr_utility.set_location('No Data Found Error : ' || g_package||'.'|| l_func ,10);
292 return null;
293 when others then
294 hr_utility.set_location('Error : ' || g_package||'.'|| l_func ,20);
295 hr_utility.set_location('Error Message: ' || sqlerrm, 30);
296 return null;
297 end getCommunicationTopicDetails;
298 --+
299 --+ getInterviewDetails
300 --+
301 function getInterviewDetails ( p_interviewId in number
302 , p_effectiveDate in date) return varchar2 as
303 cursor csrInterviewDetails ( c_interviewId in number
304 , c_effectiveDate in date) is
305 select 'IRC_INTVW_LOC_ID:'
306 || pe.location_id
307 || ';IRC_INTVW_LOC_DETAILS:'
308 || loc.description
309 || ';IRC_INTVW_LOC_CODE:'
310 || loc.location_code
311 || ';IRC_INTVW_DERIVED_LOCALE:'
312 || loc.derived_locale
313 || ';IRC_INTVW_DATE_START:'
314 || pe.date_start
315 || ';IRC_INTVW_DATE_END:'
316 || pe.date_end
317 || ';IRC_INTVW_TIME_START:'
318 || pe.time_start
319 || ';IRC_INTVW_TIME_END:'
320 || pe.time_end
321 || ';IRC_INTVW_INT_CONTACT:'
322 || ppf.full_name
323 || ';IRC_INTVW_INT_CONTACT_NUMBER:'
324 || pe.contact_telephone_number
325 || ';IRC_INTVW_EXT_CONTACT:'
326 || pe.external_contact
327 || ';IRC_INTVW_FEEDBACK:'
328 || iid.feedback
329 || ';IRC_INTVW_RESULT:'
330 || iid.result
331 || ';IRC_INTVW_NOTES:'
332 || iid.notes
333 || ';IRC_INTVW_CAND_NOTES:'
334 || iid.notes_to_candidate
335 || ';IRC_INTVW_CATEGORY:'
336 || hlk.meaning
337 || ';IRC_INTVW_TYPE:'
338 || hlk1.meaning
339 || ';IRC_INTVW_TIME_ZONE:'
340 || ft.name
341 || ';',
342 pe.location_id
343 from per_events pe
344 ,irc_interview_details iid
345 ,hr_locations_all loc
346 ,per_all_people_f ppf
347 ,hr_lookups hlk
348 ,hr_lookups hlk1
349 ,fnd_timezones_vl ft
350 where pe.event_id = c_interviewId
351 and iid.event_id = pe.event_id
352 and c_effectiveDate between iid.start_date and iid.end_date
353 and loc.location_id(+) = pe.location_id
354 and ppf.person_id(+) = pe.internal_contact_person_id
355 and (pe.internal_contact_person_id is null or trunc(c_effectiveDate)
356 between trunc(ppf.effective_start_date) and trunc(ppf.effective_end_date))
357 and hlk.lookup_code(+) = iid.category
358 and (iid.category is null or hlk.lookup_type = 'IRC_INTERVIEW_CATEGORY')
359 and hlk1.lookup_code(+) = pe.type
360 and (pe.type is null or hlk1.lookup_type = 'IRC_INTERVIEW_TYPE')
361 and ft.TIMEZONE_CODE(+) = loc.TIMEZONE_CODE;
362 l_interviewDetails varchar2(10000);
363 l_func varchar2(50) := 'getInterviewDetails';
364 l_flexfield fnd_dflex.dflex_r;
365 l_flexinfo fnd_dflex.dflex_dr;
366 p_appl_short_name varchar2(10) := 'PER';
367 p_flexfield_name varchar2(50) :='Address Location';
368 segments fnd_dflex.segments_dr;
369 l_location_id hr_locations_all.location_id%Type;
370 loc_rec hr_locations_all%rowtype;
371 v_segment_name varchar2(100);
372 v_message varchar2(1000);
373 v_message1 varchar2(1000);
374 v_segment_name1 varchar2(100);
375 v_segment_val varchar2(1000);
376 l_sqlbuf varchar2(2000);
377 l_table_name varchar2(100);
378 item_cursor SYS_REFCURSOR;
379 v_no_of_segments number;
380
381 begin
382 open csrInterviewDetails(p_interviewId, p_effectiveDate);
383 fetch csrInterviewDetails into l_interviewDetails,l_location_id;
384 close csrInterviewDetails;
385
386 select * into loc_rec from hr_locations_all
387 where location_id=l_location_id;
388 fnd_dflex.get_flexfield(appl_short_name => p_appl_short_name,
389 flexfield_name => p_flexfield_name,
390 flexfield => l_flexfield,
391 flexinfo => l_flexinfo);
392 l_table_name := l_flexinfo.table_name;
393 fnd_dflex.get_segments(fnd_dflex.make_context(flexfield => l_flexfield,
394 context_code => loc_rec.style),
395 segments,
396 TRUE);
397
398 fnd_message.set_name('PER','IRC_412699_INTVLOC_ADDR_BODY');
399 FOR i IN 1 .. segments.nsegments LOOP
400 v_segment_name1 := segments.segment_name(i);
401 v_segment_name := segments.application_column_name(i);
402 l_sqlbuf := 'select '||v_segment_name|| ' from ' || l_table_name||' where location_id= '||l_location_id;
403 OPEN item_cursor FOR l_sqlbuf ;
404 fetch item_cursor into v_segment_val;
405 if v_segment_val is not null then
406 fnd_message.set_token('TOKEN'|| i,null || v_segment_val || ',',false);
407 else
408 fnd_message.set_token('TOKEN'||i,null,false);
409 end if;
410 close item_cursor;
411 IF i = segments.nsegments then
412 exit;
413 end if;
414 END LOOP;
415
416 v_no_of_segments := segments.nsegments ;
417 if v_no_of_segments < 22 then
418 for i in v_no_of_segments+1 .. 22 loop
419 fnd_message.set_token('TOKEN'||i,null,false);
420 end loop;
421 end if;
422 v_message := fnd_message.get;
423 v_message1 := replace(v_message,' <BR>',null);
424 v_message := substr(trim(v_message1),0,length(trim(v_message1))-1);
425 v_message1 := replace(v_message,' ',null);
426 return l_interviewDetails||'IRC_INTVW_LOC_ADDRESS:'|| v_message1||';';
427
428 exception
429 when no_data_found then
430 hr_utility.set_location('No Data Found Error : ' || g_package||'.'|| l_func ,10);
431 return null;
432 when others then
433 hr_utility.set_location('Error : ' || g_package||'.'|| l_func ,20);
434 hr_utility.set_location('Error Message: ' || sqlerrm, 30);
435 return null;
436 end getInterviewDetails;
437 --+
438 --+ getPersonDetails
439 --+
440 function getPersonDetails ( p_personId in number
441 , p_role in varchar2
442 , p_effectiveDate in date) return varchar2 as
443 cursor csrPersonDetails(c_personId in number
444 ,c_role in varchar2
445 ,c_effectiveDate in date) is
446 select 'IRC_'
447 || c_role
448 || '_HZP_ID:'
449 || per.party_id
450 || ';IRC_'
451 || c_role
452 || '_FIRST_NAME:'
453 || per.first_name
454 || ';IRC_'
455 || c_role
456 || '_LAST_NAME:'
457 || per.last_name
458 || ';IRC_'
459 || c_role
460 || '_FULL_NAME:'
461 || per.full_name
462 || ';IRC_'
463 || c_role
464 || '_EMAIL_ID:'
465 || per.email_address
466 || ';'
467 from per_all_people_f per
468 where per.person_id = c_personId
469 and trunc(c_effectiveDate)
470 between trunc(per.effective_start_date) and trunc(per.effective_end_date);
471 l_personDetails varchar2(1000);
472 l_func varchar2(50) := 'getPersonDetails';
473 begin
474 open csrPersonDetails(p_personId, p_role, p_effectiveDate);
475 fetch csrPersonDetails into l_personDetails;
476 close csrPersonDetails;
477 return l_personDetails;
478 exception
479 when no_data_found then
480 hr_utility.set_location('No Data Found Error : ' || g_package||'.'|| l_func ,10);
481 return null;
482 when others then
483 hr_utility.set_location('Error : ' || g_package||'.'|| l_func ,20);
484 hr_utility.set_location('Error Message: ' || sqlerrm, 30);
485 return null;
486 end getPersonDetails;
487
488 --+
489 --+ getApplicationExtStatus
490 --+
491 function getApplicationExtStatus ( p_assignmentStatusCode in number) return varchar2 is
492 cursor csrApplicationExternalStatus (c_assignmentStatusCode in number) is
493 select 'IRC_JOB_APPL_NEW_STATUS:'
494 || per_system_status
495 || ';IRC_APPL_NEW_EXTERNAL_STATUS:'
496 || external_status
497 || ';'
498 from PER_ASSIGNMENT_STATUS_TYPES_V
499 where ASSIGNMENT_STATUS_TYPE_ID = c_assignmentStatusCode;
500 l_applicationExtStatus varchar2(500);
501 l_func_name varchar2(50) := 'getApplicationExtStatus';
502 begin
503 open csrApplicationExternalStatus(p_assignmentStatusCode);
504 fetch csrApplicationExternalStatus into l_applicationExtStatus;
505 close csrApplicationExternalStatus;
506 return l_applicationExtStatus;
507 exception
508 when others then
509 hr_utility.set_location('Error : ' || g_package||'.'|| l_func_name ,20);
510 hr_utility.set_location('Error Message: ' || sqlerrm, 30);
511 if csrApplicationExternalStatus%ISOPEN then
512 close csrApplicationExternalStatus;
513 end if;
514 return null;
515 end getApplicationExtStatus;
516
517 --+
518 --+ getApplicationStatus
519 --+
520 function getApplicationStatus ( p_assignmentStatusCode in number) return varchar2 is
521 cursor csrApplicationStatus (c_assignmentStatusCode in number) is
522 select 'IRC_JOB_APPL_NEW_STATUS:'
523 || per_system_status
524 || ';IRC_JOB_APPL_NEW_USER_STATUS:'
525 || user_status
526 || ';'
527 from PER_ASSIGNMENT_STATUS_TYPES
528 where ASSIGNMENT_STATUS_TYPE_ID = c_assignmentStatusCode;
529 l_applicationStatus varchar2(500);
530 l_func varchar2(50) := 'getApplicationStatus';
531 begin
532 open csrApplicationStatus(p_assignmentStatusCode);
533 fetch csrApplicationStatus into l_applicationStatus;
534 close csrApplicationStatus;
535 return l_applicationStatus;
536 exception
537 when no_data_found then
538 hr_utility.set_location('No Data Found Error : ' || g_package||'.'|| l_func ,10);
539 return null;
540 when others then
541 hr_utility.set_location('Error : ' || g_package||'.'|| l_func ,20);
542 hr_utility.set_location('Error Message: ' || sqlerrm, 30);
543 return null;
544 end getApplicationStatus;
545 --+
546 --+ getInterviewStatusMeaning
547 --+
548 function getInterviewStatusMeaning (p_interviewStatusCode in varchar2
549 ,p_attributeName in varchar2) return varchar2 is
550 cursor csrInterviewStatusMeaning (c_interviewStatusCode in varchar2
551 ,c_attributeName in varchar2) is
552 select c_attributeName
553 || ':'
554 || meaning
555 || ';'
556 from HR_LOOKUPS
557 where LOOKUP_TYPE = 'IRC_INTERVIEW_STATUS'
558 and LOOKUP_CODE = c_interviewStatusCode;
559 l_interviewStatusMeaning varchar2(500);
560 l_func varchar2(50) := 'getInterviewStatusMeaning';
561 begin
562 open csrInterviewStatusMeaning(p_interviewStatusCode, p_attributeName);
563 fetch csrInterviewStatusMeaning into l_interviewStatusMeaning;
564 close csrInterviewStatusMeaning;
565 return l_interviewStatusMeaning;
566 exception
567 when no_data_found then
568 hr_utility.set_location('No Data Found Error : ' || g_package||'.'|| l_func ,10);
569 return null;
570 when others then
571 hr_utility.set_location('Error : ' || g_package||'.'|| l_func ,20);
572 hr_utility.set_location('Error Message: ' || sqlerrm, 30);
573 return null;
574 end getInterviewStatusMeaning;
575
576 --+
577 --+ getApplicationOldExtStatus
578 --+
579 function getApplicationOldExtStatus ( p_assignmentOldStatusCode in number) return varchar2 is
580 cursor csrApplicationOldExtStatus (c_assignmentOldStatusCode in number) is
581 select 'IRC_JOB_APPL_OLD_STATUS:'
582 || external_status
583 || ';'
584 from PER_ASSIGNMENT_STATUS_TYPES_V
585 where ASSIGNMENT_STATUS_TYPE_ID = c_assignmentOldStatusCode;
586 l_applicationOldExtStatus varchar2(500);
587 l_func_name varchar2(50) := 'getApplicationOldExtStatus';
588 begin
589 open csrApplicationOldExtStatus(p_assignmentOldStatusCode);
590 fetch csrApplicationOldExtStatus into l_applicationOldExtStatus;
591 close csrApplicationOldExtStatus;
592 return l_applicationOldExtStatus;
593 exception
594 when others then
595 hr_utility.set_location('Error : ' || g_package||'.'|| l_func_name ,20);
596 hr_utility.set_location('Error Message: ' || sqlerrm, 30);
597 if csrApplicationOldExtStatus%ISOPEN then
598 close csrApplicationOldExtStatus;
599 end if;
600 return null;
601
602 end getApplicationOldExtStatus;
603 --+
604 --+ getInterviersNamesHTML
605 --+
606 function getInterviersNamesHTML( p_interviewId in number
607 , p_effectiveDate in date)
608 return varchar2 is
609 cursor csrInterviewersNames(c_interviewId number
610 , c_effectiveDate date) is
611 select '<LI>'
612 || ppf.full_name
613 || '</LI>' interviewer
614 from per_all_people_f ppf
615 , per_bookings pb
616 where ppf.person_id = pb.person_id
617 and pb.event_id = c_interviewId
618 and trunc(c_effectiveDate) between trunc(ppf.effective_start_date) and trunc(ppf.effective_end_date);
619 l_interviewersName varchar2(5000) := null;
620 l_func varchar2(50) := 'getInterviersNamesHTML';
621 begin
622 for l_temp in csrInterviewersNames(p_interviewId, p_effectiveDate) loop
623 l_interviewersName := l_interviewersName || l_temp.interviewer;
624 end loop;
625 return 'IRC_INTERVIEWER_NAMES:<UL>' || l_interviewersName || '</UL>';
626 exception
627 when no_data_found then
628 hr_utility.set_location('No Data Found Error : ' || g_package||'.'|| l_func ,10);
629 return null;
630 when others then
631 hr_utility.set_location('Error : ' || g_package||'.'|| l_func ,20);
632 hr_utility.set_location('Error Message: ' || sqlerrm, 30);
633 return null;
634 end getInterviersNamesHTML;
635 --+
636 --
637 end IRC_NOTIFICATION_DATA_PKG;