[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
undo_stop_update
val_assact_rollback
val_pact_rollback
validate_rollback_mode
--
Change List
-----------
Date Name Vers Bug No Description
-----------+-------------+-------+----------+-------------------------------+
09-NOV-2012 asnell 115.164 14695457 Backed out changes in .163 by re-instating .162
19-JUL-2012 kskoduri 115.162 13369815 Modified code to allow insertion of dummy quickpay via
ins_dummy_quickpay_action api the code for normal quickpay
is unchanged
12-Jun-2012 vmaripal 115.160 13340377 Modified hint in seqper cursor in
irbaact. Removed ORDERED
08-Jun-2012 ranarra 115.159 14118551 Modified function get_retry_reversal_aciton_id,
fixed a regression in version 115.154 and
reverted the change made in 115.155.
29-May-2012 asnell 115.156 13541539 seqper coded for POG
19-Apr-2012 pparate 115.155 13963282 temporarily fixed masking for 25digit result.
this change would be reverted in next version.
22-Mar-2012 asnell 115.154 13870488 re-instate hints on seqper
10-Nov-2011 pparate 115.153 13359813 For reversal changed masking from 21digits 38decimals to
23digits 36decimals as result value for customer exceeded
21digits before decimal.
16-Jun-2011 vpallapo 115.152 12356133 Performance fix, added hints to force indexes and avoid full table scans
while inserting into pay_assignment_actions.
22-Nov-2010 priupadh 115.151 10212578 Modified maintain_lat_bal now calling pay_core_utils.get_legislation_rule
for leg rule 'BAL_ADJ_LAT_BAL'
22-Apr-2010 phattarg 115.150 9089219 Removed ORDERED hint from cursor albc_selective
in hrassact.del_latest_balances.
21-Apr-2010 phattarg 115.149 8994425 Corrected the behavior of trash_latest_balances procedure
when pap 'LAT_BAL_CHECK_MODE' is set to B..
19-Apr-2010 phattarg 115.148 9558506 Performance fix to 'seqper' cursor in
hrassact.resequence_actions.
03-Jun-2009 priupadh 115.147 8570075 Modified trash_latest_balances,removed "close ivchk"
inside if condition as it was closing twice ivchk.
14-Apr-2009 priupadh 115.146 7652030 Removed checkfile in dbdrv.
14-Apr-2009 priupadh 115.145 7652030 Modified multi_assignment_reversal
now calling create_all_group_balances
after last assignment reversal.
21-Nov-2008 ubhat 115.144 7584883 Divided locking code for
per_all_assignment_f and
per_periods_of_service.
10-Oct-2008 salogana 115.143 7371362 Added bulk delete in
trash_latest_balances FOR
pay_assignment_latest_balances
05-Aug-2008 salogana 115.142 6595092 Added bulk delete code in
trash_latest_balances for
performance improvement.
26-Feb-2008 Ckesanap 115.141 6820127 Added another definition of
ext_man_payment to be called as
a concurrent request for Void and
Reversal enhancement.
19-DEC-2007 AYegappa 115.140 6676706 Performance fix to Balance
Adjustments (added some hints)
05-NOV-2007 alogue 115.139 Performance fix to
trash_latest_balances.
11-JUN-2007 KKawol 115.138 Set the entry path on reversal
run results.
12-FEB-2007 SuSivasu 115.137 Set the time period to be based
on the period earned for the
Balance Adjustment when the
TIME_PERIOD_ID leg rule is set.
16-JAN-2007 nbristow 115.136 Changes for
maintain_balances_for_action
30-NOV-2006 divicker 115.135 Retain old reversal sig order
23-NOV-2006 divicker 115.134 Multi reversal parameter update
10-NOV-2006 alogue 115.133 5410515 Stop balance adjustments or
reversals on assignments with
allready incomplete actions.
09-NOV-2006 divicker 115.132 Further batch reversal changes
03-NOV-2006 nbristow 115.131 Now performing
a distinct for pog joins
03-NOV-2006 divicker 115.130 Revert to 115.128 pending
further check on 115.129
02-NOV-2006 divicker 115.129 Add loop for processing multi
reverse run balances
31-OCT-2006 divicker 115.128 Need correct refactoring of
reversal proc.
30-OCT-2006 divicker 115.126 5616882 Reversal by assignment set func
Bug fix 5410515
19-OCT-2006 alogue 115.125 5612247 Performance fix to POG
resequence statements.
07-AUG-2006 alogue 115.124 5441737 Resequence G object group actions
- Retropay matser assignment actions
have null assignment_id.
02-AUG-2006 alogue 115.123 5416668 Performance fix to albc_selective
in del_latest_balances.
30-JUN-2006 nbristow 115.122 Context length limited to 30
chars
19-JUN-2006 SuSivasu 115.121 Enabled the Balance Date and Time
Definition ID contexts to be stored
in pay_action_contexts.
14-MAR-2006 alogue 115.120 5094068 Fix lb_context_match_check
to correctly spot if context
wasn't in the udca.
07-MAR-2006 alogue 115.119 5082050 Further fix to POG retro
resequencing for assignments
started in the future.
17-FEB-2006 alogue 115.118 Further fix to previous change.
17-FEB-2006 nbristow 115.117 Using the interlock rule G, the
Retro actions were not
correctly resequencing for
terminated employees.
14-FEB-2006 alogue 115.116 5034395 Radix issue in maintain_lat_bal.
04-OCT-2005 alogue 115.115 4644738 Load cxt_id in udca for Reversals
as used by context_match_check.
05-SEP-2005 alogue 115.114 Performance CBO hints in
trash_latest_balances.
27-JUL-2005 nbristow 115.113 Resequence_actions was not
joining to the assignment table
correctly under certain date
track conditions.
08-JUN-2005 alogue 115.112 4372751 Performance fix: hint in seqasg
in resequence_actions.
06-MAY-2005 NBristow 115.111 Added new contexts
LOCAL_UNIT_ID, ORGANIZATION_ID
and SOURCE_NUMBER2.
29-APR-2005 SuSivasu 115.110 Added p_reason parameter for
ext_man_payment.
29-APR-2005 alogue 115.109 4337565 Support of ENABLE_RR_SPARSE
upgrade.
14-APR-2005 alogue 115.108 3465844 Use PAY_ASSIGNMENT_ACTIONS_N51
instead of PAY_ASSIGNMENT_ACTIONS_N1
in index hints.
04-APR-2005 alogue 115.107 Latest Balance Maintenance by
Reversals.
23-FEB-2005 nbristow 115.106 Changes for Period Allocation.
10-DEC-2004 alogue 115.105 g_ba_lat_bal_maintenance global
for BAL_ADJ_LAT_BAL legislation
rule.
25-NOV-2004 nbristow 115.104 Retropay multi assignments
25-OCT-2004 thabara 115.103 3966979 Added p_element_type_id to
del_latest_balances().
Modified maintain_lat_bal().
24-SEP-2004 nbristow 115.102 Changes for process group
actions.
20-SEP-2004 thabara 115.101 3482270 Original Entry ID support for
balance adjustments.
Modified set_action_context(),
bal_adjust_actions() and
maintain_lat_bal().
16-SEP-2004 alogue 115.100 3863038 More Performance fixes in
del_latest_balances.
09-AUG-2004 tbattoo 115.99 3724695 Support for reversals and retropay
08-JUL-2004 nbristow 115.98 Changes for Sparse Matrix JC.
30-APR-2004 alogue 115.97 More Performance fixes in
del_latest_balances.
28-APR-2004 thabara 115.96 Modified irbaact not to call
get_tax_unit when taxunt is set
for action type B and I.
27-APR-2004 alogue 115.95 Performance fix to albc_selective
in del_latest_balances.
09-Mar-2004 swinton 115.94 Enhancement 3368211 -
Amended Trash_Quickpay() to
support new QuickPay Exclusions
model.
02-FEB-2004 nbristow 115.93 Reversal was not correctly
setting the run balances, when
the run is prior to the
balance validation date.
06-JAN-2004 alogue 115.92 3354185 Avoid execution of
del_latest_balances() for balance
initialisations.
16-DEC-2003 tbattoo 115.91 Fix to mantain latest balances
11-DEC-2003 tbattoo 115.90 Fix to mantain latest balances
09-DEC-2003 nbristow 115.89 Changes to maintain
pay_latest_balances.
24-NOV-2003 alogue 115.88 3262314 Performance enhancement to
previous change.
24-NOV-2003 alogue 115.87 3262314 Performance enhancement to
previous change.
21-NOV-2003 alogue 115.86 3262314 Avoid corruption of latest
balance value by balance
adjustments.
13-NOV-2003 nbristow 115.85 Reversal was not correctly
setting the jurisdiction code
in sparse mode.
04-NOV-2003 alogue 115.84 3176709 Use of per_business_groups_perf,
per_all_people_f and
pay_all_payrolls_f. Performance
fix to seqper cursors.
03-NOV-2003 tbattoo 115.83 support for sparse matrix and
pay_latest_balances table
17-OCT-2003 alogue 115.82 Tuned get_rr_values cursor
in set_action_context.
14-OCT-2003 alogue 115.81 3166638 Performance fix to quickpay
assignment action creation.
05-SEP-2003 alogue 115.80 3130030 Performance fix to plbc_selective
in del_latest_balances.
05-SEP-2003 alogue 115.79 3130030 Performance fix to trash_quickpay.
03-SEP-2003 thabara 115.78 Correction of 115.77.
03-SEP-2003 thabara 115.77 3105028 Modified set_action_context not
to set defaults to dynamic
contexts for Balance Upload.
Modified the process order of
create_all_asg/group_balances
in bal_adjust_actions to call
them after setting contexts.
03-SEP-2003 nbristow 115.76 RETRO_DELETE is now a
legislation rule.
29-AUG-2003 nbristow 115.75 Action sequence on
pay_run_balances was not being
updated correctly.
53-JUN-2003 alogue 115.74 2960902 New trash_latest_balances
only passed balance_type_id
and trash_date. Overloads
original version.
12-MAY-2003 alogue 115.73 2911448 Avoid locking issues in Batch
Balance Adjustments by only
updating action_population_status
in inassact_main if need to.
11-MAR-2004 rthirlby 115.72 2822429 Altered reversal, so that run
result values for SOURCE_IV
and SOURCE_NUMBER input values
are not negated. These rrvs will
be ids/numbers that should not be
reversed.
05-MAR-2003 sdhole 115.71 2805195 Added parameter tax_unit_id
with default value null to
bal_adjust procedure.
07-FEB-2003 nbristow 115.70 Reversal was not correctly
working for the new contexts.
05-FEB-2003 nbristow 115.69 Further new context changes.
05-FEB-2003 nbristow 115.68 Added contexts for
source_number and source_text2
24-JAN-2003 alogue 115.67 Reverse bug 2453546 changes.
20-JAN-2003 alogue 115.66 2758499 Fixed trash_latest_balances
to handle latest balances
earlier than trash_date.
14-JAN-2003 nbristow 115.65 Now Jurisdiction input value
can have any name.
10-JAN-2003 alogue 115.64 2266326 Use of CHECK_LATEST_BALANCES,
CHECK_RRVS_FIRST and
CHECK_LAT_BALS_FIRST package
globals to tune behaviour of
trash_latest_balances.
09-JAN-2003 alogue 115.63 2692195 Use of hr_utility.debug_enabled
for performance of trace
statements.
06-DEC-2002 alogue 115.62 Subtle-paranoid fix to
bal_adjust_actions to avoid
possible issue with NOCOPY
changes.
03-DEC-2002 scchakra 115.61 2613838 Added overloaded procedure
get_default_leg_value to return
the default run type for a
legislation. Included
NOCOPY Performance Changes.
15-NOV-2002 alogue 115.60 2667222 Re-implement 2492007 cartesian
join (in a different way!).
07-NOV-2002 alogue 115.59 2453546 Optimise performance of seqper
cursor in inassact be breaking
into 2.
24-OCT-2002 alogue 115.57 2641336 Optimise performance for
statements in del_latest_balances.
16-OCT-2002 alogue 115.56 2581887 Optimise performance for
Balance Initialisation.
24-SEP-2002 alogue 115.55 2587443 Further enhanced Reversals so
handle result values with up
to 38 decimal places.
19-SEP-2002 nbristow 115.54 Changes in 115.50 introduced
a bug in Retropay, reversed
the change to run_results
cursor.
09-SEP-2002 alogue 115.53 2529691 Performance fix to
resequence_actions.
09-AUG-2002 alogue 115.52 Performance fix to element
entries statement in
do_assact_rollback that was
using FTS.
09-AUG-2002 alogue 115.51 2362454 Enhance Reversals so handle
result values with up to 35
decimal places. Previously
there was a restriction of up
to 20 dcs within the fnd_number
code.
01-AUG-2002 dsaxby 115.50 2492007 Prevent ora-01403
no data found from within
bal_adjust_actions.
Also, fix select of run results
to reverse to remove apparent
cartesian join.
21-JUN-2002 alogue 115.49 Fix CA TAX_GROUP value fetch
in udca.
30-APR-2002 RThirlby 115.48 Support for pay_run_balances -
balance reporting architecture,
for reversals and balance
adjustments.
26-APR-2002 alogue 115.47 2346351 Support of payroll_id in udca
for balances that use payroll_id
context.
09-APR-2002 nbristow 115.46 Added get_default_leg_rule to
allow the defaulting of run
type.
03-APR-2002 tbattoo 115.45 added p_run_type_id parameter
18-DEC-2001 dsaxby 115.44 GSCC standards fix.
26-NOV-2001 dsaxby 115.43 1682940 Changes for Purge.
Do not shuffle assignment
actions in purge mode and allow
insert of upload action before
a Purge.
Added commit at end of file.
Added purge_mode parameter to
bal_adjust_actions.
Added dbdrv line.
13-NOV-2001 jtomkins 115.42 Added prepay_flag parameter to
bal_adjust and bal_adjust_actions
01-NOV-2001 nbristow 115.41 Set Action Context exist
statement now in the correct
format.
29-OCT-2001 nbristow 115.40 Set Action Context changed to
check that a row does not
already exist in the action
context table.
11-OCT-2001 nbristow 115.39 Added hints to statements
in rev_pre_inserted_rr.
04-SEP-2001 nbristow 115.38 Added the resequence_chunk
procedure.
12-JUL-2001 kkawol 115.37 Change to inassact_main. If
rule_type 'I' does not exist for
the legislation, we default rule
mode to N. Bug 1337853.
25-JUN-2001 alogue 115.36 Performance fix to balance
adjustment latest balance
maintenance.
25-JUN-2001 alogue 115.35 Added CBO hints
22-JUN-2001 alogue 115.34 Added CBO hints
12-JUN-2001 nbristow 115.33 Initialising retro_purge.
04-JUN-2001 kkawol 115.32 Added changes required for
Quickpay Prepay to work with
master and sub actions (iter eng).
Changed qpppassact and inassact.
29-MAY-2001 nbristow 115.31 Changes to Retropay so that
the RRs are not deleted.
08-MAY-2001 alogue 115.30 1763446 Added CBO hints
24-APR-2001 mreid 115.29 1518951 Added CBO hints
06-APR-2001 alogue 115.28 Changes for source text iv
context.
26-JAN-2001 alogue 115.26 1614003 Balance Adjustment lat bal
maintenance handle multiple
feeds by same adjustment.
16-JAN-2001 alogue 115.26 Handle null result values in
balance adjustment latest
balance maintenance.
10-JAN-2001 alogue 115.25 1571313 Handle -9999 balance values in
balance adjustment latest
balance maintenance.
26-NOV-2000 nbristow 115.24 Changes for source text context
21-NOV-2000 alogue 115.23 887061 Skipped terminated assignments
support.
02-OCT-2000 alogue 115.22 1421447 Avoid a PLS-00365 in latest
balance maintenance inm balance
adjustments.
29-SEP-2000 nbristow 115.21 Now passing tax unit id to
balance adjustments.
18-SEP-2000 nbristow 115.20 Maintenance of latest balances
was not taking into account
null rrv.
14-SEP-2000 NBRISTOW 115.19 Changes to trash_latest_balances
to remove full pl/sql feed
checking balances.
30-AUG-2000 ALOGUE 115.17 Deletion of latest balances
by balance adjustment if
leg rule for lat bal maintenance
is not defined.
04-AUG-2000 ALOGUE 115.16 Maintenance of latest balances
within balance adjustments.
19-MAY-2000 nbristow 115.15 Added procedures to resequence
sequenced actions.
22-FEB-2000 dsaxby 115.14 #1168142 Remove the need for a period of
service row to exist when
processing a balance adjustment
action. This is for OAB.
13-JAN-2000 alogue 115.13 Ensure that error_messages
inserted into pay_message_lines
are at max 240 in length.
16-NOV-1999 nbristow 115.12 Now reversals and balance
adjustments populate
pay_action_contexts.
26-JUL-1999 ALOGUE 115.11 Enhancement in Reversal to
get reversals results in same
order as run being reversed.
Optimises behaviour of the
retrocosting of a reversal.
02-JUN-1999 ALOGUE 115.10 Fix in bal_adjust_actions to
ensure only update this balance
adjustments run results.
22-APR-1999 ALOGUE 115.8 Changed rollback_payroll_action
12-APR-1999 ALOGUE 115.7 Fix to reversals to support
canonical numbers.
07-APR-1999 SDOSHI 115.6 Flexible Dates Conversion
04-JAN-1999 NBRISTOW 110.8 Changed rollback_payroll_action
to use the rollback package.
04-SEP-1998 KKAWOL 40.62 #721925 Reversals run results: select
actual status instead of
forcing it to be 'P'.
27-NOV-1997 MFENDER 110.3 #589767 Modified inassact to populate
tax_unit_id for quickpay
prepayments.
05-SEP-1997 KKAWOL 40.61 #547578 Period dates fix. Set the
date_earned column as
appropriate on the
pay_payroll_actions table.
19-JUN-1997 ALOGUE 40.60 #507602 Reversals run results: source_id
= run_result_id of the run
result being reversed.
04-APR-1997 NBRISTOW 40.59 #473685 Segment1 was not being decoded
for US legislation to convert
it to a number.
01-APR-1997 ALOGUE 40.58 Fixed previous change.
27-MAR-1997 ALOGUE 40.57 US reversal GRE Fix #459662
15-JAN-1996 NBRISTOW 40.56 EOY performance fix for W2.
13-JAN-1996 NBRISTOW 40.55 Reverse Backport.
13-JAN-1996 NBRISTOW 40.54 Backport EOY performance fix.
23-DEC-1996 NBRISTOW 40.53 Uncommented exit.
09-DEC-1996 SSINHA 40.52 Fixed previous change
05-DEC-1996 NBRISTOW 40.51 Fixed previous change.
29-NOV-1996 DSAXBY 40.50 #366215 New Reversal functionality.
20-NOV-1996 NBRISTOW 40.49 Now passing a flag to inassact
to indicate that the assignment
needs to be locked.
18-JUN-1996 NBRISTOW 40.48 #374931 Now when a balance adjustment
is performed only the latest
balances feed by the adjustment
are deleted.
14-JUN-1996 DSAXBY 40.47 #374389 When reversing an indirect
result, set source_type of new
result to 'V' (required for
Costing).
08-MAY-1996 NBRISTOW 40.46 #359005 Performance problem in
del_latest_balances, now using
a cursor to delete context
values.
22-APR-1996 DSAXBY 40.45 #360386 Change joins to the
per_business_groups view.
13-APR-1996 DKERR 40.44 Modified inassact for external
manual payments to allow
the insert of an assignment
action where a payment action
have been voided.
14-MAR-1996 DSAXBY 40.43 Make 'X' actions alter action
sequence where necessary.
27-FEB-1996 DSAXBY 40.42 Added support for 'X' actions.
17-JAN-1996 NBRISTOW 40.41 #335099 Altered sql statements in
inassact to improve performance
of US Check report. US Check
Report is using balance
user exit thus calling inassact.
10-JAN-1996 DSAXBY 40.40 #333428 Changed trash_latest_balances
procedure to avoid trashing
balances un-necessarily. This
required the addition of a new
parameter.
11-DEC-1995 NBRISTOW 40.39 Changed inassact to insert the
Tax Unit Id for quick pay
actions.
10-NOV-1995 NBRISTOW 40.38 Changed name of bal_adjust to
bal_adjust_actions, added extra
out arguments. Created new
procedure bal_adjust for
existing bal_adjust calls.
06-NOV-1995 NBRISTOW 40.37 Tax Unit Id now placed on the
assignment_action for balance
adjustments and resversals.
Also the jurisdiction is placed
on the run result.
13-SEP-1995 DSAXBY 40.35 #307123 New parameter to reversal
procedure, indicating that we do
not need to insert assact and
interlock. Introduced for
backpay.
16-AUG-1995 DSAXBY 40.34 #301528 Removed unnecessary check from
ensure_assact_rolled_back
procedure.
25-JUL-1995 AMILLS 40.33 Amended selection statement
substituting clause :-
'HR_6075_ELE_ENTRY_REC_EXIST'
with the following 2 hard
coded error messages:
'HR_7699_ELE_ENTRY_REC_EXISTS'
'HR_7700_ELE_ENTRY_REC_EXISTS'
10-JUL-1995 DSAXBY 40.32 #292828 Set date_earned for bal adjust.
05-JUL-1995 NBRISTOW 40.31 Added intial balance load
payroll action type.
19-APR-1995 DSAXBY 40.30 #277088 : trash_latest_balances
now deletes latest balances
correctly again!
18-APR-1995 DSAXBY 40.29 Removed 'nowait' statement to
avoid immediate failure on lock
when called as part of w2
report work.
04-APR-1995 DSAXBY 40.28 Reverse 'H_HHMM' uom.
31-MAR-1995 DSAXBY 40.27 Now insert assignment action
with status = 'U' for a non
tracked action type ('N').
24-MAR-1995 DSAXBY 40.26 Now call undo_stop_update
procedure instead of hr_ent_api
delete_element_entry.
13-FEB-1995 DSAXBY 40.25 Fix problem causing 7010 error
when inserting quickpay assact.
31-JAN-1995 DSAXBY 40.24 Must not trash latest balances
for an action type of 'N'.
16-DEC-1994 DSAXBY 40.23 Added qpppassact.
Delete pre_payment rows for
QuickPay Pre-Payment process.
25-NOV-1994 DSAXBY 40.22 Change in params to qpassact.
26-OCT-1994 DSAXBY 40.21 Delete from the
pay_balance_context_values
table where necessary.
Set time_period_id for
balance adjustments.
Created public versions of
validate_pact_rollback and
validate_assact_rollback
procedures. (To be called from
forms).
Prevent mark for retry for
Balance Adjustment. Note this
check has been moved
val_pact_rollback procedure.
Insert value for the
time_period_id column when
processing balance adjustment.
Alter rules and strategy for
checking rules for rolling back
and marking for retry assignment
and payroll actions.
Disabled all uses of the
business_group_id index.
05-OCT-1994 DKERR 40.20 Set the OBJECT_VERSION_NUMBER
to 1 for inserts into
PAY_PAYROLL_ACTIONS and
PAY_ASSIGNMENT_ACTIONS for
all action types.
28-JUN-1994 DSAXBY 40.19 Added line to decode statement
in reversal routine, to
prevent invalid number errors.
Delete from pay_costs and
pay_quickpay_inclusions when
we roll back.
Do not attempt to delete from
pay_pre_payments, unless we are
rolling back a Pre-Payment!
21-JUN-1994 CSWAN 40.18 For reversal RRVs, flip the
sign of the RRVs being reversed,
rather than prepending a '-'
character, which leads to
balance errors.
28-MAR-1994 DSAXBY 40.17 G622 Set updating_action_id to null
when rolling back update rec
entries which were corrections.
18-MAR-1994 DSAXBY 40.16 G172 Improved messaging for rollback
assignment action. Added message
for rollback payroll action.
28-FEB-1994 DSAXBY 40.15 G585 Allow nested mark for retry.
Prevent marking payment action
types for retry.
05-JAN-1994 DSAXBY 40.14 G481 Only process NEE when reversing
effective of stop and update
rules.
20-DEC-1993 DSAXBY 40.13 G454 Delete pre-payment rows.
G283/ Ensure interlock rows are
G272 deleted if rollback assact is
called from form.
---- Altered DELETE_NEXT_CHANGE to
FUTURE_CHANGE for rollback of
REE update feature.
17-DEC-1993 DSAXBY 40.12 G277 Updated incorrect comments.
14-DEC-1993 DSAXBY 40.11 G277 Rolling back of Update and
stop REE rules.
pay_element_entry_values table).
13-DEC-1993 CSWAN 40.10 G410 Removed reference to removed
PLANNED_PAYMENT_DATE column.
13-DEC-1993 AFRITH 40.9 G396 Unsequenced assignment actions
not interlocked by sequenced
actions .
09-DEC-1993 DSAXBY 40.8 G320 Handle ND unit of measure.
06-DEC-1993 DSAXBY 40.7 G296 Prevent looping on rollback or
mark for retry when assignment
actions are not to be
(correctly) rolled back or
marked for retry.
12-NOV-1993 DSAXBY 40.6 G36 Handled non tracked action
properly again.
29-OCT-1993 RPATEL 40.5 Added functionality for
Manual/External Payments,
error handling invalid action.
26-OCT-1993 DSAXBY 40.4 Added some missing error mesg.
Prevent Reversal of something
that is already reversed.
Trash latest balances for Rev
and Balance Adjustment. (Needed
to add del_latest_balances).
20-OCT-1993 AFRITH 40.3 Added BACKPAY mode.
19-OCT-1993 DSAXBY 40.2 Altered bal_adjust. No longer
a function, now procedure and
updates creator_id.
Fixed payroll action roll back.
13-AUG-1993 DSAXBY 40.1 Fix interlock delete.
28-JUL-1993 DSAXBY 30.8 Work on reversal and bal_adjust
20-JUL-1993 DSAXBY 30.7 Added bal_adjust.
24-FEB-1999 J. Moyano 115.5 MLS Changes. Reference to
pay_payment_types_tl included in
procedure do_pact_rollback.
11-SEP-2000 divicker 115.18 Performance changes
-----------+-------------+-------+----------+-------------------------------+
*/
--
--
/*-------------------------- RECORD types ---------------------------*/
--
type context_cache_type is record
(
cxt_id number_tbl,
cxt_name varchar_60_tbl,
sz number
);
select null from dual
where exists
(select null -- check for any RRs
from pay_run_results
where assignment_action_id = p_assact_id)
or exists
(select null -- check for any MESSAGEs
from pay_message_lines
where source_type = 'A'
and source_id = p_assact_id)
-- or exists -- check for any latest balances
-- (select null
-- from pay_assignment_latest_balances
-- where assignment_action_id = p_assact_id)
-- or exists -- check for any latest balances
-- (select null
-- from pay_person_latest_balances
-- where assignment_action_id = p_assact_id)
-- or exists -- check for any latest balances
-- (select null
-- from pay_latest_balances
-- where assignment_action_id = p_assact_id)
or exists -- check for updates to REEs
(select null
from pay_element_entries_f
where updating_action_id = p_assact_id);
select null from dual
where exists
(select null -- check for any RRs
from pay_run_results
where assignment_action_id = p_assact_id
and status <> 'B')
or exists
(select null -- check for any MESSAGEs
from pay_message_lines
where source_type = 'A'
and source_id = p_assact_id)
-- or exists -- check for any latest balances
-- (select null
-- from pay_assignment_latest_balances
-- where assignment_action_id = p_assact_id)
-- or exists -- check for any latest balances
-- (select null
-- from pay_person_latest_balances
-- where assignment_action_id = p_assact_id)
-- or exists -- check for any latest balances
-- (select null
-- from pay_latest_balances
-- where assignment_action_id = p_assact_id)
or exists -- check for updates to REEs
(select null
from pay_element_entries_f
where updating_action_id = p_assact_id);
select null
from dual
where exists
(select null -- check for any ASSACTs
from pay_assignment_actions
where payroll_action_id = p_pact_id)
or exists
(select null -- check for any MESSAGEs
from pay_message_lines
where source_type = 'P'
and source_id = p_pact_id);
select ACT.assignment_id,
ACT.action_sequence,
ACT.action_status,
ASS.payroll_id,
POS.person_id,
substr(PEO.full_name,1,80),
ASS.assignment_number
into p_assact_rec.assignment_id,
l_action_sequence,
l_action_status,
p_assact_rec.payroll_id,
l_person_id,
p_assact_rec.full_name,
p_assact_rec.assignment_number
from per_periods_of_service POS,
per_all_assignments_f ASS,
per_all_people_f PEO,
pay_assignment_actions ACT
where ACT.assignment_action_id = p_assact_rec.assact_id
and ASS.assignment_id = ACT.assignment_id
and p_pact_rec.action_date between
ASS.effective_start_date and ASS.effective_end_date
and PEO.person_id = ASS.person_id
and p_pact_rec.action_date between
PEO.effective_start_date and PEO.effective_end_date
and POS.period_of_service_id = ASS.period_of_service_id;
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_assact_rec.assact_id
and act.assignment_action_id = int.locking_action_id
and 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_assact_rec.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,
pay_action_classifications CLAS2
where ACT.assignment_id = p_assact_rec.assignment_id
and ACT.action_sequence > l_action_sequence
and ACT.action_status <> 'M'
and ACT.payroll_action_id = PACT.payroll_action_id
and PACT.action_type = CLASS.action_type
and CLASS.classification_name = 'SEQUENCED'
and CLAS2.action_type = p_pact_rec.action_type
and CLAS2.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,
pay_action_classifications CLAS2
where ACT.assignment_id = p_assact_rec.assignment_id
and ACT.action_sequence > l_action_sequence
and ACT.payroll_action_id = PACT.payroll_action_id
and PACT.action_type = CLASS.action_type
and CLASS.classification_name = 'SEQUENCED'
and CLAS2.action_type = p_pact_rec.action_type
and CLAS2.classification_name = 'SEQUENCED' );
select null into dummy
from dual
where not exists
(select null
from pay_action_classifications CLASS,
pay_action_classifications CLAS2,
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 <> 'M'
and ACT.payroll_action_id = PACT.payroll_action_id
and PACT.action_type = CLASS.action_type
and CLASS.classification_name = 'SEQUENCED'
and CLAS2.action_type = p_pact_rec.action_type
and CLAS2.classification_name = 'SEQUENCED' );
select null into dummy
from dual
where not exists
(select null
from pay_action_classifications CLASS,
pay_action_classifications CLAS2,
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.payroll_action_id = PACT.payroll_action_id
and PACT.action_type = CLASS.action_type
and CLASS.classification_name = 'SEQUENCED'
and CLAS2.action_type = p_pact_rec.action_type
and CLAS2.classification_name = 'SEQUENCED' );
select pac.business_group_id,
pac.effective_date,
hrl.meaning,
pac.action_type,
trunc(sysdate),
pay.payroll_name,
grp.name
into l_business_group_id,
p_pact_rec.action_date,
p_pact_rec.action_name,
p_pact_rec.action_type,
p_pact_rec.current_date,
p_pact_rec.payroll_name,
p_pact_rec.bg_name
from pay_payroll_actions pac,
pay_all_payrolls_f pay,
per_business_groups_perf grp,
hr_lookups hrl
where pac.payroll_action_id = p_pact_rec.pact_id
and hrl.lookup_code = pac.action_type
and hrl.lookup_type = 'ACTION_TYPE'
and grp.business_group_id = pac.business_group_id
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 dual
where not exists
(select null
from pay_action_classifications
where action_type = p_pact_rec.action_type
and classification_name = 'NONREMOVEABLE');
select null
into dummy
from pay_action_classifications CLASS
where CLASS.action_type = p_pact_rec.action_type
and CLASS.classification_name = 'SEQUENCED';
* This procedure removes any entries inserted for a Quickpay action
*/
procedure trash_quickpay (p_action_id number) is
cursor c1 is
select pee.element_entry_id
from pay_element_entries_f pee,
pay_assignment_actions paa
where pee.creator_type = 'Q'
and pee.creator_id = paa.assignment_action_id
and pee.assignment_id = paa.assignment_id
and paa.assignment_action_id = p_action_id
for update of pee.element_entry_id;
delete from pay_quickpay_exclusions
where assignment_action_id = p_action_id;
delete from pay_quickpay_inclusions
where assignment_action_id = p_action_id;
delete from pay_run_result_values RRV
where RRV.run_result_id in
(select RR.run_result_id
from pay_run_results RR
where RR.source_type = 'E'
and RR.source_id = c1rec.element_entry_id);
delete from pay_run_results RR
where RR.source_type = 'E'
and RR.source_id = c1rec.element_entry_id;
delete from pay_element_entry_values_f EEV
where EEV.element_entry_id = c1rec.element_entry_id;
delete from pay_element_entries_f
where current of c1;
/*------------------------ 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 constant varchar2(30) := 'pyassact.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;
select null
into dummy
from pay_element_entries_f ee
where ee.entry_type = 'E'
and ee.element_entry_id <> p_ee_id
and ee.assignment_id = asg_id
and ee.element_link_id = el_id
and (ee.effective_start_date <= val_end and
ee.effective_end_date >= val_start);
(p_mode = 'DELETE_NEXT_CHANGE' and
val_end = c_eot)
) then
effend := val_end;
if(p_mode = 'DELETE_NEXT_CHANGE') then
if g_debug 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;
update pay_element_entries_f ee
set ee.effective_end_date = decode(val_end, c_eot, effend, val_end)
where ee.element_entry_id = p_ee_id
and ee.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;
update pay_element_entries_f ee
set ee.effective_end_date = effend
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_end_date between val_start and val_end)
or (eev.effective_start_date between val_start and val_end));
update pay_element_entry_values_f eev
set eev.effective_end_date = val_end
where eev.element_entry_id = p_ee_id
and p_date between
eev.effective_start_date and eev.effective_end_date;
end undo_stop_update;
* Any deletes of child records which should only be performed for
* rollback, rather than for both retry and rollback, are performed
* either via cascading constraints or via the delete trigger on
* assignment actions.
*
*/
procedure do_assact_rollback (p_pact_rec in pact_details,
p_assact_rec in assact_details,
p_rollback_mode in varchar2,
p_leave_base_table_row in boolean) is
begin
--
if p_pact_rec.sequenced_flag then
--
-- Delete any Run Results which were created by this action. This
-- will have no effect for unsequenced actions such as
-- Pre-Payments. For efficiency there should be no cascade
-- trigger on Run Results so we need to trash the values as well.
if g_debug then
hr_utility.set_location('hrassact.do_assact_rollback',10);
delete from pay_run_result_values RRV
where RRV.run_result_id in
(select RR.run_result_id
from pay_run_results RR
where RR.assignment_action_id = p_assact_rec.assact_id);
delete from pay_run_results RR
where RR.assignment_action_id = p_assact_rec.assact_id;
delete from pay_balance_context_values VAL
where exists (
select null
from pay_person_latest_balances PLB
where PLB.assignment_action_id = p_assact_rec.assact_id
and VAL.latest_balance_id = PLB.latest_balance_id);
delete from pay_person_latest_balances PLB
where PLB.assignment_action_id = p_assact_rec.assact_id;
delete from pay_balance_context_values VAL
where exists (
select null
from pay_assignment_latest_balances ALB
where ALB.assignment_action_id = p_assact_rec.assact_id
and VAL.latest_balance_id = ALB.latest_balance_id);
delete from pay_assignment_latest_balances ALB
where ALB.assignment_action_id = p_assact_rec.assact_id;
select pee.element_entry_id
into v_eeid
from pay_element_entries_f pee
where pee.creator_id = p_assact_rec.assact_id
and pee.assignment_id = p_assact_rec.assignment_id
and p_pact_rec.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 = v_eeid
and p_pact_rec.action_date between
pev.effective_start_date and pev.effective_end_date;
delete from pay_element_entries_f pee
where pee.element_entry_id = v_eeid
and p_pact_rec.action_date between
pee.effective_start_date and pee.effective_end_date;
select pet.multiple_entries_allowed_flag,
pee.element_entry_id,
pee.updating_action_id,
pee.effective_start_date,
pee.effective_end_date
from pay_element_types_f pet,
pay_element_links_f pel,
pay_element_entries_f pee
where pee.assignment_id = p_assact_rec.assignment_id
and pee.entry_type = 'E'
and p_pact_rec.action_date between
pee.effective_start_date and pee.effective_end_date
and pel.element_link_id = pee.element_link_id
and p_pact_rec.action_date between
pel.effective_start_date and pel.effective_end_date
and pet.element_type_id = pel.element_type_id
and p_pact_rec.action_date between
pel.effective_start_date and pel.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 = c1rec.element_entry_id;
undo_stop_update (c1rec.element_entry_id,
c1rec.multiple_entries_allowed_flag,
p_pact_rec.action_date,
'FUTURE_CHANGE');
select max(pee.effective_end_date)
into v_max_date
from pay_element_entries_f pee
where pee.element_entry_id = c1rec.element_entry_id
and pee.effective_end_date = (p_pact_rec.action_date - 1);
undo_stop_update (c1rec.element_entry_id,
c1rec.multiple_entries_allowed_flag,
(p_pact_rec.action_date - 1),
'DELETE_NEXT_CHANGE');
update pay_element_entries_f pee
set pee.updating_action_id = null
where pee.element_entry_id = c1rec.element_entry_id
and p_pact_rec.action_date between
pee.effective_start_date and pee.effective_end_date;
delete from pay_message_lines ML
where ML.source_type = 'A'
and ML.source_id = p_assact_rec.assact_id;
delete from pay_pre_payments ppp
where ppp.assignment_action_id = p_assact_rec.assact_id;
delete from pay_costs
where assignment_action_id = p_assact_rec.assact_id;
insert into pay_message_lines
(line_sequence,
payroll_id,
message_level,
source_id,
source_type,
line_text
)
values
(pay_message_lines_s.nextval,
p_assact_rec.payroll_id,
'I', -- Information level
p_pact_rec.pact_id,
'P',
mesg_text
);
update pay_assignment_actions
set action_status = 'M'
where assignment_action_id = p_assact_rec.assact_id;
update pay_assignment_actions
set action_status = 'B'
where assignment_action_id = p_assact_rec.assact_id;
delete from pay_action_interlocks lck
where lck.locking_action_id = p_assact_rec.assact_id;
delete from pay_assignment_actions
where assignment_action_id = p_assact_rec.assact_id;
delete from pay_action_interlocks lck
where lck.locking_action_id = p_assact_rec.assact_id;
delete from pay_population_ranges ppr
where ppr.payroll_action_id = p_payroll_action_id;
select max(act.action_sequence) + 1
into l_cur_aseq
from pay_assignment_actions act
where act.payroll_action_id = p_payroll_action_id;
select act.assignment_action_id,
act.action_sequence
from pay_assignment_actions act
where act.payroll_action_id = p_payroll_action_id
and act.action_sequence < l_cur_aseq
order by act.action_sequence desc
for update of act.action_status;
delete from pay_message_lines ML
where ML.source_type = 'P'
and ML.source_id = p_payroll_action_id;
select ppt_tl.payment_type_name
into l_pact_rec.action_name
from pay_payroll_actions pac,
pay_payment_types_tl ppt_tl,
pay_payment_types ppt
where pac.payroll_action_id = l_pact_rec.pact_id
and ppt.payment_type_id = pac.payment_type_id
and ppt_tl.payment_type_id = ppt.payment_type_id
and userenv('LANG') = ppt_tl.language;
insert into pay_message_lines (
line_sequence,
payroll_id,
message_level,
source_id,
source_type,
line_text)
select pay_message_lines_s.nextval,
pac.payroll_id,
'I', -- information.
pac.business_group_id,
'B',
mesg_text
from pay_payroll_actions pac
where pac.payroll_action_id = l_pact_rec.pact_id;
delete from pay_payroll_actions
where payroll_action_id = p_payroll_action_id;
select payroll_action_id
from pay_assignment_actions
where assignment_action_id = p_assignment_action_id
for update of action_status;
select /*+ ORDERED INDEX (PLB PAY_PERSON_LATEST_BALANCES_FK1)
USE_NL (PLB) */
plb.latest_balance_id
from pay_defined_balances pdb,
pay_person_latest_balances plb
where pdb.balance_type_id = l_balance_type_id
and plb.defined_balance_id = pdb.defined_balance_id
and exists (
select null
from pay_run_results prr,
pay_run_result_values rrv
where rrv.input_value_id = l_input_value_id
and prr.run_result_id = rrv.run_result_id
and prr.status in ('P', 'PA')
and nvl(rrv.result_value, '0') <> '0');
select
lb.latest_balance_id
from pay_defined_balances pdb,
pay_latest_balances lb
where pdb.balance_type_id = l_balance_type_id
and lb.defined_balance_id = pdb.defined_balance_id
and exists (
select null
from pay_run_results prr,
pay_run_result_values rrv
where rrv.input_value_id = l_input_value_id
and prr.run_result_id = rrv.run_result_id
and prr.status in ('P', 'PA')
and nvl(rrv.result_value, '0') <> '0');
select /*+ ORDERED INDEX (PLB PAY_ASSIGNMENT_LATEST_BALA_FK2)
USE_NL (PLB) */
plb.latest_balance_id
from pay_defined_balances pdb,
pay_assignment_latest_balances plb
where pdb.balance_type_id = l_balance_type_id
and plb.defined_balance_id = pdb.defined_balance_id
and exists (
select null
from pay_run_results prr,
pay_run_result_values rrv
where rrv.input_value_id = l_input_value_id
and prr.run_result_id = rrv.run_result_id
and prr.status in ('P', 'PA')
and nvl(rrv.result_value, '0') <> '0');
select /*+ ORDERED INDEX (PLB PAY_PERSON_LATEST_BALANCES_FK1)
USE_NL (PLB) */
plb.latest_balance_id
from pay_defined_balances pdb,
pay_person_latest_balances plb
where pdb.balance_type_id = l_balance_type_id
and plb.defined_balance_id = pdb.defined_balance_id;
select /*+ ORDERED INDEX (PLB PAY_ASSIGNMENT_LATEST_BALA_FK2)
USE_NL (PLB) */
plb.latest_balance_id
from pay_defined_balances pdb,
pay_assignment_latest_balances plb
where pdb.balance_type_id = l_balance_type_id
and plb.defined_balance_id = pdb.defined_balance_id;
select /*+ ORDERED INDEX (PLB PAY_LATEST_BALANCES_FK1)
USE_NL (PLB) */
plb.latest_balance_id
from pay_defined_balances pdb,
pay_latest_balances plb
where pdb.balance_type_id = l_balance_type_id
and plb.defined_balance_id = pdb.defined_balance_id;
select '1' from dual
where exists (select 1
from pay_run_results prr,
pay_run_result_values rrv
where rrv.input_value_id = l_input_value_id
and prr.run_result_id = rrv.run_result_id
and prr.status in ('P', 'PA')
and nvl(rrv.result_value, '0') <> '0');
select alb.latest_balance_id
from pay_assignment_latest_balances alb,
pay_defined_balances pdb,
pay_balance_dimensions pbd
where pdb.balance_type_id = l_balance_type_id
and pdb.defined_balance_id = alb.defined_balance_id
and pdb.balance_dimension_id = pbd.balance_dimension_id
and pbd.feed_checking_type = 'F';
select plb.latest_balance_id,
'P' balance_type
from pay_person_latest_balances plb,
pay_defined_balances pdb,
pay_balance_dimensions pbd
where pdb.balance_type_id = l_balance_type_id
and pdb.defined_balance_id = plb.defined_balance_id
and pdb.balance_dimension_id = pbd.balance_dimension_id
and pbd.feed_checking_type = 'F'
union
select plb.latest_balance_id,
'B' balance_type
from pay_latest_balances plb,
pay_defined_balances pdb,
pay_balance_dimensions pbd
where pdb.balance_type_id = l_balance_type_id
and pdb.defined_balance_id = plb.defined_balance_id
and pdb.balance_dimension_id = pbd.balance_dimension_id
and pbd.feed_checking_type = 'F';
select parameter_value
into g_lat_bal_check_mode
from pay_action_parameters
where parameter_name = 'LAT_BAL_CHECK_MODE';
delete from pay_balance_context_values BCV
where BCV.latest_balance_id = plbcrec.latest_balance_id;
delete from pay_person_latest_balances PLB
where PLB.latest_balance_id = plbcrec.latest_balance_id;
delete from pay_balance_context_values BCV
where BCV.latest_balance_id = albcrec.latest_balance_id;
delete from pay_assignment_latest_balances ALB
where ALB.latest_balance_id = albcrec.latest_balance_id;
delete from pay_balance_context_values BCV
where BCV.latest_balance_id = lat_bal_list(i);
delete from pay_assignment_latest_balances ALB
where ALB.latest_balance_id =lat_bal_list(i);
delete from pay_latest_balances LB
where LB.latest_balance_id = lbcrec.latest_balance_id;
delete from pay_balance_context_values BCV
where BCV.latest_balance_id = plbcrec.latest_balance_id;
delete from pay_person_latest_balances PLB
where PLB.latest_balance_id = plbcrec.latest_balance_id;
delete from pay_balance_context_values BCV
where BCV.latest_balance_id = albcrec.latest_balance_id;
delete from pay_assignment_latest_balances ALB
where ALB.latest_balance_id = albcrec.latest_balance_id;
delete from pay_latest_balances ALB
where ALB.latest_balance_id = lbcrec.latest_balance_id;
delete from pay_balance_context_values BCV
where BCV.latest_balance_id = plbcrec.latest_balance_id;
delete from pay_person_latest_balances PLB
where PLB.latest_balance_id = plbcrec.latest_balance_id;
delete from pay_balance_context_values BCV
where BCV.latest_balance_id = lat_bal_list(i);
delete from pay_assignment_latest_balances ALB
where ALB.latest_balance_id =lat_bal_list(i);
delete from pay_latest_balances ALB
where ALB.latest_balance_id = lbcrec.latest_balance_id;
delete from pay_balance_context_values BCV
where BCV.latest_balance_id = plrec.latest_balance_id;
delete from pay_person_latest_balances PLB
where PLB.latest_balance_id = plrec.latest_balance_id;
delete from pay_latest_balances PLB
where PLB.latest_balance_id = plrec.latest_balance_id;
delete from pay_balance_context_values BCV
where BCV.latest_balance_id = lat_bal_list(i);
delete from pay_assignment_latest_balances ALB
where ALB.latest_balance_id =lat_bal_list(i);
select /*+ ORDERED INDEX (PLB PAY_PERSON_LATEST_BALANCES_FK1)
USE_NL (PLB) */
plb.latest_balance_id
from pay_defined_balances pdb,
pay_person_latest_balances plb
where pdb.balance_type_id = l_balance_type_id
and plb.defined_balance_id = pdb.defined_balance_id;
select /*+ ORDERED INDEX (PLB PAY_ASSIGNMENT_LATEST_BALA_FK2)
USE_NL (PLB) */
plb.latest_balance_id
from pay_defined_balances pdb,
pay_assignment_latest_balances plb
where pdb.balance_type_id = l_balance_type_id
and plb.defined_balance_id = pdb.defined_balance_id;
select /*+ ORDERED INDEX (LB PAY_LATEST_BALANCES_FK1)
USE_NL (LB) */
lb.latest_balance_id
from pay_defined_balances pdb,
pay_latest_balances lb
where pdb.balance_type_id = l_balance_type_id
and lb.defined_balance_id = pdb.defined_balance_id;
delete from pay_balance_context_values BCV
where BCV.latest_balance_id = plbcrec.latest_balance_id;
delete from pay_person_latest_balances PLB
where PLB.latest_balance_id = plbcrec.latest_balance_id;
delete from pay_balance_context_values BCV
where BCV.latest_balance_id = lat_bal_list(i);
delete from pay_assignment_latest_balances ALB
where ALB.latest_balance_id =lat_bal_list(i);
delete from pay_latest_balances ALB
where ALB.latest_balance_id = lbcrec.latest_balance_id;
select plb.latest_balance_id
from pay_person_latest_balances plb
where plb.person_id = p_person_id;
select lb.latest_balance_id
from pay_latest_balances lb
where lb.person_id = p_person_id;
select /*+ ORDERED */ alb.latest_balance_id
from per_periods_of_service pos,
per_all_assignments_f asg,
pay_assignment_actions act,
pay_payroll_actions pac,
pay_assignment_latest_balances alb
where pos.person_id = p_person_id
and asg.period_of_service_id = pos.period_of_service_id
and asg.person_id = p_person_id
and act.assignment_id = asg.assignment_id
and alb.assignment_action_id = act.assignment_action_id
and pac.payroll_action_id = act.payroll_action_id
and pac.effective_date between
asg.effective_start_date and asg.effective_end_date;
cursor plbc_selective (p_person_id number,
p_eletyp_id number
) is
select
plb.latest_balance_id
from
pay_input_values_f piv,
pay_balance_feeds_f pbf,
pay_defined_balances pdb,
pay_person_latest_balances plb,
pay_assignment_actions paa,
pay_payroll_actions ppa
where plb.person_id = p_person_id
and piv.element_type_id = p_eletyp_id
and piv.input_value_id = pbf.input_value_id
and plb.defined_balance_id = pdb.defined_balance_id
and pdb.balance_type_id = pbf.balance_type_id
and paa.assignment_action_id = plb.assignment_action_id
and paa.payroll_action_id = ppa.payroll_action_id
and ppa.effective_date between pbf.effective_start_date
and pbf.effective_end_date
and ppa.effective_date between piv.effective_start_date
and piv.effective_end_date;
cursor albc_selective (p_person_id number,
p_eletyp_id number) is
select
alb.latest_balance_id
from pay_assignment_latest_balances alb,
pay_assignment_actions act,
pay_payroll_actions pac,
pay_defined_balances pdb,
pay_balance_feeds_f pbf,
pay_input_values_f piv
where act.assignment_id = alb.assignment_id
and alb.assignment_id in
(select distinct asg.assignment_id
from per_all_assignments_f asg
where asg.person_id = p_person_id)
and alb.assignment_action_id = act.assignment_action_id
and piv.element_type_id = p_eletyp_id
and piv.input_value_id = pbf.input_value_id
and alb.defined_balance_id = pdb.defined_balance_id
and pdb.balance_type_id = pbf.balance_type_id
and pac.payroll_action_id = act.payroll_action_id
and pac.effective_date between pbf.effective_start_date
and pbf.effective_end_date
and pac.effective_date between piv.effective_start_date
and piv.effective_end_date;
cursor lbc_selective (p_person_id number,
p_eletyp_id number
) is
select
plb.latest_balance_id
from
pay_input_values_f piv,
pay_balance_feeds_f pbf,
pay_defined_balances pdb,
pay_latest_balances plb,
pay_assignment_actions paa,
pay_payroll_actions ppa
where plb.person_id = p_person_id
and piv.element_type_id = p_eletyp_id
and piv.input_value_id = pbf.input_value_id
and plb.defined_balance_id = pdb.defined_balance_id
and pdb.balance_type_id = pbf.balance_type_id
and paa.assignment_action_id = plb.assignment_action_id
and paa.payroll_action_id = ppa.payroll_action_id
and ppa.effective_date between pbf.effective_start_date
and pbf.effective_end_date
and ppa.effective_date between piv.effective_start_date
and piv.effective_end_date;
select asg.person_id
into l_person_id
from per_all_assignments_f asg
where asg.assignment_id = p_assignment_id
and p_effective_date between
asg.effective_start_date and asg.effective_end_date;
delete from pay_balance_context_values BCV
where BCV.latest_balance_id = plbcrec.latest_balance_id;
delete from pay_person_latest_balances PLB
where PLB.latest_balance_id = plbcrec.latest_balance_id;
delete from pay_balance_context_values BCV
where BCV.latest_balance_id = albcrec.latest_balance_id;
delete from pay_assignment_latest_balances ALB
where ALB.latest_balance_id = albcrec.latest_balance_id;
delete from pay_latest_balances ALB
where ALB.latest_balance_id = lbcrec.latest_balance_id;
select pel.element_type_id into l_element_type_id
from pay_element_entries_f pee
,pay_element_links_f pel
where
pee.element_entry_id = p_element_entry
and p_effective_date between pee.effective_start_date
and pee.effective_end_date
and pel.element_link_id = pee.element_link_id
and p_effective_date between pel.effective_start_date
and pel.effective_end_date
;
for plbcrec in plbc_selective(l_person_id, l_element_type_id) loop
delete from pay_balance_context_values BCV
where BCV.latest_balance_id = plbcrec.latest_balance_id;
delete from pay_person_latest_balances PLB
where PLB.latest_balance_id = plbcrec.latest_balance_id;
for albcrec in albc_selective(l_person_id, l_element_type_id) loop
delete from pay_balance_context_values BCV
where BCV.latest_balance_id = albcrec.latest_balance_id;
delete from pay_assignment_latest_balances ALB
where ALB.latest_balance_id = albcrec.latest_balance_id;
for lbcrec in lbc_selective(l_person_id, l_element_type_id) loop
delete from pay_latest_balances ALB
where ALB.latest_balance_id = lbcrec.latest_balance_id;
update_action_sequence
DESCRIPTION
Update the action sequence of a particular action.
NOTES
*/
procedure update_action_sequence (p_assact in number,
rmode in varchar2)
is
pact number;
select payroll_action_id,
action_sequence,
assignment_id,
pay_assignment_actions_s.nextval
into pact,
actseq,
asgid,
newseq
from pay_assignment_actions
where assignment_action_id = p_assact;
update pay_assignment_actions
set action_sequence = newseq
where assignment_action_id = p_assact;
update pay_run_balances
set action_sequence = newseq
where assignment_action_id = p_assact;
end update_action_sequence;
chldact in varchar2 default 'N' -- update child actions (Y or N)
) is
--
cursor cnkasg is
select ppa.effective_date,
paa.assignment_action_id,
ppa.action_type
from pay_payroll_actions ppa,
pay_assignment_actions paa
where ppa.payroll_action_id = pactid
and paa.payroll_action_id = ppa.payroll_action_id
and paa.source_action_id is null
and paa.chunk_number = cnkno;
chldact in varchar2 default 'N', -- update child actions (Y or N)
actype in varchar2 -- action_type
) is
--
--
cursor seqasg (
aaid number,
chldact varchar2
) is
select ac2.rowid,
ac2.assignment_action_id
from pay_action_classifications acl,
pay_assignment_actions ac2,
pay_assignment_actions paa,
pay_payroll_actions ppa,
pay_payroll_actions pa2
where paa.assignment_action_id = aaid
and ppa.payroll_action_id = paa.payroll_action_id
and ac2.assignment_id = paa.assignment_id
and pa2.payroll_action_id = ac2.payroll_action_id
and acl.classification_name = 'SEQUENCED'
and pa2.effective_date > ppa.effective_date
and pa2.action_type = acl.action_type
and (( chldact = 'N'
and ac2.source_action_id is null)
or
( chldact = 'Y')
)
order by pa2.effective_date, ac2.action_sequence
for update of ac2.assignment_action_id;
select /*+ ORDERED USE_NL(ac2, pa2)
INDEX(ac2 PAY_ASSIGNMENT_ACTIONS_N1)
INDEX(pa2 PAY_PAYROLL_ACTIONS_PK) */
ac2.rowid,
ac2.assignment_action_id
from pay_assignment_actions act,
pay_payroll_actions pac,
per_all_assignments_f asg,
per_all_assignments_f as2,
pay_assignment_actions ac2,
pay_payroll_actions pa2,
pay_action_classifications acl
where act.assignment_action_id = aaid
and pac.payroll_action_id = act.payroll_action_id
and asg.assignment_id = act.assignment_id
and pac.effective_date between
asg.effective_start_date and asg.effective_end_date
and as2.person_id = asg.person_id
and as2.effective_end_date > pac.effective_date
and ac2.assignment_id = as2.assignment_id
and pa2.payroll_action_id = ac2.payroll_action_id
and as2.effective_start_date = (select max(paf3.effective_start_date)
from per_all_assignments_f paf3
where paf3.assignment_id = ac2.assignment_id
and paf3.effective_start_date <= pa2.effective_date
)
and acl.classification_name = 'SEQUENCED'
and pa2.action_type = acl.action_type
and pa2.effective_date > pac.effective_date
and (( chldact = 'N'
and ac2.source_action_id is null)
or
( chldact = 'Y')
)
order by pa2.effective_date, ac2.action_sequence
for update of ac2.assignment_action_id;
select ac2.rowid,
ac2.assignment_action_id
from
(
select distinct ac3.assignment_action_id
from pay_assignment_actions act,
pay_object_groups pog,
pay_object_groups pog2,
pay_object_groups pog3,
pay_payroll_actions pac,
pay_object_groups pog4,
pay_assignment_actions ac3,
pay_payroll_actions pa3,
pay_action_classifications acl
where act.assignment_action_id = aaid
and pac.payroll_action_id = act.payroll_action_id
and pog.source_id = act.assignment_id
and pog.source_type = 'PAF'
and pog2.object_group_id = pog.parent_object_group_id
and pog2.source_type = 'PPF'
and pog3.source_id = pog2.source_id
and pog3.source_type = 'PPF'
and pog4.parent_object_group_id = pog3.object_group_id
and ac3.assignment_id = pog4.source_id
and pa3.payroll_action_id = ac3.payroll_action_id
and pa3.effective_date > pac.effective_date
and pa3.action_type <> 'L'
and pa3.action_type = acl.action_type
and acl.classification_name = 'SEQUENCED'
union all
select distinct ac3.assignment_action_id
from pay_assignment_actions act,
pay_object_groups pog,
pay_object_groups pog2,
pay_object_groups pog3,
pay_payroll_actions pac,
pay_assignment_actions ac3,
pay_payroll_actions pa3
where act.assignment_action_id = aaid
and pac.payroll_action_id = act.payroll_action_id
and pog.source_id = act.assignment_id
and pog.source_type = 'PAF'
and pog2.object_group_id = pog.parent_object_group_id
and pog2.source_type = 'PPF'
and pog3.source_id = pog2.source_id
and pog3.source_type = 'PPF'
and ac3.object_id = pog3.object_group_id
and ac3.object_type = 'POG'
and pa3.payroll_action_id = ac3.payroll_action_id
and pa3.effective_date > pac.effective_date
and pa3.action_type = 'L') V,
pay_assignment_actions ac2,
pay_payroll_actions pa2
where ac2.assignment_action_id = V.assignment_action_id
and pa2.payroll_action_id = ac2.payroll_action_id
and (( chldact = 'N'
and ac2.source_action_id is null)
or
( chldact = 'Y')
)
order by pa2.effective_date, ac2.action_sequence
for update of ac2.assignment_action_id;
select ac2.rowid,
ac2.assignment_action_id
from
(
select distinct ac3.assignment_action_id
from pay_assignment_actions act,
pay_object_groups pog,
pay_object_groups pog2,
pay_payroll_actions pac,
pay_object_groups pog3,
pay_assignment_actions ac3,
pay_payroll_actions pa3,
pay_action_classifications acl
where act.assignment_action_id = aaid
and pac.payroll_action_id = act.payroll_action_id
and pog.object_group_id = act.object_id
and pog.source_type = 'PPF'
and pog2.source_id = pog.source_id
and pog2.source_type = 'PPF'
and pog3.parent_object_group_id = pog2.object_group_id
and ac3.assignment_id = pog3.source_id
and pa3.payroll_action_id = ac3.payroll_action_id
and pa3.effective_date > pac.effective_date
and pa3.action_type <> 'L'
and pa3.action_type = acl.action_type
and acl.classification_name = 'SEQUENCED'
union all
select distinct ac3.assignment_action_id
from pay_assignment_actions act,
pay_object_groups pog,
pay_object_groups pog2,
pay_payroll_actions pac,
pay_assignment_actions ac3,
pay_payroll_actions pa3
where act.assignment_action_id = aaid
and pac.payroll_action_id = act.payroll_action_id
and pog.object_group_id = act.object_id
and pog.source_type = 'PPF'
and pog2.source_id = pog.source_id
and pog2.source_type = 'PPF'
and ac3.object_id = pog2.object_group_id
and ac3.object_type = 'POG'
and pa3.payroll_action_id = ac3.payroll_action_id
and pa3.effective_date > pac.effective_date
and pa3.action_type = 'L') V,
pay_assignment_actions ac2,
pay_payroll_actions pa2
where ac2.assignment_action_id = V.assignment_action_id
and pa2.payroll_action_id = ac2.payroll_action_id
and (( chldact = 'N'
and ac2.source_action_id is null)
or
( chldact = 'Y')
)
order by pa2.effective_date, ac2.action_sequence
for update of ac2.assignment_action_id;
update pay_assignment_actions act
set act.action_sequence = pay_assignment_actions_s.nextval
where act.rowid = my_rowid;
update pay_run_balances
set action_sequence = pay_assignment_actions_s.currval
where assignment_action_id = upd_aa_id;
select assignment_action_id
from pay_assignment_actions
where source_action_id = p_aa_id
order by action_sequence;
update pay_assignment_actions
set action_sequence = pay_assignment_actions_s.nextval
where assignment_action_id = chdrec.assignment_action_id;
select /*+ INDEX (pa2 PAY_PAYROLL_ACTIONS_PK) */
ac2.rowid,
ac2.assignment_action_id,
ac2.source_action_id,
ac2.object_type
from pay_action_classifications acl,
pay_assignment_actions ac2,
pay_payroll_actions pa2
where ac2.assignment_id = asgid
and pa2.payroll_action_id = ac2.payroll_action_id
and acl.classification_name = 'SEQUENCED'
and pa2.action_type = acl.action_type
and ac2.action_sequence > actseq
and (ac2.source_action_id is null
or
(ac2.object_id is not null
and ac2.object_type = 'POG')
)
order by pa2.effective_date, ac2.action_sequence
for update of ac2.assignment_action_id;
select /*+ ORDERED
INDEX (ac2 PAY_ASSIGNMENT_ACTIONS_N51)
(pa2 PAY_PAYROLL_ACTIONS_PK) */
ac2.rowid,
ac2.assignment_action_id,
ac2.source_action_id,
ac2.object_type
from pay_payroll_actions pac,
per_all_assignments_f asg,
per_all_assignments_f as2,
pay_assignment_actions act,
pay_assignment_actions ac2,
pay_payroll_actions pa2,
pay_action_classifications acl
where pac.payroll_action_id = pactid
and act.payroll_action_id = pac.payroll_action_id
and act.assignment_id = asgid
and act.source_action_id is null
and asg.assignment_id = act.assignment_id
and pac.effective_date between
asg.effective_start_date and asg.effective_end_date
and as2.person_id = asg.person_id
and decode(rmode, 'G', pac.effective_date,
as2.effective_end_date) >= pac.effective_date
and ac2.assignment_id = as2.assignment_id
and pa2.payroll_action_id = ac2.payroll_action_id
and as2.effective_start_date = (select max(paf3.effective_start_date)
from per_all_assignments_f paf3
where paf3.assignment_id = ac2.assignment_id
)
and acl.classification_name = 'SEQUENCED'
and pa2.action_type = acl.action_type
and ac2.action_sequence > actseq
and (ac2.source_action_id is null
or
(ac2.object_id is not null
and ac2.object_type = 'POG')
)
order by pa2.effective_date, ac2.action_sequence
for update of ac2.assignment_action_id;
select /*+ ORDERED
INDEX (ac2 PAY_ASSIGNMENT_ACTIONS_PK)
(pa2 PAY_PAYROLL_ACTIONS_PK) */
ac2.rowid,
ac2.assignment_action_id,
ac2.source_action_id,
ac2.object_type
from
(
select /*+ ORDERED */
distinct ac3.assignment_action_id
from pay_payroll_actions pac,
pay_assignment_actions act,
pay_object_groups pog,
pay_object_groups pog2,
pay_object_groups pog3,
pay_object_groups pog4,
pay_assignment_actions ac3,
pay_payroll_actions pa3,
pay_action_classifications acl
where pac.payroll_action_id = pactid
and act.payroll_action_id = pac.payroll_action_id
and act.assignment_id = asgid
and act.source_action_id is null
and pog.source_id = act.assignment_id
and pog.source_type = 'PAF'
and pac.effective_date between
pog.start_date and pog.end_date
and pog2.object_group_id = pog.parent_object_group_id
and pog2.source_type = 'PPF'
and pog3.source_id = pog2.source_id
and pog3.source_type = 'PPF'
and pog4.parent_object_group_id = pog3.object_group_id
and ac3.assignment_id = pog4.source_id
and ac3.action_sequence > actseq
and pa3.payroll_action_id = ac3.payroll_action_id
and pa3.action_type <> 'L'
and pa3.action_type = acl.action_type
and acl.classification_name = 'SEQUENCED'
union all
select /*+ ORDERED */
distinct ac3.assignment_action_id
from pay_payroll_actions pac,
pay_assignment_actions act,
pay_object_groups pog,
pay_object_groups pog2,
pay_object_groups pog3,
pay_assignment_actions ac3,
pay_payroll_actions pa3
where pac.payroll_action_id = pactid
and act.payroll_action_id = pac.payroll_action_id
and act.assignment_id = asgid
and act.source_action_id is null
and pog.source_id = act.assignment_id
and pog.source_type = 'PAF'
and pac.effective_date between
pog.start_date and pog.end_date
and pog2.object_group_id = pog.parent_object_group_id
and pog2.source_type = 'PPF'
and pog3.source_id = pog2.source_id
and pog3.source_type = 'PPF'
and ac3.object_id = pog3.object_group_id
and ac3.object_type = 'POG'
and ac3.action_sequence > actseq
and pa3.payroll_action_id = ac3.payroll_action_id
and pa3.action_type = 'L') V,
pay_assignment_actions ac2,
pay_payroll_actions pa2
where ac2.assignment_action_id = V.assignment_action_id
and pa2.payroll_action_id = ac2.payroll_action_id
and (ac2.source_action_id is null
or
(ac2.object_id is not null
and ac2.object_type = 'POG')
)
order by pa2.effective_date, ac2.action_sequence
for update of ac2.assignment_action_id;
update pay_assignment_actions act
set act.action_sequence = pay_assignment_actions_s.nextval
where act.rowid = my_rowid;
update pay_run_balances
set action_sequence = pay_assignment_actions_s.currval
where assignment_action_id = upd_aa_id;
update pay_assignment_actions act
set act.action_sequence = pay_assignment_actions_s.nextval
where act.assignment_action_id = src_aa_id;
select int.locked_action_id locked_action_id
from pay_action_interlocks int
where int.locking_action_id = p_locking_action_id
order by int.locked_action_id;
select context_name,
context_id
from ff_contexts;
inassact - INsert ASSignment Action
DESCRIPTION
Inserts and validates the insert of an assignment
action. This is called
a) from the QuickPay processing procedure (qpassact).
b) from the Reversal procedure (reversal).
c) from external/manual payment procedure (ext_man_payment).
d) from the Balance Adjustment procedure (bal_adjust).
e) from the hrbaldtm package (connected to balance user exit).
NOTES
This procedure handles the interlock rules
required in the case of QuickPay, and the
re-sequencing of action_sequence for
Reversal and Balance Adjustment and External/Manual
Payments.
inassact is a cover for inassact_main, to ensure a
taxunt is passed to inassact_main.
*/
procedure inassact
(
pactid in number, -- payroll_action_id.
asgid in number, -- assignment_id to create action for.
p_ass_action_seq in number default null, --action sequence
p_serial_number in varchar2 default null, --cheque number
p_pre_payment_id in number default null, --pre payment id
p_element_entry in number default null,
p_asg_lock in boolean default TRUE, --lock assignment
p_purge_mode in boolean default FALSE, --purge mode
run_type_id in number default null
) is
--
begin
hrassact.inassact_main(pactid, asgid, p_ass_action_seq,
p_serial_number, p_pre_payment_id,
p_element_entry, p_asg_lock, null,
p_purge_mode,run_type_id);
irbaact - Insert Reversal or Balance Adjustment ACTion.
DESCRIPTION
Insert an assignment action for a Reversal,
Balance Adjustment or non tracked action.
NOTES
This procedure copes with the re-sequencing
that may be required.
Note the call from the balance user exit code
via the hrbaldtm.get_bal_ass_action procedure.
*/
procedure irbaact
(
pactid in number, -- payroll_action_id.
asgid in number, -- assignment_id of action to create.
rmode in varchar2, -- rule_mode (time period independent Y or N)
actyp in varchar2, -- action type.
ee_id in number, -- element entry id.
taxunt in number, -- tax unit id
pmode in boolean, -- purge mode
p_run_type_id in number, -- tax unit id
aa_id in number,
p_mode in varchar2
) is
--
--
-- This cursor selects the rowid values of all
-- the sequenced assignment actions for the
-- specified assignment only, that are later
-- than the effective_date of the specified
-- payroll action.
cursor seqasg (pactid number,
asgid number) is
select ac2.rowid,
ac2.assignment_action_id
from pay_action_classifications acl,
pay_assignment_actions ac2,
pay_assignment_actions act,
pay_payroll_actions pa2,
pay_payroll_actions pac
where pac.payroll_action_id = pactid
and act.payroll_action_id = pac.payroll_action_id
and act.assignment_id = asgid
and ac2.assignment_id = act.assignment_id
and pa2.payroll_action_id = ac2.payroll_action_id
and acl.classification_name = 'SEQUENCED'
and pa2.action_type = acl.action_type
and pa2.effective_date > pac.effective_date
order by pa2.effective_date, ac2.action_sequence
for update of ac2.assignment_action_id;
select /*+
INDEX(ac2 PAY_ASSIGNMENT_ACTIONS_N51) */
ac2.rowid,
ac2.assignment_action_id
from pay_payroll_actions pac,
pay_assignment_actions act,
per_all_assignments_f asg,
per_all_assignments_f as2,
pay_assignment_actions ac2,
pay_payroll_actions pa2,
pay_action_classifications acl
where pac.payroll_action_id = pactid
and act.payroll_action_id = pac.payroll_action_id
and act.assignment_id = asgid
and asg.assignment_id = act.assignment_id
and pac.effective_date between
asg.effective_start_date and asg.effective_end_date
and as2.person_id = asg.person_id
and as2.effective_end_date > pac.effective_date
and ac2.assignment_id = as2.assignment_id
and pa2.payroll_action_id = ac2.payroll_action_id
and pa2.effective_date between
as2.effective_start_date and as2.effective_end_date
and acl.classification_name = 'SEQUENCED'
and pa2.action_type = acl.action_type
and pa2.effective_date > pac.effective_date
order by pa2.effective_date, ac2.action_sequence
for update of ac2.assignment_action_id;
select /*+ ORDERED
INDEX(ac2 PAY_ASSIGNMENT_ACTIONS_PK) */
ac2.rowid,
ac2.assignment_action_id
from
(
select /*+ ORDERED */
distinct ac3.assignment_action_id
from pay_payroll_actions pac,
pay_assignment_actions act,
pay_object_groups pog,
pay_object_groups pog2,
pay_object_groups pog3,
pay_object_groups pog4,
pay_assignment_actions ac3,
pay_payroll_actions pa3,
pay_action_classifications acl
where pac.payroll_action_id = pactid
and act.payroll_action_id = pac.payroll_action_id
and act.assignment_id = asgid
and pog.source_id = act.assignment_id
and pog.source_type = 'PAF'
and pog2.object_group_id = pog.parent_object_group_id
and pog2.source_type = 'PPF'
and pog3.source_id = pog2.source_id
and pog3.source_type = 'PPF'
and pog4.parent_object_group_id = pog3.object_group_id
and ac3.assignment_id = pog4.source_id
and pa3.payroll_action_id = ac3.payroll_action_id
and pa3.effective_date > pac.effective_date
and pa3.action_type <> 'L'
and pa3.action_type = acl.action_type
and acl.classification_name = 'SEQUENCED'
union all
select /*+ ORDERED */
distinct ac3.assignment_action_id
from pay_payroll_actions pac,
pay_assignment_actions act,
pay_object_groups pog,
pay_object_groups pog2,
pay_object_groups pog3,
pay_assignment_actions ac3,
pay_payroll_actions pa3
where pac.payroll_action_id = pactid
and act.payroll_action_id = pac.payroll_action_id
and act.assignment_id = asgid
and pog.source_id = act.assignment_id
and pog.source_type = 'PAF'
and pog2.object_group_id = pog.parent_object_group_id
and pog2.source_type = 'PPF'
and pog3.source_id = pog2.source_id
and pog3.source_type = 'PPF'
and ac3.object_id = pog3.object_group_id
and ac3.object_type = 'POG'
and pa3.payroll_action_id = ac3.payroll_action_id
and pa3.effective_date > pac.effective_date
and pa3.action_type = 'L') V,
pay_assignment_actions ac2,
pay_payroll_actions pa2
where ac2.assignment_action_id = V.assignment_action_id
and pa2.payroll_action_id = ac2.payroll_action_id
order by pa2.effective_date, ac2.action_sequence
for update of ac2.assignment_action_id;
select distinct paf.assignment_id
from per_all_assignments_f paf
where paf.person_id = persid;
select distinct pog_grp.source_id assignment_id
from pay_object_groups pog_act,
pay_object_groups pog_grp
where pog_act.source_id = asgid
and pog_act.source_type = 'PAF'
and pog_act.parent_object_group_id = pog_grp.parent_object_group_id
and pog_grp.source_type = 'PAF';
select pac.effective_date,
pac.action_type,
pbg.legislation_code,
asg.person_id
into effdate, actype, lgcode, persid
from
per_business_groups_perf pbg,
per_all_assignments_f asg,
pay_payroll_actions pac
where pac.payroll_action_id = pactid
and pbg.business_group_id = asg.business_group_id
and asg.assignment_id = asgid
and pac.effective_date between
asg.effective_start_date and asg.effective_end_date;
select rule_mode
into default_run_type
from pay_legislation_rules
where legislation_code = lgcode
and rule_type = 'DEFAULT_RUN_TYPE';
select 1
into aa_exists
from dual
where exists (
select /*+ INDEX (pay_pa2 pay_payroll_actions_pk) */ null
from pay_action_classifications pay_acl,
pay_payroll_actions pay_pa2,
pay_assignment_actions pay_ac2
where pay_ac2.assignment_id = asgid
and pay_pa2.payroll_action_id = pay_ac2.payroll_action_id
and pay_acl.classification_name = 'SEQUENCED'
and pay_pa2.action_type = pay_acl.action_type
and pay_ac2.action_status not in ('C', 'S', 'B'));
select 1
into aa_exists
from sys.dual
where exists (
select /*+ INDEX (pay_pa2 pay_payroll_actions_pk) */ null
from pay_action_classifications pay_acl,
pay_payroll_actions pay_pa2,
pay_assignment_actions pay_ac2
where pay_ac2.assignment_id = dprec.assignment_id
and pay_pa2.payroll_action_id = pay_ac2.payroll_action_id
and pay_acl.classification_name = 'SEQUENCED'
and pay_pa2.action_type = pay_acl.action_type
and pay_ac2.action_status not in ('C', 'S', 'B'));
select 1
into aa_exists
from sys.dual
where exists (
select /*+ INDEX (pay_pa2 pay_payroll_actions_pk) */ null
from pay_action_classifications pay_acl,
pay_payroll_actions pay_pa2,
pay_assignment_actions pay_ac2
where pay_ac2.assignment_id = dprec.assignment_id
and pay_pa2.payroll_action_id = pay_ac2.payroll_action_id
and pay_acl.classification_name = 'SEQUENCED'
and pay_pa2.action_type = pay_acl.action_type
and pay_ac2.action_status not in ('C', 'S', 'B'));
insert into pay_assignment_actions (
assignment_action_id,
assignment_id,
payroll_action_id,
action_status,
chunk_number,
action_sequence,
object_version_number,
tax_unit_id,
run_type_id)
select aa_id,
asgid,
pactid,
decode(actyp, 'N', 'U', 'X', 'U', 'C'),
1,
aa_id,
1,
taxunt2,
l_run_type_id
from sys.dual
where not exists (
select null
from pay_payroll_actions pac,
pay_payroll_actions pa2,
pay_assignment_actions act,
pay_assignment_actions ac2
where pac.payroll_action_id = pactid
and pac.action_type <> 'I'
and act.payroll_action_id = pac.payroll_action_id
and act.assignment_id = asgid
and ac2.assignment_id = act.assignment_id
and pa2.payroll_action_id = ac2.payroll_action_id
and pa2.action_type = 'Z'
and pa2.effective_date > pac.effective_date);
update pay_assignment_actions act
set act.action_sequence = pay_assignment_actions_s.nextval
where act.rowid = my_rowid;
update pay_run_balances
set action_sequence = pay_assignment_actions_s.currval
where assignment_action_id = upd_aa_id;
select pac.action_type,
pac.business_group_id,
pay_assignment_actions_s.nextval
into actyp,
bgid,
aa_id
from pay_payroll_actions pac
where pac.payroll_action_id = pactid;
select /*+ ORDERED
INDEX (asg PER_ASSIGNMENTS_F_PK)
USE_NL(asg) */
asg.business_group_id,
asg.period_of_service_id
into bgid, posid
from pay_payroll_actions pac,
per_all_assignments_f asg
where pac.payroll_action_id = pactid
and asg.assignment_id = asgid
and pac.effective_date between
asg.effective_start_date and asg.effective_end_date
for update of asg.assignment_id;
select 1
into posid
from per_periods_of_service pos
where pos.period_of_service_id = posid
for update of pos.period_of_service_id;
select /*+ ORDERED*/ plr.rule_mode
into rmode
from per_business_groups_perf grp,
pay_legislation_rules plr
where grp.business_group_id = bgid
and plr.legislation_code = grp.legislation_code
and plr.rule_type = 'I'
and plr.rule_mode in ('Y','N','G');
insert into pay_assignment_actions (
assignment_action_id,
assignment_id,
payroll_action_id,
action_status,
chunk_number,
action_sequence,
object_version_number,
tax_unit_id)
select aa_id,
asgid,
pac.payroll_action_id,
'U',
1,
aa_id,
1,
hr_dynsql.get_tax_unit(asg.assignment_id,
pac.effective_date)
from
per_business_groups_perf pbg,
per_all_assignments_f asg,
per_all_assignments_f as2,
pay_payroll_actions pac
where pac.payroll_action_id = pactid
and asg.payroll_id = pac.payroll_id
and asg.assignment_id = asgid
and pac.effective_date between
asg.effective_start_date and asg.effective_end_date
and as2.assignment_id = asg.assignment_id
and pac.date_earned between
as2.effective_start_date and as2.effective_end_date
and pbg.business_group_id = pac.business_group_id
and not exists (
select null
from pay_payroll_actions pa2,
pay_assignment_actions ac2,
per_time_periods ptp
where ac2.assignment_id = asg.assignment_id
and pa2.payroll_action_id = ac2.payroll_action_id
and ptp.payroll_id = pac.payroll_id
and pac.effective_date between ptp.start_date and ptp.end_date
and pa2.effective_date <= ptp.end_date);
insert into pay_assignment_actions (
assignment_action_id,
assignment_id,
payroll_action_id,
action_status,
chunk_number,
action_sequence,
object_version_number,
tax_unit_id)
select aa_id,
asgid,
pac.payroll_action_id,
'U',
1,
aa_id,
1,
hr_dynsql.get_tax_unit(asg.assignment_id,
pac.effective_date)
from
per_business_groups_perf pbg,
per_all_assignments_f asg,
per_all_assignments_f as2,
pay_payroll_actions pac
where pac.payroll_action_id = pactid
and asg.payroll_id = pac.payroll_id
and asg.assignment_id = asgid
and pac.effective_date between
asg.effective_start_date and asg.effective_end_date
and as2.assignment_id = asg.assignment_id
and pac.date_earned between
as2.effective_start_date and as2.effective_end_date
and pbg.business_group_id = pac.business_group_id
and not exists (
select null
from pay_action_classifications acl,
pay_payroll_actions pa2,
pay_assignment_actions ac2
where ac2.assignment_id = asg.assignment_id
and pa2.payroll_action_id = ac2.payroll_action_id
and acl.classification_name = 'SEQUENCED'
and pa2.action_type = acl.action_type
and (pa2.effective_date > pac.effective_date
or (ac2.action_status not in ('C', 'S')
and pa2.effective_date <= pac.effective_date)));
insert into pay_assignment_actions (
assignment_action_id,
assignment_id,
payroll_action_id,
action_status,
chunk_number,
action_sequence,
object_version_number,
tax_unit_id)
select aa_id,
asgid,
pac.payroll_action_id,
'U',
1,
aa_id,
1,
hr_dynsql.get_tax_unit(asg.assignment_id,
pac.effective_date)
from per_business_groups_perf pbg,
per_all_assignments_f asg,
per_all_assignments_f as2,
pay_object_groups pog,
pay_payroll_actions pac
where pac.payroll_action_id = pactid
and asg.payroll_id = pac.payroll_id
and asg.assignment_id = asgid
and pac.effective_date between
asg.effective_start_date and asg.effective_end_date
and as2.assignment_id = asg.assignment_id
and pac.date_earned between
as2.effective_start_date and as2.effective_end_date
and pog.source_id = asgid
and pac.effective_date between
pog.start_date and pog.end_date
and pog.source_type = 'PAF'
and pbg.business_group_id = pac.business_group_id
and not exists (
select /*+ INDEX (pa2 PAY_PAYROLL_ACTIONS_PK)
INDEX (ac2 PAY_ASSIGNMENT_ACTIONS_N1) */ null
from pay_assignment_actions ac2,
pay_payroll_actions pa2,
pay_object_groups pog2,
per_time_periods ptp
where pog2.parent_object_group_id = pog.parent_object_group_id
and ac2.assignment_id = pog2.source_id
and pa2.payroll_action_id = ac2.payroll_action_id
and ptp.payroll_id = pac.payroll_id
and pac.effective_date between ptp.start_date and ptp.end_date
and pa2.effective_date <= ptp.end_date);
insert into pay_assignment_actions (
assignment_action_id,
assignment_id,
payroll_action_id,
action_status,
chunk_number,
action_sequence,
object_version_number,
tax_unit_id)
select aa_id,
asgid,
pac.payroll_action_id,
'U',
1,
aa_id,
1,
hr_dynsql.get_tax_unit(asg.assignment_id,
pac.effective_date)
from per_business_groups_perf pbg,
per_all_assignments_f asg,
per_all_assignments_f as2,
pay_object_groups pog,
pay_payroll_actions pac
where pac.payroll_action_id = pactid
and asg.payroll_id = pac.payroll_id
and asg.assignment_id = asgid
and pac.effective_date between
asg.effective_start_date and asg.effective_end_date
and as2.assignment_id = asg.assignment_id
and pac.date_earned between
as2.effective_start_date and as2.effective_end_date
and pog.source_id = asgid
and pac.effective_date between
pog.start_date and pog.end_date
and pog.source_type = 'PAF'
and pbg.business_group_id = pac.business_group_id
and not exists (
select /*+ INDEX (pa2 PAY_PAYROLL_ACTIONS_PK)
INDEX (ac2 PAY_ASSIGNMENT_ACTIONS_N1) */ null
from pay_action_classifications acl,
pay_assignment_actions ac2,
pay_payroll_actions pa2,
pay_object_groups pog2
where pog2.parent_object_group_id = pog.parent_object_group_id
and ac2.assignment_id = pog2.source_id
and pa2.payroll_action_id = ac2.payroll_action_id
and acl.classification_name = 'SEQUENCED'
and pa2.action_type = acl.action_type
and (pa2.effective_date > pac.effective_date
or (ac2.action_status not in ('C', 'S')
and pa2.effective_date <= pac.effective_date)));
insert into pay_assignment_actions (
assignment_action_id,
assignment_id,
payroll_action_id,
action_status,
chunk_number,
action_sequence,
object_version_number,
tax_unit_id)
select aa_id,
asgid,
pac.payroll_action_id,
'U',
1,
aa_id,
1,
hr_dynsql.get_tax_unit(asg.assignment_id,
pac.effective_date)
from per_business_groups_perf pbg,
per_all_assignments_f asg,
per_all_assignments_f as2,
per_periods_of_service pos,
pay_payroll_actions pac
where pac.payroll_action_id = pactid
and asg.payroll_id = pac.payroll_id
and asg.assignment_id = asgid
and pos.period_of_service_id = asg.period_of_service_id
and pac.effective_date between
asg.effective_start_date and asg.effective_end_date
and as2.assignment_id = asg.assignment_id
and pac.date_earned between
as2.effective_start_date and as2.effective_end_date
and pbg.business_group_id = pac.business_group_id
and not exists (
select /*+ INDEX (pa2 PAY_PAYROLL_ACTIONS_PK)
INDEX (ac2 PAY_ASSIGNMENT_ACTIONS_N1) */ null
from pay_assignment_actions ac2,
pay_payroll_actions pa2,
per_all_assignments_f as2,
per_time_periods ptp
where as2.period_of_service_id = pos.period_of_service_id
and ac2.assignment_id = as2.assignment_id
and pa2.payroll_action_id = ac2.payroll_action_id
and ptp.payroll_id = pac.payroll_id
and pac.effective_date between ptp.start_date and ptp.end_date
and pa2.effective_date <= ptp.end_date);
insert into pay_assignment_actions (
assignment_action_id,
assignment_id,
payroll_action_id,
action_status,
chunk_number,
action_sequence,
object_version_number,
tax_unit_id)
select aa_id,
asgid,
pac.payroll_action_id,
'U',
1,
aa_id,
1,
hr_dynsql.get_tax_unit(asg.assignment_id,
pac.effective_date)
from per_business_groups_perf pbg,
per_all_assignments_f asg,
per_all_assignments_f as2,
per_periods_of_service pos,
pay_payroll_actions pac
where pac.payroll_action_id = pactid
and asg.payroll_id = pac.payroll_id
and asg.assignment_id = asgid
and pos.period_of_service_id = asg.period_of_service_id
and pac.effective_date between
asg.effective_start_date and asg.effective_end_date
and as2.assignment_id = asg.assignment_id
and pac.date_earned between
as2.effective_start_date and as2.effective_end_date
and pbg.business_group_id = pac.business_group_id
and not exists (
select /*+ INDEX (pa2 PAY_PAYROLL_ACTIONS_PK)
INDEX (ac2 PAY_ASSIGNMENT_ACTIONS_N1) */ null
from pay_action_classifications acl,
pay_assignment_actions ac2,
pay_payroll_actions pa2,
per_all_assignments_f as2
where as2.period_of_service_id = pos.period_of_service_id
and ac2.assignment_id = as2.assignment_id
and pa2.payroll_action_id = ac2.payroll_action_id
and acl.classification_name = 'SEQUENCED'
and pa2.action_type = acl.action_type
and (pa2.effective_date > pac.effective_date
or (ac2.action_status not in ('C', 'S')
and pa2.effective_date <= pac.effective_date)));
insert into PAY_ASSIGNMENT_ACTIONS (
ASSIGNMENT_ACTION_ID,
ASSIGNMENT_ID,
PAYROLL_ACTION_ID,
ACTION_STATUS,
CHUNK_NUMBER,
ACTION_SEQUENCE,
PRE_PAYMENT_ID,
SERIAL_NUMBER,
OBJECT_VERSION_NUMBER)
select p_ass_action_seq,
asgid,
pactid,
'C',
1,
PAY_ASSIGNMENT_ACTIONS_S.nextval,
p_pre_payment_id,
p_serial_number,
1
from sys.dual
where not exists (
select null
from pay_assignment_actions act
where act.pre_payment_id = p_pre_payment_id
and not exists
( select null
from pay_action_interlocks loc1,
pay_assignment_actions actv,
pay_payroll_actions pactv
where loc1.locked_action_id = act.assignment_action_id
and loc1.locking_action_id = actv.assignment_action_id
and pactv.payroll_action_id = actv.payroll_action_id
and pactv.action_type = 'D'
)
) ;
insert into PAY_ASSIGNMENT_ACTIONS (
ASSIGNMENT_ACTION_ID,
ASSIGNMENT_ID,
PAYROLL_ACTION_ID,
ACTION_STATUS,
CHUNK_NUMBER,
ACTION_SEQUENCE,
OBJECT_VERSION_NUMBER,
TAX_UNIT_ID)
select aa_id,
act.assignment_id,
pac.payroll_action_id, -- qpprepay pact.
'U',
1,
aa_id,
1,
hr_dynsql.get_tax_unit(asg.assignment_id,
pac.effective_date)
from per_business_groups_perf pbg,
pay_assignment_actions act,
pay_payroll_actions pac, -- prepay action.
pay_payroll_actions pa2, -- the QuickPay action.
per_periods_of_service pos,
per_all_assignments_f asg
where pac.payroll_action_id = pactid
and pa2.payroll_action_id = pac.target_payroll_action_id
and act.payroll_action_id = pa2.payroll_action_id
and act.source_action_id is null /* master assignment action */
and asg.assignment_id = act.assignment_id
and pa2.effective_date between
asg.effective_start_date and asg.effective_end_date
and pos.period_of_service_id = asg.period_of_service_id
and pbg.business_group_id = pac.business_group_id
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 null
from per_all_assignments_f as3,
pay_assignment_actions ac3
where rmode <> 'Y'
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 = pos.person_id);
select pac.action_population_status
into l_action_population_status
from pay_payroll_actions pac
where pac.payroll_action_id = pactid;
update pay_payroll_actions pac
set pac.action_population_status = 'C'
where pac.payroll_action_id = pactid;
qpassact - insert QuickPay ASSignment Action
DESCRIPTION
Inserts and validates the insert of an assignment
action for the QuickPay user exit.
NOTES
This procedure directly calls inassact.
*/
procedure qpassact
(
p_payroll_action_id in number, -- payroll_action_id.
p_assignment_id in number, -- assignment_id to create action for.
p_assignment_action_id out nocopy number,
p_object_version_number out nocopy number
) is
assactid number;
select pay_assignment_actions_s.currval
into assactid
from sys.dual;
qpppassact - Insert a QuickPay Pre-Payment action.
DESCRIPTION
Process a QuickPay Pre-Payment action.
NOTES
This procedure is meant to be called via the QuickPay form.
*/
procedure qpppassact
(
p_payroll_action_id in number, -- of QuickPay pre-payment.
p_assignment_action_id out nocopy number,
p_object_version_number out nocopy number
) is
l_assignment_id number;
select act.assignment_id
into l_assignment_id
from pay_assignment_actions act,
pay_payroll_actions pac
where pac.payroll_action_id = p_payroll_action_id
and act.payroll_action_id = pac.target_payroll_action_id
and act.source_action_id is null;
select pay_assignment_actions_s.currval,
act.assignment_action_id
into l_locking_action_id,
l_locked_action_id
from pay_payroll_actions pac,
pay_assignment_actions act
where pac.payroll_action_id = p_payroll_action_id
and act.payroll_action_id = pac.target_payroll_action_id
and act.source_action_id is null;
insert into pay_action_interlocks (
locking_action_id,
locked_action_id)
values (l_locking_action_id,
l_locked_action_id);
update pay_payroll_actions pac
set pac.date_earned = (
select pa2.date_earned
from pay_payroll_actions pa2,
pay_assignment_actions act
where act.assignment_action_id = l_locked_action_id
and pa2.payroll_action_id = act.payroll_action_id)
where pac.payroll_action_id =p_payroll_action_id;
insert an assignment action and interlock. If redo is
set true, we do not need to. This was introduced to
cope with the requirements for BackPay.
- The multi flag indicates if we are calling this as part of
the assignment set reversal procedure in which case we want to
skip the initial setup part of this procedure
*/
procedure reversal
(
pactid in number, -- payroll_action_id.
assactid in number, -- assignment_action_id to be reversed.
redo in boolean default false, -- ins assact and interlock if false
rev_aaid in number default 0, -- reversal aa id of parent assactid
multi in boolean default false
) is
--
-- Cursors used for latest balance maintenance
--
cursor rev_rrs (revassactid number, p_si_needed varchar2, p_st_needed varchar2,
p_sn_needed varchar2, p_st2_needed varchar2,
p_sn2_needed varchar2, p_org_needed varchar2) is
select prr.run_result_id,
paa.tax_unit_id,
prr.local_unit_id,
prr.jurisdiction_code,
prr.source_id original_entry_id,
ppa.payroll_id,
decode(p_si_needed,
'Y', pay_balance_pkg.find_context('SOURCE_ID', prr.run_result_id),
null) source_id,
decode(p_st_needed,
'Y', pay_balance_pkg.find_context('SOURCE_TEXT', prr.run_result_id),
null) source_text,
decode(p_sn_needed,
'Y', pay_balance_pkg.find_context('SOURCE_NUMBER', prr.run_result_id),
null) source_number,
decode(p_st2_needed,
'Y', pay_balance_pkg.find_context('SOURCE_TEXT2', prr.run_result_id),
null) source_text2,
decode(p_sn2_needed,
'Y', pay_balance_pkg.find_context('SOURCE_NUMBER2', prr.run_result_id),
null) source_number2,
decode(p_org_needed,
'Y', pay_balance_pkg.find_context('ORGANIZATION_ID', prr.run_result_id),
null) organization_id,
ppa.effective_date
from pay_assignment_actions paa,
pay_run_results prr,
pay_payroll_actions ppa
where paa.assignment_action_id = revassactid
and ppa.payroll_action_id = paa.payroll_action_id
and paa.assignment_action_id = prr.assignment_action_id;
select ac2.assignment_id,
ac2.tax_unit_id,
ac2.run_type_id,
pbg.legislation_code
into asgid, taxunt,run_type_id, leg_code
from pay_action_classifications acl,
pay_assignment_actions ac2,
pay_payroll_actions pa2,
per_business_groups_perf pbg,
pay_payroll_actions pac
where pac.payroll_action_id = pactid
and pbg.business_group_id = pac.business_group_id
and ac2.assignment_action_id = assactid
and pa2.payroll_action_id = ac2.payroll_action_id
and acl.classification_name = 'REVERSED'
and acl.action_type = pa2.action_type
and pa2.effective_date <= pac.effective_date;
select act.assignment_action_id
into rev_assact
from pay_assignment_actions act
where act.payroll_action_id = pactid
and act.assignment_id = asgid;
select pay_core_utils.get_process_path(assactid)
into l_process_path
from dual;
update pay_assignment_actions
set process_path =l_process_path
where assignment_action_id=rev_assact;
insert into pay_action_interlocks (
locking_action_id,
locked_action_id)
select rev_assact,
assactid
from dual
where not exists (
select null
from pay_assignment_actions ac2,
pay_payroll_actions pa2,
pay_action_interlocks pai
where pai.locked_action_id = assactid
and ac2.assignment_action_id = pai.locking_action_id
and pa2.payroll_action_id = ac2.payroll_action_id
and pa2.action_type = 'V');
insert into pay_action_interlocks (
locking_action_id,
locked_action_id)
select rev_assact, paa1.assignment_action_id
from pay_assignment_actions paa,pay_assignment_actions paa1
where paa.assignment_action_id=assactid
and paa1.payroll_action_id=paa.payroll_action_id
and paa1.source_action_id is null
and paa1.assignment_id=paa.assignment_id
and paa1.assignment_action_id<>paa.assignment_action_id;
select act.assignment_action_id
into rev_assact
from pay_assignment_actions act
where act.payroll_action_id = pactid
and act.assignment_id = asgid;
insert into pay_action_interlocks
(locking_action_id,locked_action_id)
select rev_assact,assactid
from dual where not exists (select 1
from pay_action_interlocks
where locking_action_id=rev_assact
and locked_action_id=assactid);
select prr.run_result_id, -- original run_result_id.
prr.element_type_id,
prr.entry_type,
prr.source_id,
decode(prr.source_type, 'I', 'V', 'R'), -- source_type
prr.status, ---'P' -- status
prr.jurisdiction_code,
prr.start_date,
prr.end_date,
prr.time_definition_id,
prr.entry_process_path
from pay_run_results prr
where prr.assignment_action_id = assactid
order by prr.run_result_id;
select 1
into dummy
from pay_payroll_actions pac,
pay_element_types_f pet
where pac.payroll_action_id = pactid
and pet.element_type_id = etypid
and pac.effective_date between
pet.effective_start_date and pet.effective_end_date;
insert into pay_run_results (
run_result_id,
element_type_id,
assignment_action_id,
entry_type,
source_id,
source_type,
status,
jurisdiction_code,
start_date,
end_date,
time_definition_id,
entry_process_path)
values (pay_run_results_s.nextval,
etypid,
rev_assact, -- the reversal assignment action.
enttype,
oldrrid,
stype,
stat,
v_jcode,
start_date,
end_date,
time_def_id,
entrypath);
insert into pay_run_result_values (
input_value_id,
run_result_id,
result_value)
select piv.input_value_id,
pay_run_results_s.currval,
decode(piv.uom,
'XXXXXXXXXX', '***********************',
'H_DECIMAL1', rtrim(to_char((-1) * to_number(rrv.result_value, mask_36_dec_places), mask_36_dec_places), '.'),
'H_DECIMAL2', rtrim(to_char((-1) * to_number(rrv.result_value, mask_36_dec_places), mask_36_dec_places), '.'),
'H_DECIMAL3', rtrim(to_char((-1) * to_number(rrv.result_value, mask_36_dec_places), mask_36_dec_places), '.'),
'H_HH' , rtrim(to_char((-1) * to_number(rrv.result_value, mask_36_dec_places), mask_36_dec_places), '.'),
'H_HHMM' , rtrim(to_char((-1) * to_number(rrv.result_value, mask_36_dec_places), mask_36_dec_places), '.'),
'H_HHMMSS' , rtrim(to_char((-1) * to_number(rrv.result_value, mask_36_dec_places), mask_36_dec_places), '.'),
'I' , rtrim(to_char(decode(piv.name, l_src_iv , (1)
, l_src_num, (1)
, l_src_num2, (1)
, l_org_id_iv, (1)
, (-1))
* to_number(rrv.result_value, mask_36_dec_places)
, mask_36_dec_places), '.'),
'M' , rtrim(to_char((-1) * to_number(rrv.result_value, mask_36_dec_places), mask_36_dec_places), '.'),
'N' , rtrim(to_char(decode(piv.name, l_src_iv , (1)
, l_src_num, (1)
, l_src_num2, (1)
, l_org_id_iv, (1)
, (-1))
* to_number(rrv.result_value, mask_36_dec_places)
, mask_36_dec_places), '.'),
'ND' , rtrim(to_char((-1) * to_number(rrv.result_value, mask_36_dec_places), mask_36_dec_places), '.'),
rrv.result_value)
from pay_payroll_actions pac,
pay_run_result_values rrv,
pay_input_values_f piv
where pac.payroll_action_id = pactid
and rrv.run_result_id = oldrrid
and piv.input_value_id = rrv.input_value_id
and pac.effective_date between
piv.effective_start_date and piv.effective_end_date;
insert into pay_action_contexts
(ASSIGNMENT_ACTION_ID,
ASSIGNMENT_ID,
CONTEXT_ID,
CONTEXT_VALUE)
select distinct rev_assact,
pac.assignment_id,
pac.context_id,
pac.context_value
from pay_action_contexts pac
where pac.assignment_action_id = assactid;
update pay_payroll_actions pac
set pac.action_status = 'C'
where pac.payroll_action_id = pactid;
select paa.assignment_action_id
, paa.action_sequence
, ppa.effective_date
into rev_asgact_id
, rev_act_seq
, rev_eff_date
from pay_assignment_actions paa
, pay_payroll_actions ppa
where paa.payroll_action_id = pactid
and paa.payroll_action_id = ppa.payroll_action_id;
select paa.assignment_action_id
, paa.action_sequence
, ppa.effective_date
into rev_asgact_id
, rev_act_seq
, rev_eff_date
from pay_assignment_actions paa,
pay_payroll_actions ppa
where paa.assignment_action_id = assactid
and ppa.payroll_action_id = paa.payroll_action_id;
SELECT count(*)
INTO hrassact.gv_cnt_reversal_act_id
FROM pay_action_interlocks pai,
pay_assignment_actions paa
WHERE paa.payroll_action_id = pactid
AND paa.assignment_action_id = pai.locking_action_id;
rev_pre_inserted_rr - Reversal create pre-inserted run results.
DESCRIPTION
Creates pre-inserted run results when a Reversal is processed
These are created for any non-recurring or additional entry
type for the assignment, where there is not already an
unprocessed entry.
NOTES
*/
/* procedure rev_pre_inserted_rr
(
p_payroll_action_id in number -- payroll_action_id of reversal.
) is
cursor c1 is
select + ORDERED
INDEX(PEE PAY_ELEMENT_ENTRIES_F_PK,
PEL PAY_ELEMENT_LINKS_F_PK,
PET PAY_ELEMENT_TYPES_F_PK,
ACT2 PAY_ASSIGNMENT_ACTIONS_PK)
USE_NL(PAC ACT PAI ACT2 RR2 PEE)
pet.element_type_id,
act.assignment_action_id,
pee.entry_type,
pee.element_entry_id
from pay_payroll_actions pac,
pay_assignment_actions act,
pay_action_interlocks pai,
pay_assignment_actions act2,
pay_run_results rr2,
pay_element_entries_f pee,
pay_element_links_f pel,
pay_element_types_f pet
where pac.payroll_action_id = p_payroll_action_id
and act.payroll_action_id = pac.payroll_action_id
and pee.assignment_id = act.assignment_id
and pai.locking_action_id = act.assignment_action_id
and pai.locked_action_id = act2.assignment_action_id
and pac.date_earned between
pee.effective_start_date and pee.effective_end_date
and pel.element_link_id = pee.element_link_id
and pac.date_earned between
pel.effective_start_date and pel.effective_end_date
and pet.element_type_id = pel.element_type_id
and pac.date_earned between
pet.effective_start_date and pet.effective_end_date
and ((pet.processing_type = 'N' and pee.entry_type <> 'B')
or pee.entry_type = 'D')
and rr2.source_id = pee.element_entry_id
and rr2.source_type <> 'I'-- exclude indirects
and rr2.assignment_action_id = act2.assignment_action_id
and rr2.status <> 'U'
and not exists (
select null
from pay_run_results rr3
where rr3.source_id = pee.element_entry_id
and rr3.status = 'U')
order by pee.element_entry_id;
c_indent constant varchar2(30) := 'hrassact.rev_pre_inserted_rr';
insert into pay_run_results (
run_result_id,
element_type_id,
assignment_action_id,
entry_type,
source_id,
source_type,
status,
jurisdiction_code)
values (pay_run_results_s.nextval,
c1rec.element_type_id,
c1rec.assignment_action_id,
c1rec.entry_type,
c1rec.element_entry_id,
'E',
'U',
null);
insert into pay_run_result_values (
input_value_id,
run_result_id,
result_value)
select pev.input_value_id,
pay_run_results_s.currval,
null
from pay_payroll_actions pac,
pay_assignment_actions act, -- reversal
pay_element_entry_values_f pev
where act.assignment_action_id = c1rec.assignment_action_id --rev
and act.payroll_action_id = pac.payroll_action_id
and pev.element_entry_id = c1rec.element_entry_id
and pac.date_earned between
pev.effective_start_date and pev.effective_end_date;
end rev_pre_inserted_rr;
select pay_payroll_actions_s.nextval,
pay_assignment_actions_s.nextval,
pa.CONSOLIDATION_SET_ID,
pa.BUSINESS_GROUP_ID
from PAY_ASSIGNMENT_ACTIONS paa,
PAY_PAYROLL_ACTIONS pa
where paa.ASSIGNMENT_ACTION_ID = p_assignment_action_id
and paa.PAYROLL_ACTION_ID = pa.PAYROLL_ACTION_ID;
insert into PAY_PAYROLL_ACTIONS
(PAYROLL_ACTION_ID,
ACTION_TYPE,
BUSINESS_GROUP_ID,
CONSOLIDATION_SET_ID,
PAYROLL_ID,
ACTION_POPULATION_STATUS,
ACTION_STATUS,
EFFECTIVE_DATE,
COMMENTS,
OBJECT_VERSION_NUMBER,
PAY_ADVICE_MESSAGE)
values
(l_payroll_action_id,
'E',
l_business_group_id,
l_consolidation_set_id,
p_payroll_id,
'C',
'C',
p_eff_date,
p_comments,
1,
p_reason);
insert into PAY_ACTION_INTERLOCKS
(LOCKING_ACTION_ID,
LOCKED_ACTION_ID)
values
(l_assignment_action_id,
p_assignment_action_id);
update pay_payroll_actions pac
set pac.date_earned = (
select pa2.date_earned
from pay_payroll_actions pa2,
pay_assignment_actions act
where act.assignment_action_id = p_assignment_action_id
and pa2.payroll_action_id = act.payroll_action_id
)
where pac.payroll_action_id = l_payroll_action_id;
select piv.name,
prrv.result_value,
prrv.input_value_id,
prrv.run_result_id
from pay_run_results prr,
pay_run_result_values prrv,
pay_input_values_f piv
where prr.run_result_id = p_run_result_id
and prrv.run_result_id = prr.run_result_id
and piv.input_value_id = prrv.input_value_id
and p_effective_date between piv.effective_start_date
and piv.effective_end_date;
select
pac.payroll_id
,pac.action_type
,pac.effective_date
into
pay_id
,l_action_type
,l_effective_date
from
pay_payroll_actions pac
,pay_assignment_actions aa
where
aa.assignment_action_id = p_assact
and pac.payroll_action_id = aa.payroll_action_id;
select 1
into dummy
from pay_legislation_rules plr
where p_legcode = plr.legislation_code
and plr.rule_type = 'ACTION_CONTEXTS'
and plr.rule_mode = 'Y';
select end_date,
time_definition_id
into l_ctx_balance_date,
l_ctx_time_def_id
from pay_run_results
where run_result_id = p_rrid;
update pay_run_result_values
set result_value = l_ctx_value
where run_result_id = rrvrec.run_result_id
and input_value_id = rrvrec.input_value_id;
update pay_run_results
set jurisdiction_code = l_ctx_value
where run_result_id = p_rrid;
select organization_id
into l_org_id
from hr_organization_information hoi
where hoi.organization_id = l_ctx_value
and hoi.org_information_context = 'CLASS'
and hoi.org_information1 = 'HR_PAYEE';
select local_unit_id
into l_local_unit_id
from pay_run_results
where run_result_id = p_rrid;
select hoi.org_information5,
paa.assignment_id
into tax_group,
asg_id
from hr_organization_information hoi,
pay_assignment_actions paa
where UPPER(hoi.org_information_context) = 'FEDERAL TAX RULES'
and hoi.organization_id = paa.tax_unit_id
and paa.assignment_action_id = p_assact
and hoi.org_information5 is not null;
select hoi.org_information4,
paa.assignment_id
into tax_group,
asg_id
from hr_organization_information hoi,
pay_assignment_actions paa
where UPPER(hoi.org_information_context) = 'CANADA EMPLOYER IDENTIFICATION'
and hoi.organization_id = paa.tax_unit_id
and paa.assignment_action_id = p_assact
and hoi.org_information4 is not null;
insert into pay_action_contexts
(assignment_action_id,
assignment_id,
context_id,
context_value)
select udca.assact_id(cnt),
udca.asg_id(cnt),
udca.cxt_id(cnt),
udca.cxt_value(cnt)
from sys.dual
where not exists (select ''
from pay_action_contexts
where assignment_action_id = udca.assact_id(cnt)
and assignment_id = udca.asg_id(cnt)
and context_id = udca.cxt_id(cnt)
and context_value = udca.cxt_value(cnt));
assactid number; -- assignment_action_id of inserted action.
select 'Y'
from pay_legislation_rules
where rule_type = 'TIME_PERIOD_ID'
and legislation_code = p_leg_code
and rule_mode = 'Y';
select /*+ ordered use_nl(pee asg ptp pbg)
index(pee PAY_ELEMENT_ENTRIES_F_PK)
index(asg PER_ASSIGNMENTS_F_PK)
index(ptp PER_TIME_PERIODS_N50) */
pay_payroll_actions_s.nextval,
asg.business_group_id,
pbg.legislation_code,
asg.assignment_id,
asg.payroll_id,
ptp.time_period_id,
pee.created_by,
pee.creation_date,
pee.original_entry_id
into pactid,
busgrp,
legcode,
asgid,
payid,
tperiod,
creatby,
creatdate,
l_original_entry_id
from pay_element_entries_f pee,
per_all_assignments_f asg,
per_time_periods ptp,
per_business_groups_perf pbg
where pee.element_entry_id = eentryid
and effdate between
pee.effective_start_date and pee.effective_end_date
and asg.assignment_id = pee.assignment_id
and effdate between
asg.effective_start_date and asg.effective_end_date
and pbg.business_group_id = asg.business_group_id
and ptp.payroll_id = asg.payroll_id
and effdate between
ptp.start_date and ptp.end_date;
select pt2.time_period_id, pt2.end_date
into tperiod, dtearned
from per_time_periods pt2
where pt2.time_period_id in
(select min(time_period_id)
from per_time_periods ptp
where ptp.payroll_id = payid
and effdate between ptp.start_date
and greatest(ptp.end_date, ptp.regular_payment_date));
insert into pay_payroll_actions (
payroll_action_id,
action_type,
business_group_id,
consolidation_set_id,
payroll_id,
action_population_status,
action_status,
effective_date,
date_earned,
action_sequence,
time_period_id,
future_process_mode,
created_by,
creation_date,
object_version_number)
values (pactid,
act_type,
busgrp,
consetid,
payid,
'C',
'C',
effdate,
dtearned,
pay_payroll_actions_s.nextval,
tperiod,
prepay_flag,
creatby,
creatdate,
1);
select act.assignment_action_id, act.tax_unit_id
into assactid, tax_unit
from pay_assignment_actions act
where act.payroll_action_id = pactid;
hr_utility.trace('Update of pay_run_results has failed');
update pay_element_entries_f pee
set pee.creator_id = assactid
where pee.element_entry_id = eentryid;
hr_utility.trace('Update of pay_element_entries_f has failed');
assactid in number, -- assignment_action_id of inserted action.
rrid in number, -- run_result_id
eentryid in number, -- element_entry_id.
effdate in date, -- effective_date of bal adjust.
udca in context_details, -- The UDCA
act_type in varchar2 default 'B' -- payroll_action_type.
) is
--
--
-- balance dimensions cache
--
type bal_dims_cache is record
(dim_id number_tbl,
dim_name varchar_80_tbl,
feed_chk_type varchar_tbl,
feed_chk_code varchar_80_tbl,
exp_chk_lvl varchar_tbl,
exp_chk_code varchar_80_tbl,
sz number
);
select rrv.input_value_id,
rrv.result_value,
rrv.run_result_id
from pay_run_result_values rrv
where rrv.run_result_id = rrid
and rrv.result_value is not null;
select plb.latest_balance_id,
plb.assignment_action_id,
plb.value,
nvl(plb.expired_assignment_action_id, -9999) expired_assignment_action_id,
nvl(plb.expired_value, -9999) expired_value,
nvl(plb.prev_balance_value, -9999) prev_balance_value,
nvl(plb.prev_assignment_action_id, -9999) prev_assignment_action_id,
plb.expiry_date,
plb.expired_date,
plb.prev_expiry_date,
pdb.balance_dimension_id,
pdb.balance_type_id,
pbf.scale
from pay_latest_balances plb,
pay_defined_balances pdb,
pay_balance_feeds_f pbf
where pbf.input_value_id = p_inp_val_id
and effdate between pbf.effective_start_date
and pbf.effective_end_date
and pdb.balance_type_id = pbf.balance_type_id
and plb.defined_balance_id = pdb.defined_balance_id
and plb.person_id = p_person_id
and ( plb.assignment_id = p_asg_id
or plb.assignment_id is null)
and ( plb.process_group_id = (select distinct parent_object_group_id
from pay_object_groups pog
where pog.source_id = p_asg_id
and pog.source_type = 'PAF')
or plb.process_group_id is null);
select palb.latest_balance_id,
palb.assignment_action_id,
palb.value,
nvl(palb.expired_assignment_action_id, -9999) expired_assignment_action_id,
nvl(palb.expired_value, -9999) expired_value,
nvl(palb.prev_balance_value, -9999) prev_balance_value,
nvl(palb.prev_assignment_action_id, -9999) prev_assignment_action_id,
pdb.balance_dimension_id,
pdb.balance_type_id,
pbf.scale
from pay_assignment_latest_balances palb,
pay_defined_balances pdb,
pay_balance_feeds_f pbf
where pbf.input_value_id = p_inp_val_id
and effdate between pbf.effective_start_date
and pbf.effective_end_date
and pdb.balance_type_id = pbf.balance_type_id
and palb.defined_balance_id = pdb.defined_balance_id
and palb.assignment_id = p_asgid;
select pplb.latest_balance_id,
pplb.assignment_action_id,
pplb.value,
nvl(pplb.expired_assignment_action_id, -9999) expired_assignment_action_id,
nvl(pplb.expired_value, -9999) expired_value,
nvl(pplb.prev_balance_value, -9999) prev_balance_value,
nvl(pplb.prev_assignment_action_id, -9999) prev_assignment_action_id,
pdb.balance_dimension_id,
pdb.balance_type_id,
pbf.scale
from pay_person_latest_balances pplb,
pay_defined_balances pdb,
pay_balance_feeds_f pbf
where pbf.input_value_id = p_inp_val_id
and effdate between pbf.effective_start_date
and pbf.effective_end_date
and pdb.balance_type_id = pbf.balance_type_id
and pplb.defined_balance_id = pdb.defined_balance_id
and pplb.person_id = p_person_id;
select tax_unit_id,
jurisdiction_code,
original_entry_id,
source_id,
source_text,
source_text2,
source_number,
source_number2,
tax_group,
payroll_id,
local_unit_id,
organization_id
into l_tax_unit_id,
l_jurisdiction_code,
l_original_entry_id,
l_source_id,
l_source_text,
l_source_text2,
l_source_number,
l_source_number2,
l_tax_group,
l_payroll_id,
l_local_unit_id,
l_organization_id
from pay_latest_balances
where p_lat_bal_id = latest_balance_id;
select nvl(jurisdiction_level, 0)
into l_jurisdiction_level
from pay_balance_types
where balance_type_id = p_bal_type_id;
select context_id,
value
from pay_balance_context_values
where latest_balance_id = p_lat_bal_id;
select nvl(jurisdiction_level, 0)
into l_jurisdiction_level
from pay_balance_types
where balance_type_id = p_bal_type_id;
select pbd.dimension_name,
pbd.feed_checking_type,
pbd.feed_checking_code,
pbd.expiry_checking_code,
pbd.expiry_checking_level
into bal_dim_name,
feed_checking_type,
feed_checking_code,
bal_exp_code,
bal_exp_level
from pay_balance_dimensions pbd
where pbd.balance_dimension_id = bal_dim_id;
delete from pay_balance_context_values
where latest_balance_id = lat_bal_id;
delete from pay_assignment_latest_balances
where latest_balance_id = lat_bal_id;
delete from pay_balance_context_values
where latest_balance_id = lat_bal_id;
delete from pay_person_latest_balances
where latest_balance_id = lat_bal_id;
delete from pay_latest_balances
where latest_balance_id = lat_bal_id;
select pay.payroll_action_id,
pay.effective_date
into l_bal_owner_pay_action,
p_bal_owner_eff_date
from pay_assignment_actions asg
, pay_payroll_actions pay
where asg.assignment_action_id = p_bal_owner_asg_actid
and pay.payroll_action_id = asg.payroll_action_id;
select pay.payroll_action_id,
pay.effective_date
into l_payroll_action,
l_effective_date
from pay_assignment_actions asg
, pay_payroll_actions pay
where asg.assignment_action_id = p_ass_action_id
and pay.payroll_action_id = asg.payroll_action_id;
select pera.assignment_id,
pera.person_id,
paa.payroll_action_id,
paa.tax_unit_id,
pera.business_group_id
into asgid,
l_person_id,
pactid,
tax_unit_id,
bus_grp_id
from pay_assignment_actions paa,
per_all_assignments_f pera
where paa.assignment_action_id = assactid
and pera.assignment_id = paa.assignment_id
and effdate between pera.effective_start_date
and pera.effective_end_date;
select rr.element_type_id into l_element_type_id
from pay_run_results rr
where rr.run_result_id = rrid;
update pay_assignment_latest_balances
set assignment_action_id = alb.assignment_action_id,
value = alb.value,
expired_assignment_action_id = alb.expired_assignment_action_id,
expired_value = alb.expired_value,
prev_assignment_action_id = alb.prev_assignment_action_id,
prev_balance_value = alb.prev_balance_value
where latest_balance_id = alb.latest_balance_id;
update pay_person_latest_balances
set assignment_action_id = plb.assignment_action_id,
value = plb.value,
expired_assignment_action_id = plb.expired_assignment_action_id,
expired_value = plb.expired_value,
prev_assignment_action_id = plb.prev_assignment_action_id,
prev_balance_value = plb.prev_balance_value
where latest_balance_id = plb.latest_balance_id;
update pay_latest_balances
set assignment_action_id = lb.assignment_action_id,
value = lb.value,
expiry_date = lb.expiry_date,
expired_assignment_action_id = lb.expired_assignment_action_id,
expired_value = lb.expired_value,
expired_date = lb.expired_date,
prev_assignment_action_id = lb.prev_assignment_action_id,
prev_expiry_date = lb.prev_expiry_date,
prev_balance_value = lb.prev_balance_value
where latest_balance_id = lb.latest_balance_id;
SELECT pai.locked_action_id
INTO l_mst_id
FROM pay_action_interlocks pai,pay_assignment_actions paa
WHERE pai.locking_action_id = p_act_id
AND paa.assignment_action_id = pai.locked_action_id
AND paa.source_action_id is null;
select 1
into found
from dual
where exists (select 1 from pay_assignment_actions paa
where paa.source_action_id=l_mst_id);
select child_asg.assignment_action_id
into new_assactid
from pay_assignment_actions mast_asg,
pay_assignment_actions child_asg,
pay_assignment_actions rev_asg,
pay_action_interlocks pai
where l_mst_id=mast_asg.assignment_action_id
and child_asg.source_action_id is not null
and child_asg.assignment_id=mast_asg.assignment_id
and child_asg.payroll_action_id=mast_asg.payroll_action_id
and child_asg.action_status<>'B'
and pay_core_utils.get_process_path(child_asg.assignment_action_id)
=rev_asg.process_path
and rev_asg.assignment_action_id=p_act_id
and pai.locking_action_id=p_act_id
and pai.locked_action_id=child_asg.assignment_action_id;
SELECT business_group_id
into v_business_group_id
FROM per_all_assignments_f
WHERE assignment_id = p_assignment_id
AND p_effective_date BETWEEN effective_start_date AND effective_end_date ;
/*Call the following function to insert a quickpay payroll action*/
pay_qpq_api.ins
(
p_business_group_id => v_business_group_id
,p_assignment_id => p_assignment_id
,p_consolidation_set_id => p_consolidation_set_id
,p_effective_date => p_effective_date
,p_run_type_id => p_run_type_id
,p_date_earned => p_effective_date
,p_payroll_action_id => v_payroll_action_id
,p_p_object_version_number => v_p_object_version_number
,p_assignment_action_id => v_assignment_action_id
,p_a_object_version_number => v_a_object_version_number
);
/*Inserting the action sequence in pay_payroll_actions since
it is not inserted by the previous called api
also inserting the batch_process_mode as DUM_QPAY to indicate
the payrollaction is inserted using this code*/
update pay_payroll_actions
set action_sequence = pay_payroll_actions_s.nextval,
batch_process_mode = 'DUM_QPAY'
where payroll_action_id = v_payroll_action_id
and action_sequence is null;
select /*+ ORDERED*/ plr.rule_mode
into v_r_mode
from per_business_groups_perf grp,
pay_legislation_rules plr
where grp.business_group_id = v_business_group_id
and plr.legislation_code = grp.legislation_code
and plr.rule_type = 'I'
and plr.rule_mode in ('Y','N','G');
update pay_assignment_actions
set ACTION_STATUS = 'C'
where assignment_action_id = v_assignment_action_id;
update pay_payroll_actions
set action_status = 'C'
where payroll_action_id = v_payroll_action_id;