[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
g_plsql_proc_insert varchar2(80);
update_pact - update payroll action row.
DESCRIPTION
Updates relevant information on the payroll action row.
This includes the action_population_status and the
date_earned value. This is obtained in accordance with
the new period dates fix.
NOTES
*/
procedure update_pact
(
p_payroll_action_id in number,
p_action_population_status in varchar2,
p_action_type in varchar2,
p_last_update_date in date,
p_last_updated_by in number,
p_last_update_login in number
) is
l_date_earned date;
select /*+ USE_NL(locked_pact locked locking locks)*/
max(date_earned)
into l_date_earned
from pay_payroll_actions locked_pact,
pay_assignment_actions locked,
pay_assignment_actions locking,
pay_action_interlocks locks
where locking.payroll_action_id = p_payroll_action_id
and locking.assignment_action_id = locks.locking_action_id
and locked.assignment_action_id = locks.locked_action_id
and locked.payroll_action_id = locked_pact.payroll_action_id;
update pay_payroll_actions pac
set pac.action_population_status = p_action_population_status,
pac.last_update_date = p_last_update_date,
pac.last_updated_by = p_last_updated_by,
pac.last_update_login = p_last_update_login
where pac.payroll_action_id = p_payroll_action_id;
update pay_payroll_actions pac
set pac.action_population_status = p_action_population_status,
pac.last_update_date = p_last_update_date,
pac.last_updated_by = p_last_updated_by,
pac.last_update_login = p_last_update_login,
pac.date_earned = l_date_earned
where pac.payroll_action_id = p_payroll_action_id;
update pay_payroll_actions pac
set pac.action_population_status = p_action_population_status,
pac.last_update_date = p_last_update_date,
pac.last_updated_by = p_last_updated_by,
pac.last_update_login = p_last_update_login,
pac.date_earned = l_date_earned
where pac.payroll_action_id = p_payroll_action_id;
end update_pact;
pay_population_ranges. This is used to insert
a chunk of assignments at a time.
NOTES
*/
procedure get_next_pop_chunk_seq
(
pactid in number, -- payroll_action_id.
atype in varchar2, -- action type.
p_lckhandle in varchar2, -- dbms_lock id
lub in varchar2, -- last_updated_by.
lul in varchar2, -- last_update_login.
stperson out nocopy number, -- starting_person_id.
endperson out nocopy number, -- ending_person_id.
chunk out nocopy number, -- chunk_number.
rand_chunk out nocopy number -- chunk_number.
) is
actpopstat varchar2(30);
dummy number; -- need because must select into something.
select pac.action_population_status
into actpopstat
from pay_payroll_actions pac
where pac.payroll_action_id = pactid;
select rge.starting_person_id,
rge.ending_person_id,
rge.chunk_number,
nvl(rge.rand_chunk_number,rge.chunk_number)
into stperson,
endperson,
chunk,
rand_chunk
from pay_population_ranges rge
where rge.payroll_action_id = pactid
and rge.range_status = 'U'
and rownum < 2;
select null
into dummy
from pay_population_ranges rge
where rge.payroll_action_id = pactid
and rownum < 2;
update_pact(pactid, 'A', atype,sysdate,lub,lul);
We need to update the randonised status columns
*/
select pcs_rand.population_status,
ppa.action_population_status
into chk_pop_status,
act_pop_status
from pay_payroll_actions ppa,
pay_chunk_status pcs_pop,
pay_chunk_status pcs_rand
where pcs_pop.payroll_action_id = pactid
and pcs_pop.chunk_number = p_next_chunk
and pcs_rand.payroll_action_id = pcs_pop.payroll_action_id
and pcs_rand.chunk_number = pcs_pop.rand_chunk_number
and ppa.payroll_action_id = pcs_pop.payroll_action_id;
select rge.starting_person_id,
rge.ending_person_id,
rge.chunk_number,
nvl(rge.rand_chunk_number,rge.chunk_number)
into
stperson,
endperson,
chunk,
rand_chunk
from pay_population_ranges rge
where rge.payroll_action_id = pactid
and rge.chunk_number = p_next_chunk
and rownum = 1;
lub in varchar2, -- last_updated_by.
lul in varchar2, -- last_update_login.
stperson out nocopy number, -- starting_person_id.
endperson out nocopy number, -- ending_person_id.
chunk out nocopy number, -- chunk_number.
rand_chunk out nocopy number -- chunk_number.
) is
next_chunk number;
dummy number; -- need because must select into something.
select max(chunk_number)
into next_chunk
from pay_chunk_status
where payroll_action_id = pactid
and population_status = 'U';
select
ppa.action_population_status
into
act_pop_status
from pay_payroll_actions ppa
where ppa.payroll_action_id = pactid;
select null
into dummy
from pay_population_ranges rge
where rge.payroll_action_id = pactid
and rownum < 2;
update_pact(pactid, 'A', atype,sysdate,lub,lul);
lub in varchar2, -- last_updated_by.
lul in varchar2, -- last_update_login.
chunk_type in out nocopy varchar2, -- method for allocating chunk
threads in number default 1, -- Number of Threads
slave_no in number default 1, -- Slave no
curr_chunk in number default 1, -- current chunk
max_chunks in number default 9999, -- Max no of Chunks
stperson out nocopy number, -- starting_person_id.
endperson out nocopy number, -- ending_person_id.
chunk out nocopy number, -- chunk_number.
rand_chunk out nocopy number -- chunk_number.
) is
next_chunk number;
select action_population_status
into pay_pop_status
from pay_payroll_actions
where payroll_action_id = pactid;
select population_status
into chk_pop_status
from pay_chunk_status
where payroll_action_id = pactid
and chunk_number = next_chunk;
pay_population_ranges. This is used to insert
a chunk of assignments at a time.
NOTES
*/
procedure get_next_pop_chunk
(
pactid in number, -- payroll_action_id.
atype in varchar2, -- action type.
p_lckhandle in varchar2, -- dbms_lock id
lub in varchar2, -- last_updated_by.
lul in varchar2, -- last_update_login.
chunk_type in out nocopy varchar2, -- method for allocating chunk
threads in number default 1, -- Number of Threads
slave_no in number default 1, -- Slave no
curr_chunk in number default 1, -- current chunk
max_chunks in number default 9999, -- Max no of Chunks
stperson out nocopy number, -- starting_person_id.
endperson out nocopy number, -- ending_person_id.
chunk out nocopy number, -- chunk_number.
rand_chunk out nocopy number -- chunk_number.
) is
actpopstat varchar2(30);
dummy number; -- need because must select into something.
select PAC.current_chunk_number + 1,
PAC.action_status,
PAC.action_population_status
into next_chunk,
action_status,
action_pop_status
from pay_payroll_actions PAC
where PAC.payroll_action_id = pactid
for update of PAC.current_chunk_number;
select pcs.population_status
into chk_pop_status
from pay_chunk_status pcs
where pcs.payroll_action_id = pactid
and pcs.chunk_number = next_chunk;
update pay_payroll_actions pac
set pac.current_chunk_number = next_chunk
where pac.payroll_action_id = pactid;
update pay_chunk_status
set process_status = 'P'
where payroll_action_id = pactid
and chunk_number = next_chunk;
select max(chunk_number)
into next_chunk
from pay_chunk_status
where payroll_action_id = pactid
and process_status = 'U'
and population_status = 'C';
select action_status,
action_population_status
into pact_act_status,
act_pop_status
from pay_payrolL_actions
where payroll_action_id = pactid;
select process_status
into chk_status
from pay_chunk_status
where payroll_action_id = pactid
and chunk_number = next_chunk
for update of process_status;
update pay_chunk_status
set process_status = 'P'
where payroll_action_id = pactid
and chunk_number = next_chunk;
select action_status, action_population_status
into pact_act_status,
act_pop_status
from pay_payroll_actions
where payroll_action_id = pactid;
select process_status
into chk_status
from pay_chunk_status
where payroll_action_id = pactid
and chunk_number = next_chunk
for update of process_status;
update pay_chunk_status
set process_status = 'P'
where payroll_action_id = pactid
and chunk_number = next_chunk;
pay_population_ranges. This is used to insert
a chunk of assignments at a time.
NOTES
There is a COMMIT in this procedure to release
the locks and update tables.
*/
procedure get_next_proc_chunk
(
pactid in number, -- payroll_action_id.
chunk_type in out nocopy varchar2, -- method for allocating chunk
threads in number default 1, -- Number of Threads
slave_no in number default 1, -- Slave no
curr_chunk in out nocopy number -- current chunk
) is
--
begin
--
-- Before we do any thing mark the previous chunk as complete
if (curr_chunk <> 0) then
--
update pay_chunk_status
set process_status = 'C'
where payroll_action_id = pactid
and chunk_number = curr_chunk;
pay_population_ranges. This is used to insert
a chunk of assignments at a time.
This is a cover for get_next_pop_chunk
NOTES
*/
procedure rangerow
(
pactid in number, -- payroll_action_id.
lub in varchar2, -- last_updated_by.
lul in varchar2, -- last_update_login.
stperson out nocopy number, -- starting_person_id.
endperson out nocopy number, -- ending_person_id.
chunk out nocopy number, -- chunk_number.
rand_chunk out nocopy number, -- chunk_number.
atype in varchar2 -- action type.
) is
l_chunk_type varchar2(30);
reinterlock - Re Inserts Interlocks.
DESCRIPTION
Simply re inserts interlock rows for a child action.
NOTES
This procedure recursively calls itself in case the child action
has children of its own.
*/
procedure reinterlock_child
(
p_pp_assact number,
p_run_assact number,
p_asg_id number,
p_pact_id number,
p_actype varchar2
) is
cursor get_lockers (p_run_act number,
p_pre_act number,
p_asg_id number,
p_pact_id number)
is
select paa.assignment_action_id
from pay_assignment_actions paa
where paa.source_action_id = p_run_act
and paa.assignment_id = p_asg_id
and paa.payroll_action_id = p_pact_id
and not exists (select ''
from pay_action_interlocks pai2
where pai2.locking_action_id = p_pre_act
and pai2.locked_action_id = paa.assignment_action_id
);
select paa.assignment_action_id
from pay_action_classifications pcl,
pay_payroll_actions pac,
pay_assignment_actions paa,
pay_action_interlocks pai
where pai.locked_action_id = p_run_act
and pai.locking_action_id = paa.assignment_action_id
and paa.assignment_action_id <> p_cost_act
and pac.payroll_action_id = paa.payroll_action_id
and pcl.action_type = pac.action_type
and pcl.classification_name = 'TRANSGL'
and not exists (select ''
from pay_action_interlocks pai2
where pai2.locking_action_id = p_cost_act
and pai2.locked_action_id = paa.assignment_action_id
);
reinterlock - Re Inserts Interlocks.
DESCRIPTION
Simply re inserts interlock rows. Based on the primary (master) interlocked
action.
NOTES
*/
procedure reinterlock
(
p_assact number,
p_actype varchar2 default 'U'
) is
--
cursor get_master_actions(p_act number)
is
select paa.assignment_action_id,
paa.assignment_id,
paa.payroll_action_id
from pay_action_interlocks pai,
pay_assignment_actions paa
where pai.locking_action_id = p_act
and pai.locked_action_id = paa.assignment_action_id
and paa.source_action_id is null;
insint - insert interlock row.
DESCRIPTION
Simply inserts an interlock row. Does not commit.
NOTES
*/
procedure insint
(
lockingactid in number,
lockedactid in number
) is
begin
insert into pay_action_interlocks (
locking_action_id,
locked_action_id)
values (lockingactid,
lockedactid);
insact - insert assignment action row.
DESCRIPTION
inserts row into pay_assignment_actions. Does not commit.
NOTES
*/
procedure insact
(
lockingactid in number, -- locking_action_id.
assignid in number default null, -- assignment_id
pactid in number, -- payroll_action_id
chunk in number, -- chunk_number
greid in number default null, -- GRE id.
prepayid in number default null, -- pre_payment_id.
status in varchar2 default 'U', -- action_status.
source_act in number default null, -- source_action_id
object_id in number default null, -- object id
object_type in varchar2 default null, -- object type
start_date in date default null, -- start date
end_date in date default null, -- end date
p_transient_action in boolean default false -- Transient Action
) is
--
l_transient_action boolean;
select action_type,
report_type,
report_qualifier,
report_category,
effective_date
into l_action_type,
l_report_type,
l_report_qualifier,
l_report_category,
l_eff_date
from pay_payroll_actions
where payroll_action_id = pactid;
select temporary_action_flag
into l_temp_act_flag
from pay_report_format_mappings_f
where report_type = l_report_type
and report_qualifier = l_report_qualifier
and report_category = l_report_category
and l_eff_date between effective_start_date
and effective_end_date;
insert into pay_temp_object_actions (
object_action_id,
object_id,
object_type,
payroll_action_id,
action_status,
chunk_number,
action_sequence,
object_version_number
)
select lockingactid,
object_id,
object_type,
pactid,
status,
chunk,
pay_assignment_actions_s.nextval,
1
from dual;
insert into pay_assignment_actions (
assignment_action_id,
assignment_id,
payroll_action_id,
action_status,
chunk_number,
action_sequence,
pre_payment_id,
object_version_number,
tax_unit_id,
source_action_id,
object_id,
object_type,
start_date,
end_date)
select lockingactid,
assignid,
pactid,
status,
chunk,
pay_assignment_actions_s.nextval,
prepayid,
1,
greid,
source_act,
object_id,
object_type,
start_date,
end_date
from dual;
ending person_id and inserts a chunk of assignment actions
plus their associated interlock rows. This function for the
cash action only.
NOTES
*/
procedure proccash
(
pactid in number, -- payroll_action_id.
stperson in number, -- starting person_id of range.
endperson in number, -- ending person_id of range.
chunk in number, -- current chunk_number.
rand_chunk in number, -- current chunk_number.
itpflg in varchar2, -- legislation type.
use_pop_person in number -- use population_ranges person_id column
) is
cursor cashpopcur
(
pactid number,
chunk number,
itpflg varchar2
) is
select /*+ ORDERED
INDEX(pa2 PAY_PAYROLL_ACTIONS_N5)
INDEX(pos PER_PERIODS_OF_SERVICE_N3)
INDEX(as1 PER_ASSIGNMENTS_N4)
INDEX(as2 PER_ASSIGNMENTS_F_PK)
INDEX(act PAY_ASSIGNMENT_ACTIONS_N51)
index(opm PAY_ORG_PAYMENT_METHODS_F_PK)
USE_NL(pop pos ppp opm as1 act as2) */
act.assignment_action_id,
act.assignment_id,
act.tax_unit_id,
ppp.pre_payment_id
from pay_payroll_actions pa1,
pay_payroll_actions pa2,
pay_action_classifications pcl,
pay_population_ranges pop,
per_periods_of_service pos,
per_all_assignments_f as1,
pay_assignment_actions act,
per_all_assignments_f as2,
pay_pre_payments ppp,
pay_org_payment_methods_f opm
where pa1.payroll_action_id = pactid
and pa2.consolidation_set_id +0 = pa1.consolidation_set_id
and pa2.effective_date between
pa1.start_date and pa1.effective_date
and act.payroll_action_id = pa2.payroll_action_id
and act.action_status = 'C'
and pcl.classification_name = 'CASHED'
and pa2.action_type = pcl.action_type
and as1.assignment_id = act.assignment_id
and pa2.effective_date between
as1.effective_start_date and as1.effective_end_date
and as2.assignment_id = act.assignment_id
and pa1.effective_date between
as2.effective_start_date and as2.effective_end_date
and as2.payroll_id + 0 = as1.payroll_id + 0
and pos.period_of_service_id = as1.period_of_service_id
and pop.payroll_action_id = pactid
and pop.chunk_number = chunk
and pos.person_id = pop.person_id
and (as1.payroll_id = pa1.payroll_id or pa1.payroll_id is null)
and ppp.assignment_action_id = act.assignment_action_id
and opm.org_payment_method_id = ppp.org_payment_method_id
and pa1.effective_date between
opm.effective_start_date and opm.effective_end_date
and opm.payment_type_id = pa1.payment_type_id
and (opm.org_payment_method_id = pa1.org_payment_method_id
or pa1.org_payment_method_id is null)
and not exists (
select null
from pay_assignment_actions ac2,
pay_action_interlocks int
where int.locked_action_id = act.assignment_action_id
and ac2.assignment_action_id = int.locking_action_id
and ac2.pre_payment_id = ppp.pre_payment_id)
and not exists (
select null
from per_all_assignments_f as3,
pay_assignment_actions ac3
where itpflg = 'N'
and ac3.payroll_action_id = pa2.payroll_action_id
and ac3.action_status not in ('C', 'S')
and as3.assignment_id = ac3.assignment_id
and pa2.effective_date between
as3.effective_start_date and as3.effective_end_date
and as3.person_id = as2.person_id)
order by act.assignment_id
for update of as1.assignment_id, pos.period_of_service_id;
select /*+ ORDERED
INDEX(pa2 PAY_PAYROLL_ACTIONS_N5)
INDEX(pos PER_PERIODS_OF_SERVICE_N3)
INDEX(as1 PER_ASSIGNMENTS_N4)
INDEX(as2 PER_ASSIGNMENTS_F_PK)
INDEX(act PAY_ASSIGNMENT_ACTIONS_N51)
index(opm PAY_ORG_PAYMENT_METHODS_F_PK)
USE_NL(pos ppp opm as1 act as2) */
act.assignment_action_id,
act.assignment_id,
act.tax_unit_id,
ppp.pre_payment_id
from pay_payroll_actions pa1,
pay_payroll_actions pa2,
pay_action_classifications pcl,
per_periods_of_service pos,
per_all_assignments_f as1,
pay_assignment_actions act,
per_all_assignments_f as2,
pay_pre_payments ppp,
pay_org_payment_methods_f opm
where pa1.payroll_action_id = pactid
and pa2.consolidation_set_id +0 = pa1.consolidation_set_id
and pa2.effective_date between
pa1.start_date and pa1.effective_date
and act.payroll_action_id = pa2.payroll_action_id
and act.action_status = 'C'
and pcl.classification_name = 'CASHED'
and pa2.action_type = pcl.action_type
and as1.assignment_id = act.assignment_id
and pa2.effective_date between
as1.effective_start_date and as1.effective_end_date
and as2.assignment_id = act.assignment_id
and pa1.effective_date between
as2.effective_start_date and as2.effective_end_date
and as2.payroll_id + 0 = as1.payroll_id + 0
and pos.period_of_service_id = as1.period_of_service_id
and pos.person_id between stperson and endperson
and (as1.payroll_id = pa1.payroll_id or pa1.payroll_id is null)
and ppp.assignment_action_id = act.assignment_action_id
and opm.org_payment_method_id = ppp.org_payment_method_id
and pa1.effective_date between
opm.effective_start_date and opm.effective_end_date
and opm.payment_type_id = pa1.payment_type_id
and (opm.org_payment_method_id = pa1.org_payment_method_id
or pa1.org_payment_method_id is null)
and not exists (
select null
from pay_assignment_actions ac2,
pay_action_interlocks int
where int.locked_action_id = act.assignment_action_id
and ac2.assignment_action_id = int.locking_action_id
and ac2.pre_payment_id = ppp.pre_payment_id)
and not exists (
select null
from per_all_assignments_f as3,
pay_assignment_actions ac3
where itpflg = 'N'
and ac3.payroll_action_id = pa2.payroll_action_id
and ac3.action_status not in ('C', 'S')
and as3.assignment_id = ac3.assignment_id
and pa2.effective_date between
as3.effective_start_date and as3.effective_end_date
and as3.person_id = as2.person_id)
order by act.assignment_id
for update of as1.assignment_id, pos.period_of_service_id;
select /*+ ORDERED
INDEX(pa2 PAY_PAYROLL_ACTIONS_PK)
INDEX(pos PER_PERIODS_OF_SERVICE_N3)
INDEX(as1 PER_ASSIGNMENTS_N4)
INDEX(as2 PER_ASSIGNMENTS_F_PK)
INDEX(act PAY_ASSIGNMENT_ACTIONS_N51)
index(opm PAY_ORG_PAYMENT_METHODS_F_PK)
USE_NL(pos pop ppp opm as1 act as2) */
act.assignment_action_id,
act.assignment_id,
act.tax_unit_id,
ppp.pre_payment_id
from pay_payroll_actions pa1,
pay_population_ranges pop,
per_periods_of_service pos,
per_all_assignments_f as1,
pay_assignment_actions act,
pay_payroll_actions pa2,
pay_action_classifications pcl,
per_all_assignments_f as2,
pay_pre_payments ppp,
pay_org_payment_methods_f opm
where pa1.payroll_action_id = pactid
and pa2.consolidation_set_id +0 = pa1.consolidation_set_id
and pa1.effective_date between
pa1.start_date and pa1.effective_date
and act.payroll_action_id = pa2.payroll_action_id
and act.action_status = 'C'
and pcl.classification_name = 'CASHED'
and pa2.action_type = pcl.action_type
and as1.assignment_id = act.assignment_id
and pa1.effective_date between
as1.effective_start_date and as1.effective_end_date
and as2.assignment_id = act.assignment_id
and pa1.effective_date between
as2.effective_start_date and as2.effective_end_date
and as2.payroll_id + 0 = as1.payroll_id + 0
and pos.period_of_service_id = as1.period_of_service_id
and pop.payroll_action_id = pactid
and pop.chunk_number = chunk
and pos.person_id = pop.person_id
and (as1.payroll_id = pa1.payroll_id or pa1.payroll_id is null)
and ppp.assignment_action_id = act.assignment_action_id
and opm.org_payment_method_id = ppp.org_payment_method_id
and pa1.effective_date between
opm.effective_start_date and opm.effective_end_date
and opm.payment_type_id = pa1.payment_type_id
and (opm.org_payment_method_id = pa1.org_payment_method_id
or pa1.org_payment_method_id is null)
and not exists (
select null
from pay_assignment_actions ac2,
pay_action_interlocks int
where int.locked_action_id = act.assignment_action_id
and ac2.assignment_action_id = int.locking_action_id
and ac2.pre_payment_id = ppp.pre_payment_id)
and not exists (
select null
from per_all_assignments_f as3,
pay_assignment_actions ac3
where itpflg = 'N'
and ac3.payroll_action_id = pa2.payroll_action_id
and ac3.action_status not in ('C', 'S')
and as3.assignment_id = ac3.assignment_id
and pa2.effective_date between
as3.effective_start_date and as3.effective_end_date
and as3.person_id = as2.person_id)
order by act.assignment_id
for update of as1.assignment_id, pos.period_of_service_id;
select pay_assignment_actions_s.nextval
into lockingactid
from dual;
select /*+ ORDERED
index(pa2 PAY_PAYROLL_ACTIONS_N5)
index(as1 PER_ASSIGNMENTS_F_N4)
USE_NL(pos as1) */
act.assignment_action_id,
act.assignment_id,
act.tax_unit_id
from pay_payroll_actions pa1,
pay_payroll_actions pa2,
pay_action_classifications pcl,
per_periods_of_service pos,
per_all_assignments_f as1,
pay_assignment_actions act
where pa1.payroll_action_id = pactid
and pa2.effective_date between
pa1.start_date and pa1.effective_date
and pa2.consolidation_set_id = pa1.consolidation_set_id
and act.payroll_action_id = pa2.payroll_action_id
and act.action_status in ('C','S')
and pcl.classification_name = class
and pa2.action_type = pcl.action_type
and as1.assignment_id = act.assignment_id
and (as1.payroll_id = pa1.payroll_id or pa1.payroll_id is null)
and pa2.effective_date between
as1.effective_start_date and as1.effective_end_date
and pos.period_of_service_id = as1.period_of_service_id
and pos.person_id between stperson and endperson
and exists (
select ''
from pay_pre_payments ppp
where ppp.assignment_action_id = act.assignment_action_id
and ppp.organization_id is not null
and nvl(ppp.effective_date, pa2.effective_date)
<= pa1.effective_date
and not exists (
select null
from pay_contributing_payments
where contributing_pre_payment_id =
ppp.pre_payment_id
)
)
and not exists (
select /*+ ORDERED*/
null
from per_all_assignments_f as3,
pay_assignment_actions ac3
where itpflg = 'N'
and ac3.payroll_action_id = pa2.payroll_action_id
and ac3.action_status not in ( 'C', 'S')
and as3.assignment_id = ac3.assignment_id
and pa2.effective_date between
as3.effective_start_date and as3.effective_end_date
and as3.person_id = as1.person_id)
order by as1.person_id,as1.primary_flag desc ,as1.effective_start_date,act.assignment_id
for update of as1.assignment_id, pos.period_of_service_id;
select /*+ ORDERED
index(pa2 PAY_PAYROLL_ACTIONS_N5)
index(as1 PER_ASSIGNMENTS_F_N4)
USE_NL(pos as1) */
act.assignment_action_id,
act.assignment_id,
act.tax_unit_id
from pay_payroll_actions pa1,
pay_payroll_actions pa2,
pay_action_classifications pcl,
pay_population_ranges pop,
per_periods_of_service pos,
per_all_assignments_f as1,
pay_assignment_actions act
where pa1.payroll_action_id = pactid
and pa2.effective_date between
pa1.start_date and pa1.effective_date
and pa2.consolidation_set_id = pa1.consolidation_set_id
and act.payroll_action_id = pa2.payroll_action_id
and act.action_status in ('C','S')
and pcl.classification_name = class
and pa2.action_type = pcl.action_type
and as1.assignment_id = act.assignment_id
and (as1.payroll_id = pa1.payroll_id or pa1.payroll_id is null)
and pa2.effective_date between
as1.effective_start_date and as1.effective_end_date
and pos.period_of_service_id = as1.period_of_service_id
and pop.chunk_number = chunk
and pop.payroll_action_id = pactid
and pos.person_id = pop.person_id
and exists (
select ''
from pay_pre_payments ppp
where ppp.assignment_action_id = act.assignment_action_id
and ppp.organization_id is not null
and nvl(ppp.effective_date, pa2.effective_date)
<= pa1.effective_date
and not exists (
select null
from pay_contributing_payments
where contributing_pre_payment_id =
ppp.pre_payment_id
)
)
and not exists (
select /*+ ORDERED*/
null
from per_all_assignments_f as3,
pay_assignment_actions ac3
where itpflg = 'N'
and ac3.payroll_action_id = pa2.payroll_action_id
and ac3.action_status not in ( 'C', 'S')
and as3.assignment_id = ac3.assignment_id
and pa2.effective_date between
as3.effective_start_date and as3.effective_end_date
and as3.person_id = as1.person_id)
order by as1.person_id,as1.primary_flag desc ,as1.effective_start_date,act.assignment_id
for update of as1.assignment_id, pos.period_of_service_id;
/* process the insert of assignment actions */
/* logic prevents more than one action per assignment */
if(prev_assignid is null OR prev_assignid <> assignid) then
-- get a value for the action id that is locking.
select pay_assignment_actions_s.nextval
into lockingactid
from dual;
SELECT /*+ ORDERED
*/
pcp.assignment_action_id,
hou.organization_id,
ppp.pre_payment_id
from pay_payroll_actions pa1,
pay_payroll_actions pa2,
pay_action_classifications pcl,
pay_population_ranges pop,
hr_organization_units hou,
pay_pre_payments ppp,
pay_org_payment_methods_f opm,
pay_contributing_payments pcp
where pa1.payroll_action_id = pactid
and pa2.consolidation_set_id +0 = pa1.consolidation_set_id
and pa2.effective_date between
pa1.start_date and pa1.effective_date
and pa2.action_status = 'C'
and pcl.classification_name = class
and pa2.action_type = pcl.action_type
--
and pop.payroll_action_id = pactid
and pop.chunk_number = chunk
and hou.organization_id = pop.source_id
--
and (pa2.payroll_id = pa1.payroll_id or pa1.payroll_id is null)
and ppp.payroll_action_id = pa2.payroll_action_id
and ppp.organization_id = hou.organization_id
and opm.org_payment_method_id = ppp.org_payment_method_id
and pa1.effective_date between
opm.effective_start_date and opm.effective_end_date
and opm.payment_type_id +0 = ptype
and (opm.org_payment_method_id = pa1.org_payment_method_id
or pa1.org_payment_method_id is null)
and pcp.pre_payment_id = ppp.pre_payment_id
--
and not exists (
select /*+ ORDERED*/
null
from
pay_assignment_actions ac2
where ac2.pre_payment_id = ppp.pre_payment_id
)
order by hou.organization_id, ppp.pre_payment_id
for update of hou.organization_id;
select 1
into l_cp
from pay_payroll_actions pa1
where pa1.payroll_action_id = pactid
and exists
(select 1
from pay_payroll_actions pa2,
pay_contributing_payments pcp
where pa2.payroll_action_id = pcp.payroll_action_id
and pa2.action_type = 'PRU'
and pa2.business_group_id = pa1.business_group_id);
/* process the insert of assignment actions */
/* logic prevents more than one action per assignment */
if(prev_prepayid is null OR prev_prepayid <> l_prepayid) then
-- get a value for the action id that is locking.
select pay_assignment_actions_s.nextval
into lockingactid
from dual;
SELECT /*+ ORDERED
index(pa2 PAY_PAYROLL_ACTIONS_N5)
index(as1 PER_ASSIGNMENTS_F_N4)
index(as2 PER_ASSIGNMENTS_F_PK)
index(opm PAY_ORG_PAYMENT_METHODS_F_PK)
USE_NL(pop pos as1 as2) */
act.assignment_action_id,
act.assignment_id,
act.tax_unit_id,
ppp.pre_payment_id,
pa1.assignment_set_id,
as1.payroll_id
from pay_payroll_actions pa1,
pay_payroll_actions pa2,
pay_action_classifications pcl,
pay_population_ranges pop,
per_periods_of_service pos,
per_all_assignments_f as1,
pay_assignment_actions act,
pay_pre_payments ppp,
per_all_assignments_f as2,
pay_org_payment_methods_f opm
where pa1.payroll_action_id = pactid
and pa2.consolidation_set_id +0 = pa1.consolidation_set_id
and pa2.effective_date between
pa1.start_date and pa1.effective_date
and act.payroll_action_id = pa2.payroll_action_id
and act.action_status = 'C'
and pcl.classification_name = class
and pa2.action_type = pcl.action_type
and as1.assignment_id = act.assignment_id
and pa2.effective_date between
as1.effective_start_date and as1.effective_end_date
and as2.assignment_id = act.assignment_id
and pa1.effective_date between
as2.effective_start_date and as2.effective_end_date
and as2.payroll_id + 0 = as1.payroll_id + 0
and pos.period_of_service_id = as1.period_of_service_id
and pop.payroll_action_id = pactid
and pop.chunk_number = chunk
and pos.person_id = pop.person_id
and (as1.payroll_id = pa1.payroll_id or pa1.payroll_id is null)
and ppp.assignment_action_id = act.assignment_action_id
and opm.org_payment_method_id = ppp.org_payment_method_id
and ppp.organization_id is null
and pa1.effective_date between
opm.effective_start_date and opm.effective_end_date
and opm.payment_type_id +0 = ptype
and (opm.org_payment_method_id = pa1.org_payment_method_id
or pa1.org_payment_method_id is null)
and not exists (
select /*+ ORDERED*/
null
from pay_action_interlocks int,
pay_assignment_actions ac2
where int.locked_action_id = act.assignment_action_id
and ac2.assignment_action_id = int.locking_action_id
and ac2.pre_payment_id = ppp.pre_payment_id
and not exists (
select null
from pay_assignment_actions paa_void,
pay_action_interlocks pai_void,
pay_payroll_actions ppa_void
where pai_void.locked_action_id = ac2.assignment_action_id
and pai_void.locking_action_id = paa_void.assignment_action_id
and paa_void.payroll_action_id = ppa_void.payroll_action_id
and ppa_void.action_type = 'D')
)
and not exists (
select /*+ ORDERED*/
null
from per_all_assignments_f as3,
pay_assignment_actions ac3
where itpflg = 'N'
and ac3.payroll_action_id = pa2.payroll_action_id
and ac3.action_status not in ('C', 'S')
and as3.assignment_id = ac3.assignment_id
and pa2.effective_date between
as3.effective_start_date and as3.effective_end_date
and as3.person_id = as2.person_id)
order by act.assignment_id
for update of as1.assignment_id, pos.period_of_service_id;
SELECT /*+ ORDERED
index(pa2 PAY_PAYROLL_ACTIONS_N5)
index(as1 PER_ASSIGNMENTS_F_N4)
index(as2 PER_ASSIGNMENTS_F_PK)
index(opm PAY_ORG_PAYMENT_METHODS_F_PK)
USE_NL(pos as1 as2) */
act.assignment_action_id,
act.assignment_id,
act.tax_unit_id,
ppp.pre_payment_id,
pa1.assignment_set_id,
as1.payroll_id
from pay_payroll_actions pa1,
pay_payroll_actions pa2,
pay_action_classifications pcl,
per_periods_of_service pos,
per_all_assignments_f as1,
pay_assignment_actions act,
pay_pre_payments ppp,
per_all_assignments_f as2,
pay_org_payment_methods_f opm
where pa1.payroll_action_id = pactid
and pa2.consolidation_set_id +0 = pa1.consolidation_set_id
and pa2.effective_date between
pa1.start_date and pa1.effective_date
and act.payroll_action_id = pa2.payroll_action_id
and act.action_status = 'C'
and pcl.classification_name = class
and pa2.action_type = pcl.action_type
and as1.assignment_id = act.assignment_id
and pa2.effective_date between
as1.effective_start_date and as1.effective_end_date
and as2.assignment_id = act.assignment_id
and pa1.effective_date between
as2.effective_start_date and as2.effective_end_date
and as2.payroll_id + 0 = as1.payroll_id + 0
and pos.period_of_service_id = as1.period_of_service_id
and pos.person_id between stperson and endperson
and (as1.payroll_id = pa1.payroll_id or pa1.payroll_id is null)
and ppp.assignment_action_id = act.assignment_action_id
and opm.org_payment_method_id = ppp.org_payment_method_id
and ppp.organization_id is null
and pa1.effective_date between
opm.effective_start_date and opm.effective_end_date
and opm.payment_type_id +0 = ptype
and (opm.org_payment_method_id = pa1.org_payment_method_id
or pa1.org_payment_method_id is null)
and not exists (
select /*+ ORDERED*/
null
from pay_action_interlocks int,
pay_assignment_actions ac2
where int.locked_action_id = act.assignment_action_id
and ac2.assignment_action_id = int.locking_action_id
and ac2.pre_payment_id = ppp.pre_payment_id
and not exists (
select null
from pay_assignment_actions paa_void,
pay_action_interlocks pai_void,
pay_payroll_actions ppa_void
where pai_void.locked_action_id = ac2.assignment_action_id
and pai_void.locking_action_id = paa_void.assignment_action_id
and paa_void.payroll_action_id = ppa_void.payroll_action_id
and ppa_void.action_type = 'D')
)
and not exists (
select /*+ ORDERED*/
null
from per_all_assignments_f as3,
pay_assignment_actions ac3
where itpflg = 'N'
and ac3.payroll_action_id = pa2.payroll_action_id
and ac3.action_status not in ('C', 'S')
and as3.assignment_id = ac3.assignment_id
and pa2.effective_date between
as3.effective_start_date and as3.effective_end_date
and as3.person_id = as2.person_id)
order by act.assignment_id
for update of as1.assignment_id, pos.period_of_service_id;
SELECT
/*+ leading(PA1 PA2 ACT) use_nl(PA1 PA2 ACT) index(ACT PAY_ASSIGNMENT_ACTIONS_N50)*/ -- Bug 6522667
act.assignment_action_id,
act.assignment_id,
act.tax_unit_id,
ppp.pre_payment_id,
pa1.assignment_set_id,
as1.payroll_id
from pay_payroll_actions pa1,
pay_population_ranges pop,
per_periods_of_service pos,
per_all_assignments_f as1,
pay_assignment_actions act,
pay_payroll_actions pa2,
pay_action_classifications pcl,
pay_pre_payments ppp,
per_all_assignments_f as2,
pay_org_payment_methods_f opm
where pa1.payroll_action_id = pactid
and pa2.consolidation_set_id = pa1.consolidation_set_id /* moved +0, bug 6522667 */
and pa2.effective_date between
pa1.start_date and pa1.effective_date
and act.payroll_action_id = pa2.payroll_action_id
and act.action_status = 'C'
and pcl.classification_name = class
and pa2.action_type = pcl.action_type
and as1.assignment_id = act.assignment_id
and pa1.effective_date between
as1.effective_start_date and as1.effective_end_date
and as2.assignment_id = act.assignment_id
and pa2.effective_date between
as2.effective_start_date and as2.effective_end_date
and as2.payroll_id + 0 = as1.payroll_id + 0
and pos.period_of_service_id = as1.period_of_service_id
and pop.payroll_action_id = pactid
and pop.chunk_number = chunk
and pos.person_id = pop.person_id
and (as1.payroll_id = pa1.payroll_id or pa1.payroll_id is null)
and ppp.assignment_action_id = act.assignment_action_id
and opm.org_payment_method_id = ppp.org_payment_method_id
and pa1.effective_date between
opm.effective_start_date and opm.effective_end_date
and opm.payment_type_id +0 = ptype
and ppp.organization_id is null
and (opm.org_payment_method_id = pa1.org_payment_method_id
or pa1.org_payment_method_id is null)
and not exists (
select /* Bug 6522667, moved ORDERED hint */
null
from pay_action_interlocks int,
pay_assignment_actions ac2
where int.locked_action_id = act.assignment_action_id
and ac2.assignment_action_id = int.locking_action_id
and ac2.pre_payment_id = ppp.pre_payment_id
and not exists (
select null
from pay_assignment_actions paa_void,
pay_action_interlocks pai_void,
pay_payroll_actions ppa_void
where pai_void.locked_action_id = ac2.assignment_action_id
and pai_void.locking_action_id = paa_void.assignment_action_id
and paa_void.payroll_action_id = ppa_void.payroll_action_id
and ppa_void.action_type = 'D')
)
and not exists (
select /* Bug 6522667, moved ORDERED hint */
null
from per_all_assignments_f as3,
pay_assignment_actions ac3
where itpflg = 'N'
and ac3.payroll_action_id = pa2.payroll_action_id
and ac3.action_status not in ('C', 'S')
and as3.assignment_id = ac3.assignment_id
and pa2.effective_date between
as3.effective_start_date and as3.effective_end_date
and as3.person_id = as2.person_id)
order by act.assignment_id
for update of as1.assignment_id, pos.period_of_service_id;
SELECT 1
FROM hr_assignment_sets aset
WHERE aset.assignment_set_id = pasgsetid
and nvl(aset.payroll_id,ppayrollid) = ppayrollid
and (not exists
(select 1
from hr_assignment_set_amendments hasa
where hasa.assignment_set_id = aset.assignment_set_id
and hasa.include_or_exclude = 'I')
or exists
(select 1
from hr_assignment_set_amendments hasa
where hasa.assignment_set_id = aset.assignment_set_id
and hasa.assignment_id = pasgid
and hasa.include_or_exclude = 'I'))
and not exists
(select 1
from hr_assignment_set_amendments hasa
where hasa.assignment_set_id = aset.assignment_set_id
and hasa.assignment_id = pasgid
and hasa.include_or_exclude = 'E')
-- Ensure there exists a voided check for this payment.
and exists
(select 1
from pay_action_interlocks lck1,
pay_assignment_actions chk_paa,
pay_payroll_actions chk_ppa,
pay_action_interlocks lck2,
pay_assignment_actions vd_paa,
pay_payroll_actions vd_ppa
where lck1.locked_action_id = plockedid
and lck1.locking_action_id = chk_paa.assignment_action_id
and chk_paa.payroll_action_id = chk_ppa.payroll_action_id
and chk_ppa.action_type = 'H'
and lck2.locked_action_id = chk_paa.assignment_action_id
and lck2.locking_action_id = vd_paa.assignment_action_id
and vd_paa.payroll_action_id = vd_ppa.payroll_action_id
and vd_ppa.action_type = 'D');
select pay_assignment_actions_s.nextval
into lockingactid
from dual;
ending person_id and inserts a chunk of assignment actions
plus their associated interlock rows. This function for the
magnetic transfer action only.
NOTES
*/
procedure procmag
(
pactid in number, -- payroll_action_id.
stperson in number, -- starting person_id of range.
endperson in number, -- ending person_id of range.
chunk in number, -- current chunk_number.
rand_chunk in number, -- current chunk_number.
itpflg in varchar2, -- legislation type.
ptype in number, -- payment_type_id.
use_pop_person in number -- use population_ranges person_id column
) is
cursor magpopcur
(
pactid number,
chunk number,
itpflg varchar2,
ptype number
) is
select /*+ ORDERED
INDEX(pa2 PAY_PAYROLL_ACTIONS_N5)
INDEX(pos PER_PERIODS_OF_SERVICE_N3)
INDEX(as1 PER_ASSIGNMENTS_N4)
INDEX(as2 PER_ASSIGNMENTS_F_PK)
INDEX(act PAY_ASSIGNMENT_ACTIONS_N51)
index(opm PAY_ORG_PAYMENT_METHODS_F_PK)
USE_NL(pop pos ppp opm as1 act as2) */
act.assignment_action_id,
act.assignment_id,
act.tax_unit_id,
ppp.pre_payment_id
from pay_payroll_actions pa1,
pay_payroll_actions pa2,
pay_action_classifications pcl,
pay_population_ranges pop,
per_periods_of_service pos,
per_all_assignments_f as1,
pay_assignment_actions act,
per_all_assignments_f as2,
pay_pre_payments ppp,
pay_org_payment_methods_f opm
where pa1.payroll_action_id = pactid
and pa2.consolidation_set_id +0 = pa1.consolidation_set_id
and pa2.effective_date between
pa1.start_date and pa1.effective_date
and act.payroll_action_id = pa2.payroll_action_id
and act.action_status = 'C'
and pcl.classification_name = 'MAGTAPE'
and pa2.action_type = pcl.action_type
and as1.assignment_id = act.assignment_id
and pa2.effective_date between
as1.effective_start_date and as1.effective_end_date
and as2.assignment_id = act.assignment_id
and pa1.effective_date between
as2.effective_start_date and as2.effective_end_date
and as2.payroll_id + 0 = as1.payroll_id + 0
and pos.period_of_service_id = as1.period_of_service_id
and pop.payroll_action_id = pactid
and pop.chunk_number = chunk
and pos.person_id = pop.person_id
and (as1.payroll_id = pa1.payroll_id or pa1.payroll_id is null)
and ppp.assignment_action_id = act.assignment_action_id
and opm.org_payment_method_id = ppp.org_payment_method_id
and pa1.effective_date between
opm.effective_start_date and opm.effective_end_date
and opm.payment_type_id +0 = ptype
and (opm.org_payment_method_id = pa1.org_payment_method_id
or pa1.org_payment_method_id is null)
and not exists (
select null
from per_all_assignments_f as3,
pay_assignment_actions ac3
where itpflg = 'N'
and ac3.payroll_action_id = pa2.payroll_action_id
and ac3.action_status not in ('C', 'S')
and as3.assignment_id = ac3.assignment_id
and pa2.effective_date between
as3.effective_start_date and as3.effective_end_date
and as3.person_id = as2.person_id)
and not exists (
select /*+ ORDERED*/
null
from pay_action_interlocks int,
pay_assignment_actions ac2
where int.locked_action_id = act.assignment_action_id
and ac2.assignment_action_id = int.locking_action_id
and ac2.pre_payment_id = ppp.pre_payment_id
and not exists (
select null
from pay_assignment_actions paa_void,
pay_action_interlocks pai_void,
pay_payroll_actions ppa_void
where pai_void.locked_action_id = ac2.assignment_action_id
and pai_void.locking_action_id = paa_void.assignment_action_id
and paa_void.payroll_action_id = ppa_void.payroll_action_id
and ppa_void.action_type = 'D')
)
order by act.assignment_id
for update of as1.assignment_id, pos.period_of_service_id;
select /*+ ORDERED
INDEX(pa2 PAY_PAYROLL_ACTIONS_N5)
INDEX(pos PER_PERIODS_OF_SERVICE_N3)
INDEX(as1 PER_ASSIGNMENTS_N4)
INDEX(as2 PER_ASSIGNMENTS_F_PK)
INDEX(act PAY_ASSIGNMENT_ACTIONS_N51)
index(opm PAY_ORG_PAYMENT_METHODS_F_PK)
USE_NL(pos ppp opm as1 act as2) */
act.assignment_action_id,
act.assignment_id,
act.tax_unit_id,
ppp.pre_payment_id
from pay_payroll_actions pa1,
pay_payroll_actions pa2,
pay_action_classifications pcl,
per_periods_of_service pos,
per_all_assignments_f as1,
pay_assignment_actions act,
per_all_assignments_f as2,
pay_pre_payments ppp,
pay_org_payment_methods_f opm
where pa1.payroll_action_id = pactid
and pa2.consolidation_set_id +0 = pa1.consolidation_set_id
and pa2.effective_date between
pa1.start_date and pa1.effective_date
and act.payroll_action_id = pa2.payroll_action_id
and act.action_status = 'C'
and pcl.classification_name = 'MAGTAPE'
and pa2.action_type = pcl.action_type
and as1.assignment_id = act.assignment_id
and pa2.effective_date between
as1.effective_start_date and as1.effective_end_date
and as2.assignment_id = act.assignment_id
and pa1.effective_date between
as2.effective_start_date and as2.effective_end_date
and as2.payroll_id + 0 = as1.payroll_id + 0
and pos.period_of_service_id = as1.period_of_service_id
and pos.person_id between stperson and endperson
and (as1.payroll_id = pa1.payroll_id or pa1.payroll_id is null)
and ppp.assignment_action_id = act.assignment_action_id
and opm.org_payment_method_id = ppp.org_payment_method_id
and pa1.effective_date between
opm.effective_start_date and opm.effective_end_date
and opm.payment_type_id +0 = ptype
and (opm.org_payment_method_id = pa1.org_payment_method_id
or pa1.org_payment_method_id is null)
and not exists (
select null
from per_all_assignments_f as3,
pay_assignment_actions ac3
where itpflg = 'N'
and ac3.payroll_action_id = pa2.payroll_action_id
and ac3.action_status not in ('C', 'S')
and as3.assignment_id = ac3.assignment_id
and pa2.effective_date between
as3.effective_start_date and as3.effective_end_date
and as3.person_id = as2.person_id)
and not exists (
select /*+ ORDERED*/
null
from pay_action_interlocks int,
pay_assignment_actions ac2
where int.locked_action_id = act.assignment_action_id
and ac2.assignment_action_id = int.locking_action_id
and ac2.pre_payment_id = ppp.pre_payment_id
and not exists (
select null
from pay_assignment_actions paa_void,
pay_action_interlocks pai_void,
pay_payroll_actions ppa_void
where pai_void.locked_action_id = ac2.assignment_action_id
and pai_void.locking_action_id = paa_void.assignment_action_id
and paa_void.payroll_action_id = ppa_void.payroll_action_id
and ppa_void.action_type = 'D')
)
order by act.assignment_id
for update of as1.assignment_id, pos.period_of_service_id;
select /*+ ORDERED
INDEX(pa2 PAY_PAYROLL_ACTIONS_PK)
INDEX(pos PER_PERIODS_OF_SERVICE_N3)
INDEX(as1 PER_ASSIGNMENTS_N4)
INDEX(as2 PER_ASSIGNMENTS_F_PK)
INDEX(act PAY_ASSIGNMENT_ACTIONS_N51)
index(opm PAY_ORG_PAYMENT_METHODS_F_PK)
USE_NL(pos pop ppp opm as1 act as2) */
act.assignment_action_id,
act.assignment_id,
act.tax_unit_id,
ppp.pre_payment_id
from pay_payroll_actions pa1,
pay_population_ranges pop,
per_periods_of_service pos,
per_all_assignments_f as1,
pay_assignment_actions act,
pay_payroll_actions pa2,
pay_action_classifications pcl,
per_all_assignments_f as2,
pay_pre_payments ppp,
pay_org_payment_methods_f opm
where pa1.payroll_action_id = pactid
and pa2.consolidation_set_id +0 = pa1.consolidation_set_id
and pa2.effective_date between
pa1.start_date and pa1.effective_date
and act.payroll_action_id = pa2.payroll_action_id
and act.action_status = 'C'
and pcl.classification_name = 'MAGTAPE'
and pa2.action_type = pcl.action_type
and as1.assignment_id = act.assignment_id
and pa1.effective_date between
as1.effective_start_date and as1.effective_end_date
and as2.assignment_id = act.assignment_id
and pa2.effective_date between
as2.effective_start_date and as2.effective_end_date
and as2.payroll_id + 0 = as1.payroll_id + 0
and pos.period_of_service_id = as1.period_of_service_id
and pop.payroll_action_id = pactid
and pop.chunk_number = chunk
and pos.person_id = pop.person_id
and (as1.payroll_id = pa1.payroll_id or pa1.payroll_id is null)
and ppp.assignment_action_id = act.assignment_action_id
and opm.org_payment_method_id = ppp.org_payment_method_id
and pa1.effective_date between
opm.effective_start_date and opm.effective_end_date
and opm.payment_type_id +0 = ptype
and (opm.org_payment_method_id = pa1.org_payment_method_id
or pa1.org_payment_method_id is null)
and not exists (
select null
from per_all_assignments_f as3,
pay_assignment_actions ac3
where itpflg = 'N'
and ac3.payroll_action_id = pa2.payroll_action_id
and ac3.action_status not in ('C', 'S')
and as3.assignment_id = ac3.assignment_id
and pa2.effective_date between
as3.effective_start_date and as3.effective_end_date
and as3.person_id = as2.person_id)
and not exists (
select /*+ ORDERED*/
null
from pay_action_interlocks int,
pay_assignment_actions ac2
where int.locked_action_id = act.assignment_action_id
and ac2.assignment_action_id = int.locking_action_id
and ac2.pre_payment_id = ppp.pre_payment_id
and not exists (
select null
from pay_assignment_actions paa_void,
pay_action_interlocks pai_void,
pay_payroll_actions ppa_void
where pai_void.locked_action_id = ac2.assignment_action_id
and pai_void.locking_action_id = paa_void.assignment_action_id
and paa_void.payroll_action_id = ppa_void.payroll_action_id
and ppa_void.action_type = 'D')
)
order by act.assignment_id
for update of as1.assignment_id, pos.period_of_service_id;
select pay_assignment_actions_s.nextval
into lockingactid
from dual;
proc_prepay - insert actions for pre-payment action type.
DESCRIPTION
For the range defined by the starting and ending person_id,
inserts a chunk of assignment actions and associated interlocks.
NOTES
*/
procedure proc_prepay
(
pactid in number,
stperson in number,
endperson in number,
chunk in number,
rand_chunk in number,
class in varchar2,
itpflg in varchar2,
mult_asg_flag in varchar2 default 'N',
use_pop_person in number
) is
--
cursor prepaypopcur
(
pactid number,
chunk number,
class varchar2,
itpflg varchar2
) is
select /*+ ORDERED
index(pa2 PAY_PAYROLL_ACTIONS_N5)
index(as1 PER_ASSIGNMENTS_F_N4)
index(as2 PER_ASSIGNMENTS_F_PK)
USE_NL(pop pos as1) */
act.assignment_action_id,
act.assignment_id,
act.tax_unit_id,
as1.person_id,
as1.effective_start_date,
as1.primary_flag
from pay_payroll_actions pa1,
pay_payroll_actions pa2,
pay_action_classifications pcl,
pay_population_ranges pop,
per_periods_of_service pos,
per_all_assignments_f as1,
pay_assignment_actions act,
per_all_assignments_f as2
where pa1.payroll_action_id = pactid
and pa2.payroll_id = pa1.payroll_id
and pa2.effective_date between
pa1.start_date and pa1.effective_date
and act.payroll_action_id = pa2.payroll_action_id
and act.action_status in ('C','S')
and pcl.classification_name = class
and pa2.consolidation_set_id = pa1.consolidation_set_id
and pa2.action_type = pcl.action_type
and nvl(pa2.future_process_mode, 'Y') = 'Y'
and as1.assignment_id = act.assignment_id
and pa2.effective_date between
as1.effective_start_date and as1.effective_end_date
and as2.assignment_id = act.assignment_id
and pa1.effective_date between
as2.effective_start_date and as2.effective_end_date
and as2.payroll_id = as1.payroll_id
and pos.period_of_service_id = as1.period_of_service_id
and pop.payroll_action_id = pactid
and pop.chunk_number = chunk
and pos.person_id = pop.person_id
and (as1.payroll_id = pa1.payroll_id or pa1.payroll_id is null)
and not exists (
select null
from pay_assignment_actions ac2,
pay_payroll_actions pa3,
pay_action_interlocks int
where int.locked_action_id = act.assignment_action_id
and ac2.assignment_action_id = int.locking_action_id
and pa3.payroll_action_id = ac2.payroll_action_id
and pa3.action_type in ('P', 'U'))
and not exists (
select /*+ ORDERED*/
null
from per_all_assignments_f as3,
pay_assignment_actions ac3
where itpflg = 'N'
and ac3.payroll_action_id = pa2.payroll_action_id
and ac3.action_status not in ( 'C', 'S')
and as3.assignment_id = ac3.assignment_id
and pa2.effective_date between
as3.effective_start_date and as3.effective_end_date
and as3.person_id = as2.person_id)
order by as1.person_id,as1.primary_flag desc ,as1.effective_start_date,act.assignment_id
for update of as1.assignment_id, pos.period_of_service_id;
select /*+ ORDERED
index(pa2 PAY_PAYROLL_ACTIONS_N5)
index(as1 PER_ASSIGNMENTS_F_N4)
index(as2 PER_ASSIGNMENTS_F_PK)
USE_NL(pos as1) */
act.assignment_action_id,
act.assignment_id,
act.tax_unit_id,
as1.person_id,
as1.effective_start_date,
as1.primary_flag
from pay_payroll_actions pa1,
pay_payroll_actions pa2,
pay_action_classifications pcl,
per_periods_of_service pos,
per_all_assignments_f as1,
pay_assignment_actions act,
per_all_assignments_f as2
where pa1.payroll_action_id = pactid
and pa2.payroll_id = pa1.payroll_id
and pa2.effective_date between
pa1.start_date and pa1.effective_date
and act.payroll_action_id = pa2.payroll_action_id
and act.action_status in ('C','S')
and pcl.classification_name = class
and pa2.consolidation_set_id = pa1.consolidation_set_id
and pa2.action_type = pcl.action_type
and nvl(pa2.future_process_mode, 'Y') = 'Y'
and as1.assignment_id = act.assignment_id
and pa2.effective_date between
as1.effective_start_date and as1.effective_end_date
and as2.assignment_id = act.assignment_id
and pa1.effective_date between
as2.effective_start_date and as2.effective_end_date
and as2.payroll_id = as1.payroll_id
and pos.period_of_service_id = as1.period_of_service_id
and pos.person_id between stperson and endperson
and (as1.payroll_id = pa1.payroll_id or pa1.payroll_id is null)
and not exists (
select null
from pay_assignment_actions ac2,
pay_payroll_actions pa3,
pay_action_interlocks int
where int.locked_action_id = act.assignment_action_id
and ac2.assignment_action_id = int.locking_action_id
and pa3.payroll_action_id = ac2.payroll_action_id
and pa3.action_type in ('P', 'U'))
and not exists (
select /*+ ORDERED*/
null
from per_all_assignments_f as3,
pay_assignment_actions ac3
where itpflg = 'N'
and ac3.payroll_action_id = pa2.payroll_action_id
and ac3.action_status not in ( 'C', 'S')
and as3.assignment_id = ac3.assignment_id
and pa2.effective_date between
as3.effective_start_date and as3.effective_end_date
and as3.person_id = as2.person_id)
order by as1.person_id,as1.primary_flag desc ,as1.effective_start_date,act.assignment_id
for update of as1.assignment_id, pos.period_of_service_id;
select /*+ ORDERED
index(pa2 PAY_PAYROLL_ACTIONS_PK)
index(pos PER_PERIODS_OF_SERVICE_N3)
index(act PAY_ASSIGNMENT_ACTIONS_N51)
index(as1 PER_ASSIGNMENTS_F_N4)
index(as2 PER_ASSIGNMENTS_F_PK)
USE_NL(pos pop act as1 as2 pa2) */
act.assignment_action_id,
act.assignment_id,
act.tax_unit_id,
as1.person_id,
as1.effective_start_date,
as1.primary_flag
from pay_payroll_actions pa1,
pay_population_ranges pop,
per_periods_of_service pos,
per_all_assignments_f as1,
pay_assignment_actions act,
pay_payroll_actions pa2,
pay_action_classifications pcl,
per_all_assignments_f as2
where pa1.payroll_action_id = pactid
and pa2.payroll_id = pa1.payroll_id
and pa2.effective_date between
pa1.start_date and pa1.effective_date
and act.payroll_action_id = pa2.payroll_action_id
and act.action_status in ('C','S')
and pcl.classification_name = class
and pa2.consolidation_set_id = pa1.consolidation_set_id
and pa2.action_type = pcl.action_type
and nvl(pa2.future_process_mode, 'Y') = 'Y'
and as1.assignment_id = act.assignment_id
and pa1.effective_date between
as1.effective_start_date and as1.effective_end_date
and as2.assignment_id = act.assignment_id
and pa2.effective_date between
as2.effective_start_date and as2.effective_end_date
and as2.payroll_id = as1.payroll_id
and pos.period_of_service_id = as1.period_of_service_id
and pop.payroll_action_id = pactid
and pop.chunk_number = chunk
and pos.person_id = pop.person_id
and (as1.payroll_id = pa1.payroll_id or pa1.payroll_id is null)
and not exists (
select null
from pay_assignment_actions ac2,
pay_payroll_actions pa3,
pay_action_interlocks int
where int.locked_action_id = act.assignment_action_id
and ac2.assignment_action_id = int.locking_action_id
and pa3.payroll_action_id = ac2.payroll_action_id
and pa3.action_type in ('P', 'U'))
and not exists (
select /*+ ORDERED*/
null
from per_all_assignments_f as3,
pay_assignment_actions ac3
where itpflg = 'N'
and ac3.payroll_action_id = pa2.payroll_action_id
and ac3.action_status not in ( 'C', 'S')
and as3.assignment_id = ac3.assignment_id
and pa2.effective_date between
as3.effective_start_date and as3.effective_end_date
and as3.person_id = as2.person_id)
order by as1.person_id,as1.primary_flag desc ,as1.effective_start_date,act.assignment_id
for update of as1.assignment_id, pos.period_of_service_id;
select pay_assignment_actions_s.nextval
into lockingactid
from dual;
/* process the insert of assignment actions */
/* logic prevents more than one action per assignment */
if(prev_assignid is null OR prev_assignid <> assignid) then
-- get a value for the action id that is locking.
select pay_assignment_actions_s.nextval
into lockingactid
from dual;
proc_costing - insert actions for non Costing action type.
DESCRIPTION
For the range defined by the starting and ending person_id,
inserts a chunk of assignment actions and associated interlocks.
NOTES
*/
procedure proc_costing
(
pactid in number,
stperson in number,
endperson in number,
chunk in number,
rand_chunk in number,
class in varchar2,
itpflg in varchar2,
use_pop_person in number
) is
--
cursor costingpopcur
(
pactid number,
chunk number,
class varchar2,
itpflg varchar2
) is
select /*+ ORDERED
index(pa2 PAY_PAYROLL_ACTIONS_N5)
index(as1 PER_ASSIGNMENTS_F_N4)
index(as2 PER_ASSIGNMENTS_F_PK)
USE_NL(pos pop as1) */
act.assignment_action_id,
act.assignment_id,
act.tax_unit_id
from pay_payroll_actions pa1,
pay_payroll_actions pa2,
pay_action_classifications pcl,
pay_population_ranges pop,
per_periods_of_service pos,
per_all_assignments_f as1,
pay_assignment_actions act,
per_all_assignments_f as2
where pa1.payroll_action_id = pactid
and pa2.consolidation_set_id = pa1.consolidation_set_id
and pa2.effective_date between
pa1.start_date and pa1.effective_date
and act.payroll_action_id = pa2.payroll_action_id
and act.action_status in ('C','S')
and pcl.classification_name = class
and pa2.action_type = pcl.action_type
and as1.assignment_id = act.assignment_id
and pa2.effective_date between
as1.effective_start_date and as1.effective_end_date
and as2.assignment_id = act.assignment_id
and pa1.effective_date between
as2.effective_start_date and as2.effective_end_date
and pop.payroll_action_id = pactid
and pop.chunk_number = chunk
and pos.person_id = pop.person_id
and pos.period_of_service_id = as1.period_of_service_id
and (as1.payroll_id = pa1.payroll_id or pa1.payroll_id is null)
and not exists (
select null
from pay_assignment_actions ac2,
pay_payroll_actions pa3,
pay_action_interlocks int
where int.locked_action_id = act.assignment_action_id
and ac2.assignment_action_id = int.locking_action_id
and pa3.payroll_action_id = ac2.payroll_action_id
and pa3.action_type in ('C', 'S'))
and not exists (
select /*+ ORDERED*/
null
from per_all_assignments_f as3,
pay_assignment_actions ac3
where itpflg = 'N'
and ac3.payroll_action_id = pa2.payroll_action_id
and ac3.action_status not in ('C','S')
and as3.assignment_id = ac3.assignment_id
and pa2.effective_date between
as3.effective_start_date and as3.effective_end_date
and as3.person_id = as2.person_id)
order by act.assignment_id
for update of as1.assignment_id, pos.period_of_service_id;
select /*+ ORDERED
index(pa2 PAY_PAYROLL_ACTIONS_N5)
index(as1 PER_ASSIGNMENTS_F_N4)
index(as2 PER_ASSIGNMENTS_F_PK)
USE_NL(pos as1) */
act.assignment_action_id,
act.assignment_id,
act.tax_unit_id
from pay_payroll_actions pa1,
pay_payroll_actions pa2,
pay_action_classifications pcl,
per_periods_of_service pos,
per_all_assignments_f as1,
pay_assignment_actions act,
per_all_assignments_f as2
where pa1.payroll_action_id = pactid
and pa2.consolidation_set_id = pa1.consolidation_set_id
and pa2.effective_date between
pa1.start_date and pa1.effective_date
and act.payroll_action_id = pa2.payroll_action_id
and act.action_status in ('C','S')
and pcl.classification_name = class
and pa2.action_type = pcl.action_type
and as1.assignment_id = act.assignment_id
and pa2.effective_date between
as1.effective_start_date and as1.effective_end_date
and as2.assignment_id = act.assignment_id
and pa1.effective_date between
as2.effective_start_date and as2.effective_end_date
and pos.period_of_service_id = as1.period_of_service_id
and pos.person_id between stperson and endperson
and (as1.payroll_id = pa1.payroll_id or pa1.payroll_id is null)
and not exists (
select null
from pay_assignment_actions ac2,
pay_payroll_actions pa3,
pay_action_interlocks int
where int.locked_action_id = act.assignment_action_id
and ac2.assignment_action_id = int.locking_action_id
and pa3.payroll_action_id = ac2.payroll_action_id
and pa3.action_type in ('C', 'S'))
and not exists (
select /*+ ORDERED*/
null
from per_all_assignments_f as3,
pay_assignment_actions ac3
where itpflg = 'N'
and ac3.payroll_action_id = pa2.payroll_action_id
and ac3.action_status not in ('C','S')
and as3.assignment_id = ac3.assignment_id
and pa2.effective_date between
as3.effective_start_date and as3.effective_end_date
and as3.person_id = as2.person_id)
order by act.assignment_id
for update of as1.assignment_id, pos.period_of_service_id;
select /*+ ORDERED
index(pa2 PAY_PAYROLL_ACTIONS_PK)
index(pos PER_PERIODS_OF_SERVICE_N3)
index(as1 PER_ASSIGNMENTS_F_N4)
index(act PAY_ASSIGNMENT_ACTIONS_N51)
index(as2 PER_ASSIGNMENTS_F_PK)
USE_NL(pos pop act pa2 as2 as1) */
act.assignment_action_id,
act.assignment_id,
act.tax_unit_id
from pay_payroll_actions pa1,
pay_population_ranges pop,
per_periods_of_service pos,
per_all_assignments_f as1,
pay_assignment_actions act,
pay_payroll_actions pa2,
pay_action_classifications pcl,
per_all_assignments_f as2
where pa1.payroll_action_id = pactid
and pa2.consolidation_set_id = pa1.consolidation_set_id
and pa2.effective_date between
pa1.start_date and pa1.effective_date
and act.payroll_action_id = pa2.payroll_action_id
and act.action_status in ('C','S')
and pcl.classification_name = class
and pa2.action_type = pcl.action_type
and as1.assignment_id = act.assignment_id
and pa1.effective_date between
as1.effective_start_date and as1.effective_end_date
and as2.assignment_id = act.assignment_id
and pa2.effective_date between
as2.effective_start_date and as2.effective_end_date
and pos.period_of_service_id = as1.period_of_service_id
and pop.payroll_action_id = pactid
and pop.chunk_number = chunk
and pos.person_id = pop.person_id
and (as2.payroll_id = pa1.payroll_id or pa1.payroll_id is null)
and not exists (
select null
from pay_assignment_actions ac2,
pay_payroll_actions pa3,
pay_action_interlocks int
where int.locked_action_id = act.assignment_action_id
and ac2.assignment_action_id = int.locking_action_id
and pa3.payroll_action_id = ac2.payroll_action_id
and pa3.action_type in ('C', 'S'))
and not exists (
select /*+ ORDERED*/
null
from per_all_assignments_f as3,
pay_assignment_actions ac3
where itpflg = 'N'
and ac3.payroll_action_id = pa2.payroll_action_id
and ac3.action_status not in ('C','S')
and as3.assignment_id = ac3.assignment_id
and pa2.effective_date between
as3.effective_start_date and as3.effective_end_date
and as3.person_id = as2.person_id)
order by act.assignment_id
for update of as1.assignment_id, pos.period_of_service_id;
/* process the insert of assignment actions */
/* logic prevents more than one action per assignment */
if(prev_assignid is null OR prev_assignid <> assignid) then
-- get a value for the action id that is locking.
select pay_assignment_actions_s.nextval
into lockingactid
from dual;
proc_paymcosting - insert actions for Payment Costing action type.
DESCRIPTION
For the range defined by the starting and ending person_id,
inserts a chunk of assignment actions and associated interlocks.
NOTES
*/
procedure proc_paymcosting
(
pactid in number,
stperson in number,
endperson in number,
chunk in number,
rand_chunk in number,
class in varchar2,
itpflg in varchar2,
use_pop_person in number
) is
--
cursor pmcostingpopcur
(
pactid number,
chunk number,
class varchar2,
itpflg varchar2
) is
select /*+ ORDERED
index(pa2 PAY_PAYROLL_ACTIONS_N5)
index(as1 PER_ASSIGNMENTS_F_N4)
index(as2 PER_ASSIGNMENTS_F_PK)
USE_NL(pos pop as1) */
act.assignment_action_id,
act.assignment_id,
act.tax_unit_id,
act.payroll_action_id
from pay_payroll_actions pa1,
pay_payroll_actions pa2,
pay_action_classifications pcl,
pay_population_ranges pop,
per_periods_of_service pos,
per_all_assignments_f as1,
pay_assignment_actions act,
per_all_assignments_f as2
where pa1.payroll_action_id = pactid
and pa2.consolidation_set_id = pa1.consolidation_set_id
and pa2.effective_date between
pa1.start_date and pa1.effective_date
and act.payroll_action_id = pa2.payroll_action_id
and act.action_status in ('C','S')
and pcl.classification_name = class
and pa2.action_type = pcl.action_type
and as1.assignment_id = act.assignment_id
and pa2.effective_date between
as1.effective_start_date and as1.effective_end_date
and as2.assignment_id = act.assignment_id
and pa1.effective_date between
as2.effective_start_date and as2.effective_end_date
and pop.payroll_action_id = pactid
and pop.chunk_number = chunk
and pos.person_id = pop.person_id
and pos.period_of_service_id = as1.period_of_service_id
and (as1.payroll_id = pa1.payroll_id or pa1.payroll_id is null)
and not exists (
select null
from pay_assignment_actions ac2,
pay_payroll_actions pa3,
pay_action_interlocks int
where int.locked_action_id = act.assignment_action_id
and ac2.assignment_action_id = int.locking_action_id
and pa3.payroll_action_id = ac2.payroll_action_id
and pa3.action_type = 'CP')
and not exists (
select /*+ ORDERED*/
null
from per_all_assignments_f as3,
pay_assignment_actions ac3
where itpflg = 'N'
and ac3.payroll_action_id = pa2.payroll_action_id
and ac3.action_status not in ('C','S')
and as3.assignment_id = ac3.assignment_id
and pa2.effective_date between
as3.effective_start_date and as3.effective_end_date
and as3.person_id = as2.person_id)
and ((pa2.action_type in ('P', 'U')
and pa1.batch_process_mode in ('UNCLEARED', 'ALL')
and exists (select 1
from pay_pre_payments ppp,
pay_org_payment_methods_f pom
where ppp.assignment_action_id = act.assignment_action_id
and pom.org_payment_method_id = ppp.org_payment_method_id
and pom.cost_payment = 'Y'
and pa2.effective_date between
pom.effective_start_date and pom.effective_end_date))
or (pa2.action_type in ('H', 'M')
and pa1.batch_process_mode in ('CLEARED', 'ALL')
and exists (select 1
from pay_pre_payments ppp,
pay_org_payment_methods_f pom,
pay_ce_reconciled_payments crp
where ppp.pre_payment_id = act.pre_payment_id
and pom.org_payment_method_id = ppp.org_payment_method_id
and pom.cost_cleared_payment = 'Y'
and crp.assignment_action_id = act.assignment_action_id
and pa2.effective_date between
pom.effective_start_date and pom.effective_end_date))
or (pa2.action_type = 'E'
and pa1.batch_process_mode in ('CLEARED', 'ALL')
and exists (select 1
from pay_pre_payments ppp,
pay_org_payment_methods_f pom,
pay_ce_reconciled_payments crp
where ppp.pre_payment_id = act.pre_payment_id
and pom.org_payment_method_id = ppp.org_payment_method_id
and pom.cost_cleared_payment = 'Y'
and pom.exclude_manual_payment = 'N'
and crp.assignment_action_id = act.assignment_action_id
and pa2.effective_date between
pom.effective_start_date and pom.effective_end_date))
or (pa2.action_type = 'D'
and ((pa1.batch_process_mode in ('CLEARED', 'ALL')
and exists (select 1
from pay_action_interlocks int,
pay_assignment_actions chq,
pay_payroll_actions pcq,
pay_pre_payments ppp,
pay_org_payment_methods_f pom,
pay_ce_reconciled_payments crp
where int.locking_action_id = act.assignment_action_id
and chq.assignment_action_id = int.locked_action_id
and pcq.payroll_action_id = chq.payroll_action_id
and pcq.action_type = 'H'
and ppp.pre_payment_id = chq.pre_payment_id
and pom.org_payment_method_id = ppp.org_payment_method_id
and pom.cost_cleared_payment = 'Y'
and crp.assignment_action_id = act.assignment_action_id
and pa2.effective_date between
pom.effective_start_date and pom.effective_end_date))
or (pa1.batch_process_mode in ('UNCLEARED', 'ALL')
and exists (select 1
from pay_action_interlocks int,
pay_assignment_actions chq,
pay_payroll_actions pcq,
pay_pre_payments ppp,
pay_org_payment_methods_f pom
where int.locking_action_id = act.assignment_action_id
and chq.assignment_action_id = int.locked_action_id
and pcq.payroll_action_id = chq.payroll_action_id
and pcq.action_type = 'H'
and ppp.pre_payment_id = chq.pre_payment_id
and pom.org_payment_method_id = ppp.org_payment_method_id
and pom.cost_payment = 'Y'
and pom.cost_cleared_void_payment = 'N'
and pa2.effective_date between
pom.effective_start_date and pom.effective_end_date)))))
order by act.assignment_id
for update of as1.assignment_id, pos.period_of_service_id;
select /*+ ORDERED
index(pa2 PAY_PAYROLL_ACTIONS_N5)
index(as1 PER_ASSIGNMENTS_F_N4)
index(as2 PER_ASSIGNMENTS_F_PK)
USE_NL(pos as1) */
act.assignment_action_id,
act.assignment_id,
act.tax_unit_id,
act.payroll_action_id
from pay_payroll_actions pa1,
pay_payroll_actions pa2,
pay_action_classifications pcl,
per_periods_of_service pos,
per_all_assignments_f as1,
pay_assignment_actions act,
per_all_assignments_f as2
where pa1.payroll_action_id = pactid
and pa2.consolidation_set_id = pa1.consolidation_set_id
and pa2.effective_date between
pa1.start_date and pa1.effective_date
and act.payroll_action_id = pa2.payroll_action_id
and act.action_status in ('C','S')
and pcl.classification_name = class
and pa2.action_type = pcl.action_type
and as1.assignment_id = act.assignment_id
and pa2.effective_date between
as1.effective_start_date and as1.effective_end_date
and as2.assignment_id = act.assignment_id
and pa1.effective_date between
as2.effective_start_date and as2.effective_end_date
and pos.period_of_service_id = as1.period_of_service_id
and pos.person_id between stperson and endperson
and (as1.payroll_id = pa1.payroll_id or pa1.payroll_id is null)
and not exists (
select null
from pay_assignment_actions ac2,
pay_payroll_actions pa3,
pay_action_interlocks int
where int.locked_action_id = act.assignment_action_id
and ac2.assignment_action_id = int.locking_action_id
and pa3.payroll_action_id = ac2.payroll_action_id
and pa3.action_type = 'CP')
and not exists (
select /*+ ORDERED*/
null
from per_all_assignments_f as3,
pay_assignment_actions ac3
where itpflg = 'N'
and ac3.payroll_action_id = pa2.payroll_action_id
and ac3.action_status not in ('C','S')
and as3.assignment_id = ac3.assignment_id
and pa2.effective_date between
as3.effective_start_date and as3.effective_end_date
and as3.person_id = as2.person_id)
and ((pa2.action_type in ('P', 'U')
and pa1.batch_process_mode in ('UNCLEARED', 'ALL')
and exists (select 1
from pay_pre_payments ppp,
pay_org_payment_methods_f pom
where ppp.assignment_action_id = act.assignment_action_id
and pom.org_payment_method_id = ppp.org_payment_method_id
and pom.cost_payment = 'Y'
and pa2.effective_date between
pom.effective_start_date and pom.effective_end_date))
or (pa2.action_type in ('H', 'M')
and pa1.batch_process_mode in ('CLEARED', 'ALL')
and exists (select 1
from pay_pre_payments ppp,
pay_org_payment_methods_f pom,
pay_ce_reconciled_payments crp
where ppp.pre_payment_id = act.pre_payment_id
and pom.org_payment_method_id = ppp.org_payment_method_id
and pom.cost_cleared_payment = 'Y'
and crp.assignment_action_id = act.assignment_action_id
and pa2.effective_date between
pom.effective_start_date and pom.effective_end_date))
or (pa2.action_type = 'E'
and pa1.batch_process_mode in ('CLEARED', 'ALL')
and exists (select 1
from pay_pre_payments ppp,
pay_org_payment_methods_f pom,
pay_ce_reconciled_payments crp
where ppp.pre_payment_id = act.pre_payment_id
and pom.org_payment_method_id = ppp.org_payment_method_id
and pom.cost_cleared_payment = 'Y'
and pom.exclude_manual_payment = 'N'
and crp.assignment_action_id = act.assignment_action_id
and pa2.effective_date between
pom.effective_start_date and pom.effective_end_date))
or (pa2.action_type = 'D'
and ((pa1.batch_process_mode in ('CLEARED', 'ALL')
and exists (select 1
from pay_action_interlocks int,
pay_assignment_actions chq,
pay_payroll_actions pcq,
pay_pre_payments ppp,
pay_org_payment_methods_f pom,
pay_ce_reconciled_payments crp
where int.locking_action_id = act.assignment_action_id
and chq.assignment_action_id = int.locked_action_id
and pcq.payroll_action_id = chq.payroll_action_id
and pcq.action_type = 'H'
and ppp.pre_payment_id = chq.pre_payment_id
and pom.org_payment_method_id = ppp.org_payment_method_id
and pom.cost_cleared_payment = 'Y'
and crp.assignment_action_id = act.assignment_action_id
and pa2.effective_date between
pom.effective_start_date and pom.effective_end_date))
or (pa1.batch_process_mode in ('UNCLEARED', 'ALL')
and exists (select 1
from pay_action_interlocks int,
pay_assignment_actions chq,
pay_payroll_actions pcq,
pay_pre_payments ppp,
pay_org_payment_methods_f pom
where int.locking_action_id = act.assignment_action_id
and chq.assignment_action_id = int.locked_action_id
and pcq.payroll_action_id = chq.payroll_action_id
and pcq.action_type = 'H'
and ppp.pre_payment_id = chq.pre_payment_id
and pom.org_payment_method_id = ppp.org_payment_method_id
and pom.cost_payment = 'Y'
and pom.cost_cleared_void_payment = 'N'
and pa2.effective_date between
pom.effective_start_date and pom.effective_end_date)))))
order by act.assignment_id
for update of as1.assignment_id, pos.period_of_service_id;
select /*+ ORDERED
index(pa2 PAY_PAYROLL_ACTIONS_PK)
index(pos PER_PERIODS_OF_SERVICE_N3)
index(as1 PER_ASSIGNMENTS_F_N4)
index(act PAY_ASSIGNMENT_ACTIONS_N51)
index(as2 PER_ASSIGNMENTS_F_PK)
USE_NL(pos pop act pa2 as2 as1) */
act.assignment_action_id,
act.assignment_id,
act.tax_unit_id,
act.payroll_action_id
from pay_payroll_actions pa1,
pay_population_ranges pop,
per_periods_of_service pos,
per_all_assignments_f as1,
pay_assignment_actions act,
pay_payroll_actions pa2,
pay_action_classifications pcl,
per_all_assignments_f as2
where pa1.payroll_action_id = pactid
and pa2.consolidation_set_id = pa1.consolidation_set_id
and pa2.effective_date between
pa1.start_date and pa1.effective_date
and act.payroll_action_id = pa2.payroll_action_id
and act.action_status in ('C','S')
and pcl.classification_name = class
and pa2.action_type = pcl.action_type
and as1.assignment_id = act.assignment_id
and pa1.effective_date between
as1.effective_start_date and as1.effective_end_date
and as2.assignment_id = act.assignment_id
and pa2.effective_date between
as2.effective_start_date and as2.effective_end_date
and pos.period_of_service_id = as1.period_of_service_id
and pop.payroll_action_id = pactid
and pop.chunk_number = chunk
and pos.person_id = pop.person_id
and (as2.payroll_id = pa1.payroll_id or pa1.payroll_id is null)
and not exists (
select null
from pay_assignment_actions ac2,
pay_payroll_actions pa3,
pay_action_interlocks int
where int.locked_action_id = act.assignment_action_id
and ac2.assignment_action_id = int.locking_action_id
and pa3.payroll_action_id = ac2.payroll_action_id
and pa3.action_type = 'CP')
and not exists (
select /*+ ORDERED*/
null
from per_all_assignments_f as3,
pay_assignment_actions ac3
where itpflg = 'N'
and ac3.payroll_action_id = pa2.payroll_action_id
and ac3.action_status not in ('C','S')
and as3.assignment_id = ac3.assignment_id
and pa2.effective_date between
as3.effective_start_date and as3.effective_end_date
and as3.person_id = as2.person_id)
and ((pa2.action_type in ('P', 'U')
and pa1.batch_process_mode in ('UNCLEARED', 'ALL')
and exists (select 1
from pay_pre_payments ppp,
pay_org_payment_methods_f pom
where ppp.assignment_action_id = act.assignment_action_id
and pom.org_payment_method_id = ppp.org_payment_method_id
and pom.cost_payment = 'Y'
and pa2.effective_date between
pom.effective_start_date and pom.effective_end_date))
or (pa2.action_type in ('H', 'M')
and pa1.batch_process_mode in ('CLEARED', 'ALL')
and exists (select 1
from pay_pre_payments ppp,
pay_org_payment_methods_f pom,
pay_ce_reconciled_payments crp
where ppp.pre_payment_id = act.pre_payment_id
and pom.org_payment_method_id = ppp.org_payment_method_id
and pom.cost_cleared_payment = 'Y'
and crp.assignment_action_id = act.assignment_action_id
and pa2.effective_date between
pom.effective_start_date and pom.effective_end_date))
or (pa2.action_type = 'E'
and pa1.batch_process_mode in ('CLEARED', 'ALL')
and exists (select 1
from pay_pre_payments ppp,
pay_org_payment_methods_f pom,
pay_ce_reconciled_payments crp
where ppp.pre_payment_id = act.pre_payment_id
and pom.org_payment_method_id = ppp.org_payment_method_id
and pom.cost_cleared_payment = 'Y'
and pom.exclude_manual_payment = 'N'
and crp.assignment_action_id = act.assignment_action_id
and pa2.effective_date between
pom.effective_start_date and pom.effective_end_date))
or (pa2.action_type = 'D'
and ((pa1.batch_process_mode in ('CLEARED', 'ALL')
and exists (select 1
from pay_action_interlocks int,
pay_assignment_actions chq,
pay_payroll_actions pcq,
pay_pre_payments ppp,
pay_org_payment_methods_f pom,
pay_ce_reconciled_payments crp
where int.locking_action_id = act.assignment_action_id
and chq.assignment_action_id = int.locked_action_id
and pcq.payroll_action_id = chq.payroll_action_id
and pcq.action_type = 'H'
and ppp.pre_payment_id = chq.pre_payment_id
and pom.org_payment_method_id = ppp.org_payment_method_id
and pom.cost_cleared_payment = 'Y'
and crp.assignment_action_id = act.assignment_action_id
and pa2.effective_date between
pom.effective_start_date and pom.effective_end_date))
or (pa1.batch_process_mode in ('UNCLEARED', 'ALL')
and exists (select 1
from pay_action_interlocks int,
pay_assignment_actions chq,
pay_payroll_actions pcq,
pay_pre_payments ppp,
pay_org_payment_methods_f pom
where int.locking_action_id = act.assignment_action_id
and chq.assignment_action_id = int.locked_action_id
and pcq.payroll_action_id = chq.payroll_action_id
and pcq.action_type = 'H'
and ppp.pre_payment_id = chq.pre_payment_id
and pom.org_payment_method_id = ppp.org_payment_method_id
and pom.cost_payment = 'Y'
and pom.cost_cleared_void_payment = 'N'
and pa2.effective_date between
pom.effective_start_date and pom.effective_end_date)))))
order by act.assignment_id
for update of as1.assignment_id, pos.period_of_service_id;
/* process the insert of assignment actions */
/* logic prevents more than one action per assignment */
if(prev_assignid is null OR prev_assignid <> assignid OR
prev_pactid <> lpactid) then
-- get a value for the action id that is locking.
select pay_assignment_actions_s.nextval
into lockingactid
from dual;
proc_estcosts - insert actions for Estimate Costing action type.
DESCRIPTION
For the range defined by the starting and ending person_id,
inserts a chunk of assignment actions
NOTES
*/
procedure proc_estcosts
(
pactid in number,
stperson in number,
endperson in number,
chunk in number,
rand_chunk in number,
class in varchar2,
itpflg in varchar2,
use_pop_person in number
) is
--
cursor estcostingpopcur
(
pactid number,
chunk number,
class varchar2,
itpflg varchar2
) is
select /*+ ORDERED
index(pa2 PAY_PAYROLL_ACTIONS_N5)
index(as1 PER_ASSIGNMENTS_F_N4)
index(as2 PER_ASSIGNMENTS_F_PK)
USE_NL(pop pos as1) */
act.assignment_action_id,
act.assignment_id,
act.tax_unit_id
from pay_payroll_actions pa1,
pay_all_payrolls_f pay,
per_time_periods ptp,
pay_payroll_actions pa2,
pay_action_classifications pcl,
pay_population_ranges pop,
per_periods_of_service pos,
per_all_assignments_f as1,
pay_assignment_actions act,
per_all_assignments_f as2
where pa1.payroll_action_id = pactid
and pay.consolidation_set_id = pa1.consolidation_set_id
and pa1.effective_date between
pay.effective_start_date and pay.effective_end_date
and ptp.payroll_id = pay.payroll_id
and pa1.start_date between
ptp.start_date and ptp.end_date
and pa2.consolidation_set_id = pa1.consolidation_set_id
and pa2.effective_date between
ptp.start_date and ptp.end_date
and act.payroll_action_id = pa2.payroll_action_id
and act.action_status in ('C','S')
and pcl.classification_name = class
and pa2.action_type = pcl.action_type
and as1.assignment_id = act.assignment_id
and pa2.effective_date between
as1.effective_start_date and as1.effective_end_date
and as2.assignment_id = act.assignment_id
and pa1.effective_date between
as2.effective_start_date and as2.effective_end_date
and as2.payroll_id = as1.payroll_id
and pop.payroll_action_id = pactid
and pop.chunk_number = chunk
and pos.person_id = pop.person_id
and pos.period_of_service_id = as1.period_of_service_id
and (as1.payroll_id = pa1.payroll_id or pa1.payroll_id is null)
and not exists (
select /*+ ORDERED*/
null
from per_all_assignments_f as3,
pay_assignment_actions ac3
where itpflg = 'N'
and ac3.payroll_action_id = pa2.payroll_action_id
and ac3.action_status not in ('C','S')
and as3.assignment_id = ac3.assignment_id
and pa2.effective_date between
as3.effective_start_date and as3.effective_end_date
and as3.person_id = as2.person_id)
order by act.assignment_id
for update of as1.assignment_id, pos.period_of_service_id;
select /*+ ORDERED
index(pa2 PAY_PAYROLL_ACTIONS_N5)
index(as1 PER_ASSIGNMENTS_F_N4)
index(as2 PER_ASSIGNMENTS_F_PK)
USE_NL(pos as1) */
act.assignment_action_id,
act.assignment_id,
act.tax_unit_id
from pay_payroll_actions pa1,
pay_all_payrolls_f pay,
per_time_periods ptp,
pay_payroll_actions pa2,
pay_action_classifications pcl,
per_periods_of_service pos,
per_all_assignments_f as1,
pay_assignment_actions act,
per_all_assignments_f as2
where pa1.payroll_action_id = pactid
and pay.consolidation_set_id = pa1.consolidation_set_id
and pa1.effective_date between
pay.effective_start_date and pay.effective_end_date
and ptp.payroll_id = pay.payroll_id
and pa1.start_date between
ptp.start_date and ptp.end_date
and pa2.consolidation_set_id = pa1.consolidation_set_id
and pa2.effective_date between
ptp.start_date and ptp.end_date
and act.payroll_action_id = pa2.payroll_action_id
and act.action_status in ('C','S')
and pcl.classification_name = class
and pa2.action_type = pcl.action_type
and as1.assignment_id = act.assignment_id
and pa2.effective_date between
as1.effective_start_date and as1.effective_end_date
and as2.assignment_id = act.assignment_id
and pa1.effective_date between
as2.effective_start_date and as2.effective_end_date
and as2.payroll_id = as1.payroll_id
and pos.period_of_service_id = as1.period_of_service_id
and pos.person_id between stperson and endperson
and (as1.payroll_id = pa1.payroll_id or pa1.payroll_id is null)
and not exists (
select /*+ ORDERED*/
null
from per_all_assignments_f as3,
pay_assignment_actions ac3
where itpflg = 'N'
and ac3.payroll_action_id = pa2.payroll_action_id
and ac3.action_status not in ('C','S')
and as3.assignment_id = ac3.assignment_id
and pa2.effective_date between
as3.effective_start_date and as3.effective_end_date
and as3.person_id = as2.person_id)
order by act.assignment_id
for update of as1.assignment_id, pos.period_of_service_id;
/* process the insert of assignment actions */
/* logic prevents more than one action per assignment */
if(prev_assignid is null OR prev_assignid <> assignid) then
-- get a value for the action id that is locking.
select pay_assignment_actions_s.nextval
into lockingactid
from dual;
procbee - insert assignment actions for Batch Element Entry.
DESCRIPTION
Insert assignment actions for the Batch Element Entry process.
NOTES
The insert of assignment actions for Batch Element Entry is based
on the followig logic: We select all the assignments within the
specified range. One assignment action is then inserted
for each of the assignment selected.
*/
procedure procbee
(
pactid in number,
stperson in number,
endperson in number,
chunk in number,
rand_chunk in number,
use_pop_person in number
) is
--
cursor beepopcur
(
pactid number,
chunk number
) is
select asg.assignment_id
from pay_payroll_actions pac,
pay_population_ranges pop,
pay_batch_headers bth,
pay_batch_lines btl,
per_all_assignments_f asg
where pac.payroll_action_id = pactid
and pac.action_type = 'BEE'
and pac.batch_id = bth.batch_id
and bth.batch_id = btl.batch_id
and btl.assignment_id = asg.assignment_id
and btl.effective_date between asg.effective_start_date
and asg.effective_end_date
and pop.payroll_action_id = pactid
and pop.chunk_number = chunk
and asg.person_id = pop.person_id
order by asg.assignment_id
for update of asg.assignment_id, btl.batch_line_id;
select asg.assignment_id
from pay_payroll_actions pac,
pay_batch_lines btl,
per_all_assignments_f asg
where pac.payroll_action_id = pactid
and pac.action_type = 'BEE'
and pac.batch_id = btl.batch_id
and btl.assignment_id = asg.assignment_id
and btl.effective_date between asg.effective_start_date
and asg.effective_end_date
and asg.person_id between stperson and endperson
order by asg.assignment_id
for update of asg.assignment_id, btl.batch_line_id;
select pay_assignment_actions_s.nextval
into asgactid
from dual;
proctgl - insert assignment actions for Transfer to GL.
DESCRIPTION
Insert assignment actions for the Transfer to GL process.
NOTES
The insert of assignment actions for Transfer to GL is based
on the followig logic: We select all the (Payroll Run)
assignment actions that have been costed within the
specified date range. One assignment action is then inserted
for each of the assignment actions selected. In addition,
an interlock row is inserted from the newly created TGL action
to both the Costing action and to the Payroll Run actions that
were costed by it. (Phew)
*/
procedure proctgl
(
pactid in number,
stperson in number,
endperson in number,
chunk in number,
rand_chunk in number,
itpflg in varchar2,
use_pop_person in number
) is
cursor tglpopcur
(
pactid number,
chunk number,
itpflg varchar2
) is
select /*+ ORDERED
index(pa2 PAY_PAYROLL_ACTIONS_N5)
index(as1 PER_ASSIGNMENTS_F_PK)
index(as2 PER_ASSIGNMENTS_F_N4)
USE_NL(pop pos as1 as2) */
ac2.assignment_action_id,
ac2.assignment_id,
ac2.tax_unit_id,
pa2.action_type
from pay_payroll_actions pa,
pay_payroll_actions pa2,
pay_action_classifications pcl,
pay_population_ranges pop,
per_periods_of_service pos,
per_all_assignments_f as2,
pay_assignment_actions ac2,
per_all_assignments_f as1
where pa.payroll_action_id = pactid
and pa2.consolidation_set_id = pa.consolidation_set_id
and pa2.effective_date between
pa.start_date and pa.effective_date
and ac2.payroll_action_id = pa2.payroll_action_id
and ac2.action_status = 'C'
and pcl.classification_name = 'TRANSGL'
and pa2.action_type = pcl.action_type
and as2.assignment_id = ac2.assignment_id
and pa.effective_date between
as2.effective_start_date and as2.effective_end_date
and as1.assignment_id = ac2.assignment_id
and pa2.effective_date between
as1.effective_start_date and as1.effective_end_date
and nvl(decode(pa2.action_type, 'EC', nvl(pa.payroll_id, as1.payroll_id),
'CP', nvl(pa.payroll_id, as1.payroll_id),
as1.payroll_id), -999) = nvl(as1.payroll_id, -999)
and pos.period_of_service_id = as2.period_of_service_id
and pop.payroll_action_id = pactid
and pop.chunk_number = chunk
and pos.person_id = pop.person_id
and not exists (
select null
from pay_assignment_actions ac3,
pay_payroll_actions pa3,
pay_action_interlocks in3
where in3.locked_action_id = ac2.assignment_action_id
and ac3.assignment_action_id = in3.locking_action_id
and pa3.payroll_action_id = ac3.payroll_action_id
and pa3.action_type = pa.action_type)
and not exists (
select /*+ ORDERED*/
null
from per_all_assignments_f as3,
pay_assignment_actions ac3
where itpflg = 'N'
and ac3.payroll_action_id = pa2.payroll_action_id
and ac3.action_status not in ('C','S')
and as3.assignment_id = ac3.assignment_id
and pa2.effective_date between
as3.effective_start_date and as3.effective_end_date
and as3.person_id = as1.person_id)
order by ac2.assignment_id, ac2.payroll_action_id, ac2.assignment_action_id
for update of as2.assignment_id, pos.period_of_service_id;
select /*+ ORDERED
index(pa2 PAY_PAYROLL_ACTIONS_N5)
index(as1 PER_ASSIGNMENTS_F_PK)
index(as2 PER_ASSIGNMENTS_F_N4)
USE_NL(pos as1 as2) */
ac2.assignment_action_id,
ac2.assignment_id,
ac2.tax_unit_id,
pa2.action_type
from pay_payroll_actions pa,
pay_payroll_actions pa2,
pay_action_classifications pcl,
per_periods_of_service pos,
per_all_assignments_f as2,
pay_assignment_actions ac2,
per_all_assignments_f as1
where pa.payroll_action_id = pactid
and pa2.consolidation_set_id = pa.consolidation_set_id
and pa2.effective_date between
pa.start_date and pa.effective_date
and ac2.payroll_action_id = pa2.payroll_action_id
and ac2.action_status = 'C'
and pcl.classification_name = 'TRANSGL'
and pa2.action_type = pcl.action_type
and as2.assignment_id = ac2.assignment_id
and pa.effective_date between
as2.effective_start_date and as2.effective_end_date
and as1.assignment_id = ac2.assignment_id
and pa2.effective_date between
as1.effective_start_date and as1.effective_end_date
and nvl(decode(pa2.action_type, 'EC', nvl(pa.payroll_id, as1.payroll_id),
'CP', nvl(pa.payroll_id, as1.payroll_id),
as1.payroll_id), -999) = nvl(as1.payroll_id, -999)
and pos.period_of_service_id = as2.period_of_service_id
and pos.person_id between
stperson and endperson
and not exists (
select null
from pay_assignment_actions ac3,
pay_payroll_actions pa3,
pay_action_interlocks in3
where in3.locked_action_id = ac2.assignment_action_id
and ac3.assignment_action_id = in3.locking_action_id
and pa3.payroll_action_id = ac3.payroll_action_id
and pa3.action_type = pa.action_type)
and not exists (
select /*+ ORDERED*/
null
from per_all_assignments_f as3,
pay_assignment_actions ac3
where itpflg = 'N'
and ac3.payroll_action_id = pa2.payroll_action_id
and ac3.action_status not in ('C','S')
and as3.assignment_id = ac3.assignment_id
and pa2.effective_date between
as3.effective_start_date and as3.effective_end_date
and as3.person_id = as1.person_id)
order by ac2.assignment_id, ac2.payroll_action_id, ac2.assignment_action_id
for update of as2.assignment_id, pos.period_of_service_id;
select /*+ ORDERED
index(pa2 PAY_PAYROLL_ACTIONS_PK)
index(pos PER_PERIODS_OF_SERVICE_N3)
index(as2 PER_ASSIGNMENTS_F_N4)
index(ac2 PAY_ASSIGNMENT_ACTIONS_N51)
index(as1 PER_ASSIGNMENTS_F_PK)
USE_NL(pos pop as1 as2) */
ac2.assignment_action_id,
ac2.assignment_id,
ac2.tax_unit_id,
pa2.action_type
from pay_payroll_actions pa,
pay_population_ranges pop,
per_periods_of_service pos,
per_all_assignments_f as2,
pay_assignment_actions ac2,
pay_payroll_actions pa2,
pay_action_classifications pcl,
per_all_assignments_f as1
where pa.payroll_action_id = pactid
and pa2.consolidation_set_id = pa.consolidation_set_id
and pa2.effective_date between
pa.start_date and pa.effective_date
and ac2.payroll_action_id = pa2.payroll_action_id
and ac2.action_status = 'C'
and pcl.classification_name = 'TRANSGL'
and pa2.action_type = pcl.action_type
and as2.assignment_id = ac2.assignment_id
and pa.effective_date between
as2.effective_start_date and as2.effective_end_date
and as1.assignment_id = ac2.assignment_id
and pa2.effective_date between
as1.effective_start_date and as1.effective_end_date
and nvl(decode(pa2.action_type, 'EC', nvl(pa.payroll_id, as1.payroll_id),
'CP', nvl(pa.payroll_id, as1.payroll_id),
as1.payroll_id), -999) = nvl(as1.payroll_id, -999)
and pos.period_of_service_id = as2.period_of_service_id
and pop.payroll_action_id = pactid
and pop.chunk_number = chunk
and pos.person_id = pop.person_id
and not exists (
select null
from pay_assignment_actions ac3,
pay_payroll_actions pa3,
pay_action_interlocks in3
where in3.locked_action_id = ac2.assignment_action_id
and ac3.assignment_action_id = in3.locking_action_id
and pa3.payroll_action_id = ac3.payroll_action_id
and pa3.action_type = pa.action_type)
and not exists (
select /*+ ORDERED*/
null
from per_all_assignments_f as3,
pay_assignment_actions ac3
where itpflg = 'N'
and ac3.payroll_action_id = pa2.payroll_action_id
and ac3.action_status not in ('C','S')
and as3.assignment_id = ac3.assignment_id
and pa2.effective_date between
as3.effective_start_date and as3.effective_end_date
and as3.person_id = as1.person_id)
order by ac2.assignment_id, ac2.payroll_action_id, ac2.assignment_action_id
for update of as2.assignment_id, pos.period_of_service_id;
select ac1.assignment_action_id
from pay_action_interlocks in2,
pay_assignment_actions ac1,
pay_payroll_actions pa1,
pay_action_classifications pcl1,
per_all_assignments_f as1,
pay_payroll_actions pa
where pa.payroll_action_id = pactid
and in2.locking_action_id = costactid
and ac1.assignment_action_id = in2.locked_action_id
and ac1.source_action_id is null
and pa1.payroll_action_id = ac1.payroll_action_id
and pcl1.action_type = pa1.action_type
and pcl1.classification_name = 'COSTED'
and as1.assignment_id = ac1.assignment_id
and (as1.payroll_id = pa.payroll_id or pa.payroll_id is null)
and pa1.effective_date between
as1.effective_start_date and as1.effective_end_date;
select pay_assignment_actions_s.nextval
into lockingactid
from dual;
select distinct(pa.action_type)
into pmnt_act_type
from pay_action_interlocks int,
pay_assignment_actions aa,
pay_payroll_actions pa
where int.locking_action_id = lockedactid
and aa.assignment_action_id = int.locked_action_id
and pa.payroll_action_id = aa.payroll_action_id;
select count(*)
into not_paid
from pay_action_interlocks int,
pay_pre_payments ppp,
pay_org_payment_methods_f opm
where int.locking_action_id = lockedactid
and ppp.assignment_action_id = int.locked_action_id
and opm.org_payment_method_id = ppp.org_payment_method_id
and opm.cost_payment = 'Y'
and opm.transfer_to_gl_flag = 'Y'
and not exists
(select 1
from pay_assignment_actions aa
where aa.pre_payment_id = ppp.pre_payment_id);
select pay_assignment_actions_s.nextval
into lockingactid
from dual;
proqpp - insert assignment actions for QuickPay prepayment
DESCRIPTION
Insert assignment actions for the QuickPay prepayment process
NOTES
An assignment action is inserted for the assignment which is specified
on the target_payroll_action_id column of the Quick Pay action.
When this is done the action population status is set to complete
*/
procedure proqpp
(
pactid in number,
lub in varchar2,
lul in varchar2
) is
cursor qpcur ( pactid number ) is
select ac1.assignment_action_id,
ac1.assignment_id,
ac1.tax_unit_id,
pa1.action_type
from pay_assignment_actions ac1,
pay_payroll_actions pa1
where pa1.payroll_action_id = pactid
and pa1.target_payroll_action_id = ac1.payroll_action_id
and not exists (
select 1
from pay_assignment_actions ac2
where ac2.payroll_action_id = pactid
and ac2.assignment_id = ac1.assignment_id)
for update of ac1.assignment_action_id ;
select pay_assignment_actions_s.nextval
into lockingactid
from dual;
update_pact(pactid, 'C', atype, sysdate,lub,lul);
procarc - insert assignment actions for Archive process
DESCRIPTION
Insert assignment actions for the Archive process
NOTES
This dynamically calls legislative code to perform the insertion
of the assignment actions, since it is the legislation that
knows which assignments are to be included in the archive.
*/
procedure procarc(pactid in number,
stperson in number,
endperson in number,
chunk in number
)
is
sql_cur number;
select assignment_action_code
into action_proc
from pay_report_format_mappings_f prfm,
pay_payroll_actions ppa
where ppa.payroll_action_id = pactid
and ppa.report_type = prfm.report_type
and ppa.report_qualifier = prfm.report_qualifier
and ppa.report_category = prfm.report_category
and ppa.effective_date between prfm.effective_start_date
and prfm.effective_end_date;
ending person_id and inserts a chunk of assignment actions
plus their associated interlock rows. This function for the
Bank or Post Office payment (PP) action only.
NOTES
*/
procedure procpp
(
pactid in number, -- payroll_action_id.
stperson in number, -- starting person_id of range.
endperson in number, -- ending person_id of range.
chunk in number, -- current chunk_number.
rand_chunk in number, -- current chunk_number.
itpflg in varchar2, -- legislation type.
ptype in number, -- payment_type_id.
use_pop_person in number -- use population_ranges person_id column
) is
cursor pppopcur
(
pactid number,
chunk number,
itpflg varchar2,
ptype number
) is
select /*+ ORDERED
INDEX(pa2 PAY_PAYROLL_ACTIONS_N5)
INDEX(pos PER_PERIODS_OF_SERVICE_N3)
INDEX(as1 PER_ASSIGNMENTS_N4)
INDEX(as2 PER_ASSIGNMENTS_F_PK)
INDEX(act PAY_ASSIGNMENT_ACTIONS_N51)
index(opm PAY_ORG_PAYMENT_METHODS_F_PK)
USE_NL(pop pos ppp opm as1 act as2) */
act.assignment_action_id,
act.assignment_id,
act.tax_unit_id,
ppp.pre_payment_id
from pay_payroll_actions pa1,
pay_payroll_actions pa2,
pay_action_classifications pcl,
pay_population_ranges pop,
per_periods_of_service pos,
per_all_assignments_f as1,
pay_assignment_actions act,
per_all_assignments_f as2,
pay_pre_payments ppp,
pay_org_payment_methods_f opm
where pa1.payroll_action_id = pactid
and pa2.consolidation_set_id +0 = pa1.consolidation_set_id
and pa2.effective_date between
pa1.start_date and pa1.effective_date
and act.payroll_action_id = pa2.payroll_action_id
and act.action_status = 'C'
and pcl.classification_name = 'PPPAYMENT'
and pa2.action_type = pcl.action_type
and as1.assignment_id = act.assignment_id
and pa2.effective_date between
as1.effective_start_date and as1.effective_end_date
and as2.assignment_id = act.assignment_id
and pa1.effective_date between
as2.effective_start_date and as2.effective_end_date
and as2.payroll_id + 0 = as1.payroll_id + 0
and pos.period_of_service_id = as1.period_of_service_id
and pop.payroll_action_id = pactid
and pop.chunk_number = chunk
and pos.person_id = pop.person_id
and (as1.payroll_id = pa1.payroll_id or pa1.payroll_id is null)
and ppp.assignment_action_id = act.assignment_action_id
and opm.org_payment_method_id = ppp.org_payment_method_id
and pa1.effective_date between
opm.effective_start_date and opm.effective_end_date
and opm.payment_type_id +0 = ptype
and (opm.org_payment_method_id = pa1.org_payment_method_id
or pa1.org_payment_method_id is null)
and not exists (
select null
from per_all_assignments_f as3,
pay_assignment_actions ac3
where itpflg = 'N'
and ac3.payroll_action_id = pa2.payroll_action_id
and ac3.action_status not in ('C', 'S')
and as3.assignment_id = ac3.assignment_id
and pa2.effective_date between
as3.effective_start_date and as3.effective_end_date
and as3.person_id = as2.person_id)
and not exists (
select /*+ ORDERED*/
null
from pay_action_interlocks int,
pay_assignment_actions ac2
where int.locked_action_id = act.assignment_action_id
and ac2.assignment_action_id = int.locking_action_id
and ac2.pre_payment_id = ppp.pre_payment_id
and not exists (
select null
from pay_assignment_actions paa_void,
pay_action_interlocks pai_void,
pay_payroll_actions ppa_void
where pai_void.locked_action_id = ac2.assignment_action_id
and pai_void.locking_action_id = paa_void.assignment_action_id
and paa_void.payroll_action_id = ppa_void.payroll_action_id
and ppa_void.action_type = 'D')
)
order by act.assignment_id
for update of as1.assignment_id, pos.period_of_service_id;
select /*+ ORDERED
INDEX(pa2 PAY_PAYROLL_ACTIONS_N5)
INDEX(pos PER_PERIODS_OF_SERVICE_N3)
INDEX(as1 PER_ASSIGNMENTS_N4)
INDEX(as2 PER_ASSIGNMENTS_F_PK)
INDEX(act PAY_ASSIGNMENT_ACTIONS_N51)
index(opm PAY_ORG_PAYMENT_METHODS_F_PK)
USE_NL(pos ppp opm as1 act as2) */
act.assignment_action_id,
act.assignment_id,
act.tax_unit_id,
ppp.pre_payment_id
from pay_payroll_actions pa1,
pay_payroll_actions pa2,
pay_action_classifications pcl,
per_periods_of_service pos,
per_all_assignments_f as1,
pay_assignment_actions act,
per_all_assignments_f as2,
pay_pre_payments ppp,
pay_org_payment_methods_f opm
where pa1.payroll_action_id = pactid
and pa2.consolidation_set_id +0 = pa1.consolidation_set_id
and pa2.effective_date between
pa1.start_date and pa1.effective_date
and act.payroll_action_id = pa2.payroll_action_id
and act.action_status = 'C'
and pcl.classification_name = 'PPPAYMENT'
and pa2.action_type = pcl.action_type
and as1.assignment_id = act.assignment_id
and pa2.effective_date between
as1.effective_start_date and as1.effective_end_date
and as2.assignment_id = act.assignment_id
and pa1.effective_date between
as2.effective_start_date and as2.effective_end_date
and as2.payroll_id + 0 = as1.payroll_id + 0
and pos.period_of_service_id = as1.period_of_service_id
and pos.person_id between stperson and endperson
and (as1.payroll_id = pa1.payroll_id or pa1.payroll_id is null)
and ppp.assignment_action_id = act.assignment_action_id
and opm.org_payment_method_id = ppp.org_payment_method_id
and pa1.effective_date between
opm.effective_start_date and opm.effective_end_date
and opm.payment_type_id +0 = ptype
and (opm.org_payment_method_id = pa1.org_payment_method_id
or pa1.org_payment_method_id is null)
and not exists (
select null
from per_all_assignments_f as3,
pay_assignment_actions ac3
where itpflg = 'N'
and ac3.payroll_action_id = pa2.payroll_action_id
and ac3.action_status not in ('C', 'S')
and as3.assignment_id = ac3.assignment_id
and pa2.effective_date between
as3.effective_start_date and as3.effective_end_date
and as3.person_id = as2.person_id)
and not exists (
select /*+ ORDERED*/
null
from pay_action_interlocks int,
pay_assignment_actions ac2
where int.locked_action_id = act.assignment_action_id
and ac2.assignment_action_id = int.locking_action_id
and ac2.pre_payment_id = ppp.pre_payment_id
and not exists (
select null
from pay_assignment_actions paa_void,
pay_action_interlocks pai_void,
pay_payroll_actions ppa_void
where pai_void.locked_action_id = ac2.assignment_action_id
and pai_void.locking_action_id = paa_void.assignment_action_id
and paa_void.payroll_action_id = ppa_void.payroll_action_id
and ppa_void.action_type = 'D')
)
order by act.assignment_id
for update of as1.assignment_id, pos.period_of_service_id;
select /*+ ORDERED
INDEX(pa2 PAY_PAYROLL_ACTIONS_PK)
INDEX(pos PER_PERIODS_OF_SERVICE_N3)
INDEX(as1 PER_ASSIGNMENTS_N4)
INDEX(as2 PER_ASSIGNMENTS_F_PK)
INDEX(act PAY_ASSIGNMENT_ACTIONS_N51)
index(opm PAY_ORG_PAYMENT_METHODS_F_PK)
USE_NL(pos pop ppp opm as1 act as2) */
act.assignment_action_id,
act.assignment_id,
act.tax_unit_id,
ppp.pre_payment_id
from pay_payroll_actions pa1,
pay_population_ranges pop,
per_periods_of_service pos,
per_all_assignments_f as1,
pay_assignment_actions act,
pay_payroll_actions pa2,
pay_action_classifications pcl,
per_all_assignments_f as2,
pay_pre_payments ppp,
pay_org_payment_methods_f opm
where pa1.payroll_action_id = pactid
and pa2.consolidation_set_id +0 = pa1.consolidation_set_id
and pa2.effective_date between
pa1.start_date and pa1.effective_date
and act.payroll_action_id = pa2.payroll_action_id
and act.action_status = 'C'
and pcl.classification_name = 'PPPAYMENT'
and pa2.action_type = pcl.action_type
and as1.assignment_id = act.assignment_id
and pa1.effective_date between
as1.effective_start_date and as1.effective_end_date
and as2.assignment_id = act.assignment_id
and pa2.effective_date between
as2.effective_start_date and as2.effective_end_date
and as2.payroll_id + 0 = as1.payroll_id + 0
and pos.period_of_service_id = as1.period_of_service_id
and pop.payroll_action_id = pactid
and pop.chunk_number = chunk
and pos.person_id = pop.person_id
and (as1.payroll_id = pa1.payroll_id or pa1.payroll_id is null)
and ppp.assignment_action_id = act.assignment_action_id
and opm.org_payment_method_id = ppp.org_payment_method_id
and pa1.effective_date between
opm.effective_start_date and opm.effective_end_date
and opm.payment_type_id +0 = ptype
and (opm.org_payment_method_id = pa1.org_payment_method_id
or pa1.org_payment_method_id is null)
and not exists (
select null
from per_all_assignments_f as3,
pay_assignment_actions ac3
where itpflg = 'N'
and ac3.payroll_action_id = pa2.payroll_action_id
and ac3.action_status not in ('C', 'S')
and as3.assignment_id = ac3.assignment_id
and pa2.effective_date between
as3.effective_start_date and as3.effective_end_date
and as3.person_id = as2.person_id)
and not exists (
select /*+ ORDERED*/
null
from pay_action_interlocks int,
pay_assignment_actions ac2
where int.locked_action_id = act.assignment_action_id
and ac2.assignment_action_id = int.locking_action_id
and ac2.pre_payment_id = ppp.pre_payment_id
and not exists (
select null
from pay_assignment_actions paa_void,
pay_action_interlocks pai_void,
pay_payroll_actions ppa_void
where pai_void.locked_action_id = ac2.assignment_action_id
and pai_void.locking_action_id = paa_void.assignment_action_id
and paa_void.payroll_action_id = ppa_void.payroll_action_id
and ppa_void.action_type = 'D')
)
order by act.assignment_id
for update of as1.assignment_id, pos.period_of_service_id;
select pay_assignment_actions_s.nextval
into lockingactid
from dual;
asact - insert assignment actions and interlocks
DESCRIPTION
Overall control of the insertion of assignment actions
and interlocks for the non run payroll actions.
NOTES
*/
procedure asact
(
pactid in number, -- payroll_action_id
atype in varchar2, -- action_type.
itpflg in varchar2, -- independent time periods flag.
ptype in number, -- payment_type_id.
lub in varchar2, -- last_updated_by.
lul in varchar2, -- last_update_login.
use_pop_person in number -- use population_ranges person_id column
) is
QPPREPAY constant varchar2(1) := 'U';
select nvl(multi_assignments_flag, 'N')
into multi_asg_fg
from pay_all_payrolls_f prl,
pay_payroll_Actions pact
where pact.payroll_action_id = pactid
and prl.payroll_id = pact.payroll_id
and pact.effective_date between prl.effective_start_date
and prl.effective_end_date;
select parameter_value
into g_many_procs_in_period
from pay_action_parameters
where parameter_name = 'MANY_PROCS_IN_PERIOD';
select parameter_value
into g_plsql_proc_insert
from pay_action_parameters
where parameter_name = 'PLSQL_PROC_INSERT';
g_plsql_proc_insert := 'Y';
if g_plsql_proc_insert = 'Y' then
if g_many_procs_in_period = 'N' then
l_use_pop_person := 1;
update pay_population_ranges rge
set rge.range_status = 'P'
where rge.payroll_action_id = pactid
and rge.chunk_number = chunk;
delete from pay_population_ranges rge
where rge.payroll_action_id = pactid
and rge.chunk_number = chunk;
update pay_population_ranges rge
set rge.range_status = 'E'
where rge.payroll_action_id = pactid
and rge.chunk_number = chunk;
update_pact(pactid, 'E', itpflg,sysdate,stperson,endperson);
select ppa.action_status
, ppa.action_type
, rfm.report_name
from pay_payroll_actions ppa
, pay_report_format_mappings_f rfm
where ppa.payroll_action_id = p_pact_id
and ppa.report_type = rfm.report_type(+)
and ppa.report_qualifier = rfm.report_qualifier(+)
and ppa.report_category = rfm.report_category(+);
select paa.chunk_number
from pay_assignment_actions paa
, per_all_assignments_f paf
, per_all_people_f ppf
where paa.payroll_action_id = p_ppa_id
and paa.assignment_id = p_paf_id
and paa.assignment_id = paf.assignment_id
and paf.person_id = ppf.person_id
and rownum = 1;
select min(chunk_number)
from (select chunk_number, count(assignment_action_id) ct
from pay_assignment_actions
where payroll_action_id = p_ppa_id
group by chunk_number) v1
where v1.ct = (select min(v2.ct) from (select count(assignment_action_id) ct
from pay_assignment_actions
where payroll_action_id = p_ppa_id
group by chunk_number) v2);
select pay_assignment_actions_s.nextval
into l_asg_act_id
from dual;
insert into pay_assignment_actions
(assignment_action_id
,assignment_id
,payroll_action_id
,action_status
,chunk_number
,action_sequence
,pre_payment_id
,object_version_number
,tax_unit_id
,source_action_id
,object_id
,object_type
,start_date
,end_date
)
values
(l_asg_act_id
,p_asg_id
,p_pact_id
,'U'
,l_chunk
,l_asg_act_id
,''
,1
,p_gre_id
,''
,p_object_id
,p_object_type
,''
,''
);