[Home] [Help]
821: --
822: CLOSE csr_get_ptu_id;
823: --
824: -- # 3690364 - changed application_id from 801 to 800
825: hr_utility.set_message(800,'HR_289548_PEM_EMP_PERSON_ID');
826: hr_utility.raise_error;
827: --
828: END IF;
829: CLOSE csr_get_ptu_id;
822: CLOSE csr_get_ptu_id;
823: --
824: -- # 3690364 - changed application_id from 801 to 800
825: hr_utility.set_message(800,'HR_289548_PEM_EMP_PERSON_ID');
826: hr_utility.raise_error;
827: --
828: END IF;
829: CLOSE csr_get_ptu_id;
830: -- end Bug 3285486
831: if p_cancel_type = 'HIRE' then
832: if p_where = 'BEGIN' then
833: --
834:
835: hr_utility.set_location('cancel_hire_or_apl.pre_cancel_checks',1);
836: --
837: -- Start Bug 3285486. commented the call to hr_person.chk_future_person_type
838: -- added the new call to hr_person_type_usage_info.FutSysPerTypeChgExists
839: /* if hr_person.chk_future_person_type(p_system_person_type
845: ,p_effective_date => p_primary_date
846: ,p_person_id => p_person_id ) THEN
847: -- End Bug 3285486.
848: --
849: hr_utility.set_message(801,'HR_7078_EMP_ENTER_CANCEL_TYPE');
850: hr_utility.raise_error;
851: --
852: end if;
853: --
846: ,p_person_id => p_person_id ) THEN
847: -- End Bug 3285486.
848: --
849: hr_utility.set_message(801,'HR_7078_EMP_ENTER_CANCEL_TYPE');
850: hr_utility.raise_error;
851: --
852: end if;
853: --
854: -- BUG 2964027 STARTS HERE.
857: fetch csr_assign_actions_exist into l_dummy;
858: --
859: if csr_assign_actions_exist%FOUND then
860: close csr_assign_actions_exist;
861: hr_utility.set_message(800,'PER_289566_ASG_ACTIONS_EXISTS');
862: hr_utility.raise_error;
863: end if;
864: --
865: close csr_assign_actions_exist;
858: --
859: if csr_assign_actions_exist%FOUND then
860: close csr_assign_actions_exist;
861: hr_utility.set_message(800,'PER_289566_ASG_ACTIONS_EXISTS');
862: hr_utility.raise_error;
863: end if;
864: --
865: close csr_assign_actions_exist;
866: -- BUG 2964027 ENDS HERE.
864: --
865: close csr_assign_actions_exist;
866: -- BUG 2964027 ENDS HERE.
867: --
868: hr_utility.set_location('cancel_hire_or_apl.pre_cancel_checks',2);
869: if not hr_person.chk_prev_person_type(p_system_person_type
870: ,p_person_id
871: ,p_business_group_id
872: ,p_primary_date) then
870: ,p_person_id
871: ,p_business_group_id
872: ,p_primary_date) then
873: --
874: hr_utility.set_message(801,'HR_7077_NO_CANCEL_HIRE');
875: hr_utility.raise_error;
876: --
877: end if;
878:
871: ,p_business_group_id
872: ,p_primary_date) then
873: --
874: hr_utility.set_message(801,'HR_7077_NO_CANCEL_HIRE');
875: hr_utility.raise_error;
876: --
877: end if;
878:
879: -- check for pay actions.Start Bug 2841901
877: end if;
878:
879: -- check for pay actions.Start Bug 2841901
880:
881: hr_utility.set_location('cancel_hire_or_apl.pre_cancel_checks',3);
882: open csr_payactions;
883: fetch csr_payactions into l_dummy;
884: --
885: if csr_payactions%found then
888: -- Bug# 2989638 Start Here
889: -- Description : Added new message to display proper error message
890: --
891: --
892: hr_utility.set_message(800,'HR_289529_EMP_FUT_PAY_EXIST');
893: --
894: -- Bug# 2989638 End Here
895: --
896: hr_utility.raise_error;
892: hr_utility.set_message(800,'HR_289529_EMP_FUT_PAY_EXIST');
893: --
894: -- Bug# 2989638 End Here
895: --
896: hr_utility.raise_error;
897: end if;
898: --
899: close csr_payactions;
900: -- End Bug 2841901
901: --
902: p_where := 'SUPERVISOR';
903: --
904: end if;
905: hr_utility.set_location('cancel_hire_or_apl.pre_cancel_checks',4);
906: if p_where = 'SUPERVISOR' then
907: --
908: open supervisor;
909: --
909: --
910: fetch supervisor into l_dummy;
911: --
912: if supervisor%FOUND then
913: hr_utility.set_message(801,'HR_EMP_IS_SUPER');
914: close supervisor;
915: return;
916: else
917: close supervisor;
917: close supervisor;
918: end if;
919: p_where:= 'RECRUITER';
920: end if;
921: hr_utility.set_location('cancel_hire_or_apl.pre_cancel_checks',5);
922: if p_where = 'RECRUITER' then
923: --
924: open recruiter;
925: --
925: --
926: fetch recruiter into l_dummy;
927: --
928: if recruiter%FOUND then
929: hr_utility.set_message(801,'HR_EMP_IS_RECRUITER');
930: close recruiter;
931: return;
932: else
933: close recruiter;
933: close recruiter;
934: end if;
935: p_where:= 'EVENT';
936: end if;
937: hr_utility.set_location('cancel_hire_or_apl.pre_cancel_checks',6);
938: if p_where = 'EVENT' then
939: --
940: open reviews_or_events(p_type =>'E');
941: --
941: --
942: fetch reviews_or_events into l_dummy;
943: --
944: if reviews_or_events%FOUND then
945: hr_utility.set_message(801,'HR_EMP_HAS_EVENTS');
946: close reviews_or_events;
947: return;
948: else
949: close reviews_or_events;
950: end if;
951: --
952: p_where := 'INTERVIEW';
953: end if;
954: hr_utility.set_location('cancel_hire_or_apl.pre_cancel_checks',7);
955: if p_where = 'INTERVIEW' then
956: --
957: open interviews;
958: --
958: --
959: fetch interviews into l_dummy;
960: --
961: if interviews%FOUND then
962: hr_utility.set_message(801,'HR_EMP_IS_INTERVIEWER');
963: close interviews;
964: return;
965: else
966: close interviews;
966: close interviews;
967: end if;
968: p_where := 'REVIEW';
969: end if;
970: hr_utility.set_location('cancel_hire_or_apl.pre_cancel_checks',8);
971: if p_where = 'REVIEW' then
972: --
973: open reviews_or_events(p_type =>'I');
974: --
974: --
975: fetch reviews_or_events into l_dummy;
976: --
977: if reviews_or_events%FOUND then
978: hr_utility.set_message(801,'HR_EMP_DUE_REVIEW');
979: close reviews_or_events;
980: return;
981: else
982: close reviews_or_events;
982: close reviews_or_events;
983: end if;
984: p_where := 'VACANCY';
985: end if;
986: hr_utility.set_location('cancel_hire_or_apl.pre_cancel_checks',9);
987: if p_where = 'VACANCY' then
988: --
989: open vacancy;
990: --
990: --
991: fetch vacancy into l_dummy;
992: --
993: if vacancy%FOUND then
994: hr_utility.set_message(801,'HR_EMP_VAC_RECRUITER');
995: close vacancy;
996: return;
997: else
998: close vacancy;
998: close vacancy;
999: end if;
1000: p_where:= 'REQUISITION';
1001: end if;
1002: hr_utility.set_location('cancel_hire_or_apl.pre_cancel_checks',10);
1003: if p_where = 'REQUISITION' then
1004: --
1005: open requisition;
1006: --
1006: --
1007: fetch requisition into l_dummy;
1008: --
1009: if requisition%FOUND then
1010: hr_utility.set_message(801,'HR_EMP_REQUISITIONS');
1011: close requisition;
1012: return;
1013: else
1014: close requisition;
1014: close requisition;
1015: end if;
1016: p_where:= 'BUDGET_VALUE';
1017: end if;
1018: hr_utility.set_location('cancel_hire_or_apl.pre_cancel_checks',11);
1019: if p_where = 'BUDGET_VALUE' then
1020: --
1021: open budget_values;
1022: --
1022: --
1023: fetch budget_values into l_dummy;
1024: --
1025: if budget_values%FOUND then
1026: hr_utility.set_message(801,'HR_EMP_BUDGET_VALUES');
1027: close budget_values;
1028: return;
1029: else
1030: close budget_values;
1030: close budget_values;
1031: end if;
1032: p_where:= 'PAYMENT';
1033: end if;
1034: hr_utility.set_location('cancel_hire_or_apl.pre_cancel_checks',12);
1035: if p_where = 'PAYMENT' then
1036: --
1037: open payment;
1038: --
1038: --
1039: fetch payment into l_dummy;
1040: --
1041: if payment%FOUND then
1042: hr_utility.set_message(801,'HR_EMP_PAYMENT_METHODS');
1043: close payment;
1044: return;
1045: else
1046: close payment;
1056: elsif p_cancel_type = 'APL' then
1057: --
1058: if p_where = 'BEGIN' then
1059: --
1060: hr_utility.set_location('APP1.B_PRE_DEL_CHECK',1);
1061: hr_utility.set_location('cancel_hire_or_apl.pre_cancel_checks',13);
1062: --
1063: -- Start Bug 3285486. commented the call to hr_person.chk_future_person_type
1064: -- added the new call to hr_person_type_usage_info.FutSysPerTypeChgExists
1057: --
1058: if p_where = 'BEGIN' then
1059: --
1060: hr_utility.set_location('APP1.B_PRE_DEL_CHECK',1);
1061: hr_utility.set_location('cancel_hire_or_apl.pre_cancel_checks',13);
1062: --
1063: -- Start Bug 3285486. commented the call to hr_person.chk_future_person_type
1064: -- added the new call to hr_person_type_usage_info.FutSysPerTypeChgExists
1065: --
1073: ,p_person_id => p_person_id ) THEN
1074: --
1075: -- End Bug 3285486
1076: --
1077: hr_utility.set_message(800,'HR_7080_ALL_APP_NO_CANCEL');
1078: hr_utility.raise_error;
1079: --
1080: end if;
1081: --
1074: --
1075: -- End Bug 3285486
1076: --
1077: hr_utility.set_message(800,'HR_7080_ALL_APP_NO_CANCEL');
1078: hr_utility.raise_error;
1079: --
1080: end if;
1081: --
1082: hr_utility.set_location('APP1.B_PRE_DEL_CHECK',2);
1078: hr_utility.raise_error;
1079: --
1080: end if;
1081: --
1082: hr_utility.set_location('APP1.B_PRE_DEL_CHECK',2);
1083: hr_utility.set_location('cancel_hire_or_apl.pre_cancel_checks',14);
1084: --
1085: if not hr_person.chk_prev_person_type(p_system_person_type
1086: ,p_person_id
1079: --
1080: end if;
1081: --
1082: hr_utility.set_location('APP1.B_PRE_DEL_CHECK',2);
1083: hr_utility.set_location('cancel_hire_or_apl.pre_cancel_checks',14);
1084: --
1085: if not hr_person.chk_prev_person_type(p_system_person_type
1086: ,p_person_id
1087: ,p_business_group_id
1086: ,p_person_id
1087: ,p_business_group_id
1088: ,p_primary_date) then
1089: --
1090: hr_utility.set_message(800,'HR_7081_ALL_APP_NO_CANCEL');
1091: hr_utility.raise_error;
1092: --
1093: end if;
1094: --
1087: ,p_business_group_id
1088: ,p_primary_date) then
1089: --
1090: hr_utility.set_message(800,'HR_7081_ALL_APP_NO_CANCEL');
1091: hr_utility.raise_error;
1092: --
1093: end if;
1094: --
1095: hr_utility.set_location('cancel_hire_or_apl.pre_cancel_checks',15);
1091: hr_utility.raise_error;
1092: --
1093: end if;
1094: --
1095: hr_utility.set_location('cancel_hire_or_apl.pre_cancel_checks',15);
1096:
1097: else
1098: app_exception.invalid_argument('cancel_apl',
1099: 'P_WHERE',p_where);
1101: else
1102: app_exception.invalid_argument('cancel_hire_or_apl',
1103: 'P_CANCEL_TYPE',p_cancel_type);
1104: end if;
1105: hr_utility.set_location('cancel_hire_or_apl.pre_cancel_checks',16);
1106: exception
1107: when hr_utility.hr_error then
1108: raise;
1109: when others then
1103: 'P_CANCEL_TYPE',p_cancel_type);
1104: end if;
1105: hr_utility.set_location('cancel_hire_or_apl.pre_cancel_checks',16);
1106: exception
1107: when hr_utility.hr_error then
1108: raise;
1109: when others then
1110: hr_utility.oracle_error(sqlcode);
1111: hr_utility.raise_error;
1106: exception
1107: when hr_utility.hr_error then
1108: raise;
1109: when others then
1110: hr_utility.oracle_error(sqlcode);
1111: hr_utility.raise_error;
1112: end pre_cancel_checks;
1113: --
1114:
1107: when hr_utility.hr_error then
1108: raise;
1109: when others then
1110: hr_utility.oracle_error(sqlcode);
1111: hr_utility.raise_error;
1112: end pre_cancel_checks;
1113: --
1114:
1115: /*===========================================================================*
1150:
1151: l_effective_end_date date := hr_general.end_of_time;
1152: begin
1153:
1154: hr_utility.set_location('Entering Upd_Person_Type_Usage_End_Date',491);
1155:
1156: open csr_upded_person_type_usages(
1157: p_person_id => p_person_id,
1158: p_effective_date => p_effective_date,
1160:
1161: fetch csr_upded_person_type_usages into l_csr_upd_per_type_usages;
1162: if csr_upded_person_type_usages%found then
1163:
1164: hr_utility.set_location('Entering Upd_Person_Type_Usage_End_Date',492);
1165:
1166: update per_person_type_usages_f ptu
1167: set effective_end_date = l_effective_end_date
1168: where ptu.effective_end_date = p_effective_date
1172:
1173: end if;
1174: close csr_upded_person_type_usages;
1175:
1176: hr_utility.set_location('Leaving Upd_Person_Type_Usage_End_Date',493);
1177: end upd_person_type_usage_end_date;
1178:
1179: /*Procedure Added for the bug 6460093*/
1180:
1484: --
1485: return v_dummy;
1486: exception
1487: when no_data_found then
1488: hr_utility.set_message(801,'HR_6346_EMP_ASS_NO_POS');
1489: hr_utility.raise_error;
1490: end;
1491: begin
1492: --
1485: return v_dummy;
1486: exception
1487: when no_data_found then
1488: hr_utility.set_message(801,'HR_6346_EMP_ASS_NO_POS');
1489: hr_utility.raise_error;
1490: end;
1491: begin
1492: --
1493: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',10);
1489: hr_utility.raise_error;
1490: end;
1491: begin
1492: --
1493: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',10);
1494: --
1495: l_business_group_id := p_business_group_id;-- fix for bug 5005157 .
1496: if p_period_of_service_id is null then
1497: l_period_of_service_id:= get_period_of_service(p_person_id => p_person_id
1495: l_business_group_id := p_business_group_id;-- fix for bug 5005157 .
1496: if p_period_of_service_id is null then
1497: l_period_of_service_id:= get_period_of_service(p_person_id => p_person_id
1498: ,p_start_date =>p_date_start);
1499: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',20);
1500: else
1501: l_period_of_service_id := p_period_of_service_id;
1502: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',30);
1503: end if;
1498: ,p_start_date =>p_date_start);
1499: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',20);
1500: else
1501: l_period_of_service_id := p_period_of_service_id;
1502: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',30);
1503: end if;
1504: open supervisor1;
1505: <
1506: loop
1503: end if;
1504: open supervisor1;
1505: <
1506: loop
1507: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',40);
1508: fetch supervisor1 into p_rowid;
1509: exit when supervisor1%NOTFOUND;
1510: --
1511: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',50);
1507: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',40);
1508: fetch supervisor1 into p_rowid;
1509: exit when supervisor1%NOTFOUND;
1510: --
1511: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',50);
1512: update per_all_assignments_f paf
1513: set paf.supervisor_id = NULL
1514: where paf.rowid = p_rowid;
1515: --
1513: set paf.supervisor_id = NULL
1514: where paf.rowid = p_rowid;
1515: --
1516: if sql%notfound then
1517: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
1518: hr_utility.set_message_token('PROCEDURE','do_cancel_hire');
1519: hr_utility.set_message_token('STEP',1);
1520: hr_utility.raise_error;
1521: end if;
1514: where paf.rowid = p_rowid;
1515: --
1516: if sql%notfound then
1517: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
1518: hr_utility.set_message_token('PROCEDURE','do_cancel_hire');
1519: hr_utility.set_message_token('STEP',1);
1520: hr_utility.raise_error;
1521: end if;
1522: end loop supervisor;
1515: --
1516: if sql%notfound then
1517: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
1518: hr_utility.set_message_token('PROCEDURE','do_cancel_hire');
1519: hr_utility.set_message_token('STEP',1);
1520: hr_utility.raise_error;
1521: end if;
1522: end loop supervisor;
1523: close supervisor1;
1516: if sql%notfound then
1517: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
1518: hr_utility.set_message_token('PROCEDURE','do_cancel_hire');
1519: hr_utility.set_message_token('STEP',1);
1520: hr_utility.raise_error;
1521: end if;
1522: end loop supervisor;
1523: close supervisor1;
1524: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',60);
1520: hr_utility.raise_error;
1521: end if;
1522: end loop supervisor;
1523: close supervisor1;
1524: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',60);
1525: open recruiter1;
1526: <
1527: loop
1528: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',70);
1524: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',60);
1525: open recruiter1;
1526: <
1527: loop
1528: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',70);
1529: fetch recruiter1 into p_rowid;
1530: exit when recruiter1%NOTFOUND;
1531: --
1532: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',80);
1528: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',70);
1529: fetch recruiter1 into p_rowid;
1530: exit when recruiter1%NOTFOUND;
1531: --
1532: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',80);
1533: update per_all_assignments_f paf
1534: set paf.recruiter_id = NULL
1535: where paf.rowid = p_rowid;
1536: --
1534: set paf.recruiter_id = NULL
1535: where paf.rowid = p_rowid;
1536: --
1537: if sql%notfound then
1538: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
1539: hr_utility.set_message_token('PROCEDURE','do_cancel_hire');
1540: hr_utility.set_message_token('STEP',2);
1541: hr_utility.raise_error;
1542: end if;
1535: where paf.rowid = p_rowid;
1536: --
1537: if sql%notfound then
1538: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
1539: hr_utility.set_message_token('PROCEDURE','do_cancel_hire');
1540: hr_utility.set_message_token('STEP',2);
1541: hr_utility.raise_error;
1542: end if;
1543: end loop recruiter;
1536: --
1537: if sql%notfound then
1538: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
1539: hr_utility.set_message_token('PROCEDURE','do_cancel_hire');
1540: hr_utility.set_message_token('STEP',2);
1541: hr_utility.raise_error;
1542: end if;
1543: end loop recruiter;
1544: close recruiter1;
1537: if sql%notfound then
1538: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
1539: hr_utility.set_message_token('PROCEDURE','do_cancel_hire');
1540: hr_utility.set_message_token('STEP',2);
1541: hr_utility.raise_error;
1542: end if;
1543: end loop recruiter;
1544: close recruiter1;
1545: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',90);
1541: hr_utility.raise_error;
1542: end if;
1543: end loop recruiter;
1544: close recruiter1;
1545: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',90);
1546: open events;
1547: <
1548: loop
1549: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',100);
1545: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',90);
1546: open events;
1547: <
1548: loop
1549: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',100);
1550: fetch events into p_rowid;
1551: exit when events%NOTFOUND;
1552: delete from per_bookings pb
1553: where pb.rowid = p_rowid;
1551: exit when events%NOTFOUND;
1552: delete from per_bookings pb
1553: where pb.rowid = p_rowid;
1554: --
1555: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',110);
1556: if sql%notfound then
1557: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
1558: hr_utility.set_message_token('PROCEDURE','do_cancel_hire');
1559: hr_utility.set_message_token('STEP',3);
1553: where pb.rowid = p_rowid;
1554: --
1555: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',110);
1556: if sql%notfound then
1557: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
1558: hr_utility.set_message_token('PROCEDURE','do_cancel_hire');
1559: hr_utility.set_message_token('STEP',3);
1560: hr_utility.raise_error;
1561: end if;
1554: --
1555: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',110);
1556: if sql%notfound then
1557: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
1558: hr_utility.set_message_token('PROCEDURE','do_cancel_hire');
1559: hr_utility.set_message_token('STEP',3);
1560: hr_utility.raise_error;
1561: end if;
1562: end loop event;
1555: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',110);
1556: if sql%notfound then
1557: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
1558: hr_utility.set_message_token('PROCEDURE','do_cancel_hire');
1559: hr_utility.set_message_token('STEP',3);
1560: hr_utility.raise_error;
1561: end if;
1562: end loop event;
1563: close events;
1556: if sql%notfound then
1557: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
1558: hr_utility.set_message_token('PROCEDURE','do_cancel_hire');
1559: hr_utility.set_message_token('STEP',3);
1560: hr_utility.raise_error;
1561: end if;
1562: end loop event;
1563: close events;
1564: --
1561: end if;
1562: end loop event;
1563: close events;
1564: --
1565: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',120);
1566: --
1567: open vacancies;
1568: <
1569: loop
1566: --
1567: open vacancies;
1568: <
1569: loop
1570: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',130);
1571: fetch vacancies into p_rowid;
1572: exit when vacancies%NOTFOUND;
1573: --
1574: update per_all_vacancies pv
1574: update per_all_vacancies pv
1575: set pv.recruiter_id = NULL
1576: where pv.rowid = p_rowid;
1577: if sql%notfound then
1578: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
1579: hr_utility.set_message_token('PROCEDURE','do_cancel_hire');
1580: hr_utility.set_message_token('STEP',4);
1581: hr_utility.raise_error;
1582: end if;
1575: set pv.recruiter_id = NULL
1576: where pv.rowid = p_rowid;
1577: if sql%notfound then
1578: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
1579: hr_utility.set_message_token('PROCEDURE','do_cancel_hire');
1580: hr_utility.set_message_token('STEP',4);
1581: hr_utility.raise_error;
1582: end if;
1583: end loop vacancy;
1576: where pv.rowid = p_rowid;
1577: if sql%notfound then
1578: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
1579: hr_utility.set_message_token('PROCEDURE','do_cancel_hire');
1580: hr_utility.set_message_token('STEP',4);
1581: hr_utility.raise_error;
1582: end if;
1583: end loop vacancy;
1584: close vacancies;
1577: if sql%notfound then
1578: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
1579: hr_utility.set_message_token('PROCEDURE','do_cancel_hire');
1580: hr_utility.set_message_token('STEP',4);
1581: hr_utility.raise_error;
1582: end if;
1583: end loop vacancy;
1584: close vacancies;
1585: --
1582: end if;
1583: end loop vacancy;
1584: close vacancies;
1585: --
1586: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',140);
1587: open requisitions1;
1588: <
1589: loop
1590: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',150);
1586: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',140);
1587: open requisitions1;
1588: <
1589: loop
1590: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',150);
1591: fetch requisitions1 into p_rowid;
1592: exit when requisitions1%NOTFOUND;
1593: update per_requisitions pr
1594: set pr.person_id = NULL
1593: update per_requisitions pr
1594: set pr.person_id = NULL
1595: where pr.rowid = p_rowid;
1596: if sql%notfound then
1597: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
1598: hr_utility.set_message_token('PROCEDURE','do_cancel_hire');
1599: hr_utility.set_message_token('STEP',5);
1600: hr_utility.raise_error;
1601: end if;
1594: set pr.person_id = NULL
1595: where pr.rowid = p_rowid;
1596: if sql%notfound then
1597: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
1598: hr_utility.set_message_token('PROCEDURE','do_cancel_hire');
1599: hr_utility.set_message_token('STEP',5);
1600: hr_utility.raise_error;
1601: end if;
1602: end loop requisition;
1595: where pr.rowid = p_rowid;
1596: if sql%notfound then
1597: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
1598: hr_utility.set_message_token('PROCEDURE','do_cancel_hire');
1599: hr_utility.set_message_token('STEP',5);
1600: hr_utility.raise_error;
1601: end if;
1602: end loop requisition;
1603: close requisitions1;
1596: if sql%notfound then
1597: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
1598: hr_utility.set_message_token('PROCEDURE','do_cancel_hire');
1599: hr_utility.set_message_token('STEP',5);
1600: hr_utility.raise_error;
1601: end if;
1602: end loop requisition;
1603: close requisitions1;
1604: --
1601: end if;
1602: end loop requisition;
1603: close requisitions1;
1604: --
1605: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',160);
1606: open absenses1;
1607: <
1608: loop
1609: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',170);
1605: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',160);
1606: open absenses1;
1607: <
1608: loop
1609: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',170);
1610: fetch absenses1 into p_rowid;
1611: exit when absenses1%NOTFOUND;
1612: delete from per_absence_attendances paa
1613: where paa.rowid = p_rowid;
1611: exit when absenses1%NOTFOUND;
1612: delete from per_absence_attendances paa
1613: where paa.rowid = p_rowid;
1614: if sql%notfound then
1615: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
1616: hr_utility.set_message_token('PROCEDURE','do_cancel_hire');
1617: hr_utility.set_message_token('STEP',6);
1618: hr_utility.raise_error;
1619: end if;
1612: delete from per_absence_attendances paa
1613: where paa.rowid = p_rowid;
1614: if sql%notfound then
1615: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
1616: hr_utility.set_message_token('PROCEDURE','do_cancel_hire');
1617: hr_utility.set_message_token('STEP',6);
1618: hr_utility.raise_error;
1619: end if;
1620: end loop absense;
1613: where paa.rowid = p_rowid;
1614: if sql%notfound then
1615: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
1616: hr_utility.set_message_token('PROCEDURE','do_cancel_hire');
1617: hr_utility.set_message_token('STEP',6);
1618: hr_utility.raise_error;
1619: end if;
1620: end loop absense;
1621: close absenses1;
1614: if sql%notfound then
1615: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
1616: hr_utility.set_message_token('PROCEDURE','do_cancel_hire');
1617: hr_utility.set_message_token('STEP',6);
1618: hr_utility.raise_error;
1619: end if;
1620: end loop absense;
1621: close absenses1;
1622: --
1619: end if;
1620: end loop absense;
1621: close absenses1;
1622: --
1623: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',180);
1624: open budget_values1;
1625: <
1626: loop
1627: fetch budget_values1 into p_rowid;
1628: exit when budget_values1%NOTFOUND;
1629: delete from per_assignment_budget_values_f pab
1630: where pab.rowid = p_rowid;
1631: if sql%notfound then
1632: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
1633: hr_utility.set_message_token('PROCEDURE','do_cancel_hire');
1634: hr_utility.set_message_token('STEP',7);
1635: hr_utility.raise_error;
1636: end if;
1629: delete from per_assignment_budget_values_f pab
1630: where pab.rowid = p_rowid;
1631: if sql%notfound then
1632: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
1633: hr_utility.set_message_token('PROCEDURE','do_cancel_hire');
1634: hr_utility.set_message_token('STEP',7);
1635: hr_utility.raise_error;
1636: end if;
1637: end loop budget_value;
1630: where pab.rowid = p_rowid;
1631: if sql%notfound then
1632: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
1633: hr_utility.set_message_token('PROCEDURE','do_cancel_hire');
1634: hr_utility.set_message_token('STEP',7);
1635: hr_utility.raise_error;
1636: end if;
1637: end loop budget_value;
1638: close budget_values1;
1631: if sql%notfound then
1632: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
1633: hr_utility.set_message_token('PROCEDURE','do_cancel_hire');
1634: hr_utility.set_message_token('STEP',7);
1635: hr_utility.raise_error;
1636: end if;
1637: end loop budget_value;
1638: close budget_values1;
1639: --
1636: end if;
1637: end loop budget_value;
1638: close budget_values1;
1639: --
1640: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',190);
1641: open payment1;
1642: <
1643: loop
1644: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',200);
1640: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',190);
1641: open payment1;
1642: <
1643: loop
1644: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',200);
1645: fetch payment1 into p_rowid;
1646: exit when payment1%NOTFOUND;
1647: delete from pay_personal_payment_methods ppm
1648: where ppm.rowid = p_rowid;
1646: exit when payment1%NOTFOUND;
1647: delete from pay_personal_payment_methods ppm
1648: where ppm.rowid = p_rowid;
1649: if sql%notfound then
1650: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
1651: hr_utility.set_message_token('PROCEDURE','do_cancel_hire');
1652: hr_utility.set_message_token('STEP',8);
1653: hr_utility.raise_error;
1654: end if;
1647: delete from pay_personal_payment_methods ppm
1648: where ppm.rowid = p_rowid;
1649: if sql%notfound then
1650: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
1651: hr_utility.set_message_token('PROCEDURE','do_cancel_hire');
1652: hr_utility.set_message_token('STEP',8);
1653: hr_utility.raise_error;
1654: end if;
1655: end loop pay;
1648: where ppm.rowid = p_rowid;
1649: if sql%notfound then
1650: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
1651: hr_utility.set_message_token('PROCEDURE','do_cancel_hire');
1652: hr_utility.set_message_token('STEP',8);
1653: hr_utility.raise_error;
1654: end if;
1655: end loop pay;
1656: close payment1;
1649: if sql%notfound then
1650: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
1651: hr_utility.set_message_token('PROCEDURE','do_cancel_hire');
1652: hr_utility.set_message_token('STEP',8);
1653: hr_utility.raise_error;
1654: end if;
1655: end loop pay;
1656: close payment1;
1657: --
1654: end if;
1655: end loop pay;
1656: close payment1;
1657: --
1658: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',210);
1659: open comments1;
1660: <
1661: loop
1662: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',220);
1658: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',210);
1659: open comments1;
1660: <
1661: loop
1662: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',220);
1663: fetch comments1 into p_rowid;
1664: exit when comments1%NOTFOUND;
1665: delete from hr_comments h
1666: where h.rowid = p_rowid;
1664: exit when comments1%NOTFOUND;
1665: delete from hr_comments h
1666: where h.rowid = p_rowid;
1667: if sql%notfound then
1668: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
1669: hr_utility.set_message_token('PROCEDURE','do_cancel_hire');
1670: hr_utility.set_message_token('STEP',9);
1671: hr_utility.raise_error;
1672: end if;
1665: delete from hr_comments h
1666: where h.rowid = p_rowid;
1667: if sql%notfound then
1668: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
1669: hr_utility.set_message_token('PROCEDURE','do_cancel_hire');
1670: hr_utility.set_message_token('STEP',9);
1671: hr_utility.raise_error;
1672: end if;
1673: end loop comment;
1666: where h.rowid = p_rowid;
1667: if sql%notfound then
1668: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
1669: hr_utility.set_message_token('PROCEDURE','do_cancel_hire');
1670: hr_utility.set_message_token('STEP',9);
1671: hr_utility.raise_error;
1672: end if;
1673: end loop comment;
1674: close comments1;
1667: if sql%notfound then
1668: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
1669: hr_utility.set_message_token('PROCEDURE','do_cancel_hire');
1670: hr_utility.set_message_token('STEP',9);
1671: hr_utility.raise_error;
1672: end if;
1673: end loop comment;
1674: close comments1;
1675: --
1672: end if;
1673: end loop comment;
1674: close comments1;
1675: --
1676: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',230);
1677: open assignments;
1678: <
1679: loop
1680: -- VT #438579 03/05/79 added assignment_id
1677: open assignments;
1678: <
1679: loop
1680: -- VT #438579 03/05/79 added assignment_id
1681: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',240);
1682: fetch assignments into p_rowid,l_assignment_id,l_asg_status_type_id; -- Bug 3564129
1683: exit when assignments%NOTFOUND;
1684: -- VT #438579 03/05/97 added delete
1685: delete from per_spinal_point_placements_f spp
1741: -- End of fix 3564129
1742: delete from per_all_assignments_f paf
1743: where paf.rowid = p_rowid;
1744: if sql%notfound then
1745: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
1746: hr_utility.set_message_token('PROCEDURE','do_cancel_hire');
1747: hr_utility.set_message_token('STEP',10);
1748: hr_utility.raise_error;
1749: end if;
1742: delete from per_all_assignments_f paf
1743: where paf.rowid = p_rowid;
1744: if sql%notfound then
1745: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
1746: hr_utility.set_message_token('PROCEDURE','do_cancel_hire');
1747: hr_utility.set_message_token('STEP',10);
1748: hr_utility.raise_error;
1749: end if;
1750: end loop assignment;
1743: where paf.rowid = p_rowid;
1744: if sql%notfound then
1745: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
1746: hr_utility.set_message_token('PROCEDURE','do_cancel_hire');
1747: hr_utility.set_message_token('STEP',10);
1748: hr_utility.raise_error;
1749: end if;
1750: end loop assignment;
1751: close assignments;
1744: if sql%notfound then
1745: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
1746: hr_utility.set_message_token('PROCEDURE','do_cancel_hire');
1747: hr_utility.set_message_token('STEP',10);
1748: hr_utility.raise_error;
1749: end if;
1750: end loop assignment;
1751: close assignments;
1752: --
1749: end if;
1750: end loop assignment;
1751: close assignments;
1752: --
1753: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',250);
1754: open applications;
1755: <
1756: loop
1757: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',260);
1753: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',250);
1754: open applications;
1755: <
1756: loop
1757: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',260);
1758: fetch applications into p_rowid;
1759: exit when applications%NOTFOUND;
1760: update per_applications pap
1761: set pap.date_end = NULL
1760: update per_applications pap
1761: set pap.date_end = NULL
1762: where pap.rowid = p_rowid;
1763: if sql%notfound then
1764: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
1765: hr_utility.set_message_token('PROCEDURE','do_cancel_hire');
1766: hr_utility.set_message_token('STEP',11);
1767: hr_utility.raise_error;
1768: end if;
1761: set pap.date_end = NULL
1762: where pap.rowid = p_rowid;
1763: if sql%notfound then
1764: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
1765: hr_utility.set_message_token('PROCEDURE','do_cancel_hire');
1766: hr_utility.set_message_token('STEP',11);
1767: hr_utility.raise_error;
1768: end if;
1769: end loop application;
1762: where pap.rowid = p_rowid;
1763: if sql%notfound then
1764: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
1765: hr_utility.set_message_token('PROCEDURE','do_cancel_hire');
1766: hr_utility.set_message_token('STEP',11);
1767: hr_utility.raise_error;
1768: end if;
1769: end loop application;
1770: close applications;
1763: if sql%notfound then
1764: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
1765: hr_utility.set_message_token('PROCEDURE','do_cancel_hire');
1766: hr_utility.set_message_token('STEP',11);
1767: hr_utility.raise_error;
1768: end if;
1769: end loop application;
1770: close applications;
1771: --
1768: end if;
1769: end loop application;
1770: close applications;
1771: --
1772: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',270);
1773: open ass1;
1774: <
1775: loop
1776: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',280);
1772: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',270);
1773: open ass1;
1774: <
1775: loop
1776: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',280);
1777: fetch ass1 into p_assignment_id,p_start_date,p_rowid;
1778: exit when ass1%NOTFOUND;
1779: update per_all_assignments_f paf
1780: set paf.effective_end_date = p_end_of_time
1792: --
1793: end loop entries;
1794: close ass1;
1795: --
1796: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',290);
1797: l_back2back := per_periods_of_service_pkg_v2.IsBackToBackContract
1798: ( p_person_id => p_person_id, p_hire_date_of_current_pds => p_date_start);
1799:
1800: open period;
1799:
1800: open period;
1801: <
1802: loop
1803: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',300);
1804: fetch period into p_rowid;
1805: exit when period%notfound;
1806: delete from per_periods_of_service
1807: where rowid = p_rowid;
1805: exit when period%notfound;
1806: delete from per_periods_of_service
1807: where rowid = p_rowid;
1808: if sql%notfound then
1809: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
1810: hr_utility.set_message_token('PROCEDURE','do_cancel_hire');
1811: hr_utility.set_message_token('STEP',11);
1812: hr_utility.raise_error;
1813: end if;
1806: delete from per_periods_of_service
1807: where rowid = p_rowid;
1808: if sql%notfound then
1809: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
1810: hr_utility.set_message_token('PROCEDURE','do_cancel_hire');
1811: hr_utility.set_message_token('STEP',11);
1812: hr_utility.raise_error;
1813: end if;
1814: end loop service;
1807: where rowid = p_rowid;
1808: if sql%notfound then
1809: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
1810: hr_utility.set_message_token('PROCEDURE','do_cancel_hire');
1811: hr_utility.set_message_token('STEP',11);
1812: hr_utility.raise_error;
1813: end if;
1814: end loop service;
1815: if period%rowcount <1 then
1808: if sql%notfound then
1809: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
1810: hr_utility.set_message_token('PROCEDURE','do_cancel_hire');
1811: hr_utility.set_message_token('STEP',11);
1812: hr_utility.raise_error;
1813: end if;
1814: end loop service;
1815: if period%rowcount <1 then
1816: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
1812: hr_utility.raise_error;
1813: end if;
1814: end loop service;
1815: if period%rowcount <1 then
1816: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
1817: hr_utility.set_message_token('PROCEDURE','do_cancel_hire');
1818: hr_utility.set_message_token('STEP',12);
1819: hr_utility.raise_error;
1820: end if;
1813: end if;
1814: end loop service;
1815: if period%rowcount <1 then
1816: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
1817: hr_utility.set_message_token('PROCEDURE','do_cancel_hire');
1818: hr_utility.set_message_token('STEP',12);
1819: hr_utility.raise_error;
1820: end if;
1821: close period;
1814: end loop service;
1815: if period%rowcount <1 then
1816: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
1817: hr_utility.set_message_token('PROCEDURE','do_cancel_hire');
1818: hr_utility.set_message_token('STEP',12);
1819: hr_utility.raise_error;
1820: end if;
1821: close period;
1822: --
1815: if period%rowcount <1 then
1816: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
1817: hr_utility.set_message_token('PROCEDURE','do_cancel_hire');
1818: hr_utility.set_message_token('STEP',12);
1819: hr_utility.raise_error;
1820: end if;
1821: close period;
1822: --
1823: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',310);
1819: hr_utility.raise_error;
1820: end if;
1821: close period;
1822: --
1823: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',310);
1824: -- 3194314
1825: open csr_is_cwk(p_date_start);
1826: fetch csr_is_cwk into l_pp_id, l_cwk_date_start;
1827: close csr_is_cwk;
1824: -- 3194314
1825: open csr_is_cwk(p_date_start);
1826: fetch csr_is_cwk into l_pp_id, l_cwk_date_start;
1827: close csr_is_cwk;
1828: hr_utility.trace('CWK pp_id = '||l_pp_id);
1829: --
1830: -- 3848352 start
1831: l_apl_flag := NULL;
1832: open csr_apl_ptu_id;
1834: if csr_apl_ptu_id%found then
1835: l_apl_flag := 'Y';
1836: end if;
1837: close csr_apl_ptu_id;
1838: hr_utility.trace('l_apl_flag = '||l_apl_flag);
1839: -- 3848352 end
1840:
1841: -- <<
1842: open person;
1841: -- <<
1842: open person;
1843: <
1844: loop
1845: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',320);
1846: fetch person into p_rowid, l_person_end_date;
1847: exit when person%notfound;
1848: if l_back2back
1849: then
1854: where business_group_id = p_business_group_id
1855: and system_person_type = 'EX_EMP'
1856: and default_flag = 'Y';
1857:
1858: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',322);
1859:
1860: update per_people_f
1861: set person_type_id = l_person_type_id,
1862: effective_start_date = p_date_start, -- in case DT updates exist
1866: where rowid = p_rowid;
1867: --
1868: else -- #1998140
1869: --
1870: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',323);
1871:
1872: delete from per_people_f
1873: where rowid = p_rowid;
1874: --
1898: and default_flag = 'Y';
1899: end if;
1900: -- 3848352 end
1901:
1902: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',324);
1903:
1904: --
1905: -- 3847884 Changed from p_start_date to p_date_start
1906: --
1917: where rowid = p_rowid;
1918: --
1919: else -- #1998140
1920: --
1921: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',325);
1922:
1923: delete from per_people_f
1924: where rowid = p_rowid;
1925: --
1926: end if;
1927:
1928: -- <<
1929: else
1930: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',330);
1931: delete from per_people_f
1932: where rowid = p_rowid;
1933: end if;
1934: --
1931: delete from per_people_f
1932: where rowid = p_rowid;
1933: end if;
1934: --
1935: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',340);
1936: -- if sql%ROWCOUNT <1 then
1937: if sql%notfound then
1938: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
1939: hr_utility.set_message_token('PROCEDURE','do_cancel_hire');
1934: --
1935: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',340);
1936: -- if sql%ROWCOUNT <1 then
1937: if sql%notfound then
1938: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
1939: hr_utility.set_message_token('PROCEDURE','do_cancel_hire');
1940: hr_utility.set_message_token('STEP',13);
1941: hr_utility.raise_error;
1942: end if;
1935: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',340);
1936: -- if sql%ROWCOUNT <1 then
1937: if sql%notfound then
1938: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
1939: hr_utility.set_message_token('PROCEDURE','do_cancel_hire');
1940: hr_utility.set_message_token('STEP',13);
1941: hr_utility.raise_error;
1942: end if;
1943: end loop per;
1936: -- if sql%ROWCOUNT <1 then
1937: if sql%notfound then
1938: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
1939: hr_utility.set_message_token('PROCEDURE','do_cancel_hire');
1940: hr_utility.set_message_token('STEP',13);
1941: hr_utility.raise_error;
1942: end if;
1943: end loop per;
1944: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',350);
1937: if sql%notfound then
1938: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
1939: hr_utility.set_message_token('PROCEDURE','do_cancel_hire');
1940: hr_utility.set_message_token('STEP',13);
1941: hr_utility.raise_error;
1942: end if;
1943: end loop per;
1944: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',350);
1945: if person%rowcount <1 then
1940: hr_utility.set_message_token('STEP',13);
1941: hr_utility.raise_error;
1942: end if;
1943: end loop per;
1944: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',350);
1945: if person%rowcount <1 then
1946: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
1947: hr_utility.set_message_token('PROCEDURE','do_cancel_hire');
1948: hr_utility.set_message_token('STEP',14);
1942: end if;
1943: end loop per;
1944: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',350);
1945: if person%rowcount <1 then
1946: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
1947: hr_utility.set_message_token('PROCEDURE','do_cancel_hire');
1948: hr_utility.set_message_token('STEP',14);
1949: hr_utility.raise_error;
1950: end if;
1943: end loop per;
1944: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',350);
1945: if person%rowcount <1 then
1946: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
1947: hr_utility.set_message_token('PROCEDURE','do_cancel_hire');
1948: hr_utility.set_message_token('STEP',14);
1949: hr_utility.raise_error;
1950: end if;
1951: close person;
1944: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',350);
1945: if person%rowcount <1 then
1946: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
1947: hr_utility.set_message_token('PROCEDURE','do_cancel_hire');
1948: hr_utility.set_message_token('STEP',14);
1949: hr_utility.raise_error;
1950: end if;
1951: close person;
1952: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',360);
1945: if person%rowcount <1 then
1946: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
1947: hr_utility.set_message_token('PROCEDURE','do_cancel_hire');
1948: hr_utility.set_message_token('STEP',14);
1949: hr_utility.raise_error;
1950: end if;
1951: close person;
1952: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',360);
1953: -- 3194314
1948: hr_utility.set_message_token('STEP',14);
1949: hr_utility.raise_error;
1950: end if;
1951: close person;
1952: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',360);
1953: -- 3194314
1954: -- this should not get exected when b2b cwk/emp
1955: if l_pp_id is null then
1956: open new_person;
1955: if l_pp_id is null then
1956: open new_person;
1957: <
1958: loop
1959: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',370);
1960: fetch new_person into p_rowid;
1961: exit when new_person%notfound;
1962: if NOT l_back2back
1963: then
1960: fetch new_person into p_rowid;
1961: exit when new_person%notfound;
1962: if NOT l_back2back
1963: then
1964: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',380);
1965: update per_people_f
1966: set effective_end_date = p_end_of_time
1967: where rowid = p_rowid;
1968: end if;
1966: set effective_end_date = p_end_of_time
1967: where rowid = p_rowid;
1968: end if;
1969: --
1970: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',390);
1971: if sql%ROWCOUNT <1 then
1972: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
1973: hr_utility.set_message_token('PROCEDURE','do_cancel_hire');
1974: hr_utility.set_message_token('STEP',15);
1968: end if;
1969: --
1970: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',390);
1971: if sql%ROWCOUNT <1 then
1972: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
1973: hr_utility.set_message_token('PROCEDURE','do_cancel_hire');
1974: hr_utility.set_message_token('STEP',15);
1975: hr_utility.raise_error;
1976: end if;
1969: --
1970: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',390);
1971: if sql%ROWCOUNT <1 then
1972: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
1973: hr_utility.set_message_token('PROCEDURE','do_cancel_hire');
1974: hr_utility.set_message_token('STEP',15);
1975: hr_utility.raise_error;
1976: end if;
1977: end loop new_per;
1970: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',390);
1971: if sql%ROWCOUNT <1 then
1972: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
1973: hr_utility.set_message_token('PROCEDURE','do_cancel_hire');
1974: hr_utility.set_message_token('STEP',15);
1975: hr_utility.raise_error;
1976: end if;
1977: end loop new_per;
1978: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',400);
1971: if sql%ROWCOUNT <1 then
1972: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
1973: hr_utility.set_message_token('PROCEDURE','do_cancel_hire');
1974: hr_utility.set_message_token('STEP',15);
1975: hr_utility.raise_error;
1976: end if;
1977: end loop new_per;
1978: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',400);
1979: if new_person%rowcount <1 then
1974: hr_utility.set_message_token('STEP',15);
1975: hr_utility.raise_error;
1976: end if;
1977: end loop new_per;
1978: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',400);
1979: if new_person%rowcount <1 then
1980: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
1981: hr_utility.set_message_token('PROCEDURE','do_cancel_hire');
1982: hr_utility.set_message_token('STEP',16);
1976: end if;
1977: end loop new_per;
1978: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',400);
1979: if new_person%rowcount <1 then
1980: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
1981: hr_utility.set_message_token('PROCEDURE','do_cancel_hire');
1982: hr_utility.set_message_token('STEP',16);
1983: hr_utility.raise_error;
1984: end if;
1977: end loop new_per;
1978: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',400);
1979: if new_person%rowcount <1 then
1980: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
1981: hr_utility.set_message_token('PROCEDURE','do_cancel_hire');
1982: hr_utility.set_message_token('STEP',16);
1983: hr_utility.raise_error;
1984: end if;
1985: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',410);
1978: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',400);
1979: if new_person%rowcount <1 then
1980: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
1981: hr_utility.set_message_token('PROCEDURE','do_cancel_hire');
1982: hr_utility.set_message_token('STEP',16);
1983: hr_utility.raise_error;
1984: end if;
1985: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',410);
1986: close new_person;
1979: if new_person%rowcount <1 then
1980: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
1981: hr_utility.set_message_token('PROCEDURE','do_cancel_hire');
1982: hr_utility.set_message_token('STEP',16);
1983: hr_utility.raise_error;
1984: end if;
1985: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',410);
1986: close new_person;
1987:
1981: hr_utility.set_message_token('PROCEDURE','do_cancel_hire');
1982: hr_utility.set_message_token('STEP',16);
1983: hr_utility.raise_error;
1984: end if;
1985: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',410);
1986: close new_person;
1987:
1988: end if; -- 3194314 is b2b?
1989: --
1986: close new_person;
1987:
1988: end if; -- 3194314 is b2b?
1989: --
1990: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',420);
1991: -- 115.1 Change by M Bocutt
1992: --
1993: -- If we have an EMP PTU record that started on the hire date
1994: -- then delete it.
1994: -- then delete it.
1995: --
1996: -- PTU : Code added
1997:
1998: hr_utility.set_location('cancel_hire_or_apl.p_date_start = '||to_char(p_date_start,'DD/MM/YYYY'),420);
1999: hr_utility.set_location('cancel_hire_or_apl.p_person_id = '||to_char(p_person_id),420);
2000:
2001: if l_pp_id is not null then -- 3194314
2002:
1995: --
1996: -- PTU : Code added
1997:
1998: hr_utility.set_location('cancel_hire_or_apl.p_date_start = '||to_char(p_date_start,'DD/MM/YYYY'),420);
1999: hr_utility.set_location('cancel_hire_or_apl.p_person_id = '||to_char(p_person_id),420);
2000:
2001: if l_pp_id is not null then -- 3194314
2002:
2003: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',424);
1999: hr_utility.set_location('cancel_hire_or_apl.p_person_id = '||to_char(p_person_id),420);
2000:
2001: if l_pp_id is not null then -- 3194314
2002:
2003: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',424);
2004:
2005: open csr_emp_ptu_id;
2006: fetch csr_emp_ptu_id into l_emp_ptu_id;
2007: if csr_emp_ptu_id%FOUND and l_emp_ptu_id is not null then
2005: open csr_emp_ptu_id;
2006: fetch csr_emp_ptu_id into l_emp_ptu_id;
2007: if csr_emp_ptu_id%FOUND and l_emp_ptu_id is not null then
2008:
2009: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',425);
2010: --fix for bug 6671352 starts here.
2011: /*hr_per_type_usage_internal.maintain_person_type_usage
2012: (p_effective_date => p_date_start
2013: ,p_person_id => p_person_id
2028: open csr_apl_ptu_id;
2029: fetch csr_apl_ptu_id into l_apl_ptu_id;
2030: if csr_apl_ptu_id%FOUND and l_apl_ptu_id is not null then
2031:
2032: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',425);
2033: hr_per_type_usage_internal.maintain_person_type_usage
2034: (p_effective_date => p_date_start -1
2035: ,p_person_id => p_person_id
2036: ,p_person_type_id => l_apl_ptu_id
2060: ,p_system_person_type => 'OTHER');
2061:
2062: -- Change for the bug 6899072 ends here
2063:
2064: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',430);
2065: --
2066: --5450847 start : Need to maintain APL record if any. Delete the EX_APL
2067: -- record and update EED of APL record to EOTime.
2068: --
2069: open csr_apl_ptu_id;
2070: fetch csr_apl_ptu_id into l_apl_ptu_id;
2071: if csr_apl_ptu_id%FOUND and l_apl_ptu_id is not null then
2072:
2073: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',432);
2074: hr_per_type_usage_internal.maintain_person_type_usage
2075: (p_effective_date => p_date_start -1
2076: ,p_person_id => p_person_id
2077: ,p_person_type_id => l_apl_ptu_id
2078: ,p_datetrack_delete_mode => 'DELETE_NEXT_CHANGE'
2079: );
2080: end if;
2081: close csr_apl_ptu_id;
2082: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',433);
2083: --
2084: -- 5450847 end
2085: --
2086:
2087: end if;
2088:
2089: if l_back2back then
2090:
2091: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',435);
2092:
2093: hr_per_type_usage_internal.maintain_person_type_usage
2094: (p_effective_date => p_date_start
2095: ,p_person_id => p_person_id
2114: per_cancel_hire_or_apl_pkg.update_person_list(p_person_id => p_person_id);
2115: --
2116: for asg_rec in c_assignments loop
2117: --
2118: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire', 501);
2119: --
2120: hr_security_internal.add_to_person_list(
2121: p_effective_date => asg_rec.effective_start_date
2122: ,p_assignment_id => asg_rec.assignment_id);
2120: hr_security_internal.add_to_person_list(
2121: p_effective_date => asg_rec.effective_start_date
2122: ,p_assignment_id => asg_rec.assignment_id);
2123: --
2124: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire', 502);
2125: --
2126: end loop;
2127: --
2128: end do_cancel_hire;
2365: exit when events_or_interviews%NOTFOUND;
2366: delete from per_events pe
2367: where pe.event_id = l_pk_id;
2368: if sql%notfound then
2369: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2370: hr_utility.set_message_token('PROCEDURE','do_cancel_appl');
2371: hr_utility.set_message_token('STEP',2);
2372: hr_utility.raise_error;
2373: end if;
2366: delete from per_events pe
2367: where pe.event_id = l_pk_id;
2368: if sql%notfound then
2369: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2370: hr_utility.set_message_token('PROCEDURE','do_cancel_appl');
2371: hr_utility.set_message_token('STEP',2);
2372: hr_utility.raise_error;
2373: end if;
2374: end loop event;
2367: where pe.event_id = l_pk_id;
2368: if sql%notfound then
2369: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2370: hr_utility.set_message_token('PROCEDURE','do_cancel_appl');
2371: hr_utility.set_message_token('STEP',2);
2372: hr_utility.raise_error;
2373: end if;
2374: end loop event;
2375: close events_or_interviews;
2368: if sql%notfound then
2369: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2370: hr_utility.set_message_token('PROCEDURE','do_cancel_appl');
2371: hr_utility.set_message_token('STEP',2);
2372: hr_utility.raise_error;
2373: end if;
2374: end loop event;
2375: close events_or_interviews;
2376: --
2382: exit when budget_values2%NOTFOUND;
2383: delete from per_assignment_budget_values_f pab
2384: where pab.rowid = l_rowid;
2385: if sql%notfound then
2386: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2387: hr_utility.set_message_token('PROCEDURE','do_cancel_appl');
2388: hr_utility.set_message_token('STEP',3);
2389: hr_utility.raise_error;
2390: end if;
2383: delete from per_assignment_budget_values_f pab
2384: where pab.rowid = l_rowid;
2385: if sql%notfound then
2386: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2387: hr_utility.set_message_token('PROCEDURE','do_cancel_appl');
2388: hr_utility.set_message_token('STEP',3);
2389: hr_utility.raise_error;
2390: end if;
2391: end loop budget_val;
2384: where pab.rowid = l_rowid;
2385: if sql%notfound then
2386: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2387: hr_utility.set_message_token('PROCEDURE','do_cancel_appl');
2388: hr_utility.set_message_token('STEP',3);
2389: hr_utility.raise_error;
2390: end if;
2391: end loop budget_val;
2392: close budget_values2;
2385: if sql%notfound then
2386: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2387: hr_utility.set_message_token('PROCEDURE','do_cancel_appl');
2388: hr_utility.set_message_token('STEP',3);
2389: hr_utility.raise_error;
2390: end if;
2391: end loop budget_val;
2392: close budget_values2;
2393: --
2399: exit when comments2%NOTFOUND;
2400: delete from hr_comments h
2401: where h.comment_id = l_pk_id;
2402: if sql%notfound then
2403: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2404: hr_utility.set_message_token('PROCEDURE','do_cancel_appl');
2405: hr_utility.set_message_token('STEP',4);
2406: hr_utility.raise_error;
2407: end if;
2400: delete from hr_comments h
2401: where h.comment_id = l_pk_id;
2402: if sql%notfound then
2403: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2404: hr_utility.set_message_token('PROCEDURE','do_cancel_appl');
2405: hr_utility.set_message_token('STEP',4);
2406: hr_utility.raise_error;
2407: end if;
2408: end loop comments;
2401: where h.comment_id = l_pk_id;
2402: if sql%notfound then
2403: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2404: hr_utility.set_message_token('PROCEDURE','do_cancel_appl');
2405: hr_utility.set_message_token('STEP',4);
2406: hr_utility.raise_error;
2407: end if;
2408: end loop comments;
2409: close comments2;
2402: if sql%notfound then
2403: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2404: hr_utility.set_message_token('PROCEDURE','do_cancel_appl');
2405: hr_utility.set_message_token('STEP',4);
2406: hr_utility.raise_error;
2407: end if;
2408: end loop comments;
2409: close comments2;
2410: --
2416: exit when letters2%NOTFOUND;
2417: delete from per_letter_request_lines plrl
2418: where plrl.letter_request_line_id = l_pk_id;
2419: if sql%notfound then
2420: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2421: hr_utility.set_message_token('PROCEDURE','do_cancel_appl');
2422: hr_utility.set_message_token('STEP',5);
2423: hr_utility.raise_error;
2424: end if;
2417: delete from per_letter_request_lines plrl
2418: where plrl.letter_request_line_id = l_pk_id;
2419: if sql%notfound then
2420: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2421: hr_utility.set_message_token('PROCEDURE','do_cancel_appl');
2422: hr_utility.set_message_token('STEP',5);
2423: hr_utility.raise_error;
2424: end if;
2425: end loop letters;
2418: where plrl.letter_request_line_id = l_pk_id;
2419: if sql%notfound then
2420: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2421: hr_utility.set_message_token('PROCEDURE','do_cancel_appl');
2422: hr_utility.set_message_token('STEP',5);
2423: hr_utility.raise_error;
2424: end if;
2425: end loop letters;
2426: close letters2;
2419: if sql%notfound then
2420: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2421: hr_utility.set_message_token('PROCEDURE','do_cancel_appl');
2422: hr_utility.set_message_token('STEP',5);
2423: hr_utility.raise_error;
2424: end if;
2425: end loop letters;
2426: close letters2;
2427: --
2483: exit when asg_prop_answer2%NOTFOUND;
2484: delete from per_assign_proposal_answers papa
2485: where papa.rowid = l_rowid;
2486: if sql%notfound then
2487: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2488: hr_utility.set_message_token('PROCEDURE','do_cancel_appl');
2489: hr_utility.set_message_token('STEP',6);
2490: hr_utility.raise_error;
2491: end if;
2484: delete from per_assign_proposal_answers papa
2485: where papa.rowid = l_rowid;
2486: if sql%notfound then
2487: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2488: hr_utility.set_message_token('PROCEDURE','do_cancel_appl');
2489: hr_utility.set_message_token('STEP',6);
2490: hr_utility.raise_error;
2491: end if;
2492: end loop asg_prop_answer;
2485: where papa.rowid = l_rowid;
2486: if sql%notfound then
2487: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2488: hr_utility.set_message_token('PROCEDURE','do_cancel_appl');
2489: hr_utility.set_message_token('STEP',6);
2490: hr_utility.raise_error;
2491: end if;
2492: end loop asg_prop_answer;
2493: close asg_prop_answer2;
2486: if sql%notfound then
2487: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2488: hr_utility.set_message_token('PROCEDURE','do_cancel_appl');
2489: hr_utility.set_message_token('STEP',6);
2490: hr_utility.raise_error;
2491: end if;
2492: end loop asg_prop_answer;
2493: close asg_prop_answer2;
2494: --
2524: exit when bookings2%NOTFOUND;
2525: delete from per_bookings pb
2526: where pb.booking_id = l_pk_id;
2527: if sql%notfound then
2528: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2529: hr_utility.set_message_token('PROCEDURE','do_cancel_appl');
2530: hr_utility.set_message_token('STEP',7);
2531: hr_utility.raise_error;
2532: end if;
2525: delete from per_bookings pb
2526: where pb.booking_id = l_pk_id;
2527: if sql%notfound then
2528: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2529: hr_utility.set_message_token('PROCEDURE','do_cancel_appl');
2530: hr_utility.set_message_token('STEP',7);
2531: hr_utility.raise_error;
2532: end if;
2533: end loop bookings;
2526: where pb.booking_id = l_pk_id;
2527: if sql%notfound then
2528: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2529: hr_utility.set_message_token('PROCEDURE','do_cancel_appl');
2530: hr_utility.set_message_token('STEP',7);
2531: hr_utility.raise_error;
2532: end if;
2533: end loop bookings;
2534: close bookings2;
2527: if sql%notfound then
2528: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2529: hr_utility.set_message_token('PROCEDURE','do_cancel_appl');
2530: hr_utility.set_message_token('STEP',7);
2531: hr_utility.raise_error;
2532: end if;
2533: end loop bookings;
2534: close bookings2;
2535: --
2541: exit when person_apl_comments2%NOTFOUND;
2542: delete from hr_comments h
2543: where h.comment_id = l_pk_id;
2544: if sql%notfound then
2545: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2546: hr_utility.set_message_token('PROCEDURE','do_cancel_appl');
2547: hr_utility.set_message_token('STEP',8);
2548: hr_utility.raise_error;
2549: end if;
2542: delete from hr_comments h
2543: where h.comment_id = l_pk_id;
2544: if sql%notfound then
2545: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2546: hr_utility.set_message_token('PROCEDURE','do_cancel_appl');
2547: hr_utility.set_message_token('STEP',8);
2548: hr_utility.raise_error;
2549: end if;
2550: end loop person_apl_comments;
2543: where h.comment_id = l_pk_id;
2544: if sql%notfound then
2545: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2546: hr_utility.set_message_token('PROCEDURE','do_cancel_appl');
2547: hr_utility.set_message_token('STEP',8);
2548: hr_utility.raise_error;
2549: end if;
2550: end loop person_apl_comments;
2551: close person_apl_comments2;
2544: if sql%notfound then
2545: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2546: hr_utility.set_message_token('PROCEDURE','do_cancel_appl');
2547: hr_utility.set_message_token('STEP',8);
2548: hr_utility.raise_error;
2549: end if;
2550: end loop person_apl_comments;
2551: close person_apl_comments2;
2552: --
2560: exit when assignments%NOTFOUND;
2561: delete from per_assignments_f paf
2562: where paf.rowid = l_rowid;
2563: if sql%notfound then
2564: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2565: hr_utility.set_message_token('PROCEDURE','do_cancel_appl');
2566: hr_utility.set_message_token('STEP',9);
2567: hr_utility.raise_error;
2568: end if;
2561: delete from per_assignments_f paf
2562: where paf.rowid = l_rowid;
2563: if sql%notfound then
2564: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2565: hr_utility.set_message_token('PROCEDURE','do_cancel_appl');
2566: hr_utility.set_message_token('STEP',9);
2567: hr_utility.raise_error;
2568: end if;
2569: end loop assign;
2562: where paf.rowid = l_rowid;
2563: if sql%notfound then
2564: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2565: hr_utility.set_message_token('PROCEDURE','do_cancel_appl');
2566: hr_utility.set_message_token('STEP',9);
2567: hr_utility.raise_error;
2568: end if;
2569: end loop assign;
2570: close assignments;
2563: if sql%notfound then
2564: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2565: hr_utility.set_message_token('PROCEDURE','do_cancel_appl');
2566: hr_utility.set_message_token('STEP',9);
2567: hr_utility.raise_error;
2568: end if;
2569: end loop assign;
2570: close assignments;
2571: --
2577: exit when applications%NOTFOUND;
2578: delete from per_applications pap
2579: where pap.rowid = l_rowid;
2580: if sql%notfound then
2581: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2582: hr_utility.set_message_token('PROCEDURE','do_cancel_appl');
2583: hr_utility.set_message_token('STEP',10);
2584: hr_utility.raise_error;
2585: end if;
2578: delete from per_applications pap
2579: where pap.rowid = l_rowid;
2580: if sql%notfound then
2581: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2582: hr_utility.set_message_token('PROCEDURE','do_cancel_appl');
2583: hr_utility.set_message_token('STEP',10);
2584: hr_utility.raise_error;
2585: end if;
2586: end loop application;
2579: where pap.rowid = l_rowid;
2580: if sql%notfound then
2581: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2582: hr_utility.set_message_token('PROCEDURE','do_cancel_appl');
2583: hr_utility.set_message_token('STEP',10);
2584: hr_utility.raise_error;
2585: end if;
2586: end loop application;
2587: close applications;
2580: if sql%notfound then
2581: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2582: hr_utility.set_message_token('PROCEDURE','do_cancel_appl');
2583: hr_utility.set_message_token('STEP',10);
2584: hr_utility.raise_error;
2585: end if;
2586: end loop application;
2587: close applications;
2588: --
2607: exit when people%notfound;
2608: delete from per_people_f
2609: where rowid = l_rowid;
2610: if sql%notfound then
2611: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2612: hr_utility.set_message_token('PROCEDURE','do_cancel_appl');
2613: hr_utility.set_message_token('STEP',11);
2614: hr_utility.raise_error;
2615: end if;
2608: delete from per_people_f
2609: where rowid = l_rowid;
2610: if sql%notfound then
2611: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2612: hr_utility.set_message_token('PROCEDURE','do_cancel_appl');
2613: hr_utility.set_message_token('STEP',11);
2614: hr_utility.raise_error;
2615: end if;
2616: end loop per;
2609: where rowid = l_rowid;
2610: if sql%notfound then
2611: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2612: hr_utility.set_message_token('PROCEDURE','do_cancel_appl');
2613: hr_utility.set_message_token('STEP',11);
2614: hr_utility.raise_error;
2615: end if;
2616: end loop per;
2617: if people%rowcount <1 then
2610: if sql%notfound then
2611: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2612: hr_utility.set_message_token('PROCEDURE','do_cancel_appl');
2613: hr_utility.set_message_token('STEP',11);
2614: hr_utility.raise_error;
2615: end if;
2616: end loop per;
2617: if people%rowcount <1 then
2618: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2614: hr_utility.raise_error;
2615: end if;
2616: end loop per;
2617: if people%rowcount <1 then
2618: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2619: hr_utility.set_message_token('PROCEDURE','do_cancel_appl');
2620: hr_utility.set_message_token('STEP',12);
2621: hr_utility.raise_error;
2622: end if;
2615: end if;
2616: end loop per;
2617: if people%rowcount <1 then
2618: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2619: hr_utility.set_message_token('PROCEDURE','do_cancel_appl');
2620: hr_utility.set_message_token('STEP',12);
2621: hr_utility.raise_error;
2622: end if;
2623: close people;
2616: end loop per;
2617: if people%rowcount <1 then
2618: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2619: hr_utility.set_message_token('PROCEDURE','do_cancel_appl');
2620: hr_utility.set_message_token('STEP',12);
2621: hr_utility.raise_error;
2622: end if;
2623: close people;
2624: --
2617: if people%rowcount <1 then
2618: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2619: hr_utility.set_message_token('PROCEDURE','do_cancel_appl');
2620: hr_utility.set_message_token('STEP',12);
2621: hr_utility.raise_error;
2622: end if;
2623: close people;
2624: --
2625: --
2632: set effective_end_date = p_end_of_time
2633: where rowid = l_rowid;
2634: --
2635: if sql%ROWCOUNT <1 then
2636: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2637: hr_utility.set_message_token('PROCEDURE','do_cancel_appl');
2638: hr_utility.set_message_token('STEP',13);
2639: hr_utility.raise_error;
2640: end if;
2633: where rowid = l_rowid;
2634: --
2635: if sql%ROWCOUNT <1 then
2636: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2637: hr_utility.set_message_token('PROCEDURE','do_cancel_appl');
2638: hr_utility.set_message_token('STEP',13);
2639: hr_utility.raise_error;
2640: end if;
2641: end loop new_per;
2634: --
2635: if sql%ROWCOUNT <1 then
2636: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2637: hr_utility.set_message_token('PROCEDURE','do_cancel_appl');
2638: hr_utility.set_message_token('STEP',13);
2639: hr_utility.raise_error;
2640: end if;
2641: end loop new_per;
2642: if new_people%rowcount <1 then
2635: if sql%ROWCOUNT <1 then
2636: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2637: hr_utility.set_message_token('PROCEDURE','do_cancel_appl');
2638: hr_utility.set_message_token('STEP',13);
2639: hr_utility.raise_error;
2640: end if;
2641: end loop new_per;
2642: if new_people%rowcount <1 then
2643: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2639: hr_utility.raise_error;
2640: end if;
2641: end loop new_per;
2642: if new_people%rowcount <1 then
2643: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2644: hr_utility.set_message_token('PROCEDURE','do_cancel_appl');
2645: hr_utility.set_message_token('STEP',14);
2646: hr_utility.raise_error;
2647: end if;
2640: end if;
2641: end loop new_per;
2642: if new_people%rowcount <1 then
2643: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2644: hr_utility.set_message_token('PROCEDURE','do_cancel_appl');
2645: hr_utility.set_message_token('STEP',14);
2646: hr_utility.raise_error;
2647: end if;
2648: close new_people;
2641: end loop new_per;
2642: if new_people%rowcount <1 then
2643: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2644: hr_utility.set_message_token('PROCEDURE','do_cancel_appl');
2645: hr_utility.set_message_token('STEP',14);
2646: hr_utility.raise_error;
2647: end if;
2648: close new_people;
2649: -- Bug 4095559 Starts
2642: if new_people%rowcount <1 then
2643: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2644: hr_utility.set_message_token('PROCEDURE','do_cancel_appl');
2645: hr_utility.set_message_token('STEP',14);
2646: hr_utility.raise_error;
2647: end if;
2648: close new_people;
2649: -- Bug 4095559 Starts
2650: -- Desc: : Execute the new logic, if it is B2B
2664: update per_people_f
2665: set PERSON_TYPE_ID=l_person_type_id
2666: where rowid = l_rowid;
2667: if sql%notfound then
2668: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2669: hr_utility.set_message_token('PROCEDURE','do_cancel_appl');
2670: hr_utility.set_message_token('STEP',15);
2671: hr_utility.raise_error;
2672: end if;
2665: set PERSON_TYPE_ID=l_person_type_id
2666: where rowid = l_rowid;
2667: if sql%notfound then
2668: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2669: hr_utility.set_message_token('PROCEDURE','do_cancel_appl');
2670: hr_utility.set_message_token('STEP',15);
2671: hr_utility.raise_error;
2672: end if;
2673: end loop per_b2b;
2666: where rowid = l_rowid;
2667: if sql%notfound then
2668: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2669: hr_utility.set_message_token('PROCEDURE','do_cancel_appl');
2670: hr_utility.set_message_token('STEP',15);
2671: hr_utility.raise_error;
2672: end if;
2673: end loop per_b2b;
2674: /* if people_b2b%rowcount <1 then
2667: if sql%notfound then
2668: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2669: hr_utility.set_message_token('PROCEDURE','do_cancel_appl');
2670: hr_utility.set_message_token('STEP',15);
2671: hr_utility.raise_error;
2672: end if;
2673: end loop per_b2b;
2674: /* if people_b2b%rowcount <1 then
2675: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2671: hr_utility.raise_error;
2672: end if;
2673: end loop per_b2b;
2674: /* if people_b2b%rowcount <1 then
2675: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2676: hr_utility.set_message_token('PROCEDURE','do_cancel_appl');
2677: hr_utility.set_message_token('STEP',16);
2678: hr_utility.raise_error;
2679: end if;*/
2672: end if;
2673: end loop per_b2b;
2674: /* if people_b2b%rowcount <1 then
2675: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2676: hr_utility.set_message_token('PROCEDURE','do_cancel_appl');
2677: hr_utility.set_message_token('STEP',16);
2678: hr_utility.raise_error;
2679: end if;*/
2680: close people_b2b;
2673: end loop per_b2b;
2674: /* if people_b2b%rowcount <1 then
2675: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2676: hr_utility.set_message_token('PROCEDURE','do_cancel_appl');
2677: hr_utility.set_message_token('STEP',16);
2678: hr_utility.raise_error;
2679: end if;*/
2680: close people_b2b;
2681:
2674: /* if people_b2b%rowcount <1 then
2675: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2676: hr_utility.set_message_token('PROCEDURE','do_cancel_appl');
2677: hr_utility.set_message_token('STEP',16);
2678: hr_utility.raise_error;
2679: end if;*/
2680: close people_b2b;
2681:
2682: open new_people_b2b;
2714: end if;
2715: end if;
2716: --
2717: if sql%notfound then
2718: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2719: hr_utility.set_message_token('PROCEDURE','do_cancel_appl');
2720: hr_utility.set_message_token('STEP',17);
2721: hr_utility.raise_error;
2722: end if;
2715: end if;
2716: --
2717: if sql%notfound then
2718: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2719: hr_utility.set_message_token('PROCEDURE','do_cancel_appl');
2720: hr_utility.set_message_token('STEP',17);
2721: hr_utility.raise_error;
2722: end if;
2723: end loop new_per_b2b;
2716: --
2717: if sql%notfound then
2718: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2719: hr_utility.set_message_token('PROCEDURE','do_cancel_appl');
2720: hr_utility.set_message_token('STEP',17);
2721: hr_utility.raise_error;
2722: end if;
2723: end loop new_per_b2b;
2724: if new_people_b2b%rowcount <1 then
2717: if sql%notfound then
2718: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2719: hr_utility.set_message_token('PROCEDURE','do_cancel_appl');
2720: hr_utility.set_message_token('STEP',17);
2721: hr_utility.raise_error;
2722: end if;
2723: end loop new_per_b2b;
2724: if new_people_b2b%rowcount <1 then
2725: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2721: hr_utility.raise_error;
2722: end if;
2723: end loop new_per_b2b;
2724: if new_people_b2b%rowcount <1 then
2725: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2726: hr_utility.set_message_token('PROCEDURE','do_cancel_appl');
2727: hr_utility.set_message_token('STEP',18);
2728: hr_utility.raise_error;
2729: end if;
2722: end if;
2723: end loop new_per_b2b;
2724: if new_people_b2b%rowcount <1 then
2725: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2726: hr_utility.set_message_token('PROCEDURE','do_cancel_appl');
2727: hr_utility.set_message_token('STEP',18);
2728: hr_utility.raise_error;
2729: end if;
2730: close new_people_b2b;
2723: end loop new_per_b2b;
2724: if new_people_b2b%rowcount <1 then
2725: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2726: hr_utility.set_message_token('PROCEDURE','do_cancel_appl');
2727: hr_utility.set_message_token('STEP',18);
2728: hr_utility.raise_error;
2729: end if;
2730: close new_people_b2b;
2731:
2724: if new_people_b2b%rowcount <1 then
2725: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2726: hr_utility.set_message_token('PROCEDURE','do_cancel_appl');
2727: hr_utility.set_message_token('STEP',18);
2728: hr_utility.raise_error;
2729: end if;
2730: close new_people_b2b;
2731:
2732: end if;
2750: ,p_effective_start_date => l_start_date
2751: ,p_effective_end_date => l_end_date );
2752: end loop person_type_usages;
2753: if person_type_usages2%rowcount <1 then
2754: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2755: hr_utility.set_message_token('PROCEDURE','do_cancel_appl');
2756: hr_utility.set_message_token('STEP',15);
2757: hr_utility.raise_error;
2758: end if;
2751: ,p_effective_end_date => l_end_date );
2752: end loop person_type_usages;
2753: if person_type_usages2%rowcount <1 then
2754: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2755: hr_utility.set_message_token('PROCEDURE','do_cancel_appl');
2756: hr_utility.set_message_token('STEP',15);
2757: hr_utility.raise_error;
2758: end if;
2759: close person_type_usages2;
2752: end loop person_type_usages;
2753: if person_type_usages2%rowcount <1 then
2754: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2755: hr_utility.set_message_token('PROCEDURE','do_cancel_appl');
2756: hr_utility.set_message_token('STEP',15);
2757: hr_utility.raise_error;
2758: end if;
2759: close person_type_usages2;
2760: */
2753: if person_type_usages2%rowcount <1 then
2754: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2755: hr_utility.set_message_token('PROCEDURE','do_cancel_appl');
2756: hr_utility.set_message_token('STEP',15);
2757: hr_utility.raise_error;
2758: end if;
2759: close person_type_usages2;
2760: */
2761: -- Bug 4095559 Starts
2959: AND pt.system_person_type = 'CWK';
2960: --
2961: BEGIN
2962: --
2963: hr_utility.set_location('Entering : '||l_proc,10);
2964: --
2965: l_effective_date := TRUNC(p_effective_date);
2966: --
2967: -- Retrieve the person type usage ID for the CWK,
2973: IF csr_get_ptu_id%NOTFOUND THEN
2974: --
2975: CLOSE csr_get_ptu_id;
2976: --
2977: hr_utility.set_message(801,'HR_289751_CWK_SYS_PER_TYPE_ERR');
2978: hr_utility.raise_error;
2979: --
2980: END IF;
2981: --
2974: --
2975: CLOSE csr_get_ptu_id;
2976: --
2977: hr_utility.set_message(801,'HR_289751_CWK_SYS_PER_TYPE_ERR');
2978: hr_utility.raise_error;
2979: --
2980: END IF;
2981: --
2982: hr_utility.set_location(l_proc,15);
2978: hr_utility.raise_error;
2979: --
2980: END IF;
2981: --
2982: hr_utility.set_location(l_proc,15);
2983: --
2984: CLOSE csr_get_ptu_id;
2985: --
2986: -- Check that the person in a CWK employee
2992: IF csr_chk_person_is_cwk%NOTFOUND THEN
2993: --
2994: CLOSE csr_chk_person_is_cwk;
2995: --
2996: hr_utility.set_message(801,'HR_289747_MUST_BE_CWK');
2997: hr_utility.raise_error;
2998: --
2999: END IF;
3000: --
2993: --
2994: CLOSE csr_chk_person_is_cwk;
2995: --
2996: hr_utility.set_message(801,'HR_289747_MUST_BE_CWK');
2997: hr_utility.raise_error;
2998: --
2999: END IF;
3000: --
3001: CLOSE csr_chk_person_is_cwk;
2999: END IF;
3000: --
3001: CLOSE csr_chk_person_is_cwk;
3002: --
3003: hr_utility.set_location(l_proc,20);
3004: --
3005: -- Check that the CWK Employee has only ONE assignment.
3006: -- If they do not then raise an error asking the user to delete
3007: -- the person.
3012: IF csr_multi_asg_check%ROWCOUNT > 1 THEN
3013: --
3014: CLOSE csr_multi_asg_check;
3015: --
3016: hr_utility.set_message(801,'HR_289748_MULTIPLE_CWK_ASG');
3017: hr_utility.raise_error;
3018: --
3019: END IF;
3020: --
3013: --
3014: CLOSE csr_multi_asg_check;
3015: --
3016: hr_utility.set_message(801,'HR_289748_MULTIPLE_CWK_ASG');
3017: hr_utility.raise_error;
3018: --
3019: END IF;
3020: --
3021: CLOSE csr_multi_asg_check;
3019: END IF;
3020: --
3021: CLOSE csr_multi_asg_check;
3022: --
3023: hr_utility.set_location(l_proc,30);
3024: --
3025: -- Check that the CWK has no future person type changes.
3026: --
3027: IF hr_person_type_usage_info.FutSysPerTypeChgExists -- 3194314: adding person id
3028: (p_person_type_usage_id => l_person_type_usage_id
3029: ,p_effective_date => p_date_start
3030: ,p_person_id => p_person_id ) THEN
3031: -- #3684683 modified the application id to 800
3032: hr_utility.set_message(800,'HR_289749_CWK_FUTURE_PT_CHGE');
3033: hr_utility.raise_error;
3034:
3035: END IF;
3036: --
3029: ,p_effective_date => p_date_start
3030: ,p_person_id => p_person_id ) THEN
3031: -- #3684683 modified the application id to 800
3032: hr_utility.set_message(800,'HR_289749_CWK_FUTURE_PT_CHGE');
3033: hr_utility.raise_error;
3034:
3035: END IF;
3036: --
3037: hr_utility.set_location(l_proc,40);
3033: hr_utility.raise_error;
3034:
3035: END IF;
3036: --
3037: hr_utility.set_location(l_proc,40);
3038: --
3039: -- Check that the CWK has at least one previous person type.
3040: --
3041: IF NOT hr_person.chk_prev_person_type
3043: ,p_person_id => p_person_id
3044: ,p_business_group_id => p_business_group_id
3045: ,p_effective_start_date => l_effective_start_date) THEN
3046: --
3047: hr_utility.set_message(800,'HR_289750_NO_PREVIOUS_CWK');
3048: hr_utility.raise_error;
3049: --
3050: END IF;
3051: --
3044: ,p_business_group_id => p_business_group_id
3045: ,p_effective_start_date => l_effective_start_date) THEN
3046: --
3047: hr_utility.set_message(800,'HR_289750_NO_PREVIOUS_CWK');
3048: hr_utility.raise_error;
3049: --
3050: END IF;
3051: --
3052: hr_utility.set_location(l_proc,50);
3048: hr_utility.raise_error;
3049: --
3050: END IF;
3051: --
3052: hr_utility.set_location(l_proc,50);
3053: --
3054: -- Check to see if the CWK is a supervisor for another worker.
3055: -- If they are set the supervisor_warning out parameter
3056: -- to TRUE.
3070: END IF;
3071: --
3072: CLOSE csr_supervisor;
3073: --
3074: hr_utility.set_location(l_proc,60);
3075: --
3076: -- Check to see if the CWK is a recruiter for another worker.
3077: -- If they are set the recruiter_warning out parameter
3078: -- to TRUE.
3091: END IF;
3092: --
3093: CLOSE csr_recruiter;
3094: --
3095: hr_utility.set_location(l_proc,70);
3096: --
3097: -- Check to see if the CWK has any events.
3098: -- If they do set the event_warning out parameter
3099: -- to TRUE.
3112: END IF;
3113: --
3114: CLOSE csr_reviews_or_events;
3115: --
3116: hr_utility.set_location(l_proc,80);
3117: --
3118: -- Check to see if the CWK has any interviews.
3119: -- If they do set the interview_warning out parameter
3120: -- to TRUE.
3133: END IF;
3134: --
3135: CLOSE csr_interviews;
3136: --
3137: hr_utility.set_location(l_proc,90);
3138: --
3139: OPEN csr_reviews_or_events(p_type =>'I');
3140: FETCH csr_reviews_or_events into l_dummy;
3141: --
3150: END IF;
3151: --
3152: CLOSE csr_reviews_or_events;
3153: --
3154: hr_utility.set_location(l_proc,100);
3155: --
3156: -- Check to see if the CWK is the recruiter for any vacancies.
3157: -- If they do set the vacancy_warning out parameter
3158: -- to TRUE.
3171: END IF;
3172: --
3173: CLOSE csr_vacancy;
3174: --
3175: hr_utility.set_location(l_proc,110);
3176: --
3177: -- Check to see if the CWK has any requisitions.
3178: -- If they do set the requisition_warning out parameter
3179: -- to TRUE.
3192: END IF;
3193: --
3194: CLOSE csr_requisition;
3195: --
3196: hr_utility.set_location(l_proc,120);
3197: --
3198: -- Check to see if the CWK has any budget values.
3199: -- If they do set the budget_warning out parameter
3200: -- to TRUE.
3213: END IF;
3214: --
3215: CLOSE csr_budget_values;
3216: --
3217: hr_utility.set_location(l_proc,130);
3218: --
3219: OPEN csr_payment;
3220: FETCH csr_payment INTO l_dummy;
3221: --
3234: END IF;
3235: --
3236: CLOSE csr_payment;
3237: --
3238: hr_utility.set_location(l_proc,130);
3239: --
3240: EXCEPTION
3241: --
3242: WHEN hr_utility.hr_error THEN
3238: hr_utility.set_location(l_proc,130);
3239: --
3240: EXCEPTION
3241: --
3242: WHEN hr_utility.hr_error THEN
3243: raise;
3244: --
3245: WHEN OTHERS THEN
3246: --
3243: raise;
3244: --
3245: WHEN OTHERS THEN
3246: --
3247: hr_utility.oracle_error(sqlcode);
3248: hr_utility.raise_error;
3249: --
3250: END pre_cancel_placement_checks;
3251: --
3244: --
3245: WHEN OTHERS THEN
3246: --
3247: hr_utility.oracle_error(sqlcode);
3248: hr_utility.raise_error;
3249: --
3250: END pre_cancel_placement_checks;
3251: --
3252: /*===========================================================================*
3518: and paf.assignment_type <> 'B'; -- Added For Bug # 6630290
3519:
3520: BEGIN
3521: --
3522: hr_utility.set_location('Entering : '||l_proc,10);
3523: hr_utility.set_location(l_proc||' date_start = '||p_date_start,11);
3524: hr_utility.set_location(l_proc||' eff date = '||p_effective_date,12);
3525: --
3526: l_effective_date := TRUNC(p_effective_date);
3519:
3520: BEGIN
3521: --
3522: hr_utility.set_location('Entering : '||l_proc,10);
3523: hr_utility.set_location(l_proc||' date_start = '||p_date_start,11);
3524: hr_utility.set_location(l_proc||' eff date = '||p_effective_date,12);
3525: --
3526: l_effective_date := TRUNC(p_effective_date);
3527: l_date_start := TRUNC(p_effective_date);
3520: BEGIN
3521: --
3522: hr_utility.set_location('Entering : '||l_proc,10);
3523: hr_utility.set_location(l_proc||' date_start = '||p_date_start,11);
3524: hr_utility.set_location(l_proc||' eff date = '||p_effective_date,12);
3525: --
3526: l_effective_date := TRUNC(p_effective_date);
3527: l_date_start := TRUNC(p_effective_date);
3528: --
3525: --
3526: l_effective_date := TRUNC(p_effective_date);
3527: l_date_start := TRUNC(p_effective_date);
3528: --
3529: hr_utility.set_location(l_proc,20);
3530: --
3531: FOR supervisor_rec IN csr_supervisor LOOP
3532: --
3533: hr_utility.set_location(l_proc,60);
3529: hr_utility.set_location(l_proc,20);
3530: --
3531: FOR supervisor_rec IN csr_supervisor LOOP
3532: --
3533: hr_utility.set_location(l_proc,60);
3534: --
3535: UPDATE per_all_assignments_f paf
3536: SET paf.supervisor_id = NULL
3537: WHERE paf.rowid = supervisor_rec.rowid;
3537: WHERE paf.rowid = supervisor_rec.rowid;
3538: --
3539: IF sql%notfound THEN
3540: --
3541: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
3542: hr_utility.set_message_token('PROCEDURE','do_cancel_placement');
3543: hr_utility.set_message_token('STEP',1);
3544: hr_utility.raise_error;
3545: --
3538: --
3539: IF sql%notfound THEN
3540: --
3541: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
3542: hr_utility.set_message_token('PROCEDURE','do_cancel_placement');
3543: hr_utility.set_message_token('STEP',1);
3544: hr_utility.raise_error;
3545: --
3546: END IF;
3539: IF sql%notfound THEN
3540: --
3541: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
3542: hr_utility.set_message_token('PROCEDURE','do_cancel_placement');
3543: hr_utility.set_message_token('STEP',1);
3544: hr_utility.raise_error;
3545: --
3546: END IF;
3547: --
3540: --
3541: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
3542: hr_utility.set_message_token('PROCEDURE','do_cancel_placement');
3543: hr_utility.set_message_token('STEP',1);
3544: hr_utility.raise_error;
3545: --
3546: END IF;
3547: --
3548: END LOOP;
3546: END IF;
3547: --
3548: END LOOP;
3549: --
3550: hr_utility.set_location(l_proc,70);
3551: --
3552: FOR recruiter_rec IN csr_recruiter LOOP
3553: --
3554: hr_utility.set_location(l_proc,80);
3550: hr_utility.set_location(l_proc,70);
3551: --
3552: FOR recruiter_rec IN csr_recruiter LOOP
3553: --
3554: hr_utility.set_location(l_proc,80);
3555: --
3556: UPDATE per_all_assignments_f paf
3557: SET paf.recruiter_id = NULL
3558: WHERE paf.rowid = recruiter_rec.rowid;
3558: WHERE paf.rowid = recruiter_rec.rowid;
3559: --
3560: IF SQL%NOTFOUND THEN
3561: --
3562: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
3563: hr_utility.set_message_token('PROCEDURE','do_cancel_placement');
3564: hr_utility.set_message_token('STEP',2);
3565: hr_utility.raise_error;
3566: --
3559: --
3560: IF SQL%NOTFOUND THEN
3561: --
3562: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
3563: hr_utility.set_message_token('PROCEDURE','do_cancel_placement');
3564: hr_utility.set_message_token('STEP',2);
3565: hr_utility.raise_error;
3566: --
3567: END IF;
3560: IF SQL%NOTFOUND THEN
3561: --
3562: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
3563: hr_utility.set_message_token('PROCEDURE','do_cancel_placement');
3564: hr_utility.set_message_token('STEP',2);
3565: hr_utility.raise_error;
3566: --
3567: END IF;
3568: --
3561: --
3562: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
3563: hr_utility.set_message_token('PROCEDURE','do_cancel_placement');
3564: hr_utility.set_message_token('STEP',2);
3565: hr_utility.raise_error;
3566: --
3567: END IF;
3568: --
3569: END LOOP;
3567: END IF;
3568: --
3569: END LOOP;
3570: --
3571: hr_utility.set_location(l_proc,90);
3572: --
3573: FOR vacancies_rec IN csr_vacancies LOOP
3574: --
3575: hr_utility.set_location(l_proc,100);
3571: hr_utility.set_location(l_proc,90);
3572: --
3573: FOR vacancies_rec IN csr_vacancies LOOP
3574: --
3575: hr_utility.set_location(l_proc,100);
3576: --
3577: UPDATE per_all_vacancies pv
3578: SET pv.recruiter_id = NULL
3579: WHERE pv.rowid = vacancies_rec.rowid;
3579: WHERE pv.rowid = vacancies_rec.rowid;
3580: --
3581: IF SQL%NOTFOUND THEN
3582: --
3583: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
3584: hr_utility.set_message_token('PROCEDURE','do_cancel_placement');
3585: hr_utility.set_message_token('STEP',3);
3586: hr_utility.raise_error;
3587: --
3580: --
3581: IF SQL%NOTFOUND THEN
3582: --
3583: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
3584: hr_utility.set_message_token('PROCEDURE','do_cancel_placement');
3585: hr_utility.set_message_token('STEP',3);
3586: hr_utility.raise_error;
3587: --
3588: END IF;
3581: IF SQL%NOTFOUND THEN
3582: --
3583: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
3584: hr_utility.set_message_token('PROCEDURE','do_cancel_placement');
3585: hr_utility.set_message_token('STEP',3);
3586: hr_utility.raise_error;
3587: --
3588: END IF;
3589: --
3582: --
3583: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
3584: hr_utility.set_message_token('PROCEDURE','do_cancel_placement');
3585: hr_utility.set_message_token('STEP',3);
3586: hr_utility.raise_error;
3587: --
3588: END IF;
3589: --
3590: END LOOP;
3588: END IF;
3589: --
3590: END LOOP;
3591: --
3592: hr_utility.set_location(l_proc,110);
3593: --
3594: FOR assignment_rate_rec in csr_assignment_rate_values LOOP
3595: --
3596: hr_utility.set_location(l_proc,120);
3592: hr_utility.set_location(l_proc,110);
3593: --
3594: FOR assignment_rate_rec in csr_assignment_rate_values LOOP
3595: --
3596: hr_utility.set_location(l_proc,120);
3597: --
3598: DELETE from pay_grade_rules_f pgr
3599: WHERE pgr.rowid = assignment_rate_rec.rowid;
3600: --
3599: WHERE pgr.rowid = assignment_rate_rec.rowid;
3600: --
3601: END LOOP;
3602: --
3603: hr_utility.set_location(l_proc,130);
3604: --
3605: FOR comments_rec IN csr_comments LOOP
3606: --
3607: hr_utility.set_location(l_proc,140);
3603: hr_utility.set_location(l_proc,130);
3604: --
3605: FOR comments_rec IN csr_comments LOOP
3606: --
3607: hr_utility.set_location(l_proc,140);
3608: --
3609: DELETE FROM hr_comments h
3610: WHERE h.rowid = comments_rec.rowid;
3611: --
3610: WHERE h.rowid = comments_rec.rowid;
3611: --
3612: IF SQL%NOTFOUND THEN
3613: --
3614: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
3615: hr_utility.set_message_token('PROCEDURE','cancel_placement');
3616: hr_utility.set_message_token('STEP',4);
3617: hr_utility.raise_error;
3618: --
3611: --
3612: IF SQL%NOTFOUND THEN
3613: --
3614: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
3615: hr_utility.set_message_token('PROCEDURE','cancel_placement');
3616: hr_utility.set_message_token('STEP',4);
3617: hr_utility.raise_error;
3618: --
3619: END IF;
3612: IF SQL%NOTFOUND THEN
3613: --
3614: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
3615: hr_utility.set_message_token('PROCEDURE','cancel_placement');
3616: hr_utility.set_message_token('STEP',4);
3617: hr_utility.raise_error;
3618: --
3619: END IF;
3620: --
3613: --
3614: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
3615: hr_utility.set_message_token('PROCEDURE','cancel_placement');
3616: hr_utility.set_message_token('STEP',4);
3617: hr_utility.raise_error;
3618: --
3619: END IF;
3620: --
3621: END LOOP;
3619: END IF;
3620: --
3621: END LOOP;
3622: --
3623: hr_utility.set_location(l_proc,150);
3624: --
3625: FOR requisition_rec IN csr_requisitions LOOP
3626: --
3627: hr_utility.set_location(l_proc,160);
3623: hr_utility.set_location(l_proc,150);
3624: --
3625: FOR requisition_rec IN csr_requisitions LOOP
3626: --
3627: hr_utility.set_location(l_proc,160);
3628: --
3629: UPDATE per_requisitions pr
3630: SET pr.person_id = NULL
3631: WHERE pr.rowid = requisition_rec.rowid;
3631: WHERE pr.rowid = requisition_rec.rowid;
3632: --
3633: IF SQL%NOTFOUND THEN
3634: --
3635: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
3636: hr_utility.set_message_token('PROCEDURE','do_cancel_placement');
3637: hr_utility.set_message_token('STEP',5);
3638: hr_utility.raise_error;
3639: --
3632: --
3633: IF SQL%NOTFOUND THEN
3634: --
3635: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
3636: hr_utility.set_message_token('PROCEDURE','do_cancel_placement');
3637: hr_utility.set_message_token('STEP',5);
3638: hr_utility.raise_error;
3639: --
3640: END IF;
3633: IF SQL%NOTFOUND THEN
3634: --
3635: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
3636: hr_utility.set_message_token('PROCEDURE','do_cancel_placement');
3637: hr_utility.set_message_token('STEP',5);
3638: hr_utility.raise_error;
3639: --
3640: END IF;
3641: --
3634: --
3635: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
3636: hr_utility.set_message_token('PROCEDURE','do_cancel_placement');
3637: hr_utility.set_message_token('STEP',5);
3638: hr_utility.raise_error;
3639: --
3640: END IF;
3641: --
3642: END LOOP;
3640: END IF;
3641: --
3642: END LOOP;
3643: --
3644: hr_utility.set_location(l_proc,170);
3645: --
3646: FOR events_rec IN csr_events LOOP
3647: --
3648: hr_utility.set_location(l_proc,180);
3644: hr_utility.set_location(l_proc,170);
3645: --
3646: FOR events_rec IN csr_events LOOP
3647: --
3648: hr_utility.set_location(l_proc,180);
3649: --
3650: DELETE FROM per_bookings pb
3651: WHERE pb.rowid = events_rec.rowid;
3652: --
3651: WHERE pb.rowid = events_rec.rowid;
3652: --
3653: IF SQL%NOTFOUND THEN
3654: --
3655: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
3656: hr_utility.set_message_token('PROCEDURE','do_cancel_placement');
3657: hr_utility.set_message_token('STEP',6);
3658: hr_utility.raise_error;
3659: --
3652: --
3653: IF SQL%NOTFOUND THEN
3654: --
3655: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
3656: hr_utility.set_message_token('PROCEDURE','do_cancel_placement');
3657: hr_utility.set_message_token('STEP',6);
3658: hr_utility.raise_error;
3659: --
3660: END IF;
3653: IF SQL%NOTFOUND THEN
3654: --
3655: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
3656: hr_utility.set_message_token('PROCEDURE','do_cancel_placement');
3657: hr_utility.set_message_token('STEP',6);
3658: hr_utility.raise_error;
3659: --
3660: END IF;
3661: --
3654: --
3655: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
3656: hr_utility.set_message_token('PROCEDURE','do_cancel_placement');
3657: hr_utility.set_message_token('STEP',6);
3658: hr_utility.raise_error;
3659: --
3660: END IF;
3661: --
3662: END LOOP;
3660: END IF;
3661: --
3662: END LOOP;
3663: --
3664: hr_utility.set_location(l_proc,190);
3665: --
3666: FOR budget_rec IN csr_budget_values LOOP
3667: --
3668: hr_utility.set_location(l_proc,200);
3664: hr_utility.set_location(l_proc,190);
3665: --
3666: FOR budget_rec IN csr_budget_values LOOP
3667: --
3668: hr_utility.set_location(l_proc,200);
3669: --
3670: DELETE FROM per_assignment_budget_values_f pab
3671: WHERE pab.rowid = budget_rec.rowid;
3672: --
3671: WHERE pab.rowid = budget_rec.rowid;
3672: --
3673: IF SQL%NOTFOUND THEN
3674: --
3675: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
3676: hr_utility.set_message_token('PROCEDURE','do_cancel_placement');
3677: hr_utility.set_message_token('STEP',7);
3678: hr_utility.raise_error;
3679: --
3672: --
3673: IF SQL%NOTFOUND THEN
3674: --
3675: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
3676: hr_utility.set_message_token('PROCEDURE','do_cancel_placement');
3677: hr_utility.set_message_token('STEP',7);
3678: hr_utility.raise_error;
3679: --
3680: END IF;
3673: IF SQL%NOTFOUND THEN
3674: --
3675: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
3676: hr_utility.set_message_token('PROCEDURE','do_cancel_placement');
3677: hr_utility.set_message_token('STEP',7);
3678: hr_utility.raise_error;
3679: --
3680: END IF;
3681: --
3674: --
3675: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
3676: hr_utility.set_message_token('PROCEDURE','do_cancel_placement');
3677: hr_utility.set_message_token('STEP',7);
3678: hr_utility.raise_error;
3679: --
3680: END IF;
3681: --
3682: END LOOP budget_value;
3680: END IF;
3681: --
3682: END LOOP budget_value;
3683: --
3684: hr_utility.set_location(l_proc,210);
3685: --
3686: FOR payment_rec IN csr_payment LOOP
3687: --
3688: hr_utility.set_location(l_proc,220);
3684: hr_utility.set_location(l_proc,210);
3685: --
3686: FOR payment_rec IN csr_payment LOOP
3687: --
3688: hr_utility.set_location(l_proc,220);
3689: --
3690: DELETE FROM pay_personal_payment_methods ppm
3691: WHERE ppm.rowid = payment_rec.rowid;
3692: --
3691: WHERE ppm.rowid = payment_rec.rowid;
3692: --
3693: IF SQL%NOTFOUND THEN
3694: --
3695: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
3696: hr_utility.set_message_token('PROCEDURE','do_cancel_placement');
3697: hr_utility.set_message_token('STEP',8);
3698: hr_utility.raise_error;
3699: --
3692: --
3693: IF SQL%NOTFOUND THEN
3694: --
3695: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
3696: hr_utility.set_message_token('PROCEDURE','do_cancel_placement');
3697: hr_utility.set_message_token('STEP',8);
3698: hr_utility.raise_error;
3699: --
3700: end if;
3693: IF SQL%NOTFOUND THEN
3694: --
3695: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
3696: hr_utility.set_message_token('PROCEDURE','do_cancel_placement');
3697: hr_utility.set_message_token('STEP',8);
3698: hr_utility.raise_error;
3699: --
3700: end if;
3701: --
3694: --
3695: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
3696: hr_utility.set_message_token('PROCEDURE','do_cancel_placement');
3697: hr_utility.set_message_token('STEP',8);
3698: hr_utility.raise_error;
3699: --
3700: end if;
3701: --
3702: END LOOP;
3700: end if;
3701: --
3702: END LOOP;
3703: --
3704: hr_utility.set_location(l_proc,230);
3705: --
3706: FOR assignment1_rec IN csr_assignments1 LOOP
3707: --
3708: hr_utility.set_location(l_proc,240);
3704: hr_utility.set_location(l_proc,230);
3705: --
3706: FOR assignment1_rec IN csr_assignments1 LOOP
3707: --
3708: hr_utility.set_location(l_proc,240);
3709: --
3710: DELETE FROM per_spinal_point_placements_f spp
3711: WHERE spp.assignment_id = assignment1_rec.assignment_id;
3712: --
3709: --
3710: DELETE FROM per_spinal_point_placements_f spp
3711: WHERE spp.assignment_id = assignment1_rec.assignment_id;
3712: --
3713: hr_utility.set_location(l_proc,250);
3714: --
3715: DELETE FROM pay_cost_allocations_f pca
3716: WHERE pca.assignment_id = assignment1_rec.assignment_id;
3717: --
3714: --
3715: DELETE FROM pay_cost_allocations_f pca
3716: WHERE pca.assignment_id = assignment1_rec.assignment_id;
3717: --
3718: hr_utility.set_location(l_proc,260);
3719: --
3720: DELETE FROM per_all_assignments_f paf
3721: WHERE paf.rowid = assignment1_rec.rowid;
3722: --
3721: WHERE paf.rowid = assignment1_rec.rowid;
3722: --
3723: IF SQL%NOTFOUND THEN
3724: --
3725: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
3726: hr_utility.set_message_token('PROCEDURE','cancel_placement');
3727: hr_utility.set_message_token('STEP',9);
3728: hr_utility.raise_error;
3729: --
3722: --
3723: IF SQL%NOTFOUND THEN
3724: --
3725: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
3726: hr_utility.set_message_token('PROCEDURE','cancel_placement');
3727: hr_utility.set_message_token('STEP',9);
3728: hr_utility.raise_error;
3729: --
3730: END IF;
3723: IF SQL%NOTFOUND THEN
3724: --
3725: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
3726: hr_utility.set_message_token('PROCEDURE','cancel_placement');
3727: hr_utility.set_message_token('STEP',9);
3728: hr_utility.raise_error;
3729: --
3730: END IF;
3731: --
3724: --
3725: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
3726: hr_utility.set_message_token('PROCEDURE','cancel_placement');
3727: hr_utility.set_message_token('STEP',9);
3728: hr_utility.raise_error;
3729: --
3730: END IF;
3731: --
3732: END LOOP;
3730: END IF;
3731: --
3732: END LOOP;
3733: --
3734: hr_utility.set_location(l_proc,270);
3735: --
3736: FOR applicantions_rec IN csr_applications LOOP
3737: --
3738: hr_utility.set_location(l_proc,280);
3734: hr_utility.set_location(l_proc,270);
3735: --
3736: FOR applicantions_rec IN csr_applications LOOP
3737: --
3738: hr_utility.set_location(l_proc,280);
3739: --
3740: UPDATE per_applications pap
3741: SET pap.date_end = NULL
3742: WHERE pap.rowid = applicantions_rec.rowid;
3742: WHERE pap.rowid = applicantions_rec.rowid;
3743: --
3744: IF SQL%NOTFOUND THEN
3745: --
3746: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
3747: hr_utility.set_message_token('PROCEDURE','cancel_placement');
3748: hr_utility.set_message_token('STEP',10);
3749: hr_utility.raise_error;
3750: --
3743: --
3744: IF SQL%NOTFOUND THEN
3745: --
3746: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
3747: hr_utility.set_message_token('PROCEDURE','cancel_placement');
3748: hr_utility.set_message_token('STEP',10);
3749: hr_utility.raise_error;
3750: --
3751: END IF;
3744: IF SQL%NOTFOUND THEN
3745: --
3746: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
3747: hr_utility.set_message_token('PROCEDURE','cancel_placement');
3748: hr_utility.set_message_token('STEP',10);
3749: hr_utility.raise_error;
3750: --
3751: END IF;
3752: --
3745: --
3746: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
3747: hr_utility.set_message_token('PROCEDURE','cancel_placement');
3748: hr_utility.set_message_token('STEP',10);
3749: hr_utility.raise_error;
3750: --
3751: END IF;
3752: --
3753: END LOOP;
3751: END IF;
3752: --
3753: END LOOP;
3754: --
3755: hr_utility.set_location(l_proc,320);
3756: --
3757: -- Change for the bug 6460093 starts here
3758: OPEN csr_pdped_start;
3759: FETCH csr_pdped_start into c_effective_start_date;
3763: OPEN csr_periods_of_placement;
3764: --
3765: LOOP
3766: --
3767: hr_utility.set_location(l_proc,330);
3768: --
3769: FETCH csr_periods_of_placement INTO l_rowid;
3770: --
3771: EXIT WHEN csr_periods_of_placement%NOTFOUND;
3776: IF SQL%NOTFOUND THEN
3777: --
3778: CLOSE csr_periods_of_placement;
3779: --
3780: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
3781: hr_utility.set_message_token('PROCEDURE','cancel_placement');
3782: hr_utility.set_message_token('STEP',11);
3783: hr_utility.raise_error;
3784: --
3777: --
3778: CLOSE csr_periods_of_placement;
3779: --
3780: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
3781: hr_utility.set_message_token('PROCEDURE','cancel_placement');
3782: hr_utility.set_message_token('STEP',11);
3783: hr_utility.raise_error;
3784: --
3785: END IF;
3778: CLOSE csr_periods_of_placement;
3779: --
3780: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
3781: hr_utility.set_message_token('PROCEDURE','cancel_placement');
3782: hr_utility.set_message_token('STEP',11);
3783: hr_utility.raise_error;
3784: --
3785: END IF;
3786: --
3779: --
3780: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
3781: hr_utility.set_message_token('PROCEDURE','cancel_placement');
3782: hr_utility.set_message_token('STEP',11);
3783: hr_utility.raise_error;
3784: --
3785: END IF;
3786: --
3787: END LOOP;
3785: END IF;
3786: --
3787: END LOOP;
3788: --
3789: hr_utility.set_location(l_proc,340);
3790: --
3791: IF csr_periods_of_placement%ROWCOUNT <1 THEN
3792: --
3793: CLOSE csr_periods_of_placement;
3791: IF csr_periods_of_placement%ROWCOUNT <1 THEN
3792: --
3793: CLOSE csr_periods_of_placement;
3794: --
3795: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
3796: hr_utility.set_message_token('PROCEDURE','cancel_placement');
3797: hr_utility.set_message_token('STEP',12);
3798: hr_utility.raise_error;
3799: --
3792: --
3793: CLOSE csr_periods_of_placement;
3794: --
3795: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
3796: hr_utility.set_message_token('PROCEDURE','cancel_placement');
3797: hr_utility.set_message_token('STEP',12);
3798: hr_utility.raise_error;
3799: --
3800: END IF;
3793: CLOSE csr_periods_of_placement;
3794: --
3795: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
3796: hr_utility.set_message_token('PROCEDURE','cancel_placement');
3797: hr_utility.set_message_token('STEP',12);
3798: hr_utility.raise_error;
3799: --
3800: END IF;
3801: --
3794: --
3795: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
3796: hr_utility.set_message_token('PROCEDURE','cancel_placement');
3797: hr_utility.set_message_token('STEP',12);
3798: hr_utility.raise_error;
3799: --
3800: END IF;
3801: --
3802: CLOSE csr_periods_of_placement;
3800: END IF;
3801: --
3802: CLOSE csr_periods_of_placement;
3803: --
3804: hr_utility.set_location(l_proc,350);
3805: --
3806: -- Check that there is not a previous period of placement
3807: -- record that ended the day before the current period of placement
3808: -- record. I.e is a back to back hire.
3811: FETCH csr_pop_back_to_back INTO l_dummy_id;
3812: --
3813: IF csr_pop_back_to_back%FOUND THEN
3814: --
3815: hr_utility.set_location(l_proc,353);
3816: --
3817: l_pop_back_to_back := TRUE;
3818: --
3819: ELSE
3822: -- check that there is not a record for period of service
3823: -- that ends the day before the period of placement record
3824: -- being canceled.
3825: --
3826: hr_utility.set_location(l_proc,355);
3827: --
3828: l_pop_back_to_back := FALSE;
3829: --
3830: OPEN csr_pos_back_to_back (p_date_start => p_date_start);
3831: FETCH csr_pos_back_to_back INTO l_dummy_id;
3832: --
3833: IF csr_pos_back_to_back%FOUND THEN
3834: --
3835: hr_utility.set_location(l_proc,356);
3836: --
3837: l_pos_back_to_back := TRUE;
3838: --
3839: ELSE
3837: l_pos_back_to_back := TRUE;
3838: --
3839: ELSE
3840: --
3841: hr_utility.set_location(l_proc,357);
3842: --
3843: l_pos_back_to_back := FALSE;
3844: --
3845: END IF;
3852: --
3853: if NOT l_pop_back_to_back then -- 3194314
3854: FOR assignment2_rec IN csr_assignments2 LOOP
3855: --
3856: hr_utility.set_location(l_proc,300);
3857: --
3858: UPDATE per_all_assignments_f paf
3859: SET paf.effective_end_date = l_end_of_time
3860: WHERE paf.rowid = assignment2_rec.rowid;
3858: UPDATE per_all_assignments_f paf
3859: SET paf.effective_end_date = l_end_of_time
3860: WHERE paf.rowid = assignment2_rec.rowid;
3861: --
3862: hr_utility.set_location(l_proc,310);
3863: --
3864: END LOOP;
3865: end if;
3866: --
3863: --
3864: END LOOP;
3865: end if;
3866: --
3867: hr_utility.set_location(l_proc,360);
3868: --
3869: FOR person_rec IN csr_person LOOP
3870: --
3871: l_person_rec_found := TRUE;
3869: FOR person_rec IN csr_person LOOP
3870: --
3871: l_person_rec_found := TRUE;
3872: --
3873: hr_utility.set_location(l_proc,370);
3874: --
3875: IF l_pop_back_to_back THEN
3876: --
3877: hr_utility.set_location(l_proc,380);
3873: hr_utility.set_location(l_proc,370);
3874: --
3875: IF l_pop_back_to_back THEN
3876: --
3877: hr_utility.set_location(l_proc,380);
3878: --
3879: IF person_rec.effective_end_date = hr_general.end_of_time THEN
3880: --
3881: hr_utility.set_location(l_proc,390);
3877: hr_utility.set_location(l_proc,380);
3878: --
3879: IF person_rec.effective_end_date = hr_general.end_of_time THEN
3880: --
3881: hr_utility.set_location(l_proc,390);
3882: --
3883: OPEN csr_get_person_type (p_system_person_type => 'EX_CWK');
3884: FETCH csr_get_person_type INTO l_person_type_id;
3885: --
3905: WHERE rowid = person_rec.rowid;
3906: --
3907: ELSE -- #1998140
3908: --
3909: hr_utility.set_location(l_proc,400);
3910: --
3911: DELETE FROM per_people_f
3912: WHERE rowid = person_rec.rowid;
3913: --
3919:
3920: IF person_rec.effective_end_date = hr_general.end_of_time THEN
3921:
3922: -- it should restore the EX_EMP record instead of removing it
3923: hr_utility.set_location(l_proc,405);
3924: --
3925: UPDATE per_people_f
3926: SET npw_number = null,
3927: effective_start_date = p_date_start, -- in case DT updates exist
3931:
3932: --
3933: ELSE -- #1998140
3934: --
3935: hr_utility.set_location(l_proc,406);
3936: --
3937: DELETE FROM per_people_f
3938: WHERE rowid = person_rec.rowid;
3939: --
3942: -- << 3194314
3943:
3944: ELSE
3945: --
3946: hr_utility.set_location(l_proc,410);
3947: --
3948: DELETE FROM per_people_f
3949: WHERE rowid = person_rec.rowid;
3950: --
3949: WHERE rowid = person_rec.rowid;
3950: --
3951: END IF;
3952: --
3953: hr_utility.set_location(l_proc,420);
3954: --
3955: IF SQL%NOTFOUND THEN
3956: --
3957: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
3953: hr_utility.set_location(l_proc,420);
3954: --
3955: IF SQL%NOTFOUND THEN
3956: --
3957: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
3958: hr_utility.set_message_token('PROCEDURE','cancel_placement');
3959: hr_utility.set_message_token('STEP',13);
3960: hr_utility.raise_error;
3961: --
3954: --
3955: IF SQL%NOTFOUND THEN
3956: --
3957: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
3958: hr_utility.set_message_token('PROCEDURE','cancel_placement');
3959: hr_utility.set_message_token('STEP',13);
3960: hr_utility.raise_error;
3961: --
3962: END IF;
3955: IF SQL%NOTFOUND THEN
3956: --
3957: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
3958: hr_utility.set_message_token('PROCEDURE','cancel_placement');
3959: hr_utility.set_message_token('STEP',13);
3960: hr_utility.raise_error;
3961: --
3962: END IF;
3963: --
3956: --
3957: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
3958: hr_utility.set_message_token('PROCEDURE','cancel_placement');
3959: hr_utility.set_message_token('STEP',13);
3960: hr_utility.raise_error;
3961: --
3962: END IF;
3963: --
3964: END LOOP;
3962: END IF;
3963: --
3964: END LOOP;
3965: --
3966: hr_utility.set_location(l_proc,430);
3967: --
3968: IF NOT l_person_rec_found THEN
3969: --
3970: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
3966: hr_utility.set_location(l_proc,430);
3967: --
3968: IF NOT l_person_rec_found THEN
3969: --
3970: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
3971: hr_utility.set_message_token('PROCEDURE','cancel_placement');
3972: hr_utility.set_message_token('STEP',14);
3973: hr_utility.raise_error;
3974: --
3967: --
3968: IF NOT l_person_rec_found THEN
3969: --
3970: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
3971: hr_utility.set_message_token('PROCEDURE','cancel_placement');
3972: hr_utility.set_message_token('STEP',14);
3973: hr_utility.raise_error;
3974: --
3975: END IF;
3968: IF NOT l_person_rec_found THEN
3969: --
3970: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
3971: hr_utility.set_message_token('PROCEDURE','cancel_placement');
3972: hr_utility.set_message_token('STEP',14);
3973: hr_utility.raise_error;
3974: --
3975: END IF;
3976: --
3969: --
3970: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
3971: hr_utility.set_message_token('PROCEDURE','cancel_placement');
3972: hr_utility.set_message_token('STEP',14);
3973: hr_utility.raise_error;
3974: --
3975: END IF;
3976: --
3977: hr_utility.set_location(l_proc,440);
3973: hr_utility.raise_error;
3974: --
3975: END IF;
3976: --
3977: hr_utility.set_location(l_proc,440);
3978: --
3979: if NOT l_pos_back_to_back then -- 3194314 this should not get executed if b2b emp/cwk
3980:
3981: FOR new_person_rec IN csr_new_person LOOP
3980:
3981: FOR new_person_rec IN csr_new_person LOOP
3982:
3983: --
3984: hr_utility.set_location(l_proc,450);
3985: --
3986: l_new_person_found := TRUE;
3987: --
3988: IF NOT l_pop_back_to_back THEN
3986: l_new_person_found := TRUE;
3987: --
3988: IF NOT l_pop_back_to_back THEN
3989: --
3990: hr_utility.set_location(l_proc,460);
3991: --
3992: UPDATE per_people_f
3993: SET effective_end_date = l_end_of_time
3994: WHERE rowid = new_person_rec.rowid;
3994: WHERE rowid = new_person_rec.rowid;
3995: --
3996: END IF;
3997: --
3998: hr_utility.set_location(l_proc,470);
3999: --
4000: IF sql%ROWCOUNT <1 then
4001: --
4002: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
3998: hr_utility.set_location(l_proc,470);
3999: --
4000: IF sql%ROWCOUNT <1 then
4001: --
4002: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
4003: hr_utility.set_message_token('PROCEDURE','cancel_placement');
4004: hr_utility.set_message_token('STEP',15);
4005: hr_utility.raise_error;
4006: --
3999: --
4000: IF sql%ROWCOUNT <1 then
4001: --
4002: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
4003: hr_utility.set_message_token('PROCEDURE','cancel_placement');
4004: hr_utility.set_message_token('STEP',15);
4005: hr_utility.raise_error;
4006: --
4007: END IF;
4000: IF sql%ROWCOUNT <1 then
4001: --
4002: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
4003: hr_utility.set_message_token('PROCEDURE','cancel_placement');
4004: hr_utility.set_message_token('STEP',15);
4005: hr_utility.raise_error;
4006: --
4007: END IF;
4008: --
4001: --
4002: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
4003: hr_utility.set_message_token('PROCEDURE','cancel_placement');
4004: hr_utility.set_message_token('STEP',15);
4005: hr_utility.raise_error;
4006: --
4007: END IF;
4008: --
4009: END LOOP;
4009: END LOOP;
4010: end if; -- << 3194314
4011:
4012: --
4013: hr_utility.set_location(l_proc,480);
4014: --
4015: IF NOT l_new_person_found THEN
4016: --
4017: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
4013: hr_utility.set_location(l_proc,480);
4014: --
4015: IF NOT l_new_person_found THEN
4016: --
4017: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
4018: hr_utility.set_message_token('PROCEDURE','cancel_placement');
4019: hr_utility.set_message_token('STEP',16);
4020: hr_utility.raise_error;
4021: --
4014: --
4015: IF NOT l_new_person_found THEN
4016: --
4017: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
4018: hr_utility.set_message_token('PROCEDURE','cancel_placement');
4019: hr_utility.set_message_token('STEP',16);
4020: hr_utility.raise_error;
4021: --
4022: END IF;
4015: IF NOT l_new_person_found THEN
4016: --
4017: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
4018: hr_utility.set_message_token('PROCEDURE','cancel_placement');
4019: hr_utility.set_message_token('STEP',16);
4020: hr_utility.raise_error;
4021: --
4022: END IF;
4023: --
4016: --
4017: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
4018: hr_utility.set_message_token('PROCEDURE','cancel_placement');
4019: hr_utility.set_message_token('STEP',16);
4020: hr_utility.raise_error;
4021: --
4022: END IF;
4023: --
4024: -- If there are no back to back records for
4027: --
4028: IF NOT l_pop_back_to_back AND
4029: NOT l_pos_back_to_back THEN
4030: --
4031: hr_utility.set_location(l_proc,490);
4032: --
4033: hr_per_type_usage_internal.cancel_person_type_usage
4034: (p_effective_date => l_date_start
4035: ,p_person_id => p_person_id
4055: -- bug fix 6992346
4056: -- If there are back to back records for period of service
4057: -- and period of placements then
4058: elsif NOT l_pop_back_to_back AND l_pos_back_to_back THEN
4059: hr_utility.set_location(l_proc,491);
4060: hr_utility.set_location('l_date_start '||l_date_start,491);
4061: --
4062: hr_per_type_usage_internal.cancel_person_type_usage
4063: (p_effective_date => l_date_start
4056: -- If there are back to back records for period of service
4057: -- and period of placements then
4058: elsif NOT l_pop_back_to_back AND l_pos_back_to_back THEN
4059: hr_utility.set_location(l_proc,491);
4060: hr_utility.set_location('l_date_start '||l_date_start,491);
4061: --
4062: hr_per_type_usage_internal.cancel_person_type_usage
4063: (p_effective_date => l_date_start
4064: ,p_person_id => p_person_id
4067:
4068: ELSIF l_pos_back_to_back and l_pop_back_to_back THEN
4069: --ELSIF l_pos_back_to_back THEN
4070: --
4071: hr_utility.set_location(l_proc,500);
4072: --
4073: OPEN csr_get_ptu_details;
4074: FETCH csr_get_ptu_details INTO l_person_type_usage_id,
4075: l_object_version_number;
4075: l_object_version_number;
4076: --
4077: IF csr_get_ptu_details%FOUND THEN
4078: --
4079: hr_utility.set_location(l_proc,510);
4080: --
4081: hr_per_type_usage_internal.delete_person_type_usage
4082: (p_validate => FALSE
4083: ,p_person_type_usage_id => l_person_type_usage_id
4095: per_cancel_hire_or_apl_pkg.update_person_list(p_person_id => p_person_id);
4096: --
4097: for asg_sec_rec in csr_asg_sec loop
4098: --
4099: hr_utility.set_location(l_proc,300);
4100: -- do some security maintenance.
4101: -- reset the security access(per_person_list) for this assignment
4102: hr_security_internal.add_to_person_list(
4103: p_effective_date => asg_sec_rec.effective_start_date
4104: ,p_assignment_id => asg_sec_rec.assignment_id);
4105: --
4106: end loop;
4107: --
4108: hr_utility.set_location('Leaving : '||l_proc,999);
4109: --
4110: END do_cancel_placement;
4111: --
4112: -- ---------------------------------------------------------------------------
4131: l_proc VARCHAR2(72) := g_package||'return_legislation_code';
4132: --
4133: BEGIN
4134: --
4135: hr_utility.set_location('Entering:'|| l_proc, 10);
4136: --
4137: -- Ensure that all the mandatory parameter are not null
4138: --
4139: hr_api.mandatory_arg_error
4154: fnd_message.raise_error;
4155: --
4156: END IF;
4157: --
4158: hr_utility.set_location(l_proc,20);
4159: --
4160: CLOSE csr_leg_code;
4161: --
4162: hr_utility.set_location(' Leaving:'|| l_proc, 999);
4158: hr_utility.set_location(l_proc,20);
4159: --
4160: CLOSE csr_leg_code;
4161: --
4162: hr_utility.set_location(' Leaving:'|| l_proc, 999);
4163: --
4164: RETURN l_legislation_code;
4165: --
4166: END return_legislation_code;