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