[Home] [Help]
PACKAGE BODY: APPS.HR_RATE_VALUES_SWI
Source
1 PACKAGE BODY HR_RATE_VALUES_SWI AS
2 /* $Header: hrpgrswi.pkb 115.9 2004/04/01 10:46 svittal noship $ */
3 --
4 -- Package variables
5 -- Global Variables
6 l_trans_tbl hr_transaction_ss.transaction_table;
7 g_package Varchar2(30):='HR_RATE_VALUES_SWI';
8
9 --
10 --
11 --
12 --
13 -- ----------------------------------------------------------------------------
14 -- |---------------------< create_assignment_rate_value >---------------------|
15 -- ----------------------------------------------------------------------------
16 PROCEDURE create_assignment_rate_value
17 (p_validate in boolean default false
18 ,p_effective_date in date
19 ,p_business_group_id in number
20 ,p_rate_id in number
21 ,p_assignment_id in number
22 ,p_rate_type in varchar2
23 ,p_currency_code in varchar2
24 ,p_value in varchar2
25 ,p_grade_rule_id in out nocopy number
26 ,p_object_version_number out nocopy number
27 ,p_effective_start_date out nocopy date
28 ,p_effective_end_date out nocopy date
29 ,p_return_status out nocopy varchar2
30 ) is
31 --
32 --
33 -- Variables for IN/OUT parameters
34 --
35 -- Other variables
36
37 l_grade_rule_id number;
38 l_grade_rule_id_temp number;
39 l_proc varchar2(72) := g_package ||'create_assignment_rate_value';
40 Begin
41 hr_utility.set_location(' Entering:' || l_proc,10);
42 --
43 -- Issue a savepoint
44 --
45 l_grade_rule_id_temp := p_grade_rule_id;
46 savepoint create_assignment_rate_value;
47 --
48 -- Initialise Multiple Message Detection
49 --
50 hr_multi_message.enable_message_list;
51 --
52 -- Remember IN OUT parameter IN values
53 --
54 --
55 --
56 -- Call API
57 --
58 hr_rate_values_api.create_assignment_rate_value
59 (p_validate => p_validate
60 ,p_effective_date => p_effective_date
61 ,p_business_group_id => p_business_group_id
62 ,p_rate_id => p_rate_id
63 ,p_assignment_id => p_assignment_id
64 ,p_rate_type => p_rate_type
65 ,p_currency_code => p_currency_code
66 ,p_value => p_value
67 ,p_grade_rule_id => l_grade_rule_id
68 ,p_object_version_number => p_object_version_number
69 ,p_effective_start_date => p_effective_start_date
70 ,p_effective_end_date => p_effective_end_date
71 );
72 p_grade_rule_id := l_grade_rule_id;
73 --
74 -- Convert API warning boolean parameter values to specific
75 -- messages and add them to Multiple Message List
76 --
77 --
78 -- Convert API non-warning boolean parameter values
79 --
80 --
81 -- Derive the API return status value based on whether
82 -- messages of any type exist in the Multiple Message List.
83 -- Also disable Multiple Message Detection.
84 --
85 p_return_status := hr_multi_message.get_return_status_disable;
86 hr_utility.set_location(' Leaving:' || l_proc,20);
87 --
88 exception
89 when hr_multi_message.error_message_exist then
90 --
91 -- Catch the Multiple Message List exception which
92 -- indicates API processing has been aborted because
93 -- at least one message exists in the list.
94 --
95 rollback to create_assignment_rate_value;
96 --
97 -- Reset IN OUT parameters and set OUT parameters
98 --
99 p_object_version_number := null;
100 p_effective_start_date := null;
101 p_effective_end_date := null;
102 p_return_status := hr_multi_message.get_return_status_disable;
103 hr_utility.set_location(' Leaving:' || l_proc, 30);
104 when others then
105 --
106 -- When Multiple Message Detection is enabled catch
107 -- any Application specific or other unexpected
108 -- exceptions. Adding appropriate details to the
109 -- Multiple Message List. Otherwise re-raise the
110 -- error.
111 --
112 rollback to create_assignment_rate_value;
113
114
115 if hr_multi_message.unexpected_error_add(l_proc) then
116 hr_utility.set_location(' Leaving:' || l_proc,40);
117 raise;
118 end if;
119 --
120 -- Reset IN OUT and set OUT parameters
121 --
122 p_grade_rule_id := l_grade_rule_id_temp;
123 p_object_version_number := null;
124 p_effective_start_date := null;
125 p_effective_end_date := null;
126 p_return_status := hr_multi_message.get_return_status_disable;
127 hr_utility.set_location(' Leaving:' || l_proc,50);
128 end create_assignment_rate_value;
129 -- ----------------------------------------------------------------------------
130 -- |---------------------------< create_rate_value >--------------------------|
131 -- ----------------------------------------------------------------------------
132 PROCEDURE create_rate_value
133 (p_validate in number default hr_api.g_false_num
134 ,p_effective_date in date
135 ,p_business_group_id in number
136 ,p_rate_id in number
137 ,p_grade_or_spinal_point_id in number
138 ,p_rate_type in varchar2
139 ,p_currency_code in varchar2 default null
140 ,p_maximum in varchar2 default null
141 ,p_mid_value in varchar2 default null
142 ,p_minimum in varchar2 default null
143 ,p_sequence in number default null
144 ,p_value in varchar2 default null
145 ,p_grade_rule_id out nocopy number
146 ,p_object_version_number out nocopy number
147 ,p_effective_start_date out nocopy date
148 ,p_effective_end_date out nocopy date
149 ,p_return_status out nocopy varchar2
150 ) is
151 --
152 -- Variables for API Boolean parameters
153 l_validate boolean;
154 --
155 -- Variables for IN/OUT parameters
156 --
157 -- Other variables
158 l_grade_rule_id number;
159 l_proc varchar2(72) := g_package ||'create_rate_value';
160 Begin
161 hr_utility.set_location(' Entering:' || l_proc,10);
162 --
163 -- Issue a savepoint
164 --
165 savepoint create_rate_value_swi;
166 --
167 -- Initialise Multiple Message Detection
168 --
169 hr_multi_message.enable_message_list;
170 --
171 -- Remember IN OUT parameter IN values
172 --
173 --
174 -- Convert constant values to their corresponding boolean value
175 --
176 l_validate :=
177 hr_api.constant_to_boolean
178 (p_constant_value => p_validate);
179 --
180 -- Register Surrogate ID or user key values
181 --
182 pay_pgr_ins.set_base_key_value
183 (p_grade_rule_id => p_grade_rule_id
184 );
185 --
186 -- Call API
187 --
188 hr_rate_values_api.create_rate_value
189 (p_validate => l_validate
190 ,p_effective_date => p_effective_date
191 ,p_business_group_id => p_business_group_id
192 ,p_rate_id => p_rate_id
193 ,p_grade_or_spinal_point_id => p_grade_or_spinal_point_id
194 ,p_rate_type => p_rate_type
195 ,p_currency_code => p_currency_code
196 ,p_maximum => p_maximum
197 ,p_mid_value => p_mid_value
198 ,p_minimum => p_minimum
199 ,p_sequence => p_sequence
200 ,p_value => p_value
201 ,p_grade_rule_id => l_grade_rule_id
202 ,p_object_version_number => p_object_version_number
203 ,p_effective_start_date => p_effective_start_date
204 ,p_effective_end_date => p_effective_end_date
205 );
206 --
207 -- Convert API warning boolean parameter values to specific
208 -- messages and add them to Multiple Message List
209 --
210 --
211 -- Convert API non-warning boolean parameter values
212 --
213 --
214 -- Derive the API return status value based on whether
215 -- messages of any type exist in the Multiple Message List.
216 -- Also disable Multiple Message Detection.
217 --
218 p_return_status := hr_multi_message.get_return_status_disable;
219 hr_utility.set_location(' Leaving:' || l_proc,20);
220 --
221 exception
222 when hr_multi_message.error_message_exist then
223 --
224 -- Catch the Multiple Message List exception which
225 -- indicates API processing has been aborted because
226 -- at least one message exists in the list.
227 --
228 rollback to create_rate_value_swi;
229 --
230 -- Reset IN OUT parameters and set OUT parameters
231 --
232 p_object_version_number := null;
233 p_effective_start_date := null;
234 p_effective_end_date := null;
235 p_return_status := hr_multi_message.get_return_status_disable;
236 hr_utility.set_location(' Leaving:' || l_proc, 30);
237 when others then
238 --
239 -- When Multiple Message Detection is enabled catch
240 -- any Application specific or other unexpected
241 -- exceptions. Adding appropriate details to the
242 -- Multiple Message List. Otherwise re-raise the
243 -- error.
244 --
245 rollback to create_rate_value_swi;
246 if hr_multi_message.unexpected_error_add(l_proc) then
247 hr_utility.set_location(' Leaving:' || l_proc,40);
248 raise;
249 end if;
250 --
251 -- Reset IN OUT and set OUT parameters
252 --
253 p_object_version_number := null;
254 p_effective_start_date := null;
255 p_effective_end_date := null;
256 p_return_status := hr_multi_message.get_return_status_disable;
257 hr_utility.set_location(' Leaving:' || l_proc,50);
258 end create_rate_value;
259 --
260 --
261 -- ----------------------------------------------------------------------------
262 -- |---------------------< update_assignment_rate_value >---------------------|
263 -- ----------------------------------------------------------------------------
264 PROCEDURE update_assignment_rate_value
265 (p_validate in boolean default false
266 ,p_grade_rule_id in number
267 ,p_effective_date in date
268 ,p_datetrack_mode in varchar2
269 ,p_currency_code in varchar2 default hr_api.g_varchar2
270 ,p_value in varchar2 default hr_api.g_varchar2
271 ,p_object_version_number in out nocopy number
272 ,p_effective_start_date out nocopy date
273 ,p_effective_end_date out nocopy date
274 ,p_return_status out nocopy varchar2
275 ) is
276 --
277 --
278 -- Variables for IN/OUT parameters
279 l_object_version_number number;
280 --
281 -- Other variables
282 l_proc varchar2(72) := g_package ||'update_assignment_rate_value';
283 Begin
284 hr_utility.set_location(' Entering:' || l_proc,10);
285 --
286 -- Issue a savepoint
287 --
288 savepoint update_assignment_rate_value;
289 --
290 -- Initialise Multiple Message Detection
291 --
292 hr_multi_message.enable_message_list;
293 --
294 -- Remember IN OUT parameter IN values
295 --
296 l_object_version_number := p_object_version_number;
297 --
298 --
299 -- Call API
300 --
301 hr_rate_values_api.update_assignment_rate_value
302 (p_validate => p_validate
303 ,p_grade_rule_id => p_grade_rule_id
304 ,p_effective_date => p_effective_date
305 ,p_datetrack_mode => p_datetrack_mode
306 ,p_currency_code => p_currency_code
307 ,p_value => p_value
308 ,p_object_version_number => p_object_version_number
309 ,p_effective_start_date => p_effective_start_date
310 ,p_effective_end_date => p_effective_end_date
311 );
312 --
313 -- Convert API warning boolean parameter values to specific
314 -- messages and add them to Multiple Message List
315 --
316 --
317 -- Convert API non-warning boolean parameter values
318 --
319 --
320 -- Derive the API return status value based on whether
321 -- messages of any type exist in the Multiple Message List.
322 -- Also disable Multiple Message Detection.
323 --
324 p_return_status := hr_multi_message.get_return_status_disable;
325 hr_utility.set_location(' Leaving:' || l_proc,20);
326 --
327 exception
328 when hr_multi_message.error_message_exist then
329 --
330 -- Catch the Multiple Message List exception which
331 -- indicates API processing has been aborted because
332 -- at least one message exists in the list.
333 --
334 rollback to update_assignment_rate_value;
335 --
336 -- Reset IN OUT parameters and set OUT parameters
337 --
338 p_object_version_number := l_object_version_number;
339 p_effective_start_date := null;
340 p_effective_end_date := null;
341 p_return_status := hr_multi_message.get_return_status_disable;
342 hr_utility.set_location(' Leaving:' || l_proc, 30);
343 when others then
344 --
345 -- When Multiple Message Detection is enabled catch
346 -- any Application specific or other unexpected
347 -- exceptions. Adding appropriate details to the
348 -- Multiple Message List. Otherwise re-raise the
349 -- error.
350 --
351 rollback to update_assignment_rate_value;
352 if hr_multi_message.unexpected_error_add(l_proc) then
353 hr_utility.set_location(' Leaving:' || l_proc,40);
354 raise;
355 end if;
356 --
357 -- Reset IN OUT and set OUT parameters
358 --
359 p_object_version_number := l_object_version_number;
360 p_effective_start_date := null;
361 p_effective_end_date := null;
362 p_return_status := hr_multi_message.get_return_status_disable;
363 hr_utility.set_location(' Leaving:' || l_proc,50);
364 end update_assignment_rate_value;
365 -- ----------------------------------------------------------------------------
366 -- |---------------------------< update_rate_value >--------------------------|
367 -- ----------------------------------------------------------------------------
368 PROCEDURE update_rate_value
369 (p_validate in number default hr_api.g_false_num
370 ,p_grade_rule_id in number
371 ,p_effective_date in date
372 ,p_datetrack_mode in varchar2
373 ,p_currency_code in varchar2 default hr_api.g_varchar2
374 ,p_maximum in varchar2 default hr_api.g_varchar2
375 ,p_mid_value in varchar2 default hr_api.g_varchar2
376 ,p_minimum in varchar2 default hr_api.g_varchar2
377 ,p_sequence in number default hr_api.g_number
378 ,p_value in varchar2 default hr_api.g_varchar2
379 ,p_object_version_number in out nocopy number
380 ,p_effective_start_date out nocopy date
381 ,p_effective_end_date out nocopy date
382 ,p_return_status out nocopy varchar2
383 ) is
384 --
385 -- Variables for API Boolean parameters
386 l_validate boolean;
387 --
388 -- Variables for IN/OUT parameters
389 l_object_version_number number;
390 --
391 -- Other variables
392 l_proc varchar2(72) := g_package ||'update_rate_value';
393 Begin
394 hr_utility.set_location(' Entering:' || l_proc,10);
395 --
396 -- Issue a savepoint
397 --
398 savepoint update_rate_value_swi;
399 --
400 -- Initialise Multiple Message Detection
401 --
402 hr_multi_message.enable_message_list;
403 --
404 -- Remember IN OUT parameter IN values
405 --
406 l_object_version_number := p_object_version_number;
407 --
408 -- Convert constant values to their corresponding boolean value
409 --
410 l_validate :=
411 hr_api.constant_to_boolean
412 (p_constant_value => p_validate);
413 --
414 -- Register Surrogate ID or user key values
415 --
416 --
417 -- Call API
418 --
419 hr_rate_values_api.update_rate_value
420 (p_validate => l_validate
421 ,p_grade_rule_id => p_grade_rule_id
422 ,p_effective_date => p_effective_date
423 ,p_datetrack_mode => p_datetrack_mode
424 ,p_currency_code => p_currency_code
425 ,p_maximum => p_maximum
426 ,p_mid_value => p_mid_value
427 ,p_minimum => p_minimum
428 ,p_sequence => p_sequence
429 ,p_value => p_value
430 ,p_object_version_number => p_object_version_number
431 ,p_effective_start_date => p_effective_start_date
432 ,p_effective_end_date => p_effective_end_date
433 );
434 --
435 -- Convert API warning boolean parameter values to specific
436 -- messages and add them to Multiple Message List
437 --
438 --
439 -- Convert API non-warning boolean parameter values
440 --
441 --
442 -- Derive the API return status value based on whether
443 -- messages of any type exist in the Multiple Message List.
444 -- Also disable Multiple Message Detection.
445 --
446 p_return_status := hr_multi_message.get_return_status_disable;
447 hr_utility.set_location(' Leaving:' || l_proc,20);
448 --
449 exception
450 when hr_multi_message.error_message_exist then
451 --
452 -- Catch the Multiple Message List exception which
453 -- indicates API processing has been aborted because
454 -- at least one message exists in the list.
455 --
456 rollback to update_rate_value_swi;
457 --
458 -- Reset IN OUT parameters and set OUT parameters
459 --
460 p_object_version_number := l_object_version_number;
461 p_effective_start_date := null;
462 p_effective_end_date := null;
463 p_return_status := hr_multi_message.get_return_status_disable;
464 hr_utility.set_location(' Leaving:' || l_proc, 30);
465 when others then
466 --
467 -- When Multiple Message Detection is enabled catch
468 -- any Application specific or other unexpected
469 -- exceptions. Adding appropriate details to the
470 -- Multiple Message List. Otherwise re-raise the
471 -- error.
472 --
473 rollback to update_rate_value_swi;
474 if hr_multi_message.unexpected_error_add(l_proc) then
475 hr_utility.set_location(' Leaving:' || l_proc,40);
476 raise;
477 end if;
478 --
479 -- Reset IN OUT and set OUT parameters
480 --
481 p_object_version_number := l_object_version_number;
482 p_effective_start_date := null;
483 p_effective_end_date := null;
484 p_return_status := hr_multi_message.get_return_status_disable;
485 hr_utility.set_location(' Leaving:' || l_proc,50);
486 end update_rate_value;
487 -- ----------------------------------------------------------------------------
488 -- |---------------------------< delete_rate_value >--------------------------|
489 -- ----------------------------------------------------------------------------
490 PROCEDURE delete_rate_value
491 (p_validate in boolean default false
492 ,p_grade_rule_id in number
493 ,p_datetrack_mode in varchar2
494 ,p_effective_date in date
495 ,p_object_version_number in out nocopy number
496 ,p_effective_start_date out nocopy date
497 ,p_effective_end_date out nocopy date
498 ,p_return_status out nocopy varchar2
499 ) is
500 --
501 -- Variables for API Boolean parameters
502 l_validate boolean;
503 --
504 -- Variables for IN/OUT parameters
505 l_object_version_number number;
506 --
507 -- Other variables
508 l_proc varchar2(72) := g_package ||'delete_rate_value';
509 Begin
510 hr_utility.set_location(' Entering:' || l_proc,10);
511 --
512 -- Issue a savepoint
513 --
514 savepoint delete_rate_value_swi;
515 --
516 -- Initialise Multiple Message Detection
517 --
518 hr_multi_message.enable_message_list;
519 --
520 -- Remember IN OUT parameter IN values
521 --
522 l_object_version_number := p_object_version_number;
523 --
524 --
525 -- Register Surrogate ID or user key values
526 --
527 --
528 -- Call API
529 --
530 hr_rate_values_api.delete_rate_value
531 (p_validate => l_validate
532 ,p_grade_rule_id => p_grade_rule_id
533 ,p_datetrack_mode => p_datetrack_mode
534 ,p_effective_date => p_effective_date
535 ,p_object_version_number => p_object_version_number
536 ,p_effective_start_date => p_effective_start_date
537 ,p_effective_end_date => p_effective_end_date
538 );
539 --
540 -- Convert API warning boolean parameter values to specific
541 -- messages and add them to Multiple Message List
542 --
543 --
544 -- Convert API non-warning boolean parameter values
545 --
546 --
547 -- Derive the API return status value based on whether
548 -- messages of any type exist in the Multiple Message List.
549 -- Also disable Multiple Message Detection.
550 --
551 p_return_status := hr_multi_message.get_return_status_disable;
552 hr_utility.set_location(' Leaving:' || l_proc,20);
553 --
554 exception
555 when hr_multi_message.error_message_exist then
556 --
557 -- Catch the Multiple Message List exception which
558 -- indicates API processing has been aborted because
559 -- at least one message exists in the list.
560 --
561 rollback to delete_rate_value_swi;
562 --
563 -- Reset IN OUT parameters and set OUT parameters
564 --
565 p_object_version_number := l_object_version_number;
566 p_effective_start_date := null;
567 p_effective_end_date := null;
568 p_return_status := hr_multi_message.get_return_status_disable;
569 hr_utility.set_location(' Leaving:' || l_proc, 30);
570 when others then
571 --
572 -- When Multiple Message Detection is enabled catch
573 -- any Application specific or other unexpected
574 -- exceptions. Adding appropriate details to the
575 -- Multiple Message List. Otherwise re-raise the
576 -- error.
577 --
578 rollback to delete_rate_value_swi;
579 if hr_multi_message.unexpected_error_add(l_proc) then
580 hr_utility.set_location(' Leaving:' || l_proc,40);
581 raise;
582 end if;
583 --
584 -- Reset IN OUT and set OUT parameters
585 --
586 p_object_version_number := l_object_version_number;
587 p_effective_start_date := null;
588 p_effective_end_date := null;
589 p_return_status := hr_multi_message.get_return_status_disable;
590 hr_utility.set_location(' Leaving:' || l_proc,50);
591 end delete_rate_value;
592 -- ----------------------------------------------------------------------------
593 -- |---------------------------< delete_rate_value >--------------------------|
594 -- ----------------------------------------------------------------------------
595 PROCEDURE delete_rate_value
596 (p_validate in number default hr_api.g_false_num
597 ,p_grade_rule_id in number
598 ,p_datetrack_mode in varchar2
599 ,p_effective_date in date
600 ,p_object_version_number in out nocopy number
601 ,p_effective_start_date out nocopy date
602 ,p_effective_end_date out nocopy date
603 ,p_return_status out nocopy varchar2
604 ) is
605 --
606 -- Variables for API Boolean parameters
607 l_validate boolean;
608 --
609 -- Variables for IN/OUT parameters
610 l_object_version_number number;
611 --
612 -- Other variables
613 l_proc varchar2(72) := g_package ||'delete_rate_value';
614 Begin
615 hr_utility.set_location(' Entering:' || l_proc,10);
616 --
617 -- Issue a savepoint
618 --
619 savepoint delete_rate_value_swi;
620 --
621 -- Initialise Multiple Message Detection
622 --
623 hr_multi_message.enable_message_list;
624 --
625 -- Remember IN OUT parameter IN values
626 --
627 l_object_version_number := p_object_version_number;
628 --
629 -- Convert constant values to their corresponding boolean value
630 --
631 l_validate :=
632 hr_api.constant_to_boolean
633 (p_constant_value => p_validate);
634 --
635 -- Register Surrogate ID or user key values
636 --
637 --
638 -- Call API
639 --
640 hr_rate_values_api.delete_rate_value
641 (p_validate => l_validate
642 ,p_grade_rule_id => p_grade_rule_id
643 ,p_datetrack_mode => p_datetrack_mode
644 ,p_effective_date => p_effective_date
645 ,p_object_version_number => p_object_version_number
646 ,p_effective_start_date => p_effective_start_date
647 ,p_effective_end_date => p_effective_end_date
648 );
649 --
650 -- Convert API warning boolean parameter values to specific
651 -- messages and add them to Multiple Message List
652 --
653 --
654 -- Convert API non-warning boolean parameter values
655 --
656 --
657 -- Derive the API return status value based on whether
658 -- messages of any type exist in the Multiple Message List.
659 -- Also disable Multiple Message Detection.
660 --
661 p_return_status := hr_multi_message.get_return_status_disable;
662 hr_utility.set_location(' Leaving:' || l_proc,20);
663 --
664 exception
665 when hr_multi_message.error_message_exist then
666 --
667 -- Catch the Multiple Message List exception which
668 -- indicates API processing has been aborted because
669 -- at least one message exists in the list.
670 --
671 rollback to delete_rate_value_swi;
672 --
673 -- Reset IN OUT parameters and set OUT parameters
674 --
675 p_object_version_number := l_object_version_number;
676 p_effective_start_date := null;
677 p_effective_end_date := null;
678 p_return_status := hr_multi_message.get_return_status_disable;
679 hr_utility.set_location(' Leaving:' || l_proc, 30);
680 when others then
681 --
682 -- When Multiple Message Detection is enabled catch
683 -- any Application specific or other unexpected
684 -- exceptions. Adding appropriate details to the
685 -- Multiple Message List. Otherwise re-raise the
686 -- error.
687 --
688 rollback to delete_rate_value_swi;
689 if hr_multi_message.unexpected_error_add(l_proc) then
690 hr_utility.set_location(' Leaving:' || l_proc,40);
691 raise;
692 end if;
693 --
694 -- Reset IN OUT and set OUT parameters
695 --
696 p_object_version_number := l_object_version_number;
697 p_effective_start_date := null;
698 p_effective_end_date := null;
699 p_return_status := hr_multi_message.get_return_status_disable;
700 hr_utility.set_location(' Leaving:' || l_proc,50);
701 end delete_rate_value;
702 -- ----------------------------------------------------------------------------
703 -- |----------------------------------< lck >---------------------------------|
704 -- ----------------------------------------------------------------------------
705 PROCEDURE lck
706 (p_grade_rule_id in number
707 ,p_object_version_number in number
708 ,p_effective_date in date
709 ,p_datetrack_mode in varchar2
710 ,p_validation_start_date out nocopy date
711 ,p_validation_end_date out nocopy date
712 ,p_return_status out nocopy varchar2
713 ) is
714 --
715 -- Variables for API Boolean parameters
716 --
717 -- Variables for IN/OUT parameters
718 --
719 -- Other variables
720 l_proc varchar2(72) := g_package ||'lck';
721 Begin
722 hr_utility.set_location(' Entering:' || l_proc,10);
723 --
724 -- Issue a savepoint
725 --
726 savepoint lck_swi;
727 --
728 -- Initialise Multiple Message Detection
729 --
730 hr_multi_message.enable_message_list;
731 --
732 -- Remember IN OUT parameter IN values
733 --
734 --
735 -- Convert constant values to their corresponding boolean value
736 --
737 --
738 -- Register Surrogate ID or user key values
739 --
740 --
741 -- Call API
742 --
743 hr_rate_values_api.lck
744 (p_grade_rule_id => p_grade_rule_id
745 ,p_object_version_number => p_object_version_number
746 ,p_effective_date => p_effective_date
747 ,p_datetrack_mode => p_datetrack_mode
748 ,p_validation_start_date => p_validation_start_date
749 ,p_validation_end_date => p_validation_end_date
750 );
751 --
752 -- Convert API warning boolean parameter values to specific
753 -- messages and add them to Multiple Message List
754 --
755 --
756 -- Convert API non-warning boolean parameter values
757 --
758 --
759 -- Derive the API return status value based on whether
760 -- messages of any type exist in the Multiple Message List.
761 -- Also disable Multiple Message Detection.
762 --
763 p_return_status := hr_multi_message.get_return_status_disable;
764 hr_utility.set_location(' Leaving:' || l_proc,20);
765 --
766 exception
767 when hr_multi_message.error_message_exist then
768 --
769 -- Catch the Multiple Message List exception which
770 -- indicates API processing has been aborted because
771 -- at least one message exists in the list.
772 --
773 rollback to lck_swi;
774 --
775 -- Reset IN OUT parameters and set OUT parameters
776 --
777 p_validation_start_date := null;
778 p_validation_end_date := null;
779 p_return_status := hr_multi_message.get_return_status_disable;
780 hr_utility.set_location(' Leaving:' || l_proc, 30);
781 when others then
782 --
783 -- When Multiple Message Detection is enabled catch
784 -- any Application specific or other unexpected
785 -- exceptions. Adding appropriate details to the
786 -- Multiple Message List. Otherwise re-raise the
787 -- error.
788 --
789 rollback to lck_swi;
790 if hr_multi_message.unexpected_error_add(l_proc) then
791 hr_utility.set_location(' Leaving:' || l_proc,40);
792 raise;
793 end if;
794 --
795 -- Reset IN OUT and set OUT parameters
796 --
797 p_validation_start_date := null;
798 p_validation_end_date := null;
799 p_return_status := hr_multi_message.get_return_status_disable;
800 hr_utility.set_location(' Leaving:' || l_proc,50);
801 end lck;
802 -- ---------------------------------------------------------------------------
803 -- ---------------------------- < process_api > ------------------------------
804 -- ---------------------------------------------------------------------------
805 -- Purpose: This procedure is used by the WF procedures to commit or validate
806 -- the transaction step with HRMS system
807 -- ---------------------------------------------------------------------------
808 PROCEDURE process_api
809 (p_validate in boolean default false
810 ,p_transaction_step_id in number default null
811 ,p_effective_date in varchar2 default null
812 ) is
813
814 l_return_status VARCHAR2(5) := 'S';
815 l_asg_rate_rec HR_ASG_RATE_TYPE;
816 l_effective_date date;
817 l_record_status VARCHAR2(15);
818 l_assignment_id NUMBER;
819
820 l_po_line_id NUMBER;
821 l_stp_value_name varchar2(20);
822 l_po_installed boolean default false;
823
824 cursor get_step_values (p_transaction_step_id in number) is
825 select name, number_value from hr_api_transaction_values
826 where transaction_step_id = p_transaction_step_id
827 and name = 'P_PO_LINE_ID';
828
829 not_a_valid_po_txn exception;
830
831 Begin
832
833
834 -- check to see if there exists a old SFL (before PO integration) and trying
835 -- to retrieve this SFL after PO is installed.
836 -- to be modified
837 --l_po_installed := hr_po_info.full_cwk_enabled;
838
839 if l_po_installed then
840 open get_step_values(p_transaction_step_id);
841 fetch get_step_values into l_stp_value_name, l_po_line_id;
842 if get_step_values%NOTFOUND then
843 raise not_a_valid_po_txn;
844 else
845 return;
846 end if;
847 end if;
848
849 l_effective_date:= to_date(hr_transaction_ss.get_wf_effective_date
850 (p_transaction_step_id => p_transaction_step_id),
851 hr_transaction_ss.g_date_format
852 );
853 dt_fndate.set_effective_date(l_effective_date);
854 -- start registration
855 -- If its a new user registration flow then the assignmentId which is coming
856 -- from transaction table will not be valid because the person has just been
857 -- created by the process_api of the hr_process_person_ss.process_api.
858 -- We can get that person Id and assignment id by making a call
859 -- to the global parameters but we need to branch out the code.
860 -- Adding the session id check to avoid connection pooling problems.
861 if (( hr_process_person_ss.g_assignment_id is not null) and
862 (hr_process_person_ss.g_session_id= ICX_SEC.G_SESSION_ID))
863 then
864 -- Set the Assignment Id to the one just created, don't use the
865 -- transaction table.
866 l_assignment_id := hr_process_person_ss.g_assignment_id;
867 else
868 l_assignment_id := hr_transaction_api.get_number_value(p_transaction_step_id, 'P_ASSIGNMENT_ID');
869 end if;
870 -- end registration
871 --
872 l_asg_rate_rec := HR_ASG_RATE_TYPE
873 (hr_transaction_api.get_number_value(p_transaction_step_id, 'P_BUSINESS_GROUP_ID')
874 ,hr_transaction_api.get_varchar2_value(p_transaction_step_id, 'P_RATE_NAME')
875 ,hr_transaction_api.get_number_value(p_transaction_step_id, 'P_RATE_ID')
876 ,hr_transaction_api.get_varchar2_value(p_transaction_step_id, 'P_RATE_BASIS_NAME')
877 ,hr_transaction_api.get_varchar2_value(p_transaction_step_id, 'P_CURRENCY_NAME')
878 ,l_assignment_id
879 ,hr_transaction_api.get_varchar2_value(p_transaction_step_id, 'P_CURRENCY_CODE')
880 ,hr_transaction_api.get_varchar2_value(p_transaction_step_id, 'P_VALUE')
881 ,hr_transaction_api.get_number_value(p_transaction_step_id, 'P_GRADE_RULE_ID')
882 ,null
883 ,hr_transaction_api.get_number_value(p_transaction_step_id, 'P_OBJECT_VERSION_NUMBER')
884 ,l_effective_date
885 ,hr_transaction_api.get_date_value(p_transaction_step_id, 'P_EFFECTIVE_END_DATE')
886 ,null
887 );
888
889 l_record_status := hr_transaction_api.get_varchar2_value(p_transaction_step_id, 'P_ASG_RATE_REC_STATUS');
890 validate_record
891 (p_validate => false
892 ,p_asg_rate_rec => l_asg_rate_rec
893 ,p_record_status => l_record_status
894 ,p_effective_date => l_effective_date
895 ,p_return_status => l_return_status
896 );
897 exception
898 when not_a_valid_po_txn then
899 hr_utility.set_message(800, 'HR_NOT_VALID_PO_TXN');
900 hr_utility.raise_error;
901 when others then
902 raise;
903 end process_api;
904
905 PROCEDURE process_save
906 (p_mode in VARCHAR2 default '#'
907 ,p_flow_mode in VARCHAR2 default NULL
908 ,p_item_type in VARCHAR2 default hr_api.g_varchar2
909 ,p_item_key in VARCHAR2 default hr_api.g_varchar2
910 ,p_activity_id in VARCHAR2 default hr_api.g_varchar2
911 ,p_effective_date_option in VARCHAR2 default hr_api.g_varchar2
912 ,p_asg_rate_tab in HR_ASG_RATE_TABLE
913 ,p_return_status out nocopy VARCHAR2
914 ,p_transaction_step_id out nocopy NUMBER
915 ) is
916
917 l_login_person_id NUMBER := NULL;
918 l_effective_date DATE;
919 l_transaction_id NUMBER := NULL;
920 l_transaction_step_id NUMBER := NULL;
921 l_return_status VARCHAR2(5) := 'S';
922 l_transaction_ovn NUMBER := NULL;
923 l_result VARCHAR2(100);
924 l_count NUMBER;
925 l_asg_rate_rec HR_ASG_RATE_TYPE;
926 l_record_status VARCHAR2(15) := g_no_change;
927 l_rec_old_end_date DATE := null;
928 l_validate BOOLEAN := true;
929
930 cursor csr_basetb_data(grade_id NUMBER, business_gp_id NUMBER,
931 asg_id NUMBER, effective_date date) is
932 select currency_code,
933 value,
934 effective_end_date
935 from pay_grade_rules_f pgr
936 where pgr.grade_rule_id = nvl(grade_id, -1)
937 and pgr.rate_type = 'A'
938 and pgr.business_group_id = business_gp_id
939 and pgr.grade_or_spinal_point_id = asg_id
940 and effective_date between pgr.effective_start_date
941 and pgr.effective_end_date;
942
943 l_current_rec csr_basetb_data%rowtype;
944 Begin
945 l_login_person_id := fnd_global.employee_id;
946 l_transaction_id :=
947 hr_transaction_ss.get_transaction_id(p_item_type ,p_item_key);
948 l_asg_rate_rec := p_asg_rate_tab(1);
949
950
951 if l_transaction_id is NULL
952 then
953 hr_transaction_ss.start_transaction
954 (itemtype => p_item_type
955 ,itemkey => p_item_key
956 ,actid => p_activity_id
957 ,funmode => 'RUN'
958 ,p_effective_date_option => p_effective_date_option
959 ,p_login_person_id => l_login_person_id
960 ,result => l_result
961 );
962 l_transaction_id :=
963 hr_transaction_ss.get_transaction_id(p_item_type ,p_item_key);
964 end if;
965
966 open csr_basetb_data(l_asg_rate_rec.grade_rule_id,
967 l_asg_rate_rec.business_group_id, l_asg_rate_rec.assignment_id,
968 l_asg_rate_rec.effective_start_date);
969 fetch csr_basetb_data into l_current_rec;
970 if csr_basetb_data%notfound
971 then
972 -- New Record - INSERT MODE
973 close csr_basetb_data;
974 if (l_asg_rate_rec.effective_end_date is not null and
975 (trunc(l_asg_rate_rec.effective_end_date) <> trunc(hr_api.g_eot)))
976 then
977 l_record_status := g_insert_delete;
978 else
979 l_record_status := g_insert_only;
980 end if;
981 else
982 -- Existing Record - UPDATE MODE
983 close csr_basetb_data;
984 if l_current_rec.value = l_asg_rate_rec.value and
985 l_current_rec.currency_code = l_asg_rate_rec.currency_code
986 then
987 if is_date_change_required(l_asg_rate_rec.effective_end_date,
988 l_current_rec.effective_end_date)
989 then
990 l_record_status := g_delete_only;
991 --if l_asg_rate_rec.effective_end_date is null
992 --then
993 -- l_asg_rate_rec.effective_end_date := hr_api.g_eot;
994 --end if;
995 end if;
996 else
997 if is_date_change_required(l_asg_rate_rec.effective_end_date,
998 l_current_rec.effective_end_date)
999 then
1000 l_record_status := g_update_delete;
1001 --if l_asg_rate_rec.effective_end_date is null
1002 --then
1003 -- l_asg_rate_rec.effective_end_date := hr_api.g_eot;
1004 --end if;
1005 else
1006 l_record_status := g_update_only;
1007 end if;
1008 end if;
1009 end if;
1010
1011 if l_record_status = g_no_change
1012 then
1013 if l_asg_rate_rec.transaction_step_id is not null
1014 then
1015 delete from hr_api_transaction_values
1016 where transaction_step_id = l_asg_rate_rec.transaction_step_id;
1017 delete from hr_api_transaction_steps
1018 where transaction_step_id = l_asg_rate_rec.transaction_step_id;
1019 end if;
1020 goto end_of_process;
1021 end if;
1022
1023
1024 begin
1025 if p_flow_mode is not null and
1026 p_flow_mode = hr_process_assignment_ss.g_new_hire_registration
1027 then
1028 savepoint newhire_point;
1029 hr_new_user_reg_ss.process_selected_transaction
1030 (p_item_type => p_item_type
1031 ,p_item_key => p_item_key);
1032 if (( hr_process_person_ss.g_assignment_id is not null) and
1033 (hr_process_person_ss.g_session_id= ICX_SEC.G_SESSION_ID))
1034 then
1035 -- Set the Assignment Id to the one just created, don't use the
1036 -- transaction table.
1037 l_asg_rate_rec.assignment_id := hr_process_person_ss.g_assignment_id;
1038 end if;
1039 end if;
1040 --For SFL no validation is required.
1041 if nvl(p_mode,'#') <> 'S'
1042 then
1043 validate_record
1044 (p_validate => l_validate
1045 ,p_asg_rate_rec => l_asg_rate_rec
1046 ,p_record_status => l_record_status
1047 ,p_effective_date => l_effective_date
1048 ,p_return_status => l_return_status
1049 );
1050 end if;
1051
1052 if p_flow_mode is not null and
1053 p_flow_mode = hr_process_assignment_ss.g_new_hire_registration
1054 then
1055 rollback to newhire_point;
1056 end if;
1057
1058 exception
1059 when others then
1060 -- Rollback dummy person in case of NewHire flow
1061 if p_flow_mode is not null and
1062 p_flow_mode = hr_process_assignment_ss.g_new_hire_registration
1063 then
1064 rollback to newhire_point;
1065 end if;
1066 end;
1067
1068 --Not saving to tx table if l_return_message <> 'S' and l_return_message
1069 --is set in the validate_record call
1070 l_transaction_step_id := l_asg_rate_rec.transaction_step_id;
1071 if l_return_status = 'S'
1072 then
1073 if l_transaction_step_id is NULL
1074 then
1075 hr_transaction_api.create_transaction_step
1076 (p_validate => false
1077 ,p_creator_person_id => l_login_person_id
1078 ,p_transaction_id => l_transaction_id
1079 ,p_api_name => g_package||'.PROCESS_API'
1080 ,p_item_type => p_item_type
1081 ,p_item_key => p_item_key
1082 ,p_activity_id => p_activity_id
1083 ,p_transaction_step_id => l_transaction_step_id
1084 ,p_object_version_number => l_transaction_ovn
1085 );
1086 end if;
1087 -- populating transaction table
1088
1089 l_count := 1;
1090 l_trans_tbl(l_count).param_name := 'P_BUSINESS_GROUP_ID';
1091 l_trans_tbl(l_count).param_value := l_asg_rate_rec.business_group_id;
1092 l_trans_tbl(l_count).param_data_type := 'NUMBER';
1093
1094 l_count := l_count+1;
1095 l_trans_tbl(l_count).param_name := 'P_RATE_NAME';
1096 l_trans_tbl(l_count).param_value := l_asg_rate_rec.rate_name;
1097 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
1098
1099 l_count := l_count+1;
1100 l_trans_tbl(l_count).param_name := 'P_RATE_ID';
1101 l_trans_tbl(l_count).param_value := l_asg_rate_rec.rate_id;
1102 l_trans_tbl(l_count).param_data_type := 'NUMBER';
1103
1104 l_count := l_count+1;
1105 l_trans_tbl(l_count).param_name := 'P_RATE_BASIS_NAME';
1106 l_trans_tbl(l_count).param_value := l_asg_rate_rec.rate_basis_name;
1107 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
1108
1109 l_count := l_count+1;
1110 l_trans_tbl(l_count).param_name := 'P_CURRENCY_NAME';
1111 l_trans_tbl(l_count).param_value := l_asg_rate_rec.currency_name;
1112 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
1113
1114 l_count := l_count+1;
1115 l_trans_tbl(l_count).param_name := 'P_ASSIGNMENT_ID';
1116 l_trans_tbl(l_count).param_value := l_asg_rate_rec.assignment_id;
1117 l_trans_tbl(l_count).param_data_type := 'NUMBER';
1118
1119 l_count := l_count+1;
1120 l_trans_tbl(l_count).param_name := 'P_CURRENCY_CODE';
1121 l_trans_tbl(l_count).param_value := l_asg_rate_rec.currency_code;
1122 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
1123
1124 l_count := l_count+1;
1125 l_trans_tbl(l_count).param_name := 'P_VALUE';
1126 l_trans_tbl(l_count).param_value := l_asg_rate_rec.value;
1127 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
1128
1129 l_count := l_count+1;
1130 l_trans_tbl(l_count).param_name := 'P_GRADE_RULE_ID';
1131 l_trans_tbl(l_count).param_value := l_asg_rate_rec.grade_rule_id;
1132 l_trans_tbl(l_count).param_data_type := 'NUMBER';
1133
1134 l_count := l_count+1;
1135 l_trans_tbl(l_count).param_name := 'P_OBJECT_VERSION_NUMBER';
1136 l_trans_tbl(l_count).param_value :=
1137 l_asg_rate_rec.object_version_number;
1138 l_trans_tbl(l_count).param_data_type := 'NUMBER';
1139
1140 l_count := l_count+1;
1141 l_trans_tbl(l_count).param_name := 'P_EFFECTIVE_START_DATE';
1142 l_trans_tbl(l_count).param_value :=
1143 to_char(l_asg_rate_rec.effective_start_date,hr_transaction_ss.g_date_format);
1144 l_trans_tbl(l_count).param_data_type := 'DATE';
1145
1146 l_count := l_count+1;
1147 l_trans_tbl(l_count).param_name := 'P_EFFECTIVE_END_DATE';
1148 l_trans_tbl(l_count).param_value :=
1149 to_char(l_asg_rate_rec.effective_end_date,hr_transaction_ss.g_date_format);
1150 l_trans_tbl(l_count).param_data_type := 'DATE';
1151
1152 l_count := l_count+1;
1153 l_trans_tbl(l_count).param_name := 'P_REVIEW_PROC_CALL';
1154 begin
1155 l_trans_tbl(l_count).param_value :=
1156 wf_engine.GetActivityAttrText(p_item_type,p_item_key,
1157 p_activity_id, 'HR_REVIEW_REGION_ITEM', False);
1158 exception
1159 when others then
1160 l_trans_tbl(l_count).param_value := 'HrAssignmentRate';
1161 end;
1162 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
1163
1164 l_count := l_count+1;
1165 l_trans_tbl(l_count).param_name := 'P_REVIEW_ACTID';
1166 l_trans_tbl(l_count).param_value := p_activity_id;
1167 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
1168
1169 l_count := l_count+1;
1170 l_trans_tbl(l_count).param_name := 'P_ASG_RATE_REC_STATUS';
1171 l_trans_tbl(l_count).param_value := l_record_status;
1172 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
1173
1174
1175 hr_transaction_ss.save_transaction_step
1176 (p_item_type => p_item_type
1177 ,p_item_key => p_item_key
1178 ,p_actid => p_activity_id
1179 ,p_login_person_id => l_login_person_id
1180 ,p_transaction_step_id => l_transaction_step_id
1181 ,p_api_name => g_package||'.PROCESS_API'
1182 ,p_transaction_data => l_trans_tbl
1183 );
1184 p_transaction_step_id := l_transaction_step_id;
1185 end if;
1186 <<end_of_process>>
1187 p_return_status := l_return_status;
1188 Exception
1189 When others then
1190 p_return_status := 'E';
1191 p_transaction_step_id := null;
1192 End process_save;
1193
1194 PROCEDURE po_process_save
1195 (p_mode in VARCHAR2 default '#'
1196 ,p_flow_mode in VARCHAR2 default NULL
1197 ,p_item_type in VARCHAR2 default hr_api.g_varchar2
1198 ,p_item_key in VARCHAR2 default hr_api.g_varchar2
1199 ,p_activity_id in VARCHAR2 default hr_api.g_varchar2
1200 ,p_effective_date_option in VARCHAR2 default hr_api.g_varchar2
1201 ,p_po_line_id in NUMBER
1202 ,p_return_status out nocopy VARCHAR2
1203 ,p_transaction_step_id out nocopy NUMBER
1204 ) is
1205
1206 l_login_person_id NUMBER := NULL;
1207 l_effective_date DATE;
1208 l_transaction_id NUMBER := NULL;
1209 l_transaction_step_id NUMBER := NULL;
1210 l_return_status VARCHAR2(5) := 'S';
1211 l_transaction_ovn NUMBER := NULL;
1212 l_result VARCHAR2(100);
1213 l_count NUMBER;
1214 l_asg_rate_rec HR_ASG_RATE_TYPE;
1215 l_record_status VARCHAR2(15) := g_no_change;
1216 l_rec_old_end_date DATE := null;
1217 l_validate BOOLEAN := true;
1218
1219
1220 Begin
1221 l_login_person_id := fnd_global.employee_id;
1222 l_transaction_id :=
1223 hr_transaction_ss.get_transaction_id(p_item_type ,p_item_key);
1224
1225 if l_transaction_id is NULL
1226 then
1227 hr_transaction_ss.start_transaction
1228 (itemtype => p_item_type
1229 ,itemkey => p_item_key
1230 ,actid => p_activity_id
1231 ,funmode => 'RUN'
1232 ,p_effective_date_option => p_effective_date_option
1233 ,p_login_person_id => l_login_person_id
1234 ,result => l_result
1235 );
1236 l_transaction_id :=
1237 hr_transaction_ss.get_transaction_id(p_item_type ,p_item_key);
1238 end if;
1239
1240
1241
1242 begin
1243 if p_flow_mode is not null and
1244 p_flow_mode = hr_process_assignment_ss.g_new_hire_registration
1245 then
1246 savepoint newhire_point;
1247 hr_new_user_reg_ss.process_selected_transaction
1248 (p_item_type => p_item_type
1249 ,p_item_key => p_item_key);
1250 if (( hr_process_person_ss.g_assignment_id is not null) and
1251 (hr_process_person_ss.g_session_id= ICX_SEC.G_SESSION_ID))
1252 then
1253 -- Set the Assignment Id to the one just created, don't use the
1254 -- transaction table.
1255 l_asg_rate_rec.assignment_id := hr_process_person_ss.g_assignment_id;
1256 end if;
1257 end if;
1258
1259 if p_flow_mode is not null and
1260 p_flow_mode = hr_process_assignment_ss.g_new_hire_registration
1261 then
1262 rollback to newhire_point;
1263 end if;
1264
1265 exception
1266 when others then
1267 -- Rollback dummy person in case of NewHire flow
1268 if p_flow_mode is not null and
1269 p_flow_mode = hr_process_assignment_ss.g_new_hire_registration
1270 then
1271 rollback to newhire_point;
1272 end if;
1273 end;
1274
1275 --Not saving to tx table if l_return_message <> 'S' and l_return_message
1276 --is set in the validate_record call
1277 hr_transaction_api.create_transaction_step
1278 (p_validate => false
1279 ,p_creator_person_id => l_login_person_id
1280 ,p_transaction_id => l_transaction_id
1281 ,p_api_name => g_package||'.PROCESS_API'
1282 ,p_item_type => p_item_type
1283 ,p_item_key => p_item_key
1284 ,p_activity_id => p_activity_id
1285 ,p_transaction_step_id => l_transaction_step_id
1286 ,p_object_version_number => l_transaction_ovn
1287 );
1288 -- populating transaction table
1289 -- to be changed
1290 -- may not need assignment_id, po_header_id in the values
1291
1292 l_count := 1;
1293 l_trans_tbl(l_count).param_name := 'P_PO_LINE_ID';
1294 l_trans_tbl(l_count).param_value := p_po_line_id;
1295 l_trans_tbl(l_count).param_data_type := 'NUMBER';
1296
1297
1298 l_count := l_count+1;
1299 l_trans_tbl(l_count).param_name := 'P_REVIEW_PROC_CALL';
1300 l_trans_tbl(l_count).param_value := 'POAsgnRatesRN';
1301 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
1302 /*
1303 begin
1304 l_trans_tbl(l_count).param_value :=
1305 wf_engine.GetActivityAttrText(p_item_type,p_item_key,
1306 p_activity_id, 'HR_REVIEW_REGION_ITEM', False);
1307 exception
1308 when others then
1309 l_trans_tbl(l_count).param_value := 'HrPOAssignmentRate';
1310 end;
1311 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
1312 */
1313 l_count := l_count+1;
1314 l_trans_tbl(l_count).param_name := 'P_REVIEW_ACTID';
1315 l_trans_tbl(l_count).param_value := p_activity_id;
1316 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
1317
1318 hr_transaction_ss.save_transaction_step
1319 (p_item_type => p_item_type
1320 ,p_item_key => p_item_key
1321 ,p_actid => p_activity_id
1322 ,p_login_person_id => l_login_person_id
1323 ,p_transaction_step_id => l_transaction_step_id
1324 ,p_api_name => g_package||'.PROCESS_API'
1325 ,p_transaction_data => l_trans_tbl
1326 );
1327 p_transaction_step_id := l_transaction_step_id;
1328 <<end_of_process>>
1329 p_return_status := l_return_status;
1330 Exception
1331 When others then
1332 p_return_status := 'E';
1333 p_transaction_step_id := null;
1334 End po_process_save;
1335
1336
1337
1338 PROCEDURE get_transaction_rownum
1339 (p_item_type in VARCHAR2
1340 ,p_item_key in VARCHAR2
1341 ,p_assignment_id in VARCHAR2
1342 ,p_business_gp_id in VARCHAR2
1343 ,p_row_num out nocopy VARCHAR2
1344 ) is
1345
1346 l_row_num NUMBER := 0;
1347 Begin
1348 populate_transaction_details
1349 (p_item_type => p_item_type
1350 ,p_item_key => p_item_key
1351 ,p_assignment_id => p_assignment_id
1352 ,p_business_gp_id => p_business_gp_id
1353 );
1354 p_row_num := to_char(g_asg_rate_table.count);
1355 Exception
1356 When others then
1357 p_row_num := null;
1358 End;
1359
1360 PROCEDURE populate_transaction_details
1361 (p_item_type in VARCHAR2
1362 ,p_item_key in VARCHAR2
1363 ,p_assignment_id in VARCHAR2
1364 ,p_business_gp_id in VARCHAR2
1365 ) is
1366 cursor csr_asg_rate_tx is
1367 SELECT oo.*,
1368 rownum row_index
1369 FROM (
1370 SELECT a.varchar2_value rate_name,
1371 b.number_value rate_id,
1372 c.varchar2_value rate_basis_name,
1373 d.varchar2_value currency_name,
1374 e.varchar2_value value,
1375 f.date_value effective_start_date,
1376 decode(trunc(g.date_value), trunc(hr_api.g_eot), null, g.date_value) effective_end_date,
1377 h.number_value object_version_number,
1378 i.number_value grade_rule_id,
1379 j.varchar2_value currency_code,
1380 to_char(s.transaction_step_id) transaction_step_id
1381 FROM hr_api_transaction_steps s,
1382 hr_api_transaction_values a, hr_api_transaction_values b,
1383 hr_api_transaction_values c, hr_api_transaction_values d,
1384 hr_api_transaction_values e, hr_api_transaction_values f,
1385 hr_api_transaction_values g, hr_api_transaction_values h,
1386 hr_api_transaction_values i, hr_api_transaction_values j
1387 WHERE s.item_type = p_item_type
1388 AND s.item_key = p_item_key
1389 AND s.api_name = g_package||'.PROCESS_API'
1390 AND a.transaction_step_id = s.transaction_step_id
1391 AND a.name = 'P_RATE_NAME'
1392 AND b.transaction_step_id = s.transaction_step_id
1393 AND b.name = 'P_RATE_ID'
1394 AND c.transaction_step_id = s.transaction_step_id
1395 AND c.name = 'P_RATE_BASIS_NAME'
1396 AND d.transaction_step_id = s.transaction_step_id
1397 AND d.name = 'P_CURRENCY_NAME'
1398 AND e.transaction_step_id = s.transaction_step_id
1399 AND e.name = 'P_VALUE'
1400 AND f.transaction_step_id = s.transaction_step_id
1401 AND f.name = 'P_EFFECTIVE_START_DATE'
1402 AND g.transaction_step_id = s.transaction_step_id
1403 AND g.name = 'P_EFFECTIVE_END_DATE'
1404 AND h.transaction_step_id = s.transaction_step_id
1405 AND h.name = 'P_OBJECT_VERSION_NUMBER'
1406 AND i.transaction_step_id = s.transaction_step_id
1407 AND i.name = 'P_GRADE_RULE_ID'
1408 AND j.transaction_step_id = s.transaction_step_id
1409 AND j.name = 'P_CURRENCY_CODE'
1410
1411 UNION
1412
1413 SELECT o.* from (
1414 SELECT pgr.rate_name,
1415 pgr.rate_id,
1416 pgr.rate_basis_name,
1417 pgr.currency_name,
1418 pgr.value,
1419 pgr.effective_start_date,
1420 decode(trunc(pgr.effective_end_date), trunc(hr_api.g_eot), null, pgr.effective_end_date) effective_end_date,
1421 pgr.object_version_number,
1422 pgr.grade_rule_id,
1423 pgr.currency_code,
1424 NULL transaction_step_id
1425 FROM PAY_GRADE_RULES_V pgr
1426 WHERE pgr.rate_type = 'A'
1427 AND pgr.assignment_id = to_number(p_assignment_id)
1428 AND pgr.business_group_id = to_number(p_business_gp_id)
1429 AND pgr.grade_rule_id NOT IN (SELECT nvl(a.number_value, -1)
1430 FROM hr_api_transaction_steps s,
1431 hr_api_transaction_values a
1432 WHERE s.item_type = p_item_type
1433 AND s.item_key = p_item_key
1434 AND s.api_name = g_package||'.PROCESS_API'
1435 AND a.transaction_step_id = s.transaction_step_id
1436 AND a.name = 'P_GRADE_RULE_ID')
1437 ORDER BY pgr.rate_name ) o
1438 ) oo
1439 ORDER BY grade_rule_id desc;
1440 i NUMBER := 1;
1441 begin
1442 g_asg_rate_table := HR_ASG_RATE_TABLE();
1443 for c1 in csr_asg_rate_tx
1444 loop
1445 g_asg_rate_table.extend;
1446 g_asg_rate_table(i) := HR_ASG_RATE_TYPE(null, null, null, null, null,
1447 null, null, null, null, null, null, null, null, null);
1448 g_asg_rate_table(i).business_group_id := p_business_gp_id;
1449 g_asg_rate_table(i).rate_name := c1.rate_name;
1450 g_asg_rate_table(i).rate_id := c1.rate_id;
1451 g_asg_rate_table(i).rate_basis_name := c1.rate_basis_name;
1452 g_asg_rate_table(i).currency_name := c1.currency_name;
1453 g_asg_rate_table(i).assignment_id := p_assignment_id;
1454 g_asg_rate_table(i).currency_code := c1.currency_code;
1455 g_asg_rate_table(i).value := c1.value;
1456 g_asg_rate_table(i).grade_rule_id := c1.grade_rule_id;
1457 g_asg_rate_table(i).transaction_step_id := c1.transaction_step_id;
1458 g_asg_rate_table(i).object_version_number := c1.object_version_number;
1459 g_asg_rate_table(i).effective_start_date := c1.effective_start_date;
1460 g_asg_rate_table(i).effective_end_date := c1.effective_end_date;
1461 g_asg_rate_table(i).row_index := c1.row_index;
1462 i := i + 1;
1463 end loop;
1464
1465 end populate_transaction_details;
1466
1467 PROCEDURE get_transaction_details
1468 (p_asg_rate_table in out nocopy HR_ASG_RATE_TABLE
1469 ) is
1470 l_asg_rate_table HR_ASG_RATE_TABLE := null;
1471 begin
1472 l_asg_rate_table := p_asg_rate_table;
1473 p_asg_rate_table := g_asg_rate_table;
1474 g_asg_rate_table.delete;
1475 exception
1476 when others then
1477 p_asg_rate_table := l_asg_rate_table;
1478 end get_transaction_details;
1479
1480 /**
1481 *
1482 */
1483 PROCEDURE validate_record
1484 (p_validate in boolean Default true
1485 ,p_asg_rate_rec in HR_ASG_RATE_TYPE
1486 ,p_record_status in VARCHAR2
1487 ,p_effective_date in date
1488 ,p_return_status out nocopy VARCHAR2
1489 ) is
1490
1491 cursor csr_asg_rate_date(l_grade_rule_id IN NUMBER) is
1492 select effective_start_date,
1493 effective_end_date
1494 from pay_grade_rules_v
1495 where grade_rule_id = l_grade_rule_id;
1496
1497
1498 l_rec_start_date date := null;
1499 l_rec_end_date date := null;
1500 l_rec_update_mode VARCHAR2(15) := 'UPDATE';
1501 l_object_version_number NUMBER;
1502 l_effective_start_date DATE;
1503 l_effective_end_date DATE;
1504 l_grade_rule_id NUMBER;
1505 l_validate BOOLEAN := true;
1506 l_return_status VARCHAR2(5) := 'E';
1507 l_validate_exception exception;
1508 l_temp boolean;
1509 Begin
1510 l_object_version_number := p_asg_rate_rec.object_version_number;
1511 l_grade_rule_id := p_asg_rate_rec.grade_rule_id;
1512 if (p_record_status = g_update_delete or
1513 p_record_status = g_insert_delete or
1514 p_record_status = g_delete_only)
1515 then
1516 hr_multi_message.enable_message_list;
1517 if trunc(p_asg_rate_rec.effective_start_date) > trunc(nvl(p_asg_rate_rec.effective_end_date, hr_api.g_eot))
1518 then
1519 hr_utility.set_message(800, 'HR_ASG_RATE_INV_END_DATE');
1520 l_temp := hr_multi_message.exception_add
1521 (p_associated_column1 => 'PAY_GRADE_RULES_F.EFFECTIVE_END_DATE');
1522 end if;
1523 hr_multi_message.end_validation_set;
1524 end if;
1525
1526 if l_grade_rule_id is not null
1527 then
1528 open csr_asg_rate_date(l_grade_rule_id);
1529 fetch csr_asg_rate_date into l_rec_start_date, l_rec_end_date;
1530 close csr_asg_rate_date;
1531 end if;
1532 l_validate := p_validate;
1533
1534 if (p_record_status = g_update_delete or
1535 p_record_status = g_insert_delete)
1536 then
1537 l_validate := false;
1538 savepoint record_enddate_enabled;
1539 end if;
1540
1541 if (p_record_status = g_insert_only or
1542 p_record_status = g_insert_delete)
1543 then
1544 create_assignment_rate_value
1545 (p_validate => l_validate
1546 ,p_effective_date => p_asg_rate_rec.effective_start_date
1547 ,p_business_group_id => p_asg_rate_rec.business_group_id
1548 ,p_rate_id => p_asg_rate_rec.rate_id
1549 ,p_assignment_id => p_asg_rate_rec.assignment_id
1550 ,p_rate_type => 'A'
1551 ,p_currency_code => p_asg_rate_rec.currency_code
1552 ,p_value => p_asg_rate_rec.value
1553 ,p_grade_rule_id => l_grade_rule_id
1554 ,p_object_version_number => l_object_version_number
1555 ,p_effective_start_date => l_effective_start_date
1556 ,p_effective_end_date => l_effective_end_date
1557 ,p_return_status => l_return_status
1558 );
1559 if l_return_status = 'E'
1560 then
1561 raise l_validate_exception;
1562 end if;
1563 elsif (p_record_status = g_update_delete or
1564 p_record_status = g_update_only)
1565 then
1566 if (l_rec_start_date is not null and
1567 trunc(l_rec_start_date)
1568 = trunc(p_asg_rate_rec.effective_start_date))
1569 then
1570 l_rec_update_mode := 'CORRECTION';
1571 end if;
1572 update_assignment_rate_value
1573 (p_validate => l_validate
1574 ,p_grade_rule_id => l_grade_rule_id
1575 ,p_effective_date => p_asg_rate_rec.effective_start_date
1576 ,p_datetrack_mode => l_rec_update_mode
1577 ,p_currency_code => p_asg_rate_rec.currency_code
1578 ,p_value => p_asg_rate_rec.value
1579 ,p_object_version_number => l_object_version_number
1580 ,p_effective_start_date => l_effective_start_date
1581 ,p_effective_end_date => l_effective_end_date
1582 ,p_return_status => l_return_status
1583 );
1584 if l_return_status = 'E'
1585 then
1586 raise l_validate_exception;
1587 end if;
1588 end if;
1589
1590 if (p_record_status = g_update_delete or
1591 p_record_status = g_insert_delete or
1592 p_record_status = g_delete_only)
1593 then
1594 begin
1595 savepoint record_delete_point;
1596 if trunc(l_rec_end_date) <> trunc(hr_api.g_eot)
1597 then
1598 delete_rate_value
1599 (p_validate => l_validate
1600 ,p_grade_rule_id => l_grade_rule_id
1601 ,p_datetrack_mode => 'FUTURE_CHANGE'
1602 ,p_effective_date => p_asg_rate_rec.effective_start_date
1603 ,p_object_version_number => l_object_version_number
1604 ,p_effective_start_date => l_effective_start_date
1605 ,p_effective_end_date => l_effective_end_date
1606 ,p_return_status => l_return_status
1607 );
1608 if l_return_status = 'E'
1609 then
1610 raise l_validate_exception;
1611 end if;
1612 end if;
1613 if p_asg_rate_rec.effective_end_date is not null
1614 then
1615 delete_rate_value
1616 (p_validate => l_validate
1617 ,p_grade_rule_id => l_grade_rule_id
1618 ,p_datetrack_mode => 'DELETE'
1619 ,p_effective_date => p_asg_rate_rec.effective_end_date
1620 ,p_object_version_number => l_object_version_number
1621 ,p_effective_start_date => l_effective_start_date
1622 ,p_effective_end_date => l_effective_end_date
1623 ,p_return_status => l_return_status
1624 );
1625 end if;
1626 if l_return_status = 'E'
1627 then
1628 raise l_validate_exception;
1629 end if;
1630 if p_validate = true
1631 then
1632 rollback to record_delete_point;
1633 end if;
1634 exception
1635 when others then
1636 rollback to record_delete_point;
1637 raise;
1638 end;
1639 end if;
1640
1641 if (p_record_status = g_update_delete or
1642 p_record_status = g_insert_delete)
1643 then
1644 if p_validate = true
1645 then
1646 rollback to record_enddate_enabled;
1647 end if;
1648 end if;
1649
1650 p_return_status := l_return_status;
1651 exception
1652 when hr_multi_message.error_message_exist then
1653 p_return_status := hr_multi_message.get_return_status_disable;
1654 when others then
1655 if (p_record_status = g_update_delete or
1656 p_record_status = g_insert_delete)
1657 then
1658 rollback to record_enddate_enabled;
1659 end if;
1660 p_return_status := 'E';
1661 end validate_record;
1662
1663 PROCEDURE delete_transaction_step
1664 (p_transaction_step_id in VARCHAR2
1665 ) is
1666 begin
1667 delete from hr_api_transaction_values
1668 where transaction_step_id = p_transaction_step_id;
1669 delete from hr_api_transaction_steps
1670 where transaction_step_id = p_transaction_step_id;
1671 end delete_transaction_step;
1672
1673 FUNCTION is_date_change_required
1674 (p_new_date in DATE
1675 ,p_old_date in DATE
1676 ) return boolean is
1677 l_date_change_status boolean;
1678 begin
1679 if p_new_date is null
1680 then
1681 if trunc(p_old_date) = trunc(hr_api.g_eot)
1682 then
1683 l_date_change_status := false;
1684 else
1685 l_date_change_status := true; -- in this case defaulting to hr_api.g_eot
1686 end if;
1687 else
1688 if trunc(p_new_date) = trunc(hr_api.g_eot)
1689 then
1690 if trunc(p_old_date) = trunc(hr_api.g_eot)
1691 then
1692 l_date_change_status := false;
1693 else
1694 l_date_change_status := true;
1695 end if;
1696 else
1697 if trunc(p_old_date) = trunc(p_new_date)
1698 then
1699 l_date_change_status := false;
1700 else
1701 l_date_change_status := true;
1702 end if;
1703 end if;
1704 end if;
1705 return l_date_change_status;
1706 end is_date_change_required;
1707
1708 end hr_rate_values_swi;