The following lines contain the word 'select', 'insert', 'update' or 'delete':
,spp_delete_warning boolean
,entries_changed_warning varchar2(50)
,tax_district_changed_warning boolean
,concatenated_segments hr_soft_coding_keyflex.concatenated_segments%type
,gsp_post_process_warning varchar2(2000)
,comment_id per_all_assignments_f.comment_id%type
);
select bbg.name
from ben_benfts_grp bbg
where bbg.benfts_grp_id = c_benefit_group_id
and bbg.business_group_id + 0 = c_business_group_id;
select pbg.legislation_code
,pbg.method_of_generation_emp_num
,pbg.method_of_generation_apl_num
,pbg.method_of_generation_cwk_num
,pbg.business_group_id
from per_business_groups pbg
where pbg.business_group_id = c_business_group_id;
select hrl.meaning
,hrl.lookup_code
from hr_lookups hrl
where hrl.lookup_type = c_lookup_type
and hrl.lookup_code = c_lookup_code
and hrl.enabled_flag = 'Y'
and trunc(c_effective_date)
between nvl(hrl.start_date_active, trunc(c_effective_date))
and nvl(hrl.end_date_active, trunc(c_effective_date));
select dfc.descriptive_flex_context_code
from fnd_descr_flex_contexts dfc
where dfc.application_id = 800
and dfc.descriptive_flexfield_name = 'Person Developer DF'
and dfc.enabled_flag = 'Y';
select *
from per_all_people_f ppf
where ppf.person_id = c_person_id
and ppf.business_group_id = c_business_group_id
and c_effective_date between ppf.effective_start_date
and ppf.effective_end_date;
select ppt.system_person_type
,ppf.effective_start_date
,ppf.effective_end_date
,ppf.employee_number
,ppt.person_type_id
,ppf.person_id
from per_all_people_f ppf
,per_person_types ppt
where ppt.person_type_id = ppf.person_type_id
and ppf.business_group_id = c_bg_grp_id
and ppt.business_group_id = ppf.business_group_id
and ppt.active_flag = 'Y'
and ((c_person_id is not null and ppf.person_id = c_person_id) or
(c_party_id is not null and ppf.party_id = c_party_id))
and c_effective_date between ppf.effective_start_date
and ppf.effective_end_date;
select ptu.person_id
,ptu.person_type_id
,ppt.active_flag
,ppt.system_person_type
,ppt.user_person_type
from per_person_type_usages_f ptu
,per_person_types ppt
where ptu.person_id = c_person_id
and ppt.business_group_id = c_business_group_id
and ppt.person_type_id = ptu.person_type_id
and c_effective_date between ptu.effective_start_date
and ptu.effective_end_date
and ppt.system_person_type
in ('EMP','EMP_APL','EX_EMP',
'APL','APL_EX_APL','EX_APL','EX_EMP_APL',
'CWK','EX_CWK')
order by ptu.effective_start_date desc;
select ptu.person_type_id
,ppt.system_person_type
,ppt.user_person_type
from per_person_type_usages_f ptu
,per_person_types ppt
where ptu.person_id = c_person_id
and ppt.person_type_id = ptu.person_type_id
and ppt.business_group_id = c_business_group_id
and ptu.effective_start_date > c_effective_date
and ppt.system_person_type in
('EMP' ,'CWK' ,'APL' ,'EMP_APL',
'EX_APL','EX_CWK' ,'EX_EMP_APL',
'OTHER' ,'APL_EX_APL','EX_EMP'
);
select asg.assignment_id
,asg.object_version_number
,asg.vacancy_id
from per_all_assignments_f asg
,per_assignment_status_types ast
where asg.assignment_status_type_id = ast.assignment_status_type_id
and asg.person_id = c_person_id
and asg.business_group_id = c_business_group_id
and (c_assignment_id is null or
asg.assignment_id = c_assignment_id)
and c_effective_date between asg.effective_start_date
and asg.effective_end_date
and asg.assignment_type = 'A'
and ast.per_system_status = 'ACCEPTED';
select asg.assignment_id
,asg.object_version_number
,asg.vacancy_id
from per_all_assignments_f asg
,per_assignment_status_types ast
where asg.assignment_status_type_id = ast.assignment_status_type_id
and asg.person_id = c_person_id
and asg.business_group_id = c_business_group_id
and (c_assignment_id is null or
asg.assignment_id = c_assignment_id)
and c_effective_date between asg.effective_start_date
and asg.effective_end_date
and asg.assignment_type = 'A'
and ast.per_system_status <> 'ACCEPTED';
select assignment_status_type_id
,active_flag
,per_system_status
from per_assignment_status_types
where per_system_status = 'ACCEPTED'
and (business_group_id = c_business_group_id
or legislation_code = c_leg_code
or (legislation_code is null
and business_group_id is null)
)
and default_flag = 'Y'
and active_flag = 'Y';
select ppt.user_person_type
from per_person_types ppt
where ppt.person_type_id = c_person_type_id
and ppt.business_group_id = c_business_group_id;
select gtl.name
from per_grades pgr
,per_grades_tl gtl
where pgr.grade_id = c_grade_id
and gtl.grade_id = pgr.grade_id
and gtl.language = userenv('LANG')
and pgr.business_group_id = c_business_group_id
and c_effective_date between pgr.date_from
and nvl(pgr.date_to,c_effective_date);
select ptl.name
from hr_all_positions_f pos
,hr_all_positions_f_tl ptl
where pos.position_id = c_position_id
and ptl.position_id = pos.position_id
and ptl.language = userenv('LANG')
and pos.business_group_id = c_business_group_id
and c_effective_date between pos.effective_start_date
and pos.effective_end_date;
select jtl.name
from per_jobs pjb
,per_jobs_tl jtl
where pjb.job_id = c_job_id
and jtl.job_id = pjb.job_id
and jtl.language = Userenv('LANG')
and pjb.business_group_id = c_business_group_id
and c_effective_date between pjb.date_from
and NVL(pjb.date_to,c_effective_date);
select payroll_name
from pay_payrolls_f ppf
where ppf.payroll_id = c_payroll_id
and ppf.business_group_id = c_business_group_id
and c_effective_date between ppf.effective_start_date
and ppf.effective_end_date;
select htl.location_code
from hr_locations hrl
,hr_locations_all_tl htl
where hrl.location_id = c_location_id
and htl.location_id = hrl.location_id
and htl.language = Userenv('LANG')
and (hrl.business_group_id is null or
hrl.business_group_id = c_business_group_id);
select htl.name
from hr_all_organization_units_tl htl
,hr_all_organization_units hao
where hao.organization_id = c_organization_id
and hao.business_group_id = c_business_group_id
and htl.organization_id = hao.organization_id
and htl.language = Userenv('LANG')
and c_effective_date between hao.date_from
and NVL(hao.date_to,c_effective_date);
select ppb.name
from per_pay_bases ppb
where ppb.pay_basis_id = c_pay_basis_id
and ppb.business_group_id = c_business_group_id;
select *
from per_addresses
where person_id = c_person_id
and business_group_id = c_business_group_id
and primary_flag = c_primary_flag
and c_effective_date between date_from
and NVL(date_to, c_effective_date);
select object_version_number
from per_contact_relationships
where person_id = c_person_id
and business_group_id = c_business_group_id
and c_effective_date between date_start
and NVL(date_end, c_effective_date);
select employee_number
from per_all_people_f
where person_id = c_person_id;
select lower(bic.interface_col_name) interface_col_name
from bne_interface_cols_b bic
where bic.interface_code = c_interface_code
and bic.display_flag ='Y';
select lower(bic.interface_col_name) interface_col_name
from bne_interface_cols_b bic
where bic.interface_code = c_interface_code
and bic.display_flag ='N';
when 'p_resume_last_updated' then
l_per_rec.resume_last_updated := g_per_rec.resume_last_updated;
select lower(bic.interface_col_name) interface_col_name
from bne_interface_cols_b bic
where bic.interface_code = c_interface_code
and bic.display_flag ='Y';
select lower(bic.interface_col_name) interface_col_name
from bne_interface_cols_b bic
where bic.interface_code = c_interface_code
and bic.display_flag ='N';
select lower(bic.interface_col_name) interface_col_name
from bne_interface_cols_b bic
where bic.interface_code = c_interface_code
and bic.group_name = 'PEOPLE_KEYFLEX_GROUP'
and bic.display_flag ='Y';
select lower(bic.interface_col_name) interface_col_name
from bne_interface_cols_b bic
where bic.interface_code = c_interface_code
and bic.SEQUENCE_NUM between 222 and 252 --$ All soft coded KFF segments
-- and (bic.group_name = 'SOFT_KEYFLEX_GROUP' or
-- lower(bic.interface_col_name) ='p_gre');
and bic.display_flag ='Y'; --Select only those segments that the user has selected while function creation
' select p_national_identifier
from hrdpv_create_employee
where batch_id = ' || p_batch_id ||
' and link_value = ' || p_data_pump_batch_line_id ||
' union
select p_national_identifier
from hrdpv_update_person
where batch_id = ' || p_batch_id ||
' and link_value = ' || p_data_pump_batch_line_id;
select ppf.person_id
,ppf.business_group_id
,ppf.employee_number
,ppf.applicant_number
,ppf.npw_number
,ppf.party_id
from per_all_people_f ppf
where (ppf.date_of_birth = g_per_rec.date_of_birth
or ppf.date_of_birth is null
or g_per_rec.date_of_birth is null)
and upper(ppf.last_name) = upper(g_per_rec.last_name)
and (ppf.sex = g_per_rec.sex
or ppf.sex is null
or g_per_rec.sex is null)
and ppf.business_group_id = c_business_group_id
and c_effective_date between ppf.effective_start_date
and ppf.effective_end_date;
select ppf.person_id
,ppf.business_group_id
,ppf.employee_number
,ppf.applicant_number
,ppf.npw_number
,ppf.party_id
from per_all_people_f ppf
where ppf.date_of_birth = g_per_rec.date_of_birth
and upper(ppf.last_name) = upper(g_per_rec.last_name)
and ppf.sex = g_per_rec.sex
and ppf.business_group_id = c_business_group_id
and c_effective_date between ppf.effective_start_date
and ppf.effective_end_date;
select ppf.person_id
,ppf.business_group_id
,ppf.employee_number
,ppf.applicant_number
,ppf.npw_number
,ppf.party_id
from per_all_people_f ppf
where ppf.national_identifier = c_national_identifier
and ppf.business_group_id = c_business_group_id
and c_effective_date between ppf.effective_start_date
and ppf.effective_end_date;
(p_datetrack_update_mode out nocopy varchar2
) is
l_cur_per_rec csr_per%rowtype;
l_datetrack_update_mode varchar2(50);
l_dt_update boolean;
,p_update => l_dt_update
,p_update_override => l_dt_upd_override
,p_update_change_insert => l_upd_chg_ins
);
if l_dt_update then
l_datetrack_update_mode := 'UPDATE';
l_datetrack_update_mode := 'CORRECTION';
l_datetrack_update_mode := 'CORRECTION';
hr_utility.set_location('l_datetrack_update_mode: ' ||
l_datetrack_update_mode, 30);
'This person cannot be created/updated in HRMS as the '||
'Person has future changes beyond the date: '||g_per_rec.start_date;
hr_utility.set_location('..Future Update exists for the Student Employee', 90);
select *
from per_person_types
where person_type_id = c_person_type_id
and business_group_id = p_business_group_id;
select ppt.system_person_type
,ppt.user_person_type
,ppf.effective_start_date
,ppf.effective_end_date
,ppf.employee_number
,ppf.applicant_number
,ppf.npw_number
,ptu.person_type_id
,ppf.person_id
,ppf.object_version_number
from per_all_people_f ppf
,per_person_type_usages_f ptu
,per_person_types ppt
where ptu.person_type_id = ppf.person_type_id
and ppt.person_type_id = ptu.person_type_id
and ppt.business_group_id = ppf.business_group_id
and ppf.business_group_id = c_bg_grp_id
and ((c_person_id is not null and ppf.person_id = c_person_id) or
(c_party_id is not null and ppf.party_id = c_party_id))
and c_effective_date between ppf.effective_start_date
and ppf.effective_end_date
and c_effective_date between ptu.effective_start_date
and ptu.effective_end_date;
'Invalid person type passed. Please select a valid person type.';
select *
from per_all_assignments_f paf
where paf.assignment_id = c_assignment_id
and paf.business_group_id = c_business_group_id
and c_effective_date between paf.effective_start_date
and paf.effective_end_date;
select *
from pay_people_groups
where people_group_id = c_people_grp_id;
select *
from hr_soft_coding_keyflex
where soft_coding_keyflex_id = c_scl_kff_id;
l_datetrack_update_mode varchar2(50);
l_dt_update boolean;
,p_update => l_dt_update
,p_update_override => l_dt_upd_override
,p_update_change_insert => l_upd_chg_ins
);
if l_dt_update then
l_datetrack_update_mode := 'UPDATE';
l_datetrack_update_mode := 'CORRECTION';
l_datetrack_update_mode := 'CORRECTION';
hr_utility.set_location(' l_datetrack_update_mode: ' ||
l_datetrack_update_mode, 60);
Select PER_SYSTEM_STATUS into l_expected_system_status
from per_assignment_status_types
where assignment_status_type_id = l_asg_rec.assignment_status_type_id;
hr_assignment_swi.update_apl_asg
(p_effective_date => p_effective_date
,p_datetrack_update_mode => l_datetrack_update_mode
,p_assignment_id => g_asg_rec.assignment_id
,p_object_version_number => l_cur_asg_rec.object_version_number
,p_recruiter_id => l_cur_asg_rec.recruiter_id
,p_grade_id => l_asg_rec.grade_id
,p_position_id => l_asg_rec.position_id
,p_job_id => l_asg_rec.job_id
,p_payroll_id => l_asg_rec.payroll_id
,p_location_id => l_asg_rec.location_id
,p_organization_id => l_asg_rec.organization_id
,p_assignment_status_type_id => l_asg_rec.assignment_status_type_id
,p_person_referred_by_id => l_cur_asg_rec.person_referred_by_id
,p_supervisor_id => l_asg_rec.supervisor_id
,p_special_ceiling_step_id => l_asg_rec.special_ceiling_step_id
,p_source_organization_id => l_asg_rec.source_organization_id
,p_recruitment_activity_id => l_cur_asg_rec.recruitment_activity_id
,p_vacancy_id => l_cur_asg_rec.vacancy_id
,p_pay_basis_id => l_asg_rec.pay_basis_id
,p_application_id => l_cur_asg_rec.application_id
,p_change_reason => l_asg_rec.change_reason
,p_comments => g_asg_comments
,p_date_probation_end => l_asg_rec.date_probation_end
,p_default_code_comb_id => l_asg_rec.default_code_comb_id
,p_employment_category => l_asg_rec.employment_category
,p_frequency => l_asg_rec.frequency
,p_normal_hours => l_asg_rec.normal_hours
,p_internal_address_line => l_asg_rec.internal_address_line
,p_manager_flag => l_asg_rec.manager_flag
,p_probation_period => l_asg_rec.probation_period
,p_probation_unit => l_asg_rec.probation_unit
,p_perf_review_period => l_asg_rec.perf_review_period
,p_perf_review_period_frequency => l_asg_rec.perf_review_period_frequency
,p_sal_review_period => l_asg_rec.sal_review_period
,p_sal_review_period_frequency => l_asg_rec.sal_review_period_frequency
,p_set_of_books_id => l_asg_rec.set_of_books_id
,p_source_type => l_asg_rec.source_type
,p_bargaining_unit_code => l_asg_rec.bargaining_unit_code
,p_time_normal_finish => l_asg_rec.time_normal_finish
,p_time_normal_start => l_asg_rec.time_normal_start
,p_ass_attribute_category => l_asg_rec.ass_attribute_category
,p_ass_attribute1 => l_asg_rec.ass_attribute1
,p_ass_attribute2 => l_asg_rec.ass_attribute2
,p_ass_attribute3 => l_asg_rec.ass_attribute3
,p_ass_attribute4 => l_asg_rec.ass_attribute4
,p_ass_attribute5 => l_asg_rec.ass_attribute5
,p_ass_attribute6 => l_asg_rec.ass_attribute6
,p_ass_attribute7 => l_asg_rec.ass_attribute7
,p_ass_attribute8 => l_asg_rec.ass_attribute8
,p_ass_attribute9 => l_asg_rec.ass_attribute9
,p_ass_attribute10 => l_asg_rec.ass_attribute10
,p_ass_attribute11 => l_asg_rec.ass_attribute11
,p_ass_attribute12 => l_asg_rec.ass_attribute12
,p_ass_attribute13 => l_asg_rec.ass_attribute13
,p_ass_attribute14 => l_asg_rec.ass_attribute14
,p_ass_attribute15 => l_asg_rec.ass_attribute15
,p_ass_attribute16 => l_asg_rec.ass_attribute16
,p_ass_attribute17 => l_asg_rec.ass_attribute17
,p_ass_attribute18 => l_asg_rec.ass_attribute18
,p_ass_attribute19 => l_asg_rec.ass_attribute19
,p_ass_attribute20 => l_asg_rec.ass_attribute20
,p_ass_attribute21 => l_asg_rec.ass_attribute21
,p_ass_attribute22 => l_asg_rec.ass_attribute22
,p_ass_attribute23 => l_asg_rec.ass_attribute23
,p_ass_attribute24 => l_asg_rec.ass_attribute24
,p_ass_attribute25 => l_asg_rec.ass_attribute25
,p_ass_attribute26 => l_asg_rec.ass_attribute26
,p_ass_attribute27 => l_asg_rec.ass_attribute27
,p_ass_attribute28 => l_asg_rec.ass_attribute28
,p_ass_attribute29 => l_asg_rec.ass_attribute29
,p_ass_attribute30 => l_asg_rec.ass_attribute30
,p_title => l_asg_rec.title
,p_concatenated_segments => l_UpdEmpAsg_out.concatenated_segments
,p_contract_id => l_asg_rec.contract_id
,p_establishment_id => l_asg_rec.establishment_id
,p_job_post_source_name => l_asg_rec.job_post_source_name
,p_posting_content_id => l_asg_rec.posting_content_id
,p_applicant_rank => l_asg_rec.applicant_rank
,p_cagr_grade_def_id => g_asg_rec.cagr_grade_def_id
--,p_collective_agreement_id in number default hr_api.g_number
,p_notice_period => l_asg_rec.notice_period
,p_notice_period_uom => l_asg_rec.notice_period_uom
,p_employee_category => l_asg_rec.employee_category
,p_work_at_home => l_asg_rec.work_at_home
,p_cagr_concatenated_segments => l_UpdEmpAsg_out.cagr_concatenated_segments
,p_group_name => l_UpdEmpAsg_out.concatenated_segments
,p_comment_id => l_UpdEmpAsg_out.comment_id
,p_effective_start_date => l_UpdEmpAsg_out.effective_start_date
,p_effective_end_date => l_UpdEmpAsg_out.effective_end_date
,p_people_group_id => g_asg_rec.people_group_id
,p_soft_coding_keyflex_id => g_asg_rec.soft_coding_keyflex_id
,p_return_status => l_return_status
);
hr_utility.set_location('Inside Update Assignment :'||l_asg_rec.assignment_status_type_id, 8);
hr_assignment_internal.update_status_type_apl_asg
(p_effective_date => p_effective_date
,p_datetrack_update_mode => l_datetrack_update_mode
,p_assignment_id => g_asg_rec.assignment_id
,p_object_version_number => l_cur_asg_rec.object_version_number
,p_expected_system_status => l_expected_system_status
,p_assignment_status_type_id => l_asg_rec.assignment_status_type_id
,p_change_reason => l_asg_rec.change_reason
,p_effective_start_date => l_UpdEmpAsg_out.effective_start_date
,p_effective_end_date => l_UpdEmpAsg_out.effective_end_date
);
,p_update => l_dt_update
,p_update_override => l_dt_upd_override
,p_update_change_insert => l_upd_chg_ins
);
if l_dt_update then
l_datetrack_update_mode := 'UPDATE';
l_datetrack_update_mode := 'CORRECTION';
l_datetrack_update_mode := 'CORRECTION';
HR_Assignment_API.Update_APL_Asg
(p_validate => false
,p_effective_date => p_effective_date
,p_datetrack_update_mode => l_datetrack_update_mode
,p_assignment_id => g_asg_rec.assignment_id
,p_recruiter_id => l_cur_asg_rec.recruiter_id
,p_recruitment_activity_id => l_cur_asg_rec.recruitment_activity_id
,p_person_referred_by_id => l_cur_asg_rec.person_referred_by_id
,p_vacancy_id => l_cur_asg_rec.vacancy_id
,p_application_id => l_cur_asg_rec.application_id
,p_grade_id => l_asg_rec.grade_id
,p_position_id => l_asg_rec.position_id
,p_job_id => l_asg_rec.job_id
,p_payroll_id => l_asg_rec.payroll_id
,p_location_id => l_asg_rec.location_id
,p_organization_id => l_asg_rec.organization_id
,p_pay_basis_id => l_asg_rec.pay_basis_id
,p_assignment_status_type_id => l_asg_rec.assignment_status_type_id
,p_supervisor_id => l_asg_rec.supervisor_id
,p_special_ceiling_step_id => l_asg_rec.special_ceiling_step_id
,p_source_organization_id => l_asg_rec.source_organization_id
,p_employment_category => l_asg_rec.employment_category
,p_frequency => l_asg_rec.frequency
,p_normal_hours => l_asg_rec.normal_hours
,p_time_normal_finish => l_asg_rec.time_normal_finish
,p_time_normal_start => l_asg_rec.time_normal_start
,p_probation_period => l_asg_rec.probation_period
,p_probation_unit => l_asg_rec.probation_unit
,p_perf_review_period => l_asg_rec.perf_review_period
,p_perf_review_period_frequency => l_asg_rec.perf_review_period_frequency
,p_sal_review_period => l_asg_rec.sal_review_period
,p_sal_review_period_frequency => l_asg_rec.sal_review_period_frequency
,p_change_reason => l_asg_rec.change_reason
,p_notice_period => l_asg_rec.notice_period
,p_notice_period_uom => l_asg_rec.notice_period_uom
,p_employee_category => l_asg_rec.employee_category
,p_work_at_home => l_asg_rec.work_at_home
--$
,p_comments => g_asg_comments
,p_date_probation_end => l_asg_rec.date_probation_end
,p_default_code_comb_id => l_asg_rec.default_code_comb_id
,p_internal_address_line => l_asg_rec.internal_address_line
,p_manager_flag => l_asg_rec.manager_flag
,p_set_of_books_id => l_asg_rec.set_of_books_id
,p_source_type => l_asg_rec.source_type
,p_bargaining_unit_code => l_asg_rec.bargaining_unit_code
-- Asg DF
,p_ass_attribute_category => l_asg_rec.ass_attribute_category
,p_ass_attribute1 => l_asg_rec.ass_attribute1
,p_ass_attribute2 => l_asg_rec.ass_attribute2
,p_ass_attribute3 => l_asg_rec.ass_attribute3
,p_ass_attribute4 => l_asg_rec.ass_attribute4
,p_ass_attribute5 => l_asg_rec.ass_attribute5
,p_ass_attribute6 => l_asg_rec.ass_attribute6
,p_ass_attribute7 => l_asg_rec.ass_attribute7
,p_ass_attribute8 => l_asg_rec.ass_attribute8
,p_ass_attribute9 => l_asg_rec.ass_attribute9
,p_ass_attribute10 => l_asg_rec.ass_attribute10
,p_ass_attribute11 => l_asg_rec.ass_attribute11
,p_ass_attribute12 => l_asg_rec.ass_attribute12
,p_ass_attribute13 => l_asg_rec.ass_attribute13
,p_ass_attribute14 => l_asg_rec.ass_attribute14
,p_ass_attribute15 => l_asg_rec.ass_attribute15
,p_ass_attribute16 => l_asg_rec.ass_attribute16
,p_ass_attribute17 => l_asg_rec.ass_attribute17
,p_ass_attribute18 => l_asg_rec.ass_attribute18
,p_ass_attribute19 => l_asg_rec.ass_attribute19
,p_ass_attribute20 => l_asg_rec.ass_attribute20
,p_ass_attribute21 => l_asg_rec.ass_attribute21
,p_ass_attribute22 => l_asg_rec.ass_attribute22
,p_ass_attribute23 => l_asg_rec.ass_attribute23
,p_ass_attribute24 => l_asg_rec.ass_attribute24
,p_ass_attribute25 => l_asg_rec.ass_attribute25
,p_ass_attribute26 => l_asg_rec.ass_attribute26
,p_ass_attribute27 => l_asg_rec.ass_attribute27
,p_ass_attribute28 => l_asg_rec.ass_attribute28
,p_ass_attribute29 => l_asg_rec.ass_attribute29
,p_ass_attribute30 => l_asg_rec.ass_attribute30
,p_title => l_asg_rec.title
-- Asg Soft Coding KFF
,p_scl_segment1 => l_scl_rec.segment1
,p_scl_segment2 => l_scl_rec.segment2
,p_scl_segment3 => l_scl_rec.segment3
,p_scl_segment4 => l_scl_rec.segment4
,p_scl_segment5 => l_scl_rec.segment5
,p_scl_segment6 => l_scl_rec.segment6
,p_scl_segment7 => l_scl_rec.segment7
,p_scl_segment8 => l_scl_rec.segment8
,p_scl_segment9 => l_scl_rec.segment9
,p_scl_segment10 => l_scl_rec.segment10
,p_scl_segment11 => l_scl_rec.segment11
,p_scl_segment12 => l_scl_rec.segment12
,p_scl_segment13 => l_scl_rec.segment13
,p_scl_segment14 => l_scl_rec.segment14
,p_scl_segment15 => l_scl_rec.segment15
,p_scl_segment16 => l_scl_rec.segment16
,p_scl_segment17 => l_scl_rec.segment17
,p_scl_segment18 => l_scl_rec.segment18
,p_scl_segment19 => l_scl_rec.segment19
,p_scl_segment20 => l_scl_rec.segment20
,p_scl_segment21 => l_scl_rec.segment21
,p_scl_segment22 => l_scl_rec.segment22
,p_scl_segment23 => l_scl_rec.segment23
,p_scl_segment24 => l_scl_rec.segment24
,p_scl_segment25 => l_scl_rec.segment25
,p_scl_segment26 => l_scl_rec.segment26
,p_scl_segment27 => l_scl_rec.segment27
,p_scl_segment28 => l_scl_rec.segment28
,p_scl_segment29 => l_scl_rec.segment29
,p_scl_segment30 => l_scl_rec.segment30
--,p_scl_concat_segments => g_scl_rec.
-- People Group KFF
,p_pgp_segment1 => l_grp_rec.segment1
,p_pgp_segment2 => l_grp_rec.segment2
,p_pgp_segment3 => l_grp_rec.segment3
,p_pgp_segment4 => l_grp_rec.segment4
,p_pgp_segment5 => l_grp_rec.segment5
,p_pgp_segment6 => l_grp_rec.segment6
,p_pgp_segment7 => l_grp_rec.segment7
,p_pgp_segment8 => l_grp_rec.segment8
,p_pgp_segment9 => l_grp_rec.segment9
,p_pgp_segment10 => l_grp_rec.segment10
,p_pgp_segment11 => l_grp_rec.segment11
,p_pgp_segment12 => l_grp_rec.segment12
,p_pgp_segment13 => l_grp_rec.segment13
,p_pgp_segment14 => l_grp_rec.segment14
,p_pgp_segment15 => l_grp_rec.segment15
,p_pgp_segment16 => l_grp_rec.segment16
,p_pgp_segment17 => l_grp_rec.segment17
,p_pgp_segment18 => l_grp_rec.segment18
,p_pgp_segment19 => l_grp_rec.segment19
,p_pgp_segment20 => l_grp_rec.segment20
,p_pgp_segment21 => l_grp_rec.segment21
,p_pgp_segment22 => l_grp_rec.segment22
,p_pgp_segment23 => l_grp_rec.segment23
,p_pgp_segment24 => l_grp_rec.segment24
,p_pgp_segment25 => l_grp_rec.segment25
,p_pgp_segment26 => l_grp_rec.segment26
,p_pgp_segment27 => l_grp_rec.segment27
,p_pgp_segment28 => l_grp_rec.segment28
,p_pgp_segment29 => l_grp_rec.segment29
,p_pgp_segment30 => l_grp_rec.segment30
,p_concat_segments => l_grp_rec.group_name
,p_contract_id => l_asg_rec.contract_id
,p_establishment_id => l_asg_rec.establishment_id
,p_job_post_source_name => l_asg_rec.job_post_source_name
,p_posting_content_id => l_asg_rec.posting_content_id
,p_applicant_rank => l_asg_rec.applicant_rank
,p_grade_ladder_pgm_id => l_asg_rec.grade_ladder_pgm_id
,p_supervisor_assignment_id => l_asg_rec.supervisor_assignment_id
-- In/Out
,p_cagr_grade_def_id => g_asg_rec.cagr_grade_def_id
,p_people_group_id => g_asg_rec.people_group_id
,p_soft_coding_keyflex_id => g_asg_rec.soft_coding_keyflex_id
,p_object_version_number => l_cur_asg_rec.object_version_number
-- Out
,p_concatenated_segments => l_UpdEmpAsg_out.concatenated_segments
,p_cagr_concatenated_segments => l_UpdEmpAsg_out.cagr_concatenated_segments
,p_group_name => l_UpdEmpAsg_out.concatenated_segments
,p_comment_id => l_UpdEmpAsg_out.comment_id
,p_effective_start_date => l_UpdEmpAsg_out.effective_start_date
,p_effective_end_date => l_UpdEmpAsg_out.effective_end_date
); */
select *
from per_all_assignments_f paf
where paf.assignment_id = c_assignment_id
and paf.business_group_id = c_business_group_id
and c_effective_date between paf.effective_start_date
and paf.effective_end_date;
select *
from pay_people_groups
where people_group_id = c_people_grp_id;
select *
from hr_soft_coding_keyflex
where soft_coding_keyflex_id = c_scl_kff_id;
l_datetrack_update_mode varchar2(50);
l_dt_update boolean;
,p_update => l_dt_update
,p_update_override => l_dt_upd_override
,p_update_change_insert => l_upd_chg_ins
);
if l_dt_update then
l_datetrack_update_mode := 'UPDATE';
l_datetrack_update_mode := 'CORRECTION';
l_datetrack_update_mode := 'CORRECTION';
hr_utility.set_location(' l_datetrack_update_mode: ' ||
l_datetrack_update_mode, 60);
HR_Assignment_API.Update_CWK_Asg_Criteria
(p_validate => false
,p_effective_date => p_effective_date
,p_datetrack_update_mode => l_datetrack_update_mode
,p_assignment_id => g_asg_rec.assignment_id
,p_called_from_mass_update => false
,p_grade_id => l_asg_rec.grade_id
,p_position_id => l_asg_rec.position_id
,p_job_id => l_asg_rec.job_id
,p_location_id => l_asg_rec.location_id
,p_organization_id => l_asg_rec.organization_id
,p_pay_basis_id => l_asg_rec.pay_basis_id
-- People Group Segments
,p_segment1 => l_grp_rec.segment1
,p_segment2 => l_grp_rec.segment2
,p_segment3 => l_grp_rec.segment3
,p_segment4 => l_grp_rec.segment4
,p_segment5 => l_grp_rec.segment5
,p_segment6 => l_grp_rec.segment6
,p_segment7 => l_grp_rec.segment7
,p_segment8 => l_grp_rec.segment8
,p_segment9 => l_grp_rec.segment9
,p_segment10 => l_grp_rec.segment10
,p_segment11 => l_grp_rec.segment11
,p_segment12 => l_grp_rec.segment12
,p_segment13 => l_grp_rec.segment13
,p_segment14 => l_grp_rec.segment14
,p_segment15 => l_grp_rec.segment15
,p_segment16 => l_grp_rec.segment16
,p_segment17 => l_grp_rec.segment17
,p_segment18 => l_grp_rec.segment18
,p_segment19 => l_grp_rec.segment19
,p_segment20 => l_grp_rec.segment20
,p_segment21 => l_grp_rec.segment21
,p_segment22 => l_grp_rec.segment22
,p_segment23 => l_grp_rec.segment23
,p_segment24 => l_grp_rec.segment24
,p_segment25 => l_grp_rec.segment25
,p_segment26 => l_grp_rec.segment26
,p_segment27 => l_grp_rec.segment27
,p_segment28 => l_grp_rec.segment28
,p_segment29 => l_grp_rec.segment29
,p_segment30 => l_grp_rec.segment30
,p_concat_segments => l_grp_rec.group_name
-- In/Out
,p_object_version_number => l_cur_asg_rec.object_version_number
-- Out
,p_people_group_name => p_asg_crit_out.group_name
,p_people_group_id => p_asg_crit_out.people_group_id
,p_effective_start_date => p_asg_crit_out.asg_effective_start_date
,p_effective_end_date => p_asg_crit_out.asg_effective_end_date
,p_org_now_no_manager_warning => p_asg_crit_out.org_now_no_manager_warning
,p_other_manager_warning => p_asg_crit_out.other_manager_warning
,p_spp_delete_warning => p_asg_crit_out.spp_delete_warning
,p_entries_changed_warning => p_asg_crit_out.entries_changed_warning
,p_tax_district_changed_warning => p_asg_crit_out.tax_district_changed_warning
);
l_datetrack_update_mode := 'CORRECTION';
HR_Assignment_API.Update_CWK_Asg
(p_validate => false
,p_effective_date => p_effective_date
,p_datetrack_update_mode => l_datetrack_update_mode
,p_assignment_id => g_asg_rec.assignment_id -- l_asg_rec.assignment_id => Changed by pkagrawa
,p_comments => g_asg_comments --$ null
,p_project_title => null
,p_concat_segments => null
,p_establishment_id => l_asg_rec.establishment_id
,p_supervisor_assignment_id => l_asg_rec.supervisor_assignment_id
,p_title => l_asg_rec.title
,p_assignment_category => l_asg_rec.employment_category
,p_assignment_number => l_asg_rec.assignment_number
,p_change_reason => l_asg_rec.change_reason
,p_default_code_comb_id => l_asg_rec.default_code_comb_id
,p_frequency => l_asg_rec.frequency
,p_internal_address_line => l_asg_rec.internal_address_line
,p_labour_union_member_flag => l_asg_rec.labour_union_member_flag
,p_manager_flag => l_asg_rec.manager_flag
,p_normal_hours => l_asg_rec.normal_hours
,p_set_of_books_id => l_asg_rec.set_of_books_id
,p_source_type => l_asg_rec.source_type
,p_supervisor_id => l_asg_rec.supervisor_id
,p_time_normal_finish => l_asg_rec.time_normal_finish
,p_time_normal_start => l_asg_rec.time_normal_start
,p_vendor_assignment_number => l_asg_rec.vendor_assignment_number
,p_vendor_employee_number => l_asg_rec.vendor_employee_number
,p_vendor_id => l_asg_rec.vendor_id
,p_vendor_site_id => l_asg_rec.vendor_site_id
,p_po_header_id => l_asg_rec.po_header_id
,p_po_line_id => l_asg_rec.po_line_id
,p_projected_assignment_end => l_asg_rec.projected_assignment_end
,p_assignment_status_type_id => l_asg_rec.assignment_status_type_id
-- Asg DF
,p_attribute_category => l_asg_rec.ass_attribute_category
,p_attribute1 => l_asg_rec.ass_attribute1
,p_attribute2 => l_asg_rec.ass_attribute2
,p_attribute3 => l_asg_rec.ass_attribute3
,p_attribute4 => l_asg_rec.ass_attribute4
,p_attribute5 => l_asg_rec.ass_attribute5
,p_attribute6 => l_asg_rec.ass_attribute6
,p_attribute7 => l_asg_rec.ass_attribute7
,p_attribute8 => l_asg_rec.ass_attribute8
,p_attribute9 => l_asg_rec.ass_attribute9
,p_attribute10 => l_asg_rec.ass_attribute10
,p_attribute11 => l_asg_rec.ass_attribute11
,p_attribute12 => l_asg_rec.ass_attribute12
,p_attribute13 => l_asg_rec.ass_attribute13
,p_attribute14 => l_asg_rec.ass_attribute14
,p_attribute15 => l_asg_rec.ass_attribute15
,p_attribute16 => l_asg_rec.ass_attribute16
,p_attribute17 => l_asg_rec.ass_attribute17
,p_attribute18 => l_asg_rec.ass_attribute18
,p_attribute19 => l_asg_rec.ass_attribute19
,p_attribute20 => l_asg_rec.ass_attribute20
,p_attribute21 => l_asg_rec.ass_attribute21
,p_attribute22 => l_asg_rec.ass_attribute22
,p_attribute23 => l_asg_rec.ass_attribute23
,p_attribute24 => l_asg_rec.ass_attribute24
,p_attribute25 => l_asg_rec.ass_attribute25
,p_attribute26 => l_asg_rec.ass_attribute26
,p_attribute27 => l_asg_rec.ass_attribute27
,p_attribute28 => l_asg_rec.ass_attribute28
,p_attribute29 => l_asg_rec.ass_attribute29
,p_attribute30 => l_asg_rec.ass_attribute30
-- Soft Coding KFF
,p_scl_segment1 => l_scl_rec.segment1
,p_scl_segment2 => l_scl_rec.segment2
,p_scl_segment3 => l_scl_rec.segment3
,p_scl_segment4 => l_scl_rec.segment4
,p_scl_segment5 => l_scl_rec.segment5
,p_scl_segment6 => l_scl_rec.segment6
,p_scl_segment7 => l_scl_rec.segment7
,p_scl_segment8 => l_scl_rec.segment8
,p_scl_segment9 => l_scl_rec.segment9
,p_scl_segment10 => l_scl_rec.segment10
,p_scl_segment11 => l_scl_rec.segment11
,p_scl_segment12 => l_scl_rec.segment12
,p_scl_segment13 => l_scl_rec.segment13
,p_scl_segment14 => l_scl_rec.segment14
,p_scl_segment15 => l_scl_rec.segment15
,p_scl_segment16 => l_scl_rec.segment16
,p_scl_segment17 => l_scl_rec.segment17
,p_scl_segment18 => l_scl_rec.segment18
,p_scl_segment19 => l_scl_rec.segment19
,p_scl_segment20 => l_scl_rec.segment20
,p_scl_segment21 => l_scl_rec.segment21
,p_scl_segment22 => l_scl_rec.segment22
,p_scl_segment23 => l_scl_rec.segment23
,p_scl_segment24 => l_scl_rec.segment24
,p_scl_segment25 => l_scl_rec.segment25
,p_scl_segment26 => l_scl_rec.segment26
,p_scl_segment27 => l_scl_rec.segment27
,p_scl_segment28 => l_scl_rec.segment28
,p_scl_segment29 => l_scl_rec.segment29
,p_scl_segment30 => l_scl_rec.segment30
-- In/Out
,p_object_version_number => l_cur_asg_rec.object_version_number
-- Out
,p_org_now_no_manager_warning => p_asg_crit_out.org_now_no_manager_warning
,p_effective_start_date => l_UpdEmpAsg_out.effective_start_date
,p_effective_end_date => l_UpdEmpAsg_out.effective_end_date
,p_comment_id => l_UpdEmpAsg_out.comment_id
,p_no_managers_warning => l_UpdEmpAsg_out.no_managers_warning
,p_other_manager_warning => l_UpdEmpAsg_out.other_manager_warning
,p_soft_coding_keyflex_id => l_asg_rec.soft_coding_keyflex_id
,p_concatenated_segments => l_UpdEmpAsg_out.concatenated_segments
,p_hourly_salaried_warning => l_UpdEmpAsg_out.hourly_salaried_warning
);
procedure Update_Employee_Asg
(p_effective_date in date
,p_asg_crit_out in out NOCOPY t_AsgUpdCrit_Api
--,p_UpdEmpAsg_out IN OUT NOCOPY t_Upd_Emp_Asg_Api
) as
-- Cursor to get Assignment details
cursor csr_asg (c_effective_date in date
,c_assignment_id in number
,c_business_group_id in number)is
select *
from per_all_assignments_f paf
where paf.assignment_id = c_assignment_id
and paf.business_group_id = c_business_group_id
and c_effective_date between paf.effective_start_date
and paf.effective_end_date;
select *
from pay_people_groups
where people_group_id = c_people_grp_id;
select *
from hr_soft_coding_keyflex
where soft_coding_keyflex_id = c_scl_kff_id;
l_proc_name constant varchar2(150):= g_pkg ||'Update_Employee_Asg';
l_datetrack_update_mode varchar2(50);
l_dt_update boolean;
,p_update => l_dt_update
,p_update_override => l_dt_upd_override
,p_update_change_insert => l_upd_chg_ins
);
if l_dt_update then
l_datetrack_update_mode := 'UPDATE';
l_datetrack_update_mode := 'CORRECTION' ; --$ 'UPDATE' not possible
l_datetrack_update_mode := 'CORRECTION';
hr_utility.set_location(' l_datetrack_update_mode: ' ||
l_datetrack_update_mode, 60);
/*Hr_Assignment_Api.Update_Emp_Asg_Criteria
(p_effective_date => p_effective_date
,p_datetrack_update_mode => l_datetrack_update_mode
,p_assignment_id => g_asg_rec.assignment_id
,p_validate => false
,p_called_from_mass_update => false
,p_grade_id => l_asg_rec.grade_id
,p_position_id => l_asg_rec.position_id
,p_job_id => l_asg_rec.job_id
,p_payroll_id => l_asg_rec.payroll_id
,p_location_id => l_asg_rec.location_id
,p_organization_id => l_asg_rec.organization_id
,p_pay_basis_id => l_asg_rec.pay_basis_id
,p_employment_category => l_asg_rec.employment_category
,p_segment1 => l_grp_rec.segment1
,p_segment2 => l_grp_rec.segment2
,p_segment3 => l_grp_rec.segment3
,p_segment4 => l_grp_rec.segment4
,p_segment5 => l_grp_rec.segment5
,p_segment6 => l_grp_rec.segment6
,p_segment7 => l_grp_rec.segment7
,p_segment8 => l_grp_rec.segment8
,p_segment9 => l_grp_rec.segment9
,p_segment10 => l_grp_rec.segment10
,p_segment11 => l_grp_rec.segment11
,p_segment12 => l_grp_rec.segment12
,p_segment13 => l_grp_rec.segment13
,p_segment14 => l_grp_rec.segment14
,p_segment15 => l_grp_rec.segment15
,p_segment16 => l_grp_rec.segment16
,p_segment17 => l_grp_rec.segment17
,p_segment18 => l_grp_rec.segment18
,p_segment19 => l_grp_rec.segment19
,p_segment20 => l_grp_rec.segment20
,p_segment21 => l_grp_rec.segment21
,p_segment22 => l_grp_rec.segment22
,p_segment23 => l_grp_rec.segment23
,p_segment24 => l_grp_rec.segment24
,p_segment25 => l_grp_rec.segment25
,p_segment26 => l_grp_rec.segment26
,p_segment27 => l_grp_rec.segment27
,p_segment28 => l_grp_rec.segment28
,p_segment29 => l_grp_rec.segment29
,p_segment30 => l_grp_rec.segment30
,p_concat_segments => l_grp_rec.group_name
,p_scl_segment1 => l_scl_rec.segment1
,p_object_version_number => l_cur_asg_rec.object_version_number
,p_special_ceiling_step_id => p_asg_crit_out.special_ceiling_step_id
,p_people_group_id => p_asg_crit_out.people_group_id
,p_soft_coding_keyflex_id => p_asg_crit_out.soft_coding_keyflex_id
,p_group_name => p_asg_crit_out.group_name
,p_effective_start_date => p_asg_crit_out.asg_effective_start_date
,p_effective_end_date => p_asg_crit_out.asg_effective_end_date
,p_org_now_no_manager_warning => p_asg_crit_out.org_now_no_manager_warning
,p_other_manager_warning => p_asg_crit_out.other_manager_warning
,p_spp_delete_warning => p_asg_crit_out.spp_delete_warning
,p_entries_changed_warning => p_asg_crit_out.entries_changed_warning
,p_tax_district_changed_warning => p_asg_crit_out.tax_district_changed_warning
,p_concatenated_segments => p_asg_crit_out.concatenated_segments
,p_gsp_post_process_warning => p_asg_crit_out.gsp_post_process_warning
);
Hr_Assignment_Api.Update_Emp_Asg
(p_validate => false
,p_effective_date => p_effective_date
,p_datetrack_update_mode => l_datetrack_update_mode
,p_assignment_id => l_cur_asg_rec.assignment_id
,p_object_version_number => l_cur_asg_rec.object_version_number
,p_supervisor_id => l_asg_rec.supervisor_id
,p_assignment_number => nvl(l_asg_rec.assignment_number,
l_cur_asg_rec.assignment_number)
,p_change_reason => l_asg_rec.change_reason
,p_date_probation_end => l_asg_rec.date_probation_end
,p_default_code_comb_id => l_asg_rec.default_code_comb_id
,p_frequency => l_asg_rec.frequency
,p_internal_address_line => l_asg_rec.internal_address_line
,p_manager_flag => l_asg_rec.manager_flag
,p_normal_hours => l_asg_rec.normal_hours
,p_perf_review_period => l_asg_rec.perf_review_period
,p_perf_review_period_frequency => l_asg_rec.perf_review_period_frequency
,p_probation_period => l_asg_rec.probation_period
,p_probation_unit => l_asg_rec.probation_unit
,p_sal_review_period => l_asg_rec.sal_review_period
,p_sal_review_period_frequency => l_asg_rec.sal_review_period_frequency
,p_set_of_books_id => l_asg_rec.set_of_books_id
,p_source_type => l_asg_rec.source_type
,p_time_normal_finish => l_asg_rec.time_normal_finish
,p_time_normal_start => l_asg_rec.time_normal_start
,p_bargaining_unit_code => l_asg_rec.bargaining_unit_code
,p_labour_union_member_flag => l_asg_rec.labour_union_member_flag
,p_hourly_salaried_code => l_asg_rec.hourly_salaried_code
,p_title => l_asg_rec.title
--Added by DBANSAL
,p_employee_category => l_asg_rec.employee_category
,p_comments => g_asg_comments
-- Assignment DF
,p_ass_attribute_category => l_asg_rec.ass_attribute_category
,p_ass_attribute1 => l_asg_rec.ass_attribute1
,p_ass_attribute2 => l_asg_rec.ass_attribute2
,p_ass_attribute3 => l_asg_rec.ass_attribute3
,p_ass_attribute4 => l_asg_rec.ass_attribute4
,p_ass_attribute5 => l_asg_rec.ass_attribute5
,p_ass_attribute6 => l_asg_rec.ass_attribute6
,p_ass_attribute7 => l_asg_rec.ass_attribute7
,p_ass_attribute8 => l_asg_rec.ass_attribute8
,p_ass_attribute9 => l_asg_rec.ass_attribute9
,p_ass_attribute10 => l_asg_rec.ass_attribute10
,p_ass_attribute11 => l_asg_rec.ass_attribute11
,p_ass_attribute12 => l_asg_rec.ass_attribute12
,p_ass_attribute13 => l_asg_rec.ass_attribute13
,p_ass_attribute14 => l_asg_rec.ass_attribute14
,p_ass_attribute15 => l_asg_rec.ass_attribute15
,p_ass_attribute16 => l_asg_rec.ass_attribute16
,p_ass_attribute17 => l_asg_rec.ass_attribute17
,p_ass_attribute18 => l_asg_rec.ass_attribute18
,p_ass_attribute19 => l_asg_rec.ass_attribute19
,p_ass_attribute20 => l_asg_rec.ass_attribute20
,p_ass_attribute21 => l_asg_rec.ass_attribute21
,p_ass_attribute22 => l_asg_rec.ass_attribute22
,p_ass_attribute23 => l_asg_rec.ass_attribute23
,p_ass_attribute24 => l_asg_rec.ass_attribute24
,p_ass_attribute25 => l_asg_rec.ass_attribute25
,p_ass_attribute26 => l_asg_rec.ass_attribute26
,p_ass_attribute27 => l_asg_rec.ass_attribute27
,p_ass_attribute28 => l_asg_rec.ass_attribute28
,p_ass_attribute29 => l_asg_rec.ass_attribute29
,p_ass_attribute30 => l_asg_rec.ass_attribute30
-- Hr Soft Coding KeyFlex segments
,p_segment1 => l_scl_rec.segment1
,p_segment2 => l_scl_rec.segment2
,p_segment3 => l_scl_rec.segment3
,p_segment4 => l_scl_rec.segment4
,p_segment5 => l_scl_rec.segment5
,p_segment6 => l_scl_rec.segment6
,p_segment7 => l_scl_rec.segment7
,p_segment8 => l_scl_rec.segment8
,p_segment9 => l_scl_rec.segment9
,p_segment10 => l_scl_rec.segment10
,p_segment11 => l_scl_rec.segment11
,p_segment12 => l_scl_rec.segment12
,p_segment13 => l_scl_rec.segment13
,p_segment14 => l_scl_rec.segment14
,p_segment15 => l_scl_rec.segment15
,p_segment16 => l_scl_rec.segment16
,p_segment17 => l_scl_rec.segment17
,p_segment18 => l_scl_rec.segment18
,p_segment19 => l_scl_rec.segment19
,p_segment20 => l_scl_rec.segment20
,p_segment21 => l_scl_rec.segment21
,p_segment22 => l_scl_rec.segment22
,p_segment23 => l_scl_rec.segment23
,p_segment24 => l_scl_rec.segment24
,p_segment25 => l_scl_rec.segment25
,p_segment26 => l_scl_rec.segment26
,p_segment27 => l_scl_rec.segment27
,p_segment28 => l_scl_rec.segment28
,p_segment29 => l_scl_rec.segment29
,p_segment30 => l_scl_rec.segment30
,p_concat_segments => l_scl_rec.concatenated_segments
-- Out Parameters
,p_cagr_grade_def_id => l_asg_rec.cagr_grade_def_id
,p_soft_coding_keyflex_id => g_asg_rec.soft_coding_keyflex_id
,p_cagr_concatenated_segments => l_UpdEmpAsg_out.cagr_concatenated_segments
,p_comment_id => l_UpdEmpAsg_out.comment_id
,p_effective_start_date => l_UpdEmpAsg_out.effective_start_date
,p_effective_end_date => l_UpdEmpAsg_out.effective_end_date
,p_concatenated_segments => l_UpdEmpAsg_out.concatenated_segments
,p_no_managers_warning => l_UpdEmpAsg_out.no_managers_warning
,p_other_manager_warning => l_UpdEmpAsg_out.other_manager_warning
,p_hourly_salaried_warning => l_UpdEmpAsg_out.hourly_salaried_warning
,p_gsp_post_process_warning => l_UpdEmpAsg_out.gsp_post_process_warning
);
l_datetrack_update_mode := 'CORRECTION';
Hr_Assignment_Api.Update_Emp_Asg_Criteria
(p_effective_date => p_effective_date
,p_datetrack_update_mode => l_datetrack_update_mode
,p_assignment_id => g_asg_rec.assignment_id
,p_validate => false
,p_called_from_mass_update => false
,p_grade_id => l_asg_rec.grade_id
,p_position_id => l_asg_rec.position_id
,p_job_id => l_asg_rec.job_id
,p_payroll_id => l_asg_rec.payroll_id
,p_location_id => l_asg_rec.location_id
,p_organization_id => l_asg_rec.organization_id
,p_pay_basis_id => l_asg_rec.pay_basis_id
,p_employment_category => l_asg_rec.employment_category
,p_segment1 => l_grp_rec.segment1
,p_segment2 => l_grp_rec.segment2
,p_segment3 => l_grp_rec.segment3
,p_segment4 => l_grp_rec.segment4
,p_segment5 => l_grp_rec.segment5
,p_segment6 => l_grp_rec.segment6
,p_segment7 => l_grp_rec.segment7
,p_segment8 => l_grp_rec.segment8
,p_segment9 => l_grp_rec.segment9
,p_segment10 => l_grp_rec.segment10
,p_segment11 => l_grp_rec.segment11
,p_segment12 => l_grp_rec.segment12
,p_segment13 => l_grp_rec.segment13
,p_segment14 => l_grp_rec.segment14
,p_segment15 => l_grp_rec.segment15
,p_segment16 => l_grp_rec.segment16
,p_segment17 => l_grp_rec.segment17
,p_segment18 => l_grp_rec.segment18
,p_segment19 => l_grp_rec.segment19
,p_segment20 => l_grp_rec.segment20
,p_segment21 => l_grp_rec.segment21
,p_segment22 => l_grp_rec.segment22
,p_segment23 => l_grp_rec.segment23
,p_segment24 => l_grp_rec.segment24
,p_segment25 => l_grp_rec.segment25
,p_segment26 => l_grp_rec.segment26
,p_segment27 => l_grp_rec.segment27
,p_segment28 => l_grp_rec.segment28
,p_segment29 => l_grp_rec.segment29
,p_segment30 => l_grp_rec.segment30
,p_concat_segments => l_grp_rec.group_name
,p_scl_segment1 => l_scl_rec.segment1
,p_object_version_number => l_cur_asg_rec.object_version_number
,p_special_ceiling_step_id => p_asg_crit_out.special_ceiling_step_id
,p_people_group_id => p_asg_crit_out.people_group_id
,p_soft_coding_keyflex_id => p_asg_crit_out.soft_coding_keyflex_id
,p_group_name => p_asg_crit_out.group_name
,p_effective_start_date => p_asg_crit_out.asg_effective_start_date
,p_effective_end_date => p_asg_crit_out.asg_effective_end_date
,p_org_now_no_manager_warning => p_asg_crit_out.org_now_no_manager_warning
,p_other_manager_warning => p_asg_crit_out.other_manager_warning
,p_spp_delete_warning => p_asg_crit_out.spp_delete_warning
,p_entries_changed_warning => p_asg_crit_out.entries_changed_warning
,p_tax_district_changed_warning => p_asg_crit_out.tax_district_changed_warning
,p_concatenated_segments => p_asg_crit_out.concatenated_segments
,p_gsp_post_process_warning => p_asg_crit_out.gsp_post_process_warning
);
end Update_Employee_Asg;
,p_datetrack_update_mode in varchar2
,p_adjusted_svc_date in date default null -- Added by pkagrawa
) is
l_proc_name constant varchar2(150):= g_pkg ||'Upd_Batch_Person';
l_datetrack_update_mode varchar2(50);
l_dt_update boolean;
if p_datetrack_update_mode is not null then
l_datetrack_update_mode := p_datetrack_update_mode;
,p_update => l_dt_update
,p_update_override => l_dt_upd_override
,p_update_change_insert => l_upd_chg_ins
);
if l_dt_update then
l_datetrack_update_mode := 'UPDATE';
l_datetrack_update_mode := 'CORRECTION';
l_datetrack_update_mode := 'CORRECTION';
hr_utility.set_location(' l_datetrack_update_mode: ' ||
l_datetrack_update_mode, 30);
Hrdpp_Update_Person.Insert_Batch_Lines
(p_batch_id => p_batch_id
,p_user_sequence => p_user_sequence
,p_link_value => p_link_value
,p_effective_date => g_per_rec.start_date
,p_datetrack_update_mode => l_datetrack_update_mode
,p_person_user_key => p_person_user_key
,p_user_person_type => p_user_person_type
,p_language_code => Userenv('lang')
,p_party_id => nvl(l_per_rec.party_id,
l_cur_per_rec.party_id)
,p_employee_number => nvl(l_per_rec.employee_number,
l_cur_per_rec.employee_number)
,p_applicant_number => nvl(l_per_rec.applicant_number,
l_cur_per_rec.applicant_number)
,p_npw_number => nvl(l_per_rec.npw_number,
l_cur_per_rec.npw_number)
,p_last_name => nvl(l_per_rec.last_name,
l_cur_per_rec.last_name)
,p_first_name => l_per_rec.first_name
,p_date_of_birth => nvl(l_per_rec.date_of_birth,
l_cur_per_rec.date_of_birth)
,p_marital_status => l_per_rec.marital_status
,p_middle_names => l_per_rec.middle_names
,p_sex => nvl(l_per_rec.sex,
l_cur_per_rec.sex)
,p_title => l_per_rec.title
,p_nationality => l_per_rec.nationality
,p_previous_last_name => l_per_rec.previous_last_name
,p_national_identifier => l_per_rec.national_identifier
,p_known_as => l_per_rec.known_as
,p_email_address => l_per_rec.email_address
,p_registered_disabled_flag => l_per_rec.registered_disabled_flag
,p_date_employee_data_verified => l_per_rec.date_employee_data_verified
,p_expense_check_send_to_addres => l_per_rec.expense_check_send_to_address
,p_per_information_category => l_per_rec.per_information_category
,p_per_information1 => l_per_rec.per_information1
,p_per_information2 => l_per_rec.per_information2
,p_per_information3 => l_per_rec.per_information3
,p_per_information4 => l_per_rec.per_information4
,p_per_information5 => l_per_rec.per_information5
,p_per_information6 => l_per_rec.per_information6
,p_per_information7 => l_per_rec.per_information7
,p_per_information8 => l_per_rec.per_information8
,p_per_information9 => l_per_rec.per_information9
,p_per_information10 => l_per_rec.per_information10
,p_per_information11 => l_per_rec.per_information11
,p_per_information12 => l_per_rec.per_information12
,p_per_information13 => l_per_rec.per_information13
,p_per_information14 => l_per_rec.per_information14
,p_per_information15 => l_per_rec.per_information15
,p_per_information16 => l_per_rec.per_information16
,p_per_information17 => l_per_rec.per_information17
,p_per_information18 => l_per_rec.per_information18
,p_per_information19 => l_per_rec.per_information19
,p_per_information20 => l_per_rec.per_information20
,p_per_information21 => l_per_rec.per_information21
,p_per_information22 => l_per_rec.per_information22
,p_per_information23 => l_per_rec.per_information23
,p_per_information24 => l_per_rec.per_information24
,p_per_information25 => l_per_rec.per_information25
,p_per_information26 => l_per_rec.per_information26
,p_per_information27 => l_per_rec.per_information27
,p_per_information28 => l_per_rec.per_information28
,p_per_information29 => l_per_rec.per_information29
,p_per_information30 => l_per_rec.per_information30
-- Person DF
,p_attribute_category => l_per_rec.attribute_category
,p_attribute1 => l_per_rec.attribute1
,p_attribute2 => l_per_rec.attribute2
,p_attribute3 => l_per_rec.attribute3
,p_attribute4 => l_per_rec.attribute4
,p_attribute5 => l_per_rec.attribute5
,p_attribute6 => l_per_rec.attribute6
,p_attribute7 => l_per_rec.attribute7
,p_attribute8 => l_per_rec.attribute8
,p_attribute9 => l_per_rec.attribute9
,p_attribute10 => l_per_rec.attribute10
,p_attribute11 => l_per_rec.attribute11
,p_attribute12 => l_per_rec.attribute12
,p_attribute13 => l_per_rec.attribute13
,p_attribute14 => l_per_rec.attribute14
,p_attribute15 => l_per_rec.attribute15
,p_attribute16 => l_per_rec.attribute16
,p_attribute17 => l_per_rec.attribute17
,p_attribute18 => l_per_rec.attribute18
,p_attribute19 => l_per_rec.attribute19
,p_attribute20 => l_per_rec.attribute20
,p_attribute21 => l_per_rec.attribute21
,p_attribute22 => l_per_rec.attribute22
,p_attribute23 => l_per_rec.attribute23
,p_attribute24 => l_per_rec.attribute24
,p_attribute25 => l_per_rec.attribute25
,p_attribute26 => l_per_rec.attribute26
,p_attribute27 => l_per_rec.attribute27
,p_attribute28 => l_per_rec.attribute28
,p_attribute29 => l_per_rec.attribute29
,p_attribute30 => l_per_rec.attribute30
,p_date_of_death => l_per_rec.date_of_death
,p_background_date_check => l_per_rec.background_date_check
,p_hold_applicant_date_until => l_per_rec.hold_applicant_date_until
,p_last_medical_test_date => l_per_rec.last_medical_test_date
,p_projected_start_date => l_per_rec.projected_start_date
,p_resume_last_updated => l_per_rec.resume_last_updated
,p_receipt_of_death_cert_date => l_per_rec.receipt_of_death_cert_date
,p_coord_ben_med_cvg_strt_dt => l_per_rec.coord_ben_med_cvg_strt_dt
,p_coord_ben_med_cvg_end_dt => l_per_rec.coord_ben_med_cvg_end_dt
,p_original_date_of_hire => l_per_rec.original_date_of_hire
,p_dpdnt_adoption_date => l_per_rec.dpdnt_adoption_date
,p_background_check_status => l_per_rec.background_check_status
,p_blood_type => l_per_rec.blood_type
,p_correspondence_language => l_per_rec.correspondence_language
,p_fte_capacity => l_per_rec.fte_capacity
,p_honors => l_per_rec.honors
,p_internal_location => l_per_rec.internal_location
,p_last_medical_test_by => l_per_rec.last_medical_test_by
,p_mailstop => l_per_rec.mailstop
,p_office_number => l_per_rec.office_number
,p_on_military_service => l_per_rec.on_military_service
,p_pre_name_adjunct => l_per_rec.pre_name_adjunct
,p_rehire_authorizor => l_per_rec.rehire_authorizor
,p_rehire_recommendation => l_per_rec.rehire_recommendation
,p_resume_exists => l_per_rec.resume_exists
,p_second_passport_exists => l_per_rec.second_passport_exists
,p_student_status => l_per_rec.student_status
,p_work_schedule => l_per_rec.work_schedule
,p_rehire_reason => l_per_rec.rehire_reason
,p_suffix => l_per_rec.suffix
--$ In Batch Lines Benefit Group Name has to be passed instead of ID
-- ,p_benefit_group => l_per_rec.benefit_group_id
,p_benefit_group => g_benefit_grp_name
,p_coord_ben_med_pln_no => l_per_rec.coord_ben_med_pln_no
,p_coord_ben_no_cvg_flag => l_per_rec.coord_ben_no_cvg_flag
,p_coord_ben_med_ext_er => l_per_rec.coord_ben_med_ext_er
,p_coord_ben_med_pl_name => l_per_rec.coord_ben_med_pl_name
,p_coord_ben_med_insr_crr_name => l_per_rec.coord_ben_med_insr_crr_name
,p_coord_ben_med_insr_crr_ident => l_per_rec.coord_ben_med_insr_crr_ident
,p_uses_tobacco_flag => l_per_rec.uses_tobacco_flag
,p_dpdnt_vlntry_svce_flag => l_per_rec.dpdnt_vlntry_svce_flag
,p_town_of_birth => l_per_rec.town_of_birth
,p_region_of_birth => l_per_rec.region_of_birth
,p_country_of_birth => l_per_rec.country_of_birth
,p_global_person_id => l_per_rec.global_person_id
,p_vendor_name => null
,p_adjusted_svc_date => p_adjusted_svc_date --Added By pkagrawa
);
hr_utility.set_location('..Future Update exists for the Student Employee', 60);
,p_datetrack_update_mode in varchar2
,p_vacancy_user_key in varchar2
,p_application_user_key in varchar2
) is
l_proc_name constant varchar2(150):= g_pkg ||'InsUpd_Batch_Applicant';
Hrdpp_Create_Applicant.Insert_Batch_Lines
(p_batch_id => p_batch_id
,p_data_pump_batch_line_id => p_data_pump_batch_line_id
,p_user_sequence => p_user_sequence
,p_link_value => p_link_value
,p_person_user_key => p_person_user_key
,p_assignment_user_key => p_assignment_user_key
,p_user_person_type => p_user_person_type
,p_application_user_key => p_application_user_key
,p_vacancy_user_key => p_vacancy_user_key
,p_language_code => Userenv('lang')
,p_applicant_number => g_per_rec.applicant_number
,p_date_received => g_per_rec.start_date
,p_last_name => g_per_rec.last_name
,p_sex => g_per_rec.sex
,p_date_employee_data_verified => g_per_rec.date_employee_data_verified
,p_date_of_birth => g_per_rec.date_of_birth
,p_email_address => g_per_rec.email_address
,p_expense_check_send_to_addres => g_per_rec.expense_check_send_to_address
,p_first_name => g_per_rec.first_name
,p_known_as => g_per_rec.known_as
,p_marital_status => g_per_rec.marital_status
,p_middle_names => g_per_rec.middle_names
,p_nationality => g_per_rec.nationality
,p_national_identifier => g_per_rec.national_identifier
,p_previous_last_name => g_per_rec.previous_last_name
,p_registered_disabled_flag => g_per_rec.registered_disabled_flag
,p_title => g_per_rec.title
,p_attribute_category => g_per_rec.attribute_category
,p_attribute1 => g_per_rec.attribute1
,p_attribute2 => g_per_rec.attribute2
,p_attribute3 => g_per_rec.attribute3
,p_attribute4 => g_per_rec.attribute4
,p_attribute5 => g_per_rec.attribute5
,p_attribute6 => g_per_rec.attribute6
,p_attribute7 => g_per_rec.attribute7
,p_attribute8 => g_per_rec.attribute8
,p_attribute9 => g_per_rec.attribute9
,p_attribute10 => g_per_rec.attribute10
,p_attribute11 => g_per_rec.attribute11
,p_attribute12 => g_per_rec.attribute12
,p_attribute13 => g_per_rec.attribute13
,p_attribute14 => g_per_rec.attribute14
,p_attribute15 => g_per_rec.attribute15
,p_attribute16 => g_per_rec.attribute16
,p_attribute17 => g_per_rec.attribute17
,p_attribute18 => g_per_rec.attribute18
,p_attribute19 => g_per_rec.attribute19
,p_attribute20 => g_per_rec.attribute20
,p_attribute21 => g_per_rec.attribute21
,p_attribute22 => g_per_rec.attribute22
,p_attribute23 => g_per_rec.attribute23
,p_attribute24 => g_per_rec.attribute24
,p_attribute25 => g_per_rec.attribute25
,p_attribute26 => g_per_rec.attribute26
,p_attribute27 => g_per_rec.attribute27
,p_attribute28 => g_per_rec.attribute28
,p_attribute29 => g_per_rec.attribute29
,p_attribute30 => g_per_rec.attribute30
,p_per_information_category => g_per_rec.per_information_category
,p_per_information1 => g_per_rec.per_information1
,p_per_information2 => g_per_rec.per_information2
,p_per_information3 => g_per_rec.per_information3
,p_per_information4 => g_per_rec.per_information4
,p_per_information5 => g_per_rec.per_information5
,p_per_information6 => g_per_rec.per_information6
,p_per_information7 => g_per_rec.per_information7
,p_per_information8 => g_per_rec.per_information8
,p_per_information9 => g_per_rec.per_information9
,p_per_information10 => g_per_rec.per_information10
,p_per_information11 => g_per_rec.per_information11
,p_per_information12 => g_per_rec.per_information12
,p_per_information13 => g_per_rec.per_information13
,p_per_information14 => g_per_rec.per_information14
,p_per_information15 => g_per_rec.per_information15
,p_per_information16 => g_per_rec.per_information16
,p_per_information17 => g_per_rec.per_information17
,p_per_information18 => g_per_rec.per_information18
,p_per_information19 => g_per_rec.per_information19
,p_per_information20 => g_per_rec.per_information20
,p_per_information21 => g_per_rec.per_information21
,p_per_information22 => g_per_rec.per_information22
,p_per_information23 => g_per_rec.per_information23
,p_per_information24 => g_per_rec.per_information24
,p_per_information25 => g_per_rec.per_information25
,p_per_information26 => g_per_rec.per_information26
,p_per_information27 => g_per_rec.per_information27
,p_per_information28 => g_per_rec.per_information28
,p_per_information29 => g_per_rec.per_information29
,p_per_information30 => g_per_rec.per_information30
,p_background_check_status => g_per_rec.background_check_status
,p_background_date_check => g_per_rec.background_date_check
,p_fte_capacity => g_per_rec.fte_capacity
,p_honors => g_per_rec.honors
,p_on_military_service => g_per_rec.on_military_service
,p_pre_name_adjunct => g_per_rec.pre_name_adjunct
,p_projected_start_date => g_per_rec.projected_start_date
,p_resume_exists => g_per_rec.resume_exists
,p_resume_last_updated => g_per_rec.resume_last_updated
,p_work_schedule => g_per_rec.work_schedule
,p_suffix => g_per_rec.suffix
,p_uses_tobacco_flag => g_per_rec.uses_tobacco_flag
,p_dpdnt_adoption_date => g_per_rec.dpdnt_adoption_date
,p_dpdnt_vlntry_svce_flag => g_per_rec.dpdnt_vlntry_svce_flag
,p_original_date_of_hire => g_per_rec.original_date_of_hire
,p_town_of_birth => g_per_rec.town_of_birth
,p_region_of_birth => g_per_rec.region_of_birth
,p_country_of_birth => g_per_rec.country_of_birth
,p_global_person_id => g_per_rec.global_person_id
,p_party_id => g_per_rec.party_id
,p_correspondence_language => g_per_rec.correspondence_language
--$ In Batch Lines Benefit Group Name has to be passed instead of ID
,p_benefit_group => g_benefit_grp_name
);
HrDpp_Create_Person.Insert_Batch_Lines
(p_batch_id => p_batch_id
,p_data_pump_batch_line_id => p_data_pump_batch_line_id
--,p_data_pump_business_grp_name
,p_user_sequence => p_user_sequence
,p_link_value => p_link_value
,p_person_user_key => p_person_user_key
,p_user_person_type => p_user_person_type
,p_start_date => g_per_rec.start_date
,p_last_name => g_per_rec.last_name
,p_sex => g_per_rec.sex
,p_date_employee_data_verified => g_per_rec.date_employee_data_verified
,p_date_of_birth => g_per_rec.date_of_birth
,p_email_address => g_per_rec.email_address
,p_expense_check_send_to_addres => g_per_rec.expense_check_send_to_address
,p_first_name => g_per_rec.first_name
,p_known_as => g_per_rec.known_as
,p_marital_status => g_per_rec.marital_status
,p_middle_names => g_per_rec.middle_names
,p_nationality => g_per_rec.nationality
,p_national_identifier => g_per_rec.national_identifier
,p_previous_last_name => g_per_rec.previous_last_name
,p_registered_disabled_flag => g_per_rec.registered_disabled_flag
,p_title => g_per_rec.title
,p_work_telephone => g_per_rec.work_telephone
,p_attribute_category => g_per_rec.attribute_category
,p_attribute1 => g_per_rec.attribute1
,p_attribute2 => g_per_rec.attribute2
,p_attribute3 => g_per_rec.attribute3
,p_attribute4 => g_per_rec.attribute4
,p_attribute5 => g_per_rec.attribute5
,p_attribute6 => g_per_rec.attribute6
,p_attribute7 => g_per_rec.attribute7
,p_attribute8 => g_per_rec.attribute8
,p_attribute9 => g_per_rec.attribute9
,p_attribute10 => g_per_rec.attribute10
,p_attribute11 => g_per_rec.attribute11
,p_attribute12 => g_per_rec.attribute12
,p_attribute13 => g_per_rec.attribute13
,p_attribute14 => g_per_rec.attribute14
,p_attribute15 => g_per_rec.attribute15
,p_attribute16 => g_per_rec.attribute16
,p_attribute17 => g_per_rec.attribute17
,p_attribute18 => g_per_rec.attribute18
,p_attribute19 => g_per_rec.attribute19
,p_attribute20 => g_per_rec.attribute20
,p_attribute21 => g_per_rec.attribute21
,p_attribute22 => g_per_rec.attribute22
,p_attribute23 => g_per_rec.attribute23
,p_attribute24 => g_per_rec.attribute24
,p_attribute25 => g_per_rec.attribute25
,p_attribute26 => g_per_rec.attribute26
,p_attribute27 => g_per_rec.attribute27
,p_attribute28 => g_per_rec.attribute28
,p_attribute29 => g_per_rec.attribute29
,p_attribute30 => g_per_rec.attribute30
,p_per_information_category => g_per_rec.per_information_category
,p_per_information1 => g_per_rec.per_information1
,p_per_information2 => g_per_rec.per_information2
,p_per_information3 => g_per_rec.per_information3
,p_per_information4 => g_per_rec.per_information4
,p_per_information5 => g_per_rec.per_information5
,p_per_information6 => g_per_rec.per_information6
,p_per_information7 => g_per_rec.per_information7
,p_per_information8 => g_per_rec.per_information8
,p_per_information9 => g_per_rec.per_information9
,p_per_information10 => g_per_rec.per_information10
,p_per_information11 => g_per_rec.per_information11
,p_per_information12 => g_per_rec.per_information12
,p_per_information13 => g_per_rec.per_information13
,p_per_information14 => g_per_rec.per_information14
,p_per_information15 => g_per_rec.per_information15
,p_per_information16 => g_per_rec.per_information16
,p_per_information17 => g_per_rec.per_information17
,p_per_information18 => g_per_rec.per_information18
,p_per_information19 => g_per_rec.per_information19
,p_per_information20 => g_per_rec.per_information20
,p_per_information21 => g_per_rec.per_information21
,p_per_information22 => g_per_rec.per_information22
,p_per_information23 => g_per_rec.per_information23
,p_per_information24 => g_per_rec.per_information24
,p_per_information25 => g_per_rec.per_information25
,p_per_information26 => g_per_rec.per_information26
,p_per_information27 => g_per_rec.per_information27
,p_per_information28 => g_per_rec.per_information28
,p_per_information29 => g_per_rec.per_information29
,p_per_information30 => g_per_rec.per_information30
,p_correspondence_language => g_per_rec.correspondence_language
,p_honors => g_per_rec.honors
,p_on_military_service => g_per_rec.on_military_service
,p_student_status => g_per_rec.student_status
,p_uses_tobacco_flag => g_per_rec.uses_tobacco_flag
,p_coord_ben_no_cvg_flag => g_per_rec.coord_ben_no_cvg_flag
,p_pre_name_adjunct => g_per_rec.pre_name_adjunct
,p_suffix => g_per_rec.suffix
,p_town_of_birth => g_per_rec.town_of_birth
,p_region_of_birth => g_per_rec.region_of_birth
,p_country_of_birth => g_per_rec.country_of_birth
,p_global_person_id => g_per_rec.global_person_id
--,p_vendor_name => null
--$ In Batch Lines Benefit Group Name has to be passed instead of ID
,p_benefit_group => g_benefit_grp_name
,p_language_code => Userenv('lang')
);
,p_datetrack_update_mode in varchar2
,p_cur_rec in per_all_people_f%rowtype
,p_adjusted_svc_date in date default null -- Added by pkagrawa
) is
l_proc_name constant varchar2(150):= g_pkg ||'InsUpd_Batch_Employee';
Hrdpp_Create_Employee.Insert_Batch_Lines
(p_batch_id => p_batch_id
,p_data_pump_batch_line_id => p_data_pump_batch_line_id
,p_user_sequence => p_user_sequence
,p_link_value => p_link_value
,p_person_user_key => p_person_user_key
,p_assignment_user_key => p_assignment_user_key
,p_user_person_type => p_user_person_type
,p_hire_date => g_per_rec.start_date
,p_last_name => g_per_rec.last_name
,p_sex => g_per_rec.sex
,p_date_employee_data_verified => g_per_rec.date_employee_data_verified
,p_date_of_birth => g_per_rec.date_of_birth
,p_email_address => g_per_rec.email_address
,p_employee_number => g_per_rec.employee_number
,p_expense_check_send_to_addres => g_per_rec.expense_check_send_to_address
,p_first_name => g_per_rec.first_name
,p_known_as => g_per_rec.known_as
,p_marital_status => g_per_rec.marital_status
,p_middle_names => g_per_rec.middle_names
,p_nationality => g_per_rec.nationality
,p_national_identifier => g_per_rec.national_identifier
,p_previous_last_name => g_per_rec.previous_last_name
,p_registered_disabled_flag => g_per_rec.registered_disabled_flag
,p_title => g_per_rec.title
,p_attribute_category => g_per_rec.attribute_category
,p_attribute1 => g_per_rec.attribute1
,p_attribute2 => g_per_rec.attribute2
,p_attribute3 => g_per_rec.attribute3
,p_attribute4 => g_per_rec.attribute4
,p_attribute5 => g_per_rec.attribute5
,p_attribute6 => g_per_rec.attribute6
,p_attribute7 => g_per_rec.attribute7
,p_attribute8 => g_per_rec.attribute8
,p_attribute9 => g_per_rec.attribute9
,p_attribute10 => g_per_rec.attribute10
,p_attribute11 => g_per_rec.attribute11
,p_attribute12 => g_per_rec.attribute12
,p_attribute13 => g_per_rec.attribute13
,p_attribute14 => g_per_rec.attribute14
,p_attribute15 => g_per_rec.attribute15
,p_attribute16 => g_per_rec.attribute16
,p_attribute17 => g_per_rec.attribute17
,p_attribute18 => g_per_rec.attribute18
,p_attribute19 => g_per_rec.attribute19
,p_attribute20 => g_per_rec.attribute20
,p_attribute21 => g_per_rec.attribute21
,p_attribute22 => g_per_rec.attribute22
,p_attribute23 => g_per_rec.attribute23
,p_attribute24 => g_per_rec.attribute24
,p_attribute25 => g_per_rec.attribute25
,p_attribute26 => g_per_rec.attribute26
,p_attribute27 => g_per_rec.attribute27
,p_attribute28 => g_per_rec.attribute28
,p_attribute29 => g_per_rec.attribute29
,p_attribute30 => g_per_rec.attribute30
,p_per_information_category => g_per_rec.per_information_category
,p_per_information1 => g_per_rec.per_information1
,p_per_information2 => g_per_rec.per_information2
,p_per_information3 => g_per_rec.per_information3
,p_per_information4 => g_per_rec.per_information4
,p_per_information5 => g_per_rec.per_information5
,p_per_information6 => g_per_rec.per_information6
,p_per_information7 => g_per_rec.per_information7
,p_per_information8 => g_per_rec.per_information8
,p_per_information9 => g_per_rec.per_information9
,p_per_information10 => g_per_rec.per_information10
,p_per_information11 => g_per_rec.per_information11
,p_per_information12 => g_per_rec.per_information12
,p_per_information13 => g_per_rec.per_information13
,p_per_information14 => g_per_rec.per_information14
,p_per_information15 => g_per_rec.per_information15
,p_per_information16 => g_per_rec.per_information16
,p_per_information17 => g_per_rec.per_information17
,p_per_information18 => g_per_rec.per_information18
,p_per_information19 => g_per_rec.per_information19
,p_per_information20 => g_per_rec.per_information20
,p_per_information21 => g_per_rec.per_information21
,p_per_information22 => g_per_rec.per_information22
,p_per_information23 => g_per_rec.per_information23
,p_per_information24 => g_per_rec.per_information24
,p_per_information25 => g_per_rec.per_information25
,p_per_information26 => g_per_rec.per_information26
,p_per_information27 => g_per_rec.per_information27
,p_per_information28 => g_per_rec.per_information28
,p_per_information29 => g_per_rec.per_information29
,p_per_information30 => g_per_rec.per_information30
,p_date_of_death => g_per_rec.date_of_death
,p_background_check_status => g_per_rec.background_check_status
,p_background_date_check => g_per_rec.background_date_check
,p_blood_type => g_per_rec.blood_type
,p_fast_path_employee => g_per_rec.fast_path_employee
,p_fte_capacity => g_per_rec.fte_capacity
,p_honors => g_per_rec.honors
,p_internal_location => g_per_rec.internal_location
,p_last_medical_test_by => g_per_rec.last_medical_test_by
,p_last_medical_test_date => g_per_rec.last_medical_test_date
,p_mailstop => g_per_rec.mailstop
,p_office_number => g_per_rec.office_number
,p_on_military_service => g_per_rec.on_military_service
,p_pre_name_adjunct => g_per_rec.pre_name_adjunct
,p_projected_start_date => g_per_rec.projected_start_date
,p_resume_exists => g_per_rec.resume_exists
,p_resume_last_updated => g_per_rec.resume_last_updated
,p_second_passport_exists => g_per_rec.second_passport_exists
,p_student_status => g_per_rec.student_status
,p_work_schedule => g_per_rec.work_schedule
,p_suffix => g_per_rec.suffix
,p_receipt_of_death_cert_date => g_per_rec.receipt_of_death_cert_date
,p_coord_ben_med_pln_no => g_per_rec.coord_ben_med_pln_no
,p_coord_ben_no_cvg_flag => g_per_rec.coord_ben_no_cvg_flag
,p_coord_ben_med_ext_er => g_per_rec.coord_ben_med_ext_er
,p_coord_ben_med_pl_name => g_per_rec.coord_ben_med_pl_name
,p_coord_ben_med_insr_crr_name => g_per_rec.coord_ben_med_insr_crr_name
,p_coord_ben_med_insr_crr_ident => g_per_rec.coord_ben_med_insr_crr_ident
,p_coord_ben_med_cvg_strt_dt => g_per_rec.coord_ben_med_cvg_strt_dt
,p_coord_ben_med_cvg_end_dt => g_per_rec.coord_ben_med_cvg_end_dt
,p_uses_tobacco_flag => g_per_rec.uses_tobacco_flag
,p_dpdnt_adoption_date => g_per_rec.dpdnt_adoption_date
,p_dpdnt_vlntry_svce_flag => g_per_rec.dpdnt_vlntry_svce_flag
,p_original_date_of_hire => g_per_rec.original_date_of_hire
,p_town_of_birth => g_per_rec.town_of_birth
,p_region_of_birth => g_per_rec.region_of_birth
,p_country_of_birth => g_per_rec.country_of_birth
,p_global_person_id => g_per_rec.global_person_id
,p_party_id => g_per_rec.party_id
,p_correspondence_language => g_per_rec.correspondence_language
--$ In Batch Lines Benefit Group Name has to be passed instead of ID
,p_benefit_group => g_benefit_grp_name
,p_language_code => Userenv('lang')
--,p_vendor_name => null
,p_adjusted_svc_date => p_adjusted_svc_date --Added By pkagrawa
);
Hrdpp_Hire_Into_Job.Insert_Batch_Lines
(p_batch_id => p_batch_id
,p_user_sequence => p_user_sequence
,p_link_value => p_link_value
,p_effective_date => g_per_rec.start_date
,p_object_version_number => g_per_rec.object_version_number
,p_employee_number => g_per_rec.employee_number
,p_datetrack_update_mode => p_datetrack_update_mode
,p_national_identifier => g_per_rec.national_identifier
,p_per_information7 => null
,p_person_user_key => p_person_user_key
,p_assignment_user_key => p_assignment_user_key
,p_user_person_type => p_user_person_type
,p_language_code => Userenv('LANG')
);
'Hrdpp_Update_Person.Insert_Batch_Lines', 7);
,p_datetrack_update_mode => 'CORRECTION'
);
select *
from per_addresses pad
where pad.person_id = c_person_id
and pad.business_group_id = c_business_group_id
and pad.primary_flag = c_primary_flag
and c_date_from between pad.date_from
and NVL(pad.date_to, c_date_from);
select *
from per_addresses pad
where pad.business_group_id = c_business_group_id
and pad.person_id = c_person_id
and pad.primary_flag = c_primary_flag
and pad.STYLE = c_style
and nvl(pad.ADDRESS_TYPE,0) = nvl(c_address_type,nvl(pad.ADDRESS_TYPE,0))
and trunc(pad.date_from) = trunc(c_date_from);
Hrdpp_Create_Person_Address.Insert_Batch_Lines
(p_batch_id => p_batch_id
,p_user_sequence => p_user_sequence
,p_link_value => p_link_value
,p_effective_date => g_add_rec.date_from
,p_pradd_ovlapval_override => l_pradd_ovlapval_override
,p_validate_county => true
,p_primary_flag => nvl(g_add_rec.primary_flag, 'Y')
,p_address_user_key => p_address_user_key
,p_person_user_key => p_person_user_key
,p_style => g_add_rec.style
,p_date_from => g_add_rec.date_from
,p_date_to => g_add_rec.date_to
,p_address_type => g_add_rec.address_type
,p_address_line1 => g_add_rec.address_line1
,p_address_line2 => g_add_rec.address_line2
,p_address_line3 => g_add_rec.address_line3
,p_town_or_city => g_add_rec.town_or_city
,p_region_1 => g_add_rec.region_1
,p_region_2 => g_add_rec.region_2
,p_region_3 => g_add_rec.region_3
,p_postal_code => g_add_rec.postal_code
,p_telephone_number_1 => g_add_rec.telephone_number_1
,p_telephone_number_2 => g_add_rec.telephone_number_2
,p_telephone_number_3 => g_add_rec.telephone_number_3
,p_addr_attribute_category => g_add_rec.addr_attribute_category
,p_addr_attribute1 => g_add_rec.addr_attribute1
,p_addr_attribute2 => g_add_rec.addr_attribute2
,p_addr_attribute3 => g_add_rec.addr_attribute3
,p_addr_attribute4 => g_add_rec.addr_attribute4
,p_addr_attribute5 => g_add_rec.addr_attribute5
,p_addr_attribute6 => g_add_rec.addr_attribute6
,p_addr_attribute7 => g_add_rec.addr_attribute7
,p_addr_attribute8 => g_add_rec.addr_attribute8
,p_addr_attribute9 => g_add_rec.addr_attribute9
,p_addr_attribute10 => g_add_rec.addr_attribute10
,p_addr_attribute11 => g_add_rec.addr_attribute11
,p_addr_attribute12 => g_add_rec.addr_attribute12
,p_addr_attribute13 => g_add_rec.addr_attribute13
,p_addr_attribute14 => g_add_rec.addr_attribute14
,p_addr_attribute15 => g_add_rec.addr_attribute15
,p_addr_attribute16 => g_add_rec.addr_attribute16
,p_addr_attribute17 => g_add_rec.addr_attribute17
,p_addr_attribute18 => g_add_rec.addr_attribute18
,p_addr_attribute19 => g_add_rec.addr_attribute19
,p_addr_attribute20 => g_add_rec.addr_attribute20
,p_add_information13 => g_add_rec.add_information13
,p_add_information14 => g_add_rec.add_information14
,p_add_information15 => g_add_rec.add_information15
,p_add_information16 => g_add_rec.add_information16
,p_add_information17 => g_add_rec.add_information17
,p_add_information18 => g_add_rec.add_information18
,p_add_information19 => g_add_rec.add_information19
,p_add_information20 => g_add_rec.add_information20
,p_party_id => g_add_rec.party_id
,p_country => g_add_rec.country
);
hr_utility.set_location(' HrDpp_Update_Person_Address.Insert_Batch_Lines: ' , 51);
HrDpp_Update_Person_Address.Insert_Batch_Lines
(p_batch_id => p_batch_id
,p_user_sequence => p_user_sequence
,p_link_value => p_link_value
--,p_data_pump_batch_line_id => p_data_pump_batch_line_id
--,p_data_pump_business_grp_name =>
,p_address_user_key => p_address_user_key
,p_effective_date => g_add_rec.date_from
,p_validate_county => true
,p_date_from => g_add_rec.date_from
,p_date_to => g_add_rec.date_to
,p_primary_flag => nvl(g_add_rec.primary_flag, 'Y')
,p_address_type => g_add_rec.address_type
,p_address_line1 => g_add_rec.address_line1
,p_address_line2 => g_add_rec.address_line2
,p_address_line3 => g_add_rec.address_line3
,p_town_or_city => g_add_rec.town_or_city
,p_region_1 => g_add_rec.region_1
,p_region_2 => g_add_rec.region_2
,p_region_3 => g_add_rec.region_3
,p_postal_code => g_add_rec.postal_code
,p_telephone_number_1 => g_add_rec.telephone_number_1
,p_telephone_number_2 => g_add_rec.telephone_number_2
,p_telephone_number_3 => g_add_rec.telephone_number_3
,p_addr_attribute_category => g_add_rec.addr_attribute_category
,p_addr_attribute1 => g_add_rec.addr_attribute1
,p_addr_attribute2 => g_add_rec.addr_attribute2
,p_addr_attribute3 => g_add_rec.addr_attribute3
,p_addr_attribute4 => g_add_rec.addr_attribute4
,p_addr_attribute5 => g_add_rec.addr_attribute5
,p_addr_attribute6 => g_add_rec.addr_attribute6
,p_addr_attribute7 => g_add_rec.addr_attribute7
,p_addr_attribute8 => g_add_rec.addr_attribute8
,p_addr_attribute9 => g_add_rec.addr_attribute9
,p_addr_attribute10 => g_add_rec.addr_attribute10
,p_addr_attribute11 => g_add_rec.addr_attribute11
,p_addr_attribute12 => g_add_rec.addr_attribute12
,p_addr_attribute13 => g_add_rec.addr_attribute13
,p_addr_attribute14 => g_add_rec.addr_attribute14
,p_addr_attribute15 => g_add_rec.addr_attribute15
,p_addr_attribute16 => g_add_rec.addr_attribute16
,p_addr_attribute17 => g_add_rec.addr_attribute17
,p_addr_attribute18 => g_add_rec.addr_attribute18
,p_addr_attribute19 => g_add_rec.addr_attribute19
,p_addr_attribute20 => g_add_rec.addr_attribute20
,p_party_id => g_add_rec.party_id
,p_country => g_add_rec.country
);
Hrdpp_Create_Person_Address.Insert_Batch_Lines
(p_batch_id => p_batch_id
,p_user_sequence => p_user_sequence
,p_link_value => p_link_value
,p_effective_date => g_add_rec.date_from
,p_pradd_ovlapval_override => l_pradd_ovlapval_override
,p_validate_county => true
,p_primary_flag => nvl(g_add_rec.primary_flag, 'Y')
,p_address_user_key => p_address_user_key
,p_person_user_key => p_person_user_key
,p_style => g_add_rec.style
,p_date_from => g_add_rec.date_from
,p_date_to => g_add_rec.date_to
,p_address_type => g_add_rec.address_type
,p_address_line1 => g_add_rec.address_line1
,p_address_line2 => g_add_rec.address_line2
,p_address_line3 => g_add_rec.address_line3
,p_town_or_city => g_add_rec.town_or_city
,p_region_1 => g_add_rec.region_1
,p_region_2 => g_add_rec.region_2
,p_region_3 => g_add_rec.region_3
,p_postal_code => g_add_rec.postal_code
,p_telephone_number_1 => g_add_rec.telephone_number_1
,p_telephone_number_2 => g_add_rec.telephone_number_2
,p_telephone_number_3 => g_add_rec.telephone_number_3
,p_addr_attribute_category => g_add_rec.addr_attribute_category
,p_addr_attribute1 => g_add_rec.addr_attribute1
,p_addr_attribute2 => g_add_rec.addr_attribute2
,p_addr_attribute3 => g_add_rec.addr_attribute3
,p_addr_attribute4 => g_add_rec.addr_attribute4
,p_addr_attribute5 => g_add_rec.addr_attribute5
,p_addr_attribute6 => g_add_rec.addr_attribute6
,p_addr_attribute7 => g_add_rec.addr_attribute7
,p_addr_attribute8 => g_add_rec.addr_attribute8
,p_addr_attribute9 => g_add_rec.addr_attribute9
,p_addr_attribute10 => g_add_rec.addr_attribute10
,p_addr_attribute11 => g_add_rec.addr_attribute11
,p_addr_attribute12 => g_add_rec.addr_attribute12
,p_addr_attribute13 => g_add_rec.addr_attribute13
,p_addr_attribute14 => g_add_rec.addr_attribute14
,p_addr_attribute15 => g_add_rec.addr_attribute15
,p_addr_attribute16 => g_add_rec.addr_attribute16
,p_addr_attribute17 => g_add_rec.addr_attribute17
,p_addr_attribute18 => g_add_rec.addr_attribute18
,p_addr_attribute19 => g_add_rec.addr_attribute19
,p_addr_attribute20 => g_add_rec.addr_attribute20
,p_add_information13 => g_add_rec.add_information13
,p_add_information14 => g_add_rec.add_information14
,p_add_information15 => g_add_rec.add_information15
,p_add_information16 => g_add_rec.add_information16
,p_add_information17 => g_add_rec.add_information17
,p_add_information18 => g_add_rec.add_information18
,p_add_information19 => g_add_rec.add_information19
,p_add_information20 => g_add_rec.add_information20
,p_party_id => g_add_rec.party_id
,p_country => g_add_rec.country
);
hr_utility.set_location(' Hrdpp_Create_Person_Address.Insert_Batch_Lines: ' , 51);
HrDpp_Update_Person_Address.Insert_Batch_Lines
(p_batch_id => p_batch_id
,p_user_sequence => p_user_sequence
,p_link_value => p_link_value
--,p_data_pump_batch_line_id => p_data_pump_batch_line_id
--,p_data_pump_business_grp_name =>
,p_address_user_key => p_address_user_key
,p_effective_date => g_add_rec.date_from
,p_validate_county => true
,p_date_from => g_add_rec.date_from
,p_date_to => g_add_rec.date_to
,p_primary_flag => nvl(g_add_rec.primary_flag, 'Y')
,p_address_type => g_add_rec.address_type
,p_address_line1 => g_add_rec.address_line1
,p_address_line2 => g_add_rec.address_line2
,p_address_line3 => g_add_rec.address_line3
,p_town_or_city => g_add_rec.town_or_city
,p_region_1 => g_add_rec.region_1
,p_region_2 => g_add_rec.region_2
,p_region_3 => g_add_rec.region_3
,p_postal_code => g_add_rec.postal_code
,p_telephone_number_1 => g_add_rec.telephone_number_1
,p_telephone_number_2 => g_add_rec.telephone_number_2
,p_telephone_number_3 => g_add_rec.telephone_number_3
,p_addr_attribute_category => g_add_rec.addr_attribute_category
,p_addr_attribute1 => g_add_rec.addr_attribute1
,p_addr_attribute2 => g_add_rec.addr_attribute2
,p_addr_attribute3 => g_add_rec.addr_attribute3
,p_addr_attribute4 => g_add_rec.addr_attribute4
,p_addr_attribute5 => g_add_rec.addr_attribute5
,p_addr_attribute6 => g_add_rec.addr_attribute6
,p_addr_attribute7 => g_add_rec.addr_attribute7
,p_addr_attribute8 => g_add_rec.addr_attribute8
,p_addr_attribute9 => g_add_rec.addr_attribute9
,p_addr_attribute10 => g_add_rec.addr_attribute10
,p_addr_attribute11 => g_add_rec.addr_attribute11
,p_addr_attribute12 => g_add_rec.addr_attribute12
,p_addr_attribute13 => g_add_rec.addr_attribute13
,p_addr_attribute14 => g_add_rec.addr_attribute14
,p_addr_attribute15 => g_add_rec.addr_attribute15
,p_addr_attribute16 => g_add_rec.addr_attribute16
,p_addr_attribute17 => g_add_rec.addr_attribute17
,p_addr_attribute18 => g_add_rec.addr_attribute18
,p_addr_attribute19 => g_add_rec.addr_attribute19
,p_addr_attribute20 => g_add_rec.addr_attribute20
,p_party_id => g_add_rec.party_id
,p_country => g_add_rec.country
);
select *
from per_addresses pad
where pad.person_id = c_person_id
and pad.business_group_id = c_business_group_id
and pad.primary_flag = c_primary_flag
and c_date_from between pad.date_from
and NVL(pad.date_to, c_date_from);
select *
from per_addresses pad
where pad.business_group_id = c_business_group_id
and pad.person_id = c_person_id
and pad.primary_flag = c_primary_flag
and pad.STYLE = c_style
and nvl(pad.ADDRESS_TYPE,0) = nvl(c_address_type,nvl(pad.ADDRESS_TYPE,0))
and trunc(pad.date_from) = trunc(c_date_from);
,p_action => 'UPDATE'
,p_effective_date => trunc(g_add_rec.date_from)
,p_pradd_ovlapval_override => false
,p_primary_flag => g_add_rec.primary_flag -- 'Y'
,p_validate_county => true
,p_HR_address_id => g_add_rec.address_id
,p_HR_object_version_number => g_add_rec.object_version_number
);
,p_action => 'UPDATE'
,p_effective_date => trunc(g_add_rec.date_from)
,p_pradd_ovlapval_override => false
,p_primary_flag => g_add_rec.primary_flag -- 'N'
,p_validate_county => true
,p_HR_address_id => g_add_rec.address_id
,p_HR_object_version_number => g_add_rec.object_version_number
);
l_datetrack_update_mode varchar2(50);
l_dt_update boolean;
,p_update => l_dt_update
,p_update_override => l_dt_upd_override
,p_update_change_insert => l_upd_chg_ins
);
if l_dt_update then
l_datetrack_update_mode := 'UPDATE';
l_datetrack_update_mode := 'CORRECTION';
l_datetrack_update_mode := 'CORRECTION';
hr_utility.set_location(' l_datetrack_update_mode: ' ||
l_datetrack_update_mode, 30);
Hr_Person_Api.Update_Person
(p_validate => p_validate
,p_effective_date => p_effective_date
,p_datetrack_update_mode => l_datetrack_update_mode
,p_person_id => l_cur_per_rec.person_id
,p_party_id => nvl(g_per_rec.party_id,
l_cur_per_rec.party_id)
,p_object_version_number => l_cur_per_rec.object_version_number
,p_employee_number => l_cur_per_rec.employee_number
,p_applicant_number => nvl(g_per_rec.applicant_number,
l_cur_per_rec.applicant_number)
,p_npw_number => nvl(g_per_rec.npw_number,
l_cur_per_rec.npw_number)
,p_last_name => l_per_rec.last_name
,p_first_name => l_per_rec.first_name
,p_date_of_birth => l_per_rec.date_of_birth
,p_marital_status => l_per_rec.marital_status
,p_middle_names => l_per_rec.middle_names
,p_sex => l_per_rec.sex
,p_title => l_per_rec.title
,p_nationality => l_per_rec.nationality
,p_national_identifier => l_per_rec.national_identifier
,p_previous_last_name => l_per_rec.previous_last_name
,p_known_as => l_per_rec.known_as
,p_email_address => l_per_rec.email_address
,p_registered_disabled_flag => l_per_rec.registered_disabled_flag
,p_date_employee_data_verified => l_per_rec.date_employee_data_verified
,p_expense_check_send_to_addres => l_per_rec.expense_check_send_to_address
--Added by Dbansal
,p_comments => g_per_comments
-- Person DDF
,p_per_information_category => l_per_rec.per_information_category
,p_per_information1 => l_per_rec.per_information1
,p_per_information2 => l_per_rec.per_information2
,p_per_information3 => l_per_rec.per_information3
,p_per_information4 => l_per_rec.per_information4
,p_per_information5 => l_per_rec.per_information5
,p_per_information6 => l_per_rec.per_information6
,p_per_information7 => l_per_rec.per_information7
,p_per_information8 => l_per_rec.per_information8
,p_per_information9 => l_per_rec.per_information9
,p_per_information10 => l_per_rec.per_information10
,p_per_information11 => l_per_rec.per_information11
,p_per_information12 => l_per_rec.per_information12
,p_per_information13 => l_per_rec.per_information13
,p_per_information14 => l_per_rec.per_information14
,p_per_information15 => l_per_rec.per_information15
,p_per_information16 => l_per_rec.per_information16
,p_per_information17 => l_per_rec.per_information17
,p_per_information18 => l_per_rec.per_information18
,p_per_information19 => l_per_rec.per_information19
,p_per_information20 => l_per_rec.per_information20
,p_per_information21 => l_per_rec.per_information21
,p_per_information22 => l_per_rec.per_information22
,p_per_information23 => l_per_rec.per_information23
,p_per_information24 => l_per_rec.per_information24
,p_per_information25 => l_per_rec.per_information25
,p_per_information26 => l_per_rec.per_information26
,p_per_information27 => l_per_rec.per_information27
,p_per_information28 => l_per_rec.per_information28
,p_per_information29 => l_per_rec.per_information29
,p_per_information30 => l_per_rec.per_information30
-- Person DF
,p_attribute_category => l_per_rec.attribute_category
,p_attribute1 => l_per_rec.attribute1
,p_attribute2 => l_per_rec.attribute2
,p_attribute3 => l_per_rec.attribute3
,p_attribute4 => l_per_rec.attribute4
,p_attribute5 => l_per_rec.attribute5
,p_attribute6 => l_per_rec.attribute6
,p_attribute7 => l_per_rec.attribute7
,p_attribute8 => l_per_rec.attribute8
,p_attribute9 => l_per_rec.attribute9
,p_attribute10 => l_per_rec.attribute10
,p_attribute11 => l_per_rec.attribute11
,p_attribute12 => l_per_rec.attribute12
,p_attribute13 => l_per_rec.attribute13
,p_attribute14 => l_per_rec.attribute14
,p_attribute15 => l_per_rec.attribute15
,p_attribute16 => l_per_rec.attribute16
,p_attribute17 => l_per_rec.attribute17
,p_attribute18 => l_per_rec.attribute18
,p_attribute19 => l_per_rec.attribute19
,p_attribute20 => l_per_rec.attribute20
,p_attribute21 => l_per_rec.attribute21
,p_attribute22 => l_per_rec.attribute22
,p_attribute23 => l_per_rec.attribute23
,p_attribute24 => l_per_rec.attribute24
,p_attribute25 => l_per_rec.attribute25
,p_attribute26 => l_per_rec.attribute26
,p_attribute27 => l_per_rec.attribute27
,p_attribute28 => l_per_rec.attribute28
,p_attribute29 => l_per_rec.attribute29
,p_attribute30 => l_per_rec.attribute30
,p_date_of_death => l_per_rec.date_of_death
,p_background_check_status => l_per_rec.background_check_status
,p_background_date_check => l_per_rec.background_date_check
,p_blood_type => l_per_rec.blood_type
,p_correspondence_language => l_per_rec.correspondence_language
,p_fte_capacity => l_per_rec.fte_capacity
,p_hold_applicant_date_until => l_per_rec.hold_applicant_date_until
,p_honors => l_per_rec.honors
,p_internal_location => l_per_rec.internal_location
,p_last_medical_test_by => l_per_rec.last_medical_test_by
,p_last_medical_test_date => l_per_rec.last_medical_test_date
,p_mailstop => l_per_rec.mailstop
,p_office_number => l_per_rec.office_number
,p_on_military_service => l_per_rec.on_military_service
,p_pre_name_adjunct => l_per_rec.pre_name_adjunct
,p_projected_start_date => l_per_rec.projected_start_date
,p_rehire_authorizor => l_per_rec.rehire_authorizor
,p_rehire_recommendation => l_per_rec.rehire_recommendation
,p_resume_exists => l_per_rec.resume_exists
,p_resume_last_updated => l_per_rec.resume_last_updated
,p_second_passport_exists => l_per_rec.second_passport_exists
,p_student_status => l_per_rec.student_status
,p_work_schedule => l_per_rec.work_schedule
,p_rehire_reason => l_per_rec.rehire_reason
,p_suffix => l_per_rec.suffix
,p_benefit_group_id => l_per_rec.benefit_group_id
,p_receipt_of_death_cert_date => l_per_rec.receipt_of_death_cert_date
,p_coord_ben_med_pln_no => l_per_rec.coord_ben_med_pln_no
,p_coord_ben_no_cvg_flag => l_per_rec.coord_ben_no_cvg_flag
,p_coord_ben_med_ext_er => l_per_rec.coord_ben_med_ext_er
,p_coord_ben_med_pl_name => l_per_rec.coord_ben_med_pl_name
,p_coord_ben_med_insr_crr_name => l_per_rec.coord_ben_med_insr_crr_name
,p_coord_ben_med_insr_crr_ident => l_per_rec.coord_ben_med_insr_crr_ident
,p_coord_ben_med_cvg_strt_dt => l_per_rec.coord_ben_med_cvg_strt_dt
,p_coord_ben_med_cvg_end_dt => l_per_rec.coord_ben_med_cvg_end_dt
,p_uses_tobacco_flag => l_per_rec.uses_tobacco_flag
,p_dpdnt_adoption_date => l_per_rec.dpdnt_adoption_date
,p_dpdnt_vlntry_svce_flag => l_per_rec.dpdnt_vlntry_svce_flag
,p_original_date_of_hire => l_per_rec.original_date_of_hire
,p_town_of_birth => l_per_rec.town_of_birth
,p_region_of_birth => l_per_rec.region_of_birth
,p_country_of_birth => l_per_rec.country_of_birth
,p_global_person_id => l_per_rec.global_person_id
-- Out Variables
,p_effective_start_date => p_updper_api_out.effective_start_date
,p_effective_end_date => p_updper_api_out.effective_end_date
,p_full_name => p_updper_api_out.full_name
,p_comment_id => p_updper_api_out.comment_id
,p_name_combination_warning => p_updper_api_out.name_combination_warning
,p_assign_payroll_warning => p_updper_api_out.assign_payroll_warning
,p_orig_hire_warning => p_updper_api_out.orig_hire_warning
,p_adjusted_svc_date => p_adjusted_svc_date --Uncommented by pkagrawa
);
hr_utility.set_location('..Future Update exists for the Student Employee', 60);
select paf.assignment_id
,ppf.person_id
,ppf.object_version_number per_ovn
,paf.object_version_number asg_ovn
,paf.effective_start_date -- For Hiring an applicant on the next day
from per_all_assignments_f paf
,per_all_people_f ppf
where paf.person_id = c_person_id
and paf.business_group_id = c_business_group_id
and paf.assignment_type = c_asg_type
and paf.assignment_id = c_assignment_id
and paf.person_id = ppf.person_id
and c_effective_date between ppf.effective_start_date
and ppf.effective_end_date
and c_effective_date between paf.effective_start_date
and paf.effective_end_date;
select date1-date2 from dual;
l_datetrack_update_mode varchar2(50);
l_dt_update boolean;
,p_update => l_dt_update
,p_update_override => l_dt_upd_override
,p_update_change_insert => l_upd_chg_ins
);
,p_update => l_dt_update
,p_update_override => l_dt_upd_override
,p_update_change_insert => l_upd_chg_ins
);
if l_dt_update then
l_datetrack_update_mode := 'UPDATE';
l_datetrack_update_mode := 'CORRECTION';
l_datetrack_update_mode := 'CORRECTION';
hr_utility.set_location(' DT Mode for Update of Appl Asg : ' ||
l_datetrack_update_mode, 50);
,p_datetrack_update_mode => l_datetrack_update_mode
,p_assignment_id => p_assignment_id
,p_object_version_number => l_perasg_rec.asg_ovn
,p_assignment_status_type_id => l_asg_status_rec.assignment_status_type_id
,p_change_reason => null
,p_effective_start_date => l_appl_asg_start_date
,p_effective_end_date => l_appl_asg_end_date
);
,p_update => l_dt_update
,p_update_override => l_dt_upd_override
,p_update_change_insert => l_upd_chg_ins
);
if l_dt_update then
l_datetrack_update_mode := 'UPDATE';
l_datetrack_update_mode := 'CORRECTION';
l_datetrack_update_mode := 'CORRECTION';
hr_utility.set_location(' l_datetrack_update_mode: ' ||
l_datetrack_update_mode, 100);
l_datetrack_update_mode := 'CORRECTION';
select paf.assignment_id,
ppf.object_version_number
from per_all_assignments_f paf
,per_all_people_f ppf
where paf.person_id = c_person_id
and paf.business_group_id = c_business_group_id
and paf.person_id = ppf.person_id
and c_effective_date between ppf.effective_start_date
and ppf.effective_end_date
and c_effective_date between paf.effective_start_date
and paf.effective_end_date;
l_datetrack_update_mode varchar2(50);
l_dt_update boolean;
,p_update => l_dt_update
,p_update_override => l_dt_upd_override
,p_update_change_insert => l_upd_chg_ins
);
if l_dt_update then
l_datetrack_update_mode := 'UPDATE';
l_datetrack_update_mode := 'CORRECTION';
l_datetrack_update_mode := 'CORRECTION';
hr_utility.set_location('l_datetrack_update_mode: ' ||
l_datetrack_update_mode, 30);
,p_datetrack_update_mode => l_datetrack_update_mode
,p_person_type_id => g_per_rec.person_type_id
,p_national_identifier => g_per_rec.national_identifier
,p_per_information7 => g_per_rec.per_information7
-- Out Variables
,p_effective_start_date => p_HireToJobapi_out.effective_start_date
,p_effective_end_date => p_HireToJobapi_out.effective_end_date
,p_assign_payroll_warning => p_HireToJobapi_out.assign_payroll_warning
,p_orig_hire_warning => p_HireToJobapi_out.orig_hire_warning
);
l_datetrack_update_mode := 'CORRECTION';
l_error_msg := 'This person cannot be created/updated in HRMS as the '||
'Person has future changes beyond the date: '||p_hire_date;
hr_utility.set_location('..Future Update exists for the Student Employee', 60);
,p_resume_last_updated => g_per_rec.resume_last_updated
,p_second_passport_exists => g_per_rec.second_passport_exists
,p_student_status => g_per_rec.student_status
,p_work_schedule => g_per_rec.work_schedule
,p_suffix => g_per_rec.suffix
,p_benefit_group_id => g_per_rec.benefit_group_id
,p_receipt_of_death_cert_date => g_per_rec.receipt_of_death_cert_date
,p_coord_ben_med_pln_no => g_per_rec.coord_ben_med_pln_no
,p_coord_ben_no_cvg_flag => g_per_rec.coord_ben_no_cvg_flag
,p_coord_ben_med_ext_er => g_per_rec.coord_ben_med_ext_er
,p_coord_ben_med_pl_name => g_per_rec.coord_ben_med_pl_name
,p_coord_ben_med_insr_crr_name => g_per_rec.coord_ben_med_insr_crr_name
,p_coord_ben_med_insr_crr_ident => g_per_rec.coord_ben_med_insr_crr_ident
,p_coord_ben_med_cvg_strt_dt => g_per_rec.coord_ben_med_cvg_strt_dt
,p_coord_ben_med_cvg_end_dt => g_per_rec.coord_ben_med_cvg_end_dt
,p_uses_tobacco_flag => g_per_rec.uses_tobacco_flag
,p_dpdnt_adoption_date => g_per_rec.dpdnt_adoption_date
,p_dpdnt_vlntry_svce_flag => g_per_rec.dpdnt_vlntry_svce_flag
,p_original_date_of_hire => g_per_rec.original_date_of_hire
,p_adjusted_svc_date => p_adjusted_svc_date
,p_town_of_birth => g_per_rec.town_of_birth
,p_region_of_birth => g_per_rec.region_of_birth
,p_country_of_birth => g_per_rec.country_of_birth
,p_global_person_id => g_per_rec.global_person_id
,p_party_id => g_per_rec.party_id
-- Out Variables
,p_person_id => p_emp_api_out.person_id
,p_assignment_id => p_emp_api_out.assignment_id
,p_per_object_version_number => p_emp_api_out.per_object_version_number
,p_asg_object_version_number => p_emp_api_out.asg_object_version_number
,p_per_effective_start_date => p_emp_api_out.per_effective_start_date
,p_per_effective_end_date => p_emp_api_out.per_effective_end_date
,p_full_name => p_emp_api_out.full_name
,p_per_comment_id => p_emp_api_out.per_comment_id
,p_assignment_sequence => p_emp_api_out.assignment_sequence
,p_assignment_number => p_emp_api_out.assignment_number
,p_name_combination_warning => p_emp_api_out.name_combination_warning
,p_assign_payroll_warning => p_emp_api_out.assign_payroll_warning
,p_orig_hire_warning => p_emp_api_out.orig_hire_warning
);
l_datetrack_update_mode varchar2(150);
(p_datetrack_update_mode => l_datetrack_update_mode
);
,p_resume_last_updated => g_per_rec.resume_last_updated
,p_student_status => g_per_rec.student_status
,p_work_schedule => g_per_rec.work_schedule
,p_suffix => g_per_rec.suffix
,p_date_of_death => g_per_rec.date_of_death
,p_benefit_group_id => g_per_rec.benefit_group_id
,p_receipt_of_death_cert_date => g_per_rec.receipt_of_death_cert_date
,p_coord_ben_med_pln_no => g_per_rec.coord_ben_med_pln_no
,p_coord_ben_no_cvg_flag => g_per_rec.coord_ben_no_cvg_flag
,p_uses_tobacco_flag => g_per_rec.uses_tobacco_flag
,p_dpdnt_adoption_date => g_per_rec.dpdnt_adoption_date
,p_dpdnt_vlntry_svce_flag => g_per_rec.dpdnt_vlntry_svce_flag
,p_original_date_of_hire => g_per_rec.original_date_of_hire
,p_town_of_birth => g_per_rec.town_of_birth
,p_region_of_birth => g_per_rec.region_of_birth
,p_country_of_birth => g_per_rec.country_of_birth
,p_global_person_id => g_per_rec.global_person_id
,p_party_id => g_per_rec.party_id
-- DF
,p_attribute_category => g_per_rec.attribute_category
,p_attribute1 => g_per_rec.attribute1
,p_attribute2 => g_per_rec.attribute2
,p_attribute3 => g_per_rec.attribute3
,p_attribute4 => g_per_rec.attribute4
,p_attribute5 => g_per_rec.attribute5
,p_attribute6 => g_per_rec.attribute6
,p_attribute7 => g_per_rec.attribute7
,p_attribute8 => g_per_rec.attribute8
,p_attribute9 => g_per_rec.attribute9
,p_attribute10 => g_per_rec.attribute10
,p_attribute11 => g_per_rec.attribute11
,p_attribute12 => g_per_rec.attribute12
,p_attribute13 => g_per_rec.attribute13
,p_attribute14 => g_per_rec.attribute14
,p_attribute15 => g_per_rec.attribute15
,p_attribute16 => g_per_rec.attribute16
,p_attribute17 => g_per_rec.attribute17
,p_attribute18 => g_per_rec.attribute18
,p_attribute19 => g_per_rec.attribute19
,p_attribute20 => g_per_rec.attribute20
,p_attribute21 => g_per_rec.attribute21
,p_attribute22 => g_per_rec.attribute22
,p_attribute23 => g_per_rec.attribute23
,p_attribute24 => g_per_rec.attribute24
,p_attribute25 => g_per_rec.attribute25
,p_attribute26 => g_per_rec.attribute26
,p_attribute27 => g_per_rec.attribute27
,p_attribute28 => g_per_rec.attribute28
,p_attribute29 => g_per_rec.attribute29
,p_attribute30 => g_per_rec.attribute30
-- DDF
,p_per_information_category => g_per_rec.per_information_category
,p_per_information1 => g_per_rec.per_information1
,p_per_information2 => g_per_rec.per_information2
,p_per_information3 => g_per_rec.per_information3
,p_per_information4 => g_per_rec.per_information4
,p_per_information5 => g_per_rec.per_information5
,p_per_information6 => g_per_rec.per_information6
,p_per_information7 => g_per_rec.per_information7
,p_per_information8 => g_per_rec.per_information8
,p_per_information9 => g_per_rec.per_information9
,p_per_information10 => g_per_rec.per_information10
,p_per_information11 => g_per_rec.per_information11
,p_per_information12 => g_per_rec.per_information12
,p_per_information13 => g_per_rec.per_information13
,p_per_information14 => g_per_rec.per_information14
,p_per_information15 => g_per_rec.per_information15
,p_per_information16 => g_per_rec.per_information16
,p_per_information17 => g_per_rec.per_information17
,p_per_information18 => g_per_rec.per_information18
,p_per_information19 => g_per_rec.per_information19
,p_per_information20 => g_per_rec.per_information20
,p_per_information21 => g_per_rec.per_information21
,p_per_information22 => g_per_rec.per_information22
,p_per_information23 => g_per_rec.per_information23
,p_per_information24 => g_per_rec.per_information24
,p_per_information25 => g_per_rec.per_information25
,p_per_information26 => g_per_rec.per_information26
,p_per_information27 => g_per_rec.per_information27
,p_per_information28 => g_per_rec.per_information28
,p_per_information29 => g_per_rec.per_information29
,p_per_information30 => g_per_rec.per_information30
-- Out variables
,p_person_id => p_appl_api_out.person_id
,p_assignment_id => p_appl_api_out.assignment_id
,p_application_id => l_application_id
,p_per_object_version_number => p_appl_api_out.per_object_version_number
,p_asg_object_version_number => p_appl_api_out.asg_object_version_number
,p_apl_object_version_number => l_apl_object_version_number
,p_per_effective_start_date => p_appl_api_out.per_effective_start_date
,p_per_effective_end_date => p_appl_api_out.per_effective_end_date
,p_full_name => p_appl_api_out.full_name
,p_per_comment_id => p_appl_api_out.per_comment_id
,p_assignment_sequence => p_appl_api_out.assignment_sequence
,p_name_combination_warning => p_appl_api_out.name_combination_warning
,p_orig_hire_warning => p_appl_api_out.orig_hire_warning
);
,p_datetrack_update_mode in varchar2 default null
,p_effective_date in date
,p_adjusted_svc_date in date default null
,p_per_comments in varchar2 default null
,p_cwk_api_out out nocopy t_hrEmpApi) as
l_apl_object_version_number number(15);
l_datetrack_update_mode varchar2(150);
(p_datetrack_update_mode => l_datetrack_update_mode
);
,p_resume_last_updated => g_per_rec.resume_last_updated
,p_second_passport_exists => g_per_rec.second_passport_exists
,p_sex => g_per_rec.sex
,p_student_status => g_per_rec.student_status
,p_suffix => g_per_rec.suffix
,p_title => g_per_rec.title
,p_town_of_birth => g_per_rec.town_of_birth
,p_uses_tobacco_flag => g_per_rec.uses_tobacco_flag
,p_vendor_id => g_per_rec.vendor_id
,p_work_schedule => g_per_rec.work_schedule
,p_work_telephone => g_per_rec.work_telephone
,p_exp_check_send_to_address => g_per_rec.expense_check_send_to_address
,p_hold_applicant_date_until => g_per_rec.hold_applicant_date_until
,p_date_employee_data_verified => g_per_rec.date_employee_data_verified
,p_benefit_group_id => g_per_rec.benefit_group_id
,p_coord_ben_med_pln_no => g_per_rec.coord_ben_med_pln_no
,p_coord_ben_no_cvg_flag => g_per_rec.coord_ben_no_cvg_flag
,p_original_date_of_hire => g_per_rec.original_date_of_hire
-- DF
,p_attribute_category => g_per_rec.attribute_category
,p_attribute1 => g_per_rec.attribute1
,p_attribute2 => g_per_rec.attribute2
,p_attribute3 => g_per_rec.attribute3
,p_attribute4 => g_per_rec.attribute4
,p_attribute5 => g_per_rec.attribute5
,p_attribute6 => g_per_rec.attribute6
,p_attribute7 => g_per_rec.attribute7
,p_attribute8 => g_per_rec.attribute8
,p_attribute9 => g_per_rec.attribute9
,p_attribute10 => g_per_rec.attribute10
,p_attribute11 => g_per_rec.attribute11
,p_attribute12 => g_per_rec.attribute12
,p_attribute13 => g_per_rec.attribute13
,p_attribute14 => g_per_rec.attribute14
,p_attribute15 => g_per_rec.attribute15
,p_attribute16 => g_per_rec.attribute16
,p_attribute17 => g_per_rec.attribute17
,p_attribute18 => g_per_rec.attribute18
,p_attribute19 => g_per_rec.attribute19
,p_attribute20 => g_per_rec.attribute20
,p_attribute21 => g_per_rec.attribute21
,p_attribute22 => g_per_rec.attribute22
,p_attribute23 => g_per_rec.attribute23
,p_attribute24 => g_per_rec.attribute24
,p_attribute25 => g_per_rec.attribute25
,p_attribute26 => g_per_rec.attribute26
,p_attribute27 => g_per_rec.attribute27
,p_attribute28 => g_per_rec.attribute28
,p_attribute29 => g_per_rec.attribute29
,p_attribute30 => g_per_rec.attribute30
-- DDF
,p_per_information_category => g_per_rec.per_information_category
,p_per_information1 => g_per_rec.per_information1
,p_per_information2 => g_per_rec.per_information2
,p_per_information3 => g_per_rec.per_information3
,p_per_information4 => g_per_rec.per_information4
,p_per_information5 => g_per_rec.per_information5
,p_per_information6 => g_per_rec.per_information6
,p_per_information7 => g_per_rec.per_information7
,p_per_information8 => g_per_rec.per_information8
,p_per_information9 => g_per_rec.per_information9
,p_per_information10 => g_per_rec.per_information10
,p_per_information11 => g_per_rec.per_information11
,p_per_information12 => g_per_rec.per_information12
,p_per_information13 => g_per_rec.per_information13
,p_per_information14 => g_per_rec.per_information14
,p_per_information15 => g_per_rec.per_information15
,p_per_information16 => g_per_rec.per_information16
,p_per_information17 => g_per_rec.per_information17
,p_per_information18 => g_per_rec.per_information18
,p_per_information19 => g_per_rec.per_information19
,p_per_information20 => g_per_rec.per_information20
,p_per_information21 => g_per_rec.per_information21
,p_per_information22 => g_per_rec.per_information22
,p_per_information23 => g_per_rec.per_information23
,p_per_information24 => g_per_rec.per_information24
,p_per_information25 => g_per_rec.per_information25
,p_per_information26 => g_per_rec.per_information26
,p_per_information27 => g_per_rec.per_information27
,p_per_information28 => g_per_rec.per_information28
,p_per_information29 => g_per_rec.per_information29
,p_per_information30 => g_per_rec.per_information30
-- Out Variables
,p_person_id => p_cwk_api_out.person_id
,p_assignment_id => p_cwk_api_out.assignment_id
,p_per_object_version_number => p_cwk_api_out.per_object_version_number
,p_asg_object_version_number => p_cwk_api_out.asg_object_version_number
,p_per_effective_start_date => p_cwk_api_out.per_effective_start_date
,p_per_effective_end_date => p_cwk_api_out.per_effective_end_date
,p_full_name => p_cwk_api_out.full_name
,p_comment_id => p_cwk_api_out.per_comment_id
,p_pdp_object_version_number => p_cwk_api_out.pdp_object_version_number
,p_assignment_sequence => p_cwk_api_out.assignment_sequence
,p_assignment_number => p_cwk_api_out.assignment_number
,p_name_combination_warning => p_cwk_api_out.name_combination_warning
);
,p_datetrack_update_mode => l_datetrack_update_mode
-- In/Out Variables
,p_object_version_number => g_per_rec.object_version_number
,p_npw_number => g_per_rec.npw_number
-- Out variables
,p_per_effective_start_date => p_cwk_api_out.per_effective_start_date
,p_per_effective_end_date => p_cwk_api_out.per_effective_end_date
,p_pdp_object_version_number => p_cwk_api_out.pdp_object_version_number
,p_assignment_id => p_cwk_api_out.assignment_id
,p_asg_object_version_number => p_cwk_api_out.asg_object_version_number
,p_assignment_sequence => p_cwk_api_out.assignment_sequence
);
,p_datetrack_update_mode in varchar2
) is
l_proc_name constant varchar2(150):= g_pkg ||'Upd_Batch_Employee_Asg';
Hrdpp_Update_Emp_Asg.Insert_Batch_Lines
(p_batch_id => p_batch_id
,p_user_sequence => p_user_sequence
,p_link_value => p_link_value
,p_datetrack_update_mode => p_datetrack_update_mode
,p_assignment_user_key => p_assignment_user_key
,p_effective_date => nvl(g_asg_rec.effective_start_date,g_per_rec.start_date)
,p_change_reason => g_asg_rec.change_reason
,p_date_probation_end => g_asg_rec.date_probation_end
,p_frequency => g_asg_rec.frequency
,p_internal_address_line => g_asg_rec.internal_address_line
,p_manager_flag => g_asg_rec.manager_flag
,p_normal_hours => g_asg_rec.normal_hours
,p_perf_review_period => g_asg_rec.perf_review_period
,p_perf_review_period_frequency => g_asg_rec.perf_review_period_frequency
,p_probation_period => g_asg_rec.probation_period
,p_probation_unit => g_asg_rec.probation_unit
,p_sal_review_period => g_asg_rec.sal_review_period
,p_sal_review_period_frequency => g_asg_rec.sal_review_period_frequency
,p_source_type => g_asg_rec.source_type
,p_time_normal_finish => g_asg_rec.time_normal_finish
,p_time_normal_start => g_asg_rec.time_normal_start
,p_bargaining_unit_code => g_asg_rec.bargaining_unit_code
,p_labour_union_member_flag => g_asg_rec.labour_union_member_flag
,p_hourly_salaried_code => g_asg_rec.hourly_salaried_code
,p_ass_attribute_category => g_asg_rec.ass_attribute_category
,p_ass_attribute1 => g_asg_rec.ass_attribute1
,p_ass_attribute2 => g_asg_rec.ass_attribute2
,p_ass_attribute3 => g_asg_rec.ass_attribute3
,p_ass_attribute4 => g_asg_rec.ass_attribute4
,p_ass_attribute5 => g_asg_rec.ass_attribute5
,p_ass_attribute6 => g_asg_rec.ass_attribute6
,p_ass_attribute7 => g_asg_rec.ass_attribute7
,p_ass_attribute8 => g_asg_rec.ass_attribute8
,p_ass_attribute9 => g_asg_rec.ass_attribute9
,p_ass_attribute10 => g_asg_rec.ass_attribute10
,p_ass_attribute11 => g_asg_rec.ass_attribute11
,p_ass_attribute12 => g_asg_rec.ass_attribute12
,p_ass_attribute13 => g_asg_rec.ass_attribute13
,p_ass_attribute14 => g_asg_rec.ass_attribute14
,p_ass_attribute15 => g_asg_rec.ass_attribute15
,p_ass_attribute16 => g_asg_rec.ass_attribute16
,p_ass_attribute17 => g_asg_rec.ass_attribute17
,p_ass_attribute18 => g_asg_rec.ass_attribute18
,p_ass_attribute19 => g_asg_rec.ass_attribute19
,p_ass_attribute20 => g_asg_rec.ass_attribute20
,p_ass_attribute21 => g_asg_rec.ass_attribute21
,p_ass_attribute22 => g_asg_rec.ass_attribute22
,p_ass_attribute23 => g_asg_rec.ass_attribute23
,p_ass_attribute24 => g_asg_rec.ass_attribute24
,p_ass_attribute25 => g_asg_rec.ass_attribute25
,p_ass_attribute26 => g_asg_rec.ass_attribute26
,p_ass_attribute27 => g_asg_rec.ass_attribute27
,p_ass_attribute28 => g_asg_rec.ass_attribute28
,p_ass_attribute29 => g_asg_rec.ass_attribute29
,p_ass_attribute30 => g_asg_rec.ass_attribute30
,p_segment1 => g_scl_rec.segment1
,p_segment2 => g_scl_rec.segment2
,p_segment3 => g_scl_rec.segment3
,p_segment4 => g_scl_rec.segment4
,p_segment5 => g_scl_rec.segment5
,p_segment6 => g_scl_rec.segment6
,p_segment7 => g_scl_rec.segment7
,p_segment8 => g_scl_rec.segment8
,p_segment9 => g_scl_rec.segment9
,P_SEGMENT10 => g_scl_rec.segment10
,P_SEGMENT11 => g_scl_rec.segment11
,P_SEGMENT12 => g_scl_rec.segment12
,P_SEGMENT13 => g_scl_rec.segment13
,P_SEGMENT14 => g_scl_rec.segment14
,P_SEGMENT15 => g_scl_rec.segment15
,P_SEGMENT16 => g_scl_rec.segment16
,P_SEGMENT17 => g_scl_rec.segment17
,P_SEGMENT18 => g_scl_rec.segment18
,P_SEGMENT19 => g_scl_rec.segment19
,P_SEGMENT20 => g_scl_rec.segment20
,P_SEGMENT21 => g_scl_rec.segment21
,P_SEGMENT22 => g_scl_rec.segment22
,P_SEGMENT23 => g_scl_rec.segment23
,P_SEGMENT24 => g_scl_rec.segment24
,P_SEGMENT25 => g_scl_rec.segment25
,P_SEGMENT26 => g_scl_rec.segment26
,P_SEGMENT27 => g_scl_rec.segment27
,P_SEGMENT28 => g_scl_rec.segment28
,P_SEGMENT29 => g_scl_rec.segment29
,P_SEGMENT30 => g_scl_rec.segment30
--$
,P_EMPLOYEE_CATEGORY => g_asg_rec.employee_category
,P_ASSIGNMENT_NUMBER => g_asg_rec.assignment_number
,P_COMMENTS => g_asg_comments
,P_SUPERVISOR_USER_KEY => g_supervisor_user_key
,p_cagr_grade_def_id => null
,p_con_seg_user_name => null
);
hr_utility.set_location('..Inserted into Hrdpp_Update_Emp_Asg', 10);
Hrdpp_Update_Emp_Asg_Criteria.Insert_Batch_Lines
(p_batch_id => p_batch_id
,p_user_sequence => p_user_sequence
,p_link_value => p_link_value
,p_datetrack_update_mode => 'CORRECTION'
,p_assignment_user_key => p_assignment_user_key
,p_effective_date => nvl(g_asg_rec.effective_start_date,g_per_rec.start_date)
,p_segment1 => g_grp_rec.segment1
,p_segment2 => g_grp_rec.segment2
,p_segment3 => g_grp_rec.segment3
,p_segment4 => g_grp_rec.segment4
,p_segment5 => g_grp_rec.segment5
,p_segment6 => g_grp_rec.segment6
,p_segment7 => g_grp_rec.segment7
,p_segment8 => g_grp_rec.segment8
,p_segment9 => g_grp_rec.segment9
,p_segment10 => g_grp_rec.segment10
,p_segment11 => g_grp_rec.segment11
,p_segment12 => g_grp_rec.segment12
,p_segment13 => g_grp_rec.segment13
,p_segment14 => g_grp_rec.segment14
,p_segment15 => g_grp_rec.segment15
,p_segment16 => g_grp_rec.segment16
,p_segment17 => g_grp_rec.segment17
,p_segment18 => g_grp_rec.segment18
,p_segment19 => g_grp_rec.segment19
,p_segment20 => g_grp_rec.segment20
,p_segment21 => g_grp_rec.segment21
,p_segment22 => g_grp_rec.segment22
,p_segment23 => g_grp_rec.segment23
,p_segment24 => g_grp_rec.segment24
,p_segment25 => g_grp_rec.segment25
,p_segment26 => g_grp_rec.segment26
,p_segment27 => g_grp_rec.segment27
,p_segment28 => g_grp_rec.segment28
,p_segment29 => g_grp_rec.segment29
,p_segment30 => g_grp_rec.segment30
,p_concat_segments => g_grp_rec.group_name
,p_special_ceiling_step_id => g_asg_rec.special_ceiling_step_id
,p_people_group_id => g_asg_rec.people_group_id
,p_grade_name => g_wstr_names.grade_name
,p_position_name => g_wstr_names.position_name
,p_job_name => g_wstr_names.job_name
,p_payroll_name => g_wstr_names.payroll_name
,p_location_code => g_wstr_names.location_code
,p_organization_name => g_wstr_names.organization_name
,p_pay_basis_name => g_wstr_names.pay_basis_name
--$
,P_EMPLOYMENT_CATEGORY => g_asg_rec.assignment_category
,p_language_code => userenv('LANG')
,p_con_seg_user_name => null
);
procedure Update_Batch_Assignment
(p_effective_date in date
,p_business_group_id in number
,p_person_id in number
,p_batch_id in number
,p_user_sequence in out nocopy number
,p_link_value in number
,p_assignment_user_key in varchar2
,p_action_mode in varchar2
) as
cursor csr_asg (c_assignment_number in varchar2
,c_person_id in number
,c_business_group_id in number
,c_effective_date in date
) is
select *
from per_all_assignments_f paf
where paf.person_id = c_person_id
and paf.assignment_number = c_assignment_number
and paf.business_group_id = c_business_group_id
and c_effective_date between paf.effective_start_date
and paf.effective_end_date;
select *
from per_all_assignments_f paf
where paf.person_id = c_person_id
and paf.assignment_id = c_assignment_id
and paf.business_group_id = c_business_group_id
and c_effective_date between paf.effective_start_date
and paf.effective_end_date;
l_proc_name constant varchar2(150) := g_pkg||'Update_Batch_Assignment';
l_datetrack_update_mode varchar2(150);
l_dt_update boolean;
,p_update => l_dt_update
,p_update_override => l_dt_upd_override
,p_update_change_insert => l_upd_chg_ins
);
if l_dt_update then
l_datetrack_update_mode := 'UPDATE';
l_datetrack_update_mode := 'CORRECTION';
l_datetrack_update_mode := 'CORRECTION';
,p_datetrack_update_mode => l_datetrack_update_mode
);
end Update_Batch_Assignment;
select Max(link_value) + 1
from hr_pump_batch_lines
where batch_id = c_batch_id;
l_datetrack_update_mode varchar2(50);
delete from hr_pump_batch_line_user_keys where batch_line_id in (
select batch_line_id from hr_pump_batch_lines where batch_id = p_batch_id
and link_value = p_batch_link );
delete from hr_pump_batch_lines where batch_id = p_batch_id and
link_value = p_batch_link;
(p_datetrack_update_mode => l_datetrack_update_mode
);
hr_utility.set_location(' l_datetrack_update_mode: ' || l_datetrack_update_mode, 20);
,p_datetrack_update_mode => l_datetrack_update_mode
,p_adjusted_svc_date => p_adjusted_svc_date --Added by pkagrawa
);
Update_Batch_Assignment
(p_effective_date => nvl(g_asg_rec.effective_start_date,g_per_rec.start_date)
,p_business_group_id => g_per_rec.business_group_id
,p_person_id => g_per_rec.person_id
,p_batch_id => p_batch_id
,p_user_sequence => l_user_sequence
,p_link_value => l_link_value
,p_assignment_user_key => l_assignment_user_key
,p_action_mode => l_action_mode
);
,p_datetrack_update_mode => l_datetrack_update_mode
,p_cur_rec => l_cur_rec
,p_adjusted_svc_date => p_adjusted_svc_date --Added by pkagrawa
);
l_datetrack_update_mode := 'CORRECTION';
,p_datetrack_update_mode => l_datetrack_update_mode
);
,p_datetrack_update_mode => l_datetrack_update_mode
,p_vacancy_user_key => null
,p_application_user_key => l_application_user_key
);
,p_datetrack_update_mode => l_datetrack_update_mode
);
select 'CREATE'
from hr_api_modules
where module_name = 'CREATE_EMPLOYEE'
and module_package = 'HR_EMPLOYEE_API'
and api_module_id in (select api_module_id
from hr_pump_batch_lines
where batch_id = c_batch_id
and link_value = c_link_value);
select module_name
from hr_pump_batch_lines hpbl
,hr_api_modules ham
where batch_id = c_batch_id
and link_value = c_link_value
and line_status in ('U', 'E')
and hpbl.api_module_id = ham.api_module_id;
select *
from hrdpv_hire_into_job
where batch_id = c_batch_id
and link_value = c_link_value;
select *
from hrdpv_create_employee
where batch_id = c_batch_id
and link_value = c_link_value;
cursor csr_get_update_per_data (c_batch_id in number
,c_link_value in number) is
select *
from hrdpv_update_person
where batch_id = c_batch_id
and link_value = c_link_value;
l_dp_upd_per_rec hrdpv_update_person%rowtype;
cursor csr_get_update_add_data (c_batch_id in number
,c_link_value in number) is
select *
from hrdpv_update_person_address
where batch_id = c_batch_id
and link_value = c_link_value;
l_dp_upd_add_rec hrdpv_update_person_address%rowtype;
select *
from hrdpv_create_person_address
where batch_id = c_batch_id
and link_value = c_link_value;
select *
from hrdpv_update_emp_asg
where batch_id = c_batch_id
and link_value = c_link_value;
l_dp_upd_asg_rec hrdpv_update_emp_asg%rowtype;
select *
from hrdpv_update_emp_asg_criteria
where batch_id = c_batch_id
and link_value = c_link_value;
l_dp_upd_asg_crt_rec hrdpv_update_emp_asg_criteria%rowtype;
select *
from hrdpv_create_contact
where batch_id = c_batch_id
and link_value = c_link_value;
cursor csr_get_update_cnt_data (c_batch_id in number
,c_link_value in number) is
select *
from hrdpv_update_contact_relations
where batch_id = c_batch_id
and link_value = c_link_value;
l_dp_upd_cnt_rec hrdpv_update_contact_relations%rowtype;
l_dp_mode := 'UPDATE';
Hrdpp_Hire_Into_Job.insert_batch_lines
(p_batch_id => p_batch_id
,p_data_pump_batch_line_id => l_dp_hire_job_rec.batch_line_id
,p_user_sequence => l_dp_hire_job_rec.user_sequence
,p_link_value => p_data_pump_batch_line_id
,p_effective_date => g_per_rec.start_date
,p_object_version_number => l_dp_hire_job_rec.p_object_version_number
,p_datetrack_update_mode => l_dp_hire_job_rec.p_datetrack_update_mode
,p_employee_number => g_per_rec.employee_number
,p_national_identifier => g_per_rec.national_identifier
,p_per_information7 => null
,p_person_user_key => l_dp_hire_job_rec.p_person_user_key
,p_user_person_type => l_user_person_type
,p_assignment_user_key => l_dp_hire_job_rec.p_assignment_user_key
,p_language_code => Userenv('LANG')
);
,p_datetrack_update_mode => l_datetrack_update_mode
,p_cur_rec => l_cur_rec
);
Hrdpp_Create_Employee.Insert_Batch_Lines
(p_batch_id => p_batch_id
--l_dp_batch_line_id_emp
,p_data_pump_batch_line_id => l_dp_crt_emp_rec.batch_line_id
,p_user_sequence => l_dp_crt_emp_rec.user_sequence
,p_link_value => p_data_pump_batch_line_id
,p_hire_date => g_per_rec.start_date
,p_last_name => g_per_rec.last_name
,p_sex => g_per_rec.sex
,p_date_employee_data_verified => g_per_rec.date_employee_data_verified
,p_date_of_birth => g_per_rec.date_of_birth
,p_email_address => g_per_rec.email_address
,p_employee_number => g_per_rec.employee_number
,p_expense_check_send_to_addres => g_per_rec.expense_check_send_to_address
,p_first_name => g_per_rec.first_name
,p_known_as => g_per_rec.known_as
,p_marital_status => g_per_rec.marital_status
,p_middle_names => g_per_rec.middle_names
,p_nationality => g_per_rec.nationality
,p_national_identifier => g_per_rec.national_identifier
,p_previous_last_name => g_per_rec.previous_last_name
,p_registered_disabled_flag => g_per_rec.registered_disabled_flag
,p_title => g_per_rec.title
,p_attribute_category => g_per_rec.attribute_category
,p_attribute1 => g_per_rec.attribute1
,p_attribute2 => g_per_rec.attribute2
,p_attribute3 => g_per_rec.attribute3
,p_attribute4 => g_per_rec.attribute4
,p_attribute5 => g_per_rec.attribute5
,p_attribute6 => g_per_rec.attribute6
,p_attribute7 => g_per_rec.attribute7
,p_attribute8 => g_per_rec.attribute8
,p_attribute9 => g_per_rec.attribute9
,p_attribute10 => g_per_rec.attribute10
,p_attribute11 => g_per_rec.attribute11
,p_attribute12 => g_per_rec.attribute12
,p_attribute13 => g_per_rec.attribute13
,p_attribute14 => g_per_rec.attribute14
,p_attribute15 => g_per_rec.attribute15
,p_attribute16 => g_per_rec.attribute16
,p_attribute17 => g_per_rec.attribute17
,p_attribute18 => g_per_rec.attribute18
,p_attribute19 => g_per_rec.attribute19
,p_attribute20 => g_per_rec.attribute20
,p_attribute21 => g_per_rec.attribute21
,p_attribute22 => g_per_rec.attribute22
,p_attribute23 => g_per_rec.attribute23
,p_attribute24 => g_per_rec.attribute24
,p_attribute25 => g_per_rec.attribute25
,p_attribute26 => g_per_rec.attribute26
,p_attribute27 => g_per_rec.attribute27
,p_attribute28 => g_per_rec.attribute28
,p_attribute29 => g_per_rec.attribute29
,p_attribute30 => g_per_rec.attribute30
,p_per_information_category => g_per_rec.per_information_category
,p_per_information1 => g_per_rec.per_information1
,p_per_information2 => g_per_rec.per_information2
,p_per_information3 => g_per_rec.per_information3
,p_per_information4 => g_per_rec.per_information4
,p_per_information5 => g_per_rec.per_information5
,p_per_information6 => g_per_rec.per_information6
,p_per_information7 => g_per_rec.per_information7
,p_per_information8 => g_per_rec.per_information8
,p_per_information9 => g_per_rec.per_information9
,p_per_information10 => g_per_rec.per_information10
,p_per_information11 => g_per_rec.per_information11
,p_per_information12 => g_per_rec.per_information12
,p_per_information13 => g_per_rec.per_information13
,p_per_information14 => g_per_rec.per_information14
,p_per_information15 => g_per_rec.per_information15
,p_per_information16 => g_per_rec.per_information16
,p_per_information17 => g_per_rec.per_information17
,p_per_information18 => g_per_rec.per_information18
,p_per_information19 => g_per_rec.per_information19
,p_per_information20 => g_per_rec.per_information20
,p_per_information21 => g_per_rec.per_information21
,p_per_information22 => g_per_rec.per_information22
,p_per_information23 => g_per_rec.per_information23
,p_per_information24 => g_per_rec.per_information24
,p_per_information25 => g_per_rec.per_information25
,p_per_information26 => g_per_rec.per_information26
,p_per_information27 => g_per_rec.per_information27
,p_per_information28 => g_per_rec.per_information28
,p_per_information29 => g_per_rec.per_information29
,p_per_information30 => g_per_rec.per_information30
,p_date_of_death => g_per_rec.date_of_death
,p_background_check_status => g_per_rec.background_check_status
,p_background_date_check => g_per_rec.background_date_check
,p_blood_type => g_per_rec.blood_type
,p_fast_path_employee => g_per_rec.fast_path_employee
,p_fte_capacity => g_per_rec.fte_capacity
,p_honors => g_per_rec.honors
,p_internal_location => g_per_rec.internal_location
,p_last_medical_test_by => g_per_rec.last_medical_test_by
,p_last_medical_test_date => g_per_rec.last_medical_test_date
,p_mailstop => g_per_rec.mailstop
,p_office_number => g_per_rec.office_number
,p_on_military_service => g_per_rec.on_military_service
,p_pre_name_adjunct => g_per_rec.pre_name_adjunct
,p_projected_start_date => g_per_rec.projected_start_date
,p_resume_exists => g_per_rec.resume_exists
,p_resume_last_updated => g_per_rec.resume_last_updated
,p_second_passport_exists => g_per_rec.second_passport_exists
,p_student_status => g_per_rec.student_status
,p_work_schedule => g_per_rec.work_schedule
,p_suffix => g_per_rec.suffix
,p_receipt_of_death_cert_date => g_per_rec.receipt_of_death_cert_date
,p_coord_ben_med_pln_no => g_per_rec.coord_ben_med_pln_no
,p_coord_ben_no_cvg_flag => g_per_rec.coord_ben_no_cvg_flag
,p_coord_ben_med_ext_er => g_per_rec.coord_ben_med_ext_er
,p_coord_ben_med_pl_name => g_per_rec.coord_ben_med_pl_name
,p_coord_ben_med_insr_crr_name => g_per_rec.coord_ben_med_insr_crr_name
,p_coord_ben_med_insr_crr_ident => g_per_rec.coord_ben_med_insr_crr_ident
,p_coord_ben_med_cvg_strt_dt => g_per_rec.coord_ben_med_cvg_strt_dt
,p_coord_ben_med_cvg_end_dt => g_per_rec.coord_ben_med_cvg_end_dt
,p_uses_tobacco_flag => g_per_rec.uses_tobacco_flag
,p_dpdnt_adoption_date => g_per_rec.dpdnt_adoption_date
,p_dpdnt_vlntry_svce_flag => g_per_rec.dpdnt_vlntry_svce_flag
,p_original_date_of_hire => g_per_rec.original_date_of_hire
,p_town_of_birth => g_per_rec.town_of_birth
,p_region_of_birth => g_per_rec.region_of_birth
,p_country_of_birth => g_per_rec.country_of_birth
,p_global_person_id => g_per_rec.global_person_id
,p_party_id => g_per_rec.party_id
,p_correspondence_language => g_per_rec.correspondence_language
,p_benefit_group => g_per_rec.benefit_group_id
,p_person_user_key => l_dp_crt_emp_rec.p_person_user_key
,p_assignment_user_key => l_dp_crt_emp_rec.p_assignment_user_key
,p_user_person_type => l_user_person_type
,p_language_code => Userenv('lang')
,p_vendor_name => null
);
if l_api_name = 'UPDATE_PERSON' then
open csr_get_update_per_data(c_batch_id => p_batch_id
,c_link_value => p_data_pump_batch_line_id
);
fetch csr_get_update_per_data into l_dp_upd_per_rec;
close csr_get_update_per_data;
Hrdpp_Update_Person.insert_batch_lines
(p_batch_id => p_batch_id
,p_data_pump_batch_line_id => l_dp_upd_per_rec.batch_line_id
,p_user_sequence => l_dp_upd_per_rec.user_sequence
,p_link_value => p_data_pump_batch_line_id
,p_effective_date => g_per_rec.start_date
,p_datetrack_update_mode => l_dp_upd_per_rec.p_datetrack_update_mode
,p_party_id => g_per_rec.party_id
,p_employee_number => g_per_rec.employee_number
,p_last_name => g_per_rec.last_name
,p_first_name => g_per_rec.first_name
,p_date_of_birth => g_per_rec.date_of_birth
,p_marital_status => g_per_rec.marital_status
,p_middle_names => g_per_rec.middle_names
,p_sex => g_per_rec.sex
,p_title => g_per_rec.title
,p_nationality => g_per_rec.nationality
,p_previous_last_name => g_per_rec.previous_last_name
,p_known_as => g_per_rec.known_as
,p_email_address => g_per_rec.email_address
,p_registered_disabled_flag => g_per_rec.registered_disabled_flag
,p_date_employee_data_verified => g_per_rec.date_employee_data_verified
,p_expense_check_send_to_addres => g_per_rec.expense_check_send_to_address
-- Person DDF
,p_per_information_category => g_per_rec.per_information_category
,p_per_information1 => g_per_rec.per_information1
,p_per_information2 => g_per_rec.per_information2
,p_per_information3 => g_per_rec.per_information3
,p_per_information4 => g_per_rec.per_information4
,p_per_information5 => g_per_rec.per_information5
,p_per_information6 => g_per_rec.per_information6
,p_per_information7 => g_per_rec.per_information7
,p_per_information8 => g_per_rec.per_information8
,p_per_information9 => g_per_rec.per_information9
,p_per_information10 => g_per_rec.per_information10
,p_per_information11 => g_per_rec.per_information11
,p_per_information12 => g_per_rec.per_information12
,p_per_information13 => g_per_rec.per_information13
,p_per_information14 => g_per_rec.per_information14
,p_per_information15 => g_per_rec.per_information15
,p_per_information16 => g_per_rec.per_information16
,p_per_information17 => g_per_rec.per_information17
,p_per_information18 => g_per_rec.per_information18
,p_per_information19 => g_per_rec.per_information19
,p_per_information20 => g_per_rec.per_information20
,p_per_information21 => g_per_rec.per_information21
,p_per_information22 => g_per_rec.per_information22
,p_per_information23 => g_per_rec.per_information23
,p_per_information24 => g_per_rec.per_information24
,p_per_information25 => g_per_rec.per_information25
,p_per_information26 => g_per_rec.per_information26
,p_per_information27 => g_per_rec.per_information27
,p_per_information28 => g_per_rec.per_information28
,p_per_information29 => g_per_rec.per_information29
,p_per_information30 => g_per_rec.per_information30
-- Person DF
,p_attribute_category => g_per_rec.attribute_category
,p_attribute1 => g_per_rec.attribute1
,p_attribute2 => g_per_rec.attribute2
,p_attribute3 => g_per_rec.attribute3
,p_attribute4 => g_per_rec.attribute4
,p_attribute5 => g_per_rec.attribute5
,p_attribute6 => g_per_rec.attribute6
,p_attribute7 => g_per_rec.attribute7
,p_attribute8 => g_per_rec.attribute8
,p_attribute9 => g_per_rec.attribute9
,p_attribute10 => g_per_rec.attribute10
,p_attribute11 => g_per_rec.attribute11
,p_attribute12 => g_per_rec.attribute12
,p_attribute13 => g_per_rec.attribute13
,p_attribute14 => g_per_rec.attribute14
,p_attribute15 => g_per_rec.attribute15
,p_attribute16 => g_per_rec.attribute16
,p_attribute17 => g_per_rec.attribute17
,p_attribute18 => g_per_rec.attribute18
,p_attribute19 => g_per_rec.attribute19
,p_attribute20 => g_per_rec.attribute20
,p_attribute21 => g_per_rec.attribute21
,p_attribute22 => g_per_rec.attribute22
,p_attribute23 => g_per_rec.attribute23
,p_attribute24 => g_per_rec.attribute24
,p_attribute25 => g_per_rec.attribute25
,p_attribute26 => g_per_rec.attribute26
,p_attribute27 => g_per_rec.attribute27
,p_attribute28 => g_per_rec.attribute28
,p_attribute29 => g_per_rec.attribute29
,p_attribute30 => g_per_rec.attribute30
,p_date_of_death => g_per_rec.date_of_death
,p_background_check_status => g_per_rec.background_check_status
,p_background_date_check => g_per_rec.background_date_check
,p_blood_type => g_per_rec.blood_type
,p_correspondence_language => g_per_rec.correspondence_language
,p_fte_capacity => g_per_rec.fte_capacity
,p_hold_applicant_date_until => g_per_rec.hold_applicant_date_until
,p_honors => g_per_rec.honors
,p_internal_location => g_per_rec.internal_location
,p_last_medical_test_by => g_per_rec.last_medical_test_by
,p_last_medical_test_date => g_per_rec.last_medical_test_date
,p_mailstop => g_per_rec.mailstop
,p_office_number => g_per_rec.office_number
,p_on_military_service => g_per_rec.on_military_service
,p_pre_name_adjunct => g_per_rec.pre_name_adjunct
,p_projected_start_date => g_per_rec.projected_start_date
,p_rehire_authorizor => g_per_rec.rehire_authorizor
,p_rehire_recommendation => g_per_rec.rehire_recommendation
,p_resume_exists => g_per_rec.resume_exists
,p_resume_last_updated => g_per_rec.resume_last_updated
,p_second_passport_exists => g_per_rec.second_passport_exists
,p_student_status => g_per_rec.student_status
,p_work_schedule => g_per_rec.work_schedule
,p_rehire_reason => g_per_rec.rehire_reason
,p_suffix => g_per_rec.suffix
,p_benefit_group => g_per_rec.benefit_group_id
,p_receipt_of_death_cert_date => g_per_rec.receipt_of_death_cert_date
,p_coord_ben_med_pln_no => g_per_rec.coord_ben_med_pln_no
,p_coord_ben_no_cvg_flag => g_per_rec.coord_ben_no_cvg_flag
,p_coord_ben_med_ext_er => g_per_rec.coord_ben_med_ext_er
,p_coord_ben_med_pl_name => g_per_rec.coord_ben_med_pl_name
,p_coord_ben_med_insr_crr_name => g_per_rec.coord_ben_med_insr_crr_name
,p_coord_ben_med_insr_crr_ident => g_per_rec.coord_ben_med_insr_crr_ident
,p_coord_ben_med_cvg_strt_dt => g_per_rec.coord_ben_med_cvg_strt_dt
,p_coord_ben_med_cvg_end_dt => g_per_rec.coord_ben_med_cvg_end_dt
,p_uses_tobacco_flag => g_per_rec.uses_tobacco_flag
,p_dpdnt_adoption_date => g_per_rec.dpdnt_adoption_date
,p_dpdnt_vlntry_svce_flag => g_per_rec.dpdnt_vlntry_svce_flag
,p_original_date_of_hire => g_per_rec.original_date_of_hire
,p_adjusted_svc_date => p_adjusted_svc_date
,p_town_of_birth => g_per_rec.town_of_birth
,p_region_of_birth => g_per_rec.region_of_birth
,p_country_of_birth => g_per_rec.country_of_birth
,p_global_person_id => g_per_rec.global_person_id
,p_person_user_key => l_dp_upd_per_rec.p_person_user_key
,p_user_person_type => l_dp_upd_per_rec.p_user_person_type
,p_language_code => Userenv('lang')
,p_vendor_name => null
);
if l_api_name = 'UPDATE_PERSON_ADDRESS' then
open csr_get_update_add_data(c_batch_id => p_batch_id
,c_link_value => p_data_pump_batch_line_id
);
fetch csr_get_update_add_data into l_dp_upd_add_rec;
close csr_get_update_add_data;
Hrdpp_Update_Person_Address.Insert_Batch_Lines
(p_batch_id => p_batch_id
--l_dp_batch_line_id_up_add
,p_data_pump_batch_line_id => l_dp_upd_add_rec.batch_line_id
,p_user_sequence => l_dp_upd_add_rec.user_sequence
,p_link_value => p_data_pump_batch_line_id
,p_effective_date => g_per_rec.start_date
,p_validate_county => false
,p_primary_flag => g_add_rec.primary_flag
,p_date_from => g_add_rec.date_from
,p_date_to => g_add_rec.date_to
,p_address_type => g_add_rec.address_type
--$
,p_comments => g_add_rec.comments
,p_address_line1 => g_add_rec.address_line1
,p_address_line2 => g_add_rec.address_line2
,p_address_line3 => g_add_rec.address_line3
,p_town_or_city => g_add_rec.town_or_city
,p_region_1 => g_add_rec.region_1
,p_region_2 => g_add_rec.region_2
,p_region_3 => g_add_rec.region_3
,p_postal_code => g_add_rec.postal_code
,p_telephone_number_1 => g_add_rec.telephone_number_1
,p_telephone_number_2 => g_add_rec.telephone_number_2
,p_telephone_number_3 => g_add_rec.telephone_number_3
,p_addr_attribute_category => g_add_rec.addr_attribute_category
,p_addr_attribute1 => g_add_rec.addr_attribute1
,p_addr_attribute2 => g_add_rec.addr_attribute2
,p_addr_attribute3 => g_add_rec.addr_attribute3
,p_addr_attribute4 => g_add_rec.addr_attribute4
,p_addr_attribute5 => g_add_rec.addr_attribute5
,p_addr_attribute6 => g_add_rec.addr_attribute6
,p_addr_attribute7 => g_add_rec.addr_attribute7
,p_addr_attribute8 => g_add_rec.addr_attribute8
,p_addr_attribute9 => g_add_rec.addr_attribute9
,p_addr_attribute10 => g_add_rec.addr_attribute10
,p_addr_attribute11 => g_add_rec.addr_attribute11
,p_addr_attribute12 => g_add_rec.addr_attribute12
,p_addr_attribute13 => g_add_rec.addr_attribute13
,p_addr_attribute14 => g_add_rec.addr_attribute14
,p_addr_attribute15 => g_add_rec.addr_attribute15
,p_addr_attribute16 => g_add_rec.addr_attribute16
,p_addr_attribute17 => g_add_rec.addr_attribute17
,p_addr_attribute18 => g_add_rec.addr_attribute18
,p_addr_attribute19 => g_add_rec.addr_attribute19
,p_addr_attribute20 => g_add_rec.addr_attribute20
,p_add_information13 => g_add_rec.add_information13
,p_add_information14 => g_add_rec.add_information14
,p_add_information15 => g_add_rec.add_information15
,p_add_information16 => g_add_rec.add_information16
,p_add_information17 => g_add_rec.add_information17
,p_add_information18 => g_add_rec.add_information18
,p_add_information19 => g_add_rec.add_information19
,p_add_information20 => g_add_rec.add_information20
,p_party_id => g_add_rec.party_id
,p_address_user_key => l_dp_upd_add_rec.p_address_user_key
,p_country => g_add_rec.country
);
Hrdpp_Create_Person_Address.Insert_Batch_Lines
(p_batch_id => p_batch_id
--l_dp_batch_line_id_cr_add
,p_data_pump_batch_line_id => l_dp_crt_add_rec.batch_line_id
,p_user_sequence => l_dp_crt_add_rec.user_sequence
,p_link_value => p_data_pump_batch_line_id
,p_effective_date => g_per_rec.start_date
,p_pradd_ovlapval_override => l_pradd_ovlapval_override
,p_validate_county => false
,p_primary_flag => g_add_rec.primary_flag
,p_style => g_add_rec.style
,p_date_from => g_add_rec.date_from
,p_date_to => g_add_rec.date_to
,p_address_type => g_add_rec.address_type
--$
,p_comments => g_add_rec.comments
,p_address_line1 => g_add_rec.address_line1
,p_address_line2 => g_add_rec.address_line2
,p_address_line3 => g_add_rec.address_line3
,p_town_or_city => g_add_rec.town_or_city
,p_region_1 => g_add_rec.region_1
,p_region_2 => g_add_rec.region_2
,p_region_3 => g_add_rec.region_3
,p_postal_code => g_add_rec.postal_code
,p_telephone_number_1 => g_add_rec.telephone_number_1
,p_telephone_number_2 => g_add_rec.telephone_number_2
,p_telephone_number_3 => g_add_rec.telephone_number_3
,p_addr_attribute_category => g_add_rec.addr_attribute_category
,p_addr_attribute1 => g_add_rec.addr_attribute1
,p_addr_attribute2 => g_add_rec.addr_attribute2
,p_addr_attribute3 => g_add_rec.addr_attribute3
,p_addr_attribute4 => g_add_rec.addr_attribute4
,p_addr_attribute5 => g_add_rec.addr_attribute5
,p_addr_attribute6 => g_add_rec.addr_attribute6
,p_addr_attribute7 => g_add_rec.addr_attribute7
,p_addr_attribute8 => g_add_rec.addr_attribute8
,p_addr_attribute9 => g_add_rec.addr_attribute9
,p_addr_attribute10 => g_add_rec.addr_attribute10
,p_addr_attribute11 => g_add_rec.addr_attribute11
,p_addr_attribute12 => g_add_rec.addr_attribute12
,p_addr_attribute13 => g_add_rec.addr_attribute13
,p_addr_attribute14 => g_add_rec.addr_attribute14
,p_addr_attribute15 => g_add_rec.addr_attribute15
,p_addr_attribute16 => g_add_rec.addr_attribute16
,p_addr_attribute17 => g_add_rec.addr_attribute17
,p_addr_attribute18 => g_add_rec.addr_attribute18
,p_addr_attribute19 => g_add_rec.addr_attribute19
,p_addr_attribute20 => g_add_rec.addr_attribute20
,p_add_information13 => g_add_rec.add_information13
,p_add_information14 => g_add_rec.add_information14
,p_add_information15 => g_add_rec.add_information15
,p_add_information16 => g_add_rec.add_information16
,p_add_information17 => g_add_rec.add_information17
,p_add_information18 => g_add_rec.add_information18
,p_add_information19 => g_add_rec.add_information19
,p_add_information20 => g_add_rec.add_information20
,p_party_id => g_add_rec.party_id
,p_address_user_key => l_dp_crt_add_rec.p_address_user_key
,p_person_user_key => l_dp_crt_add_rec.p_person_user_key
,p_country => g_add_rec.country
);
if l_api_name = 'UPDATE_EMP_ASG' then
open csr_get_upd_asg_data(c_batch_id => p_batch_id
,c_link_value => p_data_pump_batch_line_id
);
Hrdpp_Update_Emp_Asg.Insert_Batch_Lines
(p_batch_id => p_batch_id
-- l_dp_batch_line_id_asg
,p_data_pump_batch_line_id => l_dp_upd_asg_rec.batch_line_id
,p_user_sequence => l_dp_upd_asg_rec.user_sequence
,p_link_value => p_data_pump_batch_line_id
,p_effective_date => g_per_rec.start_date
,p_datetrack_update_mode => l_dp_upd_asg_rec.p_datetrack_update_mode
,p_change_reason => g_asg_rec.change_reason
,p_date_probation_end => g_asg_rec.date_probation_end
,p_frequency => g_asg_rec.frequency
,p_internal_address_line => g_asg_rec.internal_address_line
,p_manager_flag => g_asg_rec.manager_flag
,p_normal_hours => g_asg_rec.normal_hours
,p_perf_review_period => g_asg_rec.perf_review_period
,p_perf_review_period_frequency => g_asg_rec.perf_review_period_frequency
,p_probation_period => g_asg_rec.probation_period
,p_probation_unit => g_asg_rec.probation_unit
,p_sal_review_period => g_asg_rec.sal_review_period
,p_sal_review_period_frequency => g_asg_rec.sal_review_period_frequency
,p_source_type => g_asg_rec.source_type
,p_time_normal_finish => g_asg_rec.time_normal_finish
,p_time_normal_start => g_asg_rec.time_normal_start
,p_bargaining_unit_code => g_asg_rec.bargaining_unit_code
,p_labour_union_member_flag => g_asg_rec.labour_union_member_flag
,p_hourly_salaried_code => g_asg_rec.hourly_salaried_code
,p_ass_attribute_category => g_asg_rec.ass_attribute_category
,p_ass_attribute1 => g_asg_rec.ass_attribute1
,p_ass_attribute2 => g_asg_rec.ass_attribute2
,p_ass_attribute3 => g_asg_rec.ass_attribute3
,p_ass_attribute4 => g_asg_rec.ass_attribute4
,p_ass_attribute5 => g_asg_rec.ass_attribute5
,p_ass_attribute6 => g_asg_rec.ass_attribute6
,p_ass_attribute7 => g_asg_rec.ass_attribute7
,p_ass_attribute8 => g_asg_rec.ass_attribute8
,p_ass_attribute9 => g_asg_rec.ass_attribute9
,p_ass_attribute10 => g_asg_rec.ass_attribute10
,p_ass_attribute11 => g_asg_rec.ass_attribute11
,p_ass_attribute12 => g_asg_rec.ass_attribute12
,p_ass_attribute13 => g_asg_rec.ass_attribute13
,p_ass_attribute14 => g_asg_rec.ass_attribute14
,p_ass_attribute15 => g_asg_rec.ass_attribute15
,p_ass_attribute16 => g_asg_rec.ass_attribute16
,p_ass_attribute17 => g_asg_rec.ass_attribute17
,p_ass_attribute18 => g_asg_rec.ass_attribute18
,p_ass_attribute19 => g_asg_rec.ass_attribute19
,p_ass_attribute20 => g_asg_rec.ass_attribute20
,p_ass_attribute21 => g_asg_rec.ass_attribute21
,p_ass_attribute22 => g_asg_rec.ass_attribute22
,p_ass_attribute23 => g_asg_rec.ass_attribute23
,p_ass_attribute24 => g_asg_rec.ass_attribute24
,p_ass_attribute25 => g_asg_rec.ass_attribute25
,p_ass_attribute26 => g_asg_rec.ass_attribute26
,p_ass_attribute27 => g_asg_rec.ass_attribute27
,p_ass_attribute28 => g_asg_rec.ass_attribute28
,p_ass_attribute29 => g_asg_rec.ass_attribute29
,p_ass_attribute30 => g_asg_rec.ass_attribute30
,p_segment1 => g_scl_rec.segment1
,p_segment2 => g_scl_rec.segment2
,p_segment3 => g_scl_rec.segment3
,p_segment4 => g_scl_rec.segment4
,p_segment5 => g_scl_rec.segment5
,p_segment6 => g_scl_rec.segment6
,p_segment7 => g_scl_rec.segment7
,p_segment8 => g_scl_rec.segment8
,p_segment9 => g_scl_rec.segment9
,p_cagr_grade_def_id => null
,p_assignment_user_key => l_dp_upd_asg_rec.p_assignment_user_key
,p_con_seg_user_name => null
);
if l_api_name = 'UPDATE_EMP_ASG_CRITERIA' then
Get_WrkStrs_Names;
Hrdpp_Update_Emp_Asg_Criteria.Insert_Batch_Lines
(p_batch_id => p_batch_id
--l_dp_batch_line_id_asg_cri
,p_data_pump_batch_line_id => l_dp_upd_asg_crt_rec.batch_line_id
,p_user_sequence => l_dp_upd_asg_crt_rec.user_sequence
,p_link_value => p_data_pump_batch_line_id
,p_effective_date => g_per_rec.start_date
,p_datetrack_update_mode => l_dp_upd_asg_crt_rec.p_datetrack_update_mode
,p_segment1 => g_grp_rec.segment1
,p_segment2 => g_grp_rec.segment2
,p_segment3 => g_grp_rec.segment3
,p_segment4 => g_grp_rec.segment4
,p_segment5 => g_grp_rec.segment5
,p_segment6 => g_grp_rec.segment6
,p_segment7 => g_grp_rec.segment7
,p_segment8 => g_grp_rec.segment8
,p_segment9 => g_grp_rec.segment9
,p_segment10 => g_grp_rec.segment10
,p_segment11 => g_grp_rec.segment11
,p_segment12 => g_grp_rec.segment12
,p_segment13 => g_grp_rec.segment13
,p_segment14 => g_grp_rec.segment14
,p_segment15 => g_grp_rec.segment15
,p_segment16 => g_grp_rec.segment16
,p_segment17 => g_grp_rec.segment17
,p_segment18 => g_grp_rec.segment18
,p_segment19 => g_grp_rec.segment19
,p_segment20 => g_grp_rec.segment20
,p_segment21 => g_grp_rec.segment21
,p_segment22 => g_grp_rec.segment22
,p_segment23 => g_grp_rec.segment23
,p_segment24 => g_grp_rec.segment24
,p_segment25 => g_grp_rec.segment25
,p_segment26 => g_grp_rec.segment26
,p_segment27 => g_grp_rec.segment27
,p_segment28 => g_grp_rec.segment28
,p_segment29 => g_grp_rec.segment29
,p_segment30 => g_grp_rec.segment30
,p_special_ceiling_step_id => null
,p_people_group_id => null
,p_assignment_user_key => l_dp_upd_asg_crt_rec.p_assignment_user_key
,p_grade_name => g_wstr_names.grade_name
,p_position_name => g_wstr_names.position_name
,p_job_name => g_wstr_names.job_name
,p_payroll_name => g_wstr_names.payroll_name
,p_location_code => g_wstr_names.location_code
,p_organization_name => g_wstr_names.organization_name
,p_pay_basis_name => g_wstr_names.pay_basis_name
,p_language_code => Userenv('LANG')
,p_con_seg_user_name => null
);
Hrdpp_Create_Contact.insert_batch_lines
(p_batch_id => p_batch_id
,p_data_pump_batch_line_id => l_dp_crt_cnt_rec.batch_line_id
,p_user_sequence => l_dp_crt_cnt_rec.user_sequence
,p_link_value => p_data_pump_batch_line_id
,p_start_date => g_per_rec.start_date
,p_contact_type => g_cnt_rec.contact_type
,p_primary_contact_flag => g_cnt_rec.primary_contact_flag
,p_personal_flag => g_cnt_rec.personal_flag
,p_last_name => p_contact_name
,p_per_person_user_key => l_dp_crt_cnt_rec.p_per_person_user_key
,p_person_user_key => l_dp_crt_cnt_rec.p_person_user_key
,p_language_code => userenv('LANG')
);
if l_api_name = 'UPDATE_CONTACT_RELATIONSHIP' then
open csr_get_update_cnt_data(c_batch_id => p_batch_id
,c_link_value => p_data_pump_batch_line_id
);
fetch csr_get_update_cnt_data into l_dp_upd_cnt_rec;
close csr_get_update_cnt_data;
Hrdpp_Update_Contact_Relations.insert_batch_lines
(p_batch_id => p_batch_id
,p_data_pump_batch_line_id => l_dp_upd_cnt_rec.batch_line_id
,p_user_sequence => l_dp_crt_cnt_rec.user_sequence
,p_link_value => p_data_pump_batch_line_id
,p_effective_date => g_per_rec.start_date
,p_contact_type => g_cnt_rec.contact_type
,p_primary_contact_flag => g_cnt_rec.primary_contact_flag
,p_personal_flag => g_cnt_rec.personal_flag
,p_object_version_number => l_dp_upd_cnt_rec.p_object_version_number
,p_contact_user_key => l_dp_upd_cnt_rec.p_contact_user_key
,p_contactee_user_key => l_dp_upd_cnt_rec.p_contactee_user_key
);
procedure Update_Person_Contact
(p_effective_date in date
,p_contact_name in varchar2
,p_legislation_code in varchar2
,p_crt_cntct_out out NOCOPY t_CreateContact_Api
) as
l_cont_object_version_num number;
l_proc_name constant varchar2(150):= g_pkg ||'Update_Person_Contact';
end Update_Person_Contact;
select pbg.name
from per_business_groups pbg
where pbg.business_group_id = c_bg_grp_id;
select hr_pump_batch_headers_s.nextval
into p_batch_process_id
from dual;
insert into hr_pump_batch_headers
(batch_id
,batch_name
,batch_status
,business_group_name
,reference)
values
(p_batch_process_id
,p_batch_process_name
,'U'
,l_bg_name
--,nvl(l_bg_name,'PQPD115 Business Group') -- remove the NVL()
,l_reference);
procedure Update_Assignment
(p_effective_date in date
,p_business_group_id in number
,p_person_id in number
) as
--Added by DBANSAL to get the person type
cursor csr_type (c_person_type_id in number) is
select *
from per_person_types
where person_type_id = c_person_type_id
and business_group_id = p_business_group_id;
select *
from per_all_assignments_f paf
where paf.person_id = c_person_id
and paf.business_group_id = c_business_group_id
and c_effective_date between paf.effective_start_date
and paf.effective_end_date;
select *
from per_all_assignments_f paf
where paf.person_id = c_person_id
and paf.assignment_id = c_assignment_id
and paf.business_group_id = c_business_group_id
and c_effective_date between paf.effective_start_date
and paf.effective_end_date;
select *
from per_all_assignments_f paf
where paf.person_id = c_person_id
and paf.assignment_number = c_assignment_number
and paf.business_group_id = c_business_group_id
and c_effective_date between paf.effective_start_date
and paf.effective_end_date;
l_proc_name constant varchar2(150) := g_pkg||'Update_Assignment';
/*hr_utility.trace('CURSOR : select * from per_all_assignments_f paf
where paf.person_id =' ||p_person_id || 'and paf.assignment_number = ' ||
g_asg_rec.assignment_number || 'and paf.business_group_id = ' || p_business_group_id
|| 'and ' || sysdate|| ' between paf.effective_start_date
and paf.effective_end_date' );*/
Update_Employee_Asg
(p_effective_date => p_effective_date
,p_asg_crit_out => l_AsgUpdCrit_Api
);
end Update_Assignment;
Update_Employee_Asg
(p_effective_date => nvl(g_asg_rec.effective_start_date,g_per_rec.start_date)
,p_asg_crit_out => l_asg_crit_out
);
,p_datetrack_update_mode => Null
,p_effective_date => p_effective_date
,p_adjusted_svc_date => null
,p_per_comments => g_per_comments -- null --Changed by Dbansal
,p_cwk_api_out => l_hrEmpApi_out
);
Update_Assignment
(p_effective_date => nvl(g_asg_rec.effective_start_date,g_per_rec.start_date) --p_effective_date
,p_business_group_id => p_business_group_id
,p_person_id => g_per_rec.person_id
);
select paf.assignment_id
from per_all_assignments_f paf
where paf.person_id = c_person_id
and paf.assignment_type = 'A'
and paf.assignment_number = c_assignment_number
and paf.business_group_id = c_business_group_id
and c_effective_date between paf.effective_start_date
and paf.effective_end_date;
select paf.assignment_id
from per_all_assignments_f paf
where paf.person_id = c_person_id
and paf.assignment_type = 'A'
and paf.business_group_id = c_business_group_id
and c_effective_date between paf.effective_start_date
and paf.effective_end_date;
,p_resume_last_updated in date
,p_second_passport_exists in varchar2
,p_student_status in varchar2
,p_work_schedule in varchar2
,p_benefit_group_id in number
,p_receipt_of_death_cert_date in date
,p_coord_ben_med_pln_no in varchar2
,p_coord_ben_no_cvg_flag in varchar2
,p_coord_ben_med_ext_er in varchar2
,p_coord_ben_med_pl_name in varchar2
,p_coord_ben_med_insr_crr_name in varchar2
,p_coord_ben_med_insr_crr_ident in varchar2
,p_coord_ben_med_cvg_strt_dt in date
,p_coord_ben_med_cvg_end_dt in date
,p_uses_tobacco_flag in varchar2
,p_dpdnt_adoption_date in date
,p_dpdnt_vlntry_svce_flag in varchar2
,p_original_date_of_hire in date
,p_adjusted_svc_date in date
,p_town_of_birth in varchar2
,p_region_of_birth in varchar2
,p_country_of_birth in varchar2
,p_global_person_id in varchar2
,p_party_id in number
,p_supervisor_id in number
,p_assignment_number in varchar2
,p_change_reason in varchar2
,p_asg_comments in varchar2
,p_date_probation_end in date
,p_default_code_comb_id in number
,p_frequency in varchar2
,p_internal_address_line in varchar2
,p_manager_flag in varchar2
,p_normal_hours in number
,p_perf_review_period in number
,p_perf_review_period_frequency in varchar2
,p_probation_period in number
,p_probation_unit in varchar2
,p_sal_review_period in number
,p_sal_review_period_frequency in varchar2
,p_set_of_books_id in number
,p_source_type in varchar2
,p_time_normal_finish in varchar2
,p_time_normal_start in varchar2
,p_bargaining_unit_code in varchar2
,p_labour_union_member_flag in varchar2
,p_hourly_salaried_code in varchar2
,p_pradd_ovlapval_override in varchar2
,p_address_type in varchar2
,p_adr_comments in varchar2
,p_batch_name in varchar2
,p_location_id in number
,p_student_number in varchar2
,p_apl_assignment_id in varchar2
,p_applicant_number in varchar2
,p_cwk_number in varchar2
,p_interface_code in varchar2
--$ Update Batch
,p_batch_link in number
--$ Get the mode ("Create and Update", "Update Only" or "View Only" )
,p_crt_upd in varchar2
,p_assignment_id in number
) as
--
-- Dynamic Ref Cursor
--
csr_get_party_id ref_cur_typ;
l_sql_qry := 'select party_id
from hz_parties
where party_number ='||l_party_number;
g_per_rec.resume_last_updated := p_resume_last_updated;
select plr.rule_mode into id_flex_num
from pay_legislation_rules plr,
per_business_groups_perf pgr
where plr.legislation_code = pgr.legislation_code
and pgr.business_group_id = p_business_group_id
and plr.rule_type = 'S'
and exists
(select 1
from fnd_segment_attribute_values fsav
where to_char(fsav.id_flex_num) = plr.rule_mode
and fsav.application_id = 800
and fsav.id_flex_code = 'SCL'
and fsav.segment_attribute_type = 'ASSIGNMENT'
and fsav.attribute_value = 'Y')
and exists
(select 1
from pay_legislation_rules plr2
where plr2.legislation_code = plr.legislation_code
and plr2.rule_type = 'SDL'
and plr2.rule_mode = 'A') ;