1 Package Body hr_per_type_usage_internal as
2 /* $Header: peptubsi.pkb 120.3.12010000.2 2009/02/26 13:30:35 skura ship $ */
3 --
4 -- Package Variables
5 --
6 g_package varchar2(33) := ' per_person_type_usage_internal.';
7 g_debug boolean := hr_utility.debug_enabled;
8 g_old_ben_ptu_ler_rec ben_ptu_ler.g_ptu_ler_rec;
9 --
10 -- ----------------------------------------------------------------------------
11 -- |------------------------< create_person_type_usage >----------------------|
12 -- ----------------------------------------------------------------------------
13 --
14 procedure create_person_type_usage
15 ( p_validate in boolean default false
16 ,p_person_id in number
17 ,p_person_type_id in number
18 ,p_effective_date in date
19 ,p_attribute_category in varchar2 default null
20 ,p_attribute1 in varchar2 default null
21 ,p_attribute2 in varchar2 default null
22 ,p_attribute3 in varchar2 default null
23 ,p_attribute4 in varchar2 default null
24 ,p_attribute5 in varchar2 default null
25 ,p_attribute6 in varchar2 default null
26 ,p_attribute7 in varchar2 default null
27 ,p_attribute8 in varchar2 default null
28 ,p_attribute9 in varchar2 default null
29 ,p_attribute10 in varchar2 default null
30 ,p_attribute11 in varchar2 default null
31 ,p_attribute12 in varchar2 default null
32 ,p_attribute13 in varchar2 default null
33 ,p_attribute14 in varchar2 default null
34 ,p_attribute15 in varchar2 default null
35 ,p_attribute16 in varchar2 default null
36 ,p_attribute17 in varchar2 default null
37 ,p_attribute18 in varchar2 default null
38 ,p_attribute19 in varchar2 default null
39 ,p_attribute20 in varchar2 default null
40 ,p_attribute21 in varchar2 default null
41 ,p_attribute22 in varchar2 default null
42 ,p_attribute23 in varchar2 default null
43 ,p_attribute24 in varchar2 default null
44 ,p_attribute25 in varchar2 default null
45 ,p_attribute26 in varchar2 default null
46 ,p_attribute27 in varchar2 default null
47 ,p_attribute28 in varchar2 default null
48 ,p_attribute29 in varchar2 default null
49 ,p_attribute30 in varchar2 default null
50 ,p_person_type_usage_id out nocopy number
51 ,p_object_version_number out nocopy number
52 ,p_effective_start_date out nocopy date
53 ,p_effective_end_date out nocopy date
54 ) is
55 --
56 -- Declare cursors and local variables
57 --
58 l_person_type_usage_id per_person_type_usages_f.person_type_usage_id%TYPE;
59 l_effective_start_date per_person_type_usages_f.effective_start_date%TYPE;
60 l_effective_end_date per_person_type_usages_f.effective_end_date%TYPE;
61 l_proc varchar2(72);
62 l_object_version_number per_person_type_usages_f.object_version_number%TYPE;
63 --
64 -- BEGIN TCA_UNMERGE CHANGES
65 cursor c_person is
66 select *
67 from per_all_people_f
68 where person_id = p_person_id
69 and p_effective_date
70 between effective_start_date
71 and effective_end_date;
72 --
73 l_person per_all_people_f%rowtype;
74 --
75 -- TCA_UNMERGE
76 begin
77 --
78 if g_debug then
79 l_proc := g_package||'create_person_type_usage';
80 hr_utility.set_location('Entering:'|| l_proc, 10);
81 end if;
82 --
83 -- Issue a savepoint if operating in validation only mode
84 --
85 if p_validate then
86 savepoint create_person_type_usage;
87 end if;
88 --
89 if g_debug then
90 hr_utility.set_location(l_proc, 20);
91 end if;
92 --
93 -- Process Logic
94 --
95 per_ptu_ins.ins
96 (
97 p_person_type_usage_id => l_person_type_usage_id
98 ,p_person_id => p_person_id
99 ,p_person_type_id => p_person_type_id
100 ,p_effective_start_date => l_effective_start_date
101 ,p_effective_end_date => l_effective_end_date
102 ,p_object_version_number => l_object_version_number
103 ,p_attribute_category => p_attribute_category
104 ,p_attribute1 => p_attribute1
105 ,p_attribute2 => p_attribute2
106 ,p_attribute3 => p_attribute3
107 ,p_attribute4 => p_attribute4
108 ,p_attribute5 => p_attribute5
109 ,p_attribute6 => p_attribute6
110 ,p_attribute7 => p_attribute7
111 ,p_attribute8 => p_attribute8
112 ,p_attribute9 => p_attribute9
113 ,p_attribute10 => p_attribute10
114 ,p_attribute11 => p_attribute11
115 ,p_attribute12 => p_attribute12
116 ,p_attribute13 => p_attribute13
117 ,p_attribute14 => p_attribute14
118 ,p_attribute15 => p_attribute15
119 ,p_attribute16 => p_attribute16
120 ,p_attribute17 => p_attribute17
121 ,p_attribute18 => p_attribute18
122 ,p_attribute19 => p_attribute19
123 ,p_attribute20 => p_attribute20
124 ,p_attribute21 => p_attribute21
125 ,p_attribute22 => p_attribute22
126 ,p_attribute23 => p_attribute23
127 ,p_attribute24 => p_attribute24
128 ,p_attribute25 => p_attribute25
129 ,p_attribute26 => p_attribute26
130 ,p_attribute27 => p_attribute27
131 ,p_attribute28 => p_attribute28
132 ,p_attribute29 => p_attribute29
133 ,p_attribute30 => p_attribute30
134 ,p_effective_date => trunc(p_effective_date)
135 );
136 --
137 --
138 if g_debug then
139 hr_utility.set_location(l_proc, 60);
140 end if;
141 ------------------------------------------------
142 -- BEGIN TCA_UNMERGE CHANGES
143 --
144 -- Bug fix 3725055.If condition removed.
145 --if hr_general.g_data_migrator_mode <> 'P' then
146 open c_person;
147 fetch c_person into l_person;
148 close c_person;
149
150 per_hrtca_merge.create_tca_person(p_rec => l_person);
151 --end if;
152 --
153 -- END TCA_UNMERGE CHANGES
154 ------------------------------------------------
155 --
156 -- When in validation only mode raise the Validate_Enabled exception
157 --
158 if p_validate then
159 raise hr_api.validate_enabled;
160 end if;
161 --
162 -- Set all output arguments
163 --
164 p_person_type_usage_id := l_person_type_usage_id;
165 p_effective_start_date := l_effective_start_date;
166 p_effective_end_date := l_effective_end_date;
167 p_object_version_number := l_object_version_number;
168 --
169 if g_debug then
170 hr_utility.set_location(' Leaving:'||l_proc, 70);
171 end if;
172 --
173 exception
174 --
175 when hr_api.validate_enabled then
176 --
177 -- As the Validate_Enabled exception has been raised
178 -- we must rollback to the savepoint
179 --
180 ROLLBACK TO create_person_type_usage;
181 --
182 -- Only set output warning arguments
183 -- (Any key or derived arguments must be set to null
184 -- when validation only mode is being used.)
185 --
186 p_person_type_usage_id := null;
187 p_effective_start_date := null;
188 p_effective_end_date := null;
189 p_object_version_number := null;
190 if g_debug then
191 hr_utility.set_location(' Leaving:'||l_proc, 80);
192 end if;
193 --
194 end create_person_type_usage;
195 -- ----------------------------------------------------------------------------
196 -- |------------------------< delete_person_type_usage >----------------------|
197 -- ----------------------------------------------------------------------------
198 --
199 procedure delete_person_type_usage
200 ( p_validate in boolean default false
201 ,p_person_type_usage_id in number
202 ,p_effective_date in date
203 ,p_datetrack_mode in varchar2
204 ,p_object_version_number in out nocopy number
205 ,p_effective_start_date out nocopy date
206 ,p_effective_end_date out nocopy date
207 ) is
208 --
209 -- Declare cursors and local variables
210 --
211 l_proc varchar2(72);
212 l_object_version_number per_person_type_usages_f.object_version_number%TYPE;
213 l_effective_start_date per_person_type_usages_f.effective_start_date%TYPE;
214 l_effective_end_date per_person_type_usages_f.effective_end_date%TYPE;
215 --
216 begin
217 --
218 if g_debug then
219 l_proc := g_package||'delete_person_type_usage';
220 hr_utility.set_location('Entering:'|| l_proc, 10);
221 end if;
222 --
223 -- Issue a savepoint if operating in validation only mode
224 --
225 if p_validate then
226 savepoint delete_person_type_usage;
227 end if;
228 --
229 if g_debug then
230 hr_utility.set_location(l_proc, 20);
231 end if;
232 --
233 -- Process Logic
234 --
235 l_object_version_number := p_object_version_number;
236 --
237 --
238 per_ptu_del.del
239 (
240 p_person_type_usage_id => p_person_type_usage_id
241 ,p_effective_start_date => l_effective_start_date
242 ,p_effective_end_date => l_effective_end_date
243 ,p_object_version_number => l_object_version_number
244 ,p_effective_date => p_effective_date
245 ,p_datetrack_mode => p_datetrack_mode
246 );
247 --
248 --
249 if g_debug then
250 hr_utility.set_location(l_proc, 60);
251 end if;
252 --
253 -- When in validation only mode raise the Validate_Enabled exception
254 --
255 if p_validate then
256 raise hr_api.validate_enabled;
257 end if;
258 p_effective_start_date := l_effective_start_date;
259 p_effective_end_date := l_effective_end_date;
260 p_object_version_number := l_object_version_number;
261 --
262 if g_debug then
263 hr_utility.set_location(' Leaving:'||l_proc, 70);
264 end if;
265 --
266 exception
267 --
268 when hr_api.validate_enabled then
269 --
270 -- As the Validate_Enabled exception has been raised
271 -- we must rollback to the savepoint
272 --
273 ROLLBACK TO delete_person_type_usage;
274 --
275 -- Only set output warning arguments
276 -- (Any key or derived arguments must be set to null
277 -- when validation only mode is being used.)
278 --
279 p_effective_start_date := null;
280 p_effective_end_date := null;
281 --
282 end delete_person_type_usage;
283 --
284 -- ----------------------------------------------------------------------------
285 -- |------------------------< update_person_type_usage >----------------------|
286 -- ----------------------------------------------------------------------------
287 --
288 procedure update_person_type_usage
289 (
290 p_validate in boolean default false
291 ,p_effective_date in date
292 ,p_datetrack_mode in varchar2
293 ,p_person_type_usage_id in number
294 ,p_object_version_number in out nocopy number
295 ,p_person_type_id in number default hr_api.g_number
296 ,p_attribute_category in varchar2 default hr_api.g_varchar2
297 ,p_attribute1 in varchar2 default hr_api.g_varchar2
298 ,p_attribute2 in varchar2 default hr_api.g_varchar2
299 ,p_attribute3 in varchar2 default hr_api.g_varchar2
300 ,p_attribute4 in varchar2 default hr_api.g_varchar2
301 ,p_attribute5 in varchar2 default hr_api.g_varchar2
302 ,p_attribute6 in varchar2 default hr_api.g_varchar2
303 ,p_attribute7 in varchar2 default hr_api.g_varchar2
304 ,p_attribute8 in varchar2 default hr_api.g_varchar2
305 ,p_attribute9 in varchar2 default hr_api.g_varchar2
306 ,p_attribute10 in varchar2 default hr_api.g_varchar2
307 ,p_attribute11 in varchar2 default hr_api.g_varchar2
308 ,p_attribute12 in varchar2 default hr_api.g_varchar2
309 ,p_attribute13 in varchar2 default hr_api.g_varchar2
310 ,p_attribute14 in varchar2 default hr_api.g_varchar2
311 ,p_attribute15 in varchar2 default hr_api.g_varchar2
312 ,p_attribute16 in varchar2 default hr_api.g_varchar2
313 ,p_attribute17 in varchar2 default hr_api.g_varchar2
314 ,p_attribute18 in varchar2 default hr_api.g_varchar2
315 ,p_attribute19 in varchar2 default hr_api.g_varchar2
316 ,p_attribute20 in varchar2 default hr_api.g_varchar2
317 ,p_attribute21 in varchar2 default hr_api.g_varchar2
318 ,p_attribute22 in varchar2 default hr_api.g_varchar2
319 ,p_attribute23 in varchar2 default hr_api.g_varchar2
320 ,p_attribute24 in varchar2 default hr_api.g_varchar2
321 ,p_attribute25 in varchar2 default hr_api.g_varchar2
322 ,p_attribute26 in varchar2 default hr_api.g_varchar2
323 ,p_attribute27 in varchar2 default hr_api.g_varchar2
324 ,p_attribute28 in varchar2 default hr_api.g_varchar2
325 ,p_attribute29 in varchar2 default hr_api.g_varchar2
326 ,p_attribute30 in varchar2 default hr_api.g_varchar2
327 ,p_effective_start_date out nocopy date
328 ,p_effective_end_date out nocopy date
329 ) is
330 --
331 -- Declare cursors and local variables
332 --
333 l_effective_start_date per_person_type_usages_f.effective_start_date%TYPE;
334 l_effective_end_date per_person_type_usages_f.effective_end_date%TYPE;
335 l_proc varchar2(72);
336 l_object_version_number per_person_type_usages_f.object_version_number%TYPE := p_object_version_number;
337 --
338 begin
339 --
340 if g_debug then
341 l_proc := g_package||'update_person_type_usage';
342 hr_utility.set_location('Entering:'|| l_proc, 10);
343 end if;
344 --
345 -- Issue a savepoint if operating in validation only mode
346 --
347 if p_validate then
348 savepoint update_person_type_usage;
349 end if;
350 --
351 if g_debug then
352 hr_utility.set_location(l_proc, 20);
353 end if;
354 --
355 -- Process Logic
356 --
357 per_ptu_upd.upd
358 (
359 p_person_type_usage_id => p_person_type_usage_id
360 ,p_person_type_id => p_person_type_id
361 ,p_effective_start_date => l_effective_start_date
362 ,p_effective_end_date => l_effective_end_date
363 ,p_object_version_number => l_object_version_number
364 ,p_attribute_category => p_attribute_category
365 ,p_attribute1 => p_attribute1
366 ,p_attribute2 => p_attribute2
367 ,p_attribute3 => p_attribute3
368 ,p_attribute4 => p_attribute4
369 ,p_attribute5 => p_attribute5
370 ,p_attribute6 => p_attribute6
371 ,p_attribute7 => p_attribute7
372 ,p_attribute8 => p_attribute8
373 ,p_attribute9 => p_attribute9
374 ,p_attribute10 => p_attribute10
375 ,p_attribute11 => p_attribute11
376 ,p_attribute12 => p_attribute12
377 ,p_attribute13 => p_attribute13
378 ,p_attribute14 => p_attribute14
379 ,p_attribute15 => p_attribute15
380 ,p_attribute16 => p_attribute16
381 ,p_attribute17 => p_attribute17
382 ,p_attribute18 => p_attribute18
383 ,p_attribute19 => p_attribute19
384 ,p_attribute20 => p_attribute20
385 ,p_attribute21 => p_attribute21
386 ,p_attribute22 => p_attribute22
387 ,p_attribute23 => p_attribute23
388 ,p_attribute24 => p_attribute24
389 ,p_attribute25 => p_attribute25
390 ,p_attribute26 => p_attribute26
391 ,p_attribute27 => p_attribute27
392 ,p_attribute28 => p_attribute28
393 ,p_attribute29 => p_attribute29
394 ,p_attribute30 => p_attribute30
395 ,p_effective_date => trunc(p_effective_date)
396 ,p_datetrack_mode => p_datetrack_mode
397 );
398 --
399 --
400 if g_debug then
401 hr_utility.set_location(l_proc, 60);
402 end if;
403 --
404 -- When in validation only mode raise the Validate_Enabled exception
405 --
406 if p_validate then
407 raise hr_api.validate_enabled;
408 end if;
409 --
410 -- Set all output arguments
411 --
412 p_effective_start_date := l_effective_start_date;
413 p_effective_end_date := l_effective_end_date;
414 p_object_version_number := l_object_version_number;
415 --
416 if g_debug then
417 hr_utility.set_location(' Leaving:'||l_proc, 70);
418 end if;
419 --
420 exception
421 --
422 when hr_api.validate_enabled then
423 --
424 -- As the Validate_Enabled exception has been raised
425 -- we must rollback to the savepoint
426 --
427 ROLLBACK TO update_person_type_usage;
428 --
429 -- Only set output warning arguments
430 -- (Any key or derived arguments must be set to null
431 -- when validation only mode is being used.)
432 --
433 p_effective_start_date := null;
434 p_effective_end_date := null;
435 p_object_version_number := p_object_version_number;
436 if g_debug then
437 hr_utility.set_location(' Leaving:'||l_proc, 80);
438 end if;
439 --
440 end update_person_type_usage;
441 --
442 -- ----------------------------------------------------------------------------
443 -- |-------------------------< get_ben_ptu_ler_rec >--------------------------|
444 -- ----------------------------------------------------------------------------
445 function get_ben_ptu_ler_rec
446 ( p_effective_date in date
447 ,p_person_type_usage_id in number
448 )
449 return ben_ptu_ler.g_ptu_ler_rec is
450 --
451 -- Declare cursors and local variables
452 --
453 cursor csr_person_type_usages
454 ( p_effective_date in date
455 ,p_person_type_usage_id in number
456 ) is
457 select *
458 from per_person_type_usages_f ptu
459 where p_effective_date between ptu.effective_start_date and ptu.effective_end_date
460 and ptu.person_type_usage_id = p_person_type_usage_id;
461 l_person_type_usage csr_person_type_usages%rowtype;
462 --
463 l_ben_ptu_ler_rec ben_ptu_ler.g_ptu_ler_rec;
464 --
465 begin
466 --
467 open csr_person_type_usages
468 (p_effective_date => p_effective_date
469 ,p_person_type_usage_id => p_person_type_usage_id
470 );
471 fetch csr_person_type_usages into l_person_type_usage;
472 close csr_person_type_usages;
473 l_ben_ptu_ler_rec.person_id := l_person_type_usage.person_id;
474 l_ben_ptu_ler_rec.person_type_usage_id := l_person_type_usage.person_type_usage_id;
475 l_ben_ptu_ler_rec.person_type_id := l_person_type_usage.person_type_id;
476 l_ben_ptu_ler_rec.effective_start_date := l_person_type_usage.effective_start_date;
477 l_ben_ptu_ler_rec.effective_end_date := l_person_type_usage.effective_end_date;
478 --
479 return l_ben_ptu_ler_rec;
480 --
481 end get_ben_ptu_ler_rec;
482 --
483 -- ----------------------------------------------------------------------------
484 -- |---------------------< benefits_person_type_usage_b >---------------------|
485 -- ----------------------------------------------------------------------------
486 --
487 procedure benefits_person_type_usage_b
488 ( p_effective_date in date
489 ,p_person_type_usage_id in number
490 ) is
491 --
492 begin
493 --
494 g_old_ben_ptu_ler_rec := get_ben_ptu_ler_rec
495 (p_effective_date => p_effective_date
496 ,p_person_type_usage_id => p_person_type_usage_id
497 );
498 --
499 end benefits_person_type_usage_b;
500 --
501 -- ----------------------------------------------------------------------------
502 -- |---------------------< benefits_person_type_usage_a >---------------------|
503 -- ----------------------------------------------------------------------------
504 --
505 procedure benefits_person_type_usage_a
506 ( p_effective_date in date
507 ,p_person_type_usage_id in number
508 ) is
509 --
510 -- Declare cursors and local variables
511 --
512 l_old_ben_ptu_ler_rec ben_ptu_ler.g_ptu_ler_rec;
513 l_new_ben_ptu_ler_rec ben_ptu_ler.g_ptu_ler_rec;
514 --
515 begin
516 --
517 l_old_ben_ptu_ler_rec := g_old_ben_ptu_ler_rec;
518 l_new_ben_ptu_ler_rec := get_ben_ptu_ler_rec
519 (p_effective_date => p_effective_date
520 ,p_person_type_usage_id => p_person_type_usage_id
521 );
522 --
523 ben_ptu_ler.ler_chk
524 (p_old => l_old_ben_ptu_ler_rec
525 ,p_new => l_new_ben_ptu_ler_rec
526 ,p_effective_date => p_effective_date
527 );
528 --
529 end benefits_person_type_usage_a;
530 --
531 -- ----------------------------------------------------------------------------
532 -- |----------------------< maintain_person_type_usage >----------------------|
533 -- ----------------------------------------------------------------------------
534 --
535 procedure maintain_person_type_usage
536 (
537 p_effective_date in date
538 ,p_person_id in number
539 ,p_person_type_id in number
540 ,p_datetrack_update_mode in varchar2 default hr_api.g_update
541 ,p_datetrack_delete_mode in varchar2 default null
542 ) is
543 --
544 -- Declare cursors and local variables
545 --
546 TYPE spt_list IS TABLE OF per_person_types.system_person_type%type
547 INDEX BY binary_integer;
548 system_type spt_list;
549 --
550 -- Returns person type usages records for the specified person and person type
551 -- effective on the specified date.
552 --
553 cursor csr_delete_person_type_usages
554 (
555 p_effective_date in date
556 ,p_person_id in number
557 ,p_person_type_id in number
558 ) is
559 select ptu.person_type_usage_id
560 ,ptu.object_version_number
561 from per_person_type_usages_f ptu
562 where p_effective_date between ptu.effective_start_date and ptu.effective_end_date
563 and ptu.person_id = p_person_id
564 and ptu.person_type_id = p_person_type_id;
565 l_delete_person_type_usage csr_delete_person_type_usages%rowtype;
566 --
567 -- Returns person types records for the specified person type
568 --
569 cursor csr_person_types
570 (
571 p_person_type_id in number
572 ) is
573 select ppt.person_type_id
574 ,ppt.system_person_type
575 from per_person_types ppt
576 where ppt.person_type_id = p_person_type_id;
577 l_person_type csr_person_types%rowtype;
578 --
579 -- Returns person type usages records for the specified person and system
580 -- person type effective on the specified date. EMP and EX_EMP; and APL and
581 -- EX_APL are considered to be the same type, and are stored in the same
582 -- datetracked record.
583 --
584 cursor csr_update_person_type_usages
585 (
586 p_effective_date in date
587 ,p_person_id in number
588 ,p_system_person_type in varchar2
589 ) is
590 select ptu.person_type_usage_id
591 ,ptu.object_version_number
592 from per_person_type_usages_f ptu
593 where p_effective_date between ptu.effective_start_date and ptu.effective_end_date
594 and ptu.person_id = p_person_id
595 and ptu.person_type_id in
596 (select ppt.person_type_id
597 from per_person_types ppt
598 where ( ( p_system_person_type in ('EMP','EX_EMP')
599 and ppt.system_person_type in ('EMP','EX_EMP') )
600 or ( p_system_person_type in ('APL','EX_APL')
601 and ppt.system_person_type in ('APL','EX_APL') )
602 or ( p_system_person_type in ('CWK','EX_CWK')
603 and ppt.system_person_type in ('CWK','EX_CWK') )
604 or ( p_system_person_type = 'OTHER'
605 and ppt.system_person_type = 'OTHER' )));
606
607 l_update_person_type_usage csr_update_person_type_usages%rowtype;
608 l_update_person_type_usage1 csr_update_person_type_usages%rowtype;
609 --
610 l_effective_start_date per_person_type_usages_f.effective_start_date%TYPE;
611 l_effective_end_date per_person_type_usages_f.effective_end_date%TYPE;
612 l_proc varchar2(72) := g_package||'maintain_person_type_usage';
613 l_person_type_usage_id per_person_type_usages_f.person_type_usage_id%TYPE;
614 l_object_version_number per_person_type_usages_f.object_version_number%TYPE;
615 l_no_other varchar2(1) := 'Y';
616 --
617 -- BEGIN TCA_UNMERGE CHANGES
618 cursor c_person is
619 select *
620 from per_all_people_f
621 where person_id = p_person_id
622 and p_effective_date
623 between effective_start_date
624 and effective_end_date;
625 --
626 l_person per_all_people_f%rowtype;
627 --
628 -- TCA_UNMERGE
629 begin
630 --
631 system_type(1):='EMP';
632 system_type(2):='APL';
633 system_type(3):='CWK';
634 --
635 if g_debug then
636 hr_utility.set_location('Entering:'|| l_proc, 10);
637 hr_utility.set_location('p_effective_date = '||to_char(p_effective_date,'DD-MON-YYYY'),11);
638 hr_utility.set_location('p_person_id = '||p_person_id,12);
639 hr_utility.set_location('p_person_type_id = '||p_person_type_id,13);
640 hr_utility.set_location('p_datetrack_update_mode = '||p_datetrack_update_mode,14);
641 hr_utility.set_location('p_datetrack_delete_mode = '||p_datetrack_delete_mode,15);
642 end if;
643 --
644 -- For deletes
645 --
646 if (p_datetrack_delete_mode is not null) then
647 --
648 if g_debug then
649 hr_utility.set_location(l_proc, 10);
650 end if;
651 --
652 -- Find matching person type usage record, and delete
653 --
654 open csr_delete_person_type_usages
655 (p_effective_date => p_effective_date
656 ,p_person_id => p_person_id
657 ,p_person_type_id => p_person_type_id
658 );
659 fetch csr_delete_person_type_usages into l_delete_person_type_usage;
660 if (csr_delete_person_type_usages%notfound) then
661 --
662 if g_debug then
663 hr_utility.set_location(l_proc, 20);
664 end if;
665 --
666 close csr_delete_person_type_usages;
667 fnd_message.set_name('PAY','HR_6153_ALL_PROCEDURE_FAIL');
668 fnd_message.set_token('PROCEDURE',l_proc);
669 fnd_message.set_token('STEP',10);
670 fnd_message.raise_error;
671 else
672 --
673 if g_debug then
674 hr_utility.set_location(l_proc, 30);
675 end if;
676 --
677 close csr_delete_person_type_usages;
678 --
679 benefits_person_type_usage_b
680 (p_effective_date => p_effective_date
681 ,p_person_type_usage_id => l_delete_person_type_usage.person_type_usage_id
682 );
683 --
684 delete_person_type_usage
685 (p_person_type_usage_id => l_delete_person_type_usage.person_type_usage_id
686 ,p_effective_date => p_effective_date
687 ,p_datetrack_mode => p_datetrack_delete_mode
688 ,p_object_version_number => l_delete_person_type_usage.object_version_number
689 ,p_effective_start_date => l_effective_start_date
690 ,p_effective_end_date => l_effective_end_date
691 );
692 --
693 benefits_person_type_usage_a
694 (p_effective_date => p_effective_date
695 ,p_person_type_usage_id => l_delete_person_type_usage.person_type_usage_id
696 );
697 --
698 end if;
699 --
700 -- For updates
701 --
702 elsif (p_datetrack_update_mode is not null) then
703 --
704 if g_debug then
705 hr_utility.set_location(l_proc, 40);
706 end if;
707 --
708 -- Determine system person type of person type parameter
709 --
710 open csr_person_types
711 (p_person_type_id => p_person_type_id
712 );
713 fetch csr_person_types into l_person_type;
714 if (csr_person_types%notfound) then
715 --
716 if g_debug then
717 hr_utility.set_location(l_proc, 50);
718 end if;
719 --
720 close csr_person_types;
721 fnd_message.set_name('PAY','HR_6153_ALL_PROCEDURE_FAIL');
722 fnd_message.set_token('PROCEDURE',l_proc);
723 fnd_message.set_token('STEP',20);
724 fnd_message.raise_error;
725 else
726 --
727 if g_debug then
728 hr_utility.set_location(l_proc, 60);
729 end if;
730 --
731 close csr_person_types;
732 --
733 -- Find existing record corresponding to system person type
734 --
735 open csr_update_person_type_usages
736 (p_effective_date => p_effective_date
737 ,p_person_id => p_person_id
738 ,p_system_person_type => l_person_type.system_person_type
739 );
740 fetch csr_update_person_type_usages into l_update_person_type_usage;
741 if (csr_update_person_type_usages%found) then
742
743 -- Added close
744 close csr_update_person_type_usages;
745 --
746 if g_debug then
747 hr_utility.set_location(l_proc, 70);
748 end if;
749 --
750 -- There is an existing record, so update with new person type
751 --
752 benefits_person_type_usage_b
753 (p_effective_date => p_effective_date
754 ,p_person_type_usage_id => l_update_person_type_usage.person_type_usage_id
755 );
756 --
757 update_person_type_usage
758 (p_effective_date => p_effective_date
759 ,p_datetrack_mode => p_datetrack_update_mode
760 ,p_person_type_usage_id => l_update_person_type_usage.person_type_usage_id
761 ,p_object_version_number => l_update_person_type_usage.object_version_number
762 ,p_person_type_id => p_person_type_id
763 ,p_effective_start_date => l_effective_start_date
764 ,p_effective_end_date => l_effective_end_date
765 );
766 --
767 benefits_person_type_usage_a
768 (p_effective_date => p_effective_date
769 ,p_person_type_usage_id => l_update_person_type_usage.person_type_usage_id
770 );
771 --
772 else
773 --
774 if g_debug then
775 hr_utility.set_location(l_proc, 80);
776 end if;
777 -- Added close;
778 close csr_update_person_type_usages;
779 --
780 -- There is not an existing record, so create a new one
781 --
782 --
783 if g_debug then
784 hr_utility.set_location(l_proc, 81);
785 end if;
786
787 -- But before creating a new PTU record do the following
788 -- If SPT is EMP,APL,CWK, then delete any existing OTHER PTU record
789 -- If OTHER then only create if they are not already an EMP or APL or CWK
790
791 IF l_person_type.system_person_type in ( 'EMP','APL','CWK' ) THEN
792
793 if g_debug then
794 hr_utility.set_location(l_proc, 82);
795 end if;
796 --
797 -- Find existing record corresponding to system person type
798 --
799 open csr_update_person_type_usages
800 (p_effective_date => p_effective_date
801 ,p_person_id => p_person_id
802 ,p_system_person_type => 'OTHER'
803 );
804 fetch csr_update_person_type_usages into l_update_person_type_usage1;
805 if (csr_update_person_type_usages%found) then
806
807 -- Added close
808 close csr_update_person_type_usages;
809 --
810 if g_debug then
811 hr_utility.set_location(l_proc, 83);
812 end if;
813 --
814 benefits_person_type_usage_b
815 (p_effective_date => p_effective_date - 1
816 ,p_person_type_usage_id => l_update_person_type_usage1.person_type_usage_id
817 );
818 --
819
820 if g_debug then
821 hr_utility.set_location(l_proc, 84);
822 end if;
823
824 delete_person_type_usage
825 (p_person_type_usage_id => l_update_person_type_usage1.person_type_usage_id
826 ,p_effective_date => p_effective_date - 1
827 ,p_datetrack_mode => 'DELETE'
828 ,p_object_version_number => l_update_person_type_usage1.object_version_number
829 ,p_effective_start_date => l_effective_start_date
830 ,p_effective_end_date => l_effective_end_date
831 );
832 --
833 benefits_person_type_usage_a
834 (p_effective_date => p_effective_date - 1
835 ,p_person_type_usage_id => l_update_person_type_usage1.person_type_usage_id
836 );
837 --
838 if g_debug then
839 hr_utility.set_location(l_proc, 85);
840 end if;
841
842 end if;
843 ELSIF l_person_type.system_person_type = 'OTHER' THEN
844 --
845 if g_debug then
846 hr_utility.set_location(l_proc, 86);
847 end if;
848 --
849 /*
850 ** New code since CWK
851 */
852 for i in system_type.first..system_type.last loop
853 --
854 -- Find existing record corresponding to system person type
855 --
856 open csr_update_person_type_usages
857 (p_effective_date => p_effective_date
858 ,p_person_id => p_person_id
859 ,p_system_person_type => system_type(i)
860 );
861 fetch csr_update_person_type_usages into l_update_person_type_usage1;
862 if (csr_update_person_type_usages%found) then
863 l_no_other := 'N';
864 else
865 l_no_other := 'Y';
866 end if;
867 close csr_update_person_type_usages;
868 if l_no_other='N' then
869 exit;
870 end if;
871 end loop;
872
873 END IF;
874
875 IF l_no_other <> 'N' THEN
876
877 benefits_person_type_usage_b
878 (p_effective_date => p_effective_date
879 ,p_person_type_usage_id => l_person_type_usage_id
880 );
881 --
882 if g_debug then
883 hr_utility.set_location(l_proc, 93);
884 end if;
885 create_person_type_usage
886 (p_person_id => p_person_id
887 ,p_person_type_id => p_person_type_id
888 ,p_effective_date => p_effective_date
889 ,p_person_type_usage_id => l_person_type_usage_id
890 ,p_object_version_number => l_object_version_number
891 ,p_effective_start_date => l_effective_start_date
892 ,p_effective_end_date => l_effective_end_date
893 );
894 --
895 if g_debug then
896 hr_utility.set_location(l_proc, 94);
897 end if;
898 benefits_person_type_usage_a
899 (p_effective_date => p_effective_date
900 ,p_person_type_usage_id => l_person_type_usage_id
901 );
902 if g_debug then
903 hr_utility.set_location(l_proc, 95);
904 end if;
905 --
906 END IF;
907 if g_debug then
908 hr_utility.set_location(l_proc, 96);
909 end if;
910 end if;
911 --
912 if g_debug then
913 hr_utility.set_location(l_proc, 97);
914 end if;
915 end if;
916 --
917 if g_debug then
918 hr_utility.set_location(l_proc, 98);
919 end if;
920 end if;
921 --
922 ------------------------------------------------
923 -- BEGIN TCA_UNMERGE CHANGES
924 --
925 -- Bug fix 3725055. If condition removed.
926 --if hr_general.g_data_migrator_mode <> 'P' then
927 open c_person;
928 fetch c_person into l_person;
929 close c_person;
930
931 per_hrtca_merge.create_tca_person(p_rec => l_person);
932 --end if;
933 --
934 -- END TCA_UNMERGE CHANGES
935 ------------------------------------------------
936 if g_debug then
937 hr_utility.set_location(' Leaving:'||l_proc, 1000);
938 end if;
939 --
940 end maintain_person_type_usage;
941 --
942 -- ----------------------------------------------------------------------------
943 -- |-----------------------< cancel_person_type_usage >-----------------------|
944 -- ----------------------------------------------------------------------------
945 --
946 procedure cancel_person_type_usage
947 (
948 p_effective_date in date
949 ,p_person_id in number
950 ,p_system_person_type in varchar2
951 ) is
952 --
953 -- Declare cursors and local variables
954 --
955 c_backwards constant varchar2(30) := 'BACKWARDS';
956 c_forwards constant varchar2(30) := 'FORWARDS';
957 c_person_type_usage_id number;
958 --
959 cursor csr_person_type_usages
960 (
961 p_effective_date in date
962 ,p_person_id in number
963 ,p_search_type in varchar2
964 ) is
965 select ptu.person_type_usage_id
966 ,ptu.object_version_number
967 ,ppt.system_person_type
968 ,ptu.effective_start_date
969 ,ptu.effective_end_date
970 from per_person_types ppt
971 ,per_person_type_usages_f ptu
972 where ppt.person_type_id = ptu.person_type_id
973 and ptu.person_type_usage_id = c_person_type_usage_id
974 and ptu.person_id = p_person_id
975 and ( ( p_search_type = c_backwards
976 and ptu.effective_start_date <= p_effective_date)
977 or ( p_search_type = c_forwards
978 and ptu.effective_end_date >= p_effective_date) )
979 order by decode(p_search_type
980 ,c_backwards,(p_effective_date - ptu.effective_start_date)
981 ,c_forwards,(ptu.effective_end_date - p_effective_date) )
982 for update of ptu.person_type_usage_id;
983 --
984 cursor csr_ptu_rec_extra
985 (p_person_type_usage_id NUMBER
986 ,p_person_id NUMBER
987 ,p_effective_start_date DATE)
988 is
989 --cursor update for bug 5706213
990 select ptu.person_type_usage_id,ptu.effective_start_date,ptu.effective_end_date,object_version_number
991 from per_person_type_usages_f ptu ,per_person_types ppt
992 where ptu.person_type_usage_id <> p_person_type_usage_id
993 and ptu.person_id = p_person_id
994 and ppt.PERSON_TYPE_ID = ptu.PERSON_TYPE_ID
995 and ppt.system_person_type = 'APL'
996 and ptu.effective_end_date = p_effective_start_date -1;
997 --end changes for bug 5706213
998 --
999 cursor csr_pds_start is
1000 select max(date_start)
1001 from per_periods_of_service
1002 where person_id=p_person_id
1003 and date_start <= p_effective_date;
1004 --
1005 cursor csr_pdp_start is
1006 select max(date_start)
1007 from per_periods_of_service
1008 where person_id=p_person_id
1009 and date_start <= p_effective_date;
1010 --
1011 l_date_start date;
1012 --
1013 l_csr_person_type_usages csr_person_type_usages%ROWTYPE := NULL;
1014 l_person_type_usage_id per_person_type_usages_f.person_type_usage_id%TYPE;
1015 l_effective_start_date per_person_type_usages_f.effective_start_date%TYPE;
1016 l_effective_end_date per_person_type_usages_f.effective_end_date%TYPE;
1017 l_object_version_number per_person_type_usages_f.object_version_number%TYPE;
1018 l_system_person_type per_person_types.system_person_type%TYPE;
1019 l_proc varchar2(72) := g_package||'cancel_person_type_usage';
1020 l_effective_start_date1 per_person_type_usages_f.effective_start_date%TYPE;
1021 l_effective_end_date1 per_person_type_usages_f.effective_end_date%TYPE;
1022 l_object_version_number1 per_person_type_usages_f.object_version_number%TYPE;
1023 --
1024 -- BEGIN TCA_UNMERGE CHANGES
1025 cursor c_person is
1026 select *
1027 from per_all_people_f
1028 where person_id = p_person_id
1029 and p_effective_date
1030 between effective_start_date
1031 and effective_end_date;
1032 --
1033 l_person per_all_people_f%rowtype;
1034 --
1035 -- TCA_UNMERGE
1036 --
1037 begin
1038 --
1039 if g_debug then
1040 hr_utility.set_location('Entering:'|| l_proc, 10);
1041 hr_utility.set_location('cancel_person_type_usage.p_effective_date = '||to_char(p_effective_date,'DD-MON-YYYY'),11);
1042 hr_utility.set_location('cancel_person_type_usage.p_person_id = '||to_char(p_person_id),11);
1043 hr_utility.set_location('cancel_person_type_usage.p_system_person_type = '||p_system_person_type,11);
1044 end if;
1045 --
1046 begin
1047 select ptu.person_type_usage_id into c_person_type_usage_id
1048 from per_person_types ppt
1049 ,per_person_type_usages_f ptu
1050 where ppt.person_type_id = ptu.person_type_id
1051 and ppt.system_person_type = p_system_person_type
1052 and p_effective_date between ptu.effective_start_date and ptu.effective_end_date
1053 and ptu.person_id = p_person_id;
1054 if g_debug then
1055 hr_utility.set_location('cancel_person_type_usage.c_person_type_usage_id = '||to_char(c_person_type_usage_id),12);
1056 end if;
1057 exception
1058 when no_data_found then
1059 fnd_message.set_name('PAY','HR_6153_ALL_PROCEDURE_FAIL');
1060 fnd_message.set_token('PROCEDURE',l_proc);
1061 fnd_message.set_token('STEP',13);
1062 fnd_message.raise_error;
1063 end;
1064 --
1065 --added following 'if' clause for 2449091
1066 --
1067 if p_system_person_type='EMP' then
1068 open csr_pds_start;
1069 fetch csr_pds_start into l_date_start;
1070 close csr_pds_start;
1071 elsif p_system_person_type='CWK' then
1072 open csr_pdp_start;
1073 fetch csr_pdp_start into l_date_start;
1074 close csr_pdp_start;
1075 else
1076 l_date_start := p_effective_date;
1077 end if;
1078 --
1079 -- Search backwards through the person type usage records for the start of
1080 -- this system person type
1081 --
1082 if g_debug then
1083 hr_utility.set_location('cancel_person_type_usage.c_backwards = '||c_backwards,14);
1084 end if;
1085
1086 for currec in csr_person_type_usages
1087 (p_effective_date => p_effective_date
1088 ,p_person_id => p_person_id
1089 ,p_search_type => c_backwards) loop
1090
1091 if g_debug then
1092 hr_utility.set_location('csr_person_type_usages',15);
1093 hr_utility.set_location('person_type_usage_id = '||currec.person_type_usage_id,15);
1094 hr_utility.set_location('object_version_number = '||to_char(currec.object_version_number),15);
1095 hr_utility.set_location('system_person_type = '||currec.system_person_type,15);
1096 hr_utility.set_location('effective_start_date = '||to_char(currec.effective_start_date),15);
1097 hr_utility.set_location('effective_end_date = '||to_char(currec.effective_end_date),15);
1098 end if;
1099 --
1100 --bug 2449091: back2back contracts dont have a change in SPT, so add extra check to stop at
1101 --change of period of service or placement (but only for backwards search)
1102 --
1103 EXIT WHEN (currec.system_person_type <> p_system_person_type
1104 OR (p_system_person_type in ('EMP','CWK')
1105 AND currec.effective_start_date = l_date_start));
1106 --
1107 if g_debug then
1108 hr_utility.set_location(l_proc, 15);
1109 end if;
1110 l_person_type_usage_id := currec.person_type_usage_id;
1111 l_object_version_number := currec.object_version_number;
1112 l_system_person_type := currec.system_person_type;
1113 l_effective_start_date := currec.effective_start_date;
1114 l_effective_end_date := currec.effective_end_date;
1115
1116 end loop;
1117 --
1118 if g_debug then
1119 hr_utility.set_location(l_proc, 20);
1120 end if;
1121 --
1122 -- Search forwards through the person type usage records for the end of this
1123 -- system person type
1124 --
1125 for currec in csr_person_type_usages
1126 (p_effective_date => p_effective_date
1127 ,p_person_id => p_person_id
1128 ,p_search_type => c_forwards) loop
1129
1130 if g_debug then
1131 hr_utility.set_location('csr_person_type_usages',16);
1132 hr_utility.set_location('person_type_usage_id = '||to_char(currec.person_type_usage_id),16);
1133 hr_utility.set_location('object_version_number = '||to_char(currec.object_version_number),16);
1134 hr_utility.set_location('system_person_type = '||currec.system_person_type,16);
1135 hr_utility.set_location('effective_start_date = '||to_char(currec.effective_start_date),16);
1136 hr_utility.set_location('effective_end_date = '||to_char(currec.effective_end_date),16);
1137 end if;
1138
1139 EXIT WHEN currec.system_person_type <> p_system_person_type;
1140
1141 if g_debug then
1142 hr_utility.set_location(l_proc, 25);
1143 end if;
1144 l_person_type_usage_id := currec.person_type_usage_id;
1145 l_object_version_number := currec.object_version_number;
1146 l_system_person_type := currec.system_person_type;
1147 l_effective_end_date := currec.effective_end_date;
1148
1149 end loop;
1150 --
1151 if g_debug then
1152 hr_utility.set_location(l_proc, 30);
1153 hr_utility.set_location(l_proc||':'||to_char(l_effective_start_date,'DD-MON-YYYY'), 99);
1154 hr_utility.set_location(l_proc||':'||to_char(l_effective_end_date,'DD-MON-YYYY'), 99);
1155 hr_utility.set_location(l_proc||':'||to_char(l_person_type_usage_id),99);
1156 end if;
1157 -- Ensure the person type usage identifier and effective dates have all been set
1158 --
1159 if ((l_person_type_usage_id is not null)
1160 and (l_effective_start_date is not null) and (l_effective_end_date is not null)) then
1161 --
1162 if g_debug then
1163 hr_utility.set_location(l_proc, 40);
1164 end if;
1165 --
1166 benefits_person_type_usage_b
1167 (p_effective_date => p_effective_date
1168 ,p_person_type_usage_id => l_person_type_usage_id
1169 );
1170 --
1171 -- Remove records for the entire time that this system person type was in
1172 -- effect. Done through direct SQL as row handler does not allow this kind
1173 -- of manipulation.
1174 --
1175 delete
1176 from per_person_type_usages_f ptu
1177 where ptu.effective_start_date >= l_effective_start_date
1178 and ptu.effective_end_date <= l_effective_end_date
1179 and ptu.person_type_usage_id = l_person_type_usage_id;
1180 --
1181 --
1182 -- Extend any previous record to cover the time that the cancelled system
1183 -- person type existed. Done through direct SQL as row handler does not
1184 -- allow this kind of manipulation.
1185 --
1186 update per_person_type_usages_f ptu
1187 set effective_end_date = l_effective_end_date
1188 where ptu.effective_end_date = (l_effective_start_date - 1)
1189 and ptu.person_type_usage_id = l_person_type_usage_id;
1190
1191 --The csr_ptu_rec_extra looks for other person types that should remain in the system.
1192 --The records are fetched of other person types only when the Emp and Apl
1193 -- records are terminated on the same day. The loop is not executed otherwise.
1194 --On discussing internally, found that there was no need to look for other
1195 --person types of the record. Hence, we can eliminate the loop logic.
1196 --Bug fix 4704941
1197
1198 --fix for the bug 5706213
1199 --The csr_ptu_rec_extra is modified, it executes only in the case of Applicant.
1200 for csr_ptu_rec in csr_ptu_rec_extra
1201 (l_person_type_usage_id
1202 ,p_person_id
1203 ,l_effective_start_date )
1204 loop
1205
1206 l_object_version_number1 := csr_ptu_rec.object_version_number;
1207
1208 if g_debug then
1209 hr_utility.set_location('csr_ptu_rec',16);
1210 hr_utility.set_location('person_type_usage_id = '||to_char(csr_ptu_rec.person_type_usage_id),16);
1211 hr_utility.set_location('effective_end_date = '||to_char(csr_ptu_rec.effective_end_date,'DD-MON-YYYY'),16);
1212 end if;
1213 --
1214 hr_per_type_usage_internal.delete_person_type_usage
1215 (p_person_type_usage_id => csr_ptu_rec.person_type_usage_id
1216 ,p_effective_date => csr_ptu_rec.effective_end_date
1217 ,p_datetrack_mode => hr_api.g_future_change
1218 ,p_object_version_number => l_object_version_number1
1219 ,p_effective_start_date => l_effective_start_date1
1220 ,p_effective_end_date => l_effective_end_date1
1221 );
1222
1223 if g_debug then
1224 hr_utility.set_location('l_object_version_number1 = '||to_char(l_object_version_number1),16);
1225 hr_utility.set_location('l_effective_start_date1 = '||to_char(l_effective_start_date1,'DD-MON-YYYY'),16);
1226 hr_utility.set_location('l_effective_end_date1 = '||to_char(l_effective_end_date1,'DD-MON-YYYY'),16);
1227 end if;
1228
1229 end loop;
1230
1231 --end changes for bug 5706213
1232
1233 ------------------------------------------------
1234 -- BEGIN TCA_UNMERGE CHANGES
1235 --
1236 -- Bug fix 3725055. IF condition removed.
1237 --if hr_general.g_data_migrator_mode <> 'P' then
1238 open c_person;
1239 fetch c_person into l_person;
1240 close c_person;
1241
1242 per_hrtca_merge.create_tca_person(p_rec => l_person);
1243 --end if;
1244 --
1245 -- END TCA_UNMERGE CHANGES
1246 ------------------------------------------------
1247 benefits_person_type_usage_a
1248 (p_effective_date => p_effective_date
1249 ,p_person_type_usage_id => l_person_type_usage_id
1250 );
1251 --
1252 end if;
1253 --
1254 if g_debug then
1255 hr_utility.set_location(' Leaving:'||l_proc, 1000);
1256 end if;
1257 --
1258 end cancel_person_type_usage;
1259 --
1260 -- ----------------------------------------------------------------------------
1261 -- |-----------------------< change_hire_date_ptu >-----------------------|
1262 -- ----------------------------------------------------------------------------
1263 --
1264 procedure change_hire_date_ptu
1265 (
1266 p_date_start in date
1267 ,p_old_date_start in date
1268 ,p_person_id in number
1269 ,p_system_person_type in varchar2
1270 ) is
1271 --
1272 -- Declare cursors and local variables
1273 --
1274 l_person_type_usages_id number;
1275 l_object_version_number number;
1276 l_ptu_effective_start_date date;
1277 l_ptu_effective_end_date date;
1278 l_proc varchar2(30);
1279 --
1280 l_chk_assign varchar2(1):='N';
1281 --
1282 cursor get_ptu(c_system_person_type varchar2,
1283 c_date date,
1284 c_person_id number) is
1285 select ptu.person_type_usage_id,
1286 ptu.object_version_number,
1287 effective_start_date,
1288 effective_end_date
1289 from per_person_type_usages_f ptu,
1290 per_person_types pt
1291 where ptu.person_id = c_person_id
1292 and (c_date between ptu.effective_start_date
1293 and ptu.effective_end_date
1294 or c_date+1 between ptu.effective_start_date
1295 and ptu.effective_end_date
1296 and c_system_person_type = 'RETIREE'
1297 )
1298 and ptu.person_type_id = pt.person_type_id
1299 and pt.system_person_type = c_system_person_type;
1300 --
1301 cursor c1 is
1302 select *
1303 from per_person_type_usages_f
1304 where person_type_usage_id = l_person_type_usages_id
1305 and object_version_number = l_object_version_number;
1306 --
1307 cursor csr_ptu_prev_row is
1308 select *
1309 from per_person_type_usages_f
1310 where person_type_usage_id = l_person_type_usages_id
1311 and effective_end_date = l_ptu_effective_start_date-1;
1312 --
1313 cursor csr_ptu_exapl_row is
1314 select ptu.person_type_usage_id
1315 ,ptu.object_version_number
1316 ,ptu.effective_start_date
1317 ,ptu.effective_end_date
1318 from per_person_type_usages_f ptu
1319 ,per_person_types ppt
1320 where ptu.effective_start_date = l_ptu_effective_start_date
1321 and ptu.person_id = p_person_id
1322 and ptu.person_type_id = ppt.person_type_id
1323 and ppt.system_person_type = 'EX_APL';
1324 --
1325 cursor csr_ptu_apl_row is
1326 select ptu.person_type_usage_id
1327 ,ptu.object_version_number
1328 ,ptu.effective_start_date
1329 ,ptu.effective_end_date
1330 from per_person_type_usages_f ptu
1331 ,per_person_types ppt
1332 where ptu.effective_end_date = l_ptu_effective_start_date-1
1333 and ptu.person_id = p_person_id
1334 and ptu.person_type_id = ppt.person_type_id
1335 and ppt.system_person_type = 'APL';
1336 --
1337 cursor csr_prev_other_row is
1338 select ptu.person_type_usage_id
1339 ,ptu.object_version_number
1340 ,ptu.effective_start_date
1341 ,ptu.effective_end_date
1342 from per_person_type_usages_f ptu
1343 ,per_person_types ppt
1344 where ptu.effective_end_date = l_ptu_effective_start_date-1
1345 and ptu.person_id = p_person_id
1346 and ptu.person_type_id = ppt.person_type_id
1347 and ppt.system_person_type = 'OTHER';
1348
1349 --cursor to check if change in hire date should update
1350 --the EX_*** records
1351 cursor csr_chk_assgn_id is
1352 select 'Y'
1353 from dual
1354 where exists (select p1.assignment_id
1355 from per_all_assignments_f p1,
1356 per_all_assignments_f p2
1357 where p1.assignment_type='A'
1358 and p2.assignment_type='E'
1359 and p1.assignment_id=p2.assignment_id
1360 and p1.person_id=p_person_id);
1361 --
1362 l_prev_other_row csr_prev_other_row%rowtype;
1363 l_ptu_prev_row csr_ptu_prev_row%rowtype;
1364 l_old ben_ptu_ler.g_ptu_ler_rec;
1365 l_new ben_ptu_ler.g_ptu_ler_rec;
1366 --
1367 l_c1 c1%rowtype;
1368 l_rows_found boolean := false;
1369 --
1370 begin
1371 if g_debug then
1372 l_proc := 'change_hire_date_ptu';
1373 hr_utility.set_location(' Entering:'||l_proc, 10);
1374 end if;
1375 /*
1376 ** Get the person type usage record valid on the old date_start
1377 ** with correct type -
1378 */
1379 open get_ptu(c_system_person_type => p_system_person_type,
1380 c_date => p_old_date_start,
1381 c_person_id => p_person_id);
1382 fetch get_ptu into l_person_type_usages_id, l_object_version_number,
1383 l_ptu_effective_start_date, l_ptu_effective_end_date;
1384 if get_ptu%FOUND then
1385 /*
1386 ** Update the PTU record. This will require a direct update since the
1387 ** API does not allow for updates to effective_start_date.
1388 **
1389 ** NB. Need also to move the end date of any previous EX record
1390 ** or OTHER record if one exists as of the day before p_old_date_start
1391 ** but raise error if this comes before the effective_start_date on the same row
1392 */
1393 --
1394 open c1;
1395 --
1396 fetch c1 into l_c1;
1397 if c1%found then
1398 --
1399 l_rows_found := true;
1400 --
1401 end if;
1402 --
1403 close c1;
1404 --
1405 -- Bug 3905654 Start Here
1406 -- Desc: Modified the UPDATE statement to include the ESD and EED in the where clause
1407 -- So that its dependency on the OVN is overwritten.
1408 update PER_PERSON_TYPE_USAGES_F
1409 set effective_start_date = p_date_start,
1410 object_version_number = object_version_number+1
1411 where person_type_usage_id = l_person_type_usages_id
1412 and object_version_number = l_object_version_number
1413 and effective_start_date = l_ptu_effective_start_date
1414 and effective_end_date = l_ptu_effective_end_date;
1415 -- Bug 3905654 Ends Here
1416 --
1417 -- fix2299851: update the end date of the previous records if they exist
1418 -- These will be the EX_xxx record and the OTHER record.
1419 --
1420 open csr_ptu_prev_row;
1421 fetch csr_ptu_prev_row into l_ptu_prev_row;
1422 if csr_ptu_prev_row%found then
1423 if l_ptu_prev_row.effective_start_date > p_date_start-1 then
1424 fnd_message.set_name('PER','HR_289742_NO_CHG_DATE_PTU');
1425 fnd_message.raise_error;
1426 else
1427 update PER_PERSON_TYPE_USAGES_F
1428 set effective_end_date = p_date_start-1,
1429 object_version_number = object_version_number+1
1430 where person_type_usage_id = l_person_type_usages_id
1431 and object_version_number = l_ptu_prev_row.object_version_number;
1432 end if;
1433 end if;
1434 --
1435 if g_debug then
1436 hr_utility.set_location(l_proc, 20);
1437 end if;
1438 --
1439 open csr_prev_other_row;
1440 fetch csr_prev_other_row into l_prev_other_row;
1441 if csr_prev_other_row%found then
1442 if l_prev_other_row.effective_start_date > p_date_start-1 then
1443 fnd_message.set_name('PER','HR_289742_NO_CHG_DATE_PTU');
1444 fnd_message.raise_error;
1445 else
1446 update PER_PERSON_TYPE_USAGES_F
1447 set effective_end_date = p_date_start-1,
1448 object_version_number = object_version_number+1
1449 where person_type_usage_id = l_prev_other_row.person_type_usage_id
1450 and object_version_number = l_prev_other_row.object_version_number;
1451 end if;
1452 end if;
1453 --
1454 open csr_chk_assgn_id;
1455 fetch csr_chk_assgn_id into l_chk_assign;
1456 if l_chk_assign='Y' then
1457 --checking cursor
1458 --move the EX_APL and APL records only if assignment_id are same
1459 --else do no change
1460 open csr_ptu_exapl_row;
1461 fetch csr_ptu_exapl_row into l_prev_other_row;
1462 if csr_ptu_exapl_row%found then
1463 update PER_PERSON_TYPE_USAGES_F
1464 set effective_start_date = p_date_start,
1465 object_version_number = object_version_number+1
1466 where person_type_usage_id = l_prev_other_row.person_type_usage_id
1467 and object_version_number = l_prev_other_row.object_version_number;
1468 end if;
1469 --
1470 open csr_ptu_apl_row;
1471 fetch csr_ptu_apl_row into l_prev_other_row;
1472 if csr_ptu_apl_row%found then
1473 if l_prev_other_row.effective_start_date > p_date_start-1 then
1474 fnd_message.set_name('PER','HR_289742_NO_CHG_DATE_PTU');
1475 fnd_message.raise_error;
1476 else
1477 update PER_PERSON_TYPE_USAGES_F
1478 set effective_end_date = p_date_start-1,
1479 object_version_number = object_version_number+1
1480 where person_type_usage_id = l_prev_other_row.person_type_usage_id
1481 and object_version_number = l_prev_other_row.object_version_number;
1482 end if;
1483 end if;
1484 --
1485 end if;
1486 --
1487 if g_debug then
1488 hr_utility.set_location(l_proc, 30);
1489 end if;
1490 --
1491 if l_rows_found then
1492 --
1493 l_old.person_id := l_c1.person_id;
1494 l_old.person_type_usage_id := l_c1.person_type_usage_id;
1495 l_old.person_type_id := l_c1.person_type_id;
1496 l_old.effective_start_date := l_c1.effective_start_date;
1497 l_old.effective_end_date := l_c1.effective_end_date;
1498 l_new.person_id := l_c1.person_id;
1499 l_new.person_type_usage_id := l_c1.person_type_usage_id;
1500 l_new.person_type_id := l_c1.person_type_id;
1501 l_new.effective_start_date := p_date_start;
1502 l_new.effective_end_date := l_c1.effective_end_date;
1503 --
1504 ben_ptu_ler.ler_chk(p_old => l_old,
1505 p_new => l_new,
1506 p_effective_date => p_date_start);
1507 --
1508 end if;
1509 --
1510 end if;
1511 close get_ptu;
1512 --
1513 if g_debug then
1514 hr_utility.set_location(' Leaving:'||l_proc, 100);
1515 end if;
1516 end change_hire_date_ptu;
1517 --
1518 --
1519 -- bug fix 7410493 starts
1520 --
1521 -- ----------------------------------------------------------------------------
1522 -- |------------------------< cancel_emp_apl_ptu >----------------------|
1523 -- ----------------------------------------------------------------------------
1524 --
1525 procedure cancel_emp_apl_ptu
1526 (
1527 p_effective_date in date
1528 ,p_person_id in number
1529 ,p_system_person_type in varchar2
1530 ) is
1531
1532
1533 c_person_type_usage_id number;
1534 --
1535 cursor csr_person_type_usages is
1536
1537 select max (effective_start_date),max(effective_end_date)
1538 from per_person_type_usages_f
1539 where person_type_usage_id = c_person_type_usage_id ;
1540
1541 --
1542
1543 l_date_start date;
1544 --
1545 l_csr_person_type_usages csr_person_type_usages%ROWTYPE := NULL;
1546 l_person_type_usage_id per_person_type_usages_f.person_type_usage_id%TYPE;
1547 l_effective_start_date per_person_type_usages_f.effective_start_date%TYPE;
1548 l_effective_end_date per_person_type_usages_f.effective_end_date%TYPE;
1549 l_object_version_number per_person_type_usages_f.object_version_number%TYPE;
1550 l_system_person_type per_person_types.system_person_type%TYPE;
1551 l_proc varchar2(72) := g_package||'cancel_emp_apl_ptu';
1552 l_effective_start_date1 per_person_type_usages_f.effective_start_date%TYPE;
1553 l_effective_end_date1 per_person_type_usages_f.effective_end_date%TYPE;
1554 l_object_version_number1 per_person_type_usages_f.object_version_number%TYPE;
1555 --
1556 -- BEGIN TCA_UNMERGE CHANGES
1557 cursor c_person is
1558 select *
1559 from per_all_people_f
1560 where person_id = p_person_id
1561 and p_effective_date
1562 between effective_start_date
1563 and effective_end_date;
1564 --
1565 l_person per_all_people_f%rowtype;
1566 --
1567 -- TCA_UNMERGE
1568 --
1569 begin
1570 --
1571 g_debug:=TRUE;
1572 if g_debug then
1573 hr_utility.set_location('Entering:'|| l_proc, 10);
1574 hr_utility.set_location('cancel_emp_apl_ptu.p_effective_date = '||to_char(p_effective_date,'DD-MON-YYYY'),11);
1575 hr_utility.set_location('cancel_emp_apl_ptu.p_person_id = '||to_char(p_person_id),11);
1576 hr_utility.set_location('cancel_emp_apl_ptu.p_system_person_type = '||p_system_person_type,11);
1577 end if;
1578 --
1579 begin
1580 select ptu.person_type_usage_id into c_person_type_usage_id
1581 from per_person_types ppt
1582 ,per_person_type_usages_f ptu
1583 where ppt.person_type_id = ptu.person_type_id
1584 and ppt.system_person_type = p_system_person_type
1585 and p_effective_date between ptu.effective_start_date and ptu.effective_end_date
1586 and ptu.person_id = p_person_id;
1587 if g_debug then
1588 hr_utility.set_location('cancel_emp_apl_ptu.c_person_type_usage_id = '||to_char(c_person_type_usage_id),12);
1589 end if;
1590 exception
1591 when no_data_found then
1592 fnd_message.set_name('PAY','HR_6153_ALL_PROCEDURE_FAIL');
1593 fnd_message.set_token('PROCEDURE',l_proc);
1594 fnd_message.set_token('STEP',13);
1595 fnd_message.raise_error;
1596 end;
1597 --
1598 --added following 'if' clause for 2449091
1599 --
1600 /*
1601 if p_system_person_type='EMP' then
1602 open csr_pds_start;
1603 fetch csr_pds_start into l_date_start;
1604 close csr_pds_start;
1605 elsif p_system_person_type='CWK' then
1606 open csr_pdp_start;
1607 fetch csr_pdp_start into l_date_start;
1608 close csr_pdp_start;
1609 else
1610 l_date_start := p_effective_date;
1611 end if;
1612 */
1613 l_date_start := p_effective_date;
1614 --
1615 open csr_person_type_usages;
1616 fetch csr_person_type_usages into l_effective_start_date , l_effective_end_date;
1617 close csr_person_type_usages;
1618
1619 hr_utility.set_location(l_proc, 30);
1620 hr_utility.set_location(l_proc||':'||to_char(l_effective_start_date,'DD-MON-YYYY'), 99);
1621 hr_utility.set_location(l_proc||':'||to_char(l_effective_end_date,'DD-MON-YYYY'), 99);
1622 hr_utility.set_location(l_proc||':'||to_char(c_person_type_usage_id),99);
1623
1624
1625
1626 if ((c_person_type_usage_id is not null)
1627 and (l_effective_start_date is not null)
1628 and (l_effective_end_date is not null)) then
1629 --
1630
1631 hr_utility.set_location(l_proc, 40);
1632
1633 --
1634 benefits_person_type_usage_b
1635 (p_effective_date => p_effective_date
1636 ,p_person_type_usage_id => l_person_type_usage_id
1637 );
1638 --
1639 -- Remove records for the entire time that this system person type was in
1640 -- effect. Done through direct SQL as row handler does not allow this kind
1641 -- of manipulation.
1642 --
1643 hr_utility.set_location(l_proc, 50);
1644
1645 hr_utility.set_location(l_proc||':'||to_char(l_effective_start_date,'DD-MON-YYYY'), 99);
1646 hr_utility.set_location(l_proc||':'||to_char(l_effective_end_date,'DD-MON-YYYY'), 99);
1647 hr_utility.set_location(l_proc||':'||to_char(c_person_type_usage_id),99);
1648
1649 delete
1650 from per_person_type_usages_f ptu
1651 where ptu.effective_start_date >= l_effective_start_date
1652 and ptu.effective_end_date <= l_effective_end_date
1653 and ptu.person_type_usage_id = c_person_type_usage_id;
1654 if sql%notfound then
1655 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
1656 hr_utility.set_message_token('PROCEDURE','cancel_emp_apl_ptu');
1657 hr_utility.set_message_token('STEP',1);
1658 hr_utility.raise_error;
1659 end if;
1660 --
1661 --
1662 hr_utility.set_location(l_proc, 60);
1663 -- Extend any previous record to cover the time that the cancelled system
1664 -- person type existed. Done through direct SQL as row handler does not
1665 -- allow this kind of manipulation.
1666 --
1667 hr_utility.set_location(l_proc, 70);
1668 update per_person_type_usages_f ptu
1669 set effective_end_date = l_effective_end_date
1670 where ptu.effective_end_date = (l_effective_start_date - 1)
1671 and ptu.person_type_usage_id = c_person_type_usage_id;
1672 if sql%notfound then
1673
1674 hr_utility.set_location(l_proc, 80);
1675 update per_person_type_usages_f ptu
1676 set effective_end_date = l_effective_end_date
1677 where ptu.effective_end_date = (l_effective_start_date - 1)
1678 and ptu.person_type_usage_id = ( select distinct (person_type_usage_id)
1679 from per_person_type_usages_f ppf,
1680 per_person_types ppt
1681 where ppf.person_id = p_person_id
1682 and ppt.PERSON_TYPE_ID = ppf.PERSON_TYPE_ID
1683 and ppt.system_person_type = 'APL'
1684 and effective_end_date = l_effective_start_date -1 );
1685
1686 end if;
1687
1688 -- fix for the bug 5685089
1689 --The csr_ptu_rec_extra looks for other person types that should remain in the system.
1690 --The records are fetched of other person types only when the Emp and Apl
1691 -- records are terminated on the same day. The loop is not executed otherwise.
1692 --On discussing internally, found that there was no need to look for other
1693 --person types of the record. Hence, we can eliminate the loop logic.
1694
1695 --fix for the bug 6012689
1696 --The csr_ptu_rec_extra is modified, it executes only in the case of Applicant.
1697 hr_utility.set_location(l_proc, 90);
1698
1699 -- end of fix for the bug 6012689
1700 -- end of fix for the bug 5685089
1701 ------------------------------------------------
1702 -- BEGIN TCA_UNMERGE CHANGES
1703 --
1704 -- Bug fix 3725055. IF condition removed.
1705 --if hr_general.g_data_migrator_mode <> 'P' then
1706 hr_utility.set_location(' Leaving: 1 ', 999);
1707 open c_person;
1708 fetch c_person into l_person;
1709 close c_person;
1710 hr_utility.set_location(' Leaving: 2 ', 91);
1711 per_hrtca_merge.create_tca_person(p_rec => l_person);
1712 hr_utility.set_location(' Leaving: 3', 99);
1713 --end if;
1714 --
1715 -- END TCA_UNMERGE CHANGES
1716 ------------------------------------------------
1717 benefits_person_type_usage_a
1718 (p_effective_date => p_effective_date
1719 ,p_person_type_usage_id => l_person_type_usage_id
1720 );
1721 --
1722 hr_utility.set_location(' Leaving: 4 ', 100);
1723 end if;
1724 --
1725 if g_debug then
1726 hr_utility.set_location(' Leaving:'||l_proc, 999);
1727 end if;
1728 --
1729
1730 end cancel_emp_apl_ptu;
1731 -- bug 7410493
1732 --
1733 end hr_per_type_usage_internal;
[Home] [Help]