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