1 PACKAGE BODY IRC_UTILITIES_PKG AS
2 /* $Header: irutil.pkb 120.3.12010000.12 2008/10/31 13:57:02 vmummidi 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 ppe.person_id = ppf.person_id
166 ORDER BY NVL(ppe.end_date,HR_GENERAL.end_of_time) DESC
167 ,NVL(ppe.start_date,HR_GENERAL.start_of_time) DESC;
168 BEGIN
169 -- MAIN FUNCTION LOGIC
170 if(nvl(g_prev_emp_party_id,-1)<>p_party_id) then
171 g_prev_emp_party_id:=p_party_id;
172 OPEN c_current_employers;
173 --Get an instance of current employer (not concerned if multiple)
174 FETCH c_current_employers INTO g_prev_emp;
175 if c_current_employers%notfound then
176 g_prev_emp:='';
177 end if;
178 CLOSE c_current_employers;
179 end if;
180 RETURN (g_prev_emp);
181
182 END GET_CURRENT_EMPLOYER_PTY;
183
184
185 -- ----------------------------------------------------------------------------
186 -- |-------------------------< GET_MAX_QUAL_TYPE >---------------------------|
187 -- ----------------------------------------------------------------------------
188 FUNCTION GET_MAX_QUAL_TYPE (p_person_id per_all_people_f.person_id%TYPE)
189 RETURN VARCHAR2
190
191 IS
192
193 l_qual_type PER_QUALIFICATION_TYPES.NAME%TYPE;
194 l_qual_type2 PER_QUALIFICATION_TYPES.NAME%TYPE;
195 l_max_rank number;
196
197 CURSOR c_qual_types(p_person PER_ALL_PEOPLE_F.PERSON_ID%TYPE) IS
198 SELECT QTYP.NAME,qtyp.rank
199 FROM PER_QUALIFICATION_TYPES QTYP,
200 PER_QUALIFICATIONS QUAL,
201 PER_ALL_PEOPLE_F ppf,
202 PER_ALL_PEOPLE_F ppf2
203 WHERE ppf.person_id = p_person
204 AND ppf.party_id = ppf2.party_id
205 AND trunc(sysdate) BETWEEN ppf.effective_start_date and ppf.effective_end_date
206 AND trunc(sysdate) BETWEEN ppf2.effective_start_date and ppf2.effective_end_date
207 AND ppf2.person_id = QUAL.PERSON_ID
208 AND QUAL.QUALIFICATION_TYPE_ID = QTYP.QUALIFICATION_TYPE_ID
209 order by QTYP.RANK desc, qual.awarded_date desc, qual.creation_date desc;
210
211 CURSOR c_qual_types2(p_person PER_ALL_PEOPLE_F.PERSON_ID%TYPE
212 ,p_max_rank number) IS
213 SELECT QTYP.NAME
214 FROM PER_QUALIFICATION_TYPES QTYP,
215 PER_QUALIFICATIONS QUAL,
216 PER_ESTABLISHMENT_ATTENDANCES ESTAB,
217 PER_ALL_PEOPLE_F ppf,
218 PER_ALL_PEOPLE_F ppf2
219 WHERE ppf.person_id = p_person
220 AND ppf.party_id = ppf2.party_id
221 AND trunc(sysdate) BETWEEN ppf.effective_start_date and ppf.effective_end_date
222 AND trunc(sysdate) BETWEEN ppf2.effective_start_date and ppf2.effective_end_date
223 AND ppf2.person_id = ESTAB.PERSON_ID
224 AND estab.attendance_id=qual.attendance_id
225 AND QUAL.QUALIFICATION_TYPE_ID = QTYP.QUALIFICATION_TYPE_ID
226 and nvl(qtyp.rank,-1)>=nvl(p_max_rank,-1)
227 order by QTYP.RANK desc, qual.awarded_date desc, qual.creation_date desc;
228
229 BEGIN
230 -- MAIN FUNCTION LOGIC
231
232 OPEN c_qual_types(p_person_id);
233 --Get an instance of a qualification of max rank
234 FETCH c_qual_types INTO l_qual_type,l_max_rank;
235 if c_qual_types%notfound then
236 l_qual_type:='';
237 l_max_rank:=-1;
238 end if;
239 CLOSE c_qual_types;
240 OPEN c_qual_types2(p_person_id,l_max_rank);
241 --Get an instance of a qualification of max rank
242 FETCH c_qual_types2 INTO l_qual_type2;
243 if c_qual_types2%found then
244 l_qual_type:=l_qual_type2;
245 end if;
246 CLOSE c_qual_types2;
247
248 RETURN (l_qual_type);
249
250 END GET_MAX_QUAL_TYPE;
251 -- ----------------------------------------------------------------------------
252 -- |-------------------------< GET_MAX_QUAL_TYPE_PTY >---------------------------|
253 -- ----------------------------------------------------------------------------
254 FUNCTION GET_MAX_QUAL_TYPE_PTY (p_party_id number)
255 RETURN VARCHAR2
256
257 IS
258
259 l_qual_type PER_QUALIFICATION_TYPES.NAME%TYPE;
260
261 CURSOR c_qual_types IS
262 SELECT QTYP.NAME
263 FROM PER_QUALIFICATION_TYPES QTYP,
264 PER_QUALIFICATIONS QUAL,
265 PER_ALL_PEOPLE_F ppf
266 WHERE ppf.party_id = p_party_id
267 AND trunc(sysdate) BETWEEN ppf.effective_start_date and ppf.effective_end_date
268 AND ppf.person_id = QUAL.PERSON_ID
269 AND QUAL.QUALIFICATION_TYPE_ID = QTYP.QUALIFICATION_TYPE_ID
270 order by QTYP.RANK desc, qual.awarded_date desc, qual.creation_date desc;
271
272 CURSOR c_qual_types2 IS
273 SELECT /*+ FIRST_ROWS */ QTYP.NAME
274 FROM PER_QUALIFICATION_TYPES QTYP,
275 PER_QUALIFICATIONS QUAL,
276 PER_ESTABLISHMENT_ATTENDANCES ESTAB,
277 PER_ALL_PEOPLE_F ppf
278 WHERE ppf.party_id = p_party_id
279 AND trunc(sysdate) BETWEEN ppf.effective_start_date and ppf.effective_end_date
280 AND ppf.person_id = ESTAB.PERSON_ID
281 AND estab.attendance_id=qual.attendance_id
282 AND QUAL.QUALIFICATION_TYPE_ID = QTYP.QUALIFICATION_TYPE_ID
283 and not exists (select 1 from per_qualifications qual2,per_qualification_types qtyp2
284 where qual2.person_id=ppf.person_id
285 and qtyp2.qualification_type_id=qual2.qualification_type_id
286 and nvl(qtyp2.rank,-1)>nvl(qtyp.rank,-1))
287 order by QTYP.RANK desc, qual.awarded_date desc, qual.creation_date desc;
288
289 BEGIN
290 -- MAIN FUNCTION LOGIC
291
292 if (nvl(g_qual_party_id,-1) <>p_party_id) then
293 g_qual_party_id:=p_party_id;
294
295 OPEN c_qual_types2;
296 --Get an instance of a qualification of max rank
297 FETCH c_qual_types2 INTO g_qual_type;
298 if c_qual_types2%notfound then
299 CLOSE c_qual_types2;
300 OPEN c_qual_types;
301 --Get an instance of a qualification of max rank
302 FETCH c_qual_types INTO g_qual_type;
303 if c_qual_types%notfound then
304 g_qual_type:='';
305 end if;
306 CLOSE c_qual_types;
307 else
308 CLOSE c_qual_types2;
309 end if;
310
311 end if;
312
313 RETURN (g_qual_type);
314
315 END GET_MAX_QUAL_TYPE_PTY;
316
317
318 -- ----------------------------------------------------------------------------
319 -- |-----------------------< GET_EMP_UPT_FOR_PERSON >-------------------------|
320 -- ----------------------------------------------------------------------------
321
322 FUNCTION GET_EMP_UPT_FOR_PERSON (p_person_id per_all_people_f.person_id%TYPE,
323 p_eff_date date )
324 RETURN VARCHAR2
325 IS
326 CURSOR csr_emp_person_types
327 IS
328 SELECT ttl.user_person_type
329 FROM per_person_types_tl ttl
330 ,per_person_types typ
331 ,per_person_type_usages_f ptu
332 ,PER_ALL_PEOPLE_F ppf
333 ,PER_ALL_PEOPLE_F ppf2
334 WHERE ppf.person_id = p_person_id
335 AND ppf.party_id = ppf2.party_id
336 AND p_eff_date BETWEEN ppf.effective_start_date and ppf.effective_end_date
337 AND p_eff_date BETWEEN ppf2.effective_start_date and ppf2.effective_end_date
338 AND ppf2.person_id = ptu.person_id
339 AND ttl.language = userenv('LANG')
340 AND ttl.person_type_id = typ.person_type_id
341 AND typ.system_person_type IN ('EMP','EX_EMP')
342 AND typ.person_type_id = ptu.person_type_id
343 AND p_eff_date BETWEEN ptu.effective_start_date
344 AND ptu.effective_end_date
345 order by typ.system_person_type ASC, ptu.effective_start_date DESC;
346
347 l_user_person_type per_person_types_tl.user_person_type%type;
348
349 BEGIN
350 open csr_emp_person_types;
351 fetch csr_emp_person_types into l_user_person_type;
352 if csr_emp_person_types%notfound then
353 l_user_person_type:=null;
354 end if;
355 close csr_emp_person_types;
356
357 RETURN l_user_person_type;
358
359 END GET_EMP_UPT_FOR_PERSON;
360
361
362 -- ----------------------------------------------------------------------------
363 -- |-----------------------< GET_EMP_UPT_FOR_PARTY >-------------------------|
364 -- ----------------------------------------------------------------------------
365
366 FUNCTION GET_EMP_UPT_FOR_PARTY (p_party_id number,
367 p_eff_date date )
368 RETURN VARCHAR2
369 IS
370 CURSOR csr_emp_person_types
371 IS
372 SELECT ttl.user_person_type
373 FROM per_person_types_tl ttl
374 ,per_person_types typ
375 ,per_person_type_usages_f ptu
376 ,PER_ALL_PEOPLE_F ppf
377 WHERE ppf.party_id = p_party_id
378 AND p_eff_date BETWEEN ppf.effective_start_date and ppf.effective_end_date
379 AND ppf.person_id = ptu.person_id
380 AND ttl.language = userenv('LANG')
381 AND ttl.person_type_id = typ.person_type_id
382 AND typ.system_person_type IN ('EMP','EX_EMP')
383 AND typ.person_type_id = ptu.person_type_id
384 AND p_eff_date BETWEEN ptu.effective_start_date
385 AND ptu.effective_end_date
386 order by typ.system_person_type ASC, ptu.effective_start_date DESC;
387
388
389 BEGIN
390
391 if (nvl(g_emp_upt_party_id,-1)<>p_party_id) then
392 g_emp_upt_party_id:=p_party_id;
393 open csr_emp_person_types;
394 fetch csr_emp_person_types into g_emp_upt;
395 if csr_emp_person_types%notfound then
396 g_emp_upt:='';
397 end if;
398 close csr_emp_person_types;
399 end if;
400
401 RETURN g_emp_upt;
402
403 END GET_EMP_UPT_FOR_PARTY;
404
405
406
407
408 -- ----------------------------------------------------------------------------
409 -- |-----------------------< GET_APL_UPT_FOR_PERSON >------------------------|
410 -- ----------------------------------------------------------------------------
411
412 FUNCTION GET_APL_UPT_FOR_PERSON (p_person_id per_all_people_f.person_id%TYPE,
413 p_eff_date date )
414 RETURN VARCHAR2
415 IS
416 CURSOR csr_emp_person_type_exists
417 IS
418 SELECT GET_EMP_UPT_FOR_PERSON (ppf.person_id,p_eff_date)
419 FROM per_people_f ppf
420 WHERE ppf.person_id = p_person_id
421 AND p_eff_date BETWEEN ppf.effective_start_date
422 AND ppf.effective_end_date;
423
424 CURSOR csr_apl_person_types
425 IS
426 SELECT ttl.user_person_type
427 FROM per_person_types_tl ttl
428 ,per_person_types typ
429 ,per_person_type_usages_f ptu
430 ,PER_ALL_PEOPLE_F ppf
431 ,PER_ALL_PEOPLE_F ppf2
432 WHERE ppf.person_id = p_person_id
433 AND ppf.party_id = ppf2.party_id
434 AND p_eff_date BETWEEN ppf.effective_start_date and ppf.effective_end_date
435 AND p_eff_date BETWEEN ppf2.effective_start_date and ppf2.effective_end_date
436 AND ppf2.person_id = ptu.person_id
437 AND ttl.language = userenv('LANG')
438 AND ttl.person_type_id = typ.person_type_id
439 AND typ.system_person_type IN ('APL','EX_APL')
440 AND typ.person_type_id = ptu.person_type_id
441 AND p_eff_date BETWEEN ptu.effective_start_date
442 AND ptu.effective_end_date
443 ORDER BY typ.system_person_type ASC, ptu.effective_start_date DESC;
444
445
446 l_user_person_type per_person_types_tl.user_person_type%type;
447 l_dummy per_person_types_tl.user_person_type%type;
448
449 BEGIN
450 open csr_emp_person_type_exists;
451 fetch csr_emp_person_type_exists into l_dummy;
452 if csr_emp_person_type_exists%found OR
453 (GET_EMP_UPT_FOR_PERSON (p_person_id,p_eff_date)=null)
454 then
455 open csr_apl_person_types;
456 fetch csr_apl_person_types into l_user_person_type;
457 if csr_apl_person_types%notfound then
458 l_user_person_type:=null;
459 end if;
460 close csr_apl_person_types;
461 else
462 l_user_person_type:=null;
463 end if;
464 close csr_emp_person_type_exists;
465
466 RETURN l_user_person_type;
467
468 END GET_APL_UPT_FOR_PERSON;
469
470 -- ----------------------------------------------------------------------------
471 -- |-----------------------< GET_APL_UPT_FOR_PARTY >-------------------------|
472 -- ----------------------------------------------------------------------------
473
474 FUNCTION GET_APL_UPT_FOR_PARTY (p_party_id number,
475 p_eff_date date )
476 RETURN VARCHAR2
477 IS
478 CURSOR csr_emp_person_types
479 IS
480 SELECT ttl.user_person_type
481 FROM per_person_types_tl ttl
482 ,per_person_types typ
483 ,per_person_type_usages_f ptu
484 ,PER_ALL_PEOPLE_F ppf
485 WHERE ppf.party_id = p_party_id
486 AND p_eff_date BETWEEN ppf.effective_start_date and ppf.effective_end_date
487 AND ppf.person_id = ptu.person_id
488 AND ttl.language = userenv('LANG')
489 AND ttl.person_type_id = typ.person_type_id
490 AND typ.system_person_type IN ('APL','EX_APL')
491 AND typ.person_type_id = ptu.person_type_id
492 AND p_eff_date BETWEEN ptu.effective_start_date
493 AND ptu.effective_end_date
494 order by typ.system_person_type ASC, ptu.effective_start_date DESC;
495
496 l_user_person_type per_person_types_tl.user_person_type%type;
497
498 BEGIN
499 if (nvl(g_apl_upt_party_id,-1)<>p_party_id) then
500 g_apl_upt_party_id:=p_party_id;
501 open csr_emp_person_types;
502 fetch csr_emp_person_types into g_apl_upt;
503 if csr_emp_person_types%notfound then
504 g_apl_upt:='';
505 end if;
506 close csr_emp_person_types;
507 end if;
508 RETURN g_apl_upt;
509
510 END GET_APL_UPT_FOR_PARTY;
511
512
513
514 -- ----------------------------------------------------------------------------
515 -- |-----------------------< GET_EMP_SPT_FOR_PERSON >-------------------------|
516 -- ----------------------------------------------------------------------------
517
518 FUNCTION GET_EMP_SPT_FOR_PERSON (p_person_id per_all_people_f.person_id%TYPE,
519 p_eff_date date ) RETURN VARCHAR2
520 IS
521 CURSOR csr_emp_person_types
522 IS
523 SELECT typ.system_person_type
524 FROM per_person_types typ
525 ,per_person_type_usages_f ptu
526 ,PER_ALL_PEOPLE_F ppf
527 ,PER_ALL_PEOPLE_F ppf2
528 WHERE ppf.person_id = p_person_id
529 AND ppf.party_id = ppf2.party_id
530 AND p_eff_date BETWEEN ppf.effective_start_date and ppf.effective_end_date
531 AND p_eff_date BETWEEN ppf2.effective_start_date and ppf2.effective_end_date
532 AND ppf2.person_id = ptu.person_id
533 AND typ.system_person_type IN ('EMP','EX_EMP')
534 AND typ.person_type_id = ptu.person_type_id
535 AND p_eff_date BETWEEN ptu.effective_start_date
536 AND ptu.effective_end_date
537 order by typ.system_person_type ASC, ptu.effective_start_date DESC;
538
539 l_system_person_type per_person_types.system_person_type%type;
540
541 BEGIN
542 open csr_emp_person_types;
543 fetch csr_emp_person_types into l_system_person_type;
544 if csr_emp_person_types%notfound then
545 l_system_person_type:=null;
546 end if;
547 close csr_emp_person_types;
548
549 RETURN l_system_person_type;
550
551 END GET_EMP_SPT_FOR_PERSON;
552 --
553 -- -------------------------------------------------------------------------
554 -- |---------------------< get_recruitment_person_id >-------------------|
555 -- -------------------------------------------------------------------------
556 --
557 FUNCTION GET_RECRUITMENT_PERSON_ID
558 (p_person_id IN per_all_people_f.person_id%TYPE
559 ,p_effective_date IN per_all_people_f.effective_start_date%TYPE)
560 RETURN per_all_people_f.person_id%TYPE
561
562 IS
563 --
564 -- cursor to find party with a notification preference
565 --
566 CURSOR csr_person_with_notif_prefs(p_person_id NUMBER
567 ,p_effective_date DATE) IS
568 SELECT inp.person_id
569 FROM PER_ALL_PEOPLE_F PPF,
570 IRC_NOTIFICATION_PREFERENCES INP,
571 PER_ALL_PEOPLE_F PPF2
572 WHERE ppf.person_id = p_person_id
573 AND p_effective_date BETWEEN ppf.effective_start_date
574 AND ppf.effective_end_date
575 AND ppf.party_id = ppf2.party_id
576 AND p_effective_date BETWEEN ppf2.effective_start_date
577 AND ppf2.effective_end_date
578 AND ppf2.person_id = inp.person_id;
579
580 begin
581
582 if (nvl(g_rec_person_id_in,-1)<>p_person_id) then
583 g_rec_person_id_in:=p_person_id;
584 --
585 -- check for a person with notif prefs
586 --
587 open csr_person_with_notif_prefs(p_person_id, p_effective_date);
588 fetch csr_person_with_notif_prefs into g_rec_person_id_out;
589 if csr_person_with_notif_prefs%notfound then
590 g_rec_person_id_out:=p_person_id;
591 end if;
592 close csr_person_with_notif_prefs;
593 end if;
594
595 return g_rec_person_id_out;
596 end get_recruitment_person_id;
597 -- ----------------------------------------------------------------------------
598 -- |-----------------------< IS_OPEN_PARTY >-------------------------|
599 -- ----------------------------------------------------------------------------
600
601 FUNCTION IS_OPEN_PARTY (p_party_id number
602 ,p_eff_date date )
603 RETURN VARCHAR2
604 IS
605 CURSOR csr_is_emp is
606 select 1
607 from per_all_people_f per_per
608 where per_per.party_id=p_party_id
609 and p_eff_date between per_per.effective_start_date and per_per.effective_end_date
610 and per_per.current_employee_flag='Y';
611 --
612 cursor csr_has_vac is
613 select 1
614 from per_all_people_f per_per
615 ,per_all_assignments_f per_asg
616 ,per_vacancies per_vac
617 where per_per.party_id=p_party_id
618 and p_eff_date between per_per.effective_start_date and per_per.effective_end_date
619 and per_per.person_id=per_asg.person_id
620 and p_eff_date between per_asg.effective_start_date and per_asg.effective_end_date
621 and per_asg.vacancy_id=per_vac.vacancy_id;
622 --
623
624 l_dummy number;
625
626 BEGIN
627 if (nvl(g_open_party_id,-1)<>p_party_id) then
628 g_open_party_id:=p_party_id;
629 g_open_party:='TRUE';
630 open csr_is_emp;
631 fetch csr_is_emp into l_dummy;
632 if csr_is_emp%found then
633 close csr_is_emp;
634 open csr_has_vac;
635 fetch csr_has_vac into l_dummy;
636 if csr_has_vac%notfound then
637 g_open_party:='FALSE';
638 end if;
639 close csr_has_vac;
640 else
641 close csr_is_emp;
642 end if;
643 end if;
644 RETURN g_open_party;
645
646 END IS_OPEN_PARTY;
647
648 -- ----------------------------------------------------------------------------
649 -- |-----------------------< is_internal_person >-------------------------|
650 -- ----------------------------------------------------------------------------
651
652 FUNCTION is_internal_person (p_person_id number
653 ,p_eff_date date )
654 RETURN VARCHAR2
655 IS
656 CURSOR csr_is_emp is
657 select 1
658 from per_all_people_f per_per
659 where per_per.person_id=p_person_id
660 and p_eff_date between per_per.effective_start_date and per_per.effective_end_date
661 and (per_per.current_employee_flag='Y' or per_per.current_npw_flag='Y');
662 --
663 CURSOR csr_is_emp2 is
664 select 1
665 from per_all_people_f per_per
666 , per_all_people_f per_per2
667 where per_per.person_id=p_person_id
668 and p_eff_date between per_per.effective_start_date and per_per.effective_end_date
669 and per_per.party_id is not null
670 and per_per2.party_id=per_per.party_id
671 and p_eff_date between per_per2.effective_start_date and per_per2.effective_end_date
672 and (per_per2.current_employee_flag='Y' or per_per2.current_npw_flag='Y');
673 --
674 l_dummy number;
675
676 BEGIN
677 if (nvl(g_internal_person_id,-1)<>p_person_id) then
678 g_internal_person_id:=p_person_id;
679 g_internal_person:='FALSE';
680 open csr_is_emp2;
681 fetch csr_is_emp2 into l_dummy;
682 if csr_is_emp2%found then
683 close csr_is_emp2;
684 g_internal_person:='TRUE';
685 else
686 close csr_is_emp2;
687 open csr_is_emp;
688 fetch csr_is_emp into l_dummy;
689 if csr_is_emp%found then
690 g_internal_person:='TRUE';
691 end if;
692 close csr_is_emp;
693 end if;
694 end if;
695 RETURN g_internal_person;
696
697 END is_internal_person;
698
699 -- ----------------------------------------------------------------------------
700 -- |-----------------------< is_internal_email >-------------------------|
701 -- ----------------------------------------------------------------------------
702
703 FUNCTION is_internal_email (p_email_address varchar2
704 ,p_eff_date date )
705 RETURN VARCHAR2
706 IS
707 cursor get_user is
708 select employee_id
709 from fnd_user
710 where upper(email_address)=upper(p_email_address);
711
712 l_employee_id number(15);
713
714 begin
715
716 if (nvl(g_internal_email_address,'X')<>p_email_address) then
717 g_internal_email_address:=p_email_address;
718 g_internal_email:='FALSE';
719
720 open get_user;
721 fetch get_user into l_employee_id;
722 if get_user%found then
723 close get_user;
724 if l_employee_id is not null then
725 g_internal_email:=irc_utilities_pkg.is_internal_person(l_employee_id
726 ,p_eff_date);
727 end if;
728 else
729 close get_user;
730 end if;
731 end if;
732 RETURN g_internal_email;
733
734 end is_internal_email;
735
736 -- ----------------------------------------------------------------------------
737 -- |-----------------------< is_internal_person >-------------------------|
738 -- ----------------------------------------------------------------------------
739
740 FUNCTION is_internal_person (p_user_name varchar2
741 ,p_eff_date date )
742 RETURN VARCHAR2
743 IS
744 cursor get_user is
745 select employee_id
746 from fnd_user
747 where user_name=upper(p_user_name);
748
749 l_employee_id number(15);
750
751 begin
752 if (nvl(g_internal_user_name,'X')<>p_user_name) then
753 g_internal_user_name:=p_user_name;
754 g_internal_user:='FALSE';
755
756 open get_user;
757 fetch get_user into l_employee_id;
758 if get_user%found then
759 close get_user;
760 if l_employee_id is not null then
761 g_internal_user:=irc_utilities_pkg.is_internal_person(l_employee_id
762 ,p_eff_date);
763 end if;
764 else
765 close get_user;
766 end if;
767 end if;
768 RETURN g_internal_user;
769
770 end is_internal_person;
771
772 -- ----------------------------------------------------------------------------
773 -- |-----------------------< is_function_allowed >-------------------------|
774 -- ----------------------------------------------------------------------------
775 FUNCTION is_function_allowed(p_function_name varchar2
776 ,p_test_maint_availability in varchar2 default 'Y')
777 RETURN VARCHAR2
778 IS
779 l_retval varchar2(5);
780 begin
781 if(fnd_function.test(p_function_name,p_test_maint_availability)) then
782 l_retval := 'TRUE';
783 else
784 l_retval := 'FALSE';
785 end if;
786 RETURN l_retval;
787 end is_function_allowed;
788
789 --
790 --Added GET_LAST_QUAL_PTY by deenath to fix Bug #4726469
791 -- ----------------------------------------------------------------------------
792 -- |---------------------< GET_LAST_QUAL_PTY >-----------------------|
793 -- ----------------------------------------------------------------------------
794 FUNCTION GET_LAST_QUAL_PTY(p_party_id NUMBER,
795 p_eff_date DATE)
796 RETURN VARCHAR2
797 IS
798 --
799 CURSOR c_qualifications IS
800 SELECT pqt.name
801 FROM per_qualifications pq
802 ,per_qualification_types_vl pqt
803 WHERE pq.party_id = p_party_id
804 AND pq.attendance_id IS NOT NULL
805 AND pq.awarded_date IS NOT NULL
806 AND pqt.qualification_type_id = pq.qualification_type_id
807 ORDER BY pq.awarded_date DESC;
808 --
809 l_qual_title per_qualification_types_vl.name%TYPE;
810 --
811 BEGIN
812 OPEN c_qualifications;
813 FETCH c_qualifications INTO l_qual_title;
814 IF c_qualifications%NOTFOUND THEN
815 l_qual_title := '';
816 END IF;
817 CLOSE c_qualifications;
818 RETURN(l_qual_title);
819 END GET_LAST_QUAL_PTY;
820 --
821 -- -------------------------------------------------------------------
822 -- |--------------------< irc_applicant_tracking >-------------------|
823 -- -------------------------------------------------------------------
824 --
825 procedure irc_applicant_tracking(p_person_id in number
826 ,p_apl_profile_access_id in number
827 ,p_object_version_number out nocopy number
828 ) is
829 pragma autonomous_transaction;
830 l_object_version_number number;
831 l_return_status number;
832 l_person_id number := p_person_id;
833 l_apl_profile_access_id number := p_apl_profile_access_id;
834 --
835 begin
836 --
837 irc_apl_profile_access_api.create_apl_profile_access (
838 P_VALIDATE => false
839 ,P_PERSON_ID => l_person_id
840 ,P_APL_PROFILE_ACCESS_ID => l_apl_profile_access_id
841 ,P_OBJECT_VERSION_NUMBER => l_object_version_number
842 );
843 commit;
844 exception
845 when others then
846 rollback;
847 end irc_applicant_tracking;
848 --
849 -- ----------------------------------------------------------------------------
850 -- |---------------------< irc_mark_appl_considered >-----------------------|
851 -- ----------------------------------------------------------------------------
852 --
853 procedure irc_mark_appl_considered( p_effective_date in date
854 ,p_assignment_id in number
855 ,p_attempt_id in number
856 ,p_assignment_details_id in number
857 ,p_qualified in varchar2
858 ,p_considered in varchar2
859 ,p_update_mode in varchar2
860 ,p_details_version out nocopy number
861 ,p_effective_start_date out nocopy date
862 ,p_effective_end_date out nocopy date
863 ,p_object_version_number out nocopy number
864 )is
865 pragma autonomous_transaction;
866 --
867 l_effective_date date := p_effective_date;
868 l_assignment_id number := p_assignment_id;
869 l_attempt_id number := p_attempt_id;
870 l_assignment_details_id number ;
871 l_qualified varchar2(30) := p_qualified;
872 l_considered varchar2(30) := p_considered;
873 l_details_version number;
874 l_effective_start_date date;
875 l_effective_end_date date;
876 l_object_version_number number;
877 l_return_status varchar2(30);
878 l_update_mode varchar2(30) := p_update_mode;
879 l_assgn_details_row irc_assignment_details_f%ROWTYPE;
880 l_business_grp_id per_all_assignments_f.business_group_id%TYPE;
881 l_appln_tracking varchar2(1);
882 --
883 cursor c_assgn_details_row is
884 select *
885 from irc_assignment_details_f
886 where assignment_id =p_assignment_id
887 and sysdate between effective_start_date and effective_end_Date
888 and latest_details='Y';
889 --
890 cursor c_bg_id is
891 select business_group_id
892 from per_all_assignments_f
893 where assignment_id = p_assignment_id
894 and trunc(sysdate) between effective_start_date and effective_end_date;
895 --
896 cursor c_appln_tracking is
897 select nvl(ORG_INFORMATION11,'N')
898 from HR_ORGANIZATION_INFORMATION
899 where organization_id = l_business_grp_id
900 and org_information_context = 'BG Recruitment';
901 --
902 begin
903 hr_utility.set_location('Entering irc_mark_appl_considered',10);
904 --
905 open c_bg_id;
906 fetch c_bg_id into l_business_grp_id;
907 close c_bg_id;
908 --
909 open c_appln_tracking;
910 fetch c_appln_tracking into l_appln_tracking;
911 close c_appln_tracking;
912 --
913 if ( l_appln_tracking <> 'N') then
914 open c_assgn_details_row;
915 fetch c_assgn_details_row into l_assgn_details_row;
916 --
917 if(c_assgn_details_row%ROWCOUNT=0) then
918 hr_utility.set_location('Assignment details record does not exist',20);
919 irc_assignment_details_api.create_assignment_details (
920 p_validate => false
921 ,P_EFFECTIVE_DATE => l_effective_date
922 ,p_assignment_id => l_assignment_id
923 ,p_attempt_id => l_attempt_id
924 ,P_QUALIFIED => l_qualified
925 ,P_CONSIDERED => l_considered
926 ,p_assignment_details_id => l_assignment_details_id
927 ,p_details_version => l_details_version
928 ,p_effective_start_date => l_effective_start_date
929 ,p_effective_end_date => l_effective_end_date
930 ,P_OBJECT_VERSION_NUMBER => l_object_version_number
931 );
932 hr_utility.set_location('Created new assignment details row',30);
933 else
934 hr_utility.set_location('Assignment details record already exists',40);
935 hr_utility.set_location('p_update_mode::'||p_update_mode,50);
936
937 l_object_version_number := l_assgn_details_row.object_version_number;
938 l_assignment_details_id := l_assgn_details_row.assignment_details_id;
939
940 irc_assignment_details_api.update_assignment_details (
941 p_validate => false
942 ,p_effective_date => l_effective_date
943 ,p_datetrack_update_mode => l_update_mode
944 ,p_assignment_id => l_assignment_id
945 ,p_attempt_id => l_attempt_id
946 ,p_qualified => l_qualified
947 ,p_considered => l_considered
948 ,p_assignment_details_id => l_assignment_details_id
949 ,p_object_version_number => l_object_version_number
950 ,p_details_version => l_details_version
951 ,p_effective_start_date => l_effective_start_date
952 ,p_effective_end_date => l_effective_end_date
953 );
954 hr_utility.set_location('Updated assignment details record',60);
955 end if;
956 close c_assgn_details_row;
957 commit;
958 end if;
959 hr_utility.set_location('Leaving irc_mark_appl_considered',10);
960 exception
961 when others then
962 hr_utility.set_location('Exception in irc_mark_appl_considered::' || SQLERRM,100);
963 rollback;
964 hr_utility.set_location('Leaving irc_mark_appl_considered',10);
965 raise;
966 end irc_mark_appl_considered;
967 --
968 -- ----------------------------------------------------------------------------
969 -- |---------------------< irc_mark_appl_considered >-----------------------|
970 -- ----------------------------------------------------------------------------
971 --
972 procedure irc_mark_appl_considered(p_assignment_id in number)is
973 pragma autonomous_transaction;
974 --
975 l_effective_date date := trunc(sysdate);
976 l_assignment_id number := p_assignment_id;
977 l_attempt_id number ;
978 l_assignment_details_id number ;
979 l_qualified varchar2(30) ;
980 l_considered varchar2(30) := 'Y';
981 l_details_version number;
982 l_effective_start_date date;
983 l_effective_end_date date;
984 l_object_version_number number;
985 l_row number;
986 l_return_status varchar2(30);
987 l_update_mode varchar2(30);
988 l_assgn_details_row irc_assignment_details_f%ROWTYPE;
989 l_appl_tracking varchar2(1);
990 l_business_grp_id per_all_assignments_f.business_group_id%TYPE;
991
992 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;
993 cursor c_assgn_details_row is select * from irc_assignment_details_f where assignment_id =p_assignment_id and latest_details='Y';
994 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';
995 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';
996
997 begin
998
999 open c_bg_id;
1000 fetch c_bg_id into l_business_grp_id;
1001 close c_bg_id;
1002
1003 open c_appln_tracking;
1004 fetch c_appln_tracking into l_appl_tracking;
1005 close c_appln_tracking;
1006
1007 open c_assgn_details_row;
1008 fetch c_assgn_details_row into l_assgn_details_row;
1009 close c_assgn_details_row;
1010
1011 if (l_appl_tracking<>'N') then
1012
1013 open c_assgn_details;
1014 fetch c_assgn_details into l_row;
1015 if(c_assgn_details%NOTFOUND) then
1016
1017 irc_assignment_details_api.create_assignment_details (
1018 p_validate => false
1019 ,P_EFFECTIVE_DATE => l_effective_date
1020 ,p_assignment_id => l_assignment_id
1021 ,p_attempt_id => l_attempt_id
1022 ,P_QUALIFIED => l_qualified
1023 ,P_CONSIDERED => l_considered
1024 ,p_assignment_details_id => l_assignment_details_id
1025 ,p_details_version => l_details_version
1026 ,p_effective_start_date => l_effective_start_date
1027 ,p_effective_end_date => l_effective_end_date
1028 ,P_OBJECT_VERSION_NUMBER => l_object_version_number
1029 );
1030 else
1031
1032 if(trunc(sysdate)>l_assgn_details_row.effective_start_date) then
1033 l_update_mode:='UPDATE';
1034 else
1035 l_update_mode:='CORRECTION';
1036 end if;
1037
1038 l_assignment_details_id:=l_assgn_details_row.assignment_details_id;
1039 l_object_version_number:=l_assgn_details_row.object_version_number;
1040 irc_assignment_details_api.update_assignment_details (
1041 p_validate => false
1042 ,p_effective_date => l_effective_date
1043 ,p_datetrack_update_mode => l_update_mode
1044 ,p_assignment_id => l_assignment_id
1045 ,p_attempt_id => l_assgn_details_row.attempt_id
1046 ,p_qualified => l_assgn_details_row.qualified
1047 ,p_considered => l_considered
1048 ,p_assignment_details_id => l_assignment_details_id
1049 ,p_object_version_number => l_object_version_number
1050 ,p_details_version => l_details_version
1051 ,p_effective_start_date => l_effective_start_date
1052 ,p_effective_end_date => l_effective_end_date
1053 );
1054 end if;
1055 close c_assgn_details;
1056 commit;
1057 end if;
1058 exception
1059 when others then
1060 rollback;
1061 end irc_mark_appl_considered;
1062
1063 function getAMETxnDetailsForOffer (p_offerId in varchar2)
1064 return varchar2
1065 is
1066 cursor c_AMETxnDetails(c_offerId in varchar2) is
1067 SELECT history.transaction_history_id
1068 ,irc_xml_util.valueOf(histstate.transaction_document,'/Transaction/TransCtx/pAMETranType')
1069 ,irc_xml_util.valueOf(histstate.transaction_document,'/Transaction/TransCtx/pAMEAppId')
1070 FROM pqh_ss_transaction_history history,
1071 pqh_ss_trans_state_history histstate
1072 WHERE history.transaction_identifier = 'OFFER'
1073 AND history.transaction_history_id = ( SELECT min(transaction_history_id)
1074 FROM pqh_ss_step_history
1075 WHERE api_name = 'IRC_OFFERS_SWI.PROCESS_OFFERS_API'
1076 AND pk1 = c_offerId )
1077 AND histstate.transaction_history_id = history.transaction_history_id;
1078 txnId number := null;
1079 txnType varchar2(100) := null;
1080 applId number :=null;
1081 begin
1082 open c_AMETxnDetails(p_offerId);
1083 fetch c_AMETxnDetails into txnId, txnType, applId;
1084 if c_AMETxnDetails%FOUND then
1085 return txnId || ':' || txnType ||':' ||applId;
1086 end if;
1087 close c_AMETxnDetails;
1088 return '';
1089 end;
1090 END IRC_UTILITIES_PKG;
[Home] [Help]