[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
,p_update_primary_flag VARCHAR2
,p_employee_number VARCHAR2
,p_set_of_books_id IN INTEGER
,p_emp_apl VARCHAR2
,p_adjusted_svc_date IN DATE
,p_session_date IN DATE -- Bug 3564129
-- #2264569
,p_table IN HR_EMPLOYEE_APPLICANT_API.t_ApplTable
) IS
/*
NAME
employ_applicant
DESCRIPTION
Procedures fired when applicant is hired.
PARAMETERS
p_business_group_id : Current business group.
p_legislation_code : Legislation code.
p_new_primary_id : Id of new primary assignment.
p_assignment_status_type_id: Current assignment status id.
p_user_id : user id
p_login_id : Login id of user.
p_start_date : Start date.
p_end_of_time : Maximum date that can be held by an Oracle system.
p_current_date : Today's Date
p_update_primary_flag : Flag whether to update the primary assignment or not
p_set_of_books_id : Current set of books_id
p_emp_apl : Whether EMP_APL or APL.
p_session_date : Session Date -- Bug 3564129
-- #2264569
p_table : PL/SQL table that has information about the type of
processing performed to the appl assignment.
*/
--
v_period_of_service_id INTEGER;
l_delete_warn boolean; -- #2933750
select segment1,
segment2,
segment3,
segment4,
segment5,
segment6,
segment7,
segment8,
segment9,
segment10,
segment11,
segment12,
segment13,
segment14,
segment15,
segment16,
segment17,
segment18,
segment19,
segment20,
segment21,
segment22,
segment23,
segment24,
segment25,
segment26,
segment27,
segment28,
segment29,
segment30
from pay_people_groups
where people_group_id = l_pg_id;
select assignment_id
from per_all_assignments_f
where person_id = p_person_id
and business_group_id = p_business_group_id
and primary_flag = 'Y'
and p_start_date between effective_start_date and effective_end_date;
select pay_proposal_id
from per_pay_proposals
where assignment_id=ass_id
and APPROVED='Y';
select pps.period_of_service_id
into v_dummy
from per_periods_of_service pps
where p_start_date between pps.date_start
and nvl(pps.ACTUAL_TERMINATION_DATE,p_end_of_time)
and pps.person_id = p_person_id
and pps.business_group_id + 0 = p_business_group_id;
select per_periods_of_service_s.nextval
into v_dummy
from sys.dual;
insert into per_periods_of_service
(period_of_service_id
,business_group_id
,person_id
,date_start
,last_update_date
,last_update_login
,last_updated_by
,created_by
,creation_date
,adjusted_svc_date)
values
(v_dummy
,p_business_group_id
,p_person_id
,p_start_date
,null
,null
,null
,null
,null
,p_adjusted_svc_date
);
,p_event => 'INSERTING'
,p_effective_date => p_start_date);
PROCEDURE update_primary_assignment(p_business_group_id IN INTEGER
,p_person_id IN INTEGER
,p_start_date IN DATE
,p_current_date IN DATE
,p_user_id IN INTEGER
,p_login_id IN INTEGER
) is
--
-- Date effectively end the current primary assignment
--
--
begin
--
hr_utility.set_location('hr_person.update_primary_assignment',1);
insert into per_assignments_f
(assignment_id
,effective_start_date
,effective_end_date
,business_group_id
,grade_id
,position_id
,job_id
,assignment_status_type_id
,payroll_id
,location_id
,person_id
,organization_id
,people_group_id
,soft_coding_keyflex_id
,vacancy_id
,assignment_sequence
,assignment_type
,manager_flag
,primary_flag
,application_id
,assignment_number
,change_reason
,comment_id
,date_probation_end
,default_code_comb_id
,frequency
,internal_address_line
,normal_hours
,period_of_service_id
,probation_period
,probation_unit
,recruiter_id
,set_of_books_id
,special_ceiling_step_id
,supervisor_id
,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
,last_update_date
,last_updated_by
,last_update_login
,created_by
,creation_date
,pay_basis_id
,person_referred_by_id
,recruitment_activity_id
,source_organization_id
,source_type
,employment_category /* columns added Bug 978981 */
,perf_review_period
,perf_review_period_frequency
,sal_review_period
,sal_review_period_frequency
,bargaining_unit_code
,labour_union_member_flag
,hourly_salaried_code
,title
,supervisor_assignment_id --- #Added for fix of 4053244
,EMPLOYEE_CATEGORY -- Added for fix of 4212826
,COLLECTIVE_AGREEMENT_ID
,CAGR_ID_FLEX_NUM
,CAGR_GRADE_DEF_ID
,GRADE_LADDER_PGM_ID
,contract_id -- fix of bug 14784589
)
select pa.assignment_id
,pa.effective_start_date
,p_start_date - 1
,pa.business_group_id
,pa.grade_id
,pa.position_id
,pa.job_id
,pa.assignment_status_type_id
,pa.payroll_id
,pa.location_id
,pa.person_id
,pa.organization_id
,pa.people_group_id
,pa.soft_coding_keyflex_id
,pa.vacancy_id
,pa.assignment_sequence
,pa.assignment_type
,pa.manager_flag
,pa.primary_flag
,pa.application_id
,pa.assignment_number
,pa.change_reason
,pa.comment_id
,pa.date_probation_end
,pa.default_code_comb_id
,pa.frequency
,pa.internal_address_line
,pa.normal_hours
,pa.period_of_service_id
,pa.probation_period
,pa.probation_unit
,pa.recruiter_id
,pa.set_of_books_id
,pa.special_ceiling_step_id
,pa.supervisor_id
,pa.time_normal_finish
,pa.time_normal_start
,pa.request_id
,pa.program_application_id
,pa.program_id
,pa.program_update_date
,pa.ass_attribute_category
,pa.ass_attribute1
,pa.ass_attribute2
,pa.ass_attribute3
,pa.ass_attribute4
,pa.ass_attribute5
,pa.ass_attribute6
,pa.ass_attribute7
,pa.ass_attribute8
,pa.ass_attribute9
,pa.ass_attribute10
,pa.ass_attribute11
,pa.ass_attribute12
,pa.ass_attribute13
,pa.ass_attribute14
,pa.ass_attribute15
,pa.ass_attribute16
,pa.ass_attribute17
,pa.ass_attribute18
,pa.ass_attribute19
,pa.ass_attribute20
,pa.ass_attribute21
,pa.ass_attribute22
,pa.ass_attribute23
,pa.ass_attribute24
,pa.ass_attribute25
,pa.ass_attribute26
,pa.ass_attribute27
,pa.ass_attribute28
,pa.ass_attribute29
,pa.ass_attribute30
,p_current_date
,p_user_id
,p_login_id
,pa.created_by
,pa.creation_date
,pa.pay_basis_id
,pa.person_referred_by_id
,pa.recruitment_activity_id
,pa.source_organization_id
,pa.source_type
,employment_category /* columns added Bug 978981 */
,perf_review_period
,perf_review_period_frequency
,sal_review_period
,sal_review_period_frequency
,bargaining_unit_code
,labour_union_member_flag
,hourly_salaried_code
,title
,pa.supervisor_assignment_id --- #Added for fix of 4053244
,pa.EMPLOYEE_CATEGORY -- Added for fix of 4212826
,pa.COLLECTIVE_AGREEMENT_ID
,pa.CAGR_ID_FLEX_NUM
,pa.CAGR_GRADE_DEF_ID
,pa.GRADE_LADDER_PGM_ID -- fix of bug 5513751
,pa.contract_id -- fix of bug 14784589
from per_assignments_f pa
where pa.person_id = p_person_id
and pa.business_group_id + 0 = p_business_group_id
and pa.primary_flag = 'Y'
and p_start_date between pa.effective_start_date
and pa.effective_end_date
and p_start_date > pa.effective_start_date; -- #1981550
end update_primary_assignment;
,p_update_primary_flag IN VARCHAR2
,p_new_primary_id IN INTEGER
,p_user_id IN INTEGER
,p_login_id IN INTEGER
,p_start_date IN DATE
,p_end_of_time IN DATE
,p_employee_number IN VARCHAR2
,p_set_of_books_id IN INTEGER
,p_current_date IN DATE
) is
--
-- Make other accepted assignment rows secondary
-- as long as the user does not want to keep them in the system
-- i.e. (R)etain value exists in p_table
--
-- counter to hold number of assignments inserted
-- used to check that all are updated.
v_count INTEGER;
select default_context_field_name
from fnd_descriptive_flexs
where application_id = 800 -- bug 5469726
and descriptive_flexfield_name = 'PER_ASSIGNMENTS';
select pa.*
from per_assignments_f pa
, per_assignment_status_types past
where nvl(past.business_group_id,p_business_group_id) = pa.business_group_id + 0
and nvl(past.legislation_code, p_legislation_code)
= p_legislation_code
and past.per_system_status = 'ACCEPTED'
and pa.assignment_type = 'A'
and pa.business_group_id + 0 = p_business_group_id
and pa.person_id = p_person_id
and past.assignment_status_type_id = pa.assignment_status_type_id
and ((p_update_primary_flag in ('Y','V')
and pa.assignment_id <> p_new_primary_id
)
or (p_update_primary_flag not in ('Y','V')
)
)
and p_start_date between pa.effective_start_date
and pa.effective_end_date
order by decode(pa.assignment_id,p_new_primary_id,1,0) desc --added for bug 5589928
for update of pa.assignment_status_type_id;
select pa.*
from per_assignments_f pa
, per_assignment_status_types past
where nvl(past.business_group_id,p_business_group_id) = pa.business_group_id + 0
and nvl(past.legislation_code, p_legislation_code)
= p_legislation_code
and past.per_system_status = 'ACCEPTED'
and pa.assignment_type = 'A'
and pa.business_group_id + 0 = p_business_group_id
and pa.person_id = p_person_id
and past.assignment_status_type_id = pa.assignment_status_type_id
and ((p_update_primary_flag in ('Y','V')
and pa.assignment_id <> p_new_primary_id
)
or (p_update_primary_flag not in ('Y','V')
)
)
and p_start_date between pa.effective_start_date
and pa.effective_end_date
and pa.assignment_id = p_new_primary_id
for update of pa.assignment_status_type_id;
select pa.*
from per_assignments_f pa
, per_assignment_status_types past
where nvl(past.business_group_id,p_business_group_id) = pa.business_group_id + 0
and nvl(past.legislation_code, p_legislation_code)
= p_legislation_code
and past.per_system_status = 'ACCEPTED'
and pa.assignment_type = 'A'
and pa.business_group_id + 0 = p_business_group_id
and pa.person_id = p_person_id
and past.assignment_status_type_id = pa.assignment_status_type_id
and ((p_update_primary_flag in ('Y','V')
and pa.assignment_id <> p_new_primary_id
)
or (p_update_primary_flag not in ('Y','V')
)
)
and p_start_date between pa.effective_start_date
and pa.effective_end_date
and pa.assignment_id <> p_new_primary_id
for update of pa.assignment_status_type_id;
select application_column_name
from fnd_descr_flex_column_usages fdfcu,
fnd_descr_flex_contexts fdfc
where fdfcu.descriptive_flexfield_name = 'PER_ASSIGNMENTS'
and fdfcu.descriptive_flexfield_name = fdfc.descriptive_flexfield_name
and fdfcu.descriptive_flex_context_code = fdfc.descriptive_flex_context_code
and fdfcu.application_id = fdfc.application_id --- bug 5469726
and fdfc.application_id = 800 --- bug 5469726
and fdfc.global_flag = 'N'
and l_col_name = 'ASSIGNMENT_TYPE';
select pay_proposal_id,object_version_number,proposed_salary_n, change_date
from per_pay_proposals
where assignment_id=ass_id
and approved = 'N'
order by change_date desc;
update per_applications pap
set date_end = p_start_date -1,
successful_flag = 'Y'
where pap.person_id = p_person_id -- added for bug 5469726
and exists (select '1'
from per_assignments_f pa,
per_assignment_status_types past
where nvl(past.business_group_id,p_business_group_id) = pa.business_group_id + 0
and nvl(past.legislation_code, p_legislation_code)
= p_legislation_code
and past.per_system_status = 'ACCEPTED'
and pa.assignment_type = 'A'
and pa.business_group_id + 0 = p_business_group_id
and pa.person_id = p_person_id
and pa.person_id = pap.person_id
and past.assignment_status_type_id = pa.assignment_status_type_id
and p_start_date between pap.date_received and nvl(pap.date_end,p_start_date)
and p_start_date between pa.effective_start_date
and pa.effective_end_date)
and not exists (select '1'
from per_assignments_f pa,
per_assignment_status_types past
where nvl(past.business_group_id,p_business_group_id) = pa.business_group_id + 0
and nvl(past.legislation_code, p_legislation_code)
= p_legislation_code
and past.per_system_status <> 'ACCEPTED'
and pa.assignment_type = 'A'
and pa.business_group_id + 0 = p_business_group_id
and pa.person_id = p_person_id
and pa.person_id = pap.person_id
and past.assignment_status_type_id = pa.assignment_status_type_id
and p_start_date between pa.effective_start_date
and pa.effective_end_date);
hr_utility.set_location('p_update_primary_flag '||p_update_primary_flag,2);
if p_update_primary_flag not in ('C','N') then commented for bug 5589928*/
open ass_cur;
insert into per_assignments_f
(assignment_id
,effective_start_date
,effective_end_date
,business_group_id
,grade_id
,position_id
,job_id
,assignment_status_type_id
,payroll_id
,location_id
,person_id
,organization_id
,people_group_id
,soft_coding_keyflex_id
,vacancy_id
,assignment_sequence
,assignment_type
,manager_flag
,primary_flag
,application_id
,assignment_number
,change_reason
,comment_id
,date_probation_end
,default_code_comb_id
,frequency
,internal_address_line
,normal_hours
,period_of_service_id
,probation_period
,probation_unit
,recruiter_id
,set_of_books_id
,special_ceiling_step_id
,supervisor_id
,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
,last_update_date
,last_updated_by
,last_update_login
,created_by
,creation_date
,pay_basis_id
,person_referred_by_id
,recruitment_activity_id
,source_organization_id
,source_type
,employment_category /* columns added Bug 978981 */
,perf_review_period
,perf_review_period_frequency
,sal_review_period
,sal_review_period_frequency
,bargaining_unit_code
,labour_union_member_flag
,hourly_salaried_code
,title
,job_post_source_name -- added for 4486233
,supervisor_assignment_id) ---#4053244
values
(l_asg_rec.assignment_id
,l_asg_rec.effective_start_date
,p_start_date - 1
,l_asg_rec.business_group_id
,l_asg_rec.grade_id
,l_asg_rec.position_id
,l_asg_rec.job_id
,l_asg_rec.assignment_status_type_id
,l_asg_rec.payroll_id
,l_asg_rec.location_id
,l_asg_rec.person_id
,l_asg_rec.organization_id
,l_asg_rec.people_group_id
,l_asg_rec.soft_coding_keyflex_id
,l_asg_rec.vacancy_id
,l_asg_rec.assignment_sequence
,l_asg_rec.assignment_type
,l_asg_rec.manager_flag
,l_asg_rec.primary_flag
,l_asg_rec.application_id
,l_asg_rec.assignment_number
,l_asg_rec.change_reason
,l_asg_rec.comment_id
,l_asg_rec.date_probation_end
,l_asg_rec.default_code_comb_id
,l_asg_rec.frequency
,l_asg_rec.internal_address_line
,l_asg_rec.normal_hours
,l_asg_rec.period_of_service_id
,l_asg_rec.probation_period
,l_asg_rec.probation_unit
,l_asg_rec.recruiter_id
,l_asg_rec.set_of_books_id
,l_asg_rec.special_ceiling_step_id
,l_asg_rec.supervisor_id
,l_asg_rec.time_normal_finish
,l_asg_rec.time_normal_start
,l_asg_rec.request_id
,l_asg_rec.program_application_id
,l_asg_rec.program_id
,l_asg_rec.program_update_date
,l_asg_rec.ass_attribute_category
,l_asg_rec.ass_attribute1
,l_asg_rec.ass_attribute2
,l_asg_rec.ass_attribute3
,l_asg_rec.ass_attribute4
,l_asg_rec.ass_attribute5
,l_asg_rec.ass_attribute6
,l_asg_rec.ass_attribute7
,l_asg_rec.ass_attribute8
,l_asg_rec.ass_attribute9
,l_asg_rec.ass_attribute10
,l_asg_rec.ass_attribute11
,l_asg_rec.ass_attribute12
,l_asg_rec.ass_attribute13
,l_asg_rec.ass_attribute14
,l_asg_rec.ass_attribute15
,l_asg_rec.ass_attribute16
,l_asg_rec.ass_attribute17
,l_asg_rec.ass_attribute18
,l_asg_rec.ass_attribute19
,l_asg_rec.ass_attribute20
,l_asg_rec.ass_attribute21
,l_asg_rec.ass_attribute22
,l_asg_rec.ass_attribute23
,l_asg_rec.ass_attribute24
,l_asg_rec.ass_attribute25
,l_asg_rec.ass_attribute26
,l_asg_rec.ass_attribute27
,l_asg_rec.ass_attribute28
,l_asg_rec.ass_attribute29
,l_asg_rec.ass_attribute30
,p_current_date
,p_user_id
,p_login_id
,l_asg_rec.created_by
,l_asg_rec.creation_date
,l_asg_rec.pay_basis_id
,l_asg_rec.person_referred_by_id
,l_asg_rec.recruitment_activity_id
,l_asg_rec.source_organization_id
,l_asg_rec.source_type
,l_asg_rec.employment_category /* columns added Bug 978981 */
,l_asg_rec.perf_review_period
,l_asg_rec.perf_review_period_frequency
,l_asg_rec.sal_review_period
,l_asg_rec.sal_review_period_frequency
,l_asg_rec.bargaining_unit_code
,l_asg_rec.labour_union_member_flag
,l_asg_rec.hourly_salaried_code
,l_asg_rec.title
,l_asg_rec.job_post_source_name -- added for 4486233
,l_asg_rec.supervisor_assignment_id); ---#4053244
,'INSERT'
,p_start_date
,p_end_of_time);
select projected_hire_date
from per_applications
where application_id =l_appl_id;
update per_assignments_f pa
set pa.assignment_status_type_id = p_assignment_status_type_id
, pa.assignment_type = 'E'
, pa.effective_start_date = p_start_date
, pa.effective_end_date = p_end_of_time
, pa.period_of_service_id = v_period_of_service_id
, pa.primary_flag = 'N'
, pa.assignment_number = p_assignment_number
, pa.assignment_sequence = p_assignment_sequence
, pa.last_update_date = p_current_date
, pa.last_updated_by = p_user_id
, pa.last_update_login = p_login_id
, pa.set_of_books_id = p_set_of_books_id
, pa.ass_attribute_category = decode(l_col_name,'ASSIGNMENT_TYPE','E'
,pa.ass_attribute_category)
, pa.ass_attribute1 = l_ass_attribute1
, pa.ass_attribute2 = l_ass_attribute2
, pa.ass_attribute3 = l_ass_attribute3
, pa.ass_attribute4 = l_ass_attribute4
, pa.ass_attribute5 = l_ass_attribute5
, pa.ass_attribute6 = l_ass_attribute6
, pa.ass_attribute7 = l_ass_attribute7
, pa.ass_attribute8 = l_ass_attribute8
, pa.ass_attribute9 = l_ass_attribute9
, pa.ass_attribute10 = l_ass_attribute10
, pa.ass_attribute11 = l_ass_attribute11
, pa.ass_attribute12 = l_ass_attribute12
, pa.ass_attribute13 = l_ass_attribute13
, pa.ass_attribute14 = l_ass_attribute14
, pa.ass_attribute15 = l_ass_attribute15
, pa.ass_attribute16 = l_ass_attribute16
, pa.ass_attribute17 = l_ass_attribute17
, pa.ass_attribute18 = l_ass_attribute18
, pa.ass_attribute19 = l_ass_attribute19
, pa.ass_attribute20 = l_ass_attribute20
, pa.ass_attribute21 = l_ass_attribute21
, pa.ass_attribute22 = l_ass_attribute22
, pa.ass_attribute23 = l_ass_attribute23
, pa.ass_attribute24 = l_ass_attribute24
, pa.ass_attribute25 = l_ass_attribute25
, pa.ass_attribute26 = l_ass_attribute26
, pa.ass_attribute27 = l_ass_attribute27
, pa.ass_attribute28 = l_ass_attribute28
, pa.ass_attribute29 = l_ass_attribute29
, pa.ass_attribute30 = l_ass_attribute30
, pa.date_probation_end =l_asg_rec.date_probation_end --7120387
where current of ass_cur; -- pa.rowid = p_rowid;
select nvl(src_apl_asg_id,-1) into l_irc_link_exists
from PER_VAC_LINKED_ASSIGNMENTS
where tgt_apl_asg_id = p_assignment_id
and sysdate between nvl(start_date,sysdate) and nvl(end_date,sysdate) ;
select assignment_status_type_id into l_irc_spl_status
from per_assignment_status_types
where PER_SYSTEM_STATUS= 'ACTIVE_ASG_IN_OTH_BG'
and ACTIVE_FLAG ='Y' and DEFAULT_FLAG='Y';
,'INSERT'
,p_start_date
,p_end_of_time);
update per_assignments_f
set effective_end_date = p_start_date -1
where current of ass_cur;
insert into per_assignments_f
(assignment_id
,effective_start_date
,effective_end_date
,business_group_id
,grade_id
,position_id
,job_id
,assignment_status_type_id
,payroll_id
,location_id
,person_id
,organization_id
,people_group_id
,soft_coding_keyflex_id
,vacancy_id
,assignment_sequence
,assignment_type
,manager_flag
,primary_flag
,application_id
,assignment_number
,change_reason
,comment_id
,date_probation_end
,default_code_comb_id
,frequency
,internal_address_line
,normal_hours
,period_of_service_id
,probation_period
,probation_unit
,recruiter_id
,set_of_books_id
,special_ceiling_step_id
,supervisor_id
,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
,last_update_date
,last_updated_by
,last_update_login
,created_by
,creation_date
,pay_basis_id
,person_referred_by_id
,recruitment_activity_id
,source_organization_id
,source_type
,employment_category --columns added Bug 978981
,perf_review_period
,perf_review_period_frequency
,sal_review_period
,sal_review_period_frequency
,bargaining_unit_code
,labour_union_member_flag
,hourly_salaried_code
,title
,job_post_source_name -- added for 4486233
,supervisor_assignment_id) ---#4053244
values
(l_asg_rec.assignment_id
,l_asg_rec.effective_start_date
,p_start_date - 1
,l_asg_rec.business_group_id
,l_asg_rec.grade_id
,l_asg_rec.position_id
,l_asg_rec.job_id
,l_asg_rec.assignment_status_type_id
,l_asg_rec.payroll_id
,l_asg_rec.location_id
,l_asg_rec.person_id
,l_asg_rec.organization_id
,l_asg_rec.people_group_id
,l_asg_rec.soft_coding_keyflex_id
,l_asg_rec.vacancy_id
,l_asg_rec.assignment_sequence
,l_asg_rec.assignment_type
,l_asg_rec.manager_flag
,l_asg_rec.primary_flag
,l_asg_rec.application_id
,l_asg_rec.assignment_number
,l_asg_rec.change_reason
,l_asg_rec.comment_id
,l_asg_rec.date_probation_end
,l_asg_rec.default_code_comb_id
,l_asg_rec.frequency
,l_asg_rec.internal_address_line
,l_asg_rec.normal_hours
,l_asg_rec.period_of_service_id
,l_asg_rec.probation_period
,l_asg_rec.probation_unit
,l_asg_rec.recruiter_id
,l_asg_rec.set_of_books_id
,l_asg_rec.special_ceiling_step_id
,l_asg_rec.supervisor_id
,l_asg_rec.time_normal_finish
,l_asg_rec.time_normal_start
,l_asg_rec.request_id
,l_asg_rec.program_application_id
,l_asg_rec.program_id
,l_asg_rec.program_update_date
,l_asg_rec.ass_attribute_category
,l_asg_rec.ass_attribute1
,l_asg_rec.ass_attribute2
,l_asg_rec.ass_attribute3
,l_asg_rec.ass_attribute4
,l_asg_rec.ass_attribute5
,l_asg_rec.ass_attribute6
,l_asg_rec.ass_attribute7
,l_asg_rec.ass_attribute8
,l_asg_rec.ass_attribute9
,l_asg_rec.ass_attribute10
,l_asg_rec.ass_attribute11
,l_asg_rec.ass_attribute12
,l_asg_rec.ass_attribute13
,l_asg_rec.ass_attribute14
,l_asg_rec.ass_attribute15
,l_asg_rec.ass_attribute16
,l_asg_rec.ass_attribute17
,l_asg_rec.ass_attribute18
,l_asg_rec.ass_attribute19
,l_asg_rec.ass_attribute20
,l_asg_rec.ass_attribute21
,l_asg_rec.ass_attribute22
,l_asg_rec.ass_attribute23
,l_asg_rec.ass_attribute24
,l_asg_rec.ass_attribute25
,l_asg_rec.ass_attribute26
,l_asg_rec.ass_attribute27
,l_asg_rec.ass_attribute28
,l_asg_rec.ass_attribute29
,l_asg_rec.ass_attribute30
,p_current_date
,p_user_id
,p_login_id
,l_asg_rec.created_by
,l_asg_rec.creation_date
,l_asg_rec.pay_basis_id
,l_asg_rec.person_referred_by_id
,l_asg_rec.recruitment_activity_id
,l_asg_rec.source_organization_id
,l_asg_rec.source_type
,l_asg_rec.employment_category -- columns added Bug 978981
,l_asg_rec.perf_review_period
,l_asg_rec.perf_review_period_frequency
,l_asg_rec.sal_review_period
,l_asg_rec.sal_review_period_frequency
,l_asg_rec.bargaining_unit_code
,l_asg_rec.labour_union_member_flag
,l_asg_rec.hourly_salaried_code
,l_asg_rec.title
,l_asg_rec.job_post_source_name -- added for 4486233
,l_asg_rec.supervisor_assignment_id); ---#4053244
,'INSERT'
,p_start_date
,p_end_of_time);
update per_assignments_f pa
set pa.assignment_status_type_id = p_assignment_status_type_id
, pa.assignment_type = 'E'
, pa.effective_start_date = p_start_date
, pa.effective_end_date = p_end_of_time
, pa.period_of_service_id = v_period_of_service_id
, pa.primary_flag = 'N'
, pa.assignment_number = p_assignment_number
, pa.assignment_sequence = p_assignment_sequence
, pa.last_update_date = p_current_date
, pa.last_updated_by = p_user_id
, pa.last_update_login = p_login_id
, pa.set_of_books_id = p_set_of_books_id
, pa.ass_attribute_category = decode(l_col_name,'ASSIGNMENT_TYPE','E'
,pa.ass_attribute_category)
, pa.ass_attribute1 = l_ass_attribute1
, pa.ass_attribute2 = l_ass_attribute2
, pa.ass_attribute3 = l_ass_attribute3
, pa.ass_attribute4 = l_ass_attribute4
, pa.ass_attribute5 = l_ass_attribute5
, pa.ass_attribute6 = l_ass_attribute6
, pa.ass_attribute7 = l_ass_attribute7
, pa.ass_attribute8 = l_ass_attribute8
, pa.ass_attribute9 = l_ass_attribute9
, pa.ass_attribute10 = l_ass_attribute10
, pa.ass_attribute11 = l_ass_attribute11
, pa.ass_attribute12 = l_ass_attribute12
, pa.ass_attribute13 = l_ass_attribute13
, pa.ass_attribute14 = l_ass_attribute14
, pa.ass_attribute15 = l_ass_attribute15
, pa.ass_attribute16 = l_ass_attribute16
, pa.ass_attribute17 = l_ass_attribute17
, pa.ass_attribute18 = l_ass_attribute18
, pa.ass_attribute19 = l_ass_attribute19
, pa.ass_attribute20 = l_ass_attribute20
, pa.ass_attribute21 = l_ass_attribute21
, pa.ass_attribute22 = l_ass_attribute22
, pa.ass_attribute23 = l_ass_attribute23
, pa.ass_attribute24 = l_ass_attribute24
, pa.ass_attribute25 = l_ass_attribute25
, pa.ass_attribute26 = l_ass_attribute26
, pa.ass_attribute27 = l_ass_attribute27
, pa.ass_attribute28 = l_ass_attribute28
, pa.ass_attribute29 = l_ass_attribute29
, pa.ass_attribute30 = l_ass_attribute30
where current of csr_ass_cur_for_primary; -- pa.rowid = p_rowid;
,'INSERT'
,p_start_date
,p_end_of_time);
update per_assignments_f
set effective_end_date = p_start_date -1
where current of csr_ass_cur_for_primary;
insert into per_assignments_f
(assignment_id
,effective_start_date
,effective_end_date
,business_group_id
,grade_id
,position_id
,job_id
,assignment_status_type_id
,payroll_id
,location_id
,person_id
,organization_id
,people_group_id
,soft_coding_keyflex_id
,vacancy_id
,assignment_sequence
,assignment_type
,manager_flag
,primary_flag
,application_id
,assignment_number
,change_reason
,comment_id
,date_probation_end
,default_code_comb_id
,frequency
,internal_address_line
,normal_hours
,period_of_service_id
,probation_period
,probation_unit
,recruiter_id
,set_of_books_id
,special_ceiling_step_id
,supervisor_id
,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
,last_update_date
,last_updated_by
,last_update_login
,created_by
,creation_date
,pay_basis_id
,person_referred_by_id
,recruitment_activity_id
,source_organization_id
,source_type
,employment_category -- columns added Bug 978981
,perf_review_period
,perf_review_period_frequency
,sal_review_period
,sal_review_period_frequency
,bargaining_unit_code
,labour_union_member_flag
,hourly_salaried_code
,title
,job_post_source_name -- added for 4486233
,supervisor_assignment_id) ---#4053244
values
(l_asg_rec.assignment_id
,l_asg_rec.effective_start_date
,p_start_date - 1
,l_asg_rec.business_group_id
,l_asg_rec.grade_id
,l_asg_rec.position_id
,l_asg_rec.job_id
,l_asg_rec.assignment_status_type_id
,l_asg_rec.payroll_id
,l_asg_rec.location_id
,l_asg_rec.person_id
,l_asg_rec.organization_id
,l_asg_rec.people_group_id
,l_asg_rec.soft_coding_keyflex_id
,l_asg_rec.vacancy_id
,l_asg_rec.assignment_sequence
,l_asg_rec.assignment_type
,l_asg_rec.manager_flag
,l_asg_rec.primary_flag
,l_asg_rec.application_id
,l_asg_rec.assignment_number
,l_asg_rec.change_reason
,l_asg_rec.comment_id
,l_asg_rec.date_probation_end
,l_asg_rec.default_code_comb_id
,l_asg_rec.frequency
,l_asg_rec.internal_address_line
,l_asg_rec.normal_hours
,l_asg_rec.period_of_service_id
,l_asg_rec.probation_period
,l_asg_rec.probation_unit
,l_asg_rec.recruiter_id
,l_asg_rec.set_of_books_id
,l_asg_rec.special_ceiling_step_id
,l_asg_rec.supervisor_id
,l_asg_rec.time_normal_finish
,l_asg_rec.time_normal_start
,l_asg_rec.request_id
,l_asg_rec.program_application_id
,l_asg_rec.program_id
,l_asg_rec.program_update_date
,l_asg_rec.ass_attribute_category
,l_asg_rec.ass_attribute1
,l_asg_rec.ass_attribute2
,l_asg_rec.ass_attribute3
,l_asg_rec.ass_attribute4
,l_asg_rec.ass_attribute5
,l_asg_rec.ass_attribute6
,l_asg_rec.ass_attribute7
,l_asg_rec.ass_attribute8
,l_asg_rec.ass_attribute9
,l_asg_rec.ass_attribute10
,l_asg_rec.ass_attribute11
,l_asg_rec.ass_attribute12
,l_asg_rec.ass_attribute13
,l_asg_rec.ass_attribute14
,l_asg_rec.ass_attribute15
,l_asg_rec.ass_attribute16
,l_asg_rec.ass_attribute17
,l_asg_rec.ass_attribute18
,l_asg_rec.ass_attribute19
,l_asg_rec.ass_attribute20
,l_asg_rec.ass_attribute21
,l_asg_rec.ass_attribute22
,l_asg_rec.ass_attribute23
,l_asg_rec.ass_attribute24
,l_asg_rec.ass_attribute25
,l_asg_rec.ass_attribute26
,l_asg_rec.ass_attribute27
,l_asg_rec.ass_attribute28
,l_asg_rec.ass_attribute29
,l_asg_rec.ass_attribute30
,p_current_date
,p_user_id
,p_login_id
,l_asg_rec.created_by
,l_asg_rec.creation_date
,l_asg_rec.pay_basis_id
,l_asg_rec.person_referred_by_id
,l_asg_rec.recruitment_activity_id
,l_asg_rec.source_organization_id
,l_asg_rec.source_type
,l_asg_rec.employment_category -- columns added Bug 978981
,l_asg_rec.perf_review_period
,l_asg_rec.perf_review_period_frequency
,l_asg_rec.sal_review_period
,l_asg_rec.sal_review_period_frequency
,l_asg_rec.bargaining_unit_code
,l_asg_rec.labour_union_member_flag
,l_asg_rec.hourly_salaried_code
,l_asg_rec.title
,l_asg_rec.job_post_source_name -- added for 4486233
,l_asg_rec.supervisor_assignment_id); ---#4053244
,'INSERT'
,p_start_date
,p_end_of_time);
update per_assignments_f pa
set pa.assignment_status_type_id = p_assignment_status_type_id
, pa.assignment_type = 'E'
, pa.effective_start_date = p_start_date
, pa.effective_end_date = p_end_of_time
, pa.period_of_service_id = v_period_of_service_id
, pa.primary_flag = 'N'
, pa.assignment_number = p_assignment_number
, pa.assignment_sequence = p_assignment_sequence
, pa.last_update_date = p_current_date
, pa.last_updated_by = p_user_id
, pa.last_update_login = p_login_id
, pa.set_of_books_id = p_set_of_books_id
, pa.ass_attribute_category = decode(l_col_name,'ASSIGNMENT_TYPE','E'
,pa.ass_attribute_category)
, pa.ass_attribute1 = l_ass_attribute1
, pa.ass_attribute2 = l_ass_attribute2
, pa.ass_attribute3 = l_ass_attribute3
, pa.ass_attribute4 = l_ass_attribute4
, pa.ass_attribute5 = l_ass_attribute5
, pa.ass_attribute6 = l_ass_attribute6
, pa.ass_attribute7 = l_ass_attribute7
, pa.ass_attribute8 = l_ass_attribute8
, pa.ass_attribute9 = l_ass_attribute9
, pa.ass_attribute10 = l_ass_attribute10
, pa.ass_attribute11 = l_ass_attribute11
, pa.ass_attribute12 = l_ass_attribute12
, pa.ass_attribute13 = l_ass_attribute13
, pa.ass_attribute14 = l_ass_attribute14
, pa.ass_attribute15 = l_ass_attribute15
, pa.ass_attribute16 = l_ass_attribute16
, pa.ass_attribute17 = l_ass_attribute17
, pa.ass_attribute18 = l_ass_attribute18
, pa.ass_attribute19 = l_ass_attribute19
, pa.ass_attribute20 = l_ass_attribute20
, pa.ass_attribute21 = l_ass_attribute21
, pa.ass_attribute22 = l_ass_attribute22
, pa.ass_attribute23 = l_ass_attribute23
, pa.ass_attribute24 = l_ass_attribute24
, pa.ass_attribute25 = l_ass_attribute25
, pa.ass_attribute26 = l_ass_attribute26
, pa.ass_attribute27 = l_ass_attribute27
, pa.ass_attribute28 = l_ass_attribute28
, pa.ass_attribute29 = l_ass_attribute29
, pa.ass_attribute30 = l_ass_attribute30
where current of csr_ass_cur_for_nonprimary; -- pa.rowid = p_rowid;
,'INSERT'
,p_start_date
,p_end_of_time);
update per_assignments_f
set effective_end_date = p_start_date -1
where current of csr_ass_cur_for_nonprimary;
select rowid, assignment_id, effective_start_date, effective_end_date
from per_assignments_f
where primary_flag = 'Y'
and business_group_id + 0 = p_business_group_id
and person_id = p_person_id
and assignment_type = 'E'
and effective_start_date > p_start_date
for update of effective_start_date;
,'DELETE'
,l_asg_start_date
,l_asg_end_date);
,'DELETE'
,l_asg_start_date
,l_asg_end_date);
,p_update_primary_flag IN VARCHAR2
,p_employee_number IN VARCHAR2
,p_set_of_books_id IN INTEGER
,p_emp_apl IN VARCHAR2
) is
--
-- Create a new primary assignment.
--
--
p_assignment_number VARCHAR2(30);
select default_context_field_name
from fnd_descriptive_flexs
where application_id = 800 -- bug 5469726
and descriptive_flexfield_name = 'PER_ASSIGNMENTS';
select * from per_all_assignments_f paf
where paf.assignment_id = p_new_primary_id
and paf.business_group_id +0 = p_business_group_id
and p_start_date between paf.effective_start_date
and paf.effective_end_date;
select * from per_all_assignments_f paf
where paf.person_id = p_person_id
and paf.business_group_id +0 = p_business_group_id
-- #1981550
and paf.primary_flag = 'Y'
and paf.assignment_type = 'E'
and p_start_date between paf.effective_start_date
-- #1981550
and paf.effective_end_date;
select pay_proposal_id,object_version_number,proposed_salary_n, change_date
from per_pay_proposals
where assignment_id=ass_id
and approved = 'N'
order by change_date desc;
select pay_proposal_id,object_version_number,proposed_salary_n, change_date
from per_pay_proposals
where assignment_id=ass_id
and approved = 'N'
order by change_date desc;
select 'Y'
from dual
where exists
(SELECT NULL
FROM pay_payroll_actions pac,
pay_assignment_actions act,
per_assignments_f asg
WHERE asg.person_id = p_person_id
AND act.assignment_id = asg.assignment_id
AND pac.payroll_action_id = act.payroll_action_id
AND asg.assignment_id = lp_assignment_id
AND asg.assignment_type = 'E'
AND asg.primary_flag = 'Y'
AND pac.action_status = 'C'
AND ( p_start_date < pac.date_earned
OR p_start_date < pac.effective_date));
select object_version_number
from per_all_assignments_f
where person_id = p_person_id
and assignment_id = lp_assignment_id
and p_start_date between effective_start_date and effective_end_date;
select paaf.effective_start_date
from per_all_assignments_f paaf
where paaf.business_group_id = p_business_group_id
and paaf.person_id = p_person_id
and paaf.primary_flag = 'Y'
and paaf.assignment_type = 'E'
and paaf.assignment_id <> lp_assignment_id
and paaf.effective_start_date > p_start_date
order by paaf.effective_start_date;
select paaf.assignment_number
from per_all_assignments_f paaf
where paaf.business_group_id = p_business_group_id
and paaf.person_id = p_person_id
and paaf.primary_flag = 'Y'
and paaf.assignment_type = 'E'
and paaf.assignment_id = lp_assignment_id
order by paaf.effective_start_date;
SELECT rowid
FROM per_all_assignments_f
WHERE person_id = p_person_id
AND effective_start_date = p_start_date
AND business_group_id = p_business_group_id
AND primary_flag = 'Y'
AND assignment_type = 'E'
AND assignment_id = p_new_primary_id;
SELECT *
FROM per_all_assignments_f
WHERE assignment_type = 'A'
AND person_id = p_person_id
AND business_group_id = p_business_group_id
AND effective_start_date < p_start_date
AND assignment_id = p_new_primary_id;
procedure Delete_future_assignments(p_assignment_id number) is -- added for bug 9644377
--
cursor fut_asg is
select rowid, assignment_id, effective_start_date, effective_end_date
from per_assignments_f
where primary_flag = 'Y'
and business_group_id + 0 = p_business_group_id
and person_id = p_person_id
and assignment_type = 'E'
and effective_start_date > p_start_date
and assignment_id = p_assignment_id -- added for bug 9644377
for update of effective_start_date;
hr_utility.set_location('IN Delete future assignments',201);
delete from per_assignments_f
where rowid = l_rowid;
hr_utility.set_message_token('PROCEDURE','Delete_future_assignments');
hr_utility.set_location('OUT Delete future assignments',203);
end Delete_future_assignments;
if p_update_primary_flag in ('Y','V') then
if emp_asg_rec.payroll_id is not null
and nvl(apl_asg_rec.payroll_id,emp_asg_rec.payroll_id)<> emp_asg_rec.payroll_id then
open csr_pay_actions(emp_asg_rec.assignment_id);
if p_update_primary_flag not in ('Y','V') then
if p_emp_apl = 'Y' then
begin
-- #1769702
-- Start changes for bug 9644377
open cur_get_future_asgs(emp_asg_rec.assignment_id);
Delete_future_assignments(p_assignment_id => emp_asg_rec.assignment_id);
update per_assignments_f pa
set -- pa.primary_flag = 'N', Changed for bug 9644377
pa.effective_start_date =p_start_date
, pa.effective_end_date = p_end_of_time
, pa.last_update_login = p_login_id
, pa.last_updated_by = p_user_id
, pa.last_update_date = p_start_date
where pa.primary_flag = 'Y'
and pa.business_group_id + 0 = p_business_group_id
and pa.person_id = p_person_id
and p_start_date between pa.effective_start_date
and pa.effective_end_date;
,'INSERT'
,p_start_date
,p_end_of_time);
/* update per_all_assignments_f pa
set pa.primary_flag = 'Y'
, pa.effective_start_date = p_start_date
, pa.effective_end_date = p_end_of_time
, pa.set_of_books_id = emp_asg_rec.set_of_books_id
, pa.change_reason = null
where pa.business_group_id + 0 = p_business_group_id
and pa.assignment_id = p_new_primary_id
and p_start_date between pa.effective_start_date
and pa.effective_end_date;
if p_update_primary_flag = 'C' and p_emp_apl = 'Y' then
update per_all_assignments_f pa
set pa.effective_start_date = p_start_date
, pa.effective_end_date = p_end_of_time
, pa.set_of_books_id = emp_asg_rec.set_of_books_id
, pa.change_reason = null
where pa.business_group_id + 0 = p_business_group_id
and pa.assignment_id = p_new_primary_id
and p_start_date between pa.effective_start_date and pa.effective_end_date;
update per_all_assignments_f pa
set pa.primary_flag = 'Y'
, pa.effective_start_date = p_start_date
, pa.effective_end_date = p_end_of_time
, pa.set_of_books_id = emp_asg_rec.set_of_books_id
, pa.change_reason = null
where pa.business_group_id + 0 = p_business_group_id
and pa.assignment_id = p_new_primary_id
and p_start_date between pa.effective_start_date
and pa.effective_end_date;
,'INSERT'
,p_start_date
,p_end_of_time);
select projected_hire_date
from per_applications
where application_id =l_appl_id;
select assignment_id
, rowid
, effective_end_date -- #1769702
from per_assignments_f pa
where pa.primary_flag='Y'
and pa.person_id = p_person_id
and pa.business_group_id + 0 = p_business_group_id
and p_start_date between pa.effective_start_date
and pa.effective_end_date
for update of pa.organization_id;
select assignment_type
from per_all_assignments_f paf
where paf.assignment_id = p_old_assignment_id;
select assignment_id
, rowid
, effective_start_date
, effective_end_date
from per_assignments_f pa
where pa.primary_flag = 'Y'
and pa.business_group_id + 0 = p_business_group_id
and pa.person_id = p_person_id
and pa.assignment_type = 'E'
and pa.effective_start_date > p_start_date
for update of pa.organization_id;
select *
from per_assignments_f a
where a.rowid = P_ROW_ID;
if p_update_primary_flag = 'Y' then
hr_utility.set_location('primary_flag = Y',480);
update per_assignments_f pa
set pa.organization_id = apl_asg_rec.organization_id
,pa.effective_start_date = p_start_date
--,pa.effective_end_date = p_end_of_time -- commented for bug 9644377
,pa.recruiter_id = apl_asg_rec.recruiter_id
,pa.grade_id = apl_asg_rec.grade_id
,pa.position_id = apl_asg_rec.position_id
,pa.job_id = apl_asg_rec.job_id
,pa.payroll_id = apl_asg_rec.payroll_id
,pa.location_id = apl_asg_rec.location_id
,pa.person_referred_by_id = apl_asg_rec.person_referred_by_id
,pa.supervisor_id = apl_asg_rec.supervisor_id
,pa.supervisor_assignment_id = apl_asg_rec.supervisor_assignment_id -- #4053244
,pa.special_ceiling_step_id = apl_asg_rec.special_ceiling_step_id
,pa.recruitment_activity_id = apl_asg_rec.recruitment_activity_id
,pa.source_organization_id = apl_asg_rec.source_organization_id
,pa.people_group_id = apl_asg_rec.people_group_id
,pa.soft_coding_keyflex_id = apl_asg_rec.soft_coding_keyflex_id
,pa.vacancy_id = apl_asg_rec.vacancy_id
,pa.application_id = apl_asg_rec.application_id
,pa.comment_id = apl_asg_rec.comment_id
,pa.date_probation_end = apl_asg_rec.date_probation_end
,pa.default_code_comb_id = apl_asg_rec.default_code_comb_id
,pa.employment_category = apl_asg_rec.employment_category
,pa.frequency = apl_asg_rec.frequency
,pa.internal_address_line = apl_asg_rec.internal_address_line
,pa.manager_flag = apl_asg_rec.manager_flag
,pa.normal_hours = apl_asg_rec.normal_hours
,pa.probation_period = apl_asg_rec.probation_period
,pa.probation_unit = apl_asg_rec.probation_unit
,pa.set_of_books_id = p_set_of_books_id
,pa.source_type = apl_asg_rec.source_type
,pa.time_normal_finish = apl_asg_rec.time_normal_finish
,pa.time_normal_start = apl_asg_rec.time_normal_start
,pa.pay_basis_id = apl_asg_rec.pay_basis_id
,pa.ass_attribute_category = decode(l_col_name,'ASSIGNMENT_TYPE','E',pa.ass_attribute_category)
,pa.ass_attribute1 = apl_asg_rec.ass_attribute1
,pa.ass_attribute2 = apl_asg_rec.ass_attribute2
,pa.ass_attribute3 = apl_asg_rec.ass_attribute3
,pa.ass_attribute4 = apl_asg_rec.ass_attribute4
,pa.ass_attribute5 = apl_asg_rec.ass_attribute5
,pa.ass_attribute6 = apl_asg_rec.ass_attribute6
,pa.ass_attribute7 = apl_asg_rec.ass_attribute7
,pa.ass_attribute8 = apl_asg_rec.ass_attribute8
,pa.ass_attribute9 = apl_asg_rec.ass_attribute9
,pa.ass_attribute10 = apl_asg_rec.ass_attribute10
,pa.ass_attribute11 = apl_asg_rec.ass_attribute11
,pa.ass_attribute12 = apl_asg_rec.ass_attribute12
,pa.ass_attribute13 = apl_asg_rec.ass_attribute13
,pa.ass_attribute14 = apl_asg_rec.ass_attribute14
,pa.ass_attribute15 = apl_asg_rec.ass_attribute15
,pa.ass_attribute16 = apl_asg_rec.ass_attribute16
,pa.ass_attribute17 = apl_asg_rec.ass_attribute17
,pa.ass_attribute18 = apl_asg_rec.ass_attribute18
,pa.ass_attribute19 = apl_asg_rec.ass_attribute19
,pa.ass_attribute20 = apl_asg_rec.ass_attribute20
,pa.ass_attribute21 = apl_asg_rec.ass_attribute21
,pa.ass_attribute22 = apl_asg_rec.ass_attribute22
,pa.ass_attribute23 = apl_asg_rec.ass_attribute23
,pa.ass_attribute24 = apl_asg_rec.ass_attribute24
,pa.ass_attribute25 = apl_asg_rec.ass_attribute25
,pa.ass_attribute26 = apl_asg_rec.ass_attribute26
,pa.ass_attribute27 = apl_asg_rec.ass_attribute27
,pa.ass_attribute28 = apl_asg_rec.ass_attribute28
,pa.ass_attribute29 = apl_asg_rec.ass_attribute29
,pa.ass_attribute30 = apl_asg_rec.ass_attribute30
,pa.GRADE_LADDER_PGM_ID=apl_asg_rec.GRADE_LADDER_PGM_ID-- added for bug 5513751
,pa.EMPLOYEE_CATEGORY=apl_asg_rec.EMPLOYEE_CATEGORY--added for bug 5513751
,pa.COLLECTIVE_AGREEMENT_id=apl_asg_rec.COLLECTIVE_AGREEMENT_id-- added for bug 5513751
where pa.rowid = p_rowid;
select nvl(src_apl_asg_id,-1) into l_irc_link_exists
from PER_VAC_LINKED_ASSIGNMENTS
where tgt_apl_asg_id = emp_asg_rec.assignment_id
and sysdate between nvl(start_date,sysdate) and nvl(end_date,sysdate) ;
select assignment_status_type_id into l_irc_spl_status
from per_assignment_status_types
where PER_SYSTEM_STATUS= 'ACTIVE_ASG_IN_OTH_BG'
and ACTIVE_FLAG ='Y' and DEFAULT_FLAG='Y';
hr_utility.set_location('Updated EMP assignment',11);
Delete_future_assignments(p_assignment_id => emp_asg_rec.assignment_id); -- modified for bug 9644377
elsif p_update_primary_flag = 'V' then
hr_utility.set_location('Overwrite EMP assignment',15);
hr_utility.set_location('Updated EMP assignment',90);
update per_assignments_f pa
set pa.organization_id = emp_asg_rec.organization_id
,pa.effective_start_date = p_start_date
--,pa.effective_end_date = p_end_of_time -- #1769702 what if future asg exist?
,pa.recruiter_id = emp_asg_rec.recruiter_id
,pa.grade_id = emp_asg_rec.grade_id
,pa.position_id = emp_asg_rec.position_id
,pa.job_id = emp_asg_rec.job_id
,pa.payroll_id = emp_asg_rec.payroll_id
,pa.location_id = emp_asg_rec.location_id
,pa.person_referred_by_id = emp_asg_rec.person_referred_by_id
,pa.supervisor_id = emp_asg_rec.supervisor_id
,pa.supervisor_assignment_id = emp_asg_rec.supervisor_assignment_id -- #4053244
,pa.special_ceiling_step_id = emp_asg_rec.special_ceiling_step_id
,pa.recruitment_activity_id = emp_asg_rec.recruitment_activity_id
,pa.source_organization_id = emp_asg_rec.source_organization_id
,pa.people_group_id = emp_asg_rec.people_group_id
,pa.soft_coding_keyflex_id = emp_asg_rec.soft_coding_keyflex_id
,pa.vacancy_id = emp_asg_rec.vacancy_id
,pa.application_id = emp_asg_rec.application_id
,pa.comment_id = emp_asg_rec.comment_id
,pa.date_probation_end = emp_asg_rec.date_probation_end
,pa.default_code_comb_id = emp_asg_rec.default_code_comb_id
,pa.employment_category = emp_asg_rec.employment_category
,pa.frequency = emp_asg_rec.frequency
,pa.internal_address_line = emp_asg_rec.internal_address_line
,pa.manager_flag = emp_asg_rec.manager_flag
,pa.normal_hours = emp_asg_rec.normal_hours
,pa.probation_period = emp_asg_rec.probation_period
,pa.probation_unit = emp_asg_rec.probation_unit
,pa.set_of_books_id = emp_asg_rec.set_of_books_id
,pa.source_type = emp_asg_rec.source_type
,pa.time_normal_finish = emp_asg_rec.time_normal_finish
,pa.time_normal_start = emp_asg_rec.time_normal_start
,pa.pay_basis_id = emp_asg_rec.pay_basis_id
,pa.ass_attribute_category = decode(l_col_name,'ASSIGNMENT_TYPE','E',pa.ass_attribute_category)
,pa.ass_attribute1 = emp_asg_rec.ass_attribute1
,pa.ass_attribute2 = emp_asg_rec.ass_attribute2
,pa.ass_attribute3 = emp_asg_rec.ass_attribute3
,pa.ass_attribute4 = emp_asg_rec.ass_attribute4
,pa.ass_attribute5 = emp_asg_rec.ass_attribute5
,pa.ass_attribute6 = emp_asg_rec.ass_attribute6
,pa.ass_attribute7 = emp_asg_rec.ass_attribute7
,pa.ass_attribute8 = emp_asg_rec.ass_attribute8
,pa.ass_attribute9 = emp_asg_rec.ass_attribute9
,pa.ass_attribute10 = emp_asg_rec.ass_attribute10
,pa.ass_attribute11 = emp_asg_rec.ass_attribute11
,pa.ass_attribute12 = emp_asg_rec.ass_attribute12
,pa.ass_attribute13 = emp_asg_rec.ass_attribute13
,pa.ass_attribute14 = emp_asg_rec.ass_attribute14
,pa.ass_attribute15 = emp_asg_rec.ass_attribute15
,pa.ass_attribute16 = emp_asg_rec.ass_attribute16
,pa.ass_attribute17 = emp_asg_rec.ass_attribute17
,pa.ass_attribute18 = emp_asg_rec.ass_attribute18
,pa.ass_attribute19 = emp_asg_rec.ass_attribute19
,pa.ass_attribute20 = emp_asg_rec.ass_attribute20
,pa.ass_attribute21 = emp_asg_rec.ass_attribute21
,pa.ass_attribute22 = emp_asg_rec.ass_attribute22
,pa.ass_attribute23 = emp_asg_rec.ass_attribute23
,pa.ass_attribute24 = emp_asg_rec.ass_attribute24
,pa.ass_attribute25 = emp_asg_rec.ass_attribute25
,pa.ass_attribute26 = emp_asg_rec.ass_attribute26
,pa.ass_attribute27 = emp_asg_rec.ass_attribute27
,pa.ass_attribute28 = emp_asg_rec.ass_attribute28
,pa.ass_attribute29 = emp_asg_rec.ass_attribute29
,pa.ass_attribute30 = emp_asg_rec.ass_attribute30
,pa.GRADE_LADDER_PGM_ID= emp_asg_rec.GRADE_LADDER_PGM_ID -- 5513751
,pa.EMPLOYEE_CATEGORY= emp_asg_rec.EMPLOYEE_CATEGORY -- 5513751
,pa.COLLECTIVE_AGREEMENT_id= emp_asg_rec.COLLECTIVE_AGREEMENT_id -- 5513751
where pa.rowid = p_rowid;
update per_assignments_f pa
set pa.organization_id = emp_asg_rec.organization_id
,pa.recruiter_id = emp_asg_rec.recruiter_id
,pa.grade_id = emp_asg_rec.grade_id
,pa.position_id = emp_asg_rec.position_id
,pa.job_id = emp_asg_rec.job_id
,pa.payroll_id = emp_asg_rec.payroll_id
,pa.location_id = emp_asg_rec.location_id
,pa.person_referred_by_id = emp_asg_rec.person_referred_by_id
,pa.supervisor_id = emp_asg_rec.supervisor_id
,pa.supervisor_assignment_id = emp_asg_rec.supervisor_assignment_id -- #4053244
,pa.special_ceiling_step_id = emp_asg_rec.special_ceiling_step_id
,pa.recruitment_activity_id = emp_asg_rec.recruitment_activity_id
,pa.source_organization_id = emp_asg_rec.source_organization_id
,pa.people_group_id = emp_asg_rec.people_group_id
,pa.soft_coding_keyflex_id = emp_asg_rec.soft_coding_keyflex_id
,pa.vacancy_id = emp_asg_rec.vacancy_id
,pa.application_id = emp_asg_rec.application_id
,pa.comment_id = emp_asg_rec.comment_id
,pa.date_probation_end = emp_asg_rec.date_probation_end
,pa.default_code_comb_id = emp_asg_rec.default_code_comb_id
,pa.employment_category = emp_asg_rec.employment_category
,pa.frequency = emp_asg_rec.frequency
,pa.internal_address_line = emp_asg_rec.internal_address_line
,pa.manager_flag = emp_asg_rec.manager_flag
,pa.normal_hours = emp_asg_rec.normal_hours
,pa.probation_period = emp_asg_rec.probation_period
,pa.probation_unit = emp_asg_rec.probation_unit
,pa.set_of_books_id = emp_asg_rec.set_of_books_id
,pa.source_type = emp_asg_rec.source_type
,pa.time_normal_finish = emp_asg_rec.time_normal_finish
,pa.time_normal_start = emp_asg_rec.time_normal_start
,pa.pay_basis_id = emp_asg_rec.pay_basis_id
,pa.ass_attribute_category = decode(l_col_name,'ASSIGNMENT_TYPE','E',pa.ass_attribute_category)
,pa.ass_attribute1 = emp_asg_rec.ass_attribute1
,pa.ass_attribute2 = emp_asg_rec.ass_attribute2
,pa.ass_attribute3 = emp_asg_rec.ass_attribute3
,pa.ass_attribute4 = emp_asg_rec.ass_attribute4
,pa.ass_attribute5 = emp_asg_rec.ass_attribute5
,pa.ass_attribute6 = emp_asg_rec.ass_attribute6
,pa.ass_attribute7 = emp_asg_rec.ass_attribute7
,pa.ass_attribute8 = emp_asg_rec.ass_attribute8
,pa.ass_attribute9 = emp_asg_rec.ass_attribute9
,pa.ass_attribute10 = emp_asg_rec.ass_attribute10
,pa.ass_attribute11 = emp_asg_rec.ass_attribute11
,pa.ass_attribute12 = emp_asg_rec.ass_attribute12
,pa.ass_attribute13 = emp_asg_rec.ass_attribute13
,pa.ass_attribute14 = emp_asg_rec.ass_attribute14
,pa.ass_attribute15 = emp_asg_rec.ass_attribute15
,pa.ass_attribute16 = emp_asg_rec.ass_attribute16
,pa.ass_attribute17 = emp_asg_rec.ass_attribute17
,pa.ass_attribute18 = emp_asg_rec.ass_attribute18
,pa.ass_attribute19 = emp_asg_rec.ass_attribute19
,pa.ass_attribute20 = emp_asg_rec.ass_attribute20
,pa.ass_attribute21 = emp_asg_rec.ass_attribute21
,pa.ass_attribute22 = emp_asg_rec.ass_attribute22
,pa.ass_attribute23 = emp_asg_rec.ass_attribute23
,pa.ass_attribute24 = emp_asg_rec.ass_attribute24
,pa.ass_attribute25 = emp_asg_rec.ass_attribute25
,pa.ass_attribute26 = emp_asg_rec.ass_attribute26
,pa.ass_attribute27 = emp_asg_rec.ass_attribute27
,pa.ass_attribute28 = emp_asg_rec.ass_attribute28
,pa.ass_attribute29 = emp_asg_rec.ass_attribute29
,pa.ass_attribute30 = emp_asg_rec.ass_attribute30
,pa.GRADE_LADDER_PGM_ID= emp_asg_rec.GRADE_LADDER_PGM_ID -- 5513751
,pa.EMPLOYEE_CATEGORY= emp_asg_rec.EMPLOYEE_CATEGORY -- 5513751
,pa.COLLECTIVE_AGREEMENT_id= emp_asg_rec.COLLECTIVE_AGREEMENT_id -- 5513751
where pa.rowid = p_rowid;
,'INSERT'
,l_fut_start_date
,l_fut_end_date);
,'INSERT'
,l_fut_start_date
,l_fut_end_date);
,p_mode => 'UPDATE'
,p_location_id => emp_asg_rec.location_id
,p_return_code => l_return_code
,p_return_text => l_return_text
);
p_datetrack_mode => hr_api.g_update,
p_validation_start_date => p_start_date,
p_validation_end_date => p_end_of_time,
p_grade_id => apl_asg_rec.grade_id,
p_spp_delete_warning => l_delete_warn);
,'INSERT'
,p_start_date
,l_asg_end_date); -- #1769702
,'INSERT'
,p_start_date
,l_asg_end_date); -- #1769702
,p_mode => 'UPDATE'
,p_location_id => emp_asg_rec.location_id
,p_return_code => l_return_code
,p_return_text => l_return_text
);
select pa.rowid
from per_assignments_f pa
where pa.assignment_id = p_new_primary_id
and p_start_date between pa.effective_start_date
and pa.effective_end_date
for update of pa.effective_end_date;
hr_utility.trace(' Update APL asg id => '||to_char(p_new_primary_id));
update per_assignments_f
set effective_end_date = p_start_date - 1
where rowid = p_rowid;
hr_assignment.update_assgn_context_value (p_business_group_id,
p_person_id,
p_assignment_id,
p_start_date);
if p_update_primary_flag in ('Y','C','V') then
--
hr_utility.set_location('hr_person.employ_applicant',2);
update_primary_assignment(p_business_group_id
,p_person_id
,p_start_date
,p_current_date
,p_user_id
,p_login_id
);
,p_update_primary_flag
,p_new_primary_id
,p_user_id
,p_login_id
,p_start_date
,p_end_of_time
,p_employee_number
,p_set_of_books_id
,p_current_date);
,p_update_primary_flag
,p_employee_number
,p_set_of_books_id
,p_emp_apl
);
if l_proposal_exists ='Y' and p_update_primary_flag = 'Y' then
open get_primary_approved_proposal(p_new_primary_id);
else -- update_primary_flag = 'N'
--
hr_utility.set_location('hr_person.employ_applicant',5);
,p_update_primary_flag
,p_new_primary_id
,p_user_id
,p_login_id
,p_start_date
,p_end_of_time
,p_employee_number
,p_set_of_books_id
,p_current_date);
else -- employing an applicant; update_primary_flag = 'N'
,p_update_primary_flag
,p_new_primary_id
,p_user_id
,p_login_id
,p_start_date
,p_end_of_time
,p_employee_number
,p_set_of_books_id
,p_current_date);
,p_update_primary_flag
,p_employee_number
,p_set_of_books_id
,p_emp_apl
);
if per_otherbg_apl_api.isMultiRegVac(p_new_primary_id) and p_update_primary_flag in ('Y','V') then
-- if per_otherbg_apl_api.isMultiRegVac(p_new_primary_id) then
per_otherbg_apl_api.close_otherbg_applications(p_new_primary_id,p_start_date-1,'HIRE_CLOSE');
cursor asg_cur is select *
from per_all_assignments_f
where primary_flag ='Y'
and assignment_type = 'E'
and person_id = p_person_id
and business_group_id + 0 = p_business_group_id
and p_start_date between effective_start_date
and effective_end_date;
p_action => 'INSERT');
cursor asg_cur is select *
from per_all_assignments_f
where
assignment_type = 'E'
and person_id = p_person_id
and business_group_id + 0 = p_business_group_id
and p_start_date between effective_start_date
and effective_end_date;
select projected_hire_date
from per_applications
where application_id =l_appl_id;
update per_all_assignments_f
set date_probation_end =l_date_probation_end
where
assignment_type = 'E'
and person_id = p_person_id
and business_group_id + 0 = p_business_group_id
and p_start_date between effective_start_date
and effective_end_date
and assignment_id = l_asg_probation_det.assignment_id;
select pa.assignment_id
, pa.effective_start_date
from per_all_assignments_f pa
, per_assignment_status_types past
where nvl(past.business_group_id,p_business_group_id) = pa.business_group_id + 0
and pa.person_id = p_person_id
and pa.business_group_id + 0 = p_business_group_id
and
(( p_apl = 'Y'
and nvl(past.legislation_code, p_legislation_code) = p_legislation_code
and past.per_system_status = 'ACCEPTED'
and pa.assignment_type = 'A'
and past.assignment_status_type_id = pa.assignment_status_type_id
and p_start_date between pa.effective_start_date
and pa.effective_end_date
)
--if this is a current employee, no need to check legislation code,or
--system status.
or
(p_emp = 'Y'
and pa.assignment_type = 'E'
and p_start_date between pa.effective_start_date
and pa.effective_end_date
and past.assignment_status_type_id = pa.assignment_status_type_id));
select 1
from per_periods_of_service pps
where pps.person_id =p_person_id
and date_start <= (select effective_date
from fnd_sessions
where session_id =
userenv('sessionid'));
select 1
from per_periods_of_service pps
where pps.person_id =p_person_id;
select pa.assignment_id
from per_assignments_f pa
where pa.person_id = p_person_id
and pa.business_group_id + 0 = p_business_group_id
and pa.assignment_type = 'A'
and p_end_date between pa.effective_start_date
and pa.effective_end_date -- fix for bug 6036285
and pa.assignment_status_type_id IN (
select past.assignment_status_type_id
from per_assignment_status_types past
, per_ass_status_type_amends pasa
where pasa.assignment_status_type_id(+)=
past.assignment_status_type_id
and pasa.business_group_id(+) + 0 = p_business_group_id
and nvl(past.business_group_id,p_business_group_id) =
p_business_group_id
and nvl(past.legislation_code, p_legislation_code) =
p_legislation_code
and nvl(pasa.per_system_status,past.per_system_status) <>
'ACCEPTED'
)
for update of pa.effective_end_date;
update per_assignments pa
set pa.effective_end_date = l_end_date
where current of unacc_cur;
select pe.event_id
from per_events pe
, per_assignments_f a
where pe.business_group_id +0 = a.business_group_id
and a.business_group_id = p_business_group_id
and pe.assignment_id = a.assignment_id
and pe.date_start >=p_start_date
and a.person_id = p_person_id
and p_start_date between a.effective_start_date
and a.effective_end_date
and pe.event_or_interview = 'E'
for update of event_id;
select booking_id
from per_bookings pb
where pb.event_id = l_event_id
for update of booking_id;
delete from per_bookings pb
where pb.event_id = l_event_id;
delete from per_events
where event_id = l_event_id;