[Home] [Help]
556: for update nowait;
557: --
558: cursor csr_lock_spp is
559: select null
560: from per_spinal_point_placements_f spp
561: where spp.assignment_id = p_assignment_id
562: for update nowait;
563: --
564: cursor csr_lock_ppm is
707: -- process date:
708: --
709: -- per_secondary_ass_statuses (not datetracked)
710: -- pay_cost_allocations_f
711: -- per_spinal_point_placements_f
712: -- pay_personal_payment_methods_f
713: -- per_assignment_budget_values_f
714: --
715: update per_secondary_ass_statuses sas
740: and pca.effective_start_date > p_final_process_date;
741: --
742: hr_utility.set_location(l_proc, 190);
743: --
744: update per_spinal_point_placements_f spp
745: set spp.effective_end_date = p_final_process_date
746: where spp.assignment_id = p_assignment_id
747: and p_final_process_date between spp.effective_start_date
748: and spp.effective_end_date;
748: and spp.effective_end_date;
749: --
750: hr_utility.set_location(l_proc, 200);
751: --
752: delete per_spinal_point_placements_f spp
753: where spp.assignment_id = p_assignment_id
754: and spp.effective_start_date > p_final_process_date;
755: --
756: hr_utility.set_location(l_proc, 210);
1458: -- Check to see if a grade step has been created for assignment
1459: --
1460: cursor csr_grade_step is
1461: select spp.placement_id
1462: from per_spinal_point_placements_f spp
1463: where spp.assignment_id = p_assignment_id
1464: and p_validation_start_date between spp.effective_start_date
1465: and spp.effective_end_date;
1466:
1477: -- Get the placement id and object version number for the assignment
1478: --
1479: select placement_id,object_version_number,effective_end_date
1480: into l_placement_id,l_object_version_number,l_effective_end_date
1481: from per_spinal_point_placements_f
1482: where assignment_id = p_assignment_id
1483: and p_validation_start_date between effective_start_date
1484: and effective_end_date;
1485:
1506: ,p_effective_end_date => l_effective_end_date);
1507:
1508: select effective_end_date
1509: into l_effective_end_date
1510: from per_spinal_point_placements_f
1511: where placement_id = l_placement_id
1512: and p_validation_start_date between effective_start_date
1513: and effective_end_date;
1514:
1516:
1517: --
1518: -- Now that there is only one record for the period, use dml to remove the first record
1519: --
1520: delete from per_spinal_point_placements_f
1521: where assignment_id = p_assignment_id
1522: and placement_id = l_placement_id
1523: and effective_start_date = p_validation_start_date;
1524:
2246: for update nowait;
2247: --
2248: cursor csr_lock_spp is
2249: select null
2250: from per_spinal_point_placements_f spp
2251: where spp.assignment_id = p_assignment_id
2252: for update nowait;
2253: --
2254: cursor csr_lock_ppm is
2382: -- process date:
2383: --
2384: -- per_secondary_ass_statuses (not datetracked)
2385: -- pay_cost_allocations_f
2386: -- per_spinal_point_placements_f
2387: -- pay_personal_payment_methods_f
2388: -- per_assignment_budget_values_f
2389: --
2390: update per_secondary_ass_statuses sas
2415: and pca.effective_start_date > p_final_process_date;
2416: --
2417: hr_utility.set_location(l_proc, 190);
2418: --
2419: update per_spinal_point_placements_f spp
2420: set spp.effective_end_date = p_final_process_date
2421: where spp.assignment_id = p_assignment_id
2422: and p_final_process_date between spp.effective_start_date
2423: and spp.effective_end_date;
2423: and spp.effective_end_date;
2424: --
2425: hr_utility.set_location(l_proc, 200);
2426: --
2427: delete per_spinal_point_placements_f spp
2428: where spp.assignment_id = p_assignment_id
2429: and spp.effective_start_date > p_final_process_date;
2430: --
2431: hr_utility.set_location(l_proc, 210);
2579: --
2580: -- Declare Local Variables
2581: --
2582: l_proc VARCHAR2(72) := g_package||'spp_zap';
2583: l_previous_id per_spinal_point_placements_f.placement_id%TYPE;
2584: l_effective_start_date DATE;
2585: l_effective_end_date DATE;
2586: --
2587: CURSOR csr_spp_records IS
2587: CURSOR csr_spp_records IS
2588: SELECT spp.placement_id,
2589: spp.object_version_number,
2590: spp.effective_start_date
2591: FROM per_spinal_point_placements_f spp
2592: WHERE spp.assignment_id = p_assignment_id
2593: ORDER BY placement_id;
2594: --
2595: BEGIN
2631: -- ----------------------------------------------------------------------------
2632: --
2633: PROCEDURE spp_update_change_insert
2634: (p_assignment_id IN per_assignments_f.assignment_id%TYPE
2635: ,p_placement_id IN per_spinal_point_placements_f.placement_id%TYPE
2636: ,p_validation_start_date IN DATE
2637: ,p_validation_end_date IN DATE
2638: ,p_spp_eff_start_date IN DATE
2639: ,p_datetrack_mode IN OUT NOCOPY VARCHAR2
2644: l_proc VARCHAR2(72) := g_package||'spp_update_change_insert';
2645: l_effective_start_date DATE;
2646: l_effective_end_date DATE;
2647: l_datetrack_mode VARCHAR2(30);
2648: l_object_version_number per_spinal_point_placements_f.object_version_number%TYPE;
2649: l_dummy_id per_spinal_point_placements_f.placement_id%TYPE;
2650: --
2651: -- Checks to see if future rows exist
2652: --
2645: l_effective_start_date DATE;
2646: l_effective_end_date DATE;
2647: l_datetrack_mode VARCHAR2(30);
2648: l_object_version_number per_spinal_point_placements_f.object_version_number%TYPE;
2649: l_dummy_id per_spinal_point_placements_f.placement_id%TYPE;
2650: --
2651: -- Checks to see if future rows exist
2652: --
2653: CURSOR csr_future_records IS
2651: -- Checks to see if future rows exist
2652: --
2653: CURSOR csr_future_records IS
2654: SELECT spp.placement_id
2655: FROM per_spinal_point_placements_f spp
2656: WHERE spp.assignment_id = p_assignment_id
2657: AND spp.effective_start_date > p_validation_start_date;
2658: --
2659: --
2663: CURSOR csr_update_change_insert_rows IS
2664: SELECT spp.placement_id,
2665: spp.object_version_number,
2666: spp.effective_start_date
2667: FROM per_spinal_point_placements_f spp
2668: WHERE effective_start_date BETWEEN p_spp_eff_start_date
2669: AND p_validation_end_date
2670: AND effective_end_date < p_validation_end_date
2671: AND assignment_id = p_assignment_id
2792: -- ----------------------------------------------------------------------------
2793: --
2794: procedure chk_valid_placement_id
2795: (p_assignment_id in per_all_assignments_f.assignment_id%Type
2796: ,p_placement_id in per_spinal_point_placements_f.placement_id%Type
2797: ,p_validation_start_date in date) is
2798: --
2799: -- Local variables
2800: l_exist varchar2(1);
2802: l_proc varchar(72) := g_package||'chk_valid_placement_id';
2803: -- Fetch future SPP Records(other placement id)
2804: cursor csr_invalid_placement_id is
2805: select 'Y'
2806: from per_spinal_point_placements_f spp
2807: where spp.assignment_id = p_assignment_id
2808: and spp.effective_start_date > p_validation_start_date
2809: and spp.placement_id <> p_placement_id;
2810: --
2865: select spp.placement_id,
2866: spp.object_version_number,
2867: spp.effective_start_date,
2868: spp.effective_end_date
2869: from per_spinal_point_placements_f spp
2870: where spp.assignment_id = p_assignment_id
2871: and spp.effective_start_date >= l_asg_eff_start_date
2872: and spp.effective_end_date <= l_asg_eff_end_date;
2873: -- Cursor to retrive the SPP records without a valid Grade
2877: spp.effective_start_date,
2878: spp.effective_end_date
2879: from per_grade_spines_f pgs,
2880: per_spinal_point_steps_f sps,
2881: per_spinal_point_placements_f spp
2882: where sps.grade_spine_id = pgs.grade_spine_id
2883: and spp.step_id = sps.step_id
2884: and pgs.parent_spine_id = spp.parent_spine_id
2885: and spp.assignment_id = p_assignment_id
2890: cursor csr_asg_spp_error(l_asg_eff_start_date date, l_asg_eff_end_date date) is
2891: select pgs.grade_id
2892: from per_grade_spines_f pgs,
2893: per_spinal_point_steps_f sps,
2894: per_spinal_point_placements_f spp
2895: where sps.grade_spine_id = pgs.grade_spine_id
2896: and spp.step_id = sps.step_id
2897: and pgs.parent_spine_id = spp.parent_spine_id
2898: and spp.assignment_id = p_assignment_id
2904: cursor csr_spp_records is
2905: select spp.placement_id,
2906: spp.effective_start_date,
2907: spp.effective_end_date
2908: from per_spinal_point_placements_f spp
2909: where spp.assignment_id = p_assignment_id
2910: and spp.effective_start_date >= p_validation_start_date;
2911: --
2912: begin
2926: -- Needs to be deleted all non valid SPP records
2927: for asg_spp_rec in csr_asg_spp(csr_asg_rec.effective_start_date,
2928: csr_asg_rec.effective_end_date) loop
2929: -- There are some SPP records existing for this assignment without a Grade.
2930: delete from per_spinal_point_placements_f spp
2931: where spp.placement_id = asg_spp_rec.placement_id
2932: and spp.effective_start_date = asg_spp_rec.effective_start_date
2933: and spp.effective_end_date = asg_spp_rec.effective_end_date;
2934: --
2950: hr_utility.set_location(l_proc||' Asg Grade Id '||csr_asg_rec.grade_id , 50);
2951: end if;
2952: if rec_spp_placement.grade_id <> csr_asg_rec.grade_id then
2953: --
2954: delete from per_spinal_point_placements_f spp
2955: where spp.placement_id = rec_spp_placement.placement_id
2956: and spp.effective_start_date = rec_spp_placement.effective_start_date
2957: and spp.effective_end_date = rec_spp_placement.effective_end_date;
2958: --
3000: hr_api.g_update_change_insert) then
3001: --
3002: for spp_rec in csr_spp_records loop
3003: --
3004: delete from per_spinal_point_placements_f spp
3005: where spp.placement_id = spp_rec.placement_id
3006: and spp.effective_start_date = spp_rec.effective_start_date
3007: and spp.effective_end_date = spp_rec.effective_end_date;
3008: if g_debug then
3025: -- ----------------------------------------------------------------------------
3026: --
3027: procedure delete_next_change_spp
3028: (p_assignment_id in per_all_assignments_f.assignment_id%Type
3029: ,p_placement_id in per_spinal_point_placements_f.placement_id%Type
3030: ,p_grade_id in per_grade_spines_f.grade_id%Type
3031: ,p_datetrack_mode in varchar2
3032: ,p_validation_start_date in date
3033: ,p_validation_end_date in date
3039: l_exists varchar2(1);
3040: l_effective_start_date date;
3041: l_effective_end_date date;
3042: l_del_end_future_spp boolean := false;
3043: l_object_version_number per_spinal_point_placements_f.object_version_number%Type;
3044: l_grade_id per_spinal_point_placements_f.placement_id%Type;
3045: --
3046: l_proc varchar(72) := g_package||'delete_next_change_spp';
3047: -- Cursor used to retrieve all SPP records for a Grade.
3040: l_effective_start_date date;
3041: l_effective_end_date date;
3042: l_del_end_future_spp boolean := false;
3043: l_object_version_number per_spinal_point_placements_f.object_version_number%Type;
3044: l_grade_id per_spinal_point_placements_f.placement_id%Type;
3045: --
3046: l_proc varchar(72) := g_package||'delete_next_change_spp';
3047: -- Cursor used to retrieve all SPP records for a Grade.
3048: cursor csr_update_change_rows is
3049: select spp.placement_id,
3050: spp.effective_start_date,
3051: spp.effective_end_date,
3052: spp.object_version_number
3053: from per_spinal_point_placements_f spp
3054: where spp.effective_end_date between p_validation_start_date - 1
3055: and p_validation_end_date
3056: and spp.effective_end_date < p_validation_end_date
3057: and spp.assignment_id = p_assignment_id
3060: cursor csr_spp_grade is
3061: select pgs.grade_id
3062: from per_grade_spines_f pgs,
3063: per_spinal_point_steps_f sps,
3064: per_spinal_point_placements_f spp
3065: where sps.grade_spine_id = pgs.grade_spine_id
3066: and spp.step_id = sps.step_id
3067: and pgs.parent_spine_id = spp.parent_spine_id
3068: and spp.assignment_id = p_assignment_id
3073: --
3074: cursor csr_ved_continues_spp IS
3075: select spp.placement_id,spp.effective_start_date
3076: ,spp.effective_end_date, spp.object_version_number
3077: from per_spinal_point_placements_f spp
3078: where spp.assignment_id = p_assignment_id
3079: and spp.effective_start_date < p_validation_end_date
3080: and spp.effective_end_date > p_validation_end_date;
3081: --
3082: -- Check for future spp dt records.
3083: --
3084: cursor csr_future_spp_exists(p_date date,p_placement_id number) IS
3085: select 'Y'
3086: from per_spinal_point_placements_f
3087: where placement_id = p_placement_id
3088: and effective_start_date > p_date;
3089: --
3090: begin
3199: -- ----------------------------------------------------------------------------
3200: --
3201: procedure future_change_spp
3202: (p_assignment_id in per_all_assignments_f.assignment_id%Type
3203: ,p_placement_id in per_spinal_point_placements_f.placement_id%Type
3204: ,p_datetrack_mode in varchar2
3205: ,p_validation_start_date in date
3206: ,p_del_end_future_spp out nocopy boolean) is
3207: --
3210: l_spp_eff_end_date date;
3211: l_effective_start_date date;
3212: l_effective_end_date date;
3213: l_del_end_future_spp boolean := false;
3214: l_placement_id per_spinal_point_placements_f.placement_id%Type;
3215: l_object_version_number per_spinal_point_placements_f.object_version_number%Type;
3216: --
3217: l_proc varchar2(72) := g_package||'future_change_spp';
3218: -- As the validation start and end dates are for the asg row being
3211: l_effective_start_date date;
3212: l_effective_end_date date;
3213: l_del_end_future_spp boolean := false;
3214: l_placement_id per_spinal_point_placements_f.placement_id%Type;
3215: l_object_version_number per_spinal_point_placements_f.object_version_number%Type;
3216: --
3217: l_proc varchar2(72) := g_package||'future_change_spp';
3218: -- As the validation start and end dates are for the asg row being
3219: -- deleted, then we need to get the SPP record that belongs to the
3222: select spp.placement_id,
3223: spp.object_version_number,
3224: spp.effective_start_date,
3225: spp.effective_end_date
3226: from per_spinal_point_placements_f spp
3227: where spp.assignment_id = p_assignment_id
3228: and spp.effective_start_date < p_validation_start_date
3229: order by effective_start_date desc;
3230: --
3294: -- ----------------------------------------------------------------------------
3295: --
3296: procedure update_override_spp
3297: (p_assignment_id in per_all_assignments_f.assignment_id%Type
3298: ,p_placement_id in per_spinal_point_placements_f.placement_id%Type
3299: ,p_datetrack_mode in varchar2
3300: ,p_validation_start_date in date
3301: ,p_validation_end_date in date
3302: ,p_spp_eff_start_date in date
3313: l_max_eff_end_date date;
3314: l_grade_id number;
3315: l_datetrack_mode varchar2(30);
3316: l_del_end_future_spp boolean := false;
3317: l_dummy_id per_spinal_point_placements_f.placement_id%Type;
3318: l_previous_ovn per_spinal_point_placements_f.object_version_number%Type;
3319: l_object_version_number per_spinal_point_placements_f.object_version_number%Type
3320: := p_object_version_number;
3321: --
3314: l_grade_id number;
3315: l_datetrack_mode varchar2(30);
3316: l_del_end_future_spp boolean := false;
3317: l_dummy_id per_spinal_point_placements_f.placement_id%Type;
3318: l_previous_ovn per_spinal_point_placements_f.object_version_number%Type;
3319: l_object_version_number per_spinal_point_placements_f.object_version_number%Type
3320: := p_object_version_number;
3321: --
3322: l_proc varchar2(72) := g_package||'update_override_spp';
3315: l_datetrack_mode varchar2(30);
3316: l_del_end_future_spp boolean := false;
3317: l_dummy_id per_spinal_point_placements_f.placement_id%Type;
3318: l_previous_ovn per_spinal_point_placements_f.object_version_number%Type;
3319: l_object_version_number per_spinal_point_placements_f.object_version_number%Type
3320: := p_object_version_number;
3321: --
3322: l_proc varchar2(72) := g_package||'update_override_spp';
3323: -- Assignment record is already updated with UPDATE_OVERRIDE DT Mode
3332: order by paa.effective_start_date;
3333: -- Checks to see if any future Grade Step rows exist for the given assignment
3334: cursor csr_future_records is
3335: select spp.placement_id
3336: from per_spinal_point_placements_f spp
3337: where spp.assignment_id = p_assignment_id
3338: and spp.effective_start_date > p_validation_start_date;
3339: -- Cursor to retrive the past SPP record to perform
3340: -- DELETE_NEXT_CHANGE
3341: cursor csr_past_spp_details(l_placement_id in number) is
3342: select spp.object_version_number,
3343: spp.effective_start_date,
3344: spp.effective_end_date
3345: from per_spinal_point_placements_f spp
3346: where spp.placement_id = l_placement_id
3347: and p_validation_start_date - 1 between spp.effective_start_date
3348: and spp.effective_end_date;
3349: -- Cursor to see if any past Grade Step rows exist for thie given assignment.
3348: and spp.effective_end_date;
3349: -- Cursor to see if any past Grade Step rows exist for thie given assignment.
3350: cursor csr_past_spp_records(l_placement_id in number) is
3351: select spp.object_version_number
3352: from per_spinal_point_placements_f spp
3353: where spp.placement_id = l_placement_id
3354: and spp.effective_start_date < p_validation_start_date
3355: order by effective_start_date desc;
3356: -- Cursor used to retrieve all SPP records for a Grade
3356: -- Cursor used to retrieve all SPP records for a Grade
3357: cursor csr_update_change_rows is
3358: select spp.object_version_number,
3359: spp.effective_start_date
3360: from per_spinal_point_placements_f spp
3361: where spp.effective_start_date between p_spp_eff_start_date
3362: and p_validation_end_date
3363: and spp.effective_end_date < p_validation_end_date
3364: and spp.assignment_id = p_assignment_id
3586: -- ----------------------------------------------------------------------------
3587: --
3588: procedure close_spp_records
3589: (p_assignment_id in per_all_assignments_f.assignment_id%Type
3590: ,p_placement_id in per_spinal_point_placements_f.placement_id%Type
3591: ,p_datetrack_mode in varchar2
3592: ,p_validation_start_date in date
3593: ,p_object_version_number in number
3594: ,p_current_spp_exist in boolean
3597: -- Declare Local Variables
3598: l_effective_start_date date;
3599: l_effective_end_date date;
3600: l_del_end_future_spp boolean := false;
3601: l_dummy_id per_spinal_point_placements_f.placement_id%Type;
3602: l_object_version_number per_spinal_point_placements_f.object_version_number%Type
3603: := p_object_version_number;
3604: --
3605: l_proc varchar(72) := g_package||'close_spp_records';
3598: l_effective_start_date date;
3599: l_effective_end_date date;
3600: l_del_end_future_spp boolean := false;
3601: l_dummy_id per_spinal_point_placements_f.placement_id%Type;
3602: l_object_version_number per_spinal_point_placements_f.object_version_number%Type
3603: := p_object_version_number;
3604: --
3605: l_proc varchar(72) := g_package||'close_spp_records';
3606: -- Checks to see if future rows exist for the Placement_id.
3605: l_proc varchar(72) := g_package||'close_spp_records';
3606: -- Checks to see if future rows exist for the Placement_id.
3607: cursor csr_spp_future_records is
3608: select spp.placement_id
3609: from per_spinal_point_placements_f spp
3610: where spp.assignment_id = p_assignment_id
3611: and spp.placement_id = p_placement_id
3612: and spp.effective_start_date > p_validation_start_date;
3613: -- Cursor to see if any past Grade Step rows exist for thie given assignment.
3612: and spp.effective_start_date > p_validation_start_date;
3613: -- Cursor to see if any past Grade Step rows exist for thie given assignment.
3614: cursor csr_past_spp_records is
3615: select spp.object_version_number
3616: from per_spinal_point_placements_f spp
3617: where spp.placement_id = p_placement_id
3618: and spp.effective_start_date < p_validation_start_date
3619: order by effective_start_date desc;
3620: --
3719: l_effective_end_date date;
3720: l_effective_date date;
3721: l_datetrack_mode varchar2(30);
3722: l_del_end_future_spp boolean := false;
3723: l_object_version_number per_spinal_point_placements_f.object_version_number%Type;
3724: l_vsd_continues_spp_exists boolean :=false;
3725: l_ved_continues_spp_exists boolean :=false;
3726: l_min_step_id number;
3727: l_exists varchar2(1);
3732: --
3733: cursor csr_vsd_continues_spp IS
3734: select spp.placement_id,spp.effective_start_date
3735: ,spp.effective_end_date, spp.object_version_number
3736: from per_spinal_point_placements_f spp
3737: where spp.assignment_id = p_assignment_id
3738: and spp.effective_start_date < p_validation_start_date
3739: -- and spp.effective_end_date > p_validation_start_date;
3740: and spp.effective_end_date >= p_validation_start_date;-- fix for the bug5203227
3743: --
3744: cursor csr_ved_continues_spp IS
3745: select spp.placement_id,spp.effective_start_date
3746: ,spp.effective_end_date, spp.object_version_number
3747: from per_spinal_point_placements_f spp
3748: where spp.assignment_id = p_assignment_id
3749: and spp.effective_start_date < p_validation_end_date
3750: and spp.effective_end_date > p_validation_end_date;
3751: --
3753: --
3754: cursor csr_spps_in_validation_period IS
3755: select spp.placement_id,spp.effective_start_date
3756: ,spp.effective_end_date, spp.object_version_number
3757: from per_spinal_point_placements_f spp
3758: where spp.assignment_id = p_assignment_id
3759: and spp.effective_start_date between p_validation_start_date and p_validation_end_date
3760: order by effective_end_date;
3761: --
3761: --
3762: cursor csr_next_spp(p_date date) IS
3763: select placement_id, effective_start_date,
3764: effective_end_date, object_version_number
3765: from per_spinal_point_placements_f
3766: where placement_id = p_placement_id
3767: -- and effective_start_date >= p_date;--fix for bug 5067855 .
3768: and effective_start_date = p_date; -- fix for the bug 5306697 .
3769: --
3770: -- Check for future spp dt records.
3771: --
3772: cursor csr_future_spp_exists(p_date date) IS
3773: select 'Y'
3774: from per_spinal_point_placements_f
3775: where placement_id = p_placement_id
3776: and effective_start_date > p_date;
3777: --
3778: begin
3959: --
3960: PROCEDURE delete_future_spp_records
3961: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
3962: ,p_datetrack_mode IN VARCHAR2
3963: ,p_placement_id IN per_spinal_point_placements_f.placement_id%TYPE
3964: ,p_object_version_number IN per_spinal_point_placements_f.object_version_number%TYPE
3965: ,p_effective_date IN DATE) IS
3966: --
3967: -- Declare Local Variables
3960: PROCEDURE delete_future_spp_records
3961: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
3962: ,p_datetrack_mode IN VARCHAR2
3963: ,p_placement_id IN per_spinal_point_placements_f.placement_id%TYPE
3964: ,p_object_version_number IN per_spinal_point_placements_f.object_version_number%TYPE
3965: ,p_effective_date IN DATE) IS
3966: --
3967: -- Declare Local Variables
3968: --
3970: l_effective_date DATE;
3971: l_effective_start_date DATE;
3972: l_effective_end_date DATE;
3973: l_previous_end_date DATE;
3974: l_previous_id per_spinal_point_placements_f.placement_id%TYPE;
3975: l_previous_ovn per_spinal_point_placements_f.object_version_number%TYPE;
3976: l_object_version_number per_spinal_point_placements_f.object_version_number%TYPE;
3977: l_placement_id per_spinal_point_placements_f.placement_id%TYPE;
3978: --
3971: l_effective_start_date DATE;
3972: l_effective_end_date DATE;
3973: l_previous_end_date DATE;
3974: l_previous_id per_spinal_point_placements_f.placement_id%TYPE;
3975: l_previous_ovn per_spinal_point_placements_f.object_version_number%TYPE;
3976: l_object_version_number per_spinal_point_placements_f.object_version_number%TYPE;
3977: l_placement_id per_spinal_point_placements_f.placement_id%TYPE;
3978: --
3979: -- Fetch future SPP_Records
3972: l_effective_end_date DATE;
3973: l_previous_end_date DATE;
3974: l_previous_id per_spinal_point_placements_f.placement_id%TYPE;
3975: l_previous_ovn per_spinal_point_placements_f.object_version_number%TYPE;
3976: l_object_version_number per_spinal_point_placements_f.object_version_number%TYPE;
3977: l_placement_id per_spinal_point_placements_f.placement_id%TYPE;
3978: --
3979: -- Fetch future SPP_Records
3980: --
3973: l_previous_end_date DATE;
3974: l_previous_id per_spinal_point_placements_f.placement_id%TYPE;
3975: l_previous_ovn per_spinal_point_placements_f.object_version_number%TYPE;
3976: l_object_version_number per_spinal_point_placements_f.object_version_number%TYPE;
3977: l_placement_id per_spinal_point_placements_f.placement_id%TYPE;
3978: --
3979: -- Fetch future SPP_Records
3980: --
3981: CURSOR csr_future_spp_records IS
3981: CURSOR csr_future_spp_records IS
3982: SELECT spp.placement_id,
3983: spp.object_version_number,
3984: spp.effective_start_date
3985: FROM per_spinal_point_placements_f spp
3986: WHERE spp.assignment_id = p_assignment_id
3987: AND spp.effective_start_date > p_effective_date
3988: AND spp.placement_id <> p_placement_id
3989: ORDER BY placement_id;
3991: -- Cursor to see if past rows exist.
3992: --
3993: CURSOR csr_past_spp_records IS
3994: SELECT spp.placement_id
3995: FROM per_spinal_point_placements_f spp
3996: WHERE spp.placement_id = p_placement_id
3997: AND spp.effective_start_date < p_effective_date;
3998: --
3999: CURSOR csr_previous_spp_record IS
3998: --
3999: CURSOR csr_previous_spp_record IS
4000: SELECT spp.object_version_number,
4001: spp.effective_end_date
4002: FROM per_spinal_point_placements_f spp
4003: WHERE spp.placement_id = p_placement_id
4004: AND spp.effective_start_date < p_effective_date
4005: ORDER BY spp.effective_end_date desc;
4006: --
4139: l_datetrack_mode varchar2(30);
4140: l_current_spp_exist boolean := false;
4141: l_future_spp_exist boolean := false;
4142: l_pay_scale_defined boolean := false;
4143: l_placement_id per_spinal_point_placements_f.placement_id%Type;
4144: l_object_version_number per_spinal_point_placements_f.object_version_number%Type;
4145: l_min_step_id per_spinal_point_steps_f.step_id%Type;
4146: l_grade_spine_id per_grade_spines_f.grade_spine_id%Type;
4147: l_dummy_id per_spinal_point_placements_f.placement_id%Type;
4140: l_current_spp_exist boolean := false;
4141: l_future_spp_exist boolean := false;
4142: l_pay_scale_defined boolean := false;
4143: l_placement_id per_spinal_point_placements_f.placement_id%Type;
4144: l_object_version_number per_spinal_point_placements_f.object_version_number%Type;
4145: l_min_step_id per_spinal_point_steps_f.step_id%Type;
4146: l_grade_spine_id per_grade_spines_f.grade_spine_id%Type;
4147: l_dummy_id per_spinal_point_placements_f.placement_id%Type;
4148: -- This warning variable will be used, whenever system internaly delete's
4143: l_placement_id per_spinal_point_placements_f.placement_id%Type;
4144: l_object_version_number per_spinal_point_placements_f.object_version_number%Type;
4145: l_min_step_id per_spinal_point_steps_f.step_id%Type;
4146: l_grade_spine_id per_grade_spines_f.grade_spine_id%Type;
4147: l_dummy_id per_spinal_point_placements_f.placement_id%Type;
4148: -- This warning variable will be used, whenever system internaly delete's
4149: -- any future dated SPP records or End Date an SPP record whcih is having
4150: -- effective_end_date greater than the validation end date. This warning
4151: -- will be set depends on the DT Mode and the SPP records.
4155: -- We need this check for ZAP mode, all other DT modes we need a date
4156: -- effective check and this will be done by cursor csr_spp_details.
4157: cursor csr_lock_spp_rows is
4158: select spp.placement_id
4159: from per_spinal_point_placements_f spp
4160: where spp.assignment_id = p_assignment_id
4161: for update nowait;
4162: -- Checks to see if any future Grade Step rows exist for the given assignment
4163: cursor csr_future_records is
4161: for update nowait;
4162: -- Checks to see if any future Grade Step rows exist for the given assignment
4163: cursor csr_future_records is
4164: select spp.placement_id
4165: from per_spinal_point_placements_f spp
4166: where spp.assignment_id = p_assignment_id
4167: and spp.effective_start_date > p_validation_start_date;
4168: -- Checks to see if future rows exist for the Placement_id.
4169: cursor csr_spp_future_records(p_placement_id number) is
4167: and spp.effective_start_date > p_validation_start_date;
4168: -- Checks to see if future rows exist for the Placement_id.
4169: cursor csr_spp_future_records(p_placement_id number) is
4170: select spp.placement_id
4171: from per_spinal_point_placements_f spp
4172: where spp.assignment_id = p_assignment_id
4173: and spp.placement_id = p_placement_id
4174: and spp.effective_start_date > p_validation_start_date;
4175: -- Cursor to retrive the current Grade Step placement details
4177: select spp.placement_id,
4178: spp.object_version_number,
4179: spp.effective_start_date,
4180: spp.effective_end_date
4181: from per_spinal_point_placements_f spp
4182: where spp.assignment_id = p_assignment_id
4183: and p_validation_start_date between spp.effective_start_date
4184: and spp.effective_end_date;
4185: -- Cursor to retrieve the first step (Minimum Grade Step) on the pay scale
4207: where grade_id = p_grade_id;
4208: -- Cursor to get the minimum effective_start_date of the SPP records
4209: cursor csr_min_spp_date is
4210: select min(spp.effective_start_date)
4211: from per_spinal_point_placements_f spp
4212: where spp.assignment_id = p_assignment_id;
4213: --
4214: begin
4215: --