[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select ppf.person_id PERSON_ID,
ppf.first_name FIRST_NAME,
ppf.last_name LAST_NAME,
ppf.middle_names MIDDLE_NAMES,
ppf.full_name FULL_NAME,
ppf.date_of_birth DATE_OF_BIRTH,
ppf.national_identifier NATIONAL_IDENTIFIER,
paf.position_id POSITION_ID,
paf.assignment_id ASSIGNMENT_ID,
paf.grade_id GRADE_ID,
paf.job_id JOB_ID,
paf.location_id LOCATION_ID,
paf.organization_id ORGANIZATION_ID,
paf.business_group_id BUSINESS_GROUP_ID
from per_assignments_f paf,
per_people_f ppf,
per_person_types ppt,
per_assignment_status_types pas_t,
hr_location_extra_info hlei,
ghr_duty_stations_f gdsf,
ghr_locality_pay_areas_f glpa
where ppf.person_id = paf.person_id
and paf.primary_flag = 'Y'
and paf.assignment_type <> 'B'
and paf.assignment_status_type_id = pas_t.assignment_status_type_id
and upper(pas_t.user_status) not in (
'TERMINATE ASSIGNMENT', 'ACTIVE APPLICATION', 'OFFER', 'ACCEPTED',
'TERMINATE APPLICATION', 'END', 'TERMINATE APPOINTMENT', 'SEPARATED')
and 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 effective_date between ppf.effective_start_date and ppf.effective_end_date
and paf.organization_id + 0 = nvl(p_org_id, paf.organization_id)
and paf.position_id is not null
and paf.location_id = hlei.location_id
and hlei.information_type = 'GHR_US_LOC_INFORMATION'
and hlei.lei_information3 = gdsf.duty_station_id
and gdsf.locality_pay_area_id = glpa.locality_pay_area_id
and effective_date between gdsf.effective_start_date and gdsf.effective_end_date
and effective_date between glpa.effective_start_date and glpa.effective_end_date
and glpa.locality_pay_area_code = l_p_locality_area_code
order by ppf.person_id;
SELECT name, effective_date, mass_salary_id, user_table_id, submit_flag,
executive_order_number, executive_order_date, ROWID, PA_REQUEST_ID,
ORGANIZATION_ID, DUTY_STATION_ID, PERSONNEL_OFFICE_ID,
AGENCY_CODE_SUBELEMENT, OPM_ISSUANCE_NUMBER, OPM_ISSUANCE_DATE,
locality_pay_area_code
FROM ghr_mass_salaries
WHERE MASS_SALARY_ID = p_msl_id
---and process_type = <>
for update of user_table_id nowait;
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 in
(SELECT nature_of_action_id
FROM ghr_nature_of_actions
WHERE code = nvl(ghr_msl_pkg.g_first_noa_code,'895') )
) and proc_method_flag = 'Y';
SELECT range_or_match
FROM pay_user_tables
WHERE user_table_id = p_user_table_id;
IF check_select_flg(per.person_id,
upper(p_action),
l_effective_date,
p_mass_salary_id,
l_sel_flg) then
hr_utility.set_location('check_select_flg ' || l_proc,7);
-- Should create comments only if comments need to be inserted
ELSIF l_comment_sal IS NOT NULL THEN
-- Bug#3968005 Replaced parameter l_pay_sel with l_sel_flg
ins_upd_per_extra_info
(per.person_id,l_effective_date, l_sel_flg, l_comment_sal,p_mass_salary_id);
g_proc := 'update_SEL_FLG';
update_SEL_FLG(PER.PERSON_ID,l_effective_date);
END IF; -- end if for check_select_flg
update ghr_mass_salaries
set submit_flag = 'P'
where rowid = l_rowid;
HR_UTILITY.SET_LOCATION('Error in Update ghr_msl Sql error '||sqlerrm(sqlcode),30);
update ghr_mass_salaries
set submit_flag = 'E'
where rowid = l_rowid;
update ghr_mass_salaries
set submit_flag = 'E'
where rowid = l_rowid;
select ppf.person_id PERSON_ID,
ppf.first_name FIRST_NAME,
ppf.last_name LAST_NAME,
ppf.middle_names MIDDLE_NAMES,
ppf.full_name FULL_NAME,
ppf.date_of_birth DATE_OF_BIRTH,
ppf.national_identifier NATIONAL_IDENTIFIER,
paf.position_id POSITION_ID,
paf.assignment_id ASSIGNMENT_ID,
paf.grade_id GRADE_ID,
paf.job_id JOB_ID,
paf.location_id LOCATION_ID,
paf.organization_id ORGANIZATION_ID,
paf.business_group_id BUSINESS_GROUP_ID,
paf.assignment_status_type_id ASSIGNMENT_STATUS_TYPE_ID
from per_assignments_f paf,
per_people_f ppf,
per_person_types ppt
where ppf.person_id = paf.person_id
and effective_date between ppf.effective_start_date and ppf.effective_end_date
and effective_date between paf.effective_start_date and paf.effective_end_date
and paf.primary_flag = 'Y'
and paf.assignment_type <> 'B'
and ppf.person_type_id = ppt.person_type_id
and ppt.system_person_type IN ('EMP','EMP_APL')
and paf.organization_id = p_org_id
and paf.position_id is not null
order by ppf.person_id;
select ppf.person_id PERSON_ID,
ppf.first_name FIRST_NAME,
ppf.last_name LAST_NAME,
ppf.middle_names MIDDLE_NAMES,
ppf.full_name FULL_NAME,
ppf.date_of_birth DATE_OF_BIRTH,
ppf.national_identifier NATIONAL_IDENTIFIER,
paf.position_id POSITION_ID,
paf.assignment_id ASSIGNMENT_ID,
paf.grade_id GRADE_ID,
paf.job_id JOB_ID,
paf.location_id LOCATION_ID,
paf.organization_id ORGANIZATION_ID,
paf.business_group_id BUSINESS_GROUP_ID,
paf.assignment_status_type_id ASSIGNMENT_STATUS_TYPE_ID
from per_assignments_f paf,
per_people_f ppf,
per_person_types ppt
where ppf.person_id = paf.person_id
and effective_date between ppf.effective_start_date and ppf.effective_end_date
and effective_date between paf.effective_start_date and paf.effective_end_date
and paf.primary_flag = 'Y'
and paf.assignment_type <> 'B'
and ppf.person_type_id = ppt.person_type_id
and ppt.system_person_type IN ('EMP','EMP_APL')
and paf.position_id is not null
order by ppf.person_id;
select user_status from per_assignment_status_types
where assignment_status_type_id = asg_status_type_id
and upper(user_status) not in (
'TERMINATE ASSIGNMENT', /* 3 */
'ACTIVE APPLICATION', /* 4 */
'OFFER', /* 5 */
'ACCEPTED', /* 6 */
'TERMINATE APPLICATION', /* 7 */
'END', /* 8 */
'TERMINATE APPOINTMENT', /* 126 */
'SEPARATED'); /* 132 */
select name, effective_date, mass_salary_id, user_table_id, submit_flag,
executive_order_number, executive_order_date, ROWID, PA_REQUEST_ID,
ORGANIZATION_ID, DUTY_STATION_ID, PERSONNEL_OFFICE_ID,
AGENCY_CODE_SUBELEMENT, OPM_ISSUANCE_NUMBER, OPM_ISSUANCE_DATE, PROCESS_TYPE,
locality_pay_area_code
from ghr_mass_salaries
where MASS_SALARY_ID = p_msl_id
for update of user_table_id nowait;
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 in
(select nature_of_action_id
from ghr_nature_of_actions
where code = cnoacode)
) and proc_method_flag = 'Y';
SELECT null PERSON_ID,
'VACANT' FIRST_NAME,
'VACANT' LAST_NAME,
'VACANT' FULL_NAME,
null MIDDLE_NAMES,
null DATE_OF_BIRTH,
null NATIONAL_IDENTIFIER,
position_id POSITION_ID,
null ASSIGNMENT_ID,
to_NUMBER(null) GRADE_ID,
JOB_ID,
pop.LOCATION_ID,
pop.ORGANIZATION_ID,
pop.BUSINESS_GROUP_ID,
punits.name ORGANIZATION_NAME,
pop.availability_status_id
from hr_positions_f pop,
per_organization_units punits
WHERE trunc(effective_DATE) between pop.effective_start_DATE and pop.effective_END_DATE
and pop.organization_id = punits.organization_id
and pop.organization_id = nvl(p_org_pos_id,pop.organization_id)
and not exists
(
SELECT 'X'
FROM per_people_f p, per_assignments_f a
WHERE trunc(effective_DATE) between a.effective_start_DATE and a.effective_END_DATE
AND a.primary_flag = 'Y'
AND a.assignment_type <> 'B'
AND p.current_employee_flag = 'Y'
AND a.business_group_id = pop.business_group_id
AND a.person_id = p.person_id
AND a.position_id = pop.position_id
AND trunc(effective_DATE) between p.effective_start_DATE and p.effective_end_DATE
);
select name from hr_positions_f
where position_id = p_position_id;
l_position_data_rec ghr_sf52_pos_UPDATE.position_data_rec_type;
SELECT criteria.pay_plan pay_plan,
criteria.pay_rate_determinant prd,
ext.grade grade
FROM ghr_mass_salary_criteria criteria, ghr_mass_salary_criteria_ext ext
WHERE criteria.mass_salary_id=p_msl_id
AND criteria.mass_salary_criteria_id=ext.mass_salary_criteria_id(+);
select 1
from hr_location_extra_info hlei,
ghr_duty_stations_f gdsf,
ghr_locality_pay_areas_f glpa
where hlei.location_id = l_loc_id
and hlei.information_type = 'GHR_US_LOC_INFORMATION'
and hlei.lei_information3 = gdsf.duty_station_id
and gdsf.locality_pay_area_id = glpa.locality_pay_area_id
and effective_date between gdsf.effective_start_date and gdsf.effective_end_date
and effective_date between glpa.effective_start_date and glpa.effective_end_date
and glpa.locality_pay_area_code = l_loc_code;
SELECT gdf.segment1
,gdf.segment2
from per_grades grd, per_grade_definitions gdf
WHERE grd.grade_id = grd_id
and grd.grade_definition_id = gdf.grade_definition_id;
select user_table_id from pay_user_tables
where substr(user_table_name,1,4) = pay_table;
if check_select_flg(p_person_id
,upper(p_action)
,l_effective_date
,p_mass_salary_id
,l_sel_flg
) then
hr_utility.set_location('check_select_flg ' || l_proc,7);
-- Should create comments only if comments need to be inserted
ELSIF l_comment_sal IS NOT NULL THEN
l_comments := substr(l_comments || ' ' || l_comment_sal, 1,150);
g_proc := 'update_SEL_FLG';
update_SEL_FLG(p_PERSON_ID,l_effective_date);
END IF; -- end if for check_select_flg
check_select_flg_pos(un_per.position_id
,UPPER(p_action)
,l_effective_DATE
,p_mass_salary_id
,l_sel_flg);
ghr_position_extra_info_api.update_position_extra_info
( p_position_extra_info_id => l_pos_valid_grade_ei_data.position_extra_info_id
, p_effective_date => l_effective_date
, p_object_version_number => l_pos_valid_grade_ei_data.object_version_number
, p_poei_information5 => l_to_pay_table_id);
ghr_validate_perwsdpo.update_posn_status(l_position_id,l_effective_date);
position_history_update (p_position_id => l_position_id,
p_effective_date => l_effective_date,
p_table_id => l_user_table_id,
p_upd_tableid => l_to_pay_table_id);
g_proc := 'ghr_sf52.UPDATE_position_info';
l_mtcerrbuf := 'Error in ghr_sf52_pos_UPDATE.UPDATE_position_info' ||
' Sql Err is '|| sqlerrm(sqlcode);
update ghr_mass_salaries
set submit_flag = 'P'
where rowid = l_rowid;
HR_UTILITY.SET_LOCATION('Error in Update ghr_msl Sql error '||sqlerrm(sqlcode),30);
update ghr_mass_salaries
set submit_flag = 'E'
where rowid = l_rowid;
update ghr_mass_salaries
set submit_flag = 'E'
where rowid = l_rowid;
delete from ghr_mass_actions_preview
where mass_action_type = 'SALARY'
and session_id = p_session_id;
select EMPLOYEE_DATE_OF_BIRTH,
substr(EMPLOYEE_LAST_NAME||', '||EMPLOYEE_FIRST_NAME||' '||
EMPLOYEE_MIDDLE_NAMES,1,240) FULL_NAME,
EMPLOYEE_NATIONAL_IDENTIFIER,
DUTY_STATION_CODE,
DUTY_STATION_DESC,
PERSONNEL_OFFICE_ID,
FROM_BASIC_PAY,
TO_BASIC_PAY,
--Bug#2383992
FROM_ADJ_BASIC_PAY,
TO_ADJ_BASIC_PAY,
--Bug#2383992
NULL FROM_AVAILABILITY_PAY,
TO_AVAILABILITY_PAY,
FROM_LOCALITY_ADJ,
TO_LOCALITY_ADJ,
FROM_TOTAL_SALARY,
TO_TOTAL_SALARY,
NULL FROM_AU_OVERTIME,
TO_AU_OVERTIME,
TO_POSITION_ID POSITION_ID,
TO_POSITION_TITLE POSITION_TITLE,
-- FWFA Changes Bug#4444609
TO_POSITION_NUMBER POSITION_NUMBER,
TO_POSITION_SEQ_NO POSITION_SEQ_NO,
-- FWFA Changes
null org_structure_id,
FROM_AGENCY_CODE,
PERSON_ID,
-- p_mass_salary_id
'Y' Sel_flag,
first_action_la_code1,
first_action_la_code2,
NULL REMARK_CODE1,
NULL REMARK_CODE2,
from_grade_or_level,
from_step_or_rate,
from_pay_plan,
PAY_RATE_DETERMINANT,
TENURE,
EMPLOYEE_ASSIGNMENT_ID,
FROM_OTHER_PAY_AMOUNT,
TO_OTHER_PAY_AMOUNT,
--Bug#2383992
NULL FROM_RETENTION_ALLOWANCE,
TO_RETENTION_ALLOWANCE,
NULL FROM_SUPERVISORY_DIFFERENTIAL,
TO_SUPERVISORY_DIFFERENTIAL,
NULL FROM_CAPPED_OTHER_PAY,
NULL TO_CAPPED_OTHER_PAY,
-- FWFA Changes Bug#4444609
input_pay_rate_determinant,
from_pay_table_identifier,
to_pay_table_identifier
-- FWFA Changes
from ghr_pa_requests
where person_id = p_person_id
and effective_date = p_effective_date
and substr(request_number,(instr(request_number,'-')+1)) = to_char(p_mass_salary_id)
and first_noa_code = nvl(ghr_msl_pkg.g_first_noa_code,'895');
select EMPLOYEE_DATE_OF_BIRTH,
substr(EMPLOYEE_LAST_NAME||', '||EMPLOYEE_FIRST_NAME||' '||
EMPLOYEE_MIDDLE_NAMES,1,240) FULL_NAME,
EMPLOYEE_NATIONAL_IDENTIFIER,
DUTY_STATION_CODE,
DUTY_STATION_DESC,
PERSONNEL_OFFICE_ID,
FROM_BASIC_PAY,
TO_BASIC_PAY,
--Bug#2383992
FROM_ADJ_BASIC_PAY,
TO_ADJ_BASIC_PAY,
--Bug#2383992
NULL FROM_AVAILABILITY_PAY,
TO_AVAILABILITY_PAY,
FROM_LOCALITY_ADJ,
TO_LOCALITY_ADJ,
FROM_TOTAL_SALARY,
TO_TOTAL_SALARY,
NULL FROM_AU_OVERTIME,
TO_AU_OVERTIME,
TO_POSITION_ID POSITION_ID,
TO_POSITION_TITLE POSITION_TITLE,
-- FWFA Changes Bug#4444609
TO_POSITION_NUMBER POSITION_NUMBER,
TO_POSITION_SEQ_NO POSITION_SEQ_NO,
-- FWFA Changes
null org_structure_id,
FROM_AGENCY_CODE,
PERSON_ID,
-- p_mass_salary_id
'Y' Sel_flag,
first_action_la_code1,
first_action_la_code2,
NULL REMARK_CODE1,
NULL REMARK_CODE2,
from_grade_or_level,
from_step_or_rate,
from_pay_plan,
PAY_RATE_DETERMINANT,
TENURE,
EMPLOYEE_ASSIGNMENT_ID,
FROM_OTHER_PAY_AMOUNT,
TO_OTHER_PAY_AMOUNT,
--Bug#2383992
NULL FROM_RETENTION_ALLOWANCE,
TO_RETENTION_ALLOWANCE,
NULL FROM_SUPERVISORY_DIFFERENTIAL,
TO_SUPERVISORY_DIFFERENTIAL,
NULL FROM_CAPPED_OTHER_PAY,
NULL TO_CAPPED_OTHER_PAY,
-- FWFA Changes Bug#4444609
input_pay_rate_determinant,
from_pay_table_identifier,
to_pay_table_identifier
-- FWFA Changes
from ghr_pa_requests
where person_id = p_person_id
and effective_date = p_effective_date
and substr(request_number,(instr(request_number,'-')+1)) = to_char(p_mass_salary_id)
and first_noa_code in ('894','800');
FUNCTION check_select_flg(p_person_id in number,
p_action in varchar2,
p_effective_date in date,
p_mass_salary_id in number,
p_sel_flg in out nocopy varchar2)
RETURN boolean IS
l_per_ei_data per_people_extra_info%rowtype;
l_proc varchar2(72) := g_package || '.check_select_flg';
g_proc := 'check_select_flg';
delete from ghr_mass_actions_preview
where mass_action_type = 'SALARY'
and session_id = p_mass_salary_id;
procedure update_sel_flg (p_person_id in number,p_effective_date date) is
l_person_extra_info_id number;
l_proc varchar2(72) := g_package || '.update_sel_flg';
g_proc := 'update_sel_flg';
ghr_person_extra_info_api.update_person_extra_info
(P_PERSON_EXTRA_INFO_ID => l_person_extra_info_id
,P_EFFECTIVE_DATE => sysdate
,P_OBJECT_VERSION_NUMBER => l_object_version_number
,p_pei_INFORMATION3 => NULL
,p_pei_INFORMATION4 => NULL
,p_pei_INFORMATION5 => NULL
,P_PEI_INFORMATION_CATEGORY => 'GHR_US_PER_MASS_ACTIONS');
end update_sel_flg;
select pay_plan,description
from ghr_pay_plans
WHERE PAY_PLAN = PP;
select user_table_id,substr(user_table_name,0,4) user_table_name
from pay_user_tables
where substr(user_table_name,6,14) in
('Oracle Federal','Federal Agency')
and user_table_id = p_user_table_id;
select information9 info9
,information10 info10
,information11 info11
from ghr_pa_history
where person_id = p_person_id
and pa_history_id IN ( select max(pa_history_id)
from ghr_pa_history
where person_id = p_person_id
and information5 = 'GHR_US_PER_MASS_ACTIONS'
and table_name = 'PER_PEOPLE_EXTRA_INFO'
and effective_date = eff_date
group by information11);
SELECT person_extra_info_id, object_version_number
FROM PER_people_EXTRA_INFO
WHERE person_ID = person
and information_type = 'GHR_US_PER_MASS_ACTIONS';
ghr_person_extra_info_api.update_person_extra_info
(P_PERSON_EXTRA_INFO_ID => l_person_extra_info_id
,P_EFFECTIVE_DATE => trunc(l_eff_date)
,P_OBJECT_VERSION_NUMBER => l_object_version_number
,p_pei_INFORMATION3 => p_sel_flag
,p_pei_INFORMATION4 => p_comment
,p_pei_INFORMATION5 => to_char(p_msl_id)
,P_PEI_INFORMATION_CATEGORY => 'GHR_US_PER_MASS_ACTIONS');
SELECT effective_end_date end_date
FROM ghr_duty_stations_f
WHERE duty_station_id=p_ds_id
AND g_effective_date between effective_start_date and effective_end_date;
SELECT 1
FROM ghr_pay_plans
WHERE equivalent_pay_plan ='ES'
AND pay_plan=p_pay_plan;
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
and effective_date = p_effective_date
and nvl(pr.first_noa_cancel_or_correct,'X') <> ghr_history_api.g_cancel
---- Bug # 657439
--and nvl(pr.first_noa_cancel_or_correct,'X') <> 'CANCELED'
group by pr.pa_request_id;
select substr(pr.employee_last_name || ', ' || pr.employee_first_name,1,240) fname
from ghr_pa_requests pr
where pr.pa_request_id = l_pa_request_id;
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
and effective_date = p_effective_date
and nvl(pr.first_noa_cancel_or_correct,'X') <> ghr_history_api.g_cancel
group by pr.pa_request_id;
SELECT equivalent_pay_plan
FROM ghr_pay_plans
WHERE pay_plan = p_pay_plan;
select nvl(action_taken,' ') action_taken
from ghr_pa_routing_history
where pa_routing_history_id = p_r_hist_id;
SELECT 1
FROM ghr_pay_plans
WHERE equivalent_pay_plan ='ES'
AND pay_plan=p_pay_plan;
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 = '800')
and effective_date = p_effective_date
and nvl(pr.first_noa_cancel_or_correct,'X') <> ghr_history_api.g_cancel
group by pr.pa_request_id;
select substr(pr.employee_last_name || ', ' || pr.employee_first_name,1,240) fname
from ghr_pa_requests pr
where pr.pa_request_id = l_pa_request_id;
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 = '800')
and effective_date = p_effective_date
and nvl(pr.first_noa_cancel_or_correct,'X') <> ghr_history_api.g_cancel
group by pr.pa_request_id;
SELECT equivalent_pay_plan
FROM ghr_pay_plans
WHERE pay_plan = p_pay_plan;
select nvl(action_taken,' ') action_taken
from ghr_pa_routing_history
where pa_routing_history_id = p_r_hist_id;
GHR_MASS_ACT_CUSTOM.pre_insert (
p_cust_in_rec => l_cust_in_rec,
p_cust_rec => l_cust_rec);
insert into GHR_MASS_ACTIONS_PREVIEW
(
mass_action_type,
--report_type,
ui_type,
session_id,
effective_date,
employee_date_of_birth,
full_name,
national_identifier,
duty_station_code,
duty_station_desc,
personnel_office_id,
from_basic_pay,
to_basic_pay,
-- Bug#2383992
from_adj_basic_pay ,
to_adj_basic_pay ,
-- Bug#2383992
from_availability_pay,
to_availability_pay,
from_locality_adj,
to_locality_adj,
from_total_salary,
to_total_salary,
from_auo_pay,
to_auo_pay,
from_other_pay,
to_other_pay,
-- Bug#2383992
from_capped_other_pay,
to_capped_other_pay,
from_retention_allowance,
to_retention_allowance,
from_supervisory_differential ,
to_supervisory_differential ,
-- Bug#2383992
position_id,
position_title,
-- FWFA Changes Bug#4444609
position_number,
position_seq_no,
-- FWFA Changes
org_structure_id,
agency_code,
person_id,
select_flag,
first_noa_code,
first_action_la_code1,
first_action_la_code2,
grade_or_level,
step_or_rate,
pay_plan,
pay_rate_determinant,
tenure,
POI_DESC,
organization_name,
-- FWFA Changes Bug#4444609
input_pay_rate_determinant,
from_pay_table_identifier,
to_pay_table_identifier,
-- FWFA Changes
USER_ATTRIBUTE1,
USER_ATTRIBUTE2,
USER_ATTRIBUTE3,
USER_ATTRIBUTE4,
USER_ATTRIBUTE5,
USER_ATTRIBUTE6,
USER_ATTRIBUTE7,
USER_ATTRIBUTE8,
USER_ATTRIBUTE9,
USER_ATTRIBUTE10,
USER_ATTRIBUTE11,
USER_ATTRIBUTE12,
USER_ATTRIBUTE13,
USER_ATTRIBUTE14,
USER_ATTRIBUTE15,
USER_ATTRIBUTE16,
USER_ATTRIBUTE17,
USER_ATTRIBUTE18,
USER_ATTRIBUTE19,
USER_ATTRIBUTE20
)
values
(
'SALARY',
/*--decode(p_action,'REPORT',userenv('SESSIONID'),p_mass_realignment_id),*/
decode(p_action,'SHOW','FORM','REPORT'),
userenv('SESSIONID'),
p_effective_date,
p_date_of_birth,
p_full_name,
p_national_identifier,
p_duty_station_code,
p_duty_station_desc,
p_personnel_office_id,
p_basic_pay,
p_new_basic_pay,
-- Bug#2383992
p_adj_basic_pay ,
p_new_adj_basic_pay ,
-- Bug#2383992
p_old_avail_pay,
p_new_avail_pay,
p_old_loc_diff,
p_new_loc_diff,
p_tot_old_sal,
p_tot_new_sal,
p_old_auo_pay,
p_new_auo_pay,
p_old_other_pay, ----------- nvl(p_old_auo_pay,0)+ nvl(p_old_avail_pay,0),
p_new_other_pay, ----------- nvl(p_new_auo_pay,0)+ nvl(p_new_avail_pay,0),
-- Bug#2383992
p_old_capped_other_pay,
p_new_capped_other_pay,
p_old_retention_allowance,
p_new_retention_allowance,
p_old_supervisory_differential ,
p_new_supervisory_differential ,
-- Bug#2383992
p_position_id,
p_position_title,
-- FWFA Changes Bug#4444609
p_position_number,
p_position_seq_no,
-- FWFA Changes
p_org_structure_id,
p_agency_sub_element_code,
p_person_id,
p_sel_flg,
nvl(ghr_msl_pkg.g_first_noa_code,'895'),
p_first_action_la_code1,
p_first_action_la_code2,
p_grade_or_level,
l_step_or_rate,
p_pay_plan,
p_pay_rate_determinant,
p_tenure,
l_poi_desc,
p_organization_name,
-- FWFA Changes Bug#4444609
p_input_pay_rate_determinant,
p_from_pay_table_id,
p_to_pay_table_id,
-- FWFA Changes
l_cust_rec.user_attribute1,
l_cust_rec.user_attribute2,
l_cust_rec.user_attribute3,
l_cust_rec.user_attribute4,
l_cust_rec.user_attribute5,
l_cust_rec.user_attribute6,
l_cust_rec.user_attribute7,
l_cust_rec.user_attribute8,
l_cust_rec.user_attribute9,
l_cust_rec.user_attribute10,
l_cust_rec.user_attribute11,
l_cust_rec.user_attribute12,
l_cust_rec.user_attribute13,
l_cust_rec.user_attribute14,
l_cust_rec.user_attribute15,
l_cust_rec.user_attribute16,
l_cust_rec.user_attribute17,
l_cust_rec.user_attribute18,
l_cust_rec.user_attribute19,
l_cust_rec.user_attribute20
);
GHR_MASS_ACT_CUSTOM.pre_insert (
p_cust_in_rec => l_cust_in_rec,
p_cust_rec => l_cust_rec);
insert into GHR_MASS_ACTIONS_PREVIEW
(
mass_action_type,
--report_type,
ui_type,
session_id,
effective_date,
employee_date_of_birth,
full_name,
national_identifier,
duty_station_code,
duty_station_desc,
personnel_office_id,
from_basic_pay,
to_basic_pay,
-- Bug#2383992
from_adj_basic_pay ,
to_adj_basic_pay ,
-- Bug#2383992
from_availability_pay,
to_availability_pay,
from_locality_adj,
to_locality_adj,
from_total_salary,
to_total_salary,
from_auo_pay,
to_auo_pay,
from_other_pay,
to_other_pay,
-- Bug#2383992
from_capped_other_pay,
to_capped_other_pay,
from_retention_allowance,
to_retention_allowance,
from_supervisory_differential ,
to_supervisory_differential ,
-- Bug#2383992
position_id,
position_title,
-- FWFA Changes Bug#4444609
position_number,
position_seq_no,
-- FWFA Changes
org_structure_id,
agency_code,
person_id,
select_flag,
first_noa_code,
first_action_la_code1,
first_action_la_code2,
grade_or_level,
step_or_rate,
pay_plan,
pay_rate_determinant,
tenure,
POI_DESC,
organization_name,
-- FWFA Changes Bug#4444609
input_pay_rate_determinant,
from_pay_table_identifier,
to_pay_table_identifier,
-- FWFA Changes
USER_ATTRIBUTE1,
USER_ATTRIBUTE2,
USER_ATTRIBUTE3,
USER_ATTRIBUTE4,
USER_ATTRIBUTE5,
USER_ATTRIBUTE6,
USER_ATTRIBUTE7,
USER_ATTRIBUTE8,
USER_ATTRIBUTE9,
USER_ATTRIBUTE10,
USER_ATTRIBUTE11,
USER_ATTRIBUTE12,
USER_ATTRIBUTE13,
USER_ATTRIBUTE14,
USER_ATTRIBUTE15,
USER_ATTRIBUTE16,
USER_ATTRIBUTE17,
USER_ATTRIBUTE18,
USER_ATTRIBUTE19,
USER_ATTRIBUTE20
)
values
(
'SALARY',
/*--decode(p_action,'REPORT',userenv('SESSIONID'),p_mass_realignment_id),*/
decode(p_action,'SHOW','FORM','REPORT'),
userenv('SESSIONID'),
p_effective_date,
p_date_of_birth,
p_full_name,
p_national_identifier,
p_duty_station_code,
p_duty_station_desc,
p_personnel_office_id,
p_basic_pay,
p_new_basic_pay,
p_adj_basic_pay ,
p_new_adj_basic_pay ,
p_old_avail_pay,
p_new_avail_pay,
p_old_loc_diff,
p_new_loc_diff,
p_tot_old_sal,
p_tot_new_sal,
p_old_auo_pay,
p_new_auo_pay,
p_old_other_pay,
p_new_other_pay,
p_old_capped_other_pay,
p_new_capped_other_pay,
p_old_retention_allowance,
p_new_retention_allowance,
p_old_supervisory_differential ,
p_new_supervisory_differential ,
p_position_id,
p_position_title,
-- FWFA Changes Bug#4444609
p_position_number,
p_position_seq_no,
-- FWFA Changes
p_org_structure_id,
p_agency_sub_element_code,
p_person_id,
p_sel_flg,
decode(p_basic_pay,p_new_basic_pay,'800','894'),
p_first_action_la_code1,
p_first_action_la_code2,
p_grade_or_level,
l_step_or_rate,
p_pay_plan,
p_pay_rate_determinant,
p_tenure,
l_poi_desc,
p_organization_name,
-- FWFA Changes Bug#4444609
p_input_pay_rate_determinant,
p_from_pay_table_id,
p_to_pay_table_id,
-- FWFA Changes
l_cust_rec.user_attribute1,
l_cust_rec.user_attribute2,
l_cust_rec.user_attribute3,
l_cust_rec.user_attribute4,
l_cust_rec.user_attribute5,
l_cust_rec.user_attribute6,
l_cust_rec.user_attribute7,
l_cust_rec.user_attribute8,
l_cust_rec.user_attribute9,
l_cust_rec.user_attribute10,
l_cust_rec.user_attribute11,
l_cust_rec.user_attribute12,
l_cust_rec.user_attribute13,
l_cust_rec.user_attribute14,
l_cust_rec.user_attribute15,
l_cust_rec.user_attribute16,
l_cust_rec.user_attribute17,
l_cust_rec.user_attribute18,
l_cust_rec.user_attribute19,
l_cust_rec.user_attribute20
);
select * from ghr_pa_requests
where pa_request_id = p_pa_request_id;
select * from ghr_pa_remarks
where pa_request_id = p_pa_request_id;
SELECT position_extra_info_id, object_version_NUMBER
from per_position_extra_info
WHERE position_id = (p_position)
and INFORMATION_TYPE = 'GHR_US_POS_MASS_ACTIONS';
ghr_position_extra_info_api.UPDATE_position_extra_info
(P_POSITION_EXTRA_INFO_ID => l_position_extra_info_id
,P_OBJECT_VERSION_NUMBER => l_object_version_NUMBER
,P_POEI_INFORMATION_CATEGORY => 'GHR_US_POS_MASS_ACTIONS'
,P_EFFECTIVE_DATE => l_eff_DATE
,P_POEI_INFORMATION19 => null
,P_POEI_INFORMATION20 => null
,P_POEI_INFORMATION21 => null);
hr_utility.set_location('UPDATE posei error 3' || l_proc,10);
SELECT position_extra_info_id, object_version_NUMBER
FROM PER_POSITION_EXTRA_INFO
WHERE POSITION_ID = position
and information_type = 'GHR_US_POS_MASS_ACTIONS';
ghr_position_extra_info_api.UPDATE_position_extra_info
(P_POSITION_EXTRA_INFO_ID => l_position_extra_info_id
,P_EFFECTIVE_DATE => trunc(l_eff_DATE)
,P_OBJECT_VERSION_NUMBER => l_object_version_NUMBER
,p_poei_INFORMATION19 => p_sel_flag
,p_poei_INFORMATION20 => p_comment
,p_poei_INFORMATION21 => to_char(p_msl_id)
,P_POEI_INFORMATION_CATEGORY => 'GHR_US_POS_MASS_ACTIONS');
hr_utility.set_location('UPDATE posei error 1' || l_proc,10);
procedure check_select_flg_pos(p_position_id in NUMBER,
p_action in VARCHAR2,
p_effective_DATE in DATE,
p_msl_id in NUMBER,
p_sel_flg in OUT NOCOPY VARCHAR2)
IS
l_comments VARCHAR2(150);
l_proc VARCHAR2(72) := g_package || '.check_select_flg';
g_proc := 'check_SELECT_flg';
END check_select_flg_pos;
procedure position_history_update (p_position_id IN hr_positions_f.position_id%type,
p_effective_date IN date,
p_table_id IN pay_user_tables.user_table_id%type,
p_upd_tableid IN pay_user_tables.user_table_id%type)
is
CURSOR cur_hist_rows(l_tab_id NUMBER,l_eff_date date, l_pos_id NUMBER)
IS
SELECT pah.pa_history_id,
pah.information4 , -- position_id
to_number(pah.information11) user_tab_id,
pah.effective_date
FROM ghr_pa_history pah
WHERE pah.table_name = 'PER_POSITION_EXTRA_INFO'
AND pah.information5 = 'GHR_US_POS_VALID_GRADE'
AND to_number(pah.information4) = l_pos_id
AND to_number(pah.information11) = l_tab_id
AND pah.effective_date >= to_date('2005/05/01','YYYY/MM/DD')
AND pah.effective_date > l_eff_date
AND to_number(pah.information4) in
(SELECT position_id
from hr_positions_f pos
WHERE pos.position_id = to_number(pah.information4)
AND pah.effective_date
between pos.effective_start_date and pos.effective_end_date
AND HR_GENERAL.DECODE_AVAILABILITY_STATUS(pos.availability_status_id) = 'Active');
UPDATE GHR_PA_HISTORY upah
SET information11 = to_char(p_upd_tableid)
WHERE pa_history_id = l_hist_id;
end position_history_update;