[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select prtt.run_type_name
from pay_run_types_f_tl prtt,
pay_run_types_f prt
where prt.run_type_id = p_run_type_id
and p_effective_date
between prt.effective_start_date and prt.effective_end_date
and prtt.run_type_id = prt.run_type_id
and prtt.language = userenv('LANG');
select pka.city_province||' '||
pka.district||' '||
pka.town_village
-- pka.house_number -- Commented for Bug# 2506248
from per_kr_addresses pka
where pka.postal_code_id = l_postal_code_id;
procedure delete_action(p_source_action_id in number,
p_dml_mode in varchar2 /* NO_COMMIT, NONE, FULL */)
--------------------------------------------------------------------------------
is
--
begin
--
py_rollback_pkg.rollback_ass_action(p_assignment_action_id => p_source_action_id,
p_rollback_mode => 'ROLLBACK',
p_leave_base_table_row => false,
p_all_or_nothing => true,
p_dml_mode => p_dml_mode,
p_multi_thread => false);
end delete_action;
select *
from pay_assignment_actions
where assignment_action_id = p_source_action_id
for update nowait;
p_update out NOCOPY boolean,
p_update_override out NOCOPY boolean,
p_update_change_insert out NOCOPY boolean)
--------------------------------------------------------------------------------
is
--
l_proc varchar2(72) := g_package||'find_dt_upd_modes';
select pee.entry_type,
pet.processing_type
from pay_element_types_f pet,
pay_element_links_f pel,
pay_element_entries_f pee
where pee.element_entry_id = p_base_key_value
and p_effective_date
between pee.effective_start_date and pee.effective_end_date
and pel.element_link_id = pee.element_link_id
and p_effective_date
between pel.effective_start_date and pel.effective_end_date
and pet.element_type_id = pel.element_type_id
and p_effective_date
between pet.effective_start_date and pet.effective_end_date;
p_update := false;
p_update_override := false;
p_update_change_insert := false;
p_update => p_update,
p_update_override => p_update_override,
p_update_change_insert => p_update_change_insert);
p_delete out NOCOPY boolean,
p_future_change out NOCOPY boolean,
p_delete_next_change out NOCOPY boolean)
--------------------------------------------------------------------------------
is
--
l_proc varchar2(72) := g_package||'find_dt_del_modes';
select pee.assignment_id,
pee.element_link_id,
pee.entry_type,
pet.processing_type
from pay_element_types_f pet,
pay_element_links_f pel,
pay_element_entries_f pee
where pee.element_entry_id = p_base_key_value
and p_effective_date
between pee.effective_start_date and pee.effective_end_date
and pel.element_link_id = pee.element_link_id
and p_effective_date
between pel.effective_start_date and pel.effective_end_date
and pet.element_type_id = pel.element_type_id
and p_effective_date
between pet.effective_start_date and pet.effective_end_date;
p_delete := false;
p_delete_next_change := false;
p_delete => p_delete,
p_future_change => p_future_change,
p_delete_next_change => p_delete_next_change);
select *
from pay_element_entries_f
where element_entry_id = p_element_entry_id
and p_effective_date
between effective_start_date and effective_end_date
for update nowait;
if (p_datetrack_mode <> 'INSERT') then
--
-- We must select and lock the current row.
--
open C_Sel1;
procedure insert_element_entry(
p_validate in boolean,
p_assignment_id in number,
p_business_group_id in number,
p_effective_date in date,
p_element_link_id in number,
p_input_value_id1 in number,
p_input_value_id2 in number,
p_input_value_id3 in number,
p_input_value_id4 in number,
p_input_value_id5 in number,
p_input_value_id6 in number,
p_input_value_id7 in number,
p_input_value_id8 in number,
p_input_value_id9 in number,
p_input_value_id10 in number,
p_input_value_id11 in number,
p_input_value_id12 in number,
p_input_value_id13 in number,
p_input_value_id14 in number,
p_input_value_id15 in number,
p_entry_value1 in varchar2,
p_entry_value2 in varchar2,
p_entry_value3 in varchar2,
p_entry_value4 in varchar2,
p_entry_value5 in varchar2,
p_entry_value6 in varchar2,
p_entry_value7 in varchar2,
p_entry_value8 in varchar2,
p_entry_value9 in varchar2,
p_entry_value10 in varchar2,
p_entry_value11 in varchar2,
p_entry_value12 in varchar2,
p_entry_value13 in varchar2,
p_entry_value14 in varchar2,
p_entry_value15 in varchar2,
p_element_entry_id out NOCOPY number,
p_effective_start_date out NOCOPY date,
p_effective_end_date out NOCOPY date,
p_object_version_number out NOCOPY number)
--------------------------------------------------------------------------------
is
--
l_warning boolean;
end insert_element_entry;
procedure update_element_entry(
p_validate in boolean,
p_dt_update_mode in varchar2, /* UPDATE,UPDATE_CHANGE_INSERT,UPDATE_OVERRIDE,CORRECTION */
p_effective_date in date,
p_business_group_id in number,
p_element_entry_id in number,
p_object_version_number in out NOCOPY number,
p_input_value_id1 in number,
p_input_value_id2 in number,
p_input_value_id3 in number,
p_input_value_id4 in number,
p_input_value_id5 in number,
p_input_value_id6 in number,
p_input_value_id7 in number,
p_input_value_id8 in number,
p_input_value_id9 in number,
p_input_value_id10 in number,
p_input_value_id11 in number,
p_input_value_id12 in number,
p_input_value_id13 in number,
p_input_value_id14 in number,
p_input_value_id15 in number,
p_entry_value1 in varchar2,
p_entry_value2 in varchar2,
p_entry_value3 in varchar2,
p_entry_value4 in varchar2,
p_entry_value5 in varchar2,
p_entry_value6 in varchar2,
p_entry_value7 in varchar2,
p_entry_value8 in varchar2,
p_entry_value9 in varchar2,
p_entry_value10 in varchar2,
p_entry_value11 in varchar2,
p_entry_value12 in varchar2,
p_entry_value13 in varchar2,
p_entry_value14 in varchar2,
p_entry_value15 in varchar2,
p_effective_start_date out NOCOPY date,
p_effective_end_date out NOCOPY date)
--------------------------------------------------------------------------------
is
--
l_warning boolean;
pay_element_entry_api.update_element_entry(
p_validate => p_validate,
p_datetrack_update_mode => p_dt_update_mode,
p_effective_date => p_effective_date,
p_business_group_id => p_business_group_id,
p_element_entry_id => p_element_entry_id,
p_object_version_number => p_object_version_number,
p_input_value_id1 => p_input_value_id1,
p_input_value_id2 => p_input_value_id2,
p_input_value_id3 => p_input_value_id3,
p_input_value_id4 => p_input_value_id4,
p_input_value_id5 => p_input_value_id5,
p_input_value_id6 => p_input_value_id6,
p_input_value_id7 => p_input_value_id7,
p_input_value_id8 => p_input_value_id8,
p_input_value_id9 => p_input_value_id9,
p_input_value_id10 => p_input_value_id10,
p_input_value_id11 => p_input_value_id11,
p_input_value_id12 => p_input_value_id12,
p_input_value_id13 => p_input_value_id13,
p_input_value_id14 => p_input_value_id14,
p_input_value_id15 => p_input_value_id15,
p_entry_value1 => p_entry_value1,
p_entry_value2 => p_entry_value2,
p_entry_value3 => p_entry_value3,
p_entry_value4 => p_entry_value4,
p_entry_value5 => p_entry_value5,
p_entry_value6 => p_entry_value6,
p_entry_value7 => p_entry_value7,
p_entry_value8 => p_entry_value8,
p_entry_value9 => p_entry_value9,
p_entry_value10 => p_entry_value10,
p_entry_value11 => p_entry_value11,
p_entry_value12 => p_entry_value12,
p_entry_value13 => p_entry_value13,
p_entry_value14 => p_entry_value14,
p_entry_value15 => p_entry_value15,
p_effective_start_date => p_effective_start_date,
p_effective_end_date => p_effective_end_date,
p_update_warning => l_warning);
end update_element_entry;
procedure delete_element_entry(
p_validate in boolean,
p_dt_delete_mode in varchar2, /* DELETE,ZAP,DELETE_NEXT_CHANGE,FUTURE_CHANGE */
p_effective_date in date,
p_element_entry_id in number,
p_object_version_number in out NOCOPY number,
p_effective_start_date out NOCOPY date,
p_effective_end_date out NOCOPY date)
--------------------------------------------------------------------------------
is
--
l_warning boolean;
pay_element_entry_api.delete_element_entry(
p_validate => p_validate,
p_datetrack_delete_mode => p_dt_delete_mode,
p_effective_date => p_effective_date,
p_element_entry_id => p_element_entry_id,
p_object_version_number => p_object_version_number,
p_effective_start_date => p_effective_start_date,
p_effective_end_date => p_effective_end_date,
p_delete_warning => l_warning);
end delete_element_entry;
p_dt_update_mode in varchar2,
p_dt_delete_mode in varchar2)
--------------------------------------------------------------------------------
is
begin
hr_entry.chk_element_entry(
p_element_entry_id => p_element_entry_id,
p_original_entry_id => p_original_entry_id,
p_session_date => p_effective_date,
p_element_link_id => p_element_link_id,
p_assignment_id => p_assignment_id,
p_entry_type => p_entry_type,
p_effective_start_date => p_effective_start_date,
p_effective_end_date => p_effective_end_date,
p_validation_start_date => p_validation_start_date,
p_validation_end_date => p_validation_end_date,
p_dt_update_mode => p_dt_update_mode,
p_dt_delete_mode => p_dt_delete_mode,
p_usage => p_usage,
p_target_entry_id => p_target_entry_id);
select item_name NAME,
decode(data_type,'T','TEXT','N','NUMBER','D','DATE') DATATYPE,
decode(usage,'U','CONTEXT','INPUT') CLASS
from ff_fdi_usages_f
where formula_id = p_formula_id
and p_date_earned
between effective_start_date and effective_end_date;
select pivtl.name,
piv.uom,
piv.mandatory_flag,
piv.hot_default_flag,
piv.lookup_type,
decode(piv.hot_default_flag,
'Y',nvl(pliv.default_value,piv.default_value),
pliv.default_value) DEFAULT_VALUE,
-- decode(piv.lookup_type,NULL,NULL,
-- hr_general.decode_lookup(
-- piv.lookup_type,
-- decode(piv.hot_default_flag,
-- 'Y',nvl(pliv.default_value,piv.default_value),
-- pliv.default_value))) D_DEFAULT_VALUE,
decode(piv.hot_default_flag,
'Y',nvl(pliv.min_value,piv.min_value),
pliv.min_value) MIN_VALUE,
decode(piv.hot_default_flag,
'Y',nvl(pliv.max_value,piv.max_value),
pliv.max_value) MAX_VALUE,
piv.formula_id,
decode(piv.hot_default_flag,
'Y',nvl(pliv.warning_or_error,piv.warning_or_error),
pliv.warning_or_error) WARNING_OR_ERROR,
pet.input_currency_code
from pay_element_types_f pet,
pay_input_values_f_tl pivtl,
pay_input_values_f piv,
pay_link_input_values_f pliv
where pliv.element_link_id = p_element_link_id
and pliv.input_value_id = p_input_value_id
and p_effective_date
between pliv.effective_start_date and pliv.effective_end_date
and piv.input_value_id = pliv.input_value_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.language = userenv('LANG')
and pet.element_type_id = piv.element_type_id
and p_effective_date
between pet.effective_start_date and pet.effective_end_date;
select effective_date
from fnd_sessions
where session_id = userenv('sessionid');
select pet.element_type_id
from pay_element_types_f pet
where pet.element_name = p_element_name
and l_effective_date
between pet.effective_start_date and pet.effective_end_date
and nvl(pet.legislation_code,g_legislation_code) = g_legislation_code
and nvl(pet.business_group_id,g_business_group_id) = g_business_group_id;
g_get_element_type_id.element_name.delete;
g_get_element_type_id.element_type_id.delete;
select piv.element_type_id element_type_id,
piv.input_value_id input_value_id,
piv.display_sequence display_sequence,
piv.lookup_type lookup_type,
piv.mandatory_flag mandatory_flag,
piv.name name,
pivtl.name d_name
from pay_input_values_f_tl pivtl,
pay_input_values_f piv
where piv.element_type_id = p_element_type_id
and l_effective_date
between piv.effective_start_date and piv.effective_end_date
and pivtl.input_value_id = piv.input_value_id
and pivtl.language = userenv('LANG')
order by piv.display_sequence, piv.name;
g_get_input_value_id.element_type_id.delete;
g_get_input_value_id.input_value_id.delete;
g_get_input_value_id.display_sequence.delete;
g_get_input_value_id.lookup_type.delete;
g_get_input_value_id.mandatory_flag.delete;
g_get_input_value_id.name.delete;
g_get_input_value_id.d_name.delete;
select piv.name name,
pivtl.name d_name
from pay_input_values_f_tl pivtl,
pay_input_values_f piv
where piv.element_type_id = p_element_type_id
and l_effective_date
between piv.effective_start_date and piv.effective_end_date
and pivtl.input_value_id = piv.input_value_id
and pivtl.language = userenv('LANG')
order by piv.display_sequence, piv.name;
select piv.display_sequence display_sequence
from pay_input_values_f piv
where piv.element_type_id = p_element_type_id
and l_effective_date
between piv.effective_start_date and piv.effective_end_date
order by piv.display_sequence, piv.name;
select piv.lookup_type lookup_type
from pay_input_values_f piv
where piv.element_type_id = p_element_type_id
and l_effective_date
between piv.effective_start_date and piv.effective_end_date
order by piv.display_sequence, piv.name;
select piv.mandatory_flag mandatory_flag
from pay_input_values_f piv
where piv.element_type_id = p_element_type_id
and l_effective_date
between piv.effective_start_date and piv.effective_end_date
order by piv.display_sequence, piv.name;
select piv.input_value_id input_value_id,
piv.display_sequence display_sequence,
hr_chkfmt.changeformat(
decode(piv.lookup_type,
null,
decode(piv.hot_default_flag,
'Y',nvl(pliv.default_value,piv.default_value),
pliv.default_value),
hr_general.decode_lookup(piv.lookup_type,
decode(piv.hot_default_flag,
'Y',nvl(pliv.default_value,piv.default_value),
pliv.default_value))),
piv.uom,
pet.output_currency_code) default_value,
decode(piv.lookup_type,
null,
null,
decode(piv.hot_default_flag,
'Y',nvl(pliv.default_value,piv.default_value),
pliv.default_value)) b_default_value
from pay_element_types_f pet,
pay_input_values_f piv,
pay_link_input_values_f pliv
where pliv.element_link_id = p_element_link_id
and p_effective_date
between pliv.effective_start_date and pliv.effective_end_date
and piv.input_value_id = pliv.input_value_id
and p_effective_date
between piv.effective_start_date and piv.effective_end_date
and pet.element_type_id = piv.element_type_id
and p_effective_date
between pet.effective_start_date and pet.effective_end_date
order by piv.display_sequence, piv.name;
p_usage => 'INSERT',
p_dt_update_mode => NULL,
p_dt_delete_mode => NULL);
select peev.input_value_id input_value_id,
peev.screen_entry_value screen_entry_value
from pay_element_entry_values_f peev
where peev.element_entry_id = p_ee_element_entry_id
and peev.effective_start_date = p_ee_effective_start_date
and peev.effective_end_date = p_ee_effective_end_date;
g_screen_entry_value_tbl.delete;
select substr(hr_chkfmt.changeformat(
decode(piv.lookup_type,
null,
nvl(l_screen_value,decode(piv.hot_default_flag,
'Y',nvl(pliv.default_value,piv.default_value),
pliv.default_value)),
hr_general.decode_lookup(piv.lookup_type,
nvl(l_screen_value,decode(piv.hot_default_flag,
'Y',nvl(pliv.default_value,piv.default_value),
pliv.default_value)))),piv.uom,pet.output_currency_code),1,60) entry_value
from pay_element_types_f pet,
pay_input_values_f piv,
pay_link_input_values_f pliv
where pliv.element_link_id = p_el_element_link_id
and pliv.input_value_id = l_input_value_id
and g_session_date /* set by get_input_value_id */
between pliv.effective_start_date and pliv.effective_end_date
and piv.input_value_id = pliv.input_value_id
and g_session_date
between piv.effective_start_date and piv.effective_end_date
and pet.element_type_id = piv.element_type_id
and g_session_date
between pet.effective_start_date and pet.effective_end_date;
select petr.element_type_id
from pay_element_type_rules petr
where petr.element_set_id = p_element_set_id
and petr.include_or_exclude = 'I'
and not exists(select null
from pay_element_type_rules petr
where petr.element_set_id = p_element_set_id
and petr.include_or_exclude = 'E')
and not exists(select null
from pay_element_type_usages_f npetu
where npetu.element_type_id = petr.element_type_id
and p_session_date between npetu.effective_start_date and npetu.effective_end_date
and npetu.inclusion_flag = 'N'
and npetu.run_type_id = p_run_type_id
and npetu.business_group_id = p_business_group_id)
and nvl(hr_entry_api.get_link(p_assignment_id,petr.element_type_id,p_session_date),0) <> 0
and petr.element_type_id not in(
select distinct pet.element_type_id
from pay_element_entries_f pee,
pay_element_types_f pet,
pay_element_links_f pel
where assignment_id = p_assignment_id
and pet.element_type_id = pel.element_type_id
and pee.element_link_id = pel.element_link_id
and p_session_date between pet.effective_start_date and pet.effective_end_date
and p_session_date between pel.effective_start_date and pel.effective_end_date
and p_session_date between pee.effective_start_date and pee.effective_end_date
and (pet.business_group_id is null or pet.business_group_id = p_business_group_id)
and (pel.business_group_id is null or pel.business_group_id = p_business_group_id));
select hasa.assignment_id
from hr_assignment_sets has,
hr_assignment_set_amendments hasa
where business_group_id = p_business_group_id
and has.assignment_set_id = p_assignment_set_id
and has.payroll_id = p_payroll_id
and hasa.assignment_set_id = has.assignment_set_id
and hasa.include_or_exclude = 'I';
select petr.element_type_id
from pay_element_type_rules petr
where petr.element_set_id = p_element_set_id
and petr.include_or_exclude = 'I'
and not exists(select null
from pay_element_type_rules petr
where petr.element_set_id = p_element_set_id
and petr.include_or_exclude = 'E')
and not exists(select null
from pay_element_type_usages_f npetu
where npetu.element_type_id = petr.element_type_id
and p_session_date between npetu.effective_start_date and npetu.effective_end_date
and npetu.inclusion_flag = 'N'
and npetu.run_type_id = p_run_type_id
and npetu.business_group_id = p_business_group_id)
and nvl(hr_entry_api.get_link(p_assignment_id,petr.element_type_id,p_session_date),0) <> 0
and petr.element_type_id not in(
select distinct pet.element_type_id
from pay_element_entries_f pee,
pay_element_types_f pet,
pay_element_links_f pel
where assignment_id = p_assignment_id
and pet.element_type_id = pel.element_type_id
and pee.element_link_id = pel.element_link_id
and p_session_date between pet.effective_start_date and pet.effective_end_date
and p_session_date between pel.effective_start_date and pel.effective_end_date
and p_session_date between pee.effective_start_date and pee.effective_end_date
and (pet.business_group_id is null or pet.business_group_id = p_business_group_id)
and (pel.business_group_id is null or pel.business_group_id = p_business_group_id));
pay_kr_sep_form_pkg.insert_element_entry(
p_validate => false,
p_assignment_id => p_assignment_id,
p_business_group_id => p_business_group_id,
p_effective_date => p_effective_date,
p_element_link_id => l_element_link_id,
p_input_value_id1 => l_input_value_id1,
p_input_value_id2 => l_input_value_id2,
p_input_value_id3 => l_input_value_id3,
p_input_value_id4 => l_input_value_id4,
p_input_value_id5 => l_input_value_id5,
p_input_value_id6 => l_input_value_id6,
p_input_value_id7 => l_input_value_id7,
p_input_value_id8 => l_input_value_id8,
p_input_value_id9 => l_input_value_id9,
p_input_value_id10 => l_input_value_id10,
p_input_value_id11 => l_input_value_id11,
p_input_value_id12 => l_input_value_id12,
p_input_value_id13 => l_input_value_id13,
p_input_value_id14 => l_input_value_id14,
p_input_value_id15 => l_input_value_id15,
p_entry_value1 => l_default_value1 ,
p_entry_value2 => l_default_value2 ,
p_entry_value3 => l_default_value3 ,
p_entry_value4 => l_default_value4 ,
p_entry_value5 => l_default_value5 ,
p_entry_value6 => l_default_value6 ,
p_entry_value7 => l_default_value7 ,
p_entry_value8 => l_default_value8 ,
p_entry_value9 => l_default_value9 ,
p_entry_value10 => l_default_value10 ,
p_entry_value11 => l_default_value11 ,
p_entry_value12 => l_default_value12 ,
p_entry_value13 => l_default_value13 ,
p_entry_value14 => l_default_value14 ,
p_entry_value15 => l_default_value15 ,
p_element_entry_id => p_element_entry_id,
p_effective_start_date => p_effective_start_date,
p_effective_end_date => p_effective_end_date,
p_object_version_number => p_object_version_number);
select decode(prt.run_type_name,'SEP','P','SEP_I','P','U')
from pay_assignment_actions paa,
pay_payroll_actions ppa,
pay_run_types_f prt
where ppa.payroll_action_id = paa.payroll_action_id
and ppa.run_type_id = prt.run_type_id
and prt.run_type_name = p_run_type_name
and paa.assignment_id = p_assignment_id
and paa.source_action_id is not null
and ppa.effective_date between trunc(p_date_earned,'YYYY') and p_date_earned
order by prt.run_type_name desc;