[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Procedure UPDATE_position_info
(p_position_data_rec ghr_sf52_pos_UPDATE.position_data_rec_type);
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
start with organization_id_parent = cp_org_pos_id
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_org_pos_id, b.organization_id)
and b.business_group_id = g_business_group_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_org_pos_id
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_org_pos_id
and b.business_group_id = g_business_group_id
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_pos_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.business_group_id = g_business_group_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_pos_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.business_group_id = g_business_group_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.business_group_id = g_business_group_id
and 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 org_pos_fl = 'O'
or
pop.position_id = nvl(p_org_pos_id,pop.position_id) and org_pos_fl = 'P')
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 = g_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 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.business_group_id = g_business_group_id
and pop.position_id in
(
SELECT position_id POSITION_ID
from hr_positions_f
WHERE (organization_id = nvl(p_org_pos_id,organization_id) and org_pos_fl = 'O'
or
position_id = nvl(p_org_pos_id,position_id) and org_pos_fl = 'P')
and trunc(effective_DATE)
between effective_start_DATE and effective_END_DATE
and business_group_id = g_business_group_id
MINUS
SELECT a.position_id
from per_people_f p, per_assignments_f a
WHERE (a.organization_id = nvl(p_org_pos_id,organization_id) and org_pos_fl = 'O'
or
a.position_id = nvl(p_org_pos_id,a.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 a.business_group_id = g_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
)
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,
new_organization_id,
status,
reason,
org_structure_id,
office_symbol,
agency_code_subelement agency_sub_elem_code,
personnel_office_id,
-- added this for 3191704
target_personnel_office_id,
old_org_structure_version_id old_organization_structure_id,
old_position_id,
old_pos_structure_version_id old_position_structure_id,
PA_REQUEST_ID,
business_group_id
from ghr_mass_realignment
WHERE mass_realignment_id = p_mass_realignment_id
for UPDATE of status nowait;
Select org_information4
from HR_ORGANIZATION_INFORMATION
WHERE organization_id = p_organization_id
and ORG_INFORMATION_CONTEXT = 'GHR_US_ORG_REPORTING_INFO';
SELECT range_or_match
FROM pay_user_tables
WHERE user_table_id = p_user_table_id;
hr_utility.set_message(8301, 'GHR_38567_SELECT_LAC_REMARKS');
if check_SELECT_flg(per.position_id,UPPER(p_action),
l_effective_DATE,
p_mass_realignment_id,
l_sel_flg) then
pr('After check sel flg value is '||l_sel_flg);
pr('Error in fetch/UPDATE of 6 lines of pos org'||
l_errbuf||' '||to_char(l_retcode));
END; -- End of Sub block of 6 lines of positions org UPDATE.
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_realignment_id,
l_sel_flg) then
pr('After check sel flg value is ',l_sel_flg,l_sel_status);
l_position_data_rec ghr_sf52_pos_UPDATE.position_data_rec_type;
not in ('Eliminated','Frozen','Deleted') ) THEN
IF check_SELECT_flg(per_vacant.position_id,UPPER(p_action),
l_effective_DATE,
p_mass_realignment_id,
l_sel_flg) then
l_new_position_id := per_vacant.position_id;
hr_utility.set_location('Vac Pos Selected ' || l_position_title,5);
g_proc := 'UpDATE Vacant Position';
ghr_position_extra_info_api.UPDATE_position_extra_info
(p_position_extra_info_id => l_new_pos_grp1_rec.position_extra_info_id
,p_effective_DATE => l_effective_DATE
,p_object_version_NUMBER => l_new_pos_grp1_rec.object_version_NUMBER
,p_poei_information3 => l_target_personnel_office_id
,p_poei_information4 => l_target_office_symbol
,p_poei_information18 => l_target_payroll_office_id
,p_poei_information21 => l_target_position_organization
,p_poei_information_category => 'GHR_US_POS_GRP1');
ghr_position_extra_info_api.UPDATE_position_extra_info
(p_position_extra_info_id => l_pos_ei_data_rec.position_extra_info_id
,p_effective_DATE => l_effective_DATE
,p_object_version_NUMBER => l_pos_ei_data_rec.object_version_NUMBER
,p_poei_information4 => l_target_org_func_code
,p_poei_information13 => l_target_appropriation_code1
,p_poei_information14 => l_target_appropriation_code2
,p_poei_information_category => 'GHR_US_POS_GRP2');
g_proc := 'ghr_sf52.UPDATE_position_info';
UPDATE_position_info (l_position_data_rec);
END IF; --- Select flag
l_mslerrbuf := 'Error in ghr_sf52_pos_UPDATE.UPDATE_position_info'||' Sql Err is '|| sqlerrm(sqlcode);
UPDATE ghr_mass_realignment
set status = 'P'
WHERE mass_realignment_id = p_mass_realignment_id;
HR_UTILITY.SET_LOCATION('Error in UpDATE ghr_mre Sql error '||sqlerrm(sqlcode),30);
UPDATE ghr_mass_realignment
set status = 'E'
WHERE mass_realignment_id = p_mass_realignment_id;
UPDATE ghr_mass_realignment
set status = 'E'
WHERE mass_realignment_id = p_mass_realignment_id;
UPDATE ghr_mass_realignment
set status = 'E'
WHERE mass_realignment_id = p_mass_realignment_id;
delete from ghr_mass_actions_preview
WHERE mass_action_type = 'REALIGNMENT'
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,
PAY_RATE_DETERMINANT
from ghr_pa_requests /*, per_organization_units B*/
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_realignment_id)
and first_noa_code = '790';
function check_SELECT_flg(p_position_id in NUMBER,
p_action in VARCHAR2,
p_effective_DATE in DATE,
p_mre_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';
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_INFORMATION3 => p_sel_flag
,p_poei_INFORMATION4 => p_comment
,p_poei_INFORMATION14 => to_char(p_mre_id)
,P_POEI_INFORMATION_CATEGORY => 'GHR_US_POS_MASS_ACTIONS');
hr_utility.set_location('UPDATE posei error 1' || l_proc,10);
SELECT NAME
FROM GHR_MASS_REALIGNMENT
WHERE MASS_REALIGNMENT_ID = p_mre_id;
delete from ghr_mass_actions_preview
WHERE mass_action_type = 'REALIGNMENT'
and session_id = userenv('sessionid');
procedure UPDATE_sel_flg (p_position_id in NUMBER,p_effective_DATE in DATE) is
l_position_extra_info_id NUMBER;
l_proc VARCHAR2(72) := 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_INFORMATION5 => NULL
,p_poei_INFORMATION6 => NULL
,P_POEI_INFORMATION_CATEGORY => 'GHR_US_POS_MASS_ACTIONS');
hr_utility.set_location('UPDATE posei error 2' || l_proc,10);
END UPDATE_sel_flg;
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 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 trunc(nvl(effective_DATE,sysDATE)) = trunc(p_effective_DATE)
and nvl(pr.second_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 nvl(action_taken,' ') action_taken
from ghr_pa_routing_history
WHERE pa_routing_history_id = p_r_hist_id;
SELECT LOOKUP_CODE,MEANING
FROM HR_LOOKUPS
WHERE LOOKUP_TYPE = p_type
AND ENABLED_FLAG = 'Y'
AND trunc(sysDATE)
BETWEEN NVL(START_DATE_ACTIVE,trunc(sysDATE))
AND NVL(END_DATE_ACTIVE,trunc(sysDATE))
AND LOOKUP_CODE = p_lookup_code;
SELECT pay_plan,description
from ghr_pay_plans
WHERE PAY_PLAN = PP;
SELECT description
from ghr_pois
WHERE PERSONNEL_OFFICE_ID = p_poi;
SELECT name, organization_id
from per_organization_units
WHERE internal_external_flag = 'INT'
and trunc(sysDATE) between DATE_from and nvl(DATE_to,trunc(sysDATE+1))
and organization_id = org_id;
pr('Inside ghr_cpdf_temp insert realign id ',to_char(p_mass_realignment_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,
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,
USER_ATTRIBUTE21,
USER_ATTRIBUTE22,
USER_ATTRIBUTE23,
USER_ATTRIBUTE24,
USER_ATTRIBUTE25,
USER_ATTRIBUTE26,
USER_ATTRIBUTE27,
USER_ATTRIBUTE28,
USER_ATTRIBUTE29,
USER_ATTRIBUTE30
)
values
(
'REALIGNMENT',
/*--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,
'790',
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,
decode(p_sel_flg,'N',NULL,t_personnel_office_id),
decode(p_sel_flg,'N',NULL,t_sub_element_code),
decode(p_sel_flg,'N',NULL,t_duty_station_id),
decode(p_sel_flg,'N',NULL,t_duty_station_code),
decode(p_sel_flg,'N',NULL,t_duty_station_desc),
decode(p_sel_flg,'N',NULL,t_office_symbol),
decode(p_sel_flg,'N',NULL,t_payroll_office_id),
decode(p_sel_flg,'N',NULL,t_org_func_code),
decode(p_sel_flg,'N',NULL,t_appropriation_code1),
decode(p_sel_flg,'N',NULL,t_appropriation_code2),
decode(p_sel_flg,'N',NULL,t_position_organization),
l_agency_sub_elem_desc,
decode(p_sel_flg,'N',NULL,t_sub_element_desc),
decode(p_sel_flg,'N',NULL,t_appropriation_code1_desc),
decode(p_sel_flg,'N',NULL,t_appropriation_code2_desc),
l_pay_plan_desc,
l_poi_desc,
decode(p_sel_flg,'N',NULL,t_poi_desc),
l_position_organization_name,
decode(p_sel_flg,'N',NULL,t_position_organization_name),
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,
l_cust_rec.user_attribute21,
l_cust_rec.user_attribute22,
l_cust_rec.user_attribute23,
l_cust_rec.user_attribute24,
l_cust_rec.user_attribute25,
l_cust_rec.user_attribute26,
l_cust_rec.user_attribute27,
l_cust_rec.user_attribute28,
l_cust_rec.user_attribute29,
l_cust_rec.user_attribute30
);
SELECT personnel_office_id,
agency_code_subelement,
duty_station_code,
duty_station_id target_duty_station_id,
LOCATION_ID target_duty_stn_locn_id,
office_symbol,
payroll_office_id,
org_function_code,
appropriation_code1,
appropriation_code2,
position_organization_id
from ghr_mass_real_pos_info_v
WHERE mass_realignment_id = p_mass_realignment_id;
SELECT position_extra_info_id,
POEI_INFORMATION5,
POEI_INFORMATION6,
POEI_INFORMATION7,
POEI_INFORMATION8,
POEI_INFORMATION9,
POEI_INFORMATION10,
POEI_INFORMATION11,
POEI_INFORMATION12,
POEI_INFORMATION13
POEI_INFORMATION18
from per_position_extra_info
WHERE position_id = (position)
and INFORMATION_TYPE = 'GHR_US_POS_MASS_ACTIONS';
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_INFORMATION3 => null
,P_POEI_INFORMATION4 => null
,P_POEI_INFORMATION5 => null
,P_POEI_INFORMATION6 => null
,P_POEI_INFORMATION7 => null
,P_POEI_INFORMATION8 => null
,P_POEI_INFORMATION9 => null
,P_POEI_INFORMATION10 => null
,P_POEI_INFORMATION11 => null
,P_POEI_INFORMATION12 => null
,P_POEI_INFORMATION13 => null
,P_POEI_INFORMATION14 => null
,P_POEI_INFORMATION18 => 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 = (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(l_eff_DATE)
,P_POEI_INFORMATION5 => info5
,P_POEI_INFORMATION6 => info6
,P_POEI_INFORMATION7 => info7
,P_POEI_INFORMATION8 => info8
,P_POEI_INFORMATION9 => info9
,P_POEI_INFORMATION10 => info10
,P_POEI_INFORMATION11 => info11
,P_POEI_INFORMATION12 => info12
,P_POEI_INFORMATION13 => info13
,P_POEI_INFORMATION18 => info18);
hr_utility.set_location('UPDATE posei error 4' || l_proc,10);
Procedure UPDATE_position_info
(p_position_data_rec ghr_sf52_pos_UPDATE.position_data_rec_type) is
l_proc VARCHAR2(30):='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);
ghr_history_api.post_UPDATE_process;