[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT ppf.person_id, ppf.national_identifier, ppf.date_of_birth,
ppf.last_name, ppf.first_name, ppf.middle_names,
ppf.full_name, paf.position_id, paf.assignment_id,
paf.grade_id, paf.job_id, paf.location_id,
paf.organization_id
FROM per_assignments_f paf, per_people_f ppf,
per_person_types ppt
WHERE ppf.person_id = paf.person_id
AND paf.primary_flag = 'Y'
AND paf.assignment_type <> 'B'
AND l_effective_date BETWEEN paf.effective_start_date
AND paf.effective_end_date
AND ppf.person_type_id = ppt.person_type_id
AND ppt.system_person_type IN ('EMP','EMP_APL')
AND l_effective_date BETWEEN ppf.effective_start_date
AND ppf.effective_end_date
AND ret_wgi_pay_date(paf.assignment_id,l_effective_date, p_frequency) IS NOT NULL
AND PAF.BUSINESS_GROUP_ID >=0; --Bug #5212248
SELECT groupbox_id
FROM ghr_pois
WHERE personnel_office_id = c_po_id;
SELECT noa_family_code
FROM ghr_families
WHERE noa_family_code IN (
SELECT noa_family_code
FROM ghr_noa_families
WHERE nature_of_action_id =
(SELECT nature_of_action_id
FROM ghr_nature_of_actions
WHERE code = l_first_noa_code
AND l_effective_date BETWEEN NVL(Date_from,l_effective_date)
AND NVL(date_to,l_effective_date)
)
)
AND proc_method_flag = 'Y';
SELECT 1
FROM fnd_sessions
WHERE session_id = USERENV ('sessionid');
SELECT gdf.segment1 pay_plan
FROM per_grades grd, per_grade_definitions gdf
WHERE grd.grade_id = p_grd_id
AND grd.grade_definition_id = gdf.grade_definition_id;
select 1
from ghr_pa_remarks
where pa_request_id = p_pa_request_id
and pa_remark_id = p_remark_id;
ghr_sf52_api.update_sf52 (
p_validate => l_validate,
p_pa_request_id => l_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 => NULL,
p_additional_info_tel_number => NULL,
p_altered_pa_request_id => NULL,
p_annuitant_indicator => l_annuitant_indicator,
p_annuitant_indicator_desc => NULL,
p_appropriation_code1 => l_appropriation_code1,
p_appropriation_code2 => l_appropriation_code2,
p_authorized_by_person_id => NULL,
p_authorized_by_title => NULL,
p_award_amount => NULL,
p_award_uom => NULL,
p_bargaining_unit_status => l_bargaining_unit_status,
p_citizenship => l_citizenship,
p_concurrence_date => NULL,
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_assignment_id,
p_employee_date_of_birth => l_date_of_birth,
p_employee_first_name => l_first_name,
p_employee_last_name => l_last_name,
p_employee_middle_names => l_middle_names,
p_employee_national_identifier => l_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 => NULL,
p_first_noa_code => l_first_noa_code,
p_first_noa_desc => l_first_noa_desc,
p_first_noa_id => l_nature_of_action_id,
p_first_noa_pa_request_id => NULL,
p_flsa_category => l_flsa_category,
p_forwarding_address_line1 => NULL,
p_forwarding_address_line2 => NULL,
p_forwarding_address_line3 => NULL,
p_forwarding_country => NULL,
p_forwarding_postal_code => NULL,
p_forwarding_region_2 => NULL,
p_forwarding_town_or_city => NULL,
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,
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 => NULL,
p_part_time_hours => l_part_time_hours,
-- FWFA Changes Bug#4444609 Added NVL Condition
p_pay_rate_determinant => NVL(l_pay_calc_out_rec_type.out_pay_rate_determinant,
l_pay_rate_determinant),
-- FWFA Changes
p_person_id => l_person_id,
p_position_occupied => l_position_occupied,
p_proposed_effective_date => NULL,
p_requested_by_person_id => NULL,
p_requested_by_title => NULL,
p_requested_date => NULL,
p_requesting_office_remarks_de => NULL,
p_requesting_office_remarks_fl => NULL,
p_request_number => 'WGI:'
|| l_pa_request_id,
p_resign_and_retire_reason_des => NULL,
p_retirement_plan => l_retirement_plan,
p_retirement_plan_desc => l_retirement_plan_desc,
p_second_action_la_code1 => NULL,
p_second_action_la_code2 => NULL,
p_second_action_la_desc1 => NULL,
p_second_action_la_desc2 => NULL,
p_second_noa_cancel_or_correct => NULL,
p_second_noa_code => NULL,
p_second_noa_desc => NULL,
p_second_noa_id => NULL,
p_second_noa_pa_request_id => NULL,
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_grade_id,
p_to_grade_or_level => l_from_grade_or_level,
p_to_job_id => l_job_id,
p_to_locality_adj => l_to_locality_adj,
p_to_occ_code => l_from_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_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_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 => NULL,
p_first_noa_information2 => NULL,
p_first_noa_information3 => NULL,
p_first_noa_information4 => NULL,
p_first_noa_information5 => NULL,
p_second_lac1_information1 => NULL,
p_second_lac1_information2 => NULL,
p_second_lac1_information3 => NULL,
p_second_lac1_information4 => NULL,
p_second_lac1_information5 => NULL,
p_second_lac2_information1 => NULL,
p_second_lac2_information2 => NULL,
p_second_lac2_information3 => NULL,
p_second_lac2_information4 => NULL,
p_second_lac2_information5 => NULL,
p_second_noa_information1 => NULL,
p_second_noa_information2 => NULL,
p_second_noa_information3 => NULL,
p_second_noa_information4 => NULL,
p_second_noa_information5 => NULL,
p_first_lac1_information1 => NULL,
p_first_lac1_information2 => NULL,
p_first_lac1_information3 => NULL,
p_first_lac1_information4 => NULL,
p_first_lac1_information5 => NULL,
p_first_lac2_information1 => NULL,
p_first_lac2_information2 => NULL,
p_first_lac2_information3 => NULL,
p_first_lac2_information4 => NULL,
p_first_lac2_information5 => NULL,
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 => l_u_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,
p_to_retention_allow_percentag => l_retention_allow_percentage,
p_to_supervisory_diff_percenta => l_supervisory_diff_percentage,
p_to_staffing_diff_percentage => l_staffing_diff_percentage,
p_award_percentage => l_award_percentage,
-- FWFA Changes
p_input_pay_rate_determinant => l_pay_rate_determinant,
p_from_pay_table_identifier => l_pay_calc_out_rec_type.pay_table_id,
p_to_pay_table_identifier => l_pay_calc_out_rec_type.calculation_pay_table_id
-- FWFA Changes
);
p_log_text => 'After update_sf52 for : '
|| l_full_name
|| ' Person ID : '
|| TO_CHAR (l_person_id
)
|| ' l_retirement_plan : '
|| l_retirement_plan,
p_message_name => NULL,
p_log_date => SYSDATE
);
|| ' Increase element entry. The employee cannot be selected for a WGI without a '
|| ' Pay Date. '
|| ' Action: You must enter a Pay Date in the employee''s Within Grade Increase '
|| ' element entry. Person ID : '
|| TO_CHAR (l_person_id
)
|| ' Assignment ID : '
|| TO_CHAR (l_assignment_id
)
|| ' PA Request ID : '
|| TO_CHAR (l_pa_request_id
),
p_message_name => 'Pay Date not entered',
p_log_date => SYSDATE
);
INSERT INTO ghr_process_log
(process_log_id, program_name, log_text,
message_name, log_date
)
VALUES (ghr_process_log_s.NEXTVAL, p_program_name, p_log_text,
l_message_name, p_log_date
);
SELECT noa.nature_of_action_id, noa.description
FROM ghr_nature_of_actions noa
WHERE noa.code = l_code
AND noa.enabled_flag = 'Y'
AND NVL (l_effective_date, TRUNC (SYSDATE)) BETWEEN noa.date_from
AND NVL (noa.date_to,
NVL (l_effective_date,
TRUNC (SYSDATE
)
)
);
SELECT fcl.description
FROM hr_lookups fcl
WHERE fcl.lookup_type = l_lookup_type
AND fcl.lookup_code = l_lookup_code
AND fcl.enabled_flag = 'Y'
AND NVL (l_effective_date, TRUNC (SYSDATE))
BETWEEN NVL (fcl.start_date_active,
NVL (l_effective_date, TRUNC (SYSDATE))
)
AND NVL (fcl.end_date_active,
NVL (l_effective_date, TRUNC (SYSDATE))
);
SELECT pr.pa_request_id,
MAX (pa_routing_history_id) pa_routing_history_id
FROM ghr_pa_requests pr, ghr_pa_routing_history prh
WHERE pr.pa_request_id = prh.pa_request_id
AND person_id = p_person_id
AND (first_noa_code = p_first_noa_code OR first_noa_code = '888'
)
AND effective_date >= (p_effective_date - p_days)
AND NVL (pr.first_noa_cancel_or_correct, 'X') <>
ghr_history_api.g_cancel
-- vtakru Apr 17, 98 vtakru
-- and nvl(pr.first_noa_cancel_or_correct,'X') <> 'CANCELED'
GROUP BY pr.pa_request_id;
SELECT NVL (action_taken, ' ')
INTO l_action_taken
FROM ghr_pa_routing_history
WHERE pa_routing_history_id = v_action_taken.pa_routing_history_id;
SELECT 'X'
FROM ghr_pay_plans
WHERE pay_plan = l_pay_plan AND wgi_enabled_flag = 'Y';
SELECT gpp.equivalent_pay_plan
FROM ghr_pay_plans gpp
WHERE gpp.pay_plan = l_from_pay_plan;
ghr_mass_actions_pkg.replace_insertion_values (p_desc => l_remark_desc1,
p_information1 => l_remark1_ins1,
p_information2 => l_remark1_ins2,
p_information3 => l_remark1_ins3,
p_desc_out => l_remark_desc1_out
);
ghr_mass_actions_pkg.replace_insertion_values (p_desc => l_remark_desc2,
p_information1 => l_remark2_ins1,
p_information2 => l_remark2_ins2,
p_information3 => l_remark2_ins3,
p_desc_out => l_remark_desc2_out
);
SELECT pay_plan
FROM ghr_pay_plans
WHERE pay_plan = p_from_pay_plan AND equivalent_pay_plan = 'FW';
SELECT maximum_step
FROM ghr_pay_plans
WHERE pay_plan = l_from_pay_plan;