[Home] [Help]
344: IS
345: --
346: l_proc varchar2(72):=g_package||'delete_assign_atd';
347: begin
348: hr_utility.trace('Entered delete_assign_atd for assign '||p_assignment_id);
349: --
350: hr_utility.set_location(l_proc,1);
351: -- Remove booking and event where the event is for the assignment
352: --
346: l_proc varchar2(72):=g_package||'delete_assign_atd';
347: begin
348: hr_utility.trace('Entered delete_assign_atd for assign '||p_assignment_id);
349: --
350: hr_utility.set_location(l_proc,1);
351: -- Remove booking and event where the event is for the assignment
352: --
353: --
354: -- 3735333 Modified the query of per_bookings for better performance.
360: WHERE pev.assignment_id = p_assignment_id
361: AND pev.date_start > p_actual_termination_date
362: );
363: --
364: hr_utility.set_location(l_proc,2);
365: DELETE per_events pev
366: WHERE pev.assignment_id = p_assignment_id
367: AND pev.date_start > p_actual_termination_date
368: AND pev.event_or_interview = 'E';
366: WHERE pev.assignment_id = p_assignment_id
367: AND pev.date_start > p_actual_termination_date
368: AND pev.event_or_interview = 'E';
369: --
370: hr_utility.set_location(l_proc,3);
371: DELETE per_letter_request_lines lrl
372: WHERE lrl.assignment_id = p_assignment_id
373: AND lrl.date_from > p_actual_termination_date;
374: --
400: --
401: -- End of Fix for WWBUG 1408379
402: --
403: begin
404: hr_utility.trace('Entered delete_assign_fpf for assign '||p_assignment_id);
405: --
406: hr_utility.set_location(l_proc,1);
407: UPDATE per_secondary_ass_statuses sas
408: SET sas.end_date = p_final_process_date
402: --
403: begin
404: hr_utility.trace('Entered delete_assign_fpf for assign '||p_assignment_id);
405: --
406: hr_utility.set_location(l_proc,1);
407: UPDATE per_secondary_ass_statuses sas
408: SET sas.end_date = p_final_process_date
409: WHERE sas.assignment_id = p_assignment_id
410: AND sas.end_date IS NULL;
408: SET sas.end_date = p_final_process_date
409: WHERE sas.assignment_id = p_assignment_id
410: AND sas.end_date IS NULL;
411: --
412: hr_utility.set_location(l_proc,5);
413: DELETE per_secondary_ass_statuses sas
414: WHERE sas.assignment_id = p_assignment_id
415: AND sas.start_date > p_final_process_date;
416: --
413: DELETE per_secondary_ass_statuses sas
414: WHERE sas.assignment_id = p_assignment_id
415: AND sas.start_date > p_final_process_date;
416: --
417: hr_utility.set_location(l_proc,10);
418: UPDATE pay_personal_payment_methods_f ppm
419: SET ppm.effective_end_date = p_final_process_date
420: WHERE ppm.assignment_id = p_assignment_id
421: AND p_final_process_date
421: AND p_final_process_date
422: BETWEEN ppm.effective_start_date
423: AND ppm.effective_end_date;
424: --
425: hr_utility.set_location(l_proc,15);
426: DELETE pay_personal_payment_methods_f ppm
427: WHERE ppm.assignment_id = p_assignment_id
428: AND ppm.effective_start_date > p_final_process_date;
429: --
426: DELETE pay_personal_payment_methods_f ppm
427: WHERE ppm.assignment_id = p_assignment_id
428: AND ppm.effective_start_date > p_final_process_date;
429: --
430: hr_utility.set_location(l_proc,20);
431: UPDATE pay_cost_allocations_f pca
432: SET pca.effective_end_date = p_final_process_date
433: WHERE pca.assignment_id = p_assignment_id
434: AND p_final_process_date
434: AND p_final_process_date
435: BETWEEN pca.effective_start_date
436: AND pca.effective_end_date;
437: --
438: hr_utility.set_location(l_proc,25);
439: DELETE pay_cost_allocations_f pca
440: WHERE pca.assignment_id = p_assignment_id
441: AND pca.effective_start_date > p_final_process_date;
442: --
439: DELETE pay_cost_allocations_f pca
440: WHERE pca.assignment_id = p_assignment_id
441: AND pca.effective_start_date > p_final_process_date;
442: --
443: hr_utility.set_location(l_proc,30);
444: UPDATE per_spinal_point_placements_f spp
445: SET spp.effective_end_date = p_final_process_date
446: WHERE spp.assignment_id = p_assignment_id
447: AND p_final_process_date
448: BETWEEN spp.effective_start_date
449: AND spp.effective_end_date;
450: --
451: -- VT 03/06/96 bug #311763
452: hr_utility.set_location(l_proc,35);
453: DELETE per_spinal_point_placements_f spp
454: WHERE spp.assignment_id = p_assignment_id
455: AND spp.effective_start_date > p_final_process_date;
456: --
457:
458: -- Due to date tracking of assignment_budget_values.
459: -- SASmith 30-APR-1998
460:
461: hr_utility.set_location(l_proc,40);
462: DELETE per_assignment_budget_values_f abv
463: WHERE abv.assignment_id = p_assignment_id
464: AND abv.effective_start_date > p_final_process_date;
465: --
462: DELETE per_assignment_budget_values_f abv
463: WHERE abv.assignment_id = p_assignment_id
464: AND abv.effective_start_date > p_final_process_date;
465: --
466: hr_utility.set_location(l_proc,45);
467: --
468: -- Start of FIX for WWBUG 1408379
469: --
470: open c1;
514: IS
515: --
516: l_proc varchar2(72):=g_package||'delete_employee_atd';
517: begin
518: hr_utility.trace('Entered delete_employee_atd for person '||p_person_id);
519: --
520: hr_utility.set_location(l_proc,1);
521: DELETE per_absence_attendances paa
522: WHERE paa.person_id = p_person_id
516: l_proc varchar2(72):=g_package||'delete_employee_atd';
517: begin
518: hr_utility.trace('Entered delete_employee_atd for person '||p_person_id);
519: --
520: hr_utility.set_location(l_proc,1);
521: DELETE per_absence_attendances paa
522: WHERE paa.person_id = p_person_id
523: AND paa.date_start > p_actual_termination_date;
524: --
521: DELETE per_absence_attendances paa
522: WHERE paa.person_id = p_person_id
523: AND paa.date_start > p_actual_termination_date;
524: --
525: hr_utility.set_location(l_proc,5);
526: -- Remove bookings on employee events
527: DELETE per_bookings pb
528: WHERE pb.person_id = p_person_id
529: AND EXISTS (SELECT ''
532: AND pev.emp_or_apl = 'E'
533: AND pev.event_or_interview = 'E'
534: AND pb.event_id = pev.event_id);
535: --
536: hr_utility.set_location(l_proc,7);
537: -- Remove bookings as an interviewer for either employee or applicant
538: -- interviews
539: DELETE per_bookings pb
540: WHERE pb.person_id = p_person_id
549: WHERE person_id = p_person_id
550: )
551: );
552: --
553: hr_utility.set_location(l_proc,10);
554: DELETE per_letter_request_lines lrl
555: WHERE lrl.person_id = p_person_id
556: AND lrl.date_from > p_actual_termination_date;
557: --
568: --
569: l_proc varchar2(72):=g_package||'delete_de_assign';
570: --
571: begin
572: hr_utility.trace('Entered delete_de_assign for '||p_assignment_id);
573: --
574: hr_utility.set_location(l_proc,1);
575: DELETE per_assignments_f ass
576: WHERE ass.assignment_id = p_assignment_id
570: --
571: begin
572: hr_utility.trace('Entered delete_de_assign for '||p_assignment_id);
573: --
574: hr_utility.set_location(l_proc,1);
575: DELETE per_assignments_f ass
576: WHERE ass.assignment_id = p_assignment_id
577: AND ass.effective_start_date > p_delete_date;
578: --
588: l_max_end_date DATE;
589: --
590: l_proc varchar2(72):=g_package||'get_max_end_date';
591: begin
592: hr_utility.trace('Entered get_max_end_date for '||p_assignment_id);
593: --
594: hr_utility.set_location(l_proc,1);
595: SELECT max(ass.effective_end_date)
596: INTO l_max_end_date
590: l_proc varchar2(72):=g_package||'get_max_end_date';
591: begin
592: hr_utility.trace('Entered get_max_end_date for '||p_assignment_id);
593: --
594: hr_utility.set_location(l_proc,1);
595: SELECT max(ass.effective_end_date)
596: INTO l_max_end_date
597: FROM per_assignments_f ass
598: WHERE ass.assignment_id = p_assignment_id;
612: l_action_chk VARCHAR2(1) := 'N';
613: --
614: l_proc varchar2(72):=g_package||'check_for_future_actions';
615: begin
616: hr_utility.trace('Entered check_for_future_actions for '||p_person_id);
617: --
618: hr_utility.set_location(l_proc,1);
619: -- VT 05/15/96 added criteria for action_status and action_type
620: -- AND pac.action_status = 'C'
614: l_proc varchar2(72):=g_package||'check_for_future_actions';
615: begin
616: hr_utility.trace('Entered check_for_future_actions for '||p_person_id);
617: --
618: hr_utility.set_location(l_proc,1);
619: -- VT 05/15/96 added criteria for action_status and action_type
620: -- AND pac.action_status = 'C'
621: -- AND pac.action_type IN ('R','Q')
622: -- VT 05/22/96 restored back to version 70.32
633: AND pac.payroll_action_id = act.payroll_action_id
634: AND pac.action_type <> 'BEE'
635: AND pac.effective_date > p_action_date);
636: --
637: hr_utility.set_location(l_proc,5);
638: return l_action_chk;
639: --
640: exception when NO_DATA_FOUND then null;
641: return 'N';
657: l_action_date DATE;
658: --
659: l_proc varchar2(72):=g_package||'check_for_compl_actions';
660: begin
661: hr_utility.trace('Entered check_for_compl_actions for '||p_person_id);
662: --
663: IF p_lsp_date IS NOT NULL THEN
664: --
665: -- For bug 3100620. Added = in the following check.
671: END IF;
672: ELSE
673: l_action_date := p_act_date;
674: END IF;
675: hr_utility.set_location(l_proc,1);
676: BEGIN
677: SELECT 'Y'
678: INTO l_action_chk
679: FROM sys.dual
689: AND pac.effective_date > p_fpr_date);
690: exception when NO_DATA_FOUND then null;
691: END;
692: --
693: hr_utility.set_location(l_proc,5);
694: IF l_action_chk = 'N' THEN
695: BEGIN
696: SELECT 'W'
697: INTO l_action_chk
707: AND pac.action_status = 'C'
708: AND pac.action_type <> 'BEE'
709: AND (pac.effective_date BETWEEN l_action_date AND p_fpr_date));
710: --
711: hr_utility.set_location(l_proc,7);
712: exception when NO_DATA_FOUND then null;
713: END;
714: END IF;
715: return l_action_chk;
727: --
728: l_proc varchar2(72):=g_package||'check_for_future_person_rows';
729: begin
730: --
731: hr_utility.set_location(l_proc,5);
732: --
733: SELECT 'Y'
734: INTO l_action_chk
735: FROM sys.dual
738: from per_people_f ppf
739: where ppf.person_id = p_person_id
740: and p_action_date < ppf.effective_start_date);
741: --
742: hr_utility.set_location(l_proc,10);
743: --
744: return l_action_chk;
745: --
746: exception when NO_DATA_FOUND then null;
799: --
800: l_proc varchar2(72):=g_package||'check_for_future_person_type';
801: begin
802: --
803: hr_utility.set_location(l_proc, 10);
804: --
805: -- #289454 Allow cancel termination to succeed if future person type changes
806: -- are to EX_EMP_APL as well as to EX_EMP, providing both types don't exist
807: -- for the same person. This allows terminations to be cancelled for
808: -- Employee-Applicants.
809: --
810: FOR fpt_rec IN future_person_types LOOP
811: --
812: hr_utility.set_location(l_proc, 20);
813: --
814: IF fpt_rec.system_person_type = 'EX_EMP' THEN
815: --
816: hr_utility.set_location(l_proc, 30);
812: hr_utility.set_location(l_proc, 20);
813: --
814: IF fpt_rec.system_person_type = 'EX_EMP' THEN
815: --
816: hr_utility.set_location(l_proc, 30);
817: --
818: l_ex_emp := 'Y';
819: ELSIF fpt_rec.system_person_type = 'EX_EMP_APL' THEN
820: --
817: --
818: l_ex_emp := 'Y';
819: ELSIF fpt_rec.system_person_type = 'EX_EMP_APL' THEN
820: --
821: hr_utility.set_location(l_proc, 40);
822: --
823: l_ex_emp_apl := 'Y';
824: ELSE
825: --
825: --
826: -- Found a person type which will prohibit the cancel termination.
827: -- Can bale out now.
828: --
829: hr_utility.set_location(l_proc, 50);
830: --
831: l_action_chk := 'Y';
832: exit;
833: END IF;
835: -- Now check if we've found both types: that's an error too.
836: --
837: IF l_ex_emp = 'Y' AND l_ex_emp_apl = 'Y' THEN
838: --
839: hr_utility.set_location(l_proc, 60);
840: --
841: l_action_chk := 'Y';
842: exit;
843: END IF;
848: open bus_grp_id ;
849: fetch bus_grp_id into l_bus_id ;
850: close bus_grp_id ;
851:
852: hr_utility.set_location('thisis the bus id '||l_bus_id,100);
853: hr_utility.set_location('this is the action date '||p_action_date,110);
854: hr_utility.set_location('this is personid value '||p_person_id,115);
855:
856: open fptypes_for_cwk1;
849: fetch bus_grp_id into l_bus_id ;
850: close bus_grp_id ;
851:
852: hr_utility.set_location('thisis the bus id '||l_bus_id,100);
853: hr_utility.set_location('this is the action date '||p_action_date,110);
854: hr_utility.set_location('this is personid value '||p_person_id,115);
855:
856: open fptypes_for_cwk1;
857: loop
850: close bus_grp_id ;
851:
852: hr_utility.set_location('thisis the bus id '||l_bus_id,100);
853: hr_utility.set_location('this is the action date '||p_action_date,110);
854: hr_utility.set_location('this is personid value '||p_person_id,115);
855:
856: open fptypes_for_cwk1;
857: loop
858: fetch fptypes_for_cwk1 into l_type;
856: open fptypes_for_cwk1;
857: loop
858: fetch fptypes_for_cwk1 into l_type;
859: exit when fptypes_for_cwk1%notfound;
860: hr_utility.set_location('this isthe value of'||L_TYPE, 140);
861:
862: if (l_type = 'CWK' and l_ex_emp='Y')
863: THEN
864: l_action_chk := 'Y';
867: close fptypes_for_cwk1;
868: --
869: -- bug 4457651
870: --
871: hr_utility.set_location(l_proc, 70);
872: --
873: return l_action_chk;
874: --
875: end check_for_future_person_type;
886: l_cobra_benefits_exist VARCHAR2(1) := 'N';
887: --
888: l_proc varchar2(72):=g_package||'check_cobra_benefits';
889: begin
890: hr_utility.set_location(l_proc,10);
891: -- VT 10/18/96 bug #398699 commented out
892: -- begin
893: -- select 'Y'
894: -- into l_cobra_benefits_exist
1254: nonrec_entry.effective_end_date,
1255: p_assignment_id);
1256: IF nonrec_entry.effective_start_date > p_term_date THEN
1257: IF l_process_in_run_flag = 'Y' and p_term_rule IN ('A','L') THEN
1258: hr_utility.set_message(801,'HR_51208_EMP_ASS_NO_TERM_EE');
1259: hr_utility.raise_error;
1260: EXIT;
1261: ELSE
1262: BEGIN
1255: p_assignment_id);
1256: IF nonrec_entry.effective_start_date > p_term_date THEN
1257: IF l_process_in_run_flag = 'Y' and p_term_rule IN ('A','L') THEN
1258: hr_utility.set_message(801,'HR_51208_EMP_ASS_NO_TERM_EE');
1259: hr_utility.raise_error;
1260: EXIT;
1261: ELSE
1262: BEGIN
1263: l_ret_chng := 'N';
1367: p_entries_changed_warning := l_entries_changed_warning;
1368: END IF;
1369: END IF;
1370: --
1371: hr_utility.set_location(l_proc,5);
1372: --
1373: -- Process all recurrring entries for the assignment
1374: FOR rec_entry IN rec_entries(p_assignment_id,
1375: p_term_date
1375: p_term_date
1376: )
1377: LOOP
1378: --
1379: hr_utility.set_location(l_proc,10);
1380: --
1381: -- Bug fix 3983715.
1382: -- Cursor to check whether the type exists.
1383:
1398: WHERE el.element_link_id = rec_entry.element_link_id
1399: AND rec_entry.effective_end_date BETWEEN el.effective_start_date
1400: AND el.effective_end_date;
1401: --
1402: hr_utility.set_location(l_proc,15);
1403: --
1404: -- Make sure delete doew not overlap with a closed period
1405: hr_entry.chk_element_entry_open(l_element_type_id,
1406: p_term_date,
1407: p_term_date,
1408: rec_entry.effective_end_date,
1409: p_assignment_id);
1410: --
1411: hr_utility.set_location(l_proc,20);
1412: --
1413: -- Delete covered_dependents and beneficiaries for the recurring
1414: -- element entries
1415: --
1430: -- pay_element_entry_api.delete_element_entry
1431: --
1432: if rec_entry.effective_start_date > p_term_date then
1433: --
1434: hr_utility.set_location(l_proc,21);
1435: /*
1436: ** Original code, below, performs a delete from PAY_ELEMENT_ENTRIES_F
1437: ** and PAY_ELEMENT_ENTRY_VALUES_F where the entry effective start
1438: ** date is greater than the termination date (i.e. the entry starts
1445: DELETE FROM pay_element_entry_values_f eev
1446: WHERE eev.element_entry_id = rec_entry.element_entry_id
1447: AND eev.effective_start_date > p_term_date;
1448: --
1449: hr_utility.set_location(l_proc,25);
1450: --
1451: DELETE FROM pay_element_entries_f ee
1452: WHERE ee.element_entry_id = rec_entry.element_entry_id
1453: AND ee.effective_start_date > p_term_date;
1472: );
1473: --
1474: ELSIF rec_entry.effective_end_date <> p_term_date THEN -- 6801103
1475: --
1476: hr_utility.set_location(l_proc,30);
1477: --
1478: /*
1479: ** Original code, below, updates PAY_ELEMENT_ENTRIES_F and
1480: ** PAY_ELEMENT_ENTRY_VALUES_F, setting the effective_end_date
1488: SET eev.effective_end_date = p_term_date
1489: WHERE eev.element_entry_id = rec_entry.element_entry_id
1490: AND eev.effective_end_date > p_term_date;
1491: --
1492: hr_utility.set_location(l_proc,35);
1493: --
1494: UPDATE pay_element_entries_f ee
1495: SET ee.effective_end_date = p_term_date
1496: , ee.last_update_date = l_last_update_date
1522: --
1523: END IF; -- l_type_exists
1524: END LOOP; -- FOR rec_entry IN ...
1525: --
1526: hr_utility.set_location(l_proc,40);
1527: --
1528: -- Process all non-recurrring entries for the assignment
1529: FOR nonrec_entry IN nonrec_entries(p_assignment_id,
1530: p_term_date,
1529: FOR nonrec_entry IN nonrec_entries(p_assignment_id,
1530: p_term_date,
1531: p_term_rule) LOOP
1532: --
1533: hr_utility.set_location(l_proc,45);
1534: --
1535: l_process_in_run_flag := 'N';
1536: -- Find the element type id for the entry for use in checking if there
1537: -- are open periods
1547: AND nonrec_entry.effective_end_date BETWEEN et.effective_start_date
1548: AND et.effective_end_date;
1549: /* End Bug 1406063 */
1550: --
1551: hr_utility.set_location(l_proc,50);
1552: --
1553: -- Make sure delete doew not overlap with a closed period
1554: hr_entry.chk_element_entry_open(l_element_type_id,
1555: nonrec_entry.effective_start_date,
1561: -- removed.
1562: if nonrec_entry.effective_start_date > p_term_date then
1563: -- VT 03/01/96 additional check to fix bug #334654,#334681
1564: if l_process_in_run_flag = 'Y' and p_term_rule IN ('A','L') then
1565: hr_utility.set_message(801,'HR_51208_EMP_ASS_NO_TERM_EE');
1566: hr_utility.raise_error;
1567: exit;
1568: else
1569: --
1562: if nonrec_entry.effective_start_date > p_term_date then
1563: -- VT 03/01/96 additional check to fix bug #334654,#334681
1564: if l_process_in_run_flag = 'Y' and p_term_rule IN ('A','L') then
1565: hr_utility.set_message(801,'HR_51208_EMP_ASS_NO_TERM_EE');
1566: hr_utility.raise_error;
1567: exit;
1568: else
1569: --
1570: hr_utility.set_location(l_proc,53);
1566: hr_utility.raise_error;
1567: exit;
1568: else
1569: --
1570: hr_utility.set_location(l_proc,53);
1571: --
1572:
1573: -- Delete covered_dependents and beneficiaries for the non recurring
1574: -- element entries that start after the termination date.
1587:
1588:
1589:
1590: --
1591: hr_utility.set_location(l_proc,55);
1592: --
1593: -- TAR 1660650.999
1594: -- Changed where clause
1595:
1597: WHERE rr.source_type = 'E'
1598: AND rr.source_id = nonrec_entry.element_entry_id
1599: AND rr.status not like 'P%';
1600: --
1601: hr_utility.set_location(l_proc,60);
1602: -- Bugfix 4308892
1603: -- Replace DELETE statements with equivalent calls to
1604: -- pay_element_entry_api.delete_element_entry
1605: --
1614: **
1615: DELETE FROM pay_element_entry_values_f eev
1616: WHERE eev.element_entry_id = nonrec_entry.element_entry_id;
1617: --
1618: hr_utility.set_location(l_proc,65);
1619: --
1620: DELETE FROM pay_element_entries_f ee
1621: WHERE ee.element_entry_id = nonrec_entry.element_entry_id;
1622: **
1653: -- VT 03/05/96 additional check to fix bug #314277
1654: IF p_final_process_date IS NULL THEN
1655:
1656: --
1657: hr_utility.set_location(l_proc,67);
1658: --
1659:
1660: -- Delete covered_dependents and beneficiaries for the non recurring
1661: -- element entries that have no final process date
1672: p_end_date => p_term_date,
1673: p_validation_start_date => p_term_date);
1674:
1675: --
1676: hr_utility.set_location(l_proc,70);
1677: --
1678: -- 31-oct-1995 WWBUG 314277 changed update to p_final_process_date
1679: -- to be p_term_date as this agrees with the relevant termination rule.
1680: -- VT #553177 12/10/97
1683: -- SET eev.effective_end_date = p_term_date
1684: -- WHERE eev.element_entry_id = nonrec_entry.element_entry_id
1685: -- AND eev.effective_end_date = nonrec_entry.effective_end_date;
1686: --
1687: hr_utility.set_location(l_proc,75);
1688: --
1689: -- 31-oct-1995 WWBUG 314277 changed update to p_final_process_date
1690: -- to be p_term_date as this agrees with the relevant termination rule.
1691: -- VT 03/05/96 commented out to fix bug #314277
1697: ELSE
1698: -- VT 01/03/96 bug #314277 effective end date is to be set to
1699: -- p_final_process_date when p_final_process_date is not null.
1700: --
1701: hr_utility.set_location(l_proc,80);
1702: --
1703: -- VT #553177 12/11/97
1704: l_current_period_end := null;
1705: OPEN period_end(p_assignment_id, nonrec_entry.effective_start_date);
1710: AND l_current_period_end IS NOT NULL
1711: AND p_final_process_date <= l_current_period_end THEN
1712: --
1713: --
1714: hr_utility.set_location(l_proc,83);
1715: --
1716:
1717: -- Delete covered_dependents and beneficiaries for the non-recurring
1718: -- element entries which have a final process date.
1770: p_effective_end_date => l_ee_effective_end_date,
1771: p_delete_warning => l_delete_warning
1772: );
1773: --
1774: hr_utility.set_location(l_proc,85);
1775: --
1776: END IF;
1777: END IF;
1778: end if;
1776: END IF;
1777: END IF;
1778: end if;
1779: --
1780: hr_utility.set_location(l_proc,90);
1781: --
1782: -- VT #375157 06/28/96
1783: -- Remove any pay proposals for which there are no element entries.
1784: --delete from per_pay_proposals pp
1791: -- and ee.creator_id = pp.pay_proposal_id);
1792: --
1793: END LOOP; -- FOR nonrec_entry IN ...
1794: --
1795: hr_utility.set_location(l_proc,100);
1796: --
1797: for pay_rec in get_pay_proposals(p_assignment_id,p_term_date,p_term_rule) LOOP
1798: --
1799: hr_utility.set_location(l_proc,110);
1795: hr_utility.set_location(l_proc,100);
1796: --
1797: for pay_rec in get_pay_proposals(p_assignment_id,p_term_date,p_term_rule) LOOP
1798: --
1799: hr_utility.set_location(l_proc,110);
1800: --
1801: if pay_rec.multiple_components='Y' then
1802: DELETE FROM per_pay_proposal_components
1803: WHERE pay_proposal_id=pay_rec.pay_proposal_id;
1805: --
1806: DELETE FROM per_pay_proposals
1807: WHERE pay_proposal_id=pay_rec.pay_proposal_id;
1808: --
1809: hr_utility.set_location(l_proc,120);
1810: --
1811: end loop;
1812: --
1813: hr_utility.set_location(l_proc,130);
1809: hr_utility.set_location(l_proc,120);
1810: --
1811: end loop;
1812: --
1813: hr_utility.set_location(l_proc,130);
1814: --
1815: END delete_entries;
1816: --
1817: -- Bug 5368246.
1870: --
1871: -- 115.59 (END)
1872: --
1873: --
1874: hr_utility.set_location(l_proc,1);
1875: --
1876: DELETE FROM pay_assignment_link_usages_f alu
1877: WHERE alu.assignment_id = p_assignment_id
1878: AND alu.effective_start_date > p_term_date;
1880: if sql%found then
1881: l_alu_del := 'Y';
1882: end if;
1883: --
1884: hr_utility.set_location(l_proc,2);
1885: --
1886: UPDATE pay_assignment_link_usages_f alu
1887: SET alu.effective_end_date = p_term_date
1888: WHERE alu.assignment_id = p_assignment_id
1901: --
1902: -- Main code starts here
1903: BEGIN -- PROCEDURE terminate_entries_and_alus
1904: --
1905: hr_utility.set_location(l_proc,1);
1906: --
1907: -- Validates the combination of dates passed. The valid combinations are
1908: --
1909: -- Actual (if legislation US)
1937: NULL;
1938: --
1939: ELSE
1940: --
1941: hr_utility.set_location(l_proc,2);
1942: --
1943: -- All other parameter combinations invalid
1944: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
1945: hr_utility.set_message_token('PROCEDURE','TERMINATE_ENTRIES');
1940: --
1941: hr_utility.set_location(l_proc,2);
1942: --
1943: -- All other parameter combinations invalid
1944: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
1945: hr_utility.set_message_token('PROCEDURE','TERMINATE_ENTRIES');
1946: hr_utility.set_message_token('STEP', '1');
1947: hr_utility.raise_error;
1948: --
1941: hr_utility.set_location(l_proc,2);
1942: --
1943: -- All other parameter combinations invalid
1944: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
1945: hr_utility.set_message_token('PROCEDURE','TERMINATE_ENTRIES');
1946: hr_utility.set_message_token('STEP', '1');
1947: hr_utility.raise_error;
1948: --
1949: END IF; -- p_actual_term_date IS NOT NULL ...
1942: --
1943: -- All other parameter combinations invalid
1944: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
1945: hr_utility.set_message_token('PROCEDURE','TERMINATE_ENTRIES');
1946: hr_utility.set_message_token('STEP', '1');
1947: hr_utility.raise_error;
1948: --
1949: END IF; -- p_actual_term_date IS NOT NULL ...
1950: l_entries_changed := 'N';
1943: -- All other parameter combinations invalid
1944: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
1945: hr_utility.set_message_token('PROCEDURE','TERMINATE_ENTRIES');
1946: hr_utility.set_message_token('STEP', '1');
1947: hr_utility.raise_error;
1948: --
1949: END IF; -- p_actual_term_date IS NOT NULL ...
1950: l_entries_changed := 'N';
1951: l_cur_entries := 'N';
1950: l_entries_changed := 'N';
1951: l_cur_entries := 'N';
1952: l_cur_alus := 'N';
1953: --
1954: hr_utility.set_location(l_proc,3);
1955: --
1956: -- Shut down all element entries and ALU's for element types which have
1957: -- a post termination rule of 'Actual Termination'
1958: IF p_actual_term_date IS NOT NULL THEN
1956: -- Shut down all element entries and ALU's for element types which have
1957: -- a post termination rule of 'Actual Termination'
1958: IF p_actual_term_date IS NOT NULL THEN
1959: --
1960: hr_utility.set_location(l_proc,4);
1961: --
1962: l_cur_entries := 'N';
1963: delete_entries(p_assignment_id,
1964: p_actual_term_date,
1972: l_entries_changed := 'Y';
1973: end if;
1974: end if;
1975: --
1976: hr_utility.set_location(l_proc,5);
1977: --
1978: --
1979: -- Bug 5368246.
1980: -- We delete alus only when final process date is specified,
1989: ******/
1990: --
1991: END IF; -- IF p_actual_term_date ...
1992: --
1993: hr_utility.set_location(l_proc,6);
1994: --
1995: -- Shut down all element entries and ALU's for element types which have a
1996: -- post termination rule of 'Last Standard Process'
1997: IF p_last_standard_date IS NOT NULL THEN
1995: -- Shut down all element entries and ALU's for element types which have a
1996: -- post termination rule of 'Last Standard Process'
1997: IF p_last_standard_date IS NOT NULL THEN
1998: --
1999: hr_utility.set_location(l_proc,7);
2000: --
2001: l_cur_entries := 'N';
2002: delete_entries(p_assignment_id,
2003: p_last_standard_date,
2011: l_entries_changed := 'Y';
2012: end if;
2013: end if;
2014: --
2015: hr_utility.set_location(l_proc,8);
2016: --
2017: --
2018: -- Bug 5368246.
2019: -- We delete alus only when final process date is specified,
2028: ******/
2029: --
2030: END IF; -- IF p_last_standard_date ...
2031: --
2032: hr_utility.set_location(l_proc,9);
2033: --
2034: -- Shut down all element entries and ALU's for element types which have a
2035: -- post termination rule of 'Final Close'
2036: IF p_final_process_date IS NOT NULL THEN
2060: l_entries_changed := 'Y';
2061: end if;
2062: end if;
2063: --
2064: hr_utility.set_location(l_proc,10);
2065: --
2066: l_cur_entries := 'N';
2067: delete_entries(p_assignment_id,
2068: p_final_process_date,
2076: l_entries_changed := 'Y';
2077: end if;
2078: end if;
2079: --
2080: hr_utility.set_location(l_proc,11);
2081: --
2082: l_cur_alus := 'N';
2083: --
2084: -- Bug 5368246.
2153: where business_group_id = p_business_group_id;
2154: --
2155: l_proc varchar2(72):=g_package||'terminate_employee';
2156: begin
2157: hr_utility.trace('Entered terminate_employee for '||p_person_id);
2158: --
2159: open legislation;
2160: fetch legislation into l_legislation_code;
2161: close legislation;
2159: open legislation;
2160: fetch legislation into l_legislation_code;
2161: close legislation;
2162: --
2163: hr_utility.set_location(l_proc,1);
2164: if p_trigger = 'PRE_UPDATE' then
2165: hr_utility.set_location(l_proc,2);
2166: --
2167: -- Added for US legsilation specific code.
2161: close legislation;
2162: --
2163: hr_utility.set_location(l_proc,1);
2164: if p_trigger = 'PRE_UPDATE' then
2165: hr_utility.set_location(l_proc,2);
2166: --
2167: -- Added for US legsilation specific code.
2168: --
2169: -- Bug 1711085. VS. Removed reference to US legislature.
2171: if (p_actual_termination_date IS NOT NULL AND
2172: p_last_standard_process_date IS NOT NULL)
2173: then null;
2174: else
2175: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2176: hr_utility.set_message_token('PROCEDURE','TERMINATE_EMPLOYEE');
2177: hr_utility.set_message_token('STEP',2);
2178: hr_utility.raise_error;
2179: end if;
2172: p_last_standard_process_date IS NOT NULL)
2173: then null;
2174: else
2175: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2176: hr_utility.set_message_token('PROCEDURE','TERMINATE_EMPLOYEE');
2177: hr_utility.set_message_token('STEP',2);
2178: hr_utility.raise_error;
2179: end if;
2180: --
2173: then null;
2174: else
2175: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2176: hr_utility.set_message_token('PROCEDURE','TERMINATE_EMPLOYEE');
2177: hr_utility.set_message_token('STEP',2);
2178: hr_utility.raise_error;
2179: end if;
2180: --
2181: hr_utility.set_location(l_proc,5);
2174: else
2175: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2176: hr_utility.set_message_token('PROCEDURE','TERMINATE_EMPLOYEE');
2177: hr_utility.set_message_token('STEP',2);
2178: hr_utility.raise_error;
2179: end if;
2180: --
2181: hr_utility.set_location(l_proc,5);
2182: if (p_actual_termination_date <= p_last_standard_process_date
2177: hr_utility.set_message_token('STEP',2);
2178: hr_utility.raise_error;
2179: end if;
2180: --
2181: hr_utility.set_location(l_proc,5);
2182: if (p_actual_termination_date <= p_last_standard_process_date
2183: AND p_last_standard_process_date <=
2184: nvl(p_final_process_date,to_date('31/12/4712','DD/MM/YYYY')))
2185: then null;
2183: AND p_last_standard_process_date <=
2184: nvl(p_final_process_date,to_date('31/12/4712','DD/MM/YYYY')))
2185: then null;
2186: else
2187: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2188: hr_utility.set_message_token('PROCEDURE','TERMINATE_EMPLOYEE');
2189: hr_utility.set_message_token('STEP',5);
2190: hr_utility.raise_error;
2191: end if;
2184: nvl(p_final_process_date,to_date('31/12/4712','DD/MM/YYYY')))
2185: then null;
2186: else
2187: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2188: hr_utility.set_message_token('PROCEDURE','TERMINATE_EMPLOYEE');
2189: hr_utility.set_message_token('STEP',5);
2190: hr_utility.raise_error;
2191: end if;
2192: -- end if;
2185: then null;
2186: else
2187: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2188: hr_utility.set_message_token('PROCEDURE','TERMINATE_EMPLOYEE');
2189: hr_utility.set_message_token('STEP',5);
2190: hr_utility.raise_error;
2191: end if;
2192: -- end if;
2193: --
2186: else
2187: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2188: hr_utility.set_message_token('PROCEDURE','TERMINATE_EMPLOYEE');
2189: hr_utility.set_message_token('STEP',5);
2190: hr_utility.raise_error;
2191: end if;
2192: -- end if;
2193: --
2194: hr_utility.set_location(l_proc,7);
2190: hr_utility.raise_error;
2191: end if;
2192: -- end if;
2193: --
2194: hr_utility.set_location(l_proc,7);
2195: l_action_chk := hrempter.check_for_future_person_rows(p_person_id
2196: ,p_actual_termination_date);
2197: --
2198: if l_action_chk = 'Y' then
2195: l_action_chk := hrempter.check_for_future_person_rows(p_person_id
2196: ,p_actual_termination_date);
2197: --
2198: if l_action_chk = 'Y' then
2199: hr_utility.set_message(801,'HR_7440_TERM_FUT_ROWS_EXST');
2200: hr_utility.raise_error;
2201: end if;
2202: --
2203: hr_utility.set_location(l_proc,10);
2196: ,p_actual_termination_date);
2197: --
2198: if l_action_chk = 'Y' then
2199: hr_utility.set_message(801,'HR_7440_TERM_FUT_ROWS_EXST');
2200: hr_utility.raise_error;
2201: end if;
2202: --
2203: hr_utility.set_location(l_proc,10);
2204: SELECT current_employee_flag
2199: hr_utility.set_message(801,'HR_7440_TERM_FUT_ROWS_EXST');
2200: hr_utility.raise_error;
2201: end if;
2202: --
2203: hr_utility.set_location(l_proc,10);
2204: SELECT current_employee_flag
2205: INTO l_current_employee_flag
2206: FROM per_people_f pp
2207: WHERE pp.person_id = p_person_id
2208: AND p_actual_termination_date + 1
2209: BETWEEN pp.effective_start_date
2210: AND pp.effective_end_date;
2211: --
2212: hr_utility.set_location(l_proc,15);
2213: if l_current_employee_flag = 'Y' then null;
2214: else hr_utility.set_message(801,'HR_6513_EMP_TERMINATED');
2215: hr_utility.raise_error;
2216: end if;
2210: AND pp.effective_end_date;
2211: --
2212: hr_utility.set_location(l_proc,15);
2213: if l_current_employee_flag = 'Y' then null;
2214: else hr_utility.set_message(801,'HR_6513_EMP_TERMINATED');
2215: hr_utility.raise_error;
2216: end if;
2217: --
2218: hr_utility.set_location(l_proc,20);
2211: --
2212: hr_utility.set_location(l_proc,15);
2213: if l_current_employee_flag = 'Y' then null;
2214: else hr_utility.set_message(801,'HR_6513_EMP_TERMINATED');
2215: hr_utility.raise_error;
2216: end if;
2217: --
2218: hr_utility.set_location(l_proc,20);
2219: -- VT 06/04/96 changed function call
2214: else hr_utility.set_message(801,'HR_6513_EMP_TERMINATED');
2215: hr_utility.raise_error;
2216: end if;
2217: --
2218: hr_utility.set_location(l_proc,20);
2219: -- VT 06/04/96 changed function call
2220: l_action_chk := hrempter.check_for_compl_actions(p_person_id
2221: ,p_actual_termination_date
2222: ,p_last_standard_process_date
2221: ,p_actual_termination_date
2222: ,p_last_standard_process_date
2223: ,p_final_process_date);
2224: IF l_action_chk = 'W' THEN
2225: hr_utility.set_message(801,'HR_6516_EMP_TERM_ACTIONS_EXIST');
2226: hr_utility.set_warning;
2227: END IF;
2228: --
2229: if l_action_chk = 'Y' then
2222: ,p_last_standard_process_date
2223: ,p_final_process_date);
2224: IF l_action_chk = 'W' THEN
2225: hr_utility.set_message(801,'HR_6516_EMP_TERM_ACTIONS_EXIST');
2226: hr_utility.set_warning;
2227: END IF;
2228: --
2229: if l_action_chk = 'Y' then
2230: hr_utility.set_message(801,'HR_6516_EMP_TERM_ACTIONS_EXIST');
2226: hr_utility.set_warning;
2227: END IF;
2228: --
2229: if l_action_chk = 'Y' then
2230: hr_utility.set_message(801,'HR_6516_EMP_TERM_ACTIONS_EXIST');
2231: hr_utility.raise_error;
2232: end if;
2233: --
2234: -----------------------------------------------------------------
2227: END IF;
2228: --
2229: if l_action_chk = 'Y' then
2230: hr_utility.set_message(801,'HR_6516_EMP_TERM_ACTIONS_EXIST');
2231: hr_utility.raise_error;
2232: end if;
2233: --
2234: -----------------------------------------------------------------
2235: -- If the FPD is not null then check that COBRA Benefits do not
2234: -----------------------------------------------------------------
2235: -- If the FPD is not null then check that COBRA Benefits do not
2236: -- exist after the FPD
2237: --
2238: hr_utility.set_location(l_proc,23);
2239: if p_final_process_date IS NOT NULL then
2240: if hrempter.check_cobra_benefits
2241: (p_person_id
2242: ,p_final_process_date) then
2239: if p_final_process_date IS NOT NULL then
2240: if hrempter.check_cobra_benefits
2241: (p_person_id
2242: ,p_final_process_date) then
2243: hr_utility.set_message(801,'HR_6968_EMP_COBRA_BENS_EXIST');
2244: hr_utility.raise_error;
2245: end if;
2246: end if;
2247: --
2240: if hrempter.check_cobra_benefits
2241: (p_person_id
2242: ,p_final_process_date) then
2243: hr_utility.set_message(801,'HR_6968_EMP_COBRA_BENS_EXIST');
2244: hr_utility.raise_error;
2245: end if;
2246: end if;
2247: --
2248: hr_utility.set_location(l_proc,30);
2244: hr_utility.raise_error;
2245: end if;
2246: end if;
2247: --
2248: hr_utility.set_location(l_proc,30);
2249: FOR c_emp_rec IN c_employee LOOP
2250: --
2251: if (c_emp_rec.effective_start_date <= p_actual_termination_date
2252: AND p_actual_termination_date <= c_emp_rec.effective_end_date) then
2249: FOR c_emp_rec IN c_employee LOOP
2250: --
2251: if (c_emp_rec.effective_start_date <= p_actual_termination_date
2252: AND p_actual_termination_date <= c_emp_rec.effective_end_date) then
2253: hr_utility.set_location(l_proc,35);
2254: -- Bug 3735333 - Removed the suppression of business group index.
2255: --
2256: SELECT person_type_id
2257: , c_emp_rec.current_applicant_flag
2269: AND system_person_type = 'EX_EMP_APL')
2270: OR (c_emp_rec.current_applicant_flag IS NULL
2271: AND system_person_type = 'EX_EMP'));
2272: --
2273: hr_utility.set_location(l_proc,40);
2274: INSERT INTO PER_ALL_PEOPLE_F
2275: (person_id
2276: ,effective_start_date
2277: ,effective_end_date
2547: , c_emp_rec.country_of_birth
2548: , c_emp_rec.global_person_id
2549: );
2550: --
2551: hr_utility.set_location(l_proc,45);
2552: UPDATE per_people_f pp
2553: SET pp.effective_end_date = p_actual_termination_date
2554: WHERE CURRENT OF c_employee;
2555: --
2559: --
2560: elsif p_trigger = 'POST_UPDATE' then
2561: --
2562: --
2563: hr_utility.set_location(l_proc,50);
2564: FOR c_ass_rec IN c_assignment LOOP
2565: --
2566: hr_utility.set_location(l_proc,55);
2567: l_max_end_date := hrempter.get_max_end_date(c_ass_rec.assignment_id);
2562: --
2563: hr_utility.set_location(l_proc,50);
2564: FOR c_ass_rec IN c_assignment LOOP
2565: --
2566: hr_utility.set_location(l_proc,55);
2567: l_max_end_date := hrempter.get_max_end_date(c_ass_rec.assignment_id);
2568: --
2569: hr_utility.set_location(l_proc,60);
2570: FPD_FLAG := (p_final_process_date IS NOT NULL AND
2565: --
2566: hr_utility.set_location(l_proc,55);
2567: l_max_end_date := hrempter.get_max_end_date(c_ass_rec.assignment_id);
2568: --
2569: hr_utility.set_location(l_proc,60);
2570: FPD_FLAG := (p_final_process_date IS NOT NULL AND
2571: p_final_process_date < l_max_end_date);
2572: --
2573: hr_utility.set_location(l_proc,65);
2569: hr_utility.set_location(l_proc,60);
2570: FPD_FLAG := (p_final_process_date IS NOT NULL AND
2571: p_final_process_date < l_max_end_date);
2572: --
2573: hr_utility.set_location(l_proc,65);
2574: SELECT per_system_status
2575: INTO l_per_system_status
2576: FROM per_assignment_status_types
2577: WHERE assignment_status_type_id = c_ass_rec.assignment_status_type_id;
2575: INTO l_per_system_status
2576: FROM per_assignment_status_types
2577: WHERE assignment_status_type_id = c_ass_rec.assignment_status_type_id;
2578: --
2579: hr_utility.set_location(l_proc,70);
2580: TA_FLAG := (l_per_system_status = 'TERM_ASSIGN' AND
2581: c_ass_rec.effective_start_date < p_actual_termination_date);
2582: --
2583: if NOT TA_FLAG then
2580: TA_FLAG := (l_per_system_status = 'TERM_ASSIGN' AND
2581: c_ass_rec.effective_start_date < p_actual_termination_date);
2582: --
2583: if NOT TA_FLAG then
2584: hr_utility.set_location(l_proc,75);
2585: hrempter.delete_assign_atd(c_ass_rec.assignment_id
2586: ,p_actual_termination_date);
2587: end if;
2588: --
2586: ,p_actual_termination_date);
2587: end if;
2588: --
2589: if FPD_FLAG then
2590: hr_utility.set_location(l_proc,80);
2591: hrempter.delete_assign_fpd(c_ass_rec.assignment_id
2592: ,p_final_process_date);
2593: end if;
2594: --
2591: hrempter.delete_assign_fpd(c_ass_rec.assignment_id
2592: ,p_final_process_date);
2593: end if;
2594: --
2595: hr_utility.set_location(l_proc,85);
2596: --
2597: -- Added for US legsilation specific code.
2598: -- Legislation code will change valid values
2599: -- checking for the termination.
2631: ,p_final_process_date
2632: ,p_actual_termination_date);
2633: end if; /* Check if geocodes is installed */
2634: --
2635: hr_utility.set_location(l_proc,1000);
2636: if (FPD_FLAG AND TA_FLAG) OR
2637: (p_actual_termination_date = p_final_process_date) then
2638: --
2639: hr_utility.set_location(l_proc,90);
2635: hr_utility.set_location(l_proc,1000);
2636: if (FPD_FLAG AND TA_FLAG) OR
2637: (p_actual_termination_date = p_final_process_date) then
2638: --
2639: hr_utility.set_location(l_proc,90);
2640: hrempter.delete_de_assign(c_ass_rec.assignment_id
2641: ,p_final_process_date);
2642: --
2643: hr_utility.set_location(l_proc,95);
2639: hr_utility.set_location(l_proc,90);
2640: hrempter.delete_de_assign(c_ass_rec.assignment_id
2641: ,p_final_process_date);
2642: --
2643: hr_utility.set_location(l_proc,95);
2644: UPDATE per_assignments_f
2645: SET effective_end_date = p_final_process_date
2646: WHERE CURRENT OF c_assignment;
2647: --
2644: UPDATE per_assignments_f
2645: SET effective_end_date = p_final_process_date
2646: WHERE CURRENT OF c_assignment;
2647: --
2648: hr_utility.set_location(l_proc,1200);
2649: elsif NOT TA_FLAG then
2650: hr_utility.set_location(l_proc,95);
2651: if FPD_FLAG then
2652: l_effective_end_date := p_final_process_date;
2646: WHERE CURRENT OF c_assignment;
2647: --
2648: hr_utility.set_location(l_proc,1200);
2649: elsif NOT TA_FLAG then
2650: hr_utility.set_location(l_proc,95);
2651: if FPD_FLAG then
2652: l_effective_end_date := p_final_process_date;
2653: else
2654: l_effective_end_date := l_max_end_date;
2653: else
2654: l_effective_end_date := l_max_end_date;
2655: end if;
2656: --
2657: hr_utility.set_location(l_proc,100);
2658: hrempter.delete_de_assign(c_ass_rec.assignment_id
2659: ,p_actual_termination_date);
2660: --
2661: hr_utility.set_location(l_proc,105);
2657: hr_utility.set_location(l_proc,100);
2658: hrempter.delete_de_assign(c_ass_rec.assignment_id
2659: ,p_actual_termination_date);
2660: --
2661: hr_utility.set_location(l_proc,105);
2662: --
2663: -- #270356. There was a possible problem here, if the user passes
2664: -- a null assignment status type, and a final process date which is
2665: -- is later than the actual termination date. Under these
2686: and nvl(sta.active_flag,stt.active_flag) = 'Y'
2687: and nvl(sta.default_flag, stt.default_flag) = 'Y';
2688: end if;
2689: --
2690: hr_utility.set_location(l_proc,108);
2691: -- VT 04/12/96 bug #354874 added EMPLOYMENT_CATEGORY processing
2692: INSERT INTO per_assignments_f
2693: ( ASSIGNMENT_ID
2694: , EFFECTIVE_START_DATE
2895: , c_ass_rec.WORK_AT_HOME
2896: , c_ass_rec.EMPLOYEE_CATEGORY
2897: , c_ass_rec.JOB_POST_SOURCE_NAME);
2898: --
2899: hr_utility.set_location(l_proc,110);
2900: UPDATE per_assignments_f ass
2901: SET ass.effective_end_date = p_actual_termination_date
2902: WHERE CURRENT OF c_assignment;
2903: --
2902: WHERE CURRENT OF c_assignment;
2903: --
2904: end if;
2905: --
2906: hr_utility.set_location(l_proc,1300);
2907: if NOT TA_FLAG then
2908: hr_utility.set_location(l_proc,115);
2909: hrempter.delete_employee_atd(p_person_id
2910: ,p_actual_termination_date);
2904: end if;
2905: --
2906: hr_utility.set_location(l_proc,1300);
2907: if NOT TA_FLAG then
2908: hr_utility.set_location(l_proc,115);
2909: hrempter.delete_employee_atd(p_person_id
2910: ,p_actual_termination_date);
2911: end if;
2912: --
2913: END LOOP;
2914: -- VT 10/14/96 bug #306710
2915: IF l_entries_changed IN ('S','Y') THEN
2916: IF l_entries_changed = 'S' THEN
2917: hr_utility.set_warning;
2918: fnd_message.set_name('PAY','HR_7442_ASS_SAL_ENT_CHANGED');
2919: END IF;
2920: IF l_entries_changed = 'Y' THEN
2921: hr_utility.set_warning;
2917: hr_utility.set_warning;
2918: fnd_message.set_name('PAY','HR_7442_ASS_SAL_ENT_CHANGED');
2919: END IF;
2920: IF l_entries_changed = 'Y' THEN
2921: hr_utility.set_warning;
2922: fnd_message.set_name('PAY','HR_7016_ASS_ENTRIES_CHANGED');
2923: END IF;
2924: END IF;
2925: --
2923: END IF;
2924: END IF;
2925: --
2926: --
2927: else hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2928: hr_utility.set_message_token('PROCEDURE','TERMINATE_EMPLOYEE');
2929: hr_utility.set_message_token('STEP',1);
2930: hr_utility.raise_error;
2931: --
2924: END IF;
2925: --
2926: --
2927: else hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2928: hr_utility.set_message_token('PROCEDURE','TERMINATE_EMPLOYEE');
2929: hr_utility.set_message_token('STEP',1);
2930: hr_utility.raise_error;
2931: --
2932: end if;
2925: --
2926: --
2927: else hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2928: hr_utility.set_message_token('PROCEDURE','TERMINATE_EMPLOYEE');
2929: hr_utility.set_message_token('STEP',1);
2930: hr_utility.raise_error;
2931: --
2932: end if;
2933: --
2926: --
2927: else hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2928: hr_utility.set_message_token('PROCEDURE','TERMINATE_EMPLOYEE');
2929: hr_utility.set_message_token('STEP',1);
2930: hr_utility.raise_error;
2931: --
2932: end if;
2933: --
2934: end terminate_employee;
2961: FOR UPDATE;
2962: --
2963: l_proc varchar2(72):=g_package||'employee_shutdown';
2964: begin
2965: hr_utility.trace('Entered employee_shutdown for '||p_person_id);
2966: --
2967: hr_utility.set_location(l_proc,1);
2968: if p_trigger = 'PRE_UPDATE' then
2969: --
2963: l_proc varchar2(72):=g_package||'employee_shutdown';
2964: begin
2965: hr_utility.trace('Entered employee_shutdown for '||p_person_id);
2966: --
2967: hr_utility.set_location(l_proc,1);
2968: if p_trigger = 'PRE_UPDATE' then
2969: --
2970: hr_utility.set_location(l_proc,2);
2971: SELECT actual_termination_date
2966: --
2967: hr_utility.set_location(l_proc,1);
2968: if p_trigger = 'PRE_UPDATE' then
2969: --
2970: hr_utility.set_location(l_proc,2);
2971: SELECT actual_termination_date
2972: INTO l_actual_termination_date
2973: FROM per_periods_of_service
2974: WHERE person_id = p_person_id
2975: AND final_process_date IS NULL
2976: AND p_final_process_date BETWEEN date_start
2977: AND to_date('31/12/4712','DD/MM/YYYY');
2978: --
2979: hr_utility.set_location(l_proc,3);
2980: SELECT current_employee_flag
2981: INTO l_current_employee_flag
2982: FROM per_people_f pp
2983: WHERE pp.person_id = p_person_id
2989: BETWEEN pp.effective_start_date
2990: AND pp.effective_end_date));
2991: --
2992: if l_current_employee_flag = 'Y' then
2993: hr_utility.set_message(801,'HR_6514_EMP_NOT_TERMINATED');
2994: hr_utility.raise_error;
2995: end if;
2996: --
2997: hr_utility.set_location(l_proc,5);
2990: AND pp.effective_end_date));
2991: --
2992: if l_current_employee_flag = 'Y' then
2993: hr_utility.set_message(801,'HR_6514_EMP_NOT_TERMINATED');
2994: hr_utility.raise_error;
2995: end if;
2996: --
2997: hr_utility.set_location(l_proc,5);
2998: -- VT 06/04/96 changed function call
2993: hr_utility.set_message(801,'HR_6514_EMP_NOT_TERMINATED');
2994: hr_utility.raise_error;
2995: end if;
2996: --
2997: hr_utility.set_location(l_proc,5);
2998: -- VT 06/04/96 changed function call
2999: l_action_chk := hrempter.check_for_compl_actions(p_person_id
3000: ,l_actual_termination_date
3001: ,l_last_standard_process_date
3001: ,l_last_standard_process_date
3002: ,p_final_process_date);
3003: --
3004: IF l_action_chk = 'W' THEN
3005: hr_utility.set_message(801,'HR_6517_EMP_FPD_ACTIONS_EXIST');
3006: hr_utility.set_warning;
3007: END IF;
3008: --
3009: if l_action_chk = 'Y' then
3002: ,p_final_process_date);
3003: --
3004: IF l_action_chk = 'W' THEN
3005: hr_utility.set_message(801,'HR_6517_EMP_FPD_ACTIONS_EXIST');
3006: hr_utility.set_warning;
3007: END IF;
3008: --
3009: if l_action_chk = 'Y' then
3010: hr_utility.set_message(801,'HR_6517_EMP_FPD_ACTIONS_EXIST');
3006: hr_utility.set_warning;
3007: END IF;
3008: --
3009: if l_action_chk = 'Y' then
3010: hr_utility.set_message(801,'HR_6517_EMP_FPD_ACTIONS_EXIST');
3011: hr_utility.raise_error;
3012: end if;
3013: --
3014: -----------------------------------------------------------------
3007: END IF;
3008: --
3009: if l_action_chk = 'Y' then
3010: hr_utility.set_message(801,'HR_6517_EMP_FPD_ACTIONS_EXIST');
3011: hr_utility.raise_error;
3012: end if;
3013: --
3014: -----------------------------------------------------------------
3015: -- If the FPD is not null then check that COBRA Benefits do not
3014: -----------------------------------------------------------------
3015: -- If the FPD is not null then check that COBRA Benefits do not
3016: -- exist after the FPD
3017: --
3018: hr_utility.set_location(l_proc,7);
3019: if hrempter.check_cobra_benefits
3020: (p_person_id
3021: ,p_final_process_date) then
3022: hr_utility.set_message(801,'HR_6968_EMP_COBRA_BENS_EXIST');
3018: hr_utility.set_location(l_proc,7);
3019: if hrempter.check_cobra_benefits
3020: (p_person_id
3021: ,p_final_process_date) then
3022: hr_utility.set_message(801,'HR_6968_EMP_COBRA_BENS_EXIST');
3023: hr_utility.raise_error;
3024: end if;
3025: --
3026: elsif p_trigger = 'POST_UPDATE' then
3019: if hrempter.check_cobra_benefits
3020: (p_person_id
3021: ,p_final_process_date) then
3022: hr_utility.set_message(801,'HR_6968_EMP_COBRA_BENS_EXIST');
3023: hr_utility.raise_error;
3024: end if;
3025: --
3026: elsif p_trigger = 'POST_UPDATE' then
3027: --
3025: --
3026: elsif p_trigger = 'POST_UPDATE' then
3027: --
3028: --
3029: hr_utility.set_location(l_proc,15);
3030: FOR c_ass_rec IN c_assignment LOOP
3031: --
3032: hr_utility.set_location(l_proc,20);
3033: l_max_end_date := hrempter.get_max_end_date(c_ass_rec.assignment_id);
3028: --
3029: hr_utility.set_location(l_proc,15);
3030: FOR c_ass_rec IN c_assignment LOOP
3031: --
3032: hr_utility.set_location(l_proc,20);
3033: l_max_end_date := hrempter.get_max_end_date(c_ass_rec.assignment_id);
3034: --
3035: if p_final_process_date < l_max_end_date then
3036: hr_utility.set_location(l_proc,25);
3032: hr_utility.set_location(l_proc,20);
3033: l_max_end_date := hrempter.get_max_end_date(c_ass_rec.assignment_id);
3034: --
3035: if p_final_process_date < l_max_end_date then
3036: hr_utility.set_location(l_proc,25);
3037: hrempter.delete_assign_fpd(c_ass_rec.assignment_id
3038: ,p_final_process_date);
3039: --
3040: hr_utility.set_location(l_proc,30);
3036: hr_utility.set_location(l_proc,25);
3037: hrempter.delete_assign_fpd(c_ass_rec.assignment_id
3038: ,p_final_process_date);
3039: --
3040: hr_utility.set_location(l_proc,30);
3041: -- VT 10/07/96 bug #306710 new parameter
3042: l_ent_loop := 'N';
3043: hrempter.terminate_entries_and_alus(c_ass_rec.assignment_id
3044: ,null
3052: if l_ent_loop = 'Y' and l_entries_changed = 'N' then
3053: l_entries_changed := 'Y';
3054: end if;
3055: --
3056: hr_utility.set_location(l_proc,35);
3057: hrempter.delete_de_assign(c_ass_rec.assignment_id
3058: ,p_final_process_date);
3059: --
3060: hr_utility.set_location(l_proc,40);
3056: hr_utility.set_location(l_proc,35);
3057: hrempter.delete_de_assign(c_ass_rec.assignment_id
3058: ,p_final_process_date);
3059: --
3060: hr_utility.set_location(l_proc,40);
3061: UPDATE per_assignments_f ass
3062: SET ass.effective_end_date = p_final_process_date
3063: WHERE CURRENT OF c_assignment;
3064: end if;
3073: END LOOP;
3074: -- VT 10/14/96 bug #306710
3075: IF l_entries_changed IN ('S','Y') THEN
3076: IF l_entries_changed = 'S' THEN
3077: hr_utility.set_warning;
3078: fnd_message.set_name('PAY','HR_7442_ASS_SAL_ENT_CHANGED');
3079: END IF;
3080: IF l_entries_changed = 'Y' THEN
3081: hr_utility.set_warning;
3077: hr_utility.set_warning;
3078: fnd_message.set_name('PAY','HR_7442_ASS_SAL_ENT_CHANGED');
3079: END IF;
3080: IF l_entries_changed = 'Y' THEN
3081: hr_utility.set_warning;
3082: fnd_message.set_name('PAY','HR_7016_ASS_ENTRIES_CHANGED');
3083: END IF;
3084: END IF;
3085: --
3083: END IF;
3084: END IF;
3085: --
3086: --
3087: else hr_utility.set_location(l_proc,1);
3088: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
3089: hr_utility.set_message_token('PROCEDURE','EMPLOYEE_SHUTDOWN');
3090: hr_utility.set_message_token('STEP',1);
3091: hr_utility.raise_error;
3084: END IF;
3085: --
3086: --
3087: else hr_utility.set_location(l_proc,1);
3088: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
3089: hr_utility.set_message_token('PROCEDURE','EMPLOYEE_SHUTDOWN');
3090: hr_utility.set_message_token('STEP',1);
3091: hr_utility.raise_error;
3092: --
3085: --
3086: --
3087: else hr_utility.set_location(l_proc,1);
3088: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
3089: hr_utility.set_message_token('PROCEDURE','EMPLOYEE_SHUTDOWN');
3090: hr_utility.set_message_token('STEP',1);
3091: hr_utility.raise_error;
3092: --
3093: end if;
3086: --
3087: else hr_utility.set_location(l_proc,1);
3088: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
3089: hr_utility.set_message_token('PROCEDURE','EMPLOYEE_SHUTDOWN');
3090: hr_utility.set_message_token('STEP',1);
3091: hr_utility.raise_error;
3092: --
3093: end if;
3094: --
3087: else hr_utility.set_location(l_proc,1);
3088: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
3089: hr_utility.set_message_token('PROCEDURE','EMPLOYEE_SHUTDOWN');
3090: hr_utility.set_message_token('STEP',1);
3091: hr_utility.raise_error;
3092: --
3093: end if;
3094: --
3095: end employee_shutdown;
3118: l_proc varchar2(90) := g_package||'compareAssignment';
3119:
3120: begin
3121:
3122: hr_utility.set_location('Entering : '||l_proc,10);
3123:
3124: open c_full_assignment(p_assignment_id1, p_effective_date1);
3125: fetch c_full_assignment into l_asg1;
3126: close c_full_assignment;
3321: = nvl(l_asg2.PROJECT_TITLE, hr_api.g_varchar2)
3322: and nvl(l_asg1.APPLICANT_RANK,hr_api.g_number)
3323: = nvl(l_asg2.APPLICANT_RANK,hr_api.g_number)
3324: ) then
3325: hr_utility.set_location(l_proc, 20);
3326: l_return := TRUE;
3327: end if;
3328: hr_utility.set_location('Leaving : '||l_proc, 30);
3329: return l_return;
3324: ) then
3325: hr_utility.set_location(l_proc, 20);
3326: l_return := TRUE;
3327: end if;
3328: hr_utility.set_location('Leaving : '||l_proc, 30);
3329: return l_return;
3330: end; /* compareAssignments */
3331: --
3332: -- ======================= cancel_termination ==========================
3451: l_proc varchar2(72):=g_package||'cancel_termination';
3452: --
3453: begin
3454: --
3455: hr_utility.trace('Entering cancel_termination for '||p_person_id);
3456: --
3457: hr_utility.set_location(l_proc,1);
3458: --
3459: begin
3453: begin
3454: --
3455: hr_utility.trace('Entering cancel_termination for '||p_person_id);
3456: --
3457: hr_utility.set_location(l_proc,1);
3458: --
3459: begin
3460: --
3461: SELECT pos.period_of_service_id
3474: WHERE pos.person_id = p_person_id
3475: AND pos.actual_termination_date = p_actual_termination_date;
3476: --
3477: exception when NO_DATA_FOUND then
3478: hr_utility.set_message(801,'HR_6515_EMP_NO_CANCEL');
3479: hr_utility.raise_error;
3480: --
3481: end;
3482: --
3475: AND pos.actual_termination_date = p_actual_termination_date;
3476: --
3477: exception when NO_DATA_FOUND then
3478: hr_utility.set_message(801,'HR_6515_EMP_NO_CANCEL');
3479: hr_utility.raise_error;
3480: --
3481: end;
3482: --
3483: --- newly added code to cancel termination of contracts start
3492: WHERE pc.person_id = p_person_id
3493: AND pc.effective_start_date > p_actual_termination_date;*/
3494: --- newly added code to cancel termination of contracts end
3495: --
3496: -- hr_utility.set_location(l_proc,2);
3497: l_action_chk := hrempter.check_for_future_person_type(p_person_id
3498: ,p_actual_termination_date);
3499: --
3500: if l_action_chk = 'Y' then
3497: l_action_chk := hrempter.check_for_future_person_type(p_person_id
3498: ,p_actual_termination_date);
3499: --
3500: if l_action_chk = 'Y' then
3501: hr_utility.set_message(801,'HR_7122_EMP_CNCL_TERM_INVLD');
3502: hr_utility.raise_error;
3503: end if;
3504: --
3505: hr_utility.set_location(l_proc,99);
3498: ,p_actual_termination_date);
3499: --
3500: if l_action_chk = 'Y' then
3501: hr_utility.set_message(801,'HR_7122_EMP_CNCL_TERM_INVLD');
3502: hr_utility.raise_error;
3503: end if;
3504: --
3505: hr_utility.set_location(l_proc,99);
3506: -- VT 06/04/96 changed function call
3501: hr_utility.set_message(801,'HR_7122_EMP_CNCL_TERM_INVLD');
3502: hr_utility.raise_error;
3503: end if;
3504: --
3505: hr_utility.set_location(l_proc,99);
3506: -- VT 06/04/96 changed function call
3507: l_action_chk := hrempter.check_for_compl_actions(p_person_id
3508: ,p_actual_termination_date
3509: ,l_last_standard_process_date
3512: IF l_action_chk = 'W' THEN
3513: --
3514: -- Fix for bug 3100620 starts here. message is changed.
3515: --
3516: -- hr_utility.set_message(801,'HR_6519_EMP_CANC_ACTIONS_EXIST');
3517: hr_utility.set_message(800,'PER_449053_EMP_TERM_FUT_ERROR');
3518: --
3519: -- Fix for bug 3100620 ends here.
3520: --
3513: --
3514: -- Fix for bug 3100620 starts here. message is changed.
3515: --
3516: -- hr_utility.set_message(801,'HR_6519_EMP_CANC_ACTIONS_EXIST');
3517: hr_utility.set_message(800,'PER_449053_EMP_TERM_FUT_ERROR');
3518: --
3519: -- Fix for bug 3100620 ends here.
3520: --
3521: hr_utility.set_warning;
3517: hr_utility.set_message(800,'PER_449053_EMP_TERM_FUT_ERROR');
3518: --
3519: -- Fix for bug 3100620 ends here.
3520: --
3521: hr_utility.set_warning;
3522: END IF;
3523: --
3524: if l_action_chk = 'Y' then
3525: hr_utility.set_message(801,'HR_6519_EMP_CANC_ACTIONS_EXIST');
3521: hr_utility.set_warning;
3522: END IF;
3523: --
3524: if l_action_chk = 'Y' then
3525: hr_utility.set_message(801,'HR_6519_EMP_CANC_ACTIONS_EXIST');
3526: hr_utility.raise_error;
3527: end if;
3528: --
3529: hr_utility.set_location(l_proc,3);
3522: END IF;
3523: --
3524: if l_action_chk = 'Y' then
3525: hr_utility.set_message(801,'HR_6519_EMP_CANC_ACTIONS_EXIST');
3526: hr_utility.raise_error;
3527: end if;
3528: --
3529: hr_utility.set_location(l_proc,3);
3530: FPD_FLAG := (l_final_process_date IS NOT NULL);
3525: hr_utility.set_message(801,'HR_6519_EMP_CANC_ACTIONS_EXIST');
3526: hr_utility.raise_error;
3527: end if;
3528: --
3529: hr_utility.set_location(l_proc,3);
3530: FPD_FLAG := (l_final_process_date IS NOT NULL);
3531: --
3532: hr_utility.set_location(l_proc,4);
3533: UPDATE per_all_people_f pp
3528: --
3529: hr_utility.set_location(l_proc,3);
3530: FPD_FLAG := (l_final_process_date IS NOT NULL);
3531: --
3532: hr_utility.set_location(l_proc,4);
3533: UPDATE per_all_people_f pp
3534: SET pp.effective_end_date = to_date('31/12/4712','DD/MM/YYYY')
3535: WHERE pp.person_id = p_person_id
3536: AND p_actual_termination_date
3536: AND p_actual_termination_date
3537: BETWEEN pp.effective_start_date
3538: AND pp.effective_end_date;
3539: --
3540: hr_utility.set_location(l_proc,6);
3541: DELETE per_all_people_f pp
3542: WHERE pp.person_id = p_person_id
3543: AND pp.effective_start_date > p_actual_termination_date;
3544: --
3541: DELETE per_all_people_f pp
3542: WHERE pp.person_id = p_person_id
3543: AND pp.effective_start_date > p_actual_termination_date;
3544: --
3545: hr_utility.set_location(l_proc,8);
3546: --
3547: -- WWBUG # - CERN want to keep old details
3548: -- was a feature of rel 9
3549: if (p_clear_details = 'N') then
3673: ,p_pds_fpd => null);
3674: --
3675: --
3676: --
3677: hr_utility.set_location(l_proc,12);
3678:
3679: FOR c_ass_rec IN c_assignment LOOP
3680: --
3681: hr_utility.set_location(l_proc,14);
3677: hr_utility.set_location(l_proc,12);
3678:
3679: FOR c_ass_rec IN c_assignment LOOP
3680: --
3681: hr_utility.set_location(l_proc,14);
3682: SELECT per_system_status
3683: INTO l_per_system_status
3684: FROM per_assignment_status_types
3685: WHERE assignment_status_type_id = c_ass_rec.assignment_status_type_id;
3690: -- are currently TERM_ASSIGN back to the appropriate ACTIVE_ASG status.
3691: --
3692: l_asg_status_type_id := c_ass_rec.assignment_status_type_id;
3693: --
3694: hr_utility.set_location(l_proc,16);
3695: l_max_end_date := hrempter.get_max_end_date(c_ass_rec.assignment_id);
3696: --
3697: --if l_per_system_status <> 'TERM_ASSIGN' then
3698: -- bug 5152164 commented out the above if condition and removed the condition
3697: --if l_per_system_status <> 'TERM_ASSIGN' then
3698: -- bug 5152164 commented out the above if condition and removed the condition
3699: -- added in previous fix for the same bug
3700: --
3701: hr_utility.set_location(l_proc,18);
3702: if FPD_FLAG then
3703: hr_utility.set_location(l_proc,20);
3704: if l_max_end_date <> l_final_process_date then
3705: l_effective_end_date := l_max_end_date;
3699: -- added in previous fix for the same bug
3700: --
3701: hr_utility.set_location(l_proc,18);
3702: if FPD_FLAG then
3703: hr_utility.set_location(l_proc,20);
3704: if l_max_end_date <> l_final_process_date then
3705: l_effective_end_date := l_max_end_date;
3706: else
3707: hr_utility.set_location(l_proc,22);
3703: hr_utility.set_location(l_proc,20);
3704: if l_max_end_date <> l_final_process_date then
3705: l_effective_end_date := l_max_end_date;
3706: else
3707: hr_utility.set_location(l_proc,22);
3708: l_effective_end_date := to_date('31/12/4712','DD/MM/YYYY');
3709: end if;
3710: else
3711: hr_utility.set_location(l_proc,24);
3707: hr_utility.set_location(l_proc,22);
3708: l_effective_end_date := to_date('31/12/4712','DD/MM/YYYY');
3709: end if;
3710: else
3711: hr_utility.set_location(l_proc,24);
3712: l_effective_end_date := l_max_end_date;
3713: end if;
3714: --
3715: hr_utility.set_location(l_proc,26);
3711: hr_utility.set_location(l_proc,24);
3712: l_effective_end_date := l_max_end_date;
3713: end if;
3714: --
3715: hr_utility.set_location(l_proc,26);
3716: --
3717: -- Open out the last dated assignment record to the end of time or
3718: -- max_end_date based on above logic.
3719: --
3893: p_effective_date2 => p_actual_termination_date+1)
3894: and p_actual_termination_date <> nvl(l_final_process_date,
3895: hr_general.end_of_time))
3896: then
3897: hr_utility.set_location(l_proc,27);
3898: --
3899: -- The assignments are identical....
3900: --
3901: -- ...get the end date of the assignment created by the termination
4048: );
4049: --
4050: -- end of bug 4919804
4051: --
4052: hr_utility.set_location(l_proc,30);
4053: if l_max_end_date <> l_final_process_date then
4054: null;
4055: else
4056: hr_utility.set_location(l_proc,32);
4052: hr_utility.set_location(l_proc,30);
4053: if l_max_end_date <> l_final_process_date then
4054: null;
4055: else
4056: hr_utility.set_location(l_proc,32);
4057: l_effective_end_date := to_date('31/12/4712','DD/MM/YYYY');
4058: --
4059: hr_utility.set_location(l_proc,34);
4060: UPDATE per_secondary_ass_statuses sas
4055: else
4056: hr_utility.set_location(l_proc,32);
4057: l_effective_end_date := to_date('31/12/4712','DD/MM/YYYY');
4058: --
4059: hr_utility.set_location(l_proc,34);
4060: UPDATE per_secondary_ass_statuses sas
4061: SET sas.end_date = null
4062: WHERE sas.assignment_id = c_ass_rec.assignment_id
4063: AND sas.end_date = l_final_process_date;
4061: SET sas.end_date = null
4062: WHERE sas.assignment_id = c_ass_rec.assignment_id
4063: AND sas.end_date = l_final_process_date;
4064: --
4065: hr_utility.set_location(l_proc,36);
4066: UPDATE pay_personal_payment_methods_f ppm
4067: SET ppm.effective_end_date = l_effective_end_date
4068: WHERE ppm.assignment_id = c_ass_rec.assignment_id
4069: AND ppm.effective_end_date = l_final_process_date;
4067: SET ppm.effective_end_date = l_effective_end_date
4068: WHERE ppm.assignment_id = c_ass_rec.assignment_id
4069: AND ppm.effective_end_date = l_final_process_date;
4070: --
4071: hr_utility.set_location(l_proc,38);
4072: UPDATE pay_cost_allocations_f pca
4073: SET pca.effective_end_date = l_effective_end_date
4074: WHERE pca.assignment_id = c_ass_rec.assignment_id
4075: AND pca.effective_end_date = l_final_process_date;
4073: SET pca.effective_end_date = l_effective_end_date
4074: WHERE pca.assignment_id = c_ass_rec.assignment_id
4075: AND pca.effective_end_date = l_final_process_date;
4076: --
4077: hr_utility.set_location(l_proc,40);
4078: UPDATE per_spinal_point_placements_f spp
4079: SET spp.effective_end_date = l_effective_end_date
4080: WHERE spp.assignment_id = c_ass_rec.assignment_id
4081: AND spp.effective_end_date = l_final_process_date;
4089: -- customer do not use these TAX tables. Only Customers with HR/CERIDIAN
4090: -- use this TAX tables.
4091:
4092: if hr_general.chk_geocodes_installed ='Y' then
4093: hr_utility.set_location(l_proc,41);
4094: pay_us_update_tax_rec_pkg.reverse_term_emp_tax_records
4095: (c_ass_rec.assignment_id
4096: ,l_final_process_date);
4097:
4099: --
4100: -- SASmith 30-APR-1998
4101: -- Due to date tracking of assignment_budget_values
4102:
4103: hr_utility.set_location(l_proc,45);
4104: --
4105: -- Start of Fix for WWBUG 1408379
4106: --
4107: open c2(c_ass_rec.assignment_id);
4145: end if;
4146: --
4147: -- open up element entries closed down by the termination
4148: --
4149: hr_utility.set_location(l_proc,42);
4150: hrentmnt.maintain_entries_asg(c_ass_rec.assignment_id
4151: ,c_ass_rec.business_group_id
4152: ,'CNCL_TERM'
4153: ,p_actual_termination_date
4187: -- PER_XX_TERMINATION.REVERSE
4188: -- If it does exist then construct an anonymous PL/SQL block to call
4189: -- the procedure.
4190: --
4191: hr_utility.set_location(l_proc,43);
4192: --
4193: l_package_name := 'PER_'||l_leg_code||'_TERMINATION';
4194: l_procedure_name := 'REVERSE';
4195: --
4203: fetch csr_leg_pkg into l_dummy;
4204: --
4205: if csr_leg_pkg%found then
4206: --
4207: hr_utility.set_location(l_proc,44);
4208: --
4209: close csr_leg_pkg;
4210: --
4211: -- Added as part of fix for bug 1858597
4222: end if;
4223: --
4224: -- Update the PTU records.
4225: --
4226: hr_utility.set_location(l_proc,65);
4227: --
4228: if l_old_leaving_reason = 'R' then
4229: --
4230: hr_utility.set_location(l_proc,66);
4226: hr_utility.set_location(l_proc,65);
4227: --
4228: if l_old_leaving_reason = 'R' then
4229: --
4230: hr_utility.set_location(l_proc,66);
4231: --
4232: hr_per_type_usage_internal.cancel_person_type_usage
4233: (p_effective_date => p_actual_termination_date+1
4234: ,p_person_id => p_person_id
4237: --
4238: -- Added for Bug 5507290. Need to cancel EX_EMP PTU record
4239: -- aswell along with RETIREE
4240: --
4241: hr_utility.set_location(l_proc,67);
4242: --
4243: hr_per_type_usage_internal.cancel_person_type_usage
4244: (p_effective_date => p_actual_termination_date+1
4245: ,p_person_id => p_person_id
4247: --
4248: -- End of Bug 5507290.
4249: --
4250: else
4251: hr_utility.set_location('REV_TERM '||l_proc,68);
4252: hr_per_type_usage_internal.cancel_person_type_usage
4253: (p_effective_date => p_actual_termination_date+1
4254: ,p_person_id => p_person_id
4255: ,p_system_person_type => 'EX_EMP');
4256: end if;
4257: --
4258: -- call Hr workflow sync code.
4259: --
4260: hr_utility.set_location(l_proc,70);
4261: --
4262: per_hrwf_synch_cover.per_pds_wf(
4263: p_person_id => p_person_id,
4264: p_date => null,