[Home] [Help]
283: 115.57 27-oct-08 sidsaxen 7503993 Modified query in gen_new_ass_number
284: to improve performance.
285: 115.58 20-may-08 sathkris 8252045 Modified the cursor csr_dff_context
286: in the procedure update_assgn_context_value
287: 115.59 31-AUG-09 ktithy 8710298 Removed hr_utility.set_location calls.
288: 115.60 17-DEC-10 vepravee 9669561 Modified the update of
289: PER_ASSIGNMENT_BUDGET_VALUES_F in the
290: procedure del_ref_int_delete.
291: 115.61 19-JUL-11 vepravee 11078262 Modified the delete of
328: v_date_probation_end DATE;
329: --
330: BEGIN
331: --
332: hr_utility.set_location('hr_assignment.gen_probation_end',1);
333: --
334: v_start_date := p_start_date;
335: v_date_probation_end := p_date_probation_end;
336: --
337: IF v_date_probation_end IS NULL THEN
338: --
339: -- generate new default probation end date
340: --
341: hr_utility.set_location('hr_assignment.gen_probation_end',2);
342: --
343: /*------ changes made for bug 5619940 ---- */
344: IF p_probation_period = 0
345: and ( p_probation_unit = 'D'
369: --
370: ELSIF
371: p_assignment_id IS NULL THEN
372: --
373: hr_utility.set_location('hr_assignment.gen_probation_end',3);
374: --
375: -- If the Assignment is a new one
376: -- ensure that the DATE_PROBATION_END is on or after the assignment
377: -- start date
376: -- ensure that the DATE_PROBATION_END is on or after the assignment
377: -- start date
378: --
379: IF v_date_probation_end < v_start_date THEN
380: hr_utility.set_message(801,'HR_6150_EMP_ASS_PROB_END');
381: hr_utility.raise_error;
382: END IF;
383: --
384: ELSE
377: -- start date
378: --
379: IF v_date_probation_end < v_start_date THEN
380: hr_utility.set_message(801,'HR_6150_EMP_ASS_PROB_END');
381: hr_utility.raise_error;
382: END IF;
383: --
384: ELSE
385: --
389: -- date for the assignment.
390: --
391: BEGIN
392: --
393: hr_utility.set_location('hr_assignment.gen_probation_end',4);
394: select v_date_probation_end - min(effective_start_date)
395: into check_date
396: from per_assignments_f
397: where assignment_id = p_assignment_id
401: WHEN NO_DATA_FOUND THEN NULL;
402: --
403: END;
404: --
405: hr_utility.set_location('hr_assignment.gen_probation_end',5);
406: --
407: IF check_date < 0 THEN
408: hr_utility.set_message(801,'HR_6150_EMP_ASS_PROB_END');
409: hr_utility.raise_error;
404: --
405: hr_utility.set_location('hr_assignment.gen_probation_end',5);
406: --
407: IF check_date < 0 THEN
408: hr_utility.set_message(801,'HR_6150_EMP_ASS_PROB_END');
409: hr_utility.raise_error;
410: END IF;
411: --
412: END IF;
405: hr_utility.set_location('hr_assignment.gen_probation_end',5);
406: --
407: IF check_date < 0 THEN
408: hr_utility.set_message(801,'HR_6150_EMP_ASS_PROB_END');
409: hr_utility.raise_error;
410: END IF;
411: --
412: END IF;
413: --
431: ) is
432: --
433: begin
434: --
435: hr_utility.set_location('hr_assignment.gen_new_ass_sequence',1);
436: select nvl(max(assignment_sequence),0) +1
437: into p_assignment_sequence
438: from per_assignments_f
439: where person_id = p_person_id
522: duplicate := 'N';
523: --
524: BEGIN
525: --
526: hr_utility.set_location('hr_assignment.gen_new_ass_number',1);
527: select 'Y'
528: into duplicate
529: from sys.dual
530: where exists
554: ass_seq := p_assignment_sequence;
555: --
556: IF p_assignment_number IS NOT NULL THEN
557: --
558: hr_utility.set_location('hr_assignment.gen_new_ass_number',2);
559: IF validate_ass_number(p_assignment_id
560: ,p_business_group_id
561: ,p_assignment_number) THEN
562: NULL;
560: ,p_business_group_id
561: ,p_assignment_number) THEN
562: NULL;
563: ELSE
564: hr_utility.set_message(801,'HR_6146_EMP_ASS_DUPL_NUMBER');
565: hr_utility.raise_error;
566: END IF;
567: --
568: ELSE
561: ,p_assignment_number) THEN
562: NULL;
563: ELSE
564: hr_utility.set_message(801,'HR_6146_EMP_ASS_DUPL_NUMBER');
565: hr_utility.raise_error;
566: END IF;
567: --
568: ELSE
569: --
566: END IF;
567: --
568: ELSE
569: --
570: hr_utility.set_location('hr_assignment.gen_new_ass_number',3);
571: WHILE loop_count > 0 LOOP
572: --
573: IF ass_seq = 1 THEN
574: -- p_assignment_number := p_worker_number;
641: p_assignment_number := substr(p_worker_number,1,29-length(TO_CHAR(ass_seq)))||'-'||TO_CHAR(ass_seq);
642: -- p_assignment_number := p_worker_number||'-'||TO_CHAR(ass_seq);
643: END IF;
644: --
645: hr_utility.set_location('hr_assignment.gen_new_ass_number',4);
646: IF validate_ass_number(p_assignment_id
647: ,p_business_group_id
648: ,p_assignment_number) THEN
649: EXIT;
653: END IF;
654: --
655: END LOOP;
656: --
657: hr_utility.set_location('hr_assignment.gen_new_ass_number',5);
658: IF loop_count = 0 THEN
659: hr_utility.set_message(801,'HR_6148_EMP_ASS_LOOP_OUT');
660: hr_utility.raise_error;
661: END IF;
655: END LOOP;
656: --
657: hr_utility.set_location('hr_assignment.gen_new_ass_number',5);
658: IF loop_count = 0 THEN
659: hr_utility.set_message(801,'HR_6148_EMP_ASS_LOOP_OUT');
660: hr_utility.raise_error;
661: END IF;
662: --
663: END IF;
656: --
657: hr_utility.set_location('hr_assignment.gen_new_ass_number',5);
658: IF loop_count = 0 THEN
659: hr_utility.set_message(801,'HR_6148_EMP_ASS_LOOP_OUT');
660: hr_utility.raise_error;
661: END IF;
662: --
663: END IF;
664: --
687: no_of_hours NUMBER;
688: --
689: BEGIN
690: --
691: hr_utility.set_location('hr_assignment.check_hours',1);
692: IF p_frequency = 'D' THEN
693: no_of_hours := 24;
694: ELSIF p_frequency = 'W' THEN
695: no_of_hours := 168;
703: no_of_hours := 0;
704: END IF;
705: --
706: IF no_of_hours - p_normal_hours < 0 THEN
707: hr_utility.set_message(801,'HR_6015_ALL_FORMAT_WKG_HRS');
708: hr_utility.raise_error;
709: END IF;
710: --
711: END check_hours; -----------------------------------------------------
704: END IF;
705: --
706: IF no_of_hours - p_normal_hours < 0 THEN
707: hr_utility.set_message(801,'HR_6015_ALL_FORMAT_WKG_HRS');
708: hr_utility.raise_error;
709: END IF;
710: --
711: END check_hours; -----------------------------------------------------
712: --
788:
789:
790: BEGIN
791: --
792: hr_utility.set_location('hr_assignment.check_term',1);
793: p_start_date := p_sdate;
794: p_end_date := p_edate;
795: p_new_ass_end_date := null;
796:
810: FETCH csr_get_term_dates INTO p_atd
811: ,p_fpd;
812: CLOSE csr_get_term_dates;
813:
814: hr_utility.set_location('hr_assignment.check_term',2);
815:
816: IF p_atd IS NULL THEN null;
817: ELSE
818: --
818: --
819: -------------------------------------
820: -- Get the Effective End Date of the Assignment
821: -------------------------------------
822: hr_utility.set_location('hr_assignment.check_term',3);
823: --
824: select max(effective_end_date)
825: into p_ass_end_date
826: from per_assignments_f
832: -- If the mode is UPDATE_OVERRIDE and the current status is TERM_ASSIGN
833: -- then compare the session date with the earliest TERM_ASSIGN date
834: -- and store the earliest.
835: -------------------------------------
836: hr_utility.set_location('hr_assignment.check_term',4);
837: --
838: select min(a.effective_start_date)
839: into p_first_term_date
840: from per_assignments_f a
844: where s.assignment_status_type_id
845: = a.assignment_status_type_id
846: and s.per_system_status = 'TERM_ASSIGN');
847: --
848: hr_utility.set_location('hr_assignment.check_term',5);
849: IF p_mode = 'UPDATE_OVERRIDE' AND
850: p_current_status = 'TERM_ASSIGN' THEN
851: --
852: IF p_first_term_date IS NULL OR
870:
871: -- Bug 306713 Start
872:
873: IF (p_mode = 'DELETE_NEXT_CHANGE' or p_mode = 'FUTURE_CHANGE') and p_ass_end_date = p_atd then
874: hr_utility.set_message(801,'HR_6200_EMP_ASS_TERM_EXISTS');
875: hr_utility.raise_error;
876: end if;
877:
878: -- Bug 306713 End
871: -- Bug 306713 Start
872:
873: IF (p_mode = 'DELETE_NEXT_CHANGE' or p_mode = 'FUTURE_CHANGE') and p_ass_end_date = p_atd then
874: hr_utility.set_message(801,'HR_6200_EMP_ASS_TERM_EXISTS');
875: hr_utility.raise_error;
876: end if;
877:
878: -- Bug 306713 End
879:
879:
880: IF (p_mode = 'UPDATE_OVERRIDE' or p_mode = 'FUTURE_CHANGE')
881: AND p_start_date < p_atd + 1 THEN
882: --
883: hr_utility.set_location('hr_assignment.check_term',6);
884: --
885: p_flag := 'N';
886: --
887: BEGIN
885: p_flag := 'N';
886: --
887: BEGIN
888: --
889: hr_utility.set_location('hr_assignment.check_term',7);
890: --
891: select 'Y'
892: into p_flag
893: from per_assignments_f a
902: EXCEPTION
903: WHEN NO_DATA_FOUND THEN NULL;
904: END;
905: --
906: hr_utility.set_location('hr_assignment.check_term',8);
907: IF p_flag = 'Y' THEN
908: hr_utility.set_message(801,'HR_6200_EMP_ASS_TERM_EXISTS');
909: hr_utility.raise_error;
910: END IF;
904: END;
905: --
906: hr_utility.set_location('hr_assignment.check_term',8);
907: IF p_flag = 'Y' THEN
908: hr_utility.set_message(801,'HR_6200_EMP_ASS_TERM_EXISTS');
909: hr_utility.raise_error;
910: END IF;
911: END IF;
912: --
905: --
906: hr_utility.set_location('hr_assignment.check_term',8);
907: IF p_flag = 'Y' THEN
908: hr_utility.set_message(801,'HR_6200_EMP_ASS_TERM_EXISTS');
909: hr_utility.raise_error;
910: END IF;
911: END IF;
912: --
913: ---------------------------------------------------------------
920: -- otherwise
921: -- set the new_assignment_end_date = actual_termination_date
922: ---------------------------------------------------------------
923: IF p_mode = 'UPDATE_OVERRIDE' THEN
924: hr_utility.set_location('hr_assignment.check_term',9);
925: IF p_ass_end_date <= p_atd THEN
926: NULL;
927: ELSE
928: hr_utility.set_location('hr_assignment.check_term',10);
924: hr_utility.set_location('hr_assignment.check_term',9);
925: IF p_ass_end_date <= p_atd THEN
926: NULL;
927: ELSE
928: hr_utility.set_location('hr_assignment.check_term',10);
929: IF p_first_term_date <= p_start_date THEN
930: NULL;
931: ELSE
932: p_new_ass_end_date := p_atd;
941: -- open the assignment up to the actual term date.
942: ---------------------------------------------------------------
943: ELSIF
944: p_mode = 'FUTURE_CHANGE' THEN
945: hr_utility.set_location('hr_assignment.check_term',11);
946: IF p_first_term_date <= p_start_date THEN
947: p_new_ass_end_date := p_fpd;
948: ELSE
949: p_new_ass_end_date := p_atd;
988: -- therefore in this case issue an error.
989: ---------------------------------------------------------------
990: ELSIF
991: p_mode = 'DELETE_NEXT_CHANGE' THEN
992: hr_utility.set_location('hr_assignment.check_term',12);
993: IF p_end_date = p_ass_end_date THEN
994: hr_utility.set_location('hr_assignment.check_term',13);
995: IF p_first_term_date <= p_start_date THEN
996: p_new_ass_end_date := p_fpd;
990: ELSIF
991: p_mode = 'DELETE_NEXT_CHANGE' THEN
992: hr_utility.set_location('hr_assignment.check_term',12);
993: IF p_end_date = p_ass_end_date THEN
994: hr_utility.set_location('hr_assignment.check_term',13);
995: IF p_first_term_date <= p_start_date THEN
996: p_new_ass_end_date := p_fpd;
997: ELSE
998: p_new_ass_end_date := p_atd;
1002: p_flag := 'N';
1003: --
1004: BEGIN
1005: --
1006: hr_utility.set_location('hr_assignment.check_term',14);
1007: --
1008: select 'Y'
1009: , a.effective_start_date
1010: , a.effective_end_date
1023: EXCEPTION
1024: WHEN NO_DATA_FOUND THEN NULL;
1025: END;
1026: --
1027: hr_utility.set_location('hr_assignment.check_term',15);
1028: IF p_flag = 'Y' THEN
1029: hr_utility.set_location('hr_assignment.check_term',16);
1030: IF p_next_eff_start_date = p_atd + 1 THEN
1031: hr_utility.set_message(801,'HR_6200_EMP_ASS_TERM_EXISTS');
1025: END;
1026: --
1027: hr_utility.set_location('hr_assignment.check_term',15);
1028: IF p_flag = 'Y' THEN
1029: hr_utility.set_location('hr_assignment.check_term',16);
1030: IF p_next_eff_start_date = p_atd + 1 THEN
1031: hr_utility.set_message(801,'HR_6200_EMP_ASS_TERM_EXISTS');
1032: hr_utility.raise_error;
1033: ELSE
1027: hr_utility.set_location('hr_assignment.check_term',15);
1028: IF p_flag = 'Y' THEN
1029: hr_utility.set_location('hr_assignment.check_term',16);
1030: IF p_next_eff_start_date = p_atd + 1 THEN
1031: hr_utility.set_message(801,'HR_6200_EMP_ASS_TERM_EXISTS');
1032: hr_utility.raise_error;
1033: ELSE
1034: IF p_first_term_date <= p_start_date THEN
1035: hr_utility.set_location('hr_assignment.check_term',17);
1028: IF p_flag = 'Y' THEN
1029: hr_utility.set_location('hr_assignment.check_term',16);
1030: IF p_next_eff_start_date = p_atd + 1 THEN
1031: hr_utility.set_message(801,'HR_6200_EMP_ASS_TERM_EXISTS');
1032: hr_utility.raise_error;
1033: ELSE
1034: IF p_first_term_date <= p_start_date THEN
1035: hr_utility.set_location('hr_assignment.check_term',17);
1036: p_new_ass_end_date := p_fpd;
1031: hr_utility.set_message(801,'HR_6200_EMP_ASS_TERM_EXISTS');
1032: hr_utility.raise_error;
1033: ELSE
1034: IF p_first_term_date <= p_start_date THEN
1035: hr_utility.set_location('hr_assignment.check_term',17);
1036: p_new_ass_end_date := p_fpd;
1037: ELSE
1038: IF p_next_eff_end_date = p_ass_end_date THEN
1039: hr_utility.set_location('hr_assignment.check_term',18);
1035: hr_utility.set_location('hr_assignment.check_term',17);
1036: p_new_ass_end_date := p_fpd;
1037: ELSE
1038: IF p_next_eff_end_date = p_ass_end_date THEN
1039: hr_utility.set_location('hr_assignment.check_term',18);
1040: IF p_ass_end_date > p_atd THEN
1041: p_new_ass_end_date := p_atd;
1042: ELSE
1043: NULL;
1043: NULL;
1044: END IF; -- (p_ass_end_date > p_atd)
1045: ELSE
1046: IF p_next_eff_end_date >= p_atd + 1 THEN
1047: hr_utility.set_message(801,'HR_6320_EMP_ASS_AFTER_ATD');
1048: hr_utility.raise_error;
1049: END IF; -- (p_next_eff_end_date >= p_atd + 1)
1050: END IF; -- (p_next_eff_end_date = p_ass_end_date)
1051: END IF; -- (p_first_term_date <= p_start_date)
1044: END IF; -- (p_ass_end_date > p_atd)
1045: ELSE
1046: IF p_next_eff_end_date >= p_atd + 1 THEN
1047: hr_utility.set_message(801,'HR_6320_EMP_ASS_AFTER_ATD');
1048: hr_utility.raise_error;
1049: END IF; -- (p_next_eff_end_date >= p_atd + 1)
1050: END IF; -- (p_next_eff_end_date = p_ass_end_date)
1051: END IF; -- (p_first_term_date <= p_start_date)
1052: END IF; -- (p_next_eff_start_date = p_atd + 1)
1056: END IF; -- (p_mode = 'UPDATE_OVERRIDE')
1057: --
1058: END IF; -- (p_atd IS NULL)
1059: --
1060: hr_utility.set_location('hr_assignment.check_term',19);
1061: IF p_new_ass_end_date IS NOT NULL THEN
1062: ------------------------------------------------------------
1063: -- First check whether setting this end date will invalidate
1064: -- any child rows.
1104: p_term_found := 'N';
1105: --
1106: begin
1107: --
1108: hr_utility.set_location('hr_assignment.warn_del_term',1);
1109: --
1110: select 'Y'
1111: into p_term_found
1112: from sys.dual
1127: exception
1128: when NO_DATA_FOUND then null;
1129: end;
1130: --
1131: hr_utility.set_location('hr_assignment.warn_del_term',2);
1132: if p_term_found = 'Y' then
1133: raise local_warning;
1134: end if;
1135: --
1134: end if;
1135: --
1136: EXCEPTION
1137: when local_warning then
1138: hr_utility.set_warning;
1139: END warn_del_term;
1140: --
1141: --
1142: ------------------- delete_ass_ref_int ----------------------------
1166: --
1167: -- del_flag := 'N';
1168: --
1169: BEGIN
1170: hr_utility.set_location('hr_assignment.delete_ass_ref_int',1);
1171: --
1172: SELECT 'Y'
1173: into del_flag
1174: FROM SYS.DUAL
1182: WHEN NO_DATA_FOUND THEN NULL;
1183: END;
1184: --
1185: IF del_flag = 'Y' THEN
1186: hr_utility.set_location('hr_assignment.delete_ass_ref_int',2);
1187: --
1188: DELETE FROM PER_SPINAL_POINT_PLACEMENTS_F P
1189: WHERE P.business_group_id + 0 = p_business_group_id
1190: AND P.ASSIGNMENT_ID = p_assignment_id;
1194: del_flag := 'N';
1195: --
1196: BEGIN
1197: --
1198: hr_utility.set_location('hr_assignment.delete_ass_ref_int',3);
1199: --
1200: SELECT 'Y'
1201: into del_flag
1202: from sys.dual
1210: WHEN NO_DATA_FOUND THEN NULL;
1211: END;
1212: --
1213: IF del_flag = 'Y' THEN
1214: hr_utility.set_location('hr_assignment.delete_ass_ref_int',4);
1215: --
1216: DELETE FROM PER_SECONDARY_ASS_STATUSES
1217: WHERE business_group_id + 0 = p_business_group_id
1218: AND ASSIGNMENT_ID = p_assignment_id;
1221: --
1222: del_flag := 'N';
1223: --
1224: BEGIN
1225: hr_utility.set_location('hr_assignment.delete_ass_ref_int',5);
1226: --
1227: SELECT 'Y'
1228: into del_flag
1229: from sys.dual
1237: WHEN NO_DATA_FOUND THEN NULL;
1238: END;
1239: --
1240: IF del_flag = 'Y' THEN
1241: hr_utility.set_location('hr_assignment.delete_ass_ref_int',6);
1242: --
1243: DELETE FROM PER_ASSIGNMENT_BUDGET_VALUES_F BV
1244: WHERE BV.business_group_id + 0 = p_business_group_id
1245: AND BV.ASSIGNMENT_ID = p_assignment_id;
1270: --
1271: -------------------------------------------------
1272: -- Retrieve the ACTUAL TERMINATION DATE for the Period of Service
1273: -------------------------------------------------
1274: hr_utility.set_location('hr_assignment.get_act_term_date',1);
1275: --
1276: select actual_termination_date
1277: into p_actual_termination_date
1278: from per_periods_of_service
1329:
1330: --
1331: BEGIN
1332: --
1333: hr_utility.set_location('hr_assignment.check_future_primary',1);
1334: p_start_date := p_sdate;
1335: p_end_date := p_edate;
1336: --
1337: -------------------------------------
1363: -- NEXT_CHANGE - the next row
1364: -- FUTURE_CHANGES and UPDATE_OVERRIDE - All rows in future
1365: -------------------------------------
1366: begin
1367: hr_utility.set_location('hr_assignment.check_future_primary',2);
1368: --
1369: select 'Y'
1370: into p_change_flag
1371: from sys.dual
1388: -- retrieve the earliest occurrence of PRIMARY_FLAG = 'Y'. A new
1389: -- Primary Assignment will be required from this date.
1390: ---------------------------------------
1391: IF p_mode = 'ZAP' AND p_change_flag = 'Y' THEN
1392: hr_utility.set_location('hr_assignment.check_future_primary',3);
1393: --
1394: select min(effective_start_date)
1395: into p_primary_date_from_d
1396: from per_assignments_f
1477:
1478: -------------------------------------
1479: -- Get the Actual Termination Date and Final Process Date
1480: -------------------------------------
1481: hr_utility.set_location('hr_assignment.check_ass_for_primary',1);
1482: --
1483: -- #306211. If ATD was null, then NVL to EOT - 1, instead of EOT. This is
1484: -- because trying to add 1 to EOT (as happens in a number of places below)
1485: -- raises an ORA-1841 error. So use Dec 30 instead of Dec 31.
1491:
1492: -------------------------------------
1493: -- Get the Effective End Date of the Assignment
1494: -------------------------------------
1495: hr_utility.set_location('hr_assignment.check_ass_for_primary',2);
1496: --
1497: select max(effective_end_date)
1498: into p_ass_end_date
1499: from per_assignments_f
1502: -- If the end date of the assignment is not on or after the
1503: -- period of service final process date then ERROR
1504: --
1505: IF p_ass_end_date < p_fpd THEN
1506: hr_utility.set_message(801,'HR_6380_EMP_ASS_END_OFF_FPD');
1507: hr_utility.raise_error;
1508: END IF;
1509: --
1510: -------------------------------------
1503: -- period of service final process date then ERROR
1504: --
1505: IF p_ass_end_date < p_fpd THEN
1506: hr_utility.set_message(801,'HR_6380_EMP_ASS_END_OFF_FPD');
1507: hr_utility.raise_error;
1508: END IF;
1509: --
1510: -------------------------------------
1511: -- If the Start Date of the record is on or before the day after ATD
1517: ELSE
1518: -------------------------------------
1519: -- Get the Start Date of the First terminated status.
1520: -------------------------------------
1521: hr_utility.set_location('hr_assignment.check_ass_for_primary',3);
1522: --
1523: if l_assignment_type <> 'C' then
1524:
1525: select min(a.effective_start_date)
1531: where s.assignment_status_type_id
1532: = a.assignment_status_type_id
1533: and s.per_system_status = 'TERM_ASSIGN');
1534: --
1535: hr_utility.set_location('hr_assignment.check_ass_for_primary',4);
1536: IF p_first_term_date = p_atd + 1
1537: OR
1538: p_first_term_date IS NULL THEN NULL;
1539: ELSE
1536: IF p_first_term_date = p_atd + 1
1537: OR
1538: p_first_term_date IS NULL THEN NULL;
1539: ELSE
1540: hr_utility.set_message(801,'HR_6381_EMP_ASS_TERM_OFF_ATD');
1541: hr_utility.raise_error;
1542: END IF;
1543: END IF;
1544: END IF;
1537: OR
1538: p_first_term_date IS NULL THEN NULL;
1539: ELSE
1540: hr_utility.set_message(801,'HR_6381_EMP_ASS_TERM_OFF_ATD');
1541: hr_utility.raise_error;
1542: END IF;
1543: END IF;
1544: END IF;
1545: --
1572: AND effective_end_date >= l_start_date;
1573: --
1574: BEGIN
1575: --
1576: hr_utility.set_location('Entering : '||l_proc,10);
1577: --
1578: l_start_date := p_sdate;
1579: --
1580: -- Update the future changes for the current
1581: -- assignment with the new Primary Flag
1582: --
1583: IF p_mode <> 'ZAP' THEN
1584: --
1585: hr_utility.set_location(l_proc,20);
1586: --
1587: ----------------------------------------------------------------
1588: -- 3584122: The value of 'N' is not updating the future
1589: -- changes of the secondar_assignment to primary if a
1600: ,p_last_update_login);
1601: --
1602: END IF;
1603: --
1604: hr_utility.set_location(l_proc,30);
1605: --
1606: -- If the New Primary Asg is not the Current Asg then the Primary
1607: -- Flag has to be set to 'Y' on all the changes on or after the
1608: -- Start Date
1608: -- Start Date
1609: --
1610: IF p_assignment_id <> p_new_primary_ass_id THEN
1611: --
1612: hr_utility.set_location(l_proc,40);
1613: --
1614: do_primary_update
1615: (p_new_primary_ass_id
1616: ,p_sdate
1620: ,p_last_update_login);
1621: --
1622: END IF;
1623: --
1624: hr_utility.set_location(l_proc,50);
1625: --
1626: -- Now for each assignment other than P_ASSIGNMENT_ID and
1627: -- P_NEW_PRIMARY_ASS_ID within the period_of_service
1628: -- future changes must have their Primary Flag set to 'N'. It is only
1639: ,p_last_update_login);
1640: --
1641: END LOOP;
1642: --
1643: hr_utility.set_location(' Leaving : '||l_proc,999);
1644: --
1645: END update_primary_cwk;
1646: --
1647: --
1711: -- back to p_new_primary_flag.
1712: -------------------------------------
1713: --
1714: IF p_mode <> 'ZAP' THEN
1715: hr_utility.set_location('hr_assignment.update_primary',1);
1716: do_primary_update(p_assignment_id
1717: ,p_sdate
1718: ,p_new_primary_flag -- Bug 3584122 'N'-- Bug 2468916 p_new_primary_flag
1719: ,'Y'
1727: -- Flag has to be set to 'Y' on all the changes on or after the
1728: -- Start Date
1729: -------------------------------------
1730: IF p_assignment_id <> p_new_primary_ass_id THEN
1731: hr_utility.set_location('hr_assignment.update_primary',2);
1732: do_primary_update(p_new_primary_ass_id
1733: ,p_sdate
1734: ,'Y'
1735: ,'N'
1743: -- P_NEW_PRIMARY_ASS_ID within the period_of_service
1744: -- future changes must have their Primary Flag set to 'N'. It is only
1745: -- necessary to update the ones that are currently 'Y'.
1746: -------------------------------------
1747: hr_utility.set_location('hr_assignment.update_primary',3);
1748: --
1749: FOR ass_rec IN get_future_primary_assignments LOOP
1750: do_primary_update(ass_rec.assignment_id
1751: ,p_sdate
1801: and P_START_DATE < effective_start_date))
1802: for update;
1803: --
1804: BEGIN
1805: hr_utility.set_location('hr_assignment.do_primary_update',1);
1806: p_start_date := p_sdate;
1807: --
1808: -------------------------------------
1809: -- If the Assignment is Current i.e. P_CURRENT_ASS = 'Y' then the form
1838: --
1839: -- See CURSOR select_ass_for_update
1840: ----------------------------------------------------------------------
1841: --
1842: hr_utility.set_location('hr_assignment.do_primary_update',2);
1843: --
1844: FOR ass_rec IN select_ass_for_update LOOP
1845: NULL;
1846: END LOOP;
1847: --
1848: IF p_current_ass = 'Y' THEN
1849: NULL;
1850: ELSE
1851: hr_utility.set_location('hr_assignment.do_primary_update',3);
1852: --
1853: update per_assignments_f
1854: set primary_flag = P_PRIMARY_FLAG
1855: , last_updated_by = P_LAST_UPDATED_BY
1857: , last_update_date = sysdate
1858: where assignment_id = P_ASSIGNMENT_ID
1859: and effective_start_date = P_START_DATE;
1860: --
1861: -- hr_utility.set_location('hr_assignment.do_primary_update',4); -- Fix For Bug # 8710298. Commented the Call.
1862: IF SQL%ROWCOUNT = 1 THEN
1863: NULL;
1864: ELSIF SQL%ROWCOUNT > 1 THEN
1865: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
1861: -- hr_utility.set_location('hr_assignment.do_primary_update',4); -- Fix For Bug # 8710298. Commented the Call.
1862: IF SQL%ROWCOUNT = 1 THEN
1863: NULL;
1864: ELSIF SQL%ROWCOUNT > 1 THEN
1865: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
1866: hr_utility.set_message_token('PROCEDURE','DO_PRIMARY_UPDATE');
1867: hr_utility.set_message_token('STEP','1');
1868: hr_utility.raise_error;
1869: ELSE
1862: IF SQL%ROWCOUNT = 1 THEN
1863: NULL;
1864: ELSIF SQL%ROWCOUNT > 1 THEN
1865: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
1866: hr_utility.set_message_token('PROCEDURE','DO_PRIMARY_UPDATE');
1867: hr_utility.set_message_token('STEP','1');
1868: hr_utility.raise_error;
1869: ELSE
1870: --
1863: NULL;
1864: ELSIF SQL%ROWCOUNT > 1 THEN
1865: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
1866: hr_utility.set_message_token('PROCEDURE','DO_PRIMARY_UPDATE');
1867: hr_utility.set_message_token('STEP','1');
1868: hr_utility.raise_error;
1869: ELSE
1870: --
1871: hr_utility.set_location('hr_assignment.do_primary_update',5);
1864: ELSIF SQL%ROWCOUNT > 1 THEN
1865: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
1866: hr_utility.set_message_token('PROCEDURE','DO_PRIMARY_UPDATE');
1867: hr_utility.set_message_token('STEP','1');
1868: hr_utility.raise_error;
1869: ELSE
1870: --
1871: hr_utility.set_location('hr_assignment.do_primary_update',5);
1872: --
1867: hr_utility.set_message_token('STEP','1');
1868: hr_utility.raise_error;
1869: ELSE
1870: --
1871: hr_utility.set_location('hr_assignment.do_primary_update',5);
1872: --
1873: insert into per_assignments_f
1874: (
1875: ASSIGNMENT_ID
2102: --
2103: IF SQL%ROWCOUNT = 0 THEN
2104: NULL; -- This Assignment Start in the Future
2105: ELSE
2106: hr_utility.set_location('hr_assignment.do_primary_update',6);
2107: --
2108: update per_assignments_f
2109: set effective_start_date = P_START_DATE
2110: , primary_flag = P_PRIMARY_FLAG
2115: and P_START_DATE
2116: between effective_start_date and effective_end_date
2117: and primary_flag <> P_PRIMARY_FLAG;
2118: --
2119: -- hr_utility.set_location('hr_assignment.do_primary_update',7); -- Fix For Bug # 8710298. Commented the call.
2120: IF SQL%ROWCOUNT <> 1 THEN
2121: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2122: hr_utility.set_message_token('PROCEDURE','DO_PRIMARY_UPDATE');
2123: hr_utility.set_message_token('STEP','3');
2117: and primary_flag <> P_PRIMARY_FLAG;
2118: --
2119: -- hr_utility.set_location('hr_assignment.do_primary_update',7); -- Fix For Bug # 8710298. Commented the call.
2120: IF SQL%ROWCOUNT <> 1 THEN
2121: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2122: hr_utility.set_message_token('PROCEDURE','DO_PRIMARY_UPDATE');
2123: hr_utility.set_message_token('STEP','3');
2124: hr_utility.raise_error;
2125: END IF; -- (SQL%ROWCOUNT <> 1)
2118: --
2119: -- hr_utility.set_location('hr_assignment.do_primary_update',7); -- Fix For Bug # 8710298. Commented the call.
2120: IF SQL%ROWCOUNT <> 1 THEN
2121: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2122: hr_utility.set_message_token('PROCEDURE','DO_PRIMARY_UPDATE');
2123: hr_utility.set_message_token('STEP','3');
2124: hr_utility.raise_error;
2125: END IF; -- (SQL%ROWCOUNT <> 1)
2126: hr_utility.set_location('hr_assignment.do_primary_update',7); -- Fix For Bug # 8710298 . Moved the hr_utility.set_location call.
2119: -- hr_utility.set_location('hr_assignment.do_primary_update',7); -- Fix For Bug # 8710298. Commented the call.
2120: IF SQL%ROWCOUNT <> 1 THEN
2121: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2122: hr_utility.set_message_token('PROCEDURE','DO_PRIMARY_UPDATE');
2123: hr_utility.set_message_token('STEP','3');
2124: hr_utility.raise_error;
2125: END IF; -- (SQL%ROWCOUNT <> 1)
2126: hr_utility.set_location('hr_assignment.do_primary_update',7); -- Fix For Bug # 8710298 . Moved the hr_utility.set_location call.
2127: END IF; -- (SQL%ROWCOUNT = 0)
2120: IF SQL%ROWCOUNT <> 1 THEN
2121: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2122: hr_utility.set_message_token('PROCEDURE','DO_PRIMARY_UPDATE');
2123: hr_utility.set_message_token('STEP','3');
2124: hr_utility.raise_error;
2125: END IF; -- (SQL%ROWCOUNT <> 1)
2126: hr_utility.set_location('hr_assignment.do_primary_update',7); -- Fix For Bug # 8710298 . Moved the hr_utility.set_location call.
2127: END IF; -- (SQL%ROWCOUNT = 0)
2128: END IF; -- (SQL%ROWCOUNT = 1)
2122: hr_utility.set_message_token('PROCEDURE','DO_PRIMARY_UPDATE');
2123: hr_utility.set_message_token('STEP','3');
2124: hr_utility.raise_error;
2125: END IF; -- (SQL%ROWCOUNT <> 1)
2126: hr_utility.set_location('hr_assignment.do_primary_update',7); -- Fix For Bug # 8710298 . Moved the hr_utility.set_location call.
2127: END IF; -- (SQL%ROWCOUNT = 0)
2128: END IF; -- (SQL%ROWCOUNT = 1)
2129: END IF; -- (p_current_ass = 'Y')
2130: --
2127: END IF; -- (SQL%ROWCOUNT = 0)
2128: END IF; -- (SQL%ROWCOUNT = 1)
2129: END IF; -- (p_current_ass = 'Y')
2130: --
2131: hr_utility.set_location('hr_assignment.do_primary_update',8);
2132: --
2133: update per_assignments_f
2134: set primary_flag = P_PRIMARY_FLAG
2135: , last_updated_by = P_LAST_UPDATED_BY
2254: BEGIN
2255: --
2256: p_start_date := p_sdate;
2257:
2258: hr_utility.set_location('hr_assignment.get_new_primary_assignment',1);
2259:
2260: --
2261: -- Fetch the desired assignment details.
2262: --
2274: ,p_fpd;
2275: CLOSE csr_get_term_dates;
2276:
2277: --
2278: hr_utility.set_location('hr_assignment.get_new_primary_assignment',2);
2279: --
2280: ---------------------------------------------------
2281: -- open the cursor and read the first record if one exists
2282: -- If one doesn't exists then ERROR
2282: -- If one doesn't exists then ERROR
2283: -- Try and read another record
2284: -- If one exists then WARNING (prompt user in Form for which one)
2285: ---------------------------------------------------
2286: hr_utility.set_location('hr_assignment.get_new_primary_assignment',3);
2287: OPEN get_candidate_primary_ass;
2288: --
2289: hr_utility.set_location('hr_assignment.get_new_primary_assignment',4);
2290: FETCH get_candidate_primary_ass INTO p_new_primary_ass_id;
2285: ---------------------------------------------------
2286: hr_utility.set_location('hr_assignment.get_new_primary_assignment',3);
2287: OPEN get_candidate_primary_ass;
2288: --
2289: hr_utility.set_location('hr_assignment.get_new_primary_assignment',4);
2290: FETCH get_candidate_primary_ass INTO p_new_primary_ass_id;
2291: --
2292: IF get_candidate_primary_ass%NOTFOUND THEN
2293: hr_utility.set_location('hr_assignment.get_new_primary_assignment',5);
2289: hr_utility.set_location('hr_assignment.get_new_primary_assignment',4);
2290: FETCH get_candidate_primary_ass INTO p_new_primary_ass_id;
2291: --
2292: IF get_candidate_primary_ass%NOTFOUND THEN
2293: hr_utility.set_location('hr_assignment.get_new_primary_assignment',5);
2294: CLOSE get_candidate_primary_ass;
2295: hr_utility.set_message(801,'HR_6384_EMP_ASS_NO_PRIM');
2296: hr_utility.raise_error;
2297: ELSE
2291: --
2292: IF get_candidate_primary_ass%NOTFOUND THEN
2293: hr_utility.set_location('hr_assignment.get_new_primary_assignment',5);
2294: CLOSE get_candidate_primary_ass;
2295: hr_utility.set_message(801,'HR_6384_EMP_ASS_NO_PRIM');
2296: hr_utility.raise_error;
2297: ELSE
2298: hr_utility.set_location('hr_assignment.get_new_primary_assignment',7);
2299: FETCH get_candidate_primary_ass INTO p_new_primary_ass_id;
2292: IF get_candidate_primary_ass%NOTFOUND THEN
2293: hr_utility.set_location('hr_assignment.get_new_primary_assignment',5);
2294: CLOSE get_candidate_primary_ass;
2295: hr_utility.set_message(801,'HR_6384_EMP_ASS_NO_PRIM');
2296: hr_utility.raise_error;
2297: ELSE
2298: hr_utility.set_location('hr_assignment.get_new_primary_assignment',7);
2299: FETCH get_candidate_primary_ass INTO p_new_primary_ass_id;
2300: --
2294: CLOSE get_candidate_primary_ass;
2295: hr_utility.set_message(801,'HR_6384_EMP_ASS_NO_PRIM');
2296: hr_utility.raise_error;
2297: ELSE
2298: hr_utility.set_location('hr_assignment.get_new_primary_assignment',7);
2299: FETCH get_candidate_primary_ass INTO p_new_primary_ass_id;
2300: --
2301: hr_utility.set_location('hr_assignment.get_new_primary_assignment',8);
2302: IF get_candidate_primary_ass%FOUND THEN
2297: ELSE
2298: hr_utility.set_location('hr_assignment.get_new_primary_assignment',7);
2299: FETCH get_candidate_primary_ass INTO p_new_primary_ass_id;
2300: --
2301: hr_utility.set_location('hr_assignment.get_new_primary_assignment',8);
2302: IF get_candidate_primary_ass%FOUND THEN
2303: raise local_warning;
2304: END IF;
2305: --
2302: IF get_candidate_primary_ass%FOUND THEN
2303: raise local_warning;
2304: END IF;
2305: --
2306: hr_utility.set_location('hr_assignment.get_new_primary_assignment',9);
2307: CLOSE get_candidate_primary_ass;
2308: END IF;
2309: --
2310: EXCEPTION
2309: --
2310: EXCEPTION
2311: when local_warning then
2312: p_new_primary_ass_id := null ;
2313: hr_utility.set_warning;
2314:
2315: when others then
2316: p_new_primary_ass_id := null;
2317: raise;
2343: ,p_effective_end_date IN DATE) IS
2344:
2345: --
2346: BEGIN
2347: hr_utility.set_location('hr_assignment.load_budget_values',1);
2348: --
2349: /* 25/05/95 Fixed bug 273820 - performance of following statement
2350: NB the business_group_id no longer needs +0 appended to it, this
2351: is because the view per_default_budget_values is in fact returning
2446: p_end_date := p_edate;
2447: --
2448: --
2449: IF p_mode = 'ZAP' THEN
2450: hr_utility.set_location('hr_assignment.del_ref_int_check',0);
2451: p_del_flag := 'N';
2452: --
2453: BEGIN
2454: select 'Y'
2474: WHEN NO_DATA_FOUND THEN NULL;
2475: END;
2476: --
2477: IF p_del_flag = 'Y' THEN
2478: hr_utility.set_message(801,'HR_7625_ASS_DEL_APP_ASS');
2479: hr_utility.raise_error;
2480: END IF;
2481: END IF;
2482: --
2475: END;
2476: --
2477: IF p_del_flag = 'Y' THEN
2478: hr_utility.set_message(801,'HR_7625_ASS_DEL_APP_ASS');
2479: hr_utility.raise_error;
2480: END IF;
2481: END IF;
2482: --
2483: hr_utility.set_location('hr_assignment.del_ref_int_check',1);
2479: hr_utility.raise_error;
2480: END IF;
2481: END IF;
2482: --
2483: hr_utility.set_location('hr_assignment.del_ref_int_check',1);
2484: p_del_flag := 'N';
2485: --
2486: BEGIN
2487: select 'Y'
2499: END;
2500: --
2501: IF p_del_flag = 'Y' THEN
2502: IF p_mode = 'ZAP' THEN
2503: hr_utility.set_message(801,'HR_7630_ASS_EVE_DEL_ASS');
2504: ELSE
2505: hr_utility.set_message(801,'HR_7633_ASS_EVE_END_ASS');
2506: END IF;
2507: hr_utility.raise_error;
2501: IF p_del_flag = 'Y' THEN
2502: IF p_mode = 'ZAP' THEN
2503: hr_utility.set_message(801,'HR_7630_ASS_EVE_DEL_ASS');
2504: ELSE
2505: hr_utility.set_message(801,'HR_7633_ASS_EVE_END_ASS');
2506: END IF;
2507: hr_utility.raise_error;
2508: END IF;
2509: --
2503: hr_utility.set_message(801,'HR_7630_ASS_EVE_DEL_ASS');
2504: ELSE
2505: hr_utility.set_message(801,'HR_7633_ASS_EVE_END_ASS');
2506: END IF;
2507: hr_utility.raise_error;
2508: END IF;
2509: --
2510: --
2511: /* Took out nocopy the check on letter requests as they are now Auto Deleted
2511: /* Took out nocopy the check on letter requests as they are now Auto Deleted
2512: - 2/6/93
2513: */
2514: /*
2515: hr_utility.set_location('hr_assignment.del_ref_int_check',2);
2516: p_del_flag := 'N';
2517: --
2518: BEGIN
2519: select 'Y'
2531: END;
2532: --
2533: IF p_del_flag = 'Y' THEN
2534: IF p_mode = 'ZAP' THEN
2535: hr_utility.set_message(801,'HR_7634_ASS_LET_DEL_ASS');
2536: ELSE
2537: hr_utility.set_message(801,'HR_7637_ASS_EVE_END_ASS');
2538: END IF;
2539: hr_utility.raise_error;
2533: IF p_del_flag = 'Y' THEN
2534: IF p_mode = 'ZAP' THEN
2535: hr_utility.set_message(801,'HR_7634_ASS_LET_DEL_ASS');
2536: ELSE
2537: hr_utility.set_message(801,'HR_7637_ASS_EVE_END_ASS');
2538: END IF;
2539: hr_utility.raise_error;
2540: END IF;
2541: */
2535: hr_utility.set_message(801,'HR_7634_ASS_LET_DEL_ASS');
2536: ELSE
2537: hr_utility.set_message(801,'HR_7637_ASS_EVE_END_ASS');
2538: END IF;
2539: hr_utility.raise_error;
2540: END IF;
2541: */
2542: --
2543: --
2540: END IF;
2541: */
2542: --
2543: --
2544: hr_utility.set_location('hr_assignment.del_ref_int_check',3);
2545: p_del_flag := 'N';
2546: --
2547: BEGIN
2548: select 'Y'
2560: END;
2561: --
2562: IF p_del_flag = 'Y' THEN
2563: IF p_mode = 'ZAP' THEN
2564: hr_utility.set_message(801,'HR_7638_ASS_COST_DEL_ASS');
2565: ELSE
2566: hr_utility.set_message(801,'HR_7641_ASS_COST_END_ASS');
2567: END IF;
2568: hr_utility.raise_error;
2562: IF p_del_flag = 'Y' THEN
2563: IF p_mode = 'ZAP' THEN
2564: hr_utility.set_message(801,'HR_7638_ASS_COST_DEL_ASS');
2565: ELSE
2566: hr_utility.set_message(801,'HR_7641_ASS_COST_END_ASS');
2567: END IF;
2568: hr_utility.raise_error;
2569: END IF;
2570: --
2564: hr_utility.set_message(801,'HR_7638_ASS_COST_DEL_ASS');
2565: ELSE
2566: hr_utility.set_message(801,'HR_7641_ASS_COST_END_ASS');
2567: END IF;
2568: hr_utility.raise_error;
2569: END IF;
2570: --
2571: --
2572: -- 03/18/1998 Bug #642566
2574: -- as per_assignment_extra_info are now deleted along with other
2575: -- assignment related records.
2576: --
2577: --
2578: hr_utility.set_location('hr_assignment.del_ref_int_check',5);
2579: p_del_flag := 'N';
2580: /*
2581: N.B. PER_SECONDARY_ASS_STATUSES rows will now be deleted if they
2582: started after the Assignment End Date - changed 27/5/93.
2595: END;
2596: --
2597: IF p_del_flag = 'Y' THEN
2598: IF p_mode = 'ZAP' THEN
2599: hr_utility.set_message(801,'HR_7652_ASS_STAT_DEL_ASS');
2600: ELSE
2601: hr_utility.set_message(801,'HR_7655_ASS_SATA_END_ASS');
2602: END IF;
2603: hr_utility.raise_error;
2597: IF p_del_flag = 'Y' THEN
2598: IF p_mode = 'ZAP' THEN
2599: hr_utility.set_message(801,'HR_7652_ASS_STAT_DEL_ASS');
2600: ELSE
2601: hr_utility.set_message(801,'HR_7655_ASS_SATA_END_ASS');
2602: END IF;
2603: hr_utility.raise_error;
2604: END IF;
2605: */
2599: hr_utility.set_message(801,'HR_7652_ASS_STAT_DEL_ASS');
2600: ELSE
2601: hr_utility.set_message(801,'HR_7655_ASS_SATA_END_ASS');
2602: END IF;
2603: hr_utility.raise_error;
2604: END IF;
2605: */
2606: --
2607: --
2604: END IF;
2605: */
2606: --
2607: --
2608: hr_utility.set_location('hr_assignment.del_ref_int_check',6);
2609: p_del_flag := 'N';
2610:
2611: /* 2537091: PPMs will be deleted if they started after end date of assignment
2612: changed 04-OCT-2002
2627: END;
2628: --
2629: IF p_del_flag = 'Y' THEN
2630: IF p_mode = 'ZAP' THEN
2631: hr_utility.set_message(801,'HR_7656_ASS_PAY_DEL_ASS');
2632: ELSE
2633: hr_utility.set_message(801,'HR_7659_ASS_PAY_END_ASS');
2634: END IF;
2635: hr_utility.raise_error;
2629: IF p_del_flag = 'Y' THEN
2630: IF p_mode = 'ZAP' THEN
2631: hr_utility.set_message(801,'HR_7656_ASS_PAY_DEL_ASS');
2632: ELSE
2633: hr_utility.set_message(801,'HR_7659_ASS_PAY_END_ASS');
2634: END IF;
2635: hr_utility.raise_error;
2636: END IF;
2637:
2631: hr_utility.set_message(801,'HR_7656_ASS_PAY_DEL_ASS');
2632: ELSE
2633: hr_utility.set_message(801,'HR_7659_ASS_PAY_END_ASS');
2634: END IF;
2635: hr_utility.raise_error;
2636: END IF;
2637:
2638: */
2639: --
2637:
2638: */
2639: --
2640: --
2641: hr_utility.set_location('hr_assignment.del_ref_int_check',7);
2642: p_del_flag := 'N';
2643: --
2644: /* BEGIN
2645: select 'Y'
2694: END IF;
2695: --
2696: IF p_del_flag = 'Y' THEN
2697: IF p_mode = 'ZAP' THEN
2698: hr_utility.set_message(801,'HR_7664_ASS_ASS_DEL_ASS');
2699: ELSE
2700: hr_utility.set_message(801,'HR_7667_ASS_ASS_END_ASS');
2701: END IF;
2702: hr_utility.raise_error;
2696: IF p_del_flag = 'Y' THEN
2697: IF p_mode = 'ZAP' THEN
2698: hr_utility.set_message(801,'HR_7664_ASS_ASS_DEL_ASS');
2699: ELSE
2700: hr_utility.set_message(801,'HR_7667_ASS_ASS_END_ASS');
2701: END IF;
2702: hr_utility.raise_error;
2703: END IF;
2704: --
2698: hr_utility.set_message(801,'HR_7664_ASS_ASS_DEL_ASS');
2699: ELSE
2700: hr_utility.set_message(801,'HR_7667_ASS_ASS_END_ASS');
2701: END IF;
2702: hr_utility.raise_error;
2703: END IF;
2704: --
2705: --
2706: IF p_mode = 'ZAP' THEN
2704: --
2705: --
2706: IF p_mode = 'ZAP' THEN
2707: --
2708: hr_utility.set_location('hr_assignment.del_ref_int_check',8);
2709: p_del_flag := 'N';
2710: --
2711: BEGIN
2712: select 'Y'
2727: WHEN NO_DATA_FOUND THEN NULL;
2728: END;
2729: --
2730: IF p_del_flag = 'Y' THEN
2731: hr_utility.set_message(801,'HR_6305_ALL_ASSGT_SET_NO_DEL');
2732: hr_utility.raise_error;
2733: END IF;
2734: END IF;
2735: ------------------------------
2728: END;
2729: --
2730: IF p_del_flag = 'Y' THEN
2731: hr_utility.set_message(801,'HR_6305_ALL_ASSGT_SET_NO_DEL');
2732: hr_utility.raise_error;
2733: END IF;
2734: END IF;
2735: ------------------------------
2736: -- Cobra Coverage Enrollments
2734: END IF;
2735: ------------------------------
2736: -- Cobra Coverage Enrollments
2737: --
2738: hr_utility.set_location('hr_assignment.del_ref_int_check',9);
2739: p_del_flag := 'N';
2740: --
2741: BEGIN
2742: select 'Y'
2754: END;
2755: --
2756: IF p_del_flag = 'Y' THEN
2757: IF p_mode = 'ZAP' THEN
2758: hr_utility.set_message(801,'HR_7672_ASS_COBRA_DEL_ASS');
2759: ELSE
2760: hr_utility.set_message(801,'HR_7675_ASS_COBRA_END_ASS');
2761: END IF;
2762: hr_utility.raise_error;
2756: IF p_del_flag = 'Y' THEN
2757: IF p_mode = 'ZAP' THEN
2758: hr_utility.set_message(801,'HR_7672_ASS_COBRA_DEL_ASS');
2759: ELSE
2760: hr_utility.set_message(801,'HR_7675_ASS_COBRA_END_ASS');
2761: END IF;
2762: hr_utility.raise_error;
2763: END IF;
2764: ------------------------------
2758: hr_utility.set_message(801,'HR_7672_ASS_COBRA_DEL_ASS');
2759: ELSE
2760: hr_utility.set_message(801,'HR_7675_ASS_COBRA_END_ASS');
2761: END IF;
2762: hr_utility.raise_error;
2763: END IF;
2764: ------------------------------
2765: -- Cobra Coverage Benefits
2766: --
2763: END IF;
2764: ------------------------------
2765: -- Cobra Coverage Benefits
2766: --
2767: hr_utility.set_location('hr_assignment.del_ref_int_check',9);
2768: p_del_flag := 'N';
2769: --
2770: BEGIN
2771: select 'Y'
2787: END;
2788: --
2789: IF p_del_flag = 'Y' THEN
2790: IF p_mode = 'ZAP' THEN
2791: hr_utility.set_message(801,'HR_7668_ASS_COBR_DEL_ASS');
2792: ELSE
2793: hr_utility.set_message(801,'HR_7671_ASS_COBRA_END_ASS');
2794: END IF;
2795: hr_utility.raise_error;
2789: IF p_del_flag = 'Y' THEN
2790: IF p_mode = 'ZAP' THEN
2791: hr_utility.set_message(801,'HR_7668_ASS_COBR_DEL_ASS');
2792: ELSE
2793: hr_utility.set_message(801,'HR_7671_ASS_COBRA_END_ASS');
2794: END IF;
2795: hr_utility.raise_error;
2796: END IF;
2797: --
2791: hr_utility.set_message(801,'HR_7668_ASS_COBR_DEL_ASS');
2792: ELSE
2793: hr_utility.set_message(801,'HR_7671_ASS_COBRA_END_ASS');
2794: END IF;
2795: hr_utility.raise_error;
2796: END IF;
2797: --
2798: ------------------------------
2799: -- OTA_DELEGATE_BOOKINGS
2797: --
2798: ------------------------------
2799: -- OTA_DELEGATE_BOOKINGS
2800: --
2801: hr_utility.set_location('hr_assignment.del_ref_int_check',10);
2802: --
2803: p_del_flag := 'N';
2804: --
2805: BEGIN
2830: END;
2831: --
2832: IF p_del_flag = 'Y' THEN
2833: IF p_mode = 'ZAP' THEN
2834: hr_utility.set_message(801,'HR_52281_ASS_TAX_DEL_ASS');
2835: ELSE
2836: hr_utility.set_message(801,'HR_52280_ASS_TAX_END_ASS');
2837: END IF;
2838: hr_utility.raise_error;
2832: IF p_del_flag = 'Y' THEN
2833: IF p_mode = 'ZAP' THEN
2834: hr_utility.set_message(801,'HR_52281_ASS_TAX_DEL_ASS');
2835: ELSE
2836: hr_utility.set_message(801,'HR_52280_ASS_TAX_END_ASS');
2837: END IF;
2838: hr_utility.raise_error;
2839: END IF;
2840:
2834: hr_utility.set_message(801,'HR_52281_ASS_TAX_DEL_ASS');
2835: ELSE
2836: hr_utility.set_message(801,'HR_52280_ASS_TAX_END_ASS');
2837: END IF;
2838: hr_utility.raise_error;
2839: END IF;
2840:
2841: ------------------------------
2842: IF p_mode = 'ZAP' THEN
2842: IF p_mode = 'ZAP' THEN
2843: --
2844: -- OTA_DELEGATE_BOOKINGS
2845:
2846: hr_utility.set_location('hr_assignment.del_ref_int_check',11);
2847: per_ota_predel_validation.ota_predel_asg_validation(P_ASSIGNMENT_ID);
2848: END IF;
2849: ---
2850: END del_ref_int_check;
2977: --
2978: --
2979: p_end_date := p_edate;
2980: --
2981: hr_utility.set_location('hr_assignment.del_ref_int_delete',1);
2982: p_del_flag := 'N';
2983: --
2984: BEGIN
2985: --
3005: --
3006: EXCEPTION
3007: WHEN NO_DATA_FOUND THEN NULL;
3008: END;
3009: hr_utility.set_location('p_del_flag :'||p_del_flag,2);
3010: hr_utility.set_location('p_mode :'||p_mode,2);
3011: hr_utility.set_location('p_edate :'||p_edate,2);
3012: hr_utility.set_location('p_val_st_date: '||p_val_st_date,2);
3013: hr_utility.set_location('p_val_end_date: '||p_val_end_date,2);
3006: EXCEPTION
3007: WHEN NO_DATA_FOUND THEN NULL;
3008: END;
3009: hr_utility.set_location('p_del_flag :'||p_del_flag,2);
3010: hr_utility.set_location('p_mode :'||p_mode,2);
3011: hr_utility.set_location('p_edate :'||p_edate,2);
3012: hr_utility.set_location('p_val_st_date: '||p_val_st_date,2);
3013: hr_utility.set_location('p_val_end_date: '||p_val_end_date,2);
3014:
3007: WHEN NO_DATA_FOUND THEN NULL;
3008: END;
3009: hr_utility.set_location('p_del_flag :'||p_del_flag,2);
3010: hr_utility.set_location('p_mode :'||p_mode,2);
3011: hr_utility.set_location('p_edate :'||p_edate,2);
3012: hr_utility.set_location('p_val_st_date: '||p_val_st_date,2);
3013: hr_utility.set_location('p_val_end_date: '||p_val_end_date,2);
3014:
3015: --
3008: END;
3009: hr_utility.set_location('p_del_flag :'||p_del_flag,2);
3010: hr_utility.set_location('p_mode :'||p_mode,2);
3011: hr_utility.set_location('p_edate :'||p_edate,2);
3012: hr_utility.set_location('p_val_st_date: '||p_val_st_date,2);
3013: hr_utility.set_location('p_val_end_date: '||p_val_end_date,2);
3014:
3015: --
3016: IF p_del_flag = 'Y' THEN
3009: hr_utility.set_location('p_del_flag :'||p_del_flag,2);
3010: hr_utility.set_location('p_mode :'||p_mode,2);
3011: hr_utility.set_location('p_edate :'||p_edate,2);
3012: hr_utility.set_location('p_val_st_date: '||p_val_st_date,2);
3013: hr_utility.set_location('p_val_end_date: '||p_val_end_date,2);
3014:
3015: --
3016: IF p_del_flag = 'Y' THEN
3017: --
3037:
3038: else
3039:
3040: IF p_mode = 'ZAP' THEN
3041: hr_utility.set_location('hr_assignment.delete_ass_ref_int',2);
3042:
3043: l_datetrack_mode := 'ZAP';
3044:
3045: --
3079: --
3080: open csr_grade_step;
3081: fetch csr_grade_step into l_update;
3082: if csr_grade_step%found then
3083: hr_utility.set_location('Grade Step Found',4);
3084: hr_utility.set_location('p_calling_proc :'||p_calling_proc,4);
3085: hr_utility.set_location('p_val_st_date: '||p_val_st_date,4);
3086:
3087: --
3080: open csr_grade_step;
3081: fetch csr_grade_step into l_update;
3082: if csr_grade_step%found then
3083: hr_utility.set_location('Grade Step Found',4);
3084: hr_utility.set_location('p_calling_proc :'||p_calling_proc,4);
3085: hr_utility.set_location('p_val_st_date: '||p_val_st_date,4);
3086:
3087: --
3088: -- Calling proces = 'POST_UPDATE' then a min step has to be created for the new grade scale
3081: fetch csr_grade_step into l_update;
3082: if csr_grade_step%found then
3083: hr_utility.set_location('Grade Step Found',4);
3084: hr_utility.set_location('p_calling_proc :'||p_calling_proc,4);
3085: hr_utility.set_location('p_val_st_date: '||p_val_st_date,4);
3086:
3087: --
3088: -- Calling proces = 'POST_UPDATE' then a min step has to be created for the new grade scale
3089: --
3100: where spp.assignment_id = p_assignment_id
3101: and p_val_st_date between spp.effective_start_date
3102: and spp.effective_end_date;
3103:
3104: hr_utility.set_location('l_placement_id :'||l_placement_id,5);
3105: hr_utility.set_location('l_object_version_number :'||l_object_version_number,5);
3106: hr_utility.set_location('l_date_temp :'||l_date_temp,5);
3107: hr_utility.set_location('l_old_parent_spine_id :'||l_old_parent_spine_id,5);
3108:
3101: and p_val_st_date between spp.effective_start_date
3102: and spp.effective_end_date;
3103:
3104: hr_utility.set_location('l_placement_id :'||l_placement_id,5);
3105: hr_utility.set_location('l_object_version_number :'||l_object_version_number,5);
3106: hr_utility.set_location('l_date_temp :'||l_date_temp,5);
3107: hr_utility.set_location('l_old_parent_spine_id :'||l_old_parent_spine_id,5);
3108:
3109: --
3102: and spp.effective_end_date;
3103:
3104: hr_utility.set_location('l_placement_id :'||l_placement_id,5);
3105: hr_utility.set_location('l_object_version_number :'||l_object_version_number,5);
3106: hr_utility.set_location('l_date_temp :'||l_date_temp,5);
3107: hr_utility.set_location('l_old_parent_spine_id :'||l_old_parent_spine_id,5);
3108:
3109: --
3110: -- get the parent spine_id for the new grade
3103:
3104: hr_utility.set_location('l_placement_id :'||l_placement_id,5);
3105: hr_utility.set_location('l_object_version_number :'||l_object_version_number,5);
3106: hr_utility.set_location('l_date_temp :'||l_date_temp,5);
3107: hr_utility.set_location('l_old_parent_spine_id :'||l_old_parent_spine_id,5);
3108:
3109: --
3110: -- get the parent spine_id for the new grade
3111: --
3115: where pgs.grade_id = p_grade_id
3116: and P_edate between pgs.effective_start_date
3117: and pgs.effective_end_date;
3118:
3119: hr_utility.set_location('l_parent_spine_id :'||l_parent_spine_id,6);
3120:
3121: --
3122: -- Get the min seuence for the new grade
3123: --
3129: and psp.spinal_point_id = sps.spinal_point_id
3130: and P_edate between sps.effective_start_date
3131: and sps.effective_end_date;
3132:
3133: hr_utility.set_location('l_sequence :'||l_sequence,7);
3134: --
3135: -- Get the step id for the min sequence
3136: --
3137: select sps.step_id
3142: and psp.parent_spine_id = l_parent_spine_id
3143: and psp.sequence = l_sequence;
3144:
3145:
3146: hr_utility.set_location('l_min_step_id :'||l_min_step_id,8);
3147:
3148: open csr_future_records;
3149: fetch csr_future_records into l_temp;
3150:
3149: fetch csr_future_records into l_temp;
3150:
3151: if csr_future_records%found then
3152:
3153: hr_utility.set_location('Future record found.',9);
3154:
3155: --
3156: -- check if there is a step placement record starting on the same day
3157: --
3164: end if;
3165:
3166: else
3167:
3168: hr_utility.set_location('Future record not found,',10);
3169:
3170: if l_date_temp = p_edate then
3171: l_datetrack_mode := 'CORRECTION';
3172: p_future_spp_warning := FALSE;
3195: l_new_date := p_edate;
3196:
3197: end if;
3198:
3199: hr_utility.set_location('Deleteing the next change.',15);
3200:
3201: /*if p_datetrack_mode <> 'UPDATE_OVERRIDE' then
3202: l_new_date := p_val_st_date;
3203: else
3205: end if;
3206: */
3207: l_new_date := p_edate;
3208:
3209: hr_utility.set_location('l_new_date: '||l_new_date,15);
3210:
3211: --
3212: -- Delete next change until the effective end date of the record
3213: -- that was just inserted matches the validation end date
3220: if csr_spp_rec%found then
3221: --
3222: l_datetrack_mode := 'DELETE_NEXT_CHANGE';
3223: --
3224: hr_utility.set_location('l_effective_end_date :'||l_effective_end_date, 25);
3225: hr_utility.set_location('p_val_end_date :'||p_val_end_date, 25);
3226: --
3227: loop
3228: --
3221: --
3222: l_datetrack_mode := 'DELETE_NEXT_CHANGE';
3223: --
3224: hr_utility.set_location('l_effective_end_date :'||l_effective_end_date, 25);
3225: hr_utility.set_location('p_val_end_date :'||p_val_end_date, 25);
3226: --
3227: loop
3228: --
3229: if l_effective_end_date = p_val_end_date then
3252:
3253: end if;
3254: close csr_grade_step;
3255:
3256: hr_utility.set_location('hr_assignment.delete_ass_ref_int',3);
3257: --
3258: -- Removed dml and inserted call to api
3259: --
3260: /*
3273: ELSE
3274: -- If mode is 'END' then do Date Effective Delete
3275: -- from p_end_date.
3276: --
3277: hr_utility.set_location('hr_assignment.delete_ass_ref_int',4);
3278: --
3279: -- Removed dml and using api
3280: --
3281: /*
3314: end if;
3315: close csr_grade_step;
3316:
3317:
3318: /*hr_utility.set_location('hr_assignment.delete_ass_ref_int',5);
3319: UPDATE PER_SPINAL_POINT_PLACEMENTS_F
3320: SET EFFECTIVE_END_DATE = p_end_date
3321: , LAST_UPDATED_BY = p_last_updated_by
3322: , LAST_UPDATE_LOGIN = p_last_update_login
3359: --
3360: IF p_del_flag = 'Y' THEN
3361: --
3362: IF p_mode = 'ZAP' THEN
3363: hr_utility.set_location('hr_assignment.del_ref_int_delet',30);
3364: DELETE FROM PER_ASSIGNMENT_BUDGET_VALUES_F ABV
3365: WHERE ABV.ASSIGNMENT_ID = p_assignment_id;
3366:
3367: --
3369: IF p_mode = 'END' THEN
3370: -- If mode is 'END' then do Date Effective Delete
3371: -- from p_end_date.
3372: --
3373: hr_utility.set_location('hr_assignment.del_ref_int_delete',40);
3374:
3375: DELETE FROM PER_ASSIGNMENT_BUDGET_VALUES_F ABV
3376: WHERE ABV.ASSIGNMENT_ID = p_assignment_id
3377: AND ABV.EFFECTIVE_START_DATE > p_end_date;
3375: DELETE FROM PER_ASSIGNMENT_BUDGET_VALUES_F ABV
3376: WHERE ABV.ASSIGNMENT_ID = p_assignment_id
3377: AND ABV.EFFECTIVE_START_DATE > p_end_date;
3378: --
3379: hr_utility.set_location('hr_assignment.del_ref_int_delete',45);
3380: UPDATE PER_ASSIGNMENT_BUDGET_VALUES_F
3381: SET EFFECTIVE_END_DATE = p_end_date
3382: , LAST_UPDATED_BY = p_last_updated_by
3383: , LAST_UPDATE_LOGIN = p_last_update_login
3392: -- If mode is 'FUTURE' then do Date Effective Delete for all future records
3393: -- from p_end_date. Further, open the current end dated record. Set the last date
3394: -- of assignment_budget_value record same as the last date of current assignmet.
3395: --
3396: hr_utility.set_location('hr_assignment.del_ref_int_delete',46);
3397: hr_utility.set_location('p_end_date '||p_end_date, 47);
3398:
3399: DELETE FROM PER_ASSIGNMENT_BUDGET_VALUES_F ABV
3400: WHERE ABV.ASSIGNMENT_ID = p_assignment_id
3393: -- from p_end_date. Further, open the current end dated record. Set the last date
3394: -- of assignment_budget_value record same as the last date of current assignmet.
3395: --
3396: hr_utility.set_location('hr_assignment.del_ref_int_delete',46);
3397: hr_utility.set_location('p_end_date '||p_end_date, 47);
3398:
3399: DELETE FROM PER_ASSIGNMENT_BUDGET_VALUES_F ABV
3400: WHERE ABV.ASSIGNMENT_ID = p_assignment_id
3401: AND ABV.EFFECTIVE_START_DATE > p_end_date
3400: WHERE ABV.ASSIGNMENT_ID = p_assignment_id
3401: AND ABV.EFFECTIVE_START_DATE > p_end_date
3402: AND ABV.EFFECTIVE_END_DATE <> hr_api.g_eot;--Added as a part of fix for bug#11078262
3403: --
3404: hr_utility.set_location(' No of rows deleted '||sql%rowcount,48);
3405: hr_utility.set_location('hr_assignment.del_ref_int_delete',49);
3406:
3407: select effective_end_date into l_ass_end_date
3408: from per_all_assignments_f
3401: AND ABV.EFFECTIVE_START_DATE > p_end_date
3402: AND ABV.EFFECTIVE_END_DATE <> hr_api.g_eot;--Added as a part of fix for bug#11078262
3403: --
3404: hr_utility.set_location(' No of rows deleted '||sql%rowcount,48);
3405: hr_utility.set_location('hr_assignment.del_ref_int_delete',49);
3406:
3407: select effective_end_date into l_ass_end_date
3408: from per_all_assignments_f
3409: where assignment_id = p_assignment_id
3408: from per_all_assignments_f
3409: where assignment_id = p_assignment_id
3410: and p_end_date between EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE;
3411:
3412: hr_utility.set_location('l_ass_end_date '||l_ass_end_date,50);
3413:
3414:
3415: UPDATE PER_ASSIGNMENT_BUDGET_VALUES_F
3416: SET EFFECTIVE_END_DATE = l_ass_end_date
3422: BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE
3423: and EFFECTIVE_END_DATE <> hr_api.g_eot; -- Added the condition as a part of bug#9669561.
3424:
3425: -- Bug 13809858
3426: -- hr_utility.set_location('No of rows updated '||sql%rowcount,51);
3427:
3428: END IF;
3429: -- Addition for Bug 7112709 ends
3430:
3435: --
3436: --
3437:
3438: IF p_mode = 'ZAP' THEN
3439: hr_utility.set_location('hr_assignment.del_ref_int_delete',5);
3440: p_del_flag := 'N';
3441: --
3442: BEGIN
3443: --
3456: END;
3457: --
3458: IF p_del_flag = 'Y' THEN
3459: --
3460: hr_utility.set_location('hr_assignment.delete_ass_ref_int',6);
3461: DELETE FROM HR_ASSIGNMENT_SET_AMENDMENTS
3462: WHERE ASSIGNMENT_ID = p_assignment_id;
3463: END IF;
3464: --
3463: END IF;
3464: --
3465: --
3466: --
3467: hr_utility.set_location('hr_assignment.del_ref_int_delete',9);
3468: p_del_flag := 'N';
3469: --
3470: BEGIN
3471: --
3482: END;
3483: --
3484: IF p_del_flag = 'Y' THEN
3485: --
3486: hr_utility.set_location('hr_assignment.delete_ass_ref_int',10);
3487: DELETE FROM PER_SECONDARY_ASS_STATUSES
3488: WHERE ASSIGNMENT_ID = p_assignment_id;
3489: END IF;
3490: --
3488: WHERE ASSIGNMENT_ID = p_assignment_id;
3489: END IF;
3490: --
3491: --
3492: hr_utility.set_location('hr_assignment.del_ref_int_delete',11);
3493: p_del_flag := 'N';
3494: --
3495: BEGIN
3496: --
3507: END;
3508: --
3509: IF p_del_flag = 'Y' THEN
3510: --
3511: hr_utility.set_location('hr_assignment.delete_ass_ref_int',12);
3512: DELETE FROM PER_PAY_PROPOSALS
3513: WHERE ASSIGNMENT_ID = p_assignment_id;
3514: END IF;
3515: --
3514: END IF;
3515: --
3516: /* This is being changed for Bug# 785427 */
3517:
3518: hr_utility.set_location('hr_assignment.del_ref_int_delete',11);
3519: p_del_flag := 'N';
3520:
3521: BEGIN
3522: select 'Y'
3532: END;
3533: --
3534: IF p_del_flag = 'Y' THEN
3535: --
3536: hr_utility.set_location('hr_assignment.delete_ass_ref_int',12);
3537: /* If the federal tax record exists then the state, county
3538: and city tax records also exist (due to the defaulting of
3539: tax records). So, delete from all 4 table. In addition, delete
3540: from the table pay_us_asg_reporting as well */
3558: --
3559:
3560: -- 03/18/1998 Bug #642566
3561: -- Remove per_assignment_extra_info records
3562: hr_utility.set_location('hr_assignment.del_ref_int_delete',14);
3563: p_del_flag := 'N';
3564: --
3565: BEGIN
3566: --
3577: END;
3578: --
3579: IF p_del_flag = 'Y' THEN
3580: --
3581: hr_utility.set_location('hr_assignment.delete_ass_ref_int',16);
3582: DELETE FROM PER_ASSIGNMENT_EXTRA_INFO
3583: WHERE ASSIGNMENT_ID = p_assignment_id;
3584: END IF;
3585: -- 03/18/1998 Change Ends
3717: AND p_mode='END';
3718: --
3719: BEGIN
3720: --
3721: hr_utility.set_location('hr_assignment.tidy_up_ref_int',1);
3722: --
3723: p_cost_warning := FALSE;
3724: BEGIN
3725: select 'Y'
3742: END;
3743: --
3744: IF p_del_flag = 'Y' THEN
3745: --
3746: hr_utility.set_location('hr_assignment.tidy_up_ref_int',2);
3747: --
3748: update per_secondary_ass_statuses
3749: set END_DATE = decode(p_new_end_date,to_date('31/12/4712','DD/MM/YYYY'),
3750: null,p_new_end_date)
3763: END IF;
3764: --
3765: p_del_flag := 'N';
3766: --
3767: hr_utility.set_location('hr_assignment.tidy_up_ref_int',3);
3768: --
3769: BEGIN
3770: select 'Y'
3771: into p_del_flag
3781: END;
3782: --
3783: IF p_del_flag = 'Y' THEN
3784: --
3785: hr_utility.set_location('hr_assignment.tidy_up_ref_int',4);
3786: --
3787: delete from per_secondary_ass_statuses
3788: where assignment_id = p_assignment_id
3789: and p_mode = 'END'
3791: END IF;
3792: --
3793: p_del_flag := 'N';
3794: --
3795: hr_utility.set_location('hr_assignment.tidy_up_ref_int',5);
3796: --
3797: BEGIN
3798: select 'Y'
3799: into p_del_flag
3813: END;
3814: --
3815: IF p_del_flag = 'Y' THEN
3816: --
3817: hr_utility.set_location('hr_assignment.tidy_up_ref_int',6);
3818:
3819: if p_mode = 'END' then
3820: hr_utility.set_location('hr_assignment.tidy_up_ref_int',7);
3821: update pay_cost_allocations_f
3816: --
3817: hr_utility.set_location('hr_assignment.tidy_up_ref_int',6);
3818:
3819: if p_mode = 'END' then
3820: hr_utility.set_location('hr_assignment.tidy_up_ref_int',7);
3821: update pay_cost_allocations_f
3822: set effective_end_date = p_new_end_date
3823: , last_updated_by = P_LAST_UPDATED_BY
3824: , last_update_login = P_LAST_UPDATE_LOGIN
3827: and ((p_mode = 'END'
3828: and p_new_end_date
3829: between effective_start_date and effective_end_date));
3830: elsif p_mode='FUTURE' then
3831: hr_utility.set_location('hr_assignment.tidy_up_ref_int',8);
3832:
3833: /*
3834: ** When dealing with delete FUTURE_CHANGE, only open out
3835: ** the costing record if no future costing record exists.
3842: where assignment_id = p_assignment_id
3843: and effective_start_date > p_old_end_date;
3844:
3845: if l_exists = 0 then
3846: hr_utility.set_location('hr_assignment.tidy_up_ref_int',9);
3847: update pay_cost_allocations_f
3848: set effective_end_date = p_new_end_date
3849: , last_updated_by = P_LAST_UPDATED_BY
3850: , last_update_login = P_LAST_UPDATE_LOGIN
3852: where assignment_id = p_assignment_id
3853: and (p_mode = 'FUTURE'
3854: and p_old_end_date = effective_end_date);
3855: else
3856: hr_utility.set_location('hr_assignment.tidy_up_ref_int',10);
3857: p_cost_warning := TRUE;
3858: end if;
3859:
3860: end if;
3871: -- What then needs to happen is to change the end date of the existing row to make sure the current child
3872: -- row stays in line with the parent.
3873: -- SASmith : 16-APR-1998
3874:
3875: hr_utility.set_location(p_new_end_date,11);
3876: hr_utility.set_location(p_mode,12);
3877: hr_utility.set_location(p_old_end_date,13);
3878:
3879: hr_utility.set_location('hr_assignment.tidy_up_ref_int',15);
3872: -- row stays in line with the parent.
3873: -- SASmith : 16-APR-1998
3874:
3875: hr_utility.set_location(p_new_end_date,11);
3876: hr_utility.set_location(p_mode,12);
3877: hr_utility.set_location(p_old_end_date,13);
3878:
3879: hr_utility.set_location('hr_assignment.tidy_up_ref_int',15);
3880:
3873: -- SASmith : 16-APR-1998
3874:
3875: hr_utility.set_location(p_new_end_date,11);
3876: hr_utility.set_location(p_mode,12);
3877: hr_utility.set_location(p_old_end_date,13);
3878:
3879: hr_utility.set_location('hr_assignment.tidy_up_ref_int',15);
3880:
3881: p_del_flag := 'N';
3875: hr_utility.set_location(p_new_end_date,11);
3876: hr_utility.set_location(p_mode,12);
3877: hr_utility.set_location(p_old_end_date,13);
3878:
3879: hr_utility.set_location('hr_assignment.tidy_up_ref_int',15);
3880:
3881: p_del_flag := 'N';
3882:
3883: --
3897: END;
3898: --
3899: IF p_del_flag = 'Y' THEN
3900: --
3901: hr_utility.set_location('hr_assignment.tidy_up_ref_int',20);
3902: --
3903: delete from per_assignment_budget_values_f abv
3904: where abv.assignment_id = p_assignment_id
3905: and p_mode = 'END'
3907: END IF;
3908:
3909: p_del_flag := 'N';
3910: --
3911: hr_utility.set_location('hr_assignment.tidy_up_ref_int',25);
3912:
3913:
3914: BEGIN
3915: select 'Y'
3927: END;
3928:
3929: IF p_del_flag = 'Y' THEN
3930: --
3931: hr_utility.set_location('hr_assignment.tidy_up_ref_int',30);
3932: --
3933: update per_assignment_budget_values_f abv
3934: set abv.effective_end_date = p_new_end_date
3935: , abv.last_updated_by = P_LAST_UPDATED_BY
3941:
3942: END IF;
3943:
3944: --
3945: hr_utility.set_location('hr_assignment.tidy_up_ref_int',35);
3946: --
3947: -- # 2437795
3948: -- Reversing TAX records
3949: --
3948: -- Reversing TAX records
3949: --
3950: IF p_mode = 'FUTURE' THEN
3951: --
3952: hr_utility.set_location('hr_assignment.tidy_up_ref_int',42);
3953: hr_utility.trace(' **** old end date = '||to_char(p_old_end_date,'dd-mon-yyyy'));
3954: declare
3955: cursor csr_asg is
3956: select max(effective_end_date)
3949: --
3950: IF p_mode = 'FUTURE' THEN
3951: --
3952: hr_utility.set_location('hr_assignment.tidy_up_ref_int',42);
3953: hr_utility.trace(' **** old end date = '||to_char(p_old_end_date,'dd-mon-yyyy'));
3954: declare
3955: cursor csr_asg is
3956: select max(effective_end_date)
3957: from pay_us_emp_fed_tax_rules_f
3960: begin
3961: open csr_asg;
3962: fetch csr_asg into l_end_date;
3963: close csr_asg;
3964: hr_utility.trace(' **** l_end_date = '||to_char(l_end_date,'dd-mon-yyyy'));
3965: if l_end_date is not null then
3966: pay_us_update_tax_rec_pkg.reverse_term_emp_tax_records(p_assignment_id, l_end_date);
3967: end if;
3968: end;
3966: pay_us_update_tax_rec_pkg.reverse_term_emp_tax_records(p_assignment_id, l_end_date);
3967: end if;
3968: end;
3969: --
3970: hr_utility.set_location('hr_assignment.tidy_up_ref_int',45);
3971: END IF;
3972: -- end #2437795
3973: --
3974: --adhunter added for bug 2537091 04-OCT-02
3973: --
3974: --adhunter added for bug 2537091 04-OCT-02
3975: --need to handle pay_personal_payment_methods in the same way as the others above.
3976: --
3977: hr_utility.set_location('hr_assignment.tidy_up_ref_int',50);
3978: DECLARE
3979: l_effective_start_date date;
3980: l_effective_end_date date;
3981: --
3998: and p_mode='END';
3999: --
4000: BEGIN
4001: for l_curr_rec in csr_curr_ppm loop
4002: hr_utility.set_location('ppm_id '||l_curr_rec.personal_payment_method_id,55);
4003: --
4004: --end date current DT row and delete future rows for this PPM
4005: --this means that future-only PPMs are left.
4006: --
4013: ,p_effective_end_date => l_effective_end_date
4014: );
4015: end loop;
4016: for l_fut_rec in csr_fut_ppm loop
4017: hr_utility.set_location('ppm_id '||l_fut_rec.personal_payment_method_id,60);
4018: --
4019: --delete future-only PPMs, last loop removed future DT rows of current PPMs
4020: --
4021: hr_personal_pay_method_api.delete_personal_pay_method
4028: );
4029: end loop;
4030: END;
4031: --
4032: hr_utility.set_location(l_proc,70);
4033: --
4034: -- End Date any current Assignment Rates
4035: --
4036: FOR crec_current_asg_rates IN csr_current_asg_rates LOOP
4034: -- End Date any current Assignment Rates
4035: --
4036: FOR crec_current_asg_rates IN csr_current_asg_rates LOOP
4037: --
4038: hr_utility.set_location(l_proc||'/'||crec_current_asg_rates.grade_rule_id,80);
4039: --
4040: hr_rate_values_api.delete_rate_value
4041: (p_validate => FALSE
4042: ,p_grade_rule_id => crec_current_asg_rates.grade_rule_id
4047: ,p_effective_end_date => l_effective_end_date);
4048: --
4049: END LOOP;
4050: --
4051: hr_utility.set_location(l_proc,90);
4052: --
4053: -- Delete any future dated assignment rates if we are Ending the Assignment.
4054: --
4055: FOR crec_future_asg_rates IN csr_future_asg_rates LOOP
4053: -- Delete any future dated assignment rates if we are Ending the Assignment.
4054: --
4055: FOR crec_future_asg_rates IN csr_future_asg_rates LOOP
4056: --
4057: hr_utility.set_location(l_proc||'/'||crec_future_asg_rates.grade_rule_id,100);
4058: --
4059: hr_rate_values_api.delete_rate_value
4060: (p_validate => FALSE
4061: ,p_grade_rule_id => crec_future_asg_rates.grade_rule_id
4066: ,p_effective_end_date => l_effective_end_date);
4067: --
4068: END LOOP;
4069: --
4070: hr_utility.set_location('Leaving : '||l_proc,999);
4071: --
4072: EXCEPTION
4073: when others then
4074: p_cost_warning := null ;
4139: BEGIN
4140: -- This function returns TRUE if a TERM_ASSIGN status exists earlier than the
4141: -- date we are considering.
4142: --
4143: hr_utility.set_location('peassign.call_terminate_entries',1);
4144: --
4145: BEGIN
4146: select 'Y'
4147: into term_exists
4163: END previous_term_exists;
4164: -------------------------
4165: BEGIN
4166: --
4167: hr_utility.set_location('peassign.call_terminate_entries',2);
4168: IF P_STATUS = 'END' THEN
4169: --
4170: IF previous_term_exists(p_assignment_id
4171: ,p_start_date) THEN
4177: p_last_standard_date := p_start_date;
4178: p_final_process_date := p_start_date;
4179: END IF; -- IF previous_term_exists(....
4180: --
4181: hr_utility.set_location('peassign.call_terminate_entries',3);
4182: -- VT 10/08/96 bug #306710 added parameter
4183: hrempter.terminate_entries_and_alus(p_assignment_id
4184: ,p_actual_term_date
4185: ,p_last_standard_date
4188: ,l_entries_changed);
4189: --
4190: ELSE -- (IF p_status = 'TERM_ASSIGN')
4191: --
4192: hr_utility.set_location('peassign.call_terminate_entries',4);
4193: IF previous_term_exists(p_assignment_id
4194: ,p_start_date) THEN NULL;
4195: ELSE
4196: p_actual_term_date := p_start_date -1;
4196: p_actual_term_date := p_start_date -1;
4197: p_last_standard_date := p_start_date -1;
4198: p_final_process_date := NULL;
4199: --
4200: hr_utility.set_location('peassign.call_terminate_entries',5);
4201: BEGIN
4202: select tp.end_date
4203: into p_last_standard_date
4204: from per_assignments_f a
4210: EXCEPTION
4211: WHEN NO_DATA_FOUND THEN NULL;
4212: END;
4213: --
4214: hr_utility.set_location('peassign.call_terminate_entries',6);
4215: -- VT 10/08/96 bug #306710 added parameter
4216: hrempter.terminate_entries_and_alus(p_assignment_id
4217: ,p_actual_term_date
4218: ,p_last_standard_date
4351: date of the current operation then cancellation of entries
4352: will be required
4353: -----------------------------------------------------------------*/
4354: --
4355: hr_utility.set_location('hr_assignment.test_for_cancel_reterm',1);
4356: if p_mode in ('CORRECTION'
4357: ,'UPDATE'
4358: ,'UPDATE_OVERRIDE'
4359: ,'UPDATE_CHANGE_INSERT' )
4455: l_cobra_term_exists VARCHAR2(1);
4456: local_warning exception;
4457: --
4458: BEGIN
4459: hr_utility.set_location('hr_assignment.check_for_cobra',1);
4460: BEGIN
4461: select 'Y'
4462: into l_cobra_term_exists
4463: from sys.dual
4479: EXCEPTION
4480: WHEN NO_DATA_FOUND THEN NULL;
4481: END;
4482: --
4483: hr_utility.set_location('hr_assignment.check_for_cobra',2);
4484: if l_cobra_term_exists = 'Y' then
4485: raise local_warning;
4486: end if;
4487: --
4486: end if;
4487: --
4488: EXCEPTION
4489: when local_warning then
4490: hr_utility.set_warning;
4491: END check_for_cobra;
4492: --
4493: -----------------------------------------------------------------------
4494: -- validate_pos
4521: ORDER BY date_start DESC;
4522:
4523: BEGIN
4524:
4525: hr_utility.set_location('Entering: '||l_proc, 10);
4526:
4527: OPEN get_pos;
4528: FETCH get_pos INTO l_dummy_dt, l_pos_id;
4529: CLOSE get_pos;
4527: OPEN get_pos;
4528: FETCH get_pos INTO l_dummy_dt, l_pos_id;
4529: CLOSE get_pos;
4530:
4531: hr_utility.trace('l_pos_id: '||to_char(l_pos_id));
4532:
4533: IF l_pos_id IS NULL THEN
4534: hr_utility.set_message(801,'HR_6346_EMP_ASS_NO_POS');
4535: hr_utility.raise_error;
4530:
4531: hr_utility.trace('l_pos_id: '||to_char(l_pos_id));
4532:
4533: IF l_pos_id IS NULL THEN
4534: hr_utility.set_message(801,'HR_6346_EMP_ASS_NO_POS');
4535: hr_utility.raise_error;
4536: END IF;
4537:
4538: hr_utility.set_location('Leaving: '||l_proc, 40);
4531: hr_utility.trace('l_pos_id: '||to_char(l_pos_id));
4532:
4533: IF l_pos_id IS NULL THEN
4534: hr_utility.set_message(801,'HR_6346_EMP_ASS_NO_POS');
4535: hr_utility.raise_error;
4536: END IF;
4537:
4538: hr_utility.set_location('Leaving: '||l_proc, 40);
4539:
4534: hr_utility.set_message(801,'HR_6346_EMP_ASS_NO_POS');
4535: hr_utility.raise_error;
4536: END IF;
4537:
4538: hr_utility.set_location('Leaving: '||l_proc, 40);
4539:
4540: END validate_pos;
4541: --
4542: --
4590: where business_group_id = p_business_group_id);
4591: --
4592: begin
4593: --
4594: hr_utility.set_location('Entering hr_assignment.per_dflt_asg_cost_alloc_ff',25);
4595: --
4596: open c_formula_id(l_formula_name, p_business_group_id, p_effective_date);
4597: fetch c_formula_id into l_formula_id;
4598: --
4595: --
4596: open c_formula_id(l_formula_name, p_business_group_id, p_effective_date);
4597: fetch c_formula_id into l_formula_id;
4598: --
4599: hr_utility.set_location('hr_assignment.per_dflt_asg_cost_alloc_ff',26);
4600: --
4601: if c_formula_id%notfound then
4602: hr_utility.set_location('hr_assignment.per_dflt_asg_cost_alloc_ff',27);
4603: close c_formula_id;
4598: --
4599: hr_utility.set_location('hr_assignment.per_dflt_asg_cost_alloc_ff',26);
4600: --
4601: if c_formula_id%notfound then
4602: hr_utility.set_location('hr_assignment.per_dflt_asg_cost_alloc_ff',27);
4603: close c_formula_id;
4604: return 'N';
4605: end if;
4606: hr_utility.set_location('hr_assignment.per_dflt_asg_cost_alloc_ff',28);
4602: hr_utility.set_location('hr_assignment.per_dflt_asg_cost_alloc_ff',27);
4603: close c_formula_id;
4604: return 'N';
4605: end if;
4606: hr_utility.set_location('hr_assignment.per_dflt_asg_cost_alloc_ff',28);
4607: close c_formula_id;
4608: hr_utility.set_location('hr_assignment.per_dflt_asg_cost_alloc_ff-formula_id :'
4609: || l_formula_id,29);
4610: -- Insert fnd_sessions row
4604: return 'N';
4605: end if;
4606: hr_utility.set_location('hr_assignment.per_dflt_asg_cost_alloc_ff',28);
4607: close c_formula_id;
4608: hr_utility.set_location('hr_assignment.per_dflt_asg_cost_alloc_ff-formula_id :'
4609: || l_formula_id,29);
4610: -- Insert fnd_sessions row
4611: open c_session_date;
4612: fetch c_session_date into l_session_date;
4609: || l_formula_id,29);
4610: -- Insert fnd_sessions row
4611: open c_session_date;
4612: fetch c_session_date into l_session_date;
4613: hr_utility.set_location('hr_assignment.per_dflt_asg_cost_alloc_ff' || l_session_date,30);
4614: if c_session_date%notfound then
4615: hr_utility.set_location('hr_assignment.per_dflt_asg_cost_alloc_ff',31);
4616: insert into fnd_sessions (SESSION_ID, EFFECTIVE_DATE) values(userenv('sessionid'), trunc(p_effective_date));
4617: hr_utility.set_location('hr_assignment.per_dflt_asg_cost_alloc_ff',32);
4611: open c_session_date;
4612: fetch c_session_date into l_session_date;
4613: hr_utility.set_location('hr_assignment.per_dflt_asg_cost_alloc_ff' || l_session_date,30);
4614: if c_session_date%notfound then
4615: hr_utility.set_location('hr_assignment.per_dflt_asg_cost_alloc_ff',31);
4616: insert into fnd_sessions (SESSION_ID, EFFECTIVE_DATE) values(userenv('sessionid'), trunc(p_effective_date));
4617: hr_utility.set_location('hr_assignment.per_dflt_asg_cost_alloc_ff',32);
4618: end if;
4619: --
4613: hr_utility.set_location('hr_assignment.per_dflt_asg_cost_alloc_ff' || l_session_date,30);
4614: if c_session_date%notfound then
4615: hr_utility.set_location('hr_assignment.per_dflt_asg_cost_alloc_ff',31);
4616: insert into fnd_sessions (SESSION_ID, EFFECTIVE_DATE) values(userenv('sessionid'), trunc(p_effective_date));
4617: hr_utility.set_location('hr_assignment.per_dflt_asg_cost_alloc_ff',32);
4618: end if;
4619: --
4620: hr_utility.set_location('hr_assignment.per_dflt_asg_cost_alloc_ff',33);
4621: -- Initialize the formula
4616: insert into fnd_sessions (SESSION_ID, EFFECTIVE_DATE) values(userenv('sessionid'), trunc(p_effective_date));
4617: hr_utility.set_location('hr_assignment.per_dflt_asg_cost_alloc_ff',32);
4618: end if;
4619: --
4620: hr_utility.set_location('hr_assignment.per_dflt_asg_cost_alloc_ff',33);
4621: -- Initialize the formula
4622: ff_exec.init_formula(l_formula_id,p_effective_date, l_inputs, l_outputs);
4623: --
4624: hr_utility.set_location('hr_assignment.per_dflt_asg_cost_alloc_ff',34);
4620: hr_utility.set_location('hr_assignment.per_dflt_asg_cost_alloc_ff',33);
4621: -- Initialize the formula
4622: ff_exec.init_formula(l_formula_id,p_effective_date, l_inputs, l_outputs);
4623: --
4624: hr_utility.set_location('hr_assignment.per_dflt_asg_cost_alloc_ff',34);
4625: --
4626: --set the inputs
4627: --
4628: for i in nvl(l_inputs.first,0) .. nvl(l_inputs.last,-1) loop
4636: l_inputs(i).value := trunc(p_effective_date);
4637: end if;
4638: end loop;
4639: --
4640: hr_utility.set_location('hr_assignment.per_dflt_asg_cost_alloc_ff',35);
4641: --
4642: ff_exec.run_formula(l_inputs, l_outputs);
4643: --
4644: hr_utility.set_location('hr_assignment.per_dflt_asg_cost_alloc_ff',36);
4640: hr_utility.set_location('hr_assignment.per_dflt_asg_cost_alloc_ff',35);
4641: --
4642: ff_exec.run_formula(l_inputs, l_outputs);
4643: --
4644: hr_utility.set_location('hr_assignment.per_dflt_asg_cost_alloc_ff',36);
4645: --
4646: for i in nvl(l_outputs.first,0) .. nvl(l_outputs.last,-1) loop
4647: if (l_outputs(i).name = 'USE_FORMULA') then
4648: hr_utility.set_location('hr_assignment.per_dflt_asg_cost_alloc_ff-'|| l_outputs(i).value,361);
4644: hr_utility.set_location('hr_assignment.per_dflt_asg_cost_alloc_ff',36);
4645: --
4646: for i in nvl(l_outputs.first,0) .. nvl(l_outputs.last,-1) loop
4647: if (l_outputs(i).name = 'USE_FORMULA') then
4648: hr_utility.set_location('hr_assignment.per_dflt_asg_cost_alloc_ff-'|| l_outputs(i).value,361);
4649: l_use_formula := nvl(l_outputs(i).value,'N');
4650: if (l_use_formula <> 'Y')then
4651: return 'N';
4652: end if;
4650: if (l_use_formula <> 'Y')then
4651: return 'N';
4652: end if;
4653: elsif (substr(l_outputs(i).name,1,26) = 'COST_ALLOCATION_KEYFLEX_ID') then
4654: hr_utility.set_location('hr_assignment.per_dflt_asg_cost_alloc_ff-'|| l_outputs(i).value,362);
4655: l_rec(to_number(substr(l_outputs(i).name,27))).cost_allocation_keyflex_id := l_outputs(i).value;
4656: elsif (substr(l_outputs(i).name,1,10) = 'PROPORTION') then
4657: hr_utility.set_location('hr_assignment.per_dflt_asg_cost_alloc_ff-'|| l_outputs(i).value,363);
4658: l_rec(to_number(substr(l_outputs(i).name,11))).PROPORTION := l_outputs(i).value;
4653: elsif (substr(l_outputs(i).name,1,26) = 'COST_ALLOCATION_KEYFLEX_ID') then
4654: hr_utility.set_location('hr_assignment.per_dflt_asg_cost_alloc_ff-'|| l_outputs(i).value,362);
4655: l_rec(to_number(substr(l_outputs(i).name,27))).cost_allocation_keyflex_id := l_outputs(i).value;
4656: elsif (substr(l_outputs(i).name,1,10) = 'PROPORTION') then
4657: hr_utility.set_location('hr_assignment.per_dflt_asg_cost_alloc_ff-'|| l_outputs(i).value,363);
4658: l_rec(to_number(substr(l_outputs(i).name,11))).PROPORTION := l_outputs(i).value;
4659: end if;
4660: end loop;
4661: --
4658: l_rec(to_number(substr(l_outputs(i).name,11))).PROPORTION := l_outputs(i).value;
4659: end if;
4660: end loop;
4661: --
4662: hr_utility.set_location('hr_assignment.per_dflt_asg_cost_alloc_ff',37);
4663: --
4664: if l_use_formula = 'Y' then
4665: for i in nvl(l_rec.first,0) .. nvl(l_rec.last,-1) loop
4666: hr_utility.set_location('hr_assignment.per_dflt_asg_cost_alloc_ff l_rec(i).cost_all_kf_id'
4662: hr_utility.set_location('hr_assignment.per_dflt_asg_cost_alloc_ff',37);
4663: --
4664: if l_use_formula = 'Y' then
4665: for i in nvl(l_rec.first,0) .. nvl(l_rec.last,-1) loop
4666: hr_utility.set_location('hr_assignment.per_dflt_asg_cost_alloc_ff l_rec(i).cost_all_kf_id'
4667: || l_rec(i).cost_allocation_keyflex_id,381);
4668: hr_utility.set_location('hr_assignment.per_dflt_asg_cost_alloc_ff l_rec(i).proportion'
4669: || l_rec(i).proportion,382);
4670: if (nvl(l_rec(i).cost_allocation_keyflex_id, -1) <> -1) then
4664: if l_use_formula = 'Y' then
4665: for i in nvl(l_rec.first,0) .. nvl(l_rec.last,-1) loop
4666: hr_utility.set_location('hr_assignment.per_dflt_asg_cost_alloc_ff l_rec(i).cost_all_kf_id'
4667: || l_rec(i).cost_allocation_keyflex_id,381);
4668: hr_utility.set_location('hr_assignment.per_dflt_asg_cost_alloc_ff l_rec(i).proportion'
4669: || l_rec(i).proportion,382);
4670: if (nvl(l_rec(i).cost_allocation_keyflex_id, -1) <> -1) then
4671: --
4672: --
4669: || l_rec(i).proportion,382);
4670: if (nvl(l_rec(i).cost_allocation_keyflex_id, -1) <> -1) then
4671: --
4672: --
4673: hr_utility.set_location('hr_assignment.per_dflt_asg_cost_alloc_ff - l_rec(i).proportion:'||l_rec(i).proportion,313);
4674: 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);
4675: --
4676: l_proportion := trunc(l_rec(i).proportion,4);
4677: --
4670: if (nvl(l_rec(i).cost_allocation_keyflex_id, -1) <> -1) then
4671: --
4672: --
4673: hr_utility.set_location('hr_assignment.per_dflt_asg_cost_alloc_ff - l_rec(i).proportion:'||l_rec(i).proportion,313);
4674: 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);
4675: --
4676: l_proportion := trunc(l_rec(i).proportion,4);
4677: --
4678: hr_utility.set_location('hr_assignment.per_dflt_asg_cost_alloc_ff - l_proportion1 :'||l_proportion,315);
4674: 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);
4675: --
4676: l_proportion := trunc(l_rec(i).proportion,4);
4677: --
4678: hr_utility.set_location('hr_assignment.per_dflt_asg_cost_alloc_ff - l_proportion1 :'||l_proportion,315);
4679: if l_proportion > 1 then
4680: l_proportion := 1;
4681: end if;
4682: hr_utility.set_location('hr_assignment.per_dflt_asg_cost_alloc_ff - l_proportion2 :'||l_proportion,316);
4678: hr_utility.set_location('hr_assignment.per_dflt_asg_cost_alloc_ff - l_proportion1 :'||l_proportion,315);
4679: if l_proportion > 1 then
4680: l_proportion := 1;
4681: end if;
4682: hr_utility.set_location('hr_assignment.per_dflt_asg_cost_alloc_ff - l_proportion2 :'||l_proportion,316);
4683: --
4684: if l_proportion > 0 then
4685: for r3 in c_cost_allocation_keyflex(l_rec(i).cost_allocation_keyflex_id) loop
4686: hr_utility.set_location('hr_assignment.per_dflt_asg_cost_alloc_ff',39);
4682: hr_utility.set_location('hr_assignment.per_dflt_asg_cost_alloc_ff - l_proportion2 :'||l_proportion,316);
4683: --
4684: if l_proportion > 0 then
4685: for r3 in c_cost_allocation_keyflex(l_rec(i).cost_allocation_keyflex_id) loop
4686: hr_utility.set_location('hr_assignment.per_dflt_asg_cost_alloc_ff',39);
4687: --
4688: pay_cost_allocation_api.create_cost_allocation(
4689: p_validate =>false ,
4690: p_effective_date =>p_effective_date,
4729: p_segment30 =>r3.segment30,
4730: p_concat_segments =>r3.concatenated_segments
4731: );
4732: end loop;
4733: hr_utility.set_location('hr_assignment.per_dflt_asg_cost_alloc_ff',391);
4734: --
4735: end if;
4736: --
4737: hr_utility.set_location('hr_assignment.per_dflt_asg_cost_alloc_ff',40);
4733: hr_utility.set_location('hr_assignment.per_dflt_asg_cost_alloc_ff',391);
4734: --
4735: end if;
4736: --
4737: hr_utility.set_location('hr_assignment.per_dflt_asg_cost_alloc_ff',40);
4738: --
4739: end if;
4740: --
4741: hr_utility.set_location('hr_assignment.per_dflt_asg_cost_alloc_ff',41);
4737: hr_utility.set_location('hr_assignment.per_dflt_asg_cost_alloc_ff',40);
4738: --
4739: end if;
4740: --
4741: hr_utility.set_location('hr_assignment.per_dflt_asg_cost_alloc_ff',41);
4742: --
4743: end loop;
4744: --
4745: hr_utility.set_location('hr_assignment.per_dflt_asg_cost_alloc_ff',42);
4741: hr_utility.set_location('hr_assignment.per_dflt_asg_cost_alloc_ff',41);
4742: --
4743: end loop;
4744: --
4745: hr_utility.set_location('hr_assignment.per_dflt_asg_cost_alloc_ff',42);
4746: --
4747: return 'Y';
4748: --
4749: end if;
4747: return 'Y';
4748: --
4749: end if;
4750: --
4751: hr_utility.set_location('Leaving hr_assignment.per_dflt_asg_cost_alloc_ff',43);
4752: --
4753: return 'N';
4754: --
4755: end;
4873: or hr_psf_shd.system_availability_status(budget_unit3_id) = 'MONEY')
4874: );
4875: --
4876: BEGIN
4877: hr_utility.set_location('hr_assignment.load_assignment_allocation',1);
4878: --
4879: if nvl(fnd_profile.value('HR_DEFAULT_ASG_COST_ALLOC'),'N') <> 'Y' then
4880: return;
4881: end if;
4879: if nvl(fnd_profile.value('HR_DEFAULT_ASG_COST_ALLOC'),'N') <> 'Y' then
4880: return;
4881: end if;
4882: --
4883: hr_utility.set_location('hr_assignment.load_assignment_allocation',2);
4884: --
4885: if p_position_id is null then
4886: return;
4887: end if;
4885: if p_position_id is null then
4886: return;
4887: end if;
4888: --
4889: hr_utility.set_location('hr_assignment.load_assignment_allocation',3);
4890: hr_utility.set_location('hr_assignment.load_assignment_allocation - effec date :'|| p_effective_date,3);
4891: hr_utility.set_location('hr_assignment.load_assignment_allocation - p_position_id :'|| p_position_id,3);
4892: hr_utility.set_location('hr_assignment.load_assignment_allocation - assignment_id :'|| p_assignment_id,3);
4893: hr_utility.set_location('hr_assignment.load_assignment_allocation - business_group_id :'|| p_business_group_id,3);
4886: return;
4887: end if;
4888: --
4889: hr_utility.set_location('hr_assignment.load_assignment_allocation',3);
4890: hr_utility.set_location('hr_assignment.load_assignment_allocation - effec date :'|| p_effective_date,3);
4891: hr_utility.set_location('hr_assignment.load_assignment_allocation - p_position_id :'|| p_position_id,3);
4892: hr_utility.set_location('hr_assignment.load_assignment_allocation - assignment_id :'|| p_assignment_id,3);
4893: hr_utility.set_location('hr_assignment.load_assignment_allocation - business_group_id :'|| p_business_group_id,3);
4894: --
4887: end if;
4888: --
4889: hr_utility.set_location('hr_assignment.load_assignment_allocation',3);
4890: hr_utility.set_location('hr_assignment.load_assignment_allocation - effec date :'|| p_effective_date,3);
4891: hr_utility.set_location('hr_assignment.load_assignment_allocation - p_position_id :'|| p_position_id,3);
4892: hr_utility.set_location('hr_assignment.load_assignment_allocation - assignment_id :'|| p_assignment_id,3);
4893: hr_utility.set_location('hr_assignment.load_assignment_allocation - business_group_id :'|| p_business_group_id,3);
4894: --
4895: open c_asg_cost_allocations(p_assignment_id);
4888: --
4889: hr_utility.set_location('hr_assignment.load_assignment_allocation',3);
4890: hr_utility.set_location('hr_assignment.load_assignment_allocation - effec date :'|| p_effective_date,3);
4891: hr_utility.set_location('hr_assignment.load_assignment_allocation - p_position_id :'|| p_position_id,3);
4892: hr_utility.set_location('hr_assignment.load_assignment_allocation - assignment_id :'|| p_assignment_id,3);
4893: hr_utility.set_location('hr_assignment.load_assignment_allocation - business_group_id :'|| p_business_group_id,3);
4894: --
4895: open c_asg_cost_allocations(p_assignment_id);
4896: fetch c_asg_cost_allocations into l_dummy;
4889: hr_utility.set_location('hr_assignment.load_assignment_allocation',3);
4890: hr_utility.set_location('hr_assignment.load_assignment_allocation - effec date :'|| p_effective_date,3);
4891: hr_utility.set_location('hr_assignment.load_assignment_allocation - p_position_id :'|| p_position_id,3);
4892: hr_utility.set_location('hr_assignment.load_assignment_allocation - assignment_id :'|| p_assignment_id,3);
4893: hr_utility.set_location('hr_assignment.load_assignment_allocation - business_group_id :'|| p_business_group_id,3);
4894: --
4895: open c_asg_cost_allocations(p_assignment_id);
4896: fetch c_asg_cost_allocations into l_dummy;
4897: if c_asg_cost_allocations%found then
4894: --
4895: open c_asg_cost_allocations(p_assignment_id);
4896: fetch c_asg_cost_allocations into l_dummy;
4897: if c_asg_cost_allocations%found then
4898: hr_utility.set_location('hr_assignment.load_assignment_allocation',4);
4899: return;
4900: end if;
4901: --
4902: hr_utility.set_location('hr_assignment.load_assignment_allocation',5);
4898: hr_utility.set_location('hr_assignment.load_assignment_allocation',4);
4899: return;
4900: end if;
4901: --
4902: hr_utility.set_location('hr_assignment.load_assignment_allocation',5);
4903: --
4904: if (per_dflt_asg_cost_alloc_ff(p_assignment_id, p_business_group_id, p_position_id, p_effective_date) = 'Y') then
4905: hr_utility.set_location('hr_assignment.load_assignment_allocation',6);
4906: return;
4901: --
4902: hr_utility.set_location('hr_assignment.load_assignment_allocation',5);
4903: --
4904: if (per_dflt_asg_cost_alloc_ff(p_assignment_id, p_business_group_id, p_position_id, p_effective_date) = 'Y') then
4905: hr_utility.set_location('hr_assignment.load_assignment_allocation',6);
4906: return;
4907: end if;
4908: --
4909: hr_utility.set_location('hr_assignment.load_assignment_allocation',7);
4905: hr_utility.set_location('hr_assignment.load_assignment_allocation',6);
4906: return;
4907: end if;
4908: --
4909: hr_utility.set_location('hr_assignment.load_assignment_allocation',7);
4910: --
4911: open c0;
4912: fetch c0 into l_unit1, l_unit2, l_unit3;
4913: close c0;
4911: open c0;
4912: fetch c0 into l_unit1, l_unit2, l_unit3;
4913: close c0;
4914: --
4915: hr_utility.set_location('hr_assignment.load_assignment_allocation - ' || l_unit1,8);
4916: hr_utility.set_location('hr_assignment.load_assignment_allocation - ' || l_unit2,9);
4917: hr_utility.set_location('hr_assignment.load_assignment_allocation - ' || l_unit3,10);
4918: --
4919: open c2(l_unit1, l_unit2, l_unit3);
4912: fetch c0 into l_unit1, l_unit2, l_unit3;
4913: close c0;
4914: --
4915: hr_utility.set_location('hr_assignment.load_assignment_allocation - ' || l_unit1,8);
4916: hr_utility.set_location('hr_assignment.load_assignment_allocation - ' || l_unit2,9);
4917: hr_utility.set_location('hr_assignment.load_assignment_allocation - ' || l_unit3,10);
4918: --
4919: open c2(l_unit1, l_unit2, l_unit3);
4920: fetch c2 into l_period_value;
4913: close c0;
4914: --
4915: hr_utility.set_location('hr_assignment.load_assignment_allocation - ' || l_unit1,8);
4916: hr_utility.set_location('hr_assignment.load_assignment_allocation - ' || l_unit2,9);
4917: hr_utility.set_location('hr_assignment.load_assignment_allocation - ' || l_unit3,10);
4918: --
4919: open c2(l_unit1, l_unit2, l_unit3);
4920: fetch c2 into l_period_value;
4921: close c2;
4919: open c2(l_unit1, l_unit2, l_unit3);
4920: fetch c2 into l_period_value;
4921: close c2;
4922: --
4923: hr_utility.set_location('hr_assignment.load_assignment_allocation - '|| l_period_value,11);
4924: --
4925: if nvl(l_period_value,0) <> 0 then
4926: --
4927: hr_utility.set_location('hr_assignment.load_assignment_allocation ',12);
4923: hr_utility.set_location('hr_assignment.load_assignment_allocation - '|| l_period_value,11);
4924: --
4925: if nvl(l_period_value,0) <> 0 then
4926: --
4927: hr_utility.set_location('hr_assignment.load_assignment_allocation ',12);
4928: --
4929: for r1 in c1(l_unit1, l_unit2, l_unit3) loop
4930: --
4931: hr_utility.set_location('hr_assignment.load_assignment_allocation - '||r1.proportion,13);
4927: hr_utility.set_location('hr_assignment.load_assignment_allocation ',12);
4928: --
4929: for r1 in c1(l_unit1, l_unit2, l_unit3) loop
4930: --
4931: hr_utility.set_location('hr_assignment.load_assignment_allocation - '||r1.proportion,13);
4932: hr_utility.set_location('hr_assignment.load_assignment_allocation - '||r1.cost_allocation_keyflex_id,14);
4933: --
4934: l_proportion := trunc(r1.proportion/l_period_value,4);
4935: --
4928: --
4929: for r1 in c1(l_unit1, l_unit2, l_unit3) loop
4930: --
4931: hr_utility.set_location('hr_assignment.load_assignment_allocation - '||r1.proportion,13);
4932: hr_utility.set_location('hr_assignment.load_assignment_allocation - '||r1.cost_allocation_keyflex_id,14);
4933: --
4934: l_proportion := trunc(r1.proportion/l_period_value,4);
4935: --
4936: if l_proportion > 1 then
4986: end loop;
4987: --
4988: end if;
4989: --
4990: hr_utility.set_location('hr_assignment.load_assignment_allocation ',15);
4991: --
4992: end loop;
4993: --
4994: hr_utility.set_location('hr_assignment.load_assignment_allocation ',16);
4990: hr_utility.set_location('hr_assignment.load_assignment_allocation ',15);
4991: --
4992: end loop;
4993: --
4994: hr_utility.set_location('hr_assignment.load_assignment_allocation ',16);
4995: --
4996: end if;
4997: --
4998: hr_utility.set_location('hr_assignment.load_assignment_allocation ',17);
4994: hr_utility.set_location('hr_assignment.load_assignment_allocation ',16);
4995: --
4996: end if;
4997: --
4998: hr_utility.set_location('hr_assignment.load_assignment_allocation ',17);
4999: --
5000: END load_assignment_allocation;
5001: -----------------------------------------------------------------------
5002: -- update_assgn_context_value
5038: and primary_flag = 'Y';
5039:
5040: begin
5041:
5042: hr_utility.set_location('Entering: '||l_proc_name,10);
5043:
5044: hr_utility.set_location('Business_group_id: '||p_business_group_id,11);
5045: hr_utility.set_location('Person_id: '||p_person_id,12);
5046: hr_utility.set_location('Assignment_id: '||p_assignment_id,13);
5040: begin
5041:
5042: hr_utility.set_location('Entering: '||l_proc_name,10);
5043:
5044: hr_utility.set_location('Business_group_id: '||p_business_group_id,11);
5045: hr_utility.set_location('Person_id: '||p_person_id,12);
5046: hr_utility.set_location('Assignment_id: '||p_assignment_id,13);
5047: hr_utility.set_location('Effective_start_date: '||p_effective_start_date,14);
5048:
5041:
5042: hr_utility.set_location('Entering: '||l_proc_name,10);
5043:
5044: hr_utility.set_location('Business_group_id: '||p_business_group_id,11);
5045: hr_utility.set_location('Person_id: '||p_person_id,12);
5046: hr_utility.set_location('Assignment_id: '||p_assignment_id,13);
5047: hr_utility.set_location('Effective_start_date: '||p_effective_start_date,14);
5048:
5049: open csr_dff_context ;
5042: hr_utility.set_location('Entering: '||l_proc_name,10);
5043:
5044: hr_utility.set_location('Business_group_id: '||p_business_group_id,11);
5045: hr_utility.set_location('Person_id: '||p_person_id,12);
5046: hr_utility.set_location('Assignment_id: '||p_assignment_id,13);
5047: hr_utility.set_location('Effective_start_date: '||p_effective_start_date,14);
5048:
5049: open csr_dff_context ;
5050: fetch csr_dff_context into l_context_val;
5043:
5044: hr_utility.set_location('Business_group_id: '||p_business_group_id,11);
5045: hr_utility.set_location('Person_id: '||p_person_id,12);
5046: hr_utility.set_location('Assignment_id: '||p_assignment_id,13);
5047: hr_utility.set_location('Effective_start_date: '||p_effective_start_date,14);
5048:
5049: open csr_dff_context ;
5050: fetch csr_dff_context into l_context_val;
5051:
5052: if csr_dff_context%found then
5053:
5054: if l_context_val <> '-100' then
5055:
5056: hr_utility.set_location('DFF setting exists',20);
5057:
5058: if instr(upper(l_context_val),'$PROFILES$') <> 0 then
5059: l_context_val:=replace(upper(l_context_val),'$PROFILES$.PER_');
5060: l_context_val:=replace(upper(l_context_val),'$PROFILES$.');
5064: fetch csr_ass_data into l_ass_attribute_category;
5065:
5066: if csr_ass_data%found then
5067:
5068: hr_utility.set_location('l_ass_att: '||l_ass_attribute_category,15);
5069:
5070: if l_ass_attribute_category is null
5071: or trim(l_ass_attribute_category) = '' then
5072:
5071: or trim(l_ass_attribute_category) = '' then
5072:
5073: begin
5074:
5075: hr_utility.set_location(l_proc_name||' got value for reference field',30);
5076:
5077: l_effective_start_date:= p_effective_start_date;
5078:
5079: hr_utility.set_location(l_proc_name||' selecting records ',30);
5075: hr_utility.set_location(l_proc_name||' got value for reference field',30);
5076:
5077: l_effective_start_date:= p_effective_start_date;
5078:
5079: hr_utility.set_location(l_proc_name||' selecting records ',30);
5080:
5081: l_sql := '
5082: declare
5083: g_rec per_assignments_v%rowtype;
5089: EXECUTE IMMEDIATE l_sql using
5090: in out l_effective_start_date,
5091: in out l_output_context_val;
5092:
5093: hr_utility.set_location(l_proc_name||'l_output_context_val: '||l_output_context_val,35);
5094:
5095:
5096: if l_output_context_val is not null then
5097:
5101: and person_id = p_person_id
5102: and assignment_id = p_assignment_id
5103: and effective_start_date = p_effective_start_date;
5104:
5105: hr_utility.set_location('dynamic sql updated '||sql%rowcount||' records',50);
5106:
5107: end if;
5108:
5109: close csr_ass_data;
5110:
5111: exception
5112: when others then
5113: close csr_ass_data;
5114: hr_utility.set_location(sqlerrm,55);
5115: end;
5116: end if;
5117:
5118: end if;
5121: close csr_dff_context;
5122:
5123: end if;
5124:
5125: hr_utility.set_location('Leaving: '||l_proc_name,60);
5126:
5127: END update_assgn_context_value;
5128:
5129: -- get_assgn_dff_value
5378:
5379: l_proc_name varchar2(20):= 'get_assgn_dff_value';
5380: begin
5381:
5382: hr_utility.set_location('Entering: '||l_proc_name,10);
5383:
5384: OPEN csr_asg;
5385: FETCH csr_asg INTO p_asg_rec;
5386: if csr_asg%found then
5385: FETCH csr_asg INTO p_asg_rec;
5386: if csr_asg%found then
5387: close csr_asg;
5388: end if;
5389: hr_utility.set_location('Leaving: '||l_proc_name,10);
5390: END get_assgn_dff_value;
5391:
5392: --end for bug 6598795
5393: ---------------------------------------------------------------------------------------