[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select pbg.legislation_code
into l_leg_code
from per_business_groups_perf pbg
where pbg.business_group_id = p_bgp_id;
select group_definition_id
into l_definition_id
from pay_group_definitions
where name = p_definition_name;
select group_definition_id
into l_definition_id
from pay_group_definitions
where name = p_definition_name;
select pay_group_definitions_s.nextval
into l_definition_id
from dual;
insert into pay_group_definitions
(group_definition_id,
name)
values
(l_definition_id,
p_definition_name);
p_update_row in varchar2 default 'Y'
)
return number
is
l_object_group_id pay_object_groups.object_group_id%type;
hr_utility.trace('p_update_row ' || p_update_row );
select object_group_id,
start_date,
end_date
into l_object_group_id,
l_start_date,
l_end_date
from pay_object_groups
where source_id = p_assignment_id
and source_type = 'PAF'
and start_date = p_effective_start_date
and end_date = p_effective_end_date
and parent_object_group_id = p_person_group_id
and payroll_id = p_payroll_id;
select count(*)
into l_cnt
from pay_object_groups
where source_id = p_assignment_id
and source_type = 'PAF'
and parent_object_group_id <> p_person_group_id;
if (p_update_row = 'Y') then
--
select pay_object_groups_s.nextval
into l_object_group_id
from dual;
hr_utility.trace('Inserting into POG with ');
insert into pay_object_groups
(object_group_id,
source_id,
source_type,
start_date,
end_date,
payroll_id,
parent_object_group_id)
values
(l_object_group_id,
p_assignment_id,
'PAF',
p_effective_start_date,
p_effective_end_date, -- Bug 14585781 Case A
p_payroll_id,
p_person_group_id
);
p_update_row in varchar2 default 'Y'
)
return number
is
l_definition_id pay_group_definitions.group_definition_id%type;
l_update boolean;
select object_group_id,
start_date,
end_date
into l_object_group_id,
l_start_date,
l_end_date
from pay_object_groups
where source_id = p_person_id
and source_type = 'PPF'
-- bug 9354495
-- and start_date <= p_effective_end_date
-- and end_date >= p_effective_start_date
and period_of_service_id = p_period_of_service_id
and group_definition_id = l_definition_id;
hr_utility.trace('p_update_row = '|| p_update_row);
if (p_update_row = 'Y') then
if (l_start_date > p_effective_start_date) then
l_update := TRUE;
l_update := TRUE;
if (l_update = TRUE) then
if g_debug then
hr_utility.trace('object group id = '|| l_object_group_id);
update pay_object_groups
set start_date = l_upd_start_date,
end_date = l_upd_end_date
where object_group_id = l_object_group_id;
if (p_update_row = 'Y') then
--
select pay_object_groups_s.nextval
into l_object_group_id
from dual;
insert into pay_object_groups
(object_group_id,
source_id,
source_type,
start_date,
end_date,
group_definition_id,
period_of_service_id)
values
(l_object_group_id,
p_person_id,
'PPF',
p_effective_start_date,
to_date('4712/12/31', 'YYYY/MM/DD'),
l_definition_id,
p_period_of_service_id
);
select distinct legislation_code, paf.business_group_id
into l_leg_code, l_bg_id
from per_business_groups_perf pbg,
per_all_assignments_f paf
where paf.assignment_id = p_assignment_id
and p_effective_start_date between paf.effective_start_date
and paf.effective_end_date
and paf.business_group_id = pbg.business_group_id;
asg_datetracked_insert
Description
This is called from the Assignment Dynamic Triggers
to maintain the assignment groups
*/
procedure asg_datetracked_insert(p_assignment_id in number,
p_person_id in number,
p_period_of_service_id in number,
p_effective_start_date in date,
p_effective_end_date in date,
p_payroll_id in number
)
is
begin
--
if g_debug then
hr_utility.trace('In asg_datetracked_insert()');
hr_utility.trace('Out asg_datetracked_insert()');
end asg_datetracked_insert;
select parent_object_group_id
into l_parent_object_group_id
from pay_object_groups
where object_group_id = p_object_group_id;
select count(*)
into l_cnt
from pay_object_groups
where parent_object_group_id = l_parent_object_group_id;
delete from pay_object_groups
where object_group_id = l_parent_object_group_id;
delete from pay_object_groups
where object_group_id = p_object_group_id;
update pay_object_groups
set end_date = p_effective_end_date
where object_group_id = p_object_group_id;
asg_datetracked_delete_next
Description
This is called from the Assignment Dynamic Triggers
to maintain the assignment groups
*/
procedure asg_datetracked_delete_next(p_assignment_id in number,
p_effective_end_date in date,
p_effective_end_date_o in date,
p_val_end_date in date
)
is
cursor get_pogpaf(p_assignment_id number,
p_effective_end_date_o date)
is
select object_group_id,
parent_object_group_id,
start_date,
end_date
from pay_object_groups pog_paf
where pog_paf.source_id = p_assignment_id
and pog_paf.source_type = 'PAF'
and pog_paf.end_date = p_effective_end_date_o;
select object_group_id,
start_date,
end_date
from pay_object_groups pog_paf
where object_group_id = p_par_object_group_id
and pog_paf.end_date = p_effective_end_date_o;
hr_utility.trace('In asg_datetracked_delete_next()');
update pay_object_groups
set end_date = p_val_end_date --Bug 14585781 ,Case delete_next/future
where object_group_id = pogrec.object_group_id;
delete from pay_object_groups where source_id = p_assignment_id and source_type = 'PAF'
and start_date > = p_effective_end_date_o
and end_date <= p_val_end_date;
update pay_object_groups
set end_date = p_effective_end_date
where object_group_id = ppfrec.object_group_id;
hr_utility.trace('In asg_datetracked_delete_next()');
end asg_datetracked_delete_next;
select object_group_id,
start_date,
end_date
from pay_object_groups pog_paf
where pog_paf.source_id = p_assignment_id
and pog_paf.source_type = 'PAF'
and pog_paf.end_date > p_effective_end_date
order by start_date;
select pgd.name
into p_definition_name
from pay_group_definitions pgd,
pay_object_groups pog_ppf,
pay_object_groups pog_paf
where pog_paf.source_id = p_assignment_id
and pog_paf.source_type = 'PAF'
and pog_paf.payroll_id = p_payroll_id
and p_effective_date between pog_paf.start_date
and pog_paf.end_date
and pog_paf.parent_object_group_id = pog_ppf.object_group_id
and pog_ppf.group_definition_id = pgd.group_definition_id;
select object_group_id into l_object_group_id
from pay_object_groups pog_paf
where pog_paf.source_id = p_assignment_id
and pog_paf.source_type = 'PAF'
and pog_paf.payroll_id = p_old_payroll_id
and pog_paf.start_date = p_old_effective_start_date
and pog_paf.end_date = p_old_effective_end_date;
asg_datetracked_update
Description
This is called from the Assignment Dynamic Triggers
to maintain the assignment groups
*/
procedure asg_datetracked_update(p_assignment_id in number,
p_person_id in number,
p_period_of_service_id in number,
p_old_effective_start_date in date,
p_old_effective_end_date in date,
p_new_effective_start_date in date,
p_new_effective_end_date in date,
p_old_payroll_id in number,
p_new_payroll_id in number
)
is
l_old_group_name pay_group_definitions.name%type;
hr_utility.trace('In asg_datetracked_update()');
update pay_object_groups
set end_date = p_new_effective_start_date -1
where source_id = p_assignment_id
and source_type = 'PAF'
and payroll_id = p_old_payroll_id
and p_old_effective_end_date between start_date and end_date
and p_old_effective_start_date <> p_new_effective_start_date;
update pay_object_groups
set end_date = p_new_effective_start_date -1
where source_id = p_assignment_id
and source_type = 'PAF'
and payroll_id = p_old_payroll_id
and p_old_effective_end_date between start_date and end_date;
hr_utility.trace('Out asg_datetracked_update()');
end asg_datetracked_update;
update pay_object_groups
set start_date = p_new_effective_start_date
where source_id = p_assignment_id
and source_type = 'PAF'
and start_date = p_old_effective_start_date;
select pos.date_start
,pos.period_of_service_id
into l_new_effective_start_date
,l_period_of_service_id
from per_all_assignments_f asg
,per_periods_of_service pos
where asg.assignment_id = p_assignment_id
and asg.period_of_service_id = pos.period_of_service_id;
update pay_object_groups
set start_date = p_new_effective_start_date
where source_type = 'PPF'
and period_of_service_id = l_period_of_service_id;
update pay_object_groups
set start_date = p_new_effective_start_date
where source_id = p_assignment_id
and source_type = 'PAF'
and start_date = p_old_effective_start_date;
select pos.date_start
,pos.period_of_service_id
into l_new_effective_start_date
,l_period_of_service_id
from per_all_assignments_f asg
,per_periods_of_service pos
where asg.assignment_id = p_assignment_id
and asg.period_of_service_id = pos.period_of_service_id;
update pay_object_groups
set start_date = p_new_effective_start_date
where source_type = 'PPF'
and period_of_service_id = l_period_of_service_id
and start_date = p_old_effective_start_date;
asg_datetracked_ovrr_update
Description
This is called from the Assignment Dynamic Triggers
to maintain the assignment groups
*/
procedure asg_datetracked_ovrr_update(p_assignment_id in number,
p_person_id in number,
p_period_of_service_id in number,
p_old_effective_start_date in date,
p_old_effective_end_date in date,
p_new_effective_start_date in date,
p_new_effective_end_date in date,
p_old_payroll_id in number,
p_new_payroll_id in number
)
is
cursor get_pogpaf(p_assignment_id number,
p_effective_end_date date)
is
select object_group_id,
start_date,
end_date
from pay_object_groups pog_paf
where pog_paf.source_id = p_assignment_id
and pog_paf.source_type = 'PAF';
hr_utility.trace('In asg_datetracked_ovrr_update()');
asg_datetracked_update(p_assignment_id,
p_person_id,
p_period_of_service_id,
p_old_effective_start_date,
p_old_effective_end_date,
p_new_effective_start_date,
p_new_effective_end_date,
p_old_payroll_id,
p_new_payroll_id
);
hr_utility.trace('Out asg_datetracked_ovrr_update()');
end asg_datetracked_ovrr_update;
select object_group_id,
start_date,
end_date
from pay_object_groups pog_paf
where pog_paf.source_id = p_assignment_id
and pog_paf.source_type = 'PAF';
asg_insert_trigger
Description
This is the Dynamic Trigger Code for Assignment Processing
Groups
*/
procedure asg_insert_trigger(p_assignment_id in number,
p_person_id in number,
p_period_of_service_id in number,
p_new_effective_start_date in date,
p_new_effective_end_date in date,
p_new_payroll_id in number,
p_business_group_id in number
)
is
dt_mode varchar2(20);
hr_utility.trace('In asg_insert_trigger()');
asg_datetracked_insert(p_assignment_id,
p_person_id,
p_period_of_service_id,
p_new_effective_start_date,
p_new_effective_end_date,
p_new_payroll_id
);
hr_utility.trace('Out asg_insert_trigger()');
end asg_insert_trigger;
asg_update_trigger
Description
This is the Dynamic Trigger Code for Assignment Processing
Groups
*/
procedure asg_update_trigger(p_assignment_id in number,
p_person_id in number,
p_period_of_service_id in number,
p_old_effective_start_date in date,
p_old_effective_end_date in date,
p_new_effective_start_date in date,
p_new_effective_end_date in date,
p_old_payroll_id in number,
p_new_payroll_id in number,
p_business_group_id in number
)
is
dt_mode varchar2(20);
hr_utility.trace('In asg_update_trigger()');
elsif ( dt_mode = 'UPDATE'
or dt_mode = 'UPDATE_CHANGE_INSERT') then
--
asg_datetracked_update(p_assignment_id,
p_person_id,
p_period_of_service_id,
p_old_effective_start_date,
p_old_effective_end_date,
p_new_effective_start_date,
p_new_effective_end_date,
p_old_payroll_id,
p_new_payroll_id
);
elsif ( dt_mode = 'UPDATE_OVERRIDE') then
--
asg_datetracked_ovrr_update(p_assignment_id,
p_person_id,
p_period_of_service_id,
p_old_effective_start_date,
p_old_effective_end_date,
p_new_effective_start_date,
p_new_effective_end_date,
p_old_payroll_id,
p_new_payroll_id
);
hr_utility.trace('Out asg_update_trigger()');
end asg_update_trigger;
asg_delete_trigger
Description
This is the Dynamic Trigger Code for Assignment Processing
Groups
*/
procedure asg_delete_trigger(p_assignment_id in number,
p_effective_end_date in date,
p_business_group_id in number,
p_effective_end_date_o in date,
p_val_end_date in date
)
is
dt_mode varchar2(20);
hr_utility.trace('In asg_delete_trigger()');
elsif ( dt_mode = 'DELETE') then
--
if g_debug then
hr_utility.trace ('Asg del trig - eff date = '||p_effective_end_date);
elsif dt_mode = 'FUTURE_CHANGE' or dt_mode = 'DELETE_NEXT_CHANGE' then
asg_datetracked_delete_next(p_assignment_id,
p_effective_end_date,
p_effective_end_date_o,
p_val_end_date
);
hr_utility.trace('Out asg_delete_trigger()');
end asg_delete_trigger;
select assignment_id,
person_id,
period_of_service_id,
payroll_id,
effective_start_date,
effective_end_date,
business_group_id
from per_all_assignments_f
where assignment_id = p_asg_id
order by assignment_id, effective_start_date;
PAY_POG_ALL_ASSIGNMENTS_PKG.dyt_mode := 'INSERT';
asg_insert_trigger
(p_assignment_id => asgrec.assignment_id,
p_person_id => asgrec.person_id,
p_period_of_service_id => asgrec.period_of_service_id,
p_new_effective_start_date => asgrec.effective_start_date,
p_new_effective_end_date => asgrec.effective_end_date,
p_new_payroll_id => asgrec.payroll_id,
p_business_group_id => asgrec.business_group_id
);
PAY_POG_ALL_ASSIGNMENTS_PKG.dyt_mode := 'UPDATE';
asg_update_trigger
(p_assignment_id => asgrec.assignment_id,
p_person_id => asgrec.person_id,
p_period_of_service_id => asgrec.period_of_service_id,
p_old_effective_start_date => effective_start_date,
p_old_effective_end_date => effective_end_date,
p_new_effective_start_date => asgrec.effective_start_date,
p_new_effective_end_date => asgrec.effective_end_date,
p_old_payroll_id => pay_id,
p_new_payroll_id => asgrec.payroll_id,
p_business_group_id => asgrec.business_group_id
);