[Home] [Help]
PACKAGE BODY: APPS.PER_OTHERBG_APL_API
Source
1 Package Body per_otherbg_apl_api as
2 /* $Header: peobgpkg.pkb 120.9 2011/08/18 06:00:05 ghshanka noship $ */
3
4 g_package varchar2(33) := 'per_otherbg_apl_api.';
5 --
6 -- ---------------------------------------------------------------------------
7 -- |------------------------< close_application >---------------------------|
8 -- ---------------------------------------------------------------------------
9 --
10 procedure close_application(
11 apl_assignment_id in per_all_assignments_f.assignment_id%type,
12 effective_date date
13 ) is
14 l_proc varchar2(72) := g_package||'close_application';
15
16 cursor csr_person_id(l_apl_asg_id per_all_assignments_f.assignment_id%type,l_effective_date date) is
17 select papf.person_id,papf.object_version_number,papf.business_group_id,paaf.object_version_number
18 from per_all_people_f papf, per_all_assignments_f paaf
19 where paaf.assignment_id = l_apl_asg_id
20 and l_effective_date between nvl(paaf.effective_start_date,l_effective_date) and nvl(paaf.effective_end_date,l_effective_date)
21 and l_effective_date between nvl(papf.effective_start_date,l_effective_date) and nvl(papf.effective_end_date,l_effective_date)
22 and papf.person_id = paaf.person_id;
23
24 cursor csr_get_irc_asg_status(bg_id number) is
25 select assignment_status_type_id
26 from per_assignment_status_types
27 where PER_SYSTEM_STATUS= 'ACTIVE_ASG_IN_OTH_BG'
28 and ACTIVE_FLAG ='Y' and DEFAULT_FLAG='Y';
29 -- and (business_group_id is null or
30 -- business_group_id = bg_id);
31
32 cursor csr_get_per_asg_status(bg_id number) is
33 select assignment_status_type_id
34 from per_assignment_status_types
35 where PER_SYSTEM_STATUS= 'TERM_APL'
36 and ACTIVE_FLAG ='Y' and DEFAULT_FLAG='Y'
37 and (business_group_id is null or
38 business_group_id = bg_id);
39
40 cursor csr_apl_assignments(l_person_id per_all_assignments_f.person_id%type,l_effective_date date,
41 l_apl_asg_id per_all_assignments_f.assignment_id%type) is
42 select 1
43 from per_all_assignments_f
44 where person_id = l_person_id
45 and l_effective_date +1 between nvl(effective_start_date,l_effective_date)
46 and nvl(effective_end_date,l_effective_date)
47 and assignment_id <> l_apl_asg_id
48 and assignment_type = 'A';
49
50 cursor csr_apl_asg_end_date(l_person_id number , l_effective_date date , l_apl_asg_id number)
51 is
52 select max(effective_end_date)
53 from per_all_assignments_f
54 where person_id = l_person_id
55 and assignment_id <> l_apl_asg_id
56 and assignment_type = 'A';
57
58 l_exists varchar2(1);
59 l_asg_max_end_date date;
60 l_person_id per_all_assignments_f.person_id%type;
61 a_object_version_number per_all_assignments_f.object_version_number%TYPE;
62 l_object_version_number per_all_people_f.object_version_number%TYPE;
63 l_business_group_id per_all_people_f.business_group_id%TYPE;
64 l_assignment_status_type_id per_assignment_status_types.assignment_status_type_id%TYPE;
65 i_assignment_status_type_id per_assignment_status_types.assignment_status_type_id%TYPE;
66
67 i_assignment_status_id irc_assignment_statuses.assignment_id%type;
68 i_object_version_number irc_assignment_statuses.object_version_number%type;
69
70 t_effective_start_date per_all_people_f.effective_start_date%TYPE;
71 t_effective_end_date per_all_people_f.effective_end_date%TYPE;
72 t_remove_fut_asg_warning BOOLEAN;
73
74 a_effective_start_date per_all_assignments_f.effective_start_date%TYPE;
75 a_effective_end_date per_all_assignments_f.effective_end_date%TYPE;
76
77 begin
78 hr_utility.set_location('Entering:'|| l_proc, 5);
79
80 per_otherbg_apl_api.g_otherbg_hire_in_process := true;
81
82 hr_utility.set_location('close_application:apl_assignment_id:'|| apl_assignment_id, 10);
83
84 open csr_person_id(apl_assignment_id,effective_date);
85 fetch csr_person_id into l_person_id,l_object_version_number,l_business_group_id,a_object_version_number;
86 close csr_person_id;
87
88 hr_utility.set_location('close_application:person_id:'|| l_person_id, 15);
89 hr_utility.set_location('close_application:l_object_version_number:'|| l_object_version_number, 15);
90 hr_utility.set_location('close_application:l_business_group_id:'|| l_business_group_id, 15);
91 hr_utility.set_location('close_application:a_object_version_number:'|| a_object_version_number, 15);
92
93 open csr_get_irc_asg_status(l_business_group_id);
94 fetch csr_get_irc_asg_status into i_assignment_status_type_id;
95 close csr_get_irc_asg_status;
96
97 hr_utility.set_location('close_application:i_assignment_status_type_id:'|| i_assignment_status_type_id, 20);
98
99 IRC_ASG_STATUS_API.create_irc_asg_status
100 (p_assignment_id => apl_assignment_id
101 , p_assignment_status_type_id => i_assignment_status_type_id
102 , p_status_change_date => effective_date + 1 -- Fix for the bug#12585001
103 , p_assignment_status_id => i_assignment_status_id
104 , p_object_version_number => i_object_version_number);
105
106 open csr_get_per_asg_status(l_business_group_id);
107 fetch csr_get_per_asg_status into l_assignment_status_type_id;
108 close csr_get_per_asg_status;
109
110 open csr_apl_assignments(l_person_id,effective_date,apl_assignment_id);
111 fetch csr_apl_assignments into l_exists;
112 if csr_apl_assignments%found then
113 close csr_apl_assignments;
114
115 -- this will verify if the other assignment is running till eot or not
116 -- if not then we cannot terminate/end the current assignment as the person will remain
117 -- active applicant and there will not be assignment(applicaiton) running till eot
118 -- hence we need to raise an error as there can be n number of such scenarios.
119
120 open csr_apl_asg_end_date(l_person_id,effective_date,apl_assignment_id);
121 fetch csr_apl_asg_end_date into l_asg_max_end_date;
122 close csr_apl_asg_end_date;
123
124 if l_asg_max_end_date <> hr_api.g_eot then
125 -- raise the error as the other assignment whose end date is greater than the current one
126 -- if not running till eot . so end dating the current one
127 hr_utility.set_message(800,'HR_7075_APL_ASS_ONLY_ASS');
128 hr_utility.raise_error;
129 end if;
130
131 hr_utility.set_location('close_application:Multiple Applications In BG', 25);
132 hr_assignment_api.terminate_apl_asg(
133 p_validate => FALSE,
134 p_effective_date => effective_date,
135 p_assignment_id => apl_assignment_id,
136 p_assignment_status_type_id => l_assignment_status_type_id,
137 p_object_version_number => a_object_version_number,
138 p_effective_start_date => a_effective_start_date,
139 p_effective_end_date => a_effective_end_date
140 );
141
142 else
143 close csr_apl_assignments;
144 hr_utility.set_location('close_application:Single Application In BG', 25);
145 hr_applicant_api.terminate_applicant(
146 p_validate => FALSE,
147 p_effective_date => effective_date,
148 p_person_id => l_person_id,
149 p_object_version_number => l_object_version_number,
150 p_person_type_id => hr_person_type_usage_info.get_default_person_type_id(l_business_group_id,'EX_APL'),
151 p_termination_reason => NULL,
152 p_assignment_status_type_id => l_assignment_status_type_id,
153 p_effective_start_date => t_effective_start_date,
154 p_effective_end_date => t_effective_end_date,
155 p_remove_fut_asg_warning => t_remove_fut_asg_warning);
156
157 end if;
158 per_otherbg_apl_api.g_otherbg_hire_in_process := false;
159 hr_utility.set_location('Leaving:'|| l_proc, 999);
160 end close_application;
161 --
162 -- ---------------------------------------------------------------------------
163 -- |------------------------< close_source_application >---------------------------|
164 -- ---------------------------------------------------------------------------
165 --
166 procedure close_source_application(
167 apl_assignment_id in per_all_assignments_f.assignment_id%type,
168 effective_date date
169 ) is
170 l_proc varchar2(72) := g_package||'close_source_application';
171
172 cursor csr_irc_asg(l_tgt_assignment_id per_all_assignments_f.assignment_id%type,l_effective_date date) is
173 select src_apl_asg_id
174 from per_vac_linked_assignments
175 where tgt_apl_asg_id = l_tgt_assignment_id
176 and l_effective_date between trunc(nvl(start_date,l_effective_date)) and trunc(nvl(end_date,l_effective_date)) ;
177
178 l_src_apl_asg_id per_all_assignments_f.assignment_id%type;
179
180 begin
181 hr_utility.set_location('Entering:'|| l_proc, 5);
182 open csr_irc_asg(apl_assignment_id,effective_date);
183 fetch csr_irc_asg into l_src_apl_asg_id;
184 close csr_irc_asg;
185 hr_utility.set_location('close_source_application:src_apl_asg_id'|| l_src_apl_asg_id, 10);
186 if l_src_apl_asg_id is not null then
187 hr_utility.set_location('Source Application Start:'|| l_src_apl_asg_id, 5);
188 close_application(l_src_apl_asg_id,effective_date);
189 hr_utility.set_location('Source Application Close:'|| l_src_apl_asg_id, 5);
190 end if;
191 hr_utility.set_location('Leaving:'|| l_proc, 999);
192 end close_source_application;
193 --
194 -- ---------------------------------------------------------------------------
195 -- |------------------------< close_target_applications >---------------------------|
196 -- ---------------------------------------------------------------------------
197 --
198 procedure close_target_applications(
199 apl_assignment_id in per_all_assignments_f.assignment_id%type,
200 effective_date date
201 ) is
202 l_proc varchar2(72) := g_package||'close_target_applications';
203
204 cursor csr_irc_src_asg(l_src_assignment_id per_all_assignments_f.assignment_id%type,l_effective_date date) is
205 select tgt_apl_asg_id
206 from per_vac_linked_assignments
207 where src_apl_asg_id = l_src_assignment_id
208 and l_effective_date between trunc(nvl(start_date,l_effective_date)) and trunc(nvl(end_date,l_effective_date))
209 and tgt_apl_asg_id <> apl_assignment_id;
210
211 l_trg_apl_asg_id per_all_assignments_f.assignment_id%type;
212
213 begin
214 hr_utility.set_location('Entering:'|| l_proc, 5);
215
216 open csr_irc_src_asg(apl_assignment_id,effective_date);
217 loop
218 fetch csr_irc_src_asg into l_trg_apl_asg_id;
219 exit when csr_irc_src_asg%notfound;
220 hr_utility.set_location('Target Application Start:'|| l_trg_apl_asg_id, 5);
221 close_application(l_trg_apl_asg_id,effective_date);
222 hr_utility.set_location('Target Application Close:'|| l_trg_apl_asg_id, 5);
223 end loop;
224 close csr_irc_src_asg;
225 hr_utility.set_location('Leaving:'|| l_proc, 999);
226 end close_target_applications;
227 --
228 -- ---------------------------------------------------------------------------
229 -- |------------------------< close_otherbg_applications >---------------------------|
230 -- ---------------------------------------------------------------------------
231 --
232 procedure close_otherbg_applications(
233 apl_assignment_id in per_all_assignments_f.assignment_id%type,
234 effective_date date,
235 close_mode varchar2
236 ) is
237 l_proc varchar2(72) := g_package||'close_otherbg_applications';
238 begin
239 hr_utility.set_location('Entering:'|| l_proc, 5);
240
241 if close_mode = 'HIRE_CLOSE' then
242 -- Close the Source Application if the passed application is a target application.
243 close_source_application(apl_assignment_id,effective_date);
244
245 -- Close the Target Applications if the passed application is a source application.
246 -- I dont think this needs to be called as there wont be any Target application
247 -- if I am hiring in the source application.
248 -- close_target_applications(apl_assignment_id,effective_date);
249
250 elsif close_mode = 'HIRE_END' then
251 -- Close the Target Applications if the passed application is a source application.
252 close_target_applications(apl_assignment_id,effective_date);
253 -- Close the Source Application if the passed application is a target application.
254 close_source_application(apl_assignment_id,effective_date);
255 end if;
256 hr_utility.set_location('Leaving:'|| l_proc, 999);
257 end close_otherbg_applications;
258 --
259 -- ---------------------------------------------------------------------------
260 -- |------------------------< open_source_application >---------------------------|
261 -- ---------------------------------------------------------------------------
262 --
263 procedure open_source_application(
264 apl_assignment_id in per_all_assignments_f.assignment_id%type,
265 effective_date date
266 ) is
267 l_proc varchar2(72) := g_package||'open_source_application';
268
269 cursor csr_irc_asg(l_tgt_assignment_id per_all_assignments_f.assignment_id%type,l_effective_date date) is
270 select src_apl_asg_id
271 from per_vac_linked_assignments
272 where tgt_apl_asg_id = l_tgt_assignment_id;
273
274 l_src_apl_asg_id per_all_assignments_f.assignment_id%type;
275
276 begin
277 hr_utility.set_location('Entering:'|| l_proc, 5);
278 open csr_irc_asg(apl_assignment_id,effective_date);
279 fetch csr_irc_asg into l_src_apl_asg_id;
280 close csr_irc_asg;
281 hr_utility.set_location('open_source_application:src_apl_asg_id'|| l_src_apl_asg_id, 10);
282 if l_src_apl_asg_id is not null then
283 hr_utility.set_location('Source Application Start:'|| l_src_apl_asg_id, 5);
284 reverse_terminate(l_src_apl_asg_id,effective_date);
285 hr_utility.set_location('Source Application Open:'|| l_src_apl_asg_id, 5);
286 end if;
287 hr_utility.set_location('Leaving:'|| l_proc, 999);
288 end open_source_application;
289 --
290 -- ---------------------------------------------------------------------------
291 -- |------------------------< open_otherbg_applications >---------------------------|
292 -- ---------------------------------------------------------------------------
293 --
294 procedure open_otherbg_applications(
295 apl_assignment_id in per_all_assignments_f.assignment_id%type,
296 effective_date date,
297 open_mode varchar2
298 ) is
299 l_proc varchar2(72) := g_package||'open_otherbg_applications';
300 begin
301 hr_utility.set_location('Entering:'|| l_proc, 5);
302
303 if open_mode = 'CANCEL_HIRE' then
304 -- Open the Source Application if the passed application is a target application.
305 open_source_application(apl_assignment_id,effective_date);
306 elsif open_mode = 'CANCEL_END' then
307 -- Open the Source Application if the passed application is a target application.
308 open_source_application(apl_assignment_id,effective_date);
309 end if;
310 hr_utility.set_location('Leaving:'|| l_proc, 999);
311 end open_otherbg_applications;
312 --
313 -- ---------------------------------------------------------------------------
314 -- |------------------------< isMultiRegVac >---------------------------|
315 -- ---------------------------------------------------------------------------
316 --
317 function isMultiRegVac(apl_assignment_id in per_all_assignments_f.assignment_id%type) return boolean
318 is
319 l_proc varchar2(72) := g_package||'isMultiRegVac';
320 cursor cur_tgt_applications is
321 select SRC_APL_ASG_ID
322 from PER_VAC_LINKED_ASSIGNMENTS
323 where TGT_APL_ASG_ID = apl_assignment_id;
324
325 cursor cur_src_applications is
326 select TGT_APL_ASG_ID
327 from PER_VAC_LINKED_ASSIGNMENTS
328 where SRC_APL_ASG_ID = apl_assignment_id;
329 --
330 l_tgt_apl_asg_id per_vac_linked_assignments.tgt_apl_asg_id%type;
331 is_muti_regional_vacancy boolean :=false;
332 --
333 begin
334 hr_utility.set_location('Entering:'|| l_proc, 5);
335 hr_utility.set_location('isMultiRegVac:apl_assignment_id:'|| apl_assignment_id, 10);
336 is_muti_regional_vacancy := false;
337 open cur_tgt_applications;
338 fetch cur_tgt_applications into l_tgt_apl_asg_id;
339 if cur_tgt_applications%found then
340 close cur_tgt_applications;
341 hr_utility.set_location('isMultiRegVac:Passed Application ID is a Target', 10);
342 is_muti_regional_vacancy := true;
343 else
344 close cur_tgt_applications;
345 open cur_src_applications;
346 fetch cur_src_applications into l_tgt_apl_asg_id;
347 if cur_src_applications%found then
348 hr_utility.set_location('isMultiRegVac:Passed Application ID is a Source', 10);
349 is_muti_regional_vacancy := true;
350 end if;
351 close cur_src_applications;
352 end if;
353 hr_utility.set_location('Leaving:'|| l_proc, 999);
354 return is_muti_regional_vacancy;
355 end isMultiRegVac;
356
357
358
359
360 -- we need to pass the source applicant assignment id.. to remove the end date / change the ex applicant to applicant
361 -- comments added
362
363 PROCEDURE reverse_terminate(apl_assignment_id in per_all_assignments_f.assignment_id%type,
364 effective_date date) IS
365
366 l_proc varchar2(72) := g_package||'reverse_terminate';
367
368 cursor person_details(l_apl_assignment_id per_all_assignments_f.assignment_id%type) is
369 select papf.person_id, papf.application_id, papf.business_group_id, papf.effective_end_date
370 from per_all_assignments_f papf
371 where papf.assignment_id = apl_assignment_id;
372 -- and effective_date between nvl(papf.effective_start_date,effective_date) and nvl(papf.effective_end_date,effective_date) ;
373
374 cursor csr_ptu_row(l_person_id per_all_assignments_f.person_id%type,l_date_received Date) is
375 select ptu.effective_start_date
376 from per_person_type_usages_f ptu
377 ,per_person_types ppt
378 where ptu.person_id = l_person_id
379 and ptu.effective_start_date > l_date_received
380 and ptu.person_type_id = ppt.person_type_id
381 and ppt.system_person_type = 'EX_APL'
382 order by ptu.effective_start_date;
383
384 l_exists varchar2(1);
385
386 l_person_id per_all_assignments_f.person_id%type;
387 l_business_group_id per_all_assignments_f.business_group_id%type;
388 l_application_id per_all_assignments_f.application_id %type;
389 l_end_of_time DATE;
390 l_legislation_code per_business_groups.legislation_code%type;
391 l_date_end per_all_assignments_f.effective_end_date%type;
392 l_date_received Date;
393 l_date_end1 Date;
394 l_new_end_date Date;
395 l_exists_p varchar2(1);
396
397
398 --- Fix For Bug # 12654541 ---
399 cursor csr_get_irc_asg_status(bg_id number) is
400 select assignment_status_type_id
401 from per_assignment_status_types
402 where PER_SYSTEM_STATUS= 'ACTIVE_ASG_IN_OTH_BG'
403 and ACTIVE_FLAG ='Y' and DEFAULT_FLAG='Y'
404 and (business_group_id is null or
405 business_group_id = bg_id);
406
407 cursor irc_asg_status(l_asg_status_type_id
408 per_assignment_status_types.assignment_status_type_id%TYPE) is
409 select iass.assignment_status_id, iass.object_version_number
410 from irc_assignment_statuses iass
411 where iass.assignment_status_id =
412 (select max(iass.assignment_status_id)
413 from irc_assignment_statuses iass
414 where iass.assignment_id = apl_assignment_id
415 and iass.assignment_status_type_id = l_asg_status_type_id);
416
417 i_assignment_status_type_id per_assignment_status_types.assignment_status_type_id%TYPE;
418 l_asg_status_id irc_assignment_statuses.assignment_status_id%type;
419 l_asg_status_ovn irc_assignment_statuses.object_version_number%type;
420 --- Fix For Bug # 12654541 ---
421
422 -- bug 12868703
423 l_apl_bg_id number;
424 l_asg_end_date date;
425
426
427 cursor csr_apl_assignments(l_person_id per_all_assignments_f.person_id%type,l_effective_date date,
428 l_apl_asg_id per_all_assignments_f.assignment_id%type,l_apl_bg_id number) is
429 select 1
430 from per_all_assignments_f
431 where person_id = l_person_id
432 and l_effective_date + 1 between nvl(effective_start_date,l_effective_date)
433 and nvl(effective_end_date,l_effective_date) -- added 1 to the date for bug#12593632 fix
434 and assignment_id <> l_apl_asg_id
435 and assignment_type = 'A'
436 and business_group_id =l_apl_bg_id;-- added new
437
438 -- 12868703
439
440
441 BEGIN
442 hr_utility.set_location('Entering:'|| l_proc, 5);
443 hr_utility.set_location('apl_assignment_id :'|| apl_assignment_id, 5);
444 l_end_of_time := hr_api.g_eot; --to_date('31-Dec-4712','dd-Mon-yyyy');
445 l_new_end_date := null;
446
447 open person_details(apl_assignment_id );
448 -- if person_details%found then
449 fetch person_details into l_person_id, l_application_id, l_business_group_id, l_date_end;
450 close person_details;
451 -- end if;
452
453 --- Fix For Bug # 12654541 ---
454 open csr_get_irc_asg_status(l_business_group_id);
455 fetch csr_get_irc_asg_status into i_assignment_status_type_id;
456 close csr_get_irc_asg_status;
457 --- Fix For Bug # 12654541 ---
458
459 -- 12868703
460 select distinct business_group_id into l_apl_bg_id
461 from per_all_assignments_f
462 where assignment_id = apl_assignment_id;
463
464 select max(effective_end_date) into l_asg_end_date
465 from per_all_assignments_f
466 where assignment_id = apl_assignment_id;
467
468 hr_utility.set_location('l_apl_bg_id :'|| l_apl_bg_id, 5);
469 hr_utility.set_location('l_asg_end_date :'|| l_asg_end_date, 5);
470 -- 12868703
471
472 open csr_apl_assignments(l_person_id,l_asg_end_date,apl_assignment_id,l_apl_bg_id);
473 fetch csr_apl_assignments into l_exists_p;
474
475 If csr_apl_assignments%found then
476 close csr_apl_assignments;
477 hr_utility.set_location('More than one Application :', 50);
478 per_app_asg_pkg.pre_delete_validation
479 ( p_business_group_id => l_business_group_id,
480 p_assignment_id => apl_assignment_id,
481 p_application_id => l_application_id,
482 p_person_id => l_person_id,
483 p_session_date => l_date_end - 1 , --this should be close date - 1
484 p_validation_start_date => l_date_end + 1, --This would be close date + 1
485 p_validation_end_date => l_end_of_time, --This is end of time
486 p_delete_mode => 'DELETE_NEXT_CHANGE',
487 p_new_end_date => l_new_end_date ) ; -- This parameter need to check once
488
489 delete_row1
490 (p_assignment_id => apl_assignment_id,
491 p_new_end_date => null,
492 p_effective_end_date => l_date_end , -- this is close date
493 p_validation_end_date => l_end_of_time, -- this is end of time
494 p_session_date => l_date_end - 1, -- this should be close date - 1
495 p_delete_mode => 'DELETE_NEXT_CHANGE') ;
496
497 else
498
499 close csr_apl_assignments;
500 hr_utility.set_location('Only one Application :', 500);
501 select legislation_code into l_legislation_code
502 from per_business_groups
503 where business_group_id in
504 (select business_group_id
505 from per_all_assignments_f
506 where assignment_id = apl_assignment_id);
507
508 per_applications_pkg.cancel_chk_current_emp(
509 P_person_id =>l_person_id,
510 P_Business_group_id => l_business_group_id,
511 p_date_end => l_date_end);
512
513 per_applications_pkg.canc_chk_fut_per_changes(
514 p_person_id => l_person_id,
515 p_application_id => l_application_id,
516 P_date_end => l_date_end);
517
518 hr_utility.set_location('Entering:'|| l_proc, 10);
519 hr_utility.set_location('l_person_id :'|| l_person_id, 100);
520 hr_utility.set_location('l_business_group_id :'|| l_business_group_id, 20);
521 hr_utility.set_location('fnd_global.user_id() :'|| fnd_global.user_id(), 200);
522 hr_utility.set_location(' P_date_end :'|| l_date_end , 30);
523 hr_utility.set_location(' fnd_global.login_id() :'|| fnd_global.login_id(), 300);
524 hr_utility.set_location(' l_end_of_time :'|| l_end_of_time, 40);
525
526 per_applications_pkg.maintain_ppt_cancel(
527 P_person_id => l_person_id,
528 P_Business_group_id => l_business_group_id,
529 P_date_end => l_date_end,
530 P_last_updated_by => fnd_global.user_id(),
531 P_last_update_login => fnd_global.login_id(),
532 P_end_of_time => l_end_of_time);
533
534 hr_utility.set_location('Entering:'|| l_proc, 15);
535
536 per_applications_pkg.sec_statuses_cancel(
537 p_end_date => l_date_end,
538 p_application_id => l_application_id,
539 p_business_group_id => l_business_group_id,
540 p_last_updated_by => fnd_global.user_id(),
541 p_last_update_login => fnd_global.login_id(),
542 p_person_id => l_person_id);
543
544 --- Fix For Bug # 12654541 ---
545 /* per_applications_pkg.maintain_irc_ass_status(
546 p_person_id => l_person_id,
547 p_business_group_id => l_business_group_id,
548 p_date_end => l_date_end,
549 p_effective_date => effective_date, --:ctl_globals.session_date,
550 p_application_id => l_application_id,
551 p_legislation_code => l_legislation_code,
552 p_action => 'CANCEL');
553 */
554 open irc_asg_status(i_assignment_status_type_id);
555 fetch irc_asg_status into l_asg_status_id, l_asg_status_ovn;
556 if irc_asg_status%found then
557 --
558 IRC_ASG_STATUS_API.delete_irc_asg_status
559 (p_assignment_status_id => l_asg_status_id
560 ,p_object_version_number => l_asg_status_ovn);
561 --
562 end if;
563 close irc_asg_status;
564 --- Fix For Bug # 12654541 ---
565
566 -- Fix for the issue in the bug#12593632. Changed the call cancel_update_assigns_obg
567 per_applications_pkg.cancel_update_assigns_obg(
568 p_person_id => l_person_id,
569 p_business_group_id => l_business_group_id,
570 P_date_end => l_date_end,
571 P_application_id => l_application_id,
572 p_legislation_code => l_legislation_code,
573 P_end_of_time => l_end_of_time,
574 P_last_updated_by => fnd_global.user_id(),
575 p_last_update_login => fnd_global.login_id(),
576 p_assignment_id => apl_assignment_id);
577
578 per_applications_pkg.maint_security_cancel(
579 P_person_id => l_person_id);
580
581
582 per_applications_pkg.del_letters_cancel(
583 p_business_group_id => l_business_group_id,
584 P_person_id => l_person_id,
585 p_application_id => l_application_id
586 );
587
588
589 --Re opening the application in the per_applications
590 UPDATE PER_APPLICATIONS
591 SET date_end = null
592 where application_id = l_application_id;
593
594 -- code for PTU table.
595
596 select date_received into l_date_received
597 from per_applications
598 where application_id = l_application_id;
599
600 open csr_ptu_row(l_person_id,l_date_received) ;
601 fetch csr_ptu_row into l_date_end1;
602 close csr_ptu_row;
603
604 hr_utility.set_location('per_applications_pkg.l_date_received = '||to_char(l_date_received,'DD/MM/YYYY'),60);
605 hr_utility.set_location('per_applications_pkg.l_date_end = '||to_char(l_date_end1,'DD/MM/YYYY'),60);
606 hr_utility.set_location('per_applications_pkg.l_person_id = '||to_char(l_person_id),60);
607
608 hr_per_type_usage_internal.cancel_person_type_usage
609 (
610 p_effective_date => l_date_end1
611 ,p_person_id => l_person_id
612 ,p_system_person_type => 'EX_APL'
613 );
614
615 end if;
616 end reverse_terminate;
617
618 procedure set_end_date(
619 p_new_end_date date,
620 p_assignment_id number ) is
621 --
622 begin
623 --
624 update per_assignments_f a
625 set a.effective_end_date = p_new_end_date
626 where a.assignment_id = p_assignment_id
627 and a.effective_end_date = (
628 select max(a2.effective_end_date)
629 from per_assignments_f a2
630 where a2.assignment_id = a.assignment_id);
631 --
632 end set_end_date;
633
634 procedure delete_child ( p_assignment_id in number,
635 p_delete_mode in varchar2) is
636
637 p_del_flag VARCHAR2(1) := 'N';
638
639 --
640 BEGIN
641
642 -- hr_utility.set_location ( 'PER_APP_ASG_PKG.delete_child' , 5) ;
643 --
644 BEGIN
645 select 'Y'
646 into p_del_flag
647 from sys.dual
648 where exists (
649 select null
650 from per_assignment_budget_values_f
651 where assignment_id = p_assignment_id
652 and p_delete_mode = 'ZAP');
653
654 EXCEPTION
655 WHEN NO_DATA_FOUND THEN NULL;
656 END;
657 --
658 IF p_del_flag = 'Y' and
659 p_delete_mode = 'ZAP' THEN
660
661 Delete per_assignment_budget_values_f
662 where assignment_id = p_assignment_id;
663
664 END IF;
665 --
666 END delete_child;
667
668
669
670 procedure delete_row1(p_assignment_id number,
671 p_new_end_date date,
672 p_effective_end_date date,
673 p_validation_end_date date,
674 p_session_date date,
675 p_delete_mode varchar2 ) is
676
677 l_cost_warning boolean;
678 begin
679 --This API is similar to the one per_app_asg_pkg.delete_row
680 hr_utility.set_location ( 'PER_APP_ASG_PKG.delete_row1' , 5 ) ;
681 hr_utility.set_location('p_session_date :'|| p_session_date, 200);
682 hr_utility.set_location('p_effective_end_date :'|| p_effective_end_date, 200);
683 hr_utility.set_location('p_validation_end_date :'|| p_validation_end_date, 200);
684 hr_utility.set_location('p_delete_mode :'|| p_delete_mode, 200);
685 hr_utility.set_location('p_new_end_date :'|| p_new_end_date, 200);
686
687 delete_child ( p_assignment_id ,p_delete_mode);
688
689
690 -- delete from per_assignments_f a
691 -- where a.rowid = chartorowid(P_ROW_ID);
692 -- because we dont have rowid, replacing the above with the below one.
693
694 /*
695 delete from per_assignments_f a
696 where assignment_id = p_assignment_id;
697 */
698
699 if ( p_new_end_date is null ) then
700
701
702 if ( p_validation_end_date = hr_general.end_of_time ) then
703
704 hr_assignment.tidy_up_ref_int ( p_assignment_id,
705 'FUTURE',
706 p_validation_end_date,
707 p_effective_end_date,
708 null,
709 null ,
710 l_cost_warning) ;
711
712 update per_all_assignments_f
713 set effective_end_date = hr_api.g_eot -- to_date('31-Dec-4712','DD-MON-YYYY')
714 where assignment_id = p_assignment_id;
715
716 hr_utility.set_location ( 'PER_APP_ASG_PKG.delete_row1' , 15 ) ;
717 end if;
718 else
719 hr_assignment.tidy_up_ref_int ( p_assignment_id,
720 'FUTURE',
721 p_new_end_date,
722 p_effective_end_date,
723 null,
724 null,
725 l_cost_warning ) ;
726 hr_utility.set_location ( 'PER_APP_ASG_PKG.delete_row1' , 20 ) ;
727 end if;
728 --
729 if ( p_new_end_date is not null ) then
730 set_end_date ( p_new_end_date , p_assignment_id ) ;
731
732
733 hr_utility.set_location ( 'PER_APP_ASG_PKG.delete_row' , 25 ) ;
734 end if;
735 --
736 per_app_asg_pkg.cleanup_letters ( p_assignment_id ) ;
737
738
739 end delete_row1;
740
741
742 END per_otherbg_apl_api;
743