[Home] [Help]
PACKAGE BODY: APPS.IRC_VACANCY_APPROVALS
Source
1 Package BODY IRC_VACANCY_APPROVALS as
2 /* $Header: ircvacame.pkb 120.0 2006/03/31 23:28:53 mmillmor noship $ */
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
11 --
12 -- -------------------------------------------------------------------------
13 -- |------------------------< get_transaction_data >-----------------------|
14 -- -------------------------------------------------------------------------
15 --
16 function get_transaction_data
17 (p_transaction_id in varchar2
18 ,p_path in varchar2)
19 return varchar2 is
20 --
21 l_retval varchar2(32000);
22 transactionDoc hr_api_transactions.transaction_document%type;
23 --
24 cursor get_doc is
25 select transaction_document
26 from hr_api_transactions
27 where transaction_id = p_transaction_id;
28 --
29 begin
30 --
31 irc_vacancy_approvals.show('Entering get_transaction_data for transaction_id :' || p_transaction_id || ':');
32 irc_vacancy_approvals.show('Access path :' || p_path || ':');
33 --
34 open get_doc;
35 fetch get_doc into transactionDoc;
36 if get_doc %notfound then
37 close get_doc;
38 l_retval:=null;
39 else
40 close get_doc;
41 l_retval:=irc_xml_util.valueOf(transactionDoc,p_path);
42 end if;
43 --
44 irc_vacancy_approvals.show('Exiting get_transaction_data returning :' || l_retval || ':');
45 --
46 return l_retval;
47 --
48 end get_transaction_data;
49 --
50 -- -------------------------------------------------------------------------
51 -- |---------------------< get_transaction_number_data >-------------------|
52 -- -------------------------------------------------------------------------
53 --
54 function get_transaction_number_data
55 (transaction_id in varchar2
56 ,p_path in varchar2)
57 return number is
58 l_retval varchar2(32000);
59 ln_retval number;
60 --
61 begin
62 --
63 irc_vacancy_approvals.show('Entering get_transaction_number_data');
64 --
65 l_retval:=irc_vacancy_approvals.get_transaction_data
66 (p_transaction_id =>transaction_id
67 ,p_path =>p_path);
68 ln_retval:=to_number(l_retval);
69 --
70 irc_vacancy_approvals.show('Exiting get_transaction_number_data');
71 --
72 return ln_retval;
73 end get_transaction_number_data;
74 --
75 -- -------------------------------------------------------------------------
76 --
77 function get_transaction_mode
78 (transaction_id in varchar2)
79 --
80 return varchar2 is
81 l_retval varchar2(200);
82 begin
83 --
84 irc_vacancy_approvals.show('Entering get_transaction_mode');
85 --
86 l_retval:=irc_vacancy_approvals.get_transaction_data
87 (p_transaction_id=>transaction_id
88 ,p_path => '/Transaction/TransCtx/CNode/dmlMode');
89 --
90 irc_vacancy_approvals.show('Exiting get_transaction_mode');
91 --
92 return l_retval;
93 end get_transaction_mode;
94 --
95 -- -------------------------------------------------------------------------
96 --
97 function get_posting_data_number
98 (transaction_id in varchar2, data_name in varchar2)
99 return number is
100 l_retval number;
101 begin
102 --
103 irc_vacancy_approvals.show('Entering get_posting_content_id');
104 --
105 l_retval:=irc_vacancy_approvals.get_transaction_number_data
106 (transaction_id=>transaction_id,p_path => g_posting_path || '/' || data_name);
107 --
108 irc_vacancy_approvals.show('Exiting get_posting_content_id');
109 --
110 return l_retval;
111 end get_posting_data_number;
112 --
113 -- -------------------------------------------------------------------------
114 --
115 function get_posting_data_varchar
116 (transaction_id in varchar2, data_name in varchar2)
117 return varchar2 is
118 l_retval varchar2(200);
119 begin
120 --
121 irc_vacancy_approvals.show('Entering get_posting_content_id');
122 --
123 l_retval:=irc_vacancy_approvals.get_transaction_data
124 (p_transaction_id=>transaction_id,p_path => g_posting_path || '/' || data_name);
125 --
126 irc_vacancy_approvals.show('Exiting get_posting_content_id');
127 --
128 return l_retval;
129 end get_posting_data_varchar;
130 --
131 -- -------------------------------------------------------------------------
132 --
133 function get_vacancy_data_number
134 (transaction_id in varchar2, data_name in varchar2)
135 return number is
136 l_retval number;
137 begin
138 --
139 irc_vacancy_approvals.show('Entering get_vac_business_group_id');
140 --
141 l_retval:=irc_vacancy_approvals.get_transaction_number_data
142 (transaction_id=>transaction_id
143 ,p_path => g_vacancy_path || '/' || data_name);
144 --
145 irc_vacancy_approvals.show('Exiting get_vac_business_group_id');
146 --
147 return l_retval;
148 end get_vacancy_data_number;
149 --
150 -- -------------------------------------------------------------------------
151 --
152 function get_vacancy_data_varchar
153 (transaction_id in varchar2, data_name in varchar2)
154 return varchar2 is
155 l_retval varchar2(200);
156 begin
157 --
158 irc_vacancy_approvals.show('Entering get_vac_budget_type');
159 --
160 l_retval:=irc_vacancy_approvals.get_transaction_data
161 (p_transaction_id=>transaction_id
162 ,p_path => g_vacancy_path || '/' || data_name);
163 --
164 irc_vacancy_approvals.show('Exiting get_vac_budget_type');
165 --
166 return l_retval;
167 end get_vacancy_data_varchar;
168 --
169 -- -------------------------------------------------------------------------
170 --
171 function get_search_data_number
172 (transaction_id in varchar2, data_name in varchar2)
173 return number is
174 l_retval number;
175 begin
176 --
177 irc_vacancy_approvals.show('Entering get_search_data_number');
178 --
179 l_retval:=irc_vacancy_approvals.get_transaction_number_data
180 (transaction_id=>transaction_id,p_path => g_search_path || '/' || data_name);
181 --
182 irc_vacancy_approvals.show('Exiting get_search_data_number');
183 --
184 return l_retval;
185 end get_search_data_number;
186 --
187 -- -------------------------------------------------------------------------
188 --
189 function get_search_data_varchar
190 (transaction_id in varchar2, data_name in varchar2)
191 return varchar2 is
192 l_retval varchar2(200);
193 begin
194 --
195 irc_vacancy_approvals.show('Entering get_search_data_varchar');
196 --
197 l_retval:=irc_vacancy_approvals.get_transaction_data
198 (p_transaction_id=>transaction_id,p_path => g_search_path || '/' || data_name);
199 --
200 irc_vacancy_approvals.show('Exiting get_search_data_varchar');
201 --
202 return l_retval;
203 end get_search_data_varchar;
204 --
205 -- -------------------------------------------------------------------------
206 -- |----------------------< get_vac_organization_changed >-----------------|
207 -- -------------------------------------------------------------------------
208 --
209 function get_vac_organization_changed
210 (transaction_id in varchar2)
211 return varchar2 is
212 l_new_id number;
213 l_vacancy_id number;
214 l_old_id number;
215 --
216 l_retval varchar2(30);
217 cursor get_old_id(p_vacancy_id number) is
218 select organization_id
219 from per_all_vacancies
220 where vacancy_id=p_vacancy_id;
221 begin
222 --
223 irc_vacancy_approvals.show('Entering get_vac_organization_changed');
224 --
225 l_retval := 'false';
226 --
227 if irc_vacancy_approvals.get_transaction_mode(transaction_id) <> 'INSERT' then
228 --
229 l_new_id := irc_vacancy_approvals.get_vacancy_data_number(transaction_id=>transaction_id,data_name=>'OrganizationId');
230 --
231 l_vacancy_id:=irc_vacancy_approvals.get_vacancy_data_number(transaction_id=>transaction_id,data_name=>'VacancyId');
232 open get_old_id(l_vacancy_id);
233 fetch get_old_id into l_old_id;
234 close get_old_id;
235 --
236 irc_vacancy_approvals.show('Comparing old org id :' || to_char(l_old_id)
237 || ': to new org id :' || to_char(l_new_id) || ':');
238 --
239 if(nvl(l_new_id,hr_api.g_number)
240 <> nvl(l_old_id,hr_api.g_number) ) then
241 l_retval:='true';
242 end if;
243 end if;
244 --
245 irc_vacancy_approvals.show('Exiting get_vac_organization_changed returning :' || l_retval || ':');
246 --
247 return l_retval;
248 end get_vac_organization_changed;
249 --
250 -- -------------------------------------------------------------------------
251 -- |----------------------< get_vac_job_changed >--------------------------|
252 -- -------------------------------------------------------------------------
253 --
254 function get_vac_job_changed
255 (transaction_id in varchar2)
256 return varchar2 is
257 l_new_id number;
258 l_vacancy_id number;
259 l_old_id number;
260 --
261 l_retval varchar2(30);
262 cursor get_old_id(p_vacancy_id number) is
263 select job_id
264 from per_all_vacancies
265 where vacancy_id=p_vacancy_id;
266 begin
267 --
268 irc_vacancy_approvals.show('Entering get_vac_job_changed');
269 --
270 l_retval := 'false';
271 --
272 if irc_vacancy_approvals.get_transaction_mode(transaction_id) <> 'INSERT' then
273 l_vacancy_id:=irc_vacancy_approvals.get_vacancy_data_number(transaction_id=>transaction_id,data_name=>'VacancyId');
274 l_new_id:=irc_vacancy_approvals.get_vacancy_data_number(transaction_id=>transaction_id,data_name=>'JobId');
275 open get_old_id(l_vacancy_id);
276 fetch get_old_id into l_old_id;
277 close get_old_id;
278 --
279 irc_vacancy_approvals.show('Comparing old job id :' || to_char(l_old_id)
280 || ': to new job id :' || to_char(l_new_id) || ':');
281 --
282 if(nvl(l_new_id,hr_api.g_number)
283 <> nvl(l_old_id,hr_api.g_number) ) then
284 l_retval:='true';
285 end if;
286 end if;
287 --
288 irc_vacancy_approvals.show('Exiting get_vac_job_changed returning :' || l_retval || ':');
289 --
290 return l_retval;
291 end get_vac_job_changed;
292 --
293 -- -------------------------------------------------------------------------
294 -- |----------------------< get_vac_grade_changed >------------------------|
295 -- -------------------------------------------------------------------------
296 --
297 function get_vac_grade_changed
298 (transaction_id in varchar2)
299 return varchar2 is
300 l_new_id number;
301 l_vacancy_id number;
302 l_old_id number;
303 --
304 l_retval varchar2(30);
305 cursor get_old_id(p_vacancy_id number) is
306 select grade_id
307 from per_all_vacancies
308 where vacancy_id=p_vacancy_id;
309 begin
310 --
311 irc_vacancy_approvals.show('Entering get_vac_grade_changed');
312 --
313 l_retval := 'false';
314 --
315 if irc_vacancy_approvals.get_transaction_mode(transaction_id) <> 'INSERT' then
316 --
317 l_new_id:=irc_vacancy_approvals.get_vacancy_data_number(transaction_id=>transaction_id,data_name=>'GradeId');
318 --
319 l_vacancy_id:=irc_vacancy_approvals.get_vacancy_data_number(transaction_id=>transaction_id,data_name=>'VacancyId');
320 open get_old_id(l_vacancy_id);
321 fetch get_old_id into l_old_id;
322 close get_old_id;
323 --
324 irc_vacancy_approvals.show('Comparing old org id :' || to_char(l_old_id)
325 || ': to new org id :' || to_char(l_new_id) || ':');
326 --
327 if(nvl(l_new_id,hr_api.g_number)
328 <> nvl(l_old_id,hr_api.g_number) ) then
329 l_retval:='true';
330 end if;
331 --
332 end if;
333 --
334 irc_vacancy_approvals.show('Exiting get_vac_grade_changed returning :' || l_retval || ':');
335 --
336 return l_retval;
337 end get_vac_grade_changed;
338 --
339 -- -------------------------------------------------------------------------
340 -- |----------------------< get_vac_position_changed >---------------------|
341 -- -------------------------------------------------------------------------
342 --
343 function get_vac_position_changed
344 (transaction_id in varchar2)
345 return varchar2 is
346 l_new_id number;
347 l_vacancy_id number;
351 cursor get_old_id(p_vacancy_id number) is
348 l_old_id number;
349 --
350 l_retval varchar2(30);
352 select position_id
353 from per_all_vacancies
354 where vacancy_id=p_vacancy_id;
355 begin
356 --
357 irc_vacancy_approvals.show('Entering get_vac_position_changed');
358 --
359 l_retval := 'false';
360 --
361 if irc_vacancy_approvals.get_transaction_mode(transaction_id) <> 'INSERT' then
362 --
363 l_new_id := irc_vacancy_approvals.get_vacancy_data_number(transaction_id=>transaction_id,data_name=>'PositionId');
364 --
365 l_vacancy_id := irc_vacancy_approvals.get_vacancy_data_number(transaction_id=>transaction_id,data_name=>'VacancyId');
366 open get_old_id(l_vacancy_id);
367 fetch get_old_id into l_old_id;
368 close get_old_id;
369 --
370 irc_vacancy_approvals.show('Comparing old org id :' || to_char(l_old_id)
371 || ': to new org id :' || to_char(l_new_id) || ':');
372 --
373 if(nvl(l_new_id,hr_api.g_number)
374 <>nvl(l_old_id,hr_api.g_number) ) then
375 l_retval:='true';
376 end if;
377 --
378 end if;
379 --
380 irc_vacancy_approvals.show('Exiting get_vac_position_changed returning :' || l_retval || ':');
381 --
382 return l_retval;
383 end get_vac_position_changed;
384 --
385 -- -------------------------------------------------------------------------
386 -- |--------------------< get_vac_budget_value_changed >-------------------|
387 -- -------------------------------------------------------------------------
388 --
389 function get_vac_budget_value_changed
390 (transaction_id in varchar2)
391 return varchar2 is
392 l_new_id number;
393 l_vacancy_id number;
394 l_old_id number;
395 --
396 l_retval varchar2(30);
397 cursor get_old_id(p_vacancy_id number) is
398 select fnd_number.number_to_canonical(budget_measurement_value)
399 from per_all_vacancies
400 where vacancy_id=p_vacancy_id;
401 begin
402 --
403 irc_vacancy_approvals.show('Entering get_vac_budget_value_changed');
404 --
405 l_retval := 'false';
406 --
407 if irc_vacancy_approvals.get_transaction_mode(transaction_id)<>'INSERT' then
408 --
409 l_new_id:=irc_vacancy_approvals.get_vacancy_data_number(transaction_id=>transaction_id,data_name=>'BudgetMeasurementValue');
410 --
411 l_vacancy_id:=irc_vacancy_approvals.get_vacancy_data_number(transaction_id=>transaction_id,data_name=>'VacancyId');
412 open get_old_id(l_vacancy_id);
413 fetch get_old_id into l_old_id;
414 close get_old_id;
415 --
416 irc_vacancy_approvals.show('Comparing old org id :' || to_char(l_old_id)
417 || ': to new org id :' || to_char(l_new_id) || ':');
418 --
419 if(nvl(l_new_id,hr_api.g_number)
420 <>nvl(l_old_id,hr_api.g_number) ) then
421 l_retval:='true';
422 end if;
423 --
424 end if;
425 --
426 irc_vacancy_approvals.show('Exiting get_vac_budget_value_changed returning :' || l_retval || ':');
427 --
428 return l_retval;
429 end get_vac_budget_value_changed;
430 --
431 -- -------------------------------------------------------------------------
432 -- |--------------------< get_vac_budget_type_changed >-------------------|
433 -- -------------------------------------------------------------------------
434 --
435 function get_vac_budget_type_changed
436 (transaction_id in varchar2)
437 return varchar2 is
438 l_new_value varchar2(4000);
439 l_vacancy_id number;
440 l_old_value varchar2(4000);
441 --
442 l_retval varchar2(30);
443 cursor get_old_value(p_vacancy_id number) is
444 select budget_measurement_type
445 from per_all_vacancies
446 where vacancy_id=p_vacancy_id;
447 begin
448 --
449 irc_vacancy_approvals.show('Entering get_vac_budget_type_changed');
450 --
451 l_retval := 'false';
452 --
453 if irc_vacancy_approvals.get_transaction_mode(transaction_id) <> 'INSERT' then
454 --
455 l_new_value:=irc_vacancy_approvals.get_vacancy_data_varchar(transaction_id=>transaction_id,data_name=>'BudgetMeasurementType');
456 --
457 l_vacancy_id:=irc_vacancy_approvals.get_vacancy_data_number(transaction_id=>transaction_id,data_name=>'VacancyId');
458 open get_old_value(l_vacancy_id);
459 fetch get_old_value into l_old_value;
460 close get_old_value;
461 --
462 irc_vacancy_approvals.show('Comparing old org value :' || l_old_value
463 || ': to new org value :' || l_new_value || ':');
464 --
465 if(nvl(l_new_value,hr_api.g_varchar2)
466 <> nvl(l_old_value,hr_api.g_varchar2) ) then
467 l_retval:='true';
468 end if;
469 end if;
470 --
471 irc_vacancy_approvals.show('Exiting get_vac_budget_type_changed returning :' || l_retval || ':');
472 --
473 return l_retval;
474 --
475 end get_vac_budget_type_changed;
476 --
477 -- -------------------------------------------------------------------------
478 -- |------------------------< get_vac_status_changed >---------------------|
479 -- -------------------------------------------------------------------------
480 --
481 function get_vac_status_changed
482 (transaction_id in varchar2)
483 return varchar2 is
484 l_new_value varchar2(4000);
485 l_vacancy_id number;
486 l_old_value varchar2(4000);
487 --
488 l_retval varchar2(30);
489 cursor get_old_value(p_vacancy_id number) is
490 select status
491 from per_all_vacancies
492 where vacancy_id=p_vacancy_id;
493 begin
494 --
495 irc_vacancy_approvals.show('Entering get_vac_status_changed');
496 --
497 l_retval := 'false';
498 --
499 if irc_vacancy_approvals.get_transaction_mode(transaction_id)<> 'INSERT' then
500 --
501 l_new_value:=irc_vacancy_approvals.get_vacancy_data_varchar(transaction_id=>transaction_id,data_name=>'Status');
502 --
506 close get_old_value;
503 l_vacancy_id:=irc_vacancy_approvals.get_vacancy_data_number(transaction_id=>transaction_id,data_name=>'VacancyId');
504 open get_old_value(l_vacancy_id);
505 fetch get_old_value into l_old_value;
507 --
508 irc_vacancy_approvals.show('Comparing old org value :' || l_old_value
509 || ': to new org value :' || l_new_value || ':');
510 --
511 if(nvl(l_new_value,hr_api.g_varchar2)
512 <> nvl(l_old_value,hr_api.g_varchar2) ) then
513 l_retval:='true';
514 end if;
515 end if;
516 --
517 irc_vacancy_approvals.show('Exiting get_vac_status_changed returning :' || l_retval || ':');
518 --
519 return l_retval;
520 end get_vac_status_changed;
521 --
522 -- -------------------------------------------------------------------------
523 -- |-----------------------< get_posting_title_changed >-------------------|
524 -- -------------------------------------------------------------------------
525 --
526 function get_posting_title_changed
527 (transaction_id in varchar2)
528 return varchar2 is
529 l_new_value varchar2(4000);
530 l_posting_content_id number;
531 l_old_value varchar2(4000);
532 l_retval varchar2(30);
533 cursor get_old_value(p_posting_content_id number) is
534 select name
535 from irc_posting_contents_vl
536 where posting_content_id=p_posting_content_id;
537 begin
538 --
539 irc_vacancy_approvals.show('Entering get_posting_title_changed');
540 --
541 l_retval := 'false';
542 --
543 if irc_vacancy_approvals.get_transaction_mode(transaction_id)<> 'INSERT' then
544 --
545 l_posting_content_id := irc_vacancy_approvals.get_posting_data_number
546 (transaction_id=>transaction_id, data_name=>'PostingContentId');
547 --
548 l_new_value:=irc_vacancy_approvals.get_posting_data_varchar
549 (transaction_id=>transaction_id, data_name => 'Name');
550
551 open get_old_value(l_posting_content_id);
552 fetch get_old_value into l_old_value;
553 close get_old_value;
554 --
555 irc_vacancy_approvals.show('Comparing old org value :' || l_old_value
556 || ': to new org value :' || l_new_value || ':');
557 --
558 if(nvl(l_new_value,hr_api.g_varchar2)
559 <> nvl(l_old_value,hr_api.g_varchar2) ) then
560 l_retval:='true';
561 end if;
562 end if;
563 --
564 irc_vacancy_approvals.show('Exiting get_posting_title_changed returning :' || l_retval || ':');
565 --
566 return l_retval;
567 end get_posting_title_changed;
568 --
569 -- -------------------------------------------------------------------------
570 -- |---------------------< get_posting_job_title_changed >-----------------|
571 -- -------------------------------------------------------------------------
572 --
573 function get_posting_job_title_changed
574 (transaction_id in varchar2)
575 return varchar2 is
576 l_new_value varchar2(4000);
577 l_posting_content_id number;
578 l_old_value varchar2(4000);
579 l_retval varchar2(30);
580 cursor get_old_value(p_posting_content_id number) is
581 select job_title
582 from irc_posting_contents_vl
583 where posting_content_id=p_posting_content_id;
584 begin
585 --
586 irc_vacancy_approvals.show('Entering get_posting_job_title_changed');
587 --
588 l_retval := 'false';
589 --
590 if irc_vacancy_approvals.get_transaction_mode(transaction_id) <> 'INSERT' then
591 --
592 l_posting_content_id := irc_vacancy_approvals.get_posting_data_number
593 (transaction_id=>transaction_id, data_name=>'PostingContentId');
594 --
595 l_new_value:=irc_vacancy_approvals.get_posting_data_varchar
596 (transaction_id=>transaction_id, data_name => 'JobTitle');
597 --
598 open get_old_value(l_posting_content_id);
599 fetch get_old_value into l_old_value;
600 close get_old_value;
601 --
602 irc_vacancy_approvals.show('Comparing old org value :' || l_old_value
603 || ': to new org value :' || l_new_value || ':');
604 --
605 if(nvl(l_new_value,hr_api.g_varchar2)
606 <> nvl(l_old_value,hr_api.g_varchar2) ) then
607 l_retval:='true';
608 end if;
609 end if;
610 --
611 irc_vacancy_approvals.show('Exiting get_posting_job_title_changed returning :' || l_retval || ':');
612 --
613 return l_retval;
614 end get_posting_job_title_changed;
615 --
616 -- -------------------------------------------------------------------------
617 -- |---------------------< get_posting_department_changed >-----------------|
618 -- -------------------------------------------------------------------------
619 --
620 function get_posting_department_changed
621 (transaction_id in varchar2)
622 return varchar2 is
623 l_new_value varchar2(4000);
624 l_posting_content_id number;
625 l_old_value varchar2(4000);
626 l_retval varchar2(30);
627 cursor get_old_value(p_posting_content_id number) is
628 select org_name
629 from irc_posting_contents_vl
630 where posting_content_id=p_posting_content_id;
631 begin
632 --
633 irc_vacancy_approvals.show('Entering get_posting_department_changed');
634 --
635 l_retval := 'false';
636 --
637 if irc_vacancy_approvals.get_transaction_mode(transaction_id) <> 'INSERT' then
638 --
639 l_posting_content_id := irc_vacancy_approvals.get_posting_data_number
640 (transaction_id=>transaction_id, data_name=>'PostingContentId');
641 --
642 l_new_value:=irc_vacancy_approvals.get_posting_data_varchar
643 (transaction_id=>transaction_id, data_name => 'OrgName');
644 --
645 open get_old_value(l_posting_content_id);
646 fetch get_old_value into l_old_value;
647 close get_old_value;
648 --
649 irc_vacancy_approvals.show('Comparing old org value :' || l_old_value
653 <> nvl(l_old_value,hr_api.g_varchar2) ) then
650 || ': to new org value :' || l_new_value || ':');
651 --
652 if(nvl(l_new_value,hr_api.g_varchar2)
654 l_retval:='true';
655 end if;
656 end if;
657 --
658 irc_vacancy_approvals.show('Exiting get_posting_department_changed returning :' || l_retval || ':');
659 --
660 return l_retval;
661 end get_posting_department_changed;
662 --
663 -- -------------------------------------------------------------------------
664 -- |------------------< get_posting_dept_desc_changed >--------------------|
665 -- -------------------------------------------------------------------------
666 --
667 function get_posting_dept_desc_changed
668 (transaction_id in varchar2)
669 return varchar2 is
670 l_new_value varchar2(32000);
671 l_posting_content_id number;
672 l_old_value varchar2(32000);
673 l_clob_old_value irc_posting_contents_vl.org_description%type;
674 l_retval varchar2(30);
675 cursor get_old_value(p_posting_content_id number) is
676 select org_description
677 from irc_posting_contents_vl
678 where posting_content_id=p_posting_content_id;
679 begin
680 --
681 irc_vacancy_approvals.show('Entering get_posting_dept_desc_changed');
682 --
683 l_retval := 'false';
684 --
685 if irc_vacancy_approvals.get_transaction_mode(transaction_id) <> 'INSERT' then
686 --
687 l_posting_content_id := irc_vacancy_approvals.get_posting_data_number
688 (transaction_id=>transaction_id, data_name=>'PostingContentId');
689 --
690 l_new_value:=irc_vacancy_approvals.get_posting_data_varchar
691 (transaction_id=>transaction_id, data_name => 'OrgDescription');
692 --
693 open get_old_value(l_posting_content_id);
694 fetch get_old_value into l_clob_old_value;
695 close get_old_value;
696 --
697 l_old_value := dbms_lob.substr(l_clob_old_value);
698 --
699 irc_vacancy_approvals.show('Comparing old org value :' || l_old_value
700 || ': to new org value :' || l_new_value || ':');
701 --
702 if(nvl(l_new_value,hr_api.g_varchar2)
703 <> nvl(l_old_value,hr_api.g_varchar2) ) then
704 l_retval:='true';
705 end if;
706 end if;
707 --
708 irc_vacancy_approvals.show('Exiting get_posting_dept_desc_changed returning :' || l_retval || ':');
709 --
710 return l_retval;
711 end get_posting_dept_desc_changed;
712 --
713 -- -------------------------------------------------------------------------
714 -- |-------------------< get_brief_description_changed >-------------------|
715 -- -------------------------------------------------------------------------
716 --
717 function get_brief_description_changed
718 (transaction_id in varchar2)
719 return varchar2 is
720 l_new_value varchar2(32000);
721 l_posting_content_id number;
722 l_old_value varchar2(32000);
723 l_clob_old_value irc_posting_contents_vl.brief_description%type;
724 l_retval varchar2(30);
725 cursor get_old_value(p_posting_content_id number) is
726 select brief_description
727 from irc_posting_contents_vl
728 where posting_content_id=p_posting_content_id;
729 begin
730 --
731 irc_vacancy_approvals.show('Entering get_brief_description_changed');
732 --
733 l_retval := 'false';
734 --
735 if irc_vacancy_approvals.get_transaction_mode(transaction_id)<> 'INSERT' then
736 --
737 l_posting_content_id := irc_vacancy_approvals.get_posting_data_number
738 (transaction_id=>transaction_id, data_name=>'PostingContentId');
739 --
740 l_new_value:=irc_vacancy_approvals.get_posting_data_varchar
741 (transaction_id=>transaction_id ,data_name => 'BriefDescription');
742 --
743 open get_old_value(l_posting_content_id);
744 fetch get_old_value into l_clob_old_value;
745 close get_old_value;
746
747 l_old_value := dbms_lob.substr(l_clob_old_value);
748 --
749 irc_vacancy_approvals.show('Comparing old org value :' || l_old_value
750 || ': to new org value :' || l_new_value || ':');
751 --
752 if(nvl(l_new_value,hr_api.g_varchar2)
753 <> nvl(l_old_value,hr_api.g_varchar2) ) then
754 l_retval:='true';
755 end if;
756 end if;
757 --
758 irc_vacancy_approvals.show('Exiting get_brief_description_changed returning :' || l_retval || ':');
759 --
760 return l_retval;
761 end get_brief_description_changed;
762 --
763 -- -------------------------------------------------------------------------
764 -- |----------------------< get_detailed_desc_changed >--------------------|
765 -- -------------------------------------------------------------------------
766 --
767 function get_detailed_desc_changed
768 (transaction_id in varchar2)
769 return varchar2 is
770 l_new_value varchar2(32000);
771 l_posting_content_id number;
772 l_old_value varchar2(32000);
773 l_clob_old_value irc_posting_contents_vl.detailed_description%type;
774 l_retval varchar2(30);
775 cursor get_old_value(p_posting_content_id number) is
776 select detailed_description
777 from irc_posting_contents_vl
778 where posting_content_id=p_posting_content_id;
779 begin
780 --
781 irc_vacancy_approvals.show('Entering get_detailed_desc_changed');
782 --
783 l_retval := 'false';
784 --
785 if irc_vacancy_approvals.get_transaction_mode(transaction_id) <> 'INSERT' then
786 --
787 l_posting_content_id := irc_vacancy_approvals.get_posting_data_number
788 (transaction_id=>transaction_id, data_name=>'PostingContentId');
789 --
790 l_new_value:=irc_vacancy_approvals.get_posting_data_varchar
791 (transaction_id=>transaction_id ,data_name => 'DetailedDescription');
792 --
793 open get_old_value(l_posting_content_id);
794 fetch get_old_value into l_clob_old_value;
795 close get_old_value;
799 || ': to new org value :' || l_new_value || ':');
796 l_old_value := dbms_lob.substr(l_clob_old_value);
797 --
798 irc_vacancy_approvals.show('Comparing old org value :' || l_old_value
800 --
801 if(nvl(l_new_value,hr_api.g_varchar2)
802 <> nvl(l_old_value,hr_api.g_varchar2) ) then
803 l_retval:='true';
804 end if;
805 end if;
806 --
807 irc_vacancy_approvals.show('Exiting get_detailed_desc_changed returning :' || l_retval || ':');
808 --
809 return l_retval;
810 end get_detailed_desc_changed;
811 --
812 -- -------------------------------------------------------------------------
813 -- |----------------------< get_job_requirements_changed >----------------|
814 -- -------------------------------------------------------------------------
815 --
816 function get_job_requirements_changed
817 (transaction_id in varchar2)
818 return varchar2 is
819 l_new_value varchar2(32000);
820 l_posting_content_id number;
821 l_old_value varchar2(32000);
822 l_clob_old_value irc_posting_contents_vl.job_requirements%type;
823 l_retval varchar2(30);
824 cursor get_old_value(p_posting_content_id number) is
825 select job_requirements
826 from irc_posting_contents_vl
827 where posting_content_id=p_posting_content_id;
828 begin
829 --
830 irc_vacancy_approvals.show('Entering get_job_requirements_changed');
831 --
832 l_retval := 'false';
833 --
834 if irc_vacancy_approvals.get_transaction_mode(transaction_id) <> 'INSERT' then
835 --
836 l_posting_content_id := irc_vacancy_approvals.get_posting_data_number
837 (transaction_id=>transaction_id, data_name=>'PostingContentId');
838 --
839 l_new_value:=irc_vacancy_approvals.get_posting_data_varchar
840 (transaction_id=>transaction_id ,data_name => 'JobRequirements');
841 --
842 open get_old_value(l_posting_content_id);
843 fetch get_old_value into l_clob_old_value;
844 close get_old_value;
845 l_old_value := dbms_lob.substr(l_clob_old_value);
846 --
847 irc_vacancy_approvals.show('Comparing old org value :' || l_old_value
848 || ': to new org value :' || l_new_value || ':');
849 --
850 if(nvl(l_new_value,hr_api.g_varchar2)
851 <> nvl(l_old_value,hr_api.g_varchar2) ) then
852 l_retval:='true';
853 end if;
854 end if;
855 --
856 irc_vacancy_approvals.show('Exiting get_job_requirements_changed returning :' || l_retval || ':');
857 --
858 return l_retval;
859 end get_job_requirements_changed;
860 --
861 -- -------------------------------------------------------------------------
862 -- |---------------------< get_additional_details_changed >----------------|
863 -- -------------------------------------------------------------------------
864 --
865 function get_additional_details_changed
866 (transaction_id in varchar2)
867 return varchar2 is
868 l_new_value varchar2(32000);
869 l_posting_content_id number;
870 l_old_value varchar2(32000);
871 l_clob_old_value irc_posting_contents_vl.additional_details%type;
872 l_retval varchar2(30);
873 cursor get_old_value(p_posting_content_id number) is
874 select additional_details
875 from irc_posting_contents_vl
876 where posting_content_id=p_posting_content_id;
877 begin
878 --
879 irc_vacancy_approvals.show('Entering get_additional_details_changed');
880 --
881 l_retval := 'false';
882 --
883 if irc_vacancy_approvals.get_transaction_mode(transaction_id) <> 'INSERT' then
884 --
885 l_posting_content_id := irc_vacancy_approvals.get_posting_data_number
886 (transaction_id=>transaction_id, data_name=>'PostingContentId');
887 --
888 l_new_value:=irc_vacancy_approvals.get_posting_data_varchar
889 (transaction_id=>transaction_id ,data_name => 'AdditionalDetails');
890 --
891 open get_old_value(l_posting_content_id);
892 fetch get_old_value into l_clob_old_value;
893 close get_old_value;
894 l_old_value := dbms_lob.substr(l_clob_old_value);
895 --
896 irc_vacancy_approvals.show('Comparing old org value :' || l_old_value
897 || ': to new org value :' || l_new_value || ':');
898 --
899 if(nvl(l_new_value,hr_api.g_varchar2)
900 <> nvl(l_old_value,hr_api.g_varchar2) ) then
901 l_retval:='true';
902 end if;
903 end if;
904 --
905 irc_vacancy_approvals.show('Exiting get_additional_details_changed returning :' || l_retval || ':');
906 --
907 return l_retval;
908 end get_additional_details_changed;
909 --
910 -- -------------------------------------------------------------------------
911 -- |-----------------------< get_how_to_apply_changed >--------------------|
912 -- -------------------------------------------------------------------------
913 --
914 function get_how_to_apply_changed
915 (transaction_id in varchar2)
916 return varchar2 is
917 l_new_value varchar2(32000);
918 l_posting_content_id number;
919 l_old_value varchar2(32000);
920 l_clob_old_value irc_posting_contents_vl.how_to_apply%type;
921 l_retval varchar2(30);
922 cursor get_old_value(p_posting_content_id number) is
923 select how_to_apply
924 from irc_posting_contents_vl
925 where posting_content_id=p_posting_content_id;
926 begin
927 --
928 irc_vacancy_approvals.show('Entering get_how_to_apply_changed');
929 --
930 l_retval := 'false';
931 --
932 if irc_vacancy_approvals.get_transaction_mode(transaction_id) <> 'INSERT' then
933 --
934 l_posting_content_id := irc_vacancy_approvals.get_posting_data_number
935 (transaction_id=>transaction_id, data_name=>'PostingContentId');
936 --
937 l_new_value:=irc_vacancy_approvals.get_posting_data_varchar
938 (transaction_id=>transaction_id ,data_name => 'HowToApply');
939 --
940 open get_old_value(l_posting_content_id);
944 --
941 fetch get_old_value into l_clob_old_value;
942 close get_old_value;
943 l_old_value := dbms_lob.substr(l_clob_old_value);
945 irc_vacancy_approvals.show('Comparing old org value :' || l_old_value
946 || ': to new org value :' || l_new_value || ':');
947 --
948 if(nvl(l_new_value,hr_api.g_varchar2)
949 <> nvl(l_old_value,hr_api.g_varchar2) ) then
950 l_retval:='true';
951 end if;
952 end if;
953 --
954 irc_vacancy_approvals.show('Exiting get_how_to_apply_changed returning :' || l_retval || ':');
955 --
956 return l_retval;
957 end get_how_to_apply_changed;
958 --
959 -- -------------------------------------------------------------------------
960 -- |-----------------------< get_posting_graphic_changed >-----------------|
961 -- -------------------------------------------------------------------------
962 --
963 function get_posting_graphic_changed
964 (transaction_id in varchar2)
965 return varchar2 is
966 --
967 l_new_value varchar2(32000);
968 l_posting_content_id number;
969 l_old_value varchar2(32000);
970 l_clob_old_value irc_posting_contents_vl.image_url%type;
971 l_retval varchar2(30);
972 --
973 cursor get_old_value(p_posting_content_id number) is
974 select image_url
975 from irc_posting_contents_vl
976 where posting_content_id=p_posting_content_id;
977 --
978 BEGIN
979 --
980 irc_vacancy_approvals.show('Entering get_posting_graphic_changed');
981 --
982 l_retval := 'false';
983 --
984 if irc_vacancy_approvals.get_transaction_mode(transaction_id)<> 'INSERT' then
985 --
986 l_new_value := irc_vacancy_approvals.get_posting_data_varchar
987 (transaction_id=>transaction_id ,data_name => 'ImageUrl');
988 --
989 l_posting_content_id := irc_vacancy_approvals.get_posting_data_number
990 (transaction_id=>transaction_id, data_name=>'PostingContentId');
991 open get_old_value(l_posting_content_id);
992 fetch get_old_value into l_clob_old_value;
993 close get_old_value;
994 l_old_value := dbms_lob.substr(l_clob_old_value);
995 --
996 irc_vacancy_approvals.show('Comparing old org value :' || l_old_value
997 || ': to new org value :' || l_new_value || ':');
998 --
999 if(nvl(l_new_value,hr_api.g_varchar2)
1000 <> nvl(l_old_value,hr_api.g_varchar2) ) then
1001 l_retval:='true';
1002 end if;
1003 --
1004 end if;
1005 --
1006 irc_vacancy_approvals.show('Exiting get_posting_graphic_changed returning :' || l_retval || ':');
1007 --
1008 return l_retval;
1009 --
1010 end get_posting_graphic_changed;
1011 --
1012 procedure show(message in varchar2) is
1013 BEGIN
1014 hr_utility.trace(message);
1015 END show;
1016 --
1017 --
1018 function get_custom_rule return varchar2 is
1019 BEGIN
1020 return 'SEEDED_RULE';
1021 END get_custom_rule;
1022 --
1023 --
1024 END IRC_VACANCY_APPROVALS;