[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Select ppf.full_name
from per_people_f ppf
where ppf.person_id =
(select employee_id
from fnd_user
where user_name = l_user_name
)
and l_effective_date
between ppf.effective_start_date and ppf.effective_end_date;
update_sf52_action_taken(p_pa_request_id => itemkey,
p_routing_group_id => l_routing_group_id,
p_groupbox_id => l_groupbox_id,
p_action_taken => 'NOT_ROUTED',
p_gbx_user_id => l_gbx_user_id);
select gbx.groupbox_id, gbx.routing_group_id, gbx.name, rgp.name , rgp.description
from ghr_groupboxes gbx, ghr_routing_groups rgp
where gbx.name = l_groupbox_name
and gbx.routing_group_id = rgp.routing_group_id;
select substr(usr.user_name,1,30) user_name
from per_assignments_f paf
,per_people_f ppf
,fnd_user usr
where paf.person_id = l_in_person_id
and paf.primary_flag = 'Y'
and p_effective_date
between paf.effective_start_date
and paf.effective_end_date
and ppf.person_id = paf.supervisor_id
and ppf.current_employee_flag = 'Y'
and p_effective_date
between ppf.effective_start_date
and ppf.effective_end_date
and ppf.person_id = usr.employee_id;
SELECT gbx.name ,poi.person_id
from ghr_groupboxes gbx, ghr_pois poi
WHERE gbx.groupbox_id (+) = nvl(poi.groupbox_id,-9999999)
AND poi.personnel_office_id = nvl(l_personnel_office_id,-99999999);
select person_id, effective_date, from_position_id
from ghr_pa_requests par
where par.pa_request_id = l_pa_request_id;
select object_version_number
from ghr_pa_requests
where pa_request_id = itemkey;
select 'X'
from wf_roles
where name = p_user_name;
select fcl.meaning
from hr_lookups fcl
where fcl.lookup_type = l_type
and fcl.lookup_code = l_code
and fcl.enabled_flag = 'Y'
and nvl(p_effective_date,trunc(sysdate))
between nvl(fcl.start_date_active,nvl(p_effective_date,trunc(sysdate)))
and nvl(fcl.end_date_active, nvl(p_effective_date,trunc(sysdate)));
select from_pay_plan,
from_grade_or_level,
from_step_or_rate,
to_step_or_rate,
to_total_salary
from ghr_pa_requests
where pa_request_id = p_request_id;
select rei_information3,
rei_information9
from ghr_pa_request_extra_info
where pa_request_id = p_request_id
and information_type = 'GHR_US_PAR_PERF_APPRAISAL';
SELECT rgp.name, rgp.description
FROM ghr_routing_groups rgp, ghr_pa_requests par
WHERE par.pa_request_id = p_request_id
and par.routing_group_id = rgp.routing_group_id;
PROCEDURE UpdateRoutingHistory( itemtype in varchar2,
itemkey in varchar2,
actid in number,
funcmode in varchar2,
result in out nocopy varchar2) is
Begin
NULL;
End UpdateRoutingHistory;
PROCEDURE CallUpdateToHR( itemtype in varchar2,
itemkey in varchar2,
actid in number,
funcmode in varchar2,
result in out nocopy varchar2) is
-- NOCOPY Changes
l_result VARCHAR2(250);
--Submit to Update to HR
update_sf52_action_taken(p_pa_request_id => itemkey,
p_routing_group_id => '',
p_groupbox_id => '',
p_action_taken => 'UPDATE_HR',
p_gbx_user_id => '');
avalue => 'Update to HR failed. ' || substr(sqlerrm,1,1000) );
end CallUpdateToHR;
procedure update_sf52_action_taken(p_pa_request_id in ghr_pa_requests.pa_request_id%TYPE
,p_routing_group_id in ghr_pa_requests.routing_group_id%type
,p_groupbox_id in ghr_pa_routing_history.groupbox_id%type
,p_action_taken in ghr_pa_routing_history.action_taken%TYPE
,p_gbx_user_id in ghr_pois.person_id%TYPE)
is
--
Cursor c_fnd_sessions is
Select 1
From fnd_sessions
where session_id = userenv('sessionid');
select pa_routing_history_id, object_version_number
from ghr_pa_routing_history
where pa_request_id = p_pa_request_id;
select
noa_family_code,
routing_group_id,
object_version_number,
proposed_effective_asap_flag,
academic_discipline,
additional_info_person_id,
additional_info_tel_number,
altered_pa_request_id,
annuitant_indicator,
annuitant_indicator_desc,
appropriation_code1,
appropriation_code2,
authorized_by_person_id,
authorized_by_title,
award_amount,
award_uom,
bargaining_unit_status,
citizenship,
concurrence_date,
custom_pay_calc_flag,
duty_station_code,
duty_station_desc,
duty_station_id,
duty_station_location_id,
education_level,
effective_date,
employee_assignment_id,
employee_date_of_birth,
employee_first_name,
employee_last_name,
employee_middle_names,
employee_national_identifier,
fegli,
fegli_desc,
first_action_la_code1,
first_action_la_code2,
first_action_la_desc1,
first_action_la_desc2,
first_noa_cancel_or_correct,
first_noa_code,
first_noa_desc,
first_noa_id,
first_noa_pa_request_id,
flsa_category,
forwarding_address_line1,
forwarding_address_line2,
forwarding_address_line3,
forwarding_country,
forwarding_postal_code,
forwarding_region_2,
forwarding_town_or_city,
from_adj_basic_pay,
from_basic_pay,
from_grade_or_level,
from_locality_adj,
from_occ_code,
from_other_pay_amount,
from_pay_basis,
from_pay_plan,
-- FWFA Changes Bug#4444609
input_pay_rate_determinant,
from_pay_table_identifier,
-- FWFA Changes
from_position_id,
from_position_org_line1,
from_position_org_line2,
from_position_org_line3,
from_position_org_line4,
from_position_org_line5,
from_position_org_line6,
from_position_number,
from_position_seq_no,
from_position_title,
from_step_or_rate,
from_total_salary,
functional_class,
notepad,
part_time_hours,
pay_rate_determinant,
person_id,
position_occupied,
proposed_effective_date,
requested_by_person_id,
requested_by_title,
requested_date,
requesting_office_remarks_desc,
requesting_office_remarks_flag,
request_number,
resign_and_retire_reason_desc,
retirement_plan,
retirement_plan_desc,
second_action_la_code1,
second_action_la_code2,
second_action_la_desc1,
second_action_la_desc2,
second_noa_cancel_or_correct,
second_noa_code,
second_noa_desc,
second_noa_id,
second_noa_pa_request_id,
service_comp_date,
supervisory_status,
tenure,
to_adj_basic_pay,
to_basic_pay,
to_grade_id,
to_grade_or_level,
to_job_id,
to_locality_adj,
to_occ_code,
to_organization_id,
to_other_pay_amount,
to_au_overtime,
to_auo_premium_pay_indicator,
to_availability_pay,
to_ap_premium_pay_indicator,
to_retention_allowance,
to_supervisory_differential,
to_staffing_differential,
to_pay_basis,
to_pay_plan,
-- FWFA Changes Bug#4444609
to_pay_table_identifier,
-- FWFA Changes
to_position_id,
to_position_org_line1,
to_position_org_line2,
to_position_org_line3,
to_position_org_line4,
to_position_org_line5,
to_position_org_line6,
to_position_number,
to_position_seq_no,
to_position_title,
to_step_or_rate,
to_total_salary,
veterans_preference,
veterans_pref_for_rif,
veterans_status,
work_schedule,
work_schedule_desc,
year_degree_attained,
first_noa_information1,
first_noa_information2,
first_noa_information3,
first_noa_information4,
first_noa_information5,
second_lac1_information1,
second_lac1_information2,
second_lac1_information3,
second_lac1_information4,
second_lac1_information5,
second_lac2_information1,
second_lac2_information2,
second_lac2_information3,
second_lac2_information4,
second_lac2_information5,
second_noa_information1,
second_noa_information2,
second_noa_information3,
second_noa_information4,
second_noa_information5,
first_lac1_information1,
first_lac1_information2,
first_lac1_information3,
first_lac1_information4,
first_lac1_information5,
first_lac2_information1,
first_lac2_information2,
first_lac2_information3,
first_lac2_information4,
first_lac2_information5,
approval_date,
approving_official_full_name,
approving_official_work_title,
to_retention_allow_percentage,
to_supervisory_diff_percentage,
to_staffing_diff_percentage,
award_percentage
-- u_attachment_modified_flag,
-- u_approved_flag,
-- u_user_name_acted_on,
-- u_user_name_acted_on,
-- u_action_taken,
-- i_user_name_routed_to,
-- i_groupbox_id
-- i_routing_list_id,
-- i_routing_seq_number
from ghr_pa_requests
where pa_request_id = p_pa_request_id;
ghr_sf52_api.update_sf52(
p_validate => l_validate,
p_pa_request_id => p_pa_request_id,
p_noa_family_code => l_noa_family_code,
p_routing_group_id => l_routing_group_id,
p_par_object_version_number => l_par_object_version_number,
p_proposed_effective_asap_flag => l_proposed_effective_asap_flag,
p_academic_discipline => l_academic_discipline,
p_additional_info_person_id => l_additional_info_person_id,
p_additional_info_tel_number => l_additional_info_tel_number,
p_altered_pa_request_id => l_altered_pa_request_id,
p_annuitant_indicator => l_annuitant_indicator,
p_annuitant_indicator_desc => l_annuitant_indicator_desc,
p_appropriation_code1 => l_appropriation_code1,
p_appropriation_code2 => l_appropriation_code2,
p_authorized_by_person_id => l_authorized_by_person_id,
p_authorized_by_title => l_authorized_by_title,
p_award_amount => l_award_amount,
p_award_uom => l_award_uom,
p_bargaining_unit_status => l_bargaining_unit_status,
p_citizenship => l_citizenship,
p_concurrence_date => l_concurrence_date,
p_custom_pay_calc_flag => l_custom_pay_calc_flag,
p_duty_station_code => l_duty_station_code,
p_duty_station_desc => l_duty_station_desc,
p_duty_station_id => l_duty_station_id,
p_duty_station_location_id => l_duty_station_location_id,
p_education_level => l_education_level,
p_effective_date => l_effective_date,
p_employee_assignment_id => l_employee_assignment_id,
p_employee_date_of_birth => l_employee_date_of_birth,
p_employee_first_name => l_employee_first_name,
p_employee_last_name => l_employee_last_name,
p_employee_middle_names => l_employee_middle_names,
p_employee_national_identifier => l_employee_national_identifier,
p_fegli => l_fegli,
p_fegli_desc => l_fegli_desc,
p_first_action_la_code1 => l_first_action_la_code1,
p_first_action_la_code2 => l_first_action_la_code2,
p_first_action_la_desc1 => l_first_action_la_desc1,
p_first_action_la_desc2 => l_first_action_la_desc2,
p_first_noa_cancel_or_correct => l_first_noa_cancel_or_correct,
p_first_noa_code => l_first_noa_code,
p_first_noa_desc => l_first_noa_desc,
p_first_noa_id => l_first_noa_id,
p_first_noa_pa_request_id => l_first_noa_pa_request_id,
p_flsa_category => l_flsa_category,
p_forwarding_address_line1 => l_forwarding_address_line1,
p_forwarding_address_line2 => l_forwarding_address_line2,
p_forwarding_address_line3 => l_forwarding_address_line3,
p_forwarding_country => l_forwarding_country,
p_forwarding_postal_code => l_forwarding_postal_code,
p_forwarding_region_2 => l_forwarding_region_2,
p_forwarding_town_or_city => l_forwarding_town_or_city,
p_from_adj_basic_pay => l_from_adj_basic_pay,
p_from_basic_pay => l_from_basic_pay,
p_from_grade_or_level => l_from_grade_or_level,
p_from_locality_adj => l_from_locality_adj,
p_from_occ_code => l_from_occ_code,
p_from_other_pay_amount => l_from_other_pay_amount,
p_from_pay_basis => l_from_pay_basis,
p_from_pay_plan => l_from_pay_plan,
-- FWFA Changes Bug#4444609
p_input_pay_rate_determinant => l_input_pay_rate_determinant,
p_from_pay_table_identifier => l_from_pay_table_identifier,
-- FWFA Changes
p_from_position_id => l_from_position_id,
p_from_position_org_line1 => l_from_position_org_line1,
p_from_position_org_line2 => l_from_position_org_line2,
p_from_position_org_line3 => l_from_position_org_line3,
p_from_position_org_line4 => l_from_position_org_line4,
p_from_position_org_line5 => l_from_position_org_line5,
p_from_position_org_line6 => l_from_position_org_line6,
p_from_position_number => l_from_position_number,
p_from_position_seq_no => l_from_position_seq_no,
p_from_position_title => l_from_position_title,
p_from_step_or_rate => l_from_step_or_rate,
p_from_total_salary => l_from_total_salary,
p_functional_class => l_functional_class,
p_notepad => l_notepad,
p_part_time_hours => l_part_time_hours,
p_pay_rate_determinant => l_pay_rate_determinant,
p_person_id => l_person_id,
p_position_occupied => l_position_occupied,
p_proposed_effective_date => l_proposed_effective_date,
p_requested_by_person_id => l_requested_by_person_id,
p_requested_by_title => l_requested_by_title,
p_requested_date => l_requested_date,
p_requesting_office_remarks_de => l_requesting_office_remarks_de,
p_requesting_office_remarks_fl => l_requesting_office_remarks_fl,
p_request_number => l_request_number,
p_resign_and_retire_reason_des => l_resign_and_retire_reason_des,
p_retirement_plan => l_retirement_plan,
p_retirement_plan_desc => l_retirement_plan_desc,
p_second_action_la_code1 => l_second_action_la_code1,
p_second_action_la_code2 => l_second_action_la_code2,
p_second_action_la_desc1 => l_second_action_la_desc1,
p_second_action_la_desc2 => l_second_action_la_desc2,
p_second_noa_cancel_or_correct => l_second_noa_cancel_or_correct,
p_second_noa_code => l_second_noa_code,
p_second_noa_desc => l_second_noa_desc,
p_second_noa_id => l_second_noa_id,
p_second_noa_pa_request_id => l_second_noa_pa_request_id,
p_service_comp_date => l_service_comp_date,
p_supervisory_status => l_supervisory_status,
p_tenure => l_tenure,
p_to_adj_basic_pay => l_to_adj_basic_pay,
p_to_basic_pay => l_to_basic_pay,
p_to_grade_id => l_to_grade_id,
p_to_grade_or_level => l_to_grade_or_level,
p_to_job_id => l_to_job_id,
p_to_locality_adj => l_to_locality_adj,
p_to_occ_code => l_to_occ_code,
p_to_organization_id => l_to_organization_id,
p_to_other_pay_amount => l_to_other_pay_amount,
p_to_au_overtime => l_to_au_overtime,
p_to_auo_premium_pay_indicator => l_to_auo_premium_pay_indicator,
p_to_availability_pay => l_to_availability_pay,
p_to_ap_premium_pay_indicator => l_to_ap_premium_pay_indicator,
p_to_retention_allowance => l_to_retention_allowance,
p_to_supervisory_differential => l_to_supervisory_differential,
p_to_staffing_differential => l_to_staffing_differential,
p_to_pay_basis => l_to_pay_basis,
p_to_pay_plan => l_to_pay_plan,
-- FWFA Changes Bug#4444609
p_to_pay_table_identifier => l_to_pay_table_identifier,
-- FWFA Changes
p_to_position_id => l_to_position_id,
p_to_position_org_line1 => l_to_position_org_line1,
p_to_position_org_line2 => l_to_position_org_line2,
p_to_position_org_line3 => l_to_position_org_line3,
p_to_position_org_line4 => l_to_position_org_line4,
p_to_position_org_line5 => l_to_position_org_line5,
p_to_position_org_line6 => l_to_position_org_line6,
p_to_position_number => l_to_position_number,
p_to_position_seq_no => l_to_position_seq_no,
p_to_position_title => l_to_position_title,
p_to_step_or_rate => l_to_step_or_rate,
p_to_total_salary => l_to_total_salary,
p_veterans_preference => l_veterans_preference,
p_veterans_pref_for_rif => l_veterans_pref_for_rif,
p_veterans_status => l_veterans_status,
p_work_schedule => l_work_schedule,
p_work_schedule_desc => l_work_schedule_desc,
p_year_degree_attained => l_year_degree_attained,
p_first_noa_information1 => l_first_noa_information1,
p_first_noa_information2 => l_first_noa_information2,
p_first_noa_information3 => l_first_noa_information3,
p_first_noa_information4 => l_first_noa_information4,
p_first_noa_information5 => l_first_noa_information5,
p_second_lac1_information1 => l_second_lac1_information1,
p_second_lac1_information2 => l_second_lac1_information2,
p_second_lac1_information3 => l_second_lac1_information3,
p_second_lac1_information4 => l_second_lac1_information4,
p_second_lac1_information5 => l_second_lac1_information5,
p_second_lac2_information1 => l_second_lac2_information1,
p_second_lac2_information2 => l_second_lac2_information2,
p_second_lac2_information3 => l_second_lac2_information3,
p_second_lac2_information4 => l_second_lac2_information4,
p_second_lac2_information5 => l_second_lac2_information5,
p_second_noa_information1 => l_second_noa_information1,
p_second_noa_information2 => l_second_noa_information2,
p_second_noa_information3 => l_second_noa_information3,
p_second_noa_information4 => l_second_noa_information4,
p_second_noa_information5 => l_second_noa_information5,
p_first_lac1_information1 => l_first_lac1_information1,
p_first_lac1_information2 => l_first_lac1_information2,
p_first_lac1_information3 => l_first_lac1_information3,
p_first_lac1_information4 => l_first_lac1_information4,
p_first_lac1_information5 => l_first_lac1_information5,
p_first_lac2_information1 => l_first_lac2_information1,
p_first_lac2_information2 => l_first_lac2_information2,
p_first_lac2_information3 => l_first_lac2_information3,
p_first_lac2_information4 => l_first_lac2_information4,
p_first_lac2_information5 => l_first_lac2_information5,
-- Added for sf52_from_data_elements
p_to_retention_allow_percentag => l_to_retention_allow_percentag,
p_to_supervisory_diff_percenta => l_to_supervisory_diff_percenta,
p_to_staffing_diff_percentage => l_to_staffing_diff_percentage,
p_award_percentage => l_award_percentage,
-- Added for Elect Auth.
p_u_approval_status => l_u_approval_status,
p_approval_date => l_approval_date,
p_approving_official_full_name => l_approving_official_full_name,
p_approving_official_work_titl => l_approving_official_work_titl,
-- p_u_attachment_modified_flag => l_u_attachment_modified_flag,
-- p_u_approved_flag => l_u_approved_flag,
-- p_u_user_name_acted_on => l_u_user_name_acted_on,
p_u_action_taken => p_action_taken,
-- p_i_user_name_routed_to => l_i_user_name_routed_to,
-- p_i_groupbox_id => l_i_groupbox_id,
-- p_i_routing_list_id => l_i_routing_list_id,
-- p_i_routing_seq_number => l_i_routing_seq_number,
p_u_prh_object_version_number => l_u_prh_object_version_number,
p_i_pa_routing_history_id => l_i_pa_routing_history_id,
p_i_prh_object_version_number => l_i_prh_object_version_number
);
end update_sf52_action_taken;
update_sf52_action_taken(p_pa_request_id => itemkey,
p_routing_group_id => l_routing_group_id,
p_groupbox_id => l_groupbox_id,
p_action_taken => 'NOT_ROUTED',
p_gbx_user_id => l_gbx_user_id);
procedure update_sf52_for_wgi_denial ( itemtype in varchar2,
itemkey in varchar2,
actid in number,
funcmode in varchar2,
result in out nocopy varchar2) is
--
--
l_u_prh_object_version_number number;
select
object_version_number,
person_id,
effective_date,
pay_rate_determinant,
first_action_la_code1,
first_action_la_desc1,
first_noa_code,
first_noa_desc,
first_noa_id,
from_adj_basic_pay,
from_basic_pay,
from_grade_or_level,
from_locality_adj,
from_occ_code,
from_other_pay_amount,
from_pay_basis,
from_pay_plan,
from_position_id,
from_position_org_line1,
from_position_org_line2,
from_position_org_line3,
from_position_org_line4,
from_position_org_line5,
from_position_org_line6,
from_position_number,
from_position_seq_no,
from_position_title,
from_step_or_rate,
from_total_salary
from ghr_pa_requests
where pa_request_id = to_number(itemkey);
ghr_sf52_api.update_sf52(
p_validate => l_validate,
p_pa_request_id => to_number(itemkey),
p_par_object_version_number => l_par_object_version_number,
p_first_action_la_code1 => l_first_action_la_code1,
p_first_action_la_desc1 => l_first_action_la_desc1,
p_first_action_la_code2 => l_first_action_la_code2,
p_first_action_la_desc2 => l_first_action_la_desc2,
p_first_noa_code => l_first_noa_code,
p_first_noa_desc => l_first_noa_desc,
p_first_noa_id => l_first_noa_id,
p_to_grade_id => null,
p_to_job_id => null,
p_to_au_overtime => null,
p_to_auo_premium_pay_indicator => null,
p_to_availability_pay => null,
p_to_ap_premium_pay_indicator => null,
p_to_retention_allowance => null,
p_to_supervisory_differential => null,
p_to_staffing_differential => null,
p_to_adj_basic_pay => l_from_adj_basic_pay,
p_to_basic_pay => l_from_basic_pay,
p_to_grade_or_level => l_from_grade_or_level,
p_to_locality_adj => l_from_locality_adj,
p_to_occ_code => l_from_occ_code,
p_to_other_pay_amount => l_from_other_pay_amount,
p_to_pay_basis => l_from_pay_basis,
p_to_pay_plan => l_from_pay_plan,
p_to_position_id => l_from_position_id,
p_to_position_org_line1 => l_from_position_org_line1,
p_to_position_org_line2 => l_from_position_org_line2,
p_to_position_org_line3 => l_from_position_org_line3,
p_to_position_org_line4 => l_from_position_org_line4,
p_to_position_org_line5 => l_from_position_org_line5,
p_to_position_org_line6 => l_from_position_org_line6,
p_to_position_number => l_from_position_number,
p_to_position_seq_no => l_from_position_seq_no,
p_to_position_title => l_from_position_title,
p_to_step_or_rate => l_from_step_or_rate,
p_to_total_salary => l_from_total_salary,
p_u_action_taken => 'NOT_ROUTED',
p_u_prh_object_version_number => l_u_prh_object_version_number,
p_i_pa_routing_history_id => l_i_pa_routing_history_id,
p_i_prh_object_version_number => l_i_prh_object_version_number
);
wf_core.context('WGI', 'ghr_wf_wgi_pkg.update_sf52_for_wgi_denial',itemtype, itemkey, to_char(actid), funcmode);
end update_sf52_for_wgi_denial;
select *
from ghr_pa_requests
where pa_request_id = to_number(itemkey);
select count(*)
from ghr_pa_requests
where pa_request_id = to_number(itemkey);