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