The following lines contain the word 'select', 'insert', 'update' or 'delete':
select usr.employee_id,
per.first_name,
per.last_name,
per.middle_names
from per_people_f per,
fnd_user usr
where upper(usr.user_name) = upper(p_1_user_name_acted_on)
and per.person_id = usr.employee_id
and p_date_from
between per.effective_start_date
and per.effective_end_date;
SELECT usr.employee_id,
per.first_name,
per.last_name,
per.middle_names
FROM per_people_f per,
fnd_user usr
WHERE usr.user_name = UPPER(p_1_user_name_acted_on)
AND per.person_id = usr.employee_id
AND p_date_from
BETWEEN per.effective_start_date
AND per.effective_end_date;
select rlm.seq_number,
rlm.groupbox_id,
rlm.user_name
from ghr_routing_list_members rlm
where rlm.routing_list_id = p_2_routing_list_id
order by rlm.seq_number asc;
select 1
from ghr_pd_routing_history pdh
where pdh.position_description_id = l_position_description_id;
select gbx.name
from ghr_groupboxes gbx
where gbx.groupbox_id = l_2_groupbox_id;
select ghr_pd_wf_item_key_s.nextval
from dual;
PROCEDURE update_pdi
(
p_validate IN BOOLEAN default false,
p_position_description_id IN number,
p_routing_grp_id IN number default hr_api.g_number,
p_date_from IN date,
p_date_to IN date default hr_api.g_date,
p_opm_cert_num IN ghr_position_descriptions.opm_cert_num%TYPE default hr_api.g_varchar2,
p_flsa IN ghr_position_descriptions.flsa%TYPE default hr_api.g_varchar2,
p_financial_statement IN ghr_position_descriptions.financial_statement%TYPE default hr_api.g_varchar2,
p_subject_to_ia_action IN ghr_position_descriptions.subject_to_ia_action%TYPE default hr_api.g_varchar2,
p_position_status IN ghr_position_descriptions.position_status%TYPE default hr_api.g_number,
p_position_is IN ghr_position_descriptions.position_is%TYPE default hr_api.g_varchar2,
p_position_sensitivity IN ghr_position_descriptions.position_sensitivity%TYPE default hr_api.g_varchar2,
p_competitive_level IN ghr_position_descriptions.competitive_level%TYPE default hr_api.g_varchar2,
p_pd_remarks IN ghr_position_descriptions.pd_remarks%TYPE default hr_api.g_varchar2,
p_position_class_std IN ghr_position_descriptions.position_class_std%TYPE default hr_api.g_varchar2,
p_category IN ghr_position_descriptions.category%TYPE default hr_api.g_varchar2,
p_career_ladder IN ghr_position_descriptions.career_ladder%TYPE default hr_api.g_varchar2,
p_supervisor_name in varchar2 default hr_api.g_varchar2,
p_supervisor_title in varchar2 default hr_api.g_varchar2,
p_supervisor_date in date default hr_api.g_date,
p_manager_name in varchar2 default hr_api.g_varchar2,
p_manager_title in varchar2 default hr_api.g_varchar2,
p_manager_date in date default hr_api.g_date,
p_classifier_name in varchar2 default hr_api.g_varchar2,
p_classifier_title in varchar2 default hr_api.g_varchar2,
p_classifier_date in date default hr_api.g_date,
p_attribute_category in varchar2 default hr_api.g_varchar2,
p_attribute1 in varchar2 default hr_api.g_varchar2,
p_attribute2 in varchar2 default hr_api.g_varchar2,
p_attribute3 in varchar2 default hr_api.g_varchar2,
p_attribute4 in varchar2 default hr_api.g_varchar2,
p_attribute5 in varchar2 default hr_api.g_varchar2,
p_attribute6 in varchar2 default hr_api.g_varchar2,
p_attribute7 in varchar2 default hr_api.g_varchar2,
p_attribute8 in varchar2 default hr_api.g_varchar2,
p_attribute9 in varchar2 default hr_api.g_varchar2,
p_attribute10 in varchar2 default hr_api.g_varchar2,
p_attribute11 in varchar2 default hr_api.g_varchar2,
p_attribute12 in varchar2 default hr_api.g_varchar2,
p_attribute13 in varchar2 default hr_api.g_varchar2,
p_attribute14 in varchar2 default hr_api.g_varchar2,
p_attribute15 in varchar2 default hr_api.g_varchar2,
p_attribute16 in varchar2 default hr_api.g_varchar2,
p_attribute17 in varchar2 default hr_api.g_varchar2,
p_attribute18 in varchar2 default hr_api.g_varchar2,
p_attribute19 in varchar2 default hr_api.g_varchar2,
p_attribute20 in varchar2 default hr_api.g_varchar2,
p_business_group_id in number default hr_api.g_number,
p_u_approved_flag in varchar2 default hr_api.g_varchar2,
p_u_user_name_acted_on in varchar2 default hr_api.g_varchar2,
p_u_action_taken in varchar2 default null,
p_i_user_name_routed_to in varchar2 default null,
p_i_groupbox_id in number default null,
p_i_routing_list_id in number default null,
p_i_routing_seq_number in number default null,
p_u_pdh_object_version_number in out nocopy number,
p_i_pd_routing_history_id out nocopy number,
p_i_pdh_object_version_number out nocopy number,
p_o_pd_routing_history_id out nocopy number,
p_o_pdh_object_version_number out nocopy number,
p_pdi_object_version_number in out nocopy number)
IS
l_pdi_object_version_number number;
l_proc varchar2(72) := g_package||'Update_pdi';
SELECT count(*) cnt
FROM ghr_pd_routing_history pdh
WHERE pdh.position_description_id = p_position_description_id;
SELECT pdh.pd_routing_history_id,
pdh.object_version_number,
pdh.action_taken,
pdh.item_key
FROM ghr_pd_routing_history pdh
WHERE pdh.position_description_id = p_position_description_id
ORDER BY pdh.pd_routing_history_id desc;
SELECT pdi.routing_group_id
FROM ghr_position_descriptions pdi
WHERE pdi.position_description_id = p_position_description_id;
SELECT usr.employee_id,
per.first_name,
per.last_name,
per.middle_names
FROM fnd_user usr,
per_people_f per
WHERE upper(p_u_user_name_acted_on) = upper(usr.user_name)
AND per.person_id = usr.employee_id
AND p_date_from
BETWEEN per.effective_start_date
AND per.effective_end_date; */
SELECT usr.employee_id,
per.first_name,
per.last_name,
per.middle_names
FROM fnd_user usr,
per_people_f per
WHERE usr.user_name = UPPER(p_u_user_name_acted_on)
AND per.person_id = usr.employee_id
AND p_date_from
BETWEEN per.effective_start_date
AND per.effective_end_date;
select pdh.routing_seq_number
from ghr_pd_routing_history pdh
where pdh.position_description_id = p_position_description_id
and pdh.routing_list_id = p_i_routing_list_id
order by pdh.pd_routing_history_id desc;
select rlm.seq_number,
rlm.groupbox_id,
rlm.user_name
from ghr_routing_list_members rlm
where rlm.routing_list_id = p_i_routing_list_id
and rlm.seq_number > l_cur_seq_numb
order by rlm.seq_number asc;
select action_taken
from ghr_pd_routing_history pdh
where pdh.position_description_id = p_position_description_id
order by pd_routing_history_id;
select action_taken
from ghr_pd_routing_history pdh
where pdh.position_description_id = p_position_description_id
and action_taken = 'INITIATED';
select gbx.name
from ghr_groupboxes gbx
where gbx.groupbox_id = l_next_groupbox_id;
select ghr_pd_wf_item_key_s.nextval
from dual;
SAVEPOINT update_pdi;
ghr_pdi_bk2.update_pdi_b (
p_position_description_id => p_position_description_id,
p_routing_grp_id => p_routing_grp_id,
p_date_from => p_date_from,
p_date_to => p_date_to,
p_opm_cert_num => p_opm_cert_num,
p_flsa => p_flsa,
p_financial_statement => p_financial_statement,
p_subject_to_ia_action => p_subject_to_ia_action,
p_position_status => p_position_status,
p_position_is => p_position_is,
p_position_sensitivity => p_position_sensitivity,
p_competitive_level => p_competitive_level,
p_pd_remarks => p_pd_remarks,
p_position_class_std => p_position_class_std,
p_category => p_category,
p_career_ladder => p_career_ladder,
p_supervisor_name => p_supervisor_name,
p_supervisor_title => p_supervisor_title,
p_supervisor_date => p_supervisor_date,
p_manager_name => p_manager_name,
p_manager_title => p_manager_title,
p_manager_date => p_manager_date,
p_classifier_name => p_classifier_name,
p_classifier_title => p_classifier_title,
p_classifier_date => p_classifier_date,
p_attribute_category => p_attribute_category,
p_attribute1 => p_attribute1,
p_attribute2 => p_attribute2,
p_attribute3 => p_attribute3,
p_attribute4 => p_attribute4,
p_attribute5 => p_attribute5,
p_attribute6 => p_attribute6,
p_attribute7 => p_attribute7,
p_attribute8 => p_attribute8,
p_attribute9 => p_attribute9,
p_attribute10 => p_attribute10,
p_attribute11 => p_attribute11,
p_attribute12 => p_attribute12,
p_attribute13 => p_attribute13,
p_attribute14 => p_attribute14,
p_attribute15 => p_attribute15,
p_attribute16 => p_attribute16,
p_attribute17 => p_attribute17,
p_attribute18 => p_attribute18,
p_attribute19 => p_attribute19,
p_attribute20 => p_attribute20,
p_business_group_id => p_business_group_id,
p_u_approved_flag => p_u_approved_flag,
p_u_user_name_acted_on => p_u_user_name_acted_on,
p_u_action_taken => p_u_action_taken,
p_i_user_name_routed_to => p_i_user_name_routed_to,
p_i_groupbox_id => p_i_groupbox_id,
p_i_routing_list_id => p_i_routing_list_id,
p_i_routing_seq_number => p_i_routing_seq_number,
p_u_pdh_object_version_number => p_u_pdh_object_version_number,
p_i_pd_routing_history_id => p_i_pd_routing_history_id,
p_i_pdh_object_version_number => p_i_pdh_object_version_number,
p_pdi_object_version_number => p_pdi_object_version_number
);
(p_module_name => 'update_pdi',
p_hook_type => 'BP'
);
ghr_pdi_bk2.update_pdi_a (
p_position_description_id => p_position_description_id,
p_routing_grp_id => p_routing_grp_id,
p_date_from => p_date_from,
p_date_to => p_date_to,
p_opm_cert_num => p_opm_cert_num,
p_flsa => p_flsa,
p_financial_statement => p_financial_statement,
p_subject_to_ia_action => p_subject_to_ia_action,
p_position_status => p_position_status,
p_position_is => p_position_is,
p_position_sensitivity => p_position_sensitivity,
p_competitive_level => p_competitive_level,
p_pd_remarks => p_pd_remarks,
p_position_class_std => p_position_class_std,
p_category => p_category,
p_career_ladder => p_career_ladder,
p_supervisor_name => p_supervisor_name,
p_supervisor_title => p_supervisor_title,
p_supervisor_date => p_supervisor_date,
p_manager_name => p_manager_name,
p_manager_title => p_manager_title,
p_manager_date => p_manager_date,
p_classifier_name => p_classifier_name,
p_classifier_title => p_classifier_title,
p_classifier_date => p_classifier_date,
p_attribute_category => p_attribute_category,
p_attribute1 => p_attribute1,
p_attribute2 => p_attribute2,
p_attribute3 => p_attribute3,
p_attribute4 => p_attribute4,
p_attribute5 => p_attribute5,
p_attribute6 => p_attribute6,
p_attribute7 => p_attribute7,
p_attribute8 => p_attribute8,
p_attribute9 => p_attribute9,
p_attribute10 => p_attribute10,
p_attribute11 => p_attribute11,
p_attribute12 => p_attribute12,
p_attribute13 => p_attribute13,
p_attribute14 => p_attribute14,
p_attribute15 => p_attribute15,
p_attribute16 => p_attribute16,
p_attribute17 => p_attribute17,
p_attribute18 => p_attribute18,
p_attribute19 => p_attribute19,
p_attribute20 => p_attribute20,
p_business_group_id => p_business_group_id,
p_u_approved_flag => p_u_approved_flag,
p_u_user_name_acted_on => p_u_user_name_acted_on,
p_u_action_taken => p_u_action_taken,
p_i_user_name_routed_to => p_i_user_name_routed_to,
p_i_groupbox_id => p_i_groupbox_id,
p_i_routing_list_id => p_i_routing_list_id,
p_i_routing_seq_number => p_i_routing_seq_number,
p_u_pdh_object_version_number => p_u_pdh_object_version_number,
p_i_pd_routing_history_id => p_i_pd_routing_history_id,
p_i_pdh_object_version_number => p_i_pdh_object_version_number,
p_pdi_object_version_number => l_pdi_object_version_number
);
(p_module_name => 'update_pdi',
p_hook_type => 'AP'
);
ROLLBACK to update_pdi;
rollback to update_pdi;
END update_pdi;
select 1
from ghr_pd_routing_history pdh
where pdh.position_description_id = p_position_description_id;
select gbx.name
from ghr_groupboxes gbx
where gbx.groupbox_id = l_groupbox_id_1;
select ghr_pd_wf_item_key_s.nextval
from dual;
select count(*) cnt
from ghr_pd_routing_history pdh
where pdh.position_description_id = p_position_description_id;
select pdh.pd_routing_history_id,
pdh.action_taken,
pdh.item_key,
pdh.groupbox_id,
pdh.user_name
from ghr_pd_routing_history pdh
where pdh.position_description_id = p_position_description_id
order by pdh.pd_routing_history_id desc;
select pdi.routing_group_id
from ghr_position_descriptions pdi
where pdi.position_description_id = p_position_description_id;
select 'X'
from ghr_pd_routing_history pdh
where pdh.position_description_id = p_position_description_id
and action_taken in ( 'INITIATED','AUTHORIZED','REQUESTED','CLASSIFIED');