[Home] [Help]
316: l_function varchar2(30) := 'Increment Function';
317:
318: begin
319:
320: hr_utility.set_location(l_function,10);
321: hr_utility.set_location('p_placement_id - '||p_placement_id,50);
322: hr_utility.set_location('p_effective_start_date - '||p_effective_start_date,50);
323: hr_utility.set_location('p_step_id - '||p_step_id,50);
324: hr_utility.set_location('p_grade_spine_id - '||p_grade_spine_id,50);
317:
318: begin
319:
320: hr_utility.set_location(l_function,10);
321: hr_utility.set_location('p_placement_id - '||p_placement_id,50);
322: hr_utility.set_location('p_effective_start_date - '||p_effective_start_date,50);
323: hr_utility.set_location('p_step_id - '||p_step_id,50);
324: hr_utility.set_location('p_grade_spine_id - '||p_grade_spine_id,50);
325:
318: begin
319:
320: hr_utility.set_location(l_function,10);
321: hr_utility.set_location('p_placement_id - '||p_placement_id,50);
322: hr_utility.set_location('p_effective_start_date - '||p_effective_start_date,50);
323: hr_utility.set_location('p_step_id - '||p_step_id,50);
324: hr_utility.set_location('p_grade_spine_id - '||p_grade_spine_id,50);
325:
326: open csr_increment1;
319:
320: hr_utility.set_location(l_function,10);
321: hr_utility.set_location('p_placement_id - '||p_placement_id,50);
322: hr_utility.set_location('p_effective_start_date - '||p_effective_start_date,50);
323: hr_utility.set_location('p_step_id - '||p_step_id,50);
324: hr_utility.set_location('p_grade_spine_id - '||p_grade_spine_id,50);
325:
326: open csr_increment1;
327: fetch csr_increment1 into l_increment1;
320: hr_utility.set_location(l_function,10);
321: hr_utility.set_location('p_placement_id - '||p_placement_id,50);
322: hr_utility.set_location('p_effective_start_date - '||p_effective_start_date,50);
323: hr_utility.set_location('p_step_id - '||p_step_id,50);
324: hr_utility.set_location('p_grade_spine_id - '||p_grade_spine_id,50);
325:
326: open csr_increment1;
327: fetch csr_increment1 into l_increment1;
328: close csr_increment1;
326: open csr_increment1;
327: fetch csr_increment1 into l_increment1;
328: close csr_increment1;
329:
330: hr_utility.set_location('l_increment1 (1st SPP Record) - '||l_increment1,60);
331:
332: open csr_increment2;
333: fetch csr_increment2 into l_increment2;
334: close csr_increment2;
332: open csr_increment2;
333: fetch csr_increment2 into l_increment2;
334: close csr_increment2;
335:
336: hr_utility.set_location('l_increment2 (No Step Change) - '||l_increment2,60);
337:
338: open csr_increment3;
339: fetch csr_increment3 into l_increment3;
340: close csr_increment3;
338: open csr_increment3;
339: fetch csr_increment3 into l_increment3;
340: close csr_increment3;
341:
342: hr_utility.set_location('l_increment3 (Grade Scale Auto Change) - '||l_increment3,60);
343:
344: open csr_increment4;
345: fetch csr_increment4 into l_increment4;
346: close csr_increment4;
344: open csr_increment4;
345: fetch csr_increment4 into l_increment4;
346: close csr_increment4;
347:
348: hr_utility.set_location('l_increment4 (1st Grade Spinal Point) - '||l_increment4,60);
349:
350: open csr_all_placements;
351: fetch csr_all_placements into l_dummy;
352: close csr_all_placements;
350: open csr_all_placements;
351: fetch csr_all_placements into l_dummy;
352: close csr_all_placements;
353:
354: hr_utility.set_location('l_dummy (Placement ID to be returned) - '||l_dummy,70);
355:
356: hr_utility.set_location('End of '||l_function,80);
357:
358: return l_dummy;
352: close csr_all_placements;
353:
354: hr_utility.set_location('l_dummy (Placement ID to be returned) - '||l_dummy,70);
355:
356: hr_utility.set_location('End of '||l_function,80);
357:
358: return l_dummy;
359:
360: end func_increment;
395: l_package varchar2(30) := 'Business Rule Check';
396:
397: begin
398:
399: hr_utility.set_location(l_package,1);
400: hr_utility.set_location('p_business_rule - '||p_business_rule,1);
401: hr_utility.set_location('p_br_date_from - '||p_br_date_from,1);
402: hr_utility.set_location('p_br_date_to - '||p_br_date_to,1);
403: hr_utility.set_location('p_dependant_date - '||p_dependant_date,1);
396:
397: begin
398:
399: hr_utility.set_location(l_package,1);
400: hr_utility.set_location('p_business_rule - '||p_business_rule,1);
401: hr_utility.set_location('p_br_date_from - '||p_br_date_from,1);
402: hr_utility.set_location('p_br_date_to - '||p_br_date_to,1);
403: hr_utility.set_location('p_dependant_date - '||p_dependant_date,1);
404:
397: begin
398:
399: hr_utility.set_location(l_package,1);
400: hr_utility.set_location('p_business_rule - '||p_business_rule,1);
401: hr_utility.set_location('p_br_date_from - '||p_br_date_from,1);
402: hr_utility.set_location('p_br_date_to - '||p_br_date_to,1);
403: hr_utility.set_location('p_dependant_date - '||p_dependant_date,1);
404:
405: --
398:
399: hr_utility.set_location(l_package,1);
400: hr_utility.set_location('p_business_rule - '||p_business_rule,1);
401: hr_utility.set_location('p_br_date_from - '||p_br_date_from,1);
402: hr_utility.set_location('p_br_date_to - '||p_br_date_to,1);
403: hr_utility.set_location('p_dependant_date - '||p_dependant_date,1);
404:
405: --
406: -- Check if the business rule has not been set that all other business rule
399: hr_utility.set_location(l_package,1);
400: hr_utility.set_location('p_business_rule - '||p_business_rule,1);
401: hr_utility.set_location('p_br_date_from - '||p_br_date_from,1);
402: hr_utility.set_location('p_br_date_to - '||p_br_date_to,1);
403: hr_utility.set_location('p_dependant_date - '||p_dependant_date,1);
404:
405: --
406: -- Check if the business rule has not been set that all other business rule
407: -- only parameters are null, else raise an error.
412: or p_br_date_to is not null
413: or p_dependant_date is not null then
414:
415: fnd_message.set_name('PER', 'HR_289506_SPP_BR_NULL');
416: hr_utility.raise_error;
417:
418: end if;
419:
420: end if;
441:
442: if p_business_rule not IN ('AOJ','DOB','ASD','LHD') then
443:
444: fnd_message.set_name('PER', 'HR_289507_SPP_BR_INVALID');
445: hr_utility.raise_error;
446:
447: end if;
448:
449: --
453: if p_br_date_from is null
454: or p_br_date_to is null then
455:
456: fnd_message.set_name('PER', 'HR_289510_SPP_BR_DATE_NULL');
457: hr_utility.raise_error;
458:
459: elsif nvl(l_br_date_to,l_br_date_from) < nvl(l_br_date_from,l_br_date_to) then
460:
461: -- The business rule date from is greater than the date to
461: -- The business rule date from is greater than the date to
462: -- so raise an error accordingly
463:
464: fnd_message.set_name('PER', 'HR_289500_SPP_BR_DATE');
465: hr_utility.raise_error;
466:
467: elsif nvl(l_br_date_to,l_br_date_from) >= nvl(l_br_date_from,l_br_date_to) then
468:
469: -- The business rule dates are valid, now check that the gap between is not
473:
474: -- Gap is greater than one year, so raise error
475:
476: fnd_message.set_name('PER', 'HR_289501_SPP_BR_YEAR_GREATER');
477: hr_utility.raise_error;
478:
479: end if;
480:
481: end if;
487:
488: -- raise error as process doesn't know what date to increment assignments on
489:
490: fnd_message.set_name('PER', 'HR_289502_SPP_DEPEND_DATE');
491: hr_utility.raise_error;
492:
493: end if;
494:
495: --
507: -- Raise error due to effective date not being between business rule dates
508: --
509:
510: fnd_message.set_name('PER', 'HR_289503_SPP_EFF_BR_DATE');
511: hr_utility.raise_error;
512:
513: end if;
514:
515: --
522: -- Raise error due to year from being greater than the year to
523: --
524:
525: fnd_message.set_name('PER', 'HR_289504_SPP_BR_YEAR_FROM_TO');
526: hr_utility.raise_error;
527:
528: end if;
529:
530: else
633: FETCH csr_next_sequence into l_next_sequence_number,
634: l_next_spinal_point;
635:
636: if csr_next_sequence%notfound then
637: hr_utility.set_location('MAX POINT REACHED!',20);
638: l_update := 'N';
639: l_exception_report1 := 'Y';
640: exit;
641:
639: l_exception_report1 := 'Y';
640: exit;
641:
642: else
643: hr_utility.set_location('FOUND',21);
644: l_zero := l_zero+1;
645: l_update := 'Y';
646:
647: if p_increment_number = l_zero then
670: -- select statement to get new step id for assignment
671: -- ------------------------------------------------------------------
672: --
673: BEGIN
674: hr_utility.set_location('get step id',1313);
675: hr_utility.set_location('p_grade_spine_id '||p_grade_spine_id,1313);
676: hr_utility.set_location('l_next_spinal_point '||l_next_spinal_point,1313);
677: hr_utility.set_location('l_next_sequence_number '
678: ||l_next_sequence_number,1313);
671: -- ------------------------------------------------------------------
672: --
673: BEGIN
674: hr_utility.set_location('get step id',1313);
675: hr_utility.set_location('p_grade_spine_id '||p_grade_spine_id,1313);
676: hr_utility.set_location('l_next_spinal_point '||l_next_spinal_point,1313);
677: hr_utility.set_location('l_next_sequence_number '
678: ||l_next_sequence_number,1313);
679: select step_id
672: --
673: BEGIN
674: hr_utility.set_location('get step id',1313);
675: hr_utility.set_location('p_grade_spine_id '||p_grade_spine_id,1313);
676: hr_utility.set_location('l_next_spinal_point '||l_next_spinal_point,1313);
677: hr_utility.set_location('l_next_sequence_number '
678: ||l_next_sequence_number,1313);
679: select step_id
680: into l_new_step_id
673: BEGIN
674: hr_utility.set_location('get step id',1313);
675: hr_utility.set_location('p_grade_spine_id '||p_grade_spine_id,1313);
676: hr_utility.set_location('l_next_spinal_point '||l_next_spinal_point,1313);
677: hr_utility.set_location('l_next_sequence_number '
678: ||l_next_sequence_number,1313);
679: select step_id
680: into l_new_step_id
681: from per_spinal_point_steps_f
684: and spinal_point_id = l_next_spinal_point;
685: EXCEPTION
686: WHEN NO_DATA_FOUND
687: THEN
688: hr_utility.set_location('NO_DATA_FOUND',1314);
689: hr_utility.set_location('step id',1314);
690: hr_utility.set_location('not found for l_next_sequence_number'
691: ||l_next_sequence_number, 1314);
692: hr_utility.set_location('p_grade_spine_id '||p_grade_spine_id,1314);
685: EXCEPTION
686: WHEN NO_DATA_FOUND
687: THEN
688: hr_utility.set_location('NO_DATA_FOUND',1314);
689: hr_utility.set_location('step id',1314);
690: hr_utility.set_location('not found for l_next_sequence_number'
691: ||l_next_sequence_number, 1314);
692: hr_utility.set_location('p_grade_spine_id '||p_grade_spine_id,1314);
693: hr_utility.set_location('l_next_spinal_point '||l_next_spinal_point,1314);
686: WHEN NO_DATA_FOUND
687: THEN
688: hr_utility.set_location('NO_DATA_FOUND',1314);
689: hr_utility.set_location('step id',1314);
690: hr_utility.set_location('not found for l_next_sequence_number'
691: ||l_next_sequence_number, 1314);
692: hr_utility.set_location('p_grade_spine_id '||p_grade_spine_id,1314);
693: hr_utility.set_location('l_next_spinal_point '||l_next_spinal_point,1314);
694: END;
688: hr_utility.set_location('NO_DATA_FOUND',1314);
689: hr_utility.set_location('step id',1314);
690: hr_utility.set_location('not found for l_next_sequence_number'
691: ||l_next_sequence_number, 1314);
692: hr_utility.set_location('p_grade_spine_id '||p_grade_spine_id,1314);
693: hr_utility.set_location('l_next_spinal_point '||l_next_spinal_point,1314);
694: END;
695: --
696: -- Now update all the records using the api
689: hr_utility.set_location('step id',1314);
690: hr_utility.set_location('not found for l_next_sequence_number'
691: ||l_next_sequence_number, 1314);
692: hr_utility.set_location('p_grade_spine_id '||p_grade_spine_id,1314);
693: hr_utility.set_location('l_next_spinal_point '||l_next_spinal_point,1314);
694: END;
695: --
696: -- Now update all the records using the api
697: --
711: ,p_effective_start_date => l_effective_start_date
712: ,p_effective_end_date => l_effective_end_date
713: );
714: --
715: hr_utility.set_location ('Before HR_SPP_EXCEPTION_DATA.populate_spp_table',101);
716: hr_utility.set_location ('l_exception_report_1 = '||l_exception_report1,151);
717: --
718: ----------------------------------------------------------------------------
719: -- Update the what if table with the updated record
712: ,p_effective_end_date => l_effective_end_date
713: );
714: --
715: hr_utility.set_location ('Before HR_SPP_EXCEPTION_DATA.populate_spp_table',101);
716: hr_utility.set_location ('l_exception_report_1 = '||l_exception_report1,151);
717: --
718: ----------------------------------------------------------------------------
719: -- Update the what if table with the updated record
720: -- --------------------------------------------------------------------------
717: --
718: ----------------------------------------------------------------------------
719: -- Update the what if table with the updated record
720: -- --------------------------------------------------------------------------
721: hr_utility.set_location ('HR_SPP_WI_DATA.populate_spp_wi_table',121);
722: hr_utility.set_location ('HR_SPP_WI_DATA - Placement:'||p_placement_id,121);
723: hr_utility.set_location ('HR_SPP_WI_DATA - Assignment:'||p_assignment_id,121);
724: --
725: HR_SPP_WI_DATA.populate_spp_wi_table
718: ----------------------------------------------------------------------------
719: -- Update the what if table with the updated record
720: -- --------------------------------------------------------------------------
721: hr_utility.set_location ('HR_SPP_WI_DATA.populate_spp_wi_table',121);
722: hr_utility.set_location ('HR_SPP_WI_DATA - Placement:'||p_placement_id,121);
723: hr_utility.set_location ('HR_SPP_WI_DATA - Assignment:'||p_assignment_id,121);
724: --
725: HR_SPP_WI_DATA.populate_spp_wi_table
726: (p_placement_id => p_placement_id
719: -- Update the what if table with the updated record
720: -- --------------------------------------------------------------------------
721: hr_utility.set_location ('HR_SPP_WI_DATA.populate_spp_wi_table',121);
722: hr_utility.set_location ('HR_SPP_WI_DATA - Placement:'||p_placement_id,121);
723: hr_utility.set_location ('HR_SPP_WI_DATA - Assignment:'||p_assignment_id,121);
724: --
725: HR_SPP_WI_DATA.populate_spp_wi_table
726: (p_placement_id => p_placement_id
727: ,p_assignment_id => p_assignment_id
746: -- --------------------------------------------------------------------------
747: --
748: if (l_exception_report1 = 'Y')
749: then
750: hr_utility.set_location
751: ('Populating HR_SPP_EXCEPTION_DATA.populate_spp_table',102);
752: -- vik failing with no data found here.
753: -- but why doesn't the comment show up on the trace?
754: HR_SPP_EXCEPTION_DATA.populate_spp_table
833:
834:
835: begin
836:
837: hr_utility.set_location('Entering check_increment_available procedure',5);
838:
839: l_future_sequence_id := null;
840: l_update := 'Y';
841:
849: where placement_id = p_placement_id;
850: EXCEPTION
851: WHEN NO_DATA_FOUND
852: THEN
853: hr_utility.set_location('NO_DATA_FOUND',1315);
854: hr_utility.set_location('max(effective_end_date) ',1315);
855: hr_utility.set_location('not found for p_placement_id '
856: ||p_placement_id, 1315);
857: END;
850: EXCEPTION
851: WHEN NO_DATA_FOUND
852: THEN
853: hr_utility.set_location('NO_DATA_FOUND',1315);
854: hr_utility.set_location('max(effective_end_date) ',1315);
855: hr_utility.set_location('not found for p_placement_id '
856: ||p_placement_id, 1315);
857: END;
858: --
851: WHEN NO_DATA_FOUND
852: THEN
853: hr_utility.set_location('NO_DATA_FOUND',1315);
854: hr_utility.set_location('max(effective_end_date) ',1315);
855: hr_utility.set_location('not found for p_placement_id '
856: ||p_placement_id, 1315);
857: END;
858: --
859: if (p_end_date = l_max_end_date)
858: --
859: if (p_end_date = l_max_end_date)
860: and (P_Effective_Date <> p_effective_start_date)
861: then
862: hr_utility.set_location('Datetrack mode = UPDATE',10);
863: l_datetrack_mode := 'UPDATE';
864: elsif
865: (P_Effective_Date = p_effective_start_date)
866: then
863: l_datetrack_mode := 'UPDATE';
864: elsif
865: (P_Effective_Date = p_effective_start_date)
866: then
867: hr_utility.set_location('Datetrack mode = CORRECTION',10);
868: l_datetrack_mode := 'CORRECTION';
869: else
870: hr_utility.set_location('Datetrack mode = UPDATE_CHANGE_INSERT',10);
871: l_datetrack_mode := 'UPDATE_CHANGE_INSERT';
866: then
867: hr_utility.set_location('Datetrack mode = CORRECTION',10);
868: l_datetrack_mode := 'CORRECTION';
869: else
870: hr_utility.set_location('Datetrack mode = UPDATE_CHANGE_INSERT',10);
871: l_datetrack_mode := 'UPDATE_CHANGE_INSERT';
872: end if;
873: --
874: p_datetrack_mode := l_datetrack_mode;
875: --
876: -- ------------------------------------------------------------------
877: -- Get the step_id,sequence number for the future record
878: -- ------------------------------------------------------------------
879: hr_utility.set_location('max_end_date:'||l_max_end_date,170);
880: hr_utility.set_location('L_End_Date:'||p_end_date,170);
881: hr_utility.set_location('p_placement_id - '||p_placement_id,333);
882: if p_end_date <> hr_api.g_eot
883: and p_end_date <> l_max_end_date
876: -- ------------------------------------------------------------------
877: -- Get the step_id,sequence number for the future record
878: -- ------------------------------------------------------------------
879: hr_utility.set_location('max_end_date:'||l_max_end_date,170);
880: hr_utility.set_location('L_End_Date:'||p_end_date,170);
881: hr_utility.set_location('p_placement_id - '||p_placement_id,333);
882: if p_end_date <> hr_api.g_eot
883: and p_end_date <> l_max_end_date
884: then
877: -- Get the step_id,sequence number for the future record
878: -- ------------------------------------------------------------------
879: hr_utility.set_location('max_end_date:'||l_max_end_date,170);
880: hr_utility.set_location('L_End_Date:'||p_end_date,170);
881: hr_utility.set_location('p_placement_id - '||p_placement_id,333);
882: if p_end_date <> hr_api.g_eot
883: and p_end_date <> l_max_end_date
884: then
885: BEGIN
893: and spp.step_id = sps.step_id;
894: EXCEPTION
895: WHEN NO_DATA_FOUND
896: THEN
897: hr_utility.set_location('NO_DATA_FOUND',1316);
898: hr_utility.set_location('sps.grade_spine_id ',1316);
899: hr_utility.set_location('not found for p_placement_id '
900: ||p_placement_id, 1316);
901: END;
894: EXCEPTION
895: WHEN NO_DATA_FOUND
896: THEN
897: hr_utility.set_location('NO_DATA_FOUND',1316);
898: hr_utility.set_location('sps.grade_spine_id ',1316);
899: hr_utility.set_location('not found for p_placement_id '
900: ||p_placement_id, 1316);
901: END;
902: --
895: WHEN NO_DATA_FOUND
896: THEN
897: hr_utility.set_location('NO_DATA_FOUND',1316);
898: hr_utility.set_location('sps.grade_spine_id ',1316);
899: hr_utility.set_location('not found for p_placement_id '
900: ||p_placement_id, 1316);
901: END;
902: --
903: if nvl(l_future_grade_spine_id,-99) <> p_grade_spine_id
912: l_exception_report1 := 'Y';
913: --
914: end if;
915: --
916: hr_utility.set_location('Ceiling fetch',170);
917: --
918: -- ------------------------------------------------------------------
919: -- Get the max ceiling step_id for the pay scale and the special
920: -- ceiling step id for the assignment
934: and paa.effective_end_date;
935: EXCEPTION
936: WHEN NO_DATA_FOUND
937: THEN
938: hr_utility.set_location('NO_DATA_FOUND',1400);
939: hr_utility.set_location('pgs ceiling_step_id ',1400);
940: hr_utility.set_location('not found for p_assignment_id'
941: ||p_assignment_id, 1400);
942: hr_utility.set_location('p_parent_spine_id '||p_parent_spine_id, 1400);
935: EXCEPTION
936: WHEN NO_DATA_FOUND
937: THEN
938: hr_utility.set_location('NO_DATA_FOUND',1400);
939: hr_utility.set_location('pgs ceiling_step_id ',1400);
940: hr_utility.set_location('not found for p_assignment_id'
941: ||p_assignment_id, 1400);
942: hr_utility.set_location('p_parent_spine_id '||p_parent_spine_id, 1400);
943: hr_utility.set_location('p_grade_spine_id '||p_grade_spine_id, 1400);
936: WHEN NO_DATA_FOUND
937: THEN
938: hr_utility.set_location('NO_DATA_FOUND',1400);
939: hr_utility.set_location('pgs ceiling_step_id ',1400);
940: hr_utility.set_location('not found for p_assignment_id'
941: ||p_assignment_id, 1400);
942: hr_utility.set_location('p_parent_spine_id '||p_parent_spine_id, 1400);
943: hr_utility.set_location('p_grade_spine_id '||p_grade_spine_id, 1400);
944: END;
938: hr_utility.set_location('NO_DATA_FOUND',1400);
939: hr_utility.set_location('pgs ceiling_step_id ',1400);
940: hr_utility.set_location('not found for p_assignment_id'
941: ||p_assignment_id, 1400);
942: hr_utility.set_location('p_parent_spine_id '||p_parent_spine_id, 1400);
943: hr_utility.set_location('p_grade_spine_id '||p_grade_spine_id, 1400);
944: END;
945: --
946: hr_utility.set_location('Max step and sequence fetch',170);
939: hr_utility.set_location('pgs ceiling_step_id ',1400);
940: hr_utility.set_location('not found for p_assignment_id'
941: ||p_assignment_id, 1400);
942: hr_utility.set_location('p_parent_spine_id '||p_parent_spine_id, 1400);
943: hr_utility.set_location('p_grade_spine_id '||p_grade_spine_id, 1400);
944: END;
945: --
946: hr_utility.set_location('Max step and sequence fetch',170);
947:
942: hr_utility.set_location('p_parent_spine_id '||p_parent_spine_id, 1400);
943: hr_utility.set_location('p_grade_spine_id '||p_grade_spine_id, 1400);
944: END;
945: --
946: hr_utility.set_location('Max step and sequence fetch',170);
947:
948: if l_max_ceiling_step_id <> l_special_ceiling_step_id then -- 115.42 Update
949:
950: l_max_ceiling_step_id := l_special_ceiling_step_id;
960: and p_effective_date between effective_start_date and effective_end_date;
961: EXCEPTION
962: WHEN NO_DATA_FOUND
963: THEN
964: hr_utility.set_location('NO_DATA_FOUND',1500);
965: hr_utility.set_location('special_ceiling_sequence ',1500);
966: hr_utility.set_location('not found for l_special_ceiling_step_id '
967: ||l_special_ceiling_step_id, 1500);
968: hr_utility.set_location('p_grade_spine_id '||p_grade_spine_id, 1500);
961: EXCEPTION
962: WHEN NO_DATA_FOUND
963: THEN
964: hr_utility.set_location('NO_DATA_FOUND',1500);
965: hr_utility.set_location('special_ceiling_sequence ',1500);
966: hr_utility.set_location('not found for l_special_ceiling_step_id '
967: ||l_special_ceiling_step_id, 1500);
968: hr_utility.set_location('p_grade_spine_id '||p_grade_spine_id, 1500);
969: END;
962: WHEN NO_DATA_FOUND
963: THEN
964: hr_utility.set_location('NO_DATA_FOUND',1500);
965: hr_utility.set_location('special_ceiling_sequence ',1500);
966: hr_utility.set_location('not found for l_special_ceiling_step_id '
967: ||l_special_ceiling_step_id, 1500);
968: hr_utility.set_location('p_grade_spine_id '||p_grade_spine_id, 1500);
969: END;
970: -- ------------------------------------------------------------------
964: hr_utility.set_location('NO_DATA_FOUND',1500);
965: hr_utility.set_location('special_ceiling_sequence ',1500);
966: hr_utility.set_location('not found for l_special_ceiling_step_id '
967: ||l_special_ceiling_step_id, 1500);
968: hr_utility.set_location('p_grade_spine_id '||p_grade_spine_id, 1500);
969: END;
970: -- ------------------------------------------------------------------
971: -- Get the max step_id and seuence for the grade
972: -- ------------------------------------------------------------------
982: between effective_start_date and effective_end_date);
983: EXCEPTION
984: WHEN NO_DATA_FOUND
985: THEN
986: hr_utility.set_location('NO_DATA_FOUND',1500);
987: hr_utility.set_location('sequence and step id',1500);
988: hr_utility.set_location('not found for max grade sequence and ', 1500);
989: hr_utility.set_location('p_grade_spine_id '||p_grade_spine_id, 1500);
990: END;
983: EXCEPTION
984: WHEN NO_DATA_FOUND
985: THEN
986: hr_utility.set_location('NO_DATA_FOUND',1500);
987: hr_utility.set_location('sequence and step id',1500);
988: hr_utility.set_location('not found for max grade sequence and ', 1500);
989: hr_utility.set_location('p_grade_spine_id '||p_grade_spine_id, 1500);
990: END;
991: --
984: WHEN NO_DATA_FOUND
985: THEN
986: hr_utility.set_location('NO_DATA_FOUND',1500);
987: hr_utility.set_location('sequence and step id',1500);
988: hr_utility.set_location('not found for max grade sequence and ', 1500);
989: hr_utility.set_location('p_grade_spine_id '||p_grade_spine_id, 1500);
990: END;
991: --
992: if l_special_ceiling_sequence <> l_grade_max_sequence
985: THEN
986: hr_utility.set_location('NO_DATA_FOUND',1500);
987: hr_utility.set_location('sequence and step id',1500);
988: hr_utility.set_location('not found for max grade sequence and ', 1500);
989: hr_utility.set_location('p_grade_spine_id '||p_grade_spine_id, 1500);
990: END;
991: --
992: if l_special_ceiling_sequence <> l_grade_max_sequence
993: then
994: l_grade_max_step_id := l_special_ceiling_step_id;
995: l_grade_max_sequence := l_special_ceiling_sequence;
996: end if;
997: --
998: hr_utility.set_location(' ***** ANOTHER PROBLEM ***** ',333);
999: hr_utility.set_location('l_grade_max_step_id - '||l_grade_max_step_id,333);
1000: hr_utility.set_location('l_grade_max_sequence - '||l_grade_max_sequence,333);
1001: hr_utility.set_location('l_special_ceiling_step_id - '||l_special_ceiling_step_id,333);
1002: hr_utility.set_location('l_special_ceiling_sequence - '||l_special_ceiling_sequence,333);
995: l_grade_max_sequence := l_special_ceiling_sequence;
996: end if;
997: --
998: hr_utility.set_location(' ***** ANOTHER PROBLEM ***** ',333);
999: hr_utility.set_location('l_grade_max_step_id - '||l_grade_max_step_id,333);
1000: hr_utility.set_location('l_grade_max_sequence - '||l_grade_max_sequence,333);
1001: hr_utility.set_location('l_special_ceiling_step_id - '||l_special_ceiling_step_id,333);
1002: hr_utility.set_location('l_special_ceiling_sequence - '||l_special_ceiling_sequence,333);
1003: hr_utility.set_location('l_future_sequence_id - '||l_future_sequence_id,333);
996: end if;
997: --
998: hr_utility.set_location(' ***** ANOTHER PROBLEM ***** ',333);
999: hr_utility.set_location('l_grade_max_step_id - '||l_grade_max_step_id,333);
1000: hr_utility.set_location('l_grade_max_sequence - '||l_grade_max_sequence,333);
1001: hr_utility.set_location('l_special_ceiling_step_id - '||l_special_ceiling_step_id,333);
1002: hr_utility.set_location('l_special_ceiling_sequence - '||l_special_ceiling_sequence,333);
1003: hr_utility.set_location('l_future_sequence_id - '||l_future_sequence_id,333);
1004: --
997: --
998: hr_utility.set_location(' ***** ANOTHER PROBLEM ***** ',333);
999: hr_utility.set_location('l_grade_max_step_id - '||l_grade_max_step_id,333);
1000: hr_utility.set_location('l_grade_max_sequence - '||l_grade_max_sequence,333);
1001: hr_utility.set_location('l_special_ceiling_step_id - '||l_special_ceiling_step_id,333);
1002: hr_utility.set_location('l_special_ceiling_sequence - '||l_special_ceiling_sequence,333);
1003: hr_utility.set_location('l_future_sequence_id - '||l_future_sequence_id,333);
1004: --
1005: if l_future_sequence_id = -99 or l_future_sequence_id is null
998: hr_utility.set_location(' ***** ANOTHER PROBLEM ***** ',333);
999: hr_utility.set_location('l_grade_max_step_id - '||l_grade_max_step_id,333);
1000: hr_utility.set_location('l_grade_max_sequence - '||l_grade_max_sequence,333);
1001: hr_utility.set_location('l_special_ceiling_step_id - '||l_special_ceiling_step_id,333);
1002: hr_utility.set_location('l_special_ceiling_sequence - '||l_special_ceiling_sequence,333);
1003: hr_utility.set_location('l_future_sequence_id - '||l_future_sequence_id,333);
1004: --
1005: if l_future_sequence_id = -99 or l_future_sequence_id is null
1006: then
999: hr_utility.set_location('l_grade_max_step_id - '||l_grade_max_step_id,333);
1000: hr_utility.set_location('l_grade_max_sequence - '||l_grade_max_sequence,333);
1001: hr_utility.set_location('l_special_ceiling_step_id - '||l_special_ceiling_step_id,333);
1002: hr_utility.set_location('l_special_ceiling_sequence - '||l_special_ceiling_sequence,333);
1003: hr_utility.set_location('l_future_sequence_id - '||l_future_sequence_id,333);
1004: --
1005: if l_future_sequence_id = -99 or l_future_sequence_id is null
1006: then
1007: l_future_sequence_id := l_grade_max_sequence;
1006: then
1007: l_future_sequence_id := l_grade_max_sequence;
1008: end if;
1009: --
1010: hr_utility.set_location('l_grade_max_step_id:'||l_grade_max_step_id,200);
1011: hr_utility.set_location('l_grade_max_sequence:'||l_grade_max_sequence,200);
1012: --
1013: -- ------------------------------------------------------------------
1014: -- Check if assignment already at a grade limit
1007: l_future_sequence_id := l_grade_max_sequence;
1008: end if;
1009: --
1010: hr_utility.set_location('l_grade_max_step_id:'||l_grade_max_step_id,200);
1011: hr_utility.set_location('l_grade_max_sequence:'||l_grade_max_sequence,200);
1012: --
1013: -- ------------------------------------------------------------------
1014: -- Check if assignment already at a grade limit
1015: -- ------------------------------------------------------------------
1012: --
1013: -- ------------------------------------------------------------------
1014: -- Check if assignment already at a grade limit
1015: -- ------------------------------------------------------------------
1016: hr_utility.set_location('Ceiling Reached?',201);
1017: hr_utility.set_location('Step ID '||p_step_id,201);
1018: hr_utility.set_location('l_special_ceiling_step_id '||l_special_ceiling_step_id,201);
1019: hr_utility.set_location('max_ceiling_step_id '||l_max_ceiling_step_id,201);
1020: --
1013: -- ------------------------------------------------------------------
1014: -- Check if assignment already at a grade limit
1015: -- ------------------------------------------------------------------
1016: hr_utility.set_location('Ceiling Reached?',201);
1017: hr_utility.set_location('Step ID '||p_step_id,201);
1018: hr_utility.set_location('l_special_ceiling_step_id '||l_special_ceiling_step_id,201);
1019: hr_utility.set_location('max_ceiling_step_id '||l_max_ceiling_step_id,201);
1020: --
1021: if (l_special_ceiling_step_id = p_step_id
1014: -- Check if assignment already at a grade limit
1015: -- ------------------------------------------------------------------
1016: hr_utility.set_location('Ceiling Reached?',201);
1017: hr_utility.set_location('Step ID '||p_step_id,201);
1018: hr_utility.set_location('l_special_ceiling_step_id '||l_special_ceiling_step_id,201);
1019: hr_utility.set_location('max_ceiling_step_id '||l_max_ceiling_step_id,201);
1020: --
1021: if (l_special_ceiling_step_id = p_step_id
1022: or l_grade_max_step_id = p_step_id)
1015: -- ------------------------------------------------------------------
1016: hr_utility.set_location('Ceiling Reached?',201);
1017: hr_utility.set_location('Step ID '||p_step_id,201);
1018: hr_utility.set_location('l_special_ceiling_step_id '||l_special_ceiling_step_id,201);
1019: hr_utility.set_location('max_ceiling_step_id '||l_max_ceiling_step_id,201);
1020: --
1021: if (l_special_ceiling_step_id = p_step_id
1022: or l_grade_max_step_id = p_step_id)
1023: then
1024: l_update := 'N';
1025: l_exception_report1 := 'Y';
1026: end if;
1027: --
1028: hr_utility.set_location('l_update '||l_update,201);
1029: --
1030: -- ------------------------------------------------------------------
1031: -- Get the sequence number for the two ceiling step id's along with
1032: -- the spinal_point_id for the max ceiling
1030: -- ------------------------------------------------------------------
1031: -- Get the sequence number for the two ceiling step id's along with
1032: -- the spinal_point_id for the max ceiling
1033: -- ------------------------------------------------------------------
1034: hr_utility.set_location('sequence number for the two ceiling',202);
1035: hr_utility.set_location(l_max_ceiling_step_id,203);
1036: hr_utility.set_location(l_special_ceiling_step_id,204);
1037: BEGIN
1038: select psp1.sequence,
1031: -- Get the sequence number for the two ceiling step id's along with
1032: -- the spinal_point_id for the max ceiling
1033: -- ------------------------------------------------------------------
1034: hr_utility.set_location('sequence number for the two ceiling',202);
1035: hr_utility.set_location(l_max_ceiling_step_id,203);
1036: hr_utility.set_location(l_special_ceiling_step_id,204);
1037: BEGIN
1038: select psp1.sequence,
1039: nvl(psp2.sequence,99999999),
1032: -- the spinal_point_id for the max ceiling
1033: -- ------------------------------------------------------------------
1034: hr_utility.set_location('sequence number for the two ceiling',202);
1035: hr_utility.set_location(l_max_ceiling_step_id,203);
1036: hr_utility.set_location(l_special_ceiling_step_id,204);
1037: BEGIN
1038: select psp1.sequence,
1039: nvl(psp2.sequence,99999999),
1040: psp1.spinal_point_id,
1053: and sps2.step_id = l_special_ceiling_step_id;
1054: EXCEPTION
1055: WHEN NO_DATA_FOUND
1056: THEN
1057: hr_utility.set_location('NO_DATA_FOUND',1600);
1058: hr_utility.set_location('sequence and point ids ',1600);
1059: hr_utility.set_location('not found for l_max_ceiling_step_id '
1060: ||l_max_ceiling_step_id, 1600);
1061: hr_utility.set_location('and l_special_ceiling_step_id '
1054: EXCEPTION
1055: WHEN NO_DATA_FOUND
1056: THEN
1057: hr_utility.set_location('NO_DATA_FOUND',1600);
1058: hr_utility.set_location('sequence and point ids ',1600);
1059: hr_utility.set_location('not found for l_max_ceiling_step_id '
1060: ||l_max_ceiling_step_id, 1600);
1061: hr_utility.set_location('and l_special_ceiling_step_id '
1062: ||l_special_ceiling_step_id, 1600);
1055: WHEN NO_DATA_FOUND
1056: THEN
1057: hr_utility.set_location('NO_DATA_FOUND',1600);
1058: hr_utility.set_location('sequence and point ids ',1600);
1059: hr_utility.set_location('not found for l_max_ceiling_step_id '
1060: ||l_max_ceiling_step_id, 1600);
1061: hr_utility.set_location('and l_special_ceiling_step_id '
1062: ||l_special_ceiling_step_id, 1600);
1063: END;
1057: hr_utility.set_location('NO_DATA_FOUND',1600);
1058: hr_utility.set_location('sequence and point ids ',1600);
1059: hr_utility.set_location('not found for l_max_ceiling_step_id '
1060: ||l_max_ceiling_step_id, 1600);
1061: hr_utility.set_location('and l_special_ceiling_step_id '
1062: ||l_special_ceiling_step_id, 1600);
1063: END;
1064: --
1065: -- ------------------------------------------------------------------
1066: -- Select the max number of steps left for the grade - Also compare
1067: -- the two limits and stop if reached
1068: -- ------------------------------------------------------------------
1069: --
1070: hr_utility.set_location('******* CEILING ERROR CATCH *********',333);
1071: hr_utility.set_location('l_max_sequence_number - '||l_max_sequence_number,333);
1072: hr_utility.set_location('l_max_special_sequence_number - '||l_max_special_sequence_number,333);
1073: hr_utility.set_location('l_grade_max_sequence - '||l_grade_max_sequence,333);
1074: hr_utility.set_location('l_future_sequence_id - '||l_future_sequence_id,333);
1067: -- the two limits and stop if reached
1068: -- ------------------------------------------------------------------
1069: --
1070: hr_utility.set_location('******* CEILING ERROR CATCH *********',333);
1071: hr_utility.set_location('l_max_sequence_number - '||l_max_sequence_number,333);
1072: hr_utility.set_location('l_max_special_sequence_number - '||l_max_special_sequence_number,333);
1073: hr_utility.set_location('l_grade_max_sequence - '||l_grade_max_sequence,333);
1074: hr_utility.set_location('l_future_sequence_id - '||l_future_sequence_id,333);
1075: hr_utility.set_location('p_sequence_number - '||p_sequence_number,333);
1068: -- ------------------------------------------------------------------
1069: --
1070: hr_utility.set_location('******* CEILING ERROR CATCH *********',333);
1071: hr_utility.set_location('l_max_sequence_number - '||l_max_sequence_number,333);
1072: hr_utility.set_location('l_max_special_sequence_number - '||l_max_special_sequence_number,333);
1073: hr_utility.set_location('l_grade_max_sequence - '||l_grade_max_sequence,333);
1074: hr_utility.set_location('l_future_sequence_id - '||l_future_sequence_id,333);
1075: hr_utility.set_location('p_sequence_number - '||p_sequence_number,333);
1076: BEGIN
1069: --
1070: hr_utility.set_location('******* CEILING ERROR CATCH *********',333);
1071: hr_utility.set_location('l_max_sequence_number - '||l_max_sequence_number,333);
1072: hr_utility.set_location('l_max_special_sequence_number - '||l_max_special_sequence_number,333);
1073: hr_utility.set_location('l_grade_max_sequence - '||l_grade_max_sequence,333);
1074: hr_utility.set_location('l_future_sequence_id - '||l_future_sequence_id,333);
1075: hr_utility.set_location('p_sequence_number - '||p_sequence_number,333);
1076: BEGIN
1077: select count(sps.sequence)
1070: hr_utility.set_location('******* CEILING ERROR CATCH *********',333);
1071: hr_utility.set_location('l_max_sequence_number - '||l_max_sequence_number,333);
1072: hr_utility.set_location('l_max_special_sequence_number - '||l_max_special_sequence_number,333);
1073: hr_utility.set_location('l_grade_max_sequence - '||l_grade_max_sequence,333);
1074: hr_utility.set_location('l_future_sequence_id - '||l_future_sequence_id,333);
1075: hr_utility.set_location('p_sequence_number - '||p_sequence_number,333);
1076: BEGIN
1077: select count(sps.sequence)
1078: into l_max_count
1071: hr_utility.set_location('l_max_sequence_number - '||l_max_sequence_number,333);
1072: hr_utility.set_location('l_max_special_sequence_number - '||l_max_special_sequence_number,333);
1073: hr_utility.set_location('l_grade_max_sequence - '||l_grade_max_sequence,333);
1074: hr_utility.set_location('l_future_sequence_id - '||l_future_sequence_id,333);
1075: hr_utility.set_location('p_sequence_number - '||p_sequence_number,333);
1076: BEGIN
1077: select count(sps.sequence)
1078: into l_max_count
1079: from per_spinal_point_steps_f sps
1088: order by sps.sequence;
1089: EXCEPTION
1090: WHEN NO_DATA_FOUND
1091: THEN
1092: hr_utility.set_location('NO_DATA_FOUND',1700);
1093: hr_utility.set_location('max spinal point count ',1700);
1094: hr_utility.set_location('not found for p_grade_spine_id '
1095: ||p_grade_spine_id, 1700);
1096: hr_utility.set_location('and > p_sequence_number '
1089: EXCEPTION
1090: WHEN NO_DATA_FOUND
1091: THEN
1092: hr_utility.set_location('NO_DATA_FOUND',1700);
1093: hr_utility.set_location('max spinal point count ',1700);
1094: hr_utility.set_location('not found for p_grade_spine_id '
1095: ||p_grade_spine_id, 1700);
1096: hr_utility.set_location('and > p_sequence_number '
1097: ||p_sequence_number, 1700);
1090: WHEN NO_DATA_FOUND
1091: THEN
1092: hr_utility.set_location('NO_DATA_FOUND',1700);
1093: hr_utility.set_location('max spinal point count ',1700);
1094: hr_utility.set_location('not found for p_grade_spine_id '
1095: ||p_grade_spine_id, 1700);
1096: hr_utility.set_location('and > p_sequence_number '
1097: ||p_sequence_number, 1700);
1098: hr_utility.set_location('l_max_sequence_number '
1092: hr_utility.set_location('NO_DATA_FOUND',1700);
1093: hr_utility.set_location('max spinal point count ',1700);
1094: hr_utility.set_location('not found for p_grade_spine_id '
1095: ||p_grade_spine_id, 1700);
1096: hr_utility.set_location('and > p_sequence_number '
1097: ||p_sequence_number, 1700);
1098: hr_utility.set_location('l_max_sequence_number '
1099: ||l_max_sequence_number, 1700);
1100: hr_utility.set_location('l_max_special_sequence_number '
1094: hr_utility.set_location('not found for p_grade_spine_id '
1095: ||p_grade_spine_id, 1700);
1096: hr_utility.set_location('and > p_sequence_number '
1097: ||p_sequence_number, 1700);
1098: hr_utility.set_location('l_max_sequence_number '
1099: ||l_max_sequence_number, 1700);
1100: hr_utility.set_location('l_max_special_sequence_number '
1101: ||l_max_special_sequence_number, 1700);
1102: hr_utility.set_location('l_future_sequence_id '
1096: hr_utility.set_location('and > p_sequence_number '
1097: ||p_sequence_number, 1700);
1098: hr_utility.set_location('l_max_sequence_number '
1099: ||l_max_sequence_number, 1700);
1100: hr_utility.set_location('l_max_special_sequence_number '
1101: ||l_max_special_sequence_number, 1700);
1102: hr_utility.set_location('l_future_sequence_id '
1103: ||l_future_sequence_id, 1700);
1104: hr_utility.set_location('l_grade_max_sequence '
1098: hr_utility.set_location('l_max_sequence_number '
1099: ||l_max_sequence_number, 1700);
1100: hr_utility.set_location('l_max_special_sequence_number '
1101: ||l_max_special_sequence_number, 1700);
1102: hr_utility.set_location('l_future_sequence_id '
1103: ||l_future_sequence_id, 1700);
1104: hr_utility.set_location('l_grade_max_sequence '
1105: ||l_grade_max_sequence, 1700);
1106: END;
1100: hr_utility.set_location('l_max_special_sequence_number '
1101: ||l_max_special_sequence_number, 1700);
1102: hr_utility.set_location('l_future_sequence_id '
1103: ||l_future_sequence_id, 1700);
1104: hr_utility.set_location('l_grade_max_sequence '
1105: ||l_grade_max_sequence, 1700);
1106: END;
1107: hr_utility.set_location ('hrspine.spine', 1222);
1108: hr_utility.set_location ('count(sps.sequence):'||L_Max_Count,12222);
1103: ||l_future_sequence_id, 1700);
1104: hr_utility.set_location('l_grade_max_sequence '
1105: ||l_grade_max_sequence, 1700);
1106: END;
1107: hr_utility.set_location ('hrspine.spine', 1222);
1108: hr_utility.set_location ('count(sps.sequence):'||L_Max_Count,12222);
1109: -- ------------------------------------------------------------------
1110: -- Checks the increment by number is not greater than
1111: -- the max number of steps left for the grade
1104: hr_utility.set_location('l_grade_max_sequence '
1105: ||l_grade_max_sequence, 1700);
1106: END;
1107: hr_utility.set_location ('hrspine.spine', 1222);
1108: hr_utility.set_location ('count(sps.sequence):'||L_Max_Count,12222);
1109: -- ------------------------------------------------------------------
1110: -- Checks the increment by number is not greater than
1111: -- the max number of steps left for the grade
1112: -- ------------------------------------------------------------------
1110: -- Checks the increment by number is not greater than
1111: -- the max number of steps left for the grade
1112: -- ------------------------------------------------------------------
1113: --
1114: hr_utility.set_location('Assign Value to l_increment',140);
1115: --
1116: l_increment := p_increment_number;
1117: --
1118: hr_utility.set_location('Value = '||l_increment,140);
1114: hr_utility.set_location('Assign Value to l_increment',140);
1115: --
1116: l_increment := p_increment_number;
1117: --
1118: hr_utility.set_location('Value = '||l_increment,140);
1119: hr_utility.set_location('Max Count Value = '||L_Max_Count,141);
1120: --
1121: if l_increment > L_Max_Count
1122: then
1115: --
1116: l_increment := p_increment_number;
1117: --
1118: hr_utility.set_location('Value = '||l_increment,140);
1119: hr_utility.set_location('Max Count Value = '||L_Max_Count,141);
1120: --
1121: if l_increment > L_Max_Count
1122: then
1123: l_increment := L_Max_Count;
1125: end if;
1126: --
1127: if L_Max_Count = 0
1128: then
1129: hr_utility.set_location('L_Max_Count = 0 so update denied',141);
1130: l_update := 'N';
1131: end if;
1132: --
1133: p_increment := l_increment;
1137: l_max_special_sequence_number := p_max_special_sequence_number;
1138: l_max_spinal_point_id := p_max_spinal_point_id;
1139: l_max_special_spinal_point_id := p_max_special_spinal_point_id;
1140: --
1141: hr_utility.set_location('Exiting check_increment_available procedure',15);
1142: --
1143: end check_increment_available;
1144: --
1145: /* -------------------------------------------------------------------- */
1192: or p_org_structure_ver_id is null
1193: then
1194: -- Raise error
1195: fnd_message.set_name('PER', 'HR_289280_SPP_INC_ORG_STRUCT');
1196: hr_utility.raise_error;
1197: end if;
1198: end if;
1199: --
1200: -- Check qualifications - Either both parameters have to be passed
1205: if p_qual_type is null
1206: then
1207: -- Raise error
1208: fnd_message.set_name('PER', 'HR_289290_SPP_INC_QUAL');
1209: hr_utility.raise_error;
1210: end if;
1211: end if;
1212: --
1213: --
1218: open csr_legal_entity;
1219: fetch csr_legal_entity into l_dummy;
1220: if csr_legal_entity%notfound then
1221: fnd_message.set_name('PER', 'HR_289291_SPP_INC_LEGAL_ENTITY');
1222: hr_utility.raise_error;
1223: end if;
1224: close csr_legal_entity;
1225: end if;
1226: --
1814: ,p_year_from => p_year_from
1815: ,p_dependant_date => p_dependant_date
1816: ,p_br_flag => l_br_flag);
1817:
1818: -- hr_utility.set_location('HRSPINE - Business Rule = '||l_br_flag,55);
1819:
1820: --
1821: -- Check that all the constraints are correct
1822: --
1827: ,p_qual_type => p_qual_type
1828: ,p_business_group_id => p_business_group_id
1829: ,p_legal_entity => p_legal_entity);
1830:
1831: hr_utility.trace ('concat string = ' || p_concat_segs);
1832: --
1833: if (p_concat_segs is not null)
1834: then
1835: hr_utility.set_location ('hrspine.spine', 2);
1831: hr_utility.trace ('concat string = ' || p_concat_segs);
1832: --
1833: if (p_concat_segs is not null)
1834: then
1835: hr_utility.set_location ('hrspine.spine', 2);
1836: --
1837: BEGIN
1838: select concatenated_segment_delimiter
1839: into l_concat_sep
1843: and id_flex_code = 'GRP';
1844: EXCEPTION
1845: WHEN NO_DATA_FOUND
1846: THEN
1847: hr_utility.set_location('NO_DATA_FOUND',1900);
1848: hr_utility.set_location('concatenated_segment_delimiter ',1900);
1849: hr_utility.set_location('not found for p_id_flex_num '
1850: ||p_id_flex_num, 1900);
1851: END;
1844: EXCEPTION
1845: WHEN NO_DATA_FOUND
1846: THEN
1847: hr_utility.set_location('NO_DATA_FOUND',1900);
1848: hr_utility.set_location('concatenated_segment_delimiter ',1900);
1849: hr_utility.set_location('not found for p_id_flex_num '
1850: ||p_id_flex_num, 1900);
1851: END;
1852: --
1845: WHEN NO_DATA_FOUND
1846: THEN
1847: hr_utility.set_location('NO_DATA_FOUND',1900);
1848: hr_utility.set_location('concatenated_segment_delimiter ',1900);
1849: hr_utility.set_location('not found for p_id_flex_num '
1850: ||p_id_flex_num, 1900);
1851: END;
1852: --
1853: -- loop through for each segment, and re-arrange into the correct
1863: l_length := l_pos_sep - l_pos;
1864: l_seg_value := substr (p_concat_segs, l_pos, l_length);
1865: end if;
1866: l_pos := l_pos + l_length + 1; -- skip on to next segment
1867: hr_utility.trace ((c1rec.application_column_name || ' = ')
1868: || l_seg_value);
1869: if (c1rec.application_column_name = 'SEGMENT1') then
1870: l_seg1 := l_seg_value;
1871: elsif (c1rec.application_column_name = 'SEGMENT2') then
1926: l_seg29 := l_seg_value;
1927: elsif (c1rec.application_column_name = 'SEGMENT30') then
1928: l_seg30 := l_seg_value;
1929: else
1930: hr_utility.trace ('ERROR : unknown application column name');
1931: end if;
1932: end loop;
1933: hr_utility.set_location ('hrspine.spine', 4);
1934: hr_utility.set_location('Seg 1 - '||l_seg1,4);
1929: else
1930: hr_utility.trace ('ERROR : unknown application column name');
1931: end if;
1932: end loop;
1933: hr_utility.set_location ('hrspine.spine', 4);
1934: hr_utility.set_location('Seg 1 - '||l_seg1,4);
1935: hr_utility.set_location('Seg 15 - '||l_seg15,4);
1936: end if;
1937: --
1930: hr_utility.trace ('ERROR : unknown application column name');
1931: end if;
1932: end loop;
1933: hr_utility.set_location ('hrspine.spine', 4);
1934: hr_utility.set_location('Seg 1 - '||l_seg1,4);
1935: hr_utility.set_location('Seg 15 - '||l_seg15,4);
1936: end if;
1937: --
1938: -- Set the count to see if any data is updated
1931: end if;
1932: end loop;
1933: hr_utility.set_location ('hrspine.spine', 4);
1934: hr_utility.set_location('Seg 1 - '||l_seg1,4);
1935: hr_utility.set_location('Seg 15 - '||l_seg15,4);
1936: end if;
1937: --
1938: -- Set the count to see if any data is updated
1939: --
1959: --
1960: -- fetch standard cursor, no group partial matching
1961: --
1962: /*** vik ***/
1963: hr_utility.set_location ('hrspine.spine', 51);
1964: FETCH C_Spinal_Placements_Cursor
1965: INTO LC_Placement_ID,
1966: LC_Step_ID,
1967: LC_Assignment_ID,
1976: l_grade_spine_id;
1977: -- vik
1978: if (C_Spinal_Placements_Cursor%notfound)
1979: then
1980: hr_utility.set_location ('hrspine.spine', 69);
1981: hr_utility.set_location ('no data found', 69);
1982: hr_utility.set_location ('in C_Spinal_Placements_Cursor',69);
1983: hr_utility.set_location ('for'||TO_CHAR(LC_Assignment_ID),69);
1984: hr_utility.set_location ('and for'||TO_CHAR(LC_Placement_ID),69);
1977: -- vik
1978: if (C_Spinal_Placements_Cursor%notfound)
1979: then
1980: hr_utility.set_location ('hrspine.spine', 69);
1981: hr_utility.set_location ('no data found', 69);
1982: hr_utility.set_location ('in C_Spinal_Placements_Cursor',69);
1983: hr_utility.set_location ('for'||TO_CHAR(LC_Assignment_ID),69);
1984: hr_utility.set_location ('and for'||TO_CHAR(LC_Placement_ID),69);
1985: hr_utility.set_location ('for'|| P_Parent_Spine_ID,69);
1978: if (C_Spinal_Placements_Cursor%notfound)
1979: then
1980: hr_utility.set_location ('hrspine.spine', 69);
1981: hr_utility.set_location ('no data found', 69);
1982: hr_utility.set_location ('in C_Spinal_Placements_Cursor',69);
1983: hr_utility.set_location ('for'||TO_CHAR(LC_Assignment_ID),69);
1984: hr_utility.set_location ('and for'||TO_CHAR(LC_Placement_ID),69);
1985: hr_utility.set_location ('for'|| P_Parent_Spine_ID,69);
1986: else
1979: then
1980: hr_utility.set_location ('hrspine.spine', 69);
1981: hr_utility.set_location ('no data found', 69);
1982: hr_utility.set_location ('in C_Spinal_Placements_Cursor',69);
1983: hr_utility.set_location ('for'||TO_CHAR(LC_Assignment_ID),69);
1984: hr_utility.set_location ('and for'||TO_CHAR(LC_Placement_ID),69);
1985: hr_utility.set_location ('for'|| P_Parent_Spine_ID,69);
1986: else
1987: hr_utility.set_location ('hrspine.spine', 79);
1980: hr_utility.set_location ('hrspine.spine', 69);
1981: hr_utility.set_location ('no data found', 69);
1982: hr_utility.set_location ('in C_Spinal_Placements_Cursor',69);
1983: hr_utility.set_location ('for'||TO_CHAR(LC_Assignment_ID),69);
1984: hr_utility.set_location ('and for'||TO_CHAR(LC_Placement_ID),69);
1985: hr_utility.set_location ('for'|| P_Parent_Spine_ID,69);
1986: else
1987: hr_utility.set_location ('hrspine.spine', 79);
1988: hr_utility.set_location ('C_Spinal_Placements_Cur success',79);
1981: hr_utility.set_location ('no data found', 69);
1982: hr_utility.set_location ('in C_Spinal_Placements_Cursor',69);
1983: hr_utility.set_location ('for'||TO_CHAR(LC_Assignment_ID),69);
1984: hr_utility.set_location ('and for'||TO_CHAR(LC_Placement_ID),69);
1985: hr_utility.set_location ('for'|| P_Parent_Spine_ID,69);
1986: else
1987: hr_utility.set_location ('hrspine.spine', 79);
1988: hr_utility.set_location ('C_Spinal_Placements_Cur success',79);
1989: hr_utility.set_location ('for '||TO_CHAR(LC_Assignment_ID),79);
1983: hr_utility.set_location ('for'||TO_CHAR(LC_Assignment_ID),69);
1984: hr_utility.set_location ('and for'||TO_CHAR(LC_Placement_ID),69);
1985: hr_utility.set_location ('for'|| P_Parent_Spine_ID,69);
1986: else
1987: hr_utility.set_location ('hrspine.spine', 79);
1988: hr_utility.set_location ('C_Spinal_Placements_Cur success',79);
1989: hr_utility.set_location ('for '||TO_CHAR(LC_Assignment_ID),79);
1990: hr_utility.set_location ('and for '||TO_CHAR(LC_Placement_ID),79);
1991: hr_utility.set_location ('for'|| P_Parent_Spine_ID,79);
1984: hr_utility.set_location ('and for'||TO_CHAR(LC_Placement_ID),69);
1985: hr_utility.set_location ('for'|| P_Parent_Spine_ID,69);
1986: else
1987: hr_utility.set_location ('hrspine.spine', 79);
1988: hr_utility.set_location ('C_Spinal_Placements_Cur success',79);
1989: hr_utility.set_location ('for '||TO_CHAR(LC_Assignment_ID),79);
1990: hr_utility.set_location ('and for '||TO_CHAR(LC_Placement_ID),79);
1991: hr_utility.set_location ('for'|| P_Parent_Spine_ID,79);
1992: end if;
1985: hr_utility.set_location ('for'|| P_Parent_Spine_ID,69);
1986: else
1987: hr_utility.set_location ('hrspine.spine', 79);
1988: hr_utility.set_location ('C_Spinal_Placements_Cur success',79);
1989: hr_utility.set_location ('for '||TO_CHAR(LC_Assignment_ID),79);
1990: hr_utility.set_location ('and for '||TO_CHAR(LC_Placement_ID),79);
1991: hr_utility.set_location ('for'|| P_Parent_Spine_ID,79);
1992: end if;
1993: --
1986: else
1987: hr_utility.set_location ('hrspine.spine', 79);
1988: hr_utility.set_location ('C_Spinal_Placements_Cur success',79);
1989: hr_utility.set_location ('for '||TO_CHAR(LC_Assignment_ID),79);
1990: hr_utility.set_location ('and for '||TO_CHAR(LC_Placement_ID),79);
1991: hr_utility.set_location ('for'|| P_Parent_Spine_ID,79);
1992: end if;
1993: --
1994: l_message_number_count := l_message_number_count + 1;
1987: hr_utility.set_location ('hrspine.spine', 79);
1988: hr_utility.set_location ('C_Spinal_Placements_Cur success',79);
1989: hr_utility.set_location ('for '||TO_CHAR(LC_Assignment_ID),79);
1990: hr_utility.set_location ('and for '||TO_CHAR(LC_Placement_ID),79);
1991: hr_utility.set_location ('for'|| P_Parent_Spine_ID,79);
1992: end if;
1993: --
1994: l_message_number_count := l_message_number_count + 1;
1995: --
1998: else
1999: --
2000: -- fetch key cursor, with group partial matching
2001: --
2002: hr_utility.set_location ('hrspine.spine', 6);
2003: FETCH C_Key_Spinal_Placements_Cursor
2004: INTO LC_Placement_ID,
2005: LC_Step_ID,
2006: LC_Assignment_ID,
2083: -- ================================================================================
2084:
2085: if to_date(to_char(p_br_date_to,'YYYY'),'YYYY') > to_date(to_char(p_br_date_from,'YYYY'),'YYYY') then
2086:
2087: hr_utility.set_location('Setting new Business Rule dates',1);
2088:
2089: l_year_from_temp := substr(to_char(p_br_date_from,'DD-MM-RRRR'),6,5); -- Year to
2090: hr_utility.set_location(l_year_from_temp,1);
2091:
2086:
2087: hr_utility.set_location('Setting new Business Rule dates',1);
2088:
2089: l_year_from_temp := substr(to_char(p_br_date_from,'DD-MM-RRRR'),6,5); -- Year to
2090: hr_utility.set_location(l_year_from_temp,1);
2091:
2092: l_br_date_to_temp1 := '31-12'||l_year_from_temp;
2093: hr_utility.set_location(l_br_date_to_temp1,1);
2094:
2089: l_year_from_temp := substr(to_char(p_br_date_from,'DD-MM-RRRR'),6,5); -- Year to
2090: hr_utility.set_location(l_year_from_temp,1);
2091:
2092: l_br_date_to_temp1 := '31-12'||l_year_from_temp;
2093: hr_utility.set_location(l_br_date_to_temp1,1);
2094:
2095: l_year_to_temp := substr(to_char(p_br_date_to,'DD-MM-RRRR'),6,5); -- Year from
2096: hr_utility.set_location(l_year_to_temp,2);
2097:
2092: l_br_date_to_temp1 := '31-12'||l_year_from_temp;
2093: hr_utility.set_location(l_br_date_to_temp1,1);
2094:
2095: l_year_to_temp := substr(to_char(p_br_date_to,'DD-MM-RRRR'),6,5); -- Year from
2096: hr_utility.set_location(l_year_to_temp,2);
2097:
2098: l_br_date_from_temp1 := '01-01'||l_year_to_temp;
2099: hr_utility.set_location(l_br_date_from_temp1,2);
2100:
2095: l_year_to_temp := substr(to_char(p_br_date_to,'DD-MM-RRRR'),6,5); -- Year from
2096: hr_utility.set_location(l_year_to_temp,2);
2097:
2098: l_br_date_from_temp1 := '01-01'||l_year_to_temp;
2099: hr_utility.set_location(l_br_date_from_temp1,2);
2100:
2101: l_year_loop := 'Y';
2102:
2103: else
2114: -- for the end of the first year and then again for the start of the second year
2115:
2116: if l_year_loop = 'Y' and l_year_loop_count = 1 then
2117:
2118: hr_utility.set_location('== p_br_date_from == '||p_br_date_from,3);
2119: hr_utility.set_location('== l_br_date_to_temp1 == '||l_br_date_to_temp1,3);
2120: l_br_date_from := to_date(to_char(p_br_date_from,'DD-MM-RRRR'),'DD-MM-RRRR');
2121: l_br_date_to := to_date(l_br_date_to_temp1,'DD-MM-RRRR');
2122: l_year_temp := l_year_from_temp;
2115:
2116: if l_year_loop = 'Y' and l_year_loop_count = 1 then
2117:
2118: hr_utility.set_location('== p_br_date_from == '||p_br_date_from,3);
2119: hr_utility.set_location('== l_br_date_to_temp1 == '||l_br_date_to_temp1,3);
2120: l_br_date_from := to_date(to_char(p_br_date_from,'DD-MM-RRRR'),'DD-MM-RRRR');
2121: l_br_date_to := to_date(l_br_date_to_temp1,'DD-MM-RRRR');
2122: l_year_temp := l_year_from_temp;
2123:
2135:
2136: end if;
2137:
2138:
2139: hr_utility.set_location('Business Rule Set +++++++++++',10);
2140:
2141: open csr_assignment_business_rule;
2142:
2143: LOOP
2145: -- ================================================================================
2146: -- First find all the people who have a grade step placement for the business group
2147: -- ================================================================================
2148:
2149: hr_utility.set_location('p_business_rule - '||p_business_rule,10);
2150: hr_utility.set_location('p_br_date_from - '||l_br_date_from,10);
2151: hr_utility.set_location('p_br_date_to - '||l_br_date_to,10);
2152: hr_utility.set_location('p_year_from - '||p_year_from,10);
2153: hr_utility.set_location('p_year_to - '||p_year_to,10);
2146: -- First find all the people who have a grade step placement for the business group
2147: -- ================================================================================
2148:
2149: hr_utility.set_location('p_business_rule - '||p_business_rule,10);
2150: hr_utility.set_location('p_br_date_from - '||l_br_date_from,10);
2151: hr_utility.set_location('p_br_date_to - '||l_br_date_to,10);
2152: hr_utility.set_location('p_year_from - '||p_year_from,10);
2153: hr_utility.set_location('p_year_to - '||p_year_to,10);
2154: hr_utility.set_location('l_year_temp - '||l_year_temp,10);
2147: -- ================================================================================
2148:
2149: hr_utility.set_location('p_business_rule - '||p_business_rule,10);
2150: hr_utility.set_location('p_br_date_from - '||l_br_date_from,10);
2151: hr_utility.set_location('p_br_date_to - '||l_br_date_to,10);
2152: hr_utility.set_location('p_year_from - '||p_year_from,10);
2153: hr_utility.set_location('p_year_to - '||p_year_to,10);
2154: hr_utility.set_location('l_year_temp - '||l_year_temp,10);
2155: hr_utility.set_location('l_earliest_start_date - '||to_char(l_earliest_start_date,'DD-MM-YYYY'),10);
2148:
2149: hr_utility.set_location('p_business_rule - '||p_business_rule,10);
2150: hr_utility.set_location('p_br_date_from - '||l_br_date_from,10);
2151: hr_utility.set_location('p_br_date_to - '||l_br_date_to,10);
2152: hr_utility.set_location('p_year_from - '||p_year_from,10);
2153: hr_utility.set_location('p_year_to - '||p_year_to,10);
2154: hr_utility.set_location('l_year_temp - '||l_year_temp,10);
2155: hr_utility.set_location('l_earliest_start_date - '||to_char(l_earliest_start_date,'DD-MM-YYYY'),10);
2156:
2149: hr_utility.set_location('p_business_rule - '||p_business_rule,10);
2150: hr_utility.set_location('p_br_date_from - '||l_br_date_from,10);
2151: hr_utility.set_location('p_br_date_to - '||l_br_date_to,10);
2152: hr_utility.set_location('p_year_from - '||p_year_from,10);
2153: hr_utility.set_location('p_year_to - '||p_year_to,10);
2154: hr_utility.set_location('l_year_temp - '||l_year_temp,10);
2155: hr_utility.set_location('l_earliest_start_date - '||to_char(l_earliest_start_date,'DD-MM-YYYY'),10);
2156:
2157: fetch csr_assignment_business_rule into l_br_assignment_id,
2150: hr_utility.set_location('p_br_date_from - '||l_br_date_from,10);
2151: hr_utility.set_location('p_br_date_to - '||l_br_date_to,10);
2152: hr_utility.set_location('p_year_from - '||p_year_from,10);
2153: hr_utility.set_location('p_year_to - '||p_year_to,10);
2154: hr_utility.set_location('l_year_temp - '||l_year_temp,10);
2155: hr_utility.set_location('l_earliest_start_date - '||to_char(l_earliest_start_date,'DD-MM-YYYY'),10);
2156:
2157: fetch csr_assignment_business_rule into l_br_assignment_id,
2158: l_br_date_of_birth,
2151: hr_utility.set_location('p_br_date_to - '||l_br_date_to,10);
2152: hr_utility.set_location('p_year_from - '||p_year_from,10);
2153: hr_utility.set_location('p_year_to - '||p_year_to,10);
2154: hr_utility.set_location('l_year_temp - '||l_year_temp,10);
2155: hr_utility.set_location('l_earliest_start_date - '||to_char(l_earliest_start_date,'DD-MM-YYYY'),10);
2156:
2157: fetch csr_assignment_business_rule into l_br_assignment_id,
2158: l_br_date_of_birth,
2159: l_br_annivarsary_of_joining,
2174: l_payroll_id := null;
2175:
2176: end if;
2177:
2178: hr_utility.set_location('l_br_assignment_id,'||l_br_assignment_id,15);
2179: hr_utility.set_location('l_br_date_of_birth,'||l_br_date_of_birth,15);
2180: hr_utility.set_location('l_br_annivarsary_of_joining,'||l_br_annivarsary_of_joining,15);
2181: hr_utility.set_location('l_br_latest_hire_date,'||l_br_latest_hire_date,15);
2182: hr_utility.set_location('l_br_adjusted_svc_date,'||l_br_adjusted_svc_date,15);
2175:
2176: end if;
2177:
2178: hr_utility.set_location('l_br_assignment_id,'||l_br_assignment_id,15);
2179: hr_utility.set_location('l_br_date_of_birth,'||l_br_date_of_birth,15);
2180: hr_utility.set_location('l_br_annivarsary_of_joining,'||l_br_annivarsary_of_joining,15);
2181: hr_utility.set_location('l_br_latest_hire_date,'||l_br_latest_hire_date,15);
2182: hr_utility.set_location('l_br_adjusted_svc_date,'||l_br_adjusted_svc_date,15);
2183: hr_utility.set_location('l_copy_br_assignment_id = '||l_copy_br_assignment_id,16);
2176: end if;
2177:
2178: hr_utility.set_location('l_br_assignment_id,'||l_br_assignment_id,15);
2179: hr_utility.set_location('l_br_date_of_birth,'||l_br_date_of_birth,15);
2180: hr_utility.set_location('l_br_annivarsary_of_joining,'||l_br_annivarsary_of_joining,15);
2181: hr_utility.set_location('l_br_latest_hire_date,'||l_br_latest_hire_date,15);
2182: hr_utility.set_location('l_br_adjusted_svc_date,'||l_br_adjusted_svc_date,15);
2183: hr_utility.set_location('l_copy_br_assignment_id = '||l_copy_br_assignment_id,16);
2184: hr_utility.set_location('l_br_assignment_id = '||l_br_assignment_id,16);
2177:
2178: hr_utility.set_location('l_br_assignment_id,'||l_br_assignment_id,15);
2179: hr_utility.set_location('l_br_date_of_birth,'||l_br_date_of_birth,15);
2180: hr_utility.set_location('l_br_annivarsary_of_joining,'||l_br_annivarsary_of_joining,15);
2181: hr_utility.set_location('l_br_latest_hire_date,'||l_br_latest_hire_date,15);
2182: hr_utility.set_location('l_br_adjusted_svc_date,'||l_br_adjusted_svc_date,15);
2183: hr_utility.set_location('l_copy_br_assignment_id = '||l_copy_br_assignment_id,16);
2184: hr_utility.set_location('l_br_assignment_id = '||l_br_assignment_id,16);
2185:
2178: hr_utility.set_location('l_br_assignment_id,'||l_br_assignment_id,15);
2179: hr_utility.set_location('l_br_date_of_birth,'||l_br_date_of_birth,15);
2180: hr_utility.set_location('l_br_annivarsary_of_joining,'||l_br_annivarsary_of_joining,15);
2181: hr_utility.set_location('l_br_latest_hire_date,'||l_br_latest_hire_date,15);
2182: hr_utility.set_location('l_br_adjusted_svc_date,'||l_br_adjusted_svc_date,15);
2183: hr_utility.set_location('l_copy_br_assignment_id = '||l_copy_br_assignment_id,16);
2184: hr_utility.set_location('l_br_assignment_id = '||l_br_assignment_id,16);
2185:
2186: l_br_update := 'N';
2179: hr_utility.set_location('l_br_date_of_birth,'||l_br_date_of_birth,15);
2180: hr_utility.set_location('l_br_annivarsary_of_joining,'||l_br_annivarsary_of_joining,15);
2181: hr_utility.set_location('l_br_latest_hire_date,'||l_br_latest_hire_date,15);
2182: hr_utility.set_location('l_br_adjusted_svc_date,'||l_br_adjusted_svc_date,15);
2183: hr_utility.set_location('l_copy_br_assignment_id = '||l_copy_br_assignment_id,16);
2184: hr_utility.set_location('l_br_assignment_id = '||l_br_assignment_id,16);
2185:
2186: l_br_update := 'N';
2187:
2180: hr_utility.set_location('l_br_annivarsary_of_joining,'||l_br_annivarsary_of_joining,15);
2181: hr_utility.set_location('l_br_latest_hire_date,'||l_br_latest_hire_date,15);
2182: hr_utility.set_location('l_br_adjusted_svc_date,'||l_br_adjusted_svc_date,15);
2183: hr_utility.set_location('l_copy_br_assignment_id = '||l_copy_br_assignment_id,16);
2184: hr_utility.set_location('l_br_assignment_id = '||l_br_assignment_id,16);
2185:
2186: l_br_update := 'N';
2187:
2188: if l_first_call_flag = FALSE then
2186: l_br_update := 'N';
2187:
2188: if l_first_call_flag = FALSE then
2189:
2190: hr_utility.set_location('l_first_call_flag = FALSE',20);
2191:
2192: -- Check if the next record has the same assignment id
2193:
2194: if l_copy_br_assignment_id = l_br_assignment_id then
2192: -- Check if the next record has the same assignment id
2193:
2194: if l_copy_br_assignment_id = l_br_assignment_id then
2195:
2196: hr_utility.set_location('Assignments Match - Duplicates',25);
2197:
2198: l_duplicate_flag := TRUE;
2199:
2200: else
2198: l_duplicate_flag := TRUE;
2199:
2200: else
2201:
2202: hr_utility.set_location('Assignments Don''t Match - No Duplicates',25);
2203:
2204: l_duplicate_flag := FALSE;
2205: l_br_update := 'Y';
2206:
2211: -- ================================================================================
2212:
2213: if p_business_rule = 'DOB' then -- ** DATE OF BIRTH ** --
2214:
2215: hr_utility.set_location('p_business_rule - '||p_business_rule,30);
2216:
2217: if l_duplicate_flag = TRUE
2218: and l_copy_br_date_of_birth <> l_br_date_of_birth then
2219:
2234: end if;
2235:
2236: elsif p_business_rule = 'AOJ' then -- ** ANNIVERSARY OF JOINING ** --
2237:
2238: hr_utility.set_location('p_business_rule - '||p_business_rule,30);
2239:
2240: if l_duplicate_flag = TRUE
2241: and l_copy_br_anniv_of_joining <> l_br_annivarsary_of_joining then
2242:
2239:
2240: if l_duplicate_flag = TRUE
2241: and l_copy_br_anniv_of_joining <> l_br_annivarsary_of_joining then
2242:
2243: hr_utility.set_location('Anniversary Of Joining Changed!',1);
2244: l_br_update := 'N';
2245: l_duplicate_error_flag := TRUE; -- Incase next record is same assignment
2246:
2247: end if;
2249: l_effective_date := l_copy_br_anniv_of_joining;
2250:
2251: elsif p_business_rule = 'ASD' then -- ** ADJUSTED SERVICE DATE ** --
2252:
2253: hr_utility.set_location('p_business_rule - '||p_business_rule,30);
2254:
2255: if l_duplicate_flag = TRUE
2256: and l_copy_br_adjusted_svc_date <>l_br_adjusted_svc_date then
2257:
2273: end if;
2274:
2275: elsif p_business_rule = 'LHD' then -- ** LATEST HIRE DATE ** --
2276:
2277: hr_utility.set_location('p_business_rule - '||p_business_rule,30);
2278:
2279: if l_duplicate_flag = TRUE
2280: and l_copy_br_latest_hire_date <> l_br_latest_hire_date then
2281:
2278:
2279: if l_duplicate_flag = TRUE
2280: and l_copy_br_latest_hire_date <> l_br_latest_hire_date then
2281:
2282: hr_utility.set_location('Duplicate but Latest Hire Date Changed',1);
2283: l_br_update := 'N';
2284: l_duplicate_error_flag := TRUE; -- Incase next record is same assignment
2285:
2286: end if;
2289:
2290: else
2291:
2292: -- Invalid Business Rule
2293: hr_utility.set_location('Invalid Business Rule',1);
2294:
2295: fnd_message.set_name('PER', 'HR_289507_SPP_BR_INVALID');
2296: hr_utility.raise_error;
2297:
2292: -- Invalid Business Rule
2293: hr_utility.set_location('Invalid Business Rule',1);
2294:
2295: fnd_message.set_name('PER', 'HR_289507_SPP_BR_INVALID');
2296: hr_utility.raise_error;
2297:
2298: end if;
2299:
2300:
2307: l_effective_date := l_effective_date + 1;
2308: end if;
2309: if to_date(to_char(p_br_date_to,'YYYY'),'YYYY') > to_date(to_char(p_br_date_from,'YYYY'),'YYYY') then
2310:
2311: hr_utility.set_location('Business Rule Dates Over Two Years',31);
2312:
2313: -- use l_effective_year for l_br_date_from year
2314: -- and l_effective_month for l_br_date_to year
2315: -- and l_temp_year for l_effective_date
2316: l_effective_year := to_char(p_br_date_from, 'YYYY');
2317: l_effective_month := to_char(p_br_date_to, 'YYYY');
2318: l_temp_year := to_char(l_effective_date,'DD/MM')||'/';
2319:
2320: hr_utility.set_location('l_temp_year '||l_temp_year,75);
2321:
2322: if to_date(l_temp_year||l_effective_year,'DD-MM-YYYY') >= p_br_date_from then
2323:
2324: hr_utility.set_location('End of year',31);
2320: hr_utility.set_location('l_temp_year '||l_temp_year,75);
2321:
2322: if to_date(l_temp_year||l_effective_year,'DD-MM-YYYY') >= p_br_date_from then
2323:
2324: hr_utility.set_location('End of year',31);
2325:
2326: l_effective_year := to_char(p_br_date_from,'YYYY');
2327: l_effective_month := to_char(l_effective_date,'DD/MM')||'/';
2328: l_temp_year := l_effective_month||l_effective_year;
2330: l_effective_date := to_date(l_temp_year,'DD/MM/YYYY');
2331:
2332: elsif to_date(l_temp_year||l_effective_month,'DD-MM-YYYY') <= p_br_date_to then
2333:
2334: hr_utility.set_location('Start of year',31);
2335:
2336: l_effective_year := to_char(p_br_date_to,'YYYY');
2337: l_effective_month := to_char(l_effective_date,'DD/MM')||'/';
2338: l_temp_year := l_effective_month||l_effective_year;
2342: end if;
2343:
2344: elsif to_date(to_char(p_br_date_to,'YYYY'),'YYYY') = to_date(to_char(p_br_date_from,'YYYY'),'YYYY') then
2345:
2346: hr_utility.set_location('Business Rule Over One Year',31);
2347:
2348: l_effective_year := to_char(p_br_date_from,'YYYY');
2349: l_effective_month := to_char(l_effective_date,'DD/MM')||'/';
2350: l_temp_year := l_effective_month||l_effective_year;
2348: l_effective_year := to_char(p_br_date_from,'YYYY');
2349: l_effective_month := to_char(l_effective_date,'DD/MM')||'/';
2350: l_temp_year := l_effective_month||l_effective_year;
2351:
2352: hr_utility.set_location('l_temp_year '||l_temp_year,31);
2353:
2354: l_effective_date := to_date(l_temp_year,'DD/MM/YYYY');
2355:
2356: end if;
2362: -- effective date to use
2363: --
2364: -- ===============================================================================
2365:
2366: hr_utility.set_location('--------- Dependant Date Rule - '||p_dependant_date,32);
2367:
2368: if p_dependant_date = 'DD1' then -- ## Effective Date ##
2369:
2370: hr_utility.set_location('Dependant Date Rule - Effectvie Date',33);
2366: hr_utility.set_location('--------- Dependant Date Rule - '||p_dependant_date,32);
2367:
2368: if p_dependant_date = 'DD1' then -- ## Effective Date ##
2369:
2370: hr_utility.set_location('Dependant Date Rule - Effectvie Date',33);
2371:
2372: l_effective_date := p_effective_date;
2373:
2374: elsif p_dependant_date = 'DD2' then -- ## Business Rule effective Date ##
2372: l_effective_date := p_effective_date;
2373:
2374: elsif p_dependant_date = 'DD2' then -- ## Business Rule effective Date ##
2375:
2376: hr_utility.set_location('Dependant Date Rule - Business Rule Effective Date',33);
2377:
2378: l_effective_date := l_effective_date;
2379:
2380: elsif p_dependant_date = 'DD3' then -- ## Start Of Next Month ##
2378: l_effective_date := l_effective_date;
2379:
2380: elsif p_dependant_date = 'DD3' then -- ## Start Of Next Month ##
2381:
2382: hr_utility.set_location('Dependant Date Rule - Start Of Next Month',33);
2383: hr_utility.set_location('l_effective_date = '||l_effective_date,33);
2384:
2385: l_effective_date := last_day(l_effective_date)+1;
2386:
2379:
2380: elsif p_dependant_date = 'DD3' then -- ## Start Of Next Month ##
2381:
2382: hr_utility.set_location('Dependant Date Rule - Start Of Next Month',33);
2383: hr_utility.set_location('l_effective_date = '||l_effective_date,33);
2384:
2385: l_effective_date := last_day(l_effective_date)+1;
2386:
2387: elsif p_dependant_date = 'DD4' then -- ## Start Of next pay period ##
2385: l_effective_date := last_day(l_effective_date)+1;
2386:
2387: elsif p_dependant_date = 'DD4' then -- ## Start Of next pay period ##
2388:
2389: hr_utility.set_location('Pay Period Dependant Date',33);
2390:
2391: if l_copy_payroll_id is null then
2392:
2393: hr_utility.set_location('Null Payroll Id',33);
2389: hr_utility.set_location('Pay Period Dependant Date',33);
2390:
2391: if l_copy_payroll_id is null then
2392:
2393: hr_utility.set_location('Null Payroll Id',33);
2394:
2395: l_update2 := 'N';
2396: l_exception_report2 := 'Y';
2397:
2402: open csr_pay_period;
2403: fetch csr_pay_period into l_effective_date;
2404: close csr_pay_period;
2405:
2406: hr_utility.set_location('Payroll Exists - l_effective_date - '||l_effective_date,33);
2407:
2408: end if;
2409:
2410: else
2411:
2412: -- Invalid Dependant Date
2413:
2414: fnd_message.set_name('PER', 'HR_289508_SPP_BR_DEP_DATE');
2415: hr_utility.raise_error;
2416:
2417: end if; -- End of dependant date set
2418:
2419: hr_utility.set_location('l_br_update - '||l_br_update,40);
2415: hr_utility.raise_error;
2416:
2417: end if; -- End of dependant date set
2418:
2419: hr_utility.set_location('l_br_update - '||l_br_update,40);
2420: hr_utility.set_location('++ New effective date - '||l_effective_date,40);
2421:
2422: if l_br_update = 'Y'
2423:
2416:
2417: end if; -- End of dependant date set
2418:
2419: hr_utility.set_location('l_br_update - '||l_br_update,40);
2420: hr_utility.set_location('++ New effective date - '||l_effective_date,40);
2421:
2422: if l_br_update = 'Y'
2423:
2424: and l_duplicate_flag = FALSE
2423:
2424: and l_duplicate_flag = FALSE
2425: and l_duplicate_error_flag = FALSE then
2426:
2427: hr_utility.set_location('++++++++++++++ UPDATING ++++++++++++++++++',45);
2428:
2429: -- ================================================================================
2430: -- Call the main cursor to retrieve all information required - Assignment Id used
2431: -- ================================================================================
2443: if (p_concat_segs is null) then
2444: --
2445: -- fetch standard cursor, no group partial matching
2446: --
2447: hr_utility.set_location ('hrspine.spine', 52);
2448: FETCH C_Spinal_Placements_Cursor
2449: INTO LC_Placement_ID,
2450: LC_Step_ID,
2451: LC_Assignment_ID,
2466: else
2467: --
2468: -- fetch key cursor, with group partial matching
2469: --
2470: hr_utility.set_location ('hrspine.spine', 6);
2471: FETCH C_Key_Spinal_Placements_Cursor
2472: INTO LC_Placement_ID,
2473: LC_Step_ID,
2474: LC_Assignment_ID,
2486:
2487: EXIT when C_Key_Spinal_Placements_Cursor%notfound;
2488: end if;
2489: --
2490: hr_utility.set_location('Placement Found - '||LC_Placement_ID,45);
2491: hr_utility.set_location('############# Effective Date ###### '||l_effective_date,45);
2492: --
2493: -- Call the procedure to heck if assignment selected can be incremented
2494: --
2487: EXIT when C_Key_Spinal_Placements_Cursor%notfound;
2488: end if;
2489: --
2490: hr_utility.set_location('Placement Found - '||LC_Placement_ID,45);
2491: hr_utility.set_location('############# Effective Date ###### '||l_effective_date,45);
2492: --
2493: -- Call the procedure to heck if assignment selected can be incremented
2494: --
2495:
2512: ,p_max_special_sequence_number => l_max_special_sequence_number
2513: ,p_max_spinal_point_id => l_max_spinal_point_id
2514: ,p_max_special_spinal_point_id => l_max_special_spinal_point_id );
2515:
2516: hr_utility.set_location('############# Effective Date ###### '||l_effective_date,45);
2517:
2518: if l_update = 'N' or l_update2 = 'N' then
2519:
2520: l_update := 'N';
2615:
2616: -- -----------------------------------------------------------
2617: -- set last auto inc. date on parent spine
2618: -- -----------------------------------------------------------
2619: hr_utility.set_location ('hrspine.spine', 11);
2620: UPDATE per_parent_spines
2621: SET last_automatic_increment_date = P_Effective_Date,
2622: last_update_date = sysdate,
2623: program_update_date = sysdate
2627: --
2628: if l_message_number_count = 0 then
2629: --
2630: p_message_number := 'PER_289573_SPP_NO_DATA';
2631: hr_utility.set_location ('officially no data.spine', 11);
2632: --
2633: else
2634: --
2635: p_message_number := null;
2632: --
2633: else
2634: --
2635: p_message_number := null;
2636: hr_utility.set_location ('hrspine.spine shd be sweet', 11);
2637: end if;
2638: hr_utility.set_location ('hrspine.spine', 12);
2639: --
2640: END spine;
2634: --
2635: p_message_number := null;
2636: hr_utility.set_location ('hrspine.spine shd be sweet', 11);
2637: end if;
2638: hr_utility.set_location ('hrspine.spine', 12);
2639: --
2640: END spine;
2641: --
2642: END hrspine;