[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Procedure update_position_info
(p_position_data_rec in ghr_sf52_pos_update.position_data_rec_type);
SELECT * FROM ghr_pa_remarks
WHERE pa_request_id = p_pa_request_id;
SELECT remark_id
FROM ghr_remarks
WHERE code = 'M67'
AND enabled_flag = 'Y'
AND nvl(c_effective_date,trunc(sysdate))
BETWEEN date_from AND nvl(date_to,nvl(c_effective_date, trunc(sysdate)));
select a.organization_id_child org_pos_id
from per_org_structure_elements a,
per_org_structure_versions b
where a.org_structure_version_id = b.org_structure_version_id
and a.org_structure_version_id = org_str_id
and child_fl = 'Y'
and org_pos_fl = 'O'
and a.org_structure_element_id in
(
select org_structure_element_id
from per_org_structure_elements
-- VSM added nvl( .. to the start... clause
-- enhancement in selection criteria as org_id can be be null [Masscrit.doc]
start with organization_id_parent = cp_orgid
connect by prior organization_id_child = organization_id_parent
)
union
select b.ORGANIZATION_ID org_pos_id
from per_organization_units b
-- VSM added nvl( .. to the start... clause
-- enhancement in selection criteria as org_id can be be null [Masscrit.doc]
where b.organization_id = nvl(cp_orgid, b.organization_id)
and org_pos_fl = 'O'
union
select a.subordinate_position_id org_pos_id
from per_pos_structure_elements a,
per_pos_structure_versions b
where a.pos_structure_version_id = b.pos_structure_version_id
and a.pos_structure_version_id = org_str_id
and child_fl = 'Y'
and org_pos_fl = 'P'
and a.pos_structure_element_id in
(
select pos_structure_element_id
from per_pos_structure_elements
start with parent_position_id = cp_orgid
connect by prior subordinate_position_id = parent_position_id
)
union
select b.position_id org_pos_id
from hr_positions_f b
where b.position_id = cp_orgid
and org_pos_fl = 'P';
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,
punits.name ORGANIZATION_NAME
from per_assignments_f paf,
per_people_f ppf,
per_person_types ppt,
per_organization_units punits
-- VSM added nvl( .. to the start... clause
-- enhancement in selection criteria as org_id can be be null [Masscrit.doc]
where (paf.organization_id = nvl(p_org_id, paf.organization_id)
and
org_pos_fl = 'O')
and ppf.person_id = paf.person_id
and trunc(effective_date) between paf.effective_start_date
and paf.effective_end_date
and paf.primary_flag = 'Y'
and paf.assignment_type <> 'B'
and ppf.current_employee_flag = 'Y'
and trunc(effective_date) between ppf.effective_start_date
and ppf.effective_end_date
and ppf.person_type_id = ppt.person_type_id
and ppt.system_person_type IN ('EMP','EMP_APL')
and paf.organization_id = punits.organization_id
and paf.position_id is not null
union
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,
punits.name ORGANIZATION_NAME
from per_assignments_f paf,
per_people_f ppf,
per_person_types ppt,
per_organization_units punits
where (paf.position_id = nvl(p_org_id,paf.position_id)
and
org_pos_fl = 'P')
and ppf.person_id = paf.person_id
and trunc(effective_date) between paf.effective_start_date
and paf.effective_end_date
and paf.primary_flag = 'Y'
and paf.assignment_type <> 'B'
and ppf.current_employee_flag = 'Y'
and trunc(effective_date) between ppf.effective_start_date
and ppf.effective_end_date
and ppf.person_type_id = ppt.person_type_id
and ppt.system_person_type IN ('EMP','EMP_APL')
and paf.organization_id = punits.organization_id
and paf.position_id is not null;
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 pop.position_id in
(
select position_id POSITION_ID
from hr_positions_f
where (organization_id = nvl(p_org_id,organization_id) and org_pos_fl = 'O'
or position_id = nvl(p_org_id,position_id) and org_pos_fl = 'P')
and trunc(effective_date) between
effective_start_date and effective_end_date
MINUS
select a.position_id
from per_people_f p, per_assignments_f a
where (a.organization_id = nvl(p_org_id,organization_id) and org_pos_fl = 'O'
or a.position_id = nvl(p_org_id,position_id) and org_pos_fl = 'P')
and 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 p.person_id =a.person_id
and a.position_id = pop.position_id
and trunc(effective_date) between p.effective_start_date
and p.effective_end_date
)
and trunc(effective_date)
between pop.effective_start_date and pop.effective_end_date
and pop.organization_id = punits.organization_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 name, effective_date, old_organization_id,
OLD_ORG_STRUCTURE_VERSION_ID, status,
reason, org_structure_id, office_symbol,
AGENCY_CODE_SUBELEMENT,
PERSONNEL_OFFICE_ID, duty_station_code,duty_station_id,
old_position_id,
old_pos_structure_version_id,
TO_AGENCY_CODE_SUBELEMENT,
NVL(INTERFACE_FLAG,'N') INTERFACE_FLAG,
PA_REQUEST_ID
from ghr_mass_transfers
where mass_transfer_id = p_mass_transfer_id
and TRANSFER_TYPE = 'OUT'
for update of status nowait;
SELECT PA_REQUEST_EXTRA_INFO_ID,
OBJECT_VERSION_NUMBER
FROM GHR_PA_REQUEST_EXTRA_INFO
WHERE INFORMATION_TYPE = 'GHR_US_PAR_MASS_TERM'
and pa_request_id = p_pa_request_id;
hr_utility.set_message(8301, 'GHR_38567_SELECT_LAC_REMARKS');
' at select from mass tfr'||
' Sql err is '||sqlerrm(sqlcode),1);
if check_select_flg(per.position_id,upper(p_action),
l_effective_date,
p_mass_transfer_id,
l_sel_flg) then
begin
l_errbuf := null;
if check_select_flg(per.position_id,upper(p_action),
l_effective_date,
p_mass_transfer_id,
l_sel_flg) then
pr('After check sel flg value is ',l_sel_flg,l_sel_status);
ghr_par_extra_info_api.update_pa_request_extra_info
(p_validate => false,
p_rei_information3 => l_agency_code,
p_pa_request_extra_info_id => l_PA_REQUEST_EXTRA_INFO_ID,
p_object_version_number => l_pa_OBJECT_VERSION_NUMBER);
ELSE ------ Else for Check Select flag ----
l_ind := 260;
END IF; ---- End if for check select flag ----
not in ('Eliminated','Frozen','Deleted') ) THEN
l_position_id := per.position_id;
if check_select_flg(per.position_id,upper(p_action),
l_effective_date,
p_mass_transfer_id,
l_sel_flg) then
pr('After check sel flg value is ',l_sel_flg,l_sel_status);
End if; --- check for Eliminated, deleted and frozen positions
l_position_data_rec ghr_sf52_pos_update.position_data_rec_type;
-- Bug#4201666 Added the check to Restrict Eliminated, Frozen, Deleted Positions.
l_avail_status_id := per_vacant.availability_status_id;
not in ('Eliminated','Frozen','Deleted') ) THEN
IF check_select_flg(per_vacant.position_id,upper(p_action),
l_effective_date,
p_mass_transfer_id,
l_sel_flg) then
l_position_id := per_vacant.position_id;
hr_utility.set_location('Vac Pos Selected ' || l_position_title,5);
update_position_info
(l_position_data_rec);
END IF; -- Check select_flag
l_mass_errbuf := 'Error in ghr_sf52_pos_update.update_position_info'||' Sql Err is '|| sqlerrm(sqlcode);
update ghr_mass_transfers
set status = decode(upper(p_action),'CREATE','P',status),
interface_flag = decode(upper(p_action),'DUMP OUT','Y',
interface_flag)
where mass_transfer_id = p_mass_transfer_id;
HR_UTILITY.SET_LOCATION('Error in Update ghr_mto Sql error '||sqlerrm(sqlcode),30);
update ghr_mass_transfers
set status = decode(upper(p_action),'CREATE','P',status),
interface_flag = decode(upper(p_action),'DUMP OUT','Y',
interface_flag)
where mass_transfer_id = p_mass_transfer_id;
update ghr_mass_transfers
set status = 'E'
where mass_transfer_id = p_mass_transfer_id;
' in the Process Log or deselect the employees from the' ||
' Mass Transfer Out Preview before executing.';
update ghr_mass_transfers
set status = 'E'
where mass_transfer_id = p_mass_transfer_id;
update ghr_mass_transfers
set status = 'E'
where mass_transfer_id = p_mass_transfer_id;
delete from ghr_mass_actions_preview
where mass_action_type = 'TRANSFER'
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,
TO_POSITION_ID POSITION_ID,
TO_POSITION_TITLE POSITION_TITLE,
TO_POSITION_NUMBER POSITION_NUMBER,
TO_POSITION_SEQ_NO POSITION_SEQ_NO,
null org_structure_id,
FROM_AGENCY_CODE,
PERSON_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_OFFICE_SYMBOL,
from_pay_plan,
FROM_OCC_CODE,
TO_ORGANIZATION_ID ORGANIZATION_ID,
---B.NAME ORGANIZATION_NAME,
EMPLOYEE_ASSIGNMENT_ID
from ghr_pa_requests /**, per_organization_units B*/
where person_id = p_person_id
and effective_date = p_effective_date
and first_noa_code = '352'
-- Added by Dinkar for reports
and substr(request_number,(instr(request_number,'-')+1))
= to_char(p_mass_transfer_id);
function check_select_flg(p_position_id in number,
p_action in varchar2,
p_effective_date in date,
p_mtfr_id in number,
p_sel_flg in out NOCOPY varchar2)
return boolean IS
l_comments varchar2(150);
l_proc varchar2(72) := g_package || '.check_select_flg';
g_proc := 'check_select_flg';
delete from ghr_mass_actions_preview
where mass_action_type = 'TRANSFER'
and session_id = p_mass_transfer_id;
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_INFORMATION15 => p_sel_flag
,p_poei_INFORMATION16 => p_comment
,p_poei_INFORMATION17 => to_char(p_mtfr_id)
,P_POEI_INFORMATION_CATEGORY => 'GHR_US_POS_MASS_ACTIONS');
procedure update_sel_flg (p_position_id in number,p_effective_date in date) is
l_position_extra_info_id number;
:= g_package || '.update_sel_flg';
g_proc := 'update_sel_flg';
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_INFORMATION15 => NULL
,p_poei_INFORMATION16 => NULL
,P_POEI_INFORMATION_CATEGORY => 'GHR_US_POS_MASS_ACTIONS');
end update_sel_flg;
select occ_code
from ghr_mass_transfer_criteria
where MASS_TRANSFER_ID = tfr_id
and occ_code = p_occ_series;
select count(*) COUNT
from ghr_mass_transfer_criteria
where MASS_TRANSFER_ID = tfr_id;
pr('Inside ghr_cpdf_temp insert Transfer id ',to_char(p_mass_transfer_id),null);
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,
position_id,
position_title,
position_number,
position_seq_no,
org_structure_id,
agency_code,
person_id,
select_flag,
first_noa_code,
grade_or_level,
step_or_rate,
pay_plan,
office_symbol,
organization_id,
organization_name,
occ_code,
positions_organization,
to_personnel_office_id,
to_agency_code,
to_duty_station_id,
to_duty_station_code,
to_duty_station_desc,
to_office_symbol,
to_payroll_office_id,
to_org_func_code,
to_appropriation_code1,
to_appropriation_code2,
to_positions_organization,
AGENCY_DESC,
TO_AGENCY_DESC,
TO_APPROPRIATION_CODE1_DESC,
TO_APPROPRIATION_CODE2_DESC,
PAY_PLAN_DESC,
POI_DESC,
TO_POI_DESC,
POSITIONS_ORGANIZATION_NAME,
TO_POSITIONS_ORG_NAME,
TENURE,
PAY_RATE_DETERMINANT,
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
(
'TRANSFER',
/*--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_position_id,
p_position_title,
p_position_number,
to_number(p_position_seq_no),
p_org_structure_id,
p_agency_sub_element_code,
p_person_id,
p_sel_flg,
'352',
p_grade_or_level,
l_step_or_rate,
p_pay_plan,
p_office_symbol,
p_organization_id,
p_organization_name,
p_occ_series,
p_positions_organization,
t_personnel_office_id,
decode(p_sel_flg,'N',NULL,l_to_agency_code), --- t_sub_element_code,
t_duty_station_id,
t_duty_station_code,
t_duty_station_desc,
t_office_symbol,
t_payroll_office_id,
t_org_func_code,
t_appropriation_code1,
t_appropriation_code2,
t_position_organization,
l_agency_sub_elem_desc,
decode(p_sel_flg,'N',NULL,t_sub_element_desc),
t_appropriation_code1_desc,
t_appropriation_code2_desc,
l_pay_plan_desc,
l_poi_desc,
t_poi_desc,
l_position_organization_name,
t_position_organization_name,
p_tenure,
p_pay_rate_determinant,
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 NAME
FROM GHR_MASS_TRANSFERS
WHERE MASS_TRANSFER_ID = p_mto_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 => trunc(sysdate)
,P_POEI_INFORMATION15 => null
,P_POEI_INFORMATION16 => null
,P_POEI_INFORMATION17 => null);
select person_extra_info_id, object_version_number
from per_people_extra_info
where person_id = (p_person)
and INFORMATION_TYPE = 'GHR_US_PER_MASS_ACTIONS';
pr('Bef update pers ext info');
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_information_category => 'GHR_US_PER_MASS_ACTIONS'
,P_PEI_INFORMATION9 => p_agency_code);
Procedure update_position_info
(p_position_data_rec in ghr_sf52_pos_update.position_data_rec_type) is
l_proc varchar2(30):='update_position_info';
g_proc := 'update_position_info';
ghr_sf52_pos_update.update_position_info
( p_pos_data_rec => p_position_data_rec);
hr_utility.set_location('Calling Pust_update_process ' || l_proc, 50);
g_proc := 'post_update_process';
ghr_history_api.post_update_process;
select person_extra_info_id, object_version_number
from per_people_extra_info
where person_id = (p_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_OBJECT_VERSION_NUMBER => l_object_version_number
,P_PEI_INFORMATION_CATEGORY => 'GHR_US_PER_MASS_ACTIONS'
,P_EFFECTIVE_DATE => trunc(sysdate)
,P_PEI_INFORMATION9 => null
);