1 PACKAGE BODY IRC_UTILITIES_PKG AS
2 /* $Header: irutil.pkb 120.59.12020000.5 2013/02/20 05:40:26 pshreera ship $ */
3 g_qual_party_id number;
4 g_qual_type varchar2(150);
5 g_emp_upt_party_id number;
6 g_emp_upt varchar2(80);
7 g_apl_upt_party_id number;
8 g_apl_upt varchar2(80);
9 g_prev_emp_party_id number;
10 g_prev_emp varchar2(240);
11
12 g_open_party_id number;
13 g_open_party varchar2(5);
14
15 g_rec_person_id_in number;
16 g_rec_person_id_out number;
17
18 g_internal_person_id number;
19 g_internal_person varchar2(5);
20 g_internal_email_address varchar2(240);
21 g_internal_email varchar2(5);
22 g_internal_user_name varchar2(100);
23 g_internal_user varchar2(5);
24
25 -- ----------------------------------------------------------------------------
26 -- |---------------------------< SET_SAVEPOINT >-----------------------------|
27 -- ----------------------------------------------------------------------------
28
29 procedure SET_SAVEPOINT IS
30
31 BEGIN
32
33 savepoint IRC_SAVEPOINT;
34
35 END SET_SAVEPOINT;
36
37 -- ----------------------------------------------------------------------------
38 -- |-----------------------< ROLLBACK_TO_SAVEPOINT >-------------------------|
39 -- ----------------------------------------------------------------------------
40
41 procedure ROLLBACK_TO_SAVEPOINT IS
42
43 BEGIN
44
45 rollback to savepoint IRC_SAVEPOINT;
46
47 END ROLLBACK_TO_SAVEPOINT;
48
49 -- -------------------------------------------------------------------
50 -- |--------------------< get_home_page_function >-------------------|
51 -- -------------------------------------------------------------------
52 procedure GET_HOME_PAGE_FUNCTION(p_responsibility_id in varchar2
53 ,p_function out nocopy varchar2) is
54 --
55 cursor csr_get_function_id is
56 select fe.function_id
57 from fnd_menu_entries fe
58 where fe.function_id is not null
59 start with fe.menu_id=
60 (select resp.menu_id from fnd_responsibility resp
61 where resp.responsibility_id=p_responsibility_id
62 and resp.application_id=800)
63 connect by prior fe.sub_menu_id= fe.menu_id
64 and fe.grant_flag='Y'
65 order by level,fe.entry_sequence;
66 --
67 cursor csr_get_function_info(p_function_id number) is
68 select fff.function_name
69 from fnd_form_functions fff
70 where fff.function_id=p_function_id;
71 --
72 l_function_id fnd_menu_entries.function_id%type;
73 l_function_name fnd_form_functions.function_name%type;
74 --
75 begin
76 open csr_get_function_id;
77 fetch csr_get_function_id into l_function_id;
78 if csr_get_function_id%notfound then
79 close csr_get_function_id;
80 else
81 close csr_get_function_id;
82 open csr_get_function_info(l_function_id);
83 fetch csr_get_function_info into l_function_name;
84 close csr_get_function_info;
85 end if;
86 --
87 p_function:=l_function_name;
88
89 END GET_HOME_PAGE_FUNCTION;
90
91 function removeTags(p_in varchar2) return varchar2 is
92 l_retval varchar2(32767);
93 begin
94 l_retval:=replace(p_in,'&','&'||'amp;');
95 l_retval:=replace(l_retval,'<','&'||'lt;');
96 return l_retval;
97 end removeTags;
98
99 function removeTags(p_in clob) return varchar2 is
100 l_v_retval varchar2(32767);
101 begin
102 l_v_retval:=dbms_lob.substr(p_in);
103 l_v_retval:=removeTags(l_v_retval);
104 return l_v_retval;
105 end removeTags;
106
107
108
109 -- ----------------------------------------------------------------------------
110 -- |-----------------------< GET_CURRENT_EMPLOYER >--------------------------|
111 -- ----------------------------------------------------------------------------
112
113 FUNCTION GET_CURRENT_EMPLOYER (p_person_id per_all_people_f.person_id%TYPE,
114 p_eff_date date )
115 RETURN VARCHAR2
116
117 IS
118
119 l_employer PER_PREVIOUS_EMPLOYERS.EMPLOYER_NAME%TYPE;
120
121
122 CURSOR c_current_employers(p_person_id PER_PREVIOUS_EMPLOYERS.PERSON_ID%TYPE,
123 p_date PER_PREVIOUS_EMPLOYERS.END_DATE%TYPE) IS
124 SELECT empl.EMPLOYER_NAME
125 FROM PER_PREVIOUS_EMPLOYERS empl,
126 PER_ALL_PEOPLE_F ppf,
127 PER_ALL_PEOPLE_F ppf2
128 WHERE ppf.person_id = p_person_id
129 AND ppf.party_id = ppf2.party_id
130 AND p_date BETWEEN ppf.effective_start_date and ppf.effective_end_date
131 AND p_date BETWEEN ppf2.effective_start_date and ppf2.effective_end_date
132 AND ppf2.person_id = empl.person_id
133 AND p_date BETWEEN nvl(empl.START_DATE,trunc(SYSDATE))
134 and(nvl(empl.END_DATE,hr_api.g_eot));
135
136 BEGIN
137 -- MAIN FUNCTION LOGIC
138
139 OPEN c_current_employers(p_person_id,p_eff_date);
140 --Get an instance of current employer (not concerned if multiple)
141 FETCH c_current_employers INTO l_employer;
142 if c_current_employers%notfound then
143 l_employer:='';
144 end if;
145 CLOSE c_current_employers;
146 RETURN (l_employer);
147
148 END GET_CURRENT_EMPLOYER;
149
150
151 -- ----------------------------------------------------------------------------
152 -- |---------------------< GET_CURRENT_EMPLOYER_PTY >------------------------|
153 -- ----------------------------------------------------------------------------
154 FUNCTION GET_CURRENT_EMPLOYER_PTY(p_party_id number,
155 p_eff_date date)
156 RETURN VARCHAR2
157
158 IS
159 CURSOR c_current_employers IS
160 SELECT ppe.employer_name
161 FROM per_all_people_f ppf
162 ,per_previous_employers ppe
163 WHERE ppf.party_id = p_party_id
164 AND p_eff_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date
165 AND p_eff_date BETWEEN ppe.start_date AND NVL(ppe.end_date,HR_GENERAL.end_of_time)
166 AND ppe.person_id = ppf.person_id
167 ORDER BY NVL(ppe.end_date,HR_GENERAL.end_of_time) DESC
168 ,NVL(ppe.start_date,HR_GENERAL.start_of_time) DESC;
169 BEGIN
170 -- MAIN FUNCTION LOGIC
171 if(nvl(g_prev_emp_party_id,-1)<>p_party_id) then
172 g_prev_emp_party_id:=p_party_id;
173 OPEN c_current_employers;
174 --Get an instance of current employer (not concerned if multiple)
175 FETCH c_current_employers INTO g_prev_emp;
176 if c_current_employers%notfound then
177 g_prev_emp:='';
178 end if;
179 CLOSE c_current_employers;
180 end if;
181 RETURN (g_prev_emp);
182
183 END GET_CURRENT_EMPLOYER_PTY;
184
185
186 -- ----------------------------------------------------------------------------
187 -- |-------------------------< GET_MAX_QUAL_TYPE >---------------------------|
188 -- ----------------------------------------------------------------------------
189 FUNCTION GET_MAX_QUAL_TYPE (p_person_id per_all_people_f.person_id%TYPE)
190 RETURN VARCHAR2
191
192 IS
193
194 l_qual_type PER_QUALIFICATION_TYPES.NAME%TYPE;
195 l_qual_type2 PER_QUALIFICATION_TYPES.NAME%TYPE;
196 l_max_rank number;
197
198 CURSOR c_qual_types(p_person PER_ALL_PEOPLE_F.PERSON_ID%TYPE) IS
199 SELECT QTYP.NAME,qtyp.rank
200 FROM PER_QUALIFICATION_TYPES QTYP,
201 PER_QUALIFICATIONS QUAL,
202 PER_ALL_PEOPLE_F ppf,
203 PER_ALL_PEOPLE_F ppf2
204 WHERE ppf.person_id = p_person
205 AND ppf.party_id = ppf2.party_id
206 AND trunc(sysdate) BETWEEN ppf.effective_start_date and ppf.effective_end_date
207 AND trunc(sysdate) BETWEEN ppf2.effective_start_date and ppf2.effective_end_date
208 AND ppf2.person_id = QUAL.PERSON_ID
209 AND QUAL.QUALIFICATION_TYPE_ID = QTYP.QUALIFICATION_TYPE_ID
210 order by QTYP.RANK desc, qual.awarded_date desc, qual.creation_date desc;
211
212 CURSOR c_qual_types2(p_person PER_ALL_PEOPLE_F.PERSON_ID%TYPE
213 ,p_max_rank number) IS
214 SELECT QTYP.NAME
215 FROM PER_QUALIFICATION_TYPES QTYP,
216 PER_QUALIFICATIONS QUAL,
217 PER_ESTABLISHMENT_ATTENDANCES ESTAB,
218 PER_ALL_PEOPLE_F ppf,
219 PER_ALL_PEOPLE_F ppf2
220 WHERE ppf.person_id = p_person
221 AND ppf.party_id = ppf2.party_id
222 AND trunc(sysdate) BETWEEN ppf.effective_start_date and ppf.effective_end_date
223 AND trunc(sysdate) BETWEEN ppf2.effective_start_date and ppf2.effective_end_date
224 AND ppf2.person_id = ESTAB.PERSON_ID
225 AND estab.attendance_id=qual.attendance_id
226 AND QUAL.QUALIFICATION_TYPE_ID = QTYP.QUALIFICATION_TYPE_ID
227 and nvl(qtyp.rank,-1)>=nvl(p_max_rank,-1)
228 order by QTYP.RANK desc, qual.awarded_date desc, qual.creation_date desc;
229
230 BEGIN
231 -- MAIN FUNCTION LOGIC
232
233 OPEN c_qual_types(p_person_id);
234 --Get an instance of a qualification of max rank
235 FETCH c_qual_types INTO l_qual_type,l_max_rank;
236 if c_qual_types%notfound then
237 l_qual_type:='';
238 l_max_rank:=-1;
239 end if;
240 CLOSE c_qual_types;
241 OPEN c_qual_types2(p_person_id,l_max_rank);
242 --Get an instance of a qualification of max rank
243 FETCH c_qual_types2 INTO l_qual_type2;
244 if c_qual_types2%found then
245 l_qual_type:=l_qual_type2;
246 end if;
247 CLOSE c_qual_types2;
248
249 RETURN (l_qual_type);
250
251 END GET_MAX_QUAL_TYPE;
252 -- ----------------------------------------------------------------------------
253 -- |-------------------------< GET_MAX_QUAL_TYPE_PTY >---------------------------|
254 -- ----------------------------------------------------------------------------
255 FUNCTION GET_MAX_QUAL_TYPE_PTY (p_party_id number)
256 RETURN VARCHAR2
257
258 IS
259
260 l_qual_type PER_QUALIFICATION_TYPES.NAME%TYPE;
261
262 CURSOR c_qual_types IS
263 SELECT QTYP.NAME
264 FROM PER_QUALIFICATION_TYPES QTYP,
265 PER_QUALIFICATIONS QUAL,
266 PER_ALL_PEOPLE_F ppf
267 WHERE ppf.party_id = p_party_id
268 AND trunc(sysdate) BETWEEN ppf.effective_start_date and ppf.effective_end_date
269 AND ppf.person_id = QUAL.PERSON_ID
270 AND QUAL.QUALIFICATION_TYPE_ID = QTYP.QUALIFICATION_TYPE_ID
271 order by QTYP.RANK desc, qual.awarded_date desc, qual.creation_date desc;
272
273 CURSOR c_qual_types2 IS
274 SELECT /*+ FIRST_ROWS */ QTYP.NAME
275 FROM PER_QUALIFICATION_TYPES QTYP,
276 PER_QUALIFICATIONS QUAL,
277 PER_ESTABLISHMENT_ATTENDANCES ESTAB,
278 PER_ALL_PEOPLE_F ppf
279 WHERE ppf.party_id = p_party_id
280 AND trunc(sysdate) BETWEEN ppf.effective_start_date and ppf.effective_end_date
281 AND ppf.person_id = ESTAB.PERSON_ID
282 AND estab.attendance_id=qual.attendance_id
283 AND QUAL.QUALIFICATION_TYPE_ID = QTYP.QUALIFICATION_TYPE_ID
284 and not exists (select 1 from per_qualifications qual2,per_qualification_types qtyp2
285 where qual2.person_id=ppf.person_id
286 and qtyp2.qualification_type_id=qual2.qualification_type_id
287 and nvl(qtyp2.rank,-1)>nvl(qtyp.rank,-1))
288 order by QTYP.RANK desc, qual.awarded_date desc, qual.creation_date desc;
289
290 BEGIN
291 -- MAIN FUNCTION LOGIC
292
293 if (nvl(g_qual_party_id,-1) <>p_party_id) then
294 g_qual_party_id:=p_party_id;
295
296 OPEN c_qual_types2;
297 --Get an instance of a qualification of max rank
298 FETCH c_qual_types2 INTO g_qual_type;
299 if c_qual_types2%notfound then
300 CLOSE c_qual_types2;
301 OPEN c_qual_types;
302 --Get an instance of a qualification of max rank
303 FETCH c_qual_types INTO g_qual_type;
304 if c_qual_types%notfound then
305 g_qual_type:='';
306 end if;
307 CLOSE c_qual_types;
308 else
309 CLOSE c_qual_types2;
310 end if;
311
312 end if;
313
314 RETURN (g_qual_type);
315
316 END GET_MAX_QUAL_TYPE_PTY;
317
318
319 -- ----------------------------------------------------------------------------
320 -- |-----------------------< GET_EMP_UPT_FOR_PERSON >-------------------------|
321 -- ----------------------------------------------------------------------------
322
323 FUNCTION GET_EMP_UPT_FOR_PERSON (p_person_id per_all_people_f.person_id%TYPE,
324 p_eff_date date )
325 RETURN VARCHAR2
326 IS
327 CURSOR csr_emp_person_types
328 IS
329 SELECT ttl.user_person_type
330 FROM per_person_types_tl ttl
331 ,per_person_types typ
332 ,per_person_type_usages_f ptu
333 ,PER_ALL_PEOPLE_F ppf
334 ,PER_ALL_PEOPLE_F ppf2
335 WHERE ppf.person_id = p_person_id
336 AND ppf.party_id = ppf2.party_id
337 AND p_eff_date BETWEEN ppf.effective_start_date and ppf.effective_end_date
338 AND p_eff_date BETWEEN ppf2.effective_start_date and ppf2.effective_end_date
339 AND ppf2.person_id = ptu.person_id
340 AND ttl.language = userenv('LANG')
341 AND ttl.person_type_id = typ.person_type_id
342 AND typ.system_person_type IN ('EMP','EX_EMP')
343 AND typ.person_type_id = ptu.person_type_id
344 AND p_eff_date BETWEEN ptu.effective_start_date
345 AND ptu.effective_end_date
346 order by typ.system_person_type ASC, ptu.effective_start_date DESC;
347
348 l_user_person_type per_person_types_tl.user_person_type%type;
349
350 BEGIN
351 open csr_emp_person_types;
352 fetch csr_emp_person_types into l_user_person_type;
353 if csr_emp_person_types%notfound then
354 l_user_person_type:=null;
355 end if;
356 close csr_emp_person_types;
357
358 RETURN l_user_person_type;
359
360 END GET_EMP_UPT_FOR_PERSON;
361
362
363 -- ----------------------------------------------------------------------------
364 -- |-----------------------< GET_EMP_UPT_FOR_PARTY >-------------------------|
365 -- ----------------------------------------------------------------------------
366
367 FUNCTION GET_EMP_UPT_FOR_PARTY (p_party_id number,
368 p_eff_date date )
369 RETURN VARCHAR2
370 IS
371 CURSOR csr_emp_person_types
372 IS
373 SELECT ttl.user_person_type
374 FROM per_person_types_tl ttl
375 ,per_person_types typ
376 ,per_person_type_usages_f ptu
377 ,PER_ALL_PEOPLE_F ppf
378 WHERE ppf.party_id = p_party_id
379 AND p_eff_date BETWEEN ppf.effective_start_date and ppf.effective_end_date
380 AND ppf.person_id = ptu.person_id
381 AND ttl.language = userenv('LANG')
382 AND ttl.person_type_id = typ.person_type_id
383 AND typ.system_person_type IN ('EMP','EX_EMP')
384 AND typ.person_type_id = ptu.person_type_id
385 AND p_eff_date BETWEEN ptu.effective_start_date
386 AND ptu.effective_end_date
387 order by typ.system_person_type ASC, ptu.effective_start_date DESC;
388
389
390 BEGIN
391
392 if (nvl(g_emp_upt_party_id,-1)<>p_party_id) then
393 g_emp_upt_party_id:=p_party_id;
394 open csr_emp_person_types;
395 fetch csr_emp_person_types into g_emp_upt;
396 if csr_emp_person_types%notfound then
397 g_emp_upt:='';
398 end if;
399 close csr_emp_person_types;
400 end if;
401
402 RETURN g_emp_upt;
403
404 END GET_EMP_UPT_FOR_PARTY;
405
406
407
408
409 -- ----------------------------------------------------------------------------
410 -- |-----------------------< GET_APL_UPT_FOR_PERSON >------------------------|
411 -- ----------------------------------------------------------------------------
412
413 FUNCTION GET_APL_UPT_FOR_PERSON (p_person_id per_all_people_f.person_id%TYPE,
414 p_eff_date date )
415 RETURN VARCHAR2
416 IS
417 CURSOR csr_emp_person_type_exists
418 IS
419 SELECT GET_EMP_UPT_FOR_PERSON (ppf.person_id,p_eff_date)
420 FROM per_people_f ppf
421 WHERE ppf.person_id = p_person_id
422 AND p_eff_date BETWEEN ppf.effective_start_date
423 AND ppf.effective_end_date;
424
425 CURSOR csr_apl_person_types
426 IS
427 SELECT ttl.user_person_type
428 FROM per_person_types_tl ttl
429 ,per_person_types typ
430 ,per_person_type_usages_f ptu
431 ,PER_ALL_PEOPLE_F ppf
432 ,PER_ALL_PEOPLE_F ppf2
433 WHERE ppf.person_id = p_person_id
434 AND ppf.party_id = ppf2.party_id
435 AND p_eff_date BETWEEN ppf.effective_start_date and ppf.effective_end_date
436 AND p_eff_date BETWEEN ppf2.effective_start_date and ppf2.effective_end_date
437 AND ppf2.person_id = ptu.person_id
438 AND ttl.language = userenv('LANG')
439 AND ttl.person_type_id = typ.person_type_id
440 AND typ.system_person_type IN ('APL','EX_APL')
441 AND typ.person_type_id = ptu.person_type_id
442 AND p_eff_date BETWEEN ptu.effective_start_date
443 AND ptu.effective_end_date
444 ORDER BY typ.system_person_type ASC, ptu.effective_start_date DESC;
445
446
447 l_user_person_type per_person_types_tl.user_person_type%type;
448 l_dummy per_person_types_tl.user_person_type%type;
449
450 BEGIN
451 open csr_emp_person_type_exists;
452 fetch csr_emp_person_type_exists into l_dummy;
453 if csr_emp_person_type_exists%found OR
454 (GET_EMP_UPT_FOR_PERSON (p_person_id,p_eff_date)=null)
455 then
456 open csr_apl_person_types;
457 fetch csr_apl_person_types into l_user_person_type;
458 if csr_apl_person_types%notfound then
459 l_user_person_type:=null;
460 end if;
461 close csr_apl_person_types;
462 else
463 l_user_person_type:=null;
464 end if;
465 close csr_emp_person_type_exists;
466
467 RETURN l_user_person_type;
468
469 END GET_APL_UPT_FOR_PERSON;
470
471 -- ----------------------------------------------------------------------------
472 -- |-----------------------< GET_APL_UPT_FOR_PARTY >-------------------------|
473 -- ----------------------------------------------------------------------------
474
475 FUNCTION GET_APL_UPT_FOR_PARTY (p_party_id number,
476 p_eff_date date )
477 RETURN VARCHAR2
478 IS
479 CURSOR csr_emp_person_types
480 IS
481 SELECT ttl.user_person_type
482 FROM per_person_types_tl ttl
483 ,per_person_types typ
484 ,per_person_type_usages_f ptu
485 ,PER_ALL_PEOPLE_F ppf
486 WHERE ppf.party_id = p_party_id
487 AND p_eff_date BETWEEN ppf.effective_start_date and ppf.effective_end_date
488 AND ppf.person_id = ptu.person_id
489 AND ttl.language = userenv('LANG')
490 AND ttl.person_type_id = typ.person_type_id
491 AND typ.system_person_type IN ('APL','EX_APL')
492 AND typ.person_type_id = ptu.person_type_id
493 AND p_eff_date BETWEEN ptu.effective_start_date
494 AND ptu.effective_end_date
495 order by typ.system_person_type ASC, ptu.effective_start_date DESC;
496
497 l_user_person_type per_person_types_tl.user_person_type%type;
498
499 BEGIN
500 if (nvl(g_apl_upt_party_id,-1)<>p_party_id) then
501 g_apl_upt_party_id:=p_party_id;
502 open csr_emp_person_types;
503 fetch csr_emp_person_types into g_apl_upt;
504 if csr_emp_person_types%notfound then
505 g_apl_upt:='';
506 end if;
507 close csr_emp_person_types;
508 end if;
509 RETURN g_apl_upt;
510
511 END GET_APL_UPT_FOR_PARTY;
512
513
514
515 -- ----------------------------------------------------------------------------
516 -- |-----------------------< GET_EMP_SPT_FOR_PERSON >-------------------------|
517 -- ----------------------------------------------------------------------------
518
519 FUNCTION GET_EMP_SPT_FOR_PERSON (p_person_id per_all_people_f.person_id%TYPE,
520 p_eff_date date ) RETURN VARCHAR2
521 IS
522 CURSOR csr_emp_person_types
523 IS
524 SELECT typ.system_person_type
525 FROM per_person_types typ
526 ,per_person_type_usages_f ptu
527 ,PER_ALL_PEOPLE_F ppf
528 ,PER_ALL_PEOPLE_F ppf2
529 WHERE ppf.person_id = p_person_id
530 AND ppf.party_id = ppf2.party_id
531 AND p_eff_date BETWEEN ppf.effective_start_date and ppf.effective_end_date
532 AND p_eff_date BETWEEN ppf2.effective_start_date and ppf2.effective_end_date
533 AND ppf2.person_id = ptu.person_id
534 AND typ.system_person_type IN ('EMP','EX_EMP')
535 AND typ.person_type_id = ptu.person_type_id
536 AND p_eff_date BETWEEN ptu.effective_start_date
537 AND ptu.effective_end_date
538 order by typ.system_person_type ASC, ptu.effective_start_date DESC;
539
540 l_system_person_type per_person_types.system_person_type%type;
541
542 BEGIN
543 open csr_emp_person_types;
544 fetch csr_emp_person_types into l_system_person_type;
545 if csr_emp_person_types%notfound then
546 l_system_person_type:=null;
547 end if;
548 close csr_emp_person_types;
549
550 RETURN l_system_person_type;
551
552 END GET_EMP_SPT_FOR_PERSON;
553 --
554 -- -------------------------------------------------------------------------
555 -- |---------------------< get_recruitment_person_id >-------------------|
556 -- -------------------------------------------------------------------------
557 --
558 FUNCTION GET_RECRUITMENT_PERSON_ID
559 (p_person_id IN per_all_people_f.person_id%TYPE
560 ,p_effective_date IN per_all_people_f.effective_start_date%TYPE)
561 RETURN per_all_people_f.person_id%TYPE
562
563 IS
564 --
565 -- cursor to find party with a notification preference
566 --
567 CURSOR csr_person_with_notif_prefs(p_person_id NUMBER
568 ,p_effective_date DATE) IS
569 SELECT inp.person_id
570 FROM PER_ALL_PEOPLE_F PPF,
571 IRC_NOTIFICATION_PREFERENCES INP,
572 PER_ALL_PEOPLE_F PPF2
573 WHERE ppf.person_id = p_person_id
574 AND trunc(p_effective_date) BETWEEN ppf.effective_start_date
575 AND ppf.effective_end_date
576 AND ppf.party_id = ppf2.party_id
577 AND trunc(p_effective_date) BETWEEN ppf2.effective_start_date
578 AND ppf2.effective_end_date
579 AND ppf2.person_id = inp.person_id;
580
581 begin
582
583 if (nvl(g_rec_person_id_in,-1)<>p_person_id) then
584 g_rec_person_id_in:=p_person_id;
585 --
586 -- check for a person with notif prefs
587 --
588 open csr_person_with_notif_prefs(p_person_id, p_effective_date);
589 fetch csr_person_with_notif_prefs into g_rec_person_id_out;
590 if csr_person_with_notif_prefs%notfound then
591 g_rec_person_id_out:=p_person_id;
592 end if;
593 close csr_person_with_notif_prefs;
594 end if;
595
596 return g_rec_person_id_out;
597 end get_recruitment_person_id;
598 -- ----------------------------------------------------------------------------
599 -- |-----------------------< IS_OPEN_PARTY >-------------------------|
600 -- ----------------------------------------------------------------------------
601
602 FUNCTION IS_OPEN_PARTY (p_party_id number
603 ,p_eff_date date )
604 RETURN VARCHAR2
605 IS
606 CURSOR csr_is_emp is
607 select 1
608 from per_all_people_f per_per
609 where per_per.party_id=p_party_id
610 and p_eff_date between per_per.effective_start_date and per_per.effective_end_date
611 and per_per.current_employee_flag='Y';
612 --
613 cursor csr_has_vac is
614 select 1
615 from per_all_people_f per_per
616 ,per_all_assignments_f per_asg
617 ,per_vacancies per_vac
618 where per_per.party_id=p_party_id
619 and p_eff_date between per_per.effective_start_date and per_per.effective_end_date
620 and per_per.person_id=per_asg.person_id
621 and
622 (
623 per_asg.vacancy_id=per_vac.vacancy_id
624 or
625 per_vac.vacancy_id in (select
626 pvla.vacancy_id
627 from
628 per_vac_linked_assignments pvla,
629 per_all_assignments_f paf
630 where
631 pvla.tgt_apl_asg_id = per_asg.assignment_id
632 and sysdate between nvl(start_date,sysdate) and nvl(sysdate,end_date)
633 and paf.assignment_id = pvla.tgt_apl_asg_id
634 and paf.assignment_type = 'E'
635 )
636 );
637 --
638
639 l_dummy number;
640
641 BEGIN
642 if (nvl(g_open_party_id,-1)<>p_party_id) then
643 g_open_party_id:=p_party_id;
644 g_open_party:='TRUE';
645 open csr_is_emp;
646 fetch csr_is_emp into l_dummy;
647 if csr_is_emp%found then
648 close csr_is_emp;
649 open csr_has_vac;
650 fetch csr_has_vac into l_dummy;
651 if csr_has_vac%notfound then
652 g_open_party:='FALSE';
653 end if;
654 close csr_has_vac;
655 else
656 close csr_is_emp;
657 end if;
658 end if;
659 RETURN g_open_party;
660
661 END IS_OPEN_PARTY;
662
663 -- ----------------------------------------------------------------------------
664 -- |-----------------------< is_internal_person >-------------------------|
665 -- ----------------------------------------------------------------------------
666
667 FUNCTION is_internal_person (p_person_id number
668 ,p_eff_date date )
669 RETURN VARCHAR2
670 IS
671 CURSOR csr_is_emp is
672 select 1
673 from per_all_people_f per_per
674 where per_per.person_id=p_person_id
675 and p_eff_date between per_per.effective_start_date and per_per.effective_end_date
676 and (per_per.current_employee_flag='Y' or per_per.current_npw_flag='Y');
677 --
678 CURSOR csr_is_emp2 is
679 select 1
680 from per_all_people_f per_per
681 , per_all_people_f per_per2
682 where per_per.person_id=p_person_id
683 and p_eff_date between per_per.effective_start_date and per_per.effective_end_date
684 and per_per.party_id is not null
685 and per_per2.party_id=per_per.party_id
686 and p_eff_date between per_per2.effective_start_date and per_per2.effective_end_date
687 and (per_per2.current_employee_flag='Y' or per_per2.current_npw_flag='Y');
688 --
689 l_dummy number;
690
691 BEGIN
692 if (nvl(g_internal_person_id,-1)<>p_person_id) then
693 g_internal_person_id:=p_person_id;
694 g_internal_person:='FALSE';
695 open csr_is_emp2;
696 fetch csr_is_emp2 into l_dummy;
697 if csr_is_emp2%found then
698 close csr_is_emp2;
699 g_internal_person:='TRUE';
700 else
701 close csr_is_emp2;
702 open csr_is_emp;
703 fetch csr_is_emp into l_dummy;
704 if csr_is_emp%found then
705 g_internal_person:='TRUE';
706 end if;
707 close csr_is_emp;
708 end if;
709 end if;
710 RETURN g_internal_person;
711
712 END is_internal_person;
713
714 -- ----------------------------------------------------------------------------
715 -- |-----------------------< is_internal_email >-------------------------|
716 -- ----------------------------------------------------------------------------
717
718 FUNCTION is_internal_email (p_email_address varchar2
719 ,p_eff_date date )
720 RETURN VARCHAR2
721 IS
722 cursor get_user is
723 select employee_id
724 from fnd_user
725 where upper(email_address)=upper(p_email_address);
726
727 l_employee_id number(15);
728
729 begin
730
731 if (nvl(g_internal_email_address,'X')<>p_email_address) then
732 g_internal_email_address:=p_email_address;
733 g_internal_email:='FALSE';
734
735 open get_user;
736 fetch get_user into l_employee_id;
737 if get_user%found then
738 close get_user;
739 if l_employee_id is not null then
740 g_internal_email:=irc_utilities_pkg.is_internal_person(l_employee_id
741 ,p_eff_date);
742 end if;
743 else
744 close get_user;
745 end if;
746 end if;
747 RETURN g_internal_email;
748
749 end is_internal_email;
750
751 -- ----------------------------------------------------------------------------
752 -- |-----------------------< is_internal_person >-------------------------|
753 -- ----------------------------------------------------------------------------
754
755 FUNCTION is_internal_person (p_user_name varchar2
756 ,p_eff_date date )
757 RETURN VARCHAR2
758 IS
759 cursor get_user is
760 select employee_id
761 from fnd_user
762 where user_name=upper(p_user_name);
763
764 l_employee_id number(15);
765
766 begin
767 if (nvl(g_internal_user_name,'X')<>p_user_name) then
768 g_internal_user_name:=p_user_name;
769 g_internal_user:='FALSE';
770
771 open get_user;
772 fetch get_user into l_employee_id;
773 if get_user%found then
774 close get_user;
775 if l_employee_id is not null then
776 g_internal_user:=irc_utilities_pkg.is_internal_person(l_employee_id
777 ,p_eff_date);
778 end if;
779 else
780 close get_user;
781 end if;
782 end if;
783 RETURN g_internal_user;
784
785 end is_internal_person;
786
787 -- ----------------------------------------------------------------------------
788 -- |-----------------------< is_function_allowed >-------------------------|
789 -- ----------------------------------------------------------------------------
790 FUNCTION is_function_allowed(p_function_name varchar2
791 ,p_test_maint_availability in varchar2 default 'Y')
792 RETURN VARCHAR2
793 IS
794 l_retval varchar2(5);
795 begin
796 if(fnd_function.test(p_function_name,p_test_maint_availability)) then
797 l_retval := 'TRUE';
798 else
799 l_retval := 'FALSE';
800 end if;
801 RETURN l_retval;
802 end is_function_allowed;
803
804 --
805 --Added GET_LAST_QUAL_PTY by deenath to fix Bug #4726469
806 -- ----------------------------------------------------------------------------
807 -- |---------------------< GET_LAST_QUAL_PTY >-----------------------|
808 -- ----------------------------------------------------------------------------
809 FUNCTION GET_LAST_QUAL_PTY(p_party_id NUMBER,
810 p_eff_date DATE)
811 RETURN VARCHAR2
812 IS
813 --
814 CURSOR c_qualifications IS
815 SELECT pqt.name
816 FROM per_qualifications pq
817 ,per_qualification_types_vl pqt
818 WHERE pq.party_id = p_party_id
819 AND pq.attendance_id IS NOT NULL
820 AND pq.awarded_date IS NOT NULL
821 AND pqt.qualification_type_id = pq.qualification_type_id
822 ORDER BY pq.awarded_date DESC;
823 --
824 l_qual_title per_qualification_types_vl.name%TYPE;
825 --
826 BEGIN
827 OPEN c_qualifications;
828 FETCH c_qualifications INTO l_qual_title;
829 IF c_qualifications%NOTFOUND THEN
830 l_qual_title := '';
831 END IF;
832 CLOSE c_qualifications;
833 RETURN(l_qual_title);
834 END GET_LAST_QUAL_PTY;
835 --
836 -- -------------------------------------------------------------------
837 -- |--------------------< irc_applicant_tracking >-------------------|
838 -- -------------------------------------------------------------------
839 --
840 procedure irc_applicant_tracking(p_person_id in number
841 ,p_apl_profile_access_id in number
842 ,p_object_version_number out nocopy number
843 ) is
844 pragma autonomous_transaction;
845 l_object_version_number number;
846 l_return_status number;
847 l_person_id number := p_person_id;
848 l_apl_profile_access_id number := p_apl_profile_access_id;
849 --
850 begin
851 --
852 irc_apl_profile_access_api.create_apl_profile_access (
853 P_VALIDATE => false
854 ,P_PERSON_ID => l_person_id
855 ,P_APL_PROFILE_ACCESS_ID => l_apl_profile_access_id
856 ,P_OBJECT_VERSION_NUMBER => l_object_version_number
857 );
858 commit;
859 exception
860 when others then
861 rollback;
862 end irc_applicant_tracking;
863 --
864 -- ----------------------------------------------------------------------------
865 -- |---------------------< irc_mark_appl_considered >-----------------------|
866 -- ----------------------------------------------------------------------------
867 --
868 procedure irc_mark_appl_considered( p_effective_date in date
869 ,p_assignment_id in number
870 ,p_attempt_id in number
871 ,p_assignment_details_id in number
872 ,p_qualified in varchar2
873 ,p_considered in varchar2
874 ,p_update_mode in varchar2
875 ,p_details_version out nocopy number
876 ,p_effective_start_date out nocopy date
877 ,p_effective_end_date out nocopy date
878 ,p_object_version_number out nocopy number
879 )is
880 pragma autonomous_transaction;
881 --
882 l_effective_date date := p_effective_date;
883 l_assignment_id number := p_assignment_id;
884 l_attempt_id number := p_attempt_id;
885 l_assignment_details_id number ;
886 l_qualified varchar2(30) := p_qualified;
887 l_considered varchar2(30) := p_considered;
888 l_details_version number;
889 l_effective_start_date date;
890 l_effective_end_date date;
891 l_object_version_number number;
892 l_return_status varchar2(30);
893 l_update_mode varchar2(30) := p_update_mode;
894 l_assgn_details_row irc_assignment_details_f%ROWTYPE;
895 l_business_grp_id per_all_assignments_f.business_group_id%TYPE;
896 l_appln_tracking varchar2(1);
897 --
898 cursor c_assgn_details_row is
899 select *
900 from irc_assignment_details_f
901 where assignment_id =p_assignment_id
902 and sysdate between effective_start_date and effective_end_Date
903 and latest_details='Y';
904 --
905 cursor c_bg_id is
906 select business_group_id
907 from per_all_assignments_f
908 where assignment_id = p_assignment_id
909 and trunc(sysdate) between effective_start_date and effective_end_date;
910 --
911 cursor c_appln_tracking is
912 select nvl(ORG_INFORMATION11,'N')
913 from HR_ORGANIZATION_INFORMATION
914 where organization_id = l_business_grp_id
915 and org_information_context = 'BG Recruitment';
916 --
917 begin
918 hr_utility.set_location('Entering irc_mark_appl_considered',10);
919 --
920 open c_bg_id;
921 fetch c_bg_id into l_business_grp_id;
922 close c_bg_id;
923 --
924 open c_appln_tracking;
925 fetch c_appln_tracking into l_appln_tracking;
926 close c_appln_tracking;
927 --
928 if ( l_appln_tracking <> 'N') then
929 open c_assgn_details_row;
930 fetch c_assgn_details_row into l_assgn_details_row;
931 --
932 if(c_assgn_details_row%ROWCOUNT=0) then
933 hr_utility.set_location('Assignment details record does not exist',20);
934 irc_assignment_details_api.create_assignment_details (
935 p_validate => false
936 ,P_EFFECTIVE_DATE => l_effective_date
937 ,p_assignment_id => l_assignment_id
938 ,p_attempt_id => l_attempt_id
939 ,P_QUALIFIED => l_qualified
940 ,P_CONSIDERED => l_considered
941 ,p_assignment_details_id => l_assignment_details_id
942 ,p_details_version => l_details_version
943 ,p_effective_start_date => l_effective_start_date
944 ,p_effective_end_date => l_effective_end_date
945 ,P_OBJECT_VERSION_NUMBER => l_object_version_number
946 );
947 hr_utility.set_location('Created new assignment details row',30);
948 else
949 hr_utility.set_location('Assignment details record already exists',40);
950 hr_utility.set_location('p_update_mode::'||p_update_mode,50);
951
952 l_object_version_number := l_assgn_details_row.object_version_number;
953 l_assignment_details_id := l_assgn_details_row.assignment_details_id;
954
955 irc_assignment_details_api.update_assignment_details (
956 p_validate => false
957 ,p_effective_date => l_effective_date
958 ,p_datetrack_update_mode => l_update_mode
959 ,p_assignment_id => l_assignment_id
960 ,p_attempt_id => l_attempt_id
961 ,p_qualified => l_qualified
962 ,p_considered => l_considered
963 ,p_assignment_details_id => l_assignment_details_id
964 ,p_object_version_number => l_object_version_number
965 ,p_details_version => l_details_version
966 ,p_effective_start_date => l_effective_start_date
967 ,p_effective_end_date => l_effective_end_date
968 );
969 hr_utility.set_location('Updated assignment details record',60);
970 end if;
971 close c_assgn_details_row;
972 commit;
973 end if;
974 hr_utility.set_location('Leaving irc_mark_appl_considered',10);
975 exception
976 when others then
977 hr_utility.set_location('Exception in irc_mark_appl_considered::' || SQLERRM,100);
978 rollback;
979 hr_utility.set_location('Leaving irc_mark_appl_considered',10);
980 raise;
981 end irc_mark_appl_considered;
982 --
983 -- ----------------------------------------------------------------------------
984 -- |---------------------< irc_mark_appl_considered >-----------------------|
985 -- ----------------------------------------------------------------------------
986 --
987 procedure irc_mark_appl_considered(p_assignment_id in number)is
988 pragma autonomous_transaction;
989 --
990 l_effective_date date := trunc(sysdate);
991 l_assignment_id number := p_assignment_id;
992 l_attempt_id number ;
993 l_assignment_details_id number ;
994 l_qualified varchar2(30) ;
995 l_considered varchar2(30) := 'Y';
996 l_details_version number;
997 l_effective_start_date date;
998 l_effective_end_date date;
999 l_object_version_number number;
1000 l_row number;
1001 l_return_status varchar2(30);
1002 l_update_mode varchar2(30);
1003 l_assgn_details_row irc_assignment_details_f%ROWTYPE;
1004 l_appl_tracking varchar2(1);
1005 l_business_grp_id per_all_assignments_f.business_group_id%TYPE;
1006
1007 cursor c_bg_id is select business_group_id from per_all_assignments_f where assignment_id=p_assignment_id and trunc(sysdate) between effective_start_date and effective_end_date;
1008 cursor c_assgn_details_row is select * from irc_assignment_details_f where assignment_id =p_assignment_id and latest_details='Y';
1009 cursor c_assgn_details is select 1 from irc_assignment_details_f where assignment_id =p_assignment_id and sysdate between effective_start_date and effective_end_Date and latest_details='Y';
1010 cursor c_appln_tracking is select nvl(ORG_INFORMATION11,'N') from HR_ORGANIZATION_INFORMATION where organization_id=l_business_grp_id and org_information_context='BG Recruitment';
1011
1012 begin
1013
1014 open c_bg_id;
1015 fetch c_bg_id into l_business_grp_id;
1016 close c_bg_id;
1017
1018 open c_appln_tracking;
1019 fetch c_appln_tracking into l_appl_tracking;
1020 close c_appln_tracking;
1021
1022 open c_assgn_details_row;
1023 fetch c_assgn_details_row into l_assgn_details_row;
1024 close c_assgn_details_row;
1025
1026 if (l_appl_tracking<>'N') then
1027
1028 open c_assgn_details;
1029 fetch c_assgn_details into l_row;
1030 if(c_assgn_details%NOTFOUND) then
1031
1032 irc_assignment_details_api.create_assignment_details (
1033 p_validate => false
1034 ,P_EFFECTIVE_DATE => l_effective_date
1035 ,p_assignment_id => l_assignment_id
1036 ,p_attempt_id => l_attempt_id
1037 ,P_QUALIFIED => l_qualified
1038 ,P_CONSIDERED => l_considered
1039 ,p_assignment_details_id => l_assignment_details_id
1040 ,p_details_version => l_details_version
1041 ,p_effective_start_date => l_effective_start_date
1042 ,p_effective_end_date => l_effective_end_date
1043 ,P_OBJECT_VERSION_NUMBER => l_object_version_number
1044 );
1045 else
1046
1047 if(trunc(sysdate)>l_assgn_details_row.effective_start_date) then
1048 l_update_mode:='UPDATE';
1049 else
1050 l_update_mode:='CORRECTION';
1051 end if;
1052
1053 l_assignment_details_id:=l_assgn_details_row.assignment_details_id;
1054 l_object_version_number:=l_assgn_details_row.object_version_number;
1055 irc_assignment_details_api.update_assignment_details (
1056 p_validate => false
1057 ,p_effective_date => l_effective_date
1058 ,p_datetrack_update_mode => l_update_mode
1059 ,p_assignment_id => l_assignment_id
1060 ,p_attempt_id => l_assgn_details_row.attempt_id
1061 ,p_qualified => l_assgn_details_row.qualified
1062 ,p_considered => l_considered
1063 ,p_assignment_details_id => l_assignment_details_id
1064 ,p_object_version_number => l_object_version_number
1065 ,p_details_version => l_details_version
1066 ,p_effective_start_date => l_effective_start_date
1067 ,p_effective_end_date => l_effective_end_date
1068 );
1069 end if;
1070 close c_assgn_details;
1071 commit;
1072 end if;
1073 exception
1074 when others then
1075 rollback;
1076 end irc_mark_appl_considered;
1077
1078 function getAMETxnDetailsForOffer (p_offerId in varchar2)
1079 return varchar2
1080 is
1081 cursor c_AMETxnDetails(c_offerId in varchar2) is
1082 SELECT history.transaction_history_id
1083 ,irc_xml_util.valueOf(histstate.transaction_document,'/Transaction/TransCtx/pAMETranType')
1084 ,irc_xml_util.valueOf(histstate.transaction_document,'/Transaction/TransCtx/pAMEAppId')
1085 FROM pqh_ss_transaction_history history,
1086 pqh_ss_trans_state_history histstate
1087 WHERE history.transaction_identifier = 'OFFER'
1088 AND history.transaction_history_id = ( SELECT min(transaction_history_id)
1089 FROM pqh_ss_step_history
1090 WHERE api_name = 'IRC_OFFERS_SWI.PROCESS_OFFERS_API'
1091 AND pk1 = c_offerId )
1092 AND histstate.transaction_history_id = history.transaction_history_id;
1093 txnId number := null;
1094 txnType varchar2(100) := null;
1095 applId number :=null;
1096 begin
1097 open c_AMETxnDetails(p_offerId);
1098 fetch c_AMETxnDetails into txnId, txnType, applId;
1099 if c_AMETxnDetails%FOUND then
1100 return txnId || ':' || txnType ||':' ||applId;
1101 end if;
1102 close c_AMETxnDetails;
1103 return '';
1104 end;
1105
1106 --
1107 -- ----------------------------------------------------------------------------
1108 -- |---------------------< copy_candidate_address >-----------------------|
1109 -- ----------------------------------------------------------------------------
1110 --
1111 procedure copy_candidate_address
1112 (p_assignment_id in number) is
1113 --
1114 l_business_group_id number;
1115 l_person_id_appl number;
1116 l_person_id number;
1117 l_application_date per_addresses.date_from%TYPE;
1118 p_addr per_addresses%ROWTYPE;
1119 l_addr_id number;
1120 l_party_id number;
1121 l_object_version_number number;
1122 l_return_status varchar2(30);
1123 l_proc varchar2(72) := 'irc_utilities_pkg.copy_candidate_address';
1124 l_addr_found boolean := false;
1125 --
1126 Cursor C_Assignment_details is
1127 select person_id
1128 , business_group_id
1129 , effective_start_date
1130 from per_all_assignments_f
1131 where assignment_id=p_assignment_id
1132 and sysdate between effective_start_date and effective_end_date;
1133 --
1134 Cursor C_Address_id is
1135 select address_id
1136 from per_addresses
1137 where person_id=l_person_id_appl;
1138 --
1139 Cursor C_Person_id is
1140 select min(person_id)
1141 from per_all_people_f
1142 where party_id in (select party_id
1143 from per_all_people_f
1144 where person_id=l_person_id_appl);
1145 --
1146 Cursor C_Address_details_Rec is
1147 select *
1148 from per_addresses
1149 where person_id=l_person_id
1150 and address_type='REC'
1151 and sysdate between date_from and nvl(date_to,sysdate);
1152 --
1153 Cursor C_Address_details_Primary is
1154 select *
1155 from per_addresses
1156 where person_id=l_person_id
1157 and primary_flag = 'Y'
1158 and sysdate between date_from and nvl(date_to,sysdate);
1159 --
1160 begin
1161 --
1162 hr_utility.set_location('Enteringp_addr: '||l_proc, 5);
1163 --
1164 open C_Assignment_details;
1165 fetch C_Assignment_details into l_person_id_appl,l_business_group_id,l_application_date;
1166 close C_Assignment_details;
1167 --
1168 open C_Address_id;
1169 fetch C_Address_id into l_addr_id;
1170 if C_Address_id%notfound then
1171 --
1172 hr_utility.set_location('C_Address_id not found: '||l_proc, 6);
1173 open C_Person_id;
1174 fetch C_Person_id into l_person_id;
1175 close C_Person_id;
1176 --
1177 -- Modified the logic to get the recuritment address of the applicant.
1178 -- If the recuritment address is not found getting the primary address of the applicant
1179 -- If one of the above is found creating the address with applicant person id
1180 --
1181 open C_Address_details_Rec;
1182 fetch C_Address_details_Rec into p_addr;
1183 --
1184 if C_Address_details_Rec%notfound then
1185 hr_utility.set_location('C_Address_details_Rec not found: '||l_proc, 7);
1186 open C_Address_details_Primary;
1187 fetch C_Address_details_Primary into p_addr;
1188 --
1189 if C_Address_details_Primary%found then
1190 hr_utility.set_location('C_Address_details_Primary found: '||l_proc, 8);
1191 l_addr_found := true;
1192 end if;
1193 --
1194 close C_Address_details_Primary;
1195 else
1196 hr_utility.set_location('C_Address_details_Rec found: '||l_proc, 9);
1197 l_addr_found := true;
1198 end if;
1199 --
1200 if l_addr_found then
1201 hr_utility.set_location('Creating the address: '||l_proc, 10);
1202 --
1203 p_addr.person_id:=l_person_id_appl;
1204 p_addr.primary_flag:='Y';
1205 p_addr.address_id:=null;
1206 p_addr.object_version_number:=null;
1207 p_addr.date_from:=l_application_date;
1208 p_addr.business_group_id:=l_business_group_id;
1209 p_addr.address_type:='REC';
1210 --
1211 hr_person_address_swi.create_person_address (
1212 P_EFFECTIVE_DATE => p_addr.date_from
1213 ,P_PERSON_ID => p_addr.person_id
1214 ,P_PRIMARY_FLAG => p_addr.primary_flag
1215 ,P_STYLE => p_addr.style
1216 ,P_DATE_FROM => p_addr.date_from
1217 ,P_DATE_TO => p_addr.date_to
1218 ,P_ADDRESS_TYPE => p_addr.address_type
1219 ,P_COMMENTS => p_addr.comments
1220 ,P_ADDRESS_LINE1 => p_addr.address_line1
1221 ,P_ADDRESS_LINE2 => p_addr.address_line2
1222 ,P_ADDRESS_LINE3 => p_addr.address_line3
1223 ,P_TOWN_OR_CITY => p_addr.town_or_city
1224 ,P_REGION_1 => p_addr.region_1
1225 ,P_REGION_2 => p_addr.region_2
1226 ,P_REGION_3 => p_addr.region_3
1227 ,P_POSTAL_CODE => p_addr.postal_code
1228 ,P_COUNTRY => p_addr.country
1229 ,P_TELEPHONE_NUMBER_1 => p_addr.telephone_number_1
1230 ,P_TELEPHONE_NUMBER_2 => p_addr.telephone_number_2
1231 ,P_TELEPHONE_NUMBER_3 => p_addr.telephone_number_3
1232 ,P_ADDR_ATTRIBUTE_CATEGORY => p_addr.addr_attribute_category
1233 ,P_ADDR_ATTRIBUTE1 => p_addr.addr_attribute1
1234 ,P_ADDR_ATTRIBUTE2 => p_addr.addr_attribute2
1235 ,P_ADDR_ATTRIBUTE3 => p_addr.addr_attribute3
1236 ,P_ADDR_ATTRIBUTE4 => p_addr.addr_attribute4
1237 ,P_ADDR_ATTRIBUTE5 => p_addr.addr_attribute5
1238 ,P_ADDR_ATTRIBUTE6 => p_addr.addr_attribute6
1239 ,P_ADDR_ATTRIBUTE7 => p_addr.addr_attribute7
1240 ,P_ADDR_ATTRIBUTE8 => p_addr.addr_attribute8
1241 ,P_ADDR_ATTRIBUTE9 => p_addr.addr_attribute9
1242 ,P_ADDR_ATTRIBUTE10 => p_addr.addr_attribute10
1243 ,P_ADDR_ATTRIBUTE11 => p_addr.addr_attribute11
1244 ,P_ADDR_ATTRIBUTE12 => p_addr.addr_attribute12
1245 ,P_ADDR_ATTRIBUTE13 => p_addr.addr_attribute13
1246 ,P_ADDR_ATTRIBUTE14 => p_addr.addr_attribute14
1247 ,P_ADDR_ATTRIBUTE15 => p_addr.addr_attribute15
1248 ,P_ADDR_ATTRIBUTE16 => p_addr.addr_attribute16
1249 ,P_ADDR_ATTRIBUTE17 => p_addr.addr_attribute17
1250 ,P_ADDR_ATTRIBUTE18 => p_addr.addr_attribute18
1251 ,P_ADDR_ATTRIBUTE19 => p_addr.addr_attribute19
1252 ,P_ADDR_ATTRIBUTE20 => p_addr.addr_attribute20
1253 ,P_ADD_INFORMATION13 => p_addr.add_information13
1254 ,P_ADD_INFORMATION14 => p_addr.add_information14
1255 ,P_ADD_INFORMATION15 => p_addr.add_information15
1256 ,P_ADD_INFORMATION16 => p_addr.add_information16
1257 ,P_ADD_INFORMATION17 => p_addr.add_information17
1258 ,P_ADD_INFORMATION18 => p_addr.add_information18
1259 ,P_ADD_INFORMATION19 => p_addr.add_information19
1260 ,P_ADD_INFORMATION20 => p_addr.add_information20
1261 ,P_PARTY_ID => p_addr.party_id
1262 ,P_ADDRESS_ID => p_addr.address_id
1263 ,P_OBJECT_VERSION_NUMBER => l_object_version_number
1264 ,P_RETURN_STATUS => l_return_status
1265 );
1266 hr_utility.set_location('After Creating the address: '||l_proc, 11);
1267 end if;
1268 --
1269 close C_Address_details_Rec;
1270 end if;
1271 --
1272 close C_Address_id;
1273 hr_utility.set_location(' Leaving: ' || l_proc, 50);
1274 --
1275 exception
1276 when others then
1277 hr_utility.set_location(' Exception occured: ' || l_proc, 50);
1278 raise;
1279 end copy_candidate_address;
1280 --
1281 --
1282 -- ----------------------------------------------------------------------------
1283 -- |---------------------< copy_candidate_details >-----------------------|
1284 -- ----------------------------------------------------------------------------
1285 --
1286 procedure copy_candidate_details
1287 (p_assignment_id in number) is
1288 l_proc varchar2(72) := 'irc_utilities_pkg.copy_candidate_address';
1289 begin
1290 hr_utility.set_location('Entering: '||l_proc, 5);
1291 copy_candidate_address(p_assignment_id);
1292 hr_utility.set_location('Leaving: '||l_proc, 5);
1293 exception
1294 when others then
1295 raise;
1296 end copy_candidate_details;
1297 --
1298 -- ----------------------------------------------------------------------------
1299 -- |---------------------------< get_fte_factor >---------------------------|
1300 -- ----------------------------------------------------------------------------
1301 FUNCTION get_fte_factor(p_assignment_id NUMBER ,
1302 p_asg_hours_per_year NUMBER ,
1303 p_position_id NUMBER ,
1304 p_organization_id NUMBER ,
1305 p_business_group_id NUMBER ,
1306 p_effective_date IN DATE)
1307 RETURN NUMBER
1308 IS
1309 CURSOR csr_pos_fte IS
1310 SELECT pos.working_hours ,
1311 DECODE(pos.frequency ,'Y',1 ,'M',12 ,'W',52 ,'D',365 ,1)
1312 FROM hr_all_positions pos
1313 WHERE pos.position_id=p_position_id;
1314 CURSOR csr_org_fte IS
1315 SELECT fnd_number.canonical_to_number(org.org_information3) normal_hours ,
1316 DECODE(org.org_information4 ,'Y',1 ,'M',12 ,'W',52 ,'D',365 ,1)
1317 FROM HR_ORGANIZATION_INFORMATION org
1318 WHERE org.organization_id =p_organization_id
1319 AND org.organization_id = p_organization_id
1320 AND org.org_information_context(+) = 'Work Day Information';
1321 CURSOR csr_bus_fte IS
1322 SELECT fnd_number.canonical_to_number(bus.working_hours) normal_hours ,
1323 DECODE(bus.frequency ,'Y',1 ,'M',12 ,'W',52 ,'D',365 ,1)
1324 FROM per_business_groups bus
1325 WHERE bus.business_group_id = p_business_group_id;
1326 --
1327 l_fte_factor NUMBER := NULL;
1328 l_norm_hours_per_year NUMBER;
1329 l_hours_per_year NUMBER;
1330 l_norm_hours NUMBER;
1331 l_norm_frequency NUMBER;
1332 BEGIN
1333 --
1334 IF(NVL(p_asg_hours_per_year,0) <> 0) THEN
1335 IF(p_position_id IS NOT NULL) THEN
1336 open csr_pos_fte;
1337 fetch csr_pos_fte into l_norm_hours,l_norm_frequency;
1338 close csr_pos_fte;
1339 END IF;
1340 IF (l_norm_hours IS NULL OR l_norm_frequency IS NULL)THEN
1341 open csr_org_fte;
1342 fetch csr_org_fte into l_norm_hours,l_norm_frequency;
1343 close csr_org_fte;
1344 END IF;
1345 IF (l_norm_hours IS NULL OR l_norm_frequency IS NULL)THEN
1346 open csr_bus_fte;
1347 fetch csr_bus_fte into l_norm_hours,l_norm_frequency;
1348 close csr_bus_fte;
1349 END IF;
1350 l_norm_hours_per_year := NVL(l_norm_hours,0)*l_norm_frequency;
1351 IF ( NVL(l_norm_hours_per_year,0) = 0) THEN
1352 l_fte_factor := 1;
1353 ELSE
1354 l_fte_factor := l_norm_hours_per_year/p_asg_hours_per_year;
1355 END IF;
1356 ELSE
1357 l_fte_factor := 1;
1358 END IF;
1359 IF (l_fte_factor IS NULL) THEN
1360 l_fte_factor := 1;
1361 END IF;
1362 --
1363 RETURN l_fte_factor;
1364 END get_fte_factor;
1365 --
1366 -- ----------------------------------------------------------------------------
1367 --|----------------------< get_fte_factor >-----------------------------------|
1368 -- ----------------------------------------------------------------------------
1369 --
1370 FUNCTION get_fte_factor(p_assignment_id in NUMBER) RETURN NUMBER IS
1371 CURSOR csr_orgdetails IS
1372 SELECT asg.normal_hours * (DECODE(asg.frequency, 'D', 365, 'W', 52, 'M', 12, 'Y', 1, 1 ) ) as p_asg_hours_per_year
1373 ,asg.position_id as p_position_id
1374 ,asg.organization_id as p_organization_id
1375 ,asg.business_group_id as p_business_group_id
1376 ,pay.change_date as p_effective_date
1377 FROM PER_ALL_ASSIGNMENTS_F asg
1378 ,PER_PAY_PROPOSALS pay
1379 WHERE asg.assignment_id = p_assignment_id
1380 and pay.assignment_id = p_assignment_id
1381 and asg.effective_end_date = ( SELECT max (effective_end_date)
1382 FROM per_all_assignments_f
1383 WHERE assignment_id = p_assignment_id
1384 );
1385 --
1386 l_asg_hours_per_year NUMBER ;
1387 l_position_id NUMBER;
1388 l_organization_id NUMBER;
1389 l_business_group_id NUMBER;
1390 l_effective_date DATE;
1391 l_fte_factor NUMBER := NULL;
1392 l_proc varchar2(72) := 'irc_utilities_pkg.get_fte_factor';
1393 BEGIN
1394 hr_utility.set_location('Entering: '||l_proc, 5);
1395 If(NVL(p_assignment_id,0) <> 0 ) then
1396 open csr_orgdetails;
1397 fetch csr_orgdetails into l_asg_hours_per_year
1398 ,l_position_id
1399 ,l_organization_id
1400 ,l_business_group_id
1401 ,l_effective_date;
1402 close csr_orgdetails;
1403 l_fte_factor := get_fte_factor(p_assignment_id => p_assignment_id
1404 ,p_asg_hours_per_year => l_asg_hours_per_year
1405 ,p_position_id => l_position_id
1406 ,p_organization_id => l_organization_id
1407 ,p_business_group_id => l_business_group_id
1408 ,p_effective_date => l_effective_date);
1409 End if;
1410 Return l_fte_factor;
1411 hr_utility.set_location('Leaving: '||l_proc, 10);
1412 END get_fte_factor;
1413 --
1414 -- ----------------------------------------------------------------------------
1415 -- |---------------------< split_to_token >---------------------------------|
1416 -- ----------------------------------------------------------------------------
1417 --
1418 function split_to_token(list varchar2, indexnum number) return varchar2
1419 is
1420 pos_start number;
1421 pos_end number;
1422 delimiter varchar(2) := ',';
1423 begin
1424 if indexnum = 1 then
1425 pos_start := 1;
1426 else
1427 pos_start := instr(list, delimiter, 1, indexnum - 1);
1428 if pos_start = 0 then
1429 return null;
1430 else
1431 pos_start := pos_start + length(delimiter);
1432 end if;
1433 end if;
1434
1435 pos_end := instr(list, delimiter, pos_start, 1);
1436
1437 if pos_end = 0 then
1438 return substr(list, pos_start);
1439 else
1440 return substr(list, pos_start, pos_end - pos_start);
1441 end if;
1442
1443 end split_to_token;
1444 --
1445 --
1446 -- ----------------------------------------------------------------------------
1447 -- |---------------------< getRunFunctionUrl >---------------------------------|
1448 -- ----------------------------------------------------------------------------
1449 --
1450 function getVacancyRunFunctionUrl(
1451 p_function_id number,
1452 p_vacancy_id number,
1453 p_posting_id number,
1454 p_site_id number
1455 ) return varchar2 is
1456 url varchar2(32767);
1457 encyrpt boolean;
1458 l_function_id number;
1459 params varchar2(32767);
1460 begin
1461 encyrpt := true;
1462 l_function_id := p_function_id ;
1463 params:='p_svid='||p_vacancy_id||'&'||'p_spid='||p_posting_id||'&'||'p_site_id='||p_site_id;
1464
1465 url := fnd_run_function.get_run_function_url ( p_function_id =>l_function_id,
1466 p_resp_appl_id => fnd_global.resp_appl_id,
1467 p_resp_id =>fnd_global.resp_id,
1468 p_security_group_id =>fnd_global.security_group_id,
1469 p_parameters =>params,
1470 p_override_agent=>'/OA_HTML',
1471 p_encryptParameters =>encyrpt );
1472 return url;
1473 end;
1474 --
1475 --
1476 -- ----------------------------------------------------------------------------
1477 -- |---------------------< getJobSearchItems >---------------------------------|
1478 -- ----------------------------------------------------------------------------
1479 --
1480
1481 function getJobSearchItems(l_keyword varchar2 default null,
1482 l_job varchar2 default null,
1483 l_employee varchar2 default null,
1484 l_contractor varchar2 default null,
1485 l_dateposted varchar2 default null,
1486 l_travelpercent number default null,
1487 l_workathome varchar2 default null,
1488 l_employmentstatus varchar2 default null,
1489 l_min_salary in varchar2 default null,
1490 l_currency in varchar2 default null,
1491 l_lat in varchar2 default null,
1492 l_long in varchar2 default null,
1493 l_distance in varchar2 default null,
1494 l_location in varchar2 default null,
1495 l_attribute_category in varchar2 default null,
1496 l_attribute1 in varchar2 default null,
1497 l_attribute2 in varchar2 default null,
1498 l_attribute3 in varchar2 default null,
1499 l_attribute4 in varchar2 default null,
1500 l_attribute5 in varchar2 default null,
1501 l_attribute6 in varchar2 default null,
1502 l_attribute7 in varchar2 default null,
1503 l_attribute8 in varchar2 default null,
1504 l_attribute9 in varchar2 default null,
1505 l_attribute10 in varchar2 default null,
1506 l_attribute11 in varchar2 default null,
1507 l_attribute12 in varchar2 default null,
1508 l_attribute13 in varchar2 default null,
1509 l_attribute14 in varchar2 default null,
1510 l_attribute15 in varchar2 default null,
1511 l_attribute16 in varchar2 default null,
1512 l_attribute17 in varchar2 default null,
1513 l_attribute18 in varchar2 default null,
1514 l_attribute19 in varchar2 default null,
1515 l_attribute20 in varchar2 default null,
1516 l_attribute21 in varchar2 default null,
1517 l_attribute22 in varchar2 default null,
1518 l_attribute23 in varchar2 default null,
1519 l_attribute24 in varchar2 default null,
1520 l_attribute25 in varchar2 default null,
1521 l_attribute26 in varchar2 default null,
1522 l_attribute27 in varchar2 default null,
1523 l_attribute28 in varchar2 default null,
1524 l_attribute29 in varchar2 default null,
1525 l_attribute30 in varchar2 default null,
1526 langcode in varchar2 default null,
1527 enterprise in varchar2 default null,
1528 l_drvdlocal in varchar2 default null,
1529 l_locid in varchar2 default null) return clob is
1530
1531 query varchar2(32000);
1532 ctx DBMS_XMLQUERY.ctxType;
1533 xml clob;
1534 bindNum number:=1;
1535 type bindlist is table of varchar2(4000) index by binary_integer;
1536 bindvalues bindlist;
1537 token varchar2(100);
1538 firstToken boolean := true;
1539 i number;
1540 test varchar2(32000);
1541 l_funcId number;
1542
1543 cursor c_func(p_function_name varchar2) is
1544 select function_id from fnd_form_functions
1545 where function_name = p_function_name;
1546 begin
1547 if enterprise is not null then
1548 hr_multi_tenancy_pkg.set_context_for_enterprise(enterprise);
1549 end if;
1550
1551 open c_func('IRC_VIS_VAC_DISPLAY');
1552 fetch c_func into l_funcId;
1553 close c_func;
1554
1555 if l_keyword is not null or l_job is not null or l_dateposted is not null or l_drvdlocal is not null then
1556
1557 query:='SELECT PAV.NAME title ,
1558 fnd_message.get_string(''PER'',''IRC_412594_RSS_JOB_TITLE'')||'' ''||IPC.JOB_TITLE ||'' <br>''|| fnd_message.get_string(''PER'',''IRC_412595_RSS_ORGANIZATION'')||'' ''||IPC.ORG_NAME||
1559 '' <br>''|| fnd_message.get_string(''PER'',''IRC_412596_RSS_DESCRIPTION'')||'' ''||IPC.BRIEF_DESCRIPTION description ,
1560 to_char( trunc(pra.date_start),''Dy, DD Mon YYYY'') pubDate,
1561 irc_utilities_pkg.getVacancyRunFunctionUrl('|| l_funcId||',
1562 pav.vacancy_id,
1563 pav.primary_posting_id,
1564 1) rlink
1565 FROM HR_LOOKUPS HRL,
1566 HR_LOOKUPS HL2,
1567 PER_ALL_VACANCIES PAV,
1568 PER_RECRUITMENT_ACTIVITIES PRA,
1569 PER_RECRUITMENT_ACTIVITY_FOR PRF,
1570 IRC_POSTING_CONTENTS_VL IPC,
1571 IRC_SEARCH_CRITERIA IVS,
1572 HR_LOCATIONS_ALL LOC,
1573 HR_LOOKUPS HLW,
1574 HR_LOOKUPS HLT,
1575 IRC_ALL_RECRUITING_SITES ias WHERE pav.status = ''APPROVED''
1576 AND PAV.VACANCY_ID = PRF.VACANCY_ID
1577 AND PRF.RECRUITMENT_ACTIVITY_ID = PRA.RECRUITMENT_ACTIVITY_ID
1578 AND PRA.POSTING_CONTENT_ID = IPC.POSTING_CONTENT_ID
1579 AND PAV.VACANCY_ID = IVS.OBJECT_ID
1580 AND IVS.OBJECT_TYPE = ''VACANCY''
1581 AND HRL.LOOKUP_TYPE(+) = ''IRC_PROFESSIONAL_AREA''
1582 AND IVS.PROFESSIONAL_AREA = HRL.LOOKUP_CODE(+)
1583 AND HL2.LOOKUP_TYPE(+) = ''IRC_EMP_CAT''
1584 AND IVS.EMPLOYMENT_CATEGORY = HL2.LOOKUP_CODE(+)
1585 AND HLW.LOOKUP_TYPE(+) = ''IRC_WORK_AT_HOME''
1586 AND IVS.WORK_AT_HOME = HLW.LOOKUP_CODE(+)
1587 AND HLT.LOOKUP_TYPE(+) = ''IRC_TRAVEL_PERCENTAGE''
1588 AND IVS.TRAVEL_PERCENTAGE = HLT.LOOKUP_CODE(+)
1589 AND sysdate BETWEEN PRA.date_start AND NVL(PRA.date_end,sysdate)
1590 AND loc.location_id(+)=pav.location_id
1591 AND ias.EXTERNAL = ''Y''
1592 AND
1593 (
1594 pav.date_from <= sysdate
1595 AND
1596 (
1597 (
1598 pav.date_to IS NOT NULL
1599 AND pav.date_to > = sysdate
1600 )
1601 OR
1602 (
1603 pav.date_to IS NULL
1604 )
1605 )
1606 )
1607 AND EXISTS
1608 (SELECT 1
1609 FROM per_vacancies vac1
1610 WHERE vac1.vacancy_id =pav.vacancy_id
1611 )
1612 AND pra.recruiting_site_id = ias.recruiting_site_id
1613 AND sysdate BETWEEN pra.date_start AND NVL (pra.date_end, sysdate)';
1614
1615 --adding where clause for keyword search
1616 if l_keyword is not null then
1617 query:=query|| ' AND contains(ipc.name,:' ||bindnum || ',1) > 0';
1618 bindvalues(bindnum):=l_keyword;
1619 bindnum:=bindnum+1;
1620 end if;
1621
1622 --adding where clause for Professional area
1623 if l_job is not null then
1624
1625 query:=query|| ' AND HRL.LOOKUP_CODE in (' ;
1626 i := 1;
1627 loop
1628 token := split_to_token(l_job,i);
1629 exit when token is null;
1630 if not firstToken then
1631 query:=query|| ',';
1632 end if;
1633 firstToken := false;
1634 query:=query|| ':' || bindnum;
1635 bindvalues(bindnum) := token;
1636 bindnum:=bindnum+1;
1637 i := i+1;
1638 end loop;
1639 query:=query|| ')';
1640
1641 end if;
1642
1643 --adding where clause for contractor and employee checkbox
1644 if l_contractor is not null and l_employee is not null then
1645
1646 if l_contractor ='Y' and l_employee ='Y' then
1647 query:=query|| ' and (ivs.contractor = ''Y'' or ivs.employee = ''Y'')';
1648
1649 elsif l_contractor ='Y' and l_employee ='N' then
1650 query:=query|| ' and (ivs.contractor = ''Y'')';
1651
1652 elsif l_contractor ='N' and l_employee ='Y' then
1653 query:=query|| ' and (ivs.employee = ''Y'')';
1654
1655 elsif l_contractor ='N' and l_employee ='N' then
1656 query:=query|| ' and (ivs.contractor = ''N'' and ivs.employee = ''N'')';
1657
1658 end if;
1659
1660 end if;
1661
1662 --adding whereclause for travel percentage
1663 if l_travelpercent is not null then
1664 query:=query||' AND (HLT.LOOKUP_CODE = :'||bindnum ||' OR IVS.TRAVEL_PERCENTAGE IS NULL)';
1665 bindvalues(bindnum):=l_travelpercent;
1666 bindnum:=bindnum+1;
1667 end if;
1668
1669 --adding where clause for date posted
1670 if l_dateposted is not null then
1671 query:=query|| ' AND ((trunc(sysdate) - pra.date_start ) <= to_number(:'|| bindnum|| ') )';
1672 bindvalues(bindnum):=l_dateposted ;
1673 bindnum:=bindnum+1;
1674 end if;
1675
1676 --adding where clause for employment status
1677 if l_employmentstatus is not null then
1678 if l_employmentstatus ='FULLTIME' then
1679 query:=query||' and (HL2.LOOKUP_CODE IN (''FULLTIME'',''EITHER'') OR IVS.EMPLOYMENT_CATEGORY IS NULL)';
1680 elsif l_employmentstatus ='PARTTIME' then
1681 query:=query||' and (HL2.LOOKUP_CODE IN (''PARTTIME'',''EITHER'') OR IVS.EMPLOYMENT_CATEGORY IS NULL)';
1682 elsif l_employmentstatus ='EITHER' then
1683 query:=query||' and (HL2.LOOKUP_CODE IN (''FULLTIME'',''PARTTIME'',''EITHER'') OR IVS.EMPLOYMENT_CATEGORY IS NULL)';
1684 end if;
1685 end if;
1686
1687 --adding where clause for work at home
1688 if l_workathome is not null and l_workathome <>'' then
1689 query:=query||' and (HLW.LOOKUP_CODE = :'||bindNum||' OR IVS.WORK_AT_HOME IS NULL)';
1690 bindvalues(bindnum):=l_workathome ;
1691 bindnum:=bindnum+1;
1692 end if;
1693
1694 --adding where clause for minimum salary and currency
1695 if l_min_salary is not null and l_currency is not null then
1696 query:=query|| ' AND ( (IVS.salary_currency = :'||bindnum;
1697 bindvalues(bindnum):=l_currency;
1698 bindnum:=bindnum+1;
1699
1700 query:=query|| ' AND IVS.max_salary >= to_number(:' || bindnum|| '))';
1701 bindvalues(bindnum):=l_min_salary;
1702 bindnum:=bindnum+1;
1703
1704 query:=query|| ' OR (IVS.salary_currency<>:'||bindnum || ' AND';
1705 bindvalues(bindnum):=l_currency;
1706 bindnum:=bindnum+1;
1707
1708 query:=query|| ' to_number(:'||bindnum || ') <= irc_seeker_vac_matching_pkg.convert_vacancy_amount';
1709 bindvalues(bindnum):=l_min_salary;
1710 bindnum:=bindnum+1;
1711
1712 query:=query|| ' (IVS.salary_currency,:'||bindnum ||',IVS.max_salary,sysdate';
1713 bindvalues(bindnum):=l_currency;
1714 bindnum:=bindnum+1;
1715
1716 query:=query|| ' ,PAV.BUSINESS_GROUP_ID,''P'' )))';
1717
1718 end if;
1719 if l_location is not null and l_location <> 'null' and l_location <> '' then
1720 query:= query||' and catsearch(loc.derived_locale,'''||l_location||''',null)>0';
1721 query:=query||' and loc.derived_locale is not null ';
1722 end if;
1723 if l_locid is null and l_drvdlocal is null then
1724 --adding where clause for location based search
1725 if l_lat is not null and l_long is not null and l_distance is not null then
1726
1727
1728 query:=query|| 'and ('||
1729 ' EXISTS '||
1730 '( '||
1731 'SELECT NULL '||
1732 'FROM irc_location_preferences ilp '||
1733 ',hr_locations_all loc1 '||
1734 'WHERE ilp.object_id = pav.vacancy_id '||
1735 'AND ilp.object_type = ''VACANCY'' '||
1736 'AND loc1.location_id = ilp.location_id '||
1737 'AND locator_within_distance (loc1.geometry '||
1738 ' ,mdsys.sdo_geometry (2001 '||
1739 ',8307 '||
1740 ' ,mdsys.sdo_point_type ('||l_long||
1741 ','||l_lat||
1742 ',NULL) '||
1743 ',NULL '||
1744 ',NULL) '||
1745 ',''distance= '||l_distance||',units=mile'') = ''TRUE'' '||
1746 'AND loc1.geometry IS NOT NULL '||
1747 ') '||
1748 'OR EXISTS '||
1749 '( '||
1750 'SELECT NULL '||
1751 'FROM hr_locations_all loc2 '||
1752 'WHERE loc2.location_id = pav.location_id '||
1753 'AND locator_within_distance (loc2.geometry '||
1754 ',mdsys.sdo_geometry (2001 '||
1755 ',8307 '||
1756 ',mdsys.sdo_point_type ('||l_long||
1757 ','||l_lat||
1758 ',NULL) '||
1759 ',NULL '||
1760 ',NULL) '||
1761 ',''distance= '||l_distance||',units=mile'') = ''TRUE'' '||
1762 'AND loc2.geometry IS NOT NULL '||
1763 ') '||
1764 ') ';
1765
1766 elsif ((l_location is null or l_lat is null or l_long is null) and l_distance is not null)
1767 or ((l_location is not null and (l_lat is null or l_long is null)) and l_distance is not null)
1768 or ((l_location is null and (l_lat is not null or l_long is not null)) and l_distance is not null) then
1769 query:='SELECT fnd_message.get_string(''PER'',''IRC_412597_RSS_CRITERIA_ERROR'') title ,
1770 fnd_message.get_string(''PER'',''IRC_412011_BAD_LOCATION'') description ,
1771 to_char(trunc(sysdate),''Dy, DD Mon YYYY'') pubDate,
1772 ''/OA_HTML/IrcVisitor.jsp'' rlink from dual';
1773 ctx:= dbms_xmlquery.newContext(query);
1774 dbms_xmlquery.setTagCase(ctx,dbms_xmlquery.LOWER_CASE);
1775 dbms_xmlquery.setEncodingTag(ctx,'UTF8');
1776 xml:=dbms_xmlquery.getXML(ctx);
1777 return (xml);
1778
1779 elsif (l_location is not null and l_lat is not null and l_long is not null and l_distance is null) then
1780 query:='SELECT fnd_message.get_string(''PER'',''IRC_412597_RSS_CRITERIA_ERROR'') title ,
1781 fnd_message.get_string(''PER'',''IRC_412164_NO_DISTANCE'') description ,
1782 to_char(trunc(sysdate),''Dy, DD Mon YYYY'') pubDate,
1783 ''/OA_HTML/IrcVisitor.jsp'' rlink from dual';
1784 ctx:= dbms_xmlquery.newContext(query);
1785 dbms_xmlquery.setTagCase(ctx,dbms_xmlquery.LOWER_CASE);
1786 dbms_xmlquery.setEncodingTag(ctx,'UTF8');
1787 xml:=dbms_xmlquery.getXML(ctx);
1788 return (xml);
1789
1790 end if;
1791 elsif l_locid is not null and l_drvdlocal is null then
1792 query:=query|| ' and loc.location_id=:'||bindnum;
1793 bindvalues(bindnum):=to_number(l_locid);
1794 bindnum:=bindnum+1;
1795 elsif l_locid is null and l_drvdlocal is not null then
1796 query:=query||'and '||
1797 '( ' ||
1798 'exists '||
1799 '( '||
1800 'select null ' ||
1801 'from irc_location_preferences ilp, ' ||
1802 'hr_locations_all loc1 '||
1803 'where ilp.object_id = pav.vacancy_id '||
1804 'and ilp.object_type = ''VACANCY'' '||
1805 'and loc1.location_id = ilp.location_id ' ||
1806 'and catsearch(loc1.derived_locale,:'|| bindnum||',NULL) > 0 '||
1807 'and loc1.derived_locale is not null ' ||
1808 ') '||
1809 'or exists '||
1810 '( '||
1811 'select null '||
1812 'from hr_locations_all loc2 '||
1813 'where loc2.location_id = loc.location_id '||
1814 'and catsearch(loc2.derived_locale,:'||(bindnum+1)||',NULL) > 0 '||
1815 'and loc2.derived_locale is not null ' ||
1816 ') '||
1817 ') ';
1818 bindvalues(bindnum):=l_drvdlocal;
1819 bindnum:=bindnum+1;
1820 bindvalues(bindnum):=l_drvdlocal;
1821 bindnum:=bindnum+1;
1822 end if;
1823 --flex
1824 if l_attribute_category is not null then
1825 query := query || ' AND IVS.ATTRIBUTE_CATEGORY = :' ||bindnum ;
1826 bindvalues(bindnum):=l_attribute_category;
1827 bindnum:=bindnum+1;
1828
1829 end if;
1830 if l_attribute1 is not null then query := query || ' AND lower(IVS.attribute1) like lower(:' ||bindnum||')' ; bindvalues(bindnum):=l_attribute1; bindnum:=bindnum+1; end if;
1831 if l_attribute2 is not null then query := query || ' AND lower(IVS.attribute2) like lower(:' ||bindnum||')' ; bindvalues(bindnum):=l_attribute2; bindnum:=bindnum+1; end if;
1832 if l_attribute3 is not null then query := query || ' AND lower(IVS.attribute3) like lower(:' ||bindnum||')' ; bindvalues(bindnum):=l_attribute3; bindnum:=bindnum+1; end if;
1833 if l_attribute4 is not null then query := query || ' AND lower(IVS.attribute4) like lower(:' ||bindnum||')' ; bindvalues(bindnum):=l_attribute4; bindnum:=bindnum+1; end if;
1834 if l_attribute5 is not null then query := query || ' AND lower(IVS.attribute5) like lower(:' ||bindnum||')' ; bindvalues(bindnum):=l_attribute5; bindnum:=bindnum+1; end if;
1835 if l_attribute6 is not null then query := query || ' AND lower(IVS.attribute6) like lower(:' ||bindnum||')' ; bindvalues(bindnum):=l_attribute6; bindnum:=bindnum+1; end if;
1836 if l_attribute7 is not null then query := query || ' AND lower(IVS.attribute7) like lower(:' ||bindnum||')' ; bindvalues(bindnum):=l_attribute7; bindnum:=bindnum+1; end if;
1837 if l_attribute8 is not null then query := query || ' AND lower(IVS.attribute8) like lower(:' ||bindnum||')' ; bindvalues(bindnum):=l_attribute8; bindnum:=bindnum+1; end if;
1838 if l_attribute9 is not null then query := query || ' AND lower(IVS.attribute9) like lower(:' ||bindnum||')' ; bindvalues(bindnum):=l_attribute9; bindnum:=bindnum+1; end if;
1839 if l_attribute10 is not null then query := query || ' AND lower(IVS.attribute10) like lower(:' ||bindnum||')' ; bindvalues(bindnum):=l_attribute10; bindnum:=bindnum+1; end if;
1840 if l_attribute11 is not null then query := query || ' AND lower(IVS.attribute11) like lower(:' ||bindnum||')' ; bindvalues(bindnum):=l_attribute11; bindnum:=bindnum+1; end if;
1841 if l_attribute12 is not null then query := query || ' AND lower(IVS.attribute12) like lower(:' ||bindnum||')' ; bindvalues(bindnum):=l_attribute12; bindnum:=bindnum+1; end if;
1842 if l_attribute13 is not null then query := query || ' AND lower(IVS.attribute13) like lower(:' ||bindnum||')' ; bindvalues(bindnum):=l_attribute13; bindnum:=bindnum+1; end if;
1843 if l_attribute14 is not null then query := query || ' AND lower(IVS.attribute14) like lower(:' ||bindnum||')' ; bindvalues(bindnum):=l_attribute14; bindnum:=bindnum+1; end if;
1844 if l_attribute15 is not null then query := query || ' AND lower(IVS.attribute15) like lower(:' ||bindnum||')' ; bindvalues(bindnum):=l_attribute15; bindnum:=bindnum+1; end if;
1845 if l_attribute16 is not null then query := query || ' AND lower(IVS.attribute16) like lower(:' ||bindnum||')' ; bindvalues(bindnum):=l_attribute16; bindnum:=bindnum+1; end if;
1846 if l_attribute17 is not null then query := query || ' AND lower(IVS.attribute17) like lower(:' ||bindnum||')' ; bindvalues(bindnum):=l_attribute17; bindnum:=bindnum+1; end if;
1847 if l_attribute18 is not null then query := query || ' AND lower(IVS.attribute18) like lower(:' ||bindnum||')' ; bindvalues(bindnum):=l_attribute18; bindnum:=bindnum+1; end if;
1848 if l_attribute19 is not null then query := query || ' AND lower(IVS.attribute19) like lower(:' ||bindnum||')' ; bindvalues(bindnum):=l_attribute19; bindnum:=bindnum+1; end if;
1849 if l_attribute20 is not null then query := query || ' AND lower(IVS.attribute20) like lower(:' ||bindnum||')' ; bindvalues(bindnum):=l_attribute20; bindnum:=bindnum+1; end if;
1850 if l_attribute21 is not null then query := query || ' AND lower(IVS.attribute21) like lower(:' ||bindnum||')' ; bindvalues(bindnum):=l_attribute21; bindnum:=bindnum+1; end if;
1851 if l_attribute22 is not null then query := query || ' AND lower(IVS.attribute22) like lower(:' ||bindnum||')' ; bindvalues(bindnum):=l_attribute22; bindnum:=bindnum+1; end if;
1852 if l_attribute23 is not null then query := query || ' AND lower(IVS.attribute23) like lower(:' ||bindnum||')' ; bindvalues(bindnum):=l_attribute23; bindnum:=bindnum+1; end if;
1853 if l_attribute24 is not null then query := query || ' AND lower(IVS.attribute24) like lower(:' ||bindnum||')' ; bindvalues(bindnum):=l_attribute24; bindnum:=bindnum+1; end if;
1854 if l_attribute25 is not null then query := query || ' AND lower(IVS.attribute25) like lower(:' ||bindnum||')' ; bindvalues(bindnum):=l_attribute25; bindnum:=bindnum+1; end if;
1855 if l_attribute26 is not null then query := query || ' AND lower(IVS.attribute26) like lower(:' ||bindnum||')' ; bindvalues(bindnum):=l_attribute26; bindnum:=bindnum+1; end if;
1856 if l_attribute27 is not null then query := query || ' AND lower(IVS.attribute27) like lower(:' ||bindnum||')' ; bindvalues(bindnum):=l_attribute27; bindnum:=bindnum+1; end if;
1857 if l_attribute28 is not null then query := query || ' AND lower(IVS.attribute28) like lower(:' ||bindnum||')' ; bindvalues(bindnum):=l_attribute28; bindnum:=bindnum+1; end if;
1858 if l_attribute29 is not null then query := query || ' AND lower(IVS.attribute29) like lower(:' ||bindnum||')' ; bindvalues(bindnum):=l_attribute29; bindnum:=bindnum+1; end if;
1859 if l_attribute30 is not null then query := query || ' AND lower(IVS.attribute30) like lower(:' ||bindnum||')' ; bindvalues(bindnum):=l_attribute30; bindnum:=bindnum+1; end if;
1860
1861
1862 --flex end
1863 query:=query|| ' ORDER BY pra.date_start DESC';
1864
1865 else
1866 query:='SELECT fnd_message.get_string(''PER'',''IRC_412597_RSS_CRITERIA_ERROR'') title ,
1867 fnd_message.get_string(''PER'',''PER_34296_DIAG_NO_BLANKL_QUERY'') description ,
1868 to_char(trunc(sysdate),''Dy, DD Mon YYYY'') pubDate,
1869 ''/OA_HTML/IrcVisitor.jsp'' rlink from dual';
1870 ctx:= dbms_xmlquery.newContext(query);
1871 dbms_xmlquery.setTagCase(ctx,dbms_xmlquery.LOWER_CASE);
1872 dbms_xmlquery.setEncodingTag(ctx,'UTF8');
1873 xml:=dbms_xmlquery.getXML(ctx);
1874 return (xml);
1875 end if;
1876
1877 if bindnum=1 then
1878
1879 return (xml);
1880
1881 end if;
1882
1883 ctx:= dbms_xmlquery.newContext(query);
1884 dbms_xmlquery.setTagCase(ctx,dbms_xmlquery.LOWER_CASE);
1885 dbms_xmlquery.setEncodingTag(ctx,'UTF8');
1886
1887 for i in 1 .. bindvalues.count loop
1888 dbms_xmlquery.setBindValue(ctx,i,bindvalues(i));
1889 end loop;
1890
1891 xml:=dbms_xmlquery.getXML(ctx);
1892
1893 return (xml);
1894 end getJobSearchItems;
1895
1896 --
1897 -- ----------------------------------------------------------------------------
1898 -- |---------------------< getJobSearchChannel >-------------------------------|
1899 -- ----------------------------------------------------------------------------
1900 --
1901 function getJobSearchChannel(langcode in varchar2 default null,
1902 enterprise in varchar2 default null) return clob is
1903 xml clob;
1904 query varchar2(1000);
1905 ctx DBMS_XMLQUERY.ctxType;
1906 begin
1907 if enterprise is not null then
1908 hr_multi_tenancy_pkg.set_context_for_enterprise(enterprise);
1909 end if;
1910 query:=
1911 'SELECT fnd_message.get_string(''PER'',''IRC_412592_RSS_CHANNEL_TITLE'') title, fnd_message.get_string(''PER'',''IRC_412593_RSS_CHANNEL_DESC'') description , to_char(trunc(sysdate),''Dy, DD Mon YYYY'') pubDate,
1912 ''/OA_HTML/IrcVisitor.jsp'' rlink
1913 FROM dual';
1914
1915 ctx:= dbms_xmlquery.newContext(query);
1916 dbms_xmlquery.setTagCase(ctx,dbms_xmlquery.LOWER_CASE);
1917 dbms_xmlquery.setEncodingTag(ctx,'UTF8');
1918 xml:=dbms_xmlquery.getXML(ctx);
1919
1920 return (xml);
1921
1922 end getJobSearchChannel;
1923
1924 -- ----------------------------------------------------------------------------
1925 -- |-----------------------< GET_RATE_SQL >--------------------------|
1926 -- ----------------------------------------------------------------------------
1927
1928 FUNCTION GET_RATE_SQL
1929 (l_from_currency IN varchar2 ,
1930 l_to_currency IN varchar2 ,
1931 l_exchange_date IN date )
1932 return number is
1933 conversion_rate Number;
1934 exchange_date date := l_exchange_date;
1935 to_currency varchar2(240) := l_to_currency;
1936 l_profile_date varchar2(240);
1937 cursor csr_corp_currency is
1938 select CURRENCY_CODE
1939 from per_business_groups
1940 where BUSINESS_GROUP_ID = fnd_profile.value('IRC_CORPORATE_BUSINESS_GROUP');
1941 begin
1942 --
1943 if(to_currency is null) then
1944 open csr_corp_currency;
1945 fetch csr_corp_currency into to_currency;
1946 if csr_corp_currency%notfound then
1947 to_currency := '';
1948 end if;
1949 end if;
1950 --
1951 if(exchange_date is null) then
1952 select nvl(fnd_profile.value('IRC_CURR_CONV_DATE'),'') into l_profile_date
1953 from dual;
1954 exchange_date := get_exchange_date(l_profile_date,to_currency);
1955 end if;
1956
1957 conversion_rate := hr_currency_pkg.get_rate_sql (
1958 p_from_currency => l_from_currency,
1959 p_to_currency => to_currency,
1960 p_conversion_date => exchange_date,
1961 p_rate_type => 'Corporate');
1962 return conversion_rate;
1963 end get_rate_sql;
1964 --
1965 -- ----------------------------------------------------------------------------
1966 -- |-----------------------< get_exchange_date >----------------------------|
1967 -- ----------------------------------------------------------------------------
1968 function get_exchange_date(profile_date in varchar2,
1969 l_from_currency in varchar2,
1970 effective_date in date default sysdate)
1971 return date
1972 is
1973 l_user_date date;
1974 l_to_currency varchar2(240);
1975 cursor csr_curr_code is
1976 select CURRENCY_CODE
1977 from per_business_groups
1978 where BUSINESS_GROUP_ID = fnd_profile.value('IRC_CORPORATE_BUSINESS_GROUP');
1979 begin
1980 l_user_date := to_date(profile_date||'-'||to_char(effective_date,'YYYY'),'dd-MM-YYYY');
1981 if(l_user_date > effective_date) then
1982 l_user_date := to_date(profile_date||'-'||to_char(to_number(to_char(effective_date,'YYYY'))-1),'dd-MM-YYYY');
1983 end if;
1984 return l_user_date;
1985 exception
1986 when others then
1987 OPEN csr_curr_code;
1988 FETCH csr_curr_code INTO l_to_currency;
1989 if csr_curr_code%notfound then
1990 l_to_currency :='';
1991 end if;
1992 CLOSE csr_curr_code;
1993 --
1994 --
1995 select max(conversion_date) into l_user_date
1996 from GL_DAILY_RATES
1997 where from_currency = l_from_currency
1998 and to_currency = l_to_currency
1999 and conversion_type = 'Corporate'
2000 and conversion_date <= effective_date
2001 order by conversion_date desc;
2002 if(l_user_date is null) then
2003 return sysdate;
2004 end if;
2005 return l_user_date;
2006 --
2007 end get_exchange_date;
2008 --
2009 -- ----------------------------------------------------------------------------
2010 -- |-----------------------< is_salary_basis_required >----------------------|
2011 -- ----------------------------------------------------------------------------
2012
2013 FUNCTION is_salary_basis_required
2014 (
2015 p_business_group_id IN NUMBER,
2016 p_organization_id IN NUMBER,
2017 p_position_id IN NUMBER,
2018 p_grade_id IN NUMBER,
2019 p_job_id IN NUMBER
2020 )
2021 RETURN VARCHAR2
2022 IS
2023 l_job_dff_column_name VARCHAR2(240);
2024 l_grade_dff_column_name VARCHAR2(240);
2025 l_grade_name VARCHAR2(240);
2026 l_position_name VARCHAR2(240);
2027 l_organization_name VARCHAR2(240);
2028 l_job_name VARCHAR2(240);
2029 l_business_group_name VARCHAR2(240);
2030 CURSOR csr_select_column_names(dff_name VARCHAR2)
2031 IS
2032 SELECT APPLICATION_COLUMN_NAME
2033 FROM FND_DESCR_FLEX_COLUMN_USAGES
2034 WHERE DESCRIPTIVE_FLEXFIELD_NAME = (dff_name)
2035 and DESCRIPTIVE_FLEX_CONTEXT_CODE = 'Global Data Elements'
2036 AND END_USER_COLUMN_NAME = fnd_profile.value('IRC_OFFER_SAL_FLEX_SEGMENT');
2037
2038 return_val VARCHAR2(24);
2039 query_stmt VARCHAR2(2000);
2040 l_column_name VARCHAR2(240);
2041 --
2042 BEGIN
2043 --
2044 IF(p_grade_id IS NOT NULL) THEN
2045 SELECT name
2046 INTO l_grade_name
2047 FROM per_grades
2048 WHERE grade_id = p_grade_id;
2049
2050 OPEN csr_select_column_names('PER_GRADES');
2051 FETCH csr_select_column_names
2052 INTO l_column_name;
2053
2054 IF csr_select_column_names%found THEN
2055 query_stmt := 'select '
2056 || l_column_name
2057 || ' from per_grades where grade_id = :grade_id';
2058 EXECUTE immediate query_stmt INTO return_val USING p_grade_id;
2059 END IF;
2060 CLOSE csr_select_column_names;
2061 IF (return_val IS NOT NULL) THEN
2062 RETURN return_val;
2063 END IF;
2064 END IF;
2065 IF(p_position_id IS NOT NULL) THEN
2066 SELECT name
2067 INTO l_position_name
2068 FROM HR_ALL_POSITIONS_F
2069 WHERE position_id = p_position_id
2070 AND TRUNC(sysdate) between effective_start_date and effective_end_date;
2071
2072 OPEN csr_select_column_names('PER_POSITIONS');
2073 FETCH csr_select_column_names
2074 INTO l_column_name;
2075
2076 IF csr_select_column_names%found THEN
2077 query_stmt := 'select '
2078 || l_column_name
2079 || ' from hr_all_positions_f where position_id = '
2080 || p_position_id
2081 || ' AND TRUNC(sysdate) between effective_start_date and effective_end_date';
2082 EXECUTE immediate query_stmt INTO return_val;
2083 END IF;
2084 CLOSE csr_select_column_names;
2085 IF (return_val IS NOT NULL) THEN
2086 RETURN return_val;
2087 END IF;
2088 END IF;
2089 IF(p_job_id IS NOT NULL) THEN
2090 SELECT name
2091 INTO l_job_name
2092 FROM per_jobs
2093 WHERE job_id = p_job_id;
2094
2095 OPEN csr_select_column_names('PER_JOBS');
2096 FETCH csr_select_column_names
2097 INTO l_column_name;
2098
2099 IF csr_select_column_names%found THEN
2100 query_stmt := 'select '
2101 || l_column_name
2102 || ' from per_jobs where job_id = :job_id';
2103 EXECUTE immediate query_stmt INTO return_val USING p_job_id;
2104 END IF;
2105 CLOSE csr_select_column_names;
2106 IF (return_val IS NOT NULL) THEN
2107 RETURN return_val;
2108 END IF;
2109 END IF;
2110 IF(p_organization_id IS NOT NULL) THEN
2111 SELECT name
2112 INTO l_organization_name
2113 FROM HR_ALL_ORGANIZATION_UNITS
2114 WHERE organization_id = p_organization_id;
2115
2116 OPEN csr_select_column_names('PER_ORGANIZATION_UNITS');
2117 FETCH csr_select_column_names
2118 INTO l_column_name;
2119
2120 IF csr_select_column_names%found THEN
2121 query_stmt := 'select '
2122 || l_column_name
2123 || ' from HR_ALL_ORGANIZATION_UNITS where organization_id = '
2124 ||p_organization_id;
2125 EXECUTE immediate query_stmt INTO return_val;
2126 END IF;
2127 CLOSE csr_select_column_names;
2128 IF (return_val IS NOT NULL) THEN
2129 RETURN return_val;
2130 END IF;
2131 END IF;
2132 IF(p_business_group_id IS NOT NULL) THEN
2133 SELECT name
2134 INTO l_business_group_name
2135 FROM HR_ALL_ORGANIZATION_UNITS
2136 WHERE organization_id = p_business_group_id;
2137
2138 OPEN csr_select_column_names('PER_ORGANIZATION_UNITS');
2139 FETCH csr_select_column_names
2140 INTO l_column_name;
2141
2142 IF csr_select_column_names%found THEN
2143 query_stmt := 'select '
2144 || l_column_name
2145 || ' from HR_ALL_ORGANIZATION_UNITS where organization_id = '
2146 ||p_organization_id;
2147 EXECUTE immediate query_stmt INTO return_val;
2148 END IF;
2149 CLOSE csr_select_column_names;
2150 IF (return_val IS NOT NULL) THEN
2151 RETURN return_val;
2152 END IF;
2153 END IF;
2154 RETURN return_val;
2155 END is_salary_basis_required;
2156
2157
2158 FUNCTION is_proposed_salary_required
2159 (
2160 p_salary_basis_id IN NUMBER
2161 )
2162 RETURN VARCHAR2
2163 is
2164 l_salary_basis_name VARCHAR2(240);
2165 return_val VARCHAR2(24);
2166 query_stmt VARCHAR2(2000);
2167 l_column_name VARCHAR2(240);
2168
2169 CURSOR csr_select_column_names
2170 IS
2171 SELECT APPLICATION_COLUMN_NAME
2172 FROM FND_DESCR_FLEX_COLUMN_USAGES
2173 WHERE DESCRIPTIVE_FLEXFIELD_NAME = 'PER_PAY_BASES'
2174 and DESCRIPTIVE_FLEX_CONTEXT_CODE = 'Global Data Elements'
2175 AND END_USER_COLUMN_NAME = fnd_profile.value('IRC_OFFER_SAL_FLEX_SEGMENT');
2176 Begin
2177 IF(p_salary_basis_id IS NOT NULL) THEN
2178 SELECT name
2179 INTO l_salary_basis_name
2180 FROM per_pay_bases
2181 WHERE PAY_BASIS_ID = p_salary_basis_id;
2182
2183 OPEN csr_select_column_names;
2184 FETCH csr_select_column_names
2185 INTO l_column_name;
2186
2187 IF csr_select_column_names%found THEN
2188 query_stmt := 'select '
2189 || l_column_name
2190 || ' from per_pay_bases where PAY_BASIS_ID = '
2191 ||p_salary_basis_id;
2192 EXECUTE immediate query_stmt INTO return_val;
2193 END IF;
2194 CLOSE csr_select_column_names;
2195 IF (return_val IS NOT NULL) THEN
2196 RETURN return_val;
2197 END IF;
2198 END IF;
2199 RETURN return_val;
2200 end is_proposed_salary_required;
2201 --
2202 -- ----------------------------------------------------------------------------
2203 -- |---------------------------< GET_PERSON_TYPE >----------------------------|
2204 -- ----------------------------------------------------------------------------
2205
2206 FUNCTION get_person_type (p_party_id number
2207 ,p_eff_date date )
2208 RETURN VARCHAR2
2209 IS
2210 --
2211 l_person_type varchar2(400);
2212 --
2213 CURSOR csr_emp_person_types
2214 IS
2215 SELECT typ.system_person_type
2216 FROM per_person_types typ
2217 ,per_person_type_usages_f ptu
2218 ,PER_ALL_PEOPLE_F ppf
2219 WHERE ppf.party_id = p_party_id
2220 AND p_eff_date BETWEEN ppf.effective_start_date and ppf.effective_end_date
2221 AND ppf.person_id = ptu.person_id
2222 AND typ.system_person_type IN ('EMP','EX_EMP')
2223 AND typ.person_type_id = ptu.person_type_id
2224 AND p_eff_date BETWEEN ptu.effective_start_date
2225 AND ptu.effective_end_date
2226 order by typ.system_person_type ASC, ptu.effective_start_date DESC;
2227 --
2228 BEGIN
2229 --
2230 open csr_emp_person_types;
2231 fetch csr_emp_person_types into l_person_type;
2232 if csr_emp_person_types%notfound then
2233 l_person_type:='';
2234 end if;
2235 close csr_emp_person_types;
2236 --
2237 RETURN l_person_type;
2238 --
2239 END get_person_type;
2240 --
2241 -- ----------------------------------------------------------------------------
2242 -- |---------------------------< get_rehire_recommendation >------------------|
2243 -- ----------------------------------------------------------------------------
2244 FUNCTION get_rehire_recommendation(p_party_id in number
2245 ,p_eff_date in date )
2246 RETURN VARCHAR2
2247 IS
2248 CURSOR csr_person_types IS
2249 SELECT typ.system_person_type,
2250 ppf.rehire_recommendation,
2251 hl.Meaning
2252 FROM per_person_types typ
2253 ,per_person_type_usages_f ptu
2254 ,per_all_people_f ppf
2255 ,hr_lookups hl
2256 WHERE ppf.party_id = p_party_id
2257 AND p_eff_date BETWEEN ppf.effective_start_date and ppf.effective_end_date
2258 AND ppf.person_id = ptu.person_id
2259 AND typ.system_person_type = 'EX_EMP'
2260 AND typ.person_type_id = ptu.person_type_id
2261 AND p_eff_date BETWEEN ptu.effective_start_date AND ptu.effective_end_date
2262 AND hl.lookup_type = 'IRC_REHIRE_RECOMMENDATION'
2263 AND nvl(ppf.rehire_recommendation,'N') = hl.lookup_code
2264 AND ppf.effective_start_date = (SELECT max(effective_start_date)
2265 FROM per_all_people_f ppf1
2266 WHERE ppf1.party_id = ppf.party_id
2267 AND effective_start_date <= p_eff_date
2268 )
2269 AND NOT EXISTS (SELECT 1
2270 FROM per_person_types typ1
2271 ,per_all_people_f ppf1
2272 WHERE ppf1.party_id = ppf.party_id
2273 AND typ1.person_type_id = ppf1.person_type_id
2274 AND typ1.system_person_type = 'EMP'
2275 AND p_eff_date BETWEEN ppf1.effective_start_date AND ppf1.effective_end_date
2276 )
2277 ORDER BY typ.system_person_type ASC, ptu.effective_start_date DESC;
2278 --
2279 l_person_typ per_person_types.system_person_type%type;
2280 l_rehire_recommendation per_all_people_f.rehire_recommendation%type;
2281 l_rehire_recom_code per_all_people_f.rehire_recommendation%type;
2282 --
2283 BEGIN
2284 --
2285 open csr_person_types;
2286 fetch csr_person_types
2287 into l_person_typ
2288 ,l_rehire_recom_code
2289 ,l_rehire_recommendation;
2290 --
2291 if l_rehire_recom_code is null then
2292 l_rehire_recommendation := null;
2293 end if;
2294 --
2295 if csr_person_types%notfound then
2296 select meaning into l_rehire_recommendation
2297 from hr_lookups
2298 where lookup_type = 'IRC_REHIRE_RECOMMENDATION'
2299 and lookup_code = 'NOT_APPLICABLE';
2300 end if;
2301 close csr_person_types;
2302 --
2303 return l_rehire_recommendation;
2304 --
2305 END get_rehire_recommendation;
2306 --
2307 -- ----------------------------------------------------------------------------
2308 -- |---------------------------< get_rehire_recom_code >----------------------|
2309 -- ----------------------------------------------------------------------------
2310
2311 FUNCTION get_rehire_recom_code(p_party_id in number
2312 ,p_eff_date in date )
2313 RETURN VARCHAR2
2314 IS
2315 CURSOR csr_person_types IS
2316 SELECT typ.system_person_type,
2317 ppf.rehire_recommendation
2318 FROM per_person_types typ
2319 ,per_person_type_usages_f ptu
2320 ,per_all_people_f ppf
2321 WHERE ppf.party_id = p_party_id
2322 AND p_eff_date BETWEEN ppf.effective_start_date and ppf.effective_end_date
2323 AND ppf.person_id = ptu.person_id
2324 AND typ.system_person_type = 'EX_EMP'
2325 AND typ.person_type_id = ptu.person_type_id
2326 AND p_eff_date BETWEEN ptu.effective_start_date AND ptu.effective_end_date
2327 AND ppf.effective_start_date = (SELECT max(effective_start_date)
2328 FROM per_all_people_f ppf1
2329 WHERE ppf1.party_id = ppf.party_id
2330 AND effective_start_date <= p_eff_date
2331 )
2332 AND NOT EXISTS (SELECT 1
2333 FROM per_person_types typ1
2334 ,per_all_people_f ppf1
2335 WHERE ppf1.party_id = ppf.party_id
2336 AND typ1.person_type_id = ppf1.person_type_id
2337 AND typ1.system_person_type = 'EMP'
2338 AND p_eff_date BETWEEN ppf1.effective_start_date AND ppf1.effective_end_date
2339 )
2340 ORDER BY typ.system_person_type ASC, ptu.effective_start_date DESC;
2341 --
2342 l_person_typ per_person_types.system_person_type%type;
2343 l_rehire_recommendation per_all_people_f.rehire_recommendation%type;
2344 --
2345 BEGIN
2346 --
2347 open csr_person_types;
2348 fetch csr_person_types
2349 into l_person_typ
2350 ,l_rehire_recommendation;
2351 if l_rehire_recommendation is null then
2352 l_rehire_recommendation := null;
2353 end if;
2354 --
2355 if csr_person_types%notfound then
2356 select lookup_code into l_rehire_recommendation
2357 from hr_lookups
2358 where lookup_type = 'IRC_REHIRE_RECOMMENDATION'
2359 and lookup_code = 'NOT_APPLICABLE';
2360 end if;
2361 --
2362 close csr_person_types;
2363 --
2364 return l_rehire_recommendation;
2365 --
2366 END get_rehire_recom_code;
2367 --
2368 function getMobileNumber(pPersonIdIn in number) return varchar2 as
2369 l_number varchar2(500);
2370 begin
2371
2372 SELECT phn.phone_number || nvl2(hlk.meaning, '(' || hlk.meaning || ')', null)
2373 into l_number
2374 FROM per_phones phn, hr_lookups hlk
2375 WHERE phn.parent_id = pPersonIdIn
2376 AND phn.parent_table = 'PER_ALL_PEOPLE_F'
2377 AND trunc(SYSDATE) BETWEEN phn.date_from (+)
2378 AND nvl (phn.date_to , trunc(SYSDATE))
2379 AND phn.validity = hlk.lookup_code (+)
2380 AND hlk.lookup_type (+) = 'IRC_CONTACT_TIMES'
2381 AND phn.phone_type = 'M'
2382 and phn.last_update_date = (select max(last_update_date)
2383 from per_phones phn2
2384 where phn.parent_id = phn2.parent_id
2385 and phn2.phone_type = 'M'
2386 and phn2.parent_table = 'PER_ALL_PEOPLE_F'
2387 )
2388 and rownum <2;
2389 return l_number;
2390 exception
2391 when others then
2392 return null;
2393 end getMobileNumber;
2394
2395 function is_supervisor(p_person_id in number, p_vacancy_id in number)
2396 return varchar2
2397 is
2398 l_manager_id per_all_assignments_f.supervisor_id%type;
2399 l_supervisor varchar2(5) := 'N';
2400
2401 -- Cursor to fetch supervisors of an applicant
2402 cursor c_supervisor_id(c_managerId in number) is
2403 select distinct PERSON_ID
2404 from per_all_assignments_f paf
2405 start with paf.person_id = c_managerId
2406 and sysdate between paf.effective_start_date and paf.effective_end_date
2407 and assignment_type IN ('E','A')
2408 connect by prior paf.supervisor_id = paf.person_id ;
2409
2410 -- Cursor to fetch manager of the vacancy
2411 cursor c_manager(p_person_id in number, p_vacancy_id in number) is
2412 select pav.manager_id
2413 from per_all_vacancies pav
2414 where pav.vacancy_id = p_vacancy_id ;
2415
2416 begin
2417 open c_manager(p_person_id, p_vacancy_id);
2418 fetch c_manager into l_manager_id;
2419 close c_manager;
2420 for v_supervisor_id in c_supervisor_id(l_manager_id) loop
2421 if v_supervisor_id.PERSON_ID = p_person_id then
2422 l_supervisor := 'Y';
2423 end if;
2424 end loop;
2425 return l_supervisor;
2426 exception
2427 when others then
2428 if c_supervisor_id%isopen then
2429 close c_supervisor_id;
2430 end if;
2431 l_supervisor := 'E';
2432 return l_supervisor;
2433 end is_supervisor;
2434
2435 function get_Appl_Doc_Id(p_person_id in number default -1,
2436 p_party_id in number default -1,
2437 p_assignment_id in number)
2438 return number
2439 is
2440 l_doc_id number;
2441 cursor c_doc_Id(c_person_id in number, c_assignment_id in number) is
2442 select nvl(
2443 (SELECT max (idoc3.document_id)
2444 FROM irc_documents idoc3
2445 WHERE idoc3.person_id = c_person_id
2446 AND idoc3.type IN ('RESUME','AUTO_RESUME')
2447 AND
2448 (
2449 SELECT min (ias3.status_change_date) + (5 / 86400)
2450 FROM irc_assignment_statuses ias3
2451 WHERE ias3.assignment_id = c_assignment_Id
2452 ) BETWEEN idoc3.creation_date
2453 AND nvl2 (idoc3.end_date
2454 ,idoc3.last_update_date - (5 / 86400)
2455 ,sysdate)),-1) from dual;
2456
2457 cursor c_doc_id_party(c_party_id in number,c_assignment_id in number) is
2458 select nvl(
2459 (SELECT max (idoc3.document_id)
2460 FROM irc_documents idoc3
2461 WHERE idoc3.party_id = c_party_id
2462 AND idoc3.type IN ('RESUME','AUTO_RESUME')
2463 AND
2464 (
2465 SELECT min (ias3.status_change_date) + (5 / 86400)
2466 FROM irc_assignment_statuses ias3
2467 WHERE ias3.assignment_id = c_assignment_Id
2468 ) BETWEEN idoc3.creation_date
2469 AND nvl2 (idoc3.end_date
2470 ,idoc3.last_update_date - (5 / 86400)
2471 ,sysdate)),-1) from dual;
2472
2473 begin
2474
2475 if p_party_id=-1 then
2476 open c_doc_Id(p_person_id,p_assignment_id);
2477 fetch c_doc_Id into l_doc_id;
2478 close c_doc_Id;
2479 else
2480 open c_doc_id_party(p_party_id,p_assignment_id);
2481 fetch c_doc_id_party into l_doc_id;
2482 close c_doc_id_party;
2483 end if;
2484 return l_doc_id;
2485 end get_Appl_Doc_Id;
2486
2487 function get_Appl_Doc_Name(p_doc_id in varchar2)
2488 return varchar2
2489 is
2490 l_doc_name varchar2(100);
2491 begin
2492
2493 select nvl((select file_name from irc_documents
2494 where document_id=p_doc_id),-1) into l_doc_name from dual;
2495
2496
2497 return l_doc_name;
2498 end get_Appl_Doc_Name;
2499
2500 END IRC_UTILITIES_PKG;
[Home] [Help]