[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
update per_assignments_f a
set a.effective_end_date = p_new_end_date
where a.assignment_id = p_assignment_id
and a.effective_end_date = (
select max(a2.effective_end_date)
from per_assignments_f a2
where a2.assignment_id = a.assignment_id);
procedure delete_child ( p_assignment_id in number,
p_delete_mode in varchar2) is
p_del_flag VARCHAR2(1) := 'N';
hr_utility.set_location ( 'PER_APP_ASG_PKG.delete_child' , 5) ;
select 'Y'
into p_del_flag
from sys.dual
where exists (
select null
from per_assignment_budget_values_f
where assignment_id = p_assignment_id
and p_delete_mode = 'ZAP');
p_delete_mode = 'ZAP' THEN
--
hr_utility.set_location ( 'PER_APP_ASG_PKG.delete_child' , 10) ;
Delete per_assignment_budget_values_f
where assignment_id = p_assignment_id;
END delete_child;
delete from per_letter_request_lines p
where p.assignment_id = p_assignment_id
and exists
(select null
from per_letter_requests r2
where r2.letter_request_id = p.letter_request_id
and r2.request_status = 'PENDING')
and not exists
(select null
from per_assignments_f a
where assignment_id = p_assignment_id
and ( (a.effective_start_date = p.date_from
and
a.assignment_status_type_id = p.assignment_status_type_id)
or (a.effective_end_date =
(select max(a2.effective_end_date)
from per_assignments_f a2
where a2.assignment_id = p_assignment_id)
and a.effective_end_date = p.date_from ))) ;
procedure insert_row(
p_row_id in out nocopy varchar2,
p_assignment_id in out nocopy number,
p_effective_start_date date,
p_effective_end_date date,
p_business_group_id number,
p_recruiter_id number,
p_grade_id number,
p_position_id number,
p_job_id number,
p_assignment_status_type_id number,
p_location_id number,
p_location_code in out nocopy varchar2,
p_person_referred_by_id number,
p_supervisor_id number,
p_person_id number,
p_recruitment_activity_id number,
p_source_organization_id number,
p_organization_id number,
p_people_group_id number,
p_people_group_name varchar2,
p_vacancy_id number,
p_assignment_sequence in out nocopy number,
p_assignment_type in out nocopy varchar2,
p_primary_flag in out nocopy varchar2,
p_application_id number,
p_change_reason varchar2,
p_comment_id number,
p_date_probation_end date,
p_frequency varchar2,
p_frequency_meaning in out nocopy varchar2,
p_manager_flag varchar2,
p_normal_hours number,
p_probation_period number,
p_probation_unit varchar2,
p_source_type varchar2,
p_time_normal_finish varchar2,
p_time_normal_start varchar2,
p_request_id number,
p_program_application_id number,
p_program_id number,
p_program_update_date date,
p_ass_attribute_category varchar2,
p_ass_attribute1 varchar2,
p_ass_attribute2 varchar2,
p_ass_attribute3 varchar2,
p_ass_attribute4 varchar2,
p_ass_attribute5 varchar2,
p_ass_attribute6 varchar2,
p_ass_attribute7 varchar2,
p_ass_attribute8 varchar2,
p_ass_attribute9 varchar2,
p_ass_attribute10 varchar2,
p_ass_attribute11 varchar2,
p_ass_attribute12 varchar2,
p_ass_attribute13 varchar2,
p_ass_attribute14 varchar2,
p_ass_attribute15 varchar2,
p_ass_attribute16 varchar2,
p_ass_attribute17 varchar2,
p_ass_attribute18 varchar2,
p_ass_attribute19 varchar2,
p_ass_attribute20 varchar2,
p_ass_attribute21 varchar2,
p_ass_attribute22 varchar2,
p_ass_attribute23 varchar2,
p_ass_attribute24 varchar2,
p_ass_attribute25 varchar2,
p_ass_attribute26 varchar2,
p_ass_attribute27 varchar2,
p_ass_attribute28 varchar2,
p_ass_attribute29 varchar2,
p_ass_attribute30 varchar2,
p_session_date date,
p_contract_id number default null,
p_cagr_id_flex_num number default null,
p_cagr_grade_def_id number default null,
p_establishment_id number default null,
p_collective_agreement_id number default null,
p_notice_period number default null,
p_notice_period_uom varchar2 default null,
p_employee_category varchar2 default null,
p_work_at_home varchar2 default null,
p_job_post_source_name varchar2 default null,
p_grade_ladder_pgm_id number default null,
p_supervisor_assignment_id number default null
) is
cursor c1 is
select per_assignments_s.nextval
from sys.dual;
select rowid
from per_assignments_f
where assignment_id = P_ASSIGNMENT_ID
and effective_start_date = P_EFFECTIVE_START_DATE
and effective_end_date = P_EFFECTIVE_END_DATE;
hr_utility.set_location ( 'PER_APP_ASG_PKG.insert_row' , 1 ) ;
hr_utility.set_location ( 'PER_APP_ASG_PKG.insert_row' , 2 ) ;
hr_utility.set_location ( 'PER_APP_ASG_PKG.insert_row' , 3 ) ;
hr_utility.set_location ( 'PER_APP_ASG_PKG.insert_row' , 4 ) ;
insert into per_assignments_f (
assignment_id,
effective_start_date,
effective_end_date,
business_group_id,
recruiter_id,
grade_id,
position_id,
job_id,
assignment_status_type_id,
location_id,
person_referred_by_id,
supervisor_id,
person_id,
recruitment_activity_id,
source_organization_id,
organization_id,
people_group_id,
vacancy_id,
assignment_sequence,
assignment_type,
primary_flag,
application_id,
change_reason,
comment_id,
date_probation_end,
frequency,
manager_flag,
normal_hours,
probation_period,
probation_unit,
source_type,
time_normal_finish,
time_normal_start,
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,
contract_id,
cagr_id_flex_num,
cagr_grade_def_id,
establishment_id,
collective_agreement_id,
notice_period,
notice_period_uom,
work_at_home,
employee_category,
job_post_source_name ,
grade_ladder_pgm_id,
supervisor_assignment_id )
values (
p_assignment_id,
p_effective_start_date,
p_effective_end_date,
p_business_group_id,
p_recruiter_id,
p_grade_id,
p_position_id,
p_job_id,
p_assignment_status_type_id,
p_location_id,
p_person_referred_by_id,
p_supervisor_id,
p_person_id,
p_recruitment_activity_id,
p_source_organization_id,
p_organization_id,
p_people_group_id,
p_vacancy_id,
p_assignment_sequence,
p_assignment_type,
p_primary_flag,
p_application_id,
p_change_reason,
p_comment_id,
p_date_probation_end,
p_frequency,
p_manager_flag,
p_normal_hours,
p_probation_period,
p_probation_unit,
p_source_type,
p_time_normal_finish,
p_time_normal_start,
p_request_id,
p_program_application_id,
p_program_id,
p_program_update_date,
p_ass_attribute_category,
p_ass_attribute1,
p_ass_attribute2,
p_ass_attribute3,
p_ass_attribute4,
p_ass_attribute5,
p_ass_attribute6,
p_ass_attribute7,
p_ass_attribute8,
p_ass_attribute9,
p_ass_attribute10,
p_ass_attribute11,
p_ass_attribute12,
p_ass_attribute13,
p_ass_attribute14,
p_ass_attribute15,
p_ass_attribute16,
p_ass_attribute17,
p_ass_attribute18,
p_ass_attribute19,
p_ass_attribute20,
p_ass_attribute21,
p_ass_attribute22,
p_ass_attribute23,
p_ass_attribute24,
p_ass_attribute25,
p_ass_attribute26,
p_ass_attribute27,
p_ass_attribute28,
p_ass_attribute29,
p_ass_attribute30,
p_contract_id,
p_cagr_id_flex_num,
p_cagr_grade_def_id,
p_establishment_id,
p_collective_agreement_id,
p_notice_period,
p_notice_period_uom,
p_work_at_home,
p_employee_category,
p_job_post_source_name ,
p_grade_ladder_pgm_id,
p_supervisor_assignment_id
) ;
hr_utility.set_location ( 'PER_APP_ASG_PKG.insert_row' , 5 ) ;
per_applicant_pkg.update_group ( p_people_group_id,
p_people_group_name ) ;
hr_utility.set_location ( 'PER_APP_ASG_PKG.insert_row' , 6 ) ;
hr_utility.set_location ( 'PER_APP_ASG_PKG.insert_row' , 7 ) ;
hr_utility.set_location ( 'PER_APP_ASG_PKG.insert_row' , 8 ) ;
hr_utility.set_location ( 'PER_APP_ASG_PKG.insert_row' , 9 ) ;
hr_utility.set_location ( 'PER_APP_ASG_PKG.insert_row' , 10 ) ;
hr_utility.set_location ( 'PER_APP_ASG_PKG.insert_row' , 11 ) ;
hr_utility.set_location ( 'PER_APP_ASG_PKG.insert_row' , 11 ) ;
end insert_row;
procedure delete_row(p_row_id varchar2,
p_assignment_id number,
p_new_end_date date,
p_effective_end_date date,
p_validation_end_date date,
p_session_date date,
p_delete_mode varchar2 ) is
l_cost_warning boolean; -- used to catch the cost warning from tidy_up_ref_int
select min(assignment_status_id) from irc_assignment_statuses
where assignment_id = p_assignment_id
and STATUS_CHANGE_DATE > p_effective_end_date;
hr_utility.set_location ( 'PER_APP_ASG_PKG.delete_row' , 5 ) ;
delete_child ( p_assignment_id
,p_delete_mode);
delete from per_assignments_f a
where a.rowid = chartorowid(P_ROW_ID);
irc_asg_status_api.dt_delete_irc_asg_status(
p_validate => false
,p_assignment_status_id => l_irc_asg_type
,p_object_version_number => l_irc_asg_ovn
,p_effective_date => p_effective_end_date
,p_datetrack_mode => p_delete_mode
);
if ( p_delete_mode = 'ZAP' ) then
return ; -- This case is handled by the form at present
hr_utility.set_location ( 'PER_APP_ASG_PKG.delete_row' , 10 ) ;
elsif ( p_delete_mode in ('FUTURE_CHANGE','DELETE_NEXT_CHANGE' ) ) then
if ( p_new_end_date is null ) then
if ( p_validation_end_date = hr_general.end_of_time ) then
hr_assignment.tidy_up_ref_int ( p_assignment_id,
'FUTURE',
p_validation_end_date,
p_effective_end_date,
null,
null ,
l_cost_warning) ;
hr_utility.set_location ( 'PER_APP_ASG_PKG.delete_row' , 15 ) ;
hr_utility.set_location ( 'PER_APP_ASG_PKG.delete_row' , 20 ) ;
hr_utility.set_location ( 'PER_APP_ASG_PKG.delete_row' , 25 ) ;
hr_utility.set_location ( 'PER_APP_ASG_PKG.delete_row' , 27) ;
app_exception.invalid_argument( 'per_app_asg_pkg.delete_row',
'p_delete_mode',
p_delete_mode ) ;
end delete_row ;
p_program_update_date date,
p_ass_attribute_category varchar2,
p_ass_attribute1 varchar2,
p_ass_attribute2 varchar2,
p_ass_attribute3 varchar2,
p_ass_attribute4 varchar2,
p_ass_attribute5 varchar2,
p_ass_attribute6 varchar2,
p_ass_attribute7 varchar2,
p_ass_attribute8 varchar2,
p_ass_attribute9 varchar2,
p_ass_attribute10 varchar2,
p_ass_attribute11 varchar2,
p_ass_attribute12 varchar2,
p_ass_attribute13 varchar2,
p_ass_attribute14 varchar2,
p_ass_attribute15 varchar2,
p_ass_attribute16 varchar2,
p_ass_attribute17 varchar2,
p_ass_attribute18 varchar2,
p_ass_attribute19 varchar2,
p_ass_attribute20 varchar2,
p_ass_attribute21 varchar2,
p_ass_attribute22 varchar2,
p_ass_attribute23 varchar2,
p_ass_attribute24 varchar2,
p_ass_attribute25 varchar2,
p_ass_attribute26 varchar2,
p_ass_attribute27 varchar2,
p_ass_attribute28 varchar2,
p_ass_attribute29 varchar2,
p_ass_attribute30 varchar2,
p_contract_id number,
p_cagr_id_flex_num number,
p_cagr_grade_def_id number,
p_establishment_id number,
p_collective_agreement_id number,
p_notice_period number,
p_notice_period_uom varchar2,
p_employee_category varchar2,
p_work_at_home varchar2,
p_job_post_source_name varchar2,
p_grade_ladder_pgm_id number,
p_supervisor_assignment_id number ) is
cursor ASS_CUR is
select *
from per_assignments_f a
where a.rowid = chartorowid(P_ROW_ID)
FOR UPDATE OF ASSIGNMENT_ID NOWAIT;
and ((ass_rec.program_update_date = p_program_update_date)
or (ass_rec.program_update_date is null
and (p_program_update_date is null)))) then
if ( ((ass_rec.ass_attribute_category = p_ass_attribute_category)
or (ass_rec.ass_attribute_category is null
and (p_ass_attribute_category is null)))
and ((ass_rec.ass_attribute1 = p_ass_attribute1)
or (ass_rec.ass_attribute1 is null
and (p_ass_attribute1 is null)))
and ((ass_rec.ass_attribute2 = p_ass_attribute2)
or (ass_rec.ass_attribute2 is null
and (p_ass_attribute2 is null)))
and ((ass_rec.ass_attribute3 = p_ass_attribute3)
or (ass_rec.ass_attribute3 is null
and (p_ass_attribute3 is null)))
and ((ass_rec.ass_attribute4 = p_ass_attribute4)
or (ass_rec.ass_attribute4 is null
and (p_ass_attribute4 is null)))
and ((ass_rec.ass_attribute5 = p_ass_attribute5)
or (ass_rec.ass_attribute5 is null
and (p_ass_attribute5 is null)))
and ((ass_rec.ass_attribute6 = p_ass_attribute6)
or (ass_rec.ass_attribute6 is null
and (p_ass_attribute6 is null)))
and ((ass_rec.ass_attribute7 = p_ass_attribute7)
or (ass_rec.ass_attribute7 is null
and (p_ass_attribute7 is null)))
and ((ass_rec.ass_attribute8 = p_ass_attribute8)
or (ass_rec.ass_attribute8 is null
and (p_ass_attribute8 is null)))
and ((ass_rec.ass_attribute9 = p_ass_attribute9)
or (ass_rec.ass_attribute9 is null
and (p_ass_attribute9 is null)))
and ((ass_rec.ass_attribute10 = p_ass_attribute10)
or (ass_rec.ass_attribute10 is null
and (p_ass_attribute10 is null)))
and ((ass_rec.ass_attribute11 = p_ass_attribute11)
or (ass_rec.ass_attribute11 is null
and (p_ass_attribute11 is null)))
and ((ass_rec.ass_attribute12 = p_ass_attribute12)
or (ass_rec.ass_attribute12 is null
and (p_ass_attribute12 is null)))
and ((ass_rec.ass_attribute13 = p_ass_attribute13)
or (ass_rec.ass_attribute13 is null
and (p_ass_attribute13 is null)))
and ((ass_rec.ass_attribute14 = p_ass_attribute14)
or (ass_rec.ass_attribute14 is null
and (p_ass_attribute14 is null)))
and ((ass_rec.ass_attribute15 = p_ass_attribute15)
or (ass_rec.ass_attribute15 is null
and (p_ass_attribute15 is null)))
and ((ass_rec.ass_attribute16 = p_ass_attribute16)
or (ass_rec.ass_attribute16 is null
and (p_ass_attribute16 is null)))
and ((ass_rec.ass_attribute17 = p_ass_attribute17)
or (ass_rec.ass_attribute17 is null
and (p_ass_attribute17 is null)))
and ((ass_rec.ass_attribute18 = p_ass_attribute18)
or (ass_rec.ass_attribute18 is null
and (p_ass_attribute18 is null)))
and ((ass_rec.ass_attribute19 = p_ass_attribute19)
or (ass_rec.ass_attribute19 is null
and (p_ass_attribute19 is null)))
and ((ass_rec.ass_attribute20 = p_ass_attribute20)
or (ass_rec.ass_attribute20 is null
and (p_ass_attribute20 is null)))
and ((ass_rec.ass_attribute21 = p_ass_attribute21)
or (ass_rec.ass_attribute21 is null
and (p_ass_attribute21 is null)))
and ((ass_rec.ass_attribute22 = p_ass_attribute22)
or (ass_rec.ass_attribute22 is null
and (p_ass_attribute22 is null)))
and ((ass_rec.ass_attribute23 = p_ass_attribute23)
or (ass_rec.ass_attribute23 is null
and (p_ass_attribute23 is null)))
and ((ass_rec.ass_attribute24 = p_ass_attribute24)
or (ass_rec.ass_attribute24 is null
and (p_ass_attribute24 is null)))
and ((ass_rec.ass_attribute25 = p_ass_attribute25)
or (ass_rec.ass_attribute25 is null
and (p_ass_attribute25 is null)))
and ((ass_rec.ass_attribute26 = p_ass_attribute26)
or (ass_rec.ass_attribute26 is null
and (p_ass_attribute26 is null)))
and ((ass_rec.ass_attribute27 = p_ass_attribute27)
or (ass_rec.ass_attribute27 is null
and (p_ass_attribute27 is null)))
and ((ass_rec.ass_attribute28 = p_ass_attribute28)
or (ass_rec.ass_attribute28 is null
and (p_ass_attribute28 is null)))
and ((ass_rec.ass_attribute29 = p_ass_attribute29)
or (ass_rec.ass_attribute29 is null
and (p_ass_attribute29 is null)))
and ((ass_rec.ass_attribute30 = p_ass_attribute30)
or (ass_rec.ass_attribute30 is null
and (p_ass_attribute30 is null)))
) then
hr_utility.set_location ( 'PER_APP_ASG_PKG.lock_row' , 10) ;
procedure update_row(
p_row_id varchar2,
p_assignment_id number,
p_effective_start_date date,
p_effective_end_date date,
p_validation_start_date date,
p_business_group_id number,
p_recruiter_id number,
p_grade_id number,
p_position_id number,
p_job_id number,
p_assignment_status_type_id number,
p_per_system_status varchar2,
p_location_id number,
p_location_code in out nocopy varchar2,
p_person_referred_by_id number,
p_supervisor_id number,
p_person_id number,
p_recruitment_activity_id number,
p_source_organization_id number,
p_organization_id number,
p_people_group_id number,
p_vacancy_id number,
p_assignment_sequence number,
p_assignment_type varchar2,
p_primary_flag varchar2,
p_application_id number,
p_change_reason varchar2,
p_comment_id number,
p_date_probation_end date,
p_frequency varchar2,
p_frequency_meaning in out nocopy varchar2,
p_manager_flag varchar2,
p_normal_hours number,
p_probation_period number,
p_probation_unit varchar2,
p_source_type varchar2,
p_time_normal_finish varchar2,
p_time_normal_start varchar2,
p_request_id number,
p_program_application_id number,
p_program_id number,
p_program_update_date date,
p_ass_attribute_category varchar2,
p_ass_attribute1 varchar2,
p_ass_attribute2 varchar2,
p_ass_attribute3 varchar2,
p_ass_attribute4 varchar2,
p_ass_attribute5 varchar2,
p_ass_attribute6 varchar2,
p_ass_attribute7 varchar2,
p_ass_attribute8 varchar2,
p_ass_attribute9 varchar2,
p_ass_attribute10 varchar2,
p_ass_attribute11 varchar2,
p_ass_attribute12 varchar2,
p_ass_attribute13 varchar2,
p_ass_attribute14 varchar2,
p_ass_attribute15 varchar2,
p_ass_attribute16 varchar2,
p_ass_attribute17 varchar2,
p_ass_attribute18 varchar2,
p_ass_attribute19 varchar2,
p_ass_attribute20 varchar2,
p_ass_attribute21 varchar2,
p_ass_attribute22 varchar2,
p_ass_attribute23 varchar2,
p_ass_attribute24 varchar2,
p_ass_attribute25 varchar2,
p_ass_attribute26 varchar2,
p_ass_attribute27 varchar2,
p_ass_attribute28 varchar2,
p_ass_attribute29 varchar2,
p_ass_attribute30 varchar2,
p_session_date date,
p_status_changed boolean,
p_contract_id number default null,
p_cagr_id_flex_num number default null,
p_cagr_grade_def_id number default null,
p_establishment_id number default null,
p_collective_agreement_id number default null,
p_notice_period number default null,
p_notice_period_uom varchar2 default null,
p_employee_category varchar2 default null,
p_work_at_home varchar2 default null,
p_job_post_source_name varchar2 default null,
p_grade_ladder_pgm_id number default null,
p_supervisor_assignment_id number default null,
p_payroll_id number default null, --Bug 4861490
p_pay_basis_id number default null --Bug 4861490
) is
l_cost_warning boolean; -- used to catch the cost warning from tidy_up_ref_int
select paf.organization_id
from per_all_assignments_f paf
where assignment_id = p_assignment_id
and p_effective_start_date between effective_start_date
and effective_end_date;
select *
from per_assignments_f a
where a.rowid = chartorowid(P_ROW_ID);
hr_utility.set_location('Entering : per_app_asg_pkg.update_row' ,10);
select assignment_status_type_id, vacancy_id
into l_previous_asg_status, l_previous_vacancy_id
from per_assignments_f where rowid = chartorowid(P_ROW_ID);
hr_utility.set_location ( 'PER_APP_ASG_PKG.update_row' , 11 ) ;
update per_assignments_f a
set a.assignment_id = P_ASSIGNMENT_ID,
a.effective_start_date = P_EFFECTIVE_START_DATE,
a.effective_end_date = P_EFFECTIVE_END_DATE,
a.business_group_id = P_BUSINESS_GROUP_ID,
a.recruiter_id = P_RECRUITER_ID,
a.grade_id = P_GRADE_ID,
a.position_id = P_POSITION_ID,
a.job_id = P_JOB_ID,
a.assignment_status_type_id = P_ASSIGNMENT_STATUS_TYPE_ID,
a.location_id = P_LOCATION_ID,
a.person_referred_by_id = P_PERSON_REFERRED_BY_ID,
a.supervisor_id = P_SUPERVISOR_ID,
a.person_id = P_PERSON_ID,
a.recruitment_activity_id = P_RECRUITMENT_ACTIVITY_ID,
a.source_organization_id = P_SOURCE_ORGANIZATION_ID,
a.organization_id = P_ORGANIZATION_ID,
a.people_group_id = P_PEOPLE_GROUP_ID,
a.vacancy_id = P_VACANCY_ID,
a.assignment_sequence = P_ASSIGNMENT_SEQUENCE,
a.assignment_type = P_ASSIGNMENT_TYPE,
a.primary_flag = P_PRIMARY_FLAG,
a.application_id = P_APPLICATION_ID,
a.change_reason = P_CHANGE_REASON,
a.comment_id = P_COMMENT_ID,
a.date_probation_end = P_DATE_PROBATION_END,
a.frequency = P_FREQUENCY,
a.manager_flag = P_MANAGER_FLAG,
a.normal_hours = P_NORMAL_HOURS,
a.probation_period = P_PROBATION_PERIOD,
a.probation_unit = P_PROBATION_UNIT,
a.source_type = P_SOURCE_TYPE,
a.time_normal_finish = P_TIME_NORMAL_FINISH,
a.time_normal_start = P_TIME_NORMAL_START,
a.request_id = P_REQUEST_ID,
a.program_application_id = P_PROGRAM_APPLICATION_ID,
a.program_id = P_PROGRAM_ID,
a.program_update_date = P_PROGRAM_UPDATE_DATE,
a.ass_attribute_category = P_ASS_ATTRIBUTE_CATEGORY,
a.ass_attribute1 = P_ASS_ATTRIBUTE1,
a.ass_attribute2 = P_ASS_ATTRIBUTE2,
a.ass_attribute3 = P_ASS_ATTRIBUTE3,
a.ass_attribute4 = P_ASS_ATTRIBUTE4,
a.ass_attribute5 = P_ASS_ATTRIBUTE5,
a.ass_attribute6 = P_ASS_ATTRIBUTE6,
a.ass_attribute7 = P_ASS_ATTRIBUTE7,
a.ass_attribute8 = P_ASS_ATTRIBUTE8,
a.ass_attribute9 = P_ASS_ATTRIBUTE9,
a.ass_attribute10 = P_ASS_ATTRIBUTE10,
a.ass_attribute11 = P_ASS_ATTRIBUTE11,
a.ass_attribute12 = P_ASS_ATTRIBUTE12,
a.ass_attribute13 = P_ASS_ATTRIBUTE13,
a.ass_attribute14 = P_ASS_ATTRIBUTE14,
a.ass_attribute15 = P_ASS_ATTRIBUTE15,
a.ass_attribute16 = P_ASS_ATTRIBUTE16,
a.ass_attribute17 = P_ASS_ATTRIBUTE17,
a.ass_attribute18 = P_ASS_ATTRIBUTE18,
a.ass_attribute19 = P_ASS_ATTRIBUTE19,
a.ass_attribute20 = P_ASS_ATTRIBUTE20,
a.ass_attribute21 = P_ASS_ATTRIBUTE21,
a.ass_attribute22 = P_ASS_ATTRIBUTE22,
a.ass_attribute23 = P_ASS_ATTRIBUTE23,
a.ass_attribute24 = P_ASS_ATTRIBUTE24,
a.ass_attribute25 = P_ASS_ATTRIBUTE25,
a.ass_attribute26 = P_ASS_ATTRIBUTE26,
a.ass_attribute27 = P_ASS_ATTRIBUTE27,
a.ass_attribute28 = P_ASS_ATTRIBUTE28,
a.ass_attribute29 = P_ASS_ATTRIBUTE29,
a.ass_attribute30 = P_ASS_ATTRIBUTE30,
a.collective_agreement_id = P_COLLECTIVE_AGREEMENT_ID,
a.cagr_grade_def_id = P_CAGR_GRADE_DEF_ID,
a.establishment_id = P_ESTABLISHMENT_ID,
a.contract_id = P_CONTRACT_ID,
a.cagr_id_flex_num = P_CAGR_ID_FLEX_NUM,
a.notice_period = P_NOTICE_PERIOD,
a.notice_period_uom = P_NOTICE_PERIOD_UOM,
a.work_at_home = P_WORK_AT_HOME,
a.employee_category = P_EMPLOYEE_CATEGORY,
a.job_post_source_name = P_JOB_POST_SOURCE_NAME,
a.grade_ladder_pgm_id = p_grade_ladder_pgm_id,
a.supervisor_assignment_id = p_supervisor_assignment_id,
a.payroll_id = p_payroll_id, --Bug 4861490
a.pay_basis_id = p_pay_basis_id --Bug 4861490
where a.rowid = chartorowid(P_ROW_ID);
hr_utility.set_location ('per_app_asg_pkg.update_row', 13);
hr_utility.set_location ('per_app_asg_pkg.update_row', 14);
hr_utility.set_location ('per_app_asg_pkg.update_row', 15);
hr_utility.set_location ('per_app_asg_pkg.update_row', 16);
hr_utility.set_location ('per_app_asg_pkg.update_row', 17);
delete from per_letter_request_lines plrl
where plrl.assignment_id = p_assignment_id
and plrl.assignment_status_type_id = p_assignment_status_type_id
and exists
(select null
from per_letter_requests plr
where plr.letter_request_id = plrl.letter_request_id
and plr.request_status = 'PENDING'
and plr.auto_or_manual = 'AUTO');
hr_utility.set_location ('per_app_asg_pkg.update_row', 18);
delete from per_letter_requests plr
where plr.business_group_id = p_business_group_id
and plr.request_status = 'PENDING'
and plr.auto_or_manual = 'AUTO'
and not exists
( select 1
from per_letter_request_lines plrl
where plrl.letter_request_id = plr.letter_request_id
) ;
hr_utility.set_location ('per_app_asg_pkg.update_row', 19);
hr_utility.set_location ('per_app_asg_pkg.update_row', 20);
hr_utility.set_location ('per_app_asg_pkg.update_row', 21);
hr_utility.set_location ('per_app_asg_pkg.update_row', 22);
hr_utility.set_location(' Leaving : per_app_asg_pkg.update_row' ,100);
end update_row;
procedure check_apl_update_allowed( p_application_id in number,
p_assignment_id in number,
p_person_id in number,
p_status out nocopy varchar2 ) is
cursor get_max_apl_date is
select max(effective_end_date)
from per_assignments_f paf,
per_assignment_status_types past
where paf.application_id = p_application_id
and paf.assignment_id = p_assignment_id
and paf.assignment_type = 'A'
and paf.assignment_status_type_id = past.assignment_status_type_id
and past.per_system_status in ('ACTIVE_APL','ACCEPTED',
'INTERVIEW1','INTERVIEW2','OFFER');
hr_utility.set_location ('per_app_asg_pkg.check_apl_update_allowed', 10);
hr_utility.set_location ('per_app_asg_pkg.check_apl_update_allowed', 20);
end check_apl_update_allowed;
select 1
from per_applications a
where a.application_id = p_application_id
and a.date_end is null ;
select 1
from per_people_f
where person_id = p_person_id
and current_applicant_flag = 'Y'
and p_session_date
between effective_start_date and effective_end_date ;
select 1
from per_assignment_status_types a
, per_ass_status_type_amends b
where b.assignment_status_type_id(+) = a.assignment_status_type_id
and b.business_group_id(+) + 0 = p_business_group_id
and nvl(a.business_group_id,p_business_group_id) = p_business_group_id
and nvl(a.legislation_code,p_legislation_code) = p_legislation_code
and nvl(b.active_flag,a.active_flag) = 'Y'
and nvl(b.per_system_status,a.per_system_status) = p_per_system_status
and a.assignment_status_type_id = p_assignment_status_type_id ;
select 1
from per_assignments_f a
where assignment_id = p_assignment_id
and exists
(select null
from per_assignment_status_types b
where b.per_system_status in ('TERM_APL','ACTIVE_ASSIGN')
and a.assignment_status_type_id = b.assignment_status_type_id) ;
select max(effective_end_date)
from per_assignments_f
where assignment_id = p_assignment_id ;
select 1
from sys.dual
where exists ( select 1
from per_assignments_f
where assignment_id <> p_assignment_id ) ;
select nvl(a.date_end,to_date('31/12/4712','DD/MM/YYYY'))
from per_applications a
where a.person_id = p_person_id
and p_session_date
between a.date_received
and nvl(a.date_end,p_session_date)
and a.business_group_id + 0 = p_business_group_id ;
select max(a.effective_end_date)
from per_all_assignments_f a
where a.person_id = p_person_id
and a.business_group_id + 0 = p_business_group_id
and a.assignment_id <> p_assignment_id
and a.assignment_type = 'A'
and l_max_end_date + 1
between a.effective_start_date
and a.effective_end_date ;
select 'Y' from per_all_assignments_f
where assignment_id = cp_asg_id
and assignment_type = 'E'
and effective_start_date > cp_effective_date;
select 'Y' from per_assignments_f apl
where apl.assignment_type = 'A'
and apl.application_id = cp_appl_id
and apl.assignment_id <> cp_asg_id
and (apl.effective_end_date = hr_general.end_of_time
or exists
(select 'Y' from per_applications apa
where apa.application_id = cp_appl_id
and apa.date_end is not null
and apa.date_end >= apl.effective_end_date));
p_delete_mode in varchar2 ) is
l_max_end_date date ;
select min(effective_start_date)-1
from per_assignments_f
where assignment_id = p_assignment_id ;
if ( p_delete_mode in ( 'FUTURE_CHANGE' , 'DELETE_NEXT_CHANGE' ) ) then
--
check_future_stat_change( p_assignment_id => p_assignment_id ) ;
elsif ( p_delete_mode = 'ZAP' ) then
--
hr_utility.set_location ('per_app_asg_pkg.key_delrec', 20);
'p_delete_mode',
p_delete_mode ) ;
procedure pre_delete_validation ( p_business_group_id in number,
p_assignment_id in number,
p_application_id in number,
p_person_id in number,
p_session_date in date,
p_validation_start_date in date,
p_validation_end_date in date,
p_delete_mode in varchar2,
p_new_end_date in out nocopy date ) is
l_max_end_date date ;
select min(effective_start_date)-1
from per_assignments_f
where assignment_id = p_assignment_id ;
select date_end
from per_applications
where application_id = p_application_id
and nvl(date_end,to_date('31/12/4712','DD/MM/YYYY'))
< p_validation_end_date ;
hr_utility.set_location ('per_app_asg_pkg.pre_delete_validation', 10);
if ( p_delete_mode in ( 'FUTURE_CHANGE' , 'DELETE_NEXT_CHANGE' ) ) then
--
check_future_stat_change( p_assignment_id => p_assignment_id ) ;
elsif ( p_delete_mode = 'ZAP' ) then
--
hr_utility.set_location ('per_app_asg_pkg.pre_delete_validation', 20);
hr_utility.set_location ('per_app_asg_pkg.pre_delete_validation', 25);
hr_utility.set_location ('per_app_asg_pkg.pre_delete_validation', 27);
else app_exception.invalid_argument( 'PER_APP_ASG_PKG.PRE_DELETE_VALIDATION',
'p_delete_mode',
p_delete_mode ) ;
hr_utility.set_location ('per_app_asg_pkg.pre_delete_validation', 30);
end pre_delete_validation ;
procedure post_delete ( p_assignment_id in number,
p_validation_start_date in date ) is
l_out_parameter boolean; -- Out parmater used to warn is future changes to the spinal
hr_utility.set_location ('per_app_asg_pkg.post_delete', 10);
hr_assignment.del_ref_int_delete ( p_assignment_id,
NULL,
'ZAP',
p_validation_start_date,
null,
null,
null,
null,
null,
null,
l_out_parameter
) ;
hr_utility.set_location ('per_app_asg_pkg.post_delete', 20);
hr_utility.set_location ('per_app_asg_pkg.post_delete', 30);
end post_delete ;
p_update_mode varchar2,
p_record_status varchar2,
p_per_system_status varchar2,
p_allowed out nocopy varchar2 ) IS
--
l_dummy varchar2(1) := 'N';
select 'Y' from per_all_assignments_f a
where p_assignment_id = a.assignment_id
and (a.effective_start_date < p_effective_start_date);
and ( p_update_mode = 'UPDATE' or p_update_mode is null OR l_dummy = 'Y' ) then
--
p_allowed := 'STATUSES_UPDATE';
and (l_dummy = 'Y' or p_update_mode = 'UPDATE'or p_update_mode is null) then
--
p_allowed := 'STATUSES_UPDATE';
p_allowed := 'STATUSES_INSERT';
if p_update_mode = 'CORRECTION' and l_dummy <> 'Y' then
p_allowed := 'STATUSES_INSERT';
p_allowed := 'STATUSES_UPDATE';