[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select *
from ghr_pa_history hist_1
where ( altered_pa_request_id is null OR
not exists (select 'exists'
from ghr_pa_history hist_2
where hist_1.altered_pa_request_id = hist_2.pa_request_id
and hist_1.information1 = hist_2.information1
and hist_1.nature_of_action_id = hist_2.nature_of_action_id
and hist_1.table_name = hist_2.table_name)
)
and information1 = cp_information1
and effective_date = cp_max_date_effective
and table_name = cp_table_name
order by pa_history_id desc;
hr_utility.set_location('Selected Root Request Id ' || l_root_pa_request_id || l_proc, 60);
select *
from ghr_pa_history hist_1
where
-- root request or broken chain)
( altered_pa_request_id is null OR
not exists (select 'exists'
from ghr_pa_history hist_2
where hist_1.altered_pa_request_id = hist_2.pa_request_id
and hist_1.information1 = hist_2.information1
and hist_1.nature_of_action_id = hist_2.nature_of_action_id
and hist_1.table_name = hist_2.table_name)
)
-- and pa_history_id of the root of the record must be <= cp_root_pa_history_id
and (cp_root_pa_history_id >=
(select min(pa_history_id)
from ghr_pa_history
where pa_request_id =
(select min(pa_request_id)
from ghr_pa_requests
connect by pa_request_id = prior altered_pa_request_id
start with pa_request_id = (select pa_request_id
from ghr_pa_history
where pa_history_id = hist_1.pa_history_id))
and nature_of_action_id = hist_1.nature_of_action_id)
-- or fetch record created by core form change with lower history_id
OR
(cp_root_pa_history_id >= hist_1.pa_history_id and
hist_1.pa_request_id is null))
-- Bug #6356058 modified above to pick the latest core form change
-- with history id greater than the root.
/*(hist_1.pa_history_id >= cp_root_pa_history_id and
hist_1.pa_request_id is null))*/
and information1 = cp_information1
and effective_date = cp_date_effective
and pa_history_id <> cp_pa_history_id
and table_name = cp_table_name
order by pa_history_id desc;
select min(pa_history_id),
min(pa_request_id),
min(nature_of_action_id)
from ghr_pa_history
where pa_request_id =
(select min(pa_request_id)
from ghr_pa_requests
connect by pa_request_id = prior altered_pa_request_id
start with pa_request_id = cp_pa_req_id)
and nature_of_action_id = cp_noa_id;
select
pa_request_id,
nature_of_action_id
from ghr_pa_history
where pa_history_id = cp_pa_history_id;
hr_utility.set_location('Selected Root Hist Id ' || l_root_pa_history_id || l_proc, 60);
hr_utility.set_location('Selected Root Request Id ' || l_root_pa_request_id || l_proc, 60);
hr_utility.set_location('Selected Hist Id ' || p_hist_data.pa_history_id || l_proc, 61);
hr_utility.set_location('Selected PaRequest Id ' || p_hist_data.pa_request_id || l_proc, 62);
select
min(pa_history_id)
from ghr_pa_history
where
pa_request_id = cp_pa_request_id
and nature_of_action_id = nvl(cp_nature_of_action_id, nature_of_action_id);
select
min(pa_request_id)
from ghr_pa_requests
connect by pa_request_id = prior altered_pa_request_id
start with pa_request_id = cp_pa_request_id;
select pa_history_id
from ghr_pa_history
where effective_date = p_hist_data.effective_date
and pa_history_id > p_save_history_id
and information1 = p_hist_data.information1
and table_name = p_hist_data.table_name
and pa_request_id in (select pa_request_id
from ghr_pa_requests
where pa_notification_id is not null
start with pa_request_id = p_hist_data.pa_request_id
connect by prior pa_request_id = altered_pa_request_id);
select *
from ghr_pa_history hist_1
where
-- root request or broken chain)
( altered_pa_request_id is null OR
not exists (select 'exists'
from ghr_pa_history hist_2
where hist_1.altered_pa_request_id = hist_2.pa_request_id
and hist_1.information1 = hist_2.information1
and hist_1.nature_of_action_id = hist_2.nature_of_action_id)
)
-- and pa_history_id of the root of the record must be <= cp_root_pa_history_id
and (cp_root_pa_history_id >=
(select min(pa_history_id)
from ghr_pa_history
where pa_request_id =
(select min(pa_request_id)
from ghr_pa_requests
connect by pa_request_id = prior altered_pa_request_id
start with pa_request_id = (select pa_request_id
from ghr_pa_history
where pa_history_id = hist_1.pa_history_id))
and nature_of_action_id = hist_1.nature_of_action_id)
-- or fetch record created by core form change with lower history_id
OR
(cp_root_pa_history_id >= hist_1.pa_history_id and
hist_1.pa_request_id is null))
and information1 = cp_information1
and effective_date = cp_date_effective
and pa_history_id <> cp_pa_history_id
and table_name = cp_table_name
order by pa_history_id desc;
select max(effective_date)
from ghr_pa_history hist_1
where
-- root request or broken chain)
( altered_pa_request_id is null OR
not exists (select 'exists'
from ghr_pa_history hist_2
where hist_1.altered_pa_request_id = hist_2.pa_request_id
and hist_1.information1 = hist_2.information1
and hist_1.nature_of_action_id = hist_2.nature_of_action_id
and hist_1.table_name = hist_2.table_name)
)
and information1 = cp_information1
and effective_date < cp_date_effective
and table_name = cp_table_name;
select max(effective_date)
from ghr_pa_history hist_1
where
-- root request or broken chain)
( altered_pa_request_id is null OR
not exists (select 'exists'
from ghr_pa_history hist_2
where hist_1.altered_pa_request_id = hist_2.pa_request_id
and hist_1.information1 = hist_2.information1
and hist_1.nature_of_action_id = hist_2.nature_of_action_id
and hist_1.table_name = hist_2.table_name)
)
and information1 = cp_information1
and effective_date = cp_date_effective
and table_name = cp_table_name
and pa_history_id <> cp_pa_history_id;
select max(effective_date)
from ghr_pa_history hist_1
where ( altered_pa_request_id is null OR
not exists (select 'exists'
from ghr_pa_history hist_2
where hist_1.altered_pa_request_id = hist_2.pa_request_id
and hist_1.information1 = hist_2.information1
and hist_1.nature_of_action_id = hist_2.nature_of_action_id
and hist_1.table_name = hist_2.table_name)
)
and information1 = cp_information1
and effective_date <= cp_date_effective
and table_name = cp_table_name;
if called before update to database updates the record.
if update to database has already applied the changes then it
will return the post-update record. The session variable
pre-update-record will hold the values which can be used for
pre-record values
*/
hr_utility.set_location( l_proc, 20);
select *
from ghr_pa_history
where information1 = cp_information1
and pa_history_id <> nvl(cp_pa_history_id, 0)
and table_name = cp_table_name
and pa_request_id in
(select pa_request_id
from ghr_pa_requests
start with pa_request_id = cp_pa_request_id
connect by prior pa_request_id = altered_pa_request_id)
and nature_of_action_id + 0 = cp_noa_id
order by pa_history_id desc;
select *
from ghr_pa_history
where information1 = cp_information1
and nature_of_action_id = cp_noa_id_corrected
and pa_request_id = cp_altered_pa_request_id
and table_name = cp_table_name;
select pa_history_id
from ghr_pa_history
where pa_request_id = cp_pa_request_id
and nature_of_action_id = cp_noa_id;
select pa_history_id
from ghr_pa_history
where pa_request_id = p_pa_request_id
and nature_of_action_id = p_noa_id;
select *
from per_all_people_f
where person_id = p_person_id
and p_date_effective between effective_start_date and effective_end_date;
select *
from per_all_people_f
where rowid = p_rowid;
which will be the post-update record. So if the procedure was
passed with p_row_id parameter it'll always return the
post-update record.
*/
hr_utility.set_location( l_proc, 10);
if called before update to database updates the record.
if update to database has already applied the changes then it
will return the post-update record. The session variable
pre-update-record will hold the values which can be used for
pre-record values
*/
hr_utility.set_location( l_proc, 20);
select *
from per_assignment_extra_info
where rowid = p_rowid;
select object_version_number
from per_assignment_extra_info
where assignment_extra_info_id = p_assignment_extra_info_id;
select *
from per_position_extra_info
where rowid = p_rowid;
select object_version_number
from per_position_extra_info
where position_extra_info_id = p_position_extra_info_id;
select *
from per_all_assignments_f
where assignment_id = p_assignment_id
and p_date_effective between effective_start_date and effective_end_date;
select *
from per_all_assignments_f
where rowid = p_rowid;
which will be the post-update record. So if the procedure was
passed with p_row_id parameter it'll always return the
post-update record.
*/
hr_utility.set_location( l_proc, 10);
if called before update to database updates the record.
if update to database has already applied the changes then it
will return the post-update record. The session variable
pre-update-record will hold the values which can be used for
pre-record values
*/
hr_utility.set_location( l_proc, 20);
select *
from pay_element_entries_f
where element_entry_id = p_element_entry_id
and p_date_effective between effective_start_date and effective_end_date;
select *
from pay_element_entries_f
where rowid = p_rowid;
which will be the post-update record. So if the procedure was
passed with p_row_id parameter it'll always return the
post-update record.
*/
hr_utility.set_location( l_proc, 10);
if called before update to database updates the record.
if update to database has already applied the changes then it
will return the post-update record. The session variable
pre-update-record will hold the values which can be used for
pre-record values
*/
hr_utility.set_location( l_proc, 20);
select *
from per_people_extra_info
where rowid = p_rowid;
select object_version_number
from per_people_extra_info
where person_extra_info_id = p_person_extra_info_id;
which will be the post-update record. So if the procedure was
passed with p_row_id parameter it'll always return the
post-update record.
*/
hr_utility.set_location( l_proc, 10);
select aei.assignment_extra_info_id
from per_assignment_extra_info aei
where aei.assignment_id = p_assignment_id
and aei.information_type = p_information_type;
select pei.person_extra_info_id
from per_people_extra_info pei
where pei.person_id = p_person_id
and pei.information_type = p_information_type;
select ghr_pos.position_extra_info_id
from per_position_extra_info ghr_pos
where ghr_pos.position_id = p_position_id
and ghr_pos.information_type = p_information_type;
select *
from pay_element_entry_values_f
where element_entry_value_id = p_element_entry_value_id
and p_date_effective between effective_start_date and effective_end_date;
select *
from pay_element_entry_values_f
where rowid = p_rowid;
which will be the post-update record. So if the procedure was
passed with p_row_id parameter it'll always return the
post-update record.
*/
hr_utility.set_location( l_proc, 10);
if called before update to database updates the record.
if update to database has already applied the changes then it
will return the post-update record. The session variable
pre-update-record will hold the values which can be used for
pre-record values
*/
hr_utility.set_location( l_proc, 20);
select eev.element_entry_value_id
from pay_element_types_f elt,
pay_input_values_f ipv,
pay_element_entries_f ele,
pay_element_entry_values_f eev
where trunc(p_date_effective) between elt.effective_start_date
and elt.effective_end_date
and trunc(p_date_effective) between ipv.effective_start_date
and ipv.effective_end_date
and trunc(p_date_effective) between ele.effective_start_date
and ele.effective_end_date
and trunc(p_date_effective) between eev.effective_start_date
and eev.effective_end_date
and elt.element_type_id = ipv.element_type_id
and upper(elt.element_name) = upper(p_element_name)
and ipv.input_value_id = eev.input_value_id
and ele.assignment_id = p_assignment_id
and ele.element_entry_id + 0 = eev.element_entry_id
and upper(ipv.name) = upper(p_input_value_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=p_bg_id);
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 elt.element_type_id,
elt.processing_type
from pay_element_types_f elt
where upper(elt.element_name) = upper(p_element_name)
and p_effective_date
between elt.effective_start_date and elt.effective_end_date
and (elt.business_group_id is null or elt.business_group_id=p_bg_id);
select ele.pa_request_id,
ipv.input_value_id,
eli.element_link_id,
ele.element_entry_id
from
pay_input_values_f ipv,
pay_element_links_f eli,
ghr_element_entries_h_v ele
where
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 ipv.element_type_id = l_ele_type_id
and ipv.element_type_id = eli.element_type_id
and ele.assignment_id = asg_id
and ele.element_link_id = eli.element_link_id
and upper(ipv.name) = upper(input_name)
and ele.nature_of_action_id = l_session.noa_id_correct
and ele.pa_request_id in
(select a.pa_request_id
from ghr_pa_requests a
connect by a.pa_request_id = prior a.altered_pa_request_id
start with a.pa_request_id = l_session.altered_pa_request_id
)
-- and NVL(ipv.business_group_id,0)=NVL(eli.business_group_id,0)
and (ipv.business_group_id is null or ipv.business_group_id=bg_id)
order by 1 desc;
select gev.screen_entry_value
from ghr_element_entry_values_h_v gev
where gev.element_entry_id = l_element_entry_id
and gev.input_value_id = l_input_value_id
and gev.pa_request_id = l_pa_request_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 eev.element_entry_value_id
from pay_input_values_f ipv,
pay_element_entries_f ele,
pay_element_entry_values_f eev
where trunc(p_effective_date) between ipv.effective_start_date
and ipv.effective_end_date
and trunc(p_effective_date) between ele.effective_start_date
and ele.effective_end_date
and trunc(p_effective_date) between eev.effective_start_date
and eev.effective_end_date
and ipv.element_type_id=p_element_type_id
and ipv.input_value_id = eev.input_value_id
and ele.assignment_id = p_assignment_id
and ele.element_entry_id + 0 = eev.element_entry_id
and upper(ipv.name) = upper(p_input_value_name)
and (ipv.business_group_id is NULL or ipv.business_group_id=p_bg_id);
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 per_addresses
where rowid = p_rowid;
which will be the post-update record. So if the procedure was
passed with p_row_id parameter it'll always return the
post-update record.
*/
hr_utility.set_location( l_proc, 10);
select *
from per_person_analyses
where rowid = p_rowid;
which will be the post-update record. So if the procedure was
passed with p_row_id parameter it'll always return the
post-update record.
*/
hr_utility.set_location( l_proc, 10);
,p_program_update_date out nocopy date
,p_poei_attribute_category out nocopy varchar2
,p_poei_attribute1 out nocopy varchar2
,p_poei_attribute2 out nocopy varchar2
,p_poei_attribute3 out nocopy varchar2
,p_poei_attribute4 out nocopy varchar2
,p_poei_attribute5 out nocopy varchar2
,p_poei_attribute6 out nocopy varchar2
,p_poei_attribute7 out nocopy varchar2
,p_poei_attribute8 out nocopy varchar2
,p_poei_attribute9 out nocopy varchar2
,p_poei_attribute10 out nocopy varchar2
,p_poei_attribute11 out nocopy varchar2
,p_poei_attribute12 out nocopy varchar2
,p_poei_attribute13 out nocopy varchar2
,p_poei_attribute14 out nocopy varchar2
,p_poei_attribute15 out nocopy varchar2
,p_poei_attribute16 out nocopy varchar2
,p_poei_attribute17 out nocopy varchar2
,p_poei_attribute18 out nocopy varchar2
,p_poei_attribute19 out nocopy varchar2
,p_poei_attribute20 out nocopy varchar2
,p_poei_information_category out nocopy varchar2
,p_poei_information1 out nocopy varchar2
,p_poei_information2 out nocopy varchar2
,p_poei_information3 out nocopy varchar2
,p_poei_information4 out nocopy varchar2
,p_poei_information5 out nocopy varchar2
,p_poei_information6 out nocopy varchar2
,p_poei_information7 out nocopy varchar2
,p_poei_information8 out nocopy varchar2
,p_poei_information9 out nocopy varchar2
,p_poei_information10 out nocopy varchar2
,p_poei_information11 out nocopy varchar2
,p_poei_information12 out nocopy varchar2
,p_poei_information13 out nocopy varchar2
,p_poei_information14 out nocopy varchar2
,p_poei_information15 out nocopy varchar2
,p_poei_information16 out nocopy varchar2
,p_poei_information17 out nocopy varchar2
,p_poei_information18 out nocopy varchar2
,p_poei_information19 out nocopy varchar2
,p_poei_information20 out nocopy varchar2
,p_poei_information21 out nocopy varchar2
,p_poei_information22 out nocopy varchar2
,p_poei_information23 out nocopy varchar2
,p_poei_information24 out nocopy varchar2
,p_poei_information25 out nocopy varchar2
,p_poei_information26 out nocopy varchar2
,p_poei_information27 out nocopy varchar2
,p_poei_information28 out nocopy varchar2
,p_poei_information29 out nocopy varchar2
,p_poei_information30 out nocopy varchar2
,p_object_version_number out nocopy number
,p_last_update_date out nocopy date
,p_last_updated_by out nocopy number
,p_last_update_login out nocopy number
,p_created_by out nocopy number
,p_creation_date out nocopy date
,p_result_code out nocopy varchar2
)
Is
r_poi per_position_extra_info%rowtype;
p_program_update_date := r_poi.program_update_date;
p_last_update_date := r_poi.last_update_date;
p_last_updated_by := r_poi.last_updated_by;
p_last_update_login := r_poi.last_update_login;
select *
from hr_all_positions_f
where position_id = p_position_id
and p_date_effective between effective_start_date and
effective_end_date;
select *
from hr_all_positions_f
where rowid = p_rowid;
select object_version_number
from hr_all_positions
where position_id = p_position_id
and p_date_effective between effective_start_date and
effective_end_date;
select date_end
from hr_all_positions_f
where position_id = p_position_id
and p_date_effective between effective_start_date and
effective_end_date;
,p_program_update_date out nocopy date
,p_pei_attribute_category out nocopy varchar2
,p_pei_attribute1 out nocopy varchar2
,p_pei_attribute2 out nocopy varchar2
,p_pei_attribute3 out nocopy varchar2
,p_pei_attribute4 out nocopy varchar2
,p_pei_attribute5 out nocopy varchar2
,p_pei_attribute6 out nocopy varchar2
,p_pei_attribute7 out nocopy varchar2
,p_pei_attribute8 out nocopy varchar2
,p_pei_attribute9 out nocopy varchar2
,p_pei_attribute10 out nocopy varchar2
,p_pei_attribute11 out nocopy varchar2
,p_pei_attribute12 out nocopy varchar2
,p_pei_attribute13 out nocopy varchar2
,p_pei_attribute14 out nocopy varchar2
,p_pei_attribute15 out nocopy varchar2
,p_pei_attribute16 out nocopy varchar2
,p_pei_attribute17 out nocopy varchar2
,p_pei_attribute18 out nocopy varchar2
,p_pei_attribute19 out nocopy varchar2
,p_pei_attribute20 out nocopy varchar2
,p_pei_information_category out nocopy varchar2
,p_pei_information1 out nocopy varchar2
,p_pei_information2 out nocopy varchar2
,p_pei_information3 out nocopy varchar2
,p_pei_information4 out nocopy varchar2
,p_pei_information5 out nocopy varchar2
,p_pei_information6 out nocopy varchar2
,p_pei_information7 out nocopy varchar2
,p_pei_information8 out nocopy varchar2
,p_pei_information9 out nocopy varchar2
,p_pei_information10 out nocopy varchar2
,p_pei_information11 out nocopy varchar2
,p_pei_information12 out nocopy varchar2
,p_pei_information13 out nocopy varchar2
,p_pei_information14 out nocopy varchar2
,p_pei_information15 out nocopy varchar2
,p_pei_information16 out nocopy varchar2
,p_pei_information17 out nocopy varchar2
,p_pei_information18 out nocopy varchar2
,p_pei_information19 out nocopy varchar2
,p_pei_information20 out nocopy varchar2
,p_pei_information21 out nocopy varchar2
,p_pei_information22 out nocopy varchar2
,p_pei_information23 out nocopy varchar2
,p_pei_information24 out nocopy varchar2
,p_pei_information25 out nocopy varchar2
,p_pei_information26 out nocopy varchar2
,p_pei_information27 out nocopy varchar2
,p_pei_information28 out nocopy varchar2
,p_pei_information29 out nocopy varchar2
,p_pei_information30 out nocopy varchar2
,p_object_version_number out nocopy number
,p_last_update_date out nocopy date
,p_last_updated_by out nocopy number
,p_last_update_login out nocopy number
,p_created_by out nocopy number
,p_creation_date out nocopy date
,p_result_code out nocopy varchar2
)
Is
r_pei per_people_extra_info%rowtype;
p_program_update_date := r_pei.program_update_date;
p_last_update_date := r_pei.last_update_date;
p_last_updated_by := r_pei.last_updated_by;
p_last_update_login := r_pei.last_update_login;
,p_program_update_date out nocopy date
,p_aei_attribute_category out nocopy varchar2
,p_aei_attribute1 out nocopy varchar2
,p_aei_attribute2 out nocopy varchar2
,p_aei_attribute3 out nocopy varchar2
,p_aei_attribute4 out nocopy varchar2
,p_aei_attribute5 out nocopy varchar2
,p_aei_attribute6 out nocopy varchar2
,p_aei_attribute7 out nocopy varchar2
,p_aei_attribute8 out nocopy varchar2
,p_aei_attribute9 out nocopy varchar2
,p_aei_attribute10 out nocopy varchar2
,p_aei_attribute11 out nocopy varchar2
,p_aei_attribute12 out nocopy varchar2
,p_aei_attribute13 out nocopy varchar2
,p_aei_attribute14 out nocopy varchar2
,p_aei_attribute15 out nocopy varchar2
,p_aei_attribute16 out nocopy varchar2
,p_aei_attribute17 out nocopy varchar2
,p_aei_attribute18 out nocopy varchar2
,p_aei_attribute19 out nocopy varchar2
,p_aei_attribute20 out nocopy varchar2
,p_aei_information_category out nocopy varchar2
,p_aei_information1 out nocopy varchar2
,p_aei_information2 out nocopy varchar2
,p_aei_information3 out nocopy varchar2
,p_aei_information4 out nocopy varchar2
,p_aei_information5 out nocopy varchar2
,p_aei_information6 out nocopy varchar2
,p_aei_information7 out nocopy varchar2
,p_aei_information8 out nocopy varchar2
,p_aei_information9 out nocopy varchar2
,p_aei_information10 out nocopy varchar2
,p_aei_information11 out nocopy varchar2
,p_aei_information12 out nocopy varchar2
,p_aei_information13 out nocopy varchar2
,p_aei_information14 out nocopy varchar2
,p_aei_information15 out nocopy varchar2
,p_aei_information16 out nocopy varchar2
,p_aei_information17 out nocopy varchar2
,p_aei_information18 out nocopy varchar2
,p_aei_information19 out nocopy varchar2
,p_aei_information20 out nocopy varchar2
,p_aei_information21 out nocopy varchar2
,p_aei_information22 out nocopy varchar2
,p_aei_information23 out nocopy varchar2
,p_aei_information24 out nocopy varchar2
,p_aei_information25 out nocopy varchar2
,p_aei_information26 out nocopy varchar2
,p_aei_information27 out nocopy varchar2
,p_aei_information28 out nocopy varchar2
,p_aei_information29 out nocopy varchar2
,p_aei_information30 out nocopy varchar2
,p_object_version_number out nocopy number
,p_last_update_date out nocopy date
,p_last_updated_by out nocopy number
,p_last_update_login out nocopy number
,p_created_by out nocopy number
,p_creation_date out nocopy date
,p_result_code out nocopy varchar2
)
Is
r_aei per_assignment_extra_info%rowtype;
p_program_update_date := r_aei.program_update_date;
p_last_update_date := r_aei.last_update_date;
p_last_updated_by := r_aei.last_updated_by;
p_last_update_login := r_aei.last_update_login;
select flx.id_flex_num
from fnd_id_flex_structures_tl flx
where flx.id_flex_code = 'PEA' --
and flx.application_id = 800 --
and flx.id_flex_structure_name = p_structure_name
and flx.language = 'US';
select TO_NUMBER(gan.INFORMATION1) person_analysis_id,
FND_DATE.CANONICAL_TO_DATE(gan.information9) date_from,
FND_DATE.CANONICAL_TO_DATE(gan.information10) date_to ,
pa_request_id,
TO_NUMBER(gan.INFORMATION6) analysis_Criteria_id
from ghr_pa_history gan , -- ghr_person_analyses_h_v gan,
per_person_analyses per
where gan.table_name = 'PER_PERSON_ANALYSES'
and TO_NUMBER(gan.information7) = p_person_id -- information7 holds person_id
and per.person_id = TO_NUMBER(gan.information7)
and TO_NUMBER(gan.INFORMATION11)= l_id_flex_num -- information11 holds id_flex_Number
and per.id_flex_num = TO_NUMBER(gan.INFORMATION11)
and to_char(per.person_analysis_id) = gan.INFORMATION1 -- information1 holds person_analysis_id (3206581)
and p_effective_date
between nvl(fnd_date.canonical_to_date(gan.information9),p_effective_date)
and nvl(fnd_date.canonical_to_date(gan.information10),p_effective_date) -- information9,information10 holds date_from,date_to
order by 2,1 desc, pa_history_id asc;
select object_version_number
from per_person_analyses pan
where person_analysis_id = l_person_analysis_id;
select pea.analysis_criteria_id,
pea.segment1,
pea.segment2,
pea.segment3,
pea.segment4,
pea.segment5,
pea.segment6,
pea.segment7,
pea.segment8,
pea.segment9,
pea.segment10,
pea.segment11,
pea.segment12,
pea.segment13,
pea.segment14,
pea.segment15,
pea.segment16,
pea.segment17,
pea.segment18,
pea.segment19,
pea.segment20,
pea.segment21,
pea.segment22,
pea.segment23,
pea.segment24,
pea.segment25,
pea.segment26,
pea.segment27,
pea.segment28,
pea.segment29,
pea.segment30
from per_analysis_Criteria pea
where pea.analysis_Criteria_id = l_analysis_criteria_id
and p_effective_date
between nvl(pea.start_date_active,p_effective_date)
and nvl(pea.end_date_active,p_effective_date);
select min(pa_history_id)
from ghr_pa_history
where pa_request_id =
(select min(pa_request_id)
from ghr_pa_requests
connect by pa_request_id = prior altered_pa_request_id
start with pa_request_id = cp_pa_req_id)
and nature_of_action_id = cp_noa_id;
select aei.assignment_extra_info_id
from per_assignment_extra_info aei
where aei.assignment_id = p_assignment_id
and aei.information_type = p_information_type;
select min(pa_history_id)
from ghr_pa_history
where pa_request_id =
(select min(pa_request_id)
from ghr_pa_requests
connect by pa_request_id = prior altered_pa_request_id
start with pa_request_id = cp_pa_req_id)
and nature_of_action_id = cp_noa_id;