[Home] [Help]
PACKAGE BODY: APPS.IRC_APPROVALS
Source
1 Package BODY IRC_APPROVALS as
2 /* $Header: ircame.pkb 120.22.12020000.2 2012/12/13 06:13:57 serrabha ship $ */
3
4 g_posting_path varchar2(250) :=
5 '/Transaction/TransCache/AM/TXN/EO/IrcPostingContentsVlEORow';
6 g_vacancy_path varchar2(250) :=
7 '/Transaction/TransCache/AM/TXN/EO/PerRequisitionsEORow/CEO/EO/PerAllVacanciesEORow';
8 g_search_path varchar2(250) :=
9 '/Transaction/TransCache/AM/TXN/EO/PerRequisitionsEORow/CEO/EO/PerAllVacanciesEORow/CEO/EO/IrcVacancySearchCriteriaEORow';
10 g_rec_activity_path varchar2(250) :=
11 '/Transaction/TransCache/AM/TXN/EO/IrcPostingContentsVlEORow/CEO/EO/PerRecruitmentActivitiesEORow';
12
13 --
14 -- -------------------------------------------------------------------------
15 -- |------------------------< get_transaction_data >-----------------------|
16 -- -------------------------------------------------------------------------
17 --
18 function get_transaction_data
19 (p_transaction_id in varchar2
20 ,p_path in varchar2)
21 return varchar2 is
22 --
23 l_retval varchar2(32000);
24 transactionDoc hr_api_transactions.transaction_document%type;
25 --
26 cursor get_doc is
27 select transaction_document
28 from hr_api_transactions
29 where transaction_id = p_transaction_id;
30 --
31 begin
32 --
33 irc_approvals.log('Entering get_transaction_data for transaction_id :' || p_transaction_id || ':');
34 irc_approvals.log('Access path :' || p_path || ':');
35 --
36 open get_doc;
37 fetch get_doc into transactionDoc;
38 if get_doc %notfound then
39 close get_doc;
40 l_retval:=null;
41 else
42 close get_doc;
43 l_retval:=irc_xml_util.valueOf(transactionDoc,p_path);
44 end if;
45 --
46 irc_approvals.log('Exiting get_transaction_data returning :' || l_retval || ':');
47 --
48 return l_retval;
49 --
50 end get_transaction_data;
51 --
52 -- -------------------------------------------------------------------------
53 -- |---------------------< get_transaction_number_data >-------------------|
54 -- -------------------------------------------------------------------------
55 --
56 function get_transaction_number_data
57 (transaction_id in varchar2
58 ,p_path in varchar2)
59 return number is
60 l_retval varchar2(32000);
61 ln_retval number;
62 --
63 begin
64 --
65 irc_approvals.log('Entering get_transaction_number_data');
66 --
67 l_retval:=irc_approvals.get_transaction_data
68 (p_transaction_id =>transaction_id
69 ,p_path =>p_path);
70 ln_retval:=to_number(l_retval);
71 --
72 irc_approvals.log('Exiting get_transaction_number_data');
73 --
74 return ln_retval;
75 end get_transaction_number_data;
76 --
77 -- -------------------------------------------------------------------------
78 --
79 function get_posting_data_number
80 (transaction_id in varchar2, data_name in varchar2)
81 return number is
82 l_retval number;
83 begin
84 --
85 irc_approvals.log('Entering get_posting_content_id');
86 --
87 l_retval:=irc_approvals.get_transaction_number_data
88 (transaction_id=>transaction_id,p_path => g_posting_path || '/' || data_name);
89 --
90 irc_approvals.log('Exiting get_posting_content_id');
91 --
92 return l_retval;
93 end get_posting_data_number;
94 --
95 -- -------------------------------------------------------------------------
96 --
97 function get_posting_data_varchar
98 (transaction_id in varchar2, data_name in varchar2)
99 return varchar2 is
100 l_retval varchar2(32000);
101 begin
102 --
103 irc_approvals.log('Entering get_posting_content_id');
104 --
105 l_retval:=irc_approvals.get_transaction_data
106 (p_transaction_id=>transaction_id,p_path => g_posting_path || '/' || data_name);
107 --
108 irc_approvals.log('Exiting get_posting_content_id');
109 --
110 return l_retval;
111 end get_posting_data_varchar;
112 --
113 -- -------------------------------------------------------------------------
114 --
115 function get_vacancy_data_number
116 (transaction_id in varchar2, data_name in varchar2)
117 return number is
118 l_retval number;
119 begin
120 --
121 irc_approvals.log('Entering get_vac_business_group_id');
122 --
123 l_retval:=irc_approvals.get_transaction_number_data
124 (transaction_id=>transaction_id
125 ,p_path => g_vacancy_path || '/' || data_name);
126 --
127 irc_approvals.log('Exiting get_vac_business_group_id');
128 --
129 return l_retval;
130 end get_vacancy_data_number;
131 --
132 -- -------------------------------------------------------------------------
133 --
134 function get_vacancy_data_varchar
135 (transaction_id in varchar2, data_name in varchar2)
136 return varchar2 is
137 l_retval varchar2(200);
138 begin
139 --
140 irc_approvals.log('Entering get_vac_budget_type');
141 --
142 l_retval:=irc_approvals.get_transaction_data
143 (p_transaction_id=>transaction_id
144 ,p_path => g_vacancy_path || '/' || data_name);
145 --
146 irc_approvals.log('Exiting get_vac_budget_type');
147 --
148 return l_retval;
149 end get_vacancy_data_varchar;
150 --
151 -- -------------------------------------------------------------------------
152 --
153 function get_search_data_number
154 (transaction_id in varchar2, data_name in varchar2)
155 return number is
156 l_retval number;
157 begin
158 --
159 irc_approvals.log('Entering get_search_data_number');
160 --
161 l_retval:=irc_approvals.get_transaction_number_data
162 (transaction_id=>transaction_id,p_path => g_search_path || '/' || data_name);
163 --
164 irc_approvals.log('Exiting get_search_data_number');
165 --
166 return l_retval;
167 end get_search_data_number;
168 --
169 -- -------------------------------------------------------------------------
170 --
171 function get_search_data_varchar
172 (transaction_id in varchar2, data_name in varchar2)
173 return varchar2 is
174 l_retval varchar2(200);
175 begin
176 --
177 irc_approvals.log('Entering get_search_data_varchar');
178 --
179 l_retval:=irc_approvals.get_transaction_data
180 (p_transaction_id=>transaction_id,p_path => g_search_path || '/' || data_name);
181 --
182 irc_approvals.log('Exiting get_search_data_varchar');
183 --
184 return l_retval;
185 end get_search_data_varchar;
186 --
187 -- -------------------------------------------------------------------------
188 -- |------------------------< get_transaction_mode >-----------------------|
189 -- -------------------------------------------------------------------------
190 --
191 function get_transaction_mode
192 (transaction_id in varchar2)
193 return varchar2 is
194 --
195 l_retval varchar2(200);
196 begin
197 --
198 irc_approvals.log('Entering get_transaction_mode');
199 --
200 l_retval:=irc_approvals.get_transaction_data
201 (p_transaction_id=>transaction_id
202 ,p_path => '/Transaction/TransCtx/CNode/dmlMode');
203 --
204 irc_approvals.log('Exiting get_transaction_mode');
205 --
206 return l_retval;
207 end get_transaction_mode;
208 --
209 --
210 -- -------------------------------------------------------------------------
211 -- |-------------------------< get_vacancy_id >----------------------------|
212 -- -------------------------------------------------------------------------
213 --
214 function get_vacancy_id
215 (transaction_id in varchar2)
216 return number is
217 BEGIN
218 return irc_approvals.get_vacancy_data_number(transaction_id, 'VacancyId');
219 end get_vacancy_id;
220 --
221 -- -------------------------------------------------------------------------
222 -- |----------------------< get_posting_content_id >-----------------------|
223 -- -------------------------------------------------------------------------
224 --
225 function get_posting_content_id
226 (transaction_id in varchar2)
227 return number is
228 begin
229 return irc_approvals.get_posting_data_number(transaction_id, 'PostingContentId');
230 end get_posting_content_id;
231 --
232 -- -------------------------------------------------------------------------
233 -- |-----------------------< get_vac_business_group_id >-------------------|
234 -- -------------------------------------------------------------------------
235 --
236 function get_vac_business_group_id
237 (transaction_id in varchar2)
238 return number is
239 begin
240 return irc_approvals.get_vacancy_data_number(transaction_id, 'BusinessGroupId');
241 end get_vac_business_group_id;
242 --
243 -- -------------------------------------------------------------------------
244 -- |-------------------------< get_vac_organization_id >-------------------|
245 -- -------------------------------------------------------------------------
246 --
247 function get_vac_organization_id
248 (transaction_id in varchar2)
249 return number is
250 begin
251 return irc_approvals.get_vacancy_data_number(transaction_id, 'OrganizationId');
252 end get_vac_organization_id;
253 --
254 -- -------------------------------------------------------------------------
255 -- |----------------------------< get_vac_grade_id >-----------------------|
256 -- -------------------------------------------------------------------------
257 --
258 function get_vac_grade_id
259 (transaction_id in varchar2)
260 return number is
261 begin
262 return irc_approvals.get_vacancy_data_number(transaction_id, 'GradeId');
263 end get_vac_grade_id;
264 --
265 -- -------------------------------------------------------------------------
266 -- |----------------------------< get_vac_job_id >-------------------------|
267 -- -------------------------------------------------------------------------
268 --
269 function get_vac_job_id
270 (transaction_id in varchar2)
271 return number is
272 begin
273 return irc_approvals.get_vacancy_data_number(transaction_id, 'JobId');
274 end get_vac_job_id;
275 --
276 -- -------------------------------------------------------------------------
277 -- |--------------------------< get_vac_location_id >----------------------|
278 -- -------------------------------------------------------------------------
279 --
280 function get_vac_location_id
281 (transaction_id in varchar2)
282 return number is
283 begin
284 return irc_approvals.get_vacancy_data_number(transaction_id, 'LocationId');
285 end get_vac_location_id;
286 --
287 -- -------------------------------------------------------------------------
288 -- |--------------------------< get_vac_budget_value >----------------------|
289 -- -------------------------------------------------------------------------
290 --
291 function get_vac_budget_value
292 (transaction_id in varchar2)
293 return number is
294 begin
295 return irc_approvals.get_vacancy_data_number(transaction_id, 'BudgetMeasurementValue');
296 end get_vac_budget_value;
297 --
298 -- -------------------------------------------------------------------------
299 -- |--------------------------< get_vac_budget_type >----------------------|
300 -- -------------------------------------------------------------------------
301 --
302 function get_vac_budget_type
303 (transaction_id in varchar2)
304 return varchar2 is
305 begin
306 return irc_approvals.get_vacancy_data_varchar(transaction_id, 'BudgetMeasurementType');
307 end get_vac_budget_type;
308 --
309 -- -------------------------------------------------------------------------
310 -- |----------------------------< get_vac_status >-------------------------|
311 -- -------------------------------------------------------------------------
312 --
313 function get_vac_status
314 (transaction_id in varchar2)
315 return varchar2 is
316 begin
317 return irc_approvals.get_vacancy_data_varchar(transaction_id, 'Status');
318 end get_vac_status;
319 --
320 -- -------------------------------------------------------------------------
321 -- |-------------------------< get_vac_professional_area >-----------------|
322 -- -------------------------------------------------------------------------
323 --
324 function get_vac_professional_area
325 (transaction_id in varchar2)
326 return varchar2 is
327 begin
328 return irc_approvals.get_search_data_varchar(transaction_id, 'ProfessionalArea');
329 end get_vac_professional_area;
330 --
331 -- -------------------------------------------------------------------------
332 -- |-------------------------< get_vac_for_emp >---------------------------|
333 -- -------------------------------------------------------------------------
334 --
335 function get_vac_for_emp
336 (transaction_id in varchar2)
337 return varchar2 is
338 begin
339 return irc_approvals.get_search_data_varchar(transaction_id, 'Employee');
340 end get_vac_for_emp;
341 --
342 -- -------------------------------------------------------------------------
343 -- |-------------------------< get_vac_for_con >---------------------------|
344 -- -------------------------------------------------------------------------
345 --
346 function get_vac_for_con
347 (transaction_id in varchar2)
348 return varchar2 is
349 begin
350 return irc_approvals.get_search_data_varchar(transaction_id, 'Contractor');
351 end get_vac_for_con;
352 --
353 -- -------------------------------------------------------------------------
354 -- |--------------------< get_vac_employment_category >--------------------|
355 -- -------------------------------------------------------------------------
356 --
357 function get_vac_employment_category
358 (transaction_id in varchar2)
359 return varchar2 is
360 begin
361 return irc_approvals.get_search_data_varchar(transaction_id, 'EmploymentCategory');
362 end get_vac_employment_category;
363 --
364 -- -------------------------------------------------------------------------
365 -- |--------------------------< get_vac_min_salary >-----------------------|
366 -- -------------------------------------------------------------------------
367 --
368 function get_vac_min_salary
369 (transaction_id in varchar2)
370 return number is
371 begin
372 return irc_approvals.get_search_data_number(transaction_id, 'MinSalary');
373 end get_vac_min_salary;
374 --
375 -- -------------------------------------------------------------------------
376 -- |--------------------------< get_vac_max_salary >-----------------------|
377 -- -------------------------------------------------------------------------
378 --
379 function get_vac_max_salary
380 (transaction_id in varchar2)
381 return number is
382 begin
383 return irc_approvals.get_search_data_number(transaction_id, 'MaxSalary');
384 end get_vac_max_salary;
385 --
386 -- -------------------------------------------------------------------------
387 -- |------------------------< get_salary_currency >------------------------|
388 -- -------------------------------------------------------------------------
389 --
390 function get_salary_currency
391 (transaction_id in varchar2)
392 return varchar2 is
393 begin
394 return irc_approvals.get_search_data_varchar(transaction_id, 'SalaryCurrency');
395 end get_salary_currency;
396 --
397 -- -------------------------------------------------------------------------
398 -- |--------------------------< get_work_at_home >-------------------------|
399 -- -------------------------------------------------------------------------
400 --
401 function get_work_at_home
402 (transaction_id in varchar2)
403 return varchar2 is
404 begin
405 return irc_approvals.get_search_data_varchar(transaction_id, 'WorkAtHome');
406 end get_work_at_home;
407 --
408 -- -------------------------------------------------------------------------
409 -- |----------------------< get_vac_organization_changed >-----------------|
410 -- -------------------------------------------------------------------------
411 --
412 function get_vac_organization_changed
413 (transaction_id in varchar2)
414 return varchar2 is
415 l_new_id number;
416 l_vacancy_id number;
417 l_old_id number;
418 --
419 l_retval varchar2(30);
420 cursor get_old_id(p_vacancy_id number) is
421 select organization_id
422 from per_all_vacancies
423 where vacancy_id=p_vacancy_id;
424 begin
425 --
426 irc_approvals.log('Entering get_vac_organization_changed');
427 --
428 l_retval := 'false';
429 --
430 if irc_approvals.get_transaction_mode(transaction_id) <> 'INSERT' then
431 --
432 l_new_id := irc_approvals.get_vacancy_data_number(transaction_id=>transaction_id,data_name=>'OrganizationId');
433 --
434 l_vacancy_id:=irc_approvals.get_vacancy_data_number(transaction_id=>transaction_id,data_name=>'VacancyId');
435 open get_old_id(l_vacancy_id);
436 fetch get_old_id into l_old_id;
437 close get_old_id;
438 --
439 irc_approvals.log('Comparing old org id :' || to_char(l_old_id)
440 || ': to new org id :' || to_char(l_new_id) || ':');
441 --
442 if(nvl(l_new_id,hr_api.g_number)
443 <> nvl(l_old_id,hr_api.g_number) ) then
444 l_retval:='true';
445 end if;
446 end if;
447 --
448 irc_approvals.log('Exiting get_vac_organization_changed returning :' || l_retval || ':');
449 --
450 return l_retval;
451 end get_vac_organization_changed;
452 --
453 -- -------------------------------------------------------------------------
454 -- |----------------------< get_vac_job_changed >--------------------------|
455 -- -------------------------------------------------------------------------
456 --
457 function get_vac_job_changed
458 (transaction_id in varchar2)
459 return varchar2 is
460 l_new_id number;
461 l_vacancy_id number;
462 l_old_id number;
463 --
464 l_retval varchar2(30);
465 cursor get_old_id(p_vacancy_id number) is
466 select job_id
467 from per_all_vacancies
468 where vacancy_id=p_vacancy_id;
469 begin
470 --
471 irc_approvals.log('Entering get_vac_job_changed');
472 --
473 l_retval := 'false';
474 --
475 if irc_approvals.get_transaction_mode(transaction_id) <> 'INSERT' then
476 l_vacancy_id:=irc_approvals.get_vacancy_data_number(transaction_id=>transaction_id,data_name=>'VacancyId');
477 l_new_id:=irc_approvals.get_vacancy_data_number(transaction_id=>transaction_id,data_name=>'JobId');
478 open get_old_id(l_vacancy_id);
479 fetch get_old_id into l_old_id;
480 close get_old_id;
481 --
482 irc_approvals.log('Comparing old job id :' || to_char(l_old_id)
483 || ': to new job id :' || to_char(l_new_id) || ':');
484 --
485 if(nvl(l_new_id,hr_api.g_number)
486 <> nvl(l_old_id,hr_api.g_number) ) then
487 l_retval:='true';
488 end if;
489 end if;
490 --
491 irc_approvals.log('Exiting get_vac_job_changed returning :' || l_retval || ':');
492 --
493 return l_retval;
494 end get_vac_job_changed;
495 --
496 -- -------------------------------------------------------------------------
497 -- |----------------------< get_vac_grade_changed >------------------------|
498 -- -------------------------------------------------------------------------
499 --
500 function get_vac_grade_changed
501 (transaction_id in varchar2)
502 return varchar2 is
503 l_new_id number;
504 l_vacancy_id number;
505 l_old_id number;
506 --
507 l_retval varchar2(30);
508 cursor get_old_id(p_vacancy_id number) is
509 select grade_id
510 from per_all_vacancies
511 where vacancy_id=p_vacancy_id;
512 begin
513 --
514 irc_approvals.log('Entering get_vac_grade_changed');
515 --
516 l_retval := 'false';
517 --
518 if irc_approvals.get_transaction_mode(transaction_id) <> 'INSERT' then
519 --
520 l_new_id:=irc_approvals.get_vacancy_data_number(transaction_id=>transaction_id,data_name=>'GradeId');
521 --
522 l_vacancy_id:=irc_approvals.get_vacancy_data_number(transaction_id=>transaction_id,data_name=>'VacancyId');
523 open get_old_id(l_vacancy_id);
524 fetch get_old_id into l_old_id;
525 close get_old_id;
526 --
527 irc_approvals.log('Comparing old org id :' || to_char(l_old_id)
528 || ': to new org id :' || to_char(l_new_id) || ':');
529 --
530 if(nvl(l_new_id,hr_api.g_number)
531 <> nvl(l_old_id,hr_api.g_number) ) then
532 l_retval:='true';
533 end if;
534 --
535 end if;
536 --
537 irc_approvals.log('Exiting get_vac_grade_changed returning :' || l_retval || ':');
538 --
539 return l_retval;
540 end get_vac_grade_changed;
541 --
542 -- -------------------------------------------------------------------------
543 -- |----------------------< get_vac_position_changed >---------------------|
544 -- -------------------------------------------------------------------------
545 --
546 function get_vac_position_changed
547 (transaction_id in varchar2)
548 return varchar2 is
549 l_new_id number;
550 l_vacancy_id number;
551 l_old_id number;
552 --
553 l_retval varchar2(30);
554 cursor get_old_id(p_vacancy_id number) is
555 select position_id
556 from per_all_vacancies
557 where vacancy_id=p_vacancy_id;
558 begin
559 --
560 irc_approvals.log('Entering get_vac_position_changed');
561 --
562 l_retval := 'false';
563 --
564 if irc_approvals.get_transaction_mode(transaction_id) <> 'INSERT' then
565 --
566 l_new_id := irc_approvals.get_vacancy_data_number(transaction_id=>transaction_id,data_name=>'PositionId');
567 --
568 l_vacancy_id := irc_approvals.get_vacancy_data_number(transaction_id=>transaction_id,data_name=>'VacancyId');
569 open get_old_id(l_vacancy_id);
570 fetch get_old_id into l_old_id;
571 close get_old_id;
572 --
573 irc_approvals.log('Comparing old org id :' || to_char(l_old_id)
574 || ': to new org id :' || to_char(l_new_id) || ':');
575 --
576 if(nvl(l_new_id,hr_api.g_number)
577 <>nvl(l_old_id,hr_api.g_number) ) then
578 l_retval:='true';
579 end if;
580 --
581 end if;
582 --
583 irc_approvals.log('Exiting get_vac_position_changed returning :' || l_retval || ':');
584 --
585 return l_retval;
586 end get_vac_position_changed;
587 --
588 -- -------------------------------------------------------------------------
589 -- |--------------------< get_vac_budget_value_changed >-------------------|
590 -- -------------------------------------------------------------------------
591 --
592 function get_vac_budget_value_changed
593 (transaction_id in varchar2)
594 return varchar2 is
595 l_new_id number;
596 l_vacancy_id number;
597 l_old_id number;
598 --
599 l_retval varchar2(30);
600 cursor get_old_id(p_vacancy_id number) is
601 select fnd_number.number_to_canonical(budget_measurement_value)
602 from per_all_vacancies
603 where vacancy_id=p_vacancy_id;
604 begin
605 --
606 irc_approvals.log('Entering get_vac_budget_value_changed');
607 --
608 l_retval := 'false';
609 --
610 if irc_approvals.get_transaction_mode(transaction_id)<>'INSERT' then
611 --
612 l_new_id:=irc_approvals.get_vacancy_data_number(transaction_id=>transaction_id,data_name=>'BudgetMeasurementValue');
613 --
614 l_vacancy_id:=irc_approvals.get_vacancy_data_number(transaction_id=>transaction_id,data_name=>'VacancyId');
615 open get_old_id(l_vacancy_id);
616 fetch get_old_id into l_old_id;
617 close get_old_id;
618 --
619 irc_approvals.log('Comparing old org id :' || to_char(l_old_id)
620 || ': to new org id :' || to_char(l_new_id) || ':');
621 --
622 if(nvl(l_new_id,hr_api.g_number)
623 <>nvl(l_old_id,hr_api.g_number) ) then
624 l_retval:='true';
625 end if;
626 --
627 end if;
628 --
629 irc_approvals.log('Exiting get_vac_budget_value_changed returning :' || l_retval || ':');
630 --
631 return l_retval;
632 end get_vac_budget_value_changed;
633 --
634 -- -------------------------------------------------------------------------
635 -- |--------------------< get_vac_budget_type_changed >-------------------|
636 -- -------------------------------------------------------------------------
637 --
638 function get_vac_budget_type_changed
639 (transaction_id in varchar2)
640 return varchar2 is
641 l_new_value varchar2(4000);
642 l_vacancy_id number;
643 l_old_value varchar2(4000);
644 --
645 l_retval varchar2(30);
646 cursor get_old_value(p_vacancy_id number) is
647 select budget_measurement_type
648 from per_all_vacancies
649 where vacancy_id=p_vacancy_id;
650 begin
651 --
652 irc_approvals.log('Entering get_vac_budget_type_changed');
653 --
654 l_retval := 'false';
655 --
656 if irc_approvals.get_transaction_mode(transaction_id) <> 'INSERT' then
657 --
658 l_new_value:=irc_approvals.get_vacancy_data_varchar(transaction_id=>transaction_id,data_name=>'BudgetMeasurementType');
659 --
660 l_vacancy_id:=irc_approvals.get_vacancy_data_number(transaction_id=>transaction_id,data_name=>'VacancyId');
661 open get_old_value(l_vacancy_id);
662 fetch get_old_value into l_old_value;
663 close get_old_value;
664 --
665 irc_approvals.log('Comparing old org value :' || l_old_value
666 || ': to new org value :' || l_new_value || ':');
667 --
668 if(nvl(l_new_value,hr_api.g_varchar2)
669 <> nvl(l_old_value,hr_api.g_varchar2) ) then
670 l_retval:='true';
671 end if;
672 end if;
673 --
674 irc_approvals.log('Exiting get_vac_budget_type_changed returning :' || l_retval || ':');
675 --
676 return l_retval;
677 --
678 end get_vac_budget_type_changed;
679 --
680 -- -------------------------------------------------------------------------
681 -- |------------------------< get_vac_status_changed >---------------------|
682 -- -------------------------------------------------------------------------
683 --
684 function get_vac_status_changed
685 (transaction_id in varchar2)
686 return varchar2 is
687 l_new_value varchar2(4000);
688 l_vacancy_id number;
689 l_old_value varchar2(4000);
690 --
691 l_retval varchar2(30);
692 cursor get_old_value(p_vacancy_id number) is
693 select status
694 from per_all_vacancies
695 where vacancy_id=p_vacancy_id;
696 begin
697 --
698 irc_approvals.log('Entering get_vac_status_changed');
699 --
700 l_retval := 'false';
701 --
702 if irc_approvals.get_transaction_mode(transaction_id)<> 'INSERT' then
703 --
704 l_new_value:=irc_approvals.get_vacancy_data_varchar(transaction_id=>transaction_id,data_name=>'Status');
705 --
706 l_vacancy_id:=irc_approvals.get_vacancy_data_number(transaction_id=>transaction_id,data_name=>'VacancyId');
707 open get_old_value(l_vacancy_id);
708 fetch get_old_value into l_old_value;
709 close get_old_value;
710 --
711 irc_approvals.log('Comparing old org value :' || l_old_value
712 || ': to new org value :' || l_new_value || ':');
713 --
714 if(nvl(l_new_value,hr_api.g_varchar2)
715 <> nvl(l_old_value,hr_api.g_varchar2) ) then
716 l_retval:='true';
717 end if;
718 end if;
719 --
720 irc_approvals.log('Exiting get_vac_status_changed returning :' || l_retval || ':');
721 --
722 return l_retval;
723 end get_vac_status_changed;
724 --
725 -- -------------------------------------------------------------------------
726 -- |-----------------------< get_posting_title_changed >-------------------|
727 -- -------------------------------------------------------------------------
728 --
729 function get_posting_title_changed
730 (transaction_id in varchar2)
731 return varchar2 is
732 l_new_value varchar2(4000);
733 l_posting_content_id number;
734 l_old_value varchar2(4000);
735 l_retval varchar2(30);
736 cursor get_old_value(p_posting_content_id number) is
737 select name
738 from irc_posting_contents_vl
739 where posting_content_id=p_posting_content_id;
740 begin
741 --
742 irc_approvals.log('Entering get_posting_title_changed');
743 --
744 l_retval := 'false';
745 --
746 if irc_approvals.get_transaction_mode(transaction_id)<> 'INSERT' then
747 --
748 l_posting_content_id := irc_approvals.get_posting_data_number
749 (transaction_id=>transaction_id, data_name=>'PostingContentId');
750 --
751 l_new_value:=irc_approvals.get_posting_data_varchar
752 (transaction_id=>transaction_id, data_name => 'Name');
753
754 open get_old_value(l_posting_content_id);
755 fetch get_old_value into l_old_value;
756 close get_old_value;
757 --
758 irc_approvals.log('Comparing old org value :' || l_old_value
759 || ': to new org value :' || l_new_value || ':');
760 --
761 if(nvl(l_new_value,hr_api.g_varchar2)
762 <> nvl(l_old_value,hr_api.g_varchar2) ) then
763 l_retval:='true';
764 end if;
765 end if;
766 --
767 irc_approvals.log('Exiting get_posting_title_changed returning :' || l_retval || ':');
768 --
769 return l_retval;
770 end get_posting_title_changed;
771 --
772 -- -------------------------------------------------------------------------
773 -- |---------------------< get_posting_job_title_changed >-----------------|
774 -- -------------------------------------------------------------------------
775 --
776 function get_posting_job_title_changed
777 (transaction_id in varchar2)
778 return varchar2 is
779 l_new_value varchar2(4000);
780 l_posting_content_id number;
781 l_old_value varchar2(4000);
782 l_retval varchar2(30);
783 cursor get_old_value(p_posting_content_id number) is
784 select job_title
785 from irc_posting_contents_vl
786 where posting_content_id=p_posting_content_id;
787 begin
788 --
789 irc_approvals.log('Entering get_posting_job_title_changed');
790 --
791 l_retval := 'false';
792 --
793 if irc_approvals.get_transaction_mode(transaction_id) <> 'INSERT' then
794 --
795 l_posting_content_id := irc_approvals.get_posting_data_number
796 (transaction_id=>transaction_id, data_name=>'PostingContentId');
797 --
798 l_new_value:=irc_approvals.get_posting_data_varchar
799 (transaction_id=>transaction_id, data_name => 'JobTitle');
800 --
801 open get_old_value(l_posting_content_id);
802 fetch get_old_value into l_old_value;
803 close get_old_value;
804 --
805 irc_approvals.log('Comparing old org value :' || l_old_value
806 || ': to new org value :' || l_new_value || ':');
807 --
808 if(nvl(l_new_value,hr_api.g_varchar2)
809 <> nvl(l_old_value,hr_api.g_varchar2) ) then
810 l_retval:='true';
811 end if;
812 end if;
813 --
814 irc_approvals.log('Exiting get_posting_job_title_changed returning :' || l_retval || ':');
815 --
816 return l_retval;
817 end get_posting_job_title_changed;
818 --
819 -- -------------------------------------------------------------------------
820 -- |---------------------< get_posting_department_changed >-----------------|
821 -- -------------------------------------------------------------------------
822 --
823 function get_posting_department_changed
824 (transaction_id in varchar2)
825 return varchar2 is
826 l_new_value varchar2(4000);
827 l_posting_content_id number;
828 l_old_value varchar2(4000);
829 l_retval varchar2(30);
830 cursor get_old_value(p_posting_content_id number) is
831 select org_name
832 from irc_posting_contents_vl
833 where posting_content_id=p_posting_content_id;
834 begin
835 --
836 irc_approvals.log('Entering get_posting_department_changed');
837 --
838 l_retval := 'false';
839 --
840 if irc_approvals.get_transaction_mode(transaction_id) <> 'INSERT' then
841 --
842 l_posting_content_id := irc_approvals.get_posting_data_number
843 (transaction_id=>transaction_id, data_name=>'PostingContentId');
844 --
845 l_new_value:=irc_approvals.get_posting_data_varchar
846 (transaction_id=>transaction_id, data_name => 'OrgName');
847 --
848 open get_old_value(l_posting_content_id);
849 fetch get_old_value into l_old_value;
850 close get_old_value;
851 --
852 irc_approvals.log('Comparing old org value :' || l_old_value
853 || ': to new org value :' || l_new_value || ':');
854 --
855 if(nvl(l_new_value,hr_api.g_varchar2)
856 <> nvl(l_old_value,hr_api.g_varchar2) ) then
857 l_retval:='true';
858 end if;
859 end if;
860 --
861 irc_approvals.log('Exiting get_posting_department_changed returning :' || l_retval || ':');
862 --
863 return l_retval;
864 end get_posting_department_changed;
865 --
866 -- -------------------------------------------------------------------------
867 -- |------------------< get_posting_dept_desc_changed >--------------------|
868 -- -------------------------------------------------------------------------
869 --
870 function get_posting_dept_desc_changed
871 (transaction_id in varchar2)
872 return varchar2 is
873 l_new_value varchar2(32000);
874 l_posting_content_id number;
875 l_old_value varchar2(32000);
876 l_clob_old_value irc_posting_contents_vl.org_description%type;
877 l_retval varchar2(30);
878 cursor get_old_value(p_posting_content_id number) is
879 select org_description
880 from irc_posting_contents_vl
881 where posting_content_id=p_posting_content_id;
882 begin
883 --
884 irc_approvals.log('Entering get_posting_dept_desc_changed');
885 --
886 l_retval := 'false';
887 --
888 if irc_approvals.get_transaction_mode(transaction_id) <> 'INSERT' then
889 --
890 l_posting_content_id := irc_approvals.get_posting_data_number
891 (transaction_id=>transaction_id, data_name=>'PostingContentId');
892 --
893 l_new_value:=irc_approvals.get_posting_data_varchar
894 (transaction_id=>transaction_id, data_name => 'OrgDescription');
895 --
896 open get_old_value(l_posting_content_id);
897 fetch get_old_value into l_clob_old_value;
898 close get_old_value;
899 --
900 l_old_value := dbms_lob.substr(l_clob_old_value);
901 --
902 irc_approvals.log('Comparing old org value :' || l_old_value
903 || ': to new org value :' || l_new_value || ':');
904 --
905 if(nvl(l_new_value,hr_api.g_varchar2)
906 <> nvl(l_old_value,hr_api.g_varchar2) ) then
907 l_retval:='true';
908 end if;
909 end if;
910 --
911 irc_approvals.log('Exiting get_posting_dept_desc_changed returning :' || l_retval || ':');
912 --
913 return l_retval;
914 end get_posting_dept_desc_changed;
915 --
916 -- -------------------------------------------------------------------------
917 -- |-------------------< get_brief_description_changed >-------------------|
918 -- -------------------------------------------------------------------------
919 --
920 function get_brief_description_changed
921 (transaction_id in varchar2)
922 return varchar2 is
923 l_new_value varchar2(32000);
924 l_posting_content_id number;
925 l_old_value varchar2(32000);
926 l_clob_old_value irc_posting_contents_vl.brief_description%type;
927 l_retval varchar2(30);
928 cursor get_old_value(p_posting_content_id number) is
929 select brief_description
930 from irc_posting_contents_vl
931 where posting_content_id=p_posting_content_id;
932 begin
933 --
934 irc_approvals.log('Entering get_brief_description_changed');
935 --
936 l_retval := 'false';
937 --
938 if irc_approvals.get_transaction_mode(transaction_id)<> 'INSERT' then
939 --
940 l_posting_content_id := irc_approvals.get_posting_data_number
941 (transaction_id=>transaction_id, data_name=>'PostingContentId');
942 --
943 l_new_value:=irc_approvals.get_posting_data_varchar
944 (transaction_id=>transaction_id ,data_name => 'BriefDescription');
945 --
946 open get_old_value(l_posting_content_id);
947 fetch get_old_value into l_clob_old_value;
948 close get_old_value;
949
950 l_old_value := dbms_lob.substr(l_clob_old_value);
951 --
952 irc_approvals.log('Comparing old org value :' || l_old_value
953 || ': to new org value :' || l_new_value || ':');
954 --
955 if(nvl(l_new_value,hr_api.g_varchar2)
956 <> nvl(l_old_value,hr_api.g_varchar2) ) then
957 l_retval:='true';
958 end if;
959 end if;
960 --
961 irc_approvals.log('Exiting get_brief_description_changed returning :' || l_retval || ':');
962 --
963 return l_retval;
964 end get_brief_description_changed;
965 --
966 -- -------------------------------------------------------------------------
967 -- |----------------------< get_detailed_desc_changed >--------------------|
968 -- -------------------------------------------------------------------------
969 --
970 function get_detailed_desc_changed
971 (transaction_id in varchar2)
972 return varchar2 is
973 l_new_value varchar2(32000);
974 l_posting_content_id number;
975 l_old_value varchar2(32000);
976 l_clob_old_value irc_posting_contents_vl.detailed_description%type;
977 l_retval varchar2(30);
978 cursor get_old_value(p_posting_content_id number) is
979 select detailed_description
980 from irc_posting_contents_vl
981 where posting_content_id=p_posting_content_id;
982 begin
983 --
984 irc_approvals.log('Entering get_detailed_desc_changed');
985 --
986 l_retval := 'false';
987 --
988 if irc_approvals.get_transaction_mode(transaction_id) <> 'INSERT' then
989 --
990 l_posting_content_id := irc_approvals.get_posting_data_number
991 (transaction_id=>transaction_id, data_name=>'PostingContentId');
992 --
993 l_new_value:=irc_approvals.get_posting_data_varchar
994 (transaction_id=>transaction_id ,data_name => 'DetailedDescription');
995 --
996 open get_old_value(l_posting_content_id);
997 fetch get_old_value into l_clob_old_value;
998 close get_old_value;
999 l_old_value := dbms_lob.substr(l_clob_old_value);
1000 --
1001 irc_approvals.log('Comparing old org value :' || l_old_value
1002 || ': to new org value :' || l_new_value || ':');
1003 --
1004 if(nvl(l_new_value,hr_api.g_varchar2)
1005 <> nvl(l_old_value,hr_api.g_varchar2) ) then
1006 l_retval:='true';
1007 end if;
1008 end if;
1009 --
1010 irc_approvals.log('Exiting get_detailed_desc_changed returning :' || l_retval || ':');
1011 --
1012 return l_retval;
1013 end get_detailed_desc_changed;
1014 --
1015 -- -------------------------------------------------------------------------
1016 -- |----------------------< get_job_requirements_changed >----------------|
1017 -- -------------------------------------------------------------------------
1018 --
1019 function get_job_requirements_changed
1020 (transaction_id in varchar2)
1021 return varchar2 is
1022 l_new_value varchar2(32000);
1023 l_posting_content_id number;
1024 l_old_value varchar2(32000);
1025 l_clob_old_value irc_posting_contents_vl.job_requirements%type;
1026 l_retval varchar2(30);
1027 cursor get_old_value(p_posting_content_id number) is
1028 select job_requirements
1029 from irc_posting_contents_vl
1030 where posting_content_id=p_posting_content_id;
1031 begin
1032 --
1033 irc_approvals.log('Entering get_job_requirements_changed');
1034 --
1035 l_retval := 'false';
1036 --
1037 if irc_approvals.get_transaction_mode(transaction_id) <> 'INSERT' then
1038 --
1039 l_posting_content_id := irc_approvals.get_posting_data_number
1040 (transaction_id=>transaction_id, data_name=>'PostingContentId');
1041 --
1042 l_new_value:=irc_approvals.get_posting_data_varchar
1043 (transaction_id=>transaction_id ,data_name => 'JobRequirements');
1044 --
1045 open get_old_value(l_posting_content_id);
1046 fetch get_old_value into l_clob_old_value;
1047 close get_old_value;
1048 l_old_value := dbms_lob.substr(l_clob_old_value);
1049 --
1050 irc_approvals.log('Comparing old org value :' || l_old_value
1051 || ': to new org value :' || l_new_value || ':');
1052 --
1053 if(nvl(l_new_value,hr_api.g_varchar2)
1054 <> nvl(l_old_value,hr_api.g_varchar2) ) then
1055 l_retval:='true';
1056 end if;
1057 end if;
1058 --
1059 irc_approvals.log('Exiting get_job_requirements_changed returning :' || l_retval || ':');
1060 --
1061 return l_retval;
1062 end get_job_requirements_changed;
1063 --
1064 -- -------------------------------------------------------------------------
1065 -- |---------------------< get_additional_details_changed >----------------|
1066 -- -------------------------------------------------------------------------
1067 --
1068 function get_additional_details_changed
1069 (transaction_id in varchar2)
1070 return varchar2 is
1071 l_new_value varchar2(32000);
1072 l_posting_content_id number;
1073 l_old_value varchar2(32000);
1074 l_clob_old_value irc_posting_contents_vl.additional_details%type;
1075 l_retval varchar2(30);
1076 cursor get_old_value(p_posting_content_id number) is
1077 select additional_details
1078 from irc_posting_contents_vl
1079 where posting_content_id=p_posting_content_id;
1080 begin
1081 --
1082 irc_approvals.log('Entering get_additional_details_changed');
1083 --
1084 l_retval := 'false';
1085 --
1086 if irc_approvals.get_transaction_mode(transaction_id) <> 'INSERT' then
1087 --
1088 l_posting_content_id := irc_approvals.get_posting_data_number
1089 (transaction_id=>transaction_id, data_name=>'PostingContentId');
1090 --
1091 l_new_value:=irc_approvals.get_posting_data_varchar
1092 (transaction_id=>transaction_id ,data_name => 'AdditionalDetails');
1093 --
1094 open get_old_value(l_posting_content_id);
1095 fetch get_old_value into l_clob_old_value;
1096 close get_old_value;
1097 l_old_value := dbms_lob.substr(l_clob_old_value);
1098 --
1099 irc_approvals.log('Comparing old org value :' || l_old_value
1100 || ': to new org value :' || l_new_value || ':');
1101 --
1102 if(nvl(l_new_value,hr_api.g_varchar2)
1103 <> nvl(l_old_value,hr_api.g_varchar2) ) then
1104 l_retval:='true';
1105 end if;
1106 end if;
1107 --
1108 irc_approvals.log('Exiting get_additional_details_changed returning :' || l_retval || ':');
1109 --
1110 return l_retval;
1111 end get_additional_details_changed;
1112 --
1113 -- -------------------------------------------------------------------------
1114 -- |-----------------------< get_how_to_apply_changed >--------------------|
1115 -- -------------------------------------------------------------------------
1116 --
1117 function get_how_to_apply_changed
1118 (transaction_id in varchar2)
1119 return varchar2 is
1120 l_new_value varchar2(32000);
1121 l_posting_content_id number;
1122 l_old_value varchar2(32000);
1123 l_clob_old_value irc_posting_contents_vl.how_to_apply%type;
1124 l_retval varchar2(30);
1125 cursor get_old_value(p_posting_content_id number) is
1126 select how_to_apply
1127 from irc_posting_contents_vl
1128 where posting_content_id=p_posting_content_id;
1129 begin
1130 --
1131 irc_approvals.log('Entering get_how_to_apply_changed');
1132 --
1133 l_retval := 'false';
1134 --
1135 if irc_approvals.get_transaction_mode(transaction_id) <> 'INSERT' then
1136 --
1137 l_posting_content_id := irc_approvals.get_posting_data_number
1138 (transaction_id=>transaction_id, data_name=>'PostingContentId');
1139 --
1140 l_new_value:=irc_approvals.get_posting_data_varchar
1141 (transaction_id=>transaction_id ,data_name => 'HowToApply');
1142 --
1143 open get_old_value(l_posting_content_id);
1144 fetch get_old_value into l_clob_old_value;
1145 close get_old_value;
1146 l_old_value := dbms_lob.substr(l_clob_old_value);
1147 --
1148 irc_approvals.log('Comparing old org value :' || l_old_value
1149 || ': to new org value :' || l_new_value || ':');
1150 --
1151 if(nvl(l_new_value,hr_api.g_varchar2)
1152 <> nvl(l_old_value,hr_api.g_varchar2) ) then
1153 l_retval:='true';
1154 end if;
1155 end if;
1156 --
1157 irc_approvals.log('Exiting get_how_to_apply_changed returning :' || l_retval || ':');
1158 --
1159 return l_retval;
1160 end get_how_to_apply_changed;
1161 --
1162 -- -------------------------------------------------------------------------
1163 -- |-----------------------< get_posting_graphic_changed >-----------------|
1164 -- -------------------------------------------------------------------------
1165 --
1166 function get_posting_graphic_changed
1167 (transaction_id in varchar2)
1168 return varchar2 is
1169 --
1170 l_new_value varchar2(32000);
1171 l_posting_content_id number;
1172 l_old_value varchar2(32000);
1173 l_clob_old_value irc_posting_contents_vl.image_url%type;
1174 l_retval varchar2(30);
1175 --
1176 cursor get_old_value(p_posting_content_id number) is
1177 select image_url
1178 from irc_posting_contents_vl
1179 where posting_content_id=p_posting_content_id;
1180 --
1181 BEGIN
1182 --
1183 irc_approvals.log('Entering get_posting_graphic_changed');
1184 --
1185 l_retval := 'false';
1186 --
1187 if irc_approvals.get_transaction_mode(transaction_id)<> 'INSERT' then
1188 --
1189 l_new_value := irc_approvals.get_posting_data_varchar
1190 (transaction_id=>transaction_id ,data_name => 'ImageUrl');
1191 --
1192 l_posting_content_id := irc_approvals.get_posting_data_number
1193 (transaction_id=>transaction_id, data_name=>'PostingContentId');
1194 open get_old_value(l_posting_content_id);
1195 fetch get_old_value into l_clob_old_value;
1196 close get_old_value;
1197 l_old_value := dbms_lob.substr(l_clob_old_value);
1198 --
1199 irc_approvals.log('Comparing old org value :' || l_old_value
1200 || ': to new org value :' || l_new_value || ':');
1201 --
1202 if(nvl(l_new_value,hr_api.g_varchar2)
1203 <> nvl(l_old_value,hr_api.g_varchar2) ) then
1204 l_retval:='true';
1205 end if;
1206 --
1207 end if;
1208 --
1209 irc_approvals.log('Exiting get_posting_graphic_changed returning :' || l_retval || ':');
1210 --
1211 return l_retval;
1212 --
1213 end get_posting_graphic_changed;
1214 --
1215 -- -------------------------------------------------------------------------
1216 -- |-------------------------< get_ovn_changed >---------------------------|
1217 -- -------------------------------------------------------------------------
1218 --
1219 function get_ovn_changed
1220 (transaction_id in varchar2)
1221 return varchar2 is
1222 l_new_id number;
1223 l_vacancy_id number;
1224 l_old_id number;
1225 --
1226 l_retval varchar2(30);
1227 cursor get_old_id(p_vacancy_id number) is
1228 select object_version_number
1229 from per_all_vacancies
1230 where vacancy_id=p_vacancy_id;
1231 begin
1232 --
1233 irc_approvals.log('Entering get_ovn_changed');
1234 --
1235 l_retval := 'false';
1236 --
1237 if irc_approvals.get_transaction_mode(transaction_id) <> 'INSERT' then
1238 --
1239 l_new_id:=irc_approvals.get_vacancy_data_number(transaction_id=>transaction_id,data_name=>'ObjectVersionNumber');
1240 --
1241 l_vacancy_id:=irc_approvals.get_vacancy_data_number(transaction_id=>transaction_id,data_name=>'VacancyId');
1242 open get_old_id(l_vacancy_id);
1243 fetch get_old_id into l_old_id;
1244 close get_old_id;
1245 --
1246 irc_approvals.log('Comparing old org id :' || to_char(l_old_id)
1247 || ': to new org id :' || to_char(l_new_id) || ':');
1248 --
1249 if(nvl(l_new_id,hr_api.g_number)
1250 <> nvl(l_old_id,hr_api.g_number) ) then
1251 l_retval:='true';
1252 end if;
1253 --
1254 end if;
1255 --
1256 irc_approvals.log('Exiting get_ovn_changed returning :' || l_retval || ':');
1257 --
1258 return l_retval;
1259 end get_ovn_changed;
1260 --
1261 -- ----------------------------------------------------------------------------
1262 -- getTopApprover --
1263 -- This function is accessed by mandatory OAM Attribute --
1264 -- TOP_SUPERVISOR_PERSON_ID to retrieve the top supervisor in the chain. --
1265 -- ----------------------------------------------------------------------------
1266 --
1267 FUNCTION getTopOffersApprover(transaction_id in varchar2)
1268 return fnd_user.user_id%type is
1269
1270 p_creator_person_id per_all_people_f.person_id%type default null;
1271 c_approver_id per_all_people_f.person_id%type default null;
1272 c_top_approver_id per_all_people_f.person_id%type default null;
1273
1274 cursor csr_app(c_person_id per_people_f.person_id%TYPE) is
1275 select supervisor_id
1276 from per_all_assignments_f
1277 where person_id = c_person_id
1278 and primary_flag = 'Y'
1279 and trunc(sysdate)
1280 between effective_start_date
1281 and effective_end_date;
1282 --
1283 cursor csr_trans(c_transaction_id hr_api_transactions.transaction_id%TYPE) is
1284 select creator_person_id
1285 from hr_api_transactions
1286 where transaction_id = c_transaction_id;
1287 --
1288 BEGIN
1289 --
1290 open csr_trans(transaction_id);
1291 fetch csr_trans into p_creator_person_id;
1292 if csr_trans%notfound then
1293 close csr_trans;
1294 end if;
1295 --
1296 c_top_approver_id := p_creator_person_id;
1297
1298 for i2 in 1..3 loop
1299 open csr_app(c_top_approver_id);
1300 fetch csr_app into c_approver_id;
1301 if csr_app%notfound then
1302 close csr_app;
1303 return c_top_approver_id;
1304 end if;
1305 c_top_approver_id := nvl(c_approver_id, c_top_approver_id);
1306 close csr_app;
1307 end loop;
1308 --
1309 return c_top_approver_id;
1310 --
1311 END getTopOffersApprover;
1312 --
1313 FUNCTION getTopApprover(transaction_id in varchar2)
1314 return fnd_user.user_id%type is
1315 --
1316 BEGIN
1317 --
1318 return getTopOffersApprover(transaction_id);
1319 --
1320 END getTopApprover;
1321 --
1322 procedure log (message in varchar2) is
1323 --
1324 BEGIN
1325 --
1326 hr_utility.trace(message);
1327 --
1328 end log;
1329 --
1330 --
1331 procedure check_self_approval
1332 (p_application_id in number
1333 ,p_transaction_type in varchar2
1334 ,p_transaction_id in varchar2
1335 ,p_number_of_approvers out nocopy number
1336 )
1337 is
1338 approvalComplete varchar2(20) := ame_util.booleanFalse;
1339 approvers ame_util.approversTable2;
1340
1341 begin
1342 --
1343 irc_approvals.log('Entering check_self_approval');
1344 --
1345 ame_api2.getAllApprovers7
1346 (applicationIdIn => p_application_id
1347 ,transactionTypeIn => p_transaction_type
1348 ,transactionIdIn => p_transaction_id
1349 ,approvalProcessCompleteYNOut => approvalComplete
1350 ,approversOut => approvers);
1351
1352 p_number_of_approvers := approvers.count;
1353 if p_number_of_approvers = 1 then
1354 if fnd_global.user_name = approvers(1).name then
1355 p_number_of_approvers := 0;
1356 end if;
1357 end if;
1358 irc_approvals.log('Exiting check_self_approval');
1359 --
1360 end check_self_approval;
1361 --
1362 procedure getNotifSubjectForCreate
1363 (document_id in varchar2,
1364 display_type in varchar2,
1365 document in out nocopy varchar2,
1366 document_type in out nocopy varchar2) is
1367 begin
1368 --
1369 getNotificationSubject (
1370 document_id => document_id
1371 ,display_type => display_type
1372 ,document => document
1373 ,document_type => document_type
1374 ,flowmode => 'CREATE');
1375 --
1376 end getNotifSubjectForCreate;
1377 --
1378 procedure getNotifSubjectForEdit
1379 (document_id in varchar2,
1380 display_type in varchar2,
1381 document in out nocopy varchar2,
1382 document_type in out nocopy varchar2) is
1383 begin
1384 --
1385 getNotificationSubject (
1386 document_id => document_id
1387 ,display_type => display_type
1388 ,document => document
1389 ,document_type => document_type
1390 ,flowmode => 'EDIT');
1391 --
1392 end getNotifSubjectForEdit;
1393 --
1394 procedure getNotificationSubject (document_id in varchar2,
1395 display_type in varchar2,
1396 document in out nocopy varchar2,
1397 document_type in out nocopy varchar2,
1398 flowmode in varchar2) is
1399 --
1400 l_vacancy_name per_all_vacancies.name%type;
1401 l_transaction_id hr_api_transactions.transaction_id%type;
1402 l_originators_name per_all_people_f.full_name%type;
1403 --
1404 Begin
1405 --
1406 hr_utility.trace('Fetching transaction id :');
1407 --
1408 l_transaction_id := wf_notification.getattrnumber(document_id,'HR_TRANSACTION_REF_ID_ATTR');
1409 if l_transaction_id is null then
1410 --
1411 if flowmode = 'CREATE' then
1412 fnd_message.set_name('PER','IRC_VACANCY_APPROVAL_NEW');
1413 document := fnd_message.get;
1414 else
1415 fnd_message.set_name('PER','IRC_VACANCY_APPROVAL_UPDATE');
1416 document := fnd_message.get;
1417 end if;
1418 --
1419 else
1420 --
1421 l_vacancy_name :=
1422 irc_approvals.get_vacancy_data_varchar(
1423 transaction_id => l_transaction_id,
1424 data_name => 'Name');
1425 --
1426 l_originators_name :=
1427 getPersonNameFromID(
1428 irc_approvals.get_transaction_number_data (
1429 transaction_id => to_char(l_transaction_id),
1430 p_path => '/Transaction/TransCtx/CNode/loggedInPersonId'));
1431 --
1432 if(wf_notification.isFYI(document_id))then
1433 if flowmode = 'CREATE' then
1434 fnd_message.set_name('PER','IRC_412604_VAC_FYI_CREATE');
1435 else
1436 fnd_message.set_name('PER','IRC_412605_VAC_FYI_EDIT');
1437 end if;
1438 else
1439 fnd_message.set_name('PER','IRC_VACANCY_APPROVAL_' || flowmode);
1440 end if;
1441 fnd_message.set_token('PERSONNAME', l_originators_name, false);
1442 fnd_message.set_token('VACANCYNAME', l_vacancy_name, false);
1443 document := fnd_message.get;
1444 --
1445 --
1446 end if;
1447 --
1448 end getNotificationSubject;
1449 --
1450 -- ----------------------------------------------------------------------------
1451 -- getPersonNameFromID --
1452 -- called internally to give the person name for the given user name --
1453 -- ----------------------------------------------------------------------------
1454 --
1455 FUNCTION getPersonNameFromID
1456 (p_person_id per_all_people_f.person_id%type)
1457 return per_all_people_f.full_name%type is
1458 --
1459 cursor csr_full_name
1460 is select full_name
1461 from per_all_people_f papf
1462 where papf.person_id = p_person_id
1463 and trunc(sysdate) between effective_start_date
1464 and effective_end_date;
1465 --
1466 l_employee_name per_all_people_f.full_name%type;
1467 --
1468 BEGIN
1469 --
1470 hr_utility.trace('Finding Person name for person_id :' || p_person_id || ':');
1471 --
1472 open csr_full_name;
1473 fetch csr_full_name into l_employee_name;
1474 --
1475 if csr_full_name%notfound then
1476 l_employee_name := ' ';
1477 end if;
1478 close csr_full_name;
1479 --
1480 hr_utility.trace('Found :' || l_employee_name || ':');
1481 --
1482 return l_employee_name;
1483 --
1484 END getPersonNameFromID;
1485 --
1486 --
1487 procedure getOfferNotifSubjectForCreate
1488 (document_id in varchar2,
1489 display_type in varchar2,
1490 document in out nocopy varchar2,
1491 document_type in out nocopy varchar2) is
1492 begin
1493 --
1494 getOfferNotificationSubject (
1495 document_id => document_id
1496 ,display_type => display_type
1497 ,document => document
1498 ,document_type => document_type
1499 ,flowmode => 'CREATE');
1500 --
1501 end getOfferNotifSubjectForCreate;
1502 --
1503 procedure getOfferNotifSubjectForEdit
1504 (document_id in varchar2,
1505 display_type in varchar2,
1506 document in out nocopy varchar2,
1507 document_type in out nocopy varchar2) is
1508 begin
1509 --
1510 getOfferNotificationSubject (
1511 document_id => document_id
1512 ,display_type => display_type
1513 ,document => document
1514 ,document_type => document_type
1515 ,flowmode => 'UPDATE');
1516 --
1517 end getOfferNotifSubjectForEdit;
1518 --
1519 procedure getOfferNotificationSubject (document_id in varchar2,
1520 display_type in varchar2,
1521 document in out nocopy varchar2,
1522 document_type in out nocopy varchar2,
1523 flowmode in varchar2) is
1524 --
1525 l_vacancy_name per_all_vacancies.name%type;
1526 l_transaction_id hr_api_transactions.transaction_id%type;
1527 l_applicant_name per_all_people_f.full_name%type;
1528 --
1529 Begin
1530 --
1531 hr_utility.trace('Fetching transaction id :');
1532 --
1533 l_transaction_id := wf_notification.getattrnumber(document_id,'HR_TRANSACTION_REF_ID_ATTR');
1534 if l_transaction_id is null then
1535 --
1536 if flowmode = 'CREATE' then
1537 fnd_message.set_name('PER','IRC_CREATE_OFFER_APPROVAL');
1538 document := fnd_message.get;
1539 else
1540 fnd_message.set_name('PER','IRC_UPDATE_OFFER_APPROVAL');
1541 document := fnd_message.get;
1542 end if;
1543 --
1544 else
1545 --
1546 l_vacancy_name :=
1547 getVacancyNameFromId(
1548 getVacancyIdFromAssignmentId(
1549 irc_approvals.get_transaction_number_data (
1550 transaction_id => to_char(l_transaction_id),
1551 p_path => '/Transaction/TransCtx/PrsnAssignmentId')));
1552 --
1553 l_applicant_name :=
1554 getPersonNameFromID(
1555 irc_approvals.get_transaction_number_data (
1556 transaction_id => to_char(l_transaction_id),
1557 p_path => '/Transaction/TransCtx/PrsnId'));
1558 --
1559 if flowmode = 'CREATE' then
1560 if(wf_notification.isFYI(document_id))then
1561 fnd_message.set_name('PER','IRC_412610_OFFER_FYI_CREATE');
1562 else
1563 fnd_message.set_name('PER','IRC_412390_OFFER_APPR_CREATE');
1564 end if;
1565 else
1566 if(wf_notification.isFYI(document_id))then
1567 fnd_message.set_name('PER','IRC_412611_OFFER_FYI_EDIT');
1568 else
1569 fnd_message.set_name('PER','IRC_412391_OFFER_APPR_EDIT');
1570 end if;
1571 end if;
1572 fnd_message.set_token('PERSON_NAME', l_applicant_name, false);
1573 fnd_message.set_token('VACANCY_NAME', l_vacancy_name, false);
1574 document := fnd_message.get;
1575 --
1576 --
1577 end if;
1578 --
1579 end getOfferNotificationSubject;
1580
1581 function getVacancyNameFromId(vacancy_id number)
1582 return varchar
1583 is
1584 vac_name per_all_vacancies.name%type;
1585 cursor vac_name_from_id (vac_id number) is
1586 select name
1587 from per_all_vacancies
1588 where vacancy_id=vac_id;
1589 begin
1590
1591 open vac_name_from_id(vacancy_id);
1592 fetch vac_name_from_id into vac_name;
1593 close vac_name_from_id;
1594
1595 return vac_name;
1596
1597 end getVacancyNameFromId;
1598
1599 function getVacancyIdFromAssignmentId(assignment_id number)
1600 return number
1601 is
1602 vac_id per_all_vacancies.vacancy_id%type;
1603 cursor vac_id_from_assn_id (assn_id number) is
1604 select vacancy_id
1605 from per_all_assignments_f
1606 where assignment_id=assn_id;
1607 begin
1608
1609 open vac_id_from_assn_id(assignment_id);
1610 fetch vac_id_from_assn_id into vac_id;
1611 close vac_id_from_assn_id;
1612
1613 return vac_id;
1614
1615 end getVacancyIdFromAssignmentId;
1616
1617
1618 END IRC_APPROVALS;