1 package hrassact AUTHID CURRENT_USER as
2 /* $Header: pyassact.pkh 120.8.12020000.2 2012/07/19 08:00:47 kskoduri ship $ */
3 /*
4 ******************************************************************
5 * *
6 * Copyright (C) 1993 Oracle Corporation UK Ltd., *
7 * Chertsey, England. *
8 * *
9 * All rights reserved. *
10 * *
11 * This material has been provided pursuant to an agreement *
12 * containing restrictions on its use. The material is also *
13 * protected by copyright law. No part of this material may *
14 * be copied or distributed, transmitted or transcribed, in *
15 * any form or by any means, electronic, mechanical, magnetic, *
16 * manual, or otherwise, or disclosed to third parties without *
17 * the express written permission of Oracle Corporation UK Ltd, *
18 * Oracle Park, Bittams Lane, Guildford Road, Chertsey, Surrey, *
19 * England. *
20 * *
21 ******************************************************************
22
23 ======================================================================
24
25
26 Change List
27 ===========
28
29 Version Date Author ER/CR No. Description of Change
30 -------+---------+----------+---------+-------------------------------
31 115.33 19/07/12 kskoduri 13369815 Added global gv_dum_qpay and the procedure
32 ins_dummy_quickpay_action for enabling the
33 insertion of dummy quickpay action
34 115.31 14/04/09 priupadh 7652030 Added global variables gv_multi_reversal
35 and gv_cnt_reversal_act_id
36 115.30 26/02/08 ckesanap 6820127 Added another definition of
37 ext_man_payment to be called as
38 a concurrent request for Void and
39 Reversal enhancement.
40 115.29 30/11/06 DIVICKER Retain old reversal signature order
41 115.28 23/11/06 DIVICKER Reversal parameter change for multi
42 115.27 30/10/06 DIVICKER 5616882 Reversal by assignment set refactor
43 of reversal procedure
44 115.25 10/08/06 ALOGUE 5441737 Added actype to resequence_actions.
45 115.24 29/04/05 SuSivasu Added p_reason to ext_man_payment.
46 115.23 10/12/04 ALOGUE g_ba_lat_bal_maintenance global
47 for BAL_ADJ_LAT_BAL legislation
48 rule.
49 115.22 25/10/04 NBRISTOW Allow retropaying multi
50 assignments
51 115.21 25/10/04 THABARA Added p_element_type_id to
52 del_latest_balances().
53 115.20 20/09/04 THABARA 3482270 Orig Entry ID support for adjustments.
54 Added p_rrid and p_oentry to
55 set_action_context().
56 Added rrid to maintain_lat_bal().
57 115.19 09/08/04 tbattoo 3724695 Support for reversals and retropay
58 115.18 03/11/03 tbattoo support for sparse matrix and
59 pay_latest_balances table
60 115.17 05/06/03 ALOGUE 2960902 New trash_latest_balances only
61 passed balance_type_id and
62 trash_date. Overloads original
63 version.
64 115.16 05/03/03 sdhole 2805195 Added parameter tax_unit_id with
65 default value null to bal_adjust.
66 115.15 07/02/03 NBRISTOW Further new context changes.
67 115.14 05/02/03 NBRISTOW Added new contexts.
68 115.13 09/01/03 ALOGUE 2266326 Added CHECK_LATEST_BALANCES,
69 CHECK_RRVS_FIRST and
70 CHECK_LAT_BALS_FIRST globals
71 for tuning behaviour of
72 trash_latest_balances.
73 115.12 03/12/02 SCCHAKRA 2613838 Added procedure get_default_leg_value.
74 Included NOCOPY Performance Changes.
75 115.11 03/04/02 TBATTOO added p_run_type_id parameter
76 115.10 18/12/01 DSAXBY GSCC standards fix.
77 115.9 17/09/01 DSAXBY 1682940 Add purge_mode parameter to
78 bal_adjust_actions, inassact_main and
79 inassact procedures.
80 Added dbdrv line.
81 115.8 13/11/01 JTOMKINS Added prepay_flag to bal_adjust
82 and bal_adjust_actions.
83 115.7 04/09/01 NBRISTOW Added the resequence_chunk
84 procedure.
85 115.6 27/11/00 NBRISTOW Changes for source text context.
86 115.5 29/09/00 NBRISTOW Now passing tax unit id to
87 balance adjustments.
88 115.4 30/08/00 ALOGUE Pass eentryid to maintain_lat_bal.
89 115.3 04/08/00 ALOGUE New procedures maintain_lat_bal,
90 set_jurisdiction_code and
91 set_action_contexts.
92 115.2 19/05/00 NBRISTOW Added procedures to resequence
93 sequenced actions.
94 40.21 27/3/97 ALOGUE US reversal GRE Fix #459662
95 40.20 14/01/97 NBRISTOW Reverse Backport.
96 40.19 14/01/97 NBRISTOW Backport end of year performance
97 fix.
98 40.18 29/11/96 DSAXBY #366215 New procedure rev_pre_inserted_rr.
99 40.17 20/11/96 NBRISTOW Now passing a flag to inassact to
100 indicate if the assignment needs to
101 be locked.
102 40.16 19/06/96 NBRISTOW #374931 Now when a balance adjustment
103 is performed only the latest
104 balances feed by the adjustment
105 are deleted.
106 40.15 30/01/96 DSAXBY #333428 Changed trash_latest_balances procedure
107 to avoid trashing balances
108 un-necessarily. This required the
109 addition of a new parameter.
110 40.14 10/11/95 NBRISTOW Changed name of bal_adjust to
111 bal_adjust_actions, added extra
112 out arguments. Created new procedure
113 bal_adjust for existing bal_adjust
114 calls.
115 40.11 11/09/95 DSAXBY #307123 New parameter to reversal.
116 40.10 05/07/95 NBRISTOW Added initial balance payroll
117 action type.
118 40.9 16/12/94 DSAXBY Added qpppassact.
119 40.8 25/11/94 DSAXBY Change in parameters to qpassact.
120 40.7 15/11/94 DSAXBY Added overloaded public versions of
121 validate_pact_rollback and
122 validate_assact_rollback.
123 40.4 05/11/93 DSAXBY Made inassact public again.
124 40.3 04/11/93 DSAXBY Added qpassact and removed inassact.
125 40.2 27/10/93 DSAXBY Added del_latest_balances.
126 40.1 19/10/93 DSAXBY Altered bal_adjust defintion.
127 30.9 27/07/93 DSAXBY Altered bal_adjust definition.
128 30.8 20/07/93 DSAXBY Added bal_adjust definition..
129 3.0 11/03/93 H.MINTON Added copyright and exit line
130 ----------------------------------------------------------------------
131 */
132 type varchar_60_tbl IS TABLE OF VARCHAR(60) INDEX BY binary_integer;
133 type varchar_80_tbl IS TABLE OF VARCHAR(80) INDEX BY binary_integer;
134 type varchar_tbl IS TABLE OF VARCHAR(1) INDEX BY binary_integer;
135 type number_tbl IS TABLE OF number INDEX BY binary_integer;
136 type boolean_tbl IS TABLE OF boolean INDEX BY binary_integer;
137
138 /*Bug 7652030 Added below globals */
139 gv_multi_reversal BOOLEAN Default FALSE;
140 gv_dum_qpay BOOLEAN Default FALSE;
141 gv_cnt_reversal_act_id Number;
142 --
143 type context_details is record
144 (assact_id number_tbl,
145 asg_id number_tbl,
146 cxt_id number_tbl,
147 cxt_name varchar_60_tbl,
148 cxt_value varchar_60_tbl,
149 valid boolean_tbl,
150 sz number
151 );
152 --
153 -- The following are used for tuning the behaviour of the
154 -- trash_latest_balances procedure.
155 --
156 CHECK_LATEST_BALANCES boolean := TRUE;
157 CHECK_RRVS_FIRST boolean := FALSE;
158 CHECK_LAT_BALS_FIRST boolean := FALSE;
159
160 --
161 -- BAL_ADJ_LAT_BAL legislation rule value
162 --
163 g_ba_lat_bal_maintenance boolean := null;
164
165 /*----------------------- validate_pact_rollback -------------------------*/
166 /*
167 * This routine is called before a rollback to get any payroll action level
168 * information (e.g, action type) whch will be needed. This routine also
169 * performs some validation as to whether the action can be rolled back,
170 * and so may fail.
171 * It is overloaded with respect to an internal procedure.
172 */
173 procedure validate_pact_rollback
174 (
175 p_payroll_action_id in number,
176 p_rollback_mode in varchar2
177 );
178 --
179 /*----------------------- validate_assact_rollback -------------------------*/
180 /*
181 * This procedure is an overloaded public procedure to validate a
182 * particular assignment action.
183 */
184 function validate_assact_rollback
185 (
186 p_payroll_action_id in number,
187 p_assignment_action_id in number,
188 p_rollback_mode in varchar2
189 ) return boolean;
190 --
191 procedure ensure_assact_rolled_back (p_assact_id in number,
192 p_rollback_mode in varchar2);
193 procedure ensure_pact_rolled_back (p_pact_id in number);
194 --
195 /*----------------------- rollback_payroll_action ----------------------*/
196 /*
197 * This routine rolls back an entire payroll action.
198 *
199 * Three forms are available:
200 *
201 * 1) Rollback entire action without committing, fail if any individual
202 * assignment action couldn't be rolled back.
203 *
204 * 2) Rollback entire action without committing, continue if any
205 * assignment action couldn't be rolled back, setting
206 * p_failed_assact to indicate the problematic action.
207 *
208 * 3) Same as previous form, but commit in chunks as processing
209 * continues to avoid huge rollback segments.
210 *
211 * p_rollback_mode must be either ROLLBACK or RETRY
212 */
213 --
214 procedure rollback_payroll_action
215 (p_payroll_action_id in number,
216 p_rollback_mode in varchar2,
217 p_leave_base_table_row in boolean);
218 --
219 procedure rollback_payroll_action
220 (p_payroll_action_id in number,
221 p_failed_assact in out nocopy number,
222 p_rollback_mode in varchar2,
223 p_leave_base_table_row in boolean);
224 --
225 procedure rollback_payroll_action
226 (p_payroll_action_id in number,
227 p_chunk_size in number,
228 p_failed_assact in out nocopy number,
229 p_rollback_mode in varchar2,
230 p_leave_base_table_row in boolean);
231 --
232 --
233 /*--------------------------- rollback_ass_action -----------------------*/
234 /*
235 * This routine performs the actual work of rolling back a
236 * assignment action.
237 */
238 procedure rollback_ass_action
239 (p_assignment_action_id in number,
240 p_rollback_mode in varchar2,
241 p_leave_base_table_row in boolean);
242 --
243 --
244 /*------------------------- trash_latest_balances -----------------------*/
245 /*
246 * This procedure trashes any latest balances
247 * invalidated for the given balance type on or after the given
248 * date.
249 */
250 procedure trash_latest_balances(l_balance_type_id number,
251 l_input_value_id number,
252 l_trash_date date);
253 --
254 --
255 /*------------------------- trash_latest_balances -----------------------*/
256 /*
257 * This procedure trashes any latest balances
258 * invalidated for the given balance type on or after the given
259 * date.
260 */
261 procedure trash_latest_balances(l_balance_type_id number,
262 l_trash_date date);
263 --
264 /*------------------------- del_latest_balances -----------------------*/
265 /*
266 * This procedure trashes any latest balances invalidated for
267 * the given assignment on or after the specified date.
268 */
269 procedure del_latest_balances
270 (
271 p_assignment_id in number,
272 p_effective_date in date, -- allow date effective join.
273 p_element_entry in number default null,
274 p_element_type_id in number default null
275 );
276 --
277 --
278 /*--------------------------- applied_interlocks -------------------------*/
279 /*
280 * Returns a string of the assignment actions ids which are locked by
281 * the assignment action p_locking_action_id.
282 */
283 --
284 function applied_interlocks(p_locking_action_id number) return varchar2;
285 --
286 --------------------------- inassact ------------------------------
287 /*
288 NAME
289 inassact - INsert ASSignment Action
290 DESCRIPTION
291 Inserts and validates the insert of an assignment
292 action. This is called:
293 a) Internally, from within pyassact procedures.
294 b) Externall, from hrbaldtm package.
295 NOTES
296 o This is a general procedure, handling the insert of
297 assignent actions for QuickPay, Reversal, Balance Adjustment
298 and External/Manual payments.
299 o The last three parameters are only set for External/Manual payment
300 (before taxunt).
301 o inassact is a cover to inassact_main to ensure a taxunt gets
302 passed.
303 */
304 procedure inassact
305 (
306 pactid in number, -- payroll_action_id.
307 asgid in number, -- assignment_id to create action for.
308 p_ass_action_seq in number default null, --action sequence
309 p_serial_number in varchar2 default null, --cheque number
310 p_pre_payment_id in number default null, --pre payment id
311 p_element_entry in number default null,
312 p_asg_lock in boolean default TRUE, --lock assignment.
313 p_purge_mode in boolean default FALSE,--purge mode
314 run_type_id in number default null
315 );
316 --
317 procedure inassact_main
318 (
319 pactid in number, -- payroll_action_id.
320 asgid in number, -- assignment_id to create action for.
321 p_ass_action_seq in number default null, --action sequence
322 p_serial_number in varchar2 default null, --cheque number
323 p_pre_payment_id in number default null, --pre payment id
324 p_element_entry in number default null,
328 );
325 p_asg_lock in boolean default TRUE, --lock assignment.
326 taxunt in number default null, -- tax unit id
327 p_purge_mode in boolean default FALSE -- purge mode.
329
330 procedure inassact_main
331 (
332 pactid in number, -- payroll_action_id.
333 asgid in number, -- assignment_id to create action for.
334 p_ass_action_seq in number default null, --action sequence
335 p_serial_number in varchar2 default null, --cheque number
336 p_pre_payment_id in number default null, --pre payment id
337 p_element_entry in number default null,
338 p_asg_lock in boolean default TRUE, --lock assignment.
339 taxunt in number default null, -- tax unit id
340 p_purge_mode in boolean default FALSE, -- purge mode.
341 p_run_type_id in number default null
342 );
343
344 function inassact_main
345 (
346 pactid in number, -- payroll_action_id.
347 asgid in number, -- assignment_id to create action for.
348 p_ass_action_seq in number default null, --action sequence
349 p_serial_number in varchar2 default null, --cheque number
350 p_pre_payment_id in number default null, --pre payment id
351 p_element_entry in number default null,
352 p_asg_lock in boolean default TRUE, --lock assignment
353 taxunt in number default null, -- tax unit id
354 p_purge_mode in boolean default FALSE, --purge mode
355 p_run_type_id in number default null,
356 p_mode in varchar2 default 'STANDARD'
357 ) return number;
358 --
359 ----------------------------- qpassact ------------------------------
360 /*
361 NAME
362 qpassact - insert QuickPay ASSignment Action
363 DESCRIPTION
364 Inserts and validates the insert of an assignment
365 action for the QuickPay user exit.
366 NOTES
367 This procedure directly calls inassact.
368 */
369 procedure qpassact
370 (
371 p_payroll_action_id in number, -- payroll_action_id.
372 p_assignment_id in number, -- assignment_id to create action for.
373 p_assignment_action_id out nocopy number,
374 p_object_version_number out nocopy number
375 );
376 --
377 --------------------------- qpppassact ------------------------------
378 /*
379 NAME
380 qpppassact - Insert a QuickPay Pre-Payment action.
381 DESCRIPTION
382 Process a QuickPay Pre-Payment action.
383 NOTES
384 This procedure is meant to be called via the QuickPay form.
385 */
386 procedure qpppassact
387 (
388 p_payroll_action_id in number, -- of QuickPay pre-payment.
389 p_assignment_action_id out nocopy number,
390 p_object_version_number out nocopy number
391 );
392 --
393 --------------------------- reversal ------------------------------
394 /*
395 NAME
396 reversal - Process a reversal.
397 DESCRIPTION
398 Process a reversal for an assignment action.
399 NOTES
400 This is called directly from the Reversal form.
401 */
402 procedure reversal
403 (
404 pactid in number, -- payroll_action_id.
405 assactid in number, -- assignment_action_id to be reversed.
406 redo in boolean default false, -- insert assact and interlock if false
407 rev_aaid in number default 0, -- locking action id
408 multi in boolean default false -- skip setup for multi asg reversals
409 );
410 --
411 --------------------------- multi_assignment_reversal ------------------
412 /*
413 NAME
414 multi_assignment_reversal - Process a reversal called via PYUGEN.
415 DESCRIPTION
416 Process a reversal for an assignment action.
417 NOTES
418 This is called via PYUGEN. Basically a wrapper around the regular
419 single assignment ID, also passing multi flag TRUE
420 */
421 procedure multi_assignment_reversal
422 (
423 pactid in number, -- payroll_action_id.
424 assactid in number, -- assignment_action_id to be reversed.
425 rev_aaid in number -- locking action id
426 );
427 --
428 ----------------------- rev_pre_inserted_rr --------------------------
429 /*
430 NAME
431 rev_pre_inserted_rr - Reversal create pre-inserted run results.
432 DESCRIPTION
433 Creates pre-inserted run results when a Reversal is processed
434 These are created for any non-recurring or additional entry
435 type that is processed by the Reversal.
436 NOTES
437 This routine can be called, irrespective of whether the
438 results have already been inserted or not.
439 */
440 /*procedure rev_pre_inserted_rr
441 (
442 p_payroll_action_id in number -- payroll_action_id of reversal.
443 ); */
444 ----------------------------- ext_man_payment -------------------------
445 /*
446 NAME
447 ext_man_payment - External/Manual Payments
448 DESCRIPTION
449 Pre-Payment External/Manual Payments
450 NOTES
451 This is called directly from the Pre-Payment form.
452 */
453 procedure ext_man_payment
454 (
455 p_payroll_id in number, -- payroll id of assign
456 p_eff_date in date, -- session date
457 p_assignment_action_id in number, -- pre-payment assign action
458 p_assignment_id in number, -- assign id
459 p_comments in varchar2,-- comments
460 p_serial_number in varchar2,-- serial number
461 p_pre_payment_id in number, -- pre-payment id
462 p_reason in varchar2 default null -- Reason
463 );
464 --
465 -- Added for bug 6820127
469 ext_man_payment - Performs External/Manual Payments
466 -------------------------- ext_man_payment --------------------------
467 /*
468 NAME
470 DESCRIPTION
471 Process a External/Manual Payment.
472 NOTES
473 This procedure is called from the executable PYEXMNPT within the 'Cancel Check' flow.
474 */
475
476 procedure ext_man_payment
477 (
478 p_errmsg OUT NOCOPY VARCHAR2,
479 p_errcode OUT NOCOPY NUMBER,
480 p_payroll_id in number, -- payroll id of assign
481 p_eff_date in varchar2, -- session date
482 p_assignment_action_id in number, -- pre-payment assign action
483 p_assignment_id in number, -- assign id
484 p_comments in varchar2,-- comments
485 p_serial_number in varchar2,-- serial number
486 p_pre_payment_id in number, -- pre-payment id
487 p_reason in varchar2 default null -- Reason
488 );
489 --
490 --------------------------- set_action_contexts------------------------
491 /*
492 NAME
493 set_action_contexts - This sets up the action contexts for a
494 given element entry.
495 DESCRIPTION
496 NOTES
497 */
498 procedure set_action_context (p_assact in number,
499 p_rrid in number, -- run_result_id
500 p_entry in number,
501 p_tax_unit in number,
502 p_asgid in number,
503 p_busgrp in number,
504 p_legcode in varchar2,
505 p_oentry in number, -- original entry id
506 udca out nocopy context_details
507 );
508 --------------------------- bal_adjust_actions -----------------------
509 /*
510 NAME
511 bal_adjust_actions - perform balance adjustment.
512 DESCRIPTION
513 Process a balance adjustment.
514 NOTES
515 */
516 procedure bal_adjust_actions
517 (
518 consetid in number, -- consolidation_set_id.
519 eentryid in number, -- element_entry_id.
520 effdate in date, -- effective_date of bal adjust.
521 pyactid out nocopy number, -- payroll action id.
522 asactid out nocopy number, -- assignment action id.
523 act_type in varchar2 default 'B', -- payroll action type.
524 prepay_flag in varchar2 default null, -- include in prepay process?
525 taxunit in number default null, -- tax unit id
526 purge_mode in boolean default false, -- are we calling in purge mode?
527 run_type_id in number default null
528 );
529 --------------------------- bal_adjust ------------------------------
530 /*
531 NAME
532 bal_adjust - perform balance adjustment.
533 DESCRIPTION
534 Process a balance adjustment.
535 NOTES
536 This is called directly from the Balance Adjustment form.
537 This is a cover for the bal_adjust_actions procedure.
538 */
539 procedure bal_adjust
540 (
541 consetid in number, -- consolidation_set_id.
542 eentryid in number, -- element_entry_id.
543 effdate in date, -- effective_date of bal adjust.
544 act_type in varchar2 default 'B', -- payroll action type.
545 prepay_flag in varchar2 default null, -- Include in prepay process?
546 run_type_id in number default null ,
547 tax_unit_id in number default null
548 );
549 --
550 --------------------------- resequence_chunk ----------------------
551 /*
552 NAME
553 resequence_chunk
554 DESCRIPTION
555 Resequence sequenced actions for a whole chunk of assignments.
556 NOTE,S
557 */
558 procedure resequence_chunk
559 (
560 pactid in number,
561 cnkno in number,
562 rmode in varchar2, -- rule_mode (time period independent Y or N)
563 chldact in varchar2 default 'N' -- update child actions (Y or N)
564 );
565 --
566 --------------------------- resequence_actions ------------------------------
567 /*
568 NAME
569 resequence_actions - Resequences sequenced actions for an assignment.
570 DESCRIPTION
571 NOTES
572 */
573 procedure resequence_actions
574 (
575 aaid in number,
576 rmode in varchar2, -- rule_mode (time period independent Y or N)
577 chldact in varchar2 default 'N', -- update child actions (Y or N
578 actype in varchar2
579 );
580
581 procedure resequence_actions
582 (
583 pactid in number,
584 asgid in number,
585 actseq in number,
586 rmode in varchar2 -- rule_mode (time period independent Y or N)
587 );
588 --
589 --------------------------- update_action_sequence ------------------------------
590 /*
591 NAME
592 update_action_sequence
593 DESCRIPTION
594 Update the action sequence of a particular action.
595 NOTES
596 */
597 procedure update_action_sequence (p_assact in number,
598 rmode in varchar2);
599 --
600 --------------------------- maintain_lat_bal ------------------------
601 /*
602 NAME
603 maintain_lat_bal - maintenace of latest balances.
604 DESCRIPTION
605 Perform maintenace of latest balances within balance adjustment.
606 NOTES
607 This is called from the balance adjustment code above and from the
608 batch balance adjustment code.
609 */
610 procedure maintain_lat_bal
611 (
612 assactid in number, -- assignment_action_id of inserted action.
613 rrid in number, -- run_result_id
614 eentryid in number, -- element entry id
615 effdate in date, -- effective_date of bal adjust.
616 udca in context_details,
617 act_type in varchar2 default 'B' -- payroll_action_type.
618 );
619 --
620 --------------------------- get_default_leg_value ------------------------
621 /*
622 NAME
623 get_default_leg_value - get the default run type
624 DESCRIPTION
625 Gets the default legislative specific run type id.
626 NOTES
627 This is called from the Quick Pay form.
628 */
629 procedure get_default_leg_value
630 (p_plsql_proc in varchar2
631 ,p_effective_date in varchar2
632 ,p_run_type_id out nocopy number
633 );
634 --
635 function get_retry_revesal_action_id(p_act_id number)
636 return number ;
637
638 --------------------------- get_cache_context------------------------
639 /*
640 NAME
641 get_cache_context - This retrieves the context id given the
642 context name from the cache.
643 DESCRIPTION
644 NOTES
645 */
646 procedure get_cache_context(p_cxt_name in varchar2,
647 p_cxt_id out nocopy number);
648
649
650 --------------------------- ins_dummy_quickpay_action------------------------
651 /*
652 NAME
653 ins_dummy_quickpay_action - This inserts a dummy quick pay payroll_action
654 if there are no payroll actions later to the
655 effective date mentioned
656 DESCRIPTION
657 NOTES
658 */
659 procedure ins_dummy_quickpay_action(p_assignment_id in number,
660 p_consolidation_set_id in number,
661 p_run_type_id in number,
662 p_effective_date in date );
663
664 end hrassact;
[Home] [Help]