1 PACKAGE BODY PER_ASSIGNMENTS_F2_PKG AS
2 /* $Header: peasg01t.pkb 120.29.12010000.3 2008/09/29 05:42:27 ubhat ship $ */
3 g_package varchar2(24) := 'PER_ASSIGNMENTS_F2_PKG.';
4 g_debug boolean; -- debug flag
5 --
6
7 -----------------------------------------------------------------------------
8 --
9 -- Procedure to get s_* values of items. Called as first step of pre-update
10 -- and pre-delete.
11 --
12 -----------------------------------------------------------------------------
13 procedure get_save_fields(
14 p_row_id varchar2,
15 p_s_pos_id IN OUT NOCOPY number,
16 p_s_ass_num IN OUT NOCOPY varchar2,
17 p_s_org_id IN OUT NOCOPY number,
18 p_s_pg_id IN OUT NOCOPY number,
19 p_s_job_id IN OUT NOCOPY number,
20 p_s_grd_id IN OUT NOCOPY number,
21 p_s_pay_id IN OUT NOCOPY number,
22 p_s_def_code_comb_id IN OUT NOCOPY number,
23 p_s_soft_code_kf_id IN OUT NOCOPY number,
24 p_s_per_sys_st IN OUT NOCOPY varchar2,
25 p_s_ass_st_type_id IN OUT NOCOPY number,
26 p_s_prim_flag IN OUT NOCOPY varchar2,
27 p_s_sp_ceil_step_id IN OUT NOCOPY number,
28 p_s_pay_bas IN OUT NOCOPY varchar2) is
29 --
30 cursor get_assgt is
31 select ass.position_id,
32 ass.assignment_number,
33 ass.organization_id,
34 ass.people_group_id,
35 ass.job_id,
36 ass.grade_id,
37 ass.payroll_id,
38 ass.default_code_comb_id,
39 ass.soft_coding_keyflex_id,
40 nvl(amd.per_system_status, st.per_system_status),
41 ass.assignment_status_type_id,
42 ass.primary_flag,
43 ass.special_ceiling_step_id,
44 pb.pay_basis
45 from per_assignments_f ass,
46 per_assignment_status_types st,
47 per_ass_status_type_amends amd,
48 per_pay_bases pb
49 where ass.rowid = P_ROW_ID
50 and ass.assignment_status_type_id =
51 amd.assignment_status_type_id (+)
52 and ass.assignment_status_type_id =
53 -- amd.assignment_status_type_id (+) bug 5378516
54 st.assignment_status_type_id (+)
55 and ass.business_group_id + 0 =
56 amd.business_group_id (+) + 0
57 and ass.pay_basis_id = pb.pay_basis_id (+);
58 --
59 l_proc varchar2(15) := 'get_save_fields';
60 --
61 begin
62 g_debug := hr_utility.debug_enabled; -- get debug status
63 IF g_debug THEN
64 hr_utility.set_location('Entering: '|| g_package || l_proc, 5);
65 END IF;
66
67 open get_assgt;
68 fetch get_assgt into
69 p_s_pos_id,
70 p_s_ass_num,
71 p_s_org_id,
72 p_s_pg_id,
73 p_s_job_id,
74 p_s_grd_id,
75 p_s_pay_id,
76 p_s_def_code_comb_id,
77 p_s_soft_code_kf_id,
78 p_s_per_sys_st,
79 p_s_ass_st_type_id,
80 p_s_prim_flag,
81 p_s_sp_ceil_step_id,
82 p_s_pay_bas;
83 --
84 if get_assgt%notfound then
85 close get_assgt;
86 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
87 fnd_message.set_token('PROCEDURE',
88 'PER_ASSIGNMENTS_F2_PKG.GET_SAVE_FIELDS');
89 fnd_message.set_token('STEP', '1');
90 fnd_message.raise_error;
91 end if;
92 --
93 close get_assgt;
94 --
95 IF g_debug THEN
96 hr_utility.set_location('Leaving: '|| g_package || l_proc, 10);
97 END IF;
98
99 end get_save_fields;
100 -----------------------------------------------------------------------------
101 --
102 -- Procedure to check Personnel-type attributes of assignment record.
103 -- This is called pre-insert and pre-update into per_assignments_f.
104 --
105 procedure iu_non_payroll_checks (
106 p_per_id number,
107 p_sess_date date,
108 p_per_sys_st varchar2,
109 p_s_per_sys_st varchar2,
110 p_ass_st_type_id number,
111 p_bg_id number,
112 p_leg_code varchar2,
113 p_ass_id number,
114 p_emp_num varchar2,
115 p_ass_seq number,
116 p_ass_num varchar2) is
117 l_null number;
118 l_ass_num varchar2(30);
119 --
120 cursor curr_emp is
121 select 1
122 from per_people_f p
123 where p.person_id = P_PER_ID
124 and P_SESS_DATE between p.effective_start_date
125 and p.effective_end_date;
126 --
127 cursor asg_type is
128 select 1
129 from per_assignment_status_types a,
130 per_ass_status_type_amends b
131 where b.assignment_status_type_id (+) =
132 a.assignment_status_type_id
133 and a.assignment_status_type_id = P_ASS_ST_TYPE_ID
134 and b.business_group_id (+) + 0 = P_BG_ID
135 and nvl(a.business_group_id, P_BG_ID) = P_BG_ID
136 and nvl(a.legislation_code, P_LEG_CODE) = P_LEG_CODE
137 and nvl(b.active_flag, a.active_flag) = 'Y'
138 and nvl(b.per_system_status, a.per_system_status) =
139 P_PER_SYS_ST;
140 begin
141 --
142 -- CHECK FOR CURRENT EMPLOYEE:
143 -- Check database for current_employee flag because another user
144 -- may have changed the flag since querying the person
145 --
146 hr_utility.set_location('per_assignments_f2_pkg.iu_non_payroll_checks',1);
147 open curr_emp;
148 fetch curr_emp into l_null;
149 if curr_emp%notfound then
150 close curr_emp;
151 fnd_message.set_name('PAY', 'HR_6254_EMP_ASS_EMP_ENDED');
152 fnd_message.raise_error;
153 end if;
154 close curr_emp;
155 --
156 -- CHECK VALID STATUS:
157 -- Check that the Status is still active.
158 --
159 hr_utility.set_location('per_assignments_f2_pkg.iu_non_payroll_checks',2);
160 hr_utility.trace('p_per_sys_st is '||p_per_sys_st);
161 hr_utility.trace('p_s_per_sys_st is '||p_s_per_sys_st);
162 hr_utility.trace('P_ASS_ST_TYPE_ID is '|| to_char(P_ASS_ST_TYPE_ID));
163 if (p_per_sys_st <> p_s_per_sys_st) or (p_s_per_sys_st is null) then
164 open asg_type;
165 fetch asg_type into l_null;
166 if asg_type%notfound then
167 close asg_type;
168 fnd_message.set_name('PAY',
169 'HR_6073_APP_ASS_INVALID_STATUS');
170 fnd_message.raise_error;
171 end if;
172 close asg_type;
173 end if;
174 --
175 -- Check new assignment number - does not write back to parameter but
176 -- a local parameter must be used.
177 --
178 hr_utility.set_location('per_assignments_f2_pkg.iu_non_payroll_checks',3);
179 if p_ass_num is not null then
180 l_ass_num := p_ass_num;
181 hr_assignment.gen_new_ass_number(
182 p_ass_id,
183 p_bg_id,
184 p_emp_num,
185 p_ass_seq,
186 l_ass_num);
187 end if;
188 --
189 -- Note that a new ass_num is NOT generated here: this needs to be done
190 -- only at pre-insert when a new ass_seq is generated.
191 --
192 end iu_non_payroll_checks;
193 -----------------------------------------------------------------------------
194 --
195 -- Initiates assignment, used to initialize PERWSEMA.
196 --
197 procedure initiate_assignment(
198 p_bus_grp_id number,
199 p_person_id number,
200 p_end_of_time date,
201 p_gl_set_of_books_id IN OUT NOCOPY number,
202 p_leg_code varchar2,
203 p_sess_date date,
204 p_period_of_service_id IN OUT NOCOPY number,
205 p_accounting_flexfield_ok_flag IN OUT NOCOPY varchar2,
206 p_no_scl IN OUT NOCOPY varchar2,
207 p_scl_id_flex_num IN OUT NOCOPY number,
208 p_def_user_st IN OUT NOCOPY varchar2,
209 p_def_st_id IN OUT NOCOPY number,
210 p_yes_meaning IN OUT NOCOPY varchar2,
211 p_no_meaning IN OUT NOCOPY varchar2,
212 p_pg_struct IN OUT NOCOPY varchar2,
213 p_valid_pos_flag IN OUT NOCOPY varchar2,
214 p_valid_job_flag IN OUT NOCOPY varchar2,
215 p_gl_flex_structure IN OUT NOCOPY number,
216 p_set_of_books_name IN OUT NOCOPY varchar2,
217 p_fsp_table_name IN OUT NOCOPY varchar2,
218 p_payroll_installed IN OUT NOCOPY varchar2,
219 p_scl_title IN OUT NOCOPY varchar2,
220 p_terms_required OUT NOCOPY varchar2,
221 p_person_id2 IN number,
222 p_assignment_type IN varchar2 ) is
223 --
224 l_dummy number;
225 l_dummy_dt date;
226 l_sqlap_installed varchar2(1);
227 l_industry varchar2(1);
228 l_sql_text varchar2(2000);
229 l_sql_cursor number;
230 l_rows_fetched number;
231 l_gl_set_of_books_id number;
232 l_gl_set_of_books_id_temp number;
233 --
234 cursor pg_struct is
235 select
236 l1.meaning,
237 l2.meaning,
238 bg.people_group_structure
239 from
240 hr_lookups l1, hr_lookups l2,
241 per_business_groups bg
242 where l1.lookup_type = 'YES_NO'
243 and l2.lookup_type = 'YES_NO'
244 and l1.lookup_code = 'Y'
245 and l2.lookup_code = 'N'
246 and bg.business_group_id + 0 = P_BUS_GRP_ID;
247 --
248 -- 923011: Ensure user status is in the appropriate language
249 cursor def_assgt_status is
250 select nvl(btl.user_status,atl.user_status),
251 a.assignment_status_type_id
252 from per_assignment_status_types_tl atl,
253 per_assignment_status_types a,
254 per_ass_status_type_amends_tl btl,
255 per_ass_status_type_amends b
256 where atl.assignment_status_type_id = a.assignment_status_type_id
257 and atl.language = userenv('LANG')
258 and btl.ass_status_type_amend_id (+) = b.ass_status_type_amend_id
259 and btl.language (+) = userenv('LANG')
260 and b.assignment_status_type_id (+) = a.assignment_status_type_id
261 and b.business_group_id (+) + 0 = P_BUS_GRP_ID
262 and nvl(a.business_group_id, P_BUS_GRP_ID) = P_BUS_GRP_ID
263 and nvl(a.legislation_code, P_LEG_CODE) = P_LEG_CODE
264 and nvl(b.active_flag, a.active_flag) = 'Y'
265 and nvl(b.default_flag, a.default_flag) = 'Y'
266 and nvl(b.per_system_status, a.per_system_status)
267 = 'ACTIVE_ASSIGN';
268 --
269 -- Bug fix 3648612.
270 -- Cursor modified to improve performance.
271
272 cursor valid_pos_grades is
273 select 1
274 from per_valid_grades vg
275 where vg.business_group_id = P_BUS_GRP_ID
276 and vg.position_id is not null;
277 --
278 -- Bug fix 3648612.
279 -- Cursor modified to improve performance.
280
281 cursor valid_job_grades is
282 select 1
283 from per_valid_grades vg
284 where vg.business_group_id = P_BUS_GRP_ID
285 and vg.job_id is not null;
286 --
287 cursor get_pd_of_ser is
288 select p.date_start date_start, p.period_of_service_id
289 from per_periods_of_service p
290 where p.person_id = P_PERSON_ID
291 and P_SESS_DATE between
292 p.date_start and nvl(p.final_process_date, P_END_OF_TIME)
293 union
294 select pdp.date_start date_start, to_number(null)
295 from per_periods_of_placement pdp
296 where pdp.person_id = P_PERSON_ID
297 and P_SESS_DATE between
298 pdp.date_start and nvl(pdp.final_process_date, p_end_of_time)
299 order by date_start desc;
300 --
301 -- Simplified the following cursor by removing the unnecessary join
302 -- to fnd_applications. RMF 06-Mar-96.
303 --
304 cursor is_ap_installed is
305 select 'Y'
306 from fnd_product_installations
307 where application_id = 200
308 and status = 'I';
309 --
310 -- #345809 Added a cursor to get the set_of_books_id
311 -- from the financials_system_parameters table, rather than from a
312 -- client-side profile. See bug 243960 for more details.
313 --
314 -- Bug 874343 Query set_of_books id from financial_system_params_all
315 --
316 cursor sob_id is
317 select set_of_books_id
318 from financials_system_parameters
319 where business_group_id = p_bus_grp_id;
320 --
321 cursor get_gl_info is
322 select chart_of_accounts_id, name
323 from gl_sets_of_books
324 where set_of_books_id = p_gl_set_of_books_id;
325 --
326 -- #345809 Now find out which of the financials_system_parameters tables
327 -- is available, if any. Options are:
328 --
329 -- FINANCIALS_SYSTEM_PARAMS_ALL (10.6 install)
330 -- FINANCIALS_SYSTEM_PARAMETERS (10.5 HR + other apps install)
331 -- none (10.5 HR only install)
332 --
333 -- The ORDER BY clause ensures we pick up FINANCIALS_SYSTEM_PARAMS_ALL
334 -- if it's there, ahead of FINANCIALS_SYSTEM_PARAMETERS.
335 --
336 -- Bug 874343 - No longer need to query for financial system params tables.
337 --
338 --cursor fsp_table_name is
339 -- select table_name
340 -- from user_catalog
341 -- where table_name in ('FINANCIALS_SYSTEM_PARAMS_ALL',
342 -- 'FINANCIALS_SYSTEM_PARAMETERS')
343 -- order by table_name desc;
344 --
345 cursor scl is
346 select rule_mode
347 from pay_legislation_rules
348 where legislation_code = P_LEG_CODE
349 and rule_type = 'S'
350 and exists
351 (select null
352 from fnd_segment_attribute_values
353 where id_flex_num = rule_mode
354 and application_id = 800
355 and id_flex_code = 'SCL'
356 and segment_attribute_type = 'ASSIGNMENT'
357 and attribute_value = 'Y')
358 and exists
359 (select null
360 from pay_legislation_rules
361 where legislation_code = P_LEG_CODE
362 and rule_type = 'SDL'
363 and rule_mode = 'A') ;
364
365 cursor scl_cwk is
366 select rule_mode
367 from pay_legislation_rules
368 where legislation_code = P_LEG_CODE
369 and rule_type = 'CWK_S'
370 and exists
371 (select null
372 from fnd_segment_attribute_values
373 where id_flex_num = rule_mode
374 and application_id = 800
375 and id_flex_code = 'SCL'
376 and segment_attribute_type = 'ASSIGNMENT'
377 and attribute_value = 'Y')
378 and exists
379 (select null
380 from pay_legislation_rules
381 where legislation_code = P_LEG_CODE
382 and rule_type = 'CWK_SDL'
383 and rule_mode = 'A');
384
385 cursor scl_title is
386 select id_flex_structure_name
387 from fnd_id_flex_structures_vl
388 where id_flex_num = P_SCL_ID_FLEX_NUM
389 and application_id = 800
390 and id_flex_code = 'SCL';
391 --
392 cursor terms is
393 select rule_mode
394 from pay_legislative_field_info
395 where legislation_code = P_LEG_CODE
396 and rule_type = 'TERMS'
397 and rule_mode = 'Y';
398 --
399 begin
400 hr_utility.set_location('per_assignments_f2_pkg.initiate_assignment',10);
401 --
402 -- Get Yes/No, people group structure.
403 --
404 open pg_struct;
405 fetch pg_struct into
406 P_YES_MEANING,
407 P_NO_MEANING,
408 P_PG_STRUCT;
409 close pg_struct;
410 --
411 -- Now get default ACTIVE_ASSIGN user status.
412 --
413 hr_utility.set_location('per_assignments_f2_pkg.initiate_assignment',20);
414 open def_assgt_status;
415 fetch def_assgt_status into
416 P_DEF_USER_ST,
417 P_DEF_ST_ID;
418 if def_assgt_status%notfound then
419 close def_assgt_status;
420 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
421 fnd_message.set_token('PROCEDURE','PER_ASSIGNMENTS_F2_PKG.INITIATE_ASSIGNMENT');
422 fnd_message.set_token('STEP', '1');
423 fnd_message.raise_error;
424 end if;
425 close def_assgt_status;
426 --
427 hr_utility.set_location('per_assignments_f2_pkg.initiate_assignment',30);
428 open valid_pos_grades;
429 fetch valid_pos_grades into l_dummy;
430 if valid_pos_grades%found then
431 P_VALID_POS_FLAG := 'Y';
432 end if;
433 close valid_pos_grades;
434 --
435 hr_utility.set_location('per_assignments_f2_pkg.initiate_assignment',40);
436 open valid_job_grades;
437 fetch valid_job_grades into l_dummy;
438 if valid_job_grades%found then
439 P_VALID_JOB_FLAG := 'Y';
440 end if;
441 close valid_job_grades;
442 --
443 -- Get Person related info. if person id specified.
444 --
445 hr_utility.set_location('per_assignments_f2_pkg.initiate_assignment',50);
446 if ( p_person_id is not null ) then
447 open get_pd_of_ser;
448 fetch get_pd_of_ser into l_dummy_dt, P_PERIOD_OF_SERVICE_ID;
449 if get_pd_of_ser%notfound then
450 close get_pd_of_ser;
451 fnd_message.set_name('PAY', 'HR_6346_EMP_ASS_NO_POS');
452 fnd_message.raise_error;
453 end if;
454 close get_pd_of_ser;
455 end if;
456 --
457 -- Get Set Of Books info if AP is installed. This is required for
458 -- the GL flex.
459 --
460 hr_utility.set_location('per_assignments_f2_pkg.initiate_assignment',55);
461 open is_ap_installed;
462 fetch is_ap_installed into l_sqlap_installed;
463 close is_ap_installed;
464 hr_utility.set_location('per_assignments_f2_pkg.initiate_assignment',60);
465 --
466 if l_sqlap_installed = 'Y' then
467 hr_utility.set_location('per_assignments_f2_pkg.initiate_assignment',70);
468 --
469 -- Clear p_gl_set_of_books_id and p_fsp_table_name before we
470 -- start, though they should be null anyway.
471 --
472 p_gl_set_of_books_id := NULL;
473 -- Bug 874343 01/12/01
474 -- Can simply query FINANCIAL_SYSTEM_PARAMS_ALL
475 --
476 p_fsp_table_name := 'FINANCIALS_SYSTEM_PARAMS_ALL';
477 --
478 open sob_id;
479 fetch sob_id into l_gl_set_of_books_id;
480 if sob_id%FOUND then
481 hr_utility.set_location('per_assignments_f2_pkg.initiate_assignment',80);
482 --
483 -- Bug 3270409
484 --
485 -- Even if there is exactly one set_of_books_id for the business group
486 -- that should not be defaulted for the assignment
487 --
488 -- The user must choose the set_of_books explicitly whether the business
489 -- group has exactly one set of books or more than that.
490 --
491 P_ACCOUNTING_FLEXFIELD_OK_FLAG := 'Y';
492 P_GL_SET_OF_BOOKS_ID := NULL;
493 else
494 hr_utility.set_location('per_assignments_f2_pkg.initiate_assignment',110);
495 --
496 -- There's no set of books for the business group.
497 -- Set the flex flag to N.
498 --
499 P_ACCOUNTING_FLEXFIELD_OK_FLAG := 'N';
500 end if;
501 close sob_id;
502 else
503 --
504 -- AP is not installed.
505 --
506 P_ACCOUNTING_FLEXFIELD_OK_FLAG := 'N';
507 hr_utility.set_location('per_assignments_f2_pkg.initiate_assignment',120);
508 end if;
509
510 --
511 -- Get the soft coded keyflex setup info.
512 -- Here we use p_person_id2 because p_person_id is intentionally
513 -- nulled under certain conditions. p_person_id2 is never null.
514 --
515 -- #3609019 Added the clause of p_assignment_type
516 --
517 IF ( hr_person_type_usage_info.is_person_of_type
518 (p_person_id => p_person_id2
519 ,p_effective_date => p_sess_date
520 ,p_system_person_type => 'EMP')
521 and (nvl(p_assignment_type,'E') = 'E')) THEN
522 hr_utility.set_location('per_assignments_f2_pkg.initiate_assignment',130);
523 open scl;
524 fetch scl into P_SCL_ID_FLEX_NUM;
525 if scl%notfound then
526 P_NO_SCL := 'Y';
527 end if;
528 close scl;
529 -- Start of fix 2885212
530 ELSIF ( hr_person_type_usage_info.is_person_of_type
531 (p_person_id => p_person_id2
532 ,p_effective_date => p_sess_date
533 ,p_system_person_type => 'EX_EMP')
534 and (nvl(p_assignment_type,'E') = 'E')) THEN
535 hr_utility.set_location('per_assignments_f2_pkg.initiate_assignment',135);
536 open scl;
537 fetch scl into P_SCL_ID_FLEX_NUM;
538 if scl%notfound then
539 P_NO_SCL := 'Y';
540 end if;
541 close scl;
542 -- End of 2885212
543 ELSIF ( hr_person_type_usage_info.is_person_of_type
544 (p_person_id => p_person_id2
545 ,p_effective_date => p_sess_date
546 ,p_system_person_type => 'CWK')
547 and (nvl(p_assignment_type,'C') = 'C'))THEN
548 hr_utility.set_location('per_assignments_f2_pkg.initiate_assignment',140);
549 open scl_cwk;
550 fetch scl_cwk into P_SCL_ID_FLEX_NUM;
551 if scl_cwk%notfound then
552 P_NO_SCL := 'Y';
553 end if;
554 close scl_cwk;
555 ELSE
556 hr_utility.set_location('per_assignments_f2_pkg.initiate_assignment',150);
557 P_NO_SCL := 'Y';
558 END IF;
559
560 if not P_NO_SCL = 'Y' then
561 open scl_title;
562 fetch scl_title into P_SCL_TITLE;
563 close scl_title;
564 end if;
565
566 --
567 -- Establish whether Payroll has been installed
568 --
569 if fnd_installation.get(appl_id => 801
570 ,dep_appl_id => 801
571 ,status => p_payroll_installed
572 ,industry => l_industry)then
573 null;
574 end if;
575 --
576 -- Establish whether contracts and collective agreements canvas
577 -- should be visible to non-french customers.
578 --
579 P_TERMS_REQUIRED := 'Y';
580 --
581 if P_LEG_CODE <> 'FR' then
582 open terms;
583 fetch terms into p_terms_required;
584 if terms%notfound then
585 p_terms_required := 'N';
586 end if;
587 close terms;
588 end if;
589 --
590 hr_utility.set_location('Leaving...:per_assignments_f2_pkg.initiate_assignment',200);
591 --
592 end initiate_assignment;
593 -----------------------------------------------------------------------------
594 procedure real_del_checks(
595 p_pd_os_id number,
596 p_ass_id number,
597 p_per_id number,
598 p_del_mode varchar2,
599 p_sess_date date,
600 p_per_sys_st varchar2,
601 p_val_st_date date,
602 p_new_end_date date,
603 p_val_end_date date,
604 p_pay_id number,
605 p_eff_st_date date,
606 p_eff_end_date date,
607 p_grd_id number,
608 p_sp_ceil_st_id number,
609 p_ceil_seq number,
610 p_pay_basis_id number ) is --fix for bug 4764140
611 l_null number;
612 l_new_end_date date;
613 l_warning varchar2(80);
614 l_re_entry_point number;
615 --
616 -- Private proc used pre-delete and key-delrec.
617 --
618 -- Perform the real or total delete checks.
619 -- check that the current assignment is not the first assignment in
620 -- the employees current period of service;
621 -- check that assignments will be continuous throughout the employees
622 -- period of service;
623 -- perform the referential integrity checks.
624 --
625 cursor pd_os is
626 select 1
627 from per_periods_of_service pos
628 where pos.period_of_service_id = P_PD_OS_ID
629 and exists (
630 select null
631 from per_all_assignments_f a
632 where a.assignment_id = P_ASS_ID
633 and a.effective_start_date = pos.date_start);
634 --
635 cursor chk_ass is
636 select 1
637 from per_all_assignments_f a
638 where a.assignment_id <> P_ASS_ID
639 and a.person_id = P_PER_ID
640 and a.assignment_type = 'E'
641 and exists (
642 select null
643 from per_periods_of_service pos
644 where pos.period_of_service_id = P_PD_OS_ID
645 and pos.date_start = a.effective_start_date);
646 --
647 l_proc varchar2(15) := 'real_del_checks';
648 --
649 begin
650 g_debug := hr_utility.debug_enabled; -- get debug status
651 IF g_debug THEN
652 hr_utility.set_location('Entering: '|| g_package || l_proc, 5);
653 END IF;
654 --
655 -- Do not allow the user to delete this assignment if this is the only
656 -- assignment with a start date that is the same as the period of
657 -- service start date.
658 --
659 open pd_os;
660 fetch pd_os into l_null;
661 if pd_os%found then
662 open chk_ass;
663 fetch chk_ass into l_null;
664 if chk_ass%notfound then
665 close chk_ass;
666 close pd_os;
667 fnd_message.set_name('PAY',
668 'HR_6435_EMP_ASS_DEL_FIRST');
669 fnd_message.raise_error;
670 end if;
671 close chk_ass;
672 end if;
673 close pd_os;
674 --
675 -- Perform the referential integrity checks.
676 --
677 IF g_debug THEN
678 hr_utility.set_location( g_package || l_proc, 10);
679 END IF;
680 hr_assignment.del_ref_int_check(
681 p_ass_id,
682 'ZAP',
683 p_val_st_date);
684 --
685 -- PAYROLL_CHANGE_VALIDATE:
686 --
687 -- null = p_upd_mode as we want proc to look at p_del_mode.
688 --
689 l_re_entry_point := 2;
690 l_new_end_date := p_new_end_date;
691 --
692 IF g_debug THEN
693 hr_utility.set_location( g_package || l_proc, 20);
694 END IF;
695 per_assignments_f3_pkg.update_and_delete_bundle(
696 null,
697 p_val_st_date,
698 p_eff_st_date,
699 p_eff_end_date,
700 p_pd_os_id,
701 p_per_sys_st,
702 p_ass_id,
703 p_val_end_date,
704 null,
705 p_del_mode,
706 p_sess_date,
707 p_pay_id,
708 p_grd_id,
709 p_sp_ceil_st_id,
710 p_ceil_seq,
711 l_new_end_date,
712 l_warning,
713 l_re_entry_point,
714 'Y',
715 p_pay_basis_id);--fix for bug 4764140
716 --
717 -- Value of l_new_end_date will not be changed by
718 -- update_and_delete_bundle because we are only executing entry
719 -- point 2 of the code, not the bit which sets the
720 -- l_new_end_date value.
721 --
722 -- No end warning is returned from this check.
723 --
724 IF g_debug THEN
725 hr_utility.set_location( 'Leaving ' || g_package || l_proc, 30);
726 END IF;
727 end real_del_checks;
728 -----------------------------------------------------------------------------
729 --
730 -- *** Bundled procedures from update/delete to save on network usage
731 -- for PERWSEMA. ***
732 --
733 -----------------------------------------------------------------------------
734 --
735 -- Bundled explicit selecta from pre_update to improve performance.
736 -- Returns p_return_warning as 'Y' if assgt has been ended in future so a
737 -- warning can be issued on client.
738 -- Note that first the get_save_fields is run.
739 --
740 procedure pre_update_bundle (
741 p_pos_id number,
742 p_org_id number,
743 p_ass_id number,
744 p_row_id varchar2,
745 p_eff_st_date date,
746 p_upd_mode varchar2,
747 p_per_sys_st varchar2,
748 p_s_pos_id IN OUT NOCOPY number,
749 p_s_ass_num IN OUT NOCOPY varchar2,
750 p_s_org_id IN OUT NOCOPY number,
751 p_s_pg_id IN OUT NOCOPY number,
752 p_s_job_id IN OUT NOCOPY number,
753 p_s_grd_id IN OUT NOCOPY number,
754 p_s_pay_id IN OUT NOCOPY number,
755 p_s_def_code_comb_id IN OUT NOCOPY number,
756 p_s_soft_code_kf_id IN OUT NOCOPY number,
757 p_s_per_sys_st IN OUT NOCOPY varchar2,
758 p_s_ass_st_type_id IN OUT NOCOPY number,
759 p_s_prim_flag IN OUT NOCOPY varchar2,
760 p_s_sp_ceil_step_id IN OUT NOCOPY number,
761 p_s_pay_bas IN OUT NOCOPY varchar2,
762 p_return_warning IN OUT NOCOPY varchar2,
763 p_sess_date date default null) is
764 --
765 l_dummy number;
766 l_eot date := to_date('31124712', 'DDMMYYYY');
767 --
768 -- Changed 01-Oct-99 SCNair (per_all_positions to hr_all_positions) date track requirement
769 --
770 cursor consistent_org is
771 select 1
772 from hr_all_positions p
773 where p.position_id = P_POS_ID
774 and p.organization_id = P_ORG_ID;
775 --
776 cursor first_assgt is
777 select 1
778 from per_assignments_f a
779 where a.assignment_id = P_ASS_ID
780 and a.rowid <> P_ROW_ID
781 and a.effective_start_date < P_EFF_ST_DATE;
782 --
783 cursor ended_assgt is
784 select 1
785 from sys.dual
786 where L_EOT > (select max(effective_end_date)
787 from per_assignments_f
788 where assignment_id = P_ASS_ID);
789 --
790 -- Payroll Object Group functionality. Call to pay_pog_all_assignments_pkg
791 -- requires old assignment values, so using cursor to populate record with
792 -- the required values.
793 --
794 -- old_asg_rec per_asg_shd.g_rec_type;
795 --
796 cursor asg_details(p_asg_id number
797 ,p_eff_date date)
798 is
799 select assignment_id
800 ,effective_start_date
801 ,effective_end_date
802 ,business_group_id
803 ,recruiter_id
804 ,grade_id
805 ,position_id
806 ,job_id
807 ,assignment_status_type_id
808 ,payroll_id
809 ,location_id
810 ,person_referred_by_id
811 ,supervisor_id
812 ,special_ceiling_step_id
813 ,person_id
814 ,recruitment_activity_id
815 ,source_organization_id
816 ,organization_id
817 ,people_group_id
818 ,soft_coding_keyflex_id
819 ,vacancy_id
820 ,pay_basis_id
821 ,assignment_sequence
822 ,assignment_type
823 ,primary_flag
824 ,application_id
825 ,assignment_number
826 ,change_reason
827 ,comment_id
828 ,null
829 ,date_probation_end
830 ,default_code_comb_id
831 ,employment_category
832 ,frequency
833 ,internal_address_line
834 ,manager_flag
835 ,normal_hours
836 ,perf_review_period
837 ,perf_review_period_frequency
838 ,period_of_service_id
839 ,probation_period
840 ,probation_unit
841 ,sal_review_period
842 ,sal_review_period_frequency
843 ,set_of_books_id
844 ,source_type
845 ,time_normal_finish
846 ,time_normal_start
847 ,bargaining_unit_code
848 ,labour_union_member_flag
849 ,hourly_salaried_code
850 ,request_id
851 ,program_application_id
852 ,program_id
853 ,program_update_date
854 ,ass_attribute_category
855 ,ass_attribute1
856 ,ass_attribute2
857 ,ass_attribute3
858 ,ass_attribute4
859 ,ass_attribute5
860 ,ass_attribute6
861 ,ass_attribute7
862 ,ass_attribute8
863 ,ass_attribute9
864 ,ass_attribute10
865 ,ass_attribute11
866 ,ass_attribute12
867 ,ass_attribute13
868 ,ass_attribute14
869 ,ass_attribute15
870 ,ass_attribute16
871 ,ass_attribute17
872 ,ass_attribute18
873 ,ass_attribute19
874 ,ass_attribute20
875 ,ass_attribute21
876 ,ass_attribute22
877 ,ass_attribute23
878 ,ass_attribute24
879 ,ass_attribute25
880 ,ass_attribute26
881 ,ass_attribute27
882 ,ass_attribute28
883 ,ass_attribute29
884 ,ass_attribute30
885 ,title
886 ,object_version_number
887 ,contract_id
888 ,establishment_id
889 ,collective_agreement_id
890 ,cagr_grade_def_id
891 ,cagr_id_flex_num
892 ,notice_period
893 ,notice_period_uom
894 ,employee_category
895 ,work_at_home
896 ,job_post_source_name
897 ,posting_content_id
898 ,period_of_placement_date_start
899 ,vendor_id
900 ,vendor_employee_number
901 ,vendor_assignment_number
902 ,assignment_category
903 ,project_title
904 ,applicant_rank
905 ,grade_ladder_pgm_id
906 ,supervisor_assignment_id
907 ,vendor_site_id
908 ,po_header_id
909 ,po_line_id
910 ,projected_assignment_end
911 from per_all_assignments_f
912 where assignment_id = p_asg_id
913 and p_eff_date between effective_start_date
914 and effective_end_date;
915 --
916 --
917 l_proc varchar2(17) := 'pre_update_bundle';
918 --
919 begin
920 g_debug := hr_utility.debug_enabled; -- get debug status
921 IF g_debug THEN
922 hr_utility.set_location('Entering: '|| g_package || l_proc, 5);
923 END IF;
924 get_save_fields(
925 p_row_id,
926 p_s_pos_id,
927 p_s_ass_num,
928 p_s_org_id,
929 p_s_pg_id,
930 p_s_job_id,
931 p_s_grd_id,
932 p_s_pay_id,
933 p_s_def_code_comb_id,
934 p_s_soft_code_kf_id,
935 p_s_per_sys_st,
936 p_s_ass_st_type_id,
937 p_s_prim_flag,
938 p_s_sp_ceil_step_id,
939 p_s_pay_bas);
940 --
941 -- Check that "The position is now inconsistent with the new
942 -- organization" is not true.
943 --
944 IF g_debug THEN
945 hr_utility.set_location( g_package || l_proc, 10);
946 END IF;
947 if (p_org_id <> p_s_org_id) and (p_pos_id is not null) then
948 open consistent_org;
949 fetch consistent_org into l_dummy;
950 if consistent_org%notfound then
951 fnd_message.set_name('PAY',
952 'HR_6102_EMP_ASS_ORGANIZATION');
953 close consistent_org;
954 fnd_message.raise_error;
955 end if;
956 close consistent_org;
957 end if;
958 --
959 -- Check if the assignment row from Per_assignments_f is the first
960 -- for this assignment id. If it is then the status must be
961 -- ACTIVE_ASSIGN.
962 --
963 IF g_debug THEN
964 hr_utility.set_location( g_package || l_proc, 20);
965 END IF;
966 if p_upd_mode = 'CORRECTION' and p_per_sys_st not in ('ACTIVE_ASSIGN'
967 ,'ACTIVE_CWK') then
968 open first_assgt;
969 fetch first_assgt into l_dummy;
970 if first_assgt%notfound then
971 fnd_message.set_name('PAY',
972 'HR_7139_EMP_ASS_FIRST_EMP_ASS');
973 close first_assgt;
974 fnd_message.raise_error;
975 end if;
976 close first_assgt;
977 end if;
978 --
979 IF g_debug THEN
980 hr_utility.set_location( g_package || l_proc, 30);
981 END IF;
982 if p_upd_mode <> 'UPDATE_OVERRIDE' then
983 --
984 -- Check for "This assignment has been ended in the future...
985 -- Continue?".
986 --
987 open ended_assgt;
988 fetch ended_assgt into l_dummy;
989 if ended_assgt%found then
990 p_return_warning := 'Y';
991 end if;
992 close ended_assgt;
993 end if;
994 --
995 --
996 -- populate POG record with asg values
997 --
998 IF g_debug THEN
999 hr_utility.set_location( g_package || l_proc, 40);
1000 END IF;
1001 OPEN asg_details(p_ass_id, p_sess_date);
1002 FETCH asg_details into g_old_asg_rec;
1003 IF asg_details%NOTFOUND THEN
1004 CLOSE asg_details;
1005 hr_utility.trace('no rows in asg_details');
1006 ELSE
1007 CLOSE asg_details;
1008 END IF;
1009 --
1010 IF g_debug THEN
1011 hr_utility.set_location( 'Leaving ' || g_package || l_proc, 50);
1012 END IF;
1013 end pre_update_bundle;
1014 -----------------------------------------------------------------------------
1015 procedure key_delrec(
1016 p_del_mode varchar2,
1017 p_val_st_date date,
1018 p_eff_st_date date,
1019 p_eff_end_date date,
1020 p_pd_os_id number,
1021 p_per_sys_st varchar2,
1022 p_ass_id number,
1023 p_grd_id number,
1024 p_sp_ceil_st_id number,
1025 p_ceil_seq number,
1026 p_per_id number,
1027 p_sess_date date,
1028 p_new_end_date IN OUT NOCOPY date,
1029 p_val_end_date date,
1030 p_pay_id number,
1031 p_pay_basis_id number --fix for bug 4764140
1032 )is
1033 l_new_end_date date;
1034 l_returned_warning varchar2(80);
1035 l_re_entry_point number;
1036 --
1037 l_proc varchar2(10) := 'key_delrec';
1038 --
1039 begin
1040 g_debug := hr_utility.debug_enabled; -- get debug status
1041 IF g_debug THEN
1042 hr_utility.set_location('Entering: '|| g_package || l_proc, 5);
1043 END IF;
1044
1045 --
1046 -- These checks are also included in the FND_PRE_DELETE trigger to
1047 -- ensure the checks are also done at commit time.
1048 --
1049 -- Note that l_new_end_date is ignored, even if it were to be changed
1050 -- as if the validation fails, we do not want this value to be
1051 -- changed.
1052 --
1053 if p_del_mode in ('FUTURE_CHANGE', 'DELETE_NEXT_CHANGE') then
1054 --
1055 -- CHECK_TERM_BY_POS, upd_mode = null
1056 --
1057 l_re_entry_point := 999;
1058 per_assignments_f3_pkg.update_and_delete_bundle(
1059 p_del_mode,
1060 p_val_st_date,
1061 p_eff_st_date,
1062 p_eff_end_date,
1063 p_pd_os_id,
1064 p_per_sys_st,
1065 p_ass_id,
1066 p_val_end_date,
1067 null,
1068 p_del_mode,
1069 p_sess_date,
1070 p_pay_id,
1071 p_grd_id,
1072 p_sp_ceil_st_id,
1073 p_ceil_seq,
1074 l_new_end_date,
1075 l_returned_warning,
1076 l_re_entry_point,
1077 'Y',
1078 p_pay_basis_id);--fix for bug 4764140
1079 --
1080 -- CHECK_SPP_AND_CEIL, upd_mode = null
1081 --
1082 l_re_entry_point := 3;
1083 per_assignments_f3_pkg.update_and_delete_bundle(
1084 p_del_mode,
1085 p_val_st_date,
1086 p_eff_st_date,
1087 p_eff_end_date,
1088 p_pd_os_id,
1089 p_per_sys_st,
1090 p_ass_id,
1091 p_val_end_date,
1092 null,
1093 p_del_mode,
1094 p_sess_date,
1095 p_pay_id,
1096 p_grd_id,
1097 p_sp_ceil_st_id,
1098 p_ceil_seq,
1099 l_new_end_date,
1100 l_returned_warning,
1101 l_re_entry_point,
1102 'Y',
1103 p_pay_basis_id);--fix for bug 4764140
1104 --
1105 elsif p_del_mode = 'ZAP' then
1106 IF g_debug THEN
1107 hr_utility.set_location( g_package || l_proc, 10);
1108 END IF;
1109 real_del_checks(
1110 p_pd_os_id,
1111 p_ass_id,
1112 p_per_id,
1113 p_del_mode,
1114 p_sess_date,
1115 p_per_sys_st,
1116 p_val_st_date,
1117 p_new_end_date,
1118 p_val_end_date,
1119 p_pay_id,
1120 p_eff_st_date,
1121 p_eff_end_date,
1122 p_grd_id,
1123 p_sp_ceil_st_id,
1124 p_ceil_seq,
1125 p_pay_basis_id);--fix for bug 4764140
1126 end if;
1127 --
1128 IF g_debug THEN
1129 hr_utility.set_location( 'Leaving ' || g_package || l_proc, 10);
1130 END IF;
1131 end key_delrec;
1132 -----------------------------------------------------------------------------
1133 procedure pre_delete(
1134 p_del_mode varchar2,
1135 p_val_st_date date,
1136 p_eff_st_date date,
1137 p_eff_end_date date,
1138 p_pd_os_id number,
1139 p_per_sys_st varchar2,
1140 p_ass_id number,
1141 p_sess_date date,
1142 p_new_end_date IN OUT NOCOPY date,
1143 p_val_end_date date,
1144 p_pay_id number,
1145 p_grd_id number,
1146 p_sp_ceil_st_id number,
1147 p_ceil_seq number,
1148 p_per_id number,
1149 p_prim_flag varchar2,
1150 p_prim_change_flag IN OUT NOCOPY varchar2,
1151 p_new_prim_flag IN OUT NOCOPY varchar2,
1152 p_re_entry_point IN OUT NOCOPY number,
1153 p_returned_warning IN OUT NOCOPY varchar2,
1154 p_cancel_atd IN OUT NOCOPY date,
1155 p_cancel_lspd IN OUT NOCOPY date,
1156 p_reterm_atd IN OUT NOCOPY date,
1157 p_reterm_lspd IN OUT NOCOPY date,
1158 p_prim_date_from IN OUT NOCOPY date,
1159 p_new_prim_ass_id IN OUT NOCOPY number,
1160 p_row_id varchar2,
1161 p_s_pos_id IN OUT NOCOPY number,
1162 p_s_ass_num IN OUT NOCOPY varchar2,
1163 p_s_org_id IN OUT NOCOPY number,
1164 p_s_pg_id IN OUT NOCOPY number,
1165 p_s_job_id IN OUT NOCOPY number,
1166 p_s_grd_id IN OUT NOCOPY number,
1167 p_s_pay_id IN OUT NOCOPY number,
1168 p_s_def_code_comb_id IN OUT NOCOPY number,
1169 p_s_soft_code_kf_id IN OUT NOCOPY number,
1170 p_s_per_sys_st IN OUT NOCOPY varchar2,
1171 p_s_ass_st_type_id IN OUT NOCOPY number,
1172 p_s_prim_flag IN OUT NOCOPY varchar2,
1173 p_s_sp_ceil_step_id IN OUT NOCOPY number,
1174 p_s_pay_bas IN OUT NOCOPY varchar2,
1175 p_pay_basis_id number ) is --fix for bug 4764140
1176
1177 --
1178 -- Start of Fix for Bug 2820230
1179 --
1180 -- Declare Cursor.
1181
1182 cursor csr_grade_step is
1183 select spp.placement_id, spp.object_version_number ,step_id, spp.effective_end_date
1184 from per_spinal_point_placements_f spp
1185 where spp.assignment_id = p_ass_id
1186 and p_val_st_date between spp.effective_start_date
1187 and spp.effective_end_date;
1188
1189 -- Declare Local Variables
1190 l_placement_id number;
1191 l_object_version_number number;
1192 l_step_id number ;
1193 l_spp_end_date date ;
1194 l_max_spp_date date ;
1195 l_datetrack_mode varchar2(30);
1196 l_effective_start_date date;
1197 l_effective_end_date date;
1198 --
1199 -- End of Fix for bug 2820230
1200 --
1201 l_new_end_date date;
1202 l_show_cand_prim_assgts varchar2(1);
1203 --
1204 -- Payroll Object Group functionality. Call to pay_pog_all_assignments_pkg
1205 -- requires old assignment values, so using cursor to populate record with
1206 -- the required values.
1207 --
1208 cursor asg_details(p_asg_id number
1209 ,p_eff_date date)
1210 is
1211 select assignment_id
1212 ,effective_start_date
1213 ,effective_end_date
1214 ,business_group_id
1215 ,recruiter_id
1216 ,grade_id
1217 ,position_id
1218 ,job_id
1219 ,assignment_status_type_id
1220 ,payroll_id
1221 ,location_id
1222 ,person_referred_by_id
1223 ,supervisor_id
1224 ,special_ceiling_step_id
1225 ,person_id
1226 ,recruitment_activity_id
1227 ,source_organization_id
1228 ,organization_id
1229 ,people_group_id
1230 ,soft_coding_keyflex_id
1231 ,vacancy_id
1232 ,pay_basis_id
1233 ,assignment_sequence
1234 ,assignment_type
1235 ,primary_flag
1236 ,application_id
1237 ,assignment_number
1238 ,change_reason
1239 ,comment_id
1240 ,null
1241 ,date_probation_end
1242 ,default_code_comb_id
1243 ,employment_category
1244 ,frequency
1245 ,internal_address_line
1246 ,manager_flag
1247 ,normal_hours
1248 ,perf_review_period
1249 ,perf_review_period_frequency
1250 ,period_of_service_id
1251 ,probation_period
1252 ,probation_unit
1253 ,sal_review_period
1254 ,sal_review_period_frequency
1255 ,set_of_books_id
1256 ,source_type
1257 ,time_normal_finish
1258 ,time_normal_start
1259 ,bargaining_unit_code
1260 ,labour_union_member_flag
1261 ,hourly_salaried_code
1262 ,request_id
1263 ,program_application_id
1264 ,program_id
1265 ,program_update_date
1266 ,ass_attribute_category
1267 ,ass_attribute1
1268 ,ass_attribute2
1269 ,ass_attribute3
1270 ,ass_attribute4
1271 ,ass_attribute5
1272 ,ass_attribute6
1273 ,ass_attribute7
1274 ,ass_attribute8
1275 ,ass_attribute9
1276 ,ass_attribute10
1277 ,ass_attribute11
1278 ,ass_attribute12
1279 ,ass_attribute13
1280 ,ass_attribute14
1281 ,ass_attribute15
1282 ,ass_attribute16
1283 ,ass_attribute17
1284 ,ass_attribute18
1285 ,ass_attribute19
1286 ,ass_attribute20
1287 ,ass_attribute21
1288 ,ass_attribute22
1289 ,ass_attribute23
1290 ,ass_attribute24
1291 ,ass_attribute25
1292 ,ass_attribute26
1293 ,ass_attribute27
1294 ,ass_attribute28
1295 ,ass_attribute29
1296 ,ass_attribute30
1297 ,title
1298 ,object_version_number
1299 ,contract_id
1300 ,establishment_id
1301 ,collective_agreement_id
1302 ,cagr_grade_def_id
1303 ,cagr_id_flex_num
1304 ,notice_period
1305 ,notice_period_uom
1306 ,employee_category
1307 ,work_at_home
1308 ,job_post_source_name
1309 ,posting_content_id
1310 ,period_of_placement_date_start
1311 ,vendor_id
1312 ,vendor_employee_number
1313 ,vendor_assignment_number
1314 ,assignment_category
1315 ,project_title
1316 ,applicant_rank
1317 ,grade_ladder_pgm_id
1318 ,supervisor_assignment_id
1319 ,vendor_site_id
1320 ,po_header_id
1321 ,po_line_id
1322 ,projected_assignment_end
1323 from per_all_assignments_f
1324 where assignment_id = p_asg_id
1325 and p_eff_date between effective_start_date
1326 and effective_end_date;
1327 --
1328 begin
1329 hr_utility.set_location('per_assignments_f2_pkg.pre_delete',1);
1330 --
1331 -- Once again ignore l_new_end_date, even if it changes value, as
1332 -- the change should not be made if the validation fails (and if it
1333 -- succeeds then the record will be deleted anyway).
1334 --
1335 l_new_end_date := p_new_end_date;
1336 --
1337 if p_re_entry_point = -1 then
1338 goto RE_ENTRY_POINT_MINUS_1;
1339 end if;
1340 --
1341 get_save_fields(
1342 p_row_id,
1343 p_s_pos_id,
1344 p_s_ass_num,
1345 p_s_org_id,
1346 p_s_pg_id,
1347 p_s_job_id,
1348 p_s_grd_id,
1349 p_s_pay_id,
1350 p_s_def_code_comb_id,
1351 p_s_soft_code_kf_id,
1352 p_s_per_sys_st,
1353 p_s_ass_st_type_id,
1354 p_s_prim_flag,
1355 p_s_sp_ceil_step_id,
1356 p_s_pay_bas);
1357 --
1358 if p_del_mode in ('FUTURE_CHANGE', 'DELETE_NEXT_CHANGE') then
1359 --
1360 -- Call bundle if there is a warning then simply return to
1361 -- C-S calling routine with warning and code re-entry point.
1362 -- If warning accepted then re-enter this proc with the
1363 -- later re-entry point and continue checking further down
1364 -- update_and_delete_bundle's code.
1365 --
1366 -- N.B. This is the only point in whuch the p_new_end_date is
1367 -- passed as an IN OUT parameter. The value may change
1368 -- and the new value is required here
1369 --
1370 hr_utility.set_location('per_assignments_f2_pkg.pre_delete',2);
1371
1372
1373
1374 per_assignments_f3_pkg.update_and_delete_bundle(
1375 p_del_mode,
1376 p_val_st_date,
1377 p_eff_st_date,
1378 p_eff_end_date,
1379 p_pd_os_id,
1380 p_per_sys_st,
1381 p_ass_id,
1382 p_val_end_date,
1383 null,
1384 p_del_mode,
1385 p_sess_date,
1386 p_pay_id,
1387 p_grd_id,
1388 p_sp_ceil_st_id,
1389 p_ceil_seq,
1390 p_new_end_date,
1391 p_returned_warning,
1392 p_re_entry_point,
1393 'N' ,
1394 p_pay_basis_id);--fix for bug 4764140
1395 --
1396 if p_returned_warning is not null then
1397 return;
1398 end if;
1399 --
1400 elsif p_del_mode = 'ZAP' then
1401 --
1402 hr_utility.set_location('per_assignments_f2_pkg.pre_delete',20);
1403
1404 real_del_checks(
1405 p_pd_os_id,
1406 p_ass_id,
1407 p_per_id,
1408 p_del_mode,
1409 p_sess_date,
1410 p_per_sys_st,
1411 p_val_st_date,
1412 l_new_end_date,
1413 p_val_end_date,
1414 p_pay_id,
1415 p_eff_st_date,
1416 p_eff_end_date,
1417 p_grd_id,
1418 p_sp_ceil_st_id,
1419 p_ceil_seq,
1420 p_pay_basis_id);--fix for bug 4764140
1421 end if;
1422 --
1423 hr_utility.set_location('per_assignments_f2_pkg.pre_delete',40);
1424 per_assignments_f3_pkg.check_future_primary(
1425 p_del_mode,
1426 p_val_st_date,
1427 p_prim_flag,
1428 p_eff_st_date,
1429 p_s_prim_flag,
1430 p_prim_change_flag,
1431 p_new_prim_flag,
1432 p_ass_id,
1433 p_eff_end_date,
1434 p_pd_os_id,
1435 l_show_cand_prim_assgts,
1436 p_prim_date_from,
1437 p_new_prim_ass_id);
1438 --
1439 hr_utility.trace('Check_Future_Primary Date'||
1440 to_char(p_prim_date_from,'DD-MON-YYYY'));
1441 --
1442 if l_show_cand_prim_assgts = 'Y' then
1443 --
1444 -- Need to do a show_lov on client so
1445 -- return special warning to be interpreted
1446 -- on client.
1447 --
1448 p_returned_warning := 'SHOW_LOV';
1449 p_re_entry_point := -1;
1450 return;
1451 end if;
1452 --
1453 <<RE_ENTRY_POINT_MINUS_1>>
1454 hr_utility.set_location('per_assignments_f2_pkg.pre_delete',50);
1455 --
1456 -- Now check the changing of term_assign's with relation to it's
1457 -- impact on element entries (G255).
1458 --
1459 per_assignments_f3_pkg.test_for_cancel_term(
1460 p_ass_id,
1461 p_val_st_date,
1462 p_val_end_date,
1463 p_del_mode,
1464 p_per_sys_st,
1465 p_s_per_sys_st,
1466 p_cancel_atd,
1467 p_cancel_lspd,
1468 p_reterm_atd,
1469 p_reterm_lspd);
1470 --
1471 p_re_entry_point := 0;
1472 --
1473 -- For bug 424224. p_returned_warning is set to null on exit, so
1474 -- LOV is only displayed once.
1475 --
1476 p_returned_warning := null;
1477 --
1478 hr_utility.set_location('per_assignments_f2_pkg.pre_delete',60);
1479 OPEN asg_details(p_ass_id, p_sess_date);
1480 FETCH asg_details into g_old_asg_rec;
1481 IF asg_details%NOTFOUND THEN
1482 CLOSE asg_details;
1483 hr_utility.trace('no rows in asg_details');
1484 ELSE
1485 CLOSE asg_details;
1486 END IF;
1487 --
1488 hr_utility.set_location(' leaving per_assignments_f2_pkg.pre_delete',70);
1489 end pre_delete;
1490 -----------------------------------------------------------------------------
1491 END PER_ASSIGNMENTS_F2_PKG;
[Home] [Help]