[Home] [Help]
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_assignments_f.comment_id%TYPE
);
SELECT pbg.legislation_code
,pbg.method_of_generation_emp_num
FROM per_business_groups pbg
WHERE pbg.business_group_id = c_bg_grp_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_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_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_people_f
WHERE person_id = c_person_id;
SELECT *
FROM pay_us_emp_fed_tax_rules_f ftx
WHERE ftx.assignment_id = c_assignment_id
AND c_effective_date between ftx.effective_start_date
AND ftx.effective_end_date;
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_mode := 'UPDATE';
Pay_Federal_Tax_Rule_Api.UpDate_Fed_Tax_Rule
(p_validate => false
,p_effective_date => p_effective_date
,p_datetrack_update_mode => l_datetrack_mode
,p_emp_fed_tax_rule_id => l_fed_rec.emp_fed_tax_rule_id
,p_object_version_number => l_fed_rec.object_version_number
,p_medicare_tax_exempt => l_fed_rec.medicare_tax_exempt
,p_ss_tax_exempt => l_fed_rec.ss_tax_exempt
,p_effective_start_date => l_start_effective_date
,p_effective_end_date => l_end_effective_date
);
SELECT *
FROM pay_us_emp_fed_tax_rules_f ftx
WHERE ftx.assignment_id = c_assignment_id
AND c_effective_date between ftx.effective_start_date
AND ftx.effective_end_date;
PROCEDURE Update_StuEmpAsg_Criteria
(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_StuEmpAsg_Criteria';
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 := 'UPDATE';
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 => l_asg_rec.assignment_id
,p_validate => FALSE
,p_called_from_mass_update => FALSE
,p_grade_id => NVL(l_asg_rec.grade_id,
l_cur_asg_rec.grade_id)
,p_position_id => NVL(l_asg_rec.position_id,
l_cur_asg_rec.position_id)
,p_job_id => NVL(l_asg_rec.job_id,
l_cur_asg_rec.job_id)
,p_payroll_id => NVL(l_asg_rec.payroll_id,
l_cur_asg_rec.payroll_id)
,p_location_id => NVL(l_asg_rec.location_id,
l_cur_asg_rec.location_id)
,p_organization_id => NVL(l_asg_rec.organization_id,
l_cur_asg_rec.organization_id)
,p_pay_basis_id => NVL(l_asg_rec.pay_basis_id,
l_cur_asg_rec.pay_basis_id)
,p_employment_category => NVL(l_asg_rec.employment_category,
l_cur_asg_rec.employment_category)
,p_segment1 => l_ppl_grp_rec.segment1
,p_segment2 => l_ppl_grp_rec.segment2
,p_segment3 => l_ppl_grp_rec.segment3
,p_segment4 => l_ppl_grp_rec.segment4
,p_segment5 => l_ppl_grp_rec.segment5
,p_segment6 => l_ppl_grp_rec.segment6
,p_segment7 => l_ppl_grp_rec.segment7
,p_segment8 => l_ppl_grp_rec.segment8
,p_segment9 => l_ppl_grp_rec.segment9
,p_segment10 => l_ppl_grp_rec.segment10
,p_segment11 => l_ppl_grp_rec.segment11
,p_segment12 => l_ppl_grp_rec.segment12
,p_segment13 => l_ppl_grp_rec.segment13
,p_segment14 => l_ppl_grp_rec.segment14
,p_segment15 => l_ppl_grp_rec.segment15
,p_segment16 => l_ppl_grp_rec.segment16
,p_segment17 => l_ppl_grp_rec.segment17
,p_segment18 => l_ppl_grp_rec.segment18
,p_segment19 => l_ppl_grp_rec.segment19
,p_segment20 => l_ppl_grp_rec.segment20
,p_segment21 => l_ppl_grp_rec.segment21
,p_segment22 => l_ppl_grp_rec.segment22
,p_segment23 => l_ppl_grp_rec.segment23
,p_segment24 => l_ppl_grp_rec.segment24
,p_segment25 => l_ppl_grp_rec.segment25
,p_segment26 => l_ppl_grp_rec.segment26
,p_segment27 => l_ppl_grp_rec.segment27
,p_segment28 => l_ppl_grp_rec.segment28
,p_segment29 => l_ppl_grp_rec.segment29
,p_segment30 => l_ppl_grp_rec.segment30
,p_concat_segments => l_ppl_grp_rec.group_name
/*
,p_contract_id IN NUMBER DEFAULT Hr_Api.g_number
,p_establishment_id IN NUMBER DEFAULT Hr_Api.g_number
,p_grade_ladder_pgm_id IN NUMBER DEFAULT Hr_Api.g_number
,p_supervisor_assignment_id IN NUMBER DEFAULT Hr_Api.g_number
*/
,p_scl_segment1 => l_hr_soft_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
);
l_datetrack_update_mode := 'CORRECTION';
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 => NVL(l_asg_rec.supervisor_id,
l_cur_asg_rec.supervisor_id)
,p_assignment_number => NVL(l_asg_rec.assignment_number,
l_cur_asg_rec.assignment_number)
,p_change_reason => NVL(l_asg_rec.change_reason,
l_cur_asg_rec.change_reason)
,p_date_probation_end => NVL(l_asg_rec.date_probation_end,
l_cur_asg_rec.date_probation_end)
,p_default_code_comb_id => NVL(l_asg_rec.default_code_comb_id,
l_cur_asg_rec.default_code_comb_id)
,p_frequency => NVL(l_asg_rec.frequency,
l_cur_asg_rec.frequency)
,p_internal_address_line => NVL(l_asg_rec.internal_address_line,
l_cur_asg_rec.internal_address_line)
,p_manager_flag => NVL(l_asg_rec.manager_flag,
l_cur_asg_rec.manager_flag)
,p_normal_hours => NVL(l_asg_rec.normal_hours,
l_cur_asg_rec.normal_hours)
,p_perf_review_period => NVL(l_asg_rec.perf_review_period,
l_cur_asg_rec.perf_review_period)
,p_perf_review_period_frequency => NVL(l_asg_rec.perf_review_period_frequency,
l_cur_asg_rec.perf_review_period_frequency)
,p_probation_period => NVL(l_asg_rec.probation_period,
l_cur_asg_rec.probation_period)
,p_probation_unit => NVL(l_asg_rec.probation_unit,
l_cur_asg_rec.probation_unit)
,p_sal_review_period => NVL(l_asg_rec.sal_review_period,
l_cur_asg_rec.sal_review_period)
,p_sal_review_period_frequency => NVL(l_asg_rec.sal_review_period_frequency,
l_cur_asg_rec.sal_review_period_frequency)
,p_set_of_books_id => NVL(l_asg_rec.set_of_books_id,
l_cur_asg_rec.set_of_books_id)
,p_source_type => NVL(l_asg_rec.source_type,
l_cur_asg_rec.source_type)
,p_time_normal_finish => NVL(l_asg_rec.time_normal_finish,
l_cur_asg_rec.time_normal_finish)
,p_time_normal_start => NVL(l_asg_rec.time_normal_start,
l_cur_asg_rec.time_normal_start)
,p_bargaining_unit_code => NVL(l_asg_rec.bargaining_unit_code,
l_cur_asg_rec.bargaining_unit_code)
,p_labour_union_member_flag => NVL(l_asg_rec.labour_union_member_flag,
l_cur_asg_rec.labour_union_member_flag)
,p_hourly_salaried_code => NVL(l_asg_rec.hourly_salaried_code,
l_cur_asg_rec.hourly_salaried_code)
,p_title => NVL(l_asg_rec.title,
l_cur_asg_rec.title)
-- 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_hr_soft_rec.segment1
,p_segment2 => l_hr_soft_rec.segment2
,p_segment3 => l_hr_soft_rec.segment3
,p_segment4 => l_hr_soft_rec.segment4
,p_segment5 => l_hr_soft_rec.segment5
,p_segment6 => l_hr_soft_rec.segment6
,p_segment7 => l_hr_soft_rec.segment7
,p_segment8 => l_hr_soft_rec.segment8
,p_segment9 => l_hr_soft_rec.segment9
,p_segment10 => l_hr_soft_rec.segment10
,p_segment11 => l_hr_soft_rec.segment11
,p_segment12 => l_hr_soft_rec.segment12
,p_segment13 => l_hr_soft_rec.segment13
,p_segment14 => l_hr_soft_rec.segment14
,p_segment15 => l_hr_soft_rec.segment15
,p_segment16 => l_hr_soft_rec.segment16
,p_segment17 => l_hr_soft_rec.segment17
,p_segment18 => l_hr_soft_rec.segment18
,p_segment19 => l_hr_soft_rec.segment19
,p_segment20 => l_hr_soft_rec.segment20
,p_segment21 => l_hr_soft_rec.segment21
,p_segment22 => l_hr_soft_rec.segment22
,p_segment23 => l_hr_soft_rec.segment23
,p_segment24 => l_hr_soft_rec.segment24
,p_segment25 => l_hr_soft_rec.segment25
,p_segment26 => l_hr_soft_rec.segment26
,p_segment27 => l_hr_soft_rec.segment27
,p_segment28 => l_hr_soft_rec.segment28
,p_segment29 => l_hr_soft_rec.segment29
,p_segment30 => l_hr_soft_rec.segment30
,p_concat_segments => l_hr_soft_rec.concatenated_segments
-- Out Parameters
,p_cagr_grade_def_id => l_asg_rec.cagr_grade_def_id
,p_soft_coding_keyflex_id => l_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
);
END Update_StuEmpAsg_Criteria;
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_effective_date BETWEEN pad.date_from
AND NVL(pad.date_to, c_effective_date);
,p_action => 'UPDATE'
,p_effective_date => p_effective_date
,p_pradd_ovlapval_override => FALSE
,p_primary_flag => 'Y'
,p_validate_county => TRUE
,p_HR_address_id => l_add_rec.address_id
,p_HR_object_version_number => l_add_rec.object_version_number);
SELECT paf.assignment_id
,ppf.person_id
,ppf.object_version_number per_ovn
,paf.object_version_number asg_ovn
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;
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';
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';
hr_utility.set_location(' l_datetrack_update_mode: ' ||
l_datetrack_update_mode, 100);
l_datetrack_update_mode := 'CORRECTION';
Hr_Person_Api.Update_Person
(p_validate => p_validate
,p_effective_date => p_hire_date
,p_datetrack_update_mode => l_datetrack_update_mode
,p_person_id => l_cur_per_rec.person_id
,p_party_id => l_per_rec.party_id
,p_object_version_number => l_cur_per_rec.object_version_number
,p_employee_number => l_per_rec.employee_number
,p_last_name => NVL(l_per_rec.last_name
,l_cur_per_rec.last_name)
,p_first_name => NVL(l_per_rec.first_name
,l_cur_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 => NVL(l_per_rec.marital_status
,l_cur_per_rec.marital_status)
,p_middle_names => NVL(l_per_rec.middle_names
,l_cur_per_rec.middle_names)
,p_sex => NVL(l_per_rec.sex
,l_cur_per_rec.sex)
,p_title => NVL(l_per_rec.title
,l_cur_per_rec.title)
,p_nationality => NVL(l_per_rec.nationality
,l_cur_per_rec.nationality)
,p_previous_last_name => NVL(l_per_rec.previous_last_name
,l_cur_per_rec.previous_last_name)
,p_known_as => NVL(l_per_rec.known_as
,l_cur_per_rec.known_as)
,p_email_address => NVL(l_per_rec.email_address
,l_cur_per_rec.email_address)
,p_registered_disabled_flag => NVL(l_per_rec.registered_disabled_flag
,l_cur_per_rec.registered_disabled_flag)
,p_date_employee_data_verified => NVL(l_per_rec.date_employee_data_verified
,l_cur_per_rec.date_employee_data_verified)
,p_expense_check_send_to_addres => NVL(l_per_rec.expense_check_send_to_address
,l_cur_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 => NVL(l_per_rec.date_of_death
,l_cur_per_rec.date_of_death)
,p_background_check_status => NVL(l_per_rec.background_check_status
,l_cur_per_rec.background_check_status)
,p_background_date_check => NVL(l_per_rec.background_date_check
,l_cur_per_rec.background_date_check)
,p_blood_type => NVL(l_per_rec.blood_type
,l_cur_per_rec.blood_type)
,p_correspondence_language => NVL(l_per_rec.correspondence_language
,l_cur_per_rec.correspondence_language)
,p_fte_capacity => NVL(l_per_rec.fte_capacity
,l_cur_per_rec.fte_capacity)
,p_hold_applicant_date_until => NVL(l_per_rec.hold_applicant_date_until
,l_cur_per_rec.hold_applicant_date_until)
,p_honors => NVL(l_per_rec.honors
,l_cur_per_rec.honors)
,p_internal_location => NVL(l_per_rec.internal_location
,l_cur_per_rec.internal_location)
,p_last_medical_test_by => NVL(l_per_rec.last_medical_test_by
,l_cur_per_rec.last_medical_test_by)
,p_last_medical_test_date => NVL(l_per_rec.last_medical_test_date
,l_cur_per_rec.last_medical_test_date)
,p_mailstop => NVL(l_per_rec.mailstop
,l_cur_per_rec.mailstop)
,p_office_number => NVL(l_per_rec.office_number
,l_cur_per_rec.office_number)
,p_on_military_service => NVL(l_per_rec.on_military_service
,l_cur_per_rec.on_military_service)
,p_pre_name_adjunct => NVL(l_per_rec.pre_name_adjunct
,l_cur_per_rec.pre_name_adjunct)
,p_projected_start_date => NVL(l_per_rec.projected_start_date
,l_cur_per_rec.projected_start_date)
,p_rehire_authorizor => NVL(l_per_rec.rehire_authorizor
,l_cur_per_rec.rehire_authorizor)
,p_rehire_recommendation => NVL(l_per_rec.rehire_recommendation
,l_cur_per_rec.rehire_recommendation)
,p_resume_exists => NVL(l_per_rec.resume_exists
,l_cur_per_rec.resume_exists )
,p_resume_last_updated => NVL(l_per_rec.resume_last_updated
,l_cur_per_rec.resume_last_updated)
,p_second_passport_exists => NVL(l_per_rec.second_passport_exists
,l_cur_per_rec.second_passport_exists)
,p_student_status => NVL(l_per_rec.student_status
,l_cur_per_rec.student_status)
,p_work_schedule => NVL(l_per_rec.work_schedule
,l_cur_per_rec.work_schedule)
,p_rehire_reason => NVL(l_per_rec.rehire_reason
,l_cur_per_rec.rehire_reason)
,p_suffix => NVL(l_per_rec.suffix
,l_cur_per_rec.suffix)
,p_benefit_group_id => NVL(l_per_rec.benefit_group_id
,l_cur_per_rec.benefit_group_id)
,p_receipt_of_death_cert_date => NVL(l_per_rec.receipt_of_death_cert_date
,l_cur_per_rec.receipt_of_death_cert_date)
,p_coord_ben_med_pln_no => NVL(l_per_rec.coord_ben_med_pln_no
,l_cur_per_rec.coord_ben_med_pln_no)
,p_coord_ben_no_cvg_flag => NVL(l_per_rec.coord_ben_no_cvg_flag
,l_cur_per_rec.coord_ben_no_cvg_flag)
,p_coord_ben_med_ext_er => NVL(l_per_rec.coord_ben_med_ext_er
,l_cur_per_rec.coord_ben_med_ext_er)
,p_coord_ben_med_pl_name => NVL(l_per_rec.coord_ben_med_pl_name
,l_cur_per_rec.coord_ben_med_pl_name)
,p_coord_ben_med_insr_crr_name => NVL(l_per_rec.coord_ben_med_insr_crr_name
,l_cur_per_rec.coord_ben_med_insr_crr_name)
,p_coord_ben_med_insr_crr_ident => NVL(l_per_rec.coord_ben_med_insr_crr_ident
,l_cur_per_rec.coord_ben_med_insr_crr_ident)
,p_coord_ben_med_cvg_strt_dt => NVL(l_per_rec.coord_ben_med_cvg_strt_dt
,l_cur_per_rec.coord_ben_med_cvg_strt_dt)
,p_coord_ben_med_cvg_end_dt => NVL(l_per_rec.coord_ben_med_cvg_end_dt
,l_cur_per_rec.coord_ben_med_cvg_end_dt)
,p_uses_tobacco_flag => NVL(l_per_rec.uses_tobacco_flag
,l_cur_per_rec.uses_tobacco_flag)
,p_dpdnt_adoption_date => NVL(l_per_rec.dpdnt_adoption_date
,l_cur_per_rec.dpdnt_adoption_date)
,p_dpdnt_vlntry_svce_flag => NVL(l_per_rec.dpdnt_vlntry_svce_flag
,l_cur_per_rec.dpdnt_vlntry_svce_flag)
,p_original_date_of_hire => NVL(l_per_rec.original_date_of_hire
,l_cur_per_rec.original_date_of_hire)
,p_adjusted_svc_date => p_adjusted_svc_date
,p_town_of_birth => NVL(l_per_rec.town_of_birth
,l_cur_per_rec.town_of_birth)
,p_region_of_birth => NVL(l_per_rec.region_of_birth
,l_cur_per_rec.region_of_birth)
,p_country_of_birth => NVL(l_per_rec.country_of_birth
,l_cur_per_rec.country_of_birth)
,p_global_person_id => NVL(l_per_rec.global_person_id
,l_cur_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
);
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 paf.assignment_type = 'E'
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';
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 => l_per_rec.person_type_id
,p_national_identifier => l_per_rec.national_identifier
,p_per_information7 => l_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';
Hr_Person_Api.Update_Person
(p_validate => p_validate
,p_effective_date => p_hire_date
,p_datetrack_update_mode => l_datetrack_update_mode
,p_person_id => l_cur_per_rec.person_id
,p_party_id => l_per_rec.party_id
,p_object_version_number => l_per_rec.object_version_number
,p_employee_number => l_per_rec.employee_number
,p_last_name => NVL(l_per_rec.last_name
,l_cur_per_rec.last_name)
,p_first_name => NVL(l_per_rec.first_name
,l_cur_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 => NVL(l_per_rec.marital_status
,l_cur_per_rec.marital_status)
,p_middle_names => NVL(l_per_rec.middle_names
,l_cur_per_rec.middle_names)
,p_sex => NVL(l_per_rec.sex
,l_cur_per_rec.sex)
,p_title => NVL(l_per_rec.title
,l_cur_per_rec.title)
,p_nationality => NVL(l_per_rec.nationality
,l_cur_per_rec.nationality)
,p_previous_last_name => NVL(l_per_rec.previous_last_name
,l_cur_per_rec.previous_last_name)
,p_known_as => NVL(l_per_rec.known_as
,l_cur_per_rec.known_as)
,p_email_address => NVL(l_per_rec.email_address
,l_cur_per_rec.email_address)
,p_registered_disabled_flag => NVL(l_per_rec.registered_disabled_flag
,l_cur_per_rec.registered_disabled_flag)
,p_date_employee_data_verified => NVL(l_per_rec.date_employee_data_verified
,l_cur_per_rec.date_employee_data_verified)
,p_expense_check_send_to_addres =>NVL(l_per_rec.expense_check_send_to_address
,l_cur_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 => NVL(l_per_rec.date_of_death
,l_cur_per_rec.date_of_death)
,p_background_check_status => NVL(l_per_rec.background_check_status
,l_cur_per_rec.background_check_status)
,p_background_date_check => NVL(l_per_rec.background_date_check
,l_cur_per_rec.background_date_check)
,p_blood_type => NVL(l_per_rec.blood_type
,l_cur_per_rec.blood_type)
,p_correspondence_language => NVL(l_per_rec.correspondence_language
,l_cur_per_rec.correspondence_language)
,p_fte_capacity => NVL(l_per_rec.fte_capacity
,l_cur_per_rec.fte_capacity)
,p_hold_applicant_date_until => NVL(l_per_rec.hold_applicant_date_until
,l_cur_per_rec.hold_applicant_date_until)
,p_honors => NVL(l_per_rec.honors
,l_cur_per_rec.honors)
,p_internal_location => NVL(l_per_rec.internal_location
,l_cur_per_rec.internal_location)
,p_last_medical_test_by => NVL(l_per_rec.last_medical_test_by
,l_cur_per_rec.last_medical_test_by)
,p_last_medical_test_date => NVL(l_per_rec.last_medical_test_date
,l_cur_per_rec.last_medical_test_date)
,p_mailstop => NVL(l_per_rec.mailstop
,l_cur_per_rec.mailstop)
,p_office_number => NVL(l_per_rec.office_number
,l_cur_per_rec.office_number)
,p_on_military_service => NVL(l_per_rec.on_military_service
,l_cur_per_rec.on_military_service)
,p_pre_name_adjunct => NVL(l_per_rec.pre_name_adjunct
,l_cur_per_rec.pre_name_adjunct)
,p_projected_start_date => NVL(l_per_rec.projected_start_date
,l_cur_per_rec.projected_start_date)
,p_rehire_authorizor => NVL(l_per_rec.rehire_authorizor
,l_cur_per_rec.rehire_authorizor)
,p_rehire_recommendation => NVL(l_per_rec.rehire_recommendation
,l_cur_per_rec.rehire_recommendation)
,p_resume_exists => NVL(l_per_rec.resume_exists
,l_cur_per_rec.resume_exists )
,p_resume_last_updated => NVL(l_per_rec.resume_last_updated
,l_cur_per_rec.resume_last_updated)
,p_second_passport_exists => NVL(l_per_rec.second_passport_exists
,l_cur_per_rec.second_passport_exists)
,p_student_status => NVL(l_per_rec.student_status
,l_cur_per_rec.student_status)
,p_work_schedule => NVL(l_per_rec.work_schedule
,l_cur_per_rec.work_schedule)
,p_rehire_reason => NVL(l_per_rec.rehire_reason
,l_cur_per_rec.rehire_reason)
,p_suffix => NVL(l_per_rec.suffix
,l_cur_per_rec.suffix)
,p_benefit_group_id => NVL(l_per_rec.benefit_group_id
,l_cur_per_rec.benefit_group_id)
,p_receipt_of_death_cert_date => NVL(l_per_rec.receipt_of_death_cert_date
,l_cur_per_rec.receipt_of_death_cert_date)
,p_coord_ben_med_pln_no => NVL(l_per_rec.coord_ben_med_pln_no
,l_cur_per_rec.coord_ben_med_pln_no)
,p_coord_ben_no_cvg_flag => NVL(l_per_rec.coord_ben_no_cvg_flag
,l_cur_per_rec.coord_ben_no_cvg_flag)
,p_coord_ben_med_ext_er => NVL(l_per_rec.coord_ben_med_ext_er
,l_cur_per_rec.coord_ben_med_ext_er)
,p_coord_ben_med_pl_name => NVL(l_per_rec.coord_ben_med_pl_name
,l_cur_per_rec.coord_ben_med_pl_name)
,p_coord_ben_med_insr_crr_name => NVL(l_per_rec.coord_ben_med_insr_crr_name
,l_cur_per_rec.coord_ben_med_insr_crr_name)
,p_coord_ben_med_insr_crr_ident => NVL(l_per_rec.coord_ben_med_insr_crr_ident
,l_cur_per_rec.coord_ben_med_insr_crr_ident)
,p_coord_ben_med_cvg_strt_dt => NVL(l_per_rec.coord_ben_med_cvg_strt_dt
,l_cur_per_rec.coord_ben_med_cvg_strt_dt)
,p_coord_ben_med_cvg_end_dt => NVL(l_per_rec.coord_ben_med_cvg_end_dt
,l_cur_per_rec.coord_ben_med_cvg_end_dt)
,p_uses_tobacco_flag => NVL(l_per_rec.uses_tobacco_flag
,l_cur_per_rec.uses_tobacco_flag)
,p_dpdnt_adoption_date => NVL(l_per_rec.dpdnt_adoption_date
,l_cur_per_rec.dpdnt_adoption_date)
,p_dpdnt_vlntry_svce_flag => NVL(l_per_rec.dpdnt_vlntry_svce_flag
,l_cur_per_rec.dpdnt_vlntry_svce_flag)
,p_original_date_of_hire => NVL(l_per_rec.original_date_of_hire
,l_cur_per_rec.original_date_of_hire)
,p_adjusted_svc_date => p_adjusted_svc_date
,p_town_of_birth => NVL(l_per_rec.town_of_birth
,l_cur_per_rec.town_of_birth)
,p_region_of_birth => NVL(l_per_rec.region_of_birth
,l_cur_per_rec.region_of_birth)
,p_country_of_birth => NVL(l_per_rec.country_of_birth
,l_cur_per_rec.country_of_birth)
,p_global_person_id => NVL(l_per_rec.global_person_id
,l_cur_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
);
hr_utility.set_location('..Future Update exists for the Student Employee', 60);
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 NOT(l_dt_update AND
l_dt_upd_override AND
l_upd_chg_ins) THEN
l_datetrack_update_mode := 'CORRECTION';
ELSIF l_dt_update THEN
l_datetrack_update_mode := 'UPDATE';
hr_utility.set_location(' l_datetrack_update_mode: ' ||
l_cur_per_rec.person_id, 30);
l_datetrack_update_mode := 'CORRECTION';
Hr_Person_Api.Update_Person
(p_validate => p_validate
,p_effective_date => p_rehire_date
,p_datetrack_update_mode => l_datetrack_update_mode
,p_person_id => l_cur_per_rec.person_id
,p_party_id => l_per_rec.party_id
,p_object_version_number => l_cur_per_rec.object_version_number
,p_person_type_id => l_per_rec.person_type_id
,p_employee_number => l_per_rec.employee_number
,p_last_name => NVL(l_per_rec.last_name
,l_cur_per_rec.last_name)
,p_first_name => NVL(l_per_rec.first_name
,l_cur_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 => NVL(l_per_rec.marital_status
,l_cur_per_rec.marital_status)
,p_middle_names => NVL(l_per_rec.middle_names
,l_cur_per_rec.middle_names)
,p_sex => NVL(l_per_rec.sex
,l_cur_per_rec.sex)
,p_title => NVL(l_per_rec.title
,l_cur_per_rec.title)
,p_nationality => NVL(l_per_rec.nationality
,l_cur_per_rec.nationality)
,p_national_identifier => NVL(l_per_rec.national_identifier
,l_cur_per_rec.national_identifier)
,p_previous_last_name => NVL(l_per_rec.previous_last_name
,l_cur_per_rec.previous_last_name)
,p_known_as => NVL(l_per_rec.known_as
,l_cur_per_rec.known_as)
,p_registered_disabled_flag => NVL(l_per_rec.registered_disabled_flag
,l_cur_per_rec.registered_disabled_flag)
/*
,p_applicant_number =>
,p_comments =>
*/
,p_date_employee_data_verified => NVL(l_per_rec.date_employee_data_verified
,l_cur_per_rec.date_employee_data_verified)
,p_email_address => NVL(l_per_rec.email_address
,l_cur_per_rec.email_address)
,p_expense_check_send_to_addres => NVL(l_per_rec.expense_check_send_to_address
,l_cur_per_rec.expense_check_send_to_address)
-- Person DDF
/*
,p_per_information_category IN Varchar2 DEFAULT Hr_Api.g_Varchar2
,p_per_information1 IN Varchar2 DEFAULT Hr_Api.g_Varchar2
,p_per_information2 IN Varchar2 DEFAULT Hr_Api.g_Varchar2
,p_per_information3 IN Varchar2 DEFAULT Hr_Api.g_Varchar2
,p_per_information4 IN Varchar2 DEFAULT Hr_Api.g_Varchar2
,p_per_information5 IN Varchar2 DEFAULT Hr_Api.g_Varchar2
,p_per_information6 IN Varchar2 DEFAULT Hr_Api.g_Varchar2
,p_per_information7 IN Varchar2 DEFAULT Hr_Api.g_Varchar2
,p_per_information8 IN Varchar2 DEFAULT Hr_Api.g_Varchar2
,p_per_information9 IN Varchar2 DEFAULT Hr_Api.g_Varchar2
,p_per_information10 IN Varchar2 DEFAULT Hr_Api.g_Varchar2
,p_per_information11 IN Varchar2 DEFAULT Hr_Api.g_Varchar2
,p_per_information12 IN Varchar2 DEFAULT Hr_Api.g_Varchar2
,p_per_information13 IN Varchar2 DEFAULT Hr_Api.g_Varchar2
,p_per_information14 IN Varchar2 DEFAULT Hr_Api.g_Varchar2
,p_per_information15 IN Varchar2 DEFAULT Hr_Api.g_Varchar2
,p_per_information16 IN Varchar2 DEFAULT Hr_Api.g_Varchar2
,p_per_information17 IN Varchar2 DEFAULT Hr_Api.g_Varchar2
,p_per_information18 IN Varchar2 DEFAULT Hr_Api.g_Varchar2
,p_per_information19 IN Varchar2 DEFAULT Hr_Api.g_Varchar2
,p_per_information20 IN Varchar2 DEFAULT Hr_Api.g_Varchar2
,p_per_information21 IN Varchar2 DEFAULT Hr_Api.g_Varchar2
,p_per_information22 IN Varchar2 DEFAULT Hr_Api.g_Varchar2
,p_per_information23 IN Varchar2 DEFAULT Hr_Api.g_Varchar2
,p_per_information24 IN Varchar2 DEFAULT Hr_Api.g_Varchar2
,p_per_information25 IN Varchar2 DEFAULT Hr_Api.g_Varchar2
,p_per_information26 IN Varchar2 DEFAULT Hr_Api.g_Varchar2
,p_per_information27 IN Varchar2 DEFAULT Hr_Api.g_Varchar2
,p_per_information28 IN Varchar2 DEFAULT Hr_Api.g_Varchar2
,p_per_information29 IN Varchar2 DEFAULT Hr_Api.g_Varchar2
,p_per_information30 IN Varchar2 DEFAULT Hr_Api.g_Varchar2
-- Person DF
,p_attribute_category IN Varchar2 DEFAULT Hr_Api.g_Varchar2
,p_attribute1 IN Varchar2 DEFAULT Hr_Api.g_Varchar2
,p_attribute2 IN Varchar2 DEFAULT Hr_Api.g_Varchar2
,p_attribute3 IN Varchar2 DEFAULT Hr_Api.g_Varchar2
,p_attribute4 IN Varchar2 DEFAULT Hr_Api.g_Varchar2
,p_attribute5 IN Varchar2 DEFAULT Hr_Api.g_Varchar2
,p_attribute6 IN Varchar2 DEFAULT Hr_Api.g_Varchar2
,p_attribute7 IN Varchar2 DEFAULT Hr_Api.g_Varchar2
,p_attribute8 IN Varchar2 DEFAULT Hr_Api.g_Varchar2
,p_attribute9 IN Varchar2 DEFAULT Hr_Api.g_Varchar2
,p_attribute10 IN Varchar2 DEFAULT Hr_Api.g_Varchar2
,p_attribute11 IN Varchar2 DEFAULT Hr_Api.g_Varchar2
,p_attribute12 IN Varchar2 DEFAULT Hr_Api.g_Varchar2
,p_attribute13 IN Varchar2 DEFAULT Hr_Api.g_Varchar2
,p_attribute14 IN Varchar2 DEFAULT Hr_Api.g_Varchar2
,p_attribute15 IN Varchar2 DEFAULT Hr_Api.g_Varchar2
,p_attribute16 IN Varchar2 DEFAULT Hr_Api.g_Varchar2
,p_attribute17 IN Varchar2 DEFAULT Hr_Api.g_Varchar2
,p_attribute18 IN Varchar2 DEFAULT Hr_Api.g_Varchar2
,p_attribute19 IN Varchar2 DEFAULT Hr_Api.g_Varchar2
,p_attribute20 IN Varchar2 DEFAULT Hr_Api.g_Varchar2
,p_attribute21 IN Varchar2 DEFAULT Hr_Api.g_Varchar2
,p_attribute22 IN Varchar2 DEFAULT Hr_Api.g_Varchar2
,p_attribute23 IN Varchar2 DEFAULT Hr_Api.g_Varchar2
,p_attribute24 IN Varchar2 DEFAULT Hr_Api.g_Varchar2
,p_attribute25 IN Varchar2 DEFAULT Hr_Api.g_Varchar2
,p_attribute26 IN Varchar2 DEFAULT Hr_Api.g_Varchar2
,p_attribute27 IN Varchar2 DEFAULT Hr_Api.g_Varchar2
,p_attribute28 IN Varchar2 DEFAULT Hr_Api.g_Varchar2
,p_attribute29 IN Varchar2 DEFAULT Hr_Api.g_Varchar2
,p_attribute30 IN Varchar2 DEFAULT Hr_Api.g_Varchar2
,p_vendor_id IN NUMBER DEFAULT Hr_Api.g_number
,p_work_telephone IN Varchar2 DEFAULT Hr_Api.g_Varchar2
,p_date_of_death IN DATE DEFAULT Hr_Api.g_date
,p_background_check_status IN Varchar2 DEFAULT Hr_Api.g_Varchar2
,p_background_date_check IN DATE DEFAULT Hr_Api.g_date
,p_blood_type IN Varchar2 DEFAULT Hr_Api.g_Varchar2
,p_correspondence_language IN Varchar2 DEFAULT Hr_Api.g_Varchar2
,p_fast_path_employee IN Varchar2 DEFAULT Hr_Api.g_Varchar2
,p_fte_capacity IN NUMBER DEFAULT Hr_Api.g_number
,p_hold_applicant_date_until IN DATE DEFAULT Hr_Api.g_date
,p_honors IN Varchar2 DEFAULT Hr_Api.g_Varchar2
,p_internal_location IN Varchar2 DEFAULT Hr_Api.g_Varchar2
,p_last_medical_test_by IN Varchar2 DEFAULT Hr_Api.g_Varchar2
,p_last_medical_test_date IN DATE DEFAULT Hr_Api.g_date
,p_mailstop IN Varchar2 DEFAULT Hr_Api.g_Varchar2
,p_office_number IN Varchar2 DEFAULT Hr_Api.g_Varchar2
,p_on_military_service IN Varchar2 DEFAULT Hr_Api.g_Varchar2
,p_pre_name_adjunct IN Varchar2 DEFAULT Hr_Api.g_Varchar2
,p_projected_start_date IN DATE DEFAULT Hr_Api.g_date
,p_rehire_authorizor IN Varchar2 DEFAULT Hr_Api.g_Varchar2
,p_rehire_recommendation IN Varchar2 DEFAULT Hr_Api.g_Varchar2
,p_resume_exists IN Varchar2 DEFAULT Hr_Api.g_Varchar2
,p_resume_last_updated IN DATE DEFAULT Hr_Api.g_date
,p_second_passport_exists IN Varchar2 DEFAULT Hr_Api.g_Varchar2
,p_student_status IN Varchar2 DEFAULT Hr_Api.g_Varchar2
,p_work_schedule IN Varchar2 DEFAULT Hr_Api.g_Varchar2
,p_rehire_reason IN Varchar2 DEFAULT Hr_Api.g_Varchar2
,p_suffix IN Varchar2 DEFAULT Hr_Api.g_Varchar2
,p_benefit_group_id IN NUMBER DEFAULT Hr_Api.g_number
,p_receipt_of_death_cert_date IN DATE DEFAULT Hr_Api.g_date
,p_coord_ben_med_pln_no IN Varchar2 DEFAULT Hr_Api.g_Varchar2
,p_coord_ben_no_cvg_flag IN Varchar2 DEFAULT Hr_Api.g_Varchar2
,p_coord_ben_med_ext_er IN Varchar2 DEFAULT Hr_Api.g_Varchar2
,p_coord_ben_med_pl_name IN Varchar2 DEFAULT Hr_Api.g_Varchar2
,p_coord_ben_med_insr_crr_name IN Varchar2 DEFAULT Hr_Api.g_Varchar2
,p_coord_ben_med_insr_crr_ident IN Varchar2 DEFAULT Hr_Api.g_Varchar2
,p_coord_ben_med_cvg_strt_dt IN DATE DEFAULT Hr_Api.g_date
,p_coord_ben_med_cvg_end_dt IN DATE DEFAULT Hr_Api.g_date
,p_uses_tobacco_flag IN Varchar2 DEFAULT Hr_Api.g_Varchar2
,p_dpdnt_adoption_date IN DATE DEFAULT Hr_Api.g_date
,p_dpdnt_vlntry_svce_flag IN Varchar2 DEFAULT Hr_Api.g_Varchar2
,p_original_date_of_hire IN DATE DEFAULT Hr_Api.g_date
,p_adjusted_svc_date IN DATE DEFAULT Hr_Api.g_date
,p_town_of_birth IN Varchar2 DEFAULT Hr_Api.g_Varchar2
,p_region_of_birth IN Varchar2 DEFAULT Hr_Api.g_Varchar2
,p_country_of_birth IN Varchar2 DEFAULT Hr_Api.g_Varchar2
,p_global_person_id IN Varchar2 DEFAULT Hr_Api.g_Varchar2
,p_npw_number IN Varchar2 DEFAULT Hr_Api.g_Varchar2
*/
,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_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_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_adjusted_svc_date => p_adjusted_svc_date
,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_party_id => l_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
);
SELECT pei.pei_information1
,pei.pei_information2
,pei.pei_information3
,pei.pei_information4
,pei.pei_information5
,pei.object_version_number
,pei.person_extra_info_id
FROM per_people_extra_info pei
WHERE pei.person_id = c_person_id
AND pei.information_type = c_information_type;
SELECT hzp.party_number
FROM hz_parties hzp
WHERE hzp.party_id = c_party_id;
' SELECT igp.person_id_type ' ||
' ,igp.api_person_id ' ||
' ,igp.person_number ' ||
' ,ipt.system_type ' ||
' FROM igs_pe_person_v igp ' ||
' ,igs_pe_typ_instances_all pti ' ||
' ,igs_pe_person_types ipt ' ||
' WHERE igp.person_id = :c_party_id ' ||
' AND pti.person_type_code = ipt.person_type_code ' ||
' AND pti.person_id = igp.person_id ' ||
' AND ipt.system_type IN ('||'''STUDENT'''||',' ||
'''FACULTY'''||','||'''OTHER''' ||')' ;
hrdpp_create_person_extra_info.insert_batch_lines
(p_batch_id => p_batch_id
,p_user_sequence => p_user_sequence
,p_link_value => p_link_value
,p_information_type => l_person_extra_info_rec.information_type
,p_pei_information_category =>
l_person_extra_info_rec.pei_information_category
,p_pei_information1 => l_person_extra_info_rec.pei_information1
,p_pei_information2 => l_person_extra_info_rec.pei_information2
,p_pei_information3 => l_person_extra_info_rec.pei_information3
,p_pei_information4 => l_person_extra_info_rec.pei_information4
,p_pei_information5 => l_person_extra_info_rec.pei_information5
,p_pei_information6 => l_person_extra_info_rec.pei_information6
,p_pei_information7 => l_person_extra_info_rec.pei_information7
,p_pei_information8 => l_person_extra_info_rec.pei_information8
,p_pei_information9 => l_person_extra_info_rec.pei_information9
,p_pei_information10 => l_person_extra_info_rec.pei_information10
,p_pei_information11 => l_person_extra_info_rec.pei_information11
,p_pei_information12 => l_person_extra_info_rec.pei_information12
,p_pei_information13 => l_person_extra_info_rec.pei_information13
,p_pei_information14 => l_person_extra_info_rec.pei_information14
,p_pei_information15 => l_person_extra_info_rec.pei_information15
,p_pei_information16 => l_person_extra_info_rec.pei_information16
,p_pei_information17 => l_person_extra_info_rec.pei_information17
,p_pei_information18 => l_person_extra_info_rec.pei_information18
,p_pei_information19 => l_person_extra_info_rec.pei_information19
,p_pei_information20 => l_person_extra_info_rec.pei_information20
,p_pei_information21 => l_person_extra_info_rec.pei_information21
,p_pei_information22 => l_person_extra_info_rec.pei_information22
,p_pei_information23 => l_person_extra_info_rec.pei_information23
,p_pei_information24 => l_person_extra_info_rec.pei_information24
,p_pei_information25 => l_person_extra_info_rec.pei_information25
,p_pei_information26 => l_person_extra_info_rec.pei_information26
,p_pei_information27 => l_person_extra_info_rec.pei_information27
,p_pei_information28 => l_person_extra_info_rec.pei_information28
,p_pei_information29 => l_person_extra_info_rec.pei_information29
,p_pei_information30 => l_person_extra_info_rec.pei_information30
,p_person_extra_info_user_key => p_xtra_info_key
,p_person_user_key => p_person_user_key
);
hrdpp_update_person_extra_info.insert_batch_lines
(p_batch_id => p_batch_id
,p_user_sequence => p_user_sequence
,p_link_value => p_link_value
,p_pei_information_category =>
l_person_extra_info_rec.pei_information_category
,p_pei_information1 => l_person_extra_info_rec.pei_information1
,p_pei_information2 => l_person_extra_info_rec.pei_information2
,p_pei_information3 => l_person_extra_info_rec.pei_information3
,p_pei_information4 => l_person_extra_info_rec.pei_information4
,p_pei_information5 => l_person_extra_info_rec.pei_information5
,p_pei_information6 => l_person_extra_info_rec.pei_information6
,p_pei_information7 => l_person_extra_info_rec.pei_information7
,p_pei_information8 => l_person_extra_info_rec.pei_information8
,p_pei_information9 => l_person_extra_info_rec.pei_information9
,p_pei_information10 => l_person_extra_info_rec.pei_information10
,p_pei_information11 => l_person_extra_info_rec.pei_information11
,p_pei_information12 => l_person_extra_info_rec.pei_information12
,p_pei_information13 => l_person_extra_info_rec.pei_information13
,p_pei_information14 => l_person_extra_info_rec.pei_information14
,p_pei_information15 => l_person_extra_info_rec.pei_information15
,p_pei_information16 => l_person_extra_info_rec.pei_information16
,p_pei_information17 => l_person_extra_info_rec.pei_information17
,p_pei_information18 => l_person_extra_info_rec.pei_information18
,p_pei_information19 => l_person_extra_info_rec.pei_information19
,p_pei_information20 => l_person_extra_info_rec.pei_information20
,p_pei_information21 => l_person_extra_info_rec.pei_information21
,p_pei_information22 => l_person_extra_info_rec.pei_information22
,p_pei_information23 => l_person_extra_info_rec.pei_information23
,p_pei_information24 => l_person_extra_info_rec.pei_information24
,p_pei_information25 => l_person_extra_info_rec.pei_information25
,p_pei_information26 => l_person_extra_info_rec.pei_information26
,p_pei_information27 => l_person_extra_info_rec.pei_information27
,p_pei_information28 => l_person_extra_info_rec.pei_information28
,p_pei_information29 => l_person_extra_info_rec.pei_information29
,p_pei_information30 => l_person_extra_info_rec.pei_information30
,p_person_extra_info_user_key => p_xtra_info_key
);
SELECT Max(link_value) + 1
FROM hr_pump_batch_lines
WHERE batch_id = c_batch_id;
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;
SELECT *
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);
l_datetrack_update_mode Varchar2(50);
l_dt_update Boolean;
IF p_dp_mode = 'INSERT' THEN
Hrdpp_Create_Employee.Insert_Batch_Lines
(p_batch_id => p_batch_id
,p_user_sequence => l_user_sequence
,p_link_value => l_link_value
,p_hire_date => l_per_rec.START_DATE
,p_last_name => l_per_rec.last_name
,p_sex => l_per_rec.sex
--,p_per_comments => l_per_rec.comments
,p_date_employee_data_verified => l_per_rec.date_employee_data_verified
,p_date_of_birth => l_per_rec.date_of_birth
,p_email_address => l_per_rec.email_address
,p_employee_number => l_per_rec.employee_number
,p_expense_check_send_to_addres => l_per_rec.expense_check_send_to_address
,p_first_name => l_per_rec.first_name
,p_known_as => l_per_rec.known_as
,p_marital_status => l_per_rec.marital_status
,p_middle_names => l_per_rec.middle_names
,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_registered_disabled_flag => l_per_rec.registered_disabled_flag
,p_title => l_per_rec.title
--,p_work_telephone => p_telephone_no1
,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_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
,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_fast_path_employee => l_per_rec.fast_path_employee
,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_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_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_suffix => l_per_rec.suffix
,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_adjusted_svc_date => p_adjusted_svc_date
,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_party_id => l_per_rec.party_id
,p_correspondence_language => l_per_rec.correspondence_language
,p_benefit_group => l_per_rec.benefit_group_id
,p_person_user_key => l_person_user_key
,p_assignment_user_key => l_assignment_user_key
,p_user_person_type => l_user_person_type
,p_language_code => Userenv('lang')
,p_vendor_name => NULL
);
hr_utility.set_location('..Inserted into Hrdpp_Create_Employee', 30);
ELSIF p_dp_mode = 'UPDATE' THEN
-- Cursor to get the Latest Details of Person in System
OPEN csr_per(c_person_id => l_per_rec.person_id
,c_business_group_id => l_per_rec.business_group_id
,c_effective_date => l_per_rec.START_DATE);
,p_update => l_dt_update
,p_update_override => l_dt_upd_override
,p_update_change_insert => l_upd_chg_ins
);
IF NOT(l_dt_update AND
l_dt_upd_override AND
l_upd_chg_ins) THEN
l_datetrack_update_mode := 'CORRECTION';
ELSIF l_dt_update THEN
l_datetrack_update_mode := 'UPDATE';
Hrdpp_Hire_Into_Job.insert_batch_lines
(p_batch_id => p_batch_id
,p_user_sequence => l_user_sequence
,p_link_value => l_link_value
,p_effective_date => l_per_rec.START_DATE
,p_object_version_number => l_cur_per_rec.object_version_number
,p_employee_number => l_per_rec.employee_number
,p_datetrack_update_mode => l_datetrack_update_mode
,p_national_identifier => l_per_rec.national_identifier
,p_per_information7 => NULL
,p_person_user_key => l_person_user_key
,p_assignment_user_key => l_assignment_user_key
,p_user_person_type => l_user_person_type
,p_language_code => Userenv('LANG')
);
Hrdpp_Update_Person.insert_batch_lines
(p_batch_id => p_batch_id
,p_user_sequence => l_user_sequence
,p_link_value => l_link_value
,p_effective_date => l_per_rec.START_DATE
,p_datetrack_update_mode => l_datetrack_update_mode
,p_party_id => l_per_rec.party_id
,p_employee_number => l_per_rec.employee_number
,p_last_name => NVL(l_per_rec.last_name
,l_cur_per_rec.last_name)
,p_first_name => NVL(l_per_rec.first_name
,l_cur_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 => NVL(l_per_rec.marital_status
,l_cur_per_rec.marital_status)
,p_middle_names => NVL(l_per_rec.middle_names
,l_cur_per_rec.middle_names)
,p_sex => NVL(l_per_rec.sex
,l_cur_per_rec.sex)
,p_title => NVL(l_per_rec.title
,l_cur_per_rec.title)
,p_nationality => NVL(l_per_rec.nationality
,l_cur_per_rec.nationality)
,p_previous_last_name => NVL(l_per_rec.previous_last_name
,l_cur_per_rec.previous_last_name)
,p_national_identifier => NVL(l_per_rec.national_identifier
,l_cur_per_rec.national_identifier)
,p_known_as => NVL(l_per_rec.known_as
,l_cur_per_rec.known_as)
,p_email_address => NVL(l_per_rec.email_address
,l_cur_per_rec.email_address)
,p_registered_disabled_flag => NVL(l_per_rec.registered_disabled_flag
,l_cur_per_rec.registered_disabled_flag)
,p_date_employee_data_verified =>NVL(l_per_rec.date_employee_data_verified
,l_cur_per_rec.date_employee_data_verified)
,p_expense_check_send_to_addres=>NVL(l_per_rec.expense_check_send_to_address
,l_cur_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_vendor_id => l_per_rec.vendor_id
--,p_work_telephone => l_per_rec.vendor_id
,p_date_of_death => NVL(l_per_rec.date_of_death
,l_cur_per_rec.date_of_death)
,p_background_check_status => NVL(l_per_rec.background_check_status
,l_cur_per_rec.background_check_status)
,p_background_date_check => NVL(l_per_rec.background_date_check
,l_cur_per_rec.background_date_check)
,p_blood_type => NVL(l_per_rec.blood_type
,l_cur_per_rec.blood_type)
,p_correspondence_language => NVL(l_per_rec.correspondence_language
,l_cur_per_rec.correspondence_language)
--,p_fast_path_employee IN Varchar2 DEFAULT Hr_Api.g_Varchar2
,p_fte_capacity => NVL(l_per_rec.fte_capacity
,l_cur_per_rec.fte_capacity)
,p_hold_applicant_date_until => NVL(l_per_rec.hold_applicant_date_until
,l_cur_per_rec.hold_applicant_date_until)
,p_honors => NVL(l_per_rec.honors
,l_cur_per_rec.honors)
,p_internal_location => NVL(l_per_rec.internal_location
,l_cur_per_rec.internal_location)
,p_last_medical_test_by => NVL(l_per_rec.last_medical_test_by
,l_cur_per_rec.last_medical_test_by)
,p_last_medical_test_date => NVL(l_per_rec.last_medical_test_date
,l_cur_per_rec.last_medical_test_date)
,p_mailstop => NVL(l_per_rec.mailstop
,l_cur_per_rec.mailstop)
,p_office_number => NVL(l_per_rec.office_number
,l_cur_per_rec.office_number)
,p_on_military_service => NVL(l_per_rec.on_military_service
,l_cur_per_rec.on_military_service)
,p_pre_name_adjunct => NVL(l_per_rec.pre_name_adjunct
,l_cur_per_rec.pre_name_adjunct)
,p_projected_start_date => NVL(l_per_rec.projected_start_date
,l_cur_per_rec.projected_start_date)
,p_rehire_authorizor => NVL(l_per_rec.rehire_authorizor
,l_cur_per_rec.rehire_authorizor)
,p_rehire_recommendation => NVL(l_per_rec.rehire_recommendation
,l_cur_per_rec.rehire_recommendation)
,p_resume_exists => NVL(l_per_rec.resume_exists
,l_cur_per_rec.resume_exists)
,p_resume_last_updated => NVL(l_per_rec.resume_last_updated
,l_cur_per_rec.resume_last_updated)
,p_second_passport_exists => NVL(l_per_rec.second_passport_exists
,l_cur_per_rec.second_passport_exists)
,p_student_status => NVL(l_per_rec.student_status
,l_cur_per_rec.student_status)
,p_work_schedule => NVL(l_per_rec.work_schedule
,l_cur_per_rec.work_schedule)
,p_rehire_reason => NVL(l_per_rec.rehire_reason
,l_cur_per_rec.rehire_reason)
,p_suffix => NVL(l_per_rec.suffix
,l_cur_per_rec.suffix)
,p_benefit_group => NVL(l_per_rec.benefit_group_id
,l_cur_per_rec.benefit_group_id)
,p_receipt_of_death_cert_date => NVL(l_per_rec.receipt_of_death_cert_date
,l_cur_per_rec.receipt_of_death_cert_date)
,p_coord_ben_med_pln_no => NVL(l_per_rec.coord_ben_med_pln_no
,l_cur_per_rec.coord_ben_med_pln_no)
,p_coord_ben_no_cvg_flag => NVL(l_per_rec.coord_ben_no_cvg_flag
,l_cur_per_rec.coord_ben_no_cvg_flag)
,p_coord_ben_med_ext_er => NVL(l_per_rec.coord_ben_med_ext_er
,l_cur_per_rec.coord_ben_med_ext_er)
,p_coord_ben_med_pl_name => NVL(l_per_rec.coord_ben_med_pl_name
,l_cur_per_rec.coord_ben_med_pl_name)
,p_coord_ben_med_insr_crr_name =>NVL(l_per_rec.coord_ben_med_insr_crr_name
,l_cur_per_rec.coord_ben_med_insr_crr_name)
,p_coord_ben_med_insr_crr_ident=>NVL(l_per_rec.coord_ben_med_insr_crr_ident
,l_cur_per_rec.coord_ben_med_insr_crr_ident)
,p_coord_ben_med_cvg_strt_dt => NVL(l_per_rec.coord_ben_med_cvg_strt_dt
,l_cur_per_rec.coord_ben_med_cvg_strt_dt)
,p_coord_ben_med_cvg_end_dt => NVL(l_per_rec.coord_ben_med_cvg_end_dt
,l_cur_per_rec.coord_ben_med_cvg_end_dt)
,p_uses_tobacco_flag => NVL(l_per_rec.uses_tobacco_flag
,l_cur_per_rec.uses_tobacco_flag)
,p_dpdnt_adoption_date => NVL(l_per_rec.dpdnt_adoption_date
,l_cur_per_rec.dpdnt_adoption_date)
,p_dpdnt_vlntry_svce_flag => NVL(l_per_rec.dpdnt_vlntry_svce_flag
,l_cur_per_rec.dpdnt_vlntry_svce_flag)
,p_original_date_of_hire => NVL(l_per_rec.original_date_of_hire
,l_cur_per_rec.original_date_of_hire)
,p_adjusted_svc_date => p_adjusted_svc_date
,p_town_of_birth => NVL(l_per_rec.town_of_birth
,l_cur_per_rec.town_of_birth)
,p_region_of_birth => NVL(l_per_rec.region_of_birth
,l_cur_per_rec.region_of_birth)
,p_country_of_birth => NVL(l_per_rec.country_of_birth
,l_cur_per_rec.country_of_birth)
,p_global_person_id => NVL(l_per_rec.global_person_id
,l_cur_per_rec.global_person_id)
,p_person_user_key => l_person_user_key
,p_user_person_type => l_user_person_type
,p_language_code => Userenv('lang')
,p_vendor_name => NULL
);
IF csr_ck_add_xsts%FOUND AND p_dp_mode = 'UPDATE' AND
l_add_rec.address_line1 IS NOT NULL THEN
-- Set the p_pradd_ovlapval_override to TURE and create address if address
-- already exists.But if address record has from_date same as effective
-- date then update it
IF Trunc(l_per_rec.START_DATE) =
Trunc(l_cur_add_rec.date_from) THEN
Hrdpp_Update_Person_Address.Insert_Batch_Lines
(p_batch_id => p_batch_id
,p_user_sequence => l_user_sequence
,p_link_value => l_link_value
,p_effective_date => l_per_rec.START_DATE
,p_validate_county => TRUE
,p_primary_flag => NVL(l_add_rec.primary_flag, 'Y')
,p_date_from => l_add_rec.date_from
,p_date_to => l_add_rec.date_to
,p_address_type => l_add_rec.address_type
--,p_comments => p_adr_comments
,p_address_line1 => l_add_rec.address_line1
,p_address_line2 => l_add_rec.address_line2
,p_address_line3 => l_add_rec.address_line3
,p_town_or_city => l_add_rec.town_or_city
,p_region_1 => l_add_rec.region_1
,p_region_2 => l_add_rec.region_2
,p_region_3 => l_add_rec.region_3
,p_postal_code => l_add_rec.postal_code
,p_telephone_number_1 => l_add_rec.telephone_number_1
,p_telephone_number_2 => l_add_rec.telephone_number_2
,p_telephone_number_3 => l_add_rec.telephone_number_3
,p_addr_attribute_category => l_add_rec.addr_attribute_category
,p_addr_attribute1 => l_add_rec.addr_attribute1
,p_addr_attribute2 => l_add_rec.addr_attribute2
,p_addr_attribute3 => l_add_rec.addr_attribute3
,p_addr_attribute4 => l_add_rec.addr_attribute4
,p_addr_attribute5 => l_add_rec.addr_attribute5
,p_addr_attribute6 => l_add_rec.addr_attribute6
,p_addr_attribute7 => l_add_rec.addr_attribute7
,p_addr_attribute8 => l_add_rec.addr_attribute8
,p_addr_attribute9 => l_add_rec.addr_attribute9
,p_addr_attribute10 => l_add_rec.addr_attribute10
,p_addr_attribute11 => l_add_rec.addr_attribute11
,p_addr_attribute12 => l_add_rec.addr_attribute12
,p_addr_attribute13 => l_add_rec.addr_attribute13
,p_addr_attribute14 => l_add_rec.addr_attribute14
,p_addr_attribute15 => l_add_rec.addr_attribute15
,p_addr_attribute16 => l_add_rec.addr_attribute16
,p_addr_attribute17 => l_add_rec.addr_attribute17
,p_addr_attribute18 => l_add_rec.addr_attribute18
,p_addr_attribute19 => l_add_rec.addr_attribute19
,p_addr_attribute20 => l_add_rec.addr_attribute20
,p_add_information13 => l_add_rec.add_information13
,p_add_information14 => l_add_rec.add_information14
,p_add_information15 => l_add_rec.add_information15
,p_add_information16 => l_add_rec.add_information16
,p_add_information17 => l_add_rec.add_information17
,p_add_information18 => l_add_rec.add_information18
,p_add_information19 => l_add_rec.add_information19
,p_add_information20 => l_add_rec.add_information20
,p_party_id => l_add_rec.party_id
,p_address_user_key => l_address_user_key
,p_country => l_add_rec.country
);
Hrdpp_Create_Person_Address.Insert_Batch_Lines
(p_batch_id => p_batch_id
,p_user_sequence => l_user_sequence
,p_link_value => l_link_value
,p_effective_date => l_per_rec.START_DATE
,p_pradd_ovlapval_override => TRUE
,p_validate_county => TRUE
,p_primary_flag => NVL(l_add_rec.primary_flag, 'Y')
,p_style => l_add_rec.style
,p_date_from => l_add_rec.date_from
,p_date_to => l_add_rec.date_to
,p_address_type => l_add_rec.address_type
--,p_comments => p_adr_comments
,p_address_line1 => l_add_rec.address_line1
,p_address_line2 => l_add_rec.address_line2
,p_address_line3 => l_add_rec.address_line3
,p_town_or_city => l_add_rec.town_or_city
,p_region_1 => l_add_rec.region_1
,p_region_2 => l_add_rec.region_2
,p_region_3 => l_add_rec.region_3
,p_postal_code => l_add_rec.postal_code
,p_telephone_number_1 => l_add_rec.telephone_number_1
,p_telephone_number_2 => l_add_rec.telephone_number_2
,p_telephone_number_3 => l_add_rec.telephone_number_3
,p_addr_attribute_category => l_add_rec.addr_attribute_category
,p_addr_attribute1 => l_add_rec.addr_attribute1
,p_addr_attribute2 => l_add_rec.addr_attribute2
,p_addr_attribute3 => l_add_rec.addr_attribute3
,p_addr_attribute4 => l_add_rec.addr_attribute4
,p_addr_attribute5 => l_add_rec.addr_attribute5
,p_addr_attribute6 => l_add_rec.addr_attribute6
,p_addr_attribute7 => l_add_rec.addr_attribute7
,p_addr_attribute8 => l_add_rec.addr_attribute8
,p_addr_attribute9 => l_add_rec.addr_attribute9
,p_addr_attribute10 => l_add_rec.addr_attribute10
,p_addr_attribute11 => l_add_rec.addr_attribute11
,p_addr_attribute12 => l_add_rec.addr_attribute12
,p_addr_attribute13 => l_add_rec.addr_attribute13
,p_addr_attribute14 => l_add_rec.addr_attribute14
,p_addr_attribute15 => l_add_rec.addr_attribute15
,p_addr_attribute16 => l_add_rec.addr_attribute16
,p_addr_attribute17 => l_add_rec.addr_attribute17
,p_addr_attribute18 => l_add_rec.addr_attribute18
,p_addr_attribute19 => l_add_rec.addr_attribute19
,p_addr_attribute20 => l_add_rec.addr_attribute20
,p_add_information13 => l_add_rec.add_information13
,p_add_information14 => l_add_rec.add_information14
,p_add_information15 => l_add_rec.add_information15
,p_add_information16 => l_add_rec.add_information16
,p_add_information17 => l_add_rec.add_information17
,p_add_information18 => l_add_rec.add_information18
,p_add_information19 => l_add_rec.add_information19
,p_add_information20 => l_add_rec.add_information20
,p_party_id => l_add_rec.party_id
,p_address_user_key => l_address_user_key
,p_person_user_key => l_person_user_key
,p_country => l_add_rec.country
);
csr_ck_add_xsts%NOTFOUND AND p_dp_mode = 'UPDATE') OR
(l_add_rec.style IS NOT NULL AND p_dp_mode = 'INSERT')THEN
Hrdpp_Create_Person_Address.Insert_Batch_Lines
(p_batch_id => p_batch_id
,p_user_sequence => l_user_sequence
,p_link_value => l_link_value
,p_effective_date => l_per_rec.START_DATE
,p_pradd_ovlapval_override => FALSE
,p_validate_county => TRUE
,p_primary_flag => NVL(l_add_rec.primary_flag, 'Y')
,p_style => l_add_rec.style
,p_date_from => l_add_rec.date_from
,p_date_to => l_add_rec.date_to
,p_address_type => l_add_rec.address_type
--,p_comments => p_adr_comments
,p_address_line1 => l_add_rec.address_line1
,p_address_line2 => l_add_rec.address_line2
,p_address_line3 => l_add_rec.address_line3
,p_town_or_city => l_add_rec.town_or_city
,p_region_1 => l_add_rec.region_1
,p_region_2 => l_add_rec.region_2
,p_region_3 => l_add_rec.region_3
,p_postal_code => l_add_rec.postal_code
,p_telephone_number_1 => l_add_rec.telephone_number_1
,p_telephone_number_2 => l_add_rec.telephone_number_2
,p_telephone_number_3 => l_add_rec.telephone_number_3
,p_addr_attribute_category => l_add_rec.addr_attribute_category
,p_addr_attribute1 => l_add_rec.addr_attribute1
,p_addr_attribute2 => l_add_rec.addr_attribute2
,p_addr_attribute3 => l_add_rec.addr_attribute3
,p_addr_attribute4 => l_add_rec.addr_attribute4
,p_addr_attribute5 => l_add_rec.addr_attribute5
,p_addr_attribute6 => l_add_rec.addr_attribute6
,p_addr_attribute7 => l_add_rec.addr_attribute7
,p_addr_attribute8 => l_add_rec.addr_attribute8
,p_addr_attribute9 => l_add_rec.addr_attribute9
,p_addr_attribute10 => l_add_rec.addr_attribute10
,p_addr_attribute11 => l_add_rec.addr_attribute11
,p_addr_attribute12 => l_add_rec.addr_attribute12
,p_addr_attribute13 => l_add_rec.addr_attribute13
,p_addr_attribute14 => l_add_rec.addr_attribute14
,p_addr_attribute15 => l_add_rec.addr_attribute15
,p_addr_attribute16 => l_add_rec.addr_attribute16
,p_addr_attribute17 => l_add_rec.addr_attribute17
,p_addr_attribute18 => l_add_rec.addr_attribute18
,p_addr_attribute19 => l_add_rec.addr_attribute19
,p_addr_attribute20 => l_add_rec.addr_attribute20
,p_add_information13 => l_add_rec.add_information13
,p_add_information14 => l_add_rec.add_information14
,p_add_information15 => l_add_rec.add_information15
,p_add_information16 => l_add_rec.add_information16
,p_add_information17 => l_add_rec.add_information17
,p_add_information18 => l_add_rec.add_information18
,p_add_information19 => l_add_rec.add_information19
,p_add_information20 => l_add_rec.add_information20
,p_party_id => l_add_rec.party_id
,p_address_user_key => l_address_user_key
,p_person_user_key => l_person_user_key
,p_country => l_add_rec.country
);
hr_utility.set_location('.Inserted into Hrdpp_Create_Person_Address', 40);
l_datetrack_update_mode := 'CORRECTION';
Hrdpp_Update_Emp_Asg.Insert_Batch_Lines
(p_batch_id => p_batch_id
,p_user_sequence => l_user_sequence
,p_link_value => l_link_value
,p_effective_date => l_per_rec.START_DATE
,p_datetrack_update_mode => l_datetrack_update_mode --'CORRECTION'
,p_change_reason => l_asg_rec.change_reason
--,p_comments => l_asg_rec.asg_comments
,p_date_probation_end => l_asg_rec.date_probation_end
,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_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_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_segment1 => l_hr_soft_rec.segment1
,p_segment2 => l_hr_soft_rec.segment2
,p_segment3 => l_hr_soft_rec.segment3
,p_segment4 => l_hr_soft_rec.segment4
,p_segment5 => l_hr_soft_rec.segment5
,p_segment6 => l_hr_soft_rec.segment6
,p_segment7 => l_hr_soft_rec.segment7
,p_segment8 => l_hr_soft_rec.segment8
,p_segment9 => l_hr_soft_rec.segment9
,p_cagr_grade_def_id => NULL
,p_assignment_user_key => l_assignment_user_key
,p_con_seg_user_name => NULL
);
hr_utility.set_location('..Inserted into Hrdpp_Update_Emp_Asg', 50);
Hrdpp_Update_Emp_Asg_Criteria.Insert_Batch_Lines
(p_batch_id => p_batch_id
,p_user_sequence => l_user_sequence
,p_link_value => l_link_value
,p_effective_date => l_per_rec.START_DATE
,p_datetrack_update_mode => l_datetrack_update_mode --'CORRECTION'
,p_segment1 => l_ppl_grp_rec.segment1
,p_segment2 => l_ppl_grp_rec.segment2
,p_segment3 => l_ppl_grp_rec.segment3
,p_segment4 => l_ppl_grp_rec.segment4
,p_segment5 => l_ppl_grp_rec.segment5
,p_segment6 => l_ppl_grp_rec.segment6
,p_segment7 => l_ppl_grp_rec.segment7
,p_segment8 => l_ppl_grp_rec.segment8
,p_segment9 => l_ppl_grp_rec.segment9
,p_segment10 => l_ppl_grp_rec.segment10
,p_segment11 => l_ppl_grp_rec.segment11
,p_segment12 => l_ppl_grp_rec.segment12
,p_segment13 => l_ppl_grp_rec.segment13
,p_segment14 => l_ppl_grp_rec.segment14
,p_segment15 => l_ppl_grp_rec.segment15
,p_segment16 => l_ppl_grp_rec.segment16
,p_segment17 => l_ppl_grp_rec.segment17
,p_segment18 => l_ppl_grp_rec.segment18
,p_segment19 => l_ppl_grp_rec.segment19
,p_segment20 => l_ppl_grp_rec.segment20
,p_segment21 => l_ppl_grp_rec.segment21
,p_segment22 => l_ppl_grp_rec.segment22
,p_segment23 => l_ppl_grp_rec.segment23
,p_segment24 => l_ppl_grp_rec.segment24
,p_segment25 => l_ppl_grp_rec.segment25
,p_segment26 => l_ppl_grp_rec.segment26
,p_segment27 => l_ppl_grp_rec.segment27
,p_segment28 => l_ppl_grp_rec.segment28
,p_segment29 => l_ppl_grp_rec.segment29
,p_segment30 => l_ppl_grp_rec.segment30
,p_special_ceiling_step_id => NULL
,p_people_group_id => NULL
,p_assignment_user_key => l_assignment_user_key
,p_grade_name => l_grade_name
,p_position_name => l_position_name
,p_job_name => l_job_name
,p_payroll_name => l_payroll_name
,p_location_code => l_location_code
,p_organization_name => l_organization_name
,p_pay_basis_name => l_pay_basis_name
,p_language_code => Userenv('LANG')
,p_con_seg_user_name => NULL
);
hr_utility.set_location('..Inserted into' ||
' Hrdpp_Update_Emp_Asg_Criteria', 130);
IF (csr_get_cont_dtls%NOTFOUND OR p_dp_mode = 'INSERT') AND
l_cntct_rec.contact_type IS NOT NULL THEN
Hrdpp_Create_Contact.Insert_Batch_Lines
(p_batch_id => p_batch_id
,p_user_sequence => l_user_sequence
,p_link_value => l_link_value
,p_start_date => l_per_rec.START_DATE
,p_contact_type => l_cntct_rec.contact_type
,p_primary_contact_flag => l_cntct_rec.primary_contact_flag
,p_personal_flag => l_cntct_rec.personal_flag
,p_last_name => p_contact_name
,p_per_person_user_key => l_contact_key
,p_person_user_key => l_person_user_key
,p_language_code => Userenv('LANG')
);
ELSIF p_dp_mode = 'UPDATE' AND
l_cntct_rec.contact_type IS NOT NULL THEN
hr_pump_utils.add_user_key(p_user_key_value => l_contact_key
,p_unique_key_id => l_cont_rec.contact_person_id
);
Hrdpp_Update_Contact_Relations.Insert_Batch_Lines
(p_batch_id => p_batch_id
,p_user_sequence => l_user_sequence
,p_link_value => l_link_value
,p_effective_date => l_per_rec.START_DATE
,p_contact_type => l_cntct_rec.contact_type
,p_primary_contact_flag => l_cntct_rec.primary_contact_flag
,p_personal_flag => l_cntct_rec.personal_flag
,p_object_version_number => l_cont_rec.object_version_number
,p_contact_user_key => l_contact_key --l_person_user_key
,p_contactee_user_key => l_person_user_key
);
hr_utility.set_location('..Inserted into Hrdpp_Create_Contact', 140);
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
--l_dp_batch_line_id_hire
,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 => l_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 => l_per_rec.employee_number
,p_national_identifier => l_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')
);
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 => l_per_rec.start_date
,p_last_name => l_per_rec.last_name
,p_sex => l_per_rec.sex
--,p_per_comments => l_per_rec.comments
,p_date_employee_data_verified => l_per_rec.date_employee_data_verified
,p_date_of_birth => l_per_rec.date_of_birth
,p_email_address => l_per_rec.email_address
,p_employee_number => l_per_rec.employee_number
,p_expense_check_send_to_addres =>l_per_rec.expense_check_send_to_address
,p_first_name => l_per_rec.first_name
,p_known_as => l_per_rec.known_as
,p_marital_status => l_per_rec.marital_status
,p_middle_names => l_per_rec.middle_names
,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_registered_disabled_flag => l_per_rec.registered_disabled_flag
,p_title => l_per_rec.title
--,p_work_telephone => p_telephone_no1
,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_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
,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_fast_path_employee => l_per_rec.fast_path_employee
,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_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_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_suffix => l_per_rec.suffix
,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_adjusted_svc_date => p_adjusted_svc_date
,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_party_id => l_per_rec.party_id
,p_correspondence_language => l_per_rec.correspondence_language
,p_benefit_group => l_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 => l_per_rec.start_date
,p_datetrack_update_mode => l_dp_upd_per_rec.p_datetrack_update_mode
,p_party_id => l_per_rec.party_id
,p_employee_number => l_per_rec.employee_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_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
-- 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_vendor_id => l_per_rec.vendor_id
--,p_work_telephone => l_per_rec.vendor_id
,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_fast_path_employee IN Varchar2 DEFAULT Hr_Api.g_Varchar2
,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 => 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_adjusted_svc_date => p_adjusted_svc_date
,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_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 => l_per_rec.start_date
,p_validate_county => FALSE
,p_primary_flag => l_add_rec.primary_flag
,p_date_from => l_add_rec.date_from
,p_date_to => l_add_rec.date_to
,p_address_type => l_add_rec.address_type
--,p_comments => p_adr_comments
,p_address_line1 => l_add_rec.address_line1
,p_address_line2 => l_add_rec.address_line2
,p_address_line3 => l_add_rec.address_line3
,p_town_or_city => l_add_rec.town_or_city
,p_region_1 => l_add_rec.region_1
,p_region_2 => l_add_rec.region_2
,p_region_3 => l_add_rec.region_3
,p_postal_code => l_add_rec.postal_code
,p_telephone_number_1 => l_add_rec.telephone_number_1
,p_telephone_number_2 => l_add_rec.telephone_number_2
,p_telephone_number_3 => l_add_rec.telephone_number_3
,p_addr_attribute_category => l_add_rec.addr_attribute_category
,p_addr_attribute1 => l_add_rec.addr_attribute1
,p_addr_attribute2 => l_add_rec.addr_attribute2
,p_addr_attribute3 => l_add_rec.addr_attribute3
,p_addr_attribute4 => l_add_rec.addr_attribute4
,p_addr_attribute5 => l_add_rec.addr_attribute5
,p_addr_attribute6 => l_add_rec.addr_attribute6
,p_addr_attribute7 => l_add_rec.addr_attribute7
,p_addr_attribute8 => l_add_rec.addr_attribute8
,p_addr_attribute9 => l_add_rec.addr_attribute9
,p_addr_attribute10 => l_add_rec.addr_attribute10
,p_addr_attribute11 => l_add_rec.addr_attribute11
,p_addr_attribute12 => l_add_rec.addr_attribute12
,p_addr_attribute13 => l_add_rec.addr_attribute13
,p_addr_attribute14 => l_add_rec.addr_attribute14
,p_addr_attribute15 => l_add_rec.addr_attribute15
,p_addr_attribute16 => l_add_rec.addr_attribute16
,p_addr_attribute17 => l_add_rec.addr_attribute17
,p_addr_attribute18 => l_add_rec.addr_attribute18
,p_addr_attribute19 => l_add_rec.addr_attribute19
,p_addr_attribute20 => l_add_rec.addr_attribute20
,p_add_information13 => l_add_rec.add_information13
,p_add_information14 => l_add_rec.add_information14
,p_add_information15 => l_add_rec.add_information15
,p_add_information16 => l_add_rec.add_information16
,p_add_information17 => l_add_rec.add_information17
,p_add_information18 => l_add_rec.add_information18
,p_add_information19 => l_add_rec.add_information19
,p_add_information20 => l_add_rec.add_information20
,p_party_id => l_add_rec.party_id
,p_address_user_key => l_dp_upd_add_rec.p_address_user_key
,p_country => l_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 => l_per_rec.start_date
,p_pradd_ovlapval_override => l_pradd_ovlapval_override
,p_validate_county => FALSE
,p_primary_flag => l_add_rec.primary_flag
,p_style => l_add_rec.style
,p_date_from => l_add_rec.date_from
,p_date_to => l_add_rec.date_to
,p_address_type => l_add_rec.address_type
--,p_comments => p_adr_comments
,p_address_line1 => l_add_rec.address_line1
,p_address_line2 => l_add_rec.address_line2
,p_address_line3 => l_add_rec.address_line3
,p_town_or_city => l_add_rec.town_or_city
,p_region_1 => l_add_rec.region_1
,p_region_2 => l_add_rec.region_2
,p_region_3 => l_add_rec.region_3
,p_postal_code => l_add_rec.postal_code
,p_telephone_number_1 => l_add_rec.telephone_number_1
,p_telephone_number_2 => l_add_rec.telephone_number_2
,p_telephone_number_3 => l_add_rec.telephone_number_3
,p_addr_attribute_category => l_add_rec.addr_attribute_category
,p_addr_attribute1 => l_add_rec.addr_attribute1
,p_addr_attribute2 => l_add_rec.addr_attribute2
,p_addr_attribute3 => l_add_rec.addr_attribute3
,p_addr_attribute4 => l_add_rec.addr_attribute4
,p_addr_attribute5 => l_add_rec.addr_attribute5
,p_addr_attribute6 => l_add_rec.addr_attribute6
,p_addr_attribute7 => l_add_rec.addr_attribute7
,p_addr_attribute8 => l_add_rec.addr_attribute8
,p_addr_attribute9 => l_add_rec.addr_attribute9
,p_addr_attribute10 => l_add_rec.addr_attribute10
,p_addr_attribute11 => l_add_rec.addr_attribute11
,p_addr_attribute12 => l_add_rec.addr_attribute12
,p_addr_attribute13 => l_add_rec.addr_attribute13
,p_addr_attribute14 => l_add_rec.addr_attribute14
,p_addr_attribute15 => l_add_rec.addr_attribute15
,p_addr_attribute16 => l_add_rec.addr_attribute16
,p_addr_attribute17 => l_add_rec.addr_attribute17
,p_addr_attribute18 => l_add_rec.addr_attribute18
,p_addr_attribute19 => l_add_rec.addr_attribute19
,p_addr_attribute20 => l_add_rec.addr_attribute20
,p_add_information13 => l_add_rec.add_information13
,p_add_information14 => l_add_rec.add_information14
,p_add_information15 => l_add_rec.add_information15
,p_add_information16 => l_add_rec.add_information16
,p_add_information17 => l_add_rec.add_information17
,p_add_information18 => l_add_rec.add_information18
,p_add_information19 => l_add_rec.add_information19
,p_add_information20 => l_add_rec.add_information20
,p_party_id => l_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 => l_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 => l_per_rec.start_date
,p_datetrack_update_mode => l_dp_upd_asg_rec.p_datetrack_update_mode
,p_change_reason => l_asg_rec.change_reason
--,p_comments => l_asg_rec.asg_comments
,p_date_probation_end => l_asg_rec.date_probation_end
,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_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_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_segment1 => l_hr_soft_rec.segment1
,p_segment2 => l_hr_soft_rec.segment2
,p_segment3 => l_hr_soft_rec.segment3
,p_segment4 => l_hr_soft_rec.segment4
,p_segment5 => l_hr_soft_rec.segment5
,p_segment6 => l_hr_soft_rec.segment6
,p_segment7 => l_hr_soft_rec.segment7
,p_segment8 => l_hr_soft_rec.segment8
,p_segment9 => l_hr_soft_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
OPEN csr_grade(l_asg_rec.grade_id
,l_asg_rec.business_group_id
,l_per_rec.START_DATE);
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 => l_per_rec.start_date
,p_datetrack_update_mode => l_dp_upd_asg_crt_rec.p_datetrack_update_mode
,p_segment1 => l_ppl_grp_rec.segment1
,p_segment2 => l_ppl_grp_rec.segment2
,p_segment3 => l_ppl_grp_rec.segment3
,p_segment4 => l_ppl_grp_rec.segment4
,p_segment5 => l_ppl_grp_rec.segment5
,p_segment6 => l_ppl_grp_rec.segment6
,p_segment7 => l_ppl_grp_rec.segment7
,p_segment8 => l_ppl_grp_rec.segment8
,p_segment9 => l_ppl_grp_rec.segment9
,p_segment10 => l_ppl_grp_rec.segment10
,p_segment11 => l_ppl_grp_rec.segment11
,p_segment12 => l_ppl_grp_rec.segment12
,p_segment13 => l_ppl_grp_rec.segment13
,p_segment14 => l_ppl_grp_rec.segment14
,p_segment15 => l_ppl_grp_rec.segment15
,p_segment16 => l_ppl_grp_rec.segment16
,p_segment17 => l_ppl_grp_rec.segment17
,p_segment18 => l_ppl_grp_rec.segment18
,p_segment19 => l_ppl_grp_rec.segment19
,p_segment20 => l_ppl_grp_rec.segment20
,p_segment21 => l_ppl_grp_rec.segment21
,p_segment22 => l_ppl_grp_rec.segment22
,p_segment23 => l_ppl_grp_rec.segment23
,p_segment24 => l_ppl_grp_rec.segment24
,p_segment25 => l_ppl_grp_rec.segment25
,p_segment26 => l_ppl_grp_rec.segment26
,p_segment27 => l_ppl_grp_rec.segment27
,p_segment28 => l_ppl_grp_rec.segment28
,p_segment29 => l_ppl_grp_rec.segment29
,p_segment30 => l_ppl_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 => l_grade_name
,p_position_name => l_position_name
,p_job_name => l_job_name
,p_payroll_name => l_payroll_name
,p_location_code => l_location_code
,p_organization_name => l_organization_name
,p_pay_basis_name => l_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 => l_per_rec.start_date
,p_contact_type => l_cntct_rec.contact_type
,p_primary_contact_flag => l_cntct_rec.primary_contact_flag
,p_personal_flag => l_cntct_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 => l_per_rec.start_date
,p_contact_type => l_cntct_rec.contact_type
,p_primary_contact_flag => l_cntct_rec.primary_contact_flag
,p_personal_flag => l_cntct_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;
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';
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 => l_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_last_name => NVL(l_per_rec.last_name
,l_cur_per_rec.last_name)
,p_first_name => NVL(l_per_rec.first_name
,l_cur_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 => NVL(l_per_rec.marital_status
,l_cur_per_rec.marital_status)
,p_middle_names => NVL(l_per_rec.middle_names
,l_cur_per_rec.middle_names)
,p_sex => NVL(l_per_rec.sex
,l_cur_per_rec.sex)
,p_title => NVL(l_per_rec.title
,l_cur_per_rec.title)
,p_nationality => NVL(l_per_rec.nationality
,l_cur_per_rec.nationality)
,p_previous_last_name => NVL(l_per_rec.previous_last_name
,l_cur_per_rec.previous_last_name)
,p_known_as => NVL(l_per_rec.known_as
,l_cur_per_rec.known_as)
,p_email_address => NVL(l_per_rec.email_address
,l_cur_per_rec.email_address)
,p_registered_disabled_flag => NVL(l_per_rec.registered_disabled_flag
,l_cur_per_rec.registered_disabled_flag)
,p_date_employee_data_verified => NVL(l_per_rec.date_employee_data_verified
,l_cur_per_rec.date_employee_data_verified)
,p_expense_check_send_to_addres => NVL(l_per_rec.expense_check_send_to_address
,l_cur_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 => NVL(l_per_rec.date_of_death
,l_cur_per_rec.date_of_death)
,p_background_check_status => NVL(l_per_rec.background_check_status
,l_cur_per_rec.background_check_status)
,p_background_date_check => NVL(l_per_rec.background_date_check
,l_cur_per_rec.background_date_check)
,p_blood_type => NVL(l_per_rec.blood_type
,l_cur_per_rec.blood_type)
,p_correspondence_language => NVL(l_per_rec.correspondence_language
,l_cur_per_rec.correspondence_language)
,p_fte_capacity => NVL(l_per_rec.fte_capacity
,l_cur_per_rec.fte_capacity)
,p_hold_applicant_date_until => NVL(l_per_rec.hold_applicant_date_until
,l_cur_per_rec.hold_applicant_date_until)
,p_honors => NVL(l_per_rec.honors
,l_cur_per_rec.honors)
,p_internal_location => NVL(l_per_rec.internal_location
,l_cur_per_rec.internal_location)
,p_last_medical_test_by => NVL(l_per_rec.last_medical_test_by
,l_cur_per_rec.last_medical_test_by)
,p_last_medical_test_date => NVL(l_per_rec.last_medical_test_date
,l_cur_per_rec.last_medical_test_date)
,p_mailstop => NVL(l_per_rec.mailstop
,l_cur_per_rec.mailstop)
,p_office_number => NVL(l_per_rec.office_number
,l_cur_per_rec.office_number)
,p_on_military_service => NVL(l_per_rec.on_military_service
,l_cur_per_rec.on_military_service)
,p_pre_name_adjunct => NVL(l_per_rec.pre_name_adjunct
,l_cur_per_rec.pre_name_adjunct)
,p_projected_start_date => NVL(l_per_rec.projected_start_date
,l_cur_per_rec.projected_start_date)
,p_rehire_authorizor => NVL(l_per_rec.rehire_authorizor
,l_cur_per_rec.rehire_authorizor)
,p_rehire_recommendation => NVL(l_per_rec.rehire_recommendation
,l_cur_per_rec.rehire_recommendation)
,p_resume_exists => NVL(l_per_rec.resume_exists
,l_cur_per_rec.resume_exists )
,p_resume_last_updated => NVL(l_per_rec.resume_last_updated
,l_cur_per_rec.resume_last_updated)
,p_second_passport_exists => NVL(l_per_rec.second_passport_exists
,l_cur_per_rec.second_passport_exists)
,p_student_status => NVL(l_per_rec.student_status
,l_cur_per_rec.student_status)
,p_work_schedule => NVL(l_per_rec.work_schedule
,l_cur_per_rec.work_schedule)
,p_rehire_reason => NVL(l_per_rec.rehire_reason
,l_cur_per_rec.rehire_reason)
,p_suffix => NVL(l_per_rec.suffix
,l_cur_per_rec.suffix)
,p_benefit_group_id => NVL(l_per_rec.benefit_group_id
,l_cur_per_rec.benefit_group_id)
,p_receipt_of_death_cert_date => NVL(l_per_rec.receipt_of_death_cert_date
,l_cur_per_rec.receipt_of_death_cert_date)
,p_coord_ben_med_pln_no => NVL(l_per_rec.coord_ben_med_pln_no
,l_cur_per_rec.coord_ben_med_pln_no)
,p_coord_ben_no_cvg_flag => NVL(l_per_rec.coord_ben_no_cvg_flag
,l_cur_per_rec.coord_ben_no_cvg_flag)
,p_coord_ben_med_ext_er => NVL(l_per_rec.coord_ben_med_ext_er
,l_cur_per_rec.coord_ben_med_ext_er)
,p_coord_ben_med_pl_name => NVL(l_per_rec.coord_ben_med_pl_name
,l_cur_per_rec.coord_ben_med_pl_name)
,p_coord_ben_med_insr_crr_name => NVL(l_per_rec.coord_ben_med_insr_crr_name
,l_cur_per_rec.coord_ben_med_insr_crr_name)
,p_coord_ben_med_insr_crr_ident => NVL(l_per_rec.coord_ben_med_insr_crr_ident
,l_cur_per_rec.coord_ben_med_insr_crr_ident)
,p_coord_ben_med_cvg_strt_dt => NVL(l_per_rec.coord_ben_med_cvg_strt_dt
,l_cur_per_rec.coord_ben_med_cvg_strt_dt)
,p_coord_ben_med_cvg_end_dt => NVL(l_per_rec.coord_ben_med_cvg_end_dt
,l_cur_per_rec.coord_ben_med_cvg_end_dt)
,p_uses_tobacco_flag => NVL(l_per_rec.uses_tobacco_flag
,l_cur_per_rec.uses_tobacco_flag)
,p_dpdnt_adoption_date => NVL(l_per_rec.dpdnt_adoption_date
,l_cur_per_rec.dpdnt_adoption_date)
,p_dpdnt_vlntry_svce_flag => NVL(l_per_rec.dpdnt_vlntry_svce_flag
,l_cur_per_rec.dpdnt_vlntry_svce_flag)
,p_original_date_of_hire => NVL(l_per_rec.original_date_of_hire
,l_cur_per_rec.original_date_of_hire)
--,p_adjusted_svc_date => p_adjusted_svc_date
,p_town_of_birth => NVL(l_per_rec.town_of_birth
,l_cur_per_rec.town_of_birth)
,p_region_of_birth => NVL(l_per_rec.region_of_birth
,l_cur_per_rec.region_of_birth)
,p_country_of_birth => NVL(l_per_rec.country_of_birth
,l_cur_per_rec.country_of_birth)
,p_global_person_id => NVL(l_per_rec.global_person_id
,l_cur_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
);
hr_utility.set_location('..Future Update exists for the Student Employee', 60);
,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
) AS
-- Checking for EX-EMP as we should allow a person to be rehired, that implies
-- that NI for EX-Employee may already exist
CURSOR csr_chk_ni_exists (c_ni Varchar2
,c_bgid Number
,c_eff_date Date) IS
SELECT 'Y'
FROM per_people_f ppf, per_person_types ppt
WHERE ppf.national_identifier = c_ni
AND ppf.business_group_id = c_bgid
AND ppf.business_group_id = ppt.business_group_id
AND ppf.person_type_id = ppt.person_type_id
AND ppt.active_flag = 'Y'
AND c_eff_date BETWEEN ppf.effective_start_date
AND ppf.effective_end_date;
SELECT paf.assignment_id
FROM per_people_f ppf, per_assignments_f paf
WHERE paf.person_id = c_dup_per_id
AND paf.person_id = ppf.person_id
AND paf.business_group_id = c_business_group_id
AND paf.business_group_id = ppf.business_group_id
AND c_eff_date BETWEEN paf.effective_start_date
AND paf.effective_end_date
AND c_eff_date BETWEEN ppf.effective_start_date
AND ppf.effective_end_date;
SELECT format_type INTO l_format_type
FROM FND_ID_FLEX_SEGMENTS_VL a, fnd_flex_value_sets b
WHERE id_flex_num = (SELECT people_group_structure
FROM per_business_groups_perf
WHERE business_group_id = p_business_group_id)
AND a.flex_value_set_id = b.flex_value_set_id
AND 'p_people_'||lower(application_column_name) = p_seg_name;
l_dyn_sql_qry := 'SELECT api_person_id ' ||
'FROM igs_pe_person_v ' ||
'WHERE person_number = ''' || l_student_number
|| ''' ' ||
' AND api_person_id LIKE ''%' ||
SUBSTRB(p_national_identifier,
LENGTHB(p_national_identifier) - 3, 4) || '''';
l_dyn_sql_qry := '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;
l_sql_qry := 'SELECT ipe.person_id ' ||
' FROM igs_pe_person_v ipe, hz_party_sites hps ' ||
' WHERE hps.party_id (+) = ipe.person_id ' ||
' AND hps.identifying_address_flag(+) = ''Y'' ' ||
' AND ipe.person_number = ''' ||
l_student_number || '''';
l_per_rec.resume_last_updated := p_resume_last_updated;
, p_dp_mode => 'INSERT');
update_stuempasg_criteria(p_effective_date =>
l_per_rec.start_date ,
p_asg_crit_out => l_asg_crit_out);
hr_utility.set_location('Updated asg criteria', 230);
,p_dp_mode => 'UPDATE');
Update_StuEmpAsg_Criteria(p_effective_date =>
l_per_rec.start_date
,p_asg_crit_out => l_asg_crit_out);
hr_utility.set_location('Updated Asg criteria' , 310);
Update_Person_Contact(p_effective_date=>l_per_rec.start_date
,p_contact_name => p_contact_name
,p_legislation_code => g_leg_code
,p_crt_cntct_out => l_crt_emp_api_out);
,p_dp_mode => 'UPDATE');
Update_Person_Contact(p_effective_date=>l_per_rec.start_date
,p_contact_name => p_contact_name
,p_legislation_code => g_leg_code
,p_crt_cntct_out => l_crt_emp_api_out);
hr_utility.set_location('Updated Contact Details' , 320);
hr_utility.set_location(' Updated Person Primary Address ', 150);
Update_StuEmpAsg_Criteria(p_effective_date =>
l_per_rec.start_date
,p_asg_crit_out => l_asg_crit_out);
hr_utility.set_location('Updated Asg criteria' , 310);
hr_utility.set_location('Updated Extra Info' , 330);
hr_utility.set_location(' Updated Passport, Visa, Visit History' ||
' details: ', 160);
,p_dp_mode => 'UPDATE');
hr_utility.set_location(' Updated person details ', 180);
Update_StuEmpAsg_Criteria(p_effective_date =>
l_per_rec.start_date
,p_asg_crit_out => l_asg_crit_out);
hr_utility.set_location('Updated Asg criteria' , 310);
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)
VALUES
(p_batch_process_id
,p_batch_process_name
,'U'
,l_bg_name);
,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
-- Person DF
,p_per_attribute_category IN Varchar2
,p_per_attribute1 IN Varchar2
,p_per_attribute2 IN Varchar2
,p_per_attribute3 IN Varchar2
,p_per_attribute4 IN Varchar2
,p_per_attribute5 IN Varchar2
,p_per_attribute6 IN Varchar2
,p_per_attribute7 IN Varchar2
,p_per_attribute8 IN Varchar2
,p_per_attribute9 IN Varchar2
,p_per_attribute10 IN Varchar2
,p_per_attribute11 IN Varchar2
,p_per_attribute12 IN Varchar2
,p_per_attribute13 IN Varchar2
,p_per_attribute14 IN Varchar2
,p_per_attribute15 IN Varchar2
,p_per_attribute16 IN Varchar2
,p_per_attribute17 IN Varchar2
,p_per_attribute18 IN Varchar2
,p_per_attribute19 IN Varchar2
,p_per_attribute20 IN Varchar2
,p_per_attribute21 IN Varchar2
,p_per_attribute22 IN Varchar2
,p_per_attribute23 IN Varchar2
,p_per_attribute24 IN Varchar2
,p_per_attribute25 IN Varchar2
,p_per_attribute26 IN Varchar2
,p_per_attribute27 IN Varchar2
,p_per_attribute28 IN Varchar2
,p_per_attribute29 IN Varchar2
,p_per_attribute30 IN Varchar2
-- Person DDF
,p_per_information_category IN Varchar2
,p_per_information1 IN Varchar2
,p_per_information2 IN Varchar2
,p_per_information3 IN Varchar2
,p_per_information4 IN Varchar2
,p_per_information5 IN Varchar2
,p_per_information6 IN Varchar2
,p_per_information7 IN Varchar2
,p_per_information8 IN Varchar2
,p_per_information9 IN Varchar2
,p_per_information10 IN Varchar2
,p_per_information11 IN Varchar2
,p_per_information12 IN Varchar2
,p_per_information13 IN Varchar2
,p_per_information14 IN Varchar2
,p_per_information15 IN Varchar2
,p_per_information16 IN Varchar2
,p_per_information17 IN Varchar2
,p_per_information18 IN Varchar2
,p_per_information19 IN Varchar2
,p_per_information20 IN Varchar2
,p_per_information21 IN Varchar2
,p_per_information22 IN Varchar2
,p_per_information23 IN Varchar2
,p_per_information24 IN Varchar2
,p_per_information25 IN Varchar2
,p_per_information26 IN Varchar2
,p_per_information27 IN Varchar2
,p_per_information28 IN Varchar2
,p_per_information29 IN Varchar2
,p_per_information30 IN Varchar2
-- Primary Address: Per_Addresses
,p_pradd_ovlapval_override IN Varchar2
,p_address_type IN Varchar2
,p_adr_comments IN Varchar2
,p_primary_flag IN Varchar2
,p_address_style IN Varchar2
,p_address_line1 IN Varchar2
,p_address_line2 IN Varchar2
,p_address_line3 IN Varchar2
,p_region1 IN Varchar2
,p_region2 IN Varchar2
,p_region3 IN Varchar2
,p_town_or_city IN Varchar2
,p_country IN Varchar2
,p_postal_code IN Varchar2
,p_telephone_no1 IN Varchar2
,p_telephone_no2 IN Varchar2
,p_telephone_no3 IN Varchar2
,p_address_date_from IN Date
,p_address_date_to IN Date
,p_adr_attribute_category IN Varchar2
,p_adr_attribute1 IN Varchar2
,p_adr_attribute2 IN Varchar2
,p_adr_attribute3 IN Varchar2
,p_adr_attribute4 IN Varchar2
,p_adr_attribute5 IN Varchar2
,p_adr_attribute6 IN Varchar2
,p_adr_attribute7 IN Varchar2
,p_adr_attribute8 IN Varchar2
,p_adr_attribute9 IN Varchar2
,p_adr_attribute10 IN Varchar2
,p_adr_attribute11 IN Varchar2
,p_adr_attribute12 IN Varchar2
,p_adr_attribute13 IN Varchar2
,p_adr_attribute14 IN Varchar2
,p_adr_attribute15 IN Varchar2
,p_adr_attribute16 IN Varchar2
,p_adr_attribute17 IN Varchar2
,p_adr_attribute18 IN Varchar2
,p_adr_attribute19 IN Varchar2
,p_adr_attribute20 IN Varchar2
,p_add_information13 IN Varchar2
,p_add_information14 IN Varchar2
,p_add_information15 IN Varchar2
,p_add_information16 IN Varchar2
,p_add_information17 IN Varchar2
,p_add_information18 IN Varchar2
,p_add_information19 IN Varchar2
,p_add_information20 IN Varchar2
-- Person Phones: Per_Phones
,p_phone_type IN Varchar2
,p_phone_number IN Varchar2
,p_phone_date_from IN Date
,p_phone_date_to IN Date
-- Person Contact: Per_Contact_Relationships
,p_contact_type IN Varchar2
,p_contact_name IN Varchar2
,p_primary_contact IN Varchar2
,p_primary_relationship IN Varchar2
,p_contact_date_from IN Date
,p_contact_date_to IN Date
,p_return_status OUT NOCOPY Varchar2
,p_dup_asg_id IN Number
,p_mode_type IN Varchar2
) AS
l_proc_name CONSTANT Varchar2(150):= g_pkg ||'Create_OSS_Person';
l_per_rec.resume_last_updated := p_resume_last_updated;
hr_utility.set_location(' Updated person details ', 140);
hr_utility.set_location(' Updated Passport, Visa, Visit History' ||
' details: ', 160);
hr_utility.set_location(' Updated person details ', 180);
p_mode_type <> 'UPDATE' THEN
-- Get Passport details from OSS
Pqp_Hrtca_Integration.InsUpd_InHR_PassPort
(p_business_group_id => p_business_group_id
,p_person_id => l_per_rec.person_id
,p_party_id => p_party_id
,p_effective_date => l_effective_date
,p_pp_error_code => l_pp_error_code
,p_passport_warning => l_passport_warning
);
hr_utility.set_location(' Updated Passport, Visa, Visit History' ||
' details: ', 200);
,p_datetrack_update_mode IN Varchar2
,p_assignment_id IN Number
,p_party_id IN Number
,p_business_group_id IN Number
,p_valiDate IN Boolean
,p_called_from_mass_upDate IN Boolean
--
,p_grade_id IN Number
,p_position_id IN Number
,p_job_id IN Number
,p_payroll_id IN Number
,p_location_id IN Number
,p_organization_id IN Number
,p_pay_basis_id IN Number
,p_employment_category IN Varchar2
,p_assignment_category IN Varchar2
--
,p_supervisor_id IN Number
,p_assignment_number IN Varchar2
,p_change_reason IN Varchar2
,p_assignment_status_type_id IN Number
,p_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_title IN Varchar2
,p_notice_period IN Number
,p_notice_period_uom IN Varchar2
,p_employee_category IN Varchar2
,p_work_at_home IN Varchar2
,p_job_post_source_name IN Varchar2
,p_supervisor_assignment_id IN Number
--People Group Keyflex Field
,p_people_group_id IN Number
,p_pgrp_segment1 IN Varchar2
,p_pgrp_segment2 IN Varchar2
,p_pgrp_segment3 IN Varchar2
,p_pgrp_segment4 IN Varchar2
,p_pgrp_segment5 IN Varchar2
,p_pgrp_segment6 IN Varchar2
,p_pgrp_segment7 IN Varchar2
,p_pgrp_segment8 IN Varchar2
,p_pgrp_segment9 IN Varchar2
,p_pgrp_segment10 IN Varchar2
,p_pgrp_segment11 IN Varchar2
,p_pgrp_segment12 IN Varchar2
,p_pgrp_segment13 IN Varchar2
,p_pgrp_segment14 IN Varchar2
,p_pgrp_segment15 IN Varchar2
,p_pgrp_segment16 IN Varchar2
,p_pgrp_segment17 IN Varchar2
,p_pgrp_segment18 IN Varchar2
,p_pgrp_segment19 IN Varchar2
,p_pgrp_segment20 IN Varchar2
,p_pgrp_segment21 IN Varchar2
,p_pgrp_segment22 IN Varchar2
,p_pgrp_segment23 IN Varchar2
,p_pgrp_segment24 IN Varchar2
,p_pgrp_segment25 IN Varchar2
,p_pgrp_segment26 IN Varchar2
,p_pgrp_segment27 IN Varchar2
,p_pgrp_segment28 IN Varchar2
,p_pgrp_segment29 IN Varchar2
,p_pgrp_segment30 IN Varchar2
,p_pgrp_concat_segments IN Varchar2
--Soft Coding KeyflexId
,p_soft_coding_keyflex_id IN Number
,p_soft_concat_segments IN Varchar2
,p_scl_segment1 IN Varchar2
,p_scl_segment2 IN Varchar2
,p_scl_segment3 IN Varchar2
,p_scl_segment4 IN Varchar2
,p_scl_segment5 IN Varchar2
,p_scl_segment6 IN Varchar2
,p_scl_segment7 IN Varchar2
,p_scl_segment8 IN Varchar2
,p_scl_segment9 IN Varchar2
,p_scl_segment10 IN Varchar2
,p_scl_segment11 IN Varchar2
,p_scl_segment12 IN Varchar2
,p_scl_segment13 IN Varchar2
,p_scl_segment14 IN Varchar2
,p_scl_segment15 IN Varchar2
,p_scl_segment16 IN Varchar2
,p_scl_segment17 IN Varchar2
,p_scl_segment18 IN Varchar2
,p_scl_segment19 IN Varchar2
,p_scl_segment20 IN Varchar2
,p_scl_segment21 IN Varchar2
,p_scl_segment22 IN Varchar2
,p_scl_segment23 IN Varchar2
,p_scl_segment24 IN Varchar2
,p_scl_segment25 IN Varchar2
,p_scl_segment26 IN Varchar2
,p_scl_segment27 IN Varchar2
,p_scl_segment28 IN Varchar2
,p_scl_segment29 IN Varchar2
,p_scl_segment30 IN Varchar2
-- Assignment DF Information
,p_ass_attribute_category IN Varchar2
,p_ass_attribute1 IN Varchar2
,p_ass_attribute2 IN Varchar2
,p_ass_attribute3 IN Varchar2
,p_ass_attribute4 IN Varchar2
,p_ass_attribute5 IN Varchar2
,p_ass_attribute6 IN Varchar2
,p_ass_attribute7 IN Varchar2
,p_ass_attribute8 IN Varchar2
,p_ass_attribute9 IN Varchar2
,p_ass_attribute10 IN Varchar2
,p_ass_attribute11 IN Varchar2
,p_ass_attribute12 IN Varchar2
,p_ass_attribute13 IN Varchar2
,p_ass_attribute14 IN Varchar2
,p_ass_attribute15 IN Varchar2
,p_ass_attribute16 IN Varchar2
,p_ass_attribute17 IN Varchar2
,p_ass_attribute18 IN Varchar2
,p_ass_attribute19 IN Varchar2
,p_ass_attribute20 IN Varchar2
,p_ass_attribute21 IN Varchar2
,p_ass_attribute22 IN Varchar2
,p_ass_attribute23 IN Varchar2
,p_ass_attribute24 IN Varchar2
,p_ass_attribute25 IN Varchar2
,p_ass_attribute26 IN Varchar2
,p_ass_attribute27 IN Varchar2
,p_ass_attribute28 IN Varchar2
,p_ass_attribute29 IN Varchar2
,p_ass_attribute30 IN Varchar2
--
,p_grade_ladder_pgm_id IN Number
,p_special_ceiling_step_id IN Number
,p_cagr_grade_def_id IN Number
,p_contract_id IN Number
,p_establishment_id IN Number
,p_collective_agreement_id IN Number
,p_cagr_id_flex_num IN Number
,p_cag_segment1 IN Varchar2
,p_cag_segment2 IN Varchar2
,p_cag_segment3 IN Varchar2
,p_cag_segment4 IN Varchar2
,p_cag_segment5 IN Varchar2
,p_cag_segment6 IN Varchar2
,p_cag_segment7 IN Varchar2
,p_cag_segment8 IN Varchar2
,p_cag_segment9 IN Varchar2
,p_cag_segment10 IN Varchar2
,p_cag_segment11 IN Varchar2
,p_cag_segment12 IN Varchar2
,p_cag_segment13 IN Varchar2
,p_cag_segment14 IN Varchar2
,p_cag_segment15 IN Varchar2
,p_cag_segment16 IN Varchar2
,p_cag_segment17 IN Varchar2
,p_cag_segment18 IN Varchar2
,p_cag_segment19 IN Varchar2
,p_cag_segment20 IN Varchar2
,p_return_status OUT NOCOPY Varchar2
,p_FICA_exempt IN Varchar2
) AS
l_proc_name CONSTANT Varchar2(150):= g_pkg ||'Upd_OSS_Person_Asg';
Update_StuEmpAsg_Criteria
(p_effective_date => p_effective_date
,p_asg_crit_out => l_asg_crit_out
);
SELECT ppt.system_person_type
,ppt.user_person_type
,ppt.person_type_id
FROM per_people_f ppf
,per_person_types ppt
WHERE ppt.person_type_id = ppf.person_type_id
AND c_effective_date BETWEEN ppf.effective_start_date
AND ppf.effective_end_date
AND ppf.person_id = c_person_id
AND ppt.business_group_id = c_business_group_id
AND ppf.business_group_id = ppt.business_group_id;