[Home] [Help]
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: --
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: -----------------------------------------------------------------------------
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
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: --
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,
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;
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
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,
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;
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;
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;
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';
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';
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
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);
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: --
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: --
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
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: --
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.
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';
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';
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';
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
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(
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,
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
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
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
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,
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,
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).
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(
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,
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,
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,
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,
1477: return;
1478: end if;
1479: --
1480: <
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: --
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,
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;
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;
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;