[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
procedure INSERT_ALU (
--
--******************************************************************************
--* Inserts Assignment Link Usages for a new element link. *
--* bug 12833901 this is also called from delete next change so determine if *
--* existing alus exist before inserting. If they do exist but are different *
--* delete old alus and insert new *
--******************************************************************************
--
p_business_group_id number,
p_people_group_id number,
p_element_link_id number,
p_effective_start_date date,
p_effective_end_date date) is
--
v_previous_assignment_id number;
select greatest(asg.effective_start_date,el.effective_start_date) effective_start_date,
least(asg.effective_end_date, el.effective_end_date) effective_end_date,
asg.business_group_id,
el.element_link_id
from per_all_assignments_f asg,
pay_people_groups ppg,
pay_element_links_f el,
pay_people_groups el_pg
where el_pg.id_flex_num = ppg.id_flex_num
and el.element_link_id = p_element_link_id
and el.business_group_id + 0 = asg.business_group_id
and el.effective_start_date <= asg.effective_end_date
and el.effective_end_date >= asg.effective_start_date
and el_pg.people_group_id = el.people_group_id
and asg.assignment_id = p_assignment_id
and asg.assignment_type not in ('A','O')
and asg.people_group_id is not null
and ppg.people_group_id = asg.people_group_id
and (el_pg.segment1 is null or el_pg.segment1 = ppg.segment1)
and (el_pg.segment2 is null or el_pg.segment2 = ppg.segment2)
and (el_pg.segment3 is null or el_pg.segment3 = ppg.segment3)
and (el_pg.segment4 is null or el_pg.segment4 = ppg.segment4)
and (el_pg.segment5 is null or el_pg.segment5 = ppg.segment5)
and (el_pg.segment6 is null or el_pg.segment6 = ppg.segment6)
and (el_pg.segment7 is null or el_pg.segment7 = ppg.segment7)
and (el_pg.segment8 is null or el_pg.segment8 = ppg.segment8)
and (el_pg.segment9 is null or el_pg.segment9 = ppg.segment9)
and (el_pg.segment10 is null or el_pg.segment10 = ppg.segment10)
and (el_pg.segment11 is null or el_pg.segment11 = ppg.segment11)
and (el_pg.segment12 is null or el_pg.segment12 = ppg.segment12)
and (el_pg.segment13 is null or el_pg.segment13 = ppg.segment13)
and (el_pg.segment14 is null or el_pg.segment14 = ppg.segment14)
and (el_pg.segment15 is null or el_pg.segment15 = ppg.segment15)
and (el_pg.segment16 is null or el_pg.segment16 = ppg.segment16)
and (el_pg.segment17 is null or el_pg.segment17 = ppg.segment17)
and (el_pg.segment18 is null or el_pg.segment18 = ppg.segment18)
and (el_pg.segment19 is null or el_pg.segment19 = ppg.segment19)
and (el_pg.segment20 is null or el_pg.segment20 = ppg.segment20)
and (el_pg.segment21 is null or el_pg.segment21 = ppg.segment21)
and (el_pg.segment22 is null or el_pg.segment22 = ppg.segment22)
and (el_pg.segment23 is null or el_pg.segment23 = ppg.segment23)
and (el_pg.segment24 is null or el_pg.segment24 = ppg.segment24)
and (el_pg.segment25 is null or el_pg.segment25 = ppg.segment25)
and (el_pg.segment26 is null or el_pg.segment26 = ppg.segment26)
and (el_pg.segment27 is null or el_pg.segment27 = ppg.segment27)
and (el_pg.segment28 is null or el_pg.segment28 = ppg.segment28)
and (el_pg.segment29 is null or el_pg.segment29 = ppg.segment29)
and (el_pg.segment30 is null or el_pg.segment30 = ppg.segment30)
and (el.organization_id is null or el.organization_id = asg.organization_id)
and (el.employment_category is null or el.employment_category = asg.employment_category)
and (el.payroll_id is null or el.payroll_id = asg.payroll_id)
order by 4,1;
select id_flex_num,
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 link_group
where link_group.people_group_id = p_people_group_id;
hr_utility.set_location('PAY_ASG_LINK_USAGES_PKG.INSERT_ALU. row_count:'||row_count,50);
delete from pay_assignment_link_usages_f
where element_link_id = p_element_link_id;
v_old_asg_id_tab.delete;
v_old_start_date_tab.delete;
v_old_end_date_tab.delete;
insert into pay_assignment_link_usages_f
(assignment_link_usage_id
,effective_start_date
,effective_end_date
,element_link_id
,assignment_id)
values
(pay_assignment_link_usages_s.nextval
,v_start_date_tab(i)
,v_end_date_tab(i)
,p_element_link_id
,v_asg_id_tab(i)
);
v_asg_id_tab.delete;
v_start_date_tab.delete;
v_end_date_tab.delete;
hr_utility.set_location('PAY_ASG_LINK_USAGES_PKG.INSERT_ALU',10);
select assignment_id, effective_start_date, effective_end_date
bulk collect into
v_old_asg_id_tab, v_old_start_date_tab, v_old_end_date_tab
from pay_assignment_link_usages_f
where element_link_id = p_element_link_id
order by 1,2;
hr_utility.set_location('PAY_ASG_LINK_USAGES_PKG.INSERT_ALU. old_alu_count:'||v_old_asg_id_tab.count,20);
select organization_id, employment_category, payroll_id
into l_lnk_org_id, l_lnk_emp_cat, l_lnk_payroll_id
from pay_element_links_f
where element_link_id = p_element_link_id
and p_effective_start_date between
effective_start_date and effective_end_date;
select assignment.assignment_id,
assignment.effective_start_date,
assignment.effective_end_date
from per_all_assignments_f ASSIGNMENT,
pay_people_groups ASSIGNMENT_GROUP
where assignment.assignment_type not in (''A'',''O'')
and assignment.business_group_id + 0 = :p_business_group_id
and assignment.effective_start_date <= :p_effective_end_date
and assignment.effective_end_date >= :p_effective_start_date
and assignment_group.id_flex_num = :p_id_flex_num
and assignment_group.people_group_id = assignment.people_group_id';
statem := statem || ' for update';
hr_utility.set_location('PAY_ASG_LINK_USAGES_PKG.INSERT_ALU. row_count:'||row_count,50);
end insert_alu;
p_delete_mode varchar2,
p_effective_start_date date,
p_effective_end_date date,
p_validation_start_date date,
p_validation_end_date date ) is
--
v_session_date date;
if p_delete_mode = 'DELETE' then
--
v_session_date := p_validation_start_date -1;
delete
from pay_assignment_link_usages_f
where element_link_id = p_element_link_id
and effective_start_date >= p_validation_start_date;
if p_delete_mode = 'DELETE' then
--
update pay_assignment_link_usages_f
set effective_end_date = v_session_date
where element_link_id = p_element_link_id
and v_session_date between effective_start_date
and effective_end_date;
if p_delete_mode in ('ZAP') then
--
delete
from pay_assignment_link_usages_f
where element_link_id = p_element_link_id;
if p_delete_mode = 'DELETE_NEXT_CHANGE' then
--
insert_ALU (
p_business_group_id,
p_people_group_id,
p_element_link_id,
p_effective_start_date,
p_effective_end_date );
select 1
from pay_people_groups el_pg,
pay_people_groups asg_pg
where asg_pg.people_group_id = p_asg_people_group_id
and el_pg.people_group_id = p_link_people_group_id
and el_pg.id_flex_num = asg_pg.id_flex_num
and (el_pg.segment1 is null or el_pg.segment1 = asg_pg.segment1)
and (el_pg.segment2 is null or el_pg.segment2 = asg_pg.segment2)
and (el_pg.segment3 is null or el_pg.segment3 = asg_pg.segment3)
and (el_pg.segment4 is null or el_pg.segment4 = asg_pg.segment4)
and (el_pg.segment5 is null or el_pg.segment5 = asg_pg.segment5)
and (el_pg.segment6 is null or el_pg.segment6 = asg_pg.segment6)
and (el_pg.segment7 is null or el_pg.segment7 = asg_pg.segment7)
and (el_pg.segment8 is null or el_pg.segment8 = asg_pg.segment8)
and (el_pg.segment9 is null or el_pg.segment9 = asg_pg.segment9)
and (el_pg.segment10 is null or el_pg.segment10 = asg_pg.segment10)
and (el_pg.segment11 is null or el_pg.segment11 = asg_pg.segment11)
and (el_pg.segment12 is null or el_pg.segment12 = asg_pg.segment12)
and (el_pg.segment13 is null or el_pg.segment13 = asg_pg.segment13)
and (el_pg.segment14 is null or el_pg.segment14 = asg_pg.segment14)
and (el_pg.segment15 is null or el_pg.segment15 = asg_pg.segment15)
and (el_pg.segment16 is null or el_pg.segment16 = asg_pg.segment16)
and (el_pg.segment17 is null or el_pg.segment17 = asg_pg.segment17)
and (el_pg.segment18 is null or el_pg.segment18 = asg_pg.segment18)
and (el_pg.segment19 is null or el_pg.segment19 = asg_pg.segment19)
and (el_pg.segment20 is null or el_pg.segment20 = asg_pg.segment20)
and (el_pg.segment21 is null or el_pg.segment21 = asg_pg.segment21)
and (el_pg.segment22 is null or el_pg.segment22 = asg_pg.segment22)
and (el_pg.segment23 is null or el_pg.segment23 = asg_pg.segment23)
and (el_pg.segment24 is null or el_pg.segment24 = asg_pg.segment24)
and (el_pg.segment25 is null or el_pg.segment25 = asg_pg.segment25)
and (el_pg.segment26 is null or el_pg.segment26 = asg_pg.segment26)
and (el_pg.segment27 is null or el_pg.segment27 = asg_pg.segment27)
and (el_pg.segment28 is null or el_pg.segment28 = asg_pg.segment28)
and (el_pg.segment29 is null or el_pg.segment29 = asg_pg.segment29)
and (el_pg.segment30 is null or el_pg.segment30 = asg_pg.segment30)
;
g_alu_asg_hist.delete;
g_alu_asg_pg_hist.delete;
select
people_group_id
,effective_start_date
,effective_end_date
from per_all_assignments_f
where assignment_id = p_assignment_id
and assignment_type not in ('A','O')
and people_group_id is not null
order by effective_start_date;
g_alu_asg_pg_hist.delete;
select 1 from pay_assignment_link_usages_f
where assignment_id = p_assignment_id
and element_link_id = p_element_link_id;
select 1
from per_all_assignments_f
where assignment_id = p_asg_id
and effective_start_date <= p_end_date
and effective_end_date >= p_start_date
for update nowait
;
insert into pay_assignment_link_usages_f
(assignment_link_usage_id
,effective_start_date
,effective_end_date
,element_link_id
,assignment_id)
values
(pay_assignment_link_usages_s.nextval
,l_alu_start_date_tab(i)
,l_alu_end_date_tab(i)
,l_alu_link_id_tab(i)
,p_assignment_id
);
l_link_id_tab.delete;
l_link_start_date_tab.delete;
l_link_end_date_tab.delete;
select greatest(asg.effective_start_date,el.effective_start_date) effective_start_date,
least(asg.effective_end_date, el.effective_end_date) effective_end_date,
asg.business_group_id,
el.element_link_id
from per_all_assignments_f asg,
pay_people_groups ppg,
pay_element_links_f el,
pay_people_groups el_pg
where el_pg.id_flex_num = ppg.id_flex_num
and el.business_group_id + 0 = asg.business_group_id
and el.effective_start_date <= asg.effective_end_date
and el.effective_end_date >= asg.effective_start_date
and el_pg.people_group_id = el.people_group_id
and asg.assignment_id = p_assignment_id
and asg.assignment_type not in ('A','O')
and asg.people_group_id is not null
and ppg.people_group_id = asg.people_group_id
and (el_pg.segment1 is null or el_pg.segment1 = ppg.segment1)
and (el_pg.segment2 is null or el_pg.segment2 = ppg.segment2)
and (el_pg.segment3 is null or el_pg.segment3 = ppg.segment3)
and (el_pg.segment4 is null or el_pg.segment4 = ppg.segment4)
and (el_pg.segment5 is null or el_pg.segment5 = ppg.segment5)
and (el_pg.segment6 is null or el_pg.segment6 = ppg.segment6)
and (el_pg.segment7 is null or el_pg.segment7 = ppg.segment7)
and (el_pg.segment8 is null or el_pg.segment8 = ppg.segment8)
and (el_pg.segment9 is null or el_pg.segment9 = ppg.segment9)
and (el_pg.segment10 is null or el_pg.segment10 = ppg.segment10)
and (el_pg.segment11 is null or el_pg.segment11 = ppg.segment11)
and (el_pg.segment12 is null or el_pg.segment12 = ppg.segment12)
and (el_pg.segment13 is null or el_pg.segment13 = ppg.segment13)
and (el_pg.segment14 is null or el_pg.segment14 = ppg.segment14)
and (el_pg.segment15 is null or el_pg.segment15 = ppg.segment15)
and (el_pg.segment16 is null or el_pg.segment16 = ppg.segment16)
and (el_pg.segment17 is null or el_pg.segment17 = ppg.segment17)
and (el_pg.segment18 is null or el_pg.segment18 = ppg.segment18)
and (el_pg.segment19 is null or el_pg.segment19 = ppg.segment19)
and (el_pg.segment20 is null or el_pg.segment20 = ppg.segment20)
and (el_pg.segment21 is null or el_pg.segment21 = ppg.segment21)
and (el_pg.segment22 is null or el_pg.segment22 = ppg.segment22)
and (el_pg.segment23 is null or el_pg.segment23 = ppg.segment23)
and (el_pg.segment24 is null or el_pg.segment24 = ppg.segment24)
and (el_pg.segment25 is null or el_pg.segment25 = ppg.segment25)
and (el_pg.segment26 is null or el_pg.segment26 = ppg.segment26)
and (el_pg.segment27 is null or el_pg.segment27 = ppg.segment27)
and (el_pg.segment28 is null or el_pg.segment28 = ppg.segment28)
and (el_pg.segment29 is null or el_pg.segment29 = ppg.segment29)
and (el_pg.segment30 is null or el_pg.segment30 = ppg.segment30)
and (el.organization_id is null or el.organization_id = asg.organization_id)
and (el.employment_category is null or el.employment_category = asg.employment_category)
and (el.payroll_id is null or el.payroll_id = asg.payroll_id)
order by 4,1;
select effective_start_date, effective_end_date, element_link_id
from pay_assignment_link_usages_f
where assignment_id = p_assignment_id
order by 3,1;
v_alu_char_tab.delete;
v_old_alu_char_tab.delete;
delete from pay_assignment_link_usages_f alu
where alu.assignment_id = p_assignment_id;
insert into pay_assignment_link_usages_f
(assignment_link_usage_id,
effective_start_date,
effective_end_date,
element_link_id,
assignment_id)
values
(
pay_assignment_link_usages_s.nextval,
v_alu_tab.start_date(i),
v_alu_tab.end_date(i),
v_alu_tab.link_id(i),
p_assignment_id
);
v_alu_tab.start_date.delete;
v_alu_tab.end_date.delete;
v_alu_tab.link_id.delete;
v_old_alu_tab.delete;