[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
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 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 nvl(paf.effective_end_date,effective_date+1)
and ppf.person_type_id = ppt.person_type_id
and ppt.system_person_type = 'EMP'
and effective_date between ppf.effective_start_date
and nvl(ppf.effective_end_date,effective_date+1)
-- VSM. Enhancements [Masscrit.doc] Organization can be null
and paf.organization_id + 0 = nvl(p_org_id, paf.organization_id)
and paf.position_id is not null
order by ppf.person_id; -- 3539816 Order by added to prevent snapshot old error
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,
hr_organization_units hou
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 = hou.organization_id
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
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 =
(select nature_of_action_id
from ghr_nature_of_actions
where code = '894')
) and proc_method_flag = 'Y'; --AVR 13-JAN-99
SELECT 1 from pay_user_tables
WHERE substr(user_table_name,1,4) = 'ESSL'
AND user_table_id = l_user_table_id;
SELECT pay_plan ,pay_rate_determinant prd
FROM ghr_mass_salary_criteria
WHERE mass_salary_id=p_msl_id;
select EQUIVALENT_PAY_PLAN
from ghr_pay_plans
where pay_plan = c_pay_plan;
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;
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
-- Bug#3968005 Replaced parameter l_pay_sel with l_sel_flg
ins_upd_per_extra_info
(p_person_id,l_effective_date, l_sel_flg, l_comment_sal,p_mass_salary_id);
g_proc := 'update_SEL_FLG';
update_SEL_FLG(p_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;
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,
RPA_TYPE,
-- 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
-- Added by Dinkar for reports
AND SUBSTR(request_number,(instr(request_number,'-')+1)) = TO_CHAR(p_mass_salary_id)
AND first_noa_code = nvl(g_first_noa_code,'894');
SELECT ext.increase_percent percent
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
AND criteria.pay_plan = p_pay_plan
AND criteria.PAY_RATE_DETERMINANT = p_prd
And ext.GRADE = p_grade;
function check_select_flg_msl_perc(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,
p_increase_percent in out nocopy number
)
return boolean IS
l_per_ei_data per_people_extra_info%rowtype;
l_proc varchar2(72) := g_package || '.check_select_flg_msl_perc';
g_proc := 'check_select_flg_msl_perc';
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;
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 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;
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
-- Bug#3988449 Added p_pei_information10 to clear the increase percentage value.
,p_pei_information10 => 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
,information16 increase_percent -- Added by Sundar 3843306
,information17 info17
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_information10 => to_char(p_increase_percent)
,P_PEI_INFORMATION_CATEGORY => 'GHR_US_PER_MASS_ACTIONS');
SELECT count(*) cnt
FROM ghr_mass_salary_criteria
WHERE mass_salary_id = p_mass_salary_id
AND pay_plan = cur_pay_plan
AND pay_rate_determinant = cur_prd;
SELECT count(*) cnt
FROM ghr_mass_salary_criteria
WHERE mass_salary_id = p_mass_salary_id
AND pay_plan = cur_pay_plan
AND pay_rate_determinant = cur_prd;
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 count(*) count
from ghr_mass_salary_criteria
where mass_salary_id = p_mass_salary_id
and pay_plan = cur_pay_plan
and pay_rate_determinant = cur_prd;
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,
pr.employee_national_identifier SSN
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 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 nvl(person_id,0) = p_person_id
and nvl(second_noa_code,0) = p_second_noa_code
and nvl(effective_date,trunc(sysdate)) >= (p_effective_date-p_days)
and nvl(pr.second_noa_cancel_or_correct,'X') <> ghr_history_api.g_cancel
--Bug 657439
-- and nvl(pr.second_noa_cancel_or_correct,'X') <> 'CANCELED'
group by pr.pa_request_id;
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,
increase_percent
)
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(g_first_noa_code,'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,
p_increase_percent
);
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 person_id
FROM PER_people_EXTRA_INFO
WHERE information_type = 'GHR_US_PER_MASS_ACTIONS'
AND pei_INFORMATION3 IS NOT NULL;
update_sel_flg (l_person_id,p_effective_date);
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,
hr_organization_units hou
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 = hou.organization_id
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
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 =
(select nature_of_action_id
from ghr_nature_of_actions
where code = '894')
) and proc_method_flag = 'Y'; --AVR 13-JAN-99 */
select 1 from pay_user_tables
where substr(user_table_name,1,4) = 'ESSL'
and user_table_id = l_user_table_id;
SELECT criteria.pay_plan pay_plan,
criteria.pay_rate_determinant prd,
ext.increase_percent percent,
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 EQUIVALENT_PAY_PLAN
from ghr_pay_plans
where pay_plan = c_pay_plan;
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 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 = nvl(g_first_noa_code,'894') and g_effective_date between date_from and nvl(date_to,g_effective_date))
) and proc_method_flag = 'Y';
if check_select_flg_msl_perc(p_person_id,upper(p_action),
l_effective_date,p_mass_salary_id,l_sel_flg,
l_increase_percent ) 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
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 count(*) cnt
FROM ghr_mass_salary_criteria
WHERE mass_salary_id = p_mass_salary_id
AND pay_plan = cur_pay_plan
AND pay_rate_determinant = cur_prd;
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,
hr_organization_units hou
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 = hou.organization_id
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
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 =
(select nature_of_action_id
from ghr_nature_of_actions
where code = '890')
) and proc_method_flag = 'Y'; --AVR 13-JAN-99
select 1 from pay_user_tables
where substr(user_table_name,1,4) = 'ESSL'
and user_table_id = l_user_table_id;
SELECT pay_plan ,pay_rate_determinant prd
FROM ghr_mass_salary_criteria
WHERE mass_salary_id=p_msl_id;
select EQUIVALENT_PAY_PLAN
from ghr_pay_plans
where pay_plan = c_pay_plan;
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;
if check_select_flg_ses(p_person_id,upper(p_action),
l_effective_date,p_mass_salary_id,l_sel_flg,l_ses_basic_pay) 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
(p_person_id,l_effective_date, l_sel_flg, l_comment_sal,p_mass_salary_id);
g_proc := 'update_SEL_FLG';
update_SEL_FLG(p_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 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_information10 => NULL
,p_pei_information11 => to_char(p_ses_basic_pay)
,P_PEI_INFORMATION_CATEGORY => 'GHR_US_PER_MASS_ACTIONS');
function check_select_flg_ses(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,
p_ses_basic_pay in out nocopy number
)
return boolean IS
l_per_ei_data per_people_extra_info%rowtype;
l_proc varchar2(72) := g_package || '.check_select_flg_ses';
g_proc := 'check_select_flg_ses';
end check_select_flg_ses;