[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select rule_mode
into l_rule_value
from pay_legislation_rules
where legislation_code = p_leg_code
and rule_type = p_rule_name;
if (p_rule_name = 'RETRO_DELETE') then
l_rule_value := 'Y';
select pre_payment_id
from pay_pre_payments
where payroll_action_id = p_pact;
update pay_contributing_payments
set pre_payment_id = null
where pre_payment_id = payrec.pre_payment_id;
delete from pay_pre_payments
where pre_payment_id = payrec.pre_payment_id;
select action_information_id
from pay_action_information
where action_context_id = cp_action_context_id
and action_context_type = cp_action_context_type;
/* delete Action Information */
delete from pay_action_information
where action_information_id = actionrec.action_information_id;
select archive_item_id
from ff_archive_items
where context1 = p_source
and nvl(archive_type, 'AAP') = p_archive_type;
delete from ff_archive_item_contexts
where archive_item_id = arcrec.archive_item_id;
delete from ff_archive_items
where archive_item_id = arcrec.archive_item_id;
delete from pay_file_details
where source_id = p_source_id
and source_type = p_source_type;
select pee.element_entry_id
from pay_element_entries_f pee,
pay_assignment_actions paa
where paa.assignment_action_id = p_assact_id
and pee.assignment_id = paa.assignment_id
and pee.creator_id = paa.assignment_action_id
and pee.creator_type = 'P';
/* Delete the entry values before the element entry */
for ceevrec in ceev loop
delete from pay_element_entry_values_f pev
where element_entry_id = ceevrec.element_entry_id;
delete from pay_element_entries_f
where creator_id = p_assact_id
and creator_type = 'P'
and element_entry_id = ceevrec.element_entry_id;
select pee.element_entry_id
from pay_element_entries_f pee,
pay_assignment_actions paa
where paa.assignment_action_id = p_assact_id
and pee.assignment_id = paa.assignment_id
and pee.creator_id = paa.assignment_action_id
and pee.creator_type = 'D';
/* Delete the entry values before the element entry */
for aeevrec in aeev loop
delete from pay_element_entry_values_f pev
where element_entry_id = aeevrec.element_entry_id;
delete from pay_element_entries_f
where creator_id = p_assact_id
and creator_type = 'D'
and element_entry_id = aeevrec.element_entry_id;
select pee.element_entry_id
from pay_element_entries_f pee,
pay_assignment_actions paa
where paa.assignment_action_id = p_assact_id
and pee.assignment_id = paa.assignment_id
and pee.creator_id = paa.assignment_action_id
and pee.creator_type in ('AD', 'AE','D');
/* Delete the entry values before the element entry */
for aeevrec in aeev loop
delete from pay_element_entry_values_f pev
where element_entry_id = aeevrec.element_entry_id;
delete from pay_element_entries_f
where creator_id = p_assact_id
and creator_type in ('AD', 'AE','D')
and element_entry_id = aeevrec.element_entry_id;
select pee.element_entry_id
from pay_element_entries_f pee,
pay_assignment_actions paa
where paa.assignment_action_id = p_assact_id
and pee.assignment_id = paa.assignment_id
and pee.creator_id = paa.assignment_action_id
and pee.creator_type = 'R';
/* Delete the entry values before the element entry */
for raeevrec in raeev loop
delete from pay_element_entry_values_f pev
where element_entry_id = raeevrec.element_entry_id;
delete from pay_element_entries_f
where creator_id = p_assact_id
and creator_type = 'R'
and element_entry_id = raeevrec.element_entry_id;
select pee.element_entry_id
from pay_element_entries_f pee,
pay_assignment_actions paa
where paa.assignment_action_id = p_assact_id
and pee.creator_id = paa.assignment_action_id
and pee.creator_type in ('RR', 'EE', 'NR', 'PR') ;
select min(pre.reprocess_date)
from pay_retro_entries pre,
pay_retro_assignments pra
where pre.retro_assignment_id = pra.retro_assignment_id
and pra.retro_assignment_action_id = p_assact_id;
/* Delete the entry values before the element entry */
for eev_rec in remove_eev loop
delete from pay_element_entry_values_f pev
where element_entry_id = eev_rec.element_entry_id;
delete from pay_entry_process_details
where element_entry_id = eev_rec.element_entry_id;
delete from pay_element_entries_f
where creator_id = p_assact_id
and creator_type in ('RR', 'EE', 'NR', 'PR')
and element_entry_id = eev_rec.element_entry_id;
update pay_retro_assignments ra
set ra.retro_assignment_action_id = null,
ra.reprocess_date = nvl(l_reprocess_date,ra.reprocess_date)
where ra.retro_assignment_action_id = p_assact_id;
* Procedure to delete entries from gl_interface tables.
*/
procedure remove_gl_entries(p_info in rollback_rec) is
c_indent varchar2(40);
select upper(parameter_value)
into mtgl_mode
from pay_action_parameters
where parameter_name = 'TRANSGL_THREAD';
select user_je_source_name
into l_source_name
from gl_je_sources
where je_source_name = 'Payroll';
delete from gl_interface gl
where gl.reference21 = to_char(p_info.pact_id)
and gl.user_je_source_name = l_source_name;
delete from pay_gl_interface pgl
where pgl.assignment_action_id = p_info.assact_id;
select pea.external_account_id
from pay_external_accounts pea,
pay_payment_types ppt,
pay_personal_payment_methods_f ppm,
pay_org_payment_methods_f opm,
pay_pre_payments ppp,
pay_payroll_actions ppa,
pay_assignment_actions paa
where paa.assignment_action_id = asgact
and paa.payroll_action_id = ppa.payroll_action_id
and paa.pre_payment_id = ppp.pre_payment_id
and ppp.org_payment_method_id = opm.org_payment_method_id
and ppp.personal_payment_method_id = ppm.personal_payment_method_id
and opm.payment_type_id = ppt.payment_type_id
and ppt.pre_validation_required = 'Y'
and ppt.validation_value = ppp.value
and ppm.external_account_id = pea.external_account_id
and ppa.effective_date between ppm.effective_start_date
and ppm.effective_end_date
and ppa.effective_date between opm.effective_start_date
and opm.effective_end_date;
update pay_external_accounts
set prenote_date = null
where external_account_id = eacrec.external_account_id
and prenote_date is not null;
select prr.run_result_id
from pay_run_results prr
where prr.assignment_action_id = p_info.assact_id;
delete from pay_run_result_values rrv
where rrv.run_result_id = crrvrec.run_result_id;
delete from pay_run_results RR
where RR.assignment_action_id = p_info.assact_id;
update pay_run_results RR
set RR.status = 'B'
where RR.assignment_action_id = p_info.assact_id;
delete from pay_action_contexts
where assignment_action_id = p_assact_id;
* Deletes all latest balances and associated balance
* context values for the specified assignment action id.
* balances are only removed if a prev balance does not exist,
* other wise the prev blance is assigned to the latest balance
*
* Deletes all latest balances if the action before rolled back is a
* balance adjustment or a balance initialisation. This is because as
* these are 'special' types of sequenced actions, their results may
* have fed the balances without the latest balances being owned by them.
*/
procedure remove_balances(p_info in rollback_rec) is
c_indent varchar2(40);
select /*+ INDEX(plb PAY_PERSON_LATEST_BALANCES_N3)*/
plb.latest_balance_id,
plb.prev_balance_value,
plb.prev_assignment_action_id,
plb.expired_value,
plb.expired_assignment_action_id,
plb.assignment_action_id
from pay_person_latest_balances plb,
pay_defined_balances pdb,
pay_balance_feeds_f pbf,
pay_run_result_values rrv,
pay_run_results prr
where prr.assignment_action_id = p_info.assact_id
and rrv.run_result_id = prr.run_result_id
and rrv.result_value is not null
and pbf.input_value_id = rrv.input_value_id
and pdb.balance_type_id = pbf.balance_type_id
and plb.defined_balance_id = pdb.defined_balance_id
and plb.person_id = l_person_id
and p_info.action_date between pbf.effective_start_date
and pbf.effective_end_date;
select /*+ INDEX(alb PAY_ASSIGNMENT_LATEST_BALA_N3)*/
alb.latest_balance_id,
alb.prev_balance_value,
alb.prev_assignment_action_id,
alb.expired_value,
alb.expired_assignment_action_id,
alb.assignment_action_id
from pay_assignment_latest_balances alb,
pay_defined_balances pdb,
pay_balance_feeds_f pbf,
pay_run_result_values rrv,
pay_run_results prr
where prr.assignment_action_id = p_info.assact_id
and rrv.run_result_id = prr.run_result_id
and rrv.result_value is not null
and pbf.input_value_id = rrv.input_value_id
and pdb.balance_type_id = pbf.balance_type_id
and alb.defined_balance_id = pdb.defined_balance_id
and alb.assignment_id = p_info.assignment_id
and p_info.action_date between pbf.effective_start_date
and pbf.effective_end_date;
select
lb.latest_balance_id,
lb.prev_balance_value,
lb.prev_assignment_action_id,
lb.prev_expiry_date,
lb.expired_value,
lb.expired_assignment_action_id,
lb.assignment_action_id
from pay_latest_balances lb,
pay_defined_balances pdb,
pay_balance_feeds_f pbf,
pay_run_result_values rrv,
pay_run_results prr
where prr.assignment_action_id = p_info.assact_id
and rrv.run_result_id = prr.run_result_id
and rrv.result_value is not null
and pbf.input_value_id = rrv.input_value_id
and pdb.balance_type_id = pbf.balance_type_id
and lb.defined_balance_id = pdb.defined_balance_id
and lb.person_id = l_person_id
and ( lb.assignment_id = p_info.assignment_id
or lb.assignment_id is null)
and ( lb.process_group_id = (select distinct pog.parent_object_group_id
from pay_object_groups pog
where pog.source_id = p_info.assignment_id
and pog.source_type = 'PAF')
or lb.process_group_id is null)
and p_info.action_date between pbf.effective_start_date
and pbf.effective_end_date;
select plb.latest_balance_id,
plb.prev_balance_value,
plb.prev_assignment_action_id,
plb.expired_value,
plb.expired_assignment_action_id
from pay_person_latest_balances plb
where plb.assignment_action_id = p_info.assact_id;
select alb.latest_balance_id,
alb.prev_balance_value,
alb.prev_assignment_action_id,
alb.expired_value,
alb.expired_assignment_action_id
from pay_assignment_latest_balances alb
where alb.assignment_action_id = p_info.assact_id;
select lb.latest_balance_id,
lb.prev_balance_value,
lb.prev_assignment_action_id,
lb.prev_expiry_date,
lb.expired_value,
lb.expired_assignment_action_id
from pay_latest_balances lb
where lb.assignment_action_id = p_info.assact_id;
select person_id
into l_person_id
from per_all_assignments_f
where assignment_id = p_info.assignment_id
and p_info.action_date between
effective_start_date and effective_end_date;
delete from pay_balance_context_values bcv
where bcv.latest_balance_id = bplbrec.latest_balance_id;
delete from pay_person_latest_balances plb
where plb.latest_balance_id = bplbrec.latest_balance_id;
update pay_person_latest_balances
set expired_assignment_action_id = -9999,
expired_value = -9999
where pay_person_latest_balances.latest_balance_id =
bplbrec.latest_balance_id;
update pay_person_latest_balances
set assignment_action_id = bplbrec.prev_assignment_action_id,
value = bplbrec.prev_balance_value,
prev_assignment_action_id = -9999,
prev_balance_value = -9999
where pay_person_latest_balances.latest_balance_id =
bplbrec.latest_balance_id;
delete from pay_balance_context_values bcv
where bcv.latest_balance_id = bplbrec.latest_balance_id;
delete from pay_person_latest_balances plb
where plb.latest_balance_id = bplbrec.latest_balance_id;
delete from pay_balance_context_values bcv
where bcv.latest_balance_id = balbrec.latest_balance_id;
delete from pay_assignment_latest_balances alb
where alb.latest_balance_id = balbrec.latest_balance_id;
update pay_assignment_latest_balances
set expired_assignment_action_id = -9999,
expired_value = -9999
where pay_assignment_latest_balances.latest_balance_id =
balbrec.latest_balance_id;
update pay_assignment_latest_balances
set assignment_action_id = balbrec.prev_assignment_action_id,
value = balbrec.prev_balance_value,
prev_assignment_action_id = -9999,
prev_balance_value = -9999
where pay_assignment_latest_balances.latest_balance_id =
balbrec.latest_balance_id;
delete from pay_balance_context_values bcv
where bcv.latest_balance_id = balbrec.latest_balance_id;
delete from pay_assignment_latest_balances alb
where alb.latest_balance_id = balbrec.latest_balance_id;
delete from pay_latest_balances lb
where lb.latest_balance_id = blbrec.latest_balance_id;
update pay_latest_balances
set expired_assignment_action_id = -9999,
expired_value = -9999,
expired_date = null
where pay_latest_balances.latest_balance_id =
blbrec.latest_balance_id;
update pay_latest_balances
set assignment_action_id = blbrec.prev_assignment_action_id,
value = blbrec.prev_balance_value,
expiry_date = blbrec.prev_expiry_date,
prev_assignment_action_id = -9999,
prev_balance_value = -9999,
prev_expiry_date = null
where pay_latest_balances.latest_balance_id =
blbrec.latest_balance_id;
delete from pay_latest_balances alb
where alb.latest_balance_id = blbrec.latest_balance_id;
delete from pay_balance_context_values bcv
where bcv.latest_balance_id = calbrec.latest_balance_id;
delete from pay_assignment_latest_balances alb
where alb.latest_balance_id=calbrec.latest_balance_id;
update pay_assignment_latest_balances
set expired_assignment_action_id=-9999,
expired_value=-9999
where pay_assignment_latest_balances.latest_balance_id =
calbrec.latest_balance_id;
update pay_assignment_latest_balances
set assignment_action_id=calbrec.prev_assignment_action_id,
value=calbrec.prev_balance_value,
prev_assignment_action_id=-9999,
prev_balance_value=-9999
where pay_assignment_latest_balances.latest_balance_id =
calbrec.latest_balance_id;
delete from pay_balance_context_values bcv
where bcv.latest_balance_id = cplbrec.latest_balance_id;
delete from pay_person_latest_balances plb
where plb.latest_balance_id=cplbrec.latest_balance_id;
update pay_person_latest_balances
set expired_assignment_action_id=-9999,
expired_value=-9999
where pay_person_latest_balances.latest_balance_id =
cplbrec.latest_balance_id;
update pay_person_latest_balances
set assignment_action_id=cplbrec.prev_assignment_action_id,
value=cplbrec.prev_balance_value,
prev_assignment_action_id=-9999,
prev_balance_value=-9999
where pay_person_latest_balances.latest_balance_id =
cplbrec.latest_balance_id;
delete from pay_latest_balances lb
where lb.latest_balance_id=clbrec.latest_balance_id;
update pay_latest_balances
set expired_assignment_action_id=-9999,
expired_value=-9999,
expired_date = null
where pay_latest_balances.latest_balance_id =
clbrec.latest_balance_id;
update pay_latest_balances
set assignment_action_id=clbrec.prev_assignment_action_id,
value=clbrec.prev_balance_value,
expiry_date = clbrec.prev_expiry_date,
prev_assignment_action_id=-9999,
prev_balance_value=-9999,
prev_expiry_date = null
where pay_latest_balances.latest_balance_id =
clbrec.latest_balance_id;
* Delete all entries and entry values that were inserted
* by a balance adjustment.
* It is only called for a balance adjustment action.
*/
procedure undo_bal_adjust(p_action_date in date, p_assact_id in number) is
-- Batch balance adjustment can have many adjustments
-- for an assignment action.
cursor c1 is
select pee.element_entry_id
from pay_element_entries_f pee,
pay_assignment_actions paa
where paa.assignment_action_id = p_assact_id
and pee.assignment_id = paa.assignment_id
and pee.creator_id = paa.assignment_action_id
and pee.creator_type = 'B' -- (B)alance Adjustment
and p_action_date between
pee.effective_start_date and pee.effective_end_date;
delete from pay_element_entry_values_f pev
where pev.element_entry_id = c1rec.element_entry_id
and p_action_date between
pev.effective_start_date and pev.effective_end_date;
delete from pay_element_entries_f pee
where pee.element_entry_id = c1rec.element_entry_id
and p_action_date between
pee.effective_start_date and pee.effective_end_date;
* Delete all messages (from pay_message_lines) as specified
* by the source_type. in other words:
* P : payroll_action_id
* A : assignment_action_id
*/
procedure remove_messages(p_info in rollback_rec, p_source_type in varchar2) is
begin
delete from pay_message_lines pml
where pml.source_type = p_source_type
and pml.source_id =
decode(p_source_type,
'P', p_info.pact_id,
'A', p_info.assact_id);
select pac.business_group_id,
pac.effective_date,
pac.start_date,
hrl.meaning,
pac.action_type,
pac.action_status,
trunc(sysdate),
pay.payroll_name,
grp.name,
grp.legislation_code,
pac.date_earned,
pac.purge_phase,
pac.retro_definition_id,
pac.batch_id,
decode(pac.action_type, 'T',
nvl(pay_core_utils.get_parameter('SLA_MODE',
pac.legislative_parameters),
'N'),
'N')
into p_info.bg_id,
p_info.action_date,
p_info.action_start_date,
p_info.action_name,
p_info.action_type,
p_info.action_status,
p_info.current_date,
p_info.payroll_name,
p_info.bg_name,
p_info.legislation_code,
p_info.date_earned,
p_info.purge_phase,
p_info.retro_definition_id,
p_info.batch_id,
p_info.sub_ledger_acc
from pay_payroll_actions pac,
pay_all_payrolls_f pay,
per_business_groups_perf grp,
hr_lookups hrl
where pac.payroll_action_id = p_info.pact_id
and hrl.lookup_code = pac.action_type
and hrl.lookup_type = 'ACTION_TYPE'
and grp.business_group_id = pac.business_group_id + 0
and pay.payroll_id (+) = pac.payroll_id
and pac.effective_date between
pay.effective_start_date (+) and pay.effective_end_date (+);
select null
into dummy
from pay_action_classifications CLASS
where CLASS.action_type = p_info.action_type
and CLASS.classification_name = 'SEQUENCED';
select /*+ INDEX(paa PAY_ASSIGNMENT_ACTIONS_N50)*/
decode (ppa.action_type,
'X', paa.object_type,
decode(paa.object_type,
'PAF', null,
paa.object_type))
into p_info.object_type
from pay_assignment_actions paa,
pay_payroll_actions ppa
where paa.payroll_action_id = p_info.pact_id
and p_info.pact_id = ppa.payroll_action_id
and paa.source_action_id is null
and rownum = 1;
* Insert a message indicating a rollback has occurred. This
* is used for both assignment and payroll action rollback.
*/
procedure ins_rollback_message(p_info in rollback_rec, p_level in varchar2) is
c_indent varchar2(40);
select ppt.payment_type_name
into l_action_name
from pay_payroll_actions pac,
pay_payment_types ppt
where pac.payroll_action_id = p_info.pact_id
and ppt.payment_type_id = pac.payment_type_id;
insert into pay_message_lines (
line_sequence,
payroll_id,
message_level,
source_id,
source_type,
line_text)
values (pay_message_lines_s.nextval,
l_payroll_id,
'I', -- information.
l_source_id,
l_source_type,
l_line_text);
* overloaded procedure to insert a rollback message on successful
* completion of the rolling back of a payroll action. This must
* be callable externally, since the rollback process needs to be
* able to insert this message, independently of the plsql.
*/
procedure ins_rollback_message(p_payroll_action_id in number) is
info rollback_rec;
/*------------------------ undo_stop_update -----------------------------*/
/*
* This procedure is called when we have detected the need to undo the
* effect of a stop or update recurring entry formula result rule.
* Note that, due to the complexity of calculating entry end dates, we
* call the existing routine, but trap error messages that are
* inappropriate for our application.
*/
procedure undo_stop_update(
p_ee_id in number,
p_mult in varchar,
p_date in date,
p_mode in varchar2) is
--
-- Local variables.
effstart date;
c_indent := 'py_rollback_pkg.undo_stop_update';
select pee.effective_start_date,
pee.effective_end_date,
pee.original_entry_id,
pee.assignment_id,
pee.element_link_id
into effstart, effend, orig_ee_id, asg_id, el_id
from pay_element_entries_f pee
where pee.element_entry_id = p_ee_id
and p_date between
pee.effective_start_date and pee.effective_end_date;
if(p_mode = 'DELETE_NEXT_CHANGE') then
begin
if g_debug then
hr_utility.set_location(c_indent, 20);
select min(ee.effective_end_date)
into next_end
from pay_element_entries_f ee
where ee.element_entry_id = p_ee_id
and ee.effective_start_date > effend;
if(p_mode = 'DELETE_NEXT_CHANGE') then
update pay_element_entries_f ee
set updating_action_id=NULL
,updating_action_type =NULL
where ee.element_entry_id = p_ee_id
and ee.effective_start_date = val_start;
update pay_element_entries_f ee
set updating_action_id=NULL
,updating_action_type =NULL
where ee.element_entry_id = p_ee_id
and ee.effective_start_date = effstart;
if(p_mode = 'DELETE_NEXT_CHANGE') then
hr_utility.set_location(c_indent, 40);
delete from pay_element_entries_f ee
where ee.element_entry_id = p_ee_id
and ee.effective_start_date = val_start;
delete from pay_element_entry_values_f eev
where eev.element_entry_id = p_ee_id
and eev.effective_start_date = val_start;
update pay_element_entries_f ee
set ee.effective_end_date = next_end
where ee.element_entry_id = p_ee_id
and ee.effective_start_date = effstart;
update pay_element_entry_values_f eev
set eev.effective_end_date = next_end
where eev.element_entry_id = p_ee_id
and eev.effective_start_date = effstart;
delete from pay_element_entries_f ee
where ee.element_entry_id = p_ee_id
and ee.effective_start_date > effstart;
delete from pay_element_entry_values_f eev
where eev.element_entry_id = p_ee_id
and eev.effective_start_date > effstart;
update pay_element_entries_f ee
set ee.effective_end_date = max_end,
ee.updating_action_id=NULL,
ee.updating_action_type=NULL
where ee.element_entry_id = p_ee_id
and ee.effective_start_date = effstart;
/*check for the additional assignment id (in case of an STOP RECURRING after an UPDATE RECURRING)*/
select ee.prev_upd_action_id
into prev_asgact_id
from pay_element_entries_f ee
where ee.element_entry_id = p_ee_id
and ee.effective_start_date = effstart;
/*If it is the case then update the updating_action_id with the asg_action_id of the previous UPDATE RECURRING operation*/
if(prev_asgact_id is not null) then
update pay_element_entries_f ee
set ee.updating_action_id=prev_asgact_id,
ee.prev_upd_action_id=NULL,
ee.updating_action_type='U'
where ee.element_entry_id = p_ee_id
and ee.effective_start_date = effstart;
update pay_element_entry_values_f eev
set eev.effective_end_date = max_end
where eev.element_entry_id = p_ee_id
and eev.effective_start_date = effstart;
end undo_stop_update;
* update formula result rules. Due to the implementation of
* this functionality, the undo is non-deterministic, i.e. we
* cannot guarantee to return the database to the exact state
* it was in before the Payroll Run or QuickPay was processed.
*/
procedure proc_entry_dml(p_info in rollback_rec) is
-- This cursor returns candidates for possible undo
-- of stop ree frr. i.e. recurring entries that have
-- an effective_end_date that is same as runs date earned.
cursor stp is
select pet.multiple_entries_allowed_flag,
pee.element_entry_id,pee.updating_action_type
from pay_element_types_f pet,
pay_element_links_f pel,
pay_element_entries_f pee
where pee.assignment_id = p_info.assignment_id
and pee.entry_type = 'E'
and pel.element_link_id = pee.element_link_id
and p_info.action_date between
pel.effective_start_date and pel.effective_end_date
and pet.element_type_id = pel.element_type_id
and p_info.action_date between
pet.effective_start_date and pet.effective_end_date
and pet.processing_type = 'R'
and pee.updating_action_id = p_info.assact_id
and ((pee.effective_end_date = p_info.date_earned
and pee.effective_start_date <> p_info.action_date
and pee.updating_action_type is NULL
and p_info.date_earned between
pee.effective_start_date and pee.effective_end_date)
or(pee.updating_action_type='S'));
select pet.multiple_entries_allowed_flag,
pee.element_entry_id
from pay_element_types_f pet,
pay_element_links_f pel,
pay_element_entries_f pee
where pee.assignment_id = p_info.assignment_id
and pee.entry_type = 'E'
and ((pee.effective_start_date = p_info.action_date
and pee.updating_action_type is NULL)
or pee.updating_action_type='U')
and pee.updating_action_id = p_info.assact_id
and pel.element_link_id = pee.element_link_id
and p_info.action_date between
pel.effective_start_date and pel.effective_end_date
and pet.element_type_id = pel.element_type_id
and p_info.action_date between
pet.effective_start_date and pet.effective_end_date
and pet.processing_type = 'R';
select max(pee.effective_end_date)
into v_max_date
from pay_element_entries_f pee
where pee.element_entry_id = stprec.element_entry_id;
undo_stop_update (stprec.element_entry_id,
stprec.multiple_entries_allowed_flag,
p_info.date_earned, 'FUTURE_CHANGE');
undo_stop_update (updrec.element_entry_id,
updrec.multiple_entries_allowed_flag,
(p_info.action_date - 1), 'DELETE_NEXT_CHANGE');
update pay_assignment_actions
set action_status = 'M'
where source_action_id = p_info.assact_id;
update pay_assignment_actions
set action_status = 'M'
where assignment_action_id = p_info.assact_id;
update pay_assignment_actions
set action_status = 'B'
where source_action_id = p_info.assact_id;
update pay_assignment_actions
set action_status = 'B'
where assignment_action_id = p_info.assact_id;
delete from pay_action_interlocks lck
where lck.locking_action_id = p_info.assact_id;
delete from pay_assignment_actions
where source_action_id = p_info.assact_id;
delete from pay_assignment_actions
where assignment_action_id = p_info.assact_id;
delete from pay_assignment_actions
where source_action_id = p_info.assact_id;
delete from pay_action_interlocks lck
where lck.locking_action_id = p_info.assact_id;
select paa_chd.assignment_action_id
from pay_assignment_actions paa_chd
where paa_chd.source_action_id = p_asgact_id
order by paa_chd.action_sequence desc;
delete from pay_action_interlocks
where locking_action_id = chld_info.assact_id;
delete from pay_assignment_actions
where assignment_action_id = chld_info.assact_id;
delete from pay_run_balances
where assignment_action_id = p_info.assact_id;
update pay_run_balances
set balance_value = 0
where assignment_action_id = p_info.assact_id;
select ppa.element_type_id
into l_et_id
from pay_assignment_actions paa,
pay_payroll_actions ppa
where ppa.payroll_action_id = paa.payroll_action_id
and paa.assignment_action_id = p_info.assact_id;
proc_entry_dml(p_info); -- stop/update ree frr.
delete from pay_pre_payments ppp
where ppp.assignment_action_id = p_info.assact_id;
delete from pay_costs pc
where pc.assignment_action_id = p_info.assact_id;
delete from pay_payment_costs ppc
where ppc.assignment_action_id = p_info.assact_id;
pay_sla_pkg.delete_event(p_info.assact_id);
delete from pay_purge_rollup_balances rub
where rub.assignment_action_id = p_info.assact_id;
delete from pay_contributing_payments
where assignment_action_id = p_info.assact_id;
delete from pay_quickpay_exclusions exc
where exc.assignment_action_id = p_info.assact_id;
delete from pay_quickpay_inclusions inc
where inc.assignment_action_id = p_info.assact_id;
select null,
ACT.action_sequence,
ACT.action_status,
ACT.secondary_status,
null,
PEO.person_id,
substr(PEO.full_name,1,80),
null
into p_info.assignment_id,
l_action_sequence,
l_action_status,
l_sec_status,
p_info.payroll_id,
l_person_id,
p_info.full_name,
p_info.assignment_number
from
per_all_people_f PEO,
pay_object_groups POG_PER,
pay_assignment_actions ACT
where ACT.assignment_action_id = p_info.assact_id
and ACT.source_action_id is null
and ACT.object_id = POG_PER.object_group_id
and POG_PER.source_id = PEO.person_id
and p_info.action_date between
PEO.effective_start_date and PEO.effective_end_date;
select null,
ACT.action_sequence,
ACT.action_status,
ACT.secondary_status,
null,
null,
null,
null
into p_info.assignment_id,
l_action_sequence,
l_action_status,
l_sec_status,
p_info.payroll_id,
l_person_id,
p_info.full_name,
p_info.assignment_number
from pay_assignment_actions ACT
where ACT.assignment_action_id = p_info.assact_id;
select ACT.assignment_id,
ACT.action_sequence,
ACT.action_status,
ACT.secondary_status,
ASS.payroll_id,
PEO.person_id,
substr(PEO.full_name,1,80),
ASS.assignment_number
into p_info.assignment_id,
l_action_sequence,
l_action_status,
l_sec_status,
p_info.payroll_id,
l_person_id,
p_info.full_name,
p_info.assignment_number
from per_all_assignments_f ASS,
per_all_people_f PEO,
pay_assignment_actions ACT
where ACT.assignment_action_id = p_info.assact_id
and ASS.assignment_id = ACT.assignment_id
and PEO.person_id = ASS.person_id
and ((p_info.action_type = 'X'
and ASS.effective_start_date = (select max(ASS2.effective_start_date)
from per_all_assignments_f ASS2
where ASS2.assignment_id = ASS.assignment_id)
and PEO.effective_start_date = (select max(PEO2.effective_start_date)
from per_all_people_f PEO2
where PEO2.person_id = PEO.person_id)
)
or
((p_info.action_type = 'Z' or p_info.action_type = 'PRU')
and ASS.effective_start_date = (select max(ASS2.effective_start_date)
from per_all_assignments_f ASS2
where ASS2.assignment_id = ASS.assignment_id
and ASS2.effective_start_date <= p_info.action_date)
and PEO.effective_start_date = (select max(PEO2.effective_start_date)
from per_all_people_f PEO2
where PEO2.person_id = PEO.person_id
and PEO2.effective_start_date <= p_info.action_date)
)
or
(p_info.action_type = 'BEE'
and ASS.effective_start_date = (select max(ASS2.effective_start_date)
from per_all_assignments_f ASS2,
pay_batch_lines pbl
where ASS2.assignment_id = ASS.assignment_id
and pbl.batch_id (+) = p_info.batch_id
and pbl.assignment_id (+) = ASS.assignment_id
and pbl.effective_Date (+) between ASS2.effective_start_date
and ASS2.effective_end_date)
and PEO.effective_start_date = (select max(PEO2.effective_start_date)
from per_all_people_f PEO2,
pay_batch_lines pbl
where PEO2.person_id = PEO.person_id
and PEO2.person_id = ASS.person_id
and pbl.batch_id (+) = p_info.batch_id
and pbl.assignment_id (+) = ASS.assignment_id
and pbl.effective_Date (+) between PEO2.effective_start_date
and PEO2.effective_end_date)
)
or
(p_info.action_type not in ('BEE','Z','X', 'PRU')
and p_info.action_date between
ASS.effective_start_date and ASS.effective_end_date
and p_info.action_date between
PEO.effective_start_date and PEO.effective_end_date));
select null
into dummy
from dual
where not exists (
select null
from pay_action_interlocks int,
pay_assignment_actions act
where int.locked_action_id = p_info.assact_id
and act.assignment_action_id = int.locking_action_id
and ((exists
(select null
from pay_payroll_actions pac
where pac.payroll_action_id = act.payroll_action_id
and action_type in ('A','H','M', 'PP', 'PRU')
and act.action_status = 'M'
)
)
or act.action_status <> 'M'
));
select null
into dummy
from dual
where not exists (
select null
from pay_action_interlocks int
where int.locked_action_id = p_info.assact_id);
select null into dummy
from dual
where not exists
(select null
from pay_assignment_actions ACT,
pay_payroll_actions PACT,
pay_action_classifications CLASS
where ACT.assignment_id = p_info.assignment_id
and ACT.action_sequence > l_action_sequence
and ACT.action_status in ('C', 'S')
and ACT.payroll_action_id = PACT.payroll_action_id
and PACT.action_type = CLASS.action_type
and CLASS.classification_name = 'SEQUENCED');
select null into dummy
from dual
where not exists
(select null
from pay_assignment_actions ACT,
pay_payroll_actions PACT,
pay_action_classifications CLASS
where ACT.assignment_id = p_info.assignment_id
and ACT.action_sequence > l_action_sequence
and ACT.action_status in ('C', 'S','M')
and ACT.payroll_action_id = PACT.payroll_action_id
and PACT.action_type = CLASS.action_type
and CLASS.classification_name = 'SEQUENCED');
select null into dummy
from dual
where not exists
(select null
from pay_action_classifications CLASS,
pay_payroll_actions PACT,
pay_assignment_actions ACT,
pay_object_groups POG_ASG,
pay_object_groups POG_PER,
pay_assignment_actions PAA_RET
where PAA_RET.assignment_action_id = p_info.assact_id
and POG_PER.object_group_id = PAA_RET.object_id
and POG_PER.source_type = 'PPF'
and POG_ASG.parent_object_group_id = POG_PER.object_group_id
and POG_ASG.source_type = 'PAF'
and POG_ASG.source_id = ACT.assignment_id
and ACT.action_sequence > l_action_sequence
and ACT.action_status in ('C', 'S')
and ACT.payroll_action_id = PACT.payroll_action_id
and PACT.action_type = CLASS.action_type
and CLASS.classification_name = 'SEQUENCED');
select null into dummy
from dual
where not exists
(select null
from pay_action_classifications CLASS,
pay_payroll_actions PACT,
pay_assignment_actions ACT,
pay_object_groups POG_ASG,
pay_object_groups POG_PER,
pay_assignment_actions PAA_RET
where PAA_RET.assignment_action_id = p_info.assact_id
and POG_PER.object_group_id = PAA_RET.object_id
and POG_PER.source_type = 'PPF'
and POG_ASG.parent_object_group_id = POG_PER.object_group_id
and POG_ASG.source_type = 'PAF'
and POG_ASG.source_id = ACT.assignment_id
and ACT.action_sequence > l_action_sequence
and ACT.action_status in ('C', 'S', 'M')
and ACT.payroll_action_id = PACT.payroll_action_id
and PACT.action_type = CLASS.action_type
and CLASS.classification_name = 'SEQUENCED');
select null into dummy
from dual
where not exists
(select null
from pay_action_classifications CLASS,
pay_payroll_actions PACT,
pay_assignment_actions ACT,
pay_object_groups POG_ASG,
pay_object_groups POG_ASG2
where POG_ASG.source_type = 'PAF'
and POG_ASG2.source_type = 'PAF'
and POG_ASG.source_id = p_info.assignment_id
and POG_ASG.parent_object_group_id = POG_ASG2.parent_object_group_id
and POG_ASG2.source_id = ACT.assignment_id
and ACT.action_sequence > l_action_sequence
and ACT.action_status in ('C', 'S')
and ACT.payroll_action_id = PACT.payroll_action_id
and PACT.action_type = CLASS.action_type
and CLASS.classification_name = 'SEQUENCED');
select null into dummy
from dual
where not exists
(select null
from pay_action_classifications CLASS,
pay_payroll_actions PACT,
pay_assignment_actions ACT,
pay_object_groups POG_ASG,
pay_object_groups POG_ASG2
where POG_ASG.source_type = 'PAF'
and POG_ASG2.source_type = 'PAF'
and POG_ASG.source_id = p_info.assignment_id
and POG_ASG.parent_object_group_id = POG_ASG2.parent_object_group_id
and POG_ASG2.source_id = ACT.assignment_id
and ACT.action_sequence > l_action_sequence
and ACT.action_status in ('C', 'S', 'M')
and ACT.payroll_action_id = PACT.payroll_action_id
and PACT.action_type = CLASS.action_type
and CLASS.classification_name = 'SEQUENCED');
select null into dummy
from dual
where not exists
(select null
from pay_action_classifications CLASS,
pay_payroll_actions PACT,
pay_assignment_actions ACT,
per_all_assignments_f ASS,
per_periods_of_service POS
where POS.person_id = l_person_id
and ASS.period_of_service_id = POS.period_of_service_id
and ACT.assignment_id = ASS.assignment_id
and ACT.action_sequence > l_action_sequence
and ACT.action_status in ('C', 'S')
and ACT.payroll_action_id = PACT.payroll_action_id
and PACT.action_type = CLASS.action_type
and CLASS.classification_name = 'SEQUENCED');
select null into dummy
from dual
where not exists
(select null
from pay_action_classifications CLASS,
pay_payroll_actions PACT,
pay_assignment_actions ACT,
per_all_assignments_f ASS,
per_periods_of_service POS
where POS.person_id = l_person_id
and ASS.period_of_service_id = POS.period_of_service_id
and ACT.assignment_id = ASS.assignment_id
and ACT.action_sequence > l_action_sequence
and ACT.action_status in ('C', 'S', 'M')
and ACT.payroll_action_id = PACT.payroll_action_id
and PACT.action_type = CLASS.action_type
and CLASS.classification_name = 'SEQUENCED');
select null
into dummy
from dual
where not exists (select null
from
pay_assignment_actions paac2,
pay_assignment_actions paac,
pay_action_interlocks pai
where pai.locking_action_id = p_info.assact_id
and pai.locked_action_id = paac.assignment_action_id
and paac.pre_payment_id = paac2.pre_payment_id
and paac2.action_sequence > paac.action_sequence);
insert into pay_message_lines (
line_sequence,
payroll_id,
message_level,
source_id,
source_type,
line_text)
values (pay_message_lines_s.nextval,
p_info.payroll_id,
'F', -- it's a fatal message.
p_info.assact_id,
'A', -- assignment action level.
message_text);
select rge.chunk_number,
rge.starting_person_id,
rge.ending_person_id
into range_info
from pay_population_ranges rge
where rge.payroll_action_id = p_info.pact_id
and rge.range_status = 'U'
and rownum < 2
for update of rge.chunk_number;
update pay_population_ranges pop
set pop.range_status = 'P'
where pop.payroll_action_id = p_info.pact_id
and pop.chunk_number = range_info.chunk_number;
select /*+ ORDERED*/ act.assignment_action_id,
asg.period_of_service_id
from per_all_assignments_f asg,
pay_assignment_actions act
where act.payroll_action_id = p_info.pact_id
and asg.assignment_id = act.assignment_id
and act.source_action_id is null
and ((p_info.action_type = 'X'
and asg.effective_start_date = (select max(asg2.effective_start_date)
from per_all_assignments_f asg2
where asg2.assignment_id =
asg.assignment_id)
)
or
((p_info.action_type = 'PRU' or p_info.action_type = 'Z')
and asg.effective_start_date = (select max(asg2.effective_start_date)
from per_all_assignments_f asg2
where asg2.assignment_id =
asg.assignment_id
and asg2.effective_start_date <=
p_info.action_date)
)
or
(p_info.action_type = 'BEE'
and asg.effective_start_date = (select max(ASS2.effective_start_date)
from per_all_assignments_f ASS2,
pay_batch_lines pbl
where ASS2.assignment_id = asg.assignment_id
and pbl.batch_id (+) = p_info.batch_id
and pbl.assignment_id (+) = asg.assignment_id
and pbl.effective_Date (+) between ASS2.effective_start_date
and ASS2.effective_end_date))
or
(p_info.action_type not in ('BEE','Z','X')
and p_info.action_date between
asg.effective_start_date and asg.effective_end_date))
and asg.person_id between
range_info.starting_person and range_info.ending_person
for update of act.action_status,
asg.assignment_id
order by act.action_sequence desc;
select act.assignment_action_id
from pay_assignment_actions act
where act.payroll_action_id = p_info.pact_id
and act.source_action_id is null
and act.object_id between
range_info.starting_person and range_info.ending_person
for update of act.action_status
order by act.action_sequence desc;
select pos.period_of_service_id
from per_periods_of_service pos
where pos.period_of_service_id = c_period_of_service_id
for update of pos.period_of_service_id;
delete from pay_population_ranges range
where range.payroll_action_id = p_info.pact_id
and range.chunk_number = range_info.chunk_number;
select count(*)
into l_complete
from pay_assignment_actions act
where act.payroll_action_id = p_info.pact_id
and act.secondary_status = 'C';
select distinct pac.payroll_action_id
from pay_assignment_actions act2,
pay_action_interlocks pai,
pay_assignment_actions act,
pay_payroll_actions pac,
pay_payroll_actions pac2
where pac2.payroll_action_id = p_info.pact_id
and act2.payroll_action_id = pac2.payroll_action_id
and pac.payroll_action_id = act.payroll_action_id
and pai.locking_action_id = act.assignment_action_id
and pai.locked_action_id = act2.assignment_action_id
and pac.action_status <> 'C'
and pac.action_type in ('A', 'H', 'M', 'PP');
* single threaded mode, we need to insert a range
* row. This allows the processing to have the same
* interface for both multi and single-thread modes.
* NOTE: no date track restriction is required for
* this statement, as we obtaining min and max
* values. This happens to be convenient for Purge.
*/
procedure single_thread_range(p_info in rollback_rec)
is
l_payroll_action_id number;
delete from pay_population_ranges range
where range.payroll_action_id = l_payroll_action_id;
insert into pay_population_ranges (
payroll_action_id,
chunk_number,
starting_person_id,
ending_person_id,
range_status)
select /*+ USE_NL(asg)
INDEX(asg PER_ASSIGNMENTS_F_PK) */
pac.payroll_action_id,
1,
min(asg.person_id),
max(asg.person_id),
'U'
from pay_payroll_actions pac,
pay_assignment_actions act,
per_all_assignments_f asg
where pac.payroll_action_id = l_payroll_action_id
and act.payroll_action_id = pac.payroll_action_id
and asg.assignment_id = act.assignment_id
group by pac.payroll_action_id;
insert into pay_population_ranges (
payroll_action_id,
chunk_number,
starting_person_id,
ending_person_id,
range_status)
select pac.payroll_action_id,
1,
min(act.object_id),
max(act.object_id),
'U'
from pay_payroll_actions pac,
pay_assignment_actions act
where pac.payroll_action_id = l_payroll_action_id
and act.payroll_action_id = pac.payroll_action_id
group by pac.payroll_action_id;
* deleted, otherwise we do not wish to remove the payroll
* action.
*/
function rollback_complete(p_payroll_action_id in number)
return boolean is
dummy number;
select null
into dummy
from sys.dual
where exists (
select null
from pay_assignment_actions act
where act.payroll_action_id = p_payroll_action_id);
select count(*)
into action_count
from pay_assignment_actions act
where act.payroll_action_id = p_info.pact_id
and rownum < (p_info.max_single_undo + 2);
info.retro_purge := legislation_rule(info.legislation_code, 'RETRO_DELETE');
delete from pay_run_balances
where payroll_action_id = info.pact_id;
delete from pay_chunk_status
where payroll_action_id = info.pact_id;
delete from pay_payroll_actions pac
where pac.payroll_action_id = info.pact_id;
select 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 =info.pact_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.date_earned = l_date_earned
where pac.payroll_action_id = info.pact_id;
select act.payroll_action_id, act.source_action_id, act.object_type
into info.pact_id, src_action_id, info.object_type
from pay_assignment_actions act
where act.assignment_action_id = p_assignment_action_id;
info.retro_purge := legislation_rule(info.legislation_code, 'RETRO_DELETE');
select assignment_id
into info.assignment_id
from pay_assignment_actions
where assignment_action_id = info.assact_id;
select max(locked_pact.date_earned), locking_pact.date_earned
into l_date_earned, l_current_date_earned
from pay_payroll_actions locked_pact,
pay_assignment_actions locked,
pay_assignment_actions locking,
pay_action_interlocks locks,
pay_payroll_actions locking_pact
where locking_pact.payroll_action_id =info.pact_id
and locking.payroll_action_id =locking_pact.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
group by locking_pact.date_earned;
update pay_payroll_actions pac
set pac.date_earned = l_date_earned
where pac.payroll_action_id = info.pact_id;
update pay_payroll_actions pac
set pac.date_earned = null
where pac.payroll_action_id = info.pact_id;
delete from pay_run_balances
where payroll_action_id = info.pact_id;