[Home] [Help]
315: v_date_probation_end DATE;
316: --
317: BEGIN
318: --
319: hr_utility.set_location('hr_assignment.gen_probation_end',1);
320: --
321: v_start_date := p_start_date;
322: v_date_probation_end := p_date_probation_end;
323: --
324: IF v_date_probation_end IS NULL THEN
325: --
326: -- generate new default probation end date
327: --
328: hr_utility.set_location('hr_assignment.gen_probation_end',2);
329: --
330: /*------ changes made for bug 5619940 ---- */
331: IF p_probation_period = 0
332: and ( p_probation_unit = 'D'
356: --
357: ELSIF
358: p_assignment_id IS NULL THEN
359: --
360: hr_utility.set_location('hr_assignment.gen_probation_end',3);
361: --
362: -- If the Assignment is a new one
363: -- ensure that the DATE_PROBATION_END is on or after the assignment
364: -- start date
363: -- ensure that the DATE_PROBATION_END is on or after the assignment
364: -- start date
365: --
366: IF v_date_probation_end < v_start_date THEN
367: hr_utility.set_message(801,'HR_6150_EMP_ASS_PROB_END');
368: hr_utility.raise_error;
369: END IF;
370: --
371: ELSE
364: -- start date
365: --
366: IF v_date_probation_end < v_start_date THEN
367: hr_utility.set_message(801,'HR_6150_EMP_ASS_PROB_END');
368: hr_utility.raise_error;
369: END IF;
370: --
371: ELSE
372: --
376: -- date for the assignment.
377: --
378: BEGIN
379: --
380: hr_utility.set_location('hr_assignment.gen_probation_end',4);
381: select v_date_probation_end - min(effective_start_date)
382: into check_date
383: from per_assignments_f
384: where assignment_id = p_assignment_id
388: WHEN NO_DATA_FOUND THEN NULL;
389: --
390: END;
391: --
392: hr_utility.set_location('hr_assignment.gen_probation_end',5);
393: --
394: IF check_date < 0 THEN
395: hr_utility.set_message(801,'HR_6150_EMP_ASS_PROB_END');
396: hr_utility.raise_error;
391: --
392: hr_utility.set_location('hr_assignment.gen_probation_end',5);
393: --
394: IF check_date < 0 THEN
395: hr_utility.set_message(801,'HR_6150_EMP_ASS_PROB_END');
396: hr_utility.raise_error;
397: END IF;
398: --
399: END IF;
392: hr_utility.set_location('hr_assignment.gen_probation_end',5);
393: --
394: IF check_date < 0 THEN
395: hr_utility.set_message(801,'HR_6150_EMP_ASS_PROB_END');
396: hr_utility.raise_error;
397: END IF;
398: --
399: END IF;
400: --
418: ) is
419: --
420: begin
421: --
422: hr_utility.set_location('hr_assignment.gen_new_ass_sequence',1);
423: select nvl(max(assignment_sequence),0) +1
424: into p_assignment_sequence
425: from per_assignments_f
426: where person_id = p_person_id
509: duplicate := 'N';
510: --
511: BEGIN
512: --
513: hr_utility.set_location('hr_assignment.gen_new_ass_number',1);
514: select 'Y'
515: into duplicate
516: from sys.dual
517: where exists
541: ass_seq := p_assignment_sequence;
542: --
543: IF p_assignment_number IS NOT NULL THEN
544: --
545: hr_utility.set_location('hr_assignment.gen_new_ass_number',2);
546: IF validate_ass_number(p_assignment_id
547: ,p_business_group_id
548: ,p_assignment_number) THEN
549: NULL;
547: ,p_business_group_id
548: ,p_assignment_number) THEN
549: NULL;
550: ELSE
551: hr_utility.set_message(801,'HR_6146_EMP_ASS_DUPL_NUMBER');
552: hr_utility.raise_error;
553: END IF;
554: --
555: ELSE
548: ,p_assignment_number) THEN
549: NULL;
550: ELSE
551: hr_utility.set_message(801,'HR_6146_EMP_ASS_DUPL_NUMBER');
552: hr_utility.raise_error;
553: END IF;
554: --
555: ELSE
556: --
553: END IF;
554: --
555: ELSE
556: --
557: hr_utility.set_location('hr_assignment.gen_new_ass_number',3);
558: WHILE loop_count > 0 LOOP
559: --
560: IF ass_seq = 1 THEN
561: -- p_assignment_number := p_worker_number;
611: p_assignment_number := substr(p_worker_number,1,29-length(TO_CHAR(ass_seq)))||'-'||TO_CHAR(ass_seq);
612: -- p_assignment_number := p_worker_number||'-'||TO_CHAR(ass_seq);
613: END IF;
614: --
615: hr_utility.set_location('hr_assignment.gen_new_ass_number',4);
616: IF validate_ass_number(p_assignment_id
617: ,p_business_group_id
618: ,p_assignment_number) THEN
619: EXIT;
623: END IF;
624: --
625: END LOOP;
626: --
627: hr_utility.set_location('hr_assignment.gen_new_ass_number',5);
628: IF loop_count = 0 THEN
629: hr_utility.set_message(801,'HR_6148_EMP_ASS_LOOP_OUT');
630: hr_utility.raise_error;
631: END IF;
625: END LOOP;
626: --
627: hr_utility.set_location('hr_assignment.gen_new_ass_number',5);
628: IF loop_count = 0 THEN
629: hr_utility.set_message(801,'HR_6148_EMP_ASS_LOOP_OUT');
630: hr_utility.raise_error;
631: END IF;
632: --
633: END IF;
626: --
627: hr_utility.set_location('hr_assignment.gen_new_ass_number',5);
628: IF loop_count = 0 THEN
629: hr_utility.set_message(801,'HR_6148_EMP_ASS_LOOP_OUT');
630: hr_utility.raise_error;
631: END IF;
632: --
633: END IF;
634: --
657: no_of_hours NUMBER;
658: --
659: BEGIN
660: --
661: hr_utility.set_location('hr_assignment.check_hours',1);
662: IF p_frequency = 'D' THEN
663: no_of_hours := 24;
664: ELSIF p_frequency = 'W' THEN
665: no_of_hours := 168;
673: no_of_hours := 0;
674: END IF;
675: --
676: IF no_of_hours - p_normal_hours < 0 THEN
677: hr_utility.set_message(801,'HR_6015_ALL_FORMAT_WKG_HRS');
678: hr_utility.raise_error;
679: END IF;
680: --
681: END check_hours; -----------------------------------------------------
674: END IF;
675: --
676: IF no_of_hours - p_normal_hours < 0 THEN
677: hr_utility.set_message(801,'HR_6015_ALL_FORMAT_WKG_HRS');
678: hr_utility.raise_error;
679: END IF;
680: --
681: END check_hours; -----------------------------------------------------
682: --
758:
759:
760: BEGIN
761: --
762: hr_utility.set_location('hr_assignment.check_term',1);
763: p_start_date := p_sdate;
764: p_end_date := p_edate;
765: p_new_ass_end_date := null;
766:
780: FETCH csr_get_term_dates INTO p_atd
781: ,p_fpd;
782: CLOSE csr_get_term_dates;
783:
784: hr_utility.set_location('hr_assignment.check_term',2);
785:
786: IF p_atd IS NULL THEN null;
787: ELSE
788: --
788: --
789: -------------------------------------
790: -- Get the Effective End Date of the Assignment
791: -------------------------------------
792: hr_utility.set_location('hr_assignment.check_term',3);
793: --
794: select max(effective_end_date)
795: into p_ass_end_date
796: from per_assignments_f
802: -- If the mode is UPDATE_OVERRIDE and the current status is TERM_ASSIGN
803: -- then compare the session date with the earliest TERM_ASSIGN date
804: -- and store the earliest.
805: -------------------------------------
806: hr_utility.set_location('hr_assignment.check_term',4);
807: --
808: select min(a.effective_start_date)
809: into p_first_term_date
810: from per_assignments_f a
814: where s.assignment_status_type_id
815: = a.assignment_status_type_id
816: and s.per_system_status = 'TERM_ASSIGN');
817: --
818: hr_utility.set_location('hr_assignment.check_term',5);
819: IF p_mode = 'UPDATE_OVERRIDE' AND
820: p_current_status = 'TERM_ASSIGN' THEN
821: --
822: IF p_first_term_date IS NULL OR
840:
841: -- Bug 306713 Start
842:
843: IF (p_mode = 'DELETE_NEXT_CHANGE' or p_mode = 'FUTURE_CHANGE') and p_ass_end_date = p_atd then
844: hr_utility.set_message(801,'HR_6200_EMP_ASS_TERM_EXISTS');
845: hr_utility.raise_error;
846: end if;
847:
848: -- Bug 306713 End
841: -- Bug 306713 Start
842:
843: IF (p_mode = 'DELETE_NEXT_CHANGE' or p_mode = 'FUTURE_CHANGE') and p_ass_end_date = p_atd then
844: hr_utility.set_message(801,'HR_6200_EMP_ASS_TERM_EXISTS');
845: hr_utility.raise_error;
846: end if;
847:
848: -- Bug 306713 End
849:
849:
850: IF (p_mode = 'UPDATE_OVERRIDE' or p_mode = 'FUTURE_CHANGE')
851: AND p_start_date < p_atd + 1 THEN
852: --
853: hr_utility.set_location('hr_assignment.check_term',6);
854: --
855: p_flag := 'N';
856: --
857: BEGIN
855: p_flag := 'N';
856: --
857: BEGIN
858: --
859: hr_utility.set_location('hr_assignment.check_term',7);
860: --
861: select 'Y'
862: into p_flag
863: from per_assignments_f a
872: EXCEPTION
873: WHEN NO_DATA_FOUND THEN NULL;
874: END;
875: --
876: hr_utility.set_location('hr_assignment.check_term',8);
877: IF p_flag = 'Y' THEN
878: hr_utility.set_message(801,'HR_6200_EMP_ASS_TERM_EXISTS');
879: hr_utility.raise_error;
880: END IF;
874: END;
875: --
876: hr_utility.set_location('hr_assignment.check_term',8);
877: IF p_flag = 'Y' THEN
878: hr_utility.set_message(801,'HR_6200_EMP_ASS_TERM_EXISTS');
879: hr_utility.raise_error;
880: END IF;
881: END IF;
882: --
875: --
876: hr_utility.set_location('hr_assignment.check_term',8);
877: IF p_flag = 'Y' THEN
878: hr_utility.set_message(801,'HR_6200_EMP_ASS_TERM_EXISTS');
879: hr_utility.raise_error;
880: END IF;
881: END IF;
882: --
883: ---------------------------------------------------------------
890: -- otherwise
891: -- set the new_assignment_end_date = actual_termination_date
892: ---------------------------------------------------------------
893: IF p_mode = 'UPDATE_OVERRIDE' THEN
894: hr_utility.set_location('hr_assignment.check_term',9);
895: IF p_ass_end_date <= p_atd THEN
896: NULL;
897: ELSE
898: hr_utility.set_location('hr_assignment.check_term',10);
894: hr_utility.set_location('hr_assignment.check_term',9);
895: IF p_ass_end_date <= p_atd THEN
896: NULL;
897: ELSE
898: hr_utility.set_location('hr_assignment.check_term',10);
899: IF p_first_term_date <= p_start_date THEN
900: NULL;
901: ELSE
902: p_new_ass_end_date := p_atd;
911: -- open the assignment up to the actual term date.
912: ---------------------------------------------------------------
913: ELSIF
914: p_mode = 'FUTURE_CHANGE' THEN
915: hr_utility.set_location('hr_assignment.check_term',11);
916: IF p_first_term_date <= p_start_date THEN
917: p_new_ass_end_date := p_fpd;
918: ELSE
919: p_new_ass_end_date := p_atd;
958: -- therefore in this case issue an error.
959: ---------------------------------------------------------------
960: ELSIF
961: p_mode = 'DELETE_NEXT_CHANGE' THEN
962: hr_utility.set_location('hr_assignment.check_term',12);
963: IF p_end_date = p_ass_end_date THEN
964: hr_utility.set_location('hr_assignment.check_term',13);
965: IF p_first_term_date <= p_start_date THEN
966: p_new_ass_end_date := p_fpd;
960: ELSIF
961: p_mode = 'DELETE_NEXT_CHANGE' THEN
962: hr_utility.set_location('hr_assignment.check_term',12);
963: IF p_end_date = p_ass_end_date THEN
964: hr_utility.set_location('hr_assignment.check_term',13);
965: IF p_first_term_date <= p_start_date THEN
966: p_new_ass_end_date := p_fpd;
967: ELSE
968: p_new_ass_end_date := p_atd;
972: p_flag := 'N';
973: --
974: BEGIN
975: --
976: hr_utility.set_location('hr_assignment.check_term',14);
977: --
978: select 'Y'
979: , a.effective_start_date
980: , a.effective_end_date
993: EXCEPTION
994: WHEN NO_DATA_FOUND THEN NULL;
995: END;
996: --
997: hr_utility.set_location('hr_assignment.check_term',15);
998: IF p_flag = 'Y' THEN
999: hr_utility.set_location('hr_assignment.check_term',16);
1000: IF p_next_eff_start_date = p_atd + 1 THEN
1001: hr_utility.set_message(801,'HR_6200_EMP_ASS_TERM_EXISTS');
995: END;
996: --
997: hr_utility.set_location('hr_assignment.check_term',15);
998: IF p_flag = 'Y' THEN
999: hr_utility.set_location('hr_assignment.check_term',16);
1000: IF p_next_eff_start_date = p_atd + 1 THEN
1001: hr_utility.set_message(801,'HR_6200_EMP_ASS_TERM_EXISTS');
1002: hr_utility.raise_error;
1003: ELSE
997: hr_utility.set_location('hr_assignment.check_term',15);
998: IF p_flag = 'Y' THEN
999: hr_utility.set_location('hr_assignment.check_term',16);
1000: IF p_next_eff_start_date = p_atd + 1 THEN
1001: hr_utility.set_message(801,'HR_6200_EMP_ASS_TERM_EXISTS');
1002: hr_utility.raise_error;
1003: ELSE
1004: IF p_first_term_date <= p_start_date THEN
1005: hr_utility.set_location('hr_assignment.check_term',17);
998: IF p_flag = 'Y' THEN
999: hr_utility.set_location('hr_assignment.check_term',16);
1000: IF p_next_eff_start_date = p_atd + 1 THEN
1001: hr_utility.set_message(801,'HR_6200_EMP_ASS_TERM_EXISTS');
1002: hr_utility.raise_error;
1003: ELSE
1004: IF p_first_term_date <= p_start_date THEN
1005: hr_utility.set_location('hr_assignment.check_term',17);
1006: p_new_ass_end_date := p_fpd;
1001: hr_utility.set_message(801,'HR_6200_EMP_ASS_TERM_EXISTS');
1002: hr_utility.raise_error;
1003: ELSE
1004: IF p_first_term_date <= p_start_date THEN
1005: hr_utility.set_location('hr_assignment.check_term',17);
1006: p_new_ass_end_date := p_fpd;
1007: ELSE
1008: IF p_next_eff_end_date = p_ass_end_date THEN
1009: hr_utility.set_location('hr_assignment.check_term',18);
1005: hr_utility.set_location('hr_assignment.check_term',17);
1006: p_new_ass_end_date := p_fpd;
1007: ELSE
1008: IF p_next_eff_end_date = p_ass_end_date THEN
1009: hr_utility.set_location('hr_assignment.check_term',18);
1010: IF p_ass_end_date > p_atd THEN
1011: p_new_ass_end_date := p_atd;
1012: ELSE
1013: NULL;
1013: NULL;
1014: END IF; -- (p_ass_end_date > p_atd)
1015: ELSE
1016: IF p_next_eff_end_date >= p_atd + 1 THEN
1017: hr_utility.set_message(801,'HR_6320_EMP_ASS_AFTER_ATD');
1018: hr_utility.raise_error;
1019: END IF; -- (p_next_eff_end_date >= p_atd + 1)
1020: END IF; -- (p_next_eff_end_date = p_ass_end_date)
1021: END IF; -- (p_first_term_date <= p_start_date)
1014: END IF; -- (p_ass_end_date > p_atd)
1015: ELSE
1016: IF p_next_eff_end_date >= p_atd + 1 THEN
1017: hr_utility.set_message(801,'HR_6320_EMP_ASS_AFTER_ATD');
1018: hr_utility.raise_error;
1019: END IF; -- (p_next_eff_end_date >= p_atd + 1)
1020: END IF; -- (p_next_eff_end_date = p_ass_end_date)
1021: END IF; -- (p_first_term_date <= p_start_date)
1022: END IF; -- (p_next_eff_start_date = p_atd + 1)
1026: END IF; -- (p_mode = 'UPDATE_OVERRIDE')
1027: --
1028: END IF; -- (p_atd IS NULL)
1029: --
1030: hr_utility.set_location('hr_assignment.check_term',19);
1031: IF p_new_ass_end_date IS NOT NULL THEN
1032: ------------------------------------------------------------
1033: -- First check whether setting this end date will invalidate
1034: -- any child rows.
1074: p_term_found := 'N';
1075: --
1076: begin
1077: --
1078: hr_utility.set_location('hr_assignment.warn_del_term',1);
1079: --
1080: select 'Y'
1081: into p_term_found
1082: from sys.dual
1097: exception
1098: when NO_DATA_FOUND then null;
1099: end;
1100: --
1101: hr_utility.set_location('hr_assignment.warn_del_term',2);
1102: if p_term_found = 'Y' then
1103: raise local_warning;
1104: end if;
1105: --
1104: end if;
1105: --
1106: EXCEPTION
1107: when local_warning then
1108: hr_utility.set_warning;
1109: END warn_del_term;
1110: --
1111: --
1112: ------------------- delete_ass_ref_int ----------------------------
1136: --
1137: -- del_flag := 'N';
1138: --
1139: BEGIN
1140: hr_utility.set_location('hr_assignment.delete_ass_ref_int',1);
1141: --
1142: SELECT 'Y'
1143: into del_flag
1144: FROM SYS.DUAL
1152: WHEN NO_DATA_FOUND THEN NULL;
1153: END;
1154: --
1155: IF del_flag = 'Y' THEN
1156: hr_utility.set_location('hr_assignment.delete_ass_ref_int',2);
1157: --
1158: DELETE FROM PER_SPINAL_POINT_PLACEMENTS_F P
1159: WHERE P.business_group_id + 0 = p_business_group_id
1160: AND P.ASSIGNMENT_ID = p_assignment_id;
1164: del_flag := 'N';
1165: --
1166: BEGIN
1167: --
1168: hr_utility.set_location('hr_assignment.delete_ass_ref_int',3);
1169: --
1170: SELECT 'Y'
1171: into del_flag
1172: from sys.dual
1180: WHEN NO_DATA_FOUND THEN NULL;
1181: END;
1182: --
1183: IF del_flag = 'Y' THEN
1184: hr_utility.set_location('hr_assignment.delete_ass_ref_int',4);
1185: --
1186: DELETE FROM PER_SECONDARY_ASS_STATUSES
1187: WHERE business_group_id + 0 = p_business_group_id
1188: AND ASSIGNMENT_ID = p_assignment_id;
1191: --
1192: del_flag := 'N';
1193: --
1194: BEGIN
1195: hr_utility.set_location('hr_assignment.delete_ass_ref_int',5);
1196: --
1197: SELECT 'Y'
1198: into del_flag
1199: from sys.dual
1207: WHEN NO_DATA_FOUND THEN NULL;
1208: END;
1209: --
1210: IF del_flag = 'Y' THEN
1211: hr_utility.set_location('hr_assignment.delete_ass_ref_int',6);
1212: --
1213: DELETE FROM PER_ASSIGNMENT_BUDGET_VALUES_F BV
1214: WHERE BV.business_group_id + 0 = p_business_group_id
1215: AND BV.ASSIGNMENT_ID = p_assignment_id;
1240: --
1241: -------------------------------------------------
1242: -- Retrieve the ACTUAL TERMINATION DATE for the Period of Service
1243: -------------------------------------------------
1244: hr_utility.set_location('hr_assignment.get_act_term_date',1);
1245: --
1246: select actual_termination_date
1247: into p_actual_termination_date
1248: from per_periods_of_service
1299:
1300: --
1301: BEGIN
1302: --
1303: hr_utility.set_location('hr_assignment.check_future_primary',1);
1304: p_start_date := p_sdate;
1305: p_end_date := p_edate;
1306: --
1307: -------------------------------------
1333: -- NEXT_CHANGE - the next row
1334: -- FUTURE_CHANGES and UPDATE_OVERRIDE - All rows in future
1335: -------------------------------------
1336: begin
1337: hr_utility.set_location('hr_assignment.check_future_primary',2);
1338: --
1339: select 'Y'
1340: into p_change_flag
1341: from sys.dual
1358: -- retrieve the earliest occurrence of PRIMARY_FLAG = 'Y'. A new
1359: -- Primary Assignment will be required from this date.
1360: ---------------------------------------
1361: IF p_mode = 'ZAP' AND p_change_flag = 'Y' THEN
1362: hr_utility.set_location('hr_assignment.check_future_primary',3);
1363: --
1364: select min(effective_start_date)
1365: into p_primary_date_from_d
1366: from per_assignments_f
1447:
1448: -------------------------------------
1449: -- Get the Actual Termination Date and Final Process Date
1450: -------------------------------------
1451: hr_utility.set_location('hr_assignment.check_ass_for_primary',1);
1452: --
1453: -- #306211. If ATD was null, then NVL to EOT - 1, instead of EOT. This is
1454: -- because trying to add 1 to EOT (as happens in a number of places below)
1455: -- raises an ORA-1841 error. So use Dec 30 instead of Dec 31.
1461:
1462: -------------------------------------
1463: -- Get the Effective End Date of the Assignment
1464: -------------------------------------
1465: hr_utility.set_location('hr_assignment.check_ass_for_primary',2);
1466: --
1467: select max(effective_end_date)
1468: into p_ass_end_date
1469: from per_assignments_f
1472: -- If the end date of the assignment is not on or after the
1473: -- period of service final process date then ERROR
1474: --
1475: IF p_ass_end_date < p_fpd THEN
1476: hr_utility.set_message(801,'HR_6380_EMP_ASS_END_OFF_FPD');
1477: hr_utility.raise_error;
1478: END IF;
1479: --
1480: -------------------------------------
1473: -- period of service final process date then ERROR
1474: --
1475: IF p_ass_end_date < p_fpd THEN
1476: hr_utility.set_message(801,'HR_6380_EMP_ASS_END_OFF_FPD');
1477: hr_utility.raise_error;
1478: END IF;
1479: --
1480: -------------------------------------
1481: -- If the Start Date of the record is on or before the day after ATD
1487: ELSE
1488: -------------------------------------
1489: -- Get the Start Date of the First terminated status.
1490: -------------------------------------
1491: hr_utility.set_location('hr_assignment.check_ass_for_primary',3);
1492: --
1493: if l_assignment_type <> 'C' then
1494:
1495: select min(a.effective_start_date)
1501: where s.assignment_status_type_id
1502: = a.assignment_status_type_id
1503: and s.per_system_status = 'TERM_ASSIGN');
1504: --
1505: hr_utility.set_location('hr_assignment.check_ass_for_primary',4);
1506: IF p_first_term_date = p_atd + 1
1507: OR
1508: p_first_term_date IS NULL THEN NULL;
1509: ELSE
1506: IF p_first_term_date = p_atd + 1
1507: OR
1508: p_first_term_date IS NULL THEN NULL;
1509: ELSE
1510: hr_utility.set_message(801,'HR_6381_EMP_ASS_TERM_OFF_ATD');
1511: hr_utility.raise_error;
1512: END IF;
1513: END IF;
1514: END IF;
1507: OR
1508: p_first_term_date IS NULL THEN NULL;
1509: ELSE
1510: hr_utility.set_message(801,'HR_6381_EMP_ASS_TERM_OFF_ATD');
1511: hr_utility.raise_error;
1512: END IF;
1513: END IF;
1514: END IF;
1515: --
1542: AND effective_end_date >= l_start_date;
1543: --
1544: BEGIN
1545: --
1546: hr_utility.set_location('Entering : '||l_proc,10);
1547: --
1548: l_start_date := p_sdate;
1549: --
1550: -- Update the future changes for the current
1551: -- assignment with the new Primary Flag
1552: --
1553: IF p_mode <> 'ZAP' THEN
1554: --
1555: hr_utility.set_location(l_proc,20);
1556: --
1557: ----------------------------------------------------------------
1558: -- 3584122: The value of 'N' is not updating the future
1559: -- changes of the secondar_assignment to primary if a
1570: ,p_last_update_login);
1571: --
1572: END IF;
1573: --
1574: hr_utility.set_location(l_proc,30);
1575: --
1576: -- If the New Primary Asg is not the Current Asg then the Primary
1577: -- Flag has to be set to 'Y' on all the changes on or after the
1578: -- Start Date
1578: -- Start Date
1579: --
1580: IF p_assignment_id <> p_new_primary_ass_id THEN
1581: --
1582: hr_utility.set_location(l_proc,40);
1583: --
1584: do_primary_update
1585: (p_new_primary_ass_id
1586: ,p_sdate
1590: ,p_last_update_login);
1591: --
1592: END IF;
1593: --
1594: hr_utility.set_location(l_proc,50);
1595: --
1596: -- Now for each assignment other than P_ASSIGNMENT_ID and
1597: -- P_NEW_PRIMARY_ASS_ID within the period_of_service
1598: -- future changes must have their Primary Flag set to 'N'. It is only
1609: ,p_last_update_login);
1610: --
1611: END LOOP;
1612: --
1613: hr_utility.set_location(' Leaving : '||l_proc,999);
1614: --
1615: END update_primary_cwk;
1616: --
1617: --
1681: -- back to p_new_primary_flag.
1682: -------------------------------------
1683: --
1684: IF p_mode <> 'ZAP' THEN
1685: hr_utility.set_location('hr_assignment.update_primary',1);
1686: do_primary_update(p_assignment_id
1687: ,p_sdate
1688: ,p_new_primary_flag -- Bug 3584122 'N'-- Bug 2468916 p_new_primary_flag
1689: ,'Y'
1697: -- Flag has to be set to 'Y' on all the changes on or after the
1698: -- Start Date
1699: -------------------------------------
1700: IF p_assignment_id <> p_new_primary_ass_id THEN
1701: hr_utility.set_location('hr_assignment.update_primary',2);
1702: do_primary_update(p_new_primary_ass_id
1703: ,p_sdate
1704: ,'Y'
1705: ,'N'
1713: -- P_NEW_PRIMARY_ASS_ID within the period_of_service
1714: -- future changes must have their Primary Flag set to 'N'. It is only
1715: -- necessary to update the ones that are currently 'Y'.
1716: -------------------------------------
1717: hr_utility.set_location('hr_assignment.update_primary',3);
1718: --
1719: FOR ass_rec IN get_future_primary_assignments LOOP
1720: do_primary_update(ass_rec.assignment_id
1721: ,p_sdate
1771: and P_START_DATE < effective_start_date))
1772: for update;
1773: --
1774: BEGIN
1775: hr_utility.set_location('hr_assignment.do_primary_update',1);
1776: p_start_date := p_sdate;
1777: --
1778: -------------------------------------
1779: -- If the Assignment is Current i.e. P_CURRENT_ASS = 'Y' then the form
1808: --
1809: -- See CURSOR select_ass_for_update
1810: ----------------------------------------------------------------------
1811: --
1812: hr_utility.set_location('hr_assignment.do_primary_update',2);
1813: --
1814: FOR ass_rec IN select_ass_for_update LOOP
1815: NULL;
1816: END LOOP;
1817: --
1818: IF p_current_ass = 'Y' THEN
1819: NULL;
1820: ELSE
1821: hr_utility.set_location('hr_assignment.do_primary_update',3);
1822: --
1823: update per_assignments_f
1824: set primary_flag = P_PRIMARY_FLAG
1825: , last_updated_by = P_LAST_UPDATED_BY
1827: , last_update_date = sysdate
1828: where assignment_id = P_ASSIGNMENT_ID
1829: and effective_start_date = P_START_DATE;
1830: --
1831: hr_utility.set_location('hr_assignment.do_primary_update',4);
1832: IF SQL%ROWCOUNT = 1 THEN
1833: NULL;
1834: ELSIF SQL%ROWCOUNT > 1 THEN
1835: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
1831: hr_utility.set_location('hr_assignment.do_primary_update',4);
1832: IF SQL%ROWCOUNT = 1 THEN
1833: NULL;
1834: ELSIF SQL%ROWCOUNT > 1 THEN
1835: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
1836: hr_utility.set_message_token('PROCEDURE','DO_PRIMARY_UPDATE');
1837: hr_utility.set_message_token('STEP','1');
1838: hr_utility.raise_error;
1839: ELSE
1832: IF SQL%ROWCOUNT = 1 THEN
1833: NULL;
1834: ELSIF SQL%ROWCOUNT > 1 THEN
1835: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
1836: hr_utility.set_message_token('PROCEDURE','DO_PRIMARY_UPDATE');
1837: hr_utility.set_message_token('STEP','1');
1838: hr_utility.raise_error;
1839: ELSE
1840: --
1833: NULL;
1834: ELSIF SQL%ROWCOUNT > 1 THEN
1835: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
1836: hr_utility.set_message_token('PROCEDURE','DO_PRIMARY_UPDATE');
1837: hr_utility.set_message_token('STEP','1');
1838: hr_utility.raise_error;
1839: ELSE
1840: --
1841: hr_utility.set_location('hr_assignment.do_primary_update',5);
1834: ELSIF SQL%ROWCOUNT > 1 THEN
1835: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
1836: hr_utility.set_message_token('PROCEDURE','DO_PRIMARY_UPDATE');
1837: hr_utility.set_message_token('STEP','1');
1838: hr_utility.raise_error;
1839: ELSE
1840: --
1841: hr_utility.set_location('hr_assignment.do_primary_update',5);
1842: --
1837: hr_utility.set_message_token('STEP','1');
1838: hr_utility.raise_error;
1839: ELSE
1840: --
1841: hr_utility.set_location('hr_assignment.do_primary_update',5);
1842: --
1843: insert into per_assignments_f
1844: (
1845: ASSIGNMENT_ID
2066: --
2067: IF SQL%ROWCOUNT = 0 THEN
2068: NULL; -- This Assignment Start in the Future
2069: ELSE
2070: hr_utility.set_location('hr_assignment.do_primary_update',6);
2071: --
2072: update per_assignments_f
2073: set effective_start_date = P_START_DATE
2074: , primary_flag = P_PRIMARY_FLAG
2079: and P_START_DATE
2080: between effective_start_date and effective_end_date
2081: and primary_flag <> P_PRIMARY_FLAG;
2082: --
2083: hr_utility.set_location('hr_assignment.do_primary_update',7);
2084: IF SQL%ROWCOUNT <> 1 THEN
2085: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2086: hr_utility.set_message_token('PROCEDURE','DO_PRIMARY_UPDATE');
2087: hr_utility.set_message_token('STEP','3');
2081: and primary_flag <> P_PRIMARY_FLAG;
2082: --
2083: hr_utility.set_location('hr_assignment.do_primary_update',7);
2084: IF SQL%ROWCOUNT <> 1 THEN
2085: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2086: hr_utility.set_message_token('PROCEDURE','DO_PRIMARY_UPDATE');
2087: hr_utility.set_message_token('STEP','3');
2088: hr_utility.raise_error;
2089: END IF; -- (SQL%ROWCOUNT <> 1)
2082: --
2083: hr_utility.set_location('hr_assignment.do_primary_update',7);
2084: IF SQL%ROWCOUNT <> 1 THEN
2085: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2086: hr_utility.set_message_token('PROCEDURE','DO_PRIMARY_UPDATE');
2087: hr_utility.set_message_token('STEP','3');
2088: hr_utility.raise_error;
2089: END IF; -- (SQL%ROWCOUNT <> 1)
2090: END IF; -- (SQL%ROWCOUNT = 0)
2083: hr_utility.set_location('hr_assignment.do_primary_update',7);
2084: IF SQL%ROWCOUNT <> 1 THEN
2085: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2086: hr_utility.set_message_token('PROCEDURE','DO_PRIMARY_UPDATE');
2087: hr_utility.set_message_token('STEP','3');
2088: hr_utility.raise_error;
2089: END IF; -- (SQL%ROWCOUNT <> 1)
2090: END IF; -- (SQL%ROWCOUNT = 0)
2091: END IF; -- (SQL%ROWCOUNT = 1)
2084: IF SQL%ROWCOUNT <> 1 THEN
2085: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2086: hr_utility.set_message_token('PROCEDURE','DO_PRIMARY_UPDATE');
2087: hr_utility.set_message_token('STEP','3');
2088: hr_utility.raise_error;
2089: END IF; -- (SQL%ROWCOUNT <> 1)
2090: END IF; -- (SQL%ROWCOUNT = 0)
2091: END IF; -- (SQL%ROWCOUNT = 1)
2092: END IF; -- (p_current_ass = 'Y')
2090: END IF; -- (SQL%ROWCOUNT = 0)
2091: END IF; -- (SQL%ROWCOUNT = 1)
2092: END IF; -- (p_current_ass = 'Y')
2093: --
2094: hr_utility.set_location('hr_assignment.do_primary_update',8);
2095: --
2096: update per_assignments_f
2097: set primary_flag = P_PRIMARY_FLAG
2098: , last_updated_by = P_LAST_UPDATED_BY
2217: BEGIN
2218: --
2219: p_start_date := p_sdate;
2220:
2221: hr_utility.set_location('hr_assignment.get_new_primary_assignment',1);
2222:
2223: --
2224: -- Fetch the desired assignment details.
2225: --
2237: ,p_fpd;
2238: CLOSE csr_get_term_dates;
2239:
2240: --
2241: hr_utility.set_location('hr_assignment.get_new_primary_assignment',2);
2242: --
2243: ---------------------------------------------------
2244: -- open the cursor and read the first record if one exists
2245: -- If one doesn't exists then ERROR
2245: -- If one doesn't exists then ERROR
2246: -- Try and read another record
2247: -- If one exists then WARNING (prompt user in Form for which one)
2248: ---------------------------------------------------
2249: hr_utility.set_location('hr_assignment.get_new_primary_assignment',3);
2250: OPEN get_candidate_primary_ass;
2251: --
2252: hr_utility.set_location('hr_assignment.get_new_primary_assignment',4);
2253: FETCH get_candidate_primary_ass INTO p_new_primary_ass_id;
2248: ---------------------------------------------------
2249: hr_utility.set_location('hr_assignment.get_new_primary_assignment',3);
2250: OPEN get_candidate_primary_ass;
2251: --
2252: hr_utility.set_location('hr_assignment.get_new_primary_assignment',4);
2253: FETCH get_candidate_primary_ass INTO p_new_primary_ass_id;
2254: --
2255: IF get_candidate_primary_ass%NOTFOUND THEN
2256: hr_utility.set_location('hr_assignment.get_new_primary_assignment',5);
2252: hr_utility.set_location('hr_assignment.get_new_primary_assignment',4);
2253: FETCH get_candidate_primary_ass INTO p_new_primary_ass_id;
2254: --
2255: IF get_candidate_primary_ass%NOTFOUND THEN
2256: hr_utility.set_location('hr_assignment.get_new_primary_assignment',5);
2257: CLOSE get_candidate_primary_ass;
2258: hr_utility.set_message(801,'HR_6384_EMP_ASS_NO_PRIM');
2259: hr_utility.raise_error;
2260: ELSE
2254: --
2255: IF get_candidate_primary_ass%NOTFOUND THEN
2256: hr_utility.set_location('hr_assignment.get_new_primary_assignment',5);
2257: CLOSE get_candidate_primary_ass;
2258: hr_utility.set_message(801,'HR_6384_EMP_ASS_NO_PRIM');
2259: hr_utility.raise_error;
2260: ELSE
2261: hr_utility.set_location('hr_assignment.get_new_primary_assignment',7);
2262: FETCH get_candidate_primary_ass INTO p_new_primary_ass_id;
2255: IF get_candidate_primary_ass%NOTFOUND THEN
2256: hr_utility.set_location('hr_assignment.get_new_primary_assignment',5);
2257: CLOSE get_candidate_primary_ass;
2258: hr_utility.set_message(801,'HR_6384_EMP_ASS_NO_PRIM');
2259: hr_utility.raise_error;
2260: ELSE
2261: hr_utility.set_location('hr_assignment.get_new_primary_assignment',7);
2262: FETCH get_candidate_primary_ass INTO p_new_primary_ass_id;
2263: --
2257: CLOSE get_candidate_primary_ass;
2258: hr_utility.set_message(801,'HR_6384_EMP_ASS_NO_PRIM');
2259: hr_utility.raise_error;
2260: ELSE
2261: hr_utility.set_location('hr_assignment.get_new_primary_assignment',7);
2262: FETCH get_candidate_primary_ass INTO p_new_primary_ass_id;
2263: --
2264: hr_utility.set_location('hr_assignment.get_new_primary_assignment',8);
2265: IF get_candidate_primary_ass%FOUND THEN
2260: ELSE
2261: hr_utility.set_location('hr_assignment.get_new_primary_assignment',7);
2262: FETCH get_candidate_primary_ass INTO p_new_primary_ass_id;
2263: --
2264: hr_utility.set_location('hr_assignment.get_new_primary_assignment',8);
2265: IF get_candidate_primary_ass%FOUND THEN
2266: raise local_warning;
2267: END IF;
2268: --
2265: IF get_candidate_primary_ass%FOUND THEN
2266: raise local_warning;
2267: END IF;
2268: --
2269: hr_utility.set_location('hr_assignment.get_new_primary_assignment',9);
2270: CLOSE get_candidate_primary_ass;
2271: END IF;
2272: --
2273: EXCEPTION
2272: --
2273: EXCEPTION
2274: when local_warning then
2275: p_new_primary_ass_id := null ;
2276: hr_utility.set_warning;
2277:
2278: when others then
2279: p_new_primary_ass_id := null;
2280: raise;
2306: ,p_effective_end_date IN DATE) IS
2307:
2308: --
2309: BEGIN
2310: hr_utility.set_location('hr_assignment.load_budget_values',1);
2311: --
2312: /* 25/05/95 Fixed bug 273820 - performance of following statement
2313: NB the business_group_id no longer needs +0 appended to it, this
2314: is because the view per_default_budget_values is in fact returning
2409: p_end_date := p_edate;
2410: --
2411: --
2412: IF p_mode = 'ZAP' THEN
2413: hr_utility.set_location('hr_assignment.del_ref_int_check',0);
2414: p_del_flag := 'N';
2415: --
2416: BEGIN
2417: select 'Y'
2437: WHEN NO_DATA_FOUND THEN NULL;
2438: END;
2439: --
2440: IF p_del_flag = 'Y' THEN
2441: hr_utility.set_message(801,'HR_7625_ASS_DEL_APP_ASS');
2442: hr_utility.raise_error;
2443: END IF;
2444: END IF;
2445: --
2438: END;
2439: --
2440: IF p_del_flag = 'Y' THEN
2441: hr_utility.set_message(801,'HR_7625_ASS_DEL_APP_ASS');
2442: hr_utility.raise_error;
2443: END IF;
2444: END IF;
2445: --
2446: hr_utility.set_location('hr_assignment.del_ref_int_check',1);
2442: hr_utility.raise_error;
2443: END IF;
2444: END IF;
2445: --
2446: hr_utility.set_location('hr_assignment.del_ref_int_check',1);
2447: p_del_flag := 'N';
2448: --
2449: BEGIN
2450: select 'Y'
2462: END;
2463: --
2464: IF p_del_flag = 'Y' THEN
2465: IF p_mode = 'ZAP' THEN
2466: hr_utility.set_message(801,'HR_7630_ASS_EVE_DEL_ASS');
2467: ELSE
2468: hr_utility.set_message(801,'HR_7633_ASS_EVE_END_ASS');
2469: END IF;
2470: hr_utility.raise_error;
2464: IF p_del_flag = 'Y' THEN
2465: IF p_mode = 'ZAP' THEN
2466: hr_utility.set_message(801,'HR_7630_ASS_EVE_DEL_ASS');
2467: ELSE
2468: hr_utility.set_message(801,'HR_7633_ASS_EVE_END_ASS');
2469: END IF;
2470: hr_utility.raise_error;
2471: END IF;
2472: --
2466: hr_utility.set_message(801,'HR_7630_ASS_EVE_DEL_ASS');
2467: ELSE
2468: hr_utility.set_message(801,'HR_7633_ASS_EVE_END_ASS');
2469: END IF;
2470: hr_utility.raise_error;
2471: END IF;
2472: --
2473: --
2474: /* Took out nocopy the check on letter requests as they are now Auto Deleted
2474: /* Took out nocopy the check on letter requests as they are now Auto Deleted
2475: - 2/6/93
2476: */
2477: /*
2478: hr_utility.set_location('hr_assignment.del_ref_int_check',2);
2479: p_del_flag := 'N';
2480: --
2481: BEGIN
2482: select 'Y'
2494: END;
2495: --
2496: IF p_del_flag = 'Y' THEN
2497: IF p_mode = 'ZAP' THEN
2498: hr_utility.set_message(801,'HR_7634_ASS_LET_DEL_ASS');
2499: ELSE
2500: hr_utility.set_message(801,'HR_7637_ASS_EVE_END_ASS');
2501: END IF;
2502: hr_utility.raise_error;
2496: IF p_del_flag = 'Y' THEN
2497: IF p_mode = 'ZAP' THEN
2498: hr_utility.set_message(801,'HR_7634_ASS_LET_DEL_ASS');
2499: ELSE
2500: hr_utility.set_message(801,'HR_7637_ASS_EVE_END_ASS');
2501: END IF;
2502: hr_utility.raise_error;
2503: END IF;
2504: */
2498: hr_utility.set_message(801,'HR_7634_ASS_LET_DEL_ASS');
2499: ELSE
2500: hr_utility.set_message(801,'HR_7637_ASS_EVE_END_ASS');
2501: END IF;
2502: hr_utility.raise_error;
2503: END IF;
2504: */
2505: --
2506: --
2503: END IF;
2504: */
2505: --
2506: --
2507: hr_utility.set_location('hr_assignment.del_ref_int_check',3);
2508: p_del_flag := 'N';
2509: --
2510: BEGIN
2511: select 'Y'
2523: END;
2524: --
2525: IF p_del_flag = 'Y' THEN
2526: IF p_mode = 'ZAP' THEN
2527: hr_utility.set_message(801,'HR_7638_ASS_COST_DEL_ASS');
2528: ELSE
2529: hr_utility.set_message(801,'HR_7641_ASS_COST_END_ASS');
2530: END IF;
2531: hr_utility.raise_error;
2525: IF p_del_flag = 'Y' THEN
2526: IF p_mode = 'ZAP' THEN
2527: hr_utility.set_message(801,'HR_7638_ASS_COST_DEL_ASS');
2528: ELSE
2529: hr_utility.set_message(801,'HR_7641_ASS_COST_END_ASS');
2530: END IF;
2531: hr_utility.raise_error;
2532: END IF;
2533: --
2527: hr_utility.set_message(801,'HR_7638_ASS_COST_DEL_ASS');
2528: ELSE
2529: hr_utility.set_message(801,'HR_7641_ASS_COST_END_ASS');
2530: END IF;
2531: hr_utility.raise_error;
2532: END IF;
2533: --
2534: --
2535: -- 03/18/1998 Bug #642566
2537: -- as per_assignment_extra_info are now deleted along with other
2538: -- assignment related records.
2539: --
2540: --
2541: hr_utility.set_location('hr_assignment.del_ref_int_check',5);
2542: p_del_flag := 'N';
2543: /*
2544: N.B. PER_SECONDARY_ASS_STATUSES rows will now be deleted if they
2545: started after the Assignment End Date - changed 27/5/93.
2558: END;
2559: --
2560: IF p_del_flag = 'Y' THEN
2561: IF p_mode = 'ZAP' THEN
2562: hr_utility.set_message(801,'HR_7652_ASS_STAT_DEL_ASS');
2563: ELSE
2564: hr_utility.set_message(801,'HR_7655_ASS_SATA_END_ASS');
2565: END IF;
2566: hr_utility.raise_error;
2560: IF p_del_flag = 'Y' THEN
2561: IF p_mode = 'ZAP' THEN
2562: hr_utility.set_message(801,'HR_7652_ASS_STAT_DEL_ASS');
2563: ELSE
2564: hr_utility.set_message(801,'HR_7655_ASS_SATA_END_ASS');
2565: END IF;
2566: hr_utility.raise_error;
2567: END IF;
2568: */
2562: hr_utility.set_message(801,'HR_7652_ASS_STAT_DEL_ASS');
2563: ELSE
2564: hr_utility.set_message(801,'HR_7655_ASS_SATA_END_ASS');
2565: END IF;
2566: hr_utility.raise_error;
2567: END IF;
2568: */
2569: --
2570: --
2567: END IF;
2568: */
2569: --
2570: --
2571: hr_utility.set_location('hr_assignment.del_ref_int_check',6);
2572: p_del_flag := 'N';
2573:
2574: /* 2537091: PPMs will be deleted if they started after end date of assignment
2575: changed 04-OCT-2002
2590: END;
2591: --
2592: IF p_del_flag = 'Y' THEN
2593: IF p_mode = 'ZAP' THEN
2594: hr_utility.set_message(801,'HR_7656_ASS_PAY_DEL_ASS');
2595: ELSE
2596: hr_utility.set_message(801,'HR_7659_ASS_PAY_END_ASS');
2597: END IF;
2598: hr_utility.raise_error;
2592: IF p_del_flag = 'Y' THEN
2593: IF p_mode = 'ZAP' THEN
2594: hr_utility.set_message(801,'HR_7656_ASS_PAY_DEL_ASS');
2595: ELSE
2596: hr_utility.set_message(801,'HR_7659_ASS_PAY_END_ASS');
2597: END IF;
2598: hr_utility.raise_error;
2599: END IF;
2600:
2594: hr_utility.set_message(801,'HR_7656_ASS_PAY_DEL_ASS');
2595: ELSE
2596: hr_utility.set_message(801,'HR_7659_ASS_PAY_END_ASS');
2597: END IF;
2598: hr_utility.raise_error;
2599: END IF;
2600:
2601: */
2602: --
2600:
2601: */
2602: --
2603: --
2604: hr_utility.set_location('hr_assignment.del_ref_int_check',7);
2605: p_del_flag := 'N';
2606: --
2607: /* BEGIN
2608: select 'Y'
2657: END IF;
2658: --
2659: IF p_del_flag = 'Y' THEN
2660: IF p_mode = 'ZAP' THEN
2661: hr_utility.set_message(801,'HR_7664_ASS_ASS_DEL_ASS');
2662: ELSE
2663: hr_utility.set_message(801,'HR_7667_ASS_ASS_END_ASS');
2664: END IF;
2665: hr_utility.raise_error;
2659: IF p_del_flag = 'Y' THEN
2660: IF p_mode = 'ZAP' THEN
2661: hr_utility.set_message(801,'HR_7664_ASS_ASS_DEL_ASS');
2662: ELSE
2663: hr_utility.set_message(801,'HR_7667_ASS_ASS_END_ASS');
2664: END IF;
2665: hr_utility.raise_error;
2666: END IF;
2667: --
2661: hr_utility.set_message(801,'HR_7664_ASS_ASS_DEL_ASS');
2662: ELSE
2663: hr_utility.set_message(801,'HR_7667_ASS_ASS_END_ASS');
2664: END IF;
2665: hr_utility.raise_error;
2666: END IF;
2667: --
2668: --
2669: IF p_mode = 'ZAP' THEN
2667: --
2668: --
2669: IF p_mode = 'ZAP' THEN
2670: --
2671: hr_utility.set_location('hr_assignment.del_ref_int_check',8);
2672: p_del_flag := 'N';
2673: --
2674: BEGIN
2675: select 'Y'
2690: WHEN NO_DATA_FOUND THEN NULL;
2691: END;
2692: --
2693: IF p_del_flag = 'Y' THEN
2694: hr_utility.set_message(801,'HR_6305_ALL_ASSGT_SET_NO_DEL');
2695: hr_utility.raise_error;
2696: END IF;
2697: END IF;
2698: ------------------------------
2691: END;
2692: --
2693: IF p_del_flag = 'Y' THEN
2694: hr_utility.set_message(801,'HR_6305_ALL_ASSGT_SET_NO_DEL');
2695: hr_utility.raise_error;
2696: END IF;
2697: END IF;
2698: ------------------------------
2699: -- Cobra Coverage Enrollments
2697: END IF;
2698: ------------------------------
2699: -- Cobra Coverage Enrollments
2700: --
2701: hr_utility.set_location('hr_assignment.del_ref_int_check',9);
2702: p_del_flag := 'N';
2703: --
2704: BEGIN
2705: select 'Y'
2717: END;
2718: --
2719: IF p_del_flag = 'Y' THEN
2720: IF p_mode = 'ZAP' THEN
2721: hr_utility.set_message(801,'HR_7672_ASS_COBRA_DEL_ASS');
2722: ELSE
2723: hr_utility.set_message(801,'HR_7675_ASS_COBRA_END_ASS');
2724: END IF;
2725: hr_utility.raise_error;
2719: IF p_del_flag = 'Y' THEN
2720: IF p_mode = 'ZAP' THEN
2721: hr_utility.set_message(801,'HR_7672_ASS_COBRA_DEL_ASS');
2722: ELSE
2723: hr_utility.set_message(801,'HR_7675_ASS_COBRA_END_ASS');
2724: END IF;
2725: hr_utility.raise_error;
2726: END IF;
2727: ------------------------------
2721: hr_utility.set_message(801,'HR_7672_ASS_COBRA_DEL_ASS');
2722: ELSE
2723: hr_utility.set_message(801,'HR_7675_ASS_COBRA_END_ASS');
2724: END IF;
2725: hr_utility.raise_error;
2726: END IF;
2727: ------------------------------
2728: -- Cobra Coverage Benefits
2729: --
2726: END IF;
2727: ------------------------------
2728: -- Cobra Coverage Benefits
2729: --
2730: hr_utility.set_location('hr_assignment.del_ref_int_check',9);
2731: p_del_flag := 'N';
2732: --
2733: BEGIN
2734: select 'Y'
2750: END;
2751: --
2752: IF p_del_flag = 'Y' THEN
2753: IF p_mode = 'ZAP' THEN
2754: hr_utility.set_message(801,'HR_7668_ASS_COBR_DEL_ASS');
2755: ELSE
2756: hr_utility.set_message(801,'HR_7671_ASS_COBRA_END_ASS');
2757: END IF;
2758: hr_utility.raise_error;
2752: IF p_del_flag = 'Y' THEN
2753: IF p_mode = 'ZAP' THEN
2754: hr_utility.set_message(801,'HR_7668_ASS_COBR_DEL_ASS');
2755: ELSE
2756: hr_utility.set_message(801,'HR_7671_ASS_COBRA_END_ASS');
2757: END IF;
2758: hr_utility.raise_error;
2759: END IF;
2760: --
2754: hr_utility.set_message(801,'HR_7668_ASS_COBR_DEL_ASS');
2755: ELSE
2756: hr_utility.set_message(801,'HR_7671_ASS_COBRA_END_ASS');
2757: END IF;
2758: hr_utility.raise_error;
2759: END IF;
2760: --
2761: ------------------------------
2762: -- OTA_DELEGATE_BOOKINGS
2760: --
2761: ------------------------------
2762: -- OTA_DELEGATE_BOOKINGS
2763: --
2764: hr_utility.set_location('hr_assignment.del_ref_int_check',10);
2765: --
2766: p_del_flag := 'N';
2767: --
2768: BEGIN
2793: END;
2794: --
2795: IF p_del_flag = 'Y' THEN
2796: IF p_mode = 'ZAP' THEN
2797: hr_utility.set_message(801,'HR_52281_ASS_TAX_DEL_ASS');
2798: ELSE
2799: hr_utility.set_message(801,'HR_52280_ASS_TAX_END_ASS');
2800: END IF;
2801: hr_utility.raise_error;
2795: IF p_del_flag = 'Y' THEN
2796: IF p_mode = 'ZAP' THEN
2797: hr_utility.set_message(801,'HR_52281_ASS_TAX_DEL_ASS');
2798: ELSE
2799: hr_utility.set_message(801,'HR_52280_ASS_TAX_END_ASS');
2800: END IF;
2801: hr_utility.raise_error;
2802: END IF;
2803:
2797: hr_utility.set_message(801,'HR_52281_ASS_TAX_DEL_ASS');
2798: ELSE
2799: hr_utility.set_message(801,'HR_52280_ASS_TAX_END_ASS');
2800: END IF;
2801: hr_utility.raise_error;
2802: END IF;
2803:
2804: ------------------------------
2805: IF p_mode = 'ZAP' THEN
2805: IF p_mode = 'ZAP' THEN
2806: --
2807: -- OTA_DELEGATE_BOOKINGS
2808:
2809: hr_utility.set_location('hr_assignment.del_ref_int_check',11);
2810: per_ota_predel_validation.ota_predel_asg_validation(P_ASSIGNMENT_ID);
2811: END IF;
2812: ---
2813: END del_ref_int_check;
2940: --
2941: --
2942: p_end_date := p_edate;
2943: --
2944: hr_utility.set_location('hr_assignment.del_ref_int_delete',1);
2945: p_del_flag := 'N';
2946: --
2947: BEGIN
2948: --
2968: --
2969: EXCEPTION
2970: WHEN NO_DATA_FOUND THEN NULL;
2971: END;
2972: hr_utility.set_location('p_del_flag :'||p_del_flag,2);
2973: hr_utility.set_location('p_mode :'||p_mode,2);
2974: hr_utility.set_location('p_edate :'||p_edate,2);
2975: hr_utility.set_location('p_val_st_date: '||p_val_st_date,2);
2976: hr_utility.set_location('p_val_end_date: '||p_val_end_date,2);
2969: EXCEPTION
2970: WHEN NO_DATA_FOUND THEN NULL;
2971: END;
2972: hr_utility.set_location('p_del_flag :'||p_del_flag,2);
2973: hr_utility.set_location('p_mode :'||p_mode,2);
2974: hr_utility.set_location('p_edate :'||p_edate,2);
2975: hr_utility.set_location('p_val_st_date: '||p_val_st_date,2);
2976: hr_utility.set_location('p_val_end_date: '||p_val_end_date,2);
2977:
2970: WHEN NO_DATA_FOUND THEN NULL;
2971: END;
2972: hr_utility.set_location('p_del_flag :'||p_del_flag,2);
2973: hr_utility.set_location('p_mode :'||p_mode,2);
2974: hr_utility.set_location('p_edate :'||p_edate,2);
2975: hr_utility.set_location('p_val_st_date: '||p_val_st_date,2);
2976: hr_utility.set_location('p_val_end_date: '||p_val_end_date,2);
2977:
2978: --
2971: END;
2972: hr_utility.set_location('p_del_flag :'||p_del_flag,2);
2973: hr_utility.set_location('p_mode :'||p_mode,2);
2974: hr_utility.set_location('p_edate :'||p_edate,2);
2975: hr_utility.set_location('p_val_st_date: '||p_val_st_date,2);
2976: hr_utility.set_location('p_val_end_date: '||p_val_end_date,2);
2977:
2978: --
2979: IF p_del_flag = 'Y' THEN
2972: hr_utility.set_location('p_del_flag :'||p_del_flag,2);
2973: hr_utility.set_location('p_mode :'||p_mode,2);
2974: hr_utility.set_location('p_edate :'||p_edate,2);
2975: hr_utility.set_location('p_val_st_date: '||p_val_st_date,2);
2976: hr_utility.set_location('p_val_end_date: '||p_val_end_date,2);
2977:
2978: --
2979: IF p_del_flag = 'Y' THEN
2980: --
3000:
3001: else
3002:
3003: IF p_mode = 'ZAP' THEN
3004: hr_utility.set_location('hr_assignment.delete_ass_ref_int',2);
3005:
3006: l_datetrack_mode := 'ZAP';
3007:
3008: --
3042: --
3043: open csr_grade_step;
3044: fetch csr_grade_step into l_update;
3045: if csr_grade_step%found then
3046: hr_utility.set_location('Grade Step Found',4);
3047: hr_utility.set_location('p_calling_proc :'||p_calling_proc,4);
3048: hr_utility.set_location('p_val_st_date: '||p_val_st_date,4);
3049:
3050: --
3043: open csr_grade_step;
3044: fetch csr_grade_step into l_update;
3045: if csr_grade_step%found then
3046: hr_utility.set_location('Grade Step Found',4);
3047: hr_utility.set_location('p_calling_proc :'||p_calling_proc,4);
3048: hr_utility.set_location('p_val_st_date: '||p_val_st_date,4);
3049:
3050: --
3051: -- Calling proces = 'POST_UPDATE' then a min step has to be created for the new grade scale
3044: fetch csr_grade_step into l_update;
3045: if csr_grade_step%found then
3046: hr_utility.set_location('Grade Step Found',4);
3047: hr_utility.set_location('p_calling_proc :'||p_calling_proc,4);
3048: hr_utility.set_location('p_val_st_date: '||p_val_st_date,4);
3049:
3050: --
3051: -- Calling proces = 'POST_UPDATE' then a min step has to be created for the new grade scale
3052: --
3063: where spp.assignment_id = p_assignment_id
3064: and p_val_st_date between spp.effective_start_date
3065: and spp.effective_end_date;
3066:
3067: hr_utility.set_location('l_placement_id :'||l_placement_id,5);
3068: hr_utility.set_location('l_object_version_number :'||l_object_version_number,5);
3069: hr_utility.set_location('l_date_temp :'||l_date_temp,5);
3070: hr_utility.set_location('l_old_parent_spine_id :'||l_old_parent_spine_id,5);
3071:
3064: and p_val_st_date between spp.effective_start_date
3065: and spp.effective_end_date;
3066:
3067: hr_utility.set_location('l_placement_id :'||l_placement_id,5);
3068: hr_utility.set_location('l_object_version_number :'||l_object_version_number,5);
3069: hr_utility.set_location('l_date_temp :'||l_date_temp,5);
3070: hr_utility.set_location('l_old_parent_spine_id :'||l_old_parent_spine_id,5);
3071:
3072: --
3065: and spp.effective_end_date;
3066:
3067: hr_utility.set_location('l_placement_id :'||l_placement_id,5);
3068: hr_utility.set_location('l_object_version_number :'||l_object_version_number,5);
3069: hr_utility.set_location('l_date_temp :'||l_date_temp,5);
3070: hr_utility.set_location('l_old_parent_spine_id :'||l_old_parent_spine_id,5);
3071:
3072: --
3073: -- get the parent spine_id for the new grade
3066:
3067: hr_utility.set_location('l_placement_id :'||l_placement_id,5);
3068: hr_utility.set_location('l_object_version_number :'||l_object_version_number,5);
3069: hr_utility.set_location('l_date_temp :'||l_date_temp,5);
3070: hr_utility.set_location('l_old_parent_spine_id :'||l_old_parent_spine_id,5);
3071:
3072: --
3073: -- get the parent spine_id for the new grade
3074: --
3078: where pgs.grade_id = p_grade_id
3079: and P_edate between pgs.effective_start_date
3080: and pgs.effective_end_date;
3081:
3082: hr_utility.set_location('l_parent_spine_id :'||l_parent_spine_id,6);
3083:
3084: --
3085: -- Get the min seuence for the new grade
3086: --
3092: and psp.spinal_point_id = sps.spinal_point_id
3093: and P_edate between sps.effective_start_date
3094: and sps.effective_end_date;
3095:
3096: hr_utility.set_location('l_sequence :'||l_sequence,7);
3097: --
3098: -- Get the step id for the min sequence
3099: --
3100: select sps.step_id
3105: and psp.parent_spine_id = l_parent_spine_id
3106: and psp.sequence = l_sequence;
3107:
3108:
3109: hr_utility.set_location('l_min_step_id :'||l_min_step_id,8);
3110:
3111: open csr_future_records;
3112: fetch csr_future_records into l_temp;
3113:
3112: fetch csr_future_records into l_temp;
3113:
3114: if csr_future_records%found then
3115:
3116: hr_utility.set_location('Future record found.',9);
3117:
3118: --
3119: -- check if there is a step placement record starting on the same day
3120: --
3127: end if;
3128:
3129: else
3130:
3131: hr_utility.set_location('Future record not found,',10);
3132:
3133: if l_date_temp = p_edate then
3134: l_datetrack_mode := 'CORRECTION';
3135: p_future_spp_warning := FALSE;
3158: l_new_date := p_edate;
3159:
3160: end if;
3161:
3162: hr_utility.set_location('Deleteing the next change.',15);
3163:
3164: /*if p_datetrack_mode <> 'UPDATE_OVERRIDE' then
3165: l_new_date := p_val_st_date;
3166: else
3168: end if;
3169: */
3170: l_new_date := p_edate;
3171:
3172: hr_utility.set_location('l_new_date: '||l_new_date,15);
3173:
3174: --
3175: -- Delete next change until the effective end date of the record
3176: -- that was just inserted matches the validation end date
3183: if csr_spp_rec%found then
3184: --
3185: l_datetrack_mode := 'DELETE_NEXT_CHANGE';
3186: --
3187: hr_utility.set_location('l_effective_end_date :'||l_effective_end_date, 25);
3188: hr_utility.set_location('p_val_end_date :'||p_val_end_date, 25);
3189: --
3190: loop
3191: --
3184: --
3185: l_datetrack_mode := 'DELETE_NEXT_CHANGE';
3186: --
3187: hr_utility.set_location('l_effective_end_date :'||l_effective_end_date, 25);
3188: hr_utility.set_location('p_val_end_date :'||p_val_end_date, 25);
3189: --
3190: loop
3191: --
3192: if l_effective_end_date = p_val_end_date then
3215:
3216: end if;
3217: close csr_grade_step;
3218:
3219: hr_utility.set_location('hr_assignment.delete_ass_ref_int',3);
3220: --
3221: -- Removed dml and inserted call to api
3222: --
3223: /*
3236: ELSE
3237: -- If mode is 'END' then do Date Effective Delete
3238: -- from p_end_date.
3239: --
3240: hr_utility.set_location('hr_assignment.delete_ass_ref_int',4);
3241: --
3242: -- Removed dml and using api
3243: --
3244: /*
3277: end if;
3278: close csr_grade_step;
3279:
3280:
3281: /*hr_utility.set_location('hr_assignment.delete_ass_ref_int',5);
3282: UPDATE PER_SPINAL_POINT_PLACEMENTS_F
3283: SET EFFECTIVE_END_DATE = p_end_date
3284: , LAST_UPDATED_BY = p_last_updated_by
3285: , LAST_UPDATE_LOGIN = p_last_update_login
3322: --
3323: IF p_del_flag = 'Y' THEN
3324: --
3325: IF p_mode = 'ZAP' THEN
3326: hr_utility.set_location('hr_assignment.del_ref_int_delet',30);
3327: DELETE FROM PER_ASSIGNMENT_BUDGET_VALUES_F ABV
3328: WHERE ABV.ASSIGNMENT_ID = p_assignment_id;
3329:
3330: --
3332: IF p_mode = 'END' THEN
3333: -- If mode is 'END' then do Date Effective Delete
3334: -- from p_end_date.
3335: --
3336: hr_utility.set_location('hr_assignment.del_ref_int_delete',40);
3337:
3338: DELETE FROM PER_ASSIGNMENT_BUDGET_VALUES_F ABV
3339: WHERE ABV.ASSIGNMENT_ID = p_assignment_id
3340: AND ABV.EFFECTIVE_START_DATE > p_end_date;
3338: DELETE FROM PER_ASSIGNMENT_BUDGET_VALUES_F ABV
3339: WHERE ABV.ASSIGNMENT_ID = p_assignment_id
3340: AND ABV.EFFECTIVE_START_DATE > p_end_date;
3341: --
3342: hr_utility.set_location('hr_assignment.del_ref_int_delete',45);
3343: UPDATE PER_ASSIGNMENT_BUDGET_VALUES_F
3344: SET EFFECTIVE_END_DATE = p_end_date
3345: , LAST_UPDATED_BY = p_last_updated_by
3346: , LAST_UPDATE_LOGIN = p_last_update_login
3355: -- If mode is 'FUTURE' then do Date Effective Delete for all future records
3356: -- from p_end_date. Further, open the current end dated record. Set the last date
3357: -- of assignment_budget_value record same as the last date of current assignmet.
3358: --
3359: hr_utility.set_location('hr_assignment.del_ref_int_delete',46);
3360: hr_utility.set_location('p_end_date '||p_end_date, 47);
3361:
3362: DELETE FROM PER_ASSIGNMENT_BUDGET_VALUES_F ABV
3363: WHERE ABV.ASSIGNMENT_ID = p_assignment_id
3356: -- from p_end_date. Further, open the current end dated record. Set the last date
3357: -- of assignment_budget_value record same as the last date of current assignmet.
3358: --
3359: hr_utility.set_location('hr_assignment.del_ref_int_delete',46);
3360: hr_utility.set_location('p_end_date '||p_end_date, 47);
3361:
3362: DELETE FROM PER_ASSIGNMENT_BUDGET_VALUES_F ABV
3363: WHERE ABV.ASSIGNMENT_ID = p_assignment_id
3364: AND ABV.EFFECTIVE_START_DATE > p_end_date;
3362: DELETE FROM PER_ASSIGNMENT_BUDGET_VALUES_F ABV
3363: WHERE ABV.ASSIGNMENT_ID = p_assignment_id
3364: AND ABV.EFFECTIVE_START_DATE > p_end_date;
3365: --
3366: hr_utility.set_location(' No of rows deleted '||sql%rowcount,48);
3367: hr_utility.set_location('hr_assignment.del_ref_int_delete',49);
3368:
3369: select effective_end_date into l_ass_end_date
3370: from per_all_assignments_f
3363: WHERE ABV.ASSIGNMENT_ID = p_assignment_id
3364: AND ABV.EFFECTIVE_START_DATE > p_end_date;
3365: --
3366: hr_utility.set_location(' No of rows deleted '||sql%rowcount,48);
3367: hr_utility.set_location('hr_assignment.del_ref_int_delete',49);
3368:
3369: select effective_end_date into l_ass_end_date
3370: from per_all_assignments_f
3371: where assignment_id = p_assignment_id
3370: from per_all_assignments_f
3371: where assignment_id = p_assignment_id
3372: and p_end_date between EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE;
3373:
3374: hr_utility.set_location('l_ass_end_date '||l_ass_end_date,50);
3375:
3376:
3377: UPDATE PER_ASSIGNMENT_BUDGET_VALUES_F
3378: SET EFFECTIVE_END_DATE = l_ass_end_date
3382: WHERE ASSIGNMENT_ID = p_assignment_id
3383: AND p_end_date
3384: BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE;
3385:
3386: hr_utility.set_location('No of rows updated '||sql%rowcount,51);
3387:
3388: END IF;
3389: -- Addition for Bug 7112709 ends
3390:
3395: --
3396: --
3397:
3398: IF p_mode = 'ZAP' THEN
3399: hr_utility.set_location('hr_assignment.del_ref_int_delete',5);
3400: p_del_flag := 'N';
3401: --
3402: BEGIN
3403: --
3416: END;
3417: --
3418: IF p_del_flag = 'Y' THEN
3419: --
3420: hr_utility.set_location('hr_assignment.delete_ass_ref_int',6);
3421: DELETE FROM HR_ASSIGNMENT_SET_AMENDMENTS
3422: WHERE ASSIGNMENT_ID = p_assignment_id;
3423: END IF;
3424: --
3423: END IF;
3424: --
3425: --
3426: --
3427: hr_utility.set_location('hr_assignment.del_ref_int_delete',9);
3428: p_del_flag := 'N';
3429: --
3430: BEGIN
3431: --
3442: END;
3443: --
3444: IF p_del_flag = 'Y' THEN
3445: --
3446: hr_utility.set_location('hr_assignment.delete_ass_ref_int',10);
3447: DELETE FROM PER_SECONDARY_ASS_STATUSES
3448: WHERE ASSIGNMENT_ID = p_assignment_id;
3449: END IF;
3450: --
3448: WHERE ASSIGNMENT_ID = p_assignment_id;
3449: END IF;
3450: --
3451: --
3452: hr_utility.set_location('hr_assignment.del_ref_int_delete',11);
3453: p_del_flag := 'N';
3454: --
3455: BEGIN
3456: --
3467: END;
3468: --
3469: IF p_del_flag = 'Y' THEN
3470: --
3471: hr_utility.set_location('hr_assignment.delete_ass_ref_int',12);
3472: DELETE FROM PER_PAY_PROPOSALS
3473: WHERE ASSIGNMENT_ID = p_assignment_id;
3474: END IF;
3475: --
3474: END IF;
3475: --
3476: /* This is being changed for Bug# 785427 */
3477:
3478: hr_utility.set_location('hr_assignment.del_ref_int_delete',11);
3479: p_del_flag := 'N';
3480:
3481: BEGIN
3482: select 'Y'
3492: END;
3493: --
3494: IF p_del_flag = 'Y' THEN
3495: --
3496: hr_utility.set_location('hr_assignment.delete_ass_ref_int',12);
3497: /* If the federal tax record exists then the state, county
3498: and city tax records also exist (due to the defaulting of
3499: tax records). So, delete from all 4 table. In addition, delete
3500: from the table pay_us_asg_reporting as well */
3518: --
3519:
3520: -- 03/18/1998 Bug #642566
3521: -- Remove per_assignment_extra_info records
3522: hr_utility.set_location('hr_assignment.del_ref_int_delete',14);
3523: p_del_flag := 'N';
3524: --
3525: BEGIN
3526: --
3537: END;
3538: --
3539: IF p_del_flag = 'Y' THEN
3540: --
3541: hr_utility.set_location('hr_assignment.delete_ass_ref_int',16);
3542: DELETE FROM PER_ASSIGNMENT_EXTRA_INFO
3543: WHERE ASSIGNMENT_ID = p_assignment_id;
3544: END IF;
3545: -- 03/18/1998 Change Ends
3677: AND p_mode='END';
3678: --
3679: BEGIN
3680: --
3681: hr_utility.set_location('hr_assignment.tidy_up_ref_int',1);
3682: --
3683: p_cost_warning := FALSE;
3684: BEGIN
3685: select 'Y'
3702: END;
3703: --
3704: IF p_del_flag = 'Y' THEN
3705: --
3706: hr_utility.set_location('hr_assignment.tidy_up_ref_int',2);
3707: --
3708: update per_secondary_ass_statuses
3709: set END_DATE = decode(p_new_end_date,to_date('31/12/4712','DD/MM/YYYY'),
3710: null,p_new_end_date)
3723: END IF;
3724: --
3725: p_del_flag := 'N';
3726: --
3727: hr_utility.set_location('hr_assignment.tidy_up_ref_int',3);
3728: --
3729: BEGIN
3730: select 'Y'
3731: into p_del_flag
3741: END;
3742: --
3743: IF p_del_flag = 'Y' THEN
3744: --
3745: hr_utility.set_location('hr_assignment.tidy_up_ref_int',4);
3746: --
3747: delete from per_secondary_ass_statuses
3748: where assignment_id = p_assignment_id
3749: and p_mode = 'END'
3751: END IF;
3752: --
3753: p_del_flag := 'N';
3754: --
3755: hr_utility.set_location('hr_assignment.tidy_up_ref_int',5);
3756: --
3757: BEGIN
3758: select 'Y'
3759: into p_del_flag
3773: END;
3774: --
3775: IF p_del_flag = 'Y' THEN
3776: --
3777: hr_utility.set_location('hr_assignment.tidy_up_ref_int',6);
3778:
3779: if p_mode = 'END' then
3780: hr_utility.set_location('hr_assignment.tidy_up_ref_int',7);
3781: update pay_cost_allocations_f
3776: --
3777: hr_utility.set_location('hr_assignment.tidy_up_ref_int',6);
3778:
3779: if p_mode = 'END' then
3780: hr_utility.set_location('hr_assignment.tidy_up_ref_int',7);
3781: update pay_cost_allocations_f
3782: set effective_end_date = p_new_end_date
3783: , last_updated_by = P_LAST_UPDATED_BY
3784: , last_update_login = P_LAST_UPDATE_LOGIN
3787: and ((p_mode = 'END'
3788: and p_new_end_date
3789: between effective_start_date and effective_end_date));
3790: elsif p_mode='FUTURE' then
3791: hr_utility.set_location('hr_assignment.tidy_up_ref_int',8);
3792:
3793: /*
3794: ** When dealing with delete FUTURE_CHANGE, only open out
3795: ** the costing record if no future costing record exists.
3802: where assignment_id = p_assignment_id
3803: and effective_start_date > p_old_end_date;
3804:
3805: if l_exists = 0 then
3806: hr_utility.set_location('hr_assignment.tidy_up_ref_int',9);
3807: update pay_cost_allocations_f
3808: set effective_end_date = p_new_end_date
3809: , last_updated_by = P_LAST_UPDATED_BY
3810: , last_update_login = P_LAST_UPDATE_LOGIN
3812: where assignment_id = p_assignment_id
3813: and (p_mode = 'FUTURE'
3814: and p_old_end_date = effective_end_date);
3815: else
3816: hr_utility.set_location('hr_assignment.tidy_up_ref_int',10);
3817: p_cost_warning := TRUE;
3818: end if;
3819:
3820: end if;
3831: -- What then needs to happen is to change the end date of the existing row to make sure the current child
3832: -- row stays in line with the parent.
3833: -- SASmith : 16-APR-1998
3834:
3835: hr_utility.set_location(p_new_end_date,11);
3836: hr_utility.set_location(p_mode,12);
3837: hr_utility.set_location(p_old_end_date,13);
3838:
3839: hr_utility.set_location('hr_assignment.tidy_up_ref_int',15);
3832: -- row stays in line with the parent.
3833: -- SASmith : 16-APR-1998
3834:
3835: hr_utility.set_location(p_new_end_date,11);
3836: hr_utility.set_location(p_mode,12);
3837: hr_utility.set_location(p_old_end_date,13);
3838:
3839: hr_utility.set_location('hr_assignment.tidy_up_ref_int',15);
3840:
3833: -- SASmith : 16-APR-1998
3834:
3835: hr_utility.set_location(p_new_end_date,11);
3836: hr_utility.set_location(p_mode,12);
3837: hr_utility.set_location(p_old_end_date,13);
3838:
3839: hr_utility.set_location('hr_assignment.tidy_up_ref_int',15);
3840:
3841: p_del_flag := 'N';
3835: hr_utility.set_location(p_new_end_date,11);
3836: hr_utility.set_location(p_mode,12);
3837: hr_utility.set_location(p_old_end_date,13);
3838:
3839: hr_utility.set_location('hr_assignment.tidy_up_ref_int',15);
3840:
3841: p_del_flag := 'N';
3842:
3843: --
3857: END;
3858: --
3859: IF p_del_flag = 'Y' THEN
3860: --
3861: hr_utility.set_location('hr_assignment.tidy_up_ref_int',20);
3862: --
3863: delete from per_assignment_budget_values_f abv
3864: where abv.assignment_id = p_assignment_id
3865: and p_mode = 'END'
3867: END IF;
3868:
3869: p_del_flag := 'N';
3870: --
3871: hr_utility.set_location('hr_assignment.tidy_up_ref_int',25);
3872:
3873:
3874: BEGIN
3875: select 'Y'
3887: END;
3888:
3889: IF p_del_flag = 'Y' THEN
3890: --
3891: hr_utility.set_location('hr_assignment.tidy_up_ref_int',30);
3892: --
3893: update per_assignment_budget_values_f abv
3894: set abv.effective_end_date = p_new_end_date
3895: , abv.last_updated_by = P_LAST_UPDATED_BY
3901:
3902: END IF;
3903:
3904: --
3905: hr_utility.set_location('hr_assignment.tidy_up_ref_int',35);
3906: --
3907: -- # 2437795
3908: -- Reversing TAX records
3909: --
3908: -- Reversing TAX records
3909: --
3910: IF p_mode = 'FUTURE' THEN
3911: --
3912: hr_utility.set_location('hr_assignment.tidy_up_ref_int',42);
3913: hr_utility.trace(' **** old end date = '||to_char(p_old_end_date,'dd-mon-yyyy'));
3914: declare
3915: cursor csr_asg is
3916: select max(effective_end_date)
3909: --
3910: IF p_mode = 'FUTURE' THEN
3911: --
3912: hr_utility.set_location('hr_assignment.tidy_up_ref_int',42);
3913: hr_utility.trace(' **** old end date = '||to_char(p_old_end_date,'dd-mon-yyyy'));
3914: declare
3915: cursor csr_asg is
3916: select max(effective_end_date)
3917: from pay_us_emp_fed_tax_rules_f
3920: begin
3921: open csr_asg;
3922: fetch csr_asg into l_end_date;
3923: close csr_asg;
3924: hr_utility.trace(' **** l_end_date = '||to_char(l_end_date,'dd-mon-yyyy'));
3925: if l_end_date is not null then
3926: pay_us_update_tax_rec_pkg.reverse_term_emp_tax_records(p_assignment_id, l_end_date);
3927: end if;
3928: end;
3926: pay_us_update_tax_rec_pkg.reverse_term_emp_tax_records(p_assignment_id, l_end_date);
3927: end if;
3928: end;
3929: --
3930: hr_utility.set_location('hr_assignment.tidy_up_ref_int',45);
3931: END IF;
3932: -- end #2437795
3933: --
3934: --adhunter added for bug 2537091 04-OCT-02
3933: --
3934: --adhunter added for bug 2537091 04-OCT-02
3935: --need to handle pay_personal_payment_methods in the same way as the others above.
3936: --
3937: hr_utility.set_location('hr_assignment.tidy_up_ref_int',50);
3938: DECLARE
3939: l_effective_start_date date;
3940: l_effective_end_date date;
3941: --
3958: and p_mode='END';
3959: --
3960: BEGIN
3961: for l_curr_rec in csr_curr_ppm loop
3962: hr_utility.set_location('ppm_id '||l_curr_rec.personal_payment_method_id,55);
3963: --
3964: --end date current DT row and delete future rows for this PPM
3965: --this means that future-only PPMs are left.
3966: --
3973: ,p_effective_end_date => l_effective_end_date
3974: );
3975: end loop;
3976: for l_fut_rec in csr_fut_ppm loop
3977: hr_utility.set_location('ppm_id '||l_fut_rec.personal_payment_method_id,60);
3978: --
3979: --delete future-only PPMs, last loop removed future DT rows of current PPMs
3980: --
3981: hr_personal_pay_method_api.delete_personal_pay_method
3988: );
3989: end loop;
3990: END;
3991: --
3992: hr_utility.set_location(l_proc,70);
3993: --
3994: -- End Date any current Assignment Rates
3995: --
3996: FOR crec_current_asg_rates IN csr_current_asg_rates LOOP
3994: -- End Date any current Assignment Rates
3995: --
3996: FOR crec_current_asg_rates IN csr_current_asg_rates LOOP
3997: --
3998: hr_utility.set_location(l_proc||'/'||crec_current_asg_rates.grade_rule_id,80);
3999: --
4000: hr_rate_values_api.delete_rate_value
4001: (p_validate => FALSE
4002: ,p_grade_rule_id => crec_current_asg_rates.grade_rule_id
4007: ,p_effective_end_date => l_effective_end_date);
4008: --
4009: END LOOP;
4010: --
4011: hr_utility.set_location(l_proc,90);
4012: --
4013: -- Delete any future dated assignment rates if we are Ending the Assignment.
4014: --
4015: FOR crec_future_asg_rates IN csr_future_asg_rates LOOP
4013: -- Delete any future dated assignment rates if we are Ending the Assignment.
4014: --
4015: FOR crec_future_asg_rates IN csr_future_asg_rates LOOP
4016: --
4017: hr_utility.set_location(l_proc||'/'||crec_future_asg_rates.grade_rule_id,100);
4018: --
4019: hr_rate_values_api.delete_rate_value
4020: (p_validate => FALSE
4021: ,p_grade_rule_id => crec_future_asg_rates.grade_rule_id
4026: ,p_effective_end_date => l_effective_end_date);
4027: --
4028: END LOOP;
4029: --
4030: hr_utility.set_location('Leaving : '||l_proc,999);
4031: --
4032: EXCEPTION
4033: when others then
4034: p_cost_warning := null ;
4099: BEGIN
4100: -- This function returns TRUE if a TERM_ASSIGN status exists earlier than the
4101: -- date we are considering.
4102: --
4103: hr_utility.set_location('peassign.call_terminate_entries',1);
4104: --
4105: BEGIN
4106: select 'Y'
4107: into term_exists
4123: END previous_term_exists;
4124: -------------------------
4125: BEGIN
4126: --
4127: hr_utility.set_location('peassign.call_terminate_entries',2);
4128: IF P_STATUS = 'END' THEN
4129: --
4130: IF previous_term_exists(p_assignment_id
4131: ,p_start_date) THEN
4137: p_last_standard_date := p_start_date;
4138: p_final_process_date := p_start_date;
4139: END IF; -- IF previous_term_exists(....
4140: --
4141: hr_utility.set_location('peassign.call_terminate_entries',3);
4142: -- VT 10/08/96 bug #306710 added parameter
4143: hrempter.terminate_entries_and_alus(p_assignment_id
4144: ,p_actual_term_date
4145: ,p_last_standard_date
4148: ,l_entries_changed);
4149: --
4150: ELSE -- (IF p_status = 'TERM_ASSIGN')
4151: --
4152: hr_utility.set_location('peassign.call_terminate_entries',4);
4153: IF previous_term_exists(p_assignment_id
4154: ,p_start_date) THEN NULL;
4155: ELSE
4156: p_actual_term_date := p_start_date -1;
4156: p_actual_term_date := p_start_date -1;
4157: p_last_standard_date := p_start_date -1;
4158: p_final_process_date := NULL;
4159: --
4160: hr_utility.set_location('peassign.call_terminate_entries',5);
4161: BEGIN
4162: select tp.end_date
4163: into p_last_standard_date
4164: from per_assignments_f a
4170: EXCEPTION
4171: WHEN NO_DATA_FOUND THEN NULL;
4172: END;
4173: --
4174: hr_utility.set_location('peassign.call_terminate_entries',6);
4175: -- VT 10/08/96 bug #306710 added parameter
4176: hrempter.terminate_entries_and_alus(p_assignment_id
4177: ,p_actual_term_date
4178: ,p_last_standard_date
4311: date of the current operation then cancellation of entries
4312: will be required
4313: -----------------------------------------------------------------*/
4314: --
4315: hr_utility.set_location('hr_assignment.test_for_cancel_reterm',1);
4316: if p_mode in ('CORRECTION'
4317: ,'UPDATE'
4318: ,'UPDATE_OVERRIDE'
4319: ,'UPDATE_CHANGE_INSERT' )
4415: l_cobra_term_exists VARCHAR2(1);
4416: local_warning exception;
4417: --
4418: BEGIN
4419: hr_utility.set_location('hr_assignment.check_for_cobra',1);
4420: BEGIN
4421: select 'Y'
4422: into l_cobra_term_exists
4423: from sys.dual
4439: EXCEPTION
4440: WHEN NO_DATA_FOUND THEN NULL;
4441: END;
4442: --
4443: hr_utility.set_location('hr_assignment.check_for_cobra',2);
4444: if l_cobra_term_exists = 'Y' then
4445: raise local_warning;
4446: end if;
4447: --
4446: end if;
4447: --
4448: EXCEPTION
4449: when local_warning then
4450: hr_utility.set_warning;
4451: END check_for_cobra;
4452: --
4453: -----------------------------------------------------------------------
4454: -- validate_pos
4481: ORDER BY date_start DESC;
4482:
4483: BEGIN
4484:
4485: hr_utility.set_location('Entering: '||l_proc, 10);
4486:
4487: OPEN get_pos;
4488: FETCH get_pos INTO l_dummy_dt, l_pos_id;
4489: CLOSE get_pos;
4487: OPEN get_pos;
4488: FETCH get_pos INTO l_dummy_dt, l_pos_id;
4489: CLOSE get_pos;
4490:
4491: hr_utility.trace('l_pos_id: '||to_char(l_pos_id));
4492:
4493: IF l_pos_id IS NULL THEN
4494: hr_utility.set_message(801,'HR_6346_EMP_ASS_NO_POS');
4495: hr_utility.raise_error;
4490:
4491: hr_utility.trace('l_pos_id: '||to_char(l_pos_id));
4492:
4493: IF l_pos_id IS NULL THEN
4494: hr_utility.set_message(801,'HR_6346_EMP_ASS_NO_POS');
4495: hr_utility.raise_error;
4496: END IF;
4497:
4498: hr_utility.set_location('Leaving: '||l_proc, 40);
4491: hr_utility.trace('l_pos_id: '||to_char(l_pos_id));
4492:
4493: IF l_pos_id IS NULL THEN
4494: hr_utility.set_message(801,'HR_6346_EMP_ASS_NO_POS');
4495: hr_utility.raise_error;
4496: END IF;
4497:
4498: hr_utility.set_location('Leaving: '||l_proc, 40);
4499:
4494: hr_utility.set_message(801,'HR_6346_EMP_ASS_NO_POS');
4495: hr_utility.raise_error;
4496: END IF;
4497:
4498: hr_utility.set_location('Leaving: '||l_proc, 40);
4499:
4500: END validate_pos;
4501: --
4502: --
4550: where business_group_id = p_business_group_id);
4551: --
4552: begin
4553: --
4554: hr_utility.set_location('Entering hr_assignment.per_dflt_asg_cost_alloc_ff',25);
4555: --
4556: open c_formula_id(l_formula_name, p_business_group_id, p_effective_date);
4557: fetch c_formula_id into l_formula_id;
4558: --
4555: --
4556: open c_formula_id(l_formula_name, p_business_group_id, p_effective_date);
4557: fetch c_formula_id into l_formula_id;
4558: --
4559: hr_utility.set_location('hr_assignment.per_dflt_asg_cost_alloc_ff',26);
4560: --
4561: if c_formula_id%notfound then
4562: hr_utility.set_location('hr_assignment.per_dflt_asg_cost_alloc_ff',27);
4563: close c_formula_id;
4558: --
4559: hr_utility.set_location('hr_assignment.per_dflt_asg_cost_alloc_ff',26);
4560: --
4561: if c_formula_id%notfound then
4562: hr_utility.set_location('hr_assignment.per_dflt_asg_cost_alloc_ff',27);
4563: close c_formula_id;
4564: return 'N';
4565: end if;
4566: hr_utility.set_location('hr_assignment.per_dflt_asg_cost_alloc_ff',28);
4562: hr_utility.set_location('hr_assignment.per_dflt_asg_cost_alloc_ff',27);
4563: close c_formula_id;
4564: return 'N';
4565: end if;
4566: hr_utility.set_location('hr_assignment.per_dflt_asg_cost_alloc_ff',28);
4567: close c_formula_id;
4568: hr_utility.set_location('hr_assignment.per_dflt_asg_cost_alloc_ff-formula_id :'
4569: || l_formula_id,29);
4570: -- Insert fnd_sessions row
4564: return 'N';
4565: end if;
4566: hr_utility.set_location('hr_assignment.per_dflt_asg_cost_alloc_ff',28);
4567: close c_formula_id;
4568: hr_utility.set_location('hr_assignment.per_dflt_asg_cost_alloc_ff-formula_id :'
4569: || l_formula_id,29);
4570: -- Insert fnd_sessions row
4571: open c_session_date;
4572: fetch c_session_date into l_session_date;
4569: || l_formula_id,29);
4570: -- Insert fnd_sessions row
4571: open c_session_date;
4572: fetch c_session_date into l_session_date;
4573: hr_utility.set_location('hr_assignment.per_dflt_asg_cost_alloc_ff' || l_session_date,30);
4574: if c_session_date%notfound then
4575: hr_utility.set_location('hr_assignment.per_dflt_asg_cost_alloc_ff',31);
4576: insert into fnd_sessions (SESSION_ID, EFFECTIVE_DATE) values(userenv('sessionid'), trunc(p_effective_date));
4577: hr_utility.set_location('hr_assignment.per_dflt_asg_cost_alloc_ff',32);
4571: open c_session_date;
4572: fetch c_session_date into l_session_date;
4573: hr_utility.set_location('hr_assignment.per_dflt_asg_cost_alloc_ff' || l_session_date,30);
4574: if c_session_date%notfound then
4575: hr_utility.set_location('hr_assignment.per_dflt_asg_cost_alloc_ff',31);
4576: insert into fnd_sessions (SESSION_ID, EFFECTIVE_DATE) values(userenv('sessionid'), trunc(p_effective_date));
4577: hr_utility.set_location('hr_assignment.per_dflt_asg_cost_alloc_ff',32);
4578: end if;
4579: --
4573: hr_utility.set_location('hr_assignment.per_dflt_asg_cost_alloc_ff' || l_session_date,30);
4574: if c_session_date%notfound then
4575: hr_utility.set_location('hr_assignment.per_dflt_asg_cost_alloc_ff',31);
4576: insert into fnd_sessions (SESSION_ID, EFFECTIVE_DATE) values(userenv('sessionid'), trunc(p_effective_date));
4577: hr_utility.set_location('hr_assignment.per_dflt_asg_cost_alloc_ff',32);
4578: end if;
4579: --
4580: hr_utility.set_location('hr_assignment.per_dflt_asg_cost_alloc_ff',33);
4581: -- Initialize the formula
4576: insert into fnd_sessions (SESSION_ID, EFFECTIVE_DATE) values(userenv('sessionid'), trunc(p_effective_date));
4577: hr_utility.set_location('hr_assignment.per_dflt_asg_cost_alloc_ff',32);
4578: end if;
4579: --
4580: hr_utility.set_location('hr_assignment.per_dflt_asg_cost_alloc_ff',33);
4581: -- Initialize the formula
4582: ff_exec.init_formula(l_formula_id,p_effective_date, l_inputs, l_outputs);
4583: --
4584: hr_utility.set_location('hr_assignment.per_dflt_asg_cost_alloc_ff',34);
4580: hr_utility.set_location('hr_assignment.per_dflt_asg_cost_alloc_ff',33);
4581: -- Initialize the formula
4582: ff_exec.init_formula(l_formula_id,p_effective_date, l_inputs, l_outputs);
4583: --
4584: hr_utility.set_location('hr_assignment.per_dflt_asg_cost_alloc_ff',34);
4585: --
4586: --set the inputs
4587: --
4588: for i in nvl(l_inputs.first,0) .. nvl(l_inputs.last,-1) loop
4596: l_inputs(i).value := trunc(p_effective_date);
4597: end if;
4598: end loop;
4599: --
4600: hr_utility.set_location('hr_assignment.per_dflt_asg_cost_alloc_ff',35);
4601: --
4602: ff_exec.run_formula(l_inputs, l_outputs);
4603: --
4604: hr_utility.set_location('hr_assignment.per_dflt_asg_cost_alloc_ff',36);
4600: hr_utility.set_location('hr_assignment.per_dflt_asg_cost_alloc_ff',35);
4601: --
4602: ff_exec.run_formula(l_inputs, l_outputs);
4603: --
4604: hr_utility.set_location('hr_assignment.per_dflt_asg_cost_alloc_ff',36);
4605: --
4606: for i in nvl(l_outputs.first,0) .. nvl(l_outputs.last,-1) loop
4607: if (l_outputs(i).name = 'USE_FORMULA') then
4608: hr_utility.set_location('hr_assignment.per_dflt_asg_cost_alloc_ff-'|| l_outputs(i).value,361);
4604: hr_utility.set_location('hr_assignment.per_dflt_asg_cost_alloc_ff',36);
4605: --
4606: for i in nvl(l_outputs.first,0) .. nvl(l_outputs.last,-1) loop
4607: if (l_outputs(i).name = 'USE_FORMULA') then
4608: hr_utility.set_location('hr_assignment.per_dflt_asg_cost_alloc_ff-'|| l_outputs(i).value,361);
4609: l_use_formula := nvl(l_outputs(i).value,'N');
4610: if (l_use_formula <> 'Y')then
4611: return 'N';
4612: end if;
4610: if (l_use_formula <> 'Y')then
4611: return 'N';
4612: end if;
4613: elsif (substr(l_outputs(i).name,1,26) = 'COST_ALLOCATION_KEYFLEX_ID') then
4614: hr_utility.set_location('hr_assignment.per_dflt_asg_cost_alloc_ff-'|| l_outputs(i).value,362);
4615: l_rec(to_number(substr(l_outputs(i).name,27))).cost_allocation_keyflex_id := l_outputs(i).value;
4616: elsif (substr(l_outputs(i).name,1,10) = 'PROPORTION') then
4617: hr_utility.set_location('hr_assignment.per_dflt_asg_cost_alloc_ff-'|| l_outputs(i).value,363);
4618: l_rec(to_number(substr(l_outputs(i).name,11))).PROPORTION := l_outputs(i).value;
4613: elsif (substr(l_outputs(i).name,1,26) = 'COST_ALLOCATION_KEYFLEX_ID') then
4614: hr_utility.set_location('hr_assignment.per_dflt_asg_cost_alloc_ff-'|| l_outputs(i).value,362);
4615: l_rec(to_number(substr(l_outputs(i).name,27))).cost_allocation_keyflex_id := l_outputs(i).value;
4616: elsif (substr(l_outputs(i).name,1,10) = 'PROPORTION') then
4617: hr_utility.set_location('hr_assignment.per_dflt_asg_cost_alloc_ff-'|| l_outputs(i).value,363);
4618: l_rec(to_number(substr(l_outputs(i).name,11))).PROPORTION := l_outputs(i).value;
4619: end if;
4620: end loop;
4621: --
4618: l_rec(to_number(substr(l_outputs(i).name,11))).PROPORTION := l_outputs(i).value;
4619: end if;
4620: end loop;
4621: --
4622: hr_utility.set_location('hr_assignment.per_dflt_asg_cost_alloc_ff',37);
4623: --
4624: if l_use_formula = 'Y' then
4625: for i in nvl(l_rec.first,0) .. nvl(l_rec.last,-1) loop
4626: hr_utility.set_location('hr_assignment.per_dflt_asg_cost_alloc_ff l_rec(i).cost_all_kf_id'
4622: hr_utility.set_location('hr_assignment.per_dflt_asg_cost_alloc_ff',37);
4623: --
4624: if l_use_formula = 'Y' then
4625: for i in nvl(l_rec.first,0) .. nvl(l_rec.last,-1) loop
4626: hr_utility.set_location('hr_assignment.per_dflt_asg_cost_alloc_ff l_rec(i).cost_all_kf_id'
4627: || l_rec(i).cost_allocation_keyflex_id,381);
4628: hr_utility.set_location('hr_assignment.per_dflt_asg_cost_alloc_ff l_rec(i).proportion'
4629: || l_rec(i).proportion,382);
4630: if (nvl(l_rec(i).cost_allocation_keyflex_id, -1) <> -1) then
4624: if l_use_formula = 'Y' then
4625: for i in nvl(l_rec.first,0) .. nvl(l_rec.last,-1) loop
4626: hr_utility.set_location('hr_assignment.per_dflt_asg_cost_alloc_ff l_rec(i).cost_all_kf_id'
4627: || l_rec(i).cost_allocation_keyflex_id,381);
4628: hr_utility.set_location('hr_assignment.per_dflt_asg_cost_alloc_ff l_rec(i).proportion'
4629: || l_rec(i).proportion,382);
4630: if (nvl(l_rec(i).cost_allocation_keyflex_id, -1) <> -1) then
4631: --
4632: --
4629: || l_rec(i).proportion,382);
4630: if (nvl(l_rec(i).cost_allocation_keyflex_id, -1) <> -1) then
4631: --
4632: --
4633: hr_utility.set_location('hr_assignment.per_dflt_asg_cost_alloc_ff - l_rec(i).proportion:'||l_rec(i).proportion,313);
4634: hr_utility.set_location('hr_assignment.per_dflt_asg_cost_alloc_ff - l_rec(i).cost_kf_id:'||l_rec(i).cost_allocation_keyflex_id,314);
4635: --
4636: l_proportion := trunc(l_rec(i).proportion,4);
4637: --
4630: if (nvl(l_rec(i).cost_allocation_keyflex_id, -1) <> -1) then
4631: --
4632: --
4633: hr_utility.set_location('hr_assignment.per_dflt_asg_cost_alloc_ff - l_rec(i).proportion:'||l_rec(i).proportion,313);
4634: hr_utility.set_location('hr_assignment.per_dflt_asg_cost_alloc_ff - l_rec(i).cost_kf_id:'||l_rec(i).cost_allocation_keyflex_id,314);
4635: --
4636: l_proportion := trunc(l_rec(i).proportion,4);
4637: --
4638: hr_utility.set_location('hr_assignment.per_dflt_asg_cost_alloc_ff - l_proportion1 :'||l_proportion,315);
4634: hr_utility.set_location('hr_assignment.per_dflt_asg_cost_alloc_ff - l_rec(i).cost_kf_id:'||l_rec(i).cost_allocation_keyflex_id,314);
4635: --
4636: l_proportion := trunc(l_rec(i).proportion,4);
4637: --
4638: hr_utility.set_location('hr_assignment.per_dflt_asg_cost_alloc_ff - l_proportion1 :'||l_proportion,315);
4639: if l_proportion > 1 then
4640: l_proportion := 1;
4641: end if;
4642: hr_utility.set_location('hr_assignment.per_dflt_asg_cost_alloc_ff - l_proportion2 :'||l_proportion,316);
4638: hr_utility.set_location('hr_assignment.per_dflt_asg_cost_alloc_ff - l_proportion1 :'||l_proportion,315);
4639: if l_proportion > 1 then
4640: l_proportion := 1;
4641: end if;
4642: hr_utility.set_location('hr_assignment.per_dflt_asg_cost_alloc_ff - l_proportion2 :'||l_proportion,316);
4643: --
4644: if l_proportion > 0 then
4645: for r3 in c_cost_allocation_keyflex(l_rec(i).cost_allocation_keyflex_id) loop
4646: hr_utility.set_location('hr_assignment.per_dflt_asg_cost_alloc_ff',39);
4642: hr_utility.set_location('hr_assignment.per_dflt_asg_cost_alloc_ff - l_proportion2 :'||l_proportion,316);
4643: --
4644: if l_proportion > 0 then
4645: for r3 in c_cost_allocation_keyflex(l_rec(i).cost_allocation_keyflex_id) loop
4646: hr_utility.set_location('hr_assignment.per_dflt_asg_cost_alloc_ff',39);
4647: --
4648: pay_cost_allocation_api.create_cost_allocation(
4649: p_validate =>false ,
4650: p_effective_date =>p_effective_date,
4689: p_segment30 =>r3.segment30,
4690: p_concat_segments =>r3.concatenated_segments
4691: );
4692: end loop;
4693: hr_utility.set_location('hr_assignment.per_dflt_asg_cost_alloc_ff',391);
4694: --
4695: end if;
4696: --
4697: hr_utility.set_location('hr_assignment.per_dflt_asg_cost_alloc_ff',40);
4693: hr_utility.set_location('hr_assignment.per_dflt_asg_cost_alloc_ff',391);
4694: --
4695: end if;
4696: --
4697: hr_utility.set_location('hr_assignment.per_dflt_asg_cost_alloc_ff',40);
4698: --
4699: end if;
4700: --
4701: hr_utility.set_location('hr_assignment.per_dflt_asg_cost_alloc_ff',41);
4697: hr_utility.set_location('hr_assignment.per_dflt_asg_cost_alloc_ff',40);
4698: --
4699: end if;
4700: --
4701: hr_utility.set_location('hr_assignment.per_dflt_asg_cost_alloc_ff',41);
4702: --
4703: end loop;
4704: --
4705: hr_utility.set_location('hr_assignment.per_dflt_asg_cost_alloc_ff',42);
4701: hr_utility.set_location('hr_assignment.per_dflt_asg_cost_alloc_ff',41);
4702: --
4703: end loop;
4704: --
4705: hr_utility.set_location('hr_assignment.per_dflt_asg_cost_alloc_ff',42);
4706: --
4707: return 'Y';
4708: --
4709: end if;
4707: return 'Y';
4708: --
4709: end if;
4710: --
4711: hr_utility.set_location('Leaving hr_assignment.per_dflt_asg_cost_alloc_ff',43);
4712: --
4713: return 'N';
4714: --
4715: end;
4833: or hr_psf_shd.system_availability_status(budget_unit3_id) = 'MONEY')
4834: );
4835: --
4836: BEGIN
4837: hr_utility.set_location('hr_assignment.load_assignment_allocation',1);
4838: --
4839: if nvl(fnd_profile.value('HR_DEFAULT_ASG_COST_ALLOC'),'N') <> 'Y' then
4840: return;
4841: end if;
4839: if nvl(fnd_profile.value('HR_DEFAULT_ASG_COST_ALLOC'),'N') <> 'Y' then
4840: return;
4841: end if;
4842: --
4843: hr_utility.set_location('hr_assignment.load_assignment_allocation',2);
4844: --
4845: if p_position_id is null then
4846: return;
4847: end if;
4845: if p_position_id is null then
4846: return;
4847: end if;
4848: --
4849: hr_utility.set_location('hr_assignment.load_assignment_allocation',3);
4850: hr_utility.set_location('hr_assignment.load_assignment_allocation - effec date :'|| p_effective_date,3);
4851: hr_utility.set_location('hr_assignment.load_assignment_allocation - p_position_id :'|| p_position_id,3);
4852: hr_utility.set_location('hr_assignment.load_assignment_allocation - assignment_id :'|| p_assignment_id,3);
4853: hr_utility.set_location('hr_assignment.load_assignment_allocation - business_group_id :'|| p_business_group_id,3);
4846: return;
4847: end if;
4848: --
4849: hr_utility.set_location('hr_assignment.load_assignment_allocation',3);
4850: hr_utility.set_location('hr_assignment.load_assignment_allocation - effec date :'|| p_effective_date,3);
4851: hr_utility.set_location('hr_assignment.load_assignment_allocation - p_position_id :'|| p_position_id,3);
4852: hr_utility.set_location('hr_assignment.load_assignment_allocation - assignment_id :'|| p_assignment_id,3);
4853: hr_utility.set_location('hr_assignment.load_assignment_allocation - business_group_id :'|| p_business_group_id,3);
4854: --
4847: end if;
4848: --
4849: hr_utility.set_location('hr_assignment.load_assignment_allocation',3);
4850: hr_utility.set_location('hr_assignment.load_assignment_allocation - effec date :'|| p_effective_date,3);
4851: hr_utility.set_location('hr_assignment.load_assignment_allocation - p_position_id :'|| p_position_id,3);
4852: hr_utility.set_location('hr_assignment.load_assignment_allocation - assignment_id :'|| p_assignment_id,3);
4853: hr_utility.set_location('hr_assignment.load_assignment_allocation - business_group_id :'|| p_business_group_id,3);
4854: --
4855: open c_asg_cost_allocations(p_assignment_id);
4848: --
4849: hr_utility.set_location('hr_assignment.load_assignment_allocation',3);
4850: hr_utility.set_location('hr_assignment.load_assignment_allocation - effec date :'|| p_effective_date,3);
4851: hr_utility.set_location('hr_assignment.load_assignment_allocation - p_position_id :'|| p_position_id,3);
4852: hr_utility.set_location('hr_assignment.load_assignment_allocation - assignment_id :'|| p_assignment_id,3);
4853: hr_utility.set_location('hr_assignment.load_assignment_allocation - business_group_id :'|| p_business_group_id,3);
4854: --
4855: open c_asg_cost_allocations(p_assignment_id);
4856: fetch c_asg_cost_allocations into l_dummy;
4849: hr_utility.set_location('hr_assignment.load_assignment_allocation',3);
4850: hr_utility.set_location('hr_assignment.load_assignment_allocation - effec date :'|| p_effective_date,3);
4851: hr_utility.set_location('hr_assignment.load_assignment_allocation - p_position_id :'|| p_position_id,3);
4852: hr_utility.set_location('hr_assignment.load_assignment_allocation - assignment_id :'|| p_assignment_id,3);
4853: hr_utility.set_location('hr_assignment.load_assignment_allocation - business_group_id :'|| p_business_group_id,3);
4854: --
4855: open c_asg_cost_allocations(p_assignment_id);
4856: fetch c_asg_cost_allocations into l_dummy;
4857: if c_asg_cost_allocations%found then
4854: --
4855: open c_asg_cost_allocations(p_assignment_id);
4856: fetch c_asg_cost_allocations into l_dummy;
4857: if c_asg_cost_allocations%found then
4858: hr_utility.set_location('hr_assignment.load_assignment_allocation',4);
4859: return;
4860: end if;
4861: --
4862: hr_utility.set_location('hr_assignment.load_assignment_allocation',5);
4858: hr_utility.set_location('hr_assignment.load_assignment_allocation',4);
4859: return;
4860: end if;
4861: --
4862: hr_utility.set_location('hr_assignment.load_assignment_allocation',5);
4863: --
4864: if (per_dflt_asg_cost_alloc_ff(p_assignment_id, p_business_group_id, p_position_id, p_effective_date) = 'Y') then
4865: hr_utility.set_location('hr_assignment.load_assignment_allocation',6);
4866: return;
4861: --
4862: hr_utility.set_location('hr_assignment.load_assignment_allocation',5);
4863: --
4864: if (per_dflt_asg_cost_alloc_ff(p_assignment_id, p_business_group_id, p_position_id, p_effective_date) = 'Y') then
4865: hr_utility.set_location('hr_assignment.load_assignment_allocation',6);
4866: return;
4867: end if;
4868: --
4869: hr_utility.set_location('hr_assignment.load_assignment_allocation',7);
4865: hr_utility.set_location('hr_assignment.load_assignment_allocation',6);
4866: return;
4867: end if;
4868: --
4869: hr_utility.set_location('hr_assignment.load_assignment_allocation',7);
4870: --
4871: open c0;
4872: fetch c0 into l_unit1, l_unit2, l_unit3;
4873: close c0;
4871: open c0;
4872: fetch c0 into l_unit1, l_unit2, l_unit3;
4873: close c0;
4874: --
4875: hr_utility.set_location('hr_assignment.load_assignment_allocation - ' || l_unit1,8);
4876: hr_utility.set_location('hr_assignment.load_assignment_allocation - ' || l_unit2,9);
4877: hr_utility.set_location('hr_assignment.load_assignment_allocation - ' || l_unit3,10);
4878: --
4879: open c2(l_unit1, l_unit2, l_unit3);
4872: fetch c0 into l_unit1, l_unit2, l_unit3;
4873: close c0;
4874: --
4875: hr_utility.set_location('hr_assignment.load_assignment_allocation - ' || l_unit1,8);
4876: hr_utility.set_location('hr_assignment.load_assignment_allocation - ' || l_unit2,9);
4877: hr_utility.set_location('hr_assignment.load_assignment_allocation - ' || l_unit3,10);
4878: --
4879: open c2(l_unit1, l_unit2, l_unit3);
4880: fetch c2 into l_period_value;
4873: close c0;
4874: --
4875: hr_utility.set_location('hr_assignment.load_assignment_allocation - ' || l_unit1,8);
4876: hr_utility.set_location('hr_assignment.load_assignment_allocation - ' || l_unit2,9);
4877: hr_utility.set_location('hr_assignment.load_assignment_allocation - ' || l_unit3,10);
4878: --
4879: open c2(l_unit1, l_unit2, l_unit3);
4880: fetch c2 into l_period_value;
4881: close c2;
4879: open c2(l_unit1, l_unit2, l_unit3);
4880: fetch c2 into l_period_value;
4881: close c2;
4882: --
4883: hr_utility.set_location('hr_assignment.load_assignment_allocation - '|| l_period_value,11);
4884: --
4885: if nvl(l_period_value,0) <> 0 then
4886: --
4887: hr_utility.set_location('hr_assignment.load_assignment_allocation ',12);
4883: hr_utility.set_location('hr_assignment.load_assignment_allocation - '|| l_period_value,11);
4884: --
4885: if nvl(l_period_value,0) <> 0 then
4886: --
4887: hr_utility.set_location('hr_assignment.load_assignment_allocation ',12);
4888: --
4889: for r1 in c1(l_unit1, l_unit2, l_unit3) loop
4890: --
4891: hr_utility.set_location('hr_assignment.load_assignment_allocation - '||r1.proportion,13);
4887: hr_utility.set_location('hr_assignment.load_assignment_allocation ',12);
4888: --
4889: for r1 in c1(l_unit1, l_unit2, l_unit3) loop
4890: --
4891: hr_utility.set_location('hr_assignment.load_assignment_allocation - '||r1.proportion,13);
4892: hr_utility.set_location('hr_assignment.load_assignment_allocation - '||r1.cost_allocation_keyflex_id,14);
4893: --
4894: l_proportion := trunc(r1.proportion/l_period_value,4);
4895: --
4888: --
4889: for r1 in c1(l_unit1, l_unit2, l_unit3) loop
4890: --
4891: hr_utility.set_location('hr_assignment.load_assignment_allocation - '||r1.proportion,13);
4892: hr_utility.set_location('hr_assignment.load_assignment_allocation - '||r1.cost_allocation_keyflex_id,14);
4893: --
4894: l_proportion := trunc(r1.proportion/l_period_value,4);
4895: --
4896: if l_proportion > 1 then
4946: end loop;
4947: --
4948: end if;
4949: --
4950: hr_utility.set_location('hr_assignment.load_assignment_allocation ',15);
4951: --
4952: end loop;
4953: --
4954: hr_utility.set_location('hr_assignment.load_assignment_allocation ',16);
4950: hr_utility.set_location('hr_assignment.load_assignment_allocation ',15);
4951: --
4952: end loop;
4953: --
4954: hr_utility.set_location('hr_assignment.load_assignment_allocation ',16);
4955: --
4956: end if;
4957: --
4958: hr_utility.set_location('hr_assignment.load_assignment_allocation ',17);
4954: hr_utility.set_location('hr_assignment.load_assignment_allocation ',16);
4955: --
4956: end if;
4957: --
4958: hr_utility.set_location('hr_assignment.load_assignment_allocation ',17);
4959: --
4960: END load_assignment_allocation;
4961: -----------------------------------------------------------------------
4962: -- update_assgn_context_value
4994: and primary_flag = 'Y';
4995:
4996: begin
4997:
4998: hr_utility.set_location('Entering: '||l_proc_name,10);
4999:
5000: hr_utility.set_location('Business_group_id: '||p_business_group_id,11);
5001: hr_utility.set_location('Person_id: '||p_person_id,12);
5002: hr_utility.set_location('Assignment_id: '||p_assignment_id,13);
4996: begin
4997:
4998: hr_utility.set_location('Entering: '||l_proc_name,10);
4999:
5000: hr_utility.set_location('Business_group_id: '||p_business_group_id,11);
5001: hr_utility.set_location('Person_id: '||p_person_id,12);
5002: hr_utility.set_location('Assignment_id: '||p_assignment_id,13);
5003: hr_utility.set_location('Effective_start_date: '||p_effective_start_date,14);
5004:
4997:
4998: hr_utility.set_location('Entering: '||l_proc_name,10);
4999:
5000: hr_utility.set_location('Business_group_id: '||p_business_group_id,11);
5001: hr_utility.set_location('Person_id: '||p_person_id,12);
5002: hr_utility.set_location('Assignment_id: '||p_assignment_id,13);
5003: hr_utility.set_location('Effective_start_date: '||p_effective_start_date,14);
5004:
5005: open csr_dff_context ;
4998: hr_utility.set_location('Entering: '||l_proc_name,10);
4999:
5000: hr_utility.set_location('Business_group_id: '||p_business_group_id,11);
5001: hr_utility.set_location('Person_id: '||p_person_id,12);
5002: hr_utility.set_location('Assignment_id: '||p_assignment_id,13);
5003: hr_utility.set_location('Effective_start_date: '||p_effective_start_date,14);
5004:
5005: open csr_dff_context ;
5006: fetch csr_dff_context into l_context_val;
4999:
5000: hr_utility.set_location('Business_group_id: '||p_business_group_id,11);
5001: hr_utility.set_location('Person_id: '||p_person_id,12);
5002: hr_utility.set_location('Assignment_id: '||p_assignment_id,13);
5003: hr_utility.set_location('Effective_start_date: '||p_effective_start_date,14);
5004:
5005: open csr_dff_context ;
5006: fetch csr_dff_context into l_context_val;
5007:
5008: if csr_dff_context%found then
5009:
5010: if l_context_val <> '-100' then
5011:
5012: hr_utility.set_location('DFF setting exists',20);
5013:
5014: if instr(upper(l_context_val),'$PROFILES$') <> 0 then
5015: l_context_val:=replace(upper(l_context_val),'$PROFILES$.PER_');
5016: l_context_val:=replace(upper(l_context_val),'$PROFILES$.');
5020: fetch csr_ass_data into l_ass_attribute_category;
5021:
5022: if csr_ass_data%found then
5023:
5024: hr_utility.set_location('l_ass_att: '||l_ass_attribute_category,15);
5025:
5026: if l_ass_attribute_category is null
5027: or trim(l_ass_attribute_category) = '' then
5028:
5027: or trim(l_ass_attribute_category) = '' then
5028:
5029: begin
5030:
5031: hr_utility.set_location(l_proc_name||' got value for reference field',30);
5032:
5033: l_effective_start_date:= p_effective_start_date;
5034:
5035: hr_utility.set_location(l_proc_name||' selecting records ',30);
5031: hr_utility.set_location(l_proc_name||' got value for reference field',30);
5032:
5033: l_effective_start_date:= p_effective_start_date;
5034:
5035: hr_utility.set_location(l_proc_name||' selecting records ',30);
5036:
5037: l_sql := '
5038: declare
5039: g_rec per_assignments_v%rowtype;
5045: EXECUTE IMMEDIATE l_sql using
5046: in out l_effective_start_date,
5047: in out l_output_context_val;
5048:
5049: hr_utility.set_location(l_proc_name||'l_output_context_val: '||l_output_context_val,35);
5050:
5051:
5052: if l_output_context_val is not null then
5053:
5057: and person_id = p_person_id
5058: and assignment_id = p_assignment_id
5059: and effective_start_date = p_effective_start_date;
5060:
5061: hr_utility.set_location('dynamic sql updated '||sql%rowcount||' records',50);
5062:
5063: end if;
5064:
5065: close csr_ass_data;
5066:
5067: exception
5068: when others then
5069: close csr_ass_data;
5070: hr_utility.set_location(sqlerrm,55);
5071: end;
5072: end if;
5073:
5074: end if;
5077: close csr_dff_context;
5078:
5079: end if;
5080:
5081: hr_utility.set_location('Leaving: '||l_proc_name,60);
5082:
5083: END update_assgn_context_value;
5084:
5085: -- get_assgn_dff_value
5334:
5335: l_proc_name varchar2(20):= 'get_assgn_dff_value';
5336: begin
5337:
5338: hr_utility.set_location('Entering: '||l_proc_name,10);
5339:
5340: OPEN csr_asg;
5341: FETCH csr_asg INTO p_asg_rec;
5342: if csr_asg%found then
5341: FETCH csr_asg INTO p_asg_rec;
5342: if csr_asg%found then
5343: close csr_asg;
5344: end if;
5345: hr_utility.set_location('Leaving: '||l_proc_name,10);
5346: END get_assgn_dff_value;
5347:
5348: --end for bug 6598795
5349: ---------------------------------------------------------------------------------------