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