[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select unique_key_id
into l_id
from hr_pump_batch_line_user_keys
where user_key_value = p_user_key_value;
select pc.collective_agreement_id
into l_collective_agreement_id
from per_collective_agreements pc
where pc.business_group_id = p_business_group_id
and pc.name = p_cagr_name
and p_effective_date between
nvl(start_date,START_OF_TIME) and nvl(end_date,END_OF_TIME);
select pe.establishment_id
into l_establishment_id
from per_establishments pe
where pe.location = p_location
and pe.name = p_establishment_name;
select piv.input_value_id
into l_input_value_id
from pay_input_values_f_tl pivtl,
pay_input_values_f piv,
pay_element_types_f pet,
pay_element_types_f_tl pettl,
per_business_groups pbg
where pbg.business_group_id = p_business_group_id
and pettl.element_name = p_element_name
and pettl.language = p_language_code
and pet.element_type_id = pettl.element_type_id
and p_effective_date between
pet.effective_start_date and pet.effective_end_date
and
(
(pet.business_group_id is null and pet.legislation_code is null) or
(pet.business_group_id is null
and pet.legislation_code = pbg.legislation_code) or
(pet.legislation_code is null
and pet.business_group_id = p_business_group_id)
)
and piv.element_type_id = pet.element_type_id
and p_effective_date between
piv.effective_start_date and piv.effective_end_date
and pivtl.input_value_id = piv.input_value_id
and pivtl.name = p_input_value_name
and pivtl.LANGUAGE = p_language_code;
select rate_id
into l_rate_id
from pay_rates
where name = p_rate_name
and business_group_id + 0 = p_business_group_id;
select bbg.benfts_grp_id
into l_benefit_group_id
from ben_benfts_grp bbg
where bbg.name = p_benefit_group
and bbg.business_group_id + 0 = p_business_group_id;
select ppl.object_version_number
into l_ovn
from ben_ptnl_ler_for_per ppl,
hr_pump_batch_line_user_keys key
where key.user_key_value = p_ptnl_ler_for_per_user_key
and ppl.ptnl_ler_for_per_id = key.unique_key_id;
select pil.object_version_number
into l_ovn
from ben_per_in_ler pil,
hr_pump_batch_line_user_keys key
where key.user_key_value = p_per_in_ler_user_key
and pil.per_in_ler_id = key.unique_key_id;
select pen.object_version_number
into l_ovn
from ben_prtt_enrt_rslt_f pen,
hr_pump_batch_line_user_keys key
where key.user_key_value = p_prtt_enrt_rslt_user_key
and pen.prtt_enrt_rslt_id = key.unique_key_id
and p_effective_date between
pen.effective_start_date and pen.effective_end_date;
select prv.object_version_number
into l_ovn
from ben_prtt_rt_val prv,
hr_pump_batch_line_user_keys key
where key.user_key_value = p_prtt_rt_val_user_key
and prv.prtt_rt_val_id = key.unique_key_id;
select cqb.object_version_number
into l_ovn
from ben_cbr_quald_bnf cqb,
hr_pump_batch_line_user_keys key
where key.user_key_value = p_cbr_quald_bnf_user_key
and cqb.cbr_quald_bnf_id = key.unique_key_id;
select crp.object_version_number
into l_ovn
from ben_cbr_per_in_ler crp,
hr_pump_batch_line_user_keys key
where key.user_key_value = p_cbr_per_in_ler_user_key
and crp.cbr_per_in_ler_id = key.unique_key_id;
select pdp.object_version_number
into l_ovn
from ben_elig_cvrd_dpnt_f pdp,
hr_pump_batch_line_user_keys key
where key.user_key_value = p_elig_cvrd_dpnt_user_key
and pdp.elig_cvrd_dpnt_id = key.unique_key_id
and p_effective_date between
pdp.effective_start_date and pdp.effective_end_date;
select ppe.object_version_number
into l_ovn
from ben_prtt_prem_f ppe,
hr_pump_batch_line_user_keys key
where key.user_key_value = p_prtt_prem_user_key
and ppe.prtt_prem_id = key.unique_key_id
and p_effective_date between
ppe.effective_start_date and ppe.effective_end_date;
select egd.object_version_number
into l_ovn
from ben_elig_dpnt egd,
hr_pump_batch_line_user_keys key
where key.user_key_value = p_elig_dpnt_user_key
and egd.elig_dpnt_id = key.unique_key_id;
select pep.object_version_number
into l_ovn
from ben_elig_per_f pep,
hr_pump_batch_line_user_keys key
where key.user_key_value = p_elig_per_user_key
and pep.elig_per_id = key.unique_key_id
and p_effective_date between
pep.effective_start_date and pep.effective_end_date;
select epo.object_version_number
into l_ovn
from ben_elig_per_opt_f epo,
hr_pump_batch_line_user_keys key
where key.user_key_value = p_elig_per_opt_user_key
and epo.elig_per_opt_id = key.unique_key_id
and p_effective_date between
epo.effective_start_date and epo.effective_end_date;
select pbn.object_version_number
into l_ovn
from ben_pl_bnf_f pbn,
hr_pump_batch_line_user_keys key
where key.user_key_value = p_pl_bnf_user_key
and pbn.pl_bnf_id = key.unique_key_id
and p_effective_date between
pbn.effective_start_date and pbn.effective_end_date;
select cop.object_version_number
into l_ovn
from ben_oipl_f cop,
hr_pump_batch_line_user_keys key
where key.user_key_value = p_oipl_user_key
and cop.oipl_id = key.unique_key_id
and p_effective_date between
cop.effective_start_date and cop.effective_end_date;
select cpp.object_version_number
into l_ovn
from ben_plip_f cpp,
hr_pump_batch_line_user_keys key
where key.user_key_value = p_plip_user_key
and cpp.plip_id = key.unique_key_id
and p_effective_date between
cpp.effective_start_date and cpp.effective_end_date;
select ctp.object_version_number
into l_ovn
from ben_ptip_f ctp,
hr_pump_batch_line_user_keys key
where key.user_key_value = p_ptip_user_key
and ctp.ptip_id = key.unique_key_id
and p_effective_date between
ctp.effective_start_date and ctp.effective_end_date;
select ecr.object_version_number
into l_ovn
from ben_enrt_rt ecr,
hr_pump_batch_line_user_keys key
where key.user_key_value = p_enrt_rt_user_key
and ecr.enrt_rt_id = key.unique_key_id;
select enp.object_version_number
into l_ovn
from ben_enrt_perd enp,
hr_pump_batch_line_user_keys key
where key.user_key_value = p_enrt_perd_user_key
and enp.enrt_perd_id = key.unique_key_id;
select prc.object_version_number
into l_ovn
from ben_prtt_reimbmt_rqst_f prc,
hr_pump_batch_line_user_keys key
where key.user_key_value = p_prtt_reimbmt_rqst_user_key
and prc.prtt_reimbmt_rqst_id = key.unique_key_id
and p_effective_date between
prc.effective_start_date and prc.effective_end_date;
select epe.object_version_number
into l_ovn
from ben_elig_per_elctbl_chc epe,
hr_pump_batch_line_user_keys key
where key.user_key_value = p_elig_per_elctbl_chc_user_key
and epe.elig_per_elctbl_chc_id = key.unique_key_id;
select bng.benfts_grp_id
into l_benefit_group_id
from ben_benfts_grp bng
where bng.name = p_benefits_group
and bng.business_group_id + 0 = p_business_group_id;
select bng.object_version_number
into l_ovn
from ben_benfts_grp bng
where bng.name = p_benefits_group
and bng.business_group_id + 0 = p_business_group_id;
select ptp.pl_typ_id
into l_plan_type_id
from ben_pl_typ_f ptp
where ptp.name = p_plan_type
and ptp.business_group_id + 0 = p_business_group_id
and p_effective_date between
ptp.effective_start_date and ptp.effective_end_date;
select ptp.object_version_number
into l_ovn
from ben_pl_typ_f ptp
where ptp.name = p_plan_type
and ptp.business_group_id + 0 = p_business_group_id
and p_effective_date between
ptp.effective_start_date and ptp.effective_end_date;
select ler.ler_id
into l_ler_id
from ben_ler_f ler
where ler.name = p_life_event_reason
and ler.business_group_id + 0 = p_business_group_id
and p_effective_date between
ler.effective_start_date and ler.effective_end_date;
select ler.object_version_number
into l_ovn
from ben_ler_f ler
where ler.name = p_life_event_reason
and ler.business_group_id + 0 = p_business_group_id
and p_effective_date between
ler.effective_start_date and ler.effective_end_date;
select abr.acty_base_rt_id
into l_acty_base_rt_id
from ben_acty_base_rt_f abr
where abr.name = p_acty_base_rate
and abr.business_group_id + 0 = p_business_group_id
and p_effective_date between
abr.effective_start_date and abr.effective_end_date;
select abr.object_version_number
into l_ovn
from ben_acty_base_rt_f abr
where abr.name = p_acty_base_rate
and abr.business_group_id + 0 = p_business_group_id
and p_effective_date between
abr.effective_start_date and abr.effective_end_date;
select apr.actl_prem_id
into l_actl_prem_id
from ben_actl_prem_f apr
where apr.name = p_actual_premium
and apr.business_group_id + 0 = p_business_group_id
and p_effective_date between
apr.effective_start_date and apr.effective_end_date;
select apr.object_version_number
into l_ovn
from ben_actl_prem_f apr
where apr.name = p_actual_premium
and apr.business_group_id + 0 = p_business_group_id
and p_effective_date between
apr.effective_start_date and apr.effective_end_date;
select clf.comp_lvl_fctr_id
into l_comp_lvl_fctr_id
from ben_comp_lvl_fctr clf
where clf.name = p_comp_level_factor
and clf.business_group_id + 0 = p_business_group_id;
select clf.object_version_number
into l_ovn
from ben_comp_lvl_fctr clf
where clf.name = p_comp_level_factor
and clf.business_group_id + 0 = p_business_group_id;
select ccm.cvg_amt_calc_mthd_id
into l_cvg_amt_calc_mthd_id
from ben_cvg_amt_calc_mthd_f ccm
where ccm.name = p_cvg_amt_calc
and ccm.business_group_id + 0 = p_business_group_id
and p_effective_date between
ccm.effective_start_date and ccm.effective_end_date;
select ccm.object_version_number
into l_ovn
from ben_cvg_amt_calc_mthd_f ccm
where ccm.name = p_cvg_amt_calc
and ccm.business_group_id + 0 = p_business_group_id
and p_effective_date between
ccm.effective_start_date and ccm.effective_end_date;
select opt.opt_id
into l_opt_id
from ben_opt_f opt
where opt.name = p_option_definition
and opt.business_group_id + 0 = p_business_group_id
and p_effective_date between
opt.effective_start_date and opt.effective_end_date;
select opt.object_version_number
into l_ovn
from ben_opt_f opt
where opt.name = p_option_definition
and opt.business_group_id + 0 = p_business_group_id
and p_effective_date between
opt.effective_start_date and opt.effective_end_date;
select pln.pl_id
into l_pl_id
from ben_pl_f pln
where pln.name = p_plan
and pln.business_group_id + 0 = p_business_group_id
and p_effective_date between
pln.effective_start_date and pln.effective_end_date;
select pln.object_version_number
into l_ovn
from ben_pl_f pln
where pln.name = p_plan
and pln.business_group_id + 0 = p_business_group_id
and p_effective_date between
pln.effective_start_date and pln.effective_end_date;
select pgm.pgm_id
into l_pgm_id
from ben_pgm_f pgm
where pgm.name = p_program
and pgm.business_group_id + 0 = p_business_group_id
and p_effective_date between
pgm.effective_start_date and pgm.effective_end_date;
select pgm.object_version_number
into l_ovn
from ben_pgm_f pgm
where pgm.name = p_program
and pgm.business_group_id + 0 = p_business_group_id
and p_effective_date between
pgm.effective_start_date and pgm.effective_end_date;
select pet.element_type_id
into l_element_type_id
from pay_element_types_f pet
where pet.element_name = p_element_name
and business_group_id + 0 = p_business_group_id
and p_effective_date between
pet.effective_start_date and pet.effective_end_date;
select currency_code
into l_code
from fnd_currencies_vl
where name = p_name_or_code
and enabled_flag = 'Y'
and p_effective_date between
nvl(start_date_active, START_OF_TIME) and nvl(end_date_active, END_OF_TIME);
select currency_code
into l_code
from fnd_currencies -- user underlying table for faster code lookup
where currency_code = p_name_or_code
and enabled_flag = 'Y'
and p_effective_date between
nvl(start_date_active, START_OF_TIME) and nvl(end_date_active, END_OF_TIME);
select pcr.contact_relationship_id
into l_contact_relationship_id
from per_contact_relationships pcr,
hr_pump_batch_line_user_keys contact_key,
hr_pump_batch_line_user_keys contactee_key
where contact_key.user_key_value = p_contact_user_key
and pcr.contact_person_id = contact_key.unique_key_id
and contactee_key.user_key_value = p_contactee_user_key
and pcr.person_id = contactee_key.unique_key_id;
select typ.person_type_id
into l_person_type_id
from per_person_types_tl typtl,
per_person_types typ
where typtl.user_person_type = p_user_person_type
and typ.business_group_id + 0 = p_business_group_id
and typ.person_type_id = typtl.person_type_id
and typtl.LANGUAGE = p_language_code;
select typ.person_type_id
into l_person_type_id
from per_person_types_tl typtl,
per_person_types typ
where typtl.user_person_type = p_user_person_type
and typ.business_group_id + 0 = p_business_group_id
and typ.person_type_id = typtl.person_type_id
and typtl.LANGUAGE = p_language_code;
select pov.vendor_id
into l_vendor_id
from po_vendors pov
where pov.vendor_name = p_vendor_name;
select typ.assignment_status_type_id
into l_assignment_status_type_id
from per_ass_status_type_amends_tl typtl,
per_ass_status_type_amends typ
where typtl.user_status = p_user_status
and typ.business_group_id + 0 = p_business_group_id
and typ.ass_status_type_amend_id = typtl.ass_status_type_amend_id
and typtl.LANGUAGE = p_language_code;
select typ.assignment_status_type_id
into l_assignment_status_type_id
from per_assignment_status_types_tl typtl,
per_assignment_status_types typ
where typtl.user_status = p_user_status
and typ.assignment_status_type_id = typtl.assignment_status_type_id
and typtl.LANGUAGE = p_language_code
and
(
(typ.business_group_id is null and typ.legislation_code is null)
or
(typ.business_group_id is not null
and typ.business_group_id + 0 = p_business_group_id)
or
(typ.business_group_id is null
and typ.legislation_code is not null
and typ.legislation_code =
(select legislation_code from per_business_groups
where business_group_id = p_business_group_id))
);
select org.organization_id
into l_organization_id
from hr_all_organization_units org
, hr_all_organization_units_tl orgtl
where orgtl.name = p_organization_name
and orgtl.language = p_language_code
and org.organization_id = orgtl.organization_id
and org.business_group_id + 0 = p_business_group_id;
select gra.grade_id
into l_grade_id
from per_grades_vl gra
where gra.name = p_grade_name
and gra.business_group_id + 0 = p_business_group_id;
select pst.shared_type_id
from per_shared_types pst
, per_shared_types_tl psttl
where psttl.shared_type_name = p_shared_type_name
and psttl.language = p_language_code
and pst.shared_type_id = psttl.shared_type_id
and pst.lookup_type = 'POSITION_AVAILABILITY_STATUS'
and pst.system_type_cd = p_system_type_cd
and nvl(pst.business_group_id, p_business_group_id) = p_business_group_id;
select pos.position_id
into l_position_id
from hr_all_positions_f pos
where pos.name = p_position_name
and pos.business_group_id + 0 = p_business_group_id
and p_effective_date between
pos.effective_start_date and pos.effective_end_date;
select job.job_id
into l_job_id
from per_jobs_vl job
where job.name = p_job_name
and job.business_group_id + 0 = p_business_group_id;
select pay.payroll_id
into l_payroll_id
from pay_payrolls_f pay
where pay.payroll_name = p_payroll_name
and pay.business_group_id + 0 = p_business_group_id
and p_effective_date between
pay.effective_start_date and pay.effective_end_date;
/* Returns a location_id for the update_location APIs. */
function get_location_id_update
(
p_existing_location_code in varchar2,
p_business_group_id in number,
p_language_code in varchar2
) return number is
l_location_id number;
hr_data_pump.fail('get_location_id_update', sqlerrm,
p_existing_location_code,
p_business_group_id, p_language_code);
end get_location_id_update;
select loc.location_id
into l_location_id
from hr_locations_all loc,
hr_locations_all_tl lot
where lot.location_code = p_location_code
and lot.language = p_language_code
and lot.location_id = loc.location_id
and (loc.business_group_id is null or
loc.business_group_id + 0 = p_business_group_id);
Select person_id
Into l_designated_receiver_id
From per_all_people_f
Where employee_number Is Not Null
and business_group_id = p_business_group_id
and p_effective_date between effective_start_date and effective_end_date
and full_name = p_designated_receiver_name;
select loc.location_id
into l_location_id
from hr_locations_all loc,
hr_locations_all_tl lot
where lot.location_code = p_ship_to_location_code
and lot.language = p_language_code
and lot.location_id = loc.location_id
and (loc.business_group_id is null or
loc.business_group_id + 0 = p_business_group_id);
select ppb.pay_basis_id
into l_pay_basis_id
from per_pay_bases ppb
where ppb.name = p_pay_basis_name
and ppb.business_group_id + 0 = p_business_group_id;
select pra.recruitment_activity_id
into l_raid
from per_recruitment_activities pra
where pra.name = p_recruitment_activity_name
and pra.business_group_id + 0 = p_business_group_id
and p_effective_date between
pra.date_start and nvl(pra.date_end, END_OF_TIME);
select sob.set_of_books_id
into l_id
from gl_sets_of_books sob
where sob.name = p_set_of_books_name;
select tax.tax_unit_id
into l_tax_unit_id
from hr_tax_units_v tax
where tax.name = p_tax_unit_name;
select puc.user_column_id
into l_id
from pay_user_columns puc,
hr_organization_information hoi,
hr_all_organization_units org
, hr_all_organization_units_tl orgtl
where orgtl.name = p_organization_name
and orgtl.language = p_language_code
and org.organization_id = orgtl.organization_id
and org.business_group_id + 0 = p_business_group_id
and puc.user_column_name = p_work_schedule
and hoi.org_information_context = 'Work Schedule'
and hoi.organization_id = org.organization_id
and (puc.user_table_id = hoi.org_information1 or
hoi.org_information1 is null);
select est.establishment_id
into l_id
from HR_ESTABLISHMENTS_V est
where est.name = p_eeo_1_establishment
and est.business_group_id + 0 = p_business_group_id;
select pgr.grade_rule_id
into l_grade_rule_id
from pay_grade_rules_f pgr,
per_grades_vl pg,
pay_rates pr
where pg.name = p_grade_name
and pg.business_group_id + 0 = p_business_group_id
and pr.name = p_rate_name
and pr.business_group_id + 0 = p_business_group_id
and pgr.rate_id = pr.rate_id
and pgr.grade_or_spinal_point_id = pg.grade_id
and pgr.rate_type = 'G'
and pgr.business_group_id + 0 = p_business_group_id
and p_effective_date between pgr.effective_start_date and
pgr.effective_end_date;
select pgr.grade_rule_id
into l_grade_rule_id
from per_parent_spines pps,
per_spinal_points psp,
pay_grade_rules_f pgr,
pay_rates pr
where pps.name = p_pay_scale
and pps.business_group_id + 0 = p_business_group_id
and psp.spinal_point = p_progression_point
and psp.business_group_id + 0 = p_business_group_id
and psp.parent_spine_id = pps.parent_spine_id
and pr.name = p_rate_name
and pr.business_group_id + 0 = p_business_group_id
and pgr.rate_id = pr.rate_id
and pgr.grade_or_spinal_point_id = psp.spinal_point_id
and pgr.rate_type = 'SP'
and pgr.business_group_id + 0 = p_business_group_id
and p_effective_date between pgr.effective_start_date and
pgr.effective_end_date;
select pgr.grade_rule_id
into l_grade_rule_id
from pay_grade_rules_f pgr,
pay_rates pr
where pr.name = p_rate_name
and pr.business_group_id + 0 = p_business_group_id
and pgr.rate_id = pr.rate_id
and pgr.rate_type = 'A'
and pgr.business_group_id + 0 = p_business_group_id
and p_effective_date between pgr.effective_start_date and
pgr.effective_end_date;
select pos.organization_structure_id
into l_organization_structure_id
from per_organization_structures pos
where pos.name = p_name
and pos.business_group_id + 0 = p_business_group_id;
select osv.org_structure_version_id
into l_org_str_ver_id
from per_org_structure_versions osv
where osv.organization_structure_id = p_organization_structure_id
and osv.date_from = p_date_from
and osv.version_number = p_version_number
and osv.business_group_id + 0 = p_business_group_id;
select psp.spinal_point_id
into l_spinal_point_id
from per_parent_spines pps,
per_spinal_points psp
where pps.name = p_pay_scale
and pps.business_group_id + 0 = p_business_group_id
and psp.spinal_point = p_progression_point
and psp.business_group_id + 0 = p_business_group_id
and psp.parent_spine_id = pps.parent_spine_id;
select pps.period_of_service_id
into l_period_of_service_id
from per_periods_of_service pps,
hr_pump_batch_line_user_keys key
where key.user_key_value = p_person_user_key
and pps.person_id = key.unique_key_id
and pps.business_group_id = p_business_group_id
and pps.actual_termination_date is null;
select pps.period_of_service_id
into l_period_of_service_id
from per_periods_of_service pps,
hr_pump_batch_line_user_keys key
where key.user_key_value = p_person_user_key
and pps.person_id = key.unique_key_id
and pps.business_group_id = p_business_group_id
and pps.actual_termination_date is not null
and pps.final_process_date is null;
select pps.period_of_service_id
into l_period_of_service_id
from per_periods_of_service pps,
hr_pump_batch_line_user_keys key
where key.user_key_value = p_person_user_key
and pps.person_id = key.unique_key_id
and pps.business_group_id = p_business_group_id
and p_effective_date between pps.date_start and NVL(pps.actual_termination_date,l_eot);
select pgm.pgm_id
into l_grade_ladder_pgm_id
from ben_pgm_f pgm
where pgm.name = p_grade_ladder_name
and pgm.pgm_typ_cd = 'GSP'
and pgm.business_group_id + 0 = p_business_group_id
and p_effective_date
between pgm.effective_start_date and pgm.effective_end_date;
select parent_spine_id
into l_parent_spine_id
from per_parent_spines
where name = p_parent_spine
and business_group_id = p_business_group_id;
select sps.step_id
into l_ceiling_step_id
from per_spinal_points psp
,per_spinal_point_steps_f sps
where psp.spinal_point = p_ceiling_point
and psp.spinal_point_id = sps.spinal_point_id
and sps.business_group_id = p_business_group_id
and p_effective_date between
sps.effective_start_date and sps.effective_end_date;
select pc.object_version_number
into l_object_version_number
from per_collective_agreements pc
where pc.business_group_id = p_business_group_id
and pc.name = p_cagr_name
and p_effective_date between
nvl(start_date,START_OF_TIME) and nvl(end_date,END_OF_TIME);
select pc.object_version_number
into l_object_version_number
from per_contracts_f pc,
hr_pump_batch_line_user_keys key
where key.user_key_value = p_contract_user_key
and pc.contract_id = key.unique_key_id
and p_effective_date between
pc.effective_start_date and pc.effective_end_date;
select pe.object_version_number
into l_object_version_number
from per_establishments pe
where pe.location = p_location
and pe.name = p_establishment_name;
select rules.object_version_number
into l_ovn
from pay_us_emp_fed_tax_rules_f rules,
hr_pump_batch_line_user_keys key
where key.user_key_value = p_emp_fed_tax_rule_user_key
and rules.emp_fed_tax_rule_id = key.unique_key_id
and p_effective_date between
rules.effective_start_date and rules.effective_end_date;
select rules.object_version_number
into l_ovn
from pay_us_emp_state_tax_rules_f rules,
hr_pump_batch_line_user_keys key
where key.user_key_value = p_emp_state_tax_rule_user_key
and rules.emp_state_tax_rule_id = key.unique_key_id
and p_effective_date between
rules.effective_start_date and rules.effective_end_date;
select rules.object_version_number
into l_ovn
from pay_us_emp_county_tax_rules_f rules,
hr_pump_batch_line_user_keys key
where key.user_key_value = p_emp_county_tax_rule_user_key
and rules.emp_county_tax_rule_id = key.unique_key_id
and p_effective_date between
rules.effective_start_date and rules.effective_end_date;
select rules.object_version_number
into l_ovn
from pay_us_emp_city_tax_rules_f rules,
hr_pump_batch_line_user_keys key
where key.user_key_value = p_emp_city_tax_rule_user_key
and rules.emp_city_tax_rule_id = key.unique_key_id
and p_effective_date between
rules.effective_start_date and rules.effective_end_date;
select per.object_version_number
into l_ovn
from per_people_f per,
hr_pump_batch_line_user_keys key
where key.user_key_value = p_person_user_key
and per.person_id = key.unique_key_id
and p_effective_date between
per.effective_start_date and per.effective_end_date;
select asg.object_version_number
into l_ovn
from per_assignments_f asg,
hr_pump_batch_line_user_keys key
where key.user_key_value = p_assignment_user_key
and asg.assignment_id = key.unique_key_id
and p_effective_date between
asg.effective_start_date and asg.effective_end_date;
select adr.object_version_number
into l_ovn
from per_addresses adr,
hr_pump_batch_line_user_keys key
where key.user_key_value = p_address_user_key
and adr.address_id = key.unique_key_id;
select loc.object_version_number
into l_ovn
from hr_locations_all loc
where loc.location_code = p_location_code;
select ors.object_version_number
into l_ovn
from per_organization_structures ors
where ors.name = p_name
and ors.business_group_id + 0 = p_business_group_id;
select osv.object_version_number
into l_ovn
from per_org_structure_versions osv
where osv.organization_structure_id = p_organization_structure_id
and osv.date_from = p_date_from
and osv.version_number = p_version_number
and osv.business_group_id + 0 = p_business_group_id;
select org.object_version_number
into l_ovn
from hr_all_organization_units org
, hr_all_organization_units_tl orgtl
where orgtl.name = p_organization_name
and orgtl.language = p_language_code
and org.organization_id = orgtl.organization_id
and org.business_group_id + 0 = p_business_group_id;
select job.object_version_number
into l_ovn
from per_jobs_vl job
where job.name = p_job_name
and job.business_group_id + 0 = p_business_group_id;
select pos.object_version_number
into l_ovn
from hr_all_positions_f pos
where pos.name = p_position_name
and pos.business_group_id + 0 = p_business_group_id
and p_effective_date between
pos.effective_start_date and pos.effective_end_date;
select ppm.object_version_number
into l_ovn
from pay_personal_payment_methods_f ppm
, hr_pump_batch_line_user_keys key
where key.user_key_value = p_personal_pay_method_user_key
and ppm.personal_payment_method_id = key.unique_key_id
and p_effective_date between
ppm.effective_start_date and ppm.effective_end_date;
select pee.object_version_number
into l_element_entry_ovn
from pay_element_entries_f pee,
hr_pump_batch_line_user_keys key
where key.user_key_value = p_element_entry_user_key
and pee.element_entry_id = key.unique_key_id
and p_effective_date between
pee.effective_start_date and pee.effective_end_date;
select pgr.object_version_number
into l_object_version_number
from pay_grade_rules_f pgr,
per_grades_vl pg,
pay_rates pr
where pg.name = p_grade_name
and pg.business_group_id + 0 = p_business_group_id
and pr.name = p_rate_name
and pr.business_group_id + 0 = p_business_group_id
and pgr.rate_id = pr.rate_id
and pgr.grade_or_spinal_point_id = pg.grade_id
and pgr.rate_type = 'G'
and pgr.business_group_id + 0 = p_business_group_id
and p_effective_date between pgr.effective_start_date and
pgr.effective_end_date;
select pgr.object_version_number
into l_object_version_number
from per_parent_spines pps,
per_spinal_points psp,
pay_grade_rules_f pgr,
pay_rates pr
where pps.name = p_pay_scale
and pps.business_group_id + 0 = p_business_group_id
and psp.spinal_point = p_progression_point
and psp.business_group_id + 0 = p_business_group_id
and psp.parent_spine_id = pps.parent_spine_id
and pr.name = p_rate_name
and pr.business_group_id + 0 = p_business_group_id
and pgr.rate_id = pr.rate_id
and pgr.grade_or_spinal_point_id = psp.spinal_point_id
and pgr.rate_type = 'SP'
and pgr.business_group_id + 0 = p_business_group_id
and p_effective_date between pgr.effective_start_date and
pgr.effective_end_date;
select pps.object_version_number
into l_object_version_number
from per_periods_of_service pps,
hr_pump_batch_line_user_keys key
where key.user_key_value = p_person_user_key
and pps.person_id = key.unique_key_id
and pps.business_group_id = p_business_group_id
and pps.actual_termination_date is null;
select pps.object_version_number
into l_object_version_number
from per_periods_of_service pps,
hr_pump_batch_line_user_keys key
where key.user_key_value = p_person_user_key
and pps.person_id = key.unique_key_id
and pps.business_group_id = p_business_group_id
and pps.actual_termination_date is not null
and pps.final_process_date is null;
select pps.object_version_number
into l_object_version_number
from per_periods_of_service pps,
hr_pump_batch_line_user_keys key
where key.user_key_value = p_person_user_key
and pps.person_id = key.unique_key_id
and pps.business_group_id = p_business_group_id
and p_effective_date between pps.date_start and NVL(pps.actual_termination_date,l_eot);
select phn.object_version_number
into l_ovn
from per_phones phn,
hr_pump_batch_line_user_keys key
where key.user_key_value = p_phone_user_key
and phn.phone_id = key.unique_key_id;
select jgr.object_version_number
into l_ovn
from per_job_groups jgr,
hr_pump_batch_line_user_keys key
where key.user_key_value = p_job_group_user_key
and jgr.job_group_id = key.unique_key_id;
select rol.object_version_number
into l_ovn
from per_roles rol,
hr_pump_batch_line_user_keys key
where key.user_key_value = p_role_user_key
and rol.role_id = key.unique_key_id;
select object_version_number
into l_ovn
from per_parent_spines
where name = p_pay_scale
and business_group_id + 0 = p_business_group_id;
select object_version_number
into l_ovn
from per_spinal_points
where spinal_point = p_point
and business_group_id + 0 = p_business_group_id;
select pgs.object_version_number
into l_ovn
from per_grade_spines_f pgs
,per_grades pg
,per_parent_spines pps
where pg.name = p_grade
and pg.grade_id = pgs.grade_id
and pps.name = p_pay_scale
and pps.parent_spine_id = pgs.parent_spine_id
and pgs.business_group_id = p_business_group_id
and p_effective_date between
pgs.effective_start_date and pgs.effective_end_date;
select sps.object_version_number
into l_ovn
from per_spinal_point_steps_f sps
,per_spinal_points psp
,per_grade_spines_f pgs
where psp.spinal_point = p_point
and psp.spinal_point_id = sps.spinal_point_id
and sps.sequence = p_sequence
and sps.step_id = pgs.ceiling_step_id
and pgs.grade_spine_id = sps.grade_spine_id
and sps.business_group_id = p_business_group_id
and p_effective_date between
sps.effective_start_date and sps.effective_end_date;
select l.language_code
into l_code
from fnd_languages l
where l.nls_language = p_correspondence_language;
select territory_code
into l_territory_code
from fnd_territories_vl
where territory_short_name = p_country;
select flv.lookup_code
into l_code
from fnd_lookup_values flv,
per_assignments_f asg,
hr_pump_batch_line_user_keys key
where key.user_key_value = p_assignment_user_key
and asg.assignment_id = key.unique_key_id
and p_effective_date between
asg.effective_start_date and asg.effective_end_date
and flv.lookup_type = decode(asg.assignment_type,
'E', 'EMP_ASSIGN_REASON',
'A', 'APL_ASSIGN_REASON')
and flv.meaning = p_change_reason
and flv.language = p_language_code
and flv.view_application_id = 3
and flv.security_group_id =
fnd_global.lookup_security_group
(flv.lookup_type
,flv.view_application_id
)
and p_effective_date between
nvl(flv.start_date_active, START_OF_TIME) and
nvl(flv.end_date_active, END_OF_TIME);
select flv.lookup_code
into l_code
from fnd_lookup_values flv
where flv.meaning = p_meaning_or_code
and flv.lookup_type = p_lookup_type
and flv.language = nvl(l_language_code, userenv('LANG'))
and flv.view_application_id = 3
and flv.security_group_id =
fnd_global.lookup_security_group
(flv.lookup_type
,flv.view_application_id
)
and l_effective_date between
nvl(flv.start_date_active, START_OF_TIME) and
nvl(flv.end_date_active, END_OF_TIME);
select people_group_id
into l_people_group_id
from pay_people_groups
where GROUP_NAME = p_people_group_user_name
and p_effective_date
between nvl(start_date_active,START_OF_TIME)
and nvl(end_date_active,END_OF_TIME);
select aat.absence_attendance_type_id
into l_absence_attendance_type_id
from per_abs_attendance_types_vl aat
where aat.name = p_aat_user_name
and aat.business_group_id = p_business_group_id
and p_effective_date between
nvl(DATE_EFFECTIVE, START_OF_TIME) AND
nvl(DATE_END, END_OF_TIME);
select soft_coding_keyflex_id
into l_soft_coding_keyflex_id
from hr_soft_coding_keyflex
where concatenated_segments = p_con_seg_user_name
and p_effective_date between
nvl(START_DATE_ACTIVE, START_OF_TIME) AND
nvl(END_DATE_ACTIVE, END_OF_TIME);
select object_version_number
into l_ovn
from pay_us_emp_fed_tax_rules_f puek,
hr_pump_batch_line_user_keys uk
where uk.user_key_value = p_emp_fed_tax_rule_user_key
and p_effective_date between puek.effective_start_date and puek.effective_end_date
and puek.emp_fed_tax_rule_id = uk.unique_key_id;
select object_version_number
into l_ovn
from pay_us_emp_state_tax_rules_f puek,
hr_pump_batch_line_user_keys uk
where uk.user_key_value = p_emp_state_tax_rule_user_key
and p_effective_date between puek.effective_start_date and puek.effective_end_date
and puek.emp_state_tax_rule_id = uk.unique_key_id;
select object_version_number
into l_ovn
from pay_us_emp_county_tax_rules_f puek,
hr_pump_batch_line_user_keys uk
where uk.user_key_value = p_emp_county_tax_rule_user_key
and p_effective_date between puek.effective_start_date and puek.effective_end_date
and puek.emp_county_tax_rule_id = uk.unique_key_id;
select object_version_number
into l_ovn
from pay_us_emp_city_tax_rules_f puek,
hr_pump_batch_line_user_keys uk
where uk.user_key_value = p_emp_city_tax_rule_user_key
and p_effective_date between puek.effective_start_date and puek.effective_end_date
and puek.emp_city_tax_rule_id = uk.unique_key_id;
SELECT competence_id
INTO l_competence_id
FROM per_competences_vl
WHERE name = p_competence_name
AND business_group_id is null;
SELECT competence_id
INTO l_competence_id
FROM per_competences_vl
WHERE name = p_competence_name
AND business_group_id = p_business_group_id;
SELECT object_version_number
INTO l_cpn_ovn
FROM per_competences_vl
WHERE name = p_competence_name
AND business_group_id is null;
SELECT object_version_number
INTO l_cpn_ovn
FROM per_competences_vl
WHERE name = p_competence_name
AND business_group_id = p_business_group_id;
SELECT qualification_type_id
INTO l_qualification_type_id
FROM per_qualification_types_vl
WHERE name = p_qualification_type_name;
SELECT outcome_id
INTO l_outcome_id
FROM per_competence_outcomes_vl
WHERE name = p_outcome_name;
SELECT object_version_number
INTO l_cpo_ovn
FROM per_competence_outcomes_vl
WHERE name = p_outcome_name;
SELECT object_version_number
INTO l_eqt_ovn
FROM per_qualification_types_vl
WHERE name = p_qualification_type_name;
SELECT object_version_number
INTO l_ceo_ovn
FROM per_comp_element_outcomes_vl
WHERE name = p_element_outcome_name;
SELECT competence_element_id
INTO l_competence_element_id
FROM per_competence_elements CEL
,per_competences CPN
WHERE
CEL.type = 'PERSONAL'
and CPN.name = p_competence_name
and CEL.competence_id = CPN.competence_id
and CEL.business_group_id = p_business_group_id
and CEL.person_id = l_person_id;
select id_flex_num into l_cost_code
from fnd_id_flex_structures
where id_flex_structure_code = p_cost_flex_stru_code
and id_flex_code ='COST';
select id_flex_num into l_grade_code
from fnd_id_flex_structures
where id_flex_structure_code = p_grade_flex_stru_code
and id_flex_code ='GRD';
select id_flex_num into l_job_code
from fnd_id_flex_structures
where id_flex_structure_code = p_job_flex_stru_code
and id_flex_code ='JOB';
select id_flex_num into l_position_code
from fnd_id_flex_structures
where id_flex_structure_code = p_position_flex_stru_code
and id_flex_code ='POS';
select id_flex_num into l_group_code
from fnd_id_flex_structures
where id_flex_structure_code = p_group_flex_stru_code
and id_flex_code ='GRP';
select id_flex_num into l_competence_code
from fnd_id_flex_structures
where id_flex_structure_code = p_competence_flex_stru_code
and id_flex_code ='CMP';
select security_group_id into l_get_sec_group_id
from fnd_security_groups_tl
where upper(security_group_name) = upper(p_security_group_name)
and language = userenv('LANG');
select security_profile_id into l_sec_profile_id
from per_security_profiles
where upper(security_profile_name) = upper(p_security_profile_name)
and business_group_id = p_business_group_id;
select org.organization_id
into l_organization_id
from hr_all_organization_units org
, hr_all_organization_units_tl orgtl
where orgtl.name = p_parent_organization_name
and orgtl.language = p_language_code
and org.organization_id = orgtl.organization_id
and (org.business_group_id = p_business_group_id
or p_business_group_id is null); --Bug 3823374
select org.organization_id
into l_organization_id
from hr_all_organization_units org
, hr_all_organization_units_tl orgtl
where orgtl.name = p_child_organization_name
and orgtl.language = p_language_code
and org.organization_id = orgtl.organization_id
and (org.business_group_id = p_business_group_id
or p_business_group_id is null); --Bug fix 3823374
select pei.object_version_number
into l_ovn
from per_people_extra_info pei,
hr_pump_batch_line_user_keys key
where key.user_key_value = p_person_extra_info_user_key
and pei.person_extra_info_id = key.unique_key_id;
select rules.object_version_number
into l_ovn
from PAY_CA_EMP_FED_TAX_INFO_F rules,
hr_pump_batch_line_user_keys key
where key.user_key_value = P_EMP_FED_TAX_INF_USER_KEY
and rules.EMP_FED_TAX_INF_ID = key.unique_key_id
and p_effective_date between
rules.effective_start_date and rules.effective_end_date;
select rules.object_version_number
into l_ovn
from PAY_CA_EMP_PROV_TAX_INFO_F rules,
hr_pump_batch_line_user_keys key
where key.user_key_value = P_EMP_PROV_TAX_INF_USER_KEY
and rules.EMP_PROVINCE_TAX_INF_ID = key.unique_key_id
and p_effective_date between
rules.effective_start_date and rules.effective_end_date;