[Home] [Help]
2055: begin
2056: hr_utility.set_location('point is '||p_point_id,1);
2057: select parent_spine_id,sequence,spinal_point,object_version_number
2058: into p_scale_id, p_point_seq, p_spinal_point,p_point_ovn
2059: from per_spinal_points
2060: where spinal_point_id = p_point_id;
2061: hr_utility.set_location('scale id is '||p_scale_id,2);
2062: exception
2063: when no_data_found then
2128: into l_spinal_point_id
2129: from ben_opt_f
2130: where opt_id = p_option_id
2131: and p_effective_date between effective_start_date and effective_end_date
2132: and mapping_table_name ='PER_SPINAL_POINTS';
2133: hr_utility.set_location('spinal point id is'||l_spinal_point_id,10);
2134: return l_spinal_point_id;
2135: exception
2136: when no_data_found then
2150: into l_opt_id
2151: from ben_opt_f
2152: where mapping_table_pk_id = p_point_id
2153: and p_effective_date between effective_start_date and effective_end_date
2154: and mapping_table_name ='PER_SPINAL_POINTS';
2155: hr_utility.set_location('opt id is'||l_opt_id,30);
2156: return l_opt_id;
2157: exception
2158: when no_data_found then
3474: l_scale_cer_id number;
3475: l_point_id number;
3476: l_point_seq number;
3477: l_point_ovn number;
3478: l_spinal_point per_spinal_points.spinal_point%type;
3479: l_information_category varchar2(30);
3480: l_information1 varchar2(150);
3481: l_information2 varchar2(150);
3482: l_information3 varchar2(150);
3789: p_business_area in varchar2) is
3790: l_proc varchar2(61) :='step_to_stage';
3791: l_point_id number;
3792: l_point_cer_id number;
3793: l_point_name per_spinal_points.spinal_point%type;
3794: l_step_id number;
3795: l_step_ovn number;
3796: l_step_name number;
3797: l_grade_id number;
5090: l_cop_tr_id number;
5091: l_cop_tr_name varchar2(80);
5092: l_oipl_cer_ovn number;
5093: l_plip_cer_id number;
5094: l_point_name per_spinal_points.spinal_point%type;
5095: l_step_id number;
5096: l_step_ovn number;
5097: l_step_name number;
5098: l_grade_spine_id number;
5441: l_opt_tr_name varchar2(80);
5442: l_scale_id number;
5443: l_point_seq number;
5444: l_point_ovn number;
5445: l_spinal_point per_spinal_points.spinal_point%type;
5446: l_information_category varchar2(30);
5447: l_information1 varchar2(150);
5448: l_information2 varchar2(150);
5449: l_information3 varchar2(150);
5487: ,p_information254 => l_point_ovn
5488: ,p_information255 => l_scale_id
5489: ,p_information256 => p_scale_cer_id
5490: ,p_information257 => p_point_id
5491: ,p_information141 => 'PER_SPINAL_POINTS'
5492: ,p_information173 => l_information1
5493: ,p_information175 => l_information2
5494: ,p_information179 => l_information3
5495: ,p_information181 => l_information4
5538: --bug 6277443 changing the cursor to improve performance
5539:
5540: cursor csr_scale_points is
5541: select spinal_point_id
5542: from per_spinal_points
5543: where parent_spine_id = p_scale_id
5544: and not exists (select 1 from
5545: ben_copy_entity_results
5546: where copy_entity_txn_id = p_copy_entity_txn_id