[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
procedure iud_update_primary(
p_mod_mode varchar2,
p_new_prim_flag varchar2,
p_prim_date_from date,
p_new_end_date date,
p_eot date,
p_pd_os_id number,
p_ass_id number,
p_new_prim_ass_id IN OUT NOCOPY number,
p_prim_change_flag IN OUT NOCOPY varchar2) is
--
l_fin_proc_date date;
l_proc varchar2(18) := 'iud_update_primary';
SELECT paaf.person_id
,paaf.assignment_type
,paaf.period_of_placement_date_start
,paaf.object_version_number -- added for bug 9644377
FROM per_all_assignments_f paaf
WHERE paaf.assignment_id = p_ass_id
AND paaf.assignment_type IN ('E', 'C');
SELECT paaf.person_id
,paaf.assignment_type
,paaf.period_of_placement_date_start
,paaf.object_version_number -- added for bug 9644377
FROM per_all_assignments_f paaf
WHERE paaf.assignment_id = p_new_prim_ass_id
AND paaf.assignment_type IN ('E', 'C');
SELECT NVL(final_process_date, p_eot)
FROM per_periods_of_service
WHERE period_of_service_id = p_pd_os_id
UNION
SELECT NVL(pdp.final_process_date, p_eot)
FROM per_periods_of_placement pdp
WHERE pdp.person_id = l_person_id
AND pdp.date_start = l_pdp_date_start;
if p_mod_mode in ('UPDATE','CORRECTION') then
hr_assignment_api.set_new_primary_asg(
p_effective_date => p_prim_date_from
,p_person_id => l_person_id
,p_assignment_id => p_new_prim_ass_id
,p_object_version_number => l_object_version_number
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
);
hr_assignment.update_primary(
p_ass_id,
p_pd_os_id,
p_new_prim_ass_id,
p_prim_date_from,
p_new_prim_flag,
p_mod_mode,
0,
0);
if p_mod_mode IN ('UPDATE','CORRECTION') then
hr_assignment_api.set_new_primary_cwk_asg(
p_effective_date => p_prim_date_from
,p_person_id => l_person_id
,p_assignment_id => p_new_prim_ass_id
,p_object_version_number => l_object_version_number
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
);
hr_assignment.update_primary_cwk(
p_ass_id,
l_person_id,
l_pdp_date_start,
p_new_prim_ass_id,
p_prim_date_from,
p_new_prim_flag,
p_mod_mode,
0,
0);
end iud_update_primary;
procedure update_group(
p_pg_id number,
p_group_name varchar2,
p_bg_id number) is
--
-- Called post-insert/update.
-- Start of fix 2762904
cursor c_flex is
SELECT bg.people_group_structure flex_num
FROM PER_BUSINESS_GROUPS BG
WHERE BG.BUSINESS_GROUP_ID= p_bg_id;
select rownum, format_type
from fnd_id_flex_segments_vl f, fnd_flex_value_sets v
where f.flex_value_set_id = v.flex_value_set_id(+)
and id_flex_code ='GRP'
and f.application_id = 801 -- bug fix 3648612.
and id_flex_num = p_flexnum
and display_flag='Y'
and enabled_flag='Y'
order by segment_num;
l_proc varchar2(12) := 'update_group';
update pay_people_groups
set group_name = l_xname
where people_group_id = P_PG_ID
and (group_name <> p_group_name
or group_name is null)
and l_xname is not null; -- 4103321
'PER_ASSIGNMENTS_F1_PKG.UPDATE_GROUP');
end update_group;
procedure update_scl(
p_scl_id number,
p_scl_concat varchar2) is
--
--
CURSOR csr_chk_scl is
SELECT null
FROM hr_soft_coding_keyflex
where soft_coding_keyflex_id = p_scl_id
and (concatenated_segments <> p_scl_concat
or concatenated_segments is null);
l_proc varchar2(72) := g_package||'update_scl ';
procedure update_scl_auto
( p_scl_id number,
p_scl_concat varchar2
) is
PRAGMA AUTONOMOUS_TRANSACTION;
SELECT null
FROM hr_soft_coding_keyflex
where soft_coding_keyflex_id = p_scl_id
for update nowait;
l_proc varchar2(72) := g_package||'update_scl_auto ';
update hr_soft_coding_keyflex
set concatenated_segments = p_scl_concat
where soft_coding_keyflex_id = p_scl_id
and (concatenated_segments <> p_scl_concat
or concatenated_segments is null);
end update_scl_auto;
update_scl_auto
(p_scl_id => p_scl_id
,p_scl_concat => p_scl_concat
);
update hr_soft_coding_keyflex
set concatenated_segments = p_scl_concat
where soft_coding_keyflex_id = p_scl_id
and (concatenated_segments <> p_scl_concat
or concatenated_segments is null);
end update_scl;
/* procedure update_scl(
p_scl_id number,
p_scl_concat varchar2) is
--
-- Called post-insert/update.
--
begin
if p_scl_id <> -1 then
--
-- This is an existing desc flex record, update concatenated_segments
-- field held on hr_soft_coding_keyflex table.
--
update hr_soft_coding_keyflex
set concatenated_segments = p_scl_concat
where soft_coding_keyflex_id = p_scl_id;
'PER_ASSIGNMENTS_F1_PKG.UPDATE_SCL');
end update_scl; */
hr_assignment.del_ref_int_delete(
p_ass_id,
p_grd_id,
'FUTURE',
p_sess_date,
0, 0,
p_calling_proc,
p_val_st_date,
p_val_end_date,
p_datetrack_mode,
l_future_spp_warning);
update per_assignments_f a
set a.effective_end_date = P_NEW_END_DATE
where a.assignment_id = P_ASS_ID
and a.effective_end_date = (
select max(a2.effective_end_date)
from per_assignments_f a2
where a2.assignment_id = a.assignment_id);
l_mode := 'INSERT';
l_mode := 'DELETE';
if l_mode = 'DELETE' then
l_val_start_date := p_sess_date;
if l_mode = 'DELETE' then
l_val_start_date := l_val_start_date + 1;
procedure post_update(
p_upd_mode varchar2,
p_new_prim_flag varchar2,
p_val_st_date date,
p_new_end_date date,
p_eot date,
p_pd_os_id number,
p_ass_id number,
p_new_prim_ass_id IN OUT NOCOPY number,
p_prim_change_flag IN OUT NOCOPY varchar2,
p_old_pg_id number, -- Bug#3924690
p_new_pg_id number,
p_old_org_id number, -- Added for Bug#13960540
p_new_org_id number, -- Added for Bug#13960540
p_old_emp_cat varchar2, -- Added for Bug#13960540
p_new_emp_cat varchar2, -- Added for Bug#13960540
p_grd_id number,
p_sess_date date,
p_s_grd_id number,
p_eff_end_date date,
p_per_sys_st varchar2,
p_old_per_sys_st varchar2, --#2404335
p_val_end_date date,
p_del_mode varchar2,
p_bg_id number,
p_old_pay_id number,
p_new_pay_id number,
p_group_name varchar2,
p_was_end_assign varchar2,
p_cancel_atd date,
p_cancel_lspd date,
p_reterm_atd date,
p_reterm_lspd date,
p_scl_id number,
p_scl_concat varchar2,
p_end_salary varchar2 ,
p_warning IN OUT NOCOPY varchar2,
p_re_entry_point IN OUT NOCOPY number,
p_future_spp_warning OUT NOCOPY boolean) is
--
-- Define local variables
--
l_per_sys_st varchar2(30);
l_proc VARCHAR2(72) := g_package||'post_update';
select element_entry_id
from pay_element_entries_f
where assignment_id = p_ass_id
and creator_type = 'SP'
and p_val_st_date between
effective_start_date and effective_end_date;
select min(effective_start_date)
from per_spinal_point_placements_f
where assignment_id = p_ass_id;
select spp.placement_id, spp.object_version_number ,step_id,
spp.effective_end_date,spp.effective_start_date
from per_spinal_point_placements_f spp
where spp.assignment_id = p_ass_id
and p_val_st_date between spp.effective_start_date
and spp.effective_end_date;
SELECT spp.placement_id , spp.object_version_number,spp.effective_start_date
FROM per_spinal_point_placements_f spp
WHERE assignment_id = p_ass_id
and p_sess_date between spp.effective_start_date
and spp.effective_end_date;
select assignment_id
,effective_start_date
,effective_end_date
,business_group_id
,recruiter_id
,grade_id
,position_id
,job_id
,assignment_status_type_id
,payroll_id
,location_id
,person_referred_by_id
,supervisor_id
,special_ceiling_step_id
,person_id
,recruitment_activity_id
,source_organization_id
,organization_id
,people_group_id
,soft_coding_keyflex_id
,vacancy_id
,pay_basis_id
,assignment_sequence
,assignment_type
,primary_flag
,application_id
,assignment_number
,change_reason
,comment_id
,null
,date_probation_end
,default_code_comb_id
,employment_category
,frequency
,internal_address_line
,manager_flag
,normal_hours
,perf_review_period
,perf_review_period_frequency
,period_of_service_id
,probation_period
,probation_unit
,sal_review_period
,sal_review_period_frequency
,set_of_books_id
,source_type
,time_normal_finish
,time_normal_start
,bargaining_unit_code
,labour_union_member_flag
,hourly_salaried_code
,request_id
,program_application_id
,program_id
,program_update_date
,ass_attribute_category
,ass_attribute1
,ass_attribute2
,ass_attribute3
,ass_attribute4
,ass_attribute5
,ass_attribute6
,ass_attribute7
,ass_attribute8
,ass_attribute9
,ass_attribute10
,ass_attribute11
,ass_attribute12
,ass_attribute13
,ass_attribute14
,ass_attribute15
,ass_attribute16
,ass_attribute17
,ass_attribute18
,ass_attribute19
,ass_attribute20
,ass_attribute21
,ass_attribute22
,ass_attribute23
,ass_attribute24
,ass_attribute25
,ass_attribute26
,ass_attribute27
,ass_attribute28
,ass_attribute29
,ass_attribute30
,title
,object_version_number
,contract_id
,establishment_id
,collective_agreement_id
,cagr_grade_def_id
,cagr_id_flex_num
,notice_period
,notice_period_uom
,employee_category
,work_at_home
,job_post_source_name
,posting_content_id
,period_of_placement_date_start
,vendor_id
,vendor_employee_number
,vendor_assignment_number
,assignment_category
,project_title
,applicant_rank
,grade_ladder_pgm_id
,supervisor_assignment_id
,vendor_site_id
,po_header_id
,po_line_id
,projected_assignment_end
from per_all_assignments_f
where assignment_id = p_asg_id
and p_eff_date between effective_start_date
and effective_end_date;
procedure delete_any_pay_proposals(p_ass_id number,
p_ass_end_date date) is
--
-- Private proc to delete any pay proposals which have
-- a change date after the validation start date of the
-- current assignment. It is used for assignments
-- which have just been ended or terminated.
--
begin
--
delete from per_pay_proposals p
where p.assignment_id = P_ASS_ID
and p.change_date > P_ASS_END_DATE;
end delete_any_pay_proposals;
hr_utility.set_location(l_proc||' Update Mode: '||p_upd_mode,11);
hr_utility.set_location('peasg01t.pkb.post_update..p_grd_id:' || p_grd_id,11); -- Bug#13960540
hr_utility.set_location('peasg01t.pkb.post_update..p_s_grd_id:' || p_s_grd_id,11);
hr_utility.set_location('peasg01t.pkb.post_update..p_old_org_id:' || p_old_org_id,11);
hr_utility.set_location('peasg01t.pkb.post_update..p_new_org_id:' || p_new_org_id,11);
hr_utility.set_location('peasg01t.pkb.post_update..p_old_emp_cat:' || p_old_emp_cat,11);
hr_utility.set_location('peasg01t.pkb.post_update..p_new_emp_cat:' || p_new_emp_cat,11); -- Bug#13960540
iud_update_primary( p_upd_mode,
p_new_prim_flag,
p_val_st_date,
p_new_end_date,
p_eot,
p_pd_os_id,
p_ass_id,
p_new_prim_ass_id,
p_prim_change_flag);
update_group( p_new_pg_id,
p_group_name,
p_bg_id);
update_scl(
p_scl_id,
p_scl_concat);
if p_upd_mode = 'UPDATE_OVERRIDE' then
--
hr_utility.set_location(l_proc,50);
,p_spp_delete_warning => l_future_spp_warnings);
l_calling_proc := 'POST_UPDATE';
hr_sp_placement_api.delete_spp
(p_effective_date => P_sess_date
,p_datetrack_mode => 'DELETE'
,p_placement_id => l_placement_id
,p_object_version_number => l_object_version_number
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date);
select max(effective_end_date)
into l_max_spp_date
from per_spinal_point_placements_f
where placement_id = l_placement_id;
l_datetrack_mode := 'UPDATE';
l_datetrack_mode := 'UPDATE_CHANGE_INSERT';
hr_utility.set_location('Calling upadate_spp from post_update ',2);
hr_sp_placement_api.update_spp
(p_effective_date => p_val_st_date
,p_datetrack_mode => l_datetrack_mode
,p_placement_id => l_placement_id
,p_object_version_number => l_object_version_number
,p_step_id => l_step_id
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date);
hr_utility.set_location('Call to update_SPP finished ',2);
,p_spp_delete_warning => l_future_spp_warnings);
l_calling_proc := 'POST_UPDATE';
hr_assignment.del_ref_int_delete
(p_ass_id,
null,
'END',
p_sess_date,
0,
0,
l_calling_proc,
p_val_st_date,
p_val_end_date,
p_upd_mode,
l_future_spp_warnings);
delete_any_pay_proposals
(p_ass_id,
p_val_st_date);
delete_any_pay_proposals(p_ass_id,
p_val_st_date);
pay_pog_all_assignments_pkg.after_update
(p_effective_date => p_sess_date
,p_datetrack_mode => p_upd_mode
,p_validation_start_date => p_val_st_date
,p_validation_end_date => p_val_end_date
,P_APPLICANT_RANK => cur_asg_rec.applicant_rank
,P_APPLICATION_ID => cur_asg_rec.application_id
,P_ASSIGNMENT_CATEGORY => cur_asg_rec.assignment_category
,P_ASSIGNMENT_ID => cur_asg_rec.assignment_id
,P_ASSIGNMENT_NUMBER => cur_asg_rec.assignment_number
,P_ASSIGNMENT_STATUS_TYPE_ID => cur_asg_rec.assignment_status_type_id
,P_ASSIGNMENT_TYPE => cur_asg_rec.assignment_type
,P_ASS_ATTRIBUTE1 => cur_asg_rec.ass_attribute1
,P_ASS_ATTRIBUTE10 => cur_asg_rec.ass_attribute10
,P_ASS_ATTRIBUTE11 => cur_asg_rec.ass_attribute11
,P_ASS_ATTRIBUTE12 => cur_asg_rec.ass_attribute12
,P_ASS_ATTRIBUTE13 => cur_asg_rec.ass_attribute13
,P_ASS_ATTRIBUTE14 => cur_asg_rec.ass_attribute14
,P_ASS_ATTRIBUTE15 => cur_asg_rec.ass_attribute15
,P_ASS_ATTRIBUTE16 => cur_asg_rec.ass_attribute16
,P_ASS_ATTRIBUTE17 => cur_asg_rec.ass_attribute17
,P_ASS_ATTRIBUTE18 => cur_asg_rec.ass_attribute18
,P_ASS_ATTRIBUTE19 => cur_asg_rec.ass_attribute19
,P_ASS_ATTRIBUTE2 => cur_asg_rec.ass_attribute2
,P_ASS_ATTRIBUTE20 => cur_asg_rec.ass_attribute20
,P_ASS_ATTRIBUTE21 => cur_asg_rec.ass_attribute21
,P_ASS_ATTRIBUTE22 => cur_asg_rec.ass_attribute22
,P_ASS_ATTRIBUTE23 => cur_asg_rec.ass_attribute23
,P_ASS_ATTRIBUTE24 => cur_asg_rec.ass_attribute24
,P_ASS_ATTRIBUTE25 => cur_asg_rec.ass_attribute25
,P_ASS_ATTRIBUTE26 => cur_asg_rec.ass_attribute26
,P_ASS_ATTRIBUTE27 => cur_asg_rec.ass_attribute27
,P_ASS_ATTRIBUTE28 => cur_asg_rec.ass_attribute28
,P_ASS_ATTRIBUTE29 => cur_asg_rec.ass_attribute29
,P_ASS_ATTRIBUTE3 => cur_asg_rec.ass_attribute3
,P_ASS_ATTRIBUTE30 => cur_asg_rec.ass_attribute30
,P_ASS_ATTRIBUTE4 => cur_asg_rec.ass_attribute4
,P_ASS_ATTRIBUTE5 => cur_asg_rec.ass_attribute5
,P_ASS_ATTRIBUTE6 => cur_asg_rec.ass_attribute6
,P_ASS_ATTRIBUTE7 => cur_asg_rec.ass_attribute7
,P_ASS_ATTRIBUTE8 => cur_asg_rec.ass_attribute8
,P_ASS_ATTRIBUTE9 => cur_asg_rec.ass_attribute9
,P_ASS_ATTRIBUTE_CATEGORY => cur_asg_rec.ass_attribute_category
,P_BARGAINING_UNIT_CODE => cur_asg_rec.bargaining_unit_code
,P_CAGR_GRADE_DEF_ID => cur_asg_rec.cagr_grade_def_id
,P_CAGR_ID_FLEX_NUM => cur_asg_rec.cagr_id_flex_num
,P_CHANGE_REASON => cur_asg_rec.change_reason
,P_COLLECTIVE_AGREEMENT_ID => cur_asg_rec.collective_agreement_id
,P_COMMENTS => cur_asg_rec.comment_text
,P_COMMENT_ID => cur_asg_rec.comment_id
,P_CONTRACT_ID => cur_asg_rec.contract_id
,P_DATE_PROBATION_END => cur_asg_rec.date_probation_end
,P_DEFAULT_CODE_COMB_ID => cur_asg_rec.default_code_comb_id
,P_EFFECTIVE_END_DATE => cur_asg_rec.effective_end_date
,P_EFFECTIVE_START_DATE => cur_asg_rec.effective_start_date
,P_EMPLOYEE_CATEGORY => cur_asg_rec.employee_category
,P_EMPLOYMENT_CATEGORY => cur_asg_rec.employment_category
,P_ESTABLISHMENT_ID => cur_asg_rec.establishment_id
,P_FREQUENCY => cur_asg_rec.frequency
,P_GRADE_ID => cur_asg_rec.grade_id
,P_HOURLY_SALARIED_CODE => cur_asg_rec.hourly_salaried_code
,P_HOURLY_SALARIED_WARNING => null
,P_INTERNAL_ADDRESS_LINE => cur_asg_rec.internal_address_line
,P_JOB_ID => cur_asg_rec.job_id
,P_JOB_POST_SOURCE_NAME => cur_asg_rec.job_post_source_name
,P_LABOUR_UNION_MEMBER_FLAG => cur_asg_rec.labour_union_member_flag
,P_LOCATION_ID => cur_asg_rec.location_id
,P_MANAGER_FLAG => cur_asg_rec.manager_flag
,P_NORMAL_HOURS => cur_asg_rec.normal_hours
,P_NOTICE_PERIOD => cur_asg_rec.notice_period
,P_NOTICE_PERIOD_UOM => cur_asg_rec.notice_period_uom
,P_NO_MANAGERS_WARNING => null
,P_OBJECT_VERSION_NUMBER => cur_asg_rec.object_version_number
,P_ORGANIZATION_ID => cur_asg_rec.organization_id
,P_ORG_NOW_NO_MANAGER_WARNING => null
,P_OTHER_MANAGER_WARNING => null
,P_PAYROLL_ID => cur_asg_rec.payroll_id
,P_PAYROLL_ID_UPDATED => null
,P_PAY_BASIS_ID => cur_asg_rec.pay_basis_id
,P_PEOPLE_GROUP_ID => cur_asg_rec.people_group_id
,P_PERF_REVIEW_PERIOD => cur_asg_rec.perf_review_period
,P_PERF_REVIEW_PERIOD_FREQUEN => cur_asg_rec.perf_review_period_frequency
,P_PERIOD_OF_SERVICE_ID => cur_asg_rec.period_of_service_id
,P_PERSON_REFERRED_BY_ID => cur_asg_rec.person_referred_by_id
,P_PLACEMENT_DATE_START => cur_asg_rec.period_of_placement_date_start
,P_POSITION_ID => cur_asg_rec.position_id
,P_POSTING_CONTENT_ID => cur_asg_rec.posting_content_id
,P_PRIMARY_FLAG => cur_asg_rec.primary_flag
,P_PROBATION_PERIOD => cur_asg_rec.probation_period
,P_PROBATION_UNIT => cur_asg_rec.probation_unit
,P_PROGRAM_APPLICATION_ID => cur_asg_rec.program_application_id
,P_PROGRAM_ID => cur_asg_rec.program_id
,P_PROGRAM_UPDATE_DATE => cur_asg_rec.program_update_date
,P_PROJECT_TITLE => cur_asg_rec.project_title
,P_RECRUITER_ID => cur_asg_rec.recruiter_id
,P_RECRUITMENT_ACTIVITY_ID => cur_asg_rec.recruitment_activity_id
,P_REQUEST_ID => cur_asg_rec.request_id
,P_SAL_REVIEW_PERIOD => cur_asg_rec.sal_review_period
,P_SAL_REVIEW_PERIOD_FREQUEN => cur_asg_rec.sal_review_period_frequency
,P_SET_OF_BOOKS_ID => cur_asg_rec.set_of_books_id
,P_SOFT_CODING_KEYFLEX_ID => cur_asg_rec.soft_coding_keyflex_id
,P_SOURCE_ORGANIZATION_ID => cur_asg_rec.source_organization_id
,P_SOURCE_TYPE => cur_asg_rec.source_type
,P_SPECIAL_CEILING_STEP_ID => cur_asg_rec.special_ceiling_step_id
,P_SUPERVISOR_ID => cur_asg_rec.supervisor_id
,P_TIME_NORMAL_FINISH => cur_asg_rec.time_normal_finish
,P_TIME_NORMAL_START => cur_asg_rec.time_normal_start
,P_TITLE => cur_asg_rec.title
,P_VACANCY_ID => cur_asg_rec.vacancy_id
,P_VENDOR_ASSIGNMENT_NUMBER => cur_asg_rec.vendor_assignment_number
,P_VENDOR_EMPLOYEE_NUMBER => cur_asg_rec.vendor_employee_number
,P_VENDOR_ID => cur_asg_rec.vendor_id
,P_WORK_AT_HOME => cur_asg_rec.work_at_home
,P_GRADE_LADDER_PGM_ID => cur_asg_rec.grade_ladder_pgm_id
,P_SUPERVISOR_ASSIGNMENT_ID => cur_asg_rec.supervisor_assignment_id
,P_VENDOR_SITE_ID => cur_asg_rec.vendor_site_id
,P_PO_HEADER_ID => cur_asg_rec.po_header_id
,P_PO_LINE_ID => cur_asg_rec.po_line_id
,P_PROJECTED_ASSIGNMENT_END => cur_asg_rec.projected_assignment_end
,P_APPLICANT_RANK_O
=> per_assignments_f2_pkg.g_old_asg_rec.applicant_rank
,P_APPLICATION_ID_O
=> per_assignments_f2_pkg.g_old_asg_rec.application_id
,P_ASSIGNMENT_CATEGORY_O
=> per_assignments_f2_pkg.g_old_asg_rec.assignment_category
,P_ASSIGNMENT_NUMBER_O
=> per_assignments_f2_pkg.g_old_asg_rec.assignment_number
,P_ASSIGNMENT_SEQUENCE_O
=> per_assignments_f2_pkg.g_old_asg_rec.assignment_sequence
,P_ASSIGNMENT_STATUS_TYPE_ID_O
=> per_assignments_f2_pkg.g_old_asg_rec.assignment_status_type_id
,P_ASSIGNMENT_TYPE_O
=> per_assignments_f2_pkg.g_old_asg_rec.assignment_type
,P_ASS_ATTRIBUTE1_O
=> per_assignments_f2_pkg.g_old_asg_rec.ass_attribute1
,P_ASS_ATTRIBUTE10_O
=> per_assignments_f2_pkg.g_old_asg_rec.ass_attribute10
,P_ASS_ATTRIBUTE11_O
=> per_assignments_f2_pkg.g_old_asg_rec.ass_attribute11
,P_ASS_ATTRIBUTE12_O
=> per_assignments_f2_pkg.g_old_asg_rec.ass_attribute12
,P_ASS_ATTRIBUTE13_O
=> per_assignments_f2_pkg.g_old_asg_rec.ass_attribute13
,P_ASS_ATTRIBUTE14_O
=> per_assignments_f2_pkg.g_old_asg_rec.ass_attribute14
,P_ASS_ATTRIBUTE15_O
=> per_assignments_f2_pkg.g_old_asg_rec.ass_attribute15
,P_ASS_ATTRIBUTE16_O
=> per_assignments_f2_pkg.g_old_asg_rec.ass_attribute16
,P_ASS_ATTRIBUTE17_O
=> per_assignments_f2_pkg.g_old_asg_rec.ass_attribute17
,P_ASS_ATTRIBUTE18_O
=> per_assignments_f2_pkg.g_old_asg_rec.ass_attribute18
,P_ASS_ATTRIBUTE19_O
=> per_assignments_f2_pkg.g_old_asg_rec.ass_attribute19
,P_ASS_ATTRIBUTE2_O
=> per_assignments_f2_pkg.g_old_asg_rec.ass_attribute2
,P_ASS_ATTRIBUTE20_O
=> per_assignments_f2_pkg.g_old_asg_rec.ass_attribute20
,P_ASS_ATTRIBUTE21_O
=> per_assignments_f2_pkg.g_old_asg_rec.ass_attribute21
,P_ASS_ATTRIBUTE22_O
=> per_assignments_f2_pkg.g_old_asg_rec.ass_attribute22
,P_ASS_ATTRIBUTE23_O
=> per_assignments_f2_pkg.g_old_asg_rec.ass_attribute23
,P_ASS_ATTRIBUTE24_O
=> per_assignments_f2_pkg.g_old_asg_rec.ass_attribute24
,P_ASS_ATTRIBUTE25_O
=> per_assignments_f2_pkg.g_old_asg_rec.ass_attribute25
,P_ASS_ATTRIBUTE26_O
=> per_assignments_f2_pkg.g_old_asg_rec.ass_attribute26
,P_ASS_ATTRIBUTE27_O
=> per_assignments_f2_pkg.g_old_asg_rec.ass_attribute27
,P_ASS_ATTRIBUTE28_O
=> per_assignments_f2_pkg.g_old_asg_rec.ass_attribute28
,P_ASS_ATTRIBUTE29_O
=> per_assignments_f2_pkg.g_old_asg_rec.ass_attribute29
,P_ASS_ATTRIBUTE3_O
=> per_assignments_f2_pkg.g_old_asg_rec.ass_attribute3
,P_ASS_ATTRIBUTE30_O
=> per_assignments_f2_pkg.g_old_asg_rec.ass_attribute30
,P_ASS_ATTRIBUTE4_O
=> per_assignments_f2_pkg.g_old_asg_rec.ass_attribute4
,P_ASS_ATTRIBUTE5_O
=> per_assignments_f2_pkg.g_old_asg_rec.ass_attribute5
,P_ASS_ATTRIBUTE6_O
=> per_assignments_f2_pkg.g_old_asg_rec.ass_attribute6
,P_ASS_ATTRIBUTE7_O
=> per_assignments_f2_pkg.g_old_asg_rec.ass_attribute7
,P_ASS_ATTRIBUTE8_O
=> per_assignments_f2_pkg.g_old_asg_rec.ass_attribute8
,P_ASS_ATTRIBUTE9_O
=> per_assignments_f2_pkg.g_old_asg_rec.ass_attribute9
,P_ASS_ATTRIBUTE_CATEGORY_O
=> per_assignments_f2_pkg.g_old_asg_rec.ass_attribute_category
,P_BARGAINING_UNIT_CODE_O
=> per_assignments_f2_pkg.g_old_asg_rec.bargaining_unit_code
,P_BUSINESS_GROUP_ID_O
=> per_assignments_f2_pkg.g_old_asg_rec.business_group_id
,P_CAGR_GRADE_DEF_ID_O
=> per_assignments_f2_pkg.g_old_asg_rec.cagr_grade_def_id
,P_CAGR_ID_FLEX_NUM_O
=> per_assignments_f2_pkg.g_old_asg_rec.cagr_id_flex_num
,P_CHANGE_REASON_O
=> per_assignments_f2_pkg.g_old_asg_rec.change_reason
,P_COLLECTIVE_AGREEMENT_ID_O
=> per_assignments_f2_pkg.g_old_asg_rec.collective_agreement_id
,P_COMMENT_ID_O
=> per_assignments_f2_pkg.g_old_asg_rec.comment_id
,P_CONTRACT_ID_O
=> per_assignments_f2_pkg.g_old_asg_rec.contract_id
,P_DATE_PROBATION_END_O
=> per_assignments_f2_pkg.g_old_asg_rec.date_probation_end
,P_DEFAULT_CODE_COMB_ID_O
=> per_assignments_f2_pkg.g_old_asg_rec.default_code_comb_id
,P_EFFECTIVE_END_DATE_O
=> per_assignments_f2_pkg.g_old_asg_rec.effective_end_date
,P_EFFECTIVE_START_DATE_O
=> per_assignments_f2_pkg.g_old_asg_rec.effective_start_date
,P_EMPLOYEE_CATEGORY_O
=> per_assignments_f2_pkg.g_old_asg_rec.employee_category
,P_EMPLOYMENT_CATEGORY_O
=> per_assignments_f2_pkg.g_old_asg_rec.employment_category
,P_ESTABLISHMENT_ID_O
=> per_assignments_f2_pkg.g_old_asg_rec.establishment_id
,P_FREQUENCY_O
=> per_assignments_f2_pkg.g_old_asg_rec.frequency
,P_GRADE_ID_O
=> per_assignments_f2_pkg.g_old_asg_rec.grade_id
,P_HOURLY_SALARIED_CODE_O
=> per_assignments_f2_pkg.g_old_asg_rec.hourly_salaried_code
,P_INTERNAL_ADDRESS_LINE_O
=> per_assignments_f2_pkg.g_old_asg_rec.internal_address_line
,P_JOB_ID_O
=> per_assignments_f2_pkg.g_old_asg_rec.job_id
,P_JOB_POST_SOURCE_NAME_O
=> per_assignments_f2_pkg.g_old_asg_rec.job_post_source_name
,P_LABOUR_UNION_MEMBER_FLAG_O
=> per_assignments_f2_pkg.g_old_asg_rec.labour_union_member_flag
,P_LOCATION_ID_O
=> per_assignments_f2_pkg.g_old_asg_rec.location_id
,P_MANAGER_FLAG_O
=> per_assignments_f2_pkg.g_old_asg_rec.manager_flag
,P_NORMAL_HOURS_O
=> per_assignments_f2_pkg.g_old_asg_rec.normal_hours
,P_NOTICE_PERIOD_O
=> per_assignments_f2_pkg.g_old_asg_rec.notice_period
,P_NOTICE_PERIOD_UOM_O
=> per_assignments_f2_pkg.g_old_asg_rec.notice_period_uom
,P_OBJECT_VERSION_NUMBER_O
=> per_assignments_f2_pkg.g_old_asg_rec.object_version_number
,P_ORGANIZATION_ID_O
=> per_assignments_f2_pkg.g_old_asg_rec.organization_id
,P_PAYROLL_ID_O
=> per_assignments_f2_pkg.g_old_asg_rec.payroll_id
,P_PAY_BASIS_ID_O
=> per_assignments_f2_pkg.g_old_asg_rec.pay_basis_id
,P_PEOPLE_GROUP_ID_O
=> per_assignments_f2_pkg.g_old_asg_rec.people_group_id
,P_PERF_REVIEW_PERIOD_O
=> per_assignments_f2_pkg.g_old_asg_rec.perf_review_period
,P_PERF_REVIEW_PERIOD_FREQUEN_O
=> per_assignments_f2_pkg.g_old_asg_rec.perf_review_period_frequency
,P_PERIOD_OF_SERVICE_ID_O
=> per_assignments_f2_pkg.g_old_asg_rec.period_of_service_id
,P_PERSON_ID_O
=> per_assignments_f2_pkg.g_old_asg_rec.person_id
,P_PERSON_REFERRED_BY_ID_O
=> per_assignments_f2_pkg.g_old_asg_rec.person_referred_by_id
,P_PLACEMENT_DATE_START_O
=> per_assignments_f2_pkg.g_old_asg_rec.period_of_placement_date_start
,P_POSITION_ID_O
=> per_assignments_f2_pkg.g_old_asg_rec.position_id
,P_POSTING_CONTENT_ID_O
=> per_assignments_f2_pkg.g_old_asg_rec.posting_content_id
,P_PRIMARY_FLAG_O
=> per_assignments_f2_pkg.g_old_asg_rec.primary_flag
,P_PROBATION_PERIOD_O
=> per_assignments_f2_pkg.g_old_asg_rec.probation_period
,P_PROBATION_UNIT_O
=> per_assignments_f2_pkg.g_old_asg_rec.probation_unit
,P_PROGRAM_APPLICATION_ID_O
=> per_assignments_f2_pkg.g_old_asg_rec.program_application_id
,P_PROGRAM_ID_O
=> per_assignments_f2_pkg.g_old_asg_rec.program_id
,P_PROGRAM_UPDATE_DATE_O
=> per_assignments_f2_pkg.g_old_asg_rec.program_update_date
,P_PROJECT_TITLE_O
=> per_assignments_f2_pkg.g_old_asg_rec.project_title
,P_RECRUITER_ID_O
=> per_assignments_f2_pkg.g_old_asg_rec.recruiter_id
,P_RECRUITMENT_ACTIVITY_ID_O
=> per_assignments_f2_pkg.g_old_asg_rec.recruitment_activity_id
,P_REQUEST_ID_O
=> per_assignments_f2_pkg.g_old_asg_rec.request_id
,P_SAL_REVIEW_PERIOD_O
=> per_assignments_f2_pkg.g_old_asg_rec.sal_review_period
,P_SAL_REVIEW_PERIOD_FREQUEN_O
=> per_assignments_f2_pkg.g_old_asg_rec.sal_review_period_frequency
,P_SET_OF_BOOKS_ID_O
=> per_assignments_f2_pkg.g_old_asg_rec.set_of_books_id
,P_SOFT_CODING_KEYFLEX_ID_O
=> per_assignments_f2_pkg.g_old_asg_rec.soft_coding_keyflex_id
,P_SOURCE_ORGANIZATION_ID_O
=> per_assignments_f2_pkg.g_old_asg_rec.source_organization_id
,P_SOURCE_TYPE_O
=> per_assignments_f2_pkg.g_old_asg_rec.source_type
,P_SPECIAL_CEILING_STEP_ID_O
=> per_assignments_f2_pkg.g_old_asg_rec.special_ceiling_step_id
,P_SUPERVISOR_ID_O
=> per_assignments_f2_pkg.g_old_asg_rec.supervisor_id
,P_TIME_NORMAL_FINISH_O
=> per_assignments_f2_pkg.g_old_asg_rec.time_normal_finish
,P_TIME_NORMAL_START_O
=> per_assignments_f2_pkg.g_old_asg_rec.time_normal_start
,P_TITLE_O
=> per_assignments_f2_pkg.g_old_asg_rec.title
,P_VACANCY_ID_O
=> per_assignments_f2_pkg.g_old_asg_rec.vacancy_id
,P_VENDOR_ASSIGNMENT_NUMBER_O
=> per_assignments_f2_pkg.g_old_asg_rec.vendor_assignment_number
,P_VENDOR_EMPLOYEE_NUMBER_O
=> per_assignments_f2_pkg.g_old_asg_rec.vendor_employee_number
,P_VENDOR_ID_O
=> per_assignments_f2_pkg.g_old_asg_rec.vendor_id
,P_WORK_AT_HOME_O
=> per_assignments_f2_pkg.g_old_asg_rec.work_at_home
,P_GRADE_LADDER_PGM_ID_O
=> per_assignments_f2_pkg.g_old_asg_rec.grade_ladder_pgm_id
,P_SUPERVISOR_ASSIGNMENT_ID_O
=> per_assignments_f2_pkg.g_old_asg_rec.supervisor_assignment_id
,P_VENDOR_SITE_ID_O
=> per_assignments_f2_pkg.g_old_asg_rec.vendor_site_id
,P_PO_HEADER_ID_O
=> per_assignments_f2_pkg.g_old_asg_rec.po_header_id
,P_PO_LINE_ID_O
=> per_assignments_f2_pkg.g_old_asg_rec.po_line_id
,P_PROJECTED_ASSIGNMENT_END_O
=> per_assignments_f2_pkg.g_old_asg_rec.projected_assignment_end
);
end post_update;
procedure post_insert(
p_prim_change_flag IN OUT NOCOPY varchar2,
p_val_st_date date,
p_new_end_date date,
p_eot date,
p_pd_os_id number,
p_ass_id number,
p_new_prim_ass_id IN OUT NOCOPY number,
p_pg_id number,
p_group_name varchar2,
p_bg_id number,
p_dt_upd_mode varchar2,
p_dt_del_mode varchar2,
p_per_sys_st varchar2,
p_sess_date date,
p_val_end_date date,
p_new_pay_id number,
p_old_pay_id number,
p_scl_id number,
p_scl_concat varchar2,
p_warning IN OUT NOCOPY varchar2) is
--
l_raise_warning varchar2(1);
select assignment_id
,effective_start_date
,effective_end_date
,business_group_id
,recruiter_id
,grade_id
,position_id
,job_id
,assignment_status_type_id
,payroll_id
,location_id
,person_referred_by_id
,supervisor_id
,special_ceiling_step_id
,person_id
,recruitment_activity_id
,source_organization_id
,organization_id
,people_group_id
,soft_coding_keyflex_id
,vacancy_id
,pay_basis_id
,assignment_sequence
,assignment_type
,primary_flag
,application_id
,assignment_number
,change_reason
,comment_id
,null
,date_probation_end
,default_code_comb_id
,employment_category
,frequency
,internal_address_line
,manager_flag
,normal_hours
,perf_review_period
,perf_review_period_frequency
,period_of_service_id
,probation_period
,probation_unit
,sal_review_period
,sal_review_period_frequency
,set_of_books_id
,source_type
,time_normal_finish
,time_normal_start
,bargaining_unit_code
,labour_union_member_flag
,hourly_salaried_code
,request_id
,program_application_id
,program_id
,program_update_date
,ass_attribute_category
,ass_attribute1
,ass_attribute2
,ass_attribute3
,ass_attribute4
,ass_attribute5
,ass_attribute6
,ass_attribute7
,ass_attribute8
,ass_attribute9
,ass_attribute10
,ass_attribute11
,ass_attribute12
,ass_attribute13
,ass_attribute14
,ass_attribute15
,ass_attribute16
,ass_attribute17
,ass_attribute18
,ass_attribute19
,ass_attribute20
,ass_attribute21
,ass_attribute22
,ass_attribute23
,ass_attribute24
,ass_attribute25
,ass_attribute26
,ass_attribute27
,ass_attribute28
,ass_attribute29
,ass_attribute30
,title
,object_version_number
,contract_id
,establishment_id
,collective_agreement_id
,cagr_grade_def_id
,cagr_id_flex_num
,notice_period
,notice_period_uom
,employee_category
,work_at_home
,job_post_source_name
,posting_content_id
,period_of_placement_date_start
,vendor_id
,vendor_employee_number
,vendor_assignment_number
,assignment_category
,project_title
,applicant_rank
,grade_ladder_pgm_id
,supervisor_assignment_id
,vendor_site_id
,po_header_id
,po_line_id
,projected_assignment_end
from per_all_assignments_f
where assignment_id = p_asg_id
and p_eff_date between effective_start_date
and effective_end_date;
l_proc varchar2(11) := 'post_insert';
iud_update_primary(
'INSERT',
'Y',
p_val_st_date,
p_new_end_date,
p_eot,
p_pd_os_id,
p_ass_id,
p_new_prim_ass_id,
p_prim_change_flag);
update_group(
p_pg_id,
p_group_name,
p_bg_id);
update_scl(
p_scl_id,
p_scl_concat);
select position_id
from per_all_assignments
where assignment_id = p_ass_id;
,p_datetrack_mode => 'INSERT'
,p_base_table_name => 'per_all_assignments_f'
,p_base_key_column => 'assignment_id'
,p_base_key_value => p_ass_id
,p_validation_start_date => l_vsd
,p_validation_end_date => l_ved
);
pay_pog_all_assignments_pkg.after_insert
(p_effective_date => p_sess_date
,p_validation_start_date => l_vsd
,p_validation_end_date => l_ved
,P_APPLICANT_RANK => ins_asg_rec.applicant_rank
,P_APPLICATION_ID => ins_asg_rec.program_application_id
,P_ASSIGNMENT_CATEGORY => ins_asg_rec.assignment_category
,P_ASSIGNMENT_ID => ins_asg_rec.assignment_id
,P_ASSIGNMENT_NUMBER => ins_asg_rec.assignment_number
,P_ASSIGNMENT_SEQUENCE => ins_asg_rec.assignment_sequence
,P_ASSIGNMENT_STATUS_TYPE_ID => ins_asg_rec.assignment_status_type_id
,P_ASSIGNMENT_TYPE => ins_asg_rec.assignment_type
,P_ASS_ATTRIBUTE1 => ins_asg_rec.ass_attribute1
,P_ASS_ATTRIBUTE10 => ins_asg_rec.ass_attribute10
,P_ASS_ATTRIBUTE11 => ins_asg_rec.ass_attribute11
,P_ASS_ATTRIBUTE12 => ins_asg_rec.ass_attribute12
,P_ASS_ATTRIBUTE13 => ins_asg_rec.ass_attribute13
,P_ASS_ATTRIBUTE14 => ins_asg_rec.ass_attribute14
,P_ASS_ATTRIBUTE15 => ins_asg_rec.ass_attribute15
,P_ASS_ATTRIBUTE16 => ins_asg_rec.ass_attribute16
,P_ASS_ATTRIBUTE17 => ins_asg_rec.ass_attribute17
,P_ASS_ATTRIBUTE18 => ins_asg_rec.ass_attribute18
,P_ASS_ATTRIBUTE19 => ins_asg_rec.ass_attribute19
,P_ASS_ATTRIBUTE2 => ins_asg_rec.ass_attribute2
,P_ASS_ATTRIBUTE20 => ins_asg_rec.ass_attribute20
,P_ASS_ATTRIBUTE21 => ins_asg_rec.ass_attribute21
,P_ASS_ATTRIBUTE22 => ins_asg_rec.ass_attribute22
,P_ASS_ATTRIBUTE23 => ins_asg_rec.ass_attribute23
,P_ASS_ATTRIBUTE24 => ins_asg_rec.ass_attribute24
,P_ASS_ATTRIBUTE25 => ins_asg_rec.ass_attribute25
,P_ASS_ATTRIBUTE26 => ins_asg_rec.ass_attribute26
,P_ASS_ATTRIBUTE27 => ins_asg_rec.ass_attribute27
,P_ASS_ATTRIBUTE28 => ins_asg_rec.ass_attribute28
,P_ASS_ATTRIBUTE29 => ins_asg_rec.ass_attribute29
,P_ASS_ATTRIBUTE3 => ins_asg_rec.ass_attribute3
,P_ASS_ATTRIBUTE30 => ins_asg_rec.ass_attribute30
,P_ASS_ATTRIBUTE4 => ins_asg_rec.ass_attribute4
,P_ASS_ATTRIBUTE5 => ins_asg_rec.ass_attribute5
,P_ASS_ATTRIBUTE6 => ins_asg_rec.ass_attribute6
,P_ASS_ATTRIBUTE7 => ins_asg_rec.ass_attribute7
,P_ASS_ATTRIBUTE8 => ins_asg_rec.ass_attribute8
,P_ASS_ATTRIBUTE9 => ins_asg_rec.ass_attribute9
,P_ASS_ATTRIBUTE_CATEGORY => ins_asg_rec.ass_attribute_category
,P_BARGAINING_UNIT_CODE => ins_asg_rec.bargaining_unit_code
,P_BUSINESS_GROUP_ID => ins_asg_rec.business_group_id
,P_CAGR_GRADE_DEF_ID => ins_asg_rec.cagr_grade_def_id
,P_CAGR_ID_FLEX_NUM => ins_asg_rec.cagr_id_flex_num
,P_CHANGE_REASON => ins_asg_rec.change_reason
,P_COLLECTIVE_AGREEMENT_ID => ins_asg_rec.collective_agreement_id
,P_COMMENT_ID => ins_asg_rec.comment_id
,P_CONTRACT_ID => ins_asg_rec.contract_id
,P_DATE_PROBATION_END => ins_asg_rec.date_probation_end
,P_DEFAULT_CODE_COMB_ID => ins_asg_rec.default_code_comb_id
,P_EFFECTIVE_END_DATE => ins_asg_rec.effective_end_date
,P_EFFECTIVE_START_DATE => ins_asg_rec.effective_start_date
,P_EMPLOYEE_CATEGORY => ins_asg_rec.employee_category
,P_EMPLOYMENT_CATEGORY => ins_asg_rec.employment_category
,P_ESTABLISHMENT_ID => ins_asg_rec.establishment_id
,P_FREQUENCY => ins_asg_rec.frequency
,P_GRADE_ID => ins_asg_rec.grade_id
,P_HOURLY_SALARIED_CODE => ins_asg_rec.hourly_salaried_code
,P_INTERNAL_ADDRESS_LINE => ins_asg_rec.internal_address_line
,P_JOB_ID => ins_asg_rec.job_id
,P_JOB_POST_SOURCE_NAME => ins_asg_rec.job_post_source_name
,P_LABOUR_UNION_MEMBER_FLAG => ins_asg_rec.labour_union_member_flag
,P_LOCATION_ID => ins_asg_rec.location_id
,P_MANAGER_FLAG => ins_asg_rec.manager_flag
,P_NORMAL_HOURS => ins_asg_rec.normal_hours
,P_NOTICE_PERIOD => ins_asg_rec.notice_period
,P_NOTICE_PERIOD_UOM => ins_asg_rec.notice_period_uom
,P_OBJECT_VERSION_NUMBER => ins_asg_rec.object_version_number
,P_ORGANIZATION_ID => ins_asg_rec.organization_id
,P_PAYROLL_ID => ins_asg_rec.payroll_id
,P_PAY_BASIS_ID => ins_asg_rec.pay_basis_id
,P_PEOPLE_GROUP_ID => ins_asg_rec.people_group_id
,P_PERF_REVIEW_PERIOD => ins_asg_rec.perf_review_period
,P_PERF_REVIEW_PERIOD_FREQUEN => ins_asg_rec.perf_review_period_frequency
,P_PERIOD_OF_SERVICE_ID => ins_asg_rec.period_of_service_id
,P_PERSON_ID => ins_asg_rec.person_id
,P_PERSON_REFERRED_BY_ID => ins_asg_rec.person_referred_by_id
,P_PLACEMENT_DATE_START => ins_asg_rec.period_of_placement_date_start
,P_POSITION_ID => ins_asg_rec.position_id
,P_POSTING_CONTENT_ID => ins_asg_rec.posting_content_id
,P_PRIMARY_FLAG => ins_asg_rec.primary_flag
,P_PROBATION_PERIOD => ins_asg_rec.probation_period
,P_PROBATION_UNIT => ins_asg_rec.probation_unit
,P_PROGRAM_APPLICATION_ID => ins_asg_rec.program_application_id
,P_PROGRAM_ID => ins_asg_rec.program_id
,P_PROGRAM_UPDATE_DATE => ins_asg_rec.program_update_date
,P_PROJECT_TITLE => ins_asg_rec.project_title
,P_RECRUITER_ID => ins_asg_rec.recruiter_id
,P_RECRUITMENT_ACTIVITY_ID => ins_asg_rec.recruitment_activity_id
,P_REQUEST_ID => ins_asg_rec.request_id
,P_SAL_REVIEW_PERIOD => ins_asg_rec.sal_review_period
,P_SAL_REVIEW_PERIOD_FREQUEN => ins_asg_rec.sal_review_period_frequency
,P_SET_OF_BOOKS_ID => ins_asg_rec.set_of_books_id
,P_SOFT_CODING_KEYFLEX_ID => ins_asg_rec.soft_coding_keyflex_id
,P_SOURCE_ORGANIZATION_ID => ins_asg_rec.source_organization_id
,P_SOURCE_TYPE => ins_asg_rec.source_type
,P_SPECIAL_CEILING_STEP_ID => ins_asg_rec.special_ceiling_step_id
,P_SUPERVISOR_ID => ins_asg_rec.supervisor_id
,P_TIME_NORMAL_FINISH => ins_asg_rec.time_normal_finish
,P_TIME_NORMAL_START => ins_asg_rec.time_normal_start
,P_TITLE => ins_asg_rec.title
,P_VACANCY_ID => ins_asg_rec.vacancy_id
,P_VENDOR_ASSIGNMENT_NUMBER => ins_asg_rec.vendor_assignment_number
,P_VENDOR_EMPLOYEE_NUMBER => ins_asg_rec.vendor_employee_number
,P_VENDOR_ID => ins_asg_rec.vendor_id
,P_WORK_AT_HOME => ins_asg_rec.work_at_home
,P_GRADE_LADDER_PGM_ID => ins_asg_rec.grade_ladder_pgm_id
,P_SUPERVISOR_ASSIGNMENT_ID => ins_asg_rec.supervisor_assignment_id
,P_VENDOR_SITE_ID => ins_asg_rec.vendor_site_id
,P_PO_HEADER_ID => ins_asg_rec.po_header_id
,P_PO_LINE_ID => ins_asg_rec.po_line_id
,P_PROJECTED_ASSIGNMENT_END => ins_asg_rec.projected_assignment_end
);
end post_insert;
procedure post_delete(
p_ass_id number,
p_grd_id number,
p_sess_date date,
p_new_end_date date,
p_val_end_date date,
p_eff_end_date date,
p_del_mode varchar2,
p_val_st_date date,
p_new_prim_flag varchar2,
p_eot date,
p_pd_os_id number,
p_new_prim_ass_id IN OUT NOCOPY number,
p_prim_change_flag IN OUT NOCOPY varchar2,
p_per_sys_st varchar2,
p_bg_id number,
p_old_pay_id number,
p_new_pay_id number,
p_cancel_atd date,
p_cancel_lspd date,
p_reterm_atd date,
p_reterm_lspd date,
p_warning IN OUT NOCOPY varchar2,
p_future_spp_warning OUT NOCOPY boolean,
p_cost_warning OUT NOCOPY boolean) is
l_raise_warning varchar2(1);
l_proc VARCHAR2(72) := g_package||'post_delete';
select effective_start_date
, object_version_number
, business_group_id
from per_all_assignments_f
where assignment_id = p_asg_id
and p_eff_date between effective_start_date
and effective_end_date;
,p_spp_delete_warning => l_future_spp_warnings);
if p_del_mode in ('FUTURE_CHANGE', 'DELETE_NEXT_CHANGE') then
--
l_calling_proc := 'POST_DELETE';
l_calling_proc := 'POST_DELETE';
hr_assignment.del_ref_int_delete(
p_ass_id,
p_grd_id,
'END',
p_new_end_date,
0, 0,
l_calling_proc,
p_val_st_date,
p_val_end_date,
p_del_mode,
l_dummy_warning);
l_calling_proc := 'POST_DELETE';
hr_assignment.del_ref_int_delete(
p_ass_id,
null,
'ZAP',
p_val_st_date,
0, 0,
l_calling_proc,
p_val_st_date,
p_val_end_date,
p_del_mode,
l_dummy_warning);
iud_update_primary(
p_del_mode,
p_new_prim_flag,
p_val_st_date,
p_new_end_date,
p_eot,
p_pd_os_id,
p_ass_id,
p_new_prim_ass_id,
p_prim_change_flag);
if p_del_mode in ('DELETE_NEXT_CHANGE', 'FUTURE_CHANGE') then
--
if p_new_end_date is not null then
--
set_end_date
(p_new_end_date,
p_ass_id);
pay_pog_all_assignments_pkg.after_delete
(p_effective_date => p_sess_date
,p_datetrack_mode => p_del_mode
,p_validation_start_date => p_val_st_date
,p_validation_end_date => p_val_end_date
,P_ASSIGNMENT_ID => p_ass_id
,P_EFFECTIVE_END_DATE => p_eff_end_date
,P_EFFECTIVE_START_DATE => l_esd
,P_OBJECT_VERSION_NUMBER => l_ovn
,P_ORG_NOW_NO_MANAGER_WARNING => null
,P_APPLICANT_RANK_O
=> per_assignments_f2_pkg.g_old_asg_rec.applicant_rank
,P_APPLICATION_ID_O
=> per_assignments_f2_pkg.g_old_asg_rec.application_id
,P_ASSIGNMENT_CATEGORY_O
=> per_assignments_f2_pkg.g_old_asg_rec.assignment_category
,P_ASSIGNMENT_NUMBER_O
=> per_assignments_f2_pkg.g_old_asg_rec.assignment_number
,P_ASSIGNMENT_SEQUENCE_O
=> per_assignments_f2_pkg.g_old_asg_rec.assignment_sequence
,P_ASSIGNMENT_STATUS_TYPE_ID_O
=> per_assignments_f2_pkg.g_old_asg_rec.assignment_status_type_id
,P_ASSIGNMENT_TYPE_O
=> per_assignments_f2_pkg.g_old_asg_rec.assignment_type
,P_ASS_ATTRIBUTE1_O
=> per_assignments_f2_pkg.g_old_asg_rec.ass_attribute1
,P_ASS_ATTRIBUTE10_O
=> per_assignments_f2_pkg.g_old_asg_rec.ass_attribute10
,P_ASS_ATTRIBUTE11_O
=> per_assignments_f2_pkg.g_old_asg_rec.ass_attribute11
,P_ASS_ATTRIBUTE12_O
=> per_assignments_f2_pkg.g_old_asg_rec.ass_attribute12
,P_ASS_ATTRIBUTE13_O
=> per_assignments_f2_pkg.g_old_asg_rec.ass_attribute13
,P_ASS_ATTRIBUTE14_O
=> per_assignments_f2_pkg.g_old_asg_rec.ass_attribute14
,P_ASS_ATTRIBUTE15_O
=> per_assignments_f2_pkg.g_old_asg_rec.ass_attribute15
,P_ASS_ATTRIBUTE16_O
=> per_assignments_f2_pkg.g_old_asg_rec.ass_attribute16
,P_ASS_ATTRIBUTE17_O
=> per_assignments_f2_pkg.g_old_asg_rec.ass_attribute17
,P_ASS_ATTRIBUTE18_O
=> per_assignments_f2_pkg.g_old_asg_rec.ass_attribute18
,P_ASS_ATTRIBUTE19_O
=> per_assignments_f2_pkg.g_old_asg_rec.ass_attribute19
,P_ASS_ATTRIBUTE2_O
=> per_assignments_f2_pkg.g_old_asg_rec.ass_attribute2
,P_ASS_ATTRIBUTE20_O
=> per_assignments_f2_pkg.g_old_asg_rec.ass_attribute20
,P_ASS_ATTRIBUTE21_O
=> per_assignments_f2_pkg.g_old_asg_rec.ass_attribute21
,P_ASS_ATTRIBUTE22_O
=> per_assignments_f2_pkg.g_old_asg_rec.ass_attribute22
,P_ASS_ATTRIBUTE23_O
=> per_assignments_f2_pkg.g_old_asg_rec.ass_attribute23
,P_ASS_ATTRIBUTE24_O
=> per_assignments_f2_pkg.g_old_asg_rec.ass_attribute24
,P_ASS_ATTRIBUTE25_O
=> per_assignments_f2_pkg.g_old_asg_rec.ass_attribute25
,P_ASS_ATTRIBUTE26_O
=> per_assignments_f2_pkg.g_old_asg_rec.ass_attribute26
,P_ASS_ATTRIBUTE27_O
=> per_assignments_f2_pkg.g_old_asg_rec.ass_attribute27
,P_ASS_ATTRIBUTE28_O
=> per_assignments_f2_pkg.g_old_asg_rec.ass_attribute28
,P_ASS_ATTRIBUTE29_O
=> per_assignments_f2_pkg.g_old_asg_rec.ass_attribute29
,P_ASS_ATTRIBUTE3_O
=> per_assignments_f2_pkg.g_old_asg_rec.ass_attribute3
,P_ASS_ATTRIBUTE30_O
=> per_assignments_f2_pkg.g_old_asg_rec.ass_attribute30
,P_ASS_ATTRIBUTE4_O
=> per_assignments_f2_pkg.g_old_asg_rec.ass_attribute4
,P_ASS_ATTRIBUTE5_O
=> per_assignments_f2_pkg.g_old_asg_rec.ass_attribute5
,P_ASS_ATTRIBUTE6_O
=> per_assignments_f2_pkg.g_old_asg_rec.ass_attribute6
,P_ASS_ATTRIBUTE7_O
=> per_assignments_f2_pkg.g_old_asg_rec.ass_attribute7
,P_ASS_ATTRIBUTE8_O
=> per_assignments_f2_pkg.g_old_asg_rec.ass_attribute8
,P_ASS_ATTRIBUTE9_O
=> per_assignments_f2_pkg.g_old_asg_rec.ass_attribute9
,P_ASS_ATTRIBUTE_CATEGORY_O
=> per_assignments_f2_pkg.g_old_asg_rec.ass_attribute_category
,P_BARGAINING_UNIT_CODE_O
=> per_assignments_f2_pkg.g_old_asg_rec.bargaining_unit_code
,P_BUSINESS_GROUP_ID_O
=> p_bg_id
,P_CAGR_GRADE_DEF_ID_O
=> per_assignments_f2_pkg.g_old_asg_rec.cagr_grade_def_id
,P_CAGR_ID_FLEX_NUM_O
=> per_assignments_f2_pkg.g_old_asg_rec.cagr_id_flex_num
,P_CHANGE_REASON_O
=> per_assignments_f2_pkg.g_old_asg_rec.change_reason
,P_COLLECTIVE_AGREEMENT_ID_O
=> per_assignments_f2_pkg.g_old_asg_rec.collective_agreement_id
,P_COMMENT_ID_O
=> per_assignments_f2_pkg.g_old_asg_rec.comment_id
,P_CONTRACT_ID_O
=> per_assignments_f2_pkg.g_old_asg_rec.contract_id
,P_DATE_PROBATION_END_O
=> per_assignments_f2_pkg.g_old_asg_rec.date_probation_end
,P_DEFAULT_CODE_COMB_ID_O
=> per_assignments_f2_pkg.g_old_asg_rec.default_code_comb_id
,P_EFFECTIVE_END_DATE_O
=> per_assignments_f2_pkg.g_old_asg_rec.effective_end_date
,P_EFFECTIVE_START_DATE_O
=> per_assignments_f2_pkg.g_old_asg_rec.effective_start_date
,P_EMPLOYEE_CATEGORY_O
=> per_assignments_f2_pkg.g_old_asg_rec.employee_category
,P_EMPLOYMENT_CATEGORY_O
=> per_assignments_f2_pkg.g_old_asg_rec.employment_category
,P_ESTABLISHMENT_ID_O
=> per_assignments_f2_pkg.g_old_asg_rec.establishment_id
,P_FREQUENCY_O
=> per_assignments_f2_pkg.g_old_asg_rec.frequency
,P_GRADE_ID_O
=> per_assignments_f2_pkg.g_old_asg_rec.grade_id
,P_HOURLY_SALARIED_CODE_O
=> per_assignments_f2_pkg.g_old_asg_rec.hourly_salaried_code
,P_INTERNAL_ADDRESS_LINE_O
=> per_assignments_f2_pkg.g_old_asg_rec.internal_address_line
,P_JOB_ID_O
=> per_assignments_f2_pkg.g_old_asg_rec.job_id
,P_JOB_POST_SOURCE_NAME_O
=> per_assignments_f2_pkg.g_old_asg_rec.job_post_source_name
,P_LABOUR_UNION_MEMBER_FLAG_O
=> per_assignments_f2_pkg.g_old_asg_rec.labour_union_member_flag
,P_LOCATION_ID_O
=> per_assignments_f2_pkg.g_old_asg_rec.location_id
,P_MANAGER_FLAG_O
=> per_assignments_f2_pkg.g_old_asg_rec.manager_flag
,P_NORMAL_HOURS_O
=> per_assignments_f2_pkg.g_old_asg_rec.normal_hours
,P_NOTICE_PERIOD_O
=> per_assignments_f2_pkg.g_old_asg_rec.notice_period
,P_NOTICE_PERIOD_UOM_O
=> per_assignments_f2_pkg.g_old_asg_rec.notice_period_uom
,P_OBJECT_VERSION_NUMBER_O
=> per_assignments_f2_pkg.g_old_asg_rec.object_version_number
,P_ORGANIZATION_ID_O
=> per_assignments_f2_pkg.g_old_asg_rec.organization_id
,P_PAYROLL_ID_O
=> per_assignments_f2_pkg.g_old_asg_rec.payroll_id
,P_PAY_BASIS_ID_O
=> per_assignments_f2_pkg.g_old_asg_rec.pay_basis_id
,P_PEOPLE_GROUP_ID_O
=> per_assignments_f2_pkg.g_old_asg_rec.people_group_id
,P_PERF_REVIEW_PERIOD_O
=> per_assignments_f2_pkg.g_old_asg_rec.perf_review_period
,P_PERF_REVIEW_PERIOD_FREQUEN_O
=> per_assignments_f2_pkg.g_old_asg_rec.perf_review_period_frequency
,P_PERIOD_OF_SERVICE_ID_O
=> per_assignments_f2_pkg.g_old_asg_rec.period_of_service_id
,P_PERSON_ID_O
=> per_assignments_f2_pkg.g_old_asg_rec.person_id
,P_PERSON_REFERRED_BY_ID_O
=> per_assignments_f2_pkg.g_old_asg_rec.person_referred_by_id
,P_PLACEMENT_DATE_START_O
=> per_assignments_f2_pkg.g_old_asg_rec.period_of_placement_date_start
,P_POSITION_ID_O
=> per_assignments_f2_pkg.g_old_asg_rec.position_id
,P_POSTING_CONTENT_ID_O
=> per_assignments_f2_pkg.g_old_asg_rec.posting_content_id
,P_PRIMARY_FLAG_O
=> per_assignments_f2_pkg.g_old_asg_rec.primary_flag
,P_PROBATION_PERIOD_O
=> per_assignments_f2_pkg.g_old_asg_rec.probation_period
,P_PROBATION_UNIT_O
=> per_assignments_f2_pkg.g_old_asg_rec.probation_unit
,P_PROGRAM_APPLICATION_ID_O
=> per_assignments_f2_pkg.g_old_asg_rec.program_application_id
,P_PROGRAM_ID_O
=> per_assignments_f2_pkg.g_old_asg_rec.program_id
,P_PROGRAM_UPDATE_DATE_O
=> per_assignments_f2_pkg.g_old_asg_rec.program_update_date
,P_PROJECT_TITLE_O
=> per_assignments_f2_pkg.g_old_asg_rec.project_title
,P_RECRUITER_ID_O
=> per_assignments_f2_pkg.g_old_asg_rec.recruiter_id
,P_RECRUITMENT_ACTIVITY_ID_O
=> per_assignments_f2_pkg.g_old_asg_rec.recruitment_activity_id
,P_REQUEST_ID_O
=> per_assignments_f2_pkg.g_old_asg_rec.request_id
,P_SAL_REVIEW_PERIOD_O
=> per_assignments_f2_pkg.g_old_asg_rec.sal_review_period
,P_SAL_REVIEW_PERIOD_FREQUEN_O
=> per_assignments_f2_pkg.g_old_asg_rec.sal_review_period_frequency
,P_SET_OF_BOOKS_ID_O
=> per_assignments_f2_pkg.g_old_asg_rec.set_of_books_id
,P_SOFT_CODING_KEYFLEX_ID_O
=> per_assignments_f2_pkg.g_old_asg_rec.soft_coding_keyflex_id
,P_SOURCE_ORGANIZATION_ID_O
=> per_assignments_f2_pkg.g_old_asg_rec.source_organization_id
,P_SOURCE_TYPE_O
=> per_assignments_f2_pkg.g_old_asg_rec.source_type
,P_SPECIAL_CEILING_STEP_ID_O
=> per_assignments_f2_pkg.g_old_asg_rec.special_ceiling_step_id
,P_SUPERVISOR_ID_O
=> per_assignments_f2_pkg.g_old_asg_rec.supervisor_id
,P_TIME_NORMAL_FINISH_O
=> per_assignments_f2_pkg.g_old_asg_rec.time_normal_finish
,P_TIME_NORMAL_START_O
=> per_assignments_f2_pkg.g_old_asg_rec.time_normal_start
,P_TITLE_O
=> per_assignments_f2_pkg.g_old_asg_rec.title
,P_VACANCY_ID_O
=> per_assignments_f2_pkg.g_old_asg_rec.vacancy_id
,P_VENDOR_ASSIGNMENT_NUMBER_O
=> per_assignments_f2_pkg.g_old_asg_rec.vendor_assignment_number
,P_VENDOR_EMPLOYEE_NUMBER_O
=> per_assignments_f2_pkg.g_old_asg_rec.vendor_employee_number
,P_VENDOR_ID_O
=> per_assignments_f2_pkg.g_old_asg_rec.vendor_id
,P_WORK_AT_HOME_O
=> per_assignments_f2_pkg.g_old_asg_rec.work_at_home
,P_GRADE_LADDER_PGM_ID_O
=> per_assignments_f2_pkg.g_old_asg_rec.grade_ladder_pgm_id
,P_SUPERVISOR_ASSIGNMENT_ID_O
=> per_assignments_f2_pkg.g_old_asg_rec.supervisor_assignment_id
,P_VENDOR_SITE_ID_O
=> per_assignments_f2_pkg.g_old_asg_rec.vendor_site_id
,P_PO_HEADER_ID_O
=> per_assignments_f2_pkg.g_old_asg_rec.po_header_id
,P_PO_LINE_ID_O
=> per_assignments_f2_pkg.g_old_asg_rec.po_line_id
,P_PROJECTED_ASSIGNMENT_END_O
=> per_assignments_f2_pkg.g_old_asg_rec.projected_assignment_end
);
end post_delete;