[Home] [Help]
356: IS
357: --
358: l_proc varchar2(72):=g_package||'delete_assign_atd';
359: begin
360: hr_utility.trace('Entered delete_assign_atd for assign '||p_assignment_id);
361: --
362: hr_utility.set_location(l_proc,1);
363: -- Remove booking and event where the event is for the assignment
364: --
358: l_proc varchar2(72):=g_package||'delete_assign_atd';
359: begin
360: hr_utility.trace('Entered delete_assign_atd for assign '||p_assignment_id);
361: --
362: hr_utility.set_location(l_proc,1);
363: -- Remove booking and event where the event is for the assignment
364: --
365: --
366: -- 3735333 Modified the query of per_bookings for better performance.
372: WHERE pev.assignment_id = p_assignment_id
373: AND pev.date_start > p_actual_termination_date
374: );
375: --
376: hr_utility.set_location(l_proc,2);
377: DELETE per_events pev
378: WHERE pev.assignment_id = p_assignment_id
379: AND pev.date_start > p_actual_termination_date
380: AND pev.event_or_interview = 'E';
378: WHERE pev.assignment_id = p_assignment_id
379: AND pev.date_start > p_actual_termination_date
380: AND pev.event_or_interview = 'E';
381: --
382: hr_utility.set_location(l_proc,3);
383: DELETE per_letter_request_lines lrl
384: WHERE lrl.assignment_id = p_assignment_id
385: AND lrl.date_from > p_actual_termination_date;
386: --
412: --
413: -- End of Fix for WWBUG 1408379
414: --
415: begin
416: hr_utility.trace('Entered delete_assign_fpf for assign '||p_assignment_id);
417: --
418: hr_utility.set_location(l_proc,1);
419: UPDATE per_secondary_ass_statuses sas
420: SET sas.end_date = p_final_process_date
414: --
415: begin
416: hr_utility.trace('Entered delete_assign_fpf for assign '||p_assignment_id);
417: --
418: hr_utility.set_location(l_proc,1);
419: UPDATE per_secondary_ass_statuses sas
420: SET sas.end_date = p_final_process_date
421: WHERE sas.assignment_id = p_assignment_id
422: AND sas.end_date IS NULL;
420: SET sas.end_date = p_final_process_date
421: WHERE sas.assignment_id = p_assignment_id
422: AND sas.end_date IS NULL;
423: --
424: hr_utility.set_location(l_proc,5);
425: DELETE per_secondary_ass_statuses sas
426: WHERE sas.assignment_id = p_assignment_id
427: AND sas.start_date > p_final_process_date;
428: --
425: DELETE per_secondary_ass_statuses sas
426: WHERE sas.assignment_id = p_assignment_id
427: AND sas.start_date > p_final_process_date;
428: --
429: hr_utility.set_location(l_proc,10);
430: UPDATE pay_personal_payment_methods_f ppm
431: SET ppm.effective_end_date = p_final_process_date
432: WHERE ppm.assignment_id = p_assignment_id
433: AND p_final_process_date
433: AND p_final_process_date
434: BETWEEN ppm.effective_start_date
435: AND ppm.effective_end_date;
436: --
437: hr_utility.set_location(l_proc,15);
438: DELETE pay_personal_payment_methods_f ppm
439: WHERE ppm.assignment_id = p_assignment_id
440: AND ppm.effective_start_date > p_final_process_date;
441: --
438: DELETE pay_personal_payment_methods_f ppm
439: WHERE ppm.assignment_id = p_assignment_id
440: AND ppm.effective_start_date > p_final_process_date;
441: --
442: hr_utility.set_location(l_proc,20);
443: UPDATE pay_cost_allocations_f pca
444: SET pca.effective_end_date = p_final_process_date
445: WHERE pca.assignment_id = p_assignment_id
446: AND p_final_process_date
446: AND p_final_process_date
447: BETWEEN pca.effective_start_date
448: AND pca.effective_end_date;
449: --
450: hr_utility.set_location(l_proc,25);
451: DELETE pay_cost_allocations_f pca
452: WHERE pca.assignment_id = p_assignment_id
453: AND pca.effective_start_date > p_final_process_date;
454: --
451: DELETE pay_cost_allocations_f pca
452: WHERE pca.assignment_id = p_assignment_id
453: AND pca.effective_start_date > p_final_process_date;
454: --
455: hr_utility.set_location(l_proc,30);
456: UPDATE per_spinal_point_placements_f spp
457: SET spp.effective_end_date = p_final_process_date
458: WHERE spp.assignment_id = p_assignment_id
459: AND p_final_process_date
460: BETWEEN spp.effective_start_date
461: AND spp.effective_end_date;
462: --
463: -- VT 03/06/96 bug #311763
464: hr_utility.set_location(l_proc,35);
465: DELETE per_spinal_point_placements_f spp
466: WHERE spp.assignment_id = p_assignment_id
467: AND spp.effective_start_date > p_final_process_date;
468: --
469:
470: -- Due to date tracking of assignment_budget_values.
471: -- SASmith 30-APR-1998
472:
473: hr_utility.set_location(l_proc,40);
474: DELETE per_assignment_budget_values_f abv
475: WHERE abv.assignment_id = p_assignment_id
476: AND abv.effective_start_date > p_final_process_date;
477: --
474: DELETE per_assignment_budget_values_f abv
475: WHERE abv.assignment_id = p_assignment_id
476: AND abv.effective_start_date > p_final_process_date;
477: --
478: hr_utility.set_location(l_proc,45);
479: --
480: -- Start of FIX for WWBUG 1408379
481: --
482: open c1;
526: IS
527: --
528: l_proc varchar2(72):=g_package||'delete_employee_atd';
529: begin
530: hr_utility.trace('Entered delete_employee_atd for person '||p_person_id);
531: --
532: hr_utility.set_location(l_proc,1);
533: DELETE per_absence_attendances paa
534: WHERE paa.person_id = p_person_id
528: l_proc varchar2(72):=g_package||'delete_employee_atd';
529: begin
530: hr_utility.trace('Entered delete_employee_atd for person '||p_person_id);
531: --
532: hr_utility.set_location(l_proc,1);
533: DELETE per_absence_attendances paa
534: WHERE paa.person_id = p_person_id
535: AND paa.date_start > p_actual_termination_date;
536: --
533: DELETE per_absence_attendances paa
534: WHERE paa.person_id = p_person_id
535: AND paa.date_start > p_actual_termination_date;
536: --
537: hr_utility.set_location(l_proc,5);
538: -- Remove bookings on employee events
539: DELETE per_bookings pb
540: WHERE pb.person_id = p_person_id
541: AND EXISTS (SELECT ''
544: AND pev.emp_or_apl = 'E'
545: AND pev.event_or_interview = 'E'
546: AND pb.event_id = pev.event_id);
547: --
548: hr_utility.set_location(l_proc,7);
549: -- Remove bookings as an interviewer for either employee or applicant
550: -- interviews
551: DELETE per_bookings pb
552: WHERE pb.person_id = p_person_id
561: WHERE person_id = p_person_id
562: )
563: );
564: --
565: hr_utility.set_location(l_proc,10);
566: DELETE per_letter_request_lines lrl
567: WHERE lrl.person_id = p_person_id
568: AND lrl.date_from > p_actual_termination_date;
569: --
580: --
581: l_proc varchar2(72):=g_package||'delete_de_assign';
582: --
583: begin
584: hr_utility.trace('Entered delete_de_assign for '||p_assignment_id);
585: --
586: hr_utility.set_location(l_proc,1);
587: DELETE per_assignments_f ass
588: WHERE ass.assignment_id = p_assignment_id
582: --
583: begin
584: hr_utility.trace('Entered delete_de_assign for '||p_assignment_id);
585: --
586: hr_utility.set_location(l_proc,1);
587: DELETE per_assignments_f ass
588: WHERE ass.assignment_id = p_assignment_id
589: AND ass.effective_start_date > p_delete_date;
590: --
600: l_max_end_date DATE;
601: --
602: l_proc varchar2(72):=g_package||'get_max_end_date';
603: begin
604: hr_utility.trace('Entered get_max_end_date for '||p_assignment_id);
605: --
606: hr_utility.set_location(l_proc,1);
607: SELECT max(ass.effective_end_date)
608: INTO l_max_end_date
602: l_proc varchar2(72):=g_package||'get_max_end_date';
603: begin
604: hr_utility.trace('Entered get_max_end_date for '||p_assignment_id);
605: --
606: hr_utility.set_location(l_proc,1);
607: SELECT max(ass.effective_end_date)
608: INTO l_max_end_date
609: FROM per_assignments_f ass
610: WHERE ass.assignment_id = p_assignment_id;
624: l_action_chk VARCHAR2(1) := 'N';
625: --
626: l_proc varchar2(72):=g_package||'check_for_future_actions';
627: begin
628: hr_utility.trace('Entered check_for_future_actions for '||p_person_id);
629: --
630: hr_utility.set_location(l_proc,1);
631: -- VT 05/15/96 added criteria for action_status and action_type
632: -- AND pac.action_status = 'C'
626: l_proc varchar2(72):=g_package||'check_for_future_actions';
627: begin
628: hr_utility.trace('Entered check_for_future_actions for '||p_person_id);
629: --
630: hr_utility.set_location(l_proc,1);
631: -- VT 05/15/96 added criteria for action_status and action_type
632: -- AND pac.action_status = 'C'
633: -- AND pac.action_type IN ('R','Q')
634: -- VT 05/22/96 restored back to version 70.32
645: AND pac.payroll_action_id = act.payroll_action_id
646: AND pac.action_type <> 'BEE'
647: AND pac.effective_date > p_action_date);
648: --
649: hr_utility.set_location(l_proc,5);
650: return l_action_chk;
651: --
652: exception when NO_DATA_FOUND then null;
653: return 'N';
669: l_action_date DATE;
670: --
671: l_proc varchar2(72):=g_package||'check_for_compl_actions';
672: begin
673: hr_utility.trace('Entered check_for_compl_actions for '||p_person_id);
674: --
675: IF p_lsp_date IS NOT NULL THEN
676: --
677: -- For bug 3100620. Added = in the following check.
683: END IF;
684: ELSE
685: l_action_date := p_act_date;
686: END IF;
687: hr_utility.set_location(l_proc,1);
688: BEGIN
689: SELECT 'Y'
690: INTO l_action_chk
691: FROM sys.dual
701: AND pac.effective_date > p_fpr_date);
702: exception when NO_DATA_FOUND then null;
703: END;
704: --
705: hr_utility.set_location(l_proc,5);
706: IF l_action_chk = 'N' THEN
707: BEGIN
708: SELECT 'W'
709: INTO l_action_chk
719: AND pac.action_status = 'C'
720: AND pac.action_type <> 'BEE'
721: AND (pac.effective_date BETWEEN l_action_date AND p_fpr_date));
722: --
723: hr_utility.set_location(l_proc,7);
724: exception when NO_DATA_FOUND then null;
725: END;
726: END IF;
727: return l_action_chk;
739: --
740: l_proc varchar2(72):=g_package||'check_for_future_person_rows';
741: begin
742: --
743: hr_utility.set_location(l_proc,5);
744: --
745: SELECT 'Y'
746: INTO l_action_chk
747: FROM sys.dual
750: from per_people_f ppf
751: where ppf.person_id = p_person_id
752: and p_action_date < ppf.effective_start_date);
753: --
754: hr_utility.set_location(l_proc,10);
755: --
756: return l_action_chk;
757: --
758: exception when NO_DATA_FOUND then null;
811: --
812: l_proc varchar2(72):=g_package||'check_for_future_person_type';
813: begin
814: --
815: hr_utility.set_location(l_proc, 10);
816: --
817: -- #289454 Allow cancel termination to succeed if future person type changes
818: -- are to EX_EMP_APL as well as to EX_EMP, providing both types don't exist
819: -- for the same person. This allows terminations to be cancelled for
820: -- Employee-Applicants.
821: --
822: FOR fpt_rec IN future_person_types LOOP
823: --
824: hr_utility.set_location(l_proc, 20);
825: --
826: IF fpt_rec.system_person_type = 'EX_EMP' THEN
827: --
828: hr_utility.set_location(l_proc, 30);
824: hr_utility.set_location(l_proc, 20);
825: --
826: IF fpt_rec.system_person_type = 'EX_EMP' THEN
827: --
828: hr_utility.set_location(l_proc, 30);
829: --
830: l_ex_emp := 'Y';
831: ELSIF fpt_rec.system_person_type = 'EX_EMP_APL' THEN
832: --
829: --
830: l_ex_emp := 'Y';
831: ELSIF fpt_rec.system_person_type = 'EX_EMP_APL' THEN
832: --
833: hr_utility.set_location(l_proc, 40);
834: --
835: l_ex_emp_apl := 'Y';
836: ELSE
837: --
837: --
838: -- Found a person type which will prohibit the cancel termination.
839: -- Can bale out now.
840: --
841: hr_utility.set_location(l_proc, 50);
842: --
843: l_action_chk := 'Y';
844: exit;
845: END IF;
847: -- Now check if we've found both types: that's an error too.
848: --
849: IF l_ex_emp = 'Y' AND l_ex_emp_apl = 'Y' THEN
850: --
851: hr_utility.set_location(l_proc, 60);
852: --
853: -- ER FPT
854: if (nvl(fnd_profile.value('HR_ALLOW_FPT_UPDATES'),'N') = 'Y') then
855: l_action_chk := 'N';
866: open bus_grp_id ;
867: fetch bus_grp_id into l_bus_id ;
868: close bus_grp_id ;
869:
870: hr_utility.set_location('thisis the bus id '||l_bus_id,100);
871: hr_utility.set_location('this is the action date '||p_action_date,110);
872: hr_utility.set_location('this is personid value '||p_person_id,115);
873:
874: open fptypes_for_cwk1;
867: fetch bus_grp_id into l_bus_id ;
868: close bus_grp_id ;
869:
870: hr_utility.set_location('thisis the bus id '||l_bus_id,100);
871: hr_utility.set_location('this is the action date '||p_action_date,110);
872: hr_utility.set_location('this is personid value '||p_person_id,115);
873:
874: open fptypes_for_cwk1;
875: loop
868: close bus_grp_id ;
869:
870: hr_utility.set_location('thisis the bus id '||l_bus_id,100);
871: hr_utility.set_location('this is the action date '||p_action_date,110);
872: hr_utility.set_location('this is personid value '||p_person_id,115);
873:
874: open fptypes_for_cwk1;
875: loop
876: fetch fptypes_for_cwk1 into l_type;
874: open fptypes_for_cwk1;
875: loop
876: fetch fptypes_for_cwk1 into l_type;
877: exit when fptypes_for_cwk1%notfound;
878: hr_utility.set_location('this isthe value of'||L_TYPE, 140);
879:
880: if (l_type = 'CWK' and l_ex_emp='Y')
881: THEN
882: l_action_chk := 'Y';
885: close fptypes_for_cwk1;
886: --
887: -- bug 4457651
888: --
889: hr_utility.set_location(l_proc, 70);
890: --
891: return l_action_chk;
892: --
893: end check_for_future_person_type;
904: l_cobra_benefits_exist VARCHAR2(1) := 'N';
905: --
906: l_proc varchar2(72):=g_package||'check_cobra_benefits';
907: begin
908: hr_utility.set_location(l_proc,10);
909: -- VT 10/18/96 bug #398699 commented out
910: -- begin
911: -- select 'Y'
912: -- into l_cobra_benefits_exist
1272: nonrec_entry.effective_end_date,
1273: p_assignment_id);
1274: IF nonrec_entry.effective_start_date > p_term_date THEN
1275: IF l_process_in_run_flag = 'Y' and p_term_rule IN ('A','L') THEN
1276: hr_utility.set_message(801,'HR_51208_EMP_ASS_NO_TERM_EE');
1277: hr_utility.raise_error;
1278: EXIT;
1279: ELSE
1280: BEGIN
1273: p_assignment_id);
1274: IF nonrec_entry.effective_start_date > p_term_date THEN
1275: IF l_process_in_run_flag = 'Y' and p_term_rule IN ('A','L') THEN
1276: hr_utility.set_message(801,'HR_51208_EMP_ASS_NO_TERM_EE');
1277: hr_utility.raise_error;
1278: EXIT;
1279: ELSE
1280: BEGIN
1281: l_ret_chng := 'N';
1385: p_entries_changed_warning := l_entries_changed_warning;
1386: END IF;
1387: END IF;
1388: --
1389: hr_utility.set_location(l_proc,5);
1390: --
1391: -- Process all recurrring entries for the assignment
1392: FOR rec_entry IN rec_entries(p_assignment_id,
1393: p_term_date
1393: p_term_date
1394: )
1395: LOOP
1396: --
1397: hr_utility.set_location(l_proc,10);
1398: --
1399: -- Bug fix 3983715.
1400: -- Cursor to check whether the type exists.
1401:
1416: WHERE el.element_link_id = rec_entry.element_link_id
1417: AND rec_entry.effective_end_date BETWEEN el.effective_start_date
1418: AND el.effective_end_date;
1419: --
1420: hr_utility.set_location(l_proc,15);
1421: --
1422: -- Make sure delete doew not overlap with a closed period
1423: hr_entry.chk_element_entry_open(l_element_type_id,
1424: p_term_date,
1425: p_term_date,
1426: rec_entry.effective_end_date,
1427: p_assignment_id);
1428: --
1429: hr_utility.set_location(l_proc,20);
1430: --
1431: -- Delete covered_dependents and beneficiaries for the recurring
1432: -- element entries
1433: --
1448: -- pay_element_entry_api.delete_element_entry
1449: --
1450: if rec_entry.effective_start_date > p_term_date then
1451: --
1452: hr_utility.set_location(l_proc,21);
1453: /*
1454: ** Original code, below, performs a delete from PAY_ELEMENT_ENTRIES_F
1455: ** and PAY_ELEMENT_ENTRY_VALUES_F where the entry effective start
1456: ** date is greater than the termination date (i.e. the entry starts
1463: DELETE FROM pay_element_entry_values_f eev
1464: WHERE eev.element_entry_id = rec_entry.element_entry_id
1465: AND eev.effective_start_date > p_term_date;
1466: --
1467: hr_utility.set_location(l_proc,25);
1468: --
1469: DELETE FROM pay_element_entries_f ee
1470: WHERE ee.element_entry_id = rec_entry.element_entry_id
1471: AND ee.effective_start_date > p_term_date;
1490: );
1491: --
1492: ELSIF rec_entry.effective_end_date <> p_term_date THEN -- 6801103
1493: --
1494: hr_utility.set_location(l_proc,30);
1495: --
1496: /*
1497: ** Original code, below, updates PAY_ELEMENT_ENTRIES_F and
1498: ** PAY_ELEMENT_ENTRY_VALUES_F, setting the effective_end_date
1506: SET eev.effective_end_date = p_term_date
1507: WHERE eev.element_entry_id = rec_entry.element_entry_id
1508: AND eev.effective_end_date > p_term_date;
1509: --
1510: hr_utility.set_location(l_proc,35);
1511: --
1512: UPDATE pay_element_entries_f ee
1513: SET ee.effective_end_date = p_term_date
1514: , ee.last_update_date = l_last_update_date
1540: --
1541: END IF; -- l_type_exists
1542: END LOOP; -- FOR rec_entry IN ...
1543: --
1544: hr_utility.set_location(l_proc,40);
1545: --
1546: -- Process all non-recurrring entries for the assignment
1547: FOR nonrec_entry IN nonrec_entries(p_assignment_id,
1548: p_term_date,
1547: FOR nonrec_entry IN nonrec_entries(p_assignment_id,
1548: p_term_date,
1549: p_term_rule) LOOP
1550: --
1551: hr_utility.set_location(l_proc,45);
1552: --
1553: l_process_in_run_flag := 'N';
1554: -- Find the element type id for the entry for use in checking if there
1555: -- are open periods
1565: AND nonrec_entry.effective_end_date BETWEEN et.effective_start_date
1566: AND et.effective_end_date;
1567: /* End Bug 1406063 */
1568: --
1569: hr_utility.set_location(l_proc,50);
1570: --
1571: -- Make sure delete doew not overlap with a closed period
1572: hr_entry.chk_element_entry_open(l_element_type_id,
1573: nonrec_entry.effective_start_date,
1579: -- removed.
1580: if nonrec_entry.effective_start_date > p_term_date then
1581: -- VT 03/01/96 additional check to fix bug #334654,#334681
1582: if l_process_in_run_flag = 'Y' and p_term_rule IN ('A','L') then
1583: hr_utility.set_message(801,'HR_51208_EMP_ASS_NO_TERM_EE');
1584: hr_utility.raise_error;
1585: exit;
1586: else
1587: --
1580: if nonrec_entry.effective_start_date > p_term_date then
1581: -- VT 03/01/96 additional check to fix bug #334654,#334681
1582: if l_process_in_run_flag = 'Y' and p_term_rule IN ('A','L') then
1583: hr_utility.set_message(801,'HR_51208_EMP_ASS_NO_TERM_EE');
1584: hr_utility.raise_error;
1585: exit;
1586: else
1587: --
1588: hr_utility.set_location(l_proc,53);
1584: hr_utility.raise_error;
1585: exit;
1586: else
1587: --
1588: hr_utility.set_location(l_proc,53);
1589: --
1590:
1591: -- Delete covered_dependents and beneficiaries for the non recurring
1592: -- element entries that start after the termination date.
1605:
1606:
1607:
1608: --
1609: hr_utility.set_location(l_proc,55);
1610: --
1611: -- TAR 1660650.999
1612: -- Changed where clause
1613:
1615: WHERE rr.source_type = 'E'
1616: AND rr.source_id = nonrec_entry.element_entry_id
1617: AND rr.status not like 'P%';
1618: --
1619: hr_utility.set_location(l_proc,60);
1620: -- Bugfix 4308892
1621: -- Replace DELETE statements with equivalent calls to
1622: -- pay_element_entry_api.delete_element_entry
1623: --
1632: **
1633: DELETE FROM pay_element_entry_values_f eev
1634: WHERE eev.element_entry_id = nonrec_entry.element_entry_id;
1635: --
1636: hr_utility.set_location(l_proc,65);
1637: --
1638: DELETE FROM pay_element_entries_f ee
1639: WHERE ee.element_entry_id = nonrec_entry.element_entry_id;
1640: **
1671: -- VT 03/05/96 additional check to fix bug #314277
1672: IF p_final_process_date IS NULL THEN
1673:
1674: --
1675: hr_utility.set_location(l_proc,67);
1676: --
1677:
1678: -- Delete covered_dependents and beneficiaries for the non recurring
1679: -- element entries that have no final process date
1690: p_end_date => p_term_date,
1691: p_validation_start_date => p_term_date);
1692:
1693: --
1694: hr_utility.set_location(l_proc,70);
1695: --
1696: -- 31-oct-1995 WWBUG 314277 changed update to p_final_process_date
1697: -- to be p_term_date as this agrees with the relevant termination rule.
1698: -- VT #553177 12/10/97
1701: -- SET eev.effective_end_date = p_term_date
1702: -- WHERE eev.element_entry_id = nonrec_entry.element_entry_id
1703: -- AND eev.effective_end_date = nonrec_entry.effective_end_date;
1704: --
1705: hr_utility.set_location(l_proc,75);
1706: --
1707: -- 31-oct-1995 WWBUG 314277 changed update to p_final_process_date
1708: -- to be p_term_date as this agrees with the relevant termination rule.
1709: -- VT 03/05/96 commented out to fix bug #314277
1715: ELSE
1716: -- VT 01/03/96 bug #314277 effective end date is to be set to
1717: -- p_final_process_date when p_final_process_date is not null.
1718: --
1719: hr_utility.set_location(l_proc,80);
1720: --
1721: -- VT #553177 12/11/97
1722: l_current_period_end := null;
1723: OPEN period_end(p_assignment_id, nonrec_entry.effective_start_date);
1728: AND l_current_period_end IS NOT NULL
1729: AND p_final_process_date <= l_current_period_end THEN
1730: --
1731: --
1732: hr_utility.set_location(l_proc,83);
1733: --
1734:
1735: -- Delete covered_dependents and beneficiaries for the non-recurring
1736: -- element entries which have a final process date.
1788: p_effective_end_date => l_ee_effective_end_date,
1789: p_delete_warning => l_delete_warning
1790: );
1791: --
1792: hr_utility.set_location(l_proc,85);
1793: --
1794: END IF;
1795: END IF;
1796: end if;
1794: END IF;
1795: END IF;
1796: end if;
1797: --
1798: hr_utility.set_location(l_proc,90);
1799: --
1800: -- VT #375157 06/28/96
1801: -- Remove any pay proposals for which there are no element entries.
1802: --delete from per_pay_proposals pp
1809: -- and ee.creator_id = pp.pay_proposal_id);
1810: --
1811: END LOOP; -- FOR nonrec_entry IN ...
1812: --
1813: hr_utility.set_location(l_proc,100);
1814: --
1815: for pay_rec in get_pay_proposals(p_assignment_id,p_term_date,p_term_rule) LOOP
1816: --
1817: hr_utility.set_location(l_proc,110);
1813: hr_utility.set_location(l_proc,100);
1814: --
1815: for pay_rec in get_pay_proposals(p_assignment_id,p_term_date,p_term_rule) LOOP
1816: --
1817: hr_utility.set_location(l_proc,110);
1818: --
1819: if pay_rec.multiple_components='Y' then
1820: DELETE FROM per_pay_proposal_components
1821: WHERE pay_proposal_id=pay_rec.pay_proposal_id;
1823: --
1824: DELETE FROM per_pay_proposals
1825: WHERE pay_proposal_id=pay_rec.pay_proposal_id;
1826: --
1827: hr_utility.set_location(l_proc,120);
1828: --
1829: end loop;
1830: --
1831: hr_utility.set_location(l_proc,130);
1827: hr_utility.set_location(l_proc,120);
1828: --
1829: end loop;
1830: --
1831: hr_utility.set_location(l_proc,130);
1832: --
1833: END delete_entries;
1834: --
1835: -- Bug 5368246.
1888: --
1889: -- 115.59 (END)
1890: --
1891: --
1892: hr_utility.set_location(l_proc,1);
1893: --
1894: DELETE FROM pay_assignment_link_usages_f alu
1895: WHERE alu.assignment_id = p_assignment_id
1896: AND alu.effective_start_date > p_term_date;
1898: if sql%found then
1899: l_alu_del := 'Y';
1900: end if;
1901: --
1902: hr_utility.set_location(l_proc,2);
1903: --
1904: UPDATE pay_assignment_link_usages_f alu
1905: SET alu.effective_end_date = p_term_date
1906: WHERE alu.assignment_id = p_assignment_id
1919: --
1920: -- Main code starts here
1921: BEGIN -- PROCEDURE terminate_entries_and_alus
1922: --
1923: hr_utility.set_location(l_proc,1);
1924: --
1925: -- Validates the combination of dates passed. The valid combinations are
1926: --
1927: -- Actual (if legislation US)
1955: NULL;
1956: --
1957: ELSE
1958: --
1959: hr_utility.set_location(l_proc,2);
1960: --
1961: -- All other parameter combinations invalid
1962: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
1963: hr_utility.set_message_token('PROCEDURE','TERMINATE_ENTRIES');
1958: --
1959: hr_utility.set_location(l_proc,2);
1960: --
1961: -- All other parameter combinations invalid
1962: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
1963: hr_utility.set_message_token('PROCEDURE','TERMINATE_ENTRIES');
1964: hr_utility.set_message_token('STEP', '1');
1965: hr_utility.raise_error;
1966: --
1959: hr_utility.set_location(l_proc,2);
1960: --
1961: -- All other parameter combinations invalid
1962: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
1963: hr_utility.set_message_token('PROCEDURE','TERMINATE_ENTRIES');
1964: hr_utility.set_message_token('STEP', '1');
1965: hr_utility.raise_error;
1966: --
1967: END IF; -- p_actual_term_date IS NOT NULL ...
1960: --
1961: -- All other parameter combinations invalid
1962: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
1963: hr_utility.set_message_token('PROCEDURE','TERMINATE_ENTRIES');
1964: hr_utility.set_message_token('STEP', '1');
1965: hr_utility.raise_error;
1966: --
1967: END IF; -- p_actual_term_date IS NOT NULL ...
1968: l_entries_changed := 'N';
1961: -- All other parameter combinations invalid
1962: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
1963: hr_utility.set_message_token('PROCEDURE','TERMINATE_ENTRIES');
1964: hr_utility.set_message_token('STEP', '1');
1965: hr_utility.raise_error;
1966: --
1967: END IF; -- p_actual_term_date IS NOT NULL ...
1968: l_entries_changed := 'N';
1969: l_cur_entries := 'N';
1968: l_entries_changed := 'N';
1969: l_cur_entries := 'N';
1970: l_cur_alus := 'N';
1971: --
1972: hr_utility.set_location(l_proc,3);
1973: --
1974: -- Shut down all element entries and ALU's for element types which have
1975: -- a post termination rule of 'Actual Termination'
1976: IF p_actual_term_date IS NOT NULL THEN
1974: -- Shut down all element entries and ALU's for element types which have
1975: -- a post termination rule of 'Actual Termination'
1976: IF p_actual_term_date IS NOT NULL THEN
1977: --
1978: hr_utility.set_location(l_proc,4);
1979: --
1980: l_cur_entries := 'N';
1981: delete_entries(p_assignment_id,
1982: p_actual_term_date,
1990: l_entries_changed := 'Y';
1991: end if;
1992: end if;
1993: --
1994: hr_utility.set_location(l_proc,5);
1995: --
1996: --
1997: -- Bug 5368246.
1998: -- We delete alus only when final process date is specified,
2007: ******/
2008: --
2009: END IF; -- IF p_actual_term_date ...
2010: --
2011: hr_utility.set_location(l_proc,6);
2012: --
2013: -- Shut down all element entries and ALU's for element types which have a
2014: -- post termination rule of 'Last Standard Process'
2015: IF p_last_standard_date IS NOT NULL THEN
2013: -- Shut down all element entries and ALU's for element types which have a
2014: -- post termination rule of 'Last Standard Process'
2015: IF p_last_standard_date IS NOT NULL THEN
2016: --
2017: hr_utility.set_location(l_proc,7);
2018: --
2019: l_cur_entries := 'N';
2020: delete_entries(p_assignment_id,
2021: p_last_standard_date,
2029: l_entries_changed := 'Y';
2030: end if;
2031: end if;
2032: --
2033: hr_utility.set_location(l_proc,8);
2034: --
2035: --
2036: -- Bug 5368246.
2037: -- We delete alus only when final process date is specified,
2046: ******/
2047: --
2048: END IF; -- IF p_last_standard_date ...
2049: --
2050: hr_utility.set_location(l_proc,9);
2051: --
2052: -- Shut down all element entries and ALU's for element types which have a
2053: -- post termination rule of 'Final Close'
2054: IF p_final_process_date IS NOT NULL THEN
2078: l_entries_changed := 'Y';
2079: end if;
2080: end if;
2081: --
2082: hr_utility.set_location(l_proc,10);
2083: --
2084: l_cur_entries := 'N';
2085: delete_entries(p_assignment_id,
2086: p_final_process_date,
2094: l_entries_changed := 'Y';
2095: end if;
2096: end if;
2097: --
2098: hr_utility.set_location(l_proc,11);
2099: --
2100: l_cur_alus := 'N';
2101: --
2102: -- Bug 5368246.
2171: where business_group_id = p_business_group_id;
2172: --
2173: l_proc varchar2(72):=g_package||'terminate_employee';
2174: begin
2175: hr_utility.trace('Entered terminate_employee for '||p_person_id);
2176: --
2177: open legislation;
2178: fetch legislation into l_legislation_code;
2179: close legislation;
2177: open legislation;
2178: fetch legislation into l_legislation_code;
2179: close legislation;
2180: --
2181: hr_utility.set_location(l_proc,1);
2182: if p_trigger = 'PRE_UPDATE' then
2183: hr_utility.set_location(l_proc,2);
2184: --
2185: -- Added for US legsilation specific code.
2179: close legislation;
2180: --
2181: hr_utility.set_location(l_proc,1);
2182: if p_trigger = 'PRE_UPDATE' then
2183: hr_utility.set_location(l_proc,2);
2184: --
2185: -- Added for US legsilation specific code.
2186: --
2187: -- Bug 1711085. VS. Removed reference to US legislature.
2189: if (p_actual_termination_date IS NOT NULL AND
2190: p_last_standard_process_date IS NOT NULL)
2191: then null;
2192: else
2193: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2194: hr_utility.set_message_token('PROCEDURE','TERMINATE_EMPLOYEE');
2195: hr_utility.set_message_token('STEP',2);
2196: hr_utility.raise_error;
2197: end if;
2190: p_last_standard_process_date IS NOT NULL)
2191: then null;
2192: else
2193: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2194: hr_utility.set_message_token('PROCEDURE','TERMINATE_EMPLOYEE');
2195: hr_utility.set_message_token('STEP',2);
2196: hr_utility.raise_error;
2197: end if;
2198: --
2191: then null;
2192: else
2193: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2194: hr_utility.set_message_token('PROCEDURE','TERMINATE_EMPLOYEE');
2195: hr_utility.set_message_token('STEP',2);
2196: hr_utility.raise_error;
2197: end if;
2198: --
2199: hr_utility.set_location(l_proc,5);
2192: else
2193: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2194: hr_utility.set_message_token('PROCEDURE','TERMINATE_EMPLOYEE');
2195: hr_utility.set_message_token('STEP',2);
2196: hr_utility.raise_error;
2197: end if;
2198: --
2199: hr_utility.set_location(l_proc,5);
2200: if (p_actual_termination_date <= p_last_standard_process_date
2195: hr_utility.set_message_token('STEP',2);
2196: hr_utility.raise_error;
2197: end if;
2198: --
2199: hr_utility.set_location(l_proc,5);
2200: if (p_actual_termination_date <= p_last_standard_process_date
2201: AND p_last_standard_process_date <=
2202: nvl(p_final_process_date,to_date('31/12/4712','DD/MM/YYYY')))
2203: then null;
2201: AND p_last_standard_process_date <=
2202: nvl(p_final_process_date,to_date('31/12/4712','DD/MM/YYYY')))
2203: then null;
2204: else
2205: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2206: hr_utility.set_message_token('PROCEDURE','TERMINATE_EMPLOYEE');
2207: hr_utility.set_message_token('STEP',5);
2208: hr_utility.raise_error;
2209: end if;
2202: nvl(p_final_process_date,to_date('31/12/4712','DD/MM/YYYY')))
2203: then null;
2204: else
2205: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2206: hr_utility.set_message_token('PROCEDURE','TERMINATE_EMPLOYEE');
2207: hr_utility.set_message_token('STEP',5);
2208: hr_utility.raise_error;
2209: end if;
2210: -- end if;
2203: then null;
2204: else
2205: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2206: hr_utility.set_message_token('PROCEDURE','TERMINATE_EMPLOYEE');
2207: hr_utility.set_message_token('STEP',5);
2208: hr_utility.raise_error;
2209: end if;
2210: -- end if;
2211: --
2204: else
2205: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2206: hr_utility.set_message_token('PROCEDURE','TERMINATE_EMPLOYEE');
2207: hr_utility.set_message_token('STEP',5);
2208: hr_utility.raise_error;
2209: end if;
2210: -- end if;
2211: --
2212: hr_utility.set_location(l_proc,7);
2208: hr_utility.raise_error;
2209: end if;
2210: -- end if;
2211: --
2212: hr_utility.set_location(l_proc,7);
2213: l_action_chk := hrempter.check_for_future_person_rows(p_person_id
2214: ,p_actual_termination_date);
2215: --
2216: if l_action_chk = 'Y' then
2213: l_action_chk := hrempter.check_for_future_person_rows(p_person_id
2214: ,p_actual_termination_date);
2215: --
2216: if l_action_chk = 'Y' then
2217: hr_utility.set_message(801,'HR_7440_TERM_FUT_ROWS_EXST');
2218: hr_utility.raise_error;
2219: end if;
2220: --
2221: hr_utility.set_location(l_proc,10);
2214: ,p_actual_termination_date);
2215: --
2216: if l_action_chk = 'Y' then
2217: hr_utility.set_message(801,'HR_7440_TERM_FUT_ROWS_EXST');
2218: hr_utility.raise_error;
2219: end if;
2220: --
2221: hr_utility.set_location(l_proc,10);
2222: SELECT current_employee_flag
2217: hr_utility.set_message(801,'HR_7440_TERM_FUT_ROWS_EXST');
2218: hr_utility.raise_error;
2219: end if;
2220: --
2221: hr_utility.set_location(l_proc,10);
2222: SELECT current_employee_flag
2223: INTO l_current_employee_flag
2224: FROM per_people_f pp
2225: WHERE pp.person_id = p_person_id
2226: AND p_actual_termination_date + 1
2227: BETWEEN pp.effective_start_date
2228: AND pp.effective_end_date;
2229: --
2230: hr_utility.set_location(l_proc,15);
2231: if l_current_employee_flag = 'Y' then null;
2232: else hr_utility.set_message(801,'HR_6513_EMP_TERMINATED');
2233: hr_utility.raise_error;
2234: end if;
2228: AND pp.effective_end_date;
2229: --
2230: hr_utility.set_location(l_proc,15);
2231: if l_current_employee_flag = 'Y' then null;
2232: else hr_utility.set_message(801,'HR_6513_EMP_TERMINATED');
2233: hr_utility.raise_error;
2234: end if;
2235: --
2236: hr_utility.set_location(l_proc,20);
2229: --
2230: hr_utility.set_location(l_proc,15);
2231: if l_current_employee_flag = 'Y' then null;
2232: else hr_utility.set_message(801,'HR_6513_EMP_TERMINATED');
2233: hr_utility.raise_error;
2234: end if;
2235: --
2236: hr_utility.set_location(l_proc,20);
2237: -- VT 06/04/96 changed function call
2232: else hr_utility.set_message(801,'HR_6513_EMP_TERMINATED');
2233: hr_utility.raise_error;
2234: end if;
2235: --
2236: hr_utility.set_location(l_proc,20);
2237: -- VT 06/04/96 changed function call
2238: l_action_chk := hrempter.check_for_compl_actions(p_person_id
2239: ,p_actual_termination_date
2240: ,p_last_standard_process_date
2239: ,p_actual_termination_date
2240: ,p_last_standard_process_date
2241: ,p_final_process_date);
2242: IF l_action_chk = 'W' THEN
2243: hr_utility.set_message(801,'HR_6516_EMP_TERM_ACTIONS_EXIST');
2244: hr_utility.set_warning;
2245: END IF;
2246: --
2247: if l_action_chk = 'Y' then
2240: ,p_last_standard_process_date
2241: ,p_final_process_date);
2242: IF l_action_chk = 'W' THEN
2243: hr_utility.set_message(801,'HR_6516_EMP_TERM_ACTIONS_EXIST');
2244: hr_utility.set_warning;
2245: END IF;
2246: --
2247: if l_action_chk = 'Y' then
2248: hr_utility.set_message(801,'HR_6516_EMP_TERM_ACTIONS_EXIST');
2244: hr_utility.set_warning;
2245: END IF;
2246: --
2247: if l_action_chk = 'Y' then
2248: hr_utility.set_message(801,'HR_6516_EMP_TERM_ACTIONS_EXIST');
2249: hr_utility.raise_error;
2250: end if;
2251: --
2252: -----------------------------------------------------------------
2245: END IF;
2246: --
2247: if l_action_chk = 'Y' then
2248: hr_utility.set_message(801,'HR_6516_EMP_TERM_ACTIONS_EXIST');
2249: hr_utility.raise_error;
2250: end if;
2251: --
2252: -----------------------------------------------------------------
2253: -- If the FPD is not null then check that COBRA Benefits do not
2252: -----------------------------------------------------------------
2253: -- If the FPD is not null then check that COBRA Benefits do not
2254: -- exist after the FPD
2255: --
2256: hr_utility.set_location(l_proc,23);
2257: if p_final_process_date IS NOT NULL then
2258: if hrempter.check_cobra_benefits
2259: (p_person_id
2260: ,p_final_process_date) then
2257: if p_final_process_date IS NOT NULL then
2258: if hrempter.check_cobra_benefits
2259: (p_person_id
2260: ,p_final_process_date) then
2261: hr_utility.set_message(801,'HR_6968_EMP_COBRA_BENS_EXIST');
2262: hr_utility.raise_error;
2263: end if;
2264: end if;
2265: --
2258: if hrempter.check_cobra_benefits
2259: (p_person_id
2260: ,p_final_process_date) then
2261: hr_utility.set_message(801,'HR_6968_EMP_COBRA_BENS_EXIST');
2262: hr_utility.raise_error;
2263: end if;
2264: end if;
2265: --
2266: hr_utility.set_location(l_proc,30);
2262: hr_utility.raise_error;
2263: end if;
2264: end if;
2265: --
2266: hr_utility.set_location(l_proc,30);
2267: FOR c_emp_rec IN c_employee LOOP
2268: --
2269: if (c_emp_rec.effective_start_date <= p_actual_termination_date
2270: AND p_actual_termination_date <= c_emp_rec.effective_end_date) then
2267: FOR c_emp_rec IN c_employee LOOP
2268: --
2269: if (c_emp_rec.effective_start_date <= p_actual_termination_date
2270: AND p_actual_termination_date <= c_emp_rec.effective_end_date) then
2271: hr_utility.set_location(l_proc,35);
2272: -- Bug 3735333 - Removed the suppression of business group index.
2273: --
2274: SELECT person_type_id
2275: , c_emp_rec.current_applicant_flag
2287: AND system_person_type = 'EX_EMP_APL')
2288: OR (c_emp_rec.current_applicant_flag IS NULL
2289: AND system_person_type = 'EX_EMP'));
2290: --
2291: hr_utility.set_location(l_proc,40);
2292: INSERT INTO PER_ALL_PEOPLE_F
2293: (person_id
2294: ,effective_start_date
2295: ,effective_end_date
2565: , c_emp_rec.country_of_birth
2566: , c_emp_rec.global_person_id
2567: );
2568: --
2569: hr_utility.set_location(l_proc,45);
2570: UPDATE per_people_f pp
2571: SET pp.effective_end_date = p_actual_termination_date
2572: WHERE CURRENT OF c_employee;
2573: --
2577: --
2578: elsif p_trigger = 'POST_UPDATE' then
2579: --
2580: --
2581: hr_utility.set_location(l_proc,50);
2582: FOR c_ass_rec IN c_assignment LOOP
2583: --
2584: hr_utility.set_location(l_proc,55);
2585: l_max_end_date := hrempter.get_max_end_date(c_ass_rec.assignment_id);
2580: --
2581: hr_utility.set_location(l_proc,50);
2582: FOR c_ass_rec IN c_assignment LOOP
2583: --
2584: hr_utility.set_location(l_proc,55);
2585: l_max_end_date := hrempter.get_max_end_date(c_ass_rec.assignment_id);
2586: --
2587: hr_utility.set_location(l_proc,60);
2588: FPD_FLAG := (p_final_process_date IS NOT NULL AND
2583: --
2584: hr_utility.set_location(l_proc,55);
2585: l_max_end_date := hrempter.get_max_end_date(c_ass_rec.assignment_id);
2586: --
2587: hr_utility.set_location(l_proc,60);
2588: FPD_FLAG := (p_final_process_date IS NOT NULL AND
2589: p_final_process_date < l_max_end_date);
2590: --
2591: hr_utility.set_location(l_proc,65);
2587: hr_utility.set_location(l_proc,60);
2588: FPD_FLAG := (p_final_process_date IS NOT NULL AND
2589: p_final_process_date < l_max_end_date);
2590: --
2591: hr_utility.set_location(l_proc,65);
2592: SELECT per_system_status
2593: INTO l_per_system_status
2594: FROM per_assignment_status_types
2595: WHERE assignment_status_type_id = c_ass_rec.assignment_status_type_id;
2593: INTO l_per_system_status
2594: FROM per_assignment_status_types
2595: WHERE assignment_status_type_id = c_ass_rec.assignment_status_type_id;
2596: --
2597: hr_utility.set_location(l_proc,70);
2598: TA_FLAG := (l_per_system_status = 'TERM_ASSIGN' AND
2599: c_ass_rec.effective_start_date < p_actual_termination_date);
2600: --
2601: if NOT TA_FLAG then
2598: TA_FLAG := (l_per_system_status = 'TERM_ASSIGN' AND
2599: c_ass_rec.effective_start_date < p_actual_termination_date);
2600: --
2601: if NOT TA_FLAG then
2602: hr_utility.set_location(l_proc,75);
2603: hrempter.delete_assign_atd(c_ass_rec.assignment_id
2604: ,p_actual_termination_date);
2605: end if;
2606: --
2604: ,p_actual_termination_date);
2605: end if;
2606: --
2607: if FPD_FLAG then
2608: hr_utility.set_location(l_proc,80);
2609: hrempter.delete_assign_fpd(c_ass_rec.assignment_id
2610: ,p_final_process_date);
2611: end if;
2612: --
2609: hrempter.delete_assign_fpd(c_ass_rec.assignment_id
2610: ,p_final_process_date);
2611: end if;
2612: --
2613: hr_utility.set_location(l_proc,85);
2614: --
2615: -- Added for US legsilation specific code.
2616: -- Legislation code will change valid values
2617: -- checking for the termination.
2649: ,p_final_process_date
2650: ,p_actual_termination_date);
2651: end if; /* Check if geocodes is installed */
2652: --
2653: hr_utility.set_location(l_proc,1000);
2654: if (FPD_FLAG AND TA_FLAG) OR
2655: (p_actual_termination_date = p_final_process_date) then
2656: --
2657: hr_utility.set_location(l_proc,90);
2653: hr_utility.set_location(l_proc,1000);
2654: if (FPD_FLAG AND TA_FLAG) OR
2655: (p_actual_termination_date = p_final_process_date) then
2656: --
2657: hr_utility.set_location(l_proc,90);
2658: hrempter.delete_de_assign(c_ass_rec.assignment_id
2659: ,p_final_process_date);
2660: --
2661: hr_utility.set_location(l_proc,95);
2657: hr_utility.set_location(l_proc,90);
2658: hrempter.delete_de_assign(c_ass_rec.assignment_id
2659: ,p_final_process_date);
2660: --
2661: hr_utility.set_location(l_proc,95);
2662: UPDATE per_assignments_f
2663: SET effective_end_date = p_final_process_date
2664: WHERE CURRENT OF c_assignment;
2665: --
2662: UPDATE per_assignments_f
2663: SET effective_end_date = p_final_process_date
2664: WHERE CURRENT OF c_assignment;
2665: --
2666: hr_utility.set_location(l_proc,1200);
2667: elsif NOT TA_FLAG then
2668: hr_utility.set_location(l_proc,95);
2669: if FPD_FLAG then
2670: l_effective_end_date := p_final_process_date;
2664: WHERE CURRENT OF c_assignment;
2665: --
2666: hr_utility.set_location(l_proc,1200);
2667: elsif NOT TA_FLAG then
2668: hr_utility.set_location(l_proc,95);
2669: if FPD_FLAG then
2670: l_effective_end_date := p_final_process_date;
2671: else
2672: l_effective_end_date := l_max_end_date;
2671: else
2672: l_effective_end_date := l_max_end_date;
2673: end if;
2674: --
2675: hr_utility.set_location(l_proc,100);
2676: hrempter.delete_de_assign(c_ass_rec.assignment_id
2677: ,p_actual_termination_date);
2678: --
2679: hr_utility.set_location(l_proc,105);
2675: hr_utility.set_location(l_proc,100);
2676: hrempter.delete_de_assign(c_ass_rec.assignment_id
2677: ,p_actual_termination_date);
2678: --
2679: hr_utility.set_location(l_proc,105);
2680: --
2681: -- #270356. There was a possible problem here, if the user passes
2682: -- a null assignment status type, and a final process date which is
2683: -- is later than the actual termination date. Under these
2704: and nvl(sta.active_flag,stt.active_flag) = 'Y'
2705: and nvl(sta.default_flag, stt.default_flag) = 'Y';
2706: end if;
2707: --
2708: hr_utility.set_location(l_proc,108);
2709: -- VT 04/12/96 bug #354874 added EMPLOYMENT_CATEGORY processing
2710: INSERT INTO per_assignments_f
2711: ( ASSIGNMENT_ID
2712: , EFFECTIVE_START_DATE
2913: , c_ass_rec.WORK_AT_HOME
2914: , c_ass_rec.EMPLOYEE_CATEGORY
2915: , c_ass_rec.JOB_POST_SOURCE_NAME);
2916: --
2917: hr_utility.set_location(l_proc,110);
2918: UPDATE per_assignments_f ass
2919: SET ass.effective_end_date = p_actual_termination_date
2920: WHERE CURRENT OF c_assignment;
2921: --
2920: WHERE CURRENT OF c_assignment;
2921: --
2922: end if;
2923: --
2924: hr_utility.set_location(l_proc,1300);
2925: if NOT TA_FLAG then
2926: hr_utility.set_location(l_proc,115);
2927: hrempter.delete_employee_atd(p_person_id
2928: ,p_actual_termination_date);
2922: end if;
2923: --
2924: hr_utility.set_location(l_proc,1300);
2925: if NOT TA_FLAG then
2926: hr_utility.set_location(l_proc,115);
2927: hrempter.delete_employee_atd(p_person_id
2928: ,p_actual_termination_date);
2929: end if;
2930: --
2931: END LOOP;
2932: -- VT 10/14/96 bug #306710
2933: IF l_entries_changed IN ('S','Y') THEN
2934: IF l_entries_changed = 'S' THEN
2935: hr_utility.set_warning;
2936: fnd_message.set_name('PAY','HR_7442_ASS_SAL_ENT_CHANGED');
2937: END IF;
2938: IF l_entries_changed = 'Y' THEN
2939: hr_utility.set_warning;
2935: hr_utility.set_warning;
2936: fnd_message.set_name('PAY','HR_7442_ASS_SAL_ENT_CHANGED');
2937: END IF;
2938: IF l_entries_changed = 'Y' THEN
2939: hr_utility.set_warning;
2940: fnd_message.set_name('PAY','HR_7016_ASS_ENTRIES_CHANGED');
2941: END IF;
2942: END IF;
2943: --
2941: END IF;
2942: END IF;
2943: --
2944: --
2945: else hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2946: hr_utility.set_message_token('PROCEDURE','TERMINATE_EMPLOYEE');
2947: hr_utility.set_message_token('STEP',1);
2948: hr_utility.raise_error;
2949: --
2942: END IF;
2943: --
2944: --
2945: else hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2946: hr_utility.set_message_token('PROCEDURE','TERMINATE_EMPLOYEE');
2947: hr_utility.set_message_token('STEP',1);
2948: hr_utility.raise_error;
2949: --
2950: end if;
2943: --
2944: --
2945: else hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2946: hr_utility.set_message_token('PROCEDURE','TERMINATE_EMPLOYEE');
2947: hr_utility.set_message_token('STEP',1);
2948: hr_utility.raise_error;
2949: --
2950: end if;
2951: --
2944: --
2945: else hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2946: hr_utility.set_message_token('PROCEDURE','TERMINATE_EMPLOYEE');
2947: hr_utility.set_message_token('STEP',1);
2948: hr_utility.raise_error;
2949: --
2950: end if;
2951: --
2952: end terminate_employee;
2979: FOR UPDATE;
2980: --
2981: l_proc varchar2(72):=g_package||'employee_shutdown';
2982: begin
2983: hr_utility.trace('Entered employee_shutdown for '||p_person_id);
2984: --
2985: hr_utility.set_location(l_proc,1);
2986: if p_trigger = 'PRE_UPDATE' then
2987: --
2981: l_proc varchar2(72):=g_package||'employee_shutdown';
2982: begin
2983: hr_utility.trace('Entered employee_shutdown for '||p_person_id);
2984: --
2985: hr_utility.set_location(l_proc,1);
2986: if p_trigger = 'PRE_UPDATE' then
2987: --
2988: hr_utility.set_location(l_proc,2);
2989: SELECT actual_termination_date
2984: --
2985: hr_utility.set_location(l_proc,1);
2986: if p_trigger = 'PRE_UPDATE' then
2987: --
2988: hr_utility.set_location(l_proc,2);
2989: SELECT actual_termination_date
2990: INTO l_actual_termination_date
2991: FROM per_periods_of_service
2992: WHERE person_id = p_person_id
2993: AND final_process_date IS NULL
2994: AND p_final_process_date BETWEEN date_start
2995: AND to_date('31/12/4712','DD/MM/YYYY');
2996: --
2997: hr_utility.set_location(l_proc,3);
2998: SELECT current_employee_flag
2999: INTO l_current_employee_flag
3000: FROM per_people_f pp
3001: WHERE pp.person_id = p_person_id
3007: BETWEEN pp.effective_start_date
3008: AND pp.effective_end_date));
3009: --
3010: if l_current_employee_flag = 'Y' then
3011: hr_utility.set_message(801,'HR_6514_EMP_NOT_TERMINATED');
3012: hr_utility.raise_error;
3013: end if;
3014: --
3015: hr_utility.set_location(l_proc,5);
3008: AND pp.effective_end_date));
3009: --
3010: if l_current_employee_flag = 'Y' then
3011: hr_utility.set_message(801,'HR_6514_EMP_NOT_TERMINATED');
3012: hr_utility.raise_error;
3013: end if;
3014: --
3015: hr_utility.set_location(l_proc,5);
3016: -- VT 06/04/96 changed function call
3011: hr_utility.set_message(801,'HR_6514_EMP_NOT_TERMINATED');
3012: hr_utility.raise_error;
3013: end if;
3014: --
3015: hr_utility.set_location(l_proc,5);
3016: -- VT 06/04/96 changed function call
3017: l_action_chk := hrempter.check_for_compl_actions(p_person_id
3018: ,l_actual_termination_date
3019: ,l_last_standard_process_date
3019: ,l_last_standard_process_date
3020: ,p_final_process_date);
3021: --
3022: IF l_action_chk = 'W' THEN
3023: hr_utility.set_message(801,'HR_6517_EMP_FPD_ACTIONS_EXIST');
3024: hr_utility.set_warning;
3025: END IF;
3026: --
3027: if l_action_chk = 'Y' then
3020: ,p_final_process_date);
3021: --
3022: IF l_action_chk = 'W' THEN
3023: hr_utility.set_message(801,'HR_6517_EMP_FPD_ACTIONS_EXIST');
3024: hr_utility.set_warning;
3025: END IF;
3026: --
3027: if l_action_chk = 'Y' then
3028: hr_utility.set_message(801,'HR_6517_EMP_FPD_ACTIONS_EXIST');
3024: hr_utility.set_warning;
3025: END IF;
3026: --
3027: if l_action_chk = 'Y' then
3028: hr_utility.set_message(801,'HR_6517_EMP_FPD_ACTIONS_EXIST');
3029: hr_utility.raise_error;
3030: end if;
3031: --
3032: -----------------------------------------------------------------
3025: END IF;
3026: --
3027: if l_action_chk = 'Y' then
3028: hr_utility.set_message(801,'HR_6517_EMP_FPD_ACTIONS_EXIST');
3029: hr_utility.raise_error;
3030: end if;
3031: --
3032: -----------------------------------------------------------------
3033: -- If the FPD is not null then check that COBRA Benefits do not
3032: -----------------------------------------------------------------
3033: -- If the FPD is not null then check that COBRA Benefits do not
3034: -- exist after the FPD
3035: --
3036: hr_utility.set_location(l_proc,7);
3037: if hrempter.check_cobra_benefits
3038: (p_person_id
3039: ,p_final_process_date) then
3040: hr_utility.set_message(801,'HR_6968_EMP_COBRA_BENS_EXIST');
3036: hr_utility.set_location(l_proc,7);
3037: if hrempter.check_cobra_benefits
3038: (p_person_id
3039: ,p_final_process_date) then
3040: hr_utility.set_message(801,'HR_6968_EMP_COBRA_BENS_EXIST');
3041: hr_utility.raise_error;
3042: end if;
3043: --
3044: elsif p_trigger = 'POST_UPDATE' then
3037: if hrempter.check_cobra_benefits
3038: (p_person_id
3039: ,p_final_process_date) then
3040: hr_utility.set_message(801,'HR_6968_EMP_COBRA_BENS_EXIST');
3041: hr_utility.raise_error;
3042: end if;
3043: --
3044: elsif p_trigger = 'POST_UPDATE' then
3045: --
3043: --
3044: elsif p_trigger = 'POST_UPDATE' then
3045: --
3046: --
3047: hr_utility.set_location(l_proc,15);
3048: FOR c_ass_rec IN c_assignment LOOP
3049: --
3050: hr_utility.set_location(l_proc,20);
3051: l_max_end_date := hrempter.get_max_end_date(c_ass_rec.assignment_id);
3046: --
3047: hr_utility.set_location(l_proc,15);
3048: FOR c_ass_rec IN c_assignment LOOP
3049: --
3050: hr_utility.set_location(l_proc,20);
3051: l_max_end_date := hrempter.get_max_end_date(c_ass_rec.assignment_id);
3052: --
3053: if p_final_process_date < l_max_end_date then
3054: hr_utility.set_location(l_proc,25);
3050: hr_utility.set_location(l_proc,20);
3051: l_max_end_date := hrempter.get_max_end_date(c_ass_rec.assignment_id);
3052: --
3053: if p_final_process_date < l_max_end_date then
3054: hr_utility.set_location(l_proc,25);
3055: hrempter.delete_assign_fpd(c_ass_rec.assignment_id
3056: ,p_final_process_date);
3057: --
3058: hr_utility.set_location(l_proc,30);
3054: hr_utility.set_location(l_proc,25);
3055: hrempter.delete_assign_fpd(c_ass_rec.assignment_id
3056: ,p_final_process_date);
3057: --
3058: hr_utility.set_location(l_proc,30);
3059: -- VT 10/07/96 bug #306710 new parameter
3060: l_ent_loop := 'N';
3061: hrempter.terminate_entries_and_alus(c_ass_rec.assignment_id
3062: ,null
3070: if l_ent_loop = 'Y' and l_entries_changed = 'N' then
3071: l_entries_changed := 'Y';
3072: end if;
3073: --
3074: hr_utility.set_location(l_proc,35);
3075: hrempter.delete_de_assign(c_ass_rec.assignment_id
3076: ,p_final_process_date);
3077: --
3078: hr_utility.set_location(l_proc,40);
3074: hr_utility.set_location(l_proc,35);
3075: hrempter.delete_de_assign(c_ass_rec.assignment_id
3076: ,p_final_process_date);
3077: --
3078: hr_utility.set_location(l_proc,40);
3079: UPDATE per_assignments_f ass
3080: SET ass.effective_end_date = p_final_process_date
3081: WHERE CURRENT OF c_assignment;
3082: end if;
3091: END LOOP;
3092: -- VT 10/14/96 bug #306710
3093: IF l_entries_changed IN ('S','Y') THEN
3094: IF l_entries_changed = 'S' THEN
3095: hr_utility.set_warning;
3096: fnd_message.set_name('PAY','HR_7442_ASS_SAL_ENT_CHANGED');
3097: END IF;
3098: IF l_entries_changed = 'Y' THEN
3099: hr_utility.set_warning;
3095: hr_utility.set_warning;
3096: fnd_message.set_name('PAY','HR_7442_ASS_SAL_ENT_CHANGED');
3097: END IF;
3098: IF l_entries_changed = 'Y' THEN
3099: hr_utility.set_warning;
3100: fnd_message.set_name('PAY','HR_7016_ASS_ENTRIES_CHANGED');
3101: END IF;
3102: END IF;
3103: --
3101: END IF;
3102: END IF;
3103: --
3104: --
3105: else hr_utility.set_location(l_proc,1);
3106: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
3107: hr_utility.set_message_token('PROCEDURE','EMPLOYEE_SHUTDOWN');
3108: hr_utility.set_message_token('STEP',1);
3109: hr_utility.raise_error;
3102: END IF;
3103: --
3104: --
3105: else hr_utility.set_location(l_proc,1);
3106: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
3107: hr_utility.set_message_token('PROCEDURE','EMPLOYEE_SHUTDOWN');
3108: hr_utility.set_message_token('STEP',1);
3109: hr_utility.raise_error;
3110: --
3103: --
3104: --
3105: else hr_utility.set_location(l_proc,1);
3106: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
3107: hr_utility.set_message_token('PROCEDURE','EMPLOYEE_SHUTDOWN');
3108: hr_utility.set_message_token('STEP',1);
3109: hr_utility.raise_error;
3110: --
3111: end if;
3104: --
3105: else hr_utility.set_location(l_proc,1);
3106: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
3107: hr_utility.set_message_token('PROCEDURE','EMPLOYEE_SHUTDOWN');
3108: hr_utility.set_message_token('STEP',1);
3109: hr_utility.raise_error;
3110: --
3111: end if;
3112: --
3105: else hr_utility.set_location(l_proc,1);
3106: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
3107: hr_utility.set_message_token('PROCEDURE','EMPLOYEE_SHUTDOWN');
3108: hr_utility.set_message_token('STEP',1);
3109: hr_utility.raise_error;
3110: --
3111: end if;
3112: --
3113: end employee_shutdown;
3136: l_proc varchar2(90) := g_package||'compareAssignment';
3137:
3138: begin
3139:
3140: hr_utility.set_location('Entering : '||l_proc,10);
3141:
3142: open c_full_assignment(p_assignment_id1, p_effective_date1);
3143: fetch c_full_assignment into l_asg1;
3144: close c_full_assignment;
3339: = nvl(l_asg2.PROJECT_TITLE, hr_api.g_varchar2)
3340: and nvl(l_asg1.APPLICANT_RANK,hr_api.g_number)
3341: = nvl(l_asg2.APPLICANT_RANK,hr_api.g_number)
3342: ) then
3343: hr_utility.set_location(l_proc, 20);
3344: l_return := TRUE;
3345: end if;
3346: hr_utility.set_location('Leaving : '||l_proc, 30);
3347: return l_return;
3342: ) then
3343: hr_utility.set_location(l_proc, 20);
3344: l_return := TRUE;
3345: end if;
3346: hr_utility.set_location('Leaving : '||l_proc, 30);
3347: return l_return;
3348: end; /* compareAssignments */
3349: --
3350: -- ======================= cancel_termination ==========================
3518:
3519: --
3520: begin
3521: --
3522: hr_utility.trace('Entering cancel_termination for '||p_person_id);
3523: --
3524: hr_utility.set_location(l_proc,1);
3525: --
3526: begin
3520: begin
3521: --
3522: hr_utility.trace('Entering cancel_termination for '||p_person_id);
3523: --
3524: hr_utility.set_location(l_proc,1);
3525: --
3526: begin
3527: --
3528: SELECT pos.period_of_service_id
3541: WHERE pos.person_id = p_person_id
3542: AND pos.actual_termination_date = p_actual_termination_date;
3543: --
3544: exception when NO_DATA_FOUND then
3545: hr_utility.set_message(801,'HR_6515_EMP_NO_CANCEL');
3546: hr_utility.raise_error;
3547: --
3548: end;
3549: --
3542: AND pos.actual_termination_date = p_actual_termination_date;
3543: --
3544: exception when NO_DATA_FOUND then
3545: hr_utility.set_message(801,'HR_6515_EMP_NO_CANCEL');
3546: hr_utility.raise_error;
3547: --
3548: end;
3549: --
3550: --- newly added code to cancel termination of contracts start
3559: WHERE pc.person_id = p_person_id
3560: AND pc.effective_start_date > p_actual_termination_date;*/
3561: --- newly added code to cancel termination of contracts end
3562: --
3563: -- hr_utility.set_location(l_proc,2);
3564: l_action_chk := hrempter.check_for_future_person_type(p_person_id
3565: ,p_actual_termination_date);
3566: --
3567: if l_action_chk = 'Y' then
3564: l_action_chk := hrempter.check_for_future_person_type(p_person_id
3565: ,p_actual_termination_date);
3566: --
3567: if l_action_chk = 'Y' then
3568: hr_utility.set_message(801,'HR_7122_EMP_CNCL_TERM_INVLD');
3569: hr_utility.raise_error;
3570: end if;
3571: --
3572: hr_utility.set_location(l_proc,99);
3565: ,p_actual_termination_date);
3566: --
3567: if l_action_chk = 'Y' then
3568: hr_utility.set_message(801,'HR_7122_EMP_CNCL_TERM_INVLD');
3569: hr_utility.raise_error;
3570: end if;
3571: --
3572: hr_utility.set_location(l_proc,99);
3573: -- VT 06/04/96 changed function call
3568: hr_utility.set_message(801,'HR_7122_EMP_CNCL_TERM_INVLD');
3569: hr_utility.raise_error;
3570: end if;
3571: --
3572: hr_utility.set_location(l_proc,99);
3573: -- VT 06/04/96 changed function call
3574: l_action_chk := hrempter.check_for_compl_actions(p_person_id
3575: ,p_actual_termination_date
3576: ,l_last_standard_process_date
3579: IF l_action_chk = 'W' THEN
3580: --
3581: -- Fix for bug 3100620 starts here. message is changed.
3582: --
3583: -- hr_utility.set_message(801,'HR_6519_EMP_CANC_ACTIONS_EXIST');
3584: hr_utility.set_message(800,'PER_449053_EMP_TERM_FUT_ERROR');
3585: --
3586: -- Fix for bug 3100620 ends here.
3587: --
3580: --
3581: -- Fix for bug 3100620 starts here. message is changed.
3582: --
3583: -- hr_utility.set_message(801,'HR_6519_EMP_CANC_ACTIONS_EXIST');
3584: hr_utility.set_message(800,'PER_449053_EMP_TERM_FUT_ERROR');
3585: --
3586: -- Fix for bug 3100620 ends here.
3587: --
3588: hr_utility.set_warning;
3584: hr_utility.set_message(800,'PER_449053_EMP_TERM_FUT_ERROR');
3585: --
3586: -- Fix for bug 3100620 ends here.
3587: --
3588: hr_utility.set_warning;
3589: END IF;
3590: --
3591: if l_action_chk = 'Y' then
3592: hr_utility.set_message(801,'HR_6519_EMP_CANC_ACTIONS_EXIST');
3588: hr_utility.set_warning;
3589: END IF;
3590: --
3591: if l_action_chk = 'Y' then
3592: hr_utility.set_message(801,'HR_6519_EMP_CANC_ACTIONS_EXIST');
3593: hr_utility.raise_error;
3594: end if;
3595: --
3596: hr_utility.set_location(l_proc,3);
3589: END IF;
3590: --
3591: if l_action_chk = 'Y' then
3592: hr_utility.set_message(801,'HR_6519_EMP_CANC_ACTIONS_EXIST');
3593: hr_utility.raise_error;
3594: end if;
3595: --
3596: hr_utility.set_location(l_proc,3);
3597: FPD_FLAG := (l_final_process_date IS NOT NULL);
3592: hr_utility.set_message(801,'HR_6519_EMP_CANC_ACTIONS_EXIST');
3593: hr_utility.raise_error;
3594: end if;
3595: --
3596: hr_utility.set_location(l_proc,3);
3597: FPD_FLAG := (l_final_process_date IS NOT NULL);
3598: --
3599: hr_utility.set_location(l_proc,4);
3600: --
3595: --
3596: hr_utility.set_location(l_proc,3);
3597: FPD_FLAG := (l_final_process_date IS NOT NULL);
3598: --
3599: hr_utility.set_location(l_proc,4);
3600: --
3601:
3602: -- ER FPT
3603: IF hrempter.check_for_future_person_rows(p_person_id,p_actual_termination_date) = 'Y'
3627: close csr_is_end_apl;
3628:
3629: if l_sys_per_type is not null and l_sys_per_type = 'EX_APL' then
3630: --
3631: hr_utility.set_location(l_proc,41);
3632: --
3633: UPDATE per_all_people_f pp
3634: SET CURRENT_EMP_OR_APL_FLAG = 'Y',
3635: CURRENT_EMPLOYEE_FLAG = 'Y',
3637: WHERE pp.person_id = p_person_id
3638: AND pp.effective_start_date = p_actual_termination_date+1;
3639: else
3640: --
3641: hr_utility.set_location(l_proc,42);
3642: --
3643: UPDATE per_all_people_f pp
3644: SET pp.effective_end_date = to_date('31/12/4712','DD/MM/YYYY')
3645: WHERE pp.person_id = p_person_id
3646: AND p_actual_termination_date
3647: BETWEEN pp.effective_start_date
3648: AND pp.effective_end_date;
3649: --
3650: hr_utility.set_location(l_proc,6);
3651: --
3652: DELETE per_all_people_f pp
3653: WHERE pp.person_id = p_person_id
3654: AND pp.effective_start_date > p_actual_termination_date;
3652: DELETE per_all_people_f pp
3653: WHERE pp.person_id = p_person_id
3654: AND pp.effective_start_date > p_actual_termination_date;
3655: --
3656: hr_utility.set_location(l_proc,8);
3657: --
3658: -- WWBUG # - CERN want to keep old details
3659: -- was a feature of rel 9
3660: end if;
3791: ,p_pds_fpd => null);
3792: --
3793: --
3794: --
3795: hr_utility.set_location(l_proc,12);
3796:
3797: FOR c_ass_rec IN c_assignment(l_final_process_date) LOOP --modified for bug 8841176
3798: --
3799: hr_utility.set_location(l_proc,14);
3795: hr_utility.set_location(l_proc,12);
3796:
3797: FOR c_ass_rec IN c_assignment(l_final_process_date) LOOP --modified for bug 8841176
3798: --
3799: hr_utility.set_location(l_proc,14);
3800: SELECT per_system_status
3801: INTO l_per_system_status
3802: FROM per_assignment_status_types
3803: WHERE assignment_status_type_id = c_ass_rec.assignment_status_type_id;
3808: -- are currently TERM_ASSIGN back to the appropriate ACTIVE_ASG status.
3809: --
3810: l_asg_status_type_id := c_ass_rec.assignment_status_type_id;
3811: --
3812: hr_utility.set_location(l_proc,16);
3813: l_max_end_date := hrempter.get_max_end_date(c_ass_rec.assignment_id);
3814: --
3815: --if l_per_system_status <> 'TERM_ASSIGN' then
3816: -- bug 5152164 commented out the above if condition and removed the condition
3815: --if l_per_system_status <> 'TERM_ASSIGN' then
3816: -- bug 5152164 commented out the above if condition and removed the condition
3817: -- added in previous fix for the same bug
3818: --
3819: hr_utility.set_location(l_proc,18);
3820: if FPD_FLAG then
3821: hr_utility.set_location(l_proc,20);
3822: if l_max_end_date <> l_final_process_date then
3823: l_effective_end_date := l_max_end_date;
3817: -- added in previous fix for the same bug
3818: --
3819: hr_utility.set_location(l_proc,18);
3820: if FPD_FLAG then
3821: hr_utility.set_location(l_proc,20);
3822: if l_max_end_date <> l_final_process_date then
3823: l_effective_end_date := l_max_end_date;
3824: else
3825: hr_utility.set_location(l_proc,22);
3821: hr_utility.set_location(l_proc,20);
3822: if l_max_end_date <> l_final_process_date then
3823: l_effective_end_date := l_max_end_date;
3824: else
3825: hr_utility.set_location(l_proc,22);
3826: l_effective_end_date := to_date('31/12/4712','DD/MM/YYYY');
3827: end if;
3828: else
3829: hr_utility.set_location(l_proc,24);
3825: hr_utility.set_location(l_proc,22);
3826: l_effective_end_date := to_date('31/12/4712','DD/MM/YYYY');
3827: end if;
3828: else
3829: hr_utility.set_location(l_proc,24);
3830: l_effective_end_date := l_max_end_date;
3831: end if;
3832: --
3833: hr_utility.set_location(l_proc,26);
3829: hr_utility.set_location(l_proc,24);
3830: l_effective_end_date := l_max_end_date;
3831: end if;
3832: --
3833: hr_utility.set_location(l_proc,26);
3834: --
3835: -- Open out the last dated assignment record to the end of time or
3836: -- max_end_date based on above logic.
3837: --
3850: close csr_is_end_apl;
3851:
3852: if l_sys_per_type is not null and l_sys_per_type = 'EX_APL' then
3853: --
3854: hr_utility.set_location(l_proc,261);
3855: --
3856: UPDATE per_assignments_f ass
3857: SET ass.effective_end_date = l_effective_end_date
3858: WHERE assignment_id = c_ass_rec.assignment_id
3859: AND effective_end_date = l_max_end_date
3860: AND ass.assignment_type = 'E';
3861: else
3862: --
3863: hr_utility.set_location(l_proc,262);
3864: --
3865: UPDATE per_assignments_f ass
3866: SET ass.effective_end_date = l_effective_end_date
3867: WHERE assignment_id = c_ass_rec.assignment_id
4036: p_effective_date2 => p_actual_termination_date+1)
4037: and p_actual_termination_date <> nvl(l_final_process_date,
4038: hr_general.end_of_time))
4039: */
4040: hr_utility.set_location('c_ass_rec.assignment_id'||c_ass_rec.assignment_id,27);
4041: hr_utility.set_location('p_actual_termination_date'||p_actual_termination_date,27);
4042: hr_utility.set_location('l_final_process_date'||l_final_process_date,27);
4043:
4044: if p_actual_termination_date <> nvl(l_final_process_date,
4037: and p_actual_termination_date <> nvl(l_final_process_date,
4038: hr_general.end_of_time))
4039: */
4040: hr_utility.set_location('c_ass_rec.assignment_id'||c_ass_rec.assignment_id,27);
4041: hr_utility.set_location('p_actual_termination_date'||p_actual_termination_date,27);
4042: hr_utility.set_location('l_final_process_date'||l_final_process_date,27);
4043:
4044: if p_actual_termination_date <> nvl(l_final_process_date,
4045: hr_general.end_of_time)
4038: hr_general.end_of_time))
4039: */
4040: hr_utility.set_location('c_ass_rec.assignment_id'||c_ass_rec.assignment_id,27);
4041: hr_utility.set_location('p_actual_termination_date'||p_actual_termination_date,27);
4042: hr_utility.set_location('l_final_process_date'||l_final_process_date,27);
4043:
4044: if p_actual_termination_date <> nvl(l_final_process_date,
4045: hr_general.end_of_time)
4046: then
4043:
4044: if p_actual_termination_date <> nvl(l_final_process_date,
4045: hr_general.end_of_time)
4046: then
4047: hr_utility.set_location(l_proc,27);
4048: --
4049: -- The assignments are identical....
4050: --
4051: -- ...get the end date of the assignment created by the termination
4198: );
4199: --
4200: -- end of bug 4919804
4201: --
4202: hr_utility.set_location(l_proc,30);
4203: if l_max_end_date <> l_final_process_date then
4204: null;
4205: else
4206: hr_utility.set_location(l_proc,32);
4202: hr_utility.set_location(l_proc,30);
4203: if l_max_end_date <> l_final_process_date then
4204: null;
4205: else
4206: hr_utility.set_location(l_proc,32);
4207: l_effective_end_date := to_date('31/12/4712','DD/MM/YYYY');
4208: --
4209: hr_utility.set_location(l_proc,34);
4210: UPDATE per_secondary_ass_statuses sas
4205: else
4206: hr_utility.set_location(l_proc,32);
4207: l_effective_end_date := to_date('31/12/4712','DD/MM/YYYY');
4208: --
4209: hr_utility.set_location(l_proc,34);
4210: UPDATE per_secondary_ass_statuses sas
4211: SET sas.end_date = null
4212: WHERE sas.assignment_id = c_ass_rec.assignment_id
4213: AND sas.end_date = l_final_process_date;
4211: SET sas.end_date = null
4212: WHERE sas.assignment_id = c_ass_rec.assignment_id
4213: AND sas.end_date = l_final_process_date;
4214: --
4215: hr_utility.set_location(l_proc,36);
4216: UPDATE pay_personal_payment_methods_f ppm
4217: SET ppm.effective_end_date = l_effective_end_date
4218: WHERE ppm.assignment_id = c_ass_rec.assignment_id
4219: AND ppm.effective_end_date = l_final_process_date;
4217: SET ppm.effective_end_date = l_effective_end_date
4218: WHERE ppm.assignment_id = c_ass_rec.assignment_id
4219: AND ppm.effective_end_date = l_final_process_date;
4220: --
4221: hr_utility.set_location(l_proc,38);
4222: UPDATE pay_cost_allocations_f pca
4223: SET pca.effective_end_date = l_effective_end_date
4224: WHERE pca.assignment_id = c_ass_rec.assignment_id
4225: AND pca.effective_end_date = l_final_process_date;
4223: SET pca.effective_end_date = l_effective_end_date
4224: WHERE pca.assignment_id = c_ass_rec.assignment_id
4225: AND pca.effective_end_date = l_final_process_date;
4226: --
4227: hr_utility.set_location(l_proc,40);
4228: UPDATE per_spinal_point_placements_f spp
4229: SET spp.effective_end_date = l_effective_end_date
4230: WHERE spp.assignment_id = c_ass_rec.assignment_id
4231: AND spp.effective_end_date = l_final_process_date;
4239: -- customer do not use these TAX tables. Only Customers with HR/CERIDIAN
4240: -- use this TAX tables.
4241:
4242: if hr_general.chk_geocodes_installed ='Y' then
4243: hr_utility.set_location(l_proc,41);
4244: pay_us_update_tax_rec_pkg.reverse_term_emp_tax_records
4245: (c_ass_rec.assignment_id
4246: ,l_final_process_date);
4247:
4249: --
4250: -- SASmith 30-APR-1998
4251: -- Due to date tracking of assignment_budget_values
4252:
4253: hr_utility.set_location(l_proc,45);
4254: --
4255: -- Start of Fix for WWBUG 1408379
4256: --
4257: open c2(c_ass_rec.assignment_id);
4295: end if;
4296: --
4297: -- open up element entries closed down by the termination
4298: --
4299: hr_utility.set_location(l_proc,42);
4300: hrentmnt.maintain_entries_asg(c_ass_rec.assignment_id
4301: ,c_ass_rec.business_group_id
4302: ,'CNCL_TERM'
4303: ,p_actual_termination_date
4337: -- PER_XX_TERMINATION.REVERSE
4338: -- If it does exist then construct an anonymous PL/SQL block to call
4339: -- the procedure.
4340: --
4341: hr_utility.set_location(l_proc,43);
4342: --
4343: l_package_name := 'PER_'||l_leg_code||'_TERMINATION';
4344: l_procedure_name := 'REVERSE';
4345: --
4353: fetch csr_leg_pkg into l_dummy;
4354: --
4355: if csr_leg_pkg%found then
4356: --
4357: hr_utility.set_location(l_proc,44);
4358: --
4359: close csr_leg_pkg;
4360: --
4361: -- Added as part of fix for bug 1858597
4372: end if;
4373: --
4374: -- Update the PTU records.
4375: --
4376: hr_utility.set_location(l_proc,65);
4377: --
4378: if l_old_leaving_reason = 'R' then
4379: --
4380: hr_utility.set_location(l_proc,66);
4376: hr_utility.set_location(l_proc,65);
4377: --
4378: if l_old_leaving_reason = 'R' then
4379: --
4380: hr_utility.set_location(l_proc,66);
4381: --
4382: hr_per_type_usage_internal.cancel_person_type_usage
4383: (p_effective_date => p_actual_termination_date+1
4384: ,p_person_id => p_person_id
4387: --
4388: -- Added for Bug 5507290. Need to cancel EX_EMP PTU record
4389: -- aswell along with RETIREE
4390: --
4391: hr_utility.set_location(l_proc,67);
4392: --
4393: --
4394: -- Changes start for the bug 13571033
4395:
4398: close csr_is_end_apl;
4399:
4400: if l_sys_per_type is not null and l_sys_per_type = 'EX_APL' then
4401: --
4402: hr_utility.set_location(l_proc,671);
4403: --
4404: open csr_emp_ptu_info;
4405: fetch csr_emp_ptu_info into l_per_type_usage_id,l_ovn;
4406: close csr_emp_ptu_info;
4414: ,p_effective_start_date => l_eff_start_date
4415: ,p_effective_end_date => l_eff_end_date);
4416: else
4417: --
4418: hr_utility.set_location(l_proc,672);
4419: --
4420: hr_per_type_usage_internal.cancel_person_type_usage
4421: (p_effective_date => p_actual_termination_date+1
4422: ,p_person_id => p_person_id
4439: close csr_is_end_apl;
4440:
4441: if l_sys_per_type is not null and l_sys_per_type = 'EX_APL' then
4442: --
4443: hr_utility.set_location(l_proc,673);
4444: --
4445: open csr_emp_ptu_info;
4446: fetch csr_emp_ptu_info into l_per_type_usage_id,l_ovn;
4447: close csr_emp_ptu_info;
4455: ,p_effective_start_date => l_eff_start_date
4456: ,p_effective_end_date => l_eff_end_date);
4457: else
4458: --
4459: hr_utility.set_location(l_proc,674);
4460: --
4461: hr_utility.set_location('REV_TERM '||l_proc,68);
4462: hr_per_type_usage_internal.cancel_person_type_usage
4463: (p_effective_date => p_actual_termination_date+1
4457: else
4458: --
4459: hr_utility.set_location(l_proc,674);
4460: --
4461: hr_utility.set_location('REV_TERM '||l_proc,68);
4462: hr_per_type_usage_internal.cancel_person_type_usage
4463: (p_effective_date => p_actual_termination_date+1
4464: ,p_person_id => p_person_id
4465: ,p_system_person_type => 'EX_EMP');
4472: --
4473: --
4474: -- call Hr workflow sync code.
4475: --
4476: hr_utility.set_location(l_proc,70);
4477: --
4478: per_hrwf_synch_cover.per_pds_wf(
4479: p_person_id => p_person_id,
4480: p_date => null,