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_DATA_PKG

Source


1 PACKAGE BODY IRC_NOTIFICATION_DATA_PKG as
2   /* $Header: irntfdat.pkb 120.10.12010000.2 2008/11/14 14:59:09 kvenukop 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) return number as
61     cursor csrCandidateId(c_assignmentId in number
62                          ,c_effectiveDate in date) is
63       select person_id
64       from per_all_assignments_f
65       where assignment_id = c_assignmentId
66        and trunc(c_effectiveDate)
67              between trunc(effective_start_date) and trunc(effective_end_date);
68     l_candidateId number;
69     l_func varchar2(50) := 'getCandidatePersonId';
70     begin
71       open csrCandidateId(p_assignmentId, p_effectiveDate);
72       fetch csrCandidateId into l_candidateId;
73       close csrCandidateId;
74       return l_candidateId;
75     exception
76       when no_data_found then
77         hr_utility.set_location('No Data Found Error : ' || g_package||'.'|| l_func ,10);
78         return null;
79       when others then
80         hr_utility.set_location('Error : ' || g_package||'.'|| l_func ,20);
81         hr_utility.set_location('Error Message: ' || sqlerrm, 30);
82         return null;
83   end getCandidatePersonId;
84 --+
85 --+
86 --+
87   function getCandidateAgencyId(p_candidateId    in number
88                                 ,p_effectiveDate in date)
89                                return varchar2 is
90     cursor csrCandidateAgencyId (c_candidateId    in number
91                                 ,c_effectiveDate in date) is
92       select 'IRC_CAND_AGENCY_ID:'
93              || inp.agency_id
94              || ';'
95       from irc_notification_preferences inp
96            ,per_all_people_f ppf
97       where inp.person_id = c_candidateId
98             and ppf.person_id = c_candidateId
99             and c_effectiveDate between ppf.effective_start_date
100                 and ppf.effective_end_date;
101     l_candidateAgencyId varchar2(100);
102     l_func varchar2(50) := 'getCandidateAgencyId';
103     begin
104       open csrCandidateAgencyId(p_candidateId, p_effectiveDate);
105       fetch csrCandidateAgencyId into l_candidateAgencyId;
106       close csrCandidateAgencyId;
107       return l_candidateAgencyId;
108     exception
109       when no_data_found then
110         hr_utility.set_location('No Data Found Error : ' || g_package||'.'|| l_func ,10);
111         return null;
112       when others then
113         hr_utility.set_location('Error : ' || g_package||'.'|| l_func ,20);
114         hr_utility.set_location('Error Message: ' || sqlerrm, 30);
115         return null;
116   end getCandidateAgencyId;
117 --+
118 --+ getManagerPersonId
119 --+
120   function getManagerPersonId ( p_vacancyId     in number
121                               , p_effectiveDate in date) return number as
122     cursor csrManagerId(c_vacancyId in number
123                        ,c_effectiveDate in date) is
124       select manager_id
125       from per_all_vacancies
126       where vacancy_id = c_vacancyId
127         and trunc(c_effectiveDate)
128              between trunc(date_from) and nvl(trunc(date_to),to_date('31-12-4712','DD-MM-RRRR'));
129      l_managerId number;
130      l_func varchar2(50) := 'getManagerPersonId';
131      begin
132        open csrManagerId(p_vacancyId, p_effectiveDate);
133        fetch csrManagerId into l_managerId;
134        close csrManagerId;
135        return l_managerId;
136     exception
137       when no_data_found then
138         hr_utility.set_location('No Data Found Error : ' || g_package||'.'|| l_func ,10);
139         return null;
140       when others then
141         hr_utility.set_location('Error : ' || g_package||'.'|| l_func ,20);
142         hr_utility.set_location('Error Message: ' || sqlerrm, 30);
143         return null;
144   end getManagerPersonId;
145 --+
146 --+ getRecruiterPersonId
147 --+
148   function getRecruiterPersonId ( p_assignmentId     in number
149                                 , p_effectiveDate in date) return number as
150     cursor csrRecruiterId(c_assignmentId in number
151                          ,c_effectiveDate in date) is
152       select recruiter_id
153       from per_all_assignments_f
154       where assignment_id = c_assignmentId
155         and trunc(c_effectiveDate)
156              between trunc(effective_start_date) and trunc(effective_end_date);
157     l_recruiterId number;
158     l_func varchar2(50) := 'getRecruiterPersonId';
159     begin
160       open csrRecruiterId(p_assignmentId, p_effectiveDate);
161       fetch csrRecruiterId into l_recruiterId;
162       close csrRecruiterId;
163       return l_recruiterId;
164     exception
165       when no_data_found then
166         hr_utility.set_location('No Data Found Error : ' || g_package||'.'|| l_func ,10);
167         return null;
168       when others then
169         hr_utility.set_location('Error : ' || g_package||'.'|| l_func ,20);
170         hr_utility.set_location('Error Message: ' || sqlerrm, 30);
171         return null;
172   end getRecruiterPersonId;
173 --+
174 --+ getVacancyDetails
175 --+
176   function getVacancyDetails ( p_vacancyId     in number
177                              , p_effectiveDate in date) return varchar2 as
178     cursor csrVacancyDetails(c_vacancyId in number
179                             ,c_effectiveDate in date) is
180       select 'IRC_VACANCY_NAME:'
181              || pav.name
182              || ';IRC_VAC_BG_ID:'
183              || pav.business_group_id
184              || ';IRC_VAC_CATEGORY:'
185              || pav.vacancy_category
186              || ';IRC_VAC_JOB_TITLE:'
187              || pj.name
188              || ';IRC_VAC_POSITION_TITLE:'
189              || pp.name
190              || ';IRC_POSTING_ID:'
191              || pav.primary_posting_id
192              || ';IRC_JOB_POSTING_TITLE:'
193              || ipc.name
194              || ';IRC_RECRUITING_SITE_ID:'
195              || pra.recruiting_site_id
196              ||';'
197       from per_all_vacancies pav,
198            per_jobs pj,
199            hr_all_positions_f pp,
200            per_recruitment_activities pra,
201            irc_posting_contents_vl ipc
202       where vacancy_id = c_vacancyId
203         and pav.job_id = pj.job_id(+)
204         and pav.position_id = pp.position_id(+)
205         and trunc(c_effectiveDate) between trunc(pav.date_from)
206           and nvl(trunc(pav.date_to),to_date('31-12-4712','DD-MM-RRRR'))
207         and trunc(c_effectiveDate)
208              between trunc(pp.effective_start_date(+)) and trunc(pp.effective_end_date(+))
209         and pra.posting_content_id(+) = pav.primary_posting_id
210         and ipc.posting_content_id(+) = pav.primary_posting_id
211         and rownum = 1;
212     l_vacancyDetails varchar2(500);
213     l_func varchar2(50) := 'getVacancyDetails';
214     begin
215       open csrVacancyDetails(p_vacancyId, p_effectiveDate);
216       fetch csrVacancyDetails into l_vacancyDetails;
217       close csrVacancyDetails;
218       return l_vacancyDetails;
219     exception
220       when no_data_found then
221         hr_utility.set_location('No Data Found Error : ' || g_package||'.'|| l_func ,10);
222         return null;
223       when others then
224         hr_utility.set_location('Error : ' || g_package||'.'|| l_func ,20);
225         hr_utility.set_location('Error Message: ' || sqlerrm, 30);
226         return null;
227   end getVacancyDetails;
228 --+
229 --+ getCommTopicDetailsFromTopicId
230 --+
231   function getCommunicationTopicDetails ( p_topicId   in number
232                                         , p_messageId in number) return varchar2 as
233     cursor csrCommunicationTopicDetails (c_topicId in number
234                                         ,c_messageId in number) is
235       select 'IRC_COMM_TOPIC_SUBJECT:'
236              || ict.SUBJECT
237              || ';IRC_COMM_MSG_SENDER_TYPE:'
238              || icm.SENDER_TYPE
239              || ';IRC_COMM_MSG_SENDER_ID:'
240              || icm.SENDER_ID
241              || ';IRC_COMM_MSG_SUBJECT:'
242              || icm.MESSAGE_SUBJECT
243              || ';IRC_COMM_MSG_BODY:'
244              || icm.MESSAGE_BODY
245              || ';'
246       from  IRC_COMM_MESSAGES icm
247            ,IRC_COMM_TOPICS ict
248       where ict.COMMUNICATION_TOPIC_ID   = c_topicId
249         and icm.COMMUNICATION_TOPIC_ID   = c_topicId
250         and icm.COMMUNICATION_MESSAGE_ID = c_messageId;
251      l_communicationTopicDetails varchar2(10000);
252      l_func varchar2(50) := 'getCommunicationTopicDetails';
253     begin
254       open csrCommunicationTopicDetails(p_topicId, p_messageId);
255       fetch csrCommunicationTopicDetails into l_communicationTopicDetails;
256       close csrCommunicationTopicDetails;
257       return l_communicationTopicDetails;
258     exception
259       when no_data_found then
260         hr_utility.set_location('No Data Found Error : ' || g_package||'.'|| l_func ,10);
261         return null;
262       when others then
263         hr_utility.set_location('Error : ' || g_package||'.'|| l_func ,20);
264         hr_utility.set_location('Error Message: ' || sqlerrm, 30);
265         return null;
266   end getCommunicationTopicDetails;
267 --+
268 --+ getInterviewDetails
269 --+
270   function getInterviewDetails ( p_interviewId   in number
271                                , p_effectiveDate in date) return varchar2 as
272     cursor csrInterviewDetails ( c_interviewId in number
273                                , c_effectiveDate in date) is
274       select 'IRC_INTVW_LOC_ID:'
275              || pe.location_id
276              || ';IRC_INTVW_LOC_DETAILS:'
277              || loc.description
278              || ';IRC_INTVW_LOC_CODE:'
279              || loc.location_code
280              || ';IRC_INTVW_DERIVED_LOCALE:'
281              || loc.derived_locale
282              || ';IRC_INTVW_DATE_START:'
283              || pe.date_start
284              || ';IRC_INTVW_DATE_END:'
285              || pe.date_end
286              || ';IRC_INTVW_TIME_START:'
287              || pe.time_start
288              || ';IRC_INTVW_TIME_END:'
289              || pe.time_end
290              || ';IRC_INTVW_INT_CONTACT:'
291              || ppf.full_name
292              || ';IRC_INTVW_INT_CONTACT_NUMBER:'
293              || pe.contact_telephone_number
294              || ';IRC_INTVW_EXT_CONTACT:'
295              || pe.external_contact
296              || ';IRC_INTVW_FEEDBACK:'
297              || iid.feedback
298              || ';IRC_INTVW_RESULT:'
299              || iid.result
300              || ';IRC_INTVW_NOTES:'
301              || iid.notes
302              || ';IRC_INTVW_CAND_NOTES:'
303              || iid.notes_to_candidate
304              || ';IRC_INTVW_CATEGORY:'
305              || hlk.meaning
306              || ';IRC_INTVW_TYPE:'
307              || hlk1.meaning
308              || ';IRC_INTVW_TIME_ZONE:'
309              || ft.name
310              || ';'
311       from  per_events pe
312            ,irc_interview_details iid
313            ,hr_locations_all loc
314            ,per_all_people_f ppf
315            ,hr_lookups hlk
316            ,hr_lookups hlk1
317            ,fnd_timezones_vl ft
318       where pe.event_id = c_interviewId
319         and iid.event_id = pe.event_id
320         and c_effectiveDate between iid.start_date and iid.end_date
321         and loc.location_id(+) = pe.location_id
322         and ppf.person_id(+) = pe.internal_contact_person_id
323         and (pe.internal_contact_person_id is null or trunc(c_effectiveDate)
324              between trunc(ppf.effective_start_date) and trunc(ppf.effective_end_date))
325         and hlk.lookup_code(+) = iid.category
326         and (iid.category is null or hlk.lookup_type = 'IRC_INTERVIEW_CATEGORY')
327         and hlk1.lookup_code(+) = pe.type
328         and (pe.type is null or hlk1.lookup_type = 'IRC_INTERVIEW_TYPE')
329         and ft.TIMEZONE_CODE(+) =  loc.TIMEZONE_CODE;
330       l_interviewDetails varchar2(10000);
331       l_func varchar2(50) := 'getInterviewDetails';
332     begin
333       open csrInterviewDetails(p_interviewId, p_effectiveDate);
334       fetch csrInterviewDetails into l_interviewDetails;
335       close csrInterviewDetails;
336       return l_interviewDetails;
337     exception
338       when no_data_found then
339         hr_utility.set_location('No Data Found Error : ' || g_package||'.'|| l_func ,10);
340         return null;
341       when others then
342         hr_utility.set_location('Error : ' || g_package||'.'|| l_func ,20);
343         hr_utility.set_location('Error Message: ' || sqlerrm, 30);
344         return null;
345   end  getInterviewDetails;
346 --+
347 --+ getPersonDetails
348 --+
349   function getPersonDetails ( p_personId      in number
350                             , p_role          in varchar2
351                             , p_effectiveDate in date) return varchar2 as
352     cursor csrPersonDetails(c_personId in number
353                            ,c_role in varchar2
354                            ,c_effectiveDate in date) is
355       select 'IRC_'
356              || c_role
357              || '_HZP_ID:'
358              || per.party_id
362              || per.first_name
359              || ';IRC_'
360              || c_role
361              || '_FIRST_NAME:'
363              || ';IRC_'
364              || c_role
365              || '_LAST_NAME:'
366              || per.last_name
367              || ';IRC_'
368              || c_role
369              || '_FULL_NAME:'
370              || per.full_name
371              || ';IRC_'
372              || c_role
373              || '_EMAIL_ID:'
374              || per.email_address
375              || ';'
376       from  per_all_people_f per
377       where per.person_id = c_personId
378         and trunc(c_effectiveDate)
379              between trunc(per.effective_start_date) and trunc(per.effective_end_date);
380     l_personDetails varchar2(1000);
381     l_func varchar2(50) := 'getPersonDetails';
382     begin
383       open csrPersonDetails(p_personId, p_role, p_effectiveDate);
384       fetch csrPersonDetails into l_personDetails;
385       close csrPersonDetails;
386       return l_personDetails;
387     exception
388       when no_data_found then
389         hr_utility.set_location('No Data Found Error : ' || g_package||'.'|| l_func ,10);
390         return null;
391       when others then
392         hr_utility.set_location('Error : ' || g_package||'.'|| l_func ,20);
393         hr_utility.set_location('Error Message: ' || sqlerrm, 30);
394         return null;
395   end getPersonDetails;
396 --+
397 --+ getApplicationStatus
398 --+
399 function getApplicationStatus ( p_assignmentStatusCode in number) return varchar2 is
400     cursor csrApplicationStatus (c_assignmentStatusCode in number) is
401       select 'IRC_JOB_APPL_NEW_STATUS:'
402              || per_system_status
403              || ';IRC_JOB_APPL_NEW_USER_STATUS:'
404              || user_status
405              || ';'
406       from PER_ASSIGNMENT_STATUS_TYPES
407       where ASSIGNMENT_STATUS_TYPE_ID = c_assignmentStatusCode;
408     l_applicationStatus varchar2(500);
409     l_func varchar2(50) := 'getApplicationStatus';
410     begin
411       open csrApplicationStatus(p_assignmentStatusCode);
412       fetch csrApplicationStatus into l_applicationStatus;
413       close csrApplicationStatus;
414       return l_applicationStatus;
415     exception
416       when no_data_found then
417         hr_utility.set_location('No Data Found Error : ' || g_package||'.'|| l_func ,10);
418         return null;
419       when others then
420         hr_utility.set_location('Error : ' || g_package||'.'|| l_func ,20);
421         hr_utility.set_location('Error Message: ' || sqlerrm, 30);
422       return null;
423 end getApplicationStatus;
424 --+
425 --+ getInterviewStatusMeaning
426 --+
427   function getInterviewStatusMeaning (p_interviewStatusCode in varchar2
428                                      ,p_attributeName       in varchar2) return varchar2 is
429     cursor csrInterviewStatusMeaning (c_interviewStatusCode in varchar2
430                                      ,c_attributeName       in varchar2) is
431       select c_attributeName
432              || ':'
433              || meaning
434              || ';'
435       from HR_LOOKUPS
436      where LOOKUP_TYPE = 'IRC_INTERVIEW_STATUS'
437        and LOOKUP_CODE = c_interviewStatusCode;
438     l_interviewStatusMeaning varchar2(500);
439     l_func varchar2(50) := 'getInterviewStatusMeaning';
440     begin
441       open csrInterviewStatusMeaning(p_interviewStatusCode, p_attributeName);
442       fetch csrInterviewStatusMeaning into l_interviewStatusMeaning;
443       close csrInterviewStatusMeaning;
444       return l_interviewStatusMeaning;
445     exception
446       when no_data_found then
447         hr_utility.set_location('No Data Found Error : ' || g_package||'.'|| l_func ,10);
448         return null;
449       when others then
450         hr_utility.set_location('Error : ' || g_package||'.'|| l_func ,20);
451         hr_utility.set_location('Error Message: ' || sqlerrm, 30);
452       return null;
453   end getInterviewStatusMeaning;
454 --+
455 --+ getInterviersNamesHTML
456 --+
457   function getInterviersNamesHTML( p_interviewId  in number
458                                 , p_effectiveDate in date)
459            return varchar2 is
460   cursor csrInterviewersNames(c_interviewId number
461                              , c_effectiveDate date) is
462     select '<LI>'
463            || ppf.full_name
464            || '</LI>' interviewer
465     from per_all_people_f ppf
466          , per_bookings pb
467     where ppf.person_id = pb.person_id
468       and pb.event_id = c_interviewId
469       and trunc(c_effectiveDate) between trunc(ppf.effective_start_date) and trunc(ppf.effective_end_date);
470     l_interviewersName varchar2(5000) := null;
471     l_func varchar2(50) := 'getInterviersNamesHTML';
472     begin
473       for l_temp in csrInterviewersNames(p_interviewId, p_effectiveDate) loop
474         l_interviewersName := l_interviewersName || l_temp.interviewer;
475       end loop;
476       return 'IRC_INTERVIEWER_NAMES:<UL>' || l_interviewersName || '</UL>';
477     exception
478       when no_data_found then
479         hr_utility.set_location('No Data Found Error : ' || g_package||'.'|| l_func ,10);
480         return null;
481       when others then
482         hr_utility.set_location('Error : ' || g_package||'.'|| l_func ,20);
483         hr_utility.set_location('Error Message: ' || sqlerrm, 30);
484         return null;
485     end getInterviersNamesHTML;
486 --+
487 end IRC_NOTIFICATION_DATA_PKG;