[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
procedure update_cer_with_target(p_copy_entity_txn_id number)
is
l_counter number;
update ben_copy_entity_results
set information9 = ben_pd_copy_to_ben_one.g_pk_tbl(i).copy_reuse_type||'-'||ben_pd_copy_to_ben_one.g_pk_tbl(i).new_value
where copy_entity_txn_id = p_copy_entity_txn_id
and table_route_id = ben_pd_copy_to_ben_one.g_pk_tbl(i).table_route_id
and information1 = ben_pd_copy_to_ben_one.g_pk_tbl(i).old_value ;
update ben_copy_entity_results
set information9 = ben_pd_copy_to_ben_one.g_pk_tbl(i).copy_reuse_type||'-'||ben_pd_copy_to_ben_one.g_pk_tbl(i).new_value
where copy_entity_txn_id = p_copy_entity_txn_id
and table_route_id = ben_pd_copy_to_ben_one.g_pk_tbl(i).table_route_id
and information1 = ben_pd_copy_to_ben_one.g_pk_tbl(i).old_value ;
end update_cer_with_target ;
select transaction_category_id
from pqh_transaction_categories
where short_name = p_proc_typ_cd ;
cursor c_db is select name from v$database ;
select name from per_business_groups
where business_group_id = v_bg_id ;
procedure update_PLAN_DESIGN_TXN
(p_validate in number default 0 --boolean default false
,p_copy_entity_txn_id in number
,p_transaction_category_id in number default hr_API.g_number
,p_txn_category_attribute_id in number default hr_API.g_number
,p_context_business_group_id in number default hr_api.g_number
,p_datetrack_mode in varchar2 default hr_api.g_varchar2
,p_proc_typ_cd in varchar2 default hr_API.g_varchar2
,p_action_date in date default hr_API.g_date
,p_src_effective_date in date default hr_API.g_date
,p_number_of_copies in number default hr_API.g_number
,p_process_name in varchar2 default hr_API.g_varchar2
,p_replacement_type_cd in varchar2 default hr_API.g_varchar2
,p_sfl_step_name in varchar2 default hr_API.g_varchar2
,p_increment_by in number default hr_API.g_number
,p_status in varchar2 default hr_API.g_varchar2
,p_cet_object_version_number in out nocopy number
,p_effective_date in date
,p_copy_entity_attrib_id in number
,p_row_type_cd in varchar2 default hr_api.g_varchar2
,p_information_category in varchar2 default hr_api.g_varchar2
,p_prefix_suffix_text in varchar2 default hr_api.g_varchar2
,p_export_file_name in varchar2 default hr_api.g_varchar2
,p_target_typ_cd in varchar2 default hr_api.g_varchar2
,p_reuse_object_flag in varchar2 default hr_api.g_varchar2
,p_target_business_group_id in varchar2 default hr_api.g_varchar2
,p_search_by_cd1 in varchar2 default hr_api.g_varchar2
,p_search_value1 in varchar2 default hr_api.g_varchar2
,p_search_by_cd2 in varchar2 default hr_api.g_varchar2
,p_search_value2 in varchar2 default hr_api.g_varchar2
,p_search_by_cd3 in varchar2 default hr_api.g_varchar2
,p_search_value3 in varchar2 default hr_api.g_varchar2
,p_prefix_suffix_cd in varchar2 default hr_api.g_varchar2
,p_information13 in varchar2 default hr_api.g_varchar2
,p_information14 in varchar2 default hr_api.g_varchar2
,p_information15 in varchar2 default hr_api.g_varchar2
,p_information16 in varchar2 default hr_api.g_varchar2
,p_information17 in varchar2 default hr_api.g_varchar2
,p_information18 in varchar2 default hr_api.g_varchar2
,p_information19 in varchar2 default hr_api.g_varchar2
,p_information20 in varchar2 default hr_api.g_varchar2
,p_information21 in varchar2 default hr_api.g_varchar2
,p_information22 in varchar2 default hr_api.g_varchar2
,p_information23 in varchar2 default hr_api.g_varchar2
,p_information24 in varchar2 default hr_api.g_varchar2
,p_information25 in varchar2 default hr_api.g_varchar2
,p_information26 in varchar2 default hr_api.g_varchar2
,p_information27 in varchar2 default hr_api.g_varchar2
,p_information28 in varchar2 default hr_api.g_varchar2
,p_information29 in varchar2 default hr_api.g_varchar2
,p_information30 in varchar2 default hr_api.g_varchar2
,p_upd_record_type in varchar2 default null
,p_cea_object_version_number in out nocopy number
) is
--
-- Declare cursors and local variables
--
l_proc varchar2(72) := g_package||'update_PLAN_DESIGN_TXN';
select information5
from pqh_copy_entity_attribs
where copy_entity_attrib_id = p_copy_entity_attrib_id
-- and information5 <> p_target_business_group_id
and copy_entity_txn_id = p_copy_entity_txn_id ;
select unique table_route_id
from ben_copy_entity_results
where copy_entity_txn_id = p_copy_entity_txn_id
-- Only take unmapped rows.
and (information176 is null or
(information180 is not null and information176 is null));
select bg.legislation_code
from per_business_groups bg
where bg.business_group_id = p_target_business_group_id; -- 9999
select table_route_id
from pqh_table_route tr
where tr.table_alias = 'PLN' and where_clause = 'BEN_PL_F' ;
savepoint update_PLAN_DESIGN_TXN;
pqh_copy_entity_txns_api.update_COPY_ENTITY_TXN
(
p_copy_entity_txn_id => p_copy_entity_txn_id
,p_transaction_category_id => p_transaction_category_id
,p_txn_category_attribute_id => p_txn_category_attribute_id
,p_context_business_group_id => p_context_business_group_id
,p_datetrack_mode => p_datetrack_mode
-- ,p_context => p_proc_typ_cd
,p_action_date => p_action_date
,p_src_effective_date => p_src_effective_date
,p_number_of_copies => p_number_of_copies
,p_display_name => p_process_name
,p_replacement_type_cd => p_replacement_type_cd
,p_start_with => p_sfl_step_name
,p_increment_by => p_increment_by
,p_status => p_status
,p_object_version_number => l_cet_object_version_number
,p_effective_date => trunc(p_effective_date)
);
/* No need to update all the rows
update ben_copy_entity_results
set information175 = null,
information176 = null,
information179 = null,
information180 = null
where copy_entity_txn_id = p_copy_entity_txn_id
and table_route_id <> l_table_route_id ; */
update ben_copy_entity_results
set information175 = null,
information176 = null,
information179 = null,
information180 = null
where copy_entity_txn_id = p_copy_entity_txn_id
and (information175 is not null
or information176 is not null
or information179 is not null
or information180 is not null)
and table_route_id <> l_table_route_id ;
pqh_copy_entity_attribs_api.update_copy_entity_attrib
(p_copy_entity_attrib_id => p_copy_entity_attrib_id
,p_copy_entity_txn_id => p_copy_entity_txn_id
,p_row_type_cd => p_row_type_cd
,p_information_category => p_information_category
,p_information1 => p_prefix_suffix_text
,p_information2 => p_export_file_name
,p_information3 => p_target_typ_cd
,p_information4 => p_reuse_object_flag
,p_information5 => p_target_business_group_id
,p_information6 => p_search_by_cd1
,p_information7 => p_search_value1
,p_information8 => p_search_by_cd2
,p_information9 => p_search_value2
,p_information10 => p_search_by_cd3
,p_information11 => p_search_value3
,p_information12 => p_prefix_suffix_cd
,p_information13 => p_information13
,p_information14 => p_information14
,p_information15 => p_information15
,p_information16 => p_information16
,p_information17 => p_information17
,p_information18 => p_information18
,p_information19 => p_information19
,p_information20 => p_information20
,p_information21 => p_information21
,p_information22 => p_information22
,p_information23 => p_information23
,p_information24 => p_information24
,p_information25 => p_information25
,p_information26 => p_information26
,p_information27 => p_information27
,p_information28 => p_information28
,p_information29 => p_information29
,p_information30 => p_information30
,p_object_version_number => l_cea_object_version_number
,p_effective_date => p_effective_date
);
ROLLBACK TO update_PLAN_DESIGN_TXN;
ROLLBACK TO update_PLAN_DESIGN_TXN;
end update_PLAN_DESIGN_TXN;
procedure delete_PLAN_DESIGN_TXN
(p_validate in number default 0 -- default false
,p_copy_entity_txn_id in number
,p_cet_object_version_number in number
,p_effective_date in date
,p_retain_log in varchar2 default 'N' -- Bug No 4281567
) is
--
-- Declare cursors and local variables
--
l_proc varchar2(72) := g_package||'delete_PLAN_DESIGN_TXN';
select copy_entity_attrib_id,cea_object_version_number
from ben_copy_entity_txns_vw
where copy_entity_txn_id = p_copy_entity_txn_id;
select cer.*
from ben_copy_entity_results cer
where COPY_ENTITY_TXN_ID = p_COPY_ENTITY_TXN_ID;
savepoint delete_PLAN_DESIGN_TXN;
BEN_PLAN_DESIGN_TXNS_API.delete_plan_design_result
( p_validate => p_validate
,p_copy_entity_txn_id => p_copy_entity_txn_id
,p_effective_date => p_effective_date
);
PQH_COPY_ENTITY_ATTRIBS_api.delete_COPY_ENTITY_ATTRIB
(p_validate => false
,p_copy_entity_attrib_id => l_copy_entity_attrib_id
,p_object_version_number => l_cea_object_version_number
,p_effective_date => p_effective_date
);
delete from pqh_process_log
where txn_id = p_copy_entity_txn_id
and module_cd = 'PDC_CP';
PQH_COPY_ENTITY_TXNS_api.delete_COPY_ENTITY_TXN
(p_validate => false
,p_copy_entity_txn_id => p_copy_entity_txn_id
,p_object_version_number => l_object_version_number
,p_effective_date => p_effective_date
);
PQH_COPY_ENTITY_TXNS_api.update_COPY_ENTITY_TXN
(p_validate => false
,p_datetrack_mode => hr_api.g_correction
,p_copy_entity_txn_id => p_copy_entity_txn_id
,p_start_with => null
,p_status => 'PURGED'
,p_object_version_number => l_object_version_number
,p_effective_date => sysdate
);
ROLLBACK TO delete_PLAN_DESIGN_TXN;
ROLLBACK TO delete_PLAN_DESIGN_TXN;
end delete_PLAN_DESIGN_TXN;
update ben_copy_entity_results
set information8 = NULL
where information1 = p_pl_id
and copy_entity_txn_id = p_copy_entity_txn_id
and table_alias = 'PLN';
procedure update_child_object_selection(
p_mirror_entity_result_id in number
,p_copy_entity_txn_id in number
,p_number_of_copies in number ) is
cursor c_child_object is
select /*+ INDEX ( cer, ben_copy_entity_results_fk1) */
cer.copy_entity_result_id
,cer.mirror_entity_result_id
from ben_copy_entity_results cer
where cer.mirror_src_entity_result_id = p_mirror_entity_result_id
and cer.copy_entity_txn_id = p_copy_entity_txn_id;
update ben_copy_entity_results
set number_of_copies = p_number_of_copies
where copy_entity_result_id = l_child_object_rec.copy_entity_result_id;
update_child_object_selection(
p_mirror_entity_result_id => l_child_object_rec.mirror_entity_result_id
,p_copy_entity_txn_id => p_copy_entity_txn_id
,p_number_of_copies => p_number_of_copies);
end update_child_object_selection;
procedure update_hgrid_child_selection(
p_copy_entity_result_id in number
,p_mirror_entity_result_id in number
,p_copy_entity_txn_id in number
,p_number_of_copies in number
,p_table_route_id in number) is
cursor c_table_name is
select where_clause
from pqh_table_route
where table_route_id = p_table_route_id;
if p_number_of_copies in (0,2) then -- If Plip is de-selected update Plip and child records
update ben_copy_entity_results
set number_of_copies = p_number_of_copies
where copy_entity_result_id = p_copy_entity_result_id;
update_child_object_selection(
p_mirror_entity_result_id => p_mirror_entity_result_id
,p_copy_entity_txn_id => p_copy_entity_txn_id
,p_number_of_copies => p_number_of_copies);
else --Plip can be selected only if Program is selected
null;
update_child_object_selection(
p_mirror_entity_result_id => p_mirror_entity_result_id
,p_copy_entity_txn_id => p_copy_entity_txn_id
,p_number_of_copies => p_number_of_copies);
end update_hgrid_child_selection;
procedure update_plan_design_result
(
p_validate in number default 0 -- false
,p_copy_entity_result_id in number
,p_copy_entity_txn_id in number
,p_business_group_id in number default hr_api.g_number
,p_number_of_copies in number default hr_api.g_number
,p_object_version_number in out nocopy number
,p_effective_date in date
,p_information1 in varchar2 default hr_api.g_varchar2
,p_information8 in varchar2 default hr_api.g_varchar2
,p_information175 in varchar2 default hr_api.g_varchar2
,p_information176 in varchar2 default hr_api.g_varchar2
,p_information177 in varchar2 default hr_api.g_varchar2
,p_information178 in varchar2 default hr_api.g_varchar2
,p_information179 in varchar2 default hr_api.g_varchar2
,p_information180 in varchar2 default hr_api.g_varchar2
,p_called_from in varchar2 default hr_api.g_varchar2
,p_mirror_entity_result_id in number default hr_api.g_number
) is
l_number_of_copies number(15);
l_proc varchar2(72) := g_package||'update_plan_design_result';
select null
from ben_copy_entity_results cer,
pqh_table_route trt
where cer.information1 = c_pgm_id
and cer.copy_entity_txn_id = c_copy_entity_txn_id
and cer.table_route_id = trt.table_route_id
and trt.where_clause = 'BEN_PGM_F'
and trt.from_clause = 'OAB'
and exists ( select /*+ INDEX ( cer1, ben_copy_entity_results_fk1) */ null
from
ben_copy_entity_results cer1
where cer1.mirror_src_entity_result_id = cer.copy_entity_result_id
and cer1.copy_entity_txn_id = cer.copy_entity_txn_id ) ;
select null
from ben_copy_entity_results cer,
pqh_table_route trt
where cer.information1 = c_pl_id
and cer.copy_entity_txn_id = c_copy_entity_txn_id
and cer.table_route_id = trt.table_route_id
and trt.where_clause = 'BEN_PL_F'
and trt.from_clause = 'OAB'
and exists ( select /*+ INDEX ( cer1, ben_copy_entity_results_fk1) */ null from
ben_copy_entity_results cer1
where cer1.mirror_src_entity_result_id = cer.copy_entity_result_id
and cer1.copy_entity_txn_id = cer.copy_entity_txn_id ) ;
select cer.object_version_number
from ben_copy_entity_results cer
where cer.copy_entity_result_id = c_copy_entity_result_id;
select cer.copy_entity_result_id,cer.object_version_number
from ben_copy_entity_results cer,
pqh_table_route trt
where cer.information1 = c_information1
and trt.where_clause = c_table_name
and cer.copy_entity_txn_id = c_copy_entity_txn_id
and cer.table_route_id = trt.table_route_id
and cer.result_type_cd <> 'DISPLAY' ;
ben_copy_entity_results_api.update_copy_entity_results
( p_validate => false
,p_copy_entity_result_id => p_copy_entity_result_id
,p_copy_entity_txn_id => p_copy_entity_txn_id
,p_number_of_copies => l_number_of_copies
,p_object_version_number => l_cer_object_version_number
,p_effective_date => p_effective_date
,p_information323 => null
);
ben_copy_entity_results_api.update_copy_entity_results
( p_validate => false
,p_copy_entity_result_id => r_non_dsply_recs.copy_entity_result_id
,p_copy_entity_txn_id => p_copy_entity_txn_id
,p_number_of_copies => l_number_of_copies
,p_object_version_number => r_non_dsply_recs.object_version_number
,p_effective_date => p_effective_date
,p_information323 => null
);
if p_called_from = 'SELECTION' then
if l_number_of_copies = 1 then
--
if p_information8 = 'PGM' then
--
l_pgm_id := p_information1;
update_child_object_selection(
p_mirror_entity_result_id => p_mirror_entity_result_id
,p_copy_entity_txn_id => p_copy_entity_txn_id
,p_number_of_copies => p_number_of_copies);
update_child_object_selection(
p_mirror_entity_result_id => p_mirror_entity_result_id
,p_copy_entity_txn_id => p_copy_entity_txn_id
,p_number_of_copies => p_number_of_copies);
end update_plan_design_result ;
procedure delete_plan_design_result
(
p_validate in number default 0 -- false
,p_copy_entity_txn_id in number
,p_effective_date in date
) is
l_proc varchar2(72) := g_package||'delete_plan_design_result';
select copy_entity_result_id,
object_version_number
from ben_copy_entity_results cpe
where cpe.copy_entity_txn_id = c_copy_entity_txn_id;
select copy_entity_result_id,
object_version_number
from pqh_copy_entity_results cer
where cer.copy_entity_txn_id = c_copy_entity_txn_id;
pqh_copy_entity_results_api.delete_copy_entity_result
(p_validate => false
,p_copy_entity_result_id => l_cer.copy_entity_result_id
,p_object_version_number => l_cer.object_version_number
,p_effective_date => p_effective_date
) ;
ben_copy_entity_results_api.delete_copy_entity_results
(p_validate => false
,p_copy_entity_result_id => l_cpe.copy_entity_result_id
,p_object_version_number => l_cpe.object_version_number
,p_effective_date => p_effective_date
) ;
end delete_plan_design_result;
select information173,
information174,
information175,
information176,
information177,
information178,
information179,
information180,
information166,
information306
from ben_copy_entity_results
where table_route_id = p_table_route_id
and copy_entity_txn_id = p_copy_entity_txn_id
-- Only take unmapped rows.
and (information176 is null or
(information180 is not null and information176 is null));
select WHERE_CLAUSE
from pqh_table_route
where table_route_id = p_table_route_id;
select assignment_set_name name, assignment_set_id id
from hr_assignment_sets
where business_group_id = p_target_business_group_id
and assignment_set_name = p_source_name1;
select hl.meaning name,
abr.abs_attendance_reason_id id
from per_abs_attendance_reasons abr,
hr_leg_lookups hl
where abr.name = hl.lookup_code and
hl.lookup_type = 'ABSENCE_REASON' and
hl.enabled_flag = 'Y'
and meaning = p_source_name1
and absence_attendance_type_id = p_absence_attendance_type_id
and trunc(cv_effective_date)
between Start_Date_Active and
nvl(End_Date_Active, trunc(cv_effective_date) )
and business_group_id = p_target_business_group_id;
select abt.name Name,
abt.absence_attendance_type_id id
from per_absence_attendance_types abt
where trunc(cv_effective_date)
between Date_Effective and nvl(Date_end, trunc(cv_effective_date))
and business_group_id = p_target_business_group_id
and abt.name = p_source_name1;
select name name
,competence_id id
from per_competences_vl
where trunc(cv_effective_date)
between Date_from and nvl(Date_to, trunc(cv_effective_date) )
and nvl(business_group_id , p_target_business_group_id) = p_target_business_group_id
and name = p_source_name1;
select pbt.balance_name||' - '||pbd.dimension_name name ,
pdb.defined_balance_id id
from pay_balance_types pbt,
pay_balance_dimensions pbd,
pay_defined_balances pdb
where pdb.balance_type_id = pbt.balance_type_id
and pdb.balance_dimension_id = pbd.balance_dimension_id
and nvl(pdb.business_group_id, p_target_business_group_id) = p_target_business_group_id
and pbt.balance_name||' - '||pbd.dimension_name = p_source_name1;
select nvl(atl.user_status, stl.user_status) name
, s.assignment_status_type_id id
from per_assignment_status_types s,
per_ass_status_type_amends a,
per_assignment_status_types_tl stl,
per_ass_status_type_amends_tl atl
where a.assignment_status_type_id (+) = s.assignment_status_type_id
and a.business_group_id (+) = p_target_business_group_id
and nvl(s.business_group_id, p_target_business_group_id) = p_target_business_group_id
and nvl(s.legislation_code, p_legislation_code) = p_legislation_code
and nvl(a.active_flag, s.active_flag) = 'Y'
and atl.ass_status_type_amend_id (+) = a.ass_status_type_amend_id
and atl.language (+) = userenv('LANG')
and stl.assignment_status_type_id = s.assignment_status_type_id
and stl.language = userenv('LANG')
and nvl(atl.user_status, stl.user_status) = p_source_name1;
select pivt.name name,
pivt.input_value_id id
from pay_input_values_f piv,
pay_input_values_f_tl pivt
where trunc(cv_effective_date)
between piv.effective_start_date and piv.effective_end_date
and ((piv.business_group_id is null and nvl(piv.legislation_code, p_legislation_code)
= p_legislation_code)or piv.business_group_id = p_target_business_group_id )
and piv.input_value_id = pivt.input_value_id
and pivt.language = userenv('LANG')
and pivt.name = p_source_name1
and element_type_id = p_element_type_id;
select displayed_name name,
job_group_id id
from per_job_groups
where business_group_id = p_target_business_group_id
and displayed_name = p_source_name1;
select jobtl.name name ,
job.job_id id
from per_jobs job
,per_jobs_tl jobtl
where trunc(cv_effective_date)
between Date_from and nvl(Date_to, trunc(cv_effective_date))
and job.business_group_id + 0 = p_target_business_group_id
and job.job_id = jobtl.job_id
and jobtl.language = userenv('LANG')
and jobtl.name = p_source_name1;
select txu.name name,
txu.tax_unit_id id
from hr_tax_units_v txu
where trunc(cv_effective_date)
between Date_from and nvl(Date_to, trunc(cv_effective_date))
and p_target_business_group_id = txu.business_group_id
and txu.name = p_source_name1;
select loc.location_code name,
loc.location_id id
from hr_locations loc
where trunc(cv_effective_date) <= nvl( loc.inactive_date, trunc(cv_effective_date))
and nvl(business_group_id,p_target_business_group_id) = p_target_business_group_id
and loc.location_code = p_source_name1;
select
opt.name Name ,
Opt.opt_id
from ben_opt_f opt
where opt.opt_id = opt.group_opt_id
and opt.name = cv_name
and trunc(cv_effective_date) between
opt.effective_start_date and opt.effective_end_date;
select
bp.name name,
bp.pl_id id
FROM
ben_pl_f bp,
ben_pl_typ_f bpt
WHERE
bp.pl_id = bp.group_pl_id
and bp.name = cv_name
and bp.pl_typ_id = bpt.pl_typ_id
and bpt.opt_typ_cd = 'CWB'
and trunc(cv_effective_date) between bp.effective_start_date and bp.effective_end_date
and trunc(cv_effective_date) between bpt.effective_start_date and bpt.effective_end_date
and bp.business_group_id = bpt.business_group_id;
select orgtl.name name,
org.organization_id id
from hr_all_organization_units org,
hr_all_organization_units_tl orgtl
where org.business_group_id = p_target_business_group_id
and orgtl.organization_id = org.organization_id
and orgtl.language = userenv('LANG')
and org.internal_external_flag = 'INT'
and trunc(cv_effective_date)
between nvl(org.date_from, trunc(cv_effective_date))
and nvl(org.date_to, trunc(cv_effective_date))
and orgtl.name = p_source_name1;
select name,
pay_basis_id id
from per_pay_bases
where business_group_id = p_target_business_group_id
and name = p_source_name1;
select prl.payroll_name name /* cg$fk */ ,
prl.payroll_id id
from pay_all_payrolls_f prl
where trunc(cv_effective_date)
between prl.effective_start_date and prl.effective_end_date
and prl.business_group_id + 0 = p_target_business_group_id
and prl.payroll_name = p_source_name1;
select ptl.user_person_type name,
ppt.person_type_id id
from per_person_types ppt,
hr_leg_lookups hrlkup,
per_person_types_tl ptl
where active_flag = 'Y' and
hrlkup.lookup_type = 'PERSON_TYPE'
and hrlkup.lookup_code = ppt.system_person_type
and ppt.active_flag = 'Y'
and business_group_id = p_target_business_group_id
and ppt.person_type_id = ptl.person_type_id
and ptl.language = userenv('LANG')
and ptl.user_person_type = p_source_name1;
select org.name name,
org.organization_id id
from hr_organization_units org
where trunc(cv_effective_date)
between Date_from and nvl(Date_to, trunc(cv_effective_date))
and org.business_group_id +0 = p_target_business_group_id
and org.name = p_source_name1;
select name,
position_id id
from per_positions
where trunc(cv_effective_date) >= date_effective
and business_group_id = p_target_business_group_id
and name = p_source_name1;
select pos.NAME name,
POS_STRUCTURE_VERSION_ID id
from PER_POSITION_STRUCTURES pos,
PER_POS_STRUCTURE_VERSIONS pov
where pos.POSITION_STRUCTURE_ID = pov.POSITION_STRUCTURE_ID
and trunc(cv_effective_date)
between POV.DATE_FROM and nvl(POV.DATE_TO, trunc(cv_effective_date))
and pos.business_group_id = p_target_business_group_id
and pos.NAME = p_source_name1;
select name, qualification_type_id id
from per_qualification_types_tl pqttl
where pqttl.language = userenv('LANG')
and name = p_source_name1;
select rtl.name,
rtl.rating_level_id id
from per_rating_levels_vl rtl, /* MLS Changes*/
per_competences pct
where (rtl.competence_id = pct.competence_id
or rtl.rating_scale_id = pct.rating_scale_id )
and pct.competence_id = p_competence_id
and nvl(rtl.business_group_id, p_target_business_group_id ) = p_target_business_group_id
and rtl.name = p_source_name1;
select pett.element_name name
,pett.element_type_id id
from pay_element_types_f pet,
pay_element_types_f_tl pett
where trunc(cv_effective_date)
between nvl(pet.effective_start_date,trunc(cv_effective_date)) and nvl
(pet.effective_end_date,trunc(cv_effective_date ))
and pet.element_type_id=pett.element_type_id
and pett.language = userenv('LANG')
and ( (pet.business_group_id is null and nvl(pet.legislation_code, p_legislation_code) = p_legislation_code)
or pet.business_group_id = p_target_business_group_id)
and pett.element_name = p_source_name1;
select gra.name name
,gra.grade_id id
from per_grades_vl gra /*MLS Changes*/
where trunc(cv_effective_date)
between Date_from and nvl(Date_to, trunc(cv_effective_date) )
and business_group_id + 0 = p_target_business_group_id
and gra.name = p_source_name1;
update_mapping_target_data (
p_table_route_id =>p_table_route_id
, p_copy_entity_txn_id =>p_copy_entity_txn_id
, p_source_id1 =>l_ret.information174
, p_target_value1 =>l_ret.information175
, p_target_id1 =>l_ret.information176
, p_source_id2 =>l_ret.information178
, p_target_value2 =>l_ret.information179
, p_target_id2 =>l_ret.information180
);
update_mapping_target_data (
p_table_route_id =>p_table_route_id
, p_copy_entity_txn_id =>p_copy_entity_txn_id
, p_source_id1 =>l_ret.information174
, p_target_value1 =>l_ret.information175
, p_target_id1 =>l_ret.information176
, p_source_id2 =>l_ret.information178
, p_target_value2 =>l_ret.information179
, p_target_id2 =>l_ret.information180
);
update_mapping_target_data (
p_table_route_id =>p_table_route_id
, p_copy_entity_txn_id =>p_copy_entity_txn_id
, p_source_id1 =>l_ret.information174
, p_target_value1 =>l_ret.information175
, p_target_id1 =>l_ret.information176
, p_source_id2 =>l_ret.information178
, p_target_value2 =>l_ret.information179
, p_target_id2 =>l_ret.information180
);
update_mapping_target_data (
p_table_route_id =>p_table_route_id
, p_copy_entity_txn_id =>p_copy_entity_txn_id
, p_source_id1 =>l_ret.information174
, p_target_value1 =>l_ret.information175
, p_target_id1 =>l_ret.information176
, p_source_id2 =>l_ret.information178
, p_target_value2 =>l_ret.information179
, p_target_id2 =>l_ret.information180
);
update_mapping_target_data (
p_table_route_id =>p_table_route_id
, p_copy_entity_txn_id =>p_copy_entity_txn_id
, p_source_id1 =>l_ret.information174
, p_target_value1 =>l_ret.information175
, p_target_id1 =>l_ret.information176
, p_source_id2 =>l_ret.information178
, p_target_value2 =>l_ret.information179
, p_target_id2 =>l_ret.information180
);
update_mapping_target_data (
p_table_route_id =>p_table_route_id
, p_copy_entity_txn_id =>p_copy_entity_txn_id
, p_source_id1 =>l_ret.information174
, p_target_value1 =>l_ret.information175
, p_target_id1 =>l_ret.information176
, p_source_id2 =>l_ret.information178
, p_target_value2 =>l_ret.information179
, p_target_id2 =>l_ret.information180
);
update_mapping_target_data (
p_table_route_id =>p_table_route_id
, p_copy_entity_txn_id =>p_copy_entity_txn_id
, p_source_id1 =>l_ret.information174
, p_target_value1 =>l_ret.information175
, p_target_id1 =>l_ret.information176
, p_source_id2 =>l_ret.information178
, p_target_value2 =>l_ret.information179
, p_target_id2 =>l_ret.information180
);
update_mapping_target_data (
p_table_route_id =>p_table_route_id
, p_copy_entity_txn_id =>p_copy_entity_txn_id
, p_source_id1 =>l_ret.information174
, p_target_value1 =>l_ret.information175
, p_target_id1 =>l_ret.information176
, p_source_id2 =>l_ret.information178
, p_target_value2 =>l_ret.information179
, p_target_id2 =>l_ret.information180
);
update_mapping_target_data (
p_table_route_id =>p_table_route_id
, p_copy_entity_txn_id =>p_copy_entity_txn_id
, p_source_id1 =>l_ret.information174
, p_target_value1 =>l_ret.information175
, p_target_id1 =>l_ret.information176
, p_source_id2 =>l_ret.information178
, p_target_value2 =>l_ret.information179
, p_target_id2 =>l_ret.information180
);
update_mapping_target_data (
p_table_route_id =>p_table_route_id
, p_copy_entity_txn_id =>p_copy_entity_txn_id
, p_source_id1 =>l_ret.information174
, p_target_value1 =>l_ret.information175
, p_target_id1 =>l_ret.information176
, p_source_id2 =>l_ret.information178
, p_target_value2 =>l_ret.information179
, p_target_id2 =>l_ret.information180
);
update_mapping_target_data (
p_table_route_id =>p_table_route_id
, p_copy_entity_txn_id =>p_copy_entity_txn_id
, p_source_id1 =>l_ret.information174
, p_target_value1 =>l_ret.information175
, p_target_id1 =>l_ret.information176
, p_source_id2 =>l_ret.information178
, p_target_value2 =>l_ret.information179
, p_target_id2 =>l_ret.information180
);
update_mapping_target_data (
p_table_route_id =>p_table_route_id
, p_copy_entity_txn_id =>p_copy_entity_txn_id
, p_source_id1 =>l_ret.information174
, p_target_value1 =>l_ret.information175
, p_target_id1 =>l_ret.information176
, p_source_id2 =>l_ret.information178
, p_target_value2 =>l_ret.information179
, p_target_id2 =>l_ret.information180
);
update_mapping_target_data (
p_table_route_id =>p_table_route_id
, p_copy_entity_txn_id =>p_copy_entity_txn_id
, p_source_id1 =>l_ret.information174
, p_target_value1 =>l_ret.information175
, p_target_id1 =>l_ret.information176
, p_source_id2 =>l_ret.information178
, p_target_value2 =>l_ret.information179
, p_target_id2 =>l_ret.information180
);
update_mapping_target_data (
p_table_route_id =>p_table_route_id
, p_copy_entity_txn_id =>p_copy_entity_txn_id
, p_source_id1 =>l_ret.information174
, p_target_value1 =>l_ret.information175
, p_target_id1 =>l_ret.information176
, p_source_id2 =>l_ret.information178
, p_target_value2 =>l_ret.information179
, p_target_id2 =>l_ret.information180
);
update_mapping_target_data (
p_table_route_id =>p_table_route_id
, p_copy_entity_txn_id =>p_copy_entity_txn_id
, p_source_id1 =>l_ret.information174
, p_target_value1 =>l_ret.information175
, p_target_id1 =>l_ret.information176
, p_source_id2 =>l_ret.information178
, p_target_value2 =>l_ret.information179
, p_target_id2 =>l_ret.information180
);
update_mapping_target_data (
p_table_route_id =>p_table_route_id
, p_copy_entity_txn_id =>p_copy_entity_txn_id
, p_source_id1 =>l_ret.information174
, p_target_value1 =>l_ret.information175
, p_target_id1 =>l_ret.information176
, p_source_id2 =>l_ret.information178
, p_target_value2 =>l_ret.information179
, p_target_id2 =>l_ret.information180
);
update_mapping_target_data (
p_table_route_id =>p_table_route_id
, p_copy_entity_txn_id =>p_copy_entity_txn_id
, p_source_id1 =>l_ret.information174
, p_target_value1 =>l_ret.information175
, p_target_id1 =>l_ret.information176
, p_source_id2 =>l_ret.information178
, p_target_value2 =>l_ret.information179
, p_target_id2 =>l_ret.information180
);
update_mapping_target_data (
p_table_route_id =>p_table_route_id
, p_copy_entity_txn_id =>p_copy_entity_txn_id
, p_source_id1 =>l_ret.information174
, p_target_value1 =>l_ret.information175
, p_target_id1 =>l_ret.information176
, p_source_id2 =>l_ret.information178
, p_target_value2 =>l_ret.information179
, p_target_id2 =>l_ret.information180
);
update_mapping_target_data (
p_table_route_id =>p_table_route_id
, p_copy_entity_txn_id =>p_copy_entity_txn_id
, p_source_id1 =>l_ret.information174
, p_target_value1 =>l_ret.information175
, p_target_id1 =>l_ret.information176
, p_source_id2 =>l_ret.information178
, p_target_value2 =>l_ret.information179
, p_target_id2 =>l_ret.information180
);
update_mapping_target_data (
p_table_route_id =>p_table_route_id
, p_copy_entity_txn_id =>p_copy_entity_txn_id
, p_source_id1 =>l_ret.information174
, p_target_value1 =>l_ret.information175
, p_target_id1 =>l_ret.information176
, p_source_id2 =>l_ret.information178
, p_target_value2 =>l_ret.information179
, p_target_id2 =>l_ret.information180
);
update_mapping_target_data (
p_table_route_id =>p_table_route_id
, p_copy_entity_txn_id =>p_copy_entity_txn_id
, p_source_id1 =>l_ret.information174
, p_target_value1 =>l_ret.information175
, p_target_id1 =>l_ret.information176
, p_source_id2 =>l_ret.information178
, p_target_value2 =>l_ret.information179
, p_target_id2 =>l_ret.information180
);
procedure update_mapping_target_data(
p_validate in number default 0 -- false
,p_copy_entity_txn_id in number
,p_table_route_id in number
,p_source_id1 in number
,p_target_value1 in varchar2
,p_target_id1 in number
,p_source_id2 in number
,p_target_value2 in varchar2
,p_target_id2 in number
,p_business_group_id in number default hr_api.g_number
,p_effective_date in date default null
) is
--
cursor c_result_set(p_copy_entity_txn_id number, p_table_route_id number) is
select COPY_ENTITY_RESULT_ID,object_version_number
from ben_copy_entity_results
where copy_entity_txn_id = p_copy_entity_txn_id
and table_route_id = p_table_route_id;
update_plan_design_result(
p_validate => p_validate
,p_copy_entity_result_id => l_rec.copy_entity_result_id
,p_copy_entity_txn_id => p_copy_entity_txn_id
,p_business_group_id => p_business_group_id
,p_object_version_number => l_rec.object_version_number
,p_effective_date => p_effective_date
,p_information175 => p_target_value1
,p_information176 => p_target_id1
,p_information179 => p_target_value2
,p_information180 => p_target_id2
);
update ben_copy_entity_results
set information175 = p_target_value1
,information176 = p_target_id1
,information179 = p_target_value2
,information180 = p_target_id2
where copy_entity_txn_id = p_copy_entity_txn_id
and table_route_id = p_table_route_id
and information174 = p_source_id1
and nvl(information178,-1) = nvl(p_source_id2,-1);
end update_mapping_target_data;
select cer.information175
from ben_copy_entity_results cer,
pqh_copy_entity_txns cet
where cer.copy_entity_txn_id = p_entity_txn_id
and cer.copy_entity_txn_id = cet.copy_entity_txn_id
and cer.information176 is null
and cer.information174 is not null
and cer.NUMBER_OF_COPIES =1
and cer.table_route_id = p_table_route_id
and ( (cet.action_date is null) or
(cer.information3 is null) or
(cet.action_date is not null and
information3 >= cet.action_date)
);
select table_route_id
from pqh_table_route
where table_route_id = p_table_route_id
and where_clause in('BEN_HRS_WKD_IN_PERD_FCTR',
--'BEN_PL_F',
--'BEN_OPT_F',
--'BEN_POPL_ORG_F',
'BEN_COMP_LVL_FCTR',
'BEN_ENRT_PERD'); -- Bug No 4498668
select display_name
from pqh_table_route
where table_route_id = p_table_route_id;
select distinct(business_group_id) business_group_id
from per_sec_profile_assignments_v
where user_id = p_user_id
and trunc(sysdate) between START_DATE
and nvl(END_DATE, trunc(sysdate));
select distinct(business_group_id) business_group_id
from per_sec_profile_assignments_v
where user_id = p_user_id
and trunc(sysdate) between START_DATE
and nvl(END_DATE, trunc(sysdate))
and business_group_id is not null ;
select optval.PROFILE_OPTION_VALUE
from FND_PROFILE_OPTION_VALUES optval,
fnd_profile_options_vl opt,
FND_USER_RESP_GROUPS resp
where opt.profile_option_id = optval.profile_option_id
and optval.level_value = resp.RESPONSIBILITY_ID
and resp.user_id = p_user_id
and opt.profile_option_name like 'PER_BUSINESS_GROUP_ID'
and trunc(sysdate) between resp.START_DATE
and nvl(resp.END_DATE, trunc(sysdate));
select resp.user_id,
resp.responsibility_id,
resp.responsibility_application_id application_id
from fnd_user_resp_groups resp
where resp.user_id = p_user_id;
insert into pqh_process_log
( process_log_id,
module_cd,
txn_id,
message_text,
message_type_cd,
object_version_number
)
Values
( pqh_process_log_s.nextval,
p_module_cd,
p_txn_id,
p_message_text,
p_message_type_cd,
1
);
select cet.process_name
,cet.src_effective_date
,cet.context_business_group_id
,cet.target_business_group_id
,cet.prefix_suffix_text
,cet.prefix_suffix_cd
,cet.reuse_object_flag
,cet.target_typ_cd
,cet.information30 source_business_group_name
,cet.action_date
,tcg.short_name
from ben_copy_entity_txns_vw cet,
pqh_transaction_categories tcg
where cet.copy_entity_txn_id = c_copy_entity_txn_id
and cet.transaction_category_id = tcg.transaction_category_id;
select name
from per_business_groups
where business_group_id = c_business_group_id;
select meaning
from hr_lookups
where lookup_type = c_lookup_type
and lookup_code = c_lookup_code;
select to_char(trunc(sysdate), l_icx_date_format_mask) run_date
,to_char(sysdate,'HH24:MI:SS') run_time
from dual ;
select user_name
from fnd_user
where user_id = c_user_id;
select table_alias
,display_name
from pqh_table_route_vl trt
where trt.table_alias in
('PGM', 'PLN', 'OPT', 'PTP', 'EAT'
,'BNB', 'CLF', 'HWF', 'AGF', 'LSF'
,'PFF', 'CLA', 'REG', 'BNR', 'BPP'
,'LER', 'ELP', 'DCE', 'GOS', 'BNG'
,'PDL', 'SVA', 'CPL', 'CBP', 'CPT'
,'FFF', 'ABR', 'APR', 'VPF', 'CCM'
,'ACP', 'PSL', 'EGL');
select information5 name
from ben_copy_entity_results cer
where cer.copy_entity_txn_id = c_copy_entity_txn_id
and cer.number_of_copies = 1
and cer.result_type_cd = 'DISPLAY'
and cer.information8 = c_information8;
cursor c_selection_count(c_copy_entity_txn_id in number) is
select count(1) from (
select distinct information1,information2,information3,table_route_id
from ben_copy_entity_results cer
where copy_entity_txn_id = c_copy_entity_txn_id
and number_of_copies = 1);
select count(1) from (
select distinct information1,information2,information3,table_route_id
from ben_copy_entity_results
where copy_entity_txn_id = c_copy_entity_txn_id
and number_of_copies = 1
and information9 like c_copied_reused_type);
select count(1) from (
select distinct information1,information2,information3,table_route_id
from ben_copy_entity_results
where copy_entity_txn_id = c_copy_entity_txn_id
and number_of_copies = 1
and information9 is null);
select count(1) from (
select distinct information1,information2,information3,cer.table_route_id
from ben_copy_entity_results cer
-- pqh_table_route tre
where copy_entity_txn_id = c_copy_entity_txn_id
and number_of_copies = 1
and cer.table_alias = 'EAT'
-- and cer.table_route_id = tre.table_route_id
);
l_selection_count number;
l_selected_for_copy_lbl fnd_new_messages.message_text%type;
update_cer_with_target(p_copy_entity_txn_id);
open c_selection_count(p_copy_entity_txn_id);
fetch c_selection_count into l_selection_count;
close c_selection_count;
l_selection_count := l_selection_count - l_items_to_ignore_count ;
delete from pqh_process_log
where txn_id = p_copy_entity_txn_id
and module_cd = 'PDC_CP';
||fnd_message.get_string('BEN','BEN_93276_PDC_ROWS_SELECTED')
||l_value_end_tag||l_cell_end_tag
||l_cell_start_tag||l_single_spacer||l_cell_end_tag
||l_cell_start_tag||l_value_start_tag
||l_selection_count
||l_value_end_tag||l_cell_end_tag
||l_row_end_tag
,p_message_type_cd => l_label
);
l_selected_for_copy_lbl := l_header_start_tag
||fnd_message.get_string('BEN','BEN_93283_PDC_SLCTD_FOR_COPY')
||l_header_end_tag;
,p_message_text => l_selected_for_copy_lbl
,p_message_type_cd => l_label
);
select cet.process_name process_name
,to_char(cet.src_effective_date,'DD/MM/YYYY') src_effective_date
,cet.src_effective_date effective_date
,cet.target_typ_cd target_typ_cd
,cet.row_type_cd row_type_cd /* Bug 4278495 */
,cet.export_file_name
,cet.prefix_suffix_text
,cet.prefix_suffix_cd
,cet.reuse_object_flag
,cet.target_business_group_id
,cet.cet_object_version_number
,cet.action_date
,cet.sfl_step_name
,tcg.short_name
from ben_copy_entity_txns_vw cet,
pqh_transaction_categories tcg
where cet.copy_entity_txn_id = p_copy_entity_txn_id
and cet.transaction_category_id = tcg.transaction_category_id;
cursor c_chk_selection(c_copy_entity_txn_id in number
,c_number_of_copies in number)
is
select null
from ben_copy_entity_results cer
where copy_entity_txn_id = c_copy_entity_txn_id
and information8 is not null
and number_of_copies = c_number_of_copies
and rownum = 1;
select null
from ben_copy_entity_results cpe
where cpe.copy_entity_txn_id = p_copy_entity_txn_id
and cpe.table_alias = 'FFF'
and cpe.number_of_copies = 1
and cpe.dml_operation in ('INSERT','UPDATE')
and (cpe.datetrack_mode IN ('INSERT','CORRECTION')
or cpe.datetrack_mode like 'UPDATE%');
select copy_entity_attrib_id, object_version_number
from pqh_copy_entity_attribs cea
where copy_entity_txn_id = p_copy_entity_txn_id;
open c_chk_selection(p_copy_entity_txn_id,1);
fetch c_chk_selection into l_dummy;
if c_chk_selection%notfound then
close c_chk_selection;
fnd_message.set_name('BEN','BEN_93211_PDC_SELECT_OBJECT_ER');
close c_chk_selection;
pqh_copy_entity_txns_api.update_COPY_ENTITY_TXN
(p_copy_entity_txn_id => p_copy_entity_txn_id
,p_datetrack_mode => hr_api.g_correction
,p_status => l_status
,p_start_with => NULL /* To disable Continue Icon */
,p_object_version_number => l_copy_entity_txn.cet_object_version_number
,p_effective_date => trunc(l_copy_entity_txn.effective_date)
);
pqh_copy_entity_attribs_api.update_copy_entity_attrib
(p_copy_entity_attrib_id => l_cea.copy_entity_attrib_id
,p_object_version_number => l_cea.object_version_number
,p_effective_date => trunc(l_copy_entity_txn.effective_date)
,p_information14 => TO_CHAR(l_request_id)
);
hr_utility.set_location('After call to CET Update',200);
select table_route_id
from ben_copy_entity_results
where copy_entity_txn_id = p_copy_entity_txn_id
and information174 is not null
and table_route_id in (select table_route_id
from pqh_table_route
where where_clause in('BEN_HRS_WKD_IN_PERD_FCTR',
-- 'BEN_POPL_ORG_F',
'BEN_COMP_LVL_FCTR',
'BEN_ENRT_PERD')); -- Bug No 4498668
select display_name
from pqh_table_route
where table_route_id = p_table_route_id;
select attribute_name,refresh_col_name -- 3330990
from pqh_attributes_vl att
where enable_flag = 'Y'
and master_table_route_id = p_table_route_id;
procedure update_download_status(
errbuf out nocopy varchar2
,retcode out nocopy number
,p_request_id in number
,p_copy_entity_txn_id in number
)
is
l_phase varchar2(240);
select cet.src_effective_date effective_date
,cet.cet_object_version_number
,cet.sfl_step_name
from ben_copy_entity_txns_vw cet
where cet.copy_entity_txn_id = p_copy_entity_txn_id ;
elsif l_dev_status in ( 'CANCELLED' , 'TERMINATED','DELETED' ) then
--
l_txn_status := 'INTERRUPTED' ;
hr_utility.set_location('Before call to CET Update',100);
pqh_copy_entity_txns_api.update_COPY_ENTITY_TXN
(
p_copy_entity_txn_id => p_copy_entity_txn_id
,p_datetrack_mode => hr_api.g_correction
,p_status => l_txn_status
,p_start_with => l_start_with
,p_object_version_number => l_ovn
,p_effective_date => l_effective_date
);
hr_utility.set_location('After call to CET Update',200);
end update_download_status ;
select null
from pqh_process_log
where txn_id = c_copy_entity_txn_id
and module_cd = 'PDC_CP'
and rownum = 1;
cursor tr is select table_route_id,display_name,table_alias
from pqh_table_route
where from_clause ='OAB'
and table_alias in ( select distinct table_alias from ben_copy_entity_results
where copy_entity_txn_id = p_copy_entity_txn_id
and table_route_id is null);
update ben_copy_entity_results
set table_route_id = i.table_route_id
where copy_entity_txn_id = p_copy_entity_txn_id
and table_alias = i.table_alias;
SELECT cet.copy_entity_txn_id, cet.process_name,
cet.cet_object_version_number,
cet.status, cet.src_effective_date,
cet.target_typ_cd,
decode(ben_plan_design_txns_api.get_log_display
(cet.copy_entity_txn_id
,cet.status
,cet.target_typ_cd),
'LogEnabled','Y',
'LogDisabled','N', 'N') log_status
FROM ben_copy_entity_txns_vw cet
WHERE cet.transaction_category_id =
(SELECT ptc.transaction_category_id
FROM PQH_TRANSACTION_CATEGORIES ptc
WHERE ptc.short_name = p_transaction_short_name)
AND cet.copy_entity_txn_id = NVL (p_copy_entity_txn_id, cet.copy_entity_txn_id)
AND cet.status = NVL (p_status, cet.status)
AND cet.src_effective_date <= p_effective_date
AND cet.context_business_group_id = p_business_group_id;
/* Only if validate mode is Commit ('N'), delete the txns,
for mode = rollback ('Y'), no need of actually calling delete,
just list the processes to be deleted */
if (p_validate = 'N') then
/* Check if log is maintained for this copy_entity_txn_id, if not, then
no point in retaining the log and header record */
--
if(l_purge_processes.log_status = 'N') then
l_retain_log := 'N';
delete_PLAN_DESIGN_TXN
(p_copy_entity_txn_id => l_purge_processes.copy_entity_txn_id
,p_cet_object_version_number => l_purge_processes.cet_object_version_number
,p_effective_date => l_effective_date
,p_retain_log => l_retain_log
);