[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select elt.multiple_entries_allowed_flag,
ipv.input_value_id,
ipv.uom,
eli.element_link_id,
ele.element_entry_id,
eev.screen_entry_value screen_entry_value,
ele.object_version_number
from pay_element_types_f elt,
pay_input_values_f ipv,
pay_element_links_f eli,
pay_element_entries_f ele,
pay_element_entry_values_f eev
where trunc(eff_date) between elt.effective_start_date
and elt.effective_end_date
and trunc(eff_date) between ipv.effective_start_date
and ipv.effective_end_date
and trunc(eff_date) between eli.effective_start_date
and eli.effective_end_date
and trunc(eff_date) between ele.effective_start_date
and ele.effective_end_date
and trunc(eff_date) between eev.effective_start_date
and eev.effective_end_date
and elt.element_type_id = ipv.element_type_id
and elt.element_type_id = eli.element_type_id + 0
and upper(elt.element_name) = upper(ele_name)
and ipv.input_value_id = eev.input_value_id
and ele.assignment_id = asg_id
and ele.element_entry_id + 0 = eev.element_entry_id
and ele.element_link_id = eli.element_link_id
and upper(ipv.name) = upper(input_name)
-- and NVL(elt.business_group_id,0) = NVL(ipv.business_group_id,0)
and (elt.business_group_id is null or elt.business_group_id = bg_id);
select elt.multiple_entries_allowed_flag,
ipv.input_value_id,
ipv.uom,
eli.element_link_id,
ele.element_entry_id,
eev.screen_entry_value screen_entry_value,
ele.object_version_number
from pay_element_types_f elt,
pay_input_values_f ipv,
pay_element_links_f eli,
pay_element_entries_f ele,
pay_element_entry_values_f eev
where trunc(eff_date) between elt.effective_start_date
and elt.effective_end_date
and trunc(eff_date) between ipv.effective_start_date
and ipv.effective_end_date
and trunc(eff_date) between eli.effective_start_date
and eli.effective_end_date
and ele.effective_end_date =
(select max(ele2.effective_end_date)
from pay_element_entries_f ele2
where ele2.element_entry_id = ele.element_entry_id)
and eev.effective_end_date =
(select max(eev2.effective_end_date)
from pay_element_entries_f eev2
where eev2.element_entry_id = eev.element_entry_id)
and elt.element_type_id = ipv.element_type_id
and elt.element_type_id = eli.element_type_id + 0
and upper(elt.element_name) = upper(ele_name)
and ipv.input_value_id = eev.input_value_id
and ele.assignment_id = asg_id
and ele.element_entry_id + 0 = eev.element_entry_id
and ele.element_link_id = eli.element_link_id
and upper(ipv.name) = upper(input_name)
-- and NVL(elt.business_group_id,0) = NVL(ipv.business_group_id,0)
and (elt.business_group_id is null or elt.business_group_id = bg_id);
select elt.multiple_entries_allowed_flag,
ipv.input_value_id,
ipv.uom,
eli.element_link_id,
ele.element_entry_id,
eev.screen_entry_value screen_entry_value,
ele.object_version_number
from pay_element_types_f elt,
pay_input_values_f ipv,
pay_element_links_f eli,
pay_element_entries_f ele,
pay_element_entry_values_f eev
where trunc(eff_date) between elt.effective_start_date
and elt.effective_end_date
and trunc(eff_date) between ipv.effective_start_date
and ipv.effective_end_date
and trunc(eff_date) between eli.effective_start_date
and eli.effective_end_date
and trunc(eff_date) between ele.effective_start_date
and ele.effective_end_date
and trunc(eff_date) between eev.effective_start_date
and eev.effective_end_date
and elt.element_type_id = ipv.element_type_id
and elt.element_type_id = eli.element_type_id + 0
and upper(elt.element_name) = upper(ele_name)
and ipv.input_value_id = eev.input_value_id
and ele.assignment_id = asg_id
and ele.element_entry_id + 0 = eev.element_entry_id
and ele.element_link_id = eli.element_link_id
and upper(ipv.name) = upper(input_name)
-- and NVL(elt.business_group_id,0) = NVL(ipv.business_group_id,0)
and (elt.business_group_id is null or elt.business_group_id = bg_id);
select object_version_number
from pay_element_entries_f
where element_entry_id = l_element_entry_id
and p_effective_date
between effective_start_date and effective_end_date;
Select distinct business_group_id bg
from per_assignments_f
where assignment_id = p_assignment_id
and p_eff_date between effective_start_date
and effective_end_date;
Select from_pay_basis,to_pay_basis
From ghr_pa_requests
Where pa_request_id=l_session.pa_request_id;
Function return_update_mode
(p_id in pay_element_entries_f.element_entry_id%type,
p_effective_date in date
) return varchar2 is
l_proc varchar2(72) := 'return_update_mode';
cursor c_update_mode_e is
select ele.effective_start_date ,
ele.effective_end_date
from pay_element_entries_f ele
where ele.element_entry_id = p_id
and p_effective_date
between ele.effective_start_date
and ele.effective_end_date;
cursor c_update_mode_e1 is
select ele.effective_start_date ,
ele.effective_end_date
from pay_element_entries_f ele
where ele.element_entry_id = p_id
and p_effective_date < ele.effective_start_date
order by 1 asc;
for update_mode in c_update_mode_e loop
hr_utility.set_location(l_proc,15);
l_esd := update_mode.effective_start_date;
l_eed := update_mode.effective_end_date;
l_mode := 'UPDATE';
for update_mode1 in c_update_mode_e1 loop
hr_utility.set_location(l_proc,40);
l_mode := 'UPDATE_CHANGE_INSERT';
l_mode := 'UPDATE';
end return_update_mode;
select ipv.input_value_id
from pay_element_types_f elt,
pay_input_values_f ipv
where trunc(eff_date) between elt.effective_start_date
and elt.effective_end_date
and trunc(eff_date) between ipv.effective_start_date
and ipv.effective_end_date
and elt.element_type_id = ipv.element_type_id
and upper(elt.element_name) = upper(ele_name)
and upper(ipv.name) = upper(input_name)
-- and NVL(elt.business_group_id,0) = NVL(ipv.business_group_id,0) --Ashley
and (elt.business_group_id is null or elt.business_group_id = bg_id);
l_update_warning boolean;
l_delete_warning boolean;
l_update_element_entry boolean := FALSE;
l_update_mode varchar2(20);
select asg.business_group_id
from per_all_assignments_f asg
where asg.assignment_id = asg_id
and eff_date between asg.effective_start_date
and asg.effective_end_date;
select elt.element_type_id,
elt.processing_type
from pay_element_types_f elt
where trunc(eff_date) between elt.effective_start_date
and elt.effective_end_date
and upper(elt.element_name) = upper(ele_name)
and ( elt.business_group_id is NULL or
elt.business_group_id = bg_id );
select noa.code
from ghr_nature_of_actions noa
where noa.nature_of_action_id = l_noa_id;
Select from_pay_basis,to_pay_basis
From ghr_pa_requests
Where pa_request_id=l_session.pa_request_id;
SELECT count(*) cnt
FROM pay_element_entries_f ee, pay_element_types_f et
WHERE ee.assignment_id = l_asg_id
AND ee.element_type_id = et.element_type_id
AND et.element_name = l_element_name
AND l_effective_date between ee.effective_start_date
AND ee.effective_end_date;
l_update_mode := return_update_mode(p_id => l_element_entry_id,
p_effective_date => p_effective_date
);
l_update_mode := return_update_mode(p_id => l_element_entry_id,
p_effective_date => p_effective_date
);
-- Bug 2709841 When Retention percentage is made null, then update the percentage field with null.
IF p_element_name = 'Retention Allowance' AND p_value2 IS NULL AND p_value1 IS NOT NULL THEN
l_value2 := p_value2;
py_element_entry_api.update_element_entry
(p_datetrack_update_mode => l_update_mode
,p_effective_date => p_effective_date
,p_business_group_id => l_business_group_id
,p_element_entry_id => l_element_entry_id
,p_object_version_number => l_object_version_number
,p_input_value_id1 => l_input_value_id1
,p_entry_value1 => nvl(p_value1,l_value1)
,p_input_value_id2 => l_input_value_id2
,p_entry_value2 => nvl(p_value2,l_value2)
,p_input_value_id3 => l_input_value_id3
,p_entry_value3 => nvl(p_value3,l_value3)
,p_input_value_id4 => l_input_value_id4
,p_entry_value4 => nvl(p_value4,l_value4)
,p_input_value_id5 => l_input_value_id5
,p_entry_value5 => nvl(p_value5,l_value5)
,p_input_value_id6 => l_input_value_id6
,p_entry_value6 => nvl(p_value6,l_value6)
,p_input_value_id7 => l_input_value_id7
,p_entry_value7 => nvl(p_value7,l_value7)
,p_input_value_id8 => l_input_value_id8
,p_entry_value8 => nvl(p_value8,l_value8)
,p_input_value_id9 => l_input_value_id9
,p_entry_value9 => nvl(p_value9,l_value9)
,p_input_value_id10 => l_input_value_id10
,p_entry_value10 => nvl(p_value10,l_value10)
,p_input_value_id11 => l_input_value_id11
,p_entry_value11 => nvl(p_value11,l_value11)
,p_input_value_id12 => l_input_value_id12
,p_entry_value12 => nvl(p_value12,l_value12)
,p_input_value_id13 => l_input_value_id13
,p_entry_value13 => nvl(p_value13,l_value13)
,p_input_value_id14 => l_input_value_id14
,p_entry_value14 => nvl(p_value14,l_value14)
,p_input_value_id15 => l_input_value_id15
,p_entry_value15 => nvl(l_p_value15,l_value15)
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
,p_update_warning => l_update_warning);
pay_element_entry_api.delete_element_entry
(p_validate => false
,p_datetrack_delete_mode => 'DELETE'
,p_effective_date => l_biweekly_end_date
,p_element_entry_id => l_element_entry_id
,p_object_version_number => l_object_version_number
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
,p_delete_warning => l_delete_warning
);