[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT gvr.hierarchy_version_id
FROM per_gen_hierarchy_versions gvr
,per_gen_hierarchy gh
WHERE gh.type = 'CAREER_PATH'
AND gh.name = 'Corps Carrer Path ' --the seeded hierarchy name has a space in it.
AND gh.hierarchy_id = gvr.hierarchy_id
AND gvr.version_number = 1;
select information_category,information1,information2
into p_information_category,p_information1,p_information2
from per_parent_spines
where parent_spine_id = p_scale_id;
hr_utility.set_location('issues is selecting scale',10);
procedure pgm_extra_info_update(p_pgm_id in number,
p_pgm_extra_info_id in number,
p_quota_flag in varchar2,
p_appraisal_type in varchar2,
p_review_period in number) is
l_peit_ovn number;
hr_utility.set_location('inside pgm_extra_info_update',10);
hr_utility.set_location('insert pgm extra info ',10);
hr_utility.set_location('update pgm extra info',10);
ben_pgm_extra_info_api.update_pgm_extra_info
( p_pgm_extra_info_id => p_pgm_extra_info_id
,p_object_version_number => l_peit_ovn
,p_pgi_information1 => p_quota_flag
,p_pgi_information2 => p_appraisal_type
,p_pgi_information3 => to_char(p_review_period)
);
hr_utility.set_location('leaving pgm_extra_info_update',10);
end pgm_extra_info_update;
SELECT pgm_extra_info_id,pgi_information1,pgi_information2,pgi_information3
FROM ben_pgm_extra_info
where information_type ='PQH_FR_CORP_INFO'
and pgm_id = p_pgm_id;
hr_utility.set_location('issues is selecting pgm extra info',10);
select information_category,information1,information2,information3,information4,information5
into p_information_category,p_information1,p_information2,p_information3,p_information4,p_information5
from per_spinal_points
where spinal_point_id = p_point_id;
hr_utility.set_location('issues is selecting point',10);
select cer.information1,cer.copy_entity_txn_id,cpd.corps_definition_id
into l_pgm_id,p_cet_id,p_corps_id
from ben_copy_entity_results cer, pqh_corps_definitions cpd
where copy_entity_result_id = p_pgm_cer_id
and cpd.ben_pgm_id = cer.information1;
select plip.copy_entity_result_id
from ben_copy_entity_results plip, ben_copy_entity_results pln
where plip.table_alias = 'CPP' -- plip row
and pln.table_alias = 'PLN'
and plip.information261 = pln.information1
and pln.information294 = p_grade_id
and pln.information141 = 'PER_GRADES'
and plip.copy_entity_txn_id = p_cet_id
and pln.copy_entity_txn_id = p_cet_id;
select information253
from ben_copy_entity_results
where copy_entity_result_id = p_plip_cer_id;
SELECT corps_extra_info_id,information4,information6,information7,information8,information30
FROM pqh_corps_extra_info
where information_type ='GRADE'
and corps_definition_id = p_corps_definition_id
and to_number(information3) = p_grade_id;
hr_utility.set_location('issues is selecting quota',10);
select count(*)
into l_cdd_count
from ben_copy_entity_results
where copy_entity_txn_id = p_copy_entity_txn_id
and table_alias = 'CORPS_DOC';
select count(*)
into l_cpd_count
from ben_copy_entity_results
where copy_entity_txn_id = p_copy_entity_txn_id
and table_alias = 'CPD';
select *
from per_gen_hierarchy_nodes
where information4 = p_corps_definition_id
and hierarchy_version_id = l_hierarchy_version_id;
select copy_entity_result_id
into l_step_cer_id
from ben_copy_entity_results
where copy_entity_txn_id = p_copy_entity_txn_id
and table_alias = 'COP'
and information253 = pth_rec.entity_id;
hr_utility.set_location('cer insert api called',55);
select *
from pqh_corps_extra_info
where corps_definition_id = p_corps_definition_id
and information_type = 'DOCUMENT';
hr_utility.set_location('cer insert api called',55);
select *
from pqh_corps_definitions
where ben_pgm_id = p_pgm_id;
select copy_entity_result_id
into l_starting_plip_cer_id
from ben_copy_entity_results
where copy_entity_txn_id = p_copy_entity_txn_id
and table_alias = 'CPP'
and information253 = cpd_rec.starting_grade_id;
hr_utility.set_location('issues in selecting plip',70);
select copy_entity_result_id
into l_starting_oipl_cer_id
from ben_copy_entity_results
where copy_entity_txn_id = p_copy_entity_txn_id
and table_alias = 'COP'
and information253 = cpd_rec.starting_grade_step_id;
hr_utility.set_location('issues in selecting oipl',70);
hr_utility.set_location('cer insert api called',55);
select *
from ben_copy_entity_results
where copy_entity_txn_id = p_copy_entity_txn_id
and table_alias = 'CRPATH'
and dml_operation <> 'REUSE';
update ben_copy_entity_results
set dml_operation = 'DELETE'
where copy_entity_txn_id = p_copy_entity_txn_id
and table_alias in ('CRPATH')
and information104 = 'UNLINK';
select information1
into l_cpd_id
from ben_copy_entity_results
where copy_entity_result_id = pth_rec.information160;
select information253
into l_entity_id
from ben_copy_entity_results
where copy_entity_result_id = pth_rec.information161;
if pth_rec.dml_operation ='INSERT'
and l_pth_id is null
and l_entity_id is not null
and l_hierarchy_version_id is not null
and l_cpd_id is not null then
per_hierarchy_nodes_api.create_hierarchy_nodes
(p_hierarchy_node_id => l_pth_id
,p_business_group_id => p_business_group_id
,p_entity_id => l_entity_id
,p_hierarchy_version_id => l_hierarchy_version_id
,p_object_version_number => l_pth_ovn
,p_node_type => 'CAREER_NODE'
,p_seq => 40
,p_information_category => 'CAREER_NODE'
,p_information3 => pth_rec.information229
,p_information4 => pth_rec.information232
,p_information9 => pth_rec.information227
,p_information10 => pth_rec.information100
,p_information11 => pth_rec.information162
,p_information12 => pth_rec.information169
,p_information13 => pth_rec.information174
,p_information14 => pth_rec.information176
,p_information15 => pth_rec.information178
,p_information16 => pth_rec.information180
,p_information17 => pth_rec.information221
,p_information18 => pth_rec.information222
,p_information19 => pth_rec.information223
,p_information20 => pth_rec.information224
,p_information21 => pth_rec.information225
,p_information22 => pth_rec.information226
,p_information23 => pth_rec.information228
,p_information30 => nvl(pth_rec.information230,p_pgm_id)
,p_effective_date => p_effective_date
);
elsif pth_rec.dml_operation ='UPDATE'
and l_pth_id is not null
and l_hierarchy_version_id is not null
and l_pth_ovn is not null
and l_entity_id is not null
and l_cpd_id is not null then
hr_utility.set_location(' dt mode is '||p_datetrack_mode,30);
per_hierarchy_nodes_api.update_hierarchy_nodes
(p_hierarchy_node_id => l_pth_id
,p_entity_id => pth_rec.information234
,p_object_version_number => l_pth_ovn
,p_node_type => 'CAREER_NODE'
,p_seq => 40
,p_information_category => 'CAREER_NODE'
,p_information3 => pth_rec.information229
,p_information4 => pth_rec.information232
,p_information9 => pth_rec.information227
,p_information10 => pth_rec.information100
,p_information11 => pth_rec.information162
,p_information12 => pth_rec.information169
,p_information13 => pth_rec.information174
,p_information14 => pth_rec.information176
,p_information15 => pth_rec.information178
,p_information16 => pth_rec.information180
,p_information17 => pth_rec.information221
,p_information18 => pth_rec.information222
,p_information19 => pth_rec.information223
,p_information20 => pth_rec.information224
,p_information21 => pth_rec.information225
,p_information22 => pth_rec.information226
,p_information23 => pth_rec.information228
,p_information30 => pth_rec.information230
,p_effective_date => p_effective_date
);
elsif pth_rec.dml_operation ='DELETE'
and l_pth_id is not null
and l_pth_ovn is not null then
per_hierarchy_nodes_api.delete_hierarchy_nodes
(p_hierarchy_node_id => l_pth_id
,p_object_version_number => l_pth_ovn);
select *
from ben_copy_entity_results
where copy_entity_txn_id = p_copy_entity_txn_id
and table_alias = 'CORPS_DOC'
and dml_operation <> 'REUSE';
select information1
into l_cpd_id
from ben_copy_entity_results
where copy_entity_result_id = cdd_rec.GS_MIRROR_SRC_ENTITY_RESULT_ID;
if cdd_rec.dml_operation ='INSERT'
and l_cdd_id is null
and l_cpd_id is not null then
pqh_corps_extra_info_api.create_corps_extra_info
(p_effective_date => p_effective_date
,p_corps_extra_info_id => l_cdd_id
,p_corps_definition_id => l_cpd_id
,p_information_type => 'DOCUMENT'
,p_information3 => cdd_rec.information111
,p_information4 => cdd_rec.information112
,p_information5 => cdd_rec.information113
,p_information6 => cdd_rec.information114
,p_information7 => cdd_rec.information115
,p_object_version_number => l_cdd_ovn
);
elsif cdd_rec.dml_operation ='UPDATE'
and l_cdd_id is not null
and l_cdd_ovn is not null
and l_cpd_id is not null then
hr_utility.set_location(' dt mode is '||p_datetrack_mode,30);
pqh_corps_extra_info_api.update_corps_extra_info
(p_effective_date => p_effective_date
,p_corps_extra_info_id => l_cdd_id
,p_corps_definition_id => l_cpd_id
,p_information3 => cdd_rec.information111
,p_information4 => cdd_rec.information112
,p_information5 => cdd_rec.information113
,p_information6 => cdd_rec.information114
,p_information7 => cdd_rec.information115
,p_object_version_number => l_cdd_ovn
);
elsif cdd_rec.dml_operation ='DELETE'
and l_cdd_id is not null
and l_cdd_ovn is not null then
pqh_corps_extra_info_api.delete_corps_extra_info
(p_corps_extra_info_id => l_cdd_id
,p_object_version_number => l_cdd_ovn);
update ben_copy_entity_results
set information1 = p_cpd_id
where copy_entity_txn_id = p_copy_entity_txn_id
and table_alias = 'CPD';
hr_utility.set_location('num of corp updated'||sql%rowcount,20);
update ben_copy_entity_results
set information232 = p_cpd_id
where copy_entity_txn_id = p_copy_entity_txn_id
and table_alias = 'CRPATH';
hr_utility.set_location('num of crpaths updated'||sql%rowcount,20);
update ben_copy_entity_results
set information291 = p_cpd_id
where copy_entity_txn_id = p_copy_entity_txn_id
and table_alias = 'CPP';
hr_utility.set_location('num of plips updated'||sql%rowcount,20);
update ben_copy_entity_results
set information160 = p_cpd_id
where copy_entity_txn_id = p_copy_entity_txn_id
and table_alias = 'CORPS_DOC';
hr_utility.set_location('num of docs updated'||sql%rowcount,20);
cursor c1 is select corps_extra_info_id
from pqh_corps_extra_info
where corps_definition_id = p_corps_definition_id
and information4 = p_filiere_id
and information_type = 'FILERE';
SELECT *
FROM ben_copy_entity_results
WHERE copy_entity_txn_id = p_copy_entity_txn_id
AND table_alias = 'CPD'
AND dml_operation <> 'REUSE';
SELECT copy_entity_result_id, information253
FROM ben_copy_entity_results
WHERE copy_entity_txn_id = p_copy_entity_txn_id
AND table_alias = 'CPP'
AND information104 <> 'UNLINK'
AND result_type_cd = 'DISPLAY'
ORDER BY information263;
SELECT copy_entity_result_id, information253
FROM ben_copy_entity_results
WHERE copy_entity_txn_id = p_copy_entity_txn_id
AND table_alias = 'COP'
AND information104 <> 'UNLINK'
AND gs_parent_entity_result_id = p_plip_cer_id
AND result_type_cd = 'DISPLAY'
ORDER BY information263;
l_updated BOOLEAN;
l_updated := FALSE;
SELECT DECODE (information38, 'A', 'ACTIVE', 'INACTIVE')
INTO l_status_cd
FROM ben_copy_entity_results
WHERE copy_entity_txn_id = p_copy_entity_txn_id
AND table_alias = 'PGM'
AND dml_operation <> 'REUSE';
l_updated := TRUE;
SELECT information1, information5, information2
INTO l_pgm_id, l_pgm_name, l_pgm_esd
FROM ben_copy_entity_results
WHERE copy_entity_result_id =
cpd_rec.gs_mirror_src_entity_result_id;
('no value selected, get lowest plip grade',
10
);
SELECT information253
INTO l_starting_grade_id
FROM ben_copy_entity_results
WHERE copy_entity_result_id = cpd_rec.information162;
('issues in selecting grade for plip',
10
);
SELECT information253
INTO l_starting_step_id
FROM ben_copy_entity_results
WHERE copy_entity_result_id = cpd_rec.information169;
('issues in selecting step for oipl',
10
);
IF cpd_rec.dml_operation = 'INSERT'
AND l_cpd_id IS NULL
AND l_pgm_id IS NOT NULL
AND l_pgm_esd IS NOT NULL
AND l_pgm_name IS NOT NULL
THEN
pqh_corps_definitions_api.create_corps_definition
(p_effective_date => p_effective_date,
p_date_from => l_pgm_esd,
p_date_to => cpd_rec.information3,
p_business_group_id => p_business_group_id,
p_name => l_pgm_name,
p_type_of_ps => cpd_rec.information11,
p_corps_type_cd => cpd_rec.information14,
p_category_cd => cpd_rec.information13,
p_normal_hours_frequency => cpd_rec.information15,
p_minimum_hours_frequency => l_min_freq,
p_probation_units => cpd_rec.information17,
p_task_desc => cpd_rec.information219,
p_starting_grade_id => l_starting_grade_id,
p_starting_grade_step_id => l_starting_step_id,
p_retirement_age => cpd_rec.information287,
p_secondment_threshold => cpd_rec.information288,
p_normal_hours => cpd_rec.information289,
p_minimum_hours => cpd_rec.information290,
p_probation_period => cpd_rec.information291,
p_primary_prof_field_id => cpd_rec.information292,
p_recruitment_end_date => cpd_rec.information307,
p_status_cd => l_status_cd,
p_ben_pgm_id => l_pgm_id,
p_corps_definition_id => l_cpd_id,
p_object_version_number => l_cpd_ovn
);
pgm_extra_info_update(p_pgm_id => l_pgm_id,
p_pgm_extra_info_id => cpd_rec.information174,
p_quota_flag => cpd_rec.information18,
p_appraisal_type => cpd_rec.information19,
p_review_period => cpd_rec.information221);
ELSIF ( cpd_rec.dml_operation = 'UPDATE'
OR NVL (cpd_rec.information5, 'X') <> l_pgm_name
)
AND l_cpd_id IS NOT NULL
AND l_cpd_ovn IS NOT NULL
AND l_pgm_name IS NOT NULL
AND l_pgm_id IS NOT NULL
THEN
hr_utility.set_location (' dt mode is ' || p_datetrack_mode, 30);
pqh_corps_definitions_api.update_corps_definition
(p_effective_date => p_effective_date,
p_date_from => l_pgm_esd,
p_date_to => cpd_rec.information3,
p_business_group_id => p_business_group_id,
p_name => l_pgm_name,
p_type_of_ps => cpd_rec.information11,
p_corps_type_cd => cpd_rec.information14,
p_category_cd => cpd_rec.information13,
p_normal_hours_frequency => cpd_rec.information15,
p_minimum_hours_frequency => l_min_freq,
p_probation_units => cpd_rec.information17,
p_task_desc => cpd_rec.information219,
p_starting_grade_id => l_starting_grade_id,
p_starting_grade_step_id => l_starting_step_id,
p_retirement_age => cpd_rec.information287,
p_secondment_threshold => cpd_rec.information288,
p_normal_hours => cpd_rec.information289,
p_minimum_hours => cpd_rec.information290,
p_probation_period => cpd_rec.information291,
p_primary_prof_field_id => cpd_rec.information292,
p_recruitment_end_date => cpd_rec.information307,
p_status_cd => l_status_cd,
p_ben_pgm_id => l_pgm_id,
p_corps_definition_id => l_cpd_id,
p_object_version_number => l_cpd_ovn
);
pgm_extra_info_update(p_pgm_id => l_pgm_id,
p_pgm_extra_info_id => cpd_rec.information174,
p_quota_flag => cpd_rec.information18,
p_appraisal_type => cpd_rec.information19,
p_review_period => cpd_rec.information221);
IF l_updated = FALSE
THEN
SELECT information1, information298
INTO l_corps_def_id, l_cpd_ovn
FROM ben_copy_entity_results
WHERE copy_entity_txn_id = p_copy_entity_txn_id
AND table_alias = 'CPD'
AND dml_operation = 'REUSE';
pqh_corps_definitions_api.update_corps_definition
(p_effective_date => p_effective_date,
p_corps_definition_id => l_corps_def_id,
p_status_cd => l_status_cd,
p_object_version_number => l_cpd_ovn
);
procedure grd_quota_update(p_effective_date in date,
p_grade_id in number,
p_corps_definition_id in number,
p_corps_extra_info_id in number,
p_perc_quota in number,
p_population_cd in varchar2,
p_comb_grades in varchar2,
p_max_speed_quota in number,
p_avg_speed_quota in number) is
l_db_ovn number;
hr_utility.set_location('inside grd_quota_update',10);
hr_utility.set_location('insert grd quota ',10);
hr_utility.set_location('update grd quota',10);
pqh_corps_extra_info_api.update_corps_extra_info(
p_effective_date => p_effective_date
,p_corps_extra_info_id => p_corps_extra_info_id
,p_corps_definition_id => p_corps_definition_id
,p_information_type => 'GRADE'
,p_information3 => p_grade_id
,p_information4 => p_perc_quota
,p_information6 => p_max_speed_quota
,p_information7 => p_avg_speed_quota
,p_information8 => p_population_cd
,p_information30 => l_comp_grd
,p_object_version_number => l_db_ovn
);
hr_utility.set_location('leaving grd_quota_update',10);
end grd_quota_update;
procedure update_point(p_point_id in number,
p_point_ovn in out nocopy number,
p_information_category in varchar2,
p_information1 in varchar2,
p_information2 in varchar2,
p_information3 in varchar2,
p_information4 in varchar2,
p_information5 in varchar2,
p_effective_date in date,
p_business_group_id in number,
p_parent_spine_id in number,
p_sequence in number,
p_spinal_point in varchar2) is
begin
hr_progression_point_api.update_progression_point
(p_effective_date => p_effective_date
,p_business_group_id => p_business_group_id
,p_parent_spine_id => p_parent_spine_id
,p_sequence => p_sequence
,p_spinal_point => p_spinal_point
,p_spinal_point_id => p_point_id
,p_object_version_number => p_point_ovn
,p_information_category => p_information_category
,p_information1 => p_information1
,p_information2 => p_information2
,p_information3 => p_information3
,p_information4 => p_information4
,p_information5 => p_information5
,p_called_from => 'GSPW' -- added for bug 9328526
);
end update_point;
procedure update_scale(p_scale_id in number,
p_scale_ovn in out nocopy number,
p_information_category in varchar2,
p_information1 in varchar2,
p_information2 in varchar2,
p_business_group_id in number,
p_name in varchar2,
p_effective_date in date,
p_increment_frequency in number,
p_increment_period in varchar2) is
begin
hr_pay_scale_api.update_pay_scale
(p_business_group_id => p_business_group_id
,p_name => p_name
,p_effective_date => p_effective_date
,p_increment_frequency => p_increment_frequency
,p_increment_period => p_increment_period
,p_parent_spine_id => p_scale_id
,p_object_version_number => p_scale_ovn
,p_information_category => p_information_category
,p_information1 => p_information1
,p_information2 => p_information2
,p_called_from => 'GSPW' -- added for bug 9328526
) ;
end update_scale;