The following lines contain the word 'select', 'insert', 'update' or 'delete':
select sysdate into l_current_time
from dual;
select *
from pay_batch_element_links
where batch_element_link_id = p_batch_element_link_id;
update pay_element_links_f
set comment_id = l_bat_rec.comment_id
where
element_link_id = l_link_rec.element_link_id
and effective_start_date = l_link_rec.effective_start_date
and effective_end_date = l_link_rec.effective_end_date
;
update pay_batch_element_links
set element_link_id = l_link_rec.element_link_id
where batch_element_link_id = p_batch_element_link_id;
select
payroll_action_id
,business_group_id
,effective_date
,legislative_parameters
,pay_core_utils.get_parameter
('GEN_LINK_TYPE', legislative_parameters) gen_link_type
,pay_core_utils.get_parameter
('ELE_LINK_ID', legislative_parameters) ele_link_id
,pay_core_utils.get_parameter
('BAT_LINK_ID', legislative_parameters) bat_link_id
from pay_payroll_actions ppa
where payroll_action_id = p_payroll_action_id;
select
bat.batch_element_link_id
from
pay_batch_element_links bat
where
bat.business_group_id = p_bgid
and (bat.batch_element_link_id = p_batlink_id
or ( p_gen_link_type = 'A'
and p_batlink_id is null
and nvl(pay_batch_object_status_pkg.get_status
('BEL',bat.batch_element_link_id)
,'U') <> 'C')
)
and bat.element_link_id is null
--
-- Ensure element type exists.
--
and exists
(select 1 from pay_element_types_f pet
where pet.element_type_id = bat.element_type_id)
order by
bat.element_type_id
,bat.effective_date
for update nowait
;
g_batch_links.delete;
select
pel.element_link_id
,pel.element_type_id
,min(pel.effective_start_date) effective_start_date
,max(pel.effective_end_date) effective_end_date
,pel.link_to_all_payrolls_flag
,pel.payroll_id
,pel.job_id
,pel.grade_id
,pel.position_id
,pel.organization_id
,pel.location_id
,pel.pay_basis_id
,pel.employment_category
,pel.people_group_id
,pel.standard_link_flag
--
,bos.payroll_action_id
,bos.object_status
from
pay_element_links_f pel
,pay_batch_object_status bos
where
p_phase = 1
and ( pel.element_link_id = p_elelink_id
or ( p_gen_link_type = 'A'
and bos.object_status <> 'C'))
and pel.business_group_id = p_bgid
and (pel.standard_link_flag = 'Y'
or pel.people_group_id is not null)
and bos.object_type = 'EL'
and bos.object_id = pel.element_link_id
-- not processed by this payroll action
and nvl(bos.payroll_action_id, -999) <> p_pact_id
and (nvl(bos.object_status, 'C') <> 'P'
or not exists
(select null from pay_payroll_actions
where payroll_action_id = bos.payroll_action_id))
group by
pel.element_link_id
,pel.element_type_id
,pel.link_to_all_payrolls_flag
,pel.payroll_id
,pel.job_id
,pel.grade_id
,pel.position_id
,pel.organization_id
,pel.location_id
,pel.pay_basis_id
,pel.employment_category
,pel.people_group_id
,pel.standard_link_flag
--
,bos.payroll_action_id
,bos.object_status
--
UNION ALL
--
-- element links being processed
--
select
pel.element_link_id
,pel.element_type_id
,min(pel.effective_start_date) effective_start_date
,max(pel.effective_end_date) effective_end_date
,pel.link_to_all_payrolls_flag
,pel.payroll_id
,pel.job_id
,pel.grade_id
,pel.position_id
,pel.organization_id
,pel.location_id
,pel.pay_basis_id
,pel.employment_category
,pel.people_group_id
,pel.standard_link_flag
--
,bos.payroll_action_id
,bos.object_status
from
pay_batch_object_status bos
,pay_element_links_f pel
where
bos.payroll_action_id = p_pact_id
and bos.object_type = 'EL'
and pel.element_link_id = bos.object_id
and pel.business_group_id = p_bgid
and (pel.standard_link_flag = 'Y'
or pel.people_group_id is not null)
group by
pel.element_link_id
,pel.element_type_id
,pel.link_to_all_payrolls_flag
,pel.payroll_id
,pel.job_id
,pel.grade_id
,pel.position_id
,pel.organization_id
,pel.location_id
,pel.pay_basis_id
,pel.employment_category
,pel.people_group_id
,pel.standard_link_flag
--
,bos.payroll_action_id
,bos.object_status
order by
people_group_id
,element_link_id
;
g_standard_links.delete;
g_pg_links.delete;
l_sql := 'select nvl(1,:payroll_action_id) from dual where 1 = 0';
'select distinct asg.person_id
from
pay_payroll_actions ppa
,per_all_assignments_f asg
where
ppa.payroll_action_id = :payroll_action_id
and asg.business_group_id = ppa.business_group_id
and (asg.assignment_type = ''E''
or (asg.people_group_id is not null
and asg.assignment_type not in (''A'',''O'')))
order by asg.person_id';
'select distinct asg.person_id
from
pay_payroll_actions ppa
,per_all_assignments_f asg
,per_periods_of_service pos
where
ppa.payroll_action_id = :payroll_action_id
and asg.business_group_id = ppa.business_group_id
and pos.person_id = asg.person_id
and pos.period_of_service_id = asg.period_of_service_id
and pos.business_group_id = ppa.business_group_id
order by asg.person_id';
select distinct
paf.assignment_id
,paf.person_id
from
per_periods_of_service pos
,per_all_assignments_f paf
where
pos.person_id between p_stperson and p_endperson
and pos.business_group_id = p_bgid
and paf.period_of_service_id = pos.period_of_service_id
and p_creating_alu_flag = 'N'
--
union all
-- We should handle all assignment types for ALUs.
select distinct
paf.assignment_id
,paf.person_id
from
per_all_assignments_f paf
where
paf.person_id between p_stperson and p_endperson
and paf.business_group_id = p_bgid
and (paf.assignment_type = 'E'
or (paf.people_group_id is not null
and paf.assignment_type not in ('A','O')))
and p_creating_alu_flag = 'Y'
order by 1, 2;
select pay_assignment_actions_s.nextval into l_asgact_id
from dual;
select assignment_id into l_asgid
from pay_assignment_actions
where assignment_action_id = p_assactid;
select 'I'
from dual
where exists
(select null
from pay_assignment_actions paa
where paa.payroll_action_id = p_payroll_action_id
and paa.action_status <> 'C')
union all
select ppa.action_status
from pay_payroll_actions ppa
where ppa.payroll_action_id = p_payroll_action_id
;
select
pay_core_utils.get_parameter
('REMOVE_ACT', legislative_parameters) remove_act
into l_remove_act
from pay_payroll_actions
where payroll_action_id = p_payroll_action_id
;
delete from pay_batch_object_status
where payroll_action_id = p_payroll_action_id
and object_status in ('P', 'C')
;
update pay_batch_object_status
set payroll_action_id = null
where payroll_action_id = p_payroll_action_id
;
update pay_batch_object_status
set object_status = l_status
where payroll_action_id = p_payroll_action_id
and object_status = 'P'
;
select count(1) into l_count
from pay_batch_object_status
where payroll_action_id = p_payroll_action_id
and object_status = 'P'
;