[Home] [Help]
PACKAGE BODY: APPS.BEN_DT_TRGR_HANDLE
Source
1 package body ben_dt_trgr_handle as
2 /*$Header: bendttrg.pkb 120.4 2011/06/24 11:14:54 usaraswa ship $*/
3 --
4 -- Package Variables
5 --
6 g_package varchar2(33) := ' ben_dt_trgr_handle.';
7 --
8 g_debug boolean := hr_utility.debug_enabled;
9 --
10 --
11 procedure person(p_rowid in VARCHAR2
12 ,p_business_group_id in NUMBER
13 ,p_person_id in NUMBER
14 ,p_effective_start_date in DATE
15 ,p_effective_end_date in DATE
16 ,p_date_of_birth in DATE
17 ,p_date_of_death in DATE
18 ,p_marital_status in VARCHAR2
19 ,p_on_military_service in VARCHAR2
20 ,p_registered_disabled_flag in VARCHAR2
21 ,p_sex in VARCHAR2
22 ,p_student_status in VARCHAR2
23 ,p_coord_ben_med_pln_no in VARCHAR2
24 ,p_coord_ben_no_cvg_flag in VARCHAR2
25 ,p_uses_tobacco_flag in VARCHAR2
26 ,p_benefit_group_id in NUMBER
27 ,p_per_information10 in VARCHAR2
28 ,p_dpdnt_vlntry_svce_flag in VARCHAR2
29 ,p_receipt_of_death_cert_date in DATE
30 ,p_attribute1 in VARCHAR2
31 ,p_attribute2 in VARCHAR2
32 ,p_attribute3 in VARCHAR2
33 ,p_attribute4 in VARCHAR2
34 ,p_attribute5 in VARCHAR2
35 ,p_attribute6 in VARCHAR2
36 ,p_attribute7 in VARCHAR2
37 ,p_attribute8 in VARCHAR2
38 ,p_attribute9 in VARCHAR2
39 ,p_attribute10 in VARCHAR2
40 ,p_attribute11 in VARCHAR2
41 ,p_attribute12 in VARCHAR2
42 ,p_attribute13 in VARCHAR2
43 ,p_attribute14 in VARCHAR2
44 ,p_attribute15 in VARCHAR2
45 ,p_attribute16 in VARCHAR2
46 ,p_attribute17 in VARCHAR2
47 ,p_attribute18 in VARCHAR2
48 ,p_attribute19 in VARCHAR2
49 ,p_attribute20 in VARCHAR2
50 ,p_attribute21 in VARCHAR2
51 ,p_attribute22 in VARCHAR2
52 ,p_attribute23 in VARCHAR2
53 ,p_attribute24 in VARCHAR2
54 ,p_attribute25 in VARCHAR2
55 ,p_attribute26 in VARCHAR2
56 ,p_attribute27 in VARCHAR2
57 ,p_attribute28 in VARCHAR2
58 ,p_attribute29 in VARCHAR2
59 ,p_attribute30 in VARCHAR2
60 )
61 is
62 --
63 l_proc varchar2(72):=g_package||'person';
64 --
65
66 cursor get_old_ppf(p_rowid in varchar2)
67 is
68 select PERSON_ID
69 ,BUSINESS_GROUP_ID
70 ,EFFECTIVE_START_DATE
71 ,EFFECTIVE_END_DATE
72 ,DATE_OF_BIRTH
73 ,DATE_OF_DEATH
74 ,ON_MILITARY_SERVICE
75 ,MARITAL_STATUS
76 ,REGISTERED_DISABLED_FLAG
77 ,SEX
78 ,STUDENT_STATUS
79 ,BENEFIT_GROUP_ID
80 ,COORD_BEN_NO_CVG_FLAG
81 ,USES_TOBACCO_FLAG
82 ,COORD_BEN_MED_PLN_NO
83 ,PER_INFORMATION10
84 ,DPDNT_VLNTRY_SVCE_FLAG
85 ,RECEIPT_OF_DEATH_CERT_DATE
86 ,ATTRIBUTE1
87 ,ATTRIBUTE2
88 ,ATTRIBUTE3
89 ,ATTRIBUTE4
90 ,ATTRIBUTE5
91 ,ATTRIBUTE6
92 ,ATTRIBUTE7
93 ,ATTRIBUTE8
94 ,ATTRIBUTE9
95 ,ATTRIBUTE10
96 ,ATTRIBUTE11
97 ,ATTRIBUTE12
98 ,ATTRIBUTE13
99 ,ATTRIBUTE14
100 ,ATTRIBUTE15
101 ,ATTRIBUTE16
102 ,ATTRIBUTE17
103 ,ATTRIBUTE18
104 ,ATTRIBUTE19
105 ,ATTRIBUTE20
106 ,ATTRIBUTE21
107 ,ATTRIBUTE22
108 ,ATTRIBUTE23
109 ,ATTRIBUTE24
110 ,ATTRIBUTE25
111 ,ATTRIBUTE26
112 ,ATTRIBUTE27
113 ,ATTRIBUTE28
114 ,ATTRIBUTE29
115 ,ATTRIBUTE30
116 ,null
117 from per_all_people_f
118
119 where rowid = p_rowid;
120
121 --
122
123 l_old_rec ben_ppf_ler.g_ppf_ler_rec;
124
125 l_new_rec ben_ppf_ler.g_ppf_ler_rec;
126
127 --
128
129 l_benasg_id number;
130 l_benasg_ovn number;
131 l_perhasmultptus boolean;
132 --
133 begin
134 --
135 -- Bug : 3320133
136
137 benutils.set_data_migrator_mode;
138 --
139 -- Bug : 3320133
140 --
141 g_debug := hr_utility.debug_enabled;
142 if hr_general.g_data_migrator_mode not in ( 'Y','P') then
143 if g_debug then
144 hr_utility.set_location('Entering: '|| l_proc, 10);
145 hr_utility.set_location('p_mar_stat: '||p_marital_status||' '||l_proc, 10);
146 hr_utility.set_location('p_per_esd: '||p_effective_start_date||' '||l_proc, 10);
147 hr_utility.set_location('rowid: '||p_rowid||' '||l_proc, 10);
148 end if;
149 --
150 ben_assignment_internal.copy_empasg_to_benasg
151 (p_person_id => p_person_id
152 --
153 ,p_per_date_of_death => p_date_of_death
154 ,p_per_marital_status => p_marital_status
155 ,p_per_esd => p_effective_start_date
156 --
157 ,p_assignment_id => l_benasg_id
158 ,p_object_version_number => l_benasg_ovn
159 ,p_perhasmultptus => l_perhasmultptus
160 );
161 open get_old_ppf(p_rowid);
162 fetch get_old_ppf into l_old_rec;
163 close get_old_ppf;
164 --
165 l_new_rec.business_group_id := p_business_group_id;
166 l_new_rec.person_id := p_person_id;
167 l_new_rec.effective_start_date := p_effective_start_date;
168 l_new_rec.effective_end_date := p_effective_end_date;
169 l_new_rec.date_of_birth := p_date_of_birth;
170 l_new_rec.date_of_death := p_date_of_death;
171 l_new_rec.marital_status := p_marital_status;
172 l_new_rec.on_military_service := p_on_military_service;
173 l_new_rec.registered_disabled_flag := p_registered_disabled_flag;
174 l_new_rec.sex := p_sex;
175 l_new_rec.student_status := p_student_status;
176 l_new_rec.coord_ben_med_pln_no := p_coord_ben_med_pln_no;
177 l_new_rec.coord_ben_no_cvg_flag := p_coord_ben_no_cvg_flag;
178 l_new_rec.uses_tobacco_flag := p_uses_tobacco_flag;
179 l_new_rec.benefit_group_id := p_benefit_group_id;
180 l_new_rec.DPDNT_VLNTRY_SVCE_FLAG:= p_dpdnt_vlntry_svce_flag;
181 l_new_rec.RECEIPT_OF_DEATH_CERT_DATE:= p_receipt_of_death_cert_date;
182 l_new_rec.per_information10 := p_per_information10;
183 l_new_rec.attribute1 := p_attribute1;
184 l_new_rec.attribute2 := p_attribute2;
185 l_new_rec.attribute3 := p_attribute3;
186 l_new_rec.attribute4 := p_attribute4;
187 l_new_rec.attribute5 := p_attribute5;
188 l_new_rec.attribute6 := p_attribute6;
189 l_new_rec.attribute7 := p_attribute7;
190 l_new_rec.attribute8 := p_attribute8;
191 l_new_rec.attribute9 := p_attribute9;
192 l_new_rec.attribute10 := p_attribute10;
193 l_new_rec.attribute11 := p_attribute11;
194 l_new_rec.attribute12 := p_attribute12;
195 l_new_rec.attribute13 := p_attribute13;
196 l_new_rec.attribute14 := p_attribute14;
197 l_new_rec.attribute15 := p_attribute15;
198 l_new_rec.attribute16 := p_attribute16;
199 l_new_rec.attribute17 := p_attribute17;
200 l_new_rec.attribute18 := p_attribute18;
201 l_new_rec.attribute19 := p_attribute19;
202 l_new_rec.attribute20 := p_attribute20;
203 l_new_rec.attribute21 := p_attribute21;
204 l_new_rec.attribute22 := p_attribute22;
205 l_new_rec.attribute23 := p_attribute23;
206 l_new_rec.attribute24 := p_attribute24;
207 l_new_rec.attribute25 := p_attribute25;
208 l_new_rec.attribute26 := p_attribute26;
209 l_new_rec.attribute27 := p_attribute27;
210 l_new_rec.attribute28 := p_attribute28;
211 l_new_rec.attribute29 := p_attribute29;
212 l_new_rec.attribute30 := p_attribute30;
213 --
214 --Bug 2215549 added p_effective_start_date parameter
215 --
216 ben_ppf_ler.ler_chk(l_old_rec,l_new_rec,p_effective_start_date );
217 if g_debug then
218 hr_utility.set_location('Leaving:'|| l_proc, 100);
219 end if;
220 end if;
221
222 end person;
223
224 procedure person(p_rowid in VARCHAR2
225 ,p_business_group_id in NUMBER
226 ,p_person_id in NUMBER
227 ,p_effective_start_date in DATE
228 ,p_effective_end_date in DATE
229 ,p_date_of_birth in DATE
230 ,p_date_of_death in DATE
231 ,p_marital_status in VARCHAR2
232 ,p_on_military_service in VARCHAR2
233 ,p_registered_disabled_flag in VARCHAR2
234 ,p_sex in VARCHAR2
235 ,p_student_status in VARCHAR2
236 ,p_coord_ben_med_pln_no in VARCHAR2
237 ,p_coord_ben_no_cvg_flag in VARCHAR2
238 ,p_uses_tobacco_flag in VARCHAR2
239 ,p_benefit_group_id in NUMBER
240 ,p_per_information10 in VARCHAR2
241 ,p_original_date_of_hire in DATE
242 ,p_dpdnt_vlntry_svce_flag in VARCHAR2
243 ,p_receipt_of_death_cert_date in DATE
244 ,p_attribute1 in VARCHAR2
245 ,p_attribute2 in VARCHAR2
246 ,p_attribute3 in VARCHAR2
247 ,p_attribute4 in VARCHAR2
248 ,p_attribute5 in VARCHAR2
249 ,p_attribute6 in VARCHAR2
250 ,p_attribute7 in VARCHAR2
251 ,p_attribute8 in VARCHAR2
252 ,p_attribute9 in VARCHAR2
253 ,p_attribute10 in VARCHAR2
254 ,p_attribute11 in VARCHAR2
255 ,p_attribute12 in VARCHAR2
256 ,p_attribute13 in VARCHAR2
257 ,p_attribute14 in VARCHAR2
258 ,p_attribute15 in VARCHAR2
259 ,p_attribute16 in VARCHAR2
260 ,p_attribute17 in VARCHAR2
261 ,p_attribute18 in VARCHAR2
262 ,p_attribute19 in VARCHAR2
263 ,p_attribute20 in VARCHAR2
264 ,p_attribute21 in VARCHAR2
265 ,p_attribute22 in VARCHAR2
266 ,p_attribute23 in VARCHAR2
267 ,p_attribute24 in VARCHAR2
268 ,p_attribute25 in VARCHAR2
269 ,p_attribute26 in VARCHAR2
270 ,p_attribute27 in VARCHAR2
271 ,p_attribute28 in VARCHAR2
272 ,p_attribute29 in VARCHAR2
273 ,p_attribute30 in VARCHAR2
274 )
275 is
276 --
277 l_proc varchar2(72):=g_package||'person';
278 --
279 cursor get_old_ppf(p_rowid in varchar2)
280 is
281 select PERSON_ID
282 ,BUSINESS_GROUP_ID
283 ,EFFECTIVE_START_DATE
284 ,EFFECTIVE_END_DATE
285 ,DATE_OF_BIRTH
286 ,DATE_OF_DEATH
287 ,ON_MILITARY_SERVICE
288 ,MARITAL_STATUS
289 ,REGISTERED_DISABLED_FLAG
290 ,SEX
291 ,STUDENT_STATUS
292 ,BENEFIT_GROUP_ID
293 ,COORD_BEN_NO_CVG_FLAG
294 ,USES_TOBACCO_FLAG
295 ,COORD_BEN_MED_PLN_NO
296 ,PER_INFORMATION10
297 ,DPDNT_VLNTRY_SVCE_FLAG
298 ,RECEIPT_OF_DEATH_CERT_DATE
299 ,ATTRIBUTE1
300 ,ATTRIBUTE2
301 ,ATTRIBUTE3
302 ,ATTRIBUTE4
303 ,ATTRIBUTE5
304 ,ATTRIBUTE6
305 ,ATTRIBUTE7
306 ,ATTRIBUTE8
307 ,ATTRIBUTE9
308 ,ATTRIBUTE10
309 ,ATTRIBUTE11
310 ,ATTRIBUTE12
311 ,ATTRIBUTE13
312 ,ATTRIBUTE14
313 ,ATTRIBUTE15
314 ,ATTRIBUTE16
315 ,ATTRIBUTE17
316 ,ATTRIBUTE18
317 ,ATTRIBUTE19
318 ,ATTRIBUTE20
319 ,ATTRIBUTE21
320 ,ATTRIBUTE22
321 ,ATTRIBUTE23
322 ,ATTRIBUTE24
323 ,ATTRIBUTE25
324 ,ATTRIBUTE26
325 ,ATTRIBUTE27
326 ,ATTRIBUTE28
327 ,ATTRIBUTE29
328 ,ATTRIBUTE30
329 ,ORIGINAL_DATE_OF_HIRE
330 from per_all_people_f
331 where rowid = p_rowid;
332 --
333 l_old_rec ben_ppf_ler.g_ppf_ler_rec;
334 l_new_rec ben_ppf_ler.g_ppf_ler_rec;
335 --
336 l_benasg_id number;
337 l_benasg_ovn number;
338 l_perhasmultptus boolean;
339
340 begin
341 --
342 -- Bug : 3320133
343
344 benutils.set_data_migrator_mode;
345 --
346 -- Bug : 3320133
347 --
348 g_debug := hr_utility.debug_enabled;
349 if hr_general.g_data_migrator_mode not in ( 'Y','P') then
350 if g_debug then
351 hr_utility.set_location('Entering: '|| l_proc, 10);
352 hr_utility.set_location('p_mar_stat: '||p_marital_status||' '||l_proc, 10);
353 hr_utility.set_location('p_per_esd: '||p_effective_start_date||' '||l_proc, 10);
354 hr_utility.set_location('rowid: '||p_rowid||' '||l_proc, 10);
355 end if;
356
357 --
358 ben_assignment_internal.copy_empasg_to_benasg
359 (p_person_id => p_person_id
360 --
361 ,p_per_date_of_death => p_date_of_death
362 ,p_per_marital_status => p_marital_status
363 ,p_per_esd => p_effective_start_date
364 --
365 ,p_assignment_id => l_benasg_id
366 ,p_object_version_number => l_benasg_ovn
367 ,p_perhasmultptus => l_perhasmultptus
368 );
369 open get_old_ppf(p_rowid);
370 fetch get_old_ppf into l_old_rec;
371 close get_old_ppf;
372 --
373 l_new_rec.business_group_id := p_business_group_id;
374 l_new_rec.person_id := p_person_id;
375 l_new_rec.effective_start_date := p_effective_start_date;
376
377 l_new_rec.effective_end_date := p_effective_end_date;
378 l_new_rec.date_of_birth := p_date_of_birth;
379 l_new_rec.date_of_death := p_date_of_death;
380 l_new_rec.marital_status := p_marital_status;
381 l_new_rec.on_military_service := p_on_military_service;
382 l_new_rec.registered_disabled_flag := p_registered_disabled_flag;
383 l_new_rec.sex := p_sex;
384 l_new_rec.student_status := p_student_status;
385 l_new_rec.coord_ben_med_pln_no := p_coord_ben_med_pln_no;
386 l_new_rec.coord_ben_no_cvg_flag := p_coord_ben_no_cvg_flag;
387 l_new_rec.uses_tobacco_flag := p_uses_tobacco_flag;
388 l_new_rec.benefit_group_id := p_benefit_group_id;
389 l_new_rec.DPDNT_VLNTRY_SVCE_FLAG:= p_dpdnt_vlntry_svce_flag;
390 l_new_rec.RECEIPT_OF_DEATH_CERT_DATE:= p_receipt_of_death_cert_date;
391 l_new_rec.per_information10 := p_per_information10;
392 l_new_rec.attribute1 := p_attribute1;
393 l_new_rec.attribute2 := p_attribute2;
394 l_new_rec.attribute3 := p_attribute3;
395 l_new_rec.attribute4 := p_attribute4;
396 l_new_rec.attribute5 := p_attribute5;
397 l_new_rec.attribute6 := p_attribute6;
398 l_new_rec.attribute7 := p_attribute7;
399 l_new_rec.attribute8 := p_attribute8;
400 l_new_rec.attribute9 := p_attribute9;
401 l_new_rec.attribute10 := p_attribute10;
402 l_new_rec.attribute11 := p_attribute11;
403 l_new_rec.attribute12 := p_attribute12;
404 l_new_rec.attribute13 := p_attribute13;
405 l_new_rec.attribute14 := p_attribute14;
406 l_new_rec.attribute15 := p_attribute15;
407 l_new_rec.attribute16 := p_attribute16;
408 l_new_rec.attribute17 := p_attribute17;
409 l_new_rec.attribute18 := p_attribute18;
410 l_new_rec.attribute19 := p_attribute19;
411 l_new_rec.attribute20 := p_attribute20;
412 l_new_rec.attribute21 := p_attribute21;
413 l_new_rec.attribute22 := p_attribute22;
414 l_new_rec.attribute23 := p_attribute23;
415 l_new_rec.attribute24 := p_attribute24;
416 l_new_rec.attribute25 := p_attribute25;
417 l_new_rec.attribute26 := p_attribute26;
418 l_new_rec.attribute27 := p_attribute27;
419 l_new_rec.attribute28 := p_attribute28;
420 l_new_rec.attribute29 := p_attribute29;
421 l_new_rec.attribute30 := p_attribute30;
422 l_new_rec.person_id := p_person_id;
423 ben_ppf_ler.ler_chk(l_old_rec,l_new_rec,p_effective_start_date );
424 if g_debug then
425 hr_utility.set_location('Leaving:'|| l_proc, 100);
426 end if;
427 end if;
428
429
430 end person;
431 procedure assignment
432 (p_rowid IN VARCHAR2
433 ,p_assignment_id IN NUMBER
434 ,p_business_group_id IN NUMBER
435 ,p_person_id in NUMBER
436 ,p_effective_start_date in DATE
437 ,p_effective_end_date in DATE
438 ,p_assignment_status_type_id IN NUMBER
439 ,p_assignment_type IN VARCHAR2
440 ,p_organization_id IN NUMBER
441 ,p_primary_flag IN VARCHAR2
442 ,p_change_reason IN VARCHAR2
443 ,p_employment_category IN VARCHAR2
444 ,p_frequency IN VARCHAR2
445
446 ,p_grade_id IN NUMBER
447 ,p_job_id IN NUMBER
448 ,p_position_id IN NUMBER
449 ,p_location_id IN NUMBER
450 ,p_normal_hours IN VARCHAR2
451 ,p_payroll_id in NUMBER
452 ,p_pay_basis_id IN NUMBER
453 ,p_bargaining_unit_code IN VARCHAR2
454 ,p_labour_union_member_flag IN VARCHAR2
455 ,p_hourly_salaried_code IN VARCHAR2
456 ,p_people_group_id IN NUMBER
457 ,p_ass_attribute1 in VARCHAR2
458 ,p_ass_attribute2 in VARCHAR2
459 ,p_ass_attribute3 in VARCHAR2
460 ,p_ass_attribute4 in VARCHAR2
461 ,p_ass_attribute5 in VARCHAR2
462 ,p_ass_attribute6 in VARCHAR2
463 ,p_ass_attribute7 in VARCHAR2
464 ,p_ass_attribute8 in VARCHAR2
465 ,p_ass_attribute9 in VARCHAR2
466 ,p_ass_attribute10 in VARCHAR2
467 ,p_ass_attribute11 in VARCHAR2
468 ,p_ass_attribute12 in VARCHAR2
469 ,p_ass_attribute13 in VARCHAR2
470 ,p_ass_attribute14 in VARCHAR2
471 ,p_ass_attribute15 in VARCHAR2
472 ,p_ass_attribute16 in VARCHAR2
473 ,p_ass_attribute17 in VARCHAR2
474 ,p_ass_attribute18 in VARCHAR2
475 ,p_ass_attribute19 in VARCHAR2
476 ,p_ass_attribute20 in VARCHAR2
477 ,p_ass_attribute21 in VARCHAR2
478 ,p_ass_attribute22 in VARCHAR2
479 ,p_ass_attribute23 in VARCHAR2
480 ,p_ass_attribute24 in VARCHAR2
481 ,p_ass_attribute25 in VARCHAR2
482 ,p_ass_attribute26 in VARCHAR2
483 ,p_ass_attribute27 in VARCHAR2
484 ,p_ass_attribute28 in VARCHAR2
485 ,p_ass_attribute29 in VARCHAR2
486 ,p_ass_attribute30 in VARCHAR2
487 )
488 is
489 --
490 l_proc varchar2(72):=g_package||'assignment';
491 --
492 l_old_rec ben_asg_ler.g_asg_ler_rec;
493 l_new_rec ben_asg_ler.g_asg_ler_rec;
494 --
495 l_benasg_id number;
496 l_benasg_ovn number;
497 --
498 --
499 --Renaming for bug 10152981
500 cursor get_new_asg(p_rowid in varchar2)
501 is
502 select PERSON_ID
503 ,ASSIGNMENT_ID
504 ,BUSINESS_GROUP_ID
505 ,EFFECTIVE_START_DATE
506 ,EFFECTIVE_END_DATE
507 ,ORGANIZATION_ID
508 ,GRADE_ID
509 ,JOB_ID
510 ,POSITION_ID
511 ,PAYROLL_ID
512 ,LOCATION_ID
513 ,ASSIGNMENT_STATUS_TYPE_ID
514 ,ASSIGNMENT_TYPE
515 ,PAY_BASIS_ID
516 ,PRIMARY_FLAG
517 ,CHANGE_REASON
518 ,EMPLOYMENT_CATEGORY
519 ,FREQUENCY
520 ,NORMAl_HOURS
521 ,BARGAINING_UNIT_CODE
522 ,LABOUR_UNION_MEMBER_FLAG
523 ,PEOPLE_GROUP_ID
524 ,HOURLY_SALARIED_CODE
525 ,ASS_ATTRIBUTE1
526 ,ASS_ATTRIBUTE2
527 ,ASS_ATTRIBUTE3
528 ,ASS_ATTRIBUTE4
529 ,ASS_ATTRIBUTE5
530 ,ASS_ATTRIBUTE6
531 ,ASS_ATTRIBUTE7
532 ,ASS_ATTRIBUTE8
533 ,ASS_ATTRIBUTE9
534 ,ASS_ATTRIBUTE10
535 ,ASS_ATTRIBUTE11
536 ,ASS_ATTRIBUTE12
537 ,ASS_ATTRIBUTE13
538 ,ASS_ATTRIBUTE14
539 ,ASS_ATTRIBUTE15
540 ,ASS_ATTRIBUTE16
541 ,ASS_ATTRIBUTE17
542 ,ASS_ATTRIBUTE18
543 ,ASS_ATTRIBUTE19
544 ,ASS_ATTRIBUTE20
545 ,ASS_ATTRIBUTE21
546 ,ASS_ATTRIBUTE22
547 ,ASS_ATTRIBUTE23
548 ,ASS_ATTRIBUTE24
549 ,ASS_ATTRIBUTE25
550 ,ASS_ATTRIBUTE26
551 ,ASS_ATTRIBUTE27
552 ,ASS_ATTRIBUTE28
553 ,ASS_ATTRIBUTE29
554 ,ASS_ATTRIBUTE30
555 from per_all_assignments_f
556 where rowid = p_rowid;
557 --
558 begin
559 --
560 g_debug := hr_utility.debug_enabled;
561 if hr_general.g_data_migrator_mode not in ( 'Y','P') then
562 if g_debug then
563 hr_utility.set_location('Entering:'|| l_proc, 10);
564 hr_utility.set_location('p_per_esd: '||p_effective_start_date||' '||l_proc, 10);
565 hr_utility.set_location('rowid: '||p_rowid||' '||l_proc, 10);
566 end if;
567 --
568 --Start of bug 10152981
569 /*
570 open get_old_asg(p_rowid);
571 fetch get_old_asg into l_old_rec;
572 close get_old_asg;
573 --
574 l_new_rec.business_group_id := p_business_group_id;
575 l_new_rec.person_id := p_person_id;
576 l_new_rec.effective_start_date := p_effective_start_date;
577 l_new_rec.effective_end_date := p_effective_end_date;
578 l_new_rec.assignment_status_type_id :=p_assignment_status_type_id;
579 l_new_rec.assignment_type :=p_assignment_type;
580 l_new_rec.organization_id := p_organization_id;
581 l_new_rec.primary_flag := p_primary_flag;
582 l_new_rec.change_reason := p_change_reason;
583 l_new_rec.employment_category := p_employment_category;
584
585 l_new_rec.assignment_id := p_assignment_id;
586 l_new_rec.frequency := p_frequency;
587 l_new_rec.grade_id := p_grade_id;
588 l_new_rec.job_id := p_job_id;
589 l_new_rec.position_id := p_position_id;
590 l_new_rec.location_id := p_location_id;
591 l_new_rec.normal_hours := p_normal_hours;
592 l_new_rec.payroll_id := p_payroll_id;
593 l_new_rec.pay_basis_id := p_pay_basis_id;
594 l_new_rec.bargaining_unit_code := p_bargaining_unit_code;
595 l_new_rec.labour_union_member_flag := p_labour_union_member_flag;
596 l_new_rec.people_group_id := p_people_group_id;
597 l_new_rec.hourly_salaried_code := p_hourly_salaried_code;
598 l_new_rec.ass_attribute1 := p_ass_attribute1;
599 l_new_rec.ass_attribute2 := p_ass_attribute2;
600 l_new_rec.ass_attribute3 := p_ass_attribute3;
601 l_new_rec.ass_attribute4 := p_ass_attribute4;
602 l_new_rec.ass_attribute5 := p_ass_attribute5;
603 l_new_rec.ass_attribute6 := p_ass_attribute6;
604 l_new_rec.ass_attribute7 := p_ass_attribute7;
605 l_new_rec.ass_attribute8 := p_ass_attribute8;
606 l_new_rec.ass_attribute9 := p_ass_attribute9;
607 l_new_rec.ass_attribute10 := p_ass_attribute10;
608 l_new_rec.ass_attribute11 := p_ass_attribute11;
609 l_new_rec.ass_attribute12 := p_ass_attribute12;
610 l_new_rec.ass_attribute13 := p_ass_attribute13;
611 l_new_rec.ass_attribute14 := p_ass_attribute14;
612 l_new_rec.ass_attribute15 := p_ass_attribute15;
613 l_new_rec.ass_attribute16 := p_ass_attribute16;
614 l_new_rec.ass_attribute17 := p_ass_attribute17;
615 l_new_rec.ass_attribute18 := p_ass_attribute18;
616 l_new_rec.ass_attribute19 := p_ass_attribute19;
617 l_new_rec.ass_attribute20 := p_ass_attribute20;
618 l_new_rec.ass_attribute21 := p_ass_attribute21;
619 l_new_rec.ass_attribute22 := p_ass_attribute22;
620 l_new_rec.ass_attribute23 := p_ass_attribute23;
621 l_new_rec.ass_attribute24 := p_ass_attribute24;
622 l_new_rec.ass_attribute25 := p_ass_attribute25;
623 l_new_rec.ass_attribute26 := p_ass_attribute26;
624 l_new_rec.ass_attribute27 := p_ass_attribute27;
625 l_new_rec.ass_attribute28 := p_ass_attribute28;
626 l_new_rec.ass_attribute29 := p_ass_attribute29;
627 l_new_rec.ass_attribute30 := p_ass_attribute30;
628 */
629 --For bug 10152981, per file has been modified to send all the old values and the rowid so that
630 --we could fetch the new values. This way we do not have to modify the procedure paramters. This was
631 --done to avoid the PER file dependency on BEN file in future
632
633 l_old_rec.business_group_id := p_business_group_id;
634 l_old_rec.person_id := p_person_id;
635 l_old_rec.effective_start_date := p_effective_start_date;
636 l_old_rec.effective_end_date := p_effective_end_date;
637 l_old_rec.assignment_status_type_id :=p_assignment_status_type_id;
638 l_old_rec.assignment_type :=p_assignment_type;
639 l_old_rec.organization_id := p_organization_id;
640 l_old_rec.primary_flag := p_primary_flag;
641 l_old_rec.change_reason := p_change_reason;
642 l_old_rec.employment_category := p_employment_category;
643 l_old_rec.assignment_id := p_assignment_id;
644 l_old_rec.frequency := p_frequency;
645 l_old_rec.grade_id := p_grade_id;
646 l_old_rec.job_id := p_job_id;
647 l_old_rec.position_id := p_position_id;
648 l_old_rec.location_id := p_location_id;
649 l_old_rec.normal_hours := p_normal_hours;
650 l_old_rec.payroll_id := p_payroll_id;
651 l_old_rec.pay_basis_id := p_pay_basis_id;
652 l_old_rec.bargaining_unit_code := p_bargaining_unit_code;
653 l_old_rec.labour_union_member_flag := p_labour_union_member_flag;
654 l_old_rec.people_group_id := p_people_group_id;
655 l_old_rec.hourly_salaried_code := p_hourly_salaried_code;
656 l_old_rec.ass_attribute1 := p_ass_attribute1;
657 l_old_rec.ass_attribute2 := p_ass_attribute2;
658 l_old_rec.ass_attribute3 := p_ass_attribute3;
659 l_old_rec.ass_attribute4 := p_ass_attribute4;
660 l_old_rec.ass_attribute5 := p_ass_attribute5;
661 l_old_rec.ass_attribute6 := p_ass_attribute6;
662 l_old_rec.ass_attribute7 := p_ass_attribute7;
663 l_old_rec.ass_attribute8 := p_ass_attribute8;
664 l_old_rec.ass_attribute9 := p_ass_attribute9;
665 l_old_rec.ass_attribute10 := p_ass_attribute10;
666 l_old_rec.ass_attribute11 := p_ass_attribute11;
667 l_old_rec.ass_attribute12 := p_ass_attribute12;
668 l_old_rec.ass_attribute13 := p_ass_attribute13;
669 l_old_rec.ass_attribute14 := p_ass_attribute14;
670 l_old_rec.ass_attribute15 := p_ass_attribute15;
671 l_old_rec.ass_attribute16 := p_ass_attribute16;
672 l_old_rec.ass_attribute17 := p_ass_attribute17;
673 l_old_rec.ass_attribute18 := p_ass_attribute18;
674 l_old_rec.ass_attribute19 := p_ass_attribute19;
675 l_old_rec.ass_attribute20 := p_ass_attribute20;
676 l_old_rec.ass_attribute21 := p_ass_attribute21;
677 l_old_rec.ass_attribute22 := p_ass_attribute22;
678 l_old_rec.ass_attribute23 := p_ass_attribute23;
679 l_old_rec.ass_attribute24 := p_ass_attribute24;
680 l_old_rec.ass_attribute25 := p_ass_attribute25;
681 l_old_rec.ass_attribute26 := p_ass_attribute26;
682 l_old_rec.ass_attribute27 := p_ass_attribute27;
683 l_old_rec.ass_attribute28 := p_ass_attribute28;
684 l_old_rec.ass_attribute29 := p_ass_attribute29;
685 l_old_rec.ass_attribute30 := p_ass_attribute30;
686
687 open get_new_asg(p_rowid);
688 fetch get_new_asg into l_new_rec;
689 close get_new_asg;
690
691 --End of bug 10152981
692 -- Bug# 12664112
693 -- Changed the argument of ben_asg_ler.ler_chk from p_effective_start_date
694 -- to l_new_rec.effective_start_date
695 --ud changes start
696 --replaced old effective date with new effective date
697 ben_asg_ler.ler_chk(l_old_rec,l_new_rec,l_new_rec.effective_start_date);
698 --ud changes end
699
700 --ben_asg_ler.ler_chk(l_old_rec,l_new_rec,p_effective_start_date); --ud commented
701 -- Bug# 12664112
702 if g_debug then
703 hr_utility.set_location('Leaving:'|| l_proc, 100);
704 end if;
705 end if;
706 --
707 end assignment;
708 /*
709 procedure person_type_usages
710 (p_rowid in VARCHAR2
711 ,p_person_id IN NUMBER
712 ,p_person_type_id IN NUMBER
713 -- ,p_effective_start_date in DATE
714 -- ,p_effective_end_date in DATE
715 )
716 is
717 --
718 l_proc varchar2(72):=g_package||'person_type_usages';
719 --
720 cursor get_old_ptu(p_rowid in VARCHAR2) is
721 select PERSON_ID
722 -- 9999 ,person_type_usage_id
723 ,null
724 ,PERSON_TYPE_ID
725 ,EFFECTIVE_START_DATE
726 ,EFFECTIVE_END_DATE
727 from per_person_type_usages_f
728 where rowid = p_rowid;
729 --
730 l_old_rec ben_ptu_ler.g_ptu_ler_rec;
731 l_new_rec ben_ptu_ler.g_ptu_ler_rec;
732 --
733 begin
734 g_debug := hr_utility.debug_enabled;
735 if g_debug then
736 hr_utility.set_location('MUPPET',10);
737 end if;
738 --
739 if hr_general.g_data_migrator_mode not in ( 'Y','P') then
740 if g_debug then
741 hr_utility.set_location('Entering:'|| l_proc, 10);
742 end if;
743 -- get old record
744 open get_old_ptu(p_rowid);
745 fetch get_old_ptu into l_old_rec;
746 close get_old_ptu;
747 -- get new record
748 l_new_rec.person_id := p_person_id;
749 l_new_rec.person_type_id := p_person_type_id;
750 -- 999 l_new_rec.person_type_usage_id := p_person_type_usage_id;
751 -- 999 l_new_rec.person_type_usage_id := l_old_rec.person_type_usage_id;
752 l_new_rec.business_group_id := null;
753 -- l_new_rec.effective_start_date := p_effective_start_date;
754 -- l_new_rec.effective_end_date := p_effective_end_date;
755 --
756 ben_ptu_ler.ler_chk(l_old_rec,l_new_rec);
757 if g_debug then
758 hr_utility.set_location('Leaving:'|| l_proc, 100);
759 end if;
760 end if;
761 end person_type_usages;
762 */
763
764 procedure periods_of_service
765 (p_rowid in VARCHAR2
766 ,p_person_id IN NUMBER
767 ,p_pds_atd IN date
768 ,p_pds_leaving_reason in VARCHAR2
769 ,p_pds_fpd IN date
770 ,p_pds_old_atd IN date default null
771 )
772 is
773 --
774 l_proc varchar2(72):=g_package||'periods_of_service';
775 --
776 l_benasg_id number;
777 l_exists varchar2(1);
778 l_benasg_ovn number;
779 l_perhasmultptus boolean;
780 l_delete_benass boolean := false;
781 l_num_recs number;
782 l_effective_start_date date;
783 l_effective_end_date date;
784 --
785 cursor c1 is
786 select count(*)
787 from per_periods_of_service
788 where person_id = p_person_id;
789 --
790 cursor c2 is
791 select *
792 from per_all_assignments_f
793 where person_id = p_person_id
794 and assignment_type = 'B'
795 and primary_flag = 'Y'
796 --
797 -- Bug 4395472 : added condition so that correct benefit
798 -- assignment record is picked
799 and effective_end_date >= p_pds_old_atd
800 -- End Bug 4395472
801 order by effective_start_date;
802 --
803 cursor c3 is
804 select null
805 from per_all_assignments_f asg
806 where asg.person_id = p_person_id
807 and asg.effective_start_date = p_pds_atd + 1
808 and assignment_type = 'B'
809 and primary_flag = 'Y';
810 --
811 l_c2 c2%rowtype;
812 --
813 l_ptu_index number := 0;
814 l_ptu_system_person_type per_person_types.system_person_type%type;
815 --
816 cursor c1_revterm_ptu is
817 select ptu.person_id, ppt.system_person_type
818 from per_person_type_usages_f ptu,
819 per_person_types ppt
820 where ptu.person_id in (select pcr.contact_person_id
821 from per_contact_relationships pcr
822 where pcr.person_id = p_person_id
823 )
824 and trunc(p_pds_old_atd+1) between trunc(ptu.effective_start_date)
825 and trunc(ptu.effective_end_date)
826 and ptu.person_type_id = ppt.person_type_id
827 and ppt.system_person_type in ( 'SRVNG_SPS',
828 'SRVNG_DP',
829 'SRVNG_DPFM',
830 'SRVNG_FMLY_MMBR');
831 --
832 -- Bug 3865655
833 l_zap_mode boolean;
834 l_delete_mode boolean;
835 l_future_change_mode boolean;
836 l_delete_next_change_mode boolean;
837 l_asg_delete_mode varchar2(60);
838 -- Bug 3865655
839 --
840 begin
841 --
842
843 g_debug := hr_utility.debug_enabled;
844 if hr_general.g_data_migrator_mode not in ( 'Y','P') then
845 if g_debug then
846 hr_utility.set_location('Entering:'|| l_proc, 10);
847 end if;
848 --
849 -- Only create a benefit assignment if the actual termination date
850 -- is not null. When the hr apis are called to terminate an employee, we
851 -- do not want the benefit assignment created twice.
852 --
853 if g_debug then
854 hr_utility.set_location('p_pds_old_atd: '|| p_pds_old_atd, 10);
855 end if;
856 --
857 if (p_pds_old_atd is null and
858 p_pds_atd is not null) then
859 open c3;
860 fetch c3 into l_exists;
861 if c3%notfound then
862 close c3;
863 ben_assignment_internal.copy_empasg_to_benasg
864 (p_person_id => p_person_id
865 --
866 ,p_pds_atd => p_pds_atd
867 ,p_pds_leaving_reason => p_pds_leaving_reason
868 ,p_pds_fpd => p_pds_fpd
869 --
870 ,p_assignment_id => l_benasg_id
871 ,p_object_version_number => l_benasg_ovn
872 ,p_perhasmultptus => l_perhasmultptus
873 );
874 else
875 close c3;
876 end if;
877 end if;
878
879 -- Fix for 2057246
880 /* The reverse termination:
881 Requirement:
882 When the termination is reversed, the persontype usages created
883 at the time of termination, should be cancelled.
884 Plan :
885 if old_atd is not null and atd is null
886 then
887 1. For each termination related persontype usages
888 get the ptu s created at the time of termination
889 2. cancell them.
890 end if.
891 */
892
893 begin
894
895 if g_debug then
896 hr_utility.set_location('To cancel ptu s for reverse termination : ', 1010);
897 end if;
898 if p_pds_atd is null and
899 p_pds_old_atd is not null
900 then
901 if g_debug then
902 hr_utility.set_location('Cancel ptu s for reverse termination Started : ', 1011);
903 end if;
904
905 for l_ptu_index in c1_revterm_ptu
906 loop
907 if g_debug then
908 hr_utility.set_location('Cancel ptu s for contact_id : '||l_ptu_index.person_id
909 ||' Of Type : '||l_ptu_index.system_person_type, 1012);
910 end if;
911 hr_per_type_usage_internal.cancel_person_type_usage
912 ( p_effective_date =>(p_pds_old_atd+1)
913 ,p_person_id => l_ptu_index.person_id
914 ,p_system_person_type => l_ptu_index.system_person_type
915 );
916
917 end loop;
918 if g_debug then
919 hr_utility.set_location('Cancel ptu s for reverse termination Completed Normal: ', 1013);
920 end if;
921
922 end if;
923 exception when others then
924 if g_debug then
925 hr_utility.set_location('Cancel ptu s for reverse termination Completed with Exception: ', 1014);
926 end if;
927 raise;
928 end;
929
930 --
931 -- BUG Fix for WWBUG 1176101.
932 -- If a person is terminated and then unterminated then we have to make
933 -- sure that they only have one period of service if we are going to delete
934 -- the benefit assignment.
935 --
936 open c1;
937 --
938 fetch c1 into l_num_recs;
939 --
940 close c1;
941 --
942 if g_debug then
943 hr_utility.set_location('Number of records:'|| l_num_recs, 10);
944 end if;
945 --
946 if l_num_recs > 1 then
947 --
948 -- Previous period of service exists
949 --
950 l_delete_benass := false;
951 --
952 if g_debug then
953 hr_utility.set_location('Assignment not being deleted', 10);
954 end if;
955 --
956 else
957 --
958 -- Check if what we are updating has been nullified, in other words a
959 -- reverse termination.
960 --
961 if g_debug then
962 hr_utility.set_location('ATD'||p_pds_atd, 10);
963 end if;
964 --
965 if p_pds_atd is null and
966 p_pds_old_atd is not null -- Bug 1854968
967 then
968 --
969 if g_debug then
970 hr_utility.set_location('Assignment being deleted', 10);
971 end if;
972 l_delete_benass := true;
973 --
974 end if;
975 --
976 end if;
977 --
978 if l_delete_benass then
979 --
980 open c2;
981 --
982 fetch c2 into l_c2;
983 --
984 close c2;
985 --
986 -- WWBUG API fix , only delete if benefit assignment exists
987 --
988 if l_c2.assignment_id is not null then
989 --
990 -- We can remove the benefit assignment since a reverse termination
991 -- must have taken place.
992 -- Bug : 3865655 Changed datetrack mode from ZAP to 3865655
993
994 dt_api.Find_DT_Del_Modes
995 (p_effective_date => l_c2.effective_start_date,
996 p_base_table_name => 'PER_ALL_ASSIGNMENTS_F',
997 p_base_key_column => 'ASSIGNMENT_ID',
998 p_base_key_value => l_c2.assignment_id,
999 p_zap => l_zap_mode,
1000 p_delete => l_delete_mode,
1001 p_future_change => l_future_change_mode,
1002 p_delete_next_change => l_delete_next_change_mode);
1003 --
1004 -- The case of p_future_change to be true would not arise, because
1005 -- if there is a third date track assignment record, then deletion of
1006 -- benefit assignment is prevented by the validation due to Cursor C1 above
1007 --
1008 if l_delete_next_change_mode = true then
1009 -- Refer steps to reproduce of bug 3865655 to find the Scenario under which
1010 -- this condition will be true
1011 l_asg_delete_mode := 'DELETE_NEXT_CHANGE';
1012 else
1013 l_asg_delete_mode := 'ZAP';
1014 end if;
1015 --
1016 ben_assignment_api.delete_ben_asg
1017 (p_datetrack_mode => l_asg_delete_mode
1018 ,p_assignment_id => l_c2.assignment_id
1019 ,p_object_version_number => l_c2.object_version_number
1020 ,p_effective_date => l_c2.effective_start_date
1021 ,p_effective_start_date => l_effective_start_date
1022 ,p_effective_end_date => l_effective_end_date);
1023 --
1024 end if;
1025 --
1026 end if;
1027 --
1028 if g_debug then
1029 hr_utility.set_location('Leaving:'|| l_proc, 100);
1030 end if;
1031 end if;
1032 --
1033 end periods_of_service;
1034 --
1035 -- overloaded for per patchset A.
1036 --
1037 procedure bnfts_bal
1038 (p_rowid in VARCHAR2
1039 ,p_business_group_id IN NUMBER
1040 ,p_person_id IN NUMBER
1041 ,p_effective_start_date in DATE
1042 ,p_effective_end_date in DATE
1043 ,p_val IN NUMBER
1044 ,p_bnfts_bal_id in NUMBER
1045 )
1046 is
1047 --
1048 l_proc varchar2(72):=g_package||'bnfts_bal';
1049 --
1050
1051 cursor get_old_pbb(p_rowid in VARCHAR2) is
1052 select PERSON_ID
1053 ,null
1054 ,BUSINESS_GROUP_ID
1055 ,EFFECTIVE_START_DATE
1056 ,EFFECTIVE_END_DATE
1057 ,VAL
1058 ,BNFTS_BAL_ID
1059 from ben_per_bnfts_bal_f
1060 where rowid = p_rowid;
1061 --
1062 l_old_rec ben_pbb_ler.g_pbb_ler_rec;
1063 l_new_rec ben_pbb_ler.g_pbb_ler_rec;
1064 --
1065 begin
1066
1067 --
1068 g_debug := hr_utility.debug_enabled;
1069 if hr_general.g_data_migrator_mode not in ( 'Y','P') then
1070 if g_debug then
1071 hr_utility.set_location('Entering:'|| l_proc, 10);
1072 end if;
1073 -- get old record
1074 open get_old_pbb(p_rowid);
1075 fetch get_old_pbb into l_old_rec;
1076 close get_old_pbb;
1077 -- get new record
1078 l_new_rec.person_id := p_person_id;
1079 l_new_rec.val := p_val;
1080 l_new_rec.business_group_id := p_business_group_id;
1081 l_new_rec.effective_start_date := p_effective_start_date;
1082 l_new_rec.effective_end_date := p_effective_end_date;
1083 l_new_rec.bnfts_bal_id := p_bnfts_bal_id;
1084 --
1085 ben_pbb_ler.ler_chk(l_old_rec,l_new_rec,p_effective_start_date );
1086 if g_debug then
1087 hr_utility.set_location('Leaving:'|| l_proc, 100);
1088 end if;
1089 end if;
1090 --
1091 end bnfts_bal;
1092
1093 procedure bnfts_bal
1094 (p_rowid in VARCHAR2
1095 ,p_per_bnfts_bal_id in NUMBER
1096 ,p_business_group_id IN NUMBER
1097 ,p_person_id IN NUMBER
1098 ,p_effective_start_date in DATE
1099 ,p_effective_end_date in DATE
1100 ,p_val IN NUMBER
1101 ,p_bnfts_bal_id in NUMBER
1102 )
1103 is
1104 --
1105 l_proc varchar2(72):=g_package||'bnfts_bal';
1106 --
1107
1108 cursor get_old_pbb(p_rowid in VARCHAR2) is
1109 select PERSON_ID
1110 ,PER_BNFTS_BAL_ID
1111 ,BUSINESS_GROUP_ID
1112 ,EFFECTIVE_START_DATE
1113 ,EFFECTIVE_END_DATE
1114 ,VAL
1115 ,BNFTS_BAL_ID
1116 from ben_per_bnfts_bal_f
1117 where rowid = p_rowid;
1118 --
1119 l_old_rec ben_pbb_ler.g_pbb_ler_rec;
1120 l_new_rec ben_pbb_ler.g_pbb_ler_rec;
1121 begin
1122 --
1123 g_debug := hr_utility.debug_enabled;
1124 if hr_general.g_data_migrator_mode not in ( 'Y','P') then
1125 if g_debug then
1126 hr_utility.set_location('Entering:'|| l_proc, 10);
1127 end if;
1128 -- get old record
1129 open get_old_pbb(p_rowid);
1130 fetch get_old_pbb into l_old_rec;
1131 close get_old_pbb;
1132 -- get new record
1133 l_new_rec.person_id := p_person_id;
1134 l_new_rec.val := p_val;
1135 l_new_rec.business_group_id := p_business_group_id;
1136 l_new_rec.effective_start_date := p_effective_start_date;
1137 l_new_rec.effective_end_date := p_effective_end_date;
1138 l_new_rec.bnfts_bal_id := p_bnfts_bal_id;
1139 -- 9999 l_new_rec.per_bnfts_bal_id := l_old_rec.per_bnfts_bal_id;
1140 l_new_rec.per_bnfts_bal_id := p_bnfts_bal_id;
1141 --
1142 ben_pbb_ler.ler_chk(l_old_rec,l_new_rec,p_effective_start_date );
1143 if g_debug then
1144 hr_utility.set_location('Leaving:'|| l_proc, 100);
1145 end if;
1146 end if;
1147 --
1148 end bnfts_bal;
1149 --
1150 -- Overloaded for Per patchset A
1151 --
1152 procedure elig_cvrd_dpnt
1153 (p_rowid in VARCHAR2
1154 ,p_business_group_id IN NUMBER
1155 ,p_dpnt_person_id IN NUMBER
1156 ,p_prtt_enrt_rslt_id IN NUMBER
1157 ,p_effective_start_date in DATE
1158 ,p_effective_end_date in DATE
1159 ,p_cvg_strt_dt IN DATE
1160 ,p_cvg_thru_dt in DATE
1161 ,p_ovrdn_flag in VARCHAR2
1162 ,p_ovrdn_thru_dt in DATE
1163 )
1164 is
1165 --
1166 l_proc varchar2(72):=g_package||'elig_cvrd_dpnt';
1167 --
1168
1169 cursor get_old_ecd(p_rowid in VARCHAR2) is
1170 select PRTT_ENRT_RSLT_ID
1171 ,null
1172 ,DPNT_PERSON_ID
1173 ,BUSINESS_GROUP_ID
1174 ,EFFECTIVE_START_DATE
1175 ,EFFECTIVE_END_DATE
1176 ,CVG_STRT_DT
1177 ,CVG_THRU_DT
1178 ,ovrdn_flag
1179 ,ovrdn_thru_dt
1180 from ben_elig_cvrd_dpnt_f
1181 where rowid = p_rowid;
1182 --
1183 l_old_rec ben_ecd_ler.g_ecd_ler_rec;
1184 l_new_rec ben_ecd_ler.g_ecd_ler_rec;
1185 --
1186 begin
1187 --
1188 g_debug := hr_utility.debug_enabled;
1189 if hr_general.g_data_migrator_mode not in ( 'Y','P') then
1190 if g_debug then
1191 hr_utility.set_location('Entering:'|| l_proc, 10);
1192 end if;
1193 -- get old record
1194 open get_old_ecd(p_rowid);
1195 fetch get_old_ecd into l_old_rec;
1196 close get_old_ecd;
1197 -- get new record
1198 l_new_rec.prtt_enrt_rslt_id := p_prtt_enrt_rslt_id;
1199 l_new_rec.business_group_id := p_business_group_id;
1200 l_new_rec.dpnt_person_id := p_dpnt_person_id;
1201 l_new_rec.effective_start_date := p_effective_start_date;
1202 l_new_rec.effective_end_date := p_effective_end_date;
1203 l_new_rec.cvg_strt_dt := p_cvg_strt_dt;
1204 l_new_rec.cvg_thru_dt := p_cvg_thru_dt;
1205 l_new_rec.ovrdn_flag := p_ovrdn_flag;
1206 l_new_rec.ovrdn_thru_dt := p_ovrdn_thru_dt;
1207 --
1208 ben_ecd_ler.ler_chk(l_old_rec,l_new_rec,p_effective_start_date );
1209 if g_debug then
1210 hr_utility.set_location('Leaving:'|| l_proc, 100);
1211 end if;
1212 end if;
1213 --
1214 end elig_cvrd_dpnt;
1215
1216 procedure elig_cvrd_dpnt
1217 (p_rowid in VARCHAR2
1218 ,p_elig_cvrd_dpnt_id in number
1219 ,p_business_group_id IN NUMBER
1220 ,p_dpnt_person_id in NUMBER
1221 ,p_prtt_enrt_rslt_id IN NUMBER
1222 ,p_effective_start_date in DATE
1223 ,p_effective_end_date in DATE
1224 ,p_cvg_strt_dt IN DATE
1225 ,p_cvg_thru_dt in DATE
1226 ,p_ovrdn_flag in VARCHAR2
1227 ,p_ovrdn_thru_dt in DATE
1228 )
1229 is
1230 --
1231 l_proc varchar2(72):=g_package||'elig_cvrd_dpnt';
1232 --
1233
1234 cursor get_old_ecd(p_rowid in VARCHAR2) is
1235 select PRTT_ENRT_RSLT_ID
1236 ,elig_cvrd_dpnt_id
1237 ,dpnt_person_id
1238 ,BUSINESS_GROUP_ID
1239 ,EFFECTIVE_START_DATE
1240 ,EFFECTIVE_END_DATE
1241 ,CVG_STRT_DT
1242 ,CVG_THRU_DT
1243 ,ovrdn_flag
1244 ,ovrdn_thru_dt
1245 from ben_elig_cvrd_dpnt_f
1246 where rowid = p_rowid;
1247 --
1248 l_old_rec ben_ecd_ler.g_ecd_ler_rec;
1249 l_new_rec ben_ecd_ler.g_ecd_ler_rec;
1250 --
1251 begin
1252 --
1253 --
1254 g_debug := hr_utility.debug_enabled;
1255 if hr_general.g_data_migrator_mode not in ( 'Y','P') then
1256 if g_debug then
1257 hr_utility.set_location('Entering:'|| l_proc, 10);
1258 end if;
1259 -- get old record
1260 open get_old_ecd(p_rowid);
1261 fetch get_old_ecd into l_old_rec;
1262 close get_old_ecd;
1263 -- get new record
1264 l_new_rec.prtt_enrt_rslt_id := p_prtt_enrt_rslt_id;
1265 l_new_rec.business_group_id := p_business_group_id;
1266 l_new_rec.dpnt_person_id := p_dpnt_person_id;
1267 l_new_rec.effective_start_date := p_effective_start_date;
1268 l_new_rec.effective_end_date := p_effective_end_date;
1269 l_new_rec.cvg_strt_dt := p_cvg_strt_dt;
1270 l_new_rec.cvg_thru_dt := p_cvg_thru_dt;
1271 l_new_rec.ovrdn_flag := p_ovrdn_flag;
1272 l_new_rec.ovrdn_thru_dt := p_ovrdn_thru_dt;
1273 l_new_rec.elig_cvrd_dpnt_id := p_elig_cvrd_dpnt_id;
1274 -- 9999 l_new_rec.elig_cvrd_dpnt_id := l_old_rec.elig_cvrd_dpnt_id;
1275 --
1276 ben_ecd_ler.ler_chk(l_old_rec,l_new_rec,p_effective_start_date );
1277 if g_debug then
1278 hr_utility.set_location('Leaving:'|| l_proc, 100);
1279 end if;
1280 end if;
1281 --
1282 end elig_cvrd_dpnt;
1283 --
1284 -- Overloaded for per patchset A
1285 --
1286 procedure prtt_enrt_rslt
1287 (p_rowid in VARCHAR2
1288 ,p_business_group_id IN NUMBER
1289 ,p_person_id IN NUMBER
1290 ,p_enrt_cvg_strt_dt in DATE
1291 ,p_enrt_cvg_thru_dt in DATE
1292 ,p_bnft_amt IN NUMBER
1293 ,p_effective_start_date in DATE
1294 ,p_effective_end_date in DATE
1295 )
1296 is
1297 --
1298 l_proc varchar2(72):=g_package||'prtt_enrt_rslt';
1299 --
1300
1301 cursor get_old_pen(p_rowid in VARCHAR2) is
1302 select PERSON_ID
1303 ,null
1304 ,BUSINESS_GROUP_ID
1305 ,ENRT_CVG_STRT_DT
1306 ,ENRT_CVG_THRU_DT
1307 ,BNFT_AMT
1308 ,EFFECTIVE_START_DATE
1309 ,EFFECTIVE_END_DATE
1310 from ben_prtt_enrt_rslt_f
1311 where rowid = p_rowid;
1312 --
1313 l_old_rec ben_pen_ler.g_pen_ler_rec;
1314 l_new_rec ben_pen_ler.g_pen_ler_rec;
1315 --
1316 begin
1317 --
1318 --
1319 g_debug := hr_utility.debug_enabled;
1320 if hr_general.g_data_migrator_mode not in ( 'Y','P') then
1321 if g_debug then
1322 hr_utility.set_location('Entering:'|| l_proc, 10);
1323 end if;
1324 -- get old record
1325 open get_old_pen(p_rowid);
1326 fetch get_old_pen into l_old_rec;
1327 close get_old_pen;
1328 -- get new record
1329 l_new_rec.person_id := p_person_id;
1330 l_new_rec.bnft_amt := p_bnft_amt;
1331 l_new_rec.business_group_id := p_business_group_id;
1332 l_new_rec.enrt_cvg_strt_dt := p_enrt_cvg_strt_dt;
1333 l_new_rec.enrt_cvg_thru_dt := p_enrt_cvg_thru_dt;
1334 l_new_rec.effective_start_date := p_effective_start_date;
1335 l_new_rec.effective_end_date := p_effective_end_date;
1336 --
1337 ben_pen_ler.ler_chk(l_old_rec,l_new_rec,p_effective_start_date);
1338 if g_debug then
1339 hr_utility.set_location('Leaving:'|| l_proc, 100);
1340 end if;
1341 end if;
1342 --
1343 end prtt_enrt_rslt;
1344
1345
1346 procedure prtt_enrt_rslt
1347 (p_rowid in VARCHAR2
1348 ,p_prtt_enrt_rslt_id in number
1349 ,p_business_group_id IN NUMBER
1350 ,p_person_id IN NUMBER
1351 ,p_enrt_cvg_strt_dt in DATE
1352 ,p_enrt_cvg_thru_dt in DATE
1353 ,p_bnft_amt IN NUMBER
1354 ,p_effective_start_date in DATE
1355 ,p_effective_end_date in DATE
1356 )
1357 is
1358 --
1359 l_proc varchar2(72):=g_package||'prtt_enrt_rslt';
1360 --
1361
1362 cursor get_old_pen(p_rowid in VARCHAR2) is
1363 select PERSON_ID
1364 ,prtt_enrt_rslt_id
1365 ,BUSINESS_GROUP_ID
1366 ,ENRT_CVG_STRT_DT
1367 ,ENRT_CVG_THRU_DT
1368 ,BNFT_AMT
1369 ,EFFECTIVE_START_DATE
1370 ,EFFECTIVE_END_DATE
1371 from ben_prtt_enrt_rslt_f
1372 where rowid = p_rowid;
1373 --
1374 l_old_rec ben_pen_ler.g_pen_ler_rec;
1375 l_new_rec ben_pen_ler.g_pen_ler_rec;
1376 --
1377 begin
1378 --
1379 g_debug := hr_utility.debug_enabled;
1380 if hr_general.g_data_migrator_mode not in ( 'Y','P') then
1381 if g_debug then
1382 hr_utility.set_location('Entering:'|| l_proc, 10);
1383 end if;
1384 -- get old record
1385 open get_old_pen(p_rowid);
1386 fetch get_old_pen into l_old_rec;
1387 close get_old_pen;
1388 -- get new record
1389 l_new_rec.person_id := p_person_id;
1390 l_new_rec.bnft_amt := p_bnft_amt;
1391 l_new_rec.business_group_id := p_business_group_id;
1392 l_new_rec.enrt_cvg_strt_dt := p_enrt_cvg_strt_dt;
1393 l_new_rec.enrt_cvg_thru_dt := p_enrt_cvg_thru_dt;
1394 l_new_rec.effective_start_date := p_effective_start_date;
1395 l_new_rec.effective_end_date := p_effective_end_date;
1396 -- 9999 l_new_rec.PRTT_ENRT_RSLT_ID := l_old_rec.PRTT_ENRT_RSLT_ID;
1397 l_new_rec.PRTT_ENRT_RSLT_ID := p_PRTT_ENRT_RSLT_ID;
1398 --
1399 ben_pen_ler.ler_chk(l_old_rec,l_new_rec,p_effective_start_date);
1400 if g_debug then
1401 hr_utility.set_location('Leaving:'|| l_proc, 100);
1402 end if;
1403 end if;
1404 --
1405 end prtt_enrt_rslt;
1406
1407 /******** procedure elem_entry_values
1408 (p_rowid in VARCHAR2
1409 ,p_element_entry_value_id IN NUMBER
1410 ,p_screen_entry_value IN VARCHAR2
1411 )
1412 is
1413 --
1414 l_proc varchar2(72):=g_package||'elem_entry_values';
1415 --
1416
1417 cursor get_old_eev(p_rowid in VARCHAR2) is
1418 select ELEMENT_ENTRY_VALUE_ID
1419 ,SCREEN_ENTRY_VALUE
1420 from pay_element_entry_values_f
1421 where rowid = p_rowid;
1422 --
1423 l_old_rec ben_eev_ler.g_eev_ler_rec;
1424 l_new_rec ben_eev_ler.g_eev_ler_rec;
1425 --
1426 begin
1427 if g_debug then
1428 hr_utility.set_location('Entering:'|| l_proc, 10);
1429 end if;
1430 -- get old record
1431 open get_old_eev(p_rowid);
1432 fetch get_old_eev into l_old_rec;
1433 close get_old_eev;
1434 -- get new record
1435 l_new_rec.element_entry_value_id := p_element_entry_value_id;
1436 l_new_rec.screen_entry_value := p_screen_entry_value;
1437 --
1438 ben_eev_ler.ler_chk(l_old_rec,l_new_rec);
1439 if g_debug then
1440 hr_utility.set_location('Leaving:'|| l_proc, 100);
1441 end if;
1442 end elem_entry_values: ******************/
1443
1444 /*******procedure asgn_budget_values
1445 (p_rowid in VARCHAR2
1446 ,p_assignment_id IN NUMBER
1447 ,p_business_group_id IN NUMBER
1448 ,p_value IN NUMBER
1449 ,p_assignment_budget_value_id IN NUMBER
1450 *** ,p_effective_start_date IN DATE
1451 ,p_effective_end_date IN DATE ***
1452 )
1453 is
1454 --
1455 l_proc varchar2(72):=g_package||'asgn_budget_values';
1456 --
1457
1458 cursor get_old_abv(p_rowid in VARCHAR2) is
1459 select ASSIGNMENT_ID
1460 ,BUSINESS_GROUP_ID
1461 ,VALUE
1462 ,ASSIGNMENT_BUDGET_VALUE_ID
1463 ,EFFECTIVE_START_DATE
1464 ,EFFECTIVE_END_DATE
1465 from per_assignment_budget_values_f
1466 where rowid = p_rowid;
1467 --
1468 l_old_rec ben_abv_ler.g_abv_ler_rec;
1469 l_new_rec ben_abv_ler.g_abv_ler_rec;
1470 --
1471 begin
1472 g_debug := hr_utility.debug_enabled;
1473 if g_debug then
1474 hr_utility.set_location('Entering:'|| l_proc, 10);
1475 end if;
1476 -- get old record
1477 open get_old_abv(p_rowid);
1478 fetch get_old_abv into l_old_rec;
1479 close get_old_abv;
1480 -- get new record
1481 l_new_rec.assignment_id := p_assignment_id;
1482 l_new_rec.business_group_id := p_business_group_id;
1483 l_new_rec.value := p_value;
1484 l_new_rec.assignment_budget_value_id := p_assignment_budget_value_id;
1485 ******** l_new_rec.effective_start_date := p_effective_start_date;
1486 l_new_rec.effective_end_date := p_effective_end_date; ******
1487 --
1488 ben_abv_ler.ler_chk(l_old_rec,l_new_rec);
1489 if g_debug then
1490 hr_utility.set_location('Leaving:'|| l_proc, 100);
1491 end if;
1492 end asgn_budget_values;
1493 ********************************/
1494
1495 end ben_dt_trgr_handle;