[Home] [Help]
PACKAGE BODY: APPS.HR_TERMINATION_SS
Source
1 PACKAGE BODY hr_termination_ss AS
2 /* $Header: hrtrmwrs.pkb 120.7.12020000.5 2013/03/11 12:51:04 sudedas ship $ */
3
4 -- Package scope global variables.
5 -- The canonical date format has to use hyphens instead of slashes, ie.
6 -- "RRRR/MM/DD" will give a java IllegalArgument error because the java
7 -- dateValue() is expecting the date string in "rrrr-mm-dd" format.
8 -- All date fields are converted to canonical date formats and return to
9 -- the java caller.
10 g_date_format constant varchar2(10):='RRRR-MM-DD';
11 g_package constant varchar2(30) := 'HR_TERMINATION_SS';
12
13
14 /*
15 ||===========================================================================
16 || FUNCTION: update_object_version
17 || DESCRIPTION: Update the object version number in the transaction step
18 || to pass the invalid object api error for Save for Later.
19 ||=======================================================================
20 */
21 PROCEDURE update_object_version
22 (p_transaction_step_id in number
23 ,p_login_person_id in number) IS
24
25
26 CURSOR csr_new_object_number(p_period_of_service_id in number) is
27 SELECT object_version_number
28 FROM per_periods_of_service pps
29 where period_of_service_id = p_period_of_service_id;
30
31 ln_old_object_number number;
32 ln_period_of_service_id number;
33 ln_new_object_number number;
34 l_proc constant varchar2(100) := g_package || ' update_object_version';
35 BEGIN
36 hr_utility.set_location('Entering: '|| l_proc,5);
37 ln_period_of_service_id :=
38 hr_transaction_api.get_number_value
39 (p_transaction_step_id => p_transaction_step_id
40 ,p_name => 'P_PERIOD_OF_SERVICE_ID');
41
42
43 OPEN csr_new_object_number(ln_period_of_service_id);
44 FETCH csr_new_object_number into ln_new_object_number;
45 CLOSE csr_new_object_number;
46
47 ln_old_object_number :=
48 hr_transaction_api.get_number_value
49 (p_transaction_step_id => p_transaction_step_id
50 ,p_name => 'P_OBJECT_VERSION_NUMBER');
51
52 IF ln_old_object_number <> ln_new_object_number then
53 hr_transaction_api.set_number_value
54 (p_transaction_step_id => p_transaction_step_id
55 ,p_person_id => p_login_person_id
56 ,p_name => 'P_OBJECT_VERSION_NUMBER'
57 ,p_value => ln_new_object_number);
58 END IF;
59
60 hr_utility.set_location('Leaving: '|| l_proc,10);
61 END update_object_version;
62
63 -- Bug 2098595 Fix Ends
64 --
65 -- Core HR API will not support update of field Rehire Recommendation
66 -- and Rehire Reason. Hence we make following call to Person API
67 -- to update the Fields.
68 PROCEDURE update_per_details(
69 p_validate in number default 0
70 ,p_effective_date in date
71 ,p_period_of_service_id in number
72 ,p_actual_termination_date in date
73 ,p_rehire_recommendation in varchar2 default hr_api.g_varchar2
74 ,p_rehire_reason in varchar2 default hr_api.g_varchar2
75 ) IS
76
77 l_person_id per_all_people_f.person_id%TYPE;
78 l_per_object_version_number per_all_people_f.object_version_number%TYPE;
79 l_employee_number per_all_people_f.employee_number%TYPE;
80 l_effective_start_date date;
81 l_effective_end_date date;
82 l_full_name per_all_people_f.full_name%TYPE;
83 l_comment_id per_all_people_f.comment_id%TYPE;
84 l_name_combination_warning boolean;
85 l_assign_payroll_warning boolean;
86 l_orig_hire_warning boolean;
87 l_proc varchar2(100) := g_package ||'update_per_details';
88
89 l_err_msg long default null;
90
91 cursor csr_get_derived_details is
92 select per.person_id
93 , per.employee_number
94 , per.object_version_number
95 from per_all_people_f per
96 , per_business_groups bus
97 , per_periods_of_service pds
98 , per_person_types pet
99 where pds.period_of_service_id = p_period_of_service_id
100 and bus.business_group_id = pds.business_group_id
101 and per.person_id = pds.person_id
102 and p_actual_termination_date + 1 between per.effective_start_date
103 and per.effective_end_date
104 and pet.person_type_id = per.person_type_id;
105
106 l_validate boolean;
107
108 BEGIN
109
110 SAVEPOINT update_person_details;
111 open csr_get_derived_details;
112 fetch csr_get_derived_details
113 into l_person_id
114 , l_employee_number
115 , l_per_object_version_number;
116
117 l_validate := hr_java_conv_util_ss.get_boolean (p_number => p_validate);
118 -- The Transaction mode will be UPDATE as per the rehire tab in people form.
119
120 hr_person_api.update_person (
121 p_validate => l_validate
122 ,p_effective_date => p_effective_date + 1
123 ,p_datetrack_update_mode => 'CORRECTION'
124 ,p_person_id => l_person_id
125 ,p_object_version_number => l_per_object_version_number
126 ,p_employee_number => l_employee_number
127 ,p_effective_start_date => l_effective_start_date
128 ,p_effective_end_date => l_effective_end_date
129 ,p_rehire_recommendation => p_rehire_recommendation
130 ,p_rehire_reason => p_rehire_reason
131 ,p_full_name => l_full_name
132 ,p_comment_id => l_comment_id
133 ,p_name_combination_warning => l_name_combination_warning
134 ,p_assign_payroll_warning => l_assign_payroll_warning
135 ,p_orig_hire_warning => l_orig_hire_warning
136 );
137
138 IF l_validate THEN
139 ROLLBACK TO update_person_details;
140 END IF;
141
142 EXCEPTION
143 WHEN OTHERS THEN
144 l_err_msg := hr_java_conv_util_ss.get_formatted_error_message
145 (p_single_error_message => hr_utility.get_message);
146 hr_utility.set_location('EXCEPTION '|| l_err_msg || ': '|| l_proc,560);
147 rollback TO update_person_details;
148 raise;
149 END update_per_details;
150
151
152 /*
153 ||=======================================================================
154 || FUNCTION : get_termination_details - Private
155 || DESCRIPTION : This overloaded funciton return the termination related
156 || information by given transaction_step_id.
157 ||=======================================================================
158 */
159 FUNCTION get_termination_details (
160 p_transaction_step_id IN
161 hr_api_transaction_steps.transaction_step_id%type
162 )
163 RETURN hr_termination_ss.rt_termination;
164
165 /*
166 ||=======================================================================
167 || FUNCTION: get_term_flex_detail - Private
168 || DESCRIPTION: This function returns termination dff data by
169 || transaction step id.
170 ||=======================================================================
171 */
172 FUNCTION get_term_flex_detail (
173 p_transaction_step_id IN
174 hr_api_transaction_steps.transaction_step_id%type
175 )
176 RETURN hr_termination_ss.t_flex_table;
177
178 /*
179 ||===========================================================================
180 || FUNCTION: branch_on_subordinate_presence
181 || DESCRIPTION:
182 || This procedure will read the CURRENT_PERSON_ID item level
183 || attribute value and then find out if the employee to be terminated
184 || has any subordinates or not. If he has, this procedure will set
185 || the wf result code to "Y". So, workflow will transition to the
186 || Supevisor page accordingly.
187 || This procedure will set the wf transition code as follows:
188 || (Y/N)
189 || For 'Y' => branch to Supervisor page
190 || 'N' => do not branch to Supervisor page
191 ||=======================================================================
192 */
193 PROCEDURE branch_on_subordinate_presence
194 (itemtype in varchar2
195 ,itemkey in varchar2
196 ,actid in number
197 ,funcmode in varchar2
198 ,resultout out nocopy varchar2)
199 IS
200
201 l_text_value wf_item_attribute_values.text_value%type;
202 l_number_value wf_item_attribute_values.number_value%type;
203 l_asg_number_value wf_item_attribute_values.number_value%type;
204 l_effective_date date;
205 l_person_id number default null;
206 l_proc constant varchar2(100) := g_package || 'branch_on_subordinate_presence';
207
208 dummy varchar2(2);
209 l_term_sec_asg varchar2(2);
210
211 dummy_2 varchar2(2);
212 l_vol_term varchar2(6);
213
214 ----------------------------------------------------------------------------
215 -- Bug 2130066 Fix Begins:
216 -- When a Supervisor Security profile is defined with a restrictions to n
217 -- number of levels, this cursor will not return any row if the employee to
218 -- terminated has subordinates beyond the n number of levels.
219 -- For example, Employee 1 (a supervisor with a Supervisor Security profile
220 -- set up to have the maximum hierarchy level = 1, that means the profile
221 -- will only show 1 level of subordinates) is a supervisor, he has Employee 2
222 -- as the subordinate. In SSHR hierarchy tree, Employee 2 is shown.
223 -- Employee 2 himself also has subordinates reporting to him, say Employee 3.
224 -- But in SSHR hierarchy tree, Employee 3 is not listed because of the
225 -- maximum hierarchy level in the Supervisor Security Profile. When Employee
226 -- 1 selects to terminate Employee 2, the following cursor will not return
227 -- rows for Employee 2. I believe the hr security view uses the Supervisor's
228 -- person list to run the query. In this case, Employee 3 is beyong the
229 -- level specified, thus no rows returned.
230 -- Changed the cursor to use base table so that it will return rows regardless
231 -- of maximum hierarchy level specified in the Supervisor Security profile.
232 ----------------------------------------------------------------------------
233 CURSOR csr_get_subordinate IS
234 SELECT ppf.person_id
235 FROM per_all_people_f ppf -- Bug 2130066 fix
236 ,per_all_assignments_f paf
237 ,per_periods_of_service ppos
238 WHERE paf.supervisor_id = l_number_value
239 AND paf.person_id = ppf.person_id
240 AND ppf.person_id = ppos.person_id
241 AND ppf.current_employee_flag = 'Y'
242 AND l_effective_date between ppf.effective_start_date
243 and ppf.effective_end_date -1 --added -1 for Bug 12360962
244 -- AND paf.primary_flag = 'Y' -- commented to support multiple assignments
245 AND paf.assignment_type = 'E'
246 AND l_effective_date between paf.effective_start_date
247 and paf.effective_end_date
248 AND l_effective_date between ppos.date_start
249 and nvl(ppos.actual_termination_date
250 ,l_effective_date)
251 and ((dummy = 'N' and paf.supervisor_assignment_id=l_asg_number_value)
252 OR dummy = 'Y')
253 UNION -- CWK Phase III Changes.
254 SELECT ppf.person_id
255 FROM per_all_people_f ppf -- Bug 2130066 fix
256 ,per_all_assignments_f paf
257 ,per_periods_of_placement ppop
258 WHERE paf.supervisor_id = l_number_value
259 AND paf.person_id = ppf.person_id
260 AND ppf.person_id = ppop.person_id
261 AND ppf.current_npw_flag = 'Y'
262 AND l_effective_date between ppf.effective_start_date
263 and ppf.effective_end_date -1 --added -1 for Bug 12360962
264 -- AND paf.primary_flag = 'Y' -- commented to support multiple assignments
265 AND paf.assignment_type = 'C'
266 AND l_effective_date between paf.effective_start_date
267 and paf.effective_end_date
268 AND l_effective_date between ppop.date_start
269 and nvl(ppop.actual_termination_date
270 ,l_effective_date)
271 and ((dummy = 'N' and paf.supervisor_assignment_id=l_asg_number_value)
272 OR dummy = 'Y');
273
274 cursor csr_attr_value(actid in number, name in varchar2) is
275 SELECT WAAV.TEXT_VALUE Value
276 FROM WF_ACTIVITY_ATTR_VALUES WAAV
277 WHERE WAAV.PROCESS_ACTIVITY_ID = actid
278 AND WAAV.NAME = name;
279
280 BEGIN
281 --
282 hr_utility.set_location('Entering: '|| l_proc,5);
283 l_number_value := wf_engine.GetItemAttrNumber
284 (itemtype => itemtype
285 ,itemkey => itemkey
286 ,aname => 'CURRENT_PERSON_ID');
287
288 -- The termination effective date was stored in the wf item attribute
289 -- P_EFFECTIVE_DATE as a text value in the canonical format, ie. 'RRRR/MM/DD'.
290 l_text_value := wf_engine.GetItemAttrText
291 (itemtype => itemtype
292 ,itemkey => itemkey
293 ,aname => 'P_EFFECTIVE_DATE');
294
295 --
296 -- Now convert the text value date to date data type
297 -- Bug 2476134 - 07/29/2002
298 -- Changed the <> null to IS NOT NULL.
299 IF l_text_value IS NOT NULL
300 THEN
301 hr_utility.trace('In (if l_text_value IS NOT NULL): '|| l_proc);
302 l_effective_date := trunc(
303 to_date(l_text_value, hr_transaction_ss.g_date_format)
304 );
305 ELSE
306 hr_utility.trace('In else of (if l_text_value IS NOT NULL): '|| l_proc);
307 -- Use sysdate if the wf item attribute contains null value
308 l_effective_date := trunc(sysdate);
309 END IF;
310
311 l_asg_number_value := wf_engine.GetItemAttrNumber (
312 itemtype => itemtype,
313 itemkey => itemkey,
314 aname => 'CURRENT_ASSIGNMENT_ID');
315
316 select primary_flag into dummy from per_all_assignments_f
317 where assignment_id=l_asg_number_value and l_effective_date between effective_start_date
318 and effective_end_date;
319
320 l_term_sec_asg := wf_engine.getitemattrtext(itemtype, itemkey,
321 'HR_TERM_SEC_ASG',true);
322
323 if (l_term_sec_asg is null OR l_term_sec_asg <> 'Y') then
324 dummy := 'Y';
325 end if;
326
327 if (l_number_value = fnd_global.employee_id) then
328 l_vol_term := wf_engine.getitemattrtext(itemtype, itemkey,
329 'HR_VOL_TERM_SS',true);
330 if (l_vol_term is not null) then
331 open csr_attr_value(actid,'BYPASS_CHG_MGR');
332 fetch csr_attr_value into dummy_2;
333 close csr_attr_value;
334 end if;
335 end if;
336
337 OPEN csr_get_subordinate;
338 FETCH csr_get_subordinate into l_person_id;
339
340 IF csr_get_subordinate%NOTFOUND
341 THEN
342 -- no subordinates, then set the result code to 'N'
343 resultout := 'COMPLETE:'|| 'N';
344 ELSIF (dummy_2 = 'Y') then
345 resultout := 'COMPLETE:'|| 'N';
346 if (l_vol_term = 'CCMGR') then
347 wf_engine.setitemattrtext(itemtype,itemkey,'HR_VOL_TERM_SS','BOTH');
348 elsif (l_vol_term = 'Y') then
349 wf_engine.setitemattrtext(itemtype,itemkey,'HR_VOL_TERM_SS','SUP');
350 end if;
351 ELSE
352 resultout := 'COMPLETE:'|| 'Y';
353 END IF;
354 --
355 CLOSE csr_get_subordinate;
356
357 -- 08/07/01 Bug 1853417 Fix:
358 -- Need to set the wf item attribute HR_TERM_SUP_FLAG so that the Supervisor
359 -- page knows that the caller is from Termination.
360 -- NOTE: The HR_TERM_SUP_FLAG attribute is not used for determining whether
361 -- to branch to the Supervisor page or not.
362 wf_engine.SetItemAttrText
363 (itemtype => itemtype
364 ,itemkey => itemkey
365 ,aname => 'HR_TERM_SUP_FLAG'
366 ,avalue => 'Y');
367 --
368 hr_utility.set_location('Leaving: '|| l_proc,15);
369
370 EXCEPTION
371 WHEN OTHERS THEN
372 resultout := null;
373 hr_utility.set_location('EXCEPTION: '|| l_proc,555);
374 WF_CORE.CONTEXT(g_package
375 ,'branch_on_subordinate_presence'
376 ,itemtype
377 ,itemkey
378 ,to_char(actid)
379 ,funcmode);
380 RAISE;
381 end branch_on_subordinate_presence;
382 --
383 --
384 /*
385 ||===========================================================================
386 || PROCEDURE: actual_termination_emp
387 ||---------------------------------------------------------------------------
388 ||
389 || Description:
390 || This procedure will call the actual API -
391 || hr_ex_employee_api.actual_termination_emp
392 ||
393 || Pre Conditions:
394 ||
395 || In Arguments:
396 || Contains entire list of parameters that are defined in the actual
397 || API. For details see peexeapi.pkb file.
398 ||
399 || out nocopy Arguments:
400 ||
401 || In out nocopy Arguments:
402 ||
403 || Post Success:
404 || Executes the API call.
405 ||
406 || Post Failure:
407 || Raises an exception
408 ||
409 || Access Status:
410 || Public.
411 ||
412 ||===========================================================================
413 */
414 PROCEDURE actual_termination_emp
415 (p_validate in number default 0
416 ,p_effective_date in date
417 ,p_period_of_service_id in number
418 ,p_object_version_number in out nocopy number
419 ,p_actual_termination_date in date
420 ,p_last_standard_process_date in out nocopy date
421 ,p_person_type_id in number default hr_api.g_number
422 ,p_assignment_status_type_id in number default hr_api.g_number
423 ,p_leaving_reason in varchar2 default hr_api.g_varchar2
424 ,p_rehire_recommendation in varchar2 default hr_api.g_varchar2
425 ,p_rehire_reason in varchar2 default hr_api.g_varchar2
426 ,p_termination_accepted_person in number default hr_api.g_number
427 ,p_accepted_termination_date in date default hr_api.g_date
428 ,p_comments in varchar2 default hr_api.g_varchar2
429 ,p_notified_termination_date in date default hr_api.g_date
430 ,p_projected_termination_date in date default hr_api.g_date
431 ,p_final_process_date in out nocopy date
432 ,p_attribute_category in varchar2 default hr_api.g_varchar2
433 ,p_attribute1 in varchar2 default hr_api.g_varchar2
434 ,p_attribute2 in varchar2 default hr_api.g_varchar2
435 ,p_attribute3 in varchar2 default hr_api.g_varchar2
436 ,p_attribute4 in varchar2 default hr_api.g_varchar2
437 ,p_attribute5 in varchar2 default hr_api.g_varchar2
438 ,p_attribute6 in varchar2 default hr_api.g_varchar2
439 ,p_attribute7 in varchar2 default hr_api.g_varchar2
440 ,p_attribute8 in varchar2 default hr_api.g_varchar2
441 ,p_attribute9 in varchar2 default hr_api.g_varchar2
442 ,p_attribute10 in varchar2 default hr_api.g_varchar2
443 ,p_attribute11 in varchar2 default hr_api.g_varchar2
444 ,p_attribute12 in varchar2 default hr_api.g_varchar2
445 ,p_attribute13 in varchar2 default hr_api.g_varchar2
446 ,p_attribute14 in varchar2 default hr_api.g_varchar2
447 ,p_attribute15 in varchar2 default hr_api.g_varchar2
448 ,p_attribute16 in varchar2 default hr_api.g_varchar2
449 ,p_attribute17 in varchar2 default hr_api.g_varchar2
450 ,p_attribute18 in varchar2 default hr_api.g_varchar2
451 ,p_attribute19 in varchar2 default hr_api.g_varchar2
452 ,p_attribute20 in varchar2 default hr_api.g_varchar2
453 ,p_pds_information_category in varchar2 default hr_api.g_varchar2
454 ,p_pds_information1 in varchar2 default hr_api.g_varchar2
455 ,p_pds_information2 in varchar2 default hr_api.g_varchar2
456 ,p_pds_information3 in varchar2 default hr_api.g_varchar2
457 ,p_pds_information4 in varchar2 default hr_api.g_varchar2
458 ,p_pds_information5 in varchar2 default hr_api.g_varchar2
459 ,p_pds_information6 in varchar2 default hr_api.g_varchar2
460 ,p_pds_information7 in varchar2 default hr_api.g_varchar2
461 ,p_pds_information8 in varchar2 default hr_api.g_varchar2
462 ,p_pds_information9 in varchar2 default hr_api.g_varchar2
463 ,p_pds_information10 in varchar2 default hr_api.g_varchar2
464 ,p_pds_information11 in varchar2 default hr_api.g_varchar2
465 ,p_pds_information12 in varchar2 default hr_api.g_varchar2
466 ,p_pds_information13 in varchar2 default hr_api.g_varchar2
467 ,p_pds_information14 in varchar2 default hr_api.g_varchar2
468 ,p_pds_information15 in varchar2 default hr_api.g_varchar2
469 ,p_pds_information16 in varchar2 default hr_api.g_varchar2
470 ,p_pds_information17 in varchar2 default hr_api.g_varchar2
471 ,p_pds_information18 in varchar2 default hr_api.g_varchar2
472 ,p_pds_information19 in varchar2 default hr_api.g_varchar2
473 ,p_pds_information20 in varchar2 default hr_api.g_varchar2
474 ,p_pds_information21 in varchar2 default hr_api.g_varchar2
475 ,p_pds_information22 in varchar2 default hr_api.g_varchar2
476 ,p_pds_information23 in varchar2 default hr_api.g_varchar2
477 ,p_pds_information24 in varchar2 default hr_api.g_varchar2
478 ,p_pds_information25 in varchar2 default hr_api.g_varchar2
479 ,p_pds_information26 in varchar2 default hr_api.g_varchar2
480 ,p_pds_information27 in varchar2 default hr_api.g_varchar2
481 ,p_pds_information28 in varchar2 default hr_api.g_varchar2
482 ,p_pds_information29 in varchar2 default hr_api.g_varchar2
483 ,p_pds_information30 in varchar2 default hr_api.g_varchar2
484 ,p_supervisor_warning out nocopy number
485 ,p_event_warning out nocopy number
486 ,p_interview_warning out nocopy number
487 ,p_review_warning out nocopy number
488 ,p_recruiter_warning out nocopy number
489 ,p_asg_future_changes_warning out nocopy number
490 ,p_entries_changed_warning out nocopy varchar2
491 ,p_pay_proposal_warning out nocopy number
492 ,p_dod_warning out nocopy number
493 ,p_error_message out nocopy long
494 )
495 IS
496
497 lb_supervisor_warning BOOLEAN;
498 lb_event_warning BOOLEAN;
499 lb_interview_warning BOOLEAN;
500 lb_review_warning BOOLEAN;
501 lb_recruiter_warning BOOLEAN;
502 lb_asg_future_changes_warning BOOLEAN;
503 lb_pay_proposal_warning BOOLEAN;
504 lb_dod_warning BOOLEAN;
505
506 l_proc varchar2(100) := g_package ||'actual_termination_emp';
507
508 l_err_msg long default null;
509 actual_term_emp_err exception;
510 update_pds_details_err exception;
511 fr_localization_err exception;
512
513 -- Added for FR localization bug 2881583
514 l_pds_information10 varchar2(150);
515 l_business_group_id number;
516 l_legislation_code varchar2(30);
517 l_pds_information_category varchar2(30);
518
519 --set outparameter for final_process_emp
520 l_org_now_no_manager_warning boolean;
521 l_entries_changed_warning varchar2(1);
522 l_final_process_date date;
523 l_asg_future_changes_warning boolean;
524
525 BEGIN
526
527
528 hr_utility.set_location('Entering: ' || l_proc,5);
529
530 -- Bug 2098595 Fix Begins: 12/03/2001
531 -- Need to switch the order of calling the api. We need to call
532 -- hr_termination_ss.update_pds_details first to set the flex segments
533 -- before calling hr_ex_employee_api.actual_termination_emp.
534 -- Otherwise, when there is a mandatory segment, we'll get an error
535 -- as follows:
536 -- The mandatory column Attribute??(also known as xxxx) has not been
537 -- assigned a value.
538 -- The reason is because in hr_ex_employee_api.actual_termination_emp,
539 -- it validates flex segments and there are no parameters to receive
540 -- flex segments in hr_ex_employee_api.actual_termination_emp. So, we
541 -- need to flip the order to set the value of flex segments.
542 -- Call update_pds_details;
543 --
544 -- Need to set a savepoint. Call update_pds_details and
545 -- actual_termination_emp with p_validate = false. Rollback changes
546 -- if the passed in parameter p_validate = true.
547
548
549 SAVEPOINT terminate_ee;
550 -- Set p_validate to false to commit the desc flex segments, especially
551 -- the mandatory segments to avoid the following error issued by
552 -- hr_ex_employee_api.actual_termination_emp.
553 -- The mandatory column Attribute??(also known as xxxx) has not been
554 -- assigned a value.
555
556 --------------------------------------------------------------
557 -- Bug 2881583
558 -- Determine whether legislation code is FR. If so the default the
559 -- PDS_INFORMATION10 to Actual Termination Date.
560 -- This is required for compatibility with code delivered in FP.D
561 --
562 -- N.B. Local variable l_pds_information10 declared at
563 -- top of procedure
564 -- Also l_pds_information10 used in call to
565 -- hr_termination_ss.update_pds_details below
566 --------------------------------------------------------------
567 l_pds_information10 := p_pds_information10;
568 l_pds_information_category := p_pds_information_category;
569
570 select business_group_id
571 into l_business_group_id
572 from per_periods_of_service
573 where period_of_service_id = p_period_of_service_id;
574
575 hr_utility.trace('checking FR legislation ' || l_proc);
576 --
577 l_legislation_code :=
578 hr_api.return_legislation_code(p_business_group_id => l_business_group_id);
579 --
580 if l_legislation_code = 'FR' then
581 if p_actual_termination_date is not null and
582 (p_pds_information10 = hr_api.g_varchar2
583 or p_pds_information10 is null ) then
584 l_pds_information10
585 := fnd_date.date_to_canonical(p_actual_termination_date);
586 l_pds_information_category := l_legislation_code;
587 end if;
588 end if;
589 --
590
591 hr_utility.trace('Calling hr_termination_ss.update_pds_details ' || l_proc);
592 hr_termination_ss.update_pds_details
593 (p_validate => 0
594 ,p_effective_date => p_effective_date
595 ,p_period_of_service_id => p_period_of_service_id
596 ,p_termination_accepted_person => p_termination_accepted_person
597 ,p_accepted_termination_date => p_accepted_termination_date
598 ,p_object_version_number => p_object_version_number
599 ,p_comments => p_comments
600 ,p_leaving_reason => p_leaving_reason
601 ,p_notified_termination_date => p_notified_termination_date
602 ,p_projected_termination_date => p_projected_termination_date
603 ,p_attribute_category => p_attribute_category
604 ,p_attribute1 => p_attribute1
605 ,p_attribute2 => p_attribute2
606 ,p_attribute3 => p_attribute3
607 ,p_attribute4 => p_attribute4
608 ,p_attribute5 => p_attribute5
609 ,p_attribute6 => p_attribute6
610 ,p_attribute7 => p_attribute7
611 ,p_attribute8 => p_attribute8
612 ,p_attribute9 => p_attribute9
613 ,p_attribute10 => p_attribute10
614 ,p_attribute11 => p_attribute11
615 ,p_attribute12 => p_attribute12
616 ,p_attribute13 => p_attribute13
617 ,p_attribute14 => p_attribute14
618 ,p_attribute15 => p_attribute15
619 ,p_attribute16 => p_attribute16
620 ,p_attribute17 => p_attribute17
621 ,p_attribute18 => p_attribute18
622 ,p_attribute19 => p_attribute19
623 ,p_attribute20 => p_attribute20
624 ,p_pds_information_category => l_pds_information_category -- bug 2881583
625 ,p_pds_information1 => p_pds_information1
626 ,p_pds_information2 => p_pds_information2
627 ,p_pds_information3 => p_pds_information3
628 ,p_pds_information4 => p_pds_information4
629 ,p_pds_information5 => p_pds_information5
630 ,p_pds_information6 => p_pds_information6
631 ,p_pds_information7 => p_pds_information7
632 ,p_pds_information8 => p_pds_information8
633 ,p_pds_information9 => p_pds_information9
634 ,p_pds_information10 => l_pds_information10 -- bug 2881583
635 ,p_pds_information11 => p_pds_information11
636 ,p_pds_information12 => p_pds_information12
637 ,p_pds_information13 => p_pds_information13
638 ,p_pds_information14 => p_pds_information14
639 ,p_pds_information15 => p_pds_information15
640 ,p_pds_information16 => p_pds_information16
641 ,p_pds_information17 => p_pds_information17
642 ,p_pds_information18 => p_pds_information18
643 ,p_pds_information19 => p_pds_information19
644 ,p_pds_information20 => p_pds_information20
645 ,p_pds_information21 => p_pds_information21
646 ,p_pds_information22 => p_pds_information22
647 ,p_pds_information23 => p_pds_information23
648 ,p_pds_information24 => p_pds_information24
649 ,p_pds_information25 => p_pds_information25
650 ,p_pds_information26 => p_pds_information26
651 ,p_pds_information27 => p_pds_information27
652 ,p_pds_information28 => p_pds_information28
653 ,p_pds_information29 => p_pds_information29
654 ,p_pds_information30 => p_pds_information30
655 );
656
657 -- Fix for the bug 13846099 Starts
658 -- Moved this piece of code "update rehire" info
659 -- before calling "hr_ex_employee_api.actual_termination_emp"
660
661
662 -- Core HR API will not support update of field Rehire Recommendation
663 -- and Rehire Reason. Hence we make following call to Person API
664 -- to update the Fields.
665 hr_utility.trace('Calling hr_termination_ss.update_per_details ' || l_proc);
666 -- moved cursor definitions to new procedure , which can be called from
667 -- proces_api also.
668
669 update_per_details(
670 p_validate => 0, -- false
671 p_effective_date => p_effective_date,
672 p_period_of_service_id => p_period_of_service_id,
673 p_actual_termination_date => p_actual_termination_date,
674 p_rehire_recommendation => p_rehire_recommendation,
675 p_rehire_reason => p_rehire_reason);
676
677
678 -- Fix for the bug 13846099 Ends
679
680 -- Now actually call API
681 hr_utility.trace('Calling hr_ex_employee_api.actual_termination_emp ' || l_proc);
682
683 hr_ex_employee_api.actual_termination_emp
684 (p_validate => false
685 ,p_effective_date => p_effective_date
686 ,p_period_of_service_id => p_period_of_service_id
687 ,p_object_version_number => p_object_version_number
688 ,p_actual_termination_date => p_actual_termination_date
689 ,p_last_standard_process_date => p_last_standard_process_date
690 ,p_person_type_id => p_person_type_id
691 ,p_assignment_status_type_id => p_assignment_status_type_id
692 ,p_leaving_reason => p_leaving_reason
693 -- ,p_rehire_recommendation => p_rehire_recommendation
694 -- ,p_rehire_reason => p_rehire_reason
695 ,p_supervisor_warning => lb_supervisor_warning
696 ,p_event_warning => lb_event_warning
697 ,p_interview_warning => lb_interview_warning
698 ,p_review_warning => lb_review_warning
699 ,p_recruiter_warning => lb_recruiter_warning
700 ,p_asg_future_changes_warning => lb_asg_future_changes_warning
701 ,p_entries_changed_warning => p_entries_changed_warning
702 ,p_pay_proposal_warning => lb_pay_proposal_warning
703 ,p_dod_warning => lb_dod_warning);
704
705
706 p_supervisor_warning :=
707 hr_java_conv_util_ss.get_number(p_boolean => lb_supervisor_warning);
708 p_event_warning :=
709 hr_java_conv_util_ss.get_number(p_boolean => lb_event_warning);
710 p_interview_warning :=
711 hr_java_conv_util_ss.get_number(p_boolean => lb_interview_warning);
712 p_review_warning :=
713 hr_java_conv_util_ss.get_number(p_boolean => lb_review_warning);
714 p_recruiter_warning :=
715 hr_java_conv_util_ss.get_number(p_boolean => lb_recruiter_warning);
716 p_asg_future_changes_warning :=
717 hr_java_conv_util_ss.get_number(p_boolean => lb_asg_future_changes_warning);
718 p_pay_proposal_warning :=
719 hr_java_conv_util_ss.get_number(p_boolean => lb_pay_proposal_warning);
720 p_dod_warning :=
721 hr_java_conv_util_ss.get_number(p_boolean => lb_dod_warning);
722
723 -- Bug 2098595 Fix Ends
724 --
725
726
727 --call for Final_emp_process
728 l_entries_changed_warning := 'N';
729 l_final_process_date := p_final_process_date;
730
731 IF l_final_process_date IS NOT NULL
732 THEN
733 hr_ex_employee_api.final_process_emp(
734 p_validate => false,
735 p_period_of_service_id => p_period_of_service_id,
736 p_object_version_number => p_object_version_number,
737 p_final_process_date => l_final_process_date,
738 p_org_now_no_manager_warning => l_org_now_no_manager_warning,
739 p_asg_future_changes_warning => l_asg_future_changes_warning,
740 p_entries_changed_warning => l_entries_changed_warning );
741 END IF;
742
743 --end of call Final-emp_process
744 IF hr_java_conv_util_ss.get_boolean (p_number => p_validate)
745 THEN
746 -- validate mode is true, rollback all the changes
747 rollback to terminate_ee;
748 END IF;
749 --
750 --
751
752 hr_utility.set_location(' Leaving: ' || l_proc,10);
753 EXCEPTION
754
755 WHEN OTHERS THEN
756
757
758 -- Call the hr_java_conv_util_ss to strip off the unfriendly error
759 -- message.
760 -- For example:
761 -- "Error: java.sql.SQLExcpetion: ORA-01400: cannot insert NULL into
762 -- ("HR"."PER_PERSON_LIST_CHANGES"."SECURITY_PROFILE_ID"). ORA-06512:
763 -- at "APPS.HR_TERMINATION_SS", line xxx ORA-06512: at line 1.
764 -- With the call to hr_java_conv_util_ss, the error message text will
765 -- become:
766 -- "Error: ORA-01400: cannot insert NULL into
767 -- ("HR"."PER_PERSON_LIST_CHANGES"."SECURITY_PROFILE_ID").
768
769
770 p_error_message := hr_java_conv_util_ss.get_formatted_error_message
771 (p_single_error_message => hr_utility.get_message);
772 hr_utility.set_location('EXCEPTION '|| p_error_message|| ' :' || l_proc,575);
773 -- rollback the changes in case of exception
774 rollback to terminate_ee;
775 p_supervisor_warning := null;
776 p_event_warning := null;
777 p_interview_warning := null;
778 p_review_warning := null;
779 p_recruiter_warning := null;
780 p_asg_future_changes_warning := null;
781 p_pay_proposal_warning := null;
782 p_dod_warning := null;
783
784 END actual_termination_emp;
785
786 /*
787 ||===========================================================================
788 || PROCEDURE: update_pds_details
789 ||---------------------------------------------------------------------------
790 ||
791 || Description:
792 || This procedure will call the actual API -
793 || hr_periods_of_service_api.update_pds_details
794 ||
795 || Pre Conditions:
796 ||
797 || In Arguments:
798 || Contains entire list of parameters that are defined in the actual
799 || API. For details see pepdsapi.pkb file.
800 ||
801 || out nocopy Arguments:
802 ||
803 || In out nocopy Arguments:
804 ||
805 || Post Success:
806 || Executes the API call.
807 ||
808 || Post Failure:
809 || Raises an exception
810 ||
811 || Access Status:
812 || Public.
813 ||
814 ||===========================================================================
815 */
816 PROCEDURE update_pds_details
817 (p_validate in number default 0
818 ,p_effective_date in date
819 ,p_period_of_service_id in number
820 ,p_termination_accepted_person in number default hr_api.g_number
821 ,p_accepted_termination_date in date default hr_api.g_date
822 ,p_object_version_number in out nocopy number
823 ,p_comments in varchar2 default hr_api.g_varchar2
824 ,p_leaving_reason in varchar2 default hr_api.g_varchar2
825 ,p_notified_termination_date in date default hr_api.g_date
826 ,p_projected_termination_date in date default hr_api.g_date
827 ,p_attribute_category in varchar2 default hr_api.g_varchar2
828 ,p_attribute1 in varchar2 default hr_api.g_varchar2
829 ,p_attribute2 in varchar2 default hr_api.g_varchar2
830 ,p_attribute3 in varchar2 default hr_api.g_varchar2
831 ,p_attribute4 in varchar2 default hr_api.g_varchar2
832 ,p_attribute5 in varchar2 default hr_api.g_varchar2
833 ,p_attribute6 in varchar2 default hr_api.g_varchar2
834 ,p_attribute7 in varchar2 default hr_api.g_varchar2
835 ,p_attribute8 in varchar2 default hr_api.g_varchar2
836 ,p_attribute9 in varchar2 default hr_api.g_varchar2
837 ,p_attribute10 in varchar2 default hr_api.g_varchar2
838 ,p_attribute11 in varchar2 default hr_api.g_varchar2
839 ,p_attribute12 in varchar2 default hr_api.g_varchar2
840 ,p_attribute13 in varchar2 default hr_api.g_varchar2
841 ,p_attribute14 in varchar2 default hr_api.g_varchar2
842 ,p_attribute15 in varchar2 default hr_api.g_varchar2
843 ,p_attribute16 in varchar2 default hr_api.g_varchar2
844 ,p_attribute17 in varchar2 default hr_api.g_varchar2
845 ,p_attribute18 in varchar2 default hr_api.g_varchar2
846 ,p_attribute19 in varchar2 default hr_api.g_varchar2
847 ,p_attribute20 in varchar2 default hr_api.g_varchar2
848 ,p_pds_information_category in varchar2 default hr_api.g_varchar2
849 ,p_pds_information1 in varchar2 default hr_api.g_varchar2
850 ,p_pds_information2 in varchar2 default hr_api.g_varchar2
851 ,p_pds_information3 in varchar2 default hr_api.g_varchar2
852 ,p_pds_information4 in varchar2 default hr_api.g_varchar2
853 ,p_pds_information5 in varchar2 default hr_api.g_varchar2
854 ,p_pds_information6 in varchar2 default hr_api.g_varchar2
855 ,p_pds_information7 in varchar2 default hr_api.g_varchar2
856 ,p_pds_information8 in varchar2 default hr_api.g_varchar2
857 ,p_pds_information9 in varchar2 default hr_api.g_varchar2
858 ,p_pds_information10 in varchar2 default hr_api.g_varchar2
859 ,p_pds_information11 in varchar2 default hr_api.g_varchar2
860 ,p_pds_information12 in varchar2 default hr_api.g_varchar2
861 ,p_pds_information13 in varchar2 default hr_api.g_varchar2
862 ,p_pds_information14 in varchar2 default hr_api.g_varchar2
863 ,p_pds_information15 in varchar2 default hr_api.g_varchar2
864 ,p_pds_information16 in varchar2 default hr_api.g_varchar2
865 ,p_pds_information17 in varchar2 default hr_api.g_varchar2
866 ,p_pds_information18 in varchar2 default hr_api.g_varchar2
867 ,p_pds_information19 in varchar2 default hr_api.g_varchar2
868 ,p_pds_information20 in varchar2 default hr_api.g_varchar2
869 ,p_pds_information21 in varchar2 default hr_api.g_varchar2
870 ,p_pds_information22 in varchar2 default hr_api.g_varchar2
871 ,p_pds_information23 in varchar2 default hr_api.g_varchar2
872 ,p_pds_information24 in varchar2 default hr_api.g_varchar2
873 ,p_pds_information25 in varchar2 default hr_api.g_varchar2
874 ,p_pds_information26 in varchar2 default hr_api.g_varchar2
875 ,p_pds_information27 in varchar2 default hr_api.g_varchar2
876 ,p_pds_information28 in varchar2 default hr_api.g_varchar2
877 ,p_pds_information29 in varchar2 default hr_api.g_varchar2
878 ,p_pds_information30 in varchar2 default hr_api.g_varchar2
879 )
880 IS
881 l_proc constant varchar2(100) := g_package || ' update_pds_details';
882 BEGIN
883 hr_utility.set_location('Entering: '|| l_proc,5);
884 -- Call Actual API
885 hr_periods_of_service_api.update_pds_details
886 (p_validate => hr_java_conv_util_ss.get_boolean (p_number => p_validate)
887 ,p_effective_date => p_effective_date
888 ,p_period_of_service_id => p_period_of_service_id
889 ,p_termination_accepted_person => p_termination_accepted_person
890 ,p_accepted_termination_date => p_accepted_termination_date
891 ,p_object_version_number => p_object_version_number
892 ,p_comments => p_comments
893 ,p_leaving_reason => p_leaving_reason
894 ,p_notified_termination_date => p_notified_termination_date
895 ,p_projected_termination_date => p_projected_termination_date
896 ,p_attribute_category => p_attribute_category
897 ,p_attribute1 => p_attribute1
898 ,p_attribute2 => p_attribute2
899 ,p_attribute3 => p_attribute3
900 ,p_attribute4 => p_attribute4
901 ,p_attribute5 => p_attribute5
902 ,p_attribute6 => p_attribute6
903 ,p_attribute7 => p_attribute7
904 ,p_attribute8 => p_attribute8
905 ,p_attribute9 => p_attribute9
906 ,p_attribute10 => p_attribute10
907 ,p_attribute11 => p_attribute11
908 ,p_attribute12 => p_attribute12
909 ,p_attribute13 => p_attribute13
910 ,p_attribute14 => p_attribute14
911 ,p_attribute15 => p_attribute15
912 ,p_attribute16 => p_attribute16
913 ,p_attribute17 => p_attribute17
914 ,p_attribute18 => p_attribute18
915 ,p_attribute19 => p_attribute19
916 ,p_attribute20 => p_attribute20
917 ,p_pds_information_category => p_pds_information_category
918 ,p_pds_information1 => p_pds_information1
919 ,p_pds_information2 => p_pds_information2
920 ,p_pds_information3 => p_pds_information3
921 ,p_pds_information4 => p_pds_information4
922 ,p_pds_information5 => p_pds_information5
923 ,p_pds_information6 => p_pds_information6
924 ,p_pds_information7 => p_pds_information7
925 ,p_pds_information8 => p_pds_information8
926 ,p_pds_information9 => p_pds_information9
927 ,p_pds_information10 => p_pds_information10
928 ,p_pds_information11 => p_pds_information11
929 ,p_pds_information12 => p_pds_information12
930 ,p_pds_information13 => p_pds_information13
931 ,p_pds_information14 => p_pds_information14
932 ,p_pds_information15 => p_pds_information15
933 ,p_pds_information16 => p_pds_information16
934 ,p_pds_information17 => p_pds_information17
935 ,p_pds_information18 => p_pds_information18
936 ,p_pds_information19 => p_pds_information19
937 ,p_pds_information20 => p_pds_information20
938 ,p_pds_information21 => p_pds_information21
939 ,p_pds_information22 => p_pds_information22
940 ,p_pds_information23 => p_pds_information23
941 ,p_pds_information24 => p_pds_information24
942 ,p_pds_information25 => p_pds_information25
943 ,p_pds_information26 => p_pds_information26
944 ,p_pds_information27 => p_pds_information27
945 ,p_pds_information28 => p_pds_information28
946 ,p_pds_information29 => p_pds_information29
947 ,p_pds_information30 => p_pds_information30);
948
949 hr_utility.set_location('Leaving: '|| l_proc,10);
950
951
952 EXCEPTION
953 WHEN OTHERS THEN
954 hr_utility.set_location('EXCEPTION: '|| l_proc,555);
955 RAISE;
956
957 END update_pds_details;
958
959 /*
960 ||===========================================================================
961 || PROCEDURE: process_save
962 ||---------------------------------------------------------------------------
963 ||
964 || Description:
965 || Save Termination Transaction to transaction table
966 ||
967 || Pre Conditions:
968 ||
969 || In Arguments:
970 || Transaction details that need to be saved to transaction table
971 ||
972 || out nocopy Arguments:
973 || None.
974 ||
975 || In out nocopy Arguments:
976 ||
977 || Post Success:
978 || Writes to transaction table
979 ||
980 || Post Failure:
981 || Raises an exception
982 ||
983 || Access Status:
984 || Public
985 ||
986 ||===========================================================================
987 */
988 PROCEDURE process_save
989 (p_item_type in wf_items.item_type%TYPE
990 ,p_item_key in wf_items.item_key%TYPE
991 ,p_actid in varchar2
992 ,p_effective_date in varchar2 default hr_api.g_varchar2
993 ,p_period_of_service_id in varchar2 default hr_api.g_varchar2
994 ,p_object_version_number in varchar2 default hr_api.g_varchar2
995 ,p_actual_termination_date in varchar2 default hr_api.g_varchar2
996 ,p_notified_termination_date in varchar2 default hr_api.g_varchar2
997 ,p_leaving_reason in varchar2 default hr_api.g_varchar2
998 ,p_comments in varchar2 default hr_api.g_varchar2
999 ,p_login_person_id in number
1000 ,p_person_id in number
1001 ,p_attribute_category in varchar2 default hr_api.g_varchar2
1002 ,p_attribute1 in varchar2 default hr_api.g_varchar2
1003 ,p_attribute2 in varchar2 default hr_api.g_varchar2
1004 ,p_attribute3 in varchar2 default hr_api.g_varchar2
1005 ,p_attribute4 in varchar2 default hr_api.g_varchar2
1006 ,p_attribute5 in varchar2 default hr_api.g_varchar2
1007 ,p_attribute6 in varchar2 default hr_api.g_varchar2
1008 ,p_attribute7 in varchar2 default hr_api.g_varchar2
1009 ,p_attribute8 in varchar2 default hr_api.g_varchar2
1010 ,p_attribute9 in varchar2 default hr_api.g_varchar2
1011 ,p_attribute10 in varchar2 default hr_api.g_varchar2
1012 ,p_attribute11 in varchar2 default hr_api.g_varchar2
1013 ,p_attribute12 in varchar2 default hr_api.g_varchar2
1014 ,p_attribute13 in varchar2 default hr_api.g_varchar2
1015 ,p_attribute14 in varchar2 default hr_api.g_varchar2
1016 ,p_attribute15 in varchar2 default hr_api.g_varchar2
1017 ,p_attribute16 in varchar2 default hr_api.g_varchar2
1018 ,p_attribute17 in varchar2 default hr_api.g_varchar2
1019 ,p_attribute18 in varchar2 default hr_api.g_varchar2
1020 ,p_attribute19 in varchar2 default hr_api.g_varchar2
1021 ,p_attribute20 in varchar2 default hr_api.g_varchar2
1022 ,p_review_proc_call in varchar2 default hr_api.g_varchar2
1023 ,p_pds_information_category in varchar2 default hr_api.g_varchar2
1024 ,p_pds_information1 in varchar2 default hr_api.g_varchar2
1025 ,p_pds_information2 in varchar2 default hr_api.g_varchar2
1026 ,p_pds_information3 in varchar2 default hr_api.g_varchar2
1027 ,p_pds_information4 in varchar2 default hr_api.g_varchar2
1028 ,p_pds_information5 in varchar2 default hr_api.g_varchar2
1029 ,p_pds_information6 in varchar2 default hr_api.g_varchar2
1030 ,p_pds_information7 in varchar2 default hr_api.g_varchar2
1031 ,p_pds_information8 in varchar2 default hr_api.g_varchar2
1032 ,p_pds_information9 in varchar2 default hr_api.g_varchar2
1033 ,p_pds_information10 in varchar2 default hr_api.g_varchar2
1034 ,p_pds_information11 in varchar2 default hr_api.g_varchar2
1035 ,p_pds_information12 in varchar2 default hr_api.g_varchar2
1036 ,p_pds_information13 in varchar2 default hr_api.g_varchar2
1037 ,p_pds_information14 in varchar2 default hr_api.g_varchar2
1038 ,p_pds_information15 in varchar2 default hr_api.g_varchar2
1039 ,p_pds_information16 in varchar2 default hr_api.g_varchar2
1040 ,p_pds_information17 in varchar2 default hr_api.g_varchar2
1041 ,p_pds_information18 in varchar2 default hr_api.g_varchar2
1042 ,p_pds_information19 in varchar2 default hr_api.g_varchar2
1043 ,p_pds_information20 in varchar2 default hr_api.g_varchar2
1044 ,p_pds_information21 in varchar2 default hr_api.g_varchar2
1045 ,p_pds_information22 in varchar2 default hr_api.g_varchar2
1046 ,p_pds_information23 in varchar2 default hr_api.g_varchar2
1047 ,p_pds_information24 in varchar2 default hr_api.g_varchar2
1048 ,p_pds_information25 in varchar2 default hr_api.g_varchar2
1049 ,p_pds_information26 in varchar2 default hr_api.g_varchar2
1050 ,p_pds_information27 in varchar2 default hr_api.g_varchar2
1051 ,p_pds_information28 in varchar2 default hr_api.g_varchar2
1052 ,p_pds_information29 in varchar2 default hr_api.g_varchar2
1053 ,p_pds_information30 in varchar2 default hr_api.g_varchar2
1054 ,p_person_type_id in number default hr_api.g_number
1055 ,p_assignment_status_type_id in number default hr_api.g_number
1056 ,p_effective_date_option in varchar2 default hr_api.g_varchar2
1057 ,p_rehire_recommendation in varchar2 default hr_api.g_varchar2
1058 ,p_rehire_reason in varchar2 default hr_api.g_varchar2
1059 ,p_last_standard_process_date in varchar2 default hr_api.g_varchar2
1060 ,p_projected_termination_date in varchar2 default hr_api.g_varchar2
1061 ,p_final_process_date in varchar2 default hr_api.g_varchar2
1062 )
1063
1064 IS
1065
1066 li_count INTEGER ;
1067 lv_activity_name wf_item_activity_statuses_v.activity_name%TYPE;
1068 ln_transaction_id NUMBER;
1069 lv_result VARCHAR2(100);
1070 ln_ovn hr_api_transaction_steps.object_version_number%TYPE;
1071 ltt_trans_step_ids hr_util_web.g_varchar2_tab_type;
1072 ln_transaction_step_id hr_api_transaction_steps.transaction_step_id%TYPE;
1073 ltt_trans_obj_vers_num hr_util_web.g_varchar2_tab_type;
1074 ln_trans_step_rows NUMBER default 0;
1075 ln_supervisor_count NUMBER;
1076
1077 ItemType varchar2(200);
1078 ItemKey varchar2(200);
1079 ActivityId varchar2(200);
1080 isQkFn varchar2(10) default 'N';
1081 isSupAsgPGExists varchar2(10) default 'N';
1082 l_old_sup_person_id number;
1083 l_term_date date;
1084 l_login_person_id number;
1085 l_old_sup_asg_id number;
1086 l_old_sup_bg_id number;
1087 l_new_sup_person_id number;
1088 l_new_sup_asg_id number;
1089 l_validate varchar2(10);
1090 l_proc constant varchar2(100) := g_package || ' process_save';
1091
1092 BEGIN
1093
1094 hr_utility.set_location('Entering: '|| l_proc,5);
1095 ----------------------------------------------------------------------
1096 -- Save data to transaction table.
1097 ----------------------------------------------------------------------
1098
1099 li_count := 1;
1100 hr_termination_ss.gtt_transaction_steps(li_count).param_name
1101 := 'P_ACTUAL_TERMINATION_DATE';
1102 hr_termination_ss.gtt_transaction_steps(li_count).param_value
1103 := p_actual_termination_date;
1104 hr_termination_ss.gtt_transaction_steps(li_count).param_data_type
1105 := 'DATE';
1106
1107 li_count := li_count + 1;
1108 hr_termination_ss.gtt_transaction_steps(li_count).param_name
1109 := 'P_PERIOD_OF_SERVICE_ID';
1110 hr_termination_ss.gtt_transaction_steps(li_count).param_value
1111 := p_period_of_service_id;
1112 hr_termination_ss.gtt_transaction_steps(li_count).param_data_type
1113 := 'NUMBER';
1114
1115 li_count := li_count + 1;
1116 hr_termination_ss.gtt_transaction_steps(li_count).param_name
1117 := 'P_PERSON_ID';
1118 hr_termination_ss.gtt_transaction_steps(li_count).param_value
1119 := p_person_id;
1120 hr_termination_ss.gtt_transaction_steps(li_count).param_data_type
1121 := 'NUMBER';
1122
1123 li_count := li_count + 1;
1124 hr_termination_ss.gtt_transaction_steps(li_count).param_name
1125 := 'P_OBJECT_VERSION_NUMBER';
1126 hr_termination_ss.gtt_transaction_steps(li_count).param_value
1127 := p_object_version_number;
1128 hr_termination_ss.gtt_transaction_steps(li_count).param_data_type
1129 := 'NUMBER';
1130
1131 li_count := li_count + 1;
1132 hr_termination_ss.gtt_transaction_steps(li_count).param_name
1133 := 'P_LEAVING_REASON';
1134 hr_termination_ss.gtt_transaction_steps(li_count).param_value
1135 := p_leaving_reason;
1136 hr_termination_ss.gtt_transaction_steps(li_count).param_data_type
1137 := 'VARCHAR2';
1138
1139 li_count := li_count + 1;
1140 hr_termination_ss.gtt_transaction_steps(li_count).param_name
1141 := 'P_PERSON_TYPE_ID';
1142 hr_termination_ss.gtt_transaction_steps(li_count).param_value
1143 := p_person_type_id;
1144 hr_termination_ss.gtt_transaction_steps(li_count).param_data_type
1145 := 'NUMBER';
1146
1147 li_count := li_count + 1;
1148 hr_termination_ss.gtt_transaction_steps(li_count).param_name
1149 := 'P_ASSIGNMENT_STATUS_TYPE_ID';
1150 hr_termination_ss.gtt_transaction_steps(li_count).param_value
1151 := p_assignment_status_type_id;
1152 hr_termination_ss.gtt_transaction_steps(li_count).param_data_type
1153 := 'NUMBER';
1154
1155 li_count := li_count + 1;
1156 hr_termination_ss.gtt_transaction_steps(li_count).param_name
1157 := 'P_REHIRE_RECOMMENDATION';
1158 hr_termination_ss.gtt_transaction_steps(li_count).param_value
1159 := p_rehire_recommendation;
1160 hr_termination_ss.gtt_transaction_steps(li_count).param_data_type
1161 := 'VARCHAR2';
1162
1163 li_count := li_count + 1;
1164 hr_termination_ss.gtt_transaction_steps(li_count).param_name
1165 := 'P_REHIRE_REASON';
1166 hr_termination_ss.gtt_transaction_steps(li_count).param_value
1167 := p_rehire_reason;
1168 hr_termination_ss.gtt_transaction_steps(li_count).param_data_type
1169 := 'VARCHAR2';
1170
1171 li_count := li_count + 1;
1172 hr_termination_ss.gtt_transaction_steps(li_count).param_name
1173 := 'P_NOTIFIED_TERMINATION_DATE';
1174 hr_termination_ss.gtt_transaction_steps(li_count).param_value
1175 := p_notified_termination_date;
1176 hr_termination_ss.gtt_transaction_steps(li_count).param_data_type
1177 := 'DATE';
1178
1179 li_count := li_count + 1;
1180 hr_termination_ss.gtt_transaction_steps(li_count).param_name
1181 := 'P_LAST_STANDARD_PROCESS_DATE';
1182 hr_termination_ss.gtt_transaction_steps(li_count).param_value
1183 := p_last_standard_process_date;
1184 hr_termination_ss.gtt_transaction_steps(li_count).param_data_type
1185 := 'DATE';
1186
1187 li_count := li_count + 1;
1188 hr_termination_ss.gtt_transaction_steps(li_count).param_name
1189 := 'P_PROJECTED_TERMINATION_DATE';
1190 hr_termination_ss.gtt_transaction_steps(li_count).param_value
1191 := p_projected_termination_date;
1192 hr_termination_ss.gtt_transaction_steps(li_count).param_data_type
1193 := 'DATE';
1194
1195 li_count := li_count + 1;
1196 hr_termination_ss.gtt_transaction_steps(li_count).param_name
1197 := 'P_FINAL_PROCESS_DATE';
1198 hr_termination_ss.gtt_transaction_steps(li_count).param_value
1199 := p_final_process_date;
1200 hr_termination_ss.gtt_transaction_steps(li_count).param_data_type
1201 := 'DATE';
1202
1203 li_count := li_count + 1;
1204 hr_termination_ss.gtt_transaction_steps(li_count).param_name
1205 := 'P_COMMENTS';
1206 hr_termination_ss.gtt_transaction_steps(li_count).param_value
1207 := p_comments;
1208 hr_termination_ss.gtt_transaction_steps(li_count).param_data_type
1209 := 'VARCHAR2';
1210
1211 ----------------------------------------------------------------------
1212 -- DDF repeat 20 times
1213 ----------------------------------------------------------------------
1214
1215 li_count := li_count + 1;
1216 hr_termination_ss.gtt_transaction_steps(li_count).param_name
1217 := 'P_ATTRIBUTE_CATEGORY';
1218 hr_termination_ss.gtt_transaction_steps(li_count).param_value
1219 := p_attribute_category;
1220 hr_termination_ss.gtt_transaction_steps(li_count).param_data_type
1221 := 'VARCHAR2';
1222
1223 li_count := li_count + 1;
1224 hr_termination_ss.gtt_transaction_steps(li_count).param_name
1225 := 'P_ATTRIBUTE1';
1226 hr_termination_ss.gtt_transaction_steps(li_count).param_value
1227 := p_attribute1;
1228 hr_termination_ss.gtt_transaction_steps(li_count).param_data_type
1229 := 'VARCHAR2';
1230
1231 li_count := li_count + 1;
1232 hr_termination_ss.gtt_transaction_steps(li_count).param_name
1233 := 'P_ATTRIBUTE2';
1234 hr_termination_ss.gtt_transaction_steps(li_count).param_value
1235 := p_attribute2;
1236 hr_termination_ss.gtt_transaction_steps(li_count).param_data_type
1237 := 'VARCHAR2';
1238
1239 li_count := li_count + 1;
1240 hr_termination_ss.gtt_transaction_steps(li_count).param_name
1241 := 'P_ATTRIBUTE3';
1242 hr_termination_ss.gtt_transaction_steps(li_count).param_value
1243 := p_attribute3;
1244 hr_termination_ss.gtt_transaction_steps(li_count).param_data_type
1245 := 'VARCHAR2';
1246
1247 li_count := li_count + 1;
1248 hr_termination_ss.gtt_transaction_steps(li_count).param_name
1249 := 'P_ATTRIBUTE4';
1250 hr_termination_ss.gtt_transaction_steps(li_count).param_value
1251 := p_attribute4;
1252 hr_termination_ss.gtt_transaction_steps(li_count).param_data_type
1253 := 'VARCHAR2';
1254
1255 li_count := li_count + 1;
1256 hr_termination_ss.gtt_transaction_steps(li_count).param_name
1257 := 'P_ATTRIBUTE5';
1258 hr_termination_ss.gtt_transaction_steps(li_count).param_value
1259 := p_attribute5;
1260 hr_termination_ss.gtt_transaction_steps(li_count).param_data_type
1261 := 'VARCHAR2';
1262
1263 li_count := li_count + 1;
1264 hr_termination_ss.gtt_transaction_steps(li_count).param_name
1265 := 'P_ATTRIBUTE6';
1266 hr_termination_ss.gtt_transaction_steps(li_count).param_value
1267 := p_attribute6;
1268 hr_termination_ss.gtt_transaction_steps(li_count).param_data_type
1269 := 'VARCHAR2';
1270
1271 li_count := li_count + 1;
1272 hr_termination_ss.gtt_transaction_steps(li_count).param_name
1273 := 'P_ATTRIBUTE7';
1274 hr_termination_ss.gtt_transaction_steps(li_count).param_value
1275 := p_attribute7;
1276 hr_termination_ss.gtt_transaction_steps(li_count).param_data_type
1277 := 'VARCHAR2';
1278
1279 li_count := li_count + 1;
1280 hr_termination_ss.gtt_transaction_steps(li_count).param_name
1281 := 'P_ATTRIBUTE8';
1282 hr_termination_ss.gtt_transaction_steps(li_count).param_value
1283 := p_attribute8;
1284 hr_termination_ss.gtt_transaction_steps(li_count).param_data_type
1285 := 'VARCHAR2';
1286
1287 li_count := li_count + 1;
1288 hr_termination_ss.gtt_transaction_steps(li_count).param_name
1289 := 'P_ATTRIBUTE9';
1290 hr_termination_ss.gtt_transaction_steps(li_count).param_value
1291 := p_attribute9;
1292 hr_termination_ss.gtt_transaction_steps(li_count).param_data_type
1293 := 'VARCHAR2';
1294
1295 li_count := li_count + 1;
1296 hr_termination_ss.gtt_transaction_steps(li_count).param_name
1297 := 'P_ATTRIBUTE10';
1298 hr_termination_ss.gtt_transaction_steps(li_count).param_value
1299 := p_attribute10;
1300 hr_termination_ss.gtt_transaction_steps(li_count).param_data_type
1301 := 'VARCHAR2';
1302
1303 li_count := li_count + 1;
1304 hr_termination_ss.gtt_transaction_steps(li_count).param_name
1305 := 'P_ATTRIBUTE11';
1306 hr_termination_ss.gtt_transaction_steps(li_count).param_value
1307 := p_attribute11;
1308 hr_termination_ss.gtt_transaction_steps(li_count).param_data_type
1309 := 'VARCHAR2';
1310
1311 li_count := li_count + 1;
1312 hr_termination_ss.gtt_transaction_steps(li_count).param_name
1313 := 'P_ATTRIBUTE12';
1314 hr_termination_ss.gtt_transaction_steps(li_count).param_value
1315 := p_attribute12;
1316 hr_termination_ss.gtt_transaction_steps(li_count).param_data_type
1317 := 'VARCHAR2';
1318
1319 li_count := li_count + 1;
1320 hr_termination_ss.gtt_transaction_steps(li_count).param_name
1321 := 'P_ATTRIBUTE13';
1322 hr_termination_ss.gtt_transaction_steps(li_count).param_value
1323 := p_attribute13;
1324 hr_termination_ss.gtt_transaction_steps(li_count).param_data_type
1325 := 'VARCHAR2';
1326
1327 li_count := li_count + 1;
1328 hr_termination_ss.gtt_transaction_steps(li_count).param_name
1329 := 'P_ATTRIBUTE14';
1330 hr_termination_ss.gtt_transaction_steps(li_count).param_value
1331 := p_attribute14;
1332 hr_termination_ss.gtt_transaction_steps(li_count).param_data_type
1333 := 'VARCHAR2';
1334
1335 li_count := li_count + 1;
1336 hr_termination_ss.gtt_transaction_steps(li_count).param_name
1337 := 'P_ATTRIBUTE15';
1338 hr_termination_ss.gtt_transaction_steps(li_count).param_value
1339 := p_attribute15;
1340 hr_termination_ss.gtt_transaction_steps(li_count).param_data_type
1341 := 'VARCHAR2';
1342
1343 li_count := li_count + 1;
1344 hr_termination_ss.gtt_transaction_steps(li_count).param_name
1345 := 'P_ATTRIBUTE16';
1346 hr_termination_ss.gtt_transaction_steps(li_count).param_value
1347 := p_attribute16;
1348 hr_termination_ss.gtt_transaction_steps(li_count).param_data_type
1349 := 'VARCHAR2';
1350
1351 li_count := li_count + 1;
1352 hr_termination_ss.gtt_transaction_steps(li_count).param_name
1353 := 'P_ATTRIBUTE17';
1354 hr_termination_ss.gtt_transaction_steps(li_count).param_value
1355 := p_attribute17;
1356 hr_termination_ss.gtt_transaction_steps(li_count).param_data_type
1357 := 'VARCHAR2';
1358
1359 li_count := li_count + 1;
1360 hr_termination_ss.gtt_transaction_steps(li_count).param_name
1361 := 'P_ATTRIBUTE18';
1362 hr_termination_ss.gtt_transaction_steps(li_count).param_value
1363 := p_attribute18;
1364 hr_termination_ss.gtt_transaction_steps(li_count).param_data_type
1365 := 'VARCHAR2';
1366
1367 li_count := li_count + 1;
1368 hr_termination_ss.gtt_transaction_steps(li_count).param_name
1369 := 'P_ATTRIBUTE19';
1370 hr_termination_ss.gtt_transaction_steps(li_count).param_value
1371 := p_attribute19;
1372 hr_termination_ss.gtt_transaction_steps(li_count).param_data_type
1373 := 'VARCHAR2';
1374
1375 li_count := li_count + 1;
1376 hr_termination_ss.gtt_transaction_steps(li_count).param_name
1377 := 'P_ATTRIBUTE20';
1378 hr_termination_ss.gtt_transaction_steps(li_count).param_value
1379 := p_attribute20;
1380 hr_termination_ss.gtt_transaction_steps(li_count).param_data_type
1381 := 'VARCHAR2';
1382
1383 ----------------------------------------------------------------------
1384 -- Store the activity internal name for this particular
1385 -- activity with other information.
1386 ----------------------------------------------------------------------
1387 lv_activity_name := hr_termination_ss.gv_TERMINATION_ACTIVITY_NAME;
1388 li_count := li_count + 1;
1389 hr_termination_ss.gtt_transaction_steps(li_count).param_name
1390 := 'P_ACTIVITY_NAME';
1391 hr_termination_ss.gtt_transaction_steps(li_count).param_value
1392 := lv_activity_name;
1393 hr_termination_ss.gtt_transaction_steps(li_count).param_data_type
1394 := 'VARCHAR2';
1395
1396 ----------------------------------------------------------------------
1397 -- Store the the Review Procedure Call and
1398 -- activity id with other information.
1399 ----------------------------------------------------------------------
1400 li_count := li_count + 1;
1401 hr_termination_ss.gtt_transaction_steps(li_count).param_name
1402 := 'P_REVIEW_PROC_CALL';
1403 hr_termination_ss.gtt_transaction_steps(li_count).param_value
1404 := p_review_proc_call;
1405 hr_termination_ss.gtt_transaction_steps(li_count).param_data_type
1406 := 'VARCHAR2';
1407
1408 li_count := li_count + 1;
1409 hr_termination_ss.gtt_transaction_steps(li_count).param_name
1410 := 'P_REVIEW_ACTID';
1411 hr_termination_ss.gtt_transaction_steps(li_count).param_value
1412 := p_actid;
1413 hr_termination_ss.gtt_transaction_steps(li_count).param_data_type
1414 := 'VARCHAR2';
1415
1416 ---------------------------------------------------------------------
1417 -- DDF Enhancement
1418 -- Store DDF Segments
1419 ---------------------------------------------------------------------
1420
1421 li_count := li_count + 1;
1422 hr_termination_ss.gtt_transaction_steps(li_count).param_name
1423 := 'P_PDS_INFORMATION_CATEGORY';
1424 hr_termination_ss.gtt_transaction_steps(li_count).param_value
1425 := p_pds_information_category;
1426 hr_termination_ss.gtt_transaction_steps(li_count).param_data_type
1427 := 'VARCHAR2';
1428
1429 li_count := li_count + 1;
1430 hr_termination_ss.gtt_transaction_steps(li_count).param_name
1431 := 'P_PDS_INFORMATION1';
1432 hr_termination_ss.gtt_transaction_steps(li_count).param_value
1433 := p_pds_information1;
1434 hr_termination_ss.gtt_transaction_steps(li_count).param_data_type
1435 := 'VARCHAR2';
1436
1437 li_count := li_count + 1;
1438 hr_termination_ss.gtt_transaction_steps(li_count).param_name
1439 := 'P_PDS_INFORMATION2';
1440 hr_termination_ss.gtt_transaction_steps(li_count).param_value
1441 := p_pds_information2;
1442 hr_termination_ss.gtt_transaction_steps(li_count).param_data_type
1443 := 'VARCHAR2';
1444
1445 li_count := li_count + 1;
1446 hr_termination_ss.gtt_transaction_steps(li_count).param_name
1447 := 'P_PDS_INFORMATION3';
1448 hr_termination_ss.gtt_transaction_steps(li_count).param_value
1449 := p_pds_information3;
1450 hr_termination_ss.gtt_transaction_steps(li_count).param_data_type
1451 := 'VARCHAR2';
1452
1453 li_count := li_count + 1;
1454 hr_termination_ss.gtt_transaction_steps(li_count).param_name
1455 := 'P_PDS_INFORMATION4';
1456 hr_termination_ss.gtt_transaction_steps(li_count).param_value
1457 := p_pds_information4;
1458 hr_termination_ss.gtt_transaction_steps(li_count).param_data_type
1459 := 'VARCHAR2';
1460
1461 li_count := li_count + 1;
1462 hr_termination_ss.gtt_transaction_steps(li_count).param_name
1463 := 'P_PDS_INFORMATION5';
1464 hr_termination_ss.gtt_transaction_steps(li_count).param_value
1465 := p_pds_information5;
1466 hr_termination_ss.gtt_transaction_steps(li_count).param_data_type
1467 := 'VARCHAR2';
1468
1469 li_count := li_count + 1;
1470 hr_termination_ss.gtt_transaction_steps(li_count).param_name
1471 := 'P_PDS_INFORMATION6';
1472 hr_termination_ss.gtt_transaction_steps(li_count).param_value
1473 := p_pds_information6;
1474 hr_termination_ss.gtt_transaction_steps(li_count).param_data_type
1475 := 'VARCHAR2';
1476
1477 li_count := li_count + 1;
1478 hr_termination_ss.gtt_transaction_steps(li_count).param_name
1479 := 'P_PDS_INFORMATION7';
1480 hr_termination_ss.gtt_transaction_steps(li_count).param_value
1481 := p_pds_information7;
1482 hr_termination_ss.gtt_transaction_steps(li_count).param_data_type
1483 := 'VARCHAR2';
1484
1485 li_count := li_count + 1;
1486 hr_termination_ss.gtt_transaction_steps(li_count).param_name
1487 := 'P_PDS_INFORMATION8';
1488 hr_termination_ss.gtt_transaction_steps(li_count).param_value
1489 := p_pds_information8;
1490 hr_termination_ss.gtt_transaction_steps(li_count).param_data_type
1491 := 'VARCHAR2';
1492
1493 li_count := li_count + 1;
1494 hr_termination_ss.gtt_transaction_steps(li_count).param_name
1495 := 'P_PDS_INFORMATION9';
1496 hr_termination_ss.gtt_transaction_steps(li_count).param_value
1497 := p_pds_information9;
1498 hr_termination_ss.gtt_transaction_steps(li_count).param_data_type
1499 := 'VARCHAR2';
1500
1501 li_count := li_count + 1;
1502 hr_termination_ss.gtt_transaction_steps(li_count).param_name
1503 := 'P_PDS_INFORMATION10';
1504 hr_termination_ss.gtt_transaction_steps(li_count).param_value
1505 := p_pds_information10;
1506 hr_termination_ss.gtt_transaction_steps(li_count).param_data_type
1507 := 'VARCHAR2';
1508
1509 li_count := li_count + 1;
1510 hr_termination_ss.gtt_transaction_steps(li_count).param_name
1511 := 'P_PDS_INFORMATION11';
1512 hr_termination_ss.gtt_transaction_steps(li_count).param_value
1513 := p_pds_information11;
1514 hr_termination_ss.gtt_transaction_steps(li_count).param_data_type
1515 := 'VARCHAR2';
1516
1517 li_count := li_count + 1;
1518 hr_termination_ss.gtt_transaction_steps(li_count).param_name
1519 := 'P_PDS_INFORMATION12';
1520 hr_termination_ss.gtt_transaction_steps(li_count).param_value
1521 := p_pds_information12;
1522 hr_termination_ss.gtt_transaction_steps(li_count).param_data_type
1523 := 'VARCHAR2';
1524
1525 li_count := li_count + 1;
1526 hr_termination_ss.gtt_transaction_steps(li_count).param_name
1527 := 'P_PDS_INFORMATION13';
1528 hr_termination_ss.gtt_transaction_steps(li_count).param_value
1529 := p_pds_information13;
1530 hr_termination_ss.gtt_transaction_steps(li_count).param_data_type
1531 := 'VARCHAR2';
1532
1533 li_count := li_count + 1;
1534 hr_termination_ss.gtt_transaction_steps(li_count).param_name
1535 := 'P_PDS_INFORMATION14';
1536 hr_termination_ss.gtt_transaction_steps(li_count).param_value
1537 := p_pds_information14;
1538 hr_termination_ss.gtt_transaction_steps(li_count).param_data_type
1539 := 'VARCHAR2';
1540
1541 li_count := li_count + 1;
1542 hr_termination_ss.gtt_transaction_steps(li_count).param_name
1543 := 'P_PDS_INFORMATION15';
1544 hr_termination_ss.gtt_transaction_steps(li_count).param_value
1545 := p_pds_information15;
1546 hr_termination_ss.gtt_transaction_steps(li_count).param_data_type
1547 := 'VARCHAR2';
1548
1549 li_count := li_count + 1;
1550 hr_termination_ss.gtt_transaction_steps(li_count).param_name
1551 := 'P_PDS_INFORMATION16';
1552 hr_termination_ss.gtt_transaction_steps(li_count).param_value
1553 := p_pds_information16;
1554 hr_termination_ss.gtt_transaction_steps(li_count).param_data_type
1555 := 'VARCHAR2';
1556
1557 li_count := li_count + 1;
1558 hr_termination_ss.gtt_transaction_steps(li_count).param_name
1559 := 'P_PDS_INFORMATION17';
1560 hr_termination_ss.gtt_transaction_steps(li_count).param_value
1561 := p_pds_information17;
1562 hr_termination_ss.gtt_transaction_steps(li_count).param_data_type
1563 := 'VARCHAR2';
1564
1565 li_count := li_count + 1;
1566 hr_termination_ss.gtt_transaction_steps(li_count).param_name
1567 := 'P_PDS_INFORMATION18';
1568 hr_termination_ss.gtt_transaction_steps(li_count).param_value
1569 := p_pds_information18;
1570 hr_termination_ss.gtt_transaction_steps(li_count).param_data_type
1571 := 'VARCHAR2';
1572
1573 li_count := li_count + 1;
1574 hr_termination_ss.gtt_transaction_steps(li_count).param_name
1575 := 'P_PDS_INFORMATION19';
1576 hr_termination_ss.gtt_transaction_steps(li_count).param_value
1577 := p_pds_information19;
1578 hr_termination_ss.gtt_transaction_steps(li_count).param_data_type
1579 := 'VARCHAR2';
1580
1581 li_count := li_count + 1;
1582 hr_termination_ss.gtt_transaction_steps(li_count).param_name
1583 := 'P_PDS_INFORMATION20';
1584 hr_termination_ss.gtt_transaction_steps(li_count).param_value
1585 := p_pds_information20;
1586 hr_termination_ss.gtt_transaction_steps(li_count).param_data_type
1587 := 'VARCHAR2';
1588
1589 li_count := li_count + 1;
1590 hr_termination_ss.gtt_transaction_steps(li_count).param_name
1591 := 'P_PDS_INFORMATION21';
1592 hr_termination_ss.gtt_transaction_steps(li_count).param_value
1593 := p_pds_information21;
1594 hr_termination_ss.gtt_transaction_steps(li_count).param_data_type
1595 := 'VARCHAR2';
1596
1597 li_count := li_count + 1;
1598 hr_termination_ss.gtt_transaction_steps(li_count).param_name
1599 := 'P_PDS_INFORMATION22';
1600 hr_termination_ss.gtt_transaction_steps(li_count).param_value
1601 := p_pds_information22;
1602 hr_termination_ss.gtt_transaction_steps(li_count).param_data_type
1603 := 'VARCHAR2';
1604
1605 li_count := li_count + 1;
1606 hr_termination_ss.gtt_transaction_steps(li_count).param_name
1607 := 'P_PDS_INFORMATION23';
1608 hr_termination_ss.gtt_transaction_steps(li_count).param_value
1609 := p_pds_information23;
1610 hr_termination_ss.gtt_transaction_steps(li_count).param_data_type
1611 := 'VARCHAR2';
1612
1613 li_count := li_count + 1;
1614 hr_termination_ss.gtt_transaction_steps(li_count).param_name
1615 := 'P_PDS_INFORMATION24';
1616 hr_termination_ss.gtt_transaction_steps(li_count).param_value
1617 := p_pds_information24;
1618 hr_termination_ss.gtt_transaction_steps(li_count).param_data_type
1619 := 'VARCHAR2';
1620
1621 li_count := li_count + 1;
1622 hr_termination_ss.gtt_transaction_steps(li_count).param_name
1623 := 'P_PDS_INFORMATION25';
1624 hr_termination_ss.gtt_transaction_steps(li_count).param_value
1625 := p_pds_information25;
1626 hr_termination_ss.gtt_transaction_steps(li_count).param_data_type
1627 := 'VARCHAR2';
1628
1629 li_count := li_count + 1;
1630 hr_termination_ss.gtt_transaction_steps(li_count).param_name
1631 := 'P_PDS_INFORMATION26';
1632 hr_termination_ss.gtt_transaction_steps(li_count).param_value
1633 := p_pds_information26;
1634 hr_termination_ss.gtt_transaction_steps(li_count).param_data_type
1635 := 'VARCHAR2';
1636
1637 li_count := li_count + 1;
1638 hr_termination_ss.gtt_transaction_steps(li_count).param_name
1639 := 'P_PDS_INFORMATION27';
1640 hr_termination_ss.gtt_transaction_steps(li_count).param_value
1641 := p_pds_information27;
1642 hr_termination_ss.gtt_transaction_steps(li_count).param_data_type
1643 := 'VARCHAR2';
1644
1645 li_count := li_count + 1;
1646 hr_termination_ss.gtt_transaction_steps(li_count).param_name
1647 := 'P_PDS_INFORMATION28';
1648 hr_termination_ss.gtt_transaction_steps(li_count).param_value
1649 := p_pds_information28;
1650 hr_termination_ss.gtt_transaction_steps(li_count).param_data_type
1651 := 'VARCHAR2';
1652
1653 li_count := li_count + 1;
1654 hr_termination_ss.gtt_transaction_steps(li_count).param_name
1655 := 'P_PDS_INFORMATION29';
1656 hr_termination_ss.gtt_transaction_steps(li_count).param_value
1657 := p_pds_information29;
1658 hr_termination_ss.gtt_transaction_steps(li_count).param_data_type
1659 := 'VARCHAR2';
1660
1661 li_count := li_count + 1;
1662 hr_termination_ss.gtt_transaction_steps(li_count).param_name
1663 := 'P_PDS_INFORMATION30';
1664 hr_termination_ss.gtt_transaction_steps(li_count).param_value
1665 := p_pds_information30;
1666 hr_termination_ss.gtt_transaction_steps(li_count).param_data_type
1667 := 'VARCHAR2';
1668 ---------------------------------------------------------------------
1669 -- Check if there is already a transaction for this process?
1670 ---------------------------------------------------------------------
1671 ln_transaction_id := hr_transaction_ss.get_transaction_id (
1672 p_Item_Type => p_item_type,
1673 p_Item_Key => p_item_key
1674 );
1675
1676 IF ln_transaction_id IS NULL
1677 THEN
1678 hr_utility.trace('In (iF ln_transaction_id IS NULL): '|| l_proc);
1679 -------------------------------------------------------------------
1680 -- Create a new transaction
1681 -------------------------------------------------------------------
1682 hr_transaction_ss.start_transaction (
1683 itemtype => p_item_type,
1684 itemkey => p_item_key,
1685 actid => TO_NUMBER(p_actid),
1686 funmode => 'RUN',
1687 p_effective_date_option => p_effective_date_option,
1688 p_login_person_id => p_login_person_id,
1689 result => lv_result
1690 );
1691
1692 ln_transaction_id := hr_transaction_ss.get_transaction_id (
1693 p_Item_Type => p_item_type,
1694 p_Item_Key => p_item_key
1695 );
1696 END IF;
1697
1698 ---------------------------------------------------------------------
1699 -- There is already a transaction for this process.
1700 -- Retieve the transaction step for this current
1701 -- activity. We will update this transaction step with
1702 -- the new information.
1703 ---------------------------------------------------------------------
1704 hr_transaction_api.get_transaction_step_info (
1705 p_Item_Type => p_item_type,
1706 p_Item_Key => p_item_key,
1707 p_activity_id => to_number(p_actid),
1708 p_transaction_step_id => ltt_trans_step_ids,
1709 p_object_version_number => ltt_trans_obj_vers_num,
1710 p_rows => ln_trans_step_rows
1711 );
1712
1713 IF ln_trans_step_rows < 1
1714 THEN
1715 hr_utility.trace('In (IF ln_trans_step_rows < 1): '|| l_proc);
1716 --------------------------------------------------------------------
1717 -- There is no transaction step for this transaction.
1718 -- Create a step within this new transaction
1719 --------------------------------------------------------------------
1720 hr_transaction_api.create_transaction_step (
1721 p_validate => false,
1722 p_creator_person_id => p_login_person_id,
1723 p_transaction_id => ln_transaction_id,
1724 p_api_name => g_package || '.PROCESS_API',
1725 p_Item_Type => p_item_type,
1726 p_Item_Key => p_item_key,
1727 p_activity_id => TO_NUMBER(p_actid),
1728 p_transaction_step_id => ln_transaction_step_id,
1729 p_object_version_number => ln_ovn
1730 );
1731 ELSE
1732 hr_utility.trace('In else of (IF ln_trans_step_rows < 1): '|| l_proc);
1733 --------------------------------------------------------------------
1734 -- There are transaction steps for this transaction.
1735 -- Get the Transaction Step ID for this activity.
1736 --------------------------------------------------------------------
1737 ln_transaction_step_id :=
1738 hr_transaction_ss.get_activity_trans_step_id (
1739 p_activity_name => lv_activity_name,
1740 p_trans_step_id_tbl => ltt_trans_step_ids
1741 );
1742
1743 END IF;
1744
1745 hr_transaction_ss.save_transaction_step (
1746 p_item_Type => p_item_type,
1747 p_item_Key => p_item_key,
1748 p_actid => TO_NUMBER(p_actid),
1749 p_login_person_id => p_login_person_id,
1750 p_transaction_step_id => ln_transaction_step_id,
1751 p_api_name => 'hr_termination_ss.process_save',
1752 p_transaction_data => hr_termination_ss.gtt_transaction_steps
1753 );
1754
1755 -- 07/16/2001 Bug 1853417 Fix:
1756 -- The code to check for existence of subordinates for the
1757 -- terminated employee has been moved to the new procedure
1758 -- branch_on_subordinate_presence.
1759
1760 -- Quick Termination changes start from here
1761
1762 if ( p_item_type is not null and p_item_key is not null ) then
1763
1764 isQKFn := wf_engine.getItemAttrText(p_item_type, p_item_key, 'HR_QUICK_FUNCTION', true);
1765
1766 if ( ActivityId is not null ) then
1767 isSupAsgPGExists := wf_engine.getActivityAttrText(p_item_type, p_item_key, p_actid, 'REASSIGN_DIRECTS');
1768 end if;
1769
1770 end if;
1771
1772 if (isQKFn = 'Y' and isSupAsgPGExists = 'Y' ) then
1773
1774 l_term_date := hr_transaction_api.get_date_value(ln_transaction_step_id, 'P_ACTUAL_TERMINATION_DATE');
1775
1776 l_old_sup_person_id := wf_engine.getItemAttrText(p_item_type, p_item_key, 'QK_TERM_OLD_SUP_PER_ID', true);
1777 l_old_sup_asg_id := wf_engine.getItemAttrText(p_item_type, p_item_key, 'QK_TERM_OLD_SUP_ASG_ID', true);
1778 l_old_sup_bg_id := wf_engine.getItemAttrText(p_item_type, p_item_key, 'QK_TERM_OLD_SUP_BG_ID', true);
1779 l_new_sup_person_id := wf_engine.getItemAttrText(p_item_type, p_item_key, 'QK_TERM_NEW_SUP_PER_ID', true);
1780 l_new_sup_asg_id := wf_engine.getItemAttrText(p_item_type, p_item_key, 'QK_TERM_NEW_SUP_ASG_ID', true);
1781 l_login_person_id := wf_engine.getItemAttrText(p_item_type, p_item_key, 'QK_TERM_LOGIN_PER_ID', true);
1782
1783
1784 if ( l_old_sup_person_id is not null and
1785 l_old_sup_asg_id is not null and
1786 l_old_sup_bg_id is not null and
1787 l_new_sup_person_id is not null and
1788 l_new_sup_asg_id is not null and
1789 l_login_person_id is not null
1790 ) then
1791
1792 hr_utility.trace('Called moveAllDirectReports from process_save');
1793
1794 moveAllDirectReports(
1795 p_validate => 'Y',
1796 p_item_type => p_item_type,
1797 p_item_key => p_item_key,
1798 p_old_sup_person_id => l_old_sup_person_id,
1799 p_old_sup_asg_id => l_old_sup_asg_id,
1800 p_old_sup_bg_id => l_old_sup_bg_id,
1801 p_new_sup_person_id => l_new_sup_person_id,
1802 p_new_sup_asg_id => l_new_sup_asg_id,
1803 p_login_person_id => l_login_person_id,
1804 p_term_date => to_char(l_term_date, 'YYYY-MM-DD'),
1805 p_flow_identifier => 'TERMINATION_FLOW');
1806 end if;
1807
1808 end if;
1809 -- Quick Termination changes ends here
1810
1811
1812 hr_utility.set_location('Leaving: '|| l_proc,20);
1813 EXCEPTION
1814 WHEN OTHERS THEN
1815 hr_utility.set_location('EXCEPTION: '|| l_proc,555);
1816 RAISE; -- Raise error here relevant to the new tech stack.
1817
1818 END process_save;
1819
1820 /*
1821 ||=======================================================================
1822 || PROCEDURE : process_api
1823 || DESCRIPTION : This procedure gets data stored in the transaction table
1824 || : and call the APIs in update mode
1825 ||=======================================================================
1826 */
1827 PROCEDURE process_api (
1828 p_validate IN BOOLEAN DEFAULT FALSE,
1829 p_transaction_step_id IN NUMBER DEFAULT NULL,
1830 p_effective_date IN VARCHAR2 DEFAULT NULL
1831 )
1832 IS
1833 lrt_termination hr_termination_ss.rt_termination;
1834 ld_actual_term_date
1835 per_periods_of_service.actual_termination_date%TYPE;
1836 lv_term_reason per_periods_of_service.leaving_reason%TYPE;
1837 ll_term_comments per_periods_of_service.comments%TYPE;
1838 ln_person_id per_all_people_f.person_id%TYPE;
1839 ln_period_of_service_id
1840 per_periods_of_service.period_of_service_id%TYPE;
1841 ln_object_version_number
1842 per_periods_of_service.object_version_number%TYPE;
1843 ------------------------------------------------------------------------
1844 -- out parameters required by API actual_termination_emp
1845 ------------------------------------------------------------------------
1846 ld_last_standard_process_date
1847 per_periods_of_service.last_standard_process_date%TYPE;
1848 ld_notified_term_date
1849 per_periods_of_service.notified_termination_date%TYPE;
1850
1851 ld_projected_termination_date
1852 per_periods_of_service.projected_termination_date%TYPE;
1853 ld_final_process_date
1854 per_periods_of_service.final_process_date%TYPE;
1855
1856 lb_supervisor_warning BOOLEAN;
1857 lb_event_warning BOOLEAN;
1858 lb_interview_warning BOOLEAN;
1859 lb_review_warning BOOLEAN;
1860 lb_recruiter_warning BOOLEAN;
1861 lb_asg_future_changes_warning BOOLEAN;
1862 lv_entries_changed_warning VARCHAR2(500);
1863 lb_pay_proposal_warning BOOLEAN;
1864 lb_dod_warning BOOLEAN;
1865
1866 --DFF
1867 lt_term_flex hr_termination_ss.t_flex_table;
1868 lv_attribute_category VARCHAR2(100);
1869
1870 --DDF Enhancement
1871 lv_pds_information_category VARCHAR2(100);
1872
1873 -- For SAVE_FOR_LATER
1874 ld_effective_date date default null;
1875
1876 lv_person_type_id per_person_types.person_type_id%TYPE;
1877 lv_assignment_status_type_id
1878 per_assignment_status_types.assignment_status_type_id%TYPE;
1879 lv_rehire_recommendation per_all_people_f.rehire_recommendation%TYPE;
1880 lv_rehire_reason per_all_people_f.rehire_reason%TYPE;
1881
1882 l_proc varchar2(100) := g_package ||'process_api';
1883
1884 -- Added for FR localization bug 2881583
1885 l_business_group_id number;
1886 l_legislation_code varchar2(30);
1887
1888 --set outparameter for final_process_emp
1889 l_org_now_no_manager_warning boolean;
1890 l_entries_changed_warning varchar2(1);
1891 l_asg_future_changes_warning boolean;
1892
1893 -- to print the error message
1894 l_err_msg long default null;
1895
1896 --
1897
1898 ItemType varchar2(200);
1899 ItemKey varchar2(200);
1900 ActivityId number;
1901 isQkFn varchar2(10) default 'N';
1902 isSupAsgPGExists varchar2(10) default 'N';
1903 l_old_sup_person_id number;
1904 l_term_date date;
1905 l_login_person_id number;
1906 l_old_sup_asg_id number;
1907 l_old_sup_bg_id number;
1908 l_new_sup_person_id number;
1909 l_new_sup_asg_id number;
1910 l_validate varchar2(10);
1911 --
1912
1913 BEGIN
1914
1915 hr_utility.set_location('Entering: ' || l_proc,5 );
1916
1917 -- The following is for SAVE_FOR_LATER code change.
1918 -- 1)When the Action page re-launch a suspended workflow process, it does
1919 -- a validation by calling the process_api with the new user entered
1920 -- effective date. Added a new parameter p_effective_date to this proc.
1921 -- If p_effective_date is not null, then use it as the effective date for
1922 -- api validation.
1923 -- 2)When the Action page re-launch a suspended workflow process, it
1924 -- allows user to enter a new effective date. We should not use the
1925 -- effective date that we saved in the transaction table.
1926 -- In process_api, if the p_effective_date parameter is null then use
1927 -- the workflow attribute P_EFFECTIVE_DATE as the effective date for api
1928 -- validation.
1929 --
1930 IF (p_effective_date is not null) THEN
1931 ld_effective_date:= to_date(p_effective_date,g_date_format);
1932 ELSE
1933 ld_effective_date:= to_date(
1934 hr_transaction_ss.get_wf_effective_date
1935 (p_transaction_step_id => p_transaction_step_id),g_date_format);
1936 END IF;
1937
1938 savepoint ex_emp_savepoint;
1939
1940 -----------------------------------------------------------------------
1941 -- get common data for APIs from transaction table
1942 -----------------------------------------------------------------------
1943 ln_period_of_service_id :=
1944 hr_transaction_api.get_number_value (
1945 p_transaction_step_id => p_transaction_step_id,
1946 p_name => 'P_PERIOD_OF_SERVICE_ID'
1947 );
1948
1949 ln_object_version_number :=
1950 hr_transaction_api.get_number_value (
1951 p_transaction_step_id => p_transaction_step_id,
1952 p_name => 'P_OBJECT_VERSION_NUMBER'
1953 );
1954
1955 lrt_termination := get_termination_details (
1956 p_transaction_step_id => p_transaction_step_id
1957 );
1958
1959 ld_notified_term_date := lrt_termination.notified_termination_date;
1960
1961 ld_projected_termination_date := lrt_termination.projected_termination_date;
1962 ld_final_process_date := lrt_termination.final_process_date;
1963
1964 -- For SAVE_FOR_LATER, when a user launch a suspended process, he will have
1965 -- an opportunity to change to a new effective date. Since the
1966 -- actual_termination_date is populated from the effective date, this field
1967 -- needs to use new effective date value instead of from transaction table.
1968 -- See SAVE_FOR_LATER comments above.
1969 ld_actual_term_date := ld_effective_date;
1970 lv_term_reason := lrt_termination.leaving_reason;
1971 ll_term_comments := lrt_termination.comments;
1972
1973 ld_last_standard_process_date := lrt_termination.last_standard_process_date;
1974
1975 lv_person_type_id := lrt_termination.person_type_id;
1976 lv_assignment_status_type_id
1977 := lrt_termination.assignment_status_type_id;
1978
1979 lv_rehire_recommendation := lrt_termination.rehire_recommendation;
1980 lv_rehire_reason := lrt_termination.rehire_reason;
1981
1982 --------------------------------------------------------------------------
1983 -- get dff data for update_pds_details API
1984 -- DDF Enhancement, This Function will return DFF Data as well from 21-50
1985 --------------------------------------------------------------------------
1986 lt_term_flex := get_term_flex_detail (
1987 p_transaction_step_id => p_transaction_step_id
1988 );
1989
1990 lv_attribute_category :=
1991 hr_transaction_api.get_varchar2_value (
1992 p_transaction_step_id => p_transaction_step_id,
1993 p_name => 'P_ATTRIBUTE_CATEGORY'
1994 );
1995
1996 -- DDF Enhancement : Get PDS_INFORMATION_CATEGORY
1997 lv_pds_information_category :=
1998 hr_transaction_api.get_varchar2_value (
1999 p_transaction_step_id => p_transaction_step_id,
2000 p_name => 'P_PDS_INFORMATION_CATEGORY'
2001 );
2002
2003 --------------------------------------------------------------
2004 -- Bug 2881583
2005 -- Determine whether legislation code is FR. If so the default the
2006 -- PDS_INFORMATION10 to Actual Termination Date.
2007 -- This is required for compatibility with code delivered in FP.D
2008 --------------------------------------------------------------
2009 begin
2010 select business_group_id
2011 into l_business_group_id
2012 from per_periods_of_service
2013 where period_of_service_id = ln_period_of_service_id;
2014 --
2015 l_legislation_code :=
2016 hr_api.return_legislation_code(p_business_group_id => l_business_group_id);
2017 --
2018 if l_legislation_code = 'FR' then
2019 hr_utility.trace('In if l_legislation_code = FR'|| l_proc);
2020 if ld_actual_term_date is not null and
2021 (lt_term_flex(30) = hr_api.g_varchar2
2022 or lt_term_flex(30) is null ) then
2023 lt_term_flex(30)
2024 := fnd_date.date_to_canonical(ld_actual_term_date);
2025 lv_pds_information_category := l_legislation_code;
2026 end if;
2027 end if;
2028
2029 exception when others then
2030 hr_utility.set_location('EXCEPTION: '|| l_proc,555);
2031 raise;
2032 end;
2033
2034 -- Bug 2098595 Fix Begins: 11/12/2001
2035 -- Need to switch the order of calling the api. We need to call
2036 -- hr_termination_ss.update_pds_details first to set the flex segments
2037 -- before calling hr_ex_employee_api.actual_termination_emp.
2038 -- Otherwise, when there is a mandatory segment, we'll get an error
2039 -- as follows:
2040 -- The mandatory column Attribute??(also known as xxxx) has not been
2041 -- assigned a value.
2042 -- The reason is because in hr_ex_employee_api.actual_termination_emp,
2043 -- it validates flex segments and there are no parameters to receive
2044 -- flex segments in hr_ex_employee_api.actual_termination_emp. So, we
2045 -- need to flip the order to set the value of flex segments.
2046 --
2047 -- Call update_pds_details;
2048 hr_periods_of_service_api.update_pds_details (
2049 p_validate => FALSE,
2050 p_effective_date => ld_actual_term_date,
2051 p_period_of_service_id => ln_period_of_service_id,
2052 p_object_version_number => ln_object_version_number,
2053 p_comments => ll_term_comments,
2054 p_leaving_reason => lv_term_reason,
2055 p_notified_termination_date => ld_notified_term_date,
2056 p_projected_termination_date => ld_projected_termination_date,
2057 p_attribute_category => lv_attribute_category,
2058 p_attribute1 => lt_term_flex(1),
2059 p_attribute2 => lt_term_flex(2),
2060 p_attribute3 => lt_term_flex(3),
2061 p_attribute4 => lt_term_flex(4),
2062 p_attribute5 => lt_term_flex(5),
2063 p_attribute6 => lt_term_flex(6),
2064 p_attribute7 => lt_term_flex(7),
2065 p_attribute8 => lt_term_flex(8),
2066 p_attribute9 => lt_term_flex(9),
2067 p_attribute10 => lt_term_flex(10),
2068 p_attribute11 => lt_term_flex(11),
2069 p_attribute12 => lt_term_flex(12),
2070 p_attribute13 => lt_term_flex(13),
2071 p_attribute14 => lt_term_flex(14),
2072 p_attribute15 => lt_term_flex(15),
2073 p_attribute16 => lt_term_flex(16),
2074 p_attribute17 => lt_term_flex(17),
2075 p_attribute18 => lt_term_flex(18),
2076 p_attribute19 => lt_term_flex(19),
2077 p_attribute20 => lt_term_flex(20),
2078 ---- DDF Enhancement : Save DDF Segments data as well.
2079 p_pds_information_category => lv_pds_information_category,
2080 p_pds_information1 => lt_term_flex(21),
2081 p_pds_information2 => lt_term_flex(22),
2082 p_pds_information3 => lt_term_flex(23),
2083 p_pds_information4 => lt_term_flex(24),
2084 p_pds_information5 => lt_term_flex(25),
2085 p_pds_information6 => lt_term_flex(26),
2086 p_pds_information7 => lt_term_flex(27),
2087 p_pds_information8 => lt_term_flex(28),
2088 p_pds_information9 => lt_term_flex(29),
2089 p_pds_information10 => lt_term_flex(30),
2090 p_pds_information11 => lt_term_flex(31),
2091 p_pds_information12 => lt_term_flex(32),
2092 p_pds_information13 => lt_term_flex(33),
2093 p_pds_information14 => lt_term_flex(34),
2094 p_pds_information15 => lt_term_flex(35),
2095 p_pds_information16 => lt_term_flex(36),
2096 p_pds_information17 => lt_term_flex(37),
2097 p_pds_information18 => lt_term_flex(38),
2098 p_pds_information19 => lt_term_flex(39),
2099 p_pds_information20 => lt_term_flex(40),
2100 p_pds_information21 => lt_term_flex(41),
2101 p_pds_information22 => lt_term_flex(42),
2102 p_pds_information23 => lt_term_flex(43),
2103 p_pds_information24 => lt_term_flex(44),
2104 p_pds_information25 => lt_term_flex(45),
2105 p_pds_information26 => lt_term_flex(46),
2106 p_pds_information27 => lt_term_flex(47),
2107 p_pds_information28 => lt_term_flex(48),
2108 p_pds_information29 => lt_term_flex(49),
2109 p_pds_information30 => lt_term_flex(50)
2110 );
2111
2112 -- Bug Fix 2089615 Ends
2113
2114 -- Fix for the bug 13846099 Starts
2115 -- Moved this piece of code "update rehire" info
2116 -- before calling "hr_ex_employee_api.actual_termination_emp"
2117
2118 -- Core HR API will not support update of field Rehire Recommendation
2119 -- and Rehire Reason. Hence we make following call to Person API
2120 -- to update the Fields.
2121 update_per_details(
2122 p_validate => 0, -- false
2123 p_effective_date => ld_actual_term_date,
2124 p_period_of_service_id => ln_period_of_service_id,
2125 p_actual_termination_date => ld_actual_term_date,
2126 p_rehire_recommendation => lv_rehire_recommendation,
2127 p_rehire_reason => lv_rehire_reason
2128 );
2129
2130 -- Fix for the bug 13846099 Ends
2131
2132 hr_ex_employee_api.actual_termination_emp (
2133 p_validate => FALSE,
2134 p_effective_date => ld_actual_term_date,
2135 p_period_of_service_id => ln_period_of_service_id,
2136 p_object_version_number => ln_object_version_number,
2137 p_actual_termination_date => ld_actual_term_date,
2138 p_last_standard_process_date => ld_last_standard_process_date,
2139 p_leaving_reason => lv_term_reason,
2140 -- p_rehire_recommendation => lv_rehire_recommendation,
2141 -- p_rehire_reason => lv_rehire_reason,
2142 p_person_type_id => lv_person_type_id,
2143 p_assignment_status_type_id => lv_assignment_status_type_id,
2144 p_supervisor_warning => lb_supervisor_warning,
2145 p_event_warning => lb_event_warning,
2146 p_interview_warning => lb_interview_warning,
2147 p_review_warning => lb_review_warning,
2148 p_recruiter_warning => lb_recruiter_warning,
2149 p_asg_future_changes_warning => lb_asg_future_changes_warning,
2150 p_entries_changed_warning => lv_entries_changed_warning,
2151 p_pay_proposal_warning => lb_pay_proposal_warning,
2152 p_dod_warning => lb_dod_warning
2153 );
2154
2155 -- Bug 2098595 Fix Ends
2156
2157
2158 --call for Final_emp_process bug 3843399
2159 l_entries_changed_warning := 'N';
2160
2161 if ld_final_process_date is not null
2162 THEN
2163 hr_ex_employee_api.final_process_emp(
2164 p_validate => false,
2165 p_period_of_service_id => ln_period_of_service_id,
2166 p_object_version_number => ln_object_version_number,
2167 p_final_process_date => ld_final_process_date,
2168 p_org_now_no_manager_warning => l_org_now_no_manager_warning,
2169 p_asg_future_changes_warning => l_asg_future_changes_warning,
2170 p_entries_changed_warning => l_entries_changed_warning );
2171
2172
2173 END IF;
2174 --end of call bug 3843399
2175
2176 -- Quick Termination changes start from here
2177
2178 hr_transaction_api.get_transaction_step_info(
2179 p_transaction_step_id
2180 ,ItemType
2181 ,ItemKey
2182 ,ActivityId);
2183
2184 if ( ItemType is not null and ItemKey is not null ) then
2185
2186 isQKFn := wf_engine.getItemAttrText(ItemType, ItemKey, 'HR_QUICK_FUNCTION');
2187
2188 if ( ActivityId is not null ) then
2189 isSupAsgPGExists := wf_engine.getActivityAttrText(ItemType, ItemKey, ActivityId, 'REASSIGN_DIRECTS');
2190 end if;
2191
2192 end if;
2193
2194 if (isQKFn = 'Y' and isSupAsgPGExists = 'Y' ) then
2195
2196 l_term_date := hr_transaction_api.get_date_value(p_transaction_step_id, 'P_ACTUAL_TERMINATION_DATE');
2197
2198 l_old_sup_person_id := wf_engine.getItemAttrText(ItemType, ItemKey, 'QK_TERM_OLD_SUP_PER_ID', true);
2199 l_old_sup_asg_id := wf_engine.getItemAttrText(ItemType, ItemKey, 'QK_TERM_OLD_SUP_ASG_ID', true);
2200 l_old_sup_bg_id := wf_engine.getItemAttrText(ItemType, ItemKey, 'QK_TERM_OLD_SUP_BG_ID', true);
2201 l_new_sup_person_id := wf_engine.getItemAttrText(ItemType, ItemKey, 'QK_TERM_NEW_SUP_PER_ID', true);
2202 l_new_sup_asg_id := wf_engine.getItemAttrText(ItemType, ItemKey, 'QK_TERM_NEW_SUP_ASG_ID', true);
2203 l_login_person_id := wf_engine.getItemAttrText(ItemType, ItemKey, 'QK_TERM_LOGIN_PER_ID', true);
2204
2205
2206 if ( l_old_sup_person_id is not null and
2207 l_old_sup_asg_id is not null and
2208 l_old_sup_bg_id is not null and
2209 l_new_sup_person_id is not null and
2210 l_new_sup_asg_id is not null and
2211 l_login_person_id is not null
2212 ) then
2213
2214 l_proc := l_proc || ' moveAllDirectReports';
2215
2216 if ( p_validate ) then
2217 l_validate := 'Y';
2218 else
2219 l_validate := 'N';
2220 end if;
2221
2222 hr_utility.trace('Called moveAllDirectReports from process_api');
2223
2224 moveAllDirectReports(
2225 p_validate => l_validate,
2226 p_item_type => ItemType,
2227 p_item_key => ItemKey,
2228 p_old_sup_person_id => l_old_sup_person_id,
2229 p_old_sup_asg_id => l_old_sup_asg_id,
2230 p_old_sup_bg_id => l_old_sup_bg_id,
2231 p_new_sup_person_id => l_new_sup_person_id,
2232 p_new_sup_asg_id => l_new_sup_asg_id,
2233 p_login_person_id => l_login_person_id,
2234 p_term_date => to_char(l_term_date, 'YYYY-MM-DD'),
2235 p_flow_identifier => 'TERMINATION_FLOW');
2236 end if;
2237
2238 end if;
2239 -- Quick Termination changes ends here
2240
2241 hr_utility.set_location(' Leaving: ' || l_proc,15);
2242
2243
2244 EXCEPTION
2245 WHEN OTHERS THEN
2246 l_err_msg := hr_java_conv_util_ss.get_formatted_error_message
2247 (p_single_error_message => hr_utility.get_message);
2248 hr_utility.set_location('EXCEPTION '|| l_err_msg || ': ' || l_proc,5600);
2249 rollback to ex_emp_savepoint;
2250 RAISE;
2251 END process_api;
2252
2253
2254 /*
2255 ||=======================================================================
2256 || FUNCTION : get_termination_details
2257 || DESCRIPTION : This overloaded funciton return the termination related
2258 || information by given transaction_step_id.
2259 ||=======================================================================
2260 */
2261 FUNCTION get_termination_details (
2262 p_transaction_step_id IN
2263 hr_api_transaction_steps.transaction_step_id%type
2264 )
2265 RETURN hr_termination_ss.rt_termination
2266 IS
2267 lrt_termination hr_termination_ss.rt_termination;
2268 l_proc constant varchar2(100) := g_package || ' get_termination_details';
2269 BEGIN
2270 hr_utility.set_location('Entering: '|| l_proc,5);
2271 lrt_termination.notified_termination_date :=
2272 hr_transaction_api.get_date_value (
2273 p_transaction_step_id => p_transaction_step_id,
2274 p_name => 'P_NOTIFIED_TERMINATION_DATE'
2275 );
2276
2277 lrt_termination.last_standard_process_date :=
2278 hr_transaction_api.get_date_value (
2279 p_transaction_step_id => p_transaction_step_id,
2280 p_name => 'P_LAST_STANDARD_PROCESS_DATE'
2281 );
2282
2283 lrt_termination.projected_termination_date :=
2284 hr_transaction_api.get_date_value (
2285 p_transaction_step_id => p_transaction_step_id,
2286 p_name => 'P_PROJECTED_TERMINATION_DATE'
2287 );
2288
2289 lrt_termination.final_process_date :=
2290 hr_transaction_api.get_date_value (
2291 p_transaction_step_id => p_transaction_step_id,
2292 p_name => 'P_FINAL_PROCESS_DATE'
2293 );
2294
2295 lrt_termination.actual_termination_date :=
2296 hr_transaction_api.get_date_value (
2297 p_transaction_step_id => p_transaction_step_id,
2298 p_name => 'P_ACTUAL_TERMINATION_DATE'
2299 );
2300
2301 lrt_termination.leaving_reason :=
2302 hr_transaction_api.get_varchar2_value (
2303 p_transaction_step_id => p_transaction_step_id,
2304 p_name => 'P_LEAVING_REASON'
2305 );
2306
2307 lrt_termination.person_type_id :=
2308 hr_transaction_api.get_number_value (
2309 p_transaction_step_id => p_transaction_step_id,
2310 p_name => 'P_PERSON_TYPE_ID'
2311 );
2312
2313 lrt_termination.assignment_status_type_id :=
2314 hr_transaction_api.get_number_value (
2315 p_transaction_step_id => p_transaction_step_id,
2316 p_name => 'P_ASSIGNMENT_STATUS_TYPE_ID'
2317 );
2318
2319 lrt_termination.comments :=
2320 hr_transaction_api.get_varchar2_value (
2321 p_transaction_step_id => p_transaction_step_id,
2322 p_name => 'P_COMMENTS'
2323 );
2324
2325 lrt_termination.period_of_service_id :=
2326 hr_transaction_api.get_number_value (
2327 p_transaction_step_id => p_transaction_step_id,
2328 p_name => 'P_PERIOD_OF_SERVICE_ID'
2329 );
2330
2331 lrt_termination.object_version_number :=
2332 hr_transaction_api.get_number_value (
2333 p_transaction_step_id => p_transaction_step_id,
2334 p_name => 'P_OBJECT_VERSION_NUMBER'
2335 );
2336
2337 lrt_termination.rehire_recommendation :=
2338 hr_transaction_api.get_varchar2_value (
2339 p_transaction_step_id => p_transaction_step_id,
2340 p_name => 'P_REHIRE_RECOMMENDATION'
2341 );
2342
2343 lrt_termination.rehire_reason :=
2344 hr_transaction_api.get_varchar2_value (
2345 p_transaction_step_id => p_transaction_step_id,
2346 p_name => 'P_REHIRE_REASON'
2347 );
2348 hr_utility.set_location('Leaving: '|| l_proc,10);
2349 RETURN lrt_termination;
2350 EXCEPTION
2351 WHEN OTHERS THEN
2352 hr_utility.set_location('EXCEPTION: '|| l_proc,555);
2353 RAISE;
2354 END get_termination_details;
2355
2356 /*
2357 ||=======================================================================
2358 || FUNCTION: get_term_flex_detail
2359 || DESCRIPTION: This function returns termination dff data by
2360 || transaction step id.
2361 ||=======================================================================
2362 */
2363 FUNCTION get_term_flex_detail (
2364 p_transaction_step_id IN
2365 hr_api_transaction_steps.transaction_step_id%type
2366 )
2367 RETURN hr_termination_ss.t_flex_table
2368 IS
2369 lt_term_flex hr_termination_ss.t_flex_table;
2370 l_proc constant varchar2(100) := g_package || 'get_term_flex_detail ';
2371 BEGIN
2372 ---- First 20 DFF Data
2373 ---- Next 30, DDF Data
2374 hr_utility.set_location('Entering: '|| l_proc,5);
2375 FOR ln_counter IN 1..20 LOOP
2376 lt_term_flex(ln_counter) := NULL;
2377 END LOOP;
2378 FOR ln_counter IN 1..20 LOOP
2379 lt_term_flex(ln_counter) :=
2380 hr_transaction_api.get_varchar2_value (
2381 p_transaction_step_id => p_transaction_step_id,
2382 p_name => 'P_ATTRIBUTE' || to_char(ln_counter)
2383 );
2384 END LOOP;
2385 ---------------------------------------------------------------------------
2386 -- DDF Enhancement : Return DDF Segments data from Transaction Table
2387 -- We have to get P_PDS_INFORMATION1 to P_PDS_INFORMATION30
2388 ---------------------------------------------------------------------------
2389 FOR ln_counter IN 21..50 LOOP
2390 lt_term_flex(ln_counter) := NULL;
2391 END LOOP;
2392 FOR ln_counter IN 21..50 LOOP
2393 lt_term_flex(ln_counter) :=
2394 hr_transaction_api.get_varchar2_value (
2395 p_transaction_step_id => p_transaction_step_id,
2396 p_name => 'P_PDS_INFORMATION' ||
2397 to_char(ln_counter - 20)
2398 );
2399 END LOOP;
2400 hr_utility.set_location('Leaving: '|| l_proc,10);
2401 RETURN lt_term_flex;
2402 END get_term_flex_detail;
2403
2404
2405 /*
2406 ||===========================================================================
2407 || PROCEDURE: get_term_transaction
2408 ||---------------------------------------------------------------------------
2409 ||
2410 || Description:
2411 || Reads Termination Transaction from transaction table
2412 ||
2413 || Pre Conditions:
2414 ||
2415 || In Arguments:
2416 || Transaction id keys
2417 ||
2418 || out nocopy Arguments:
2419 || None.
2420 ||
2421 || In out nocopy Arguments:
2422 ||
2423 || Post Success:
2424 || Reads from transaction table
2425 ||
2426 || Post Failure:
2427 || Raises an exception
2428 ||
2429 || Access Status:
2430 || Public
2431 ||
2432 ||===========================================================================
2433 */
2434 procedure get_term_transaction
2435 (p_transaction_step_id in varchar2
2436 ,p_period_of_service_id out nocopy varchar2
2437 ,p_object_version_number out nocopy varchar2
2438 ,p_actual_termination_date out nocopy varchar2
2439 ,p_notified_termination_date out nocopy varchar2
2440 ,p_leaving_reason out nocopy varchar2
2441 ,p_person_type_id out nocopy varchar2
2442 ,p_assignment_status_type_id out nocopy varchar2
2443 ,p_rehire_recommendation out nocopy varchar2
2444 ,p_rehire_reason out nocopy varchar2
2445 ,p_comments out nocopy varchar2
2446 ,p_last_standard_process_date out nocopy varchar2
2447 ,p_projected_termination_date out nocopy varchar2
2448 ,p_final_process_date out nocopy varchar2
2449 ,p_attribute_category out nocopy varchar2
2450 ,p_attribute1 out nocopy varchar2
2451 ,p_attribute2 out nocopy varchar2
2452 ,p_attribute3 out nocopy varchar2
2453 ,p_attribute4 out nocopy varchar2
2454 ,p_attribute5 out nocopy varchar2
2455 ,p_attribute6 out nocopy varchar2
2456 ,p_attribute7 out nocopy varchar2
2457 ,p_attribute8 out nocopy varchar2
2458 ,p_attribute9 out nocopy varchar2
2459 ,p_attribute10 out nocopy varchar2
2460 ,p_attribute11 out nocopy varchar2
2461 ,p_attribute12 out nocopy varchar2
2462 ,p_attribute13 out nocopy varchar2
2463 ,p_attribute14 out nocopy varchar2
2464 ,p_attribute15 out nocopy varchar2
2465 ,p_attribute16 out nocopy varchar2
2466 ,p_attribute17 out nocopy varchar2
2467 ,p_attribute18 out nocopy varchar2
2468 ,p_attribute19 out nocopy varchar2
2469 ,p_attribute20 out nocopy varchar2
2470 ,p_review_actid out nocopy varchar2
2471 ,p_review_proc_call out nocopy varchar2
2472 ,p_pds_information_category out nocopy varchar2
2473 ,p_pds_information1 out nocopy varchar2
2474 ,p_pds_information2 out nocopy varchar2
2475 ,p_pds_information3 out nocopy varchar2
2476 ,p_pds_information4 out nocopy varchar2
2477 ,p_pds_information5 out nocopy varchar2
2478 ,p_pds_information6 out nocopy varchar2
2479 ,p_pds_information7 out nocopy varchar2
2480 ,p_pds_information8 out nocopy varchar2
2481 ,p_pds_information9 out nocopy varchar2
2482 ,p_pds_information10 out nocopy varchar2
2483 ,p_pds_information11 out nocopy varchar2
2484 ,p_pds_information12 out nocopy varchar2
2485 ,p_pds_information13 out nocopy varchar2
2486 ,p_pds_information14 out nocopy varchar2
2487 ,p_pds_information15 out nocopy varchar2
2488 ,p_pds_information16 out nocopy varchar2
2489 ,p_pds_information17 out nocopy varchar2
2490 ,p_pds_information18 out nocopy varchar2
2491 ,p_pds_information19 out nocopy varchar2
2492 ,p_pds_information20 out nocopy varchar2
2493 ,p_pds_information21 out nocopy varchar2
2494 ,p_pds_information22 out nocopy varchar2
2495 ,p_pds_information23 out nocopy varchar2
2496 ,p_pds_information24 out nocopy varchar2
2497 ,p_pds_information25 out nocopy varchar2
2498 ,p_pds_information26 out nocopy varchar2
2499 ,p_pds_information27 out nocopy varchar2
2500 ,p_pds_information28 out nocopy varchar2
2501 ,p_pds_information29 out nocopy varchar2
2502 ,p_pds_information30 out nocopy varchar2
2503 )
2504 IS
2505
2506 lv_date varchar2(200) default null;
2507 l_proc constant varchar2(100) := g_package || ' get_term_transaction';
2508
2509 BEGIN
2510 hr_utility.set_location('Entering: '|| l_proc,5);
2511 --
2512 p_period_of_service_id:=
2513 hr_transaction_api.get_number_value
2514 (p_transaction_step_id => p_transaction_step_id
2515 ,p_name => 'P_PERIOD_OF_SERVICE_ID');
2516 --
2517 p_object_version_number:=
2518 hr_transaction_api.get_number_value
2519 (p_transaction_step_id => p_transaction_step_id
2520 ,p_name => 'P_OBJECT_VERSION_NUMBER');
2521 --
2522 -- Bug 2086516 Fix Begins - 11/15/2001
2523 -- When re-launching a Save For Later action,we need to get the Effective Date
2524 -- from Workflow because a user can change the Effective Date which is saved
2525 -- to WF as an item attribute. Otherwise, the page will display the Effective
2526 -- Date from the transaction table and ignore the new date entered by the
2527 -- user.
2528
2529 p_actual_termination_date:=
2530 hr_transaction_ss.get_wf_effective_date
2531 (p_transaction_step_id => p_transaction_step_id);
2532 --
2533 -- Bug 2086516 Fix Ends - 11/15/2001
2534 --
2535 p_notified_termination_date:= to_char(
2536 hr_transaction_api.get_date_value
2537 (p_transaction_step_id => p_transaction_step_id
2538 ,p_name => 'P_NOTIFIED_TERMINATION_DATE')
2539 ,g_date_format);
2540 --
2541 p_last_standard_process_date := to_char(
2542 hr_transaction_api.get_date_value
2543 (p_transaction_step_id => p_transaction_step_id
2544 ,p_name => 'P_LAST_STANDARD_PROCESS_DATE')
2545 ,g_date_format);
2546 --
2547 p_projected_termination_date := to_char(
2548 hr_transaction_api.get_date_value
2549 (p_transaction_step_id => p_transaction_step_id
2550 ,p_name => 'P_PROJECTED_TERMINATION_DATE')
2551 ,g_date_format);
2552 --
2553 p_final_process_date := to_char(
2554 hr_transaction_api.get_date_value
2555 (p_transaction_step_id => p_transaction_step_id
2556 ,p_name => 'P_FINAL_PROCESS_DATE')
2557 ,g_date_format);
2558 --
2559 p_leaving_reason:=
2560 hr_transaction_api.get_varchar2_value
2561 (p_transaction_step_id => p_transaction_step_id
2562 ,p_name => 'P_LEAVING_REASON');
2563 --
2564 p_person_type_id:=
2565 hr_transaction_api.get_number_value
2566 (p_transaction_step_id => p_transaction_step_id
2567 ,p_name => 'P_PERSON_TYPE_ID');
2568 --
2569 p_assignment_status_type_id:=
2570 hr_transaction_api.get_number_value
2571 (p_transaction_step_id => p_transaction_step_id
2572 ,p_name => 'P_ASSIGNMENT_STATUS_TYPE_ID');
2573 --
2574 p_comments:=
2575 hr_transaction_api.get_varchar2_value
2576 (p_transaction_step_id => p_transaction_step_id
2577 ,p_name => 'P_COMMENTS');
2578 --
2579 p_rehire_recommendation:=
2580 hr_transaction_api.get_varchar2_value
2581 (p_transaction_step_id => p_transaction_step_id
2582 ,p_name => 'P_REHIRE_RECOMMENDATION');
2583 --
2584 p_rehire_reason:=
2585 hr_transaction_api.get_varchar2_value
2586 (p_transaction_step_id => p_transaction_step_id
2587 ,p_name => 'P_REHIRE_REASON');
2588
2589 --
2590 p_attribute_category:=
2591 hr_transaction_api.get_varchar2_value
2592 (p_transaction_step_id => p_transaction_step_id
2593 ,p_name => 'P_ATTRIBUTE_CATEGORY');
2594 --
2595 p_attribute1:=
2596 hr_transaction_api.get_varchar2_value
2597 (p_transaction_step_id => p_transaction_step_id
2598 ,p_name => 'P_ATTRIBUTE1');
2599 --
2600 p_attribute2:=
2601 hr_transaction_api.get_varchar2_value
2602 (p_transaction_step_id => p_transaction_step_id
2603 ,p_name => 'P_ATTRIBUTE2');
2604 --
2605 p_attribute3:=
2606 hr_transaction_api.get_varchar2_value
2607 (p_transaction_step_id => p_transaction_step_id
2608 ,p_name => 'P_ATTRIBUTE3');
2609 --
2610 p_attribute4:=
2611 hr_transaction_api.get_varchar2_value
2612 (p_transaction_step_id => p_transaction_step_id
2613 ,p_name => 'P_ATTRIBUTE4');
2614 --
2615 p_attribute5:=
2616 hr_transaction_api.get_varchar2_value
2617 (p_transaction_step_id => p_transaction_step_id
2618 ,p_name => 'P_ATTRIBUTE5');
2619 --
2620 p_attribute6:=
2621 hr_transaction_api.get_varchar2_value
2622 (p_transaction_step_id => p_transaction_step_id
2623 ,p_name => 'P_ATTRIBUTE6');
2624 --
2625 p_attribute7:=
2626 hr_transaction_api.get_varchar2_value
2627 (p_transaction_step_id => p_transaction_step_id
2628 ,p_name => 'P_ATTRIBUTE7');
2629 --
2630 p_attribute8:=
2631 hr_transaction_api.get_varchar2_value
2632 (p_transaction_step_id => p_transaction_step_id
2633 ,p_name => 'P_ATTRIBUTE8');
2634 --
2635 p_attribute9:=
2636 hr_transaction_api.get_varchar2_value
2637 (p_transaction_step_id => p_transaction_step_id
2638 ,p_name => 'P_ATTRIBUTE9');
2639 --
2640 p_attribute10:=
2641 hr_transaction_api.get_varchar2_value
2642 (p_transaction_step_id => p_transaction_step_id
2643 ,p_name => 'P_ATTRIBUTE10');
2644 --
2645 p_attribute11:=
2646 hr_transaction_api.get_varchar2_value
2647 (p_transaction_step_id => p_transaction_step_id
2648 ,p_name => 'P_ATTRIBUTE11');
2649 --
2650 p_attribute12:=
2651 hr_transaction_api.get_varchar2_value
2652 (p_transaction_step_id => p_transaction_step_id
2653 ,p_name => 'P_ATTRIBUTE12');
2654 --
2655 p_attribute13:=
2656 hr_transaction_api.get_varchar2_value
2657 (p_transaction_step_id => p_transaction_step_id
2658 ,p_name => 'P_ATTRIBUTE13');
2659 --
2660 p_attribute14:=
2661 hr_transaction_api.get_varchar2_value
2662 (p_transaction_step_id => p_transaction_step_id
2663 ,p_name => 'P_ATTRIBUTE14');
2664 --
2665 p_attribute15:=
2666 hr_transaction_api.get_varchar2_value
2667 (p_transaction_step_id => p_transaction_step_id
2668 ,p_name => 'P_ATTRIBUTE15');
2669 --
2670 p_attribute16:=
2671 hr_transaction_api.get_varchar2_value
2672 (p_transaction_step_id => p_transaction_step_id
2673 ,p_name => 'P_ATTRIBUTE16');
2674 --
2675 p_attribute17:=
2676 hr_transaction_api.get_varchar2_value
2677 (p_transaction_step_id => p_transaction_step_id
2678 ,p_name => 'P_ATTRIBUTE17');
2679 --
2680 p_attribute18:=
2681 hr_transaction_api.get_varchar2_value
2682 (p_transaction_step_id => p_transaction_step_id
2683 ,p_name => 'P_ATTRIBUTE18');
2684 --
2685 p_attribute19:=
2686 hr_transaction_api.get_varchar2_value
2687 (p_transaction_step_id => p_transaction_step_id
2688 ,p_name => 'P_ATTRIBUTE19');
2689 --
2690 p_attribute20:=
2691 hr_transaction_api.get_varchar2_value
2692 (p_transaction_step_id => p_transaction_step_id
2693 ,p_name => 'P_ATTRIBUTE20');
2694 --
2695 p_review_actid:=
2696 hr_transaction_api.get_varchar2_value
2697 (p_transaction_step_id => p_transaction_step_id
2698 ,p_name => 'P_REVIEW_ACTID');
2699 --
2700 p_review_proc_call:=
2701 hr_transaction_api.get_varchar2_value
2702 (p_transaction_step_id => p_transaction_step_id
2703 ,p_name => 'P_REVIEW_PROC_CALL');
2704
2705 -- DDF Enhancement : Retrieve from Transaction table.
2706 --
2707 p_pds_information_category:=
2708 hr_transaction_api.get_varchar2_value
2709 (p_transaction_step_id => p_transaction_step_id
2710 ,p_name => 'P_PDS_INFORMATION_CATEGORY');
2711 --
2712 p_pds_information1:=
2713 hr_transaction_api.get_varchar2_value
2714 (p_transaction_step_id => p_transaction_step_id
2715 ,p_name => 'P_PDS_INFORMATION1');
2716 --
2717 p_pds_information2:=
2718 hr_transaction_api.get_varchar2_value
2719 (p_transaction_step_id => p_transaction_step_id
2720 ,p_name => 'P_PDS_INFORMATION2');
2721 --
2722 p_pds_information3:=
2723 hr_transaction_api.get_varchar2_value
2724 (p_transaction_step_id => p_transaction_step_id
2725 ,p_name => 'P_PDS_INFORMATION3');
2726 --
2727 p_pds_information4:=
2728 hr_transaction_api.get_varchar2_value
2729 (p_transaction_step_id => p_transaction_step_id
2730 ,p_name => 'P_PDS_INFORMATION4');
2731 --
2732 p_pds_information5:=
2733 hr_transaction_api.get_varchar2_value
2734 (p_transaction_step_id => p_transaction_step_id
2735 ,p_name => 'P_PDS_INFORMATION5');
2736 --
2737 p_pds_information6:=
2738 hr_transaction_api.get_varchar2_value
2739 (p_transaction_step_id => p_transaction_step_id
2740 ,p_name => 'P_PDS_INFORMATION6');
2741 --
2742 p_pds_information7:=
2743 hr_transaction_api.get_varchar2_value
2744 (p_transaction_step_id => p_transaction_step_id
2745 ,p_name => 'P_PDS_INFORMATION7');
2746 --
2747 p_pds_information8:=
2748 hr_transaction_api.get_varchar2_value
2749 (p_transaction_step_id => p_transaction_step_id
2750 ,p_name => 'P_PDS_INFORMATION8');
2751 --
2752 p_pds_information9:=
2753 hr_transaction_api.get_varchar2_value
2754 (p_transaction_step_id => p_transaction_step_id
2755 ,p_name => 'P_PDS_INFORMATION9');
2756 --
2757 p_pds_information10:=
2758 hr_transaction_api.get_varchar2_value
2759 (p_transaction_step_id => p_transaction_step_id
2760 ,p_name => 'P_PDS_INFORMATION10');
2761 --
2762 p_pds_information11:=
2763 hr_transaction_api.get_varchar2_value
2764 (p_transaction_step_id => p_transaction_step_id
2765 ,p_name => 'P_PDS_INFORMATION11');
2766 --
2767 p_pds_information12:=
2768 hr_transaction_api.get_varchar2_value
2769 (p_transaction_step_id => p_transaction_step_id
2770 ,p_name => 'P_PDS_INFORMATION12');
2771 --
2772 p_pds_information13:=
2773 hr_transaction_api.get_varchar2_value
2774 (p_transaction_step_id => p_transaction_step_id
2775 ,p_name => 'P_PDS_INFORMATION13');
2776 --
2777 p_pds_information14:=
2778 hr_transaction_api.get_varchar2_value
2779 (p_transaction_step_id => p_transaction_step_id
2780 ,p_name => 'P_PDS_INFORMATION14');
2781 --
2782 p_pds_information15:=
2783 hr_transaction_api.get_varchar2_value
2784 (p_transaction_step_id => p_transaction_step_id
2785 ,p_name => 'P_PDS_INFORMATION15');
2786 --
2787 p_pds_information16:=
2788 hr_transaction_api.get_varchar2_value
2789 (p_transaction_step_id => p_transaction_step_id
2790 ,p_name => 'P_PDS_INFORMATION16');
2791 --
2792 p_pds_information17:=
2793 hr_transaction_api.get_varchar2_value
2794 (p_transaction_step_id => p_transaction_step_id
2795 ,p_name => 'P_PDS_INFORMATION17');
2796 --
2797 p_pds_information18:=
2798 hr_transaction_api.get_varchar2_value
2799 (p_transaction_step_id => p_transaction_step_id
2800 ,p_name => 'P_PDS_INFORMATION18');
2801 --
2802 p_pds_information19:=
2803 hr_transaction_api.get_varchar2_value
2804 (p_transaction_step_id => p_transaction_step_id
2805 ,p_name => 'P_PDS_INFORMATION19');
2806 --
2807 p_pds_information20:=
2808 hr_transaction_api.get_varchar2_value
2809 (p_transaction_step_id => p_transaction_step_id
2810 ,p_name => 'P_PDS_INFORMATION20');
2811 --
2812 p_pds_information21:=
2813 hr_transaction_api.get_varchar2_value
2814 (p_transaction_step_id => p_transaction_step_id
2815 ,p_name => 'P_PDS_INFORMATION21');
2816 --
2817 p_pds_information22:=
2818 hr_transaction_api.get_varchar2_value
2819 (p_transaction_step_id => p_transaction_step_id
2820 ,p_name => 'P_PDS_INFORMATION22');
2821 --
2822 p_pds_information23:=
2823 hr_transaction_api.get_varchar2_value
2824 (p_transaction_step_id => p_transaction_step_id
2825 ,p_name => 'P_PDS_INFORMATION23');
2826 --
2827 p_pds_information24:=
2828 hr_transaction_api.get_varchar2_value
2829 (p_transaction_step_id => p_transaction_step_id
2830 ,p_name => 'P_PDS_INFORMATION24');
2831 --
2832 p_pds_information25:=
2833 hr_transaction_api.get_varchar2_value
2834 (p_transaction_step_id => p_transaction_step_id
2835 ,p_name => 'P_PDS_INFORMATION25');
2836 --
2837 p_pds_information26:=
2838 hr_transaction_api.get_varchar2_value
2839 (p_transaction_step_id => p_transaction_step_id
2840 ,p_name => 'P_PDS_INFORMATION26');
2841 --
2842 p_pds_information27:=
2843 hr_transaction_api.get_varchar2_value
2844 (p_transaction_step_id => p_transaction_step_id
2845 ,p_name => 'P_PDS_INFORMATION27');
2846 --
2847 p_pds_information28:=
2848 hr_transaction_api.get_varchar2_value
2849 (p_transaction_step_id => p_transaction_step_id
2850 ,p_name => 'P_PDS_INFORMATION28');
2851 --
2852 p_pds_information29:=
2853 hr_transaction_api.get_varchar2_value
2854 (p_transaction_step_id => p_transaction_step_id
2855 ,p_name => 'P_PDS_INFORMATION29');
2856 --
2857 p_pds_information30:=
2858 hr_transaction_api.get_varchar2_value
2859 (p_transaction_step_id => p_transaction_step_id
2860 ,p_name => 'P_PDS_INFORMATION30');
2861 --
2862 hr_utility.set_location('Leaving: '|| l_proc,10);
2863 EXCEPTION
2864 WHEN OTHERS THEN
2865 hr_utility.set_location('EXCEPTION: '|| l_proc,555);
2866 p_period_of_service_id := null;
2867 p_object_version_number := null;
2868 p_actual_termination_date := null;
2869 p_notified_termination_date := null;
2870 p_leaving_reason := null;
2871 p_person_type_id := NULL;
2872 p_assignment_status_type_id := NULL;
2873 p_comments := null;
2874 p_attribute_category := null;
2875 p_attribute1 := null;
2876 p_attribute2 := null;
2877 p_attribute3 := null;
2878 p_attribute4 := null;
2879 p_attribute5 := null;
2880 p_attribute6 := null;
2881 p_attribute7 := null;
2882 p_attribute8 := null;
2883 p_attribute9 := null;
2884 p_attribute10 := null;
2885 p_attribute11 := null;
2886 p_attribute12 := null;
2887 p_attribute13 := null;
2888 p_attribute14 := null;
2889 p_attribute15 := null;
2890 p_attribute16 := null;
2891 p_attribute17 := null;
2892 p_attribute18 := null;
2893 p_attribute19 := null;
2894 p_attribute20 := null;
2895 p_review_actid := null;
2896 p_review_proc_call := null;
2897 p_pds_information_category := null;
2898 p_pds_information1 := null;
2899 p_pds_information2 := null;
2900 p_pds_information3 := null;
2901 p_pds_information4 := null;
2902 p_pds_information5 := null;
2903 p_pds_information6 := null;
2904 p_pds_information7 := null;
2905 p_pds_information8 := null;
2906 p_pds_information9 := null;
2907 p_pds_information10 := null;
2908 p_pds_information11 := null;
2909 p_pds_information12 := null;
2910 p_pds_information13 := null;
2911 p_pds_information14 := null;
2912 p_pds_information15 := null;
2913 p_pds_information16 := null;
2914 p_pds_information17 := null;
2915 p_pds_information18 := null;
2916 p_pds_information19 := null;
2917 p_pds_information20 := null;
2918 p_pds_information21 := null;
2919 p_pds_information22 := null;
2920 p_pds_information23 := null;
2921 p_pds_information24 := null;
2922 p_pds_information25 := null;
2923 p_pds_information26 := null;
2924 p_pds_information27 := null;
2925 p_pds_information28 := null;
2926 p_pds_information29 := null;
2927 p_pds_information30 := null;
2928 RAISE; -- Raise error here relevant to the new tech stack.
2929
2930 END get_term_transaction;
2931
2932
2933 function get_sup_asg_byprfl(p_param_1 varchar2
2934 ,p_param_2 varchar2
2935 ,p_param_3 varchar2
2936 ,p_param_4 varchar2
2937 ,p_param_5 varchar2
2938 ,p_param_6 varchar2
2939 ,p_param_7 varchar2
2940 ,p_param_8 varchar2
2941 ,p_param_9 varchar2
2942 ,p_param_10 varchar2
2943 ,p_param_11 varchar2
2944 ,p_param_12 varchar2
2945 ,p_param_13 varchar2
2946 ,p_param_14 varchar2
2947 ,p_param_15 varchar2
2948 )
2949 return number is
2950
2951 cursor cur_get_sup_asg(cp_param_1 varchar2
2952 ,cp_param_2 varchar2
2953 ,cp_param_3 varchar2
2954 ,cp_param_4 varchar2
2955 ,cp_param_5 varchar2
2956 ,cp_param_6 varchar2
2957 ,cp_param_7 varchar2
2958 ,cp_param_8 varchar2
2959 ,cp_param_9 varchar2
2960 ,cp_param_10 varchar2
2961 ,cp_param_11 varchar2
2962 ,cp_param_12 varchar2
2963 ,cp_param_13 varchar2
2964 ,cp_param_14 varchar2
2965 ,cp_param_15 varchar2
2966 ) IS
2967
2968 SELECT ppf.list_name FULL_NAME,
2969 ppf.last_name LAST_NAME,
2970 ppf.first_name FIRST_NAME,
2971 nvl(ppf.order_name, ppf.list_name) ORDER_NAME,
2972 ppf.person_id PERSON_ID,
2973 ppf.business_group_id BUSINESS_GROUP_ID,
2974 ppf.effective_start_date EFFECTIVE_START_DATE,
2975 ppf.effective_end_date EFFECTIVE_END_DATE,
2976 ppf.employee_number EMPLOYEE_NUMBER,
2977 ppf.email_address EMAIL_ADDRESS,
2978 paf.effective_start_date ASN_EFFECTIVE_START_DATE,
2979 paf.effective_end_date ASN_EFFECTIVE_END_DATE,
2980 jbs.name JOB_NAME,
2981 pos.name POSITION_NAME,
2982 pg.name GRADE_NAME,
2983 org.name ORGANIZATION,
2984 loc.location_code LOCATION_NAME,
2985 bus.name BUSINESS_GROUP,
2986 hrl.meaning PRIMARY_FLAG,
2987 paf.assignment_number ASSIGNMENT_NUMBER,
2988 paf.assignment_id,
2989 paf.object_version_number,
2990 ppf.local_name LOCAL_NAME,
2991 ppf.global_name GLOBAL_NAME,
2992 ppf.npw_number
2993 from per_people_f ppf, -- from secured view for assign new report(bug1855111)
2994 per_Assignments_f2 paf,
2995 per_jobs_vl jbs,
2996 hr_all_organization_units_tl org,
2997 hr_all_organization_units_tl bus,
2998 hr_all_positions_f_vl pos,
2999 per_grades_vl pg,
3000 hr_lookups hrl,
3001 hr_locations_all loc
3002 WHERE (paf.assignment_type = 'E' and
3003 ppf.current_employee_flag = 'Y' or
3004 paf.assignment_type = 'C' and
3005 ppf.current_npw_flag = 'Y')
3006 AND ppf.person_id = paf.person_id
3007 AND paf.primary_flag in (cp_param_1 , cp_param_2)
3008 AND paf.business_group_id = bus.organization_id
3009 AND bus.language = userenv('LANG')
3010 AND paf.job_id = jbs.job_id (+)
3011 AND paf.grade_id = pg.grade_id (+)
3012 AND paf.organization_id = org.organization_id (+)
3013 AND paf.location_id = loc.location_id(+)
3014 AND org.language = userenv('LANG')
3015 AND hrl.lookup_type(+) = 'YES_NO'
3016 AND paf.primary_flag = hrl.lookup_code (+)
3017 AND to_date(cp_param_3, 'YYYY-MM-DD') between paf.effective_start_date and paf.effective_end_date
3018 AND to_date(cp_param_4, 'YYYY-MM-DD') between ppf.effective_start_date and ppf.effective_end_date
3019 AND paf.position_id = pos.position_id (+)
3020 AND to_date(cp_param_5, 'YYYY-MM-DD') between pos.effective_start_date(+) and pos.effective_end_date(+)
3021 AND (cp_param_6 = 'Y' or ppf.business_group_id = cp_param_7)
3022 AND NOT EXISTS
3023 -- Replaced NOT IN clause with NOT EXISTS, Bug 2416631
3024 -- TO FILTER ALL DIRECT REPORTS TO THE PERSON
3025 (
3026 select paf2.person_id
3027 from per_assignments_f2 paf2
3028 where paf2.supervisor_id = cp_param_8
3029 and paf2.primary_flag in (cp_param_9 , cp_param_10)
3030 and paf2.assignment_type in ('E','C')
3031 and to_date(cp_param_11, 'YYYY-MM-DD') between paf2.effective_start_date and paf2.effective_end_date
3032 and paf2.person_id = ppf.person_id
3033 and ((hr_general2.supervisor_assignments_in_use <> 'TRUE') or
3034 paf2.supervisor_assignment_id = cp_param_12)
3035 and ((hr_general2.supervisor_assignments_in_use <> 'TRUE') or
3036 paf2.assignment_id = paf.assignment_id)
3037
3038
3039 )
3040 -- TO FILTER THE MANAGER HIMSELF
3041 -- Bug 2212956 Fix
3042 --and ppf.person_id not in (cp_param_13 , cp_param_14, cp_param_15 )
3043 and ppf.person_id = cp_param_13;
3044
3045 l_rec_sup_asg cur_get_sup_asg%rowtype;
3046 ln_assignment_id number;
3047
3048 begin
3049 ln_assignment_id := -1;
3050 open cur_get_sup_asg(cp_param_1 => p_param_1
3051 ,cp_param_2 => p_param_2
3052 ,cp_param_3 => p_param_3
3053 ,cp_param_4 => p_param_4
3054 ,cp_param_5 => p_param_5
3055 ,cp_param_6 => p_param_6
3056 ,cp_param_7 => p_param_7
3057 ,cp_param_8 => p_param_8
3058 ,cp_param_9 => p_param_9
3059 ,cp_param_10 => p_param_10
3060 ,cp_param_11 => p_param_11
3061 ,cp_param_12 => p_param_12
3062 ,cp_param_13 => p_param_13
3063 ,cp_param_14 => p_param_14
3064 ,cp_param_15 => p_param_15
3065 );
3066 fetch cur_get_sup_asg into l_rec_sup_asg;
3067 close cur_get_sup_asg;
3068
3069 if ( l_rec_sup_asg.assignment_id is not null ) then
3070 ln_assignment_id := l_rec_sup_asg.assignment_id;
3071 else
3072 ln_assignment_id := -1;
3073 end if;
3074
3075 return ln_assignment_id;
3076
3077 end get_sup_asg_byprfl;
3078
3079 Procedure moveAllDirectReports(
3080 p_validate in varchar2 default 'Y',
3081 p_old_sup_person_id in number,
3082 p_old_sup_bg_id in number,
3083 p_old_sup_asg_id in number,
3084 p_term_date in varchar2,
3085 p_flow_identifier in varchar2,
3086 p_new_sup_person_id in number,
3087 p_new_sup_asg_id in number,
3088 p_login_person_id number,
3089 p_item_type varchar2,
3090 p_item_key varchar2
3091 ) is
3092
3093 cursor cp_get_direct_reports(cp_old_sup_person_id in number
3094 , cp_term_date in varchar2
3095 , cp_flow_identifier in varchar2) is
3096
3097 SELECT DECODE(fnd_profile.value('HR_LOCAL_OR_GLOBAL_NAME_FORMAT'),'G',ppf.global_name,ppf.local_name) full_name,
3098 paf.object_version_number,
3099 paf.assignment_id,
3100 paf.supervisor_id SupervisorLov_Id,
3101 DECODE(fnd_profile.value('HR_LOCAL_OR_GLOBAL_NAME_FORMAT'),'G',ppf2.global_name,ppf2.local_name) Supervisor_Name,
3102 Paf.Person_Id,
3103 (to_date( p_term_date, 'YYYY-MM-DD' ) + 1) Effective_Date,
3104 jbs.name JOB_NAME,
3105 pos.name POSITION_NAME,
3106 pg.name GRADE_NAME,
3107 org.name ORGANIZATION,
3108 loc.location_code LOCATION_NAME,
3109 bus.name BUSINESS_GROUP,
3110 bus.organization_id business_group_id,
3111 hrl.meaning PRIMARY_FLAG,
3112 paf.assignment_number ASSIGNMENT_NUMBER,
3113 paf.supervisor_id old_supervisor_id,
3114 NVL(paf.supervisor_assignment_id,-1) SupervisorLov_assignment_id ,
3115 NVL(paf.supervisor_assignment_id,-1) old_supervisor_assignment_id,
3116 paf2.assignment_number SUPERVISOR_ASSIGNMENT_NUMBER,
3117 jbs2.name SUPERVISOR_JOB_NAME,
3118 pos2.name SUPERVISOR_POSITION_NAME,
3119 org2.name SUPERVISOR_ORGANIZATION
3120 FROM per_all_people_f ppf, -- Changed to use base table, see bug 2130066
3121 per_all_people_f ppf2 ,
3122 per_all_assignments_f paf ,
3123 per_all_assignments_f paf2 ,
3124 per_jobs_vl jbs ,
3125 hr_all_positions_f_tl pos ,
3126 per_grades_vl pg ,
3127 hr_all_organization_units_tl org ,
3128 hr_all_organization_units_tl bus ,
3129 hr_lookups hrl ,
3130 hr_locations_all loc ,
3131 per_jobs_vl jbs2 ,
3132 hr_all_positions_f_tl pos2 ,
3133 Hr_All_Organization_Units_Tl Org2
3134 Where Paf.Supervisor_Id = p_old_sup_person_id
3135 AND ((p_flow_identifier = 'TERMINATION_FLOW')
3136 Or ((Hr_General2.Supervisor_Assignments_In_Use = 'TRUE'
3137 Or p_flow_identifier = 'TERM_SEC_ASGN_FLOW')
3138 AND paf.supervisor_assignment_id = p_old_sup_asg_id)
3139 Or ((Hr_General2.Supervisor_Assignments_In_Use <> 'TRUE')
3140 AND p_flow_identifier = 'ASSIGNMENT_FLOW'))
3141 AND paf.person_id = ppf.person_id
3142 AND ((ppf.current_employee_flag = 'Y'
3143 AND paf.assignment_type = 'E')
3144 OR (ppf.current_npw_flag = 'Y'
3145 And Paf.Assignment_Type = 'C'))
3146 AND paf.primary_flag IN ('Y')
3147 AND paf.business_group_id = bus.organization_id
3148 AND bus.language = userenv('LANG')
3149 AND paf.job_id = jbs.job_id (+)
3150 AND paf.position_id = pos.position_id (+)
3151 AND pos.language(+) = userenv('LANG')
3152 AND paf.grade_id = pg.grade_id (+)
3153 AND paf.organization_id = org.organization_id (+)
3154 AND paf.location_id = loc.location_id(+)
3155 AND org.language(+) = userenv('LANG')
3156 AND hrl.lookup_type(+) = 'YES_NO'
3157 And Paf.Primary_Flag = Hrl.Lookup_Code (+)
3158 And to_date(p_term_date, 'YYYY-MM-DD') Between Paf.Effective_Start_Date And Paf.Effective_End_Date
3159 And to_date(p_term_date, 'YYYY-MM-DD') Between Ppf.Effective_Start_Date And Ppf.Effective_End_Date-1
3160 AND to_date(p_term_date, 'YYYY-MM-DD') BETWEEN ppf2.effective_start_date AND ppf2.effective_end_date
3161 AND ppf2.person_id = paf.supervisor_id
3162 AND paf.supervisor_id = paf2.person_id(+)
3163 AND ( ((NVL(fnd_profile.value('HR_SUPERVISOR_HIERARCHY_USAGE'), 'P') <> 'A')
3164 AND (paf.supervisor_id IS NULL
3165 OR ((paf2.primary_flag = 'Y')
3166 AND (paf2.assignment_type IN ('E','C','A'))) ) )
3167 OR ( (NVL(fnd_profile.value('HR_SUPERVISOR_HIERARCHY_USAGE'), 'P') = 'A')
3168 AND (paf.supervisor_id IS NULL
3169 Or Paf.Supervisor_Assignment_Id = Paf2.Assignment_Id) ) )
3170 AND ( to_date(p_term_date, 'YYYY-MM-DD') BETWEEN paf2.effective_start_date(+) AND paf2.effective_end_date(+))
3171 AND paf2.job_id = jbs2.job_id (+)
3172 AND paf2.position_id = pos2.position_id (+)
3173 AND pos2.language(+) = userenv('LANG')
3174 AND Paf2.Organization_Id = Org2.Organization_Id (+)
3175 AND org2.language(+) = userenv('LANG');
3176
3177 CURSOR lc_object_version_no(cp_assignment_id number
3178 ,cp_effective_date date) IS
3179 SELECT object_version_number,
3180 assignment_type
3181 FROM per_all_assignments_f
3182 WHERE assignment_id = cp_assignment_id
3183 AND (cp_effective_date BETWEEN
3184 NVL ( effective_start_date , cp_effective_date)
3185 AND NVL ( effective_end_date , cp_effective_date )) ;
3186
3187 Cur_Get_Direct_Reports Cp_Get_Direct_Reports%Rowtype;
3188
3189 L_Assignment_Security_Profile Varchar2(30);
3190 L_Old_Sup_Asg_Id Number;
3191 l_new_sup_asg_id number;
3192 l_old_sup_id number;
3193 l_new_sup_id number;
3194 allowMultipleAsg varchar2(80);
3195 xBgProfileValue varchar2(80);
3196 supDirId number;
3197 l_tmp_new_asg_id number;
3198 lv_param_1 varchar2(200);
3199 lv_param_2 varchar2(200);
3200 lv_param_3 varchar2(200);
3201 lv_param_4 varchar2(200);
3202 lv_param_5 varchar2(200);
3203 lv_param_6 varchar2(200);
3204 lv_param_7 varchar2(200);
3205 lv_param_8 varchar2(200);
3206 lv_param_9 varchar2(200);
3207 lv_param_10 varchar2(200);
3208 lv_param_11 varchar2(200);
3209 lv_param_12 varchar2(200);
3210 lv_param_13 varchar2(200);
3211 lv_param_14 varchar2(200);
3212 lv_param_15 varchar2(200);
3213 l_validate boolean;
3214
3215 ln_object_version_no NUMBER ;
3216 ln_assignment_type per_all_assignments_f.assignment_type%type;
3217 ln_comment_id NUMBER ;
3218 ld_effective_start_date DATE;
3219 ld_effective_end_date DATE;
3220 lb_no_managers_warning BOOLEAN ;
3221 lb_other_manager_warning BOOLEAN ;
3222
3223 begin
3224
3225 l_new_sup_asg_id := p_new_sup_asg_id;
3226 l_old_sup_id := p_old_sup_person_id;
3227 l_new_sup_id := p_new_sup_person_id;
3228
3229 -- create workflow item attributes
3230
3231 hr_approval_ss.create_item_attrib_if_notexist(p_item_type, p_item_key, 'QK_TERM_OLD_SUP_PER_ID', p_old_sup_person_id, null, null);
3232 hr_approval_ss.create_item_attrib_if_notexist(p_item_type, p_item_key, 'QK_TERM_OLD_SUP_ASG_ID', p_old_sup_asg_id, null, null);
3233 hr_approval_ss.create_item_attrib_if_notexist(p_item_type, p_item_key, 'QK_TERM_OLD_SUP_BG_ID', p_old_sup_bg_id, null, null);
3234 hr_approval_ss.create_item_attrib_if_notexist(p_item_type, p_item_key, 'QK_TERM_NEW_SUP_PER_ID', p_new_sup_person_id, null, null);
3235 hr_approval_ss.create_item_attrib_if_notexist(p_item_type, p_item_key, 'QK_TERM_NEW_SUP_ASG_ID', p_new_sup_asg_id, null, null);
3236 hr_approval_ss.create_item_attrib_if_notexist(p_item_type, p_item_key, 'QK_TERM_LOGIN_PER_ID', p_login_person_id, null, null);
3237
3238 if ( nvl(l_new_sup_id, -1) = -1) then
3239 l_new_sup_id := null;
3240 end if;
3241
3242 if ( nvl(l_new_sup_asg_id, -1) = -1) then
3243 l_new_sup_asg_id := null;
3244 end if;
3245
3246 l_assignment_security_profile := hr_general2.supervisor_assignments_in_use ;
3247
3248 if ( p_validate = 'Y' ) then
3249 savepoint madr_sp;
3250 end if;
3251
3252 open cp_get_direct_reports(cp_old_sup_person_id => p_old_sup_person_id
3253 , cp_term_date => p_term_date
3254 , cp_flow_identifier => 'termination_flow' );
3255 loop
3256 fetch cp_get_direct_reports into cur_get_direct_reports;
3257 exit when cp_get_direct_reports%notfound;
3258
3259 if ( l_assignment_security_profile = 'true' ) then
3260
3261 --
3262 allowMultipleAsg := fnd_profile.value('HR_ALLOW_MULTIPLE_ASSIGNMENTS');
3263 xBgProfileValue := fnd_profile.value('HR_CROSS_BUSINESS_GROUP');
3264
3265 hr_utility.trace('allowMultipleAsg = ' || allowMultipleAsg);
3266 hr_utility.trace('xBgProfileValue = ' || xBgProfileValue);
3267
3268 if ( allowMultipleAsg is not null and allowMultipleAsg = 'Y' ) then
3269 lv_param_1 := 'Y';
3270 lv_param_2 := 'N';
3271 lv_param_9 := 'Y';
3272 lv_param_10 := 'N';
3273 else
3274 lv_param_1 := 'Y';
3275 lv_param_2 := 'Y';
3276 lv_param_9 := 'Y';
3277 lv_param_10 := 'N';
3278 end if;
3279
3280 lv_param_3 := p_term_date;
3281 lv_param_4 := p_term_date;
3282 lv_param_5 := p_term_date;
3283 lv_param_11 := p_term_date;
3284
3285 if ( xBgProfileValue is not null and xBgProfileValue = 'Y' ) then
3286 lv_param_6 := 'Y';
3287 lv_param_7 := '1';
3288 else
3289 lv_param_6 := 'N';
3290 lv_param_7 := fnd_number.number_to_canonical(cur_get_direct_reports.business_group_id);
3291 end if;
3292
3293 supDirId := fnd_number.number_to_canonical(cur_get_direct_reports.person_id);
3294
3295 if ( supDirId is null ) then
3296 supDirId := fnd_number.number_to_canonical(p_login_person_id);
3297 end if;
3298
3299 lv_param_12 := fnd_number.number_to_canonical(cur_get_direct_reports.assignment_id);
3300 lv_param_13 := fnd_number.number_to_canonical(p_old_sup_person_id);
3301 lv_param_14 := fnd_number.number_to_canonical(cur_get_direct_reports.person_id);
3302 lv_param_15 := fnd_number.number_to_canonical(supDirId);
3303
3304 l_tmp_new_asg_id := hr_termination_ss.get_sup_asg_byprfl(
3305 p_param_1 => lv_param_1
3306 ,p_param_2 => lv_param_2
3307 ,p_param_3 => lv_param_3
3308 ,p_param_4 => lv_param_4
3309 ,p_param_5 => lv_param_5
3310 ,p_param_6 => lv_param_6
3311 ,p_param_7 => lv_param_7
3312 ,p_param_8 => lv_param_8
3313 ,p_param_9 => lv_param_9
3314 ,p_param_10 => lv_param_10
3315 ,p_param_11 => lv_param_11
3316 ,p_param_12 => lv_param_12
3317 ,p_param_13 => lv_param_13
3318 ,p_param_14 => lv_param_14
3319 ,p_param_15 => lv_param_15);
3320
3321 if ( l_tmp_new_asg_id is not null ) then
3322 l_new_sup_asg_id := l_tmp_new_asg_id;
3323 end if;
3324
3325 end if;
3326
3327 if ( p_validate = 'Y' ) then
3328 l_validate := true;
3329 else
3330 l_validate := false;
3331 end if;
3332
3333 OPEN lc_object_version_no(cp_assignment_id => cur_get_direct_reports.assignment_id
3334 ,cp_effective_date => cur_get_direct_reports.effective_date) ;
3335 FETCH lc_object_version_no into ln_object_version_no, ln_assignment_type ;
3336 CLOSE lc_object_version_no ;
3337
3338 if ( (p_new_sup_person_id is not null and p_new_sup_person_id <> -1) OR
3339 (l_new_sup_asg_id is not null and l_new_sup_asg_id <> -1) ) then
3340
3341 -- Update Supervisor
3342 hr_assignment_att.update_asg(
3343 p_validate=> l_validate ,
3344 p_effective_date => cur_get_direct_reports.effective_date ,
3345 p_attribute_update_mode=> 'ATTRIBUTE_UPDATE' ,
3346 p_assignment_id=> cur_get_direct_reports.assignment_id ,
3347 p_assignment_type => ln_assignment_type,
3348 p_object_version_number => ln_object_version_no ,
3349 p_supervisor_id => p_new_sup_person_id ,
3350 p_supervisor_assignment_id => l_new_sup_asg_id ,
3351 p_comment_id => ln_comment_id ,
3352 p_effective_start_date=> ld_effective_start_date,
3353 p_effective_end_date=> ld_effective_end_date,
3354 p_no_managers_warning=> lb_no_managers_warning,
3355 p_other_manager_warning=> lb_other_manager_warning ) ;
3356 end if;
3357
3358 end loop;
3359
3360 if ( p_validate = 'Y' ) then
3361 rollback to savepoint madr_sp;
3362 end if;
3363
3364 exception
3365 when others then
3366 hr_utility.trace('In exception moveAllDirectReports !!');
3367 if ( p_validate = 'Y' ) then
3368 rollback to savepoint madr_sp;
3369 end if;
3370 raise;
3371 end moveAllDirectReports;
3372
3373
3374 END hr_termination_ss;