1 package body pay_purge_pkg as
2 /* $Header: pypurge.pkb 120.15.12010000.1 2008/07/27 23:29:37 appldev ship $ */
3 /*
4 +======================================================================+
5 | Copyright (c) 2000 Oracle Corporation |
6 | Redwood Shores, California, USA |
7 | All rights reserved. |
8 +======================================================================+
9 Package Header Name : PAY_PURGE_PKG
10 Package File Name : pypurge.pkb
11
12 Description : Defines procedures for Purge functionality.
13
14 Change List:
15 ------------
16
17 Name Date Version Bug Text
18 -------------- ------------ ------- ------- ----------------------------
19 T. Habara 20-NOV-2006 115.30 5665425 Modified bal_exists. Added
20 the constant boundary for the
21 RR fetch limit.
22 Checking the rr count with
23 run result values.
24 T. Habara 10-NOV-2006 115.29 Modified bal_exists to check
25 the balance under a limited
26 condition.
27 T. Habara 16-OCT-2006 115.28 Modified open_asg_rep_cur to
28 order by context id and value.
29 T. Habara 24-MAY-2006 115.27 Modified csr_term_asg not to
30 use max(end_date).
31 T. Habara 17-MAY-2006 115.26 5231021 Modified csr_act in
32 pypu1_validate_asg.
33 T. Habara 03-APR-2006 115.25 5131274 Support of rollup date for
34 terminated assignments.
35 Added pypu1_validate_asg.
36 Modified t_asgact_rec,
37 get_act_info() and pypurgbv().
38 T. Habara 28-MAR-2006 115.24 Modified csr_bal_exists to
39 check the action status.
40 T. Habara 24-MAR-2006 115.23 Added hint to csr_bal_exists.
41 T. Habara 23-MAR-2006 115.22 Modified bal_exists.
42 T. Habara 21-MAR-2006 115.21 Modified init_pact and
43 bal_exists.
44 T. Habara 17-MAR-2006 115.20 5089841 Added init_pact,bal_exists.
45 Also added global variables
46 t_bal_tab, g_purge_action_id
47 and g_bal_exists.
48 Modified open_asg_rep_cur to
49 check actual tax unit id.
50 T. Habara 21-DEC-2005 115.19 4893251 Modified cursor c1 in pypurcif.
51 T. Habara 09-DEC-2005 115.18 4755511 Modified get_act_info.
52 T. Habara 23-NOV-2005 115.17 4755511 Modified pypurgbv to avoid
53 creating pay and asg action for
54 itd balances.
55 Added t_asgact_rec, g_asgact_rec
56 and get_act_info().
57 T. Habara 09-SEP-2005 115.16 4595640 Modified pypu2uacs to sync
58 action seq on run balances.
59 A. Logue 27-MAR-2004 115.15 Performance Repository fix
60 to c1 in pypu2uacs().
61 D. Saxby 24-FEB-2004 115.14 No longer force get_value call
62 to get value from db item.
63 Needed to allow access to
64 Run Balances.
65 D. Saxby 05-DEC-2002 115.13 2692195 Nocopy changes.
66 A. Logue 14-NOV-2002 115.12 3288322 Index hint cursor in pypu2uacs
67 for performance purposes.
68 D. Saxby 02-AUG-2002 115.11 Alter to allow SOURCE_TEXT and
69 SOURCE_ID context support.
70 D. Saxby 27-MAY-2002 115.10 GSCC fix.
71 D. Saxby 27-MAY-2002 115.9 2341428 Altered pypurgbv to detect
72 condition where the insert of
73 payroll action inserts no rows.
74 Caused ora-08002 error, currval
75 not defined. Note changes
76 elsewhere (pydynsql.pkb) should
77 prevent this situation occurring
78 but this is a safety measure.
79 RThirlby 02-MAY-2002 115.8 2348875 Altered != to <> for gscc
80 standards.
81 D. Saxby 25-APR-2002 115.7 2341428 Altered pypurgbv to detect
82 condition where the insert of
83 payroll action inserts no rows.
84 Caused ora-08002 error, currval
85 not defined. Note changes
86 elsewhere (pydynsql.pkb) should
87 prevent this situation occurring
88 but this is a safety measure.
89 D. Saxby 16-JAN-2002 115.6 2179667 Ensure that elements can be
90 correctly created in NLS envs.
91 D. Saxby 18-DEC-2001 115.5 GSCC standards fix.
92 D. Saxby 14-NOV-2001 115.4 Added procedure pypurgbv.
93 D. Saxby 06-AUG-2001 115.2 Added procedure pypurcif.
94 D. Saxby 15-DEC-2000 115.1 Amended order by statements.
95 D. Saxby 12-DEC-2000 115.0 Initial Version
96 ========================================================================
97 */
98
99 --
100 -- Global Types
101 --
102 type t_asgact_rec is record
103 (assignment_action_id number
104 ,action_status pay_assignment_actions.action_status%type
105 ,action_sequence number
106 -- Payroll action information
107 ,payroll_action_id number
108 ,action_type pay_payroll_actions.action_type%type
109 ,business_group_id number
110 ,action_population_status pay_payroll_actions.action_population_status%type
111 ,ppa_action_status pay_payroll_actions.action_status%type
112 ,effective_date date
113 ,date_earned date
114 -- Assignment information
115 ,assignment_id number
116 ,payroll_id number
117 ,time_period_id number
118 ,rollup_date date -- The rollup date for terminated assignment
119 );
120
121 --
122 type t_payact_rec is record
123 (payroll_action_id number
124 ,business_group_id number
125 ,legislation_code per_business_groups.legislation_code%type
126 ,effective_date date
127 ,asg_count number -- number of assignments to process.
128 );
129
130 type t_bal_tab is table of boolean index by binary_integer;
131
132 --
133 -- Global Variables
134 --
135 g_asgact_rec t_asgact_rec; -- assignment action cache.
136 g_purge_action_rec t_payact_rec; -- Purge payroll action cache.
137 g_bal_exists t_bal_tab; -- index by balance_type_id
138
139 /*
140 * Represents the current number of rows in
141 * the PAY_PURGE_ACTION_TYPES table.
142 * Allows an important sanity check that the
143 * correct rows are present - else there can be
144 * serious consequences.
145 */
146 PURGE_ACTION_TYPE_ROWS constant binary_integer := 20;
147
148 /*
149 * Private procedure to get the payroll and assignment action
150 * information.
151 */
152 procedure get_act_info
153 (p_assignment_action_id in number
154 ,p_asgact_rec out nocopy t_asgact_rec
155 )
156 is
157 --
158 l_asgact_rec t_asgact_rec;
159 --
160 cursor csr_asgact
161 is
162 select
163 act.action_status
164 ,act.action_sequence
165 -- Payroll action information
166 ,act.payroll_action_id
167 ,pac.action_type
168 ,pac.business_group_id
169 ,pac.action_population_status
170 ,pac.action_status ppa_action_status
171 ,pac.effective_date
172 ,pac.date_earned
173 -- Assignment information
174 ,act.assignment_id
175 ,act.end_date rollup_date
176 from
177 pay_payroll_actions pac
178 ,pay_assignment_actions act
179 where
180 act.assignment_action_id = p_assignment_action_id
181 and pac.payroll_action_id = act.payroll_action_id
182 ;
183 --
184 cursor csr_asg(p_assignment_id number
185 ,p_effective_date date)
186 is
187 select
188 -- Assignment information
189 asg.payroll_id
190 ,ptp.time_period_id
191 from
192 per_all_assignments_f asg
193 ,per_time_periods ptp
194 where
195 asg.assignment_id = p_assignment_id
196 and p_effective_date between
197 asg.effective_start_date and asg.effective_end_date
198 and ptp.payroll_id = asg.payroll_id
199 and p_effective_date between
200 ptp.start_date and ptp.end_date;
201 --
202 begin
203 --
204 -- Check if the assignment action cache exists.
205 --
206 if p_assignment_action_id = g_asgact_rec.assignment_action_id then
207 --
208 -- Cache already exists.
209 --
210 p_asgact_rec := g_asgact_rec;
211
212 elsif p_assignment_action_id is not null then
213 --
214 -- Set the new assignment action id.
215 --
216 l_asgact_rec.assignment_action_id := p_assignment_action_id;
217
218 --
219 -- Retrieve the assignment action information.
220 --
221 open csr_asgact;
222 fetch csr_asgact into l_asgact_rec.action_status
223 ,l_asgact_rec.action_sequence
224 -- Payroll action information
225 ,l_asgact_rec.payroll_action_id
226 ,l_asgact_rec.action_type
227 ,l_asgact_rec.business_group_id
228 ,l_asgact_rec.action_population_status
229 ,l_asgact_rec.ppa_action_status
230 ,l_asgact_rec.effective_date
231 ,l_asgact_rec.date_earned
232 -- Assignment information
233 ,l_asgact_rec.assignment_id
234 ,l_asgact_rec.rollup_date;
235 close csr_asgact;
236 --
237 -- Retrieve the assignment information.
238 --
239 open csr_asg(l_asgact_rec.assignment_id
240 ,nvl(l_asgact_rec.rollup_date, l_asgact_rec.effective_date));
241 fetch csr_asg into l_asgact_rec.payroll_id
242 ,l_asgact_rec.time_period_id;
243 close csr_asg;
244 --
245 -- Set the global cache and out variable.
246 --
247 g_asgact_rec := l_asgact_rec;
248 p_asgact_rec := l_asgact_rec;
249
250 end if;
251
252 end get_act_info;
253
254 /*
255 * This procedure initializes the cached payroll action information.
256 *
257 */
258 procedure init_pact
259 (
260 p_purge_action_id in number
261 )
262 is
263 l_proc varchar2(80):='pay_purge_pkg.init_pact';
264 cursor csr_ppa is
265 select
266 ppa.action_type
267 ,ppa.business_group_id
268 ,pbg.legislation_code
269 ,ppa.effective_date
270 ,ppa.balance_set_id
271 from
272 pay_payroll_actions ppa
273 ,per_business_groups_perf pbg
274 where
275 ppa.payroll_action_id = p_purge_action_id
276 and pbg.business_group_id = ppa.business_group_id
277 ;
278 --
279 l_ppa_rec csr_ppa%rowtype;
280
281 cursor csr_balset(p_bal_set_id number)
282 is
283 select
284 distinct pdb.balance_type_id
285 from
286 pay_balance_set_members pbsm
287 ,pay_defined_balances pdb
288 where
289 pbsm.balance_set_id = p_bal_set_id
290 and pdb.defined_balance_id = pbsm.defined_balance_id
291 order by pdb.balance_type_id
292 ;
293 begin
294 hr_utility.set_location('Entering: '||l_proc, 10);
295
296 if p_purge_action_id is not null then
297 --
298 -- Check to see if this is a purge action just in case.
299 --
300 open csr_ppa;
301 fetch csr_ppa into l_ppa_rec;
302 close csr_ppa;
303 --
304 hr_utility.set_location(l_proc, 15);
305 pay_core_utils.assert_condition
306 ('pay_purge_pkg.init_pact:1', nvl(l_ppa_rec.action_type,'null') = 'Z');
307 end if;
308
309 --
310 -- Reset the action cache.
311 --
312 g_purge_action_rec.payroll_action_id := p_purge_action_id;
313 g_purge_action_rec.business_group_id := l_ppa_rec.business_group_id;
314 g_purge_action_rec.legislation_code := l_ppa_rec.legislation_code;
315 g_purge_action_rec.effective_date := l_ppa_rec.effective_date;
316
317 hr_utility.trace(' Payroll Action ID = '||p_purge_action_id);
318 hr_utility.trace(' Business Group ID = '||l_ppa_rec.business_group_id);
319 hr_utility.trace(' Legislation Code = '||l_ppa_rec.legislation_code);
320 hr_utility.trace(' Effective Date = '||l_ppa_rec.effective_date);
321
322 --
323 -- Retrieve the number of assignments to process.
324 --
325 select count(assignment_action_id)
326 into g_purge_action_rec.asg_count
327 from pay_assignment_actions
328 where payroll_action_id = p_purge_action_id
329 and action_status <> 'C';
330
331 hr_utility.trace(' Asg Count = '||g_purge_action_rec.asg_count);
332
333 --
334 -- Delete the balance cache.
335 --
336 g_bal_exists.delete;
337
338 --
339 -- We can assume those balances in the specified balance set would
340 -- have balance values.
341 --
342 if l_ppa_rec.balance_set_id is not null then
343 for l_bal in csr_balset(l_ppa_rec.balance_set_id) loop
344 --
345 g_bal_exists(l_bal.balance_type_id) := true;
346 --
347 end loop;
348 end if;
349
350 hr_utility.set_location('Leaving: '||l_proc, 50);
351 end init_pact;
352
353 /*
354 * This function checks to see if the specified balance could have a
355 * value to rollup for the assignments to be processed in Purge.
356 * This is used in the Purge Preparation Phase1 to ensure if the
357 * balance should be added to the rollup balance list for the
358 * payroll action.
359 *
360 * NOTE: The importance of this function is to check the possibility
361 * of the balance existence in any dimensions especially
362 * _asg_itd. Since it is difficult to prove no balance exists
363 * by checking run balances, we need to check the run results
364 * at the moment.
365 *
366 */
367 function bal_exists
368 (
369 p_purge_action_id in number, -- Purge Payroll Action ID
370 p_balance_type_id in number
371 ) return varchar2
372 is
373 l_dummy number;
374 l_bal_exists boolean;
375 l_rr_fetch_limit number;
376 l_rr_count number;
377 --
378 -- Run Result Fetch Limit.
379 --
380 c_limit_min constant number:= 100;
381 c_limit_max constant number:= 5000;
382 --
383 cursor csr_check_rr_count
384 (p_baltypid number
385 ,p_purge_date date
386 ,p_bg_id number
387 ,p_leg_code varchar2
388 ,p_limit number
389 )
390 is
391 --
392 -- This sql checks whether the number of run result values for a given
393 -- balance type is less than the specific limit number.
394 --
395 -- NOTE: This cursor has to finish as soon as the number of fetch
396 -- exceeds the limit.
397 --
398 select
399 /*+ ordered
400 use_nl (piv prrv) */
401 count(1)
402 from
403 pay_balance_feeds_f pbf
404 ,pay_input_values_f piv
405 ,pay_run_result_values prrv
406 where
407 pbf.balance_type_id = p_baltypid
408 and pbf.effective_start_date <= p_purge_date
409 and piv.input_value_id = pbf.input_value_id
410 and pbf.effective_start_date between piv.effective_start_date
411 and piv.effective_end_date
412 and nvl(piv.business_group_id, p_bg_id) = p_bg_id
413 and nvl(piv.legislation_code, p_leg_code) = p_leg_code
414 and prrv.input_value_id = piv.input_value_id
415 and rownum < p_limit+2
416 ;
417
418 --
419 cursor csr_bal_exists
420 (p_baltypid number
421 ,p_purge_pactid number
422 ,p_purge_date date
423 ,p_bg_id number
424 ,p_leg_code varchar2)
425 is
426 --
427 -- This sql searches for any run results created for the specified
428 -- balance type before the purge date.
429 -- NOTE: The Purge assignment actions must be already prepared.
430 --
431 select
432 /*+ ordered
433 index (prr PAY_RUN_RESULTS_N1)
434 index (purge_paa PAY_ASSIGNMENT_ACTIONS_N51)
435 use_nl (piv prr paa ppa purge_paa prrv) */
436 1
437 from
438 pay_balance_feeds_f pbf
439 ,pay_input_values_f piv
440 ,pay_run_results prr
441 ,pay_assignment_actions paa
442 ,pay_payroll_actions ppa
443 ,pay_assignment_actions purge_paa
444 ,pay_run_result_values prrv
445 where
446 pbf.balance_type_id = p_baltypid
447 and pbf.effective_start_date <= p_purge_date
448 and piv.input_value_id = pbf.input_value_id
449 and pbf.effective_start_date between piv.effective_start_date
450 and piv.effective_end_date
451 and nvl(piv.business_group_id, p_bg_id) = p_bg_id
452 and nvl(piv.legislation_code, p_leg_code) = p_leg_code
453 and prr.element_type_id = piv.element_type_id
454 and paa.assignment_action_id = prr.assignment_action_id
455 and ppa.payroll_action_id = paa.payroll_action_id
456 and ppa.business_group_id+0 = p_bg_id
457 and ppa.effective_date <= p_purge_date
458 and ppa.effective_date between pbf.effective_start_date
459 and pbf.effective_end_date
460 and purge_paa.payroll_action_id = p_purge_pactid
461 and purge_paa.assignment_id = paa.assignment_id
462 -- not including completed purge assignment actions.
463 and purge_paa.action_status <> 'C'
464 and prrv.run_result_id = prr.run_result_id
465 and prrv.input_value_id = piv.input_value_id
466 and prrv.result_value is not null
467 ;
468
469 begin
470 --
471 -- Check if the payroll action id has been initialized.
472 --
473 if g_purge_action_rec.payroll_action_id = p_purge_action_id then
474 null;
475 elsif p_purge_action_id is not null then
476 init_pact(p_purge_action_id);
477 else
478 return null;
479 end if;
480
481 --
482 -- Check if the balance has already been in cache.
483 --
484 if g_bal_exists.exists(p_balance_type_id) then
485 --
486 -- Cache found.
487 --
488 l_bal_exists := g_bal_exists(p_balance_type_id);
489 else
490 --
491 -- The balance check should proceed only under a limited condition.
492 -- By default, assume the balance exists.
493 --
494 l_bal_exists := true;
495
496 --
497 -- Before going further for checking the balance with run results,
498 -- we have to ensure that this approach can be well under control.
499 -- This approach is effective only when the number of run results
500 -- are within a reasonable amount, hence we need to check the count
501 -- beforehand.
502 --
503 -- Setting the limit to the number of assignments within a
504 -- certain range.
505 --
506 l_rr_fetch_limit := least(greatest(g_purge_action_rec.asg_count
507 , c_limit_min), c_limit_max);
508
509 --
510 -- Check to see if the number of run results exceeds the limit.
511 --
512 open csr_check_rr_count
513 (p_balance_type_id
514 ,g_purge_action_rec.effective_date
515 ,g_purge_action_rec.business_group_id
516 ,g_purge_action_rec.legislation_code
517 ,l_rr_fetch_limit);
518 fetch csr_check_rr_count into l_rr_count;
519 close csr_check_rr_count;
520
521 if l_rr_count = 0 then
522 --
523 -- No run result exists for this balance type.
524 --
525 l_bal_exists := false;
526
527 elsif l_rr_count <= l_rr_fetch_limit then
528 --
529 -- Check to see if any run result exists for this balance.
530 --
531 open csr_bal_exists
532 (p_balance_type_id
533 ,p_purge_action_id
534 ,g_purge_action_rec.effective_date
535 ,g_purge_action_rec.business_group_id
536 ,g_purge_action_rec.legislation_code);
537 fetch csr_bal_exists into l_dummy;
538 if csr_bal_exists%found then
539 l_bal_exists := true;
540 else
541 l_bal_exists := false;
542 end if;
543 close csr_bal_exists;
544 end if;
545
546 --
547 -- Set this result to the cache.
548 --
549 g_bal_exists(p_balance_type_id) := l_bal_exists;
550
551 end if;
552
553 if l_bal_exists then
554 return 'Y';
555 else
556 return 'N';
557 end if;
558
559 end bal_exists;
560 --
561 /*
562 * This procedure validates the assignment for the specified
563 * assignment action id.
564 *
565 * If the assignment is terminated on the purge date, look for
566 * a possible rollup date for this assignment and set it on
567 * the assignment action.
568 *
569 */
570 procedure pypu1_validate_asg
571 (
572 p_assignment_action_id in number
573 ) is
574 --
575 l_exists number;
576 l_rollup_date date;
577 --
578 cursor csr_act
579 is
580 select
581 ppa.effective_date
582 ,paa.assignment_id
583 ,paa.end_date
584 from
585 pay_assignment_actions paa
586 ,pay_payroll_actions ppa
587 where
588 paa.assignment_action_id = p_assignment_action_id
589 and ppa.payroll_action_id = paa.payroll_action_id
590 ;
591 --
592 l_act_rec csr_act%rowtype;
593 --
594 cursor csr_active_asg
595 (p_assignment_id number
596 ,p_effective_date date)
597 is
598 select
599 1
600 from
601 per_all_assignments_f asg
602 ,per_time_periods ptp
603 where
604 asg.assignment_id = p_assignment_id
605 and p_effective_date between asg.effective_start_date
606 and asg.effective_end_date
607 and ptp.payroll_id = asg.payroll_id
608 and p_effective_date between ptp.start_date
609 and ptp.end_date;
610 --
611 cursor csr_term_asg
612 (p_assignment_id number
613 ,p_effective_date date)
614 is
615 --
616 -- The asg end date is in the period.
617 --
618 -- Effective Date
619 -- Asg |------------------> |
620 -- Prd |----->|----->|----->
621 --
622 -- Note: Max(end date) should not be used here since it will return
623 -- null when no rows found.
624 --
625 select
626 asg.effective_end_date end_date
627 from
628 per_all_assignments_f asg
629 ,per_time_periods ptp
630 where
631 asg.assignment_id = p_assignment_id
632 and asg.effective_end_date <= p_effective_date
633 and ptp.payroll_id = asg.payroll_id
634 and asg.effective_end_date between ptp.start_date
635 and ptp.end_date
636 UNION ALL
637 --
638 -- The time period ends before the asg end date.
639 -- Effective Date
640 -- Asg |------------------> |
641 -- Prd |----->|----->
642 --
643 select
644 ptp.end_date end_date
645 from
646 per_all_assignments_f asg
647 ,per_time_periods ptp
648 where
649 asg.assignment_id = p_assignment_id
650 and asg.effective_start_date <= p_effective_date
651 and ptp.payroll_id = asg.payroll_id
652 and ptp.end_date <= p_effective_date
653 and ptp.end_date between asg.effective_start_date
654 and asg.effective_end_date
655 order by 1 desc;
656 --
657 l_null_asgact_rec t_asgact_rec;
658 --
659 begin
660 --
661 -- Obtain the action info.
662 --
663 open csr_act;
664 fetch csr_act into l_act_rec;
665 close csr_act;
666
667 --
668 -- Check to see if the assignment is on a payroll period.
669 --
670 open csr_active_asg(l_act_rec.assignment_id, l_act_rec.effective_date);
671 fetch csr_active_asg into l_exists;
672 if csr_active_asg%found then
673 close csr_active_asg;
674 --
675 -- If the end date was set to a different date, this has to be corrected.
676 --
677 if nvl(l_act_rec.end_date, l_act_rec.effective_date)
678 <> l_act_rec.effective_date then
679 --
680 update pay_assignment_actions
681 set end_date = null
682 where assignment_action_id = p_assignment_action_id;
683 end if;
684 else
685 close csr_active_asg;
686 --
687 -- Obtain the rollup date for this assignment.
688 --
689 open csr_term_asg(l_act_rec.assignment_id, l_act_rec.effective_date);
690 fetch csr_term_asg into l_rollup_date;
691 if csr_term_asg%found and
692 (nvl(l_act_rec.end_date, l_act_rec.effective_date) <> l_rollup_date)
693 then
694 --
695 update pay_assignment_actions
696 set end_date = l_rollup_date
697 where assignment_action_id = p_assignment_action_id;
698 end if;
699 close csr_term_asg;
700 end if;
701 --
702 -- Reset the global asg act cache just in case.
703 --
704 g_asgact_rec := l_null_asgact_rec;
705 --
706
707 end pypu1_validate_asg;
708
709 procedure validate
710 (
711 p_balance_set_id in number default null,
712 p_assignment_set_id in number default null,
713 p_business_group_id in number,
714 p_reporting_date in date,
715 p_purge_date in date
716 ) is
717 l_types_count number;
718 begin
719 /*
720 * Further validation should appear below this point.
721 */
722
723 return;
724 end validate;
725
726 /*
727 * This procedure is designed to run various 'sanity checks'
728 * when purge phase two is about to be run.
729 */
730 procedure phase_two_checks
731 (
732 p_payroll_action_id in number
733 ) is
734 l_pat_count binary_integer;
735 begin
736
737 /*
738 * Perform quick check that we have the correct
739 * number of pay_purge_action_type rows.
740 * Serious consequences can occur if there are not
741 * the correct number of rows.
742 */
743 select count(*)
744 into l_pat_count
745 from pay_purge_action_types pat;
746
747 ff_utils.assert((PURGE_ACTION_TYPE_ROWS = l_pat_count),
748 'purge_sanity_checks:1');
749
750 end phase_two_checks;
751
752 procedure open_asg_rep_cur
753 (
754 p_ctx_cursor in out nocopy ctx_cur_t,
755 p_assignment_id in number,
756 p_purge_date in date
757 ) is
758 begin
759 open p_ctx_cursor for
760 select distinct
761 ffc.context_id,
762 ffc.context_name,
763 rep.jurisdiction_code
764 from pay_us_asg_reporting rep,
765 ff_contexts ffc
766 where rep.assignment_id = p_assignment_id
767 and ffc.context_name = 'JURISDICTION_CODE'
768 and rep.jurisdiction_code is not null
769 union all
770 --
771 -- Check the assignment action to restrict the contexts
772 -- with the purge date. Bug 5089841.
773 --
774 select distinct
775 ffc.context_id,
776 ffc.context_name,
777 to_char(paa.tax_unit_id)
778 from ff_contexts ffc,
779 pay_assignment_actions paa,
780 pay_payroll_actions ppa
781 where ffc.context_name = 'TAX_UNIT_ID'
782 and paa.assignment_id = p_assignment_id
783 and paa.tax_unit_id is not null
784 and ppa.payroll_action_id = paa.payroll_action_id
785 and ppa.effective_date <= p_purge_date
786 order by 1, 3;
787
788 end open_asg_rep_cur;
789
790 procedure open_act_ctx_cur
791 (
792 p_ctx_cursor in out nocopy ctx_cur_t,
793 p_assignment_id in number,
794 p_purge_date in date
795 ) is
796 begin
797 open p_ctx_cursor for
798 select ffc.context_id,
799 ffc.context_name,
800 to_char(act.tax_unit_id) context_value
801 from ff_contexts ffc,
802 pay_assignment_actions act,
803 pay_payroll_actions pac
804 where act.assignment_id = p_assignment_id
805 and pac.payroll_action_id = act.payroll_action_id
806 and pac.effective_date <= p_purge_date
807 and act.tax_unit_id is not null
808 and ffc.context_name = 'TAX_UNIT_ID'
809 union
810 select ctx.context_id,
811 ffc.context_name,
812 ctx.context_value
813 from pay_action_contexts ctx,
814 ff_contexts ffc,
815 pay_assignment_actions act,
816 pay_payroll_actions pac
817 where ctx.assignment_id = p_assignment_id
818 and ffc.context_id = ctx.context_id
819 and ffc.context_name in ('JURISDICTION_CODE',
820 'ORIGINAL_ENTRY_ID',
821 'ELEMENT_ENTRY_ID',
822 'SOURCE_ID',
823 'SOURCE_TEXT')
824 and act.assignment_action_id = ctx.assignment_action_id
825 and pac.payroll_action_id = act.payroll_action_id
826 and pac.effective_date <= p_purge_date
827 order by 1, 3;
828
829 end open_act_ctx_cur;
830
831 procedure open_ctx_cur
832 (
833 p_ctx_cursor in out nocopy ctx_cur_t,
834 p_assignment_id in number,
835 p_purge_date in date,
836 p_select_type in varchar2
837 ) is
838 begin
839 -- Open the appropriate cursor, depending on
840 -- the string passed in.
841 if(p_select_type = 'ASG_REPORTING') then
842 open_asg_rep_cur(p_ctx_cursor, p_assignment_id, p_purge_date);
843 elsif(p_select_type = 'ACT_CONTEXTS') then
844 open_act_ctx_cur(p_ctx_cursor, p_assignment_id, p_purge_date);
845 else
846 ff_utils.assert(false, 'open_ctx_cur:1');
847 end if;
848 end open_ctx_cur;
849
850 procedure pypu2uacs
851 (
852 p_batch_id in number,
853 p_action_sequence in number -- of current Purge action.
854 ) is
855
856 /*
857 * Will return the balance initialization actions
858 * in reverse time order.
859 */
860 cursor c1 is
861 select /*+ INDEX(act PAY_ASSIGNMENT_ACTIONS_N51) */
862 distinct
863 act.rowid
864 ,act.assignment_action_id
865 ,act.action_sequence
866 from pay_assignment_actions act,
867 pay_balance_batch_lines bbl
868 where bbl.batch_id = p_batch_id
869 and act.payroll_action_id = bbl.payroll_action_id
870 and act.assignment_id = bbl.assignment_id
871 order by act.action_sequence desc;
872
873 l_action_sequence number;
874 l_update_count number := 0;
875
876 begin
877 -- The action_sequence must NOT be null.
878 ff_utils.assert((p_action_sequence is not null), 'pypu2uacs:1');
879
880 l_action_sequence := p_action_sequence;
881
882 for c1rec in c1 loop
883 l_action_sequence := l_action_sequence - 1;
884
885 update pay_assignment_actions act
886 set act.action_sequence = l_action_sequence
887 where act.rowid = c1rec.rowid;
888
889 --
890 -- Bug 4595640.
891 -- Update action sequence on run balances.
892 --
893 update pay_run_balances prb
894 set prb.action_sequence = l_action_sequence
895 where prb.assignment_action_id = c1rec.assignment_action_id;
896
897 l_update_count := l_update_count + 1;
898
899 end loop;
900
901 -- Would expect some actions to have been updated.
902 ff_utils.assert((l_update_count > 0), 'pypu2uacs:2');
903 end pypu2uacs;
904
905 procedure pypurgbv
906 (
907 p_defined_balance_id in number,
908 p_assignment_action_id in number,
909 p_balance_value out nocopy number,
910 p_nonzero_flag out nocopy binary_integer
911 ) is
912 l_new_assactid number;
913 l_new_payactid number;
914 l_asgact_rec t_asgact_rec; -- assignment action info.
915 l_period_type pay_balance_dimensions.period_type%type;
916 l_creating_new_action boolean;
917 --
918 -- Cursor to check the period type
919 --
920 cursor csr_period_type
921 is
922 select
923 pbd.period_type
924 from
925 pay_defined_balances pdb
926 ,pay_balance_dimensions pbd
927 where
928 pdb.defined_balance_id = p_defined_balance_id
929 and pbd.balance_dimension_id = pdb.balance_dimension_id
930 ;
931
932 begin
933
934 p_nonzero_flag := 0;
935 p_balance_value := 0;
936
937 --
938 -- Get the assignment action information.
939 --
940 get_act_info
941 (p_assignment_action_id => p_assignment_action_id
942 ,p_asgact_rec => l_asgact_rec
943 );
944
945 --
946 -- Check to see if the assignment is on a payroll on the date.
947 --
948 -- Note: This check is now performed on the rollup date of this
949 -- assignment. (Bug 5131274)
950 --
951 if l_asgact_rec.payroll_id is null then
952 --
953 -- Setting the balance value to 0 and exit.
954 --
955 p_nonzero_flag := 0;
956 p_balance_value := 0;
957 return;
958 end if;
959
960 --
961 -- Bug 4755511.
962 -- Temporary solution to avoid the majority of pay/asg action creation.
963 -- We don't have to create extra payroll and assignment actions
964 -- for certain type(s) of dimensions.
965 --
966 open csr_period_type;
967 fetch csr_period_type into l_period_type;
968 close csr_period_type;
969
970 if l_period_type = 'LIFETIME' and
971 l_asgact_rec.effective_date
972 = nvl(l_asgact_rec.rollup_date, l_asgact_rec.effective_date) then
973 --
974 -- Get balance value with the processing purge action.
975 --
976 p_balance_value := pay_balance_pkg.get_value
977 (p_defined_balance_id
978 ,p_assignment_action_id);
979 else
980
981 /*
982 * Before we call the get_value function, we insert
983 * a temporary assignment and payroll action.
984 * This is done to ensure that values of time_period_id
985 * and payroll_id are set on the payroll action associated
986 * with the assignment action whoes value is passed to
987 * the get_value call.
988 * In addition, the assignment action must have the same
989 * action_sequence value as that for the current purge
990 * action.
991 * As these are somewhat unique requirements, decided
992 * that would be better to do this here, rather than
993 * further complicate the balance code.
994 * We'd love to just update the purge payroll action
995 * to set the time_period_id, but expect this to be
996 * a major locking issue.
997 */
998
999 savepoint get_value;
1000
1001 --
1002 -- Set the indicator of the savepoint.
1003 --
1004 l_creating_new_action := true;
1005
1006 -- Obtain the payroll_action_id.
1007 select pay_payroll_actions_s.nextval
1008 into l_new_payactid
1009 from dual;
1010
1011 insert into pay_payroll_actions (
1012 payroll_action_id,
1013 action_type,
1014 business_group_id,
1015 payroll_id,
1016 action_population_status,
1017 action_status,
1018 effective_date,
1019 date_earned,
1020 time_period_id,
1021 object_version_number)
1022 values(l_new_payactid,
1023 l_asgact_rec.action_type,
1024 l_asgact_rec.business_group_id,
1025 l_asgact_rec.payroll_id,
1026 l_asgact_rec.action_population_status,
1027 l_asgact_rec.ppa_action_status,
1028 nvl(l_asgact_rec.rollup_date, l_asgact_rec.effective_date),
1029 nvl(l_asgact_rec.rollup_date, l_asgact_rec.date_earned),
1030 l_asgact_rec.time_period_id,
1031 1);
1032
1033 -- Obtain the assignment_action_id.
1034 select pay_assignment_actions_s.nextval
1035 into l_new_assactid
1036 from dual;
1037
1038 -- Action sequence must match value for purge.
1039 insert into pay_assignment_actions (
1040 assignment_action_id,
1041 assignment_id,
1042 payroll_action_id,
1043 action_status,
1044 action_sequence,
1045 object_version_number)
1046 values(l_new_assactid,
1047 l_asgact_rec.assignment_id,
1048 l_new_payactid,
1049 l_asgact_rec.action_status,
1050 l_asgact_rec.action_sequence,
1051 1);
1052
1053 /* do not bother looking for a latest balance */
1054 p_balance_value := pay_balance_pkg.get_value(p_defined_balance_id,
1055 l_new_assactid);
1056
1057 rollback to get_value;
1058
1059 end if;
1060
1061 if(p_balance_value <> 0) then
1062 p_nonzero_flag := 1;
1063 end if;
1064
1065
1066 exception
1067 -- A no data found should only occur if the assignment
1068 -- is terminated. In this case, their balance is by
1069 -- definition zero and this is returned immediately.
1070 when no_data_found then
1071 p_balance_value := 0;
1072 if l_creating_new_action then
1073 rollback to get_value;
1074 end if;
1075 end pypurgbv;
1076
1077 procedure pypurvbr
1078 (
1079 p_assignment_action_id in number
1080 ) is
1081 cursor c1 is
1082 select rub.defined_balance_id,
1083 rub.jurisdiction_code,
1084 rub.original_entry_id,
1085 rub.tax_unit_id,
1086 rub.value
1087 from pay_purge_rollup_balances rub
1088 where rub.assignment_action_id = p_assignment_action_id;
1089
1090 l_balance_name varchar2(200);
1091 l_value number;
1092 begin
1093 for c1rec in c1 loop
1094 -- Set context values if required.
1095 if(c1rec.jurisdiction_code is not null) then
1096 pay_balance_pkg.set_context('JURISDICTION_CODE',
1097 c1rec.jurisdiction_code);
1098 end if;
1099
1100 if(c1rec.original_entry_id is not null) then
1101 pay_balance_pkg.set_context('ORIGINAL_ENTRY_ID',
1102 c1rec.original_entry_id);
1103 pay_balance_pkg.set_context('ELEMENT_ENTRY_ID',
1104 c1rec.original_entry_id);
1105 end if;
1106
1107 if(c1rec.tax_unit_id is not null) then
1108 pay_balance_pkg.set_context('TAX_UNIT_ID', c1rec.tax_unit_id);
1109 end if;
1110
1111 -- Obtain the value of the balance - directly from results.
1112 l_value := pay_balance_pkg.get_value(c1rec.defined_balance_id,
1113 p_assignment_action_id,
1114 true);
1115
1116 -- Compare the expected and actual values.
1117 if(l_value <> c1rec.value) then
1118 -- Failure : obtain information about balance
1119 -- for error reporting.
1120 select upper(replace(pbt.balance_name, ' ', '_')) ||
1121 pbd.database_item_suffix
1122 into l_balance_name
1123 from pay_balance_types pbt,
1124 pay_balance_dimensions pbd,
1125 pay_defined_balances pdb
1126 where pdb.defined_balance_id = c1rec.defined_balance_id
1127 and pbt.balance_type_id = pdb.balance_type_id
1128 and pbd.balance_dimension_id = pdb.balance_dimension_id;
1129
1130 hr_utility.set_message(801, 'PAY_289018_PUR_BAL_VAL_FAIL');
1131 hr_utility.set_message_token('BALANCE', l_balance_name);
1132 hr_utility.set_message_token('EXPECTED', c1rec.value);
1133 hr_utility.set_message_token('ACTUAL', l_value);
1134 hr_utility.raise_error;
1135 end if;
1136 end loop;
1137 end pypurvbr;
1138
1139 procedure pypurcif
1140 (
1141 p_balance_set_id in number,
1142 p_business_group_id in number,
1143 p_legislation_code in varchar2
1144 ) is
1145
1146 c_sot constant date := to_date('0001/01/01', 'YYYY/MM/DD');
1147 c_eot constant date := to_date('4712/12/31', 'YYYY/MM/DD');
1148
1149 -- Number of input values (not including Jurisdiction
1150 -- and Pay Value that will be created when an element
1151 -- is created.
1152 c_iv_limit constant number := 15; -- which includes Jurdisdiction.
1153
1154 l_iv_count number;
1155 l_et_id pay_element_types_f.element_type_id%type;
1156 l_iv_id pay_input_values_f.input_value_id%type;
1157 l_el_id pay_element_links_f.element_link_id%type;
1158 l_et_name pay_element_types_f.element_name%type;
1159 l_iv_name pay_input_values_f.name%type;
1160 l_bg_name per_business_groups.name%type;
1161 l_et_count number := 0;
1162
1163 type et_r is record
1164 (
1165 iv_count number,
1166 currency_code pay_element_types.input_currency_code%type,
1167 jur_lev pay_balance_types.jurisdiction_level%type
1168 );
1169
1170 -- The table is indexed by element_link_id.
1171 type et_t is table of et_r index by binary_integer;
1172 l_et_tab et_t;
1173
1174 -- Return distinct set of balance types that might
1175 -- require rolling up by purge.
1176 --
1177 -- Bug 4893251. Include the supported dimensions only.
1178 cursor c1 is
1179 select pbt.balance_type_id,
1180 pbt.balance_name,
1181 pbt.balance_uom,
1182 pbt.currency_code,
1183 nvl(pbt.jurisdiction_level, 0) jurisdiction_level
1184 from pay_balance_set_members bsm,
1185 pay_defined_balances pdb,
1186 pay_balance_types pbt,
1187 pay_balance_dimensions pbd
1188 where bsm.balance_set_id = p_balance_set_id
1189 and pdb.defined_balance_id = bsm.defined_balance_id
1190 and pbt.balance_type_id = pdb.balance_type_id
1191 and pbd.balance_dimension_id = pdb.balance_dimension_id
1192 and pay_balance_upload.dim_is_supported
1193 (p_legislation_code, pbd.dimension_name) = 'Y'
1194 union /* do not return duplicates */
1195 select pbt.balance_type_id,
1196 pbt.balance_name,
1197 pbt.balance_uom,
1198 pbt.currency_code,
1199 nvl(pbt.jurisdiction_level, 0) jurisdiction_level
1200 from ff_fdi_usages_f fdu,
1201 ff_formulas_f fff,
1202 ff_database_items fdi,
1203 ff_user_entities fue,
1204 pay_defined_balances pdb,
1205 pay_balance_types pbt,
1206 pay_balance_dimensions pbd
1207 where ((fff.business_group_id is null and fff.legislation_code is null)
1208 or (fff.business_group_id is null
1209 and fff.legislation_code = p_legislation_code)
1210 or (fff.legislation_code is null
1211 and fff.business_group_id = p_business_group_id))
1212 and fdu.formula_id = fff.formula_id
1213 and fdu.usage = 'D'
1214 and fdi.user_name = fdu.item_name
1215 and fue.user_entity_id = fdi.user_entity_id
1216 and ((fue.business_group_id is null and fue.legislation_code is null)
1217 or (fue.business_group_id is null
1218 and fue.legislation_code = p_legislation_code)
1219 or (fue.legislation_code is null
1220 and fue.business_group_id = p_business_group_id))
1221 and fue.creator_type = 'B'
1222 and pdb.defined_balance_id = fue.creator_id
1223 and pbd.balance_dimension_id = pdb.balance_dimension_id
1224 and pbd.dimension_level = 'ASG'
1225 and pbd.period_type = 'LIFETIME'
1226 and pay_balance_upload.dim_is_supported
1227 (p_legislation_code, pbd.dimension_name) = 'Y'
1228 and pbt.balance_type_id = pdb.balance_type_id
1229 order by 1; -- balance_type_id.
1230
1231 -- Creates an element type and associated element link.
1232 -- The element link value is returned for further
1233 -- processing.
1234 -- The name of the element ends up of the form:
1235 -- 'Initial_Value_Element_<uniqueid>_<currency>'.
1236 function create_et_el
1237 (
1238 p_currency_code in varchar2,
1239 p_bg_name in varchar2,
1240 p_bg_id in number,
1241 p_et_count in number
1242 ) return number is
1243 l_et_name pay_element_types_f.element_name%type;
1244 l_et_id pay_element_types_f.element_type_id%type;
1245 l_el_id pay_element_links_f.element_link_id%type;
1246 l_ptr hr_lookups.meaning%type;
1247 begin
1248
1249 -- We use the element type sequence to obtain a unique
1250 -- value that becomes part of the name. This avoids
1251 -- problems if someone ever deletes any of the existing
1252 -- initial balance feeds and re-runs.
1253 -- Also, obtain the meaning for appropriate post termination
1254 -- rule to be used later. The procedure used to create the
1255 -- element requires meaning to be passed in, but we mustn't
1256 -- hard code it.
1257 select pay_element_types_s.nextval,
1258 hrl.meaning
1259 into l_et_id,
1260 l_ptr
1261 from hr_lookups hrl
1262 where hrl.lookup_type = 'TERMINATION_RULE'
1263 and hrl.lookup_code = 'F';
1264
1265 l_et_name := 'Initial_Value_Element_' ||
1266 l_et_id || '_' || p_et_count || '_' || p_currency_code;
1267
1268 l_et_id := pay_db_pay_setup.create_element (
1269 p_element_name => l_et_name,
1270 p_effective_start_date => c_sot,
1271 p_effective_end_date => c_eot,
1272 p_classification_name => 'Balance Initialization',
1273 p_input_currency_code => p_currency_code,
1274 p_output_currency_code => p_currency_code,
1275 p_processing_type => 'N',
1276 p_adjustment_only_flag => 'Y',
1277 p_business_group_name => p_bg_name,
1278 p_post_termination_rule => l_ptr);
1279
1280 hr_utility.trace('l_et_name : ' || l_et_name);
1281
1282 -- The element_information1 needs to be set to 'B'
1283 -- to allow rollup of subject balances. We have
1284 -- no 'official' way and so so use direct update.
1285 update pay_element_types_f pet
1286 set pet.element_information1 = 'B'
1287 where pet.element_type_id = l_et_id;
1288
1289 -- We need to create an appropriate element link
1290 -- for this type.
1291 -- Don't need to return the element link id though.
1292 l_el_id := pay_db_pay_setup.create_element_link (
1293 p_element_name => l_et_name,
1294 p_link_to_all_pyrlls_fl => 'Y',
1295 p_effective_start_date => c_sot,
1296 p_effective_end_date => c_eot,
1297 p_business_group_name => p_bg_name);
1298
1299 return(l_el_id);
1300
1301 end create_et_el;
1302
1303 -- Convenience routine that creates an input value,
1304 -- link input value and, if necessary, a balance feed.
1305 function cre_iv_bf
1306 (
1307 p_bg_name in varchar2,
1308 p_el_id in number,
1309 p_iv_name in varchar2,
1310 p_iv_uom in varchar2,
1311 p_seq in number,
1312 p_bt_id in number default null
1313 ) return number is
1314 l_et_name pay_element_types_f.element_name%type;
1315 l_bg_id per_business_groups.business_group_id%type;
1316 l_et_id pay_element_types_f.element_type_id%type;
1317
1318 begin
1319
1320 hr_utility.set_location ('cre_iv_bf', 10);
1321
1322 -- Grab some details for calls.
1323 select pet.element_name,
1324 pet.element_type_id,
1325 pet.business_group_id
1326 into l_et_name,
1327 l_et_id,
1328 l_bg_id
1329 from pay_element_links_f pel,
1330 pay_element_types_f pet
1331 where pel.element_link_id = p_el_id
1332 and pel.effective_start_date = c_sot
1333 and pel.effective_end_date = c_eot
1334 and pet.element_type_id = pel.element_type_id
1335 and pet.effective_start_date = c_sot
1336 and pet.effective_end_date = c_eot;
1337
1338 hr_utility.set_location ('cre_iv_bf', 20);
1339
1340 l_iv_id := pay_db_pay_setup.create_input_value (
1341 p_element_name => l_et_name,
1342 p_name => p_iv_name,
1343 p_uom_code => p_iv_uom,
1344 p_business_group_name => p_bg_name,
1345 p_effective_start_date => c_sot,
1346 p_effective_end_date => c_eot,
1347 p_display_sequence => p_seq);
1348
1349 hr_utility.set_location ('cre_iv_bf', 30);
1350
1351 hr_input_values.create_link_input_value(
1352 p_insert_type => 'INSERT_INPUT_VALUE',
1353 p_element_link_id => p_el_id,
1354 p_input_value_id => l_iv_id,
1355 p_input_value_name => p_iv_name,
1356 p_costable_type => NULL,
1357 p_validation_start_date => c_sot,
1358 p_validation_end_date => c_eot,
1359 p_default_value => NULL,
1360 p_max_value => NULL,
1361 p_min_value => NULL,
1362 p_warning_or_error_flag => NULL,
1363 p_hot_default_flag => NULL,
1364 p_legislation_code => NULL,
1365 p_pay_value_name => NULL,
1366 p_element_type_id => l_et_id);
1367
1368 hr_utility.set_location ('cre_iv_bf', 40);
1369
1370 if(p_bt_id is not null) then
1371 -- We must be creating a balance feed as well
1372
1373 hr_utility.set_location ('cre_iv_bf', 50);
1374
1375 hr_balances.ins_balance_feed(
1376 p_option => 'INS_MANUAL_FEED',
1377 p_input_value_id => l_iv_id,
1378 p_element_type_id => l_et_id,
1379 p_primary_classification_id => NULL,
1380 p_sub_classification_id => NULL,
1381 p_sub_classification_rule_id => NULL,
1382 p_balance_type_id => p_bt_id,
1383 p_scale => '1',
1384 p_session_date => c_sot,
1385 p_business_group => l_bg_id,
1386 p_legislation_code => NULL,
1387 p_mode => 'USER');
1388 end if;
1389
1390 return(l_iv_id);
1391
1392 end cre_iv_bf;
1393
1394 begin
1395
1396 hr_utility.set_location('pay_purge_pkg.pypurcif', 10);
1397
1398 select pbg.name
1399 into l_bg_name
1400 from per_business_groups pbg
1401 where pbg.business_group_id = p_business_group_id;
1402
1403 -- Return all the balance types that might need to have
1404 -- balances rolled up for them by purge.
1405 for c1rec in c1 loop
1406
1407 -- Find out if the balance already has an
1408 -- existing initial balance feed.
1409 declare
1410 l_dummy number;
1411 l_found boolean := false;
1412 l number;
1413 begin
1414 -- Note the date track restrictions that insist that
1415 -- the types, feeds and input values all exist
1416 -- across the whole of time.
1417 select 1
1418 into l_dummy
1419 from pay_balance_feeds_f pbf,
1420 pay_input_values_f piv,
1421 pay_element_types_f pet,
1422 pay_element_classifications pec
1423 where pbf.balance_type_id = c1rec.balance_type_id
1424 and piv.input_value_id = pbf.input_value_id
1425 and pet.element_type_id = piv.element_type_id
1426 and pec.classification_id = pet.classification_id
1427 and pec.balance_initialization_flag = 'Y'
1428 and pbf.effective_start_date = c_sot
1429 and pbf.effective_end_date = c_eot
1430 and piv.effective_start_date = c_sot
1431 and piv.effective_end_date = c_eot
1432 and piv.effective_start_date = c_sot
1433 and piv.effective_end_date = c_eot;
1434
1435 exception when no_data_found then
1436
1437 -- There is no initial balance feed, we therefore
1438 -- look to create an appropriate feed.
1439
1440 -- Begin by searching for an element type that
1441 -- can be used for feeding this balance. We want
1442 -- to match the following rules:
1443 -- a) For 'M' (money) balances, the element type's output
1444 -- currency code must match the balances currency code
1445 -- and the input and output currency codes must always
1446 -- be the same (to avoid currency conversion issues
1447 -- when the balance adjustment is processed).
1448
1449 hr_utility.set_location('pay_purge_pkg.pypurcif', 20);
1450
1451 l := l_et_tab.first;
1452
1453 while(l is not null) loop
1454
1455 if(l_et_tab(l).iv_count < c_iv_limit and
1456 (l_et_tab(l).currency_code = c1rec.currency_code
1457 or (l_et_tab(l).currency_code is null and
1458 c1rec.currency_code is null)) and
1459 l_et_tab(l).jur_lev = c1rec.jurisdiction_level)
1460 then
1461 -- We have found an element type.
1462 l_found := true;
1463 l_el_id := l;
1464 exit when l_found;
1465 end if;
1466
1467 l := l_et_tab.next(l);
1468
1469 end loop;
1470
1471 -- If we haven't found a type that has been created
1472 -- already, we create the element type, link and so on.
1473 if(not l_found) then
1474
1475 hr_utility.set_location('pay_purge_pkg.pypurcif', 30);
1476
1477 -- Record that another element type will be created.
1478 l_et_count := l_et_count + 1;
1479
1480 -- Create the element type and link.
1481 l_el_id := create_et_el(c1rec.currency_code, l_bg_name,
1482 p_business_group_id, l_et_count);
1483
1484 -- Always create a Jurisdiction input value to allow
1485 -- US legislative balances to work.
1486 l_iv_id := cre_iv_bf (l_bg_name, l_el_id, 'Jurisdiction', 'C', 1);
1487
1488 -- Store the relevant values in the et stuff.
1489 l_et_tab(l_el_id).iv_count := 1;
1490 l_et_tab(l_el_id).currency_code := c1rec.currency_code;
1491 l_et_tab(l_el_id).jur_lev := c1rec.jurisdiction_level;
1492
1493 end if;
1494
1495 hr_utility.set_location('pay_purge_pkg.pypurcif', 40);
1496
1497 -- The display sequence is the same as the
1498 -- input value count.
1499 l_iv_count := l_et_tab(l_el_id).iv_count;
1500
1501 -- Creates input value, link input value and
1502 -- balance feed.
1503 l_iv_id := cre_iv_bf (l_bg_name, l_el_id,
1504 substrb(c1rec.balance_name, 1, 28) || l_iv_count,
1505 c1rec.balance_uom, l_iv_count,
1506 c1rec.balance_type_id);
1507
1508 -- Record the number of input values now.
1509 l_et_tab(l_el_id).iv_count := l_et_tab(l_el_id).iv_count + 1;
1510
1511 if(l_et_tab(l_el_id).iv_count = c_iv_limit) then
1512 -- If the input value limit has been reached, we
1513 -- remove the element type from the list.
1514 hr_utility.trace('** l_el_id : ' || l_el_id);
1515 l_et_tab.delete(l_el_id);
1516 end if;
1517
1518 end;
1519
1520 end loop;
1521
1522 end pypurcif;
1523
1524 end pay_purge_pkg;
[Home] [Help]