[Home] [Help]
PACKAGE BODY: APPS.PER_REFRESH_POSITION
Source
1 PACKAGE BODY PER_REFRESH_POSITION AS
2 /* $Header: hrpsfref.pkb 120.1 2005/08/12 16:27:02 hsajja noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(100) := ' per_refresh_position.'; -- Global package name
9 gl_pos_ovn number ; -- Internal holder for pos ovn
10 --
11 --
12 --
13 -- ----------------------------------------------------------------------------
14 -- |------------------------< set_copied_flag >------------------------|
15 -- ----------------------------------------------------------------------------
16 --
17 procedure set_copied_flag ( p_row in rowid
18 ,p_position_id in number
19 ,p_effective_date in date
20 ,p_date_effective in date
21 ,p_object_version_number in out nocopy number)
22 is
23 --
24 l_proc varchar2(100) := g_package||'set_copied_flag';
25 l_validation_start_date date;
26 l_validation_end_date date;
27 l_effective_start_date date;
28 l_effective_end_date date;
29 --
30 l_object_version_number number := p_object_version_number;
31 --
32 begin
33 --
34 hr_utility.set_location('Entering : '||l_proc, 10);
35 --
36 if p_row is not null then
37 --
38 hr_utility.set_location(l_proc||' lock date track pos table call '||p_object_version_number, 20);
39 --
40 hr_psf_shd.lck(p_effective_date => p_effective_date
41 , p_datetrack_mode => 'CORRECTION'
42 , p_position_id => p_position_id
43 , p_object_version_number => p_object_version_number
44 , p_validation_start_date => l_validation_start_date
45 , p_validation_end_date => l_validation_end_date );
46 --
47 hr_utility.set_location(l_proc||' updt date track pos table call ', 20);
48 --
49 hr_psf_upd.upd(p_position_id => p_position_id
50 , p_effective_start_date => l_effective_start_date
51 , p_effective_end_date => l_effective_end_date
52 , p_copied_to_old_table_flag => 'Y'
53 , p_object_version_number => p_object_version_number
54 , p_effective_date => p_effective_date
55 , p_date_effective => p_date_effective
56 , p_datetrack_mode => 'CORRECTION');
57 --
58 hr_utility.set_location(l_proc||' after upd date track pos ovn: '||p_object_version_number,20);
59 --
60 else
61 --
62 hr_utility.set_location(l_proc, 30);
63 --
64 rollback to refresh;
65 end if;
66 --
67 hr_utility.set_location(l_proc, 40);
68 --
69 exception
70 when others then
71 p_object_version_number := l_object_version_number;
72 hr_utility.set_location(l_proc, 50);
73 raise;
74 end set_copied_flag;
75 --
76 -- ----------------------------------------------------------------------------
77 -- |------------------------< check_position_table >------------------------|
78 -- ----------------------------------------------------------------------------
79 --
80 function check_position_table(p_position_id in varchar2
81 , p_object_version_number out nocopy number)
82 return boolean
83 is
84 --
85 l_proc varchar2(100) := g_package||'check_position_table';
86 --
87 l_object_version_number number := p_object_version_number;
88 --
89 cursor c_ovn(p_position_id number) is
90 select object_version_number
91 from per_all_positions
92 where position_id = p_position_id;
93 begin
94 --
95 hr_utility.set_location('Entering : '||l_proc, 10);
96 --
97 open c_ovn(p_position_id);
98 fetch c_ovn into p_object_version_number;
99 --
100 if c_ovn%found then
101 -- if record found then return true
102 return(true);
103 else
104 return(false);
105 end if; --c_ovn%found
106 close c_ovn;
107 --
108 exception
109 when others then
110 p_object_version_number := l_object_version_number;
111 hr_utility.set_location(l_proc, 20);
112 raise;
113 end check_position_table;
114 -- ----------------------------------------------------------------------------
115 -- |------------------------< refresh_all_position >------------------------|
116 -- ----------------------------------------------------------------------------
117 --
118 procedure refresh_all_position ( errbuf out nocopy varchar2
119 , retcode out nocopy number
120 , p_refresh_date date default trunc(sysdate))
121 is
122 l_position_id number := '';
123 l_effective_date date := '';
124 l_object_version_number number := '';
125 begin
126 -- calling the refresh_position with position_id.
127 --
128 hr_utility.set_location('Entering:'||g_package||'refresh_all_position', 5);
129 --
130 refresh_position ( p_refresh_date => p_refresh_date
131 , p_position_id => l_position_id
132 , p_effective_date => l_effective_date
133 , p_object_version_number => l_object_version_number
134 , errbuf => errbuf
135 , retcode => retcode );
136 --
137 hr_utility.set_location('Leaving:'||g_package||'refresh_all_position', 500);
138 --
139 end refresh_all_position;
140 --
141 -- ----------------------------------------------------------------------------
142 -- |------------------------< refresh_position >------------------------|
143 -- ----------------------------------------------------------------------------
144 --
145 procedure refresh_position ( p_refresh_date date
146 ,p_position_id number default null
147 ,p_effective_date date
148 ,p_full_hr varchar2 default 'Y'
149 ,p_object_version_number in out nocopy number
150 ,errbuf out nocopy varchar2
151 ,retcode out nocopy varchar2) is
152 --
153 l_proc varchar2(100) := g_package||'refresh_position';
154 l_position_id number := -99.99;
155 l_request_id number;
156 l_program_application_id number;
157 l_program_id number;
158 l_program_update_date date;
159 l_rowid rowid;
160 -- out params
161 l_object_version_number number(15);
162 --
163 -- PMFLETCH p_object_version_number is not set until the end of processing
164 -- **within the cursor loop** . It can therefore be set so multiple different
165 -- values so we need to store the incoming value and reset it on exception.
166 l_original_object_version_num number(15) := p_object_version_number;
167 -- position cursor
168 cursor c_pos(p_position_id number) is
169 select rowid
170 , position_id
171 , effective_start_date
172 , effective_end_date
173 , availability_status_id
174 , business_group_id
175 , entry_step_id
176 , job_id
177 , location_id
178 , organization_id
179 , pay_freq_payroll_id
180 , position_definition_id
181 , position_transaction_id
182 , prior_position_id
183 , relief_position_id
184 , successor_position_id
185 , supervisor_position_id
186 , amendment_date
187 , amendment_recommendation
188 , amendment_ref_number
189 , bargaining_unit_cd
190 , comments
191 , current_job_prop_end_date
192 , current_org_prop_end_date
193 , avail_status_prop_end_date
194 , date_effective
195 , hr_general.get_position_date_end(position_id) date_end
196 , earliest_hire_date
197 , fill_by_date
198 , frequency
199 , fte
200 , max_persons
201 , hr_general.decode_position_latest_name(position_id) name
202 , overlap_period
203 , overlap_unit_cd
204 , pay_term_end_day_cd
205 , pay_term_end_month_cd
206 , permanent_temporary_flag
207 , permit_recruitment_flag
208 , position_type
209 , posting_description
210 , probation_period
211 , probation_period_unit_cd
212 , replacement_required_flag
213 , review_flag
214 , seasonal_flag
215 , security_requirements
216 , status
217 , term_start_day_cd
218 , term_start_month_cd
219 , time_normal_finish
220 , time_normal_start
221 , update_source_cd
222 , working_hours
223 , works_council_approval_flag
224 , work_period_type_cd
225 , work_term_end_day_cd
226 , work_term_end_month_cd
227 , information1
228 , information2
229 , information3
230 , information4
231 , information5
232 , information6
233 , information7
234 , information8
235 , information9
236 , information10
237 , information11
238 , information12
239 , information13
240 , information14
241 , information15
242 , information16
243 , information17
244 , information18
245 , information19
246 , information20
247 , information21
248 , information22
249 , information23
250 , information24
251 , information25
252 , information26
253 , information27
254 , information28
255 , information29
256 , information30
257 , information_category
258 , attribute1
259 , attribute2
260 , attribute3
261 , attribute4
262 , attribute5
263 , attribute6
264 , attribute7
265 , attribute8
266 , attribute9
267 , attribute10
268 , attribute11
269 , attribute12
270 , attribute13
271 , attribute14
272 , attribute15
273 , attribute16
274 , attribute17
275 , attribute18
276 , attribute19
277 , attribute20
278 , attribute21
279 , attribute22
280 , attribute23
281 , attribute24
282 , attribute25
283 , attribute26
284 , attribute27
285 , attribute28
286 , attribute29
287 , attribute30
288 , attribute_category
289 , request_id
290 , program_application_id
291 , program_id
292 , program_update_date
293 , created_by
294 , creation_date
295 , last_updated_by
296 , last_update_date
297 , last_update_login
298 , object_version_number
299 , entry_grade_id
300 , entry_grade_rule_id
301 , proposed_fte_for_layoff
302 , proposed_date_for_layoff
303 , pay_basis_id
304 , supervisor_id
305 , copied_to_old_table_flag
306 from hr_all_positions_f
307 where (copied_to_old_table_flag <> 'Y'
308 or copied_to_old_table_flag is null )
309 --and effective_start_date <= p_refresh_date
310 and position_id = p_position_id
311 order by position_id, effective_start_date desc;
312 --
313 --
314 --
315 cursor c_all_pos is
316 select rowid
317 , position_id
318 , effective_start_date
319 , effective_end_date
320 , availability_status_id
321 , business_group_id
322 , entry_step_id
323 , job_id
324 , location_id
325 , organization_id
326 , pay_freq_payroll_id
327 , position_definition_id
328 , position_transaction_id
329 , prior_position_id
330 , relief_position_id
331 , successor_position_id
332 , supervisor_position_id
333 , amendment_date
334 , amendment_recommendation
335 , amendment_ref_number
336 , bargaining_unit_cd
337 , comments
338 , current_job_prop_end_date
339 , current_org_prop_end_date
340 , avail_status_prop_end_date
341 , date_effective
342 , hr_general.get_position_date_end(position_id) date_end
343 , earliest_hire_date
344 , fill_by_date
345 , frequency
346 , fte
347 , max_persons
348 , hr_general.decode_position_latest_name(position_id) name
349 , overlap_period
350 , overlap_unit_cd
351 , pay_term_end_day_cd
352 , pay_term_end_month_cd
353 , permanent_temporary_flag
354 , permit_recruitment_flag
355 , position_type
356 , posting_description
357 , probation_period
358 , probation_period_unit_cd
359 , replacement_required_flag
360 , review_flag
361 , seasonal_flag
362 , security_requirements
363 , status
364 , term_start_day_cd
365 , term_start_month_cd
366 , time_normal_finish
367 , time_normal_start
368 , update_source_cd
369 , working_hours
370 , works_council_approval_flag
371 , work_period_type_cd
372 , work_term_end_day_cd
373 , work_term_end_month_cd
374 , information1
375 , information2
376 , information3
377 , information4
378 , information5
379 , information6
380 , information7
381 , information8
382 , information9
383 , information10
384 , information11
385 , information12
386 , information13
387 , information14
388 , information15
389 , information16
390 , information17
391 , information18
392 , information19
393 , information20
394 , information21
395 , information22
396 , information23
397 , information24
398 , information25
399 , information26
400 , information27
401 , information28
402 , information29
403 , information30
404 , information_category
405 , attribute1
406 , attribute2
407 , attribute3
408 , attribute4
409 , attribute5
410 , attribute6
411 , attribute7
412 , attribute8
413 , attribute9
414 , attribute10
415 , attribute11
416 , attribute12
417 , attribute13
418 , attribute14
419 , attribute15
420 , attribute16
421 , attribute17
422 , attribute18
423 , attribute19
424 , attribute20
425 , attribute21
426 , attribute22
427 , attribute23
428 , attribute24
429 , attribute25
430 , attribute26
431 , attribute27
432 , attribute28
433 , attribute29
434 , attribute30
435 , attribute_category
436 , request_id
437 , program_application_id
438 , program_id
439 , program_update_date
440 , created_by
441 , creation_date
442 , last_updated_by
443 , last_update_date
444 , last_update_login
445 , object_version_number
446 , entry_grade_id
447 , entry_grade_rule_id
448 , proposed_fte_for_layoff
449 , proposed_date_for_layoff
450 , pay_basis_id
451 , supervisor_id
452 , copied_to_old_table_flag
453 from hr_all_positions_f
454 where (copied_to_old_table_flag <> 'Y'
455 or copied_to_old_table_flag is null )
456 --and effective_start_date <= p_refresh_date
457 order by position_id, effective_start_date desc;
458 --
459 rec c_pos%rowtype;
460 ---
461 BEGIN
462 --
463 hr_utility.set_location('Entering:'||l_proc, 5);
464 --
465 per_refresh_position.refreshing_position := true;
466 --
467 hr_utility.set_location(l_proc||' p_refresh_date ' || p_refresh_date, 5);
468 --
469 if p_position_id is not null then
470 open c_pos(p_position_id);
471 else
472 open c_all_pos;
473 end if;
474
475 loop
476 if p_position_id is not null then
477 fetch c_pos into rec;
478 exit when c_pos%NOTFOUND;
479 else
480 fetch c_all_pos into rec;
481 exit when c_all_pos%NOTFOUND;
482 end if;
483 --
484 savepoint refresh;
485 --
486 -- (reset gl variable )
487 --
488 gl_pos_ovn := '' ;
489 --
490 hr_utility.set_location(l_proc||' POSITION ID ' || rec.position_id, 10);
491 --
492 if rec.position_id <> l_position_id then
493 --
494 l_rowid := rec.rowid ;
495 l_position_id := rec.position_id ;
496 l_object_version_number := rec.object_version_number;
497 -- call per_all_positions api.
498 if (check_position_table(rec.position_id, l_object_version_number)) then
499 --
500 hr_utility.set_location(l_proc||' Lock per_all_positions api call ', 20);
501 --
502 begin
503 -- lock
504 if p_full_hr = 'N' or rec.effective_start_date <= p_refresh_date then
505
506 per_pos_shd.lck(p_position_id => rec.position_id
507 , p_object_version_number => l_object_version_number);
508 --
509 hr_utility.set_location(l_proc||' upd per_all_positions api call ', 30);
510 --
511 per_pos_upd.upd
512 (p_position_id => rec.position_id
513 ,p_successor_position_id => rec.successor_position_id
514 ,p_relief_position_id => rec.relief_position_id
515 ,p_location_id => rec.location_id
516 ,p_position_definition_id => rec.position_definition_id
517 ,p_date_effective => rec.date_effective
518 ,p_comments => rec.comments
519 ,p_date_end => rec.date_end
520 ,p_frequency => rec.frequency
521 ,p_name => rec.name
522 ,p_probation_period => rec.probation_period
523 ,p_probation_period_units => rec.probation_period_unit_cd
524 ,p_replacement_required_flag => rec.replacement_required_flag
525 ,p_time_normal_finish => rec.time_normal_finish
526 ,p_time_normal_start => rec.time_normal_start
527 ,p_status => rec.status
528 ,p_working_hours => rec.working_hours
529 ,p_attribute_category => rec.attribute_category
530 ,p_attribute1 => rec.attribute1
531 ,p_attribute2 => rec.attribute2
532 ,p_attribute3 => rec.attribute3
533 ,p_attribute4 => rec.attribute4
534 ,p_attribute5 => rec.attribute5
535 ,p_attribute6 => rec.attribute6
536 ,p_attribute7 => rec.attribute7
537 ,p_attribute8 => rec.attribute8
538 ,p_attribute9 => rec.attribute9
539 ,p_attribute10 => rec.attribute10
540 ,p_attribute11 => rec.attribute11
541 ,p_attribute12 => rec.attribute12
542 ,p_attribute13 => rec.attribute13
543 ,p_attribute14 => rec.attribute14
544 ,p_attribute15 => rec.attribute15
545 ,p_attribute16 => rec.attribute16
546 ,p_attribute17 => rec.attribute17
547 ,p_attribute18 => rec.attribute18
548 ,p_attribute19 => rec.attribute19
549 ,p_attribute20 => rec.attribute20
550 ,p_object_version_number => l_object_version_number
551 ,p_validate => FALSE
552 );
553 --
554 -- Store POS OVN in gl variable to help see the last pos ovn
555 --
556 gl_pos_ovn := l_object_version_number ;
557 --
558 hr_utility.set_location(l_proc, 60);
559 --
560 l_object_version_number := rec.object_version_number;
561 --
562 begin
563 set_copied_flag ( p_row => l_rowid
564 ,p_position_id => rec.position_id
565 ,p_effective_date => rec.effective_start_date
566 ,p_date_effective => rec.date_effective
567 ,p_object_version_number => l_object_version_number );
568 exception
569 when others then
570 rollback to refresh;
571 errbuf := 'S'||l_position_id||'-'||sqlerrm ;
572 retcode := 1 ;
573 if p_position_id is not null then
574 raise;
575 end if;
576 end;
577 -- set object version number for single position refresh request.
578 if p_effective_date between rec.effective_start_date and rec.effective_end_date then
579 p_object_version_number := l_object_version_number;
580 end if;
581 --
582 end if; -- refresh only if the date is valid
583 --
584 hr_utility.set_location(l_proc, 40);
585 --
586 exception
587 when others then
588 if p_position_id is not null then
589 raise;
590 end if;
591 l_rowid := null;
592 errbuf := 'U'||l_position_id||'-'||sqlerrm ;
593 retcode := 1 ;
594 --
595 hr_utility.set_location(' Exp:'||errbuf, 40);
596 --
597 end;
598 else
599 --
600 hr_utility.set_location(l_proc, 50);
601 --
602 begin
603 --
604 hr_utility.set_location(l_proc||' insert per_all_positions api call ', 50);
605 --
606 per_pos_ins.ins
607 (p_position_id => rec.position_id
608 ,p_business_group_id => rec.business_group_id
609 ,p_job_id => rec.job_id
610 ,p_organization_id => rec.organization_id
611 ,p_successor_position_id => rec.successor_position_id
612 ,p_relief_position_id => rec.relief_position_id
613 ,p_location_id => rec.location_id
614 ,p_position_definition_id => rec.position_definition_id
615 ,p_date_effective => rec.date_effective
616 ,p_comments => rec.comments
617 ,p_date_end => rec.date_end
618 ,p_frequency => rec.frequency
619 ,p_name => rec.name
620 ,p_probation_period => rec.probation_period
621 ,p_probation_period_units => rec.probation_period_unit_cd
622 ,p_replacement_required_flag => rec.replacement_required_flag
623 ,p_time_normal_finish => rec.time_normal_finish
624 ,p_time_normal_start => rec.time_normal_start
625 ,p_status => rec.status
626 ,p_working_hours => rec.working_hours
627 ,p_attribute_category => rec.attribute_category
628 ,p_attribute1 => rec.attribute1
629 ,p_attribute2 => rec.attribute2
630 ,p_attribute3 => rec.attribute3
631 ,p_attribute4 => rec.attribute4
632 ,p_attribute5 => rec.attribute5
633 ,p_attribute6 => rec.attribute6
634 ,p_attribute7 => rec.attribute7
635 ,p_attribute8 => rec.attribute8
636 ,p_attribute9 => rec.attribute9
637 ,p_attribute10 => rec.attribute10
638 ,p_attribute11 => rec.attribute11
639 ,p_attribute12 => rec.attribute12
640 ,p_attribute13 => rec.attribute13
641 ,p_attribute14 => rec.attribute14
642 ,p_attribute15 => rec.attribute15
643 ,p_attribute16 => rec.attribute16
644 ,p_attribute17 => rec.attribute17
645 ,p_attribute18 => rec.attribute18
646 ,p_attribute19 => rec.attribute19
647 ,p_attribute20 => rec.attribute20
648 ,p_object_version_number => l_object_version_number
649 ,p_validate => FALSE
650 );
651 --
652 -- Store POS OVN in gl variable to help see the last pos ovn
653 --
654 gl_pos_ovn := l_object_version_number ;
655 --
656 hr_utility.set_location(l_proc, 65);
657 --
658 l_object_version_number := rec.object_version_number;
659 --
660 begin
661 set_copied_flag ( p_row => l_rowid
662 ,p_position_id => rec.position_id
663 ,p_effective_date => rec.effective_start_date
664 ,p_date_effective => rec.date_effective
665 ,p_object_version_number => l_object_version_number );
666 exception
667 when others then
668 rollback to refresh;
669 errbuf := 'S'||l_position_id||'-'||sqlerrm ;
670 retcode := 1 ;
671 if p_position_id is not null then
672 raise;
673 end if;
674 end;
675 -- set object version number for single position refresh request.
676 if p_effective_date between rec.effective_start_date and rec.effective_end_date then
677 p_object_version_number := l_object_version_number;
678 end if;
679 --
680 hr_utility.set_location(l_proc||' after ins per_all_positions ', 50);
681 --
682 exception
683 when others then
684 hr_utility.set_location(l_proc||' When Others raised ', 50);
685 if p_position_id is not null then
686 raise;
687 end if;
688 l_rowid := null;
689 errbuf := 'I'||l_position_id||'-'||sqlerrm ;
690 retcode := 1 ;
691 --
692 hr_utility.set_location(' Exp:'||errbuf, 50);
693 --
694 end;
695 end if; -- check_position_table(position_id)
696 end if; -- rec.position_id <> l_position_id
697 --
698 hr_utility.set_location(l_proc, 70);
699 --
700 if p_position_id is null then
701 commit;
702 end if;
703 --
704 end loop; -- rec in c_pos loop
705 --
706 if p_position_id is not null then
707 close c_pos;
708 else
709 close c_all_pos;
710 end if;
711 --
712 per_refresh_position.refreshing_position := false;
713 exception
714 when others then
715 --
716 per_refresh_position.refreshing_position := false;
717 --
718 p_object_version_number := l_original_object_version_num;
719 --
720 if p_position_id is not null then
721 raise;
722 end if;
723 errbuf := 'Oth '||l_position_id||sqlerrm ;
724 --
725 hr_utility.set_location(l_proc||' '||errbuf, 70);
726 --
727 retcode := 2 ;
728 END refresh_position;
729 --
730 -- ----------------------------------------------------------------------------
731 -- |------------------------< refresh_position_purge >------------------------|
732 -- ----------------------------------------------------------------------------
733 procedure refresh_position_purge (p_position_id in number) is
734
735 l_ovn number := 0;
736 cursor c_chk_pos is
737 select object_version_number
738 from per_all_positions p
739 where position_id = p_position_id
740 and position_id not in ( select position_id from hr_all_positions_f where position_id = p_position_id) ;
741 begin
742 hr_utility.set_location('Entering:'||g_package||'refresh_position_purge', 600);
743 for i in c_chk_pos loop
744 per_pos_del.del
745 ( p_position_id => p_position_id ,
746 p_object_version_number => i.object_version_number ,
747 p_validate => false );
748 --
749 end loop;
750 hr_utility.set_location('Leaving:'||g_package||'refresh_position_purge', 600);
751 end;
752 --
753 -- ----------------------------------------------------------------------------
754 -- |------------------------< refresh_single_position >------------------------|
755 -- ----------------------------------------------------------------------------
756 --
757 procedure refresh_single_position ( p_refresh_date date
758 , p_position_id number
759 , p_effective_date date
760 , p_object_version_number in out nocopy number)
761 is
762 l_errbuf varchar2(2000);
763 l_retcode number;
764 begin
765 -- calling the refresh_position with position_id.
766 --
767 hr_utility.set_location('Entering:'||g_package||'refresh_single_position', 5);
768 --
769 if not (hr_position_api.full_hr) then
770 refresh_position ( p_refresh_date => p_refresh_date
771 , p_position_id => p_position_id
772 , p_effective_date => p_effective_date
773 , p_full_hr => 'N'
774 , p_object_version_number => p_object_version_number
775 , errbuf => l_errbuf
776 , retcode => l_retcode );
777 else
778 refresh_position ( p_refresh_date => p_refresh_date
779 , p_position_id => p_position_id
780 , p_effective_date => p_effective_date
781 , p_full_hr => 'Y'
782 , p_object_version_number => p_object_version_number
783 , errbuf => l_errbuf
784 , retcode => l_retcode );
785 end if;
786 --
787 -- purge position if purged from hr_all_positions_f
788 --
789 refresh_position_purge(p_position_id => p_position_id);
790 --
791 -- following update is put in place for Non HR Products who refers per_all_positions
792 --
793 update per_all_positions
794 set date_end = hr_general.get_position_date_end(position_id)
795 where position_id = p_position_id;
796 --
797 hr_utility.set_location('Entering:'||g_package||'refresh_single_position', 500);
798 --
799 end refresh_single_position;
800 --
801 function get_position_ovn return number
802 is
803 --
804 -- Retrive POS OVN from gl variable
805 --
806 begin
807 return(gl_pos_ovn);
808 end;
809 --
810 END PER_REFRESH_POSITION;