[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
04-SEP-1995 DSAXBY 40.2 Missing '/' after the select from
user_errors at bottom of file.
28-SEP-1993 DSAXBY 40.1 Alter time dependent leg check to
join via per_periods_of_service.
22-APR-1993 DSAXBY 3.1 Chge per_all_ass to per_ass.
29-JAN-1993 DSAXBY 3.0 First created.
*/
----------------------------- validate -------------------------------------
/*
NAME
validate - validates assignment interlocks.
DESCRIPTION
This procedure validates assignment level interlock rules
for the run on an individual assignment basis.
It is called from the main run code when interlock flag
is set to 'Y'.
NOTES
There is currently no code within this package to
report those assignments failing interlock rules.
This can only currently be done by setting logging
on and examining the resultant log file.
The reporting ability will be added in due course.
*/
procedure validate
(
pactid in out nocopy number, -- payroll_action_id.
assignid in out nocopy number, -- assignment_id to check.
itpflag in out nocopy varchar2, -- independent time periods flag.
assnum in out nocopy varchar2, -- returned assignment_number.
intstat in out nocopy number -- interlock status.
) is
SUCC constant number := 0;
select 1,
pa1.effective_date,
pa2.effective_date
from pay_payroll_actions pa1,
pay_payroll_actions pa2,
pay_action_classifications pcl,
pay_assignment_actions ac2
where pa1.payroll_action_id = pactid
and ac2.assignment_id = assignid
and pa2.payroll_action_id = ac2.payroll_action_id
and pcl.classification_name = 'SEQUENCED'
and pa2.action_type = pcl.action_type
and (pa2.effective_date > pa1.effective_date
or (ac2.action_status not in ('C', 'S')
and pa2.effective_date <= pa1.effective_date));
select 1,
pa1.effective_date,
pa2.effective_date,
as1.assignment_number
from per_assignments_f as1,
pay_payroll_actions pa1,
pay_payroll_actions pa2,
pay_action_classifications pcl,
pay_assignment_actions ac2,
pay_object_groups pog_act,
pay_object_groups pog_grp
where pa1.payroll_action_id = pactid
and as1.assignment_id = assignid
and pa1.effective_date between
as1.effective_start_date and as1.effective_end_date
and pog_act.source_id = as1.assignment_id
and pog_act.source_type = 'PAF'
and pog_act.parent_object_group_id = pog_grp.parent_object_group_id
and pog_grp.source_type = 'PAF'
and pog_grp.source_id = ac2.assignment_id
and pa2.payroll_action_id = ac2.payroll_action_id
and pcl.classification_name = 'SEQUENCED'
and pa2.action_type = pcl.action_type
and (pa2.effective_date > pa1.effective_date
or (ac2.action_status not in ('C', 'S')
and pa2.effective_date <= pa1.effective_date));
select 1,
pa1.effective_date,
pa2.effective_date,
as1.assignment_number
from per_assignments_f as1,
per_assignments_f as2,
pay_payroll_actions pa1,
pay_payroll_actions pa2,
pay_action_classifications pcl,
pay_assignment_actions ac2,
per_periods_of_service pos
where pa1.payroll_action_id = pactid
and as1.assignment_id = assignid
and pa1.effective_date between
as1.effective_start_date and as1.effective_end_date
and pos.period_of_service_id = as1.period_of_service_id
and as2.period_of_service_id = pos.period_of_service_id
and ac2.assignment_id = as2.assignment_id
and pa2.payroll_action_id = ac2.payroll_action_id
and pcl.classification_name = 'SEQUENCED'
and pa2.action_type = pcl.action_type
and (pa2.effective_date > pa1.effective_date
or (ac2.action_status not in ('C', 'S')
and pa2.effective_date <= pa1.effective_date));
select ass.assignment_number
into assnum
from pay_payroll_actions ppa,
per_assignments_f ass
where ppa.payroll_action_id = pactid
and ass.assignment_id = assignid
and ass.payroll_id = ppa.payroll_id
and (ppa.effective_date between
ass.effective_start_date and ass.effective_end_date);