[Home] [Help]
610: -- fix for bug 5841180 ends here
611:
612: cursor csr_lock_spp is
613: select null
614: from per_spinal_point_placements_f spp
615: where spp.assignment_id = p_assignment_id
616: for update nowait;
617: --
618: cursor csr_lock_ppm is
773: -- process date:
774: --
775: -- per_secondary_ass_statuses (not datetracked)
776: -- pay_cost_allocations_f
777: -- per_spinal_point_placements_f
778: -- pay_personal_payment_methods_f
779: -- per_assignment_budget_values_f
780: --
781: update per_secondary_ass_statuses sas
806: and pca.effective_start_date > p_final_process_date;
807: --
808: hr_utility.set_location(l_proc, 190);
809: --
810: update per_spinal_point_placements_f spp
811: set spp.effective_end_date = p_final_process_date
812: where spp.assignment_id = p_assignment_id
813: and p_final_process_date between spp.effective_start_date
814: and spp.effective_end_date;
814: and spp.effective_end_date;
815: --
816: hr_utility.set_location(l_proc, 200);
817: --
818: delete per_spinal_point_placements_f spp
819: where spp.assignment_id = p_assignment_id
820: and spp.effective_start_date > p_final_process_date;
821: --
822: hr_utility.set_location(l_proc, 210);
1557: -- Check to see if a grade step has been created for assignment
1558: --
1559: cursor csr_grade_step is
1560: select spp.placement_id
1561: from per_spinal_point_placements_f spp
1562: where spp.assignment_id = p_assignment_id
1563: and p_validation_start_date between spp.effective_start_date
1564: and spp.effective_end_date;
1565:
1576: -- Get the placement id and object version number for the assignment
1577: --
1578: select placement_id,object_version_number,effective_end_date
1579: into l_placement_id,l_object_version_number,l_effective_end_date
1580: from per_spinal_point_placements_f
1581: where assignment_id = p_assignment_id
1582: and p_validation_start_date between effective_start_date
1583: and effective_end_date;
1584:
1605: ,p_effective_end_date => l_effective_end_date);
1606:
1607: select effective_end_date
1608: into l_effective_end_date
1609: from per_spinal_point_placements_f
1610: where placement_id = l_placement_id
1611: and p_validation_start_date between effective_start_date
1612: and effective_end_date;
1613:
1615:
1616: --
1617: -- Now that there is only one record for the period, use dml to remove the first record
1618: --
1619: delete from per_spinal_point_placements_f
1620: where assignment_id = p_assignment_id
1621: and placement_id = l_placement_id
1622: and effective_start_date = p_validation_start_date;
1623:
2368: -- Fix for bug 5841180 ends here
2369: --
2370: cursor csr_lock_spp is
2371: select null
2372: from per_spinal_point_placements_f spp
2373: where spp.assignment_id = p_assignment_id
2374: for update nowait;
2375: --
2376: cursor csr_lock_ppm is
2515: -- process date:
2516: --
2517: -- per_secondary_ass_statuses (not datetracked)
2518: -- pay_cost_allocations_f
2519: -- per_spinal_point_placements_f
2520: -- pay_personal_payment_methods_f
2521: -- per_assignment_budget_values_f
2522: --
2523: update per_secondary_ass_statuses sas
2548: and pca.effective_start_date > p_final_process_date;
2549: --
2550: hr_utility.set_location(l_proc, 190);
2551: --
2552: update per_spinal_point_placements_f spp
2553: set spp.effective_end_date = p_final_process_date
2554: where spp.assignment_id = p_assignment_id
2555: and p_final_process_date between spp.effective_start_date
2556: and spp.effective_end_date;
2556: and spp.effective_end_date;
2557: --
2558: hr_utility.set_location(l_proc, 200);
2559: --
2560: delete per_spinal_point_placements_f spp
2561: where spp.assignment_id = p_assignment_id
2562: and spp.effective_start_date > p_final_process_date;
2563: --
2564: hr_utility.set_location(l_proc, 210);
2712: --
2713: -- Declare Local Variables
2714: --
2715: l_proc VARCHAR2(72) := g_package||'spp_zap';
2716: l_previous_id per_spinal_point_placements_f.placement_id%TYPE;
2717: l_effective_start_date DATE;
2718: l_effective_end_date DATE;
2719: --
2720: CURSOR csr_spp_records IS
2720: CURSOR csr_spp_records IS
2721: SELECT spp.placement_id,
2722: spp.object_version_number,
2723: spp.effective_start_date
2724: FROM per_spinal_point_placements_f spp
2725: WHERE spp.assignment_id = p_assignment_id
2726: ORDER BY placement_id;
2727: --
2728: BEGIN
2764: -- ----------------------------------------------------------------------------
2765: --
2766: PROCEDURE spp_update_change_insert
2767: (p_assignment_id IN per_assignments_f.assignment_id%TYPE
2768: ,p_placement_id IN per_spinal_point_placements_f.placement_id%TYPE
2769: ,p_validation_start_date IN DATE
2770: ,p_validation_end_date IN DATE
2771: ,p_spp_eff_start_date IN DATE
2772: ,p_datetrack_mode IN OUT NOCOPY VARCHAR2
2777: l_proc VARCHAR2(72) := g_package||'spp_update_change_insert';
2778: l_effective_start_date DATE;
2779: l_effective_end_date DATE;
2780: l_datetrack_mode VARCHAR2(30);
2781: l_object_version_number per_spinal_point_placements_f.object_version_number%TYPE;
2782: l_dummy_id per_spinal_point_placements_f.placement_id%TYPE;
2783: --
2784: -- Checks to see if future rows exist
2785: --
2778: l_effective_start_date DATE;
2779: l_effective_end_date DATE;
2780: l_datetrack_mode VARCHAR2(30);
2781: l_object_version_number per_spinal_point_placements_f.object_version_number%TYPE;
2782: l_dummy_id per_spinal_point_placements_f.placement_id%TYPE;
2783: --
2784: -- Checks to see if future rows exist
2785: --
2786: CURSOR csr_future_records IS
2784: -- Checks to see if future rows exist
2785: --
2786: CURSOR csr_future_records IS
2787: SELECT spp.placement_id
2788: FROM per_spinal_point_placements_f spp
2789: WHERE spp.assignment_id = p_assignment_id
2790: AND spp.effective_start_date > p_validation_start_date;
2791: --
2792: --
2796: CURSOR csr_update_change_insert_rows IS
2797: SELECT spp.placement_id,
2798: spp.object_version_number,
2799: spp.effective_start_date
2800: FROM per_spinal_point_placements_f spp
2801: WHERE effective_start_date BETWEEN p_spp_eff_start_date
2802: AND p_validation_end_date
2803: AND effective_end_date < p_validation_end_date
2804: AND assignment_id = p_assignment_id
2925: -- ----------------------------------------------------------------------------
2926: --
2927: procedure chk_valid_placement_id
2928: (p_assignment_id in per_all_assignments_f.assignment_id%Type
2929: ,p_placement_id in per_spinal_point_placements_f.placement_id%Type
2930: ,p_validation_start_date in date) is
2931: --
2932: -- Local variables
2933: l_exist varchar2(1);
2935: l_proc varchar(72) := g_package||'chk_valid_placement_id';
2936: -- Fetch future SPP Records(other placement id)
2937: cursor csr_invalid_placement_id is
2938: select 'Y'
2939: from per_spinal_point_placements_f spp
2940: where spp.assignment_id = p_assignment_id
2941: and spp.effective_start_date > p_validation_start_date
2942: and spp.placement_id <> p_placement_id;
2943: --
2998: select spp.placement_id,
2999: spp.object_version_number,
3000: spp.effective_start_date,
3001: spp.effective_end_date
3002: from per_spinal_point_placements_f spp
3003: where spp.assignment_id = p_assignment_id
3004: and spp.effective_start_date >= l_asg_eff_start_date
3005: and spp.effective_end_date <= l_asg_eff_end_date;
3006: -- Cursor to retrive the SPP records without a valid Grade
3010: spp.effective_start_date,
3011: spp.effective_end_date
3012: from per_grade_spines_f pgs,
3013: per_spinal_point_steps_f sps,
3014: per_spinal_point_placements_f spp
3015: where sps.grade_spine_id = pgs.grade_spine_id
3016: and spp.step_id = sps.step_id
3017: and pgs.parent_spine_id = spp.parent_spine_id
3018: and spp.assignment_id = p_assignment_id
3023: cursor csr_asg_spp_error(l_asg_eff_start_date date, l_asg_eff_end_date date) is
3024: select pgs.grade_id
3025: from per_grade_spines_f pgs,
3026: per_spinal_point_steps_f sps,
3027: per_spinal_point_placements_f spp
3028: where sps.grade_spine_id = pgs.grade_spine_id
3029: and spp.step_id = sps.step_id
3030: and pgs.parent_spine_id = spp.parent_spine_id
3031: and spp.assignment_id = p_assignment_id
3037: cursor csr_spp_records is
3038: select spp.placement_id,
3039: spp.effective_start_date,
3040: spp.effective_end_date
3041: from per_spinal_point_placements_f spp
3042: where spp.assignment_id = p_assignment_id
3043: and spp.effective_start_date >= p_validation_start_date;
3044: --
3045: begin
3059: -- Needs to be deleted all non valid SPP records
3060: for asg_spp_rec in csr_asg_spp(csr_asg_rec.effective_start_date,
3061: csr_asg_rec.effective_end_date) loop
3062: -- There are some SPP records existing for this assignment without a Grade.
3063: delete from per_spinal_point_placements_f spp
3064: where spp.placement_id = asg_spp_rec.placement_id
3065: and spp.effective_start_date = asg_spp_rec.effective_start_date
3066: and spp.effective_end_date = asg_spp_rec.effective_end_date;
3067: --
3083: hr_utility.set_location(l_proc||' Asg Grade Id '||csr_asg_rec.grade_id , 50);
3084: end if;
3085: if rec_spp_placement.grade_id <> csr_asg_rec.grade_id then
3086: --
3087: delete from per_spinal_point_placements_f spp
3088: where spp.placement_id = rec_spp_placement.placement_id
3089: and spp.effective_start_date = rec_spp_placement.effective_start_date
3090: and spp.effective_end_date = rec_spp_placement.effective_end_date;
3091: --
3133: hr_api.g_update_change_insert) then
3134: --
3135: for spp_rec in csr_spp_records loop
3136: --
3137: delete from per_spinal_point_placements_f spp
3138: where spp.placement_id = spp_rec.placement_id
3139: and spp.effective_start_date = spp_rec.effective_start_date
3140: and spp.effective_end_date = spp_rec.effective_end_date;
3141: if g_debug then
3158: -- ----------------------------------------------------------------------------
3159: --
3160: procedure delete_next_change_spp
3161: (p_assignment_id in per_all_assignments_f.assignment_id%Type
3162: ,p_placement_id in per_spinal_point_placements_f.placement_id%Type
3163: ,p_grade_id in per_grade_spines_f.grade_id%Type
3164: ,p_datetrack_mode in varchar2
3165: ,p_validation_start_date in date
3166: ,p_validation_end_date in date
3172: l_exists varchar2(1);
3173: l_effective_start_date date;
3174: l_effective_end_date date;
3175: l_del_end_future_spp boolean := false;
3176: l_object_version_number per_spinal_point_placements_f.object_version_number%Type;
3177: l_grade_id per_spinal_point_placements_f.placement_id%Type;
3178: --
3179: l_proc varchar(72) := g_package||'delete_next_change_spp';
3180: -- Cursor used to retrieve all SPP records for a Grade.
3173: l_effective_start_date date;
3174: l_effective_end_date date;
3175: l_del_end_future_spp boolean := false;
3176: l_object_version_number per_spinal_point_placements_f.object_version_number%Type;
3177: l_grade_id per_spinal_point_placements_f.placement_id%Type;
3178: --
3179: l_proc varchar(72) := g_package||'delete_next_change_spp';
3180: -- Cursor used to retrieve all SPP records for a Grade.
3181: cursor csr_update_change_rows is
3182: select spp.placement_id,
3183: spp.effective_start_date,
3184: spp.effective_end_date,
3185: spp.object_version_number
3186: from per_spinal_point_placements_f spp
3187: where spp.effective_end_date between p_validation_start_date - 1
3188: and p_validation_end_date
3189: and spp.effective_end_date < p_validation_end_date
3190: and spp.assignment_id = p_assignment_id
3193: cursor csr_spp_grade is
3194: select pgs.grade_id
3195: from per_grade_spines_f pgs,
3196: per_spinal_point_steps_f sps,
3197: per_spinal_point_placements_f spp
3198: where sps.grade_spine_id = pgs.grade_spine_id
3199: and spp.step_id = sps.step_id
3200: and pgs.parent_spine_id = spp.parent_spine_id
3201: and spp.assignment_id = p_assignment_id
3206: --
3207: cursor csr_ved_continues_spp IS
3208: select spp.placement_id,spp.effective_start_date
3209: ,spp.effective_end_date, spp.object_version_number
3210: from per_spinal_point_placements_f spp
3211: where spp.assignment_id = p_assignment_id
3212: and spp.effective_start_date < p_validation_end_date
3213: and spp.effective_end_date > p_validation_end_date;
3214: --
3215: -- Check for future spp dt records.
3216: --
3217: cursor csr_future_spp_exists(p_date date,p_placement_id number) IS
3218: select 'Y'
3219: from per_spinal_point_placements_f
3220: where placement_id = p_placement_id
3221: and effective_start_date > p_date;
3222: --
3223: begin
3332: -- ----------------------------------------------------------------------------
3333: --
3334: procedure future_change_spp
3335: (p_assignment_id in per_all_assignments_f.assignment_id%Type
3336: ,p_placement_id in per_spinal_point_placements_f.placement_id%Type
3337: ,p_datetrack_mode in varchar2
3338: ,p_validation_start_date in date
3339: ,p_del_end_future_spp out nocopy boolean) is
3340: --
3343: l_spp_eff_end_date date;
3344: l_effective_start_date date;
3345: l_effective_end_date date;
3346: l_del_end_future_spp boolean := false;
3347: l_placement_id per_spinal_point_placements_f.placement_id%Type;
3348: l_object_version_number per_spinal_point_placements_f.object_version_number%Type;
3349: --
3350: l_proc varchar2(72) := g_package||'future_change_spp';
3351: -- As the validation start and end dates are for the asg row being
3344: l_effective_start_date date;
3345: l_effective_end_date date;
3346: l_del_end_future_spp boolean := false;
3347: l_placement_id per_spinal_point_placements_f.placement_id%Type;
3348: l_object_version_number per_spinal_point_placements_f.object_version_number%Type;
3349: --
3350: l_proc varchar2(72) := g_package||'future_change_spp';
3351: -- As the validation start and end dates are for the asg row being
3352: -- deleted, then we need to get the SPP record that belongs to the
3355: select spp.placement_id,
3356: spp.object_version_number,
3357: spp.effective_start_date,
3358: spp.effective_end_date
3359: from per_spinal_point_placements_f spp
3360: where spp.assignment_id = p_assignment_id
3361: and spp.effective_start_date < p_validation_start_date
3362: order by effective_start_date desc;
3363: --
3427: -- ----------------------------------------------------------------------------
3428: --
3429: procedure update_override_spp
3430: (p_assignment_id in per_all_assignments_f.assignment_id%Type
3431: ,p_placement_id in per_spinal_point_placements_f.placement_id%Type
3432: ,p_datetrack_mode in varchar2
3433: ,p_validation_start_date in date
3434: ,p_validation_end_date in date
3435: ,p_spp_eff_start_date in date
3446: l_max_eff_end_date date;
3447: l_grade_id number;
3448: l_datetrack_mode varchar2(30);
3449: l_del_end_future_spp boolean := false;
3450: l_dummy_id per_spinal_point_placements_f.placement_id%Type;
3451: l_previous_ovn per_spinal_point_placements_f.object_version_number%Type;
3452: l_object_version_number per_spinal_point_placements_f.object_version_number%Type
3453: := p_object_version_number;
3454: --
3447: l_grade_id number;
3448: l_datetrack_mode varchar2(30);
3449: l_del_end_future_spp boolean := false;
3450: l_dummy_id per_spinal_point_placements_f.placement_id%Type;
3451: l_previous_ovn per_spinal_point_placements_f.object_version_number%Type;
3452: l_object_version_number per_spinal_point_placements_f.object_version_number%Type
3453: := p_object_version_number;
3454: --
3455: l_proc varchar2(72) := g_package||'update_override_spp';
3448: l_datetrack_mode varchar2(30);
3449: l_del_end_future_spp boolean := false;
3450: l_dummy_id per_spinal_point_placements_f.placement_id%Type;
3451: l_previous_ovn per_spinal_point_placements_f.object_version_number%Type;
3452: l_object_version_number per_spinal_point_placements_f.object_version_number%Type
3453: := p_object_version_number;
3454: --
3455: l_proc varchar2(72) := g_package||'update_override_spp';
3456: -- Assignment record is already updated with UPDATE_OVERRIDE DT Mode
3465: order by paa.effective_start_date;
3466: -- Checks to see if any future Grade Step rows exist for the given assignment
3467: cursor csr_future_records is
3468: select spp.placement_id
3469: from per_spinal_point_placements_f spp
3470: where spp.assignment_id = p_assignment_id
3471: and spp.effective_start_date > p_validation_start_date;
3472: -- Cursor to retrive the past SPP record to perform
3473: -- DELETE_NEXT_CHANGE
3474: cursor csr_past_spp_details(l_placement_id in number) is
3475: select spp.object_version_number,
3476: spp.effective_start_date,
3477: spp.effective_end_date
3478: from per_spinal_point_placements_f spp
3479: where spp.placement_id = l_placement_id
3480: and p_validation_start_date - 1 between spp.effective_start_date
3481: and spp.effective_end_date;
3482: -- Cursor to see if any past Grade Step rows exist for thie given assignment.
3481: and spp.effective_end_date;
3482: -- Cursor to see if any past Grade Step rows exist for thie given assignment.
3483: cursor csr_past_spp_records(l_placement_id in number) is
3484: select spp.object_version_number
3485: from per_spinal_point_placements_f spp
3486: where spp.placement_id = l_placement_id
3487: and spp.effective_start_date < p_validation_start_date
3488: order by effective_start_date desc;
3489: -- Cursor used to retrieve all SPP records for a Grade
3489: -- Cursor used to retrieve all SPP records for a Grade
3490: cursor csr_update_change_rows is
3491: select spp.object_version_number,
3492: spp.effective_start_date
3493: from per_spinal_point_placements_f spp
3494: where spp.effective_start_date between p_spp_eff_start_date
3495: and p_validation_end_date
3496: and spp.effective_end_date < p_validation_end_date
3497: and spp.assignment_id = p_assignment_id
3719: -- ----------------------------------------------------------------------------
3720: --
3721: procedure close_spp_records
3722: (p_assignment_id in per_all_assignments_f.assignment_id%Type
3723: ,p_placement_id in per_spinal_point_placements_f.placement_id%Type
3724: ,p_datetrack_mode in varchar2
3725: ,p_validation_start_date in date
3726: ,p_object_version_number in number
3727: ,p_current_spp_exist in boolean
3730: -- Declare Local Variables
3731: l_effective_start_date date;
3732: l_effective_end_date date;
3733: l_del_end_future_spp boolean := false;
3734: l_dummy_id per_spinal_point_placements_f.placement_id%Type;
3735: l_object_version_number per_spinal_point_placements_f.object_version_number%Type
3736: := p_object_version_number;
3737: --
3738: l_proc varchar(72) := g_package||'close_spp_records';
3731: l_effective_start_date date;
3732: l_effective_end_date date;
3733: l_del_end_future_spp boolean := false;
3734: l_dummy_id per_spinal_point_placements_f.placement_id%Type;
3735: l_object_version_number per_spinal_point_placements_f.object_version_number%Type
3736: := p_object_version_number;
3737: --
3738: l_proc varchar(72) := g_package||'close_spp_records';
3739: -- Checks to see if future rows exist for the Placement_id.
3738: l_proc varchar(72) := g_package||'close_spp_records';
3739: -- Checks to see if future rows exist for the Placement_id.
3740: cursor csr_spp_future_records is
3741: select spp.placement_id
3742: from per_spinal_point_placements_f spp
3743: where spp.assignment_id = p_assignment_id
3744: and spp.placement_id = p_placement_id
3745: and spp.effective_start_date > p_validation_start_date;
3746: -- Cursor to see if any past Grade Step rows exist for thie given assignment.
3745: and spp.effective_start_date > p_validation_start_date;
3746: -- Cursor to see if any past Grade Step rows exist for thie given assignment.
3747: cursor csr_past_spp_records is
3748: select spp.object_version_number
3749: from per_spinal_point_placements_f spp
3750: where spp.placement_id = p_placement_id
3751: and spp.effective_start_date < p_validation_start_date
3752: order by effective_start_date desc;
3753: --
3852: l_effective_end_date date;
3853: l_effective_date date;
3854: l_datetrack_mode varchar2(30);
3855: l_del_end_future_spp boolean := false;
3856: l_object_version_number per_spinal_point_placements_f.object_version_number%Type;
3857: l_vsd_continues_spp_exists boolean :=false;
3858: l_ved_continues_spp_exists boolean :=false;
3859: l_min_step_id number;
3860: l_exists varchar2(1);
3865: --
3866: cursor csr_vsd_continues_spp IS
3867: select spp.placement_id,spp.effective_start_date
3868: ,spp.effective_end_date, spp.object_version_number
3869: from per_spinal_point_placements_f spp
3870: where spp.assignment_id = p_assignment_id
3871: and spp.effective_start_date < p_validation_start_date
3872: -- and spp.effective_end_date > p_validation_start_date;
3873: and spp.effective_end_date >= p_validation_start_date;-- fix for the bug5203227
3876: --
3877: cursor csr_ved_continues_spp IS
3878: select spp.placement_id,spp.effective_start_date
3879: ,spp.effective_end_date, spp.object_version_number
3880: from per_spinal_point_placements_f spp
3881: where spp.assignment_id = p_assignment_id
3882: and spp.effective_start_date < p_validation_end_date
3883: and spp.effective_end_date > p_validation_end_date;
3884: --
3886: --
3887: cursor csr_spps_in_validation_period IS
3888: select spp.placement_id,spp.effective_start_date
3889: ,spp.effective_end_date, spp.object_version_number
3890: from per_spinal_point_placements_f spp
3891: where spp.assignment_id = p_assignment_id
3892: and spp.effective_start_date between p_validation_start_date and p_validation_end_date
3893: order by effective_end_date;
3894: --
3894: --
3895: cursor csr_next_spp(p_date date) IS
3896: select placement_id, effective_start_date,
3897: effective_end_date, object_version_number
3898: from per_spinal_point_placements_f
3899: where placement_id = p_placement_id
3900: -- and effective_start_date >= p_date;--fix for bug 5067855 .
3901: and effective_start_date = p_date; -- fix for the bug 5306697 .
3902: --
3903: -- Check for future spp dt records.
3904: --
3905: cursor csr_future_spp_exists(p_date date) IS
3906: select 'Y'
3907: from per_spinal_point_placements_f
3908: where placement_id = p_placement_id
3909: and effective_start_date > p_date;
3910: --
3911: begin
4092: --
4093: PROCEDURE delete_future_spp_records
4094: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
4095: ,p_datetrack_mode IN VARCHAR2
4096: ,p_placement_id IN per_spinal_point_placements_f.placement_id%TYPE
4097: ,p_object_version_number IN per_spinal_point_placements_f.object_version_number%TYPE
4098: ,p_effective_date IN DATE) IS
4099: --
4100: -- Declare Local Variables
4093: PROCEDURE delete_future_spp_records
4094: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
4095: ,p_datetrack_mode IN VARCHAR2
4096: ,p_placement_id IN per_spinal_point_placements_f.placement_id%TYPE
4097: ,p_object_version_number IN per_spinal_point_placements_f.object_version_number%TYPE
4098: ,p_effective_date IN DATE) IS
4099: --
4100: -- Declare Local Variables
4101: --
4103: l_effective_date DATE;
4104: l_effective_start_date DATE;
4105: l_effective_end_date DATE;
4106: l_previous_end_date DATE;
4107: l_previous_id per_spinal_point_placements_f.placement_id%TYPE;
4108: l_previous_ovn per_spinal_point_placements_f.object_version_number%TYPE;
4109: l_object_version_number per_spinal_point_placements_f.object_version_number%TYPE;
4110: l_placement_id per_spinal_point_placements_f.placement_id%TYPE;
4111: --
4104: l_effective_start_date DATE;
4105: l_effective_end_date DATE;
4106: l_previous_end_date DATE;
4107: l_previous_id per_spinal_point_placements_f.placement_id%TYPE;
4108: l_previous_ovn per_spinal_point_placements_f.object_version_number%TYPE;
4109: l_object_version_number per_spinal_point_placements_f.object_version_number%TYPE;
4110: l_placement_id per_spinal_point_placements_f.placement_id%TYPE;
4111: --
4112: -- Fetch future SPP_Records
4105: l_effective_end_date DATE;
4106: l_previous_end_date DATE;
4107: l_previous_id per_spinal_point_placements_f.placement_id%TYPE;
4108: l_previous_ovn per_spinal_point_placements_f.object_version_number%TYPE;
4109: l_object_version_number per_spinal_point_placements_f.object_version_number%TYPE;
4110: l_placement_id per_spinal_point_placements_f.placement_id%TYPE;
4111: --
4112: -- Fetch future SPP_Records
4113: --
4106: l_previous_end_date DATE;
4107: l_previous_id per_spinal_point_placements_f.placement_id%TYPE;
4108: l_previous_ovn per_spinal_point_placements_f.object_version_number%TYPE;
4109: l_object_version_number per_spinal_point_placements_f.object_version_number%TYPE;
4110: l_placement_id per_spinal_point_placements_f.placement_id%TYPE;
4111: --
4112: -- Fetch future SPP_Records
4113: --
4114: CURSOR csr_future_spp_records IS
4114: CURSOR csr_future_spp_records IS
4115: SELECT spp.placement_id,
4116: spp.object_version_number,
4117: spp.effective_start_date
4118: FROM per_spinal_point_placements_f spp
4119: WHERE spp.assignment_id = p_assignment_id
4120: AND spp.effective_start_date > p_effective_date
4121: AND spp.placement_id <> p_placement_id
4122: ORDER BY placement_id;
4124: -- Cursor to see if past rows exist.
4125: --
4126: CURSOR csr_past_spp_records IS
4127: SELECT spp.placement_id
4128: FROM per_spinal_point_placements_f spp
4129: WHERE spp.placement_id = p_placement_id
4130: AND spp.effective_start_date < p_effective_date;
4131: --
4132: CURSOR csr_previous_spp_record IS
4131: --
4132: CURSOR csr_previous_spp_record IS
4133: SELECT spp.object_version_number,
4134: spp.effective_end_date
4135: FROM per_spinal_point_placements_f spp
4136: WHERE spp.placement_id = p_placement_id
4137: AND spp.effective_start_date < p_effective_date
4138: ORDER BY spp.effective_end_date desc;
4139: --
4272: l_datetrack_mode varchar2(30);
4273: l_current_spp_exist boolean := false;
4274: l_future_spp_exist boolean := false;
4275: l_pay_scale_defined boolean := false;
4276: l_placement_id per_spinal_point_placements_f.placement_id%Type;
4277: l_object_version_number per_spinal_point_placements_f.object_version_number%Type;
4278: l_min_step_id per_spinal_point_steps_f.step_id%Type;
4279: l_grade_spine_id per_grade_spines_f.grade_spine_id%Type;
4280: l_dummy_id per_spinal_point_placements_f.placement_id%Type;
4273: l_current_spp_exist boolean := false;
4274: l_future_spp_exist boolean := false;
4275: l_pay_scale_defined boolean := false;
4276: l_placement_id per_spinal_point_placements_f.placement_id%Type;
4277: l_object_version_number per_spinal_point_placements_f.object_version_number%Type;
4278: l_min_step_id per_spinal_point_steps_f.step_id%Type;
4279: l_grade_spine_id per_grade_spines_f.grade_spine_id%Type;
4280: l_dummy_id per_spinal_point_placements_f.placement_id%Type;
4281: -- This warning variable will be used, whenever system internaly delete's
4276: l_placement_id per_spinal_point_placements_f.placement_id%Type;
4277: l_object_version_number per_spinal_point_placements_f.object_version_number%Type;
4278: l_min_step_id per_spinal_point_steps_f.step_id%Type;
4279: l_grade_spine_id per_grade_spines_f.grade_spine_id%Type;
4280: l_dummy_id per_spinal_point_placements_f.placement_id%Type;
4281: -- This warning variable will be used, whenever system internaly delete's
4282: -- any future dated SPP records or End Date an SPP record whcih is having
4283: -- effective_end_date greater than the validation end date. This warning
4284: -- will be set depends on the DT Mode and the SPP records.
4288: -- We need this check for ZAP mode, all other DT modes we need a date
4289: -- effective check and this will be done by cursor csr_spp_details.
4290: cursor csr_lock_spp_rows is
4291: select spp.placement_id
4292: from per_spinal_point_placements_f spp
4293: where spp.assignment_id = p_assignment_id
4294: for update nowait;
4295: -- Checks to see if any future Grade Step rows exist for the given assignment
4296: cursor csr_future_records is
4294: for update nowait;
4295: -- Checks to see if any future Grade Step rows exist for the given assignment
4296: cursor csr_future_records is
4297: select spp.placement_id
4298: from per_spinal_point_placements_f spp
4299: where spp.assignment_id = p_assignment_id
4300: and spp.effective_start_date > p_validation_start_date;
4301: -- Checks to see if future rows exist for the Placement_id.
4302: cursor csr_spp_future_records(p_placement_id number) is
4300: and spp.effective_start_date > p_validation_start_date;
4301: -- Checks to see if future rows exist for the Placement_id.
4302: cursor csr_spp_future_records(p_placement_id number) is
4303: select spp.placement_id
4304: from per_spinal_point_placements_f spp
4305: where spp.assignment_id = p_assignment_id
4306: and spp.placement_id = p_placement_id
4307: and spp.effective_start_date > p_validation_start_date;
4308: -- Cursor to retrive the current Grade Step placement details
4310: select spp.placement_id,
4311: spp.object_version_number,
4312: spp.effective_start_date,
4313: spp.effective_end_date
4314: from per_spinal_point_placements_f spp
4315: where spp.assignment_id = p_assignment_id
4316: and p_validation_start_date between spp.effective_start_date
4317: and spp.effective_end_date;
4318: -- Cursor to retrieve the first step (Minimum Grade Step) on the pay scale
4340: where grade_id = p_grade_id;
4341: -- Cursor to get the minimum effective_start_date of the SPP records
4342: cursor csr_min_spp_date is
4343: select min(spp.effective_start_date)
4344: from per_spinal_point_placements_f spp
4345: where spp.assignment_id = p_assignment_id;
4346: --
4347: begin
4348: --