[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select pee.element_entry_id element_entry_id
,pet.processing_type processing_type
,pee.effective_start_date effective_start_date
,pee.effective_end_date effective_end_date
from per_absence_attendances abs
,per_all_assignments_f asg
,pay_element_types_f pet
,pay_element_links_f pel
,pay_element_entries_f pee
,pay_element_entry_values_f peev
,pay_input_values_f piv
where abs.absence_attendance_id = p_absence_attendance_id
and abs.person_id = asg.person_id
and asg.assignment_id = p_assignment_id
and nvl(abs.date_start,asg.effective_end_date) <= asg.effective_end_date
and nvl(abs.date_end,asg.effective_start_date) >= asg.effective_start_date
and pet.element_type_id = p_element_type_id
and nvl(abs.date_start,pet.effective_end_date) <= pet.effective_end_date
and nvl(abs.date_end,pet.effective_start_date) >= pet.effective_start_date
and pet.element_type_id = pel.element_type_id
and pel.element_link_id = pee.element_link_id
and pee.assignment_id = asg.assignment_id
and pee.creator_type = 'F'
and pet.element_type_id = piv.element_type_id
and nvl(abs.date_start,piv.effective_end_date) <= piv.effective_end_date
and nvl(abs.date_end,piv.effective_start_date) >= piv.effective_start_date
and piv.name = 'CREATOR_ID'
and peev.element_entry_id = pee.element_entry_id
and peev.input_value_id=piv.input_value_id
and peev.screen_entry_value = abs.absence_attendance_id;
select pee.element_entry_id element_entry_id
,pet.processing_type processing_type
,pee.effective_start_date effective_start_date
,pee.effective_end_date effective_end_date
from per_absence_attendances abs
,per_all_assignments_f asg
,pay_element_types_f pet
,pay_element_links_f pel
,pay_element_entries_f pee
where abs.absence_attendance_id = p_absence_attendance_id
and abs.person_id = asg.person_id
and asg.assignment_id = p_assignment_id
and abs.date_start <= asg.effective_end_date
and abs.date_end >= asg.effective_start_date
and pet.element_type_id = p_element_type_id
and abs.date_start >= pet.effective_start_date
and abs.date_end <= pet.effective_end_date
and pet.element_type_id = pel.element_type_id
and pel.element_link_id = pee.element_link_id
and pee.assignment_id = asg.assignment_id
and pee.creator_id = abs.absence_attendance_id
and pee.creator_type = 'F';*/
SELECT abs.business_group_id
,abt.name
,abs.person_id
,abs.date_start
,abs.date_end
,abt.absence_category
FROM per_absence_attendances abs
,per_absence_attendance_types abt
WHERE abs.absence_attendance_id = p_absence_attendance_id
AND abt.absence_attendance_type_id = abs.absence_attendance_type_id;
SELECT iv.input_value_id input_value
,p_iv1_value entry_value
FROM pay_input_values_f iv
WHERE iv.element_type_id = p_element_type_id
AND iv.name = p_iv1_name
AND p_effective_date BETWEEN iv.effective_start_date AND iv.effective_end_date;
SELECT legislation_code
FROM per_business_groups_perf
WHERE business_group_id = p_business_group_id;
SELECT pur.legislation_code, pur.business_group_id
FROM pay_user_tables put
,pay_user_rows_f pur
WHERE user_table_name = p_table_name
AND nvl(put.legislation_code, p_legislation_code) = p_legislation_code
AND put.user_table_id = pur.user_table_id
and pur.row_low_range_or_name = p_exact ;
SELECT REPLACE(l_plsql_block, '', l_entry_package) INTO l_plsql_block FROM dual;
SELECT REPLACE(l_plsql_block, '', l_entry_package) INTO l_plsql_block FROM dual;
SELECT asg.assignment_id, asg.effective_start_date, asg.effective_end_date
FROM per_all_assignments_f asg,
per_absence_attendances paa
WHERE paa.absence_attendance_id = p_absence_attendance_id
and asg.person_id = paa.person_id
AND nvl(paa.date_start,asg.effective_end_date) <= asg.effective_end_date
AND nvl(paa.date_end,asg.effective_start_date) >= asg.effective_start_date
ORDER BY asg.assignment_id, asg.effective_start_date, asg.effective_end_date;
SELECT processing_type
FROM pay_element_types_f
where element_type_id= p_element_type_id
AND p_effective_date between effective_start_date and effective_end_date;
hr_entry_api.insert_element_entry
(p_effective_start_date => l_date_start
,p_effective_end_date => l_date_end
,p_element_entry_id => l_element_entry_id
,p_assignment_id => l_asg_rec.assignment_id
,p_element_link_id => l_element_link_id
,p_creator_type => 'F' -- 'A' for absence
,p_entry_type => 'E'
,p_creator_id => null--p_absence_attendance_id
,p_original_entry_id => l_original_entry_id --pgopal
,p_input_value_id1 => l_input_value_id
,p_entry_value1 => l_input_value
,p_input_value_id2 => l_iv2_id
,p_entry_value2 => l_iv2_value
,p_input_value_id3 => l_iv3_id
,p_entry_value3 => l_iv3_value
,p_input_value_id4 => l_iv4_id
,p_entry_value4 => l_iv4_value
,p_input_value_id5 => l_iv5_id
,p_entry_value5 => l_iv5_value
,p_input_value_id6 => l_iv6_id
,p_entry_value6 => l_iv6_value
,p_input_value_id7 => l_iv7_id
,p_entry_value7 => l_iv7_value
,p_input_value_id8 => l_iv8_id
,p_entry_value8 => l_iv8_value
,p_input_value_id9 => l_iv9_id
,p_entry_value9 => l_iv9_value
,p_input_value_id10 => l_iv10_id
,p_entry_value10 => l_iv10_value
,p_input_value_id11 => l_iv11_id
,p_entry_value11 => l_iv11_value
,p_input_value_id12 => l_iv12_id
,p_entry_value12 => l_iv12_value
,p_input_value_id13 => l_iv13_id
,p_entry_value13 => l_iv13_value
,p_input_value_id14 => l_iv14_id
,p_entry_value14 => l_iv14_value
,p_input_value_id15 => l_iv15_id
,p_entry_value15 => l_iv15_value);
hr_entry_api.delete_element_entry
(p_dt_delete_mode => 'DELETE'
,p_session_date => p_date_end
,p_element_entry_id => l_element_entry_id);
SELECT abs.business_group_id
,abt.name
,abs.person_id
,abs.date_start
,abs.date_end
,abt.absence_category
FROM per_absence_attendances abs
,per_absence_attendance_types abt
WHERE abs.absence_attendance_id = p_absence_attendance_id
AND abt.absence_attendance_type_id = abs.absence_attendance_type_id;
SELECT legislation_code
FROM per_business_groups_perf
WHERE business_group_id = p_business_group_id;
SELECT element_type_id
FROM pay_element_types_f pet
WHERE pet.element_name = p_element_name
AND nvl( pet.business_group_id, p_business_group_id) = p_business_group_id
AND nvl( pet.legislation_code, p_legislation_code) = p_legislation_code;
SELECT effective_date
FROM fnd_sessions
WHERE session_id = userenv('sessionid');
SELECT abs.business_group_id
,abt.name
,abs.person_id
,abs.date_start
,abs.date_end
,abt.absence_category
FROM per_absence_attendances abs
,per_absence_attendance_types abt
WHERE abs.absence_attendance_id = p_absence_attendance_id
AND abt.absence_attendance_type_id = abs.absence_attendance_type_id;
SELECT legislation_code
FROM per_business_groups_perf
WHERE business_group_id = p_business_group_id;
select pee.element_entry_id element_entry_id
,pet.processing_type processing_type
,pee.effective_start_date effective_start_date
,pee.effective_end_date effective_end_date
from per_absence_attendances abs
,per_all_assignments_f asg
,pay_element_types_f pet
,pay_element_links_f pel
,pay_element_entries_f pee
,pay_element_entry_values_f peev
,pay_input_values_f piv
where abs.absence_attendance_id = p_absence_attendance_id
and abs.person_id = asg.person_id
and asg.assignment_id = p_assignment_id
and nvl(abs.date_start,asg.effective_end_date) <= asg.effective_end_date
and nvl(abs.date_end,asg.effective_start_date) >= asg.effective_start_date
and pet.element_type_id = p_element_type_id
and p_effective_date between pet.effective_start_date and pet.effective_end_date
and pet.element_type_id = pel.element_type_id
and pel.element_link_id = pee.element_link_id
and pee.assignment_id = asg.assignment_id
and pee.creator_type = 'F'
and pet.element_type_id = piv.element_type_id
and p_effective_date between piv.effective_start_date and piv.effective_end_date
and piv.name = 'CREATOR_ID'
and peev.element_entry_id = pee.element_entry_id
and peev.input_value_id=piv.input_value_id
and peev.screen_entry_value = abs.absence_attendance_id;
/* select pee.element_entry_id
,pet.processing_type
,pee.effective_start_date
,pee.effective_end_date
from per_absence_attendances abs
,per_all_assignments_f asg
,pay_element_types_f pet
,pay_element_links_f pel
,pay_element_entries_f pee
where abs.absence_attendance_id = p_absence_attendance_id
and abs.person_id = asg.person_id
and asg.assignment_id = p_assignment_id
and nvl(abs.date_start,asg.effective_end_date) <= asg.effective_end_date
and nvl(abs.date_end,asg.effective_start_date) >= asg.effective_start_date
and pet.element_type_id = p_element_type_id
and p_effective_date between pet.effective_start_date and pet.effective_end_date
and pet.element_type_id = pel.element_type_id
and pel.element_link_id = pee.element_link_id
and pee.assignment_id = asg.assignment_id
and pee.creator_id = abs.absence_attendance_id
and pee.creator_type = 'F';*/
procedure delete_absence_element
(p_dt_delete_mode in varchar2
,p_session_date in date
,p_element_entry_id in number
) is
l_proc varchar2(72) := g_package||'delete_absence_element';
hr_utility.set_location('p_dt_delete_mode :'|| p_dt_delete_mode, 30);
hr_entry_api.delete_element_entry
(p_dt_delete_mode => p_dt_delete_mode
,p_session_date => p_session_date
,p_element_entry_id => p_element_entry_id);
end delete_absence_element;
procedure update_absence_element
(p_dt_update_mode in varchar2
,p_assignment_id in number
,p_session_date in date
,p_element_entry_id in number
,p_absence_attendance_id in number
) is
l_proc varchar2(72) := g_package||'update_absence_element';
hr_utility.set_location('Dt update mode : '||p_dt_update_mode, 20);
hr_entry_api.update_element_entry
(p_dt_update_mode => p_dt_update_mode
,p_session_date => p_session_date
,p_element_entry_id => p_element_entry_id
,p_creator_type => 'F'
,p_creator_id => p_absence_attendance_id
,p_original_entry_id => l_original_entry_id --pgopal
,p_input_value_id1 => l_input_value_id
,p_entry_value1 => l_entry_value
,p_input_value_id2 => l_iv2_id
,p_entry_value2 => l_iv2_value
,p_input_value_id3 => l_iv3_id
,p_entry_value3 => l_iv3_value
,p_input_value_id4 => l_iv4_id
,p_entry_value4 => l_iv4_value
,p_input_value_id5 => l_iv5_id
,p_entry_value5 => l_iv5_value
,p_input_value_id6 => l_iv6_id
,p_entry_value6 => l_iv6_value
,p_input_value_id7 => l_iv7_id
,p_entry_value7 => l_iv7_value
,p_input_value_id8 => l_iv8_id
,p_entry_value8 => l_iv8_value
,p_input_value_id9 => l_iv9_id
,p_entry_value9 => l_iv9_value
,p_input_value_id10 => l_iv10_id
,p_entry_value10 => l_iv10_value
,p_input_value_id11 => l_iv11_id
,p_entry_value11 => l_iv11_value
,p_input_value_id12 => l_iv12_id
,p_entry_value12 => l_iv12_value
,p_input_value_id13 => l_iv13_id
,p_entry_value13 => l_iv13_value
,p_input_value_id14 => l_iv14_id
,p_entry_value14 => l_iv14_value
,p_input_value_id15 => l_iv15_id
,p_entry_value15 => l_iv15_value);
end update_absence_element;
procedure insert_absence_element
(p_date_start in date
,p_assignment_id in number
,p_absence_attendance_id in number
,p_element_entry_id out nocopy number
) is
l_proc varchar2(72) := g_package||'insert_absence_element';
hr_utility.set_location('Inserting element', 30);
hr_entry_api.insert_element_entry
(p_effective_start_date => l_date_start
,p_effective_end_date => l_date_end
,p_element_entry_id => p_element_entry_id
,p_assignment_id => p_assignment_id
,p_element_link_id => l_element_link_id
,p_creator_type => 'F'
,p_entry_type => 'E'
,p_creator_id => null--p_absence_attendance_id
,p_original_entry_id => l_original_entry_id --pgopal
,p_input_value_id1 => l_input_value_id
,p_entry_value1 => l_entry_value
,p_input_value_id2 => l_iv2_id
,p_entry_value2 => l_iv2_value
,p_input_value_id3 => l_iv3_id
,p_entry_value3 => l_iv3_value
,p_input_value_id4 => l_iv4_id
,p_entry_value4 => l_iv4_value
,p_input_value_id5 => l_iv5_id
,p_entry_value5 => l_iv5_value
,p_input_value_id6 => l_iv6_id
,p_entry_value6 => l_iv6_value
,p_input_value_id7 => l_iv7_id
,p_entry_value7 => l_iv7_value
,p_input_value_id8 => l_iv8_id
,p_entry_value8 => l_iv8_value
,p_input_value_id9 => l_iv9_id
,p_entry_value9 => l_iv9_value
,p_input_value_id10 => l_iv10_id
,p_entry_value10 => l_iv10_value
,p_input_value_id11 => l_iv11_id
,p_entry_value11 => l_iv11_value
,p_input_value_id12 => l_iv12_id
,p_entry_value12 => l_iv12_value
,p_input_value_id13 => l_iv13_id
,p_entry_value13 => l_iv13_value
,p_input_value_id14 => l_iv14_id
,p_entry_value14 => l_iv14_value
,p_input_value_id15 => l_iv15_id
,p_entry_value15 => l_iv15_value);
end insert_absence_element;
PROCEDURE update_absence(p_absence_attendance_id NUMBER,
p_date_start DATE,
p_date_end DATE,
P_EFFECTIVE_DATE DATE) IS
--
--
-- Local Cursors.
--
-- Find all assignments for the person as of a given date.
-- Bug no 5020916. Order by clause included in the cursor
CURSOR csr_assignments(p_person_id NUMBER, p_effective_date DATE) IS
SELECT asg.assignment_id, asg.effective_start_date, asg.effective_end_date
FROM per_all_assignments_f asg,
per_absence_attendances paa
WHERE paa.absence_attendance_id = p_absence_attendance_id
and asg.person_id = paa.person_id
and paa.person_id = p_person_id
AND nvl(paa.date_start,asg.effective_end_date) <= asg.effective_end_date
AND nvl(paa.date_end,asg.effective_start_date) >= asg.effective_start_date
ORDER BY asg.assignment_id, asg.effective_start_date, asg.effective_end_date;
SELECT iv.input_value_id input_value1
,p_iv1_value entry_value1
FROM pay_input_values_f iv
WHERE iv.element_type_id = p_element_type_id
AND iv.name = p_iv1_name
AND p_effective_date BETWEEN iv.effective_start_date AND iv.effective_end_date;
SELECT processing_type
FROM pay_element_types_f
where element_type_id= p_element_type_id
AND p_effective_date between effective_start_date and effective_end_date;
SELECT pee.element_entry_id, pee.effective_start_date
FROM pay_element_entries_f pee,
per_all_assignments_f asg1,
per_absence_attendances paa1
where pee.element_type_id = p_element_type_id
AND paa1.absence_attendance_id = p_absence_attendance_id
AND asg1.person_id = paa1.person_id
AND asg1.assignment_id not in (
SELECT asg.assignment_id
FROM per_all_assignments_f asg,
per_absence_attendances paa
WHERE paa.absence_attendance_id = p_absence_attendance_id
AND asg.person_id = paa.person_id
AND nvl(paa.date_start,asg.effective_end_date) <= asg.effective_end_date
AND nvl(paa.date_end,asg.effective_start_date) >= asg.effective_start_date);
select abs.person_id,
abs.absence_attendance_type_id
from per_absence_attendances abs
where abs.absence_attendance_id = p_absence_attendance_id;
l_proc varchar2(72) := g_package||'update_person_absence';
insert_absence_element
(p_date_start => l_date_start
,p_assignment_id => l_assignment_id.assignment_id
,p_absence_attendance_id => p_absence_attendance_id
,p_element_entry_id => l_element_entry_id);
delete_absence_element
(p_dt_delete_mode => 'DELETE'
,p_session_date => l_date_end
,p_element_entry_id => l_element_entry_id);
hr_utility.set_location('before delete absence element ', 40);
delete_absence_element
(p_dt_delete_mode => 'ZAP'
,p_session_date => l_effective_start_date
,p_element_entry_id => l_element_entry_id);
update_absence_element
(p_dt_update_mode => 'CORRECTION'
,p_assignment_id => l_assignment_id.assignment_id
,p_session_date => l_effective_start_date
,p_element_entry_id => l_element_entry_id
,p_absence_attendance_id => p_absence_attendance_id);
delete_absence_element
(p_dt_delete_mode => 'ZAP'
,p_session_date => l_effective_start_date
,p_element_entry_id => l_element_entry_id);
insert_absence_element
(p_date_start => l_date_start
,p_assignment_id => l_assignment_id.assignment_id
,p_absence_attendance_id => p_absence_attendance_id
,p_element_entry_id => l_element_entry_id);
delete_absence_element
(p_dt_delete_mode => 'DELETE'
,p_session_date => l_date_end
,p_element_entry_id => l_element_entry_id);
update_absence_element
(p_dt_update_mode => 'CORRECTION'
,p_assignment_id => l_assignment_id.assignment_id
,p_session_date => l_effective_start_date
,p_element_entry_id => l_element_entry_id
,p_absence_attendance_id => p_absence_attendance_id);
delete_absence_element
(p_dt_delete_mode => 'DELETE_NEXT_CHANGE'
,p_session_date => l_effective_end_date
,p_element_entry_id => l_element_entry_id);
delete_absence_element
(p_dt_delete_mode => 'DELETE'
,p_session_date => l_date_end
,p_element_entry_id => l_element_entry_id);
update_absence_element
(p_dt_update_mode => 'CORRECTION'
,p_assignment_id => l_assignment_id.assignment_id
,p_session_date => l_effective_start_date
,p_element_entry_id => l_element_entry_id
,p_absence_attendance_id => p_absence_attendance_id);
/*logic to delete the element entries which are not valid due to change in absence dates*/
l_element_type_id := get_element_for_category (p_absence_attendance_id );
delete_absence_element
(p_dt_delete_mode => 'ZAP'
,p_session_date => l_invalid_entries.effective_start_date
,p_element_entry_id => l_invalid_entries.element_entry_id);
end update_absence;
procedure delete_absence
(p_absence_attendance_id in number
) is
CURSOR csr_assignments IS
SELECT asg.assignment_id, asg.effective_start_date
FROM per_all_assignments_f asg,
per_absence_attendances paa
WHERE paa.absence_attendance_id = p_absence_attendance_id
and asg.person_id = paa.person_id
AND nvl(paa.date_start,asg.effective_end_date) <= asg.effective_end_date
AND nvl(paa.date_end,asg.effective_start_date) >= asg.effective_start_date;
select pee.element_entry_id element_entry_id
,pet.processing_type processing_type
,pee.effective_start_date effective_start_date
,pee.effective_end_date effective_end_date
from per_absence_attendances abs
,per_all_assignments_f asg
,pay_element_types_f pet
,pay_element_links_f pel
,pay_element_entries_f pee
,pay_element_entry_values_f peev
,pay_input_values_f piv
where abs.absence_attendance_id = p_absence_attendance_id
and abs.person_id = asg.person_id
and asg.assignment_id = p_assignment_id
and nvl(abs.date_start,asg.effective_end_date) <= asg.effective_end_date
and nvl(abs.date_end,asg.effective_start_date) >= asg.effective_start_date
and pet.element_type_id = p_element_type_id
and nvl(abs.date_start,pet.effective_end_date) <= pet.effective_end_date
and nvl(abs.date_end,pet.effective_start_date) >= pet.effective_start_date
and pet.element_type_id = pel.element_type_id
and pel.element_link_id = pee.element_link_id
and pee.assignment_id = asg.assignment_id
and pee.creator_type = 'F'
and pet.element_type_id = piv.element_type_id
and nvl(abs.date_start,piv.effective_end_date) <= piv.effective_end_date
and nvl(abs.date_end,piv.effective_start_date) >= piv.effective_start_date
and piv.name = 'CREATOR_ID'
and peev.element_entry_id = pee.element_entry_id
and peev.input_value_id=piv.input_value_id
and peev.screen_entry_value = to_char(abs.absence_attendance_id);
/* select pee.element_entry_id element_entry_id
,pet.processing_type processing_type
,pee.effective_start_date effective_start_date
,pee.effective_end_date effective_end_date
from per_absence_attendances abs
,per_all_assignments_f asg
,pay_element_types_f pet
,pay_element_links_f pel
,pay_element_entries_f pee
where abs.absence_attendance_id = p_absence_attendance_id
and abs.person_id = asg.person_id
and asg.assignment_id = p_assignment_id
and nvl(abs.date_start,asg.effective_end_date) <= asg.effective_end_date
and nvl(abs.date_end,asg.effective_start_date) >= asg.effective_start_date
and pet.element_type_id = p_element_type_id
and nvl(abs.date_start,pet.effective_start_date) >= pet.effective_start_date
and nvl(abs.date_end,pet.effective_end_date) <= pet.effective_end_date
and pet.element_type_id = pel.element_type_id
and pel.element_link_id = pee.element_link_id
and pee.assignment_id = asg.assignment_id
and pee.creator_id = abs.absence_attendance_id
and pee.creator_type = 'F';
delete_absence_element
(p_dt_delete_mode => 'ZAP'
,p_session_date => rec_absence_element.effective_start_date
,p_element_entry_id => rec_absence_element.element_entry_id);
END delete_absence;