[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
procedure update_pgp_concat_segs
(p_people_group_id in number
,p_group_name in varchar2
) is
--
CURSOR csr_chk_pgp is
SELECT null
FROM pay_people_groups
where people_group_id = p_people_group_id
and (group_name <> p_group_name
or group_name is null);
l_proc varchar2(72) := g_package||'update_pgp_concat_segs';
procedure update_pgp_concat_segs_auto
(p_people_group_id in number
,p_group_name in varchar2
) is
PRAGMA AUTONOMOUS_TRANSACTION;
SELECT null
FROM pay_people_groups
where people_group_id = p_people_group_id
for update nowait;
l_proc varchar2(72) := g_package||'update_pgp_concat_segs_auto';
update pay_people_groups
--set group_name = p_group_name fix for Bug#7601790
set group_name = l_group_name1
where people_group_id = p_people_group_id
and (group_name <> p_group_name
or group_name is null);
end update_pgp_concat_segs_auto;
update_pgp_concat_segs_auto
(p_people_group_id => p_people_group_id
,p_group_name => p_group_name
);
end update_pgp_concat_segs;
select date_end ,date_received, application_id
from per_applications papp
where application_id =
( select distinct (application_id)
from per_all_assignments_f
where assignment_id = p_assignment_id and
business_group_id = p_business_group_id )
for update nowait;
select 1
from per_all_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 1
FROM PER_PERSON_TYPE_USAGES_F PTU, PER_PERSON_TYPES PPT
WHERE PTU.PERSON_ID = P_PERSON_ID
AND PTU.EFFECTIVE_START_DATE > p_DATE_END
AND PTU.PERSON_TYPE_ID = PPT.PERSON_TYPE_ID
AND PPT.SYSTEM_PERSON_TYPE IN ('EMP','CWK');
select ptu.person_type_usage_id,
ptu.effective_start_date
from per_person_type_usages_f ptu
,per_person_types ppt
where ptu.person_id = p_person_id
and ptu.effective_start_date > p_date_received
and ptu.person_type_id = ppt.person_type_id
and ppt.system_person_type = 'EX_APL'
order by ptu.effective_start_date;
select 1
from per_business_groups
where business_group_id = p_business_group_id;
select 1
from per_all_people_f
where business_group_id = p_business_group_id
and person_id = p_person_id
and p_effective_date between effective_start_date and effective_end_date;
select 1
from per_all_assignments_f
where business_group_id = p_business_group_id
and person_id = p_person_id
and assignment_id = p_assignment_id;
select rowid, application_id,date_received from per_applications
where date_received > p_date_received
and person_id = p_person_id;
select nvl(max(effective_end_date),hr_api.g_eot)
from per_person_type_usages_f ptu, per_person_types ppt
where ptu.person_type_id = ppt.person_type_id
and ppt.system_person_type = 'APL'
and ptu.effective_start_date > p_date_received;
select effective_start_date, effective_end_date,object_version_number into l_asg_start_date,l_asg_end_date,l_ovn
from per_all_assignments_f
where assignment_id = p_assignment_id
and effective_end_date = (select max(effective_end_date)
from per_all_assignments_f
where assignment_id = p_assignment_id );
select assignment_status_type_id into l_assignment_status_id
from per_all_assignments_f
where assignment_id = p_assignment_id
and effective_end_date = l_asg_end_date ;
update per_all_assignments_f
set effective_end_date = l_eot
where assignment_id = p_assignment_id
and person_id=p_person_id
and business_group_id= p_business_group_id
and effective_end_date = l_asg_end_date;
DELETE FROM per_all_people_f papf
WHERE papf.person_id = p_person_id
AND papf.business_group_id + 0 = p_Business_group_id
AND papf.effective_start_date > l_date_end;
UPDATE per_all_people_f papf
SET papf.effective_end_date = l_eot
WHERE papf.person_id = p_person_id
AND papf.BUSINESS_GROUP_ID + 0 = p_Business_group_id
AND papf.effective_end_date = l_date_end;
update per_person_type_usages_f
set effective_end_date = l_max_ptu_end_date
where l_date_recieved between effective_start_date and effective_end_date
and person_type_id = l_apl_person_type_id;
delete from per_person_type_usages_f
where effective_start_date > l_date_recieved
and person_type_id = l_apl_person_type_id;
UPDATE PER_APPLICATIONS
SET date_end =null
where APPLICATION_ID =l_application_id
and person_id= p_person_id ;
-- update the future asgs with the application ID
for apl in future_apl(l_date_recieved)
loop
update per_all_assignments_f
set application_id = l_application_id
where person_id = p_person_id
and application_id =apl.application_id
and effective_start_date >= apl.date_received
and assignment_type = 'A';
per_applications_pkg.delete_row(apl.rowid);
update per_all_assignments_f
set effective_end_date = l_eot
where assignment_id = p_assignment_id
and person_id=p_person_id
and business_group_id= p_business_group_id
and effective_end_date = l_asg_end_date;
select date_end ,date_received, application_id
from per_applications papp
where application_id =
( select distinct (application_id)
from per_all_assignments_f
where assignment_id = p_assignment_id and
business_group_id = p_business_group_id )
for update nowait;
select 1
from per_all_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 1
FROM PER_ALL_PEOPLE_F PAPF
WHERE PAPF.PERSON_ID = P_PERSON_ID
AND PAPF.EFFECTIVE_START_DATE > p_DATE_END + 1 ;
select ptu.effective_start_date
from per_person_type_usages_f ptu
,per_person_types ppt
where ptu.person_id = p_person_id
and ptu.effective_start_date > p_date_received
and ptu.person_type_id = ppt.person_type_id
and ppt.system_person_type = 'EX_APL'
order by ptu.effective_start_date;
select 1
from per_business_groups
where business_group_id = p_business_group_id;
select 1
from per_all_people_f
where business_group_id = p_business_group_id
and person_id = p_person_id
and p_effective_date between effective_start_date and effective_end_date;
select 1
from per_all_assignments_f
where business_group_id = p_business_group_id
and person_id = p_person_id
and assignment_id = p_assignment_id;
select effective_start_date, effective_end_date,object_version_number into l_asg_start_date,l_asg_end_date,l_ovn
from per_all_assignments_f
where assignment_id = p_assignment_id
and effective_end_date = (select max(effective_end_date)
from per_all_assignments_f
where assignment_id = p_assignment_id );
select assignment_status_type_id into l_assignment_status_id
from per_all_assignments_f
where assignment_id = p_assignment_id
and effective_end_date = l_asg_end_date ;
update per_all_assignments_f
set effective_end_date = l_eot
where assignment_id = p_assignment_id
and person_id=p_person_id
and business_group_id= p_business_group_id
and effective_end_date = l_asg_end_date;
if (nvl(fnd_profile.value('HR_ALLOW_FPT_UPDATES'),'N') = 'Y') then
hr_utility.set_location('Calling reverse_term_apln_fut', 105);
DELETE FROM per_all_people_f papf
WHERE papf.person_id = p_person_id
AND papf.business_group_id + 0 = p_Business_group_id
AND papf.effective_start_date = l_date_end + 1;
UPDATE per_all_people_f papf
SET papf.effective_end_date = l_eot
WHERE papf.person_id = p_person_id
AND papf.BUSINESS_GROUP_ID + 0 = p_Business_group_id
AND papf.effective_end_date = l_date_end;
UPDATE PER_APPLICATIONS
SET date_end =null
where APPLICATION_ID =l_application_id
and person_id= p_person_id ;
update per_all_assignments_f
set effective_end_date = l_eot
where assignment_id = p_assignment_id
and person_id=p_person_id
and business_group_id= p_business_group_id
and effective_end_date = l_asg_end_date;
procedure update_scl_concat_segs
(p_soft_coding_keyflex_id in number
,p_concatenated_segments in varchar2
) is
--
--
CURSOR csr_chk_scl is
SELECT null
FROM hr_soft_coding_keyflex
where soft_coding_keyflex_id = p_soft_coding_keyflex_id
and (concatenated_segments <> p_concatenated_segments
or concatenated_segments is null);
l_proc varchar2(72) := g_package||'update_scl_concat_segs ';
procedure update_scl_concat_segs_auto
( p_soft_coding_keyflex_id in number
,p_concatenated_segments in varchar2
) is
PRAGMA AUTONOMOUS_TRANSACTION;
SELECT null
FROM hr_soft_coding_keyflex
where soft_coding_keyflex_id = p_soft_coding_keyflex_id
for update nowait;
l_proc varchar2(72) := g_package||'update_scl_concat_segs_auto';
update hr_soft_coding_keyflex
set concatenated_segments = p_concatenated_segments
where soft_coding_keyflex_id = p_soft_coding_keyflex_id
and (concatenated_segments <> p_concatenated_segments
or concatenated_segments is null);
end update_scl_concat_segs_auto;
update_scl_concat_segs_auto
(p_soft_coding_keyflex_id => p_soft_coding_keyflex_id
,p_concatenated_segments => p_concatenated_segments
);
end update_scl_concat_segs;
select asg.soft_coding_keyflex_id
from per_all_assignments_f asg
where asg.assignment_id = p_assignment_id
and p_effective_date between asg.effective_start_date
and asg.effective_end_date;
select plr.rule_mode id_flex_num
from pay_legislation_rules plr,
per_business_groups_perf pgr
where plr.legislation_code = pgr.legislation_code
and pgr.business_group_id = p_business_group_id
and plr.rule_type = 'S'
and exists
(select 1
from fnd_segment_attribute_values fsav
where to_char(fsav.id_flex_num) = plr.rule_mode -- Fix For Bug # 12813119
and fsav.application_id = 800
and fsav.id_flex_code = 'SCL'
and fsav.segment_attribute_type = 'ASSIGNMENT'
and fsav.attribute_value = 'Y')
and exists
(select 1
from pay_legislation_rules plr2
where plr2.legislation_code = plr.legislation_code
and plr2.rule_type = 'SDL'
and plr2.rule_mode = 'A') ;
select concatenated_segments,
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 hr_soft_coding_keyflex
where soft_coding_keyflex_id = l_soft_coding_keyflex_id;
update_scl_concat_segs
(p_soft_coding_keyflex_id => l_soft_coding_keyflex_id
,p_concatenated_segments => l_concatenated_segments
);
SELECT as2.assignment_id
FROM per_all_assignments_f as2
,per_all_assignments_f as1
WHERE as2.person_id = as1.person_id
AND as2.assignment_type = as1.assignment_type
AND csr_last_apl_asg.p_effective_date BETWEEN as2.effective_start_date
AND as2.effective_end_date
AND as2.assignment_id <> as1.assignment_id
AND as1.assignment_id = csr_last_apl_asg.p_assignment_id;
,p_datetrack_update_mode in varchar2
,p_assignment_id in number
,p_change_reason in varchar2
,p_object_version_number in out nocopy number
,p_assignment_status_type_id in number
,p_effective_start_date out nocopy date
,p_effective_end_date out nocopy date
) is
--
-- Declare cursors and local variables
--
l_effective_date date;
,p_datetrack_update_mode => p_datetrack_update_mode
,p_assignment_id => p_assignment_id
,p_change_reason => p_change_reason
,p_object_version_number => p_object_version_number
,p_assignment_status_type_id => p_assignment_status_type_id
);
hr_assignment_internal.update_status_type_emp_asg
(p_effective_date => l_effective_date
,p_datetrack_update_mode => p_datetrack_update_mode
,p_assignment_id => p_assignment_id
,p_change_reason => p_change_reason
,p_object_version_number => l_object_version_number
,p_expected_system_status => 'ACTIVE_ASSIGN'
,p_assignment_status_type_id => p_assignment_status_type_id
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
);
,p_datetrack_update_mode => p_datetrack_update_mode
,p_assignment_id => p_assignment_id
,p_change_reason => p_change_reason
,p_object_version_number => l_object_version_number
,p_assignment_status_type_id => p_assignment_status_type_id
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
);
,p_datetrack_update_mode in varchar2
,p_assignment_id in number
,p_change_reason in varchar2
,p_object_version_number in out nocopy number
,p_assignment_status_type_id in number
,p_effective_start_date out nocopy date
,p_effective_end_date out nocopy date
) is
--
-- Declare cursors and local variables
--
l_effective_date date;
,p_datetrack_update_mode => p_datetrack_update_mode
,p_assignment_id => p_assignment_id
,p_change_reason => p_change_reason
,p_object_version_number => p_object_version_number
,p_assignment_status_type_id => p_assignment_status_type_id
);
hr_assignment_internal.update_status_type_cwk_asg
(p_effective_date => l_effective_date
,p_datetrack_update_mode => p_datetrack_update_mode
,p_assignment_id => p_assignment_id
,p_change_reason => p_change_reason
,p_object_version_number => l_object_version_number
,p_expected_system_status => 'ACTIVE_CWK'
,p_assignment_status_type_id => p_assignment_status_type_id
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
);
,p_datetrack_update_mode => p_datetrack_update_mode
,p_assignment_id => p_assignment_id
,p_change_reason => p_change_reason
,p_object_version_number => l_object_version_number
,p_assignment_status_type_id => p_assignment_status_type_id
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
);
select asg.assignment_type
, asg.payroll_id
, asg.primary_flag
, bus.business_group_id
, bus.legislation_code
from per_all_assignments_f asg
, per_business_groups_perf bus
where asg.assignment_id = p_assignment_id
and l_actual_termination_date between asg.effective_start_date
and asg.effective_end_date
and bus.business_group_id+0 = asg.business_group_id;
select null
from per_all_assignments_f asg
, per_assignment_status_types ast
where asg.assignment_id = p_assignment_id
and asg.effective_end_date >= l_actual_termination_date
and ast.assignment_status_type_id = asg.assignment_status_type_id
and ast.per_system_status = 'TERM_CWK_ASG';
select tpe.end_date
from per_time_periods tpe
where tpe.payroll_id = l_payroll_id
and l_actual_termination_date between tpe.start_date
and tpe.end_date;
select asg.assignment_type
, asg.primary_flag
from per_all_assignments_f asg
where asg.assignment_id = p_assignment_id
and l_final_process_date between asg.effective_start_date
and asg.effective_end_date;
select min(asg.effective_start_date) - 1
from per_all_assignments_f asg
where asg.assignment_id = p_assignment_id
and exists ( select null
from per_assignment_status_types ast
where ast.assignment_status_type_id
= asg.assignment_status_type_id
and ast.per_system_status = 'TERM_CWK_ASG');
select max(asg.effective_end_date)
from per_all_assignments_f asg
where asg.assignment_id = p_assignment_id
and exists ( select null
from per_assignment_status_types ast
where ast.assignment_status_type_id
= asg.assignment_status_type_id
and ast.per_system_status = 'TERM_CWK_ASG');
,p_datetrack_update_mode in varchar2
,p_assignment_id in number
,p_change_reason in varchar2
,p_object_version_number in out nocopy number
,p_assignment_status_type_id in number
,p_effective_start_date out nocopy date
,p_effective_end_date out nocopy date
) is
--
-- Declare cursors and local variables
--
l_effective_date date;
,p_datetrack_update_mode => p_datetrack_update_mode
,p_assignment_id => p_assignment_id
,p_change_reason => p_change_reason
,p_object_version_number => p_object_version_number
,p_assignment_status_type_id => p_assignment_status_type_id
);
hr_assignment_internal.update_status_type_cwk_asg
(p_effective_date => l_effective_date
,p_datetrack_update_mode => p_datetrack_update_mode
,p_assignment_id => p_assignment_id
,p_change_reason => p_change_reason
,p_object_version_number => l_object_version_number
,p_expected_system_status => 'SUSP_CWK_ASG'
,p_assignment_status_type_id => p_assignment_status_type_id
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
);
,p_datetrack_update_mode => p_datetrack_update_mode
,p_assignment_id => p_assignment_id
,p_change_reason => p_change_reason
,p_object_version_number => l_object_version_number
,p_assignment_status_type_id => p_assignment_status_type_id
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
);
select asg.assignment_type
, asg.payroll_id
, asg.primary_flag
, bus.business_group_id
, bus.legislation_code
from per_all_assignments_f asg
, per_business_groups_perf bus
where asg.assignment_id = p_assignment_id
and l_actual_termination_date between asg.effective_start_date
and asg.effective_end_date
and bus.business_group_id+0 = asg.business_group_id;
select null
from per_all_assignments_f asg
, per_assignment_status_types ast
where asg.assignment_id = p_assignment_id
and asg.effective_end_date >= l_actual_termination_date
and ast.assignment_status_type_id = asg.assignment_status_type_id
and ast.per_system_status = 'TERM_ASSIGN';
select tpe.end_date
from per_time_periods tpe
where tpe.payroll_id = l_payroll_id
and l_actual_termination_date between tpe.start_date
and tpe.end_date;
select bus.business_group_id
, bus.legislation_code
from per_all_people_f per
, per_business_groups_perf bus
where per.person_id = p_person_id
and l_effective_date between per.effective_start_date
and per.effective_end_date
and bus.business_group_id = per.business_group_id;
select asg.period_of_service_id
from per_all_assignments_f asg
where asg.person_id = p_person_id
and l_effective_date between asg.effective_start_date
and asg.effective_end_date
and asg.primary_flag = 'Y'
--
-- Start of Bug: 2288629.
and asg.assignment_type = 'E';
select people_group_structure
from per_business_groups_perf
where business_group_id = l_business_group_id;
select plr.rule_mode id_flex_num
from pay_legislation_rules plr
where plr.legislation_code = l_legislation_code
and plr.rule_type = 'S'
and exists
(select 1
from fnd_segment_attribute_values fsav
where to_char(fsav.id_flex_num) = plr.rule_mode -- Fix For Bug 12813119
and fsav.application_id = 800
and fsav.id_flex_code = 'SCL'
and fsav.segment_attribute_type = 'ASSIGNMENT'
and fsav.attribute_value = 'Y')
and exists
(select 1
from pay_legislation_rules plr2
where plr2.legislation_code = l_legislation_code
and plr2.rule_type = 'SDL'
and plr2.rule_mode = 'A') ;
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_people_group_id;
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 hr_soft_coding_keyflex
where soft_coding_keyflex_id = l_soft_coding_keyflex_id;
select segment1,
segment2,
segment3,
segment4,
segment5,
segment6,
segment7,
segment8,
segment9,
segment10,
segment11,
segment12,
segment13,
segment14,
segment15,
segment16,
segment17,
segment18,
segment19,
segment20
from per_cagr_grades_def
where cagr_grade_def_id = l_cagr_grade_def_id;
update_scl_concat_segs -- shd this be available for when id known.
(p_soft_coding_keyflex_id => l_soft_coding_keyflex_id
,p_concatenated_segments => l_concatenated_segments
);
update_pgp_concat_segs
(p_people_group_id => l_people_group_id
,p_group_name => l_group_name
);
select bus.business_group_id
, bus.legislation_code
from per_all_people_f per
, per_business_groups_perf bus
where per.person_id = p_person_id
and l_effective_date between per.effective_start_date
and per.effective_end_date
and bus.business_group_id = per.business_group_id;
select asg.period_of_placement_date_start
from per_all_assignments_f asg
where asg.person_id = p_person_id
and l_effective_date between asg.effective_start_date
and asg.effective_end_date
and asg.primary_flag = 'Y'
and asg.assignment_type = 'C'; -- Bug fix 3266813
select people_group_structure
from per_business_groups_perf
where business_group_id = l_business_group_id;
select plr.rule_mode id_flex_num
from pay_legislation_rules plr
where plr.legislation_code = l_legislation_code
and plr.rule_type = 'S'
and exists
(select 1
from fnd_segment_attribute_values fsav
where to_char(fsav.id_flex_num) = plr.rule_mode --Fix For Bug 12813119
and fsav.application_id = 800
and fsav.id_flex_code = 'SCL'
and fsav.segment_attribute_type = 'ASSIGNMENT'
and fsav.attribute_value = 'Y')
and exists
(select 1
from pay_legislation_rules plr2
where plr2.legislation_code = l_legislation_code
and plr2.rule_type = 'SDL'
and plr2.rule_mode = 'A') ;
update_scl_concat_segs
(p_soft_coding_keyflex_id => l_soft_coding_keyflex_id
,p_concatenated_segments => l_concatenated_segments
);
update_pgp_concat_segs
(p_people_group_id => l_people_group_id
,p_group_name => l_group_name);
SELECT poh.vendor_id
,poh.vendor_site_id
FROM po_temp_labor_headers_v poh
WHERE poh.po_header_id = p_po_header_id;
SELECT povs.vendor_id
FROM po_vendor_sites_all povs
WHERE povs.vendor_site_id = p_vendor_site_id;
SELECT pol.po_header_id
FROM po_temp_labor_lines_v pol
WHERE pol.po_line_id = p_po_line_id;
SELECT pol.job_id
FROM po_temp_labor_lines_v pol
WHERE pol.po_line_id = p_po_line_id;
select null
from per_all_assignments_f paf,
per_business_groups_perf pbg
where paf.person_id = p_person_id
and l_effective_date between paf.effective_start_date
and paf.effective_end_date
and pbg.business_group_id = paf.business_group_id
and pbg.legislation_code = 'GB';
select null
from per_all_assignments_f paf,
per_business_groups_perf pbg
where paf.person_id = p_person_id
and l_effective_date between paf.effective_start_date
and paf.effective_end_date
and pbg.business_group_id = paf.business_group_id
and pbg.legislation_code = 'GB';
select null
from per_all_assignments_f paf,
per_business_groups_perf pbg
where paf.person_id = p_person_id
and l_effective_date between paf.effective_start_date
and paf.effective_end_date
and pbg.business_group_id = paf.business_group_id
and pbg.legislation_code = 'US';
select null
from per_all_assignments_f paf,
per_business_groups_perf pbg
where paf.person_id = p_person_id
and l_effective_date between paf.effective_start_date
and paf.effective_end_date
and pbg.business_group_id = paf.business_group_id
and pbg.legislation_code = 'US';
select asg.assignment_type
, asg.primary_flag
from per_all_assignments_f asg
where asg.assignment_id = p_assignment_id
and l_final_process_date between asg.effective_start_date
and asg.effective_end_date;
select min(asg.effective_start_date) - 1
from per_all_assignments_f asg
where asg.assignment_id = p_assignment_id
and exists ( select null
from per_assignment_status_types ast
where ast.assignment_status_type_id
= asg.assignment_status_type_id
and ast.per_system_status = 'TERM_ASSIGN');
select max(asg.effective_end_date)
from per_all_assignments_f asg
where asg.assignment_id = p_assignment_id
and exists ( select null
from per_assignment_status_types ast
where ast.assignment_status_type_id
= asg.assignment_status_type_id
and ast.per_system_status = 'TERM_ASSIGN');
,p_datetrack_update_mode in varchar2
,p_assignment_id in number
,p_change_reason in varchar2
,p_object_version_number in out nocopy number
,p_assignment_status_type_id in number
,p_effective_start_date out nocopy date
,p_effective_end_date out nocopy date
) is
--
-- Declare cursors and local variables
--
l_effective_date date;
,p_datetrack_update_mode => p_datetrack_update_mode
,p_assignment_id => p_assignment_id
,p_change_reason => p_change_reason
,p_object_version_number => p_object_version_number
,p_assignment_status_type_id => p_assignment_status_type_id
);
hr_assignment_internal.update_status_type_emp_asg
(p_effective_date => l_effective_date
,p_datetrack_update_mode => p_datetrack_update_mode
,p_assignment_id => p_assignment_id
,p_change_reason => p_change_reason
,p_object_version_number => l_object_version_number
,p_expected_system_status => 'SUSP_ASSIGN'
,p_assignment_status_type_id => p_assignment_status_type_id
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
);
,p_datetrack_update_mode => p_datetrack_update_mode
,p_assignment_id => p_assignment_id
,p_change_reason => p_change_reason
,p_object_version_number => l_object_version_number
,p_assignment_status_type_id => p_assignment_status_type_id
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
);
procedure update_emp_asg
(p_validate in boolean
,p_effective_date in date
,p_datetrack_update_mode in varchar2
,p_assignment_id in number
,p_object_version_number in out nocopy number
,p_supervisor_id in number
,p_assignment_number in varchar2
,p_change_reason in varchar2
,p_comments in varchar2
,p_date_probation_end in date
,p_default_code_comb_id in number
,p_frequency in varchar2
,p_internal_address_line in varchar2
,p_manager_flag in varchar2
,p_normal_hours in number
,p_perf_review_period in number
,p_perf_review_period_frequency in varchar2
,p_probation_period in number
,p_probation_unit in varchar2
,p_sal_review_period in number
,p_sal_review_period_frequency in varchar2
,p_set_of_books_id in number
,p_source_type in varchar2
,p_time_normal_finish in varchar2
,p_time_normal_start in varchar2
,p_bargaining_unit_code in varchar2
,p_labour_union_member_flag in varchar2
,p_hourly_salaried_code in varchar2
,p_ass_attribute_category in varchar2
,p_ass_attribute1 in varchar2
,p_ass_attribute2 in varchar2
,p_ass_attribute3 in varchar2
,p_ass_attribute4 in varchar2
,p_ass_attribute5 in varchar2
,p_ass_attribute6 in varchar2
,p_ass_attribute7 in varchar2
,p_ass_attribute8 in varchar2
,p_ass_attribute9 in varchar2
,p_ass_attribute10 in varchar2
,p_ass_attribute11 in varchar2
,p_ass_attribute12 in varchar2
,p_ass_attribute13 in varchar2
,p_ass_attribute14 in varchar2
,p_ass_attribute15 in varchar2
,p_ass_attribute16 in varchar2
,p_ass_attribute17 in varchar2
,p_ass_attribute18 in varchar2
,p_ass_attribute19 in varchar2
,p_ass_attribute20 in varchar2
,p_ass_attribute21 in varchar2
,p_ass_attribute22 in varchar2
,p_ass_attribute23 in varchar2
,p_ass_attribute24 in varchar2
,p_ass_attribute25 in varchar2
,p_ass_attribute26 in varchar2
,p_ass_attribute27 in varchar2
,p_ass_attribute28 in varchar2
,p_ass_attribute29 in varchar2
,p_ass_attribute30 in varchar2
,p_title in varchar2
,p_segment1 in varchar2
,p_segment2 in varchar2
,p_segment3 in varchar2
,p_segment4 in varchar2
,p_segment5 in varchar2
,p_segment6 in varchar2
,p_segment7 in varchar2
,p_segment8 in varchar2
,p_segment9 in varchar2
,p_segment10 in varchar2
,p_segment11 in varchar2
,p_segment12 in varchar2
,p_segment13 in varchar2
,p_segment14 in varchar2
,p_segment15 in varchar2
,p_segment16 in varchar2
,p_segment17 in varchar2
,p_segment18 in varchar2
,p_segment19 in varchar2
,p_segment20 in varchar2
,p_segment21 in varchar2
,p_segment22 in varchar2
,p_segment23 in varchar2
,p_segment24 in varchar2
,p_segment25 in varchar2
,p_segment26 in varchar2
,p_segment27 in varchar2
,p_segment28 in varchar2
,p_segment29 in varchar2
,p_segment30 in varchar2
-- Bug fix for 944911
-- p_concatenated_segments has been changed from in out to out
-- Added new param p_concat_segments as in param
,p_concat_segments in varchar2
,p_supervisor_assignment_id in number
,p_concatenated_segments out nocopy varchar2
,p_soft_coding_keyflex_id in out nocopy number -- bug 2359997
,p_comment_id out nocopy number
,p_effective_start_date out nocopy date
,p_effective_end_date out nocopy date
,p_no_managers_warning out nocopy boolean
,p_other_manager_warning out nocopy boolean
) is
--
-- Declare cursors and local variables
--
-- Out variables
--
l_comment_id per_all_assignments_f.comment_id%TYPE;
l_proc := g_package||'update_emp_asg';
hr_assignment_api.update_emp_asg
(p_validate => p_validate
,p_effective_date => p_effective_date
,p_datetrack_update_mode => p_datetrack_update_mode
,p_assignment_id => p_assignment_id
,p_object_version_number => l_object_version_number
,p_supervisor_id => p_supervisor_id
,p_assignment_number => p_assignment_number
,p_change_reason => p_change_reason
,p_comments => p_comments
,p_date_probation_end => p_date_probation_end
,p_default_code_comb_id => p_default_code_comb_id
,p_frequency => p_frequency
,p_internal_address_line => p_internal_address_line
,p_manager_flag => p_manager_flag
,p_normal_hours => p_normal_hours
,p_perf_review_period => p_perf_review_period
,p_perf_review_period_frequency => p_perf_review_period_frequency
,p_probation_period => p_probation_period
,p_probation_unit => p_probation_unit
,p_sal_review_period => p_sal_review_period
,p_sal_review_period_frequency => p_sal_review_period_frequency
,p_set_of_books_id => p_set_of_books_id
,p_source_type => p_source_type
,p_time_normal_finish => p_time_normal_finish
,p_time_normal_start => p_time_normal_start
,p_bargaining_unit_code => p_bargaining_unit_code
,p_labour_union_member_flag => p_labour_union_member_flag
,p_hourly_salaried_code => p_hourly_salaried_code
,p_ass_attribute_category => p_ass_attribute_category
,p_ass_attribute1 => p_ass_attribute1
,p_ass_attribute2 => p_ass_attribute2
,p_ass_attribute3 => p_ass_attribute3
,p_ass_attribute4 => p_ass_attribute4
,p_ass_attribute5 => p_ass_attribute5
,p_ass_attribute6 => p_ass_attribute6
,p_ass_attribute7 => p_ass_attribute7
,p_ass_attribute8 => p_ass_attribute8
,p_ass_attribute9 => p_ass_attribute9
,p_ass_attribute10 => p_ass_attribute10
,p_ass_attribute11 => p_ass_attribute11
,p_ass_attribute12 => p_ass_attribute12
,p_ass_attribute13 => p_ass_attribute13
,p_ass_attribute14 => p_ass_attribute14
,p_ass_attribute15 => p_ass_attribute15
,p_ass_attribute16 => p_ass_attribute16
,p_ass_attribute17 => p_ass_attribute17
,p_ass_attribute18 => p_ass_attribute18
,p_ass_attribute19 => p_ass_attribute19
,p_ass_attribute20 => p_ass_attribute20
,p_ass_attribute21 => p_ass_attribute21
,p_ass_attribute22 => p_ass_attribute22
,p_ass_attribute23 => p_ass_attribute23
,p_ass_attribute24 => p_ass_attribute24
,p_ass_attribute25 => p_ass_attribute25
,p_ass_attribute26 => p_ass_attribute26
,p_ass_attribute27 => p_ass_attribute27
,p_ass_attribute28 => p_ass_attribute28
,p_ass_attribute29 => p_ass_attribute29
,p_ass_attribute30 => p_ass_attribute30
,p_title => p_title
,p_segment1 => p_segment1
,p_segment2 => p_segment2
,p_segment3 => p_segment3
,p_segment4 => p_segment4
,p_segment5 => p_segment5
,p_segment6 => p_segment6
,p_segment7 => p_segment7
,p_segment8 => p_segment8
,p_segment9 => p_segment9
,p_segment10 => p_segment10
,p_segment11 => p_segment11
,p_segment12 => p_segment12
,p_segment13 => p_segment13
,p_segment14 => p_segment14
,p_segment15 => p_segment15
,p_segment16 => p_segment16
,p_segment17 => p_segment17
,p_segment18 => p_segment18
,p_segment19 => p_segment19
,p_segment20 => p_segment20
,p_segment21 => p_segment21
,p_segment22 => p_segment22
,p_segment23 => p_segment23
,p_segment24 => p_segment24
,p_segment25 => p_segment25
,p_segment26 => p_segment26
,p_segment27 => p_segment27
,p_segment28 => p_segment28
,p_segment29 => p_segment29
,p_segment30 => p_segment30
,p_concat_segments => p_concat_segments
,p_contract_id => hr_api.g_number
,p_establishment_id => hr_api.g_number
,p_collective_agreement_id => hr_api.g_number
,p_cagr_id_flex_num => hr_api.g_number
,p_cag_segment1 => hr_api.g_varchar2
,p_cag_segment2 => hr_api.g_varchar2
,p_cag_segment3 => hr_api.g_varchar2
,p_cag_segment4 => hr_api.g_varchar2
,p_cag_segment5 => hr_api.g_varchar2
,p_cag_segment6 => hr_api.g_varchar2
,p_cag_segment7 => hr_api.g_varchar2
,p_cag_segment8 => hr_api.g_varchar2
,p_cag_segment9 => hr_api.g_varchar2
,p_cag_segment10 => hr_api.g_varchar2
,p_cag_segment11 => hr_api.g_varchar2
,p_cag_segment12 => hr_api.g_varchar2
,p_cag_segment13 => hr_api.g_varchar2
,p_cag_segment14 => hr_api.g_varchar2
,p_cag_segment15 => hr_api.g_varchar2
,p_cag_segment16 => hr_api.g_varchar2
,p_cag_segment17 => hr_api.g_varchar2
,p_cag_segment18 => hr_api.g_varchar2
,p_cag_segment19 => hr_api.g_varchar2
,p_cag_segment20 => hr_api.g_varchar2
,p_cagr_grade_def_id => l_cagr_grade_def_id
,p_cagr_concatenated_segments => l_cagr_concatenated_segments
,p_soft_coding_keyflex_id => l_soft_coding_keyflex_id
,p_comment_id => l_comment_id
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
,p_concatenated_segments => l_concatenated_segments
,p_no_managers_warning => l_no_managers_warning
,p_other_manager_warning => l_other_manager_warning
,p_supervisor_assignment_id => p_supervisor_assignment_id
);
end update_emp_asg;
procedure update_emp_asg
(p_validate in boolean
,p_effective_date in date
,p_datetrack_update_mode in varchar2
,p_assignment_id in number
,p_object_version_number in out nocopy number
,p_supervisor_id in number
,p_assignment_number in varchar2
,p_change_reason in varchar2
,p_assignment_status_type_id in number
,p_comments in varchar2
,p_date_probation_end in date
,p_default_code_comb_id in number
,p_frequency in varchar2
,p_internal_address_line in varchar2
,p_manager_flag in varchar2
,p_normal_hours in number
,p_perf_review_period in number
,p_perf_review_period_frequency in varchar2
,p_probation_period in number
,p_probation_unit in varchar2
,p_sal_review_period in number
,p_sal_review_period_frequency in varchar2
,p_set_of_books_id in number
,p_source_type in varchar2
,p_time_normal_finish in varchar2
,p_time_normal_start in varchar2
,p_bargaining_unit_code in varchar2
,p_labour_union_member_flag in varchar2
,p_hourly_salaried_code in varchar2
,p_ass_attribute_category in varchar2
,p_ass_attribute1 in varchar2
,p_ass_attribute2 in varchar2
,p_ass_attribute3 in varchar2
,p_ass_attribute4 in varchar2
,p_ass_attribute5 in varchar2
,p_ass_attribute6 in varchar2
,p_ass_attribute7 in varchar2
,p_ass_attribute8 in varchar2
,p_ass_attribute9 in varchar2
,p_ass_attribute10 in varchar2
,p_ass_attribute11 in varchar2
,p_ass_attribute12 in varchar2
,p_ass_attribute13 in varchar2
,p_ass_attribute14 in varchar2
,p_ass_attribute15 in varchar2
,p_ass_attribute16 in varchar2
,p_ass_attribute17 in varchar2
,p_ass_attribute18 in varchar2
,p_ass_attribute19 in varchar2
,p_ass_attribute20 in varchar2
,p_ass_attribute21 in varchar2
,p_ass_attribute22 in varchar2
,p_ass_attribute23 in varchar2
,p_ass_attribute24 in varchar2
,p_ass_attribute25 in varchar2
,p_ass_attribute26 in varchar2
,p_ass_attribute27 in varchar2
,p_ass_attribute28 in varchar2
,p_ass_attribute29 in varchar2
,p_ass_attribute30 in varchar2
,p_title in varchar2
,p_segment1 in varchar2
,p_segment2 in varchar2
,p_segment3 in varchar2
,p_segment4 in varchar2
,p_segment5 in varchar2
,p_segment6 in varchar2
,p_segment7 in varchar2
,p_segment8 in varchar2
,p_segment9 in varchar2
,p_segment10 in varchar2
,p_segment11 in varchar2
,p_segment12 in varchar2
,p_segment13 in varchar2
,p_segment14 in varchar2
,p_segment15 in varchar2
,p_segment16 in varchar2
,p_segment17 in varchar2
,p_segment18 in varchar2
,p_segment19 in varchar2
,p_segment20 in varchar2
,p_segment21 in varchar2
,p_segment22 in varchar2
,p_segment23 in varchar2
,p_segment24 in varchar2
,p_segment25 in varchar2
,p_segment26 in varchar2
,p_segment27 in varchar2
,p_segment28 in varchar2
,p_segment29 in varchar2
,p_segment30 in varchar2
-- Bug fix for 944911
-- p_concatenated_segments has been changed from in out to out
-- Added new param p_concat_segments as in param
,p_concat_segments in varchar2
,p_contract_id in number
,p_establishment_id in number
,p_collective_agreement_id in number
,p_cagr_id_flex_num in number
,p_cag_segment1 in varchar2
,p_cag_segment2 in varchar2
,p_cag_segment3 in varchar2
,p_cag_segment4 in varchar2
,p_cag_segment5 in varchar2
,p_cag_segment6 in varchar2
,p_cag_segment7 in varchar2
,p_cag_segment8 in varchar2
,p_cag_segment9 in varchar2
,p_cag_segment10 in varchar2
,p_cag_segment11 in varchar2
,p_cag_segment12 in varchar2
,p_cag_segment13 in varchar2
,p_cag_segment14 in varchar2
,p_cag_segment15 in varchar2
,p_cag_segment16 in varchar2
,p_cag_segment17 in varchar2
,p_cag_segment18 in varchar2
,p_cag_segment19 in varchar2
,p_cag_segment20 in varchar2
,p_notice_period in number
,p_notice_period_uom in varchar2
,p_employee_category in varchar2
,p_work_at_home in varchar2
,p_job_post_source_name in varchar2
,p_supervisor_assignment_id in number
,p_cagr_grade_def_id in out nocopy number -- bug 2359997
,p_cagr_concatenated_segments out nocopy varchar2
,p_concatenated_segments out nocopy varchar2
,p_soft_coding_keyflex_id in out nocopy number -- bug 2359997
,p_comment_id out nocopy number
,p_effective_start_date out nocopy date
,p_effective_end_date out nocopy date
,p_no_managers_warning out nocopy boolean
,p_other_manager_warning out nocopy boolean
) is
--
-- Declare cursors and local variables
--
-- Out variables
--
l_comment_id per_all_assignments_f.comment_id%TYPE;
l_proc := g_package||'update_emp_asg';
hr_assignment_api.update_emp_asg
(p_validate => p_validate
,p_effective_date => p_effective_date
,p_datetrack_update_mode => p_datetrack_update_mode
,p_assignment_id => p_assignment_id
,p_object_version_number => l_object_version_number
,p_supervisor_id => p_supervisor_id
,p_assignment_number => p_assignment_number
,p_change_reason => p_change_reason
,p_assignment_status_type_id => p_assignment_status_type_id
,p_comments => p_comments
,p_date_probation_end => p_date_probation_end
,p_default_code_comb_id => p_default_code_comb_id
,p_frequency => p_frequency
,p_internal_address_line => p_internal_address_line
,p_manager_flag => p_manager_flag
,p_normal_hours => p_normal_hours
,p_perf_review_period => p_perf_review_period
,p_perf_review_period_frequency => p_perf_review_period_frequency
,p_probation_period => p_probation_period
,p_probation_unit => p_probation_unit
,p_sal_review_period => p_sal_review_period
,p_sal_review_period_frequency => p_sal_review_period_frequency
,p_set_of_books_id => p_set_of_books_id
,p_source_type => p_source_type
,p_time_normal_finish => p_time_normal_finish
,p_time_normal_start => p_time_normal_start
,p_bargaining_unit_code => p_bargaining_unit_code
,p_labour_union_member_flag => p_labour_union_member_flag
,p_hourly_salaried_code => p_hourly_salaried_code
,p_ass_attribute_category => p_ass_attribute_category
,p_ass_attribute1 => p_ass_attribute1
,p_ass_attribute2 => p_ass_attribute2
,p_ass_attribute3 => p_ass_attribute3
,p_ass_attribute4 => p_ass_attribute4
,p_ass_attribute5 => p_ass_attribute5
,p_ass_attribute6 => p_ass_attribute6
,p_ass_attribute7 => p_ass_attribute7
,p_ass_attribute8 => p_ass_attribute8
,p_ass_attribute9 => p_ass_attribute9
,p_ass_attribute10 => p_ass_attribute10
,p_ass_attribute11 => p_ass_attribute11
,p_ass_attribute12 => p_ass_attribute12
,p_ass_attribute13 => p_ass_attribute13
,p_ass_attribute14 => p_ass_attribute14
,p_ass_attribute15 => p_ass_attribute15
,p_ass_attribute16 => p_ass_attribute16
,p_ass_attribute17 => p_ass_attribute17
,p_ass_attribute18 => p_ass_attribute18
,p_ass_attribute19 => p_ass_attribute19
,p_ass_attribute20 => p_ass_attribute20
,p_ass_attribute21 => p_ass_attribute21
,p_ass_attribute22 => p_ass_attribute22
,p_ass_attribute23 => p_ass_attribute23
,p_ass_attribute24 => p_ass_attribute24
,p_ass_attribute25 => p_ass_attribute25
,p_ass_attribute26 => p_ass_attribute26
,p_ass_attribute27 => p_ass_attribute27
,p_ass_attribute28 => p_ass_attribute28
,p_ass_attribute29 => p_ass_attribute29
,p_ass_attribute30 => p_ass_attribute30
,p_title => p_title
,p_segment1 => p_segment1
,p_segment2 => p_segment2
,p_segment3 => p_segment3
,p_segment4 => p_segment4
,p_segment5 => p_segment5
,p_segment6 => p_segment6
,p_segment7 => p_segment7
,p_segment8 => p_segment8
,p_segment9 => p_segment9
,p_segment10 => p_segment10
,p_segment11 => p_segment11
,p_segment12 => p_segment12
,p_segment13 => p_segment13
,p_segment14 => p_segment14
,p_segment15 => p_segment15
,p_segment16 => p_segment16
,p_segment17 => p_segment17
,p_segment18 => p_segment18
,p_segment19 => p_segment19
,p_segment20 => p_segment20
,p_segment21 => p_segment21
,p_segment22 => p_segment22
,p_segment23 => p_segment23
,p_segment24 => p_segment24
,p_segment25 => p_segment25
,p_segment26 => p_segment26
,p_segment27 => p_segment27
,p_segment28 => p_segment28
,p_segment29 => p_segment29
,p_segment30 => p_segment30
,p_concat_segments => p_concat_segments
,p_contract_id => p_contract_id
,p_establishment_id => p_establishment_id
,p_collective_agreement_id => p_collective_agreement_id
,p_cagr_id_flex_num => p_cagr_id_flex_num
,p_cag_segment1 => p_cag_segment1
,p_cag_segment2 => p_cag_segment2
,p_cag_segment3 => p_cag_segment3
,p_cag_segment4 => p_cag_segment4
,p_cag_segment5 => p_cag_segment5
,p_cag_segment6 => p_cag_segment6
,p_cag_segment7 => p_cag_segment7
,p_cag_segment8 => p_cag_segment8
,p_cag_segment9 => p_cag_segment9
,p_cag_segment10 => p_cag_segment10
,p_cag_segment11 => p_cag_segment11
,p_cag_segment12 => p_cag_segment12
,p_cag_segment13 => p_cag_segment13
,p_cag_segment14 => p_cag_segment14
,p_cag_segment15 => p_cag_segment15
,p_cag_segment16 => p_cag_segment16
,p_cag_segment17 => p_cag_segment17
,p_cag_segment18 => p_cag_segment18
,p_cag_segment19 => p_cag_segment19
,p_cag_segment20 => p_cag_segment20
,p_notice_period => p_notice_period
,p_notice_period_uom => p_notice_period_uom
,p_employee_category => p_employee_category
,p_work_at_home => p_work_at_home
,p_job_post_source_name => p_job_post_source_name
,p_cagr_grade_def_id => l_cagr_grade_def_id
,p_cagr_concatenated_segments => l_cagr_concatenated_segments
,p_soft_coding_keyflex_id => l_soft_coding_keyflex_id
,p_comment_id => l_comment_id
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
,p_concatenated_segments => l_concatenated_segments
,p_no_managers_warning => l_no_managers_warning
,p_other_manager_warning => l_other_manager_warning
,p_hourly_salaried_warning => l_hourly_salaried_warning
,p_supervisor_assignment_id => p_supervisor_assignment_id
);
end update_emp_asg;
procedure update_emp_asg
(p_validate in boolean
,p_effective_date in date
,p_datetrack_update_mode in varchar2
,p_assignment_id in number
,p_object_version_number in out nocopy number
,p_supervisor_id in number
,p_assignment_number in varchar2
,p_change_reason in varchar2
,p_assignment_status_type_id in number
,p_comments in varchar2
,p_date_probation_end in date
,p_default_code_comb_id in number
,p_frequency in varchar2
,p_internal_address_line in varchar2
,p_manager_flag in varchar2
,p_normal_hours in number
,p_perf_review_period in number
,p_perf_review_period_frequency in varchar2
,p_probation_period in number
,p_probation_unit in varchar2
,p_sal_review_period in number
,p_sal_review_period_frequency in varchar2
,p_set_of_books_id in number
,p_source_type in varchar2
,p_time_normal_finish in varchar2
,p_time_normal_start in varchar2
,p_bargaining_unit_code in varchar2
,p_labour_union_member_flag in varchar2
,p_hourly_salaried_code in varchar2
,p_ass_attribute_category in varchar2
,p_ass_attribute1 in varchar2
,p_ass_attribute2 in varchar2
,p_ass_attribute3 in varchar2
,p_ass_attribute4 in varchar2
,p_ass_attribute5 in varchar2
,p_ass_attribute6 in varchar2
,p_ass_attribute7 in varchar2
,p_ass_attribute8 in varchar2
,p_ass_attribute9 in varchar2
,p_ass_attribute10 in varchar2
,p_ass_attribute11 in varchar2
,p_ass_attribute12 in varchar2
,p_ass_attribute13 in varchar2
,p_ass_attribute14 in varchar2
,p_ass_attribute15 in varchar2
,p_ass_attribute16 in varchar2
,p_ass_attribute17 in varchar2
,p_ass_attribute18 in varchar2
,p_ass_attribute19 in varchar2
,p_ass_attribute20 in varchar2
,p_ass_attribute21 in varchar2
,p_ass_attribute22 in varchar2
,p_ass_attribute23 in varchar2
,p_ass_attribute24 in varchar2
,p_ass_attribute25 in varchar2
,p_ass_attribute26 in varchar2
,p_ass_attribute27 in varchar2
,p_ass_attribute28 in varchar2
,p_ass_attribute29 in varchar2
,p_ass_attribute30 in varchar2
,p_title in varchar2
,p_segment1 in varchar2
,p_segment2 in varchar2
,p_segment3 in varchar2
,p_segment4 in varchar2
,p_segment5 in varchar2
,p_segment6 in varchar2
,p_segment7 in varchar2
,p_segment8 in varchar2
,p_segment9 in varchar2
,p_segment10 in varchar2
,p_segment11 in varchar2
,p_segment12 in varchar2
,p_segment13 in varchar2
,p_segment14 in varchar2
,p_segment15 in varchar2
,p_segment16 in varchar2
,p_segment17 in varchar2
,p_segment18 in varchar2
,p_segment19 in varchar2
,p_segment20 in varchar2
,p_segment21 in varchar2
,p_segment22 in varchar2
,p_segment23 in varchar2
,p_segment24 in varchar2
,p_segment25 in varchar2
,p_segment26 in varchar2
,p_segment27 in varchar2
,p_segment28 in varchar2
,p_segment29 in varchar2
,p_segment30 in varchar2
-- Bug fix for 944911
-- p_concatenated_segments has been changed from in out to out
-- Added new param p_concat_segments as in param
,p_concat_segments in varchar2
,p_contract_id in number
,p_establishment_id in number
,p_collective_agreement_id in number
,p_cagr_id_flex_num in number
,p_cag_segment1 in varchar2
,p_cag_segment2 in varchar2
,p_cag_segment3 in varchar2
,p_cag_segment4 in varchar2
,p_cag_segment5 in varchar2
,p_cag_segment6 in varchar2
,p_cag_segment7 in varchar2
,p_cag_segment8 in varchar2
,p_cag_segment9 in varchar2
,p_cag_segment10 in varchar2
,p_cag_segment11 in varchar2
,p_cag_segment12 in varchar2
,p_cag_segment13 in varchar2
,p_cag_segment14 in varchar2
,p_cag_segment15 in varchar2
,p_cag_segment16 in varchar2
,p_cag_segment17 in varchar2
,p_cag_segment18 in varchar2
,p_cag_segment19 in varchar2
,p_cag_segment20 in varchar2
,p_notice_period in number
,p_notice_period_uom in varchar2
,p_employee_category in varchar2
,p_work_at_home in varchar2
,p_job_post_source_name in varchar2
,p_supervisor_assignment_id in number
,p_cagr_grade_def_id in out nocopy number -- bug 2359997
,p_cagr_concatenated_segments out nocopy varchar2
,p_concatenated_segments out nocopy varchar2
,p_soft_coding_keyflex_id in out nocopy number -- bug 2359997
,p_comment_id out nocopy number
,p_effective_start_date out nocopy date
,p_effective_end_date out nocopy date
,p_no_managers_warning out nocopy boolean
,p_other_manager_warning out nocopy boolean
,p_hourly_salaried_warning out nocopy boolean
) is
--
-- Declare cursors and local variables
--
-- Out variables
--
l_comment_id per_all_assignments_f.comment_id%TYPE;
l_payroll_id_updated boolean;
l_proc varchar2(72) := g_package||'update_emp_asg';
hr_assignment_api.update_emp_asg
(p_validate => p_validate
,p_effective_date => p_effective_date
,p_datetrack_update_mode => p_datetrack_update_mode
,p_assignment_id => p_assignment_id
,p_object_version_number => l_object_version_number
,p_supervisor_id => p_supervisor_id
,p_assignment_number => p_assignment_number
,p_change_reason => p_change_reason
,p_assignment_status_type_id => p_assignment_status_type_id
,p_comments => p_comments
,p_date_probation_end => p_date_probation_end
,p_default_code_comb_id => p_default_code_comb_id
,p_frequency => p_frequency
,p_internal_address_line => p_internal_address_line
,p_manager_flag => p_manager_flag
,p_normal_hours => p_normal_hours
,p_perf_review_period => p_perf_review_period
,p_perf_review_period_frequency => p_perf_review_period_frequency
,p_probation_period => p_probation_period
,p_probation_unit => p_probation_unit
,p_sal_review_period => p_sal_review_period
,p_sal_review_period_frequency => p_sal_review_period_frequency
,p_set_of_books_id => p_set_of_books_id
,p_source_type => p_source_type
,p_time_normal_finish => p_time_normal_finish
,p_time_normal_start => p_time_normal_start
,p_bargaining_unit_code => p_bargaining_unit_code
,p_labour_union_member_flag => p_labour_union_member_flag
,p_hourly_salaried_code => p_hourly_salaried_code
,p_ass_attribute_category => p_ass_attribute_category
,p_ass_attribute1 => p_ass_attribute1
,p_ass_attribute2 => p_ass_attribute2
,p_ass_attribute3 => p_ass_attribute3
,p_ass_attribute4 => p_ass_attribute4
,p_ass_attribute5 => p_ass_attribute5
,p_ass_attribute6 => p_ass_attribute6
,p_ass_attribute7 => p_ass_attribute7
,p_ass_attribute8 => p_ass_attribute8
,p_ass_attribute9 => p_ass_attribute9
,p_ass_attribute10 => p_ass_attribute10
,p_ass_attribute11 => p_ass_attribute11
,p_ass_attribute12 => p_ass_attribute12
,p_ass_attribute13 => p_ass_attribute13
,p_ass_attribute14 => p_ass_attribute14
,p_ass_attribute15 => p_ass_attribute15
,p_ass_attribute16 => p_ass_attribute16
,p_ass_attribute17 => p_ass_attribute17
,p_ass_attribute18 => p_ass_attribute18
,p_ass_attribute19 => p_ass_attribute19
,p_ass_attribute20 => p_ass_attribute20
,p_ass_attribute21 => p_ass_attribute21
,p_ass_attribute22 => p_ass_attribute22
,p_ass_attribute23 => p_ass_attribute23
,p_ass_attribute24 => p_ass_attribute24
,p_ass_attribute25 => p_ass_attribute25
,p_ass_attribute26 => p_ass_attribute26
,p_ass_attribute27 => p_ass_attribute27
,p_ass_attribute28 => p_ass_attribute28
,p_ass_attribute29 => p_ass_attribute29
,p_ass_attribute30 => p_ass_attribute30
,p_title => p_title
,p_segment1 => p_segment1
,p_segment2 => p_segment2
,p_segment3 => p_segment3
,p_segment4 => p_segment4
,p_segment5 => p_segment5
,p_segment6 => p_segment6
,p_segment7 => p_segment7
,p_segment8 => p_segment8
,p_segment9 => p_segment9
,p_segment10 => p_segment10
,p_segment11 => p_segment11
,p_segment12 => p_segment12
,p_segment13 => p_segment13
,p_segment14 => p_segment14
,p_segment15 => p_segment15
,p_segment16 => p_segment16
,p_segment17 => p_segment17
,p_segment18 => p_segment18
,p_segment19 => p_segment19
,p_segment20 => p_segment20
,p_segment21 => p_segment21
,p_segment22 => p_segment22
,p_segment23 => p_segment23
,p_segment24 => p_segment24
,p_segment25 => p_segment25
,p_segment26 => p_segment26
,p_segment27 => p_segment27
,p_segment28 => p_segment28
,p_segment29 => p_segment29
,p_segment30 => p_segment30
,p_concat_segments => p_concat_segments
,p_contract_id => p_contract_id
,p_establishment_id => p_establishment_id
,p_collective_agreement_id => p_collective_agreement_id
,p_cagr_id_flex_num => p_cagr_id_flex_num
,p_cag_segment1 => p_cag_segment1
,p_cag_segment2 => p_cag_segment2
,p_cag_segment3 => p_cag_segment3
,p_cag_segment4 => p_cag_segment4
,p_cag_segment5 => p_cag_segment5
,p_cag_segment6 => p_cag_segment6
,p_cag_segment7 => p_cag_segment7
,p_cag_segment8 => p_cag_segment8
,p_cag_segment9 => p_cag_segment9
,p_cag_segment10 => p_cag_segment10
,p_cag_segment11 => p_cag_segment11
,p_cag_segment12 => p_cag_segment12
,p_cag_segment13 => p_cag_segment13
,p_cag_segment14 => p_cag_segment14
,p_cag_segment15 => p_cag_segment15
,p_cag_segment16 => p_cag_segment16
,p_cag_segment17 => p_cag_segment17
,p_cag_segment18 => p_cag_segment18
,p_cag_segment19 => p_cag_segment19
,p_cag_segment20 => p_cag_segment20
,p_notice_period => p_notice_period
,p_notice_period_uom => p_notice_period_uom
,p_employee_category => p_employee_category
,p_work_at_home => p_work_at_home
,p_job_post_source_name => p_job_post_source_name
,p_cagr_grade_def_id => l_cagr_grade_def_id
,p_cagr_concatenated_segments => l_cagr_concatenated_segments
,p_soft_coding_keyflex_id => l_soft_coding_keyflex_id
,p_comment_id => l_comment_id
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
,p_concatenated_segments => l_concatenated_segments
,p_no_managers_warning => l_no_managers_warning
,p_other_manager_warning => l_other_manager_warning
,p_hourly_salaried_warning => l_hourly_salaried_warning
,p_gsp_post_process_warning => l_gsp_post_process_warning
,p_supervisor_assignment_id => p_supervisor_assignment_id
); -- bug 2999562
end update_emp_asg;
procedure update_emp_asg
(p_validate in boolean
,p_effective_date in date
,p_datetrack_update_mode in varchar2
,p_assignment_id in number
,p_object_version_number in out nocopy number
,p_supervisor_id in number
,p_assignment_number in varchar2
,p_change_reason in varchar2
,p_assignment_status_type_id in number
,p_comments in varchar2
,p_date_probation_end in date
,p_default_code_comb_id in number
,p_frequency in varchar2
,p_internal_address_line in varchar2
,p_manager_flag in varchar2
,p_normal_hours in number
,p_perf_review_period in number
,p_perf_review_period_frequency in varchar2
,p_probation_period in number
,p_probation_unit in varchar2
,p_projected_assignment_end in varchar2
,p_sal_review_period in number
,p_sal_review_period_frequency in varchar2
,p_set_of_books_id in number
,p_source_type in varchar2
,p_time_normal_finish in varchar2
,p_time_normal_start in varchar2
,p_bargaining_unit_code in varchar2
,p_labour_union_member_flag in varchar2
,p_hourly_salaried_code in varchar2
,p_ass_attribute_category in varchar2
,p_ass_attribute1 in varchar2
,p_ass_attribute2 in varchar2
,p_ass_attribute3 in varchar2
,p_ass_attribute4 in varchar2
,p_ass_attribute5 in varchar2
,p_ass_attribute6 in varchar2
,p_ass_attribute7 in varchar2
,p_ass_attribute8 in varchar2
,p_ass_attribute9 in varchar2
,p_ass_attribute10 in varchar2
,p_ass_attribute11 in varchar2
,p_ass_attribute12 in varchar2
,p_ass_attribute13 in varchar2
,p_ass_attribute14 in varchar2
,p_ass_attribute15 in varchar2
,p_ass_attribute16 in varchar2
,p_ass_attribute17 in varchar2
,p_ass_attribute18 in varchar2
,p_ass_attribute19 in varchar2
,p_ass_attribute20 in varchar2
,p_ass_attribute21 in varchar2
,p_ass_attribute22 in varchar2
,p_ass_attribute23 in varchar2
,p_ass_attribute24 in varchar2
,p_ass_attribute25 in varchar2
,p_ass_attribute26 in varchar2
,p_ass_attribute27 in varchar2
,p_ass_attribute28 in varchar2
,p_ass_attribute29 in varchar2
,p_ass_attribute30 in varchar2
,p_title in varchar2
,p_segment1 in varchar2
,p_segment2 in varchar2
,p_segment3 in varchar2
,p_segment4 in varchar2
,p_segment5 in varchar2
,p_segment6 in varchar2
,p_segment7 in varchar2
,p_segment8 in varchar2
,p_segment9 in varchar2
,p_segment10 in varchar2
,p_segment11 in varchar2
,p_segment12 in varchar2
,p_segment13 in varchar2
,p_segment14 in varchar2
,p_segment15 in varchar2
,p_segment16 in varchar2
,p_segment17 in varchar2
,p_segment18 in varchar2
,p_segment19 in varchar2
,p_segment20 in varchar2
,p_segment21 in varchar2
,p_segment22 in varchar2
,p_segment23 in varchar2
,p_segment24 in varchar2
,p_segment25 in varchar2
,p_segment26 in varchar2
,p_segment27 in varchar2
,p_segment28 in varchar2
,p_segment29 in varchar2
,p_segment30 in varchar2
-- Bug fix for 944911
-- p_concatenated_segments has been changed from in out to out
-- Added new param p_concat_segments as in param
,p_concat_segments in varchar2
,p_contract_id in number
,p_establishment_id in number
,p_collective_agreement_id in number
,p_cagr_id_flex_num in number
,p_cag_segment1 in varchar2
,p_cag_segment2 in varchar2
,p_cag_segment3 in varchar2
,p_cag_segment4 in varchar2
,p_cag_segment5 in varchar2
,p_cag_segment6 in varchar2
,p_cag_segment7 in varchar2
,p_cag_segment8 in varchar2
,p_cag_segment9 in varchar2
,p_cag_segment10 in varchar2
,p_cag_segment11 in varchar2
,p_cag_segment12 in varchar2
,p_cag_segment13 in varchar2
,p_cag_segment14 in varchar2
,p_cag_segment15 in varchar2
,p_cag_segment16 in varchar2
,p_cag_segment17 in varchar2
,p_cag_segment18 in varchar2
,p_cag_segment19 in varchar2
,p_cag_segment20 in varchar2
,p_notice_period in number
,p_notice_period_uom in varchar2
,p_employee_category in varchar2
,p_work_at_home in varchar2
,p_job_post_source_name in varchar2
,p_supervisor_assignment_id in number
,p_cagr_grade_def_id in out nocopy number -- bug 2359997
,p_cagr_concatenated_segments out nocopy varchar2
,p_concatenated_segments out nocopy varchar2
,p_soft_coding_keyflex_id in out nocopy number -- bug 2359997
,p_comment_id out nocopy number
,p_effective_start_date out nocopy date
,p_effective_end_date out nocopy date
,p_no_managers_warning out nocopy boolean
,p_other_manager_warning out nocopy boolean
,p_hourly_salaried_warning out nocopy boolean
,p_gsp_post_process_warning out nocopy varchar2
) is
--
-- Declare cursors and local variables
--
-- Out variables
--
l_comment_id per_all_assignments_f.comment_id%TYPE;
l_payroll_id_updated boolean;
l_proc varchar2(72) := g_package||'update_emp_asg';
select ast.per_system_status
from per_assignment_status_types ast,
per_all_assignments_f asg
where ast.assignment_status_type_id = asg.assignment_status_type_id
and asg.assignment_id = p_assignment_id
and l_effective_date between asg.effective_start_date
and asg.effective_end_date;
select ast.per_system_status
from per_assignment_status_types ast
where ast.assignment_status_type_id = p_assignment_status_type_id;
select asg.assignment_type
, asg.business_group_id
-- , asg.soft_coding_keyflex_id -- bug 2359997
, asg.organization_id
, asg.location_id
from per_all_assignments_f asg
where asg.assignment_id = p_assignment_id
and l_effective_date between asg.effective_start_date
and asg.effective_end_date;
select asg.soft_coding_keyflex_id
from per_all_assignments_f asg
where asg.assignment_id = p_assignment_id
and l_effective_date between asg.effective_start_date
and asg.effective_end_date;
select plr.rule_mode id_flex_num
from pay_legislation_rules plr,
per_business_groups_perf pgr
where plr.legislation_code = pgr.legislation_code
and pgr.business_group_id = l_business_group_id
and plr.rule_type = 'S'
and exists
(select 1
from fnd_segment_attribute_values fsav
where fsav.id_flex_num = plr.rule_mode
and fsav.application_id = 800
and fsav.id_flex_code = 'SCL'
and fsav.segment_attribute_type = 'ASSIGNMENT'
and fsav.attribute_value = 'Y')
and exists
(select 1
from pay_legislation_rules plr2
where plr2.legislation_code = plr.legislation_code
and plr2.rule_type = 'SDL'
and plr2.rule_mode = 'A') ;
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 hr_soft_coding_keyflex
where soft_coding_keyflex_id = l_soft_coding_keyflex_id;
select segment1,
segment2,
segment3,
segment4,
segment5,
segment6,
segment7,
segment8,
segment9,
segment10,
segment11,
segment12,
segment13,
segment14,
segment15,
segment16,
segment17,
segment18,
segment19,
segment20
from per_cagr_grades_def
where cagr_grade_def_id = l_cagr_grade_def_id;
savepoint update_emp_asg;
hr_assignment_bk2.update_emp_asg_b
(p_effective_date => l_effective_date
,p_datetrack_update_mode => p_datetrack_update_mode
,p_assignment_id => p_assignment_id
,p_object_version_number => p_object_version_number
,p_supervisor_id => p_supervisor_id
,p_assignment_number => p_assignment_number
,p_change_reason => p_change_reason
,p_assignment_status_type_id => p_assignment_status_type_id
,p_comments => p_comments
,p_date_probation_end => l_date_probation_end
,p_default_code_comb_id => p_default_code_comb_id
,p_frequency => p_frequency
,p_internal_address_line => p_internal_address_line
,p_manager_flag => p_manager_flag
,p_normal_hours => p_normal_hours
,p_perf_review_period => p_perf_review_period
,p_perf_review_period_frequency => p_perf_review_period_frequency
,p_probation_period => p_probation_period
,p_probation_unit => p_probation_unit
,p_projected_assignment_end => p_projected_assignment_end
,p_sal_review_period => p_sal_review_period
,p_sal_review_period_frequency => p_sal_review_period_frequency
,p_set_of_books_id => p_set_of_books_id
,p_source_type => p_source_type
,p_time_normal_finish => p_time_normal_finish
,p_time_normal_start => p_time_normal_start
,p_bargaining_unit_code => p_bargaining_unit_code
,p_labour_union_member_flag => p_labour_union_member_flag
,p_hourly_salaried_code => p_hourly_salaried_code
,p_ass_attribute_category => p_ass_attribute_category
,p_ass_attribute1 => p_ass_attribute1
,p_ass_attribute2 => p_ass_attribute2
,p_ass_attribute3 => p_ass_attribute3
,p_ass_attribute4 => p_ass_attribute4
,p_ass_attribute5 => p_ass_attribute5
,p_ass_attribute6 => p_ass_attribute6
,p_ass_attribute7 => p_ass_attribute7
,p_ass_attribute8 => p_ass_attribute8
,p_ass_attribute9 => p_ass_attribute9
,p_ass_attribute10 => p_ass_attribute10
,p_ass_attribute11 => p_ass_attribute11
,p_ass_attribute12 => p_ass_attribute12
,p_ass_attribute13 => p_ass_attribute13
,p_ass_attribute14 => p_ass_attribute14
,p_ass_attribute15 => p_ass_attribute15
,p_ass_attribute16 => p_ass_attribute16
,p_ass_attribute17 => p_ass_attribute17
,p_ass_attribute18 => p_ass_attribute18
,p_ass_attribute19 => p_ass_attribute19
,p_ass_attribute20 => p_ass_attribute20
,p_ass_attribute21 => p_ass_attribute21
,p_ass_attribute22 => p_ass_attribute22
,p_ass_attribute23 => p_ass_attribute23
,p_ass_attribute24 => p_ass_attribute24
,p_ass_attribute25 => p_ass_attribute25
,p_ass_attribute26 => p_ass_attribute26
,p_ass_attribute27 => p_ass_attribute27
,p_ass_attribute28 => p_ass_attribute28
,p_ass_attribute29 => p_ass_attribute29
,p_ass_attribute30 => p_ass_attribute30
,p_title => p_title
,p_segment1 => l_scl_segment1
,p_segment2 => l_scl_segment2
,p_segment3 => l_scl_segment3
,p_segment4 => l_scl_segment4
,p_segment5 => l_scl_segment5
,p_segment6 => l_scl_segment6
,p_segment7 => l_scl_segment7
,p_segment8 => l_scl_segment8
,p_segment9 => l_scl_segment9
,p_segment10 => l_scl_segment10
,p_segment11 => l_scl_segment11
,p_segment12 => l_scl_segment12
,p_segment13 => l_scl_segment13
,p_segment14 => l_scl_segment14
,p_segment15 => l_scl_segment15
,p_segment16 => l_scl_segment16
,p_segment17 => l_scl_segment17
,p_segment18 => l_scl_segment18
,p_segment19 => l_scl_segment19
,p_segment20 => l_scl_segment20
,p_segment21 => l_scl_segment21
,p_segment22 => l_scl_segment22
,p_segment23 => l_scl_segment23
,p_segment24 => l_scl_segment24
,p_segment25 => l_scl_segment25
,p_segment26 => l_scl_segment26
,p_segment27 => l_scl_segment27
,p_segment28 => l_scl_segment28
,p_segment29 => l_scl_segment29
,p_segment30 => l_scl_segment30
-- Bug 944911
-- Amended p_concatendated_segments by p_concat_segments
,p_concat_segments => l_old_conc_segments
,p_contract_id => p_contract_id
,p_establishment_id => p_establishment_id
,p_collective_agreement_id => p_collective_agreement_id
,p_cagr_id_flex_num => p_cagr_id_flex_num
,p_cag_segment1 => l_cag_segment1
,p_cag_segment2 => l_cag_segment2
,p_cag_segment3 => l_cag_segment3
,p_cag_segment4 => l_cag_segment4
,p_cag_segment5 => l_cag_segment5
,p_cag_segment6 => l_cag_segment6
,p_cag_segment7 => l_cag_segment7
,p_cag_segment8 => l_cag_segment8
,p_cag_segment9 => l_cag_segment9
,p_cag_segment10 => l_cag_segment10
,p_cag_segment11 => l_cag_segment11
,p_cag_segment12 => l_cag_segment12
,p_cag_segment13 => l_cag_segment13
,p_cag_segment14 => l_cag_segment14
,p_cag_segment15 => l_cag_segment15
,p_cag_segment16 => l_cag_segment16
,p_cag_segment17 => l_cag_segment17
,p_cag_segment18 => l_cag_segment18
,p_cag_segment19 => l_cag_segment19
,p_cag_segment20 => l_cag_segment20
,p_notice_period => p_notice_period
,p_notice_period_uom => p_notice_period_uom
,p_employee_category => p_employee_category
,p_work_at_home => p_work_at_home
,p_job_post_source_name => p_job_post_source_name
,p_supervisor_assignment_id => p_supervisor_assignment_id
);
(p_module_name => 'UPDATE_EMP_ASG'
,p_hook_type => 'BP'
);
,p_organization_id => NVL(per_qh_maintain_update.p_qh_organization_id,l_organization_id) -- Fix For Bug # 8238220
,p_location_id => l_location_id);
per_qh_maintain_update.p_qh_organization_id := NULL; --- Added For Bug # 8238220
update_scl_concat_segs
(p_soft_coding_keyflex_id => l_soft_coding_keyflex_id
,p_concatenated_segments => l_concatenated_segments
);
p_datetrack_mode => p_datetrack_update_mode,
p_assignment_id => p_assignment_id,
p_object_version_number => p_object_version_number,
p_validation_start_date => l_unused_start_date,
p_validation_end_date => l_unused_end_date
);
,p_payroll_id_updated => l_payroll_id_updated
,p_other_manager_warning => l_other_manager_warning
,p_no_managers_warning => l_no_managers_warning
,p_org_now_no_manager_warning => l_org_now_no_manager_warning
,p_validation_start_date => l_validation_start_date
,p_validation_end_date => l_validation_end_date
,p_object_version_number => l_object_version_number
,p_effective_date => l_effective_date
,p_datetrack_mode => p_datetrack_update_mode
,p_contract_id => p_contract_id
,p_establishment_id => p_establishment_id
,p_collective_agreement_id => p_collective_agreement_id
,p_cagr_grade_def_id => l_cagr_grade_def_id
,p_cagr_id_flex_num => p_cagr_id_flex_num
,p_validate => FALSE
,p_hourly_salaried_warning => l_hourly_salaried_warning
,p_supervisor_assignment_id => p_supervisor_assignment_id
);
hr_assignment_bk2.update_emp_asg_a
(p_effective_date => l_effective_date
,p_datetrack_update_mode => p_datetrack_update_mode
,p_assignment_id => p_assignment_id
,p_object_version_number => p_object_version_number
,p_supervisor_id => p_supervisor_id
,p_assignment_number => p_assignment_number
,p_change_reason => p_change_reason
,p_assignment_status_type_id => p_assignment_status_type_id
,p_comments => p_comments
,p_date_probation_end => l_date_probation_end
,p_default_code_comb_id => p_default_code_comb_id
,p_frequency => p_frequency
,p_internal_address_line => p_internal_address_line
,p_manager_flag => p_manager_flag
,p_normal_hours => p_normal_hours
,p_perf_review_period => p_perf_review_period
,p_perf_review_period_frequency => p_perf_review_period_frequency
,p_probation_period => p_probation_period
,p_probation_unit => p_probation_unit
,p_projected_assignment_end => p_projected_assignment_end
,p_sal_review_period => p_sal_review_period
,p_sal_review_period_frequency => p_sal_review_period_frequency
,p_set_of_books_id => p_set_of_books_id
,p_source_type => p_source_type
,p_time_normal_finish => p_time_normal_finish
,p_time_normal_start => p_time_normal_start
,p_bargaining_unit_code => p_bargaining_unit_code
,p_labour_union_member_flag => p_labour_union_member_flag
,p_hourly_salaried_code => p_hourly_salaried_code
,p_ass_attribute_category => p_ass_attribute_category
,p_ass_attribute1 => p_ass_attribute1
,p_ass_attribute2 => p_ass_attribute2
,p_ass_attribute3 => p_ass_attribute3
,p_ass_attribute4 => p_ass_attribute4
,p_ass_attribute5 => p_ass_attribute5
,p_ass_attribute6 => p_ass_attribute6
,p_ass_attribute7 => p_ass_attribute7
,p_ass_attribute8 => p_ass_attribute8
,p_ass_attribute9 => p_ass_attribute9
,p_ass_attribute10 => p_ass_attribute10
,p_ass_attribute11 => p_ass_attribute11
,p_ass_attribute12 => p_ass_attribute12
,p_ass_attribute13 => p_ass_attribute13
,p_ass_attribute14 => p_ass_attribute14
,p_ass_attribute15 => p_ass_attribute15
,p_ass_attribute16 => p_ass_attribute16
,p_ass_attribute17 => p_ass_attribute17
,p_ass_attribute18 => p_ass_attribute18
,p_ass_attribute19 => p_ass_attribute19
,p_ass_attribute20 => p_ass_attribute20
,p_ass_attribute21 => p_ass_attribute21
,p_ass_attribute22 => p_ass_attribute22
,p_ass_attribute23 => p_ass_attribute23
,p_ass_attribute24 => p_ass_attribute24
,p_ass_attribute25 => p_ass_attribute25
,p_ass_attribute26 => p_ass_attribute26
,p_ass_attribute27 => p_ass_attribute27
,p_ass_attribute28 => p_ass_attribute28
,p_ass_attribute29 => p_ass_attribute29
,p_ass_attribute30 => p_ass_attribute30
,p_title => p_title
,p_segment1 => l_scl_segment1
,p_segment2 => l_scl_segment2
,p_segment3 => l_scl_segment3
,p_segment4 => l_scl_segment4
,p_segment5 => l_scl_segment5
,p_segment6 => l_scl_segment6
,p_segment7 => l_scl_segment7
,p_segment8 => l_scl_segment8
,p_segment9 => l_scl_segment9
,p_segment10 => l_scl_segment10
,p_segment11 => l_scl_segment11
,p_segment12 => l_scl_segment12
,p_segment13 => l_scl_segment13
,p_segment14 => l_scl_segment14
,p_segment15 => l_scl_segment15
,p_segment16 => l_scl_segment16
,p_segment17 => l_scl_segment17
,p_segment18 => l_scl_segment18
,p_segment19 => l_scl_segment19
,p_segment20 => l_scl_segment20
,p_segment21 => l_scl_segment21
,p_segment22 => l_scl_segment22
,p_segment23 => l_scl_segment23
,p_segment24 => l_scl_segment24
,p_segment25 => l_scl_segment25
,p_segment26 => l_scl_segment26
,p_segment27 => l_scl_segment27
,p_segment28 => l_scl_segment28
,p_segment29 => l_scl_segment29
,p_segment30 => l_scl_segment30
,p_concatenated_segments => l_concatenated_segments
,p_soft_coding_keyflex_id => l_soft_coding_keyflex_id
,p_comment_id => l_comment_id
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
,p_no_managers_warning => l_no_managers_warning
,p_other_manager_warning => l_other_manager_warning
-- Bug 944911
-- Added the new input param
,p_concat_segments => l_old_conc_segments
,p_contract_id => p_contract_id
,p_establishment_id => p_establishment_id
,p_collective_agreement_id => p_collective_agreement_id
,p_cagr_id_flex_num => p_cagr_id_flex_num
,p_cag_segment1 => l_cag_segment1
,p_cag_segment2 => l_cag_segment2
,p_cag_segment3 => l_cag_segment3
,p_cag_segment4 => l_cag_segment4
,p_cag_segment5 => l_cag_segment5
,p_cag_segment6 => l_cag_segment6
,p_cag_segment7 => l_cag_segment7
,p_cag_segment8 => l_cag_segment8
,p_cag_segment9 => l_cag_segment9
,p_cag_segment10 => l_cag_segment10
,p_cag_segment11 => l_cag_segment11
,p_cag_segment12 => l_cag_segment12
,p_cag_segment13 => l_cag_segment13
,p_cag_segment14 => l_cag_segment14
,p_cag_segment15 => l_cag_segment15
,p_cag_segment16 => l_cag_segment16
,p_cag_segment17 => l_cag_segment17
,p_cag_segment18 => l_cag_segment18
,p_cag_segment19 => l_cag_segment19
,p_cag_segment20 => l_cag_segment20
,p_notice_period => p_notice_period
,p_notice_period_uom => p_notice_period_uom
,p_employee_category => p_employee_category
,p_work_at_home => p_work_at_home
,p_job_post_source_name => p_job_post_source_name
,p_cagr_grade_def_id => l_cagr_grade_def_id
,p_cagr_concatenated_segments => l_cagr_concatenated_segments
,p_hourly_salaried_warning => l_hourly_salaried_warning
,p_supervisor_assignment_id => p_supervisor_assignment_id
);
(p_module_name => 'UPDATE_EMP_ASG'
,p_hook_type => 'AP'
);
,p_date_track_mode => p_datetrack_update_mode
,p_warning_mesg => l_gsp_post_process_warning
);
ROLLBACK TO update_emp_asg;
ROLLBACK TO update_emp_asg;
end update_emp_asg;
procedure update_cwk_asg
(p_validate in boolean
,p_effective_date in date
,p_datetrack_update_mode in varchar2
,p_assignment_id in number
,p_object_version_number in out nocopy number
,p_assignment_category in varchar2
,p_assignment_number in varchar2
,p_change_reason in varchar2
,p_comments in varchar2
,p_default_code_comb_id in number
,p_establishment_id in number
,p_frequency in varchar2
,p_internal_address_line in varchar2
,p_labour_union_member_flag in varchar2
,p_manager_flag in varchar2
,p_normal_hours in number
,p_project_title in varchar2
,p_set_of_books_id in number
,p_source_type in varchar2
,p_supervisor_id in number
,p_time_normal_finish in varchar2
,p_time_normal_start in varchar2
,p_title in varchar2
,p_vendor_assignment_number in varchar2
,p_vendor_employee_number in varchar2
,p_vendor_id in number
,p_vendor_site_id in number
,p_po_header_id in number
,p_po_line_id in number
,p_projected_assignment_end in date
,p_assignment_status_type_id in number
,p_concat_segments in varchar2
,p_attribute_category in varchar2
,p_attribute1 in varchar2
,p_attribute2 in varchar2
,p_attribute3 in varchar2
,p_attribute4 in varchar2
,p_attribute5 in varchar2
,p_attribute6 in varchar2
,p_attribute7 in varchar2
,p_attribute8 in varchar2
,p_attribute9 in varchar2
,p_attribute10 in varchar2
,p_attribute11 in varchar2
,p_attribute12 in varchar2
,p_attribute13 in varchar2
,p_attribute14 in varchar2
,p_attribute15 in varchar2
,p_attribute16 in varchar2
,p_attribute17 in varchar2
,p_attribute18 in varchar2
,p_attribute19 in varchar2
,p_attribute20 in varchar2
,p_attribute21 in varchar2
,p_attribute22 in varchar2
,p_attribute23 in varchar2
,p_attribute24 in varchar2
,p_attribute25 in varchar2
,p_attribute26 in varchar2
,p_attribute27 in varchar2
,p_attribute28 in varchar2
,p_attribute29 in varchar2
,p_attribute30 in varchar2
,p_scl_segment1 in varchar2
,p_scl_segment2 in varchar2
,p_scl_segment3 in varchar2
,p_scl_segment4 in varchar2
,p_scl_segment5 in varchar2
,p_scl_segment6 in varchar2
,p_scl_segment7 in varchar2
,p_scl_segment8 in varchar2
,p_scl_segment9 in varchar2
,p_scl_segment10 in varchar2
,p_scl_segment11 in varchar2
,p_scl_segment12 in varchar2
,p_scl_segment13 in varchar2
,p_scl_segment14 in varchar2
,p_scl_segment15 in varchar2
,p_scl_segment16 in varchar2
,p_scl_segment17 in varchar2
,p_scl_segment18 in varchar2
,p_scl_segment19 in varchar2
,p_scl_segment20 in varchar2
,p_scl_segment21 in varchar2
,p_scl_segment22 in varchar2
,p_scl_segment23 in varchar2
,p_scl_segment24 in varchar2
,p_scl_segment25 in varchar2
,p_scl_segment26 in varchar2
,p_scl_segment27 in varchar2
,p_scl_segment28 in varchar2
,p_scl_segment29 in varchar2
,p_scl_segment30 in varchar2
,p_supervisor_assignment_id in number
,p_org_now_no_manager_warning out nocopy boolean
,p_effective_start_date out nocopy date
,p_effective_end_date out nocopy date
,p_comment_id out nocopy number
,p_no_managers_warning out nocopy boolean
,p_other_manager_warning out nocopy boolean
,p_soft_coding_keyflex_id out nocopy number
,p_concatenated_segments out nocopy varchar2
,p_hourly_salaried_warning out nocopy boolean) IS
--
-- Declare cursors and local variables
--
-- Out variables
--
l_org_now_no_manager_warning BOOLEAN;
l_payroll_id_updated BOOLEAN;
l_proc VARCHAR2(72) := g_package||'update_cwk_asg';
select ast.per_system_status
from per_assignment_status_types ast,
per_all_assignments_f asg
where ast.assignment_status_type_id = asg.assignment_status_type_id
and asg.assignment_id = p_assignment_id
and l_effective_date between asg.effective_start_date and asg.effective_end_date;
select ast.per_system_status
from per_assignment_status_types ast
where ast.assignment_status_type_id = p_assignment_status_type_id;
select asg.assignment_type
, asg.business_group_id
, asg.soft_coding_keyflex_id
, asg.organization_id
, asg.location_id
from per_all_assignments_f asg
where asg.assignment_id = p_assignment_id
and l_effective_date between asg.effective_start_date
and asg.effective_end_date;
select plr.rule_mode id_flex_num
from pay_legislation_rules plr,
per_business_groups_perf pgr
where plr.legislation_code = pgr.legislation_code
and pgr.business_group_id = l_business_group_id
and plr.rule_type = 'CWK_S'
and exists
(select 1
from fnd_segment_attribute_values fsav
where to_char(fsav.id_flex_num) = plr.rule_mode --Fix For Bug 12813119
and fsav.application_id = 800
and fsav.id_flex_code = 'SCL'
and fsav.segment_attribute_type = 'ASSIGNMENT'
and fsav.attribute_value = 'Y')
and exists
(select 1
from pay_legislation_rules plr2
where plr2.legislation_code = plr.legislation_code
and plr2.rule_type = 'CWK_SDL'
and plr2.rule_mode = 'A') ;
select vendor_id,vendor_site_id from
per_all_assignments_f paf
where paf.assignment_id = p_assignment_id
and nvl(l_effective_date,sysdate) between paf.effective_start_date
and paf.effective_end_date;
SAVEPOINT update_cwk_asg;
hr_assignment_bkm.update_cwk_asg_b
(p_effective_date => l_effective_date
,p_datetrack_update_mode => p_datetrack_update_mode
,p_assignment_id => p_assignment_id
,p_object_version_number => p_object_version_number
,p_assignment_category => p_assignment_category
,p_assignment_number => p_assignment_number
,p_change_reason => p_change_reason
,p_comments => p_comments
,p_default_code_comb_id => p_default_code_comb_id
,p_establishment_id => p_establishment_id
,p_frequency => p_frequency
,p_internal_address_line => p_internal_address_line
,p_labour_union_member_flag => p_labour_union_member_flag
,p_manager_flag => p_manager_flag
,p_normal_hours => p_normal_hours
,p_project_title => p_project_title
,p_set_of_books_id => p_set_of_books_id
,p_source_type => p_source_type
,p_supervisor_id => p_supervisor_id
,p_time_normal_finish => p_time_normal_finish
,p_time_normal_start => p_time_normal_start
,p_title => p_title
,p_vendor_assignment_number => p_vendor_assignment_number
,p_vendor_employee_number => p_vendor_employee_number
,p_vendor_id => p_vendor_id
,p_vendor_site_id => p_vendor_site_id
,p_po_header_id => p_po_header_id
,p_po_line_id => p_po_line_id
,p_projected_assignment_end => l_projected_assignment_end
,p_assignment_status_type_id => p_assignment_status_type_id
,p_attribute_category => p_attribute_category
,p_attribute1 => p_attribute1
,p_attribute2 => p_attribute2
,p_attribute3 => p_attribute3
,p_attribute4 => p_attribute4
,p_attribute5 => p_attribute5
,p_attribute6 => p_attribute6
,p_attribute7 => p_attribute7
,p_attribute8 => p_attribute8
,p_attribute9 => p_attribute9
,p_attribute10 => p_attribute10
,p_attribute11 => p_attribute11
,p_attribute12 => p_attribute12
,p_attribute13 => p_attribute13
,p_attribute14 => p_attribute14
,p_attribute15 => p_attribute15
,p_attribute16 => p_attribute16
,p_attribute17 => p_attribute17
,p_attribute18 => p_attribute18
,p_attribute19 => p_attribute19
,p_attribute20 => p_attribute20
,p_attribute21 => p_attribute21
,p_attribute22 => p_attribute22
,p_attribute23 => p_attribute23
,p_attribute24 => p_attribute24
,p_attribute25 => p_attribute25
,p_attribute26 => p_attribute26
,p_attribute27 => p_attribute27
,p_attribute28 => p_attribute28
,p_attribute29 => p_attribute29
,p_attribute30 => p_attribute30
,p_scl_segment1 => p_scl_segment1
,p_scl_segment2 => p_scl_segment2
,p_scl_segment3 => p_scl_segment3
,p_scl_segment4 => p_scl_segment4
,p_scl_segment5 => p_scl_segment5
,p_scl_segment6 => p_scl_segment6
,p_scl_segment7 => p_scl_segment7
,p_scl_segment8 => p_scl_segment8
,p_scl_segment9 => p_scl_segment9
,p_scl_segment10 => p_scl_segment10
,p_scl_segment11 => p_scl_segment11
,p_scl_segment12 => p_scl_segment12
,p_scl_segment13 => p_scl_segment13
,p_scl_segment14 => p_scl_segment14
,p_scl_segment15 => p_scl_segment15
,p_scl_segment16 => p_scl_segment16
,p_scl_segment17 => p_scl_segment17
,p_scl_segment18 => p_scl_segment18
,p_scl_segment19 => p_scl_segment19
,p_scl_segment20 => p_scl_segment20
,p_scl_segment21 => p_scl_segment21
,p_scl_segment22 => p_scl_segment22
,p_scl_segment23 => p_scl_segment23
,p_scl_segment24 => p_scl_segment24
,p_scl_segment25 => p_scl_segment25
,p_scl_segment26 => p_scl_segment26
,p_scl_segment27 => p_scl_segment27
,p_scl_segment28 => p_scl_segment28
,p_scl_segment29 => p_scl_segment29
,p_scl_segment30 => p_scl_segment30
,p_supervisor_assignment_id => p_supervisor_assignment_id
);
(p_module_name => 'UPDATE_CWK_ASG'
,p_hook_type => 'BP'
);
update_scl_concat_segs
(p_soft_coding_keyflex_id => l_soft_coding_keyflex_id
,p_concatenated_segments => l_concatenated_segments);
p_datetrack_mode => p_datetrack_update_mode,
p_assignment_id => p_assignment_id,
p_object_version_number => p_object_version_number,
p_validation_start_date => l_unused_start_date,
p_validation_end_date => l_unused_end_date
);
,p_payroll_id_updated => l_payroll_id_updated
,p_other_manager_warning => l_other_manager_warning
,p_no_managers_warning => l_no_managers_warning
,p_org_now_no_manager_warning => l_org_now_no_manager_warning
,p_validation_start_date => l_validation_start_date
,p_validation_end_date => l_validation_end_date
,p_object_version_number => l_object_version_number
,p_effective_date => l_effective_date
,p_datetrack_mode => p_datetrack_update_mode
--,p_contract_id => p_contract_id
,p_establishment_id => p_establishment_id
--,p_collective_agreement_id => p_collective_agreement_id
,p_cagr_grade_def_id => l_cagr_grade_def_id
--,p_cagr_id_flex_num => p_cagr_id_flex_num
,p_validate => FALSE
,p_hourly_salaried_warning => l_hourly_salaried_warning
,p_supervisor_assignment_id => p_supervisor_assignment_id
);
hr_assignment_bkm.update_cwk_asg_a
(p_effective_date => l_effective_date
,p_datetrack_update_mode => p_datetrack_update_mode
,p_assignment_id => p_assignment_id
,p_object_version_number => p_object_version_number
,p_assignment_category => p_assignment_category
,p_assignment_number => p_assignment_number
,p_change_reason => p_change_reason
,p_comments => p_comments
,p_default_code_comb_id => p_default_code_comb_id
,p_establishment_id => p_establishment_id
,p_frequency => p_frequency
,p_internal_address_line => p_internal_address_line
,p_labour_union_member_flag => p_labour_union_member_flag
,p_manager_flag => p_manager_flag
,p_normal_hours => p_normal_hours
,p_project_title => p_project_title
,p_set_of_books_id => p_set_of_books_id
,p_source_type => p_source_type
,p_supervisor_id => p_supervisor_id
,p_time_normal_finish => p_time_normal_finish
,p_time_normal_start => p_time_normal_start
,p_title => p_title
,p_vendor_assignment_number => p_vendor_assignment_number
,p_vendor_employee_number => p_vendor_employee_number
,p_vendor_id => l_vendor_id
,p_vendor_site_id => p_vendor_site_id
,p_po_header_id => l_po_header_id
,p_po_line_id => p_po_line_id
,p_projected_assignment_end => l_projected_assignment_end
,p_assignment_status_type_id => p_assignment_status_type_id
,p_attribute_category => p_attribute_category
,p_attribute1 => p_attribute1
,p_attribute2 => p_attribute2
,p_attribute3 => p_attribute3
,p_attribute4 => p_attribute4
,p_attribute5 => p_attribute5
,p_attribute6 => p_attribute6
,p_attribute7 => p_attribute7
,p_attribute8 => p_attribute8
,p_attribute9 => p_attribute9
,p_attribute10 => p_attribute10
,p_attribute11 => p_attribute11
,p_attribute12 => p_attribute12
,p_attribute13 => p_attribute13
,p_attribute14 => p_attribute14
,p_attribute15 => p_attribute15
,p_attribute16 => p_attribute16
,p_attribute17 => p_attribute17
,p_attribute18 => p_attribute18
,p_attribute19 => p_attribute19
,p_attribute20 => p_attribute20
,p_attribute21 => p_attribute21
,p_attribute22 => p_attribute22
,p_attribute23 => p_attribute23
,p_attribute24 => p_attribute24
,p_attribute25 => p_attribute25
,p_attribute26 => p_attribute26
,p_attribute27 => p_attribute27
,p_attribute28 => p_attribute28
,p_attribute29 => p_attribute29
,p_attribute30 => p_attribute30
,p_scl_segment1 => p_scl_segment1
,p_scl_segment2 => p_scl_segment2
,p_scl_segment3 => p_scl_segment3
,p_scl_segment4 => p_scl_segment4
,p_scl_segment5 => p_scl_segment5
,p_scl_segment6 => p_scl_segment6
,p_scl_segment7 => p_scl_segment7
,p_scl_segment8 => p_scl_segment8
,p_scl_segment9 => p_scl_segment9
,p_scl_segment10 => p_scl_segment10
,p_scl_segment11 => p_scl_segment11
,p_scl_segment12 => p_scl_segment12
,p_scl_segment13 => p_scl_segment13
,p_scl_segment14 => p_scl_segment14
,p_scl_segment15 => p_scl_segment15
,p_scl_segment16 => p_scl_segment16
,p_scl_segment17 => p_scl_segment17
,p_scl_segment18 => p_scl_segment18
,p_scl_segment19 => p_scl_segment19
,p_scl_segment20 => p_scl_segment20
,p_scl_segment21 => p_scl_segment21
,p_scl_segment22 => p_scl_segment22
,p_scl_segment23 => p_scl_segment23
,p_scl_segment24 => p_scl_segment24
,p_scl_segment25 => p_scl_segment25
,p_scl_segment26 => p_scl_segment26
,p_scl_segment27 => p_scl_segment27
,p_scl_segment28 => p_scl_segment28
,p_scl_segment29 => p_scl_segment29
,p_scl_segment30 => p_scl_segment30
,p_org_now_no_manager_warning => l_org_now_no_manager_warning
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
,p_comment_id => l_comment_id
,p_no_managers_warning => l_no_managers_warning
,p_other_manager_warning => l_other_manager_warning
,p_soft_coding_keyflex_id => l_soft_coding_keyflex_id
,p_concatenated_segments => l_concatenated_segments
,p_hourly_salaried_warning => l_hourly_salaried_warning
,p_supervisor_assignment_id => p_supervisor_assignment_id
);
(p_module_name => 'UPDATE_CWK_ASG'
,p_hook_type => 'AP'
);
ROLLBACK TO update_cwk_asg;
ROLLBACK TO update_cwk_asg;
END update_cwk_asg;
procedure update_cwk_asg_criteria
(p_validate in boolean
,p_effective_date in date
,p_datetrack_update_mode in varchar2
,p_assignment_id in number
,p_called_from_mass_update in boolean
,p_object_version_number in out nocopy number
,p_grade_id in number
,p_position_id in number
,p_job_id in number
--
-- p_payroll_id included for future phases of cwk
--
--,p_payroll_id in number
,p_location_id in number
,p_organization_id in number
--
-- p_pay_basis_id for future phases of cwk
--
,p_pay_basis_id in number
,p_segment1 in varchar2
,p_segment2 in varchar2
,p_segment3 in varchar2
,p_segment4 in varchar2
,p_segment5 in varchar2
,p_segment6 in varchar2
,p_segment7 in varchar2
,p_segment8 in varchar2
,p_segment9 in varchar2
,p_segment10 in varchar2
,p_segment11 in varchar2
,p_segment12 in varchar2
,p_segment13 in varchar2
,p_segment14 in varchar2
,p_segment15 in varchar2
,p_segment16 in varchar2
,p_segment17 in varchar2
,p_segment18 in varchar2
,p_segment19 in varchar2
,p_segment20 in varchar2
,p_segment21 in varchar2
,p_segment22 in varchar2
,p_segment23 in varchar2
,p_segment24 in varchar2
,p_segment25 in varchar2
,p_segment26 in varchar2
,p_segment27 in varchar2
,p_segment28 in varchar2
,p_segment29 in varchar2
,p_segment30 in varchar2
,p_concat_segments in varchar2
,p_people_group_name out nocopy varchar2
,p_effective_start_date out nocopy date
,p_effective_end_date out nocopy date
,p_people_group_id out nocopy number
,p_org_now_no_manager_warning out nocopy boolean
,p_other_manager_warning out nocopy boolean
,p_spp_delete_warning out nocopy boolean
--
-- p_entries_changed_warning included for future phases of cwk
--
,p_entries_changed_warning out nocopy varchar2
,p_tax_district_changed_warning out nocopy boolean
) is
--
-- Declare cursors and local variables
--
-- Out variables
--
l_effective_end_date per_all_assignments_f.effective_end_date%TYPE;
l_payroll_id_updated boolean;
l_spp_delete_warning boolean := false; -- Bug 3545065
g_package || 'update_cwk_asg_criteria';
select bus.legislation_code
from per_business_groups_perf bus
where bus.business_group_id = l_business_group_id;
select element_entry_id
from pay_element_entries_f
where assignment_id = p_assignment_id
and creator_type = 'SP'
and l_validation_start_date between
effective_start_date and effective_end_date;
select bus.people_group_structure
from per_business_groups_perf bus
where bus.business_group_id = l_business_group_id;
select asg.effective_start_date
, asg.effective_end_date
from per_all_assignments_f asg
where asg.assignment_id=p_assignment_id
and ((sysdate between asg.effective_start_date
and asg.effective_end_date)
or
(sysdate
select asg.assignment_type
from per_all_assignments_f asg
where asg.assignment_id = p_assignment_id
and l_effective_date between asg.effective_start_date
and asg.effective_end_date;
l_dt_update_mode VARCHAR2(30);
l_new_dt_update_mode VARCHAR2(30);
IF p_called_from_mass_update THEN
--
if g_debug then
hr_utility.set_location(l_proc,40);
l_dt_update_mode := 'CORRECTION';
l_new_dt_update_mode := p_datetrack_update_mode;
l_dt_update_mode := p_datetrack_update_mode;
l_new_dt_update_mode := p_datetrack_update_mode;
SAVEPOINT update_cwk_asg_criteria;
hr_assignment_bko.update_cwk_asg_criteria_b
(p_effective_date => l_effective_date
,p_datetrack_update_mode => l_dt_update_mode
,p_assignment_id => p_assignment_id
,p_object_version_number => p_object_version_number
,p_grade_id => l_grade_id -- Bug 3545065
,p_position_id => p_position_id
,p_job_id => p_job_id
--,p_payroll_id => p_payroll_id
,p_location_id => p_location_id
,p_organization_id => p_organization_id
,p_pay_basis_id => p_pay_basis_id
,p_segment1 => p_segment1
,p_segment2 => p_segment2
,p_segment3 => p_segment3
,p_segment4 => p_segment4
,p_segment5 => p_segment5
,p_segment6 => p_segment6
,p_segment7 => p_segment7
,p_segment8 => p_segment8
,p_segment9 => p_segment9
,p_segment10 => p_segment10
,p_segment11 => p_segment11
,p_segment12 => p_segment12
,p_segment13 => p_segment13
,p_segment14 => p_segment14
,p_segment15 => p_segment15
,p_segment16 => p_segment16
,p_segment17 => p_segment17
,p_segment18 => p_segment18
,p_segment19 => p_segment19
,p_segment20 => p_segment20
,p_segment21 => p_segment21
,p_segment22 => p_segment22
,p_segment23 => p_segment23
,p_segment24 => p_segment24
,p_segment25 => p_segment25
,p_segment26 => p_segment26
,p_segment27 => p_segment27
,p_segment28 => p_segment28
,p_segment29 => p_segment29
,p_segment30 => p_segment30
,p_concat_segments => l_old_group_name);
(p_module_name => 'UPDATE_CWK_ASG_CRITERIA'
,p_hook_type => 'BP');
if (p_called_from_mass_update = TRUE and p_position_id is not null) then
if (l_job_id is null) or (l_org_id is null) then
hr_psf_shd.get_position_job_org(p_position_id, p_effective_date,
l_job_id, l_org_id);
update_pgp_concat_segs
(p_people_group_id => l_people_group_id
,p_group_name => l_people_group_name);
,p_payroll_id_updated => l_payroll_id_updated
,p_other_manager_warning => l_other_manager_warning
,p_no_managers_warning => l_no_managers_warning
,p_org_now_no_manager_warning => l_org_now_no_manager_warning
,p_validation_start_date => l_validation_start_date
,p_validation_end_date => l_validation_end_date
,p_object_version_number => l_object_version_number
,p_effective_date => l_effective_date
,p_datetrack_mode => l_dt_update_mode
,p_validate => FALSE
,p_hourly_salaried_warning => l_hourly_salaried_warning);
hr_entry_api.delete_element_entry
('DELETE'
,l_validation_start_date - 1
,l_element_entry_id);
,p_dt_mode => p_datetrack_update_mode
,p_validation_start_date => l_validation_start_date
,p_validation_end_date => l_validation_end_date
,p_entries_changed => l_entries_changed
);
,p_datetrack_mode => l_new_dt_update_mode
,p_validation_start_date => l_validation_start_date
,p_validation_end_date => l_validation_end_date
,p_grade_id => p_grade_id
,p_spp_delete_warning => l_spp_delete_warning);
l_spp_delete_warning := FALSE;
IF l_legislation_code = 'GB' AND l_payroll_id_updated THEN
--
if g_debug then
hr_utility.set_location(l_proc, 240);
hr_assignment_bko.update_cwk_asg_criteria_a
(p_effective_date => l_effective_date
,p_datetrack_update_mode => l_dt_update_mode
,p_assignment_id => p_assignment_id
,p_object_version_number => p_object_version_number
,p_grade_id => p_grade_id -- Bug 3545065
,p_position_id => p_position_id
,p_job_id => l_job_id -- Bug 3553286
--,p_payroll_id => p_payroll_id
,p_location_id => p_location_id
,p_organization_id => l_org_id -- Bug 3553286
,p_pay_basis_id => p_pay_basis_id
,p_segment1 => p_segment1
,p_segment2 => p_segment2
,p_segment3 => p_segment3
,p_segment4 => p_segment4
,p_segment5 => p_segment5
,p_segment6 => p_segment6
,p_segment7 => p_segment7
,p_segment8 => p_segment8
,p_segment9 => p_segment9
,p_segment10 => p_segment10
,p_segment11 => p_segment11
,p_segment12 => p_segment12
,p_segment13 => p_segment13
,p_segment14 => p_segment14
,p_segment15 => p_segment15
,p_segment16 => p_segment16
,p_segment17 => p_segment17
,p_segment18 => p_segment18
,p_segment19 => p_segment19
,p_segment20 => p_segment20
,p_segment21 => p_segment21
,p_segment22 => p_segment22
,p_segment23 => p_segment23
,p_segment24 => p_segment24
,p_segment25 => p_segment25
,p_segment26 => p_segment26
,p_segment27 => p_segment27
,p_segment28 => p_segment28
,p_segment29 => p_segment29
,p_segment30 => p_segment30
,p_people_group_name => l_people_group_name
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
,p_people_group_id => l_people_group_id
,p_org_now_no_manager_warning => l_org_now_no_manager_warning
,p_other_manager_warning => l_other_manager_warning
,p_spp_delete_warning => l_spp_delete_warning
,p_entries_changed_warning => l_entries_changed_warning
,p_tax_district_changed_warning => l_tax_district_changed_warning
,p_concat_segments => l_old_group_name);
(p_module_name => 'UPDATE_CWK_ASG_CRITERIA'
,p_hook_type => 'AP');
p_spp_delete_warning := l_spp_delete_warning;
ROLLBACK TO update_cwk_asg_criteria;
p_spp_delete_warning := l_spp_delete_warning;
ROLLBACK TO update_cwk_asg_criteria;
END update_cwk_asg_criteria;
procedure update_gb_emp_asg
(p_validate in boolean
,p_effective_date in date
,p_datetrack_update_mode in varchar2
,p_assignment_id in number
,p_object_version_number in out nocopy number
,p_supervisor_id in number
,p_assignment_number in varchar2
,p_change_reason in varchar2
,p_comments in varchar2
,p_date_probation_end in date
,p_default_code_comb_id in number
,p_frequency in varchar2
,p_internal_address_line in varchar2
,p_manager_flag in varchar2
,p_normal_hours in number
,p_perf_review_period in number
,p_perf_review_period_frequency in varchar2
,p_probation_period in number
,p_probation_unit in varchar2
,p_sal_review_period in number
,p_sal_review_period_frequency in varchar2
,p_set_of_books_id in number
,p_source_type in varchar2
,p_time_normal_finish in varchar2
,p_time_normal_start in varchar2
,p_bargaining_unit_code in varchar2
,p_labour_union_member_flag in varchar2
,p_hourly_salaried_code in varchar2
,p_ass_attribute_category in varchar2
,p_ass_attribute1 in varchar2
,p_ass_attribute2 in varchar2
,p_ass_attribute3 in varchar2
,p_ass_attribute4 in varchar2
,p_ass_attribute5 in varchar2
,p_ass_attribute6 in varchar2
,p_ass_attribute7 in varchar2
,p_ass_attribute8 in varchar2
,p_ass_attribute9 in varchar2
,p_ass_attribute10 in varchar2
,p_ass_attribute11 in varchar2
,p_ass_attribute12 in varchar2
,p_ass_attribute13 in varchar2
,p_ass_attribute14 in varchar2
,p_ass_attribute15 in varchar2
,p_ass_attribute16 in varchar2
,p_ass_attribute17 in varchar2
,p_ass_attribute18 in varchar2
,p_ass_attribute19 in varchar2
,p_ass_attribute20 in varchar2
,p_ass_attribute21 in varchar2
,p_ass_attribute22 in varchar2
,p_ass_attribute23 in varchar2
,p_ass_attribute24 in varchar2
,p_ass_attribute25 in varchar2
,p_ass_attribute26 in varchar2
,p_ass_attribute27 in varchar2
,p_ass_attribute28 in varchar2
,p_ass_attribute29 in varchar2
,p_ass_attribute30 in varchar2
,p_title in varchar2
,p_supervisor_assignment_id in number
,p_comment_id out nocopy number
,p_effective_start_date out nocopy date
,p_effective_end_date out nocopy date
,p_no_managers_warning out nocopy boolean
,p_other_manager_warning out nocopy boolean
) is
--
-- Declare cursors and local variables
--
l_proc varchar2(72);
l_proc := g_package||'update_gb_emp_asg';
hr_assignment_api.update_gb_emp_asg
(p_validate => p_validate
,p_effective_date => p_effective_date
,p_datetrack_update_mode => p_datetrack_update_mode
,p_assignment_id => p_assignment_id
,p_object_version_number => p_object_version_number
,p_supervisor_id => p_supervisor_id
,p_assignment_number => p_assignment_number
,p_change_reason => p_change_reason
,p_comments => p_comments
,p_date_probation_end => p_date_probation_end
,p_default_code_comb_id => p_default_code_comb_id
,p_frequency => p_frequency
,p_internal_address_line => p_internal_address_line
,p_manager_flag => p_manager_flag
,p_normal_hours => p_normal_hours
,p_perf_review_period => p_perf_review_period
,p_perf_review_period_frequency => p_perf_review_period_frequency
,p_probation_period => p_probation_period
,p_probation_unit => p_probation_unit
,p_sal_review_period => p_sal_review_period
,p_sal_review_period_frequency => p_sal_review_period_frequency
,p_set_of_books_id => p_set_of_books_id
,p_source_type => p_source_type
,p_time_normal_finish => p_time_normal_finish
,p_time_normal_start => p_time_normal_start
,p_bargaining_unit_code => p_bargaining_unit_code
,p_labour_union_member_flag => p_labour_union_member_flag
,p_hourly_salaried_code => p_hourly_salaried_code
,p_ass_attribute_category => p_ass_attribute_category
,p_ass_attribute1 => p_ass_attribute1
,p_ass_attribute2 => p_ass_attribute2
,p_ass_attribute3 => p_ass_attribute3
,p_ass_attribute4 => p_ass_attribute4
,p_ass_attribute5 => p_ass_attribute5
,p_ass_attribute6 => p_ass_attribute6
,p_ass_attribute7 => p_ass_attribute7
,p_ass_attribute8 => p_ass_attribute8
,p_ass_attribute9 => p_ass_attribute9
,p_ass_attribute10 => p_ass_attribute10
,p_ass_attribute11 => p_ass_attribute11
,p_ass_attribute12 => p_ass_attribute12
,p_ass_attribute13 => p_ass_attribute13
,p_ass_attribute14 => p_ass_attribute14
,p_ass_attribute15 => p_ass_attribute15
,p_ass_attribute16 => p_ass_attribute16
,p_ass_attribute17 => p_ass_attribute17
,p_ass_attribute18 => p_ass_attribute18
,p_ass_attribute19 => p_ass_attribute19
,p_ass_attribute20 => p_ass_attribute20
,p_ass_attribute21 => p_ass_attribute21
,p_ass_attribute22 => p_ass_attribute22
,p_ass_attribute23 => p_ass_attribute23
,p_ass_attribute24 => p_ass_attribute24
,p_ass_attribute25 => p_ass_attribute25
,p_ass_attribute26 => p_ass_attribute26
,p_ass_attribute27 => p_ass_attribute27
,p_ass_attribute28 => p_ass_attribute28
,p_ass_attribute29 => p_ass_attribute29
,p_ass_attribute30 => p_ass_attribute30
,p_title => p_title
,p_cagr_grade_def_id => l_cagr_grade_def_id
,p_cagr_concatenated_segments => l_cagr_concatenated_segments
,p_soft_coding_keyflex_id => l_soft_coding_keyflex_id
,p_comment_id => p_comment_id
,p_effective_start_date => p_effective_start_date
,p_effective_end_date => p_effective_end_date
,p_concatenated_segments => l_concatenated_segments
,p_no_managers_warning => p_no_managers_warning
,p_other_manager_warning => p_other_manager_warning
,p_supervisor_assignment_id => p_supervisor_assignment_id
);
end update_gb_emp_asg;
procedure update_gb_emp_asg
(p_validate in boolean
,p_effective_date in date
,p_datetrack_update_mode in varchar2
,p_assignment_id in number
,p_object_version_number in out nocopy number
,p_supervisor_id in number
,p_assignment_number in varchar2
,p_change_reason in varchar2
,p_comments in varchar2
,p_date_probation_end in date
,p_default_code_comb_id in number
,p_frequency in varchar2
,p_internal_address_line in varchar2
,p_manager_flag in varchar2
,p_normal_hours in number
,p_perf_review_period in number
,p_perf_review_period_frequency in varchar2
,p_probation_period in number
,p_probation_unit in varchar2
,p_sal_review_period in number
,p_sal_review_period_frequency in varchar2
,p_set_of_books_id in number
,p_source_type in varchar2
,p_time_normal_finish in varchar2
,p_time_normal_start in varchar2
,p_bargaining_unit_code in varchar2
,p_labour_union_member_flag in varchar2
,p_hourly_salaried_code in varchar2
,p_ass_attribute_category in varchar2
,p_ass_attribute1 in varchar2
,p_ass_attribute2 in varchar2
,p_ass_attribute3 in varchar2
,p_ass_attribute4 in varchar2
,p_ass_attribute5 in varchar2
,p_ass_attribute6 in varchar2
,p_ass_attribute7 in varchar2
,p_ass_attribute8 in varchar2
,p_ass_attribute9 in varchar2
,p_ass_attribute10 in varchar2
,p_ass_attribute11 in varchar2
,p_ass_attribute12 in varchar2
,p_ass_attribute13 in varchar2
,p_ass_attribute14 in varchar2
,p_ass_attribute15 in varchar2
,p_ass_attribute16 in varchar2
,p_ass_attribute17 in varchar2
,p_ass_attribute18 in varchar2
,p_ass_attribute19 in varchar2
,p_ass_attribute20 in varchar2
,p_ass_attribute21 in varchar2
,p_ass_attribute22 in varchar2
,p_ass_attribute23 in varchar2
,p_ass_attribute24 in varchar2
,p_ass_attribute25 in varchar2
,p_ass_attribute26 in varchar2
,p_ass_attribute27 in varchar2
,p_ass_attribute28 in varchar2
,p_ass_attribute29 in varchar2
,p_ass_attribute30 in varchar2
,p_title in varchar2
,p_contract_id in number
,p_establishment_id in number
,p_collective_agreement_id in number
,p_cagr_id_flex_num in number
,p_notice_period in number
,p_notice_period_uom in varchar2
,p_employee_category in varchar2
,p_work_at_home in varchar2
,p_job_post_source_name in varchar2
,p_supervisor_assignment_id in number
,p_cagr_grade_def_id out nocopy number
,p_cagr_concatenated_segments out nocopy varchar2
,p_concatenated_segments out nocopy varchar2
,p_soft_coding_keyflex_id out nocopy number
,p_comment_id out nocopy number
,p_effective_start_date out nocopy date
,p_effective_end_date out nocopy date
,p_no_managers_warning out nocopy boolean
,p_other_manager_warning out nocopy boolean
) is
--
-- Declare cursors and local variables
--
l_proc varchar2(72);
select bgp.legislation_code
from per_all_assignments_f asg,
per_business_groups_perf bgp
where asg.business_group_id = bgp.business_group_id
and asg.assignment_id = c_assignment_id
and c_effective_date
between effective_start_date and effective_end_date;
l_proc := g_package||'update_gb_emp_asg';
hr_assignment_api.update_emp_asg
(p_validate => p_validate
,p_effective_date => p_effective_date
,p_datetrack_update_mode => p_datetrack_update_mode
,p_assignment_id => p_assignment_id
,p_object_version_number => p_object_version_number
,p_supervisor_id => p_supervisor_id
,p_assignment_number => p_assignment_number
,p_change_reason => p_change_reason
,p_comments => p_comments
,p_date_probation_end => p_date_probation_end
,p_default_code_comb_id => p_default_code_comb_id
,p_frequency => p_frequency
,p_internal_address_line => p_internal_address_line
,p_manager_flag => p_manager_flag
,p_normal_hours => p_normal_hours
,p_perf_review_period => p_perf_review_period
,p_perf_review_period_frequency => p_perf_review_period_frequency
,p_probation_period => p_probation_period
,p_probation_unit => p_probation_unit
,p_sal_review_period => p_sal_review_period
,p_sal_review_period_frequency => p_sal_review_period_frequency
,p_set_of_books_id => p_set_of_books_id
,p_source_type => p_source_type
,p_time_normal_finish => p_time_normal_finish
,p_time_normal_start => p_time_normal_start
,p_bargaining_unit_code => p_bargaining_unit_code
,p_labour_union_member_flag => p_labour_union_member_flag
,p_hourly_salaried_code => p_hourly_salaried_code
,p_ass_attribute_category => p_ass_attribute_category
,p_ass_attribute1 => p_ass_attribute1
,p_ass_attribute2 => p_ass_attribute2
,p_ass_attribute3 => p_ass_attribute3
,p_ass_attribute4 => p_ass_attribute4
,p_ass_attribute5 => p_ass_attribute5
,p_ass_attribute6 => p_ass_attribute6
,p_ass_attribute7 => p_ass_attribute7
,p_ass_attribute8 => p_ass_attribute8
,p_ass_attribute9 => p_ass_attribute9
,p_ass_attribute10 => p_ass_attribute10
,p_ass_attribute11 => p_ass_attribute11
,p_ass_attribute12 => p_ass_attribute12
,p_ass_attribute13 => p_ass_attribute13
,p_ass_attribute14 => p_ass_attribute14
,p_ass_attribute15 => p_ass_attribute15
,p_ass_attribute16 => p_ass_attribute16
,p_ass_attribute17 => p_ass_attribute17
,p_ass_attribute18 => p_ass_attribute18
,p_ass_attribute19 => p_ass_attribute19
,p_ass_attribute20 => p_ass_attribute20
,p_ass_attribute21 => p_ass_attribute21
,p_ass_attribute22 => p_ass_attribute22
,p_ass_attribute23 => p_ass_attribute23
,p_ass_attribute24 => p_ass_attribute24
,p_ass_attribute25 => p_ass_attribute25
,p_ass_attribute26 => p_ass_attribute26
,p_ass_attribute27 => p_ass_attribute27
,p_ass_attribute28 => p_ass_attribute28
,p_ass_attribute29 => p_ass_attribute29
,p_ass_attribute30 => p_ass_attribute30
,p_notice_period => p_notice_period
,p_notice_period_uom => p_notice_period_uom
,p_employee_category => p_employee_category
,p_work_at_home => p_work_at_home
,p_job_post_source_name => p_job_post_source_name
,p_title => p_title
,p_contract_id => p_contract_id
,p_establishment_id => p_establishment_id
,p_collective_agreement_id => p_collective_agreement_id
,p_cagr_id_flex_num => p_cagr_id_flex_num
,p_cagr_grade_def_id => p_cagr_grade_def_id
,p_cagr_concatenated_segments => p_cagr_concatenated_segments
,p_soft_coding_keyflex_id => l_soft_coding_keyflex_id
,p_comment_id => p_comment_id
,p_effective_start_date => p_effective_start_date
,p_effective_end_date => p_effective_end_date
,p_concatenated_segments => l_concatenated_segments
,p_no_managers_warning => p_no_managers_warning
,p_other_manager_warning => p_other_manager_warning
,p_supervisor_assignment_id => p_supervisor_assignment_id
);
end update_gb_emp_asg;
procedure update_gb_emp_asg
(p_validate in boolean
,p_effective_date in date
,p_datetrack_update_mode in varchar2
,p_assignment_id in number
,p_object_version_number in out nocopy number
,p_supervisor_id in number
,p_assignment_number in varchar2
,p_change_reason in varchar2
,p_comments in varchar2
,p_date_probation_end in date
,p_default_code_comb_id in number
,p_frequency in varchar2
,p_internal_address_line in varchar2
,p_manager_flag in varchar2
,p_normal_hours in number
,p_perf_review_period in number
,p_perf_review_period_frequency in varchar2
,p_probation_period in number
,p_probation_unit in varchar2
,p_sal_review_period in number
,p_sal_review_period_frequency in varchar2
,p_set_of_books_id in number
,p_source_type in varchar2
,p_time_normal_finish in varchar2
,p_time_normal_start in varchar2
,p_bargaining_unit_code in varchar2
,p_labour_union_member_flag in varchar2
,p_hourly_salaried_code in varchar2
,p_ass_attribute_category in varchar2
,p_ass_attribute1 in varchar2
,p_ass_attribute2 in varchar2
,p_ass_attribute3 in varchar2
,p_ass_attribute4 in varchar2
,p_ass_attribute5 in varchar2
,p_ass_attribute6 in varchar2
,p_ass_attribute7 in varchar2
,p_ass_attribute8 in varchar2
,p_ass_attribute9 in varchar2
,p_ass_attribute10 in varchar2
,p_ass_attribute11 in varchar2
,p_ass_attribute12 in varchar2
,p_ass_attribute13 in varchar2
,p_ass_attribute14 in varchar2
,p_ass_attribute15 in varchar2
,p_ass_attribute16 in varchar2
,p_ass_attribute17 in varchar2
,p_ass_attribute18 in varchar2
,p_ass_attribute19 in varchar2
,p_ass_attribute20 in varchar2
,p_ass_attribute21 in varchar2
,p_ass_attribute22 in varchar2
,p_ass_attribute23 in varchar2
,p_ass_attribute24 in varchar2
,p_ass_attribute25 in varchar2
,p_ass_attribute26 in varchar2
,p_ass_attribute27 in varchar2
,p_ass_attribute28 in varchar2
,p_ass_attribute29 in varchar2
,p_ass_attribute30 in varchar2
,p_title in varchar2
,p_contract_id in number
,p_establishment_id in number
,p_collective_agreement_id in number
,p_cagr_id_flex_num in number
,p_notice_period in number
,p_notice_period_uom in varchar2
,p_employee_category in varchar2
,p_work_at_home in varchar2
,p_job_post_source_name in varchar2
,p_supervisor_assignment_id in number
,p_cagr_grade_def_id out nocopy number
,p_cagr_concatenated_segments out nocopy varchar2
,p_concatenated_segments out nocopy varchar2
,p_soft_coding_keyflex_id out nocopy number
,p_comment_id out nocopy number
,p_effective_start_date out nocopy date
,p_effective_end_date out nocopy date
,p_no_managers_warning out nocopy boolean
,p_other_manager_warning out nocopy boolean
,p_hourly_salaried_warning out nocopy boolean
) is
--
-- Declare cursors and local variables
--
l_proc varchar2(72);
select bgp.legislation_code
from per_all_assignments_f asg,
per_business_groups_perf bgp
where asg.business_group_id = bgp.business_group_id
and asg.assignment_id = c_assignment_id
and c_effective_date
between effective_start_date and effective_end_date;
l_proc := g_package||'update_gb_emp_asg';
hr_assignment_api.update_emp_asg
(p_validate => p_validate
,p_effective_date => p_effective_date
,p_datetrack_update_mode => p_datetrack_update_mode
,p_assignment_id => p_assignment_id
,p_object_version_number => p_object_version_number
,p_supervisor_id => p_supervisor_id
,p_assignment_number => p_assignment_number
,p_change_reason => p_change_reason
,p_comments => p_comments
,p_date_probation_end => p_date_probation_end
,p_default_code_comb_id => p_default_code_comb_id
,p_frequency => p_frequency
,p_internal_address_line => p_internal_address_line
,p_manager_flag => p_manager_flag
,p_normal_hours => p_normal_hours
,p_perf_review_period => p_perf_review_period
,p_perf_review_period_frequency => p_perf_review_period_frequency
,p_probation_period => p_probation_period
,p_probation_unit => p_probation_unit
,p_sal_review_period => p_sal_review_period
,p_sal_review_period_frequency => p_sal_review_period_frequency
,p_set_of_books_id => p_set_of_books_id
,p_source_type => p_source_type
,p_time_normal_finish => p_time_normal_finish
,p_time_normal_start => p_time_normal_start
,p_bargaining_unit_code => p_bargaining_unit_code
,p_labour_union_member_flag => p_labour_union_member_flag
,p_hourly_salaried_code => p_hourly_salaried_code
,p_ass_attribute_category => p_ass_attribute_category
,p_ass_attribute1 => p_ass_attribute1
,p_ass_attribute2 => p_ass_attribute2
,p_ass_attribute3 => p_ass_attribute3
,p_ass_attribute4 => p_ass_attribute4
,p_ass_attribute5 => p_ass_attribute5
,p_ass_attribute6 => p_ass_attribute6
,p_ass_attribute7 => p_ass_attribute7
,p_ass_attribute8 => p_ass_attribute8
,p_ass_attribute9 => p_ass_attribute9
,p_ass_attribute10 => p_ass_attribute10
,p_ass_attribute11 => p_ass_attribute11
,p_ass_attribute12 => p_ass_attribute12
,p_ass_attribute13 => p_ass_attribute13
,p_ass_attribute14 => p_ass_attribute14
,p_ass_attribute15 => p_ass_attribute15
,p_ass_attribute16 => p_ass_attribute16
,p_ass_attribute17 => p_ass_attribute17
,p_ass_attribute18 => p_ass_attribute18
,p_ass_attribute19 => p_ass_attribute19
,p_ass_attribute20 => p_ass_attribute20
,p_ass_attribute21 => p_ass_attribute21
,p_ass_attribute22 => p_ass_attribute22
,p_ass_attribute23 => p_ass_attribute23
,p_ass_attribute24 => p_ass_attribute24
,p_ass_attribute25 => p_ass_attribute25
,p_ass_attribute26 => p_ass_attribute26
,p_ass_attribute27 => p_ass_attribute27
,p_ass_attribute28 => p_ass_attribute28
,p_ass_attribute29 => p_ass_attribute29
,p_ass_attribute30 => p_ass_attribute30
,p_notice_period => p_notice_period
,p_notice_period_uom => p_notice_period_uom
,p_employee_category => p_employee_category
,p_work_at_home => p_work_at_home
,p_job_post_source_name => p_job_post_source_name
,p_title => p_title
,p_contract_id => p_contract_id
,p_establishment_id => p_establishment_id
,p_collective_agreement_id => p_collective_agreement_id
,p_cagr_id_flex_num => p_cagr_id_flex_num
,p_cagr_grade_def_id => p_cagr_grade_def_id
,p_cagr_concatenated_segments => p_cagr_concatenated_segments
,p_soft_coding_keyflex_id => l_soft_coding_keyflex_id
,p_comment_id => p_comment_id
,p_effective_start_date => p_effective_start_date
,p_effective_end_date => p_effective_end_date
,p_concatenated_segments => l_concatenated_segments
,p_no_managers_warning => p_no_managers_warning
,p_other_manager_warning => p_other_manager_warning
,p_hourly_salaried_warning => p_hourly_salaried_warning
,p_supervisor_assignment_id => p_supervisor_assignment_id
);
end update_gb_emp_asg;
procedure update_us_emp_asg
(p_validate in boolean
,p_effective_date in date
,p_datetrack_update_mode in varchar2
,p_assignment_id in number
,p_object_version_number in out nocopy number
,p_supervisor_id in number
,p_assignment_number in varchar2
,p_change_reason in varchar2
,p_comments in varchar2
,p_date_probation_end in date
,p_default_code_comb_id in number
,p_frequency in varchar2
,p_internal_address_line in varchar2
,p_manager_flag in varchar2
,p_normal_hours in number
,p_perf_review_period in number
,p_perf_review_period_frequency in varchar2
,p_probation_period in number
,p_probation_unit in varchar2
,p_sal_review_period in number
,p_sal_review_period_frequency in varchar2
,p_set_of_books_id in number
,p_source_type in varchar2
,p_time_normal_finish in varchar2
,p_time_normal_start in varchar2
,p_bargaining_unit_code in varchar2
,p_labour_union_member_flag in varchar2
,p_hourly_salaried_code in varchar2
,p_ass_attribute_category in varchar2
,p_ass_attribute1 in varchar2
,p_ass_attribute2 in varchar2
,p_ass_attribute3 in varchar2
,p_ass_attribute4 in varchar2
,p_ass_attribute5 in varchar2
,p_ass_attribute6 in varchar2
,p_ass_attribute7 in varchar2
,p_ass_attribute8 in varchar2
,p_ass_attribute9 in varchar2
,p_ass_attribute10 in varchar2
,p_ass_attribute11 in varchar2
,p_ass_attribute12 in varchar2
,p_ass_attribute13 in varchar2
,p_ass_attribute14 in varchar2
,p_ass_attribute15 in varchar2
,p_ass_attribute16 in varchar2
,p_ass_attribute17 in varchar2
,p_ass_attribute18 in varchar2
,p_ass_attribute19 in varchar2
,p_ass_attribute20 in varchar2
,p_ass_attribute21 in varchar2
,p_ass_attribute22 in varchar2
,p_ass_attribute23 in varchar2
,p_ass_attribute24 in varchar2
,p_ass_attribute25 in varchar2
,p_ass_attribute26 in varchar2
,p_ass_attribute27 in varchar2
,p_ass_attribute28 in varchar2
,p_ass_attribute29 in varchar2
,p_ass_attribute30 in varchar2
,p_title in varchar2
,p_tax_unit in varchar2
,p_timecard_approver in varchar2
,p_timecard_required in varchar2
,p_work_schedule in varchar2
,p_shift in varchar2
,p_spouse_salary in varchar2
,p_legal_representative in varchar2
,p_wc_override_code in varchar2
,p_eeo_1_establishment in varchar2
,p_supervisor_assignment_id in number
,p_comment_id out nocopy number
,p_soft_coding_keyflex_id out nocopy number
,p_effective_start_date out nocopy date
,p_effective_end_date out nocopy date
-- Bug 944911
-- Amended p_concatenated_segments to be out
-- Added p_concat_segments - in param
,p_concatenated_segments out nocopy varchar2
,p_concat_segments in varchar2
,p_no_managers_warning out nocopy boolean
,p_other_manager_warning out nocopy boolean
)
is
--
-- Declare cursors and local variables
--
l_proc varchar2(72);
l_proc := g_package||'update_us_emp_asg';
hr_assignment_api.update_emp_asg
(p_validate => p_validate
,p_effective_date => p_effective_date
,p_datetrack_update_mode => p_datetrack_update_mode
,p_assignment_id => p_assignment_id
,p_object_version_number => p_object_version_number
,p_supervisor_id => p_supervisor_id
,p_assignment_number => p_assignment_number
,p_change_reason => p_change_reason
,p_comments => p_comments
,p_date_probation_end => p_date_probation_end
,p_default_code_comb_id => p_default_code_comb_id
,p_frequency => p_frequency
,p_internal_address_line => p_internal_address_line
,p_manager_flag => p_manager_flag
,p_normal_hours => p_normal_hours
,p_perf_review_period => p_perf_review_period
,p_perf_review_period_frequency => p_perf_review_period_frequency
,p_probation_period => p_probation_period
,p_probation_unit => p_probation_unit
,p_sal_review_period => p_sal_review_period
,p_sal_review_period_frequency => p_sal_review_period_frequency
,p_set_of_books_id => p_set_of_books_id
,p_source_type => p_source_type
,p_time_normal_finish => p_time_normal_finish
,p_time_normal_start => p_time_normal_start
,p_bargaining_unit_code => p_bargaining_unit_code
,p_labour_union_member_flag => p_labour_union_member_flag
,p_hourly_salaried_code => p_hourly_salaried_code
,p_ass_attribute_category => p_ass_attribute_category
,p_ass_attribute1 => p_ass_attribute1
,p_ass_attribute2 => p_ass_attribute2
,p_ass_attribute3 => p_ass_attribute3
,p_ass_attribute4 => p_ass_attribute4
,p_ass_attribute5 => p_ass_attribute5
,p_ass_attribute6 => p_ass_attribute6
,p_ass_attribute7 => p_ass_attribute7
,p_ass_attribute8 => p_ass_attribute8
,p_ass_attribute9 => p_ass_attribute9
,p_ass_attribute10 => p_ass_attribute10
,p_ass_attribute11 => p_ass_attribute11
,p_ass_attribute12 => p_ass_attribute12
,p_ass_attribute13 => p_ass_attribute13
,p_ass_attribute14 => p_ass_attribute14
,p_ass_attribute15 => p_ass_attribute15
,p_ass_attribute16 => p_ass_attribute16
,p_ass_attribute17 => p_ass_attribute17
,p_ass_attribute18 => p_ass_attribute18
,p_ass_attribute19 => p_ass_attribute19
,p_ass_attribute20 => p_ass_attribute20
,p_ass_attribute21 => p_ass_attribute21
,p_ass_attribute22 => p_ass_attribute22
,p_ass_attribute23 => p_ass_attribute23
,p_ass_attribute24 => p_ass_attribute24
,p_ass_attribute25 => p_ass_attribute25
,p_ass_attribute26 => p_ass_attribute26
,p_ass_attribute27 => p_ass_attribute27
,p_ass_attribute28 => p_ass_attribute28
,p_ass_attribute29 => p_ass_attribute29
,p_ass_attribute30 => p_ass_attribute30
,p_title => p_title
,p_segment1 => p_tax_unit
,p_segment2 => p_timecard_approver
,p_segment3 => p_timecard_required
,p_segment4 => p_work_schedule
,p_segment5 => p_shift
,p_segment6 => p_spouse_salary
,p_segment7 => p_legal_representative
,p_segment8 => p_wc_override_code
,p_segment9 => p_eeo_1_establishment
,p_soft_coding_keyflex_id => p_soft_coding_keyflex_id
,p_comment_id => p_comment_id
,p_effective_start_date => p_effective_start_date
,p_effective_end_date => p_effective_end_date
-- Bug 1889914
,p_cagr_grade_def_id => l_cagr_grade_def_id
,p_cagr_concatenated_segments => l_cagr_concatenated_segments
-- Bug 944911
-- Added new param
,p_concatenated_segments => p_concatenated_segments
,p_concat_segments => p_concat_segments
,p_no_managers_warning => p_no_managers_warning
,p_other_manager_warning => p_other_manager_warning
,p_supervisor_assignment_id => p_supervisor_assignment_id
);
end update_us_emp_asg;
procedure update_us_emp_asg
(p_validate in boolean
,p_effective_date in date
,p_datetrack_update_mode in varchar2
,p_assignment_id in number
,p_object_version_number in out nocopy number
,p_supervisor_id in number
,p_assignment_number in varchar2
,p_change_reason in varchar2
,p_comments in varchar2
,p_date_probation_end in date
,p_default_code_comb_id in number
,p_frequency in varchar2
,p_internal_address_line in varchar2
,p_manager_flag in varchar2
,p_normal_hours in number
,p_perf_review_period in number
,p_perf_review_period_frequency in varchar2
,p_probation_period in number
,p_probation_unit in varchar2
,p_sal_review_period in number
,p_sal_review_period_frequency in varchar2
,p_set_of_books_id in number
,p_source_type in varchar2
,p_time_normal_finish in varchar2
,p_time_normal_start in varchar2
,p_bargaining_unit_code in varchar2
,p_labour_union_member_flag in varchar2
,p_hourly_salaried_code in varchar2
,p_ass_attribute_category in varchar2
,p_ass_attribute1 in varchar2
,p_ass_attribute2 in varchar2
,p_ass_attribute3 in varchar2
,p_ass_attribute4 in varchar2
,p_ass_attribute5 in varchar2
,p_ass_attribute6 in varchar2
,p_ass_attribute7 in varchar2
,p_ass_attribute8 in varchar2
,p_ass_attribute9 in varchar2
,p_ass_attribute10 in varchar2
,p_ass_attribute11 in varchar2
,p_ass_attribute12 in varchar2
,p_ass_attribute13 in varchar2
,p_ass_attribute14 in varchar2
,p_ass_attribute15 in varchar2
,p_ass_attribute16 in varchar2
,p_ass_attribute17 in varchar2
,p_ass_attribute18 in varchar2
,p_ass_attribute19 in varchar2
,p_ass_attribute20 in varchar2
,p_ass_attribute21 in varchar2
,p_ass_attribute22 in varchar2
,p_ass_attribute23 in varchar2
,p_ass_attribute24 in varchar2
,p_ass_attribute25 in varchar2
,p_ass_attribute26 in varchar2
,p_ass_attribute27 in varchar2
,p_ass_attribute28 in varchar2
,p_ass_attribute29 in varchar2
,p_ass_attribute30 in varchar2
,p_title in varchar2
,p_tax_unit in varchar2
,p_timecard_approver in varchar2
,p_timecard_required in varchar2
,p_work_schedule in varchar2
,p_shift in varchar2
,p_spouse_salary in varchar2
,p_legal_representative in varchar2
,p_wc_override_code in varchar2
,p_eeo_1_establishment in varchar2
-- Added for bug 1889914
,p_contract_id in number
,p_establishment_id in number
,p_collective_agreement_id in number
,p_cagr_id_flex_num in number
,p_notice_period in number
,p_notice_period_uom in varchar2
,p_employee_category in varchar2
,p_work_at_home in varchar2
,p_job_post_source_name in varchar2
,p_supervisor_assignment_id in number
,p_cagr_grade_def_id out nocopy number
,p_cagr_concatenated_segments out nocopy varchar2
-- End 1889914
,p_comment_id out nocopy number
,p_soft_coding_keyflex_id out nocopy number
,p_effective_start_date out nocopy date
,p_effective_end_date out nocopy date
-- Bug 944911
-- Amended p_concatenated_segments to be out
-- Added p_concat_segments - in param
,p_concatenated_segments out nocopy varchar2
,p_concat_segments in varchar2
,p_no_managers_warning out nocopy boolean
,p_other_manager_warning out nocopy boolean
)
is
--
-- Declare cursors and local variables
--
l_proc varchar2(72);
select bgp.legislation_code
from per_all_assignments_f asg,
per_business_groups_perf bgp
where asg.business_group_id = bgp.business_group_id
and asg.assignment_id = c_assignment_id
and c_effective_date
between effective_start_date and effective_end_date;
l_proc := g_package||'update_us_emp_asg';
hr_assignment_api.update_emp_asg
(p_validate => p_validate
,p_effective_date => p_effective_date
,p_datetrack_update_mode => p_datetrack_update_mode
,p_assignment_id => p_assignment_id
,p_object_version_number => p_object_version_number
,p_supervisor_id => p_supervisor_id
,p_assignment_number => p_assignment_number
,p_change_reason => p_change_reason
,p_comments => p_comments
,p_date_probation_end => p_date_probation_end
,p_default_code_comb_id => p_default_code_comb_id
,p_frequency => p_frequency
,p_internal_address_line => p_internal_address_line
,p_manager_flag => p_manager_flag
,p_normal_hours => p_normal_hours
,p_perf_review_period => p_perf_review_period
,p_perf_review_period_frequency => p_perf_review_period_frequency
,p_probation_period => p_probation_period
,p_probation_unit => p_probation_unit
,p_sal_review_period => p_sal_review_period
,p_sal_review_period_frequency => p_sal_review_period_frequency
,p_set_of_books_id => p_set_of_books_id
,p_source_type => p_source_type
,p_time_normal_finish => p_time_normal_finish
,p_time_normal_start => p_time_normal_start
,p_bargaining_unit_code => p_bargaining_unit_code
,p_labour_union_member_flag => p_labour_union_member_flag
,p_hourly_salaried_code => p_hourly_salaried_code
,p_ass_attribute_category => p_ass_attribute_category
,p_ass_attribute1 => p_ass_attribute1
,p_ass_attribute2 => p_ass_attribute2
,p_ass_attribute3 => p_ass_attribute3
,p_ass_attribute4 => p_ass_attribute4
,p_ass_attribute5 => p_ass_attribute5
,p_ass_attribute6 => p_ass_attribute6
,p_ass_attribute7 => p_ass_attribute7
,p_ass_attribute8 => p_ass_attribute8
,p_ass_attribute9 => p_ass_attribute9
,p_ass_attribute10 => p_ass_attribute10
,p_ass_attribute11 => p_ass_attribute11
,p_ass_attribute12 => p_ass_attribute12
,p_ass_attribute13 => p_ass_attribute13
,p_ass_attribute14 => p_ass_attribute14
,p_ass_attribute15 => p_ass_attribute15
,p_ass_attribute16 => p_ass_attribute16
,p_ass_attribute17 => p_ass_attribute17
,p_ass_attribute18 => p_ass_attribute18
,p_ass_attribute19 => p_ass_attribute19
,p_ass_attribute20 => p_ass_attribute20
,p_ass_attribute21 => p_ass_attribute21
,p_ass_attribute22 => p_ass_attribute22
,p_ass_attribute23 => p_ass_attribute23
,p_ass_attribute24 => p_ass_attribute24
,p_ass_attribute25 => p_ass_attribute25
,p_ass_attribute26 => p_ass_attribute26
,p_ass_attribute27 => p_ass_attribute27
,p_ass_attribute28 => p_ass_attribute28
,p_ass_attribute29 => p_ass_attribute29
,p_ass_attribute30 => p_ass_attribute30
,p_title => p_title
,p_contract_id => p_contract_id
,p_establishment_id => p_establishment_id
,p_collective_agreement_id => p_collective_agreement_id
,p_cagr_id_flex_num => p_cagr_id_flex_num
,p_notice_period => p_notice_period
,p_notice_period_uom => p_notice_period_uom
,p_employee_category => p_employee_category
,p_work_at_home => p_work_at_home
,p_job_post_source_name => p_job_post_source_name
,p_segment1 => p_tax_unit
,p_segment2 => p_timecard_approver
,p_segment3 => p_timecard_required
,p_segment4 => p_work_schedule
,p_segment5 => p_shift
,p_segment6 => p_spouse_salary
,p_segment7 => p_legal_representative
,p_segment8 => p_wc_override_code
,p_segment9 => p_eeo_1_establishment
,p_cagr_grade_def_id => p_cagr_grade_def_id
,p_cagr_concatenated_segments => p_cagr_concatenated_segments
,p_soft_coding_keyflex_id => p_soft_coding_keyflex_id
,p_comment_id => p_comment_id
,p_effective_start_date => p_effective_start_date
,p_effective_end_date => p_effective_end_date
-- Bug 944911
-- Added new param
,p_concatenated_segments => p_concatenated_segments
,p_concat_segments => p_concat_segments
,p_no_managers_warning => p_no_managers_warning
,p_other_manager_warning => p_other_manager_warning
,p_supervisor_assignment_id => p_supervisor_assignment_id
);
end update_us_emp_asg;
procedure update_us_emp_asg
(p_validate in boolean
,p_effective_date in date
,p_datetrack_update_mode in varchar2
,p_assignment_id in number
,p_object_version_number in out nocopy number
,p_supervisor_id in number
,p_assignment_number in varchar2
,p_change_reason in varchar2
,p_comments in varchar2
,p_date_probation_end in date
,p_default_code_comb_id in number
,p_frequency in varchar2
,p_internal_address_line in varchar2
,p_manager_flag in varchar2
,p_normal_hours in number
,p_perf_review_period in number
,p_perf_review_period_frequency in varchar2
,p_probation_period in number
,p_probation_unit in varchar2
,p_sal_review_period in number
,p_sal_review_period_frequency in varchar2
,p_set_of_books_id in number
,p_source_type in varchar2
,p_time_normal_finish in varchar2
,p_time_normal_start in varchar2
,p_bargaining_unit_code in varchar2
,p_labour_union_member_flag in varchar2
,p_hourly_salaried_code in varchar2
,p_ass_attribute_category in varchar2
,p_ass_attribute1 in varchar2
,p_ass_attribute2 in varchar2
,p_ass_attribute3 in varchar2
,p_ass_attribute4 in varchar2
,p_ass_attribute5 in varchar2
,p_ass_attribute6 in varchar2
,p_ass_attribute7 in varchar2
,p_ass_attribute8 in varchar2
,p_ass_attribute9 in varchar2
,p_ass_attribute10 in varchar2
,p_ass_attribute11 in varchar2
,p_ass_attribute12 in varchar2
,p_ass_attribute13 in varchar2
,p_ass_attribute14 in varchar2
,p_ass_attribute15 in varchar2
,p_ass_attribute16 in varchar2
,p_ass_attribute17 in varchar2
,p_ass_attribute18 in varchar2
,p_ass_attribute19 in varchar2
,p_ass_attribute20 in varchar2
,p_ass_attribute21 in varchar2
,p_ass_attribute22 in varchar2
,p_ass_attribute23 in varchar2
,p_ass_attribute24 in varchar2
,p_ass_attribute25 in varchar2
,p_ass_attribute26 in varchar2
,p_ass_attribute27 in varchar2
,p_ass_attribute28 in varchar2
,p_ass_attribute29 in varchar2
,p_ass_attribute30 in varchar2
,p_title in varchar2
,p_tax_unit in varchar2
,p_timecard_approver in varchar2
,p_timecard_required in varchar2
,p_work_schedule in varchar2
,p_shift in varchar2
,p_spouse_salary in varchar2
,p_legal_representative in varchar2
,p_wc_override_code in varchar2
,p_eeo_1_establishment in varchar2
-- Added for bug 1889914
,p_contract_id in number
,p_establishment_id in number
,p_collective_agreement_id in number
,p_cagr_id_flex_num in number
,p_notice_period in number
,p_notice_period_uom in varchar2
,p_employee_category in varchar2
,p_work_at_home in varchar2
,p_job_post_source_name in varchar2
,p_supervisor_assignment_id in number
,p_cagr_grade_def_id out nocopy number
,p_cagr_concatenated_segments out nocopy varchar2
-- End 1889914
,p_comment_id out nocopy number
,p_soft_coding_keyflex_id out nocopy number
,p_effective_start_date out nocopy date
,p_effective_end_date out nocopy date
-- Bug 944911
-- Amended p_concatenated_segments to be out
-- Added p_concat_segments - in param
,p_concatenated_segments out nocopy varchar2
,p_concat_segments in varchar2
,p_no_managers_warning out nocopy boolean
,p_other_manager_warning out nocopy boolean
,p_hourly_salaried_warning out nocopy boolean
)
is
--
-- Declare cursors and local variables
--
l_proc varchar2(72);
select bgp.legislation_code
from per_all_assignments_f asg,
per_business_groups_perf bgp
where asg.business_group_id = bgp.business_group_id
and asg.assignment_id = c_assignment_id
and c_effective_date
between effective_start_date and effective_end_date;
l_proc := g_package||'update_us_emp_asg';
hr_assignment_api.update_emp_asg
(p_validate => p_validate
,p_effective_date => p_effective_date
,p_datetrack_update_mode => p_datetrack_update_mode
,p_assignment_id => p_assignment_id
,p_object_version_number => p_object_version_number
,p_supervisor_id => p_supervisor_id
,p_assignment_number => p_assignment_number
,p_change_reason => p_change_reason
,p_comments => p_comments
,p_date_probation_end => p_date_probation_end
,p_default_code_comb_id => p_default_code_comb_id
,p_frequency => p_frequency
,p_internal_address_line => p_internal_address_line
,p_manager_flag => p_manager_flag
,p_normal_hours => p_normal_hours
,p_perf_review_period => p_perf_review_period
,p_perf_review_period_frequency => p_perf_review_period_frequency
,p_probation_period => p_probation_period
,p_probation_unit => p_probation_unit
,p_sal_review_period => p_sal_review_period
,p_sal_review_period_frequency => p_sal_review_period_frequency
,p_set_of_books_id => p_set_of_books_id
,p_source_type => p_source_type
,p_time_normal_finish => p_time_normal_finish
,p_time_normal_start => p_time_normal_start
,p_bargaining_unit_code => p_bargaining_unit_code
,p_labour_union_member_flag => p_labour_union_member_flag
,p_hourly_salaried_code => p_hourly_salaried_code
,p_ass_attribute_category => p_ass_attribute_category
,p_ass_attribute1 => p_ass_attribute1
,p_ass_attribute2 => p_ass_attribute2
,p_ass_attribute3 => p_ass_attribute3
,p_ass_attribute4 => p_ass_attribute4
,p_ass_attribute5 => p_ass_attribute5
,p_ass_attribute6 => p_ass_attribute6
,p_ass_attribute7 => p_ass_attribute7
,p_ass_attribute8 => p_ass_attribute8
,p_ass_attribute9 => p_ass_attribute9
,p_ass_attribute10 => p_ass_attribute10
,p_ass_attribute11 => p_ass_attribute11
,p_ass_attribute12 => p_ass_attribute12
,p_ass_attribute13 => p_ass_attribute13
,p_ass_attribute14 => p_ass_attribute14
,p_ass_attribute15 => p_ass_attribute15
,p_ass_attribute16 => p_ass_attribute16
,p_ass_attribute17 => p_ass_attribute17
,p_ass_attribute18 => p_ass_attribute18
,p_ass_attribute19 => p_ass_attribute19
,p_ass_attribute20 => p_ass_attribute20
,p_ass_attribute21 => p_ass_attribute21
,p_ass_attribute22 => p_ass_attribute22
,p_ass_attribute23 => p_ass_attribute23
,p_ass_attribute24 => p_ass_attribute24
,p_ass_attribute25 => p_ass_attribute25
,p_ass_attribute26 => p_ass_attribute26
,p_ass_attribute27 => p_ass_attribute27
,p_ass_attribute28 => p_ass_attribute28
,p_ass_attribute29 => p_ass_attribute29
,p_ass_attribute30 => p_ass_attribute30
,p_title => p_title
,p_contract_id => p_contract_id
,p_establishment_id => p_establishment_id
,p_collective_agreement_id => p_collective_agreement_id
,p_cagr_id_flex_num => p_cagr_id_flex_num
,p_notice_period => p_notice_period
,p_notice_period_uom => p_notice_period_uom
,p_employee_category => p_employee_category
,p_work_at_home => p_work_at_home
,p_job_post_source_name => p_job_post_source_name
,p_segment1 => p_tax_unit
,p_segment2 => p_timecard_approver
,p_segment3 => p_timecard_required
,p_segment4 => p_work_schedule
,p_segment5 => p_shift
,p_segment6 => p_spouse_salary
,p_segment7 => p_legal_representative
,p_segment8 => p_wc_override_code
,p_segment9 => p_eeo_1_establishment
,p_cagr_grade_def_id => p_cagr_grade_def_id
,p_cagr_concatenated_segments => p_cagr_concatenated_segments
,p_soft_coding_keyflex_id => p_soft_coding_keyflex_id
,p_comment_id => p_comment_id
,p_effective_start_date => p_effective_start_date
,p_effective_end_date => p_effective_end_date
-- Bug 944911
-- Added new param
,p_concatenated_segments => p_concatenated_segments
,p_concat_segments => p_concat_segments
,p_no_managers_warning => p_no_managers_warning
,p_other_manager_warning => p_other_manager_warning
,p_hourly_salaried_warning => p_hourly_salaried_warning
,p_gsp_post_process_warning => l_gsp_post_process_warning -- bug 2999562
,p_supervisor_assignment_id => p_supervisor_assignment_id
);
end update_us_emp_asg;
procedure update_us_emp_asg
(p_validate in boolean
,p_effective_date in date
,p_datetrack_update_mode in varchar2
,p_assignment_id in number
,p_object_version_number in out nocopy number
,p_supervisor_id in number
,p_assignment_number in varchar2
,p_change_reason in varchar2
,p_comments in varchar2
,p_date_probation_end in date
,p_default_code_comb_id in number
,p_frequency in varchar2
,p_internal_address_line in varchar2
,p_manager_flag in varchar2
,p_normal_hours in number
,p_perf_review_period in number
,p_perf_review_period_frequency in varchar2
,p_probation_period in number
,p_probation_unit in varchar2
,p_sal_review_period in number
,p_sal_review_period_frequency in varchar2
,p_set_of_books_id in number
,p_source_type in varchar2
,p_time_normal_finish in varchar2
,p_time_normal_start in varchar2
,p_bargaining_unit_code in varchar2
,p_labour_union_member_flag in varchar2
,p_hourly_salaried_code in varchar2
,p_ass_attribute_category in varchar2
,p_ass_attribute1 in varchar2
,p_ass_attribute2 in varchar2
,p_ass_attribute3 in varchar2
,p_ass_attribute4 in varchar2
,p_ass_attribute5 in varchar2
,p_ass_attribute6 in varchar2
,p_ass_attribute7 in varchar2
,p_ass_attribute8 in varchar2
,p_ass_attribute9 in varchar2
,p_ass_attribute10 in varchar2
,p_ass_attribute11 in varchar2
,p_ass_attribute12 in varchar2
,p_ass_attribute13 in varchar2
,p_ass_attribute14 in varchar2
,p_ass_attribute15 in varchar2
,p_ass_attribute16 in varchar2
,p_ass_attribute17 in varchar2
,p_ass_attribute18 in varchar2
,p_ass_attribute19 in varchar2
,p_ass_attribute20 in varchar2
,p_ass_attribute21 in varchar2
,p_ass_attribute22 in varchar2
,p_ass_attribute23 in varchar2
,p_ass_attribute24 in varchar2
,p_ass_attribute25 in varchar2
,p_ass_attribute26 in varchar2
,p_ass_attribute27 in varchar2
,p_ass_attribute28 in varchar2
,p_ass_attribute29 in varchar2
,p_ass_attribute30 in varchar2
,p_title in varchar2
,p_tax_unit in varchar2
,p_timecard_approver in varchar2
,p_timecard_required in varchar2
,p_work_schedule in varchar2
,p_shift in varchar2
,p_spouse_salary in varchar2
,p_legal_representative in varchar2
,p_wc_override_code in varchar2
,p_eeo_1_establishment in varchar2
-- Added for bug 1889914
,p_contract_id in number
,p_establishment_id in number
,p_collective_agreement_id in number
,p_cagr_id_flex_num in number
,p_notice_period in number
,p_notice_period_uom in varchar2
,p_employee_category in varchar2
,p_work_at_home in varchar2
,p_job_post_source_name in varchar2
,p_supervisor_assignment_id in number
,p_cagr_grade_def_id out nocopy number
,p_cagr_concatenated_segments out nocopy varchar2
-- End 1889914
,p_comment_id out nocopy number
,p_soft_coding_keyflex_id out nocopy number
,p_effective_start_date out nocopy date
,p_effective_end_date out nocopy date
-- Bug 944911
-- Amended p_concatenated_segments to be out
-- Added p_concat_segments - in param
,p_concatenated_segments out nocopy varchar2
,p_concat_segments in varchar2
,p_no_managers_warning out nocopy boolean
,p_other_manager_warning out nocopy boolean
,p_hourly_salaried_warning out nocopy boolean
,p_gsp_post_process_warning out nocopy varchar2
)
is
--
-- Declare cursors and local variables
--
l_proc varchar2(72);
select bgp.legislation_code
from per_all_assignments_f asg,
per_business_groups_perf bgp
where asg.business_group_id = bgp.business_group_id
and asg.assignment_id = c_assignment_id
and c_effective_date
between effective_start_date and effective_end_date;
l_proc := g_package||'update_us_emp_asg';
hr_assignment_api.update_emp_asg
(p_validate => p_validate
,p_effective_date => p_effective_date
,p_datetrack_update_mode => p_datetrack_update_mode
,p_assignment_id => p_assignment_id
,p_object_version_number => p_object_version_number
,p_supervisor_id => p_supervisor_id
,p_assignment_number => p_assignment_number
,p_change_reason => p_change_reason
,p_comments => p_comments
,p_date_probation_end => p_date_probation_end
,p_default_code_comb_id => p_default_code_comb_id
,p_frequency => p_frequency
,p_internal_address_line => p_internal_address_line
,p_manager_flag => p_manager_flag
,p_normal_hours => p_normal_hours
,p_perf_review_period => p_perf_review_period
,p_perf_review_period_frequency => p_perf_review_period_frequency
,p_probation_period => p_probation_period
,p_probation_unit => p_probation_unit
,p_sal_review_period => p_sal_review_period
,p_sal_review_period_frequency => p_sal_review_period_frequency
,p_set_of_books_id => p_set_of_books_id
,p_source_type => p_source_type
,p_time_normal_finish => p_time_normal_finish
,p_time_normal_start => p_time_normal_start
,p_bargaining_unit_code => p_bargaining_unit_code
,p_labour_union_member_flag => p_labour_union_member_flag
,p_hourly_salaried_code => p_hourly_salaried_code
,p_ass_attribute_category => p_ass_attribute_category
,p_ass_attribute1 => p_ass_attribute1
,p_ass_attribute2 => p_ass_attribute2
,p_ass_attribute3 => p_ass_attribute3
,p_ass_attribute4 => p_ass_attribute4
,p_ass_attribute5 => p_ass_attribute5
,p_ass_attribute6 => p_ass_attribute6
,p_ass_attribute7 => p_ass_attribute7
,p_ass_attribute8 => p_ass_attribute8
,p_ass_attribute9 => p_ass_attribute9
,p_ass_attribute10 => p_ass_attribute10
,p_ass_attribute11 => p_ass_attribute11
,p_ass_attribute12 => p_ass_attribute12
,p_ass_attribute13 => p_ass_attribute13
,p_ass_attribute14 => p_ass_attribute14
,p_ass_attribute15 => p_ass_attribute15
,p_ass_attribute16 => p_ass_attribute16
,p_ass_attribute17 => p_ass_attribute17
,p_ass_attribute18 => p_ass_attribute18
,p_ass_attribute19 => p_ass_attribute19
,p_ass_attribute20 => p_ass_attribute20
,p_ass_attribute21 => p_ass_attribute21
,p_ass_attribute22 => p_ass_attribute22
,p_ass_attribute23 => p_ass_attribute23
,p_ass_attribute24 => p_ass_attribute24
,p_ass_attribute25 => p_ass_attribute25
,p_ass_attribute26 => p_ass_attribute26
,p_ass_attribute27 => p_ass_attribute27
,p_ass_attribute28 => p_ass_attribute28
,p_ass_attribute29 => p_ass_attribute29
,p_ass_attribute30 => p_ass_attribute30
,p_title => p_title
,p_contract_id => p_contract_id
,p_establishment_id => p_establishment_id
,p_collective_agreement_id => p_collective_agreement_id
,p_cagr_id_flex_num => p_cagr_id_flex_num
,p_notice_period => p_notice_period
,p_notice_period_uom => p_notice_period_uom
,p_employee_category => p_employee_category
,p_work_at_home => p_work_at_home
,p_job_post_source_name => p_job_post_source_name
,p_segment1 => p_tax_unit
,p_segment2 => p_timecard_approver
,p_segment3 => p_timecard_required
,p_segment4 => p_work_schedule
,p_segment5 => p_shift
,p_segment6 => p_spouse_salary
,p_segment7 => p_legal_representative
,p_segment8 => p_wc_override_code
,p_segment9 => p_eeo_1_establishment
,p_cagr_grade_def_id => p_cagr_grade_def_id
,p_cagr_concatenated_segments => p_cagr_concatenated_segments
,p_soft_coding_keyflex_id => p_soft_coding_keyflex_id
,p_comment_id => p_comment_id
,p_effective_start_date => p_effective_start_date
,p_effective_end_date => p_effective_end_date
-- Bug 944911
-- Added new param
,p_concatenated_segments => p_concatenated_segments
,p_concat_segments => p_concat_segments
,p_no_managers_warning => p_no_managers_warning
,p_other_manager_warning => p_other_manager_warning
,p_hourly_salaried_warning => p_hourly_salaried_warning
,p_gsp_post_process_warning => p_gsp_post_process_warning
,p_supervisor_assignment_id => p_supervisor_assignment_id
);
end update_us_emp_asg;
procedure update_emp_asg_criteria
(p_effective_date in date
,p_datetrack_update_mode in varchar2
,p_assignment_id in number
,p_validate in boolean
,p_called_from_mass_update in boolean
,p_grade_id in number
,p_position_id in number
,p_job_id in number
,p_payroll_id in number
,p_location_id in number
,p_organization_id in number
,p_pay_basis_id in number
,p_segment1 in varchar2
,p_segment2 in varchar2
,p_segment3 in varchar2
,p_segment4 in varchar2
,p_segment5 in varchar2
,p_segment6 in varchar2
,p_segment7 in varchar2
,p_segment8 in varchar2
,p_segment9 in varchar2
,p_segment10 in varchar2
,p_segment11 in varchar2
,p_segment12 in varchar2
,p_segment13 in varchar2
,p_segment14 in varchar2
,p_segment15 in varchar2
,p_segment16 in varchar2
,p_segment17 in varchar2
,p_segment18 in varchar2
,p_segment19 in varchar2
,p_segment20 in varchar2
,p_segment21 in varchar2
,p_segment22 in varchar2
,p_segment23 in varchar2
,p_segment24 in varchar2
,p_segment25 in varchar2
,p_segment26 in varchar2
,p_segment27 in varchar2
,p_segment28 in varchar2
,p_segment29 in varchar2
,p_segment30 in varchar2
,p_employment_category in varchar2
-- Bug 944911
-- Amended p_group_name to out
-- Added new param p_pgp_concat_segments - for sec asg procs
-- for others added p_concat_segments
,p_concat_segments in varchar2
,p_grade_ladder_pgm_id in number
,p_supervisor_assignment_id in number
,p_people_group_id in out nocopy number --bug 2359997
,p_object_version_number in out nocopy number
,p_special_ceiling_step_id in out nocopy number
,p_group_name out nocopy varchar2
,p_effective_start_date out nocopy date
,p_effective_end_date out nocopy date
,p_org_now_no_manager_warning out nocopy boolean
,p_other_manager_warning out nocopy boolean
,p_spp_delete_warning out nocopy boolean
,p_entries_changed_warning out nocopy varchar2
,p_tax_district_changed_warning out nocopy boolean
) is
--
-- Declare cursors and local variables
--
-- Out variables
--
l_effective_end_date per_all_assignments_f.effective_end_date%TYPE;
l_spp_delete_warning boolean;
l_proc varchar2(72) := g_package||'update_emp_asg_criteria';
hr_assignment_api.update_emp_asg_criteria
(p_validate => p_validate
,p_effective_date => p_effective_date
,p_datetrack_update_mode => p_datetrack_update_mode
,p_called_from_mass_update => p_called_from_mass_update
,p_assignment_id => p_assignment_id
,p_object_version_number => l_object_version_number
,p_grade_id => p_grade_id
,p_position_id => p_position_id
,p_job_id => p_job_id -- Bug 2656155 -- 3553286
,p_payroll_id => p_payroll_id
,p_location_id => p_location_id
,p_special_ceiling_step_id => l_special_ceiling_step_id
,p_organization_id => p_organization_id -- Bug 2656155 -- 3553286
,p_pay_basis_id => p_pay_basis_id
,p_segment1 => p_segment1
,p_segment2 => p_segment2
,p_segment3 => p_segment3
,p_segment4 => p_segment4
,p_segment5 => p_segment5
,p_segment6 => p_segment6
,p_segment7 => p_segment7
,p_segment8 => p_segment8
,p_segment9 => p_segment9
,p_segment10 => p_segment10
,p_segment11 => p_segment11
,p_segment12 => p_segment12
,p_segment13 => p_segment13
,p_segment14 => p_segment14
,p_segment15 => p_segment15
,p_segment16 => p_segment16
,p_segment17 => p_segment17
,p_segment18 => p_segment18
,p_segment19 => p_segment19
,p_segment20 => p_segment20
,p_segment21 => p_segment21
,p_segment22 => p_segment22
,p_segment23 => p_segment23
,p_segment24 => p_segment24
,p_segment25 => p_segment25
,p_segment26 => p_segment26
,p_segment27 => p_segment27
,p_segment28 => p_segment28
,p_segment29 => p_segment29
,p_segment30 => p_segment30
,p_concat_segments => p_concat_segments
,p_grade_ladder_pgm_id => p_grade_ladder_pgm_id
,p_supervisor_assignment_id => p_supervisor_assignment_id
,p_group_name => l_group_name
,p_employment_category => p_employment_category
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
,p_people_group_id => l_people_group_id
,p_org_now_no_manager_warning => l_org_now_no_manager_warning
,p_other_manager_warning => l_other_manager_warning
,p_spp_delete_warning => l_spp_delete_warning
,p_entries_changed_warning => l_entries_changed_warning
,p_tax_district_changed_warning => l_tax_district_changed_warning
,p_soft_coding_keyflex_id => l_soft_coding_keyflex_id
,p_concatenated_segments => l_concatenated_segments
--2689059: changed the following: must pass hr_api defaults to NEW update API
,p_contract_id => hr_api.g_number --l_contract_id
,p_establishment_id => hr_api.g_number --l_establishment_id
,p_scl_segment1 => hr_api.g_varchar2 --l_scl_segment1
) ;
p_spp_delete_warning := l_spp_delete_warning;
End update_emp_asg_criteria;
procedure update_emp_asg_criteria
(p_effective_date in date
,p_datetrack_update_mode in varchar2
,p_assignment_id in number
,p_validate in boolean
,p_called_from_mass_update in boolean
,p_grade_id in number
,p_position_id in number
,p_job_id in number
,p_payroll_id in number
,p_location_id in number
,p_organization_id in number
,p_pay_basis_id in number
,p_segment1 in varchar2
,p_segment2 in varchar2
,p_segment3 in varchar2
,p_segment4 in varchar2
,p_segment5 in varchar2
,p_segment6 in varchar2
,p_segment7 in varchar2
,p_segment8 in varchar2
,p_segment9 in varchar2
,p_segment10 in varchar2
,p_segment11 in varchar2
,p_segment12 in varchar2
,p_segment13 in varchar2
,p_segment14 in varchar2
,p_segment15 in varchar2
,p_segment16 in varchar2
,p_segment17 in varchar2
,p_segment18 in varchar2
,p_segment19 in varchar2
,p_segment20 in varchar2
,p_segment21 in varchar2
,p_segment22 in varchar2
,p_segment23 in varchar2
,p_segment24 in varchar2
,p_segment25 in varchar2
,p_segment26 in varchar2
,p_segment27 in varchar2
,p_segment28 in varchar2
,p_segment29 in varchar2
,p_segment30 in varchar2
,p_employment_category in varchar2
-- Bug 944911
-- Amended p_group_name to out
-- Added new param p_pgp_concat_segments - for sec asg procs
-- for others added p_concat_segments
,p_concat_segments in varchar2
,p_contract_id in number
,p_establishment_id in number
,p_scl_segment1 in varchar2
,p_grade_ladder_pgm_id in number
,p_supervisor_assignment_id in number
,p_object_version_number in out nocopy number
,p_special_ceiling_step_id in out nocopy number
,p_people_group_id in out nocopy number
,p_soft_coding_keyflex_id in out nocopy number
,p_group_name out nocopy varchar2
,p_effective_start_date out nocopy date
,p_effective_end_date out nocopy date
,p_org_now_no_manager_warning out nocopy boolean
,p_other_manager_warning out nocopy boolean
,p_spp_delete_warning out nocopy boolean
,p_entries_changed_warning out nocopy varchar2
,p_tax_district_changed_warning out nocopy boolean
,p_concatenated_segments out nocopy varchar2
) is
--
-- Declare cursors and local variables
--
-- Out variables
--
l_effective_end_date per_all_assignments_f.effective_end_date%TYPE;
l_payroll_id_updated boolean;
l_spp_delete_warning boolean;
g_package || 'update_emp_asg_criteria';
hr_assignment_api.update_emp_asg_criteria
(p_validate => p_validate
,p_effective_date => p_effective_date
,p_datetrack_update_mode => p_datetrack_update_mode
,p_called_from_mass_update => p_called_from_mass_update
,p_assignment_id => p_assignment_id
,p_object_version_number => l_object_version_number
,p_grade_id => p_grade_id
,p_position_id => p_position_id
,p_job_id => p_job_id
,p_payroll_id => p_payroll_id
,p_location_id => p_location_id
,p_special_ceiling_step_id => l_special_ceiling_step_id
,p_organization_id => p_organization_id
,p_pay_basis_id => p_pay_basis_id
,p_segment1 => p_segment1
,p_segment2 => p_segment2
,p_segment3 => p_segment3
,p_segment4 => p_segment4
,p_segment5 => p_segment5
,p_segment6 => p_segment6
,p_segment7 => p_segment7
,p_segment8 => p_segment8
,p_segment9 => p_segment9
,p_segment10 => p_segment10
,p_segment11 => p_segment11
,p_segment12 => p_segment12
,p_segment13 => p_segment13
,p_segment14 => p_segment14
,p_segment15 => p_segment15
,p_segment16 => p_segment16
,p_segment17 => p_segment17
,p_segment18 => p_segment18
,p_segment19 => p_segment19
,p_segment20 => p_segment20
,p_segment21 => p_segment21
,p_segment22 => p_segment22
,p_segment23 => p_segment23
,p_segment24 => p_segment24
,p_segment25 => p_segment25
,p_segment26 => p_segment26
,p_segment27 => p_segment27
,p_segment28 => p_segment28
,p_segment29 => p_segment29
,p_segment30 => p_segment30
,p_concat_segments => p_concat_segments
,p_grade_ladder_pgm_id => p_grade_ladder_pgm_id
,p_supervisor_assignment_id => p_supervisor_assignment_id
,p_employment_category => p_employment_category
,p_contract_id => p_contract_id
,p_establishment_id => p_establishment_id
,p_scl_segment1 => p_scl_segment1
,p_group_name => l_group_name
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
,p_people_group_id => l_people_group_id
,p_org_now_no_manager_warning => l_org_now_no_manager_warning
,p_other_manager_warning => l_other_manager_warning
,p_spp_delete_warning => l_spp_delete_warning
,p_entries_changed_warning => l_entries_changed_warning
,p_tax_district_changed_warning => l_tax_district_changed_warning
,p_soft_coding_keyflex_id => l_soft_coding_keyflex_id
,p_concatenated_segments => l_concatenated_segments
,p_gsp_post_process_warning => l_gsp_post_process_warning -- bug 2999562
) ;
p_spp_delete_warning := l_spp_delete_warning;
end update_emp_asg_criteria;
procedure update_emp_asg_criteria
(p_effective_date in date
,p_datetrack_update_mode in varchar2
,p_assignment_id in number
,p_validate in boolean
,p_called_from_mass_update in boolean
,p_grade_id in number
,p_position_id in number
,p_job_id in number
,p_payroll_id in number
,p_location_id in number
,p_organization_id in number
,p_pay_basis_id in number
,p_segment1 in varchar2
,p_segment2 in varchar2
,p_segment3 in varchar2
,p_segment4 in varchar2
,p_segment5 in varchar2
,p_segment6 in varchar2
,p_segment7 in varchar2
,p_segment8 in varchar2
,p_segment9 in varchar2
,p_segment10 in varchar2
,p_segment11 in varchar2
,p_segment12 in varchar2
,p_segment13 in varchar2
,p_segment14 in varchar2
,p_segment15 in varchar2
,p_segment16 in varchar2
,p_segment17 in varchar2
,p_segment18 in varchar2
,p_segment19 in varchar2
,p_segment20 in varchar2
,p_segment21 in varchar2
,p_segment22 in varchar2
,p_segment23 in varchar2
,p_segment24 in varchar2
,p_segment25 in varchar2
,p_segment26 in varchar2
,p_segment27 in varchar2
,p_segment28 in varchar2
,p_segment29 in varchar2
,p_segment30 in varchar2
,p_employment_category in varchar2
-- Bug 944911
-- Amended p_group_name to out
-- Added new param p_pgp_concat_segments - for sec asg procs
-- for others added p_concat_segments
,p_concat_segments in varchar2
,p_contract_id in number
,p_establishment_id in number
,p_scl_segment1 in varchar2
,p_grade_ladder_pgm_id in number
,p_supervisor_assignment_id in number
,p_object_version_number in out nocopy number
,p_special_ceiling_step_id in out nocopy number
,p_people_group_id in out nocopy number
,p_soft_coding_keyflex_id in out nocopy number
,p_group_name out nocopy varchar2
,p_effective_start_date out nocopy date
,p_effective_end_date out nocopy date
,p_org_now_no_manager_warning out nocopy boolean
,p_other_manager_warning out nocopy boolean
,p_spp_delete_warning out nocopy boolean
,p_entries_changed_warning out nocopy varchar2
,p_tax_district_changed_warning out nocopy boolean
,p_concatenated_segments out nocopy varchar2
,p_gsp_post_process_warning out nocopy varchar2
,p_create_salary_proposal in varchar2 default 'N' --added for bug 12911607
) is
--
-- Declare cursors and local variables
--
-- Out variables
--
l_effective_end_date per_all_assignments_f.effective_end_date%TYPE;
l_payroll_id_updated boolean;
l_spp_delete_warning boolean;
g_package || 'update_emp_asg_criteria';
select group_name, --4103321
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_people_group_id;
select bus.legislation_code
from per_business_groups_perf bus
where bus.business_group_id = l_business_group_id;
select element_entry_id
from pay_element_entries_f
where assignment_id = p_assignment_id
and creator_type = 'SP'
and l_validation_start_date between
effective_start_date and effective_end_date;
select element_entry_id
from pay_element_entries_f
where assignment_id = p_assignment_id
and creator_type = 'SP'
and element_entry_id = l_element_entry_id -- added for Bug14373426
and (l_validation_start_date - 1) between
effective_start_date and effective_end_date;
select bus.people_group_structure
from per_business_groups_perf bus
where bus.business_group_id = l_business_group_id;
select asg.effective_start_date
, asg.effective_end_date
from per_all_assignments_f asg
where asg.assignment_id=p_assignment_id
and ((sysdate between asg.effective_start_date
and asg.effective_end_date)
or
(sysdate
select sps.step_id
from per_spinal_point_steps_f sps,
per_grade_spines_f pgs
where pgs.grade_id = p_grade_id
and pgs.grade_spine_id = sps.grade_spine_id
and sps.step_id = p_special_ceiling_step_id;
select people_group_id
from per_all_assignments_f
where assignment_id = p_assignment_id
and p_effective_date between effective_start_date and effective_end_date;
l_dt_update_mode VARCHAR2(30);
l_new_dt_update_mode VARCHAR2(30);
select pay_proposal_id
from per_pay_proposals
where assignment_id = p_assignment_id
and change_date = p_effective_date; */
select pay_proposal_id
from per_pay_proposals
where assignment_id = p_assignment_id
and change_date = p_effective_date;
select 'exist'
from per_pay_proposals
where assignment_id = p_assignment_id
and change_date >= p_effective_date;
IF p_called_from_mass_update THEN
--
if g_debug then
hr_utility.set_location(l_proc,40);
l_dt_update_mode := 'CORRECTION';
l_new_dt_update_mode := p_datetrack_update_mode;
l_dt_update_mode := p_datetrack_update_mode;
l_new_dt_update_mode := p_datetrack_update_mode;
savepoint update_emp_asg_criteria;
hr_assignment_bk3.update_emp_asg_criteria_b
(p_effective_date => l_effective_date
,p_datetrack_update_mode => l_dt_update_mode
,p_assignment_id => p_assignment_id
,p_object_version_number => p_object_version_number
,p_grade_id => p_grade_id
,p_position_id => p_position_id
,p_job_id => p_job_id
,p_payroll_id => p_payroll_id
,p_location_id => p_location_id
,p_special_ceiling_step_id => p_special_ceiling_step_id
,p_organization_id => p_organization_id
,p_pay_basis_id => p_pay_basis_id
,p_segment1 => l_pgp_segment1
,p_segment2 => l_pgp_segment2
,p_segment3 => l_pgp_segment3
,p_segment4 => l_pgp_segment4
,p_segment5 => l_pgp_segment5
,p_segment6 => l_pgp_segment6
,p_segment7 => l_pgp_segment7
,p_segment8 => l_pgp_segment8
,p_segment9 => l_pgp_segment9
,p_segment10 => l_pgp_segment10
,p_segment11 => l_pgp_segment11
,p_segment12 => l_pgp_segment12
,p_segment13 => l_pgp_segment13
,p_segment14 => l_pgp_segment14
,p_segment15 => l_pgp_segment15
,p_segment16 => l_pgp_segment16
,p_segment17 => l_pgp_segment17
,p_segment18 => l_pgp_segment18
,p_segment19 => l_pgp_segment19
,p_segment20 => l_pgp_segment20
,p_segment21 => l_pgp_segment21
,p_segment22 => l_pgp_segment22
,p_segment23 => l_pgp_segment23
,p_segment24 => l_pgp_segment24
,p_segment25 => l_pgp_segment25
,p_segment26 => l_pgp_segment26
,p_segment27 => l_pgp_segment27
,p_segment28 => l_pgp_segment28
,p_segment29 => l_pgp_segment29
,p_segment30 => l_pgp_segment30
--
-- Bug 944911
-- Amended p_group_name to p_concat_segments
--
,p_concat_segments => l_old_group_name
,p_employment_category => p_employment_category
-- Start of Fix for Bug 2622747
,p_contract_id => p_contract_id
,p_establishment_id => p_establishment_id
);
(p_module_name => 'UPDATE_EMP_ASG_CRITERIA'
,p_hook_type => 'BP'
);
if (p_called_from_mass_update = TRUE and p_position_id is not null) then
if (l_job_id is null) or (l_org_id is null) then
hr_psf_shd.get_position_job_org(p_position_id, p_effective_date,
l_job_id, l_org_id);
update_pgp_concat_segs
(p_people_group_id => l_people_group_id
,p_group_name => l_group_name
);
,p_payroll_id_updated => l_payroll_id_updated
,p_other_manager_warning => l_other_manager_warning
,p_no_managers_warning => l_no_managers_warning
,p_org_now_no_manager_warning => l_org_now_no_manager_warning
,p_validation_start_date => l_validation_start_date
,p_validation_end_date => l_validation_end_date
,p_object_version_number => l_object_version_number
,p_effective_date => l_effective_date
,p_datetrack_mode => l_dt_update_mode
,p_validate => FALSE
,p_hourly_salaried_warning => l_hourly_salaried_warning
,p_soft_coding_keyflex_id => l_soft_coding_keyflex_id
,p_contract_id => p_contract_id
,p_establishment_id => p_establishment_id
,p_grade_ladder_pgm_id => p_grade_ladder_pgm_id
,p_supervisor_assignment_id => p_supervisor_assignment_id
);
hr_entry_api.delete_element_entry
('DELETE'
,l_validation_start_date - 1
,l_element_entry_id);
hr_entry_api.delete_element_entry
('DELETE'
,l_validation_start_date - 1
,l_element_entry_id);*/ --Commented as part of Fix of Bug#16170631 and Bug#12911607.
/*hr_entry_api.delete_element_entry
('DELETE'
,l_validation_start_date - 1
,l_element_entry_id);*/
Hr_Maintain_Proposal_Api.INSERT_SALARY_PROPOSAL
(P_PAY_PROPOSAL_ID => l_Pay_Proposal_Id
,P_ASSIGNMENT_ID => p_assignment_id
,P_BUSINESS_GROUP_ID => l_business_group_id
,P_CHANGE_DATE => p_effective_date
,P_PROPOSED_SALARY_N => per_saladmin_utility.get_proposed_salary(p_assignment_id,p_effective_date-1)
,P_OBJECT_VERSION_NUMBER => l_ovn
,P_ELEMENT_ENTRY_ID => L_sp_Element_Entry_Id
,P_MULTIPLE_COMPONENTS => 'N'
,P_APPROVED => 'Y'
,P_PROPOSAL_REASON => l_proposal_reason
,P_INV_NEXT_SAL_DATE_WARNING => L_INV_NEXT_SAL_DATE_WARNING
,P_PROPOSED_SALARY_WARNING => L_PROPOSED_SALARY_WARNING
,P_APPROVED_WARNING => L_APPROVED_WARNING
,P_PAYROLL_WARNING => L_PAYROLL_WARNING);*/
hr_utility.set_location('P_CREATE_SALARY_PROPOSAL is Y. Update SP', 292);
if p_datetrack_update_mode in ('CORRECTION','UPDATE') then
open future_pay_proposals;
Hr_Maintain_Proposal_Api.INSERT_SALARY_PROPOSAL
(P_PAY_PROPOSAL_ID => l_Pay_Proposal_Id,
P_ASSIGNMENT_ID => p_assignment_id,
P_BUSINESS_GROUP_ID => l_business_group_id,
P_CHANGE_DATE => p_effective_date,
P_PROPOSED_SALARY_N => per_saladmin_utility.get_proposed_salary(p_assignment_id, p_effective_date - 1),
P_OBJECT_VERSION_NUMBER => l_ovn,
P_ELEMENT_ENTRY_ID => L_sp_Element_Entry_Id,
P_MULTIPLE_COMPONENTS => 'N',
P_APPROVED => 'Y',
P_PROPOSAL_REASON => l_proposal_reason,
P_INV_NEXT_SAL_DATE_WARNING => L_INV_NEXT_SAL_DATE_WARNING,
P_PROPOSED_SALARY_WARNING => L_PROPOSED_SALARY_WARNING,
P_APPROVED_WARNING => L_APPROVED_WARNING,
P_PAYROLL_WARNING => L_PAYROLL_WARNING);
hr_entry_api.delete_element_entry
('DELETE',
l_validation_start_date - 1,
l_element_entry_id);
hr_entry_api.delete_element_entry
('ZAP',
l_validation_start_date,
l_element_entry_id);
hr_entry_api.delete_element_entry
('ZAP',
l_validation_start_date,
l_element_entry_id);
hr_entry_api.delete_element_entry
('DELETE'
,l_validation_start_date - 1
,l_element_entry_id);
,p_dt_mode => l_new_dt_update_mode
,p_validation_start_date => l_validation_start_date
,p_validation_end_date => l_validation_end_date
,p_entries_changed => l_entries_changed
,p_old_people_group_id => per_asg_shd.g_old_rec.people_group_id
,p_new_people_group_id => l_people_group_id
,p_old_grade_id => per_asg_shd.g_old_rec.grade_id -- Added for Bug#13960540
,p_new_grade_id => p_grade_id -- Added for Bug#13960540
,p_old_organization_id => per_asg_shd.g_old_rec.organization_id -- Added for Bug#13960540
,p_new_organization_id => l_organization_id -- Added for Bug#13960540
,p_old_employment_category => per_asg_shd.g_old_rec.employment_category -- Added for Bug#13960540
,p_new_employment_category => p_employment_category -- Added for Bug#13960540
);
,p_datetrack_mode => l_new_dt_update_mode
,p_validation_start_date => l_validation_start_date
,p_validation_end_date => l_validation_end_date
,p_grade_id => p_grade_id
,p_spp_delete_warning => l_spp_delete_warning
);
l_spp_delete_warning := FALSE;
and l_payroll_id_updated
then
--
if g_debug then
hr_utility.set_location(l_proc, 390);
hr_assignment_bk3.update_emp_asg_criteria_a
(p_effective_date => l_effective_date
,p_datetrack_update_mode => l_dt_update_mode
,p_assignment_id => p_assignment_id
,p_object_version_number => p_object_version_number
,p_grade_id => p_grade_id
,p_position_id => p_position_id
,p_job_id => l_job_id -- Bug 3553286 p_job_id
,p_payroll_id => p_payroll_id
,p_location_id => p_location_id
,p_special_ceiling_step_id => p_special_ceiling_step_id
,p_organization_id => l_org_id -- Bug 3553286 p_organization_id
,p_pay_basis_id => p_pay_basis_id
,p_segment1 => l_pgp_segment1
,p_segment2 => l_pgp_segment2
,p_segment3 => l_pgp_segment3
,p_segment4 => l_pgp_segment4
,p_segment5 => l_pgp_segment5
,p_segment6 => l_pgp_segment6
,p_segment7 => l_pgp_segment7
,p_segment8 => l_pgp_segment8
,p_segment9 => l_pgp_segment9
,p_segment10 => l_pgp_segment10
,p_segment11 => l_pgp_segment11
,p_segment12 => l_pgp_segment12
,p_segment13 => l_pgp_segment13
,p_segment14 => l_pgp_segment14
,p_segment15 => l_pgp_segment15
,p_segment16 => l_pgp_segment16
,p_segment17 => l_pgp_segment17
,p_segment18 => l_pgp_segment18
,p_segment19 => l_pgp_segment19
,p_segment20 => l_pgp_segment20
,p_segment21 => l_pgp_segment21
,p_segment22 => l_pgp_segment22
,p_segment23 => l_pgp_segment23
,p_segment24 => l_pgp_segment24
,p_segment25 => l_pgp_segment25
,p_segment26 => l_pgp_segment26
,p_segment27 => l_pgp_segment27
,p_segment28 => l_pgp_segment28
,p_segment29 => l_pgp_segment29
,p_segment30 => l_pgp_segment30
,p_group_name => l_group_name
,p_employment_category => p_employment_category
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
,p_people_group_id => l_people_group_id
,p_org_now_no_manager_warning => l_org_now_no_manager_warning
,p_other_manager_warning => l_other_manager_warning
,p_spp_delete_warning => l_spp_delete_warning
,p_entries_changed_warning => l_entries_changed_warning
,p_tax_district_changed_warning => l_tax_district_changed_warning
--
-- Bug 944911
-- Added the new in param
--
,p_concat_segments => l_old_group_name
-- Start of Fix for Bug 2622747
,p_contract_id => p_contract_id
,p_establishment_id => p_establishment_id
,p_concatenated_segments => l_concatenated_segments
,p_soft_coding_keyflex_id => l_soft_coding_keyflex_id
,p_scl_segment1 => l_scl_segment1
-- End of Fix for Bug 2622747
);
(p_module_name => 'UPDATE_EMP_ASG_CRITERIA'
,p_hook_type => 'AP'
);
,p_date_track_mode => p_datetrack_update_mode
,p_warning_mesg => l_gsp_post_process_warning
);
p_spp_delete_warning := l_spp_delete_warning;
ROLLBACK TO update_emp_asg_criteria;
p_spp_delete_warning := l_spp_delete_warning;
p_spp_delete_warning := null;
ROLLBACK TO update_emp_asg_criteria;
end update_emp_asg_criteria;
procedure update_apl_asg
(p_validate in boolean
,p_effective_date in date
,p_datetrack_update_mode in varchar2
,p_assignment_id in number
,p_object_version_number in out nocopy number
,p_recruiter_id in number
,p_grade_id in number
,p_position_id in number
,p_job_id in number
,p_payroll_id in number
,p_location_id in number
,p_person_referred_by_id in number
,p_supervisor_id in number
,p_special_ceiling_step_id in number
,p_recruitment_activity_id in number
,p_source_organization_id in number
,p_organization_id in number
,p_vacancy_id in number
,p_pay_basis_id in number
,p_application_id in number
,p_change_reason in varchar2
,p_assignment_status_type_id in number
,p_comments in varchar2
,p_date_probation_end in date
,p_default_code_comb_id in number
,p_employment_category in varchar2
,p_frequency in varchar2
,p_internal_address_line in varchar2
,p_manager_flag in varchar2
,p_normal_hours in number
,p_perf_review_period in number
,p_perf_review_period_frequency in varchar2
,p_probation_period in number
,p_probation_unit in varchar2
,p_sal_review_period in number
,p_sal_review_period_frequency in varchar2
,p_set_of_books_id in number
,p_source_type in varchar2
,p_time_normal_finish in varchar2
,p_time_normal_start in varchar2
,p_bargaining_unit_code in varchar2
,p_ass_attribute_category in varchar2
,p_ass_attribute1 in varchar2
,p_ass_attribute2 in varchar2
,p_ass_attribute3 in varchar2
,p_ass_attribute4 in varchar2
,p_ass_attribute5 in varchar2
,p_ass_attribute6 in varchar2
,p_ass_attribute7 in varchar2
,p_ass_attribute8 in varchar2
,p_ass_attribute9 in varchar2
,p_ass_attribute10 in varchar2
,p_ass_attribute11 in varchar2
,p_ass_attribute12 in varchar2
,p_ass_attribute13 in varchar2
,p_ass_attribute14 in varchar2
,p_ass_attribute15 in varchar2
,p_ass_attribute16 in varchar2
,p_ass_attribute17 in varchar2
,p_ass_attribute18 in varchar2
,p_ass_attribute19 in varchar2
,p_ass_attribute20 in varchar2
,p_ass_attribute21 in varchar2
,p_ass_attribute22 in varchar2
,p_ass_attribute23 in varchar2
,p_ass_attribute24 in varchar2
,p_ass_attribute25 in varchar2
,p_ass_attribute26 in varchar2
,p_ass_attribute27 in varchar2
,p_ass_attribute28 in varchar2
,p_ass_attribute29 in varchar2
,p_ass_attribute30 in varchar2
,p_title in varchar2
,p_scl_segment1 in varchar2
,p_scl_segment2 in varchar2
,p_scl_segment3 in varchar2
,p_scl_segment4 in varchar2
,p_scl_segment5 in varchar2
,p_scl_segment6 in varchar2
,p_scl_segment7 in varchar2
,p_scl_segment8 in varchar2
,p_scl_segment9 in varchar2
,p_scl_segment10 in varchar2
,p_scl_segment11 in varchar2
,p_scl_segment12 in varchar2
,p_scl_segment13 in varchar2
,p_scl_segment14 in varchar2
,p_scl_segment15 in varchar2
,p_scl_segment16 in varchar2
,p_scl_segment17 in varchar2
,p_scl_segment18 in varchar2
,p_scl_segment19 in varchar2
,p_scl_segment20 in varchar2
,p_scl_segment21 in varchar2
,p_scl_segment22 in varchar2
,p_scl_segment23 in varchar2
,p_scl_segment24 in varchar2
,p_scl_segment25 in varchar2
,p_scl_segment26 in varchar2
,p_scl_segment27 in varchar2
,p_scl_segment28 in varchar2
,p_scl_segment29 in varchar2
,p_scl_segment30 in varchar2
-- Bug 944911
-- Amended p_scl_concatenated_segments to be an out instead of in out
-- Added p_scl_concat_segments ( in param )
-- Amended p_scl_concatenated_segments to be p_concatenated_segments
,p_scl_concat_segments in varchar2
,p_concatenated_segments out nocopy varchar2
,p_pgp_segment1 in varchar2
,p_pgp_segment2 in varchar2
,p_pgp_segment3 in varchar2
,p_pgp_segment4 in varchar2
,p_pgp_segment5 in varchar2
,p_pgp_segment6 in varchar2
,p_pgp_segment7 in varchar2
,p_pgp_segment8 in varchar2
,p_pgp_segment9 in varchar2
,p_pgp_segment10 in varchar2
,p_pgp_segment11 in varchar2
,p_pgp_segment12 in varchar2
,p_pgp_segment13 in varchar2
,p_pgp_segment14 in varchar2
,p_pgp_segment15 in varchar2
,p_pgp_segment16 in varchar2
,p_pgp_segment17 in varchar2
,p_pgp_segment18 in varchar2
,p_pgp_segment19 in varchar2
,p_pgp_segment20 in varchar2
,p_pgp_segment21 in varchar2
,p_pgp_segment22 in varchar2
,p_pgp_segment23 in varchar2
,p_pgp_segment24 in varchar2
,p_pgp_segment25 in varchar2
,p_pgp_segment26 in varchar2
,p_pgp_segment27 in varchar2
,p_pgp_segment28 in varchar2
,p_pgp_segment29 in varchar2
,p_pgp_segment30 in varchar2
-- Bug 944911
-- Made p_group_name to be out param
-- and add p_concat_segment to be IN
-- in case of sec_asg alone made p_pgp_concat_segments as in param
,p_concat_segments in varchar2
,p_contract_id in number
,p_establishment_id in number
,p_collective_agreement_id in number
,p_cagr_id_flex_num in number
,p_cag_segment1 in varchar2
,p_cag_segment2 in varchar2
,p_cag_segment3 in varchar2
,p_cag_segment4 in varchar2
,p_cag_segment5 in varchar2
,p_cag_segment6 in varchar2
,p_cag_segment7 in varchar2
,p_cag_segment8 in varchar2
,p_cag_segment9 in varchar2
,p_cag_segment10 in varchar2
,p_cag_segment11 in varchar2
,p_cag_segment12 in varchar2
,p_cag_segment13 in varchar2
,p_cag_segment14 in varchar2
,p_cag_segment15 in varchar2
,p_cag_segment16 in varchar2
,p_cag_segment17 in varchar2
,p_cag_segment18 in varchar2
,p_cag_segment19 in varchar2
,p_cag_segment20 in varchar2
,p_notice_period in number
,p_notice_period_uom in varchar2
,p_employee_category in varchar2
,p_work_at_home in varchar2
,p_job_post_source_name in varchar2
,p_posting_content_id in number
,p_applicant_rank in number
,p_grade_ladder_pgm_id in number
,p_supervisor_assignment_id in number
,p_cagr_grade_def_id in out nocopy number
,p_cagr_concatenated_segments out nocopy varchar2
,p_group_name out nocopy varchar2
,p_comment_id out nocopy number
,p_people_group_id in out nocopy number
,p_soft_coding_keyflex_id in out nocopy number
,p_effective_start_date out nocopy date
,p_effective_end_date out nocopy date
) is
--
-- Declare cursors and local variables
--
-- Out variables
--
l_comment_id per_all_assignments_f.comment_id%TYPE;
l_proc varchar2(72) := g_package||'update_apl_asg';
select ast.per_system_status
from per_assignment_status_types ast,
per_all_assignments_f asg
where ast.assignment_status_type_id = asg.assignment_status_type_id
and asg.assignment_id = p_assignment_id
and l_effective_date between asg.effective_start_date and asg.effective_end_date;
select ast.per_system_status
from per_assignment_status_types ast
where ast.assignment_status_type_id = p_assignment_status_type_id;
select bus.people_group_structure
from per_business_groups_perf bus
where bus.business_group_id = l_business_group_id;
select plr.rule_mode id_flex_num
from pay_legislation_rules plr,
per_business_groups_perf pgr
where plr.legislation_code = pgr.legislation_code
and pgr.business_group_id = l_business_group_id
and plr.rule_type = 'S'
and exists
(select 1
from fnd_segment_attribute_values fsav
where to_char(fsav.id_flex_num) = plr.rule_mode -- Fix For Bug# 12813119
and fsav.application_id = 800
and fsav.id_flex_code = 'SCL'
and fsav.segment_attribute_type = 'ASSIGNMENT'
and fsav.attribute_value = 'Y')
and exists
(select 1
from pay_legislation_rules plr2
where plr2.legislation_code = plr.legislation_code
and plr2.rule_type = 'SDL'
and plr2.rule_mode = 'A') ;
select asg.effective_start_date
, asg.effective_end_date
from per_all_assignments_f asg
where asg.assignment_id=p_assignment_id
and ((sysdate between asg.effective_start_date
and asg.effective_end_date)
or
(sysdate
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_people_group_id;
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 hr_soft_coding_keyflex
where soft_coding_keyflex_id = l_soft_coding_keyflex_id;
select segment1,
segment2,
segment3,
segment4,
segment5,
segment6,
segment7,
segment8,
segment9,
segment10,
segment11,
segment12,
segment13,
segment14,
segment15,
segment16,
segment17,
segment18,
segment19,
segment20
from per_cagr_grades_def
where cagr_grade_def_id = l_cagr_grade_def_id;
select assignment_id
from per_all_assignments_f
where person_id=csr_person_id
and business_group_id=l_business_group_id
and l_effective_date between effective_start_date and effective_end_date
and assignment_type not in ('B','O'); -- added for the bug 6925339
savepoint update_apl_asg;
,p_argument => 'datetrack_update_mode'
,p_argument_value => p_datetrack_update_mode);
hr_assignment_bk5.update_apl_asg_b
(p_effective_date => l_effective_date
,p_datetrack_update_mode => p_datetrack_update_mode
,p_assignment_id => p_assignment_id
,p_object_version_number => p_object_version_number
,p_grade_id => p_grade_id
,p_job_id => p_job_id
,p_payroll_id => p_payroll_id
,p_location_id => p_location_id
,p_organization_id => p_organization_id
,p_position_id => p_position_id
,p_application_id => p_application_id
,p_special_ceiling_step_id => p_special_ceiling_step_id
,p_recruiter_id => p_recruiter_id
,p_recruitment_activity_id => p_recruitment_activity_id
,p_vacancy_id => p_vacancy_id
,p_pay_basis_id => p_pay_basis_id
,p_person_referred_by_id => p_person_referred_by_id
,p_supervisor_id => p_supervisor_id
,p_source_organization_id => p_source_organization_id
,p_change_reason => p_change_reason
,p_assignment_status_type_id => p_assignment_status_type_id
,p_internal_address_line => p_internal_address_line
,p_default_code_comb_id => p_default_code_comb_id
,p_employment_category => p_employment_category
,p_frequency => p_frequency
,p_manager_flag => p_manager_flag
,p_normal_hours => p_normal_hours
,p_perf_review_period => p_perf_review_period
,p_perf_review_period_frequency => p_perf_review_period_frequency
,p_probation_period => p_probation_period
,p_probation_unit => p_probation_unit
,p_sal_review_period => p_sal_review_period
,p_sal_review_period_frequency => p_sal_review_period_frequency
,p_set_of_books_id => p_set_of_books_id
,p_source_type => p_source_type
,p_time_normal_finish => p_time_normal_finish
,p_time_normal_start => p_time_normal_start
,p_bargaining_unit_code => p_bargaining_unit_code
,p_comments => p_comments
,p_date_probation_end => l_date_probation_end
,p_title => p_title
,p_ass_attribute_category => p_ass_attribute_category
,p_ass_attribute1 => p_ass_attribute1
,p_ass_attribute2 => p_ass_attribute2
,p_ass_attribute3 => p_ass_attribute3
,p_ass_attribute4 => p_ass_attribute4
,p_ass_attribute5 => p_ass_attribute5
,p_ass_attribute6 => p_ass_attribute6
,p_ass_attribute7 => p_ass_attribute7
,p_ass_attribute8 => p_ass_attribute8
,p_ass_attribute9 => p_ass_attribute9
,p_ass_attribute10 => p_ass_attribute10
,p_ass_attribute11 => p_ass_attribute11
,p_ass_attribute12 => p_ass_attribute12
,p_ass_attribute13 => p_ass_attribute13
,p_ass_attribute14 => p_ass_attribute14
,p_ass_attribute15 => p_ass_attribute15
,p_ass_attribute16 => p_ass_attribute16
,p_ass_attribute17 => p_ass_attribute17
,p_ass_attribute18 => p_ass_attribute18
,p_ass_attribute19 => p_ass_attribute19
,p_ass_attribute20 => p_ass_attribute20
,p_ass_attribute21 => p_ass_attribute21
,p_ass_attribute22 => p_ass_attribute22
,p_ass_attribute23 => p_ass_attribute23
,p_ass_attribute24 => p_ass_attribute24
,p_ass_attribute25 => p_ass_attribute25
,p_ass_attribute26 => p_ass_attribute26
,p_ass_attribute27 => p_ass_attribute27
,p_ass_attribute28 => p_ass_attribute28
,p_ass_attribute29 => p_ass_attribute29
,p_ass_attribute30 => p_ass_attribute30
,p_scl_segment1 => l_scl_segment1
,p_scl_segment2 => l_scl_segment2
,p_scl_segment3 => l_scl_segment3
,p_scl_segment4 => l_scl_segment4
,p_scl_segment5 => l_scl_segment5
,p_scl_segment6 => l_scl_segment6
,p_scl_segment7 => l_scl_segment7
,p_scl_segment8 => l_scl_segment8
,p_scl_segment9 => l_scl_segment9
,p_scl_segment10 => l_scl_segment10
,p_scl_segment11 => l_scl_segment11
,p_scl_segment12 => l_scl_segment12
,p_scl_segment13 => l_scl_segment13
,p_scl_segment14 => l_scl_segment14
,p_scl_segment15 => l_scl_segment15
,p_scl_segment16 => l_scl_segment16
,p_scl_segment17 => l_scl_segment17
,p_scl_segment18 => l_scl_segment18
,p_scl_segment19 => l_scl_segment19
,p_scl_segment20 => l_scl_segment20
,p_scl_segment21 => l_scl_segment21
,p_scl_segment22 => l_scl_segment22
,p_scl_segment23 => l_scl_segment23
,p_scl_segment24 => l_scl_segment24
,p_scl_segment25 => l_scl_segment25
,p_scl_segment26 => l_scl_segment26
,p_scl_segment27 => l_scl_segment27
,p_scl_segment28 => l_scl_segment28
,p_scl_segment29 => l_scl_segment29
,p_scl_segment30 => l_scl_segment30
-- Bug 944911
-- Amended p_scl_concatenated_segments to be p_scl_concat_segments
,p_scl_concat_segments => l_old_scl_conc_segments
,p_pgp_segment1 => l_pgp_segment1
,p_pgp_segment2 => l_pgp_segment2
,p_pgp_segment3 => l_pgp_segment3
,p_pgp_segment4 => l_pgp_segment4
,p_pgp_segment5 => l_pgp_segment5
,p_pgp_segment6 => l_pgp_segment6
,p_pgp_segment7 => l_pgp_segment7
,p_pgp_segment8 => l_pgp_segment8
,p_pgp_segment9 => l_pgp_segment9
,p_pgp_segment10 => l_pgp_segment10
,p_pgp_segment11 => l_pgp_segment11
,p_pgp_segment12 => l_pgp_segment12
,p_pgp_segment13 => l_pgp_segment13
,p_pgp_segment14 => l_pgp_segment14
,p_pgp_segment15 => l_pgp_segment15
,p_pgp_segment16 => l_pgp_segment16
,p_pgp_segment17 => l_pgp_segment17
,p_pgp_segment18 => l_pgp_segment18
,p_pgp_segment19 => l_pgp_segment19
,p_pgp_segment20 => l_pgp_segment20
,p_pgp_segment21 => l_pgp_segment21
,p_pgp_segment22 => l_pgp_segment22
,p_pgp_segment23 => l_pgp_segment23
,p_pgp_segment24 => l_pgp_segment24
,p_pgp_segment25 => l_pgp_segment25
,p_pgp_segment26 => l_pgp_segment26
,p_pgp_segment27 => l_pgp_segment27
,p_pgp_segment28 => l_pgp_segment28
,p_pgp_segment29 => l_pgp_segment29
,p_pgp_segment30 => l_pgp_segment30
,p_contract_id => p_contract_id
,p_establishment_id => p_establishment_id
,p_collective_agreement_id => p_collective_agreement_id
,p_cagr_id_flex_num => p_cagr_id_flex_num
,p_cag_segment1 => l_cag_segment1
,p_cag_segment2 => l_cag_segment2
,p_cag_segment3 => l_cag_segment3
,p_cag_segment4 => l_cag_segment4
,p_cag_segment5 => l_cag_segment5
,p_cag_segment6 => l_cag_segment6
,p_cag_segment7 => l_cag_segment7
,p_cag_segment8 => l_cag_segment8
,p_cag_segment9 => l_cag_segment9
,p_cag_segment10 => l_cag_segment10
,p_cag_segment11 => l_cag_segment11
,p_cag_segment12 => l_cag_segment12
,p_cag_segment13 => l_cag_segment13
,p_cag_segment14 => l_cag_segment14
,p_cag_segment15 => l_cag_segment15
,p_cag_segment16 => l_cag_segment16
,p_cag_segment17 => l_cag_segment17
,p_cag_segment18 => l_cag_segment18
,p_cag_segment19 => l_cag_segment19
,p_cag_segment20 => l_cag_segment20
,p_notice_period => p_notice_period
,p_notice_period_uom => p_notice_period_uom
,p_employee_category => p_employee_category
,p_work_at_home => p_work_at_home
,p_job_post_source_name => p_job_post_source_name
,p_posting_content_id => p_posting_content_id
,p_applicant_rank => p_applicant_rank
-- Bug 944911
-- Amended p_group_name to p_concat_segments
,p_concat_segments => l_old_group_name
,p_grade_ladder_pgm_id => p_grade_ladder_pgm_id
,p_supervisor_assignment_id => p_supervisor_assignment_id
);
(p_module_name => 'UPDATE_APL_ASG',
p_hook_type => 'BP'
);
update_pgp_concat_segs
(p_people_group_id => l_people_group_id
,p_group_name => l_group_name
);
update_scl_concat_segs
(p_soft_coding_keyflex_id => l_soft_coding_keyflex_id
,p_concatenated_segments => l_scl_concatenated_segments
);
p_datetrack_mode => p_datetrack_update_mode,
p_assignment_id => p_assignment_id,
p_object_version_number => p_object_version_number,
p_validation_start_date => l_unused_start_date,
p_validation_end_date => l_unused_end_date
);
,p_payroll_id_updated => l_dummy_payroll
,p_other_manager_warning => l_dummy_manager1
,p_no_managers_warning => l_dummy_manager2
,p_org_now_no_manager_warning => l_dummy_manager3
,p_comment_id => l_comment_id
,p_validation_start_date => l_validation_start_date
,p_validation_end_date => l_validation_end_date
,p_object_version_number => l_object_version_number
,p_effective_date => l_effective_date
,p_datetrack_mode => p_datetrack_update_mode
,p_validate => FALSE
,p_hourly_salaried_warning => l_hourly_salaried_warning
,p_applicant_rank => p_applicant_rank
,p_posting_content_id => p_posting_content_id
,p_grade_ladder_pgm_id => p_grade_ladder_pgm_id
,p_supervisor_assignment_id => p_supervisor_assignment_id
);
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');
delete from per_letter_requests plr
where plr.business_group_id = l_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_assignment_bk5.update_apl_asg_a
(p_effective_date => l_effective_date
,p_datetrack_update_mode => p_datetrack_update_mode
,p_assignment_id => p_assignment_id
,p_object_version_number => l_object_version_number
,p_grade_id => p_grade_id
,p_job_id => p_job_id
,p_payroll_id => p_payroll_id
,p_location_id => p_location_id
,p_organization_id => p_organization_id
,p_position_id => p_position_id
,p_application_id => p_application_id
,p_special_ceiling_step_id => p_special_ceiling_step_id
,p_recruiter_id => p_recruiter_id
,p_recruitment_activity_id => p_recruitment_activity_id
,p_soft_coding_keyflex_id => l_soft_coding_keyflex_id
,p_vacancy_id => p_vacancy_id
,p_pay_basis_id => p_pay_basis_id
,p_person_referred_by_id => p_person_referred_by_id
,p_supervisor_id => p_supervisor_id
,p_source_organization_id => p_source_organization_id
,p_change_reason => p_change_reason
,p_assignment_status_type_id => p_assignment_status_type_id
,p_internal_address_line => p_internal_address_line
,p_default_code_comb_id => p_default_code_comb_id
,p_employment_category => p_employment_category
,p_frequency => p_frequency
,p_manager_flag => p_manager_flag
,p_normal_hours => p_normal_hours
,p_perf_review_period => p_perf_review_period
,p_perf_review_period_frequency => p_perf_review_period_frequency
,p_probation_period => p_probation_period
,p_probation_unit => p_probation_unit
,p_sal_review_period => p_sal_review_period
,p_sal_review_period_frequency => p_sal_review_period_frequency
,p_set_of_books_id => p_set_of_books_id
,p_source_type => p_source_type
,p_time_normal_finish => p_time_normal_finish
,p_time_normal_start => p_time_normal_start
,p_bargaining_unit_code => p_bargaining_unit_code
,p_comments => p_comments
,p_date_probation_end => l_date_probation_end
,p_title => p_title
,p_ass_attribute_category => p_ass_attribute_category
,p_ass_attribute1 => p_ass_attribute1
,p_ass_attribute2 => p_ass_attribute2
,p_ass_attribute3 => p_ass_attribute3
,p_ass_attribute4 => p_ass_attribute4
,p_ass_attribute5 => p_ass_attribute5
,p_ass_attribute6 => p_ass_attribute6
,p_ass_attribute7 => p_ass_attribute7
,p_ass_attribute8 => p_ass_attribute8
,p_ass_attribute9 => p_ass_attribute9
,p_ass_attribute10 => p_ass_attribute10
,p_ass_attribute11 => p_ass_attribute11
,p_ass_attribute12 => p_ass_attribute12
,p_ass_attribute13 => p_ass_attribute13
,p_ass_attribute14 => p_ass_attribute14
,p_ass_attribute15 => p_ass_attribute15
,p_ass_attribute16 => p_ass_attribute16
,p_ass_attribute17 => p_ass_attribute17
,p_ass_attribute18 => p_ass_attribute18
,p_ass_attribute19 => p_ass_attribute19
,p_ass_attribute20 => p_ass_attribute20
,p_ass_attribute21 => p_ass_attribute21
,p_ass_attribute22 => p_ass_attribute22
,p_ass_attribute23 => p_ass_attribute23
,p_ass_attribute24 => p_ass_attribute24
,p_ass_attribute25 => p_ass_attribute25
,p_ass_attribute26 => p_ass_attribute26
,p_ass_attribute27 => p_ass_attribute27
,p_ass_attribute28 => p_ass_attribute28
,p_ass_attribute29 => p_ass_attribute29
,p_ass_attribute30 => p_ass_attribute30
,p_scl_segment1 => l_scl_segment1
,p_scl_segment2 => l_scl_segment2
,p_scl_segment3 => l_scl_segment3
,p_scl_segment4 => l_scl_segment4
,p_scl_segment5 => l_scl_segment5
,p_scl_segment6 => l_scl_segment6
,p_scl_segment7 => l_scl_segment7
,p_scl_segment8 => l_scl_segment8
,p_scl_segment9 => l_scl_segment9
,p_scl_segment10 => l_scl_segment10
,p_scl_segment11 => l_scl_segment11
,p_scl_segment12 => l_scl_segment12
,p_scl_segment13 => l_scl_segment13
,p_scl_segment14 => l_scl_segment14
,p_scl_segment15 => l_scl_segment15
,p_scl_segment16 => l_scl_segment16
,p_scl_segment17 => l_scl_segment17
,p_scl_segment18 => l_scl_segment18
,p_scl_segment19 => l_scl_segment19
,p_scl_segment20 => l_scl_segment20
,p_scl_segment21 => l_scl_segment21
,p_scl_segment22 => l_scl_segment22
,p_scl_segment23 => l_scl_segment23
,p_scl_segment24 => l_scl_segment24
,p_scl_segment25 => l_scl_segment25
,p_scl_segment26 => l_scl_segment26
,p_scl_segment27 => l_scl_segment27
,p_scl_segment28 => l_scl_segment28
,p_scl_segment29 => l_scl_segment29
,p_scl_segment30 => l_scl_segment30
--
-- Amended p_scl_concatenated_segments to be p_concatenated_segments
-- Bug 944911
,p_concatenated_segments => l_scl_concatenated_segments
,p_pgp_segment1 => l_pgp_segment1
,p_pgp_segment2 => l_pgp_segment2
,p_pgp_segment3 => l_pgp_segment3
,p_pgp_segment4 => l_pgp_segment4
,p_pgp_segment5 => l_pgp_segment5
,p_pgp_segment6 => l_pgp_segment6
,p_pgp_segment7 => l_pgp_segment7
,p_pgp_segment8 => l_pgp_segment8
,p_pgp_segment9 => l_pgp_segment9
,p_pgp_segment10 => l_pgp_segment10
,p_pgp_segment11 => l_pgp_segment11
,p_pgp_segment12 => l_pgp_segment12
,p_pgp_segment13 => l_pgp_segment13
,p_pgp_segment14 => l_pgp_segment14
,p_pgp_segment15 => l_pgp_segment15
,p_pgp_segment16 => l_pgp_segment16
,p_pgp_segment17 => l_pgp_segment17
,p_pgp_segment18 => l_pgp_segment18
,p_pgp_segment19 => l_pgp_segment19
,p_pgp_segment20 => l_pgp_segment20
,p_pgp_segment21 => l_pgp_segment21
,p_pgp_segment22 => l_pgp_segment22
,p_pgp_segment23 => l_pgp_segment23
,p_pgp_segment24 => l_pgp_segment24
,p_pgp_segment25 => l_pgp_segment25
,p_pgp_segment26 => l_pgp_segment26
,p_pgp_segment27 => l_pgp_segment27
,p_pgp_segment28 => l_pgp_segment28
,p_pgp_segment29 => l_pgp_segment29
,p_pgp_segment30 => l_pgp_segment30
,p_contract_id => p_contract_id
,p_establishment_id => p_establishment_id
,p_collective_agreement_id => p_collective_agreement_id
,p_cagr_id_flex_num => l_cagr_id_flex_num
,p_cag_segment1 => l_cag_segment1
,p_cag_segment2 => l_cag_segment2
,p_cag_segment3 => l_cag_segment3
,p_cag_segment4 => l_cag_segment4
,p_cag_segment5 => l_cag_segment5
,p_cag_segment6 => l_cag_segment6
,p_cag_segment7 => l_cag_segment7
,p_cag_segment8 => l_cag_segment8
,p_cag_segment9 => l_cag_segment9
,p_cag_segment10 => l_cag_segment10
,p_cag_segment11 => l_cag_segment11
,p_cag_segment12 => l_cag_segment12
,p_cag_segment13 => l_cag_segment13
,p_cag_segment14 => l_cag_segment14
,p_cag_segment15 => l_cag_segment15
,p_cag_segment16 => l_cag_segment16
,p_cag_segment17 => l_cag_segment17
,p_cag_segment18 => l_cag_segment18
,p_cag_segment19 => l_cag_segment19
,p_cag_segment20 => l_cag_segment20
,p_notice_period => p_notice_period
,p_notice_period_uom => p_notice_period_uom
,p_employee_category => p_employee_category
,p_work_at_home => p_work_at_home
,p_job_post_source_name => p_job_post_source_name
,p_cagr_grade_def_id => l_cagr_grade_def_id
,p_cagr_concatenated_segments => l_cagr_concatenated_segments
,p_group_name => l_group_name
,p_comment_id => l_comment_id
,p_people_group_id => l_people_group_id
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
,p_applicant_rank => p_applicant_rank
,p_posting_content_id => p_posting_content_id
--
-- Bug 944911
-- Added the 2 additional IN param
-- Bug 944911
-- Amended p_group_name to p_concat_segments
--
,p_concat_segments => l_old_group_name
--
-- Bug 944911
-- Amended p_scl_concatenated_segments to be p_scl_concat_segments
,p_scl_concat_segments => l_old_scl_conc_segments
,p_grade_ladder_pgm_id => p_grade_ladder_pgm_id
,p_supervisor_assignment_id => p_supervisor_assignment_id
);
(p_module_name => 'UPDATE_APL_ASG',
p_hook_type => 'AP'
);
ROLLBACK TO update_apl_asg;
ROLLBACK TO update_apl_asg;
end update_apl_asg;
procedure update_apl_asg
(p_validate in boolean
,p_effective_date in date
,p_datetrack_update_mode in varchar2
,p_assignment_id in number
,p_object_version_number in out nocopy number
,p_grade_id in number
,p_job_id in number
,p_location_id in number
,p_organization_id in number
,p_position_id in number
,p_application_id in number
,p_recruiter_id in number
,p_recruitment_activity_id in number
,p_vacancy_id in number
,p_person_referred_by_id in number
,p_supervisor_id in number
,p_source_organization_id in number
,p_change_reason in varchar2
,p_frequency in varchar2
,p_manager_flag in varchar2
,p_normal_hours in number
,p_probation_period in number
,p_probation_unit in varchar2
,p_source_type in varchar2
,p_time_normal_finish in varchar2
,p_time_normal_start in varchar2
,p_comments in varchar2
,p_date_probation_end in date
,p_title in varchar2
,p_ass_attribute_category in varchar2
,p_ass_attribute1 in varchar2
,p_ass_attribute2 in varchar2
,p_ass_attribute3 in varchar2
,p_ass_attribute4 in varchar2
,p_ass_attribute5 in varchar2
,p_ass_attribute6 in varchar2
,p_ass_attribute7 in varchar2
,p_ass_attribute8 in varchar2
,p_ass_attribute9 in varchar2
,p_ass_attribute10 in varchar2
,p_ass_attribute11 in varchar2
,p_ass_attribute12 in varchar2
,p_ass_attribute13 in varchar2
,p_ass_attribute14 in varchar2
,p_ass_attribute15 in varchar2
,p_ass_attribute16 in varchar2
,p_ass_attribute17 in varchar2
,p_ass_attribute18 in varchar2
,p_ass_attribute19 in varchar2
,p_ass_attribute20 in varchar2
,p_ass_attribute21 in varchar2
,p_ass_attribute22 in varchar2
,p_ass_attribute23 in varchar2
,p_ass_attribute24 in varchar2
,p_ass_attribute25 in varchar2
,p_ass_attribute26 in varchar2
,p_ass_attribute27 in varchar2
,p_ass_attribute28 in varchar2
,p_ass_attribute29 in varchar2
,p_ass_attribute30 in varchar2
,p_segment1 in varchar2
,p_segment2 in varchar2
,p_segment3 in varchar2
,p_segment4 in varchar2
,p_segment5 in varchar2
,p_segment6 in varchar2
,p_segment7 in varchar2
,p_segment8 in varchar2
,p_segment9 in varchar2
,p_segment10 in varchar2
,p_segment11 in varchar2
,p_segment12 in varchar2
,p_segment13 in varchar2
,p_segment14 in varchar2
,p_segment15 in varchar2
,p_segment16 in varchar2
,p_segment17 in varchar2
,p_segment18 in varchar2
,p_segment19 in varchar2
,p_segment20 in varchar2
,p_segment21 in varchar2
,p_segment22 in varchar2
,p_segment23 in varchar2
,p_segment24 in varchar2
,p_segment25 in varchar2
,p_segment26 in varchar2
,p_segment27 in varchar2
,p_segment28 in varchar2
,p_segment29 in varchar2
,p_segment30 in varchar2
-- Bug 944911
-- Amended p_concat_segments to be an in instead of in out
-- Added p_concatenated_segments to be out
-- Reverting back changes as this for compatibilty with v11
--,p_concat_segments in varchar2
,p_supervisor_assignment_id in number
,p_concatenated_segments in out nocopy varchar2
,p_comment_id out nocopy number
,p_people_group_id out nocopy number -- in out?
,p_effective_start_date out nocopy date
,p_effective_end_date out nocopy date
) is
--
-- Declare cursors and local variables
--
-- Out variables
--
l_comment_id per_all_assignments_f.comment_id%TYPE;
l_proc := g_package||'update_apl_asg';
hr_assignment_api.update_apl_asg(
p_validate => p_validate
,p_effective_date => p_effective_date
,p_datetrack_update_mode => p_datetrack_update_mode
,p_assignment_id => p_assignment_id
,p_object_version_number => l_object_version_number
,p_grade_id => p_grade_id
,p_job_id => p_job_id
,p_location_id => p_location_id
,p_organization_id => p_organization_id
,p_position_id => p_position_id
,p_application_id => p_application_id
,p_recruiter_id => p_recruiter_id
,p_recruitment_activity_id => p_recruitment_activity_id
,p_vacancy_id => p_vacancy_id
,p_person_referred_by_id => p_person_referred_by_id
,p_supervisor_id => p_supervisor_id
,p_source_organization_id => p_source_organization_id
,p_change_reason => p_change_reason
,p_frequency => p_frequency
,p_manager_flag => p_manager_flag
,p_normal_hours => p_normal_hours
,p_probation_period => p_probation_period
,p_probation_unit => p_probation_unit
,p_source_type => p_source_type
,p_time_normal_finish => p_time_normal_finish
,p_time_normal_start => p_time_normal_start
,p_comments => p_comments
,p_date_probation_end => p_date_probation_end
,p_title => p_title
,p_ass_attribute_category => p_ass_attribute_category
,p_ass_attribute1 => p_ass_attribute1
,p_ass_attribute2 => p_ass_attribute2
,p_ass_attribute3 => p_ass_attribute3
,p_ass_attribute4 => p_ass_attribute4
,p_ass_attribute5 => p_ass_attribute5
,p_ass_attribute6 => p_ass_attribute6
,p_ass_attribute7 => p_ass_attribute7
,p_ass_attribute8 => p_ass_attribute8
,p_ass_attribute9 => p_ass_attribute9
,p_ass_attribute10 => p_ass_attribute10
,p_ass_attribute11 => p_ass_attribute11
,p_ass_attribute12 => p_ass_attribute12
,p_ass_attribute13 => p_ass_attribute13
,p_ass_attribute14 => p_ass_attribute14
,p_ass_attribute15 => p_ass_attribute15
,p_ass_attribute16 => p_ass_attribute16
,p_ass_attribute17 => p_ass_attribute17
,p_ass_attribute18 => p_ass_attribute18
,p_ass_attribute19 => p_ass_attribute19
,p_ass_attribute20 => p_ass_attribute20
,p_ass_attribute21 => p_ass_attribute21
,p_ass_attribute22 => p_ass_attribute22
,p_ass_attribute23 => p_ass_attribute23
,p_ass_attribute24 => p_ass_attribute24
,p_ass_attribute25 => p_ass_attribute25
,p_ass_attribute26 => p_ass_attribute26
,p_ass_attribute27 => p_ass_attribute27
,p_ass_attribute28 => p_ass_attribute28
,p_ass_attribute29 => p_ass_attribute29
,p_ass_attribute30 => p_ass_attribute30
,p_scl_segment1 => p_segment1
,p_scl_segment2 => p_segment2
,p_scl_segment3 => p_segment3
,p_scl_segment4 => p_segment4
,p_scl_segment5 => p_segment5
,p_scl_segment6 => p_segment6
,p_scl_segment7 => p_segment7
,p_scl_segment8 => p_segment8
,p_scl_segment9 => p_segment9
,p_scl_segment10 => p_segment10
,p_scl_segment11 => p_segment11
,p_scl_segment12 => p_segment12
,p_scl_segment13 => p_segment13
,p_scl_segment14 => p_segment14
,p_scl_segment15 => p_segment15
,p_scl_segment16 => p_segment16
,p_scl_segment17 => p_segment17
,p_scl_segment18 => p_segment18
,p_scl_segment19 => p_segment19
,p_scl_segment20 => p_segment20
,p_scl_segment21 => p_segment21
,p_scl_segment22 => p_segment22
,p_scl_segment23 => p_segment23
,p_scl_segment24 => p_segment24
,p_scl_segment25 => p_segment25
,p_scl_segment26 => p_segment26
,p_scl_segment27 => p_segment27
,p_scl_segment28 => p_segment28
,p_scl_segment29 => p_segment29
,p_scl_segment30 => p_segment30
,p_comment_id => l_comment_id
,p_people_group_id => l_people_group_id
,p_soft_coding_keyflex_id => l_soft_coding_keyflex_id
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
,p_group_name => l_group_name
,p_scl_concat_segments => l_concat_segments
,p_concatenated_segments => l_concatenated_segments
,p_cagr_grade_def_id => l_cagr_grade_def_id
,p_cagr_concatenated_segments => l_cagr_concatenated_segments
,p_supervisor_assignment_id => p_supervisor_assignment_id
);
end update_apl_asg;
select bus.business_group_id
, bus.legislation_code
, per.applicant_number, per.object_version_number --3652025
from per_all_people_f per
, per_business_groups_perf bus
where per.person_id = p_person_id
and l_effective_date between per.effective_start_date
and per.effective_end_date
and bus.business_group_id = per.business_group_id;
select apl.application_id, apl.date_end
from per_applications apl
where apl.person_id = p_person_id
and l_effective_date between apl.date_received
and nvl(apl.date_end,hr_api.g_eot);
select asg.application_id
from per_all_assignments_f asg
where asg.person_id = p_person_id
and l_effective_date between asg.effective_start_date
and asg.effective_end_date
and asg.assignment_type = 'A';
select bus.people_group_structure
from per_business_groups_perf bus
where bus.business_group_id = l_business_group_id;
select plr.rule_mode id_flex_num
from pay_legislation_rules plr,
per_business_groups_perf pgr
where plr.legislation_code = pgr.legislation_code
and pgr.business_group_id = l_business_group_id
and plr.rule_type = 'S'
and exists
(select 1
from fnd_segment_attribute_values fsav
where to_char(fsav.id_flex_num) = plr.rule_mode -- Fix For Bug# 12813119
and fsav.application_id = 800
and fsav.id_flex_code = 'SCL'
and fsav.segment_attribute_type = 'ASSIGNMENT'
and fsav.attribute_value = 'Y')
and exists
(select 1
from pay_legislation_rules plr2
where plr2.legislation_code = plr.legislation_code
and plr2.rule_type = 'SDL'
and plr2.rule_mode = 'A') ;
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_people_group_id;
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 hr_soft_coding_keyflex
where soft_coding_keyflex_id = l_soft_coding_keyflex_id;
select segment1,
segment2,
segment3,
segment4,
segment5,
segment6,
segment7,
segment8,
segment9,
segment10,
segment11,
segment12,
segment13,
segment14,
segment15,
segment16,
segment17,
segment18,
segment19,
segment20
from per_cagr_grades_def
where cagr_grade_def_id = l_cagr_grade_def_id;
update_pgp_concat_segs
(p_people_group_id => l_people_group_id
,p_group_name => l_group_name
);
update_scl_concat_segs
(p_soft_coding_keyflex_id => l_soft_coding_keyflex_id
,p_concatenated_segments => l_scl_concatenated_segments
);
hr_assignment_api.update_apl_asg
(p_validate => FALSE
,p_effective_date => l_effective_date
,p_datetrack_update_mode => hr_api.g_correction
,p_assignment_id => l_assignment_id
,p_object_version_number => l_object_version_number
,p_recruiter_id => p_recruiter_id
,p_grade_id => p_grade_id
,p_position_id => p_position_id
,p_job_id => p_job_id
,p_payroll_id => p_payroll_id
,p_location_id => p_location_id
,p_person_referred_by_id => p_person_referred_by_id
,p_supervisor_id => p_supervisor_id
,p_special_ceiling_step_id => p_special_ceiling_step_id
,p_recruitment_activity_id => p_recruitment_activity_id
,p_source_organization_id => p_source_organization_id
,p_organization_id => p_organization_id
,p_vacancy_id => p_vacancy_id
,p_pay_basis_id => p_pay_basis_id
,p_application_id => l_application_id
,p_change_reason => p_change_reason
,p_assignment_status_type_id => l_assignment_status_type_id
,p_comments => p_comments
,p_date_probation_end => l_date_probation_end
,p_default_code_comb_id => p_default_code_comb_id
,p_employment_category => p_employment_category
,p_frequency => p_frequency
,p_internal_address_line => p_internal_address_line
,p_manager_flag => p_manager_flag
,p_normal_hours => p_normal_hours
,p_perf_review_period => p_perf_review_period
,p_perf_review_period_frequency => p_perf_review_period_frequency
,p_probation_period => p_probation_period
,p_probation_unit => p_probation_unit
,p_sal_review_period => p_sal_review_period
,p_sal_review_period_frequency => p_sal_review_period_frequency
,p_set_of_books_id => p_set_of_books_id
,p_source_type => p_source_type
,p_time_normal_finish => p_time_normal_finish
,p_time_normal_start => p_time_normal_start
,p_bargaining_unit_code => p_bargaining_unit_code
,p_ass_attribute_category => p_ass_attribute_category
,p_ass_attribute1 => p_ass_attribute1
,p_ass_attribute2 => p_ass_attribute2
,p_ass_attribute3 => p_ass_attribute3
,p_ass_attribute4 => p_ass_attribute4
,p_ass_attribute5 => p_ass_attribute5
,p_ass_attribute6 => p_ass_attribute6
,p_ass_attribute7 => p_ass_attribute7
,p_ass_attribute8 => p_ass_attribute8
,p_ass_attribute9 => p_ass_attribute9
,p_ass_attribute10 => p_ass_attribute10
,p_ass_attribute11 => p_ass_attribute11
,p_ass_attribute12 => p_ass_attribute12
,p_ass_attribute13 => p_ass_attribute13
,p_ass_attribute14 => p_ass_attribute14
,p_ass_attribute15 => p_ass_attribute15
,p_ass_attribute16 => p_ass_attribute16
,p_ass_attribute17 => p_ass_attribute17
,p_ass_attribute18 => p_ass_attribute18
,p_ass_attribute19 => p_ass_attribute19
,p_ass_attribute20 => p_ass_attribute20
,p_ass_attribute21 => p_ass_attribute21
,p_ass_attribute22 => p_ass_attribute22
,p_ass_attribute23 => p_ass_attribute23
,p_ass_attribute24 => p_ass_attribute24
,p_ass_attribute25 => p_ass_attribute25
,p_ass_attribute26 => p_ass_attribute26
,p_ass_attribute27 => p_ass_attribute27
,p_ass_attribute28 => p_ass_attribute28
,p_ass_attribute29 => p_ass_attribute29
,p_ass_attribute30 => p_ass_attribute30
,p_scl_segment1 => l_scl_segment1
,p_scl_segment2 => l_scl_segment2
,p_scl_segment3 => l_scl_segment3
,p_scl_segment4 => l_scl_segment4
,p_scl_segment5 => l_scl_segment5
,p_scl_segment6 => l_scl_segment6
,p_scl_segment7 => l_scl_segment7
,p_scl_segment8 => l_scl_segment8
,p_scl_segment9 => l_scl_segment9
,p_scl_segment10 => l_scl_segment10
,p_scl_segment11 => l_scl_segment11
,p_scl_segment12 => l_scl_segment12
,p_scl_segment13 => l_scl_segment13
,p_scl_segment14 => l_scl_segment14
,p_scl_segment15 => l_scl_segment15
,p_scl_segment16 => l_scl_segment16
,p_scl_segment17 => l_scl_segment17
,p_scl_segment18 => l_scl_segment18
,p_scl_segment19 => l_scl_segment19
,p_scl_segment20 => l_scl_segment20
,p_scl_segment21 => l_scl_segment21
,p_scl_segment22 => l_scl_segment22
,p_scl_segment23 => l_scl_segment23
,p_scl_segment24 => l_scl_segment24
,p_scl_segment25 => l_scl_segment25
,p_scl_segment26 => l_scl_segment26
,p_scl_segment27 => l_scl_segment27
,p_scl_segment28 => l_scl_segment28
,p_scl_segment29 => l_scl_segment29
,p_scl_segment30 => l_scl_segment30
,p_scl_concat_segments => l_old_scl_conc_segments
,p_concatenated_segments => l_scl_concatenated_segments
,p_pgp_segment1 => l_pgp_segment1
,p_pgp_segment2 => l_pgp_segment2
,p_pgp_segment3 => l_pgp_segment3
,p_pgp_segment4 => l_pgp_segment4
,p_pgp_segment5 => l_pgp_segment5
,p_pgp_segment6 => l_pgp_segment6
,p_pgp_segment7 => l_pgp_segment7
,p_pgp_segment8 => l_pgp_segment8
,p_pgp_segment9 => l_pgp_segment9
,p_pgp_segment10 => l_pgp_segment10
,p_pgp_segment11 => l_pgp_segment11
,p_pgp_segment12 => l_pgp_segment12
,p_pgp_segment13 => l_pgp_segment13
,p_pgp_segment14 => l_pgp_segment14
,p_pgp_segment15 => l_pgp_segment15
,p_pgp_segment16 => l_pgp_segment16
,p_pgp_segment17 => l_pgp_segment17
,p_pgp_segment18 => l_pgp_segment18
,p_pgp_segment19 => l_pgp_segment19
,p_pgp_segment20 => l_pgp_segment20
,p_pgp_segment21 => l_pgp_segment21
,p_pgp_segment22 => l_pgp_segment22
,p_pgp_segment23 => l_pgp_segment23
,p_pgp_segment24 => l_pgp_segment24
,p_pgp_segment25 => l_pgp_segment25
,p_pgp_segment26 => l_pgp_segment26
,p_pgp_segment27 => l_pgp_segment27
,p_pgp_segment28 => l_pgp_segment28
,p_pgp_segment29 => l_pgp_segment29
,p_pgp_segment30 => l_pgp_segment30
,p_concat_segments => l_old_group_name
,p_contract_id => p_contract_id
,p_establishment_id => p_establishment_id
,p_collective_agreement_id => p_collective_agreement_id
,p_cagr_id_flex_num => p_cagr_id_flex_num
,p_cag_segment1 => l_cag_segment1
,p_cag_segment2 => l_cag_segment2
,p_cag_segment3 => l_cag_segment3
,p_cag_segment4 => l_cag_segment4
,p_cag_segment5 => l_cag_segment5
,p_cag_segment6 => l_cag_segment6
,p_cag_segment7 => l_cag_segment7
,p_cag_segment8 => l_cag_segment8
,p_cag_segment9 => l_cag_segment9
,p_cag_segment10 => l_cag_segment10
,p_cag_segment11 => l_cag_segment11
,p_cag_segment12 => l_cag_segment12
,p_cag_segment13 => l_cag_segment13
,p_cag_segment14 => l_cag_segment14
,p_cag_segment15 => l_cag_segment15
,p_cag_segment16 => l_cag_segment16
,p_cag_segment17 => l_cag_segment17
,p_cag_segment18 => l_cag_segment18
,p_cag_segment19 => l_cag_segment19
,p_cag_segment20 => l_cag_segment20
,p_title => p_title
,p_notice_period => p_notice_period
,p_notice_period_uom => p_notice_period_uom
,p_employee_category => p_employee_category
,p_work_at_home => p_work_at_home
,p_job_post_source_name => p_job_post_source_name
,p_cagr_grade_def_id => l_cagr_grade_def_id
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
,p_comment_id => l_comment_id
,p_applicant_rank => p_applicant_rank
,p_posting_content_id => p_posting_content_id
,p_grade_ladder_pgm_id => p_grade_ladder_pgm_id
,p_supervisor_assignment_id => p_supervisor_assignment_id
,p_cagr_concatenated_segments => l_cagr_concatenated_segments
,p_group_name => l_group_name
,p_people_group_id => l_people_group_id
,p_soft_coding_keyflex_id => l_soft_coding_keyflex_id
);
,p_datetrack_update_mode in varchar2
,p_assignment_id in number
,p_object_version_number in out nocopy number
,p_assignment_status_type_id in number
,p_change_reason in varchar2
,p_effective_start_date out nocopy date
,p_effective_end_date out nocopy date
) is
--
-- Declare cursors and local variables
--
l_object_version_number_orig number;
,p_datetrack_update_mode => p_datetrack_update_mode
,p_assignment_id => p_assignment_id
,p_object_version_number => p_object_version_number
,p_assignment_status_type_id => p_assignment_status_type_id
,p_change_reason => p_change_reason
);
hr_assignment_internal.update_status_type_apl_asg
(p_effective_date => l_effective_date
,p_datetrack_update_mode => p_datetrack_update_mode
,p_assignment_id => p_assignment_id
,p_object_version_number => l_object_version_number
,p_expected_system_status => 'OFFER'
,p_assignment_status_type_id => p_assignment_status_type_id
,p_change_reason => p_change_reason
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
);
,p_datetrack_update_mode => p_datetrack_update_mode
,p_assignment_id => p_assignment_id
,p_object_version_number => l_object_version_number
,p_assignment_status_type_id => p_assignment_status_type_id
,p_change_reason => p_change_reason
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
);
,p_datetrack_update_mode in varchar2
,p_assignment_id in number
,p_object_version_number in out nocopy number
,p_assignment_status_type_id in number
,p_change_reason in varchar2
,p_effective_start_date out nocopy date
,p_effective_end_date out nocopy date
) is
--
-- Declare cursors and local variables
--
l_object_version_number_orig number;
,p_datetrack_update_mode => p_datetrack_update_mode
,p_assignment_id => p_assignment_id
,p_object_version_number => p_object_version_number
,p_assignment_status_type_id => p_assignment_status_type_id
,p_change_reason => p_change_reason
);
hr_assignment_internal.update_status_type_apl_asg
(p_effective_date => l_effective_date
,p_datetrack_update_mode => p_datetrack_update_mode
,p_assignment_id => p_assignment_id
,p_object_version_number => l_object_version_number
,p_expected_system_status => 'ACCEPTED'
,p_assignment_status_type_id => p_assignment_status_type_id
,p_change_reason => p_change_reason
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
);
,p_datetrack_update_mode => p_datetrack_update_mode
,p_assignment_id => p_assignment_id
,p_object_version_number => l_object_version_number
,p_assignment_status_type_id => p_assignment_status_type_id
,p_change_reason => p_change_reason
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
);
,p_datetrack_update_mode IN VARCHAR2
,p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
,p_object_version_number IN OUT NOCOPY per_all_assignments_f.object_version_number%TYPE
,p_assignment_status_type_id IN per_assignment_status_types.assignment_status_type_id%TYPE
,p_change_reason IN per_all_assignments_f.change_reason%TYPE
,p_effective_start_date OUT NOCOPY per_all_assignments_f.effective_start_date%TYPE
,p_effective_end_date OUT NOCOPY per_all_assignments_f.effective_end_date%TYPE
)
IS
--
-- Local variables
--
l_proc VARCHAR2(72);
,p_datetrack_update_mode => p_datetrack_update_mode
,p_assignment_id => p_assignment_id
,p_object_version_number => p_object_version_number
,p_assignment_status_type_id => p_assignment_status_type_id
,p_change_reason => p_change_reason
);
hr_assignment_internal.update_status_type_apl_asg
(p_effective_date => l_effective_date
,p_datetrack_update_mode => p_datetrack_update_mode
,p_assignment_id => p_assignment_id
,p_object_version_number => p_object_version_number
,p_expected_system_status => l_expected_system_status
,p_assignment_status_type_id => p_assignment_status_type_id
,p_change_reason => p_change_reason
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
);
,p_datetrack_update_mode => p_datetrack_update_mode
,p_assignment_id => p_assignment_id
,p_object_version_number => p_object_version_number
,p_assignment_status_type_id => p_assignment_status_type_id
,p_change_reason => p_change_reason
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
);
l_payroll_id_updated boolean;
select user_id
from per_all_assignments_f paf, fnd_user usr, per_all_people_f ppf,
per_all_people_f linkppf
where p_effective_date between paf.effective_start_date and
paf.effective_end_date
and p_effective_date between usr.start_date and
nvl(usr.end_date,p_effective_date)
and p_effective_date between ppf.effective_start_date and
ppf.effective_end_date
and p_effective_date between linkppf.effective_start_date and
linkppf.effective_end_date
and usr.employee_id=linkppf.person_id
and ppf.party_id = linkppf.party_id
and ppf.person_id = paf.person_id
and paf.assignment_id=p_assignment_id
and usr.user_id = fnd_global.user_id;
SELECT asg.assignment_type
,asg.business_group_id
,bus.legislation_code
,person_id ,effective_end_date, application_id
,asg.assignment_status_type_id --7229710
,asg.effective_start_date -- Added for bug 9875744
FROM per_all_assignments_f asg
,per_business_groups_perf bus
WHERE asg.assignment_id = p_assignment_id
AND bus.business_group_id = asg.business_group_id
AND p_effective_date BETWEEN asg.effective_start_date
AND asg.effective_end_date;
SELECT as2.assignment_id, as2.effective_start_date
,as2.effective_end_date
FROM per_all_assignments_f as2
,per_all_assignments_f as1
WHERE as2.person_id = as1.person_id
AND as2.assignment_type = as1.assignment_type
AND cp_effective_date BETWEEN as2.effective_start_date
AND as2.effective_end_date
AND as2.assignment_id <> as1.assignment_id
AND as1.assignment_id = cp_asg_id;
SELECT as2.assignment_id, as2.business_group_id
, as2.effective_start_date, as2.effective_end_date
, as2.application_id, as2.person_id, as2.object_version_number
FROM per_all_assignments_f as2
,per_all_assignments_f as1
WHERE as2.person_id = as1.person_id
AND as2.assignment_type = as1.assignment_type
AND as2.effective_start_date > cp_effective_date
AND as2.assignment_id <> as1.assignment_id
AND as1.assignment_id = cp_asg_id
ORDER BY as2.effective_start_date, as2.assignment_id ASC;
select *
from per_applications
where application_id = cp_application_id;
select assignment_id
from per_all_assignments_f paaf
where paaf.person_id = lp_person_id
and paaf.assignment_type = 'A'
and lp_effective_start_date between paaf.effective_start_date and paaf.effective_end_date
and paaf.assignment_id <> p_assignment_id
and paaf.application_id = lp_application_id
and exists
( select 1
from per_all_assignments_f paaf1
where paaf1.person_id = paaf.person_id
and paaf1.assignment_id = paaf.assignment_id
and paaf1.assignment_type = 'E' );
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));
Select vacancy_id
From per_all_assignments_f
Where assignment_id = l_assg_id
And p_effective_date between effective_start_date and effective_end_date;
select person_id
from per_all_assignments_f
where assignment_id=l_assg_id;
,p_datetrack_mode => hr_api.g_delete
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
,p_business_group_id => l_business_group_id
,p_validation_start_date => l_validation_start_date
,p_validation_end_date => l_validation_end_date
,p_org_now_no_manager_warning => l_org_now_no_manager_warning
);
delete from per_letter_request_lines plrl
where plrl.assignment_id = p_assignment_id
and plrl.assignment_status_type_id = l_assignment.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');
UPDATE per_applications
SET date_end = l_effective_date
where application_id = l_fut_asg.application_id;
,p_program_update_date => l_appl_details.program_update_date
,p_appl_attribute_category => l_appl_details.appl_attribute_category
,p_appl_attribute1 => l_appl_details.appl_attribute1
,p_appl_attribute2 => l_appl_details.appl_attribute2
,p_appl_attribute3 => l_appl_details.appl_attribute3
,p_appl_attribute4 => l_appl_details.appl_attribute4
,p_appl_attribute5 => l_appl_details.appl_attribute5
,p_appl_attribute6 => l_appl_details.appl_attribute6
,p_appl_attribute7 => l_appl_details.appl_attribute7
,p_appl_attribute8 => l_appl_details.appl_attribute8
,p_appl_attribute9 => l_appl_details.appl_attribute9
,p_appl_attribute10 => l_appl_details.appl_attribute10
,p_appl_attribute11 => l_appl_details.appl_attribute11
,p_appl_attribute12 => l_appl_details.appl_attribute12
,p_appl_attribute13 => l_appl_details.appl_attribute13
,p_appl_attribute14 => l_appl_details.appl_attribute14
,p_appl_attribute15 => l_appl_details.appl_attribute15
,p_appl_attribute16 => l_appl_details.appl_attribute16
,p_appl_attribute17 => l_appl_details.appl_attribute17
,p_appl_attribute18 => l_appl_details.appl_attribute18
,p_appl_attribute19 => l_appl_details.appl_attribute19
,p_appl_attribute20 => l_appl_details.appl_attribute20
);
hr_utility.trace(' update all future assignments');
,p_payroll_id_updated => l_payroll_id_updated
,p_other_manager_warning => l_other_manager_warning
,p_no_managers_warning => l_no_managers_warning
,p_org_now_no_manager_warning => l_org_now_no_manager_warning
,p_validation_start_date => l_validation_start_date
,p_validation_end_date => l_validation_end_date
,p_object_version_number => l_asg_ovn
,p_effective_date => l_asg_eff_date
,p_datetrack_mode => hr_api.g_correction
,p_validate => FALSE
,p_hourly_salaried_warning => l_hourly_salaried_warning);
hr_utility.trace(' update all other assignments');
,p_payroll_id_updated => l_payroll_id_updated
,p_other_manager_warning => l_other_manager_warning
,p_no_managers_warning => l_no_managers_warning
,p_org_now_no_manager_warning => l_org_now_no_manager_warning
,p_validation_start_date => l_validation_start_date
,p_validation_end_date => l_validation_end_date
,p_object_version_number => l_asg_ovn
,p_effective_date => l_asg_eff_date
,p_datetrack_mode => hr_api.g_correction
,p_validate => FALSE
,p_hourly_salaried_warning => l_hourly_salaried_warning);
select min(effective_start_date) into l_apl_min_start_date
from per_all_assignments_f
where assignment_id = l_ae_assignment_id;
select min(effective_start_date) into l_emp_min_start_date
from per_all_assignments_f
where assignment_id = l_ae_assignment_id
and assignment_type = 'E';
hr_applicant_internal.Update_PER_PTU_to_EX_APL(
p_business_group_id => l_assignment.business_group_id
,p_person_id => l_assignment.person_id
,p_effective_date => l_emp_min_start_date
,p_person_type_id => l_person_type_id
,p_per_effective_start_date => l_per_effective_start_date
,p_per_effective_end_date => l_per_effective_end_date);
UPDATE per_applications
SET date_end = l_emp_min_start_date - 1
where application_id = l_new_application_id;
select max(effective_end_date) into l_mx_end_dated
from per_assignments_f
where person_id = l_assignment.person_id
and assignment_type = 'A'
and effective_end_date < hr_general.end_of_time
and assignment_id <> p_assignment_id;
select min(effective_start_date) into l_min_no_end_dated
from per_assignments_f
where person_id = l_assignment.person_id
and assignment_type = 'A'
and effective_end_date = hr_general.end_of_time
and assignment_id <> p_assignment_id;
UPDATE per_applications
SET date_end = l_mx_end_dated
where application_id = l_assignment.application_id;
,p_program_update_date => l_appl_details.program_update_date
,p_appl_attribute_category => l_appl_details.appl_attribute_category
,p_appl_attribute1 => l_appl_details.appl_attribute1
,p_appl_attribute2 => l_appl_details.appl_attribute2
,p_appl_attribute3 => l_appl_details.appl_attribute3
,p_appl_attribute4 => l_appl_details.appl_attribute4
,p_appl_attribute5 => l_appl_details.appl_attribute5
,p_appl_attribute6 => l_appl_details.appl_attribute6
,p_appl_attribute7 => l_appl_details.appl_attribute7
,p_appl_attribute8 => l_appl_details.appl_attribute8
,p_appl_attribute9 => l_appl_details.appl_attribute9
,p_appl_attribute10 => l_appl_details.appl_attribute10
,p_appl_attribute11 => l_appl_details.appl_attribute11
,p_appl_attribute12 => l_appl_details.appl_attribute12
,p_appl_attribute13 => l_appl_details.appl_attribute13
,p_appl_attribute14 => l_appl_details.appl_attribute14
,p_appl_attribute15 => l_appl_details.appl_attribute15
,p_appl_attribute16 => l_appl_details.appl_attribute16
,p_appl_attribute17 => l_appl_details.appl_attribute17
,p_appl_attribute18 => l_appl_details.appl_attribute18
,p_appl_attribute19 => l_appl_details.appl_attribute19
,p_appl_attribute20 => l_appl_details.appl_attribute20
);
hr_utility.trace(' update all future assignments');
,p_payroll_id_updated => l_payroll_id_updated
,p_other_manager_warning => l_other_manager_warning
,p_no_managers_warning => l_no_managers_warning
,p_org_now_no_manager_warning => l_org_now_no_manager_warning
,p_validation_start_date => l_validation_start_date
,p_validation_end_date => l_validation_end_date
,p_object_version_number => l_asg_ovn
,p_effective_date => l_asg_eff_date
,p_datetrack_mode => hr_api.g_correction
,p_validate => FALSE
,p_hourly_salaried_warning => l_hourly_salaried_warning);
hr_utility.trace(' update all other assignments');
,p_payroll_id_updated => l_payroll_id_updated
,p_other_manager_warning => l_other_manager_warning
,p_no_managers_warning => l_no_managers_warning
,p_org_now_no_manager_warning => l_org_now_no_manager_warning
,p_validation_start_date => l_validation_start_date
,p_validation_end_date => l_validation_end_date
,p_object_version_number => l_asg_ovn
,p_effective_date => l_asg_eff_date
,p_datetrack_mode => hr_api.g_correction
,p_validate => FALSE
,p_hourly_salaried_warning => l_hourly_salaried_warning);
SELECT asg.object_version_number
,asg.assignment_type
,asg.period_of_service_id
,asg.person_id
,asg.primary_flag
,asg.effective_start_date
,asg.effective_end_date
,MAX(mxa.effective_end_date) AS max_effective_end_date
,pds.actual_termination_date
FROM per_all_assignments_f asg
,per_all_assignments_f mxa
,per_periods_of_service pds
WHERE pds.period_of_service_id(+) = asg.period_of_service_id
AND mxa.assignment_id = asg.assignment_id
AND asg.assignment_id = csr_new_assignment.p_assignment_id
AND csr_new_assignment.p_effective_date BETWEEN asg.effective_start_date
AND asg.effective_end_date
GROUP BY asg.object_version_number
,asg.assignment_type
,asg.period_of_service_id
,asg.person_id
,asg.primary_flag
,asg.effective_start_date
,asg.effective_end_date
,pds.actual_termination_date;
SELECT asg.assignment_id
,asg.period_of_service_id
,asg.assignment_type
,asg.primary_flag
,asg.person_id
,asg.effective_start_date
,asg.effective_end_date
FROM per_all_assignments_f asg
WHERE asg.person_id = csr_old_assignment.p_person_id
AND csr_old_assignment.p_effective_date BETWEEN asg.effective_start_date
AND asg.effective_end_date
AND asg.primary_flag = 'Y'
AND asg.assignment_type = 'E';
hr_assignment.update_primary
(p_assignment_id => l_old_assignment.assignment_id -- #2468916:instead of p_assignment_id
,p_period_of_service_id => l_old_assignment.period_of_service_id -- #2468916:instead of new.
,p_new_primary_ass_id => p_assignment_id
,p_sdate => l_effective_date
,p_new_primary_flag => 'Y'
,p_mode => hr_api.g_update
,p_last_updated_by => TO_NUMBER(NULL)
,p_last_update_login => TO_NUMBER(NULL)
);
hr_assignment.do_primary_update(l_old_assignment.assignment_id --p_assignment_id
,l_effective_date -- p_sdate
,'N' -- primary flag
,'N' -- current asg
,TO_NUMBER(NULL) --p_last_updated_by
,TO_NUMBER(NULL) --p_last_update_login
);
SELECT asg.object_version_number
,asg.assignment_type
,asg.period_of_placement_date_start
,asg.person_id
,asg.primary_flag
,asg.effective_start_date
,asg.effective_end_date
,MAX(mxa.effective_end_date) AS max_effective_end_date
,pop.actual_termination_date
FROM per_all_assignments_f asg
,per_all_assignments_f mxa
,per_periods_of_placement pop
WHERE pop.person_id (+) = asg.person_id
AND pop.date_start (+) = asg.period_of_placement_date_start
AND mxa.assignment_id = asg.assignment_id
AND asg.assignment_id = csr_new_assignment.p_assignment_id
AND csr_new_assignment.p_effective_date BETWEEN asg.effective_start_date
AND asg.effective_end_date
GROUP BY asg.object_version_number
,asg.assignment_type
,asg.period_of_placement_date_start
,asg.person_id
,asg.primary_flag
,asg.effective_start_date
,asg.effective_end_date
,pop.actual_termination_date;
SELECT asg.assignment_id
,asg.assignment_type
,asg.period_of_placement_date_start
,asg.primary_flag
,asg.person_id
,asg.effective_start_date
,asg.effective_end_date
FROM per_all_assignments_f asg
WHERE asg.person_id = csr_old_assignment.p_person_id
AND csr_old_assignment.p_effective_date BETWEEN asg.effective_start_date
AND asg.effective_end_date
AND asg.primary_flag = 'Y'
AND asg.assignment_type = 'C';
hr_assignment.update_primary_cwk
(p_assignment_id => l_old_assignment.assignment_id
,p_person_id => p_person_id
,p_pop_date_start => l_old_assignment.period_of_placement_date_start
,p_new_primary_ass_id => p_assignment_id
,p_sdate => l_effective_date
,p_new_primary_flag => 'Y'
,p_mode => hr_api.g_update
,p_last_updated_by => TO_NUMBER(NULL)
,p_last_update_login => TO_NUMBER(NULL)
);
hr_assignment.update_primary_cwk
(p_assignment_id => p_assignment_id
,p_person_id => p_person_id
,p_pop_date_start => l_new_assignment.period_of_placement_date_start
,p_new_primary_ass_id => p_assignment_id
,p_sdate => l_effective_date
,p_new_primary_flag => 'Y'
,p_mode => hr_api.g_update
,p_last_updated_by => TO_NUMBER(NULL)
,p_last_update_login => TO_NUMBER(NULL)
);
,p_datetrack_update_mode IN VARCHAR2
,p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
,p_object_version_number IN OUT NOCOPY per_all_assignments_f.object_version_number%TYPE
,p_assignment_status_type_id IN per_assignment_status_types.assignment_status_type_id%TYPE
,p_change_reason IN per_all_assignments_f.change_reason%TYPE
,p_effective_start_date OUT NOCOPY per_all_assignments_f.effective_start_date%TYPE
,p_effective_end_date OUT NOCOPY per_all_assignments_f.effective_end_date%TYPE
)
IS
--
-- Local variables
--
l_proc VARCHAR2(72);
,p_datetrack_update_mode => p_datetrack_update_mode
,p_assignment_id => p_assignment_id
,p_object_version_number => p_object_version_number
,p_assignment_status_type_id => p_assignment_status_type_id
,p_change_reason => p_change_reason
);
hr_assignment_internal.update_status_type_apl_asg
(p_effective_date => l_effective_date
,p_datetrack_update_mode => p_datetrack_update_mode
,p_assignment_id => p_assignment_id
,p_object_version_number => p_object_version_number
,p_expected_system_status => l_expected_system_status
,p_assignment_status_type_id => p_assignment_status_type_id
,p_change_reason => p_change_reason
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
);
,p_datetrack_update_mode => p_datetrack_update_mode
,p_assignment_id => p_assignment_id
,p_object_version_number => p_object_version_number
,p_assignment_status_type_id => p_assignment_status_type_id
,p_change_reason => p_change_reason
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
);
,p_datetrack_update_mode IN VARCHAR2
,p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
,p_object_version_number IN OUT NOCOPY per_all_assignments_f.object_version_number%TYPE
,p_assignment_status_type_id IN per_assignment_status_types.assignment_status_type_id%TYPE
,p_change_reason IN per_all_assignments_f.change_reason%TYPE
,p_effective_start_date OUT NOCOPY per_all_assignments_f.effective_start_date%TYPE
,p_effective_end_date OUT NOCOPY per_all_assignments_f.effective_end_date%TYPE
)
IS
--
-- Local variables
--
l_proc VARCHAR2(72);
,p_datetrack_update_mode => p_datetrack_update_mode
,p_assignment_id => p_assignment_id
,p_object_version_number => p_object_version_number
,p_assignment_status_type_id => p_assignment_status_type_id
,p_change_reason => p_change_reason
);
hr_assignment_internal.update_status_type_apl_asg
(p_effective_date => l_effective_date
,p_datetrack_update_mode => p_datetrack_update_mode
,p_assignment_id => p_assignment_id
,p_object_version_number => p_object_version_number
,p_expected_system_status => l_expected_system_status
,p_assignment_status_type_id => p_assignment_status_type_id
,p_change_reason => p_change_reason
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
);
,p_datetrack_update_mode => p_datetrack_update_mode
,p_assignment_id => p_assignment_id
,p_object_version_number => p_object_version_number
,p_assignment_status_type_id => p_assignment_status_type_id
,p_change_reason => p_change_reason
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
);
PROCEDURE delete_assignment
(p_validate IN boolean default false
,p_effective_date IN DATE
,p_datetrack_mode IN VARCHAR2
,p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
,p_object_version_number IN OUT NOCOPY per_all_assignments_f.object_version_number%TYPE
,p_effective_start_date OUT NOCOPY per_all_assignments_f.effective_start_date%TYPE
,p_effective_end_date OUT NOCOPY per_all_assignments_f.effective_end_date%TYPE
,p_loc_change_tax_issues OUT NOCOPY boolean
,p_delete_asg_budgets OUT NOCOPY boolean
,p_org_now_no_manager_warning OUT NOCOPY boolean
,p_element_salary_warning OUT NOCOPY boolean
,p_element_entries_warning OUT NOCOPY boolean
,p_spp_warning OUT NOCOPY boolean
,P_cost_warning OUT NOCOPY Boolean
,p_life_events_exists OUT NOCOPY Boolean
,p_cobra_coverage_elements OUT NOCOPY Boolean
,p_assgt_term_elements OUT NOCOPY Boolean)
IS
l_effective_date date;
l_proc varchar2(72) := g_package||'delete_assignment';
select assignment_type
from per_all_assignments_f
where assignment_id = p_assignment_id
and p_effective_date between effective_start_date and effective_end_date;
savepoint hr_delete_assignment;
hr_utility.set_location('Selected assignment is of type Benifit', 10);
hr_assignment_bkp.delete_assignment_b
(p_effective_date => l_effective_date
,p_assignment_id => p_assignment_id
,p_datetrack_mode => p_datetrack_mode
);
(p_module_name => 'DELETE_ASSIGNMENT'
,p_hook_type => 'BP'
);
,p_delete_asg_budgets => p_delete_asg_budgets
,p_org_now_no_manager_warning => p_org_now_no_manager_warning
,p_element_salary_warning => p_element_salary_warning
,p_element_entries_warning => p_element_entries_warning
,p_spp_warning => p_spp_warning
,P_cost_warning => P_cost_warning
,p_life_events_exists => p_life_events_exists
,p_cobra_coverage_elements => p_cobra_coverage_elements
,p_assgt_term_elements => p_assgt_term_elements );
hr_assignment_bkp.delete_assignment_a
(p_effective_date => l_effective_date
,p_assignment_id => p_assignment_id
,p_datetrack_mode => p_datetrack_mode
,p_loc_change_tax_issues => p_loc_change_tax_issues
,p_delete_asg_budgets => p_delete_asg_budgets
,p_org_now_no_manager_warning => p_org_now_no_manager_warning
,p_element_salary_warning => p_element_salary_warning
,p_element_entries_warning => p_element_entries_warning
,p_spp_warning => p_spp_warning
,P_cost_warning => P_cost_warning
,p_life_events_exists => p_life_events_exists
,p_cobra_coverage_elements => p_cobra_coverage_elements
,p_assgt_term_elements => p_assgt_term_elements );
(p_module_name => 'DELETE_ASSIGNMENT'
,p_hook_type => 'AP'
);
p_delete_asg_budgets := null;
ROLLBACK TO hr_delete_assignment;
p_delete_asg_budgets := null;
ROLLBACK TO hr_delete_assignment;
END; -- End of delete_assignment Procedure