[Home] [Help]
PACKAGE BODY: APPS.BEN_ASSIGNMENT_API
Source
1 Package Body ben_assignment_api as
2 /* $Header: beasgapi.pkb 120.1.12000000.1 2007/01/19 00:29:34 appldev ship $ */
3 --
4 -- Package Variables
5 --
6 g_package varchar2(33) := ' ben_assignment_api.';
7 --
8 -- ----------------------------------------------------------------------------
9 -- |----------------------------< check__benasg_allow >----------------------------|
10 -- ----------------------------------------------------------------------------
11 --
12 function check__benasg_allow
13 (p_business_group_id in number )
14 return boolean is
15
16 l_allow boolean := false ;
17
18
19 cursor ben_assign_ok is
20 select substr(hoi.ORG_INFORMATION3,1)
21 from hr_organization_information hoi
22 where hoi.org_information_context = 'Benefits Defaults'
23 and hoi.organization_id = p_business_group_id;
24
25 l_status varchar2(1);
26
27 begin
28 open ben_assign_ok;
29 fetch ben_assign_ok into l_status;
30 if ben_assign_ok%FOUND and l_status = 'Y'
31 then
32 l_allow :=true;
33 end if;
34 close ben_assign_ok;
35 return l_allow ;
36 end check__benasg_allow ;
37
38 -- ----------------------------------------------------------------------------
39 -- |----------------------------< create_ben_asg >----------------------------|
40 -- ----------------------------------------------------------------------------
41 --
42 procedure create_ben_asg
43 (p_validate in boolean default false
44 ,p_event_mode in boolean default false
45 ,p_effective_date in date
46 ,p_person_id in number
47 ,p_organization_id in number
48 ,p_grade_id in number default null
49 ,p_position_id in number default null
50 ,p_job_id in number default null
51 ,p_assignment_status_type_id in number default null
52 ,p_payroll_id in number default null
53 ,p_location_id in number default null
54 ,p_supervisor_id in number default null
55 ,p_special_ceiling_step_id in number default null
56 ,p_people_group_id in number default null
57 ,p_soft_coding_keyflex_id in number default null
58 ,p_pay_basis_id in number default null
59 ,p_change_reason in varchar2 default null
60 ,p_comments in varchar2 default null
61 ,p_date_probation_end in date default null
62 ,p_default_code_comb_id in number default null
63 ,p_employment_category in varchar2 default null
64 ,p_frequency in varchar2 default null
65 ,p_internal_address_line in varchar2 default null
66 ,p_manager_flag in varchar2 default null
67 ,p_normal_hours in number default null
68 ,p_perf_review_period in number default null
69 ,p_perf_review_period_frequency in varchar2 default null
70 ,p_probation_period in number default null
71 ,p_probation_unit in varchar2 default null
72 ,p_sal_review_period in number default null
73 ,p_sal_review_period_frequency in varchar2 default null
74 ,p_set_of_books_id in number default null
75 ,p_source_type in varchar2 default null
76 ,p_time_normal_finish in varchar2 default null
77 ,p_time_normal_start in varchar2 default null
78 ,p_bargaining_unit_code in varchar2 default null
79 ,p_labour_union_member_flag in varchar2 default 'N'
80 ,p_hourly_salaried_code in varchar2 default null
81 ,p_ass_attribute_category in varchar2 default null
82 ,p_ass_attribute1 in varchar2 default null
83 ,p_ass_attribute2 in varchar2 default null
84 ,p_ass_attribute3 in varchar2 default null
85 ,p_ass_attribute4 in varchar2 default null
86 ,p_ass_attribute5 in varchar2 default null
87 ,p_ass_attribute6 in varchar2 default null
88 ,p_ass_attribute7 in varchar2 default null
89 ,p_ass_attribute8 in varchar2 default null
90 ,p_ass_attribute9 in varchar2 default null
91 ,p_ass_attribute10 in varchar2 default null
92 ,p_ass_attribute11 in varchar2 default null
93 ,p_ass_attribute12 in varchar2 default null
94 ,p_ass_attribute13 in varchar2 default null
95 ,p_ass_attribute14 in varchar2 default null
96 ,p_ass_attribute15 in varchar2 default null
97 ,p_ass_attribute16 in varchar2 default null
98 ,p_ass_attribute17 in varchar2 default null
99 ,p_ass_attribute18 in varchar2 default null
100 ,p_ass_attribute19 in varchar2 default null
101 ,p_ass_attribute20 in varchar2 default null
102 ,p_ass_attribute21 in varchar2 default null
103 ,p_ass_attribute22 in varchar2 default null
104 ,p_ass_attribute23 in varchar2 default null
105 ,p_ass_attribute24 in varchar2 default null
106 ,p_ass_attribute25 in varchar2 default null
107 ,p_ass_attribute26 in varchar2 default null
108 ,p_ass_attribute27 in varchar2 default null
109 ,p_ass_attribute28 in varchar2 default null
110 ,p_ass_attribute29 in varchar2 default null
111 ,p_ass_attribute30 in varchar2 default null
112 ,p_title in varchar2 default null
113 ,p_age in number default null
114 ,p_adjusted_service_date in date default null
115 ,p_original_hire_date in date default null
116 ,p_salary in varchar2 default null
117 ,p_original_person_type in varchar2 default null
118 ,p_termination_date in date default null
119 ,p_termination_reason in varchar2 default null
120 ,p_leave_of_absence_date in date default null
121 ,p_absence_type in varchar2 default null
122 ,p_absence_reason in varchar2 default null
123 ,p_date_of_hire in date default null
124 --
125 ,p_called_from in varchar2 default null
126 --
127 ,p_assignment_id out nocopy number
128 ,p_object_version_number out nocopy number
129 ,p_effective_start_date out nocopy date
130 ,p_effective_end_date out nocopy date
131 ,p_assignment_extra_info_id out nocopy number
132 ,p_aei_object_version_number out nocopy number
133 )
134 is
135 --
136 -- Declare cursors and local variables
137 --
138 l_proc varchar2(72) := g_package||'create_ben_asg';
139 --
140 l_effective_date date;
141 l_date_probation_end date;
142 l_assignment_number per_all_assignments_f.assignment_number%TYPE;
143 l_business_group_id number;
144 l_legislation_code per_business_groups.legislation_code%TYPE;
145 l_assignment_id number;
146 l_object_version_number number;
147 l_age varchar2(100);
148 l_adj_serv_date varchar2(100);
149 l_orig_hire_date varchar2(100);
150 l_aei_payroll_changed varchar2(100);
151 l_salary varchar2(100);
152 l_date_of_hire varchar2(100);
153 l_date_dummy1 date;
154 l_date_dummy2 date;
155 l_number_dummy1 number;
156 l_number_dummy2 number;
157 l_default_code_comb_id number;
158 l_location_id number;
159 l_supervisor_id number;
160 l_assignment_status_type_id number;
161 l_boolean_dummy1 boolean;
162 l_boolean_dummy2 boolean;
163 l_aei_id number;
164 l_aei_ovn number;
165 l_pay_period_type varchar2(100);
166 l_default_payroll boolean;
167 l_mthpayroll_id number := null;
168 l_origpayroll_id number;
169 l_asg_esd date;
170 l_asg_eed date;
171 l_v2exists varchar2(1);
172 l_dummy varchar2(1);
173 --
174 cursor csr_get_derived_details
175 (c_person_id in number
176 ,c_eff_date in date
177 )
178 is
179 select per.business_group_id
180 from per_all_people_f per
181 where per.person_id = c_person_id
182 and c_eff_date between per.effective_start_date
183 and per.effective_end_date;
184 --
185 cursor csr_getactempasg
186 (c_person_id in number
187 ,c_eff_date in date
188 )
189 is
190 select null
191 from per_all_assignments_f asg,
192 per_assignment_status_types ast
193 where asg.person_id = c_person_id
194 and asg.assignment_type <> 'C'
195 and asg.assignment_status_type_id = ast.assignment_status_type_id
196 and c_eff_date between asg.effective_start_date
197 and asg.effective_end_date
198 and ast.PER_SYSTEM_STATUS = 'ACTIVE_ASSIGN'
199 and asg.primary_flag = 'Y';
200 --
201 cursor c_getpaydtinsdets
202 (c_payroll_id in number
203 ,c_eff_date in date
204 )
205 Is
206 select pay.period_type
207 from pay_all_payrolls_f pay
208 where pay.payroll_id = c_payroll_id
209 and c_eff_date
210 between pay.effective_start_date and pay.effective_end_date;
211 --
212 cursor c_getdefmthpaydets
213 (c_bgp_id in number
214 )
215 Is
216 select to_number(ori.ORG_INFORMATION2)
217 from hr_organization_information ori
218 where ori.organization_id = c_bgp_id
219 and ori.ORG_INFORMATION_CONTEXT = 'Benefits Defaults';
220 --
221 -- Begin of bug 1919015
222 --
223 cursor c_default_code_comb_id is
224 select null
225 from gl_code_combinations
226 where code_combination_id = l_default_code_comb_id
227 and enabled_flag = 'Y'
228 and l_effective_date
229 between nvl(start_date_active,l_effective_date)
230 and nvl(end_date_active,l_effective_date);
231 --
232 -- End of bug 1919015
233 --
234 --
235 -- Begin of bug 1925131
236 --
237 cursor c_location is
238 select null
239 from hr_locations_all
240 where location_id = l_location_id
241 and l_effective_date <= nvl(inactive_date,l_effective_date);
242 --
243 cursor c_supervisor is
244 select null
245 from per_all_people_f
246 where person_id = l_supervisor_id
247 and current_employee_flag = 'Y'
248 and l_effective_date
249 between effective_start_date
250 and effective_end_date;
251 --
252 cursor c_assignment_status_type_id is
253 select null
254 from per_assignment_status_types
255 where assignment_status_type_id = p_assignment_status_type_id
256 and active_flag = 'Y';
257 --
258 -- End of bug 1925131
259 --
260 l_entries_changed varchar2(200) := null;
261 begin
262 hr_utility.set_location('Entering:'|| l_proc, 5);
263 --
264 -- Issue a savepoint.
265 --
266 -- Truncate the parameter p_effective_date and p_date_probation_end
267 -- into local variables
268 --
269 l_effective_date := trunc(p_effective_date);
270 l_date_probation_end := trunc(p_date_probation_end);
271 --
272 savepoint create_ben_asg;
273 --
274 begin
275 null;
276 exception
277 when hr_api.cannot_find_prog_unit then
278 hr_api.cannot_find_prog_unit_error
279 (p_module_name => 'CREATE_SECONDARY_EMP_ASG'
280 ,p_hook_type => 'BP'
281 );
282 --
283 -- End of API User Hook for the before hook of create_secondary_emp_asg
284 --
285 end;
286 --
287 hr_utility.set_location(l_proc, 10);
288 --
289 -- Validation in addition to Table Handlers
290 --
291 -- Get person details.
292 --
293 hr_api.mandatory_arg_error
294 (p_api_name => l_proc
295 ,p_argument => 'person_id'
296 ,p_argument_value => p_person_id
297 );
298 --
299 hr_api.mandatory_arg_error
300 (p_api_name => l_proc
301 ,p_argument => 'effective_date'
302 ,p_argument_value => l_effective_date
303 );
304 --
305 -- Record the value of in out parameters
306 --
307 open csr_get_derived_details
308 (c_person_id => p_person_id
309 ,c_eff_date => l_effective_date
310 );
311 fetch csr_get_derived_details into l_business_group_id;
312 --
313 if csr_get_derived_details%NOTFOUND then
314 --
315 close csr_get_derived_details;
316 --
317 hr_utility.set_message(801,'HR_7432_ASG_INVALID_PERSON');
318 hr_utility.raise_error;
319 --
320 end if;
321 close csr_get_derived_details;
322 --- check for whther the Business group allows
323 --- creation of benefits assignments
324 ---
325 if not check__benasg_allow(l_business_group_id) then
326 hr_utility.set_location('Leaving:'|| l_proc, 999);
327 return ;
331 --
328 end if ;
329 hr_utility.set_location(l_proc, 20);
330
332 -- Remove this edit as it is not relevant for reduction of hours.
333 --
334 -- Check if an existing employee assignment with a status of
335 -- 'ACTIVE_ASSIGN' exists
336 --
337 /* if not p_event_mode then
338 --
339 -- Get existing 'ACTIVE_ASSIGN' employee assignments for the person
340 --
341 open csr_getactempasg
342 (c_person_id => p_person_id
343 ,c_eff_date => l_effective_date
344 );
345 fetch csr_getactempasg into l_v2exists;
346 if csr_getactempasg%FOUND then
347 close csr_getactempasg;
348 --
349 -- A benefit assignment cannot be created for a person with an
350 -- active employee assignment
351 --
352 hr_utility.set_message(805,'BEN_92114_ACTEMPASGEXISTS');
353 hr_utility.raise_error;
354 --
355 end if;
356 close csr_getactempasg;
357 hr_utility.set_location(l_proc, 25);
358 --
359 end if; */
360 --
361 -- Default the payroll from the business group
362 --
363 hr_utility.set_location(l_proc, 35);
364 --
365 -- Get the default payroll
366 --
367 open c_getdefmthpaydets
368 (c_bgp_id => l_business_group_id
369 );
370 --
371 fetch c_getdefmthpaydets into l_mthpayroll_id;
372 if hr_api.return_legislation_code(l_business_group_id) in ('US','CA') and
373 (c_getdefmthpaydets%notfound or l_mthpayroll_id is null) then --Bug 1539414
374 close c_getdefmthpaydets;
375 --
376 hr_utility.set_message(805,'BEN_92109_NODEFPAYEXISTS');
377 hr_utility.raise_error;
378 --
379 end if;
380 close c_getdefmthpaydets;
381
382 hr_utility.set_location(l_proc, 45);
383 hr_utility.set_location('p_payroll_id: '||p_payroll_id||' '||l_proc, 45);
384 hr_utility.set_location('l_mthpayroll_id: '||l_mthpayroll_id||' '||l_proc, 45);
385 --
386 -- Check if existing payroll is set
387 --
388 if p_payroll_id is not null then
389 if p_payroll_id <> l_mthpayroll_id then
390 --
391 l_aei_payroll_changed := 'Y';
392 l_origpayroll_id := p_payroll_id;
393 --
394 else
395 l_aei_payroll_changed := 'N';
396 l_origpayroll_id := p_payroll_id;
397 --
398 end if;
399
400 else
401 --
402 -- Do not set payroll changed flag when payroll was null
403 --
404 l_aei_payroll_changed := 'N';
405 l_origpayroll_id := null;
406 --
407 end if;
408 --
409 -- Begin of bug 1919015
410 --
411 l_default_code_comb_id := p_default_code_comb_id;
412 --
413 open c_default_code_comb_id;
414 --
415 fetch c_default_code_comb_id into l_dummy;
416 --
417 if c_default_code_comb_id%notfound then
418 --
419 l_default_code_comb_id := null;
420 --
421 end if;
422 --
423 close c_default_code_comb_id;
424 --
425 -- End of bug 1919015
426 --
427 --
428 -- Begin of bug 1925131
429 --
430 l_location_id := p_location_id;
431 --
432 open c_location;
433 --
434 fetch c_location into l_dummy;
435 --
436 if c_location%notfound then
437 --
438 l_location_id := null;
439 --
440 end if;
441 --
442 close c_location;
443 --
444 l_supervisor_id := p_supervisor_id;
445 --
446 open c_supervisor;
447 --
448 fetch c_supervisor into l_dummy;
449 --
450 if c_supervisor%notfound then
451 --
452 l_supervisor_id := null;
453 --
454 end if;
455 --
456 close c_supervisor;
457 --
458 l_assignment_status_type_id := p_assignment_status_type_id;
459 --
460 open c_assignment_status_type_id;
461 --
462 fetch c_assignment_status_type_id into l_dummy;
463 --
464 if c_assignment_status_type_id%notfound then
465 --
466 -- Use an assignment status type that can't be disabled.
467 -- In other words take the default active assignment status.
468 --
469 select assignment_status_type_id
470 into l_assignment_status_type_id
471 from per_assignment_status_types
472 where per_system_status = 'ACTIVE_ASSIGN'
473 and default_flag = 'Y'
474 and business_group_id is null;
475 --
476 end if;
477 --
478 close c_assignment_status_type_id;
479 --
480 -- End of bug 1925131
481 --
482 --
483 -- Create the benefits assignment
484 --
485 l_assignment_number := null;
486 --
487 hr_utility.set_location(l_proc, 40);
488
489 --
490 -- Call per_asg_ins.ins when p_called_from = 'FORM', (called from BENEBNAS.pld)
491 -- to create benefits assignment with HR api validations.
492 --
493 -- Call ben_asg_ins.ins when p_called_from is NULL, to create benefits assignment
497 if p_called_from = 'FORM' then
494 -- without HR api validations
495 --
496
498 per_asg_ins.ins
499 (p_business_group_id => l_business_group_id
500 ,p_effective_date => p_effective_date
501 ,p_assignment_status_type_id => l_assignment_status_type_id
502 ,p_person_id => p_person_id
503 ,p_organization_id => p_organization_id
504 ,p_period_of_service_id => null
505 ,p_assignment_type => 'B'
506 ,p_primary_flag => 'Y'
507 ,p_assignment_number => l_assignment_number
508 --
509 ,p_grade_id => p_grade_id
510 ,p_position_id => p_position_id
511 ,p_job_id => p_job_id
512 ,p_payroll_id => nvl(p_payroll_id, l_mthpayroll_id)
513 ,p_location_id => l_location_id
514 ,p_supervisor_id => l_supervisor_id
515 ,p_special_ceiling_step_id => p_special_ceiling_step_id
516 ,p_people_group_id => p_people_group_id
517 ,p_soft_coding_keyflex_id => p_soft_coding_keyflex_id
518 ,p_pay_basis_id => p_pay_basis_id
519 ,p_change_reason => p_change_reason
520 ,p_date_probation_end => p_date_probation_end
521 ,p_default_code_comb_id => l_default_code_comb_id
522 ,p_employment_category => p_employment_category
523 ,p_frequency => p_frequency
524 ,p_internal_address_line => p_internal_address_line
525 ,p_manager_flag => p_manager_flag
526 ,p_normal_hours => p_normal_hours
527 ,p_perf_review_period => p_perf_review_period
528 ,p_perf_review_period_frequency => p_perf_review_period_frequency
529 ,p_probation_period => p_probation_period
530 ,p_probation_unit => p_probation_unit
531 ,p_sal_review_period => p_sal_review_period
532 ,p_sal_review_period_frequency => p_sal_review_period_frequency
533 ,p_set_of_books_id => p_set_of_books_id
534 ,p_source_type => p_source_type
535 ,p_time_normal_finish => p_time_normal_finish
536 ,p_time_normal_start => p_time_normal_start
537 ,p_bargaining_unit_code => p_bargaining_unit_code
538 ,p_labour_union_member_flag => p_labour_union_member_flag
539 ,p_hourly_salaried_code => p_hourly_salaried_code
540 ,p_ass_attribute_category => p_ass_attribute_category
541 ,p_ass_attribute1 => p_ass_attribute1
542 ,p_ass_attribute2 => p_ass_attribute2
543 ,p_ass_attribute3 => p_ass_attribute3
544 ,p_ass_attribute4 => p_ass_attribute4
545 ,p_ass_attribute5 => p_ass_attribute5
546 ,p_ass_attribute6 => p_ass_attribute6
547 ,p_ass_attribute7 => p_ass_attribute7
548 ,p_ass_attribute8 => p_ass_attribute8
549 ,p_ass_attribute9 => p_ass_attribute9
550 ,p_ass_attribute10 => p_ass_attribute10
551 ,p_ass_attribute11 => p_ass_attribute11
552 ,p_ass_attribute12 => p_ass_attribute12
553 ,p_ass_attribute13 => p_ass_attribute13
554 ,p_ass_attribute14 => p_ass_attribute14
555 ,p_ass_attribute15 => p_ass_attribute15
556 ,p_ass_attribute16 => p_ass_attribute16
557 ,p_ass_attribute17 => p_ass_attribute17
558 ,p_ass_attribute18 => p_ass_attribute18
559 ,p_ass_attribute19 => p_ass_attribute19
560 ,p_ass_attribute20 => p_ass_attribute20
561 ,p_ass_attribute21 => p_ass_attribute21
562 ,p_ass_attribute22 => p_ass_attribute22
563 ,p_ass_attribute23 => p_ass_attribute23
564 ,p_ass_attribute24 => p_ass_attribute24
565 ,p_ass_attribute25 => p_ass_attribute25
566 ,p_ass_attribute26 => p_ass_attribute26
567 ,p_ass_attribute27 => p_ass_attribute27
568 ,p_ass_attribute28 => p_ass_attribute28
569 ,p_ass_attribute29 => p_ass_attribute29
570 ,p_ass_attribute30 => p_ass_attribute30
571 ,p_title => p_title
572 ,p_validate => FALSE
573 --
574 ,p_assignment_id => l_assignment_id
575 ,p_effective_start_date => l_asg_esd
576 ,p_effective_end_date => l_asg_eed
577 ,p_assignment_sequence => l_number_dummy1
578 ,p_comment_id => l_number_dummy2
579 ,p_other_manager_warning => l_boolean_dummy1
580 ,p_object_version_number => l_object_version_number
581 ,p_hourly_salaried_warning => l_boolean_dummy2
582 );
583 else
584 ben_asg_ins.ins
585 (p_business_group_id => l_business_group_id
586 ,p_effective_date => p_effective_date
587 ,p_assignment_status_type_id => l_assignment_status_type_id
588 ,p_person_id => p_person_id
589 ,p_organization_id => p_organization_id
590 ,p_period_of_service_id => null
591 ,p_assignment_type => 'B'
592 ,p_primary_flag => 'Y'
596 ,p_position_id => p_position_id
593 ,p_assignment_number => l_assignment_number
594 --
595 ,p_grade_id => p_grade_id
597 ,p_job_id => p_job_id
598 ,p_payroll_id => l_mthpayroll_id
599 ,p_location_id => p_location_id
600 ,p_supervisor_id => p_supervisor_id
601 ,p_special_ceiling_step_id => p_special_ceiling_step_id
602 ,p_people_group_id => p_people_group_id
603 ,p_soft_coding_keyflex_id => p_soft_coding_keyflex_id
604 ,p_pay_basis_id => p_pay_basis_id
605 ,p_change_reason => p_change_reason
606 ,p_date_probation_end => p_date_probation_end
607 ,p_default_code_comb_id => p_default_code_comb_id
608 ,p_employment_category => p_employment_category
609 ,p_frequency => p_frequency
610 ,p_internal_address_line => p_internal_address_line
611 ,p_manager_flag => p_manager_flag
612 ,p_normal_hours => p_normal_hours
613 ,p_perf_review_period => p_perf_review_period
614 ,p_perf_review_period_frequency => p_perf_review_period_frequency
615 ,p_probation_period => p_probation_period
616 ,p_probation_unit => p_probation_unit
617 ,p_sal_review_period => p_sal_review_period
618 ,p_sal_review_period_frequency => p_sal_review_period_frequency
619 ,p_set_of_books_id => p_set_of_books_id
620 ,p_source_type => p_source_type
621 ,p_time_normal_finish => p_time_normal_finish
622 ,p_time_normal_start => p_time_normal_start
623 ,p_bargaining_unit_code => p_bargaining_unit_code
624 ,p_labour_union_member_flag => p_labour_union_member_flag
625 ,p_hourly_salaried_code => p_hourly_salaried_code
626 ,p_ass_attribute_category => p_ass_attribute_category
627 ,p_ass_attribute1 => p_ass_attribute1
628 ,p_ass_attribute2 => p_ass_attribute2
629 ,p_ass_attribute3 => p_ass_attribute3
630 ,p_ass_attribute4 => p_ass_attribute4
631 ,p_ass_attribute5 => p_ass_attribute5
632 ,p_ass_attribute6 => p_ass_attribute6
633 ,p_ass_attribute7 => p_ass_attribute7
634 ,p_ass_attribute8 => p_ass_attribute8
635 ,p_ass_attribute9 => p_ass_attribute9
636 ,p_ass_attribute10 => p_ass_attribute10
637 ,p_ass_attribute11 => p_ass_attribute11
638 ,p_ass_attribute12 => p_ass_attribute12
639 ,p_ass_attribute13 => p_ass_attribute13
640 ,p_ass_attribute14 => p_ass_attribute14
641 ,p_ass_attribute15 => p_ass_attribute15
642 ,p_ass_attribute16 => p_ass_attribute16
643 ,p_ass_attribute17 => p_ass_attribute17
644 ,p_ass_attribute18 => p_ass_attribute18
645 ,p_ass_attribute19 => p_ass_attribute19
646 ,p_ass_attribute20 => p_ass_attribute20
647 ,p_ass_attribute21 => p_ass_attribute21
648 ,p_ass_attribute22 => p_ass_attribute22
649 ,p_ass_attribute23 => p_ass_attribute23
650 ,p_ass_attribute24 => p_ass_attribute24
651 ,p_ass_attribute25 => p_ass_attribute25
652 ,p_ass_attribute26 => p_ass_attribute26
653 ,p_ass_attribute27 => p_ass_attribute27
654 ,p_ass_attribute28 => p_ass_attribute28
655 ,p_ass_attribute29 => p_ass_attribute29
656 ,p_ass_attribute30 => p_ass_attribute30
657 ,p_title => p_title
658 ,p_validate => FALSE
659 --
660 ,p_assignment_id => l_assignment_id
661 ,p_effective_start_date => l_asg_esd
662 ,p_effective_end_date => l_asg_eed
663 ,p_assignment_sequence => l_number_dummy1
664 ,p_comment_id => l_number_dummy2
665 ,p_other_manager_warning => l_boolean_dummy1
666 ,p_object_version_number => l_object_version_number
667 ,p_hourly_salaried_warning => l_boolean_dummy2
668 );
669 end if;
670 hr_utility.set_location(l_proc, 50);
671 --
672 -- Bug 5355232
673 --
674 hr_utility.set_location('ACE : Before : hrentmnt.adjust_entries_asg_criteria', 9999);
675 --
676 begin
677 --
678 hrentmnt.maintain_entries_asg
679 (
680 p_assignment_id => l_assignment_id,
681 p_old_payroll_id => null,
682 p_new_payroll_id => l_mthpayroll_id,
683 p_business_group_id => l_business_group_id,
684 p_operation => 'ASG_CRITERIA',
685 p_actual_term_date => null,
686 p_last_standard_date => null,
687 p_final_process_date => null,
688 p_dt_mode => 'INSERT',
689 p_validation_start_date => l_asg_esd,
690 p_validation_end_date => l_asg_eed,
691 p_entries_changed => l_entries_changed,
692 p_old_hire_date => null,
693 p_old_people_group_id => null,
697 exception
694 p_new_people_group_id => p_people_group_id
695 );
696 --
698 --
699 when others then
700 --
701 hr_utility.set_location('EXC : ' || substr(SQLERRM, 1, 50), 9999);
702 hr_utility.set_location('EXC : ' || substr(SQLERRM, 51, 100), 9999);
703 hr_utility.set_location('EXC : ' || substr(SQLERRM, 101, 150), 9999);
704 hr_utility.set_location('EXC : ' || substr(SQLERRM, 151, 200), 9999);
705 --
706 raise;
707 --
708 end;
709 --
710 hr_utility.set_location('ACE : After : hrentmnt.adjust_entries_asg_criteria', 9999);
711 --
712 --
713 -- Bug 5355232
714 --
715 --
716 -- Create the assignment extra info for the assignment
717 --
718 hr_assignment_extra_info_api.create_assignment_extra_info
719 (p_assignment_id => l_assignment_id
720 ,p_information_type => 'BEN_DERIVED'
721 --
722 ,p_aei_information_category => 'BEN_DERIVED'
723 ,p_aei_information1 => p_age
724 ,p_aei_information2 => fnd_date.date_to_canonical(p_adjusted_service_date)
725 ,p_aei_information3 => fnd_date.date_to_canonical(p_original_hire_date)
726 ,p_aei_information4 => l_aei_payroll_changed
727 ,p_aei_information5 => l_origpayroll_id
728 ,p_aei_information6 => p_salary
729 ,p_aei_information7 => p_original_person_type
730 ,p_aei_information8 => fnd_date.date_to_canonical(p_termination_date)
731 ,p_aei_information9 => p_termination_reason
732 ,p_aei_information10 => fnd_date.date_to_canonical(p_leave_of_absence_date)
733 ,p_aei_information11 => p_absence_type
734 ,p_aei_information12 => p_absence_reason
735 ,p_aei_information13 => fnd_date.date_to_canonical(p_date_of_hire)
736 --
737 ,p_assignment_extra_info_id => l_aei_id
738 ,p_object_version_number => l_aei_ovn
739 );
740 hr_utility.set_location(l_proc, 60);
741 begin
742 null;
743 exception
744 when hr_api.cannot_find_prog_unit then
745 hr_api.cannot_find_prog_unit_error
746 (p_module_name => 'CREATE_SECONDARY_EMP_ASG'
747 ,p_hook_type => 'AP'
748 );
749 --
750 -- End of API User Hook for the after hook of create_secondary_emp_asg
751 --
752 end;
753 --
754 -- When in validation only mode raise the Validate_Enabled exception
755 --
756 if p_validate then
757 raise hr_api.validate_enabled;
758 end if;
759 --
760 -- Set remaining output arguments
761 --
762 p_assignment_id := l_assignment_id;
763 p_object_version_number := l_object_version_number;
764 p_effective_start_date := l_asg_esd;
765 p_effective_end_date := l_asg_eed;
766 p_assignment_extra_info_id := l_aei_id;
767 p_aei_object_version_number := l_aei_ovn;
768 --
769 hr_utility.set_location(' Leaving:'||l_proc, 50);
770 exception
771 when hr_api.validate_enabled then
772 --
773 -- As the Validate_Enabled exception has been raised
774 -- we must rollback to the savepoint
775 --
776 ROLLBACK TO create_ben_asg;
777 --
778 -- Only set output warning arguments
779 -- (Any key or derived arguments must be set to null
780 -- when validation only mode is being used.)
781 --
782 p_assignment_id := null;
783 p_object_version_number := null;
784 p_effective_start_date := null;
785 p_effective_end_date := null;
786 p_assignment_extra_info_id := null;
787 p_aei_object_version_number := null;
788 --
789 when others then
790 --
791 -- A validation or unexpected error has occurred
792 --
793 -- Added as part of fix to bug 632479
794 --
795 ROLLBACK TO create_ben_asg;
796 raise;
797 --
798 end create_ben_asg;
799 --
800 -- ----------------------------------------------------------------------------
801 -- |--------------------------< update_ben_asg >------------------------------|
802 -- ----------------------------------------------------------------------------
803 --
804 procedure update_ben_asg
805 (p_validate in boolean default false
806 ,p_effective_date in date
807 ,p_datetrack_update_mode in varchar2
808 ,p_assignment_id in number
809 ,p_object_version_number in out nocopy number
810 --
811 ,p_grade_id in number default hr_api.g_number
812 ,p_position_id in number default hr_api.g_number
813 ,p_job_id in number default hr_api.g_number
814 ,p_payroll_id in number default hr_api.g_number
815 ,p_location_id in number default hr_api.g_number
816 ,p_special_ceiling_step_id in out nocopy number
817 ,p_organization_id in number default hr_api.g_number
818 ,p_people_group_id in number default hr_api.g_number
819 ,p_pay_basis_id in number default hr_api.g_number
820 ,p_employment_category in varchar2 default hr_api.g_varchar2
821 --
825 ,p_date_probation_end in date default hr_api.g_date
822 ,p_supervisor_id in number default hr_api.g_number
823 ,p_change_reason in varchar2 default hr_api.g_varchar2
824 ,p_comments in varchar2 default hr_api.g_varchar2
826 ,p_default_code_comb_id in number default hr_api.g_number
827 ,p_frequency in varchar2 default hr_api.g_varchar2
828 ,p_internal_address_line in varchar2 default hr_api.g_varchar2
829 ,p_manager_flag in varchar2 default hr_api.g_varchar2
830 ,p_normal_hours in number default hr_api.g_number
831 ,p_perf_review_period in number default hr_api.g_number
832 ,p_perf_review_period_frequency in varchar2 default hr_api.g_varchar2
833 ,p_probation_period in number default hr_api.g_number
834 ,p_probation_unit in varchar2 default hr_api.g_varchar2
835 ,p_sal_review_period in number default hr_api.g_number
836 ,p_sal_review_period_frequency in varchar2 default hr_api.g_varchar2
837 ,p_set_of_books_id in number default hr_api.g_number
838 ,p_source_type in varchar2 default hr_api.g_varchar2
839 ,p_time_normal_finish in varchar2 default hr_api.g_varchar2
840 ,p_time_normal_start in varchar2 default hr_api.g_varchar2
841 ,p_bargaining_unit_code in varchar2 default hr_api.g_varchar2
842 ,p_labour_union_member_flag in varchar2 default hr_api.g_varchar2
843 ,p_hourly_salaried_code in varchar2 default hr_api.g_varchar2
844 ,p_ass_attribute_category in varchar2 default hr_api.g_varchar2
845 ,p_ass_attribute1 in varchar2 default hr_api.g_varchar2
846 ,p_ass_attribute2 in varchar2 default hr_api.g_varchar2
847 ,p_ass_attribute3 in varchar2 default hr_api.g_varchar2
848 ,p_ass_attribute4 in varchar2 default hr_api.g_varchar2
849 ,p_ass_attribute5 in varchar2 default hr_api.g_varchar2
850 ,p_ass_attribute6 in varchar2 default hr_api.g_varchar2
851 ,p_ass_attribute7 in varchar2 default hr_api.g_varchar2
852 ,p_ass_attribute8 in varchar2 default hr_api.g_varchar2
853 ,p_ass_attribute9 in varchar2 default hr_api.g_varchar2
854 ,p_ass_attribute10 in varchar2 default hr_api.g_varchar2
855 ,p_ass_attribute11 in varchar2 default hr_api.g_varchar2
856 ,p_ass_attribute12 in varchar2 default hr_api.g_varchar2
857 ,p_ass_attribute13 in varchar2 default hr_api.g_varchar2
858 ,p_ass_attribute14 in varchar2 default hr_api.g_varchar2
859 ,p_ass_attribute15 in varchar2 default hr_api.g_varchar2
860 ,p_ass_attribute16 in varchar2 default hr_api.g_varchar2
861 ,p_ass_attribute17 in varchar2 default hr_api.g_varchar2
862 ,p_ass_attribute18 in varchar2 default hr_api.g_varchar2
863 ,p_ass_attribute19 in varchar2 default hr_api.g_varchar2
864 ,p_ass_attribute20 in varchar2 default hr_api.g_varchar2
865 ,p_ass_attribute21 in varchar2 default hr_api.g_varchar2
866 ,p_ass_attribute22 in varchar2 default hr_api.g_varchar2
867 ,p_ass_attribute23 in varchar2 default hr_api.g_varchar2
868 ,p_ass_attribute24 in varchar2 default hr_api.g_varchar2
869 ,p_ass_attribute25 in varchar2 default hr_api.g_varchar2
870 ,p_ass_attribute26 in varchar2 default hr_api.g_varchar2
871 ,p_ass_attribute27 in varchar2 default hr_api.g_varchar2
872 ,p_ass_attribute28 in varchar2 default hr_api.g_varchar2
873 ,p_ass_attribute29 in varchar2 default hr_api.g_varchar2
874 ,p_ass_attribute30 in varchar2 default hr_api.g_varchar2
875 ,p_title in varchar2 default hr_api.g_varchar2
876 ,p_age in number default hr_api.g_number
877 ,p_adjusted_service_date in date default hr_api.g_date
878 ,p_original_hire_date in date default hr_api.g_date
879 ,p_salary in varchar2 default hr_api.g_varchar2
880 ,p_original_person_type in varchar2 default hr_api.g_varchar2
881 ,p_termination_date in date default hr_api.g_date
882 ,p_termination_reason in varchar2 default hr_api.g_varchar2
883 ,p_leave_of_absence_date in date default hr_api.g_date
884 ,p_absence_type in varchar2 default hr_api.g_varchar2
885 ,p_absence_reason in varchar2 default hr_api.g_varchar2
886 ,p_date_of_hire in date default hr_api.g_date
887 --
888 ,p_called_from in varchar2 default hr_api.g_varchar2
889 --
890 ,p_effective_start_date out nocopy date
891 ,p_effective_end_date out nocopy date
892 )
893 is
894 --
895 -- Declare cursors and local variables
896 --
897 l_proc varchar2(72) := g_package||'update_ben_asg';
898 -- Out variables
899 --
900 l_comment_id per_all_assignments_f.comment_id%TYPE;
901 l_effective_start_date per_all_assignments_f.effective_start_date%TYPE;
902 l_effective_end_date per_all_assignments_f.effective_end_date%TYPE;
903 l_object_version_number per_all_assignments_f.object_version_number%TYPE;
904 l_no_managers_warning boolean;
908 l_effective_date date;
905 l_other_manager_warning boolean;
906 l_soft_coding_keyflex_id per_all_assignments_f.soft_coding_keyflex_id%TYPE;
907 l_concatenated_segments hr_soft_coding_keyflex.concatenated_segments%TYPE;
909 l_date_probation_end per_all_assignments_f.date_probation_end%TYPE;
910 --
911 -- Internal working variables
912 --
913 l_assignment_type per_all_assignments_f.assignment_type%TYPE;
914 l_business_group_id per_business_groups.business_group_id%TYPE;
915 l_person_id number;
916 l_assignment_extra_info_id number;
917 l_assignment_extra_info_ovn number;
918 l_payroll_id_updated boolean;
919 l_people_group_id per_all_assignments_f.people_group_id%TYPE;
920 l_org_now_no_manager_warning boolean;
921 l_validation_start_date per_all_assignments_f.effective_start_date%TYPE;
922 l_validation_end_date per_all_assignments_f.effective_end_date%TYPE;
923 l_entries_changed varchar2(100);
924 l_age number;
925 l_adj_serv_date date;
926 l_orig_hire_date date;
927 l_payroll_changed varchar2(100);
928 l_orig_payroll_id varchar2(100);
929 l_salary varchar2(100);
930 l_date_of_hire date;
931 l_payroll_id number;
932 l_aei_origpayroll_id number;
933 l_char_age varchar2(100);
934 l_char_adjusted_service_date varchar2(100);
935 l_char_original_hire_date varchar2(100);
936 l_char_termination_date varchar2(100);
937 l_char_leave_of_absence_date varchar2(100);
938 l_char_date_of_hire varchar2(100);
939 l_boolean_dummy2 boolean;
940 --
941 cursor csr_get_asg_dets
942 (c_assignment_id in number
943 ,c_eff_date in date
944 )
945 is
946 select asg.assignment_type
947 , asg.business_group_id
948 , asg.soft_coding_keyflex_id
949 , asg.payroll_id
950 from per_all_assignments_f asg
951 where asg.assignment_id = c_assignment_id
952 and asg.assignment_type = 'B'
953 and c_eff_date between asg.effective_start_date
954 and asg.effective_end_date;
955 --
956 cursor csr_get_aei_dets
957 (c_assignment_id in number
958 )
959 is
960 select aei.assignment_extra_info_id
961 , aei.object_version_number
962 , aei.aei_information5
963 from per_assignment_extra_info aei
964 where aei.assignment_id = c_assignment_id
965 and aei.aei_information_category = 'BEN_DERIVED';
966 --
967
968 cursor c_getdefmthpaydets
969 (c_bgp_id in number
970 )
971 Is
972 select to_number(ori.ORG_INFORMATION2)
973 from hr_organization_information ori
974 where ori.organization_id = c_bgp_id
975 and ori.ORG_INFORMATION_CONTEXT = 'Benefits Defaults';
976 l_mthpayroll_id number := null;
977 begin
978 hr_utility.set_location('Entering:'|| l_proc, 10);
979 hr_utility.set_location('OVN:'||p_object_version_number||' '||l_proc, 10);
980 --
981 -- Truncate date and date_probation_end values, effectively removing time element.
982 --
983 l_effective_date := trunc(p_effective_date);
984 l_date_probation_end := trunc(p_date_probation_end);
985 --
986 l_object_version_number := p_object_version_number;
987 --
988 -- Issue a savepoint.
989 --
990 savepoint update_ben_asg;
991 --
992 hr_utility.set_location(l_proc, 20);
993 --
994 -- Validation in addition to Table Handlers
995 --
996 -- Get assignment type.
997 --
998 hr_api.mandatory_arg_error
999 (p_api_name => l_proc
1000 ,p_argument => 'assignment_id'
1001 ,p_argument_value => p_assignment_id);
1002 --
1003 hr_api.mandatory_arg_error
1004 (p_api_name => l_proc
1005 ,p_argument => 'effective_date'
1006 ,p_argument_value => l_effective_date);
1007 hr_utility.set_location('ASG ID: '||p_assignment_id||' '||l_proc, 25);
1008 hr_utility.set_location('ESD: '||l_effective_date||' '||l_proc, 25);
1009 --
1010 -- Get assignment details
1011 --
1012 open csr_get_asg_dets
1013 (c_assignment_id => p_assignment_id
1014 ,c_eff_date => l_effective_date
1015 );
1016 fetch csr_get_asg_dets
1017 into l_assignment_type,
1018 l_business_group_id,
1019 l_soft_coding_keyflex_id,
1020 l_payroll_id;
1021 --
1022 if csr_get_asg_dets%NOTFOUND then
1023 close csr_get_asg_dets;
1024 --
1025 -- Temporary - BEN_????_BENASGNOTEXISTS
1026 --
1027 -- - The benefit assignment cannot be modified because it does not exist
1028 --
1029 hr_utility.set_message(805,'BEN_????_BENASGNOTEXISTS ');
1030 hr_utility.raise_error;
1031 --
1032 end if;
1033 close csr_get_asg_dets;
1034 hr_utility.set_location(l_proc, 20);
1035 --- check for whether the Business group allows
1036 --- creation of benefits assignments
1037 ---
1038 if not check__benasg_allow(l_business_group_id) then
1039 hr_utility.set_location('Leaving:'|| l_proc, 999);
1040 return ;
1041 end if ;
1042
1043 --
1047 (c_assignment_id => p_assignment_id
1044 -- Get assignment details
1045 --
1046 open csr_get_aei_dets
1048 );
1049 fetch csr_get_aei_dets into l_assignment_extra_info_id,
1050 l_assignment_extra_info_ovn,
1051 l_aei_origpayroll_id;
1052 --
1053 close csr_get_aei_dets;
1054 hr_utility.set_location(l_proc, 30);
1055 --
1056 -- Check for a assignment type of B
1057 --
1058 if l_assignment_type <> 'B'
1059 then
1060 --
1061 -- Temporary - BEN_????_NOTBENASG
1062 --
1063 -- - The assignment being modified should be a benefits assignment.
1064 --
1065 hr_utility.set_message(805,'BEN_????_NOTBENASG');
1066 hr_utility.raise_error;
1067 --
1068 end if;
1069 --
1070 -- Update assignment.
1071 --
1072
1073 --
1074 -- Call per_asg_upd.upd when p_called_from = 'FORM', (called from BENEBNAS.pld)
1075 -- to update benefits assignment with HR api validations.
1076 --
1077 -- Call ben_asg_upd.upd when p_called_from is NULL, to update benefits assignment
1078 -- without HR api validations
1079 --
1080
1081 if p_called_from = 'FORM' then
1082 per_asg_upd.upd
1083 (p_assignment_id => p_assignment_id
1084 ,p_effective_start_date => l_effective_start_date
1085 ,p_effective_end_date => l_effective_end_date
1086 ,p_business_group_id => l_business_group_id
1087 --
1088 ,p_grade_id => p_grade_id
1089 ,p_position_id => p_position_id
1090 ,p_job_id => p_job_id
1091 ,p_payroll_id => p_payroll_id
1092 ,p_location_id => p_location_id
1093 ,p_special_ceiling_step_id => p_special_ceiling_step_id
1094 ,p_organization_id => p_organization_id
1095 ,p_people_group_id => p_people_group_id
1096 ,p_pay_basis_id => p_pay_basis_id
1097 ,p_employment_category => p_employment_category
1098 --
1099 ,p_supervisor_id => p_supervisor_id
1100 ,p_soft_coding_keyflex_id => l_soft_coding_keyflex_id
1101 ,p_assignment_number => hr_api.g_varchar2
1102 ,p_change_reason => p_change_reason
1103 ,p_comment_id => l_comment_id
1104 ,p_comments => p_comments
1105 ,p_date_probation_end => l_date_probation_end
1106 ,p_default_code_comb_id => p_default_code_comb_id
1107 ,p_frequency => p_frequency
1108 ,p_internal_address_line => p_internal_address_line
1109 ,p_manager_flag => p_manager_flag
1110 ,p_normal_hours => p_normal_hours
1111 ,p_perf_review_period => p_perf_review_period
1112 ,p_perf_review_period_frequency => p_perf_review_period_frequency
1113 ,p_probation_period => p_probation_period
1114 ,p_probation_unit => p_probation_unit
1115 ,p_sal_review_period => p_sal_review_period
1116 ,p_sal_review_period_frequency => p_sal_review_period_frequency
1117 ,p_set_of_books_id => p_set_of_books_id
1118 ,p_source_type => p_source_type
1119 ,p_time_normal_finish => p_time_normal_finish
1120 ,p_time_normal_start => p_time_normal_start
1121 ,p_bargaining_unit_code => p_bargaining_unit_code
1122 ,p_labour_union_member_flag => p_labour_union_member_flag
1123 ,p_hourly_salaried_code => p_hourly_salaried_code
1124 ,p_ass_attribute_category => p_ass_attribute_category
1125 ,p_ass_attribute1 => p_ass_attribute1
1126 ,p_ass_attribute2 => p_ass_attribute2
1127 ,p_ass_attribute3 => p_ass_attribute3
1128 ,p_ass_attribute4 => p_ass_attribute4
1129 ,p_ass_attribute5 => p_ass_attribute5
1130 ,p_ass_attribute6 => p_ass_attribute6
1131 ,p_ass_attribute7 => p_ass_attribute7
1132 ,p_ass_attribute8 => p_ass_attribute8
1133 ,p_ass_attribute9 => p_ass_attribute9
1134 ,p_ass_attribute10 => p_ass_attribute10
1135 ,p_ass_attribute11 => p_ass_attribute11
1136 ,p_ass_attribute12 => p_ass_attribute12
1137 ,p_ass_attribute13 => p_ass_attribute13
1138 ,p_ass_attribute14 => p_ass_attribute14
1139 ,p_ass_attribute15 => p_ass_attribute15
1140 ,p_ass_attribute16 => p_ass_attribute16
1141 ,p_ass_attribute17 => p_ass_attribute17
1142 ,p_ass_attribute18 => p_ass_attribute18
1143 ,p_ass_attribute19 => p_ass_attribute19
1144 ,p_ass_attribute20 => p_ass_attribute20
1145 ,p_ass_attribute21 => p_ass_attribute21
1146 ,p_ass_attribute22 => p_ass_attribute22
1147 ,p_ass_attribute23 => p_ass_attribute23
1148 ,p_ass_attribute24 => p_ass_attribute24
1149 ,p_ass_attribute25 => p_ass_attribute25
1150 ,p_ass_attribute26 => p_ass_attribute26
1151 ,p_ass_attribute27 => p_ass_attribute27
1152 ,p_ass_attribute28 => p_ass_attribute28
1156 --
1153 ,p_ass_attribute29 => p_ass_attribute29
1154 ,p_ass_attribute30 => p_ass_attribute30
1155 ,p_title => p_title
1157 ,p_payroll_id_updated => l_payroll_id_updated
1158 ,p_other_manager_warning => l_other_manager_warning
1159 ,p_no_managers_warning => l_no_managers_warning
1160 ,p_org_now_no_manager_warning => l_org_now_no_manager_warning
1161 ,p_validation_start_date => l_validation_start_date
1162 ,p_validation_end_date => l_validation_end_date
1163 ,p_object_version_number => l_object_version_number
1164 ,p_effective_date => l_effective_date
1165 ,p_datetrack_mode => p_datetrack_update_mode
1166 ,p_validate => FALSE
1167 ,p_hourly_salaried_warning => l_boolean_dummy2
1168 );
1169 else
1170
1171 l_orig_payroll_id := hr_api.g_varchar2;
1172 l_payroll_changed := hr_api.g_varchar2;
1173
1174 --- Defaulrt Payroll id passed only for
1175 --- Backend process, if Ben_Assg form modifield
1176 ---- the payroll passed as it is
1177 open c_getdefmthpaydets
1178 (c_bgp_id => l_business_group_id
1179 );
1180 --
1181 fetch c_getdefmthpaydets into l_mthpayroll_id;
1182 if hr_api.return_legislation_code(l_business_group_id) in ('US','CA') and
1183 (c_getdefmthpaydets%notfound or l_mthpayroll_id is null) then -- Bug 1539414
1184 close c_getdefmthpaydets;
1185 --
1186 hr_utility.set_message(805,'BEN_92109_NODEFPAYEXISTS');
1187 hr_utility.raise_error;
1188 --
1189 end if;
1190 close c_getdefmthpaydets;
1191 hr_utility.set_location(l_proc, 45);
1192 hr_utility.set_location('p_payroll_id: '||p_payroll_id||' '||l_proc, 45);
1193 hr_utility.set_location('l_mthpayroll_id: '||l_mthpayroll_id||' '||l_proc, 45);
1194 --
1195 -- Check if existing payroll is set
1196 if p_payroll_id is not null then
1197 if p_payroll_id <> l_mthpayroll_id then
1198 --
1199 l_orig_payroll_id := p_payroll_id;
1200 --
1201 l_payroll_changed := 'Y';
1202
1203 else
1204 l_orig_payroll_id := p_payroll_id;
1205 --
1206 end if;
1207
1208 end if;
1209
1210
1211 ben_asg_upd.upd
1212 (p_assignment_id => p_assignment_id
1213 ,p_effective_start_date => l_effective_start_date
1214 ,p_effective_end_date => l_effective_end_date
1215 ,p_business_group_id => l_business_group_id
1216 --
1217 ,p_grade_id => p_grade_id
1218 ,p_position_id => p_position_id
1219 ,p_job_id => p_job_id
1220 ,p_payroll_id => nvl(l_mthpayroll_id,p_payroll_id)
1221 ,p_location_id => p_location_id
1222 ,p_special_ceiling_step_id => p_special_ceiling_step_id
1223 ,p_organization_id => p_organization_id
1224 ,p_people_group_id => p_people_group_id
1225 ,p_pay_basis_id => p_pay_basis_id
1226 ,p_employment_category => p_employment_category
1227 --
1228 ,p_supervisor_id => p_supervisor_id
1229 ,p_soft_coding_keyflex_id => l_soft_coding_keyflex_id
1230 ,p_assignment_number => hr_api.g_varchar2
1231 ,p_change_reason => p_change_reason
1232 ,p_comment_id => l_comment_id
1233 ,p_comments => p_comments
1234 ,p_date_probation_end => l_date_probation_end
1235 ,p_default_code_comb_id => p_default_code_comb_id
1236 ,p_frequency => p_frequency
1237 ,p_internal_address_line => p_internal_address_line
1238 ,p_manager_flag => p_manager_flag
1239 ,p_normal_hours => p_normal_hours
1240 ,p_perf_review_period => p_perf_review_period
1241 ,p_perf_review_period_frequency => p_perf_review_period_frequency
1242 ,p_probation_period => p_probation_period
1243 ,p_probation_unit => p_probation_unit
1244 ,p_sal_review_period => p_sal_review_period
1245 ,p_sal_review_period_frequency => p_sal_review_period_frequency
1246 ,p_set_of_books_id => p_set_of_books_id
1247 ,p_source_type => p_source_type
1248 ,p_time_normal_finish => p_time_normal_finish
1249 ,p_time_normal_start => p_time_normal_start
1250 ,p_bargaining_unit_code => p_bargaining_unit_code
1251 ,p_labour_union_member_flag => p_labour_union_member_flag
1252 ,p_hourly_salaried_code => p_hourly_salaried_code
1253 ,p_ass_attribute_category => p_ass_attribute_category
1254 ,p_ass_attribute1 => p_ass_attribute1
1255 ,p_ass_attribute2 => p_ass_attribute2
1256 ,p_ass_attribute3 => p_ass_attribute3
1257 ,p_ass_attribute4 => p_ass_attribute4
1258 ,p_ass_attribute5 => p_ass_attribute5
1259 ,p_ass_attribute6 => p_ass_attribute6
1260 ,p_ass_attribute7 => p_ass_attribute7
1261 ,p_ass_attribute8 => p_ass_attribute8
1262 ,p_ass_attribute9 => p_ass_attribute9
1266 ,p_ass_attribute13 => p_ass_attribute13
1263 ,p_ass_attribute10 => p_ass_attribute10
1264 ,p_ass_attribute11 => p_ass_attribute11
1265 ,p_ass_attribute12 => p_ass_attribute12
1267 ,p_ass_attribute14 => p_ass_attribute14
1268 ,p_ass_attribute15 => p_ass_attribute15
1269 ,p_ass_attribute16 => p_ass_attribute16
1270 ,p_ass_attribute17 => p_ass_attribute17
1271 ,p_ass_attribute18 => p_ass_attribute18
1272 ,p_ass_attribute19 => p_ass_attribute19
1273 ,p_ass_attribute20 => p_ass_attribute20
1274 ,p_ass_attribute21 => p_ass_attribute21
1275 ,p_ass_attribute22 => p_ass_attribute22
1276 ,p_ass_attribute23 => p_ass_attribute23
1277 ,p_ass_attribute24 => p_ass_attribute24
1278 ,p_ass_attribute25 => p_ass_attribute25
1279 ,p_ass_attribute26 => p_ass_attribute26
1280 ,p_ass_attribute27 => p_ass_attribute27
1281 ,p_ass_attribute28 => p_ass_attribute28
1282 ,p_ass_attribute29 => p_ass_attribute29
1283 ,p_ass_attribute30 => p_ass_attribute30
1284 ,p_title => p_title
1285 --
1286 ,p_payroll_id_updated => l_payroll_id_updated
1287 ,p_other_manager_warning => l_other_manager_warning
1288 ,p_no_managers_warning => l_no_managers_warning
1289 ,p_org_now_no_manager_warning => l_org_now_no_manager_warning
1290 ,p_validation_start_date => l_validation_start_date
1291 ,p_validation_end_date => l_validation_end_date
1292 ,p_object_version_number => l_object_version_number
1293 ,p_effective_date => l_effective_date
1294 ,p_datetrack_mode => p_datetrack_update_mode
1295 ,p_validate => FALSE
1296 ,p_hourly_salaried_warning => l_boolean_dummy2
1297 );
1298 end if;
1299 hr_utility.set_location(l_proc, 30);
1300 --
1301 -- Bug 5355232
1302 --
1303 hr_utility.set_location('ACE : Before : hrentmnt.adjust_entries_asg_criteria', 9999);
1304 --
1305 begin
1306 --
1307 hrentmnt.maintain_entries_asg
1308 (
1309 p_assignment_id => p_assignment_id,
1310 p_old_payroll_id => null,
1311 p_new_payroll_id => nvl(l_mthpayroll_id, p_payroll_id),
1312 p_business_group_id => l_business_group_id,
1313 p_operation => 'ASG_CRITERIA',
1314 p_actual_term_date => null,
1315 p_last_standard_date => null,
1316 p_final_process_date => null,
1317 p_dt_mode => p_datetrack_update_mode,
1318 p_validation_start_date => l_validation_start_date,
1319 p_validation_end_date => l_validation_end_date,
1320 p_entries_changed => l_entries_changed,
1321 p_old_hire_date => null,
1322 p_old_people_group_id => null,
1323 p_new_people_group_id => p_people_group_id
1324 );
1325 --
1326 exception
1327 --
1328 when others then
1329 --
1330 hr_utility.set_location('EXC : ' || substr(SQLERRM, 1, 50), 9999);
1331 hr_utility.set_location('EXC : ' || substr(SQLERRM, 51, 100), 9999);
1332 hr_utility.set_location('EXC : ' || substr(SQLERRM, 101, 150), 9999);
1333 hr_utility.set_location('EXC : ' || substr(SQLERRM, 151, 200), 9999);
1334 --
1335 raise;
1336 --
1337 end;
1338 --
1339 hr_utility.set_location('ACE : After : hrentmnt.adjust_entries_asg_criteria', 9999);
1340 --
1341 --
1342 -- Bug 5355232
1343 --
1344 --
1345 -- Check if the payroll has changed
1346 --
1347 /*
1348 if l_payroll_id_updated
1349 then
1350 --
1351 hr_utility.set_location(l_proc, 50);
1352 hr_utility.set_location('ORIG PAY ID: '||l_orig_payroll_id||' '||l_proc, 50);
1353 --
1354 -- Check if the original payroll is already set
1355 --
1356 if l_aei_origpayroll_id is null then
1357 --
1358 l_orig_payroll_id := l_payroll_id;
1359 l_payroll_changed := 'Y';
1360 --
1361 else
1362 --
1363 l_orig_payroll_id := hr_api.g_varchar2;
1364 l_payroll_changed := hr_api.g_varchar2;
1365 --
1366 end if;
1367 --
1368 else
1369 --
1370 hr_utility.set_location(l_proc, 60);
1371 --
1372 -- Unchanged
1373 --
1374 l_orig_payroll_id := hr_api.g_varchar2;
1375 l_payroll_changed := hr_api.g_varchar2;
1376 --
1377 end if;
1378 */
1379 --
1380 -- Refresh the AEI details
1381 --
1382 -- Check for forms mode
1383 --
1384 -- Bug 1904347 : eventhough update_ben_asg defaults p_age to number and
1385 -- p_adjusted_service_date, p_original_hire_date, p_termination_date
1386 -- p_leave_of_absence_date are defaulted to dates, when they are defaulted
1387 -- they have to go as hr_api.g_varchar2.
1388 --
1392 l_char_age := hr_api.g_varchar2;
1389 hr_utility.set_location('ORIG PAY ID: '||l_orig_payroll_id||' '||l_proc, 50);
1390 if p_age = hr_api.g_number then
1391 --
1393 --
1394 else
1395 --
1396 l_char_age := to_char(p_age);
1397 --
1398 end if;
1399 --
1400 if p_adjusted_service_date = hr_api.g_date then
1401 --
1402 l_char_adjusted_service_date := hr_api.g_varchar2;
1403 --
1404 else
1405 --
1406 l_char_adjusted_service_date := fnd_date.date_to_canonical(p_adjusted_service_date);
1407 --
1408 end if;
1409 --
1410 if p_original_hire_date = hr_api.g_date then
1411 --
1412 l_char_original_hire_date := hr_api.g_varchar2;
1413 --
1414 else
1415 --
1416 l_char_original_hire_date := fnd_date.date_to_canonical(p_original_hire_date);
1417 --
1418 end if;
1419 --
1420 if p_termination_date = hr_api.g_date then
1421 --
1422 l_char_termination_date := hr_api.g_varchar2;
1423 --
1424 else
1425 --
1426 l_char_termination_date := fnd_date.date_to_canonical(p_termination_date);
1427 --
1428 end if;
1429 --
1430 if p_leave_of_absence_date = hr_api.g_date then
1431 --
1432 l_char_leave_of_absence_date := hr_api.g_varchar2;
1433 --
1434 else
1435 --
1436 l_char_leave_of_absence_date := fnd_date.date_to_canonical(p_leave_of_absence_date);
1437 --
1438 end if;
1439 --
1440 if p_date_of_hire = hr_api.g_date then
1441 --
1442 l_char_date_of_hire := hr_api.g_varchar2;
1443 --
1444 else
1445 --
1446 l_char_date_of_hire := fnd_date.date_to_canonical(p_date_of_hire);
1447 --
1448 end if;
1449 --
1450 hr_assignment_extra_info_api.update_assignment_extra_info
1451 (p_assignment_extra_info_id => l_assignment_extra_info_id
1452 ,p_object_version_number => l_assignment_extra_info_ovn
1453 ,p_aei_information_category => 'BEN_DERIVED'
1454 ,p_aei_information1 => l_char_age -- p_age
1455 ,p_aei_information2 => l_char_adjusted_service_date -- fnd_date.date_to_canonical(p_adjusted_service_date)
1456 ,p_aei_information3 => l_char_original_hire_date -- fnd_date.date_to_canonical(p_original_hire_date)
1457 ,p_aei_information4 => l_payroll_changed
1458 ,p_aei_information5 => l_orig_payroll_id
1459 ,p_aei_information6 => p_salary
1460 ,p_aei_information7 => p_original_person_type
1461 ,p_aei_information8 => l_char_termination_date -- fnd_date.date_to_canonical(p_termination_date)
1462 ,p_aei_information9 => p_termination_reason
1463 ,p_aei_information10 => l_char_leave_of_absence_date -- fnd_date.date_to_canonical(p_leave_of_absence_date)
1464 ,p_aei_information11 => p_absence_type
1465 ,p_aei_information12 => p_absence_reason
1466 ,p_aei_information13 => l_char_date_of_hire -- fnd_date.date_to_canonical(p_date_of_hire)
1467 );
1468 hr_utility.set_location(l_proc, 70);
1469 --
1470 -- When in validation only mode raise the Validate_Enabled exception
1471 --
1472 if p_validate then
1473 raise hr_api.validate_enabled;
1474 end if;
1475 --
1476 -- Set all output arguments
1477 --
1478 p_object_version_number := l_object_version_number;
1479 p_effective_start_date := l_effective_start_date;
1480 p_effective_end_date := l_effective_end_date;
1481 --
1482 hr_utility.set_location(' Leaving:'||l_proc, 30);
1483 exception
1484 when hr_api.validate_enabled then
1485 --
1486 -- As the Validate_Enabled exception has been raised
1487 -- we must rollback to the savepoint
1488 --
1489 ROLLBACK TO update_ben_asg;
1490 --
1491 -- Only set output warning arguments
1492 -- (Any key or derived arguments must be set to null
1493 -- when validation only mode is being used.)
1494 --
1495 p_object_version_number := p_object_version_number;
1496 p_effective_start_date := null;
1497 p_effective_end_date := null;
1498 --
1499 when others then
1500 --
1501 -- A validation or unexpected error has occurred
1502 --
1503 -- Added as part of fix to bug 632479
1504 --
1505 ROLLBACK TO update_ben_asg;
1506 /* Inserted for nocopy changes */
1507 p_object_version_number := l_object_version_number;
1508 p_effective_start_date := null;
1509 p_effective_end_date := null;
1510 raise;
1511 --
1512 end update_ben_asg;
1513 --
1514 procedure delete_ben_asg
1515 (p_validate in boolean default false
1516 ,p_datetrack_mode in varchar2
1517 ,p_assignment_id in number
1518 ,p_object_version_number in out nocopy number
1519 ,p_effective_date in date
1520 ---
1521 ,p_effective_start_date out nocopy date
1522 ,p_effective_end_date out nocopy date
1523 )
1524 is
1525 --
1526 -- Declare cursors and local variables
1527 --
1528 l_proc varchar2(72) := g_package||'delete_ben_asg';
1529 --
1530 cursor c_getbenasgptudets
1531 (c_assignment_id in number
1535 select ptu.person_type_usage_id,
1532 ,c_eff_date in date
1533 )
1534 Is
1536 ptu.object_version_number
1537 from per_all_assignments_f asg,
1538 per_person_type_usages_f ptu,
1539 per_person_types pet
1540 where ptu.person_id = asg.person_id
1541 and asg.assignment_type <> 'C'
1542 and ptu.person_type_id = pet.person_type_id
1543 and c_eff_date
1544 between ptu.effective_start_date and ptu.effective_end_date
1545 and c_eff_date
1546 between asg.effective_start_date and asg.effective_end_date
1547 and asg.assignment_id = c_assignment_id
1548 and pet.SYSTEM_PERSON_TYPE
1549 in('SRVNG_SPS'
1550 ,'FRMR_SPS'
1551 ,'SRVNG_FMLY_MMBR'
1552 ,'FRMR_FMLY_MMBR'
1553 );
1554 --
1555 cursor c_getbenasgpendets
1556 (c_assignment_id in number
1557 ,c_eff_date in date
1558 )
1559 Is
1560 select pen.PRTT_ENRT_RSLT_ID,
1561 pen.object_version_number
1562 from BEN_PRTT_ENRT_RSLT_F pen
1563 where c_eff_date
1564 between pen.effective_start_date and pen.effective_end_date
1565 and pen.assignment_id = c_assignment_id;
1566 --
1567 l_business_group_id number;
1568 l_dummy_id number;
1569 l_dummy_warning boolean;
1570 l_dummy_date1 date;
1571 l_dummy_date2 date;
1572 --
1573 l_object_version_number number;
1574 --
1575 l_effective_start_date date;
1576 l_effective_end_date date;
1577 l_validation_start_date date;
1578 l_validation_end_date date;
1579 --
1580 l_aei_id number;
1581 l_aei_ovn number;
1582 --
1583 l_ovn number;
1584 --
1585 begin
1586 --
1587 hr_utility.set_location('Entering:'|| l_proc, 10);
1588 begin
1589 select asg.business_group_id
1590 into l_business_group_id
1591 from per_all_assignments_f asg
1592 where asg.assignment_id = p_assignment_id
1593 and asg.assignment_type <> 'C'
1594 and p_effective_date between asg.effective_start_date
1595 and asg.effective_end_date;
1596 exception
1597 when no_data_found then
1598 fnd_message.set_name('BEN','BEN_92409_ASG_NOT_FOUND');
1599 fnd_message.set_token('PROC',l_proc);
1600 fnd_message.raise_error;
1601 end;
1602 --- check for whether the Business group allows
1603 --- creation of benefits assignments
1604 ---
1605 /*
1606 -- This restrict to delete the asg which is created
1607 -- whne the prfile allows to create the asg
1608 if not check__benasg_allow(l_business_group_id) then
1609 hr_utility.set_location('Leaving:'|| l_proc, 999);
1610 return ;
1611 end if ;
1612 */
1613 --
1614 -- Issue a savepoint if operating in validation only mode
1615 --
1616 savepoint delete_ben_asg;
1617 --
1618 hr_utility.set_location(l_proc, 20);
1619 --
1620 -- Check the datetrack mode
1621 --
1622 if p_datetrack_mode = hr_api.g_zap then
1623 --
1624 -- Remove related information
1625 --
1626 -- - Remove all benefits assignment related PTUs
1627 -- for the person of the benefits assignment
1628 --
1629 delete from per_person_type_usages_f ptu
1630 where ptu.person_id in
1631 (select asg.person_id
1632 from per_all_assignments_f asg
1633 where asg.assignment_id = p_assignment_id
1634 )
1635 and ptu.person_type_id in
1636 (select pet.person_type_id
1637 from per_person_types pet
1638 where pet.SYSTEM_PERSON_TYPE
1639 in('SRVNG_SPS'
1640 ,'FRMR_SPS'
1641 ,'SRVNG_FMLY_MMBR'
1642 ,'FRMR_FMLY_MMBR'
1643 )
1644 );
1645 --
1646 -- Remove in-direct references
1647 --
1648 -- - Remove children before parents
1649 --
1650 -- - per_events
1651 -- - per_bookings
1652 --
1653 delete from per_bookings chd
1654 where chd.event_id in
1655 (select par.event_id
1656 from per_events par
1657 where par.assignment_id = p_assignment_id
1658 );
1659 --
1660 delete from per_events par
1661 where par.assignment_id = p_assignment_id;
1662 --
1663 -- - per_pay_proposals
1664 -- - per_pay_proposal_components
1665 --
1666 delete from per_pay_proposal_components chd
1667 where chd.pay_proposal_id in
1668 (select par.pay_proposal_id
1669 from per_pay_proposals par
1670 where par.assignment_id = p_assignment_id
1671 );
1672 --
1673 delete from per_pay_proposals par
1674 where par.assignment_id = p_assignment_id;
1675 --
1676 -- - pay_element_entries_f
1677 -- - pay_element_entry_values_f
1678 --
1679 delete from pay_element_entry_values_f chd
1680 where chd.ELEMENT_ENTRY_ID in
1681 (select par.ELEMENT_ENTRY_ID
1682 from pay_element_entries_f par
1686 delete from pay_element_entries_f par
1683 where par.assignment_id = p_assignment_id
1684 );
1685 --
1687 where par.assignment_id = p_assignment_id;
1688 --
1689 -- Remove direct references
1690 --
1691 delete from ben_le_clsn_n_rstr
1692 where assignment_id = p_assignment_id;
1693 --
1694 delete from ben_prtt_enrt_rslt_f
1695 where assignment_id = p_assignment_id;
1696 --
1697 delete from per_assignment_budget_values_f
1698 where assignment_id = p_assignment_id;
1699 --
1700 delete from per_assignment_extra_info
1701 where assignment_id = p_assignment_id;
1702 --
1703 delete from per_assign_proposal_answers
1704 where assignment_id = p_assignment_id;
1705 --
1706 delete from per_letter_request_lines
1707 where assignment_id = p_assignment_id;
1708 --
1709 delete from per_mm_assignments
1710 where assignment_id = p_assignment_id;
1711 --
1712 delete from per_quickpaint_result_text
1713 where assignment_id = p_assignment_id;
1714 --
1715 delete from per_secondary_ass_statuses
1716 where assignment_id = p_assignment_id;
1717 --
1718 delete from per_spinal_point_placements_f
1719 where assignment_id = p_assignment_id;
1720 --
1721 delete from hr_assignment_set_amendments
1722 where assignment_id = p_assignment_id;
1723 --
1724 delete from pay_cost_allocations_f
1725 where assignment_id = p_assignment_id;
1726 --
1727 delete from pay_personal_payment_methods_f
1728 where assignment_id = p_assignment_id;
1729 --
1730 delete from pay_assignment_latest_balances
1731 where assignment_id = p_assignment_id;
1732 --
1733 delete from pay_assignment_link_usages_f
1734 where assignment_id = p_assignment_id;
1735 --
1736 elsif p_datetrack_mode = hr_api.g_delete then
1737 --
1738 -- End date related information
1739 --
1740 -- - PTUs
1741 --
1742 for dets in c_getbenasgptudets
1743 (c_assignment_id => p_assignment_id
1744 ,c_eff_date => p_effective_date
1745 )
1746 loop
1747 --
1748 l_ovn := dets.object_version_number;
1749 --
1750 hr_per_type_usage_internal.delete_person_type_usage
1751 (p_person_type_usage_id => dets.person_type_usage_id
1752 ,p_effective_date => p_effective_date
1753 ,p_datetrack_mode => 'DELETE'
1754 ,p_object_version_number => l_ovn
1755 --
1756 ,p_effective_start_date => l_dummy_date1
1757 ,p_effective_end_date => l_dummy_date2
1758 );
1759 --
1760 end loop;
1761 --
1762 -- - PENs
1763 --
1764 for dets in c_getbenasgpendets
1765 (c_assignment_id => p_assignment_id
1766 ,c_eff_date => p_effective_date
1767 )
1768 loop
1769 --
1770 l_ovn := dets.object_version_number;
1771 --
1772 ben_PRTT_ENRT_RESULT_api.update_PRTT_ENRT_RESULT
1773 (p_prtt_enrt_rslt_id => dets.prtt_enrt_rslt_id
1774 ,p_object_version_number => l_ovn
1775 ,p_effective_date => p_effective_date
1776 ,p_datetrack_mode => 'DELETE'
1777 --
1778 ,p_effective_start_date => l_dummy_date1
1779 ,p_effective_end_date => l_dummy_date2
1780 );
1781 --
1782 end loop;
1783 --
1784 end if;
1785 --
1786 hr_utility.set_location(l_proc, 30);
1787 --
1788 l_object_version_number := p_object_version_number;
1789 --
1790 per_asg_del.del
1791 (p_assignment_id => p_assignment_id
1792 ,p_datetrack_mode => p_datetrack_mode
1793 ,p_effective_date => p_effective_date
1794 --
1795 ,p_object_version_number => l_object_version_number
1796 --
1797 ,p_effective_start_date => l_effective_start_date
1798 ,p_effective_end_date => l_effective_end_date
1799 ,p_business_group_id => l_dummy_id
1800 ,p_validation_start_date => l_validation_start_date
1801 ,p_validation_end_date => l_validation_end_date
1802 ,p_org_now_no_manager_warning => l_dummy_warning
1803 );
1804 --
1805 hr_utility.set_location(l_proc, 60);
1806 --
1807 -- When in validation only mode raise the Validate_Enabled exception
1808 --
1809 if p_validate then
1810 raise hr_api.validate_enabled;
1811 end if;
1812 --
1813 -- Set OUT parameters
1814 --
1815 p_effective_start_date := l_effective_start_date;
1816 p_effective_end_date := l_effective_end_date;
1817 p_object_version_number := l_object_version_number;
1818 --
1819 hr_utility.set_location(' Leaving:'||l_proc, 70);
1820 --
1821 exception
1822 --
1823 when hr_api.validate_enabled then
1824 --
1825 -- As the Validate_Enabled exception has been raised
1826 -- we must rollback to the savepoint
1827 --
1828 ROLLBACK TO delete_ben_asg;
1829 --
1830 -- Only set output warning arguments
1831 -- (Any key or derived arguments must be set to null
1832 -- when validation only mode is being used.)
1833 --
1834 --
1835 when others then
1836 --
1837 -- A validation or unexpected error has occured
1838 --
1839 ROLLBACK TO delete_ben_asg;
1840 /* Inserted for nocopy changes */
1841 p_object_version_number := l_object_version_number;
1842 p_effective_start_date := null;
1843 p_effective_end_date := null;
1844 raise;
1845 --
1846 end delete_ben_asg;
1847 --
1848 end ben_assignment_api;