[Home] [Help]
PACKAGE BODY: APPS.HR_POSITION_API
Source
1 Package Body hr_position_api as
2 /* $Header: peposapi.pkb 120.5.12010000.2 2008/08/06 09:29:42 ubhat ship $ */
3 --
4 -- Package Variables
5 --
6 g_package varchar2(33) := ' hr_position_api.';
7 --
8 -- Local procedure used by non-DateTrack Position API
9 --
10 procedure create_eliminated_rec(
11 p_position_id number,
12 p_object_version_number in out nocopy number,
13 p_effective_date date) is
14 --
15 l_proc varchar2(72) ;
16 l_effective_start_date date;
17 l_effective_end_date date ;
18 l_position_definition_id number;
19 l_date_effective date;
20 l_name varchar2(500);
21 l_valid_grades_changed1 boolean;
22 l_availability_status_id number;
23 Cursor c2 is
24 Select SHARED_TYPE_ID
25 from per_shared_types
26 where LOOKUP_TYPE = 'POSITION_AVAILABILITY_STATUS'
27 AND SYSTEM_TYPE_CD = 'ELIMINATED';
28 --
29 cursor c3 is
30 select object_version_number, position_definition_id, name
31 from hr_all_positions_f
32 where position_id = p_position_id
33 and p_effective_date between effective_start_date and effective_end_date;
34 --
35 l_ovn number;
36 --
37 begin
38 if g_debug then
39 l_proc := g_package||'create_eliminated_rec';
40 hr_utility.set_location('Entering:'|| l_proc, 5);
41 end if;
42 --
43 open c3;
44 fetch c3 into l_ovn, l_position_definition_id, l_name;
45 close c3;
46 --
47 if p_position_id is not null
48 and p_object_version_number is not null
49 and p_effective_date is not null then
50 open c2;
51 fetch c2 into l_availability_status_id;
52 close c2;
53 --
54 hr_position_api.update_position
55 (p_position_id =>p_position_id
56 ,p_effective_start_date =>l_effective_start_date
57 ,p_effective_end_date =>l_effective_end_date
58 ,p_position_definition_id =>l_position_definition_id
59 ,p_valid_grades_changed_warning =>l_valid_grades_changed1
60 ,p_name =>l_name
61 ,p_availability_status_id =>l_availability_status_id
62 ,p_object_version_number =>p_object_version_number
63 ,p_effective_date =>p_effective_date
64 ,p_datetrack_mode =>'UPDATE'
65 );
66 end if;
67 if g_debug then
68 hr_utility.set_location('Leaving:'|| l_proc, 30);
69 end if;
70 end;
71 -- Reset FULL_HR to True
72 procedure reset_hr_installation is
73 begin
74 FULL_HR := TRUE;
75 end;
76 -- Get the HR Installation status and set FULL_HR variable
77 procedure get_hr_installation is
78 --
79 l_proc varchar2(72);
80 l_return boolean;
81 l_status varchar2(1);
82 l_industry varchar2(1);
83 --
84 begin
85 --
86 if g_debug then
87 l_proc := g_package||'get_hr_installation';
88 hr_utility.set_location('Entering:'|| l_proc, 5);
89 end if;
90 --
91 -- Find if full hr installation or shared hr installation
92 --
93 l_return := fnd_installation.get(appl_id => 800,
94 dep_appl_id => 800,
95 status => l_status,
96 industry => l_industry);
97 --
98 If l_status = 'I' then
99 FULL_HR := TRUE;
100 Elsif l_status = 'S' then
101 FULL_HR := FALSE;
102 Else
103 hr_utility.set_message(801,'HR_NULL_INSTALLATION_STATUS');
104 hr_utility.raise_error;
105 End if;
106 --
107 -- For SHARED HR testing purposes
108 -- FULL_HR := FALSE;
109 --
110 if g_debug then
111 hr_utility.set_location('Leaving:'|| l_proc, 30);
112 end if;
113 end;
114 --
115 -- Function to delete unused per_position_definitions
116 --
117 function delete_unused_per_pos_def(p_position_definition_id number)
118 return boolean is
119 --
120 cursor c_per_pos_def_used(p_position_definition_id number) is
121 SELECT 'x'
122 from dual
123 where exists (
124 select null
125 from hr_all_positions_f
126 where position_definition_id = p_position_definition_id)
127 or exists (
128 select null
129 from per_all_positions
130 where position_definition_id = p_position_definition_id)
131 or exists (
132 select null
133 from per_mm_positions
134 where new_position_definition_id = p_position_definition_id)
135 or exists (
136 select null
137 from pqh_position_transactions
138 where position_definition_id = p_position_definition_id);
139 --
140 l_dummy varchar2(10);
141 --
142 begin
143 open c_per_pos_def_used(p_position_definition_id);
144 fetch c_per_pos_def_used into l_dummy;
145 if (c_per_pos_def_used%found) then
146 close c_per_pos_def_used;
147 return false;
148 end if;
149 close c_per_pos_def_used;
150 --
151 delete from per_position_definitions
152 where position_definition_id = p_position_definition_id;
153 --
154 return true;
155 --
156 end;
157 --
158 --
159 -- ----------------------------------------------------------------------------
160 -- |------------------------< create_position >-------------------------------|
161 -- ----------------------------------------------------------------------------
162 -- NON DATE-TRACK CREATE_POSITION
163 procedure create_position
164 (p_validate in boolean default false
165 ,p_job_id in number
166 ,p_organization_id in number
167 ,p_date_effective in date
168 ,p_successor_position_id in number default null
169 ,p_relief_position_id in number default null
170 ,p_location_id in number default null
171 ,p_comments in varchar2 default null
172 ,p_date_end in date default null
173 ,p_frequency in varchar2 default null
174 ,p_probation_period in number default null
175 ,p_probation_period_units in varchar2 default null
176 ,p_replacement_required_flag in varchar2 default null
177 ,p_time_normal_finish in varchar2 default null
178 ,p_time_normal_start in varchar2 default null
179 ,p_status in varchar2 default null
180 ,p_working_hours in number default null
181 ,p_attribute_category in varchar2 default null
182 ,p_attribute1 in varchar2 default null
183 ,p_attribute2 in varchar2 default null
184 ,p_attribute3 in varchar2 default null
185 ,p_attribute4 in varchar2 default null
186 ,p_attribute5 in varchar2 default null
187 ,p_attribute6 in varchar2 default null
188 ,p_attribute7 in varchar2 default null
189 ,p_attribute8 in varchar2 default null
190 ,p_attribute9 in varchar2 default null
191 ,p_attribute10 in varchar2 default null
192 ,p_attribute11 in varchar2 default null
193 ,p_attribute12 in varchar2 default null
194 ,p_attribute13 in varchar2 default null
195 ,p_attribute14 in varchar2 default null
196 ,p_attribute15 in varchar2 default null
197 ,p_attribute16 in varchar2 default null
198 ,p_attribute17 in varchar2 default null
199 ,p_attribute18 in varchar2 default null
200 ,p_attribute19 in varchar2 default null
201 ,p_attribute20 in varchar2 default null
202 ,p_segment1 in varchar2 default null
203 ,p_segment2 in varchar2 default null
204 ,p_segment3 in varchar2 default null
205 ,p_segment4 in varchar2 default null
206 ,p_segment5 in varchar2 default null
207 ,p_segment6 in varchar2 default null
208 ,p_segment7 in varchar2 default null
209 ,p_segment8 in varchar2 default null
210 ,p_segment9 in varchar2 default null
211 ,p_segment10 in varchar2 default null
212 ,p_segment11 in varchar2 default null
213 ,p_segment12 in varchar2 default null
214 ,p_segment13 in varchar2 default null
215 ,p_segment14 in varchar2 default null
216 ,p_segment15 in varchar2 default null
217 ,p_segment16 in varchar2 default null
218 ,p_segment17 in varchar2 default null
219 ,p_segment18 in varchar2 default null
220 ,p_segment19 in varchar2 default null
221 ,p_segment20 in varchar2 default null
222 ,p_segment21 in varchar2 default null
223 ,p_segment22 in varchar2 default null
224 ,p_segment23 in varchar2 default null
225 ,p_segment24 in varchar2 default null
226 ,p_segment25 in varchar2 default null
227 ,p_segment26 in varchar2 default null
228 ,p_segment27 in varchar2 default null
229 ,p_segment28 in varchar2 default null
230 ,p_segment29 in varchar2 default null
231 ,p_segment30 in varchar2 default null
232 ,p_concat_segments in varchar2 default null
233 ,p_position_id out nocopy number
234 ,p_object_version_number out nocopy number
235 ,p_position_definition_id in out nocopy number
236 ,p_name in out nocopy varchar2
237 ) is
238 --
239 -- Declare cursors and local variables
240 --
241 l_business_group_id per_positions.business_group_id%TYPE;
242 l_position_definition_id per_positions.position_definition_id%TYPE
243 := p_position_definition_id;
244 l_old_position_definition_id per_positions.position_definition_id%TYPE;
245 l_flex_num fnd_id_flex_segments.id_flex_num%TYPE;
246 l_name per_positions.name%TYPE := p_name;
247 l_proc varchar2(72) ;
248 l_date_effective per_positions.date_effective%TYPE;
249 l_date_end per_positions.date_end%TYPE;
250 --
251 l_effective_start_date date;
252 l_effective_end_date date;
253 l_active_status_id number;
254 l_effective_date date;
255 /*Added trim to all the segment values for bug 6750144*/
256 l_segment1 varchar2(60) := trim(p_segment1);
257 l_segment2 varchar2(60) := trim(p_segment2);
258 l_segment3 varchar2(60) := trim(p_segment3);
259 l_segment4 varchar2(60) := trim(p_segment4);
260 l_segment5 varchar2(60) := trim(p_segment5);
261 l_segment6 varchar2(60) := trim(p_segment6);
262 l_segment7 varchar2(60) := trim(p_segment7);
263 l_segment8 varchar2(60) := trim(p_segment8);
264 l_segment9 varchar2(60) := trim(p_segment9);
265 l_segment10 varchar2(60) := trim(p_segment10);
266 l_segment11 varchar2(60) := trim(p_segment11);
267 l_segment12 varchar2(60) := trim(p_segment12);
268 l_segment13 varchar2(60) := trim(p_segment13);
269 l_segment14 varchar2(60) := trim(p_segment14);
270 l_segment15 varchar2(60) := trim(p_segment15);
271 l_segment16 varchar2(60) := trim(p_segment16);
272 l_segment17 varchar2(60) := trim(p_segment17);
273 l_segment18 varchar2(60) := trim(p_segment18);
274 l_segment19 varchar2(60) := trim(p_segment19);
275 l_segment20 varchar2(60) := trim(p_segment20);
276 l_segment21 varchar2(60) := trim(p_segment21);
277 l_segment22 varchar2(60) := trim(p_segment22);
278 l_segment23 varchar2(60) := trim(p_segment23);
279 l_segment24 varchar2(60) := trim(p_segment24);
280 l_segment25 varchar2(60) := trim(p_segment25);
281 l_segment26 varchar2(60) := trim(p_segment26);
282 l_segment27 varchar2(60) := trim(p_segment27);
283 l_segment28 varchar2(60) := trim(p_segment28);
284 l_segment29 varchar2(60) := trim(p_segment29);
285 l_segment30 varchar2(60) := trim(p_segment30);
286 --
287 -- bug 2271064 new variable to indicate whether key flex id parameter
288 -- enters the program with a value.
289 --
290 l_null_ind number(1) := 0;
291 --
292 --
293 -- Declare additional OUT variables
294 --
295 l_position_id per_positions.position_id%TYPE;
296 --
297 cursor csr_job_bg is
298 select business_group_id
299 from per_jobs
300 where job_id = p_job_id;
301 --
302 -- bug 2271064 get per_position_definitions segment values where
303 -- position_definition_id is known
304 --
305 cursor c_segments is
306 select segment1,
307 segment2,
308 segment3,
309 segment4,
310 segment5,
311 segment6,
312 segment7,
313 segment8,
314 segment9,
315 segment10,
316 segment11,
317 segment12,
318 segment13,
319 segment14,
320 segment15,
321 segment16,
322 segment17,
323 segment18,
324 segment19,
325 segment20,
326 segment21,
327 segment22,
328 segment23,
329 segment24,
330 segment25,
331 segment26,
332 segment27,
333 segment28,
334 segment29,
335 segment30
336 from per_position_definitions
337 where position_definition_id = l_position_definition_id;
338 --
339 begin
340 --
341 g_debug := hr_utility.debug_enabled;
342 if g_debug then
343 l_proc := g_package||'create_position';
344 hr_utility.set_location('Entering:'|| l_proc, 5);
345 end if;
346 --
347 -- Issue a savepoint
348 --
349 savepoint create_position;
350 --
351 get_hr_installation;
352 --
353 -- Get business_group_id using job.
354 --
355 if g_debug then
356 hr_utility.set_location(l_proc, 10);
357 end if;
358 --
359 open csr_job_bg;
360 fetch csr_job_bg
361 into l_business_group_id;
362 --
363 if csr_job_bg%notfound then
364 close csr_job_bg;
365 hr_utility.set_message(801, 'HR_51090_JOB_NOT_EXIST');
366 hr_utility.raise_error;
367 else
368 close csr_job_bg;
369 end if;
370 --
371 if g_debug then
372 hr_utility.set_location(l_proc, 15);
373 end if;
374 --
375 /*
376 obsoleted code [Date Tracking] vmolasi
377 idsel calls to user hooks etc
378 */
379 --
380 -- assign variables
381 --
382 l_date_effective := trunc(p_date_effective) ;
383 l_date_end := trunc(p_date_end);
384 --
385 if g_debug then
386 hr_utility.set_location(l_proc || ' l_name ' || l_name, 200);
387 hr_utility.set_location(l_proc || ' l_pos_def_id '|| l_position_definition_id, 201);
388 end if;
389 --
390 -- 2242339 get segment values if p_position_definition_id entered with a value
391 --
392 if l_position_definition_id is not null
393 --
394 then
395 --
396 if g_debug then
397 hr_utility.set_location(l_proc, 15);
398 end if;
399 --
400 --set indicator to show p_position_definition_id did not enter program null
401 --
402 l_null_ind := 1;
403 --
404 open c_segments;
405 fetch c_segments into
406 l_segment1,
407 l_segment2,
408 l_segment3,
409 l_segment4,
410 l_segment5,
411 l_segment6,
412 l_segment7,
413 l_segment8,
414 l_segment9,
415 l_segment10,
416 l_segment11,
417 l_segment12,
418 l_segment13,
419 l_segment14,
420 l_segment15,
421 l_segment16,
422 l_segment17,
423 l_segment18,
424 l_segment19,
425 l_segment20,
426 l_segment21,
427 l_segment22,
428 l_segment23,
429 l_segment24,
430 l_segment25,
431 l_segment26,
432 l_segment27,
433 l_segment28,
434 l_segment29,
435 l_segment30;
436 close c_segments;
437 else
438 l_null_ind := 0;
439 l_name := null;
440 end if;
441 --
442 --begin
443 --
444 --
445 -- Call new date-tracked position API
446 --
447 hr_position_api.create_position
448 (p_position_id => p_position_id
449 ,p_effective_start_date => l_effective_start_date
450 ,p_effective_end_date => l_effective_end_date
451 ,p_effective_date => l_date_effective
452 ,p_job_id => p_job_id
453 ,p_organization_id => p_organization_id
454 ,p_successor_position_id => p_successor_position_id
455 ,p_relief_position_id => p_relief_position_id
456 ,p_location_id => p_location_id
457 ,p_position_definition_id => l_position_definition_id
458 ,p_date_effective => l_date_effective
459 ,p_comments => p_comments
460 ,p_date_end => null --l_date_end
461 ,p_frequency => p_frequency
462 ,p_name => l_name
463 ,p_probation_period => p_probation_period
464 ,p_probation_period_unit_cd => p_probation_period_units
465 ,p_replacement_required_flag => p_replacement_required_flag
466 ,p_time_normal_finish => p_time_normal_finish
467 ,p_time_normal_start => p_time_normal_start
468 ,p_status => p_status
469 ,p_working_hours => p_working_hours
470 ,p_attribute_category => p_attribute_category
471 ,p_attribute1 => p_attribute1
472 ,p_attribute2 => p_attribute2
473 ,p_attribute3 => p_attribute3
474 ,p_attribute4 => p_attribute4
475 ,p_attribute5 => p_attribute5
476 ,p_attribute6 => p_attribute6
477 ,p_attribute7 => p_attribute7
478 ,p_attribute8 => p_attribute8
479 ,p_attribute9 => p_attribute9
480 ,p_attribute10 => p_attribute10
481 ,p_attribute11 => p_attribute11
482 ,p_attribute12 => p_attribute12
483 ,p_attribute13 => p_attribute13
484 ,p_attribute14 => p_attribute14
485 ,p_attribute15 => p_attribute15
486 ,p_attribute16 => p_attribute16
487 ,p_attribute17 => p_attribute17
488 ,p_attribute18 => p_attribute18
489 ,p_attribute19 => p_attribute19
490 ,p_attribute20 => p_attribute20
491 ,p_segment1 => l_segment1
492 ,p_segment2 => l_segment2
493 ,p_segment3 => l_segment3
494 ,p_segment4 => l_segment4
495 ,p_segment5 => l_segment5
496 ,p_segment6 => l_segment6
497 ,p_segment7 => l_segment7
498 ,p_segment8 => l_segment8
499 ,p_segment9 => l_segment9
500 ,p_segment10 => l_segment10
501 ,p_segment11 => l_segment11
502 ,p_segment12 => l_segment12
503 ,p_segment13 => l_segment13
504 ,p_segment14 => l_segment14
505 ,p_segment15 => l_segment15
506 ,p_segment16 => l_segment16
507 ,p_segment17 => l_segment17
508 ,p_segment18 => l_segment18
509 ,p_segment19 => l_segment19
510 ,p_segment20 => l_segment20
511 ,p_segment21 => l_segment21
512 ,p_segment22 => l_segment22
513 ,p_segment23 => l_segment23
514 ,p_segment24 => l_segment24
515 ,p_segment25 => l_segment25
516 ,p_segment26 => l_segment26
517 ,p_segment27 => l_segment27
518 ,p_segment28 => l_segment28
519 ,p_segment29 => l_segment29
520 ,p_segment30 => l_segment30
521 ,p_concat_segments => p_concat_segments
522 ,p_object_version_number => p_object_version_number
523 ,p_validate => p_validate
524 );
525 if g_debug then
526 hr_utility.set_location(l_proc, 25);
527 end if;
528 --
529 -- Create Eliminated Record is date_end is not null
530 --
531 if p_date_end is not null then
532 create_eliminated_rec(
533 p_position_id => p_position_id,
534 p_object_version_number => p_object_version_number,
535 p_effective_date => p_date_end);
536 end if;
537 if g_debug then
538 hr_utility.set_location(l_proc, 30);
539 end if;
540 --
541 -- get the updated Object version number of the per_all_positions table
542 -- which is returned to the user as ovn of created row.
543 --
544 p_object_version_number := per_refresh_position.get_position_ovn;
545 --
546 if g_debug then
547 hr_utility.set_location('per_all_ovn is '||p_object_version_number||l_proc,9);
548 end if;
549 --
550 -- When in validation only mode raise the Validate_Enabled exception
551 --
552 if p_validate then
553 raise hr_api.validate_enabled;
554 end if;
555 --
556 -- Set remaining output arguments
557 --
558 p_position_definition_id := l_position_definition_id;
559 p_name := l_name;
560 --
561 reset_hr_installation;
562 --
563 if g_debug then
564 hr_utility.set_location(' Leaving:'||l_proc, 30);
565 end if;
566 exception
567 when hr_api.validate_enabled then
568 reset_hr_installation;
569 --
570 -- As the Validate_Enabled exception has been raised
571 -- we must rollback to the savepoint
572 --
573 ROLLBACK TO create_position;
574 --
575 -- Only set output warning arguments
576 -- (Any key or derived arguments must be set to null
577 -- when validation only mode is being used.)
578 --
579 p_position_id := null;
580 p_object_version_number := null;
581 if l_null_ind = 0
582 then
583 p_position_definition_id := null;
584 end if;
585 p_name := null;
586 --
587 when others then
588 reset_hr_installation;
589 --
590 -- A validation or unexpected error has occurred
591 --
592 -- Added as part of the fix to bug 632479
593 --
594 p_position_id := null;
595 p_object_version_number := null;
596 p_position_definition_id := l_position_definition_id;
597 p_name := l_name;
598
599 ROLLBACK TO create_position;
600 --
601 raise;
602 --
603 end create_position;
604 --
605 -- ----------------------------------------------------------------------------
606 --|------------------------< get_dt_position_ovn >-----------------------------|
607 -- ----------------------------------------------------------------------------
608 --
609 function get_dt_position_ovn (p_position_id number, p_effective_date date)
610 return number is
611 cursor c1 is select object_version_number
612 from hr_all_positions_f
613 where position_id = p_position_id
614 and p_effective_date between effective_start_date
615 and effective_end_date ;
616 l_proc varchar2(72);
617 l_object_version_number number;
618 begin
619 if g_debug then
620 l_proc := g_package||'get_dt_position_ovn' ;
621 hr_utility.set_location(' Entering:'||l_proc, 5);
622 end if;
623 open c1;
624 fetch c1 into l_object_version_number;
625 close c1;
626 return l_object_version_number;
627 if c1%notfound then
628 close c1;
629 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
630 hr_utility.set_message_token('Function', l_proc);
631 hr_utility.set_message_token('STEP','5');
632 hr_utility.raise_error;
633 end if;
634 if g_debug then
635 hr_utility.set_location(' Leaving:'||l_proc, 30);
636 end if;
637 end get_dt_position_ovn;
638 --
639 --
640 function SYSTEM_AVAILABILITY_STATUS (
641 --
642 p_availability_status_id number) return varchar2 is
643 --
644 cursor csr_lookup is
645 select system_type_cd
646 from per_shared_types
647 where shared_type_id = p_availability_status_id;
648 --
649 v_meaning varchar2(30) := null;
650 --
651 begin
652 --
653 -- Only open the cursor if the parameter is going to retrieve anything
654 --
655 if p_availability_status_id is not null then
656 --
657 open csr_lookup;
658 fetch csr_lookup into v_meaning;
659 close csr_lookup;
660 --
661 end if;
662 return v_meaning;
663 end system_availability_status;
664 --
665 procedure delete_eliminated_rec(p_position_id number) is
666 l_system_availability_status varchar2(100);
667 l_deleted varchar2(10):='N';
668 l_eot date:= TO_DATE('31/12/4712','DD/MM/YYYY');
669 cursor c2 is
670 select position_id, effective_start_date, effective_end_date, availability_status_id
671 from hr_all_Positions_f
672 where position_id = p_position_id
673 order by effective_start_date desc
674 for update;
675 r2 c2%rowtype;
676 l_proc varchar2(72) ;
677 begin
678 if g_debug then
679 l_proc := g_package||'delete_eliminated_rec' ;
680 hr_utility.set_location(' Entering:'||l_proc, 5);
681 end if;
682 -- Delete Eliminated Record
683 open c2;
684 fetch c2 into r2;
685 --
686 if c2%found then
687 l_system_availability_status := system_availability_status(r2.availability_status_id);
688 if l_system_availability_status = 'ELIMINATED' then
689 delete hr_all_positions_f
690 where current of c2;
691 l_deleted := 'Y';
692 end if;
693 end if;
694 if l_deleted = 'Y' then
695 fetch c2 into r2;
696 if c2%found then
697 update hr_all_positions_f
698 set effective_end_date = l_eot
699 where current of c2;
700 end if;
701 end if;
702 close c2;
703 --
704 if g_debug then
705 hr_utility.set_location(' Leaving:'||l_proc, 30);
706 end if;
707 --
708 end;
709 --
710 -- ----------------------------------------------------------------------------
711 -- |------------------------< update_position >-------------------------------|
712 -- ----------------------------------------------------------------------------
713 --
714 procedure update_position
715 (p_validate in boolean default false
716 ,p_position_id in number
717 ,p_object_version_number in out nocopy number
718 ,p_language_code in varchar2 default hr_api.userenv_lang
719 ,p_successor_position_id in number default hr_api.g_number
720 ,p_relief_position_id in number default hr_api.g_number
721 ,p_location_id in number default hr_api.g_number
722 ,p_date_effective in date default hr_api.g_date
723 ,p_comments in varchar2 default hr_api.g_varchar2
724 ,p_date_end in date default hr_api.g_date
725 ,p_frequency in varchar2 default hr_api.g_varchar2
726 ,p_probation_period in number default hr_api.g_number
727 ,p_probation_period_units in varchar2 default hr_api.g_varchar2
728 ,p_replacement_required_flag in varchar2 default hr_api.g_varchar2
729 ,p_time_normal_finish in varchar2 default hr_api.g_varchar2
730 ,p_time_normal_start in varchar2 default hr_api.g_varchar2
731 ,p_status in varchar2 default hr_api.g_varchar2
732 ,p_working_hours in number default hr_api.g_number
733 ,p_attribute_category in varchar2 default hr_api.g_varchar2
734 ,p_attribute1 in varchar2 default hr_api.g_varchar2
735 ,p_attribute2 in varchar2 default hr_api.g_varchar2
736 ,p_attribute3 in varchar2 default hr_api.g_varchar2
737 ,p_attribute4 in varchar2 default hr_api.g_varchar2
738 ,p_attribute5 in varchar2 default hr_api.g_varchar2
739 ,p_attribute6 in varchar2 default hr_api.g_varchar2
740 ,p_attribute7 in varchar2 default hr_api.g_varchar2
741 ,p_attribute8 in varchar2 default hr_api.g_varchar2
742 ,p_attribute9 in varchar2 default hr_api.g_varchar2
743 ,p_attribute10 in varchar2 default hr_api.g_varchar2
744 ,p_attribute11 in varchar2 default hr_api.g_varchar2
745 ,p_attribute12 in varchar2 default hr_api.g_varchar2
746 ,p_attribute13 in varchar2 default hr_api.g_varchar2
747 ,p_attribute14 in varchar2 default hr_api.g_varchar2
748 ,p_attribute15 in varchar2 default hr_api.g_varchar2
749 ,p_attribute16 in varchar2 default hr_api.g_varchar2
750 ,p_attribute17 in varchar2 default hr_api.g_varchar2
751 ,p_attribute18 in varchar2 default hr_api.g_varchar2
752 ,p_attribute19 in varchar2 default hr_api.g_varchar2
753 ,p_attribute20 in varchar2 default hr_api.g_varchar2
754 ,p_segment1 in varchar2 default hr_api.g_varchar2
755 ,p_segment2 in varchar2 default hr_api.g_varchar2
756 ,p_segment3 in varchar2 default hr_api.g_varchar2
757 ,p_segment4 in varchar2 default hr_api.g_varchar2
758 ,p_segment5 in varchar2 default hr_api.g_varchar2
759 ,p_segment6 in varchar2 default hr_api.g_varchar2
760 ,p_segment7 in varchar2 default hr_api.g_varchar2
761 ,p_segment8 in varchar2 default hr_api.g_varchar2
762 ,p_segment9 in varchar2 default hr_api.g_varchar2
763 ,p_segment10 in varchar2 default hr_api.g_varchar2
764 ,p_segment11 in varchar2 default hr_api.g_varchar2
765 ,p_segment12 in varchar2 default hr_api.g_varchar2
766 ,p_segment13 in varchar2 default hr_api.g_varchar2
767 ,p_segment14 in varchar2 default hr_api.g_varchar2
768 ,p_segment15 in varchar2 default hr_api.g_varchar2
769 ,p_segment16 in varchar2 default hr_api.g_varchar2
770 ,p_segment17 in varchar2 default hr_api.g_varchar2
771 ,p_segment18 in varchar2 default hr_api.g_varchar2
772 ,p_segment19 in varchar2 default hr_api.g_varchar2
773 ,p_segment20 in varchar2 default hr_api.g_varchar2
774 ,p_segment21 in varchar2 default hr_api.g_varchar2
775 ,p_segment22 in varchar2 default hr_api.g_varchar2
776 ,p_segment23 in varchar2 default hr_api.g_varchar2
777 ,p_segment24 in varchar2 default hr_api.g_varchar2
778 ,p_segment25 in varchar2 default hr_api.g_varchar2
779 ,p_segment26 in varchar2 default hr_api.g_varchar2
780 ,p_segment27 in varchar2 default hr_api.g_varchar2
781 ,p_segment28 in varchar2 default hr_api.g_varchar2
782 ,p_segment29 in varchar2 default hr_api.g_varchar2
783 ,p_segment30 in varchar2 default hr_api.g_varchar2
784 ,p_concat_segments in varchar2 default hr_api.g_varchar2
785 ,p_position_definition_id in out nocopy number
786 ,p_name in out nocopy varchar2
787 ,p_valid_grades_changed_warning out nocopy boolean
788 ) is
789 --
790 -- Declare cursors and local variables
791 --
792 -- bug 2271064 initialize l_position_definition_id and segment variables with
793 -- values where these are passed into program.
794 --
795 l_object_version_number per_positions.object_version_number%TYPE;
796 l_proc varchar2(72);
797 l_valid_grades_changed1 boolean default FALSE;
798 l_valid_grades_changed2 boolean default FALSE;
799 l_flex_num fnd_id_flex_segments.id_flex_num%TYPE;
800 l_api_updating boolean;
801 l_position_definition_id per_positions.position_definition_id%TYPE
802 := p_position_definition_id;
803 l_name per_positions.name%TYPE
804 := p_name;
805 l_date_effective per_positions.date_effective%TYPE;
806 l_date_end per_positions.date_end%TYPE;
807 l_business_group_id per_positions.business_group_id%TYPE;
808 l_effective_start_date date;
809 l_effective_end_date date;
810 l_effective_date date;
811 --
812 l_return boolean;
813 l_status varchar2(1);
814 l_industry varchar2(1);
815 --
816 l_segment1 varchar2(60) := p_segment1;
817 l_segment2 varchar2(60) := p_segment2;
818 l_segment3 varchar2(60) := p_segment3;
819 l_segment4 varchar2(60) := p_segment4;
820 l_segment5 varchar2(60) := p_segment5;
821 l_segment6 varchar2(60) := p_segment6;
822 l_segment7 varchar2(60) := p_segment7;
823 l_segment8 varchar2(60) := p_segment8;
824 l_segment9 varchar2(60) := p_segment9;
825 l_segment10 varchar2(60) := p_segment10;
826 l_segment11 varchar2(60) := p_segment11;
827 l_segment12 varchar2(60) := p_segment12;
828 l_segment13 varchar2(60) := p_segment13;
829 l_segment14 varchar2(60) := p_segment14;
830 l_segment15 varchar2(60) := p_segment15;
831 l_segment16 varchar2(60) := p_segment16;
832 l_segment17 varchar2(60) := p_segment17;
833 l_segment18 varchar2(60) := p_segment18;
834 l_segment19 varchar2(60) := p_segment19;
835 l_segment20 varchar2(60) := p_segment20;
836 l_segment21 varchar2(60) := p_segment21;
837 l_segment22 varchar2(60) := p_segment22;
838 l_segment23 varchar2(60) := p_segment23;
839 l_segment24 varchar2(60) := p_segment24;
840 l_segment25 varchar2(60) := p_segment25;
841 l_segment26 varchar2(60) := p_segment26;
842 l_segment27 varchar2(60) := p_segment27;
843 l_segment28 varchar2(60) := p_segment28;
844 l_segment29 varchar2(60) := p_segment29;
845 l_segment30 varchar2(60) := p_segment30;
846 l_null_ind number(1) := 0;
847 --
848 -- Declare cursors
849 --
850 cursor csr_idsel is
851 select pd.id_flex_num
852 from per_position_definitions pd
853 where pd.position_definition_id = l_position_definition_id;
854 --
855 cursor get_curr_esd ( p_position_id in number) is
856 select
857 max(effective_start_date)
858 from hr_all_positions_f
859 where position_id = p_position_id and
860 nvl(copied_to_old_table_flag, 'N') = 'Y';
861 --
862 -- bug 2271064 get per_position_definitions segment values where
863 -- position_definition_id is known
864 --
865 cursor c_segments is
866 select segment1,
867 segment2,
868 segment3,
869 segment4,
870 segment5,
871 segment6,
872 segment7,
873 segment8,
874 segment9,
875 segment10,
876 segment11,
877 segment12,
878 segment13,
879 segment14,
880 segment15,
881 segment16,
882 segment17,
883 segment18,
884 segment19,
885 segment20,
886 segment21,
887 segment22,
888 segment23,
889 segment24,
890 segment25,
891 segment26,
892 segment27,
893 segment28,
894 segment29,
895 segment30
896 from per_position_definitions
897 where position_definition_id = l_position_definition_id;
898 --
899 begin
900 --
901 g_debug := hr_utility.debug_enabled;
902 if g_debug then
903 l_proc := g_package||'update_position';
904 hr_utility.set_location('Entering:'|| l_proc, 5);
905 end if;
906 --
907 -- Issue a savepoint
908 --
909 savepoint update_position;
910 --
911 get_hr_installation;
912 --
913 l_date_effective := trunc(p_date_effective);
914 l_date_end := trunc(p_date_end);
915 --
916 -- Validation in addition to Table Handlers
917 --
918 -- Retrieve current position details from position
919 --
920 l_api_updating := per_pos_shd.api_updating
921 (p_position_id => p_position_id
922 ,p_object_version_number => p_object_version_number);
923 --
924 if g_debug then
925 hr_utility.set_location(l_proc, 15);
926 end if;
927 --
928 if not l_api_updating
929 then
930 if g_debug then
931 hr_utility.set_location(l_proc, 20);
932 end if;
933 --
934 -- As this an updating API, the position should already exist.
935 --
936 hr_utility.set_message(801, 'HR_7220_INVALID_PRIMARY_KEY');
937 hr_utility.raise_error;
938 else
939 if l_position_definition_id is null
940 then
941 l_null_ind := 0;
942 -- l_name := null;
943 l_position_definition_id
944 := per_pos_shd.g_old_rec.position_definition_id;
945 --
946 if g_debug then
947 hr_utility.set_location(l_proc, 25);
948 end if;
949 --
950 else
951 -- 2242339
952 -- get segment values if p_position_definition_id entered with a value
953 -- set indicator to show p_position_definition_id didnot enter pgm null
954 --
955 l_null_ind := 1;
956 --
957 open c_segments;
958 fetch c_segments into
959 l_segment1,
960 l_segment2,
961 l_segment3,
962 l_segment4,
963 l_segment5,
964 l_segment6,
965 l_segment7,
966 l_segment8,
967 l_segment9,
968 l_segment10,
969 l_segment11,
970 l_segment12,
971 l_segment13,
972 l_segment14,
973 l_segment15,
974 l_segment16,
975 l_segment17,
976 l_segment18,
977 l_segment19,
978 l_segment20,
979 l_segment21,
980 l_segment22,
981 l_segment23,
982 l_segment24,
983 l_segment25,
984 l_segment26,
985 l_segment27,
986 l_segment28,
987 l_segment29,
988 l_segment30;
989 close c_segments;
990 if g_debug then
991 hr_utility.set_location(l_proc, 27);
992 end if;
993 --
994 end if;
995 end if;
996 --
997 open csr_idsel;
998 fetch csr_idsel
999 into l_flex_num;
1000 if csr_idsel%NOTFOUND then
1001 close csr_idsel;
1002 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1003 hr_utility.set_message_token('PROCEDURE', l_proc);
1004 hr_utility.set_message_token('STEP','5');
1005 hr_utility.raise_error;
1006 end if;
1007 close csr_idsel;
1008 --
1009 -- Code hr_kflex_utility.upd_or_sel_keyflex_comb etc has been Obsoleted due to
1010 -- Position date-tracking [vmolasi]
1011 --
1012 --
1013 --
1014 -- get effective_start_Date
1015 --
1016 open get_curr_esd( p_position_id);
1017 fetch get_curr_esd into l_effective_date;
1018 if get_curr_esd%notfound then
1019 close get_curr_esd;
1020 --
1021 -- As this an updating API, the position should already exist.
1022 --
1023 hr_utility.set_message(801, 'HR_7220_INVALID_PRIMARY_KEY');
1024 hr_utility.raise_error;
1025 else
1026 close get_curr_esd;
1027 end if;
1028 if g_debug then
1029 hr_utility.set_location(l_proc, 30);
1030 end if;
1031 --
1032 -- Delete Eliminated Rec
1033 delete_eliminated_rec(p_position_id);
1034 if g_debug then
1035 hr_utility.set_location(l_proc, 35);
1036 end if;
1037 --
1038 -- ovn of date tracked table is fetched for passing
1039 --
1040 l_object_version_number := get_dt_position_ovn(p_position_id,l_effective_date);
1041 --
1042 if g_debug then
1043 hr_utility.set_location('hr_all_ovn is '||l_object_version_number||l_proc,5);
1044 end if;
1045 --
1046 -- fnd_message.set_name('PQH','DATE-'||l_object_version_number);
1047 -- fnd_message.raise_error;
1048 --
1049 if g_debug then
1050 hr_utility.set_location(l_proc || 'l_pos_def_id '|| l_position_definition_id, 401);
1051 hr_utility.set_location(l_proc || 'p_pos_def_id '|| p_position_definition_id, 402);
1052 hr_utility.set_location(l_proc || 'l_name '|| l_name, 403);
1053 hr_utility.set_location(l_proc || 'p_name '|| p_name, 404);
1054 end if;
1055 l_effective_date := greatest(l_effective_date, l_date_effective);
1056 l_position_definition_id := p_position_definition_id;
1057 l_name := p_name;
1058 --
1059 if g_debug then
1060 hr_utility.set_location(l_proc, 40);
1061 end if;
1062 --
1063 hr_position_api.update_position
1064 (p_position_id => p_position_id
1065 ,p_effective_start_date => l_effective_start_date
1066 ,p_effective_end_date => l_effective_end_date
1067 ,p_effective_date => l_effective_date
1068 ,p_successor_position_id => p_successor_position_id
1069 ,p_relief_position_id => p_relief_position_id
1070 ,p_location_id => p_location_id
1071 ,p_position_definition_id => l_position_definition_id
1072 ,p_date_effective => l_date_effective
1073 ,p_comments => p_comments
1074 ,p_date_end => null --l_date_end
1075 ,p_frequency => p_frequency
1076 ,p_name => l_name
1077 ,p_probation_period => p_probation_period
1078 ,p_probation_period_unit_cd => p_probation_period_units
1079 ,p_replacement_required_flag => p_replacement_required_flag
1080 ,p_time_normal_finish => p_time_normal_finish
1081 ,p_time_normal_start => p_time_normal_start
1082 ,p_status => p_status
1083 ,p_working_hours => p_working_hours
1084 ,p_attribute_category => p_attribute_category
1085 ,p_attribute1 => p_attribute1
1086 ,p_attribute2 => p_attribute2
1087 ,p_attribute3 => p_attribute3
1088 ,p_attribute4 => p_attribute4
1089 ,p_attribute5 => p_attribute5
1090 ,p_attribute6 => p_attribute6
1091 ,p_attribute7 => p_attribute7
1092 ,p_attribute8 => p_attribute8
1093 ,p_attribute9 => p_attribute9
1094 ,p_attribute10 => p_attribute10
1095 ,p_attribute11 => p_attribute11
1096 ,p_attribute12 => p_attribute12
1097 ,p_attribute13 => p_attribute13
1098 ,p_attribute14 => p_attribute14
1099 ,p_attribute15 => p_attribute15
1100 ,p_attribute16 => p_attribute16
1101 ,p_attribute17 => p_attribute17
1102 ,p_attribute18 => p_attribute18
1103 ,p_attribute19 => p_attribute19
1104 ,p_attribute20 => p_attribute20
1105 ,p_segment1 => l_segment1
1106 ,p_segment2 => l_segment2
1107 ,p_segment3 => l_segment3
1108 ,p_segment4 => l_segment4
1109 ,p_segment5 => l_segment5
1110 ,p_segment6 => l_segment6
1111 ,p_segment7 => l_segment7
1112 ,p_segment8 => l_segment8
1113 ,p_segment9 => l_segment9
1114 ,p_segment10 => l_segment10
1115 ,p_segment11 => l_segment11
1116 ,p_segment12 => l_segment12
1117 ,p_segment13 => l_segment13
1118 ,p_segment14 => l_segment14
1119 ,p_segment15 => l_segment15
1120 ,p_segment16 => l_segment16
1121 ,p_segment17 => l_segment17
1122 ,p_segment18 => l_segment18
1123 ,p_segment19 => l_segment19
1124 ,p_segment20 => l_segment20
1125 ,p_segment21 => l_segment21
1126 ,p_segment22 => l_segment22
1127 ,p_segment23 => l_segment23
1128 ,p_segment24 => l_segment24
1129 ,p_segment25 => l_segment25
1130 ,p_segment26 => l_segment26
1131 ,p_segment27 => l_segment27
1132 ,p_segment28 => l_segment28
1133 ,p_segment29 => l_segment29
1134 ,p_segment30 => l_segment30
1135 ,p_concat_segments => p_concat_segments
1136 ,p_object_version_number => l_object_version_number
1137 ,p_valid_grades_changed_warning => l_valid_grades_changed1
1138 --,p_maintain_valid_grade_warning => l_valid_grades_changed1
1139 ,p_datetrack_mode => 'CORRECTION'
1140 ,p_validate => p_validate
1141 );
1142 --
1143 if g_debug then
1144 hr_utility.set_location(l_proc, 45);
1145 end if;
1146 -- Create Eliminated Record
1147 -- fnd_message.set_name('PQH','DATE-'||l_object_version_number);
1148 -- fnd_message.raise_error;
1149
1150 -- changed it for bugfix 2997103
1151 if ( nvl(hr_psf_shd.g_old_rec.date_end, hr_api.g_date) <>
1152 nvl(p_date_end, hr_api.g_date)
1153 and p_date_end is not null) then
1154
1155 create_eliminated_rec(
1156 p_position_id => p_position_id,
1157 p_object_version_number => l_object_version_number,
1158 p_effective_date => l_date_end+1);
1159 end if;
1160 if g_debug then
1161 hr_utility.set_location(l_proc, 50);
1162 end if;
1163 --
1164 -- get the updated Object version number of the per_all_positions table which
1165 -- is to be returned to the user as ovn of updated row.
1166 --
1167 p_object_version_number := per_refresh_position.get_position_ovn;
1168 --
1169 if g_debug then
1170 hr_utility.set_location('per_all_ovn is '||p_object_version_number||l_proc,9);
1171 end if;
1172 --
1173 --
1174 -- When in validation only mode raise the Validate_Enabled exception
1175 --
1176 if p_validate then
1177 raise hr_api.validate_enabled;
1178 end if;
1179 --
1180 if l_valid_grades_changed1 or l_valid_grades_changed2 then
1181 p_valid_grades_changed_warning := TRUE;
1182 else
1183 p_valid_grades_changed_warning := FALSE;
1184 end if;
1185 p_position_definition_id := l_position_definition_id;
1186 p_name := l_name;
1187 --
1188 reset_hr_installation;
1189 --
1190 if g_debug then
1191 hr_utility.set_location(' Leaving:'||l_proc, 11);
1192 end if;
1193 exception
1194 when hr_api.validate_enabled then
1195 --
1196 -- As the Validate_Enabled exception has been raised
1197 -- we must rollback to the savepoint
1198 --
1199 ROLLBACK TO update_position;
1200 --
1201 reset_hr_installation;
1202 --
1203 --
1204 -- Only set output warning arguments
1205 -- (Any key or derived arguments must be set to null
1206 -- when validation only mode is being used.)
1207 --
1208 if l_valid_grades_changed1 or l_valid_grades_changed2 then
1209 p_valid_grades_changed_warning := TRUE;
1210 else
1211 p_valid_grades_changed_warning := FALSE;
1212 end if;
1213 if l_null_ind = 0
1214 then
1215 p_position_definition_id := null;
1216 end if;
1217 p_name := null;
1218 --
1219 when others then
1220 --
1221 -- A validation or unexpected error has occurred
1222 --
1223 -- Added as part of the fix to bug 632479
1224 --
1225 p_object_version_number := l_object_version_number;
1226 p_name := l_name;
1227 p_position_definition_id := l_position_definition_id;
1228 p_valid_grades_changed_warning := null;
1229
1230 ROLLBACK TO update_position;
1231 --
1232 reset_hr_installation;
1233 --
1234 raise;
1235 --
1236 end update_position;
1237 --
1238 --
1239 -- ----------------------------------------------------------------------------
1240 -- |------------------------< get_dt_position_esd >---------------------------|
1241 -- ----------------------------------------------------------------------------
1242 --
1243 function get_dt_position_esd (p_position_id number)
1244 return date is
1245 cursor c1 is select min(effective_start_date)
1246 from hr_all_positions_f
1247 where position_id = p_position_id;
1248 l_proc varchar2(72) ;
1249 l_esd date;
1250 begin
1251 if g_debug then
1252 l_proc := g_package||'get_dt_position_esd' ;
1253 hr_utility.set_location(' Entering:'||l_proc, 5);
1254 end if;
1255 open c1;
1256 fetch c1 into l_esd;
1257 close c1;
1258 return l_esd;
1259 if c1%notfound then
1260 close c1;
1261 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1262 hr_utility.set_message_token('Function', l_proc);
1263 hr_utility.set_message_token('STEP','5');
1264 hr_utility.raise_error;
1265 end if;
1266 if g_debug then
1267 hr_utility.set_location(' Leaving:'||l_proc, 30);
1268 end if;
1269 end get_dt_position_esd;
1270 --
1271 procedure delete_position(
1272 p_validate boolean default false,
1273 p_position_id number,
1274 p_object_version_number number) is
1275 l_object_version_number number;
1276 l_proc varchar2(72);
1277 l_effective_date date;
1278 l_effective_start_date date;
1279 l_effective_end_date date;
1280 --
1281 l_return boolean;
1282 l_status varchar2(1);
1283 l_industry varchar2(1);
1284 --
1285 begin
1286 --
1287 g_debug := hr_utility.debug_enabled;
1288 if g_debug then
1289 l_proc := g_package||'delete_position';
1290 hr_utility.set_location('Entering:'|| l_proc, 5);
1291 end if;
1292 --
1293 -- Issue a savepoint
1294 --
1295 savepoint delete_position;
1296 --
1297 get_hr_installation;
1298 --
1299 --
1300 -- esd of date tracked table is fetched for passing
1301 --
1302 l_effective_date := get_dt_position_esd(p_position_id);
1303 --
1304 -- ovn of date tracked table is fetched for passing
1305 --
1306 l_object_version_number := get_dt_position_ovn(p_position_id,l_effective_date);
1307 --
1308 hr_position_api.delete_position
1309 (
1310 p_validate => p_validate
1311 ,p_position_id => p_position_id
1312 ,p_effective_start_date => l_effective_start_date
1313 ,p_effective_end_date => l_effective_end_date
1314 ,p_object_version_number => l_object_version_number
1315 ,p_effective_date => l_effective_date
1316 ,p_datetrack_mode => 'ZAP'
1317 );
1318 --
1319 reset_hr_installation;
1320 --
1321 if g_debug then
1322 hr_utility.set_location('Exiting:'|| l_proc, 30);
1323 end if;
1324 --
1325 exception
1326 when others then
1327 --
1328 -- As the Validate_Enabled exception has been raised
1329 -- we must rollback to the savepoint
1330 --
1331 ROLLBACK TO delete_position;
1332 --
1333 --
1334 reset_hr_installation;
1335 --
1336 raise;
1337 end;
1338 --
1339 procedure lck
1340 (
1341 p_position_id in number
1342 ,p_object_version_number in number
1343 ,p_language_code in varchar2 default hr_api.userenv_lang
1344 ) is
1345 l_proc varchar2(72);
1346 l_effective_date date;
1347 l_object_version_number number;
1348 l_validation_start_date date;
1349 l_validation_end_date date;
1350 l_datetrack_mode varchar2(50) := 'ZAP';
1351 --
1352 begin
1353 --
1354
1355 g_debug := hr_utility.debug_enabled;
1356 if g_debug then
1357 l_proc := g_package||'lck';
1358 hr_utility.set_location('Entering:'|| l_proc, 10);
1359 end if;
1360 --
1361 --
1362 -- esd of date tracked table is fetched for passing
1363 --
1364 l_effective_date := get_dt_position_esd(p_position_id);
1365 --
1366 --
1367 -- ovn of date tracked table is fetched for passing
1368 --
1369 l_object_version_number := get_dt_position_ovn(p_position_id,l_effective_date);
1370 --
1371 hr_position_api.lck
1372 (
1373 p_position_id => p_position_id
1374 ,p_validation_start_date => l_validation_start_date
1375 ,p_validation_end_date => l_validation_end_date
1376 ,p_object_version_number => l_object_version_number
1377 ,p_effective_date => l_effective_date
1378 ,p_datetrack_mode => l_datetrack_mode
1379 );
1380 --
1381 if g_debug then
1382 hr_utility.set_location(' Leaving:'||l_proc, 70);
1383 end if;
1384 --
1385
1386 end;
1387 --
1388 --
1389 --
1390 -- date tracked position api code will be inserted here
1391 --
1392 -- ----------------------------------------------------------------------------
1393 -- |------------------------< maintain_valid_grades >-------------------------|
1394 -- ----------------------------------------------------------------------------
1395 -- {Start Of Comments}
1396 --
1397 -- Description:
1398 -- This internal procedure maintains valid grades for a position when the
1399 -- date effective or date end of a position is updated.
1400
1401 -- If the Position Date Effective is being updated, then valid grades with
1402 -- a date to which is earlier than that Date Effective are deleted.
1403 -- Valid Grades with a date from which is earlier than the Position Date
1404 -- Effective and a date to which is later than the Position Date Effective
1405 -- or null are update with their Date From set to the Position Date
1406 -- Effective.
1407 --
1408 -- If the Position Date End is being updated, valid grades with a date from
1409 -- which is later than the end date of the position are deleted. Valid
1410 -- Grades with a date from which is earlier then the position end date and a
1411 -- date to which is later than the position end date or null are updated with
1412 -- their date to set to the position end date.
1413 --
1414 -- Prerequisites:
1415 -- A valid position (p_position_id) must exist.
1416 --
1417 -- In Parameters:
1418 -- Name Reqd Type Description
1419 -- p_validate Y boolean Default False
1420 -- p_position_id Y number
1421 -- p_maintenance_mode Y varchar2 Indicates whether the
1422 -- position date effective or
1423 -- the position date end has
1424 -- been update. Valid values
1425 -- are 'DATE_EFFECTIVE' and
1426 -- 'DATE_END'.
1427 -- p_date_effective N date Position date effective
1428 -- p_date_end N date Position date end
1429 --
1430 --
1431 -- Post Success:
1432 --
1433 -- Name Type Description
1434 -- p_valid_grades_changed boolean Only set to true if any valid
1435 -- grade rows have been updated
1436 -- or deleted.
1437 --
1438 -- Post Failure:
1439 --
1440 -- Access Status:
1441 -- Internal Development Use Only.
1442 --
1443 -- {End Of Comments}
1444 -- ---------------------------------------------------------------------------
1445 procedure maintain_valid_grades
1446 (
1447 p_validate in boolean default false
1448 ,p_position_id in number
1449 ,p_maintenance_mode in varchar2
1450 ,p_date_effective in date
1451 ,p_date_end in date
1452 ,p_valid_grades_changed out nocopy boolean
1453 )
1454 is
1455 --
1456 l_proc varchar2(72) ;
1457 l_valid_grade_changed boolean default FALSE;
1458 --
1459 cursor csr_valid_grades is
1460 select
1461 vgr.valid_grade_id valid_grade_id
1462 ,vgr.object_version_number object_version_number
1463 ,vgr.date_from date_from
1464 ,vgr.date_to date_to
1465 from per_valid_grades vgr
1466 where vgr.position_id = p_position_id;
1467 --
1468 begin
1469 if g_debug then
1470 l_proc := g_package||'maintain_valid_grades';
1471 hr_utility.set_location('Entering:'|| l_proc, 5);
1472 end if;
1473 --
1474 IF p_maintenance_mode = 'DATE_EFFECTIVE' THEN
1475 --
1476 -- When maintain_valid_grades has been called to maintain the valid grades
1477 -- for a position in accordance with that position's new Date Effective,
1478 -- (ie. p_maintenance_mode = 'DATE_EFFECTIVE') then the p_date_effective
1479 -- parameter should have been set.
1480 --
1481 hr_api.mandatory_arg_error
1482 (p_api_name => l_proc
1483 ,p_argument => 'date_effective'
1484 ,p_argument_value => p_date_effective);
1485 --
1486 if g_debug then
1487 hr_utility.set_location(l_proc, 10);
1488 end if;
1489 --
1490 FOR c_vgr_rec IN csr_valid_grades LOOP
1491 --
1492 -- If a valid grade for the position has a Date From that is earlier
1493 -- than the new Date Effective of the position and a Date To that is
1494 -- later than that new Date Effective or is null, then update that
1495 -- valid grade's Date From to that new Date Effective.
1496 --
1497 if g_debug then
1498 hr_utility.set_location(l_proc, 15);
1499 end if;
1500 if (c_vgr_rec.date_from < p_date_effective and
1501 nvl(c_vgr_rec.date_to, hr_api.g_eot) > p_date_effective ) then
1502 --
1503 if g_debug then
1504 hr_utility.set_location(l_proc, 20);
1505 end if;
1506 --
1507 per_vgr_upd.upd
1508 (p_valid_grade_id => c_vgr_rec.valid_grade_id
1509 ,p_object_version_number => c_vgr_rec.object_version_number
1510 ,p_date_from => p_date_effective
1511 ,p_validate => p_validate
1512 ,p_effective_date => p_date_effective); --Added for Bug# 1760707
1513 --
1514 l_valid_grade_changed := TRUE;
1515 --
1516 -- Else if valid grades exist for the position which have a date to that
1517 -- is earlier than the new Date Effective for the position then delete
1518 -- those valid grades.
1519 --
1520 --
1521 elsif (c_vgr_rec.date_to < p_date_effective) then
1522 --
1523 if g_debug then
1524 hr_utility.set_location(l_proc, 25);
1525 end if;
1526 --
1527 per_vgr_del.del
1528 (p_valid_grade_id => c_vgr_rec.valid_grade_id
1529 ,p_object_version_number => c_vgr_rec.object_version_number
1530 ,p_validate => p_validate);
1531 --
1532 l_valid_grade_changed := TRUE;
1533 --
1534 --
1535 end if;
1536 --
1537 END LOOP;
1538 --
1539 ELSE
1540 --
1541 -- When maintain_valid_grades has been called to maintain the valid grades
1542 -- for a position in accordance with that position's new Date End,
1543 -- (ie. p_maintenance_mode = 'DATE_END') then the p_date_end parameter
1544 -- should have been set.
1545 --
1546 hr_api.mandatory_arg_error
1547 (p_api_name => l_proc
1548 ,p_argument => 'date_end'
1549 ,p_argument_value => p_date_end);
1550 --
1551 if g_debug then
1552 hr_utility.set_location(l_proc, 30);
1553 end if;
1554 --
1555 for c_vgr_rec in csr_valid_grades loop
1556 --
1557 -- If a valid grade for the position has a Date From that is earlier
1558 -- than the new End Date of the position and a Date To that is later than
1559 -- that new End Date or is null, then update that valid grade's Date To
1560 -- to that new End Date.
1561 --
1562 if g_debug then
1563 hr_utility.set_location(l_proc, 35);
1564 end if;
1565 --
1566 if (c_vgr_rec.date_from < p_date_end and
1567 nvl(c_vgr_rec.date_to, hr_api.g_eot) > p_date_end ) then
1568 --
1569 if g_debug then
1570 hr_utility.set_location(l_proc, 40);
1571 end if;
1572 --
1573 per_vgr_upd.upd
1574 (p_valid_grade_id => c_vgr_rec.valid_grade_id
1575 ,p_object_version_number => c_vgr_rec.object_version_number
1576 ,p_date_to => p_date_end
1577 ,p_validate => p_validate
1578 ,p_effective_date => p_date_effective); --Added for Bug#1760707
1579 --
1580 l_valid_grade_changed := TRUE;
1581 --
1582 -- Else if valid grades exist for the position which have a date from that
1583 -- is later than the new End Date for the position then delete those
1584 -- valid grades.
1585 --
1586 elsif (c_vgr_rec.date_from > p_date_end) then
1587 --
1588 if g_debug then
1589 hr_utility.set_location(l_proc, 45);
1590 end if;
1591 --
1592 per_vgr_del.del
1593 (p_valid_grade_id => c_vgr_rec.valid_grade_id
1594 ,p_object_version_number => c_vgr_rec.object_version_number
1595 ,p_validate => p_validate);
1596 --
1597 l_valid_grade_changed := TRUE;
1598 --
1599 end if;
1600 --
1601 END LOOP;
1602 --
1603 END IF;
1604 --
1605 if g_debug then
1606 hr_utility.set_location(l_proc, 50);
1607 end if;
1608 --
1609 p_valid_grades_changed := l_valid_grade_changed;
1610 if g_debug then
1611 hr_utility.set_location('Leaving: '||l_proc, 55);
1612 end if;
1613 --
1614 end maintain_valid_grades;
1615 --
1616 --
1617 -- Procedure synchronize_per_all_positions
1618 --
1619 Procedure synchronize_per_all_positions
1620 (p_position_id in hr_all_positions_f.position_id%TYPE
1621 ,p_effective_date in date
1622 ,p_datetrack_mode in varchar2
1623 ,p_object_version_number in out nocopy hr_all_positions_f.object_version_number%TYPE
1624 ) is
1625
1626 --
1627 l_ovn number;
1628 l_esd date;
1629 l_eed date;
1630 l_lck_mode varchar2(100):=hr_api.g_future_change;
1631 l_proc varchar2(30);
1632 --
1633 cursor c1 (p_position_id in number) is
1634 select object_version_number
1635 from per_all_positions
1636 where position_id = p_position_id;
1637 --
1638 --
1639 begin
1640
1641 if g_debug then
1642 l_proc :='synchronize_per_all_positions';
1643 hr_utility.set_location('Entering:'||l_proc, 1);
1644 end if;
1645 --
1646 -- Check mandatory parameters have been set
1647 --
1648 hr_api.mandatory_arg_error
1649 (p_api_name => l_proc
1650 ,p_argument => 'position_id'
1651 ,p_argument_value => p_position_id
1652 );
1653 if g_debug then
1654 hr_utility.set_location(l_proc, 20);
1655 end if;
1656 --
1657 hr_api.mandatory_arg_error
1658 (p_api_name => l_proc
1659 ,p_argument => 'effective_date'
1660 ,p_argument_value => p_effective_date
1661 );
1662 if g_debug then
1663 hr_utility.set_location(l_proc, 30);
1664 end if;
1665 --
1666 /*
1667 hr_api.mandatory_arg_error
1668 (p_api_name => l_proc
1669 ,p_argument => 'datetrack_mode'
1670 ,p_argument_value => p_datetrack_mode
1671 );
1672 */
1673 --
1674 if p_datetrack_mode = hr_api.g_zap then
1675 --
1676 -- lock row in per_all_positions
1677 --
1678 if g_debug then
1679 hr_utility.set_location(l_proc, 40);
1680 end if;
1681 --
1682 open c1(p_position_id);
1683 fetch c1 into l_ovn;
1684 if c1%notfound then
1685 close c1;
1686 /*
1687 if g_debug then
1688 hr_utility.set_location(l_proc, 50);
1689 end if;
1690 hr_utility.set_message(801, 'HR_7220_INVALID_PRIMARY_KEY');
1691 hr_utility.raise_error;
1692 */
1693 else
1694 close c1;
1695 --
1696 if g_debug then
1697 hr_utility.set_location(l_proc, 60);
1698 end if;
1699 --
1700 per_pos_shd.lck(
1701 p_position_id => p_position_id
1702 ,p_object_version_number => l_ovn);
1703 --
1704 -- delete row from per_all_positions
1705 --
1706 if g_debug then
1707 hr_utility.set_location(l_proc, 70);
1708 end if;
1709 --
1710 per_pos_del.del(
1711 p_position_id => p_position_id
1712 ,p_object_version_number => l_ovn);
1713 --
1714 if g_debug then
1715 hr_utility.set_location(l_proc, 80);
1716 end if;
1717 --
1718 end if;
1719 elsif p_datetrack_mode = hr_api.g_update or
1720 p_datetrack_mode = hr_api.g_update_change_insert or
1721 p_datetrack_mode = hr_api.g_correction or
1722 p_datetrack_mode = hr_api.g_delete or
1723 p_datetrack_mode = hr_api.g_delete_next_change or
1724 p_datetrack_mode = hr_api.g_future_change or
1725 p_datetrack_mode is null
1726 then
1727 --
1728 -- update in per_all_positions table
1729 --
1730 begin
1731 -- if l_effective_end_date <> hr_api.g_eot then
1732 -- lock the position rows explicitly
1733 if g_debug then
1734 hr_utility.set_location(l_proc, 80);
1735 end if;
1736
1737 l_ovn := p_object_version_number;
1738 --
1739 /*
1740 if p_datetrack_mode is not null then
1741 hr_psf_shd.lck
1742 (p_position_id => p_position_id
1743 ,p_datetrack_mode => l_lck_mode
1744 ,p_effective_Date => p_effective_Date - 1
1745 ,p_object_version_number => l_ovn
1746 ,p_validation_start_date => l_esd
1747 ,p_validation_end_date => l_eed
1748 );
1749 end if;
1750 */
1751 --
1752 if g_debug then
1753 hr_utility.set_location(l_proc, 90);
1754 end if;
1755 --
1756 -- update copied_to_old_position_flag in all the following rows
1757 update hr_all_positions_f
1758 set copied_to_old_table_flag = 'N'
1759 where position_id = p_position_id and
1760 effective_end_date >
1761 decode( p_datetrack_mode
1762 ,hr_api.g_update, p_effective_date
1763 ,hr_api.g_correction, p_effective_date
1764 ,hr_api.g_update_change_insert, p_effective_date
1765 ,hr_api.g_delete, p_effective_date - 1
1766 ,hr_api.g_delete_next_change, p_effective_date - 1
1767 ,hr_api.g_future_change, p_effective_date - 1
1768 );
1769 --
1770 if g_debug then
1771 hr_utility.set_location(l_proc, 100);
1772 end if;
1773 -- call refresh_position procedure to replicate changes in
1774 -- per_all_positions
1775 --
1776 per_refresh_position.refresh_single_position
1777 (p_position_id => p_position_id
1778 ,p_effective_date => p_effective_date
1779 ,p_object_version_number => p_object_version_number
1780 ,p_refresh_date => trunc(sysdate));
1781 --
1782 if g_debug then
1783 hr_utility.set_location(l_proc, 110);
1784 end if;
1785 -- end if;
1786 end;
1787 end if;
1788 if g_debug then
1789 hr_utility.set_location( 'Leaving : ' || l_proc, 200);
1790 end if;
1791 --
1792
1793 end synchronize_per_all_positions;
1794 --
1795 --
1796 -- ----------------------------------------------------------------------------
1797 -- |------------------------< create_position >----------------------|
1798 -- ----------------------------------------------------------------------------
1799 --
1800 procedure create_position
1801 (p_position_id out nocopy number
1802 ,p_effective_start_date out nocopy date
1803 ,p_effective_end_date out nocopy date
1804 ,p_position_definition_id in out nocopy number
1805 ,p_name in out nocopy varchar2
1806 ,p_object_version_number out nocopy number
1807 ,p_job_id in number
1808 ,p_organization_id in number
1809 ,p_effective_date in date
1810 ,p_date_effective in date
1811 ,p_language_code in varchar2 default hr_api.userenv_lang
1812 ,p_validate in boolean default false
1813 ,p_availability_status_id in number default null
1814 ,p_business_group_id in number default null
1815 ,p_entry_step_id in number default null
1816 ,p_entry_grade_rule_id in number default null
1817 ,p_location_id in number default null
1818 ,p_pay_freq_payroll_id in number default null
1819 ,p_position_transaction_id in number default null
1820 ,p_prior_position_id in number default null
1821 ,p_relief_position_id in number default null
1822 ,p_entry_grade_id in number default null
1823 ,p_successor_position_id in number default null
1824 ,p_supervisor_position_id in number default null
1825 ,p_amendment_date in date default null
1826 ,p_amendment_recommendation in varchar2 default null
1827 ,p_amendment_ref_number in varchar2 default null
1828 ,p_bargaining_unit_cd in varchar2 default null
1829 ,p_comments in long default null
1830 ,p_current_job_prop_end_date in date default null
1831 ,p_current_org_prop_end_date in date default null
1832 ,p_avail_status_prop_end_date in date default null
1833 ,p_date_end in date default null
1834 ,p_earliest_hire_date in date default null
1835 ,p_fill_by_date in date default null
1836 ,p_frequency in varchar2 default null
1837 ,p_fte in number default null
1838 ,p_max_persons in number default null
1839 ,p_overlap_period in number default null
1840 ,p_overlap_unit_cd in varchar2 default null
1841 ,p_pay_term_end_day_cd in varchar2 default null
1842 ,p_pay_term_end_month_cd in varchar2 default null
1843 ,p_permanent_temporary_flag in varchar2 default null
1844 ,p_permit_recruitment_flag in varchar2 default null
1845 ,p_position_type in varchar2 default 'NONE'
1846 ,p_posting_description in varchar2 default null
1847 ,p_probation_period in number default null
1848 ,p_probation_period_unit_cd in varchar2 default null
1849 ,p_replacement_required_flag in varchar2 default null
1850 ,p_review_flag in varchar2 default null
1851 ,p_seasonal_flag in varchar2 default null
1852 ,p_security_requirements in varchar2 default null
1853 ,p_status in varchar2 default null
1854 ,p_term_start_day_cd in varchar2 default null
1855 ,p_term_start_month_cd in varchar2 default null
1856 ,p_time_normal_finish in varchar2 default null
1857 ,p_time_normal_start in varchar2 default null
1858 ,p_update_source_cd in varchar2 default null
1859 ,p_working_hours in number default null
1860 ,p_works_council_approval_flag in varchar2 default null
1861 ,p_work_period_type_cd in varchar2 default null
1862 ,p_work_term_end_day_cd in varchar2 default null
1863 ,p_work_term_end_month_cd in varchar2 default null
1864 ,p_proposed_fte_for_layoff in number default null
1865 ,p_proposed_date_for_layoff in date default null
1866 ,p_pay_basis_id in number default null
1867 ,p_supervisor_id in number default null
1868 --,p_copied_to_old_table_flag in varchar2 default null
1869 ,p_information1 in varchar2 default null
1870 ,p_information2 in varchar2 default null
1871 ,p_information3 in varchar2 default null
1872 ,p_information4 in varchar2 default null
1873 ,p_information5 in varchar2 default null
1874 ,p_information6 in varchar2 default null
1875 ,p_information7 in varchar2 default null
1876 ,p_information8 in varchar2 default null
1877 ,p_information9 in varchar2 default null
1878 ,p_information10 in varchar2 default null
1879 ,p_information11 in varchar2 default null
1880 ,p_information12 in varchar2 default null
1881 ,p_information13 in varchar2 default null
1882 ,p_information14 in varchar2 default null
1883 ,p_information15 in varchar2 default null
1884 ,p_information16 in varchar2 default null
1885 ,p_information17 in varchar2 default null
1886 ,p_information18 in varchar2 default null
1887 ,p_information19 in varchar2 default null
1888 ,p_information20 in varchar2 default null
1889 ,p_information21 in varchar2 default null
1890 ,p_information22 in varchar2 default null
1891 ,p_information23 in varchar2 default null
1892 ,p_information24 in varchar2 default null
1893 ,p_information25 in varchar2 default null
1894 ,p_information26 in varchar2 default null
1895 ,p_information27 in varchar2 default null
1896 ,p_information28 in varchar2 default null
1897 ,p_information29 in varchar2 default null
1898 ,p_information30 in varchar2 default null
1899 ,p_information_category in varchar2 default null
1900 ,p_attribute1 in varchar2 default null
1901 ,p_attribute2 in varchar2 default null
1902 ,p_attribute3 in varchar2 default null
1903 ,p_attribute4 in varchar2 default null
1904 ,p_attribute5 in varchar2 default null
1905 ,p_attribute6 in varchar2 default null
1906 ,p_attribute7 in varchar2 default null
1907 ,p_attribute8 in varchar2 default null
1908 ,p_attribute9 in varchar2 default null
1909 ,p_attribute10 in varchar2 default null
1910 ,p_attribute11 in varchar2 default null
1911 ,p_attribute12 in varchar2 default null
1912 ,p_attribute13 in varchar2 default null
1913 ,p_attribute14 in varchar2 default null
1914 ,p_attribute15 in varchar2 default null
1915 ,p_attribute16 in varchar2 default null
1916 ,p_attribute17 in varchar2 default null
1917 ,p_attribute18 in varchar2 default null
1918 ,p_attribute19 in varchar2 default null
1919 ,p_attribute20 in varchar2 default null
1920 ,p_attribute21 in varchar2 default null
1921 ,p_attribute22 in varchar2 default null
1922 ,p_attribute23 in varchar2 default null
1923 ,p_attribute24 in varchar2 default null
1924 ,p_attribute25 in varchar2 default null
1925 ,p_attribute26 in varchar2 default null
1926 ,p_attribute27 in varchar2 default null
1927 ,p_attribute28 in varchar2 default null
1928 ,p_attribute29 in varchar2 default null
1929 ,p_attribute30 in varchar2 default null
1930 ,p_attribute_category in varchar2 default null
1931 ,p_segment1 in varchar2 default null
1932 ,p_segment2 in varchar2 default null
1933 ,p_segment3 in varchar2 default null
1934 ,p_segment4 in varchar2 default null
1935 ,p_segment5 in varchar2 default null
1936 ,p_segment6 in varchar2 default null
1937 ,p_segment7 in varchar2 default null
1938 ,p_segment8 in varchar2 default null
1939 ,p_segment9 in varchar2 default null
1940 ,p_segment10 in varchar2 default null
1941 ,p_segment11 in varchar2 default null
1942 ,p_segment12 in varchar2 default null
1943 ,p_segment13 in varchar2 default null
1944 ,p_segment14 in varchar2 default null
1945 ,p_segment15 in varchar2 default null
1946 ,p_segment16 in varchar2 default null
1947 ,p_segment17 in varchar2 default null
1948 ,p_segment18 in varchar2 default null
1949 ,p_segment19 in varchar2 default null
1950 ,p_segment20 in varchar2 default null
1951 ,p_segment21 in varchar2 default null
1952 ,p_segment22 in varchar2 default null
1953 ,p_segment23 in varchar2 default null
1954 ,p_segment24 in varchar2 default null
1955 ,p_segment25 in varchar2 default null
1956 ,p_segment26 in varchar2 default null
1957 ,p_segment27 in varchar2 default null
1958 ,p_segment28 in varchar2 default null
1959 ,p_segment29 in varchar2 default null
1960 ,p_segment30 in varchar2 default null
1961 ,p_concat_segments in varchar2 default null
1962 ,p_request_id in number default null
1963 ,p_program_application_id in number default null
1964 ,p_program_id in number default null
1965 ,p_program_update_date in date default null
1966 ,p_security_profile_id in number default hr_security.get_security_profile
1967 ) is
1968 --
1969 -- Declare cursors and local variables
1970 --
1971 l_position_id hr_all_positions_f.position_id%TYPE;
1972 l_effective_start_date hr_all_positions_f.effective_start_date%TYPE;
1973 l_effective_end_date hr_all_positions_f.effective_end_date%TYPE;
1974 l_proc varchar2(72) ;
1975 l_object_version_number hr_all_positions_f.object_version_number%TYPE;
1976 --
1977 l_language_code fnd_languages.language_code%TYPE;
1978 --
1979 l_business_group_id hr_all_positions_f.business_group_id%TYPE;
1980 l_position_definition_id hr_all_positions_f.position_definition_id%TYPE
1981 := p_position_definition_id;
1982 l_old_position_definition_id per_positions.position_definition_id%TYPE;
1983 l_flex_num fnd_id_flex_segments.id_flex_num%TYPE;
1984 l_pos_def_id_flex_num fnd_id_flex_segments.id_flex_num%TYPE;
1985 l_name hr_all_positions_f.name%TYPE
1986 := p_name;
1987 l_date_effective hr_all_positions_f.date_effective%TYPE;
1988 l_date_end hr_all_positions_f.date_end%TYPE;
1989 --
1990 l_active_status_id number;
1991 l_availability_Status_id number;
1992 l_copied_to_old_table_flag varchar2(30):='N';
1993 l_retcode varchar2(2000);
1994 l_errbuf varchar2(2000);
1995 l_view_all_positions_flag varchar2(30);
1996 l_dummy hr_all_positions_f.object_version_number%TYPE;
1997 --
1998 -- bug 2271064 set up segments as local variables
1999 --
2000 l_segment1 varchar2(60) := p_segment1;
2001 l_segment2 varchar2(60) := p_segment2;
2002 l_segment3 varchar2(60) := p_segment3;
2003 l_segment4 varchar2(60) := p_segment4;
2004 l_segment5 varchar2(60) := p_segment5;
2005 l_segment6 varchar2(60) := p_segment6;
2006 l_segment7 varchar2(60) := p_segment7;
2007 l_segment8 varchar2(60) := p_segment8;
2008 l_segment9 varchar2(60) := p_segment9;
2009 l_segment10 varchar2(60) := p_segment10;
2010 l_segment11 varchar2(60) := p_segment11;
2011 l_segment12 varchar2(60) := p_segment12;
2012 l_segment13 varchar2(60) := p_segment13;
2013 l_segment14 varchar2(60) := p_segment14;
2014 l_segment15 varchar2(60) := p_segment15;
2015 l_segment16 varchar2(60) := p_segment16;
2016 l_segment17 varchar2(60) := p_segment17;
2017 l_segment18 varchar2(60) := p_segment18;
2018 l_segment19 varchar2(60) := p_segment19;
2019 l_segment20 varchar2(60) := p_segment20;
2020 l_segment21 varchar2(60) := p_segment21;
2021 l_segment22 varchar2(60) := p_segment22;
2022 l_segment23 varchar2(60) := p_segment23;
2023 l_segment24 varchar2(60) := p_segment24;
2024 l_segment25 varchar2(60) := p_segment25;
2025 l_segment26 varchar2(60) := p_segment26;
2026 l_segment27 varchar2(60) := p_segment27;
2027 l_segment28 varchar2(60) := p_segment28;
2028 l_segment29 varchar2(60) := p_segment29;
2029 l_segment30 varchar2(60) := p_segment30;
2030 --
2031 -- bug 2271064 new variable to indicate whether key flex id parameter
2032 -- enters the program with a value.
2033 --
2034 l_null_ind number(1) := 0;
2035 --
2036 --
2037 cursor c_view_all_pos is
2038 select view_all_positions_flag
2039 from per_security_profiles
2040 where security_profile_id = p_security_profile_id;
2041 --
2042 cursor csr_job_bg is
2043 select business_group_id
2044 from per_jobs
2045 where job_id = p_job_id;
2046 --
2047 cursor isdel is
2048 select pbg.position_structure
2049 from per_business_groups pbg
2050 where pbg.business_group_id = l_business_group_id;
2051 --
2052 cursor csr_idsel is
2053 select pd.id_flex_num
2054 from per_position_definitions pd
2055 where pd.position_definition_id = l_position_definition_id;
2056 --
2057 cursor c1 is
2058 select SHARED_TYPE_ID
2059 from per_shared_types
2060 where LOOKUP_TYPE = 'POSITION_AVAILABILITY_STATUS'
2061 AND SYSTEM_TYPE_CD = 'ACTIVE'
2062 and (business_group_id = p_business_group_id or business_group_id is null);
2063 --
2064 cursor csr_get_nondt_pos(p_position_id in number) is
2065 select object_version_number
2066 from per_all_positions
2067 where position_id = p_position_id;
2068 --
2069 -- bug 2271064 get per_position_definitions segment values where
2070 -- position_definition_id is known
2071 --
2072 cursor c_segments is
2073 select segment1,
2074 segment2,
2075 segment3,
2076 segment4,
2077 segment5,
2078 segment6,
2079 segment7,
2080 segment8,
2081 segment9,
2082 segment10,
2083 segment11,
2084 segment12,
2085 segment13,
2086 segment14,
2087 segment15,
2088 segment16,
2089 segment17,
2090 segment18,
2091 segment19,
2092 segment20,
2093 segment21,
2094 segment22,
2095 segment23,
2096 segment24,
2097 segment25,
2098 segment26,
2099 segment27,
2100 segment28,
2101 segment29,
2102 segment30
2103 from per_position_definitions
2104 where position_definition_id = l_position_definition_id;
2105 --
2106 begin
2107 --
2108 g_debug := hr_utility.debug_enabled;
2109 if g_debug then
2110 l_proc := g_package||'create_position';
2111 hr_utility.set_location('Entering:'|| l_proc, 10);
2112 end if;
2113 --
2114 -- Issue a savepoint
2115 --
2116 savepoint create_position;
2117 --
2118 -- Validate the language parameter. l_language_code should be passed
2119 -- instead of p_language_code from now on, to allow an IN OUT parameter to
2120 -- be passed through.
2121 --
2122 l_language_code := p_language_code;
2123 hr_api.validate_language_code(p_language_code => l_language_code);
2124 --
2125 -- Get business_group_id using job.
2126 --
2127 if g_debug then
2128 hr_utility.set_location(l_proc, 10);
2129 end if;
2130 --
2131 open csr_job_bg;
2132 fetch csr_job_bg
2133 into l_business_group_id;
2134 --
2135 if csr_job_bg%notfound then
2136 close csr_job_bg;
2137 hr_utility.set_message(801, 'HR_51090_JOB_NOT_EXIST');
2138 hr_utility.raise_error;
2139 else
2140 close csr_job_bg;
2141 end if;
2142 --
2143 if g_debug then
2144 hr_utility.set_location(l_proc, 15);
2145 end if;
2146 --
2147 open isdel;
2148 fetch isdel into l_flex_num;
2149 if isdel%notfound then
2150 close isdel;
2151 --
2152 -- the flex structure has not been found
2153 --
2154 hr_utility.set_message(801, 'HR_7471_FLEX_PEA_INVLALID_ID');
2155 hr_utility.raise_error;
2156 end if;
2157 close isdel;
2158 --
2159 if g_debug then
2160 hr_utility.set_location(l_proc, 30);
2161 end if;
2162 --
2163 --
2164 l_date_effective := trunc(p_date_effective);
2165 l_date_end := trunc(p_date_end);
2166 --
2167 if g_debug then
2168 hr_utility.set_location(l_proc, 35);
2169 end if;
2170 --
2171 -- if p_availability_status_id is NULL then default it to 'ACTIVE' status_id
2172 --
2173 if p_availability_status_id is null then
2174 open c1;
2175 fetch c1 into l_availability_status_id;
2176 if c1%notfound then
2177 close c1;
2178 --
2179 hr_utility.set_message(801,'HR_INVALID_ACTIVE_POS_STATUS');
2180 hr_utility.raise_error;
2181 --
2182 else
2183 close c1;
2184 end if;
2185 else
2186 l_availability_Status_id := p_availability_status_id;
2187 end if;
2188 --
2189 if g_debug then
2190 hr_utility.set_location(l_proc, 20);
2191 end if;
2192 --
2193 -- 2242339 get segment values if p_job_definition_id entered with a value
2194 -- also get flex number for this flex structure.
2195 --
2196 if l_position_definition_id is not null
2197 --
2198 then
2199 --
2200 if g_debug then
2201 hr_utility.set_location(l_proc, 15);
2202 end if;
2203 --
2204 -- set indicator to show p_position_definition_id did not enter pgm null
2205 --
2206 l_null_ind := 1;
2207 --
2208 open c_segments;
2209 fetch c_segments into
2210 l_segment1,
2211 l_segment2,
2212 l_segment3,
2213 l_segment4,
2214 l_segment5,
2215 l_segment6,
2216 l_segment7,
2217 l_segment8,
2218 l_segment9,
2219 l_segment10,
2220 l_segment11,
2221 l_segment12,
2222 l_segment13,
2223 l_segment14,
2224 l_segment15,
2225 l_segment16,
2226 l_segment17,
2227 l_segment18,
2228 l_segment19,
2229 l_segment20,
2230 l_segment21,
2231 l_segment22,
2232 l_segment23,
2233 l_segment24,
2234 l_segment25,
2235 l_segment26,
2236 l_segment27,
2237 l_segment28,
2238 l_segment29,
2239 l_segment30;
2240 close c_segments;
2241 --
2242 open csr_idsel;
2243 fetch csr_idsel
2244 into l_pos_def_id_flex_num;
2245 if csr_idsel%NOTFOUND
2246 then
2247 close csr_idsel;
2248 /*-- start change for the bug 5682240
2249 hr_utility.set_message (801, 'No flex number for this position definition id'); -- orignal
2250 -------------------------- */
2251 hr_utility.set_location('No flex number for position definition id= '||l_position_definition_id, 630);
2252
2253 end if;
2254 close csr_idsel;
2255 else
2256 l_null_ind := 0;
2257 l_name := null;
2258 end if;
2259 --
2260 -- Process Logic
2261 --
2262 begin
2263 --
2264 -- Start of API User Hook for the before hook of create_position
2265 --
2266 hr_position_bk1.create_position_b
2267 (
2268 p_availability_status_id => l_availability_status_id
2269 ,p_business_group_id => l_business_group_id
2270 ,p_entry_step_id => p_entry_step_id
2271 ,p_entry_grade_rule_id => p_entry_grade_rule_id
2272 ,p_job_id => p_job_id
2273 ,p_location_id => p_location_id
2274 ,p_organization_id => p_organization_id
2275 ,p_pay_freq_payroll_id => p_pay_freq_payroll_id
2276 -- ,p_position_definition_id => p_position_definition_id
2277 ,p_position_transaction_id => p_position_transaction_id
2278 ,p_prior_position_id => p_prior_position_id
2279 ,p_relief_position_id => p_relief_position_id
2280 ,p_entry_grade_id => p_entry_grade_id
2281 ,p_successor_position_id => p_successor_position_id
2282 ,p_supervisor_position_id => p_supervisor_position_id
2283 ,p_amendment_date => p_amendment_date
2284 ,p_amendment_recommendation => p_amendment_recommendation
2285 ,p_amendment_ref_number => p_amendment_ref_number
2286 ,p_bargaining_unit_cd => p_bargaining_unit_cd
2287 ,p_comments => p_comments
2288 ,p_current_job_prop_end_date => p_current_job_prop_end_date
2289 ,p_current_org_prop_end_date => p_current_org_prop_end_date
2290 ,p_avail_status_prop_end_date => p_avail_status_prop_end_date
2291 ,p_date_effective => l_date_effective
2292 ,p_date_end => l_date_end
2293 ,p_earliest_hire_date => p_earliest_hire_date
2294 ,p_fill_by_date => p_fill_by_date
2295 ,p_frequency => p_frequency
2296 ,p_fte => p_fte
2297 ,p_max_persons => p_max_persons
2298 -- ,p_name => l_name --vb
2299 ,p_overlap_period => p_overlap_period
2300 ,p_overlap_unit_cd => p_overlap_unit_cd
2301 ,p_pay_term_end_day_cd => p_pay_term_end_day_cd
2302 ,p_pay_term_end_month_cd => p_pay_term_end_month_cd
2303 ,p_permanent_temporary_flag => p_permanent_temporary_flag
2304 ,p_permit_recruitment_flag => p_permit_recruitment_flag
2305 ,p_position_type => p_position_type
2306 ,p_posting_description => p_posting_description
2307 ,p_probation_period => p_probation_period
2308 ,p_probation_period_unit_cd => p_probation_period_unit_cd
2309 ,p_replacement_required_flag => p_replacement_required_flag
2310 ,p_review_flag => p_review_flag
2311 ,p_seasonal_flag => p_seasonal_flag
2312 ,p_security_requirements => p_security_requirements
2313 ,p_status => p_status
2314 ,p_term_start_day_cd => p_term_start_day_cd
2315 ,p_term_start_month_cd => p_term_start_month_cd
2316 ,p_time_normal_finish => p_time_normal_finish
2317 ,p_time_normal_start => p_time_normal_start
2318 ,p_update_source_cd => p_update_source_cd
2319 ,p_working_hours => p_working_hours
2320 ,p_works_council_approval_flag => p_works_council_approval_flag
2321 ,p_work_period_type_cd => p_work_period_type_cd
2322 ,p_work_term_end_day_cd => p_work_term_end_day_cd
2323 ,p_work_term_end_month_cd => p_work_term_end_month_cd
2324 ,p_proposed_fte_for_layoff => p_proposed_fte_for_layoff
2325 ,p_proposed_date_for_layoff => p_proposed_date_for_layoff
2326 ,p_pay_basis_id => p_pay_basis_id
2327 ,p_supervisor_id => p_supervisor_id
2328 --,p_copied_to_old_table_flag => l_copied_to_old_table_flag
2329 ,p_information1 => p_information1
2330 ,p_information2 => p_information2
2331 ,p_information3 => p_information3
2332 ,p_information4 => p_information4
2333 ,p_information5 => p_information5
2334 ,p_information6 => p_information6
2335 ,p_information7 => p_information7
2336 ,p_information8 => p_information8
2337 ,p_information9 => p_information9
2338 ,p_information10 => p_information10
2339 ,p_information11 => p_information11
2340 ,p_information12 => p_information12
2341 ,p_information13 => p_information13
2342 ,p_information14 => p_information14
2343 ,p_information15 => p_information15
2344 ,p_information16 => p_information16
2345 ,p_information17 => p_information17
2346 ,p_information18 => p_information18
2347 ,p_information19 => p_information19
2348 ,p_information20 => p_information20
2349 ,p_information21 => p_information21
2350 ,p_information22 => p_information22
2351 ,p_information23 => p_information23
2352 ,p_information24 => p_information24
2353 ,p_information25 => p_information25
2354 ,p_information26 => p_information26
2355 ,p_information27 => p_information27
2356 ,p_information28 => p_information28
2357 ,p_information29 => p_information29
2358 ,p_information30 => p_information30
2359 ,p_information_category => p_information_category
2360 ,p_attribute1 => p_attribute1
2361 ,p_attribute2 => p_attribute2
2362 ,p_attribute3 => p_attribute3
2363 ,p_attribute4 => p_attribute4
2364 ,p_attribute5 => p_attribute5
2365 ,p_attribute6 => p_attribute6
2366 ,p_attribute7 => p_attribute7
2367 ,p_attribute8 => p_attribute8
2368 ,p_attribute9 => p_attribute9
2369 ,p_attribute10 => p_attribute10
2370 ,p_attribute11 => p_attribute11
2371 ,p_attribute12 => p_attribute12
2372 ,p_attribute13 => p_attribute13
2373 ,p_attribute14 => p_attribute14
2374 ,p_attribute15 => p_attribute15
2375 ,p_attribute16 => p_attribute16
2376 ,p_attribute17 => p_attribute17
2377 ,p_attribute18 => p_attribute18
2378 ,p_attribute19 => p_attribute19
2379 ,p_attribute20 => p_attribute20
2380 ,p_attribute21 => p_attribute21
2381 ,p_attribute22 => p_attribute22
2382 ,p_attribute23 => p_attribute23
2383 ,p_attribute24 => p_attribute24
2384 ,p_attribute25 => p_attribute25
2385 ,p_attribute26 => p_attribute26
2386 ,p_attribute27 => p_attribute27
2387 ,p_attribute28 => p_attribute28
2388 ,p_attribute29 => p_attribute29
2389 ,p_attribute30 => p_attribute30
2390 ,p_attribute_category => p_attribute_category
2391 ,p_segment1 => l_segment1
2392 ,p_segment2 => l_segment2
2393 ,p_segment3 => l_segment3
2394 ,p_segment4 => l_segment4
2395 ,p_segment5 => l_segment5
2396 ,p_segment6 => l_segment6
2397 ,p_segment7 => l_segment7
2398 ,p_segment8 => l_segment8
2399 ,p_segment9 => l_segment9
2400 ,p_segment10 => l_segment10
2401 ,p_segment11 => l_segment11
2402 ,p_segment12 => l_segment12
2403 ,p_segment13 => l_segment13
2404 ,p_segment14 => l_segment14
2405 ,p_segment15 => l_segment15
2406 ,p_segment16 => l_segment16
2407 ,p_segment17 => l_segment17
2408 ,p_segment18 => l_segment18
2409 ,p_segment19 => l_segment19
2410 ,p_segment20 => l_segment20
2411 ,p_segment21 => l_segment21
2412 ,p_segment22 => l_segment22
2413 ,p_segment23 => l_segment23
2414 ,p_segment24 => l_segment24
2415 ,p_segment25 => l_segment25
2416 ,p_segment26 => l_segment26
2417 ,p_segment27 => l_segment27
2418 ,p_segment28 => l_segment28
2419 ,p_segment29 => l_segment29
2420 ,p_segment30 => l_segment30
2421 ,p_concat_segments => p_concat_segments
2422 ,p_request_id => p_request_id
2423 ,p_program_application_id => p_program_application_id
2424 ,p_program_id => p_program_id
2425 ,p_program_update_date => p_program_update_date
2426 ,p_effective_date => trunc(p_effective_date)
2427 ,p_language_code => l_language_code
2428 );
2429 exception
2430 when hr_api.cannot_find_prog_unit then
2431 hr_api.cannot_find_prog_unit_error
2432 (
2433 p_module_name => 'CREATE_POSITION'
2434 ,p_hook_type => 'BP'
2435 );
2436 --
2437 -- End of API User Hook for the before hook of create_position
2438 --
2439 end;
2440 --
2441 -- Determine the position defintion by calling ins_or_sel
2442 -- bug 2271064 - when position definition id is null.
2443 -- also make sure that name has a value... get it from the appropriate
2444 -- flex number
2445 --
2446 if l_position_definition_id is not null
2447 and l_name is null
2448 then
2449 hr_kflex_utility.ins_or_sel_keyflex_comb
2450 (p_appl_short_name => 'PER'
2451 ,p_flex_code => 'POS'
2452 ,p_flex_num => l_pos_def_id_flex_num
2453 ,p_segment1 => l_segment1
2454 ,p_segment2 => l_segment2
2455 ,p_segment3 => l_segment3
2456 ,p_segment4 => l_segment4
2457 ,p_segment5 => l_segment5
2458 ,p_segment6 => l_segment6
2459 ,p_segment7 => l_segment7
2460 ,p_segment8 => l_segment8
2461 ,p_segment9 => l_segment9
2462 ,p_segment10 => l_segment10
2463 ,p_segment11 => l_segment11
2464 ,p_segment12 => l_segment12
2465 ,p_segment13 => l_segment13
2466 ,p_segment14 => l_segment14
2467 ,p_segment15 => l_segment15
2468 ,p_segment16 => l_segment16
2469 ,p_segment17 => l_segment17
2470 ,p_segment18 => l_segment18
2471 ,p_segment19 => l_segment19
2472 ,p_segment20 => l_segment20
2473 ,p_segment21 => l_segment21
2474 ,p_segment22 => l_segment22
2475 ,p_segment23 => l_segment23
2476 ,p_segment24 => l_segment24
2477 ,p_segment25 => l_segment25
2478 ,p_segment26 => l_segment26
2479 ,p_segment27 => l_segment27
2480 ,p_segment28 => l_segment28
2481 ,p_segment29 => l_segment29
2482 ,p_segment30 => l_segment30
2483 ,p_ccid => l_old_position_definition_id
2484 ,p_concat_segments_out => l_name
2485 );
2486 end if;
2487 --
2488 if l_position_definition_id is null
2489 -- or l_name is null
2490 then
2491 if nvl(fnd_profile.value('FLEXFIELDS:VALIDATE_ON_SERVER'),'N') = 'Y'
2492 or l_name is null
2493 then
2494 hr_kflex_utility.ins_or_sel_keyflex_comb
2495 (p_appl_short_name => 'PER'
2496 ,p_flex_code => 'POS'
2497 ,p_flex_num => l_flex_num
2498 ,p_segment1 => l_segment1
2499 ,p_segment2 => l_segment2
2500 ,p_segment3 => l_segment3
2501 ,p_segment4 => l_segment4
2502 ,p_segment5 => l_segment5
2503 ,p_segment6 => l_segment6
2504 ,p_segment7 => l_segment7
2505 ,p_segment8 => l_segment8
2506 ,p_segment9 => l_segment9
2507 ,p_segment10 => l_segment10
2508 ,p_segment11 => l_segment11
2509 ,p_segment12 => l_segment12
2510 ,p_segment13 => l_segment13
2511 ,p_segment14 => l_segment14
2512 ,p_segment15 => l_segment15
2513 ,p_segment16 => l_segment16
2514 ,p_segment17 => l_segment17
2515 ,p_segment18 => l_segment18
2516 ,p_segment19 => l_segment19
2517 ,p_segment20 => l_segment20
2518 ,p_segment21 => l_segment21
2519 ,p_segment22 => l_segment22
2520 ,p_segment23 => l_segment23
2521 ,p_segment24 => l_segment24
2522 ,p_segment25 => l_segment25
2523 ,p_segment26 => l_segment26
2524 ,p_segment27 => l_segment27
2525 ,p_segment28 => l_segment28
2526 ,p_segment29 => l_segment29
2527 ,p_segment30 => l_segment30
2528 ,p_concat_segments_in => p_concat_segments
2529 ,p_ccid => l_position_definition_id
2530 ,p_concat_segments_out => l_name
2531 );
2532 end if;
2533 end if;
2534 --
2535 -- l_position_definition_id now has a value, whether it entered with one
2536 -- or not. ditto l_name.
2537 --
2538 hr_psf_ins.ins
2539 (
2540 p_position_id => l_position_id
2541 ,p_effective_start_date => l_effective_start_date
2542 ,p_effective_end_date => l_effective_end_date
2543 ,p_availability_status_id => l_availability_status_id
2544 ,p_business_group_id => l_business_group_id
2545 ,p_entry_step_id => p_entry_step_id
2546 ,p_entry_grade_rule_id => p_entry_grade_rule_id
2547 ,p_job_id => p_job_id
2548 ,p_location_id => p_location_id
2549 ,p_organization_id => p_organization_id
2550 ,p_pay_freq_payroll_id => p_pay_freq_payroll_id
2551 ,p_position_definition_id => l_position_definition_id
2552 ,p_position_transaction_id => p_position_transaction_id
2553 ,p_prior_position_id => p_prior_position_id
2554 ,p_relief_position_id => p_relief_position_id
2555 ,p_entry_grade_id => p_entry_grade_id
2556 ,p_successor_position_id => p_successor_position_id
2557 ,p_supervisor_position_id => p_supervisor_position_id
2558 ,p_amendment_date => p_amendment_date
2559 ,p_amendment_recommendation => p_amendment_recommendation
2560 ,p_amendment_ref_number => p_amendment_ref_number
2561 ,p_bargaining_unit_cd => p_bargaining_unit_cd
2562 ,p_comments => p_comments
2563 ,p_current_job_prop_end_date => p_current_job_prop_end_date
2564 ,p_current_org_prop_end_date => p_current_org_prop_end_date
2565 ,p_avail_status_prop_end_date => p_avail_status_prop_end_date
2566 ,p_date_effective => l_date_effective
2567 ,p_date_end => l_date_end
2568 ,p_earliest_hire_date => p_earliest_hire_date
2569 ,p_fill_by_date => p_fill_by_date
2570 ,p_frequency => p_frequency
2571 ,p_fte => p_fte
2572 ,p_max_persons => p_max_persons
2573 ,p_name => l_name
2574 ,p_overlap_period => p_overlap_period
2575 ,p_overlap_unit_cd => p_overlap_unit_cd
2576 ,p_pay_term_end_day_cd => p_pay_term_end_day_cd
2577 ,p_pay_term_end_month_cd => p_pay_term_end_month_cd
2578 ,p_permanent_temporary_flag => p_permanent_temporary_flag
2579 ,p_permit_recruitment_flag => p_permit_recruitment_flag
2580 ,p_position_type => p_position_type
2581 ,p_posting_description => p_posting_description
2582 ,p_probation_period => p_probation_period
2583 ,p_probation_period_unit_cd => p_probation_period_unit_cd
2584 ,p_replacement_required_flag => p_replacement_required_flag
2585 ,p_review_flag => p_review_flag
2586 ,p_seasonal_flag => p_seasonal_flag
2587 ,p_security_requirements => p_security_requirements
2588 ,p_status => p_status
2589 ,p_term_start_day_cd => p_term_start_day_cd
2590 ,p_term_start_month_cd => p_term_start_month_cd
2591 ,p_time_normal_finish => p_time_normal_finish
2592 ,p_time_normal_start => p_time_normal_start
2593 ,p_update_source_cd => p_update_source_cd
2594 ,p_working_hours => p_working_hours
2595 ,p_works_council_approval_flag => p_works_council_approval_flag
2596 ,p_work_period_type_cd => p_work_period_type_cd
2597 ,p_work_term_end_day_cd => p_work_term_end_day_cd
2598 ,p_work_term_end_month_cd => p_work_term_end_month_cd
2599 ,p_proposed_fte_for_layoff => p_proposed_fte_for_layoff
2600 ,p_proposed_date_for_layoff => p_proposed_date_for_layoff
2601 ,p_pay_basis_id => p_pay_basis_id
2602 ,p_supervisor_id => p_supervisor_id
2603 ,p_copied_to_old_table_flag => 'N'
2604 ,p_information1 => p_information1
2605 ,p_information2 => p_information2
2606 ,p_information3 => p_information3
2607 ,p_information4 => p_information4
2608 ,p_information5 => p_information5
2609 ,p_information6 => p_information6
2610 ,p_information7 => p_information7
2611 ,p_information8 => p_information8
2612 ,p_information9 => p_information9
2613 ,p_information10 => p_information10
2614 ,p_information11 => p_information11
2615 ,p_information12 => p_information12
2616 ,p_information13 => p_information13
2617 ,p_information14 => p_information14
2618 ,p_information15 => p_information15
2619 ,p_information16 => p_information16
2620 ,p_information17 => p_information17
2621 ,p_information18 => p_information18
2622 ,p_information19 => p_information19
2623 ,p_information20 => p_information20
2624 ,p_information21 => p_information21
2625 ,p_information22 => p_information22
2626 ,p_information23 => p_information23
2627 ,p_information24 => p_information24
2628 ,p_information25 => p_information25
2629 ,p_information26 => p_information26
2630 ,p_information27 => p_information27
2631 ,p_information28 => p_information28
2632 ,p_information29 => p_information29
2633 ,p_information30 => p_information30
2634 ,p_information_category => p_information_category
2635 ,p_attribute1 => p_attribute1
2636 ,p_attribute2 => p_attribute2
2637 ,p_attribute3 => p_attribute3
2638 ,p_attribute4 => p_attribute4
2639 ,p_attribute5 => p_attribute5
2640 ,p_attribute6 => p_attribute6
2641 ,p_attribute7 => p_attribute7
2642 ,p_attribute8 => p_attribute8
2643 ,p_attribute9 => p_attribute9
2644 ,p_attribute10 => p_attribute10
2645 ,p_attribute11 => p_attribute11
2646 ,p_attribute12 => p_attribute12
2647 ,p_attribute13 => p_attribute13
2648 ,p_attribute14 => p_attribute14
2649 ,p_attribute15 => p_attribute15
2650 ,p_attribute16 => p_attribute16
2651 ,p_attribute17 => p_attribute17
2652 ,p_attribute18 => p_attribute18
2653 ,p_attribute19 => p_attribute19
2654 ,p_attribute20 => p_attribute20
2655 ,p_attribute21 => p_attribute21
2656 ,p_attribute22 => p_attribute22
2657 ,p_attribute23 => p_attribute23
2658 ,p_attribute24 => p_attribute24
2659 ,p_attribute25 => p_attribute25
2660 ,p_attribute26 => p_attribute26
2661 ,p_attribute27 => p_attribute27
2662 ,p_attribute28 => p_attribute28
2663 ,p_attribute29 => p_attribute29
2664 ,p_attribute30 => p_attribute30
2665 ,p_attribute_category => p_attribute_category
2666 ,p_request_id => p_request_id
2667 ,p_program_application_id => p_program_application_id
2668 ,p_program_id => p_program_id
2669 ,p_program_update_date => p_program_update_date
2670 ,p_object_version_number => l_object_version_number
2671 ,p_effective_date => trunc(p_effective_date)
2672 ,p_validate => p_validate
2673 ,p_security_profile_id => p_security_profile_id
2674 );
2675 --
2676 -- PMFLETCH Insert into translation table
2677 --
2678 hr_pft_ins.ins_tl
2679 ( p_language_code => l_language_code
2680 , p_position_id => l_position_id
2681 , p_position_definition_id => l_position_definition_id
2682 );
2683 --
2684 -- Insert in per_all_positions table
2685 --
2686 begin
2687 --
2688 -- call refresh_position procedure to replicate changes in per_all_positions
2689 --
2690 if g_debug then
2691 hr_utility.set_location ( l_proc, 1000);
2692 hr_utility.set_location ( 'POSITION ID : ' || l_position_id || l_proc, 1000);
2693 end if;
2694 --
2695 synchronize_per_all_positions
2696 (p_position_id => l_position_id
2697 ,p_effective_date => trunc(p_effective_date)
2698 ,p_datetrack_mode => null
2699 ,p_object_version_number => l_object_version_number
2700 );
2701 --
2702 if g_debug then
2703 hr_utility.set_location ( l_proc, 1001);
2704 hr_utility.set_location ( 'POSITION ID : ' || l_position_id || l_proc, 1000);
2705 end if;
2706 --
2707 --
2708 end;
2709 --
2710 -- Insert in position security list table
2711 --
2712 begin
2713 open c_view_all_pos;
2714 fetch c_view_all_pos into l_view_all_positions_flag;
2715 close c_view_all_pos;
2716
2717 if l_view_all_positions_flag <> 'Y' then
2718 /*
2719 open csr_get_nondt_pos(l_position_id );
2720 fetch csr_get_nondt_pos into l_dummy;
2721 if csr_get_nondt_pos%FOUND then
2722 --
2723 close csr_get_nondt_pos;
2724 --
2725 */
2726 hr_security.add_position(l_position_id,
2727 p_security_profile_id);
2728 --
2729 end if;
2730 end;
2731 --
2732 --
2733 begin
2734 --
2735 -- Start of API User Hook for the after hook of create_position
2736 --
2737 hr_position_bk1.create_position_a
2738 (
2739 p_position_id => l_position_id
2740 ,p_effective_start_date => l_effective_start_date
2741 ,p_effective_end_date => l_effective_end_date
2742 ,p_availability_status_id => l_availability_status_id
2743 ,p_business_group_id => l_business_group_id
2744 ,p_entry_step_id => p_entry_step_id
2745 ,p_entry_grade_rule_id => p_entry_grade_rule_id
2746 ,p_job_id => p_job_id
2747 ,p_location_id => p_location_id
2748 ,p_organization_id => p_organization_id
2749 ,p_pay_freq_payroll_id => p_pay_freq_payroll_id
2750 ,p_position_definition_id => l_position_definition_id
2751 ,p_position_transaction_id => p_position_transaction_id
2752 ,p_prior_position_id => p_prior_position_id
2753 ,p_relief_position_id => p_relief_position_id
2754 ,p_entry_grade_id => p_entry_grade_id
2755 ,p_successor_position_id => p_successor_position_id
2756 ,p_supervisor_position_id => p_supervisor_position_id
2757 ,p_amendment_date => p_amendment_date
2758 ,p_amendment_recommendation => p_amendment_recommendation
2759 ,p_amendment_ref_number => p_amendment_ref_number
2760 ,p_bargaining_unit_cd => p_bargaining_unit_cd
2761 ,p_comments => p_comments
2762 ,p_current_job_prop_end_date => p_current_job_prop_end_date
2763 ,p_current_org_prop_end_date => p_current_org_prop_end_date
2764 ,p_avail_status_prop_end_date => p_avail_status_prop_end_date
2765 ,p_date_effective => l_date_effective
2766 ,p_date_end => l_date_end
2767 ,p_earliest_hire_date => p_earliest_hire_date
2768 ,p_fill_by_date => p_fill_by_date
2769 ,p_frequency => p_frequency
2770 ,p_fte => p_fte
2771 ,p_max_persons => p_max_persons
2772 ,p_name => l_name
2773 ,p_overlap_period => p_overlap_period
2774 ,p_overlap_unit_cd => p_overlap_unit_cd
2775 ,p_pay_term_end_day_cd => p_pay_term_end_day_cd
2776 ,p_pay_term_end_month_cd => p_pay_term_end_month_cd
2777 ,p_permanent_temporary_flag => p_permanent_temporary_flag
2778 ,p_permit_recruitment_flag => p_permit_recruitment_flag
2779 ,p_position_type => p_position_type
2780 ,p_posting_description => p_posting_description
2781 ,p_probation_period => p_probation_period
2782 ,p_probation_period_unit_cd => p_probation_period_unit_cd
2783 ,p_replacement_required_flag => p_replacement_required_flag
2784 ,p_review_flag => p_review_flag
2785 ,p_seasonal_flag => p_seasonal_flag
2786 ,p_security_requirements => p_security_requirements
2787 ,p_status => p_status
2788 ,p_term_start_day_cd => p_term_start_day_cd
2789 ,p_term_start_month_cd => p_term_start_month_cd
2790 ,p_time_normal_finish => p_time_normal_finish
2791 ,p_time_normal_start => p_time_normal_start
2792 ,p_update_source_cd => p_update_source_cd
2793 ,p_working_hours => p_working_hours
2794 ,p_works_council_approval_flag => p_works_council_approval_flag
2795 ,p_work_period_type_cd => p_work_period_type_cd
2796 ,p_work_term_end_day_cd => p_work_term_end_day_cd
2797 ,p_work_term_end_month_cd => p_work_term_end_month_cd
2798 ,p_proposed_fte_for_layoff => p_proposed_fte_for_layoff
2799 ,p_proposed_date_for_layoff => p_proposed_date_for_layoff
2800 ,p_pay_basis_id => p_pay_basis_id
2801 ,p_supervisor_id => p_supervisor_id
2802 --,p_copied_to_old_table_flag => p_copied_to_old_table_flag
2803 ,p_information1 => p_information1
2804 ,p_information2 => p_information2
2805 ,p_information3 => p_information3
2806 ,p_information4 => p_information4
2807 ,p_information5 => p_information5
2808 ,p_information6 => p_information6
2809 ,p_information7 => p_information7
2810 ,p_information8 => p_information8
2811 ,p_information9 => p_information9
2812 ,p_information10 => p_information10
2813 ,p_information11 => p_information11
2814 ,p_information12 => p_information12
2815 ,p_information13 => p_information13
2816 ,p_information14 => p_information14
2817 ,p_information15 => p_information15
2818 ,p_information16 => p_information16
2819 ,p_information17 => p_information17
2820 ,p_information18 => p_information18
2821 ,p_information19 => p_information19
2822 ,p_information20 => p_information20
2823 ,p_information21 => p_information21
2824 ,p_information22 => p_information22
2825 ,p_information23 => p_information23
2826 ,p_information24 => p_information24
2827 ,p_information25 => p_information25
2828 ,p_information26 => p_information26
2829 ,p_information27 => p_information27
2830 ,p_information28 => p_information28
2831 ,p_information29 => p_information29
2832 ,p_information30 => p_information30
2833 ,p_information_category => p_information_category
2834 ,p_attribute1 => p_attribute1
2835 ,p_attribute2 => p_attribute2
2836 ,p_attribute3 => p_attribute3
2837 ,p_attribute4 => p_attribute4
2838 ,p_attribute5 => p_attribute5
2839 ,p_attribute6 => p_attribute6
2840 ,p_attribute7 => p_attribute7
2841 ,p_attribute8 => p_attribute8
2842 ,p_attribute9 => p_attribute9
2843 ,p_attribute10 => p_attribute10
2844 ,p_attribute11 => p_attribute11
2845 ,p_attribute12 => p_attribute12
2846 ,p_attribute13 => p_attribute13
2847 ,p_attribute14 => p_attribute14
2848 ,p_attribute15 => p_attribute15
2849 ,p_attribute16 => p_attribute16
2850 ,p_attribute17 => p_attribute17
2851 ,p_attribute18 => p_attribute18
2852 ,p_attribute19 => p_attribute19
2853 ,p_attribute20 => p_attribute20
2854 ,p_attribute21 => p_attribute21
2855 ,p_attribute22 => p_attribute22
2856 ,p_attribute23 => p_attribute23
2857 ,p_attribute24 => p_attribute24
2858 ,p_attribute25 => p_attribute25
2859 ,p_attribute26 => p_attribute26
2860 ,p_attribute27 => p_attribute27
2861 ,p_attribute28 => p_attribute28
2862 ,p_attribute29 => p_attribute29
2863 ,p_attribute30 => p_attribute30
2864 ,p_attribute_category => p_attribute_category
2865 ,p_segment1 => l_segment1
2866 ,p_segment2 => l_segment2
2867 ,p_segment3 => l_segment3
2868 ,p_segment4 => l_segment4
2869 ,p_segment5 => l_segment5
2870 ,p_segment6 => l_segment6
2871 ,p_segment7 => l_segment7
2872 ,p_segment8 => l_segment8
2873 ,p_segment9 => l_segment9
2874 ,p_segment10 => l_segment10
2875 ,p_segment11 => l_segment11
2876 ,p_segment12 => l_segment12
2877 ,p_segment13 => l_segment13
2878 ,p_segment14 => l_segment14
2879 ,p_segment15 => l_segment15
2880 ,p_segment16 => l_segment16
2881 ,p_segment17 => l_segment17
2882 ,p_segment18 => l_segment18
2883 ,p_segment19 => l_segment19
2884 ,p_segment20 => l_segment20
2885 ,p_segment21 => l_segment21
2886 ,p_segment22 => l_segment22
2887 ,p_segment23 => l_segment23
2888 ,p_segment24 => l_segment24
2889 ,p_segment25 => l_segment25
2890 ,p_segment26 => l_segment26
2891 ,p_segment27 => l_segment27
2892 ,p_segment28 => l_segment28
2893 ,p_segment29 => l_segment29
2894 ,p_segment30 => l_segment30
2895 ,p_concat_segments => p_concat_segments
2896 ,p_request_id => p_request_id
2897 ,p_program_application_id => p_program_application_id
2898 ,p_program_id => p_program_id
2899 ,p_program_update_date => p_program_update_date
2900 ,p_object_version_number => l_object_version_number
2901 ,p_effective_date => trunc(p_effective_date)
2902 ,p_language_code => l_language_code
2903 );
2904 exception
2905 when hr_api.cannot_find_prog_unit then
2906 hr_api.cannot_find_prog_unit_error
2907 (p_module_name => 'CREATE_POSITION'
2908 ,p_hook_type => 'AP'
2909 );
2910 --
2911 -- End of API User Hook for the after hook of create_position
2912 --
2913 end;
2914 --
2915 if g_debug then
2916 hr_utility.set_location(l_proc, 60);
2917 end if;
2918 --
2919 -- When in validation only mode raise the Validate_Enabled exception
2920 --
2921 if p_validate then
2922 raise hr_api.validate_enabled;
2923 end if;
2924 --
2925 -- Set all output arguments
2926 --
2927 p_position_id := l_position_id;
2928 p_effective_start_date := l_effective_start_date;
2929 p_effective_end_date := l_effective_end_date;
2930 p_object_version_number := l_object_version_number;
2931 p_position_definition_id := l_position_definition_id;
2932 p_name := l_name;
2933 --
2934 if g_debug then
2935 hr_utility.set_location(' Leaving:'||l_proc, 70);
2936 end if;
2937 --
2938 exception
2939 --
2940 when hr_api.validate_enabled then
2941 --
2942 -- As the Validate_Enabled exception has been raised
2943 -- we must rollback to the savepoint
2944 --
2945 ROLLBACK TO create_position;
2946 --
2947 -- Only set output warning arguments
2948 -- (Any key or derived arguments must be set to null
2949 -- when validation only mode is being used.)
2950 --
2951 p_position_id := null;
2952 p_effective_start_date := null;
2953 p_effective_end_date := null;
2954 p_object_version_number := null;
2955 if l_null_ind = 0
2956 then
2957 p_position_definition_id := null;
2958 else
2959 p_position_definition_id := l_position_definition_id;
2960 end if;
2961 p_name := l_name;
2962 if g_debug then
2963 hr_utility.set_location(' Leaving:'||l_proc, 80);
2964 end if;
2965 --
2966 when others then
2967 --
2968 -- A validation or unexpected error has occured
2969 --
2970 p_position_id := null;
2971 p_effective_start_date := null;
2972 p_effective_end_date := null;
2973 p_object_version_number := null;
2974 p_position_definition_id := l_position_definition_id;
2975 p_name := l_name;
2976
2977 ROLLBACK TO create_position;
2978 raise;
2979 --
2980 end create_position;
2981 -- ----------------------------------------------------------------------------
2982 -- |------------------------< update_position >--- ------------------|
2983 -- ----------------------------------------------------------------------------
2984 --
2985 procedure update_position
2986 (p_validate in boolean default false
2987 ,p_position_id in number
2988 ,p_effective_start_date out nocopy date
2989 ,p_effective_end_date out nocopy date
2990 ,p_position_definition_id in out nocopy number
2991 ,p_valid_grades_changed_warning out nocopy boolean
2992 ,p_name in out nocopy varchar2
2993 ,p_language_code in varchar2 default hr_api.userenv_lang
2994 ,p_availability_status_id in number default hr_api.g_number
2995 -- ,p_business_group_id in number default hr_api.g_number
2996 ,p_entry_step_id in number default hr_api.g_number
2997 ,p_entry_grade_rule_id in number default hr_api.g_number
2998 -- ,p_job_id in number default hr_api.g_number
2999 ,p_location_id in number default hr_api.g_number
3000 -- ,p_organization_id in number default hr_api.g_number
3001 ,p_pay_freq_payroll_id in number default hr_api.g_number
3002 ,p_position_transaction_id in number default hr_api.g_number
3003 ,p_prior_position_id in number default hr_api.g_number
3004 ,p_relief_position_id in number default hr_api.g_number
3005 ,p_entry_grade_id in number default hr_api.g_number
3006 ,p_successor_position_id in number default hr_api.g_number
3007 ,p_supervisor_position_id in number default hr_api.g_number
3008 ,p_amendment_date in date default hr_api.g_date
3009 ,p_amendment_recommendation in varchar2 default hr_api.g_varchar2
3010 ,p_amendment_ref_number in varchar2 default hr_api.g_varchar2
3011 ,p_bargaining_unit_cd in varchar2 default hr_api.g_varchar2
3012 ,p_comments in long default hr_api.g_varchar2
3013 ,p_current_job_prop_end_date in date default hr_api.g_date
3014 ,p_current_org_prop_end_date in date default hr_api.g_date
3015 ,p_avail_status_prop_end_date in date default hr_api.g_date
3016 ,p_date_effective in date default hr_api.g_date
3017 ,p_date_end in date default hr_api.g_date
3018 ,p_earliest_hire_date in date default hr_api.g_date
3019 ,p_fill_by_date in date default hr_api.g_date
3020 ,p_frequency in varchar2 default hr_api.g_varchar2
3021 ,p_fte in number default hr_api.g_number
3022 ,p_max_persons in number default hr_api.g_number
3023 ,p_overlap_period in number default hr_api.g_number
3024 ,p_overlap_unit_cd in varchar2 default hr_api.g_varchar2
3025 ,p_pay_term_end_day_cd in varchar2 default hr_api.g_varchar2
3026 ,p_pay_term_end_month_cd in varchar2 default hr_api.g_varchar2
3027 ,p_permanent_temporary_flag in varchar2 default hr_api.g_varchar2
3028 ,p_permit_recruitment_flag in varchar2 default hr_api.g_varchar2
3029 ,p_position_type in varchar2 default hr_api.g_varchar2
3030 ,p_posting_description in varchar2 default hr_api.g_varchar2
3031 ,p_probation_period in number default hr_api.g_number
3032 ,p_probation_period_unit_cd in varchar2 default hr_api.g_varchar2
3033 ,p_replacement_required_flag in varchar2 default hr_api.g_varchar2
3034 ,p_review_flag in varchar2 default hr_api.g_varchar2
3035 ,p_seasonal_flag in varchar2 default hr_api.g_varchar2
3036 ,p_security_requirements in varchar2 default hr_api.g_varchar2
3037 ,p_status in varchar2 default hr_api.g_varchar2
3038 ,p_term_start_day_cd in varchar2 default hr_api.g_varchar2
3039 ,p_term_start_month_cd in varchar2 default hr_api.g_varchar2
3040 ,p_time_normal_finish in varchar2 default hr_api.g_varchar2
3041 ,p_time_normal_start in varchar2 default hr_api.g_varchar2
3042 ,p_update_source_cd in varchar2 default hr_api.g_varchar2
3043 ,p_working_hours in number default hr_api.g_number
3044 ,p_works_council_approval_flag in varchar2 default hr_api.g_varchar2
3045 ,p_work_period_type_cd in varchar2 default hr_api.g_varchar2
3046 ,p_work_term_end_day_cd in varchar2 default hr_api.g_varchar2
3047 ,p_work_term_end_month_cd in varchar2 default hr_api.g_varchar2
3048 ,p_proposed_fte_for_layoff in number default hr_api.g_number
3049 ,p_proposed_date_for_layoff in date default hr_api.g_date
3050 ,p_pay_basis_id in number default hr_api.g_number
3051 ,p_supervisor_id in number default hr_api.g_number
3052 --,p_copied_to_old_table_flag in varchar2 default hr_api.g_varchar2
3053 ,p_information1 in varchar2 default hr_api.g_varchar2
3054 ,p_information2 in varchar2 default hr_api.g_varchar2
3055 ,p_information3 in varchar2 default hr_api.g_varchar2
3056 ,p_information4 in varchar2 default hr_api.g_varchar2
3057 ,p_information5 in varchar2 default hr_api.g_varchar2
3058 ,p_information6 in varchar2 default hr_api.g_varchar2
3059 ,p_information7 in varchar2 default hr_api.g_varchar2
3060 ,p_information8 in varchar2 default hr_api.g_varchar2
3061 ,p_information9 in varchar2 default hr_api.g_varchar2
3062 ,p_information10 in varchar2 default hr_api.g_varchar2
3063 ,p_information11 in varchar2 default hr_api.g_varchar2
3064 ,p_information12 in varchar2 default hr_api.g_varchar2
3065 ,p_information13 in varchar2 default hr_api.g_varchar2
3066 ,p_information14 in varchar2 default hr_api.g_varchar2
3067 ,p_information15 in varchar2 default hr_api.g_varchar2
3068 ,p_information16 in varchar2 default hr_api.g_varchar2
3069 ,p_information17 in varchar2 default hr_api.g_varchar2
3070 ,p_information18 in varchar2 default hr_api.g_varchar2
3071 ,p_information19 in varchar2 default hr_api.g_varchar2
3072 ,p_information20 in varchar2 default hr_api.g_varchar2
3073 ,p_information21 in varchar2 default hr_api.g_varchar2
3074 ,p_information22 in varchar2 default hr_api.g_varchar2
3075 ,p_information23 in varchar2 default hr_api.g_varchar2
3076 ,p_information24 in varchar2 default hr_api.g_varchar2
3077 ,p_information25 in varchar2 default hr_api.g_varchar2
3078 ,p_information26 in varchar2 default hr_api.g_varchar2
3079 ,p_information27 in varchar2 default hr_api.g_varchar2
3080 ,p_information28 in varchar2 default hr_api.g_varchar2
3081 ,p_information29 in varchar2 default hr_api.g_varchar2
3082 ,p_information30 in varchar2 default hr_api.g_varchar2
3083 ,p_information_category in varchar2 default hr_api.g_varchar2
3084 ,p_attribute1 in varchar2 default hr_api.g_varchar2
3085 ,p_attribute2 in varchar2 default hr_api.g_varchar2
3086 ,p_attribute3 in varchar2 default hr_api.g_varchar2
3087 ,p_attribute4 in varchar2 default hr_api.g_varchar2
3088 ,p_attribute5 in varchar2 default hr_api.g_varchar2
3089 ,p_attribute6 in varchar2 default hr_api.g_varchar2
3090 ,p_attribute7 in varchar2 default hr_api.g_varchar2
3091 ,p_attribute8 in varchar2 default hr_api.g_varchar2
3092 ,p_attribute9 in varchar2 default hr_api.g_varchar2
3093 ,p_attribute10 in varchar2 default hr_api.g_varchar2
3094 ,p_attribute11 in varchar2 default hr_api.g_varchar2
3095 ,p_attribute12 in varchar2 default hr_api.g_varchar2
3096 ,p_attribute13 in varchar2 default hr_api.g_varchar2
3097 ,p_attribute14 in varchar2 default hr_api.g_varchar2
3098 ,p_attribute15 in varchar2 default hr_api.g_varchar2
3099 ,p_attribute16 in varchar2 default hr_api.g_varchar2
3100 ,p_attribute17 in varchar2 default hr_api.g_varchar2
3101 ,p_attribute18 in varchar2 default hr_api.g_varchar2
3102 ,p_attribute19 in varchar2 default hr_api.g_varchar2
3103 ,p_attribute20 in varchar2 default hr_api.g_varchar2
3104 ,p_attribute21 in varchar2 default hr_api.g_varchar2
3105 ,p_attribute22 in varchar2 default hr_api.g_varchar2
3106 ,p_attribute23 in varchar2 default hr_api.g_varchar2
3107 ,p_attribute24 in varchar2 default hr_api.g_varchar2
3108 ,p_attribute25 in varchar2 default hr_api.g_varchar2
3109 ,p_attribute26 in varchar2 default hr_api.g_varchar2
3110 ,p_attribute27 in varchar2 default hr_api.g_varchar2
3111 ,p_attribute28 in varchar2 default hr_api.g_varchar2
3112 ,p_attribute29 in varchar2 default hr_api.g_varchar2
3113 ,p_attribute30 in varchar2 default hr_api.g_varchar2
3114 ,p_attribute_category in varchar2 default hr_api.g_varchar2
3115 ,p_segment1 in varchar2 default hr_api.g_varchar2
3116 ,p_segment2 in varchar2 default hr_api.g_varchar2
3117 ,p_segment3 in varchar2 default hr_api.g_varchar2
3118 ,p_segment4 in varchar2 default hr_api.g_varchar2
3119 ,p_segment5 in varchar2 default hr_api.g_varchar2
3120 ,p_segment6 in varchar2 default hr_api.g_varchar2
3121 ,p_segment7 in varchar2 default hr_api.g_varchar2
3122 ,p_segment8 in varchar2 default hr_api.g_varchar2
3123 ,p_segment9 in varchar2 default hr_api.g_varchar2
3124 ,p_segment10 in varchar2 default hr_api.g_varchar2
3125 ,p_segment11 in varchar2 default hr_api.g_varchar2
3126 ,p_segment12 in varchar2 default hr_api.g_varchar2
3127 ,p_segment13 in varchar2 default hr_api.g_varchar2
3128 ,p_segment14 in varchar2 default hr_api.g_varchar2
3129 ,p_segment15 in varchar2 default hr_api.g_varchar2
3130 ,p_segment16 in varchar2 default hr_api.g_varchar2
3131 ,p_segment17 in varchar2 default hr_api.g_varchar2
3132 ,p_segment18 in varchar2 default hr_api.g_varchar2
3133 ,p_segment19 in varchar2 default hr_api.g_varchar2
3134 ,p_segment20 in varchar2 default hr_api.g_varchar2
3135 ,p_segment21 in varchar2 default hr_api.g_varchar2
3136 ,p_segment22 in varchar2 default hr_api.g_varchar2
3137 ,p_segment23 in varchar2 default hr_api.g_varchar2
3138 ,p_segment24 in varchar2 default hr_api.g_varchar2
3139 ,p_segment25 in varchar2 default hr_api.g_varchar2
3140 ,p_segment26 in varchar2 default hr_api.g_varchar2
3141 ,p_segment27 in varchar2 default hr_api.g_varchar2
3142 ,p_segment28 in varchar2 default hr_api.g_varchar2
3143 ,p_segment29 in varchar2 default hr_api.g_varchar2
3144 ,p_segment30 in varchar2 default hr_api.g_varchar2
3145 ,p_concat_segments in varchar2 default hr_api.g_varchar2
3146 ,p_request_id in number default hr_api.g_number
3147 ,p_program_application_id in number default hr_api.g_number
3148 ,p_program_id in number default hr_api.g_number
3149 ,p_program_update_date in date default hr_api.g_date
3150 ,p_object_version_number in out nocopy number
3151 ,p_effective_date in date
3152 ,p_datetrack_mode in varchar2
3153 ) is
3154 --
3155 -- Declare cursors and local variables
3156 --
3157 l_proc varchar2(72) ;
3158 l_object_version_number hr_all_positions_f.object_version_number%TYPE;
3159 l_effective_start_date hr_all_positions_f.effective_start_date%TYPE;
3160 l_effective_end_date hr_all_positions_f.effective_end_date%TYPE;
3161 --
3162 l_language_code fnd_languages.language_code%TYPE;
3163 --
3164 l_valid_grades_changed1 boolean default FALSE;
3165 l_valid_grades_changed2 boolean default FALSE;
3166 l_flex_num fnd_id_flex_segments.id_flex_num%TYPE;
3167 l_api_updating boolean;
3168 l_position_definition_id hr_all_positions_f.position_definition_id%TYPE := p_position_definition_id;
3169 l_name hr_all_positions_f.name%TYPE
3170 := p_name;
3171 l_date_effective hr_all_positions_f.date_effective%TYPE;
3172 l_date_end hr_all_positions_f.date_end%TYPE;
3173 l_business_group_id hr_all_positions_f.business_group_id%TYPE;
3174 l_minesd date;
3175 l_ovn number;
3176 l_esd date;
3177 l_eed date;
3178 l_retcode varchar2(2000);
3179 l_errbuf varchar2(2000);
3180 l_segment1 varchar2(60) := p_segment1;
3181 l_segment2 varchar2(60) := p_segment2;
3182 l_segment3 varchar2(60) := p_segment3;
3183 l_segment4 varchar2(60) := p_segment4;
3184 l_segment5 varchar2(60) := p_segment5;
3185 l_segment6 varchar2(60) := p_segment6;
3186 l_segment7 varchar2(60) := p_segment7;
3187 l_segment8 varchar2(60) := p_segment8;
3188 l_segment9 varchar2(60) := p_segment9;
3189 l_segment10 varchar2(60) := p_segment10;
3190 l_segment11 varchar2(60) := p_segment11;
3191 l_segment12 varchar2(60) := p_segment12;
3192 l_segment13 varchar2(60) := p_segment13;
3193 l_segment14 varchar2(60) := p_segment14;
3194 l_segment15 varchar2(60) := p_segment15;
3195 l_segment16 varchar2(60) := p_segment16;
3196 l_segment17 varchar2(60) := p_segment17;
3197 l_segment18 varchar2(60) := p_segment18;
3198 l_segment19 varchar2(60) := p_segment19;
3199 l_segment20 varchar2(60) := p_segment20;
3200 l_segment21 varchar2(60) := p_segment21;
3201 l_segment22 varchar2(60) := p_segment22;
3202 l_segment23 varchar2(60) := p_segment23;
3203 l_segment24 varchar2(60) := p_segment24;
3204 l_segment25 varchar2(60) := p_segment25;
3205 l_segment26 varchar2(60) := p_segment26;
3206 l_segment27 varchar2(60) := p_segment27;
3207 l_segment28 varchar2(60) := p_segment28;
3208 l_segment29 varchar2(60) := p_segment29;
3209 l_segment30 varchar2(60) := p_segment30;
3210 l_null_ind number(1) := 0;
3211 --
3212 -- Declare cursors
3213 --
3214 cursor csr_idsel is
3215 select pd.id_flex_num
3216 from per_position_definitions pd
3217 where pd.position_definition_id = l_position_definition_id;
3218 --
3219 cursor csr_isfirstrow is
3220 select min(effective_start_date)
3221 from hr_all_positions_f
3222 where position_id = p_position_id;
3223 --
3224 -- bug 2271064 get per_position_definitions segment values where
3225 -- position_definition_id is known
3226 --
3227 cursor c_segments is
3228 select segment1,
3229 segment2,
3230 segment3,
3231 segment4,
3232 segment5,
3233 segment6,
3234 segment7,
3235 segment8,
3236 segment9,
3237 segment10,
3238 segment11,
3239 segment12,
3240 segment13,
3241 segment14,
3242 segment15,
3243 segment16,
3244 segment17,
3245 segment18,
3246 segment19,
3247 segment20,
3248 segment21,
3249 segment22,
3250 segment23,
3251 segment24,
3252 segment25,
3253 segment26,
3254 segment27,
3255 segment28,
3256 segment29,
3257 segment30
3258 from per_position_definitions
3259 where position_definition_id = l_position_definition_id;
3260 --
3261 begin
3262 --
3263 g_debug := hr_utility.debug_enabled;
3264 if g_debug then
3265 l_proc := g_package||'update_position';
3266 hr_utility.set_location('Entering:'|| l_proc, 10);
3267 end if;
3268 --
3269 -- Issue a savepoint if operating in validation only mode
3270 --
3271 savepoint update_position;
3272 --
3273 -- Validate the language parameter. l_language_code should be passed
3274 -- instead of p_language_code from now on, to allow an IN OUT parameter to
3275 -- be passed through.
3276 --
3277 l_language_code := p_language_code;
3278 hr_api.validate_language_code(p_language_code => l_language_code);
3279 --
3280 if g_debug then
3281 hr_utility.set_location(l_proc, 20);
3282 end if;
3283 --
3284 -- Process Logic
3285 --
3286 l_object_version_number := p_object_version_number;
3287 l_date_effective := trunc(p_date_effective);
3288 l_date_end := trunc(p_date_end);
3289 --
3290 -- 2271064 get segment values if p_job_definition_id entered with a value
3291 --
3292 if l_position_definition_id is not null
3293 --
3294 then
3295 --
3296 if g_debug then
3297 hr_utility.set_location(l_proc, 15);
3298 end if;
3299 --
3300 -- set indicator to show p_position_definition_id did not enter pgm null
3301 --
3302 l_null_ind := 1;
3303 --
3304 open c_segments;
3305 fetch c_segments into
3306 l_segment1,
3307 l_segment2,
3308 l_segment3,
3309 l_segment4,
3310 l_segment5,
3311 l_segment6,
3312 l_segment7,
3313 l_segment8,
3314 l_segment9,
3315 l_segment10,
3316 l_segment11,
3317 l_segment12,
3318 l_segment13,
3319 l_segment14,
3320 l_segment15,
3321 l_segment16,
3322 l_segment17,
3323 l_segment18,
3324 l_segment19,
3325 l_segment20,
3326 l_segment21,
3327 l_segment22,
3328 l_segment23,
3329 l_segment24,
3330 l_segment25,
3331 l_segment26,
3332 l_segment27,
3333 l_segment28,
3334 l_segment29,
3335 l_segment30;
3336 close c_segments;
3337 --
3338 if g_debug then
3339 hr_utility.set_location(l_proc, 27);
3340 end if;
3341 --
3342 else
3343 l_null_ind := 0;
3344 -- l_name := null;
3345 end if;
3346 --
3347 begin
3348 --
3349 -- Start of API User Hook for the before hook of update_position
3350 --
3351 hr_position_bk2.update_position_b
3352 (
3353 p_position_id => p_position_id
3354 ,p_availability_status_id => p_availability_status_id
3355 -- ,p_business_group_id => p_business_group_id
3356 ,p_entry_step_id => p_entry_step_id
3357 ,p_entry_grade_rule_id => p_entry_grade_rule_id
3358 -- ,p_job_id => p_job_id
3359 ,p_location_id => p_location_id
3360 -- ,p_organization_id => p_organization_id
3361 ,p_pay_freq_payroll_id => p_pay_freq_payroll_id
3362 ,p_position_definition_id => p_position_definition_id
3363 ,p_position_transaction_id => p_position_transaction_id
3364 ,p_prior_position_id => p_prior_position_id
3365 ,p_relief_position_id => p_relief_position_id
3366 ,p_entry_grade_id => p_entry_grade_id
3367 ,p_successor_position_id => p_successor_position_id
3368 ,p_supervisor_position_id => p_supervisor_position_id
3369 ,p_amendment_date => p_amendment_date
3370 ,p_amendment_recommendation => p_amendment_recommendation
3371 ,p_amendment_ref_number => p_amendment_ref_number
3372 ,p_bargaining_unit_cd => p_bargaining_unit_cd
3373 ,p_comments => p_comments
3374 ,p_current_job_prop_end_date => p_current_job_prop_end_date
3375 ,p_current_org_prop_end_date => p_current_org_prop_end_date
3376 ,p_avail_status_prop_end_date => p_avail_status_prop_end_date
3377 ,p_date_effective => l_date_effective
3378 ,p_date_end => l_date_end
3379 ,p_earliest_hire_date => p_earliest_hire_date
3380 ,p_fill_by_date => p_fill_by_date
3381 ,p_frequency => p_frequency
3382 ,p_fte => p_fte
3383 ,p_max_persons => p_max_persons
3384 ,p_name => p_name
3385 ,p_overlap_period => p_overlap_period
3386 ,p_overlap_unit_cd => p_overlap_unit_cd
3387 ,p_pay_term_end_day_cd => p_pay_term_end_day_cd
3388 ,p_pay_term_end_month_cd => p_pay_term_end_month_cd
3389 ,p_permanent_temporary_flag => p_permanent_temporary_flag
3390 ,p_permit_recruitment_flag => p_permit_recruitment_flag
3391 ,p_position_type => p_position_type
3392 ,p_posting_description => p_posting_description
3393 ,p_probation_period => p_probation_period
3394 ,p_probation_period_unit_cd => p_probation_period_unit_cd
3395 ,p_replacement_required_flag => p_replacement_required_flag
3396 ,p_review_flag => p_review_flag
3397 ,p_seasonal_flag => p_seasonal_flag
3398 ,p_security_requirements => p_security_requirements
3399 ,p_status => p_status
3400 ,p_term_start_day_cd => p_term_start_day_cd
3401 ,p_term_start_month_cd => p_term_start_month_cd
3402 ,p_time_normal_finish => p_time_normal_finish
3403 ,p_time_normal_start => p_time_normal_start
3404 ,p_update_source_cd => p_update_source_cd
3405 ,p_working_hours => p_working_hours
3406 ,p_works_council_approval_flag => p_works_council_approval_flag
3407 ,p_work_period_type_cd => p_work_period_type_cd
3408 ,p_work_term_end_day_cd => p_work_term_end_day_cd
3409 ,p_work_term_end_month_cd => p_work_term_end_month_cd
3410 ,p_proposed_fte_for_layoff => p_proposed_fte_for_layoff
3411 ,p_proposed_date_for_layoff => p_proposed_date_for_layoff
3412 ,p_pay_basis_id => p_pay_basis_id
3413 ,p_supervisor_id => p_supervisor_id
3414 -- ,p_copied_to_old_table_flag => p_copied_to_old_table_flag
3415 ,p_information1 => p_information1
3416 ,p_information2 => p_information2
3417 ,p_information3 => p_information3
3418 ,p_information4 => p_information4
3419 ,p_information5 => p_information5
3420 ,p_information6 => p_information6
3421 ,p_information7 => p_information7
3422 ,p_information8 => p_information8
3423 ,p_information9 => p_information9
3424 ,p_information10 => p_information10
3425 ,p_information11 => p_information11
3426 ,p_information12 => p_information12
3427 ,p_information13 => p_information13
3428 ,p_information14 => p_information14
3429 ,p_information15 => p_information15
3430 ,p_information16 => p_information16
3431 ,p_information17 => p_information17
3432 ,p_information18 => p_information18
3433 ,p_information19 => p_information19
3434 ,p_information20 => p_information20
3435 ,p_information21 => p_information21
3436 ,p_information22 => p_information22
3437 ,p_information23 => p_information23
3438 ,p_information24 => p_information24
3439 ,p_information25 => p_information25
3440 ,p_information26 => p_information26
3441 ,p_information27 => p_information27
3442 ,p_information28 => p_information28
3443 ,p_information29 => p_information29
3444 ,p_information30 => p_information30
3445 ,p_information_category => p_information_category
3446 ,p_attribute1 => p_attribute1
3447 ,p_attribute2 => p_attribute2
3448 ,p_attribute3 => p_attribute3
3449 ,p_attribute4 => p_attribute4
3450 ,p_attribute5 => p_attribute5
3451 ,p_attribute6 => p_attribute6
3452 ,p_attribute7 => p_attribute7
3453 ,p_attribute8 => p_attribute8
3454 ,p_attribute9 => p_attribute9
3455 ,p_attribute10 => p_attribute10
3456 ,p_attribute11 => p_attribute11
3457 ,p_attribute12 => p_attribute12
3458 ,p_attribute13 => p_attribute13
3459 ,p_attribute14 => p_attribute14
3460 ,p_attribute15 => p_attribute15
3461 ,p_attribute16 => p_attribute16
3462 ,p_attribute17 => p_attribute17
3463 ,p_attribute18 => p_attribute18
3464 ,p_attribute19 => p_attribute19
3465 ,p_attribute20 => p_attribute20
3466 ,p_attribute21 => p_attribute21
3467 ,p_attribute22 => p_attribute22
3468 ,p_attribute23 => p_attribute23
3469 ,p_attribute24 => p_attribute24
3470 ,p_attribute25 => p_attribute25
3471 ,p_attribute26 => p_attribute26
3472 ,p_attribute27 => p_attribute27
3473 ,p_attribute28 => p_attribute28
3474 ,p_attribute29 => p_attribute29
3475 ,p_attribute30 => p_attribute30
3476 ,p_attribute_category => p_attribute_category
3477 ,p_segment1 => l_segment1
3478 ,p_segment2 => l_segment2
3479 ,p_segment3 => l_segment3
3480 ,p_segment4 => l_segment4
3481 ,p_segment5 => l_segment5
3482 ,p_segment6 => l_segment6
3483 ,p_segment7 => l_segment7
3484 ,p_segment8 => l_segment8
3485 ,p_segment9 => l_segment9
3486 ,p_segment10 => l_segment10
3487 ,p_segment11 => l_segment11
3488 ,p_segment12 => l_segment12
3489 ,p_segment13 => l_segment13
3490 ,p_segment14 => l_segment14
3491 ,p_segment15 => l_segment15
3492 ,p_segment16 => l_segment16
3493 ,p_segment17 => l_segment17
3494 ,p_segment18 => l_segment18
3495 ,p_segment19 => l_segment19
3496 ,p_segment20 => l_segment20
3497 ,p_segment21 => l_segment21
3498 ,p_segment22 => l_segment22
3499 ,p_segment23 => l_segment23
3500 ,p_segment24 => l_segment24
3501 ,p_segment25 => l_segment25
3502 ,p_segment26 => l_segment26
3503 ,p_segment27 => l_segment27
3504 ,p_segment28 => l_segment28
3505 ,p_segment29 => l_segment29
3506 ,p_segment30 => l_segment30
3507 ,p_concat_segments => p_concat_segments
3508 ,p_request_id => p_request_id
3509 ,p_program_application_id => p_program_application_id
3510 ,p_program_id => p_program_id
3511 ,p_program_update_date => p_program_update_date
3512 ,p_object_version_number => p_object_version_number
3513 ,p_effective_date => trunc(p_effective_date)
3514 ,p_datetrack_mode => p_datetrack_mode
3515 ,p_language_code => l_language_code
3516 );
3517 exception
3518 when hr_api.cannot_find_prog_unit then
3519 hr_api.cannot_find_prog_unit_error
3520 (p_module_name => 'UPDATE_POSITION'
3521 ,p_hook_type => 'BP'
3522 );
3523 --
3524 -- End of API User Hook for the before hook of update_position
3525 --
3526 end;
3527
3528 if g_debug then
3529 hr_utility.set_location(l_proc, 30);
3530 end if;
3531 --
3532 -- Validation in addition to Table Handlers
3533 --
3534 -- Retrieve current position details from position
3535 --
3536 l_api_updating := hr_psf_shd.api_updating
3537 (p_position_id => p_position_id
3538 ,p_effective_Date => p_effective_Date
3539 ,p_object_version_number => l_object_version_number);
3540 --
3541 if g_debug then
3542 hr_utility.set_location(l_proc, 40);
3543 end if;
3544 --
3545 if not l_api_updating then
3546 if g_debug then
3547 hr_utility.set_location(l_proc, 50);
3548 end if;
3549 --
3550 -- As this an updating API, the position should already exist.
3551 --
3552 hr_utility.set_message(801, 'HR_7220_INVALID_PRIMARY_KEY');
3553 hr_utility.raise_error;
3554 else
3555 --
3556 if g_debug then
3557 hr_utility.set_location(l_proc, 60);
3558 end if;
3559 --
3560 if l_null_ind = 0
3561 then
3562 l_position_definition_id := hr_psf_shd.g_old_rec.position_definition_id;
3563 end if;
3564 end if;
3565 --
3566 open csr_idsel;
3567 fetch csr_idsel
3568 into l_flex_num;
3569 if csr_idsel%NOTFOUND then
3570 close csr_idsel;
3571 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
3572 hr_utility.set_message_token('PROCEDURE', l_proc);
3573 hr_utility.set_message_token('STEP','5');
3574 hr_utility.raise_error;
3575 end if;
3576 close csr_idsel;
3577 --
3578 open csr_isfirstrow;
3579 fetch csr_isfirstrow into l_minesd;
3580 if csr_isfirstrow%NOTFOUND then
3581 close csr_idsel;
3582 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
3583 hr_utility.set_message_token('PROCEDURE', l_proc);
3584 hr_utility.set_message_token('STEP','6');
3585 hr_utility.raise_error;
3586 else
3587 close csr_isfirstrow;
3588 end if;
3589 --
3590 if g_debug then
3591 hr_utility.set_location(l_proc||'l_pos_def_id : '
3592 ||l_position_definition_id, 700);
3593 hr_utility.set_location(l_proc||'l_name :' || l_name, 701);
3594 hr_utility.set_location(l_proc, 70);
3595 end if;
3596 --
3597 if l_null_ind = 1 and l_name is null
3598 then
3599 hr_kflex_utility.upd_or_sel_keyflex_comb
3600 (p_appl_short_name => 'PER'
3601 ,p_flex_code => 'POS'
3602 ,p_flex_num => l_flex_num
3603 ,p_segment1 => p_segment1
3604 ,p_segment2 => p_segment2
3605 ,p_segment3 => p_segment3
3606 ,p_segment4 => p_segment4
3607 ,p_segment5 => p_segment5
3608 ,p_segment6 => p_segment6
3609 ,p_segment7 => p_segment7
3610 ,p_segment8 => p_segment8
3611 ,p_segment9 => p_segment9
3612 ,p_segment10 => p_segment10
3613 ,p_segment11 => p_segment11
3614 ,p_segment12 => p_segment12
3615 ,p_segment13 => p_segment13
3616 ,p_segment14 => p_segment14
3617 ,p_segment15 => p_segment15
3618 ,p_segment16 => p_segment16
3619 ,p_segment17 => p_segment17
3620 ,p_segment18 => p_segment18
3621 ,p_segment19 => p_segment19
3622 ,p_segment20 => p_segment20
3623 ,p_segment21 => p_segment21
3624 ,p_segment22 => p_segment22
3625 ,p_segment23 => p_segment23
3626 ,p_segment24 => p_segment24
3627 ,p_segment25 => p_segment25
3628 ,p_segment26 => p_segment26
3629 ,p_segment27 => p_segment27
3630 ,p_segment28 => p_segment28
3631 ,p_segment29 => p_segment29
3632 ,p_segment30 => p_segment30
3633 ,p_ccid => l_position_definition_id
3634 ,p_concat_segments_out => l_name
3635 );
3636 end if;
3637 --
3638 if l_null_ind = 0
3639 then
3640 if nvl(fnd_profile.value('FLEXFIELDS:VALIDATE_ON_SERVER'),'N') = 'Y'
3641 or p_name is null
3642 then
3643 --
3644 hr_kflex_utility.upd_or_sel_keyflex_comb
3645 (p_appl_short_name => 'PER'
3646 ,p_flex_code => 'POS'
3647 ,p_flex_num => l_flex_num
3648 ,p_segment1 => p_segment1
3649 ,p_segment2 => p_segment2
3650 ,p_segment3 => p_segment3
3651 ,p_segment4 => p_segment4
3652 ,p_segment5 => p_segment5
3653 ,p_segment6 => p_segment6
3654 ,p_segment7 => p_segment7
3655 ,p_segment8 => p_segment8
3656 ,p_segment9 => p_segment9
3657 ,p_segment10 => p_segment10
3658 ,p_segment11 => p_segment11
3659 ,p_segment12 => p_segment12
3660 ,p_segment13 => p_segment13
3661 ,p_segment14 => p_segment14
3662 ,p_segment15 => p_segment15
3663 ,p_segment16 => p_segment16
3664 ,p_segment17 => p_segment17
3665 ,p_segment18 => p_segment18
3666 ,p_segment19 => p_segment19
3667 ,p_segment20 => p_segment20
3668 ,p_segment21 => p_segment21
3669 ,p_segment22 => p_segment22
3670 ,p_segment23 => p_segment23
3671 ,p_segment24 => p_segment24
3672 ,p_segment25 => p_segment25
3673 ,p_segment26 => p_segment26
3674 ,p_segment27 => p_segment27
3675 ,p_segment28 => p_segment28
3676 ,p_segment29 => p_segment29
3677 ,p_segment30 => p_segment30
3678 ,p_concat_segments_in => p_concat_segments
3679 ,p_ccid => l_position_definition_id
3680 ,p_concat_segments_out => l_name
3681 );
3682 end if;
3683 end if;
3684 --
3685 if g_debug then
3686 hr_utility.set_location(l_proc||'l_pos_def_id : '
3687 || l_position_definition_id, 700);
3688 hr_utility.set_location(l_proc||'l_name :' || l_name, 701);
3689 hr_utility.set_location(l_proc, 80);
3690 end if;
3691 /*
3692 --
3693 -- Because we may need to maintain the valid grade dates, need to
3694 -- explicitly lock the hr_all_positions_f row.
3695 --
3696 -- hr_psf_shd.lck(p_position_id => p_position_id
3697 -- ,p_object_version_number => l_object_version_number);
3698 --
3699 if g_debug then
3700 hr_utility.set_location(l_proc, 85);
3701 end if;
3702 Maintain Grade requirements to be investigated
3703 --
3704 -- If date_effective is being updated , then need to maintain valid grades
3705 -- accordingly for that position.
3706 --
3707 IF ((nvl(p_date_effective, hr_api.g_date) <> hr_api.g_date) and
3708 hr_psf_shd.g_old_rec.date_effective <>
3709 nvl(p_date_effective, hr_api.g_date)) THEN
3710 --
3711
3712 PER_POSITIONS_PKG.maintain_valid_grades
3713 (p_validate => p_validate
3714 ,p_position_id => p_position_id
3715 ,p_maintenance_mode => 'DATE_EFFECTIVE'
3716 ,p_date_end => l_date_end
3717 ,p_date_effective => l_date_effective
3718 ,p_valid_grades_changed => l_valid_grades_changed1);
3719 --
3720 end if;
3721 --
3722 if g_debug then
3723 hr_utility.set_location(l_proc, 90);
3724 end if;
3725 --
3726 -- If date_end is being updated , then need to maintain valid grades
3727 -- accordingly for that position.
3728 --
3729 IF ((nvl(p_date_end, hr_api.g_date) <> hr_api.g_date) and
3730 nvl(hr_psf_shd.g_old_rec.date_end, hr_api.g_date) <>
3731 nvl(p_date_end, hr_api.g_date)) THEN
3732 --
3733 pER_POSITIONS_PKG.maintain_valid_grades
3734 (p_validate => p_validate
3735 ,p_position_id => p_position_id
3736 ,p_maintenance_mode => 'DATE_END'
3737 ,p_date_end => l_date_end
3738 ,p_date_effective => l_date_effective
3739 ,p_valid_grades_changed => l_valid_grades_changed2);
3740 --
3741 end if;
3742 */
3743 --
3744 -- Update Position Details
3745 --
3746 if g_debug then
3747 hr_utility.set_location(l_proc||'l_pos_def_id : '
3748 || l_position_definition_id, 800);
3749 hr_utility.set_location(l_proc||'l_name :' || l_name, 801);
3750 end if;
3751 --
3752 hr_psf_upd.upd
3753 (
3754 p_position_id => p_position_id
3755 ,p_effective_start_date => l_effective_start_date
3756 ,p_effective_end_date => l_effective_end_date
3757 ,p_availability_status_id => p_availability_status_id
3758 -- ,p_business_group_id => p_business_group_id
3759 ,p_entry_step_id => p_entry_step_id
3760 ,p_entry_grade_rule_id => p_entry_grade_rule_id
3761 -- ,p_job_id => p_job_id
3762 ,p_location_id => p_location_id
3763 -- ,p_organization_id => p_organization_id
3764 ,p_pay_freq_payroll_id => p_pay_freq_payroll_id
3765 ,p_position_definition_id => l_position_definition_id
3766 ,p_position_transaction_id => p_position_transaction_id
3767 ,p_prior_position_id => p_prior_position_id
3768 ,p_relief_position_id => p_relief_position_id
3769 ,p_entry_grade_id => p_entry_grade_id
3770 ,p_successor_position_id => p_successor_position_id
3771 ,p_supervisor_position_id => p_supervisor_position_id
3772 ,p_amendment_date => p_amendment_date
3773 ,p_amendment_recommendation => p_amendment_recommendation
3774 ,p_amendment_ref_number => p_amendment_ref_number
3775 ,p_bargaining_unit_cd => p_bargaining_unit_cd
3776 ,p_comments => p_comments
3777 ,p_current_job_prop_end_date => p_current_job_prop_end_date
3778 ,p_current_org_prop_end_date => p_current_org_prop_end_date
3779 ,p_avail_status_prop_end_date => p_avail_status_prop_end_date
3780 ,p_date_effective => l_date_effective
3781 ,p_date_end => p_date_end
3782 ,p_earliest_hire_date => p_earliest_hire_date
3783 ,p_fill_by_date => p_fill_by_date
3784 ,p_frequency => p_frequency
3785 ,p_fte => p_fte
3786 ,p_max_persons => p_max_persons
3787 ,p_name => l_name
3788 ,p_overlap_period => p_overlap_period
3789 ,p_overlap_unit_cd => p_overlap_unit_cd
3790 ,p_pay_term_end_day_cd => p_pay_term_end_day_cd
3791 ,p_pay_term_end_month_cd => p_pay_term_end_month_cd
3792 ,p_permanent_temporary_flag => p_permanent_temporary_flag
3793 ,p_permit_recruitment_flag => p_permit_recruitment_flag
3794 ,p_position_type => p_position_type
3795 ,p_posting_description => p_posting_description
3796 ,p_probation_period => p_probation_period
3797 ,p_probation_period_unit_cd => p_probation_period_unit_cd
3798 ,p_replacement_required_flag => p_replacement_required_flag
3799 ,p_review_flag => p_review_flag
3800 ,p_seasonal_flag => p_seasonal_flag
3801 ,p_security_requirements => p_security_requirements
3802 ,p_status => p_status
3803 ,p_term_start_day_cd => p_term_start_day_cd
3804 ,p_term_start_month_cd => p_term_start_month_cd
3805 ,p_time_normal_finish => p_time_normal_finish
3806 ,p_time_normal_start => p_time_normal_start
3807 ,p_update_source_cd => p_update_source_cd
3808 ,p_working_hours => p_working_hours
3809 ,p_works_council_approval_flag => p_works_council_approval_flag
3810 ,p_work_period_type_cd => p_work_period_type_cd
3811 ,p_work_term_end_day_cd => p_work_term_end_day_cd
3812 ,p_work_term_end_month_cd => p_work_term_end_month_cd
3813 ,p_proposed_fte_for_layoff => p_proposed_fte_for_layoff
3814 ,p_proposed_date_for_layoff => p_proposed_date_for_layoff
3815 ,p_pay_basis_id => p_pay_basis_id
3816 ,p_supervisor_id => p_supervisor_id
3817 ,p_copied_to_old_table_flag => 'N'
3818 ,p_information1 => p_information1
3819 ,p_information2 => p_information2
3820 ,p_information3 => p_information3
3821 ,p_information4 => p_information4
3822 ,p_information5 => p_information5
3823 ,p_information6 => p_information6
3824 ,p_information7 => p_information7
3825 ,p_information8 => p_information8
3826 ,p_information9 => p_information9
3827 ,p_information10 => p_information10
3828 ,p_information11 => p_information11
3829 ,p_information12 => p_information12
3830 ,p_information13 => p_information13
3831 ,p_information14 => p_information14
3832 ,p_information15 => p_information15
3833 ,p_information16 => p_information16
3834 ,p_information17 => p_information17
3835 ,p_information18 => p_information18
3836 ,p_information19 => p_information19
3837 ,p_information20 => p_information20
3838 ,p_information21 => p_information21
3839 ,p_information22 => p_information22
3840 ,p_information23 => p_information23
3841 ,p_information24 => p_information24
3842 ,p_information25 => p_information25
3843 ,p_information26 => p_information26
3844 ,p_information27 => p_information27
3845 ,p_information28 => p_information28
3846 ,p_information29 => p_information29
3847 ,p_information30 => p_information30
3848 ,p_information_category => p_information_category
3849 ,p_attribute1 => p_attribute1
3850 ,p_attribute2 => p_attribute2
3851 ,p_attribute3 => p_attribute3
3852 ,p_attribute4 => p_attribute4
3853 ,p_attribute5 => p_attribute5
3854 ,p_attribute6 => p_attribute6
3855 ,p_attribute7 => p_attribute7
3856 ,p_attribute8 => p_attribute8
3857 ,p_attribute9 => p_attribute9
3858 ,p_attribute10 => p_attribute10
3859 ,p_attribute11 => p_attribute11
3860 ,p_attribute12 => p_attribute12
3861 ,p_attribute13 => p_attribute13
3862 ,p_attribute14 => p_attribute14
3863 ,p_attribute15 => p_attribute15
3864 ,p_attribute16 => p_attribute16
3865 ,p_attribute17 => p_attribute17
3866 ,p_attribute18 => p_attribute18
3867 ,p_attribute19 => p_attribute19
3868 ,p_attribute20 => p_attribute20
3869 ,p_attribute21 => p_attribute21
3870 ,p_attribute22 => p_attribute22
3871 ,p_attribute23 => p_attribute23
3872 ,p_attribute24 => p_attribute24
3873 ,p_attribute25 => p_attribute25
3874 ,p_attribute26 => p_attribute26
3875 ,p_attribute27 => p_attribute27
3876 ,p_attribute28 => p_attribute28
3877 ,p_attribute29 => p_attribute29
3878 ,p_attribute30 => p_attribute30
3879 ,p_attribute_category => p_attribute_category
3880 ,p_request_id => p_request_id
3881 ,p_program_application_id => p_program_application_id
3882 ,p_program_id => p_program_id
3883 ,p_program_update_date => p_program_update_date
3884 ,p_object_version_number => l_object_version_number
3885 ,p_effective_date => trunc(p_effective_date)
3886 ,p_datetrack_mode => p_datetrack_mode
3887 ,p_validate => p_validate
3888 );
3889 --
3890 -- PMFLETCH Update translation table if base table record is to eot and
3891 -- position_definition_id has been updated.
3892 --
3893 if ( l_effective_end_date = hr_api.g_eot
3894 AND (
3895 hr_psf_shd.g_old_rec.position_definition_id <> l_position_definition_id
3896 or
3897 hr_psf_shd.g_old_rec.name <> l_name
3898 )
3899 ) then
3900 hr_pft_upd.upd_tl
3901 ( p_language_code => l_language_code
3902 , p_position_id => p_position_id
3903 , p_position_definition_id => l_position_definition_id
3904 );
3905 end if;
3906 --
3907 -- Refresh per_all_positions
3908 --
3909 begin
3910 --
3911 -- call refresh_position procedure to replicate changes in per_all_positions
3912 --
3913 if g_debug then
3914 hr_utility.set_location ( l_proc, 1000);
3915 end if;
3916 --
3917 synchronize_per_all_positions
3918 (p_position_id => p_position_id
3919 ,p_effective_date => p_effective_date
3920 ,p_datetrack_mode => p_datetrack_mode
3921 ,p_object_version_number => l_object_version_number
3922 );
3923 --
3924 if g_debug then
3925 hr_utility.set_location ( l_proc, 1001);
3926 end if;
3927 --
3928 end;
3929 --
3930 begin
3931 --
3932 -- Start of API User Hook for the after hook of update_position
3933 --
3934 hr_position_bk2.update_position_a
3935 (
3936 p_position_id => p_position_id
3937 ,p_effective_start_date => l_effective_start_date
3938 ,p_effective_end_date => l_effective_end_date
3939 ,p_availability_status_id => p_availability_status_id
3940 -- ,p_business_group_id => p_business_group_id
3941 ,p_entry_step_id => p_entry_step_id
3942 ,p_entry_grade_rule_id => p_entry_grade_rule_id
3943 -- ,p_job_id => p_job_id
3944 ,p_location_id => p_location_id
3945 -- ,p_organization_id => p_organization_id
3946 ,p_pay_freq_payroll_id => p_pay_freq_payroll_id
3947 ,p_position_definition_id => l_position_definition_id
3948 ,p_position_transaction_id => p_position_transaction_id
3949 ,p_prior_position_id => p_prior_position_id
3950 ,p_relief_position_id => p_relief_position_id
3951 ,p_entry_grade_id => p_entry_grade_id
3952 ,p_successor_position_id => p_successor_position_id
3953 ,p_supervisor_position_id => p_supervisor_position_id
3954 ,p_amendment_date => p_amendment_date
3955 ,p_amendment_recommendation => p_amendment_recommendation
3956 ,p_amendment_ref_number => p_amendment_ref_number
3957 ,p_bargaining_unit_cd => p_bargaining_unit_cd
3958 ,p_comments => p_comments
3959 ,p_current_job_prop_end_date => p_current_job_prop_end_date
3960 ,p_current_org_prop_end_date => p_current_org_prop_end_date
3961 ,p_avail_status_prop_end_date => p_avail_status_prop_end_date
3962 ,p_date_effective => l_date_effective
3963 ,p_date_end => l_date_end
3964 ,p_earliest_hire_date => p_earliest_hire_date
3965 ,p_fill_by_date => p_fill_by_date
3966 ,p_frequency => p_frequency
3967 ,p_fte => p_fte
3968 ,p_max_persons => p_max_persons
3969 ,p_name => p_name
3970 ,p_overlap_period => p_overlap_period
3971 ,p_overlap_unit_cd => p_overlap_unit_cd
3972 ,p_pay_term_end_day_cd => p_pay_term_end_day_cd
3973 ,p_pay_term_end_month_cd => p_pay_term_end_month_cd
3974 ,p_permanent_temporary_flag => p_permanent_temporary_flag
3975 ,p_permit_recruitment_flag => p_permit_recruitment_flag
3976 ,p_position_type => p_position_type
3977 ,p_posting_description => p_posting_description
3978 ,p_probation_period => p_probation_period
3979 ,p_probation_period_unit_cd => p_probation_period_unit_cd
3980 ,p_replacement_required_flag => p_replacement_required_flag
3981 ,p_review_flag => p_review_flag
3982 ,p_seasonal_flag => p_seasonal_flag
3983 ,p_security_requirements => p_security_requirements
3984 ,p_status => p_status
3985 ,p_term_start_day_cd => p_term_start_day_cd
3986 ,p_term_start_month_cd => p_term_start_month_cd
3987 ,p_time_normal_finish => p_time_normal_finish
3988 ,p_time_normal_start => p_time_normal_start
3989 ,p_update_source_cd => p_update_source_cd
3990 ,p_working_hours => p_working_hours
3991 ,p_works_council_approval_flag => p_works_council_approval_flag
3992 ,p_work_period_type_cd => p_work_period_type_cd
3993 ,p_work_term_end_day_cd => p_work_term_end_day_cd
3994 ,p_work_term_end_month_cd => p_work_term_end_month_cd
3995 ,p_proposed_fte_for_layoff => p_proposed_fte_for_layoff
3996 ,p_proposed_date_for_layoff => p_proposed_date_for_layoff
3997 ,p_pay_basis_id => p_pay_basis_id
3998 ,p_supervisor_id => p_supervisor_id
3999 --,p_copied_to_old_table_flag => p_copied_to_old_table_flag
4000 ,p_information1 => p_information1
4001 ,p_information2 => p_information2
4002 ,p_information3 => p_information3
4003 ,p_information4 => p_information4
4004 ,p_information5 => p_information5
4005 ,p_information6 => p_information6
4006 ,p_information7 => p_information7
4007 ,p_information8 => p_information8
4008 ,p_information9 => p_information9
4009 ,p_information10 => p_information10
4010 ,p_information11 => p_information11
4011 ,p_information12 => p_information12
4012 ,p_information13 => p_information13
4013 ,p_information14 => p_information14
4014 ,p_information15 => p_information15
4015 ,p_information16 => p_information16
4016 ,p_information17 => p_information17
4017 ,p_information18 => p_information18
4018 ,p_information19 => p_information19
4019 ,p_information20 => p_information20
4020 ,p_information21 => p_information21
4021 ,p_information22 => p_information22
4022 ,p_information23 => p_information23
4023 ,p_information24 => p_information24
4024 ,p_information25 => p_information25
4025 ,p_information26 => p_information26
4026 ,p_information27 => p_information27
4027 ,p_information28 => p_information28
4028 ,p_information29 => p_information29
4029 ,p_information30 => p_information30
4030 ,p_information_category => p_information_category
4031 ,p_attribute1 => p_attribute1
4032 ,p_attribute2 => p_attribute2
4033 ,p_attribute3 => p_attribute3
4034 ,p_attribute4 => p_attribute4
4035 ,p_attribute5 => p_attribute5
4036 ,p_attribute6 => p_attribute6
4037 ,p_attribute7 => p_attribute7
4038 ,p_attribute8 => p_attribute8
4039 ,p_attribute9 => p_attribute9
4040 ,p_attribute10 => p_attribute10
4041 ,p_attribute11 => p_attribute11
4042 ,p_attribute12 => p_attribute12
4043 ,p_attribute13 => p_attribute13
4044 ,p_attribute14 => p_attribute14
4045 ,p_attribute15 => p_attribute15
4046 ,p_attribute16 => p_attribute16
4047 ,p_attribute17 => p_attribute17
4048 ,p_attribute18 => p_attribute18
4049 ,p_attribute19 => p_attribute19
4050 ,p_attribute20 => p_attribute20
4051 ,p_attribute21 => p_attribute21
4052 ,p_attribute22 => p_attribute22
4053 ,p_attribute23 => p_attribute23
4054 ,p_attribute24 => p_attribute24
4055 ,p_attribute25 => p_attribute25
4056 ,p_attribute26 => p_attribute26
4057 ,p_attribute27 => p_attribute27
4058 ,p_attribute28 => p_attribute28
4059 ,p_attribute29 => p_attribute29
4060 ,p_attribute30 => p_attribute30
4061 ,p_attribute_category => p_attribute_category
4062 ,p_segment1 => l_segment1
4063 ,p_segment2 => l_segment2
4064 ,p_segment3 => l_segment3
4065 ,p_segment4 => l_segment4
4066 ,p_segment5 => l_segment5
4067 ,p_segment6 => l_segment6
4068 ,p_segment7 => l_segment7
4069 ,p_segment8 => l_segment8
4070 ,p_segment9 => l_segment9
4071 ,p_segment10 => l_segment10
4072 ,p_segment11 => l_segment11
4073 ,p_segment12 => l_segment12
4074 ,p_segment13 => l_segment13
4075 ,p_segment14 => l_segment14
4076 ,p_segment15 => l_segment15
4077 ,p_segment16 => l_segment16
4078 ,p_segment17 => l_segment17
4079 ,p_segment18 => l_segment18
4080 ,p_segment19 => l_segment19
4081 ,p_segment20 => l_segment20
4082 ,p_segment21 => l_segment21
4083 ,p_segment22 => l_segment22
4084 ,p_segment23 => l_segment23
4085 ,p_segment24 => l_segment24
4086 ,p_segment25 => l_segment25
4087 ,p_segment26 => l_segment26
4088 ,p_segment27 => l_segment27
4089 ,p_segment28 => l_segment28
4090 ,p_segment29 => l_segment29
4091 ,p_segment30 => l_segment30
4092 ,p_concat_segments => p_concat_segments
4093 ,p_request_id => p_request_id
4094 ,p_program_application_id => p_program_application_id
4095 ,p_program_id => p_program_id
4096 ,p_program_update_date => p_program_update_date
4097 ,p_object_version_number => l_object_version_number
4098 ,p_effective_date => trunc(p_effective_date)
4099 ,p_datetrack_mode => p_datetrack_mode
4100 ,p_language_code => l_language_code
4101 );
4102 exception
4103 when hr_api.cannot_find_prog_unit then
4104 hr_api.cannot_find_prog_unit_error
4105 (p_module_name => 'UPDATE_POSITION'
4106 ,p_hook_type => 'AP'
4107 );
4108 --
4109 -- End of API User Hook for the after hook of update_position
4110 --
4111 end;
4112 --
4113 if g_debug then
4114 hr_utility.set_location(l_proc, 100);
4115 end if;
4116 -- ----------- Maintain Grade requirements to be investigated
4117 --
4118 -- Because we may need to maintain the valid grade dates, need to
4119 -- explicitly lock the hr_all_positions_f row.
4120 --
4121 -- hr_psf_shd.lck(p_position_id => p_position_id
4122 -- ,p_object_version_number => l_object_version_number);
4123 --
4124 if g_debug then
4125 hr_utility.set_location(l_proc, 85);
4126 end if;
4127 --
4128 -- If date_effective is being updated , then need to maintain valid grades
4129 -- accordingly for that position.
4130 --
4131 IF ((nvl(p_date_effective, hr_api.g_date) <> hr_api.g_date) and
4132 hr_psf_shd.g_old_rec.date_effective <>
4133 nvl(p_date_effective, hr_api.g_date)) THEN
4134 --
4135
4136 maintain_valid_grades
4137 (p_validate => p_validate
4138 ,p_position_id => p_position_id
4139 ,p_maintenance_mode => 'DATE_EFFECTIVE'
4140 ,p_date_end => l_date_end
4141 ,p_date_effective => l_date_effective
4142 ,p_valid_grades_changed => l_valid_grades_changed1);
4143 --
4144 end if;
4145 --
4146 if g_debug then
4147 hr_utility.set_location(l_proc, 90);
4148 end if;
4149 --
4150 -- If date_end is being updated , then need to maintain valid grades
4151 -- accordingly for that position.
4152 --
4153 IF ((nvl(p_date_end, hr_api.g_date) <> hr_api.g_date) and
4154 nvl(hr_psf_shd.g_old_rec.date_end, hr_api.g_date) <>
4155 nvl(p_date_end, hr_api.g_date)) THEN
4156 --
4157 maintain_valid_grades
4158 (p_validate => p_validate
4159 ,p_position_id => p_position_id
4160 ,p_maintenance_mode => 'DATE_END'
4161 ,p_date_end => l_date_end
4162 ,p_date_effective => l_date_effective
4163 ,p_valid_grades_changed => l_valid_grades_changed2);
4164 --
4165 end if;
4166 --
4167 if l_valid_grades_changed1 or l_valid_grades_changed2 then
4168 p_valid_grades_changed_warning := TRUE;
4169 else
4170 p_valid_grades_changed_warning := FALSE;
4171 end if;
4172 --
4173 if g_debug then
4174 hr_utility.set_location(l_proc, 110);
4175 end if;
4176 --
4177 -- When in validation only mode raise the Validate_Enabled exception
4178 --
4179 if p_validate then
4180 raise hr_api.validate_enabled;
4181 end if;
4182 --
4183 -- Set all output arguments
4184 --
4185 p_position_definition_id := l_position_definition_id;
4186 p_name := l_name;
4187 p_object_version_number := l_object_version_number;
4188 p_effective_start_date := l_effective_start_date;
4189 p_effective_end_date := l_effective_end_date;
4190 --
4191 if g_debug then
4192 hr_utility.set_location('date effective is '||to_char(p_date_effective)
4193 ||l_proc,192);
4194 hr_utility.set_location(' Leaving:'||l_proc, 120);
4195 end if;
4196 --
4197 exception
4198 --
4199 when hr_api.validate_enabled then
4200 --
4201 -- As the Validate_Enabled exception has been raised
4202 -- we must rollback to the savepoint
4203 --
4204 ROLLBACK TO update_position;
4205 --
4206 -- Only set output warning arguments
4207 -- (Any key or derived arguments must be set to null
4208 -- when validation only mode is being used.)
4209 --
4210 p_object_version_number := p_object_version_number;
4211 p_position_definition_id := p_position_definition_id;
4212 if g_debug then
4213 hr_utility.set_location(' Leaving:'||l_proc, 130);
4214 end if;
4215 --
4216 when others then
4217 --
4218 -- A validation or unexpected error has occured
4219 --
4220 p_object_version_number := l_object_version_number;
4221 p_position_definition_id := l_position_definition_id;
4222 p_name := l_name;
4223 p_effective_start_date := null;
4224 p_effective_end_date := null;
4225 p_valid_grades_changed_warning := null;
4226
4227 ROLLBACK TO update_position;
4228 raise;
4229 --
4230 end update_position;
4231 -- ----------------------------------------------------------------------------
4232 -- |------------------------< delete_position >----------------------|
4233 -- ----------------------------------------------------------------------------
4234 --
4235 procedure delete_position
4236 (p_validate in boolean default false
4237 ,p_position_id in number
4238 ,p_effective_start_date out nocopy date
4239 ,p_effective_end_date out nocopy date
4240 ,p_object_version_number in out nocopy number
4241 ,p_effective_date in date
4242 ,p_datetrack_mode in varchar2
4243 ,p_security_profile_id in number default hr_security.get_security_profile
4244 ) is
4245 --
4246 -- Declare cursors and local variables
4247 --
4248 l_proc varchar2(72) ;
4249 l_object_version_number hr_all_positions_f.object_version_number%TYPE;
4250 l_effective_start_date hr_all_positions_f.effective_start_date%TYPE;
4251 l_effective_end_date hr_all_positions_f.effective_end_date%TYPE;
4252 --
4253 l_view_all_positions_flag varchar2(30);
4254 --
4255 cursor c1 is
4256 select view_all_positions_flag
4257 from per_security_profiles
4258 where security_profile_id = p_security_profile_id;
4259 --
4260 begin
4261
4262 --
4263 g_debug := hr_utility.debug_enabled;
4264 if g_debug then
4265 l_proc := g_package||'delete_position';
4266 hr_utility.set_location('Entering:'|| l_proc, 10);
4267 end if;
4268 --
4269 -- Issue a savepoint if operating in validation only mode
4270 --
4271 savepoint delete_position;
4272 --
4273 if g_debug then
4274 hr_utility.set_location(l_proc, 20);
4275 end if;
4276 --
4277 -- Process Logic
4278 --
4279 l_object_version_number := p_object_version_number;
4280 --
4281 --
4282 begin
4283 --
4284 -- Start of API User Hook for the before hook of delete_position
4285 --
4286 hr_position_bk3.delete_position_b
4287 (
4288 p_position_id => p_position_id
4289 ,p_object_version_number => p_object_version_number
4290 ,p_effective_date => trunc(p_effective_date)
4291 ,p_datetrack_mode => p_datetrack_mode
4292 );
4293 exception
4294 when hr_api.cannot_find_prog_unit then
4295 hr_api.cannot_find_prog_unit_error
4296 (p_module_name => 'DELETE_POSITION'
4297 ,p_hook_type => 'BP'
4298 );
4299 --
4300 -- End of API User Hook for the before hook of delete_position
4301 --
4302 end;
4303 --
4304 begin
4305 --
4306 --
4307 -- Delete record from position security list table
4308 --
4309 if (p_datetrack_mode = 'ZAP') then
4310 open c1;
4311 fetch c1 into l_view_all_positions_flag;
4312 close c1;
4313 --
4314 -- if l_view_all_positions_flag <> 'Y' then
4315 hr_security.delete_pos_from_list(p_position_Id);
4316 -- end if;
4317 end if;
4318 --
4319 end;
4320 --
4321 -- PMFLETCH Delete TL Table
4322 --
4323 if (p_datetrack_mode = 'ZAP') then
4324 hr_pft_del.del_tl
4325 ( p_position_id => p_position_id
4326 , p_datetrack_mode => p_datetrack_mode
4327 );
4328 end if;
4329 --
4330 declare
4331 --
4332 cursor c_position_definition(p_position_id number) is
4333 select position_definition_id
4334 from hr_all_positions_f
4335 where position_id = p_position_id;
4336 --
4337 l_position_definition_id number;
4338 l_pos_def_deleted boolean;
4339 begin
4340 open c_position_definition(p_position_id);
4341 --
4342 hr_psf_del.del
4343 (
4344 p_position_id => p_position_id
4345 ,p_effective_start_date => l_effective_start_date
4346 ,p_effective_end_date => l_effective_end_date
4347 ,p_object_version_number => l_object_version_number
4348 ,p_effective_date => p_effective_date
4349 ,p_datetrack_mode => p_datetrack_mode
4350 ,p_validate => p_validate
4351 ,p_security_profile_id => p_security_profile_id
4352 );
4353 --
4354 -- PMFLETCH Delete/Update TL Table
4355 --
4356 if (p_datetrack_mode <> 'ZAP') then
4357 hr_pft_del.del_tl
4358 ( p_position_id => p_position_id
4359 , p_datetrack_mode => p_datetrack_mode
4360 );
4361 end if;
4362
4363 --
4364
4365 --
4366 begin
4367 --
4368 -- call refresh_position procedure to replicate changes in per_all_positions
4369 --
4370 if g_debug then
4371 hr_utility.set_location ( l_proc, 1000);
4372 end if;
4373 --
4374 synchronize_per_all_positions
4375 (p_position_id => p_position_id
4376 ,p_effective_date => p_effective_date
4377 ,p_datetrack_mode => p_datetrack_mode
4378 ,p_object_version_number => l_object_version_number
4379 );
4380 --
4381 if g_debug then
4382 hr_utility.set_location ( l_proc, 1001);
4383 end if;
4384 --
4385 -- delete Position Defintions if not used
4386 --
4387 if (not p_validate) then
4388 loop
4389 fetch c_position_definition into l_position_definition_id;
4390 exit when (c_position_definition%notfound);
4391 l_pos_def_deleted := delete_unused_per_pos_def(l_position_definition_id);
4392 end loop;
4393 end if;
4394 close c_position_definition;
4395 exception
4396 when others then
4397 if (c_position_definition%isopen) then
4398 close c_position_definition;
4399 end if;
4400 raise;
4401 end;
4402 --
4403 end;
4404 --
4405 begin
4406 --
4407 -- Start of API User Hook for the after hook of delete_position
4408 --
4409 hr_position_bk3.delete_position_a
4410 (
4411 p_position_id => p_position_id
4412 ,p_effective_start_date => l_effective_start_date
4413 ,p_effective_end_date => l_effective_end_date
4414 ,p_object_version_number => l_object_version_number
4415 ,p_effective_date => trunc(p_effective_date)
4416 ,p_datetrack_mode => p_datetrack_mode
4417 );
4418 exception
4419 when hr_api.cannot_find_prog_unit then
4420 hr_api.cannot_find_prog_unit_error
4421 (p_module_name => 'DELETE_POSITION'
4422 ,p_hook_type => 'AP'
4423 );
4424 --
4425 -- End of API User Hook for the after hook of delete_position
4426 --
4427 end;
4428 --
4429 if g_debug then
4430 hr_utility.set_location(l_proc, 60);
4431 end if;
4432 --
4433 -- When in validation only mode raise the Validate_Enabled exception
4434 --
4435 if p_validate then
4436 raise hr_api.validate_enabled;
4437 end if;
4438 --
4439 if g_debug then
4440 hr_utility.set_location(' Leaving:'||l_proc, 70);
4441 end if;
4442 --
4443 exception
4444 --
4445 when hr_api.validate_enabled then
4446 --
4447 -- As the Validate_Enabled exception has been raised
4448 -- we must rollback to the savepoint
4449 --
4450 ROLLBACK TO delete_position;
4451 --
4452 -- Only set output warning arguments
4453 -- (Any key or derived arguments must be set to null
4454 -- when validation only mode is being used.)
4455 --
4456 p_effective_start_date := null;
4457 p_effective_end_date := null;
4458 --
4459 when others then
4460 --
4461 -- A validation or unexpected error has occured
4462 --
4463 p_effective_start_date := null;
4464 p_effective_end_date := null;
4465 p_object_version_number := l_object_version_number;
4466
4467 ROLLBACK TO delete_position;
4468 raise;
4469 --
4470 end delete_position;
4471 --
4472 -- ----------------------------------------------------------------------------
4473 -- |-------------------------------< lck >------------------------------------|
4474 -- ----------------------------------------------------------------------------
4475 --
4476 procedure lck
4477 (
4478 p_position_id in number
4479 ,p_object_version_number in number
4480 ,p_effective_date in date
4481 ,p_datetrack_mode in varchar2
4482 ,p_validation_start_date out nocopy date
4483 ,p_validation_end_date out nocopy date
4484 ,p_language_code in varchar2 default hr_api.userenv_lang
4485 ) is
4486 --
4487 --
4488 -- Declare cursors and local variables
4489 --
4490 l_proc varchar2(72) ;
4491 l_validation_start_date date;
4492 l_validation_end_date date;
4493 --
4494 begin
4495 --
4496 g_debug := hr_utility.debug_enabled;
4497 if g_debug then
4498 l_proc := g_package||'lck';
4499 hr_utility.set_location('Entering:'|| l_proc, 10);
4500 end if;
4501 --
4502 hr_psf_shd.lck
4503 (
4504 p_position_id => p_position_id
4505 ,p_validation_start_date => l_validation_start_date
4506 ,p_validation_end_date => l_validation_end_date
4507 ,p_object_version_number => p_object_version_number
4508 ,p_effective_date => p_effective_date
4509 ,p_datetrack_mode => p_datetrack_mode
4510 );
4511 --
4512 if g_debug then
4513 hr_utility.set_location(' Leaving:'||l_proc, 70);
4514 end if;
4515 --
4516 end lck;
4517 --
4518 -- ----------------------------------------------------------------------------
4519 -- |------------------------< regenerate_position_name >----------------------|
4520 -- ----------------------------------------------------------------------------
4521 -- Regenerate_position_name to rebuild position name for only one position
4522 -- from current flexfield values
4523 --
4524 -- If this process is called at the server-side ensure that
4525 -- fnd_profiles are initialized when position flexfield valuesets
4526 -- uses profile values
4527 --
4528 procedure regenerate_position_name(p_position_id number) is
4529 cursor c_position(p_position_id number) is
4530 select psf.effective_start_date, psf.position_definition_id,
4531 psf.object_version_number, pd.id_flex_num
4532 from hr_all_positions_f psf, per_position_definitions pd
4533 where position_id = p_position_id
4534 and psf.position_definition_id = pd.position_definition_id
4535 and effective_end_date = hr_api.g_eot;
4536 --
4537 l_effective_start_date date;
4538 l_effective_end_date date;
4539 l_position_definition_id number;
4540 l_valid_grades_changed_warning boolean;
4541 l_name varchar2(420);
4542 l_object_version_number number;
4543 l_effective_date date;
4544 l_id_flex_num number;
4545 l_commit number;
4546 begin
4547 if (p_position_id is not null) then
4548 --
4549 open c_position(p_position_id);
4550 fetch c_position into l_effective_date, l_position_definition_id,
4551 l_object_version_number, l_id_flex_num;
4552 --
4553 if (c_position%found) then
4554 --
4555 dt_fndate.change_ses_date(trunc(l_effective_date),l_commit);
4556 --
4557 l_name := FND_FLEX_EXT.GET_SEGS('PER', 'POS', l_id_flex_num, l_position_definition_id);
4558 --
4559 hr_position_api.update_position
4560 (p_validate => false
4561 ,p_position_id => p_position_id
4562 ,p_effective_start_date => l_effective_start_date
4563 ,p_effective_end_date => l_effective_end_date
4564 ,p_position_definition_id => l_position_definition_id
4565 ,p_valid_grades_changed_warning => l_valid_grades_changed_warning
4566 ,p_name => l_name
4567 ,p_object_version_number => l_object_version_number
4568 ,p_effective_date => l_effective_date
4569 ,p_datetrack_mode => 'CORRECTION'
4570 );
4571 --
4572 end if;
4573 --
4574 close c_position;
4575 --
4576 end if;
4577 --
4578 end;
4579 --
4580 -- ----------------------------------------------------------------------------
4581 -- |------------------------< regenerate_position_names >---------------------|
4582 -- ----------------------------------------------------------------------------
4583 -- Regenerate Position Names process is used to rebuild
4584 -- position names using current Position flexfield values
4585 --
4586 procedure regenerate_position_names(
4587 errbuf out nocopy varchar2
4588 , retcode out nocopy number
4589 , p_business_group_id number,
4590 p_organization_id number) is
4591 --
4592 cursor c_all_positions is
4593 select psf.position_id
4594 from hr_all_positions_f psf
4595 where psf.effective_end_date = hr_api.g_eot;
4596 --
4597 cursor c_bg_positions(p_business_group_id number) is
4598 select psf.position_id
4599 from hr_all_positions_f psf
4600 where psf.business_group_id = p_business_group_id
4601 and psf.effective_end_date = hr_api.g_eot;
4602 --
4603 cursor c_org_positions(p_organization_id number) is
4604 select psf.position_id
4605 from hr_all_positions_f psf
4606 where psf.organization_id = p_organization_id
4607 and psf.effective_end_date = hr_api.g_eot;
4608 --
4609 l_position_id number;
4610 --
4611 begin
4612 if (p_organization_id is not null) then
4613 -- Regenerate Position names for all positions in an organization
4614 for r_pos in c_org_positions(p_organization_id)
4615 loop
4616 l_position_id := r_pos.position_id;
4617 regenerate_position_name(r_pos.position_id);
4618 commit;
4619 end loop;
4620 elsif (p_business_group_id is not null) then
4621 -- Regenerate Position names for all positions in a Business Group
4622 for r_pos in c_bg_positions(p_business_group_id)
4623 loop
4624 l_position_id := r_pos.position_id;
4625 regenerate_position_name(r_pos.position_id);
4626 commit;
4627 end loop;
4628 else
4629 -- Regenerate Position names for all positions
4630 for r_pos in c_all_positions
4631 loop
4632 l_position_id := r_pos.position_id;
4633 regenerate_position_name(r_pos.position_id);
4634 commit;
4635 end loop;
4636 end if;
4637 --
4638 --
4639 exception
4640 when others then
4641 retcode := 2;
4642 errbuf := SQLERRM;
4643 end;
4644 --
4645 -- end of date tracked position api code
4646 --
4647 --
4648 end hr_position_api;