[Home] [Help]
PACKAGE BODY: APPS.IRC_OFFERS_SWI
Source
1 Package Body irc_offers_swi As
2 /* $Header: iriofswi.pkb 120.24.12010000.3 2009/01/07 15:24:36 avarri ship $ */
3 --
4 -- Package variables
5 --
6 g_package varchar2(33) := 'irc_offers_swi.';
7 --
8 -- ----------------------------------------------------------------------------
9 -- |-----------------------------< create_offer >-----------------------------|
10 -- ----------------------------------------------------------------------------
11 PROCEDURE create_offer
12 (p_validate in number default hr_api.g_false_num
13 ,p_effective_date in date default null
14 ,p_offer_status in varchar2
15 ,p_discretionary_job_title in varchar2 default null
16 ,p_offer_extended_method in varchar2 default null
17 ,p_respondent_id in number default null
18 ,p_expiry_date in date default null
19 ,p_proposed_start_date in date default null
20 ,p_offer_letter_tracking_code in varchar2 default null
21 ,p_offer_postal_service in varchar2 default null
22 ,p_offer_shipping_date in date default null
23 ,p_applicant_assignment_id in number
24 ,p_offer_assignment_id in number
25 ,p_address_id in number default null
26 ,p_template_id in number default null
27 ,p_offer_letter_file_type in varchar2 default null
28 ,p_offer_letter_file_name in varchar2 default null
29 ,p_attribute_category in varchar2 default null
30 ,p_attribute1 in varchar2 default null
31 ,p_attribute2 in varchar2 default null
32 ,p_attribute3 in varchar2 default null
33 ,p_attribute4 in varchar2 default null
34 ,p_attribute5 in varchar2 default null
35 ,p_attribute6 in varchar2 default null
36 ,p_attribute7 in varchar2 default null
37 ,p_attribute8 in varchar2 default null
38 ,p_attribute9 in varchar2 default null
39 ,p_attribute10 in varchar2 default null
40 ,p_attribute11 in varchar2 default null
41 ,p_attribute12 in varchar2 default null
42 ,p_attribute13 in varchar2 default null
43 ,p_attribute14 in varchar2 default null
44 ,p_attribute15 in varchar2 default null
45 ,p_attribute16 in varchar2 default null
46 ,p_attribute17 in varchar2 default null
47 ,p_attribute18 in varchar2 default null
48 ,p_attribute19 in varchar2 default null
49 ,p_attribute20 in varchar2 default null
50 ,p_attribute21 in varchar2 default null
51 ,p_attribute22 in varchar2 default null
52 ,p_attribute23 in varchar2 default null
53 ,p_attribute24 in varchar2 default null
54 ,p_attribute25 in varchar2 default null
55 ,p_attribute26 in varchar2 default null
56 ,p_attribute27 in varchar2 default null
57 ,p_attribute28 in varchar2 default null
58 ,p_attribute29 in varchar2 default null
59 ,p_attribute30 in varchar2 default null
60 ,p_status_change_date in date default null
61 ,p_offer_id in out nocopy number
62 ,p_offer_version out nocopy number
63 ,p_object_version_number out nocopy number
64 ,p_return_status out nocopy varchar2
65 ) is
66 --
67 -- Variables for API Boolean parameters
68 l_validate boolean;
69 --
70 -- Other variables
71 l_proc varchar2(72) := g_package ||'create_offer';
72 Begin
73 hr_utility.set_location(' Entering:' || l_proc,10);
74 --
75 -- Issue a savepoint
76 --
77 savepoint create_offer_swi;
78 --
79 -- Initialise Multiple Message Detection
80 --
81 hr_multi_message.enable_message_list;
82 --
83 -- Remember IN OUT parameter IN values
84 --
85 --
86 -- Convert constant values to their corresponding boolean value
87 --
88 l_validate :=
89 hr_api.constant_to_boolean
90 (p_constant_value => p_validate);
91 --
92 -- Register Surrogate ID or user key values
93 --
94 irc_iof_ins.set_base_key_value
95 (p_offer_id => p_offer_id
96 );
97 --
98 -- Call API
99 --
100 irc_offers_api.create_offer
101 (p_validate => l_validate
102 ,p_effective_date => p_effective_date
103 ,p_offer_status => p_offer_status
104 ,p_discretionary_job_title => p_discretionary_job_title
105 ,p_offer_extended_method => p_offer_extended_method
106 ,p_respondent_id => p_respondent_id
107 ,p_expiry_date => p_expiry_date
108 ,p_proposed_start_date => p_proposed_start_date
109 ,p_offer_letter_tracking_code => p_offer_letter_tracking_code
110 ,p_offer_postal_service => p_offer_postal_service
111 ,p_offer_shipping_date => p_offer_shipping_date
112 ,p_applicant_assignment_id => p_applicant_assignment_id
113 ,p_offer_assignment_id => p_offer_assignment_id
114 ,p_address_id => p_address_id
115 ,p_template_id => p_template_id
116 ,p_offer_letter_file_type => p_offer_letter_file_type
117 ,p_offer_letter_file_name => p_offer_letter_file_name
118 ,p_attribute_category => p_attribute_category
119 ,p_attribute1 => p_attribute1
120 ,p_attribute2 => p_attribute2
121 ,p_attribute3 => p_attribute3
122 ,p_attribute4 => p_attribute4
123 ,p_attribute5 => p_attribute5
124 ,p_attribute6 => p_attribute6
125 ,p_attribute7 => p_attribute7
126 ,p_attribute8 => p_attribute8
127 ,p_attribute9 => p_attribute9
128 ,p_attribute10 => p_attribute10
129 ,p_attribute11 => p_attribute11
130 ,p_attribute12 => p_attribute12
131 ,p_attribute13 => p_attribute13
132 ,p_attribute14 => p_attribute14
133 ,p_attribute15 => p_attribute15
134 ,p_attribute16 => p_attribute16
135 ,p_attribute17 => p_attribute17
136 ,p_attribute18 => p_attribute18
137 ,p_attribute19 => p_attribute19
138 ,p_attribute20 => p_attribute20
139 ,p_attribute21 => p_attribute21
140 ,p_attribute22 => p_attribute22
141 ,p_attribute23 => p_attribute23
142 ,p_attribute24 => p_attribute24
143 ,p_attribute25 => p_attribute25
144 ,p_attribute26 => p_attribute26
145 ,p_attribute27 => p_attribute27
146 ,p_attribute28 => p_attribute28
147 ,p_attribute29 => p_attribute29
148 ,p_attribute30 => p_attribute30
149 ,p_status_change_date => p_status_change_date
150 ,p_offer_id => p_offer_id
151 ,p_offer_version => p_offer_version
152 ,p_object_version_number => p_object_version_number
153 );
154 --
155 -- Convert API warning boolean parameter values to specific
156 -- messages and add them to Multiple Message List
157 --
158 --
159 -- Convert API non-warning boolean parameter values
160 --
161 --
162 -- Derive the API return status value based on whether
163 -- messages of any type exist in the Multiple Message List.
164 -- Also disable Multiple Message Detection.
165 --
166 --
167 p_return_status := hr_multi_message.get_return_status_disable;
168 hr_utility.set_location(' Leaving:' || l_proc,20);
169 --
170 exception
171 when hr_multi_message.error_message_exist then
172 --
173 -- Catch the Multiple Message List exception which
174 -- indicates API processing has been aborted because
175 -- at least one message exists in the list.
176 --
177 rollback to create_offer_swi;
178 --
179 -- Reset IN OUT parameters and set OUT parameters
180 --
181 p_offer_version := null;
182 p_object_version_number := null;
183 p_return_status := hr_multi_message.get_return_status_disable;
184 hr_utility.set_location(' Leaving:' || l_proc, 30);
185 when others then
186 --
187 -- When Multiple Message Detection is enabled catch
188 -- any Application specific or other unexpected
189 -- exceptions. Adding appropriate details to the
190 -- Multiple Message List. Otherwise re-raise the
191 -- error.
192 --
193 rollback to create_offer_swi;
194 if hr_multi_message.unexpected_error_add(l_proc) then
195 hr_utility.set_location(' Leaving:' || l_proc,40);
196 raise;
197 end if;
198 --
199 -- Reset IN OUT and set OUT parameters
200 --
201 p_offer_version := null;
202 p_object_version_number := null;
203 p_return_status := hr_multi_message.get_return_status_disable;
204 hr_utility.set_location(' Leaving:' || l_proc,50);
205 end create_offer;
206 -- ----------------------------------------------------------------------------
207 -- |-----------------------------< update_offer >-----------------------------|
208 -- ----------------------------------------------------------------------------
209 PROCEDURE update_offer
210 (p_validate in number default hr_api.g_false_num
211 ,p_effective_date in date default hr_api.g_date
212 ,p_offer_status in varchar2 default hr_api.g_varchar2
213 ,p_discretionary_job_title in varchar2 default hr_api.g_varchar2
214 ,p_offer_extended_method in varchar2 default hr_api.g_varchar2
215 ,p_respondent_id in number default hr_api.g_number
216 ,p_expiry_date in date default hr_api.g_date
217 ,p_proposed_start_date in date default hr_api.g_date
218 ,p_offer_letter_tracking_code in varchar2 default hr_api.g_varchar2
219 ,p_offer_postal_service in varchar2 default hr_api.g_varchar2
220 ,p_offer_shipping_date in date default hr_api.g_date
221 ,p_applicant_assignment_id in number default hr_api.g_number
222 ,p_offer_assignment_id in number default hr_api.g_number
223 ,p_address_id in number default hr_api.g_number
224 ,p_template_id in number default hr_api.g_number
225 ,p_offer_letter_file_type in varchar2 default hr_api.g_varchar2
226 ,p_offer_letter_file_name in varchar2 default hr_api.g_varchar2
227 ,p_attribute_category in varchar2 default hr_api.g_varchar2
228 ,p_attribute1 in varchar2 default hr_api.g_varchar2
229 ,p_attribute2 in varchar2 default hr_api.g_varchar2
230 ,p_attribute3 in varchar2 default hr_api.g_varchar2
231 ,p_attribute4 in varchar2 default hr_api.g_varchar2
232 ,p_attribute5 in varchar2 default hr_api.g_varchar2
233 ,p_attribute6 in varchar2 default hr_api.g_varchar2
234 ,p_attribute7 in varchar2 default hr_api.g_varchar2
235 ,p_attribute8 in varchar2 default hr_api.g_varchar2
236 ,p_attribute9 in varchar2 default hr_api.g_varchar2
237 ,p_attribute10 in varchar2 default hr_api.g_varchar2
238 ,p_attribute11 in varchar2 default hr_api.g_varchar2
239 ,p_attribute12 in varchar2 default hr_api.g_varchar2
240 ,p_attribute13 in varchar2 default hr_api.g_varchar2
241 ,p_attribute14 in varchar2 default hr_api.g_varchar2
242 ,p_attribute15 in varchar2 default hr_api.g_varchar2
243 ,p_attribute16 in varchar2 default hr_api.g_varchar2
244 ,p_attribute17 in varchar2 default hr_api.g_varchar2
245 ,p_attribute18 in varchar2 default hr_api.g_varchar2
246 ,p_attribute19 in varchar2 default hr_api.g_varchar2
247 ,p_attribute20 in varchar2 default hr_api.g_varchar2
248 ,p_attribute21 in varchar2 default hr_api.g_varchar2
249 ,p_attribute22 in varchar2 default hr_api.g_varchar2
250 ,p_attribute23 in varchar2 default hr_api.g_varchar2
251 ,p_attribute24 in varchar2 default hr_api.g_varchar2
252 ,p_attribute25 in varchar2 default hr_api.g_varchar2
253 ,p_attribute26 in varchar2 default hr_api.g_varchar2
254 ,p_attribute27 in varchar2 default hr_api.g_varchar2
255 ,p_attribute28 in varchar2 default hr_api.g_varchar2
256 ,p_attribute29 in varchar2 default hr_api.g_varchar2
257 ,p_attribute30 in varchar2 default hr_api.g_varchar2
258 ,p_change_reason in varchar2 default null
259 ,p_decline_reason in varchar2 default null
260 ,p_note_text in varchar2 default null
261 ,p_status_change_date in date default null
262 ,p_offer_id in out nocopy number
263 ,p_object_version_number in out nocopy number
264 ,p_offer_version out nocopy number
265 ,p_return_status out nocopy varchar2
266 ) is
267 --
268 -- Variables for API Boolean parameters
269 l_validate boolean;
270 --
271 -- Variables for IN/OUT parameters
272 l_offer_id number;
273 l_object_version_number number;
274 --
275 -- Other variables
276 l_proc varchar2(72) := g_package ||'update_offer';
277 Begin
278 hr_utility.set_location(' Entering:' || l_proc,10);
279 --
280 -- Issue a savepoint
281 --
282 savepoint update_offer_swi;
283 --
284 -- Initialise Multiple Message Detection
285 --
286 hr_multi_message.enable_message_list;
287 --
288 -- Remember IN OUT parameter IN values
289 --
290 l_offer_id := p_offer_id;
291 l_object_version_number := p_object_version_number;
292 --
293 -- Convert constant values to their corresponding boolean value
294 --
295 l_validate :=
296 hr_api.constant_to_boolean
297 (p_constant_value => p_validate);
298 --
299 -- Register Surrogate ID or user key values
300 --
301 --
302 -- Call API
303 --
304 irc_offers_api.update_offer
305 (p_validate => l_validate
306 ,p_effective_date => p_effective_date
307 ,p_offer_status => p_offer_status
308 ,p_discretionary_job_title => p_discretionary_job_title
309 ,p_offer_extended_method => p_offer_extended_method
310 ,p_respondent_id => p_respondent_id
311 ,p_expiry_date => p_expiry_date
312 ,p_proposed_start_date => p_proposed_start_date
313 ,p_offer_letter_tracking_code => p_offer_letter_tracking_code
314 ,p_offer_postal_service => p_offer_postal_service
315 ,p_offer_shipping_date => p_offer_shipping_date
316 ,p_applicant_assignment_id => p_applicant_assignment_id
317 ,p_offer_assignment_id => p_offer_assignment_id
318 ,p_address_id => p_address_id
319 ,p_template_id => p_template_id
320 ,p_offer_letter_file_type => p_offer_letter_file_type
321 ,p_offer_letter_file_name => p_offer_letter_file_name
322 ,p_attribute_category => p_attribute_category
323 ,p_attribute1 => p_attribute1
324 ,p_attribute2 => p_attribute2
325 ,p_attribute3 => p_attribute3
326 ,p_attribute4 => p_attribute4
327 ,p_attribute5 => p_attribute5
328 ,p_attribute6 => p_attribute6
329 ,p_attribute7 => p_attribute7
330 ,p_attribute8 => p_attribute8
331 ,p_attribute9 => p_attribute9
332 ,p_attribute10 => p_attribute10
333 ,p_attribute11 => p_attribute11
334 ,p_attribute12 => p_attribute12
335 ,p_attribute13 => p_attribute13
336 ,p_attribute14 => p_attribute14
337 ,p_attribute15 => p_attribute15
338 ,p_attribute16 => p_attribute16
339 ,p_attribute17 => p_attribute17
340 ,p_attribute18 => p_attribute18
341 ,p_attribute19 => p_attribute19
342 ,p_attribute20 => p_attribute20
343 ,p_attribute21 => p_attribute21
344 ,p_attribute22 => p_attribute22
345 ,p_attribute23 => p_attribute23
346 ,p_attribute24 => p_attribute24
347 ,p_attribute25 => p_attribute25
348 ,p_attribute26 => p_attribute26
349 ,p_attribute27 => p_attribute27
350 ,p_attribute28 => p_attribute28
351 ,p_attribute29 => p_attribute29
352 ,p_attribute30 => p_attribute30
353 ,p_change_reason => p_change_reason
354 ,p_decline_reason => p_decline_reason
355 ,p_note_text => p_note_text
356 ,p_status_change_date => p_status_change_date
357 ,p_offer_id => p_offer_id
358 ,p_object_version_number => p_object_version_number
359 ,p_offer_version => p_offer_version
360 );
361 --
362 -- Convert API warning boolean parameter values to specific
363 -- messages and add them to Multiple Message List
364 --
365 --
366 -- Convert API non-warning boolean parameter values
367 --
368 --
369 -- Derive the API return status value based on whether
370 -- messages of any type exist in the Multiple Message List.
371 -- Also disable Multiple Message Detection.
372 --
373 p_return_status := hr_multi_message.get_return_status_disable;
374 hr_utility.set_location(' Leaving:' || l_proc,20);
375 --
376 exception
377 when hr_multi_message.error_message_exist then
378 --
379 -- Catch the Multiple Message List exception which
380 -- indicates API processing has been aborted because
381 -- at least one message exists in the list.
382 --
383 rollback to update_offer_swi;
384 --
385 -- Reset IN OUT parameters and set OUT parameters
386 --
387 p_offer_id := l_offer_id;
388 p_object_version_number := l_object_version_number;
389 p_offer_version := null;
390 p_return_status := hr_multi_message.get_return_status_disable;
391 hr_utility.set_location(' Leaving:' || l_proc, 30);
392 when others then
393 --
394 -- When Multiple Message Detection is enabled catch
395 -- any Application specific or other unexpected
396 -- exceptions. Adding appropriate details to the
397 -- Multiple Message List. Otherwise re-raise the
398 -- error.
399 --
400 rollback to update_offer_swi;
401 if hr_multi_message.unexpected_error_add(l_proc) then
402 hr_utility.set_location(' Leaving:' || l_proc,40);
403 raise;
404 end if;
405 --
406 -- Reset IN OUT and set OUT parameters
407 --
408 p_offer_id := l_offer_id;
409 p_object_version_number := l_object_version_number;
410 p_offer_version := null;
411 p_return_status := hr_multi_message.get_return_status_disable;
412 hr_utility.set_location(' Leaving:' || l_proc,50);
413 end update_offer;
414 -- ----------------------------------------------------------------------------
415 -- |-----------------------------< delete_offer >-----------------------------|
416 -- ----------------------------------------------------------------------------
417 PROCEDURE delete_offer
418 (p_validate in number default hr_api.g_false_num
419 ,p_object_version_number in number
420 ,p_offer_id in number
421 ,p_effective_date in date default hr_api.g_date
422 ,p_return_status out nocopy varchar2
423 ) is
424 --
425 -- Variables for API Boolean parameters
426 l_validate boolean;
427 --
428 -- Variables for IN/OUT parameters
429 --
430 -- Other variables
431 l_proc varchar2(72) := g_package ||'delete_offer';
432 Begin
433 hr_utility.set_location(' Entering:' || l_proc,10);
434 --
435 -- Issue a savepoint
436 --
437 savepoint delete_offer_swi;
438 --
439 -- Initialise Multiple Message Detection
440 --
441 hr_multi_message.enable_message_list;
442 --
443 -- Remember IN OUT parameter IN values
444 --
445 --
446 -- Convert constant values to their corresponding boolean value
447 --
448 l_validate :=
449 hr_api.constant_to_boolean
450 (p_constant_value => p_validate);
451 --
452 -- Register Surrogate ID or user key values
453 --
454 --
455 -- Call API
456 --
457 irc_offers_api.delete_offer
458 (p_validate => l_validate
459 ,p_object_version_number => p_object_version_number
460 ,p_offer_id => p_offer_id
461 ,p_effective_date => p_effective_date
462 );
463 --
464 -- Convert API warning boolean parameter values to specific
465 -- messages and add them to Multiple Message List
466 --
467 --
468 -- Convert API non-warning boolean parameter values
469 --
470 --
471 -- Derive the API return status value based on whether
472 -- messages of any type exist in the Multiple Message List.
473 -- Also disable Multiple Message Detection.
474 --
475 p_return_status := hr_multi_message.get_return_status_disable;
476 hr_utility.set_location(' Leaving:' || l_proc,20);
477 --
478 exception
479 when hr_multi_message.error_message_exist then
480 --
481 -- Catch the Multiple Message List exception which
482 -- indicates API processing has been aborted because
483 -- at least one message exists in the list.
484 --
485 rollback to delete_offer_swi;
486 --
487 -- Reset IN OUT parameters and set OUT parameters
488 --
489 p_return_status := hr_multi_message.get_return_status_disable;
490 hr_utility.set_location(' Leaving:' || l_proc, 30);
491 when others then
492 --
493 -- When Multiple Message Detection is enabled catch
494 -- any Application specific or other unexpected
495 -- exceptions. Adding appropriate details to the
496 -- Multiple Message List. Otherwise re-raise the
497 -- error.
498 --
499 rollback to delete_offer_swi;
500 if hr_multi_message.unexpected_error_add(l_proc) then
501 hr_utility.set_location(' Leaving:' || l_proc,40);
502 raise;
503 end if;
504 --
505 -- Reset IN OUT and set OUT parameters
506 --
507 p_return_status := hr_multi_message.get_return_status_disable;
508 hr_utility.set_location(' Leaving:' || l_proc,50);
509 end delete_offer;
510 -- ----------------------------------------------------------------------------
511 -- |------------------------------< close_offer >-----------------------------|
512 -- ----------------------------------------------------------------------------
513 PROCEDURE close_offer
514 (p_validate in number default hr_api.g_false_num
515 ,p_effective_date in date default hr_api.g_date
516 ,p_applicant_assignment_id in number default hr_api.g_number
517 ,p_offer_id in number default hr_api.g_number
518 ,p_respondent_id in number default hr_api.g_number
519 ,p_change_reason in varchar2 default hr_api.g_varchar2
520 ,p_decline_reason in varchar2 default hr_api.g_varchar2
521 ,p_note_text in varchar2 default hr_api.g_varchar2
522 ,p_status_change_date in date default hr_api.g_date
523 ,p_return_status out nocopy varchar2
524 ) is
525 --
526 -- Variables for API Boolean parameters
527 l_validate boolean;
528 --
529 -- Variables for IN/OUT parameters
530 --
531 -- Other variables
532 l_proc varchar2(72) := g_package ||'close_offer';
533 Begin
534 hr_utility.set_location(' Entering:' || l_proc,10);
535 --
536 -- Issue a savepoint
537 --
538 savepoint close_offer_swi;
539 --
540 -- Initialise Multiple Message Detection
541 --
542 hr_multi_message.enable_message_list;
543 --
544 -- Remember IN OUT parameter IN values
545 --
546 --
547 -- Convert constant values to their corresponding boolean value
548 --
549 l_validate :=
550 hr_api.constant_to_boolean
551 (p_constant_value => p_validate);
552 --
553 -- Register Surrogate ID or user key values
554 --
555 --
556 -- Call API
557 --
558 irc_offers_api.close_offer
559 (p_validate => l_validate
560 ,p_effective_date => p_effective_date
561 ,p_applicant_assignment_id => p_applicant_assignment_id
562 ,p_offer_id => p_offer_id
563 ,p_respondent_id => p_respondent_id
564 ,p_change_reason => p_change_reason
565 ,p_decline_reason => p_decline_reason
566 ,p_note_text => p_note_text
567 ,p_status_change_date => p_status_change_date
568 );
569 --
570 -- Convert API warning boolean parameter values to specific
571 -- messages and add them to Multiple Message List
572 --
573 --
574 -- Convert API non-warning boolean parameter values
575 --
576 --
577 -- Derive the API return status value based on whether
578 -- messages of any type exist in the Multiple Message List.
579 -- Also disable Multiple Message Detection.
580 --
581 p_return_status := hr_multi_message.get_return_status_disable;
582 hr_utility.set_location(' Leaving:' || l_proc,20);
583 --
584 exception
585 when hr_multi_message.error_message_exist then
586 --
587 -- Catch the Multiple Message List exception which
588 -- indicates API processing has been aborted because
589 -- at least one message exists in the list.
590 --
591 rollback to close_offer_swi;
592 --
593 -- Reset IN OUT parameters and set OUT parameters
594 --
595 p_return_status := hr_multi_message.get_return_status_disable;
596 hr_utility.set_location(' Leaving:' || l_proc, 30);
597 when others then
598 --
599 -- When Multiple Message Detection is enabled catch
600 -- any Application specific or other unexpected
601 -- exceptions. Adding appropriate details to the
602 -- Multiple Message List. Otherwise re-raise the
603 -- error.
604 --
605 rollback to close_offer_swi;
606 if hr_multi_message.unexpected_error_add(l_proc) then
607 hr_utility.set_location(' Leaving:' || l_proc,40);
608 raise;
609 end if;
610 --
611 -- Reset IN OUT and set OUT parameters
612 --
613 p_return_status := hr_multi_message.get_return_status_disable;
614 hr_utility.set_location(' Leaving:' || l_proc,50);
615 end close_offer;
616 -- ----------------------------------------------------------------------------
617 -- |------------------------------< hold_offer >------------------------------|
618 -- ----------------------------------------------------------------------------
619 PROCEDURE hold_offer
620 (p_validate in number default hr_api.g_false_num
621 ,p_effective_date in date default hr_api.g_date
622 ,p_offer_id in number
623 ,p_respondent_id in number default hr_api.g_number
624 ,p_change_reason in varchar2 default hr_api.g_varchar2
625 ,p_status_change_date in date default hr_api.g_date
626 ,p_note_text in varchar2 default hr_api.g_varchar2
627 ,p_object_version_number in out nocopy number
628 ,p_return_status out nocopy varchar2
629 ) is
630 --
631 -- Variables for API Boolean parameters
632 l_validate boolean;
633 --
634 -- Variables for IN/OUT parameters
635 l_object_version_number number;
636 --
637 -- Other variables
638 l_proc varchar2(72) := g_package ||'hold_offer';
639 Begin
640 hr_utility.set_location(' Entering:' || l_proc,10);
641 --
642 -- Issue a savepoint
643 --
644 savepoint hold_offer_swi;
645 --
646 -- Initialise Multiple Message Detection
647 --
648 hr_multi_message.enable_message_list;
649 --
650 -- Remember IN OUT parameter IN values
651 --
652 l_object_version_number := p_object_version_number;
653 --
654 -- Convert constant values to their corresponding boolean value
655 --
656 l_validate :=
657 hr_api.constant_to_boolean
658 (p_constant_value => p_validate);
659 --
660 -- Register Surrogate ID or user key values
661 --
662 --
663 -- Call API
664 --
665 irc_offers_api.hold_offer
666 (p_validate => l_validate
667 ,p_effective_date => p_effective_date
668 ,p_offer_id => p_offer_id
669 ,p_respondent_id => p_respondent_id
670 ,p_change_reason => p_change_reason
671 ,p_status_change_date => p_status_change_date
672 ,p_note_text => p_note_text
673 ,p_object_version_number => p_object_version_number
674 );
675 --
676 -- Convert API warning boolean parameter values to specific
677 -- messages and add them to Multiple Message List
678 --
679 --
680 -- Convert API non-warning boolean parameter values
681 --
682 --
683 -- Derive the API return status value based on whether
684 -- messages of any type exist in the Multiple Message List.
685 -- Also disable Multiple Message Detection.
686 --
687 p_return_status := hr_multi_message.get_return_status_disable;
688 hr_utility.set_location(' Leaving:' || l_proc,20);
689 --
690 exception
691 when hr_multi_message.error_message_exist then
692 --
693 -- Catch the Multiple Message List exception which
694 -- indicates API processing has been aborted because
695 -- at least one message exists in the list.
696 --
697 rollback to hold_offer_swi;
698 --
699 -- Reset IN OUT parameters and set OUT parameters
700 --
701 p_object_version_number := l_object_version_number;
702 p_return_status := hr_multi_message.get_return_status_disable;
703 hr_utility.set_location(' Leaving:' || l_proc, 30);
704 when others then
705 --
706 -- When Multiple Message Detection is enabled catch
707 -- any Application specific or other unexpected
708 -- exceptions. Adding appropriate details to the
709 -- Multiple Message List. Otherwise re-raise the
710 -- error.
711 --
712 rollback to hold_offer_swi;
713 if hr_multi_message.unexpected_error_add(l_proc) then
714 hr_utility.set_location(' Leaving:' || l_proc,40);
715 raise;
716 end if;
717 --
718 -- Reset IN OUT and set OUT parameters
719 --
720 p_object_version_number := l_object_version_number;
721 p_return_status := hr_multi_message.get_return_status_disable;
722 hr_utility.set_location(' Leaving:' || l_proc,50);
723 end hold_offer;
724 -- ----------------------------------------------------------------------------
725 -- |-----------------------------< release_offer >----------------------------|
726 -- ----------------------------------------------------------------------------
727 PROCEDURE release_offer
728 (p_validate in number default hr_api.g_false_num
729 ,p_effective_date in date default hr_api.g_date
730 ,p_offer_id in number
731 ,p_respondent_id in number default hr_api.g_number
732 ,p_change_reason in varchar2 default hr_api.g_varchar2
733 ,p_status_change_date in date default hr_api.g_date
734 ,p_note_text in varchar2 default hr_api.g_varchar2
735 ,p_object_version_number in out nocopy number
736 ,p_return_status out nocopy varchar2
737 ) is
738 --
739 -- Variables for API Boolean parameters
740 l_validate boolean;
741 --
742 -- Variables for IN/OUT parameters
743 l_object_version_number number;
744 --
745 -- Other variables
746 l_proc varchar2(72) := g_package ||'release_offer';
747 Begin
748 hr_utility.set_location(' Entering:' || l_proc,10);
749 --
750 -- Issue a savepoint
751 --
752 savepoint release_offer_swi;
753 --
754 -- Initialise Multiple Message Detection
755 --
756 hr_multi_message.enable_message_list;
757 --
758 -- Remember IN OUT parameter IN values
759 --
760 l_object_version_number := p_object_version_number;
761 --
762 -- Convert constant values to their corresponding boolean value
763 --
764 l_validate :=
765 hr_api.constant_to_boolean
766 (p_constant_value => p_validate);
767 --
768 -- Register Surrogate ID or user key values
769 --
770 --
771 -- Call API
772 --
773 irc_offers_api.release_offer
774 (p_validate => l_validate
775 ,p_effective_date => p_effective_date
776 ,p_offer_id => p_offer_id
777 ,p_respondent_id => p_respondent_id
778 ,p_change_reason => p_change_reason
779 ,p_status_change_date => p_status_change_date
780 ,p_note_text => p_note_text
781 ,p_object_version_number => p_object_version_number
782 );
783 --
784 -- Convert API warning boolean parameter values to specific
785 -- messages and add them to Multiple Message List
786 --
787 --
788 -- Convert API non-warning boolean parameter values
789 --
790 --
791 -- Derive the API return status value based on whether
792 -- messages of any type exist in the Multiple Message List.
793 -- Also disable Multiple Message Detection.
794 --
795 p_return_status := hr_multi_message.get_return_status_disable;
796 hr_utility.set_location(' Leaving:' || l_proc,20);
797 --
798 exception
799 when hr_multi_message.error_message_exist then
800 --
801 -- Catch the Multiple Message List exception which
802 -- indicates API processing has been aborted because
803 -- at least one message exists in the list.
804 --
805 rollback to release_offer_swi;
806 --
807 -- Reset IN OUT parameters and set OUT parameters
808 --
809 p_object_version_number := l_object_version_number;
810 p_return_status := hr_multi_message.get_return_status_disable;
811 hr_utility.set_location(' Leaving:' || l_proc, 30);
812 when others then
813 --
814 -- When Multiple Message Detection is enabled catch
815 -- any Application specific or other unexpected
816 -- exceptions. Adding appropriate details to the
817 -- Multiple Message List. Otherwise re-raise the
818 -- error.
819 --
820 rollback to release_offer_swi;
821 if hr_multi_message.unexpected_error_add(l_proc) then
822 hr_utility.set_location(' Leaving:' || l_proc,40);
823 raise;
824 end if;
825 --
826 -- Reset IN OUT and set OUT parameters
827 --
828 p_object_version_number := l_object_version_number;
829 p_return_status := hr_multi_message.get_return_status_disable;
830 hr_utility.set_location(' Leaving:' || l_proc,50);
831 end release_offer;
832 -- ----------------------------------------------------------------------------
833 -- |------------------------< create_offer_assignment >-----------------------|
834 -- ----------------------------------------------------------------------------
835 PROCEDURE create_offer_assignment
836 (p_assignment_id in out nocopy number
837 ,p_effective_start_date out nocopy date
838 ,p_effective_end_date out nocopy date
839 ,p_business_group_id in number
840 ,p_recruiter_id in number default null
841 ,p_grade_id in number default null
842 ,p_position_id in number default null
843 ,p_job_id in number default null
844 ,p_assignment_status_type_id in number
845 ,p_payroll_id in number default null
846 ,p_location_id in number default null
847 ,p_person_referred_by_id in number default null
848 ,p_supervisor_id in number default null
849 ,p_special_ceiling_step_id in number default null
850 ,p_person_id in number
851 ,p_recruitment_activity_id in number default null
852 ,p_source_organization_id in number default null
853 ,p_organization_id in number
854 ,p_people_group_id in number default null
855 ,p_soft_coding_keyflex_id in number default null
856 ,p_vacancy_id in number default null
857 ,p_pay_basis_id in number default null
858 ,p_assignment_sequence out nocopy number
859 ,p_assignment_type in varchar2
860 ,p_primary_flag in varchar2
861 ,p_application_id in number default null
862 ,p_assignment_number in out nocopy varchar2
863 ,p_change_reason in varchar2 default null
864 ,p_comment_id out nocopy number
865 ,p_comments in varchar2 default null
866 ,p_date_probation_end in date default null
867 ,p_default_code_comb_id in number default null
868 ,p_employment_category in varchar2 default null
869 ,p_frequency in varchar2 default null
870 ,p_internal_address_line in varchar2 default null
871 ,p_manager_flag in varchar2 default null
872 ,p_normal_hours in number default null
873 ,p_perf_review_period in number default null
874 ,p_perf_review_period_frequency in varchar2 default null
875 ,p_period_of_service_id in number default null
876 ,p_probation_period in number default null
877 ,p_probation_unit in varchar2 default null
878 ,p_sal_review_period in number default null
879 ,p_sal_review_period_frequency in varchar2 default null
880 ,p_set_of_books_id in number default null
881 ,p_source_type in varchar2 default null
882 ,p_time_normal_finish in varchar2 default null
883 ,p_time_normal_start in varchar2 default null
884 ,p_bargaining_unit_code in varchar2 default null
885 ,p_labour_union_member_flag in varchar2 default null
886 ,p_hourly_salaried_code in varchar2 default null
887 ,p_request_id in number default null
888 ,p_program_application_id in number default null
889 ,p_program_id in number default null
890 ,p_program_update_date in date default null
891 ,p_ass_attribute_category in varchar2 default null
892 ,p_ass_attribute1 in varchar2 default null
893 ,p_ass_attribute2 in varchar2 default null
894 ,p_ass_attribute3 in varchar2 default null
895 ,p_ass_attribute4 in varchar2 default null
896 ,p_ass_attribute5 in varchar2 default null
897 ,p_ass_attribute6 in varchar2 default null
898 ,p_ass_attribute7 in varchar2 default null
899 ,p_ass_attribute8 in varchar2 default null
900 ,p_ass_attribute9 in varchar2 default null
901 ,p_ass_attribute10 in varchar2 default null
902 ,p_ass_attribute11 in varchar2 default null
903 ,p_ass_attribute12 in varchar2 default null
904 ,p_ass_attribute13 in varchar2 default null
905 ,p_ass_attribute14 in varchar2 default null
906 ,p_ass_attribute15 in varchar2 default null
907 ,p_ass_attribute16 in varchar2 default null
908 ,p_ass_attribute17 in varchar2 default null
909 ,p_ass_attribute18 in varchar2 default null
910 ,p_ass_attribute19 in varchar2 default null
911 ,p_ass_attribute20 in varchar2 default null
912 ,p_ass_attribute21 in varchar2 default null
913 ,p_ass_attribute22 in varchar2 default null
914 ,p_ass_attribute23 in varchar2 default null
915 ,p_ass_attribute24 in varchar2 default null
916 ,p_ass_attribute25 in varchar2 default null
917 ,p_ass_attribute26 in varchar2 default null
918 ,p_ass_attribute27 in varchar2 default null
919 ,p_ass_attribute28 in varchar2 default null
920 ,p_ass_attribute29 in varchar2 default null
921 ,p_ass_attribute30 in varchar2 default null
922 ,p_title in varchar2 default null
923 ,p_validate_df_flex in number default null
924 ,p_object_version_number out nocopy number
925 ,p_effective_date in date
926 ,p_validate in number default hr_api.g_false_num
927 ,p_contract_id in number default null
928 ,p_establishment_id in number default null
929 ,p_collective_agreement_id in number default null
930 ,p_cagr_grade_def_id in number default null
931 ,p_cagr_id_flex_num in number default null
932 ,p_notice_period in number default null
933 ,p_notice_period_uom in varchar2 default null
934 ,p_employee_category in varchar2 default null
935 ,p_work_at_home in varchar2 default null
936 ,p_job_post_source_name in varchar2 default null
937 ,p_posting_content_id in number default null
938 ,p_placement_date_start in date default null
939 ,p_vendor_id in number default null
940 ,p_vendor_employee_number in varchar2 default null
941 ,p_vendor_assignment_number in varchar2 default null
942 ,p_assignment_category in varchar2 default null
943 ,p_project_title in varchar2 default null
944 ,p_applicant_rank in number default null
945 ,p_grade_ladder_pgm_id in number default null
946 ,p_supervisor_assignment_id in number default null
947 ,p_vendor_site_id in number default null
948 ,p_po_header_id in number default null
949 ,p_po_line_id in number default null
950 ,p_projected_assignment_end in date default null
951 ,p_return_status out nocopy varchar2
952 ) is
953 --
954 -- Variables for API Boolean parameters
955 l_validate_df_flex boolean;
956 l_other_manager_warning boolean;
957 l_hourly_salaried_warning boolean;
958 l_validate boolean;
959 --
960 -- Variables for IN/OUT parameters
961 l_assignment_number per_all_assignments_f.assignment_number%TYPE;
962 --
963 -- Other variables
964 l_proc varchar2(72) := g_package ||'create_offer_assignment';
965 Begin
966 hr_utility.set_location(' Entering:' || l_proc,10);
967 --
968 -- Issue a savepoint
969 --
970 savepoint create_offer_assignment_swi;
971 --
972 -- Initialise Multiple Message Detection
973 --
974 hr_multi_message.enable_message_list;
975 --
976 -- Remember IN OUT parameter IN values
977 --
978 l_assignment_number := p_assignment_number;
979 --
980 -- Convert constant values to their corresponding boolean value
981 --
982 l_validate_df_flex :=
983 hr_api.constant_to_boolean
984 (p_constant_value => p_validate_df_flex);
985 l_validate :=
986 hr_api.constant_to_boolean
987 (p_constant_value => p_validate);
988 --
989 -- Register Surrogate ID or user key values
990 --
991 per_asg_ins.set_base_key_value
992 (p_assignment_id => p_assignment_id
993 );
994 --
995 -- Call API
996 --
997 irc_offers_api.create_offer_assignment
998 (p_assignment_id => p_assignment_id
999 ,p_effective_start_date => p_effective_start_date
1000 ,p_effective_end_date => p_effective_end_date
1001 ,p_business_group_id => p_business_group_id
1002 ,p_recruiter_id => p_recruiter_id
1003 ,p_grade_id => p_grade_id
1004 ,p_position_id => p_position_id
1005 ,p_job_id => p_job_id
1006 ,p_assignment_status_type_id => p_assignment_status_type_id
1007 ,p_payroll_id => p_payroll_id
1008 ,p_location_id => p_location_id
1009 ,p_person_referred_by_id => p_person_referred_by_id
1010 ,p_supervisor_id => p_supervisor_id
1011 ,p_special_ceiling_step_id => p_special_ceiling_step_id
1012 ,p_person_id => p_person_id
1013 ,p_recruitment_activity_id => p_recruitment_activity_id
1014 ,p_source_organization_id => p_source_organization_id
1015 ,p_organization_id => p_organization_id
1016 ,p_people_group_id => p_people_group_id
1017 ,p_soft_coding_keyflex_id => p_soft_coding_keyflex_id
1018 ,p_vacancy_id => p_vacancy_id
1019 ,p_pay_basis_id => p_pay_basis_id
1020 ,p_assignment_sequence => p_assignment_sequence
1021 ,p_assignment_type => p_assignment_type
1022 ,p_primary_flag => p_primary_flag
1023 ,p_application_id => p_application_id
1024 ,p_assignment_number => p_assignment_number
1025 ,p_change_reason => p_change_reason
1026 ,p_comment_id => p_comment_id
1027 ,p_comments => p_comments
1028 ,p_date_probation_end => p_date_probation_end
1029 ,p_default_code_comb_id => p_default_code_comb_id
1030 ,p_employment_category => p_employment_category
1031 ,p_frequency => p_frequency
1032 ,p_internal_address_line => p_internal_address_line
1033 ,p_manager_flag => p_manager_flag
1034 ,p_normal_hours => p_normal_hours
1035 ,p_perf_review_period => p_perf_review_period
1036 ,p_perf_review_period_frequency => p_perf_review_period_frequency
1037 ,p_period_of_service_id => p_period_of_service_id
1038 ,p_probation_period => p_probation_period
1039 ,p_probation_unit => p_probation_unit
1040 ,p_sal_review_period => p_sal_review_period
1041 ,p_sal_review_period_frequency => p_sal_review_period_frequency
1042 ,p_set_of_books_id => p_set_of_books_id
1043 ,p_source_type => p_source_type
1044 ,p_time_normal_finish => p_time_normal_finish
1045 ,p_time_normal_start => p_time_normal_start
1046 ,p_bargaining_unit_code => p_bargaining_unit_code
1047 ,p_labour_union_member_flag => p_labour_union_member_flag
1048 ,p_hourly_salaried_code => p_hourly_salaried_code
1049 ,p_request_id => p_request_id
1050 ,p_program_application_id => p_program_application_id
1051 ,p_program_id => p_program_id
1052 ,p_program_update_date => p_program_update_date
1053 ,p_ass_attribute_category => p_ass_attribute_category
1054 ,p_ass_attribute1 => p_ass_attribute1
1055 ,p_ass_attribute2 => p_ass_attribute2
1056 ,p_ass_attribute3 => p_ass_attribute3
1057 ,p_ass_attribute4 => p_ass_attribute4
1058 ,p_ass_attribute5 => p_ass_attribute5
1059 ,p_ass_attribute6 => p_ass_attribute6
1060 ,p_ass_attribute7 => p_ass_attribute7
1061 ,p_ass_attribute8 => p_ass_attribute8
1062 ,p_ass_attribute9 => p_ass_attribute9
1063 ,p_ass_attribute10 => p_ass_attribute10
1064 ,p_ass_attribute11 => p_ass_attribute11
1065 ,p_ass_attribute12 => p_ass_attribute12
1066 ,p_ass_attribute13 => p_ass_attribute13
1067 ,p_ass_attribute14 => p_ass_attribute14
1068 ,p_ass_attribute15 => p_ass_attribute15
1069 ,p_ass_attribute16 => p_ass_attribute16
1070 ,p_ass_attribute17 => p_ass_attribute17
1071 ,p_ass_attribute18 => p_ass_attribute18
1072 ,p_ass_attribute19 => p_ass_attribute19
1073 ,p_ass_attribute20 => p_ass_attribute20
1074 ,p_ass_attribute21 => p_ass_attribute21
1075 ,p_ass_attribute22 => p_ass_attribute22
1076 ,p_ass_attribute23 => p_ass_attribute23
1077 ,p_ass_attribute24 => p_ass_attribute24
1078 ,p_ass_attribute25 => p_ass_attribute25
1079 ,p_ass_attribute26 => p_ass_attribute26
1080 ,p_ass_attribute27 => p_ass_attribute27
1081 ,p_ass_attribute28 => p_ass_attribute28
1082 ,p_ass_attribute29 => p_ass_attribute29
1083 ,p_ass_attribute30 => p_ass_attribute30
1084 ,p_title => p_title
1085 ,p_validate_df_flex => l_validate_df_flex
1086 ,p_object_version_number => p_object_version_number
1087 ,p_other_manager_warning => l_other_manager_warning
1088 ,p_hourly_salaried_warning => l_hourly_salaried_warning
1089 ,p_effective_date => p_effective_date
1090 ,p_validate => l_validate
1091 ,p_contract_id => p_contract_id
1092 ,p_establishment_id => p_establishment_id
1093 ,p_collective_agreement_id => p_collective_agreement_id
1094 ,p_cagr_grade_def_id => p_cagr_grade_def_id
1095 ,p_cagr_id_flex_num => p_cagr_id_flex_num
1096 ,p_notice_period => p_notice_period
1097 ,p_notice_period_uom => p_notice_period_uom
1098 ,p_employee_category => p_employee_category
1099 ,p_work_at_home => p_work_at_home
1100 ,p_job_post_source_name => p_job_post_source_name
1101 ,p_posting_content_id => p_posting_content_id
1102 ,p_placement_date_start => p_placement_date_start
1103 ,p_vendor_id => p_vendor_id
1104 ,p_vendor_employee_number => p_vendor_employee_number
1105 ,p_vendor_assignment_number => p_vendor_assignment_number
1106 ,p_assignment_category => p_assignment_category
1107 ,p_project_title => p_project_title
1108 ,p_applicant_rank => p_applicant_rank
1109 ,p_grade_ladder_pgm_id => p_grade_ladder_pgm_id
1110 ,p_supervisor_assignment_id => p_supervisor_assignment_id
1111 ,p_vendor_site_id => p_vendor_site_id
1112 ,p_po_header_id => p_po_header_id
1113 ,p_po_line_id => p_po_line_id
1114 ,p_projected_assignment_end => p_projected_assignment_end
1115 );
1116 --
1117 -- Convert API warning boolean parameter values to specific
1118 -- messages and add them to Multiple Message List
1119 --
1120 if l_other_manager_warning then
1121 fnd_message.set_name('PER', 'HR_289215_DUPLICATE_MANAGERS');
1122 hr_multi_message.add
1123 (p_message_type => hr_multi_message.g_warning_msg
1124 );
1125 end if;
1126 if l_hourly_salaried_warning then
1127 fnd_message.set_name('PER', 'HR_289648_CWK_HR_CODE_NOT_NULL');
1128 hr_multi_message.add
1129 (p_message_type => hr_multi_message.g_warning_msg
1130 );
1131 end if;
1132 --
1133 -- Convert API non-warning boolean parameter values
1134 --
1135 --
1136 -- Derive the API return status value based on whether
1137 -- messages of any type exist in the Multiple Message List.
1138 -- Also disable Multiple Message Detection.
1139 --
1140 p_return_status := hr_multi_message.get_return_status_disable;
1141 hr_utility.set_location(' Leaving:' || l_proc,20);
1142 --
1143 exception
1144 when hr_multi_message.error_message_exist then
1145 --
1146 -- Catch the Multiple Message List exception which
1147 -- indicates API processing has been aborted because
1148 -- at least one message exists in the list.
1149 --
1150 rollback to create_offer_assignment_swi;
1151 --
1152 -- Reset IN OUT parameters and set OUT parameters
1153 --
1154 p_assignment_id := null;
1155 p_effective_start_date := null;
1156 p_effective_end_date := null;
1157 p_assignment_sequence := null;
1158 p_assignment_number := l_assignment_number;
1159 p_comment_id := null;
1160 p_object_version_number := null;
1161 --
1162 p_return_status := hr_multi_message.get_return_status_disable;
1163 hr_utility.set_location(' Leaving:' || l_proc, 30);
1164 when others then
1165 --
1166 -- When Multiple Message Detection is enabled catch
1167 -- any Application specific or other unexpected
1168 -- exceptions. Adding appropriate details to the
1169 -- Multiple Message List. Otherwise re-raise the
1170 -- error.
1171 --
1172 rollback to create_offer_assignment_swi;
1173 if hr_multi_message.unexpected_error_add(l_proc) then
1174 hr_utility.set_location(' Leaving:' || l_proc,40);
1175 raise;
1176 end if;
1177 --
1178 -- Reset IN OUT and set OUT parameters
1179 --
1180 p_assignment_id := null;
1181 p_effective_start_date := null;
1182 p_effective_end_date := null;
1183 p_assignment_sequence := null;
1184 p_assignment_number := l_assignment_number;
1185 p_comment_id := null;
1186 p_object_version_number := null;
1187 --
1188 p_return_status := hr_multi_message.get_return_status_disable;
1189 hr_utility.set_location(' Leaving:' || l_proc,50);
1190 end create_offer_assignment;
1191 -- ----------------------------------------------------------------------------
1192 -- |------------------------< update_offer_assignment >-----------------------|
1193 -- ----------------------------------------------------------------------------
1194 PROCEDURE update_offer_assignment
1195 (p_assignment_id in out nocopy number
1196 ,p_effective_start_date out nocopy date
1197 ,p_effective_end_date out nocopy date
1198 ,p_business_group_id out nocopy number
1199 ,p_recruiter_id in number default hr_api.g_number
1200 ,p_grade_id in number default hr_api.g_number
1201 ,p_position_id in number default hr_api.g_number
1202 ,p_job_id in number default hr_api.g_number
1203 ,p_assignment_status_type_id in number default hr_api.g_number
1204 ,p_payroll_id in number default hr_api.g_number
1205 ,p_location_id in number default hr_api.g_number
1206 ,p_person_referred_by_id in number default hr_api.g_number
1207 ,p_supervisor_id in number default hr_api.g_number
1208 ,p_special_ceiling_step_id in number default hr_api.g_number
1209 ,p_recruitment_activity_id in number default hr_api.g_number
1210 ,p_source_organization_id in number default hr_api.g_number
1211 ,p_organization_id in number default hr_api.g_number
1212 ,p_people_group_id in number default hr_api.g_number
1213 ,p_soft_coding_keyflex_id in number default hr_api.g_number
1214 ,p_vacancy_id in number default hr_api.g_number
1215 ,p_pay_basis_id in number default hr_api.g_number
1216 ,p_assignment_type in varchar2 default hr_api.g_varchar2
1217 ,p_primary_flag in varchar2 default hr_api.g_varchar2
1218 ,p_application_id in number default hr_api.g_number
1219 ,p_assignment_number in varchar2 default hr_api.g_varchar2
1220 ,p_change_reason in varchar2 default hr_api.g_varchar2
1221 ,p_comment_id out nocopy number
1222 ,p_comments in varchar2 default hr_api.g_varchar2
1223 ,p_date_probation_end in date default hr_api.g_date
1224 ,p_default_code_comb_id in number default hr_api.g_number
1225 ,p_employment_category in varchar2 default hr_api.g_varchar2
1226 ,p_frequency in varchar2 default hr_api.g_varchar2
1227 ,p_internal_address_line in varchar2 default hr_api.g_varchar2
1228 ,p_manager_flag in varchar2 default hr_api.g_varchar2
1229 ,p_normal_hours in number default hr_api.g_number
1230 ,p_perf_review_period in number default hr_api.g_number
1231 ,p_perf_review_period_frequency in varchar2 default hr_api.g_varchar2
1232 ,p_period_of_service_id in number default hr_api.g_number
1233 ,p_probation_period in number default hr_api.g_number
1234 ,p_probation_unit in varchar2 default hr_api.g_varchar2
1235 ,p_sal_review_period in number default hr_api.g_number
1236 ,p_sal_review_period_frequency in varchar2 default hr_api.g_varchar2
1237 ,p_set_of_books_id in number default hr_api.g_number
1238 ,p_source_type in varchar2 default hr_api.g_varchar2
1239 ,p_time_normal_finish in varchar2 default hr_api.g_varchar2
1240 ,p_time_normal_start in varchar2 default hr_api.g_varchar2
1241 ,p_bargaining_unit_code in varchar2 default hr_api.g_varchar2
1242 ,p_labour_union_member_flag in varchar2 default hr_api.g_varchar2
1243 ,p_hourly_salaried_code in varchar2 default hr_api.g_varchar2
1244 ,p_request_id in number default hr_api.g_number
1245 ,p_program_application_id in number default hr_api.g_number
1246 ,p_program_id in number default hr_api.g_number
1247 ,p_program_update_date in date default hr_api.g_date
1248 ,p_ass_attribute_category in varchar2 default hr_api.g_varchar2
1249 ,p_ass_attribute1 in varchar2 default hr_api.g_varchar2
1250 ,p_ass_attribute2 in varchar2 default hr_api.g_varchar2
1251 ,p_ass_attribute3 in varchar2 default hr_api.g_varchar2
1252 ,p_ass_attribute4 in varchar2 default hr_api.g_varchar2
1253 ,p_ass_attribute5 in varchar2 default hr_api.g_varchar2
1254 ,p_ass_attribute6 in varchar2 default hr_api.g_varchar2
1255 ,p_ass_attribute7 in varchar2 default hr_api.g_varchar2
1256 ,p_ass_attribute8 in varchar2 default hr_api.g_varchar2
1257 ,p_ass_attribute9 in varchar2 default hr_api.g_varchar2
1258 ,p_ass_attribute10 in varchar2 default hr_api.g_varchar2
1259 ,p_ass_attribute11 in varchar2 default hr_api.g_varchar2
1260 ,p_ass_attribute12 in varchar2 default hr_api.g_varchar2
1261 ,p_ass_attribute13 in varchar2 default hr_api.g_varchar2
1262 ,p_ass_attribute14 in varchar2 default hr_api.g_varchar2
1263 ,p_ass_attribute15 in varchar2 default hr_api.g_varchar2
1264 ,p_ass_attribute16 in varchar2 default hr_api.g_varchar2
1265 ,p_ass_attribute17 in varchar2 default hr_api.g_varchar2
1266 ,p_ass_attribute18 in varchar2 default hr_api.g_varchar2
1267 ,p_ass_attribute19 in varchar2 default hr_api.g_varchar2
1268 ,p_ass_attribute20 in varchar2 default hr_api.g_varchar2
1269 ,p_ass_attribute21 in varchar2 default hr_api.g_varchar2
1270 ,p_ass_attribute22 in varchar2 default hr_api.g_varchar2
1271 ,p_ass_attribute23 in varchar2 default hr_api.g_varchar2
1272 ,p_ass_attribute24 in varchar2 default hr_api.g_varchar2
1273 ,p_ass_attribute25 in varchar2 default hr_api.g_varchar2
1274 ,p_ass_attribute26 in varchar2 default hr_api.g_varchar2
1275 ,p_ass_attribute27 in varchar2 default hr_api.g_varchar2
1276 ,p_ass_attribute28 in varchar2 default hr_api.g_varchar2
1277 ,p_ass_attribute29 in varchar2 default hr_api.g_varchar2
1278 ,p_ass_attribute30 in varchar2 default hr_api.g_varchar2
1279 ,p_title in varchar2 default hr_api.g_varchar2
1280 ,p_contract_id in number default hr_api.g_number
1281 ,p_establishment_id in number default hr_api.g_number
1282 ,p_collective_agreement_id in number default hr_api.g_number
1283 ,p_cagr_grade_def_id in number default hr_api.g_number
1284 ,p_cagr_id_flex_num in number default hr_api.g_number
1285 ,p_asg_object_version_number in out nocopy number
1286 ,p_notice_period in number default hr_api.g_number
1287 ,p_notice_period_uom in varchar2 default hr_api.g_varchar2
1288 ,p_employee_category in varchar2 default hr_api.g_varchar2
1289 ,p_work_at_home in varchar2 default hr_api.g_varchar2
1290 ,p_job_post_source_name in varchar2 default hr_api.g_varchar2
1291 ,p_posting_content_id in number default hr_api.g_number
1292 ,p_placement_date_start in date default hr_api.g_date
1293 ,p_vendor_id in number default hr_api.g_number
1294 ,p_vendor_employee_number in varchar2 default hr_api.g_varchar2
1295 ,p_vendor_assignment_number in varchar2 default hr_api.g_varchar2
1296 ,p_assignment_category in varchar2 default hr_api.g_varchar2
1297 ,p_project_title in varchar2 default hr_api.g_varchar2
1298 ,p_applicant_rank in number default hr_api.g_number
1299 ,p_grade_ladder_pgm_id in number default hr_api.g_number
1300 ,p_supervisor_assignment_id in number default hr_api.g_number
1301 ,p_vendor_site_id in number default hr_api.g_number
1302 ,p_po_header_id in number default hr_api.g_number
1303 ,p_po_line_id in number default hr_api.g_number
1304 ,p_projected_assignment_end in date default hr_api.g_date
1305 ,p_payroll_id_updated out nocopy number
1306 ,p_validation_start_date out nocopy date
1307 ,p_validation_end_date out nocopy date
1308 ,p_effective_date in date default hr_api.g_date
1309 ,p_datetrack_mode in varchar2 default hr_api.g_varchar2
1310 ,p_validate in number default hr_api.g_false_num
1311 ,p_offer_id in out nocopy number
1312 ,p_offer_status in varchar2 default null
1313 ,p_return_status out nocopy varchar2
1314 ) is
1315 --
1316 -- Variables for API Boolean parameters
1317 l_payroll_id_updated boolean;
1318 l_other_manager_warning boolean;
1319 l_hourly_salaried_warning boolean;
1320 l_no_managers_warning boolean;
1321 l_org_now_no_manager_warning boolean;
1322 l_validate boolean;
1323 --
1324 -- Variables for IN/OUT parameters
1325 l_assignment_id number;
1326 l_asg_object_version_number number;
1327 l_offer_id number;
1328 --
1329 -- Other variables
1330 l_proc varchar2(72) := g_package ||'update_offer_assignment';
1331 Begin
1332 hr_utility.set_location(' Entering:' || l_proc,10);
1333 --
1334 -- Issue a savepoint
1335 --
1336 savepoint update_offer_assignment_swi;
1337 --
1338 -- Initialise Multiple Message Detection
1339 --
1340 hr_multi_message.enable_message_list;
1341 --
1342 -- Remember IN OUT parameter IN values
1343 --
1344 l_assignment_id := p_assignment_id;
1345 l_asg_object_version_number := p_asg_object_version_number;
1346 l_offer_id := p_offer_id;
1347 --
1348 -- Convert constant values to their corresponding boolean value
1349 --
1350 l_payroll_id_updated :=
1351 hr_api.constant_to_boolean
1352 (p_constant_value => p_payroll_id_updated);
1353 l_validate :=
1354 hr_api.constant_to_boolean
1355 (p_constant_value => p_validate);
1356 --
1357 -- Register Surrogate ID or user key values
1358 --
1359 --
1360 -- Call API
1361 --
1362 irc_offers_api.update_offer_assignment
1363 (p_assignment_id => p_assignment_id
1364 ,p_effective_start_date => p_effective_start_date
1365 ,p_effective_end_date => p_effective_end_date
1366 ,p_business_group_id => p_business_group_id
1367 ,p_recruiter_id => p_recruiter_id
1368 ,p_grade_id => p_grade_id
1369 ,p_position_id => p_position_id
1370 ,p_job_id => p_job_id
1371 ,p_assignment_status_type_id => p_assignment_status_type_id
1372 ,p_payroll_id => p_payroll_id
1373 ,p_location_id => p_location_id
1374 ,p_person_referred_by_id => p_person_referred_by_id
1375 ,p_supervisor_id => p_supervisor_id
1376 ,p_special_ceiling_step_id => p_special_ceiling_step_id
1377 ,p_recruitment_activity_id => p_recruitment_activity_id
1378 ,p_source_organization_id => p_source_organization_id
1379 ,p_organization_id => p_organization_id
1380 ,p_people_group_id => p_people_group_id
1381 ,p_soft_coding_keyflex_id => p_soft_coding_keyflex_id
1382 ,p_vacancy_id => p_vacancy_id
1383 ,p_pay_basis_id => p_pay_basis_id
1384 ,p_assignment_type => p_assignment_type
1385 ,p_primary_flag => p_primary_flag
1386 ,p_application_id => p_application_id
1387 ,p_assignment_number => p_assignment_number
1388 ,p_change_reason => p_change_reason
1389 ,p_comment_id => p_comment_id
1390 ,p_comments => p_comments
1391 ,p_date_probation_end => p_date_probation_end
1392 ,p_default_code_comb_id => p_default_code_comb_id
1393 ,p_employment_category => p_employment_category
1394 ,p_frequency => p_frequency
1395 ,p_internal_address_line => p_internal_address_line
1396 ,p_manager_flag => p_manager_flag
1397 ,p_normal_hours => p_normal_hours
1398 ,p_perf_review_period => p_perf_review_period
1399 ,p_perf_review_period_frequency => p_perf_review_period_frequency
1400 ,p_period_of_service_id => p_period_of_service_id
1401 ,p_probation_period => p_probation_period
1402 ,p_probation_unit => p_probation_unit
1403 ,p_sal_review_period => p_sal_review_period
1404 ,p_sal_review_period_frequency => p_sal_review_period_frequency
1405 ,p_set_of_books_id => p_set_of_books_id
1406 ,p_source_type => p_source_type
1407 ,p_time_normal_finish => p_time_normal_finish
1408 ,p_time_normal_start => p_time_normal_start
1409 ,p_bargaining_unit_code => p_bargaining_unit_code
1410 ,p_labour_union_member_flag => p_labour_union_member_flag
1411 ,p_hourly_salaried_code => p_hourly_salaried_code
1412 ,p_request_id => p_request_id
1413 ,p_program_application_id => p_program_application_id
1414 ,p_program_id => p_program_id
1415 ,p_program_update_date => p_program_update_date
1416 ,p_ass_attribute_category => p_ass_attribute_category
1417 ,p_ass_attribute1 => p_ass_attribute1
1418 ,p_ass_attribute2 => p_ass_attribute2
1419 ,p_ass_attribute3 => p_ass_attribute3
1420 ,p_ass_attribute4 => p_ass_attribute4
1421 ,p_ass_attribute5 => p_ass_attribute5
1422 ,p_ass_attribute6 => p_ass_attribute6
1423 ,p_ass_attribute7 => p_ass_attribute7
1424 ,p_ass_attribute8 => p_ass_attribute8
1425 ,p_ass_attribute9 => p_ass_attribute9
1426 ,p_ass_attribute10 => p_ass_attribute10
1427 ,p_ass_attribute11 => p_ass_attribute11
1428 ,p_ass_attribute12 => p_ass_attribute12
1429 ,p_ass_attribute13 => p_ass_attribute13
1430 ,p_ass_attribute14 => p_ass_attribute14
1431 ,p_ass_attribute15 => p_ass_attribute15
1432 ,p_ass_attribute16 => p_ass_attribute16
1433 ,p_ass_attribute17 => p_ass_attribute17
1434 ,p_ass_attribute18 => p_ass_attribute18
1435 ,p_ass_attribute19 => p_ass_attribute19
1436 ,p_ass_attribute20 => p_ass_attribute20
1437 ,p_ass_attribute21 => p_ass_attribute21
1438 ,p_ass_attribute22 => p_ass_attribute22
1439 ,p_ass_attribute23 => p_ass_attribute23
1440 ,p_ass_attribute24 => p_ass_attribute24
1441 ,p_ass_attribute25 => p_ass_attribute25
1442 ,p_ass_attribute26 => p_ass_attribute26
1443 ,p_ass_attribute27 => p_ass_attribute27
1444 ,p_ass_attribute28 => p_ass_attribute28
1445 ,p_ass_attribute29 => p_ass_attribute29
1446 ,p_ass_attribute30 => p_ass_attribute30
1447 ,p_title => p_title
1448 ,p_contract_id => p_contract_id
1449 ,p_establishment_id => p_establishment_id
1450 ,p_collective_agreement_id => p_collective_agreement_id
1451 ,p_cagr_grade_def_id => p_cagr_grade_def_id
1452 ,p_cagr_id_flex_num => p_cagr_id_flex_num
1453 ,p_asg_object_version_number => p_asg_object_version_number
1454 ,p_notice_period => p_notice_period
1455 ,p_notice_period_uom => p_notice_period_uom
1456 ,p_employee_category => p_employee_category
1457 ,p_work_at_home => p_work_at_home
1458 ,p_job_post_source_name => p_job_post_source_name
1459 ,p_posting_content_id => p_posting_content_id
1460 ,p_placement_date_start => p_placement_date_start
1461 ,p_vendor_id => p_vendor_id
1462 ,p_vendor_employee_number => p_vendor_employee_number
1463 ,p_vendor_assignment_number => p_vendor_assignment_number
1464 ,p_assignment_category => p_assignment_category
1465 ,p_project_title => p_project_title
1466 ,p_applicant_rank => p_applicant_rank
1467 ,p_grade_ladder_pgm_id => p_grade_ladder_pgm_id
1468 ,p_supervisor_assignment_id => p_supervisor_assignment_id
1469 ,p_vendor_site_id => p_vendor_site_id
1470 ,p_po_header_id => p_po_header_id
1471 ,p_po_line_id => p_po_line_id
1472 ,p_projected_assignment_end => p_projected_assignment_end
1473 ,p_payroll_id_updated => l_payroll_id_updated
1474 ,p_other_manager_warning => l_other_manager_warning
1475 ,p_hourly_salaried_warning => l_hourly_salaried_warning
1476 ,p_no_managers_warning => l_no_managers_warning
1477 ,p_org_now_no_manager_warning => l_org_now_no_manager_warning
1478 ,p_validation_start_date => p_validation_start_date
1479 ,p_validation_end_date => p_validation_end_date
1480 ,p_effective_date => p_effective_date
1481 ,p_datetrack_mode => p_datetrack_mode
1482 ,p_validate => l_validate
1483 ,p_offer_id => p_offer_id
1484 ,p_offer_status => p_offer_status
1485 );
1486 --
1487 -- Convert API warning boolean parameter values to specific
1488 -- messages and add them to Multiple Message List
1489 --
1490 if l_other_manager_warning then
1491 fnd_message.set_name('PER', 'HR_289215_DUPLICATE_MANAGERS');
1492 hr_multi_message.add
1493 (p_message_type => hr_multi_message.g_warning_msg
1494 );
1495 end if;
1496 if l_hourly_salaried_warning then
1497 fnd_message.set_name('PER', 'HR_289648_CWK_HR_CODE_NOT_NULL');
1498 hr_multi_message.add
1499 (p_message_type => hr_multi_message.g_warning_msg
1500 );
1501 end if;
1502 if l_no_managers_warning then
1503 fnd_message.set_name('PER', 'HR_289214_NO_MANAGERS');
1504 hr_multi_message.add
1505 (p_message_type => hr_multi_message.g_warning_msg
1506 );
1507 end if;
1508 if l_org_now_no_manager_warning then
1509 fnd_message.set_name('PER', 'HR_7429_ASG_INV_MANAGER_FLAG');
1510 hr_multi_message.add
1511 (p_message_type => hr_multi_message.g_warning_msg
1512 );
1513 end if;
1514 --
1515 -- Convert API non-warning boolean parameter values
1516 --
1517 p_payroll_id_updated :=
1518 hr_api.boolean_to_constant
1519 (p_boolean_value => l_payroll_id_updated
1520 );
1521 --
1522 -- Derive the API return status value based on whether
1523 -- messages of any type exist in the Multiple Message List.
1524 -- Also disable Multiple Message Detection.
1525 --
1526 p_return_status := hr_multi_message.get_return_status_disable;
1527 hr_utility.set_location(' Leaving:' || l_proc,20);
1528 --
1529 exception
1530 when hr_multi_message.error_message_exist then
1531 --
1532 -- Catch the Multiple Message List exception which
1533 -- indicates API processing has been aborted because
1534 -- at least one message exists in the list.
1535 --
1536 rollback to update_offer_assignment_swi;
1537 --
1538 -- Reset IN OUT parameters and set OUT parameters
1539 --
1540 p_assignment_id := l_assignment_id;
1541 p_effective_start_date := null;
1542 p_effective_end_date := null;
1543 p_business_group_id := null;
1544 p_comment_id := null;
1545 p_asg_object_version_number := l_asg_object_version_number;
1546 p_payroll_id_updated := null;
1547 p_validation_start_date := null;
1548 p_validation_end_date := null;
1549 p_offer_id := l_offer_id;
1550 p_return_status := hr_multi_message.get_return_status_disable;
1551 hr_utility.set_location(' Leaving:' || l_proc, 30);
1552 when others then
1553 --
1554 -- When Multiple Message Detection is enabled catch
1555 -- any Application specific or other unexpected
1556 -- exceptions. Adding appropriate details to the
1557 -- Multiple Message List. Otherwise re-raise the
1558 -- error.
1559 --
1560 rollback to update_offer_assignment_swi;
1561 if hr_multi_message.unexpected_error_add(l_proc) then
1562 hr_utility.set_location(' Leaving:' || l_proc,40);
1563 raise;
1564 end if;
1565 --
1566 -- Reset IN OUT and set OUT parameters
1567 --
1568 p_assignment_id := l_assignment_id;
1569 p_effective_start_date := null;
1570 p_effective_end_date := null;
1571 p_business_group_id := null;
1572 p_comment_id := null;
1573 p_asg_object_version_number := l_asg_object_version_number;
1574 p_payroll_id_updated := null;
1575 p_validation_start_date := null;
1576 p_validation_end_date := null;
1577 p_offer_id := l_offer_id;
1578 p_return_status := hr_multi_message.get_return_status_disable;
1579 hr_utility.set_location(' Leaving:' || l_proc,50);
1580 end update_offer_assignment;
1581 -- ----------------------------------------------------------------------------
1582 -- |------------------------< delete_offer_assignment >-----------------------|
1583 -- ----------------------------------------------------------------------------
1584 PROCEDURE delete_offer_assignment
1585 (p_validate in number default hr_api.g_false_num
1586 ,p_effective_date in date default hr_api.g_date
1587 ,p_offer_assignment_id in number
1588 ,p_return_status out nocopy varchar2
1589 ) is
1590 --
1591 -- Variables for API Boolean parameters
1592 l_validate boolean;
1593 --
1594 -- Variables for IN/OUT parameters
1595 --
1596 -- Other variables
1597 l_proc varchar2(72) := g_package ||'delete_offer_assignment';
1598 Begin
1599 hr_utility.set_location(' Entering:' || l_proc,10);
1600 --
1601 -- Issue a savepoint
1602 --
1603 savepoint delete_offer_assignment_swi;
1604 --
1605 -- Initialise Multiple Message Detection
1606 --
1607 hr_multi_message.enable_message_list;
1608 --
1609 -- Remember IN OUT parameter IN values
1610 --
1611 --
1612 -- Convert constant values to their corresponding boolean value
1613 --
1614 l_validate :=
1615 hr_api.constant_to_boolean
1616 (p_constant_value => p_validate);
1617 --
1618 -- Register Surrogate ID or user key values
1619 --
1620 --
1621 -- Call API
1622 --
1623 irc_offers_api.delete_offer_assignment
1624 (p_validate => l_validate
1625 ,p_effective_date => p_effective_date
1626 ,p_offer_assignment_id => p_offer_assignment_id
1627 );
1628 --
1629 -- Convert API warning boolean parameter values to specific
1630 -- messages and add them to Multiple Message List
1631 --
1632 --
1633 -- Convert API non-warning boolean parameter values
1634 --
1635 --
1636 -- Derive the API return status value based on whether
1637 -- messages of any type exist in the Multiple Message List.
1638 -- Also disable Multiple Message Detection.
1639 --
1640 p_return_status := hr_multi_message.get_return_status_disable;
1641 hr_utility.set_location(' Leaving:' || l_proc,20);
1642 --
1643 exception
1644 when hr_multi_message.error_message_exist then
1645 --
1646 -- Catch the Multiple Message List exception which
1647 -- indicates API processing has been aborted because
1648 -- at least one message exists in the list.
1649 --
1650 rollback to delete_offer_assignment_swi;
1651 --
1652 -- Reset IN OUT parameters and set OUT parameters
1653 --
1654 p_return_status := hr_multi_message.get_return_status_disable;
1655 hr_utility.set_location(' Leaving:' || l_proc, 30);
1656 when others then
1657 --
1658 -- When Multiple Message Detection is enabled catch
1659 -- any Application specific or other unexpected
1660 -- exceptions. Adding appropriate details to the
1661 -- Multiple Message List. Otherwise re-raise the
1662 -- error.
1663 --
1664 rollback to delete_offer_assignment_swi;
1665 if hr_multi_message.unexpected_error_add(l_proc) then
1666 hr_utility.set_location(' Leaving:' || l_proc,40);
1667 raise;
1668 end if;
1669 --
1670 -- Reset IN OUT and set OUT parameters
1671 --
1672 p_return_status := hr_multi_message.get_return_status_disable;
1673 hr_utility.set_location(' Leaving:' || l_proc,50);
1674 end delete_offer_assignment;
1675 -- ----------------------------------------------------------------------------
1676 -- |--------------------------< upload_offer_letter >-------------------------|
1677 -- ----------------------------------------------------------------------------
1678 PROCEDURE upload_offer_letter
1679 (p_validate in number default hr_api.g_false_num
1680 ,p_offer_letter in BLOB
1681 ,p_offer_id in number
1682 ,p_object_version_number in number
1683 ,p_return_status out nocopy varchar2
1684 ) is
1685 --
1686 -- Variables for API Boolean parameters
1687 l_validate boolean;
1688 --
1689 -- Variables for IN/OUT parameters
1690 --
1691 -- Other variables
1692 l_proc varchar2(72) := g_package ||'upload_offer_letter';
1693 Begin
1694 hr_utility.set_location(' Entering:' || l_proc,10);
1695 --
1696 -- Issue a savepoint
1697 --
1698 savepoint upload_offer_letter_swi;
1699 --
1700 -- Initialise Multiple Message Detection
1701 --
1702 hr_multi_message.enable_message_list;
1703 --
1704 -- Remember IN OUT parameter IN values
1705 --
1706 --
1707 -- Convert constant values to their corresponding boolean value
1708 --
1709 l_validate :=
1710 hr_api.constant_to_boolean
1711 (p_constant_value => p_validate);
1712 --
1713 -- Register Surrogate ID or user key values
1714 --
1715 --
1716 -- Call API
1717 --
1718 irc_offers_api.upload_offer_letter
1719 (p_validate => l_validate
1720 ,p_offer_letter => p_offer_letter
1721 ,p_offer_id => p_offer_id
1722 ,p_object_version_number => p_object_version_number
1723 );
1724 --
1725 -- Convert API warning boolean parameter values to specific
1726 -- messages and add them to Multiple Message List
1727 --
1728 --
1729 -- Convert API non-warning boolean parameter values
1730 --
1731 --
1732 -- Derive the API return status value based on whether
1733 -- messages of any type exist in the Multiple Message List.
1734 -- Also disable Multiple Message Detection.
1735 --
1736 p_return_status := hr_multi_message.get_return_status_disable;
1737 hr_utility.set_location(' Leaving:' || l_proc,20);
1738 --
1739 exception
1740 when hr_multi_message.error_message_exist then
1741 --
1742 -- Catch the Multiple Message List exception which
1743 -- indicates API processing has been aborted because
1744 -- at least one message exists in the list.
1745 --
1746 rollback to upload_offer_letter_swi;
1747 --
1748 -- Reset IN OUT parameters and set OUT parameters
1749 --
1750 p_return_status := hr_multi_message.get_return_status_disable;
1751 hr_utility.set_location(' Leaving:' || l_proc, 30);
1752 when others then
1753 --
1754 -- When Multiple Message Detection is enabled catch
1755 -- any Application specific or other unexpected
1756 -- exceptions. Adding appropriate details to the
1757 -- Multiple Message List. Otherwise re-raise the
1758 -- error.
1759 --
1760 rollback to upload_offer_letter_swi;
1761 if hr_multi_message.unexpected_error_add(l_proc) then
1762 hr_utility.set_location(' Leaving:' || l_proc,40);
1763 raise;
1764 end if;
1765 --
1766 -- Reset IN OUT and set OUT parameters
1767 --
1768 p_return_status := hr_multi_message.get_return_status_disable;
1769 hr_utility.set_location(' Leaving:' || l_proc,50);
1770 end upload_offer_letter;
1771 -- ----------------------------------------------------------------------------
1772 -- |--------------------< is_run_benmgle_for_irec_reqd >----------------------|
1773 -- ----------------------------------------------------------------------------
1774 PROCEDURE is_run_benmgle_for_irec_reqd
1775 (p_assignment_id in number
1776 ,p_effective_start_date in date default trunc(sysdate)
1777 ,p_effective_end_date in date default hr_api.g_eot
1778 ,p_business_group_id in number
1779 ,p_recruiter_id in number default null
1780 ,p_grade_id in number default null
1781 ,p_position_id in number default null
1782 ,p_job_id in number default null
1783 ,p_assignment_status_type_id in number
1784 ,p_payroll_id in number default null
1785 ,p_location_id in number default null
1786 ,p_person_referred_by_id in number default null
1787 ,p_supervisor_id in number default null
1788 ,p_special_ceiling_step_id in number default null
1789 ,p_person_id in number
1790 ,p_recruitment_activity_id in number default null
1791 ,p_source_organization_id in number default null
1792 ,p_organization_id in number
1793 ,p_people_group_id in number default null
1794 ,p_soft_coding_keyflex_id in number default null
1795 ,p_vacancy_id in number default null
1796 ,p_pay_basis_id in number default null
1797 ,p_assignment_sequence in number default 1
1798 ,p_assignment_type in varchar2
1799 ,p_primary_flag in varchar2
1800 ,p_application_id in number default null
1801 ,p_assignment_number in varchar2 default null
1802 ,p_change_reason in varchar2 default null
1803 ,p_comment_id in number default null
1804 ,p_date_probation_end in date default null
1805 ,p_default_code_comb_id in number default null
1806 ,p_employment_category in varchar2 default null
1807 ,p_frequency in varchar2 default null
1808 ,p_internal_address_line in varchar2 default null
1809 ,p_manager_flag in varchar2 default null
1810 ,p_normal_hours in number default null
1811 ,p_perf_review_period in number default null
1812 ,p_perf_review_period_frequency in varchar2 default null
1813 ,p_period_of_service_id in number default null
1814 ,p_probation_period in number default null
1815 ,p_probation_unit in varchar2 default null
1816 ,p_sal_review_period in number default null
1817 ,p_sal_review_period_frequency in varchar2 default null
1818 ,p_set_of_books_id in number default null
1819 ,p_source_type in varchar2 default null
1820 ,p_time_normal_finish in varchar2 default null
1821 ,p_time_normal_start in varchar2 default null
1822 ,p_bargaining_unit_code in varchar2 default null
1823 ,p_labour_union_member_flag in varchar2 default null
1824 ,p_hourly_salaried_code in varchar2 default null
1825 ,p_request_id in number default null
1826 ,p_program_application_id in number default null
1827 ,p_program_id in number default null
1828 ,p_program_update_date in date default null
1829 ,p_ass_attribute_category in varchar2 default null
1830 ,p_ass_attribute1 in varchar2 default null
1831 ,p_ass_attribute2 in varchar2 default null
1832 ,p_ass_attribute3 in varchar2 default null
1833 ,p_ass_attribute4 in varchar2 default null
1834 ,p_ass_attribute5 in varchar2 default null
1835 ,p_ass_attribute6 in varchar2 default null
1836 ,p_ass_attribute7 in varchar2 default null
1837 ,p_ass_attribute8 in varchar2 default null
1838 ,p_ass_attribute9 in varchar2 default null
1839 ,p_ass_attribute10 in varchar2 default null
1840 ,p_ass_attribute11 in varchar2 default null
1841 ,p_ass_attribute12 in varchar2 default null
1842 ,p_ass_attribute13 in varchar2 default null
1843 ,p_ass_attribute14 in varchar2 default null
1844 ,p_ass_attribute15 in varchar2 default null
1845 ,p_ass_attribute16 in varchar2 default null
1846 ,p_ass_attribute17 in varchar2 default null
1847 ,p_ass_attribute18 in varchar2 default null
1848 ,p_ass_attribute19 in varchar2 default null
1849 ,p_ass_attribute20 in varchar2 default null
1850 ,p_ass_attribute21 in varchar2 default null
1851 ,p_ass_attribute22 in varchar2 default null
1852 ,p_ass_attribute23 in varchar2 default null
1853 ,p_ass_attribute24 in varchar2 default null
1854 ,p_ass_attribute25 in varchar2 default null
1855 ,p_ass_attribute26 in varchar2 default null
1856 ,p_ass_attribute27 in varchar2 default null
1857 ,p_ass_attribute28 in varchar2 default null
1858 ,p_ass_attribute29 in varchar2 default null
1859 ,p_ass_attribute30 in varchar2 default null
1860 ,p_title in varchar2 default null
1861 ,p_object_version_number in number default 1
1862 ,p_contract_id in number default null
1863 ,p_establishment_id in number default null
1864 ,p_collective_agreement_id in number default null
1865 ,p_cagr_grade_def_id in number default null
1866 ,p_cagr_id_flex_num in number default null
1867 ,p_notice_period in number default null
1868 ,p_notice_period_uom in varchar2 default null
1869 ,p_employee_category in varchar2 default null
1870 ,p_work_at_home in varchar2 default null
1871 ,p_job_post_source_name in varchar2 default null
1872 ,p_posting_content_id in number default null
1873 ,p_placement_date_start in date default null
1874 ,p_vendor_id in number default null
1875 ,p_vendor_employee_number in varchar2 default null
1876 ,p_vendor_assignment_number in varchar2 default null
1877 ,p_assignment_category in varchar2 default null
1878 ,p_project_title in varchar2 default null
1879 ,p_applicant_rank in number default null
1880 ,p_grade_ladder_pgm_id in number default null
1881 ,p_supervisor_assignment_id in number default null
1882 ,p_vendor_site_id in number default null
1883 ,p_po_header_id in number default null
1884 ,p_po_line_id in number default null
1885 ,p_projected_assignment_end in date default null
1886 ,p_effective_date in date
1887 --
1888 -- pay proposal details
1889 --
1890 ,p_pay_proposal_id in number
1891 ,p_event_id in number default null
1892 ,p_change_date in date default null
1893 ,p_last_change_date in date default null
1894 ,p_next_perf_review_date in date default null
1895 ,p_next_sal_review_date in date default null
1896 ,p_performance_rating in varchar2 default null
1897 ,p_proposal_reason in varchar2 default null
1898 ,p_proposed_salary in varchar2 default null
1899 ,p_review_date in date default null
1900 ,p_attribute_category in varchar2 default null
1901 ,p_attribute1 in varchar2 default null
1902 ,p_attribute2 in varchar2 default null
1903 ,p_attribute3 in varchar2 default null
1904 ,p_attribute4 in varchar2 default null
1905 ,p_attribute5 in varchar2 default null
1906 ,p_attribute6 in varchar2 default null
1907 ,p_attribute7 in varchar2 default null
1908 ,p_attribute8 in varchar2 default null
1909 ,p_attribute9 in varchar2 default null
1910 ,p_attribute10 in varchar2 default null
1911 ,p_attribute11 in varchar2 default null
1912 ,p_attribute12 in varchar2 default null
1913 ,p_attribute13 in varchar2 default null
1914 ,p_attribute14 in varchar2 default null
1915 ,p_attribute15 in varchar2 default null
1916 ,p_attribute16 in varchar2 default null
1917 ,p_attribute17 in varchar2 default null
1918 ,p_attribute18 in varchar2 default null
1919 ,p_attribute19 in varchar2 default null
1920 ,p_attribute20 in varchar2 default null
1921 ,p_pay_proposal_ovn in number default null
1922 ,p_approved in varchar2 default null
1923 ,p_multiple_components in varchar2 default null
1924 ,p_forced_ranking in number default null
1925 ,p_performance_review_id in number default null
1926 ,p_proposed_salary_n in number default null
1927 ,p_comments in long default null
1928 --
1929 ,p_is_run_reqd out nocopy varchar2
1930 ,p_return_status out nocopy varchar2
1931 ) is
1932 --
1933 l_proc varchar2(72) := g_package ||'is_run_benmgle_for_irec_reqd';
1934 l_offer_assignment_record per_all_assignments_f%rowtype;
1935 l_pay_proposal_record per_pay_proposals%rowtype;
1936 --
1937 Begin
1938 hr_utility.set_location(' Entering:' || l_proc,10);
1939 --
1940 -- Issue a savepoint
1941 --
1942 savepoint IS_RUN_BENMGLE_FOR_IREC_REQD;
1943 --
1944 -- Initialise Multiple Message Detection
1945 --
1946 hr_multi_message.enable_message_list;
1947 --
1948 -- Create an assignment record
1949 --
1950 l_offer_assignment_record.assignment_id := p_assignment_id;
1951 l_offer_assignment_record.effective_start_date := p_effective_start_date;
1952 l_offer_assignment_record.effective_end_date := p_effective_end_date;
1953 l_offer_assignment_record.business_group_id := p_business_group_id;
1954 l_offer_assignment_record.recruiter_id := p_recruiter_id;
1955 l_offer_assignment_record.grade_id := p_grade_id;
1956 l_offer_assignment_record.position_id := p_position_id;
1957 l_offer_assignment_record.job_id := p_job_id;
1958 l_offer_assignment_record.assignment_status_type_id := p_assignment_status_type_id;
1959 l_offer_assignment_record.payroll_id := p_payroll_id;
1960 l_offer_assignment_record.location_id := p_location_id;
1961 l_offer_assignment_record.person_referred_by_id := p_person_referred_by_id;
1962 l_offer_assignment_record.supervisor_id := p_supervisor_id;
1963 l_offer_assignment_record.special_ceiling_step_id := p_special_ceiling_step_id;
1964 l_offer_assignment_record.person_id := p_person_id;
1965 l_offer_assignment_record.recruitment_activity_id := p_recruitment_activity_id;
1966 l_offer_assignment_record.source_organization_id := p_source_organization_id;
1967 l_offer_assignment_record.organization_id := p_organization_id;
1968 l_offer_assignment_record.people_group_id := p_people_group_id;
1969 l_offer_assignment_record.soft_coding_keyflex_id := p_soft_coding_keyflex_id;
1970 l_offer_assignment_record.vacancy_id := p_vacancy_id;
1971 l_offer_assignment_record.pay_basis_id := p_pay_basis_id;
1972 l_offer_assignment_record.assignment_sequence := p_assignment_sequence;
1973 l_offer_assignment_record.assignment_type := p_assignment_type;
1974 l_offer_assignment_record.primary_flag := p_primary_flag;
1975 l_offer_assignment_record.application_id := p_application_id;
1976 l_offer_assignment_record.assignment_number := p_assignment_number;
1977 l_offer_assignment_record.change_reason := p_change_reason;
1978 l_offer_assignment_record.comment_id := p_comment_id;
1979 l_offer_assignment_record.date_probation_end := p_date_probation_end;
1980 l_offer_assignment_record.default_code_comb_id := p_default_code_comb_id;
1981 l_offer_assignment_record.employment_category := p_employment_category;
1982 l_offer_assignment_record.frequency := p_frequency;
1983 l_offer_assignment_record.internal_address_line := p_internal_address_line;
1984 l_offer_assignment_record.manager_flag := p_manager_flag;
1985 l_offer_assignment_record.normal_hours := p_normal_hours;
1986 l_offer_assignment_record.perf_review_period := p_perf_review_period;
1987 l_offer_assignment_record.perf_review_period_frequency:= p_perf_review_period_frequency;
1988 l_offer_assignment_record.period_of_service_id := p_period_of_service_id;
1989 l_offer_assignment_record.probation_period := p_probation_period;
1990 l_offer_assignment_record.probation_unit := p_probation_unit;
1991 l_offer_assignment_record.sal_review_period := p_sal_review_period;
1992 l_offer_assignment_record.sal_review_period_frequency := p_sal_review_period_frequency;
1993 l_offer_assignment_record.set_of_books_id := p_set_of_books_id;
1994 l_offer_assignment_record.source_type := p_source_type;
1995 l_offer_assignment_record.time_normal_finish := p_time_normal_finish;
1996 l_offer_assignment_record.time_normal_start := p_time_normal_start;
1997 l_offer_assignment_record.bargaining_unit_code := p_bargaining_unit_code;
1998 l_offer_assignment_record.labour_union_member_flag := p_labour_union_member_flag;
1999 l_offer_assignment_record.hourly_salaried_code := p_hourly_salaried_code;
2000 l_offer_assignment_record.request_id := p_request_id;
2001 l_offer_assignment_record.program_application_id := p_program_application_id;
2002 l_offer_assignment_record.program_id := p_program_id;
2003 l_offer_assignment_record.program_update_date := p_program_update_date;
2004 l_offer_assignment_record.ass_attribute_category := p_ass_attribute_category;
2005 l_offer_assignment_record.ass_attribute1 := p_ass_attribute1;
2006 l_offer_assignment_record.ass_attribute2 := p_ass_attribute2;
2007 l_offer_assignment_record.ass_attribute3 := p_ass_attribute3;
2008 l_offer_assignment_record.ass_attribute4 := p_ass_attribute4;
2009 l_offer_assignment_record.ass_attribute5 := p_ass_attribute5;
2010 l_offer_assignment_record.ass_attribute6 := p_ass_attribute6;
2011 l_offer_assignment_record.ass_attribute7 := p_ass_attribute7;
2012 l_offer_assignment_record.ass_attribute8 := p_ass_attribute8;
2013 l_offer_assignment_record.ass_attribute9 := p_ass_attribute9;
2014 l_offer_assignment_record.ass_attribute10 := p_ass_attribute10;
2015 l_offer_assignment_record.ass_attribute11 := p_ass_attribute11;
2016 l_offer_assignment_record.ass_attribute12 := p_ass_attribute12;
2017 l_offer_assignment_record.ass_attribute13 := p_ass_attribute13;
2018 l_offer_assignment_record.ass_attribute14 := p_ass_attribute14;
2019 l_offer_assignment_record.ass_attribute15 := p_ass_attribute15;
2020 l_offer_assignment_record.ass_attribute16 := p_ass_attribute16;
2021 l_offer_assignment_record.ass_attribute17 := p_ass_attribute17;
2022 l_offer_assignment_record.ass_attribute18 := p_ass_attribute18;
2023 l_offer_assignment_record.ass_attribute19 := p_ass_attribute19;
2024 l_offer_assignment_record.ass_attribute20 := p_ass_attribute20;
2025 l_offer_assignment_record.ass_attribute21 := p_ass_attribute21;
2026 l_offer_assignment_record.ass_attribute22 := p_ass_attribute22;
2027 l_offer_assignment_record.ass_attribute23 := p_ass_attribute23;
2028 l_offer_assignment_record.ass_attribute24 := p_ass_attribute24;
2029 l_offer_assignment_record.ass_attribute25 := p_ass_attribute25;
2030 l_offer_assignment_record.ass_attribute26 := p_ass_attribute26;
2031 l_offer_assignment_record.ass_attribute27 := p_ass_attribute27;
2032 l_offer_assignment_record.ass_attribute28 := p_ass_attribute28;
2033 l_offer_assignment_record.ass_attribute29 := p_ass_attribute29;
2034 l_offer_assignment_record.ass_attribute30 := p_ass_attribute30;
2035 l_offer_assignment_record.title := p_title;
2036 l_offer_assignment_record.object_version_number := p_object_version_number;
2037 l_offer_assignment_record.contract_id := p_contract_id;
2038 l_offer_assignment_record.establishment_id := p_establishment_id;
2039 l_offer_assignment_record.collective_agreement_id := p_collective_agreement_id;
2040 l_offer_assignment_record.cagr_grade_def_id := p_cagr_grade_def_id;
2041 l_offer_assignment_record.cagr_id_flex_num := p_cagr_id_flex_num;
2042 l_offer_assignment_record.notice_period := p_notice_period;
2043 l_offer_assignment_record.notice_period_uom := p_notice_period_uom;
2044 l_offer_assignment_record.employee_category := p_employee_category;
2045 l_offer_assignment_record.work_at_home := p_work_at_home;
2046 l_offer_assignment_record.job_post_source_name := p_job_post_source_name;
2047 l_offer_assignment_record.posting_content_id := p_posting_content_id;
2048 l_offer_assignment_record.period_of_placement_date_start := p_placement_date_start;
2049 l_offer_assignment_record.vendor_id := p_vendor_id;
2050 l_offer_assignment_record.vendor_employee_number := p_vendor_employee_number;
2051 l_offer_assignment_record.vendor_assignment_number := p_vendor_assignment_number;
2052 l_offer_assignment_record.assignment_category := p_assignment_category;
2053 l_offer_assignment_record.project_title := p_project_title;
2054 l_offer_assignment_record.applicant_rank := p_applicant_rank;
2055 l_offer_assignment_record.grade_ladder_pgm_id := p_grade_ladder_pgm_id;
2056 l_offer_assignment_record.supervisor_assignment_id := p_supervisor_assignment_id;
2057 l_offer_assignment_record.vendor_site_id := p_vendor_site_id;
2058 l_offer_assignment_record.po_header_id := p_po_header_id;
2059 l_offer_assignment_record.po_line_id := p_po_line_id;
2060 l_offer_assignment_record.projected_assignment_end := p_projected_assignment_end;
2061 --
2062 -- Create a Pay Proposal Record
2063 --
2064 l_pay_proposal_record.pay_proposal_id := p_pay_proposal_id;
2065 l_pay_proposal_record.assignment_id := p_assignment_id;
2066 l_pay_proposal_record.event_id := p_event_id;
2067 l_pay_proposal_record.business_group_id := p_business_group_id;
2068 l_pay_proposal_record.change_date := p_change_date;
2069 l_pay_proposal_record.last_change_date := p_last_change_date;
2070 l_pay_proposal_record.next_perf_review_date := p_next_perf_review_date;
2071 l_pay_proposal_record.next_sal_review_date := p_next_sal_review_date;
2072 l_pay_proposal_record.performance_rating := p_performance_rating;
2073 l_pay_proposal_record.proposal_reason := p_proposal_reason;
2074 l_pay_proposal_record.proposed_salary := p_proposed_salary;
2075 l_pay_proposal_record.review_date := p_review_date;
2076 l_pay_proposal_record.attribute_category := p_attribute_category;
2077 l_pay_proposal_record.attribute1 := p_attribute1;
2078 l_pay_proposal_record.attribute2 := p_attribute2;
2079 l_pay_proposal_record.attribute3 := p_attribute3;
2080 l_pay_proposal_record.attribute4 := p_attribute4;
2081 l_pay_proposal_record.attribute5 := p_attribute5;
2082 l_pay_proposal_record.attribute6 := p_attribute6;
2083 l_pay_proposal_record.attribute7 := p_attribute7;
2084 l_pay_proposal_record.attribute8 := p_attribute8;
2085 l_pay_proposal_record.attribute9 := p_attribute9;
2086 l_pay_proposal_record.attribute10 := p_attribute10;
2087 l_pay_proposal_record.attribute11 := p_attribute11;
2088 l_pay_proposal_record.attribute12 := p_attribute12;
2089 l_pay_proposal_record.attribute13 := p_attribute13;
2090 l_pay_proposal_record.attribute14 := p_attribute14;
2091 l_pay_proposal_record.attribute15 := p_attribute15;
2092 l_pay_proposal_record.attribute16 := p_attribute16;
2093 l_pay_proposal_record.attribute17 := p_attribute17;
2094 l_pay_proposal_record.attribute18 := p_attribute18;
2095 l_pay_proposal_record.attribute19 := p_attribute19;
2096 l_pay_proposal_record.attribute20 := p_attribute20;
2097 l_pay_proposal_record.object_version_number := p_pay_proposal_ovn;
2098 l_pay_proposal_record.approved := p_approved;
2099 l_pay_proposal_record.multiple_components := p_multiple_components;
2100 l_pay_proposal_record.forced_ranking := p_forced_ranking;
2101 l_pay_proposal_record.performance_review_id := p_performance_review_id;
2102 l_pay_proposal_record.proposed_salary_n := p_proposed_salary_n;
2103 l_pay_proposal_record.comments := p_comments;
2104 --
2105 if(g_old_offer_assignment_record.assignment_id <> p_assignment_id)
2106 then
2107 g_old_offer_assignment_record := null;
2108 end if;
2109
2110 if(g_old_pay_proposal_record.assignment_id <> p_assignment_id)
2111 then
2112 g_old_pay_proposal_record := null;
2113 end if;
2114 --
2115 p_is_run_reqd :=
2116 ben_irc_util.is_benmngle_for_irec_reqd
2117 ( p_person_id => p_person_id
2118 ,p_assignment_id => p_assignment_id
2119 ,p_business_group_id => p_business_group_id
2120 ,p_effective_date => p_effective_date
2121 ,p_pay_proposal_rec_old => g_old_pay_proposal_record
2122 ,p_pay_proposal_rec_new => l_pay_proposal_record
2123 ,p_offer_assignment_rec_old => g_old_offer_assignment_record
2124 ,p_offer_assignment_rec_new => l_offer_assignment_record
2125 );
2126 --
2127 -- Set the old record values to the current ones
2128 --
2129 g_old_offer_assignment_record := l_offer_assignment_record;
2130 g_old_pay_proposal_record := l_pay_proposal_record;
2131 --
2132 -- Derive the API return status value based on whether
2133 -- messages of any type exist in the Multiple Message List.
2134 -- Also disable Multiple Message Detection.
2135 --
2136 p_return_status := hr_multi_message.get_return_status_disable;
2137 hr_utility.set_location(' Leaving:' || l_proc,20);
2138 --
2139 exception
2140 when hr_multi_message.error_message_exist then
2141 --
2142 -- Catch the Multiple Message List exception which
2143 -- indicates API processing has been aborted because
2144 -- at least one message exists in the list.
2145 --
2146 rollback to IS_RUN_BENMGLE_FOR_IREC_REQD;
2147 --
2148 p_return_status := hr_multi_message.get_return_status_disable;
2149 p_is_run_reqd := 'N';
2150 --
2151 hr_utility.set_location(' Leaving:' || l_proc, 30);
2152 when others then
2153 --
2154 -- When Multiple Message Detection is enabled catch
2155 -- any Application specific or other unexpected
2156 -- exceptions. Adding appropriate details to the
2157 -- Multiple Message List. Otherwise re-raise the
2158 -- error.
2159 --
2160 rollback to IS_RUN_BENMGLE_FOR_IREC_REQD;
2161 --
2162 if hr_multi_message.unexpected_error_add(l_proc) then
2163 hr_utility.set_location(' Leaving:' || l_proc,40);
2164 raise;
2165 end if;
2166 --
2167 p_return_status := hr_multi_message.get_return_status_disable;
2168 p_is_run_reqd := 'N';
2169 --
2170 hr_utility.set_location(' Leaving:' || l_proc,50);
2171 --
2172 end is_run_benmgle_for_irec_reqd;
2173 -- ----------------------------------------------------------------------------
2174 -- |-------------------------< run_benmgle_for_irec >-------------------------|
2175 -- ----------------------------------------------------------------------------
2176 PROCEDURE run_benmgle_for_irec
2177 (p_assignment_id in number
2178 ,p_effective_start_date in date default trunc(sysdate)
2179 ,p_effective_end_date in date default hr_api.g_eot
2180 ,p_business_group_id in number
2181 ,p_recruiter_id in number default null
2182 ,p_grade_id in number default null
2183 ,p_position_id in number default null
2184 ,p_job_id in number default null
2185 ,p_assignment_status_type_id in number
2186 ,p_payroll_id in number default null
2187 ,p_location_id in number default null
2188 ,p_person_referred_by_id in number default null
2189 ,p_supervisor_id in number default null
2190 ,p_special_ceiling_step_id in number default null
2191 ,p_person_id in number
2192 ,p_recruitment_activity_id in number default null
2193 ,p_source_organization_id in number default null
2194 ,p_organization_id in number
2195 ,p_people_group_id in number default null
2196 ,p_soft_coding_keyflex_id in number default null
2197 ,p_vacancy_id in number default null
2198 ,p_pay_basis_id in number default null
2199 ,p_assignment_sequence in number default 1
2200 ,p_assignment_type in varchar2
2201 ,p_primary_flag in varchar2
2202 ,p_application_id in number default null
2203 ,p_assignment_number in varchar2 default null
2204 ,p_change_reason in varchar2 default null
2205 ,p_comment_id in number default null
2206 ,p_date_probation_end in date default null
2207 ,p_default_code_comb_id in number default null
2208 ,p_employment_category in varchar2 default null
2209 ,p_frequency in varchar2 default null
2210 ,p_internal_address_line in varchar2 default null
2211 ,p_manager_flag in varchar2 default null
2212 ,p_normal_hours in number default null
2213 ,p_perf_review_period in number default null
2214 ,p_perf_review_period_frequency in varchar2 default null
2215 ,p_period_of_service_id in number default null
2216 ,p_probation_period in number default null
2217 ,p_probation_unit in varchar2 default null
2218 ,p_sal_review_period in number default null
2219 ,p_sal_review_period_frequency in varchar2 default null
2220 ,p_set_of_books_id in number default null
2221 ,p_source_type in varchar2 default null
2222 ,p_time_normal_finish in varchar2 default null
2223 ,p_time_normal_start in varchar2 default null
2224 ,p_bargaining_unit_code in varchar2 default null
2225 ,p_labour_union_member_flag in varchar2 default null
2226 ,p_hourly_salaried_code in varchar2 default null
2227 ,p_request_id in number default null
2228 ,p_program_application_id in number default null
2229 ,p_program_id in number default null
2230 ,p_program_update_date in date default null
2231 ,p_ass_attribute_category in varchar2 default null
2232 ,p_ass_attribute1 in varchar2 default null
2233 ,p_ass_attribute2 in varchar2 default null
2234 ,p_ass_attribute3 in varchar2 default null
2235 ,p_ass_attribute4 in varchar2 default null
2236 ,p_ass_attribute5 in varchar2 default null
2237 ,p_ass_attribute6 in varchar2 default null
2238 ,p_ass_attribute7 in varchar2 default null
2239 ,p_ass_attribute8 in varchar2 default null
2240 ,p_ass_attribute9 in varchar2 default null
2241 ,p_ass_attribute10 in varchar2 default null
2242 ,p_ass_attribute11 in varchar2 default null
2243 ,p_ass_attribute12 in varchar2 default null
2244 ,p_ass_attribute13 in varchar2 default null
2245 ,p_ass_attribute14 in varchar2 default null
2246 ,p_ass_attribute15 in varchar2 default null
2247 ,p_ass_attribute16 in varchar2 default null
2248 ,p_ass_attribute17 in varchar2 default null
2249 ,p_ass_attribute18 in varchar2 default null
2250 ,p_ass_attribute19 in varchar2 default null
2251 ,p_ass_attribute20 in varchar2 default null
2252 ,p_ass_attribute21 in varchar2 default null
2253 ,p_ass_attribute22 in varchar2 default null
2254 ,p_ass_attribute23 in varchar2 default null
2255 ,p_ass_attribute24 in varchar2 default null
2256 ,p_ass_attribute25 in varchar2 default null
2257 ,p_ass_attribute26 in varchar2 default null
2258 ,p_ass_attribute27 in varchar2 default null
2259 ,p_ass_attribute28 in varchar2 default null
2260 ,p_ass_attribute29 in varchar2 default null
2261 ,p_ass_attribute30 in varchar2 default null
2262 ,p_title in varchar2 default null
2263 ,p_object_version_number in number default 1
2264 ,p_contract_id in number default null
2265 ,p_establishment_id in number default null
2266 ,p_collective_agreement_id in number default null
2267 ,p_cagr_grade_def_id in number default null
2268 ,p_cagr_id_flex_num in number default null
2269 ,p_notice_period in number default null
2270 ,p_notice_period_uom in varchar2 default null
2271 ,p_employee_category in varchar2 default null
2272 ,p_work_at_home in varchar2 default null
2273 ,p_job_post_source_name in varchar2 default null
2274 ,p_posting_content_id in number default null
2275 ,p_placement_date_start in date default null
2276 ,p_vendor_id in number default null
2277 ,p_vendor_employee_number in varchar2 default null
2278 ,p_vendor_assignment_number in varchar2 default null
2279 ,p_assignment_category in varchar2 default null
2280 ,p_project_title in varchar2 default null
2281 ,p_applicant_rank in number default null
2282 ,p_grade_ladder_pgm_id in number default null
2283 ,p_supervisor_assignment_id in number default null
2284 ,p_vendor_site_id in number default null
2285 ,p_po_header_id in number default null
2286 ,p_po_line_id in number default null
2287 ,p_projected_assignment_end in date default null
2288 ,p_effective_date in date
2289 ,p_return_status out nocopy varchar2
2290 ) is
2291 --
2292 l_proc varchar2(72) := g_package ||'run_benmgle_for_irec';
2293 l_offer_assignment_record per_all_assignments_f%rowtype;
2294 --
2295 Begin
2296 hr_utility.set_location(' Entering:' || l_proc,10);
2297 --
2298 -- Issue a savepoint
2299 --
2300 savepoint run_benmgle_for_irec;
2301 --
2302 -- Initialise Multiple Message Detection
2303 --
2304 hr_multi_message.enable_message_list;
2305 --
2306 -- Create an assignment record
2307 --
2308 l_offer_assignment_record.assignment_id := p_assignment_id;
2309 l_offer_assignment_record.effective_start_date := p_effective_start_date;
2310 l_offer_assignment_record.effective_end_date := p_effective_end_date;
2311 l_offer_assignment_record.business_group_id := p_business_group_id;
2312 l_offer_assignment_record.recruiter_id := p_recruiter_id;
2313 l_offer_assignment_record.grade_id := p_grade_id;
2314 l_offer_assignment_record.position_id := p_position_id;
2315 l_offer_assignment_record.job_id := p_job_id;
2316 l_offer_assignment_record.assignment_status_type_id := p_assignment_status_type_id;
2317 l_offer_assignment_record.payroll_id := p_payroll_id;
2318 l_offer_assignment_record.location_id := p_location_id;
2319 l_offer_assignment_record.person_referred_by_id := p_person_referred_by_id;
2320 l_offer_assignment_record.supervisor_id := p_supervisor_id;
2321 l_offer_assignment_record.special_ceiling_step_id := p_special_ceiling_step_id;
2322 l_offer_assignment_record.person_id := p_person_id;
2323 l_offer_assignment_record.recruitment_activity_id := p_recruitment_activity_id;
2324 l_offer_assignment_record.source_organization_id := p_source_organization_id;
2325 l_offer_assignment_record.organization_id := p_organization_id;
2326 l_offer_assignment_record.people_group_id := p_people_group_id;
2327 l_offer_assignment_record.soft_coding_keyflex_id := p_soft_coding_keyflex_id;
2328 l_offer_assignment_record.vacancy_id := p_vacancy_id;
2329 l_offer_assignment_record.pay_basis_id := p_pay_basis_id;
2330 l_offer_assignment_record.assignment_sequence := p_assignment_sequence;
2331 l_offer_assignment_record.assignment_type := p_assignment_type;
2332 l_offer_assignment_record.primary_flag := p_primary_flag;
2333 l_offer_assignment_record.application_id := p_application_id;
2334 l_offer_assignment_record.assignment_number := p_assignment_number;
2335 l_offer_assignment_record.change_reason := p_change_reason;
2336 l_offer_assignment_record.comment_id := p_comment_id;
2337 l_offer_assignment_record.date_probation_end := p_date_probation_end;
2338 l_offer_assignment_record.default_code_comb_id := p_default_code_comb_id;
2339 l_offer_assignment_record.employment_category := p_employment_category;
2340 l_offer_assignment_record.frequency := p_frequency;
2341 l_offer_assignment_record.internal_address_line := p_internal_address_line;
2342 l_offer_assignment_record.manager_flag := p_manager_flag;
2343 l_offer_assignment_record.normal_hours := p_normal_hours;
2344 l_offer_assignment_record.perf_review_period := p_perf_review_period;
2345 l_offer_assignment_record.perf_review_period_frequency:= p_perf_review_period_frequency;
2346 l_offer_assignment_record.period_of_service_id := p_period_of_service_id;
2347 l_offer_assignment_record.probation_period := p_probation_period;
2348 l_offer_assignment_record.probation_unit := p_probation_unit;
2349 l_offer_assignment_record.sal_review_period := p_sal_review_period;
2350 l_offer_assignment_record.sal_review_period_frequency := p_sal_review_period_frequency;
2351 l_offer_assignment_record.set_of_books_id := p_set_of_books_id;
2352 l_offer_assignment_record.source_type := p_source_type;
2353 l_offer_assignment_record.time_normal_finish := p_time_normal_finish;
2354 l_offer_assignment_record.time_normal_start := p_time_normal_start;
2355 l_offer_assignment_record.bargaining_unit_code := p_bargaining_unit_code;
2356 l_offer_assignment_record.labour_union_member_flag := p_labour_union_member_flag;
2357 l_offer_assignment_record.hourly_salaried_code := p_hourly_salaried_code;
2358 l_offer_assignment_record.request_id := p_request_id;
2359 l_offer_assignment_record.program_application_id := p_program_application_id;
2360 l_offer_assignment_record.program_id := p_program_id;
2361 l_offer_assignment_record.program_update_date := p_program_update_date;
2362 l_offer_assignment_record.ass_attribute_category := p_ass_attribute_category;
2363 l_offer_assignment_record.ass_attribute1 := p_ass_attribute1;
2364 l_offer_assignment_record.ass_attribute2 := p_ass_attribute2;
2365 l_offer_assignment_record.ass_attribute3 := p_ass_attribute3;
2366 l_offer_assignment_record.ass_attribute4 := p_ass_attribute4;
2367 l_offer_assignment_record.ass_attribute5 := p_ass_attribute5;
2368 l_offer_assignment_record.ass_attribute6 := p_ass_attribute6;
2369 l_offer_assignment_record.ass_attribute7 := p_ass_attribute7;
2370 l_offer_assignment_record.ass_attribute8 := p_ass_attribute8;
2371 l_offer_assignment_record.ass_attribute9 := p_ass_attribute9;
2372 l_offer_assignment_record.ass_attribute10 := p_ass_attribute10;
2373 l_offer_assignment_record.ass_attribute11 := p_ass_attribute11;
2374 l_offer_assignment_record.ass_attribute12 := p_ass_attribute12;
2375 l_offer_assignment_record.ass_attribute13 := p_ass_attribute13;
2376 l_offer_assignment_record.ass_attribute14 := p_ass_attribute14;
2377 l_offer_assignment_record.ass_attribute15 := p_ass_attribute15;
2378 l_offer_assignment_record.ass_attribute16 := p_ass_attribute16;
2379 l_offer_assignment_record.ass_attribute17 := p_ass_attribute17;
2380 l_offer_assignment_record.ass_attribute18 := p_ass_attribute18;
2381 l_offer_assignment_record.ass_attribute19 := p_ass_attribute19;
2382 l_offer_assignment_record.ass_attribute20 := p_ass_attribute20;
2383 l_offer_assignment_record.ass_attribute21 := p_ass_attribute21;
2384 l_offer_assignment_record.ass_attribute22 := p_ass_attribute22;
2385 l_offer_assignment_record.ass_attribute23 := p_ass_attribute23;
2386 l_offer_assignment_record.ass_attribute24 := p_ass_attribute24;
2387 l_offer_assignment_record.ass_attribute25 := p_ass_attribute25;
2388 l_offer_assignment_record.ass_attribute26 := p_ass_attribute26;
2389 l_offer_assignment_record.ass_attribute27 := p_ass_attribute27;
2390 l_offer_assignment_record.ass_attribute28 := p_ass_attribute28;
2391 l_offer_assignment_record.ass_attribute29 := p_ass_attribute29;
2392 l_offer_assignment_record.ass_attribute30 := p_ass_attribute30;
2393 l_offer_assignment_record.title := p_title;
2394 l_offer_assignment_record.object_version_number := p_object_version_number;
2395 l_offer_assignment_record.contract_id := p_contract_id;
2396 l_offer_assignment_record.establishment_id := p_establishment_id;
2397 l_offer_assignment_record.collective_agreement_id := p_collective_agreement_id;
2398 l_offer_assignment_record.cagr_grade_def_id := p_cagr_grade_def_id;
2399 l_offer_assignment_record.cagr_id_flex_num := p_cagr_id_flex_num;
2400 l_offer_assignment_record.notice_period := p_notice_period;
2401 l_offer_assignment_record.notice_period_uom := p_notice_period_uom;
2402 l_offer_assignment_record.employee_category := p_employee_category;
2403 l_offer_assignment_record.work_at_home := p_work_at_home;
2404 l_offer_assignment_record.job_post_source_name := p_job_post_source_name;
2405 l_offer_assignment_record.posting_content_id := p_posting_content_id;
2406 l_offer_assignment_record.period_of_placement_date_start := p_placement_date_start;
2407 l_offer_assignment_record.vendor_id := p_vendor_id;
2408 l_offer_assignment_record.vendor_employee_number := p_vendor_employee_number;
2409 l_offer_assignment_record.vendor_assignment_number := p_vendor_assignment_number;
2410 l_offer_assignment_record.assignment_category := p_assignment_category;
2411 l_offer_assignment_record.project_title := p_project_title;
2412 l_offer_assignment_record.applicant_rank := p_applicant_rank;
2413 l_offer_assignment_record.grade_ladder_pgm_id := p_grade_ladder_pgm_id;
2414 l_offer_assignment_record.supervisor_assignment_id := p_supervisor_assignment_id;
2415 l_offer_assignment_record.vendor_site_id := p_vendor_site_id;
2416 l_offer_assignment_record.po_header_id := p_po_header_id;
2417 l_offer_assignment_record.po_line_id := p_po_line_id;
2418 l_offer_assignment_record.projected_assignment_end := p_projected_assignment_end;
2419 --
2420 -- Call The benifits wrapper for iRec
2421 --
2422 ben_on_line_lf_evt.p_manage_irec_life_events_w
2423 (
2424 p_person_id => p_person_id
2425 ,p_assignment_id => p_assignment_id
2426 ,p_effective_date => p_effective_date
2427 ,p_business_group_id => p_business_group_id
2428 ,p_offer_assignment_rec => l_offer_assignment_record
2429 );
2430 --
2431 -- Derive the API return status value based on whether
2432 -- messages of any type exist in the Multiple Message List.
2433 -- Also disable Multiple Message Detection.
2434 --
2435 p_return_status := hr_multi_message.get_return_status_disable;
2436 hr_utility.set_location(' Leaving:' || l_proc,20);
2437 --
2438 exception
2439 when hr_multi_message.error_message_exist then
2440 --
2441 -- Catch the Multiple Message List exception which
2442 -- indicates API processing has been aborted because
2443 -- at least one message exists in the list.
2444 --
2445 rollback to run_benmgle_for_irec;
2446 --
2447 p_return_status := hr_multi_message.get_return_status_disable;
2448 hr_utility.set_location(' Leaving:' || l_proc, 30);
2449 when others then
2450 --
2451 -- When Multiple Message Detection is enabled catch
2452 -- any Application specific or other unexpected
2453 -- exceptions. Adding appropriate details to the
2454 -- Multiple Message List. Otherwise re-raise the
2455 -- error.
2456 --
2457 rollback to run_benmgle_for_irec;
2458 if hr_multi_message.unexpected_error_add(l_proc) then
2459 hr_utility.set_location(' Leaving:' || l_proc,40);
2460 raise;
2461 end if;
2462 --
2463 p_return_status := hr_multi_message.get_return_status_disable;
2464 hr_utility.set_location(' Leaving:' || l_proc,50);
2465 --
2466 end run_benmgle_for_irec;
2467 --
2468 --Save For Later Code Changes
2469 -- ----------------------------------------------------------------------------
2470 -- |-------------------------< process_offers_api >---------------------------|
2471 -- ----------------------------------------------------------------------------
2472
2473 procedure process_offers_api
2474 (
2475 p_document in CLOB
2476 ,p_return_status out nocopy VARCHAR2
2477 ,p_validate in number default hr_api.g_false_num
2478 ,p_effective_date in date default null
2479 )
2480 IS
2481 l_postState VARCHAR2(2);
2482 l_return_status VARCHAR2(1);
2483 l_object_version_number number;
2484 l_commitElement xmldom.DOMElement;
2485 l_parser xmlparser.Parser;
2486 l_CommitNode xmldom.DOMNode;
2487 l_proc varchar2(72) := g_package || 'process_offers_api';
2488
2489 -- Variables for OUT parameters
2490 l_offer_id number;
2491 l_offer_version number;
2492 l_offer_status varchar(10);
2493 l_effective_date date := trunc(sysdate);
2494 --
2495 cursor current_offer_ovn(p_offer_id NUMBER) is
2496 select object_version_number
2497 from irc_offers
2498 where offer_id = p_offer_id;
2499 --
2500
2501 BEGIN
2502 --
2503 hr_utility.set_location(' Entering:' || l_proc,10);
2504 hr_utility.set_location(' CLOB --> xmldom.DOMNode:' || l_proc,15);
2505 --
2506 l_parser := xmlparser.newParser;
2507 xmlparser.ParseCLOB(l_parser,p_document);
2508 l_CommitNode := xmldom.makeNode(xmldom.getDocumentElement(xmlparser.getDocument(l_parser)));
2509 --
2510 hr_utility.set_location('Extracting the PostState:' || l_proc,20);
2511
2512 l_commitElement := xmldom.makeElement(l_CommitNode);
2513 l_postState := xmldom.getAttribute(l_commitElement, 'PS');
2514 --
2515 --Get the values for in/out parameters
2516 --
2517 l_offer_id := hr_transaction_swi.getNumberValue(l_CommitNode,'OfferId');
2518 --
2519 open current_offer_ovn(l_offer_id);
2520 fetch current_offer_ovn into l_object_version_number;
2521 close current_offer_ovn;
2522 --
2523 l_offer_version := hr_transaction_swi.getNumberValue(l_CommitNode,'OfferVersion');
2524 l_offer_status := hr_transaction_swi.getVarchar2Value(l_CommitNode,'OfferStatus');
2525 --
2526 if p_effective_date is null
2527 then
2528 --
2529 l_effective_date := trunc(sysdate);
2530 --
2531 else
2532 --
2533 l_effective_date := p_effective_date;
2534 --
2535 end if;
2536 --
2537 if l_postState = '2' then
2538 -- call update offer
2539 --
2540 update_offer
2541 (p_validate => p_validate
2542 ,p_effective_date => l_effective_date
2543 ,p_offer_status => hr_transaction_swi.getVarchar2Value(l_CommitNode,'OfferStatus')
2544 ,p_discretionary_job_title => hr_transaction_swi.getVarchar2Value(l_CommitNode,'DiscretionaryJobTitle')
2545 ,p_offer_extended_method => hr_transaction_swi.getVarchar2Value(l_CommitNode,'OfferExtendedMethod')
2546 ,p_respondent_id => hr_transaction_swi.getNumberValue(l_CommitNode,'RespondentId')
2547 ,p_expiry_date => hr_transaction_swi.getDateValue(l_CommitNode,'ExpiryDate')
2548 ,p_proposed_start_date => hr_transaction_swi.getDateValue(l_CommitNode,'ProposedStartDate')
2549 ,p_offer_letter_tracking_code => hr_transaction_swi.getVarchar2Value(l_CommitNode,'OfferLetterTrackingCode')
2550 ,p_offer_postal_service => hr_transaction_swi.getVarchar2Value(l_CommitNode,'OfferPostalService')
2551 ,p_offer_shipping_date => hr_transaction_swi.getDateValue(l_CommitNode,'OfferShippingDate')
2552 ,p_applicant_assignment_id => hr_transaction_swi.getNumberValue(l_CommitNode,'ApplicantAssignmentId')
2553 ,p_offer_assignment_id => hr_transaction_swi.getNumberValue(l_CommitNode,'OfferAssignmentId')
2554 ,p_address_id => hr_transaction_swi.getNumberValue(l_CommitNode,'AddressId')
2555 ,p_template_id => hr_transaction_swi.getNumberValue(l_CommitNode,'TemplateId')
2556 ,p_offer_letter_file_type => hr_transaction_swi.getVarchar2Value(l_CommitNode,'OfferLetterFileType')
2557 ,p_offer_letter_file_name => hr_transaction_swi.getVarchar2Value(l_CommitNode,'OfferLetterFileName')
2558 ,p_attribute_category => hr_transaction_swi.getVarchar2Value(l_CommitNode,'AttributeCategory')
2559 ,p_attribute1 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute1')
2560 ,p_attribute2 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute2')
2561 ,p_attribute3 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute3')
2562 ,p_attribute4 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute4')
2563 ,p_attribute5 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute5')
2564 ,p_attribute6 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute6')
2565 ,p_attribute7 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute7')
2566 ,p_attribute8 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute8')
2567 ,p_attribute9 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute9')
2568 ,p_attribute10 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute10')
2569 ,p_attribute11 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute11')
2570 ,p_attribute12 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute12')
2571 ,p_attribute13 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute13')
2572 ,p_attribute14 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute14')
2573 ,p_attribute15 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute15')
2574 ,p_attribute16 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute16')
2575 ,p_attribute17 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute17')
2576 ,p_attribute18 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute18')
2577 ,p_attribute19 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute19')
2578 ,p_attribute20 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute20')
2579 ,p_attribute21 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute21')
2580 ,p_attribute22 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute22')
2581 ,p_attribute23 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute23')
2582 ,p_attribute24 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute24')
2583 ,p_attribute25 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute25')
2584 ,p_attribute26 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute26')
2585 ,p_attribute27 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute27')
2586 ,p_attribute28 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute28')
2587 ,p_attribute29 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute29')
2588 ,p_attribute30 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute30')
2589 ,p_change_reason => hr_transaction_swi.getVarchar2Value(l_CommitNode,'ChangeReason',null)
2590 ,p_decline_reason => hr_transaction_swi.getVarchar2Value(l_CommitNode,'DeclineReason',null)
2591 ,p_note_text => hr_transaction_swi.getVarchar2Value(l_CommitNode,'NoteText',null)
2592 ,p_status_change_date => hr_transaction_swi.getDateValue(l_CommitNode,'StatusChangeDate',null)
2593 ,p_offer_id => l_offer_id
2594 ,p_object_version_number => l_object_version_number
2595 ,p_offer_version => l_offer_version
2596 ,p_return_status => l_return_status
2597 );
2598 --
2599 elsif l_postState = '3' then
2600 -- call delete offer
2601 --
2602 delete_offer
2603 (p_validate => p_validate
2604 ,p_object_version_number => l_object_version_number
2605 ,p_offer_id => l_offer_id
2606 ,p_effective_date => l_effective_date
2607 ,p_return_status => l_return_status
2608 );
2609 --
2610 end if;
2611 p_return_status := l_return_status;
2612 hr_utility.set_location('Exiting:' || l_proc,40);
2613
2614 end process_offers_api;
2615
2616 -- ----------------------------------------------------------------------------
2617 -- |-------------------------< process_asg_api >----------------------------|
2618 -- ----------------------------------------------------------------------------
2619
2620 procedure process_asg_api
2621 (
2622 p_document in CLOB
2623 ,p_return_status out nocopy VARCHAR2
2624 ,p_validate in number default hr_api.g_false_num
2625 ,p_effective_date in date default null
2626 )
2627 IS
2628 l_postState VARCHAR2(2);
2629 l_return_status VARCHAR2(1);
2630 l_commitElement xmldom.DOMElement;
2631 l_parser xmlparser.Parser;
2632 l_CommitNode xmldom.DOMNode;
2633 l_proc varchar2(72) := g_package || 'process_asg_api';
2634
2635 --
2636 l_assignment_id number;
2637 l_offer_id number;
2638 l_assignment_sequence number;
2639 l_comment_id number;
2640 l_assignment_number number;
2641 l_effective_start_date date;
2642 l_effective_end_date date;
2643 --
2644 l_asg_object_version_number number;
2645 l_business_group_id number;
2646 l_payroll_id_updated number;
2647 l_validation_start_date date;
2648 l_validation_end_date date;
2649 --
2650 l_effective_date date := trunc(sysdate);
2651
2652 BEGIN
2653
2654 hr_utility.set_location(' Entering:' || l_proc,10);
2655 hr_utility.set_location(' CLOB --> xmldom.DOMNode:' || l_proc,15);
2656
2657 l_parser := xmlparser.newParser;
2658 xmlparser.ParseCLOB(l_parser,p_document);
2659 l_CommitNode := xmldom.makeNode(xmldom.getDocumentElement(xmlparser.getDocument(l_parser)));
2660
2661 hr_utility.set_location('Extracting the PostState:' || l_proc,20);
2662
2663 l_commitElement := xmldom.makeElement(l_CommitNode);
2664 l_postState := xmldom.getAttribute(l_commitElement, 'PS');
2665
2666 --Get the values for in/out parameters
2667 l_assignment_id := hr_transaction_swi.getNumberValue(l_CommitNode,'AssignmentId');
2668 l_offer_id := hr_transaction_swi.getNumberValue(l_CommitNode,'OfferId');
2669 l_assignment_sequence := hr_transaction_swi.getNumberValue(l_CommitNode,'AssignmentSequence');
2670 l_assignment_number := hr_transaction_swi.getNumberValue(l_CommitNode,'AssignmentNumber', null);
2671 l_asg_object_version_number := hr_transaction_swi.getNumberValue(l_CommitNode,'ObjectVersionNumber');
2672
2673 if p_effective_date is null
2674 then
2675 --
2676 l_effective_date := trunc(sysdate);
2677 --
2678 else
2679 --
2680 l_effective_date := p_effective_date;
2681 --
2682 end if;
2683
2684 if l_postState = '2' then
2685 --
2686 -- call update offer
2687 --
2688 update_offer_assignment
2689 (p_assignment_id => l_assignment_id
2690 ,p_effective_start_date => l_effective_start_date
2691 ,p_effective_end_date => l_effective_end_date
2692 ,p_business_group_id => l_business_group_id
2693 ,p_recruiter_id => hr_transaction_swi.getNumberValue(l_CommitNode,'RecruiterId')
2694 ,p_grade_id => hr_transaction_swi.getNumberValue(l_CommitNode,'GradeId')
2695 ,p_position_id => hr_transaction_swi.getNumberValue(l_CommitNode,'PositionId')
2696 ,p_job_id => hr_transaction_swi.getNumberValue(l_CommitNode,'JobId')
2697 ,p_assignment_status_type_id => hr_transaction_swi.getNumberValue(l_CommitNode,'AssignmentStatusTypeId')
2698 ,p_payroll_id => hr_transaction_swi.getNumberValue(l_CommitNode,'PayrollId')
2699 ,p_location_id => hr_transaction_swi.getNumberValue(l_CommitNode,'LocationId')
2700 ,p_person_referred_by_id => hr_transaction_swi.getNumberValue(l_CommitNode,'PersonReferredById')
2701 ,p_supervisor_id => hr_transaction_swi.getNumberValue(l_CommitNode,'SupervisorId')
2702 ,p_special_ceiling_step_id => hr_transaction_swi.getNumberValue(l_CommitNode,'SpecialCeilingStepId')
2703 ,p_recruitment_activity_id => hr_transaction_swi.getNumberValue(l_CommitNode,'RecruitmentActivityId')
2704 ,p_source_organization_id => hr_transaction_swi.getNumberValue(l_CommitNode,'SourceOrganizationId')
2705 ,p_organization_id => hr_transaction_swi.getNumberValue(l_CommitNode,'OrganizationId')
2706 ,p_people_group_id => hr_transaction_swi.getNumberValue(l_CommitNode,'PeopleGroupId')
2707 ,p_soft_coding_keyflex_id => hr_transaction_swi.getNumberValue(l_CommitNode,'SoftCodingKeyflexId')
2708 ,p_vacancy_id => hr_transaction_swi.getNumberValue(l_CommitNode,'VacancyId')
2709 ,p_pay_basis_id => hr_transaction_swi.getNumberValue(l_CommitNode,'PayBasisId')
2710 ,p_assignment_type => hr_transaction_swi.getVarchar2Value(l_CommitNode,'AssignmentType')
2711 ,p_primary_flag => hr_transaction_swi.getVarchar2Value(l_CommitNode,'PrimaryFlag')
2712 ,p_application_id => hr_transaction_swi.getNumberValue(l_CommitNode,'ApplicationId')
2713 ,p_assignment_number => l_assignment_number
2714 ,p_change_reason => hr_transaction_swi.getVarchar2Value(l_CommitNode,'ChangeReason')
2715 ,p_comment_id => l_comment_id
2716 ,p_comments => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Comments')
2717 ,p_date_probation_end => hr_transaction_swi.getDateValue(l_CommitNode,'DateProbationEnd')
2718 ,p_default_code_comb_id => hr_transaction_swi.getNumberValue(l_CommitNode,'DefaultCodeCombId')
2719 ,p_employment_category => hr_transaction_swi.getVarchar2Value(l_CommitNode,'EmploymentCategory')
2720 ,p_frequency => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Frequency')
2721 ,p_internal_address_line => hr_transaction_swi.getVarchar2Value(l_CommitNode,'InternalAddressLine')
2722 ,p_manager_flag => hr_transaction_swi.getVarchar2Value(l_CommitNode,'ManagerFlag')
2723 ,p_normal_hours => hr_transaction_swi.getNumberValue(l_CommitNode,'NormalHours')
2724 ,p_perf_review_period => hr_transaction_swi.getNumberValue(l_CommitNode,'PerfReviewPeriod')
2725 ,p_perf_review_period_frequency => hr_transaction_swi.getVarchar2Value(l_CommitNode,'PerfReviewPeriodFrequency')
2726 ,p_period_of_service_id => hr_transaction_swi.getNumberValue(l_CommitNode,'PeriodOfServiceId')
2727 ,p_probation_period => hr_transaction_swi.getNumberValue(l_CommitNode,'ProbationPeriod')
2728 ,p_probation_unit => hr_transaction_swi.getVarchar2Value(l_CommitNode,'ProbationUnit')
2729 ,p_sal_review_period => hr_transaction_swi.getNumberValue(l_CommitNode,'SalReviewPeriod')
2730 ,p_sal_review_period_frequency => hr_transaction_swi.getVarchar2Value(l_CommitNode,'SalReviewPeriodFrequency')
2731 ,p_set_of_books_id => hr_transaction_swi.getNumberValue(l_CommitNode,'SetOfBooksId')
2732 ,p_source_type => hr_transaction_swi.getVarchar2Value(l_CommitNode,'SourceType')
2733 ,p_time_normal_finish => hr_transaction_swi.getVarchar2Value(l_CommitNode,'TimeNormalFinish')
2734 ,p_time_normal_start => hr_transaction_swi.getVarchar2Value(l_CommitNode,'TimeNormalStart')
2735 ,p_bargaining_unit_code => hr_transaction_swi.getVarchar2Value(l_CommitNode,'BargainingUnitCode')
2736 ,p_labour_union_member_flag => hr_transaction_swi.getVarchar2Value(l_CommitNode,'LabourUnionMemberFlag')
2737 ,p_hourly_salaried_code => hr_transaction_swi.getVarchar2Value(l_CommitNode,'HourlySalariedCode')
2738 ,p_request_id => hr_transaction_swi.getNumberValue(l_CommitNode,'RequestId')
2739 ,p_program_application_id => hr_transaction_swi.getNumberValue(l_CommitNode,'ProgramApplicationId')
2740 ,p_program_id => hr_transaction_swi.getNumberValue(l_CommitNode,'ProgramId')
2741 ,p_program_update_date => hr_transaction_swi.getDateValue(l_CommitNode,'ProgramUpdateDate')
2742 ,p_ass_attribute_category => hr_transaction_swi.getVarchar2Value(l_CommitNode,'AssAttributeCategory')
2743 ,p_ass_attribute1 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'AssAttribute1')
2744 ,p_ass_attribute2 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'AssAttribute2')
2745 ,p_ass_attribute3 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'AssAttribute3')
2746 ,p_ass_attribute4 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'AssAttribute4')
2747 ,p_ass_attribute5 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'AssAttribute5')
2748 ,p_ass_attribute6 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'AssAttribute6')
2749 ,p_ass_attribute7 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'AssAttribute7')
2750 ,p_ass_attribute8 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'AssAttribute8')
2751 ,p_ass_attribute9 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'AssAttribute9')
2752 ,p_ass_attribute10 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'AssAttribute10')
2753 ,p_ass_attribute11 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'AssAttribute11')
2754 ,p_ass_attribute12 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'AssAttribute12')
2755 ,p_ass_attribute13 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'AssAttribute13')
2756 ,p_ass_attribute14 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'AssAttribute14')
2757 ,p_ass_attribute15 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'AssAttribute15')
2758 ,p_ass_attribute16 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'AssAttribute16')
2759 ,p_ass_attribute17 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'AssAttribute17')
2760 ,p_ass_attribute18 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'AssAttribute18')
2761 ,p_ass_attribute19 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'AssAttribute19')
2762 ,p_ass_attribute20 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'AssAttribute20')
2763 ,p_ass_attribute21 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'AssAttribute21')
2764 ,p_ass_attribute22 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'AssAttribute22')
2765 ,p_ass_attribute23 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'AssAttribute23')
2766 ,p_ass_attribute24 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'AssAttribute24')
2767 ,p_ass_attribute25 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'AssAttribute25')
2768 ,p_ass_attribute26 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'AssAttribute26')
2769 ,p_ass_attribute27 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'AssAttribute27')
2770 ,p_ass_attribute28 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'AssAttribute28')
2771 ,p_ass_attribute29 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'AssAttribute29')
2772 ,p_ass_attribute30 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'AssAttribute30')
2773 ,p_title => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Title')
2774 ,p_contract_id => hr_transaction_swi.getNumberValue(l_CommitNode,'ContractId')
2775 ,p_establishment_id => hr_transaction_swi.getNumberValue(l_CommitNode,'EstablishmentId')
2776 ,p_collective_agreement_id => hr_transaction_swi.getNumberValue(l_CommitNode,'CollectiveAgreementId')
2777 ,p_cagr_grade_def_id => hr_transaction_swi.getNumberValue(l_CommitNode,'CagrGradeDefId')
2778 ,p_cagr_id_flex_num => hr_transaction_swi.getNumberValue(l_CommitNode,'CagrIdFlexNum')
2779 ,p_asg_object_version_number => l_asg_object_version_number
2780 ,p_notice_period => hr_transaction_swi.getNumberValue(l_CommitNode,'NoticePeriod')
2781 ,p_notice_period_uom => hr_transaction_swi.getVarchar2Value(l_CommitNode,'NoticePeriodUom')
2782 ,p_employee_category => hr_transaction_swi.getVarchar2Value(l_CommitNode,'EmployeeCategory')
2783 ,p_work_at_home => hr_transaction_swi.getVarchar2Value(l_CommitNode,'WorkAtHome')
2784 ,p_job_post_source_name => hr_transaction_swi.getVarchar2Value(l_CommitNode,'JobPostSourceName')
2785 ,p_posting_content_id => hr_transaction_swi.getNumberValue(l_CommitNode,'PostingContentId')
2786 ,p_placement_date_start => hr_transaction_swi.getDateValue(l_CommitNode,'PlacementDateStart')
2787 ,p_vendor_id => hr_transaction_swi.getNumberValue(l_CommitNode,'VendorId')
2788 ,p_vendor_employee_number => hr_transaction_swi.getVarchar2Value(l_CommitNode,'VendorEmployeeNumber')
2789 ,p_vendor_assignment_number => hr_transaction_swi.getVarchar2Value(l_CommitNode,'VendorAssignmentNumber')
2790 ,p_assignment_category => hr_transaction_swi.getVarchar2Value(l_CommitNode,'AssignmentCategory')
2791 ,p_project_title => hr_transaction_swi.getVarchar2Value(l_CommitNode,'ProjectTitle')
2792 ,p_applicant_rank => hr_transaction_swi.getNumberValue(l_CommitNode,'ApplicantRank')
2793 ,p_grade_ladder_pgm_id => hr_transaction_swi.getNumberValue(l_CommitNode,'GradeLadderPgmId')
2794 ,p_supervisor_assignment_id => hr_transaction_swi.getNumberValue(l_CommitNode,'SupervisorAssignmentId')
2795 ,p_vendor_site_id => hr_transaction_swi.getNumberValue(l_CommitNode,'VendorSiteId')
2796 ,p_po_header_id => hr_transaction_swi.getNumberValue(l_CommitNode,'PoHeaderId')
2797 ,p_po_line_id => hr_transaction_swi.getNumberValue(l_CommitNode,'PoLineId')
2798 ,p_projected_assignment_end => hr_transaction_swi.getDateValue(l_CommitNode,'ProjectedAssignmentEnd')
2799 ,p_payroll_id_updated => l_payroll_id_updated
2800 ,p_validation_start_date => l_validation_start_date
2801 ,p_validation_end_date => l_validation_end_date
2802 ,p_effective_date => l_effective_date
2803 ,p_datetrack_mode => hr_transaction_swi.getVarchar2Value(l_CommitNode,'DatetrackMode')
2804 ,p_validate => p_validate
2805 ,p_offer_id => l_offer_id
2806 ,p_offer_status => hr_transaction_swi.getVarchar2Value(l_CommitNode,'OfferStatus',null)
2807 ,p_return_status => l_return_status
2808 );
2809 --
2810
2811 elsif l_postState = '3' then
2812 -- call delete offer
2813 --
2814 delete_offer_assignment
2815 (p_validate => p_validate
2816 ,p_effective_date => l_effective_date
2817 ,p_offer_assignment_id => hr_transaction_swi.getNumberValue(l_CommitNode,'OfferAssignmentId',null)
2818 ,p_return_status => l_return_status
2819 );
2820 --
2821 end if;
2822 p_return_status := l_return_status;
2823 hr_utility.set_location('Exiting:' || l_proc,40);
2824
2825 end process_asg_api;
2826 -- ----------------------------------------------------------------------------
2827 -- |-----------------------------< finalize_transaction >---------------------|
2828 -- ----------------------------------------------------------------------------
2829
2830 procedure finalize_transaction
2831 (
2832 p_transaction_id in number
2833 ,p_event in varchar2
2834 ,p_return_status out nocopy varchar2
2835 )
2836 is
2837 l_return_status varchar2(1);
2838 l_object_version_number number;
2839 l_offer_id number;
2840 l_offer_version number;
2841 l_offer_status varchar2(30);
2842 l_prev_offer_status varchar2(30);
2843 l_offer_status_before_hold varchar2(30);
2844 l_applicant_assignment_id number;
2845 l_expiry_date date;
2846 l_change_reason varchar2(30);
2847 l_close_reason varchar2(30);
2848 l_status varchar2(30);
2849 l_effective_date date := trunc(sysdate);
2850 l_proc varchar2(72) := g_package || 'finalize_transaction';
2851 --
2852 cursor csr_offer_details is
2853 select offer_id
2854 ,offer_version
2855 ,offer_status
2856 ,applicant_assignment_id
2857 ,expiry_date
2858 ,object_version_number
2859 from hr_api_transactions hrt, irc_offers iro
2860 where hrt.transaction_id = p_transaction_id
2861 and hrt.transaction_ref_id = iro.offer_id;
2862 --
2863 cursor csr_offer_history_details(p_offer_id number) is
2864 select offer_status
2865 ,change_reason
2866 from irc_offer_status_history iosh1
2867 where iosh1.offer_id = p_offer_id
2868 and status_change_date = (select max(status_change_date)
2869 from irc_offer_status_history iosh2
2870 where iosh2.offer_id = p_offer_id);
2871 --
2872 cursor csr_offer_status_before_hold is
2873 select ios1.offer_status
2874 from irc_offer_status_history ios1
2875 where ios1.offer_id = l_offer_id
2876 and 1 = (select count(*)
2877 from irc_offer_status_history ios2
2878 where ios2.offer_id = l_offer_id
2879 and ios2.offer_status_history_id > ios1.offer_status_history_id);
2880 --
2881 begin
2882 --
2883 hr_utility.set_location(' Entering:' || l_proc,10);
2884 hr_utility.set_location(l_proc || ' Event:' || p_event,13);
2885 --
2886 open csr_offer_details;
2887 fetch csr_offer_details into
2888 l_offer_id, l_offer_version, l_prev_offer_status,
2889 l_applicant_assignment_id, l_expiry_date,l_object_version_number;
2890 if csr_offer_details%found then
2891 close csr_offer_details;
2892 --
2893 hr_utility.set_location(l_proc,15);
2894 --
2895 -- Approved or updated offer event handling
2896 --
2897 if p_event = 'APPROVED' OR p_event = 'SUBMIT' OR p_event = 'RESUBMIT' then
2898 hr_utility.set_location(l_proc, 20);
2899 -- if the transaction is approval of PENDING_EXTENDED, the status
2900 -- here will be EXTENDED as the update_offer would have converted
2901 -- the status already.
2902 if l_prev_offer_status = 'EXTENDED' then
2903 l_offer_status := NULL;
2904 --
2905 elsif l_prev_offer_status = 'HOLD' then
2906 open csr_offer_status_before_hold;
2907 fetch csr_offer_status_before_hold into l_offer_status_before_hold;
2908 close csr_offer_status_before_hold;
2909 --
2910 -- If the offer has been EXTENDED due to approval of PENDING_EXTENDED, and put
2911 -- back on HOLD, set the offer status to NULL.
2912 --
2913 if l_offer_status_before_hold = 'EXTENDED' then
2914 l_offer_status := NULL;
2915 elsif l_offer_status_before_hold = 'PENDING' then
2916 hr_utility.set_location(l_proc, 24);
2917 l_offer_status := 'APPROVED';
2918 end if;
2919 --
2920 else
2921 --
2922 -- This cursor is used to get the offer status and offer close reason
2923 -- If the close reason is Manager Withdraw or Applicant withdrew their
2924 -- application offer should not be updated
2925 hr_utility.set_location(l_proc, 25);
2926 open csr_offer_history_details(l_offer_id);
2927 fetch csr_offer_history_details into l_status,l_close_reason;
2928 if csr_offer_history_details%found then
2929 hr_utility.set_location(l_proc, 26);
2930 if l_status='CLOSED' and (l_close_reason='MGR_WITHDRAW' OR l_close_reason='WITHDRAWAL') then
2931 hr_utility.set_location(l_proc, 27);
2932 l_offer_status := NULL;
2933 else
2934 hr_utility.set_location(l_proc, 28);
2935 l_offer_status := 'APPROVED';
2936 end if;
2937 else
2938 hr_utility.set_location(l_proc, 29);
2939 l_offer_status := 'APPROVED';
2940 end if;
2941 close csr_offer_history_details;
2942 end if;
2943 --
2944 -- RFC offer event handling
2945 --
2946 elsif p_event = 'RFC' then
2947 hr_utility.set_location(l_proc, 30);
2948 open csr_offer_history_details(l_offer_id);
2949 fetch csr_offer_history_details into l_status,l_close_reason;
2950 if csr_offer_history_details%found then
2951 hr_utility.set_location(l_proc, 31);
2952 if l_status='CLOSED' and (l_close_reason = 'MGR_WITHDRAW' OR l_close_reason = 'WITHDRAWAL') then
2953 hr_utility.set_location(l_proc, 32);
2954 l_offer_status := NULL;
2955 else
2956 hr_utility.set_location(l_proc, 33);
2957 l_offer_status := 'CORRECTION';
2958 end if;
2959 close csr_offer_history_details;
2960 else
2961 hr_utility.set_location(l_proc, 34);
2962 l_offer_status := 'CORRECTION';
2963 close csr_offer_history_details;
2964 end if;
2965 --
2966 -- Rejected offer event handling
2967 --
2968 elsif p_event = 'REJECTED' OR p_event = 'DELETED' OR p_event = 'CANCEL' then
2969 if l_prev_offer_status = 'PENDING_EXTENDED'
2970 and l_expiry_date >= l_effective_date then
2971 l_offer_status := 'EXTENDED';
2972 else
2973 handleAttachmentsWhenRejected(p_applicant_assignment_id=>l_applicant_assignment_id);
2974 l_offer_status := 'CLOSED';
2975 if p_event = 'REJECTED' then
2976 hr_utility.set_location(l_proc, 40);
2977 l_change_reason := 'APPROVER_REJECTED';
2978 else
2979 -- DELETE, CANCEL
2980 hr_utility.set_location(l_proc, 50);
2981 l_change_reason := 'MANUAL_CLOSURE';
2982 end if;
2983 end if;
2984 end if;
2985 --
2986 if l_offer_status is not null then
2987 hr_utility.set_location(l_proc || ' Offer Status:' || l_offer_status, 60);
2988 update_offer
2989 (p_effective_date => l_effective_date
2990 ,p_offer_status => l_offer_status
2991 ,p_change_reason => l_change_reason
2992 ,p_offer_id => l_offer_id
2993 ,p_object_version_number => l_object_version_number
2994 ,p_offer_version => l_offer_version
2995 ,p_return_status => l_return_status
2996 );
2997 end if;
2998 else
2999 close csr_offer_details;
3000 end if;
3001 --
3002 p_return_status := l_return_status;
3003 hr_utility.set_location('Exiting:' || l_proc,70);
3004 --
3005 end finalize_transaction;
3006
3007 -- ----------------------------------------------------------------------------
3008 -- |-----------------------------< void_ben_records >-------------------------|
3009 -- ----------------------------------------------------------------------------
3010
3011 procedure void_ben_records
3012 ( p_applicant_assignment_id in number default null
3013 ,p_offer_assignment_id in number default null
3014 ,p_status_code in varchar2 default null
3015 ,p_effective_date in date default trunc(sysdate)
3016 ,p_transaction_id in number default null
3017 ,p_void_single_per_in_ler in varchar2 default 'N'
3018 )
3019 is
3020 --
3021 l_proc varchar2(72) := g_package || 'void_ben_records';
3022 l_void_per_in_ler_id ben_pil_assignment.per_in_ler_id%TYPE;
3023 l_restore_per_in_ler_id ben_pil_assignment.per_in_ler_id%TYPE := null;
3024 l_person_id per_all_assignments_f.person_id%TYPE := null;
3025 l_applicant_assignment_id hr_api_transactions.assignment_id%TYPE := p_applicant_assignment_id;
3026 --
3027 cursor csr_applicant_assignment_id is
3028 select assignment_id
3029 from hr_api_transactions
3030 where transaction_id = p_transaction_id;
3031 --
3032 cursor csr_void_per_in_ler_id is
3033 select max(per_in_ler_id)
3034 from ben_pil_assignment
3035 where applicant_assignment_id = l_applicant_assignment_id
3036 and offer_assignment_id is null;
3037 --
3038 cursor csr_person_id is
3039 select person_id
3040 from per_all_assignments_f
3041 where assignment_id = l_applicant_assignment_id
3042 and trunc(sysdate)
3043 between effective_start_date
3044 and effective_end_date;
3045 --
3046 cursor csr_restore_per_in_ler_id is
3047 select max(per_in_ler_id)
3048 from ben_pil_assignment
3049 where applicant_assignment_id = l_applicant_assignment_id
3050 and offer_assignment_id is not null;
3051 --
3052 PRAGMA AUTONOMOUS_TRANSACTION;
3053 --
3054 begin
3055 --
3056 hr_utility.set_location(' Entering:' || l_proc,10);
3057 --
3058 if p_applicant_assignment_id is null
3059 then
3060 --
3061 open csr_applicant_assignment_id;
3062 fetch csr_applicant_assignment_id into l_applicant_assignment_id;
3063 close csr_applicant_assignment_id;
3064 --
3065 end if;
3066 --
3067 open csr_void_per_in_ler_id;
3068 fetch csr_void_per_in_ler_id into l_void_per_in_ler_id;
3069 if csr_void_per_in_ler_id%found
3070 then
3071 --
3072 close csr_void_per_in_ler_id;
3073 --
3074 open csr_restore_per_in_ler_id;
3075 fetch csr_restore_per_in_ler_id into l_restore_per_in_ler_id;
3076 close csr_restore_per_in_ler_id;
3077 --
3078 open csr_person_id;
3079 fetch csr_person_id into l_person_id;
3080 close csr_person_id;
3081 --
3082 if p_void_single_per_in_ler = 'N'
3083 then
3084 --
3085 -- Since we need to VOID all the ben records following the latest
3086 -- ben record associated with an offer assignment, pass NULL for
3087 -- void_per_in_ler_id
3088 --
3089 l_void_per_in_ler_id := null;
3090 --
3091 end if;
3092 --
3093 ben_irc_util.void_or_restore_life_event
3094 ( p_person_id => l_person_id
3095 ,p_assignment_id => l_applicant_assignment_id
3096 ,p_offer_assignment_id => p_offer_assignment_id
3097 ,p_void_per_in_ler_id => l_void_per_in_ler_id
3098 ,p_restore_per_in_ler_id => l_restore_per_in_ler_id
3099 ,p_status_cd => p_status_code
3100 ,p_effective_date => p_effective_date
3101 );
3102 --
3103 else
3104 --
3105 close csr_void_per_in_ler_id;
3106 --
3107 end if;
3108 --
3109 commit;
3110 --
3111 exception
3112 when OTHERS then
3113 rollback;
3114 --
3115 hr_utility.set_location('Exiting:' || l_proc,20);
3116 --
3117 end void_ben_records;
3118
3119 --
3120 -- ----------------------------------------------------------------------------
3121 -- |-----------------------------< handleAttachmentsWhenCommit >---------------|
3122 -- ----------------------------------------------------------------------------
3123 --
3124 procedure handleAttachmentsWhenCommit(p_applicant_assignment_id in number) is
3125 PRAGMA AUTONOMOUS_TRANSACTION;
3126 l_proc varchar2(72) := g_package || 'handleAttachmentsWhenCommit';
3127 begin
3128 hr_utility.set_location(' Entering:' || l_proc,10);
3129 fnd_attached_documents2_pkg.delete_attachments(X_entity_name=>'IRC_EXT_OFFER_APPROVED',X_pk1_value=>p_applicant_assignment_id,X_delete_document_flag=>'Y');
3130 fnd_attached_documents2_pkg.delete_attachments(X_entity_name=>'IRC_INT_OFFER_APPROVED',X_pk1_value=>p_applicant_assignment_id,X_delete_document_flag=>'Y');
3131
3132 fnd_attached_documents2_pkg.copy_attachments(X_from_entity_name =>'IRC_EXT_OFFER',X_from_pk1_value =>p_applicant_assignment_id,X_to_entity_name=>'IRC_EXT_OFFER_APPROVED',X_to_pk1_value=>p_applicant_assignment_id);
3133 fnd_attached_documents2_pkg.copy_attachments(X_from_entity_name =>'IRC_INT_OFFER',X_from_pk1_value =>p_applicant_assignment_id,X_to_entity_name=>'IRC_INT_OFFER_APPROVED',X_to_pk1_value=>p_applicant_assignment_id);
3134
3135 fnd_attached_documents2_pkg.delete_attachments(X_entity_name=>'IRC_EXT_OFFER',X_pk1_value=>p_applicant_assignment_id,X_delete_document_flag=>'Y');
3136 fnd_attached_documents2_pkg.delete_attachments(X_entity_name=>'IRC_INT_OFFER',X_pk1_value=>p_applicant_assignment_id,X_delete_document_flag=>'Y');
3137 commit;
3138 hr_utility.set_location(' Exiting:' || l_proc,20);
3139 end;
3140 --
3141 -- ----------------------------------------------------------------------------
3142 -- |-----------------------------< handleAttachmentsWhenRejected >-------------|
3143 -- ----------------------------------------------------------------------------
3144 --
3145 procedure handleAttachmentsWhenRejected(p_applicant_assignment_id in number) is
3146 PRAGMA AUTONOMOUS_TRANSACTION;
3147 l_proc varchar2(72) := g_package || 'handleAttachmentsWhenRejected';
3148 begin
3149 hr_utility.set_location(' Entering:' || l_proc,10);
3150 fnd_attached_documents2_pkg.delete_attachments(X_entity_name=>'IRC_EXT_OFFER',X_pk1_value=>p_applicant_assignment_id,X_delete_document_flag=>'Y');
3151 fnd_attached_documents2_pkg.delete_attachments(X_entity_name=>'IRC_INT_OFFER',X_pk1_value=>p_applicant_assignment_id,X_delete_document_flag=>'Y');
3152 commit;
3153 hr_utility.set_location(' Exiting:' || l_proc,20);
3154 end;
3155
3156 -- ---------------------------------------------------------------------------
3157 -- |-----------------------------< handleAttachmentsWhenEditing >--------------|
3158 -- ----------------------------------------------------------------------------
3159 --
3160 procedure handleAttachmentsWhenEdit(p_applicant_assignment_id in number) is
3161 PRAGMA AUTONOMOUS_TRANSACTION;
3162 l_proc varchar2(72) := g_package || 'handleAttachmentsWhenEdit';
3163 begin
3164 hr_utility.set_location(' Entering:' || l_proc,10);
3165
3166 fnd_attached_documents2_pkg.delete_attachments(X_entity_name=>'IRC_EXT_OFFER',X_pk1_value=>p_applicant_assignment_id,X_delete_document_flag=>'Y');
3167 fnd_attached_documents2_pkg.delete_attachments(X_entity_name=>'IRC_INT_OFFER',X_pk1_value=>p_applicant_assignment_id,X_delete_document_flag=>'Y');
3168
3169 fnd_attached_documents2_pkg.copy_attachments(X_from_entity_name =>'IRC_INT_OFFER_APPROVED',X_from_pk1_value =>p_applicant_assignment_id,X_to_entity_name=>'IRC_INT_OFFER',X_to_pk1_value=>p_applicant_assignment_id);
3170 fnd_attached_documents2_pkg.copy_attachments(X_from_entity_name =>'IRC_EXT_OFFER_APPROVED',X_from_pk1_value =>p_applicant_assignment_id,X_to_entity_name=>'IRC_EXT_OFFER',X_to_pk1_value=>p_applicant_assignment_id);
3171 commit;
3172 hr_utility.set_location(' Exiting:' || l_proc,20);
3173 end;
3174
3175 end irc_offers_swi;